HiveBrain v1.2.0
Get Started
← Back to all entries
principleModerate

whats best practice for Log Truncation in SQL Server?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
logpracticesqltruncationwhatsforserverbest

Problem

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:
MY RECOVERY MODEL IS SIMPLE

ALTER proc [dbo].[UTIL_ShrinkDB_TruncateLog]
as

-- exec sp_helpfile
BACKUP LOG PMIS WITH TRUNCATE_ONLY

DBCC SHRINKFILE (PMIS, 1)

DBCC SHRINKFILE (PMIS, 1)

Solution


  • Don't truncate or shrink your log files.



  • See point 1



Why do you think this is a good idea?

With simple recovery, the internal log entries are removed periodically (ready to be reused) but the file requires no more processing.

The log file is the size it is because of your day to day activities. If you shrink the log file you'll end up with poor performance.

Context

StackExchange Database Administrators Q#1487, answer score: 13

Revisions (0)

No revisions yet.