Maintenance

Bojan Petrovic

Optimizing SQL Server index strategies

July 2, 2019 by

Index strategies overview

This article is about techniques for optimizing the SQL Server indexes strategy. It is an appendix of the SQL index overview and strategy article in which I covered different areas like what indexes actually do, how to create them, and I briefly mentioned some index design guidelines. Furthermore, I also presented an example of how to design them by tuning and optimizing queries, so I’ve really tried to cover all but there is always more when it comes to SQL Server indexes.

Read more »
Bojan Petrovic

SQL index maintenance

January 23, 2019 by

One of the main DBA responsibilities is to ensure databases to perform optimally. The most efficient way to do this is through indexes. SQL indexes are one of the greatest resources when it comes to performance gain. However, the thing about indexes is that they degrade over time.

Read more »
Ed Pollack

Generating Schedules with SQL Server Agent

January 15, 2019 by

Summary

SQL Server Agent allows us to create jobs and assign any number of schedules to them. This allows for great flexibility when deciding when jobs should run. Over time, as the number of jobs and schedules increase on a given SQL Server, it can become challenging to fully understand when and how often jobs run during a given span of time.

Read more »
Esat Erkec

How to create an email alert in SQL Server Agent for job failures

November 28, 2018 by

SQL Server Agent is a Microsoft Windows service which helps to execute, schedule and automatize T-SQL queries, Integration Service Package, SQL Server Analysis Service queries, executable programs, operating system, and PowerShell commands. These actions which are performed by SQL Server can be called by the SQL Server Agent. Maybe, we can liken SQL Server Agent to an alarm clock because the agent will execute the scheduled task when the time comes

Read more »
Esat Erkec

Forecast SQL backup size

October 23, 2018 by

This article will cover the process of analyzing and predicting/forecasting the size of a SQL backup as a means to better handle/manage retention of backups.

One of the main database management tenets is “Do not lose your data”. According to this; a database administrator incurs huge responsibilities to protect data. Under these circumstances, taking database backups and archiving SQL backup files is a key task for database administrators. In data protection strategy, taking database backups and archiving backup file processes play the leading role. Especially, backup planning is very significant for disaster recovery scenarios because backup file will be used for restore operation after any failure or data corruption. For this reason, every dba must generate recovery strategies for possible disaster scenarios and ensure that these scenarios can be solvable. At the same time, these backup files must be tested for data integrity; thus process provides to evaluate the recovery time and integrity of backup files. In this Backup and Restore (or Recovery) strategies for SQL Server database article you can find all details about backup and restore strategies.

Read more »
James Rhoat

SQL Server Setup – Instant File Initialization (IFI)

August 31, 2018 by

If SQL Server needs to allocate space, first it fills the space it needs with zeros. Examples of when it needs to allocate space are creating/restoring a database, growing/allocating data and log files, and finally the troublesome auto growth operation. However, most of these slowdowns/long running actions can be improved by enabling instant file initialization. I say most because the one case where this is required is on log file growth. However, even when Instant file initialization has been enabled the log file will be zeroed out before the space is usable for SQL Server.

Read more »
James Rhoat

SQL Server Dedicated Admin Connection (DAC) – how to enable, connect and use

August 31, 2018 by

The dedicated admin connection (DAC) can help you out of a sticky situation. This was built to help you connect to SQL Server and run basic queries in cases with critical performance problems. This works by telling SQL Server to reserve a thread specifically for processing your queries in an emergency. While it does reserve a connection for you, it is only one thread, there is no parallelism happening here, in fact, you will receive an error.

Read more »
Gerald Britton

Shrinking your database using DBCC SHRINKFILE

August 16, 2018 by

Introduction

SQL Server is pretty good at managing disk space. As long as we do our part to set up appropriate storage types and place files and filegroups properly and set reasonable AUTOGROW settings, it’s almost a set-it-and-forget-it operation. Mind you, I said, “almost!” Sometimes, things do go BUMP! in the night and we need to act. Here’s what happened to me not too long ago:

Read more »
Ahmad Yaseen

Maintaining SQL Server indexes

May 31, 2018 by

In the previous articles of this series (see the full article TOC at bottom), we discussed the internal structure of SQL Server tables and indexes, the guidelines that you can follow in order to design a proper index, the list of operations that can be performed on the SQL Server indexes, how to design effective Clustered and Non-clustered indexes, the different types of SQL Server indexes (above and beyond Clustered and Non-clustered indexes classification), how to tune the performance of the inefficient queries using different types of SQL Server Indexes and finally, how to gather statistical information about index structure and the index usage. In this article, the last article in this series, we will discuss how to benefit from the previously gathered index information in maintaining SQL Server indexes.

Read more »
Timothy Smith

How to archive SQL Server data with scale in mind

February 21, 2018 by

We manage data in a growing environment where our clients query some of our data, and on occasion will query past data. We do not have an environment that scales and we know that we need to archive some of our data in a way that allows clients to access it, but also doesn’t interfere with current data clients are more interested in querying. With the current data in our environment and new data sets will be using in the future, what are some ways we can archive and scale our environment?

Read more »
Robert Seles

Ola Hallengren’s SQL Server Maintenance Solution – Database integrity check

January 10, 2018 by

This is the second article in Ola Hallengren’s SQL Server Maintenance Solution series. It will cover the jobs for database integrity, backup history cleanup and job history cleanup. The installation of Ola Hallengren’s Maintenance Solution is covered in the first article in the series: Ola Hallengren’s SQL Server Maintenance Solution – Installation and SQL Server Backup solution

Read more »
Robert Seles

Ola Hallengren’s SQL Server Maintenance Solution – Installation and SQL Server Backup solution

December 5, 2017 by

Database administrators tend to use various scripts or applications, to make the daily SQL Server database maintenance task easier.

Some more experienced administrators prefer to design and use their own scripts for these tasks. The scripts are usually designed to fit the requirements imposed by the specific environment. After the scripts are thoroughly tested, they often get included in a maintenance plan, or SQL Server agent job to automate their execution. Such solution is usually optimal for some demanding environments like high traffic servers or databases that are still in development. Designing these solutions usually take time, and require an experienced DBA skilled in T-SQL or PowerShell scripting.

Read more »
Ed Pollack

How to solve the SQL Identity Crisis in SQL Server

November 14, 2017 by

Description

SQL Identity columns provide a convenient way to auto-number an ID column within a table without the need to manage the sequence. This convenience can save immense amounts of time, but also presents a single challenge: What happens when an identity column runs out of space within the data type chosen?

Read more »
Ahmad Yaseen

SQL Server 2016 Maintenance Plan Enhancements

September 18, 2017 by

SQL Server Maintenance Plans is a SQL Server Management Studio built-in feature that helps in creating a workflow of variant database administration tasks, which can be run automatically using a predefined schedule or manually triggered by the user.

SQL Server Maintenance Plans allow you to use typical database maintenance tasks or customize your own task using a T-SQL script that runs on the local server or group of SQL Servers, providing more flexibility to the database administration tasks.

Read more »
Jefferson Elias

How to drop a role in a SQL Server Database

September 6, 2017 by

Introduction

Let’s say we have a database role that we don’t need anymore because we defined another security policy using another database role. What are the steps to follow in order to properly drop this database role? That’s the question this article will try to answer, covering as many cases as possible.

In the following article, we will consider the simple steps we can follow in order to do this task using both SSMS and T-SQL. Then, we will focus on some facts that will lead us to the conclusion that, if we do it this way, it won’t work every time. We will list some situations where it could fail and define a test case situation in order to create a stored procedure that will do the job correctly, in all cases bymanaging these situations.

Read more »
Sifiso W. Ndlovu

SQL replace: How to replace ASCII special characters in SQL Server

August 7, 2017 by

One of the important steps in an ETL process involves the transformation of source data. This could involve looking up foreign keys, converting values from one data type into another, or simply conducting data clean-ups by removing trailing and leading spaces. One aspect of transforming source data that could get complicated relates to the removal of ASCII special characters such as new line characters and the horizontal tab. In this article, we take a look at some of the issues you are likely to encounter when cleaning up source data that contains ASCII special characters and we also look at the user-defined function that could be applied to successfully remove such characters.

Read more »