Please help: Shrink SQL Server database

Hi,
I created a database for test purposes. I am trying to obtain some
metrics for various scenarios I wish to test. Many of these scenarios
involve bulk inserts. Rather then deleting all rows after each run I
simply dropped and recreated the tables. However I've noticed that the
mdf and log files are getting larger and larger even when the database
is completely empty of data. How can I safely shrink the size of these
files?
Thanks,
PaulSee Books OnLine regarding dbcc shrinkfile and
shrinkdatabase.
>--Original Message--
>Hi,
>I created a database for test purposes. I am trying to
obtain some
>metrics for various scenarios I wish to test. Many of
these scenarios
>involve bulk inserts. Rather then deleting all rows after
each run I
>simply dropped and recreated the tables. However I've
noticed that the
>mdf and log files are getting larger and larger even when
the database
>is completely empty of data. How can I safely shrink the
size of these
>files?
>Thanks,
>Paul
>.
>|||You could shrink the database, but beware that if you're going to occupy
that space again, you're better off leaving the file large. Otherwise, you
will go through the performance problem of autogrowing during a transaction
to make room again.
http://www.aspfaq.com/2471
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Paul" <paulsmith5@.hotmail.com> wrote in message
news:ca236fb1.0402170925.125f087e@.posting.google.com...
> Hi,
> I created a database for test purposes. I am trying to obtain some
> metrics for various scenarios I wish to test. Many of these scenarios
> involve bulk inserts. Rather then deleting all rows after each run I
> simply dropped and recreated the tables. However I've noticed that the
> mdf and log files are getting larger and larger even when the database
> is completely empty of data. How can I safely shrink the size of these
> files?
> Thanks,
> Paul|||First off you should use SIMPLE recovery mode and then use DBCC SHRINKFILE
to shrink the files.
--
Andrew J. Kelly
SQL Server MVP
"Paul" <paulsmith5@.hotmail.com> wrote in message
news:ca236fb1.0402170925.125f087e@.posting.google.com...
> Hi,
> I created a database for test purposes. I am trying to obtain some
> metrics for various scenarios I wish to test. Many of these scenarios
> involve bulk inserts. Rather then deleting all rows after each run I
> simply dropped and recreated the tables. However I've noticed that the
> mdf and log files are getting larger and larger even when the database
> is completely empty of data. How can I safely shrink the size of these
> files?
> Thanks,
> Paul|||Thanks|||Thanks Andrew|||Thanks Aaron,
The performance problem of autogrowing is another thing I should look at.|||In Enterprise Manager
1. Right click on the database in question
2. All Tasks -> Shrink Databases
3. Put in a Maximum Free space of say 10%
4. Check the "Move Pages to beginning....."
5. Press OK
...and that should do it.
J
>--Original Message--
>Hi,
>I created a database for test purposes. I am trying to
obtain some
>metrics for various scenarios I wish to test. Many of
these scenarios
>involve bulk inserts. Rather then deleting all rows after
each run I
>simply dropped and recreated the tables. However I've
noticed that the
>mdf and log files are getting larger and larger even when
the database
>is completely empty of data. How can I safely shrink the
size of these
>files?
>Thanks,
>Paul
>.
>