Tempdb growth

Question

Post reply

Tempdb growth

Oracle_91

SSChampion

Points: 14548
More actions
November 11, 2009 at 11:18 pm

#385770

Hi Guys,
Need urgent help, i need to know / track how many times the tempdb autogrowth is fired.
I have done the below change to the tempdb and i want to check this in sql logs but unable to find it.
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', FILEGROWTH = 20%)
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', FILEGROWTH = 20%)
GO
The Actual problem, There is available disk space in OS system drive. i.e D: 50 GB free space avaible and autogrowth has been specified as 20%. But when the client running some process which is making tempdb to grow and ending up with an error saying
Could not allocate space for object '(SYSTEM table id: -888228007)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.
But there is ample amount of free space being available.
At First , i would like to know whether the autogrowth is happening or not.
if so, i want to track how many times the autogrowth is done.
Any help/suggestions would be greatly appreciated.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Oracle_91 SSChampion Points: 14548 More actions · Answer 1

I tried to investigate the issue.

Tried at my local end.

Temp.mdf initial size 10m , autogrwth = 10% , maxsize =20m

Temp.ldf intital size 1m, autogrowth=10% , unrestricted growth

now, i tried to do some insert operation in temp db as follows

select @@spid

--52

use tempdb

go

create table #temp

(id int,

sname char(100),

saddr char(100)

)

declare @i numeric

set @i =1

while @i>0

begin

insert into #temp

select 777,'hjsdgfhsdgfhjsdf','manu'

end

/*

ERROR :::

Server: Msg 1105, Level 17, State 2, Line 6

Could not allocate space for object '#temp__00000000001A' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.

*/

Also, i opened one more window and i was excuting the below cmd, to check the autogrowth of the mdf and i can clearly observe the growth.

use tempdb

go

sp_helpfile

Now, my doubt is, am inserting records into a temp table it is saying

Could not allocate space for object '#temp__00000000001A' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.

But when will i get the ERROR saying

Could not allocate space for object '(SYSTEM table id: -667755274)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full

Is that sql server is inserting into a User table / Meta-data(system) table ??????????????

Expecting more comments on this!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

First, in the Object Explorer window of SSMS, look for tempdb under System Databases. Right click on tempdb and select Reports, then Standard Reports, then Disk Usage. If there have been autogrowth events recorded in the default trace you will see them here.

Second, you should not be growing your tempdb in percentage increments. Each time it grows, it grows by a larger amount. The problem you are experiencing may be a result that tempdb could not grow fast enough for the database engine and it reported that the database was full.

You should set tempdb to a size large enough for your daily activity without worrying about it needing to grow. The autogrowth should be set to a fixed amount to allow tempdb to grow when needed due to extraordinary events or unexpected growth in activity.

Oracle_91 SSChampion Points: 14548 More actions · Answer 3

Thank You.

I will allocate sufficient space in the beginig and kick off the script!

Thanks once again!

Oracle_91 SSChampion Points: 14548 More actions · Answer 4

Hi,

I want to keep track of all the autogrowths of a particular database in sql server table , how can we do that?

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 5

http://vyaskn.tripod.com/track_sql_database_file_growth.htm

MJ

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

mahesh.vsp (11/12/2009)
Hi,
I want to keep track of all the autogrowths of a particular database in sql server table , how can we do that?

This is also along the lines of the tempdb. Though you may need an autogrowth from time to time, you really should set the db large enough so it will give you enough free space to allow for enough data over the next 1-2yrs (some say more, some say less).

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Oracle_91 SSChampion Points: 14548 More actions · Answer 7

Hi All,

I have allocated enough space and the problem was resolved.

Thanks all.