0

I'm building VMs centrally and distributing subtly tweaked versions to different people. I've been doing this by mounting the virtual hard disk and editing files. I'm pretty confident I can do something similar with the registry. The next subtle tweak I'd like to make is to the SQL memory allocations.

I know you can change this by running a stored proc, but is there any way to do it that would work while the machine is offline? If not, is there a way I can push a sql command onto a queue somewhere so that it gets run the next time SQL starts up?

Much obliged,

Dave

Dave Gregory
  • 143
  • 3

3 Answers3

1

You can run a stored procedure on startup - see http://weblogs.sqlteam.com/mladenp/archive/2007/08/14/60280.aspx. Your stored procedure could then read in parameters from the file system. Bit nasty though.

Joel Mansford
  • 985
  • 1
  • 5
  • 13
1

What you can do is use the stored procedure sp_procoption to run a specified sp at startup. In this procedure you'll have to read your values from the registry, file or other linked server.

See: sp_procoption

Anders

chankster
  • 1,324
  • 7
  • 9
0

A fun way to do this across many machines using the power of SQL 2008 is by using the new policy based management. Create a policy that sets the max memory to a number of your choosing. Apply your policy against the target server and your setting will change. If you don't have a SQL 2008 instance in-house yet you can download the 180-day trial of Enterprise Edition and try it out.

Try SQL 2008: http://www.microsoft.com/sqlserver/2008/en/us/trial-software.aspx

Policy Based Management team blog (great resource): http://blogs.msdn.com/sqlpbm/default.aspx

Blog regarding PBM against 2000/2005 servers: http://blogs.msdn.com/sqlpbm/archive/2008/07/04/using-pbm-against-sql2k-and-sql2k5.aspx

SQLChicken
  • 1,307
  • 8
  • 10