1

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.

4
  • One of our development database has a size of about 850MB and a log file of 38GB. And its really nothing. Mar 1, 2011 at 16:49
  • cobo is a typo, i fixed it to say combo. as in combination.
    – kacalapy
    Mar 1, 2011 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, 2011 at 16:58
  • RE: The Title Question Mar 2, 2011 at 7:34

3 Answers 3

3

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.

2

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.

3
  • 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, 2011 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, 2011 at 17:36
  • I see the Q has been updated: simple mode => log file backups don't apply.
    – Richard
    Mar 1, 2011 at 18:22
1

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.

1
  • 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, 2011 at 18:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .