October 1, 2014 at 10:21 am
SQL Server 2008 R2
I have two SQL Server Agent jobs operating on one database each. Both jobs have the same steps and both are "failing" on step 3.
I put "failing" in quotes because the job does not fail, only an informational failure statement is printed in the job history but the job continues and completes the remaining steps.
Recovery Model is Full
step 1: Backup database
BACKUP DATABASE [x] TO DISK = N'location.bak' WITH NOFORMAT, INIT, NAME = N'x Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
step 2: Backup transaction log
BACKUP LOG [x] TO DISK = N'location.trn' WITH NOFORMAT, INIT, NAME = N'x Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
step 3: Truncate Transaction Log
DBCC SHRINKFILE (N'X_log' , 0, TRUNCATEONLY)
The "error" message from step 3 is:
Cannot shrink log file 2 (x_log) because the logical log file located at the end of the file is in use.
After researching this error the follow manual steps were executed with SSMS interactively. The research indicated these steps might need to be repeated multiple times before the transaction log would actually shrink.
Backup Log X TO Disk = 'location.trn'
DBCC SHRINKFILE (N'X_log' , 0, TRUNCATEONLY)
DBCC SQLPERF(LOGSPACE)
DBCC LOGINFO
After two executions, yes the transaction log did shrink as it should. Unfortunately, the nightly job failed exactly the same way the following night.
While the manual execution does fix it, as a workaround.
What do I need to change in my nightly job to no longer need the manual workaround execution?
You time and advise are appreciated.
October 1, 2014 at 10:30 am
Why are you truncating the log file? You're only affecting performance. You should define an appropriate size to fulfill your needs and prevent autogrowth with normal operations.
October 1, 2014 at 10:40 am
From the BOL, for BACKUP LOG statement...
LOG
Specifies a backup of the transaction log only. The log is backed up from the last successfully executed LOG backup to the current end of the log. After the log is backed up, the space may be truncated when no longer required by transactional replication or active transactions.
Note:
If backing up the log does not appear to truncate most of the log, an old open transaction may exist in the log. Log space can be monitored with DBCC SQLPERF (LOGSPACE). For more information, see Managing a Full Transaction Log.
So what is happening is that step 2 is backing up the log, which is also truncating any unused log space. When it gets to step 3 there isn't any more log space (at that time) to truncate. The rest of the log is active, so it fails. Running it over and over again, eventually part of the log becomes inactive/unused and the truncate happens. There really isn't a failure, it just can't do what you are asking it to do.
-SQLBill
October 1, 2014 at 10:54 am
Luis Cazares (10/1/2014)
Why are you truncating the log file? You're only affecting performance. You should define an appropriate size to fulfill your needs and prevent autogrowth with normal operations.
I would agree in principle, as what you suggest is straight from Autogrow/Shrink MS KB
These database are the backend to Incident management system. Thus, transaction volume is highly variable. All you need is a primary service to have an event and the transaction volume spikes significantly. Now if max size instituted on the transaction log, you have just added another problem for the support staff to handle because the spike would trip the max size threshold.
This approach has just created a second issue, compounding the first one. It was deemed more feasible to use the above approach.
October 1, 2014 at 11:05 am
As a general note, I suggest looking through this free book (free when downloaded as PDF): SQL Server Transaction Log Management by Tony Davis and Gail Shaw. It's the best source of info on this topic and I wish I had a copy back when I was a DBA.
Moving on...
What is the purpose of having a FULL recovery model if you are only aren't doing transaction log backups throughout the day? A FULL recovery model allows you to do restore your DB back to to the last transaction log backup. You are doing the DB and log backup at the same time, the backed up log file is essentially worthless. If you need point-in-time restores then you should stop truncating the log manually and increase the frequency of your log backups. If you don't need the ability to do point-in-time restores then change the recovery model to SIMPLE. a SIMPLE recovery model will automatically (well, usually :rolleyes:) manage your transaction log for you.
Edit: First, did not see Louis' reply when posting this... Also fixed some bad grammar.
-- Itzik Ben-Gan 2001
October 1, 2014 at 4:03 pm
doug.davidson (10/1/2014)
Luis Cazares (10/1/2014)
Why are you truncating the log file? You're only affecting performance. You should define an appropriate size to fulfill your needs and prevent autogrowth with normal operations.I would agree in principle, as what you suggest is straight from Autogrow/Shrink MS KB
These database are the backend to Incident management system. Thus, transaction volume is highly variable. All you need is a primary service to have an event and the transaction volume spikes significantly. Now if max size instituted on the transaction log, you have just added another problem for the support staff to handle because the spike would trip the max size threshold.
This approach has just created a second issue, compounding the first one. It was deemed more feasible to use the above approach.
I'm not saying that you should disable autogrowth and, even if is a good idea to set a max size to prevent full disk usage, I didn't suggest setting the max size.
All that I'm saying is that you shouldn't shrink your log file (not even with truncateonly) if you expect it to grow again at any time. You're basically adding unnecessary workload to your server when you can leave the unused allocated space in your log file.
As Alan said, if you're not going to take frequent log backups, you're basically wasting the advantages that full recovery model gives you.
October 2, 2014 at 2:42 am
doug.davidson (10/1/2014)
step 3: Truncate Transaction LogDBCC SHRINKFILE (N'X_log' , 0, TRUNCATEONLY)
Firstly, the comment is wrong. That doesn't truncate the log. The TruncateOnly option is only valid for data files, it's ignored when shrinking log files. All that statement does is attempt to shrink the log file to 0 MB. It won't be able to shrink anything if the active portion of the log is at the end of the file, as nothing ever moves log records around.
Secondly, why are you shrinking the log every night? That's just a waste of resources and will result in slower transactions the next day as the log regrows.
Shrinking the log should be a very exceptional operation, done only when some non-standard operation has grown the log well above what it needs to be for regular activity.
http://www.sqlservercentral.com/articles/Administration/64582/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply