Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2003
    Posts
    26

    Unanswered: 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 Jain

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Do you have SQL Mail configured on your server
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Jun 2003
    Posts
    26
    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

  4. #4
    Join Date
    Jun 2003
    Posts
    26
    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 .

  5. #5
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    http://support.microsoft.com/default...312839&sd=tech for information about mail without using SQLMail.

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

    HTH
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  6. #6
    Join Date
    Jun 2003
    Posts
    26
    But what about reporting of database and transaction log files.. Can you help

  7. #7
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    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
    ---
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  8. #8
    Join Date
    Jun 2003
    Posts
    26
    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

  9. #9
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    ONce you create the given SP, save the results to the text file and attach the same to mail.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  10. #10
    Join Date
    Jun 2003
    Posts
    26

    Wink

    How can i save results in Text file ??

  11. #11
    Join Date
    Jun 2003
    Posts
    26
    Can anyone help ?

  12. #12
    Join Date
    Jun 2003
    Posts
    26
    Satya .. U r my friend .. I know you'll help me out for this

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •