CODEWITHSUNDEEP
sql-server-2005
kacalapy
kacalapy

Reputation: 10164

whats best practice for Log Truncation in SQL Server?

i have a production DB in SQL server and wanted to put the final touches after the functionality is completed. prior to shipping it out i want to make sure i have some clean up in the SQL server DB and truncate and shrink log files?

can i have a nightly job run to truncate logs and shrink files?

this is what i have so far:

ALTER proc [dbo].[UTIL_ShrinkDB_TruncateLog]
as

-- exec sp_helpfile
BACKUP LOG PMIS WITH TRUNCATE_ONLY

DBCC SHRINKFILE (PMIS, 1)

DBCC SHRINKFILE (PMIS, 1)

EDIT: MY RECOVERY MODEL IS SIMPLE

Upvotes: 1

Views: 2138

Answers (2)

Damian Vogel
Damian Vogel

Reputation: 1192

In any case someone (like me) stumbled upon this entry and was looking for truncate logs with recovery model full, here is what I've found after a few hours of investigation. Improve your log size and disaster recovery with these steps.

  1. Activate full recovery
SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'AdventureWorks'
-- AdventureWorks  SIMPLE
ALTER DATABASE AdventureWorks SET RECOVERY FULL
  1. Backup data
DECLARE @FileName varchar(1000)
SELECT @FileName = (SELECT 'D:\SQL Server\Backup\' + CAST(FORMAT(GETDATE(), 'yyyy-MM-dd HH-mm-ss') AS VARCHAR) + ' AdventureWorks.bak')
BACKUP DATABASE AdventureWorks TO DISK=@FileName

Bonus: inspect backup history

-- inspect backup history (type D=Data, L=Log)
SELECT TOP 10 bs.database_name, bs.backup_start_date, CAST((bs.backup_finish_date-bs.backup_start_date) as time(0)) duration, bs.server_name, bs.user_name, bs.type, 
        bm.physical_device_name, CAST(bs.compressed_backup_size / 1024 / 1024 / 1024 AS decimal(10,3)) compressed_backup_size_GB
    FROM msdb.dbo.backupset AS bs 
    INNER JOIN msdb.dbo.backupmediafamily AS bm on bs.media_set_id = bm.media_set_id
    ORDER BY bs.backup_start_date DESC
  1. Monitor log file size

Trace the log size and usage for a few weeks. Create the table TMP_dbLogSize:

-- create table to track log usage and size
SELECT GETDATE() AS date, total_log_size_in_bytes / 1024 / 1024 total_log_MB, used_log_space_in_bytes / 1024 / 1024 used_log_MB, log_space_in_bytes_since_last_backup / 1024 / 1024 space_since_last_bak_MB, used_log_space_in_percent INTO TMP_dbLogSize FROM sys.dm_db_log_space_usage

Collect info in table with a scheduled job

-- track log usage and size
INSERT INTO TMP_dbLogSize SELECT GETDATE() AS date, total_log_size_in_bytes / 1024 / 1024 total_log, used_log_space_in_bytes / 1024 / 1024 used_log, log_space_in_bytes_since_last_backup / 1024 / 1024 space_since_last_bak, used_log_space_in_percent FROM sys.dm_db_log_space_usage
-- clean table for tracking log usage after some time
delete from TMP_dbLogSize where DATEDIFF(DAY, date, GETDATE()) > 60
  1. Backup logs

This is the step that will free up the space to in the log files.

-- backup log (overwrite last log backup)
BACKUP LOG AdventureWorks TO DISK = N'D:\SQL Server\Backup\AdventureWorks_log.trn' WITH FORMAT, INIT, SKIP
  1. Optimize log file size

Check the typical maximum log usage in TMP_dbLogSize, then shrink it to this value.

SELECT name, size/128 FileSizeInMB, FILE_ID FROM sys.database_files
--AdventureWorks_data  32921   1
--AdventureWorks_log   285 2
use AdventureWorks
-- dbcc shrinkfile(FILE_ID from sys.database_files, target size in MB)
dbcc shrinkfile(AdventureWorks_log, 150)
  1. Optional: Rebuild indexes

Regularly rebuild indexes so performance stays good (given it was good in the beginning), can be executed following the backup (ideally directly afterwards, before anything else runs on the server).

-- rebuilding all indexes will create a lot of logs if in RECOVERY FULL, but we just did a backup
ALTER DATABASE evi_sarsta_prod SET RECOVERY BULK_LOGGED
GO
-- DB maintenance, rebuild all indexes
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))+'.' + QUOTENAME(name) AS TableName FROM sys.tables
OPEN TableCursor
    FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
    EXEC (@sql)
    FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
-- reset log level back to full
ALTER DATABASE evi_sarsta_prod SET RECOVERY FULL
GO

Upvotes: 0

msarchet
msarchet

Reputation: 15242

Since you are performing a Backup on the logfile you shouldn't have to truncate it. Note that this doesn't cause the logfile to shrink it just causes it to overwrite itself. So you need to perform them frequently to maintain a small log file.

Here is a good article on log maintenance.

https://web.archive.org/web/20111216163302/https://www.emmet-gray.com/Articles/SQL_LogMaintenance.htm

Upvotes: 2

Related Questions