Reputation: 1201

Why can't I shrink a transaction log file, even after backup?

I have a database that has a 28gig transaction log file. Recovery mode is simple. I just took a full backup of the database, and then ran both:

backup log dbmcms with truncate_only

DBCC SHRINKFILE ('Wxlog0', TRUNCATEONLY)

The name of the db is db_mcms and the name of the transaction log file is Wxlog0.

Neither has helped. I'm not sure what to do next.

Upvotes: 30

Answers (15)

Tung Dang

Reputation: 1

I had the same problem. I ran an index defrag process but the transaction log became full and the defrag process errored out. The transaction log remained large.

I backed up the transaction log then proceeded to shrink the transaction log .ldf file. However the transaction log did not shrink at all.

I then issued a "CHECKPOINT" followed by "DBCC DROPCLEANBUFFER" and was able to shrink the transaction log .ldf file thereafter

Upvotes: 0

Russ

Reputation: 21

I know this is a few years old, but wanted to add some info.

I found on very large logs, specifically when the DB was not set to backup transaction logs (logs were very big), the first backup of the logs would not set log_reuse_wait_desc to nothing but leave the status as still backing up. This would block the shrink. Running the backup a second time properly reset the log_reuse_wait_desc to NOTHING, allowing the shrink to process.

Upvotes: 2

Jordan Hudson

Reputation: 1201

Thank you to everyone for answering.

We finally found the issue. In sys.databases, log_reuse_wait_desc was equal to 'replication'. Apparently this means something to the effect of SQL Server waiting for a replication task to finish before it can reuse the log space.

Replication ~~has never been used on this DB or this server~~ was toyed with once upon a time on this db. We cleared the incorrect state by running sp_removedbreplication. After we ran this, backup log and dbcc shrinkfile worked just fine.

Definitely one for the bag-of-tricks.

Sources:

http://social.technet.microsoft.com/Forums/pt-BR/sqlreplication/thread/34ab68ad-706d-43c4-8def-38c09e3bfc3b

http://www.eggheadcafe.com/conversation.aspx?messageid=34020486&threadid=33890705

Upvotes: 49

user3386130

Reputation: 7

I tried all the solutions listed and none of them worked. I ended up having to do a sp_detach_db, then deleting the ldf file and re-attaching the database forcing it to create a new ldf file. That worked.

Upvotes: -1

Legend

Reputation: 117000

This answer has been lifted from here and is posted here in case the other thread gets deleted:

The fact that you have non-distributed LSN in the log is the problem. I have seen this once before not sure why we dont unmark the transaction as replicated. We will investigate this internally. You can execute the following command to unmark the transaction as replicated

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

At this point you should be able to truncate the log.

Upvotes: 3

MrEs

Reputation: 725

'sp_removedbreplication' didn't solve the issue for me as SQL just returned saying that the Database wasn't part of a replication...

I found my answer here:

Basically I had to create a replication, reset all of the replication pointers to Zero; then delete the replication I had just made. i.e.

Execute SP_ReplicationDbOption {DBName},Publish,true,1
GO
Execute sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
GO
DBCC ShrinkFile({LogFileName},0)
GO
Execute SP_ReplicationDbOption {DBName},Publish,false,1
GO

Upvotes: 7

MikeJ

Reputation: 14565

If you set the recovery mode on the database in 2005 (don't know for pre-2005) it will drop the log file all together and then you can put it back in full recovery mode to restart/recreate the logfile. We ran into this with SQL 2005 express in that we couldn't get near the 4GB limit with data until we changed the recovery mode.

Upvotes: 1

Brent

Reputation: 21

I've had the same issue in the past. Normally a shrink and a trn backup need to occur multiple times. In extreme cases I set the DB to "Simple" recovery and then run a shrink operation on the log file. That always works for me. However recently I had a situation where that would not work. The issue was caused by a long running query that did not complete, so any attempts to shrink were useless until I could kill that process then run my shrink operations. We are talking a log file that grew to 60 GB and is now shrunk to 500 MB.

Remember, as soon as you change from FULL to Simple recovery mode and do the shrink, dont forget to set it back to FULL. Then immediately afterward you must do a FULL DB backup.

Upvotes: 2

Nick Kavadias

Reputation: 7358

You may run into this problem if your database is set to autogrow the log & you end up with lots of virtual log files.
Run DBCC LOGINFO('databasename') & look at the last entry, if this is a 2 then your log file wont shrink. Unlike data files virtual log files cannot be moved around inside the log file.

You will need to run BACKUP LOG and DBCC SHRINKFILE several times to get the log file to shrink.

For extra bonus points run DBBC LOGINFO in between log & shirks

Upvotes: 40

JP Alioto

Reputation: 45127

You cannot shrink a transaction log smaller than its initially created size.

Upvotes: -1

Guy

Reputation: 9816

Put the DB back into Full mode, run the transaction log backup (not just a full backup) and then the shrink.

After it's shrunk, you can put the DB back into simple mode and it txn log will stay the same size.

Upvotes: 0

Irina C

Reputation: 365

Try to use target size you need insted of TRUNCATEONLY in DBCC:

DBCC SHRINKFILE ('Wxlog0', 1)

And check this to articles:

http://msdn.microsoft.com/en-us/library/ms189493(SQL.90).aspx

http://support.microsoft.com/kb/907511

Edit:

You can try to move allocated pages to the beginning of the log file first with

DBCC SHRINKFILE ('Wxlog0', NOTRUNCATE)

and after that

DBCC SHRINKFILE ('Wxlog0', 1)

Upvotes: 0

Matt

Reputation: 5172

Try creating another full backup after you backup the log w/ truncate_only (IIRC you should do this anyway to maintain the log chain). In simple recovery mode, your log shouldn't grow much anyway since it's effectively truncated after every transaction. Then try specifying the size you want the logfile to be, e.g.

-- shrink log file to c. 1 GB
DBCC SHRINKFILE (Wxlog0, 1000);

The TRUNCATEONLY option doesn't rearrange the pages inside the log file, so you might have an active page at the "end" of your file, which could prevent it from being shrunk.

You can also use DBCC SQLPERF(LOGSPACE) to make sure that there really is space in the log file to be freed.

Upvotes: 0