June 19, 2013 at 3:49 am
Good day all,
I would like to know if it is possible to limit the number of databases on an instance and if yes, how can I do it? I thank you in advance for your assistance.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
June 19, 2013 at 4:40 am
You can try this approach.
ALTER TRIGGER DDLTrigger_Sample
ON ALL SERVER
FOR CREATE_DATABASE
AS
BEGIN
if (select count(*) from sys.databases ) > 9
rollback;
END
Sriram
June 19, 2013 at 4:45 am
CREATE TRIGGER [DDLTriggerLimitNumberOfDBs]
ON ALL SERVER
FOR CREATE_DATABASE
AS
SET NOCOUNT ON
DECLARE @MSG VARCHAR(500)
IF (SELECT COUNT(*) FROM sys.databases WHERE name NOT IN ('master', 'msdb', 'model', 'tempdb')) > 5 -- Change this to whatever you want!
BEGIN
SET @MSG = N'maximum number of user databass reached'
RAISERROR(@MSG,16,1);
ROLLBACK
END
GO
ENABLE TRIGGER [DDLTriggerLimitNumberOfDBs] ON ALL SERVER
GO
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
June 19, 2013 at 5:33 am
Abu Dina (6/19/2013)
CREATE TRIGGER [DDLTriggerLimitNumberOfDBs]ON ALL SERVER
FOR CREATE_DATABASE
AS
SET NOCOUNT ON
DECLARE @MSG VARCHAR(500)
IF (SELECT COUNT(*) FROM sys.databases WHERE name NOT IN ('master', 'msdb', 'model', 'tempdb')) > 5 -- Change this to whatever you want!
BEGIN
SET @MSG = N'maximum number of user databass reached'
RAISERROR(@MSG,16,1);
ROLLBACK
END
GO
ENABLE TRIGGER [DDLTriggerLimitNumberOfDBs] ON ALL SERVER
GO
Abu, thanks a lot I have already put this trigger on a test server and it works famously.
You can try this approach.
ALTER TRIGGER DDLTrigger_Sample
ON ALL SERVER
FOR CREATE_DATABASE
AS
BEGIN
if (select count(*) from sys.databases ) > 9
rollback;
END
Sriram
Sriram,
Thank you also for your effort. It come basically down to the same thing as what Abu gave me. He just added something more.
Thanks a lot to both.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
June 19, 2013 at 5:36 am
You welcome! 🙂
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply