Resize transaction log

[Paul]

I have a SQL 7.0 database Drill.mdf (19 MB) and Drill_log.ldf (594 MB).
After operation "shrink database" space allocated became as:

Data space Used Free Total
Drill 18,06MB 0,06MB 18,13MB
Transaction log space 20,79MB 545,70MB 566,49MB

Could I somehow decrease the size of Drill_log.ldf file?

[Karl]

Paul,

Due to the structure of the SQL Server log files, the amount of log that can be shrunk is based on the position of the virtual log file.

I have a script that allows you to resize the log by creating dummy transactions that effectively move the position of the virtual log file until the log can be shrunk effectively.

If you mail me I can send you that script.

Regards,

Karl


------------
Paul at 11/24/00 8:17:22 AM

I have a SQL 7.0 database Drill.mdf (19 MB) and Drill_log.ldf (594 MB).
After operation "shrink database" space allocated became as:

Data space Used Free Total
Drill 18,06MB 0,06MB 18,13MB
Transaction log space 20,79MB 545,70MB 566,49MB

Could I somehow decrease the size of Drill_log.ldf file?

[Jun]

Yes, you can if you use the "Shrinkfile". There are two things you have to consider when you use "Shrink database" :
1) If the transaction log was not truncated, the transaction log can only be shrinked to the size which is necessary to hold all the ACTIVE part of the transaction log.

2) Both SQL data and log files can only be shrinked to the original size you specified when you created the DB.

Instead of using DBCC Shrink database", you can use "DBCC Shrinkfile" to shrink your data and log file to a minimum size or the size you specify.

e.g.

-- Shrink to minimum size:
USE UserDB
GO
DBCC SHRINKFILE (UserDb_data)
DBCC SHRINKFILE (UserDb_log)
GO

-- Shrink to a specific size:
USE UserDB
GO
DBCC SHRINKFILE (UserDb_data, 50) -- 50 is 50 MB
DBCC SHRINKFILE (UserDb_log, 30)
GO

jun




------------
Paul at 11/24/00 8:17:22 AM

I have a SQL 7.0 database Drill.mdf (19 MB) and Drill_log.ldf (594 MB).
After operation "shrink database" space allocated became as:

Data space Used Free Total
Drill 18,06MB 0,06MB 18,13MB
Transaction log space 20,79MB 545,70MB 566,49MB

Could I somehow decrease the size of Drill_log.ldf file?

------------
Paul at 11/24/00 8:17:22 AM

I have a SQL 7.0 database Drill.mdf (19 MB) and Drill_log.ldf (594 MB).
After operation "shrink database" space allocated became as:

Data space Used Free Total
Drill 18,06MB 0,06MB 18,13MB
Transaction log space 20,79MB 545,70MB 566,49MB

Could I somehow decrease the size of Drill_log.ldf file?