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.