Solved

sql 2000 shrink database

Posted on 2006-07-24
12
672 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
12 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 168 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 166 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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
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 166 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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

717 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