question about log file

[equus2]

Hi,

We do full backups only, no log file backups. One of our log files reached the 100GB mark. I realized this is my fault because I wasn't truncating & shrinking the log file on a regular basis.

But I'm just curious as to why this log got so big? Is it only because I neglected to back it up with truncate? OR - is it because there a huge transaction at some point that increased the size?

I ask, because my understanding is that a log file will release old transactions after they've been written to the data file, so unless the transaction are inordinately huge, the log shouldn't grow this big under "normal" use.

Thanks

 

[SQLBill]

Right click on your database, go to Properties, then to Options. Is the database recovery mode set to FULL. If so, then you need to do transaction log backups (BACKUP LOG command) to get it to shrink. This is because FULL recovery mode is used to allow you to recover to a specific point-in-time which requires log backups.

If you don't need that, change the Recovery Mode to Simple. Then the log file is automagically CHECKPOINTed and SQL Server will keep up with the shrinking.


-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[BridgeRE]

I've backed up a 9GB log file, then ran the shrink on it and it shrunk it a whole 200 MB. What's the deal with that? Also is there anything wrong with specifying the shrink size. Realize in Simple Mode you can only restore to the last full or incremental backup. All transactions from then till crash would be lost.

 

[MissTipps]

Ok - transaction log 101 (in simple terms)

Transaction log exists for two reasons:

1. So that YOU can recover to a point in time if the database gets screwed up
2. So that SQL Server can recover itself should, for example, the service unexpectedly stop for any reason.

In Simple recovery model, SQL Server needs to be able to do (2), so it will ALWAYS retain ACTIVE transactions in the log until the point they are actually written to disk. Then the CHECKPOINT process will run and inactive transactions will be removed, freeing up log space.

In Full recovery model SQL Server believes that EVERYTHING in the log is needed by you, to satisfy (1) so never removes anything from the log until you tell it that the transactions are 'saved' by running a log backup. No backup = ever increasing log file.

Transaction logs are made up of a number of VLFs (Virtual Log Files) and occasionally if an active transaction remains in the last VLF of the log, even when you back the log up, it cannot truncate past that VLF with the active transaction. This is the reason you occasionally see very little log shinkage

MissTipps

CISSP, CEH, CEI, MCT, MCDBA, MCSE 2K3, CTT+, ECSA, Security+

 

[BridgeRE]

What steps are needed to be done to shrink the log file after a log file back up is done?