Manually growing a data file by a certain percentage

Hello all,

With a script, I need to get the file size of a DB's data file, multiply it by a certain number, then grow the data file by the results.

Here is my script I came up with that is not working (FYI, I know next to nothing about SQLCMD mode...I just found references to it online and read up on it a little bit):



******** Start of Code ********


DECLARE @intNbrOfPages int
, @intCurrentFileSizeKB int
, @intNewFileSizeKB int


SET @intNbrOfPages = (SELECT [size] FROM sys.database_files WHERE [file_id] = 1)
SET @intCurrentFileSizeKB = @intNbrOfPages / 128 * 1024
SET @intNewFileSizeKB = CAST((CAST(@intCurrentFileSizeKB as float) * 4.3) as int)

:setvar newfilesize @intNewFileSizeKB

ALTER DATABASE [USAAFederalSavingsBank]
MODIFY FILE (NAME = USAAFederalSavingsBank, SIZE = $(newfilesize) KB);
/*
MODIFY FILE (NAME = USAAFederalSavingsBank, SIZE = @intNewFileSizeKB KB);
*/
GO



******** End of Code ********


You'll see I tried ":setvar newfilesize @intNewFileSizeKB", but I'm guessing that will give me the exact same thing as the commented-out line. I'm guessing if I hard code a value for "newfilesize", that will work (just guessing...haven't tried/verified it), but I need the new file size to be calculated.

is there any way to pull the value from "intNewFileSizeKB" and store it in "newfilesize"?...something like ":setvar newfilesize val(@intNewFileSizeKB)"?

Thanks for any help anyone can provide,

CTB

CTB ( ) writes:
> ******** Start of Code ********
>
>
> DECLARE @intNbrOfPages int
> , @intCurrentFileSizeKB int
> , @intNewFileSizeKB int
>
>
> SET @intNbrOfPages = (SELECT [size] FROM
> sys.database_files WHERE [file_id] = 1)
> SET @intCurrentFileSizeKB = @intNbrOfPages / 128 * 1024
> SET @intNewFileSizeKB =
> CAST((CAST(@intCurrentFileSizeKB as float) * 4.3) as int)
>
>:setvar newfilesize @intNewFileSizeKB
>
> ALTER DATABASE [USAAFederalSavingsBank]
> MODIFY FILE (NAME = USAAFederalSavingsBank, SIZE = $(newfilesize) KB);
> /*
> MODIFY FILE (NAME = USAAFederalSavingsBank, SIZE = @intNewFileSizeKB KB);
> */
> GO

SQLCMD mode cannot work where, becuase you cannot set a variable in SQLCMD
with a value from SQL Server. Remember that SQLCMD is a client, and SQL
Server is a server, and they have different process spaces.

But you can use dynamic SQL:

SELECT @sql = 'ALTER DATABASE [USAAFederalSavingsBank]
MODIFY FILE (NAME = USAAFederalSavingsBank, SIZE = ' +
ltrim(str(@intNewFileSizeKB)) + ' KB)'
EXEC sp_executesql @sql

Dynamic SQL is a very powerful feature, but it can easily be abused. I
have an article on my web site that discusses it benefits and pitfalls:
http://www.sommarskog.se/dynamic_sql.html

--
Erland Sommarskog, SQL Server MVP, esquel.TakeThisOut@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx