Configuring Database Mail Notifications in MS SQL Server

Configuring Database Mail Notifications in MS SQL Server
Rate this post

Introduction

Often, there is a need to somehow inform administrators about the problems with a server. Notifications are generally divided into 2 types:

1) real-time notifications, i.e. those that must come immediately when a problem occurs

2) delayed notifications, i.e. those that come after a fairly long time (more than 1 hour) after a problem occurs. Read More

SQL Server Security Ponderings – Part 2 | Database owner + TRUSTWORTHY

SQL Server Security Ponderings – Part 2 | Database owner + TRUSTWORTHY
Rate this post

This article is the second one of the three articles devoted to a particular security configuration combination of database security.

In my previous article, I presented a scenario in which we were able to compromise data in a SQL Server database.

I would like to note that the knowledge of this configuration combination is critical. In this article, I am going to provide further information and reasons for the importance of this issue. Read More

Automatic Data Collection: Database Files and Logical Drives in MS SQL Server

Automatic Data Collection: Database Files and Logical Drives in MS SQL Server
Rate this post

Introduction

It is important for a database administrator to know when there is no space on a disk. Thus, it is better to automate the process in order for them not to do it manually on each server.

In this article, I am going to describe how to implement automatic daily data collection about logical drives and database files.

Read More

Automatic Data Collection of Database Schema Changes in MS SQL Server

Automatic Data Collection of Database Schema Changes in MS SQL Server
Rate this post

Introduction

Have you ever faced a situation when you need to make changes in a stored procedure or a view very quickly? I have, very often, especially at the implementation stage. Unfortunately, a version control system cannot help in this case. Still, how could I understand that something has been modified, and when?

This article describes a possible solution for automatic data collection about database schema changes in MS SQL Server. As usual, I will be glad to hear any alternative solutions.

Read More

Automatic Data Collection about Completed Tasks in MS SQL Server

Automatic Data Collection about Completed Tasks in MS SQL Server
Rate this post

Introduction

It is important for a database administrator to know what tasks and how have they been completed. To simplify this process, it is better to automate it, rather than perform it manually.

In this article, I will analyze on a particular example how to automatically collect data about completed tasks of the SQL Server Agent.

Read More

Automatic Deletion of Stuck Processes in MS SQL Server

Automatic Deletion of Stuck Processes in MS SQL Server
Rate this post

Introduction

There are situations when applications keep the database connection for a long period of time. It seems to be not important. However, if this application makes many connections or there are several applications with such behavior — things are getting worse.

This article is not a tutorial. It describes possible solutions to this issue. As usual, I will be glad to hear any alternative solutions.

Read More

Troubleshooting long running queries in MS SQL Server

Troubleshooting long running queries in MS SQL Server
Rate this post

Preface

There is an information system that I administer. The system consists of the following components:

1. MS SQL Server database
2. Server application
3. Client applications

These information systems are installed on several objects. The information system is used actively 24 hours a day by 2 to 20 users at once on each object. Therefore, you cannot perform routine maintenance all at once. So, I have to «spread» SQL Server index defragmentation throughout the day, rather than defragmenting all the necessary fragmented indexes at one stroke. This applies to other operations as well.

Read More

Automating Index Defragmentation in MS SQL Server Database

Automating Index Defragmentation in MS SQL Server Database
Rate this post

Preface

The World Wide Web offers a bunch of information on SQL Server index defragmentation or SQL Server  index rebuild. However, most of the recommendations refer to databases that have minimum load time (mostly, at night).

And what about databases that are used for both, data modification, and retrieving information on a 24/7 basis?

In this article, I will provide a mechanism for automating SQL Server index defragmentation implemented in a database used in the company I work for. This mechanism allows defragmenting required indexes on a regular basis since index fragmentation takes place constantly in the 24/7 system. Often, this is not enough to perform index defragmentation once a day. Read More