Home MS SQL Server DB Maintenance Backup & Truncate MSDB database in SQL Server
Backup & Truncate MSDB database in SQL Server E-mail
Written by Administrator   
Monday, 29 December 2008 13:38
USE MSDB 
--get the file IDs.
Select * from sysfiles
--This gives me the file IDs
--The LDF file is 2.

--backup the database.
BACKUP DATABASE MSDB TO DISK = 'D:\SQL2K5DDUMP\MSDB.BAK'
--THEN THE LOG.
BACKUP LOG MSDB TO DISK = 'D:\SQL2K5DDUMP\MSDB.TRN'

--TRUNCATE THE LOG.
BACKUP LOG MSDB TO DISK = 'D:\SQL2K5DDUMP\MSDB,TRN ,TRUNCATEONLY'
--BACKUP THE LOG AGAIN.
BACKUP LOG MSDB TO DISK = 'D:\SQL2K5DDUMP\MSDB.TRN'
--The size is still the same. About 600 MB.

--Now shrink the file using the ID.
DBCC shrinkfile (2,truncateonly)

--check the log size
--52 MB
Last Updated on Monday, 29 December 2008 15:47