DBCC Shrinkdatabase

Hello,
A question related to shrinking a database. Find below details of a
database I use, extracted from sp_helpdb abd sp_spaceused commands
Database
Name : ABC
Size : 56999.44 MB
Status : select into/bulkcopy, trunc. log on chkpt
Name Size Max Size Growth Usage
----
Abc_Data 53734848 KB Unlimited 10% data only
Abc_Log 4632576 KB Unlimited 10% log only
database_name database_size unallocated space
-----
Abc 56999.44 MB 23566.41 MB
reserved data index_size unused
-- -- --
--
34235424 KB 30824144 KB 3404648 KB 6632 KB
I am looking to free up the unallocated space of 23 GB. How do I go
about doing the same ?
DBCC SHRINKDATABASE Truncateonly did not result in any freeing up of
space.
Should I try "DBCC Shrinkdatabase" ? If so,
a) Is the amount of time taken for the process related to the size of
the database ?
b) If the answer to a) is in the affirmative, how long do you think
shrinking a 56 GB database would take ?
c) Should I stop all activity on the database before doing this ?
d) Will the process lock the database in entirety for the duration of
the execution of the command ?
Thank you,
Narayanan B> a) Is the amount of time taken for the process related to the size of
> the database ?
Depends more on the level of fragmentation. Obviously, you have parts of
objects at the end of the data file, otherwise the TRUNCATEONLY option would
have been enough.
> b) If the answer to a) is in the affirmative, how long do you think
> shrinking a 56 GB database would take ?
Again, depends on the level of fragmentation.
> c) Should I stop all activity on the database before doing this ?
Would help, but not necessary.
> d) Will the process lock the database in entirety for the duration of
> the execution of the command ?
No, not the entire database.
It may sometimes be a good idea to leave the database size as is, if you
think it would grow to that size some day. This is because if the database
needs to grow dynamically, it comes with a small impact on performance. If
performance is currently an issue, you could try defragmenting individual
tables / indexes.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"narayanan" <narayanan@.mytiger.com> wrote in message
news:13949517.0406030114.52d7432d@.posting.google.com...
> Hello,
> A question related to shrinking a database. Find below details of a
> database I use, extracted from sp_helpdb abd sp_spaceused commands
> Database
> Name : ABC
> Size : 56999.44 MB
> Status : select into/bulkcopy, trunc. log on chkpt
>
> Name Size Max Size Growth Usage
> ----
> Abc_Data 53734848 KB Unlimited 10% data only
> Abc_Log 4632576 KB Unlimited 10% log only
>
> database_name database_size unallocated space
> ----
--
> Abc 56999.44 MB 23566.41 MB
>
> reserved data index_size unused
> -- -- --
> --
> 34235424 KB 30824144 KB 3404648 KB 6632 KB
>
> I am looking to free up the unallocated space of 23 GB. How do I go
> about doing the same ?
> DBCC SHRINKDATABASE Truncateonly did not result in any freeing up of
> space.
> Should I try "DBCC Shrinkdatabase" ? If so,
> a) Is the amount of time taken for the process related to the size of
> the database ?
> b) If the answer to a) is in the affirmative, how long do you think
> shrinking a 56 GB database would take ?
> c) Should I stop all activity on the database before doing this ?
> d) Will the process lock the database in entirety for the duration of
> the execution of the command ?
> Thank you,
> Narayanan B|||Also, a bit more info is found at:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Yeoh" <nospam@.nospam.com> wrote in message news:eieOE4USEHA.2480@.TK2MSFTNGP10.phx.gbl...
> > a) Is the amount of time taken for the process related to the size of
> > the database ?
> Depends more on the level of fragmentation. Obviously, you have parts of
> objects at the end of the data file, otherwise the TRUNCATEONLY option would
> have been enough.
> > b) If the answer to a) is in the affirmative, how long do you think
> > shrinking a 56 GB database would take ?
> Again, depends on the level of fragmentation.
> > c) Should I stop all activity on the database before doing this ?
> Would help, but not necessary.
> > d) Will the process lock the database in entirety for the duration of
> > the execution of the command ?
> No, not the entire database.
> It may sometimes be a good idea to leave the database size as is, if you
> think it would grow to that size some day. This is because if the database
> needs to grow dynamically, it comes with a small impact on performance. If
> performance is currently an issue, you could try defragmenting individual
> tables / indexes.
>
> Peter Yeoh
> http://www.yohz.com
> Need smaller backup files? Try MiniSQLBackup
>
> "narayanan" <narayanan@.mytiger.com> wrote in message
> news:13949517.0406030114.52d7432d@.posting.google.com...
> > Hello,
> >
> > A question related to shrinking a database. Find below details of a
> > database I use, extracted from sp_helpdb abd sp_spaceused commands
> >
> > Database
> > Name : ABC
> > Size : 56999.44 MB
> > Status : select into/bulkcopy, trunc. log on chkpt
> >
> >
> > Name Size Max Size Growth Usage
> > ----
> > Abc_Data 53734848 KB Unlimited 10% data only
> > Abc_Log 4632576 KB Unlimited 10% log only
> >
> >
> > database_name database_size unallocated space
> > ----
> --
> > Abc 56999.44 MB 23566.41 MB
> >
> >
> > reserved data index_size unused
> > -- -- --
> > --
> > 34235424 KB 30824144 KB 3404648 KB 6632 KB
> >
> >
> > I am looking to free up the unallocated space of 23 GB. How do I go
> > about doing the same ?
> > DBCC SHRINKDATABASE Truncateonly did not result in any freeing up of
> > space.
> >
> > Should I try "DBCC Shrinkdatabase" ? If so,
> > a) Is the amount of time taken for the process related to the size of
> > the database ?
> > b) If the answer to a) is in the affirmative, how long do you think
> > shrinking a 56 GB database would take ?
> > c) Should I stop all activity on the database before doing this ?
> > d) Will the process lock the database in entirety for the duration of
> > the execution of the command ?
> >
> > Thank you,
> > Narayanan B
>