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)

  • 6 Users Found This Useful
Was this answer helpful?

Related Articles

Can I connect to my database from a website not hosted at DigiServ.biz?

No, we do not allow linking to your database from a website hosted elsewhere. This is in...

How do I connect to my database in my scripts?

To connect to your database from your scripts, use the connection information found on the...

How do I edit my MS SQL Database?

There are two ways to edit your MS SQL Database. A. You can connect to your databases online...

How do I edit my MySQL database?

There are two ways to edit your MySQL Database. A. You can connect to your databases online by...

I need to move my MSSQL or MySQL database over from another host

You will need to provide us with a MSSQL .bak file or all the MySQL database files from your old...