--  ========================================================================================
--	PROCEDURE:	sp_ShrinkDB 
--	AUTHOR:	Richard L. Dawson
--	COMPANY:	New Century Technology, Inc.
--	DATE:		8/23/2001
--	FOR:		SC Dept of Education Data Warehouse Implementation
--
--	Accept the name of a database and a path for the temporary backup device. ALTER  the backup
--	device use it during the shrinking of the database and then delete it.
--	USED BY:	
--	USEAGE:	Use EDW_Staging
--			Go
--
--			sp_shrinkDB 'dbName', 'tempBackupPath'
--
--  ========================================================================================
Create Procedure sp_shrinkDB (	@dbName varchar (100)
				,	@devPath varchar (100)
				)
As
Begin
Declare	@devName varchar (28)
	,	@intValue tinyint

--	Trim any spaces from the database name and path.
	Select	@dbname = LTrim ( RTrim ( @dbname ) ), @devPath = LTrim ( RTrim ( @devPath ) ),
		@devName = 'tmp_' + @dbName + '_Device'

--	Drop a trailing backslash if it exists
	If Right ( @devPath, 1 ) = '\'
	Begin
		Declare @strLen tinyint
		Select @strLen = Len ( @devPath )
		Select @devPath = SubString ( @devPath, 1, @strLen - 1 )
	End

	Select @devPath = @devPath  + '\tmp_' + @dbName + '_Device.bak'
	
	If Not Exists
	(	Select *
		From master..sysdevices
		Where name = @devName   -- and sysstat & 0xf = 3
	)
	Begin
		Print 'Backup Device ''' + @devName + ''' does not exist.'
		Exec master..sp_addumpdevice 'disk', @devName, @devPath
		Print 'Backup Device ''' + @devName + ''' created.'
	End

	If Not Exists
	(	Select *
		From master..sysdevices
		Where name = @devName
	)
	Begin
		Print 'Backup Device ''' + @devName + ''' does not exist. Exiting procedure.'
		Return
	End

--	Do a full database backup.
	Backup Database @dbname to @devName

--	Backup the log file of the database.
	Backup Log @dbname with Truncate_Only

--	Check for existance of the temp table.
--	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.
	If Not Exists
	(	Select *
		From sysobjects
		Where id = object_id('Temp1') and sysstat & 0xf = 3
		)
	Begin
		Print 'The table ''Temp1'' does not exist.'
		Exec ('Create Table ' + @dbname + '..[Temp1] (Col1 int)')
		Print 'Table ''Temp1'' created.'
	End

	Select	@intValue = 0
	While	@intValue < 21
	Begin
		If Not Exists
		(	Select *
			From sysobjects
			Where id = object_id('Temp1') and sysstat & 0xf = 3
		)
		Begin
			Print 'The table ''Temp1'' does not exist. Exiting procedure.'
			Return
		End

		Exec ('Insert Into ' + @dbname + '..[Temp1] values (' + @intValue  + ')')
		Select @intValue = @intValue + 1
	End

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

--	Drop the created table to be clean.
	Exec ('Drop Table ' + @dbname + '..[Temp1]')

	If Not Exists
	(	Select *
		From sysobjects
		Where id = object_id('Temp1') and sysstat & 0xf = 3
	)
		Print 'Table ''Temp1'' has been dropped.'

--	Execute a log dump to force the shrink.
	Backup LOG @dbname WITH TRUNCATE_ONLY

--	Now do a full database dump.
	If Not Exists
	(	Select *
		From master..sysdevices
		Where name = @devName-- and sysstat & 0xf = 3
	)
	Begin
		Print 'Backup Device ''' + @devName + ''' does not exist. Exiting procedure.'
		Return
	End
	Else
	Backup Database @dbname to @devName

--	Remove the temporary backup device
	Exec master..sp_dropdevice @devName, 'delfile'

	If Not Exists
	(	Select *
		From master..sysdevices
		Where name = @devName
	)
		Print 'Backup Device ''' + @devName + ''' has been dropped.'
End
GO