SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


*** HUGE SQL Server Problem *** =(


*** HUGE SQL Server Problem *** =(

Author
Message
benc007
benc007
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 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?



brad.mason5
brad.mason5
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1596 Visits: 2044
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.
benc007
benc007
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 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?



Robert Davis
Robert Davis
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2882 Visits: 1623
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 MVP
Database Engineer at BlueMountain Capital Management
benc007
benc007
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 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?



Robert Davis
Robert Davis
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2882 Visits: 1623
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 MVP
Database Engineer at BlueMountain Capital Management
benc007
benc007
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 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.



Robert Davis
Robert Davis
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2882 Visits: 1623
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 MVP
Database Engineer at BlueMountain Capital Management
benc007
benc007
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 55
Hopefully you can help ... please see three screenshots at http://bit.ly/1oCRgLh



Robert Davis
Robert Davis
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2882 Visits: 1623
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 MVP
Database Engineer at BlueMountain Capital Management
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search