Shrink log file size of Database

There are fixed boundaries from which a transaction log file can be shrunk. The size of the virtual log files within the log determines the possible reductions in size. Therefore, the log file can never be shrunk to a size less than the virtual log file. Also, the log file is reduced in increments equal to the size of the virtual log file size. For example, a transaction log file of 1 GB can consist of five virtual log files of 200 MB each. Shrinking the transaction log file deletes unused virtual log files, but leaves at least two virtual log files. Because each virtual log file in this example is 200 MB, the transaction log can be reduced only to a minimum of 400 MB and only in increments of 200 MB. To be able to reduce a transaction log file to a smaller size, create a small transaction log and let it grow automatically, instead of creating a large transaction log file all at once.

A DBCC SHRINKDATABASE or DBCC SHRINKFILE operation immediately tries to reduce a transaction log file to the requested size (subject to rounding). You should backup the log file before shrinking the file to reduce the size of the logical log and mark as inactive the virtual logs that do not hold any part of the logical log. For more information, see Shrinking the Transaction Log.

Best Practices
Consider the following information when you plan to shrink a database or file:
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 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.
A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. For example, you should not shrink a database or data file after rebuilding indexes. This is another reason not to repeatedly shrink the database.
Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.
These are the steps to create log.
Run below query to check log size of all databases whose log Size more than 1 GB

select DB_Name(A.database_id) as DBName, 
A.name,A.type_desc,(A.size*8/1024) as size_in_MB,
(select primary_database  from msdb.dbo.log_shipping_Primary_databases
where primary_database=DB_Name(A.database_id)) as LogShipping_DB,
'Mirror DB'= case
WHEN B.mirroring_guid is not null then 'Mirroring DB'
else ''
end
from sys.master_files as A, sys.database_mirroring as B
where A.database_id=B.database_id
and A.type_desc='LOG' and (A.size*8/1024)>1024

Note – In above query result, you will also get Databases who are involved in mirroring session. So ignore mirror Database.

Shrink log size using log truncation .

If you are using Sql Server 2005 then run below query –

• First Take log backup
  

  Backup Log Database_Name
  To Disk='L:\Tlog_Shrink\DBName_Log.trn'

• Now truncate the log of DB using below T-Sql Command -
  

  Backup log  Database_Name
  To Disk='D:\DB_Backup\DBNAME_log.trn'
  With truncate_only
  

  Note - Above command will truncate the log but will not create any log backup file on disk. After running this command, you can not take log backup becoz log truncation break the the LSN of log. Now if you want to take log backup of DB, first you need to take Full or Differential backup then log backup will start again. If you will not take full or Differential backup after truncation of log of DB then you will not able to recover data to point-of-failure.

• Now shrink log file size using below T-Sql Command -
  

  USE   Database_Name              
  DBCC Shrinkfile('logical name of Log file',Size in MB)

• Now check the log size of DB using below T-Sql Command -
  

  DBCC SQLPERF(logspace)
  


• Take full Backup – After log truncation you need to take full backup to start log backup again or to get data to point-of-failure.
  

  Backup Database Database_Name
  To Disk='L:\Tlog_Shrink\DBName_Full.Bak'
  

If you are using Sql Server 2008 then run below query –

• First Take log backup
  

  Backup Log 
  To Disk='L:\Tlog_Shrink\DBName_Log.trn'
  

• Truncate the log by changing the database recovery model to SIMPLE
  

  USE Database_Name
  ALTER DATABASE Database_Name
  SET RECOVERY SIMPLE;
  GO

• Shrink the truncated log file to 1 MB.
  

  DBCC SHRINKFILE ('Log file Name', 1);
  GO

• Reset the database recovery model to FULL
  

  ALTER DATABASE Database_Name
  SET RECOVERY FULL;
  GO
  

• Now check the log size of DB using below T-Sql Command -
  

  DBCC SQLPERF(logspace)
  

• Take full Backup
  

  Backup Database Database_Name
  To Disk='L:\Tlog_Shrink\DBName_Full.Bak'