Tag Archives: Troubleshooting

Creating Wait stats widget on Azure Data Studio for macOS

A couple of weeks ago, Microsoft released a new multi-platform tool called Azure Data Studio, this tool is the final version of SQL Operations Studio. If you are familiar to SQLOps, you probably recall that this tool 100% open source, and because of that you can customize the JSON code to do certain things the way it works best for you.

In my personal opinion, this is of the best features of Azure Data Studio are widgets. It gives the option to DBA’s or Database developers to create their own custom widgets to access SQL Server data using simple charts. The old out of the box SSMS Instance reports are good in some way, when you require to check something really quick but the lack of customization and the time they take to load doesn’t make them a really good troubleshooting tool … at least for me. I know how they work and even know how to build a custom report but I think the interface is not that responsive in my personal opinion, when dealing with an issue we need something really really fast.

Continue reading

Reviewing the SQL Server ERROR log

 

The SQL Server ERROR log is the best source to understand what’s wrong with your SQL Server instance, think this log is like your flight recorder.

In case you are not too familiar with the SQL Server ERROR log, the xp_readerrorlog extended stored procedure is your friend; and as a good friend you must get to know him. In this post, you will find some examples of how to take advantage of this undocumented stored procedure and maximize your time avoiding the utilization of slow and painful tools like Event Viewer from Windows.

First thing first, let’s understand what are the parameters we need to provide to the stored procedure and the most important how we have to use them.

Archive ID:
The first parameter is is about picking the order of the log we want to query:

  • Current log = 0
  • Archived error log #1 = 1
  • Archived error log #2 = 2

Log type:
Then, the second parameter is the SQL Server component we are focusing in:

  • SQL Server Instance Log = 1
  • SQL Agent Log = 0

So, combining these two parameter we can check the current log (0 in the first parameter) for a SQL Server instance (1 in the second parameter) or the SQL Server Agent (2 in the second parameter)

Here is how it looks like in T-SQL code:

-- Checking the current SQL Server instance and SQL Agent logs:
xp_readerrorlog 0,1 -- Remember 1 for SQL Server instance
xp_readerrorlog 0,2 -- Remember 2 fro SQL Server Agent

We also have an optional parameter we can include, it’s a simple search string. This parameter it’s really handy when you are looking for a specific event.

Here are some T-SQL examples how we can combine the first two parameters with a specific search string:

-- Checking for failed logins in our SQL Server instance:
xp_readerrorlog 0,1,'Login failed' --For SQL Server 2012 and below
xp_readerrorlog 0,1,"Login failed" --For SQL Server 2014 onwards

-- Checking for TempDB related entries in our SQL Server instance:
xp_readerrorlog 0,1,'TempDB' --For SQL Server 2012 and below
xp_readerrorlog 0,1,"TempDB" --For SQL Server 2014 onwards

Hopefully this tip could help you to save some time when looking at your SQL Server instance for recent errors or some events from the past that you might need to collect for further analysis.

Stay tuned for more DBA mastery tips.