Many times the large size of transaction log file (.ldf) in Microsoft SQL is too big which leads to performance issues and loss of valuable disk space.Therefore it's imperative to periodically do database maintenance.
In order to clear or shrink the .ldf file, you should first take backup and then follow the below process:
USE AdventureWorks2008R2;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY FULL;
GO
Ref : https://msdn.microsoft.com/en-us/library/ms189493.aspx
0 Comment(s)