hi,

is there a recommended procedure for shrinking a SQL 2000 database
file to minimal size? we have tried a lot of methods but so far have
come up with only one that works (or worked for over a year):
1) creating a new database;
2) exporting the old database (including objects and extended
objects) to the new database;

unfortunately, this doesn't work at the moment -- after lots of
exporting the process gives up with a "bulk copy failed" error. (we're
trying to figure out where -- is there any way to get a more helpful
error message? we've stored the export as a DTS package and activated
logging to a file. however, this logging could be more helpful imho.
what do I learn from this:
Step 'Copy SQL Server Objects' failed

Step Error Source: Microsoft SQL-DMO
Step Error Description:[SQL-DMO]The Bulk Copy execution failed.
Step Error code: 80045707
Step Error Help File:SQLDMO80.hlp
Step Error Help Context ID:1131

Step Execution Started: 06.12.2002 08:57:44
Step Execution Completed: 06.12.2002 09:19:27
Total Step Execution Time: 1303,359 seconds
Progress count in Step: 0

couldn't it at least mention a table name or something? (row ID would
be a bonus!)


but back to the original question: why isn't there a command or
procedure that works to shrink a database file? (i grant you it's a
non-trivial assignment -- garbage collecting etc. in a running
system.) from such recent posts as:
http://groups.google.com/groups? q=...sftngp04&rnum=5

i sense that there is no easy answer at the moment. why not?

cheers,


Tim Hanson

Report this post to a moderator

I'm obviously mssing something but why doesn't
DBCC SHRINKFILE do the job for you. With
DBCC DBREINDEX to compact your tables and
DBCC SHRINKFILE to actually decrease the
file size you should be covered. Why are you
shrinking the database anyway ? Maybe a few
more details of what you are trying to achieve
would prompt a few more useful answers.

--
HTH

Jasper Smith (SQL Server MVP)

I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org

"Tim Hanson" <t.hanson@faz.de> wrote in message
news:adacec79.0212060101.193ce803@posting.google.com...
> hi,
>
> is there a recommended procedure for shrinking a SQL 2000 database
> file to minimal size? we have tried a lot of methods but so far have
> come up with only one that works (or worked for over a year):
> 1) creating a new database;
> 2) exporting the old database (including objects and extended
> objects) to the new database;
>
> unfortunately, this doesn't work at the moment -- after lots of
> exporting the process gives up with a "bulk copy failed" error. (we're
> trying to figure out where -- is there any way to get a more helpful
> error message? we've stored the export as a DTS package and activated
> logging to a file. however, this logging could be more helpful imho.
> what do I learn from this:
> Step 'Copy SQL Server Objects' failed
>
> Step Error Source: Microsoft SQL-DMO
> Step Error Description:[SQL-DMO]The Bulk Copy execution failed.
> Step Error code: 80045707
> Step Error Help File:SQLDMO80.hlp
> Step Error Help Context ID:1131
>
> Step Execution Started: 06.12.2002 08:57:44
> Step Execution Completed: 06.12.2002 09:19:27
> Total Step Execution Time: 1303,359 seconds
> Progress count in Step: 0
>
> couldn't it at least mention a table name or something? (row ID would
> be a bonus!)
>
>
> but back to the original question: why isn't there a command or
> procedure that works to shrink a database file? (i grant you it's a
> non-trivial assignment -- garbage collecting etc. in a running
> system.) from such recent posts as:
>
http://groups.google.com/groups? q=...sftngp04&rnum=5
>
> i sense that there is no easy answer at the moment. why not?
>
> cheers,
>
>
> Tim Hanson

Report this post to a moderator

Thanks, Jasper.

I have been under the impression for some time that
dbcc shrinkfile
doesn't -- at least not with databases restored from our production
database. But you're right, some background makes oodles of sense.

We have a database supporting our web site that has grown over roughly
2 years to be something like 10Gb in size. For various reasons
developers need a much smaller version which in effect contains "new
data only" (we have a script which purges most documents older than
say 4 weeks, most users who haven't logged on in the last 3 weeks, and
a bunch of other data that a developer can live without.)

When I restore from a nightly back-up to a test machine, then run this
purge script I have a DB that is roughly 10Gb in size but with only
about 300Mb actually in use.

I have tried shrinkfile and shrinkdatabase (and will be happy to try
dbreindex) but have never seen the DB file (or resulting backups, or
resulting DBs restored from those backups) actually shrink.

Hence a year or more ago we came up with an alternative approach:
1) restore a copy from backup
2) purge it
3) build a new empty DB
4) export the purged contents into the new empty DB (this one is
compact)
5) back up the compact one, zip it, let developers at it.

Recently we've started to have trouble with this procedure.

However if it's possible to get the standard commands to work I'd much
prefer it. (The only added benefit to the export approach that I can
think of is that all the replication "baggage" is discarded.)

I'd be grateful for any advice. I'll gladly go through a dbcc
shrink... drill one more time and report results if that would help.

Best regards,


Tim Hanson




"Jasper Smith" <jasper_smith9@hotmail.com> wrote in message news:<OIXBWtSnCHA.2344@TK2MSFTNGP11>...
> I'm obviously mssing something but why doesn't
> DBCC SHRINKFILE do the job for you. With
> DBCC DBREINDEX to compact your tables and
> DBCC SHRINKFILE to actually decrease the
> file size you should be covered. Why are you
> shrinking the database anyway ? Maybe a few
> more details of what you are trying to achieve
> would prompt a few more useful answers.

Report this post to a moderator

To follow up, I tried the following steps...

1) dbcc shrinkdatabase('IN4_20021204',
5)
(it runs a long time then returns:
DbId FileId CurrentSize Minimu
mSize UsedPages EstimatedPages

18 1 1421208 96 227472 227472
18 2 736384 63 736384 56

2) dbcc shrinkfile(1,100)
(it runs a long time, then returns:
18 1 1421208 96 197912 197912

3) dbcc shrinkfile(2,7)

18 2 1078152 63 1078152 56

the file IN4_20021204.mdf is still 11.3Gb in size.

are there other steps I should try?

thanks,

Tim Hanson

Report this post to a moderator

In case anyone is curious, we still haven't gotten dbcc shrinkfile to
work (though we're still very interested in learning how), but we did
work around our "make a shrunk DB copy for developers" problem:
1) we "scripted" the creation of the DB;
2) tweaked the script (change the name, ask for much smaller files)
and divided it into 3 parts:
a) create the DB and the tables;
b) copy the contents;
c) create all the constraints, indices, triggers, functions, ...

3) we then ran part a, copied the contents (data only), and finally
ran part c

we're not quite sure why this works better than clicking through the
"export wizard", but evidently some checks aren't applied during the
bulk copies using our more manual approach that *are* applied using
the wizard. does that sound plausible?

cheers,

tbh

"guess I'll stand over here on the yellow line like an idiot talking
to myself" -- Uh, Clem, FST, Bozos

Report this post to a moderator

After much pulling of hair, gnashing of teeth and throwing of tantrums I
finally got dbcc shrinkfile to work successfully and consistently.

The T-SQL to do do it is thus:

use <dbname>
go

backup database <dbname> to disk='<filename>' with init
<take whatever action you want if backup fails>
restore verifyonly from disk='<filename>'
<take whatever action you want if restore fails>
go

backup log <dbname> with truncate_only
<take whatever action you want if backup fails>
go

dbcc shrinkfile (N'<dbname>',<minsize> )
go

dbcc shrinkfile (N'<dblogname>',<minsize> )
go

The secret seems to be in the backup log <dbname> with truncate_only batch
otherwise the dbcc shrinkfile batches appear to have no effect. Also, it
does not seem to work if all the commands are in the same batch.


"Tim Hanson" <t.hanson@faz.de> wrote in message
news:adacec79.0212100222.68e7b01d@posting.google.com...
> In case anyone is curious, we still haven't gotten dbcc shrinkfile to
> work (though we're still very interested in learning how), but we did
> work around our "make a shrunk DB copy for developers" problem:
> 1) we "scripted" the creation of the DB;
> 2) tweaked the script (change the name, ask for much smaller files)
> and divided it into 3 parts:
> a) create the DB and the tables;
> b) copy the contents;
> c) create all the constraints, indices, triggers, functions, ...
>
> 3) we then ran part a, copied the contents (data only), and finally
> ran part c
>
> we're not quite sure why this works better than clicking through the
> "export wizard", but evidently some checks aren't applied during the
> bulk copies using our more manual approach that *are* applied using
> the wizard. does that sound plausible?
>
> cheers,
>
> tbh
>
> "guess I'll stand over here on the yellow line like an idiot talking
> to myself" -- Uh, Clem, FST, Bozos

Report this post to a moderator

Thanks, Stephany!

Much appreciated. That sounds plausible. I know from experience that
we have used something that resembles part of your recipe to shrink
transaction log files successfully. (Often seeming to need to do two
steps twice to get them to work. Pas de deux, as it weux.

We will try your approach next time!

Thanks again,

Tim Hanson

"Stephany Young" <stephany@sysoft.co.nz> wrote in message news:<ucHENcIoCHA.2392@TK2MSFTNGP12>...
> After much pulling of hair, gnashing of teeth and throwing of tantrums I
> finally got dbcc shrinkfile to work successfully and consistently.
....

Report this post to a moderator

Instead of doing the backup log WITH TRUNCATE_ONLY, why not have a proper backup routine in
place and do a real log backup (not using TRUNCATE ONLY option)?

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver


"Stephany Young" <stephany@sysoft.co.nz> wrote in message news:ucHENcIoCHA.2392@TK2MSFTNGP12...
> After much pulling of hair, gnashing of teeth and throwing of tantrums I
> finally got dbcc shrinkfile to work successfully and consistently.
>
> The T-SQL to do do it is thus:
>
> use <dbname>
> go
>
> backup database <dbname> to disk='<filename>' with init
> <take whatever action you want if backup fails>
> restore verifyonly from disk='<filename>'
> <take whatever action you want if restore fails>
> go
>
> backup log <dbname> with truncate_only
> <take whatever action you want if backup fails>
> go
>
> dbcc shrinkfile (N'<dbname>',<minsize> )
> go
>
> dbcc shrinkfile (N'<dblogname>',<minsize> )
> go
>
> The secret seems to be in the backup log <dbname> with truncate_only batch
> otherwise the dbcc shrinkfile batches appear to have no effect. Also, it
> does not seem to work if all the commands are in the same batch.
>
>
> "Tim Hanson" <t.hanson@faz.de> wrote in message
> news:adacec79.0212100222.68e7b01d@posting.google.com...
> > In case anyone is curious, we still haven't gotten dbcc shrinkfile to
> > work (though we're still very interested in learning how), but we did
> > work around our "make a shrunk DB copy for developers" problem:
> > 1) we "scripted" the creation of the DB;
> > 2) tweaked the script (change the name, ask for much smaller files)
> > and divided it into 3 parts:
> > a) create the DB and the tables;
> > b) copy the contents;
> > c) create all the constraints, indices, triggers, functions, ...
> >
> > 3) we then ran part a, copied the contents (data only), and finally
> > ran part c
> >
> > we're not quite sure why this works better than clicking through the
> > "export wizard", but evidently some checks aren't applied during the
> > bulk copies using our more manual approach that *are* applied using
> > the wizard. does that sound plausible?
> >
> > cheers,
> >
> > tbh
> >
> > "guess I'll stand over here on the yellow line like an idiot talking
> > to myself" -- Uh, Clem, FST, Bozos
>
>

Report this post to a moderator

Tried that.

Experience has shown that the shrinkfile only works after the backup log
WITH TRUNCATE_ONLY.

Also, the preceding full backup of the database renders the content of the
logfile redundant.

"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@cornerstone.se>
wrote in message news:uo5MrePoCHA.456@TK2MSFTNGP08...
> Instead of doing the backup log WITH TRUNCATE_ONLY, why not have a proper
backup routine in
> place and do a real log backup (not using TRUNCATE ONLY option)?
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
>
>
> "Stephany Young" <stephany@sysoft.co.nz> wrote in message
news:ucHENcIoCHA.2392@TK2MSFTNGP12...
> > After much pulling of hair, gnashing of teeth and throwing of tantrums I
> > finally got dbcc shrinkfile to work successfully and consistently.
> >
> > The T-SQL to do do it is thus:
> >
> > use <dbname>
> > go
> >
> > backup database <dbname> to disk='<filename>' with init
> > <take whatever action you want if backup fails>
> > restore verifyonly from disk='<filename>'
> > <take whatever action you want if restore fails>
> > go
> >
> > backup log <dbname> with truncate_only
> > <take whatever action you want if backup fails>
> > go
> >
> > dbcc shrinkfile (N'<dbname>',<minsize> )
> > go
> >
> > dbcc shrinkfile (N'<dblogname>',<minsize> )
> > go
> >
> > The secret seems to be in the backup log <dbname> with truncate_only
batch

> > otherwise the dbcc shrinkfile batches appear to have no effect. Also, it
> > does not seem to work if all the commands are in the same batch.
> >
> >
> > "Tim Hanson" <t.hanson@faz.de> wrote in message
> > news:adacec79.0212100222.68e7b01d@posting.google.com...
> > > In case anyone is curious, we still haven't gotten dbcc shrinkfile to
> > > work (though we're still very interested in learning how), but we did
> > > work around our "make a shrunk DB copy for developers" problem:
> > > 1) we "scripted" the creation of the DB;
> > > 2) tweaked the script (change the name, ask for much smaller files)
> > > and divided it into 3 parts:
> > > a) create the DB and the tables;
> > > b) copy the contents;
> > > c) create all the constraints, indices, triggers, functions, ...
> > >
> > > 3) we then ran part a, copied the contents (data only), and finally
> > > ran part c
> > >
> > > we're not quite sure why this works better than clicking through the
> > > "export wizard", but evidently some checks aren't applied during the
> > > bulk copies using our more manual approach that *are* applied using
> > > the wizard. does that sound plausible?
> > >
> > > cheers,
> > >
> > > tbh
> > >
> > > "guess I'll stand over here on the yellow line like an idiot talking
> > > to myself" -- Uh, Clem, FST, Bozos
> >
> >
>
>

Report this post to a moderator

> Experience has shown that the shrinkfile only works after the backup log
> WITH TRUNCATE_ONLY.

I haven't heard anything along those lines... I'd like to see such install to see what the case
is. The two BACKUP LOG commands are the same with the exception what if you don't specify WITH
TRUNCATE_ONLY then the log backup file will be generated. Internally in the log, the same things
are performed.
OTOH, I don't do shrink on a general basis, as I don't like constant grow/shrink...


> Also, the preceding full backup of the database renders the content of the
> logfile redundant.

I don't agree. I never set up a production environment without doing log backups. Log backups
has so many advantages, so I won't even begin to list them here...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver


"Stephany Young" <stephany@sysoft.co.nz> wrote in message news:#oUF6zPoCHA.1612@TK2MSFTNGP10...
> Tried that.
>
> Experience has shown that the shrinkfile only works after the backup log
> WITH TRUNCATE_ONLY.
>
> Also, the preceding full backup of the database renders the content of the
> logfile redundant.
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@cornerstone.se>
> wrote in message news:uo5MrePoCHA.456@TK2MSFTNGP08...
> > Instead of doing the backup log WITH TRUNCATE_ONLY, why not have a proper
> backup routine in
> > place and do a real log backup (not using TRUNCATE ONLY option)?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=...ublic.sqlserver
> >
> >
> > "Stephany Young" <stephany@sysoft.co.nz> wrote in message
> news:ucHENcIoCHA.2392@TK2MSFTNGP12...
> > > After much pulling of hair, gnashing of teeth and throwing of tantrums I
> > > finally got dbcc shrinkfile to work successfully and consistently.
> > >
> > > The T-SQL to do do it is thus:
> > >
> > > use <dbname>
> > > go
> > >
> > > backup database <dbname> to disk='<filename>' with init
> > > <take whatever action you want if backup fails>
> > > restore verifyonly from disk='<filename>'
> > > <take whatever action you want if restore fails>
> > > go
> > >
> > > backup log <dbname> with truncate_only
> > > <take whatever action you want if backup fails>
> > > go
> > >
> > > dbcc shrinkfile (N'<dbname>',<minsize> )
> > > go
> > >
> > > dbcc shrinkfile (N'<dblogname>',<minsize> )
> > > go
> > >
> > > The secret seems to be in the backup log <dbname> with truncate_only
> batch
> > > otherwise the dbcc shrinkfile batches appear to have no effect. Also, it
> > > does not seem to work if all the commands are in the same batch.
> > >
> > >
> > > "Tim Hanson" <t.hanson@faz.de> wrote in message
> > > news:adacec79.0212100222.68e7b01d@posting.google.com...
> > > > In case anyone is curious, we still haven't gotten dbcc shrinkfile to
> > > > work (though we're still very interested in learning how), but we did
> > > > work around our "make a shrunk DB copy for developers" problem:
> > > > 1) we "scripted" the creation of the DB;
> > > > 2) tweaked the script (change the name, ask for much smaller files)
> > > > and divided it into 3 parts:
> > > > a) create the DB and the tables;
> > > > b) copy the contents;
> > > > c) create all the constraints, indices, triggers, functions, ...
> > > >
> > > > 3) we then ran part a, copied the contents (data only), and finally
> > > > ran part c
> > > >
> > > > we're not quite sure why this works better than clicking through the
> > > > "export wizard", but evidently some checks aren't applied during the
> > > > bulk copies using our more manual approach that *are* applied using
> > > > the wizard. does that sound plausible?
> > > >
> > > > cheers,
> > > >
> > > > tbh
> > > >
> > > > "guess I'll stand over here on the yellow line like an idiot talking
> > > > to myself" -- Uh, Clem, FST, Bozos
> > >
> > >
> >
> >
>
>

Report this post to a moderator