Shrink Database and Log Files in Production?

[gavm99]

Hi all,

I really need to shrink the size of a database (mdf) and transaction log (ldf) and I simply what to right click on the database and select the shrink database option.

Is it safe to do this with users accessing the database?

Also is there a better way of reducing the transaction log size?

It is SQL 2005.

Thanks.

 

[ptheriault]

Are you backing up your log?
What is the recovery model of your database?

I would not shrink the database during normal operational hours. I could impact performance.

To shrink you log use:
DBCC SHRINKFILE(log_name, truncateonly)

That will remove the free space from the end of the log. If your db is in full recovery backup your log first, it will clear all the committed transactions from your log and you will gain more space back.

You should then schedule transaction log backups to keep the log size in check.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[gavm99]

Hi Paul,

Thanks for the speedy response.

Yes I have backed the log up but it made no difference to the size of it.

My reovery model is set to Full.

Performance does not concern me, I'm only concerned about keeping the database healthy.

Thanks.

 

[SQLBill]

Paul asked if you were backing up your log file. He didn't mean just one time....do you have it scheduled to be backed up reqularly?

Backing up the log file will NOT cause the file to shrink immediately after. SQL Server will wait until it determines it is a good time to release the space.

With 2005, you can right click on the database and go to Tasks>Shrink>Files to do it through the GUI.


-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gavm99]

Hi Bill,

I'm sat at the server now and I have just used the right click and shrink option. I changed it the log file and then clicked ok. The log has jumped from 77GB to 82GB has not come down at all since. I have been waiting 5 mins or so.

How can I tell if it is running?

Thanks.

 

[ptheriault]

>>I have been waiting 5 mins or so.

It will take a lot longer than 5 minutes. Like maybe days of the log being around the same size.

You should manually shrink the log off hours.

- Paul
- If at first you don't succeed, find out if the loser gets anything.