473,381 Members | 1,508 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,381 software developers and data experts.

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 5689
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Edwinah63 | last post by:
Hi Gurus, i am having problems with restoring a ms sql database. i have restored the database using veritas to a different location ('g:\datafiles') in no recover mode. when i view the...
19
by: dchow | last post by:
Our database size is currently 4G and is incrementing at a rate of 45M/day. What is the max size of a SQL database? And what is the size beyond which the server performance will start to go down?
2
by: trotter | last post by:
I want to know if there is a "best-practice" for setting up Database Maintenance Plans in SQL Server 7 or 2000. To be more specific, I want to know the order in which I complete the tasks. Do I...
2
by: Moti.Ba | last post by:
Hello, I need to restore the Master DB in my SQL 2k server (with sp3). The problem is that i don't have SQL backups of this file I only have general backup of the entire c:\ drive i made using...
3
by: Jon Jacobs | last post by:
I attempt to back up a database on one server and restore it on my local machine. This is what the query text looks like: restore database model from Disk='c:\JQJ\mydump\model.bak' with...
3
by: Tommy.Vincent | last post by:
Hi, its newbie question. I have one production server(A) ,one backup server(B),additional server(C). my database backup are moved from A to C in regular basis. My problem is that when...
5
by: Hassan Naqvi | last post by:
Hi, Basically, I am Java developer. In past I have played with Oracle using Java (JDBC). But this is the time to play with IBM DB2 using Java (JDBC). So kindly help this DB2 newbie. I have a...
3
by: Bill E. | last post by:
I'm trying to restore a database backed up a production machine onto my development machine. I don't want to restore the transaction logs (there are two) because they are far too large and are...
1
by: grzegorz.gazda | last post by:
I had to detach the database to delete the transaction log as it took the hole disk space. No I am trying to attach database, however I am not able to do so. The engine is asking me whether I...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.