tempdb growing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

tempdb growing

Author

Topic

May82
Starting Member

7 Posts

Posted - 2006-04-09 : 22:48:19

Hi there,
My tempdb is growing from its normal size of 800MB to 2GB.
I've been shrinking it using dbcc shinkfile/ dbcc shrinkdatabase.
Everytime I run the command, immediately it says that the execution is completed and successful. However, when I checked the disk space, it remained the same, as though no shrinking is done. Can anyone help?
Also, it was published that restarting the SQL server can re-create tempdb from scratch. I've tried it too, the tempdb just wont go back to its normal allocation. My constraint is limited disk space, would appreciate any good samaritan to give me some help here.
Thanks in advance!

Kristen
Test

22859 Posts

Posted - 2006-04-10 : 01:53:52

Its growing because it needed the extra room. If it is going to need that much room every day there is no point trying to make it smaller! If it was a one-off event then fine.

Note that Shrinking TEMPDB whilst other user are using it you may well cause corruption of the database, so only do that in single user mode.

For details on shrinking TEMPDB see: http://support.microsoft.com/default.aspx/kb/307487

Kristen

May82
Starting Member

7 Posts

Posted - 2006-04-10 : 02:31:51

But all the while the size of the tempdb was around 800MB, then it suddenly shoot up to 2GB. But I found that the tempdb in the server is not responding to any shrinking actions that I have triggered. Why is that so? I'm afraid that the tempdb will continue until all the disk space is used up.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-10 : 03:46:02

you will not be able to control the tempdb growth like the other databases

you need to find out what's making it grow to 2gb, if there are batch jobs, then you may need to schedule them evenly across the day,
if there are transactions requesting for very large datasets, you may want to advise your developers to divide the results
otherwise, expand your hd to accomodate the growth,

once the transactions have been committed, you will see that it will shrink back

HTH

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2006-04-10 : 04:55:02

"I found that the tempdb in the server is not responding to any shrinking actions that I have triggered"

Did you try the actions in the link I posted?

Kristen

May82
Starting Member

7 Posts

Posted - 2006-04-10 : 05:15:01

I've tried running commands like dbcc shrinkfile and dbcc shrinkdatabase in query analyzer.
It prompted that DBCC execution completed. But when I checked the disk space, its still the same.

Newbie =)

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-10 : 05:36:04

quote:

tempdb holds all temporary tables and temporary stored procedures. It also fills any other temporary storage needs such as work tables generated by SQL Server. tempdb is a global resource; the temporary tables and stored procedures for all users connected to the system are stored there. tempdb is re-created every time SQL Server is started so the system starts with a clean copy of the database. Because temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down, there is never anything in tempdb to be saved from one session of SQL Server to another.

By default, tempdb autogrows as needed while SQL Server is running. Unlike other databases, however, it is reset to its initial size each time the database engine is started. If the size defined for tempdb is small, part of your system processing load may be taken up with autogrowing tempdb to the size needed to support your workload each time to restart SQL Server. You can avoid this overhead by using ALTER DATABASE to increase the size of tempdb.

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2006-04-10 : 06:10:59

"I've tried running commands like dbcc shrinkfile and dbcc shrinkdatabase in query analyzer"

Did you use them in the way that the article I provided a link to explicitly states?

Sorry to be blunt, but that isn't obvious from your reply.

If you read the article, and carefully followed its instructions, and it didn't work I can try to give you some additional help.

If you didn't do that then it would be a bit pointless me trying to help further, since that will probably work just fine!

Kristen

May82
Starting Member

7 Posts

Posted - 2006-04-10 : 06:26:13

Sorry for not being detailed in my reply.
Yes, I did according to the article but to no avali. Also, one funny thing is, when the SQL server is restarted, it is supposed to recreate tempdb again. But it didn't work for my case too. It seems, whatever commands, I tried, the tempdb is not responding to any of it.

Newbie =)

Kristen
Test

22859 Posts

Posted - 2006-04-10 : 08:55:00

"when the SQL server is restarted, it is supposed to recreate tempdb again"

I wondered about that too. I suppose its possible it doesn't delete the existing file, just "initialise it" instead - so the size would be unchanged. However, when I stop/start SQL here TEMPDB is initialised to the minimum filesize

Is there any chance that the file you are looking at is "owned by" a different instance of SQL Server from the one you are working with to change its size?

In Enterprise Manager : Databases - right click "tempdb" and choose Properties.

Does [Data files] and [Transaction log] show the path/filename you are expecting?

It may also be that the Size of TEMPDB has become set. I think you can review this with:

use tempdb
GO
sp_helpfile
go

and you should be able to reset it using:


use master
go
alter database tempdb
   modify file (name=tempdev, size=1000)
go
alter database tempdb
   modify file (name=templog, size=1000)

Kristen

May82
Starting Member

7 Posts

Posted - 2006-04-10 : 09:04:30

Thanks for your reply. Just tried to run dbcc shrinkfile again. This time it prompted a long list of error messages like
Server: Msg 8909, Level 16, State 1, Line 0
Table Corrupt: Object ID 0, index ID 0, page ID (1:354047). The PageId in the page header = (0:0).
Does it mean that the tables in tempdb is corrupted? How can i rebuild tempdb? Thanks once again.

Newbie =)

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-10 : 09:34:51

ok, now you're on the right forum.. Paul, take it away...

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2006-04-10 : 11:22:40

"Does it mean that the tables in tempdb is corrupted?"

That's why I specifically said earlier "Note that Shrinking TEMPDB whilst other user are using it you may well cause corruption of the database, so only do that in single user mode"

This is clearly covered in the article I pointed you at ....

Did you check the properties of the TEMPDB database on the server instance to be certain that you are indeed looking at the correct file for that server?

Kristen

May82
Starting Member

7 Posts

Posted - 2006-04-10 : 22:01:38

Yes, already checked the properties of tempdb database. Is there a way to rebuild it?
The thing is, even I stopped & restarted the SQL server wouldn't work in recreating the tempdb.

Newbie =)

Kristen
Test

22859 Posts

Posted - 2006-04-11 : 01:12:06

Sorry don't know the answer to that, but I can tell you that that is not the behaviour I see here, so sounds like there is something screwy / unusual at your end.

What does

SELECT @@VERSION

give you pls (should be about 4 lines of version/copyright stuff)?

Kristen

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-04-17 : 17:24:31

Kristen - did you get any reply back from your last question? I'm curious why TEMPDB wasn't getting rebuilt - there are undocumented ways to prevent it but I'd be surprised if they were in use here.

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

Kristen
Test

22859 Posts

Posted - 2006-04-18 : 00:42:58

"did you get any reply back from your last question"

No, I guess that May82 solved the problem somehow.

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-18 : 01:39:48

rebuilt perhaps?

--------------------
keeping it simple...

ansz5
Starting Member

4 Posts

Posted - 2008-04-28 : 08:15:26

I cannot use tempdb for any of the commands :

GO
EXEC sp_spaceused
go

The server is reporting blocking.

Please suggest what to do.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-28 : 22:24:58

Blocked by apps? What do you really like to get?

ansz5
Starting Member

4 Posts

Posted - 2008-04-29 : 02:47:39

We have a daily monitoring tool for SQL Servers. It cannot fetch details for tempdb. E.g. one of the queries fired is :

select b.groupname,sum(a.used)
from sysindexes a, sysfilegroups b
where a.groupid =* b.groupid
and (a.indid = 0 or a.indid = 1 or a.indid = 255)
group by b.groupname

It causes blocking. Next tiem (after 5 mins) when the same query is fired...it piles up the lock requests and tempdb does not respond to any request.

Please note that the query works fine,when used with NOLOCK hint.
ALso, the same query is working fine with all other databases on the server.

Please suggest.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources