SQL Server System Report

  • My pleasure. If there's anything else just let me know. 🙂

  • Joe Zonum (5/6/2014)

    It didn't work..

    It still sent an email but the email was blank because no jobs failed.

    I hoping to avoid blank emails when no jobs fail.

    Was this the only change you made?

    I am trying to learn how to be a better tsql coder.

  • That and I moved this to the end:


    IF OBJECT_ID (N'tempdb.dbo.#temp_sssr_agent_jobs', N'U') IS NOT NULL


    DROP TABLE dbo.#temp_sssr_agent_jobs



    Here are two great books if you want:




  • Thank you Old Hand... I'll take a look at the books.


  • They are really great. The Sam's one gave me my start many years ago, the other showed me a lot of great advanced stuff.

  • Latest version with a bunch of changes now available. 🙂

  • Hi,

    thanks for sharing this procedure. I tried this on one of our servers and wondering about the reserved_mb metric in the fourth recordset about databases.

    In this resultset there are several databases where the reserved_mb is greater than the total_size_mb? how can this be?


    total_size_mb: 8605

    unallocated_mb: 0

    reserved_mb: 12103

    data_mb: 3692

    index_mb: 1839

    unused_mb: 6573

    cached_mb: 1222



    Today is a good day to sql

  • Unfortunately, without having access to your environment I cannot say for sure. What does it show if you run this on the database in question:

    EXEC sp_spaceused

  • thanks for your quick reply.

    here is the output of the sp_spaceused proc.

    database_size unallocated space

    ------------------ ------------------

    8605.13 MB 0.00 MB

    reserved data index_size unused

    ------------------ ------------------ ------------------ ------------------

    12400080 KB 3780312 KB 1888000 KB 6731768 KB

    Today is a good day to sql

  • Sounds weird. If you look at the BOL entry for sp_space_used in Remarks it states: "database_size will always be larger than the sum of reserved + unallocated space because it includes the size of log files, but reserved and unallocated_space consider only data pages."

    Try running this satement in that database: EXEC sp_spaceused @updateusage = N'TRUE';


  • Thanks Lee. 🙂

  • executed

    EXEC sp_spaceused @updateusage = N'TRUE';

    and now the result looks much better

    database_size unallocated space

    ------------------ ------------------

    8605.13 MB 199.59 MB

    reserved data index_size unused

    ------------------ ------------------ ------------------ ------------------

    6207264 KB 3789840 KB 1860992 KB 556432 KB

    turns out to be a good day today, because I have learned something 🙂

    thanks lee.

    @sean: maybe it it makes sense to use the updateuseage option in your procedure...

    Today is a good day to sql

  • That looks much better. I would disagree though about including the update usage option in Sean's code. Again from BOL: "updateusage can take some time to run on large tables or databases. Use updateusage only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database. "


  • I would have to agree with Lee on this one, just because of pressure it could put on the server.

  • Sean

    I've been using your report quite happily for a while now on a SQL 2008R2 server but it just started failing with this error:

    There is insufficient result space to convert a money value to varchar. [SQLSTATE 22003] (Error 234). The step failed.

    Can you help?

Viewing 15 posts - 166 through 180 (of 189 total)

You must be logged in to reply to this topic. Login to reply