Question about master DB rebuild

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

Question about master DB rebuild

Author

Topic

vbenkert
Starting Member

12 Posts

Posted - 2007-09-26 : 11:53:55

I've been in support for over ten years, but have had minimal exposure to SQL. I have a client who is running SQL 2005 (as part of SBS Premium), who experienced a server crash due to the database "administrator" filling up the C: drive with manual SQL backups.

Anyway, if I go into SQL Management Studio and try to pull up properties of any database I get a "cannot show requested dialog" error. The only other issue that I've found is that a maintenance plan doesn't quite work correctly -- It will backup their database, but not delete old copies off the hard drive. Otherwise, SQL seems to be running okay.

I have come to the conclusion that I probably need to rebuild the master database. I have tried restoring the master database from a version prior to the crash, but I still have the same issue. I believe the rebuild should be fairly easy, but just don't have a full understanding of what it will affect. They have one custom application that uses a single SQL database and very little security customization (the custom application uses one Windows AD account to access the database).

Basically, I'm wondering:

1. Can someone summarize what is contained in the master database
2. I assume that I'm looking at downtime on their custom application/database - Would this be correct?
3. Will this affect their database at all?
4. How long does the database rebuild process take?
5. I understand that in SQL 2005, the rebuild process it part of the setup on the CD. If the problem is with SQL application files themselves, will this process restore any corrupt program files?

Thanks in advance for any advice,

Vishnu
Weston Technology Solutions
Bend, OR

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 12:05:06

"It will backup their database, but not delete old copies off the hard drive"

Is this SQL2000? if so its a common problem. One suggested solution is to remake the Maintenance Plan from scratch, as it may be related to editing the Plan that causes this to happen.

For SQL2005 there ere all sorts of bugs in the maintenance plans prior to SP2. Install SP2 and then you should probably delete and remake the maintenance plans.

"I have come to the conclusion that I probably need to rebuild the master database"

That sounds VERY drastic. I suggest avoiding that if you can.

I would have expected that making some disk space available, and stopping/restarting the SQL Service should be enough.

Your problem with the master database is that you won't know what MIGHT have been included, though ignorance of happenstance, which IS critical to the applications

Kristen

vbenkert
Starting Member

12 Posts

Posted - 2007-09-26 : 13:07:47

No, it's SQL 2005 as stated. I have tried to re-setup the maintenance plan, but it still has the same outcome. It worked fine before the crash.

What alternative do I have to rebuilding the master database? Disk space has been freed up, SQL services run fine, but *something* is just not right. Am I heading in the wrong direction? I've searched on the error that I receive, and it doesn't tell me anything (it's a very generic error). I've reapplied SQL Service Pack 2. I'm open to suggestions.....

quote:
Originally posted by Kristen

"It will backup their database, but not delete old copies off the hard drive"

Is this SQL2000? if so its a common problem. One suggested solution is to remake the Maintenance Plan from scratch, as it may be related to editing the Plan that causes this to happen.

For SQL2005 there ere all sorts of bugs in the maintenance plans prior to SP2. Install SP2 and then you should probably delete and remake the maintenance plans.

"I have come to the conclusion that I probably need to rebuild the master database"

That sounds VERY drastic. I suggest avoiding that if you can.

I would have expected that making some disk space available, and stopping/restarting the SQL Service should be enough.

Your problem with the master database is that you won't know what MIGHT have been included, though ignorance of happenstance, which IS critical to the applications

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-26 : 13:29:52

Yes you are heading in the wrong direction. As Kristen mentions, rebuilding master is a drastic course of action.

Run DBCC CHECKDB against all databases (DBCC CHECKDB(DatabaseNameGoesHere)). If no errors are reported, then you absolutely do not need to rebuild master.

Could you explain in detail exactly what you think isn't right with SQL Server?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

vbenkert
Starting Member

12 Posts

Posted - 2007-09-26 : 13:54:29

I just ran DBCC CHECKDB against all databases. All completed OK, other than the master database which returned the following final messages:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'.
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:1746; actual 50:109). It occurred during a read of page (1:1746) in database ID 32767 at offset 0x00000000da4000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\mssqlsystemresource.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Microsoft KB909003 (related to MSG/eventid 824) says the following:

......Note This behavior may cause a false failure to occur when you run the DBCC check command. Because the DBCC check command operates on an internal, read-only database snapshot, the command does not indicate that the database itself is damaged. The command only indicates that there is a problem with the internal, read-only database snapshot.

CAUSE
This problem may occur because of a race condition. The race condition occurs in an asynchronous non-cached write operation to an NTFS sparse file.

Umm.. Huh? I'm familiar with quite a bit of MS-lingo, but that one threw me for a loop.

As an afterthought, maybe I should've provided the full error when trying to get properties of a database:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Cannot show requested dialog.

------------------------------
ADDITIONAL INFORMATION:

Cannot show requested dialog. (SqlMgmt)

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Could not continue scan with NOLOCK due to data movement. (Microsoft SQL Server, Error: 601)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=601&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Any ideas?

Thanks again.

quote:
Originally posted by tkizer

Yes you are heading in the wrong direction. As Kristen mentions, rebuilding master is a drastic course of action.

Run DBCC CHECKDB against all databases (DBCC CHECKDB(DatabaseNameGoesHere)). If no errors are reported, then you absolutely do not need to rebuild master.

Could you explain in detail exactly what you think isn't right with SQL Server?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-26 : 14:20:14

Yikes, you've got data corruption issues in master. Post your entire DBCC CHECKDB(master) output into the Data Corruption forums here so that engineers can assist you with it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

vbenkert
Starting Member

12 Posts

Posted - 2007-09-26 : 14:29:30

Are you suggesting a start a new topic, or will someone pick up on it here?

Just in case, here's the full output:

DBCC results for 'master'.
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 693 rows in 6 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 101 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sysallocunits'.
There are 112 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 693 rows in 7 pages for object "sys.syshobtcolumns".
DBCC results for 'sys.syshobts'.
There are 101 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 112 rows in 1 pages for object "sys.sysserefs".
DBCC results for 'sys.sysowners'.
There are 20 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysdbreg'.
There are 8 rows in 1 pages for object "sys.sysdbreg".
DBCC results for 'sys.sysprivs'.
There are 1776 rows in 17 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 66 rows in 3 pages for object "sys.sysschobjs".
DBCC results for 'sys.syslogshippers'.
There are 0 rows in 0 pages for object "sys.syslogshippers".
DBCC results for 'sys.syscolpars'.
There are 558 rows in 10 pages for object "sys.syscolpars".
DBCC results for 'sys.sysxlgns'.
There are 33 rows in 1 pages for object "sys.sysxlgns".
DBCC results for 'sys.sysxsrvs'.
There are 1 rows in 1 pages for object "sys.sysxsrvs".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.sysusermsgs'.
There are 0 rows in 0 pages for object "sys.sysusermsgs".
DBCC results for 'sys.syscerts'.
There are 5 rows in 1 pages for object "sys.syscerts".
DBCC results for 'sys.sysrmtlgns'.
There are 0 rows in 0 pages for object "sys.sysrmtlgns".
DBCC results for 'sys.syslnklgns'.
There are 1 rows in 1 pages for object "sys.syslnklgns".
DBCC results for 'sys.sysxprops'.
There are 0 rows in 0 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 160 rows in 2 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 278 rows in 2 pages for object "sys.sysiscols".
DBCC results for 'sys.sysendpts'.
There are 5 rows in 1 pages for object "sys.sysendpts".
DBCC results for 'sys.syswebmethods'.
There are 0 rows in 0 pages for object "sys.syswebmethods".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysobjvalues'.
There are 241 rows in 38 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 19 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 149 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 112 rows in 1 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysdbfiles'.
There are 18 rows in 2 pages for object "sys.sysdbfiles".
DBCC results for 'sys.sysguidrefs'.
There are 1 rows in 1 pages for object "sys.sysguidrefs".
DBCC results for 'sys.syschildinsts'.
There are 0 rows in 0 pages for object "sys.syschildinsts".
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 8 rows in 1 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 'spt_fallback_db'.
There are 0 rows in 0 pages for object "spt_fallback_db".
DBCC results for 'spt_fallback_dev'.
There are 0 rows in 0 pages for object "spt_fallback_dev".
DBCC results for 'spt_fallback_usg'.
There are 0 rows in 0 pages for object "spt_fallback_usg".
DBCC results for 'sys.queue_messages_1003150619'.
There are 0 rows in 0 pages for object "sys.queue_messages_1003150619".
DBCC results for 'sys.queue_messages_1035150733'.
There are 0 rows in 0 pages for object "sys.queue_messages_1035150733".
DBCC results for 'sys.queue_messages_1067150847'.
There are 0 rows in 0 pages for object "sys.queue_messages_1067150847".
DBCC results for 'MSreplication_options'.
There are 3 rows in 1 pages for object "MSreplication_options".
DBCC results for 'spt_monitor'.
There are 1 rows in 1 pages for object "spt_monitor".
DBCC results for 'spt_values'.
There are 2346 rows in 16 pages for object "spt_values".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'.
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:1746; actual 50:109). It occurred during a read of page (1:1746) in database ID 32767 at offset 0x00000000da4000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\mssqlsystemresource.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

quote:
Originally posted by tkizer

Yikes, you've got data corruption issues in master. Post your entire DBCC CHECKDB(master) output into the Data Corruption forums here so that engineers can assist you with it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-26 : 14:33:19

Oops, I didn't realize you had posted in this forum before you knew you had data corruption already.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

vbenkert
Starting Member

12 Posts

Posted - 2007-09-26 : 14:50:12

I knew that something was/is corrupt, so I figured this would be the most appropriate forum to post to....

quote:
Originally posted by tkizer

Oops, I didn't realize you had posted in this forum before you knew you had data corruption already.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 15:23:10

"Yikes, you've got data corruption issues in master."

Just before we panic:

Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:1746; actual 50:109). It occurred during a read of page (1:1746) in database ID 32767 at offset 0x00000000da4000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\mssqlsystemresource.mdf'.

I thought mssqlsystemresource.mdf was effectively read only, and not really "opened"? (Might have confused it with something else but to do 2ith "config" though).

In a default install TEMPDB has probably been chucked on C: (and probably under C:\program files\ ... </SIGH!>), so TEMPDB might genuinely be running out of space ??

Might be worth stop/start SQL Service (or rebooting) to force TEMPDB to be recreated.

If C: is still short in space try making as much available as possible, in the short term. (The system databases can be moved to another drive, but there is little point if they a re actually corrupted, so would be worth making sure that's NOT the case if poss. as it will save a lot of effort)

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-26 : 15:31:06

Yes that database is "hidden" from us but not from SQL Server. The part that concerns me is this: "SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:1746; actual 50:109)."

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 15:52:35

Yup, I agree with that. But before that error is shown the OP gets:

"CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'."

so I reckon that either the error is genuinely in TEMPDB (which may just be disk starvation) or the error is after the aggregation of the data, and may still be in the MASTER database.

I'm a perennial optimist though

and am hopeful that its NOT in the master database.

Kristen

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-26 : 15:58:33

To the OP. If you run daily backups of the master db, you might want to make a copy of it just in case you don't get this resolved today. Otherwise your backup will contain the corruption as well tomorrow when your backup file is overwritten with today's master db.

vbenkert
Starting Member

12 Posts

Posted - 2007-10-09 : 17:31:12

I've restarted the server, which doesn't seem to help the situation. Otherwise, I've got 4GB free on the C: drive....Shouldn't that be enough?

Thanks again.

quote:
Originally posted by Kristen

"Yikes, you've got data corruption issues in master."

Just before we panic:

Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:1746; actual 50:109). It occurred during a read of page (1:1746) in database ID 32767 at offset 0x00000000da4000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\mssqlsystemresource.mdf'.

I thought mssqlsystemresource.mdf was effectively read only, and not really "opened"? (Might have confused it with something else but to do 2ith "config" though).

In a default install TEMPDB has probably been chucked on C: (and probably under C:\program files\ ... </SIGH!>), so TEMPDB might genuinely be running out of space ??

Might be worth stop/start SQL Service (or rebooting) to force TEMPDB to be recreated.

If C: is still short in space try making as much available as possible, in the short term. (The system databases can be moved to another drive, but there is little point if they a re actually corrupted, so would be worth making sure that's NOT the case if poss. as it will save a lot of effort)

Kristen

Kristen
Test

22859 Posts

Posted - 2007-10-09 : 22:16:07

Restarting the server will have recreated TEMPDB, so you may have been out of disk space when this error occurred, but restarting will have released that.

Suggest you monitor disk space and size of tempdb.mdf/.ldf files, and see if they eat up all the diskspace over time.

Kristen

vbenkert
Starting Member

12 Posts

Posted - 2007-10-10 : 10:59:17

From what I've seen, disk space does not go down over time. The previous event of running out of disk space was due to an individual creating multiple backup files, filling up the drive. Event after restarting the server, I'm still getting errors when performing a check database.

quote:
Originally posted by Kristen

Restarting the server will have recreated TEMPDB, so you may have been out of disk space when this error occurred, but restarting will have released that.

Suggest you monitor disk space and size of tempdb.mdf/.ldf files, and see if they eat up all the diskspace over time.

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2007-10-10 : 12:43:33

Well what drive is tempdb on and how much free space is on that drive

Is tempdb set to unlimited growth or is it fixed?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

vbenkert
Starting Member

12 Posts

Posted - 2007-10-12 : 12:39:45

The tempdb is on the C: drive, which has 4GB free. Currently the tempdb is 8MB in size. Where do I check if it's set to fixed or unlimited growth? (If you find that by pulling up the properties of tempdb database, then I can't - This goes back to my original issue of not being able to pull up properties of any database...).

Thanks.

quote:
Originally posted by X002548

Well what drive is tempdb on and how much free space is on that drive

Is tempdb set to unlimited growth or is it fixed?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2007-10-12 : 12:50:11

Look in EM and right click on the db and check properties

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

vbenkert
Starting Member

12 Posts

Posted - 2007-10-18 : 16:11:44

So.... Seeing as how I can't pull up properties of a database, I ask again....Should I just backup the client's main database and reinstall SQL???

Thanks.

quote:
Originally posted by X002548

Look in EM and right click on the db and check properties

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2007-10-19 : 01:51:20

Reinstall will lose all permissions, anything stored explicitly in MASTER database, all jobs, backup history, and probably some other stuff that I've forgotten.

All these may be minor though, depends on your circumstances.

Kristen

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources