I've successfully set up replication on two servers, but the third one
is giving me problems. Naturally, the first two were development test
servers and the last one is the customer's server. It's a Win2000
server, with SP4. The SQL Server version is 8.00.760 (SP3). I
upgraded MDAC last night to 2.8, so the SQL Server ODBC version is
2000.85.1022.00.
I am trying to set up transactional publications between databases.
When they initialize--before the first transaction can run--I get the
Invalid Descriptor Index error. If anyone can help, please do!
(Please reply to the group, so that all who search may benefit from
your wisdom.)
I ran distrib from the command line with OutputVerboseLevel set to 2.
The command I used was:
>distrib -Publisher SQL1S -PublisherDB B_DB_Copy -Subscriber SQL1S
>-OutputVerboseLevel 2 -DistributorLogin sa -DistributorPassword
><password_removed> -SubscriberDB RC_DB_Copy -SubscriberLogin sa
>-SubscriberPassword <password_removed> -Distributor SQL1S
>-SubscriptionType 0 -SubscriptionTableName reg_geo_cfg
>-Publication B_DB_Copy
Here's the result (sanitized so the customer won't get mad):
Microsoft SQL Server Distribution Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Connecting to Distributor 'SQL1S'
Connecting to Distributor 'SQL1S.'
[6/10/2004 10:14:50 PM]SQL1S.: exec sp_helpdistpublisher N'SQL1S'
[6/10/2004 10:14:50 PM]SQL1S.distribution: select @.@.SERVERNAME
Server: SQL1S
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[6/10/2004 10:14:50 PM]SQL1S.distribution: execute sp_server_info 18
ANSI codepage: 1
[6/10/2004 10:14:50 PM]SQL1S.distribution: select datasource, srvid
from master..sysservers where upper(srvname) = upper(N'SQL1S')
[6/10/2004 10:14:50 PM]SQL1S.distribution: {?=call
sp_MShelp_subscriber_info (N'SQL1S', N'SQL1S')}
Subscriber security mode: 0, login name: sa.
[6/10/2004 10:14:50 PM]SQL1S.distribution: select datasource, srvid
from master..sysservers where upper(srvname) = upper(N'SQL1S')
[6/10/2004 10:14:50 PM]SQL1S.distribution: {call
sp_MShelp_distribution_agentid(0, N'B_DB_Copy', N'B_DB_Copy', 0,
N'RC_DB_Copy', 0)}
Agent message code 20046. Invalid Descriptor Index
[6/10/2004 10:14:50 PM]SQL1S.distribution: {call
sp_MSadd_distribution_history(1, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, -1,
0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 3,
Transaction Seqno = 0000000000000000000000000000, Command ID = -1
Message: Replication-Replication Distribution Subsystem: agent
SQL1S-BDBCopy-B_DB_Copy-SQL1S-1 failed. Invalid Descriptor
Index[6/10/2004 10:14:50 PM]SQL1S.distribution: {call
sp_MSadd_repl_alert(3, 1, 3, 14151, ?, -1, N'SQL1S', N'B_DB_Copy',
N'SQL1S', N'RC_DB_Copy', ?)}
ErrorId = 3, SourceTypeId = 4
ErrorCode = 'S1002'
ErrorText = 'Invalid Descriptor Index'
[6/10/2004 10:14:50 PM]SQL1S.distribution: {call
sp_MSadd_repl_error(3, 0, 4, ?, N'S1002', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1002
Message: Invalid Descriptor Index
Disconnecting from Distributor History 'SQL1S'
The process finished. Use CTRL+C to close this window.
there have been reports that this problem has been solved by reapplying the
latest sp (sp3a).
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"KenRobertson" <google.nospam.roberken@.spamgourmet.com> wrote in message
news:4c25a332.0406110528.5bfd7b97@.posting.google.c om...
> I've successfully set up replication on two servers, but the third one
> is giving me problems. Naturally, the first two were development test
> servers and the last one is the customer's server. It's a Win2000
> server, with SP4. The SQL Server version is 8.00.760 (SP3). I
> upgraded MDAC last night to 2.8, so the SQL Server ODBC version is
> 2000.85.1022.00.
> I am trying to set up transactional publications between databases.
> When they initialize--before the first transaction can run--I get the
> Invalid Descriptor Index error. If anyone can help, please do!
> (Please reply to the group, so that all who search may benefit from
> your wisdom.)
> I ran distrib from the command line with OutputVerboseLevel set to 2.
> The command I used was:
> Here's the result (sanitized so the customer won't get mad):
> Microsoft SQL Server Distribution Agent 8.00.760
> Copyright (c) 2000 Microsoft Corporation
> Connecting to Distributor 'SQL1S'
> Connecting to Distributor 'SQL1S.'
> [6/10/2004 10:14:50 PM]SQL1S.: exec sp_helpdistpublisher N'SQL1S'
> [6/10/2004 10:14:50 PM]SQL1S.distribution: select @.@.SERVERNAME
> Server: SQL1S
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [6/10/2004 10:14:50 PM]SQL1S.distribution: execute sp_server_info 18
> ANSI codepage: 1
> [6/10/2004 10:14:50 PM]SQL1S.distribution: select datasource, srvid
> from master..sysservers where upper(srvname) = upper(N'SQL1S')
> [6/10/2004 10:14:50 PM]SQL1S.distribution: {?=call
> sp_MShelp_subscriber_info (N'SQL1S', N'SQL1S')}
> Subscriber security mode: 0, login name: sa.
> [6/10/2004 10:14:50 PM]SQL1S.distribution: select datasource, srvid
> from master..sysservers where upper(srvname) = upper(N'SQL1S')
> [6/10/2004 10:14:50 PM]SQL1S.distribution: {call
> sp_MShelp_distribution_agentid(0, N'B_DB_Copy', N'B_DB_Copy', 0,
> N'RC_DB_Copy', 0)}
> Agent message code 20046. Invalid Descriptor Index
> [6/10/2004 10:14:50 PM]SQL1S.distribution: {call
> sp_MSadd_distribution_history(1, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, -1,
> 0x01, 0x01)}
> Adding alert to msdb..sysreplicationalerts: ErrorId = 3,
> Transaction Seqno = 0000000000000000000000000000, Command ID = -1
> Message: Replication-Replication Distribution Subsystem: agent
> SQL1S-BDBCopy-B_DB_Copy-SQL1S-1 failed. Invalid Descriptor
> Index[6/10/2004 10:14:50 PM]SQL1S.distribution: {call
> sp_MSadd_repl_alert(3, 1, 3, 14151, ?, -1, N'SQL1S', N'B_DB_Copy',
> N'SQL1S', N'RC_DB_Copy', ?)}
> ErrorId = 3, SourceTypeId = 4
> ErrorCode = 'S1002'
> ErrorText = 'Invalid Descriptor Index'
> [6/10/2004 10:14:50 PM]SQL1S.distribution: {call
> sp_MSadd_repl_error(3, 0, 4, ?, N'S1002', ?)}
> Category:ODBC
> Source: ODBC SQL Server Driver
> Number: S1002
> Message: Invalid Descriptor Index
> Disconnecting from Distributor History 'SQL1S'
>
> The process finished. Use CTRL+C to close this window.
|||Reapplying the service pack worked. Thank you for your help!!
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:<uO#U0aWUEHA.3664@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> there have been reports that this problem has been solved by reapplying the
> latest sp (sp3a).
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "KenRobertson" <google.nospam.roberken@.spamgourmet.com> wrote in message
> news:4c25a332.0406110528.5bfd7b97@.posting.google.c om...
Showing posts with label index. Show all posts
Showing posts with label index. Show all posts
Monday, March 19, 2012
Invalid Descriptor Index on SQL 2005 sp 1
We have an app that use ODBC and works on SQL server 2000 but
when running the same app and same ODBC driver on 2005 sp 1 we get
Invalid Descriptor Index
Why?
Regards,
DavidMaybe if you show us the actual code that is causing the issue we could make
a more informed answer.
Andrew J. Kelly SQL MVP
"David@.StreamServe" <david.svennevid@.gmail.com> wrote in message
news:1147860037.905325.193680@.j55g2000cwa.googlegroups.com...
> We have an app that use ODBC and works on SQL server 2000 but
> when running the same app and same ODBC driver on 2005 sp 1 we get
> Invalid Descriptor Index
>
> Why?
> Regards,
> David
>
when running the same app and same ODBC driver on 2005 sp 1 we get
Invalid Descriptor Index
Why?
Regards,
DavidMaybe if you show us the actual code that is causing the issue we could make
a more informed answer.
Andrew J. Kelly SQL MVP
"David@.StreamServe" <david.svennevid@.gmail.com> wrote in message
news:1147860037.905325.193680@.j55g2000cwa.googlegroups.com...
> We have an app that use ODBC and works on SQL server 2000 but
> when running the same app and same ODBC driver on 2005 sp 1 we get
> Invalid Descriptor Index
>
> Why?
> Regards,
> David
>
Invalid Descriptor Index error
I'm testing db to db transactional replication on a box ( all on the same box ) and the distribution agent fails with the above error. I know it's something to do with the physical server as this test works on other servers fine. SQL2k Ent sp4 on w2k3 ent sp1. ( clustered )
Server and Agent accounts are in local admins, tried push and pull, named and anonymous. Replication also fails if I use the default snapshot location. I suspect policy restrictions ( maybe on the sql service accounts ) Any pointers would be helpful - there are no errors other than above, sadly.
can you cut/paste the entire agent error output?|||The distribution Job fails with this message " Invalid Descriptor Index. The step failed."
There are no other error messages within any of the logs. Have re-applied replication over 12 times now.
|||I am having the same issue and wondered if you had found out what was causing the problem. In my case it is a brand new publication but I have built the same one on another server without this error. Thanks
|||Do you know if the distrib.exe process was able to start at all when you start the SQL Server Agent job? (May be tricky to find out from taskmgr.exe...) If possible, can you manually run the distrib.exe executable using the command-line from msdb..sysjobsteps with -OutputVerboseLevel 2 and post the (sanitized) output here? Thanks.
-Raymond
|||Ah cool ! I looked at the job and thought "what runs this - I should get to the command line"
Microsoft SQL Server Distribution Agent 8.00.2039
Copyright (c) 2000 Microsoft Corporation
Connecting to Subscriber 'MyServer'
Connecting to Subscriber 'MyServer.ServerAdmin'
Server: MyServer
DBMS: Microsoft SQL Server
Version: 08.00.2040
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[10/9/2006 9:38:46 AM]MyServer.ServerAdmin: {?=call sp_helpsubscription_properties (N'MyServer', N'SouthWind', N'')}
Distributor security mode: 1, login name: sa, password: ********.
alternate snapshot folder: .working directory: .use ftp?: 0.
Server: MyServer
DBMS: Microsoft SQL Server
Version: 08.00.2040
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
Connecting to Distributor 'MyServer'
Connecting to Distributor 'MyServer.'
[10/9/2006 9:38:46 AM]MyServer.: exec sp_helpdistpublisher N'MyServer'
[10/9/2006 9:38:46 AM]MyServer.distribution: select @.@.SERVERNAME
Server: MyServer
DBMS: Microsoft SQL Server
Version: 08.00.2040
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[10/9/2006 9:38:46 AM]MyServer.distribution: execute sp_server_info 18
ANSI codepage: 1
[10/9/2006 9:38:46 AM]MyServer.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'MyServer')
[10/9/2006 9:38:46 AM]MyServer.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'MyServer')
[10/9/2006 9:38:46 AM]MyServer.distribution: {call sp_MShelp_distribution_agentid(0, N'SouthWind', NULL, 0, N'ServerAdmin', 1)}
Agent message code 20046. Invalid Descriptor Index
[10/9/2006 9:38:46 AM]MyServer.distribution: {call sp_MSadd_distribution_history(1, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, -1, 0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 2,
Transaction Seqno = 0000000000000000000000000000, Command ID = -1
Message: Replication-Replication Distribution Subsystem: agent MyServer-SouthWind-MyServer-1 failed. Invalid Descriptor Index[10/9/2006 9:38:46 AM]MyServer.distribution: {call sp_MSadd_repl_alert(3, 1, 2, 14151, ?, -1, N'MyServer', N'SouthWind', N'MyServer', N'ServerAdmin', ?)}
[10/9/2006 9:38:46 AM]MyServer.ServerAdmin: exec dbo.sp_MSupdatelastsyncinfo N'MyServer',N'SouthWind', N'', 1, 6, N'Invalid Descriptor Index'
Disconnecting from Subscriber 'MyServer'
Disconnecting from Distributor History 'MyServer'
Here is the results I am getting. I am sorry to say this didn't help me much. I also have someone checking a possible issue with the xprepl.dll file on the publisher - it seems to be an older file. Most of the web searches I have done mention SP3a or SP2 being needed but I have the same replication working on another server with the same SQL Server versions on publisher and subscriber so I don't think that is causing my issue. Thanks for any ideas you have.
Microsoft SQL Server Distribution Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: USALCOT-DB02-TCSC-subscriber-3
Startup Delay: 3702 (msecs)
Connecting to Distributor 'publisher'
Connecting to Distributor 'publisher.'
[10/9/2006 9:29:23 AM]publisher.: exec sp_helpdistpublisher N'publisher'
[10/9/2006 9:29:23 AM]publisher.distribution: select @.@.SERVERNAME
Server: publisher
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[10/9/2006 9:29:24 AM]publisher.distribution: execute sp_server_info 18
ANSI codepage: 1
[10/9/2006 9:29:24 AM]publisher.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'subscriber')
[10/9/2006 9:29:24 AM]publisher.distribution: {?=call sp_MShelp_subscriber_info (N'publisher', N'subscriber')}
Subscriber security mode: 0, login name: sa.
[10/9/2006 9:29:24 AM]publisher.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'publisher')
[10/9/2006 9:29:24 AM]publisher.distribution: {call sp_MShelp_distribution_agentid(0, N'TCSC', NULL, 2, N'tcsc', 0)}
Agent message code 20046. Invalid Descriptor Index
[10/9/2006 9:29:24 AM]publisher.distribution: {call sp_MSadd_distribution_history(3, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, -1, 0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 9,
Transaction Seqno = 0000000000000000000000000000, Command ID = -1
Message: Replication-Replication Distribution Subsystem: agent publisher-TCSC-subscriber-3 failed. Invalid Descriptor Index[10/9/2006 9:29:24 AM]publisher.distribution: {call sp_MSadd_repl_alert(3, 3, 9, 14151, ?, -1, N'publisher', N'TCSC', N'subscriber', N'tcsc', ?)}
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1002'
ErrorText = 'Invalid Descriptor Index'
[10/9/2006 9:29:24 AM]publisher.distribution: {call sp_MSadd_repl_error(9, 0, 4, ?, N'S1002', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1002
Message: Invalid Descriptor Index
Disconnecting from Distributor History 'publisher'
This looks like a mismatch of distribution database version (SP, QFE not applied correctly?). Can you do a distribution..sp_helptext 'sp_MSadd_distribution_history' to see if the parameters are lined up properly? If you have a working environment, you may also want to compare the text of the sp_MSadd_distribution_history proc and see if you can find any suspicious differences.
-Raymond
|||You are my hero !! doh ! how could I be so stupid as to not consider checking the distribution template database create date.
I checked against another box and it appears the templates had never been updated, copied the mdf and ldf from another box and lo it works!!!
Raymond I owe you a beer big time!! ( it's also the first time in around 2 or 3 years I've actually had a problem solved on a forum ) I will store this information as a crucial peice of information. This explains the "re-apply service pack" solution but has never explained the reasoning behind it.
Thanks again!
|||Its seems like this may also be the problem I am having. What I wondered if there is anyway to do this without rebuilding the replication? I will need to do this work on the weekend again if I have to resend the snapshot.
This is the first time I have ever added information to a forum so the fact that I got the answer so quickly is real impressive to me.
Thanks to both of you for your help!!!
|||you don't have to rebuild anything, just try reapply the last service pack/QFE that was attempted.|||in my case I couldn't apply the sp so I just moved the files. Thanks for clarifying the point Greg.
Still sort of worrying about the SP though.
Invalid Descriptor Index error
I'm testing db to db transactional replication on a box ( all on the same box ) and the distribution agent fails with the above error. I know it's something to do with the physical server as this test works on other servers fine. SQL2k Ent sp4 on w2k3 ent sp1. ( clustered )
Server and Agent accounts are in local admins, tried push and pull, named and anonymous. Replication also fails if I use the default snapshot location. I suspect policy restrictions ( maybe on the sql service accounts ) Any pointers would be helpful - there are no errors other than above, sadly.
can you cut/paste the entire agent error output?|||The distribution Job fails with this message " Invalid Descriptor Index. The step failed."
There are no other error messages within any of the logs. Have re-applied replication over 12 times now.
|||I am having the same issue and wondered if you had found out what was causing the problem. In my case it is a brand new publication but I have built the same one on another server without this error. Thanks
|||Do you know if the distrib.exe process was able to start at all when you start the SQL Server Agent job? (May be tricky to find out from taskmgr.exe...) If possible, can you manually run the distrib.exe executable using the command-line from msdb..sysjobsteps with -OutputVerboseLevel 2 and post the (sanitized) output here? Thanks.
-Raymond
|||Ah cool ! I looked at the job and thought "what runs this - I should get to the command line"
Microsoft SQL Server Distribution Agent 8.00.2039
Copyright (c) 2000 Microsoft Corporation
Connecting to Subscriber 'MyServer'
Connecting to Subscriber 'MyServer.ServerAdmin'
Server: MyServer
DBMS: Microsoft SQL Server
Version: 08.00.2040
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[10/9/2006 9:38:46 AM]MyServer.ServerAdmin: {?=call sp_helpsubscription_properties (N'MyServer', N'SouthWind', N'')}
Distributor security mode: 1, login name: sa, password: ********.
alternate snapshot folder: .working directory: .use ftp?: 0.
Server: MyServer
DBMS: Microsoft SQL Server
Version: 08.00.2040
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
Connecting to Distributor 'MyServer'
Connecting to Distributor 'MyServer.'
[10/9/2006 9:38:46 AM]MyServer.: exec sp_helpdistpublisher N'MyServer'
[10/9/2006 9:38:46 AM]MyServer.distribution: select @.@.SERVERNAME
Server: MyServer
DBMS: Microsoft SQL Server
Version: 08.00.2040
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[10/9/2006 9:38:46 AM]MyServer.distribution: execute sp_server_info 18
ANSI codepage: 1
[10/9/2006 9:38:46 AM]MyServer.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'MyServer')
[10/9/2006 9:38:46 AM]MyServer.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'MyServer')
[10/9/2006 9:38:46 AM]MyServer.distribution: {call sp_MShelp_distribution_agentid(0, N'SouthWind', NULL, 0, N'ServerAdmin', 1)}
Agent message code 20046. Invalid Descriptor Index
[10/9/2006 9:38:46 AM]MyServer.distribution: {call sp_MSadd_distribution_history(1, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, -1, 0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 2,
Transaction Seqno = 0000000000000000000000000000, Command ID = -1
Message: Replication-Replication Distribution Subsystem: agent MyServer-SouthWind-MyServer-1 failed. Invalid Descriptor Index[10/9/2006 9:38:46 AM]MyServer.distribution: {call sp_MSadd_repl_alert(3, 1, 2, 14151, ?, -1, N'MyServer', N'SouthWind', N'MyServer', N'ServerAdmin', ?)}
[10/9/2006 9:38:46 AM]MyServer.ServerAdmin: exec dbo.sp_MSupdatelastsyncinfo N'MyServer',N'SouthWind', N'', 1, 6, N'Invalid Descriptor Index'
Disconnecting from Subscriber 'MyServer'
Disconnecting from Distributor History 'MyServer'
Here is the results I am getting. I am sorry to say this didn't help me much. I also have someone checking a possible issue with the xprepl.dll file on the publisher - it seems to be an older file. Most of the web searches I have done mention SP3a or SP2 being needed but I have the same replication working on another server with the same SQL Server versions on publisher and subscriber so I don't think that is causing my issue. Thanks for any ideas you have.
Microsoft SQL Server Distribution Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: USALCOT-DB02-TCSC-subscriber-3
Startup Delay: 3702 (msecs)
Connecting to Distributor 'publisher'
Connecting to Distributor 'publisher.'
[10/9/2006 9:29:23 AM]publisher.: exec sp_helpdistpublisher N'publisher'
[10/9/2006 9:29:23 AM]publisher.distribution: select @.@.SERVERNAME
Server: publisher
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[10/9/2006 9:29:24 AM]publisher.distribution: execute sp_server_info 18
ANSI codepage: 1
[10/9/2006 9:29:24 AM]publisher.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'subscriber')
[10/9/2006 9:29:24 AM]publisher.distribution: {?=call sp_MShelp_subscriber_info (N'publisher', N'subscriber')}
Subscriber security mode: 0, login name: sa.
[10/9/2006 9:29:24 AM]publisher.distribution: select datasource, srvid from master..sysservers where upper(srvname) = upper(N'publisher')
[10/9/2006 9:29:24 AM]publisher.distribution: {call sp_MShelp_distribution_agentid(0, N'TCSC', NULL, 2, N'tcsc', 0)}
Agent message code 20046. Invalid Descriptor Index
[10/9/2006 9:29:24 AM]publisher.distribution: {call sp_MSadd_distribution_history(3, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, -1, 0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 9,
Transaction Seqno = 0000000000000000000000000000, Command ID = -1
Message: Replication-Replication Distribution Subsystem: agent publisher-TCSC-subscriber-3 failed. Invalid Descriptor Index[10/9/2006 9:29:24 AM]publisher.distribution: {call sp_MSadd_repl_alert(3, 3, 9, 14151, ?, -1, N'publisher', N'TCSC', N'subscriber', N'tcsc', ?)}
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1002'
ErrorText = 'Invalid Descriptor Index'
[10/9/2006 9:29:24 AM]publisher.distribution: {call sp_MSadd_repl_error(9, 0, 4, ?, N'S1002', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1002
Message: Invalid Descriptor Index
Disconnecting from Distributor History 'publisher'
This looks like a mismatch of distribution database version (SP, QFE not applied correctly?). Can you do a distribution..sp_helptext 'sp_MSadd_distribution_history' to see if the parameters are lined up properly? If you have a working environment, you may also want to compare the text of the sp_MSadd_distribution_history proc and see if you can find any suspicious differences.
-Raymond
|||You are my hero !! doh ! how could I be so stupid as to not consider checking the distribution template database create date.
I checked against another box and it appears the templates had never been updated, copied the mdf and ldf from another box and lo it works!!!
Raymond I owe you a beer big time!! ( it's also the first time in around 2 or 3 years I've actually had a problem solved on a forum ) I will store this information as a crucial peice of information. This explains the "re-apply service pack" solution but has never explained the reasoning behind it.
Thanks again!
|||Its seems like this may also be the problem I am having. What I wondered if there is anyway to do this without rebuilding the replication? I will need to do this work on the weekend again if I have to resend the snapshot.
This is the first time I have ever added information to a forum so the fact that I got the answer so quickly is real impressive to me.
Thanks to both of you for your help!!!
|||you don't have to rebuild anything, just try reapply the last service pack/QFE that was attempted.|||in my case I couldn't apply the sp so I just moved the files. Thanks for clarifying the point Greg.
Still sort of worrying about the SP though.
Invalid Descriptor Index
I have a Sql Server 2000 SP3 Publisher that performs merge replication with a group of MSDE 2000 SP3 subscribers.
Everything was working fine. I had to make changes to the publication and reinit the subscribers. Some subscribers are working fine. Others are getting an error. The error is
The process could not enumerate changes at the 'Subscriber'.
The process could not enumerate changes at the 'Subscriber'.
(Source: Merge Replication Provider (Agent); Error number: -2147200999)
------
Invalid Descriptor Index
(Source: XXXXXXXX(Data source); Error number: 0)
------
Anyone know what might cause this? And the solution for it?
90 % of the failure to enumerat changes errors can be cleared by restarting the agent.
In your case the invalid descriptor index is unusual and points to an ODBC error. SQL Server replication uses ODBC to communicate between servers.
There are some reports that you can clear this error by rearranging your columns returned in your results set.
It sounds like this might be a bug.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||"There are some reports that you can clear this error by rearranging your columns returned in your results set."
I can't rearrange anything since its setup from replication itself. Its wierd because some subscribers to go through fine, others are failing with the error.
|||Is this error a transient error for these subscribers? Are these subscribers at the same MDAC, sp, hot fix level for that the successful subscribers are at?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Everything was working fine. I had to make changes to the publication and reinit the subscribers. Some subscribers are working fine. Others are getting an error. The error is
The process could not enumerate changes at the 'Subscriber'.
The process could not enumerate changes at the 'Subscriber'.
(Source: Merge Replication Provider (Agent); Error number: -2147200999)
------
Invalid Descriptor Index
(Source: XXXXXXXX(Data source); Error number: 0)
------
Anyone know what might cause this? And the solution for it?
90 % of the failure to enumerat changes errors can be cleared by restarting the agent.
In your case the invalid descriptor index is unusual and points to an ODBC error. SQL Server replication uses ODBC to communicate between servers.
There are some reports that you can clear this error by rearranging your columns returned in your results set.
It sounds like this might be a bug.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||"There are some reports that you can clear this error by rearranging your columns returned in your results set."
I can't rearrange anything since its setup from replication itself. Its wierd because some subscribers to go through fine, others are failing with the error.
|||Is this error a transient error for these subscribers? Are these subscribers at the same MDAC, sp, hot fix level for that the successful subscribers are at?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Invalid Descriptor Index
Hi,
I have setup snapshot pull replication between SQL2000 (publisher) and
MSDE2000 (subscriber).
The following error "Invalid Descriptor Index" is occuring on the
subscriber. Would someone please tell me what this error means and how to
get around it?
Thanks
reapply sp3 on the subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"mm" <postto@.news.com> wrote in message
news:u8luytqlEHA.2492@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have setup snapshot pull replication between SQL2000 (publisher) and
> MSDE2000 (subscriber).
> The following error "Invalid Descriptor Index" is occuring on the
> subscriber. Would someone please tell me what this error means and how to
> get around it?
> Thanks
>
I have setup snapshot pull replication between SQL2000 (publisher) and
MSDE2000 (subscriber).
The following error "Invalid Descriptor Index" is occuring on the
subscriber. Would someone please tell me what this error means and how to
get around it?
Thanks
reapply sp3 on the subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"mm" <postto@.news.com> wrote in message
news:u8luytqlEHA.2492@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have setup snapshot pull replication between SQL2000 (publisher) and
> MSDE2000 (subscriber).
> The following error "Invalid Descriptor Index" is occuring on the
> subscriber. Would someone please tell me what this error means and how to
> get around it?
> Thanks
>
Invalid descriptor index
Good day
I have a simple transactional replication set up between two SQL2000
servers. A single table is replicated with columns of type int, bit and
varchar. The server with the subscription has had SP3 for quite some time,
however after we installed SP3 on the publishing server, we started receiving
an "Invalid descriptor index" error when trying to start the distribution
agent on the publishing server.
Does anyone have an idea why this would happen and how to fix this problem?
Thanks
I have removed the replication and set it up again, however I am still
receiving the invalid descriptor index error...?
"Pieter" wrote:
> Good day
> I have a simple transactional replication set up between two SQL2000
> servers. A single table is replicated with columns of type int, bit and
> varchar. The server with the subscription has had SP3 for quite some time,
> however after we installed SP3 on the publishing server, we started receiving
> an "Invalid descriptor index" error when trying to start the distribution
> agent on the publishing server.
> Does anyone have an idea why this would happen and how to fix this problem?
> Thanks
I have a simple transactional replication set up between two SQL2000
servers. A single table is replicated with columns of type int, bit and
varchar. The server with the subscription has had SP3 for quite some time,
however after we installed SP3 on the publishing server, we started receiving
an "Invalid descriptor index" error when trying to start the distribution
agent on the publishing server.
Does anyone have an idea why this would happen and how to fix this problem?
Thanks
I have removed the replication and set it up again, however I am still
receiving the invalid descriptor index error...?
"Pieter" wrote:
> Good day
> I have a simple transactional replication set up between two SQL2000
> servers. A single table is replicated with columns of type int, bit and
> varchar. The server with the subscription has had SP3 for quite some time,
> however after we installed SP3 on the publishing server, we started receiving
> an "Invalid descriptor index" error when trying to start the distribution
> agent on the publishing server.
> Does anyone have an idea why this would happen and how to fix this problem?
> Thanks
Invalid Descriptor Index
I'm testing db to db transactional replication on a box ( all on the same box
) and the distribution agent fails with the above error. I know it's
something to do with the physical server as this test works on other servers
fine. SQL2k Ent sp4 on w2k3 ent sp1. ( clustered )
Server and Agent accounts are in local admins, tried push and pull, named
and anonymous. Replication also fails if I use the default snapshot location.
I suspect policy restrictions ( maybe on the sql service accounts ) Any
pointers would be helpful - there are no errors other than above, sadly.
The distribution Job fails with this message " Invalid Descriptor Index.
The step failed."
The snapshot works fine, I can see snapshots created ( as I add articles
through tsql ) the data is produced in the designated folder ( not the
default )
When I used the default snapshot folder the snapshot failed with a
permission error - couldn't write the files ( or similar ) which with the
services in the local admins makes me think this is a policy thing.
The servers are not really on the domain and it's actually quite tricky (
like a collection of workgroups ) but that shouldn't stop local replication
working.
This is a hoary problem with no good solution I know of. Some people have
reported success by
1) remove and re-enabling replication (not an option on a clustered server)
2) applying the sp again
3) rearranging the order of columns so the text column is not the last
column in the table. This would require a recreating of the table.
Can you enable logging to determine which table it is breaking on?
http://support.microsoft.com/default...312292&sd=tech
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"colinlr" <colinlr@.discussions.microsoft.com> wrote in message
news:872587F6-346A-46F9-80D3-6A3C6660B7C0@.microsoft.com...
> I'm testing db to db transactional replication on a box ( all on the same
> box
> ) and the distribution agent fails with the above error. I know it's
> something to do with the physical server as this test works on other
> servers
> fine. SQL2k Ent sp4 on w2k3 ent sp1. ( clustered )
> Server and Agent accounts are in local admins, tried push and pull, named
> and anonymous. Replication also fails if I use the default snapshot
> location.
> I suspect policy restrictions ( maybe on the sql service accounts ) Any
> pointers would be helpful - there are no errors other than above, sadly.
> The distribution Job fails with this message " Invalid Descriptor Index.
> The step failed."
> The snapshot works fine, I can see snapshots created ( as I add articles
> through tsql ) the data is produced in the designated folder ( not the
> default )
> When I used the default snapshot folder the snapshot failed with a
> permission error - couldn't write the files ( or similar ) which with the
> services in the local admins makes me think this is a policy thing.
> The servers are not really on the domain and it's actually quite tricky (
> like a collection of workgroups ) but that shouldn't stop local
> replication
> working.
>
|||Hah - well there's a point, I'm actually replicating a function, although I
did try a table and a procedure all produced the same result.
have removed and replaced replication about twelve times with no change to
result.
will ask about having the sp re-installed but as it's hosted, not sure. I
could try for 2187 rollup I guess.
"Hilary Cotter" wrote:
> This is a hoary problem with no good solution I know of. Some people have
> reported success by
> 1) remove and re-enabling replication (not an option on a clustered server)
> 2) applying the sp again
> 3) rearranging the order of columns so the text column is not the last
> column in the table. This would require a recreating of the table.
> Can you enable logging to determine which table it is breaking on?
> http://support.microsoft.com/default...312292&sd=tech
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "colinlr" <colinlr@.discussions.microsoft.com> wrote in message
> news:872587F6-346A-46F9-80D3-6A3C6660B7C0@.microsoft.com...
>
>
|||Two considerations. 1) use snapshot replication for replicating schema only
objects - like functions, views, stored procedures. Snapshot replication is
the only replication type which picks up schema changes.
2) try to use sp_addscriptexec to deploy your function if you deployed your
snapshot through a unc. It does not work using ftp.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"colinlr" <colinlr@.discussions.microsoft.com> wrote in message
news:45033792-E26F-4936-B81B-41E4FAE9D7A3@.microsoft.com...[vbcol=seagreen]
> Hah - well there's a point, I'm actually replicating a function, although
> I
> did try a table and a procedure all produced the same result.
> have removed and replaced replication about twelve times with no change to
> result.
> will ask about having the sp re-installed but as it's hosted, not sure. I
> could try for 2187 rollup I guess.
>
> "Hilary Cotter" wrote:
|||I have to provide a scripted solution for replication, using the GUI is not
an option for a controlled environment. It all works fine on the test boxes,
and yes I'm using the snapshot to move the non table objects. It provides a
simplified solution for the client if everything is within one publication,
less chance of mistakes, and they ( or another DBA ) will have to support my
work after I've gone. There are in truth a number of routes I could take but
a consistant method of implementing changes is important.
Anyway I digress - it works except on the production cluster, if I could
extract a more useful error message or figure out how to run the distributor
command out of the agent job ?
Unless I can get a handle on the problem there is no way to go to the data
centre providers so currently we have an impasse as I figure it's the server
config but without some measure of documented proof I can't approach the data
centre.
"Hilary Cotter" wrote:
> Two considerations. 1) use snapshot replication for replicating schema only
> objects - like functions, views, stored procedures. Snapshot replication is
> the only replication type which picks up schema changes.
> 2) try to use sp_addscriptexec to deploy your function if you deployed your
> snapshot through a unc. It does not work using ftp.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "colinlr" <colinlr@.discussions.microsoft.com> wrote in message
> news:45033792-E26F-4936-B81B-41E4FAE9D7A3@.microsoft.com...
>
>
|||Use a pre or post snapshot script to deploy the schema only objects then.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"colinlr" <colinlr@.discussions.microsoft.com> wrote in message
news:E870FE9C-8794-4E27-AD96-CEDE11FD7103@.microsoft.com...[vbcol=seagreen]
>I have to provide a scripted solution for replication, using the GUI is not
> an option for a controlled environment. It all works fine on the test
> boxes,
> and yes I'm using the snapshot to move the non table objects. It provides
> a
> simplified solution for the client if everything is within one
> publication,
> less chance of mistakes, and they ( or another DBA ) will have to support
> my
> work after I've gone. There are in truth a number of routes I could take
> but
> a consistant method of implementing changes is important.
> Anyway I digress - it works except on the production cluster, if I could
> extract a more useful error message or figure out how to run the
> distributor
> command out of the agent job ?
> Unless I can get a handle on the problem there is no way to go to the data
> centre providers so currently we have an impasse as I figure it's the
> server
> config but without some measure of documented proof I can't approach the
> data
> centre.
> "Hilary Cotter" wrote:
|||It's interesting that there doesn't seem to be any logical solutions or
pointers to this error message. I searched extensively prior to posting ( on
several forums ) and I haven't seen one solution other than re-installing sp3
- which doesn't apply here. I have asked that the data centre re-patch but I
don't know when that will be.
I have to admit I rarely post problems I encounter, as, like now, I never
seem to find a resolution - it's very frustrating !!!
Such is life I guess.
"Hilary Cotter" wrote:
> Use a pre or post snapshot script to deploy the schema only objects then.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "colinlr" <colinlr@.discussions.microsoft.com> wrote in message
> news:E870FE9C-8794-4E27-AD96-CEDE11FD7103@.microsoft.com...
>
>
|||You can always open a support incident with PSS.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"colinlr" <colinlr@.discussions.microsoft.com> wrote in message
news:44A1572A-A1A8-4DC7-A449-D0418991B110@.microsoft.com...[vbcol=seagreen]
> It's interesting that there doesn't seem to be any logical solutions or
> pointers to this error message. I searched extensively prior to posting
> ( on
> several forums ) and I haven't seen one solution other than re-installing
> sp3
> - which doesn't apply here. I have asked that the data centre re-patch but
> I
> don't know when that will be.
> I have to admit I rarely post problems I encounter, as, like now, I never
> seem to find a resolution - it's very frustrating !!!
> Such is life I guess.
> "Hilary Cotter" wrote:
) and the distribution agent fails with the above error. I know it's
something to do with the physical server as this test works on other servers
fine. SQL2k Ent sp4 on w2k3 ent sp1. ( clustered )
Server and Agent accounts are in local admins, tried push and pull, named
and anonymous. Replication also fails if I use the default snapshot location.
I suspect policy restrictions ( maybe on the sql service accounts ) Any
pointers would be helpful - there are no errors other than above, sadly.
The distribution Job fails with this message " Invalid Descriptor Index.
The step failed."
The snapshot works fine, I can see snapshots created ( as I add articles
through tsql ) the data is produced in the designated folder ( not the
default )
When I used the default snapshot folder the snapshot failed with a
permission error - couldn't write the files ( or similar ) which with the
services in the local admins makes me think this is a policy thing.
The servers are not really on the domain and it's actually quite tricky (
like a collection of workgroups ) but that shouldn't stop local replication
working.
This is a hoary problem with no good solution I know of. Some people have
reported success by
1) remove and re-enabling replication (not an option on a clustered server)
2) applying the sp again
3) rearranging the order of columns so the text column is not the last
column in the table. This would require a recreating of the table.
Can you enable logging to determine which table it is breaking on?
http://support.microsoft.com/default...312292&sd=tech
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"colinlr" <colinlr@.discussions.microsoft.com> wrote in message
news:872587F6-346A-46F9-80D3-6A3C6660B7C0@.microsoft.com...
> I'm testing db to db transactional replication on a box ( all on the same
> box
> ) and the distribution agent fails with the above error. I know it's
> something to do with the physical server as this test works on other
> servers
> fine. SQL2k Ent sp4 on w2k3 ent sp1. ( clustered )
> Server and Agent accounts are in local admins, tried push and pull, named
> and anonymous. Replication also fails if I use the default snapshot
> location.
> I suspect policy restrictions ( maybe on the sql service accounts ) Any
> pointers would be helpful - there are no errors other than above, sadly.
> The distribution Job fails with this message " Invalid Descriptor Index.
> The step failed."
> The snapshot works fine, I can see snapshots created ( as I add articles
> through tsql ) the data is produced in the designated folder ( not the
> default )
> When I used the default snapshot folder the snapshot failed with a
> permission error - couldn't write the files ( or similar ) which with the
> services in the local admins makes me think this is a policy thing.
> The servers are not really on the domain and it's actually quite tricky (
> like a collection of workgroups ) but that shouldn't stop local
> replication
> working.
>
|||Hah - well there's a point, I'm actually replicating a function, although I
did try a table and a procedure all produced the same result.
have removed and replaced replication about twelve times with no change to
result.
will ask about having the sp re-installed but as it's hosted, not sure. I
could try for 2187 rollup I guess.
"Hilary Cotter" wrote:
> This is a hoary problem with no good solution I know of. Some people have
> reported success by
> 1) remove and re-enabling replication (not an option on a clustered server)
> 2) applying the sp again
> 3) rearranging the order of columns so the text column is not the last
> column in the table. This would require a recreating of the table.
> Can you enable logging to determine which table it is breaking on?
> http://support.microsoft.com/default...312292&sd=tech
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "colinlr" <colinlr@.discussions.microsoft.com> wrote in message
> news:872587F6-346A-46F9-80D3-6A3C6660B7C0@.microsoft.com...
>
>
|||Two considerations. 1) use snapshot replication for replicating schema only
objects - like functions, views, stored procedures. Snapshot replication is
the only replication type which picks up schema changes.
2) try to use sp_addscriptexec to deploy your function if you deployed your
snapshot through a unc. It does not work using ftp.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"colinlr" <colinlr@.discussions.microsoft.com> wrote in message
news:45033792-E26F-4936-B81B-41E4FAE9D7A3@.microsoft.com...[vbcol=seagreen]
> Hah - well there's a point, I'm actually replicating a function, although
> I
> did try a table and a procedure all produced the same result.
> have removed and replaced replication about twelve times with no change to
> result.
> will ask about having the sp re-installed but as it's hosted, not sure. I
> could try for 2187 rollup I guess.
>
> "Hilary Cotter" wrote:
|||I have to provide a scripted solution for replication, using the GUI is not
an option for a controlled environment. It all works fine on the test boxes,
and yes I'm using the snapshot to move the non table objects. It provides a
simplified solution for the client if everything is within one publication,
less chance of mistakes, and they ( or another DBA ) will have to support my
work after I've gone. There are in truth a number of routes I could take but
a consistant method of implementing changes is important.
Anyway I digress - it works except on the production cluster, if I could
extract a more useful error message or figure out how to run the distributor
command out of the agent job ?
Unless I can get a handle on the problem there is no way to go to the data
centre providers so currently we have an impasse as I figure it's the server
config but without some measure of documented proof I can't approach the data
centre.
"Hilary Cotter" wrote:
> Two considerations. 1) use snapshot replication for replicating schema only
> objects - like functions, views, stored procedures. Snapshot replication is
> the only replication type which picks up schema changes.
> 2) try to use sp_addscriptexec to deploy your function if you deployed your
> snapshot through a unc. It does not work using ftp.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "colinlr" <colinlr@.discussions.microsoft.com> wrote in message
> news:45033792-E26F-4936-B81B-41E4FAE9D7A3@.microsoft.com...
>
>
|||Use a pre or post snapshot script to deploy the schema only objects then.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"colinlr" <colinlr@.discussions.microsoft.com> wrote in message
news:E870FE9C-8794-4E27-AD96-CEDE11FD7103@.microsoft.com...[vbcol=seagreen]
>I have to provide a scripted solution for replication, using the GUI is not
> an option for a controlled environment. It all works fine on the test
> boxes,
> and yes I'm using the snapshot to move the non table objects. It provides
> a
> simplified solution for the client if everything is within one
> publication,
> less chance of mistakes, and they ( or another DBA ) will have to support
> my
> work after I've gone. There are in truth a number of routes I could take
> but
> a consistant method of implementing changes is important.
> Anyway I digress - it works except on the production cluster, if I could
> extract a more useful error message or figure out how to run the
> distributor
> command out of the agent job ?
> Unless I can get a handle on the problem there is no way to go to the data
> centre providers so currently we have an impasse as I figure it's the
> server
> config but without some measure of documented proof I can't approach the
> data
> centre.
> "Hilary Cotter" wrote:
|||It's interesting that there doesn't seem to be any logical solutions or
pointers to this error message. I searched extensively prior to posting ( on
several forums ) and I haven't seen one solution other than re-installing sp3
- which doesn't apply here. I have asked that the data centre re-patch but I
don't know when that will be.
I have to admit I rarely post problems I encounter, as, like now, I never
seem to find a resolution - it's very frustrating !!!
Such is life I guess.
"Hilary Cotter" wrote:
> Use a pre or post snapshot script to deploy the schema only objects then.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "colinlr" <colinlr@.discussions.microsoft.com> wrote in message
> news:E870FE9C-8794-4E27-AD96-CEDE11FD7103@.microsoft.com...
>
>
|||You can always open a support incident with PSS.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"colinlr" <colinlr@.discussions.microsoft.com> wrote in message
news:44A1572A-A1A8-4DC7-A449-D0418991B110@.microsoft.com...[vbcol=seagreen]
> It's interesting that there doesn't seem to be any logical solutions or
> pointers to this error message. I searched extensively prior to posting
> ( on
> several forums ) and I haven't seen one solution other than re-installing
> sp3
> - which doesn't apply here. I have asked that the data centre re-patch but
> I
> don't know when that will be.
> I have to admit I rarely post problems I encounter, as, like now, I never
> seem to find a resolution - it's very frustrating !!!
> Such is life I guess.
> "Hilary Cotter" wrote:
Invalid descriptor Index
Hilary Cotter mentioned to someone to reapply SP3A to fix the problem.
I have a really stupid question. Which device are you reapplying
SP3A? (The distributor, publisher, and or clients?)
Thanks,
Amy M
subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Amy M" <amarshall@.rhtc.net> wrote in message
news:119d3885.0410110449.5b71bed@.posting.google.co m...
> Hilary Cotter mentioned to someone to reapply SP3A to fix the problem.
> I have a really stupid question. Which device are you reapplying
> SP3A? (The distributor, publisher, and or clients?)
> Thanks,
> Amy M
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:<O3FbtBFsEHA.1272@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> subscriber.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Amy M" <amarshall@.rhtc.net> wrote in message
> news:119d3885.0410110449.5b71bed@.posting.google.co m...
Hilary,
I've reapplied to the subscriber, with no luck. The only way to clear
it out is to reinitialize the subscription. That works for some users
who don't input a lot of data, but for users who do input large
amounts of data, their data would be lost.
Any more suggestions? (The subscriber OS is WinXP, sp2)
I have a really stupid question. Which device are you reapplying
SP3A? (The distributor, publisher, and or clients?)
Thanks,
Amy M
subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Amy M" <amarshall@.rhtc.net> wrote in message
news:119d3885.0410110449.5b71bed@.posting.google.co m...
> Hilary Cotter mentioned to someone to reapply SP3A to fix the problem.
> I have a really stupid question. Which device are you reapplying
> SP3A? (The distributor, publisher, and or clients?)
> Thanks,
> Amy M
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:<O3FbtBFsEHA.1272@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> subscriber.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Amy M" <amarshall@.rhtc.net> wrote in message
> news:119d3885.0410110449.5b71bed@.posting.google.co m...
Hilary,
I've reapplied to the subscriber, with no luck. The only way to clear
it out is to reinitialize the subscription. That works for some users
who don't input a lot of data, but for users who do input large
amounts of data, their data would be lost.
Any more suggestions? (The subscriber OS is WinXP, sp2)
Sunday, February 19, 2012
Interpreting index statistics on SQL 2005
I ran the DBCC SHOW_STATISTICS command for all of my indexes; I was told that high density numbers are bad, low numbers good. I have some questions about my results, though; I'm not sure how to interpret them.
Of 48 indexes, 14 have a density of 0. Does this mean that the indexes are not selective enough? Does it mean they're garbage and I should toss them?
6 have a density of NULL. They are all primary keys. I suppose this just means that they're never used because these tables are rarely queried. Would this assumption be correct?
13 have a density of 1. I have no idea what this means.
The others have densities ranging from 0.01210491 to 0.5841165. I was told that the lower this number is, the more selective and thus more useful an index is. I think 0.5841165 is too high a number. Would this be correct?
Thanks in advance.First you need to ask good and bad for what. I can argue both ways equally well. Low density numbers are good for OLTP, and high density numbers are good for OLAP. Density is practically meaningless for compound keys, since they are almost always compound and the densisty only refers to the leftmost column.
Density is meant to convey one specific property of a table. I use it to get an initial feel for the table, but it doesn't mean much in the "big picture" to me... I really need more information than what the Density can convey by itself.
-PatP|||First you need to ask good and bad for what. I can argue both ways equally well. Low density numbers are good for OLTP, and high density numbers are good for OLAP. Density is practically meaningless for compound keys, since they are almost always compound and the densisty only refers to the leftmost column.
Density is meant to convey one specific property of a table. I use it to get an initial feel for the table, but it doesn't mean much in the "big picture" to me... I really need more information than what the Density can convey by itself.
-PatP
Ummm kaaaaaaay. Didn't understand hardly a word of that.
Basically I have a bunch of giant indexes chewing up space on my DB...one table's indexes take up 3 times as much space as the table's data does. Another tables indexes take up 20% more space than the table's data. I want to get rid of indexes that aren't being used or that are garbage; I thought I could use statistics to help determine that.
I know next to nothing about indexing.|||Ah, now we're getting somewhere!
First of all, I need to correct one typing error, I meant "natural keys, since they..." in the second paragraph above, I just didn't proofread it before I sent it.
Just FYI, the density has very little to do with how useful the index is to an application. There are lots of other things that factor in much more importantly than the density.
If you don't want to take the time to understand indexing, you really shouldn't try to make decisions about it... Kind of like if you don't know a distributor from a carburator, you probably shouldn't work on your motorcycle... Just ride it and enjoy! When it needs a tune up, call a professional.
If you want to learn about indexing, there are lots of web pages that explain it at varying levels of detail. I'm not sure what your needs are, so I can't really recommend one over another, but if you snoop around ten or fifteen minutes I'm sure you can find several that suit your needs.
If you don't want to learn about indexing, you've still got several choices. You can hire someone to come in and do the job for you. This is quick, efficient, and gets you about as good a solution as you're likely to get. You can post your index script here, and maybe someone will help you online. This isn't as good or as reliable, but its cheaper and might be faster too.
-PatP|||One thing you may try is the Index Tuning Wizard. It is not a perfect tool, but it may point out what indexes are not getting used. You will need to run a trace of the activity, and making sure you get enough activity is the big problem. If some of these indexes are used for the CEO's big report at the end of the quarter, and you drop that index, there could be a few uncomfortable explanations you will have to give. In short, have all the indexes scripted out, so you can add them back later on, if need be.|||Ah, now we're getting somewhere!
First of all, I need to correct one typing error, I meant "natural keys, since they..." in the second paragraph above, I just didn't proofread it before I sent it.
Just FYI, the density has very little to do with how useful the index is to an application. There are lots of other things that factor in much more importantly than the density.
If you don't want to take the time to understand indexing, you really shouldn't try to make decisions about it... Kind of like if you don't know a distributor from a carburator, you probably shouldn't work on your motorcycle... Just ride it and enjoy! When it needs a tune up, call a professional.
If you want to learn about indexing, there are lots of web pages that explain it at varying levels of detail. I'm not sure what your needs are, so I can't really recommend one over another, but if you snoop around ten or fifteen minutes I'm sure you can find several that suit your needs.
If you don't want to learn about indexing, you've still got several choices. You can hire someone to come in and do the job for you. This is quick, efficient, and gets you about as good a solution as you're likely to get. You can post your index script here, and maybe someone will help you online. This isn't as good or as reliable, but its cheaper and might be faster too.
-PatP
*sigh*
I never said I didn't want to learn. I said that I don't know very much about it and that I didn't understand what you had said earlier, which is not the same thing.
I have read many web pages dealing with indexing. I've found them to be as useful as a bucket of pee.
I'm sorry I asked. I don't feel that I did anything to deserve that barrage of condescending comments. I certainly won't be back asking any more questions or taking up any more of your precious time.|||One thing you may try is the Index Tuning Wizard. It is not a perfect tool, but it may point out what indexes are not getting used. You will need to run a trace of the activity, and making sure you get enough activity is the big problem. If some of these indexes are used for the CEO's big report at the end of the quarter, and you drop that index, there could be a few uncomfortable explanations you will have to give. In short, have all the indexes scripted out, so you can add them back later on, if need be.
We don't have access to that here; I did have the DBAs run it on a SQL Server 2000 database a short time ago and while they didn't let me see the actual output, they said that my indexes were "fine." Then they called me the next day griping about clustered index scans. Can't hit a moving target so why try? Seems nearly every time I ask, I get insulted and shouted down.|||Well, I think you need to chill just a bit...I don't think Pat was trying to be condesending...
If the DBA's are griping about scans...that is a totally different issue...|||Well, I think you need to chill just a bit...I don't think Pat was trying to be condesending...
If the DBA's are griping about scans...that is a totally different issue...
I'm trying to chill but it seems that indexing is a sacred subject because everywhere I've asked, I've basically been told to take a hike when I ask questions about that particular subject. The books I've read suck and I haven't found a decent website yet. I'm here asking for help and I'm getting fobbed off. My DBAs don't want to help me since they're about to be outsourced; you couldn't pay them to care about what's going on at the moment. Thing is, I still have a job and my database's performance is an issue. I'm trying to fix it. That's all I'm trying to do here.|||How about posting one of the queries that seem to be giving you a problem? Not all queries can be helped by indexes. In fact, some can be outright hurt by indexes, so even the best of experts have to guess more often than you might think.|||I've been watching Pat post for several years and he is just about the most even handed poster on here. I think you will probably find that he was trying to triangulate in on your experience and knowledge level to ensure his answer was appropriate and (it appears) simply missed the target a little.
And what is wrong with the UK anyway? Or have we covered that already before...?|||How about posting one of the queries that seem to be giving you a problem? Not all queries can be helped by indexes. In fact, some can be outright hurt by indexes, so even the best of experts have to guess more often than you might think.
Well...I think some of the queries are problematic because they're pulling down a ton of rows. The queries drive reports in an application I wrote and their where clauses are dynamic. I've tried to index the columns used in the where clauses but they just never seem to get used; I just get hit with a clustered index scan unless I keep narrowing the focus of the query; then I get the index seeks that the DBAs want to see.
For example, this query takes 24 seconds to run and returns 53,914 rows.
SELECT a.Pol_no
, a.OM_Available
, a.OM_Complete
, a.Agency
, a.Channel
, a.EDI_Flag
, a.NTU_Date
, a.PLCY_NAPI
, a.Plcy_Type_Cd
, PIE.Started AS 'Policy_Issue_Started'
, PR.Started AS 'PROPNTU_Started'
, Reg.Ended AS 'Registration_Ended'
, a.Team
, a.SumAssured
, a.RunDate
, a.MissingItems
, a.Distributor
, a.ParentCompany
, a.Site
, a.CI_Type
, a.ProcessOwner
, a.Status AS 'FullStatus'
, CriticalIllness = CASE WHEN a.CI_Type IS NULL THEN 'Life Only' ELSE 'Critical Illness' END
, QuartersOld = CAST(DATEDIFF(m, a.OM_Available, a.RunDate)/3 AS Int)
, DATEDIFF(d, a.OM_Available, a.RunDate) AS 'DaysElapsed'
FROM vwFESubset a
LEFT OUTER JOIN vwPIE PIE ON a.Pol_no = PIE.Pol_no
LEFT OUTER JOIN vwPROPNTU PR ON a.Pol_no = PR.Pol_no
LEFT OUTER JOIN vwReg Reg ON a.Pol_no = Reg.Pol_no
WHERE a.Status IN('Accepted','Registered','Pending')
AND a.OM_Complete IS NULL
We never run it on its own; we just query it for other reports. I get index scans in five different tables and index seeks in three, and sometimes multiple index scans or seeks for the same table. There are some tables with less than 12 rows in them which always have clustered index scans. I've been doing this less than a year so I'm not always sure how to interpret the query plan but our DBAs really hate seeing scans of any kind and they jump all over me whenever they see them.|||And what is wrong with the UK anyway? Or have we covered that already before...?
I don't like it.|||I am going to guess that the "vw" in the from clause means views. One of the first things to try here is to go through those views, and see if the views involve tables you do not need. If so, you can rewrite the query to go to the base tables, and you will avoid all the reads and joins against those extra tables.
As a percentage, how many rows have a.Status IN('Accepted','Registered','Pending')? I am going to guess it is a sizable percentage. If so, this query may not be eligible for indexes.
Is this query run for every report? If so, is the data aggregated or filtered further up the chain? If you can get that extra filtering back down to the database level, you may stand a better chance of making your DBAs less unpleasant.|||I am going to guess that the "vw" in the from clause means views. One of the first things to try here is to go through those views, and see if the views involve tables you do not need. If so, you can rewrite the query to go to the base tables, and you will avoid all the reads and joins against those extra tables.
As a percentage, how many rows have a.Status IN('Accepted','Registered','Pending')? I am going to guess it is a sizable percentage. If so, this query may not be eligible for indexes.
Is this query run for every report? If so, is the data aggregated or filtered further up the chain? If you can get that extra filtering back down to the database level, you may stand a better chance of making your DBAs less unpleasant.
The views are all necessary, and that table is indexed. The data is filtered sometimes, and the columns it is filtered on are indexed.
The Status IN value grabs 106800 rows out of 124727 possibles. I would think that wouldn't be eligible for indexing ever but I can't make my DBAs understand. They want me to further narrow the scope of the queries, which would make the reports pretty much useless.
Of 48 indexes, 14 have a density of 0. Does this mean that the indexes are not selective enough? Does it mean they're garbage and I should toss them?
6 have a density of NULL. They are all primary keys. I suppose this just means that they're never used because these tables are rarely queried. Would this assumption be correct?
13 have a density of 1. I have no idea what this means.
The others have densities ranging from 0.01210491 to 0.5841165. I was told that the lower this number is, the more selective and thus more useful an index is. I think 0.5841165 is too high a number. Would this be correct?
Thanks in advance.First you need to ask good and bad for what. I can argue both ways equally well. Low density numbers are good for OLTP, and high density numbers are good for OLAP. Density is practically meaningless for compound keys, since they are almost always compound and the densisty only refers to the leftmost column.
Density is meant to convey one specific property of a table. I use it to get an initial feel for the table, but it doesn't mean much in the "big picture" to me... I really need more information than what the Density can convey by itself.
-PatP|||First you need to ask good and bad for what. I can argue both ways equally well. Low density numbers are good for OLTP, and high density numbers are good for OLAP. Density is practically meaningless for compound keys, since they are almost always compound and the densisty only refers to the leftmost column.
Density is meant to convey one specific property of a table. I use it to get an initial feel for the table, but it doesn't mean much in the "big picture" to me... I really need more information than what the Density can convey by itself.
-PatP
Ummm kaaaaaaay. Didn't understand hardly a word of that.
Basically I have a bunch of giant indexes chewing up space on my DB...one table's indexes take up 3 times as much space as the table's data does. Another tables indexes take up 20% more space than the table's data. I want to get rid of indexes that aren't being used or that are garbage; I thought I could use statistics to help determine that.
I know next to nothing about indexing.|||Ah, now we're getting somewhere!
First of all, I need to correct one typing error, I meant "natural keys, since they..." in the second paragraph above, I just didn't proofread it before I sent it.
Just FYI, the density has very little to do with how useful the index is to an application. There are lots of other things that factor in much more importantly than the density.
If you don't want to take the time to understand indexing, you really shouldn't try to make decisions about it... Kind of like if you don't know a distributor from a carburator, you probably shouldn't work on your motorcycle... Just ride it and enjoy! When it needs a tune up, call a professional.
If you want to learn about indexing, there are lots of web pages that explain it at varying levels of detail. I'm not sure what your needs are, so I can't really recommend one over another, but if you snoop around ten or fifteen minutes I'm sure you can find several that suit your needs.
If you don't want to learn about indexing, you've still got several choices. You can hire someone to come in and do the job for you. This is quick, efficient, and gets you about as good a solution as you're likely to get. You can post your index script here, and maybe someone will help you online. This isn't as good or as reliable, but its cheaper and might be faster too.
-PatP|||One thing you may try is the Index Tuning Wizard. It is not a perfect tool, but it may point out what indexes are not getting used. You will need to run a trace of the activity, and making sure you get enough activity is the big problem. If some of these indexes are used for the CEO's big report at the end of the quarter, and you drop that index, there could be a few uncomfortable explanations you will have to give. In short, have all the indexes scripted out, so you can add them back later on, if need be.|||Ah, now we're getting somewhere!
First of all, I need to correct one typing error, I meant "natural keys, since they..." in the second paragraph above, I just didn't proofread it before I sent it.
Just FYI, the density has very little to do with how useful the index is to an application. There are lots of other things that factor in much more importantly than the density.
If you don't want to take the time to understand indexing, you really shouldn't try to make decisions about it... Kind of like if you don't know a distributor from a carburator, you probably shouldn't work on your motorcycle... Just ride it and enjoy! When it needs a tune up, call a professional.
If you want to learn about indexing, there are lots of web pages that explain it at varying levels of detail. I'm not sure what your needs are, so I can't really recommend one over another, but if you snoop around ten or fifteen minutes I'm sure you can find several that suit your needs.
If you don't want to learn about indexing, you've still got several choices. You can hire someone to come in and do the job for you. This is quick, efficient, and gets you about as good a solution as you're likely to get. You can post your index script here, and maybe someone will help you online. This isn't as good or as reliable, but its cheaper and might be faster too.
-PatP
*sigh*
I never said I didn't want to learn. I said that I don't know very much about it and that I didn't understand what you had said earlier, which is not the same thing.
I have read many web pages dealing with indexing. I've found them to be as useful as a bucket of pee.
I'm sorry I asked. I don't feel that I did anything to deserve that barrage of condescending comments. I certainly won't be back asking any more questions or taking up any more of your precious time.|||One thing you may try is the Index Tuning Wizard. It is not a perfect tool, but it may point out what indexes are not getting used. You will need to run a trace of the activity, and making sure you get enough activity is the big problem. If some of these indexes are used for the CEO's big report at the end of the quarter, and you drop that index, there could be a few uncomfortable explanations you will have to give. In short, have all the indexes scripted out, so you can add them back later on, if need be.
We don't have access to that here; I did have the DBAs run it on a SQL Server 2000 database a short time ago and while they didn't let me see the actual output, they said that my indexes were "fine." Then they called me the next day griping about clustered index scans. Can't hit a moving target so why try? Seems nearly every time I ask, I get insulted and shouted down.|||Well, I think you need to chill just a bit...I don't think Pat was trying to be condesending...
If the DBA's are griping about scans...that is a totally different issue...|||Well, I think you need to chill just a bit...I don't think Pat was trying to be condesending...
If the DBA's are griping about scans...that is a totally different issue...
I'm trying to chill but it seems that indexing is a sacred subject because everywhere I've asked, I've basically been told to take a hike when I ask questions about that particular subject. The books I've read suck and I haven't found a decent website yet. I'm here asking for help and I'm getting fobbed off. My DBAs don't want to help me since they're about to be outsourced; you couldn't pay them to care about what's going on at the moment. Thing is, I still have a job and my database's performance is an issue. I'm trying to fix it. That's all I'm trying to do here.|||How about posting one of the queries that seem to be giving you a problem? Not all queries can be helped by indexes. In fact, some can be outright hurt by indexes, so even the best of experts have to guess more often than you might think.|||I've been watching Pat post for several years and he is just about the most even handed poster on here. I think you will probably find that he was trying to triangulate in on your experience and knowledge level to ensure his answer was appropriate and (it appears) simply missed the target a little.
And what is wrong with the UK anyway? Or have we covered that already before...?|||How about posting one of the queries that seem to be giving you a problem? Not all queries can be helped by indexes. In fact, some can be outright hurt by indexes, so even the best of experts have to guess more often than you might think.
Well...I think some of the queries are problematic because they're pulling down a ton of rows. The queries drive reports in an application I wrote and their where clauses are dynamic. I've tried to index the columns used in the where clauses but they just never seem to get used; I just get hit with a clustered index scan unless I keep narrowing the focus of the query; then I get the index seeks that the DBAs want to see.
For example, this query takes 24 seconds to run and returns 53,914 rows.
SELECT a.Pol_no
, a.OM_Available
, a.OM_Complete
, a.Agency
, a.Channel
, a.EDI_Flag
, a.NTU_Date
, a.PLCY_NAPI
, a.Plcy_Type_Cd
, PIE.Started AS 'Policy_Issue_Started'
, PR.Started AS 'PROPNTU_Started'
, Reg.Ended AS 'Registration_Ended'
, a.Team
, a.SumAssured
, a.RunDate
, a.MissingItems
, a.Distributor
, a.ParentCompany
, a.Site
, a.CI_Type
, a.ProcessOwner
, a.Status AS 'FullStatus'
, CriticalIllness = CASE WHEN a.CI_Type IS NULL THEN 'Life Only' ELSE 'Critical Illness' END
, QuartersOld = CAST(DATEDIFF(m, a.OM_Available, a.RunDate)/3 AS Int)
, DATEDIFF(d, a.OM_Available, a.RunDate) AS 'DaysElapsed'
FROM vwFESubset a
LEFT OUTER JOIN vwPIE PIE ON a.Pol_no = PIE.Pol_no
LEFT OUTER JOIN vwPROPNTU PR ON a.Pol_no = PR.Pol_no
LEFT OUTER JOIN vwReg Reg ON a.Pol_no = Reg.Pol_no
WHERE a.Status IN('Accepted','Registered','Pending')
AND a.OM_Complete IS NULL
We never run it on its own; we just query it for other reports. I get index scans in five different tables and index seeks in three, and sometimes multiple index scans or seeks for the same table. There are some tables with less than 12 rows in them which always have clustered index scans. I've been doing this less than a year so I'm not always sure how to interpret the query plan but our DBAs really hate seeing scans of any kind and they jump all over me whenever they see them.|||And what is wrong with the UK anyway? Or have we covered that already before...?
I don't like it.|||I am going to guess that the "vw" in the from clause means views. One of the first things to try here is to go through those views, and see if the views involve tables you do not need. If so, you can rewrite the query to go to the base tables, and you will avoid all the reads and joins against those extra tables.
As a percentage, how many rows have a.Status IN('Accepted','Registered','Pending')? I am going to guess it is a sizable percentage. If so, this query may not be eligible for indexes.
Is this query run for every report? If so, is the data aggregated or filtered further up the chain? If you can get that extra filtering back down to the database level, you may stand a better chance of making your DBAs less unpleasant.|||I am going to guess that the "vw" in the from clause means views. One of the first things to try here is to go through those views, and see if the views involve tables you do not need. If so, you can rewrite the query to go to the base tables, and you will avoid all the reads and joins against those extra tables.
As a percentage, how many rows have a.Status IN('Accepted','Registered','Pending')? I am going to guess it is a sizable percentage. If so, this query may not be eligible for indexes.
Is this query run for every report? If so, is the data aggregated or filtered further up the chain? If you can get that extra filtering back down to the database level, you may stand a better chance of making your DBAs less unpleasant.
The views are all necessary, and that table is indexed. The data is filtered sometimes, and the columns it is filtered on are indexed.
The Status IN value grabs 106800 rows out of 124727 possibles. I would think that wouldn't be eligible for indexing ever but I can't make my DBAs understand. They want me to further narrow the scope of the queries, which would make the reports pretty much useless.
Subscribe to:
Posts (Atom)