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,
MalcMalcolm
Make a long story shortly
1)Perfom BACKUP LOG file ( it removes all inactive transaction )
2)Pefrom DBCC SHRINKFILE to reduce physical size of the log file.
"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
>|||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
>|||Yes this is expected behavior. When you shrink a data file it has to
physically move all data at the end of the file towards the beginning and
each move is fully logged. I would shrink in smaller increments and backup
the log after each one to keep it in check.
--
Andrew J. Kelly
SQL Server MVP
"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
>|||Guys,
Thanks for the advice, but I'm not trying to shrink the log file. I'm
trying to shrink the *DATA* file. However, whenever I try this, the log
file starts growing like crazy.
Please advice further,
Malc
Christian Smith wrote:
>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
>>
>
>|||Andrew J. Kelly wrote:
>Yes this is expected behavior. When you shrink a data file it has to
>physically move all data at the end of the file towards the beginning and
>each move is fully logged. I would shrink in smaller increments and backup
>the log after each one to keep it in check.
>
Andrew,
That's interesting. If I understand you correctly, when trying to
recover some of this 61,000MB of slack space, I should do the following:
DBCC SHRINKFILE (MyDb_Data, 140000)
-> Backup log
DBCC SHRINKFILE (MyDb_Data, 135000)
-> Backup log
DBCC ... etc.
rather than:
DBCC SHRINKFILE (MyDb_Data)
or
DBCC SHRINKFILE (MyDb_Data, 85000)
If I do this, you're saying it will consume less log space?
Cheers,
Malc|||yes. shrinking the data file(s) will fill up your log file. you could try
setting your recovery mode to simple before doing the shrinkfile. you'll still
need some available space in your log file even with simple recovery.
Malcolm Ferguson wrote:
> Thanks for the advice, but I'm not trying to shrink the log file. I'm
> trying to shrink the *DATA* file. However, whenever I try this, the log
> file starts growing like crazy.|||What I am saying is it will allow you to control your log file by giving you
time to issue log backups in between the shrinks. That way your log file
won't grow on you. It also allows you to manage the process a bit better.
By the way you don't want to remove any where near all of your free space.
The database needs lots of free space to operate properly. The less free
space you have the more you risk fragmentating your tables when you reindex.
When you reindex a table you should ensure you have 1.2 to 1.5 times the
size of the table and indexes free and hopefully contiguous.
--
Andrew J. Kelly
SQL Server MVP
"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
message news:%23nlIL7j9DHA.3880@.TK2MSFTNGP11.phx.gbl...
> Andrew J. Kelly wrote:
> >Yes this is expected behavior. When you shrink a data file it has to
> >physically move all data at the end of the file towards the beginning and
> >each move is fully logged. I would shrink in smaller increments and
backup
> >the log after each one to keep it in check.
> >
> >
> Andrew,
> That's interesting. If I understand you correctly, when trying to
> recover some of this 61,000MB of slack space, I should do the following:
> DBCC SHRINKFILE (MyDb_Data, 140000)
> -> Backup log
> DBCC SHRINKFILE (MyDb_Data, 135000)
> -> Backup log
> DBCC ... etc.
> rather than:
> DBCC SHRINKFILE (MyDb_Data)
> or
> DBCC SHRINKFILE (MyDb_Data, 85000)
> If I do this, you're saying it will consume less log space?
> Cheers,
> Malc
>|||Andrew J. Kelly wrote:
>What I am saying is it will allow you to control your log file by giving you
>time to issue log backups in between the shrinks. That way your log file
>won't grow on you. It also allows you to manage the process a bit better.
>By the way you don't want to remove any where near all of your free space.
>The database needs lots of free space to operate properly. The less free
>space you have the more you risk fragmentating your tables when you reindex.
>When you reindex a table you should ensure you have 1.2 to 1.5 times the
>size of the table and indexes free and hopefully contiguous.
>
Thanks all for you help and advice. I was able to remove 20GB using the
suggestions, which leaves plenty of slack space in the datafile, and
leaves the free disk space at a sane level.
Cheers,
Malc
