Resize data file manually to prevent downtime

create Procedure dbo.up_DataFileResize(@RetNm Int =null output,
@Mode Varchar(8) =null,  --'INFO' or 'RESIZE' or 'HELP'

@DatabaseNameTxt Varchar(100)=null,
@FileNameTxt Varchar(100)=null)
-- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Name : dbo.up_DataFileResize
-- Purpose :  Resize Database file by 10% if free space is less than 10%
--Future modifications: Allow percentage to be passed in as parameter
-----------------------------------------------------------------------------------------------------------------------------------------------------------
--Implemetation:
--@Mode can be info, resize, or help
--@Mode=Info will expect parameter @DatabaseName.If not provided Proc will error out and Help will print out
--This will return File info for the selected database
--@Mode=Resize will expect @DatabaseNameTxt and @FileNameTxt. If not provided,Proc will errorout and Help will print out
--This will rezize the passed in database file of the passed in database by 10% if <10% free
--@Mode=Help Will not expect  @DatabaseNameTxt and @FileNameTxt
--This will return help info for the stored proc. Help will print out withour error
--If no Paramaters are provided, Proc will error out and Help will be displayed.
--Error on @Mode=null
--Error on @Mode='RESIZE' and @DatabaseNameTxt=null and @FileNameTxt=null

-- Change Management
-- NameDateChange
--------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Alex Campomanes2003-03-20Initial Creation
-- -------
As
declare @SpaceUsed int --Amount of space used in file
declare @TenPercent int--Ten Percent of amount used
declare @NewSize int--New PhysicalFile size =SpaceUsed+SpaceUsed*.1
Declare @Size int--Physical File size
Declare @PercentFree int
Declare @ErrorTxt varchar(500)
Declare @ProcNam Varchar(30)
Declare @SQLAlterDatabaseTxt Varchar(2000) --Dynamic Alter statement
Declare @SQLDatabaseInfoTxt Varchar(2000) --Dynamic DBInfo statement
Declare @SQLGetSizesTxt nVarchar(2000) --Dynamic Select statement




Set @RetNm=-1
Set nocount on
Set @ProcNam='up_DataFileResize'
Set @Mode=Isnull(@Mode,'')
Set @DatabaseNameTxt=Isnull(@DatabaseNameTxt,'')
Set @FileNameTxt=Isnull(@FileNameTxt,'')
--If no mode then error out and print help
If (@Mode ='') Or @Mode not in ('INFO','HELP','RESIZE') Begin
SELECT @ErrorTxt = 'Required Parameter @Mode not passed in correctly.'
    raiserror(55001,15,1,@ProcNam,'PARMVLD',@ErrorTxt)
SET @RetNm = -1
Goto Help
End
If (@Mode ='RESIZE') and ( @DatabaseNameTxt ='' or @FileNameTxt ='') Begin
SELECT @ErrorTxt = 'Required paramenters @DatabaseNameTxt/@FileNameTxt not passed in correctly for RESIZE mode.'
    raiserror(55001,15,1,@ProcNam,'PARMVLD',@ErrorTxt)
SET @RetNm = -1
Goto Help
End
If (@Mode ='INFO') and ( @DatabaseNameTxt ='') Begin
SELECT @ErrorTxt = 'Required paramenters @DatabaseNameTxt not passed in correctly for INFO mode.'
    raiserror(55001,15,1,@ProcNam,'PARMVLD',@ErrorTxt)
SET @RetNm = -1
Goto Help
End

--WIS3PRD01_Data
--Declare @FileName Varchar(150)   --Name of Datafile to be sized
--Declare @DatabaseName Varchar(150)   --Name of database


--At this point if still executing on a resize,DatabaseNameTxt and FIleNameTxt will not be null,
--Set Database Name for INFO (Allows this to be null)

If @Mode='HELP' Begin
GOTO Help
End

exec ('dbcc updateUsage ('''+@DatabaseNameTxt+''')')


If @Mode='INFO' Begin

Set @SQLDatabaseInfoTxt='select convert(varchar(40),a.Name) as ''Logical Data File Name'' ,'+ 
'convert(varchar(40),a.FileName) as ''Physical Data File Name'','+
'convert(int,c.Size*8/1024) as  ''File Size'','+
'convert(int,FILEPROPERTY(a.Name, ''SpaceUsed'')*8.000/1024.000) as ''Space Used'' from master.dbo.sysaltfiles a'+
' Join master.dbo.sysdatabases b'+
' on a.dbid=b.dbid'+
' Join '+@DatabaseNameTxt+'.dbo.SysFiles c'+
' on a.Name=c.Name'+
' where b.Name = '''+@DatabaseNameTxt+''' and a.Status=32770'
--select @SQLDatabaseInfoTxt
exec (@SQLDatabaseInfoTxt)
--Log file status=32834
Set @SQLDatabaseInfoTxt='select convert(varchar(40),a.Name) as ''Logical Data File Name'' ,'+ 
'convert(varchar(40),a.FileName) as ''Physical Data File Name'','+
'convert(int,c.Size*8/1024) as  ''File Size'','+
'convert(int,FILEPROPERTY(a.Name, ''SpaceUsed'')*8.000/1024.000) as ''Space Used'' from master.dbo.sysaltfiles a'+
' Join master.dbo.sysdatabases b'+
' on a.dbid=b.dbid'+
' Join '+@DatabaseNameTxt+'.dbo.SysFiles c'+
' on a.Name=c.Name'+
' where b.Name = '''+@DatabaseNameTxt+''' and  a.Status=32834'
exec (@SQLDatabaseInfoTxt)
Set @RetNm=0
Return
End

If @Mode='RESIZE' Begin

--Physical file size coverted to MB
set @SQLGetSizesTxt='SELECT @Size=Size*8/1024 From '+@DatabaseNameTxt+'.dbo.SysFiles where Name='''+@FileNameTxt +''''
--Select @SQLGetSizesTxt
exec sp_ExecuteSQL @SQLGetSizesTxt,N'@Size Int OUTPUT',@Size Output
--Space Used coverted to MB
set @SQLGetSizesTxt='SELECT @SpaceUsed= sum(Convert(dec(15),reserved))*8.000/1024.000 from '+@DatabaseNameTxt+'.dbo.SysIndexes  Where indId in (0,1,255)'
--Select @SQLGetSizesTxt
exec sp_ExecuteSQL @SQLGetSizesTxt,N'@SpaceUsed Int OUTPUT',@SpaceUsed Output
--10% of space Used
Set @TenPercent= @SpaceUsed*.1
--New Size of Physical file
Set @NewSize= @SpaceUsed*1.1
Set @PercentFree=100-(@SpaceUsed*100/@Size*100)/100

If @NewSize>@Size Begin
print'Resized'
Set @SQLAlterDatabaseTxt='Alter database '+@DatabaseNameTxt+' Modify file (Name='+@FileNameTxt+', Size='+convert(Varchar(10),@NewSize)+')'
exec (@SQLAlterDatabaseTxt)
End
Else Begin
Print 'No Resize Required!'
Print ''
End


Select 'Old Size' as 'File', @Size  as 'Size in MB' union all
Select 'Space Used',@SpaceUsed union all
Select 'Percentage Free',@PercentFree Union All
Select 'Ten Percent of Space Used',@TenPercent union all
Select 'New Size on resize',@NewSize 

Set @RetNm=0
Return
End


Help:
Print'up_DataFileResize Help:'
Print'up_DataFileResize is executed in 2 modes and expects 1 required parameter and 2 optional ones'
Print'@Mode: this value can be INFO or RESIZE.'
Print'@Mode=INFO will return back the filenames for a selected database'
Print'@DatabaseNameTxt is optional when @Mode=INFO. If no database name is passed in,'
Print'the current database is used.' 
Print'@FileNameTxt is not used when @Mode=INFO.'
Print'@Mode=RESIZE will resize a selected physical file for a selected database'
Print'@DatabaseNameTxt,@FileNameTxt are both required when @Mode=RESIZE'
Print'@DatabaseNameTxt is the name of the database where the rezize is to be executed.'
Print'@FileNameTxt is the logical filename of the physical file to be resized'
Print''
Print''
Return





GO