Solved

Database Table Locks

Posted on 2006-06-16
12
247 Views
Last Modified: 2011-10-03
We are currently trying to "catch" data from another program's database before it is deleted.  The program is a document management system, so when a user deletes a "document", all records pertaining to that document are deleted in the database.  Our goal is to capture that data before it is deleted.  To do this, we have set up a trigger on the table that is updated when documents are deleted.  This trigger will call the following sp and "capture" all the data related to the document and put it into a table.  

When only one instance of the document management software is running, the sp runs fine and captures the data.  However, if two instances or more of the dm software are running, the program logs that deadlocks were created on the database and fails to delete the documents.  

The stored procedure is causing the deadlocks, but we're not sure exactly where and how.  All "select" statements are using "with (nolock)".  I know that update statements might be doing it as well as the insert statements.  How do we keep these statements from locking the tables?  Is there a way that the sp can check to see if an instance of the sp is already running, then wait until the instance is finished?


CREATE PROCEDURE get_docinfo
/*      @StoreID int,
      @VolumeID int
*/
AS

set nocount on
set transaction isolation level read uncommitted

/* Populate Tables */
INSERT INTO DeletedPageCache
                    (VolumeId, StoreId)
SELECT     VolumeId, StoreId
FROM         DeletedPage WITH (NOLOCK)


INSERT INTO DeletedPageCacheData
                      (VolumeId, StoreId)
SELECT DeletedPageCache.volumeid, DeletedPageCache.storeid
FROM DeletedPageCache with (nolock) LEFT JOIN DeletedPageCacheData ON DeletedPageCache.storeid = DeletedPageCacheData.storeid
WHERE (((DeletedPageCacheData.storeid) Is Null) AND ((DeletedPageCacheData.volumeid) Is Null))
      

/*  Update each record with a tocid */
      DECLARE @StoreID int
      DECLARE @VolumeID int      
      DECLARE @iTocID nvarchar(50)            
      
      DECLARE records CURSOR LOCAL for
      SELECT STOREID, VOLUMEID FROM DELETEDPAGECACHEDATA with(nolock) where tocid is null
      OPEN records
      FETCH NEXT FROM records
      into @StoreID, @VolumeID
      print @storeid
      WHILE @@FETCH_STATUS = 0
      BEGIN
            set @iTocID = (SELECT     top 1 Doc.TocId AS Expr1 FROM Toc with(nolock) INNER JOIN Doc ON Toc.TocId = Doc.TocId  INNER JOIN DeletedPageCacheData ON Toc.VolumeId = @volumeid AND Doc.StoreId = @storeid )
                  update deletedpagecachedata set tocid = @itocid where volumeid = @volumeid and storeid = storeid and tocid is null
            FETCH NEXT FROM records
            into @StoreID, @VolumeID
             END
             CLOSE records
             DEALLOCATE records


/* Begin loop for each path update */
      print 'Beginning record loop'
      DECLARE @tocstring nvarchar(500)
      DECLARE toc_cursor CURSOR LOCAL FOR
      SELECT tocid, storeid, volumeid FROM deletedpagecachedata with (nolock) where lfpath is null
      OPEN toc_cursor
      FETCH NEXT FROM toc_cursor
      into @itocid, @storeid, @volumeid
      WHILE @@FETCH_STATUS = 0
      BEGIN
            /* Get LF Path*/
            declare @counter int
            declare @mystring varchar(250)
            declare @objectname as varchar(250)
            declare @parent int
            set @parent = (select parentid from toc with (nolock) where tocid = @itocid)
            set @counter = 0
            set @mystring = ''
            set @objectname = ''
      
            while @objectname <> 'ROOT FOLDER'
            begin
                    set @counter = @counter + 1
                    set @objectname = (select name from toc with (nolock) where tocid = @parent)
                    set @mystring =  @objectname + '\' + @mystring
                    set @parent = (select parentid from toc with (nolock) where tocid = @parent)
            end
            print @mystring
            print @storeid
                       update deletedpagecachedata set lfpath = @mystring where volumeid = @volumeid and storeid = @storeid

            /*Get Volume Information*/
            declare @IsImageFixed varchar(10)
            declare @FixedPath varchar(50)
            declare @RemovablePath varchar(50)
            declare volume_cursor cursor for (SELECT     Vol.IsImageFixed AS IsImageFixed, Vol.FixedPath AS FixedPath, Vol.RemovablePath AS RemovablePath
            FROM         DeletedPageCacheData with(nolock)  INNER JOIN
                   Toc ON DeletedPageCacheData.TocID = @itocid  INNER JOIN
                           Vol ON Toc.VolumeId = Vol.VolumeId)
            OPEN volume_cursor
            FETCH NEXT FROM volume_cursor
            into @IsImageFixed, @FixedPath, @RemovablePath
            close volume_cursor
            deallocate volume_cursor

            update deletedpagecachedata set IsImageFixed=@IsImageFixed, FixedPath=@FixedPath, RemovablePath=@RemovablePath where volumeid = @volumeid and storeid=@storeid

            /*Get Document Name*/
            declare @DocumentName varchar(250)
            set @DocumentName = (select top 1 toc.name from toc with (nolock) where tocid = @itocid)
            update deletedpagecachedata set DocumentName  = @DocumentName where volumeid=@volumeid and storeid = @storeid

            /*Get Template Name*/
            declare @TemplateName varchar(100)
            set @TemplateName = (SELECT    top 1 Tstr.TemplateName AS TemplateName
            FROM         DeletedPageCacheData with(nolock) INNER JOIN
            Toc ON DeletedPageCacheData.TocID = Toc.TocId INNER JOIN
            Tstr ON Toc.TemplateId = Tstr.TemplateId
            WHERE     (DeletedPageCacheData.VolumeId = @volumeid) AND (DeletedPageCacheData.StoreId = @storeid))
            update deletedpagecachedata set TemplateName  = @TemplateName where volumeid=@volumeid and storeid = @storeid

            /*Get Table Name */
            declare @TableName varchar(50)
            set @TableName = (SELECT  top 1   Tstr.TableName
            FROM         DeletedPageCacheData with(nolock) INNER JOIN
            Toc ON DeletedPageCacheData.TocID = Toc.TocId INNER JOIN
            Tstr ON Toc.TemplateId = Tstr.TemplateId
            WHERE     (DeletedPageCacheData.VolumeId = @volumeid) AND (DeletedPageCacheData.StoreId = @storeid))
            update deletedpagecachedata set TableName  = @TableName where volumeid=@volumeid and storeid = @storeid

            /*Get PageID, PageNum*/
            declare @PageID varchar(50)
            declare @PageNum varchar(10)

            declare page_cursor cursor for  (SELECT DOC.pageid, doc.pagenum from doc with(nolock) where tocid = @itocid and storeid = @storeid)
            OPEN page_cursor
            FETCH NEXT FROM page_cursor
            into @PageID, @PageNum
            close page_cursor
            deallocate page_cursor
            update deletedpagecachedata set PageID = @PageId, PageNum = @PageNum where volumeid=@volumeid and storeid = @storeid

            /*Get Template Data*/
            declare @stmt as varchar(4000),
            @col as varchar(100)
            declare c cursor for
            select column_name from information_schema.columns with(nolock) where table_name = @TableName
            open c
            fetch next from c into @col
            set @stmt = 'update DeletedPageCacheData set DeletedPageCacheData.TemplateData =  '
            while @@fetch_status = 0
            begin
                 set @stmt = @stmt + 'rtrim(isnull(cast('+ @TableName + '.' + @col +' as varchar(100)),'''')) ' + ' + ''|'''
                                fetch next from c into @col

                 if @@fetch_status = 0
                 set @stmt = @stmt + '+ '
            end
            set @stmt = @stmt + ' from ' + @TableName + ' where DeletedPageCacheData.TocID = ' + @TableName + '.TocID'
            close c
            deallocate c
            
            exec (@stmt)


              FETCH NEXT  FROM toc_Cursor
              into @itocid, @storeid, @volumeid
      END
      CLOSE toc_Cursor
      DEALLOCATE toc_Cursor
      print 'Exited toc_cursor loop'
      /*exec master..xp_cmdshell 'C:\PROGRA~2\vb.net\SAFETY~1\v6\Console\bin\SafetyNet_Console.exe /RUN'*/

      truncate table deletedpagecache
GO
0
Comment
Question by:dalebrumbaugh
  • 5
  • 4
12 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16921077
0
 

Author Comment

by:dalebrumbaugh
ID: 16921895
All of the cursors are select  with (nolock) statements, though.  This should only be causing performance issues, not locking issues.  Does the table lock while a select command is being issued?  I'm thinking the update statements are causing this and different instances of the sp are being ran at the same time.  I think I need a way for the sp to wait for all instances of the sp to finish???  Of course, I don't know all the ins and outs of sql and t-sql, either.   Please advise.
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16929329
hi,

Change all the cursur defination to be for Read Only
Change all the read only tables to be with nolock
Create Necessary Indexes


CREATE PROCEDURE get_docinfo
/*     @StoreID int,
     @VolumeID int
*/
AS

set nocount on
set transaction isolation level read uncommitted

/* Populate Tables */
INSERT INTO DeletedPageCache
                    (VolumeId, StoreId)
SELECT     VolumeId, StoreId
FROM         DeletedPage WITH (NOLOCK)


INSERT INTO DeletedPageCacheData
                      (VolumeId, StoreId)
SELECT DeletedPageCache.volumeid, DeletedPageCache.storeid
FROM DeletedPageCache with (nolock) LEFT JOIN DeletedPageCacheData with (nolock) ON DeletedPageCache.storeid = DeletedPageCacheData.storeid
WHERE (((DeletedPageCacheData.storeid) Is Null) AND ((DeletedPageCacheData.volumeid) Is Null))
     

/*  Update each record with a tocid */
     DECLARE @StoreID int
     DECLARE @VolumeID int    
     DECLARE @iTocID nvarchar(50)          
     
     DECLARE records CURSOR LOCAL for
     SELECT STOREID, VOLUMEID FROM DELETEDPAGECACHEDATA with(nolock) where tocid is null for read only
     OPEN records
     FETCH NEXT FROM records
     into @StoreID, @VolumeID
     print @storeid
     WHILE @@FETCH_STATUS = 0
     BEGIN
          set @iTocID = (SELECT     top 1 Doc.TocId AS Expr1 FROM Toc with(nolock) INNER JOIN Doc ON Toc.TocId = Doc.TocId  INNER JOIN DeletedPageCacheData ON Toc.VolumeId = @volumeid AND Doc.StoreId = @storeid )
                 update deletedpagecachedata set tocid = @itocid where volumeid = @volumeid and storeid = storeid and tocid is null
          FETCH NEXT FROM records
          into @StoreID, @VolumeID
             END
             CLOSE records
             DEALLOCATE records


/* Begin loop for each path update */
     print 'Beginning record loop'
     DECLARE @tocstring nvarchar(500)
     DECLARE toc_cursor CURSOR LOCAL FOR
     SELECT tocid, storeid, volumeid FROM deletedpagecachedata with (nolock) where lfpath is null for read only
     OPEN toc_cursor
     FETCH NEXT FROM toc_cursor
     into @itocid, @storeid, @volumeid
     WHILE @@FETCH_STATUS = 0
     BEGIN
          /* Get LF Path*/
          declare @counter int
          declare @mystring varchar(250)
          declare @objectname as varchar(250)
          declare @parent int
          set @parent = (select parentid from toc with (nolock) where tocid = @itocid)
          set @counter = 0
          set @mystring = ''
          set @objectname = ''
     
          while @objectname <> 'ROOT FOLDER'
          begin
                 set @counter = @counter + 1
                 set @objectname = (select name from toc with (nolock) where tocid = @parent)
                 set @mystring =  @objectname + '\' + @mystring
                 set @parent = (select parentid from toc with (nolock) where tocid = @parent)
          end
          print @mystring
          print @storeid
                     update deletedpagecachedata set lfpath = @mystring where volumeid = @volumeid and storeid = @storeid

          /*Get Volume Information*/
          declare @IsImageFixed varchar(10)
          declare @FixedPath varchar(50)
          declare @RemovablePath varchar(50)
          declare volume_cursor cursor for (SELECT     Vol.IsImageFixed AS IsImageFixed, Vol.FixedPath AS FixedPath, Vol.RemovablePath AS RemovablePath
          FROM         DeletedPageCacheData with(nolock)  INNER JOIN
                  Toc with (nolock) ON DeletedPageCacheData.TocID = @itocid  INNER JOIN
                           Vol with (nolock) ON Toc.VolumeId = Vol.VolumeId) for read only
          OPEN volume_cursor
          FETCH NEXT FROM volume_cursor
          into @IsImageFixed, @FixedPath, @RemovablePath
          close volume_cursor
          deallocate volume_cursor

          update deletedpagecachedata set IsImageFixed=@IsImageFixed, FixedPath=@FixedPath, RemovablePath=@RemovablePath where volumeid = @volumeid and storeid=@storeid

          /*Get Document Name*/
          declare @DocumentName varchar(250)
          set @DocumentName = (select top 1 toc.name from toc with (nolock) where tocid = @itocid)
          update deletedpagecachedata set DocumentName  = @DocumentName where volumeid=@volumeid and storeid = @storeid

          /*Get Template Name*/
          declare @TemplateName varchar(100)
          set @TemplateName = (SELECT    top 1 Tstr.TemplateName AS TemplateName
          FROM         DeletedPageCacheData with(nolock) INNER JOIN
          Toc with (nolock) ON DeletedPageCacheData.TocID = Toc.TocId INNER JOIN
          Tstr  with (nolock)ON Toc.TemplateId = Tstr.TemplateId
          WHERE     (DeletedPageCacheData.VolumeId = @volumeid) AND (DeletedPageCacheData.StoreId = @storeid))
          update deletedpagecachedata set TemplateName  = @TemplateName where volumeid=@volumeid and storeid = @storeid

          /*Get Table Name */
          declare @TableName varchar(50)
          set @TableName = (SELECT  top 1   Tstr.TableName
          FROM         DeletedPageCacheData with(nolock) INNER JOIN
          Toc with (nolock) ON DeletedPageCacheData.TocID = Toc.TocId INNER JOIN
          Tstr with (nolock) ON Toc.TemplateId = Tstr.TemplateId
          WHERE     (DeletedPageCacheData.VolumeId = @volumeid) AND (DeletedPageCacheData.StoreId = @storeid))
          update deletedpagecachedata set TableName  = @TableName where volumeid=@volumeid and storeid = @storeid

          /*Get PageID, PageNum*/
          declare @PageID varchar(50)
          declare @PageNum varchar(10)

          declare page_cursor cursor for  (SELECT DOC.pageid, doc.pagenum from doc with(nolock) where tocid = @itocid and storeid = @storeid) for read only
          OPEN page_cursor
          FETCH NEXT FROM page_cursor
          into @PageID, @PageNum
          close page_cursor
          deallocate page_cursor
          update deletedpagecachedata set PageID = @PageId, PageNum = @PageNum where volumeid=@volumeid and storeid = @storeid

          /*Get Template Data*/
          declare @stmt as varchar(4000),
          @col as varchar(100)
          declare c cursor for
          select column_name from information_schema.columns with(nolock) where table_name = @TableName for read only
          open c
          fetch next from c into @col
          set @stmt = 'update DeletedPageCacheData set DeletedPageCacheData.TemplateData =  '
          while @@fetch_status = 0
          begin
               set @stmt = @stmt + 'rtrim(isnull(cast('+ @TableName + '.' + @col +' as varchar(100)),'''')) ' + ' + ''|'''
                               fetch next from c into @col

               if @@fetch_status = 0
               set @stmt = @stmt + '+ '
          end
          set @stmt = @stmt + ' from ' + @TableName + ' where DeletedPageCacheData.TocID = ' + @TableName + '.TocID'
          close c
          deallocate c
         
          exec (@stmt)


            FETCH NEXT  FROM toc_Cursor
            into @itocid, @storeid, @volumeid
     END
     CLOSE toc_Cursor
     DEALLOCATE toc_Cursor
     print 'Exited toc_cursor loop'
     /*exec master..xp_cmdshell 'C:\PROGRA~2\vb.net\SAFETY~1\v6\Console\bin\SafetyNet_Console.exe /RUN'*/

     truncate table deletedpagecache
GO
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16929336
In addition to above i will recommend to create this index

Create nonclustured index ix_deletepagecachedata on deletepagecachedate(volumeid, storeid)
go
0
 

Author Comment

by:dalebrumbaugh
ID: 16934252
What is meant by the following?

"Change all the read only tables to be with nolock"

How would this be done?  Remember that I can't "hard set" some tables to nolock, because they are used by another application.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 28

Expert Comment

by:imran_fast
ID: 16940048
Check this statement of yours
===================

INSERT INTO DeletedPageCacheData
                      (VolumeId, StoreId)
SELECT DeletedPageCache.volumeid, DeletedPageCache.storeid
FROM DeletedPageCache with (nolock)
LEFT JOIN DeletedPageCacheData with (nolock)  <---------------- Added nolock hint here.
ON DeletedPageCache.storeid = DeletedPageCacheData.storeid
WHERE (((DeletedPageCacheData.storeid) Is Null) AND ((DeletedPageCacheData.volumeid) Is Null))
0
 

Author Comment

by:dalebrumbaugh
ID: 16946562
I went through and made the suggested change, including all other join statements as well.  I am still getting the problem of a lock somewhere.

Any ideas where?

-------
CREATE PROCEDURE get_docinfo
/*      @StoreID int,
      @VolumeID int
*/
AS

set nocount on
set transaction isolation level read uncommitted

/* Populate Tables */
INSERT INTO DeletedPageCache
                    (VolumeId, StoreId)
SELECT     VolumeId, StoreId
FROM         DeletedPage WITH (NOLOCK)


INSERT INTO DeletedPageCacheData
                      (VolumeId, StoreId)
SELECT DeletedPageCache.volumeid, DeletedPageCache.storeid
FROM DeletedPageCache with (nolock) LEFT JOIN DeletedPageCacheData with (nolock) ON DeletedPageCache.storeid = DeletedPageCacheData.storeid
WHERE (((DeletedPageCacheData.storeid) Is Null) AND ((DeletedPageCacheData.volumeid) Is Null))
      

/*  Update each record with a tocid */
      DECLARE @StoreID int
      DECLARE @VolumeID int      
      DECLARE @iTocID nvarchar(50)            
      
      DECLARE records CURSOR READ_ONLY for
      SELECT STOREID, VOLUMEID FROM DELETEDPAGECACHEDATA with(nolock) where tocid is null
      OPEN records
      FETCH NEXT FROM records
      into @StoreID, @VolumeID
      print @storeid
      WHILE @@FETCH_STATUS = 0
      BEGIN
            set @iTocID = (SELECT     top 1 Doc.TocId AS Expr1 FROM Toc with(nolock) INNER JOIN Doc with (nolock) ON Toc.TocId = Doc.TocId  INNER JOIN DeletedPageCacheData with (nolock) ON Toc.VolumeId = @volumeid AND Doc.StoreId = @storeid )
                  update deletedpagecachedata set tocid = @itocid where volumeid = @volumeid and storeid = storeid and tocid is null
            FETCH NEXT FROM records
            into @StoreID, @VolumeID
             END
             CLOSE records
             DEALLOCATE records


/* Begin loop for each path update */
      print 'Beginning record loop'
      DECLARE @tocstring nvarchar(500)
      DECLARE toc_cursor CURSOR READ_ONLY FOR
      SELECT tocid, storeid, volumeid FROM deletedpagecachedata with (nolock) where lfpath is null
      OPEN toc_cursor
      FETCH NEXT FROM toc_cursor
      into @itocid, @storeid, @volumeid
      WHILE @@FETCH_STATUS = 0
      BEGIN
            /* Get LF Path*/
            declare @counter int
            declare @mystring varchar(250)
            declare @objectname as varchar(250)
            declare @parent int
            set @parent = (select parentid from toc with (nolock) where tocid = @itocid)
            set @counter = 0
            set @mystring = ''
            set @objectname = ''
      
            while @objectname <> 'ROOT FOLDER'
            begin
                    set @counter = @counter + 1
                    set @objectname = (select name from toc with (nolock) where tocid = @parent)
                    set @mystring =  @objectname + '\' + @mystring
                    set @parent = (select parentid from toc with (nolock) where tocid = @parent)
            end
            print @mystring
            print @storeid
                       update deletedpagecachedata set lfpath = @mystring where volumeid = @volumeid and storeid = @storeid

            /*Get Volume Information*/
            declare @IsImageFixed varchar(10)
            declare @FixedPath varchar(50)
            declare @RemovablePath varchar(50)
            declare volume_cursor cursor READ_ONLY for (SELECT     Vol.IsImageFixed AS IsImageFixed, Vol.FixedPath AS FixedPath, Vol.RemovablePath AS RemovablePath
            FROM         DeletedPageCacheData with(nolock)  INNER JOIN
                   Toc ON DeletedPageCacheData.TocID = @itocid  INNER JOIN
                           Vol with (nolock) ON Toc.VolumeId = Vol.VolumeId)
            OPEN volume_cursor
            FETCH NEXT FROM volume_cursor
            into @IsImageFixed, @FixedPath, @RemovablePath
            close volume_cursor
            deallocate volume_cursor

            update deletedpagecachedata set IsImageFixed=@IsImageFixed, FixedPath=@FixedPath, RemovablePath=@RemovablePath where volumeid = @volumeid and storeid=@storeid

            /*Get Document Name*/
            declare @DocumentName varchar(250)
            set @DocumentName = (select top 1 toc.name from toc with (nolock) where tocid = @itocid)
            update deletedpagecachedata set DocumentName  = @DocumentName where volumeid=@volumeid and storeid = @storeid

            /*Get Template Name*/
            declare @TemplateName varchar(100)
            set @TemplateName = (SELECT    top 1 Tstr.TemplateName AS TemplateName
            FROM         DeletedPageCacheData with(nolock) INNER JOIN
            Toc with (nolock) ON DeletedPageCacheData.TocID = Toc.TocId INNER JOIN
            Tstr with (nolock) ON Toc.TemplateId = Tstr.TemplateId
            WHERE     (DeletedPageCacheData.VolumeId = @volumeid) AND (DeletedPageCacheData.StoreId = @storeid))
            update deletedpagecachedata set TemplateName  = @TemplateName where volumeid=@volumeid and storeid = @storeid

            /*Get Table Name */
            declare @TableName varchar(50)
            set @TableName = (SELECT  top 1   Tstr.TableName
            FROM         DeletedPageCacheData with(nolock) INNER JOIN
            Toc with (nolock) ON DeletedPageCacheData.TocID = Toc.TocId INNER JOIN
            Tstr with (nolock) ON Toc.TemplateId = Tstr.TemplateId
            WHERE     (DeletedPageCacheData.VolumeId = @volumeid) AND (DeletedPageCacheData.StoreId = @storeid))
            update deletedpagecachedata set TableName  = @TableName where volumeid=@volumeid and storeid = @storeid

            /*Get PageID, PageNum*/
            declare @PageID varchar(50)
            declare @PageNum varchar(10)

            declare page_cursor cursor READ_ONLY for  (SELECT DOC.pageid, doc.pagenum from doc with(nolock) where tocid = @itocid and storeid = @storeid)
            OPEN page_cursor
            FETCH NEXT FROM page_cursor
            into @PageID, @PageNum
            close page_cursor
            deallocate page_cursor
            update deletedpagecachedata set PageID = @PageId, PageNum = @PageNum where volumeid=@volumeid and storeid = @storeid

            /*Get Template Data*/
            declare @stmt as varchar(4000),
            @col as varchar(100)
            declare c cursor READ_ONLY for
            select column_name from information_schema.columns with(nolock) where table_name = @TableName
            open c
            fetch next from c into @col
            set @stmt = 'update DeletedPageCacheData set DeletedPageCacheData.TemplateData =  '
            while @@fetch_status = 0
            begin
                 set @stmt = @stmt + 'rtrim(isnull(cast('+ @TableName + '.' + @col +' as varchar(100)),'''')) ' + ' + ''|'''
                                fetch next from c into @col

                 if @@fetch_status = 0
                 set @stmt = @stmt + '+ '
            end
            set @stmt = @stmt + ' from ' + @TableName + ' where DeletedPageCacheData.TocID = ' + @TableName + '.TocID'
            close c
            deallocate c
            
            exec (@stmt)


              FETCH NEXT  FROM toc_Cursor
              into @itocid, @storeid, @volumeid
      END
      CLOSE toc_Cursor
      DEALLOCATE toc_Cursor
      print 'Exited toc_cursor loop'
      /*exec master..xp_cmdshell 'C:\PROGRA~2\vb.net\SAFETY~1\v6\Console\bin\SafetyNet_Console.exe /RUN'*/

      truncate table deletedpagecache
GO
------
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16949643
You need to have following indexes
========================

create nonclustured index ix_DeletedPageCache on DeletedPageCache(storeid)
create nonclustured index ix_DeletedPageCacheData on DeletedPageCacheData(storeid,columnid)
create nonclustured index ix_DeletedPageCacheData_2 on DeletedPageCacheData(tocid)
create nonclustured index ix_DeletedPageCacheData_3 on DeletedPageCacheData(ifpath)

create nonclustured index ix_doc on doc(tocid, storeid)
create nonclustured index ix_vol on vol(volumeid)
create nonclustured index ix_toc on toc(volumeid)
create nonclustured index ix_tstr on tstr(templateid)

0
 

Author Comment

by:dalebrumbaugh
ID: 16950946
Thanks for the suggestion imran fast; I am still getting the deadlock however.  For future reference of anybody looking at this thread, nonclustured is actually nonclustered.  Just for clarificaiton in my own mind; would it not be the update statements that are locking the table?
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 500 total points
ID: 16977584
>>nonclustured is actually nonclustered
sorry that is just spelling mistake

>>would it not be the update statements that are locking the table?

Could be if DeletedPageCache has millions of record try running the above in text mode instead of grid mode and check if you are getting any responses from the query analyzer.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now