July 20, 2015 at 9:07 am
Hello people I'm new here ni SQLServerCentral my name and André'm from Brazil
and I have some problems with a faulty basis. The situation and the following have a table with 114,866,343 Sedo so that these can not access 5866343. Started as follows
select count (*) from dbo.tblSIFOB
- First the count of the table it presents 114 866 343
select * from dbo.tblSIFOB
- However through a select * so I can access 5866343
- Introducing the following error.
--An Error occurred while executing batch. Error message is: There is not enough space on the disk.
- When running the dbcc checkdb he apresena the following error has
DBCC CHECKDB WITH ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS
--Msg 0, Level 11, State 0, Line 0
--The Severe error occurred on the current command. The results, if any, shouldnt be discarded.
- I tried to create a dump table to try to rescue the data deacordo with the last sample
- Video Best of PASS Summit 2014: Advanced Data Recovery Techniques
- Paul Randal.
- Out of curiosity I ran one dbcc checktable
DBCC CHECKTABLE ('dbo.tblSIFOB');
GO
--Msg 8939, Level 16, State 33, Line 2
--table Error: Object ID 99, index ID 0, partition ID 0, alloc unit ID 6488064 (type System allocation date), page (3: 3777096). Test (m_slots [0] .GetOffset () == == PAGEHEADSIZE && m_type PFS_PAGE) failed. Values are 74 and 96.
--CHECKTABLE Found 0 allocation errors and 1 consistency errors in table '(Object ID 99)' (object ID 99).
Someone would have any tips?
There is some more information to base this with this 2010 des problem therefore all backup are in trouble.
Obs .: Sorry for my English by google hehehehe
July 20, 2015 at 9:56 am
andre1983 (7/20/2015)
Hello people I'm new here ni SQLServerCentral my name and André'm from Braziland I have some problems with a faulty basis. The situation and the following have a table with 114,866,343 Sedo so that these can not access 5866343. Started as follows
select count (*) from dbo.tblSIFOB
- First the count of the table it presents 114 866 343
select * from dbo.tblSIFOB
- However through a select * so I can access 5866343
- Introducing the following error.
--An Error occurred while executing batch. Error message is: There is not enough space on the disk.
- When running the dbcc checkdb he apresena the following error has
DBCC CHECKDB WITH ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS
--Msg 0, Level 11, State 0, Line 0
--The Severe error occurred on the current command. The results, if any, shouldnt be discarded.
- I tried to create a dump table to try to rescue the data deacordo with the last sample
- Video Best of PASS Summit 2014: Advanced Data Recovery Techniques
- Paul Randal.
- Out of curiosity I ran one dbcc checktable
DBCC CHECKTABLE ('dbo.tblSIFOB');
GO
--Msg 8939, Level 16, State 33, Line 2
--table Error: Object ID 99, index ID 0, partition ID 0, alloc unit ID 6488064 (type System allocation date), page (3: 3777096). Test (m_slots [0] .GetOffset () == == PAGEHEADSIZE && m_type PFS_PAGE) failed. Values ??are 74 and 96.
--CHECKTABLE Found 0 allocation errors and 1 consistency errors in table '(Object ID 99)' (object ID 99).
Someone would have any tips?
There is some more information to base this with this 2010 des problem therefore all backup are in trouble.
Obs .: Sorry for my English by google hehehehe
Don't worry about the English;-)
Can you tell us more about your system, what are the hard drive sizes and free space (xp_fixeddrives) on those drives: the location of tempdb data files and log files, the user databases' data and log files and so on. The more information you give us, the better the answers. First guess is that you have run out of space and further, probably the DBCC cannot create a snapshot.
😎
July 20, 2015 at 11:00 am
Ok
the xp_fixeddrives sp had the following result
--Result
--C5375 (S.O)
--D821059(Data)
--E131847(LOG)
--F32456(tempdb)
I managed to clear the server and let it so.
--C8220
--D845353
--E131847
--F197138
As I do not have much space available for this machine created two NDF files on disk temp db date you so it can grow.
The recovery model is simple .
And the error persists in the same way
DBCC CHECKDB WITH ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
July 20, 2015 at 11:24 am
From your error message, it looks like you table has failed a consistency check. First and foremost, you should check your backup situation. If a viable backup contains the information you need from this table, you can restore the information without loosing your data. If there isn't a good backup available, DBCC CHECKDB has a repair option that can fix the issues, but may result in loss of data. Use at your own risk, and always be sure to take frequent backups and test them in the future to mitigate chances of loosing your data.
DBCC CHECKTABLE ( 'dbo.tblSIFOB', REPAIR_ALLOW_DATA_LOSS )
July 20, 2015 at 11:59 am
Kutsch I ran her over and he presented the following command result
--DBCC CHECKTABLE ('dbo.tblSIFOB' REPAIR_ALLOW_DATA_LOSS)
Msg 8939, Level 16, State 33, Line 1
Table error: Object ID 99, index ID 0, partition ID 0, alloc unit ID 6488064 (type System allocation date), page (3: 3777096). Test (m_slots [0] .GetOffset () == == PAGEHEADSIZE && m_type PFS_PAGE) failed. Values are 74 and 96.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table '(Object ID 99)' (object ID 99).
July 20, 2015 at 12:10 pm
looking at your sample: DBCC CHECKTABLE ('dbo.tblSIFOB' REPAIR_ALLOW_DATA_LOSS)
the comma between the table and the repair option was missed.
Did you check your backup situation BEFORE running this?
DBCC CHECKTABLE('dbo.tblSIFOB',REPAIR_ALLOW_DATA_LOSS)
July 20, 2015 at 12:14 pm
Thus gave the same result as the previous message.
July 20, 2015 at 12:50 pm
Do you have any backups prior to your running DBCC CHECKDB (or DBCC CHECKTABLE)? Have you restored the latest backup prior to the error under a different name or on a test server and run DBCC CHECKDB (or DBCC CHECKTABLE) there?
Just a swag, you need to recover at least the table from a good backup.
July 20, 2015 at 1:34 pm
I ran the dbcc checkdb and checktable on a backup
2010 and gave the same error is that I can restore at least
of the data by means of a write page table dump?
July 21, 2015 at 6:16 am
André do Nascimento (7/20/2015)
--An Error occurred while executing batch. Error message is: There is not enough space on the disk.
Aside from the DBCC error, you should verify tempdb. The above error often occurs when tempdb has run out of space while trying to run a query against a user database.
If tempdb is full, run a checkpoint against tempdb, then see if you can release all unused space with a GUI shrink. This is the quickest way to reclaim space for a full drive so you can later reorg and shrink. However, if the space is still being used, this won't work.
This method also works with user databases. Be aware, though, this is only a temporary fix for getting just enough room to do whatever needs to be done. Which includes adding hard drive space, if that is necessary.
July 21, 2015 at 6:43 am
If the output listed is all that checkDB returned, this is not repairable.
Restore from your last good backup. If you don't have a backup. Script the objects out (tables, procedures, views, etc), export the data that you can (some will probably fail) and recreate the DB.
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
July 21, 2015 at 6:56 am
: S Huston we have a problem.
Well I believe to have no solution yesterday afternoon tried to create a dump picking table
the example of last year's pass of videos of Paul Randal did not have more success
after writing the pages in the new table to adjust the value of alloc id of pages the same mistake was made. Too bad more'll keep trying.
July 21, 2015 at 7:00 am
If this database is critical, don't fiddle with writepage or similar, you could easily make things worse. Export what data you can, recreate the DB, get the system up and working with the data you could get out. Then, if you want, play with writepage and the like to try and get more data out.
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
July 21, 2015 at 11:13 pm
This was removed by the editor as SPAM
July 28, 2015 at 12:03 pm
Also I wanted to say WELCOME André do Nascimento!!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply