Monday, March 7, 2011

To solve problem on SQL Server : truncate_only' is not a recognized BACKUP option

One day i tried to shrink my log file on sql server, but the file xxx_log ( file.ldf –physical file ) still on huge size. Previous script that i use :
BACKUP LOG DATABASE WITH TRUNCATE_ONLY


And i get this issue : truncate_only' is not a recognized BACKUP option


This issue happen on SQL Server 2008, and after i run this scripts it work for me, hope it will work with you.





-- this script is work on sql server 2008, it work for me
-- Use sp_helpfile to identify the logical file
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.
-- the log file name (dbname_log)
DBCC SHRINKFILE (DatabaseName_log , 1);  
GO
-- Reset the database recovery model.
ALTER DATABASE DatabaseName
SET RECOVERY FULL;
GO


No comments: