Tips for Read/Write Locks Depending on Transaction Isolation Level in MSSQL

Tips for Read/Write Locks Depending on Transaction Isolation Level in MSSQL
Rate this post

Read Uncommitted

  • If data is being changed in one transaction, selection of this data (in other transaction or without a transaction) will not wait till the first transaction is finished and will return data entries of uncommitted transactions.
  • If data is being read in one transaction, updates of this data in other transaction will not wait till the first transaction is finished.
  • Shared locks are not used. Identical to setting the NOLOCK hint for all selections in Read Committed.
  • Exclusive locks are enabled during statement execution and disabled at the end of the transaction.

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

Optimizing Overlapping Queries Part 1: Introduction & Enhanced T-SQL Solution

Optimizing Overlapping Queries Part 1: Introduction & Enhanced T-SQL Solution
Rate this post

Databases that serve business applications should often support temporal data. For example, suppose a contract with a supplier is valid for a limited time only. It can be valid from a specific point in time onward, or it can be valid for a specific time interval—from a starting time point to an ending time point. In addition, many times you need to audit all changes in one or more tables. You might also need to be able to show the state at a specific point in time or all changes made to a table in a specific period of time. From the data integrity perspective, you might need to implement many additional temporal specific constraints.

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

Comparing Objects by Value. Part 6: Structure Equality Implementation

Comparing Objects by Value. Part 6: Structure Equality Implementation
Rate this post

We have already analyzed peculiarities of structs of the .NET framework that represent Value Types when comparing objects by value – instance of structs.

Now, I am going to describe this process on a particular example to check whether it will allow us to determine the use of the object comparison by value in general and thus, to simplify a sample of comparing objects by value – class instances that represent reference types.

Read More