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
 Shrink TempDB Error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

under2811
Constraint Violating Yak Guru

India
356 Posts

Posted - 02/08/2006 :  14:35:49  Show Profile  Reply with Quote
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

USA
37167 Posts

Posted - 02/08/2006 :  14:50:13  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:

we have job Shrink TempDB



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

Tara Kizer
aka tduggan
Go to Top of Page

under2811
Constraint Violating Yak Guru

India
356 Posts

Posted - 02/08/2006 :  15:04:14  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37167 Posts

Posted - 02/08/2006 :  15:07:51  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 02/08/2006 15:08:28
Go to Top of Page

under2811
Constraint Violating Yak Guru

India
356 Posts

Posted - 02/08/2006 :  15:28:20  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37167 Posts

Posted - 02/08/2006 :  15:30:29  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

under2811
Constraint Violating Yak Guru

India
356 Posts

Posted - 02/08/2006 :  16:50:26  Show Profile  Reply with Quote
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?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37167 Posts

Posted - 02/08/2006 :  16:57:04  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

under2811
Constraint Violating Yak Guru

India
356 Posts

Posted - 02/08/2006 :  16:58:31  Show Profile  Reply with Quote
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

Edited by - under2811 on 02/10/2006 15:46:45
Go to Top of Page

jasper_smith
SQL Server MVP & SQLTeam MVY

United Kingdom
846 Posts

Posted - 02/10/2006 :  18:33:05  Show Profile  Visit jasper_smith's Homepage  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/11/2006 :  14:10:03  Show Profile  Reply with Quote
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
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 02/12/2006 :  11:47:59  Show Profile  Reply with Quote
I think you definitely need to check the SQL being run on your system, if tempdb is growing that fast. Cross-joins, anyone?
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 02/14/2006 :  12:33:48  Show Profile  Visit paulrandal's Homepage  Reply with Quote
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.)
Go to Top of Page

TRACEYSQL
Aged Yak Warrior

593 Posts

Posted - 07/17/2006 :  08:44:14  Show Profile  Send TRACEYSQL an ICQ Message  Reply with Quote
Same here got 4 gig tempdb should i do shrink command
The ,10 whats does that stand for 10 mg ?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17658 Posts

Posted - 07/17/2006 :  08:48:00  Show Profile  Reply with Quote
see the link posted by Jasper


KH

Go to Top of Page

mr_mist
Grunnio

United Kingdom
1870 Posts

Posted - 07/17/2006 :  10:32:22  Show Profile  Visit mr_mist's Homepage  Reply with Quote
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. :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 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