SQL Server Performance Forum – Threads Archive

sql server 2000 transaction log too large

My company as a sql server 2000 transaction log file that is too big. I need to truncate the size of the log file. I am relatively new to a small company that does not have an assigned DBA since there are only 5 programmers. I have been working with sql server 2000 for about 6 months. I have been told by my company that I will be performing the DBA functions. I am trying to figure out what I need to do to truncate the transaction log table. I have been setup as the SA for the production data base using enterprise manager.
I do not individually have access to the production sql server database except through the SQL Server tools like enterprise manager. Is there a reason why I would need direct access to the *.ldf file except through enterprise manager? Do I need to be able to map to the production sql server? If so, why would I need to setup this link? Note: I have read the "shrinking and truncating the transaction log" with the online
help. Can you recommend to me the best way to truncate and shrink a transaction log that is too large?

I suppose you have recovery model Full.
So, how often do you backup the transaction log?
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I am new to sql servwer 2000. From what I can tell, the databases are backed up daily.

Well, check if recovery model is full (Enterprise Manager, yourdatabase, properties,options).
If is full, you have to backup transaction log frequently, said each 30 minuts to reduce size. Check in Books on Line. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

If you are performing the database backups regularly then ensure Transaction log backups are also scheduled in shorter intervals in order to keep the size of Tlogs. Also chekc for any optimization or bulk insert jobs that might contribute the increase in size of Tlog. Refer to the books online for Transaction log architecture for better understanding.
There is no use if you truncate the log and other jobs are contributing the Tlog to increase, so you must ascertain the jobs and activity on the server and assign a value to the Tlog on the database. Also refer to the similar posts in the forum for more information. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=6894&SearchTerms=log
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8018&whichpage=1

After reading all so many comments about the Transaction log backup, seems that backing up transaction log is a very important step in maintaining the database. I have no experience in backing up transaction log file. All the while, I only backup the database which is done on the daily basis. Thus, I felt that I have missed out one very important step in maintaining my database. I have a few queries on the transaction log backup.
1) Is it a normal practice for a DBA to do transaction log backup ? I was not aware of tranaction log backup until I read so many posting on this topic in this forum. 2) If it is a normal practice, usually how often a DBA backup the transaction log. 3) What’s other benefits of backing up the transaction log file besides keeping the log file size smaller. Thank You.

The main benefit is near point-in-time recovery. Higher transaction log backup frequency means smaller data loss in case of disaster. The frequency depends on the trafic. We had tran log backups each 5 minutes on extremely busy server. If there is low activity on db server then each hour may be enough. It really depends on your situation.

]]>