September 13, 2002 at 6:59 am
What happened to me yesterday is completely weird and I hope that you intelligent individuals can shed some light for me.
We have a database that is currently 4GB. This database only holds about 15 tables, the biggest table holds about 3.5 million records and contains only 7 columns. We have reports (crystal) that pull from views, within the database, that pull from this specific table.
Yesterday, all our reports stopped working. I went into query analyzer and was able to query the tables individually with no problems, however when I select from a view that pulls from the table in questyion it just looped and never returned a result. I even ran a trace and the trace showed the start of the view and the end of the view and then then start then end,etc. I ran a DBCC CHECKDB on the database (being stupid i forgot to write down the exact errors that were reported, but the id reported was the id of the table described above) and it basically stated there were inconsistencies. To make a long story as short as possible, I spent my night restoring the database, from backup, just so the reports would work again.
1) My ultimate question is, does this sound familiar to anyone and if so what caused it and how do I protect against it?
2) What can I do next time to exhaust all means prior to restoring. I don't understand how I could query the tables individually, but could not get a result from a very simple view that had only 1 join in it.
I hope someone can shed some light.
Thanks
September 13, 2002 at 7:36 am
Never seen it. Did you try running Checkdb with the repair option to try to fix it? Do you run checkdb on a regular basis? Have a power drop or something recently?
Andy
September 13, 2002 at 7:44 am
No power outages. No I didn't run the dbcc checkdb with repair option. Call it a rookie mistake. Not sure that would have fixed it though. No I don't run DBCC Checkdb on a regular basis. As an administrator, what kind of routines should I be running and how often, to keep track of my servers and their consistency.
I really appreciate your help.
quote:
Never seen it. Did you try running Checkdb with the repair option to try to fix it? Do you run checkdb on a regular basis? Have a power drop or something recently?Andy
http://qa.sqlservercentral.com/columnists/awarren/
September 13, 2002 at 8:46 am
Maintenance plan is the simplest way to get started. I typically run the consistency checks once a week.
Andy
September 16, 2002 at 7:14 am
Andy, I set up a maintenance plan for one of my databases this weekend to see how it worked out. The selections I made within the package are as follows:
Optimizations Tab: chose only to remove unused space from database files that had grown more than 50MB. I set it to leave 35% free space when it was completed. (NOTE: This portion ran fine)
Integrity Tab: Selected "Check Database Integrity" "Include Indexes" "Attempt to repair any minor problems. This part failed with many errors like the one below:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'DB1' as 'SECSOURCE\admin_' (trusted)
Starting maintenance plan '59 - DB Consistency and Integrity Check 'Weekly'' on 9/15/2002 3:00:09 AM
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL Server Driver][SQL Server]Database state cannot be changed while other users are using the database 'DS'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[1] Database DS: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
Two questions, Why is the server trying to put databases in Single User mode? and to save you time, is there an article or something the a details each step of the Maintenance Plans as BOL's description are somewhat broad?
I hope you can help.
Thanks again.
quote:
Maintenance plan is the simplest way to get started. I typically run the consistency checks once a week.Andy
http://qa.sqlservercentral.com/columnists/awarren/
September 16, 2002 at 12:34 pm
I can't tell you why (MS would have to do that), but before you can even run the checkdb with "repair option", the database must be in single user mode. This doesn't mean it is trying to repair anything, just has to be that way even before executing the check. Funny thing is that with SQL 2000, you can't run this option at all on MSDB and MASTER since they can't be put in single-user mode.
Brian Glass
Sr. Database Adminstrator
Bombardier Aerospace
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply