moving procedures to other filegroup ?
-
can we move all stored procedures and functions to another file group other than primary filegroup.
-
Hi,
"Physical Database Files and Filegroups" article in BOL for SQL 2000 says that "All pages for system tables are allocated in the primary filegroup"
Since all procedures are text that is stored in syscomments table than they are stored in the Primary filegroup.
The very popular alternative of several years ago is to create one database with data tables and another database with views and procedures. I support a couple of applications designed like that.
Regards,Yelena Varsha
-
Yes You are right Varshal ,
I rather thinking where exactly stored procedures are stored , thinking blindly to store into some other filesystems, like how we do for tables and indexes.
Thanks a lot .
-
this would be like pulling a cat insideout
(in a matter of speaking 
)If you don't want your table / index objects to reside with your system-info, move them to another filegroup.
this is a way to do it :
ALTER DATABASE 'yourdb'
ADD FILEGROUP 'yournewfilegroupname'
go
ALTER DATABASE 'yourdb'
ADD FILE < filespec > TO FILEGROUP 'yournewfilegroupname'
go
-- Set the new filegroup as default filegroup for new objects
ALTER DATABASE 'yourdb'
MODIFY FILEGROUP 'yournewfilegroupname' DEFAULT
GO
If you do this on an existing database which contains user-objects, you'll have to move them.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply