Maintenance

Generating Schedules with SQL Server Agent

January 15, 2019 by Ed Pollack

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.

Cleaning up Old Code and Unused objects in SQL Server

December 31, 2018 by Timothy Smith

In this article, we are going to talk about discovering and archiving SQL references to invalid procedures.

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

November 28, 2018 by Esat Erkec

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

Six different methods to copy tables between databases in SQL Server

November 16, 2018 by Prashanth Jayaram

In this article, you’ll learn the key skills that you need to copy tables between SQL Server instances including both on-premises and cloud SQL databases. In this article, I’ll walk-through several ways of copying a table(s) between SQL databases, helping you to see the benefits and trade-offs of each option.

Forecast SQL backup size

October 23, 2018 by Esat Erkec

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.

SQL Server Setup – Instant File Initialization (IFI)

August 31, 2018 by James Rhoat

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.

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

August 31, 2018 by James Rhoat

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.

SQL Server Setup – Database Alerts

August 31, 2018 by James Rhoat

Why should you enable alerts

While the article “How to create and configure SQL S e rver Agent Alerts” created by Minette Steynberg a SQLShack author discusses the features around Alerting through SQL Agent and some conditions for testing. It does not go through some of the common alerts that you should have enabled on your SQL Server.

Shrinking your database using DBCC SHRINKFILE

August 16, 2018 by Gerald Britton

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:

Maintaining SQL Server indexes

May 31, 2018 by Ahmad Yaseen

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.

FAQ and examples about the SQL Server Agent

May 11, 2018 by Daniel Calbimonte

Introduction

In this article, we will answer FAQs about the SQL Server Agent. We will learn how to create a job, some things about the internal tables used, how to schedule jobs, add PowerShell jobs, cmd jobs, T-SQL jobs and more.

How to archive SQL Server data with scale in mind

February 21, 2018 by Timothy Smith

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?

Ola Hallengren’s SQL Server Maintenance Solution – Index and statistics maintenance

January 23, 2018 by Robert Seles

This is the third article in Ola Hallengren’s SQL Server Maintenance Solution series. The article will cover the IndexOptimize stored procedure in more detail, along with the index optimization jobs created by Ola Hallengren’s scripts.

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

January 10, 2018 by Robert Seles

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

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

December 5, 2017 by Robert Seles

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.

How to solve the Identity Crisis in SQL Server

November 14, 2017 by Ed Pollack

Description

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?

How to automatically maintain Full-Text indexes and catalogs

September 21, 2017 by Jefferson Elias

Introduction

In a previous article entitled Hands on Full-Text Search in SQL Server, we had an overview on the Full-Text feature as it’s implemented in SQL Server. We saw how to create Full-Text indexes and that they were stored inside a container called a Full-Text catalog. We’ve also seen that, by design, this kind of index will generate a fragmentation.

SQL Server 2016 Maintenance Plan Enhancements

September 18, 2017 by Ahmad Yaseen

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.

How to drop a role in a SQL Server Database

September 6, 2017 by Jefferson Elias

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.

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

August 7, 2017 by Sifiso W. Ndlovu

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.