I’ve seen a lot of question about how to truncate Sql Server log file, so I decide to write this post.
Using SQL Server Management Studio (SSMS)
First of all you need to switch to simple recovery model Select you database :
Right click on it and select properties:
Go to the options and change the recovery model to simple and click ok.
Now let’s truncate the log file to section Tasks -> Shrink -> Files.
In File type select Log and click ok.
Now you can check the size of your file
Using Transact SQL
You can you achieve the same results using Trasact SQL script :
DECLARE @SQL nvarchar(100)
DECLARE @DBName varchar(60)
DECLARE @LogName varchar(60)
DECLARE @recovery_model varchar(60)
SELECT @DBName= DB_NAME()
SELECT @LogName= name
FROM sys.master_files
Where db_id(@DBName) = database_id and type_desc = 'LOG'
SELECT @recovery_model = recovery_model_desc
FROM sys.databases
WHERE name = @DBName
IF @recovery_model <> 'SIMPLE'
BEGIN
SET @SQL = N'ALTER DATABASE '+ @DBName+ N' SET RECOVERY SIMPLE;';
exec sp_executesql @SQL
END
DBCC SHRINKFILE( @LogName , 2);
IF @recovery_model <> 'SIMPLE'
BEGIN
IF @recovery_model = 'FULL'
BEGIN
SET @SQL = N'ALTER DATABASE '+ @DBName+ N' SET RECOVERY FULL;';
exec sp_executesql @SQL
END
ELSE
BEGIN
SET @SQL = N'ALTER DATABASE '+ @DBName+ N' SET RECOVERY BULK_LOGGED;'
exec sp_executesql @SQL
END
END /*IF*/
That’s it see you soon!.