Database Suspect

[Yusuf]

Ini ada sumbangan dari rekan kita yg sudah mengalaminya:

Untuk memperbaiki database yg suspect, jalankan perintah-perintah ini pada Query Analyzer atau SQL Server Management Studio:

Code Select Expand


  EXEC sp_resetstatus 'yourDBname';
  ALTER DATABASE yourDBname SET EMERGENCY
  DBCC checkdb('yourDBname')
  ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
  DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
  ALTER DATABASE yourDBname SET MULTI_USER


Sumber: http://gargmanoj.wordpress.com/2008/07/17/how-to-repair-a-sql-server-2005-suspect-database/

[d3k4]

Mantab...
makasih BOS..
jadi nambah ilmu....

[Yusuf]

yourDBname diganti jadi otomax:

Code Select Expand


  EXEC sp_resetstatus 'otomax';
  ALTER DATABASE otomax SET EMERGENCY
  DBCC checkdb('otomax')
  ALTER DATABASE otomax SET SINGLE_USER WITH ROLLBACK IMMEDIATE
  DBCC CheckDB ('otomax', REPAIR_ALLOW_DATA_LOSS)
  ALTER DATABASE otomax SET MULTI_USER


[bralingtronik]

Mantabbb BIG BOSS  ;D ;D
bookmark dulu ahhh, belum ada kasus neehh

[didiek]

finally... berbagi uang, uang habis.... berbagi ilmu, ilmu nambah teruuss...  ;D ;D

[titanium_ponsel]

kok ndk bisa ya ???
replay nya
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '''.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '''.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '''.
knp ya???

[Yusuf]

tanda ' nya coba bos hapus, ketik ulang...

[titanium_ponsel]

The suspect flag on the database "otomax" is already reset.
DBCC results for 'Otomax'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
DBCC results for 'sys.sysrowsetcolumns'.
There are 834 rows in 8 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 120 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sysallocunits'.
There are 131 rows in 2 pages for object "sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syshobtcolumns'.
There are 834 rows in 10 pages for object "sys.syshobtcolumns".
DBCC results for 'sys.syshobts'.
There are 120 rows in 1 pages for object "sys.syshobts".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysserefs'.
There are 131 rows in 1 pages for object "sys.sysserefs".
DBCC results for 'sys.sysowners'.
There are 14 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 120 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 166 rows in 4 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 663 rows in 12 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for 'sys.sysxprops'.
There are 1 rows in 1 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 27 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 332 rows in 6 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 465 rows in 3 pages for object "sys.sysiscols".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysobjvalues'.
There are 393 rows in 118 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 14 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 157 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 105 rows in 1 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysdbfiles'.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for 'sys.sysguidrefs'.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for 'sys.sysqnames'.
There are 91 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 93 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 97 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 17 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 0 rows in 0 pages for object "sys.sysbinsubobjs".
DBCC results for 'jawaban'.
There are 124 rows in 2 pages for object "jawaban".
DBCC results for 'transaksi'.
There are 1794961 rows in 45225 pages for object "transaksi".
DBCC results for 'mutasi'.
There are 3054402 rows in 33120 pages for object "mutasi".
DBCC results for 'parameter'.
There are 210 rows in 3 pages for object "parameter".
DBCC results for 'pengirim'.
There are 765 rows in 4 pages for object "pengirim".
DBCC results for 'produk'.
There are 129 rows in 1 pages for object "produk".
DBCC results for 'sms_sender'.
There are 2 rows in 1 pages for object "sms_sender".
DBCC results for 'operator'.
There are 15 rows in 1 pages for object "operator".
DBCC results for 'inbox'.
There are 6880967 rows in 127211 pages for object "inbox".
DBCC results for 'terminal'.
There are 33 rows in 1 pages for object "terminal".
DBCC results for 'biaya'.
There are 63 rows in 1 pages for object "biaya".
DBCC results for 'tiket_deposit'.
There are 24463 rows in 138 pages for object "tiket_deposit".
DBCC results for 'data_bank'.
There are 24693 rows in 386 pages for object "data_bank".
DBCC results for 'level'.
There are 20 rows in 1 pages for object "level".
DBCC results for 'komisi'.
There are 172 rows in 1 pages for object "komisi".
DBCC results for 'hlr'.
There are 1 rows in 1 pages for object "hlr".
DBCC results for 'log_sistem'.
There are 82605 rows in 1239 pages for object "log_sistem".
DBCC results for 'sms_center'.
There are 19 rows in 1 pages for object "sms_center".
DBCC results for 'produk_level'.
There are 144 rows in 1 pages for object "produk_level".
DBCC results for 'outbox'.
There are 7238473 rows in 169880 pages for object "outbox".
DBCC results for 'parsing'.
There are 268 rows in 6 pages for object "parsing".
DBCC results for 'modul'.
There are 41 rows in 1 pages for object "modul".
DBCC results for 'reseller'.
There are 392 rows in 7 pages for object "reseller".
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
DBCC results for 'fisik'.
There are 60 rows in 1 pages for object "fisik".
DBCC results for 'auto_respon'.
There are 14 rows in 1 pages for object "auto_respon".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'Otomax'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC results for 'Otomax'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
DBCC results for 'sys.sysrowsetcolumns'.
There are 834 rows in 8 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 120 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sysallocunits'.
There are 131 rows in 2 pages for object "sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syshobtcolumns'.
There are 834 rows in 10 pages for object "sys.syshobtcolumns".
DBCC results for 'sys.syshobts'.
There are 120 rows in 1 pages for object "sys.syshobts".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysserefs'.
There are 131 rows in 1 pages for object "sys.sysserefs".
DBCC results for 'sys.sysowners'.
There are 14 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 120 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 166 rows in 4 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 663 rows in 12 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for 'sys.sysxprops'.
There are 1 rows in 1 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 27 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 332 rows in 6 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 465 rows in 3 pages for object "sys.sysiscols".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysobjvalues'.
There are 393 rows in 118 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 14 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 157 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 105 rows in 1 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysdbfiles'.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for 'sys.sysguidrefs'.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for 'sys.sysqnames'.
There are 91 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 93 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 97 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 17 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 0 rows in 0 pages for object "sys.sysbinsubobjs".
DBCC results for 'jawaban'.
There are 124 rows in 2 pages for object "jawaban".
DBCC results for 'transaksi'.
There are 1794961 rows in 45225 pages for object "transaksi".
DBCC results for 'mutasi'.
There are 3054402 rows in 33120 pages for object "mutasi".
DBCC results for 'parameter'.
There are 210 rows in 3 pages for object "parameter".
DBCC results for 'pengirim'.
There are 765 rows in 4 pages for object "pengirim".
DBCC results for 'produk'.
There are 129 rows in 1 pages for object "produk".
DBCC results for 'sms_sender'.
There are 2 rows in 1 pages for object "sms_sender".
DBCC results for 'operator'.
There are 15 rows in 1 pages for object "operator".
DBCC results for 'inbox'.
There are 6880967 rows in 127211 pages for object "inbox".
DBCC results for 'terminal'.
There are 33 rows in 1 pages for object "terminal".
DBCC results for 'biaya'.
There are 63 rows in 1 pages for object "biaya".
DBCC results for 'tiket_deposit'.
There are 24463 rows in 138 pages for object "tiket_deposit".
DBCC results for 'data_bank'.
There are 24693 rows in 386 pages for object "data_bank".
DBCC results for 'level'.
There are 20 rows in 1 pages for object "level".
DBCC results for 'komisi'.
There are 172 rows in 1 pages for object "komisi".
DBCC results for 'hlr'.
There are 1 rows in 1 pages for object "hlr".
DBCC results for 'log_sistem'.
There are 82605 rows in 1239 pages for object "log_sistem".
DBCC results for 'sms_center'.
There are 19 rows in 1 pages for object "sms_center".
DBCC results for 'produk_level'.
There are 144 rows in 1 pages for object "produk_level".
DBCC results for 'outbox'.
There are 7238473 rows in 169880 pages for object "outbox".
DBCC results for 'parsing'.
There are 268 rows in 6 pages for object "parsing".
DBCC results for 'modul'.
There are 41 rows in 1 pages for object "modul".
DBCC results for 'reseller'.
There are 392 rows in 7 pages for object "reseller".
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
DBCC results for 'fisik'.
There are 60 rows in 1 pages for object "fisik".
DBCC results for 'auto_respon'.
There are 14 rows in 1 pages for object "auto_respon".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'Otomax'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

kok ada error nya ya???

[AbiTamim]

@ atasku
Memang ada pesan Error tapi sukses, suspect hilang
tested pada pasien

[autronik]

ikut liat perkembangan dolo..

[zurigh]

Database Suspect apa ya ??? ???
kesini ga ngerti http://gargmanoj.wordpress.com/2008/07/17/how-to-repair-a-sql-server-2005-suspect-database/
maklum newbie jarang makan roti ;D

[idzoel]

mohon dicek pak bigboss, beberapa kali benerin database otomax yg bermasalah ( terutama yg salah installan dari awal => maksudnya folder otomax.exe dan file .mdf / .ldf nya jadi satu folder), saya coba pake script ini, alhamdulillah sukses terus  ;D,

Code Select Expand


USE master;
GO
ALTER DATABASE otomax SET EMERGENCY
GO
ALTER DATABASE otomax SET SINGLE_USER
GO
DBCC CHECKDB (otomax, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO

USE otomax;

DBCC CHECKDB WITH NO_INFOMSGS;
GO

USE otomax
ALTER DATABASE [otomax] SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE ([otomax_log])
GO
ALTER DATABASE [otomax] SET RECOVERY FULL
DBCC SHRINKFILE (otomax, 1) WITH NO_INFOMSGS

ALTER DATABASE otomax SET MULTI_USER



kalo ada yg salah mohon dikoreksi ... maklum nubie

[skullz]

penyakitnya apa ya??
sudah coba semua trik di atas hasilnya masih nihil...
harus gimana ya  ??? ??? ???
di tab Produk juga begitu...

[skullz]

oh iya, nambah produk juga ga mau
Mmmm Sepertinya kasus ini lumayan berat :( :( :(
didatabase sich ga ada indikasi suspect
Help me pleaseeeeee

[skullz]

case solved
dengan cara menghapus

;D ;D ;D
bagi yang mempunyai masaalah yang sama jangan lupa backup DB sebelum modifikasi tabel

[satelitpulsa]

finally... berbagi uang, uang habis.... berbagi ilmu, ilmu nambah teruuss...  ;D ;D

Setuju...
new bie mw blajar dulu sama all big boss..

[uyan_kids]

tolong bantuan nya bos


http://i1193.photobucket.com/albums/aa360/uyan_kids/ERORQUERY1.jpg

[uyan_kids]

tolong bantuan nya bos


http://i1193.photobucket.com/albums/aa360/uyan_kids/ERORQUERY1.jpg

itu yang saya alami bos, tolong bantuan nya...help me

[Tanalodu]

Ini ada sumbangan dari rekan kita yg sudah mengalaminya:

Untuk memperbaiki database yg suspect, jalankan perintah-perintah ini pada Query Analyzer atau SQL Server Management Studio:

Code Select Expand


  EXEC sp_resetstatus 'yourDBname';
  ALTER DATABASE yourDBname SET EMERGENCY
  DBCC checkdb('yourDBname')
  ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
  DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
  ALTER DATABASE yourDBname SET MULTI_USER


Sumber: http://gargmanoj.wordpress.com/2008/07/17/how-to-repair-a-sql-server-2005-suspect-database/

Nambah dikit...

You should keep one thing in mind while using the above queries that the repair mode used here , REPAIR_ALLOW_DATA_LOSS, is a one way operation i.e. once the database is repaired all the actions performed by these queries can't be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database before executing above mentioned queries.

[deydi]

tanda ' nya coba bos hapus, ketik ulang...

pakai ini

EXEC sp_resetstatus 'otomax';
  ALTER DATABASE otomax SET EMERGENCY
  DBCC checkdb('otomax')
  ALTER DATABASE otomax SET SINGLE_USER WITH ROLLBACK IMMEDIATE
  DBCC CheckDB ('otomax', REPAIR_ALLOW_DATA_LOSS)
  ALTER DATABASE otomax SET MULTI_USER

hasilnya :

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '''.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '''.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '''.



saya udah coba hapus '

EXEC sp_resetstatus otomax;
ALTER DATABASE otomax SET EMERGENCY
BCC checkdb(otomax)
ALTER DATABASE otomax SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (otomax, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE otomax SET MULTI_USER

hasilnya

Msg 102, Level 15, State 6, Line 3
Incorrect syntax near 'checkdb'.