Hey folks,

I'm pretty much a newbie as it comes to doing any DBA-type work on S2K
servers, so hence this message. I'm in a position at work where
everyone has to chip in as best he/she can, and since I know about some
databases (mainly MySQL and Oracle), guess who got volunteered for this?
:-)

We want to trim back some of our databases based on a target size on
disk. So for instance, if we say we want to hold no more than 30gb of
data in database X, then we want to dump database records and truncate
the database to that target size.

Now assuming I know enough about my database tables and relationships
that I can formulate the SQL necessary for estimating the row and table
sizes and dumping the actual data, the rest is really what I'm after.
I've ascertained that S2K meta-data (namely in the dbo.sysfiles table)
will tell me the size of the database file on disk by number of 8K
pages. But I've also noticed (in playing around with a sandbox
database) that it appears that the database allocation maybe larger than
the actual data in it, I assume to keep from adding extents or something
similiar? Again, my partial ignorance shines through... For example,
the following script truncated my sandbox database down from 104mb on
disk to 60-ish mb on disk without even removing a single record in the
single flat table I had in the database:

select 'Before', rtrim( name ) as Name, size*8192 as Bytes from sysfiles
where fileid = 1
dbcc shrinkdatabase( SandboxDB, 10, TRUNCATEONLY )
select 'After', rtrim( name ) as Name, size*8192 as Bytes from sysfiles
where fileid = 1

In summary, I'm not really asking someone to work out the logic for me.
I feel adequate to put it together myself for what we need with a
little bit of guidance. Perhaps someone pointing to a similar snippet
of code or a URL that would be helpful.

Finally, it's been suggested that this be done using DTS. My reading
and little bit of S2K knowledge leads me to beleive this is more
appropriate as an Agent Job. (We do want to be able to schedule, which
I know we can do via DTS or as an SAJ.)

Would love to hear some comments, tips, pointers or other helpful
suggestions here. Some references to existing code (there has to be
some other people that have done this sort of thing before) would be
really nice and greatly appreciated. Again, anything similar is
probably adequate to get me running in the right direction.

Thanks,
Chris

"Chris" <ceo (AT) nospan (DOT) on.net> wrote

When you create a database you give it an initial size. It creates a file
(or files) of that size, puts the system tables in and leaves the rest as
space within the file. You're right in that this means that SQL doesn't
have to faff around extending the file each time you add a row.
It sounds like what you need is an alert. You can set up an alert to
monitor the amount of free space in a database, and run a job to archive
data when the threshold is reached.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004