November 19, 2008 at 8:41 am
Good Day,
I am attempting to help our network ops team troubleshoot an application error. I am running into a bit of a conundrum however, I've never seen this message before:
Error: 913, Severity: 16, State: 8
The "problem" system is setup like this:
Device > Application Server > Database Server
The Device caches data, sends it to Application Server, Application Server inserts into database.
What's been done to attempt a fix:
- Both servers were stopped and re-started in attempt to fix. The problem is still persisting.
- The application drive was full, this was cleaned out to allow additional space.
- SQL Server trace was performed.
I do not know if this can be chalked up to an application error or if I need to further investigate into the database.
The SQL statements before and after the error from my profile are:
-----------------------------------------------------------------------------------------
declare @P1 int set @P1=180155022 declare @P2 int set @P2=8 declare @P3 int set @P3=1 declare @P4 int set @P4=0 exec sp_cursoropen @P1 output, N'SELECT DISTINCT clock_alias FROM cmi_message_queue WHERE clock_alias IN (SELECT clock_id FROM cmi_clocks )', @P2 output, @P3 output, @P4 output select @P1, @P2, @P3, @P4
-----------------------------------------------------------------------------------------
exec sp_cursorfetch 180155022, 16, 1, 1
-----------------------------------------------------------------------------------------
exec sp_cursorclose 180155022
-----------------------------------------------------------------------------------------
Error: 913, Severity: 16, State: 8
-----------------------------------------------------------------------------------------
network protocol: TCP/IP set quoted_identifier off set implicit_transactions off set cursor_close_on_commit off set ansi_warnings off set ansi_padding off set ansi_nulls off set concat_null_yields_null off set language us_english set dateformat mdy set datefirst 7
-----------------------------------------------------------------------------------------
exec sp_server_info 18
-----------------------------------------------------------------------------------------
exec sp_server_info 18
-----------------------------------------------------------------------------------------
use CCR_Prod
-----------------------------------------------------------------------------------------
use CCR_Prod
-----------------------------------------------------------------------------------------
set textsize 65536
-----------------------------------------------------------------------------------------
set textsize 65536
-----------------------------------------------------------------------------------------
select name from sysusers where uid = user_id()
-----------------------------------------------------------------------------------------
select name from sysusers where uid = user_id()
-----------------------------------------------------------------------------------------
Thank you in advance for any assistance - if you need more info to help I am happy to provide it!
Sandy
November 19, 2008 at 9:20 am
Is there more to the error message than just this: Error: 913, Severity: 16, State: 8?
November 19, 2008 at 11:11 am
Thanks,
Unfortunately no - this is the only error message, and I had to profile it to receive it.
Thanks for the BP link as well, have never seen that one before.
We figured out the issue - the application server was running low on disk space :rolleyes:
I just didn't know if any one else had ever seen the error message regarding cursor performance. This is a vendor application so I cannot control much except to report on what I see.
Thanks again!
November 19, 2008 at 11:46 am
googled sql error 913 and the first hit came up with this:
http://www.lcard.ru/~nail/sybase/error/8662.htm
Possible causes of Error 913 are:
Accessing a stored procedure or view that refers to a table in a database that has been dropped.
Accessing a stored procedure or view that refers to a table in a database that has been dropped and re-created.
A nonclustered index on the sysdatabases table in the master database has been corrupted.
This is a serious error if the index is corrupt or recovery fails.
Action
If the 913 error is caused by accessing a stored procedure or view that refers to a table in a database that has been dropped, either re-create the database or update the procedure or view to point to the appropriate database.
Lowell
November 19, 2008 at 12:08 pm
Thanks for the link - I also saw this and did run a CheckDB to see if any errors returned, alas nothing.
There hasn't been any changes or restores - however, I wonder if the application that is opening the cursor is creating a temp table, and for some reason the temp table is getting dropped prior to the insert?
Clearing out the full disk drive where the application lives seems to have corrected the problem, but I am still curious 🙂
Thanks again!
Sandy
November 19, 2008 at 1:31 pm
I ran a dbcc checktable on sysdatabases and there was no error reported out. I checked my documentation and the compared the database ids and there is no difference from 1 month ago till now.
It appears that this error keeps occurring when the application is attempting to issue the command sp_cursorclose;1
I do not see this stored procedure in any of the databases. I think perhaps this is being generated through an API.
Thoughts?
February 9, 2009 at 11:06 am
We are running into a similar issue.
When attempting to login through a Windows Group, I receive the "Cannot open user default database. Login failed." message. Default database is master, and master seems to be ok. When I create a Windows User login, I can connect just fine.
We've tried deleting and re-creating our group, but that didn't help.
Though profiling, I was able to see the "Error: 913, Severity: 16, State: 8" exception.
Anyone have any ideas?
-checktable on sysdatabases returns ok.
-checkdb on master (and other other system databases) returns ok.
February 9, 2009 at 11:29 am
Eat (2/9/2009)
We are running into a similar issue.When attempting to login through a Windows Group, I receive the "Cannot open user default database. Login failed." message. Default database is master, and master seems to be ok. When I create a Windows User login, I can connect just fine.
We've tried deleting and re-creating our group, but that didn't help.
Though profiling, I was able to see the "Error: 913, Severity: 16, State: 8" exception.
Anyone have any ideas?
-checktable on sysdatabases returns ok.
-checkdb on master (and other other system databases) returns ok.
This sounds more like a permissions problem. Does the group have permissions to access the master database?
February 9, 2009 at 11:49 am
Hey Lynn,
Yes it does. This Windows Group login has sysadmin, and it I've also added it as a user in master.
February 10, 2009 at 1:45 pm
State 8 pops up in case of password mismatch.
MJ
February 13, 2009 at 7:06 am
Hi! Sorry I lost track of this thread.
I finally contacted the vendor about this issue and the vendor states that sometimes their application drops it's connection, and then comes the invalid reference to the cursor object. The application is programmed to handle this issue so there is no user impact.
If you create a cursor and then drop the connection, and then try to refer to the cursor to close it - undoubtably this will error out which is what happened in our case.
I'm not sure if this helps..
Sandy
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply