Shrink TempDB Error - 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

Shrink TempDB Error

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-02-08 : 14:35:49

Hello friends..
We are getting below error every day.we have job Shrink TempDB which runs every hour..but once a day we are getting this error.Is this error becasue of other jobs also running by that time which uses huge resource from DB..I cant understand why???.. can any body have an idea??

Executed as user: NT AUTHORITY\SYSTEM. ...97031). The PageId in the page header = (1:1056620160). [SQLSTATE 42000] (Error 8909) Table error: Object ID -720718983, index ID 0, page ID (1:297030). The PageId in the page header = (1:-1839155936). [SQLSTATE 42000] (Error 8909) Table error: Object ID -720718983, index ID 0, page ID (1:297029). The PageId in the page header = (1:1057229472). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID 0, page ID (1:297023). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID 0, page ID (1:297022). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) Table error: Object ID -720718983, index ID 0, page ID (1:297021). The PageId in the page header = (1:1056467424). [SQLSTATE 42000] (Error 8909) Table error: Object ID -720718983, index ID 0, page ID (1:297015). The PageId in the page header = (2:1071640160). [SQLSTATE 42000] (Error 8909) Table error: Object ID -7207189... The step failed.

Papillon

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-08 : 14:50:13

quote:

we have job Shrink TempDB

Why are you doing this!!!? This is creating performance problems for you.

Tara Kizer
aka tduggan

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-02-08 : 15:04:14

Because of huge transaction, temp db grows upto sometimes 15 to 16 GB in hour so we run this to cut it to normal...it is working fine for rest of the time but once or two days we getting this error...is there alternate solution??? but is this error is because of some tables engaged in transaction??

pls help me out

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-08 : 15:07:51

Why is it a problem that the tempdb grows? When you are shrinking it, other transactions attempting to access tempdb are being blocked. Plus the query that needs more tempdb space than its current size has to wait for the database to grow.

You'll have to wait for Paul Randal's response as to what the problem is.

But you should immediately stop shrinking the tempdb database every hour. It just isn't needed.

Tara Kizer
aka tduggan

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-02-08 : 15:28:20

Hi..
It grows on an average 7 to 8 GB...so we need to do this step every hour..can u list me few causes because of which it grows up..i just uses this in my job..

use tempdb
go
dbcc shrinkfile (tempdev, 10)
go

dbcc shrinkfile (templog, 10)
go

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-08 : 15:30:29

You don't need to shrink it. Your system needs that apparently. So make sure you have enough disk space to support whatever size your system needs. It won't just keep growing 7 to 8GB per day. It will level off.

There aren't any causes, per se. SQL Server needs to use tempdb for sorts and other operations. It's a scratch pad database.

Tara Kizer
aka tduggan

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-02-08 : 16:50:26

Hi..
can i enable my auto shrink option for tempdb...so that i will not run this job every hour...can u plz have any idea on this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-08 : 16:57:04

It is not recommended to turn that option on. It is not recommended that you shrink your databases. Hopefully, Paul Randal (Microsoft Engineer) will chime in soon to help you out.

Tara Kizer
aka tduggan

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-02-08 : 16:58:31

Ok..I will wait for him..any way thanx for ur help in this journey....

Hi Paul Randal!!
Today we got the same error message after failing job..
like...
Executed as user: NT AUTHORITY\SYSTEM. ...
The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909)
Table error: Object ID 0, index ID 0, page ID (1:198111).
The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909)
Table error: Object ID 0, index ID 0, page ID (1:198110). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909)
Table error: Object ID 0, index ID 0, page ID (1:198109). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909)
Table error: Object ID 0, index ID 0, page ID (1:198103). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909)
Table error: Object ID 0, index ID 0, page ID (1:198102). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909)
Table error: Object ID 0, index ID 0, page ID (1:198101). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909)
Table error: Object ID 0, index ID 0, page ID (1:198100). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8... The step failed....

right now i disable the job..but why this error message i got????
Plz help me out

T.I.A

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2006-02-10 : 18:33:05

Those errors are indicative of performing a shrink operation whilst tempdb is in use. Have a look at the bottom section of the KB article below (section headed Effects of Execution of DBCC SHRINKDATABASE or DBCCSHRINKFILE While Tempdb Is In Use). As Tara has already stated, you need to size tempdb appropriately for your environment. You also need to examine the large transactions that are causing it to expand and see if you can break them down into smaller batches.

KB307487 : How to shrink the tempdb database in SQL Server

HTH
Jasper Smith

X002548
Not Just a Number

15586 Posts

Posted - 2006-02-11 : 14:10:03

under2811,

Who told you you need to shrink tempdb? How much free space do you have on the drive?

Oh, btw, how big is your transaction log?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-02-12 : 11:47:59

I think you definitely need to check the SQL being run on your system, if tempdb is growing that fast. Cross-joins, anyone?

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-02-14 : 12:33:48

Sorry for the delay - I've been offline for a bit. Jasper and Tara are both correct - what you'e seeing is normal. Stop running shrink on your tempdb.

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

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-07-17 : 08:44:14

Same here got 4 gig tempdb should i do shrink command
The ,10 whats does that stand for 10 mg ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-17 : 08:48:00

see the link posted by Jasper

mr_mist
Grunnio

1870 Posts

Posted - 2006-07-17 : 10:32:22

How could this not be obvious by now?

You do not need to shrink the tempdb ever.

However, if you're making massive use of it, there's probably optimisations that could be made elsewhere in the server that would lessen the usage.

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources