November 8, 2012 at 3:27 am
Hello,
I know this question has probably been asked a thousand times, so I apologise in advance, but I appear to be unable to reduce the physical file size of a log file I have on a database using the Full recovery model.
So far, I have done:
* Backed up the transaction log
* Perform full backup of the database
* Checked via DBCC OPENTRAN for open or active transaction - there are none
* DBCC SHRINKDATABASE
After using DBCC SHRINKDATABASE and even DBCC SHRINKFILE using the log file ID, I can see that using DBCC SQLPERF(LOGSPACE) that less than 1% of the 50Gb log file is in use.
Initially the log file was created with a 4Gb file size, so I expected that the file would return to this size after the usual steps, but this is not so.
I imagine I've missed something simple, so would be most grateful for any advice to get my log file back to its 4Gb original empty size.
Regards
Steve
November 8, 2012 at 3:36 am
what is the output of the
dbcc loginfo( YourDBName)
also
select log_reuse_wait_desc from sys.databases where name = 'YourDbName'
-----------------------------------------------------------------------------
संकेत कोकणे
November 8, 2012 at 3:36 am
Personally, I wouldn't shrink the database if you don't HAVE to.
It tends to hit a specific size for a reason, and you'll be hit in performance when autogrowth takes place to expand it back to it's natural size.
As for the logs, if it is a problem with size and you don't have log shipping set up or a regular log backup, you can try the "WITH TRUNCATE_ONLY" option in the backup step and then try the shrinkfile.
I normally run;
shrinkfile
log backup
shrinkfile
It should work after this,
November 8, 2012 at 3:43 am
Managing Transaction Logs - http://www.sqlservercentral.com/articles/Administration/64582/
Why is my transaction log full - http://www.sqlservercentral.com/articles/Transaction+Logs/72488/
Stairway to Transaction Log Management - http://www.sqlservercentral.com/stairway/73776/
Some good links above on transaction log management.
As you have run shrinkdatabase I would strongly recommend checking for index fragmentation and rebuilding any indexes which need rebuilding.
As for the 50GB log file, what caused the log to get that big? One off data import? Poor transaction log management? Day to day activity?
Also remember that the log can only be shrunk to the most active point as the log is cyclical, so if the active portion of the log is marked at 49.5GB and you shrink it, it can only shrink it to 49.5GB, you will need to wait till the active portion of the log has looped round to the beginning of the file before you can shrink it down fully.
November 8, 2012 at 4:06 am
I ran the two commands you suggested. However, the second one told me that "LOG BACKUP" was needed. So, I performed another log backup and then did a SHRINKFILE on the log and Voila!
I now have a 4Gb log file once more! 🙂
What puzzles me though is the fact that after doing my first log backup that this didn't solve the problem. It seems doing the second one was needed, but I don't know why.
November 8, 2012 at 4:08 am
Probably as I stated on my post above, the active portion of the log was at the end and it needed the second backup to move the active portion back to the beginning of the file allowing you to shrink the file.
November 8, 2012 at 4:11 am
Thanks for your reply.
I've now solved the problem, but when I tried your suggestion of using the TRUNCATE_ONLY option on the log backup I received the following error:
Msg 155, Level 15, State 1, Line 3
'TRUNCATE_ONLY' is not a recognized BACKUP option.
November 8, 2012 at 4:12 am
TRUNCATE_ONLY has been removed in SQL 2008
November 8, 2012 at 4:14 am
anthony.green (11/8/2012)
Probably as I stated on my post above, the active portion of the log was at the end and it needed the second backup to move the active portion back to the beginning of the file allowing you to shrink the file.
Microsoft removed Truncate_only option from sql server 2005 onwards.
you should refer links provided by Anthony to manage your log file
-----------------------------------------------------------------------------
संकेत कोकणे
November 8, 2012 at 4:16 am
sorry truncate_only present in sql server 2005 .
Just verified 😀
-----------------------------------------------------------------------------
संकेत कोकणे
November 8, 2012 at 4:24 am
Ah apologies, yes you're right, you would have to set RECOVERY to SIMPLE and then run the shrink commands in SQL 2008.
Forgot that this was a discountinued command in 2008 😛
November 8, 2012 at 4:29 am
McSQL (11/8/2012)
Ah apologies, yes you're right, you would have to set RECOVERY to SIMPLE and then run the shrink commands in SQL 2008.Forgot that this was a discountinued command in 2008 😛
be aware about this . this can break LSN chain in log file
-----------------------------------------------------------------------------
संकेत कोकणे
November 8, 2012 at 4:31 am
sanket kokane (11/8/2012)
McSQL (11/8/2012)
Ah apologies, yes you're right, you would have to set RECOVERY to SIMPLE and then run the shrink commands in SQL 2008.Forgot that this was a discountinued command in 2008 😛
be aware about this . this can break LSN chain in log file
So does TRUNCATE_ONLY, should only ever use it if you dont care about recovery and if you dont care about recovery need to step back and decide if you actually need to be in the full or bulk logged recovery model.
November 8, 2012 at 4:36 am
Yeah they both do, and I only suggested it on the basis that the log file was at 50GB (from a standard 4GB), and therefore log shipping and log backups were unlikely to be taken, at least with any regularity.
The better option is to set up maintenance routines on your logs to prevent them getting to that size, but in the event you're not maintaining logs or are out of space, then it is an option to consider.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply