Shrink your Quest Performance v6.x database

Did you ever wanted to shrink your Quest Performance v6.x Database from a SQL 2000? Uninstalling the agent don't always get rid you of the data for a specified instance, so here is a script that do this...

-- v1.3

USE master
DECLARE @vINSTANCE varchar(255), @vDATABASE sysname
DECLARE @vKILL_TIME int,  @vKILLCONNECTIONS int, @vSINGLEMODE int,  @vREINDEX int, @vUPDATESTATISTICS int,  @vSHRINK int
DECLARE @vPROC varchar(255), @var4 varchar(255), @var5 int, @var6 int, @st datetime, @sti int, @var7 varchar(255), @name sysname, @txt_db NVARCHAR(255)

-- Compulsory variables
SET @vINSTANCE='SERVER\INSTANCE'
SET @vDATABASE='Quest_Perf_Analysis'

-- Options variables
SET @vKILL_TIME=0
SET @vKILLCONNECTIONS=1
SET @vSINGLEMODE=1
SET @vREINDEX=1
SET @vUPDATESTATISTICS=1
SET @vSHRINK=1

-- Initialize variables
set @vPROC=0
set @var5=0
set @var6=0

if @vSINGLEMODE=1
BEGIN
print '.'
print 'Set multiuser database ['+@vDATABASE+']...'
EXEC sp_dboption @vDATABASE, 'single user', 'false'
END -- @vSINGLEMODE

if @vKILLCONNECTIONS=1
BEGIN
print '.'
print 'Kill processes for database ['+@vDATABASE+']...'
WHILE EXISTS (select * FROM master.dbo.sysprocesses WHERE db_name(dbid)=@vDATABASE and login_time
BEGIN
    SELECT TOP 1 @vPROC =spid from  master.dbo.sysprocesses where db_name(dbid)=@vDATABASE and login_time
    EXEC ('kill '+ @vPROC)
     PRINT ('Kiled... ' + @vPROC)
    WAITFOR DELAY '000:00:01'
END
-- Show not sleeping!
select db_name(dbid),* from  master.dbo.sysprocesses where db_name(dbid)=@vDATABASE order by login_time
END -- @vKILLCONNECTIONS

if @vSINGLEMODE=1
BEGIN
print '.'
print 'Set singleuser database ['+@vDATABASE+']...'
EXEC sp_dboption @vDATABASE, 'single user', 'true'
END -- @vSINGLEMODE

-- BEGIN PURGE
select @txt_db = 'declare c1 cursor for (select name from '+@vDATABASE+'.dbo.sysobjects where xtype=''U'' and name<>''QUEST_INSTANCE_DIM'')'
exec(@txt_db)

IF EXISTS (SELECT name FROM [tempdb].dbo.sysobjects
WHERE name = '##_ExecOutput' AND type = 'U')
DROP TABLE ##_ExecOutput

select @txt_db = 'select INSTANCE_KEY into ##_ExecOutput from '+@vDATABASE+'.dbo.QUEST_INSTANCE_DIM where '''+@vINSTANCE+'''=HOST'
exec(@txt_db)
select @var4 = INSTANCE_KEY from ##_ExecOutput -- INSTANCE_KEY from ##_ExecOutput
DROP TABLE ##_ExecOutput

    print '.'
    print '. Instance no. for '+@vINSTANCE+' is '+@var4
    print '.'

open c1
fetch next from c1 into @name
WHILE @@FETCH_STATUS = 0
BEGIN
        set @var5=@var5+1
    fetch next from c1 into @name
END
close c1

open c1
fetch next from c1 into @name
WHILE @@FETCH_STATUS = 0
BEGIN

        set @st = getdate()

        set @var6=@var6+1
    print '.   '+CAST(@var6 AS char(4))+' /  '+CAST(@var5 AS char(4))
    print 'Deleting instance references from... "'+@vDATABASE+'.dbo.'+@name+'"'
        SELECT @txt_db='DELETE '+@vDATABASE+'.dbo.'+@name+' WHERE '+@var4+'=INSTANCE_KEY'
        print 'Executing: '+@txt_db
        EXEC(@txt_db)

        if @vREINDEX=1
        BEGIN         
    print 'Reindexing... "'+@vDATABASE+'.dbo.'+@name+'"'
               SELECT @txt_db='DBCC DBREINDEX ('''+@vDATABASE+'.dbo.'+@name+''')'
               print '[SQL]: '+@txt_db
               EXEC(@txt_db)
               END -- @vUPDATESTATISTICS

        if @vUPDATESTATISTICS=1
               BEGIN         
    print 'Update statistics... "'+@vDATABASE+'.dbo.'+@name+'"'
    SELECT @txt_db='UPDATE STATISTICS '+@vDATABASE+'.dbo.'+@name+' with FULLSCAN, ALL'
    print '[SQL]: '+@txt_db
                EXEC(@txt_db)
    END -- @vUPDATESTATISTICS

        set @sti=cast(datediff(ss,@st,getdate()) as int)
        print 'Batch completed in: ' + rtrim(CONVERT(varchar(6), @sti/3600)+ 'h:' + RIGHT('0' + CONVERT(varchar(2), (@sti % 3600) / 60), 2)+ 'm:' + RIGHT('0' + CONVERT(varchar(2), @sti % 60)+'s', 2)) + ' !'
    print '.'

    fetch next from c1 into @name
END
close c1
deallocate c1
-- END PURGE


if @vSHRINK=1
BEGIN
  print '.'
  print 'Shrinking database ['+@vDATABASE+']...'
  backup log @vDATABASE with no_log
  DBCC SHRINKDATABASE (@vDATABASE, 0)
END -- @vSHRINK

if @vSINGLEMODE=1
BEGIN
print '.'
print 'Set multiuser database ['+@vDATABASE+']...'
EXEC sp_dboption @vDATABASE, 'single user', 'false'
END -- @vSINGLEMODE

print '*** If all ok delete manually from QUEST_INSTANCE_DIM the row with HOST='+@vINSTANCE+' ! ***'

-- Observations
/* ALTER TABLE [dbo].[QUEST_TIME_DIM] ADD
    CONSTRAINT [QUEST_TIME_DIM_FK1] FOREIGN KEY
    (
        [PYRAMID_LEVEL]
    ) REFERENCES [dbo].[QUEST_CTRL_PYRAMID_LEVELS] (
        [LEVEL_ID]
    ) ON DELETE CASCADE
GO */