June 2, 2011 at 12:03 am
Hi, I am in a fix as my SQL 2000 server main database transactional log file grows to 50GB overnight due to high number of transactions. And affects the application running on sql server. I have put a cap of 50GB, as I am left with only 100GB free space in the drive.
Transactional log backup happens every 1 hour, and also I have scheduled a job running every half an hour to free up space from log. However both not helping much. I do shrink it in the morning and evening. Cant change it to simple recovery model due to safety measures(point to point backup)
Any ideas? 🙂
June 2, 2011 at 12:46 am
Yes in Simple recovery we cant do the transactional log, and shrink the database and see any deadlock process and kill the process i will help bit fast to backup.
June 2, 2011 at 1:27 am
joydeepr 82685 (6/2/2011)
Hi, I am in a fix as my SQL 2000 server main database transactional log file grows to 50GB overnight due to high number of transactions. And affects the application running on sql server. I have put a cap of 50GB, as I am left with only 100GB free space in the drive.Transactional log backup happens every 1 hour, and also I have scheduled a job running every half an hour to free up space from log. However both not helping much. I do shrink it in the morning and evening. Cant change it to simple recovery model due to safety measures(point to point backup)
Any ideas? 🙂
Post me what it's return
SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases name='test'
Check the link you may get an idea.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 2, 2011 at 2:10 am
Don't shrink your transaction log. It needs to be the size it needs to be. Shrinking wastes resources and causes physical fragmentation on your disk.
It sounds as if hourly log backups aren't frequent enough. Try changing to 15 minutes. However, if it's one single transaction that's causing your log to grow then this won't help. You'll either need to get more disk or redesign your application so that it works in small(er) batches.
John
June 2, 2011 at 4:43 am
Are you running any sort of index rebuilds during the night? This will cause the ldf to bloat if you are. Look for a script which rebuilds / reorganises based on fragmentation levels, plenty of them on this site.
June 2, 2011 at 5:42 am
Yes Kyle, the reindexing activity does bloats the ldf, have to look for better reindexing script.
I will give a try for increasing the log backup frequency.
🙂
June 3, 2011 at 12:45 am
Joydeep Roy C (6/2/2011)
Yes Kyle, the reindexing activity does bloats the ldf, have to look for better reindexing script.I will give a try for increasing the log backup frequency.
🙂
@Joydeep Roy
Just look the beta version script and test it your non prod servers.
Let me know how its goes.
SQL server 2000:Index defragmentation Script
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply