Need some clarification on Initial Size and Autogrowth?

Question

Need some clarification on Initial Size and Autogrowth?

SQLisAwe5oMe

SSChampion

Points: 14052
More actions
June 11, 2013 at 6:56 am

#274910

Is the Initial size(MB) under db properties/Files grows as database grows?
As per my understanding, initial size grows as per the autogrowth set?
I recently had a tempdb issue, however, the initial size shows 8MB(data) and 1MB(log) but the actual size of the tempdb was about 29GB.
Can someone clear this doubts for me?
Thanks,
SueTons.
Regards,
SQLisAwe5oMe.

Viewing 6 posts - 1 through 6 (of 6 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

for tempdb (only) initial size shows the file size at create time, it does not reflect autogrowths. (a manual growth via alter database would be reflected as this would change the file size on restart.)

All other databases initial size shows actual file size.

---------------------------------------------------------------------

Matthew Darwin SSCertifiable Points: 5513 More actions · Answer 2

Is that the actual size of the data files or the log files?

I've seen interesting things with tempdb following runaway transactions whereby the log showed a negative available space value (as in this example).

Either way, I would suggest that you need to consider sizing your tempdb appropriately. Klaus Aschenbrenner has some pretty good tips on tempdb performance; you can view them from his last SQLBits presentation here.

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 3

Matthew Darwin (6/11/2013)
Is that the actual size of the data files or the log files?
I've seen interesting things with tempdb following runaway transactions whereby the log showed a negative available space value (as in this example).
Either way, I would suggest that you need to consider sizing your tempdb appropriately. Klaus Aschenbrenner has some pretty good tips on tempdb performance; you can view them from his last SQLBits presentation here.

Thanks George and Matthew for your quick response.

To clarify my scenario, the tempdb was holding space of 29GB out of 30GB drive, however, the space available inside the tempdb was 28GB( so actual size of tempd is only 1GB).....so, I was looking under files tab and saw 8MB for data and 1MB for Log as the initial size and I couldn't explain myself. So, thanks for clarifying that.

And also, I would need to take some space away from the temdb itself, so I can avoid getting tickets for the drive being running out of space....and I tried to shrink the files, but since the files only hold 8MB and 1MB, I can't really do much there.

So, is the only way to release some space to OS would be to shrink the temdb itself? I hope I am explaining correctly. Please advise.

Thanks.

SueTons.

Regards,
SQLisAwe5oMe.

Matthew Darwin SSCertifiable Points: 5513 More actions · Answer 4

Well if tempdb has grown to 29GB then it's a surefire signal that you shouldn't be running it with the default settings. Something at some point has needed that much space in tempdb to run.

Have a read of this to give you some pointer:- http://msdn.microsoft.com/en-us/library/ms175527(v=sql.105).aspx

As for shrinking the files, you can run a DBCC SHRINKFILE command against tempdb; however the best method of doing this is to restart the instance, this will result in tempdb being recreated with the specified sizes. You can then add more files to tempdb and increase them to a size of your choosing.

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

HowardW One Orange Chip Points: 29109 More actions · Answer 5

Matthew Darwin (6/11/2013)
As for shrinking the files, you can run a DBCC SHRINKFILE command against tempdb

I'd be cautious about this. I think the official recommendation is still to only do this in single user mode for tempdb as shrinking while user transactions are running can cause corruption. The KB article for this was still in place as of 2005. It's possible it's fixed in 2008, but I've seen no confirmation.