SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 tempdb growing
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

May82
Starting Member

Malaysia
7 Posts

Posted - 04/09/2006 :  22:48:19  Show Profile  Reply with Quote
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

United Kingdom
22431 Posts

Posted - 04/10/2006 :  01:53:52  Show Profile  Reply with Quote
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
Go to Top of Page

May82
Starting Member

Malaysia
7 Posts

Posted - 04/10/2006 :  02:31:51  Show Profile  Reply with Quote
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.
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 04/10/2006 :  03:46:02  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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...
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 04/10/2006 :  04:55:02  Show Profile  Reply with Quote
"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

Edited by - Kristen on 04/10/2006 04:55:15
Go to Top of Page

May82
Starting Member

Malaysia
7 Posts

Posted - 04/10/2006 :  05:15:01  Show Profile  Reply with Quote
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 =)
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 04/10/2006 :  05:36:04  Show Profile  Send jen a Yahoo! Message  Reply with Quote

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...
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 04/10/2006 :  06:10:59  Show Profile  Reply with Quote
"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
Go to Top of Page

May82
Starting Member

Malaysia
7 Posts

Posted - 04/10/2006 :  06:26:13  Show Profile  Reply with Quote
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 =)
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 04/10/2006 :  08:55:00  Show Profile  Reply with Quote
"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
Go to Top of Page

May82
Starting Member

Malaysia
7 Posts

Posted - 04/10/2006 :  09:04:30  Show Profile  Reply with Quote
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 =)
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 04/10/2006 :  09:34:51  Show Profile  Send jen a Yahoo! Message  Reply with Quote
ok, now you're on the right forum.. Paul, take it away...



--------------------
keeping it simple...
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 04/10/2006 :  11:22:40  Show Profile  Reply with Quote
"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
Go to Top of Page

May82
Starting Member

Malaysia
7 Posts

Posted - 04/10/2006 :  22:01:38  Show Profile  Reply with Quote
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 =)
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 04/11/2006 :  01:12:06  Show Profile  Reply with Quote
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
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 04/17/2006 :  17:24:31  Show Profile  Visit paulrandal's Homepage  Reply with Quote
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.)
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 04/18/2006 :  00:42:58  Show Profile  Reply with Quote
"did you get any reply back from your last question"

No, I guess that May82 solved the problem somehow.

Kristen
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 04/18/2006 :  01:39:48  Show Profile  Send jen a Yahoo! Message  Reply with Quote
rebuilt perhaps?

--------------------
keeping it simple...
Go to Top of Page

ansz5
Starting Member

4 Posts

Posted - 04/28/2008 :  08:15:26  Show Profile  Reply with Quote
I cannot use tempdb for any of the commands :

GO
EXEC sp_spaceused
go

The server is reporting blocking.

Please suggest what to do.
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 04/28/2008 :  22:24:58  Show Profile  Reply with Quote
Blocked by apps? What do you really like to get?
Go to Top of Page

ansz5
Starting Member

4 Posts

Posted - 04/29/2008 :  02:47:39  Show Profile  Reply with Quote
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.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000