Determining database size using T-SQL scripting

There are a few ways to determine the size of databases within SQL Server without needing to right click the DB and select the Properties option.

We can use the classic T-SQL query directly from the SQL Server Management Studio to display all DBs as well as their sizes.

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files

Add a “Where” statement filter at the end to retrieve only one DB.

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = ‘AdventureWorks2012’

There is also another method if you have no access to the Management Studio or just prefer the Command line. You can use the SQLCMD command (\Program FilesMicrosoft SQL Server110ToolsBinn>).

The command tool can get quite complex depending on the task you want to perform, but in this case we will be simply calling up a query file and exporting the results to a text file.

The SQLCMD tool can be used to get information from a SQL server in a verbose way without accessing the GUI.

To start, one can simply go to the path mentioned above and type the command in the Command Prompt window (sqlcmd -S localhost -i C:ScriptsDBsize.sql).You must have a SQL file ready with a query beforehand in order to get the information. When you write the command you have to mention the server name (-S), UserName and Password, and the path of the file (-i).

As soon as you press enter, the results will display on the screen. This works fine if you want a quick visual, but if you want a physical copy of the results, add the name of the file you want the results to be saved in. This can be added to the end of the command using the (-o) switch and the path:

sqlcmd -S localhost -i C:ScriptsDBsize.sql -o C:ScriptsResults.txt