SQL 2014 Truncate Log File

Auguy · Jul 13, 2020

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.

Auguy
Sylvania/Toledo Ohio

bborissov · Jul 13, 2020

https://docs.microsoft.com/en-us/sq...shrinkfile-transact-sql?view=sql-server-ver15

Borislav Borissov
VFP9 SP2, SQL Server

Auguy · Jul 13, 2020

Thanks I will check it out.

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

SQL 2014 Truncate Log File

Auguy

Programmer

bborissov

Programmer

Auguy

Programmer

Similar threads

Part and Inventory Search

Sponsor