SQL Server performace when attaching a Database
-
Hello all,
I'm involved in a project that requires a 1TB database to be migrated to a new SQL 2008 instance. I've successfully copied the existing data/log files to new storage, but attaching the database takes ~1.5 hours. My understanding is that Indexes are included with the data file I'm attaching to the new SQL Instance, so a Reorganize/Rebuild shouldn't be necessary. At this point, I'm unable to determine the underlying cause of such a long duration to attach the db. So, any insights are greatly appreciated.
Thank you
-
Have you tested doing a straight restore rather than attach? Also why are you using such an old version that is out of support (or very soon out of support if you have paid for extended)?
Thanks
-
Thanks for the reply.
We're constrained to SQL 2008 for licensing reasons. That's outside of my perview. The restore took a similar amount of time.
What I'm trying to grasp are the mechanics of what happens "under the hood" during a DB Attach and now DB Restore to determine if there's a way to improve the performance of either
- cg0x1 - Monday, October 22, 2018 9:08 AM
You can get more information on what is going on in the restore using trace flag 3004.
The following blog has some more information:
How It Works: What is Restore/Backup Doing?Sue
-
one of the possible issues is a big log file with lots and lots of virtual files
do this on the database and see how many you got. If over 500 I would say shrink it on the source instance and change the growth increment to be something like 5 or 6 GB at each increment
dbcc loginfo -
Thank you for this info.
I believe you've identified the crux of my dilemma. The log file for the database in question is 1 Terabtye. Why? Beats the sh!t outta' me.
Just so I can make the case fully informed, I have a few more questions:
Are these statements accurate?
1) I would not need a log file that goes back to the beginning of time to restore a database as long as I have a current, full-backup on hand
2) There is no cause to keep a log file for longer than a sensible duration of time (say, 1 week) with incremental-backups daily. -
Sue,
Thank you very much for the article link. I'm reviewing it now.
- cg0x1 - Monday, October 22, 2018 10:36 AM
regarding 1 and 2
you should periodically do full backups, and log backups frequently - frequency depends on the volumes of DML activity.
Some databases require these to be done every 5 min, others once a month, so no hard rule here.
UPDATE: Do read https://www.sqlservercentral.com/Forums/540904/how-long-do-you-keep-your-log-backupsOne of the main reasons for a log backup to grow so much is that db is in full recovery mode, and no log backups are taken.
Another big reason is if on a big database some huge transactions happened that grew the log a lot even if there are log backups in place. In this case even after the log backup the log file size remains. If this was the case and if it was a once off operation the log file should still be shrink.And do give us the count of the VLF's from the previous command.
and what is the output of this? replace db name with desired name.
SELECT
DB_NAME(db.database_id) DatabaseName,
(CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
(CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,
(CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
(CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB
FROM sys.databases db
LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id
LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id
LEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id
LEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id
where DB_NAME(db.database_id) = 'yourdbname' -
The VLF count is 591
The query provided produced these results:
- cg0x1 - Monday, October 22, 2018 10:36 AM
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Great article. Thanks for sharing
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply