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

How to fix tempdb full issue in SQL Server?How do I clear tempdb space in SQL Server? Read more →

How to shrink tempdb when Event ID 1101 Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'

Question: An analyst was completing some reconciliation checks and some of the queries required to complete the reconciliation forced the SQL Server TempDB to grow and fill the drive hosting the TempDB.The error message appearing was : Event ID 1101Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. What tactics are available to the DBA for dealing with this temporary problem? Answer: Even with the best... Read more →

SQL Server uptime query

Question: Is there a reliable method to extract the sql server uptime? I’ve tried different methods , such as xp_readerrorlog , but I discovered a limitation when the error log is recycled , updated date & time information is added to the error log , which is the time \ date of the error log recycle , but not the sql server instance restart. Answer: There are two methods I use to for a sql server uptime query. Method 1 extracts the sql server start time from the sys.dm_os_sys_info DMV. Method 2 utilises the tempdb create date , as the... Read more →

SQL Script - How to find type of objects filling TempDB

Question: My TempDB is filling up quickly. I've checked the queries on Monitor SQL Server TempDB usage - SQL Server DBA , and can see which transactions are currently running. Do you have a query which can present a summary of the type of activity filling my TempDB? Answers: Identifying what type of activity is filling the TempDB is the first step Broadly speaking there are 3 categories of TempDB activity. Identifying which one is in highest usage gives you an immediate insight into TempDB activity. Object Type - User:Normally for storing local\global temp tables, table variables,indices. Scope (user session... Read more →

How to create a temporary Stored Procedure using TempDB

Question: I have to execute a stored procedure for troubleshooting – but permanent stored procedures are disallowed. Is there a way to create a temporary stored procedure - ? Answer: Yes, you can create a temporary stored procedure in SQL Server , using tempdb. There are two options. A stored procedure available only to your session and a stored procedure available to more than your session Option 1 – Stored Procedure available only for your session CREATE PROC #myStoredProcedure If another session attempts to execute the stored procedure , the user will see a “cannot find the stored procedure” message.... Read more →