Blocking Alerts

Set NOCOUNT ON
if((select COUNT(spid) from sys.sysprocesses where blocked<>0)>0)
begin

DECLARE @tableHTML  NVARCHAR(MAX) ; 

SET @tableHTML = 
    N'<H1>Blocking Information</H1>' + 
    CAST ( ( SELECT spid,       ' is blocked by  ', 
                    blocked, ' on Database ', 
                    DB_NAME(dbid), ' from application server ', 
                    hostname, ' through application name ', 
                    program_name, ' and login is ', 
                    loginame 
              from master.sys.sysprocesses where blocked >0
              FOR XML PATH('tr'), TYPE
              )
              AS NVARCHAR(MAX) ) + 
    N'</table>' ;
    --select @tableHTML

exec msdb..sp_send_dbmail @profile_name= 'DBA' 
    ,  @recipients= 'xxx@xxx.com' 
    
    ,  @subject= 'Blocking Alerts '  
    ,  @body= @tableHTML,
    @body_format = 'HTML' ;
end

DB Status Check

DB Status Check:

set nocount ON
select getdate()
declare @name VARCHAR(50),@read varchar(1500),@standby varchar(1500),@dbname1 nvarchar(1500),@state nvarchar(1500),@autoclose nvarchar(1500)
--SET @name=db_name()

DECLARE db_cursor CURSOR FOR 
SELECT name FROM sys.databases --where name =isNULL(@name,name)
WHERE (is_read_only>0 OR is_in_standby>0 OR state>0 or is_auto_close_on > 0) and name=isNULL(@name,name) order by name

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  
WHILE @@FETCH_STATUS = 0  
BEGIN  
select @dbname1=name, @read=is_read_only,@standby=is_in_standby,@state=state,@autoclose=is_auto_close_on
  from sys.databases
  where name=@name
   --select @dbname1 +' '+@read+', '+ @close
   print '--- Start of Databse '+@dbname1
 if @standby>0
set @standby='--It is in stand by  '
ELSE
SET @standby=''
if @read>0
set @read='--It is read only, Run below query to bring back the database to normal:

ALTER DATABASE ['+@dbname1+'] SET  READ_WRITE
 
  '

ELSE
SET @read=''
IF @state>0
SET @state='--Database Is not online'
ELSE
set @state=''
IF @autoclose>0
SET @autoclose='--Database Is Auto Close, Run below query to set Auto Close Off:

ALTER DATABASE ['+@dbname1+'] SET AUTO_CLOSE OFF

'

ELSE
set @autoclose=''
print @read+ @standby + @state + @autoclose

FETCH NEXT FROM db_cursor INTO @name  
END  
CLOSE db_cursor
DEALLOCATE db_cursor

Databases which are not backed up for more than 24hours

Finding the databases which are not backed up for more than 24hours:

 set nocount on
create table #backupcheck (columnName varchar(500),backuptype varchar(50))
insert into #backupcheck SELECT
    CASE
        WHEN ((datediff(day,MAX(msdb.dbo.backupset.backup_finish_date),getdate()) )>1 )
                    THEN msdb.dbo.backupset.database_name + ' backup is not completed last day and last backup date is '+CONVERT(VARCHAR(100), MAX(msdb.dbo.backupset.backup_finish_date))+''
          END AS columnName,
        CASE  when  msdb.dbo.backupset.type='I'   then 'incremental backup'
        else 'full backup'
        END as backuptype
            FROM   msdb.dbo.backupmediafamily 
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE  msdb..backupset.type in ('D','I')
GROUP BY
   msdb.dbo.backupset.database_name ,msdb..backupset.type
ORDER BY 
   msdb.dbo.backupset.database_name

      select columnName+' and backup type is '+backuptype from #backupcheck where columnName is NOT NULL
      drop table #backupcheck

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);

Defination of IAM, GAM and SGAM

GAM: Global Allocation Map

Provides the information about the extents which are available for allocation.
Covers 4GB interval of Data.

SGAM: Shared Global Allocation Map


Provides the information about the mix extents which have at least 1 page available for allocation.
Covers 4GB interval of Data.

PFS:
PFS stands for Page Free Space, but the PFS page tracks much more than that. As well as GAM intervals, every database file is also split (conceptually) into PFS intervals. A PFS interval is 8088 pages, or about 64MB. A PFS page doesn’t have a bitmap – it has a byte-map, with one byte for each page in the PFS interval (not including itself).

Used to track how much free space is on pages
Covers 64MB interval of Data.

IAM: Index Allocation Map

Special internal page on data file that tracks all extent allocation for tables, indexes and partitions.
Basically, lets SQL Server know what extent belongs to what specific entity.
Covers 4GB interval of Data.

PFS,GAM and SGAM help SQL Server to determine where free space is and how much so that it can allocate it appropriately without having to scan all the pages.
IAM pages and Indexes help SQL Server fulfill queries

Findind when the statistics have updated.

select si.name as index_name, STATS_DATE(si.OBJECT_ID,index_id) as statsupdatedate, OBJECT_NAME(si.object_id) as tablename,sch.name as scheemaname
from sys.indexes si, sys.schemas sch,sys.tables st where si.object_id=st.object_id and st.schema_id=sch.schema_id
update statistics owner.objectname

How to remove a specific execution plan from the SQL Server query cache

Below query will provide you the list of query plans available in the instance:

select plan_handle, creation_time, last_execution_time, execution_count, qt.text
FROM 
   sys.dm_exec_query_stats qs
   CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt

from the output select the appropriate plan_handle based on query text. and run below query

DBCC FREEPROCCACHE (plan_handle_id_goes_here) 

Issue with Default language of a Login

Recently we have faced one issue with converting the datetime to varchar.
When we are trying to execute the query it is throwing the error like conversion failed. out of range etc...

The database was migrated from SQL Server 2008 to 2014.

In 2008 it was executing fine without any issues. But it is not working in SQL Server 2014.

We tried to modify the query but not worked. We tried all the possible ways.

In last we have compared the properties of the application login in 2008 and 2014. In 2008 default language for that login is British English. In 2014, when migrated we have did not changed it to British English. Once we changed that to British English problem solved and every thing working as expected.  :)

So now on wards we need to check the Login default language also while migrating.

Replication Issues

https://technet.microsoft.com/en-us/library/ms152532(v=sql.105).aspx

Data Is Not Being Delivered to Subscribers

·         The data is not being applied due to filtering, an agent issue, or another replication error.

·         The data is being deleted at the Subscriber after it has been applied.

Explanation

There are a number of possible causes for data not being delivered to Subscribers:

·         The table is filtered, and there are no changes to deliver to a given Subscriber.

·         One or more agents are not running or are failing with an error.

·         A transactional subscription was initialized without a snapshot, and changes have occurred on the Publisher since the publication was created.

·         Replication of stored procedure execution for a transactional publication produces different results at the Subscriber.

·         The INSERT stored procedure used by a transactional article includes a condition that is not met.

·         Data is deleted by a user, a replication script, or another application.

·         Data is deleted by a trigger, or a trigger includes a ROLLBACK statement.

https://technet.microsoft.com/en-us/library/ms151272(v=sql.105).aspx

Data at the Publisher and Subscriber Do Not Match

Data at the Publisher and Subscriber is considered non-convergent (in other words the data does not match) if:

·         There are a different number of rows at the Subscriber than at the Publisher, and the publication is not filtered. If the publication is filtered, it might be expected that the number of rows differs.

·         The data in one or more rows is different in content at the Publisher and Subscriber.

Explanation

Data at the Publisher and Subscriber can be non-convergent for a number of reasons:

·         Data is updated at a Subscriber that should be treated as read-only. The subscription database should be treated as read-only unless you are using merge replication, transactional replication with updatable subscriptions, or peer-to-peer transactional replication.

·         Triggers are used at the Subscriber. Triggers can modify data at the Subscriber and also prevent the data from being updated if the trigger issues a ROLLBACK.

·         Scripts are executed by replication at the Subscriber but not at the Publisher.

·         Replication of stored procedure execution for a transactional publication produces different results at the Subscriber.

·         Constraint violations or other issues prevent rows from being inserted, updated, or deleted at the Subscriber.

https://technet.microsoft.com/en-us/library/ms152522(v=sql.105).aspx

The System Has a Performance Issue

Replication performance can be measured along the following five dimensions:

·         Latency: the amount of time it takes for a data change to be propagated between nodes in a replication topology.

·         Throughput: the amount of replication activity (measured in commands delivered over a period of time) a system can sustain over time.

·         Concurrency: the number of replication processes that can operate on a system simultaneously.

·         Duration of synchronization: how long it takes a given synchronization to complete.

·         Resource consumption: hardware and network resources used as a result of replication processing.

Replication performance is affected by the following factors:

·         Server and network hardware

·         Database design

·         Distributor configuration

·         Publication design and options

·         Filter design and use

·         Subscription options

·         Snapshot options

·         Agent parameters

·         Maintenance

A Slow Network Is Causing Problems

 

Security Issues Are Preventing Data from Being Replicated

https://technet.microsoft.com/en-us/library/ms152519(v=sql.105).aspx

 

The password has changed for an account used by one or more replication agents.

An account used by a replication agent is no longer valid and must be changed.

A replication agent does not have sufficient permissions to connect to a computer in the topology or perform operations in a database.

The Snapshot Agent cannot write to the snapshot folder; the Distribution Agent or Merge Agent cannot read from the snapshot folder.

https://technet.microsoft.com/en-us/library/ms152544(v=sql.105).aspx

Running a Large Number of Agents Is Causing Memory Problems

In some cases, you might need to run a large number of replication agents simultaneously on a single computer. For example, if you are generating snapshots for a large number of publications or you are synchronizing a large number of push subscriptions at the Distributor. In these situations, the non-interactive desktop heap (a memory structure in Microsoft Windows) can run out of memory.

Question to new customer from SQL side

1. What is the environment.  Are there any Architecture diagrams available ?
2. No.Of   Preprod /UAT/Testing/PROD servers. Versions of SQL Server
3. In these what are the High critical, medium and low servers and databases. Also is there any DR structure available or planning to set up now.
4. Are service accounts configured independetly to each server/instance or one for domain. Password reset policy.
5. What is load on the servers, average requests on the database. Avg growth of the databases.
6. standard maintenance jobs scheduled on each SQL Server.
7. Important list of jobs running on the servers
8. How many SQL instances installed per Server.
9. Any specific standards for installing SQL Server(Pre and Post).  -- Client level
10. What is the storage structure.
11. Uptime of database High critical, medium and low servers.
12. Backup startegery for disaster recovery.
13. What is the prefered SQL Server edition.
14. Database monitoring mechanism. Is there any tool to monitor and how it works.
15. What is the Windows version, CPU and RAM configurations.
16. What are the high availability techniques are in use in current environment.
17. What level of permissions required for database users.
18.   Any Troubleshooting guide they follow?
19. IS there any inventory of the Database/Instances/Servers. Which database relates to which application
20.   What are the issues /tickets we get usually.
21. SSIS,RS,AS
22. Any SQL Server/OS up-gradation plans.

23. Is there any incoming data from other interfaces

24.  About Monitoring Tools and ticketing system.

SQL Server Intigration Services Connectivity Issue

I am trying to connect SSIS 2012 from management studio on my local system.

But I am receiving below error while connecting

TITLE: Connect to Server
------------------------------

Cannot connect to D-113069721.

------------------------------
ADDITIONAL INFORMATION:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

------------------------------

Connecting to the Integration Services service on the computer "D-113069721" failed with the following error: "Access is denied."

By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service. See the help topic for information on how to configure access to the service.


For help, click: http://go.microsoft.com/fwlink/?LinkId=220763

------------------------------

Connecting to the Integration Services service on the computer "D-113069721" failed with the following error: "Access is denied."

By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service. See the help topic for information on how to configure access to the service.


------------------------------
BUTTONS:

OK
------------------------------

I followed below method to over come this access issue:

Below is the source URL:

https://msdn.microsoft.com/en-us/library/hh213130.aspx

1. Run Dcomcnfg.exe. Dcomcnfg.exe provides a user interface for modifying certain settings in the registry.
2. In the Component Services dialog, expand the Component Services > Computers > My Computer > DCOM Config node.
3. Right-click Microsoft SQL Server Integration Services 11.0, and then click Properties.
4. On the Security tab, click Edit in the Launch and Activation Permissions area.
5. Add users and assign appropriate permissions, and then click Ok.
6. Repeat steps 4 - 5 for Access Permissions.
7. Restart SQL Server Management Studio.
8. Restart the Integration Services Service.
   
   
   Same process has to be followed all the options in security tab(Access Permissions and Configuration Permissions) .
   
   
   This resolved my connectivity issue.