CODEWITHSUNDEEP
sql-server
Loganj99
Loganj99

Reputation: 449

Truncate SQL Server transaction log file

In my local C: drive I have a ldf file for a database that is hosted on one of our servers. I have a local copy of one of the databases that is 1 gb and a ldf (log file) of that database that is 16gb. It is eating up a lot of my local space on my hard drive. I would like to truncate that file. A lot of what I read online is don't, but it seems that they are talking about files on the server that the database is on. That isn't the case here, it is on my local machine.

The location of the file on my machine is:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

How come I have a copy of that log file locally and also that database locally?

How would I go about truncating this file?

Thanks!

Upvotes: 17

Views: 61852

Answers (2)

Jason Clark
Jason Clark

Reputation: 1425

The another option you can try is to use WITH TRUNCATE_ONLY:

BACKUP LOG  databasename  WITH TRUNCATE_ONLY
DBCC SHRINKFILE (  adventureworks_Log, 1)

but don't try this option in live environment, the far better option is to set database in simple recovery. see the below command to do this:

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

Upvotes: 1

Nick Vaccaro
Nick Vaccaro

Reputation: 5504

Go to the Object Explorer pane in SSMS and right click on the database in question. Choose tasks -> shrink -> files. Change the file type option to Log, click the "Reorganize pages before releasing unused space" option, and set the value to 1 MB. Hit OK.

If this doesn't work, check to see if your database is set up with a Full database recover model. Right click the database and go to properties. Choose Options, and check the Recover model option. Set to simple (if you can!!!), then shrink the logs.

Upvotes: 36

Related Questions