Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite
1

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)

share|improve this question
in a simple recovery model do i just shrink logs after the backup is done (and a log truncation happened already as part of the backup)? – kacalapy Feb 28 '11 at 16:20
1  
Why you should not shrink your data files: sqlskills.com/blogs/paul/post/… – Eric Humphrey - lotsahelp Feb 28 '11 at 19:06

1 Answer

up vote 12 down vote accepted
  1. Don't truncate or shrink your log files.
  2. 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.

share|improve this answer
2  
@Eric Humphrey: Thanks, I was poking around there earlier for some reference material but didn't see this one. – gbn Feb 28 '11 at 19:06
this doesn't help me understand how to keep my log file for a DB in check and from growing or being out of control. – kacalapy Mar 1 '11 at 16:52
@kacalapy: your log file should not be growing if recovery model is simple. if it is, let it grow for a while to let it find it's natural size. If it keeps growing you have a problem, say with open transactions – gbn Mar 1 '11 at 18:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.