Information About Waits Stats in SQL Server Waits – Part 2

SQLServerF1

SQL Server internally uses worker threads to execute SQL queries sent by various applications. While threads are executing they may be either running on CPU processing the request or would be waiting on a certain resource or waiting in waiting for its chance to run on CPU. SQL Server assigns a certain wait type to the worker thread that is waiting.

If a query is taking long time to complete, we can look at the query sessions wait type to get an understanding of what kind of resource it is the query waiting for and take appropriate action to avoid the queries to wait, thus making the queries complete fast.

Below are some of the SQL Server Wait Types related to Service Broker waits.

BAD_PAGE_PROCESS
Occurs when the background suspect page logger is trying to avoid running more than every five seconds. Excessive suspect pages cause the logger to run frequently.

BROKER_CONNECTION_RECEIVE_TASK
Occurs when waiting for access to receive a message on a connection endpoint. Receive access to the endpoint is serialized.

BROKER_ENDPOINT_STATE_MUTEX
Occurs when there is contention to access the state of a Service Broker connection endpoint. Access to the state for changes is serialized.

BROKER_EVENTHANDLER
Occurs when a task is waiting in the primary event handler of the Service Broker. This should occur very briefly.

BROKER_INIT
Occurs when initializing Service Broker in each active database. This should occur infrequently.

BROKER_MASTERSTART
Occurs when a task is waiting for the primary event handler of the Service Broker to start. This should occur very briefly.

BROKER_RECEIVE_WAITFOR
Occurs when the RECEIVE WAITFOR is waiting. This is typical if no messages are ready to be received.

BROKER_REGISTERALLENDPOINTS
Occurs during the initialization of a Service Broker connection endpoint. This should occur very briefly.

BROKER_SERVICE
Occurs when the Service Broker destination list that is associated with a target service is updated or re-prioritized.

BROKER_SHUTDOWN
Occurs when there is a planned shutdown of Service Broker. This should occur very briefly, if at all.

BROKER_TASK_STOP
Occurs when the Service Broker queue task handler tries to shut down the task. The state check is serialized and must be in a running state beforehand.

BROKER_TO_FLUSH
Occurs when the Service Broker lazy flusher flushes the in-memory transmission objects to a work table.

BROKER_TRANSMITTER
Occurs when the Service Broker transmitter is waiting for work.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Information About Waits Stats in SQL Server Waits – Part 1

SQLServerF1

SQL Server internally uses worker threads to execute SQL queries sent by various applications. While threads are executing they may be either running on CPU processing the request or would be waiting on a certain resource or waiting in waiting for its chance to run on CPU. SQL Server assigns a certain wait type to the worker thread that is waiting.

If a query is taking long time to complete, we can look at the query sessions wait type to get an understanding of what kind of resource it is the query waiting for and take appropriate action to avoid the queries to wait, thus making the queries complete fast.

Below are some of the SQL Server Wait Types related to Backup, Network and SQL Server Audit related waits.

ABR
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

ASSEMBLY_LOAD
Occurs during exclusive access to assembly loading.

ASYNC_DISKPOOL_LOCK
Occurs when there is an attempt to synchronize parallel threads that are performing tasks such as creating or initializing a file.

ASYNC_IO_COMPLETION
Occurs when a task is waiting for I/Os to finish.

ASYNC_NETWORK_IO
Occurs on network writes when the task is blocked behind the network. Verify that the client is processing data from the server.

AUDIT_GROUPCACHE_LOCK
Occurs when there is a wait on a lock that controls access to a special cache. The cache contains information about which audits are being used to audit each audit action group.

AUDIT_LOGINCACHE_LOCK
Occurs when there is a wait on a lock that controls access to a special cache. The cache contains information about which audits are being used to audit login audit action groups.

AUDIT_ON_DEMAND_TARGET_LOCK
Occurs when there is a wait on a lock that is used to ensure single initialization of audit related Extended Event targets.

AUDIT_XE_SESSION_MGR
Occurs when there is a wait on a lock that is used to synchronize the starting and stopping of audit related Extended Events sessions.

BACKUP
Occurs when a task is blocked as part of backup processing.

BACKUP_OPERATOR
Occurs when a task is waiting for a tape mount. To view the tape status, query sys.dm_io_backup_tapes. If a mount operation is not pending, this wait type may indicate a hardware problem with the tape drive.

BACKUPBUFFER
Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount.

BACKUPIO
Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount.

BACKUPTHREAD
Occurs when a task is waiting for a backup task to finish. Wait times may be long, from several minutes to several hours. If the task that is being waited on is in an I/O process, this type does not indicate a problem.

PARALLEL_BACKUP_QUEUE
Occurs when serializing output produced by RESTORE HEADERONLY, RESTORE FILELISTONLY, or RESTORE LABELONLY.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server Errors or Failures Error: 3703 to Error: 3733

SQLServerF1

 

Error: 3703, Severity: 16, Cannot detach the %S_MSG ‘%.*ls’ because it is currently in use.,
Error: 3705, Severity: 16, Cannot use DRP %ls with ‘%.*ls’ because ‘%.*ls’ is a %S_MSG. Use %ls.,
Error: 3706, Severity: 16, Cannot %S_MSG a database snapshot.,
Error: 3707, Severity: 16, Cannot detach a suspect or recovery pending database. It must be repaired or dropped.,
Error: 3708, Severity: 16, Cannot %S_MSG the %S_MSG ‘%.*ls’ because it is a system %S_MSG.,
Error: 3709, Severity: 16, Cannot %S_MSG the database while the database snapshot “%.*ls” refers to it. Drp that database first.,

Error: 3710, Severity: 16, Cannot detach an opened database when the server is in minimally configured mode.,
Error: 3716, Severity: 16, The %S_MSG ‘%.*ls’ cannot be dropped because it is bound to one or more %S_MSG.,
Error: 3717, Severity: 16, Cannot drp a default constraint by DRP DEFAULT statement. Use ALTR TABLE to drp a constraint default.,
Error: 3721, Severity: 16, Type ‘%.*ls’ cannot be renamed because it is being referenced by object ‘%.*ls’.,
Error: 3723, Severity: 16, An explicit DRP INDEX is not allowed on index ‘%.*ls’. It is being used for %ls constraint enforcement.,
Error: 3724, Severity: 16, Cannot %S_MSG the %S_MSG ‘%.*ls’ because it is being used for replication.,
Error: 3725, Severity: 16, The constraint ‘%.*ls’ is being referenced by table ‘%.*ls’, foreign key constraint ‘%.*ls’.,

Error: 3726, Severity: 16, Could not drp object ‘%.*ls’ because it is referenced by a FOREIGN KEY constraint.,
Error: 3727, Severity: 10, Could not drp constraint. See previous errors.,
Error: 3728, Severity: 16, ‘%.*ls’ is not a constraint.,
Error: 3729, Severity: 16, Cannot %ls ‘%.*ls’ because it is being referenced by object ‘%.*ls’.,
Error: 3730, Severity: 16, Cannot drp the default constraint ‘%.*ls’ while it is being used by a foreign key as SET DEFAULT referential action.,
Error: 3732, Severity: 16, Cannot drp type ‘%.*ls’ because it is being referenced by object ‘%.*ls’. There may be other objects that reference this type.,
Error: 3733, Severity: 16, Constraint ‘%.*ls’ does not belong to table ‘%.*ls’.,

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server Agent Job History with Status, StartDate, EndDate and Duration

SQLServerF1

As a DBA we create various jobs and schedule them, these jobs may be related to maintenance tasks like Database Backups, Rebuild Indexes, Integrity Check, Application related jobs running queries or Stored Procedures, etc. In most of the environments there may be automated monitoring tools which raises an alert or ticket when there is any job failure and DBA’s take appropriate action.

Periodically, DBA’s may be asked for some reports related to jobs like management of Developers or other teams may require list of all jobs which were running since last 24 hours(or different threshold) along with their job run status, job start date and time, job end date and time and job run duration. There may be request to send this data regularly, so manually getting these details can take lot of time especially when there are many SQL Server instances and jobs.

Below is the script which can be used to generate a quick report from SQL Server Management Studio (SSMS) with list of job names, Job Step Name, Last Job Start Date, Last Job End Date, Last Job Run Status, Last Job Run Duration, and description of the job if available.

Declare @stdt datetime, @endt datetime;
Declare @temp_jobhistory table (JobName nvarchar(100),JobStepName nvarchar(100),StartDate datetime,EndDate datetime, RunStatus int)
Declare @query1 nvarchar(1024)
Set @Query1 = 
'SELECT j.Name, jh.Step_name,
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/) AS Start_DateTime, 
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100) / (86399.9964 /* Start Date Time */)
+ ((jh.run_duration/10000 * 3600) + ((jh.run_duration%10000)/100*60) + (jh.run_duration%10000)%100
/*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS End_DateTime, run_status
from msdb..sysjobhistory jh, msdb..sysjobs j
where jh.job_id=j.job_id and 
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/) >
DATEADD(HOUR, -168, GETDATE())
ORDER BY  name asc, step_name asc,run_date desc,run_time desc'
print @query
Insert into @temp_jobhistory Exec sp_executesql @query1
select JobName,JobStepName,StartDate,EndDate,  
CASE 
      WHEN RunStatus = 0 THEN 'Failed' 
      WHEN RunStatus = 1 THEN 'success' 
      WHEN RunStatus = 2 THEN 'Retry' 
      WHEN RunStatus = 3 THEN 'Canceled' 
      WHEN RunStatus = 4 THEN 'In progress' 
   END as JobStatus, 
DATEDIFF(minute,StartDate,EndDate) as 'DurationMinutes'
from @temp_jobhistory

Output would looks like below.

JobName

JobStepName

StartDate

EndDate

JobStatus

DurationMinutes

JobDesc

_DBA_FullBackups

(Job outcome)

2014-11-01 23:25:00.003

2014-11-01 23:25:04.000

success

0

Perform Full Backup of All User Databases

_DBA_FullBackups

FullBackupStep

2014-11-01 23:25:01.003

2014-11-01 23:25:03.000

success

0

Perform Full Backup of All User Databases

_DBA_UpdateStats

(Job outcome)

2014-11-02 04:30:00.000

2014-11-02 07:43:00.000

success

193

Performs statistics update of all databases.

_DBA_UpdateStats

TestDB

2014-11-02 06:11:45.000

2014-11-02 06:28:49.000

success

17

Performs statistics update of all User databases.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Maintenance Plan Cleanup Task is Not Deleting the Old Backup Files

SQLServerF1

It is common in most environments to have maintenance plans used to perform regular maintenance tasks like Full, Differential and Log Backups, Rebuild Indexes, Statistics Update and Integrity Check.

Backups are considered one of the most important task and proper care needs to be taken while creating the maintenance plans for performing the database backups in SQL Server. There are many options available while creating the backup maintenance plan and need to choose options correctly to avoid any failures or problems in future.

Although there can be various problems related to configuring backup maintenance plans, one of the small but difficult one to identify for many DBA’s seem to that they enable the task “Maintenance Cleanup Task” to delete old backups older than 3 days or so and they later identify the old backups are not getting deleted. The backup job will run successfully and does not report any errors in this case, the only way DBA’s identify the issue mostly is when the disk drive where backups are stored gets full or has grown to large size and alerts are raised.

Coming to the solution of this problem is mostly due to incorrect values provided to the “Maintenance Cleanup Task”. In the maintenance cleanup task, verify the option “File extension” and see if the extension of the backup to delete is correct, for example if backup task is creating the backups with extension .bak, make sure “bak” is mentioned, if for transaction log backups, extension is .trn, then make sure that the option provided is “trn”. It is important to note to use “bak”(without quotes) instead of “.bak” as using .bak will not delete the old backup files. So, make sure there is no .(Dot) infront of the extension bak or trn.

This is applicable on below versions of SQL Server.

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 
1 6 7 8