Backup Order

  • Hi to all. We are in the process of migrating away from a Unix Platform to a Windows / SQL 2005 environment. I was looking at configuring maintenance plans using the wizard in SQL 2005 and have a couple of questions for you gurus.

    1)

    Database Backups. If I chose options:

    Back up Database (Full) / Shrink Database / Rebuild Index / Reorganize Index is there a preferred order these tasks should be run in? Should I include more tasks?

    2)

    Transaction Log Backups

    I chose to create a Maintenance Plan for the transaction logs. 'Define Back Up Database' menu 'Database' when expanded only displayed 'Model'?

    Maybe it is me!!

    Any other tips for best practice from those with real world experience would be warmly received.

    Many Thanks,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • Phillip,

    1.

    Rebuilding/reorganizing indexs everday is not usually required. Index rebuilding really is really benificial when the database has moderate to heavy fragmentation. When you rebuild the indexes there is no longer a need to reorganize them, as rebuilding removes the fragmentation by dropping and creating the indexs. You can use the sys.dm_db_index_physical_stats to determine where you fragmentation level is at.

    If I were to include the rebuild or reorganize in the same maintenance plan, I would choose to perform this before the backup.

    2.

    I am guessing the reason you do not see the other databaes in the drop down is because they are not in the full recovery mode. Can you confirm this? Right-click on the database and choose properties and the go to the options tab.

  • Thanks Adam I will take a look at the recovery mode tomorrow. Pretty sure they were set to Full unless the developers have been having a play!! I will implement your suggestions. Maybe have a separate plan for rebuilding the indexes say once a month or once per quarter?

    Thanks,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • Rebuild your index according to how much the are fragmented. You can do this by using the new dynamic management views to determine what level they are fragmented. One you know about how long it takes for them to become fragmented, schedule the plan to run then.

    You should note that your fragmentation will depend on the fill factor you have specified for the database. If you have a 90% fill factore then you are leaving 10% available for indexes to grow. Once the fill factor "fills up" pages start to split and performance degrades. On another note: if this is a OLTP database and there are lots of deletes, you can have "internal fragmentation", which causes empty space in pages. While this form of fragmentation is less degrading to performance, it should still be addressed frequently to keep performance optimal.

    Like I said, indexing is dependant on your environment.

  • I pulled these scripts from the Microsoft 70-431 training kit:

    This script allows you to view the fragmentation

    DECLARE @dbname VARCHAR(20)

    SET @dbname = 'AdventureWorks'

    SELECT object_name(dt.object_id),si.name,

    dt.avg_fragmentation_in_percent, dt.avg_page_space_used_in_percent

    FROM

    (SELECT object_id, index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent

    FROM sys.dm_db_index_physical_stats (db_id(@dbname),null,null,null,'DETAILED')

    WHERE index_id <> 0

    )as dt --it does not return info about heaps

    INNER JOIN sys.indexes si

    ON si.object_id = dt.object_id

    AND si.index_id = dt.index_id

    --If avg_fragmentation_in_percent > 10 then the database has External Fragmentation

    --If avg_page_space_used_in_percent < 75 then the database has Internal Fragmentation

    This script determines if you should rebuild or reorganize based off the authors

    recommendation. You can adjust the values.

    DECLARE @dbname VARCHAR(20)

    SET @dbname = 'AdventureWorks'

    --These indexes should be reorganized, not rebuilt

    -- Alter Index ... Reorganize

    select *

    from sys.dm_db_index_physical_stats (db_id(@dbname),null,null,null,'DETAILED')

    where avg_page_space_used_in_percent 10 and

    avg_fragmentation_in_percent < 15

    --These indexes should be rebuilt, not reorganized

    -- Alter Index ... Rebuild

    select *

    from sys.dm_db_index_physical_stats (db_id(@dbname),null,null,null,'DETAILED')

    where avg_page_space_used_in_percent 15

  • Adam many thanks for your efforts. The DB is not OLTP. I will make use of your advice as the project progresses.

    Many Thanks,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • One more question! I would like a maintenance plan to delete .bak & Log files older than say 1 week. Is this achieved via 'Clean Up History' 'Maintenance Plan History'?

    Update:

    I created a new query with the the script you provided to determine fragmentation level? I take it that was what I was supposed to do? I ran it and it ran OK, returned 'No Rows Affected'.

    Thanks,

    Phil.

    PS: You were correct regards 'Recovery Model'. The developers must have been playing.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • Philip Horan (12/17/2007)


    Back up Database (Full) / Shrink Database / Rebuild Index / Reorganize Index

    Don't shrink your production databases. It's a waste of time and resources and it scrambles your indexes. If you shrink then rebuild indexes, the rebuild will likely require the DB to grow again. Repeated shrink/grow can also cause external (file-level) fragmentation of your data files.

    Read this[/url], especially the 2 links towards the bottom

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • The clean up task is exactly what you need 😉

  • Update:

    I created a new query with the the script you provided to determine fragmentation level? I take it that was what I was supposed to do? I ran it and it ran OK, returned 'No Rows Affected'.

    This means that no of your indexes met the criteria specified by the query. You can adjust the numbers or remove the filter to see where you actually stand.

  • Thanks guys. The shrink database option was initially set up by the application vendor, although they are not SQL specialists (I do have one coming in January).

    I will have to get reading.

    Many Thanks,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

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

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