| Author |
Message |
epetro
Joined: 31 May 2011
Posts: 55
Location: Zotec Partners
|
 Posted: Fri Aug 05, 2011 4:40 pm Post subject: Databases start up 'In Recovery' in V5.4.0.22 (and 5.4.1.5) |
 |
|
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 |
|
 |
epetro
Joined: 31 May 2011
Posts: 55
Location: Zotec Partners
|
| Posted: Fri Aug 05, 2011 5:23 pm Post subject: Support response |
|
|
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 |
|
|
epetro
Joined: 31 May 2011
Posts: 55
Location: Zotec Partners
|
| Posted: Fri Aug 05, 2011 6:52 pm Post subject: startup/shutdown script for Windows 7 and Server 2008 |
|
|
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 |
|
|
Chris.Allen
Joined: 12 Mar 2009
Posts: 454
|
| Posted: Mon Aug 08, 2011 1:50 pm Post subject: |
|
|
| Thank you very, very much for posting this- I'll investigate getting some formal documentation up on our site about this. |
|
| Back to top |
|
|
Chris.Allen
Joined: 12 Mar 2009
Posts: 454
|
| Posted: Fri Aug 12, 2011 1:40 pm Post subject: |
|
|
| So, just for completeness- this is a brilliant workaround to an issue we are currently dealing with as a priority. |
|
| Back to top |
|
|
skyline69
Joined: 03 Feb 2010
Posts: 4
|
| Posted: Wed Nov 30, 2011 4:46 pm Post subject: SQL Compress/HyperBac Problem |
|
|
| This is still an on-going issue Chris!? |
|
| Back to top |
|
|
mglenn
Joined: 13 Sep 2006
Posts: 16
|
| Posted: Thu Feb 21, 2013 7:18 pm Post subject: Re: SQL Compress/HyperBac Problem |
|
|
| 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 |
|
|
|
