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. WideWorldImporters WWI_UserData 2048 3172 Result: Database File Size (MB) Database Total SUM(m.size * 8/1024) OVER (PARTITION BY d.name) AS 'Database Total', You could use the OVER clause to do exactly that. So what if you want to see both the size of each individual file, and the total of all files for each database? 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 – they don’t list out each individual file along with its size. This could be seen as a positive or a negative depending on what you want to achieve. One potential issue with the previous two examples is that they list out the size of each file separately. We can use this view to return the same info as the previous example: USE WideWorldImporters There’s also a system view called sys.database_files. You’ll also notice that I perform a calculation on the size column to convert the value into megabytes (MB). In this case we can see the size of each data file and log file, as they’re listed separately. WHERE DB_NAME(database_id) = 'WideWorldImporters' So an alternative is to go straight to the view and cherry pick your columns: SELECT The above stored procedure queries the sys.master_files view. WorldData 16384 null The sys.master_files View Result: DATABASE_NAME DATABASE_SIZE REMARKS
SQL SERVER DATABASE FILES HOW TO
Here’s how to execute it: EXEC sp_databases This stored procedure lists databases that either reside in an instance of the SQL Server or are accessible through a database gateway. Yet another option is the sp_databases system stored procedure. If we do this, it will return information on all databases in the sys.databases catalog view. We can also call sp_helpdb without providing an argument. In this case, we pass the name of the database as an argument. WWI_UserData 3 /data/WWI_UD.ndf USERDATA 2097152 KB Unlimited 65536 KB data only WWI_Primary 1 /data/WWI.mdf PRIMARY 1048576 KB Unlimited 65536 KB data only Result: name fileid filename filegroup size maxsize growth usage Here’s an example of calling that: EXEC sp_helpdb N'WideWorldImporters' The sp_helpdb Stored ProcedureĪnother system stored procedure is sp_helpdb. In this example we return information about the Cities table only. Result: name rows reserved data index_size unusedĬities 37940 4880 KB 3960 KB 896 KB 24 KB In this case, only one result set will be returned. You can also provide an object name to return data on a specific object within the database. This returns two result sets that provide the relevant information. Result: database_name database_size unallocated space To use it, simply switch to the relevant database and execute the procedure. 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. This article presents six ways to check the size of a SQL Server database using T-SQL. However, if you prefer to use T-SQL to manage your databases, you’ll need to run a query that returns this information. If you’re using a GUI tool, such as SSMS to 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 Reports, then Standard Reports, and then click Disk Usage).
0 kommentar(er)
