Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    32

    Unanswered: Schedule mail of DB sizes

    Wonder if anyone can help me out here.

    I'm trying to set up a job to run overnight that mails me the size of all the databases on SQL Server.

    The way I'm getting the size of the db's is by running the following in SQL Query Analyzer...

    EXEC sp_MSforeachdb @command1="print '?' select cast(name as varchar(32)), round(size * 8 / 1.024,3) from ?..sysfiles"

    Can anyone suggest a way that I can export the results of this to a text file that can then be mailed to me as part of a scheduled job.

    Or can anyone suggest a better/easier way of doing this??

    Thanks in advance.

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    exec master..xp_sendmail @recipients = 'recipients '
    ,@message = 'message'
    ,@query = select cast(name as varchar(128)) "LogicalFileName", (size * 8 / 1024) "Size in MB" from master..sysaltfiles'
    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
    Jan 2004
    Posts
    32

    Thanks, but....

    ... I'm afraid I've not explained myself properly

    SQL Mail is not set up on this server, so I need a file creating which I can then ftp to a location from which it can be mailed.

    And I'm afraid it's not as simple as setting up SQL Mail, it has to be sent from a different box.

    Any suggestions?

    Thanks

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Look into isql or osql to execute the query and store results in a file
    Get yourself a copy of the The Holy Book

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

  5. #5
    Join Date
    Jan 2004
    Location
    Romania - Bucharest
    Posts
    50
    Create an DTS package, two data sources, one as your SQL Serv db, one as "Output Text", then create a "transfer" task between the SQL serv and the text file. Edit the transfer task and set the source as "execute sql" and than select the "exec .... " string you are using now.

    Then schedule your DTS package as you like it...

  6. #6
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    no need for dts here .. simple osql or isql will do the task.
    Get yourself a copy of the The Holy Book

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

  7. #7
    Join Date
    Jan 2004
    Posts
    32
    Thanks Enigma.

    Just looking into osql now and that seems to do the trick.

Posting Permissions

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