Geeks With Blogs
BI cooking with a touch of SQL

If you need to alter a database file and logfile to change their maxsize and filegrowth settings, here is a small script that might help you

----------------------------------------------------------------------------------------- spChangeMaxsizeandFilegrowth
----------------------------------------------------------------------------------------- Author: Remko de Boer
-- Created: 2013-01-30
-- Description: Change the Maxsize and filegrowth of database with a single
--  datafile and a single transaction logfile
---------------------------------------------------------------------------------------
-- Version: 1.0 Initial
---------------------------------------------------------------------------------------



-- Declare variables ------------------------------------------------------------------
declare @datafile varchar(250)
declare
@logfile varchar(250)
declare
@dbname varchar(250)
declare
@maxsize int
declare
@autogrowth int
declare
@sizesuffux char(2)
declare
@string nvarchar(4000)
---------------------------------------------------------------------------------------

-- Set Variables ----------------------------------------------------------------------

set @maxsize = 8192
set
@autogrowth = 64
set
@sizesuffux = 'MB'
---------------------------------------------------------------------------------------


-- automated variables setting --------------------------------------------------------
set @datafile = (select name from sys.database_files where type = 0)
set
@logfile = (select name from sys.database_files where type = 1)
set
@dbname = (select DB_NAME() as databasename)
---------------------------------------------------------------------------------------


-- Set the database file maxsize and filegrowth ---------------------------------------
set @string = N''
set
@string = @string + N'ALTER DATABASE [' + @dbname + '] '
set @string = @string
+ N'MODIFY FILE (NAME = [' + @datafile + '], '
set @string = @string + N'MAXSIZE = ' + cast(@maxsize as varchar) + @sizesuffux + ', '
set @string = @string + N'FILEGROWTH = ' + cast(@autogrowth as varchar) + @sizesuffux
set @string = @string + N' );'


exec
sp_executesql @string
---------------------------------------------------------------------------------------


-- set the logfile maxsize and filegrowth ---------------------------------------------
set @string = N''
set
@string = @string + N'ALTER DATABASE [' + @dbname + '] '
set
@string = @string + N'MODIFY FILE (NAME = [' + @logfile + '], '
set @string = @string + N'MAXSIZE = ' + cast(@maxsize as varchar) + @sizesuffux + ', '
set
@string = @string + N'FILEGROWTH = ' + cast(@autogrowth as varchar) + @sizesuffux
set @string = @string + N' );'


exec
sp_executesql @string
---------------------------------------------------------------------------------------
Posted on Wednesday, January 30, 2013 12:09 PM | Back to top


Comments on this post: Change the Maxsize and Filegrowth of a datafile and a logfile

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © remko | Powered by: GeeksWithBlogs.net