Category Archives: Scripts

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 →

DBA Master

Carlos Robles is a Solutions Architect at AWS, a former Microsoft Data Platform MVP, a Friend of Redgate, but more than anything a technology lover. He has worked in the database management field on multiple platforms for over ten years in various industries.

He has diverse experience as a Consultant, DBA and DBA Manager. He is currently working as a Solution Architect, helping customers to solve software/infrastructure problems in their on-premise or cloud environments.

Speaker, author, blogger, mentor, Guatemala SQL User group leader. If you don’t find him chatting with friends about geek stuff, he will be enjoying life with his family.

www.dbamastery.com

Reviewing the SQL Server ERROR log

Leave a reply

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.

DBA Master

Speaker, author, blogger, mentor, Guatemala SQL User group leader. If you don’t find him chatting with friends about geek stuff, he will be enjoying life with his family.

www.dbamastery.com

MSDB Tuning by purging backup and restore history

Leave a reply

Have you ever had problem with your backup jobs taking more time than usual overlapping your maintenance window? It is the disk IO throughput fast enough? or it is your SQL Server not performing well? what could be affecting the performance to avoid your backup job to completely in timely fashion?

The answer to all these questions is not that difficult to find, in most of the cases there is a common point of failure for backup jobs taking more time with the past of the time. Your databases are bigger and your backup and restore history has been retained forever (since the SQL instance was created).

Most of the DBAs in the wild doesn’t know about this very important performance tuning aspect, they completely forgot about paying attention to the MSDB database. Probably because this is a system database there is an assumption that does not requires maintenance as any other regular database.

According to Microsoft docs you simply have run the sp_delete_backuphistory stored procedure in MSDB database, providing a date parameter to be used by this process as your oldest date to retain data to …. but guess what it does not work well and is not that simply.

Before you go for it, you must consider the following aspects:

Purging this backup and restore history leads to blocking into MSDB
Running the in sp_delete_backuphistory stored procedure causes odd problems to your ongoing or scheduled SQL Agent jobs
The sp_delete_backuphistory it’s not optimal and takes a long time to run

So what are your options?

Understand what the sp_delete_backuphistory stored procedure is doing and write your own

Probably not the best option, because it will require you to invest time doing research, analysis, design and testing

Look for missing indexes in all the MSDB tables purged by the sp_delete_backuphistory stored procedure (Best option due the amount of time you have to invest creating a new process)
Schedule a job to purge the backup history during short periods of time after all your daily jobs are completed

This is a good option, because we have identified those indexes beforehand for you and also there is no development effort to re-create a process that already exists. Also you probably can schedule a job to run every weekend to purge 2 weeks of data at the time so the MSDB is not compromised by the purging effort.

Based on our experience adding the missing indexes to all the tables involved in the purge process, we noticed an improvement of 60% of the sp_delete_backuphistory stored procedure. So go ahead and try it and let us know how everything works for you 🙂

Happy purging, stay tuned for more DBA mastery tips.

Note:

This indexes works starting from SQL Server 2008 R2 SP3
You can find the MSDB Missing indexes script here

DBA Master

Speaker, author, blogger, mentor, Guatemala SQL User group leader. If you don’t find him chatting with friends about geek stuff, he will be enjoying life with his family.