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 Friday, May 9, 2014 6:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:32 PM
Points: 19, Visits: 55
I've checked the error logs and the database goes into single-user mode either during or after a database backup or full index catalog rebuild.

I am guessing this is happening because the server's resources are overloaded from applications, database usage, etc.

What is the least resource intensive CheckDB command to run? Is there a way to see how long CheckDB will take without actually running it?



Post #1569480
Posted Friday, May 9, 2014 7:01 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:57 AM
Points: 985, Visits: 1,044
I remember when I first starting working with SQL Server, it was SQL Server 2000. We had a job that would restore the database to another environment and if an error occurred for some reason it would be set to single user.

Maybe you are running out of disk space or something. If it is resources then probably need to invest in some more RAM.
Post #1569481
Posted Saturday, May 10, 2014 4:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:32 PM
Points: 19, Visits: 55
Gail - CHECKDB found 0 allocation errors and 0 consistency errors.

Brad - The database isn't restored to another environment, but I also heard this could cause it to go into single-user mode.

It could be that the server needs more RAM. How can I confirm this?



Post #1569555
Posted Saturday, May 10, 2014 4:08 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:57 PM
Points: 1,616, Visits: 1,543
Databases don't get set to single-user because a restore fails. It was probably set to single-user as part of the restore process because restoring a database requires sole access to the database.

Performance problems don't set a database to single-user either.

You should run a SQL trace on the Object:Altered event to determine what is setting the database to single user. Don't guess at it. Find the root problem and then you can fix it.




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1569556
Posted Saturday, May 10, 2014 4:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:32 PM
Points: 19, Visits: 55
RE: You should run a SQL trace on the Object:Altered event to determine what is setting the database to single user.

Robert - I definitely need to figure out the problem so I can fix it. How do I do this?



Post #1569558
Posted Saturday, May 10, 2014 4:36 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:57 PM
Points: 1,616, Visits: 1,543
1. Open the SQL Profiler tool
2. Connect to the server
3. Check Show All Events and Show All Columns
4. Deselect any pre-selected events
5. Expand Objects
6. Select Object:Altered event
7. Go back to the General tab
8. Check Save to File
9. Set the file path and name
10. Start the trace running




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1569559
Posted Saturday, May 10, 2014 5:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:32 PM
Points: 19, Visits: 55
Robert - SQL Profiler is freezing, and the firewall is asking if I should allow it to access the internet? Why does SQL Profiler need access to the internet?

I have SQL Server 2000 and I can't get pass step 3. There isn't a "Show All Events and Show All Columns" area.



Post #1569561
Posted Saturday, May 10, 2014 6:02 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:57 PM
Points: 1,616, Visits: 1,543
SQL Profiler doesn't need access to the internet. It only needs access to the SQL Server. Your interface will be different from what I described. I haven't worked regularly with SQL 2000 in more than 10 years.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1569562
Posted Saturday, May 10, 2014 6:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:32 PM
Points: 19, Visits: 55
Hopefully you can help ... please see three screenshots at http://bit.ly/1oCRgLh


Post #1569563
Posted Saturday, May 10, 2014 9:26 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:57 PM
Points: 1,616, Visits: 1,543
If there isn't an equivalent event to Object:Alter, you can do SQL:StmtCompleted and filter for an alter database statement.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1569573
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse