Solved

Need to resize a SQL 2000 database

Posted on 2013-05-30
5
267 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 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 27

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

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…
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
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

738 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