Extent allocation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

Extent allocation

Author

Topic

jhnegrao
Yak Posting Veteran

81 Posts

Posted - 2008-11-12 : 07:23:14

Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:103784) in database ID 17 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:103792) in database ID 17 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:103800) in database ID 17 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:103808) in database ID 17 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:103816) in database ID 17 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:103824) in database ID 17 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:103832) in database ID 17 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:103840) in database ID 17 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:103848) in database ID 17 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:103856) in database ID 17 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:103864) in database ID 17 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:104016) in database ID 17 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:104024) in database ID 17 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:104032) in database ID 17 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:104040) in database ID 17 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
DBCC results for 'Teste2003II_2_Restore'.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:104048) in database ID 17 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:104056) in database ID 17 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:104064) in database ID 17 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:104072) in database ID 17 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:104080) in database ID 17 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:104088) in database ID 17 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:104096) in database ID 17 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
CHECKDB found 22 allocation errors and 0 consistency errors not associated with any single object.
DBCC results for 'sysobjects'.
There are 459 rows in 9 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 427 rows in 27 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 1451 rows in 31 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 438 rows in 128 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 18 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 12 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 1415 rows in 9 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 1 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 'D_SistemaFornecedores'.
There are 0 rows in 0 pages for object 'D_SistemaFornecedores'.
DBCC results for 'D_SistemaInventario'.
There are 0 rows in 0 pages for object 'D_SistemaInventario'.
DBCC results for 'D_SistemaProdutos'.
There are 0 rows in 0 pages for object 'D_SistemaProdutos'.
DBCC results for 'S_Ajuste_Exportacao'.
There are 5778 rows in 160 pages for object 'S_Ajuste_Exportacao'.
DBCC results for 'A_Media_Vendas'.
There are 14076 rows in 194 pages for object 'A_Media_Vendas'.
DBCC results for 'D_SistemaRelacao'.
There are 0 rows in 0 pages for object 'D_SistemaRelacao'.
DBCC results for 'D_Vendas'.
There are 0 rows in 0 pages for object 'D_Vendas'.
DBCC results for 'D_Vendas_Exportacao'.
There are 0 rows in 0 pages for object 'D_Vendas_Exportacao'.
DBCC results for 'D_Vendas_Venc'.
There are 0 rows in 0 pages for object 'D_Vendas_Venc'.
DBCC results for 'I_CFOP'.
There are 808 rows in 10 pages for object 'I_CFOP'.
DBCC results for 'I_Libor'.
There are 2587 rows in 20 pages for object 'I_Libor'.
DBCC results for 'I_Metodologias_de_Calculo'.
There are 13 rows in 1 pages for object 'I_Metodologias_de_Calculo'.
DBCC results for 'I_Moedas_Cotacoes'.
There are 415212 rows in 4799 pages for object 'I_Moedas_Cotacoes'.
DBCC results for 'I_Moedas_Nomes'.
There are 219 rows in 2 pages for object 'I_Moedas_Nomes'.
DBCC results for 'I_Paises'.
There are 242 rows in 3 pages for object 'I_Paises'.
DBCC results for 'D_Paises_Teste'.
There are 35 rows in 1 pages for object 'D_Paises_Teste'.
DBCC results for 'NFComple'.
There are 3805 rows in 50 pages for object 'NFComple'.
DBCC results for 'I_Selic'.
There are 2587 rows in 7 pages for object 'I_Selic'.
DBCC results for 'I_UnidadeMedida'.
There are 15 rows in 1 pages for object 'I_UnidadeMedida'.
DBCC results for 'I_Unidades'.
There are 15 rows in 1 pages for object 'I_Unidades'.
DBCC results for 'S_Vendas_Cod_Cli_ME_Vinc'.
There are 5778 rows in 413 pages for object 'S_Vendas_Cod_Cli_ME_Vinc'.
DBCC results for 'I_Vinc_Just'.
There are 0 rows in 0 pages for object 'I_Vinc_Just'.
DBCC results for 'Exportacao_bp0'.
There are 56327 rows in 911 pages for object 'Exportacao_bp0'.
DBCC results for 'D_DE_PARA_Clientes'.
There are 87 rows in 1 pages for object 'D_DE_PARA_Clientes'.
DBCC results for 'exportexcluidos'.
There are 1151 rows in 68 pages for object 'exportexcluidos'.
DBCC results for 'S_Ajuste_Importacao_2'.
There are 14610 rows in 225 pages for object 'S_Ajuste_Importacao_2'.
DBCC results for 'D_DE_PARA_Fornecedores'.
There are 25012 rows in 110 pages for object 'D_DE_PARA_Fornecedores'.
DBCC results for 'indexaa'.
There are 14662 rows in 52 pages for object 'indexaa'.
DBCC results for 'indexab'.
There are 8435 rows in 29 pages for object 'indexab'.
DBCC results for 'D_DE_PARA_Moedas'.
There are 6 rows in 1 pages for object 'D_DE_PARA_Moedas'.
DBCC results for 'A_top_10_ajustes'.
There are 10 rows in 1 pages for object 'A_top_10_ajustes'.
DBCC results for 'S_Dolar_Consumo'.
There are 53772 rows in 350 pages for object 'S_Dolar_Consumo'.
DBCC results for 'A_top_10_imports'.
There are 10 rows in 1 pages for object 'A_top_10_imports'.
DBCC results for 'S_Falta_Calcular'.
There are 1722 rows in 24 pages for object 'S_Falta_Calcular'.
DBCC results for 'S_Ficha_31'.
There are 1 rows in 1 pages for object 'S_Ficha_31'.
DBCC results for 'Compras$'.
There are 0 rows in 0 pages for object 'Compras$'.
DBCC results for 'S_Materias_Primas'.
There are 25179 rows in 78 pages for object 'S_Materias_Primas'.
DBCC results for 'D_Balanco'.
There are 0 rows in 0 pages for object 'D_Balanco'.
DBCC results for 'S_PIC'.
There are 0 rows in 0 pages for object 'S_PIC'.
DBCC results for 'S_Resultado_Exportacao'.
There are 3 rows in 1 pages for object 'S_Resultado_Exportacao'.
DBCC results for 'S_Preco_Parametros_Import'.
There are 10405 rows in 59 pages for object 'S_Preco_Parametros_Import'.
DBCC results for 'S_Precos_Medios'.
There are 13758 rows in 109 pages for object 'S_Precos_Medios'.
DBCC results for 'AUX_CONSUMO'.
There are 197632 rows in 318 pages for object 'AUX_CONSUMO'.
DBCC results for 'S_Processos'.
There are 882 rows in 9 pages for object 'S_Processos'.
DBCC results for 'AUX_MEDIA_PRECO'.
There are 197632 rows in 2279 pages for object 'AUX_MEDIA_PRECO'.
DBCC results for 'S_Produtos_Acabados'.
There are 30468 rows in 101 pages for object 'S_Produtos_Acabados'.
DBCC results for 'D_RELACAO_BKP_COMPLETO'.
There are 274789 rows in 7852 pages for object 'D_RELACAO_BKP_COMPLETO'.
DBCC results for 'A_media_vendas_min'.
There are 19197 rows in 96 pages for object 'A_media_vendas_min'.
DBCC results for 'S_Pendencia'.
There are 0 rows in 0 pages for object 'S_Pendencia'.
DBCC results for 'D_Compras'.
There are 270400 rows in 12948 pages for object 'D_Compras'.
DBCC results for 'S_Saldos'.
There are 101211 rows in 1635 pages for object 'S_Saldos'.
DBCC results for 'S_Vendas_Cod_ME_Terc'.
There are 4281 rows in 98 pages for object 'S_Vendas_Cod_ME_Terc'.
DBCC results for 'S_Vendas_Cod_MI_Terc'.
There are 20026 rows in 456 pages for object 'S_Vendas_Cod_MI_Terc'.
DBCC results for 'S_Vendas_Cod_Terc'.
There are 24907 rows in 568 pages for object 'S_Vendas_Cod_Terc'.
DBCC results for 'AUX_CFOPS'.
There are 42517 rows in 143 pages for object 'AUX_CFOPS'.
DBCC results for 'S_DIPJ_Exportacao'.
There are 109 rows in 7 pages for object 'S_DIPJ_Exportacao'.
DBCC results for 'S_Ajuste_Importacao'.
There are 14610 rows in 198 pages for object 'S_Ajuste_Importacao'.
DBCC results for 'D_Vendas_Itens'.
There are 2171527 rows in 127213 pages for object 'D_Vendas_Itens'.
DBCC results for 'D_Inventario'.
There are 182357 rows in 1414 pages for object 'D_Inventario'.
DBCC results for 'D_Empresas'.
There are 1 rows in 1 pages for object 'D_Empresas'.
DBCC results for 'D_relacao'.
There are 251136 rows in 7584 pages for object 'D_relacao'.
DBCC results for 'D_Produtos'.
There are 496147 rows in 5566 pages for object 'D_Produtos'.
DBCC results for 'S_Composicao_Saldos'.
There are 191316 rows in 4050 pages for object 'S_Composicao_Saldos'.
DBCC results for 'S_DIPJ_Ajustes'.
There are 8373 rows in 114 pages for object 'S_DIPJ_Ajustes'.
DBCC results for 'S_DIPJ_Importacao'.
There are 165 rows in 7 pages for object 'S_DIPJ_Importacao'.
DBCC results for 'S_Dados_DIPJ_Exp'.
There are 3958 rows in 85 pages for object 'S_Dados_DIPJ_Exp'.
DBCC results for 'S_Dados_DIPJ_Imp'.
There are 9211 rows in 140 pages for object 'S_Dados_DIPJ_Imp'.
DBCC results for 'Testesaldos2002'.
There are 15271 rows in 136 pages for object 'Testesaldos2002'.
DBCC results for 'dtproperties'.
There are 0 rows in 0 pages for object 'dtproperties'.
DBCC results for 'D_Inventario_Carga'.
There are 0 rows in 0 pages for object 'D_Inventario_Carga'.
DBCC results for 'D_Ordens_Producao'.
There are 2636351 rows in 70435 pages for object 'D_Ordens_Producao'.
DBCC results for 'D_Clientes'.
There are 99051 rows in 1453 pages for object 'D_Clientes'.
DBCC results for 'D_Compras_Int'.
There are 0 rows in 0 pages for object 'D_Compras_Int'.
DBCC results for 'D_Custo_Medio'.
There are 0 rows in 0 pages for object 'D_Custo_Medio'.
DBCC results for 'D_Equivalencia'.
There are 4166 rows in 37 pages for object 'D_Equivalencia'.
DBCC results for 'D_Fornecedores'.
There are 28447 rows in 427 pages for object 'D_Fornecedores'.
DBCC results for 'D_relacao2'.
There are 535772 rows in 14481 pages for object 'D_relacao2'.
DBCC results for 'D_SistemaClientes'.
There are 0 rows in 0 pages for object 'D_SistemaClientes'.
CHECKDB found 22 allocation errors and 0 consistency errors in database 'Teste2003II_2_Restore'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Teste2003II_2_Restore ).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2008-11-12 : 15:17:22

So you didn't post a question or anything - what are you looking for? How to recover? What this means? etc etc

Paul S. Randal, SQL Server MVP
Author of SQL 2005 DBCC CHECKDB/repair code
Author & Instructor of Microsoft Certified Master - Database course
Managing Director, SQLskills.com (www.SQLskills.com/blogs/paul)

jhnegrao
Yak Posting Veteran

81 Posts

Posted - 2008-11-13 : 06:22:18

I'm sorry!

I didn't write anything because I've posted in another forum and somebody said me to put it here.
So, I've forgotten to write.

My Check DB Analyze got the report above and I would like to detect which database object has been allocated to these extents.

Thanks

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2008-11-13 : 13:14:43

No database object has any allocation errors - as the CHECKDB output says, the GAM page thinks the extents are allocated but nothing actually does. You can safely run repair_allow_data_loss and it will just reset the bits in the GAM page.

What's more concerning is how it happened. Its a contiguous set of bits in the GAM page so my guess is some memory or I/O subsystem corruption. Run the usual diagnostics to figure out which.

Thanks

Paul S. Randal, SQL Server MVP
Author of SQL 2005 DBCC CHECKDB/repair code
Author & Instructor of Microsoft Certified Master - Database course
Managing Director, SQLskills.com (www.SQLskills.com/blogs/paul)

jhnegrao
Yak Posting Veteran

81 Posts

Posted - 2008-11-13 : 13:45:44

Thank you very much

hotfix4sql
Starting Member

2 Posts

Posted - 2008-11-30 : 15:00:44

I recently ran into "Data Corruption" issues in most of our primary tables a couple of times, I ran CHECKDB, brought the DB into single user mode to execute REPAIR_ALLOW_DATA_LOSS on the tables reported issues. Now all clean.

Question is: what would you recommend to diagnose internals including data pages?

Any recommendation is greatly appreciated.

Mahidhar Vattem
MCDBA, MBA

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-11-30 : 16:32:35

CheckDB is the best way to pick up corruption and diagnose problems. In general, restoring from a backup is preferred over repair because, as it's name indicates, repair_allow_data_loss will lose data. It is also very likely to leave the database in a transactionally-inconsistent state (Records violating foreign key relationships)

If you're getting corruption repeatedly, take a look at your IO system. It is very likely the cause.

--
Gail Shaw
SQL Server MVP

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2008-11-30 : 16:56:27

Mahidhar - are you on SQL 2005? If so, make sure you have page checksums enabled - this will give you as early an indication as possible of I/O susbsystem problems. You should also have a regular job running DBCC CHECKDB and monitor the output.

Given that you've just had corruption, run diagnostics on the I/O subsystem, check all drivers and firmware are up-to-date, consider downloading and running SQLIOSim.

And get a backup strategy that allows restore rather than repair.

Thanks

Paul S. Randal, Managing Director, SQLskills.com (www.SQLskills.com/blogs/paul)
SQL Server MVP, Contributing Editor of TechNet Magazine
Author of SQL 2005 DBCC CHECKDB/repair code
Author & Instructor of Microsoft Certified Master - Database course

hotfix4sql
Starting Member

2 Posts

Posted - 2008-12-02 : 17:31:49

Gila, In theory use the latest backup and restore, question is what if that backup has corrupted data? I am back to square one. I take 4 full backups of the database and then tape bakups, I guess I could try restore each one of them and run CHECKDB.

Corruption started happening since after 10/24 Microsoft's Emergency patch on Windows 2003 Servers, assuming the patch upgraded necessary dlls.

As Paul suggested, I scheduled a job to execute CHECKDB on Clone database early in the morning, so I come to work and troubleshoot while I am at work. I am working close to 60-70 hours a week now a days, I guess that is DBA's life.

Paul, our legacy system is on SQL Server 2000 SP4 + Cumulative 2187.

Thank you guys for your replies.

Mahidhar

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-12-02 : 18:43:12

quote:
Originally posted by hotfix4sql

Gila, In theory use the latest backup and restore, question is what if that backup has corrupted data?

Same as if you needed to restore the last backup and it was unrestorable. Restore the one before and apply tran logs up to date.

That's why it's recommended to run checkDB in a job on a regular basis, so that you know exactly when corruption hits and so that you can easily restore from it.

The problem with repair is that, as its name implies, it loses data. It's hard to tell what it's going to discard before the run and what damage (in terms of orphaned rows) it leaves behind.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources