January 23, 2019 at 11:11 am
I am trying to get all these scripts into one that shows for all databases but the script 2 and 3 is only shows data for the database you are on within SQL Studio
On Script 1 this is working for all databases.
Script 2 only returns one database, I tried to add the
sys.master_files to join the sys.database_files
but only get the database I run the query on in SQL Studio.
--SCRIPT 1 - System Volume by database
SELECT
DISTINCT vs.volume_mount_point, vs.file_system_type, f.name,
vs.logical_volume_name, CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0) AS [Total Size (GB)],
CONVERT(DECIMAL(18,2),vs.available_bytes/1073741824.0) AS [Available Size (GB)],
CAST(CAST(vs.available_bytes AS FLOAT)/ CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %]
FROM sys.master_files AS f WITH (NOLOCK)
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs
SCRIPT 2 - Database settings
DECLARE @mult float = 8192. / 1024 / 1024 / 1024;
SELECT df.physical_name AS FileName, df.name,
MAX(CAST(df.size AS FLOAT) * 8192 / 1024 / 1024 / 1024) AS Size,
CASE df.max_size
WHEN 0 THEN 'No growth is allowed.'
WHEN-1 THEN 'File will grow until the disk is full'
WHEN 268435456 THEN 'Log file will grow to a maximum size of 2 TB.'
ELSE 'Other'
END AS MaxSizeType,
df.max_size AS MaxSize,
CASE df.growth
WHEN 0
THEN 'File is fixed size and will not grow.'
ELSE 'File will grow automatically.'
END AS GrowthType,
CASE df.is_percent_growth
WHEN 0 THEN 'Percentage growth size'
WHEN 1 THEN 'Absolute growth size'
END AS GrowthIncrementType,
MAX(CAST(df.growth AS FLOAT) * @mult) AS GrowthGB
FROM sys.master_files mst
inner join sys.database_files df on mst.name = df.name
GROUP BY df.physical_name,
df.name,
df.max_size,
df.growth,
df.is_percent_growth
ORDER BY df.physical_name
--SCRIPT 3 - Space within database
INSERT INTO #SpaceUsed
(database_name,
database_size,
unallocated_space,
reserved,
data,
index_size,
unused
)
EXEC sp_spaceused @oneresultset = 1;
SELECT * FROM #SpaceUsed
January 24, 2019 at 12:36 pm
try this:
DECLARE @command varchar(1000)
SELECT @command = 'USE ?
DECLARE @mult float = 8192. / 1024 / 1024 / 1024;
SELECT df.physical_name AS FileName, df.name,
MAX(CAST(df.size AS FLOAT) * 8192 / 1024 / 1024 / 1024) AS Size,
CASE df.max_size
WHEN 0 THEN ''No growth is allowed.''
WHEN-1 THEN ''File will grow until the disk is full''
WHEN 268435456 THEN ''Log file will grow to a maximum size of 2 TB.''
ELSE ''Other''
END AS MaxSizeType,
df.max_size AS MaxSize,
CASE df.growth
WHEN 0
THEN ''File is fixed size and will not grow.''
ELSE ''File will grow automatically.''
END AS GrowthType,
CASE df.is_percent_growth
WHEN 0 THEN ''Percentage growth size''
WHEN 1 THEN ''Absolute growth size''
END AS GrowthIncrementType,
MAX(CAST(df.growth AS FLOAT) * @mult) AS GrowthGB
FROM sys.master_files mst
inner join sys.database_files df on mst.name = df.name
GROUP BY df.physical_name,
df.name,
df.max_size,
df.growth,
df.is_percent_growth
ORDER BY df.physical_name'
EXEC sp_MSforeachdb @command
January 28, 2019 at 4:31 pm
Thank you
January 31, 2019 at 2:43 pm
I can't get this to run, it only does one database only not all the databases.
I did try to put the INSERT at the top before SELECT, but that gave errors
So I just went with the #tmp table
--It just does master only if I am on another database, so it isn't looping.
DECLARE @command varchar(1000)
SELECT @command = 'USE ?
DECLARE @mult float = 8192. / 1024 / 1024 / 1024;
SELECT df.physical_name AS FileName, df.name,
MAX(CAST(df.size AS FLOAT) * 8192 / 1024 / 1024 / 1024) AS Size,
CASE df.max_size
WHEN 0 THEN ''No growth is allowed.''
WHEN-1 THEN ''File will grow until the disk is full''
WHEN 268435456 THEN ''Log file will grow to a maximum size of 2 TB.''
ELSE ''Other''
END AS MaxSizeType,
df.max_size AS MaxSize,
CASE df.growth
WHEN 0
THEN ''File is fixed size and will not grow.''
ELSE ''File will grow automatically.''
END AS GrowthType,
CASE df.is_percent_growth
WHEN 0 THEN ''Percentage growth size''
WHEN 1 THEN ''Absolute growth size''
END AS GrowthIncrementType,
MAX(CAST(df.growth AS FLOAT) * @mult) AS GrowthGB
into #TMP_DBSettings
FROM sys.master_files mst
inner join sys.database_files df on mst.name = df.name
GROUP BY df.physical_name,
df.name,
df.max_size,
df.growth,
df.is_percent_growth
ORDER BY df.physical_name
INSERT INTO [MYDATABASENAME].[dbo].[DBSettings]
([File_Name]
,[Database_File_Name]
,[Size]
,[MaxSizeType]
,[MaxSize]
,[GrowthType]
,[GrowthIncrementType]
,[GrowthGB])
select * from #TMP_DBSettings
drop table #TMP_DBSettings'
EXEC sp_MSforeachdb
@command
---table
CREATE TABLE [dbo].[DBSettings](
[File_Name] [nvarchar](260) NULL,
[Database_File_Name] [sysname] NOT NULL,
[Size] [float] NULL,
[MaxSizeType] [varchar](45) NOT NULL,
[MaxSize] [int] NOT NULL,
[GrowthType] [varchar](37) NOT NULL,
[GrowthIncrementType] [varchar](22) NULL,
[GrowthGB] [float] NULL
) ON [PRIMARY]
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply