Script for monitor blocking in SQL 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 in SQL Server


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.