none
Unable to shrink log file

    Question

  • I am working with a small database. Data file is size 2 MB. But, the log file size is 11 MB. The database is in simple recovery mode. The version is SQL Server 2008 R2 64-bit standard. I am unable to shrink the log file to something lower than 11 MB. I am using the statement, DBCC SHRINKFILE (dblog1);

    I have also used the statement, DBCC SHRINKFILE (dblog1, 10);

    I would like any guidance on why the log refuses to shrink.
     

    The following t-sql is used to view the space used:

     SELECT  
    ds.name as filegroupname
    , df.name AS 'FileName' 
    , physical_name AS 'PhysicalName'
    , size/128 AS 'TotalSizeinMB'
    , CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0 AS 'ActualSpaceUsedInMB'
    , size/128.0 - CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0 AS 'AvailableSpaceInMB' 
    , (CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0)/(size/128)*100. as '%SpaceUsed'
    FROM sys.database_files df LEFT OUTER JOIN sys.data_spaces ds  
            ON df.data_space_id = ds.data_space_id;

    Thank you.

    +ive


    Thursday, April 07, 2011 9:36 PM

All replies

  • Have you ever tried to use "DBCC ShrinkFile (DbLog1, TruncateOnly)" Or Try to ShrinkDatabase first.

    Use Master 
    Go
    DBCC ShrinkDatabase ('DB1')
    

    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    Thursday, April 07, 2011 9:49 PM
  • One more thing, Keep in mind that If you enable CDC (Change Data Capture) on your database, you CANNOT Shrink your database log.
    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    Thursday, April 07, 2011 10:06 PM
  • Hi,

    When you used the above query, did the log file show free space available? Also note that the Transaction Log can only be shrunk to a virtual log boundary, which is dynamic based on the transaction log size. To shrink beyond the initial size you have to specify the size like DBCC SHRINKFILE (dblog1, 10). Looks like you have already tried that.

    http://msdn.microsoft.com/en-us/library/ms189493.aspx

    On a side note, the size of the database and transaction log are very very very small to a SQL Server 2008 R2 64 bit standard edition. Ideally you want to pre-allocate the files to a large size so such the files won't auto-grow for at-least in the near future. Too many auto-grows of small increments can be very bad for performance.


    --Sankar Reddy

    Blog: http://SankarReddy.com/
    Twitter: http://twitter.com/SankarReddy13/
    Thursday, April 07, 2011 10:43 PM
    Moderator
  • Try to run DBCC loginfo that will show you the virtual logs for the transaction log file. Check the status column and if it is 2 that menas it is in use and 0 means its is free. Then you can find out the transaction that is holding the space in the virtual logs.

     


    Cheers!! Kalps
    Friday, April 08, 2011 6:37 AM
  • Bunch of details (including shrink of log, DBCC LOGINFO, how to do it etc) in here: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
    Tibor Karaszi, SQL Server MVP | web | blog
    Friday, April 08, 2011 6:52 AM
    Moderator
  • run dbcc open tran and se eif there are any active transactions . Let the transactions complete or roll them back .take database in to singleuser mode . Once done , run the dbcc log info and see if you get any 2s in the status (specially at the bottom) . If not then try shrinking again .
    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Saturday, April 09, 2011 7:53 AM
  • If u want to reduce the log file space. Just try  like the following, Right click that particular database and go to tasks and select shrink-> file. or take a backup of log file then it ill trucate the committed tran and the log size ill b reduced. 
    Wednesday, April 13, 2011 8:50 AM
  • Hi

    BACKUP won't truncate log size, you need DBCC SHRINKFILE to run after taking BACKUP LOG file


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, April 13, 2011 9:05 AM
    Answerer
  • Hi

    BACKUP won't truncate log size, you need DBCC SHRINKFILE to run after taking BACKUP LOG file


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, April 13, 2011 9:05 AM
    Answerer
  • All the above approaches are true.

    Use DBCC LOGINFO to get vlf's status as said by kalpana and find why the logfiles status is 2 by looking in to open tran.

    if u find no open tran andthe status of  vlf will be obviously zero. Once you see that open log file preoperties of the database and increase the size of log file to add more vlfs and try a shrink option that will clear your log for sure.

     

    Thanks,


    Thanks, Aditya Badramraju, ____________________________________________________ Please mark answered if your question is completely answered; vote it as helpful to help others find a solution quicker. This posting is provided AS IS with no warranties and confers no rights.
    Wednesday, April 13, 2011 11:46 AM