Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Resize data file manually to prevent downtime

By acampoma, 2003/06/19

This script will resize the data file of a passed in database name and filename by 10%. It can run in 3 modes.Resize, INFO, HELP. RESIZE is passed into parameter @Mode when we know the database name and file we wish to resize. INFO is passed in when we only know the database name and we wish to find out the data file names. HELP is passed in when we have no idea what to do. I run this in my job agent every night to prevent SQL from doing it automatically every time the data file fills up. The reason for this is that this automatic resize can happen at any time (including peak hours) and it could slow down performance.
Instructions and parameter definitions are in the stored proc comments.
Keep in mind this uses custom errors for error trapping. You should create these errors before doing this:.Just run the following SQL. EXEC sp_addmessage 55001, 16, 'Validate - Source: [''%s''] Operation: [''%s''] Description: [''%s'']' , NULL, true, REPLACE

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
-- Name			Date			Change
---------------------------------------	---------------------------------------	--------------------------------------------------------------------------------
-- Alex Campomanes	2003-03-20		Initial 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