Error: Could not find an entry for table or index with partition ID xxxxxx in database & Ser

Hello! guys

I am using sql server 2005 enterprise edition (x64) with sp1

Periodically, the sql server is crashing. Before crashing i am getting these messages in the error log

1) During undoing of a logged operation in database 'msdb', an error occurred at log record ID (). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.

2) Could not find an entry for table or index with partition ID xxxxxx in database 2. This error can occur if a stored procedure references a dropped table, or metadata is corrupted. Drop and re-create the stored procedure, or execute DBCC CHECKDB.

3) Unable to drop worktable with partition ID xxxxx after repeated attempts. Worktable is marked for deferred drop. This is an informational message only. No user action is required.

4) Error occurred while attempting to drop worktable with partition ID xxxxx.

5) SQL Server must shut down in order to recover a database (database ID 1). The database is either a user database that could not be shut down or a system database. Restart SQL Server. If the database fails to recover after another startup, repair or restore the database.

6)The log for database 'msdb' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

Usually i get (2),(3),(4) (5) error message. But last time i also got (1) and (6)
Why msdb database is mentioned in error messages? Whats wrong with msdb dattabase?

Can someone tell me how to fix this issue?

I have seen this hotfix -

http://support.microsoft.com/kb/916086
( FIX: Errors may be generated in the tempdb database when you create and then drop many temporary tables in SQL Server 2005 BUG #: 518 sql )

Should i apply this hotfix? or should apply SP2? I checked SP2 bug list but i dont see anywhere about above bug. Does that mean it is not included in SP2?

Has anyone experienced such an issue? How you have resolved it?

Please any help would be apprecited.

Thanks

Database id:1 is master, :2 is tempdb. msdb database id should be 4.

It's a good thing to apply that hotfix. Also, please post the sql log. It gives us a lot more info than windows event log.

|||
Thanks so much for the reply.

All the above errors are from sql log only. I looked at the event log and it shows the same error messages.

Also that hotfix says-

This hotfix may receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next SQL Server 2005 service pack that contains this hotfix.

So is it safe to apply that hotfix? and if not does sp2 includes this hotfix bug?

Also wondering why the error message showing msdb database?

Thanks

|||

All hotfixes have that disclaimer. Basically, hotfixes are released to customers who truly need the fix before a formal release of a service pack (which they could wait for a long time for).

Sql2k5 sp2 does have this fix included. You can tell by looking at the build number (hotfix: 1533 and sp2: 3042).

The "missing log for msdb" error implies your sqlserver can't get to the log file (*.ldf) which implies a storage problem. Please check system event log for any indication of hardware failure.

sql

Error: Could not find an entry for table or index with partition ID xxxxxx in database & Ser

Hello! guys

I am using sql server 2005 enterprise edition (x64) with sp1

Periodically, the sql server is crashing. Before crashing i am getting these messages in the error log

1) During undoing of a logged operation in database 'msdb', an error occurred at log record ID (). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.

2) Could not find an entry for table or index with partition ID xxxxxx in database 2. This error can occur if a stored procedure references a dropped table, or metadata is corrupted. Drop and re-create the stored procedure, or execute DBCC CHECKDB.

3) Unable to drop worktable with partition ID xxxxx after repeated attempts. Worktable is marked for deferred drop. This is an informational message only. No user action is required.

4) Error occurred while attempting to drop worktable with partition ID xxxxx.

5) SQL Server must shut down in order to recover a database (database ID 1). The database is either a user database that could not be shut down or a system database. Restart SQL Server. If the database fails to recover after another startup, repair or restore the database.

6)The log for database 'msdb' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

Usually i get (2),(3),(4) (5) error message. But last time i also got (1) and (6)
Why msdb database is mentioned in error messages? Whats wrong with msdb dattabase?

Can someone tell me how to fix this issue?

I have seen this hotfix -

http://support.microsoft.com/kb/916086
( FIX: Errors may be generated in the tempdb database when you create and then drop many temporary tables in SQL Server 2005 BUG #: 518 sql )

Should i apply this hotfix? or should apply SP2? I checked SP2 bug list but i dont see anywhere about above bug. Does that mean it is not included in SP2?

Has anyone experienced such an issue? How you have resolved it?

Please any help would be apprecited.

Thanks

Database id:1 is master, :2 is tempdb. msdb database id should be 4.

It's a good thing to apply that hotfix. Also, please post the sql log. It gives us a lot more info than windows event log.

|||
Thanks so much for the reply.

All the above errors are from sql log only. I looked at the event log and it shows the same error messages.

Also that hotfix says-

This hotfix may receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next SQL Server 2005 service pack that contains this hotfix.

So is it safe to apply that hotfix? and if not does sp2 includes this hotfix bug?

Also wondering why the error message showing msdb database?

Thanks

|||

All hotfixes have that disclaimer. Basically, hotfixes are released to customers who truly need the fix before a formal release of a service pack (which they could wait for a long time for).

Sql2k5 sp2 does have this fix included. You can tell by looking at the build number (hotfix: 1533 and sp2: 3042).

The "missing log for msdb" error implies your sqlserver can't get to the log file (*.ldf) which implies a storage problem. Please check system event log for any indication of hardware failure.

Error: 18456, Severity: 14, State: 12.

I am running SQL Server 2005 x64 SP2 and have problem connecting to database with the SQL logins I created. I test the database login using Microsoft ODBC Administrator to connect the the database locally, only 'sa' can login. All other sql logins I created fails and returns this message:

Microsoft SQL Server Login

Connection failed:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'doteasylogin'.

OK

In the SQL Logs, it shows: Error: 18456, Severity: 14, State: 12.

The SQL Services is running as 'Local System'.

Can anyone help please?

Did you create the user at the server level and give him access to the server (and to the database) ? The state 12 indicates that there is an access problem which is normally dedicated to the fact that the user is either not allowed to login or does not have the appropiate permissions for the database.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I created the user 'testlogin' in SQL Server Login and grant the 'testlogin' user db_datareader & db_datawriter permission in Database Role Membership.

I'm using Windows Server 2003 x64 standard edition.

|||

Try executing the following two queries and check that each of them returns a row:

select name, principal_id from sys.server_principals where name = 'testlogin'

select permission_name, state_desc from sys.server_permissions where grantee_principal_id = suser_id('testlogin')

The first query should return the 'testlogin' login name and its associated id.

The second query should display a row with the CONNECT_SQL, GRANT value.

Thanks

Laurentiu

|||

Hello.

I have the same problem.

I am a beginner in SQL Server.I tried to install SQLServerExpress2005sp2

I've migrated a database from Access.

I tried to make a new user: for example 'testlogin' .

I've do all you sugest (server permissions, database permissions, ..)

I've run the queries from above and everything seems ok, but I cannot connect to database using the 'testlogin' account.

Error: 18456, Severity: 14, State: 12.

Login failed for user 'testlogin'. [CLIENT: xx.xx.xx.xxx]

IMPORTANT: if I grant sysadmin role to 'testlogin' I can connect.

thanks a lot in advance.

|||I started receiving this error after attempting to set up database mirroring. After running through the configuration including setting up endpoints (unsuccessfully) the mirror server has stopped accepting logins from SQL accounts (except SA). The principal server still accepts SQL logins without failure. I have stopped mirroring and deleted the database on both servers. Thank you for any help you can give. This was not an issue before I attempted database mirroring.

Okay after looking at it further it turned out that in my instance there was a new object for mirroring created. This object was accessible by going to the properties of the user account, choosing securables on the left side of the window. Then click on the add button and choose "all objects of the types" click OK; check Endpoints and click OK. In my case I saw the "Mirroring" option under Securables. That seems to be the culprit, after selecting Grant for the Connect permission I was able to connect fine. As a temporary solution I have chosen to follow the steps mentioned before using the "Public" server role, that way all accounts will have connect access. This is just temporary until I learn how to Drop the pesky Mirroring object from the server altogether.
|||

I am having the same issue, create a login using TSQL and granting access to a user database and it will not connect. I have tried the queries mentioned and from sys.server_permissions is saying CONNECT SQL with the GRANT option.

As soon as I added the user to the sysadmin server role, no problems connecting.

Thank you.

Error: 18456, Severity: 14, State: 12.

I am running SQL Server 2005 x64 SP2 and have problem connecting to database with the SQL logins I created. I test the database login using Microsoft ODBC Administrator to connect the the database locally, only 'sa' can login. All other sql logins I created fails and returns this message:

Microsoft SQL Server Login

Connection failed:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'doteasylogin'.

OK

In the SQL Logs, it shows: Error: 18456, Severity: 14, State: 12.

The SQL Services is running as 'Local System'.

Can anyone help please?

Did you create the user at the server level and give him access to the server (and to the database) ? The state 12 indicates that there is an access problem which is normally dedicated to the fact that the user is either not allowed to login or does not have the appropiate permissions for the database.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I created the user 'testlogin' in SQL Server Login and grant the 'testlogin' user db_datareader & db_datawriter permission in Database Role Membership.

I'm using Windows Server 2003 x64 standard edition.

|||

Try executing the following two queries and check that each of them returns a row:

select name, principal_id from sys.server_principals where name = 'testlogin'

select permission_name, state_desc from sys.server_permissions where grantee_principal_id = suser_id('testlogin')

The first query should return the 'testlogin' login name and its associated id.

The second query should display a row with the CONNECT_SQL, GRANT value.

Thanks

Laurentiu

|||

Hello.

I have the same problem.

I am a beginner in SQL Server.I tried to install SQLServerExpress2005sp2

I've migrated a database from Access.

I tried to make a new user: for example 'testlogin' .

I've do all you sugest (server permissions, database permissions, ..)

I've run the queries from above and everything seems ok, but I cannot connect to database using the 'testlogin' account.

Error: 18456, Severity: 14, State: 12.

Login failed for user 'testlogin'. [CLIENT: xx.xx.xx.xxx]

IMPORTANT: if I grant sysadmin role to 'testlogin' I can connect.

thanks a lot in advance.

|||I started receiving this error after attempting to set up database mirroring. After running through the configuration including setting up endpoints (unsuccessfully) the mirror server has stopped accepting logins from SQL accounts (except SA). The principal server still accepts SQL logins without failure. I have stopped mirroring and deleted the database on both servers. Thank you for any help you can give. This was not an issue before I attempted database mirroring.

Okay after looking at it further it turned out that in my instance there was a new object for mirroring created. This object was accessible by going to the properties of the user account, choosing securables on the left side of the window. Then click on the add button and choose "all objects of the types" click OK; check Endpoints and click OK. In my case I saw the "Mirroring" option under Securables. That seems to be the culprit, after selecting Grant for the Connect permission I was able to connect fine. As a temporary solution I have chosen to follow the steps mentioned before using the "Public" server role, that way all accounts will have connect access. This is just temporary until I learn how to Drop the pesky Mirroring object from the server altogether.
|||

I am having the same issue, create a login using TSQL and granting access to a user database and it will not connect. I have tried the queries mentioned and from sys.server_permissions is saying CONNECT SQL with the GRANT option.

As soon as I added the user to the sysadmin server role, no problems connecting.

Thank you.