Category Archives: Events and News

Find SSRS report execution time

SELECT
ExecutionLog.ReportID
,TBL_Catalog.”Name” Report_Name
,TBL_Catalog.”Path” Report_Path
,COUNT(*) AS RunCount
,COUNT(DISTINCT ExecutionLog.UserName) AS UserCount
,MIN(ExecutionLog.TimeEnd – ExecutionLog.TimeStart ) Min_Runtime
,Max(ExecutionLog.TimeEnd – ExecutionLog.TimeStart ) Max_Runtime
,AVG(ExecutionLog.TimeEnd – ExecutionLog.TimeStart ) avg_Runtime
,MIN(ByteCount) Min_ByteCount
,Max(ByteCount) Max_ByteCount
,AVG(ByteCount) Avg_ByteCount
,MIN(RowCount) Min_RowCount
,Max(RowCount) Max_RowCount
,AVG(RowCount) Avg_RowCount
FROM
dbo.ExecutionLog ExecutionLog
INNER JOIN
/dbo.”Catalog” TBL_Catalog
ON ExecutionLog.ReportID = TBL_Catalog.ItemID

WHERE ExecutionLog.TimeStart > DATEADD(‘dd’,-30, current_date())
GROUP BY
ExecutionLog.ReportID
,TBL_Catalog.”Name”
,TBL_Catalog.”Path”
ORDER BY COUNT(*) DESC

Find the latest update on tables by user

SELECT spid,
[Current LSN],
[Operation],
[Context],
[Transaction ID],
[Description]
INTO #temp
FROM
fn_dblog (NULL, NULL),
(SELECT
[Transaction ID] AS [tid]
FROM
fn_dblog (NULL, NULL)
WHERE
[Transaction Name] LIKE ‘%%’) [fd]

WHERE
[Transaction ID] = [fd].[tid] and [Description] like ‘%update%’;
GO

—————————————————-
create table #tmp

(

spid int, ecid int, status varchar(50),loginname varchar(50),

hostname varchar(50),blk int, dbname varchar(50),

cmd varchar(150), request_id int

)

insert into #tmp

exec sp_who
—————————-
select * From #temp join #tmp on #temp.spid = #tmp.spid

drop table #temp
drop table #tmp

SQL server find all the cdc related databases and tables

DECLARE @sql as varchar(max)

SET @sql = ‘Select @@SERVERNAME DB_SERVER, NULL AS DB_NAME, NULL AS Table_name, NULL AS CDC_table_name, Null As Create_Date’

SELECT @sql=@sql+’ UNION ALL select @@SERVERNAME DB_SERVER, ”’ + name + ”’ AS DB_Name, sys.name COLLATE DATABASE_DEFAULT AS table_name
, cdc.capture_instance + ”_CT” as CDC_table_name
,cdc.Create_Date

FROM ‘ + name + ‘.sys.tables sys(NOLOCK) JOIN ‘ + name + ‘.cdc.change_tables cdc (NOLOCK) on sys.object_id = cdc.source_object_id’ +char(10)
FROM sys.databases
WHERE is_cdc_enabled = 1

EXEC (@sql)