Re: Does DBCC SHRINKFILE on a data file cause the log to grow?

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Christian Smith (csmith_at_digex.com)
Date: 02/18/04 

Date: Wed, 18 Feb 2004 10:46:36 -0500

Sometimes, if this fails, you have to put some transactions into the
database to roll the virtual log to the beginning of the physical log. I've
had success with:

1) Backup the log
2) Run DBCC SHRINKFILE
3) If step 2 does not work, create a temp table in the database and add 1000
rows.
4) Delete the rows and the temp table. This will create t-log entries that
will force the virtual log to roll to the frony of the physical file (See
BOL for details on t-log architecture).
5) Backup the log.
6) Run DBCC SHRINKFILE again. It should work this time. If not, repeat
from 3.

The process is documented in a Q article somewhere for SQL 7. You are not
supposed to need it for SQL 2000 but I havbe found it comes in handy.

Christian Smith

"Malcolm Ferguson" <Malcolm_Ferguson@NO_SPAM_PLEASEyahoo.com> wrote in
message news:ekOInAj9DHA.2472@TK2MSFTNGP10.phx.gbl...
> We have 60GB free of a 140GB data file in one of our databases in SQL
> Server 2000 SP2. I've truncated the free space at the end, but it won't
> shrink any further now. I tried running DBCC SHRINKFILE, but had to
> cancel it and run a log backup when the transaction log grew to 40GB and
> started threatening the free disk space. Is this expected behaviour.
> If it is, can anybody recommend a way to shrink the data file without
> growing the log - e.g. do I have to temporarily change the database
> model to simple, or something like that?
>
> Cheers,
> Malc
>