Saturday 30 July 2011

SHRINKFILE and TRUNCATE Log File in SQL Server 2008

Introduction

 

You know there is always an issue - the log file growing very fast and big.  If you have plenty of storage, then this might not be a problem for you.  Anyway, this is no exception in the latest version of SQL, we still have to do something to truncate and shrink these files.

 

Implementation

 

1)   Let’s first check the log file size.

 

SELECT

  --DB_NAME(database_id) AS DatabaseName,

  --Physical_Name,

  Name AS Logical_Name,

  (size*8)/1024 SizeMB

FROM

  sys.master_files

WHERE

  DB_NAME(database_id) = 'tempdb'

GO

 

Output

 

image001

 

2)   Now truncate the log file.

 

USE tempdb;

GO

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE tempdb

SET RECOVERY SIMPLE WITH NO_WAIT;

GO

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE(tempdb_log, 1);  --file_name is the logical name of the file to be shrink

GO

-- Reset the database recovery model.

ALTER DATABASE tempdb

SET RECOVERY FULL WITH NO_WAIT;

GO

 

3)   Let’s check the log file size.

 

SELECT

  --DB_NAME(database_id) AS DatabaseName,

  --Physical_Name,

  Name AS Logical_Name,

  (size*8)/1024 SizeMB

FROM

  sys.master_files

WHERE

  DB_NAME(database_id) = 'tempdb'

GO

 

Output

 

image002

 

Consider the following information when you plan to shrink a file:

 

  • Make a full backup of your database before shrink the database file.
  • From setting the database to simple recovery, shrinking the file and once again setting in full recovery, you are in fact losing your valuable log data and will be not able to restore point in time. Not only that, you will also not able to use subsequent log files.
  • A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
  • Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation. In this case, you should consider increasing the Growth Rate of your Database to keep the performance under control.
  • A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.

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

5 comments:

  1. How will i reduce logfile in sql server2008.
    The size of Logfile-86 GB
    The Size of Mdf file -356 MB
    Pls help

    ReplyDelete
  2. Hello Sanjiv,

    1. You can do it using TSQL as explained above.

    2. You can follow the below msdn link to shring file using SSMS -
    http://msdn.microsoft.com/en-us/library/ms190757.aspx

    3. You can also follow the below steps (if it's feasible in your environment) -
    a. Take FULL BACKUP of your DB.
    b. Detach the DB.
    c. Attach the same DB and while attaching REMOVE the log file.
    d. Rename the log file on the physical disk.
    e. And then proceed with Attaching.

    A new log file with default size will be generated. And then you can safely remove your re-named log file.

    Hope, this helps!


    ReplyDelete
  3. How to find logical name in following query.

    -- Shrink the truncated log file to 1 MB.
    DBCC SHRINKFILE(tempdb_log, 1); --file_name is the logical name of the file to be shrink
    GO

    Run below query, you will get logical name of log file.

    SELECT DB_NAME(database_id) AS DatabaseName,
    Name AS Logical_Name,
    Physical_Name, (size*8)/1024 SizeMB ,*
    FROM sys.master_files
    WHERE DB_NAME(database_id) = 'DatabaseName'

    ReplyDelete
  4. Saved me some time, couldntfind anything that worked and this did it.

    Thanks!

    ReplyDelete
  5. you can follow the steps as below:

    Always TAKE a FULL Back up first
    1. Take log backup

    2. Change the recovery model to simple

    3 CHECKPOINT

    3 CHECKPOINT

    Note: By issuing the checkpoint command when the database is in the simple recovery model will allow for the vlfs to be inactivated.

    3. Then run SHRINKFILE command to reduce to file size on disk.
    ==> DBCC SHRINKFILE(YourDB_log', 0)
    you can also run before and After followign command:
    DBCC sqlperf(logspace)
    4. Change back to full recovery model.
    ALTER DATABASE SET RECOVERY FULL;
    5. Then take full backup of database again.

    You can also take a FULL BAck up after Changing the Recovery Model to SIMPLE

    ReplyDelete