Resizing the Hedberg database log file
RESIZING THE HEDBERG DATABASE LOG FILE
Why would you do this?
There are times when the transaction log portion of the database gets much
larger than even the data portion of the database. That can happen if transaction
log backups are not being done or do not successfully complete.
How would you do this?
Here is a process to resize the Hedberg database log file while users are still on the
system. This process is for SQL2K databases and will only work if the current recovery
model (check the Options tab under database Properties in Enterprise manager) is set to
FULL.
Here are the steps:
1) Through MS SQL Query Analyzer, back up the database.
2) In Enterprise Manager right click on database you wish to shrink the log and select
Properties. Under Properties select the Options tab. In the Options tab select
"Simple" under recovery model (assumes recover model was "Full").
3) Select OK
4) Right click again on the database and select All Tasks\Shrink Database
5) In the Shrink Database screen select "Files" button in lower right
6) In the Shrink File Screen select Hedberg_Log in the Database file drop down box.
7) Select OK to begin the file shrink. Once complete a Shrink Successful box will
appear.
8) Back at the Shrink Database screen select Cancel
9) At this point your log file should be very small
10) Right click again on the database and select Properties. Select the Transaction Log
tab and resize the log to 75% of the database data file. Enter the size (MB) in the
space allocated section and select OK.
11) Right click again on the database and select Properties. Select the Options tab. In
the Options tab select "Full" under recovery model. This will allow you to backup
your transaction log. Select OK
Hedberg Data Systems 9/4/2003
Resizing the Hedberg database log file
12) Backup your database (transaction log backups will error unless a full database is
performed). Your users will not be affected.
Hedberg Data Systems 9/4/2003