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. Read more »
Considerations and concerns when setting up Database mail for SQL Server August 31, 2018 by James Rhoat Database mail is widely used by DBAs and companies around the world and is one of the features of SQL Server that can be very important for startups. That is because it is a cheap solution for getting alerts from your SQL Server for potential hardware issues, early warning signs of corruption, along with potential resource constraints. However, it can be misused or pose potential security issues. Before we start, to my myself clear I am in favor of configuring Database mail for the DBA team when done properly. In the rest of the article, we will touch on some of the common mistakes people make with database mail and their SQL Servers. We will not discuss the setup of this feature as it was well documented by Bojan Petrovic on SQLShack. Read more »
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. Read more »
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. Read more »
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 Server 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. Read more »
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. Read more »
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. Read more »
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 Read more »
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. Read more »
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? Read more »
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. Read more »
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. Read more »
How to drop a SQL Server Login and all its dependencies July 19, 2017 by Jefferson Elias Introduction Sometimes, we need to drop a SQL Server Login because we gave an access to a person who left, this login was used by an application that has been decommissioned for example. Read more »
Overview of Resumable Indexes in SQL Server 2017 June 26, 2017 by Prashanth Jayaram Managing indexes is a critical component of database maintenance but we often don’t think about the indicators behind the index maintenance operations. SQL Server 2017 (CTP 2.0) introduces a very useful index feature, to mitigate the administration overhead of index maintenance which we’ll review and discuss in this article. Read more »
Deep dive into SQL Server Extended events – The Event Pairing target May 30, 2017 by Kaloyan Kosev Extended event sessions can use multiple targets to store captured information. We should use different targets depending on the type of data we are capturing and the structure of the data outcome we desire. The available targets include the following: event counter, ring buffer, event file, histogram, event tracing for Windows (ETW), and Event Pairing. Read more »
Deep dive into the Extended Events – Histogram target May 24, 2017 by Kaloyan Kosev An Extended events target is the destination for all of the information that is captured by Extended Events sessions. You can rely on couple of different targets such as event counter, event file, event tracing for Windows (ETW), ring buffer, event pairing, and histogram. Read more »
How to Spoof Production Environment Resources for Performance Tuning May 16, 2017 by Ahmad Yaseen Performance tuning is one of the most important and critical tasks that the SQL Server Database Administrator performs on a daily basis to keep a SQL Server running in a healthy state, by identifying the performance bottlenecks and fixing the main cause of these problems. Read more »
How to use Microsoft Assessment and Planning (MAP) Toolkit for SQL Server March 31, 2017 by Musab Umair As a Microsoft SQL Server Database Consultant, I have to visit multiple companies with variety of SQL Server versions and patch level. Generally speaking, we need to analyze the environment of these clients and get information about the infrastructure. To do so, we have to run multiple scripts and tools on almost all the machines at client. Though it’s a part of our job but at times it takes hours to get even the basic information about the SQL Server versions, patch level, configuration and small details which even client does not have. Read more »
How to discover and handle orphaned database users in SQL Server March 30, 2017 by Jefferson Elias Introduction Context As SQL Server database administrators, we should all know that, most of the time, a database user is linked to a SQL Server login. We do this to tell SQL Server that a SQL login LoginA has access to database Db1 using the context and permissions of UserA database user. Read more »
CHECKSUM page verification in SQL Server March 21, 2017 by David Alcock CHECKSUM is an option for page verification that is available at the database instance level and we can see what level of verification each of our databases are currently using by the following query: Read more »
