March 31, 2014 at 7:02 am
Hi,
I have a cliente that has a database.
The database got suspect after he restarted the Server.
I doesn't have backups. .......
I sent me the log and mdf files.
I did the following procedures:
EXEC sp_configure 'Allow updates', '1'
Reconfigure with override
UPDATE master.dbo.sysdatabases
SET Status = -32768
WHERE [Name] = 'SGT'
GO
EXEC sp_dboption 'SGT', 'Single User','TRUE'
DBCC REBUILD_LOG ('SGT','D:\sgt_log.LDF')
ALTER DATABASE sgt SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (SGT, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE SGT SET MULTI_USER
The result was:
Clustered index successfully restored for object 'syscolumns' in database 'sgt'.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'FUNCIONARIO'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'POSSNOTIFICACAO'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'ALTERIMP'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'CONFVALORMULTAS'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'PAGAMENTOCHEQUE'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'LiqOfIPU'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'PAGAMENTOPOS'. Check sysobjects.
DBCC results for 'SGT'.
DBCC results for 'sysobjects'.
There are 363 rows in 9 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 419 rows in 18 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
Msg 8928, Level 16, State 1, Line 1
Object ID 3, index ID 0: Page (1:30) could not be processed. See other errors for details.
The error has been repaired.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 3, index ID 0, page (1:30). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1.
The error has been repaired.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 3, index ID 1. Page (1:20) is missing a reference from previous page (1:30). Possible chain linkage problem.
The error has been repaired.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 3, index ID 1. Page (1:30) was not seen in the scan although its parent (1:17) and previous (1:28) refer to it. Check any previous errors.
The error has been repaired.
Clustered index successfully restored for object 'dbo.syscolumns' in database 'sgt'.
Repair: Page (1:30) has been deallocated from object ID 3, index ID 1.
There are 921 rows in 19 pages for object 'syscolumns'.
There are 921 rows in 9 pages for object 'syscolumns'.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'syscolumns' (object ID 3).
CHECKDB fixed 0 allocation errors and 4 consistency errors in table 'syscolumns' (object ID 3).
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 252 rows in 29 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 50 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 18 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 599 rows in 3 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 39 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for 'CRELAXE'.
There are 2169 rows in 37 pages for object 'CRELAXE'.
DBCC results for 'PEDIDOANULACOES'.
There are 625 rows in 15 pages for object 'PEDIDOANULACOES'.
DBCC results for 'CREDITO'.
There are 2 rows in 1 pages for object 'CREDITO'.
DBCC results for 'CCCOERCIVA'.
There are 1697 rows in 16 pages for object 'CCCOERCIVA'.
DBCC results for 'PERIODICIDADE'.
There are 4 rows in 1 pages for object 'PERIODICIDADE'.
DBCC results for 'CCCREDITO'.
There are 1 rows in 1 pages for object 'CCCREDITO'.
DBCC results for 'LOG_SESSION'.
There are 12883 rows in 224 pages for object 'LOG_SESSION'.
DBCC results for '__RELATORIO'.
There are 122 rows in 2 pages for object '__RELATORIO'.
DBCC results for 'RELATORIO'.
There are 151 rows in 3 pages for object 'RELATORIO'.
DBCC results for 'TOTAISORCAMENTAIS'.
There are 2335 rows in 12 pages for object 'TOTAISORCAMENTAIS'.
DBCC results for 'PROVINCIA'.
There are 0 rows in 0 pages for object 'PROVINCIA'.
DBCC results for 'ANULACAO'.
There are 11700 rows in 115 pages for object 'ANULACAO'.
DBCC results for 'BANCO'.
There are 17 rows in 1 pages for object 'BANCO'.
DBCC results for 'CONTARESPONSABILIDADE'.
There are 2279 rows in 48 pages for object 'CONTARESPONSABILIDADE'.
DBCC results for 'ENTLIQOF'.
There are 0 rows in 0 pages for object 'ENTLIQOF'.
DBCC results for 'REPFISCAL'.
There are 0 rows in 0 pages for object 'REPFISCAL'.
DBCC results for 'L_REP'.
There are 1 rows in 1 pages for object 'L_REP'.
DBCC results for 'BAIRRO'.
There are 0 rows in 0 pages for object 'BAIRRO'.
DBCC results for 'TFECHO'.
There are 4 rows in 1 pages for object 'TFECHO'.
DBCC results for 'COBRANCA'.
There are 293972 rows in 3902 pages for object 'COBRANCA'.
DBCC results for 'ACESSOS'.
There are 866 rows in 4 pages for object 'ACESSOS'.
DBCC results for 'TLIQUIDACAO'.
There are 13 rows in 1 pages for object 'TLIQUIDACAO'.
DBCC results for 'REPORTLEVEL'.
There are 10633 rows in 53 pages for object 'REPORTLEVEL'.
DBCC results for 'TOPERACAO'.
There are 7 rows in 1 pages for object 'TOPERACAO'.
DBCC results for 'COMPATIBILIDADE'.
There are 4 rows in 1 pages for object 'COMPATIBILIDADE'.
DBCC results for 'COMUNA'.
There are 0 rows in 0 pages for object 'COMUNA'.
DBCC results for 'TESOURARIADIARIO'.
There are 807 rows in 11 pages for object 'TESOURARIADIARIO'.
DBCC results for 'TPAGAMENTO'.
There are 5 rows in 1 pages for object 'TPAGAMENTO'.
DBCC results for 'CONFIGURACAO'.
There are 1 rows in 1 pages for object 'CONFIGURACAO'.
DBCC results for 'CONTARESPONSABILIDADECOERCIVA'.
There are 2279 rows in 58 pages for object 'CONTARESPONSABILIDADECOERCIVA'.
DBCC results for 'VERSAOBD'.
There are 1 rows in 1 pages for object 'VERSAOBD'.
DBCC results for 'SGTACONF'.
There are 9 rows in 1 pages for object 'SGTACONF'.
DBCC results for 'PIB'.
There are 0 rows in 1 pages for object 'PIB'.
DBCC results for 'SGTALIQDATA'.
There are 0 rows in 0 pages for object 'SGTALIQDATA'.
DBCC results for 'ORCAMENTO'.
There are 0 rows in 1 pages for object 'ORCAMENTO'.
DBCC results for 'EXERCICIOECON'.
There are 2 rows in 1 pages for object 'EXERCICIOECON'.
DBCC results for 'FECHOABERTURA'.
There are 1659 rows in 8 pages for object 'FECHOABERTURA'.
DBCC results for 'FUNCIONALIDADE'.
There are 31 rows in 1 pages for object 'FUNCIONALIDADE'.
DBCC results for 'SCRIPTS'.
There are 414 rows in 9 pages for object 'SCRIPTS'.
DBCC results for 'fechoaberturabak'.
There are 505 rows in 2 pages for object 'fechoaberturabak'.
DBCC results for 'PAGAMENTOTRANSFER'.
There are 6 rows in 1 pages for object 'PAGAMENTOTRANSFER'.
DBCC results for 'GRUPORELATORIO'.
There are 14 rows in 1 pages for object 'GRUPORELATORIO'.
DBCC results for 'TESOURARIAMENSAL'.
There are 39 rows in 1 pages for object 'TESOURARIAMENSAL'.
DBCC results for 'IMPOSTO'.
There are 144 rows in 2 pages for object 'IMPOSTO'.
DBCC results for 'OBSLIQUIDACAO'.
There are 0 rows in 0 pages for object 'OBSLIQUIDACAO'.
DBCC results for 'LIQINFO'.
There are 0 rows in 0 pages for object 'LIQINFO'.
DBCC results for 'COBEXPORT'.
There are 228188 rows in 931 pages for object 'COBEXPORT'.
DBCC results for 'LIQUIDACAO'.
Msg 8952, Level 16, State 1, Line 1
Table error: Database 'sgt', index 'LIQUIDACAO.AK_LIQUIDACAO_LANCLIQ' (ID 1243151474) (index ID 3). Extra or invalid key for the keys:
The error has been repaired.
Msg 8956, Level 16, State 1, Line 1
Index row (1:2456:337) with values (LANCLIQ = 301 and DATALIQ = Mar 28 2014 12:00AM and CODLIQ = 417206) points to the data row identified by ().
There are 307520 rows in 16348 pages for object 'LIQUIDACAO'.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'LIQUIDACAO' (object ID 1243151474).
CHECKDB fixed 0 allocation errors and 1 consistency errors in table 'LIQUIDACAO' (object ID 1243151474).
DBCC results for 'ELIMINACAO_COBRANCA'.
There are 221 rows in 7 pages for object 'ELIMINACAO_COBRANCA'.
DBCC results for 'ALTERDAR'.
There are 83 rows in 4 pages for object 'ALTERDAR'.
DBCC results for 'PCBANCO'.
There are 33 rows in 1 pages for object 'PCBANCO'.
DBCC results for 'TESOURARIADIARIOBANCO'.
There are 810 rows in 13 pages for object 'TESOURARIADIARIOBANCO'.
DBCC results for 'dtproperties'.
There are 0 rows in 0 pages for object 'dtproperties'.
DBCC results for 'CONTARESPONSABILIDADEBANCO'.
There are 1544 rows in 16 pages for object 'CONTARESPONSABILIDADEBANCO'.
DBCC results for 'LOG_OPERATION'.
There are 673397 rows in 11011 pages for object 'LOG_OPERATION'.
DBCC results for 'SESSOESEXPORT'.
There are 1431 rows in 6 pages for object 'SESSOESEXPORT'.
DBCC results for 'LOGOPERATION_HISTORICO'.
There are 713080 rows in 13118 pages for object 'LOGOPERATION_HISTORICO'.
DBCC results for 'LOGSESSION_HISTORICO'.
There are 16635 rows in 79 pages for object 'LOGSESSION_HISTORICO'.
DBCC results for 'MODFUNCIONAL'.
There are 48 rows in 1 pages for object 'MODFUNCIONAL'.
DBCC results for 'CCCONTRIBUINTE'.
There are 427344 rows in 3011 pages for object 'CCCONTRIBUINTE'.
DBCC results for 'MODULO'.
There are 11 rows in 1 pages for object 'MODULO'.
DBCC results for 'T_NSEQ'.
There are 8 rows in 1 pages for object 'T_NSEQ'.
DBCC results for 'MUNICIPIO'.
There are 0 rows in 0 pages for object 'MUNICIPIO'.
DBCC results for 'NOTIFICACAO'.
There are 2224 rows in 32 pages for object 'NOTIFICACAO'.
CHECKDB found 0 allocation errors and 5 consistency errors in database 'sgt'.
CHECKDB fixed 0 allocation errors and 5 consistency errors in database 'sgt'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
So I lost tables.
Do you know if I can recover this tables?
Thanks
P.S - The database is online but with some tables that don't appear
March 31, 2014 at 7:19 am
You ran CheckDB with repair_allow_data_loss, and it lost data. That's what the command does.
The only way to recover is if you had backups from before the corruption occurred.
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
March 31, 2014 at 7:26 am
I lost this tables:
Clustered index successfully restored for object 'syscolumns' in database 'sgt'.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'FUNCIONARIO'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'POSSNOTIFICACAO'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'ALTERIMP'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'CONFVALORMULTAS'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'PAGAMENTOCHEQUE'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'LiqOfIPU'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'PAGAMENTOPOS'. Check sysobjects.
March 31, 2014 at 7:27 am
The cliente has a backup (1 month old...)
March 31, 2014 at 7:44 am
Corruption in the system tables is severe. Restoring that month old backup may be a better approach than repairing.
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
March 31, 2014 at 8:21 am
I would check that month old backup and confirm it is corruption free. If they aren't taking regular backups, I'd guess they didn't perform routine checkdb's. They very well could have had corruption sitting out there for quite a while.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply