Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

*** HUGE SQL Server Problem *** =( Expand / Collapse
Author
Message
Posted Thursday, May 8, 2014 11:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:32 PM
Points: 19, Visits: 55
My SQL Server 2000 keeps going into "single user" mode. I have to manually go on the server to fix it each time. I don't know when it happens until my client complains =(

How can I fix this automatically?

I think the problem is that the data is fragmented / corrupted but I am not sure. I was able to shrink the database (data and log files), but I don't know if this will fix the "single user" problem.

Can anyone help?



Post #1569042
Posted Thursday, May 8, 2014 12:09 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
Go look at your database integrity job. Does it have the option "automatically fix minor problems" checked? If so, uncheck it. Then do a full DBCC CheckDB and see how bad your corruption is.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1569060
Posted Thursday, May 8, 2014 1:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:32 PM
Points: 19, Visits: 55
I have a database back up job with one step and the command:
BACKUP DATABASE [myDBName] TO DISK = N'F:\myDBName' WITH INIT , NOUNLOAD , NAME = N'myDBName backup', NOSKIP , STATS = 10, NOFORMAT

I don't do anything else ...

In SQL 2000, how can I see if "automatically fix minor problems" is checked?

Does "DBCC CheckDB" only check the database and not fix it? How long will it take to run? Will shrinking the database more help (log and data files)?



Post #1569076
Posted Thursday, May 8, 2014 1:26 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
I'm not talking about your backup job.

Look at your maintenance plans. Do you have any which have the database integrity task? If there are, does that task have 'automatically repair minor problems' checked?

Shrinking won't help anything. It'll potentially hinder performance afterward.
CheckDB without options just checks. That said, corruption will not alone make a database go into the single user state. Neither will fragmentation. The only thing which will make a database go into single user is something running ALTER DATABASE <database name> SET SINGLE_USER;

While you're at it, search through jobs and procedures for anything that runs ALTER DATABASE.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1569080
Posted Thursday, May 8, 2014 3:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:32 PM
Points: 19, Visits: 55
I don't think there are any maintenance plans. How do I check this, and also look for database integrity tasks ad see if "automatically repair minor problems" is checked?

How will shrinking hinder performance? How do I search jobs and procedures for ALTER DATABASE?





Post #1569099
Posted Friday, May 9, 2014 12:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
There's a maintenance plans folder in Enterprise manager, you look through that. Check to see if any of your maintenance plans have the check database integrity task. If any do, check the task, see if the check box I mentioned is checked.

You can get procedure definitions with sp_helptext, that can be inserted into a table which you can then query.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1569146
Posted Friday, May 9, 2014 10:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:32 PM
Points: 19, Visits: 55
In Enterprise Manager, there are no maintenance plans set up.

I also returned no results for:
SELECT object_name(id)
FROM syscomments
WHERE text LIKE '%alter database%'




Post #1569382
Posted Friday, May 9, 2014 12:01 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
Then either there's an application that's sending that command to SQL, or someone's running it manually.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1569401
Posted Friday, May 9, 2014 2:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:32 PM
Points: 19, Visits: 55
There's no application that do this, and no one else has access to the database to run the command manually.

We use full text index and rebuild our catalog every few hours, but I'm not sure if this is the cause of the problem:
exec sp_fulltext_catalog 'MyCatalogName', 'start_incremental'

I am out of ideas except that the database or indexes are corrupted? Any other ideas? I am really stuck here.

... should I still do a DBCC CheckDB now?




Post #1569458
Posted Friday, May 9, 2014 3:28 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
SQL doesn't randomly set databases to single user mode, the command is coming from somewhere. Either an application, a job or another user. Check the error log, consider running traces to catch the event.

Regular checkDBs are part of database maintenance. If you're not doing them, why not?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1569463
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse