Delete data from DB then need to shrink the DB

[eo]

Hi

SQL server 2000.

I have a DB which is 18,671MB in size with only 445MB free space. I am now deleting records from this DB as we only need 45 days worth of data. But I am assuming this does not automatically free up space!! Do I need to perform DBCC SHRINKDATABASE to physically free up DB space and to reduce the size oif the log files??

The syntax for DBCC SHRINKDATABASE is

DBCC SHRINKDATABASE
( database_name [ , target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ]
)

...where target_percent "Is the desired percentage of free space left in the database file after the database has been shrunk"

How do I know what this percentage should be as I have simply deleted records, I do not know how much space has potentially been freed?

EO
Hertfordshire, England

 

[Crowley16]

the percentage free is just how much space you want left in the database before it needs to grow again, just specify what you feel you need...

if you don't know, then put in 10%...

--------------------
Procrastinate Now!

 

[ptheriault]

What is your autogrow size? If it is larger than 10% of free space then your db will just autogrow again after you've shrunk it resulting in more than 10% free space.

Don't use truncateonly as that will remove all free space from the end of the file. Then your db will just autogrow so it has the ammount of free space you've specified.



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