Sunday, 7 May 2017

SQL Server job fail with DBCC error

Today one of the our sql server agent job fail due to below error. The job actually capture the disk space information.

Message
Executed as user: Domain\xxxxx. ...s entered correctly. [SQLSTATE 08004] (Error 911)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528)  DBCC execution completed. If DBCC print...  The step failed.

Work Around:

1. I analyzed the above error(sql agent job history option in EM which may not display all the info (I think it truncates it to 1024 characters)), but i couldn't find any thing.
2. I verified sql error log but no luck
3. Verified window event error log no luck
4. Finally i open the job and open the steps page and in steps page click on Edit , Advanced, and specify the output file. Got the job fails this output file will show what error was encountered.

Reason:

Job 'sql server disk space' : Step 1, 'disk space capture' : Began Executing xxxx-xx-xx 03:15:00

Msg 911, Sev 16, State 1, Line 1 : Database 'xxxx_' does not exist. Make sure that the name is entered correctly. [SQLSTATE 08004]
Msg 2528, Sev 16, State 1, Line 1 : DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
Msg 2528, Sev 16, State 1, Line 1 : DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
Msg 2528, Sev 16, State 1, Line 1 : DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]

In our sql server of one of the database was created using space(ex:abc_ xyz) i.e the reason the job is failed.

Solution:

1. I verified the active connection on that database using below command there is no active connection(looks like no body using that database).

Use master
go
select a.* from master..sysprocesses a,

master..sysdatabases b where a.dbid=b.dbid and b.name='<Database_Name>'--Give respective\your database Name
go

2. re-named the database using below query.

use master
go
exec sp_renamedb 'oldname', 'newname'

go

3. Re-ran the job again the job was completed successfully.

No comments:

Post a Comment

Disable CDC at table level

 How to Disable CDC( Change Data Capture) for tables Change Data Capture (cdc) property is disabled as default.  here I will Query you how t...