Shrinking SQL 2000 Transaction Logs

[Robin Frousheger]

We are having some problems shrinking SQL 2000 transaction logs.

When the transaction log auto-grows to 10GB or so, we can't seem to shrink it with the 'Shrink Database' tool of SQL Enterprise Manager.

Articles I have read indicate that we have to shrink, backup and shrink again. The problem with this is that we don't really have the time & resources to do this or even to test it at present.

Is this the only way, or can someone offer a better suggestion.

Thanks & Regards,
Robin.

[Keld · Accepted solution]

SQL Server 2000 autoshrinks databases that have a large amount of free space. Only those databases where the autoshrink option has been set to true are candidates for this process. The server checks the space usage in each database periodically. If a database is found with a lot of empty space and it has the autoshrink option set to true, SQL Server reduces the size of the files in the database. You can also use SQL Server Enterprise Manager or the DBCC SHRINKDATABASE and DBCC SHRINKFILE statements to shrink the files of a database manually.

Files are always shrunk from the end. For example, if you have a 5 GB file and specify 4GB as the target_size in a DBCC SHRINKDB statement, SQL Server will free as much space as it can from the last 1 GB of the file. If there are used pages in the part of the file being released, SQL Server first relocates the pages to the part being retained. You can only shrink a database to the point where it has no free space remaining. For example, if a 5GB database has 4 GB of data and you specify 3 GB as the target_size of a DBCC SHRINKDATABASE statement, only 1 GB will be freed.

If a DBCC SHRINKDATABASE or DBCC SHRINKFILE statement cannot reclaim all the space in a log file, the statement will issue an informational message indicating what action you must perform to make more space eligible to be freed


The size of the log files are physically reduced when:

A DBCC SHRINKDATABASE statement is executed.


A DBCC SHRINKFILE statement referencing a log file is executed.


An autoshrink operation occurs.
Shrinking a log is dependent on first truncating the log. Log truncation does not reduce the size of a physical log file, it reduces the size of the logical log and marks as inactive the virtual logs that do not hold any part of the logical log. A log shrink operation removes enough inactive virtual logs to reduce the log file to the requested size.

The unit of size reduction is a virtual log. For example, if you have a 600 MB log file that has been divided into six 100 MB virtual logs, the size of the log file can only be reduced in 100 MB increments. The file size can be reduced to sizes such as 500 MB or 400 MB, but it cannot be reduced to sizes such as 433 MB or 525 MB.

Virtual logs that hold part of the logical log cannot be freed. If all the virtual logs in a log file hold parts of the logical log, the file cannot be shrink until a truncation marks one or more of the virtual logs at the end of the physical log as inactive.

When any file is shrunk, the space freed must come from the end of the file. When a transaction log file is shrunk, enough virtual logs from the end of the file are freed to reduce the log to the size requested by the user. The target_size specified by the user is rounded to the next highest virtual log boundary. For example, if a user specifies a target_size of 325 MB for our sample 600 MB file with 100 MB virtual log files, the last two virtual log files are removed and the new file size is 400 MB.

In SQL Server 2000, a DBCC SHRINKDATABASE or DBCC SHRINKFILE operation attempts to shrink the physical log file to the requested size (subject to rounding) immediately:

If no part of the logical log is in the virtual logs beyond the target_size mark, the virtual logs after the target_size mark are freed and the successful DBCC statement completes with no messages.


If part of the logical log is in the virtual logs beyond the target_size mark, SQL Server 2000 frees as much space as possible and issues an informational message. The message tells you what actions you need to perform to get the logical log out of the virtual logs at the end of the file. After you perform this action, you can then reissue the DBCC statement to free the remaining space.


Do you get any error messages???