question

SubodhAgnihotri-3576 avatar image
0 Votes"
SubodhAgnihotri-3576 asked SubodhAgnihotri-3576 answered

stored proceduce sp_databases is returning Null value for larger databases

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


sql-server-generalsql-server-transact-sql
sp-db-issue.png (57.4 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Since this is expected for databases over 2GB, I created a documentation pull request to include this behavior in the remarks section.

1 Vote 1 ·
GuoxiongYuan-7218 avatar image
1 Vote"
GuoxiongYuan-7218 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @SubodhAgnihotri-3576,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SubodhAgnihotri-3576 avatar image
0 Votes"
SubodhAgnihotri-3576 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.