Tips to Shrink SQL Server Log Files

Analyze the log size of the databases present in SQL Server:

dbcc sqlperf (logspace );

Output:

Database Name   Log Size(MB) Log space Used(%)  Status
--------------  -----------  -----------------  -------
AdventureWorks  813.245      71.3443535         0
TEST              0.5        79.1235678         0

It show the log size of all databases Present in SQL Server instance.

Logspace is growing large because of recovery mode defines.
Recover model is simple then once checkpoint is occurred then it truncate the logs. No point in time recovery at simple recovery.
Recover model is bulk-logged then at checkpoint occurred, its not truncated the transaction logging.
Recover model is full then every transaction is full logged and transaction is not truncated
Note:Another way is to take backup of transaction log otherwise it becomes larger or larger in size

Check the Status of Log transaction status

select name , log_reuse_wait_desc from sys.databases;

Output:

NAME             LOG_RESUE_WAIT_DESC
-------------    --------------------
master		 NOTHING
AdventureWorks   LOG_BACKUP
TEST             ACTIVE_TRANSACTION

LOG BACKUP values show need to be backup the transactional log for adventureworks database otherwise its continue grow until you take a transaction backup.

You can take the transaction log backup with right click on database in SQL Server Management Studio.

In TEST database having active transaction it finished after checkpoint issue.

Manually fire the checkpoint on ADVENTUREWORKS & TEST database

use ADVENTUREWORKS
Checkpoint

use TEST
Checkpoint

Check the status of log transaction again:

select name , log_reuse_wait_desc from sys.databases;

name             log_Resue_wait_desc
-------------    --------------------
master           NOTHING
AdventureWorks   NOTHING
TEST             NOTHING

Check again the AdventureWorks transaction Log space

dbcc sqlperf (logspace );

Output:

Database Name   Log Size(MB) Log space Used(%)  Status
--------------  -----------  -----------------  -------
AdventureWorks  813.245      2.443535         0
TEST              0.5        79.1235678         0

Following are steps to shrink the Transaction Log file in File System of Operating system:

Note: Following steps follow only if you are not using production database.

alter database adventureworks set recovery simple;
Go
-- Shrink log file to 1 MB
DBCC shrinkfile (adventureworks_log,1);
Go
alter database adventureworks set recovery FULL;
GO

Note: If you are using production database then first take backup of LOG files and then shrink the log file with DBCC Shrinkfile