Found this added as a truncate job on customer SQL Server 2005

Question

Found this added as a truncate job on customer SQL Server 2005

kevin_nikolai

SSCarpal Tunnel

Points: 4343
More actions
November 17, 2008 at 7:57 am

#154923

Found this added as a truncate job on customer SQL Server 2005,
can someone elaborate on this method of shrinking the log file.
use Mydatabase
go
DECLARE @DB VARCHAR(40)
SELECT @DB = 'Mydatabase'
CHECKPOINT
EXEC('DBCC SHRINKFILE (Mydatabase_log, 10, NOTRUNCATE)')
EXEC('BACKUP LOG '+@DB+' WITH TRUNCATE_ONLY')

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

clive (11/17/2008)
Found this added as a truncate job on customer SQL Server 2005,
can someone elaborate on this method of shrinking the log file.
use Mydatabase
go
DECLARE @DB VARCHAR(40)
SELECT @DB = 'Mydatabase'
CHECKPOINT
EXEC('DBCC SHRINKFILE (Mydatabase_log, 10, NOTRUNCATE)')
EXEC('BACKUP LOG '+@DB+' WITH TRUNCATE_ONLY')

Based on the above, is it safe to assume that "Mydatabase" is using the full or bulk logged recovery model? If so, this really is a no-no. It will break log chain preventing point in time recovery of the database.

If the point of the above is to control the size of the transaction log file of the database, it is still a no-no. Shrinking the transaction log file only results in SQL Server having to grow it again as it needs space to write the transaction log records. This will impact system performance.

If the database is using the full or bulk logged recovery model, then transaction log backups should be scheduled periodically throughout the day to maintain a steady transaction log file. This log file should be set large enough to handle the busiest time of the day between log backups plus a little more for a cushion.