Infrastructure at your Service

Tag

Microsoft Archives - Page 2 of 23 - Blog dbi services

Steven Naudet

SQL Server: Fixing another huge MSDB database – 80GB+

By | Database Administration & Monitoring, SQL Server | No Comments

I have blogged several times about unusually large MSDB databases like here. Most of the time the problem comes from the backup history which is never purged. This time it’s different. Try to guess, if not the backup history, what can cause MSDB to increase abnormally in size? Let’s start by looking at the total size of the database: It’s huge. The database size is almost 90GB. The 10% configuration on data files causes large…

Read More
Stéphane Haby

SQL Server 2019: What’s new in sp_configure and sys.configurations options?

By | Database Administration & Monitoring, Database management, SQL Server, Technology Survey | No Comments

SQL Server 2019 added new options in sp_configure and sys.configurations. First, how can we find the difference between these SQL Server Versions. This simple query will give us the number of options and the SQL Server Version: select count(*),@@version FROM sys.configurations In SQL server 2016, we have 74 parameters for the instance configuration: In SQL server 2017, we have 77 parameters for the instance configuration: In SQL server 2019, we have 84 parameters for the…

Read More
Stéphane Haby

SQL Server SCOM Alert Analysis: The Average Wait Time of SQL instance “xxx” on computer “xxx” is too high

By | Database Administration & Monitoring, Database management, Development & Performance, SQL Server | No Comments

I have an interested alert on SCOM for SQL Server by a customer two weeks ago. In the SCOM interface, the warning on the server is MSSQL2016: The Average Wait Time of SQL instance “xxx” on computer “xxx” is too high. After right-click on the properties, go to the tab Alert Context. On this part, you can see that the Object Name is MSSQL$xxx:Locks. My first step is to go in the Reports>Standard Reports> Performance…

Read More
Stéphane Haby

SQL Server SCOM: Many perf.perfHourly and State.StateHourly tables in OperationManagerDW

By | Database Administration & Monitoring, Database management, Development & Performance, SQL Server | No Comments

By a customer, the SCOM’s Database OperationManagerDW growth fast and becomes very big. To see what’s happens, in SSMS (SQL Server Management Studio), I right-click on the database open Reports>Standard Report> Disk Usage by Top Tables: You have many interesting reports with SSMS and I advise you to use it. In my case the “Disk Usage by Top Tables” report displays me many perf.perfHourly and State.StateHourly tables with a _guid(): After reading some blog’s, the…

Read More
Stéphane Haby

SQL Server: How to compare Server and Database Collation

By | Database Administration & Monitoring, Database management, SQL Server | No Comments

Sometimes during an SQL Server audit, I check the collation of the server and the databases but every time separately. The goal of this blog is to give you a script to have both and compare it. First step is to have the server collation and the code page. The code page will help to see if the character set is the same. For example, for us, the 1252 is Latin/Western European and the 1258…

Read More
Stéphane Haby

SQL Server: Analysis of SCOM Warning on the Health Check

By | Database Administration & Monitoring, Database management, SQL Server | No Comments

Sometimes, it’s hard to find how to analyze a Warning in the SCOM Interface and correct the problem. By a customer, we install new instances in SQL Server 2019 and change the version of SCOM. All new servers have a warning on the Health. The first step in the SCOM interface is to go to the SQL Server Roles: On this step, the most important is to open the Health Explorer and not double-click or…

Read More
Stéphane Haby

SQL Server Tips: Get back permissions lost for mount points!

By | Database Administration & Monitoring, SQL Server | No Comments

Recently by a customer, we are facing the case to lose all mount points on a secondary node in a HA SQL Server AlwaysOn infrastructure due to a big problem on the Datacenter. We need to build from scratch all mount points for the Data, Log and Tempdb. After multiple researches, I didn’t find how to give back correctly the permission for the SQL Server account on these mount points. The disk environment is very…

Read More
Steven Naudet

Virtual Symposium SQL Server & Azure SQL – Session: Debugging without debugger

By | Database Administration & Monitoring, SQL Server | No Comments

As a consultant at dbi services we can dedicate a significant part of our time to training and I took the opportunity today to attend an online conference. In this blog post, I will present this event and one of the sessions that particularly interested me. The event I followed is the Virtual Symposium – SQL Server & Azure SQL organized by SQLServerGeeks. It’s a free event that can easily be followed on Zoom but…

Read More
Steven Naudet

SQL Server CPU limitation for Express and Standard Edition

By | Database Administration & Monitoring, SQL Server | One Comment

Introduction When performing an audit on an SQL Server VM the first thing the system administrator told me was that there’s no issue with the CPU on this box, the monitoring shows it’s always around 50% usage. Problem Here is the Task Manager: The average CPU usage on this VM is indeed 50% but half of the cores are stuck at 100%. There are two SQL Server instances installed on this server but they are…

Read More
Steven Naudet

SQL Server: Get email notifications for Error Log entries

By | Database Administration & Monitoring, SQL Server | No Comments

Introduction In a previous blog post, I did a T-SQL script reading Audit files to send emails with valuable information about instance changes. In this one, you will see how to get nice emails from critical errors or specific events occurring on the instance using the ErrorLog. A common best practice for any DBA is to configure SQL Server Agent Alert for errors with severity 17 to 25. Alerts are also often set for less…

Read More