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 →

SQL Server gets slower until I restart

Question: My SQL Server Instance gets slower over a few days. When I restart the SQL Server Instance – the response times improve. The cycle repeats itself ad infinitum. How can I troubleshoot ? Answer: This is a very common situation. Based on the same workload a SQL Server slowly starts grinding . It’s not a satisfactory situation for anyone. Firstly , the DBA needs to negotiate an agreed time for restart – disrupting server access. Secondly, a regular SQL Server restart suggests there are some underlying issues. Identifying the issues starts with asking the right question. What is happening... Read more →