Databases start up 'In Recovery' in V5.4.0.22 (and 5.4.1.5)
epetro
Posts: 69
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:
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?
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?
Comments
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
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 RowsStep 3 in next post.
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: 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
The fact that it's February 2013 and there's still no response to this question is not encouraging.