August 16, 2018 at 12:55 pm
Hi All,
User is running SSIS package which consits multiple transactions, 4-5 connections to one single table, there are multiple such operations happening sequentially where SELECT data from source and insert into destination. sometimes, one of the operation is getting failed due to the login failure but able to login in next try and more over, all the operations are using the same connection string.
Errors captured in SQLServer Error Log:
-----------------------------------------
ERROR 1:
Login failure for the user 'XXXX'
Error:18456, Severity: 14, State: 8 – where state 8indicates that the authentication failed because the user provided an incorrectpassword/Password mismatch but i'm not suspecting this is the password mismatch issue because all are operations using the same connection string which make the connection to the db server without any login failure and one of the operation is getting failed due to the login failure but make the connection in next attempt.
ERROR 2:
Error: 2802, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
Configurations:
-----------------
WIndows Version and Edition: Windows server 2003 Enterprise Edition (X64)
Total Memory: 127 GB
SQLServer Version and Edition:SQLServer 2005 SP3 (9.00.4060.00) and Enterprise Edition (X64)
Max Memory allocated to SQLServer: 90 GB
.
Could you please help me on this.
Thanks,
Ravismigo
August 16, 2018 at 4:41 pm
ravisamigo - Thursday, August 16, 2018 12:55 PMHi All,
User is running SSIS package which consits multiple transactions, 4-5 connections to one single table, there are multiple such operations happening sequentially where SELECT data from source and insert into destination. sometimes, one of the operation is getting failed due to the login failure but able to login in next try and more over, all the operations are using the same connection string.Errors captured in SQLServer Error Log:
-----------------------------------------ERROR 1:
Login failure for the user 'XXXX'
Error:18456, Severity: 14, State: 8 – where state 8indicates that the authentication failed because the user provided an incorrectpassword/Password mismatch but i'm not suspecting this is the password mismatch issue because all are operations using the same connection string which make the connection to the db server without any login failure and one of the operation is getting failed due to the login failure but make the connection in next attempt.ERROR 2:
Error: 2802, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.Configurations:
-----------------WIndows Version and Edition: Windows server 2003 Enterprise Edition (X64)
Total Memory: 127 GBSQLServer Version and Edition:SQLServer 2005 SP3 (9.00.4060.00) and Enterprise Edition (X64)
Max Memory allocated to SQLServer: 90 GB.
Could you please help me on this.Thanks,
Ravismigo
I've only ever seen the password mismatch error be due to a password mismatch. What's interesting is it is just one operation so I'd be more inclined to think it was something with that operation. I've seen it be various things - improper case, adding a space before/after the password, code error when building strings, having more than one connection string. Probably some others but I'd take a close look at whatever operation has the issue. Could be something else but I'd look there. And make sure that error is coming from that process in that package.
The second error - for those errors where you don't end up getting the error message, query sys.messages to find the error. The message for English is
SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache)
due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.
There is a long list of things that could clear the cache in SQL Server 2005. You can find a list in this article - there is one or two that depend on the service pack level:
You may experience a decrease in query performance after you perform certain database maintenance operations or regular transaction operations in SQL Server 2005
Sue
August 16, 2018 at 5:33 pm
HI Sue,
Thanks for your reply. I'm able to connect the server with the credentials from SSMS and don't see any issue.
And also there are lot of SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete errors for the tempdb, is this the reason ?
Can we increase the max memory to 100 GB from 90 GB for the SQLServer?
In windows event viewer:
The description for Event ID ( 2802 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: 1, Bound Trees.
The description for Event ID ( 2802 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: 1, SQL Plans.
The description for Event ID ( 2802 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: 1, Object Plans.
August 17, 2018 at 12:09 pm
ravisamigo - Thursday, August 16, 2018 5:33 PMHI Sue,Thanks for your reply. I'm able to connect the server with the credentials from SSMS and don't see any issue.
And also there are lot of SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete errors for the tempdb, is this the reason ?
Can we increase the max memory to 100 GB from 90 GB for the SQLServer?
In windows event viewer:
The description for Event ID ( 2802 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: 1, Bound Trees.
The description for Event ID ( 2802 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: 1, SQL Plans.
The description for Event ID ( 2802 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: 1, Object Plans.
I don't know that any of these in either post are related. You may just have a screwed up server.
Check your databases to see if one has autoclose set to true.
Check your file stats and consider running perf mon as well to check how the disk are performing.
Sue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply