get disk queue length – powershell

June 4, 2015

EXEC xp_cmdshell ‘C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -command “(get-counter ”\PhysicalDisk(1 d:)\Current Disk Queue Length” -ComputerName COMPUTERNAME).countersamples | select CookedValue”‘
FROM a WHERE r = 4

querying an xml column

June 2, 2015

simple example



‘ AS XML) xml_column
INTO #data_table

SELECT n.l.value(‘first_name[1]’,’VARCHAR(100)’) AS destination_record_count
FROM #data_table a
CROSS APPLY xml_column.nodes(‘//staff’) n(l)

SSIS – How to prevent a Task failure from failing an entire package

May 11, 2015

Sometimes you will have an SSIS package which contains volatile task where failure is sometimes allowable – for example if I file doesn’t exist you can not rename it, but you might not want the whole package to fail as a result. Here’s how to continue on failure without failing the package.

1. Create an On error handler for the task
2. Expose System Variables in the variables pane
3. Set the propagate variable value to FALSE


get OS process memory usage

May 7, 2015

exec xp_cmdshell ‘C:\Windows\System32\WindowsPowerShell\v1.0\Powershell.exe get-process -computername XXX ^| ft name,WorkingSet64 ‘

tempdb: DBCC SHRINKFILE: Page could not be moved because it is a work table page.

April 18, 2015

If you dont mind losing all your cached query plans try the following

DBCC SHRINKFILE: Page 4:11283400 could not be moved because it is a work table page.


I have not needed to follow up with the following command but apparently it could help


After killing all user sessions and issueing freesystemcache all I was able to shrink tempdb

Changing SSIS package program name

April 15, 2015

If you want to change the Program name for an ssis package you can do so in the connection string properties of the connection manager.

Open the connection manager, go to the ‘All’ page and update the Application Name property.

You might want to do this so to help assign the package to a resource workload group.

do not want header in ssrs export

March 16, 2015

Problem: do not want header row in a particular SSRS export, but want it in others

Solution: Add a new export type eg “csv – no header”

Locate the rsreportserver.config

Add the following to extension section of the file

CSV No Header


for other formats find the relevant “with header” section and create a new entry based on the above xml


steps taken to discover source of account lockout

February 26, 2015

*microsoft network monitor with authentication filter to capture pre authentication failures (program files)
*eventcombmt to parse security logs (success and failure) on domain controller event code 644 675 4771 4625 which revealed pre authentication failures and source machine, (logs stored in c:\temp, app in desktop\lockout)
*lockout status to unlock account, (located on desktop\lockout)

used to above to track the lockouts to a server. temporarily disabled kerberos pre authentication until we can work out what on the server is causing the lockouts

view database permissions

February 20, 2015

SELECT principal_name ,
princ.type_desc principal_type ,
perm.permission_name ,
perm.state_desc permission_type , object_name , schema_name ,
obj.type_desc object_type
FROM sys.database_principals princ
LEFT JOIN sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id]
LEFT JOIN sys.schemas s ON s.schema_id = obj.schema_id

Create a Gantt chart effect in SSRS

February 19, 2015

I want to create something like this:


Narayana Swamy Palla’s Blog_ Range Bar Chart (Gantt Chart) using SSRS 2008

example rdl, change extension to rdl


Get every new post delivered to your Inbox.