February 8, 2006 at 12:40 pm
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.
Regards,
Papillon
February 8, 2006 at 1:08 pm
Hello ppls
I know there is performance issue by running this job every hour but...
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
Regards,
Papillon
February 8, 2006 at 2:53 pm
Hi..
can i enable my auto shrink option for tempdb...so that i will not run this job every hour...can any body plz have any idea on this?
Regards,
Papillon
February 9, 2006 at 3:25 am
The best advice is: stop shrinking tempdb!!
You should plan to have a Tempdb that is large enough for your workload. If it needs 20GB then plan for 20GB. On our Data Warehouse system we need a Tempdb of 160GB to allow our ETL process to run. (OK the ETL code could be improved, but it is a 3-rd party development...)
Shrinking a database should only be done where you expect a long-term reduction in space requirements. If you expect the DB to grow to its original size within 3 months then leave it alone. Every shrink operation will cause Windows file fragmentation which will harm your performance. The Windows file fragmentation can only be cured by a drive defrag - an index rebuild has no impact on this.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 12, 2008 at 8:08 am
Any idea for the same error with SQL 2005?
June 12, 2008 at 1:52 pm
Your posting in the wrong forum. For quicker, more accurate responses, post in SQL2005. See Ed's post. Quit shrinking the database. Size it properly with enough free disk remaining for future or unexpected growth. A lot of work takes place in there. When you restart SQL it'll basically rebuild a new tempdb for you.
-- You can't be late until you show up.
June 12, 2008 at 2:30 pm
See http://support.microsoft.com/kb/307487#6 for why you should not shrink tempdb while in-use:
Effects of Execution of DBCC SHRINKDATABASE or DBCCSHRINKFILE While Tempdb Is In Use
If tempdb is in use and you attempt to shrink it by using the DBCC SHRINKDATABASE or DBCC SHRINKFILE commands, you may receive multiple consistency errors similar to the following type and the shrink operation may fail:
Server: Msg 2501, Level 16, State 1, Line 1 Could not find table named '1525580473'. Check sysobjects.
-or-
Server: Msg 8909, Level 16, State 1, Line 0 Table Corrupt: Object ID 1, index ID 0, page ID %S_PGID. The PageId in the page header = %S_PGID.
Although error 2501 may not be indicative of any corruption in tempdb, it causes the shrink operation to fail. On the other hand, error 8909 could indicate corruption in the tempdb database. Restart SQL Server to re-create tempdb and clean up the consistency errors. However, keep in mind that there could be other reasons for physical data corruption errors like error 8909 and those include input/output subsystem problems.
SQL = Scarcely Qualifies as a Language
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply