September 10, 2003 at 5:48 am
I have TEMPDB set to 1.8gb for the data and 300mb for the log. Even with that setting it fills and generates an error 17. I've run the SP that shows TEMPDB size but it always shows it to be 99% empty. The compatibility level is set to 80. The recovery method is SIMPLE. I have "monitored" the server and can see what SQL query is causing the problem but don't know why.
So, how can I tell exactly what's getting filled (data or log)? What is a "normal" size for TEMPDB.
One clue, the query writes to TEMP tables.
September 10, 2003 at 8:17 am
I don't feel "strong" enough to give you advice about what's causing the problem and why, but I can at least answer one of the questions: Size of tempdb varies with what is happening in the DB - inserts, updates, selects etc. We have a production DB that is almost 90GB, and the associated tempdb data (at the moment) 2GB; tempdb log 1 GB. Of course sometimes it grows much bigger... but this is so to say "normal" value for our system.
Cheers, Vladan
September 10, 2003 at 8:38 am
quote:
Even with that setting it fills and generates an error 17.
Can you post the completed error messages?
September 10, 2003 at 11:26 am
Vladan, The database it fails on it about 5GB in size. There are about 15 other databases that total maybe 20GB. I would think 1.8GB for the data would be more than enough.
Allen_Cui, Here's the error message....
DESCRIPTION:Error: 1105, Severity: 17, State: 2
Could not allocate space for object '#olqtmp1____________________________________________________________________________________________________________000000005909' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.
September 10, 2003 at 11:42 am
Dod you set the TEMPDB to auto grow? Do you have free spaces in the disks the TEMPDB located?
Edited by - allen_cui on 09/10/2003 11:42:32 AM
September 11, 2003 at 12:38 am
We have sometimes the same kind of msg on one of our production servers.
Msg:
Could not allocate space for object '(SYSTEM table id: -1016764113)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full..
nevertheless the tempdb has the following db settings:
name db_size owner dbid created status compatibility_level
------ -------------------------------------------------------------
tempdb 2351.06 MB sa 2 Aug 14 2003 Status=ONLINE, Updateability=READ_WRITE,
UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=Latin1_General_CI_AI,
SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics 80
name fileid filename size maxsize growth usage
-------------------------------------------------------------- ------------------ ------------------ ---------
tempdev 1 s:\mssql8\MSSQL\data\tempdb.mdf 1076288 KB Unlimited 102400 KB data only
templog 2 t:\mssql8\MSSQL\Tlog\templog.ldf 1331200 KB Unlimited 102400 KB log only
I don't know the cause of this.
September 11, 2003 at 1:58 am
jscii, it looks like it should be enough, but there are operations which may require more space to be completed... I remember we got the same error a few times, although there seemed to be lots of free space - especially while we were importing the data during migration from our old system, and also during massive export of data for reporting, which required to join several huge tables (this could be your situation).
First thing to check you'll find in Allen's post - is tempdb Autogrow enabled and do you have enough free space on the disks? Also, make sure that the tempdb growth is set to Unrestricted (or high enough, say 15GB).
Well, that all is nice, but as it seems that you're getting that problem with one query only (is that so?), you probably could avoid it by tuning the query... This includes index tuning on used tables and possibly splitting the query into several separate statements (not knowing what the query does, I'm merely guessing what could need adjustment).
September 11, 2003 at 6:49 am
Have you tried DBCC OPENTRAN to see if there are any open transactions? This would prevent the log from being truncated. If the open transaction is near the beginning of the log, SQL Server can't use all of the log space that is showing available once it wraps around.
September 11, 2003 at 10:19 am
I am not sure which one, but I think DBCC INDEXDEFRAG or DBCC DBREINDEX use TempDB to reorganize the data pages. If you rebuild/defrag your indexes in the production database with a maintenance plan or use the DBCC commands, you may want to pick a few tables to have defragged at a time vs. do them all at the same time.
Greg
September 11, 2003 at 8:28 pm
If you use re-index, on the run set dboption to bulk_logged and at end set it to original setting.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply