Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sql 2000 shrink database

Posted on 2006-07-24
12
Medium Priority
?
682 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 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

609 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