SQL Server Performance Forum – Threads Archive

Tempdb becoming full and never cleaned up automati

Hi all, I am running SQL Server 7.0 Standard Edition with SP4. I have a strange situation whereby the tempdb becomes full and never frees up the space after the user logs out and fills up the whole drive as the auto grow by 10% is on. Every time, I have to re-boot the SQLServer to clean up the tempdb. We are using the tool called "Business Objects" to generate some reports from the database, and this fills up the tempdb. I have also searched KB for any known bugs for SQL 7.0 / SP4 (Stad Edition ) but no use. Has anyone encountered this before ? If yes, is there any solution to reduce the tempdb without re-booting the SQLServer ? Is "shrink database" applicable in this situation ? Thanks for your help !
Kanthi

could you try checking what tables are in tempdb, by selecting from sysobjects…? Are there non-temporary tables being created and not destroyed? Cheers
Twan

Hi, There seems to be no other objects except system objects. After every report generation the tempdb grows up to 1.5GB in size. Can I try shrink option ? Thanks select * from sysobjects sysobjects1S 125-214748364599001998-11-13 03:00:19.1870963S 111301998-11-13 03:00:19.1870000020
sysindexes2S 129-214748364367001998-11-13 03:00:19.1870643S 1827301998-11-13 03:00:19.1870000020
syscolumns3S 128-214748364597001998-11-13 03:00:19.1870961S 111301998-11-13 03:00:19.1870000020
systypes4S 118-214748364596001998-11-13 03:00:19.1870960S 111301998-11-13 03:00:19.1870000020
syscomments6S 110-214748364780001998-11-13 03:00:19.1870800S 18101998-11-13 03:00:19.1870000020
sysfiles18S 14-21474836480001998-11-13 03:00:19.187000S 16501998-11-13 03:00:19.1870000020
syspermissions9S 111-214748364716001998-11-13 03:00:19.1870160S 18101998-11-13 03:00:19.1870000020
sysusers10S 121-2147483645112001998-11-13 03:00:19.18701120S 111301998-11-13 03:00:19.1870000020
sysdepends12S 110-214748364780001998-11-13 03:00:19.1870800S 18101998-11-13 03:00:19.1870000020
sysreferences14S 141-2147483645112001998-11-13 03:00:19.18701120S 111301998-11-13 03:00:19.1870000020
sysfulltextcatalogs19S 14-214748364532001998-11-13 03:00:19.1870320S 111301998-11-13 03:00:19.1870000020
sysindexkeys20S 14-21453864960002003-03-18 13:59:34.670000S 1108902003-03-18 13:59:34.6700000020
sysforeignkeys21S 16-21453864960002003-03-18 13:59:34.687000S 1108902003-03-18 13:59:34.6870000020
sysmembers22S 12-21453864960002003-03-18 13:59:34.700000S 1108902003-03-18 13:59:34.7000000020
sysprotects23S 16-21453864960002003-03-18 13:59:34.717000S 1108902003-03-18 13:59:34.7170000020
sysfiles95S 19-21453864960002003-03-18 13:59:34.733000S 1108902003-03-18 13:59:34.7330000020
sysfilegroups96S 14-214748364532001998-11-13 03:00:19.1870320S 111301998-11-13 03:00:19.1870000020
sysallocations98S 15-21474836470001998-11-13 03:00:19.187000S 18101998-11-13 03:00:19.1870000020
REFERENTIAL_CONSTRAINTS5575058V 39-21474836480001998-11-13 03:07:16.467000V 0201998-11-13 03:07:16.4670000020
CHECK_CONSTRAINTS21575115V 34-21474836480001998-11-13 03:07:17.217000V 0201998-11-13 03:07:17.2170000020
CONSTRAINT_TABLE_USAGE37575172V 36-21474836480001998-11-13 03:07:17.967000V 0201998-11-13 03:07:17.9670000020
CONSTRAINT_COLUMN_USAGE53575229V 37-21474836480001998-11-13 03:07:18.750000V 0201998-11-13 03:07:18.7500000020
VIEWS69575286V 36-21474836480001998-11-13 03:07:19.467000V 0201998-11-13 03:07:19.4670000020
VIEW_TABLE_USAGE85575343V 36-21474836480001998-11-13 03:07:20.217000V 0201998-11-13 03:07:20.2170000020
VIEW_COLUMN_USAGE101575400V 37-21474836480001998-11-13 03:07:20.967000V 0201998-11-13 03:07:20.9670000020
syssegments117575457V 13-21474836310002003-03-18 13:59:34.763000V 0202003-03-18 13:59:34.7630000020
sysconstraints133575514V 17-21474836310002003-03-18 13:59:34.780000V 0202003-03-18 13:59:34.7800000020
sysalternates149575571V 12-21474836310002003-03-18 13:59:34.797000V 0202003-03-18 13:59:34.7970000020
KEY_COLUMN_USAGE165575628V 38-21474836310002003-03-18 14:00:33.780000V 0202003-03-18 14:00:33.7800000020
SCHEMATA1993058136V 36-21474836480001998-11-13 03:07:09.013000V 0201998-11-13 03:07:09.0130000020
TABLES2009058193V 34-21474836480001998-11-13 03:07:09.733000V 0201998-11-13 03:07:09.7330000020
TABLE_CONSTRAINTS2025058250V 39-21474836480001998-11-13 03:07:10.467000V 0201998-11-13 03:07:10.4670000020
TABLE_PRIVILEGES2041058307V 37-21474836480001998-11-13 03:07:11.217000V 0201998-11-13 03:07:11.2170000020
COLUMNS2057058364V 323-21474836480001998-11-13 03:07:12.013000V 0201998-11-13 03:07:12.0130000020
COLUMN_DOMAIN_USAGE2073058421V 37-21474836480001998-11-13 03:07:12.717000V 0201998-11-13 03:07:12.7170000020
COLUMN_PRIVILEGES2089058478V 38-21474836480001998-11-13 03:07:13.483000V 0201998-11-13 03:07:13.4830000020
DOMAINS2105058535V 317-21474836480001998-11-13 03:07:14.233000V 0201998-11-13 03:07:14.2330000020
DOMAIN_CONSTRAINTS2121058592V 38-21474836480001998-11-13 03:07:14.967000V 0201998-11-13 03:07:14.9670000020

It should be the tool that is trying to use lot of tempdb by means of temp tables etc. Yes you can try shrinking the tempdb log intermittently while running this tool. You can also add an additional log file to it so that it can expand into another disk and can then shirnk it later.

Each time you restart the sqlserver service TempDB is rebuilt, but if the settings are still the
same for the size it will just recreate it at the same size. You can either use DBCC SHRINKFILE and usually it will shrink then or ALTER DATABASE MODIFY FILE specifying the new file size(s) and then restart it. Please refer BooksOnLine for exact syntax. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

This is now a ‘feature’ of SQL.
From books online tempdb Database
The tempdb system database expands automatically as needed. For example, a reporting application may be submitted that needs a lot of space in tempdb. Rather than failing with an out-of-space error, tempdb grows automatically to the size needed to support the report. SQL Server 7.0 reinitializes tempdb to the configured size the next time the server is started. In otherwords, tempdb does not shrink after you delete tables from it. For those who’s usage of tempdb is steady, this is a good thing. But if you have one or two processes that will fill up your tempdb drive that run once a day or less, then this feature is a pain in the neck. But you dont need to restart SQL to shrink it. What I’ve done in the past and is simple to impliment is to shrink the database on some kind of schedule. If you have one monster process, then add the shrink to the end of that process. If you have a several regular daily processes that will fill it, shrink once a day (during a quiet period). If you impliment a scheduled shrink, monitor the size before/after shrinking. Forcing it to grow every process, every time may mean that you should allocate more to tempdb to start. Also, double check the processes that are filling tempdb to make sure they need to be doing the things that fill it (temp tables, sorts, groups etc). Chris

Hi all, Thanks for your prompt response. My problem here is it fills the data segment and not the log segment. That is tempdb.MDF grows to 7GB at the end of the long process and never gets released and tempdb.LDF stays in couple of MBs. Can I try shrinking of data segment as well?? Thanks !

Yes try to shrink it if not may try a restart of SQL services. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

]]>