Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Need to resize a SQL 2000 database

Posted on 2013-05-30
5
Medium Priority
?
274 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 49

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 27

Accepted Solution

by:
Zberteoc earned 1050 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 27

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 27

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

972 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