{"id":3127,"date":"2021-03-16T17:35:29","date_gmt":"2021-03-17T00:35:29","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3127"},"modified":"2021-03-16T17:35:31","modified_gmt":"2021-03-17T00:35:31","slug":"6-ways-to-check-the-size-of-a-database-in-sql-server-using-t-sql","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3127","title":{"rendered":"6 Ways to Check the Size of a Database in SQL Server using T-SQL"},"content":{"rendered":"\n

If you\u2019re using a GUI tool, such as\u00a0SSMS\u00a0to manage your databases, you can easily check the size of your database by clicking your way through the GUI (right-click the database, point to\u00a0Reports<\/strong>, then\u00a0Standard Reports,<\/strong>\u00a0and then click\u00a0Disk Usage<\/strong>).<\/p>\n\n\n\n

However, if you prefer to use\u00a0T-SQL\u00a0to manage your databases, you\u2019ll need to run a query that returns this information.<\/p>\n\n\n\n

This article presents six ways to check the size of a SQL Server database using T-SQL.<\/p>\n\n\n\n

The sp_spaceused Stored Procedure<\/h3>\n\n\n\n

This is a system stored procedure that displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.<\/p>\n\n\n\n

To use it, simply switch to the relevant database and execute the procedure. Like this:<\/p>\n\n\n\n

USE WideWorldImporters;\nEXEC sp_spaceused;<\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
database_name       database_size  unallocated space\n------------------  -------------  -----------------\nWideWorldImporters  3172.00 MB     2511.76 MB       \n\n1 row(s) returned\n\nreserved   data       index_size  unused \n---------  ---------  ----------  -------\n573688 KB  461728 KB  104120 KB   7840 KB\n\n1 row(s) returned<\/pre>\n\n\n\n
This returns two result sets that provide the relevant information.<\/p>\n\n\n\n
You can also provide an object name to return data on a specific object within the database. In this case, only one result set will be returned.<\/p>\n\n\n\n
Example:<\/p>\n\n\n\n
USE WideWorldImporters;\nEXEC sp_spaceused N'Application.Cities';<\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
name    rows                  reserved  data     index_size  unused\n------  --------------------  --------  -------  ----------  ------\nCities  37940                 4880 KB   3960 KB  896 KB      24 KB<\/pre>\n\n\n\n
In this example we return information about the Cities<\/code> table only.<\/p>\n\n\n\n
The sp_helpdb Stored Procedure<\/h3>\n\n\n\n
Another system stored procedure is sp_helpdb<\/code>.<\/p>\n\n\n\n
Here\u2019s an example of calling that:<\/p>\n\n\n\n
EXEC sp_helpdb N'WideWorldImporters';<\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
name          fileid  filename          filegroup  size        maxsize        growth    usage    \n------------  ------  ----------------  ---------  ----------  -------------  --------  ---------\nWWI_Primary   1       \/data\/WWI.mdf     PRIMARY    1048576 KB  Unlimited      65536 KB  data only\nWWI_Log       2       \/data\/WWI.ldf     null       102400 KB   2147483648 KB  65536 KB  log only \nWWI_UserData  3       \/data\/WWI_UD.ndf  USERDATA   2097152 KB  Unlimited      65536 KB  data only\n<\/pre>\n\n\n\n
In this case, we pass the name of the database as an argument. We can also call sp_helpdb<\/code> without providing an argument. If we do this, it will return information on all databases in the sys.databases<\/code> catalog view.<\/p>\n\n\n\n
The sp_databases Stored Procedure<\/h3>\n\n\n\n
Yet another option is the sp_databases<\/code> system stored procedure. This stored procedure lists databases that either reside in an instance of the SQL Server or are accessible through a database gateway.<\/p>\n\n\n\n
Here\u2019s how to execute it:<\/p>\n\n\n\n
EXEC sp_databases;<\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
DATABASE_NAME       DATABASE_SIZE  REMARKS\n------------------  -------------  -------\nmaster              6848           null   \nmodel               16384          null   \nmsdb                15616          null   \nMusic               16384          null   \nNature              16384          null   \nSolutions           47104          null   \ntempdb              16384          null   \nTest                16384          null   \nWideWorldImporters  3248128        null   \nworld               16384          null   \nWorldData           16384          null<\/pre>\n\n\n\n
The sys.master_files View<\/h3>\n\n\n\n
The above stored procedure queries the sys.master_files<\/code> view. So an alternative is to go straight to the view and cherry pick your columns:<\/p>\n\n\n\n
SELECT\n    name,\n    size,\n    size * 8\/1024 'Size (MB)',\n    max_size\nFROM sys.master_files\nWHERE DB_NAME(database_id) = 'WideWorldImporters';\n<\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
name          size    Size (MB)  max_size \n------------  ------  ---------  ---------\nWWI_Primary   131072  1024       -1       \nWWI_Log       12800   100        268435456\nWWI_UserData  262144  2048       -1       \n<\/pre>\n\n\n\n
In this case we can see the size of each data file and log file, as they\u2019re listed separately. You\u2019ll also notice that I perform a calculation on the size<\/code> column to convert the value into megabytes (MB).<\/p>\n\n\n\n
The sys.database_files View<\/h3>\n\n\n\n
There\u2019s also a system view called sys.database_files<\/code>. We can use this view to return the same info as the previous example:<\/p>\n\n\n\n
USE WideWorldImporters;\nSELECT\n    name,\n    size,\n    size * 8\/1024 'Size (MB)',\n    max_size\nFROM sys.database_files;\n<\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
name          size    Size (MB)  max_size \n------------  ------  ---------  ---------\nWWI_Primary   131072  1024       -1       \nWWI_Log       12800   100        268435456\nWWI_UserData  262144  2048       -1       \n<\/pre>\n\n\n\n
Use a Window Function<\/h3>\n\n\n\n
One potential issue with the previous two examples is that they list out the size of each file separately. This could be seen as a positive or a negative depending on what you want to achieve.<\/p>\n\n\n\n
It could also be argued that the first three solutions on this page are problematic, because they only provide the sum total of all files \u2013 they don\u2019t list out each individual file along with its size.<\/p>\n\n\n\n
So what if you want to see both the size of each individual file, and<\/em> the total of all files for each database?<\/p>\n\n\n\n
You could use the OVER<\/code> clause to do exactly that.<\/p>\n\n\n\n
Here\u2019s an example:<\/p>\n\n\n\n
SELECT\n    d.name AS 'Database',\n    m.name AS 'File',\n    m.size,\n    m.size * 8\/1024 'Size (MB)',\n    SUM(m.size * 8\/1024) OVER (PARTITION BY d.name) AS 'Database Total',\n    m.max_size\nFROM sys.master_files m\nINNER JOIN sys.databases d ON\nd.database_id = m.database_id;<\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Database            File             Size (MB)  Database Total\n------------------  ---------------  ---------  --------------\nmaster              master           4          6             \nmaster              mastlog          2          6             \nmodel               modeldev         8          16            \nmodel               modellog         8          16            \nmsdb                MSDBData         14         14            \nmsdb                MSDBLog          0          14            \nMusic               Music            8          16            \nMusic               Music_log        8          16            \nNature              Nature           8          16            \nNature              Nature_log       8          16            \nSolutions           Solutions        8          46            \nSolutions           Solutions_log    8          46            \nSolutions           Solutions_dat_2  10         46            \nSolutions           Solutions_dat_3  10         46            \nSolutions           Solutions_log_2  10         46            \ntempdb              tempdev          8          16            \ntempdb              templog          8          16            \nWideWorldImporters  WWI_Primary      1024       3172          \nWideWorldImporters  WWI_Log          100        3172          \nWideWorldImporters  WWI_UserData     2048       3172          \nworld               world            8          16            \nworld               world_log        8          16       \n<\/pre>\n\n\n\n
This lists out each database, the files for each database, the file size for each file, as well as the total of all files for each database. This requires that each database (and their total size) is listed multiple times (once for each file).<\/p>\n\n\n\n
Ref: https:\/\/database.guide\/6-ways-to-check-the-size-of-a-database-in-sql-server-using-t-sql\/<\/p>\n","protected":false},"excerpt":{"rendered":"
If you\u2019re using a GUI tool, such as\u00a0SSMS\u00a0to manage your databases, you can easily check the size of your database by clicking your way through the GUI (right-click the database, point to\u00a0Reports, then\u00a0Standard Reports,\u00a0and then click\u00a0Disk Usage). However, if you prefer to use\u00a0T-SQL\u00a0to manage your databases, you\u2019ll need to run a query that returns this Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,625],"tags":[641],"class_list":["post-3127","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-sql","tag-check-size-of-a-sql-database"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3127","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3127"}],"version-history":[{"count":1,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3127\/revisions"}],"predecessor-version":[{"id":3128,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3127\/revisions\/3128"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3127"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3127"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3127"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}