Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have jobs in place to trim tables used for history and logging to keep them trim. I want to make sure I don't neglect the log file.

How and what can I or should I do to keep the log file in check?

The SQL script I have running on a nightly schedule is:

declare @DBname varchar(500)
set @DBname = 'E:\Database\backup\PMIS_backup_' +
    convert(varchar(MAX), getdate(), 23 ) +'.bak'

BACKUP DATABASE [PMIS] TO DISK = @DBname
    WITH NOFORMAT, NOINIT, NAME = @DBname, SKIP, REWIND, NOUNLOAD, STATS = 10

The database is in simple recovery mode.

share|improve this question

migrated from stackoverflow.com Mar 1 '11 at 18:13

This question came from our site for professional and enthusiast programmers.

    
One of our development database has a size of about 850MB and a log file of 38GB. And its really nothing. –  Pierre-Alain Vigeant Mar 1 '11 at 16:49
    
cobo is a typo, i fixed it to say combo. as in combination. –  kacalapy Mar 1 '11 at 16:53
    
@Kacalapy - it's pretty silly for you to tell someone that how they operate is "nuts" when you are posting a question that shows you don't have any idea what the best way to maintain this is. It's fine not to know, it's not fine to imply others are wrong. –  JNK Mar 1 '11 at 16:58
    
RE: The Title Question –  Martin Smith Mar 2 '11 at 7:34

3 Answers 3

up vote 3 down vote accepted

If the database is in simple recovery mode, and the log file isn't getting gradually larger, then I wouldn't worry about it. Chances are it needs to be that big if there's any sort of maintenance plan doing substantial reindexing on a regular basis.

If the log file does get larger, you'll want to find out why. You can run DBCC SQLPERF(LOGSPACE) to see how much of the log file is in use - if it's consistently high, then check the log_reuse_wait_desc column in master.sys.databases to see why it's not reusing the space like it's supposed to. If the usage is low, then there was probably some one-off transaction that did a ton of work and caused the log file to grow. If it's a transaction that will happen regularly, then repeatedly shrinking and growing the log file will only hurt performance, so leave it at whatever size gives comfortable working room. On the other hand, if the log file is many times the size of the data file, then that's probably unnecessary.

share|improve this answer

Are you doing transaction log backups? Each time one succeeds, SQL gets rid of inactive transactions from the log, and this alone might keep the transaction log file size manageable.

share|improve this answer
    
no backups other than a nightly job to backup the db and create a DB .bak file. i use the script posted above to make a bak file –  kacalapy Mar 1 '11 at 17:04
    
+1 frequent and scheduled transaction log backups are the key to keeping your transaction logs humming and working just fine –  marc_s Mar 1 '11 at 17:36
    
I see the Q has been updated: simple mode => log file backups don't apply. –  Richard Mar 1 '11 at 18:22

You'll need to shrink the log file after performing a backup of the log file.

Every time you delete a record from the table, you're removing teh data from the data file, but that operation gets logged in the log file (hence the name ;-).

When a database is configured for Full recovery model, the log file is endlessly appended too until it's backed up. Check out MSDN: Transaction Log Physical Architecture for more info.

Also, if you're not doing regular transaction log backups (presumably because you don't need point in time recovery), you should consider using a Simple recovery model. Of course, you should backup & shrink the log file first and then change the recovery model of the database to simple.

share|improve this answer
    
i for got to add the Db is already in simple mode. so my thinking is that my nightly backup wipes the log clean and starts using it all over again. –  kacalapy Mar 1 '11 at 18:19

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.