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

see: http://stackoverflow.com/questions/11531896/how-to-export-a-csv-without-header-in-ssrs

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 princ.name principal_name ,
princ.type_desc principal_type ,
perm.permission_name ,
perm.state_desc permission_type ,
obj.name object_name ,
s.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

Database is stuck ‘In Recovery’ with an unacceptably long delay before it is available and you dont care about any pending rollbacks completing.

February 19, 2015

Stop the sql server service
Rename the log file of the database that is in recovery
start the service

alter database XXX set emergency, single_user
alter database xxx rebuild log on (NAME = ‘log file name’, filename = ‘G:\Logs\log file name.ldf’)
alter database xxx set online, multi_user

the database is back online and the rollback has been partially completed

sort tempdb session usage by size

February 10, 2015

select * from tempdb.sys.dm_db_session_space_usage
order by internal_objects_alloc_page_count desc

Start up server in master only recovery mode, allowing only access to sqlcmd

January 28, 2015

on a command line
net start MSSQL$instancename /f /T3608 /m”SQLCMD”

then logon using sqlcmd

Access Denied error when attempting to access Oracle data via a linked server

January 27, 2015

Problem: The OLE DB provider “OraOLEDB.Oracle” for linked server “X” reported an error. Access Denied
Cannot get the column information from OLE DB provider “OraOLEDB.Oracle” for linked server “X”

Solution: Go to the OraOLEDB.Oracle provider, check Allow in process. Then restart the server service.

Can be scripted using EXEC master.dbo.sp_MSset_oledb_prop N’OraOLEDB.Oracle’, N’AllowInProcess’, 1


Get every new post delivered to your Inbox.