Purpose is to monitor health on mssql database installations and send one report to an email with detailed attached (Uses the opensource utilities bmail.exe, mpack.exe and munpack.exe )

DatabaseStorageUsageQuery.sql

SET NOCOUNT ON
SELECT '$(ServerName)' AS ServerName
			,CAST( DB_NAME(mf.database_id) AS varchar) AS databaseName
            ,CAST(size_on_disk_bytes AS varchar(15)) as size_on_disk
			,CAST(dbs.state_desc AS varchar(10)) as [state]
			,CAST(dbs.recovery_model_desc AS varchar(10)) as rec_model
			,CAST(dbusers.name AS varchar(10)) as [Owner]
            ,CAST(num_of_reads AS varchar(15)) as num_of_reads
            ,CAST(num_of_bytes_read AS varchar(15)) as num_of_bytes_read
            ,CAST(io_stall_read_ms AS varchar(15)) as io_stall_read_ms
            ,CAST(num_of_writes AS varchar(15)) as num_of_writes
            ,CAST(num_of_bytes_written AS varchar(15)) as num_of_bytes_written
            ,CAST(io_stall_write_ms AS varchar(15)) as io_stall_write_ms
            ,CAST(io_stall AS varchar(15)) as io_stall
			,CAST(dbs.compatibility_level AS varchar(15)) as compa_level
			,CAST(dbs.page_verify_option_desc AS varchar(15)) as page_verify
			,CAST(mf.physical_name AS varchar(100)) AS physical_name
            FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
            JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
			JOIN sys.databases AS dbs ON dbs.database_id = mf.database_id 
			JOIN sys.server_principals AS dbusers ON dbusers.sid = dbs.owner_sid
            AND mf.file_id = divfs.file_id
            --ORDER BY num_of_reads DESC 
            ORDER BY size_on_disk_bytes DESC

DatabaseStorageUsageShortQuery.sql

SET NOCOUNT ON
SELECT '$(ServerName)' AS ServerName
			,CAST( DB_NAME(mf.database_id) AS varchar) AS databaseName
            ,CONVERT(varchar(20),REPLACE(CONVERT(varchar(20), CAST(size_on_disk_bytes AS Money), 1),'.00','')) as size_on_disk
            ,CAST(num_of_bytes_read AS varchar(15)) as num_of_bytes_read
            ,CAST(num_of_bytes_written AS varchar(15)) as num_of_bytes_written
            FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
            JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
			JOIN sys.databases AS dbs ON dbs.database_id = mf.database_id 
			JOIN sys.server_principals AS dbusers ON dbusers.sid = dbs.owner_sid
            AND mf.file_id = divfs.file_id
			WHERE size_on_disk_bytes > 1000000000
            ORDER BY size_on_disk_bytes DESC

MsSqlDatabaseMonitorUsageWithDetailedReportToEmail.cmd

Echo off
Echo Author Jan Hebnes / [email protected]
Echo Created 2014-02-20 
Echo.
Echo Purpose is to monitor health on mssql database installations and send one report to an email with detailed attached

echo DEVELOPMENT1 > DatabaseStorageUsage.txt
Sqlcmd -S development1 -E -v ServerName = "development1" -i DatabaseStorageUsageQuery.sql >> DatabaseStorageUsage.txt
echo DEV2 >> DatabaseStorageUsage.txt
Sqlcmd -S development2 -E -v ServerName = "development2" -i DatabaseStorageUsageQuery.sql >> DatabaseStorageUsage.txt
echo TEST1 >> DatabaseStorageUsage.txt
Sqlcmd -S test1 -E -v ServerName = "test1" -i DatabaseStorageUsageQuery.sql >> DatabaseStorageUsage.txt
echo TEST2 >> DatabaseStorageUsage.txt
Sqlcmd -S test2 -E -v ServerName = "test2" -i DatabaseStorageUsageQuery.sql >> DatabaseStorageUsage.txt
echo PROD1 >> DatabaseStorageUsage.txt
Sqlcmd -S production1 -E -v ServerName = "production1" -i DatabaseStorageUsageQuery.sql >> DatabaseStorageUsage.txt

echo Please find attached today's top fat databases report >> body.txt
echo. >> body.txt
echo. List of 1+ GB Databases (size_on_disk / num_of_bytes_read / num_of_bytes_written) >> body.txt
echo. >> body.txt
echo DEVELOPMENT1 >> body.txt
Sqlcmd -S development1 -E -v ServerName = "development1" -i DatabaseStorageUsageShortQuery.sql -h-1 >>body.txt
echo. >> body.txt
echo DEVELOPMENT2 >> body.txt
Sqlcmd -S development2 -E -v ServerName = "development2" -i DatabaseStorageUsageShortQuery.sql -h-1 >>body.txt
echo. >> body.txt
echo TEST1 >> body.txt
Sqlcmd -S test1 -E -v ServerName = "test1" -i DatabaseStorageUsageShortQuery.sql -h-1 >> body.txt
echo. >> body.txt
echo TEST2 >> body.txt
Sqlcmd -S test2 -E -v ServerName = "test2" -i DatabaseStorageUsageShortQuery.sql -h-1 >> body.txt
echo. >> body.txt
echo PRODUCTION1 >> body.txt
Sqlcmd -S production1 -E -v ServerName = "production1" -i DatabaseStorageUsageShortQuery.sql -h-1 >> body.txt
echo. >> body.txt
echo More details can be found in the attached DatabaseStorageUsage.txt >> body.txt

mpack -s "Database Usage Report" -d body.txt -o body.msg DatabaseStorageUsage.txt
bmail -s mail.1508.dk -t [email protected] -f [email protected] -h -m body.msg

DEL DatabaseStorageUsage.txt
DEL body.msg
DEL body.txt

echo Done