Truncate local SQL Server database log file to save disk space

Possibly save 1 hour of your time: I notice my machine disk space is running low. So where can I save some space? Database log is usually a big one.

First, download a free program call WindirStat. This program will tell you in a very nice visual way of where most of your space are taken up.

So I found out from my machine there are two database log files in which both took up over 50 GB.

Solution: I backup the database first. Then I ran the following command on the specific database in SQL Server Management Studio 2012.

select * from sys.database_files --find the name of the log file

ALTER DATABASE your_database_name SET RECOVERY SIMPLE
DBCC SHRINKFILE('name_of_log', 0, TRUNCATEONLY)
ALTER DATABASE your_database_name SET RECOVERY FULL

Murach’s SQL Server 2016 for Developers

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s