January 4, 2013 at 7:35 am
I have a table that is populated by a job every hour. The first step is to truncate the table then populate using stored procedure, then add index. The process takes about 5 minutes. Several SSRS Reports depend on the table and during the 5 minutes the reports return no records.
Is there someway I can reduce the downtime to zero, maybe by wrapping in transaction or populateing another table and changing name??? I really have no clue and would appreciate any ideas
🙂
January 4, 2013 at 7:51 am
SP_RENAME will be a good friend on this one.
Create the table again, but with a different name, eg table2.
Do all your processing, truncating etc in table2
Then rename table1 to table3
Then rename table2 to table1
Then rename table3 to table2
The rename will take a split second once any locks have been released on the object.
January 4, 2013 at 8:07 am
As you said, have you tried wrapping all the process in a transaction? I think, that is the usual and best way to utilize the maximum availability...
January 4, 2013 at 8:18 am
bala.a (1/4/2013)
As you said, have you tried wrapping all the process in a transaction? I think, that is the usual and best way to utilize the maximum availability...
But even in a transaction while the process is running the reports wont be able to run as they will wait for the process to finish.
The only thing would be to set the database to ALLOW_SNAPSHOT_ISOLATION and then set the transaction to run in SNAPSHOT, but that would invoke the row version store and have an increased impact on TempDB.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply