January 26, 2010 at 8:56 pm
Alright,
So interesting question. Due to some wonderful programming by one of our .NET developers, we had an audit table grow to some stupidly large number in just under 1 month (70 million records). This audit table takes up 70% of the entire database, which is now 50GB. While normally, I wouldn't bother with a database shrink (I would just remove the bad records), I am also in the process of moving this database to another server. Of course restoring a 50 gb database takes a lot longer than a 15 GB database.
So here is the question.
Shrinking the database will cause fragmentation. However, if I shrink the database, then backup and restore the database to another server, does this solve the fragmentation issue caused by shrinking the database?
Thanks,
Fraggle
January 26, 2010 at 9:19 pm
Backing up the database will only backup the used portion of the data file and then restore that portion. The extra time to process is due to creating the correct file size on disk. Thus, if you create the database on the destination server first (with the full data file sizes), then your restore will take less time. If you create the destination database in parallel with backing up your source database, you could save time overall.
Or, you could do it as you said and then make sure you run maintenance on the restored database.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 26, 2010 at 9:26 pm
You are correct there.
However, I am still curious to know the answer to the question. I would rather have the 15 GB database on the server and not the 50GB. Genearal waste of space, and I doubt that over the next year, the database will even grow to 20GB as it grows at a rate of about 1-3% per year depending upon how busy the client is.
Fraggle.
January 26, 2010 at 9:33 pm
Fraggle-805517 (1/26/2010)
You are correct there.However, I am still curious to know the answer to the question. I would rather have the 15 GB database on the server and not the 50GB. Genearal waste of space, and I doubt that over the next year, the database will even grow to 20GB as it grows at a rate of about 1-3% per year depending upon how busy the client is.
Fraggle.
IMHO, leave the database size as is. 50GB is not big at all. It is better to leave it at that size and thus decrease the need to grow the file which is far more detrimental to the database and transactions (performance wise). Furthermore, more and more requirements are surfacing that require auditing. IF you must turn auditing back on, you would be set for some auditing immediately without persistent database growths. I would also be more concerned about log file fragmentation and vlf's.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 26, 2010 at 10:22 pm
If you are absolutely, totally sure that the DB will never grow that big again, do a once-off shrink of the data file to a reasonable size (20GB from what you said) and afterwards rebuild all your indexes. Don't shrink the log file
Restore puts the DB back exactly as it was when it was backed up, it will not change the index fragmentation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 27, 2010 at 5:13 am
Gail,
Thank you very much. That is what I needed to know.
Fraggle
January 27, 2010 at 8:43 am
CirquedeSQLeil (1/26/2010)
Backing up the database will only backup the used portion of the data file and then restore that portion. The extra time to process is due to creating the correct file size on disk. Thus, if you create the database on the destination server first (with the full data file sizes), then your restore will take less time. If you create the destination database in parallel with backing up your source database, you could save time overall.Or, you could do it as you said and then make sure you run maintenance on the restored database.
Jason - this is not correct. When you restore a database from a backup and overwrite and existing database, the original files are removed first - then new files are created. Creating a database first has no effect on restoring except to waste time.
On SQL Server 2005 and above, you now have the ability to setup instant initialization. Using instant initialization, the files that are created would not be zeroed - meaning the actual restore process can begin much earlier in the process reducing the amount of time it takes to restore.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 27, 2010 at 8:51 am
Jeffrey Williams-493691 (1/27/2010)
Using instant initialization, the files that are created would not be zeroed
Using instant initialization, the data files that are created would not be zeroed. The log files still get zeroed out. They do not and cannot take advantage of instant initialisation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 27, 2010 at 10:56 am
Hmmm. Without instant initialization my experience has been that I save several hours on my restore jobs (300GB DBs) when restoring to a database where the files already exist. If I create the database first, it takes about 10-20 mins (based on previous personal experience) to grow it to 300GB. I would take that time savings.
From Microsoft
Optimizing Restore Performance
Restoring a database or differential backup consists of four steps:
Creating the database and transaction log files if they do not already exist.
Copying the data from the backup devices to the database files.
Copying the transaction log from the transaction log files.
Rolling forward the transaction log, and then restarting recovery if necessary.
Applying a transaction log backup consists of two steps:
Copying data from the backup devices to the transaction log file.
Rolling forward the transaction log.
Restoring a database file consists of two steps:
Creating any missing database files.
Copying the data from the backup devices to the database files.
File Initialization
If the database and transaction log files do not already exist, they must be created before data can be restored to them. The database and transaction log files are created and the file contents initialized to zero. Separate worker threads create and initialize the files in parallel. The database and transaction log files are sorted by disk device, and a separate worker thread is assigned to each disk device. Because creating and initializing files requires very high throughput, spreading the files evenly across the available logical drives yields the highest performance.
Instant File Initialization
In SQL Server 2005 and later versions, data files can be initialized instantaneously, allowing for fast execution of database or filegroup restore operations. Instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files. Log file initialization still requires zeroing, but it will happen in parallel with the transfer of the data from the backup. The roll forward step of restore will not start until all of the data has been transferred and the whole log has been initialized.
The zeroing out of the files is much faster than to claim disk space and grow the file. Using Instant File Initialization would provide yet another boost - but I have as yet to use that in my restores. Now, if you don't run the tasks in parallel - there would be time wasted, but not as much as restoring to a server where the database doesn't exist.
In this case, 50GB is not going to take that long - and the time differences would not be as much as they were for me.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 28, 2010 at 8:04 am
Experts....Just to remind :-
Instant file initialization is available only on Microsoft Windows XP Professional or Windows Server 2003 or later versions. 😎
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply