Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup Order


Backup Order

Author
Message
2Tall
2Tall
Right there with Babe
Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)

Group: General Forum Members
Points: 792 Visits: 1203
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

Adam Haines
Adam Haines
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2522 Visits: 3135
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
2Tall
2Tall
Right there with Babe
Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)

Group: General Forum Members
Points: 792 Visits: 1203
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

Adam Haines
Adam Haines
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2522 Visits: 3135
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
Adam Haines
Adam Haines
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2522 Visits: 3135
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
2Tall
2Tall
Right there with Babe
Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)

Group: General Forum Members
Points: 792 Visits: 1203
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

2Tall
2Tall
Right there with Babe
Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)

Group: General Forum Members
Points: 792 Visits: 1203
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

GilaMonster
GilaMonster
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57597 Visits: 44707
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, 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


Adam Haines
Adam Haines
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2522 Visits: 3135
The clean up task is exactly what you need Wink



My blog: http://jahaines.blogspot.com
Adam Haines
Adam Haines
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2522 Visits: 3135

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search