SQL Server Restart Notification

If you ever wanted to be notified by email when your SQL Servers are restarted, here is a script which will do just that using sp_procoption which sets stored procedures for autoexecution.   What it does: This script sets up a stored procedure in the master database. sp_procoption is used to configure the stored procedure […]

Read More »

Enable Dedicated Administrator Connection in SQL Server 2012

Introduction Microsoft introduced Dedicated Administrator Connection (DAC) feature in SQL Server 2005. Using DAC feature a database administrator can connect to an SQL Server Instance when SQL Server stops responding to normal connections. During such scenarios using DAC feature a DBA can connect to the instance to troubleshoot and kill processes which are causing issues. […]

Read More »

SQL Server 2012 DatabaseMail Setup

  This is an enterprise solution for sending mails from the SQL Server database engine to SMTP servers. Introduction This is an enterprise solution for sending mails from the SQL Server database engine to SMTP servers.  SQL Server database applications can communicate with users through an email system. It provides features like scalability, security, and reliability. […]

Read More »

How to Partition an existing SQL Server Table

I have read many articles on SQL Server partitioning and how to create a partitioned table, but I have an existing SQL Server database that has a few very large tables that could benefit from partitioning. What are the steps required to partition an already existing table?   I will outline below, we can partition the table […]

Read More »

How to use Data Compression?

By taking advantage of SQL Server’s data compression feature, you can improve SQL Server performance without changing any application code. Compressing data reduces database storage, which leads to fewer I/O reads and writes. However, SQL Server consumes more CPU resources when it compresses and decompresses data. SQL Server currently supports two types of data compression: […]

Read More »

Performance Improvement for Cursors in Stored Procedures

Background on this example This is a fairly simple example, but is from a real world stored procedure.  The procedure accumulates the special instructions for all tasks assigned to a department and outputs them as a single string.  In this application, each job is assigned multiple tasks required to complete the job and each of […]

Read More »

Copy over SQL Login with dbatools.io

Copy over SQL Login with dbatools.io We recently went through an exercise to move most of our SQL Server databases onto another server. Moving the databases was easy enough, we just backed them up and restored them into the new environment. However that left the problem of how to move the logins. We could have […]

Read More »

Best Practices for SQL Server Tempdb

I will take your tempdb’s peformance to new heights by making a few adjustments to the standard database settings.   What Happens in SQL Server’s TempDB? Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors. Internal objects that are created by the SQL […]

Read More »