HOWTO: 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.
DETAILS
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 ComputerName\SQLEXPRESS).
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:\backup\sssbackup.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.
APPLIES TO
- All versions