Unable to start services after patching - msdb110_upgrade.sql' encountered error 574, state 0, severity 16.

Question : During a recent round of patching , I've been frequently seeing msdb110_upgrade.sql' encountered error 574, state 0, severity 16 in the Event Viewer when the service for the SQL Server Instance is attempting to start. How can I resolve this issue? The service will not currently start. I've checked in the SQL Server Error log file via a text editor and can see A problem was encountered granting access to MSDB database for login '(null)'. Make sure this login is provisioned with SQLServer and rerun sqlagent_msdb_upgrade.sql The lines in the SQL Error Log are: 2018-09-09 13:41:56.88 spid5s Granting... Read more →

How to write to a sql error to windows server event viewer

Over the last few versions SQL Server has improved greatly on writing sql server error messages . A good example is writing to both SQL Server Error Log and the windows server event viewer. There are a number of benefits in writing a SQL Server error to the windows event viewer. Centralising error messages to the one source makes it more efficient for a monitoring application. It is possible for the monitoing package to parse the Windows event viewer and extract all alerting situations from the one source. This minimises the access footprint on the server . There is no... Read more →

SQL Server 100 cpu diagnose

Question: Users are reporting long delays in query responses. I checked on the server and noticed CPU counter at 100 % on Performance Monitor. There are 4 cores associated to this server and all the cores were hovering around 100%. How can I diagnose the underlying cause ? Answer: It’s a common problem faced by DBAs , where the server is sitting at 100% for prolonged periods. Check there are no other processes outside of SQL Server which are causing the server to be at 100%. Typically security scans, virus scans and monitoring can be root causes. I have also... Read more →

Configurations eating up sql server performance

Question: I’m setting up a best practises document for our SQL Server builds and would like to focus on some configurations which fit in with sql server tuning best practises, What are some of the SQL Server configurations you emphasise when setting up a server? Answer: Firstly – I like this approach of focusing on the configurations at the SQL Server Install phase. This is an important aspect of SQL Server performance tuning. Research should be completed looking at a combination of industry standards, capacity planning , and application specific requirements. A large chunk of problems can be stopped by... Read more →

Troubleshooting SQL Server Service Broker

If you’re not experienced in all the nuances of Service Broker, it can be tricky to diagnose or identify if there is an issue with Service Broker. There are plenty of products using Service Broker. SCCM is an example . Here are some queries I use when needing to troubleshoot SQL Server Service Broker. --Check Service Broker activated stored procedures SELECT t.*, s.last_wait_type,* FROM sys.dm_broker_activated_tasks t INNER JOIN sys.dm_exec_requests s ON s.session_id = t.spid --check to see if the database is broker enabled select name , is_broker_enabled from sys.databases --check if queues are enabled select name,is_receive_enabled,is_enqueue_enabled from sys.service_queues --check tcp... Read more →