Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need to resize a SQL 2000 database

Posted on 2013-05-30
5
Medium Priority
?
271 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
[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
  • 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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

688 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