:::: MENU ::::

Posts Categorized / Troubleshooting

  • Jul 30 / 2009
  • 0
dbDigger, SQL Server Agent scheduled Jobs, Troubleshooting

How to start troubleshooting sql server agent failed jobs

SQL Server agent jobs or SQL Server jobs are predefined tasks performed in SQL Server at scheduled time. Any change in permissions or job related objects may cause a job to be failed. It is important that DBA should have a clear idea about how to troubleshoot when job gets failed.
Suppose we have a job TestJob. It has just one task defined in it, that is to select data from sales.salesPerson table in AdventureWorks.

To check last run status of all jobs

Suppose currently we are unaware of status of all of jobs. So first we have to get last run status of all SQL Server agent jobs. For this purpose, double click the Job Activity Monitor in SSMS or right click it to view last run status of all SQL Server scheduled jobs.

check last run status of all jobs

It will provide you a frame with information about last run status of all jobs. I have only two jobs configured so frame has status of just two jobs

last run status of all jobs

Get Details about specific failed job

Job named TestJob has been failed for some reason. Now we have to further investigate several aspects about its failure. For this purpose

  • Go to Jobs folder
  • Right click on failed job
  • Click on View History

Get Details about failed job

It will present previous history of this specific failed jobs. By further drilling any record of failure, you may found further detail and reason of job failure.

Details about failed job

Once you have details and clue about reason, you may further divide the steps execution through executing it through query pane by same user which owns the job. As in our case there is permissions problem for User1 on sales.salesPerson table. User1 owns the job. If we get log in SSMS through User1 and execute the step through query pane, we will get same error.

Get automatically informed about failed jobs

You may automatically get informed and log the job failure in windows event log by following way.

  • Right click on job and go to properties, or simply double click the job
  • Go to notifications section and there you have various intimation options to use

  • Apr 02 / 2009
  • 0
Backup and Restore, DBA Interview questions, dbDigger, System Databases, Troubleshooting

Cannot back up the log of the master database. Use BACKUP DATABASE instead.

The error message
Msg 4212, Level 16, State 1, Line 2
Cannot back up the log of the master database. Use BACKUP DATABASE instead.
is error of severity level 16 and can occur on all versions of SQL Server. It appears when you try to backup log of master database. SQL Server does not allow to backup log of master database. To avoid the error you should take full backup of master database.

  • Jan 23 / 2009
  • 0
dbDigger, Troubleshooting

Understanding Severity Levels of SQL Server Error Messages

In SQL Server a full error message for T-SQL consists of four parts normally. To outline the parts in messages consider following sample error message

Msg 207, Level 16, State 1, Line 1
Invalid column name ‘count’.

  • Msg is message number, or it may be said error number. It is unique to every error description statement. If the error occurs in a stored procedure or trigger, it returns the line number in the routine.
  • Level is severity level of error message from 0 to 25. It points towards nature of error and its severity.
  • State is integer value between 1 and 127. It points to source of error message. The values for this parameter are specific to error message and are not documented by Microsoft.
  • Line points to line number in code that generated the error. The line may not be the exact line produced the error. In that case you may examine the control flow to detect exact source of error.

In second line there is error description statement that briefly describes the produced error.
If error occurred in set of defined T-SQL statements like stored procedures, functions etc. Then name of that source may also be included in full error message.
Among the above mentioned parts of a full error message, after the error statement itself, level is important information that helps to trouble shoot the error. Some error statements makes no idea without considering the associated error severity level. So how to get error severity levels summary to use these while trouble shooting T-SQL statements?
Severity level of errors range from 0 to 25. I will summarize the description of error levels at reasonable level of abstraction.

Level 00 – 09
These messages are not errors but just informational messages produced
by SQL Server Engine.

Level 10
These are also informational messages but report errors that are not severe and does not threat the execution. For sake of compatibility reasons, the Database Engine converts severity 10 to severity 0 before returning the error information to the calling application.

Level 11
Indicates that the given object or entity does not exist.

Level 12
A special severity for queries that do not use locking because of special hints provided in query. In some cases, read operations performed by these statements could result in inconsistent data due to fact that locking mechanism is not applied.

Level 13
Indicates errors due to transaction deadlock.

Level 14
Indicates permission and security-related errors, such as permission denied.

Level 15

Indicates presence of syntax errors in the Transact-SQL command.

Level 16
Indicates general errors that can be corrected by the user.

Level 17
Produced when of SQL Server becomes short of configurable resources. Example of such resources may be SQL Server locks, memory and disk space.

Level 18
roduced due to internal software problem in database engine. Statement is executed and connection to SQL Server engine is maintained. BOL recommends that system admin should be informed in case of an error with severity level 18.

Level 19
Produced when SQL Server becomes short of non configurable resources. Execution of current batch is terminated. These errors are written in error log.

Level 20
Produced by any problem with statement issued by the current process. Over all environment of SQL Server is not threatened except the related process. Errors of this level are also written in error log.

Level 21
Produced when an error occurs that affects all the processes of that specific database in SQL Server. Mostly it is unlikely that database itself is damaged. Errors of this level is also written in error log.

Level 22
Indicates the damage of an index or table. The problem may be in cache or on disk. If problem is on cache then just restart of SQL Server will work. If problem is on disk then use maintenance and trouble shooting commands to detect and fix the problems.

Level 23
Indicates that specific database is corrupt to some extent. Use commands to fix the problems. reason of corruption may be hard ware or software related.

Level 24
Indicates that a hardware problem occurs. You have to restore the database
and hard ware vendor may also be contacted in this case.

Level 25
Indicates that some type of system level error has occurred

Understanding and using the severity level concept along with error statement produced helps allot to efficiently detect and fix the errors. To have a complete default list of SQL Server error number, severity level and error description use following commands for SQL Server 2000, SQL Server 2005 and SQL Server 2008.

 Command to get default list of SQL Server error numbers and descriptions  
 --For SQL Server 2000  
 USE Master  
 SELECT * FROM SysMessages  
 -- For SQL Server 2005, 2008  
 USE Master  
 SELECT * FROM SysMessages  
 WHERE MsgLangId = 1033  

In case of SQL Server 2000 value for ‘MsgLangID’ is not required because it returns default language only. While SQL Server 2005 returns all language so you have to provide language ID as in this case we have provided for English. To get list of all languages and their IDs in SQL Server 2005 execute following T-SQL command.

  • Dec 02 / 2008
  • 0
dbDigger, Troubleshooting

SQL Server process not killed


In SQL Server 2005 you see a process(SPID 623) that takes a lot of time and is locking other processes. So you give it a KILL 623 command, but nothing is happening. In the log you see “Process id 623 killed by Hostname hendrik” After this you do a select on the sysprocesses table “SELECT * FROM sysprocesses WHERE SPID = 623” And the waittype is 0x0800 and lastwaittype “NETWORKIO”

Why is the process not KILLED?


The server is waiting on the client to get al the results and the “kill yourself” is next in the Process Slot Structure


If the client does not fetch all result rows, the server will eventually be forced to wait when writing to the client. This is seen as a sysprocesses.waittype of 0x0800. While waiting on the network, no SQL Server code is being run that can interrogate the PSS and detect a KILL command. If the spid holds locks prior to waiting on the network I/O, it may block other processes.
  • Nov 05 / 2008
  • 0
dbDigger, Disaster Recovery, Troubleshooting

Page Verify (PAGE_VERIFY) in SQL Server 2005

You can use Page Verify option to determine the mechanism used to discover damaged database pages caused by disk I/O path errors. When set to Checksum, a checksum over the contents of the whole page is calculated and the value stored in the page header when a page is written to disk. When the page is later read from disk, the checksum is recomputed and compared to the checksum value stored in the page header. If the values do not match, an error is reported. When set to TornPageDetection, a specific bit for each 512-byte sector in the 8-kilobyte (KB) database page is saved and stored in the database page header when the page is written to disk. When the page is read from disk, the torn bits stored in the page header are compared to the actual page sector information. Unmatched values indicate that only part of the page was written to disk, a condition called a torn page. When set to None, the database will not generate or verify a checksum or torn page detection bits.

  • Oct 16 / 2008
  • 0
dbDigger, Indexes, Troubleshooting

Error generated due to schema binded view

Our web application started to generate error messages while inserting/updating through specified form. Another DBA just used index Tuning Wizard and created some indexes there. The Error statement showed in browser was.

INSERT failed because the following SET options have incorrect settings: ‘ARITHABORT’

Description: An unhandled exception occurred during the execution of current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System .Data.sqlclient.SqlException: INSERT failed because the following SET options have incorrect settings: ‘ARITHABORT’

Source Error:
An unhandled exception was generated during the execution of current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


So as we analyzed the database. A new view was found that was created by index tuning wizard. As the specified view was deleted, application started working properly without any error.

Consult us to explore the Databases. Contact us