March 14, 2010 at 11:11 pm
I have reporting database, which is refreshed every 3 hour. I usually get a Transaction deadlock error whenever a user tries to generate a reporting.
We have checked, it is when the indexing on the DB is running, the user gets this error.
Now is it possible to check if the indexing is still running?
March 15, 2010 at 1:59 am
By 'indexing' do you mean index creation? Rebuilding indexes? Something else?
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
March 15, 2010 at 2:19 am
Ya it rebuild index. When we use the DBCC DBREINDEX 'tablename', it takes some time to rebuild it. And if in the mean time if a user queries the same table, gets a transaction deadlock error.
Let me know if I am missing something here.
Thanks for your quick turnaround.
March 15, 2010 at 6:27 am
Ajit-297150 (3/15/2010)
Ya it rebuild index. When we use the DBCC DBREINDEX 'tablename', it takes some time to rebuild it. And if in the mean time if a user queries the same table, gets a transaction deadlock error.Let me know if I am missing something here.
Thanks for your quick turnaround.
So, what would you like to do? Give the user a graceful warning? You could add something to the procs that use the table and have it check for the SQL JOB that's doing the reindexing.
If you're using the Enterprise edition of SQL Server, you can do the reindexing with the ONLINE option and that should take care of things. If you're not, then consider breaking the larger table up into smaller tables (NOT table partitioning which also requires the Enterprise edition) with their own indexes and use a "partitioned view" to put it all back together again. The advantage will be that you probably won't have to reindex all the tables if they are in "date order" because the data won't change much if at all. Only the "active" data will change and since it will be in a much smaller table, the indexes will also be smaller which means they'll reindex much quick. Users may still get an error here and there, but not as many as before.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 15, 2010 at 6:35 am
Why are you performing a reindex operation each 3 hours?
Maybe it is enough to run the operation over night.
Nervertheless DBCC DBREINDEX performs a DROP and a CREATE Index operation and on Primaray Keys an exclusive table lock will be held.
March 16, 2010 at 9:19 am
I think you can use READPAST or WITHNOLOCK hint in the query.
March 16, 2010 at 9:25 am
ESAT ERKEC (3/16/2010)
I think you can use READPAST or WITHNOLOCK hint in the query.
Well, you can ...
On rare occasions it may even be a good idea. However, there are significant potential drawbacks to this approach
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
March 16, 2010 at 9:39 am
How about using something like the following to allow a number of retries before timing out of the query the user is running encounters a lock (adjust timing and reries to your own circumstances?
SET NOCOUNT ON;
DECLARE @Retry TINYINT;
DECLARE @Retried TINYINT;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @Error INT;
SET LOCK_TIMEOUT 1500
SET @Retry = 1;
SET @Retried = 0;
/**********************
*Declarations for the process here
*
**********************/
WHILE @Retry = 1 AND @Retried <= 5
BEGIN
BEGIN TRY
SET @Retry = 0;
/**********************
*SELECT Statement(s) accessing Facts and Dims where
*you want to retry if resources are locked, for example
*when the ETL process has them
**********************/
END TRY
BEGIN CATCH
SELECT@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@Error = ERROR_NUMBER();
--SELECT @Retried
IF @Error = 1222 AND @Retried < 5--Lock Timeout
BEGIN
SET @Retried = @Retried + 1;
SET @Retry = 1;
WAITFOR DELAY '00:00:05';--Pause for 5 seconds before retrying
END
ELSE
BEGIN
IF @Error = 1205 AND @Retried < 5 --Deadlock
BEGIN
SET @Retried = @Retried + 1;
SET @Retry = 1;
WAITFOR DELAY '00:00:05';--Pause for 5 seconds before retrying
END
ELSE-- Boot it back 'upstairs'
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END
END CATCH
END -- @Retry = 1 AND @Retries <= 5
March 17, 2010 at 3:51 am
Ajit, are you aware that SQL Server automatically indexes every row as it is added to a table. There is no need to do a separate index build in SQL Server.
The DBCC REINDEX command is designed to do maintenance on an index that is fragmented. If you do not understand what a fragmented index is, then do a few searches on Google and read what you find.
Why do you think you need to do a DBCC REINDEX after you have refreshed your reporting data? If it is because you are getting poor query performance, the problem may be due to out of date statistics. Although rebuilding an index will refresh its statistics, you can refresh statistics without needing an index rebuild.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 17, 2010 at 5:00 am
May be you can do/schedule the reindexing in OFF peak hours.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 17, 2010 at 6:09 am
Andrew Gothard-467944 (3/16/2010)
ESAT ERKEC (3/16/2010)
I think you can use READPAST or WITHNOLOCK hint in the query.
Well, you can ...
On rare occasions it may even be a good idea. However, there are significant potential drawbacks to this approach
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx%5B/quote%5D
Those hints are only capable of ignoring shared locks. Rebuilding an index offline acquires and holds a schema-modification lock.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 17, 2010 at 6:35 am
Paul White (3/17/2010) Rebuilding an index offline acquires and holds a schema-modification lock.
why index offline holds a schema-modification lock ? please explain
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 17, 2010 at 6:42 am
Bhuvnesh (3/17/2010)
Paul White (3/17/2010) Rebuilding an index offline acquires and holds a schema-modification lock.
why index offline holds a schema-modification lock ? please explain
Books Online: ALTER INDEX (Transact-SQL)
Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered, spatial, or XML index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 18, 2010 at 6:12 am
To check for the indexing:
You could always execute "sp_who2" into a temp table and check to see if there is an input_buffer with "DBCC DBREINDEX 'tablename'" for the tables that are affected by your report.
This is in lieu of checking to see if the job is running.
July 1, 2010 at 7:56 am
I started a reindex job it ran for 1hr and 19min. then error "data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max)" there is more but not that important to my question. Did the reindex finish or stop when it encountered this error? I can't find detailed log files.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply