Constant locking issue

Hello,
I have a SQL server 2000 running on windows 2000 server, SP4 (4 CPU, and 2G
RAM)
We use this for Maximizer ECRM application, but lately (past 2-3month) we
seem to get more SQL locking issues while running application.
When I try to kill the proccess that causing the lock, it still shows the
proccess in Enterprise manager, but this leads to more locks somehow.
If I try to stop SQL server, at this stage, SQL server engine hangs then
times out (doesn't properly stop)
If I try to re-start the whole server, server itself hangs on the blue
screen trying to shut down
(I can re-start server, no problem, as long as these locks doesn't occur)
No CRM application changes.
Only changes on the server is constant windows patches, Symantec
Anti-Virus updates.
I don't know if any of those windows security update is the cause.
I donwloaded latest SQL 2000 updates and installed still seem to happen.
Nothing in the windows event log that tells me that I am having software or
hardware issues
I noticed this error in SQL event log though, but I don't remember getting
this error on previous SQL crashes, due to locks
Any idea appreciated
MC
SQL log
SQL Server has encountered 1 occurrence(s) of IO requests taking longer than
15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf] in
database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The offset
of the latest long IO is: 0x000000004a9c00
Could you run a diagnostic on your disk subsystem?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"MC" <webmaster@.ozoptics.com> wrote in message
news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
Hello,
I have a SQL server 2000 running on windows 2000 server, SP4 (4 CPU, and 2G
RAM)
We use this for Maximizer ECRM application, but lately (past 2-3month) we
seem to get more SQL locking issues while running application.
When I try to kill the proccess that causing the lock, it still shows the
proccess in Enterprise manager, but this leads to more locks somehow.
If I try to stop SQL server, at this stage, SQL server engine hangs then
times out (doesn't properly stop)
If I try to re-start the whole server, server itself hangs on the blue
screen trying to shut down
(I can re-start server, no problem, as long as these locks doesn't occur)
No CRM application changes.
Only changes on the server is constant windows patches, Symantec
Anti-Virus updates.
I don't know if any of those windows security update is the cause.
I donwloaded latest SQL 2000 updates and installed still seem to happen.
Nothing in the windows event log that tells me that I am having software or
hardware issues
I noticed this error in SQL event log though, but I don't remember getting
this error on previous SQL crashes, due to locks
Any idea appreciated
MC
SQL log
SQL Server has encountered 1 occurrence(s) of IO requests taking longer than
15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf] in
database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The offset
of the latest long IO is: 0x000000004a9c00
|||I ran Dell's Hard drive diagnostics (Dell Server) seem ok
(Server has Raid 5 configuration, OS and SQL program installed on C: drive,
my application data MDF, LDF files on E: drive, and log and DB backups to G
drive with plenty of space. Moved page file to G drive from C
The error that I mentioned, never remember seeing on same locking issues
before. According to Microsoft articles, that type of issue is not easy to
pinpoint.
MC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#MvpAszNGHA.2472@.TK2MSFTNGP11.phx.gbl...
> Could you run a diagnostic on your disk subsystem?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a SQL server 2000 running on windows 2000 server, SP4 (4 CPU, and
2G
> RAM)
> We use this for Maximizer ECRM application, but lately (past 2-3month) we
> seem to get more SQL locking issues while running application.
> When I try to kill the proccess that causing the lock, it still shows the
> proccess in Enterprise manager, but this leads to more locks somehow.
> If I try to stop SQL server, at this stage, SQL server engine hangs then
> times out (doesn't properly stop)
> If I try to re-start the whole server, server itself hangs on the blue
> screen trying to shut down
> (I can re-start server, no problem, as long as these locks doesn't occur)
> No CRM application changes.
> Only changes on the server is constant windows patches, Symantec
> Anti-Virus updates.
> I don't know if any of those windows security update is the cause.
> I donwloaded latest SQL 2000 updates and installed still seem to happen.
> Nothing in the windows event log that tells me that I am having software
or
> hardware issues
> I noticed this error in SQL event log though, but I don't remember
getting
> this error on previous SQL crashes, due to locks
> Any idea appreciated
> MC
> SQL log
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
than
> 15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf] in
> database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The
offset
> of the latest long IO is: 0x000000004a9c00
>
|||Your .MDF and .LDF files should be on separate drives. This is both for
performance as well as safety. If your E: drive blew, you'd be in trouble.
Also, RAID5 is slow for update. RAID10 is preferable, if you have space
and money.
Also, check my response re RAM. More is better.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"MC" <webmaster@.ozoptics.com> wrote in message
news:O1HeACIOGHA.2268@.TK2MSFTNGP09.phx.gbl...
I ran Dell's Hard drive diagnostics (Dell Server) seem ok
(Server has Raid 5 configuration, OS and SQL program installed on C: drive,
my application data MDF, LDF files on E: drive, and log and DB backups to G
drive with plenty of space. Moved page file to G drive from C
The error that I mentioned, never remember seeing on same locking issues
before. According to Microsoft articles, that type of issue is not easy to
pinpoint.
MC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#MvpAszNGHA.2472@.TK2MSFTNGP11.phx.gbl...
> Could you run a diagnostic on your disk subsystem?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:endIdYzNGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a SQL server 2000 running on windows 2000 server, SP4 (4 CPU, and
2G
> RAM)
> We use this for Maximizer ECRM application, but lately (past 2-3month) we
> seem to get more SQL locking issues while running application.
> When I try to kill the proccess that causing the lock, it still shows the
> proccess in Enterprise manager, but this leads to more locks somehow.
> If I try to stop SQL server, at this stage, SQL server engine hangs then
> times out (doesn't properly stop)
> If I try to re-start the whole server, server itself hangs on the blue
> screen trying to shut down
> (I can re-start server, no problem, as long as these locks doesn't occur)
> No CRM application changes.
> Only changes on the server is constant windows patches, Symantec
> Anti-Virus updates.
> I don't know if any of those windows security update is the cause.
> I donwloaded latest SQL 2000 updates and installed still seem to happen.
> Nothing in the windows event log that tells me that I am having software
or
> hardware issues
> I noticed this error in SQL event log though, but I don't remember
getting
> this error on previous SQL crashes, due to locks
> Any idea appreciated
> MC
> SQL log
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
than
> 15 seconds to complete on file [E:\MaxData\AddrBks\OZCUSTOMERS_Log.ldf] in
> database [OZCUSTOMERS] (15). The OS file handle is 0x000003C8. The
offset
> of the latest long IO is: 0x000000004a9c00
>
|||When it comes to having LDF file being in different drive,
this is only valid, if you have 2 physical drives and not valid if you have
Raid 5, or Raid 10
(4 x SCSI disk)
This is how we have been tought and many people says the samething. Did I
miss anything here?
MC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIsOohOOGHA.3896@.TK2MSFTNGP15.phx.gbl...
> Your .MDF and .LDF files should be on separate drives. This is both for
> performance as well as safety. If your E: drive blew, you'd be in
trouble.
> Also, RAID5 is slow for update. RAID10 is preferable, if you have space
> and money.
> Also, check my response re RAM. More is better.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:O1HeACIOGHA.2268@.TK2MSFTNGP09.phx.gbl...
> I ran Dell's Hard drive diagnostics (Dell Server) seem ok
> (Server has Raid 5 configuration, OS and SQL program installed on C:
drive,
> my application data MDF, LDF files on E: drive, and log and DB backups to
G[vbcol=seagreen]
> drive with plenty of space. Moved page file to G drive from C
> The error that I mentioned, never remember seeing on same locking issues
> before. According to Microsoft articles, that type of issue is not easy to
> pinpoint.
> MC
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#MvpAszNGHA.2472@.TK2MSFTNGP11.phx.gbl...
> 2G
we[vbcol=seagreen]
the[vbcol=seagreen]
occur)[vbcol=seagreen]
> or
> getting
> than
in
> offset
>
|||Of course I meant that they should be on separate physical drives. That
also applies if you are using RAID. What if 2 disks fail in your RAID5?
That said, have you been able to use System Monitor to look at the Avg Disk
Queue Lengths on each physical disk set - both read and write queues? The
figure should not be 2X the number of spindles.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"MC" <webmaster@.ozoptics.com> wrote in message
news:eYVKxqUOGHA.1360@.TK2MSFTNGP10.phx.gbl...
When it comes to having LDF file being in different drive,
this is only valid, if you have 2 physical drives and not valid if you have
Raid 5, or Raid 10
(4 x SCSI disk)
This is how we have been tought and many people says the samething. Did I
miss anything here?
MC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIsOohOOGHA.3896@.TK2MSFTNGP15.phx.gbl...
> Your .MDF and .LDF files should be on separate drives. This is both for
> performance as well as safety. If your E: drive blew, you'd be in
trouble.
> Also, RAID5 is slow for update. RAID10 is preferable, if you have space
> and money.
> Also, check my response re RAM. More is better.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "MC" <webmaster@.ozoptics.com> wrote in message
> news:O1HeACIOGHA.2268@.TK2MSFTNGP09.phx.gbl...
> I ran Dell's Hard drive diagnostics (Dell Server) seem ok
> (Server has Raid 5 configuration, OS and SQL program installed on C:
drive,
> my application data MDF, LDF files on E: drive, and log and DB backups to
G[vbcol=seagreen]
> drive with plenty of space. Moved page file to G drive from C
> The error that I mentioned, never remember seeing on same locking issues
> before. According to Microsoft articles, that type of issue is not easy to
> pinpoint.
> MC
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#MvpAszNGHA.2472@.TK2MSFTNGP11.phx.gbl...
> 2G
we[vbcol=seagreen]
the[vbcol=seagreen]
occur)[vbcol=seagreen]
> or
> getting
> than
in
> offset
>