August 30, 2012 at 5:04 pm
I'm setting up SCOM 2012 with the SQL Server Management Pack and I'm getting these errors:
Could 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.
The DBAs are telling me to ignore this error as the database is set to Auto-Grow. But if this were the case, then shouldn't I NOT be getting these errors? Shouldn't the DB auto-grow the database before generating this error?
Thanks.
August 31, 2012 at 11:06 am
Autogrow may be setup and the max size might be UNLIMITED but the error can still occur if the disk the tempdb data file is stored on has filled up.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 31, 2012 at 11:44 am
Could be one of a few things
The DB has reached the max size it's allowed and won't autogrow any more
The autogrow's too slow, probably because the growth increments are tiny
The disk is full.
All of those are things the DBA is responsible for.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 31, 2012 at 11:50 am
GilaMonster (8/31/2012)
Could be one of a few thingsThe DB has reached the max size it's allowed and won't autogrow any more
The autogrow's too slow, probably because the growth increments are tiny
The disk is full.
All of those are things the DBA is responsible for.
This one is new to me. I thought the lucky query that compelled the database to autogrow would simply be blocked and would wait for the autogrow to complete but would not necessarily give up.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 31, 2012 at 11:56 am
I remember seeing it, but back on SQL 2000. Maybe there was something else happening as well that caused it. Not sure.
There's also the case where autogrow takes too long and times out. Seen that on a bad IO subsystem
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 31, 2012 at 12:06 pm
GilaMonster (8/31/2012)
I remember seeing it, but back on SQL 2000. Maybe there was something else happening as well that caused it. Not sure.There's also the case where autogrow takes too long and times out. Seen that on a bad IO subsystem
Oof, those systems must have been under duress. Good to know these cases are out there lurking. Thanks as always.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 31, 2012 at 12:25 pm
Not really. Mix of a really crap IO subsystem (500ms avg write latency) and a bad autogrow setting (10% on a file hundreds of GB in size)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 31, 2012 at 12:30 pm
OK, I can see that on the timeouts. On the 'could not allocate' I was thinking the other way, hundreds or thousands of requests all asking for 1MB autogrows simultaneously.
@Julian, what are your current tempdb file settings:
USE tempdb;
WITH cte
AS (
SELECT DB_NAME(database_id) AS name,
mf.name AS db_filename,
mf.physical_name,
CAST(((mf.size * 8) / 1024.0) AS DECIMAL(20, 2)) AS initial_size_MB,
CAST(((df.size * 8) / 1024.0) AS DECIMAL(20, 2)) AS actual_size_MB,
CASE mf.is_percent_growth
WHEN 0 THEN STR(CAST(((mf.growth * 8) / 1024.0) AS DECIMAL(10, 2))) + ' MB'
WHEN 1 THEN STR(mf.growth) + '%'
END AS auto_grow_setting
FROM sys.master_files mf
JOIN sys.database_files df ON mf.name = df.name
WHERE mf.database_id = DB_ID()
)
SELECT *,
actual_size_MB - initial_size_MB AS change_in_MB_since_restart
FROM cte;
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 22, 2013 at 6:23 am
I had someone perform an SELECT INTO and let it run for a day and it caused tempdb to grow to 800 GB.
So I tried shrinking the file with the shrinkfile and shrink database.
Finally I used the ALTER DATABASE tempdb MODIFY FILE but the size of tempdb is still 300 GB.
There are no open transactions.
Any ideas on what I can do?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 22, 2013 at 6:39 am
Restart SQL.
New questions in a new thread please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 22, 2013 at 6:57 am
ALTER DATABASE tempdb MODIFY FILE changes the initial size settings but they do not take effect until you restart the service. Shrinking tempdb is risky too and is not recommended unless the instance is in single user mode.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply