Care and Feeding of Databases

I've been wrestling with some questions about databases.
1. I allow my databases to grow 10% and I find that I will have tons of
excess space according to EM database properties. I try to shrink but
it doesn't. I try a full databack up and transaction backup, but the
shrink still doens't help. (I understand that it may be bad form to
allow the database to automaticly grow, however I wish to learn of
whats going on here. Changing the option to prevent growth wont teach
me anything) ;)
2. Because of the above situation, I've found a brute force way: Create
a smaller database (the original size) and use Import/Export to copy
all the data and objects. However, many times this process will fail
with vague errors, (when I check for details about the error, it just
restates it had an error, nothing I could sink my teeth into).

Anyone have links or info that speaks to these situations, or "Do's and
Dont's" about database maintance?
TIA1. It's not really clear (to me) what the exact problem is. Do you mean
that you 'successfully' shrank the database, but the file sizes didn't
change? If so, how did you shink it - with Enterprise Manager, or with
DBCC SHRINKDATABASE/SHRINKFILE (see Books Online)? Did you get any
error messages? How big is the database, and what size are you trying
to shrink it to?

2. No idea - what exactly are you doing, and what are the errors?

You might find these links useful:

http://support.microsoft.com/defaul...kb;en-us;315512
http://support.microsoft.com/defaul...kb;en-us;272318
http://support.microsoft.com/defaul...kb;en-us;324432
http://www.sql-server-performance.c...se_settings.asp

Finally, have you installed the latest servicepack? There are a number
of fixes documented in the Knowledge Base related to shrinking
databases.

Simon|||Thanks Simon,
As an example: Databae DS_V5_TARGET is 23,328MB with 16,086 MB free. I
think the original size that I created was 2,048MB. I try to shrink it,
no change in size. I then learned that I have to backup the
transactions so that they can be flagged as clearable (not sure the
exact terms). So I did a backup via EM, shrink still wont work (via
EM). I dont get errors, i just dont get the files to shrink in size. I
will say, that I have shrunk the databases in the past succesfully, it
just seems hit or miss.
I know point 2 is very vague, I'll try to recreate and post follow ups.
running with SP4.
Thanks for the links
Rob|||Simon,
The third article you listed is esentially what I've been doing...
Thanks!
Rob|||"rcamarda" <rcamarda@.cablespeed.com> wrote in message
news:1119367627.676850.223550@.g44g2000cwa.googlegr oups.com...
> Thanks Simon,
> As an example: Databae DS_V5_TARGET is 23,328MB with 16,086 MB free. I
> think the original size that I created was 2,048MB. I try to shrink it,
> no change in size. I then learned that I have to backup the
> transactions so that they can be flagged as clearable (not sure the
> exact terms). So I did a backup via EM, shrink still wont work (via
> EM). I dont get errors, i just dont get the files to shrink in size. I
> will say, that I have shrunk the databases in the past succesfully, it
> just seems hit or miss.

First question:
Why are you letting it grow in the first place. You're better off
keeping it one size. (i.e. not growing and shrinking it.)

Second question: Where is most of the space, in the DB file or the log file?
If it's the log file most likely the "virtual log" is at the end of the
physical log file. (I thought SQL 2000 "fixed" this issue but I haven't
really looked into it.)

Suggestion:
Don't let DB grow by 10% if you do insist on using autogrowth. Use a fixed
amount. Otherwise each time it grows it'll grow by a larger amount each
time.

> I know point 2 is very vague, I'll try to recreate and post follow ups.
> running with SP4.
> Thanks for the links
> Rob