Client Area
All-in-one service management,
billing & support, conveniently & securely
in one single feature rich place.

Portal Home > Knowledgebase > Windows Hosting > Windows (Helm USA) > Databases > How do I reduce the size of my MS SQL Log Files?


How do I reduce the size of my MS SQL Log Files?




MS SQL Server Databases are set to Simple backup mode, so you shouldn't see large log files.  However, if you find a large amount of disk space is being taken up by your MS SQL Server log files, please use the following methods to truncate the size of your Microsoft SQL database transaction log file.

Step 1:
backup log [DATABASE NAME] with truncate_only

(this makes the oldest parts of the log file redundant, the size doesnt change at this point though)

Step 2:
dbcc shrinkfile([LOGICAL NAME OF LOG FILE IN DATABASE],X)

(X above is the size in megabytes that you wish to shrink the size of the log file to)

EXAMPLE:
Assume you had a database called "info" .

To get the appropriate information for this database (e.g. logical file name) load up SQL Enterprise manager.

Right click on the database and go properties > transaction log. The value listed under "filename" is the logical file name of the log file, and the location is the physical location on disk. Suppose for our test database "info" that the value under filename (in the properties > transaction log tab) is "info_log" and location is "C:\Program Files\Microsoft SQL Server\MSSQL\Data\info_log.ldf". Also assume you want to trim the log file to 5 Mb.

With the above information the commands you need to run would be:

backup log info with truncate_only

dbcc shrinkfile(info_log,5)



Was this answer helpful?

Add to Favourites Add to Favourites    Print this Article Print this Article

Also Read