THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Very Large Database Maintenance

I've got a third-party app that uses a SQL Server 2005 database. The database has grown to 190GB, with 150GB of that in one table (116M rows). I've been trying to get the greenlight to partition this table since June, and finally got it this weekend.

I wasn't able to start my process until Friday at noon, and had space on my backup drive to export the table data. (By law I have to keep two years worth of data in that table for auditing purposes - it has less than a month over that now.) I used bcp native mode to export the data and it took from Friday noon until 9am Sunday morning.

Once the data was exported I dropped the table. (Until this point all tables are in the PRIMARY filegroup, and it has three physical files in the group. Now I have two ndf files of 80-90GB each that have 16-18GB of data, the rest free. I need that space to allocate for the partition files, so I need to shrink the files.

I started the shrink (dbcc shrinkfile - sorry Paul [Randal], I know, but I need the space) at 9am. After 8 hours I decided it was not the correct solution.

I then built a new database to temporarily house the data so I could drop and recreate the application database with appropriate file sizes. The copy, using a combination of "select into" for small tables, and SSIS data transfer for the larger tables, took over 11 hours. By this time it was after 4:30am and the start of business was closing in rapidly. I knew I didn't have time to build the new database and get the data back in from the temporary database, so I abandoned the task. The import of the large table was causing enough performance problems that we've decided (in a meeting with the business owners) to restore the full database from the backup I have from before I dropped the large table.

The problem we have with the database still exists. That table grows by over 8 million rows per month, increasing by as much as 10 percent each month. All tables are in the PRIMARY file group so I have to backup data that stays constant every night.

The point really is that size makes a real difference in how to solve a problem. Solutions that work well on databases of 1 or 2 GB aren't at all appropriate for databases approaching 200GB. I have a couple of ideas on how I could have done some things differently, but of course they're based on the experience I gained in attempting to perform this maintenance.

Hindsight isn't 20/20, but it's close.


Published Monday, November 26, 2007 3:14 PM by AllenMWhite



a.m. said:

Hi Allen,

Why not just script out the full database, create a new empty version with the files the size you want them, then move all the data into the empty shell with SSIS?  Then drop the old database, rename the new database, and you're done.  No reason to have a "temporary" database and move the data back -- the second DB becomes your first DB when you're done with the copy.

November 26, 2007 2:52 PM

AllenMWhite said:

The problem there is lack of physical space on the SAN for the new database, Adam.  The environment is in a clustered environment, and so disk is at a premium.  I have to drop the existing database to create the new one, so the data has to live somewhere in the mean time.

November 26, 2007 2:57 PM

Denis Gobo said:

>>The problem there is lack of physical space on the SAN for the new database

I feel your pain, we maxed out our SAN also.

Do you have physical access to the server? If yes then can you temporarily  plug in a external 400GB HD move the DB there, script out the DB on the SAN and then populate that DB from the External HD?

November 26, 2007 3:08 PM

Kevin3NF said:

Would the TRUNCATEONLY  argument of DBCC Shrinkfile have helped here to free up space from those ndf files?  This may be an ignorant question, as I've never had cause to use it on a production database...

November 26, 2007 3:09 PM

Roger Breu said:

Hi Allen,

interesting post :) couldnt table partitioning be a solution? spread the new clustered index over several filegroups with multiple files and you have also the ability to make filegroup backups...

perhaps you couldn't shrink the file because there LOBs on your table, so an index reorganize with LOB-compaction gets you the space back...


November 26, 2007 3:15 PM

a.m. said:


You must have room somewhere for the temporary DB, otherwise you couldn't even move the data in at all, right?  So why not build it outside the SAN, then do a detach/attach and move the 16 GB back?  Still faster than doing the shrink, I bet :-)

November 26, 2007 3:30 PM

AllenMWhite said:

Wow, what a great community asset here!  OK, to answer everyone's questions - I had space on another drive on the SAN currently used for backups that I temporarily appropriated for the temporary database - Had I been thinking (and thus the benefit of experience) I'd have built the temporary database as the new database, copied the data, then detached it, moved the files and reattached it.  That's how I'd go about it now.  Kevin3NF, I tried the TRUNCATEONLY argument - it reduced the 80GB file to 73GB, even though I only had roughly 16GB used in the file.  Roger, partitioning the table was the objective of the work - I couldn't get that far because I couldn't get enough physical space cleared to create the partition files.

Thanks, everybody!

November 26, 2007 3:55 PM

Andrew Pike said:

You have my sympathy - I'm very familiar with such long weekends ;-(

The difficulty you experienced during this whole process would seem to be fundamentally one of I/O constraint.  I recently worked on a database migration which involved some similar tasks to those you followed.  I extracted the entire database using bcp with the -n option (we were migrating from 2000 to 2005, and moving from a SQL collation to a Windows collation).  However, that's where the similarities end; my bcp process exported the data from all tables in 1hr 8mins (approx. 135GB database at the time).  I'm staggered that the export of one 150GB table took nearly 21hrs.  Could you possibly investigate the I/O configuration in further detail?

November 26, 2007 6:04 PM

AllenMWhite said:

Thanks, Andrew - actually, the export of the 150GB table took 45 hours.  I believe the reason is the text column in the table and the internal logistics of getting that column data merged with the rest of the row data.  Because it's a 3rd party app I can't change anything structurally so I have to live with it as it is.

November 27, 2007 8:40 AM

StevenClark said:

I know this was a few months back, but I'm hoping that my recent experiences might be relevant.

We have a VERY large SQL 2K database.  VERY large in this case means around 1.7 Terabytes - no, that's not a typo - counting the transaction log,  it's currently reporting as 1,812,875.38 MB.

For Development and Test purposes, we have a copy of this database from which we aggressively archived (deleted) a lot of data.  This resulted in free space inside the database to the tune of about 900GB.  

We want to shrink the database down to a bare minimum of overhead space, so we can clone it again (the reduced size db).

I have tried DBCC shrinkfile, with the truncateonly and notruncate options, with no options, from the SQL Enterprise Manager , from the Query Analyzer etc...

There are 10 separate datafiles.

It takes FOREVER to run - around twenty hours - a shrinkfile on just one file, and so far the results have been bizarrely inconsistent.  Basically, a couple of the files have happily resized downwards, but the remainder have not shrunk, according to SQL Enterprise Manager, and the overall database size remains mych larger than we want  it to be.

Any thoughts on what we could do?

Btw.... a rough average size for the database files is 170 GB.

Thanks for listening


March 11, 2008 11:22 AM

JVS said:

I have a table with 650 million regs and 1,2TB.

I spend 2 months partitioning in smaller tables and now I need to drop the original table.

What is, in your opinion,  the best way to drop this table?

November 11, 2008 3:38 PM

Ashish said:

I have a database of 450GB on SQL 2005. There is free space of 200 GB. I want to shrink the database. There are tables of max sizes 30 GB

Would i able to free the space if i use the shrink Command directly on the database ?

Would it create any problems ?

April 22, 2010 3:51 AM
New Comments to this post are disabled

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog


Privacy Statement