There have been some changes since sql server 2005 and the backup log with truncate no longer works. So here is a little stored procedure to shrink the current database log file for mssql 2008. I also created this in such a way so that it will run for any database.
CREATE PROCEDURE ShrinkLog
AS
BEGIN
DECLARE @DBName nvarchar(MAX)
DECLARE @DBLog nvarchar(MAX)
DECLARE @SQL nvarchar(MAX)
SELECT @DBName = DB_NAME()
SELECT @DBLog = name
FROM sys.master_files
WHERE database_id = DB_ID()
AND type = 1
SELECT @SQL = 'ALTER DATABASE ' + @DBName + ' SET RECOVERY SIMPLE'
EXEC sp_executesql @SQL
SELECT @SQL = 'DBCC SHRINKFILE(' + @DBLog + ')'
EXEC sp_executesql @SQL
SELECT @SQL = 'ALTER DATABASE ' + @DBName + ' SET RECOVERY FULL'
EXEC sp_executesql @SQL
END
GO
You can use the following sql to run it
EXEC ShrinkLog
Did You find this page useful?
Yes
No
Last Modified: 18 February 2017
Releated Posts
2012-08-07 - MSSQL - Finding the database restore history
2011-09-24 - MSSQL - Checking Uptime
2011-08-11 - MSSQL - Saving memory by using stored procedures
2011-08-09 - MSSQL - Last Backup time and size
2011-06-24 - MSSQL - Extracting part of an email address
2011-06-15 - MSSQL - Bulk Deleting rows with backup
2011-06-07 - MSSQL - DBCC Check All Databases
2011-06-03 - MSSQL - Who locks what
2011-05-26 - MSSQL - RandomString Function
2011-05-22 - MSSQL - Removing the aspnet membership database
2011-05-19 - MSSQL - ShrinkLog
2011-05-16 - MSSQL - Enum
2011-05-05 - MSSQL - Log Sizes
2011-03-01 - MSSQL - Kill connections by host
2011-02-22 - MSSQL - Kill connections by username
2011-02-20 - MSSQL - Unsigned int
2011-02-20 - MSSQL - All temp tables are global
2011-02-17 - MSSQL - Convert IP To big int
2011-02-05 - MSSQL - Levenshtein
2011-02-02 - MSSQL - Kill connections to a database
2011-02-01 - MSSQL - Convert unix timestamp to date time
2011-01-30 - MSSQL - Adding SHASum support
2011-01-30 - MSSQL - Enabling CLR Functions
2011-01-30 - MSSQL - RegEx Support
2011-01-30 - MSSQL - Adding MD5 checksum support
2011-01-29 - MSSQL - TRIM Function