SQL Log File Growing Too Large
Our Microsoft SQL Log file was growing consistently to very large sizes. The log file would quickly eat up our hard drive space and start to cause performance issues with our web sites. To fix the performance issue the size of the log file needed to be shrunk. Using the shrink DB option in SQL Manager did nothing to the physical size of the log file sitting on the server. After some searching on google I found the following SQL script which truncated the log file and dropped the size of the SQL Log file to 1mb.
As a safety measure, make sure you run a full backup of the database before running the script below!
USE databaseName;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE databaseName
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (databaseName_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE databaseName
SET RECOVERY FULL;
GO
