Wednesday, July 16, 2014

How to manually uninstall SQL if uninstalling from Add/Remove Programs fails

msiinv.exe is the most useful tool I came across.

One of the common uses of msiinv.exe is if someone is trying to install one of the recent beta builds of VS 2005 or .NET Framework 2.0 and the setup UI states that you are not allowed to install because a previous beta version of is on the machine and you must uninstall that first.  Sometimes after receiving this error, a user will look in Add/Remove Programs and the product that setup is complaining about is nowhere to be found, or there is an Add/Remove Programs entry for that product but trying to remove it claims that the product is not on the computer and asks if you would like to remove the entry from the Add/Remove Programs list.

these cases, you can use the following steps:

Download msiinv.zip from the following location:

 http://blogs.msdn.com/b/astebner/archive/2005/07/01/434814.aspx

Extract the contents of msiinv.zip to the folder c:\msiinv on your system
Click on the Start menu, choose Run, type cmd and click OK
Type this command:  c:\msiinv\msiinv.exe -p > c:\msiinv\msiinv_output.txt 

Note: This command must be run from a cmd prompt or it will not create a log file as expected.
These steps will create a text file named c:\msiinv\msiinv_output.txt with a list of each product that Windows Installer thinks is installed on the system.  Then you can open the text file in any text editor and search the list of products for the name of the product that setup told you to uninstall.  The output will look something like this (I am using an example from a machine that has .NET Framework 2.0 beta 2 installed):

Microsoft .NET Framework 2.0 Beta 2
 Product code: {7A1ADD0C-17F3-47B8-B033-A06E189C835D}
 Product state: (5) Installed.
 Package code: {856D48D2-6F94-466D-9732-534DB5854FB3}
 Version: 2.0.50215

Now we have the Windows Installer product code and we can use that to uninstall the product by running msiexec /x (make sure that you include the curly braces in this command line).  If the product is actually installed on your system you will see a progress screen and uninstall will complete.

Thursday, March 27, 2014

Rebuild Index

-- Ensure a USE statement has been executed first.

GO

SET NOCOUNT ON;

-- define index defrag rate for this run
DECLARE @fragrate float;
SET @fragrate = 30.0; -- set to do something if defrag rate HIGH

DECLARE @rbrirate float;
SET @rbrirate = 50.0; -- set to rebuild if defrag rate EXTREME

-- define table holding index information
DECLARE @indexinfo TABLE(objectid int, indexid int, partitionnum bigint, frag float)

DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
INSERT INTO @indexinfo
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > @fragrate AND index_id > 0
--and object_id = object_id('mlsdata');

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM @indexinfo;
OPEN partitions;

-- Loop through the partitions.
FETCH NEXT FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;

WHILE (@@fetch_status <> -1)
    BEGIN;
        IF (@@fetch_status <> -2)
BEGIN

SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;

SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE  object_id = @objectid AND index_id = @indexid;

SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < @rbrirate
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= @rbrirate
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

EXEC (@command);
PRINT N'Executed (Defrag ' + CAST(@frag AS varchar(12)) + '): ' + @command;
--PRINT @command;
END;
-- Get the next record to process
        FETCH NEXT FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
    END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;