SQL server restart | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL server restart

We have sql server 2005 without SP on windows 2003 server. When DBCC CHECKDB executes on databse, expecially on masterdb, some of the time sql server database engine restarts and that obviously restarts sql server agent. One thing might be noted over here is we have backup running at same time and that single backup is split in 4 files on same server. I am not sure whether its because of this that sql server is restarting or there are any other issues. It might be leaked memory, I am not sure, does anybody have any idea or had same issue with sql server 2005 without sp. The database size that was backed up was around 170 gb. There are no errors reported in event viewer?? can anyone suggest any issues with handling of IO or memory or load on sql server 2005 last command in sql server logs was "DBCC CHECKDB (master) executed by PATSQLACCOUNT found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 30 seconds."
I have not seen this kind of issues ever. You can analyze the SQL Error logs and see if any kind of UMS errors are logged, also don’t forget to check Windos Event logs. Check the installation folder for any memory dump, those dumps can provide some info. Also, why don’t you try running SQL Profiler before you start running DBCC CHECKDB
Girish Patil
MS-SQL DBA
http://www.facebook.com/p/Girish_Patil/517990969
*** This posting is provided AS IS with no rights for the sake of knowledge sharing. ***
I have executed trace and even checked for windows and sql server logs, however no errors are reported in logs, even checked for event viewer and couldn’t find anything that could lead to any conclusion, or point in right direction
Do you see any mdp or mdmp files in sql error log folder? MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
No THERE are no mdp or mdmp files in sql error log folder
Final option would be call PSS [<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />]<br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><br />All postings are provided “AS IS” with no warranties for accuracy.<br />
sorry, but what do you mean by PSS???
Microsoft Product Support Services.
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Just an update on this that this is cluster environment, so its like cluster check for sql server every 60 sec "Is Alive" poll interval. Looking at all aspects I think that it might be cluster server which is restarting the server as whenever the server restarts, there is backups and replication running and even have more than 200 connections and applications connected, so looks like sql server is too busy with this and when it doesn’t respond to "cluster Is Alive" message, cluster server decide to restart the server(We have disable the failover to another node). Since this is 32bit computer with 16gb of RAM, so I am now diverting my attention towards worker threads. I think it might be there are no worker threads availbale arouind this time and thats why sql behaves like this?? please let me know How can I monitor threads around the failure, is there a trace or monitoring that I can turn on to check for number of threads in use. Default on our server is "0", which means it can use 256 threads to process. I am checking for threads by using these views SELECT * FROM sys.dm_os_schedulers
SELECT * FROM sys.dm_os_workers
SELECT * FROM sys.dm_os_threads
SELECT * FROM sys.dm_os_tasks
SELECT * FROM sys.dm_os_waiting_tasks
SELECT * FROM sys.dm_os_ring_buffers however since this happens around midnight, so how can I have a clear piture to rule out this option . Can you update on how to monitor this and also please provide me link to some articles where I can understand more on this worker threads. Thanks
If the cluster is causing the sql server to start, then you should see "Is Alive" or "keep alive" failure messages. Cluster will try three times with 300 secodns default intervel…then it restarts it self when there is no HW related issue. In sql server 2005 MAX WORKER THREADS are not 256 for all servers it depends on HW…
Check BOL topic "max worker threads Option " When the actual number of user connections is less than the amount set in max worker threads, one thread handles each connection. However, if the actual number of connections exceeds the amount set in max worker threads, SQL Server pools the worker threads so that the next available worker thread can handle the request. When all worker threads are active with long running queries, SQL Server may appear unresponsive until a worker thread completes and becomes available. Though not a defect, this can sometimes be undesirable. If a process appears to be unresponsive and no new queries can be processed, then connect to SQL Server using the dedicated administrator connection (DAC), and kill the process. To prevent this, increase the number of max worker threads. MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Also check Slavo’s weblog… http://blogs.msdn.com/slavao/archive/2006/09/28/776437.aspx Q. Should I configure SQL Server to use more threads – sp_configure ‘max server threads#%92? You can answer this question by looking at the work queue length for each scheduler. If on average such value is above 1 then you might benefit from adding more threads to the system but only if A. Your load currently is not CPU bounded (See info above on how to find out if your load is CPU bound) B. Your load currently doesn#%92t experience any other heavy waits (If you add more threads in this case they will just end up waiting as everyone else) select AVG (work_queue_count)
from sys.dm_os_schedulers
where status = ‘VISIBLE ONLINE’
Check the following too..
http://www.microsoft.com/technet/prodtechnol/sql/2005/diagandcorrecterrs.mspx
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Can you test the latest service pack on SQL, as I can see you have no service pack. There are good bug fixes around with the latest service pack too and few of them will be related to memory too. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>