March 17, 2014 at 10:03 am
i need to find the total size , free and used space of all the databases in sql2008\2005
March 17, 2014 at 10:17 am
Not sure where I got this:
-----------------------------------------------------------------------------------------
-- Database free space
-----------------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..##DatabaseSize') IS NOT NULL DROP TABLE ##DatabaseSize;
CREATE TABLE ##DatabaseSize
(
DatabaseName sysname,
Name sysname,
physical_name nvarchar(260),
FileType varchar(60),
size Numeric(18,3),
FreeSpace Numeric(18,3),
Growth Numeric(18,3),
IsPercent BIT
)
Exec sp_msforeachdb '
IF ''?'' IN (''master'', ''tempdb'', ''model'', ''msdb'',
''ReportServer$SQLADMIN01'',''ReportServer$SQLADMIN01TempDB'') RETURN;
USE [?];
INSERT INTO ##DatabaseSize (DatabaseName, Name, physical_name, Size, FreeSpace, growth, IsPercent)
SELECT
DB_NAME() AS [DatabaseName]
, name
, physical_name
, Type_Desc
, size * 8.0/1024.0
, (size * 8.0/1024.0 ) - (FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0)
, CASE WHEN is_percent_growth = 1 THEN growth
ELSE growth * 8.0/1024.0
END AS Growth
, is_percent_growth
FROM sys.database_files; '
SELECT * FROM ##DatabaseSize ORDER BY 1, 2
I really need to start saving the links.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply