POSTPONED to 02/11 - Speaking at the Omaha SQL Server User Group *Tomorrow* Night!

UPDATE - due to weather conditions today (snow this AM and sub-zero cold tonight) we are postponed to next Wednesday 02/11 still at Farm Credit - I will be giving this same talk and we hope to see you there!

Please RSVP at this new link: http://omahamtg.com/Events.aspx?ID=268

Thanks!

--

I will be giving my talk on "Performing a SQL Server Health Check" tomorrow night at our local SQL User Group.  We will be at Farm Credit Services of America at 6pm.  You can RSVP at this link:

http://www.omahasql.com/2015/01/28/omaha-ssug-february-2015-performing-a-sql-server-health-check/

Hope to see you there!

Log Shipping - Exclusive access could not be obtained because the database is in use

This story began as they almost always do - with a page at 4am local time...

--

'Alert: SQL Failed Jobs on InstanceName is Failed'

Job name: LSRestore_InstanceName_DatabaseName
Date & Message: 2014-07-31 05:24:00.0 The job failed. The Job was invoked by User DOMAIN\LOGIN. The last step to run was step 1 (Log shipping restore log job step.).

--

Sigh.  

I am not a fan of log shipping but I do acknowledge that it has its place.  In this case the client is using it to have a read-only standby database on a Standard Edition SQL Server (hence no mirroring+snapshot or Availability Group with readable secondary).  The database in question is large (>1TB) but with relatively small daily churn (~5GB of LOG backups per day).  Due to the traffic on the standby database during the day they have their log shipping on this database configured to only run restores during the overnight, keeping the standby database static and available for their users during the business day.

By the time I received the page, the LSRestore job has been failing for much of the overnight, with service desk'ers trying various things to try to resolve the issue.

When I signed on to the system I drilled into the job step history and found the following:

--

Microsoft (R) SQL Server Log Shipping Agent  [Assembly Version = 10.0.0.0, File Version = 10.50.1600.1 ((KJ_RTM).100402-1539 )]  Microsoft Corporation. All rights reserved.    2014-07-31 03:45:00.32 ----- START OF TRANSACTION LOG RESTORE   ----- 

2014-07-31 03:45:00.39 Starting transaction log restore. Secondary ID: '8f45136f-fb73-4815-b849-c7c1b391831b'  2014-07-31 03:45:00.39 Retrieving restore settings. Secondary ID: '8f45136f-fb73-4815-b849-c7c1b391831b'  2014-07-31 03:45:00.41 Retrieved common restore settings. Primary Server: 'InstanceName', Primary Database: 'DatabaseName', Backup Destination Directory: 'H:\LogShip\DatabaseName', File Retention Period: 1440 minute(s)  2014-07-31 03:45:00.42 Retrieved database restore settings. Secondary Database: 'DatabaseName', Restore Delay: 10, Restore All: True, Restore Mode: Standby, Disconnect Users: False, Last Restored File: H:\LogShip\DatabaseName\DatabaseName_20140730071500.trn, Block Size: Not Specified, Buffer Count: Not Specified, Max Transfer Size: Not Specified 

2014-07-31 03:45:20.77 *** Error: Could not apply log backup file 'H:\LogShip\DatabaseName\DatabaseName_20140730073000.trn' to secondary database 'DatabaseName'.(Microsoft.SqlServer.Management.LogShipping) ***  2014-07-31 03:45:20.77 *** Error: Exclusive access could not be obtained because the database is in use.  RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) *** 

2014-07-31 03:45:21.05 *** Error: The log backup file 'H:\LogShip\DatabaseName\DatabaseName_20140730073000.trn' was verified but could not be applied to secondary database 'DatabaseName'.(Microsoft.SqlServer.Management.LogShipping) ***  2014-07-31 03:45:21.05 Deleting old log backup files. Primary Database: 'DatabaseName'  2014-07-31 03:45:21.06 The restore operation completed with errors. Secondary ID: '8f45136f-fb73-4815-b849-c7c1b391831b'  2014-07-31 03:45:21.06 ----- END OF TRANSACTION LOG RESTORE     -----    Exit Status: 1 (Error)  

 --

Seeing the "Exclusive access could not be obtained because the database is in use" error (buried in the middle of the messages), I went looking for something connected to the DatabaseName database.  Sure enough there it was:

spid	blocked	dbid	uid	login_time	last_batch	status	hostname
69	0	10	1	7/30/2014 11:07	7/30/2014 11:07	sleeping	ServerName
program_name		hostprocess	cmd	loginame	stmt_start	stmt_end	request_id
Microsoft SQL Server Management Studio - Query		5404	AWAITING COMMAND	Login1	0	0	0

Basically someone opened a Management Studio window on InstanceName as loginname Login1 at 11:07am the previous day, ran one or two queries (last_batch only one second after the login_time), and then left the window open, maintaining a connection to the DatabaseName database.

Log Shipping requires exclusive access (as noted in the error) to apply the log backups, so this single Management Studio connection was effectively breaking log shipping.

Because the SPID was "Awaiting Command" with a Last_Batch of some time ago I went ahead and killed SPID 69 and manually started the "LSRestore_ServerName_DatabaseName" job.  The job completed successfully, restoring the last day's worth of logs in about ten minutes.

--

How could this be prevented?  What was configured incorrectly?

When setting up a log shipping secondary as a read-only standby, there is an option to disconnect any existing connections before attempting log shipping restores.  This option is available whether you use the Management Studio GUI or T-SQL commands.

In Management Studio, the option is "Disconnect users in the database before restoring backups."  This option is on the "Restore Transaction Log" tab on the "Secondary Database Settings" window after you select to "Add" a secondary database.  The option is only exposed if you select Standby mode - there is no need for the option in No Recovery mode since users won't be able to connect to the secondary database anyway: 

Via T-SQL, the option is a parameter of the "sp_add_log_shipping_secondary_database" stored procedure.  Set "@disconnect_users = 1" to enable the disconnect functionality.

IMPORTANT NOTE - as seen in the screenshot above, this option is not enabled by default in Management Studio, and it is also not enabled by default by the stored procedure (default value is 0).  If you wish to use this functionality you need to go "out of your way" to turn it on. Be aware of the impact of this option *before* turning it on.  If use pattern/business rules of your standby database is such that queries against the standby are more important than successful restores, do *not* enable this option as it will terminate your queries with extreme prejudice.

http://sd.keepcalm-o-matic.co.uk/i/keep-calm-and-terminate-with-extreme-prejudice.png

Hope this helps!

End of Mainstream Support for Microsoft SQL Server 2008 and 2008 R2

@DBA_ANDY: A reminder to all #sqlserver peeps out there and *especially* to our clients: #sql2008 #sql20008R2 http://blogs.msdn.com/b/sqlreleaseservices/archive/2014/07/09/end-of-mainstream-support-for-sql-server-2008-and-sql-server-2008-r2.aspx #Ntirety

Original Message:
http://twitter.com/DBA_ANDY/status/488744595136999424

--

What does this mean?

The key takeaway comes from this section of the MSDN blog:

For both SQL Server 2008 and SQL Server 2008 R2, Microsoft will continue to provide technical support which also includes security updates during the duration of extended support.  See the table below for extended support end date.  Non-security hotfixes for these versions will be offered only to customers who have an Extended Hotfix Support agreement.  Please refer to Extended Hotfix Support – Microsoft for more information.

Microsoft uses a standard cycle of mainstream support/extended support for almost all of their products, as described at http://support.microsoft.com/lifecycle/default.aspx?LN=en-us&x=14&y=6

The biggest difference between mainstream and extended support is that during the extended support period the only true support that occurs for the product is security fixes (which very rarely occur for Microsoft SQL Server) and paid support (aka 1-900-Microsoft).  There are no service packs/cumulative updates/functionality changes/online support for a product once it enters extended support.  If you pay for an Extended Support contract (I can only think of one shop I have worked with that does pay for it) then you get some added support beyond security patches, but not much.

What do we need to do?

As described in the MSDN blog, you should plan to get off Microsoft SQL Server 2008 and 2008 R2 ASAP.  Generally Microsoft keeps a product in mainstream support until the second version after becomes GA (Generally Available).  In the case, now that SQL Server 2012 *and* 2014 are out, 2008/2008 R2 are now “minus two” versions and therefore have gone out of mainstream support.

In many cases it is possible to piggy back on top of a hardware refresh project or virtualization project to try to remove as much older SQL Server as you can.

I still run lots of even older versions – SQL Server 7.0/2000/2005 – why should I care?

Many people don’t care, but I have found it is a good general IT policy is to run everything – hardware/software/etc. – within vendor-supported timelines.  Not to make the vendors $$$, but for the supportability of the product *and* for the functionality/performance of the product.

Sure, your SQL Server 2005 on Windows Server 2003 runs your database (kind of) but what would you do if the server blew its motherboard – do you have a spare eight-year-old board in stock?  What about the software (Windows/SQL Server/etc.) – each new version adds increased performance options (compression, availability groups, etc.) and improved manageability (new dynamic management views (DMV’s), Extended Events, improved tools, etc.) – what could your staff be doing if they didn’t need to continue to hand-hold those old servers?

My application vendor doesn’t support SQL Server 2012/2014 yet!

This is one of the most cited catches to advancing software, especially database platforms.  In most cases it is possible to upgrade/replace your existing server with a new current version Windows Server/SQL Server and run your “unsupported” application databases under a down-level compatibility level (for example, running the database under SQL Server 20008 compatibility (100) on a SQL server 2012 (110) server).  This option needs to be tested before it is implemented.

Where am I going to get the $$$$ to do this?

I can’t help you much there other than to refer back to my earlier point about the old server and its eight-year-old motherboard – quantify the cost to your management of the system going down, possibly for days on end while you search eBay and Craigslist for replacement hardware

 (this isn’t a joke – I worked in one shop that had to go through this). 

This is where many DBA's (and DBA Managers) get hung up in the idea of SQL Server backups.

**IMPORTANT NOTE - you absolutely need to have SQL Server backups of all of your systems with only limited exceptions – even DEV and TEST since they are functionally PROD for your developers and QA staff**

Having said that, a wonderful stack of SQL Server backups shipped securely to your offsite facility doesn’t save you from the failed eight-year-old motherboard scenario – best case you could spin up a VMware/HyperV/etc. virtual server and restore the databases there, but do you still have all of the necessary Windows/SQL Server/service pack installation media to even install Windows 2003 SP2 and SQL Server 2005 SP4CU3 patched with MS12-070?

--

I wanted to put this out there because I know that for many of you SQL Server 2008 and 2008 R2 are your base versions – there are some SQL Server 2012’s out there (and more than a few 2005’s and 2000’s) but most servers I deal with day to day are 2008/2008 R2.

Pause and consider what I have said and work together with your team and your management in the coming months to get this done.