A Fine Slice Of SQL Server
Chad Callihan gets rid of secondary data files:
As I was working on a recent tempdb blog post, I encountered an error when trying to remove data files. Let’s look into the issue you may have removing data files and the solution to get those files cleaned up.
Click through to see how you can empty a data file and remove it without receiving error messages. I’m going to guess that this works better on lightly-used databases more than slammed ones.
Comments closedAjay Dwivedi sets up an alert:
Recently one of my LinkedIn friends contacted me for a blocking alert that would work on on-prem & cloud SQL Server instances alike. Previously I wrote https://ajaydwivedi.com/2018/08/how-to-setup-blocking-alerts-on-sql-server blog post for on-prem SQL Server blocking alert which makes use of WMI based event.
So I wrote the following blocking alert setup code for SQL Server that has the below features –
– Send mail notification to one or more recepients unlike SQL Agent job which is restricted to only one operator.
– Parameter to control the consistent blocking threshold. Nobody wants spontaneous blockings which comes/goes.
– Parameter to control the mail notification delay. Say, I want to be notified every X minutes (defult 15 minutes).
– Parameter to control the mail notification subject.
– Separate mail notification for Blocking & Script failure itself.
– Auto-clearing feature. Means, if the blocking is resolved, we should get an automated mail notification saying Blocking is cleared.
Click through for the instructions.
Comments closedEtienne Lopes tracks a notification event:
Did you ever need to automate some actions immediately after a database restore?
There are several ways to accomplish this, in this article I will demonstrate one of them, using a simple event called: “AUDIT_BACKUP_RESTORE_EVENT“
Read on to learn more about how to use this event.
Comments closedGarry Bargsley has a cmdlet for us:
This week’s blog post will help you check your SQL Servers up-time. There are numerous reasons I can think of that you would want to know how long your SQL Server has been online. Was the server recently patched, did it crash and come back online, or did someone restart it by mistake? These are all valid questions about a single SQL Server or your entire estate. I will show you how you can easily check one too many servers quickly for uptime.
We will start by using every DBA’s favorite PowerShell module… dbatools
Admittedly, I’d just check the start time for the tempdb database, but this cmdlet does give more info.
Comments closedBrent Ozar has a timely analogy for us:
You’re trying to decide whether to use DHL, FedEx, UPS, or your local postal service.
You could measure them by sending me a gift – after all, it is the holidays, and I do a lot of stuff for you year round, and it’s probably the least you could do.
– You place one box outside
– You call the shipping company to come pick it up, and start the clock
– When I get it, I’ll call you to confirm receipt, and you stop the clock
Click through for the rest of the story.
Comments closedChad Callihan has a two-parter for us. First up is a look at tempdb usage:
The tempdb database in SQL Server holds a little bit of everything over its existence. There can be the temporary tables that you created, work tables like spools that SQL Server creates, version store data, and much more in tempdb at any time.
Is your tempdb having a hard time keeping up with the workload on your SQL Server? How can you find out what is using tempdb the most?
Chad also recommends picking the right number of files for tempdb:
We just made it through Thanksgiving dinner and there’s another big Christmas dinner coming up in few weeks. Sometimes you might find that one plate is not enough to hold all of that food. Just as multiple plates can make big dinners easier to manage, multiple tempdb data files can make the SQL Server workload easier to manage.
Read on to see how to track and modify this setting.
Comments closedTomas Zika wants to track a change:
This time a colleague from work asked how to best find a culprit that has been changing a specific cell in a table. It could be an automated process, application logic, application user or even an ad-hoc statement – we didn’t know. The table has many different access patterns, some of which are frequent. Ideally, we don’t want to monitor everything and sift through it.
We wanted to learn the who, the how and then ask why? If you like to know the whole journey, read on. Otherwise, you can skip to section Eureka moment
Click through for the Eureka moment. It is important to embrace the power of “and.”
Comments closedTom Collins does not want guests barging into SQL Server unannounced:
A common SQL Server Security check is to identify if the guest id is enabled. By default , the SQL Server guest id is disabled , but for various reasons it can become enabled.
To identify the guest status you can either use the SQL Server Management Studio (SSMS) or through SQL code.
Click through to see how. I don’t think I’ve ever seen a use case for having the guest account enabled on a SQL Server instance.
Comments closedRohit Choudhary argues that it’s a good idea to move from YARN to Kubernetes for your Spark clusters:
When it comes to data operations, Spark provides a tremendous advantage as a resource for data operations because it aligns with the things that make data ops valuable. It is optimized for machine learning and AI, which are used for batch processing (in real-time and at scale), and it is adept at operating within different types of environments.
Spark doesn’t completely manage these clusters of machines but instead uses a cluster manager (known as a scheduler). Most companies have traditionally used the Java Virtual Machine (JVM)-based Hadoop YARN to manage their clusters. But with the dramatic rise of Kubernetes and cloud-native computing, many organizations are moving away from YARN to Kubernetes to manage their Spark clusters. Spark on Kubernetes is even now generally available since the Apache Spark 3.1 release in March 2021.
I see some of the benefits there but am not totally sold, especially given the complexity of Kubernetes and its own lack of built-in security measures.
Comments closedJonathan Kehayias wants to know what changes you’ve made:
The introduction of DATABASE SCOPED CONFIGURATIONS in SQL Server 2016 enabled different configuration settings at the individual database level. However, there is no logging of changes to the database scoped settings by default in SQL Server, making it nearly impossible to track down when a change was made and by who. After recently working on a client problem where performance issues were attributed to a DATABASE SCOPED CONFIGURATION of MAXDOP = 1 multiple times, I decided to create a DDL trigger for the ALTER_DATABASE_SCOPED_CONFIGURATION events in SQL Server to have it log the change to the ERRORLOG file similar to the one I wrote years ago for logging Extended Event session changes.
Click through for the definition of that trigger.
Comments closed