To do a project, the first thing to ask is:"How big is this database?" "
Here's how to count the data size of the database
Usually we will use the command: "sp_helpdb @dbname"
For example, querying the usage of the database "TESTDB3"
EXEC sp_helpdb TESTDB3
"Sp_helpdb" is the most commonly used command,
However, note that the database size "db_size" displayed by this command does not refer to the size of the existing valid data , but rather: the sum of the database physical file "data file size + log file size"
For example, in the example above, the database "TESTDB3" corresponds to the data file and the log file:
218.112 + 916.352 = 1107.88MB
At the beginning of the database, the initial size of the data files and log files is the default 2M or 3M
When data is inserted, the size of the data file and log file grows as the amount of data grows
But what happens when the data is deleted?
Will the data files and log files be smaller? No, or keep the original size.
How do I get the size of a valid data?
The answer is to use the "sp_spaceused" stored procedure to count the amount of data per table separately
1) Statistics The use of a single table space
exec sp_spaceused ' dbo.t1 '
2) to count the space used for each table
exec sp_msforeachtable "exec sp_spaceused '?"
SQL Server Statistics data volume