Category Archives: SQL Server 2008 R2

Database is 100 percent restored but restore command still running and DB is in restoring mode

9 Replies

100 percent restored. [SQLSTATE 01000]

Processed 52345768 pages for database ‘SQLDBPool’, file ‘SQLDBPool_Data’ on file 1. [SQLSTATE 01000]

Processed 3045777 pages for database ‘SQLDBPool’, file ‘SQLDBPool_Log’ on file 1. [SQLSTATE 01000]

Many times you came across a situation where the Database restore is completed but you can see the restore command is running for a long time and your database status is restoring. We always want to know why database restore is taking so long time.

Let’s first check out what restore is doing internally. Restore session performs below three tasks while you execute the restore command.

  • Data Copy Phase
  • Redo phase
  • Undo phase

The data copy phase involves copying all the data, log, and index pages from the backup of a database to the database files. After the completion of this phase SQL Server reports restore completes 100 percent.

In the Redo phase, all the committed transactions present in the transaction log when the database was backed up are rolled forward. It means all the committed transaction changes applied to the database. Still the database has un-committed transaction so database will be in unusable state.

In the Undo phase, all the uncommitted transactions in the transaction log while the database was backed up are rolled back. If the database is being restored with NORECOVERY, the Undo phase is skipped.

From the above explanation you can understand why the restore command is taking longer time after 100 percent completes.

T-SQL script to Identify the SQL Server version and edition

Leave a reply

Execute below T-SQL Script to get the SQL Server version details.

SELECT SERVERPROPERTY ('productversion') as ProductVersion,
SERVERPROPERTY ('productlevel') as Productlevel,
SERVERPROPERTY ('edition') as Edition

Output:
-Product version (for example, 10.0.1600.22)
-Product level (for example, SP3)
-Edition (for example, Development)

SQL Server 2012 version number examples
11.0.2316 SQL Server 2012 CU1
11.0.2100.6 SQL Server 2012 RTM

Using a PowerShell Script to delete old files for SQL Server

Leave a reply

Problem

Many clients are using custom stored procedures or third party tools to backup databases in production environments instead of using database maintenance plans. One of the things that you need to do is to maintain the number of backup files that exist on disk, so you don’t run out of disk space.  There are several techniques for deleting old files, but in this tip I show how this can be done using PowerShell.

Solution

http://www.mssqltips.com/sqlservertip/2726/using-a-powershell-script-to-delete-old-files-for-sql-server/

Different ways to make a table read only in a SQL Server database

Leave a reply

Problem

In some cases there may be a need to make a SQL Server table read only. There are several different options for doing this and in this tip we cover various ways that you can make a table read only in a SQL Server database.

Solution

http://www.mssqltips.com/sqlservertip/2711/different-ways-to-make-a-table-read-only-in-a-sql-server-database/#comments

FIPS cryptographic algorithms validation error reporting service

3 Replies

Problem: Recently we have installed the SQL Server 2008 Reporting Service on the Windows Server 2008. Reporting Service installed and configured successfully but while browsing the http://servername:80/reports it is getting failed with the error HTTP 500. Below error recoded into the Reporting Service Error log.

Unable to validate data. at System.Web.Configuration.MachineKeySection.GetDeco dedData(Byte[] buf, Byte[] modifier, Int32 start, Int32 length, Int32& dataLength) at System.Web.UI.ObjectStateFormatter.Deserialize(Str ing inputString)

Solution:

It looks reason for the above error is two different encryptions (FIPS and AES) and decryption algorithm is trying to do the validation and because of that reporting service is unable to load the reporting service URL. I have resolved the issue by following the below steps.

Reporting Service will fail to load if any of the below condition will be true.

The HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\fipsalgorithmpolicy registry value is set to 1.

ASP.NET 2.0 uses the RijndaelManaged implementation of the AES algorithm when it processes view state data

You can follow one of the below fix. Either disable the FipsAlgorithmPolocy or add the mentioned machine key into the web.config file. Browse the path HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\fipsalgorithmpolicy and changed value 1 to 0.

Open the reporting service web.config file and add the below <machinekey> into <system.web> section.

<machineKey validationKey=”AutoGenerate,IsolateApps” decryptionKey=”AutoGenerate,IsolateApps” validation=”3DES” decryption=”3DES”/>

Next step is to restart the reporting service and IIS. You can restart the IIS using IISRESET command and reporting service using NET START/STOP command.

NET STOP “SQL Server Reporting Services (MSSQLSERVER)”

NET START “SQL Server Reporting Services (MSSQLSERVER)”