Solved

sql 2000 shrink database

Posted on 2006-07-24
12
655 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
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
 
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ 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 42

Expert Comment

by:EugeneZ
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Dynamics crm 2011 8 43
Help on Setting up an identical test database 17 51
SQL server 2008 SP4 29 33
Calculating Business Hours 19 62
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now