January 4, 2011 at 9:45 am
I have a database where the log grew to quite a size due to server issue. this required a sql service recycle to fix the issue.
Now i have performed a full backup on the database, and then don a dbcc shrinkfile on the log file, but the log is still the same size.
I thought the full backup would create a checkpoint so that i could shrink the log file.
Do i need to manually create a checkpoint?
January 4, 2011 at 10:11 am
January 4, 2011 at 10:14 am
but shouldnt a full backup mean i dont need to do a tran log backup?
January 4, 2011 at 10:20 am
No. Full backups and log backups are two completely different things. Full backups do not and never have truncated the transaction log. In full and bulk-logged recovery you need log backups to mark log space as reusable. In simple recovery a checkpoint is sufficient (which run automatically)
Please read through this - 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
January 4, 2011 at 10:21 am
A full backup doesn't truncate the log, that's why the t-log doesn't shrink. Only a transaction log backup will truncate the log and allow shrinkage.
A full only backs up data pages and enough of the log at the time of the backup to make it complete (captures transactions made at the time of backup).
A transaction log backup will backup all transactions since the last full backup (or last log backup in a sequence)
January 4, 2011 at 1:29 pm
but once i perform a full backup, isnt a checkpoint automatically performed, which will allow me to do a dbcc shrinkfile on the log file?
January 4, 2011 at 1:40 pm
Checkpoints flush buffer cache to disk. Nothing to do with truncating a log.
http://msdn.microsoft.com/en-us/library/ms188748(v=sql.90).aspx
January 4, 2011 at 1:45 pm
winston Smith (1/4/2011)
but once i perform a full backup, isnt a checkpoint automatically performed, which will allow me to do a dbcc shrinkfile on the log file?
Only in Simple recovery. In full or bulk-logged you need a log backup, not a checkpoint. See the article I linked to earlier.
If you are in simple recovery, query sys.databases and see what the value of log_reuse_wait_desc is for that database. That will list the reason the log space cannot be reused.
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
January 4, 2011 at 1:48 pm
calvo (1/4/2011)
Checkpoints flush buffer cache to disk. Nothing to do with truncating a log.
Not entirely true.
Checkpoint writes all dirty data page to disk and then, in simple recovery only, marks the inactive portion of the transaction log as reusable (log truncation).
http://msdn.microsoft.com/en-us/library/ms189085%28v=sql.90%29.aspx
Except when delayed for some reason, log truncation occurs automatically as follows:
* Under the simple recovery model, after a checkpoint.
* Under the full recovery model or bulk-logged recovery model, after a log backup, if a checkpoint has occurred since the previous backup.
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
January 4, 2011 at 2:27 pm
thanks folks. To get round my issue and allow me to shrink the log i :
1. recycled sql server srvc to kill the frozen backup spid that was stuck in rollback.
2. set the datbase to simple recovery.
3. Performed a full backup.
4. Done a dbcc shrink file.
The log is now back to normal. Am i correct in assuming that the backup in step 3 performed a checkpoint which freed up space in the log file, or could it have been an automatic checkpoint?
January 4, 2011 at 2:43 pm
Could have been either.
Should the DB be in simple recovery? Point-in-time restores not necessary?
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
January 4, 2011 at 2:54 pm
GilaMonster (1/4/2011)
calvo (1/4/2011)
Checkpoints flush buffer cache to disk. Nothing to do with truncating a log.Not entirely true.
Checkpoint writes all dirty data page to disk and then, in simple recovery only, marks the inactive portion of the transaction log as reusable (log truncation).
http://msdn.microsoft.com/en-us/library/ms189085%28v=sql.90%29.aspx
Except when delayed for some reason, log truncation occurs automatically as follows:
* Under the simple recovery model, after a checkpoint.
* Under the full recovery model or bulk-logged recovery model, after a log backup, if a checkpoint has occurred since the previous backup.
Thank you Ms. Shaw.
Recovery model relevance had slipped my mind.
January 4, 2011 at 3:22 pm
GilaMonster (1/4/2011)
Could have been either.Should the DB be in simple recovery? Point-in-time restores not necessary?
database should be in full recovery mode, but had the following issue.
1.tran log backup job kicked off.
2. For some reason it failed ( its litespeed by the way, if it makes a difference).
3. instead of failing outrightly, the spid sat in rollback status, with ETA ever increasing.
4. Log grew to a size greater than the free space on the server.
5. To kill the backup/rollback spid i had to recycle the sql server srvc.
6. Once the spid was killed, i still couldnt backup the log as there wasnt enough free space.
7. To allow me to get the log file to a managable size i set the db to simple mode, backed up the db and shrunk the file.
8. I then set back to full recovery and took another backup. All is cool now.
im not sure what kicked this all off at step 2. will need to identify that now that immediate danger is resolved.
Thanks for the help all.
January 4, 2011 at 4:14 pm
I like following thread for Resolving Full Log Esp in Simple Recovery Mode and then click on Link
-lucky
January 4, 2011 at 6:47 pm
Actually, the first backup you performed before shrinking the log file wasn't technically necessary. As soon as you changed the recovery model from full to simple - the virtual log files that did not currently have any open transactions were marked as reusable.
The database backup had nothing to do with this.
However, it is always a good idea to backup your database before you make these kinds of changes - just in case.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply