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 Daily Tips � Backup your database without affect the normal backup sequence. | Backup Server Tips

Sometimes is necessary take a full backup or log backup for a database without affect the backup normal sequence for the database and log as well. This backup can be with different purpose as testing, development, etc. For this cases SQL Server provides an option for the backup statement, this is COPY_ONLY.

BACKUP DATABASE db_name TO disk='C:\db_name.bkp' WITH COPY_ONLY

This option COPY_ONLY, can be performed for any backup type. COPY_ONLY has certains behaviors depending of the backup type:

* A log backup taken using copy_only, does not truncate the transaction log.

* A data backup taken using copy_only cannot be used as base backup for differential or incremental backups.

* A differential backup has not effect with copy_only option.

SQL SERVER � SQL Daily Tips � Monitoring progress for tasks. | Backup Server Tips

SQL Server 2005 introduces Dynamic Management View, one of this objects allows us monitoring the progress for different tasks and process in SQL Server.

select percent_complete, estimated_completion_time, cpu_time, total_elapsed_time, *

from sys.dm_exec_requests

this query is very helpful in the daily work to monitoring different tasks.

SQL SERVER � SQL Daily Tip � Force Database Offline. | Backup Server Tips

To force a database to bring OFFLINE you can use the next statement:

alter database DBNAME set offline with rollback immediate

this is a quickly way to force a database to set it OFFLINE, when you use this statement a rollback occurs for all the transactions running.

SQL SERVER � SQL TIPS � SELECT the ERROLOG. | Backup Server Tips

A useful tip for look into the ERRORLOG, is use a SELECT statement with ERROLOG as table.

First is necessary to create a table with the structure of the ERROLOG outcome.

CREATE TABLE TBLERRORLOG

(

LOGDATE DATETIME,

PROCESSINFO VARCHAR(30),

DETAIL VARCHAR(8000)

)

Then, full the table with the results of extended stored procedure xp_readerrolog:

INSERT INTO TBLERRORLOG exec master..xp_readerrorlog

Now is possible SELECT the table TBLERRORLOG with a certain filter:

SELECT * FROM TBLERRORLOG where DETAIL like '%master%'

If you have several error logs, you can use the following code:

truncate table TBLERRORLOG

Go

INSERT INTO TBLERRORLOG exec sp_readerrorlog 2

go

SELECT * FROM TBLERRORLOG where DETAIL like '%master%'

First, the table TBLERRORLOG is truncated, then the table is filled using the undocumented stored procedure sp_readerrolog, there is possible to define the ERROLOG number, in this case I use 2, then a SELECT statement is used. This code can be used several times depending of the ERROLOG allowing us a quickly way to find some issue.

SQL SERVER � TIPS � Reclaim space for a table after drop column. | Backup Server Tips

In a table, after a column is dropped using alter table drop column statement, is necessary to reclaim the space, just when a fixed-length column or text column were dropped.

To see the behavior you can create 3 tables with 3 fields, this 3 fields must to be different datatypes

-- create a db test create database db_test go use db_test go -- create 3 table with the same fields create table test1 ( a char(20), --fixed-lenght column b varchar(20), --variable-lenght column c text --text column ) go create table test2 ( a char(20), --fixed-lenght column b varchar(20), --variable-lenght column c text --text column ) go create table test3 ( a char(20), --fixed-lenght column b varchar(20), --variable-lenght column c text --text column ) Go

Now, you can add some information and see the space of each table.

-- insert some information into the 3 tables. insert into test1 values ('aaaaaaaaaa','bbbbbbbbb','text text text text text text text text text text text text text ') insert into test2 values ('aaaaaaaaaa','bbbbbbbbb','text text text text text text text text text text text text text ') insert into test3 values ('aaaaaaaaaa','bbbbbbbbb','text text text text text text text text text text text text text ') --checking the space sp_spaceused test1 Go sp_spaceused test2 Go sp_spaceused test3 GO /* --Result test1 1 32 KB 24 KB 8 KB 0 KB test2 1 32 KB 24 KB 8 KB 0 KB test3 1 32 KB 24 KB 8 KB 0 KB */

Then, drop a column for each table, after drop a column the space for each table is the same than before. To reclaim the space is necessary to execute the dbcc cleantable on each table. This dbcc utility must to be executed when a variable-length column or text column were dropped.

-- drop one column in each table in order to -- see the behavior after drop a column alter table test1 drop column a alter table test2 drop column b alter table test3 drop column c --reclaim space dbcc cleantable ('db_test','test1') dbcc cleantable ('db_test','test2') dbcc cleantable ('db_test','test3')

MSDN says �DBCC CLEANTABLE reclaims space after a variable-length column or a text column is dropped by using the ALTER TABLE DROP COLUMN statement. The command does not reclaim space after a fixed-length column is dropped.�

After execute DBCC CLEANTABLE, you can see that some space was reclaimed for the table test3 where the TEXT column was dropped, for the other two tables the space is the same, even for the table 2 where a variable-length column was dropped, in this case some space must to be reclaimed, perhaps does not happening anything because the amount of data is not relevant.

sp_spaceused test1 Go sp_spaceused test2 Go sp_spaceused test3 GO /* --Result test1 1 32 KB 24 KB 8 KB 0 KB test2 1 32 KB 24 KB 8 KB 0 KB test3 1 24 KB 16 KB 8 KB 0 KB */