Urgent: Need script to compare Log size to datafile size

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

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

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?

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

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

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

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

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

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

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

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

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

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, ..

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