Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Backup Order Expand / Collapse
Author
Message
Posted Monday, December 17, 2007 11:25 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 5:29 AM
Points: 621, Visits: 1,073
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
Post #433988
Posted Monday, December 17, 2007 11:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:29 PM
Points: 2,278, Visits: 3,047
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.




My blog: http://jahaines.blogspot.com
Post #433997
Posted Monday, December 17, 2007 12:10 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 5:29 AM
Points: 621, Visits: 1,073
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
Post #433999
Posted Monday, December 17, 2007 12:25 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:29 PM
Points: 2,278, Visits: 3,047
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.




My blog: http://jahaines.blogspot.com
Post #434002
Posted Monday, December 17, 2007 12:29 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:29 PM
Points: 2,278, Visits: 3,047
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





My blog: http://jahaines.blogspot.com
Post #434005
Posted Monday, December 17, 2007 12:32 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 5:29 AM
Points: 621, Visits: 1,073
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
Post #434009
Posted Monday, December 17, 2007 1:17 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 5:29 AM
Points: 621, Visits: 1,073
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
Post #434026
Posted Monday, December 17, 2007 2:03 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:43 AM
Points: 41,544, Visits: 34,466
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, especially the 2 links towards the bottom



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #434047
Posted Monday, December 17, 2007 2:24 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:29 PM
Points: 2,278, Visits: 3,047
The clean up task is exactly what you need ;)



My blog: http://jahaines.blogspot.com
Post #434057
Posted Monday, December 17, 2007 2:26 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:29 PM
Points: 2,278, Visits: 3,047

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.




My blog: http://jahaines.blogspot.com
Post #434058
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse