list all MS SQL user database which has data file size not set to unlimlited

You can query Sys.master_files

select DB_NAME(database_id) as DBName, file_id, type_desc, name, physical_name, state_desc, max_size
from sys.master_files
where max_size != -1 and type_desc='ROWS'

Dear Sir,

tks for that :

"select DB_NAME(database_id) as DBName, file_id, type_desc, name, physical_name, state_desc, max_size
from sys.master_files
where max_size != -1 and type_desc='ROWS'"

but one thing, it can't show if the log file has limit too !  it only show data file, anyway to show the log file set to limited size ?

also the max_size it returns seems wrong, my example is, it set to limited to growth more than 400MB but the script show it is 51200 , that 51200 is MB , right? not KB ...?

this one seems doing better:

select DB_NAME(database_id) as DBName, file_id, type_desc, name, physical_name, state_desc, max_size
from sys.master_files
where ((max_size != -1 and type_desc='ROWS') OR ((max_size != -1 and type_desc ='LOG')  AND ( max_size != 268435456 and type_desc ='LOG') ))
and DB_NAME(database_id) NOT IN ('master', 'msdb', 'tempdb', 'model');


as you know once the log file size change to a smaller size than 2TB, once change to unlimited again, the maximize size it can reach is 2TB.

for this:

/****** Object:  DdlTrigger [ddl_trig_database]    Script Date: 05/13/2015 11:40:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
Create TRIGGER [ddl_trig_database] 
ON ALL SERVER 
FOR Alter_DATABASE 
AS 
insert into AuditDB.dbo.DatabaseAlterEvents
    SELECT  getdate()    
           ,EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'), 
            EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'),
            DB_NAME(),
            EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
            EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
            HOST_NAME(),
            PROGRAM_NAME(),
            SUSER_SNAME();
 
GO

Select allOpen in new window

it record all change, yes, it is good but what if

1) we just need to know the change in DB size limited with who(what login) and time the login do it, together with what tools they use to do it, e.g. script by SMS, SQL job with job name..
2) how can we disable the trigger in case it is totally out of control.
3) where can we add the email alert over there in case our email is allowed for that.
4) once 1) was done, can we trigger database by database automatically and change the file size of that file automatically ?

Script 1 was the initial start up script you can modify it accordingly.
 @script show it is 51200 , that 51200 is MB , right? not KB
 >> This value is not in MB in Sys.master_files, the max size in 8 KB pages.
so  value =51200 convert in KB  >> 51200*8 =409600 KB and 409600/1024 =400MB.

you can change in the script   >>  (max_size *8)/1024 as maxSize_MB


Script 2.
1) we just need to know the change in DB size limited with who(what login) and time the login do it, together with what tools they use to do it, e.g. script by SMS, SQL job with job name..  

This will records only changes happened by Alter database command  and you can query the

select * from AuditDb.dbo.DatabaseAlterEvents
 
           PROGRAMNAME -- column will show the tool name
           Loginname -- Column will show the login id.
           Eventdate -- column having command execution time.

2) how can we disable the trigger in case it is totally out of control.
    >>DISABLE TRIGGER [ddl_trig_database] ON ALL SERVER

3) where can we add the email alert over there in case our email is allowed for that.
     You can add one more For insert trigger on AuditDb.dbo.DatabaseAlterEvents table, whenever new alter command record will be inserted trigger through the email.
   
4) once 1) was done, can we trigger database by database automatically and change the file size of that file automatically ?

Yes you can write a script to change the file size if does not match the standard.

one thing for the trigger, anyway to check if this kind of trigger already exist in the database already?

For server level trigger you can see as showing in the attached.

trigger exist in the database run the query.

use <your database name>

select * from sys.triggers

or expand the Database > Tables > tablename > triggers

here you can find the list of trigger on the table.
trig.jpg

so the one shown in your picture only  include DDL trigger ?

DML trigger is considered to be database or table level, right?

hi,

can SQL Server 2008  R2 Extend event log down who change the database data file size limitation ?

@DML trigger is considered to be database or table level, right?
yes you are right.

Here is the link for throughout extended event information with example

https://technet.microsoft.com/en-us/library/dd822788(v=sql.100).aspx

"Here is the link for throughout extended event information with example

https://technet.microsoft.com/en-us/library/dd822788(v=sql.100).aspx"

so this mean extend event can show who and when they change the DB size limit, right?

I don't think extended events can bless us with this functionality  in sql server 2008 but not sure in SQL server 2012 and higher.

"2) how can we disable the trigger in case it is totally out of control.
    >>DISABLE TRIGGER [ddl_trig_database] ON ALL SERVER
"

but basically we can simple right click on that server level trigger and select delete, the one shown in your jpg, right?

but deleting that trigger will not record to that table which show someone deleted the trigger and who is it, right? someone can remove it.

"3) where can we add the email alert over there in case our email is allowed for that.
     You can add one more For insert trigger on AuditDb.dbo.DatabaseAlterEvents table, whenever new alter command record will be inserted trigger through the email."

so the new one can be:

SET QUOTED_IDENTIFIER ON
GO
Create TRIGGER [ddl_trig_database] 
ON ALL SERVER 
FOR Alter_DATABASE, insert 
AS 
declare @emailalert varchar(200);

insert into AuditDB.dbo.DatabaseAlterEvents
    SELECT  getdate()    
           ,EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'), 
            EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'),
            DB_NAME(),
            EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
            EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
            HOST_NAME(),
            PROGRAM_NAME(),
            SUSER_SNAME();
 
GO

set @emailalert =  concat (getdate()    
           ,EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'), 
            EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'),
            DB_NAME(),
            EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
            EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'), HOST_NAME(), PROGRAM_NAME(),  SUSER_SNAME() )

	EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Account_1',
	@recipients = email address,
    @body = @query,
    @subject = @emailalert;

GO

Select allOpen in new window

Yes you can delete any anyone can delete if he\she has permissions to delete the server objects.

@but deleting that trigger will not record to that table which show someone deleted the trigger and who is it, right? someone can remove it.
>>Yes its true.

3) where can we add the email alert over there in case our email is allowed for that

Please check the script, i have just written for you. Here is two thing. If you want to keep the changes records into AuditDB then you can use insert statement. otherwise you can remove the insert code from the trigger. This will just send a notification email to you at the event of database alteration.
 

/****** Object:  DdlTrigger [ddl_trig_database]    Script Date: 05/13/2015 11:40:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
Create TRIGGER [ddl_trig_database] 
ON ALL SERVER 
FOR Alter_DATABASE 
AS 

declare @date datetime
declare @eventtype varchar(1000)
declare @eventDDL nvarchar(max)
declare @DBNAME varchar(100)
declare @hostName varchar(128)
declare @programName nvarchar(max)
declare @login nvarchar(100)
DECLARE @tableHTML  NVARCHAR(MAX)
Declare @sub nvarchar(1000)

--************ You can remove this CODE in the * section if you dont want to save the record in AuditDB

insert into AuditDB.dbo.DDLEvents
    SELECT  getdate()    
           ,EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'), 
            EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'),
        
            DB_NAME(),
            EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
            EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
            HOST_NAME(),
        
            PROGRAM_NAME(),
            SUSER_SNAME();
--*********************************************************************************************************
--=======================Email notification send on the event of alter database===========================    
    SELECT  @date=getdate(),   
            @eventtype=EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'), 
            @eventDDL=EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'),
            @DBNAME=DB_NAME(),
            @hostName=HOST_NAME(),
            @programName=PROGRAM_NAME(),
            @login=SUSER_SNAME();
 
 SET @tableHTML =
    N'<H1>Database Changes Report</H1>' +
    N'<table border="1">' +
    N'<tr><th> date</th><th> eventtype</th>' +
    N'<th> eventDDL</th><th> DBNAME</th>' +
    N'<th> hostName</th><th> programName</th>' +
    N'<th> login</th></tr>'+
    CAST ( ( SELECT td =  @date,  '',
                    td =  @eventtype, '',
                    td =  @eventDDL, '',
                    td =  @DBNAME, '',
                    td =  @hostName,  '',
                    td =  @programName, '',
                    td =  @login, ''
                    FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;
    set @sub='Database Change happened on server ::'+@@servername
    
EXEC msdb.dbo.sp_send_dbmail 
    @profile_name = '<Profile Name>',
    @recipients='<Recepient Email ID>',
    @subject = @sub,
    @body = @tableHTML,
    @body_format = 'HTML' ; 
    
 

Select allOpen in new window

"Yes you can delete any anyone can delete if he\she has permissions to delete the server objects.

@but deleting that trigger will not record to that table which show someone deleted the trigger and who is it, right? someone can remove it.
>>Yes its true."

yeah, verified that, it doesn't get recorded.

one thing, I don't see what kind of SQL query the user use to alter the database, even from SSMS UI, is it possible to add this content too ?

This code is already in the script. Whatever users will execute, that will be capture in the AuditDB in email notification as well.

If you checked the script below command will capture the same.

EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'),

oh, you mean the EventDDL column ?

e.g. :

ALTER DATABASE [<Database name> ] MODIFY FILE ( NAME = N'iXAudit_Port_Data', MAXSIZE = 409600KB )

one thing, if the change script is execute from within inside the SQL job, it will print in "program name" column ?

ok, you script here:

this:

SELECT  @date=getdate(),  
            @eventtype=EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'),
            @eventDDL=EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'),
            @DBNAME=DB_NAME(),
            @hostName=HOST_NAME(),
            @programName=PROGRAM_NAME(),
            @login=SUSER_SNAME();
 

just assign the value to all variable, and then you just format the email content to an HTML format by:

 SET @tableHTML =
    N'<H1>Database Changes Report</H1>' +
    N'<table border="1">' +
    N'<tr><th> date</th><th> eventtype</th>' +
    N'<th> eventDDL</th><th> DBNAME</th>' +
    N'<th> hostName</th><th> programName</th>' +
    N'<th> login</th></tr>'+
    CAST ( ( SELECT td =  @date,  '',
                    td =  @eventtype, '',
                    td =  @eventDDL, '',
                    td =  @DBNAME, '',
                    td =  @hostName,  '',
                    td =  @programName, '',
                    td =  @login, ''
                    FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

Select allOpen in new window

then assign a subject for the email :

set @sub='Database Change happened on server ::'+@@servername

that's it?

why don't just use the concat and +  ' ' syntax to construct the email body ? however it seems similar in complexity !

how many type of DDL trigger we have other that:

Create TRIGGER [ddl_trig_database]
ON ALL SERVER
FOR Alter_DATABASE

ALL server, Alter_Database?

You can go through the microsoft documentation there are very good and clear explanation around DDL trigger and its syntax with example.

This script is working very well and solve the requirement which you explained in your original question.

This is doing everything what you need.

Thanks

yes, it is very handy