Press "Enter" to skip to content

Category: Administration

Using Powershell to Configure Database Mail and SQL Agent Alerts

Published 2020-04-30 by Kevin Feasel

Eric Cobb shows us how to use Powershell to set up database mail and SQL Agent alerts:

As a DBA, you need to know when there’s a problem on your SQL Servers. And while I highly recommend you use a full-fledged monitoring system, there are also some things you can set up on your SQL Servers so that they will tell you when certain things go wrong. This doesn’t replace a full monitoring system, but setting up the below alerts will give you notification when SQL Server encounters things like corruption or resource issues.

Even with a full-fledged monitoring system, there are places where you can still make use of mail and side alerts.

Comments closed

Stored Parameter Procedure Caching

Published 2020-04-29 by Kevin Feasel

Greg Dodd follows Betteridge’s Law of Headlines:

When SQL Server caches your plan, it caches it with the parameter values that you pass through the first time, and it assumes that the same query plan will be the best one for any parameter you pass in next time.

But does SQL Server always cache your parameters? Does it always keep track of what you pass in?

Click through for a demonstration good enough to give you a conclusive answer.

Comments closed

The Problems with Shrinking tempdb Data Files

Published 2020-04-29 by Kevin Feasel

Andy Mallon explains why you should never shrink tempdb data files:

I recently wrote about growing, shrinking, and removing tempdb files. In that article I explained that SQL Server won’t move a page that contains an internal worktable object, and thus trying to shrink tempdb files can be futile. Today, I’m going to explain how attempting to shrink tempdb files can actually be harmful.

Andy has good advice for tempdb here. Shrinking other database is generally bad but sometimes necessary; shrinking tempdb can lead to all kinds of problems.

Comments closed

Accessing Managed Instances from SSMS

Published 2020-04-28 by Kevin Feasel

James Serra shows us what we need to do in order to reach an Azure SQL Managed Instance from SQL Server Management Studio:

It used to be that the only way to use SQL Server Management Studio (SSMS) against Azure SQL Database Managed Instance (SQLMI) was to create a VM on the same VNET as SQLMI and use SSMS on that VM. That VM was usually called a jumpbox (see instructions here).

But about a year ago Microsoft added a way to use SSMS without using a VNET (announcement) by allowing you to enable a public endpoint for your SQLMI. This made it easy for me to access a SQLMI database on my laptop.

That change enables what James shows us.

Comments closed

SQL Server 2019 and Columnstore Cleanup Issues

Published 2020-04-27 by Kevin Feasel

Taryn Pratt shares an issue with columnstore cleanup in SQL Server 2019:

The output of sp_who2 was repeatedly showing GHOST CLEANUP and CREATE INDEX. Over and over and over again. To be clear, I’m not a clustered columnstore expert, I know enough to be able to maintain them as needed. I went to Twitter and mentioned what I was seeing. I was advised by @sqL_handLe to try trace flag 661 which disables the ghost record removal process, and by Joe Obbish via Erik Darling to enable trace flag 634 to disable the tuple mover background task.

Initially, we enabled trace flag 634, but the logs continued to grow. We disabled trace flag 634. Then we enabled trace flag 661, and the logs continued to grow, so we disabled it. Finally, we tried enabling both of the trace flags. The big jumps stopped, but we now had about 400GB of logs that needed to be written to the reporting cluster before we could perform the failover.

While the logs were exploding we wondered if whatever was happening might have been caused by the deletions we did in early February. But why would they be triggered by the upgrade to SQL Server 2019?

Read the whole thing if you’re looking at a migration to 2019.

Comments closed

Finding Your Azure Database Pricing Tier

Published 2020-04-27 by Kevin Feasel

Kenneth Fisher needs to know a server’s current pricing tier:

I ran into an interesting problem today. I needed to find out the pricing tier of an Azure SQL DB but while I have access to to the DB via SSMS I don’t have access via the Portal. So I needed something I could use via T-SQL. I did some research, and found something that said it worked but didn’t look right to me. So I asked on twitter.

Click through to see the answer.

Comments closed

Cleaning Up SQL Server Backup History

Published 2020-04-27 by Kevin Feasel

Stuart Moore has a public service announcement for us:

From looking around at some of the Issues that get logged for dbatools, it looks like people never clear out their SQL backup history from msdb. This causes slow queries and potentially misleading backup trees to be returned.

In my (not so humble) opinion you really shouldn’t let your backup history fill up with, let’s be honest, useless junk. In this post I’ll go through why I think you should be keeping way less in there than people think you should

Read on for Stuart’s thoughts on the topic.

Comments closed

Fun with tempdb

Published 2020-04-22 by Kevin Feasel

Andy Mallon walks us through setting up tempdb:

There are three problems I’ve got to fix. I need to (1) remove those two extra files, (2) grow the tempdb log file, and (3) even out the size of the data files (and shrink them a little to make room for the larger log file. We’re going to tackle these in the reverse order than I listed them–partially out of necessity, and partially because it’s going to be easier.

Click through to see how Andy sets up tempdb. This is a good way to set up tempdb.

Comments closed