DBCC SHRINKFILE on log file not reducing size even after BACKUP LOG TO DISK

Question

I ve got a database   My DB   that has the following info  SQL Server 2008 MDF size  30 GB LDF size  67 GB    I wanted to shrink the log file as much as possible and so I started my quest to figure out how to do this   Caveat  I am not a DBA  or even approaching a DBA  and have been progressing by feel through this quest   First  I just went into SSMS  DB properties  Files  and edited the Initial Size  MB  value to 10   That reduced the log file to 62 GB  not exactly the 10 MB that I entered    So  I attached SQL Profiler  saw that DBCC SHRINKFILE was being called   I then entered that command into the query editor and here s the results   DBCC SHRINKFILE  N My DB Log    10    And the output was   Cannot shrink log file 2  My DB Log  because the logical log file located at the end of the file is in use  DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages ------ ----------- ----------- ----------- ----------- -------------- 8      2           8044104     12800       8044104     12800   1 row s  affected   DBCC execution completed  If DBCC printed error messages  contact your system administrator    I then did some research on that and found this   http   support microsoft com kb 907511  Which says that I need to backup the log file before the shrinkfile so that the virtual log files will be released and the shrinkfile can do its job - I don t know what that means    I m just paraphrasing here     So  I figured I d try to backup the log file and then do a DBCC SHRINKFILE  and I changed the new log file size to 12800 since that was the MinimumSize identified in the output of the previous DBCC SHRINKFILE command   BACKUP LOG  My DB  TO DISK    D  SQLBackup 20110824-MyDB-Log bak  GO DBCC SHRINKFILE  N My DB Log    12800  GO   The result was the same as the first go around   I can only get the log file down to 62 GB   I m not sure what I m doing wrong and what I should try next

User · Answer

I resolved this problem by taking the full and transactional backup  Sometimes  the backup process is not completed and that s one of the reason the  ldf file is not getting shrink  Try this  It worked for me

User · Answer

Paul Randal has an exccellent discussion of this problem on his blog  http   www sqlskills com blogs paul post backup-log-with-no log-use-abuse-and-undocumented-trace-flags-to-stop-it aspx

User · Answer

I use this script on sql server 2008 R2   USE  db name   ALTER DATABASE  db name  SET RECOVERY SIMPLE WITH NO WAIT  DBCC SHRINKFILE  log file name  log file number  wanted size   ALTER DATABASE  db name  SET RECOVERY FULL WITH NO WAIT

User · Answer

Try this  ALTER DATABASE XXXX  SET RECOVERY SIMPLE  use XXXX  declare  log File Name varchar 200    select  log File Name    name from sysfiles where filename like   LDF   declare  i int   FILE IDEX    log File Name   dbcc shrinkfile    i   50

User · Answer

In addition to the steps you have already taken  you will need to set the recovery mode to simple before you can shrink the log    THIS IS NOT A RECOMMENDED PRACTICE for production systems    You will lose your ability to recover to a point in time from previous backups log files    See example B on this DBCC SHRINKFILE  Transact-SQL  msdn page for an example  and explanation

User · Answer

Thanks to  user2630576 and  Ed S   the following worked a treat   BACKUP LOG  database  TO DISK    D  database bak  GO  ALTER DATABASE  database  SET RECOVERY SIMPLE  use  database   declare  log File Name varchar 200   select  log File Name   name from sysfiles where filename like   LDF   declare  i int   FILE IDEX    log File Name   dbcc shrinkfile    i   50   ALTER DATABASE  database  SET RECOVERY FULL

User · Answer

I tried many ways but this works   Sample code is availalbe in DBCC SHRINKFILE  USE DBName    GO   -- Truncate the log by changing the database recovery model to SIMPLE    ALTER DATABASE DBName   SET RECOVERY SIMPLE    GO   -- Shrink the truncated log file to 1 MB    DBCC SHRINKFILE  DBName log  1    --File name SELECT   FROM sys database files  query to get the file name GO   -- Reset the database recovery model    ALTER DATABASE DBName   SET RECOVERY FULL    GO

User · Answer

Okay  here is a solution to reduce the physical size of the transaction file  but without changing the recovery mode to simple   Within your database  locate the file id of the log file using the following query   SELECT   FROM sys database files    In my instance  the log file is file id 2  Now we want to locate the virtual logs in use  and do this with the following command   DBCC LOGINFO    Here you can see if any virtual logs are in use by seeing if the status is 2  in use   or 0  free   When shrinking files  empty virtual logs are physically removed starting at the end of the file until it hits the first used status  This is why shrinking a transaction log file sometimes shrinks it part way but does not remove all free virtual logs   If you notice a status 2 s that occur after 0 s  this is blocking the shrink from fully shrinking the file  To get around this do another transaction log backup  and immediately run these commands  supplying the file id found above  and the size you would like your log file to be reduced to   -- DBCC SHRINKFILE  file id  LogSize MB  DBCC SHRINKFILE  2  100   DBCC LOGINFO    This will then show the virtual log file allocation  and hopefully you ll notice that it s been reduced somewhat  Because virtual log files are not always allocated in order  you may have to backup the transaction log a couple of times and run this last query again  but I can normally shrink it down within a backup or two

[sql-server] DBCC SHRINKFILE on log file not reducing size even after BACKUP LOG TO DISK

Examples related to sql-server

Examples related to backup

Examples related to transaction-log

Examples related to dbcc