Post reply

Monitoring Stored Procedure Space

  • February 22, 2008 at 5:40 am

    #127988

    Maybe I'm just missing the obvious, but how can I monitor disk space used by syscomments? sp_spaceused does not recognize syscomments and I can't so far find any procedure that shows the space used by stored procedures in a user database.

  • February 22, 2008 at 6:21 am

    #781062

    Hi,

    If you want to know how big sys.syscomments is, you need to calculate row size and multiply by # of rows. It shouldn't be much, so I wouldn't worry to much.

    Thanks,

    Phillip Cox

  • February 22, 2008 at 7:38 am

    #781100

    This may give you what your looking for:

    exec sp_spaceused syscomments

    and will yield:

    name rows reserved data index unsued

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

    syscomments 187 264 KB 192 KB 16 KB56 KB

    -- You can't be late until you show up.

  • February 22, 2008 at 8:11 am

    #781119

    Hi,

    Thanks for the responses. When I attempt to exec sp_spaceused syscomments in SSMS 2005 I get the following no matter what DB I use:

    Msg 15009, Level 16, State 1, Procedure sp_spaceused, Line 62

    The object 'syscomments' does not exist in database 'AdventureWorks' or is invalid for this operation.

    What am I missing?

Viewing 4 posts - 1 through 3 (of 3 total)

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