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

Dear all,

Right now we have issue that we don't know who change the logical limit of a user database file size to limited growth, we want to find out a way list out any which has size set to limited instead of  unlimited, any script to do it ?

DDL trigger might not suite us as we don't connect the database system to an email system. can't receive notification in email.

DDL to record down who did it is welcome later but daily script check is the first considered.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL Server DBACommented:
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'
0
Deepak ChauhanSQL Server DBACommented:
In addition you can create an audit DB with event table to capture the database Alteration event.

here is the script.

Create database AuditDB
go
USE AuditDB
GO
CREATE TABLE dbo.DatabaseAlterEvents
(
    EventDate    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    EventType    NVARCHAR(64),
    EventDDL     NVARCHAR(MAX),
    DatabaseName NVARCHAR(255),
    SchemaName   NVARCHAR(255),
    ObjectName   NVARCHAR(255),
    HostName     VARCHAR(64),
    ProgramName  NVARCHAR(255),
    LoginName    NVARCHAR(255)
);

GO

/****** 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


Now you can query the dbo.DatabaseAlterEvents

select * from dbo.DatabaseAlterEvents
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
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 ...?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

marrowyungSenior Technical architecture (Data)Author Commented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
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

Open 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 ?
0
Deepak ChauhanSQL Server DBACommented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
one thing for the trigger, anyway to check if this kind of trigger already exist in the database already?
0
Deepak ChauhanSQL Server DBACommented:
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
0
marrowyungSenior Technical architecture (Data)Author Commented:
so the one shown in your picture only  include DDL trigger ?

DML trigger is considered to be database or table level, right?
0
marrowyungSenior Technical architecture (Data)Author Commented:
hi,

can SQL Server 2008  R2 Extend event log down who change the database data file size limitation ?
0
Deepak ChauhanSQL Server DBACommented:
@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
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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?
0
Deepak ChauhanSQL Server DBACommented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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

Open in new window

0
Deepak ChauhanSQL Server DBACommented:
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' ; 
    
 

Open in new window

0
marrowyungSenior Technical architecture (Data)Author Commented:
"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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
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 ?
0
Deepak ChauhanSQL Server DBACommented:
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)'),
0
marrowyungSenior Technical architecture (Data)Author Commented:
oh, you mean the EventDDL column ?

e.g. :

ALTER DATABASE [<Database name> ] MODIFY FILE ( NAME = N'iXAudit_Port_Data', MAXSIZE = 409600KB )
0
marrowyungSenior Technical architecture (Data)Author Commented:
one thing, if the change script is execute from within inside the SQL job, it will print in "program name" column ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
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>' ;

Open 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 !
0
marrowyungSenior Technical architecture (Data)Author Commented:
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?
0
Deepak ChauhanSQL Server DBACommented:
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
0
marrowyungSenior Technical architecture (Data)Author Commented:
yes, it is very handy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.