Databases start up 'In Recovery' in V5.4.0.22 (and 5.4.1.5)

epetro

We are utilizing SQL Storage Compress for 5 of 6 databases on a server. The databases range from a native 30GB - 600GB. The release notes state:

The following issues have been fixed:

• On start-up some databases show as suspect due to the sequence in which the HyperBac service starts relative to SQL Server.

I think this is a similar issue.
I have a workaround which involves running a shutdown and startup script to take the databases OFFLINE and return them ONLINE after boot. However, I do not wish to continue such a task.

Hyperbac Configuration Manager shows these values in the Status File:
ServiceVersion=5.4.0.22 UPDATE value is now = 5.4.1.5
KernelVersion=5.4.0.5 UPDATE value is now = 5.4.1.2

Do I have any other options?
Are others experiencing the same issue?

epetro

A knowledgeable member of support has informed me directly that this is a known issue that is being investigated.

As such, I thought I would post my resolution.
Step 1: create stored proc on Master to assist OFFLINE / ONLINE operations against compressed database only.
Step 2: create powershell script that can access the database.
Step 3: create shutdown / startup script in group policy to achieve automate process.

Here is the stored Proc

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[usp_toggle_DB_state]    Script Date: 08/05/2011 12:08:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[usp_toggle_DB_state]
@request varchar(12)
as
/***
8/1/2011
Eric Petro
This stored proc will assist 
avoiding "(IN RECOVERY)" state after reboot when utilizing SQL Storage Compress.
Adjusts databases with known extensions(MDFX,NDFX,LDFX) to OFFLINE/ONLINE'

EXEC Master.usp_toggle_DB_state 'ONLINE'
EXEC Master.usp_toggle_DB_state 'OFFLINE'


***/
/*variables*/
PRINT @REQUEST
IF @REQUEST NOT IN ('OFFLINE','ONLINE') 
BEGIN
RAISERROR ('Unknown request. Please use OFFLINE or ONLINE',18,1)
goto myexit
END
	declare @stmt varchar(max)
			,@dname varchar(128)
			,@state varchar(12)
	declare curDB cursor for
	select DISTINCT
	case @request 
		when 'ONLINE'  then 'ALTER DATABASE '+Quotename(a.[Name])+' SET ONLINE'
		when 'OFFLINE' then 'ALTER DATABASE '+Quotename(a.[Name])+' SET OFFLINE WITH ROLLBACK AFTER 10 SECONDS' END
		,a.[NAME]
		,a.[STATE_DESC]
	FROM sys.databases a join sys.master_files b on a.[database_id]=b.[database_id]
	WHERE a.DATABASE_ID>4 
	  AND right(b.[physical_name],4) in ('MDFX','LDFX','NDFX')
	  AND a.STATE_DESC= CASE @request WHEN 'ONLINE' THEN 'OFFLINE' WHEN 'OFFLINE' THEN 'ONLINE' ELSE '' END

/*auto create tracking table*/
	if object_id('tmp_DB_state') IS NULL
	create table tmp_DB_state
	(dname varchar(128)
	,previous_state varchar(12)
	,executed_code	varchar(500)
	,execution_time datetime)

/*keep only 20 records*/
	delete from tmp_DB_state where execution_time not in (select top 20 execution_time from tmp_DB_state order by execution_time desc)


/*cursor through available databases*/
	open curDB 
	FETCH NEXT from curDB into @stmt,@dname,@state
	while @@fetch_status=0
	begin 
		insert into tmp_DB_state(dname,previous_state,executed_code,execution_time)
		select @dname,@state,@stmt,getdate()
		/*adjust the state*/
		--print @stmt
		EXEC (@stmt)
	FETCH NEXT from curDB into @stmt,@dname,@state
	end
	close curDB
	deallocate curDB

SELECT a.[dname],a.previous_state,b.STATE_desc[current_state],a.executed_code,a.execution_time 
FROM TMP_DB_STATE a 
  join sys.databases b on a.[dname]=b.[name] 
order by a.execution_time desc
myexit:

Here is the powershell script (adjust your credentials on line 22)
place here:
C:\Windows\System32\GroupPolicy\Machine\Scripts\Shutdown
and
C:\Windows\System32\GroupPolicy\Machine\Scripts\Startup

##############################################################################
##
## Invoke-SqlCommand.ps1
##
## From Windows PowerShell Cookbook (O’Reilly)
## by Lee Holmes (http://www.leeholmes.com/guide)
##
## Return the results of a SQL query or operation
##
## ie:
##
##    ## Use Windows authentication
##    Invoke-SqlCommand.ps1 -Sql "SELECT TOP 10 * FROM Orders"
##
##    ## Use SQL Authentication
##    $cred = Get-Credential
##    Invoke-SqlCommand.ps1 -Sql "SELECT TOP 10 * FROM Orders" -Cred $cred
##
##    ## Perform an update
##    $server = "MYSERVER"
##    $database = "Master"
##    $sql = "UPDATE Orders SET EmployeeID = 6 WHERE OrderID = 10248?
##    Invoke-SqlCommand $server $database $sql
##
##    $sql = "EXEC SalesByCategory ‘Beverages’"
##    Invoke-SqlCommand -Sql $sql
##
##    ## Access an access database
##    Invoke-SqlCommand (Resolve-Path access_test.mdb) -Sql "SELECT * FROM Users"
##    
##    ## Access an excel file
##    Invoke-SqlCommand (Resolve-Path xls_test.xls) -Sql ‘SELECT * FROM [Sheet1$]‘
##
##############################################################################
param(
    [string] $dataSource = ".\",
    [string] $database = "MASTER",
    [string] $sqlCommand = $(throw "Please specify a query."),
    [System.Management.Automation.PSCredential] $credential	
  )
## Prepare the authentication information. By default, we pick
## Windows authentication
$authentication = "Integrated Security=SSPI;"
## If the user supplies a credential, then they want SQL
## authentication


if($credential)
{
    $plainCred = $credential.GetNetworkCredential()
    $authentication =
        ("uid={0};pwd={1};" -f $plainCred.Username,$plainCred.Password)
}


## Prepare the connection string out of the information they
## provide
$connectionString = "Provider=sqloledb; " +
                    "Data Source=$dataSource; " +
                    "Initial Catalog=$database; " +
####commented out authentication to use hard coded value                    "$authentication; "
		    "uid=MYUSERNAME;pwd=MYPASSWORD"
## If they specify an Access database or Excel file as the connection
## source, modify the connection string to connect to that data source
if($dataSource -match ‘\.xls$|\.mdb$’)
{
    $connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$dataSource; "
    if($dataSource -match ‘\.xls$’)
    {
        $connectionString += ‘Extended Properties="Excel 8.0;"; ‘
        ## Generate an error if they didn’t specify the sheet name properly
        if($sqlCommand -notmatch ‘\[.+\$\]‘)
        {
            $error = ‘Sheet names should be surrounded by square brackets, and ‘ +
                       ‘have a dollar sign at the end: [Sheet1$]‘
            Write-Error $error
            return
        }
    }
}
## Connect to the data source and open it
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $sqlCommand,$connection
$connection.Open()
## Fetch the results, and close the connection
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object System.Data.DataSet
[void] $adapter.Fill($dataSet)
$connection.Close()
## Return all of the rows from their query
$dataSet.Tables | Select-Object -Expand Rows

Step 3 in next post.

epetro

To create the startup /shutdown script follow these steps
1. Open gpedit.msc (group policy editor)
2. Navigate to Local Computer Policy > Computer Configuration > Windows Settings > Scripts(Startup/Shutdown)
Note: if you are interested, there is a similar location for login/logoff in User Configuration
3. Double-Click "Shutdown" to access properties.
4. Access the "PowerShell Scripts" tab.
5. Add the powershell file created from previous forum post (mine is saved as SqlCommand.ps1)
6. Add "Script Parameters" as follows:

-Sql "EXEC Master.dbo.usp_Toggle_DB_State 'OFFLINE'"

7. Click OK to exit "Add a Script" window.
8. Click OK to exit "Shutdown Properties" window.
9. Repeat steps 3-8 for Startup (or bring your databases ONLINE manually).
10. Schedule a window where you could afford the time to recover databases in the event this fails.
11. Restart your server. (Shutdown script executes on SHUTDOWN and RESTART)
12. Post your experience to this forum.

There is a small log table created at Master.dbo.tmp_DB_state

Chris.Allen

Thank you very, very much for posting this- I'll investigate getting some formal documentation up on our site about this.

Chris.Allen

So, just for completeness- this is a brilliant workaround to an issue we are currently dealing with as a priority.

skyline69

This is still an on-going issue Chris!?

mglenn

skyline69 wrote:

This is still an on-going issue Chris!?

The fact that it's February 2013 and there's still no response to this question is not encouraging.