stored proceduce sp_databases is returning Null value for larger databases

Subodh Agnihotri 21 Reputation points
2021-04-14T15:02:10.503+00:00

Stored Procedure sp_databases returns database size as Null when database size is in Terabytes. Please find below the result of the stored procedure execution in one of my instance where database size is 6TB.

87770-sp-db-issue.png

Developer technologies | Transact-SQL
SQL Server | Other
{count} votes

Accepted answer
  1. Guoxiong 8,206 Reputation points
    2021-04-14T15:24:09.86+00:00

    The data type of DATABASE_SIZE in the output of sp_databases is int. The biggest number of int is 2,147,483,647. If the database size is over 2.15TB, it can not be displayed. That might be the reason why it is NULL.

    1 person found this answer helpful.
    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Tom Phillips 17,771 Reputation points
    2021-04-14T18:23:28.023+00:00
    0 comments No comments

  2. MelissaMa-MSFT 24,221 Reputation points
    2021-04-15T03:19:40.443+00:00

    Hi @Subodh Agnihotri ,

    Welcome to Microsoft Q&A!

    Agreed with Guoxiong, in official document sp_databases as below:

    Column name |Data type |Description
    DATABASE_SIZE |int |Size of database, in kilobytes

    Then the maximum of database size could be shown is 2147483647(KB)/1024/1024
    =2047(GB)~2(TB) which is much smaller than 6TB as you mentioned. So it would show as NULL.

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  3. Subodh Agnihotri 21 Reputation points
    2021-04-16T09:09:18.543+00:00

    Thanks everyone for helping me out here. I extracted the query used by stored procedure and modified it to convert the size to numeric instead of int.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.