Solved

Need to resize a SQL 2000 database

Posted on 2013-05-30
5
263 Views
Last Modified: 2013-05-30
I was asked to assist in converting a low use SQL 2000 server to a virtual machine for a small network.  This server is primarily used for historical trend data and has had the historical database file size is configured to 450GB on a 600GB drive.  There is approximately two years worth of historical data stored on the database in which only 34 of the 450GB is used.  Why the database file size was configured for 450GB in the first place is beyond me.

I've read that there has been issues with shrinking a database on a regular basis, but is there anything to be worried about when doing this as a one time thing?

Also, I've never had to shrink a database before.  What is the best way to shrink the database to a more reasonable size?  Say 50GB.
0
Comment
Question by:3Discovery
  • 3
5 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39207490
I don't know the answer but this makes for relevant (I think) and interesting reading...
http://blog.sqlauthority.com/2011/01/19/sql-server-shrinking-database-is-bad-increases-fragmentation-reduces-performance/
I don't know the ins and outs of sql 2000 so I'm not going to do much more than this
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 350 total points
ID: 39207737
As a part of resource planning is a good practice to estimate the maximum size for a database size for both data and log files. In the link given above you will see that the main reason is to avoid database files increases, which can affect performance if there are too often, i.e. the amount set for increase is small compared to the real needs. Once you set up the size to a maximum planned it doesn't really make sense to shrink it and on top of that shrinking will cause a lot of fragmentation, which also causes performance problems.

Coming back to your task, the fact that you have to create a VM for that server should not matter when it comes to the database size, which should be kept the same as it was initial planned. If for some reason maybe that planning proves to be unrealistic to the actual needs then you can consider a database re sizing. Keep in mind that the size of the backup files are not affected by the unused space in the database but when you restore from them the original size will be preserved.
0
 

Author Comment

by:3Discovery
ID: 39207873
So if I was to build the VM from scratch and restore the SQL database from a BAK backup, it would restore the 450MB size?  That won't work.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39208185
You are correct. In that case you have no choice but shrink the database to its actual used size and after that do a backup or just copy the data file and log file over and attach them.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39208818
I recommend to run index rebuilds after the database shrink to eliminate fragmentation that are created by shrinking. You can use the maintenance plan wizard for that. Also make sure that your database has enough room on the VM to grow and set the growth ratio for the data file(.mdb)to a value and not a percentage, like 512MB. Same for the log file(.ldf).
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

895 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

13 Experts available now in Live!

Get 1:1 Help Now