SQL Server Uptime

Some time we need to find out the Sql Server time 

SET NOCOUNT ON
DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)
SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'
SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60
IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
ELSE
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hr+' hours & '+@min+' minutes'
IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
PRINT 'SQL Server is running but SQL Server Agent <<NOT>> running'
END
ELSE BEGIN
PRINT 'SQL Server and SQL Server Agent both are running'

END

How to Find The % of Job completed

How to Find The % of Job completed 



SELECT A.NAME,B.TOTAL_ELAPSED_TIME/60000 AS [Running Time],
B.ESTIMATED_COMPLETION_TIME/60000 AS [Remaining],
B.PERCENT_COMPLETE as [%],(SELECT TEXT FROM sys.dm_exec_sql_text(B.SQL_HANDLE))AS COMMAND FROM
msdb.dbo.sysjobs A, sys.dm_exec_requests B
WHERE  Job_id LIKE '052280DF-7799-46AB-A259-A522BB90FB01'
order by percent_complete desc,B.TOTAL_ELAPSED_TIME/60000 desc

Note:please replace the Job_id

How To Find Service Accounts of SQL server through t-SQL

How To Find Service Accounts of SQL server through t-SQL

DECLARE       @DBEngineLogin       VARCHAR(100)
DECLARE       @AgentLogin          VARCHAR(100)
EXECUTE       master.dbo.xp_instance_regread

              @rootkey      = N'HKEY_LOCAL_MACHINE',

              @key          = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',

              @value_name   = N'ObjectName',

              @value        = @DBEngineLogin OUTPUT

 EXECUTE       master.dbo.xp_instance_regread

              @rootkey      = N'HKEY_LOCAL_MACHINE',

              @key          = N'SYSTEM\CurrentControlSet\Services\SQLServerAgent',

              @value_name   = N'ObjectName',

              @value        = @AgentLogin OUTPUT

 SELECT        [DBEngineLogin] = @DBEngineLogin, [AgentLogin] = @AgentLogin

GO
SELECT servicename, service_account
FROM   sys.dm_server_services


GO

ERROR:The sql server database services feature failed when it was initially intalled.the feature must be removed before the current scenarion can proceed

Today when I am Adding node to sql server 2012 cluster I got one of the

Error :

The sql server database services feature failed when it was initially installed.the feature must be removed before the current scenario can proceed

For the above error says:there was problem on the installation which we have done already on first node so when we are trying to add node to specific cluster it through this kind of errors.

Resolution:

when the installation of sql server is success we can see the values on the registry  will be always '1'on the location:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.\ConfigurationState

when the installation having some problem the value in registry will be '2' you can see in the screen shot below.

So this is why our add node installation is getting failed.

when we are installing on the first node all the installation went cool but at the end our DQS was failed.you can see the error from the below screenshot

To resolve the issue with DQS ,we have manually ran the DQSIntaller.exe on the cluster now the DQS is running fine but it not updated in the registry.

That is why I got this error why I am running add not the cluster it is getting failed so to bi-pass this error I have made a change in the registry file

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.\ConfigurationState

SQL_DQ_FULL DWORD 0x00001(1)

Finally I have added node to cluster successfully.

(OR) you can run the repair from media setup file you can fix the problem like this and then you can add node to cluster as well.

Note:Before change any registry setting take the backup of the registry

FRAGMENTATION INFORMATION FOR A DATABASE WITH PAGE COUNTS AND AVG_FRAG

Script

SELECT object_name(object_id) as objectname, index_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID('Databasename'),
null, NULL, NULL, NULL)
where avg_fragmentation_in_percent >30 and page_count >1000

Note:please replace the database

Generally the page count > 1000 and avg_fragmentation_in_percent>30 indicates there was a performance degrade

SQL WAIT TYPES EXPLANATION

ASYNC_NETWORK_IO:

This wait type is RBAR (Row-By-Agonizing-Row) processing of results in a client, instead of caching the results client-side and telling SQL Server to send more. A common misconception is that this wait type is usually caused by network problems

CXPACKET:

This wait type always accrues when parallelism happens, as the control thread in a parallel operation waits until all threads have completed. However, when parallel threads are given unbalanced amounts of work to do, the threads that finish early also accrue this wait type, leading to it maybe becoming the most prevalent. So this one could be benign, as the workload has lots of good parallelism, but could be malignant if there’s unwanted parallelism or problems causing skewed distribution of work among parallel threads.

LCK_M_IX:

This wait type occurs when a thread is waiting for a table or page IX lock so that a row insert or update can occur. It could be from lock escalation to a table X or S lock causing all other threads to wait to be able to insert/update.

LCK_M_X:

 This wait type commonly occurs when lock escalation is happening. It could also be caused by using a restrictive isolation level like REPEATABLE_READ or SERIALIZABLE that requires S and IS locks to be held until the end of a transaction. Note that distributed transactions change the isolation level to SERIALIZABLE under the covers – something that’s bitten several of our clients before we helped them. Someone could also have inhibited row locks on a clustered index causing all inserts to acquire page X locks – this is very uncommon though.

PAGEIOLATCH_SH:

 This wait type occurs when a thread is waiting for a data file page to be read into memory. Common causes of this wait being the most prevalent are when the workload doesn't fit in memory and the buffer pool has to keep evicting pages and reading others in from disk, or when query plans are using table scans instead of index seeks, or when the buffer pool is under memory pressure which reduces the amount of space available for data.

 PAGELATCH_EX:

The two classic causes of this wait type are tempdb allocation bitmap contention (from lots of concurrent threads creating and dropping temp tables combined with a small number of tempdb files and not having TF1118 enabled) and an insert hotspot (from lots of concurrent threads inserting small rows into a clustered index with an identity value, leading to contention on the index leaf-level pages). There are plenty of other causes of this wait type too, but none that would commonly lead to it being the leading wait type over the course of a week.

 SOS_SCHEDULER_YIELD:

The most common cause of this wait type is that the workload is memory resident and there is no contention for resources, so threads are able to repeatedly exhaust their scheduling quanta (4ms), registering SOS_SCHEDULER_YIELD when they voluntarily yield the processor. An example would be scanning through a large number of pages in an index. This may or may not be a good thing.

WRITELOG:

This wait type is common to see in the first few top waits on servers as the transaction log is often one of the chief bottlenecks on a busy server. This could be caused by the I/O subsystem not being able to keep up with the rate of log flushing combined with lots of tiny transactions forcing frequent flushes of minimal-sized log blocks.

Script to get all Primary and foreign Keys in a Database

list of primary keys info on database

select '

ALTER TABLE ['+t.table_name+'] ADD  CONSTRAINT ['+t.constraint_name+'] PRIMARY KEY CLUSTERED 

(

 ['+ u. COLUMN_NAME +'] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 

SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

 ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]

GO' from INFORMATION_SCHEMA.TABLE_CONSTRAINTS t

, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u

where CONSTRAINT_TYPE = 'PRIMARY KEY' AND t.CONSTRAINT_NAME = u.CONSTRAINT_NAME

ORDER BY u.TABLE_NAME

list of foreign keys info on database

select '

ALTER TABLE ['+object_name(f.parent_object_id)+'] WITH NOCheck ADD  CONSTRAINT ['+f.name+'] FOREIGN KEY  

 ('+ COL_NAME(fc.parent_object_id,fc.parent_column_id) +') references ['+OBJECT_NAME (f.referenced_object_id)+'] 

 ('+COL_NAME(fc.referenced_object_id,fc.referenced_column_id)+') 

GO'

FROM sys.foreign_keys AS f

INNER JOIN sys.foreign_key_columns AS fc

ON f.OBJECT_ID = fc.constraint_object_id