SQL Server Performance

Urgent: Need script to compare Log size to datafile size

Discussion in 'SQL Server 2005 General DBA Questions' started by shabnyc, Jul 31, 2007.

  1. shabnyc Member

    Hi everyone,
    I am looking for a script that check the log files for all DBs in an SQL server and compare the size of log to the size of datafile. If the size of log is about 50% of the datafile, an alert is sent to manager email address stating the log size is => 50% of datafile.
    any help is appreciated,
    thanks
    shab
  2. MohammedU New Member

    You can make use of the following script to write your own alert...SELECT @@servername as ServerName, db_name(database_id) as DatabaseName, Name as LogiCalFileName, Physical_Name [Physical file]
    , (size*8)/1024 [Size_MB] FROM
    sys.master_files
  3. shabnyc Member

    thanks MohammedU for replying,
    (FYI.. I am novice DBA specially in writing SQL. my next question is easy for you, but kinda hard for me to do. I hope this won't b dicouraging!) how I am going to compare the log to the data sizes since they are in the same column? or how can I get log and data size in 2 different columns and one row for each DB?
  4. AJITH123 Member

    You can do like this...
    --DECLARE @RetValue AS VARCHAR(10)
    SELECT --@RetValue =
    CASE WHEN (((T.size * 8) / 1024) *.5) > SUM(T1.Size) THEN 'Exceed Limit'
    ELSE 'Not Exceed' END
    ,T1.Size [Mdf-size]
    ,(T.size * 8 / 1024) [Ldf-size]
    FROM sys.master_files T
    INNER JOIN (
    SELECT database_id,TYPE,(SUM(SIZE) * 8 ) / 1024 [SIZE]
    FROM sys.master_files
    GROUP BY database_id,TYPE ) T1
    ON T.database_id = T1.database_id
    WHERE T.type = 1
    AND T1.Type = 0 AND T.Database_id =8
    GROUP BY (T.size * 8 / 1024),T1.Size
  5. shabnyc Member

    thanks Ajith, I actually need to compare sizes for each DB separetly not for all of them, so I can use SQL email to alert me for that particular DB if its log file is => 50% of the data file. any more ideas?
    thanks
  6. AJITH123 Member

    You can use 'AND T.Database_id = 10 <your db id> in the where clause...and assign the same to a variable like this,
    DECLARE @RetValue AS VARCHAR(10)
    SELECT @RetValue =
    CASE WHEN (((T.size * 8) / 1024) *.5) > SUM(T1.Size) THEN 'Exceed Limit'
    ELSE 'Not Exceed' END
    --,T1.Size [Mdf-size]
    --,(T.size * 8 / 1024) [Ldf-size]
    FROM sys.master_files T
    INNER JOIN (
    SELECT database_id,TYPE,(SUM(SIZE) * 8 ) / 1024 [SIZE]
    FROM sys.master_files
    GROUP BY database_id,TYPE ) T1
    ON T.database_id = T1.database_id
    WHERE T.type = 1
    AND T1.Type = 0 AND T.Database_id =8 -- <change it>
    GROUP BY (T.size * 8 / 1024),T1.Size
    Print @RetValue
  7. shabnyc Member

    Hi AJITH123 , thanks for your help
    I need to use that to send an alert when the size of log is > 50 %. so I am trying to do the following through a cursor later on for each DBs. but when I execute the following, I receive:Msg 102, Level 15, State 1, Line 22
    Incorrect syntax near '@sendmail'.
    Msg 102, Level 15, State 1, Line 29
    Incorrect syntax near 'T1'.declare
    @Sendmail varchar(1000)declare
    @dbname varchar(20)Set
    @dbname = 'dbname'set @Sendmail ='EXEC msdb.dbo.sp_send_dbmail
    @recipients = ''my name@domain.com'',
    @body = ''The log file on database: '+@dbname+' has exceeded the limit size'',
    @body_format=''text'',
    @subject = ''****SQL Server Message****** Log file is over 50%******'',
    @query_result_header =0,
    @query_result_width = 10000 ;'declare
    @RetValue AS VARCHAR(1000)SELECT
    @RetValue =CASE WHEN (((T.size * 8) / 1024) *.3) > SUM(T1.Size) THEN sp_executesql @sendmail
    else 'Looks Good'End
    FROM
    sys.master_files TINNER
    JOIN (SELECT database_id,TYPE,(SUM(SIZE) * 8 ) / 1024 [SIZE]
    FROM sys.master_files
    GROUP BY database_id,TYPE) T1ON
    T.database_id = T1.database_idWHERE
    T.type = 1AND
    T1.Type = 0 AND T.Database_id =8 -- <change it> GROUP
    BY (T.size * 8 / 1024),T1.Size
    -- do you think I can do sp_executesql the way I put it above.
    thanks
  8. AJITH123 Member

    I dont think so you can do it the way what you asked, so you can go with a different approach like this......
    DECLARE @Sendmail nVARCHAR(1000)
    DECLARE @dbname VARCHAR (20)
    SET @dbname = 'dbname'
    SELECT @Sendmail =
    CASE WHEN (((T.size * 8) / 1024) *.3) > SUM(T1.Size)
    THEN
    'EXEC msdb.dbo.sp_send_dbmail
    @recipients = ''my name@domain.com'',
    @body = ''The log file on database: ' + @dbname + ' has exceeded the limit size'',
    @body_format=''text'',
    @subject = ''****SQL Server Message****** Log file is over 50%******'',
    @query_result_header =0,
    @query_result_width = 10000 ;'
    ELSE
    NULL
    END
    FROM sys.master_files T
    INNER JOIN (
    SELECT database_id,TYPE,(SUM(SIZE) * 8 ) / 1024 [SIZE]
    FROM sys.master_files
    GROUP BY database_id,TYPE) T1
    ON T.database_id = T1.database_id
    WHERE T.type = 1
    AND T1.Type = 0 AND T.Database_id =8 -- <change it>
    GROUP BY (T.size * 8 / 1024),T1.Size
    IF @Sendmail IS NOT NULL
    EXEC sp_executesql @Sendmail
  9. shabnyc Member

    thanks Ajith123, this sounds good,
    just want to make sure, does T table represents Log file, and T1 represents Data files in your script? let me know if I am correct/wrong
    an example of what I need, when Log= 50GB and Data= 100GB then send alert stating log file exceeded the limit. otherwise, do nothing.
    thanks alot
    shabnyc
  10. MohammedU New Member

    Yes, in Ajith123 script...T1 represents data and T represents the log....it is called aliasing...
  11. AJITH123 Member

    Dont forget to multiply with .5 [50%], since in the script, it is showing .3
  12. shabnyc Member

    I will not forget doing that,
    thanks Ajith123, thanks Mohamed, you guys were very helpful. I appreciate your patience and cooperation.
    Thanks SQL-Performace.com
  13. shabnyc Member

    Guys, before I get this resolved, have one last question, why would you get the .5 (50%) of the Log file and not from the Datafile since we are looking at 50% of data file?
    why should not it be like this: -- (T1=data, T=Log)..
    CASE WHEN Sum((T.size * 8) / 1024) > ((T.Size * 8) /1024) * .5) Then.....send email Else, ..
  14. shabnyc Member

    Hi guys, thanks for your help.
    I put this on my testing enviornment. is there any feedback for my previous message.
    thanks
    ShabNyc

Share This Page