Presently, we have allocated 600MB to tempdb and when I run the sp_spaceused command for tempdb I notice that the unallocated space is a negative number. I have not received any errors in the error log with regards to expanding the segment because segment is full. I also tried running the command sp_spaceused @updateusage='true' and still received the same negative number. We eventually ended up having to restart SQL Server and this cleared things, however I am still concerned about this issue. Does anyone know if this just a bug in SQL 6.5 or something else?
Our client's database is hosted by a third party in a clustered SQL Server 2000 environment. Things were running swimmingly for 18 months when, in January, some inconsistencies started to appear. The first symptoms were three records in one of the tables that look corrupted; i.e. they contained dates for the years 2079 and 6619! We simply removed the data that hd been bulk loaded that day and re-bcpd it and were able to continue without issue.
We never quite got to the bottom of the exact cause of this corruption and were happy to leave it be until, on 30 June, a DBCC after a failure gave the following (trimmed) results.
DBCC results for 'DealPeriodParmsFlexShadow'. There are 0 rows in 1 pages for object 'DealPeriodParmsFlexShadow'. DBCC results for 'DealPeriodParmsShadow'. Server: Msg 8928, Level 16, State 1, Line 1 Object ID 1383012008, index ID 0: Page (1:128399) could not be processed. See other errors for details. There are 127575 rows in 1057 pages for object 'DealPeriodParmsShadow'. DBCC results for 'recvspre17'. There are 97593 rows in 3367 pages for object 'recvspre17'. DBCC results for 'DealPeriodParmsSubShadow'. There are 0 rows in 1 pages for object 'DealPeriodParmsSubShadow'. DBCC results for 'System'. There are 22 rows in 1 pages for object 'System'. DBCC results for 'Recvs'. There are 4980591 rows in 185222 pages for object 'Recvs'. CHECKDB found 0 allocation errors and 1 consistency errors in table 'Recvs' (object ID 1383012008). DBCC results for 'DealPeriodsShadow'. There are 1182 rows in 32 pages for object 'DealPeriodsShadow'. DBCC results for 'DealsShadow'. There are 0 rows in 1 pages for object 'DealsShadow'.
Running DBCC CHECKDB a second time seemed to imply that the issue was fixed. The application crashed within 45 minutes again. Manually running the SP that was executing at the time of the crash resulted in the following message: Server: Msg 21, Level 21, State 1, Procedure sp_FlagGalpIneligibleOverdueObligors, Line 109 Warning: Fatal error 644 occurred at Jun 30 2005 4:23PM
Running the same SP immediately again resulted in a new message: Server: Msg 21, Level 20, State 1, Procedure sp_FlagGalpIneligibleOverdueObligors, Line 109 Warning: Fatal error 3624 occurred at Jun 30 2005 4:31PM Location: scanrid.cpp:321 Expression: m_len != 0 SPID: 52 Process ID: 3756
A DBCC CHECKDB resulted in "CHECKDB found 0 allocation errors and 68 consistency errors in database" with messages such as: Server: Msg 8952, Level 16, State 1, Line 1 Table error: Database 'dbCresta', index 'Recvs.XIEDealPeriodTypeStatus' (ID 1383012008) (index ID 2). Extra or invalid key for the keys: Server: Msg 8956, Level 16, State 1, Line 1 Index row (1:245:193) with values (DealId = 2 and PeriodId = 469 and Type = 'R' and Status = 'I') points to the data row identified by (RID = (1:663407:10)). Server: Msg 8952, Level 16, State 1, Line 1 Table error: Database 'dbCresta', index 'Recvs.XIEDealPeriodTypeStatus' (ID 1383012008) (index ID 2). Extra or invalid key for the keys: Server: Msg 8956, Level 16, State 1, Line 1 Index row (1:245:194) with values (DealId = 2 and PeriodId = 469 and Type = 'R' and Status = 'I') points to the data row identified by (RID = (1:663407:11)). Server: Msg 8952, Level 16, State 1, Line 1 Table error: Database 'dbCresta', index 'Recvs.XIEDealPeriodTypeStatus' (ID 1383012008) (index ID 2). Extra or invalid key for the keys: Server: Msg 8956, Level 16, State 1, Line 1 Index row (1:245:195) with values (DealId = 2 and PeriodId = 469 and Type = 'R' and Status = 'I') points to the data row identified by (RID = (1:663407:12)). Server: Msg 8952, Level 16, State 1, Line 1 Table error: Database 'dbCresta', index 'Recvs.XIEDealPeriodTypeStatus' (ID 1383012008) (index ID 2). Extra or invalid key for the keys: Server: Msg 8956, Level 16, State 1, Line 1 Index row (1:245:196) with values (DealId = 2 and PeriodId = 469 and Type = 'R' and Status = 'I') points to the data row identified by (RID = (1:663407:16)). Server: Msg 8952, Level 16, State 1, Line 1 Table error: Database 'dbCresta', index 'Recvs.XIEDealPeriodTypeStatus' (ID 1383012008) (index ID 2). Extra or invalid key for the keys: Server: Msg 8956, Level 16, State 1, Line 1 Index row (1:245:197) with values (DealId = 2 and PeriodId = 469 and Type = 'R' and Status = 'I') points to the data row identified by (RID = (1:663407:17)). Server: Msg 8952, Level 16, State 1, Line 1 Table error: Database 'dbCresta', index 'Recvs.XIEDealPeriodTypeStatus' (ID 1383012008) (index ID 2). Extra or invalid key for the keys:
{These two messages appeared 42 consecutive times, then there were some standard DBCC result messages and then...}
Server: Msg 8952, Level 16, State 1, Line 1 Table error: Database 'dbCresta', index 'Recvs.XIEDealPeriodTypeStatus' (ID 1383012008) (index ID 2). Extra or invalid key for the keys: Server: Msg 8956, Level 16, State 1, Line 1 Index row (1:571866:69) with values (DealId = 2 and PeriodId = 469 and Type = 'R' and Status = 'I') points to the data row identified by (RID = (1:663445:25)). Server: Msg 8952, Level 16, State 1, Line 1 Table error: Database 'dbCresta', index 'Recvs.XIEDealPeriodTypeStatus' (ID 1383012008) (index ID 2). Extra or invalid key for the keys: Server: Msg 8956, Level 16, State 1, Line 1 Index row (1:571867:244) with values (DealId = 2 and PeriodId = 469 and Type = 'R' and Status = 'I') points to the data row identified by (RID = (1:663382:1)). Server: Msg 8952, Level 16, State 1, Line 1 Table error: Database 'dbCresta', index 'Recvs.XIEDealPeriodTypeStatus' (ID 1383012008) (index ID 2). Extra or invalid key for the keys:
{a further 26 consecutive times.}
Running DBCC a second time resulted in 0 inconsistencies. This seemed to be a growing pattern: i.e. DBCC would show problems on the first attempt and come back clean on immediate subsequent attempts. The third party hosting the database insist that there are no hard drive issues (we pointed them to a number of forums implying this might be the case). They are using a RAID 5 configuration and also insist that no config changes have taken place in the last year.
That night a job was run to rebuild all of the indexes in the database. The following morning the very first thing I did was run a DBCC checkdb, with the following (trimmed) results: ... Table error: Table 'Recvs' (ID 1383012008). Missing or invalid key in index 'XIEDealPeriodTypeStatus' (ID 2) for the row: Server: Msg 8955, Level 16, State 1, Line 1 Data row (1:663382:1) identified by (RID = (1:663382:1) ) has index values (DealId = 2 and PeriodId = 469 and Type = 'R' and Status = 'E'). Server: Msg 8951, Level 16, State 1, Line 1 Table error: Table 'Recvs' (ID 1383012008). Missing or invalid key in index 'XIEDealPeriodTypeStatus' (ID 2) for the row: Server: Msg 8955, Level 16, State 1, Line 1 Data row (1:663382:2) identified by (RID = (1:663382:2) ) has index values (DealId = 2 and PeriodId = 469 and Type = 'R' and Status = 'E'). Server: Msg 8951, Level 16, State 1, Line 1 Table error: Table 'Recvs' (ID 1383012008). Missing or invalid key in index 'XIEDealPeriodTypeStatus' (ID 2) for the row: Server: Msg 8955, Level 16, State 1, Line 1 Data row (1:663382:3) identified by (RID = (1:663382:3) ) has index values (DealId = 2 and PeriodId = 469 and Type = 'R' and Status = 'E'). ... CHECKDB found 0 allocation errors and 3 consistency errors in database
{Running DBCC immediately again resulted in:} ... DBCC results for 'DealPeriodParmsShadow'. Server: Msg 8951, Level 16, State 1, Line 1 Table error: Table 'Recvs' (ID 1383012008). Missing or invalid key in index 'XIEDealPeriodTypeStatus' (ID 2) for the row: Server: Msg 8955, Level 16, State 1, Line 1 Data row (1:663382:1) identified by (RID = (1:663382:1) ) has index values (DealId = 2 and PeriodId = 469 and Type = 'R' and Status = 'E'). Server: Msg 8951, Level 16, State 1, Line 1 Table error: Table 'Recvs' (ID 1383012008). Missing or invalid key in index 'XIEDealPeriodTypeStatus' (ID 2) for the row: Server: Msg 8955, Level 16, State 1, Line 1 Data row (1:663382:2) identified by (RID = (1:663382:2) ) has index values (DealId = 2 and PeriodId = 469 and Type = 'R' and Status = 'E'). Server: Msg 8951, Level 16, State 1, Line 1 Table error: Table 'Recvs' (ID 1383012008). Missing or invalid key in index 'XIEDealPeriodTypeStatus' (ID 2) for the row: Server: Msg 8955, Level 16, State 1, Line 1 Data row (1:663382:3) identified by (RID = (1:663382:3) ) has index values (DealId = 2 and PeriodId = 469 and Type = 'R' and Status = 'E'). There are 127803 rows in 1059 pages for object 'DealPeriodParmsShadow'. ... CHECKDB found 0 allocation errors and 3 consistency errors in database
It should be noted that between the overnight job running and me executing DBCC in the morning no other external database activity took place. I.e. our application had been closed and no other databases on the server are currently in use.
The third party host subsequently executed dbcc checkdb with repair_rebuild and after this a standard DBCC CHECKDB came back clean. We later found that some of the data we had deleted in an attempt to rollback to a state before the errors had actually been resurrected by some of this activity. We then had to run many manual checks on the affected tables to verify the data was as expected.
Over the weekend of the 2nd and 3rd July diagnostic tests were run against the hardware in question in order to rule out h/w issues. The system was given a clean bill of health.
On Monday 4th the DBCC inconsistencies were back... They come and go, appearing on average once a week (sometimes two days in a row, sometimes not for two weeks). We have tried archiving data to make the tables smaller, removing the statistics (which we discovered were severely affecting performance anyway), running nightly DBCC CHECKDBs, all to no avail. The Recvs table seems to be the one that is the main problem. The bizarre thing is that the indexes on this table are dropped and recreated daily, since we bcp our daily data in. So the indexes should always be 'fresh'. There are no clustered indexes on this table, simply four non-clustered ones.
The support work resulting from the application failing (each time these DBCC inconsistencies occur) is time we'd very much like to spend more productively. We would very much appreciate any assistance anyone can give in getting to the root cause of the problem so that it can be addressed and the problems made to go away.
I have a situation where a common table within multiple databases across multiple servers is randomly getting corrupt. The database schema is identical for each database. There are approx. 200 copies of the database on each server. I am seeing one or two errors per week. There doesn't seem to be any similarities between the databases that are effected... It is always the same table that gets corrupted. The mulitple servers are using a SAN but since it is different databases / common table I have a hard time believing it is hardware related.
The error that the application throws is: Microsoft OLE DB Provider for ODBC Drivers (-2147467259) [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find the index entry for RID '16b700000000000000030002' in index page (1:5366), index ID 0, database 'DISOPT'.
When running a DBCC on the table the following is returned:
Database DISOPT consistency errors in table TF_FILE_METADATA_VALUES fixed no data loss reported. DBCC results for 'TF_FILE_METADATA_VALUES'. The error has been repaired. Clustered index successfully restored for object 'dbo.TF_FILE_METADATA_VALUES' in database 'MUTGEN'. There are 277 rows in 2 pages for object 'TF_FILE_METADATA_VALUES'. CHECKDB found 0 allocation errors and 1 consistency errors in table 'TF_FILE_METADATA_VALUES' (object ID 1332199796). CHECKDB fixed 0 allocation errors and 1 consistency errors in table 'TF_FILE_METADATA_VALUES' (object ID 1332199796).
This corrects the problem and the app functions as normal. The errors are random across databases but ALWAYS the same table.
I have been dealing with a recurring problem importing Excel data into SSIS. When I pull data in from an Excel source, the data is somehow changed. In the most recent example of this, I was importing about 28,000 rows from a single sheet of Excel data, all of it formatted as Text on the Excel side. The key field, Charge_Code, is an 8 digit numeric field but it may eventually contain alphanumeric identifiers so I am interpreting this as text. The majority of the rows import without any obvious errors; however, about 7,000 of these Charge_Codes are not being passed over into the subsequent components properly. For example, I had a Charge_Code that was 30100100 in my Excel source, but it came across as 30100000.
I've tried a number of tricks to solve this problem, including changing the Jet row sample "guess" parameter and using the IMEX driver directive described here: http://msmvps.com/blogs/nickwienholt/archive/2006/03/15/86379.aspx.
Interestingly enough, if I save the Excel spreadsheet into a CSV and use the resulting CSV file as my data source, these problems disappear.
I have experienced the same type of problem on 2 different computers, using several different packages and Excel sources. Are these problems common to those using Excel as SSIS data sources, and if so are there any reasonable workarounds to solve?
"tempdb is skipped. You cannot run a query that requires tempdb"?
We're running a .Net web application with a SQL Server 2000 backend, and we get the error intermittently. Restarting the SQL Server service seems to fix it, as it causes tempdb to be rebuilt, but this isn't a long term solution. Any direction or hints would be greatly appreciated. Thanks! - Mike
THis is sql server 6.5 question. I have tempdb data device size default 2 MB, which has completely filled up. I am trying to expand data device to it. I created new device tempdb_data_ext (250 MB) and tried to expand tempdb data device. But everytime I do it, it ends up adding space to tempdb log device. How can I expand tempdb data device?
Hi, How can I control the growth of tempdb in SQl server.It's growing like anything. CAn I create some alerts or jobs and what those alerts/job are supposed to do? All help appreciated. Jai
Hello! This is error message I discovered in NT even viewer: c:MSSQL7DATATEMPDB.MDF: Operating system error 112(There is not enough space on the disk.) encountered.
In SQL Server error log the errors are: Error: 1101, Severity: 17, State: 10
Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth..
Currently tempdb rezides on C drive and it's almost out of space. What should I do? Detach tempdb and then move to different drive? What's the procedure?
TEMPDB in one of our production servers does not clear up so every three to four weeks I have to restart NT. Nothing like this happens on any of the other three servers. Does anybody know where I should look at to correct the problem. I sure would appreciate it. Thanks Shashu
I have never done this before and thought I would ask. Is it possible to detach the tempdb database, move it to another drive or partition, and then re-attach it? What would be the downside or side-affects to doing such a thing?
Error : 933, Severity: 22, State: 1 Logical page 258 of the log encountered while retrieving highest timestamp in database 'tempdb' is not the last page of the log and we are not currently recovering that database.
I use sqlserver -T4022 to start my SQL Server since it will not start with out it. When I start sqlserver without the option, it tells me that
Error : 615, Severity: 21, State: 1 Unable to find database table id = 2, name = 'tempdb'.
I need to move tempdb to another drive,also increase the size.Largest database is 15GB.Can anyone suggest the size and also the exact commands to move.Do I need to backup the databases before I do this task?If SP1 is not installed,will it be o.k for me for this tempdb problem.If we have a larger tempdb like 4GB,will it effect anything?...Urgent!!
I read an article on this site by Michael Hotek re "Basic SQL Server 6.5 Configuration Options". In the paragraph about TempDB he says that you should always avoid using Temp tables in stored procs. I use this feature a lot when trying to do "not in" type queries (I filter out a portion of a larger table and then use the "not in" on the temp table rather than the entire table.) Is there a better way to run a Not in query. I have the table well indexed (i think) but it seems to do a full table scan if I use the entire table.
Our Tempdb.mdf file is 11 gigs. I have tried several things to shrink this but with no luck. Does anybody have a suggestion on how I can free up that space. I have tried to re-start Sql but that didn't do anything. I thought that there was a bug, if the files got above 4 gig that sql wouldn't clear them, but I could be wrong
I thought I could detach it, and attach a new file, but makes me nervous without knowing if that’s correct.
I am trying to configure a 6.5 server to set the tempdb to run off disk. I reset the tempdb in ram = 0 in the configuration, and restarted the service, but it left it as running in ram, with 0 configured. I then rebooted the server, and it still left the tempdb in ram. Any ideas?
I am trying to get some information about tempdb database. I've tried BOL but I couldn't find a whole lot of info. I am trying to find out what size should tempdb be to not to cause problems. Also, I am trying to shrink tempdb by using shrink database option in EM, but it only shrinks the tempdb transaction log not the datafile, and I don't know why it is happening.
We are using Peoplesoft HRMS version 7.0 on NT Server 4.0 and SQL Server 6.5, service packs on both up-to-date.
The server is a HP LX Pro with 4 200 MHz 1MB cache processors with 2,560 MB of RAM and 18 9GB drives configured with different combinations of RAID 1, 1+0, and 5.
The database is 2.3GB in actual size with the specific size of 6GB for data and 2GB for log on separate RAIDs.
We would like to use tempdb in RAM to boost the speed of complex queries that use the tempdb for intermediate working tables.
We would like to know of any experience of running tempdb in RAM, issues to overcome, of how to setup.
There are articles and books not recommending using tempdb in RAM. Comments anyone?
Note: Have looked in SQL Server DBA Survival Guide, and SQL Server Unleashed, along with the normal MS Documentation, technet, online books, and knowledge base searches.
Everything I've ever heard or seen says not to put tempDB in RAM, citing reasons of negligible benefit or even worse performance as it steals memory from SQL's cache memory.
My issue is I need to take anywhere from 500 to 3000 records (games played) in a batch every five minutes from a table, ordered by game id. There's no guarantee of sequential order to how the records are inserted, so I need to do an ORDER BY in my query. I also do a stored procedure for ranking that requires an ORDER BY.
The performance difference between the two is staggering. 1000 records with a 200 mb tempdb database (not in RAM) takes about 300 seconds. With a 16 MB tempdb database IN ram, it takes just under 127 seconds. Only problem? I'm constantly getting, after a few batches have run, "out of space in syslogs for tempdb ram messages. "
Is there a way to net such performance from tempdb without putting it in RAM, and if not, is there a way to explicity clear out tempdb. Thanks for any help.
I have a problem with Tempdb database on SQL 6.5 server.....when i create a temporary table, i block other users in other databases. Actually i run a stored procedure which creates a temporary table and then the procedure insets some values of a table from my test database. When the spored procedure is executed, users in other databases also get blocked and no new user will be allowd to log on to the server. When i check as to what the other users are executing, i find that they are also creting or droping a temp table. So i will have to either kill my SP or let it finish and continue blocking other users.
As of now, i am not able to find a reason for that and i try to run the Stored procedure in offpeak hrs.
I have not played around much with Temp tables in SQL 6.5. Pl do suggest me as to what to do....
Some details you would be interested on :
Server - SQL Server 6.5 Front End - Great Plains Size of Tempdb - Data Space Available( It says shared with data) database_name database_size unallocated space ------------------------------ ------------------ ------------------ tempdb 1502.00 MB -6301.82 MB
I have a dumb ? When I go in to expand the tempdb to at least 25mb req. for the migration It is actually expanding the Log size & the Db available size still says 1.76mb.
Being new to SQL server I would like some information regarding the tempdb database.
I user SQL Server 2000.
At present I appear to be backing up my tempdb but it has been failing since way back. It gives me this error when trying to bakup the database:
Executed as user: NT AUTHORITYSYSTEM. Backup and restore operations are not allowed on database tempdb. [SQLSTATE 42000] (Error 3147) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Can someone give me a basic grounding as to what the tempdb is used for and should I back it up or not?
Hi All if temdb grows and use all the disk space and then after log backup or other clean up process its size got reduced but it is still holding the space it was grown upto. In this case what would be ideal to do restart server/services or is it possible to reduce the space alocated to tempdb through properties.
