Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

sql 2000 shrink database

Posted on 2006-07-24
12
Medium Priority
?
686 Views
Last Modified: 2010-05-18
im trying to shrink my database to get some disk space back
i created a databse maintnace plan to shrink the file
it runs and says it succeded  in the logs but the database does not shrink
databse size is 16gb
12 gb used
4 gb free space
when i schedule i set 5% free space
why isnt the database shrinking and relesing the free space ?
im trying to get back 3 gigs of much needed space
0
Comment
Question by:dano992
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 672 total points
ID: 17169122
I hope you are shrinking the log files ?
The log file is internally divided into smaller virtual logs and shrinking will only happen when the active part of the log is at the
beginning of your log file. You can use the dbcc loginfo(dbname) command to see where the active part of the transaction log is - any logs marked with a Status of 2 are active.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17169127
Alternatively you can change the recovery model to 'Simple;
0
 
LVL 14

Assisted Solution

by:shahprabal
shahprabal earned 664 total points
ID: 17169187
What happens when do try to shrink the database manually?

i.e : Right Click on the database > All Tasks > Shrink Database.

Also if hdd space is more important than performance then you can reduce the amount of space that the file auto-grows to by going in database's property.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17169386
Shrinking the database will just cause the files to need to grow again immediately as new transactions arrive; that in turn will take more CPU effort, reduce the availability of the system during the extension (which may lead to application errors & timeouts) and increase the fragmentation of the file.

Paul Randal (Lead Program Manager, Microsoft SQL Server Storage Engine) said i:

"Don't shrink your database. Shrink causes index fragmentation - I've specifically documented this in the BOL for SQL Server 2005.
0
 
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 664 total points
ID: 17170509
try to use
DBCC SHRINKFILE  (in your DB run  select name from sysfiles)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_8b51.asp
0
 

Author Comment

by:dano992
ID: 17179284
i plan on leaving 1 gb of free space on the database for grouth
i would like the other 3 gigs back as im short on disk space
i never heard of shrinking the database is bad
plus can i just reindex the database after the shrink?
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 17183581
sure, you can do anything after the shrink
just check - maybe you will need to shrink db after reindexing
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17603119
I would suggest splitting the points
0
 
LVL 14

Expert Comment

by:shahprabal
ID: 17603596
ditto
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question