SQL SERVER - Everything About Shrink Log File with Examples.

How to truncate and shrink Transaction Log file in SQL Server in 2000/2005/2008

Note: Please read the complete post before taking any actions.

This article has covered following Questions.

Question What is shrinking?

Question When we should perform the shrink operation on T-log file?

Question How to perform shrinking in different version of SQL Server?

Question How to Shrink all database in Single Instance?

Question Why avoided shrink operation?

I would like to inform you that there is not need of all truncation of transaction log is not a right practice. But generally most of the person is avoid it. So that you should always use the right backup and recovery model to your database. If it’s happen than you can easily know the problem and cause and you can prevent your transactional log file size issue.

Shrinking is the method which used for the remove unused space form the Log file and reduce the size of file.

When the space is limited and there is no space available in the disk at that time we can perform the shrink operation. When the Transactional log file is not manageable then you can perform the shrink operation.

I have to step to perform this operation-

1) First of all you can get the inactive part of Trasaction log.
2) Than release the got portion of Transaction log to the System

There are two way to do the same task-

A) Using Query (Write Script)

B) Using Wizard (Window Dialog)

Using Query (Write Script)

SQL Server 2005

Step 1 get the inactive part of Trasaction log.

Use <database name>
Go
Backup Log <database name> With Truncate_Only
GO

Step 2 Than release the got portion of Transaction log to the System


Declare @ T_LogFileName as varchar
select @ T_LogFileName =Name from sys.database_files where Type=1

DBCC Shrinkfile(@T_LogFileName,100)

Note : In the above script Type=1 means It will work with only one Log file. Now I think you are thinking about How to perform shrink operation on multiple Log file. There are I am showing how can perform this task in SQL Server 2000

SQl Server 2000

Step 1  Get the inactive part of Trasaction log

Use <database name>
Go
Backup Log <database name> With Truncate_Only
GO

Step 2  Than release the got portion of Transaction log to the System

Declare <@T_LogFileName> sysname
select @ T_LogFileName =Name from sysfiles where filename like '%.ldf'

DBCC Shrinkfile(@T_LogFileName,100)


SQL Server 2008

You have to change the recovery model to simple and then you can perform the DBCC ShrinkFile task.

Syntax:

Select name,recovery_model_desc from sys.databases
GO
Alter database <databasename> Recovery simple
GO
Declare @ T_LogFileName sysname
select @ T_LogFileName =Name from sys.database_files where Type=1
print @T_LogFileName

DBCC Shrinkfile(@T_LogFileName,100)

Using Wizard (Window Dialog)

It is very easy. You have to follow below some simple step.

1. Right click on the database
2. Go to the ‘Task’ Menu
3. Than go to the ‘Shrink’ meun. Here you will get two option ‘Database’ and ‘File’ Click on you option #
4. Than you will find new ‘Shrink Database Wizard’ with details
5. Here you can set the Maximum free space in files after shrinking

Click on ‘Ok’ button

Snap Shot:

Question How to Shrink all database in Single Instance?

EXEC sp_MSForEachDB 'DBCC SHRINKDATABASE (''?'' , 0)'

Question Why avoided shrink operation?

It’s slow down the performance of the database.

Warning:  Shrink operation perform when it’s needful for you database other wise avoid it because it’s slow down the performance of the database.

I hope this article help to you…. Thanks