0

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?

Law29
  • 3,557
  • 1
  • 16
  • 28
r.tom
  • 1
  • 1
  • 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 03 '19 at 16:07
  • thanks a lot! but i curious why it 'dirty' way? – r.tom Jan 03 '19 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 04 '19 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 http://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 '19 at 20:16

1 Answers1

3

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!

Humberto Castellon
  • 879
  • 1
  • 7
  • 17
  • What Humberto said. Also, if you're using the database for development and "don't need your transaction log," leave it on simple recovery. [This article on simple vs. full recovery](https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-2017) might help. – Katherine Villyard Jan 03 '19 at 18:13