How do I truncate the log file of a SQL Server database

  1. Home
  2. Knowledge Base
  3. Softalk
  4. Softalk Share Server
  5. How do I truncate the log file of a SQL Server database

Symptoms

The SQL Server log file has grown to a large size and needs to be truncated,

Summary

The size of the log will continue to grow on a SQL Server database. The file may be truncated using the following procedure.

Detail

On a SQL Server Express database, download and install SQL Server Management Studio Express and then open it. Connect to the SQL Server instance (usually ComputerNameSQLEXPRESS).

Expand Databases on the left and select the Softalk Business Server database.

Click on the New Query button under the menu bar.

In the new query paste the following:

dbcc shrinkfile(SoftalkBusinessServer_log,1)
backup log SoftalkBusinessServer with truncate_only
dbcc shrinkfile(SoftalkBusinessServer_log,1)

Click on the Execute button. SQL Server will truncate the log file.

On a SQL Server database (not Express) the same procedure as above may be followed but using SQL Server Management Studio. Alternatively, if the recovery mode is not set to simple, the following command may be used to back up the log file before truncating:

dbcc shrinkfile(SoftalkBusinessServer_log,1)

GO

BACKUP LOG SoftalkBusinessServer

TO DISK = 'c:backupsssbackup.bak'

GO

dbcc shrinkfile(SoftalkBusinessServer_log,1)

GO

In SQL Server the above command may also be added as a SQL Server Agent task so that it is executed every couple of days.

Please see the SQL Server documentation for more information.

Was this article helpful?

Related Articles