Need to resize a SQL 2000 database

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.
3DiscoveryAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ZberteocConnect With a Mentor Commented:
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
 
PaulCommented:
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
 
3DiscoveryAuthor Commented:
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
 
ZberteocCommented:
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
 
ZberteocCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.