Scripts

Get table row count for all tables in a database


SELECT o.name, ddps.row_count
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0
ORDER BY row_count DESC

------------------------------------------------------------------------------------------------------------
Queries taking longest elapsed time:


SELECT TOP 100 qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds
, qs.total_elapsed_time / 1000000.0 AS total_seconds
, qs.execution_count
, SUBSTRING (qt.text,qs.statement_start_offset/2
, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query
, o.name AS object_name
, DB_NAME(qt.dbid) AS database_name 
FROM sys.dm_exec_query_stats qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN 
sys.objects o ON qt.objectid = o.object_id
WHERE qt.dbid = DB_ID()
ORDER 
BY average_seconds DESC;
------------------------------------------------------------------------------------------------------------
Queries doing most I/O:

SELECT TOP 100

    (total_logical_reads + total_logical_writes) / qs.execution_count AS average_IO,

    (total_logical_reads + total_logical_writes) AS total_IO,

    qs.execution_count AS execution_count,

    SUBSTRING (qt.text,qs.statement_start_offset/2, 

         (CASE WHEN qs.statement_end_offset = -1 

            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 

          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS indivudual_query,
    o.name AS object_name,
    DB_NAME(qt.dbid) AS database_name
  FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qt.dbid = DB_ID()
  ORDER BY average_IO DESC;
------------------------------------------------------------------------------------------------------------
TO FIND HOW MUCH % BACKUP COMPLETED?
SELECT      command, percent_complete,
            'elapsed' = total_elapsed_time / 60000.0,
            'remaining' = estimated_completion_time / 60000.0
FROM        sys.dm_exec_requests
WHERE       command like 'BACKUP%'
------------------------------------------------------------------------------------------------------------
Sample code to trace locks:

SELECT  L.request_session_id AS SPID,
    DB_NAME(L.resource_database_id) AS DatabaseName,
    O.Name AS LockedObjectName,
    P.object_id AS LockedObjectId,
    L.resource_type AS LockedResource,
    L.request_mode AS LockType,
    ST.text AS SqlStatementText,       
    ES.login_name AS LoginName,
    ES.host_name AS HostName,
    TST.is_user_transaction as IsUserTransaction,
    AT.name as TransactionName,
    CN.auth_scheme as AuthenticationMethod
FROM    sys.dm_tran_locks L
    JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
    JOIN sys.objects O ON O.object_id = P.object_id
    JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
    JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
    JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
    JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
    CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id
------------------------------------------------------------------------------------------------------------

Script for monitor blocking in my prod server ....

select loginame,cpu,memusage,physical_io,* from master..sysprocesses a where exists ( select b.* from master..sysprocesses b where b.blocked > 0 and b.blocked = a.spid ) and not exists ( select b.* from master..sysprocesses b where b.blocked > 0 and b.spid = a.spid )order by spid

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

T-SQL Query to find currently running jobs


SELECT  J.name as Running_Jobs,  
  JA.Start_execution_date As Starting_time,
        datediff(ss, JA.Start_execution_date,getdate()) as [Has_been_running(in Sec)]
FROM msdb.dbo.sysjobactivity JA<a name="more"></a>
JOIN msdb.dbo.sysjobs J
ON J.job_id=JA.job_id
WHERE job_history_id is null
      AND start_execution_date is NOT NULL
ORDER BY start_execution_date

--------------------------------------------------------------------------------------------------------------------------------------------------------------
To Drop All Linked Server:

DECLARE @sql NVARCHAR(MAX)

    DECLARE db_cursor CURSOR FOR  
        select 'sp_dropserver ''' + [name] + '''' from sys.servers

    OPEN db_cursor   
    FETCH NEXT FROM db_cursor INTO @sql   

    WHILE @@FETCH_STATUS = 0   
    BEGIN   

           EXEC (@sql)

           FETCH NEXT FROM db_cursor INTO @sql   
    END   

    CLOSE db_cursor   

    DEALLOCATE db_cursor

Note: It will delete even your local server details also.
--------------------------------------------------------------------------------------------------------------------------------------------------------------
If you not found local server details in sys.servers

sp_addserver 'Servername', local
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Robocopy:
Robocopy.exe E:\MSSQL\BAK(source) "\\<server_name>\Latest_BKP"(destination) <Backup_File_name.BAK> /mt:50
--------------------------------------------------------------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Popular Posts