March 3, 2014 at 12:55 pm
Hi All,
After our network team patched our the OS on our database server two weeks ago we started getting these errors almost constantly.
Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup.
This is a SQL Server 2008 64 bit machine with 150 GB of memory. The load on the server is minimal yet running any query seems to throw this error. When I run a query and monitor I can see TEMPDB fill up until it throws the error, its almost like it is ignoring the 150 GB of memory altogether. I checked the indexes and statistics, all good. There is one particular query that is having issues and I viewed the execution plan and that is good as well. These queries should run in seconds yet hang for 30 minutes I'm really stumped. Any ideas?
Thanks,
Ken
March 3, 2014 at 1:23 pm
Check if the disk on which TempDb exists is Full. If so you need to free up space on that drive.
Also check if Autogrowth is restricted on TempDB.
Final option would be add an additional file of Tempdb on a drive with enough space and restart SQL Server.
--
SQLBuddy
March 3, 2014 at 1:29 pm
sqlbuddy123 (3/3/2014)
Check if the disk on which TempDb exists is Full. If so you need to free up space on that drive.Also check if Autogrowth is restricted on TempDB.
Final option would be add an additional file of Tempdb on a drive with enough space and restart SQL Server.
--
SQLBuddy
Disks are ok, until you run a query and then they fill up. I've already added 100 GB drive for TempDB but all that seems to do is add another disk to fill up when running a query. The queries that are being run should not even fill up a 100 mb drive and they run fine on our replicated server and return results in seconds. Stumped because nothing has changed.
March 3, 2014 at 1:36 pm
What are the autogrowth settings for TempDB ? Could you tell us how did you add 100 GB drive to TempDB ? And also did you restart the SQL Server after the OS patching ?
--
SQLBuddy
March 3, 2014 at 1:43 pm
sqlbuddy123 (3/3/2014)
What are the autogrowth settings for TempDB ? Could you tell us how did you add 100 GB drive to TempDB ? And also did you restart the SQL Server after the OS patching ?--
SQLBuddy
There are 6 driver for tempdb with Autogrowth set to 100 MB/Limited to 4200 MB. I added the 7(100 GB) drive with 10 percent growth/Unlimited. I added through the GUI one and then I also dropped and added a second using ALTER database. Form what I understand it does not require a re-start but when I did the second time I went ahead and re-started anyway. In both scenarios I could confirm the added drive was being used by tempdb, one time it maxed out.
The SQL server has been re-started multiple times since the patch and the server twice.
March 3, 2014 at 1:56 pm
Could you check the SQL Server Error Log\ Application Log for any Disk I/O path errors during this error message ?
--
SQLBuddy
March 3, 2014 at 2:04 pm
sqlbuddy123 (3/3/2014)
Could you check the SQL Server Error Log\ Application Log for any Disk I/O path errors during this error message ?--
SQLBuddy
I've been through all the logs. On SQL Server nothing other than the TempDB error. There is an error in the App log.
"Unable to read Server Queue performance data from the Server service. The first four bytes (DWORD) of the Data section contains the status code, the second four bytes contains the IOSB.Status and the next four bytes contains the IOSB.Information."
I looke dit up I couldn't find anything significant to SQL.
March 3, 2014 at 2:06 pm
I kind of doubt that this is related to an OS patch. Have there been any recent code migrations? Check in sys.procedures to see if anything has a created or modified date in about the same date range. This has some of the hallmarks of a large cartesian join. Assuming the data files are on different drives from the tempdb files, do you see large disk activity spikes on the data drives as well?
Run this to see if you are lucky enough for this to be a stored procedure.
select db_name(ps.database_id), ps.object_id, t.[text], ps.execution_count, ps.total_worker_time, ps.total_physical_reads, ps.total_physical_reads/ps.execution_count as "avg phys reads"
from sys.dm_exec_procedure_stats ps cross apply
sys.dm_exec_sql_text (ps.sql_handle) t
where ps.total_physical_reads > 100000
order by ps.total_physical_reads desc
Does anyone know offhand if there is an easy way to peruse the procedure cache for suspect ad hoc or LINQ plans?
March 3, 2014 at 2:28 pm
Matt Crowley (3/3/2014)
I kind of doubt that this is related to an OS patch. Have there been any recent code migrations? Check in sys.procedures to see if anything has a created or modified date in about the same date range. This has some of the hallmarks of a large cartesian join. Assuming the data files are on different drives from the tempdb files, do you see large disk activity spikes on the data drives as well?Run this to see if you are lucky enough for this to be a stored procedure.
select db_name(ps.database_id), ps.object_id, t.[text], ps.execution_count, ps.total_worker_time, ps.total_physical_reads, ps.total_physical_reads/ps.execution_count as "avg phys reads"
from sys.dm_exec_procedure_stats ps cross apply
sys.dm_exec_sql_text (ps.sql_handle) t
where ps.total_physical_reads > 100000
order by ps.total_physical_reads desc
Does anyone know offhand if there is an easy way to peruse the procedure cache for suspect ad hoc or LINQ plans?
This is a Data Warehouse server, no code ahs changed on here for years, but I did verify. Unfortunantely I have done most of your checks already and that query didn't turn up anything, sadly. This may have nothing to do with the patch but the issue happened after a patch and reboot occured. So I found that odd just the same. Again the key things:
1. A query that has been run forever on a pretty beefy server now fills up 150 GB's of tempDB without completing.
2. The indexes are fine and execution plan is solid.
3. Runing these same queries on a replicated DB returns results in seconds.
The one thing I left out is for one query I did identify that a view was causing the issue with some of the queries and that view had had a self join. Your comment made me think of that. The view only returned 96 records but I took it out the query and dumped the results into a temp table and joined the query to that instead and it helped.
March 3, 2014 at 2:35 pm
Instead of a temp table, you could try a CTE as a replacement. Views don't like temp tables very much. Performance wise, it may evaluate to the same query plan as before, of course.
March 3, 2014 at 2:43 pm
Ken Overton (3/3/2014)
sqlbuddy123 (3/3/2014)
Could you check the SQL Server Error Log\ Application Log for any Disk I/O path errors during this error message ?--
SQLBuddy
I've been through all the logs. On SQL Server nothing other than the TempDB error. There is an error in the App log.
"Unable to read Server Queue performance data from the Server service. The first four bytes (DWORD) of the Data section contains the status code, the second four bytes contains the IOSB.Status and the next four bytes contains the IOSB.Information."
I looke dit up I couldn't find anything significant to SQL.
What is the build number for the SQL Server ?
--
SQLBuddy
March 3, 2014 at 3:40 pm
sqlbuddy123 (3/3/2014)
Ken Overton (3/3/2014)
sqlbuddy123 (3/3/2014)
Could you check the SQL Server Error Log\ Application Log for any Disk I/O path errors during this error message ?--
SQLBuddy
I've been through all the logs. On SQL Server nothing other than the TempDB error. There is an error in the App log.
"Unable to read Server Queue performance data from the Server service. The first four bytes (DWORD) of the Data section contains the status code, the second four bytes contains the IOSB.Status and the next four bytes contains the IOSB.Information."
I looke dit up I couldn't find anything significant to SQL.
What is the build number for the SQL Server ?
--
SQLBuddy
Microsoft SQL Server 2008 (SP2) - 10.0.4064.0 (X64)
March 3, 2014 at 4:04 pm
May be you should try upgrading to SP3. This may fix any instability\bugs that SQL Server has been experiencing since the OS Patch.
--
SQLBuddy
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply