Highest Wait Type On A Database

Query TO Use :

WITH Waits AS

    (SELECT

        wait_type,

        wait_time_ms / 1000.0 AS WaitS,

        (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,

        signal_wait_time_ms / 1000.0 AS SignalS,

        waiting_tasks_count AS WaitCount,

        100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,

        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum

    FROM sys.dm_os_wait_stats

    WHERE wait_type NOT IN (

        'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',

        'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',

        'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',

        'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',

        'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',

        'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')

     )

SELECT

     W1.wait_type AS WaitType,

     CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,

     CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,

     CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,

     W1.WaitCount AS WaitCount,

     CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage

FROM Waits AS W1

INNER JOIN Waits AS W2

     ON W2.RowNum <= W1.RowNum

GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage

HAVING SUM (W2.Percentage) - W1.Percentage < 95 ;-- percentage threshold;

GO

Reference Table For Actions 

 http://msdn.microsoft.com/en-us/library/ms179984.aspx

Mirroring Basics

http://social.technet.microsoft.com/wiki/contents/articles/28637.database-mirroring-basics.aspx

Query which searches all columns in all databases

Here "USE" is  Dynamic.

sp_msforeachdb 'Use [?];

begin

select ''?'' as DBNAME

end

SELECT t.name AS table_name,

SCHEMA_NAME(schema_id) AS schema_name,

c.name AS column_name

FROM sys.tables AS t

INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

WHERE c.name LIKE ''%exe%''

ORDER BY schema_name, table_name;'

============================================

sp_MSforeachdb Examples 

Without using cursor we can execute a command in all the databases or on required databases by using sp_MSforeachdb.

Examples
sp_MSforeachdb 'select ''?'''   -- List all databases that are in the instance. '?' Indicated Database Name

sp_MSforeachdb 'Use [?]; exec sp_spaceused'  -- Example to execute any stored procedure

sp_MSforeachdb 'Use [?]; select * from sysfiles'  -- Example to execute any SQL Statement

sp_MSforeachdb 'Use [?]; Create table ForEachDBTest(Id Int)'  -- Creating a table in all databases

sp_MSforeachdb 'Use [?]; select * from ForEachDBTest'   -- Selecting rows from a table through all databases.

sp_MSforeachdb 'If ''?'' like ''TempDB''    -- Conditional wise execution
begin
select * from sysfiles
end '
Below command is used to display list of databases which has Create Statistics property is set to True

EXEC sp_MSforeachdb N'IF DatabasePropertyEx(''?'', ''ISAutocreateStatistics'')=1  
begin
 Print ''?''
end'

 

SQL Server Collation Change Step By Step

 Please find here:

https://gallery.technet.microsoft.com/SQL-Server-Collation-cc5cc63c

Replicated Transaction Information:Oldest distributed LSN OR The process could not execute 'sp_repldone/sp_replcounters'

Some times this is resolved by using repl_done command along with repl_flush.

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,    @time = 0, @reset = 1

go

sp_repleflush

This time we faced issue and went unsuccessful. Then we followed below steps.

1. Stopped Log Reader Agent
2. EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,    @time = 0, @reset = 1

3. sp_repleflush
4. Started Log reader agent
5. Replication came in Sync.

Enable Change Tracking - SQL Server 2008

Setup.

1. First enable Change tracking. Here’s some sample T-SQL you can execute to start the process

ALTER DATABASE SAMPLEDB
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 10 DAYS, AUTO_CLEANUP = ON);

GO

After it’s enabled and you want to check on which databases are affected, run this T-SQL

SELECT DB_NAME(database_id) [mydbname]
FROM SYS.CHANGE_TRACKING_DATABASES;

GO

To enable change tracking on a specific table use a query like this:

ALTER TABLE dbo.MYTABLENAME
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

Now to check the tables that have tracking enabled, you can get that list here:

SELECT OBJECT_NAME(OBJECT_ID) [MYTABLENAME]
FROM SYS.CHANGE_TRACKING_TABLES;

GO

No changes are required to statements for insert or update statements that are enabled with change_tracking.

To find out what’s changed after enabled, you can run a query similar to the following

SELECT ct.ID, ct.SYS_CHANGE_OPERATION, c.[NAME]

FROM CHANGETABLE(CHANGES dbo.MYTABLENAME, 0) ct

JOIN dbo.MYTABLENAME c ON c.[ID] = ct.[ID];

GO

Grant access to Report Builder

1. Start Report Manager.
   
2. Click Site Settings.
   
3. Click Security.
   
4. If a role assignment already exists for the user or group for which you want to configure Report Builder access, click Edit.
   Otherwise, click New Role Assignment. In Group or user, enter a Windows domain user or group account in this format: \. If you are using forms authentication or custom security, specify the user or group account in the format that is correct for your deployment.
   
5. Select System User, and then click OK.
   
6. Click Home.
   
7. Click the Folder Settings tab.
   
8. Click the Security tab.
   
9. If a role assignment already exists for the user or group for which you want to configure Report Builder access, click Edit.
   Otherwise, click New Role Assignment. In Group or user, enter a Windows domain user or group account in this format: \. If you are using forms authentication or custom security, specify the user or group account in the format that is correct for your deployment.
   
10. Select Report Builder, and then click Apply.
    
11. Repeat to create or modify role assignments for additional users or groups.
    

SQL Server 2012 SP2 failure - Validation for setting 'FAILOVERCLUSTERGROUP' failed. Error message: The cluster group cannot be determined for the instance name 'DBSQLPW80PA''

Error :

01) 2014-07-26 22:41:02 Slp: Sco: Attempting to get directory information for path C:\Program Files\Microsoft SQL Server\

(01) 2014-07-26 22:41:05 Slp: The failover instance name 'DBSQLPW80PA' does not exist.

(01) 2014-07-26 22:41:05 Slp: Validation for setting 'FAILOVERCLUSTERGROUP' failed. Error message: The cluster group cannot be determined for the instance name 'DBS80pA'.  This indicates there is a problem with the product registry setting for ClusterName, with product discovery, or the cluster resources.

(01) 2014-07-26 22:41:05 Slp: Validation for setting 'FAILOVERCLUSTERNETWORKNAME' failed. Error message: The SQL Server failover cluster instance name 'DBSQLPW80PA' could not be found as a cluster resource.

(01) 2014-07-26 22:41:05 Slp: Error: Action "Microsoft.SqlServer.Configuration.SetupExtension.ValidateFeatureSettingsAction" threw an exception during execution.

(01) 2014-07-26 22:41:05 Slp: Microsoft.SqlServer.Setup.Chainer.Workflow.ActionExecutionException: The cluster group cannot be determined for the instance name 'DBS80PA'.  This indicates there is a problem with the product registry setting for ClusterName, with product discovery, or the cluster resources. ---> Microsoft.SqlServer.Chainer.Infrastructure.InputSettingValidationException: The cluster group cannot be determined for the instance name 'DBS80PA'.  This indicates there is a problem with the product registry setting for ClusterName, with product discovery, or the cluster resources.

 
Resolution :

SStep 1.     Failover SQL Node A to Node B  

2Step 2.  On Node A, backup existing registry entry “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.DBSQL30A\Cluster”

3 

SStep 3.     On Node A, update existing registry entry “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.DBSQL30A\Cluster” to DBSQLPW80PA

4Step 4.     Reboot Node A

  

SStep 5.      Apply SQL 2012 SP2 and hotfix to  on Node A