April 27, 2015 at 4:31 am
SQL Version: Microsoft SQL Server 2012 with SP1
Recovery Model: Full
I had configured Maintenance Plans for my database but the transaction log keep grow till maximum size of the hard disk space. How do I keep my transaction log is shrink to it minimum size everytime transaction log backup has been completed?
Maintenance Plans:
Daily
1. Transaction log backup every 2 hours from 8am to 8pm
2. Differential database backup every weekday night
Weekly
1. Full database backup on Saturday
Currently I'm use below steps to reduce the transaction log manually:
1. Take a full backup of the database
2. Take a backup on the transaction log
3. Run below query,
USE ITBM
DBCC SHRINKFILE(2,20)
Transaction file is reduced. 🙂
April 27, 2015 at 5:09 am
If the transaction log is growing a lot, two things. One, take more frequent log backups. That will free up the committed transactions more often. Two, maybe you just need a bigger log? Either you have lots of transactions or your transactions are big or your transactions are running long or all of the above. You just need a bigger log than you think.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 28, 2015 at 1:34 am
I did take transaction log backup, but the transaction log isn't decrease but increase. Please refer to the attached screenshots.
April 28, 2015 at 4:03 am
Backing up the log won't decrease the file size of the log itself. It frees space within the log of committed transactions. If your log is continuing to grow, I suspect you may have either extremely large transactions, or you have uncommitted transactions that are holding on to their space. You should check sys.dm_tran_active_transactions to see if you have open transactions. You can combine that with other DMVs such as sys.dm_exec_requests and sys.dm_exec_sql_text to see the query, blocked/blocking processes, resources waits, etc.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 28, 2015 at 9:39 am
I see your tlog file is 33gb for 2 hours. When such a job runs regularly you shouldn't shrink the logfile - instead you should add more diskspace. If it was a one-time dataload then it's ok to shrink. As for logfile won't shrink - try DBCC OPENTRAN (https://msdn.microsoft.com/en-us/library/ms182792.aspx)
April 28, 2015 at 12:07 pm
If the files shown in that folder are all the transaction log backups taken for that DB, then it looks like you went 14 hours between log backups (4/27 6 PM to 4/28 8 AM), and 33 GB was written to the log in that time.
As has been stated previously, I'd look at taking more frequent log backups, especially in that timeframe, since it looks like some process is writing to the log quite heavily overnight (maybe overnight reindexing?).
Ultimately, you'll need the log file to be at least big enough to accommodate the log written between log backups, and for any long-running transactions that might span log backups (rebuilds of a very large index, for example).
To reduce the size of the log you need, you'll need to 1) increase the frequency of log backups, and 2) investigate what large, long-running transactions you might have running with an eye to perhaps breaking them up into smaller transactions.
Once you've done that, you should just plan to keep the log at least that big, since shrinking it just to have it grow again while user queries are running is not a good idea, as others have already pointed out.
I hope this helps!
April 28, 2015 at 8:38 pm
I forgot to mention that I had daily Rebuild Index on the Maintenance Plan. Is this cause the log file to grow?
April 28, 2015 at 11:17 pm
Axell (4/28/2015)
I forgot to mention that I had daily Rebuild Index on the Maintenance Plan. Is this cause the log file to grow?
As said before this can be the reason.
April 28, 2015 at 11:23 pm
Yes it can certainly cause a large transaction log. You might want to check out http://ola.hallengren.com. He has created some great scripts - you should take a look at indexoptimize.
I actually didn't notice the 14hr timedifference on the picture. I normally have transaction log backups every hour. This gives me a good indication of what times different work is being done. For example - if suddenly the translog is 15gb at 03.am. Then someone in dev/app have added a new job which causes it and it easier to ask dev/app team what job is running between 02-03am. Also - if you run into some trouble someday you know which time is best to take service down and do some critical maintenance.
April 29, 2015 at 12:05 am
Axell (4/27/2015)
SQL Version: Microsoft SQL Server 2012 with SP1Recovery Model: Full
I had configured Maintenance Plans for my database but the transaction log keep grow till maximum size of the hard disk space. How do I keep my transaction log is shrink to it minimum size everytime transaction log backup has been completed?
Maintenance Plans:
Daily
1. Transaction log backup every 2 hours from 8am to 8pm
2. Differential database backup every weekday night
Weekly
1. Full database backup on Saturday
Currently I'm use below steps to reduce the transaction log manually:
1. Take a full backup of the database
2. Take a backup on the transaction log
3. Run below query,
USE ITBM
DBCC SHRINKFILE(2,20)
Transaction file is reduced. 🙂
How big is the database for that log file, what's the largest the logfile has ever been, and how much free space do you have on the drive where the logfiles live?
--Jeff Moden
April 29, 2015 at 7:47 am
Ville-Pekka Vahteala (4/28/2015)
As said before this can be the reason.
Noted that, I try to disable the daily Rebuild Index today and see the result tomorrow.
runaldo (4/28/2015)
Yes it can certainly cause a large transaction log. You might want to check out http://ola.hallengren.com. He has created some great scripts - you should take a look at indexoptimize.I actually didn't notice the 14hr timedifference on the picture. I normally have transaction log backups every hour. This gives me a good indication of what times different work is being done. For example - if suddenly the translog is 15gb at 03.am. Then someone in dev/app have added a new job which causes it and it easier to ask dev/app team what job is running between 02-03am. Also - if you run into some trouble someday you know which time is best to take service down and do some critical maintenance.
Great, currently I set the transaction logs backup every 2 hours but only in 12 hours. I should set it to run 24 hours.
Jeff Moden (4/29/2015)
How big is the database for that log file, what's the largest the logfile has ever been, and how much free space do you have on the drive where the logfiles live?
Database file is 30GB
Largest transaction logs last time before I configure the daily Rebuild Index is around 80MB, after I configured the daily Rebuild Index. The transaction logs grow to 30GB in the next morning.
The transaction logs live in another HDD specially for the logs, 100GB total size and only left with 50GB free size. I have to perform manual truncate on the transaction logs if not it will grow to utilize all the HDD size
April 29, 2015 at 10:23 am
Set your transaction log backups to backup once every 30 minutes 24 hours a day. Nights are actually where this is needed the most because of things like index maintenance and batch jobs. You might even want to consider doing it once every 15 minutes.
You can also do a little bit of a trick if you don't mind not having Point-In-Time recovery available for the duration of your index maintenance to keep your log files from exploding at night. If you set the database recovery model to BULK LOGGED just before you start your rebuilds, REBUILDs are then minimally logged. REORGANIZEs are ALWAYS fully logged no matter what the recovery model is. Of course, don't forget to change back to the FULL Recovery Model when your index maintenance is done.
You really need to stop shrinking the log files no matter what. Unless you're shriking them to zero and rebuilding them properly, you're making a train wreck of performance because of all the VLF fragging you're doing by shrinking and then the system regrowing them every day.
--Jeff Moden
April 30, 2015 at 1:14 am
Hi guys, the issue has been resolved. It is caused by the Rebuild Index that I schedule on 10PM while my transaction logs backup is every 2 hours from 8AM to 8PM.
Right now I have disabled the Rebuild Index and change the schedule of the transaction logs backup to every 2 hours from 12AM to 11PM.
Thank you all and SQLServerCentral.com! 😀
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply