useful powershell for sql jobs

–to delete files from drive $limit = (Get-Date).AddDays(-7) $path = “D:folderpath” # Delete files older than the $limit. Get-ChildItem -Path $path -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit } | Remove-Item -Force# Delete any empty directories left behind after deleting the old…

server side trace sql script

https://www.toadworld.com/platforms/sql-server/w/wiki/10400.the-server-side-trace-what-why-and-how — Create a Queue declare @rc int declare @TraceID int declare @maxfilesize bigint set @maxfilesize = 5 — Please replace the text InsertFileNameHere, with an appropriate — filename prefixed by a path, e.g., c:MyFolderMyTrace. The .trc extension — will be appended to the filename…

create extended event

CREATE EVENT SESSION [compliance] ON SERVER ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1) ACTION(package0.collect_system_time,sqlserver.database_name,sqlserver.username) WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N’%decrypt%’))) ADD TARGET package0.event_file(SET filename=N’C:folderxeventname.xel’,max_file_size=(5),max_rollover_files=(10)) WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON) GO

read from extended events written to ring buffer

SELECT CAST(target_data as xml) AS targetdata INTO #capture_waits_data FROM sys.dm_xe_session_targets xet JOIN sys.dm_xe_sessions xes ON xes.address = xet.event_session_address WHERE xes.name = ‘xeventname’ AND xet.target_name = ‘ring_buffer’; SELECT xed.event_data.value(‘(action[@name=”username”]/value)[1]’, ‘varchar(100)’) AS username, xed.event_data.value(‘(action[@name=”database_name”]/value)[1]’, ‘varchar(100)’) AS dbname, xed.event_data.value(‘(data[@name=”statement”]/value)[1]’, ‘varchar(max)’) AS statement, xed.event_data.value(‘(action[@name=”collect_system_time”]/value)[1]’, ‘datetime’) AS date FROM #capture_waits_data…

read from extended events file

SELECT file_name, CAST(event_data AS XML) AS ‘event_data_XML’ FROM sys.fn_xe_file_target_read_file(‘c:folderxevents*.xel’, NULL, NULL, NULL) –parsing xml http://blogs.msdn.com/b/extended_events/archive/2010/03/09/reading-event-data-101-what-s-up-with-the-xml.aspx SELECT event_xml.value(‘(action[@name=”username”]/value)[1]’, ‘varchar(100)’) AS username, event_xml.value(‘(action[@name=”database_name”]/value)[1]’, ‘varchar(100)’) AS dbname, event_xml.value(‘(data[@name=”statement”]/value)[1]’, ‘varchar(max)’) AS sqlstatement, event_xml.value(‘(action[@name=”collect_system_time”]/value)[1]’, ‘datetime’) AS date FROM    (SELECT CAST(event_data AS XML) xml_event_data FROM sys.fn_xe_file_target_read_file(‘D:Compliancecompliance*.xel’, ‘D:Compliancecompliance*.xem’, NULL, NULL)) AS event_table…

parse xml data field with sql query

select x.c.value(‘(action[@name=”username”]/value)[1]’, ‘varchar(100)’) AS username, x.c.value(‘(action[@name=”database_name”]/value)[1]’, ‘varchar(100)’) AS dbname, x.c.value(‘(data[@name=”statement”]/value)[1]’, ‘varchar(max)’) AS sqlstatement, x.c.value(‘(action[@name=”collect_system_time”]/value)[1]’, ‘datetime’) AS date from xmltable as c outer apply c.event_data_XML.nodes(‘event’) as x(c)

sql server audit

use master — Create the server audit. create SERVER AUDIT auditname TO application_LOG with (ON_FAILURE = CONTINUE, QUEUE_DELAY = 1000, AUDIT_GUID = ‘e9f52225-4e78-48f2-b3f4-8e1de833d7bf’) GO — Move to the target database. USE userdb ; GO — Create the database audit specification. CREATE DATABASE AUDIT SPECIFICATION DBSpecificationName…

SQL statements currently using transaction logs

select * FROM sys.dm_tran_session_transactions AS tst INNER JOIN sys.dm_tran_active_transactions AS tat ON tst.transaction_id = tat.transaction_id INNER JOIN sys.dm_tran_database_transactions AS tdt ON tst.transaction_id = tdt.transaction_id INNER JOIN sys.dm_exec_sessions es ON tst.session_id = es.session_id INNER JOIN sys.dm_exec_requests er ON tst.session_id = er.session_id CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) TXT http://www.sqlservercentral.com/articles/Transaction+Log/122600/