MSSQLWIKI

Karthick P.K on SQL Server

Archive for January, 2012

SQL-Server resource fails to come online IS Alive check fails

Posted by Karthick P.K on January 31, 2012

SQL-Server resource fails to come online with below Error:

[sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = 35; message = [Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible.

Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

 

Resolution:

Look at the version of (c:\windows\system32\sqsrvres.dll) and install the same version of SQL Server native client.

Cause:

When Higher version of SQL-Server is installed on a cluster in which lower version of SQL Server is already installed, the lower version SQL Server Resource DLL (c:\windows\system32\sqsrvres.dll) is upgraded to higher version and Higher resource DLL will be loaded by the resource monitor process to monitor Lower version as well.

For example: The Denali SQL Server Resource uses SNAC 11.0 to connect to the SQL instance and because SNAC 11.0 can be used to connect to Shiloh, Yukon and Katmai as well this side by side configuration will work. However if Denali is uninstalled, the Denali SQL Server resource DLL is not downgraded to Katmai, Yukon or Shiloh version and hence care should be taken to not uninstall SNAC 11.0 otherwise Yukon or Shiloh instance cannot be brought online.

Similarly When we install Yukon and Shiloh together, Yukon SQL Server Resource uses SNAC to connect to the SQL instance and because SNAC can be used to connect to Shiloh as well this side by side configuration will work. However if Yukon is uninstalled, the Yukon SQL Server resource DLL is not  downgraded to Shiloh version and hence care should be taken to not uninstall SNAC otherwise Shiloh instance cannot be brought online.

 

If you liked this post do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group MSSQLWIKI

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Disclaimer
The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Posted in Configuration, Connectivity, SQL General, SQL Server Cluster | Tagged: , , , , | 8 Comments »

The backup of the file or filegroup "" is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.

Posted by Karthick P.K on January 25, 2012

Database backup fails with below error after piecemeal restore?

 

I do not have backup for some of file groups and I do not want the data in those file group.

How to drop offline file and file groups after piecemeal restore?  I don’t have the backup for some of my file groups.

 

I came across some of the customers with large databases schedule file group backup for some of file groups in database and ignore some file groups which do not have critical information. They do piece meal restore for file groups for which they have backup and bring the database online during disaster.

They end with below error message when they take backup of database which is piece meal restored. How do we Fix?

 

 

 

Error1

 

Msg 3007, Level 16, State 1, Line 1

The backup of the file or filegroup "Test2" is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.

Msg 3013, Level 16, State 1, Line 1

 

Error2

 

BACKUP DATABASE is terminating abnormally.

 

Backup failed for Server ‘Test\kj’.  (Microsoft.SqlServer.SmoExtended)

 

——————————

ADDITIONAL INFORMATION:

 

System.Data.SqlClient.SqlError: The backup of the file or filegroup "Test2" is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)

 

 

Error3

 

We also get below error when we try to drop the file which is no more required.

 

 

Msg 5056, Level 16, State 2, Line 1

Cannot add, remove, or modify a file in filegroup ‘Secondary’ because the filegroup is not online.

 

 

 

 

 

If the unrestored file group is no longer required they can be removed using below queries. Many people get stuck when they

get “Cannot add, remove, or modify a file in filegroup ‘ ’ because the filegroup is not online.“ while they remove file.

This error can be ignored and we can proceed to removing the file group as mentioned in below query.

 

 

 

ALTER DATABASE database_name REMOVE FILE file_name – If this query fails proceed to next file or Remove file group

 

ALTER DATABASE database_name REMOVE FILEGROUP filegroup_name

 

Once we issue the REMOVE FILEGROUP query to offline file group they will get in to defunct state and we can proceed taking the database backups like any other normal database.

 

Sample query to reproduce the above error is below

 

 

 

 

USE [master]

go

create database Test

 

GO

ALTER DATABASE [Test] ADD FILEGROUP [Secondary]

GO

 

ALTER DATABASE [Test] ADD FILE ( NAME = N’Test2′, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.KJ\MSSQL\DATA\Test2.ndf’ , SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Secondary]

GO

 

BACKUP DATABASE [Test] FILEGROUP = N’PRIMARY’ TO  DISK = N’D:\Backupstore\PrimaryFilegroupbackup’ WITH NOFORMAT, NOINIT,  NAME = N’Test-Full Filegroup Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

 

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N’Test’

GO

USE [master]

GO

ALTER DATABASE [Test] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

USE [master]

GO

DROP DATABASE [Test]

GO

 

RESTORE DATABASE [Test] FILEGROUP=‘Primary’ FROM  DISK = N’D:\Backupstore\PrimaryFilegroupbackup’  WITH PARTIAL, RECOVERY

Go 

 

–Now the database is online and full database backup would fail with error mentioned above. To resolve issues below queries.

 

 

ALTER DATABASE [Test] REMOVE FILE Test2

go

 

–{

–Error

–=====

–Msg 5056, Level 16, State 2, Line 1

–Cannot add, remove, or modify a file in filegroup ‘Secondary’ because the filegroup is not online.

–}

 

 

–Above error can be ignored and we can proceed to remove filegroup as shown below.

 

ALTER DATABASE [Test] REMOVE FILEGROUP Secondary

go

 

 

–Now we can take normal database backups 

 

 

 

Thank You and Best Regards,

Karthick P.K

 

Posted in Backup/Restore | Tagged: , , , | 11 Comments »

How to move the LOB data from one file group to other?

Posted by Karthick P.K on January 17, 2012

 

We do not have a direct way to move the LOB data from one file group to other. Using ALTER TABLE and/or CREATE INDEX to support moving LOB data is unavailable till current version of  SQL (SQL Server 2008).

 

Only way to move the LOB data is to

1. create new table in new file group

2. Move the data from existing table to new table.

3. Drop the existing table.

4. Change the name of new table to Old table.

Management studio has easy way to create script for all the above task.

1. In management studio Right click the table –>Design –>change the file group in properties windows (Click View—> properties window if you do not see properties window)

2. Generate Change Script.

clip_image002

3. Script similar to following script is generated.

4. Copy the script and run in Query window.

 

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

BEGIN TRANSACTION

SET QUOTED_IDENTIFIER ON

SET ARITHABORT ON

SET NUMERIC_ROUNDABORT OFF

SET CONCAT_NULL_YIELDS_NULL ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

COMMIT

BEGIN TRANSACTION

GO

CREATE TABLE dbo.Tmp_BLOB_TABLE

                (

                BLOBName varchar(100) NULL,

                BLOBData varbinary(MAX) NULL

                )  ON [PRIMARY]

                TEXTIMAGE_ON Lob2

GO

ALTER TABLE dbo.Tmp_BLOB_TABLE SET (LOCK_ESCALATION = TABLE)

GO

IF EXISTS(SELECT * FROM dbo.BLOB_TABLE)

                EXEC(‘INSERT INTO dbo.Tmp_BLOB_TABLE (BLOBName, BLOBData)

                                SELECT BLOBName, BLOBData FROM dbo.BLOB_TABLE WITH (HOLDLOCK TABLOCKX)’)

GO

DROP TABLE dbo.BLOB_TABLE

GO

EXECUTE sp_rename N’dbo.Tmp_BLOB_TABLE’, N’BLOB_TABLE’, ‘OBJECT’

GO

COMMIT

 

Thanks

Karthick P.K

Posted in Space management, SQL General, SQL Server Tools | Tagged: | 34 Comments »

Linked server connection fails with “An error occurred during decryption”

Posted by Karthick P.K on January 9, 2012

We might get Error: 15466, Severity: 16, State: 2  An error occurred during decryption while installing Projects servers (or) Sending mails using database mail (or) Linked server connections might fail with Msg 15593, Level 16, State 1, Line 1

Linked server connection fails with below error

{

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
An error occurred during decryption. (Microsoft SQL Server, Error: 15466)

Msg 15593, Level 16, State 1, Line 1

An error occurred while decrypting the password for linked login ‘distributor_admin’ that was encrypted by the old master key. The error was ignored because the FORCE option was specified.

}

Database mail might fail with below error

{

Set mail server login password failed for MailServer ‘Domain’.  (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

An error occurred during decryption. (Microsoft SQL Server, Error: 15466)

}

Or

You notice below errors in SQL Server errorlogs

spid10s Error: 15581, Severity: 16, State: 3.
Please create a master key in the database or open the master key in the session before performing this operation.

Cause

SQL Server service account was changed from services control manager (or) service master key was not backed up and restored when migrating SQL Server to another computer domain.

{

http://msdn.microsoft.com/en-us/library/ms187788.aspx

To change the SQL Server service account, use SQL Server Configuration Manager. To manage a change of the service account, SQL Server stores a redundant copy of the service master key protected by the machine account that has the necessary permissions granted to the SQL Server service group. If the computer is rebuilt, the same domain user that was previously used by the service account can recover the service master key. This does not work with local accounts or the Local System, Local Service, or Network Service accounts. When you are moving SQL Server to another computer, migrate the service master key by using backup and restore.

The REGENERATE phrase regenerates the service master key. When the service master key is regenerated, SQL Server decrypts all the keys that have been encrypted with it, and then encrypts them with the new service master key. This is a resource-intensive operation. You should schedule this operation during a period of low demand, unless the key has been compromised. If any one of the decryptions fail, the whole statement fails.

The FORCE option causes the key regeneration process to continue even if the process cannot retrieve the current master key, or cannot decrypt all the private keys that are encrypted with it. Use FORCE only if regeneration fails and you cannot restore the service master key by using the RESTORE SERVICE MASTER KEY statement.

}

 

Resolution

Regenerate the service master key using ALTER SERVICE MASTER KEY REGENERATE

If you receive the following error message when running ALTER SERVICE MASTER KEY REGENERATE.

{

The current master key cannot be decrypted. If this is a database master key, you should attempt to open it in the session before performing this operation. The FORCE option can be used to ignore this error and continue the operation but the data encrypted by the old master key will be lost.

}

We are left with only option to force regenerating service master key using “ALTER SERVICE MASTER KEY FORCE REGENERATE “.

Note:The service master key is the root of the SQL Server encryption hierarchy. The service master key directly or indirectly protects all other keys and secrets in the tree. If a dependent key cannot be decrypted during a forced regeneration, the data the key secures will be lost.

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Posted in Configuration, Connectivity, Security, SQL Server Tools | Tagged: , , , , , , , , , , | 9 Comments »

SQL Saturday #116 : Don’t miss it

Posted by Karthick P.K on January 5, 2012

SQL Server PASS and Microsoft is organizing first SQL Saturday event in Bangalore on January 7th 2012.

I will be part of SQL Server clinic which is between 2:30 PM to 5:30 PM. SQL Server clinic is event in which MSFT CSS engineers having deep

technical knowledge is available to give result set for any technical queries you fire. Be open to bring any SQL Server related problems you are

facing and get immediate assistance from SQL Server experts.

click here to get event location

 

Thanks

Karthick P.K

Posted in SQL Saturday | Tagged: | 2 Comments »