June 3, 2009 at 2:01 am
Hello,
I'm trying to create a maintenance plan for my database. It does a daily backup of the transaction log, and then executes dbcc shrinkfile on the transaction log. The backup seems to work fine, but the job always gives an error when executing the transaction log. The task I use in the maintenance plan is 'Execute T-SQL Statement Task' and it has the following command:
dbcc shrinkfile(database_name_log, 70)
When I execute the query manually, it gives no problem.
In the job history I can just see:
Executed as user: DOMAIN\user. The package execution failed. The step failed.
In the windows event viewer I can see two relevant events:
I created the same maintenance plan without any problem on 8 other machines.
Does anyone know what could be the problem?
Thanks in advance.
June 3, 2009 at 2:32 am
Use DB_TEST
BACKUP LOG [DB_TEST] TO DISK = N'C:\BackupLOG\DB_TEST_log.trn' WITH NOFORMAT, NOINIT, NAME = N'DB_TEST-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
checkpoint
dbcc shrinkfile (DB_TEST_log,target_size)
For executing dbcc shrinkfile () you sholud be inside the particular database whose file you have to shrink.
always issue checkpoint before executing dbcc shrinkfile ().
Tanx 😀
June 3, 2009 at 2:34 am
Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.
If you shrink the log is is going to have to grow again. Growing a log is an expensive operation as logs cannot be instan initialised. While the log is growing changes to the DB have to wait. It's going to cause things to slow down
General recommendation for the log is to set it's size based on the activity in the DB and the frequency of your log backups and then leave the log alone. A once-off shrink after unusual database maintenance is OK, but regularly shrinking the log and then letting it grow again is a waste of time and resources.
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
June 3, 2009 at 2:35 am
Eswin (6/3/2009)
always issue checkpoint before executing dbcc shrinkfile ().
Why?
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
June 3, 2009 at 2:52 am
@Eswin. Thank you, I put the 'use DB_NAME' in front and it works now. I still don't understand why it works on the other 8 machines without the 'use DB_NAME'.
GilaMonster (6/3/2009)
Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.If you shrink the log is is going to have to grow again. Growing a log is an expensive operation as logs cannot be instan initialised. While the log is growing changes to the DB have to wait. It's going to cause things to slow down
General recommendation for the log is to set it's size based on the activity in the DB and the frequency of your log backups and then leave the log alone. A once-off shrink after unusual database maintenance is OK, but regularly shrinking the log and then letting it grow again is a waste of time and resources.
Well, someone recomended this approach to me about a year ago because the log always grew very large in a short time (short time = the size of the complete hard disk (80GB) in a few months, while the DB is only 500MB in size). This approach seems to do the job (log doesn't grow that large anymore). I understand it's not the most ideal approach, but unfortunatly I don't have the time available to clean things up (I wish I had though).
But maybe you can help me with my original problem. I don't know why the log is growing very large. I do encounter some conflicts in the replication from time to time and I haven't resolved them. Might that have something to do with it?
I kinda had to learn SQL Server completely from scratch in a very short time so that explains why I'm having problems I guess. I really wish I had more time to study it with a good book.
Thanks for your answers!
June 3, 2009 at 2:59 am
GilaMonster (6/3/2009)
Eswin (6/3/2009)
always issue checkpoint before executing dbcc shrinkfile ().Why?
SQL Server doesn't immediately shrink log files when you issue the DBCC Shrinkfile command. The DBCC Shrinkfile operation occurs only at checkpoints or transaction log backups. SQL Server segments each physical log file internally into a number of virtual log files (VLFs), which make up the transaction log. SQL Server marks the VLFs as truncateable either after SQL Server has backed them up or at checkpoints. At any given time, you might have VLFs with free or reusable space at the beginning, middle, and end of the log. Only when the VLFs that SQL Server marked as truncateable are at the end of the log file can the DBCC Shrinkfile operation remove the VLFs and shrink the log file. Because SQL Server can shrink a log file only to a virtual-log-file boundary, you can't shrink a log file to a size smaller than the size of a virtual log file—even if you aren't using the log file.
Normally when checkpoint is performed on a database, its transaction log is truncated to that checkpoint after the log backup.
Tanx 😀
June 3, 2009 at 7:27 am
Eswin (6/3/2009)
SQL Server doesn't immediately shrink log files when you issue the DBCC Shrinkfile command.
Yes it does. When you issue a SHRINKFILE, the file in question is shrunk. How far it's shrunk depends on how much free space is inside. Maybe I'm misunderstanding what you're saying here
The DBCC Shrinkfile operation occurs only at checkpoints or transaction log backups.
No. Log truncations occur at checkpoints (in simple recovery) or tran log backups in full/bulk logged. Neither operation shrinks or triggers a shrink
Normally when checkpoint is performed on a database, its transaction log is truncated to that checkpoint after the log backup.
Checkpoints only truncate the log in Simple Recovery. Transaction log backups truncate the log in full or bulk-logged recovery. The only thing that a checkpoint does in full recovery is write dirty data pages to disk.
Since the OP mentioned transaction log backup, it can be assumed that he's in full or bulk-logged recovery. In that case, a checkpoint will do nothing to the used space in the log or the size of the log file
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
June 3, 2009 at 7:30 am
rvz (6/3/2009)
I kinda had to learn SQL Server completely from scratch in a very short time so that explains why I'm having problems I guess. I really wish I had more time to study it with a good book.
Try this. It doesn't cover everything, but people tell me that it's fairly useful. Managing Transaction Logs[/url]
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
June 4, 2009 at 12:35 am
When ever I perform dbcc shrinkfile after transactional log backup in full recovery it didnt shrink my log file.
When i started issuing checkpoint after performing transactional log backup in full recovery and the performed dbcc shrinkfile i found that my log had shrunk.
That's why i asked to use checkpoint before dbcc shrinkfile.
Tanx 😀
June 4, 2009 at 7:50 am
rvz (6/3/2009)
Well, someone recomended this approach to me about a year ago because the log always grew very large in a short time (short time = the size of the complete hard disk (80GB) in a few months, while the DB is only 500MB in size).
Thanks for your answers!
Are you taking regular transaction log backups and full backups ? That will help manage log growth and ensure you can recover from various types of disaster.
June 4, 2009 at 8:32 am
homebrew01 (6/4/2009)
Are you taking regular transaction log backups and full backups ? That will help manage log growth and ensure you can recover from various types of disaster.
Yes that's what I'm doing.
Oh and Gila I've read your article. It gives a decent overview and clears some things up. Thanks.
June 4, 2009 at 9:01 am
You might have VLFs (virtual log files) that prevent shrinking at times. There's a Forcibly Shrink the Transaction Log script on this site that can help.
HOWEVER, you should not be doing this in a maintenance plan or regularly. You should be managing your data and log files and preventing the need for shrinking.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply