October 11, 2016 at 10:27 am
Hi,
Is it possible to block all users from querying a table while it's being refreshed (truncate/insert into) by a maintenance job? I'm not a DBA but would like to know the possible options.
Thx,
J.
October 11, 2016 at 10:42 am
BI_Dev (10/11/2016)
Hi,Is it possible to block all users from querying a table while it's being refreshed (truncate/insert into) by a maintenance job? I'm not a DBA but would like to know the possible options.
Thx,
J.
You can use Table Hints (Transact-SQL) in the maintenance operation.
😎
October 11, 2016 at 11:39 am
To block all readers suring your insert operation, either the TABLOCK or TABLOCKX hint will work.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 11, 2016 at 11:59 am
So would the syntax below be correct:
TRUNCATE TABLE DBO.OUTPUTTABLE
INSERT INTO DBO.OUTPUTTABLE WITH (TABLLOCK) OR (TABLLOCKX)
SELECT *
FROM DBO.INPUTTABLE
October 11, 2016 at 9:55 pm
BI_Dev (10/11/2016)
Hi,Is it possible to block all users from querying a table while it's being refreshed (truncate/insert into) by a maintenance job? I'm not a DBA but would like to know the possible options.
Thx,
J.
If you have the space for it, I wouldn't block any users during such an evolution. Instead, here's what I do...
Let's say that the table that you're trying to refresh is called TheTable. To set things up for a "no outage" refresh for all future refreshes...
1. Start out by renaming TheTable to TheTableRefresh1.
2. Now, create a synonym called TheTable and point it at TheTableRefresh1
3. Create an identical table and call it TheTableRefresh2
The stage is set for all future updates.
1. During the next refresh cycle, refresh TheTableRefresh2. During this time, the TheTable synonym is still pointing at TheTableRefresh1 and the users continue to use it.
2. Once TheTableRefresh2 has been refreshed and validated, simply drop and rebuild the TheTable synonym to point at the newly refreshed TheTableRefresh2 table. Total down time will be measured in milliseconds and the system will dutifully wait until the synonym is no longer used before dropping and renaming through "normal blocking protocol".
3. During the next refresh process, just reverse the roles of the tables. Wash, rinse, repeat for each refresh cycle.
Your users will love you.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 12, 2016 at 10:57 am
Here is another "Zero downtime" option that uses partition switching.
See the inline comments for the details...
USE tempdb;
GO
--=============================================================================
-- Create the "original" table and populate it
IF OBJECT_ID('dbo.OriginalTable', 'U') IS NOT NULL
DROP TABLE dbo.OriginalTable;
CREATE TABLE dbo.OriginalTable (
SomeNumber INT NOT NULL
CONSTRAINT pk_OriginalTable_SomeNumber PRIMARY KEY CLUSTERED (SomeNumber) ON [DEFAULT]
);
WITH
n (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
cte_Tally (SomeNumber) AS (
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))--(1M sequential numbers)
FROM n n1, n n2, n n3, n n4, n n5, n n6
)
INSERT dbo.OriginalTable (SomeNumber)
SELECT t.SomeNumberFROM cte_Tally t;
-- Verify that 1,000,000 rows were inserted...
--SELECT row_count = COUNT(*) FROM dbo.OriginalTable ot;
--=============================================================================
-- Create a "New" table that contains the newly refreshed values.
-- Note: The NewTable must have the same structure as the OriginalTable... Including indexes!!!
IF OBJECT_ID('dbo.NewTable', 'U') IS NOT NULL
DROP TABLE dbo.NewTable;
CREATE TABLE dbo.NewTable (
SomeNumber INT NOT NULL
CONSTRAINT pk_NewTable_SomeNumber PRIMARY KEY CLUSTERED (SomeNumber) ON [DEFAULT]
);
-- add some "refreshed" data...
WITH
n (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
cte_Tally (SomeNumber) AS (
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) * 2 --(1M even numbers)
FROM n n1, n n2, n n3, n n4, n n5, n n6
)
INSERT dbo.NewTable (SomeNumber)
SELECT t.SomeNumberFROM cte_Tally t;
-- Again... Verify that 1,000,000 rows were inserted...
--SELECT row_count = COUNT(*) FROM dbo.NewTable ot;
--=============================================================================
--=============================================================================
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION
-------------------------
DECLARE @SwitchStart DATETIME2(7) = SYSDATETIME();
-- 1) Truncate the original...
TRUNCATE TABLE dbo.OriginalTable;
-- 2) Make the switch...
ALTER TABLE dbo.NewTable SWITCH TO dbo.OriginalTable;
-- See how long it took to actually make the switch...
SELECT DownTimeInMS = DATEDIFF(ms, @SwitchStart, SYSDATETIME())
-- 3) Trash NewTable (it's now empty)
DROP TABLE dbo.NewTable;
-------------------------
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@trancount > 0 ROLLBACK TRANSACTION;
DECLARE @msg nvarchar(2048) = error_message();
RAISERROR (@msg, 16, 1);
END CATCH;
-- 4) verify that Original table has the refreshed values.
SELECT TOP 1000 * FROM dbo.OriginalTable ot;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply