Waite Types in SQL Server

A resource wait is Process running on SQL Server which is waiting for resources avaialability. like a process is waiting for the release of lock on specific object/record.
We can check all the waits encountered by threads that executed by querieng sys.dm_os_wait_stats DMV.
We can query sys.sysprocesses to know the current waittype of specific spid. lastwaittype coloumn provides waittype information.

Below is the list of some wait types:

Type	Description
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
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.
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_SERVICE	Occurs when the Service Broker destination list that is associated with a target service is updated or re-prioritized.
CHECKPOINT_QUEUE	Occurs while the checkpoint task is waiting for the next checkpoint request.
CLEAR_DB	Occurs during operations that change the state of a database, such as opening or closing a database.
CXPACKET	Occurs with parallel query plans when synchronizing the query processor exchange iterator, and when producing and consuming rows. If waiting is excessive and cannot be reduced by tuning the query (such as adding indexes), consider adjusting the cost threshold for parallelism or lowering the degree of parallelism.
CXROWSET_SYNC	Occurs during a parallel range scan.
DEADLOCK_ENUM_MUTEX	Occurs when the deadlock monitor and sys.dm_os_waiting_tasks try to make sure that SQL Server is not running multiple deadlock searches at the same time.
DEADLOCK_TASK_SEARCH	Large waiting time on this resource indicates that the server is executing queries on top of sys.dm_os_waiting_tasks, and these queries are blocking deadlock monitor from running deadlock search. This wait type is used by deadlock monitor only. Queries on top of sys.dm_os_waiting_tasks use DEADLOCK_ENUM_MUTEX.
DISKIO_SUSPEND	Occurs when a task is waiting to access a file when an external backup is active. This is reported for each waiting user process. A count larger than five per user process may indicate that the external backup is taking too much time to finish.
EXCHANGE	Occurs during synchronization in the query processor exchange iterator during parallel queries.
IO_AUDIT_MUTEX	Occurs during synchronization of trace event buffers.
IO_COMPLETION	Occurs while waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O completion waits appear as PAGEIOLATCH_* waits.
IO_QUEUE_LIMIT	TBD
	Applies to: SQL Server 2016 through SQL Server 2017.
IO_RETRY	Occurs when an I/O operation such as a read or a write to disk fails because of insufficient resources, and is then retried.
LATCH_DT	Occurs when waiting for a DT (destroy) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.
LATCH_EX	Occurs when waiting for an EX (exclusive) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.
LATCH_KP	Occurs when waiting for a KP (keep) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.
LATCH_NL	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
LATCH_SH	Occurs when waiting for an SH (share) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.
LATCH_UP	Occurs when waiting for an UP (update) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.
LAZYWRITER_SLEEP	Occurs when lazywriter tasks are suspended. This is a measure of the time spent by background tasks that are waiting. Do not consider this state when you are looking for user stalls.
LCK_M_BU	Occurs when a task is waiting to acquire a Bulk Update (BU) lock.
LCK_M_X	Occurs when a task is waiting to acquire an Exclusive lock.
OGBUFFER	Occurs when a task is waiting for space in the log buffer to store a log record. Consistently high values may indicate that the log devices cannot keep up with the amount of log being generated by the server.
LOGMGR	Occurs when a task is waiting for any outstanding log I/Os to finish before shutting down the log while closing the database.
LOGMGR_FLUSH	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
LOGMGR_QUEUE	Occurs while the log writer task waits for work requests.
OLEDB	Occurs when SQL Server calls the SQL Server Native Client OLE DB Provider. This wait type is not used for synchronization. Instead, it indicates the duration of calls to the OLE DB provider.
ONDEMAND_TASK_QUEUE	Occurs while a background task waits for high priority system task requests. Long wait times indicate that there have been no high priority requests to process, and should not cause concern.
PAGEIOLATCH_DT	Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Destroy mode. Long waits may indicate problems with the disk subsystem.
PAGEIOLATCH_EX	Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.
PAGEIOLATCH_KP	Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Keep mode. Long waits may indicate problems with the disk subsystem.
PAGEIOLATCH_NL	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
PAGEIOLATCH_SH	Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.
PAGEIOLATCH_UP	Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Update mode. Long waits may indicate problems with the disk subsystem.
PAGELATCH_DT	Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Destroy mode.
PAGELATCH_EX	Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Exclusive mode.
PAGELATCH_KP	Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Keep mode.
PAGELATCH_NL	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
PAGELATCH_SH	Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Shared mode.
PAGELATCH_UP	Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Update mode.
WAITFOR	Occurs as a result of a WAITFOR Transact-SQL statement. The duration of the wait is determined by the parameters to the statement. This is a user-initiated wait.
WRITELOG	Occurs while waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits.

Last run date of job history more than 90 days

declare @lastrun varchar(20)
declare @diff varchar(20)
set @lastrun=(select MIN(run_date)from msdb.dbo.sysjobhistory)
set @diff=(select DATEDIFF(DAY, @lastrun, Getdate()))
print @diff
--print @lastrun
if(@diff>0)
begin
--print @lastrun
SELECT j.[name],
case j.enabled when 0 then 'Disabled'
               when 1 then 'enabled'
      End as [enabled],
CAST(STUFF(STUFF(CAST(jh.run_date as varchar),7,0,'-'),5,0,'-') + ' ' +
STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),' ','0'),5,0,':'),3,0,':') as datetime) AS [LastRun],
CASE jh.run_status WHEN 0 THEN 'Failed'
                   WHEN 1 THEN 'Success'
                   WHEN 2 THEN 'Retry'
                   WHEN 3 THEN 'Canceled'
                   WHEN 4 THEN 'In progress'
                   END AS [Status]
into #tempjobhistory
FROM (SELECT a.job_id,MAX(a.instance_id) As [instance_id]
FROM msdb.dbo.sysjobhistory a
WHERE a.step_id = 0
GROUP BY a.job_id) b
INNER JOIN msdb.dbo.sysjobhistory jh ON jh.instance_id=b.instance_id
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = jh.job_id

select * from #tempjobhistory where DATEDIFF(DAY, [LastRun], Getdate()) >=90
drop table #tempjobhistory
end
else
print 'Table having History less than 90 days'

Hiding SQL Instance from Network

Go To configuration manager ---> Expand SQL Server Network Configuration ---> right click on protocols for MSSQLSERVER and select properties ---> Hide instance  to Yes

Insert Trigger to collect the spid information of a insert query

create table tempid(spid int,loginname varchar(50),hostname varchar(50))
create table temptrigger(spid int,loginname varchar(50),hostname varchar(50),lastbatch datetime)

/*declare @spid int
select @spid=@@spid
--print @spid
insert into temptrigger select spid,loginame,hostname,last_batch from sys.sysprocesses where spid=@spid
select * from #tempid
--drop table #tempid
*/
CREATE TRIGGER TR_INS_WhatYouWant
    ON tempid
    AFTER INSERT
    AS
    declare @spid int
select @spid=@@spid
--print @spid
insert into temptrigger select spid,loginame,hostname,last_batch from sys.sysprocesses where spid=@spid
    GO

insert into tempid values(1,'abc','bbc')
select * from temptrigger

CHECKDB on Resource Database

As we know resource database is hidden database, we can't access it directly from SSMS
One might be thinking how we can check the consistency errors on resource database.

If we cleanly observe, when we run DBCC CHECKDB on MASTER database we will get below


CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'.

CHECKDB found 0 allocation errors and 0 consistency errors in database 'mssqlsystemresource'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC CHECKDB on master database also runs checkdb on resource database also.

The target principal name is incorrect. cannot generate sspi context

In this Article we will see how missing a small point can make uncomfortable.

One day Morning after coming to office I was trying to connect one of the SQL Server, but it is not allowing me to connect and below error popped up. I have the SA privileges on that instance. Not sure why it is not allowing me to connect. Previous day it worked fine.

I have asked other team mate to check whether he also facing the same issue. He successfully connected to the instance.
I searched about this error in google and found some forums/blogs are mentioning to flush dns. But if it is due to DNS SQL instance should not connect to others also.

I was searching for all the possible resolutions without success.

As a trail I have logged of from DB server and then reconnected to it. Surprisingly I am able to connect to the SQL Server without any issues.
Now I was thinking what went wrong and how logging off from the server resolved the issue.

Actually here I was missing one small point.

In our environment we need to connect servers through a terminal server.

Previous day after completing my work, instead of logging off or disconnecting from the DB server I just directly disconnected the terminal server.

On the next day when I connected to terminal server, password change pop up came and I changed the password.

After logging to the terminal server previous day's DB server connection was not closed. So it did not asked for the credentials in turn it was using old credentials. But when I try to connect SQL Server using Windows Authentication, SQL Server not accepting the credentials as the windows server credentials are old one.
Once after I logged off from the DB server and logged into it again with new credentials all the issues cleared.

This might seems silly issue but some times silly issues also eat our time.  

Select serverproperty('propertyname')

SERVERPROPERTY ( 'propertyname' )
Property	Values returned
BuildClrVersion	Version of the Microsoft .NET Framework common language runtime (CLR) that was used while building the instance of SQL Server .
Collation	Name of the default collation for the server.
CollationID	ID of the SQL Server collation.
ComputerNamePhysicalNetBIOS	NetBIOS name of the local computer on which the instance of SQL Server is currently running. For a clustered instance of SQL Server on a failover cluster, this value changes as the instance of SQL Server fails over to other nodes in the failover cluster. On a stand-alone instance of SQL Server , this value remains constant and returns the same value as the MachineName property.
Edition	Installed product edition of the instance of SQL Server . Returns: 'Enterprise Edition', ‘Enterprise Edition: Core-based Licensing’ etc….
EditionID	EditionID represents the installed product edition of the instance of SQL Server . Use the value of this property to determine features and limits, such as Compute Capacity Limits by Edition of SQL Server. 1804890536 = Enterprise 1872460670 = Enterprise Edition: Core-based Licensing 610778273= Enterprise Evaluation 284895786 = Business Intelligence -2117995310 = Developer -1592396055 = Express -133711905= Express with Advanced Services -1534726760 = Standard 1293598313 = Web 1674378470 = SQL Database or SQL Data Warehouse
EngineEdition	Database Engine edition of the instance of SQL Server installed on the server. 1 = Personal or Desktop Engine (Not available in SQL Server 2005 and later versions.) 2 = Standard (This is returned for Standard, Web, and Business Intelligence.) 3 = Enterprise (This is returned for Evaluation, Developer, and both Enterprise editions.) 4 = Express (This is returned for Express, Express with Tools and Express with Advanced Services) 5 = SQL Database 6 - SQL Data Warehouse
HadrManagerStatus	Applies to: SQL Server 2012 through SQL Server 2016 . Indicates whether the Always On Availability Groups manager has started. 0 = Not started, pending communication. 1 = Started and running. 2 = Not started and failed. NULL = Input is not valid, an error, or not applicable.
InstanceDefaultDataPath	Applies to: SQL Server 2012 through current version in updates beginning in late 2015. Name of the default path to the instance data files.
InstanceDefaultLogPath	Applies to: SQL Server 2012 through current version in updates beginning in late 2015. Name of the default path to the instance log files.
InstanceName	Name of the instance to which the user is connected. Returns NULL if the instance name is the default instance, if the input is not valid, or error. NULL = Input is not valid, an error, or not applicable.
IsClustered	Server instance is configured in a failover cluster. 1 = Clustered. 0 = Not Clustered. NULL = Input is not valid, an error, or not applicable.
IsFullTextInstalled	1 = Full-text and semantic indexing components are installed. 0 = Full-text and semantic indexing components are not installed. NULL = Input is not valid, an error, or not applicable.
IsHadrEnabled	Applies to: SQL Server 2012 through SQL Server 2016 . Always On Availability Groups is enabled on this server instance. 0 = The Always On Availability Groups feature is disabled. 1 = The Always On Availability Groups feature is enabled. NULL = Input is not valid, an error, or not applicable.
IsIntegratedSecurityOnly	1 = Integrated security (Windows Authentication) 0 = Not integrated security. (Both Windows Authentication and SQL Server Authentication.)
IsLocalDB	Server is an instance of SQL Server Express LocalDB.
IsPolybaseInstalled	Returns whether the server instance has the PolyBase feature installed. 0 = PolyBase is not installed. 1 = PolyBase is installed.
IsSingleUser	Server is in single-user mode. 1 = Single user. 0 = Not single user
IsXTPSupported	Applies to: SQL Server ( SQL Server 2014 through SQL Server 2016 ), SQL Database . Server supports In-Memory OLTP. 1= Server supports In-Memory OLTP. 0= Server does not supports In-Memory OLTP.
MachineName	Windows computer name on which the server instance is running. For a clustered instance, an instance of SQL Server running on a virtual server on Microsoft Cluster Service, it returns the name of the virtual server.
ProcessID	Process ID of the SQL Server service. ProcessID is useful in identifying which Sqlservr.exe belongs to this instance.
ProductBuild	Applies to: SQL Server 2014 beginning October, 2015. The build number.
ProductBuildType	Applies to: SQL Server 2012 through current version in updates beginning in late 2015. Type of build of the current build. Returns one of the following: OD = On Demand release a specific customer. GDR = General Distribution Release released through windows update.
ProductLevel	Level of the version of the instance of SQL Server . Returns one of the following: 'RTM' = Original release version 'SPn' = Service pack version 'CTPn', = Community Technology Preview version
ProductMajorVersion	Applies to: SQL Server 2012 through current version in updates beginning in late 2015. The major version.
ProductMinorVersion	Applies to: SQL Server 2012 through current version in updates beginning in late 2015. The minor version.
ProductUpdateLevel	Applies to: SQL Server 2012 through current version in updates beginning in late 2015. Update level of the current build. CU indicates a cumulative update. Returns one of the following: CUn = Cumulative Update
ProductUpdateReference	Applies to: SQL Server 2012 through current version in updates beginning in late 2015. KB article for that release.
ProductVersion	Version of the instance of SQL Server , in the form of 'major.minor.build.revision'.
ResourceLastUpdateDateTime	Returns the date and time that the Resource database was last updated.
ResourceVersion	Returns the version Resource database.
ServerName	Both the Windows server and instance information associated with a specified instance of SQL Server .
FilestreamShareName	The name of the share used by FILESTREAM.

Difference between Suspect Mode and Recovery Pending

Whenever a database restarted it must go through the recovery process to bring it online.

If a database status is "Recovery Pending", then it mean recovery process could not start. It might be due to incorrect file path, access denied on the database file path for SQL Server services.

If a Database status is "Suspect", then it means recovery process started but it had problems in one of the recovery process stages(Analysis, Redo, Undo).

Cluster Setup Error

Recently I had to setup SQL Server 2012 cluster (Active-Passive) for my client.
We had given 2 physical boxes which are placed in 2 different DCs and storage has zonned so that storage can be used by both the boxes and fail over can happen without any issues between the two DCs.
Windows cluster build completed and handed over to me for SQL Server setup. This cluster was built with 2 cluster shared disks. I have started building SQL Server but out of 2 cluster disks only 1 disk was detected by SQL Server setup. I was with no clue what might be the problem. I checked with wintel person who built the windows cluster, but response is "Issue is with your SQL Server not from my side.". Again I started digging the problem and for its solution. After some research, I got an Idea to check the 2  cluster disk drives and properties, here I found one drive built with NTFS and other drive is built with ReiserFS file systems. The drive which built with NTFS file system detected by SQL Server setup but other drive which built with ReiserFS file system is not detected by SQL Server setup.

I have asked the wintel team to change the file system to NTFS. Once this change is done, SQL Server setup was able to detect both the disks.

After this in the next steps setup went smoothly but at the last step of setup encountered below error:

This error is due to insufficient permissions at OU level in AD and description is like below. 

The user credentials of the currently logged on user who is creating the Failover Cluster will be used to create the computer objects in Active Directory.  The user must have Create Computer Objects permissions to the OU to create the computer objects.

http://technet.microsoft.com/en-us/library/dn466519.aspx" 

For this issue I have contacted AD team to provide create object permissions at OU level in AD and AD team checked whether is there any computer object with SQL Cluster name which I have used for cluster setup, which is not found in the computer object list of AD.

After providing proper permissions I have started a fresh setup again but same error repeated. Again I was with no clue. Again contacted the respective team. Now they are able to find the SQL Cluster name in the computer objects of AD, but they are not sure about the repeated error.
I did checked the windows logs but nothing was found. So thought to check the cluster logs also.
When I checked the Cluster logs found below error:

Cluster network name resource 'SQL Network Name (Name of the SQL Cluster)' failed to create its associated computer object in domain 'Domain name' for the following reason: Resource online.

The associated error code is: -1073741790

Please work with your domain administrator to ensure that:

- The cluster identity 'Windows Cluster name' can create computer objects. By default all computer objects are created in the 'Computers' container; consult the domain administrator if this location has been changed.

- The quota for computer objects has not been reached.

- If there is an existing computer object, verify the Cluster Identity 'Windows Cluster name' has 'Full Control' permission to that computer object using the Active Directory Users and Computers tool.

This issue is because "Windows Cluster name" didn't have full access on "SQL Cluster name"  at AD Level.

Our domain guy has provided "Windows Cluster name"  full access on "SQL Cluster name" computer object at AD Level. Then I can able to bring the SQL Server resources online and failover also from fail-over cluster manager. I am happy. Cluster setup is the one task which comes rarely to most of the DBAs and everybody want to get the exposure to it. I got the chance and that too with some learning. Before also I did setup the SQL cluster on previous versions. I think we had the enough permissions at that moment so we were able to set it up without any issues. But now I think I did not provided enough privileges which made me to learn a new thing(at least new to me).

So here I had 3 issues while Building the SQL Cluster.

1. Disks are not built with NTFS file system.

2. I was not having the create objects permissions at OU Level in AD.

3.  Didn't had full control for cluster 'Windows Cluster name’ on SQL Cluster name.

After working on all these hurdles I was able to build the SQL Server cluster without any issues.

DB Suspect Mode --- Database cannot be started in this edition of SQL Server

Recently we have faced a issue with a Database.

We have got a requirement for installing SQL Server 2012 standard edition, but customer updated that they will provide the standard edition license key afterwards. So we have installed a SQL Server 2012 evaluation edition on one of the servers, then we asked application team to proceed with their application setup and they can use this evaluation edition for their database setup. Application team has setup their application and started using the DB on this instance for their application.
Meanwhile we have received the standard edition license key. So we have applied it on the evaluation edition. Once after applying the license key application DB went into suspect mode. When I have checked it in error log found below type error

"Database cannot be started in this edition of SQL Server because part or all of object is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition."

When we checked application prerequisite notes it mentioned then it required SQL Server enterprise edition and it uses data compression.

Evaluation edition supports all features of Enterprise edition so application team did not faced any issues while setting up the application. Once after applying the standard edition license key database went into suspect mode by saying current edition of SQL Serevr(here stanadard edition) did not support the data compression.

After searching for some time found one solution for this issue.

Along with this instance we had 2 more instances which were still having evaluation edition. So I have moved the database to another server which has evaluation edition using detach and attach method. Then removed the data compression feature using below command and moved this database back to original server using backup and restore, which made the database online

ALTER INDEX ALL ON <TABLE NAME> REBUILD WITH (DATA_COMPRESSION = None);