Hello,
I have an installation of Reporting Services with SQL
Server 7.0 SP3. One of my reports is a data driven
subscription and it worked wonderfully.
Then, I applied Reporting Services Service Pack 1 and now
the data driven subscription does not work.
Any ideas?
Thanks,
DaveSorry, SQL Server 2000 SP3
>--Original Message--
>Hello,
>I have an installation of Reporting Services with SQL
>Server 7.0 SP3. One of my reports is a data driven
>subscription and it worked wonderfully.
>Then, I applied Reporting Services Service Pack 1 and now
>the data driven subscription does not work.
>Any ideas?
>Thanks,
>Dave
>.
>
Showing posts with label sp3. Show all posts
Showing posts with label sp3. Show all posts
Thursday, March 22, 2012
Monday, March 19, 2012
data corruption question
sql2k sp3
Based on recent experience Ive got a few questions about data corruption:
1> How does it happen?
2> What can be done to prevent it?
3> If it does happen, what are the proper steps to try to fix it without
restoring?
4> If I do need to restore, how do I know how far back to restore? I wouldnt
want to go through a lengthy restore process and then find out I still have
the corruption. Is there a way to know the day/time it first started?
TIA, ChrisRHi
99.999% of all corruption is caused by hardware.
Get good and reputable hardware and don't cut corners when installing it.
Have a UPS to keep the server up during power interruptions. Have battery
backed up RAID controller cards.
Make very regular backups, including transaction logs and get them off to
tape ASAP. Run regular DBCC's to check the consistency of the databases, and
have 'Torn page detection' on for each database. This will tell you very
quickly that some corruption has occurred. How often? Well, depending on
your uptime requirements, DBCC's daily does not sound like a bad idea.
Some times, corruption is not detected until weeks after it occurred so you
loose a lot. Daily, at worst case you loose a day's data.
Run the latest BIOS and driver versions, keep up to date with Windows SP's
and hotfixes and SQL SP's.
Don't plug you server and the kettle into the same wall plug, and keep the
Managers away from the servers. LOL.
Most of it is just common sense.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1A640B78-E981-4F57-B8A9-AF324C0ECE10@.microsoft.com...
> sql2k sp3
> Based on recent experience Ive got a few questions about data corruption:
> 1> How does it happen?
> 2> What can be done to prevent it?
> 3> If it does happen, what are the proper steps to try to fix it without
> restoring?
> 4> If I do need to restore, how do I know how far back to restore? I
wouldnt
> want to go through a lengthy restore process and then find out I still
have
> the corruption. Is there a way to know the day/time it first started?
> TIA, ChrisR
Based on recent experience Ive got a few questions about data corruption:
1> How does it happen?
2> What can be done to prevent it?
3> If it does happen, what are the proper steps to try to fix it without
restoring?
4> If I do need to restore, how do I know how far back to restore? I wouldnt
want to go through a lengthy restore process and then find out I still have
the corruption. Is there a way to know the day/time it first started?
TIA, ChrisRHi
99.999% of all corruption is caused by hardware.
Get good and reputable hardware and don't cut corners when installing it.
Have a UPS to keep the server up during power interruptions. Have battery
backed up RAID controller cards.
Make very regular backups, including transaction logs and get them off to
tape ASAP. Run regular DBCC's to check the consistency of the databases, and
have 'Torn page detection' on for each database. This will tell you very
quickly that some corruption has occurred. How often? Well, depending on
your uptime requirements, DBCC's daily does not sound like a bad idea.
Some times, corruption is not detected until weeks after it occurred so you
loose a lot. Daily, at worst case you loose a day's data.
Run the latest BIOS and driver versions, keep up to date with Windows SP's
and hotfixes and SQL SP's.
Don't plug you server and the kettle into the same wall plug, and keep the
Managers away from the servers. LOL.
Most of it is just common sense.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1A640B78-E981-4F57-B8A9-AF324C0ECE10@.microsoft.com...
> sql2k sp3
> Based on recent experience Ive got a few questions about data corruption:
> 1> How does it happen?
> 2> What can be done to prevent it?
> 3> If it does happen, what are the proper steps to try to fix it without
> restoring?
> 4> If I do need to restore, how do I know how far back to restore? I
wouldnt
> want to go through a lengthy restore process and then find out I still
have
> the corruption. Is there a way to know the day/time it first started?
> TIA, ChrisR
Data corruption on a replicated database
We are running SQL 2000 EI SP3 on a Windows 2000 Adv. Server
We have recently setup a database for replication using transactional replication every ten minutes
Shortly after implementing this, we started receiving integrity errors on the database on a daily basis
Many of the errors are requiring checktable with repair data loss to correct the problem. The corruption has shown up on both replicated and non-replicated tables, but only occurs if replication is running. As soon as replication was stopped, the database quit reporting integrity errors
Please let me know if you need further information
Has anyone else ran into this?I don't think replication has anything to do with it. Probably replication
is stressing your hard disk subsystem, leading to errors. So the real
culprit could be hardware. Check your event viewer and any hardware logs for
clues.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ghost" <anonymous@.discussions.microsoft.com> wrote in message
news:B6B0118A-C15D-41A5-9E11-FA01F4AD14BD@.microsoft.com...
We are running SQL 2000 EI SP3 on a Windows 2000 Adv. Server.
We have recently setup a database for replication using transactional
replication every ten minutes.
Shortly after implementing this, we started receiving integrity errors on
the database on a daily basis.
Many of the errors are requiring checktable with repair data loss to correct
the problem. The corruption has shown up on both replicated and
non-replicated tables, but only occurs if replication is running. As soon
as replication was stopped, the database quit reporting integrity errors.
Please let me know if you need further information.
Has anyone else ran into this?|||Our first thought was hardware as well, however, a few things are pointing to something else causing the problem
1. All of the hardware is brand new, and the server is running at about a quarter of our target performance. (This server is not yet hosting databases that will be added later this year... During peak time we are seeing a max utilization of 15% across all processors). The hardware vendor has already been over this server and hasn't found anything, and there is no hardware related errors in the server event log
2. This is the first enterprise class server we have run in a SAN environment. To eliminate the SAN hardware or network we moved all of the data to internal drives on the server. The problem continues to occur
3. This server is hosting multiple application databases. The only database with errors is the replicated database. If this was hardware related we would expect to see the other databases having problems, as they were using the same internal hardware and SAN array to hold their data as the replicated database
Any thoughts|||Okay, could you post the exact errors you are seeing in the SQL Server error
logs?
Are you replicating text or image columns?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ghost" <anonymous@.discussions.microsoft.com> wrote in message
news:34BB1D32-6935-40A8-B061-1372260A868B@.microsoft.com...
Our first thought was hardware as well, however, a few things are pointing
to something else causing the problem:
1. All of the hardware is brand new, and the server is running at about a
quarter of our target performance. (This server is not yet hosting databases
that will be added later this year... During peak time we are seeing a max
utilization of 15% across all processors). The hardware vendor has already
been over this server and hasn't found anything, and there is no hardware
related errors in the server event logs
2. This is the first enterprise class server we have run in a SAN
environment. To eliminate the SAN hardware or network we moved all of the
data to internal drives on the server. The problem continues to occur.
3. This server is hosting multiple application databases. The only
database with errors is the replicated database. If this was hardware
related we would expect to see the other databases having problems, as they
were using the same internal hardware and SAN array to hold their data as
the replicated database.
Any thoughts?|||Yes, There are both image and text columns in some of the tables
Below is excerpt from the maintenance log on the errors we are getting
[1] Database ServiceCenter: Check Data and Index Linkage..
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8929: [Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 525244926: Errors found in text ID 462078345216 owned by data record identified by RID = (1:202550:3)
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 525244926. The text, ntext, or image node at page (1:40468), slot 12, text ID 462078345216 is referenced by page (1:202550), slot 3, but was not seen in the scan
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 525244926. The text, ntext, or image node at page (1:695828), slot 12, text ID 462078345216 is not referenced
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Database 'ServiceCenter', index 'applicationm1.applicationm1_P' (ID 1589580701) (index ID 2). Extra or invalid key for the keys
[Microsoft][ODBC SQL Server Driver][SQL Server]Index row (1:81744:140) with values (application = 'cc.find.problem' and label = 'build.s) points to the data row identified by (RID = (1:348126:8))
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 3 consistency errors in table 'problemm1' (object ID 525244926)
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors in table 'applicationm1' (object ID 1589580701)
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 4 consistency errors in database 'ServiceCenter'
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (ServiceCenter ).|||There are a lot of bugs/problems, replicating text/image columns. First make
sure all participating servers are on the latest service pack. Then see if
you find any hits for those error numbers at: http://support.microsoft.com
And consider contacting Microsoft support.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ghost" <anonymous@.discussions.microsoft.com> wrote in message
news:07926FA4-64B3-4038-8934-E2A3A631DDE3@.microsoft.com...
Yes, There are both image and text columns in some of the tables.
Below is excerpt from the maintenance log on the errors we are getting:
[1] Database ServiceCenter: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8929: [Microsoft][ODBC SQL
Server Driver][SQL Server]Object ID 525244926: Errors found in text ID
462078345216 owned by data record identified by RID = (1:202550:3).
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
525244926. The text, ntext, or image node at page (1:40468), slot 12, text
ID 462078345216 is referenced by page (1:202550), slot 3, but was not seen
in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
525244926. The text, ntext, or image node at page (1:695828), slot 12, text
ID 462078345216 is not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Database
'ServiceCenter', index 'applicationm1.applicationm1_P' (ID 1589580701)
(index ID 2). Extra or invalid key for the keys:
[Microsoft][ODBC SQL Server Driver][SQL Server]Index row (1:81744:140) with
values (application = 'cc.find.problem' and label = 'build.s) points to the
data row identified by (RID = (1:348126:8)).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 3 consistency errors in table 'problemm1' (object ID 525244926).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 1 consistency errors in table 'applicationm1' (object ID
1589580701).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 4 consistency errors in database 'ServiceCenter'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the
minimum repair level for the errors found by DBCC CHECKDB (ServiceCenter ).
We have recently setup a database for replication using transactional replication every ten minutes
Shortly after implementing this, we started receiving integrity errors on the database on a daily basis
Many of the errors are requiring checktable with repair data loss to correct the problem. The corruption has shown up on both replicated and non-replicated tables, but only occurs if replication is running. As soon as replication was stopped, the database quit reporting integrity errors
Please let me know if you need further information
Has anyone else ran into this?I don't think replication has anything to do with it. Probably replication
is stressing your hard disk subsystem, leading to errors. So the real
culprit could be hardware. Check your event viewer and any hardware logs for
clues.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ghost" <anonymous@.discussions.microsoft.com> wrote in message
news:B6B0118A-C15D-41A5-9E11-FA01F4AD14BD@.microsoft.com...
We are running SQL 2000 EI SP3 on a Windows 2000 Adv. Server.
We have recently setup a database for replication using transactional
replication every ten minutes.
Shortly after implementing this, we started receiving integrity errors on
the database on a daily basis.
Many of the errors are requiring checktable with repair data loss to correct
the problem. The corruption has shown up on both replicated and
non-replicated tables, but only occurs if replication is running. As soon
as replication was stopped, the database quit reporting integrity errors.
Please let me know if you need further information.
Has anyone else ran into this?|||Our first thought was hardware as well, however, a few things are pointing to something else causing the problem
1. All of the hardware is brand new, and the server is running at about a quarter of our target performance. (This server is not yet hosting databases that will be added later this year... During peak time we are seeing a max utilization of 15% across all processors). The hardware vendor has already been over this server and hasn't found anything, and there is no hardware related errors in the server event log
2. This is the first enterprise class server we have run in a SAN environment. To eliminate the SAN hardware or network we moved all of the data to internal drives on the server. The problem continues to occur
3. This server is hosting multiple application databases. The only database with errors is the replicated database. If this was hardware related we would expect to see the other databases having problems, as they were using the same internal hardware and SAN array to hold their data as the replicated database
Any thoughts|||Okay, could you post the exact errors you are seeing in the SQL Server error
logs?
Are you replicating text or image columns?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ghost" <anonymous@.discussions.microsoft.com> wrote in message
news:34BB1D32-6935-40A8-B061-1372260A868B@.microsoft.com...
Our first thought was hardware as well, however, a few things are pointing
to something else causing the problem:
1. All of the hardware is brand new, and the server is running at about a
quarter of our target performance. (This server is not yet hosting databases
that will be added later this year... During peak time we are seeing a max
utilization of 15% across all processors). The hardware vendor has already
been over this server and hasn't found anything, and there is no hardware
related errors in the server event logs
2. This is the first enterprise class server we have run in a SAN
environment. To eliminate the SAN hardware or network we moved all of the
data to internal drives on the server. The problem continues to occur.
3. This server is hosting multiple application databases. The only
database with errors is the replicated database. If this was hardware
related we would expect to see the other databases having problems, as they
were using the same internal hardware and SAN array to hold their data as
the replicated database.
Any thoughts?|||Yes, There are both image and text columns in some of the tables
Below is excerpt from the maintenance log on the errors we are getting
[1] Database ServiceCenter: Check Data and Index Linkage..
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8929: [Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 525244926: Errors found in text ID 462078345216 owned by data record identified by RID = (1:202550:3)
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 525244926. The text, ntext, or image node at page (1:40468), slot 12, text ID 462078345216 is referenced by page (1:202550), slot 3, but was not seen in the scan
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 525244926. The text, ntext, or image node at page (1:695828), slot 12, text ID 462078345216 is not referenced
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Database 'ServiceCenter', index 'applicationm1.applicationm1_P' (ID 1589580701) (index ID 2). Extra or invalid key for the keys
[Microsoft][ODBC SQL Server Driver][SQL Server]Index row (1:81744:140) with values (application = 'cc.find.problem' and label = 'build.s) points to the data row identified by (RID = (1:348126:8))
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 3 consistency errors in table 'problemm1' (object ID 525244926)
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors in table 'applicationm1' (object ID 1589580701)
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 4 consistency errors in database 'ServiceCenter'
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (ServiceCenter ).|||There are a lot of bugs/problems, replicating text/image columns. First make
sure all participating servers are on the latest service pack. Then see if
you find any hits for those error numbers at: http://support.microsoft.com
And consider contacting Microsoft support.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ghost" <anonymous@.discussions.microsoft.com> wrote in message
news:07926FA4-64B3-4038-8934-E2A3A631DDE3@.microsoft.com...
Yes, There are both image and text columns in some of the tables.
Below is excerpt from the maintenance log on the errors we are getting:
[1] Database ServiceCenter: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8929: [Microsoft][ODBC SQL
Server Driver][SQL Server]Object ID 525244926: Errors found in text ID
462078345216 owned by data record identified by RID = (1:202550:3).
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
525244926. The text, ntext, or image node at page (1:40468), slot 12, text
ID 462078345216 is referenced by page (1:202550), slot 3, but was not seen
in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
525244926. The text, ntext, or image node at page (1:695828), slot 12, text
ID 462078345216 is not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Database
'ServiceCenter', index 'applicationm1.applicationm1_P' (ID 1589580701)
(index ID 2). Extra or invalid key for the keys:
[Microsoft][ODBC SQL Server Driver][SQL Server]Index row (1:81744:140) with
values (application = 'cc.find.problem' and label = 'build.s) points to the
data row identified by (RID = (1:348126:8)).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 3 consistency errors in table 'problemm1' (object ID 525244926).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 1 consistency errors in table 'applicationm1' (object ID
1589580701).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 4 consistency errors in database 'ServiceCenter'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the
minimum repair level for the errors found by DBCC CHECKDB (ServiceCenter ).
Data corruption on a replicated database
We are running SQL 2000 EI SP3 on a Windows 2000 Adv. Server.
We have recently setup a database for replication using transactional replic
ation every ten minutes.
Shortly after implementing this, we started receiving integrity errors on th
e database on a daily basis.
Many of the errors are requiring checktable with repair data loss to correct
the problem. The corruption has shown up on both replicated and non-replic
ated tables, but only occurs if replication is running. As soon as replicat
ion was stopped, the databa
se quit reporting integrity errors.
Please let me know if you need further information.
Has anyone else ran into this?I don't think replication has anything to do with it. Probably replication
is stressing your hard disk subsystem, leading to errors. So the real
culprit could be hardware. Check your event viewer and any hardware logs for
clues.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ghost" <anonymous@.discussions.microsoft.com> wrote in message
news:B6B0118A-C15D-41A5-9E11-FA01F4AD14BD@.microsoft.com...
We are running SQL 2000 EI SP3 on a Windows 2000 Adv. Server.
We have recently setup a database for replication using transactional
replication every ten minutes.
Shortly after implementing this, we started receiving integrity errors on
the database on a daily basis.
Many of the errors are requiring checktable with repair data loss to correct
the problem. The corruption has shown up on both replicated and
non-replicated tables, but only occurs if replication is running. As soon
as replication was stopped, the database quit reporting integrity errors.
Please let me know if you need further information.
Has anyone else ran into this?|||Our first thought was hardware as well, however, a few things are pointing t
o something else causing the problem:
1. All of the hardware is brand new, and the server is running at about a q
uarter of our target performance. (This server is not yet hosting databases
that will be added later this year... During peak time we are seeing a max u
tilization of 15% across
all processors). The hardware vendor has already been over this server and
hasn't found anything, and there is no hardware related errors in the server
event logs
2. This is the first enterprise class server we have run in a SAN environme
nt. To eliminate the SAN hardware or network we moved all of the data to in
ternal drives on the server. The problem continues to occur.
3. This server is hosting multiple application databases. The only databas
e with errors is the replicated database. If this was hardware related we w
ould expect to see the other databases having problems, as they were using t
he same internal hardware
and SAN array to hold their data as the replicated database.
Any thoughts?|||Okay, could you post the exact errors you are seeing in the SQL Server error
logs?
Are you replicating text or image columns?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ghost" <anonymous@.discussions.microsoft.com> wrote in message
news:34BB1D32-6935-40A8-B061-1372260A868B@.microsoft.com...
Our first thought was hardware as well, however, a few things are pointing
to something else causing the problem:
1. All of the hardware is brand new, and the server is running at about a
quarter of our target performance. (This server is not yet hosting databases
that will be added later this year... During peak time we are seeing a max
utilization of 15% across all processors). The hardware vendor has already
been over this server and hasn't found anything, and there is no hardware
related errors in the server event logs
2. This is the first enterprise class server we have run in a SAN
environment. To eliminate the SAN hardware or network we moved all of the
data to internal drives on the server. The problem continues to occur.
3. This server is hosting multiple application databases. The only
database with errors is the replicated database. If this was hardware
related we would expect to see the other databases having problems, as they
were using the same internal hardware and SAN array to hold their data as
the replicated database.
Any thoughts?|||Yes, There are both image and text columns in some of the tables.
Below is excerpt from the maintenance log on the errors we are getting:
[1] Database ServiceCenter: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8929: [Microsoft][ODBC SQL Server Driv
er][SQL Server]Object ID 525244926: Errors found in text ID 462078345216 own
ed by data record identified by RID = (1:202550:3).
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 525244926. The te
xt, ntext, or image node at page (1:40468), slot 12, text ID 462078345216 is
referenced by page (1:202550), slot 3, but was not seen in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 525244926. The te
xt, ntext, or image node at page (1:695828), slot 12, text ID 462078345216 i
s not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Database 'ServiceCenter', i
ndex 'applicationm1.applicationm1_P' (ID 1589580701) (index ID 2). Extra or
invalid key for the keys:
[Microsoft][ODBC SQL Server Driver][SQL Server]Index row (1:81744:140) with values (app
lication = 'cc.find.problem' and label = 'build.s) points to the data row id
entified by (RID = (1:348126:8)).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 3
consistency errors in table 'problemm1' (object ID 525244926).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1
consistency errors in table 'applicationm1' (object ID 1589580701).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 4
consistency errors in database 'ServiceCenter'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum re
pair level for the errors found by DBCC CHECKDB (ServiceCenter ).|||There are a lot of bugs/problems, replicating text/image columns. First make
sure all participating servers are on the latest service pack. Then see if
you find any hits for those error numbers at: http://support.microsoft.com
And consider contacting Microsoft support.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ghost" <anonymous@.discussions.microsoft.com> wrote in message
news:07926FA4-64B3-4038-8934-E2A3A631DDE3@.microsoft.com...
Yes, There are both image and text columns in some of the tables.
Below is excerpt from the maintenance log on the errors we are getting:
[1] Database ServiceCenter: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8929: [Microsoft][ODBC SQL
Server Driver][SQL Server]Object ID 525244926: Errors found in text ID
462078345216 owned by data record identified by RID = (1:202550:3).
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
525244926. The text, ntext, or image node at page (1:40468), slot 12, text
ID 462078345216 is referenced by page (1:202550), slot 3, but was not seen
in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
525244926. The text, ntext, or image node at page (1:695828), slot 12, text
ID 462078345216 is not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Database
'ServiceCenter', index 'applicationm1.applicationm1_P' (ID 1589580701)
(index ID 2). Extra or invalid key for the keys:
[Microsoft][ODBC SQL Server Driver][SQL Server]Index row (1:81744:140) with
values (application = 'cc.find.problem' and label = 'build.s) points to the
data row identified by (RID = (1:348126:8)).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 3 consistency errors in table 'problemm1' (object ID 525244926).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 1 consistency errors in table 'applicationm1' (object ID
1589580701).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 4 consistency errors in database 'ServiceCenter'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the
minimum repair level for the errors found by DBCC CHECKDB (ServiceCenter ).
We have recently setup a database for replication using transactional replic
ation every ten minutes.
Shortly after implementing this, we started receiving integrity errors on th
e database on a daily basis.
Many of the errors are requiring checktable with repair data loss to correct
the problem. The corruption has shown up on both replicated and non-replic
ated tables, but only occurs if replication is running. As soon as replicat
ion was stopped, the databa
se quit reporting integrity errors.
Please let me know if you need further information.
Has anyone else ran into this?I don't think replication has anything to do with it. Probably replication
is stressing your hard disk subsystem, leading to errors. So the real
culprit could be hardware. Check your event viewer and any hardware logs for
clues.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ghost" <anonymous@.discussions.microsoft.com> wrote in message
news:B6B0118A-C15D-41A5-9E11-FA01F4AD14BD@.microsoft.com...
We are running SQL 2000 EI SP3 on a Windows 2000 Adv. Server.
We have recently setup a database for replication using transactional
replication every ten minutes.
Shortly after implementing this, we started receiving integrity errors on
the database on a daily basis.
Many of the errors are requiring checktable with repair data loss to correct
the problem. The corruption has shown up on both replicated and
non-replicated tables, but only occurs if replication is running. As soon
as replication was stopped, the database quit reporting integrity errors.
Please let me know if you need further information.
Has anyone else ran into this?|||Our first thought was hardware as well, however, a few things are pointing t
o something else causing the problem:
1. All of the hardware is brand new, and the server is running at about a q
uarter of our target performance. (This server is not yet hosting databases
that will be added later this year... During peak time we are seeing a max u
tilization of 15% across
all processors). The hardware vendor has already been over this server and
hasn't found anything, and there is no hardware related errors in the server
event logs
2. This is the first enterprise class server we have run in a SAN environme
nt. To eliminate the SAN hardware or network we moved all of the data to in
ternal drives on the server. The problem continues to occur.
3. This server is hosting multiple application databases. The only databas
e with errors is the replicated database. If this was hardware related we w
ould expect to see the other databases having problems, as they were using t
he same internal hardware
and SAN array to hold their data as the replicated database.
Any thoughts?|||Okay, could you post the exact errors you are seeing in the SQL Server error
logs?
Are you replicating text or image columns?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ghost" <anonymous@.discussions.microsoft.com> wrote in message
news:34BB1D32-6935-40A8-B061-1372260A868B@.microsoft.com...
Our first thought was hardware as well, however, a few things are pointing
to something else causing the problem:
1. All of the hardware is brand new, and the server is running at about a
quarter of our target performance. (This server is not yet hosting databases
that will be added later this year... During peak time we are seeing a max
utilization of 15% across all processors). The hardware vendor has already
been over this server and hasn't found anything, and there is no hardware
related errors in the server event logs
2. This is the first enterprise class server we have run in a SAN
environment. To eliminate the SAN hardware or network we moved all of the
data to internal drives on the server. The problem continues to occur.
3. This server is hosting multiple application databases. The only
database with errors is the replicated database. If this was hardware
related we would expect to see the other databases having problems, as they
were using the same internal hardware and SAN array to hold their data as
the replicated database.
Any thoughts?|||Yes, There are both image and text columns in some of the tables.
Below is excerpt from the maintenance log on the errors we are getting:
[1] Database ServiceCenter: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8929: [Microsoft][ODBC SQL Server Driv
er][SQL Server]Object ID 525244926: Errors found in text ID 462078345216 own
ed by data record identified by RID = (1:202550:3).
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 525244926. The te
xt, ntext, or image node at page (1:40468), slot 12, text ID 462078345216 is
referenced by page (1:202550), slot 3, but was not seen in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 525244926. The te
xt, ntext, or image node at page (1:695828), slot 12, text ID 462078345216 i
s not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Database 'ServiceCenter', i
ndex 'applicationm1.applicationm1_P' (ID 1589580701) (index ID 2). Extra or
invalid key for the keys:
[Microsoft][ODBC SQL Server Driver][SQL Server]Index row (1:81744:140) with values (app
lication = 'cc.find.problem' and label = 'build.s) points to the data row id
entified by (RID = (1:348126:8)).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 3
consistency errors in table 'problemm1' (object ID 525244926).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1
consistency errors in table 'applicationm1' (object ID 1589580701).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 4
consistency errors in database 'ServiceCenter'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum re
pair level for the errors found by DBCC CHECKDB (ServiceCenter ).|||There are a lot of bugs/problems, replicating text/image columns. First make
sure all participating servers are on the latest service pack. Then see if
you find any hits for those error numbers at: http://support.microsoft.com
And consider contacting Microsoft support.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ghost" <anonymous@.discussions.microsoft.com> wrote in message
news:07926FA4-64B3-4038-8934-E2A3A631DDE3@.microsoft.com...
Yes, There are both image and text columns in some of the tables.
Below is excerpt from the maintenance log on the errors we are getting:
[1] Database ServiceCenter: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8929: [Microsoft][ODBC SQL
Server Driver][SQL Server]Object ID 525244926: Errors found in text ID
462078345216 owned by data record identified by RID = (1:202550:3).
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
525244926. The text, ntext, or image node at page (1:40468), slot 12, text
ID 462078345216 is referenced by page (1:202550), slot 3, but was not seen
in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
525244926. The text, ntext, or image node at page (1:695828), slot 12, text
ID 462078345216 is not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Database
'ServiceCenter', index 'applicationm1.applicationm1_P' (ID 1589580701)
(index ID 2). Extra or invalid key for the keys:
[Microsoft][ODBC SQL Server Driver][SQL Server]Index row (1:81744:140) with
values (application = 'cc.find.problem' and label = 'build.s) points to the
data row identified by (RID = (1:348126:8)).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 3 consistency errors in table 'problemm1' (object ID 525244926).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 1 consistency errors in table 'applicationm1' (object ID
1589580701).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 4 consistency errors in database 'ServiceCenter'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the
minimum repair level for the errors found by DBCC CHECKDB (ServiceCenter ).
Sunday, March 11, 2012
data conversion -- varchar to nvarchar
Hello,
The following question applies to SQL Server 2000 SP3 and SQL Server 2005:
We are in the process of investigating international support for our
application and part of that will likely require changing all of the
char/varchar columns in our database to nchar/nvarchar.
From what I gather by reading these forums, altering the columns in place
via ALTER TABLE statements is an acceptable method for doing this.
The question that I have been unable to completely confirm, however, is what
happens to existing data in the tables? Does the data get converted to
Unicode as part of the ALTER TABLE statement? And, if so, is there any risk
that this conversion will produce unexpected or unpredictable results?
Preliminary testing seems to indicate that the data does get converted
successfully during this process but I am just trying to confirm that
suspicion.
Thanks in advance for any assistance,
JohnLx> From what I gather by reading these forums, altering the columns in place
> via ALTER TABLE statements is an acceptable method for doing this.
> The question that I have been unable to completely confirm, however, is
> what
> happens to existing data in the tables? Does the data get converted to
> Unicode as part of the ALTER TABLE statement?
Yes, but of course there can't already be any data in there that requires
Unicode. Other than disk space requirements for those columns doubling (and
don't forget indexes), you shouldn't see any noticeable difference, except
(see next comment).
> And, if so, is there any risk
> that this conversion will produce unexpected or unpredictable results?
Absolutely. For char/varchar in the smaller size ranges (up to 4000) you
shouldn't see any problems. However, if you have a varchar(8000) with at
least one tuple with 4001 or more characters, you will get the following
when you try to convert VARCHAR(8000) to NVARCHAR(4000) (the largest size
for varying double-wide):
Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
In addition, you'll want to reindex and update statistics for any tables
that have indexes/statistics on the column(s) you're changing. You'll also
want to verify that you re-compile (and alter params and converts, where
necessary) any stored procedures or functions that reference the columns.
You might also want to issue sp_refreshview for any views that reference
those tables.
A|||John,
the data get's converted in place as long as they fit into the new
datatype. Inyour case there shouldn't be an problem. The only thing
you have to keep in mind is while you can store up 8000 char in a
varchar column, nvarchar uses twice the space and thus the limit is
4000 characters.
Markus|||Whenever you alter the structure of a table, it can cause index
fragmentation. You will want to use DBCC INDEXDEFRAG for each altered table.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"johnlx" <nomail@.discussions.microsoft.com> wrote in message
news:2288C9DC-40B8-48E0-A63B-143A622A7030@.microsoft.com...
> Hello,
> The following question applies to SQL Server 2000 SP3 and SQL Server 2005:
> We are in the process of investigating international support for our
> application and part of that will likely require changing all of the
> char/varchar columns in our database to nchar/nvarchar.
> From what I gather by reading these forums, altering the columns in place
> via ALTER TABLE statements is an acceptable method for doing this.
> The question that I have been unable to completely confirm, however, is
> what
> happens to existing data in the tables? Does the data get converted to
> Unicode as part of the ALTER TABLE statement? And, if so, is there any
> risk
> that this conversion will produce unexpected or unpredictable results?
> Preliminary testing seems to indicate that the data does get converted
> successfully during this process but I am just trying to confirm that
> suspicion.
> Thanks in advance for any assistance,
> JohnLx
>|||Thanks to everyone who responded. You confirmed what I thought with regards
to the data conversion.
Aaron, good point about the views. I hadn't thought to rebuild them but
will include that in the process.
Thanks,
--John Lennox|||On Mon, 19 Dec 2005 14:23:08 -0500, Aaron Bertrand [SQL Server MVP]
wrote:
(snip)
>In addition, you'll want to reindex and update statistics for any tables
>that have indexes/statistics on the column(s) you're changing. You'll also
>want to verify that you re-compile (and alter params and converts, where
>necessary) any stored procedures or functions that reference the columns.
>You might also want to issue sp_refreshview for any views that reference
>those tables.
Hi Aaron (and JohnLx),
And to avoid implicit conversions that might damage performance, the
next step would be to check all variable declarations, temp table
definitions and table variable definitions - they too should be changed
from varchar to nvarchar and from char to nchar.
And finally, put an N in front of all string constants in your code.
(I.e., use SET @.var = N'Text', not SET @.var = 'Text')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
The following question applies to SQL Server 2000 SP3 and SQL Server 2005:
We are in the process of investigating international support for our
application and part of that will likely require changing all of the
char/varchar columns in our database to nchar/nvarchar.
From what I gather by reading these forums, altering the columns in place
via ALTER TABLE statements is an acceptable method for doing this.
The question that I have been unable to completely confirm, however, is what
happens to existing data in the tables? Does the data get converted to
Unicode as part of the ALTER TABLE statement? And, if so, is there any risk
that this conversion will produce unexpected or unpredictable results?
Preliminary testing seems to indicate that the data does get converted
successfully during this process but I am just trying to confirm that
suspicion.
Thanks in advance for any assistance,
JohnLx> From what I gather by reading these forums, altering the columns in place
> via ALTER TABLE statements is an acceptable method for doing this.
> The question that I have been unable to completely confirm, however, is
> what
> happens to existing data in the tables? Does the data get converted to
> Unicode as part of the ALTER TABLE statement?
Yes, but of course there can't already be any data in there that requires
Unicode. Other than disk space requirements for those columns doubling (and
don't forget indexes), you shouldn't see any noticeable difference, except
(see next comment).
> And, if so, is there any risk
> that this conversion will produce unexpected or unpredictable results?
Absolutely. For char/varchar in the smaller size ranges (up to 4000) you
shouldn't see any problems. However, if you have a varchar(8000) with at
least one tuple with 4001 or more characters, you will get the following
when you try to convert VARCHAR(8000) to NVARCHAR(4000) (the largest size
for varying double-wide):
Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
In addition, you'll want to reindex and update statistics for any tables
that have indexes/statistics on the column(s) you're changing. You'll also
want to verify that you re-compile (and alter params and converts, where
necessary) any stored procedures or functions that reference the columns.
You might also want to issue sp_refreshview for any views that reference
those tables.
A|||John,
the data get's converted in place as long as they fit into the new
datatype. Inyour case there shouldn't be an problem. The only thing
you have to keep in mind is while you can store up 8000 char in a
varchar column, nvarchar uses twice the space and thus the limit is
4000 characters.
Markus|||Whenever you alter the structure of a table, it can cause index
fragmentation. You will want to use DBCC INDEXDEFRAG for each altered table.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"johnlx" <nomail@.discussions.microsoft.com> wrote in message
news:2288C9DC-40B8-48E0-A63B-143A622A7030@.microsoft.com...
> Hello,
> The following question applies to SQL Server 2000 SP3 and SQL Server 2005:
> We are in the process of investigating international support for our
> application and part of that will likely require changing all of the
> char/varchar columns in our database to nchar/nvarchar.
> From what I gather by reading these forums, altering the columns in place
> via ALTER TABLE statements is an acceptable method for doing this.
> The question that I have been unable to completely confirm, however, is
> what
> happens to existing data in the tables? Does the data get converted to
> Unicode as part of the ALTER TABLE statement? And, if so, is there any
> risk
> that this conversion will produce unexpected or unpredictable results?
> Preliminary testing seems to indicate that the data does get converted
> successfully during this process but I am just trying to confirm that
> suspicion.
> Thanks in advance for any assistance,
> JohnLx
>|||Thanks to everyone who responded. You confirmed what I thought with regards
to the data conversion.
Aaron, good point about the views. I hadn't thought to rebuild them but
will include that in the process.
Thanks,
--John Lennox|||On Mon, 19 Dec 2005 14:23:08 -0500, Aaron Bertrand [SQL Server MVP]
wrote:
(snip)
>In addition, you'll want to reindex and update statistics for any tables
>that have indexes/statistics on the column(s) you're changing. You'll also
>want to verify that you re-compile (and alter params and converts, where
>necessary) any stored procedures or functions that reference the columns.
>You might also want to issue sp_refreshview for any views that reference
>those tables.
Hi Aaron (and JohnLx),
And to avoid implicit conversions that might damage performance, the
next step would be to check all variable declarations, temp table
definitions and table variable definitions - they too should be changed
from varchar to nvarchar and from char to nchar.
And finally, put an N in front of all string constants in your code.
(I.e., use SET @.var = N'Text', not SET @.var = 'Text')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Friday, February 24, 2012
data "movement" the culprit?
sql2k sp3
I have a report query that sometimes runs in 3 seconds, other times I kill
it after 10 minutes. When its taking a long time, there is no no blocking, no
extremely high cpu/ memory usage, no huge amount of activity. Something I
just noticed though is that someone put the Clustered Index on a date filed
even though this is an OLTP DB. Is there any possibility that when its taking
a long time to return results, whats happening is that data was recently
inserted that would cause the data in the table to move because of where the
clustering is? That SQL is searching for a moving target based on the data
range of the SP, which is in the middle of being rearranged because of it's
clustering setup? If this were the case, would it cause actual blocking(dont
forget Im not seeing any.)? I know I'm grasping here, but dont know what else
could cause this.
TIA, ChrisRIt's unlikely that is the cause. A page split if it occurs should only take
ms not minutes. And you would see blocking. My guess is you have a bad query
plan and disk bottlenecks. But the only way to be sure is to monitor the
server from all aspects to see what is the bottleneck. What about wait's
during that time?
--
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
> sql2k sp3
> I have a report query that sometimes runs in 3 seconds, other times I kill
> it after 10 minutes. When its taking a long time, there is no no blocking,
> no
> extremely high cpu/ memory usage, no huge amount of activity. Something I
> just noticed though is that someone put the Clustered Index on a date
> filed
> even though this is an OLTP DB. Is there any possibility that when its
> taking
> a long time to return results, whats happening is that data was recently
> inserted that would cause the data in the table to move because of where
> the
> clustering is? That SQL is searching for a moving target based on the data
> range of the SP, which is in the middle of being rearranged because of
> it's
> clustering setup? If this were the case, would it cause actual
> blocking(dont
> forget Im not seeing any.)? I know I'm grasping here, but dont know what
> else
> could cause this.
> TIA, ChrisR|||I havent looked, but will. Does this number represent how long it take SQL to
satisfy a query? Im doing my testing in Query Analyzer so I can already tell
that.
"Andrew J. Kelly" wrote:
> It's unlikely that is the cause. A page split if it occurs should only take
> ms not minutes. And you would see blocking. My guess is you have a bad query
> plan and disk bottlenecks. But the only way to be sure is to monitor the
> server from all aspects to see what is the bottleneck. What about wait's
> during that time?
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
> > sql2k sp3
> >
> > I have a report query that sometimes runs in 3 seconds, other times I kill
> > it after 10 minutes. When its taking a long time, there is no no blocking,
> > no
> > extremely high cpu/ memory usage, no huge amount of activity. Something I
> > just noticed though is that someone put the Clustered Index on a date
> > filed
> > even though this is an OLTP DB. Is there any possibility that when its
> > taking
> > a long time to return results, whats happening is that data was recently
> > inserted that would cause the data in the table to move because of where
> > the
> > clustering is? That SQL is searching for a moving target based on the data
> > range of the SP, which is in the middle of being rearranged because of
> > it's
> > clustering setup? If this were the case, would it cause actual
> > blocking(dont
> > forget Im not seeing any.)? I know I'm grasping here, but dont know what
> > else
> > could cause this.
> >
> > TIA, ChrisR
>
>|||Waits tell you allot about what sql server is waiting on most. See here for
more details:
http://sqldev.net/misc/WaitTypes.htm Wait Types
--
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:90871384-25AD-485B-BB51-B623B8671647@.microsoft.com...
>I havent looked, but will. Does this number represent how long it take SQL
>to
> satisfy a query? Im doing my testing in Query Analyzer so I can already
> tell
> that.
> "Andrew J. Kelly" wrote:
>> It's unlikely that is the cause. A page split if it occurs should only
>> take
>> ms not minutes. And you would see blocking. My guess is you have a bad
>> query
>> plan and disk bottlenecks. But the only way to be sure is to monitor the
>> server from all aspects to see what is the bottleneck. What about wait's
>> during that time?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
>> > sql2k sp3
>> >
>> > I have a report query that sometimes runs in 3 seconds, other times I
>> > kill
>> > it after 10 minutes. When its taking a long time, there is no no
>> > blocking,
>> > no
>> > extremely high cpu/ memory usage, no huge amount of activity. Something
>> > I
>> > just noticed though is that someone put the Clustered Index on a date
>> > filed
>> > even though this is an OLTP DB. Is there any possibility that when its
>> > taking
>> > a long time to return results, whats happening is that data was
>> > recently
>> > inserted that would cause the data in the table to move because of
>> > where
>> > the
>> > clustering is? That SQL is searching for a moving target based on the
>> > data
>> > range of the SP, which is in the middle of being rearranged because of
>> > it's
>> > clustering setup? If this were the case, would it cause actual
>> > blocking(dont
>> > forget Im not seeing any.)? I know I'm grasping here, but dont know
>> > what
>> > else
>> > could cause this.
>> >
>> > TIA, ChrisR
>>
I have a report query that sometimes runs in 3 seconds, other times I kill
it after 10 minutes. When its taking a long time, there is no no blocking, no
extremely high cpu/ memory usage, no huge amount of activity. Something I
just noticed though is that someone put the Clustered Index on a date filed
even though this is an OLTP DB. Is there any possibility that when its taking
a long time to return results, whats happening is that data was recently
inserted that would cause the data in the table to move because of where the
clustering is? That SQL is searching for a moving target based on the data
range of the SP, which is in the middle of being rearranged because of it's
clustering setup? If this were the case, would it cause actual blocking(dont
forget Im not seeing any.)? I know I'm grasping here, but dont know what else
could cause this.
TIA, ChrisRIt's unlikely that is the cause. A page split if it occurs should only take
ms not minutes. And you would see blocking. My guess is you have a bad query
plan and disk bottlenecks. But the only way to be sure is to monitor the
server from all aspects to see what is the bottleneck. What about wait's
during that time?
--
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
> sql2k sp3
> I have a report query that sometimes runs in 3 seconds, other times I kill
> it after 10 minutes. When its taking a long time, there is no no blocking,
> no
> extremely high cpu/ memory usage, no huge amount of activity. Something I
> just noticed though is that someone put the Clustered Index on a date
> filed
> even though this is an OLTP DB. Is there any possibility that when its
> taking
> a long time to return results, whats happening is that data was recently
> inserted that would cause the data in the table to move because of where
> the
> clustering is? That SQL is searching for a moving target based on the data
> range of the SP, which is in the middle of being rearranged because of
> it's
> clustering setup? If this were the case, would it cause actual
> blocking(dont
> forget Im not seeing any.)? I know I'm grasping here, but dont know what
> else
> could cause this.
> TIA, ChrisR|||I havent looked, but will. Does this number represent how long it take SQL to
satisfy a query? Im doing my testing in Query Analyzer so I can already tell
that.
"Andrew J. Kelly" wrote:
> It's unlikely that is the cause. A page split if it occurs should only take
> ms not minutes. And you would see blocking. My guess is you have a bad query
> plan and disk bottlenecks. But the only way to be sure is to monitor the
> server from all aspects to see what is the bottleneck. What about wait's
> during that time?
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
> > sql2k sp3
> >
> > I have a report query that sometimes runs in 3 seconds, other times I kill
> > it after 10 minutes. When its taking a long time, there is no no blocking,
> > no
> > extremely high cpu/ memory usage, no huge amount of activity. Something I
> > just noticed though is that someone put the Clustered Index on a date
> > filed
> > even though this is an OLTP DB. Is there any possibility that when its
> > taking
> > a long time to return results, whats happening is that data was recently
> > inserted that would cause the data in the table to move because of where
> > the
> > clustering is? That SQL is searching for a moving target based on the data
> > range of the SP, which is in the middle of being rearranged because of
> > it's
> > clustering setup? If this were the case, would it cause actual
> > blocking(dont
> > forget Im not seeing any.)? I know I'm grasping here, but dont know what
> > else
> > could cause this.
> >
> > TIA, ChrisR
>
>|||Waits tell you allot about what sql server is waiting on most. See here for
more details:
http://sqldev.net/misc/WaitTypes.htm Wait Types
--
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:90871384-25AD-485B-BB51-B623B8671647@.microsoft.com...
>I havent looked, but will. Does this number represent how long it take SQL
>to
> satisfy a query? Im doing my testing in Query Analyzer so I can already
> tell
> that.
> "Andrew J. Kelly" wrote:
>> It's unlikely that is the cause. A page split if it occurs should only
>> take
>> ms not minutes. And you would see blocking. My guess is you have a bad
>> query
>> plan and disk bottlenecks. But the only way to be sure is to monitor the
>> server from all aspects to see what is the bottleneck. What about wait's
>> during that time?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
>> > sql2k sp3
>> >
>> > I have a report query that sometimes runs in 3 seconds, other times I
>> > kill
>> > it after 10 minutes. When its taking a long time, there is no no
>> > blocking,
>> > no
>> > extremely high cpu/ memory usage, no huge amount of activity. Something
>> > I
>> > just noticed though is that someone put the Clustered Index on a date
>> > filed
>> > even though this is an OLTP DB. Is there any possibility that when its
>> > taking
>> > a long time to return results, whats happening is that data was
>> > recently
>> > inserted that would cause the data in the table to move because of
>> > where
>> > the
>> > clustering is? That SQL is searching for a moving target based on the
>> > data
>> > range of the SP, which is in the middle of being rearranged because of
>> > it's
>> > clustering setup? If this were the case, would it cause actual
>> > blocking(dont
>> > forget Im not seeing any.)? I know I'm grasping here, but dont know
>> > what
>> > else
>> > could cause this.
>> >
>> > TIA, ChrisR
>>
Damn Pesky ' RED X'
How...Why...
Running SQL 2000 SP3 & Win 2000 Adv Srv.
I cannot get rid of the Red X that appears in my replication monitor.
I have run 'sp_MSload_replication_status' to no avail.
HELP!!!
How can I get rid of this?
The X appears on the Replication
Monitor->Publishers->servername->Publication, but there is no problem
with the Snapshot, log Reader or the Distribution Agent.
The X appears on the Replication Monitor->Agents->Distribution Agents
folder, but there is no errors with any of the distribution agents.
My SQL servers get restarted nightly, but the servers are not
restarted. Manually restarting the SQL server do not fix this either.
Any help will be appreciated.
Larry...
Try restarting the sql server service if all else fails. Usually it results
from an out-of-date error in tempdb (assuming it is not a genuine error
.
Rgds,
Paul Ibison
<lreames@.gmail.com> wrote in message
news:1109791850.594371.167360@.l41g2000cwc.googlegr oups.com...
> How...Why...
> Running SQL 2000 SP3 & Win 2000 Adv Srv.
> I cannot get rid of the Red X that appears in my replication monitor.
> I have run 'sp_MSload_replication_status' to no avail.
> HELP!!!
> How can I get rid of this?
>
> The X appears on the Replication
> Monitor->Publishers->servername->Publication, but there is no problem
> with the Snapshot, log Reader or the Distribution Agent.
> The X appears on the Replication Monitor->Agents->Distribution Agents
> folder, but there is no errors with any of the distribution agents.
> My SQL servers get restarted nightly, but the servers are not
> restarted. Manually restarting the SQL server do not fix this either.
> Any help will be appreciated.
> Larry...
>
|||have you enabled show anonymous subscriptions? Right click on Replication
Monitor to do this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<lreames@.gmail.com> wrote in message
news:1109791850.594371.167360@.l41g2000cwc.googlegr oups.com...
> How...Why...
> Running SQL 2000 SP3 & Win 2000 Adv Srv.
> I cannot get rid of the Red X that appears in my replication monitor.
> I have run 'sp_MSload_replication_status' to no avail.
> HELP!!!
> How can I get rid of this?
>
> The X appears on the Replication
> Monitor->Publishers->servername->Publication, but there is no problem
> with the Snapshot, log Reader or the Distribution Agent.
> The X appears on the Replication Monitor->Agents->Distribution Agents
> folder, but there is no errors with any of the distribution agents.
> My SQL servers get restarted nightly, but the servers are not
> restarted. Manually restarting the SQL server do not fix this either.
> Any help will be appreciated.
> Larry...
>
Running SQL 2000 SP3 & Win 2000 Adv Srv.
I cannot get rid of the Red X that appears in my replication monitor.
I have run 'sp_MSload_replication_status' to no avail.
HELP!!!
How can I get rid of this?
The X appears on the Replication
Monitor->Publishers->servername->Publication, but there is no problem
with the Snapshot, log Reader or the Distribution Agent.
The X appears on the Replication Monitor->Agents->Distribution Agents
folder, but there is no errors with any of the distribution agents.
My SQL servers get restarted nightly, but the servers are not
restarted. Manually restarting the SQL server do not fix this either.
Any help will be appreciated.
Larry...
Try restarting the sql server service if all else fails. Usually it results
from an out-of-date error in tempdb (assuming it is not a genuine error
.Rgds,
Paul Ibison
<lreames@.gmail.com> wrote in message
news:1109791850.594371.167360@.l41g2000cwc.googlegr oups.com...
> How...Why...
> Running SQL 2000 SP3 & Win 2000 Adv Srv.
> I cannot get rid of the Red X that appears in my replication monitor.
> I have run 'sp_MSload_replication_status' to no avail.
> HELP!!!
> How can I get rid of this?
>
> The X appears on the Replication
> Monitor->Publishers->servername->Publication, but there is no problem
> with the Snapshot, log Reader or the Distribution Agent.
> The X appears on the Replication Monitor->Agents->Distribution Agents
> folder, but there is no errors with any of the distribution agents.
> My SQL servers get restarted nightly, but the servers are not
> restarted. Manually restarting the SQL server do not fix this either.
> Any help will be appreciated.
> Larry...
>
|||have you enabled show anonymous subscriptions? Right click on Replication
Monitor to do this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<lreames@.gmail.com> wrote in message
news:1109791850.594371.167360@.l41g2000cwc.googlegr oups.com...
> How...Why...
> Running SQL 2000 SP3 & Win 2000 Adv Srv.
> I cannot get rid of the Red X that appears in my replication monitor.
> I have run 'sp_MSload_replication_status' to no avail.
> HELP!!!
> How can I get rid of this?
>
> The X appears on the Replication
> Monitor->Publishers->servername->Publication, but there is no problem
> with the Snapshot, log Reader or the Distribution Agent.
> The X appears on the Replication Monitor->Agents->Distribution Agents
> folder, but there is no errors with any of the distribution agents.
> My SQL servers get restarted nightly, but the servers are not
> restarted. Manually restarting the SQL server do not fix this either.
> Any help will be appreciated.
> Larry...
>
Friday, February 17, 2012
CXPACKET, NETWORKIO, PAGELATCH_EX on server
I have a server with 300Gb+ of disk space on a SAN, with a database 55Gb for data and 15Gb for tranx log. SQL Server 2000 w/SP3 is installed. Whenever I run any query (simple, complex, short, long, using indexes or not) the server current activity shows many instances of NETWORKIO, PAGELATCH_EX, CXPACKET and other wait types on several processes. In Profiler, 500,000+ events were recorded within a 60 second timeframe. Someone else is responsible for "infrastructure" so before I go with recommendations to fix my issues, I would like as much accurate info as possible about the cause. This is supposed to be a relatively low-activity server. My understanding is serious disk subsystem issues would cause all these wait types together. But why would this happen when running a "select x, y, z from table" where the table contains < 250000 records and x,y,z make up a covering index. Additionally, I ran a relatively simple query, two tables, inner join, with properly defined indexes and went home to let it run. I returned and it had been running for 18 hours - I finally ran out of patience and stopped it. Any ideas?And you just went home? 2 possibilities:
1. You've been blocked
2. Too many rows to return and the bottleneck is network and your workstation memory|||yes, i went home. :)
the query that ran for 18 hours was an update, and would have affected fewer than 300k records, which is not an issue when running on my local instance of sql.
there are no deadlocks for any of my processes; just the wait types.|||Can you post the query and the table(s) DDL?|||I've also had this kind of issue on a multiprocessor system. In my case, the update/select just took a *very* long time (but it did finish), while sysprocesses was throwing cxpackets and all that. Sometimes I could rewrite the query, in others I just had to wait.
Next to that: I found that sometimes it helps a lot to recreate the indices of a table. I found this out when it took 15 minutes to return on a simple query. Considering the query, I thought it took way too long. Showplan indicated a full table scan, it didn't use an index while I had a perfect index for that query. Hinting didn't help. So, I reindexed. After that, the query started using the index and voila... just a sec.
1. You've been blocked
2. Too many rows to return and the bottleneck is network and your workstation memory|||yes, i went home. :)
the query that ran for 18 hours was an update, and would have affected fewer than 300k records, which is not an issue when running on my local instance of sql.
there are no deadlocks for any of my processes; just the wait types.|||Can you post the query and the table(s) DDL?|||I've also had this kind of issue on a multiprocessor system. In my case, the update/select just took a *very* long time (but it did finish), while sysprocesses was throwing cxpackets and all that. Sometimes I could rewrite the query, in others I just had to wait.
Next to that: I found that sometimes it helps a lot to recreate the indices of a table. I found this out when it took 15 minutes to return on a simple query. Considering the query, I thought it took way too long. Showplan indicated a full table scan, it didn't use an index while I had a perfect index for that query. Hinting didn't help. So, I reindexed. After that, the query started using the index and voila... just a sec.
Subscribe to:
Posts (Atom)