Tempdb giving up
-
I've recently been receiving the following messages when checking the tempdb using 'dbcc checkdb(tempdb)' :
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:193) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
It appears to happen after a certain period of time. A SQL Serveer reboot solves the problem, but I'm not really keen on repeatadly rebooting the server, as it does contain a lot of central databases used by our worldwide affiliates.
The BOL does tell you that '..yes you have got a problem...', but does not give you any ideas how to fix the problem.
You can't turn the tempdb on to single user/dbo only mode, because of the nature of the database, whiche means the the REPAIR_FAST, etc. options will not work.
Any ideas how I could go about fixing the problem? I don't really want to speculate on this matter.
Before I forget:
- SQL 7.0 Service Pack 3
- Windows NT 4.0 SP6.0a
- C: System [Disk 0]
- D: Data (tempdb, msdb, etc...)[Disk 1]
- E: Tran Logs [Disk 0]
- F: Backup [Disk 2]
- RAID 1 on all disks
Thanks, hot2use
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find. -
You don't have to dbcc checkdb to tempdb. It stored only temporary data.
Form BOL.
"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."
-
I agree with Allen
Also most of the Tempdb issues like this were fixed in SP3, but strange you already have SP3
http://support.microsoft.com/default.aspx?scid=kb;en-us;278363
http://support.microsoft.com/default.aspx?scid=kb;en-us;278366
Shas3
-
Ok.
I'd agree on a normal basis, but other databases are timing out because of the problems with the tempdb.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find. -
What are your issues on time-out? Maybe due to block in tempdb?
-
Yes, that's basically what is happening.
I've got one SPID (from any individual database on the server) that connects to the tempdb and creates up to 100 database, table and/or index processes, one of which just locks. This then causes the connections/processes on the productive database to hang and the application locks up for the whole company.
I probably should add that our main SQL Server is at the end of its' life span and trying to run a Profiler almost always produces useless data. As soon as the load gets interesting I receive the message that the Profiler can't trace the information due to lack of ressources.
I mean what would I have to do if the tempdb and the database in question were running on a dedicated server and I'd still be receiving the error messages? You can't just keep rebooting the server to fix the tempdb...
Thanks for any pushes, hints, jabs in the right direction.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find. -
quote:
I've got one SPID (from any individual database on the server) that connects to the tempdb and creates up to 100 database, table and/or index processes, one of which just locks. This then causes the connections/processes on the productive database to hang and the application locks up for the whole company.Can you describe more about above process? If those tables/indexes are created within a transaction, some system tables (syscolumns, sysindexes, syscomments) will be locked to preventing others from executing the query, greatly hurting concurrency and performance.
-
The following link may be interesting: http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B328551
It looks like you can get some blocking within TempDB if TempDB has a lot of pressure from heavy workfile and/or temporary table create delete activity. Your problem may be different from that documented, but may not.
Beware if you apply the -T1118 flag to stop the use of shared extents, as documented in the link, you also have to apply a further fix (also documented) if you run SQL7.
All information provided is a personal opinion that may not match reality.
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
-
Hi Allen & Ed
Seems like Ed's jab in the right direction would be my solution (except SQL 7.0 doesn't need the fix in step 1, that is solely intended for SQL 2000 ).
We've got around 40 small and medium sized databases (max. 1GB; min 20MB) on that one server and they all generate quite a lot of requests with order by statements and statements wrapped in transactions.
It's not easy to pin it down to just one database. There's one small one at 40 MB, which nearly everybody at our company uses (tel. book) on a regular basis and that tends to mess up the tempdb sometimes. The next time it is the 500 MB database containing other information.
I think I will be better of waiting another week or so and then transfering the databases to their new home. (Dual 2.3 GHz, 2GB RAM, single disk for tempdb)
The description in the KB-article Ed mentioned points in the general direction of your-server-is-to-slow. (now using single 800 MHz, 1GB RAM machine)
I'll keep the article in mind and let you know how things turned out.
Thanks for the hints and remarks.
Allen: Sorry I can't give you any detailed infromation. No time at the moment to Profile the whole server and the server wouldn't give me any detailed information any way, with its' current workload.
As I said, there will be a definite feedback in this thread to let you know how things turned out. If I forget, then feel free to bug me.
Thanks again
John
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply