January 14, 2016 at 1:00 pm
Here is the reach... so I heard, was told, someone hinted, anyway the need a snopes for SQL Server
Here is the deal we have a repository of tables that are refreshed from their source every 4 hours, someone told me there was a way to present this data so that I don't run into locking issues when I truncate the table while they are running a query and they don't fail if the query it in the middle of a reload. They said you could snapshot that the data.
anyone?
January 14, 2016 at 2:43 pm
database snapshots would not solve your problem as you would run into the same blocking issue when creating new snapshot.
the way I deal with this exact situation is to load the data into staging tables, add all relevant indexes then rename existing tables to something and rename staging table to live. these is still the possibility of blocking if you have a long running query as you will not be able to rename table as it is being accessed.
CREATE TABLE [tablename]([id] int);
INSERT INTO [tablename]
VALUES
(1),(2);
--check data
SELECT *
FROM [tablename];
--start load
CREATE TABLE [tablename_stage]([id] int);
INSERT INTO [tablename_stage]
VALUES(1),(2),(3);
BEGIN TRANSACTION;
EXEC [sp_rename] 'tablename', 'tablename_old';
EXEC [sp_rename] 'tablename_stage', 'tablename';
COMMIT TRANSACTION;
DROP TABLE [tablename_old];
--load finshed, check data
SELECT *
FROM [tablename];
February 4, 2016 at 7:20 am
So is this the best way to manage this with minimal downtime? I mean this in relation to creating a transaction, truncating and loading the data and then closing the transaction. The issue at hand is not a lack of availability, I don't care if they have to wait, I am more concerned about them trying to access something that is not there and getting an error.
February 4, 2016 at 8:35 am
John1.5.nlt (2/4/2016)
So is this the best way to manage this with minimal downtime? I mean this in relation to creating a transaction, truncating and loading the data and then closing the transaction. The issue at hand is not a lack of availability, I don't care if they have to wait, I am more concerned about them trying to access something that is not there and getting an error.
By doing truncate/load in transaction instead of rename you will get the same behaviour. The downside of this, as you alluded to, is the table will not be available during the load. the rename takes a schema mod lock and since done in a transaction the transaction has to commit before selects can continue. even if you add the nolock query hint to the select, a shared schema lock is still taken and thus has to wait for the schema mod lock to be released.
Here is a quick test you can do to verify that queries will not try to select from a non-existent table.
In one query window create tables and run the rename transaction, I added a wait in between the renames for the purpose of the test.
CREATE TABLE tmp(id int);
CREATE TABLE tmp_new(id int);
BEGIN TRAN;
EXEC sp_rename 'tmp','tmp_old'
WAITFOR DELAY '00:01:00';
EXEC [sp_rename] 'tmp_new', 'tmp';
COMMIT;
While this query is running, in a second query window check if there is any table named tmp in the database. You should only see tmp_old and tmp_new.
SELECT *
FROM sys.objects
WHERE name LIKE 'tmp%'
If there is not a table named tmp, then try to select from it(use NOLOCK if you want)
SELECT * FROM tmp WITH (NOLOCK)
This select will wait until the rename transaction is committed. when done dont forget to drop the tables.
DROP TABLE tmp;
DROP TABLE tmp_old;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply