Truncate Log on a SQL Server 2012 Database

I have a SQL Server 2012 DB in Full recovery mode that has never had a TLOG backup.   the log is huge 200+GB.

I tried doing a transaction log backup but there is not enough space on the Disk.    

How can I reclaim this log space in SQL Server 2012?
itsonlyme4Asked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
Easiest is to change recovery to SIMPLE -- so you don't have to take a log backup -- then do a CHECKPOINT(s).  You go back to FULL later if you want to.

USE <db_name>
ALTER DATABASE <db_name> SET RECOVERY SIMPLE
CHECKPOINT
DBCC SHRINKFILE ( 2, 4096 ) --leave 4GB in the log file
0
 
Russ SuterCommented:
One way to do it would be to change the backup method to "Simple" then shrink the file.

ALTER DATABASE mydatabase SET RECOVERY SIMPLE
 DBCC SHRINKFILE (mydatabase_Log, 1)

Open in new window


The only thing is that might also require disk space.

You could create a new database, copy the schema, then import the data, then delete the old database. That'll be a pain but it would work with minimal disk space requirements.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.