A Fine Slice Of SQL Server
Jack Vamvas shows how you can check on the SQL Server Agent status using T-SQL:
I want to check if SQL Server Agent is running using t-sql. I know I can check through the services.msc or other methods such as Powershell scripts to report on the SQL Server Agent status , but the requirement is to extract this information via t-sql. Do you have a script using t-sql to get the SQL Server Agent Status?
There is a method and Jack shows us the way.
Comments closedGarry Bargsley gives us two methods for copying an Azure SQL Database:
Copying an Azure SQL Database is a vital skill when managing cloud databases. Recently, a request was received from the “business”. They wanted to create a copy of an Azure SQL Database that was in a development environment. The database has been certified and early testing was accepted. They now want an exact copy in QA to start integration testing. The process of making an Azure SQL Database copy is straightforward. There are several different ways to perform this action.
Two methods chosen will use the Azure Portal and PowerShell to demonstrate the completion of this request.
Click through for the demos.
Comments closedAmeena Lalani shares some good info on the tempdb database:
Tempdb is a SQL Server temporary system database. Everytime SQL Server restarts, new Tempdb database is created. Whatever data was stored in the tempdb is lost. When TempDB gets recreated its data and log files reset to their last configured size. SQL Server records only enough information in the tempdb transaction log to roll back a transaction, but not to redo transactions during database recovery. This feature increases the performance of INSERT statements in tempdb. Tempdb database is always in Simple recovery mode. If you try to change it to Full Recovery mode, you will receive the following error message.
Click through for more info on how the database is special, types of issues you can run into as concurrency grows, and ways to resolve those issues.
Comments closedErik Darling has started a new series:
If you go to open a new tab in SSMS and you hear a creaking sound, it might be time to revisit some of your hardware choices.
But that’s a problem for future you. Your problem is right now.
– How to check it: sp_PressureDetector
– What to look for: Stuck queries, Resource Usage, Wait StatsBy default, it’ll look at both CPU and Memory counters. If you don’t know what’s going on, just hit F5.
Read on to see what it looks like for a server hitting memory limits and for a server hitting CPU limits.
Comments closedMichael Sorens walks us through some tips for monitoring Kubernetes:
The world begins, of course, with kubectl, the command-line interface to Kubernetes. The commands you start using early on help you examine your Kubernetes resources.
kubectl get . . .With that command, you can examine your deployments, which rollout your replica sets, which create pods. Then you need services, which are logical sets of pods that provide an interface for external access. What can you examine with
kubectl get?Use
kubectl api-resourcesto see the list. At the time of this writing, there are 66 different resource types! That number will likely only grow over time.
Read on for more, including the setup of the Kubernetes UI and third-party tooling.
Comments closedRene Antunez diagnoses an Oracle error:
I noticed the original error after applying the October 2018 bundle patch (BP) for 11.2.0.4. While I realize most clients are no longer in 11.2.0.4, this information remains valid for anyone upgrading from 11.2 to 12, 18 or 19c.
I had been doing several tests on my Spanish RAC (Real Application Cluster) Attack for 12.2. The goal was to patch my client to October 2018 PSU; obtaining enough security leverage to avoid patching their database and do their DB (database) upgrade to 18c. I created RAC VMs to enable testing. I also set up my environment to match the client’s, which had TDE with FIPS 140 enabled (I will provide more details on this later in the post).
While the patching was successful, the problem arose after applying the patch. I was unable to open the database despite having the correct password for the encryption key.
When I first read the title, I thought it was a joke making fun of Oracle’s licensing practices.
Comments closedJohn McCormack shows how you can set up database mail from an Azure SQL Managed Instance:
It’s not too difficult to set up database mail for Azure SQL DB Managed Instance in comparison to SQL Server (on-prem or IaaS) however there are a few extra things to consider. This post will describe how to set up database mail for Azure SQL DB Managed Instance. I will use Sendgrid as the mail provider but you can follow the same steps for any other mail provider or your company’s smtp server.
Before I go on, my personal opinion is that including database mail is a massive feature for Managed Instances. The lack of DB Mail on Azure SQL DB Single Database or Amazon RDS is a major blocker to PaaS adoption. Now with Managed Instance, we can have PaaS and database mail.
Read on for the instructions. There’s a little bit more than what you typically would need to do on-premises, but just a little bit.
Comments closedEitan Blumin takes us through a troubleshooting scenario:
In short, high THREADPOOL waits can happen when SQL Server doesn’t have enough “worker threads” to handle new tasks, which could cause SQL Server to hang and refuse connections. When a task is waiting for a worker thread to become available, that wait type is called THREADPOOL wait.
A background process, called “Scheduler Monitor“, will identify when the same worker threads are “stuck” in the same state for 60 seconds or more. In which case it will resolve the issue as a Deadlocked Scheduler, and that’ll cause dropped connections, rollbacks, and even fail-overs.
When a Deadlocked Scheduler event happens, SQL Server will automatically generate a memory dump file (SQLDump#####.mdmp), and log the incident in the SQL Server Error Log.
Read on to understand what causes this as well as why we always fumble our keys under the car as the scary monster approaches.
Comments closedSasha Nosov takes us through Azure Arc deployment options for SQL Server:
As you can see, both on-Azure and off-Azure options offer you a choice between IaaS and PaaS. The IaaS category targets the applications that cannot be changed because of the SQL version dependency, ISV certification or simply because the lack of in-house expertise to modernize. The PaaS category targets the applications that will benefit from modernization by leveraging the latest SQL features, gaining a better SLA and reducing the management complexity.
Click through for a graphic, as well as further clarification on each item.
Comments closedTo some of you out there, that many databases might not be a big deal. To me, it is a big deal. That many databases can cause many admin tasks to take a very long time. One such task is to review all databases to make sure that they are not growing their files by 1MB or 10 Percent, especially the transaction log files. I find those default settings to not be a good thing generally, and have learned to set files to grow by specified amounts if auto growth needs to occur. I also try to manually grow data files during maintenance periods but we all know that auto growth happens.
So…how do we find out if our databases have 1MB or 10 Percent file growth set up?
Click through for a script which finds default-sized databases, as well as a script to fix them.
Comments closed