1

Working on building a big SQL Server database, I noticed my C drive is running out of space when running heavy SQL queries.

The following folder size is over 150GB, log.ldf files reach over 50GB.

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

The DB is still under development and I don't need any transaction logs or stuff like that.

How can I safely clear those log files?

Improve this question
4
  • Quick and dirty way is to right-click the database in SSMS, Tasks > Shrink > Files. Select 'File Type' of Log and ensure the 'Release Unused Space' radio button is set. Click OK and watch the log shrink.
    – spikey_richie
    Jan 3, 2019 at 16:07
  • thanks a lot! but i curious why it 'dirty' way?
    – r.tom
    Jan 3, 2019 at 16:26
  • Because it assumes the log file is effectively empty. It also doesn't deal with a log file that has data at either end of the file on disk, in which case you'd want to use Reorganize instead of Release
    – spikey_richie
    Jan 4, 2019 at 11:02
  • Bunch of things to recommend and ask here. First, is this one database or many? Second, are they in Full recovery mode or Simple? You'd probably be happier with simple for now. Next, I strongly urge you to move at least your user databases off of C drive, and over to a single-purpose partition that only holds user databases. Finally, I highly recommend that you go to brentozar.com and download his Blitz server analysis tool and work through the recommendations that it will provide you. You likely can gain quite a bit from the relatively simple tweaks it will report.
    – Rob Pearson
    Jan 29, 2019 at 20:16

1 Answer 1

Reset to default
4

You can truncate the log file by issuing the following code block from SSMS:

-- 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

You can check the full documentation at this MS DOCS page

There's is more information regarding this topic but I'm trying to address your question with the details you're providing. Hope it helps!

Improve this answer
1

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .