April 14, 2010 at 7:14 am
I'm trying to write some code, either a trigger or another monitoring object that watches for a new database to be added, then adds 2-3 .ndf files with the same naming (if DB01.mdf then DB01_002.ndf). The database itself is created by an application I can't modify so my thought was to use a DDL trigger to watch for a create database event with a particular naming convention "PROD_"+unique name and then use sys.master_files to gather the needed information in temporary variable to create an alter database statement. This is in SQL2005 Standard, by the way.
I'm pretty new to writing procedural code, so I'm wondering if this the best way to go about it?
Any help or points in the right direction would be helpful. Also, please let me know if this a question better suited to the newbie section.
Thank you,
Bryan
April 14, 2010 at 8:05 am
You could do that with a DDL trigger on the CREATE_DATABASE event. However, can I ask what the purpose of this is? I'd like to understand what is going on before I give more suggestions.
CEWII
April 14, 2010 at 8:21 am
Currently I add each .ndf file by hand (two of them) to spread the datafiles across multiple drives on the server. Given the databases are created by our operations department via their program, there are times the database grows significantly before I get to adding the new .ndf files manually. I'd like to automate this process so I don't have to add them by hand and they get added at the same time the initial database is created to spread the load more evenly on the server for each database.
Thanks,
Bryan
April 14, 2010 at 9:21 am
Ok, that makes sense. I would be sure to include logic to make sure you don't create too many files. In other words, watch what assumptions are made. You can do this with a DDL trigger, often user rights can come into play with DDL triggers. But if you have enough enough rights to create a database you will VERY likely have the rights to modify them too.. Because those triggers are run in the context of the user you can only do what that user can do..
CEWII
April 21, 2010 at 12:25 pm
Well, I was able to get everything working up to adding the actual trigger...
When I fire off the code to create the trigger (below), I receive the errors:
"
Msg 102, Level 15, State 1, Procedure AddNDF, Line 40
Incorrect syntax near 'END'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'END'.
"
I have successfully run the code within the trigger's BEGIN/END. Can someone advise me where I am going wrong or better ways to handle this? I'd like to say in advance I'm fairly new at developing in SQL. This was created and run in SSMS for 2005 MS SQL Standard version.
Thank you for any help!
Bryan Wilson
Here is the code:
Use master
GO
IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'AddNDF')
DROP TRIGGER AddNDF
ON ALL SERVER
GO
CREATE TRIGGER AddNDF
ON ALL SERVER
FOR CREATE_DATABASE
AS
BEGIN
/*
**Created by Bryan Wilson - 04/21/2010**
Trigger to create to add two new ndf files and normalize mdf
*/
--Declarations
Declare @DBID int
Declare @DBName as varchar(80)
Declare @NDFName as varchar(80)
Declare @NDFPath1 as varchar(max)
Declare @NDFPath2 as varchar(max)
Declare @SQLString as varchar(max)
Set @DBName=''
Set @NDFName=''
Set @NDFPath1=''
Set @NDFPath2=''
--Select new database
set @DBID=(Select Max(database_id) from sys.master_files)
--Error testing
If not exists (select physical_name from sys.master_files where database_id=@DBID and right(physical_name,4) = '.ndf')
BEGIN
Set @DBName = (Select name from sys.databases where database_id=@DBID)
Set @NDFName = (Select name from sys.databases where database_id=@DBID)+'_00'
Set @NDFPath1 = (replace((Select physical_name from sys.master_files where database_id=@DBID and (right(physical_name,4))='.mdf'),'.mdf','_002.ndf'))
set @NDFPath1 = 'C'+(Right(@NDFPath1,(Len(@NDFPath1)-1)))
Set @NDFPath2 = (replace((@NDFPath1),'_002.ndf','_003.ndf'))
set @NDFPath2 = 'C'+(Right(@NDFPath2,(Len(@NDFPath2)-1)))
SET @SQLString = 'ALTER DATABASE '+@DBName+' ADD FILE
(NAME = '+@NDFName+'2, FILENAME = '''+@NDFPath1+''', Size=500MB, Filegrowth=5%),
(NAME = '+@NDFName+'3, FILENAME = '''+@NDFPath2+''', Size=500MB, Filegrowth=5%)
ALTER DATABASE '+@DBName+' MODIFY FILE (NAME = '+@DBName+', Size=500MB)
ALTER DATABASE '+@DBName+' MODIFY FILE (NAME = '+@DBName+', Filegrowth=5%)'
--Select @SQLString
EXEC(@SQLString)
END
--In the future, add failure email here.
--Else
--Select 'Error output needed'
GO
/*
Helpful views and queries
Select * from sys.master_files
Select * from sys.databases
Select name,physical_name,database_id from sys.master_files where database_id=@DBID
*/
END
April 21, 2010 at 1:14 pm
There's a GO right before the last END. Since GO is a batch breaker, that terminates the CREATE TRIGGER statement and leaves an orphaned END at the end.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2010 at 1:34 pm
Ah ha - thank you Gail. That was a rather silly mistake!
The code worked great, the trigger has been created. Except now when the application creates a new database the following error is triggered in the application's log.
"
System.Data.SqlClient.SqlException: ALTER DATABASE statement not allowed within multi-statement transaction.
"
A brief look in Google suggests there may be some set statements that need to be made initially in the code launched by the trigger - referenced to http://msdn.microsoft.com/en-us/library/ms190356.aspx. Could someone with more experience guide me in the right direction? Is it the multiple "alter" statements in @SQLString causing the issue? Is it something to do with the trigger acting too fast? Or the setup on within the trigger differentiated from my SSMS?
Thanks for any suggestions where to start!
May 7, 2010 at 7:10 am
Followup:
If anyone needs the answer to this - I changed my methodology by adding a record to a tracking table setup specifically for this purpose and used a maintenance job to regularly check and execute a stored procedure, which created the ndf file. Its a bit clumsy, but it gets the job done.
-Bryan
May 7, 2010 at 3:09 pm
Actually,I kind of like that methodology..
CEWII
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply