create a sql server linked server with a name different to the server that is being linked to

July 1, 2014

Provider = sql server native client
datasource = server name
catalog = database

key is not to use server type = sql server in the gui

delete file older than a given date with xp_delete_file

May 28, 2014


EXECUTE master.dbo.xp_delete_file 0,N’C:\SQLServer\Backup’,N’bak’,’2014-05-27T17:29:04′

this will delete all files in the specified location with a .bak extension which are older than the specified date

trouble shooting resource semaphore waits

April 29, 2014

The following query will show where the waits are and if other groups have unused minimum amounts assigned

SELECT name ,
active_memgrant_count ,
active_memgrant_kb ,
memgrant_waiter_count ,
min_memory_percent ,
FROM sys.dm_resource_governor_resource_pools
ORDER BY pool_id

querying extended event event file (xel)

April 28, 2014

the following select string will query an even file for the following sample xml

SELECT q.duration,q.database_id,FILE_ID,file_type,total_size_kb,size_change_kb,timestamp,name,file_name,username
SELECT duration=e.event_data_XML.value(‘(//data[@name="duration"]/value)[1]‘,’int’)
, database_id=e.event_data_XML.value(‘(//data[@name="database_id"]/value)[1]‘,’int’)
, file_id=e.event_data_XML.value(‘(//data[@name="file_id"]/value)[1]‘,’int’)
, file_type=e.event_data_XML.value(‘(//data[@name="file_type"]/value)[1]‘,’varchar(max)’)
, total_size_kb=e.event_data_XML.value(‘(//data[@name="total_size_kb"]/value)[1]‘,’int’)
, size_change_kb=e.event_data_XML.value(‘(//data[@name="size_change_kb"]/value)[1]‘,’int’)
, file_name=e.event_data_XML.value(‘(//data[@name="file_name"]/value)[1]‘,’varchar(max)’)
, username=e.event_data_XML.value(‘(//action[@name="username"]/value)[1]‘,’varchar(max)’)
, TIMESTAMP=e.event_data_XML.value(‘(//@timestamp)[1]‘,’datetime2(7)’)
, name=e.event_data_XML.value(‘(//@name)[1]‘,’varchar(max)’)
, *
SELECT CAST(event_data AS XML) AS event_data_XML
FROM sys.fn_xe_file_target_read_file(‘\\server_name\d$\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\aaa_0_130385763224100000.xel’, NULL, NULL, NULL)



<event name=”database_file_size_change” package=”sqlserver” timestamp=”2014-03-06T12:00:04.561Z”>
<data name=”duration”>
<data name=”database_id”>
<data name=”file_id”>
<data name=”file_type”>
<text>Data file</text>
<data name=”is_automatic”>
<data name=”total_size_kb”>
<data name=”size_change_kb”>
<data name=”file_name”>
<data name=”database_name”>
<value />
<action name=”username” package=”sqlserver”>




Delete files over 24 hours old via SSIS

March 27, 2014

The following should be pasted into a for each file enumerator, the file name is passed to the script

Dim fi As System.IO.FileSystemInfo

fi = My.Computer.FileSystem.GetFileInfo(Dts.Variables(“file_name”).Value.ToString)

If (DateDiff(“h”, Now.Date, fi.CreationTime)) >= 24 Then
End If

Drop all temp tables

March 27, 2014

Rather than list DROP TABLE1,2,3….n.



SET @d_sql = ”

SELECT @d_sql = @d_sql + ‘DROP TABLE ‘ + QUOTENAME(name) + ‘;

FROM tempdb..sysobjects
WHERE name like ‘#[^#]%’

IF @d_sql <> ”
PRINT @d_sql
EXEC( @d_sql )

UK Heat Map using SSRS

March 20, 2014

download a shape file, there is one available here

extract all contents to a directly, not just the .shp file

create a dataset which contains postcodes and summarized data, simple example:

SELECT ‘WV’ AS post_code, 1 AS count

Drag a map report item to your new report
select esri shapefile and browse to your downloaded file
Set the map resolution to smallest size (i find that the report will fail if I use best quality)
set the position and zoom of the map
select color analytical map

tick the box next to label, select postcode from the dropdown box

change field to visualize to sum(count)

shape file:shape file

Interrogate AD group membership

March 13, 2014

To find which Groups a user is a member of

exec xp_logininfo‘DOMAIN\jlester’,‘all’
To list all members of a group
exec xp_logininfo‘DOMAIN\GROUP’,‘members’

Mapping Report Subscriptions to SQL JOB Names

February 19, 2014

SELECT ‘EXEC ReportServer.dbo.AddEvent @EventType=”TimedSubscription”,
+ CAST(a.SubscriptionID AS VARCHAR(40)) + ”” AS ReportCommand , AS JobName ,
a.SubscriptionID , ,
e.path ,
d.description ,
laststatus ,
eventtype ,
LastRunTime ,
date_created ,
FROM ReportServer.dbo.ReportSchedule a
JOIN msdb.dbo.sysjobs b ON CAST(a.ScheduleID AS VARCHAR(1000)) =
JOIN ReportServer.dbo.ReportSchedule c ON = c.ScheduleID
JOIN ReportServer.dbo.Subscriptions d ON c.SubscriptionID = d.SubscriptionID
JOIN ReportServer.dbo.Catalog e ON CAST(d.report_oid AS VARCHAR (MAX)) = CAST(e.itemid AS VARCHAR(MAX))

check if user is a member of AD Group

January 20, 2014

EXECUTE AS USER=‘domain\user’






Get every new post delivered to your Inbox.