SQL Server Performance Forum – Threads Archive

Scope_identity locking database?

Hi, blocking keeps occuring in my database, and when I click on the process that is blocking, it shows "select SCOPE_IDENTITY()". I’ve got a database maintenance plan that do Optimization, integrity check every week. So far I have received no error on the maintenance. I’m using the SQL Enterprise Manager, Managment, Current Activity, Locks/ProcessID to detect the blocking. When I click on the item with "blocking" status, I click on "properties" to know what is the process doing. I tried killing the process, but after a very short while, another block will happen, with "Select scope_identity()" again. This happened so frequently that I have no other choice but to reboot the server. Anyone experiencing this? Any solution to the above?[<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />]

Run DBCC CHECKIDENT to correct the identity value on this table. And take help from this KBA http://support.microsoft.com/default.aspx?scid=kb;en-us;271509] to monitor the blocking. Andhttp://www.sql-server-performance.com/blocking.asp to minimize the blocking. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

And use sp_who or sp_who2 in Query Analyzer to find bloking. EM is GUI tool and is heavy to find the same.
Luis Martin
Moderator
SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

True, by all means when the blocking occurs using GUI tools will generate bit stress on SQL resources. Better to follow as suggested by Luis. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

*nod* I will follow what Luis has recommended. Meanwhile as the lock is not happening anymore, so I will just have to wait.. I know it will happen again sometime.. I have ran DBCC CHECKIDENT on some database with NORESEED option and found the values reported are not the same. I will run CHECKIDENT again with reseed option after operation hours, to rectify this problem. Do I have to look out for anything? Can I know what could have caused the identity value to be out of sync? Should I schedule DBCC CHECKIDENT to be run more often?

You shouldn’t need to ever use DBCC CHECKIDENT. I’m assuming you’ve done a DBCC CHECKDB on the table. Also, have you searched and made sure you don’t have any queries accessing the table with WITH(NOLOCK)? Do you have a trigger on the table? Is it just one query that’s causing this? If so, can you post it? This situation can be caused if you have your transactions set up wrong inside a stored procedure, so I would be interested in seeing the stored procedure(s). MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Yes, DBCC CHECKDB is scheduled to run every week. So far it has been 0 errors. It looks like it’s the same query that is causing the problem, since I’m always receiving:
Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQ
LServer]Violation of PRIMARY KEY constraint ‘PK_tTransactionBillHeader’. Cannot
insert duplicate key in object ‘tTransactionBillHeader’. I abstract the query from a log file, which look like below:
INSERT INTO tTransactionBillHeader(iBillIDBH,iBillSuffixBH,sbillprefixbh,
dbilldatebh,spayercodebh,bselfpaybh,mbillamountbh,bcancelbh,scancelreasonbh,iref
billidbh,sinvoicenumbh,screatedbybh,dcreatedonbh,slastupdatedbybh,dlastupdatedon
bh,ivisitidbh,ilocationidbh,bduplicatebh,slockuseridbh,dlocktimestampbh) VALUES(
689239,1,’BLU’,’2004-04-30 08:31:07′,NULL,’1′,24.0,’0′,NULL,NULL,NULL,’saadiah’,
‘2004-04-30 08:31:07′,’saadiah’,’2004-04-30 08:31:07′,788695,2,’0′,NULL,NULL) I’m pretty sure it’s not running from a stored procedure… Do you mean if I make any queries accessing the table with "WITH(NOLOCK)", it’s likely to cause this problem? I have plenty of queries in the applications and scripts using "WITH(NOLOCK)" option. seems like a recommended practice by my previous DBA. DBCC CHECKDB will resolve the identity value sync issue?

I must have been smoking crack when I wrote that last post. Sorry about that. Just forget it ever happened. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> There are times when you don’t want to use WITH(NOLOCK). It can solve a lot of locking issues though. On this query, is one of the fields in the VALUE list the primary key? Are you getting that primary key from a SELECT SCOPE_IDENTITY()? If you are, you can occasionally get duplicates if you haven’t handled your transactions and locking correctly. If enought time happens before the insert, someone else could already have inserted into the field. Does that make sense? Let me know if that’s what’s happening. If it is, we can figure out a way to work around it. MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

hmm… yes I think so. There is a field that is not display here that is the primary key for the table, and it’s getting values through select scope_identity().

]]>