Problems Shrinking Log File

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Problems Shrinking Log File

14 posts, Page 1 of 2

1 2 »»

Problems Shrinking Log File

Rate Topic

Display Mode

Topic Options

Author

Message

raotor

Posted Thursday, November 08, 2012 3:27 AM

SSC-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

sanket kokane

Posted Thursday, November 08, 2012 3:36 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 1:13 AM
Points: 323, Visits: 964

what is the output of the

dbcc loginfo( YourDBName)

also

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

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

Post #1382372

McSQL

Posted Thursday, November 08, 2012 3:36 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 7:39 AM
Points: 146, Visits: 280

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

anthony.green

Posted Thursday, November 08, 2012 3:43 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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

raotor

Posted Thursday, November 08, 2012 4:06 AM

SSC-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

anthony.green

Posted Thursday, November 08, 2012 4:08 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Post #1382388

raotor

Posted Thursday, November 08, 2012 4:11 AM

SSC-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

anthony.green

Posted Thursday, November 08, 2012 4:12 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

TRUNCATE_ONLY has been removed in SQL 2008

Post #1382396

sanket kokane

Posted Thursday, November 08, 2012 4:14 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 1:13 AM
Points: 323, Visits: 964

anthony.green (11/8/2012)

Microsoft removed Truncate_only option from sql server 2005 onwards.

you should refer links provided by Anthony to manage your log file

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

Post #1382398

sanket kokane

Posted Thursday, November 08, 2012 4:16 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 1:13 AM
Points: 323, Visits: 964

sorry truncate_only present in sql server 2005 .

Just verified BigGrin

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

Post #1382399

« Prev Topic | Next Topic »

14 posts, Page 1 of 2

1 2 »»

Permissions