SQL SERVER – Introduction to Disk Systems

I generally on a drive to learn something new and if I can quickly learn from my friends who are expert in that field, then I just jump immediately to become a student. With the “teacher’s day” being celebrated last week, I was fortunate enough to get wishes from so many people. Feeling blessed, I thought I must become a student. In this blog, let me bring the notes on different disk system technologies that can be important to understand when working SQL Server database and transaction log files. More information about disk systems and considerations with SQL Server can be located here.


SCSI stands for Small Computer System Interface and is simply a protocol for disk storage. The term is most commonly used when discussing what type of disk drive would be installed on a computer (IDE vs SCSI). The most important point here is that many SCSI disk drives work with disk controllers that support read and/or write caching. Therefore, it is important to discuss with DBAs whether their caching controller has an alternate power source (such as battery backed) so if system failures occur, writes are guaranteed to be flushed to disk (and in the order in which they were submitted).


IDE stands for Integrated Drive Electronics that later standardized under the name AT Attachment, or ATA.  Many IDE drives can support large sizes (150Gb+) at inexpensive prices. One common characteristic of IDE drives is that the disk drive itself supports caching. A possible issue with some of these IDE drives that support disk caching is described in KB 234656. Many of these caches are not backed by a battery source and therefore cannot guarantee writes are flushed to disk. Furthermore, the use of the disk drive cache could result in writing reordering in which the transaction log writes may not be flushed to disk before page writes. This could potentially result in a violation of the WAL protocol.

Because of these issues, use caution when using IDE drives with SQL Server databases. Many of the drive manufactures do support disabling of the disk cache via Windows Disk Management. This is recommended for IDE drives, but could slow down expected performance.


SATA stands for Serial ATA.   These are the most common drives used in personal computers today.  The SATA data cable has one data pair for differential transmission of data to the device and one pair for differential receiving from the device. That requires that data be transmitted serially.


SAS stands for Serial Attached SCSI.  SAS is a new generation serial communication protocol for devices designed to allow for much higher speed data transfers. SAS also includes features such as advanced queuing up to 256 levels and out of order queuing. The SAS backplane is designed in a way that enables the use of both SAS and SATA drives within the same system. The benefits of the SAS design are obvious.


SAN stands for Storage Area Network. SANs are designed to share large disk storage across multiple servers while not requiring close proximity between the disk storage and computer server.  A good resource to understand the basics of SAN technologies comes from the IBM website.

A SAN is literally a network of disk storage (the actual disk media can be a different geographical location), but does not use standard network protocols to transmit data. From the perspective of the local computer accessing this disk storage, it is a local disk drive. In other words, the disk storage is not a network based mapped drive or UNC path.

SAN devices are connected using a technology called fiber channel. It is up to the SAN system to ensure read/write consistency just as though the disk was attached locally to the computer.


Network Attached Storage is simply a file server dedicated to storage that can be accessed across the network. Technically, this is not different than using a network share from another computer because any access to the NAS device is through the Windows network redirector. The difference is that NAS device is a dedicated disk storage that can reside on the network instead of disk attached to a computer. However, both a network share and NAS device must be accessed via a network mapped drive or UNC path. The best source of information on SQL Server’s support for NAS devices is in KB article 304261.

Use of NAS devices or network shares for database files can lead to some unexpected OS errors such as OS Error 64, “The specified network name is no longer available” when SQL Server fails during an I/O operation (Msg 823).


iSCSI stands for Internet Small Computer System Interface. The simplest way to think of iSCSI is that it is the implementation of the SCSI protocol across the TCP/IP network protocol. Some people are calling it “SAN over IP”. The concept is to try to cheaply implement a SAN without requiring all of the infrastructure that makes up a SAN system (including fibre channel). So effectively with iSCSI, NAS devices can be more reliable given that the SCSI protocol is used to transmit data now back and forth between the NAS device and the client computer vs using the network protocol to transmit data.

Since iSCSI devices can operate across the internet, there are of course performance concerns. Therefore, the issue is not a prevalent storage technology used in the marketplace, especially for SQL Server databases. Like SAN devices, iSCSI devices appear to the application as a local disk drive and therefore do not require trace flag 1807 to create databases on these devices. See KB article 304261 for more information about trace flag 1807 and network based database files.

Final words

I think I am exhausted by assimilating all this information and tons of learning that I got in one sitting of 30 minutes. I wrote this blog to make sure I use the notes from paper as an electronic topic for this as a reference for future.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Column Alias and Usage in Where Clause

You can assign a new name for a derived column in the SELECT statement which is also known as an alias name. But you need to understand how column alias are assigned and its scope

Let us create the following dataset

CREATE TABLE #temp (prod_id INT, transaction_Date DATETIME, amount DECIMAL(12,2))
SELECT 1,'2010-10-10',2700.00 UNION ALL
SELECT 2,'2010-12-07',340 UNION ALL
SELECT 3,'2011-09-03',1200.50 UNION ALL
SELECT 2,'2011-08-11',4100 UNION ALL
SELECT 1,'2012-01-01',17650.50

Suppose you want to find the year of the transaction date and display in the SELECT statement and also order by itself. You can use

SELECT YEAR(transaction_date) AS YEAR,amount FROM #temp

Which gives you the following result

year        amount
----------- --------------
2010        2700.00
2010        340.00
2011        4100.00
2011        1200.50
2012        17650.50

But what happens when you run the following?

SELECT YEAR(transaction_date) AS YEAR,amount FROM #temp

You get an error

Msg 207, Level 16, State 1, Line 2
Invalid column name ‘year’.

It is because the column alias are not immediately known to the WHERE clause, whereas it is known in the ORDER BY clause because ORDER BY is executed lastly after the entire column list is known.

For more information about the order of execution in the statement, refer this my earlier blog.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Identify Time Between Backups Calculation

As part of my script archives, I stumbled upon a script that I had written a long time back and thought this must get to the blog. This script was inspired from a simple question asked by an DBA when I had visited them for a session. The question was to identify the time taken between backups. I was immediately in asking why they wanted it.

The DBA told me a story that they were taking backups on a tape drive. After such backups being taken, one of the days their latest backup was corrupt. So they resorted to an old backup taken before that. They wanted to know the amount of data loss which might potentially happen because of this roaster.

I personally felt this was a simple requirement and needed to be addressed in some way. I made a rudimentary script to attack this requirement as shown below:

CREATE TABLE #backupset (backup_set_id INT, database_name NVARCHAR(128), backup_finish_date DATETIME, TYPE CHAR(1), next_backup_finish_date DATETIME);
INSERT INTO #backupset (backup_set_id, database_name, backup_finish_date, TYPE)
SELECT backup_set_id, database_name, backup_finish_date, TYPE
msdb.dbo.backupset WITH (NOLOCK)
WHERE backup_finish_date >= DATEADD(dd, -14, GETDATE())
database_name NOT IN ('master', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB');
CREATE CLUSTERED INDEX CL_database_name_backup_finish_date ON #backupset (database_name, backup_finish_date);
UPDATE #backupset
SET next_backup_finish_date = (SELECT TOP 1 backup_finish_date FROM #backupset bsNext WHERE bs.database_name = bsNext.database_name AND bs.backup_finish_date < bsNext.backup_finish_date ORDER BY bsNext.backup_finish_date)
FROM #backupset bs;
SELECT bs1.database_name, MAX(DATEDIFF(mi, bs1.backup_finish_date, bs1.next_backup_finish_date)) AS max_minutes_of_data_loss,
'SELECT bs.database_name, bs.type, bs.backup_start_date, bs.backup_finish_date, DATEDIFF(mi, COALESCE((SELECT TOP 1 bsPrior.backup_finish_date FROM msdb.dbo.backupset bsPrior WHERE bs.database_name = bsPrior.database_name AND bs.backup_finish_date > bsPrior.backup_finish_date ORDER BY bsPrior.backup_finish_date DESC), ''1900/1/1''), bs.backup_finish_date) AS minutes_since_last_backup, DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date) AS backup_duration_minutes, CASE DATEDIFF(ss, bs.backup_start_date, bs.backup_finish_date) WHEN 0 THEN 0 ELSE CAST(( bs.backup_size / ( DATEDIFF(ss, bs.backup_start_date, bs.backup_finish_date) ) / 1048576 ) AS INT) END AS throughput_mb_sec FROM msdb.dbo.backupset bs WHERE database_name = ''' + database_name + ''' AND bs.backup_start_date > DATEADD(dd, -14, GETDATE()) ORDER BY bs.backup_start_date' AS more_info_query
FROM #backupset bs1
GROUP BY bs1.database_name
ORDER BY bs1.database_name
DROP TABLE #backupset;

backupset 01 SQL SERVER   Identify Time Between Backups Calculation

For each of your databases, it lists the maximum amount of time you want between backups over the last two weeks. You can take this query to for your requirements. I had in the past written few queries with variations of these concepts which are worth a note and here for reference:

Get Database Backup History for a Single Database

Finding Last Backup Time for All Database – Last Full, Differential and Log Backup – Optimized

If you change the query, please let me know via comments so that it will help others using the script too.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #035 – Different Ways to Identify the Open Transactions

Question: What are different ways to identify open transactions in SQL Server?

Answer: There are many ways to identify the open transactions. If there are some transactions which were started and not yet committed or rollback, you can use any of these to find them

Use DBCC command

There is a specific DBCC command for this.




This will list out more details about the open transactions



Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to Enable or Disable All the Triggers on a Table and Database?

Here is the quickest way to disable all the triggers for a table. Please note that when it is about the table, you will have to specify the name of the table. However, when we have to enable or disable trigger on the database or server, we just have to specify word like database (and keep the current context of the database where you want to disable database) and all server.

Enable Triggers on a Table
ENABLE TRIGGER safety ON TableName;

Enable Triggers on a Database

Enable Triggers on a Server

Disable Triggers on a Table
DISABLE TRIGGER safety ON TableName;

Disable Triggers on a Database

Disable Triggers on a Server

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Configure the Backup Compression Default Server Configuration Option

When I get a chance to talk to Enterprise customers working with a really smart DBA team, I generally look out for options they use on a daily basis and are often missed in the bigger scheme of things. One such feature of SQL Server Enterprise Edition is the ability to do Backup Compression. It is really a powerful feature and the fine prints is that – it is disabled by default. In this blog we will look at how to view or configure the backup compression default server configuration option in SQL Server by using SQL Server Management Studio or Transact-SQL. The backup compression default option determines whether the server instance creates compressed backups by default. When SQL Server is installed, the backup compression default option is off.

Changing the setting using SSMS

To configure this setting, use the following step.

  1. In Object Explorer, right-click a server and select Properties.
  2. Click the Database settings node.
  3. Under Backup and restore, Compress backup shows the current setting of the backup compression default option. This setting determines the server-level default for compressing backups, as follows:
    • If the Compress backup box is blank, new backups are uncompressed by default.
    • If the Compress backup box is checked, new backups are compressed by default.

compression setting default 01 SQL SERVER   Configure the Backup Compression Default Server Configuration Option

If you are a member of the sysadmin or serveradmin fixed server role, you can also change the default setting by clicking the Compress backup box.

TSQL – Steps to configure backup compression default option

The T-SQL command to achieve the same will be as described below:

  1. Connect to the Database Engine.
  2. From the Standard bar, click New Query.
  3. Copy and paste the following example into the query window and click Execute. This example shows how to use sp_configure to configure the server instance to create compressed backups by default.

EXEC sp_configure 'backup compression default', 1;

If you want to find out if backup compression is enabled by default, then we can use the following TSQL command to find the same.

SELECT value
FROM sys.configurations
WHERE name = 'backup compression default';

This example queries the sys.configurations catalog view to determine the value for backup compression default. A value of 0 means that backup compression is off, and a value of 1 means that backup compression is enabled.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – What’s the Difference between ROW_NUMBER, RANK, and DENSE_RANK? – Notes from the Field #096

Kathi SQL SERVER   What’s the Difference between ROW NUMBER, RANK, and DENSE RANK?   Notes from the Field #096[Note from Pinal]: In this episode of the Notes from the Field series database expert Kathi Kellenberger explains about T-SQL Window Functions. Kathi is an amazing instructor, she was the SQL author I have read in my early career. The reason, I love SQL Server because her writing has instigated love for this technology in me. Today she brings a relatively interesting topic for database experts. Read the experience of  Kathi in her own words.

Microsoft introduced the first T-SQL window functions ten years ago with SQL Server 2005!  They introduced more T-SQL window functions with SQL Server 2012. I’ve been on a mission to educate the SQL Server and developer communities about T-SQL window functions since then.

Feedback from my sessions tell me that ROW_NUMBER is the most used T-SQL window function. ROW_NUMBER returns a unique number for every row in the results. (NOTE: If the PARTITION BY option is used, it returns a unique number within each partition.)

Did you know that two other similar functions, RANK and DENSE_RANK, were introduced at the same time? RANK and DENSE_RANK are comparable to ROW_NUMBER, but they handle ties in the ORDER BY expression differently.

To demonstrate the differences, I have chosen a customer from AdventureWorks that placed two orders on the same day. These three functions require an ORDER BY expression in the OVER clause. This tells SQL Server in which order to apply the numbers generated by the window function. In this case, I am using OrderDate, which is not unique.

SELECT CustomerID, SalesOrderID, CAST(OrderDate AS DATE) AS OrderDate,
FROM Sales.SalesOrderHeader
WHERE CustomerID = 11300;

Take a look at the partial results, starting where the numbers returned by the functions begin to get interesting.

notes 96 SQL SERVER   What’s the Difference between ROW NUMBER, RANK, and DENSE RANK?   Notes from the Field #096

The OrderDate values were unique until the 7th row, and all three functions return 7 on row 7. The 8th order has the same OrderDate as row 7 so it’s a tie. ROW_NUMBER doesn’t care; it continues to provide unique incrementing numbers. RANK and DENSE_RANK both return 7 again because both rows 7 and 8 are ranked the same.

Notice that on row 9, RANK “catches up” with ROW_NUMBER by skipping the value 8. RANK catches up with ROW_NUMBER every time once it’s past the tie. DENSE_RANK, on the other hand, does not. Instead of catching up, it returns the next possible value. DENSE_RANK doesn’t skip any numbers.

Take a look at row 9 again. ROW_NUMBER returns the position of the row. RANK returns the rank of the row based on its position. DENSE_RANK returns the logical rank: the rank over unique OrderDate values. The row with OrderDate 2913-11-14 is in the 9th position. It is ranked 9th over the set of rows. It is ranked 8th over the set of unique OrderDate values.

If you use a unique ORDER BY expression, all three functions will return the same values because there will be no ties.


These three functions are usually a step along the way to a more complex solution. Understanding how they work and how they differ will help you decide which one to use when faced with the choice.

notes 82 3 SQL SERVER   What’s the Difference between ROW NUMBER, RANK, and DENSE RANK?   Notes from the Field #096If you would like to learn more about T-SQL window functions, be sure to check out my latest book Expert T-SQL Window Functions in SQL Server or my Pluralsight course T-SQL Window Functions.

If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – FIX – Error: Alter failed for Server ‘Server Name’ in Check Integrity task

Recently I was getting ready for an upcoming demo session and found that my maintenance plan for CHECKDB were failing. When I executed manually, I got below error

Execution failed. See the maintenance plan and SQL Server Agent job history logs for details.

I followed error message and checked the history of maintenance plan, here was the error.

Failed: (0) Alter failed for server ‘ComputerName\InstanceName’

My research yielded https://support.microsoft.com/en-us/kb/945067 which was talking about incorrect setting of Allow Updates in sp_configure. In my case it was just fine. I have put profiler and found below statement failing.

EXEC sys.sp_configure N'user options', 0 RECONFIGURE

When I executed below statement from SQL Server Management Studio, I got below error

Alter failed 01 SQL SERVER    FIX   Error: Alter failed for Server Server Name in Check Integrity task

Configuration option ‘user options’ changed from 0 to 0. Run the RECONFIGURE statement to install.

Msg 5807, Level 16, State 1, Line 1
Recovery intervals above 60 minutes not recommended.
Use the RECONFIGURE WITH OVERRIDE statement to force this configuration.

In my case, it was recovery interval value which I as 10000, was the problem. Once I bought it down to 60, I was able to run the maintenance plan without any problem.

sp_configure 'recovery interval (min)', 60

As shown above, the maintenance plan is using the “reconfigure” command and getting an error. Due to that it wasn’t able to proceed further and failing with “Alter failed for server” error. Hope it’s clear that if you are getting the same error, you should run the RECONFIGURE command from SQL Server Management studio and check the any error. Here are the various errors which I saw.

  1. Make sure that “Allow updates” is 0
  2. Make sure that the “recovery interval (min0” is less than 60
  3. Make sure that the service account is having “Lock Pages in Memory” when AWE is enabled.

Later, when I tried to reproduce the same on SQL 2012 and SQL Server 2014, I was not able to. This is a good news that the issue is fixed by Microsoft in later versions.

Have you ever found the same error and the solution was other than three which I mentioned? Please comment and let me know so that it can help others also.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Transparent Data Encryption and Frequently Asked Questions

In the recent past, I have been traveling to Delhi to meet customers and also attend a few conferences which my company has been sponsoring. These travel experiences give me an opportunity to meet folks who read my blog regularly as a face-to-face interaction. Most of these interactions mean I get to spend time with really smart people who quiz me for which I get back to my blog for answers. Last week, when I was at a conference, one DBA walked up to me and said – “Hey Pinal, I am a DBA from a reputed banking company. We are in the process of deploying TDE for one of our databases which is in SQL Server 2008 R2. I had a few doubts, can I ask you?” Well, as simple as this interaction went, the whole conversation spanned for close to 30 mins and I am doing a summary of the conversation in this blog for your reference.

When does TDE encrypt the DB?

Once Transparent Data Encryption is enabled by issuing the “Alter Database” command, SQL Server performs basic checks such as Edition Check, Read-only Filegroups, and presence of DEK etc. Once the checks are complete, the command returns immediately with success. This does not mean that the database encryption is complete. Since Encryption is done in the I/O path, all the data pages that are already written to the disk prior to enabling the Encryption have to be read into the memory and then written back to the disk after encrypting the page. This process is also referred to as “Encryption scan”. This task is carried out by Background processes (system SPIDS). The encryption scan, which runs asynchronously to the DDL, takes a shared lock on the database. All normal operations that do not conflict with these locks can proceed without being blocked.

You mentioned READONLY DB, tell me more?

Transparent data Encryption does not work on a database that contains any filegroups that are marked Read-Only or any files that are marked as Read-only. Alter Database fails with an error message (33118) clearly indicating the reason for the failure. Users can enable read-only property on the filegroups once the encryption scan is completed. However, no DDL related to TDE (DEK change) can be run on the database until the read-only property is removed.

What happens to TLog files?

Encryption works differently on a Transaction Log and is complicated. Since Transaction Log is designed to be Write-Once fail safe, TDE does not attempt to Encrypt the contents of the Log file that were written to the disk already. Similarly, the log header cannot be re-written because of this write-once principle so there is no guarantee that log records written to the log even after TDE is enabled will be encrypted. The smallest unit of encryption for log files is a virtual log file (VLF). So either an entire virtual log file (VLF) is encrypted or it’s not. Also the entire VLF is encrypted with the same key. When encryption is turned on for a database, the next time the log manager moves to a new VLF, this new VLF will be encrypted. So there is no deterministic order between when data pages are encrypted by the scan vs. when the log is encrypted.

I thought these were some of the interesting conversations I have had in the recent past that are worth a mention to share. If you have used TDE in your environment, do let me know. I would love to know your experiences in working with it. Is there any catch that you want me to explain next time? Write it over as comments and would love to explore more.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SPID is KILLED/ROLLBACK state. What to Do Next?

I always recall my fun days after talking to my friends and blog readers. Recently killed/rollback discussion came back when my close DBA friend called me for help. Our discussion was worth blogging.

If you are a SQL DBA or developer, I am sure you must have seen something like below:

killed 01 SQL SERVER – SPID is KILLED/ROLLBACK state. What to Do Next?

The first question which would come to mind would be “what is killed/rollback command?” The answer is very simple – sometimes, DBA/Developer use “KILL” command to end processes that appear in a hung state. Once that happen, if SQL knows how to rollback that work, it would start “real” rollback process and would undo the work done. There are situations where SQL doesn’t know and has no control of work done of the work done and it has no control to roll back the work. These are the cases where its stays in KILLED/ROLLBACK state forever.

How would you decide which action to take? It completely depends on the action done by the SPID which was killed. There are only a few things, which you can do:

  1. Have patience and wait for rollback to finish. Some large operations may take a long time to rollback. Such SPID should show a change in CPU, Memory and IO column in size. sys.sysprocesses DMV (query in the next step).
  2. If the SPID is not showing any change in CPU, Memory and IO column then it is doing nothing. Here is the query which can tell us more about the SPID and from where the connection might have originated.

FROM sys.sysprocesses

If the process is originated from different machine, then using task manager and doing end process on that machine might help. This decision has to be taken very carefully as it might be a process of a 3rd party application.

After figuring out the SPID we can run the DBCC INPUTBUFFER (<SPID>) to know what is the batch running under this session.

  1. Restart SQL Service if it was killed and doing nothing.

Can you reproduce it? The simplest way is to use xp_cmdshell and open notepad.exe using below command. DON’T DO THIS ON PRODUCTION

xp_cmdshell 'notepad.exe'

Now, it you kill this SPID it would go to KILLED/ROLLBACK state.

In summary, it is important to understand the cause. Killed/Rollback SPIDs are waiting for an event that will never happen. It could be a real “rollback” situation where SPID is rolling back and trying to get the database into a consistent state. By restarting the SQL Server service, you are postponing that rollback (or roll forward) work to the recovery step that runs before the database becomes available.

Have you ever come across such situation?

Reference: Pinal Dave (http://blog.sqlauthority.com)