Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


How long does it take to resize a 200 GB SQL database by 50%?

Posted on 2014-08-05
Medium Priority
Last Modified: 2014-08-12
We have a 200 GB production system which I'd like to resize to 300 GB.  Approximately how long does it take to resize the database and what will be the impact on my users?    Also, please confirm that this can be done without taking the database offline.
Question by:PetEdge
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 total points
ID: 40242007
You can do it without prevent others from using the db.  They might see some slowdown, depending on the disk subsystem's capacity.

How long will directly depend on disk speed and how much data SQL has to move to 'repack' existing data into a smaller space.  There's no way to generically state how long it will take.
LVL 32

Assisted Solution

by:Brendt Hess
Brendt Hess earned 500 total points
ID: 40242069
How long this expansion would take depends on so many factors - how busy is the disk subsystem, how fragmented is the disk subsystem, how fast is the disk subsystem - notice a common feature? The disk subsystem performance is very important! But other subsystems (memory, CPU, etc) also have an effect.  The optimal speed would happen when you had the most cpu and memory free, on a totally defragmented disk with continuous free space blocks of 100GB or more.

Without knowing a lot about the technical specifications of your system, and probably without having used a comparable system, I can not provide an accurate estimate.  The good news? This can be done online.

My recommendation would be to perform this at the slowest point on your system (disk, cpu, and memory), with the data location as clean, defragmented, and contiguous as possible.
LVL 75

Accepted Solution

Anthony Perkins earned 500 total points
ID: 40242764
i cannot imagine it would take longer than 15 minutes even on a slow system.  But as everyone has pointed out it depends.  One of the biggest boosts you can have is if you have enabled IFI.   See here for an article on the subject:
How and Why to Enable Instant File Initialization  But I suspect that if you are asking then it is probably not enabled or you would have pointed it out.

Having said all of this.  Try increasing it by say 20GB and see how long it takes.  As has been mentioned you cannot automatically assume it will take you 5x to reach 100GB, but it should give you a good idea.
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 total points
ID: 40243798

Oops, I misread/misunderstood.  I assumed you were trying to shrink from 200GB to 100GB.

Increasing from 200GB to 300GB will be virtually instantaneous if IFI is on.  If not, the time will vary according to disk speed.

Author Closing Comment

ID: 40256589
Thanks all.

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

580 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