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
This blog is to learn and share SQL DBA and Azure SQL knowledge among people by Bandaru Ajeyudu & Sudheer Thota
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
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.
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.
Subscribe to:
Posts (Atom)