October 18, 2005 at 8:55 am
So here’s a weird one. In the unknown depths of time (before any of us started working here), some unknown sage tagged our tempdb to have an 8GB max size. (The hosting volume is 16G, and nothing else of relevance is on it.) This caused a problem recently, and a few days back we reset it to unlimited size. This morning more processes blew up, and we discovered that tempdb had been reset to an 800M max size. Now there’s no way any of us would do this, odds are no application did this [though who knows what gems the unknown sages buried in the codebase of yore--that, based on precedent, is my bet]. All we know is that “they” had to reboot the database server last night, and we can only assume that the change went in effect then. [Hmm, gotta check for any “run on startup” routines.]
A question: Why or how did a limit on the tempdb suddenly crop up?
The real question: Where are these settings actually stored, anyway? (1) For every database, this and related critical info is stored in SysFiles. This data gets read and loaded when SQL starts up, because (2) in the master database, sysDatabases stores the location of each file’s primary file group, which always holds the system tables, so it gets opened and the database’s stats are accessed thusly, BUT (3) tempdb gets recreated every time SQL Server boots, so presumably the old tempdb (and it’s contained settings and definitions) gets wiped and completely ignored. If so, where are those tempdb settings actually stored? (I just tested--SQL doesn’t open up the old tempdb, read the stats, close it, wipe it, and recreate it.)
Philip
October 18, 2005 at 9:24 am
I wonder if it takes the copy of your model database.
mom
October 18, 2005 at 10:17 am
The physical attributes for tempdb are stored in master.dbo.sysaltfiles.
At startup, tempdb is created from model and then the physical attributes are applied such as changing the recovery mode to simple, adding any additional files and growing the underlying files.
SQL = Scarcely Qualifies as a Language
October 18, 2005 at 2:42 pm
Dang, how 'bout that. I'd guess it does this for ALL databases. I'll have to stick a "KilroyWasHere" table in model and see if it shows up after the next restore I run...
Thanks for the info! I wrote off as redundant fluff a long time back. Time to reassess...
Philip
October 19, 2005 at 5:41 pm
If I am not mistaken from your initial comments a reboot did not cause tempdb to be sized at 800M, but it happened during normal operation. Is there any jobs or processes to shrink and/or alter tempdb that you have overlooked?
October 20, 2005 at 8:17 am
I've long-since audited them, and there are no jobs that do this--directly. There might be hidden processes secreted away under unknown myriad layers of code; this is unlikely, but not impossible.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply