You Really Should Stay Current on Cumulative Updates

 

I have encountered a Log Shipping error that I want to detail as an encouragement for staying current on SQL Server cumulative updates. First, a little background. The environment is SQL Server 2014 Standard edition with a physical machine as the primary and two secondaries, both virtual machines backed by a flash storage system.   SQL Server is on service pack 1, cumulative update 1. Log shipping had been configured in this environment for many months, but SQL Server 2008R2 was updated to SQL Server 2014 some months prior. The Log Shipping Restore job leaves the database in the Standby Mode so it can be queried for reporting purposes.

 

One evening I received the following error via database mail: “SQL Server Alert System: ’Severity 021’ occurred on \\MySecondary. An error occurred during recovery, preventing the database ‘MyDatabase’ from restarting. Diagnose the recovery errors and fix them or restore from a known good backup.” This was immediately followed by another email advising that the Log Shipping restore job had failed. Yet a third email was sent for error 9004 severity 16, explaining that an error occurred processing the log for “MyDatabase.” The message informed that I could restore from backup or rebuild the log. I connected to the SQL instance and saw that the database was in the restoring mode.

 

A quick internet search turned up the following two links.

https://support.microsoft.com/en-us/kb/2987585

https://support.microsoft.com/en-us/kb/2015753

 

The symptoms section of the first link seemed to fit my situation fairly well. I was unfamiliar with the fsutil command line utility. When I executed it against my environment the results indicated that the Bytes per Sector value was 512 and Bytes per Physical Sector returned “Not Supported.” The support article gave me two workarounds for addressing the issue.

  • Move the transaction log file at the destination to a drive that has “Bytes per Physical Sector” set as 512 bytes.Note The Standby file can still be located on the drive that has “Bytes per Physical Sector” set as 4096 bytes.
  • Restore the log backups without using the standby option. Instead of the STANDBY option, use the WITH NORECOVERY option during the restore operation.

 

Since my database was still in the restoring mode and the second URL indicated I had a damaged transaction log, I could not offline the database and relocate the file per the first recommendation. I also could not confirm how the bytes per sector value on the drive was configured anyway. The first article indicates that the issue was fixed in SQL Server 2014 SP1 CU2 and SQL Server 2012 SP2, CU7. Remember, I mentioned that the environment was on CU1 of SQL Server 2014 SP1. Unfortunately, that put me one update behind with respect to being protected from this situation.

 

Looks like I had one good course of action – restore from backup. I disabled the restore job, restored from a recent full backup and the most recent differential. I left the database in the restoring mode so I could re-enable the Log Shipping restore job. This would get the secondary back to Standby mode.

 

If you want to know when the latest updates for SQL Server are available, then I recommend saving the links below in your favorite web browser.  As this post demonstrates, be sure to read what the latest are about as soon as possible to see if they apply in your situation.

 

http://sqlserverbuilds.blogspot.com/

http://sqlserverupdates.com/

http://sqlblog.com/blogs/aaron_bertrand/default.aspx

 

3 Reasons Not to Install Databases On the C Drive

We’re probably all familiar with the fact that SQL Server by default will install not only the program binaries but all your databases on the C drive as well.  Accidental DBA’s, i.e. System Administrators and Developers who inherited the maintenance of a database, may not be aware of how bad it can be if someone simply clicked the “Next” button all the way through the SQL Server install.  There are surely many reasons not to accept this default during the install, but I will start with three.

First, from a purely practical standpoint, I think installing to the default path makes backups and restore scripts unduly difficult.  Which is easier to remember, “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup” or something simple like “D:\SQLBackups”? Which one would you rather have to type or copy from Windows Explorer after browsing the path?  With the second one it is so easy you don’t have to spend time browsing and copying it.  You just quickly type it out.  For the restore script would you rather type “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\SomeDB.mdf” or “D:\SQLData\SomeDB.mdf”?  The same point applies here – Simplicity.  Why make it hard on yourself?

Second, if you don’t put your databases on the C drive then database growth can’t crash the entire computer by filling up the C drive.  We all know that databases grow and that growth has to be monitored and managed.  If the System Administrator misses those emails from Database Mail or her favorite monitoring tool, then she will be awakened first thing in the morning by a company end user who can’t do their regular work only to discover that the entire machine is experiencing paralysis.  If the database files are not on the C drive with the OS then at least database growth will not be the culprit of server paralysis from a full C drive.

Third, if you install all your databases to the C drive, then you are inviting performance issues.  Databases and the OS will be fighting for drive resources.  Disks only spin so fast.  If you have the OS and 10, 14, 50 or more database files all trying to read and write to the same drive, then your C drive is going to be spinning like a yo yo.  Not only is there contention between the OS and the database files, but the database files will be fighting each other for disk usage too.

Let me provide some real numbers from an environment I have seen.  I had a system with 12 databases all on the C drive.  That number includes 4 system databases and 8 user databases.  Latency was in the thousands for several of the databases.  I moved the user databases and TempDB off to another drive and left the System databases on the c drive.  I know I can move those too, but one step at a time.  See the comparisons below.

Database FileName Overall Latency Overall Latency Next Morning Overall Latency 3 Weeks Later
Master.mdf 8330 8287 4
Mastlog.ldf 2409 1607 0
Model.mdf 8129 No Change 3
Modellog.ldf 2881 No Change 1
msdbdata.mdf 32446 16031 5
msdblog.ldf 31270 22969 0
TempDB.mdf 254 Moved to Fusion IO card Moved to Fusion IO card
Templog.ldf 155 Moved to Fusion IO card Moved to Fusion IO card

 

In some cases there was an immediate impact for latency.  Clearly, removing the activity of the eight user databases made a huge difference in performance.  As you can imagine, the user databases performed much better from a latency perspective as well, especially since I put them on a Fusion IO card.

To see latency for your drives you can run the following query from Paul Randal:

 

/*

How to examine IO subsystem latencies from within SQL Server


*/

SELECT
[ReadLatency] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
[WriteLatency] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
[Latency] =
CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
[AvgBPerRead] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
[AvgBPerWrite] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
[AvgBPerTransfer] =
CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
THEN 0 ELSE
(([num_of_bytes_read] + [num_of_bytes_written]) /
([num_of_reads] + [num_of_writes])) END,
LEFT ([mf].[physical_name], 2) AS [Drive],
DB_NAME ([vfs].[database_id]) AS [DB],
[mf].[physical_name]
FROM
sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
ON [vfs].[database_id] = [mf].[database_id]
AND [vfs].[file_id] = [mf].[file_id]
— WHERE [vfs].[file_id] = 2 — log files
— ORDER BY [Latency] DESC
— ORDER BY [ReadLatency] DESC
ORDER BY [WriteLatency] DESC;
GO

Well, I hope this has given you something to think about.  Your homework is to find out the latency in your environment, do some research, make changes as needed and then follow up to see if the changes you made helped.  Let me know how the homework turns out.

 

3 Tips for Using SQL Server Management Studio

SQL Server Management Studio, also known simply as SSMS, has many options that can be customized to help you get work done.  In this post I will cover three such options that I have found useful.

The first option discussed will be line numbers. Line numbers can be added to the query editor window to help identify sections of code.  To do this click the Tools menu.  In the drop down then select Options.  Then under Text Editor left click Transact SQL.  Now several options will be available related to what happens inside the text editor screen.  Left click the box next to Line Numbers to enable the display of line numbers next to your code in the editor.

Line Numbers Option in SSMS

 

The second option is query shortcuts. Ever find yourself retyping commands like sp_who2, sp_helpindex from Kimberly Tripp or other handy procedures?  You can actually execute these by assigning them to a keyboard shortcut rather than having to type them each time you want to run the procedure.

To do this, go back to the Tools and Options menu.  Choose Environment, then Keyboard, then Query Shortcuts.  The left column will be labeled Shortcuts.  To the right of the shortcut you wish to use simlpy left click and type the command you want to assign.  Then click OK to assign that shortcut.  Presto!  Now the next time you want to run that favorite metadata proc, just use the assigned shortcut.

SSMS Query Shortcuts

 

The third option is one of my favorite options to adjust. It is the number of recent files and projects that SSMS displays.  This list gives you access to recently opened files and projects.  The default is 10 but I don’t always find that 10 is enough for me.  To adjust this, go to Tools, then Options and Environment.  In the Recent Files section, the number can be adjusted up or down to suit your preference.

 

SSMS Recent Files Option