| Groups | Blog | Home |
sql server (microsoft) : 0 Blocking, but lots 'o locks...can that be right?
TIA, because I really hardly know where to begin with this issue...
Currently have a SQL Server 2000 system being accessed as a backend to a MS A2k mde. After about 6 years of working with relative pain-free ease, my tiny db started preventing users from being able to update one of the tables (of about 27). Everyone seems fine in the morning, but by afternoon, the table stops being editable. Access will run and timeout, no other errors. If I make an update directly in slq server during this time, again timeout with no other errors. The table is 1888 rows by 40 columns being accessed by no more than 20 folks, and more like 5-10 concurrently. In addition, there only appears to ever be 2 folks who wind up with many, many locks, while everyone else seems fine otherwise. They are not always the same 2 people. spid59: 51 IS page locks on this particular object (dbname.dbo.table.PK_table, owner type Xact) spid59: 1318 S key locaks, same object spid61: 39 IS page locks, same object spid61: 922 S key locaks, same object All users seem to have very high wait times (locks) So far, I have run the profiler index tuner wizard, and amazingly, what I orignally butchered does not seem to be the problem, as applying the recommended fixes did not correct the problem. In my reading about this issue, i found the aba_lockinfo stored procedure (and thanks by the way, though I forget who to thank). I have also run some of the various traces (deadlock trace, for instance) and have become overwhelmed with the new information in attempting to understand what it all means while still placating the users. For instance, the MSKB aticle here: http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q224453 says I should have blocks, but none appear to exist...or am I misinterpreting this? Can there be more key locks than records in the table? I think I began down the right path, but am now lost in the woods. Any help would be greatly appreciated.
[quoted text, click to view]
On Feb 28, 1:12 pm, "EJO" <MyD...@gmail.com> wrote: > TIA, because I really hardly know where to begin with this issue... > > Currently have a SQL Server 2000 system being accessed as a backend to > a MS A2k mde. After about 6 years of working with relative pain-free > ease, my tiny db started preventing users from being able to update > one of the tables (of about 27). Everyone seems fine in the morning, > but by afternoon, the table stops being editable. Access will run and > timeout, no other errors. If I make an update directly in slq server > during this time, again timeout with no other errors. The table is > 1888 rows by 40 columns being accessed by no more than 20 folks, and > more like 5-10 concurrently. In addition, there only appears to ever > be 2 folks who wind up with many, many locks, while everyone else > seems fine otherwise. They are not always the same 2 people. > > spid59: 51 IS page locks on this particular object > (dbname.dbo.table.PK_table, owner type Xact) > spid59: 1318 S key locaks, same object > spid61: 39 IS page locks, same object > spid61: 922 S key locaks, same object > > All users seem to have very high wait times (locks) > > So far, I have run the profiler index tuner wizard, and amazingly, > what I orignally butchered does not seem to be the problem, as > applying the recommended fixes did not correct the problem. In my > reading about this issue, i found the aba_lockinfo stored procedure > (and thanks by the way, though I forget who to thank). I have also > run some of the various traces (deadlock trace, for instance) and have > become overwhelmed with the new information in attempting to > understand what it all means while still placating the users. For > instance, the MSKB aticle here: > > http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q224453 > > says I should have blocks, but none appear to exist...or am I > misinterpreting this? Can there be more key locks than records in the > table? > > I think I began down the right path, but am now lost in the woods. > Any help would be greatly appreciated. What did you see when you ran sp_who2?
[quoted text, click to view]
> > What did you see when you ran sp_who2?- Hide quoted text - > Thanks, Steve. 0 Blocked by; the highest CPU time was 10543 against msdb login nt authority (sleeping) with a disk io of 8; being in the master db using EM was the next highest at 1297cpu/179io. The highest CPU/disk io related to the user db was 63CPU/8io. There were more than 2313 locks on this one table (3 table, 3669 key, 141 page). When I look at the other tables that have locks, there are only as many locks as there are current users. Granted, I have a lot to learn yet about sql server, let alone sql...but this can't be right or good, can it? Eric
MDB was obsolete 10 years ago.
Just use Access Data Projects [quoted text, click to view] On Feb 28, 1:12 pm, "EJO" <MyD...@gmail.com> wrote: > TIA, because I really hardly know where to begin with this issue... > > Currently have a SQL Server 2000 system being accessed as a backend to > a MS A2k mde. After about 6 years of working with relative pain-free > ease, my tiny db started preventing users from being able to update > one of the tables (of about 27). Everyone seems fine in the morning, > but by afternoon, the table stops being editable. Access will run and > timeout, no other errors. If I make an update directly in slq server > during this time, again timeout with no other errors. The table is > 1888 rows by 40 columns being accessed by no more than 20 folks, and > more like 5-10 concurrently. In addition, there only appears to ever > be 2 folks who wind up with many, many locks, while everyone else > seems fine otherwise. They are not always the same 2 people. > > spid59: 51 IS page locks on this particular object > (dbname.dbo.table.PK_table, owner type Xact) > spid59: 1318 S key locaks, same object > spid61: 39 IS page locks, same object > spid61: 922 S key locaks, same object > > All users seem to have very high wait times (locks) > > So far, I have run the profiler index tuner wizard, and amazingly, > what I orignally butchered does not seem to be the problem, as > applying the recommended fixes did not correct the problem. In my > reading about this issue, i found the aba_lockinfo stored procedure > (and thanks by the way, though I forget who to thank). I have also > run some of the various traces (deadlock trace, for instance) and have > become overwhelmed with the new information in attempting to > understand what it all means while still placating the users. For > instance, the MSKB aticle here: > > http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q224453 > > says I should have blocks, but none appear to exist...or am I > misinterpreting this? Can there be more key locks than records in the > table? > > I think I began down the right path, but am now lost in the woods. > Any help would be greatly appreciated.
Access MDB can't handle such a large table.
I swear to god; I've seen it myself 100 times. MDB won't scale to 5-20 mb of data.. so throw away MDB and spit on anyone that still uses it. -Aaron [quoted text, click to view] On Feb 28, 1:12 pm, "EJO" <MyD...@gmail.com> wrote: > TIA, because I really hardly know where to begin with this issue... > > Currently have a SQL Server 2000 system being accessed as a backend to > a MS A2k mde. After about 6 years of working with relative pain-free > ease, my tiny db started preventing users from being able to update > one of the tables (of about 27). Everyone seems fine in the morning, > but by afternoon, the table stops being editable. Access will run and > timeout, no other errors. If I make an update directly in slq server > during this time, again timeout with no other errors. The table is > 1888 rows by 40 columns being accessed by no more than 20 folks, and > more like 5-10 concurrently. In addition, there only appears to ever > be 2 folks who wind up with many, many locks, while everyone else > seems fine otherwise. They are not always the same 2 people. > > spid59: 51 IS page locks on this particular object > (dbname.dbo.table.PK_table, owner type Xact) > spid59: 1318 S key locaks, same object > spid61: 39 IS page locks, same object > spid61: 922 S key locaks, same object > > All users seem to have very high wait times (locks) > > So far, I have run the profiler index tuner wizard, and amazingly, > what I orignally butchered does not seem to be the problem, as > applying the recommended fixes did not correct the problem. In my > reading about this issue, i found the aba_lockinfo stored procedure > (and thanks by the way, though I forget who to thank). I have also > run some of the various traces (deadlock trace, for instance) and have > become overwhelmed with the new information in attempting to > understand what it all means while still placating the users. For > instance, the MSKB aticle here: > > http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q224453 > > says I should have blocks, but none appear to exist...or am I > misinterpreting this? Can there be more key locks than records in the > table? > > I think I began down the right path, but am now lost in the woods. > Any help would be greatly appreciated.
Thanks for response, sorry for the delay, took vacation...
[quoted text, click to view] > Access MDB can't handle such a large table. > >From access help: Microsoft Access database (.mdb) file size 2 gigabytes. However, because your database can include linked tables in other files, its total size is limited only by available storage capacity. and Number of fields in a table 255 Number of open tables 2048. The actual number may be less because of tables open internally by Microsoft Access. Table size 1 gigabyte Number of characters in a Text field 255 [quoted text, click to view] > > MDB won't scale to 5-20 mb of data.. so throw away MDB and spit on > anyone that still uses it. > None of my characteristics come close to exceeding these...and since this is a fe/be with the SQL Server file at less than 57mb, how can MS Access scaling be the issue?
Don't see what you're looking for? Try a search.
|
March 2003
April 2003
May 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |