November 29, 2007 at 11:07 am
So this is what I want to do:
I have this database, on a daily basis we have a table in this database that needs to be dropped and recreated at a curtain time. This table is constantly be used by many other applications, so what I want to do is, when my stored procedure runs on a daily basis to recreate this table, I want to lock all the calls to this table/database while this drop and recreate are being executed.
So the flow would be like this:
block all sp's
begin trans
drop table_daily
recreate table_daily
commit trans
unblock all sp's
I tested the drop and recreating of the tables, and it takes less then 1 second to do. The reason I want to block the sp's is, when the table is dropped, I do not want any applications to through any errors such as (table doesn't exist, etc.).
Thanks.
November 29, 2007 at 11:26 am
What is the reason you need to drop the table?
By dropping the table you will be unable to hold any locks against it.
By default sql will lock the table if your deleting everyting in it, or if your doing it within a transaction.
So I would consider revising your flow.
Begin tran
Truncate Table.
Populate table_daily
Commit
There is no way to "Block" Stored procedures from running.
November 29, 2007 at 11:30 am
Well the reason I want to do this is, the table is created on a daily basis dynamically, new columns can be added/some columns can be removed. This is the reason why it needs to be done this way.
So will I be able to do the following then:
Being Trans
truncate table
alter schema of the table
insert data into the table.
commit trans
I figured droping and recreating the table would be much easier then writing the alter columns/dropping columns dynamically in the stored procedure.
November 29, 2007 at 12:05 pm
How about sacrificing a little disk space;
Create the new table with a different name
load the new table
rename the old table
rename the new table.
drop the old table
The down time is completely minimized and you can error trap all this in such a way that until the rename, nothing is happening to affect the old table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 29, 2007 at 12:11 pm
So lets say, someone is running a query and I am about to rename the table...what would happen here? The query is still not finished and its a long query.
November 29, 2007 at 12:18 pm
Oh, you'd have to drop the connections for the ms that the query takes, but, based on the process you've defined, you're slapping the users around at some point no matter what. This way it would be a minimal amount of slapping, say Raymond Chandler instead of Mickey Spillane.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 29, 2007 at 9:58 pm
Ravi Patel (11/29/2007)
So lets say, someone is running a query and I am about to rename the table...what would happen here? The query is still not finished and its a long query.
Depends,
but your update will likely wait for that other query to be done.
that is what locking is all about.
November 30, 2007 at 5:24 am
We're using this method and the same idea with whole databases in some of our datamarts. It works well to minimize down time. Best of all, if there is a failure on load, the end-users never have to see it. We put the switches in at the lowest possible work time, but I know that we have interrupted one or two users in the past. It's one of those things, depending on the circumstances, there's going to be some pain felt somewhere, minimizing it is the key.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply