asked
database size script on SQL Server 2008 R2
I need to run script on set of databases under an instance to find out dbsize, free space etc. Can anyone point me to the right direction please?
This script returns the size, max size and growth for all databases on the instance, including the system databases (to exclude those you can apply the appropriate predicate, for example where database\_id < 5): select database_id, db_name(database_id) database_name, [file_id], type_desc, name logical_name, physical_name physical_path, state_desc, cast(size / 128 as varchar) + ' MB' size, case max_size when - 1 then 'unlimited' else cast(max_size / 128 as varchar) + ' MB' end max_size, case when is_percent_growth = 1 then cast(growth as varchar) + ' percent' else cast(growth / 128 as varchar) + ' MB' end growth from sys.master_files order by database_id, [file_id]; Hope this helps. Oleg
If interested, here is another one, very similar to Oleg's. The only thing different is the free space column. DECLARE @database_id int DECLARE @database_name sysname DECLARE @sql_string nvarchar(2000) DECLARE @file_size TABLE ( [database_name] [sysname] NULL, [groupid] [smallint] NULL, [groupname] sysname NULL, [fileid] [smallint] NULL, [file_size] [decimal](12, 2) NULL, [space_used] [decimal](12, 2) NULL, [free_space] [decimal](12, 2) NULL, [name] [sysname] NOT NULL, [filename] [nvarchar](260) NOT NULL ) SELECT TOP 1 @database_id = database_id ,@database_name = name FROM sys.databases WHERE database_id > 0 ORDER BY database_id WHILE @database_name IS NOT NULL BEGIN SET @sql_string = 'USE ' + QUOTENAME(@database_name) + CHAR(10) SET @sql_string = @sql_string + 'SELECT DB_NAME() ,sysfilegroups.groupid ,sysfilegroups.groupname ,fileid ,convert(decimal(12,2),round(sysfiles.size/128.000,2)) as file_size ,convert(decimal(12,2),round(fileproperty(sysfiles.name,''SpaceUsed'')/128.000,2)) as space_used ,convert(decimal(12,2),round((sysfiles.size-fileproperty(sysfiles.name,''SpaceUsed''))/128.000,2)) as free_space ,sysfiles.name ,sysfiles.filename FROM sys.sysfiles LEFT OUTER JOIN sys.sysfilegroups ON sysfiles.groupid = sysfilegroups.groupid' INSERT INTO @file_size EXEC sp_executesql @sql_string --Grab next database SET @database_name = NULL SELECT TOP 1 @database_id = database_id ,@database_name = name FROM sys.databases WHERE database_id > @database_id ORDER BY database_id END --File Sizes SELECT database_name, groupid, ISNULL(groupname,'TLOG') groupname, fileid, name, file_size, space_used, free_space, filename FROM @file_size
19 People are following this question.
