Hi all,

Its my understanding that when I perform a full backup, or even a transaction log backup using the full recovery model, that all committed transactions are cleared out of the log. I notice, however, that after performing a backup the physical size of the transaction log on disk remains the same. I have the log set to auto-grow. So either:

1. The physical size of the log following a backup doesn't shrink from the size it has expanded to, but its emptied of committed transactions nonetheless, or

2. I'm doing something wrong.

Any insights?

Thanks,

Bob

When you do a backup (in full recovery mode), you only truncate the log, you don't actually physically shrink its size. Truncating the log simply marks the committed entries as unused and available. To physically shrink the file, you must execute a DBCC SHRINKDATABASE statement, or a DBCC SHRINKFILE statement referencing a log file. The log file is also physically shrunk if the autoshrink option is on and the database autoshrinks.

See BOL table of contents SQL Server Architecture, DataBase Architecture, Physical DataBase Architecture, Transaction Log Architecture, Shrinking the Transaction Log (whew!).

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Thanks Jeff,

I'll have a look at the references. I also pulled the following off another forum last night where some folks are talking about something similar. Looks like it might do the trick:

CREATE PROCEDURE [DBO].[usp_Truncate_Log] AS
BEGIN
    EXEC sp_dboption 'DB_NAME', 'trunc. log on chkpt.', 'TRUE'
END
BEGIN
    CHECKPOINT
END
BEGIN
    EXEC sp_dboption 'DB_NAME', 'trunc. log on chkpt.', 'FALSE'
END
BEGIN
    DBCC SHRINKFILE(LogFileName,1)
END

Thanks again,

Bob

From BOL:

"The trunc. log on chkpt and select into/bulkcopy database options are supported in SQL Server 2000 for backward compatibility purposes, but may not be supported in future releases."

Use at your own risk...

That whole mechanism has been replaced by "recovery models".


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

I see...thanks Jeff.