468,107 Members | 1,325 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,107 developers. It's quick & easy.

Bloated database wont shrink after backup/restore SQL 2000

Help,
I have a database that has a data file of 2GB and a log file of 31GB.
In enterprise manager, when I choose shrink it says there is 30GB of
unused space. When I shrink the database, it does not shrink,
(however it says it has completed).
I've done a complete backup, tried shrink again, no dice. I then
backed up the database (which the backup was 1.9GB), deleted the
database and made a new database with 2,048MB for the data and same
for the log file.
When I restore, the log file jumps up to 31GB again. When I check the
space when I use the shrink, it again says I have 30GB of unused
space.
How on earth do I get this file to shrink?
I've been able to shrink other databases, but not this one.
TIA
Rob
Jul 20 '05 #1
1 5436
Hopefully one of these articles may help you out

317375 INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/?id=317375
272318 INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://support.microsoft.com/?id=272318
256650 INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/?id=256650
315512 INF: Considerations for Autogrow and Autoshrink Configuration
http://support.microsoft.com/?id=315512
272093 INF: Effects of Nonlogged and Minimally Logged Operations on
Transaction http://support.microsoft.com/?id=272093
230785 INF: SQL Server 7.0 and SQL Server 2000 Logging and Data Storage
http://support.microsoft.com/?id=230785
295108 INF: Incomplete Transaction May Hold Large Number of Locks and Cause
http://support.microsoft.com/?id=295108
--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know
"R Camarda" <rc******@cablespeed.com> wrote in message
news:d7**************************@posting.google.c om...
Help,
I have a database that has a data file of 2GB and a log file of 31GB.
In enterprise manager, when I choose shrink it says there is 30GB of
unused space. When I shrink the database, it does not shrink,
(however it says it has completed).
I've done a complete backup, tried shrink again, no dice. I then
backed up the database (which the backup was 1.9GB), deleted the
database and made a new database with 2,048MB for the data and same
for the log file.
When I restore, the log file jumps up to 31GB again. When I check the
space when I use the shrink, it again says I have 30GB of unused
space.
How on earth do I get this file to shrink?
I've been able to shrink other databases, but not this one.
TIA
Rob

Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Edwinah63 | last post: by
19 posts views Thread by dchow | last post: by
3 posts views Thread by Jon Jacobs | last post: by
3 posts views Thread by Tommy.Vincent | last post: by
5 posts views Thread by Hassan Naqvi | last post: by
3 posts views Thread by Bill E. | last post: by
1 post views Thread by grzegorz.gazda | last post: by
1 post views Thread by Solo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.