Shrinking the database size (SQL 7)

[Gus]

We have a SQL 7 database that grew to about 50GB. Needless to say we've done
some work to minimize the size of it, but we've come to a problem trying
to release all the unused space to the OS. (Amount of data is about 13 GB).

We've tried to run the "Shrink Database" option in Ent. Mgr., and after working
for a long time, it simply stops responding, so we have to kill it with Task
Manager. Then, we created a SQL Job and scheduled it to run in the middle
of the night. That time it completed (about 14 hours later), and it shrunk
the file by 5 GB.

The statements we're using are:
1. DBCC SHRINKDATABASE (<db_name>, TRUNCATEONLY)
to let SQL determine how much it can take off
or
2. DBCC SHRINKDATABASE (<db_name>, 10)
to force it to leave 10% of free space (actually we've tried different options
5%, 10%, 50% and no setting seems to matter.

We'd appreciate your input for we are wasting valuable space on our server.

Thanks,

Gus

[Rob Vieira]

Hi Gus,

Can you post or e-mail me the results of a sp_helpdb on your database?

Also, can you be more clear on what you're trying to shrink? Could it be the
log is the thing that you're trying to shrink rather than the database
itself? If so, it's quite doable but there are a different set of issues to
deal with in that scenario. In a log situation, you have to deal with the
notion of what part of the log is "Active". If the active part of the log is
the part at the end, then it won't shrink because that would involve
chopping off a part of the log that's needed.

Check out the MSKB article at:

http://support.microsoft.com/support.../Q256/6/50.ASP

--
Rob Vieira MCSD, MCT, MCDBA
www.ProfessionalSQL.com


"Gus" <GWellmann@TeldataControl.com> wrote in message
news:3a6f2a55$1@news.devx.com...
>
> We have a SQL 7 database that grew to about 50GB. Needless to say we've
done
> some work to minimize the size of it, but we've come to a problem trying
> to release all the unused space to the OS. (Amount of data is about 13
GB).
>
> We've tried to run the "Shrink Database" option in Ent. Mgr., and after
working
> for a long time, it simply stops responding, so we have to kill it with
Task
> Manager. Then, we created a SQL Job and scheduled it to run in the middle
> of the night. That time it completed (about 14 hours later), and it
shrunk
> the file by 5 GB.
>
> The statements we're using are:
> 1. DBCC SHRINKDATABASE (<db_name>, TRUNCATEONLY)
> to let SQL determine how much it can take off
> or
> 2. DBCC SHRINKDATABASE (<db_name>, 10)
> to force it to leave 10% of free space (actually we've tried different
options
> 5%, 10%, 50% and no setting seems to matter.
>
> We'd appreciate your input for we are wasting valuable space on our
server.
>
> Thanks,
>
> Gus
>