SQL Service hanging with Change Pending Error

Here’s a strange error I experienced when attempting to restart a SQL Server Service. The Service wouldn’t restart , the status was hanging on “Change Pending”. I’d seen this error before, but during installations. I hadn’t seen it when the service was established. I checked the SQL Server Error logs – which I had to access directly via the file system and read as a palin text file, as I couldn’t access SQL Server . There were no clues in the logs to suggest what may have caused this error. I also checked the Applications sections of Windows Event Log... Read more →

Powershell Script – How to get windows logs events with Get-WinEvent for a date range

Question: How can I return a Windows Event Logs recordset within a date range? I’m troubleshooting a recurring issue across certain servers, and needed a Powershell scripted method of returning Windows Events from the System event log , event id ,start date, end date. Answer: For filtering event log details I use the Get-WinEvent Powershell cmdlet. The -FilterHastable switch manages the parameters . Let’s look at some basic examples. Example 1 : Find all events from the System log of the event id 36874 between the 01/12/15 and 21/12/15 Get-WinEvent -FilterHashtable @{logname='system';id=36874;StartTime="01/12/15";EndTime="21/12/15"} Example 2: Find all events from the Application... Read more →

How to Audit user DML with SQL Server Extended Events

Question: What methods are available to audit the DML activity of a user. I need to track a third party developer and record the DML statements they execute on a SQL Server data? The data needs to be recorded and made available for a regular review. Answer: There are many ways to skin the audit cat. Since SQL Server 2008 the DBA life has become easier with the introduction of Extended events , as applied to auditing. Before I explain of how to set up Extended Events let’s review some other methods Manual auditing – refers to a home grown... Read more →

How to troubleshoot Flush Cache error message in SQL Server Logs

Question: I’m getting a Flush Cache message in the SQL Server Logs. FlushCache: cleaned up 3606 bufs with 476 writes in 69033 ms (avoided 4 new dirty bufs) for db 7:0 What does it mean regarding sql server performance? Are there methods I should be using to troubleshoot this message Answer: The impact on SQL Server performance will depend on understanding the context . Checkpoint logging causes this message to appear in the SQL Server logs. Just to be specific, these messages only started appearing in the logs since 2012. Prior to 2012 you needed to turn on a trace... Read more →

Connect to SQL Server using port number

When SQL Server Browser is not started , and a SQL Server connection attempt is made remotely using the named instance an error 26 message appears. A network related or instance-specific error occurred Error 26 – Error Locating Server/Instance. The connection attempt finds the server – SERVER1 – but cannot map the instance request to the SQL service running on the server. The Error 26 message appears in a number of different circumstances. Read SQL Server Error 26 troubleshooting checklist for a list of different causes of Error 26 The root cause of this error message was the SQL Server... Read more →