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

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Don t you need this  DBCC SHRINKFILE   Wxlog0   0    Just be sure that you are aware of the dangers  see here  Do not truncate your ldf files   And here  Backup Log with Truncate Only  Like a Bear Trap

User · Answer

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    http   www sql-server-performance com forum threads log-file-fails-to-truncate 25410  http   blogs msdn com b sqlserverfaq archive 2009 06 01 size-of-the-transaction-log-increasing-and-cannot-be-truncated-or-shrinked-due-to-snapshot-replication aspx   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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 amp threadid 33890705

User · Answer

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

User · Answer

Have you tried from within SQL Server management studio with the GUI  Right click on the database  tasks  shrink  files  Select filetype Log    I worked for me a week ago

User · Answer

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

User · Answer

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

[sql-server] Why can't I shrink a transaction log file, even after backup?

Examples related to sql-server

Examples related to sql-server-2005