October 28, 2008 at 10:30 am
We have an accounting software (not a good one!!!) using SQL Server 2000. When the users run reports, it creates a lot of temp table and it creates a lot of segmentation fault. We have to re-boot the server sometimes to clear the fault.
Is there anyway to check the database so we don't need to re-boot the server?
October 29, 2008 at 8:44 am
Just stumbled on this post but wondering where you see these faults? Curious.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 29, 2008 at 9:42 am
Can you post the entire error message please. SQL doesn't have a native error that refers to segmentation, so unless it's a user-defined error (error number > 50000), it's not coming from SQL
select * from sysmessages where error like '%segmentation%'
(0 row(s) affected)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2008 at 11:55 am
The problem is the system hang when people start running tons of reports using up the tempdb. I am not responsible for the system. The 'DBA' that normally takes care of the system is on a business trip. He told me if the system hang, it was the fragmentation problem in tempdb and told me to restart the server. That was not acceptable to me so I started to research the problem. I suggested to shrink the tempdb but he said it was no use.
That was what I saw in the sysmessages table. It was a mess.
- Pages Scanned................................: %lu
- Extents Scanned..............................: %lu
- Extent Switches..............................: %lu
- Avg. Pages per Extent........................: %3.1f
- Scan Density [Best Count:Actual Count].......: %4.2f%ls [%lu:%lu]
- Logical Scan Fragmentation ..................: %4.2f%ls
- Physical Scan Fragmentation .................: %4.2f%ls
- Extent Scan Fragmentation ...................: %4.2f%ls
- Avg. Bytes Free per Page.....................: %3.1f
- Avg. Page Density (full).....................: %4.2f%ls
Single page allocation %S_PGID in table %ls, object ID %d, index ID %d is not allocated in PFS page ID %S_PGID.
CHECKTABLE terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Database error: Page %S_PGID is marked with the wrong type in PFS page %S_PGID. PFS status 0x%x expected 0x%x.
initdata: No memory for kernel buffer hash table.
initdata: No memory for kernel buffers.
initdata: No memory for kernel locks.
initdata: Not enough memory for descriptor hash tables.
initdata: Not enough memory for descriptors.
initmaster: Could not allocate process status structure (PSS).
Could not allocate enough memory to initialize '%1'.
Could not dispatch SQL Server by Service Control Manager. Operating system error = %1.
Could not register Service Control Handler. Operating system error = %1.
sp_mergecompletecleanup cannot be executed before sp_mergepreparecleanup is executed. Use sp_mergepreparecleanup to initiate the first phase of merge meta data cleanup.
October 29, 2008 at 1:50 pm
Fragmentation's very different from segmentation which is what you initially posted.
Can you post the actual error you get, complete with all values? The list you gave vary from unimportant to absolutely critical. I'd need to see the exact message to offer any help.
How big's tempDB? How many files? What's the drive layout like (how many drives, what databases on what drives, what raid levels)?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2008 at 2:08 pm
The datafile of tempdb is in e drive and has 328 MB with 10% auto growth.
The transaction log is also in e drive and has 40 MB with 10% auth growth.
The master, model and msdb databases are in e drive.
The rest of other databases - data file is in G drive and log file is in F drive.
In the maint plan, it did not do any re-org of data or index pages, update statistics or remove unused space, would it cause a problem?
Since I did not do anything with this database, I had very limited info. I was just told if no one could access the application, re-boot the server. I thought that was ridiculous.
I was told the reason was when the users started generating a lot of reports, it created a lot of temp tables that caused the application to hang. I was not familiar with the application or the database.
Maybe I should not stick my noise to some place I don't belong.;)
October 29, 2008 at 7:23 pm
Is the "autoshrink" option turned on on TempDB??? If so, it must be turned off!.
Also, you need to figure out what the largest it will grow to, add 20 or 25% to that, and set the size to that! Autogrow should NOT be in percent... it should be in mega bytes because the default settings will cause 73 fragments just trying to get to 1 gig. I set my to autogrow by 250MB for the MDF and 100MB for the LDF...
Heh, I also set the starting size at 9 gig. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 30, 2008 at 1:23 am
Loner (10/29/2008)
In the maint plan, it did not do any re-org of data or index pages, update statistics or remove unused space, would it cause a problem?
No. It's TempDB, it's for temporary objects which shouldn't be around long enough to need any of that. In fact, I'm not sure you can put a maint plan on tempDB
Since I did not do anything with this database, I had very limited info. I was just told if no one could access the application, re-boot the server. I thought that was ridiculous.
Oh it is. Problem is, you haven't given us enough info.
I do have a theory though. Do you have enough rights to query sysprocesses? If so, when the large reports start to run, please query sysprocesses and look for all the waiting processes. Find out what the last wait type and wait resource are.
I suspect, you're going to find a lot of page latches on the resource 2:1:3. This indicates contention on TempDB's allocation units. There is a way to fix it, would like to confirm that it is the cause first..
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2008 at 6:05 am
The autoshrink is not turned on in tempdb but the unrestricted file growth is turned on. I am looking at the sysprocesses right now and hopefully I will get more info when the users start running reports.
October 30, 2008 at 7:01 am
When I query the sysprocesses, I saw a lot of PAGEIOLATCH_SH. What is that meant?
October 30, 2008 at 7:02 am
Loner (10/30/2008)
The autoshrink is not turned on in tempdb but the unrestricted file growth is turned on.
As it should be. What's the initial size for tempDB?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2008 at 7:05 am
Loner (10/30/2008)
When I query the sysprocesses, I saw a lot of PAGEIOLATCH_SH. What is that meant?
What were the wait resources?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2008 at 8:58 am
The size of tempdb is 328 MB.
The waitresource is 20:1:6751, 20:1:1050, 9:1:11506
October 30, 2008 at 9:45 am
Loner (10/30/2008)
The size of tempdb is 328 MB.
Yes, you said so earlier. What's the initial size for tempDB? The size it gets created at on a restart? (Should be the size shown when you check the DB property in Enterprise manager)
The waitresource is 20:1:6751, 20:1:1050, 9:1:11506
That has nothing to do with tempDB. Use the DB_Name function (or query sysdatabases) to see what databases have the IDs 9 and 20. It could be that the IO system is inadequate (that's usually a cause of a latch wait) and can't cope with the load.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 24, 2009 at 5:24 am
Why has this post been left dangling?
It was just getting interesting.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply