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

Silent data compression to optimize SQL Server Storage

Moderators: eddie davis, Colin Millerchip, fionag

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

Postby epetro » Fri Aug 05, 2011 4:40 pm

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?
Last edited by epetro on Fri Nov 11, 2011 5:13 pm, edited 2 times in total.
epetro
 
Posts: 55
Joined: Tue May 31, 2011 7:55 pm
Location: Zotec Partners

Support response

Postby epetro » Fri Aug 05, 2011 5:23 pm

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
Code: Select all
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
Code: Select all
##############################################################################
##
## 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
 
Posts: 55
Joined: Tue May 31, 2011 7:55 pm
Location: Zotec Partners

startup/shutdown script for Windows 7 and Server 2008

Postby epetro » Fri Aug 05, 2011 6:52 pm

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:
Code: Select all
-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
epetro
 
Posts: 55
Joined: Tue May 31, 2011 7:55 pm
Location: Zotec Partners

Postby Chris.Allen » Mon Aug 08, 2011 1:50 pm

Thank you very, very much for posting this- I'll investigate getting some formal documentation up on our site about this.
Chris.Allen
 
Posts: 594
Joined: Thu Mar 12, 2009 4:17 pm

Postby Chris.Allen » Fri Aug 12, 2011 1:40 pm

So, just for completeness- this is a brilliant workaround to an issue we are currently dealing with as a priority.
Chris.Allen
 
Posts: 594
Joined: Thu Mar 12, 2009 4:17 pm

SQL Compress/HyperBac Problem

Postby skyline69 » Wed Nov 30, 2011 4:46 pm

This is still an on-going issue Chris!?
skyline69
 
Posts: 4
Joined: Wed Feb 03, 2010 5:32 pm

Re: SQL Compress/HyperBac Problem

Postby mglenn » Thu Feb 21, 2013 7:18 pm

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.
mglenn
 
Posts: 16
Joined: Wed Sep 13, 2006 2:36 pm


Return to SQL Storage Compress 6

Who is online

Users browsing this forum: No registered users and 0 guests