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 »
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 solutionRead more »
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 »
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 »
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.Read more »
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 »
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 »
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 »
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 »
In the previous article, on tracking database file storage usage How to track SQL Server database space usage with built-in functions and DMVs, I introduced some ideas and calculations for figuring out when your database is going to run out of headroom on the file system – hopefully in time to get an order in the works to increase that space!Read more »
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 »
Identifying the SQL Server database state and how a database can be moved between these different states is considered an important aspect of SQL Server database administration . A good understanding of this will help us in troubleshooting and fixing many database problems and issues.Read more »
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 »
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 »
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 »
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 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 »
Standalone Managed Service Accounts, introduced a long ago with Windows Server 2008 R2, were a ray of hope for the database administrators. They promised to provide automatic password management and simplified SPN management, meaning that the time-consuming task of maintaining passwords would be a thing of the past (not to mention the required downtime for this).Read more »
Troubleshooting SQL Server is all about gathering the right evidence. Ordinarily we utilise a variety of different methods and analyse their output to look for specific areas where we would focus our diagnostic efforts. We could for example, use the results of various DMVs to look at wait statistic and resource information to help us focus our investigation in a particular area of SQL Server.Read more »
If you have ever wanted to run a job dependent on a certain performance condition, then this article is for you.
Usually SQL Server agent jobs are configured to run on a schedule. But what if instead of a schedule you want a job to be executed when a certain performance threshold is exceeded?Read more »
A few weeks back, I was approached by a client who lives in Cape Town, South Africa. The data that he wanted to insert into Master data services was far from clean and he was wondering if there was any way have a positive influence on the data quality.Read more »
SQL Server 2008 introduces a new column attribute that is used to reduce the storage consumed by NULL values in the database tables. This feature is known as Sparse Columns. Sparse Columns work effectively in the case of the columns with high proportion of NULL values, as SQL Server will not consume any space storing the NULL values at all, which helps in optimizing the SQL storage usage.Read more »
Have you ever been in a situation where you had to manage hundres of MSSQL Servers? Well, I am right on track here and believe me it does not get easier, even minor tasks take quite the time to do in large enterprise enviornments. One of the solutions I like and I’m using to ease the administration of standard maintenance jobs across my servers was ‘master – target’ jobs. Within the article to follow I will introduce you to the concept, the prerequisites and the limitations they have.Read more »
SQL Server Policy-Based Management was introduced in SQL Server 2008, to make it easy for database administrators to define and enforce SQL Server best practices and company standards in the form of policies. This feature is available in both Enterprise and Standard SQL Server Editions.Read more »