Find resource usage by Application

 

SELECT 

     CPU            = SUM(cpu_time)

    ,WaitTime       = SUM(total_scheduled_time)

    ,ElapsedTime    = SUM(total_elapsed_time)

    ,Reads          = SUM(num_reads) 

    ,Writes         = SUM(num_writes) 

    ,Connections    = COUNT(1) 

    ,Program        = program_name

    ,LoginName      = ses.login_name

FROM sys.dm_exec_connections con

LEFT JOIN sys.dm_exec_sessions ses

    ON ses.session_id = con.session_id

GROUP BY program_name, ses.login_name

ORDER BY cpu DESC

What transaction is causing log space to fill out

 

-- -- -- -- --Transaction causing log space filled most-- -- -- -- --

SELECT tst.[session_id],

s.[login_name] AS [Login Name],

DB_NAME (tdt.database_id) AS [Database],

tdt.[database_transaction_begin_time] AS [Begin Time],

tdt.[database_transaction_log_record_count] AS [Log Records],

tdt.[database_transaction_log_bytes_used] AS [Log Bytes Used],

tdt.[database_transaction_log_bytes_reserved] AS [Log Bytes Rsvd],

SUBSTRING(st.text, (r.statement_start_offset/2)+1, 

((CASE r.statement_end_offset

WHEN -1 THEN DATALENGTH(st.text)

ELSE r.statement_end_offset

END - r.statement_start_offset)/2) + 1) AS statement_text,

st.[text] AS [Last T-SQL Text],

qp.[query_plan] AS [Last Plan]

FROM sys.dm_tran_database_transactions tdt

JOIN sys.dm_tran_session_transactions tst

ON tst.[transaction_id] = tdt.[transaction_id]

JOIN sys.[dm_exec_sessions] s

ON s.[session_id] = tst.[session_id]

JOIN sys.dm_exec_connections c

ON c.[session_id] = tst.[session_id]

LEFT OUTER JOIN sys.dm_exec_requests r

ON r.[session_id] = tst.[session_id]

CROSS APPLY sys.dm_exec_sql_text (c.[most_recent_sql_handle]) AS st

OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp

where DB_NAME (tdt.database_id) = 'reportingdb'

ORDER BY [Log Bytes Used] DESC;

Find who are in what groups

 xp_logininfo 'domaun\Groupname', 'members'--find what logins inside group

purge or delete files older than x days

 

DECLARE @DeleteDate datetime

SET @DeleteDate = DateAdd(day, -3, GetDate()) 

EXECUTE master.sys.xp_delete_file 0, -- FileTypeSelected (0 = FileBackup, 1 = FileReport)

N'\\xyz\abc\12\', -- folder path (trailing slash)

N'bak', -- file extension which needs to be deleted (no dot)

@DeleteDate, -- date prior which to delete 

1 -- subfolder flag (1 = include files in first subfolder level, 0 = not) 

PowerShell script to move files from source location to destination and then delete the file, folder and all files with name

 

$ErrorActionPreference = "SilentlyContinue"
$path = "\\abc\c\NareshTest.zip"
$dest = "\\xyz\d\abc"

$ErrorActionPreference = "SilentlyContinue"
Expand-Archive -LiteralPath " \\abc\c\NareshTest.zip " -DestinationPath " \\xyz\d\abc" -Force

Copy-Item -Path " \\abc\c\NareshTest  .csv" -Destination " \\xyz\d\abc" -Force
 

Start-Sleep -s 30

Remove-Item -Path " \\abc\c\NareshTest.csv " -Force
Remove-Item -Path " \\abc\c\NareshTest.zip" -Force
<#
Remove-Item -Path " \\abc\c\NareshTest.*" -Force
#>