Constraint and Linked Server query problem

Hi,

I've got a problem querying a remote table via linked server (SQL Server 2000 SP4).
If I do
select * from <linked server>.<database name>.<db owner>.<Table1>
it returns all data, as expected.
However, if I do:
select * from <linked server>.<database name>.<db owner>.<table name>
where ColumnID = 51588
it doesn't return a row, even though it exists in the remote table.

A likely reason for this could be the fact that the column ColumnID has a constraint on it:
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [repl_identity_range_sub]
CHECK NOT FOR REPLICATION ([ColumnID] > 90000 and [ColumnID] < 95000)

I've tried making the constraint NOCHECK, but still get the same problem.
ALTER TABLE [dbo].[REGION_1] NOCHECK CONSTRAINT [repl_identity_range_sub]

Does anyone have an idea why this is hapenning and how to get around it (other than obviously just dropping the constraint - which works by the way, but can not be done as it is used).

Any help would be greatly appreciated.

NR

Hi,

never heard of that issue. If you have control over the remote server, start a SQl profiler session to see which statement is arriving on which database. Perhaps you are connection to the wrong server (or to another database). Querying with filters on remote server is no magic, so that should be no problem.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Already tried that and it's no help, all I can see is some system sps executing: sp_tables_info_rowset, sp_columns_rowset, sp_indexes_rowset, sp_check_constbytable_rowset, sp_table_statistics_rowset, a few DBCC SHOW_STATISTICS and then it executes sp_getschemalock followed by exec sp_releaseschemalock 1.

All those are for my table.

Weird thing is that a select * from the table works. Filtering on any other columns other than the one with constraint on it also works, it is only using the column with the constraint that cosistently does not work.

NR

|||

I would make sure that you are turning off constraints and doing the select in the same transaction. Otherwise I don't have any ideas.

It doesn't address the issue, but you may just want to do a pass-through openquery select to get around the issue.

|||

I'm afraid it doesn't help as the constraint is already marked as NOCHECK, and I'd rather not have to drop and recreate it (as it's put on by merge replication).

NR

|||I know it isn't a real answer to the problem, but if your needs are limited, you may want to just create a view that is outside the replication scheme and pull the remote data using the view. Good luck.|||

Hi,

I have the same problem and found the following solution to the problem.

I added an additional check constraint to each questionable table to deactivate the "optimization" filter that was intended for effective table partitioning:

ALTER TABLE [dbo].[Customer] WITH NOCHECK ADD CONSTRAINT [CK_Customer_AntiPartitioning] CHECK NOT FOR REPLICATION ([CustomerID] > 0)
ALTER TABLE [dbo].[Customer] NOCHECK CONSTRAINT [CK_Customer_AntiPartitioning]

the trick is that the optimization feature needs an unambiguous check constraint set that is not the case, because the replication range always overlap with the above added range. the "nocheck constraint" statement disables actual checking so that you do not have significant performance penalty

Greetings - Richie

Constraint and Linked Server query problem

Hi,

I've got a problem querying a remote table via linked server (SQL Server 2000 SP4).
If I do
select * from <linked server>.<database name>.<db owner>.<Table1>
it returns all data, as expected.
However, if I do:
select * from <linked server>.<database name>.<db owner>.<table name>
where ColumnID = 51588
it doesn't return a row, even though it exists in the remote table.

A likely reason for this could be the fact that the column ColumnID has a constraint on it:
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [repl_identity_range_sub]
CHECK NOT FOR REPLICATION ([ColumnID] > 90000 and [ColumnID] < 95000)

I've tried making the constraint NOCHECK, but still get the same problem.
ALTER TABLE [dbo].[REGION_1] NOCHECK CONSTRAINT [repl_identity_range_sub]

Does anyone have an idea why this is hapenning and how to get around it (other than obviously just dropping the constraint - which works by the way, but can not be done as it is used).

Any help would be greatly appreciated.

NR

Hi,

never heard of that issue. If you have control over the remote server, start a SQl profiler session to see which statement is arriving on which database. Perhaps you are connection to the wrong server (or to another database). Querying with filters on remote server is no magic, so that should be no problem.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Already tried that and it's no help, all I can see is some system sps executing: sp_tables_info_rowset, sp_columns_rowset, sp_indexes_rowset, sp_check_constbytable_rowset, sp_table_statistics_rowset, a few DBCC SHOW_STATISTICS and then it executes sp_getschemalock followed by exec sp_releaseschemalock 1.

All those are for my table.

Weird thing is that a select * from the table works. Filtering on any other columns other than the one with constraint on it also works, it is only using the column with the constraint that cosistently does not work.

NR

|||

I would make sure that you are turning off constraints and doing the select in the same transaction. Otherwise I don't have any ideas.

It doesn't address the issue, but you may just want to do a pass-through openquery select to get around the issue.

|||

I'm afraid it doesn't help as the constraint is already marked as NOCHECK, and I'd rather not have to drop and recreate it (as it's put on by merge replication).

NR

|||I know it isn't a real answer to the problem, but if your needs are limited, you may want to just create a view that is outside the replication scheme and pull the remote data using the view. Good luck.|||

Hi,

I have the same problem and found the following solution to the problem.

I added an additional check constraint to each questionable table to deactivate the "optimization" filter that was intended for effective table partitioning:

ALTER TABLE [dbo].[Customer] WITH NOCHECK ADD CONSTRAINT [CK_Customer_AntiPartitioning] CHECK NOT FOR REPLICATION ([CustomerID] > 0)
ALTER TABLE [dbo].[Customer] NOCHECK CONSTRAINT [CK_Customer_AntiPartitioning]

the trick is that the optimization feature needs an unambiguous check constraint set that is not the case, because the replication range always overlap with the above added range. the "nocheck constraint" statement disables actual checking so that you do not have significant performance penalty

Greetings - Richie

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
>

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: 0x000000004a9c00Could 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
> 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
> >
> >
>|||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
> 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
> >
> >
>

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 off
set
of the latest long IO is: 0x000000004a9c00Could 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 off
set
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
> 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[vbcol=seagreen]
> 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
> 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[vbcol=seagreen]
> offset
>