my database wont shrink

[nuct]

Hi

We have a database which is 11 gb in size. However only 9 gb is is used the remaining 2 gb is free space. I was wondering how I go about freeing this the OS. I have tried:

DBCC SHRINKDATABASE (i3_eic, 1, TRUNCATEONLY)

but it only seemed to free about 400 mb or so.

Has anybody got any ideas as we are rapidly running out of space on the server.

Cheers,
Simon

 

[jgoodman00]

Have you run a backup lately & truncated the log?

Are there transactions still running, preventing truncation?

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[nuct]

are you saying that it might shrink to the desired size at the next backup?

 

[jgoodman00]

You need to backup the log in order to move the transactions away from the active log. You should then be able to shrink it. However, if the log is just going to grow again then there is little point doing this.

Scheduling regular log backups can help to keep its size to a minimum, but this is at the cost of restore-simplicity.
e.g.
Full backup at 01:00 every day.
TLog backup every 15 mins.

If the system fails at 00:46 you will have to restore the full backup, then each of the TLog backups (4 per hour x 23 hours + 3 + active = 96 logs!)



James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[nuct]

You need to backup the log in order to move the transactions away from the active log. You should then be able to shrink it. "

When you say "it" do you mean the database files of the log files. Because my problem is that the database file needs to be shrunk. The log file isn't an issue because it is set to truncate on checkpoint.

 

[nuct]

If there is something i am misunderstanding feel free to explain it to me.

 

[jgoodman00]

Ahh, ok.

You might be able to free a little more space by simply running:
DBCC SHRINKDATABASE (i3_eic)

This should move the pages to the beginning of the file, which might then free up some more space.

You will want to reindex/defrag all of your indexes after running any shrink command, as the indexes become fragmented & performance can suffer.



James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[nuct]

ok cheers, and obviously i would be better to do this after hours right?

Simon.

 

[jgoodman00]

It can be run during normal hours, but it will use resources. I guess it depends how loaded the system is. If the system is close to capacity then wait...

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[nuct]

I think I'll do it later. Cheers for your help.