Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Problems Shrinking Log File Expand / Collapse
Author
Message
Posted Thursday, November 8, 2012 3:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 7:03 AM
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
Post #1382366
Posted Thursday, November 8, 2012 3:36 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 1, 2013 10:17 AM
Points: 323, Visits: 984
what is the output of the

dbcc loginfo( YourDBName)

also

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



-----------------------------------------------------------------------------
संकेत कोकणे
Post #1382372
Posted Thursday, November 8, 2012 3:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:44 AM
Points: 146, Visits: 282

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,
Post #1382373
Posted Thursday, November 8, 2012 3:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:20 AM
Points: 5,143, Visits: 4,948
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
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

Post #1382376
Posted Thursday, November 8, 2012 4:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 7:03 AM
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! :)

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.
Post #1382385
Posted Thursday, November 8, 2012 4:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:20 AM
Points: 5,143, Visits: 4,948
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
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

Post #1382388
Posted Thursday, November 8, 2012 4:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 7:03 AM
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.
Post #1382394
Posted Thursday, November 8, 2012 4:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:20 AM
Points: 5,143, Visits: 4,948
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
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

Post #1382396
Posted Thursday, November 8, 2012 4:14 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 1, 2013 10:17 AM
Points: 323, Visits: 984
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


-----------------------------------------------------------------------------
संकेत कोकणे
Post #1382398
Posted Thursday, November 8, 2012 4:16 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 1, 2013 10:17 AM
Points: 323, Visits: 984
sorry truncate_only present in sql server 2005 .

Just verified


-----------------------------------------------------------------------------
संकेत कोकणे
Post #1382399
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse