A Fine Slice Of SQL Server
Taiob 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 closedGarry Bargsley does some spring cleaning:
If you are like me, you inherited variously configured SQL Servers when you took over as the DBA for your company. After almost two years, I have gotten all the standards in place where I feel that the environment is clean. One of the last things I accomplished was to standardize SQL Server Error Log configurations, Error Log Cycle schedules and cleaning up of old Error Logs.
To accomplish this there were several steps involved to get all SQL Servers into a unified set of configurations.
This is pretty easy to do and to script out.
Comments closedDavid Klee killed the radio star:
I just released a new training video (one of many to be released in the upcoming months) showing you how to set up Windows Perfmon for ongoing 24×7 collection. Recording ongoing performance information is vital to have a running history of the system state in case issues arise. More importantly, having a running stream of performance information gives you a running history that you can refer to for generating an ongoing baseline of your system’s resource consumption. Come learn how to set up Perfmon for ongoing collection!
Click through for the video and check out the Heraflux channel.
Comments closedShabnam Watson shows us how to kill idle SQL Server Analysis Services sessions:
Think of this method as an emergency procedure only. As always, have database backups and try this on a development server first. Always take a backup of msmdsrv.ini before you modify any server properties. The default location of the file is this: C:\Program Files\Microsoft SQL Server\MSAS15.MSSQLSERVER\OLAP\Config
If you set the timeout values too low on server that is under resource pressure, you may not be able to get to the server properties using SSMS and change them quickly within the time you set for the timeout. For this reason, I prefer the user of XMLA in this case which makes the process faster.
Read on to see how to do this.
Comments closedMikey Bronowski continues a series on dbatools:
The built-in feature of the SSMS allows us to configure a group of SQL instances and run queries against multiple instances at once. With the registered servers you can also build a list of SQL Servers in one place, so everyone with access to the CMS can see them. First, we will start by creating registered servers and server groups.
This is an underrated set of functionality for SQL Server and dbatools does a good job working with it.
Comments closed