SQL Server performace when attaching a Database

  • cg0x1

    Valued Member

    Points: 73

    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

  • NorthernSoul

    SSCertifiable

    Points: 6765

    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

  • cg0x1

    Valued Member

    Points: 73

    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

  • Sue_H

    SSC Guru

    Points: 89885

    cg0x1 - Monday, October 22, 2018 9:08 AM

    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

    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

  • frederico_fonseca

    SSChampion

    Points: 14051

    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

  • cg0x1

    Valued Member

    Points: 73

    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.

  • cg0x1

    Valued Member

    Points: 73

    Sue,

    Thank you very much for the article link. I'm reviewing it now.

  • frederico_fonseca

    SSChampion

    Points: 14051

    cg0x1 - Monday, October 22, 2018 10:36 AM

    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.

    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-backups

    One 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'

  • cg0x1

    Valued Member

    Points: 73

    The VLF count is 591

    The query provided produced these results:

  • Grant Fritchey

    SSC Guru

    Points: 395316

    cg0x1 - Monday, October 22, 2018 10:36 AM

    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.

    Might want to go read this

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • cg0x1

    Valued Member

    Points: 73

    Great article. Thanks for sharing

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply