Sql server locking/blocking issue

hi guys,

I have a procedure to delete old records by id (clustered index) from
the a big table (delete about 150k records at a time comparing to 30 M
total in the table) and at the same time, some procedures will insert
or update data in this table by id as well. When I did the tests, I
found the first procedure is blocking all other insert/update
procedures. The locks of the first procedure has is the PAGE exclusive
lock.  Those update/insert procedures will only work on the newer
ids.  I don't know why this block can happen. Could anyone here help
me explain a little bit?

Right now I don't know what I can do except I will rewrite the delete
procedure to delete small chuck of data at a time. Is there anything
else I can do to solve this blocking issue?

Thanks,
YY

0
mirthcyy
2/18/2010 4:11:42 PM
sqlserver.programming 1873 articles. 0 followers. Follow

3 Replies
829 Views

Similar Articles

[PageSpeed] 41

"mirthcyy" <mirthcyy@gmail.com> wrote in message 
news:943a7eb6-a6c7-4c95-b99d-830627d7d067@x22g2000yqx.googlegroups.com...
> hi guys,
>
> I have a procedure to delete old records by id (clustered index) from
> the a big table (delete about 150k records at a time comparing to 30 M
> total in the table) and at the same time, some procedures will insert
> or update data in this table by id as well. When I did the tests, I
> found the first procedure is blocking all other insert/update
> procedures. The locks of the first procedure has is the PAGE exclusive
> lock.  Those update/insert procedures will only work on the newer
> ids.  I don't know why this block can happen. Could anyone here help
> me explain a little bit?

What version of sql server?
Can you post an example of your delete statement, along with the DDL of the 
table (including indexes - just so we can check)?
Are there any indexed views that reference the table?
Does the table have any delete triggers? 


0
Scott
2/18/2010 4:44:26 PM
Please find more info at

http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thr=
ead/thread/5f2617fa9bde753e#

Thanks.

On Feb 18, 10:44=A0am, "Scott Morris" <bo...@bogus.com> wrote:
> "mirthcyy" <mirth...@gmail.com> wrote in message
>
> news:943a7eb6-a6c7-4c95-b99d-830627d7d067@x22g2000yqx.googlegroups.com...
>
> > hi guys,
>
> > I have a procedure to delete old records by id (clustered index) from
> > the a big table (delete about 150k records at a time comparing to 30 M
> > total in the table) and at the same time, some procedures will insert
> > or update data in this table by id as well. When I did the tests, I
> > found the first procedure is blocking all other insert/update
> > procedures. The locks of the first procedure has is the PAGE exclusive
> > lock. =A0Those update/insert procedures will only work on the newer
> > ids. =A0I don't know why this block can happen. Could anyone here help
> > me explain a little bit?
>
> What version of sql server?
> Can you post an example of your delete statement, along with the DDL of t=
he
> table (including indexes - just so we can check)?
> Are there any indexed views that reference the table?
> Does the table have any delete triggers?

0
mirthcyy
2/18/2010 4:59:40 PM
Hi
See if you can play with SNAPSHOT isolation level to prevent blocking...






"mirthcyy" <mirthcyy@gmail.com> wrote in message 
news:943a7eb6-a6c7-4c95-b99d-830627d7d067@x22g2000yqx.googlegroups.com...
> hi guys,
>
> I have a procedure to delete old records by id (clustered index) from
> the a big table (delete about 150k records at a time comparing to 30 M
> total in the table) and at the same time, some procedures will insert
> or update data in this table by id as well. When I did the tests, I
> found the first procedure is blocking all other insert/update
> procedures. The locks of the first procedure has is the PAGE exclusive
> lock.  Those update/insert procedures will only work on the newer
> ids.  I don't know why this block can happen. Could anyone here help
> me explain a little bit?
>
> Right now I don't know what I can do except I will rewrite the delete
> procedure to delete small chuck of data at a time. Is there anything
> else I can do to solve this blocking issue?
>
> Thanks,
> YY
> 


0
Uri
2/21/2010 8:56:14 AM
Reply:

Similar Artilces:

delegate rights issue
Is there anyway to see what a users has delegate rights to? Thanks, Cactus Try this script. Reporting on Meeting Delegate Forward Rules in Outlook http://gsexdev.blogspot.com/search?q=delegates James Chong (MVP) MCSE | M+, S+, MCTS, Security+ msexchangetips.blogspot.com On Apr 3, 5:02 pm, "Cactus Johnson" <cactus.john...@cactus.com> wrote: > Is there anyway to see what a users has delegate rights to? > > Thanks, > > Cactus What I am interested in doing is checking the whole directory for what permissions say user x has on every other mailbox in t...

Need help troubleshooting an Entourage issue
Hi, I'm trying to help a non-technical friend of mine determine the root cause of an issue INVOLVING Entourage, but it's not the main application in the scenario. More than anything, I'm really just looking suggestions of things I haven't considered already. So, here goes: We're using 10.4.3 with Entourage 2004 11.2.1. The problem is with using Backup 3.0.1, the application included with .Mac to backup the Microsoft User Data folder from ~/Documents. I can backup the folder to CD/DVD without issue, but when I try to backup to my iDisk, Backup gives me a -54 error while tr...

ActiveX-Exe blocking
I have an ActiveX-Exe that has a property "DoThis" It looks like this Public Property Let DoThis(byval uDo as boolean) someform.Timer1.Enabled = uDo End Property The timer fires after 1 second, and I thought that my application would return right after calling myActiveXExe.DoThis = True It looks like it does, but when the timer fires and calls the real sub, my application is blocking as if it was waiting for the return of the real call. I am not sure why it does that... The real call raises an event if a certain condition is true, and this event is r...

Outlook XP Logon Issue
hi, i am having troubles with logging thru outlook xp. i get the following error message when i create my profile. "Your logon information was incorrect. Check your username and domain, then your type your password again. if your account is new or if your administrator requested a password change you need to check Change Password then logon with your new password." what should i do? plz note, thru the same popup, i can change my pwd successfully. my coworker who can successfully login the same domain, tried to logon from my laptop, but got the same error. thanks for your response. ...

Installation Issue
I have just installed CRM 1.2 Professional - I was able to install the 1.2 client locally. On a VPN connection, I can't seem to access the server - I tried to installed the client and it's asking for the website, but it can't be found. Do I need to allow remote access or some other approach? Thank you See if you can get to the CRM server via a web browser. If you can access it by name, it's probably related to open ports. If you can't, it's a name resolution issue. "tony" wrote: > I have just installed CRM 1.2 Professional - > I was able to ins...

Add Sender To Blocked Senders List
I am getting inundated with e-mail spam and when I select "Add Sender To Blocked Senders List" so that mail gets sent to my Junk E-Mail Folder it appears that it is ignored and I keep getting the same spam directed to my Inbox. Anyone have a way to make this work? Patrick I don't use WLM but I have some advice for you. Stop using your real email address when you post to a newsgroup and you will reduce the number of spam emails you receive....Maybe. I say "Maybe" because it might be too late due to your address already being on numerous spam lists...

ExportErrors Issue
I have created a series of Macros in Microsoft Access 2000 that allows a technology-challegenged client to create exports from underlying SQL data. The final process in the macro creates an Excel spreadsheet to a specified folder on their desktop computer. Since I am using Access 2000 and have over 30,000 records, I must use the TransferSpreadsheet action in the Macro. But when the export occurs, it creates a new table that has "ExportError" as the last part of the name. This is due to the fact that the data has "dummy" dates from 1/1/1753. Excel doesn't recognize ...

Exchange 2003 Server Replacement, Events Script Issue
We recently replaced one of our exchange servers that was housing our public folders. This server was also used as the events service for our entire site. After the replacement our mailboxes and public folders with events scripts were not firing and we had to manually change them over to the new server by opening outlook, clicking the agents tab and selecting the new server. What I'd like to know is if there is a way or script to move these events to the new server. I can see two eventconfig_<severname> and I can view the contents in the events root folder, however, I do not know ho...

Using multiple Back-end servers
I've installed Front-end and back end exchange 2003 servers. Now we have to host another domain and old back-end it's full. I've so installed another beck-end server where hosting new mailboxes. Which I've to set to Master? Where put public folders and have to set replica? How set RSUS policy for new domain and new mailboxes in another back-end server? Thanks The additional backend should make no difference. The Routing Master and RUS can stay the same. As for Public Folders you could point the new server to the old server or create a replicas. Probably the best approac...

How to lock the size of a text box so it won't keep changing?
I format a text box in line with the guides and lock the aspect ratio (and save the file) but when I come back to the pages the size of the text boxes has changed. Any suggestions? ...

Power down CAS servers, activesync stops working
Hi all, We have 2 CAS servers we need to shut down for the weekend. When I shut them down, activesync stops working. Neither of these CAS servers are the internet facing one. CAS01 - internet facing one. Configured with internal and external URL. CAS02 and CAS03 configured only with internal URL. When CAS02 and CAS03 are shut down, activesync stops working and the following event is logged on CAS01 "The proxy request to CAS02.contoso.com has timed out" When I power back up the 2 CAS servers, mail starts to sync again. Now I know this is due to proxying on C...

CRM 1.2 doesn't work after installation
After the full installation of Microsoft CRM on Win2003 server (DC, SQL2000 server, Exchange 2003 server and IIS 6.0; security privileges for local computer and local computer account added to "Pre-Windows 2000" in Active Directory) I recieve the next error message in CRM Deployment Manager when trying to add users: The server localhost is not responding. This might happen if it is currently unavailable, it is not a Microsoft CRM server, or you do not have sufficient privileges to run User Manager. An attempt to run the CRM brings me "Page not available" message in IE. O...

Single Instance Messaging/Adding a new Server
Greetings All, I am adding a new exchnage server to one of our regional offices in order to ease a perceieved performance issue. Currently we have 1 storage group with 2 info stores, Users A-K and Users L-Z. Users L-Z will be moved to the new server.The edb for Users L-Z is approx 17GB in size with a single instance messaging ratio of approx 11-1 (we have alot of people using distribution lists here). Users L-Z is approx 400 people. My question - in moving users in the L-Z group to the new server, what can I expect (in real life terms) the expansion of the database to be... ie, what kind of ...

SQL Server vs Access for back ends
Hi Is it better to use SQL Server for my back end database, than to use Access? Yours, Stapes Stapes wrote: > Hi > > Is it better to use SQL Server for my back end database, than to use > Access? Define "better" and someone could answer the question. If you need the security, scalability, conciurrency, 24/7 operation, and other advantages that SQL Server provides then obviously using SQL Server would be "better" then using an MDB file. If you don't need those things then we still need to know more information. For example, if your organization alr...

Microsoft SQL Server Management Studio / Convert Date
This isn't exactly a Microsoft Access question, but I didn't know where else to go. I am using Microsoft SQL Server Management Studio. The table and field name are “dbo.EncounterData.CreateDateTime” The results are formatted like “2010-03-02 10:36:52.527”. Would you know how I can use the Convert function to format the results as “yyyymmdd”. I know it is something like one of these: select (convert (char(8), getdate(), 112) SELECT CONVERT(VARCHAR(8),CONVERT(DATETIME),112) but I can't get either one to work. -- Tyro from Missouri Tyro wrote: > This...

Unable to mount 'Public Folders" on Exchange Server 2003
Hello, I am having problems mounting the "Public Folders" tree in my Exchange Server 2003 which is running on a Windows 2003 server. The "Public Folders" tree does not appear in the "Folders" folder since I accidently removed the check mark for "Allow inheritable permissions to propagate to this object" while adding a new user to it. I checked the "Public Folders" under the server object and "Allow inheritable permissions to propagate to this object" is checked for it. I get the following error when I try to mount it: "The...

Transfering data into locked spreadsheet
Hello all, I need some help on how to transfer data into a protected spreadsheet. Copy and paste is not working because there are some locked cells in-between the cells I am trying to paste. Here is an example: A B C 1 P 0 P 2 0 P 0 3 P 0 P P=Protected cell 0=Unprotected cell where I'm trying to paste data into My problem is that I can paste data only when the cells are together and there are no protected cells between them. Can anyone tell me if there is a way to paste or move the data into the above cells without having to enter data into each c...

Sql Server
My rms version is 2.0 and I am running sql server 2000 standard addition and my data is already over 7GB and I heard RMS 2.0 SP3 do not suppot sql 2000anymore. So what my other options and What sql I should buy. -- Thank You Harjit SherGill /DBA Altaville Market 324 South Main st po box 370 Altaville CA 95221 Tel : 209-736-1677 vendaliajat@yahoo.com SQL 2005, although with RMS V3 coming out you will probably have to change to SQL 2008 soon enough. -- Jenny "Gill" wrote: > My rms version is 2.0 and I am running sql server 2000 standard addition and > my data is al...

the RPC server is unavailable
When I open up Message Tracking Center from my XP box it gives me an error: The RPC server is unavailable Facility: Win32 ID no: 800706ba Exchange System Manager I found a KB about it: http://support.microsoft.com/?id=821830 and it says to make sure that you can connect to the server. I can connect fine. I am thinking that there is a service that I need to start. Anyone with any ideas? TIA -Steven- On Fri, 23 Jun 2006 08:10:06 -0500, "Steven Platt" <me@nowhere.com> wrote: >When I open up Message Tracking Center from my XP box it gives me an error: > >The ...

Exchange server uninstall
Hi, We've migrated all mailboxes from an Exchange 2000 Server to a Exchange 2007 server. The Exchange 2000 server is still on the network and I notice it still sends external emails out, there is no activity on the firewall advising the new Exchange 2007 server is doing this.. When Exchange is uninstalled from this 2000 server will external emails continue to work properly? (I assume it's doing this as there's a connection between the new Exchange 2007 server and the 2000 box?) On Wed, 21 Apr 2010 13:05:26 +0100, "Andrew Story" <andrewDOTst...

restrict timing that clients can download updates from wsus server
Hi all, i have installed wsus with sp2 on a windows 2003 server that is part of a domain. I am using AD GPO to enable automatic updates of my clients to download from my wsus server. As there will be 2 other site office that download updates from this server located in HQ, i wonder if its possible to limit the timing schedule they can download so as not to use up the bandwidth in the lease line between HQ and the other 2 sites. Thks in advance ...

ifdtest2 issue: GetOverlappedResult
When I run test 1 of ifdtest2, section e (power management tests), I get a couple of failures. Does anyone have an idea of what's wrong with my driver given the information below? Any input is of interest. Test 1: DO NOT INSERT smart card during hibernate mode Card out / card out - Hibernating now * FAILED - GetOverlappedResult failed >> Please insert smart card Passed Checking reader status * FAILED - Invalid reader state. Returned 1 Expected...

Issue in Closing USB Isochronous Pipe
Dear Team, I am implementing Isochronous transfers for my custom ARM based USB Audio device in WinCE,6.0. During driver initialization, I am opening isochronous out endpoint using the call p_dev->UsbFunPtrs->lpOpenPipe and getting a valid pipe handle. When the framework issues WAV_CLOSE ioctl, I need to close my opened isochronous endpoint using p_dev->UsbFunPtrs->lpClosePipe(OpenedPipeHandle); But this function never returns and the entire system hangs. The same code is working on other ARM based i.MX25 platform. I verified that p_dev, OpenedPipeHandle in the...

Task 'Microsoft Exchange Server' reported error (0x8004010F) : 'The operation failed. An object could not be found.' #2
Hi, I have been running Outlook 2003 through HTTP over RPC to connect to an Exchange 2003 server for some time without troubles until recently. All of the sudden both I and my users have been getting the following error on send / receive: "Task 'Microsoft Exchange Server' reported error (0x8004010F) : 'The operation failed. An object could not be found.'" I have tried rebuilding the offline address list and creating new profiles. We are able to send and receive mail without issue. But I am worried that this error is indicative of a problem i am not seeing and it...

DHCP Server
Good morning all, SBS 2003, all patches applied. Just recently DHCP basically stopped giving leases out and some computers cannot connect to SBS server intermittently. Checked all network cables, connections, switch, etc. All are OK. I deactivated scope, deleted it, rebuilt, activated scope, reconsiled, restarted DHCP server, no real errors in log. I have a machine that I am using as a test. My distribution range for the scope is from 192.168.16.1 to 192.168.16.100, with 1-26 excluded and .34 excluded because of a IPNAT error saying that address is in use. I see the D...