/*
** ksp_ShrinkDB
** Created on Dec. 22, 1999 by Kevin Eberwein [kevine@ms.ncga.state.nc.us]
** Algorithm posted to the SQL Server 7 list by John Sances [John.Sances@experian.com]
** This procedure will shrink a database that does not respond to just the DBCC SHRINKDB command.
*/
Drop procedure ksp_ShrinkDB
go

Create Procedure ksp_ShrinkDB (@dbname varchar(100)) as

/* Trim the spaces from the database name. */
select @dbname=rtrim(ltrim(@dbname))

/*Backup the log file of the database. */
exec ('Backup Log '+@dbname+' with Truncate_Only')

/* 
** Create a small table in the database, insert a row, then update the row several times 
**  to move the active VLF to an earlier physical location. 
*/
exec ('create table '+@dbname+'..Temp1 (Col1 int)')
exec ('insert into '+@dbname+'..Temp1 values (0)')
exec ('update '+@dbname+'..Temp1 set Col1 = 1')
exec ('update '+@dbname+'..Temp1 set Col1 = 2')
exec ('update '+@dbname+'..Temp1 set Col1 = 3')
exec ('update '+@dbname+'..Temp1 set Col1 = 4')
exec ('update '+@dbname+'..Temp1 set Col1 = 5')
exec ('update '+@dbname+'..Temp1 set Col1 = 6')
exec ('update '+@dbname+'..Temp1 set Col1 = 7')
exec ('update '+@dbname+'..Temp1 set Col1 = 8')
exec ('update '+@dbname+'..Temp1 set Col1 = 9')
exec ('update '+@dbname+'..Temp1 set Col1 = 10')
exec ('update '+@dbname+'..Temp1 set Col1 = 11')
exec ('update '+@dbname+'..Temp1 set Col1 = 12')
exec ('update '+@dbname+'..Temp1 set Col1 = 13')
exec ('update '+@dbname+'..Temp1 set Col1 = 14')
exec ('update '+@dbname+'..Temp1 set Col1 = 15')
exec ('update '+@dbname+'..Temp1 set Col1 = 16')
exec ('update '+@dbname+'..Temp1 set Col1 = 17')
exec ('update '+@dbname+'..Temp1 set Col1 = 18')
exec ('update '+@dbname+'..Temp1 set Col1 = 19')

/* Execute the ShrinkDB command to mark the shrinkpoint. */
DBCC SHRINKDATABASE (@dbname,   NOTRUNCATE )

/* Drop the created table to be clean. */
exec ('drop table '+@dbname+'..Temp1')

/* Execute a log dump to force the shrink. */
exec ('BACKUP LOG '+@dbname+' WITH TRUNCATE_ONLY')

/* Now do a full database dump to be a good DBA. */
exec ('Backup Database '+@dbname+' to DISK = ''E:\SQL_Backups\'+@dbname+'.dat'' with INIT, Name='''+@dbname+'Shrinked'' ')
go