Daily Reporting of Database and its Transaction Log Files.

Hello -

I have a database server with over 300 databases. I want that MS-SQL Server should daily report me the sizes of SQL databases along with Transaction log files by sending me an email on my address.

How can I do that. Does someone have any script which can help me to do that.

Any help will be appreciated.

Kind Regards,

Rubal JainDo you have SQL Mail configured on your server|||What is SQL Mail .. I m not very sure about it. How to installed and use that. How should I implement daily reporting.

Please help me out.

Thanks|||If you were asking abt CDO and CDONTS .. I have both installed on the server.

Is there any script available which can help me out .|||http://support.microsoft.com/default.aspx?scid=kb;de;312839&sd=tech for information about mail without using SQLMail.

http://www.searchdatabase.com/tip/1,289483,sid13_gci826453,00.html - further information.

HTH|||But what about reporting of database and transaction log files.. Can you help|||You can find out how much space a database is occupying on the hard disk by using the sp_spaceused function. However, if you want to find all database sizes at once, you have to use sp_spaceused for all databases.
USe this script:
--
CREATE PROCEDURE Usp_FindAllDBSizes
AS
SET NOCOUNT ON
DECLARE @.counter SMALLINT
DECLARE @.counter1 SMALLINT
DECLARE @.dbname VARCHAR(100)
DECLARE @.size INT
DECLARE @.size1 DECIMAL(15,2)
SET @.size1=0.0

SELECT @.counter=MAX(dbid) FROM master..sysdatabases
IF EXISTS(SELECT name FROM sysobjects WHERE name='sizeinfo')
DROP TABLE sizeinfo
CREATE TABLE sizeinfo(fileid SMALLINT, filesize DECIMAL(15,2), filename VARCHAR(1000))
WHILE @.counter > 0
BEGIN
SELECT @.dbname=name FROM master..sysdatabases WHERE dbid=@.counter
TRUNCATE TABLE sizeinfo
EXEC ('INSERT INTO sizeinfo SELECT fileid,size,filename FROM '+ @.dbname +'..SYSFILES')
SELECT @.counter1=MAX(fileid) FROM sizeinfo
WHILE @.counter1>0
BEGIN
SELECT @.size=filesize FROM sizeinfo WHERE fileid=@.counter1
SET @.size1=@.size1+@.size
SET @.counter1=@.counter1-1
END
SET @.counter=@.counter-1
SELECT @.dbname AS DBNAME,CAST(((@.size1)*0.0078125) AS DECIMAL(15,2)) AS [DBSIZE(MB)]
SET @.size1=0.0
END
SET NOCOUNT OFF
--|||Hey Satya Thanks ..

Can you integrate it with CDONTS so it can send me daily emails ??

Your Help will be really appreciated.

Kind Regards,

Rubal|||ONce you create the given SP, save the results to the text file and attach the same to mail.|||How can i save results in Text file ??|||Can anyone help ?|||Satya .. U r my friend .. I know you'll help me out for this ;)