SQL Server Performance Forum – Threads Archive

Passing variables to ALTER DATABASE

A little new on the rules of T-SQL. Trying to pass a variable to the ALTER Database command: declare @newsize as char(3)
SET @newsize = ‘3MB’
USE master
ALTER DATABASE Northwind
MODIFY FILE
(NAME = Northwnd,
SIZE = @newsize) Errors out. Is this possible? I have tried int and varchar and get the same error.

You would need to use dynamic SQL, where you prepare the whole script as a string, concatenating the variables, then executing the string. However, why would you want to use variables here? This sort of script is obviously intended to be executed once, so there’s absolutely no point in creating re-usable code. Just put the values where you’ve put the variables. In terms of T-SQL, you should be cautious with re-usable code. It may have its place, but don’t be surprised if you run into performance issues, or find you’ve painted yourself into a corner. If you’re coming from a programming background, you’re in for a lot of fun – unlearning recordset loops and stuff. Enjoy the ride![<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

Actually this is not the entire script. I just pasted a segment where is was having an issue. The script will actually be run in a job every 4 hours to check the free space of a particular db. The autogrow function was causing disconnects from another vendors system. I am basiacally re-inventing the wheel and creating my own version of auto grow that executes if the amount of free space falls below a predefined level, say 500MB. It is really a failsafe if the DBA does not do his/her job in monitoring db growth. I was just looking at dynamic sql this morning right after posting this. I will try it out. Thanks

Why not run assign a size to the data file and monitor it on weekly basis and then increase the size in order to addup intermittent workaround. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

It is possible and one of the most frequently referenced article about that topic in general you can find here:<a target="_blank" href=http://www.sommarskog.se/dynamic_sql.html>http://www.sommarskog.se/dynamic_sql.html</a> Although it’s a quite long article, make sure you read closely through it. It’s a good read. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstÃ¯Â¿Â½tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

]]>