Solved

sql 2000 shrink database

Posted on 2006-07-24
12
662 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

803 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