A Fine Slice Of SQL Server
Mikey Bronowski continues a series on dbatools:
OK, let’s start. While doing some maintenance tasks there is a need to start/stop/restart SQL Services. Using SQL Server Configuration Manager is one way or even straight from SSMS, however, working with multiple instances is easier with dbatools.
Mikey covers services, SQL Agent jobs, endpoints, traces, and Extended Events sessions, so check it out.
Comments closedMonica Rathbun has a script for people who just want to watch the world burn:
Occasionally, we must resize or realign our Tempdb log file (.ldf) or data files (.mdf or .ndf) due to a growth event that forces the file size out of whack. To resize we have three options, restart the SQL Server Service, add additional files, or shrink the current file. We most likely have all been faced with run away log files and in an emergency situation restarting the SQL Services may not be an option but we still need to get our log file size smaller before we run out of disk space for example. The process of shrinking down that file can get tricky so I created this flow chart to help you out if you ever get into this situation.
Now its very important to note that many of these commands will clear your cache and will greatly impact your server performance as it warms cache backup. In addition, you should not shrink your database data or log file unless absolutely necessary. But doing so, it can result in a corrupt tempdb.
Click through for the process, including a well-made flow chart with a large “Know what you’re doing before doing this” warning.
Comments closedHugo Kornelis walks us through automated restoration of database backups:
Now I’ve been to quite a few conferences. And I’ve heard a lot of DBAs talk about best practices. One of the constants in those talks is: automate your restores. So I felt confident that a quick internet search would surely be enough to find me an existing script for restoring a database. Sure, I’d need to modify it to restore to a test database, but that should be minimal effort.
To my surprise, I was unable to find a script. Is scripting this so easy that every DBA can do it with their eyes closed, and nobody feels a need to share it? Is it so hard that they all decided they’re sitting on gold and will only share it for big money? Or were my internet search skills simply severely lacking?
Anyway, bottom line is that I had to do it myself. And I’ll share the result in this post.
Click through for the script and a detailed explanation of how it works.
Comments closedJohn McCormack shows how to save the output from sp_whoisactive for later research:
When I took over a fairly troublesome SQL Server, I was always being asked what was running when the SQL Server was slow. That was fine when it was during the day and I could look. But often I would be asked, why was the server slow at 9pm last Friday? Or some other time. It’s not always the easiest thing to do as SQL Server doesn’t natively keep this information for you.
A quick but effective way to automate this is to download and schedule Adam Machanic’s sp_whoisactive stored procedure. I run this every 10 minutes but I have seen it scheduled as often as every 30-60 seconds. It depends on your system and your needs.
This is in place where I work, and I’ve put it into place when consulting for companies without the budget for fancy tools. I really appreciate that Adam Machanic made it a simple option to insert results into a table. Also, John has a step to delete older data, which is critical.
Comments closedTaiob Ali creates a process to track ensure specific trace flags are in use:
Recently during a SQL Server upgrade, I noticed some inconsistency with trace flags between production and non-production instances. Investigating further also found a similar inconsistency between Always On Availability Group replicas.
My team members fixed this issue by leveraging startup stored procedures.
Click through for the explanation.
Comments closedKenneth Igiri walks us through restoring the master database in SQL Server:
The master database contains records of the structure/configuration for both the current instance and all other databases. When you run sp_configure, you are writing data to the master database. It also contains most of the dynamic management views that are necessary to monitor the instance.
The importance of the master database is crucial. First, it has the information necessary for opening all other databases and has to be opened first. Then, it involves all instance level principals for the current instance.
It is crucial to back up the master database daily. Equally important is to know how to restore the master database to the instance. The most frequent cases are the database crash or the necessity to restore the master database to another instance when no longer use the source instance. In this article, we will examine the specific case of moving the master database to another instance.
It’s definitely not as easy as restoring other databases, but it is possible.
Comments closedGarry Bargsley has started a four-part series:
SQL Server Agent Jobs are one of the core features of SQL Server. Agent Jobs perform many actions, from maintenance tasks (server and database), data movement flows, replication, log shipping, data cleanup, health checks and many more. Since Agent Jobs are a critical component in a data organization, it is important to know when Agent Jobs do not succeed. There are several ways to accomplish the monitoring of failed Agent Jobs, from easy to more complex. This four part series is going to cover how you, the DBA, can be notified of failing Agent Jobs in your SQL Server environment.
Click through for part one, which is all about finding failed jobs and filtering down to relevant jobs using dbatools.
Comments closedSo far this series has covered: installing Docker for Windows, the basic commands for managing images and containers, and creating a new image. This post will cover troubleshooting containers & the Docker application using the various log files that are available.
Depending on the type of process being run in a container (interactive or non-interactive), event and error information may be collected by the host logs, application logs, or by the Docker logs (or by all 3, in the case of SQL Server and IIS for Windows).
Read on, as it’s not just “The same places as you’d see on SQL Servers outside of containers.”
Comments closedMonica Rathbun notes that SSMS lets you change service-level objectives for Azure SQL Databases:
Sometimes as a DBA, I am lazy and want the ability to execute all of my tasks in one place. Lucky for me I discovered the other day that I can change my Azure SQL Database Service Level Object options within SQL Server Management Studio (SSMS) without ever having to go to the Azure Portal.
Read on to learn how, as well as what you can change.
Comments closedGarry Bargsley wants the buck to stop somewhere:
Did you know it is possible for SQL Server Agent to alert you of problems if something goes haywire with your Agent? Have you ever had an issue with Alerts not being sent after critical events? Then you might need to configure the SQL Server Agent Fail-Safe Operator to save the day. A Fail-Safe WHAT you might say?? This is a special SQL Agent Operator configured in the SQL Agent Alert System in the chance any of the following situations occur.
Click through for the situations as well as configuration steps using Powershell + dbatools.
Comments closed