SQL SERVER � SQL Error � MsiGetProductInfo failed to retrieve ProductVersion for package with Product Code = '{8BDD006D-7863-4D25-9806-08527E285EA4}'. | Backup Server Tips

Problem.

Overall summary:

Final result: Failed: see details below

Exit code (Decimal): 2064843076

Exit facility code: 787

Exit error code: 324

Exit message: MsiGetProductInfo failed to retrieve ProductVersion for package with Product Code = '{8BDD006D-7863-4D25-9806-08527E285EA4}'. Error code: 1605.

Start time: 2009-07-28 18:30:43

End time: 2009-07-28 18:31:14

Requested action: ComponentUpdate

Log with failure: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20090728_182952\Detail_ComponentUpdate.txt

Exception help link: http%3a%2f%2fgo.microsoft.com%2ffwlink%3fLinkId%3d20476%26ProdName%3dMicrosoft%2bSQL%2bServer%26EvtSrc%3dsetup.rll%26EvtID%3d50000%26ProdVer%3d10.0.1600.22%26EvtType%3d0x4E099C65%400x7B130144

The error happened during the installation of a SQL Server 2008.

Solution / Fix / Workaround

The above remark line show us the product code related with the error. First, is necessary to know that SQL Server 2008 has around of 30 products registered on the Registry. Each product has a Product code and an installation code as well (GUID).

For this case the Product Code is the following:

{8BDD006D-7863-4D25-9806-08527E285EA4}

The Installation Code is build with the product code in reverse form:

D600DD8368752D460894AE582E72580

One time that you got the Installation Code look into the Windows Registry and delete all the entries found.

Then try again the installation and the result must to be SUCCESSFULLY.

SQL SERVER � SQL Error � Msg 7399, Level 16, State 1, Line 1. The OLE DB provider "SQLNCLI" for linked server "(null)" reported an error. Authenticati | Backup Server Tips

Problem.

OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Invalid authorization specification".

OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Invalid connection string attribute".

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "SQLNCLI" for linked server "(null)" reported an error. Authentication failed.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "(null)".

The error happened when the OPENROWSET statement was used.

The syntax used for the error is the following.

select * from openrowset('SQLOLEDB.1','Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=db_test2;Data Source=.\bkp','select * from dbo.table2')

Solution / Fix / Workaround

The right syntax for my query was the next one:

select * from

openrowset('SQLNCLI','Server=.\bkp;Uid=test_user;Pwd=test;Database=db_test2;','select * from dbo.table2')

SQL SERVER � SQL Real Cases � Issue with auto_close option (Root cause of SUSPECT database). | Backup Server Tips

Days ago I was working to find out the root cause of a SUSPECT state in a productive database. When I started to work in the database, it was already online. Somebody restart the MSSQL service and it bring online the database, but nobody knows what was the cause of the issue, it task is my job. :-)

Please be aware that the database name, data file or log file names are not the real values.

1. First I saw that the size of Transaction Log was several times the size of the entire db. It is bad, but this is not the cause of the suspect database.

2. I started to checking the ERRORLOG and I saw the system was configured to archive almost 60 ERROLOG. It is strange.

3. I started to read all the ERROLOG files, one by one. How to check it?, please check SQL SERVER � SQL TIPS � SELECT the ERROLOG. or SQL SERVER � Working with ERRORLOG.

4. When I was checking the ERROLOG.10, I saw the following error:

2009-07-03 22:40:02.78 spid51 udopen: Operating system error 32(The process cannot access the file because it is being used by another process.) during the creation/opening of physical device c:\Productive_DB_LOG_FILE.ldf. 0

2009-07-03 22:40:02.78 spid51 FCB::Open failed: Could not open device c:\ Productive_DB_LOG_FILE.ldf for virtual device number (VDN) 2.

In the first error I can see that the log file can not be blocked by SQL Server. We knows that SQL Server block the data files and log files when it try to start the database. The issue here is, the database was not offline at any moment. The system was working during long period of time and I did not see any message where the database was offline. So, if the database was ONLINE, what was the cause of the log file was not blocked by SQL Server?

At this moment I knew that the cause of the database was in SUSPECT state, was because a log file for the database was blocked by other process and can not be blocked by SQL Server when it try start the database.

5. When I was checking the ERRORLOG files, I saw several messages (not errors) as the following.

2009-07-03 22:33:08.85 spid51 Analysis of database 'PRODUCTIVE_DB' (9) is 100% complete (approximately 0 more seconds)

2009-07-03 22:40:02.74 spid51 Starting up database 'PRODUCTIVE_DB'.

Mmmmmm Starting up database several times?,

What is the cause that the database is starting in several times? I thought in the option AUTO_CLOSE at db level. SQL Server has an option that close the database automatically when the last user close his connection to database. It option is AUTO_CLOSE.

6. Then I checked if the AUTO_CLOSE is enabled with the next statement:

SELECT DATABASEPROPERTYEX('PRODUCTIVE_DB','ISAUTOCLOSE')

Result.

1

If IsAutoClose is 1, the option is enabled.

As per SQL Server BOL.

IsAutoClose: Database shuts down cleanly and frees resources after the last user exits.

7. Conclusion.

At this moment I know that AUTO_CLOSE option is enable. So, if this option is enable, it cause that SQL Server frees the resources of the database after the last user finish his connection and when a new user is connected to the database, it is started and it generate a message of �Starting Database� in the ERROLOG files. Hence, I am ready to conclude: AUTO_CLOSE option is enabled, this option caused that all resources are frees and caused that the data files and log files of the database are not blocked anymore, then this is cause that another process was able to block the log file and it happened, finally, when SQL Server try to start the database and block the log file again, it was not possible and SQL Server marks in SUSPECT state the PRODUCTIVE_DB.

SQL SERVER � Working with LSN for Restore and Backup Databases. (Error: Msg 4305, Level 16, State 1, Line 1) | Backup Server Tips

Today in my job, some colleague asks me about the next error:

Msg 4305, Level 16, State 1, Line 1

The log in this backup set terminates at LSN 9386000024284900001, which is too early to apply to the database. A more recent log backup that includes LSN 9417000002731000001 can be restored.

So, my explanation was:

You are trying to apply a Log file that finish with LSN 9386000024284900001 into a DB restored with LSN 9417000002731000001, so, the LSN of the Full Backup (restored) is older than the log file that is trying to apply. So, the log file is not possible to apply it.

Now, we are going to replicate that error and we are going to see how to know the LSN of each backup, in order to clarify and understand the error mentioned above.

First a DB will be created, some backups will be taken, then the DB will be restored and some log will be applied in the wrong way, then we are be able to identify the right way and the we will apply the logs.

First, we create a database and a table for testing purpose.

--Create DB for Testing

create database DB_TestLSN

GO

USE DB_TestLSN

GO

--create a testing table.

create table test(

a int

)

Now, we are going to backup the database and log:

backup database DB_TestLSN

to disk='c:\DB_TestLSN.bak'

A table2 and table3 are generated and two log backups are taken:

create table test2(

a int

)

-- backup log file

backup log DB_TestLSN

to disk='c:\DB_TestLSN_LOG1'

create table test3(

a int

)

-- backup log file

backup log DB_TestLSN

to disk='c:\DB_TestLSN_LOG2'

Restoring database�.

First restore the full backup

restore database DB_TestLSN_REST

from disk='c:\DB_TestLSN.bak'

with norecovery

then we restore intentionally the wrong log file

restore log DB_TestLSN_REST

from disk='c:\DB_TestLSN_LOG2'

Msg 4305, Level 16, State 1, Line 1

The log in this backup set begins at LSN 18000000015300001, which is too recent to apply to the database. An earlier log backup that includes LSN 18000000014000001 can be restored.

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

� is necessary to identify what is the correct way to restore the logfiles. For this, we can mention two manners:

1. If we have access to the server where the backup was taken.

We can check the system table msdb..backupset. To identify the chronologic order of the data backup and log backup, we can put special attention on the following columns

• backup_start_date, backup_finish_date, first_lsn, last_lsn

With that columns we can view the LSN of each of the backups taken, so our error shows that 18000000015300001 (this is the first lsn for the second log backup) is too early to apply and and earlier log backup that includes LSN 18000000014000001 must be exists, and that is correct, the first log backup includes the LSN 18000000014000001 and also this logs begins with the log_chain.

select database_name, type, first_lsn, last_lsn ,checkpoint_lsn ,database_backup_lsn

from msdb..backupset where database_name = 'DB_TestLSN'

database_name type first_lsn last_lsn begins_log_chain

DB_TestLSN D 18000000007400155 18000000014000001 0

DB_TestLSN L 18000000007400155 18000000015300001 1

DB_TestLSN L 18000000015300001 18000000015900001 0

2. If we have just the backup files.

To know the LSN of the backups we can use

RESTORE HEADERONLY from disk='c:\DB_TestLSN.bak'

RESTORE HEADERONLY from disk='c:\DB_TestLSN_LOG1'

RESTORE HEADERONLY from disk='c:\DB_TestLSN_LOG2'

In this case also is necessary to put special attention on the columns first_lsn, last_lsn in each of the backup files.

To reproduce exactly the error mentioned on the top of this article,

Msg 4326, Level 16, State 1, Line 2

The log in this backup set terminates at LSN 18000000015400001, which is too early to apply to the database. A more recent log backup that includes LSN 18000000022000001 can be restored.

Msg 3013, Level 16, State 1, Line 2

RESTORE LOG is terminating abnormally.

follow up the next steps.

1. Create DB
2. Backup Full Database (BkpDBFULL1)
3. Backup Log (BkpLOG1)
4. Backup Full Database (BkpDBFULL2)
5. Restore BkpDBFULL2
6. Try to apply BkpLOG1

Instead of use backup log, you can use backup incremental and the behavior is the same than we show in this examples.

[Questions & Answers]

Q: What is a LSN (Log Secuence Number): ?

A:

Every record in the Microsoft SQL Server transaction log is uniquely identified by a log sequence number (LSN). LSNs are ordered such that if LSN2 is greater than LSN1, the change described by the log record referred to by LSN2 occurred after the change described by the log record LSN.

The LSN of a log record at which a significant event occurred can be useful for constructing correct restore sequences. Because LSNs are ordered, they can be compared for equality and inequality (that is, <, >, =, <=, >=). Such comparisons are useful when constructing restore sequences.

LSNs are used internally during a RESTORE sequence to track the point in time to which data has been restored. When a backup is restored, the data is restored to the LSN corresponding to the point in time at which the backup was taken. Differential and log backups advance the restored database to a later time, which corresponds to a higher LSN.

SQL SERVER � Error: Property Owner is not available for Database '[DB_NAME]'. This property may not exist for this object, or may not be retrievable | Backup Server Tips

Problem.

Error: Property Owner is not available for Database '[DB_NAME]'.

This property may not exist for this object, or may not be retrievable

due to insufficient access rights.  (Microsoft.SqlServer.Smo)

This a common error that happens when we try to access the database options and the database doesn�t have owner. The database can not be an owner because somebody clean the owner property, or the user (owner) was deleted, etc.

Solution / Fix / Workaround.

Is necessary to assign a proper owner for the database. To know what are the valid owners for the database, we can execute the following query:

select databases.name,server_principals.name

from sys.[databases]

inner join sys.[server_principals]

on [databases].owner_sid = [server_principals].sid

To assign the owner for the database:

USE <SID>

Go

sp_changedbowner ''

Go

SQL SERVER � Error: The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is runn | Backup Server Tips

Problem.

Sometimes when we are trying to truncate the transaction log, we can got the following error message:

Statement:

BACKUP LOG <SID> WITH TRUNCATE ONLY

Error:

The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed.

This is because some part of the transaction log is pending to replicate (publisher side), hence is not possible to truncate it from the transaction log. In this case is necessary to mark these transactions as replicated in order to be able to truncate them.

Solution / Fix / Workaround.

To mark the transactions as distributed (or replicated) we can use the following statemtent:

USE <SID>

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

Use sp_repldone just for troubleshooting purpose and in emergency situations. If this SP is not used in the proper way you can invalidate pending information to be replicated.

Sometimes when sp_repldone is used, is possible that we get the following error.

Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1 The database is not published

It means that our Database is not published. A workaround to this error, is to publish our database with the below statement, then try again the sp_repldone and finally execute again the sp_dboption with publish = false.

sp_dboption '', 'Publish', 'true'

Ex.

sp_dboption '', 'Publish', 'true'

EXEC sp_repldone ....

sp_dboption '', 'Publish', 'false'