hello,
thank you for the reply i have posted a request to verify account so i can upload a picture which will help clearify my issue.
i have a query for diskspace monitoring for several customers
DECLARE @SQL VARCHAR(8000), @sname VARCHAR(3)
SELECT @sname=CONVERT(VARCHAR(MAX),SERVERPROPERTY('PRODUCTVERSION'))
SELECT @sname=CONVERT(TINYINT,SUBSTRING(@sname,1,CHARINDEX('.',@sname)-1))
IF @sname=8
BEGIN
SET @SQL='USE [?]
SELECT ''[?]'' [Dbname]
,[name] [Filename]
,CASE STATUS & 0x40 WHEN 0x40 THEN ''LOG'' ELSE ''ROWS'' END [Type]
,filename [FilePath]
,size/128.0 AS [TotalSize_MB]
,CONVERT(INT,FILEPROPERTY(name, ''SpaceUsed''))/128.0 [Space_Used_MB]
,CASE STATUS & 0x100000 WHEN 0x100000 THEN convert(NVARCHAR(3), growth) + ''%''
ELSE CONVERT(NVARCHAR(15), (growth * 8)/1024) + '' MB'' END [Autogrow_Value]
,CASE maxsize WHEN -1 THEN CASE growth WHEN 0 THEN ''Restricted'' ELSE N''Unlimited'' END
ELSE CONVERT(NVARCHAR(15), (maxsize * 8)/1024) + '' MB'' END [Max_Size]
FROM [?].dbo.sysfiles'
END
ELSE
BEGIN
SET @SQL=' USE [?]
SELECT ''[?]'' [Dbname]
,[name] [Filename]
,type_desc [Type]
,physical_name [FilePath]
,CONVERT(INT,[size]/128.0) [TotalSize_MB]
,CONVERT(INT,FILEPROPERTY(name, ''SpaceUsed''))/128.0 AS [Space_Used_MB]
,CASE is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR(5),growth)+''%''
ELSE CONVERT(VARCHAR(20),(growth/128))+'' MB''
END [Autogrow_Value]
,CASE max_size
WHEN -1 THEN CASE growth
WHEN 0 THEN CONVERT(VARCHAR(30),''Restricted'')
ELSE CONVERT(VARCHAR(30),''Unlimited'') END
ELSE CONVERT(VARCHAR(25),max_size/128)
END [Max_Size]
FROM [?].sys.database_files'
END
IF EXISTS(SELECT 1 FROM tempdb..sysobjects WHERE name='##Fdetails')
DROP TABLE ##Fdetails
CREATE TABLE ##Fdetails (Dbname VARCHAR(MAX),Filename VARCHAR(MAX),Type VARCHAR(10),Filepath
VARCHAR(MAX)
,TotalSizeDBF_MB INT,UsedSpaceDBF_MB INT,Autogrow_Value VARCHAR(15),Max_Size VARCHAR(30))
INSERT INTO ##Fdetails
EXEC sp_MSforeachdb @SQL exec sp_executesql @stmt=N'
begin try
declare @filestats_temp_table table(
file_id int
, file_group_id int
, total_extents int
, used_extents int
, logical_file_name nvarchar(500) collate database_default
, physical_file_name nvarchar(500) collate database_default
);
insert into @filestats_temp_table
exec sp_msforeachdb ''USE [?] exec (''''DBCC SHOWFILESTATS'''')''
SELECT
(row_number() over (order by t2.name))%2 as l1
,
t2.name as [file_group_name]
, t1.logical_file_name
, t1.physical_file_name
-- nieuw
, t3.Type
, t3.Dbname
, t3.Autogrow_Value
, t3.Max_Size
-- einde nieuw
, CAST( cast(case when (total_extents * 64) < 1024 then (total_extents *
64)
when (total_extents * 64 / 1024.0) < 1024 then (total_extents * 64 / 1024.0)
else (total_extents * 64 / 1048576.0)
end as decimal(10,2)) as varchar(20)) + case when (total_extents * 64) < 1024
then '' KB''
when (total_extents * 64 / 1024.0) < 1024 then '' MB''
else '' GB''
end as space_reserved
,
CAST( cast(case when (used_extents * 64) < 1024 then
(used_extents * 64)
when (used_extents * 64 / 1024.0) < 1024 then (used_extents * 64 / 1024.0)
else (used_extents * 64 / 1048576.0)
end as decimal(10,2)) as varchar(20)) + case when (used_extents * 64) < 1024 then
'' KB''
when (used_extents * 64 / 1024.0) < 1024 then '' MB''
else '' GB''
end as space_used
from @filestats_temp_table t1
inner join sys.data_spaces t2 ON t1.file_Group_id =t2.data_space_id
inner join ##Fdetails t3 on t3.Filepath = t1.physical_file_name
end try
begin catch
select -100 as l1
, ERROR_NUMBER() as file_group_name
, ERROR_SEVERITY() as logical_file_name
, ERROR_STATE() as physical_file_name
, ERROR_MESSAGE() as space_reserved
, 1 as space_reserved_unit, 1 as space_used, 1 as space_used_unit
end catch ;
',@params=N''
--these are the results from the query
| PRIMARY |
tempdev |
J:\tempdb.mdf |
ROWS |
[tempdb] |
10% |
Unlimited |
5.73 GB |
5.19 MB |
| PRIMARY |
modeldev |
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\model.mdf |
ROWS |
[model] |
100 MB |
Unlimited |
2.25 MB |
1.31 MB |
| PRIMARY |
MSDBData |
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf |
ROWS |
[msdb] |
10% |
Unlimited |
364.19 MB |
317.00 MB |
when i put the columns into their respective "server name AXIS,dbname AXIS, space_used/space_free into VALUES"
the space_used and space_free value change from 5.19MB to something entirely else, same for all my values.
i use Excel 2013 and SQL Server management 2008 2012 and 2014
as soon as my account is verified i upload a screenshot to help me clearify it.
please wait
Regards,
Enma
-
Edited by
Enma_Ai2012
5 minutes ago
Other recent topics
