:::: MENU ::::

Posts Categorized / SQL Server Filegroups

  • Mar 18 / 2015
  • 0
dbDigger, Monitoring and Analysis, SQL Server Filegroups

Get size of all databases on a server

Following are three scripts to get

  • Get size of all databases on a server
  • Get size of all databases in terms of data and log
  • Get size of all data and log files

Get Size of all databases

SELECT DB_NAME(database_id) AS DatabaseName,
cast(sum( size* 8.0 / 1024)/1024 as DECIMAL(18,3)) [Size(GB)]
FROM sys.master_files
GROUP BY database_id
ORDER BY database_id

Get Size of all database in data and log category

SELECT DB_NAME(database_id) AS DatabaseName, type_desc,
cast(sum( size* 8.0 / 1024)/1024 as DECIMAL(18,3)) [Size(GB)]
FROM sys.master_files
GROUP BY database_id, type_desc
ORDER BY database_id, type_desc DESC 

— Get Size of all data and log files

SELECT DB_NAME(database_id) AS DatabaseName, type_desc, name, physical_name,
cast( (size* 8.0 / 1024)/1024 as DECIMAL(18,3)) [Size(GB)]
FROM sys.master_files
WHERE DB_NAME(database_id) = 'DBToFiletr'
ORDER BY database_id, type_desc DESC, name 
  • Apr 11 / 2012
  • 0
DBA best practices, dbDigger, Performance Tunning and Optimization, Publications of Atif Shehzad on MSSQLTips.com, SQL Server Filegroups

Configuring the default filegroup for a database with multiple filegroups

By default a single file group exists for SQL Server databases.  It is primary and default file group for that database. However for better manageability and performance optimization we may be required to configure additional file groups for particular database. Data may be spread over multiple files in different file groups. SQL Server provides an option to change the default file group from primary file group to any other. Read here my article related to Using Multiple File groups for a Database and Changing the Default File group.

Consult us to explore the Databases. Contact us