MSSQL database size statistics method

We often need to know the size of all databases (or some databases) on one server. It not only allows us to know the proportion of the current database disk used, but also regularly collects such information, you can also learn about the data increment of the database for a period of time. More often, you can easily plan the disk capacity of the new server during data migration and upgrade. Here we will introduce three methods to measure the database size:

Method 1: Use sp_spaceused

declare @dbname varchar(100) declare db_cur cursor forselect name from sys.sysdatabases where dbid>4 and name<>'distribution'open db_curfetch next from db_cur  into @dbnamewhile @@FETCH_STATUS=0 begin  print('use '+QUOTENAME(@dbname))  Print('go')  Print('sp_spaceused')  fetch next from db_cur  into @dbname endclose db_curdeallocate db_cur

Note: We have performed preliminary data filtering and removed the system database and distribution database. This method generates the following script:

use [ReportServer]gosp_spaceuseduse [ReportServerTempDB]gosp_spaceuseduse [DataCache]gosp_spaceuseduse [MessageCenter]gosp_spaceuseduse [ABS]gosp_spaceuseduse [dbcenter]gosp_spaceuseduse [MDW]gosp_spaceused

Run the command directly to obtain the statistics about the database size:

The data we need is the hook information (with database_name). copy the data and put it in Excel for convenient statistics.

We can see that the database size is 363 mb. This method is troublesome, but it is a good method for databases of versions earlier than 2000 and 2000 (No DMV );

Method 2: Combine SYS. database_files and sp_msforeachdb

-- Count the size of some databases in an instance: Use mastergocreate table dbsize (dbname varchar (100), size INT) -- Insert the Database Name and size into the database table exec sp_msforeachdb "insert into master. DBO. dbsize select '? 'As dbname, sum (size) as size from ?. SYS. database_files"

Note: We use sp_msforeachdb to query the SYS. database_files view in each database to calculate the file size. The obtained results are put into a table,

Then use this table for statistics;

Select * from master. DBO. dbsize -- calculate the total size of the filtered database select sum (size) * 8/1024 as dbsize from dbsize where dbname not in ('master', 'tempdb', 'model', 'mdb ', 'stribucket ')

As shown in, the size of the collected database is also 363 MB.

Method 3: Combine SYS. sysaltfiles with sys. Databases

--db file sizeselect db.name,sf.name,sf.filename,sf.size from sys.sysaltfiles sf inner join sys.databases db on sf.dbid=db.database_id where db.database_id>4 and db.name <>'distribution'--dbsizeselect SUM(size)*8/1024 from sys.sysaltfiles sf inner join sys.databases db on sf.dbid=db.database_id where db.database_id>4 and db.name <>'distribution'

This method is one-time, and the size of the database is also 363 MB, which is more convenient.