Vijay
asked on
T-SQL modification
Hi Team,
How to modify below t-sql to run on all databases in sql server 2016?
How to modify below t-sql to run on all databases in sql server 2016?
select
DBName,
name,
[filename],
size as 'Size(MB)',
usedspace as 'UsedSpace(MB)',
(size - usedspace) as 'AvailableFreeSpace(MB)'
from
(
SELECT
db_name(s.database_id) as DBName,
s.name AS [Name],
s.physical_name AS [FileName],
(s.size * CONVERT(float,8))/1024 AS [Size],
(CAST(CASE s.type WHEN 2 THEN 0 ELSE CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)* CONVERT(float,8) END AS float))/1024 AS [UsedSpace],
s.file_id AS [ID]
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON ((s.type = 2 or s.type = 0) and s.database_id = db_id() and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
) DBFileSizeInfoASKER
Yes. I having admin rights.
OK, the above query does not provide complete info for all databases. You may look here for much better script.
There is a beautiful script written by Microsoft for both DATA AND LOG FILES. Please use that-
FOR DATA FILES
OUTPUT
FOR LOG FILE
OUTPUT
FOR DATA FILES
------------------------------Data file size----------------------------
if exists (select * from tempdb.sys.all_objects where name like '%#dbsize%')
drop table #dbsize
create table #dbsize
(Dbname sysname,dbstatus varchar(50),Recovery_Model varchar(40) default ('NA'), file_Size_MB decimal(30,2)default (0),Space_Used_MB decimal(30,2)default (0),Free_Space_MB decimal(30,2) default (0))
go
insert into #dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB)
exec sp_msforeachdb
'use [?];
select DB_NAME() AS DbName,
CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) ,
CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')),
sum(size)/128.0 AS File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB
from sys.database_files where type=0 group by type'
SELECT * FROM #dbsizeOUTPUT
/*------------------------
OUTPUT
------------------------*/
(10 row(s) affected)
Dbname dbstatus Recovery_Model file_Size_MB Space_Used_MB Free_Space_MB
-------------------------------- -------------- ---------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
master ONLINE SIMPLE 8.00 4.13 3.88
tempdb ONLINE SIMPLE 64.00 4.69 59.31
model ONLINE FULL 8.00 2.31 5.69
msdb ONLINE SIMPLE 14.81 14.00 0.81
ReportServer$MSSQL2016 ONLINE FULL 8.00 6.50 1.50
ReportServer$MSSQL2016TempDB ONLINE SIMPLE 8.00 3.69 4.31
DWDiagnostics ONLINE SIMPLE 1000.00 84.75 915.25
DWConfiguration ONLINE FULL 8.00 4.06 3.94
DWQueue ONLINE SIMPLE 8.00 2.81 5.19
Pawan ONLINE FULL 8.00 3.13 4.88
(10 row(s) affected)FOR LOG FILE
-------------------log size--------------------------------------
if exists (select * from tempdb.sys.all_objects where name like '#logsize%')
drop table #logsize
create table #logsize
(Dbname sysname, Log_File_Size_MB decimal(38,2)default (0),log_Space_Used_MB decimal(30,2)default (0),log_Free_Space_MB decimal(30,2)default (0))
go
insert into #logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB)
exec sp_msforeachdb
'use [?];
select DB_NAME() AS DbName,
sum(size)/128.0 AS Log_File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB
from sys.database_files where type=1 group by type'
SELECT * FROM #logsizeOUTPUT
/*------------------------
OUTPUT
------------------------*/
(10 row(s) affected)
Dbname Log_File_Size_MB log_Space_Used_MB log_Free_Space_MB
--------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
master 5.06 1.60 3.46
tempdb 8.00 0.45 7.55
model 8.00 0.59 7.41
msdb 1.00 0.50 0.50
ReportServer$MSSQL2016 8.00 1.80 6.20
ReportServer$MSSQL2016TempDB 8.00 1.80 6.20
DWDiagnostics 72.00 9.38 62.63
DWConfiguration 8.00 1.27 6.73
DWQueue 8.00 0.94 7.06
Pawan 8.00 1.31 6.69
(10 row(s) affected)
Use the sp_MSforeachdb undocumented stored procedure. It useful to run a command in all databases.
I've adapted it to use with your script:
I've adapted it to use with your script:
DECLARE @MyCommand VARCHAR(MAX) = '
select
''?'' DBName,
name,
[filename],
size as ''Size(MB)'',
usedspace as ''UsedSpace(MB)'',
(size - usedspace) as ''AvailableFreeSpace(MB)''
from
(
SELECT
s.name AS [Name],
s.physical_name AS [FileName],
(s.size * CONVERT(float,8))/1024 AS [Size],
(CAST(CASE s.type WHEN 2 THEN 0 ELSE CAST(FILEPROPERTY(s.name, ''SpaceUsed'') AS float)* CONVERT(float,8) END AS float))/1024 AS [UsedSpace],
s.file_id AS [ID]
FROM sys.filegroups AS g
INNER JOIN sys.master_files AS s ON ((s.type = 2 or s.type = 0) and s.database_id = db_id(''?'') and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
) DBFileSizeInfo'
EXEC sp_MSforeachdb @MyCommand
Yep, use the undocumented stored procedure sp_MSforeachdb
It will automatically swap the '?' for the database.
it is important to include 'use [?];' as your first line. There are some things that need just the current DB for granularity, so best to set your db.
Both pawan and vitor have supplied code, so no need to repost. Just include the 'use [?];' as the first line in vitor's example.
It will automatically swap the '?' for the database.
it is important to include 'use [?];' as your first line. There are some things that need just the current DB for granularity, so best to set your db.
Both pawan and vitor have supplied code, so no need to repost. Just include the 'use [?];' as the first line in vitor's example.
Mark, if you would read all comments more carefully then you could realize my post also contains a link to code using sp_MSforeachdb call :-).
So to summarize: We have 4 code samples, two of them (ID: 42391577, ID: 42392179) working just partially, the other two (ID: 42391608, ID: 42391648) providing correct results.
To publish the URL seems to me more convenient for the original author than to copy some code without the original source URL publishing. Web page value highly depends on external links...
So to summarize: We have 4 code samples, two of them (ID: 42391577, ID: 42392179) working just partially, the other two (ID: 42391608, ID: 42391648) providing correct results.
To publish the URL seems to me more convenient for the original author than to copy some code without the original source URL publishing. Web page value highly depends on external links...
Apologies pcelba,
Didnt see the link, and going back to inspect, now I do.
Sometimes masking the link with 'here' can be easy to miss :)
Didnt see the link, and going back to inspect, now I do.
Sometimes masking the link with 'here' can be easy to miss :)
Just include the 'use [?];' as the first line in vitor's example.There's no need to add the USE statement. The SP will do that automatically.
ASKER
Hi pcelba,
Your query is working fine as i expected. But only issue is showing NULL values in UsedSpace and AvailableFreespace Column, except for master database.
Your query is working fine as i expected. But only issue is showing NULL values in UsedSpace and AvailableFreespace Column, except for master database.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Have u tried my comment?
ASKER
I found some other T-SQL. which is working as i expected,
Thank you for all.
Thank you for all.
ASKER
Thank you victor
Ummm...that's not an answer to this question (ID: 42393117)
And YES it is important to include 'use [?];' as your first line whenever using sp_MSforeachdb
I repeat... There are some things that need just the current DB for granularity, so always best to set your db.
Using Vitor's code (ID: 42392179), you get NULL values as pointed out by pcelba
And YES it is important to include 'use [?];' as your first line whenever using sp_MSforeachdb
I repeat... There are some things that need just the current DB for granularity, so always best to set your db.
Using Vitor's code (ID: 42392179), you get NULL values as pointed out by pcelba
ASKER
This is the script i used.
-- =============================================
-- Author: Jonathan Roberts
-- Create date: 2015-07-23
-- Description: Script to display the database data and log file sizes
-- and available space on the volume.
-- Can be run on a registered server group to get data for all database servers
-- =============================================
GO
DECLARE @ServerVersion varchar(100)
SET @ServerVersion = CONVERT(varchar,SERVERPROPERTY('productversion'))
SET @ServerVersion = LEFT(@ServerVersion, CHARINDEX('.',@ServerVersion, 4)-1)
--PRINT @ServerVersion
DECLARE @command nvarchar(2000)
IF OBJECT_ID('tempdb..#FileData','U') IS NOT NULL
BEGIN
PRINT 'Dropping #FileData'
DROP TABLE tempdb..#FileData
END
CREATE TABLE tempdb..#FileData
(
[CurrentHost] varchar(250) COLLATE Latin1_General_CI_AS NULL,
[ClusterNodes] varchar(250) COLLATE Latin1_General_CI_AS NULL,
[DB] varchar(250) COLLATE Latin1_General_CI_AS NULL,
[FileType] varchar(250) COLLATE Latin1_General_CI_AS NULL,
[Name] varchar(250) COLLATE Latin1_General_CI_AS NULL,
[VolumeOrDrive] varchar(250) COLLATE Latin1_General_CI_AS NULL,
[FileName] varchar(250) COLLATE Latin1_General_CI_AS NULL,
[File Size (MB)] decimal(15,2) NULL,
[Space Used In File (MB)] decimal(15,2) NULL,
[Available Space In File (MB)] decimal(15,2) NULL,
[Drive Free Space (MB)] decimal(15,2) NULL
)
IF CONVERT(float, @ServerVersion) < 10.5 BEGIN --–2000, 2005, 2008
IF OBJECT_ID('tempdb..#xp_fixeddrives','U') IS NOT NULL
BEGIN
PRINT 'Dropping table #xp_fixeddrives'
DROP TABLE #xp_fixeddrives;
END
CREATE TABLE #xp_fixeddrives
(
Drive varchar(250),
MBFree int
)
INSERT INTO #xp_fixeddrives
(
Drive,
MBFree
)
EXEC master..xp_fixeddrives
SET @command = '
USE [?]
INSERT INTO #FileData
(
[CurrentHost],
[ClusterNodes],
[DB],
[FileType],
[Name],
[VolumeOrDrive],
[FileName],
[File Size (MB)],
[Space Used In File (MB)],
[Available Space In File (MB)],
[Drive Free Space (MB)]
)
SELECT CONVERT(varchar(250), SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')) COLLATE Latin1_General_CI_AS AS [CurrentHost],
CONVERT(varchar(250), ISNULL(STUFF((SELECT '', '' + NodeName FROM fn_virtualservernodes() FOR XML PATH('''')), 1, 1, '''' ), '''')) COLLATE Latin1_General_CI_AS AS [CluserNodes],
CONVERT(varchar(250), DB_NAME()) COLLATE Latin1_General_CI_AS [DB],
CONVERT(varchar(250), df.type_desc) COLLATE Latin1_General_CI_AS [FileType],
CONVERT(varchar(250), f.Name) COLLATE Latin1_General_CI_AS [Name],
CONVERT(varchar(250), LEFT(f.FileName, 3)) COLLATE Latin1_General_CI_AS [VolumeOrDrive],
CONVERT(varchar(250), f.FileName) COLLATE Latin1_General_CI_AS [FileName],
CONVERT(Decimal(15,2), ROUND(f.Size/128.000, 2)) [File Size (MB)],
CONVERT(Decimal(15,2), ROUND(FILEPROPERTY(f.Name,''SpaceUsed'')/128.000,2)) [Space Used In File (MB)],
CONVERT(Decimal(15,2), ROUND((f.Size-FILEPROPERTY(f.Name,''SpaceUsed''))/128.000,2)) [Available Space In File (MB)],
CONVERT(Decimal(15,2), d.MBFree) [Drive Free Space (MB)]
FROM dbo.sysfiles f WITH (NOLOCK)
INNER JOIN sys.database_files df ON df.file_id = f.fileid
LEFT JOIN tempdb..#xp_fixeddrives d
ON LEFT(f.FileName, 1) COLLATE Latin1_General_CI_AS = d.Drive COLLATE Latin1_General_CI_AS;'
END
ELSE -- SQL 2008R2+ (function sys.dm_os_volume_stats is available)
BEGIN
SET @command = 'USE [?]
INSERT INTO #FileData
(
[CurrentHost],
[ClusterNodes],
[DB],
[FileType],
[Name],
[VolumeOrDrive],
[FileName],
[File Size (MB)],
[Space Used In File (MB)],
[Available Space In File (MB)],
[Drive Free Space (MB)]
)
SELECT CONVERT(varchar(250), SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')) COLLATE Latin1_General_CI_AS AS [CurrentHost],
CONVERT(varchar(250), ISNULL(STUFF((SELECT '', '' + NodeName FROM fn_virtualservernodes() FOR XML PATH('''')), 1, 1, '''' ), '''')) COLLATE Latin1_General_CI_AS AS [CluserNodes],
CONVERT(varchar(250), DB_NAME(v.database_id)) COLLATE Latin1_General_CI_AS [DB],
CONVERT(varchar(250), df.type_desc) COLLATE Latin1_General_CI_AS [FileType],
CONVERT(varchar(250), f.name) COLLATE Latin1_General_CI_AS [Name],
CONVERT(varchar(250), v.volume_mount_point) COLLATE Latin1_General_CI_AS [VolumeOrDrive],
CONVERT(varchar(250), f.[Filename]) COLLATE Latin1_General_CI_AS [Filename],
CONVERT(Decimal(15,2), ROUND(f.Size/128.000,2)) [File Size (MB)],
CONVERT(Decimal(15,2), ROUND(FILEPROPERTY(f.Name,''SpaceUsed'')/128.000,2)) [Space Used In File (MB)],
CONVERT(Decimal(15,2), ROUND((f.Size-FILEPROPERTY(f.Name,''SpaceUsed''))/128.000,2)) [Available Space In File (MB)],
CONVERT(Decimal(15,2), v.available_bytes/1048576.0) [Drive Free Space (MB)]
FROM sys.sysfiles f WITH (NOLOCK)
INNER JOIN sys.database_files df ON df.file_id = f.fileid
CROSS APPLY sys.dm_os_volume_stats(DB_ID(), f.fileid) v;'
END -- END IF
EXEC sp_MSforeachdb @command
SELECT *
FROM #FileData
DROP TABLE tempdb..#FileData
GOASKER
Thank you pcelba
Open in new window