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

Posted on 2014-08-05
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 68

    Assisted Solution

    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

    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

    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 68

    Assisted Solution


    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

    Thanks all.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    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…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    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.

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now