SQL 2014 Truncate Log File
SQL 2014 Truncate Log File
(OP)
I have reviewed many of the posts her on truncating the log file.
I would like to completely remove the present log file on a production DB.
I have tried backing up the log but it fails, the DB back ups OK.
A checkdb on the DB shows no errors.
I can access the DB at night when nobody is using it.
I have seen that detaching, renaming the log file, and attaching the Db will force a new log file, but I have also seen warnings against this.
What is the safest and best ways to do this?
Sorry, I know this has been discussed many times but I want to make sure I do it correctly.
I would like to completely remove the present log file on a production DB.
I have tried backing up the log but it fails, the DB back ups OK.
A checkdb on the DB shows no errors.
I can access the DB at night when nobody is using it.
I have seen that detaching, renaming the log file, and attaching the Db will force a new log file, but I have also seen warnings against this.
What is the safest and best ways to do this?
Sorry, I know this has been discussed many times but I want to make sure I do it correctly.
Auguy
Sylvania/Toledo Ohio
Talk To Other Members
RE: SQL 2014 Truncate Log File
Borislav Borissov
VFP9 SP2, SQL Server
RE: SQL 2014 Truncate Log File
I'm going with this example as a template.
It worked on my test DB after I made a change.
For anyone using this code the Shrinkfile didn't work and gave me an error
Check your logical file name for the log file, mine was different than the DB name.
Shrinkfile worked once the correct logical file name was used.
select * from sys.database_files to get the logical name.
USE AdventureWorks2012;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2012
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL;
GO
Auguy
Sylvania/Toledo Ohio