Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problems Shrinking Log File


Problems Shrinking Log File

Author
Message
raotor
raotor
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 239
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
sanket kokane
sanket kokane
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 1019
what is the output of the

dbcc loginfo( YourDBName)



also


select log_reuse_wait_desc from sys.databases where name = 'YourDbName'



-----------------------------------------------------------------------------
संकेत कोकणे
McSQL
McSQL
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 299
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,
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6091 Visits: 6069
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.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


raotor
raotor
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 239
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! Smile

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.
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6091 Visits: 6069
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.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


raotor
raotor
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 239
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.

anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6091 Visits: 6069
TRUNCATE_ONLY has been removed in SQL 2008



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


sanket kokane
sanket kokane
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 1019
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

-----------------------------------------------------------------------------
संकेत कोकणे
sanket kokane
sanket kokane
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 1019
sorry truncate_only present in sql server 2005 .

Just verified :-D

-----------------------------------------------------------------------------
संकेत कोकणे
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search