December 15, 2011 at 7:40 pm
SQL Server version is SQL Server 2005 Standard. I encountered an issue yesterday whereby the Full Backup job was hung or took longer time to execute. When I execute sp_who2 "active", I found the process was blocked by this "ALTER INDEX [DATABASE_NAME] REBUILD WITH (ONLINE = OFF)".
My question:
Will the ALTER INDEX ... REBUILD WITH (ONLINE=OFF) locked the full backup job?
Kindly advice. Thanks.
Regards,monkeyDBAWe dont grow when things are easy; We grow when we face challenges.
December 15, 2011 at 9:27 pm
I had just initiated the same steps on one of my 2005 instances at home. seems to be going ok. Will update once again..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 15, 2011 at 9:40 pm
Had no locking issues..
Though the backup was slower (as expected)...
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 15, 2011 at 10:14 pm
Thanks Bru for your prompt response on this.
As per my understanding the table locks are applied for the duration of the rebuild index operation. Just want to double confirm will the full backup halted while the ALTER INDEX... REBUILD WITH (ONLINE=OFF) execute on a huge table which has 75,087,140 records?
Kindly advice.
Regards,monkeyDBAWe dont grow when things are easy; We grow when we face challenges.
December 15, 2011 at 10:49 pm
How large is the table in terms of size? Just wanted to compare to what I have at my end..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 15, 2011 at 11:18 pm
If the online = off with index rebuild option on a huge table will certainly block the full backup. The table will be available if you use the online = on option. But curious to know why did not you use online option. Is there any constraint.
December 15, 2011 at 11:21 pm
baabhu (12/15/2011)
But curious to know why did not you use online option. Is there any constraint.
The OP posted that it is SQL Server 2005 Standard, the feature you are talking about is available in Enterprise Edition only...
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 15, 2011 at 11:34 pm
reserved : 28,400,944 KB
data : 16,343,440 KB
index_size : 12,056,360 KB
Regards,monkeyDBAWe dont grow when things are easy; We grow when we face challenges.
December 15, 2011 at 11:46 pm
Well, the largest table that I set up at my end is almost 80 % of the size that you have mentioned...
Your understanding on the locks being placed on the table during the Index rebuild is right and no doubt about it..
I am not able to reproduce the index rebuild blocking the backup job at my end.. Couldn't be of much help in that..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 16, 2011 at 12:01 am
Thanks Bru for your help. I really appreciated that. 😀
Regards,monkeyDBAWe dont grow when things are easy; We grow when we face challenges.
December 16, 2011 at 4:46 am
Backups don't take locks of any form, hence they can never be blocked by activity in the database.
It's not a good idea to rebuild indexes and back up at the same time, the IO load will be unpleasant and the backup will take longer and will be larger than it would normally be (due to the log records it'll have to include)
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
December 16, 2011 at 5:23 am
Jimmy.Liew (12/16/2011)
Thanks Bru for your help. I really appreciated that. 😀
No problem Jimmy..
I second Gail's thoughts..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 20, 2011 at 2:31 am
Gail,
Will the backup job wait for the activities( currently running ) to finish and backup the log.
Hope it is not, i have tried in production server when the load is slightly high. Need clarity on this.
Regards
Durai Nagarajan
December 20, 2011 at 2:51 am
durai nagarajan (12/20/2011)
Gail,Will the backup job wait for the activities( currently running ) to finish and backup the log.
No. A backup is not blocked by any data modification queries.
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
December 20, 2011 at 3:04 am
gail,
My question is will the backup wait for the current activities and backup those activities log as well.
I have experience that even at huge load my backup job will complete without much delay.
just want to knwo about the first point.
Regards
Durai Nagarajan
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply