TEMPDB databse is running out of space

[somaiyatushar11]

From batch job when the TEMPDB..TABLE table getting updated , it is running for longer time and the TEMPDB databse is running out of space causing problem to the database. There are around 5,20,000 records in TEMPDB..TABLE.

Update statement is as follows:

UPDATE tempdb..TABLE
SET COLUMN1 = '0',
COLUMN2 = '0',
COLUMN3 = NULL,
COLUMN4 = NULL
WHERE COLUMN1 <> '0'
OR COLUMN2 <> '0'
OR COLUMN3 IS NOT NULL
OR COLUMN4 IS NOT NULL

 

[SQLBill]

Most likely problem....you don't allow the TEMPDB to grow fast enough.

In Enterprise Manager, right click on TEMPDB. Select Properties. Go to DATA tab. Is this set to allow AUTOGROW? Is it set to grow by percent or MB? How much is it set to grow by? I would make sure it's set to Autogrow and use MB. You will have to figure out how it should autogrow (mine is set for 1000MB at a time). Then do the same on the Log tab.

-SQLBill

 

[somaiyatushar11]

client will not increase the size...

 

[rsinj]

let's see, the table is growing, but the client won't increase the space? brilliant logic on the customer's part - what is the reasoning?

can I ask why the table is in tempdb to begin with and not in a user database? tempdb is used by the database for query processing. you size tempdb based on working space that you need to give the SQL Server for operating - think of swap file here. now, you are going and putting your own large working table(s) in there, reducing the amount of space that the SQL Server has to work with? bad decision by the designer and the client.

if the client won't allow you to increase the size of the tempdb and you are set on having this table in tempdb, you have limited options. hit the client up to do the right thing and move the table to an alternate, appropriately sized database. likewise, make sure your tempdb is sufficiently sized even without your batch job working table in it.

isn't it amazing, that in this day, when disk space goes for $1/GB, you run into this type of mentality where the client won't agree to allocate more disk space to you?

 

[SQLBill]

By the way, it's really a bad idea to use TEMPDB for permanent tables. TEMPDB is just that - a temporary database. If the server is shutdown (or services stopped and restarted), TEMPDB is DROPped, it's gone, then it is rebuilt from scratch. But any tables in TEMPDB are gone forever.

-SQLBill

 

[rsinj]

SQLBill, I would gather that anyone storing tables in tempdb is aware of the issue you raise and they are putting the table(s) in tempdb for that reason. a batch job is a good candidate for something like this - assuming what I've indicated previously - tempdb is sized appropriately for the table in addition to any working requirements of the SQL Server.

In one of my applications, I use a table in tempdb - to maintain real-time database transaction log and database dump status in a multi-node environment - but it's only a couple records. the jobs check for the existence of the table in tempdb before writing to it. if the table doesn't exist, it creates it on the fly knowing that it will disappear on reboots.

 

[SQLBill]

rsinj,

I agree MOST people are aware of that, but I met someone who was told that TEMPDB was the only place they could put their database and was very upset when it was lost due to the SQL Server being rebooted. So I just like to point that out in the remote possibility that it could happen again.

-SQLBill