February 10, 2009 at 7:05 am
Hi,
I'm (even) newby with SQL Server. I have to administer an old SQL 2000 server (SP3) since short time.
I have some errors in the errorlog. I mean the first messages group is a bug corrected with the SP4 (thank you for your confirmation).
For the second messages group, which shows errors in MSDB, I ran an integrity check job on this db (excluding indexes). The results are at the end of this post.
I tried to run the same job including indexes and repair, but the job failled because it was unabble to put the db in single-mode.
Please, could somebody help me, and indicate the way to correct this problem. You have to know that the only backups I have for msdb have been made with these errors, so, I mean I can't use them to restore ...
Maybe could I just truncate those two table, and recreate indexes ? but there are some dependances ...
Thank you in advance for your help ...
JM
ERRORLOG :
=========
--------- First block, (correction with the SP4 ?) -----------
2009-02-09 10:13:10.82spid58Getpage: bstat=0x9, sstat=0x2800, cache
2009-02-09 10:13:10.82spid58pageno is/should be:objid is/should be:
2009-02-09 10:13:10.82spid58(1:1705)/(1:1705)2073058421/2069582411
2009-02-09 10:13:10.82spid58... IAM indicates that page is allocated to this object
2009-02-09 10:13:10.82spid58Erreur : 605, Gravité : 21, État : 1.
2009-02-09 10:13:10.82spid58Erreur : 605, Gravité : 21, État : 1
2009-02-09 10:13:10.82spid58Attempt to fetch logical page (1:1705) in database 'msdb' belongs to object 'sysjobhistory', not to object 'backupmediafamily'..
-----------------------------------------------------------
--------- Second block (sorry, messages are in french ...) ---
2009-02-10 02:03:44.88spid53Erreur : 644, Gravité : 21, État : 3
2009-02-10 02:03:44.88spid53Impossible de trouver l'entrée d'index pour le RID = '169e1000100' de la page d'index (1:1895), index ID = 0, base de données 'msdb'..
2009-02-10 02:03:44.88spid53Erreur : 644, Gravité : 21, État : 3
2009-02-10 02:03:44.88spid53Could not find the index entry for RID '169e1000100' in index page (1:1895), index ID 0, database 'msdb'..
2009-02-10 02:03:44.97spid53Erreur : 644, Gravité : 21, État : 3
2009-02-10 02:03:44.97spid53Impossible de trouver l'entrée d'index pour le RID = '169e1000100' de la page d'index (1:1895), index ID = 0, base de données 'msdb'..
2009-02-10 02:03:44.97spid53Erreur : 644, Gravité : 21, État : 3
2009-02-10 02:03:44.97spid53Could not find the index entry for RID '169e1000100' in index page (1:1895), index ID 0, database 'msdb'..
-----------------------------------------------------------
--------- log for the integrity check job (in french too ...) ---
Utilitaire Microsoft (R) SQLMaint (Unicode), Version Connect‚ au serveur SQL xxxx en tant que NT AUTHORITY\SYSTEM (approuvé)
Début du plan de maintenance PM Bases Systemes au 09/02/2009 11:06:14
[1] Base de données master : Vérification de la liaison des données...
** Temps d'exécution : 0 h, 0 min, 1 s **
[2] Base de données model : Vérification de la liaison des données...
** Temps d'exécution : 0 h, 0 min, 1 s **
[3] Base de données msdb : Vérification de la liaison des données...
[Microsoft SQL-DMO (ODBC SQLState : 42000)] Erreur 8978 : [Microsoft][ODBC SQL Server Driver][SQL Server]Erreur de table : Objet ID = 18099105, index ID = 1. La page (1:1407) n'a pas de référence dans la page précédente (1:1406). Possibilité d'un problème de liaison de chaîne.
[Microsoft][ODBC SQL Server Driver][SQL Server]Erreur de table : Objet ID = 2073058421, index ID = 1. La valeur de clé haute sur la page (1:1521) (niveau 0) n'est pas inférieure … la valeur de clé basse dans la page parente (0:1), slot 0, de la page suivante (1:1895).
[Microsoft][ODBC SQL Server Driver][SQL Server]Erreur de table : Objet ID = 2073058421, index ID = 1. Le lien précédent (1:1521) sur la page (1:1895) ne correspond pas … la page précédente (1:1094) que la page parente (1:2652), slot 1, attend pour cette page.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB a trouv‚ 0 erreurs d'allocation et 1 erreurs de cohérence dans la table 'backupfile' (objet ID = 18099105).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB a trouv‚ 0 erreurs d'allocation et 2 erreurs de cohérence dans la table 'sysjobhistory' (objet ID = 2073058421).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB a trouv‚ 0 erreurs d'allocation et 3 erreurs de cohérence dans la base de données 'msdb'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_rebuild est le minimum de niveau de réparation pour les erreurs trouvés par DBCC CHECKDB (msdb noindex).
Les erreurs suivantes ont été détectées :
[Microsoft][ODBC SQL Server Driver][SQL Server]Erreur de table : Objet ID = 18099105, index ID = 1. La page (1:1407) n'a pas de référence dans la page précédente (1:1406). Possibilité d'un problème de liaison de chaîne.
[Microsoft][ODBC SQL Server Driver][SQL Server]Erreur de table : Objet ID = 2073058421, index ID = 1. La valeur de clé haute sur la page (1:1521) (niveau 0) n'est pas inférieure … la valeur de clé basse dans la page parente (0:1), slot 0, de la page suivante (1:1895).
[Microsoft][ODBC SQL Server Driver][SQL Server]Erreur de table : Objet ID = 2073058421, index ID = 1. Le lien précédent (1:1521) sur la page (1:1895) ne correspond pas … la page précédente (1:1094) que la page parente (1:2652), slot 1, attend pour cette page.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB a trouv‚ 0 erreurs d'allocation et 1 erreurs de cohérence dans la table 'backupfile' (objet ID = 18099105).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB a trouv‚ 0 erreurs d'allocation et 2 erreurs de cohérence dans la table 'sysjobhistory' (objet ID = 2073058421).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB a trouv‚ 0 erreurs d'allocation et 3 erreurs de cohérence dans la base de données 'msdb'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_rebuild est le minimum de niveau de réparation pour les erreurs trouvés par DBCC CHECKDB (msdb noindex).
** Temps d'ex‚cution : 0 h, 0 min, 1 s **
Suppression des anciens rapports texte... 0 fichier(s) supprimé(s).
Fin du plan de maintenance PM Bases Systemes au 09/02/2009 11:06:16
SQLMAINT.EXE Code de sortie du processus : 1 (échec)
-----------------------------------------------------------
February 20, 2009 at 8:29 am
Hi
nobody to help me ? 🙁
February 20, 2009 at 8:32 am
I'd love to, but I don't read French. 🙁
Any chance you can back the DB up, restore it on an english server (as a different database, don't overwrite MSDB) and run the following on it?
DBCC CHECKDB (< Database Name > ) WITH NO_INFOMSGS, ALL_ERRORMSGS
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
February 20, 2009 at 8:59 am
Salut, je suis bilingue et je vais faire le pont entre Gail et toi. Je voudrais bien te guider mais c'est Gail l'experte dans ce domaine.
Gail : He's getting errors in the clustered indexes. He says that the backups he has also have this error. As far as I can see, the errors are not in absolutely critical tables. From what I understand of your article, the best thing to do right now would be to rebuild msdb, then reimport the data.
Can you tell him what to do so that I can translate the indications back to him.
TIA.
JMF : J'ai posé des questions à Gail, je vais te traduire la marche à suivre. Je crois que tu auras à faire un rebuild de msdb et ensuite réimporter les données. Attends la confirmation de Gail avant de faire quoi que ce soit.
Bonne chance.
February 20, 2009 at 9:12 am
Ninja, can you please do a rough translation of these three errors?
La page (1:1407) n'a pas de référence dans la page précédente (1:1406). Possibilité d'un problème de liaison de chaîne.
La valeur de clé haute sur la page (1:1521) (niveau 0) n'est pas inférieure … la valeur de clé basse dans la page parente (0:1), slot 0, de la page suivante (1:1895).
Le lien précédent (1:1521) sur la page (1:1895) ne correspond pas … la page précédente (1:1094) que la page parente (1:2652), slot 1, attend pour cette page.
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
February 20, 2009 at 9:24 am
Hi Gail and Ninja !
thank's a lot for wotrking on my problem ...
I've changed the language of my login, and ran the dbcc command on the MSDB. Here is the output :
Msg 8978, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Object ID 18099105, index ID 1. Page (1:1407) is missing a reference from previous page (1:1406). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'backupfile' (object ID 18099105).
Msg 8951, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Table 'backupmediaset' (ID 2037582297). Missing or invalid key in index 'backupmediasetuuid' (ID 2) for the row:
Msg 8955, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Data row (1:1870:28) identified by (RID = (1:1870:28) media_set_id = 2066) has index values (media_uuid = 00000000-0000-0000-0000-000000000000h½[ and media_set_id = NULL).
Msg 8951, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Table 'backupmediaset' (ID 2037582297). Missing or invalid key in index 'backupmediasetuuid' (ID 2) for the row:
Msg 8955, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Data row (1:1870:29) identified by (RID = (1:1870:29) media_set_id = 2067) has index values (media_uuid = 00000000-0000-0000-0000-000000000000h½[ and media_set_id = NULL).
Msg 8951, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Table 'backupmediaset' (ID 2037582297). Missing or invalid key in index 'backupmediasetuuid' (ID 2) for the row:
Msg 8955, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Data row (1:1870:30) identified by (RID = (1:1870:30) media_set_id = 2068) has index values (media_uuid = 00000000-0000-0000-0000-000000000000h½[ and media_set_id = NULL).
Msg 8951, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Table 'backupmediaset' (ID 2037582297). Missing or invalid key in index 'backupmediasetuuid' (ID 2) for the row:
Msg 8955, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Data row (1:1870:31) identified by (RID = (1:1870:31) media_set_id = 2069) has index values (media_uuid = 00000000-0000-0000-0000-000000000000h½[ and media_set_id = NULL).
CHECKDB found 0 allocation errors and 4 consistency errors in table 'backupmediaset' (object ID 2037582297).
Msg 8925, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Cross object linkage: Page (1:42), slot 4, in object ID 2069582411, index ID 2, refers to page (1:1705), slot 0, in object ID 2073058421, index ID 1.
Msg 2533, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Page (1:1705) allocated to object ID 2069582411, index ID 2 was not seen. Page may be invalid or have incorrect object ID information in its header.
Msg 8935, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Object ID 2069582411, index ID 2. The previous link (1:1707) on page (1:1765) does not match the previous page (1:1705) that the parent (1:42), slot 5 expects for this page.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'backupmediafamily' (object ID 2069582411).
Msg 2534, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Page (1:1705) with object ID 2073058421, index ID 0 in its header is allocated by another object.
Msg 8981, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Object ID 2073058421, index ID 1. The next pointer of (1:1705) refers to page (1:1495). Neither (1:1495) nor its parent were encountered. Possible bad chain linkage.
Msg 8978, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Object ID 2073058421, index ID 1. Page (1:1705) is missing a reference from previous page (1:1490). Possible chain linkage problem.
Msg 8935, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Object ID 2073058421, index ID 1. The previous link (1:3355) on page (1:1895) does not match the previous page (1:1094) that the parent (1:2652), slot 1 expects for this page.
Msg 8934, Level 16, State 2, Server PSNTP048\SAGE, Line 1
Table error: Object ID 2073058421, index ID 1. The high key value on page (1:3355) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:1895).
Msg 8934, Level 16, State 3, Server PSNTP048\SAGE, Line 1
Table error: Object ID 2073058421, index ID 1. The high key value on page (1:3355) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:1895).
CHECKDB found 0 allocation errors and 6 consistency errors in table 'sysjobhistory' (object ID 2073058421).
Msg 8951, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Table 'backupset' (ID 2117582582). Missing or invalid key in index 'backupsetuuid' (ID 2) for the row:
Msg 8955, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Data row (1:1506:21) identified by (RID = (1:1506:21) backup_set_id = 2232) has index values (backup_set_uuid = F78B4F82-3628-4BFB-9464-11BF013A2DA5h½[ and backup_set_id = 2232).
Msg 8951, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Table 'backupset' (ID 2117582582). Missing or invalid key in index 'backupsetuuid' (ID 2) for the row:
Msg 8955, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Data row (1:1506:23) identified by (RID = (1:1506:23) backup_set_id = 2234) has index values (backup_set_uuid = 2217EC66-B8A4-45D3-AA4B-02C3930F6377h½[ and backup_set_id = 2234).
CHECKDB found 0 allocation errors and 2 consistency errors in table 'backupset' (object ID 2117582582).
CHECKDB found 0 allocation errors and 16 consistency errors in database 'msdb'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (msdb ).
1>
February 20, 2009 at 9:25 am
La page (1:1407) n'a pas de référence dans la page précédente (1:1406). Possibilité d'un problème de liaison de chaîne.
The page(1:1407) doesn't have any refference to the previous page (1:1406). Possible broken link.
La valeur de clé haute sur la page (1:1521) (niveau 0) n'est pas inférieure … la valeur de clé basse dans la page parente (0:1), slot 0, de la page suivante (1:1895).
Upper key on page (1:1521) (level 0) is not inferior... the value of the low key of the parent page (0:1), slot 0, of the next page (1:1895)
Le lien précédent (1:1521) sur la page (1:1895) ne correspond pas … la page précédente (1:1094) que la page parente (1:2652), slot 1, attend pour cette page.
The previous link (1:1521) on the page (1:1895) does not correspond... the previous page (1:1094) that the parent page (1:2652), slot 1 expects for that page.
February 20, 2009 at 9:26 am
JMF (2/20/2009)
Hi Gail and Ninja !thank's a lot for wotrking on my problem ...
I've changed the language of my login, and ran the dbss command on the MSDB. Here is the output :
Msg 8978, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Object ID 18099105, index ID 1. Page (1:1407) is missing a reference from previous page (1:1406). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'backupfile' (object ID 18099105).
Msg 8951, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Table 'backupmediaset' (ID 2037582297). Missing or invalid key in index 'backupmediasetuuid' (ID 2) for the row: Msg 8955, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Data row (1:1870:28) identified by (RID = (1:1870:28) media_set_id = 2066) has index values (media_uuid = 00000000-0000-0000-0000-000000000000h½[ and media_set_id = NULL).
Msg 8951, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Table 'backupmediaset' (ID 2037582297). Missing or invalid key in index 'backupmediasetuuid' (ID 2) for the row: Msg 8955, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Data row (1:1870:29) identified by (RID = (1:1870:29) media_set_id = 2067) has index values (media_uuid = 00000000-0000-0000-0000-000000000000h½[ and media_set_id = NULL).
Msg 8951, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Table 'backupmediaset' (ID 2037582297). Missing or invalid key in index 'backupmediasetuuid' (ID 2) for the row: Msg 8955, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Data row (1:1870:30) identified by (RID = (1:1870:30) media_set_id = 2068) has index values (media_uuid = 00000000-0000-0000-0000-000000000000h½[ and media_set_id = NULL).
Msg 8951, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Table 'backupmediaset' (ID 2037582297). Missing or invalid key in index 'backupmediasetuuid' (ID 2) for the row:
Msg 8955, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Data row (1:1870:31) identified by (RID = (1:1870:31) media_set_id = 2069) has index values (media_uuid = 00000000-0000-0000-0000-000000000000h½[ and media_set_id = NULL).
CHECKDB found 0 allocation errors and 4 consistency errors in table 'backupmediaset' (object ID 2037582297).
Msg 8925, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Cross object linkage: Page (1:42), slot 4, in object ID 2069582411, index ID 2, refers to page (1:1705), slot 0, in object ID 2073058421, index ID 1.
Msg 2533, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Page (1:1705) allocated to object ID 2069582411, index ID 2 was not seen. Page may be invalid or have incorrect object ID information in its header.
Msg 8935, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Object ID 2069582411, index ID 2. The previous link (1:1707) on page (1:1765) does not match the previous page (1:1705) that the parent (1:42), slot 5 expects for this page.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'backupmediafamily' (object ID 2069582411).
Msg 2534, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Page (1:1705) with object ID 2073058421, index ID 0 in its header is allocated by another object.
Msg 8981, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Object ID 2073058421, index ID 1. The next pointer of (1:1705) refers to page (1:1495). Neither (1:1495) nor its parent were encountered. Possible bad chain linkage.
Msg 8978, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Object ID 2073058421, index ID 1. Page (1:1705) is missing a reference from previous page (1:1490). Possible chain linkage problem.
Msg 8935, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Object ID 2073058421, index ID 1. The previous link (1:3355) on page (1:1895) does not match the previous page (1:1094) that the parent (1:2652), slot 1 expects for this page.
Msg 8934, Level 16, State 2, Server PSNTP048\SAGE, Line 1
Table error: Object ID 2073058421, index ID 1. The high key value on page (1:3355) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:1895).
Msg 8934, Level 16, State 3, Server PSNTP048\SAGE, Line 1
Table error: Object ID 2073058421, index ID 1. The high key value on page (1:3355) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:1895).
CHECKDB found 0 allocation errors and 6 consistency errors in table 'sysjobhistory' (object ID 2073058421).
Msg 8951, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Table 'backupset' (ID 2117582582). Missing or invalid key in index 'backupsetuuid' (ID 2) for the row: Msg 8955, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Data row (1:1506:21) identified by (RID = (1:1506:21) backup_set_id = 2232) has index values (backup_set_uuid = F78B4F82-3628-4BFB-9464-11BF013A2DA5h½[ and backup_set_id = 2232).
Msg 8951, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Table error: Table 'backupset' (ID 2117582582). Missing or invalid key in index 'backupsetuuid' (ID 2) for the row: Msg 8955, Level 16, State 1, Server PSNTP048\SAGE, Line 1
Data row (1:1506:23) identified by (RID = (1:1506:23) backup_set_id = 2234) has index values (backup_set_uuid = 2217EC66-B8A4-45D3-AA4B-02C3930F6377h½[ and backup_set_id = 2234).
CHECKDB found 0 allocation errors and 2 consistency errors in table 'backupset' (object ID 2117582582).
CHECKDB found 0 allocation errors and 16 consistency errors in database 'msdb'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (msdb ).
1>
Cool idea... never thought of doing that!!!
I'm sure Gail will know exactly what to do from that text. Let me know if you need any more translations.
February 20, 2009 at 9:49 am
ok, Ninja
thank you, and have a good WE ...
February 20, 2009 at 1:35 pm
JMF (2/20/2009)
I've changed the language of my login, and ran the dbcc command on the MSDB.
That's a lot easier.
Ok, this is not a good situation. You have corruption that cannot be repaired without losing data. Since you have no clean backups, we're going to have to repair or rebuild MSDB completely.
You have two options.
You can run Checkdb with the repair_allow_data_loss option. This will delete data from five tables: backupfile, backupmediaset, backupmediafamily, sysjobhistory, backupset.
I don't know what problems this could cause with backups in the future.
You can script out all your jobs and then search for the article on MSDN that explains how to rebuild MSDB. Once rebuilt you can run all the scripted jobs to get the jobs back.
This is more work, but it is safer.
Your choice.
Once you have MSDB clean, check the windows event logs to see if you can find anything that may have cause this. Typically corruption is due to IO issues.
Put a job in place to do regular consistency checks on all databases (except TempDB) So that if this happens again you can catch it early.
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
February 20, 2009 at 1:46 pm
And to add to what Gail said, also upgrade to SP4 to pick up fixexs for a number of corruption-causing bugs.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
February 20, 2009 at 2:09 pm
GilaMonster (2/20/2009)
JMF (2/20/2009)
I've changed the language of my login, and ran the dbcc command on the MSDB.That's a lot easier.
Ok, this is not a good situation. You have corruption that cannot be repaired without losing data. Since you have no clean backups, we're going to have to repair or rebuild MSDB completely.
You have two options.
You can run Checkdb with the repair_allow_data_loss option. This will delete data from five tables: backupfile, backupmediaset, backupmediafamily, sysjobhistory, backupset.
I don't know what problems this could cause with backups in the future.
You can script out all your jobs and then search for the article on MSDN that explains how to rebuild MSDB. Once rebuilt you can run all the scripted jobs to get the jobs back.
This is more work, but it is safer.
Your choice.
Once you have MSDB clean, check the windows event logs to see if you can find anything that may have cause this. Typically corruption is due to IO issues.
Put a job in place to do regular consistency checks on all databases (except TempDB) So that if this happens again you can catch it early.
Would it be prudent to rerun all the backups after the rebuild is done so that any data loss cannot screw something up later on?
February 20, 2009 at 3:14 pm
Ninja's_RGR'us (2/20/2009)
Would it be prudent to rerun all the backups after the rebuild is done so that any data loss cannot screw something up later on?
Repair or rebuild?
Repair, definitely.
Rebuild is going to leave a clean (empty) MSDB. No backup history, but no possibility for problems.
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
February 20, 2009 at 6:14 pm
I meant repair. I was reffering to losing data in the backupsets table. If you start with a fresh set of backups once the server is back online, can it avoid restoring troubles later on?
February 21, 2009 at 2:20 am
Ninja's_RGR'us (2/20/2009)
If you start with a fresh set of backups once the server is back online, can it avoid restoring troubles later on?
I would think so. I'm not sure what those tables are used for.
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
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply