How to add a user-defined SQL Server error message and send message to the Windows event log

I discussed in an earlier post on triggering an error message to write to the Windows event viewer. This can be very useful - as log readers can use one source to gather error messages and trigger alerts. If you're managing large amounts of SQL Server instances - standardising is essential. One of the standardisations is to add user-defined error messages dedicated to the DBA team. These user-defined messages can be used to give information for alerts - for example - failed sql server agent jobs , failed ETL jobs etc An example of how you could manage this process... Read more →

Service Exit Code 1077 and false negatives

This is not a trick question, but what is the Windows Exit Code 1077? In the Microsoft Windows documentation the details related to this Exit Code are No attempts to start the service have been made since the last boot. ERROR_SERVICE_NEVER_STARTED I’m writing some Powershell script to review Windows services which failed to start and I’m getting loads of rows of services with Exit Code 1077 and the Windows service is stopped. Answer: As the documented definition states “No attempts to start the service have been made since the last boot” . Therefore there is no failure in this case.... Read more →

Read SQL Server Error Logs with Powershell and SMO

SQL Server troubleshooting and SQL Server problem analysis usually results in a DBA reading the SQL Server Error Logs and other error logs , such as Windows Events Logs. The extent of analysing error logs depends on the problem. I’ve written in previous posts about the necessity to regularly review the SQL Server Error Logs. There is tons of detail or clues about other potential problems , which may not cause an outage today – but if left untreated could cause an outage tomorrow. How to use xp_readerrorlog parameters to search SQL Error Logs ... In a previous post ,... 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 uptime query

Question: Is there a reliable method to extract the sql server uptime? I’ve tried different methods , such as xp_readerrorlog , but I discovered a limitation when the error log is recycled , updated date & time information is added to the error log , which is the time \ date of the error log recycle , but not the sql server instance restart. Answer: There are two methods I use to for a sql server uptime query. Method 1 extracts the sql server start time from the sys.dm_os_sys_info DMV. Method 2 utilises the tempdb create date , as the... Read more →