SQL 2000 Truncate/Shrink transaction logs

[swhitten]

We have some tx logs that are growing disproportionately large and not shrinking. We have maintenance plans that backup up the logs every 6 hours and backup the databases nightly. DBS are in Full recovery model.

I want to be sure I understand the process, and I want this to be an automatic process, not something dependent on users doing manual truncate/shrink operations.

Here's what I think I understand. First the log file must be truncated before it can shrink. This should be happening when we backup the log files. Correct?

Then in order to reduce the size on the disk, the log file must be shrunk. This is happening when an autoshrink is run? If the database is marked 'autoshrink', when does that run? Does it run as a part of the db backup?

I also looked at FAQ183-345, which addresses why tx logs won't shrink. I ran the DBCC LOGINFO against one of my problem dbs. It has many rows (maybe 75? - forgot to look exactly), and one of the rows has a Status=2 in the third from the last row. The FAQ says if the active portion is near the end of the log file, the log won't shrink. But it doesn't say what to do about it . . .

All help will be greatly appreciated!!

 

[TheBugSlayer]

Switten,

Yes, shrinking is dependent on truncation. When you truncate a log file the size of the logical log is reduced and virtual logs that do not hold any part of the logical log are marked as inactive. Shrinking then removes the virtual logs to reduce the size of your log file.

SQL Server checks the space used by each databse. When AUTOSHRINK is set to true, SQL Server reduces the size of the database during that process. It runs automatically. The databases' files sizes are checked periodically, not sure how often. This also when happens when you execute DBCC SHRINKDATABASE or DBCC SHRINKFILE.

I hope this helps you a bit.