May 11, 2016 at 11:23 pm
I have a few servers and approx. 40 DB's per server... I'm trying to determine the size of all DBs which I can get by simply using "sp_spaceused" against each database however it is a lot of work to do it manually.
I can't find anything online that can loop it through and give me the results. I've tried the below:
DECLARE @DBName TABLE (
id INT IDENTITY(1,1),
Name VARCHAR(255)
)
DECLARE @MinID INT
DECLARE @MaxID INT
DECLARE @DB VARCHAR(255)
declare @sql varchar(max)
INSERT INTO @DBName (
Name
)
SELECT Name
FROM [master].[dbo].[sysdatabases]
SELECT @MinID = (SELECT MIN(ID) FROM @DBName)
SELECT @MaxID = (SELECT MAX(ID) FROM @DBName)
WHILE @MinID <= @MaxID
BEGIN
SELECT @sql = ('USE ' + Name + ' GO sp_spaceused' )
FROM @DBName
WHEREID = @MinID
select @sql
SELECT @MinID = @MinID + 1
END
It gives me the outcome of "USE [DBName] GO sp_spaceused" but it doesn't work as it throws an error since you only need to say "sp_spaceused" on the DB itself..
any idea or does someone have a script previously used?
May 11, 2016 at 11:31 pm
as usual ... soon as I post it, I find the solution.
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'
Is there anyway to get this into 1 table though as its across all the multiple result sets??
May 12, 2016 at 7:52 am
Tava here is what i created, based on a post her eon SCC that i found;
I've enhanced it over time, but i just put this in master and run it on my server when i need to peek at disk space and free space.
IF OBJECT_ID('[dbo].[sp_dbspaceused]') IS NOT NULL
DROP PROCEDURE [dbo].[sp_dbspaceused]
GO
--#################################################################################################
--developer utility function added by Lowell, used in SQL Server Management Studio
--Purpose: disk psace per db, with consdieration for db's that ar eoffline
--#################################################################################################
CREATE PROCEDURE sp_dbspaceused
AS
BEGIN
SET NOCOUNT ON
DECLARE @CurrentDB NVARCHAR(128)
-- The table #tblServerDatabases holds the names of databases on the server.
-- This table is used here to allow us to loop through each database,
-- rather than to run undocumented procedures, such as sp_msforeachdb
-- (which eliminated the need for a loop).
IF OBJECT_ID('tempdb..#tblServerDatabases', 'U') IS NOT NULL
DROP TABLE #tblServerDatabases
CREATE TABLE #tblServerDatabases (DBName NVARCHAR(128),state_desc NVARCHAR(128))
-- The table #tblDBFilesExtendedInfo holds the data and log files info
-- (name, size, used, free spaces, etc.).
IF OBJECT_ID('tempdb..#tblDBFilesExtendedInfo', 'U') IS NOT NULL
DROP TABLE #tblDBFilesExtendedInfo
CREATE TABLE #tblDBFilesExtendedInfo (
Idx INT IDENTITY(1, 1),
FileID INT,
FileGroupID INT,
TotalExtents BIGINT,
UsedExtents BIGINT,
DBFileName NVARCHAR(128),
LogicalFileName NVARCHAR(128),
DBFilePath NVARCHAR(1024),
DBFileType VARCHAR(16),
DBName NVARCHAR(128),
[TotalFileSize(MB)] MONEY,
[TotalUsed(MB)] MONEY,
[TotalFree(MB)] MONEY,
[SpaceUsed(%)] MONEY,
Status INT)
-- This table will hold the output of sp_helpfile, for each database.
-- This is needed in order to get the log file path and file name.
IF OBJECT_ID('tempdb..#tblDBFilesBasicInfo', 'U') IS NOT NULL
DROP TABLE #tblDBFilesBasicInfo
CREATE TABLE #tblDBFilesBasicInfo (
DBName NVARCHAR(128),
DBFileName NVARCHAR(128),
FileID INT,
FilePath NVARCHAR(1024),
FileGroupDesc NVARCHAR(128),
FileSizeKB NVARCHAR(64),
MaxSizeDesc NVARCHAR(64),
Growth NVARCHAR(64),
Usage NVARCHAR(64))
-- First - the data files are handled throught the
-- DBCC SHOWFILESTATS command.
INSERT INTO #tblServerDatabases (DBName,state_desc)
SELECT dbz.[name],state_desc
FROM master.sys.databases dbz
SELECT @CurrentDB = MIN(DBName)
FROM #tblServerDatabases
WHERE state_desc='ONLINE'
WHILE @CurrentDB IS NOT NULL
BEGIN
INSERT INTO #tblDBFilesExtendedInfo(
FileID,
FileGroupID,
TotalExtents,
UsedExtents,
DBFileName,
DBFilePath)
EXEC ('USE [' + @CurrentDB + '] DBCC SHOWFILESTATS')
UPDATE #tblDBFilesExtendedInfo
SET DBName = @CurrentDB,
DBFileType = 'Data File'
WHERE DBName IS NULL
-- Run the sp_helpfile in order to get log file data.
INSERT INTO #tblDBFilesBasicInfo(
DBFileName,
FileID,
FilePath,
FileGroupDesc,
FileSizeKB,
MaxSizeDesc,
Growth,
Usage)
EXEC ('USE [' + @CurrentDB + '] EXEC sp_helpfile ')
UPDATE #tblDBFilesBasicInfo
SET DBName = @CurrentDB
WHERE DBName IS NULL
SELECT @CurrentDB = MIN(DBName)
FROM #tblServerDatabases WITH (NOLOCK)
WHERE DBName > @CurrentDB
AND state_desc='ONLINE'
END
-- Update the total file size, used and free space, based on the
-- extents information returned from DBCC SHOWFILESTATS.
UPDATE #tblDBFilesExtendedInfo
SET [DBFileName] = RIGHT(DBFilePath, CHARINDEX('\', REVERSE(DBFilePath)) -1),
[TotalFileSize(MB)] = CAST(((TotalExtents*64) / 1024.00) AS MONEY),
[TotalUsed(MB)] = CAST(((UsedExtents*64) / 1024.00) AS MONEY),
[TotalFree(MB)] = CAST(((TotalExtents*64) / 1024.00) AS MONEY)
- CAST(((UsedExtents*64) / 1024.00) AS MONEY),
[SpaceUsed(%)] = CASE
WHEN CAST(((TotalExtents*64) / 1024.00) AS MONEY) = 0.0 THEN 0.0
ELSE (CAST(((UsedExtents*64) / 1024.00) AS MONEY)*100)
/ CAST(((TotalExtents*64) / 1024.00) AS MONEY)
END
-- We are now done with the data file statuses, and we shall move
-- on to get the log files info, by using DBCC SQLPERF(LOGSPACE)
INSERT INTO #tblDBFilesExtendedInfo (DBName, [TotalFileSize(MB)], [SpaceUsed(%)], Status)
EXEC('DBCC SQLPERF(LOGSPACE)')
UPDATE a
SET [TotalUsed(MB)] = (a.[SpaceUsed(%)]/100.00)*a.[TotalFileSize(MB)],
[TotalFree(MB)] = (1.0 - (a.[SpaceUsed(%)]/100.00))*a.[TotalFileSize(MB)],
DBFileType = 'Log file',
DBFilePath = b.FilePath,
DBFileName = RIGHT(b.FilePath, CHARINDEX('\', REVERSE(b.FilePath)) -1)
FROM #tblDBFilesExtendedInfo a
INNER JOIN #tblDBFilesBasicInfo b
ON a.DBName = b.DBName
WHERE a.DBFileType IS NULL
AND b.Usage = 'log only'
--we want the logical file name as well, in case we need it for other purposes, like getting everything in a single spot to move files
UPDATE MyTarget
SET MyTarget.LogicalFileName = mff.name
FROM #tblDBFilesExtendedInfo MyTarget
INNER JOIN sys.master_files mff
ON MyTarget.DBFilePath = mff.physical_name
SET NOCOUNT OFF
SELECT * FROM #tblServerDatabases WHERE DBname NOT IN(SELECT DbName FROM #tblDBFilesExtendedInfo)
-- That's it. We now need to return a readable recordset.
SELECT DBName,
DBFileType,
LogicalFileName,
DBFileName,
DBFilePath,
[TotalFileSize(MB)],
[TotalUsed(MB)],
[SpaceUsed(%)],
[TotalFree(MB)]
FROM #tblDBFilesExtendedInfo WITH (NOLOCK)
UNION ALL
SELECT DBname, state_desc,state_desc,state_desc,state_desc,NULL,NULL,NULL,NULL FROM #tblServerDatabases WHERE DBname NOT IN(SELECT DbName FROM #tblDBFilesExtendedInfo)
ORDER BY DBName ASC, DBFileType ASC
-- Cleanup
IF OBJECT_ID('tempdb..#tblServerDatabases', 'U') IS NOT NULL
DROP TABLE #tblServerDatabases
IF OBJECT_ID('tempdb..#tblDBFilesExtendedInfo', 'U') IS NOT NULL
DROP TABLE #tblDBFilesExtendedInfo
IF OBJECT_ID('tempdb..#tblDBFilesBasicInfo', 'U') IS NOT NULL
DROP TABLE #tblDBFilesBasicInfo
END -- PROC
GO
--#################################################################################################
--Mark as a system object
EXECUTE sp_ms_marksystemobject '[dbo].[sp_dbspaceused]'
--#################################################################################################
Lowell
May 12, 2016 at 5:25 pm
Thanks for sharing that script... appreciate it
May 12, 2016 at 9:13 pm
Wouldn't it all be easier to just use a SELECT from sys.master_files? Do you really need to know things like the amount of free space and how much space was used by data vs indexes?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 13, 2016 at 12:38 am
Jeff Moden (5/12/2016)
Wouldn't it all be easier to just use a SELECT from sys.master_files? Do you really need to know things like the amount of free space and how much space was used by data vs indexes?
Unfortunately they want to know the whole lot... Index vs data vs unallocated
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply