Red Gate forums :: View topic - Databases start up 'In Recovery' in V5.4.0.22 (and 5.4.1.5)
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Storage Compress 6
SQL Storage Compress 6 forum

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

Search in SQL Storage Compress 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
epetro



Joined: 31 May 2011
Posts: 55
Location: Zotec Partners

PostPosted: Fri Aug 05, 2011 4:40 pm    Post subject: Databases start up 'In Recovery' in V5.4.0.22 (and 5.4.1.5) Reply with quote

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:
Quote:
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
Back to top
View user's profile Send private message
epetro



Joined: 31 May 2011
Posts: 55
Location: Zotec Partners

PostPosted: Fri Aug 05, 2011 5:23 pm    Post subject: Support response Reply with quote

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:
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:

##############################################################################
##
## 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.
Back to top
View user's profile Send private message
epetro



Joined: 31 May 2011
Posts: 55
Location: Zotec Partners

PostPosted: Fri Aug 05, 2011 6:52 pm    Post subject: startup/shutdown script for Windows 7 and Server 2008 Reply with quote

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:
-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
Back to top
View user's profile Send private message
Chris.Allen



Joined: 12 Mar 2009
Posts: 591

PostPosted: Mon Aug 08, 2011 1:50 pm    Post subject: Reply with quote

Thank you very, very much for posting this- I'll investigate getting some formal documentation up on our site about this.
Back to top
View user's profile Send private message
Chris.Allen



Joined: 12 Mar 2009
Posts: 591

PostPosted: Fri Aug 12, 2011 1:40 pm    Post subject: Reply with quote

So, just for completeness- this is a brilliant workaround to an issue we are currently dealing with as a priority.
Back to top
View user's profile Send private message
skyline69



Joined: 03 Feb 2010
Posts: 4

PostPosted: Wed Nov 30, 2011 4:46 pm    Post subject: SQL Compress/HyperBac Problem Reply with quote

This is still an on-going issue Chris!?
Back to top
View user's profile Send private message
mglenn



Joined: 13 Sep 2006
Posts: 16

PostPosted: Thu Feb 21, 2013 7:18 pm    Post subject: Re: SQL Compress/HyperBac Problem Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group