First of all, let me say an apology for not following up on TechTarget since I published sp_SDS in May, 2008, despite of pretty good feedback in the SQL Server community. I've been using sp_SDS during the last decade with occasional modifications, most of which are SQL build eligibility. If you're interested please check my original post here:
http://searchsqlserver.techtarget.com/tip/Check-SQL-Server-database-and-log-file-size-with-this-stored-procedure
In this post I'd like to present the current code of stored procedure sp_SDS. I will not repeat what was described in the TechTarget article but instead I'm putting down some notes on improvements. As database sizes increase to terabytes, the original code broke due to insufficient decimal conversion. In this most updated version decimal numbers are expanded to scale of 16, enough to accommodate petabytes which is rarely seen nowadays. One pain of updating the code is to keep up with new releases of SQL Servers. In this version of sp_SDS, I made it current to work with SQL Server 2017. In addition I boldly put down the next three future versions - SQL Server 2018/2019/2020. Given the crazy pace that Microsoft releases its products, it might very well be a good guess. Lastly, I added a column to show database recovery mode since many times I found I need that information handy.
As usual, feel free to use it and leave your comment. If there's a popular feature in demand, I may consider incorporating it.
T-SQL Code for sp_SDS:
USE master;
GO
IF OBJECT_ID('dbo.sp_SDS', 'P') IS NOT NULL
DROP PROCEDURE dbo.sp_SDS;
GO
CREATE PROCEDURE dbo.sp_SDS
@TargetDatabase sysname = NULL, -- NULL: all dbs
@Level varchar(10) = 'Database', -- or "File"
@UpdateUsage bit = 0, -- default no update
@Unit char(2) = 'KB' -- Megabytes, Kilobytes or Gigabytes
WITH ENCRYPTION
AS
/***************************************************************************************************************
**
** Author: Richard Ding
** Created: 4/8/2008
** Updated:
** 8/20/2010 - Fix "Arithmetic overflow error converting numeric to data type numeric"
** when IsNumericRoundAbortEnabled turns on, it error out when loss of precision
** Replace HAS_DBACCESS with serverproperty function as the former is permission
** 3/17/2015 - Added SQL 2012 and 2014 in version checking
** 5/4/2017 - Added SQL 2016 and 2017 in version checking
** 1/23/2018 - Expanded size to petabytes (PB) with dec (18, 2).
** Added potential future SQL 2018/2020 in version checking
** Usage: list db/file size AND path w/o summary
** Refer to: http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1313431,00.html
** Test code: sp_SDS -- default behavior
** sp_SDS 'master'
** sp_SDS NULL, NULL, 0
** sp_SDS NULL, 'file', 1, 'GB'
** sp_SDS 'Test_snapshot', 'Database', 1
** sp_SDS 'adventureworks', 'File', 0, 'kb'
** sp_SDS 'Finance', 'Database', 0, 'gb'
** sp_SDS 'tempdb', NULL, 1, 'kb'
**
***************************************************************************************************************/
SET NOCOUNT ON;
IF @TargetDatabase IS NOT NULL AND DB_ID(@TargetDatabase) IS NULL
BEGIN
RAISERROR(15010, -1, -1, @TargetDatabase);
RETURN (-1)
END
IF OBJECT_ID('tempdb.dbo.##Tbl_CombinedInfo', 'U') IS NOT NULL
DROP TABLE dbo.##Tbl_CombinedInfo;
IF OBJECT_ID('tempdb.dbo.##Tbl_DbFileStats', 'U') IS NOT NULL
DROP TABLE dbo.##Tbl_DbFileStats;
IF OBJECT_ID('tempdb.dbo.##Tbl_ValidDbs', 'U') IS NOT NULL
DROP TABLE dbo.##Tbl_ValidDbs;
IF OBJECT_ID('tempdb.dbo.##Tbl_Logs', 'U') IS NOT NULL
DROP TABLE dbo.##Tbl_Logs;
CREATE TABLE dbo.##Tbl_CombinedInfo (
DatabaseName sysname NULL,
[type] VARCHAR(10) NULL,
LogicalName sysname NULL,
T dec(18, 2) NULL,
U dec(18, 2) NULL,
[U(%)] dec(5, 2) NULL,
F dec(18, 2) NULL,
[F(%)] dec(5, 2) NULL,
PhysicalName sysname NULL );
CREATE TABLE dbo.##Tbl_DbFileStats (
Id int identity,
DatabaseName sysname NULL,
FileId int NULL,
FileGroup int NULL,
TotalExtents bigint NULL,
UsedExtents bigint NULL,
Name sysname NULL,
FileName varchar(255) NULL );
CREATE TABLE dbo.##Tbl_ValidDbs (
Id int identity,
Dbname sysname NULL );
CREATE TABLE dbo.##Tbl_Logs (
DatabaseName sysname NULL,
LogSize dec(18, 2) NULL,
LogSpaceUsedPercent dec (5, 2) NULL,
Status int NULL );
DECLARE @Ver varchar(15),
@DatabaseName sysname,
@Ident_last int,
@String varchar(2000),
@BaseString varchar(2000);
SELECT @DatabaseName = '',
@Ident_last = 0,
@String = '',
@Ver = CASE WHEN @@VERSION LIKE '%8.0%' THEN 'SQL 2000'
WHEN @@VERSION LIKE '%9.0%' THEN 'SQL 2005'
WHEN @@VERSION LIKE '%10.0%' THEN 'SQL 2008'
WHEN @@VERSION LIKE '%10.50%' THEN 'SQL 2008 R2'
WHEN @@VERSION LIKE '%11.0%' THEN 'SQL 2012'
WHEN @@VERSION LIKE '%12.0%' THEN 'SQL 2014'
WHEN @@VERSION LIKE '%13.0%' THEN 'SQL 2016'
WHEN @@VERSION LIKE '%14.0%' THEN 'SQL 2017'
WHEN @@VERSION LIKE '%15.0%' THEN 'SQL 2018' -- guess
WHEN @@VERSION LIKE '%16.0%' THEN 'SQL 2019' -- guess
WHEN @@VERSION LIKE '%17.0%' THEN 'SQL 2020' -- guess
END;
SELECT @BaseString =
' SELECT DB_NAME(), ' +
CASE WHEN @Ver = 'SQL 2000' THEN 'CASE WHEN status & 0x40 = 0x40 THEN ''Log'' ELSE ''Data'' END'
ELSE ' CASE type WHEN 0 THEN ''Data'' WHEN 1 THEN ''Log'' WHEN 4 THEN ''Full-text'' ELSE ''reserved'' END' END +
', name, ' +
CASE WHEN @Ver = 'SQL 2000' THEN 'filename' ELSE 'physical_name' END +
', size*8.0/1024.0 FROM ' +
CASE WHEN @Ver = 'SQL 2000' THEN 'sysfiles' ELSE 'sys.database_files' END +
' WHERE '
+ CASE WHEN @Ver = 'SQL 2000' THEN ' HAS_DBACCESS(DB_NAME()) = 1' ELSE 'state_desc = ''ONLINE''' END + '';
SELECT @String = 'INSERT INTO dbo.##Tbl_ValidDbs SELECT name FROM ' +
CASE WHEN @Ver = 'SQL 2000' THEN 'master.dbo.sysdatabases'
WHEN @Ver IN ('SQL 2005', 'SQL 2008', 'SQL 2008 R2', 'SQL 2012', 'SQL 2014', 'SQL 2016', 'SQL 2017', 'SQL 2018', 'SQL 2019', 'SQL 2020')
THEN 'master.sys.databases'
END + ' WHERE DATABASEPROPERTYEX (name,''Status'') = ''ONLINE'' ORDER BY name ASC';
EXEC (@String);
INSERT INTO dbo.##Tbl_Logs EXEC ('DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS');
-- For data part
IF @TargetDatabase IS NOT NULL
BEGIN
SELECT @DatabaseName = @TargetDatabase;
IF @UpdateUsage <> 0 AND DATABASEPROPERTYEX (@DatabaseName,'Status') = 'ONLINE'
AND DATABASEPROPERTYEX (@DatabaseName, 'Updateability') <> 'READ_ONLY'
BEGIN
SELECT @String = 'USE [' + @DatabaseName + '] DBCC UPDATEUSAGE (0)';
PRINT '*** ' + @String + ' *** ';
EXEC (@String);
PRINT '';
END
SELECT @String = 'INSERT INTO dbo.##Tbl_CombinedInfo (DatabaseName, type, LogicalName, PhysicalName, T) ' + @BaseString;
INSERT INTO dbo.##Tbl_DbFileStats (FileId, FileGroup, TotalExtents, UsedExtents, Name, FileName)
EXEC ('USE [' + @DatabaseName + '] DBCC SHOWFILESTATS WITH NO_INFOMSGS');
EXEC ('USE [' + @DatabaseName + '] ' + @String);
UPDATE dbo.##Tbl_DbFileStats SET DatabaseName = @DatabaseName;
END
ELSE
BEGIN
WHILE 1 = 1
BEGIN
SELECT TOP 1 @DatabaseName = Dbname FROM dbo.##Tbl_ValidDbs WHERE Dbname > @DatabaseName ORDER BY Dbname ASC;
IF @@ROWCOUNT = 0
BREAK;
IF @UpdateUsage <> 0 AND DATABASEPROPERTYEX (@DatabaseName, 'Status') = 'ONLINE'
AND DATABASEPROPERTYEX (@DatabaseName, 'Updateability') <> 'READ_ONLY'
BEGIN
SELECT @String = 'DBCC UPDATEUSAGE (''' + @DatabaseName + ''') ';
PRINT '*** ' + @String + '*** ';
EXEC (@String);
PRINT '';
END
SELECT @Ident_last = ISNULL(MAX(Id), 0) FROM dbo.##Tbl_DbFileStats;
SELECT @String = 'INSERT INTO dbo.##Tbl_CombinedInfo (DatabaseName, type, LogicalName, PhysicalName, T) ' + @BaseString;
EXEC ('USE [' + @DatabaseName + '] ' + @String);
INSERT INTO dbo.##Tbl_DbFileStats (FileId, FileGroup, TotalExtents, UsedExtents, Name, FileName)
EXEC ('USE [' + @DatabaseName + '] DBCC SHOWFILESTATS WITH NO_INFOMSGS');
UPDATE dbo.##Tbl_DbFileStats SET DatabaseName = @DatabaseName WHERE Id BETWEEN @Ident_last + 1 AND @@IDENTITY;
END
END
-- set used size for data files, do not change total obtained from sys.database_files as it has for log files
UPDATE dbo.##Tbl_CombinedInfo
SET U = s.UsedExtents*8*8/1024.0
FROM dbo.##Tbl_CombinedInfo t JOIN dbo.##Tbl_DbFileStats s
ON t.LogicalName = s.Name AND s.DatabaseName = t.DatabaseName;
-- set used size and % values for log files:
UPDATE dbo.##Tbl_CombinedInfo
SET [U(%)] = LogSpaceUsedPercent,
U = T * LogSpaceUsedPercent/100.0
FROM dbo.##Tbl_CombinedInfo t JOIN dbo.##Tbl_Logs l
ON l.DatabaseName = t.DatabaseName
WHERE t.type = 'Log';
UPDATE dbo.##Tbl_CombinedInfo SET F = T - U, [U(%)] = U*100.0/T;
UPDATE dbo.##Tbl_CombinedInfo SET [F(%)] = F*100.0/T;
IF UPPER(ISNULL(@Level, 'DATABASE')) = 'FILE'
BEGIN
IF @Unit = 'KB'
UPDATE dbo.##Tbl_CombinedInfo
SET T = T * 1024, U = U * 1024, F = F * 1024;
IF @Unit = 'GB'
UPDATE dbo.##Tbl_CombinedInfo
SET T = T / 1024, U = U / 1024, F = F / 1024;
SELECT DatabaseName AS 'Database',
type AS 'Type',
LogicalName,
T AS 'Total',
U AS 'Used',
[U(%)] AS 'Used (%)',
F AS 'Free',
[F(%)] AS 'Free (%)',
PhysicalName
FROM dbo.##Tbl_CombinedInfo
WHERE DatabaseName LIKE ISNULL(@TargetDatabase, '%')
ORDER BY DatabaseName ASC, type ASC;
SELECT CASE WHEN @Unit = 'GB' THEN 'GB' WHEN @Unit = 'KB' THEN 'KB' ELSE 'MB' END AS 'SUM',
SUM (T) AS 'TOTAL', SUM (U) AS 'USED', SUM (F) AS 'FREE' FROM dbo.##Tbl_CombinedInfo;
END
IF UPPER(ISNULL(@Level, 'DATABASE')) = 'DATABASE'
BEGIN
DECLARE @Tbl_Final TABLE (
DatabaseName sysname NULL,
TOTAL dec(18, 2),
[=] char(1),
used dec(18, 2),
[used (%)] dec (5, 2),
[+] char(1),
free dec(18, 2),
[free (%)] dec (5, 2),
[==] char(2),
Data dec(18, 2),
Data_Used dec(18, 2),
[Data_Used (%)] dec (5, 2),
Data_Free dec(18, 2),
[Data_Free (%)] dec (5, 2),
[++] char(2),
Log dec(18, 2),
Log_Used dec(18, 2),
[Log_Used (%)] dec (5, 2),
Log_Free dec(18, 2),
[Log_Free (%)] dec (5, 2) );
INSERT INTO @Tbl_Final
SELECT x.DatabaseName,
x.Data + y.Log AS 'TOTAL',
'=' AS '=',
x.Data_Used + y.Log_Used AS 'U',
(x.Data_Used + y.Log_Used)*100.0 / (x.Data + y.Log) AS 'U(%)',
'+' AS '+',
x.Data_Free + y.Log_Free AS 'F',
(x.Data_Free + y.Log_Free)*100.0 / (x.Data + y.Log) AS 'F(%)',
'==' AS '==',
x.Data,
x.Data_Used,
x.Data_Used*100/x.Data AS 'D_U(%)',
x.Data_Free,
x.Data_Free*100/x.Data AS 'D_F(%)',
'++' AS '++',
y.Log,
y.Log_Used,
y.Log_Used*100/y.Log AS 'L_U(%)',
y.Log_Free,
y.Log_Free*100/y.Log AS 'L_F(%)'
FROM
( SELECT d.DatabaseName,
SUM(d.T) AS 'Data',
SUM(d.U) AS 'Data_Used',
SUM(d.F) AS 'Data_Free'
FROM dbo.##Tbl_CombinedInfo d WHERE d.type = 'Data' GROUP BY d.DatabaseName ) AS x
JOIN
( SELECT l.DatabaseName,
SUM(l.T) AS 'Log',
SUM(l.U) AS 'Log_Used',
SUM(l.F) AS 'Log_Free'
FROM dbo.##Tbl_CombinedInfo l WHERE l.type = 'Log' GROUP BY l.DatabaseName ) AS y
ON x.DatabaseName = y.DatabaseName;
IF @Unit = 'KB'
UPDATE @Tbl_Final SET TOTAL = TOTAL * 1024,
used = used * 1024,
free = free * 1024,
Data = Data * 1024,
Data_Used = Data_Used * 1024,
Data_Free = Data_Free * 1024,
Log = Log * 1024,
Log_Used = Log_Used * 1024,
Log_Free = Log_Free * 1024;
IF @Unit = 'GB'
UPDATE @Tbl_Final SET TOTAL = TOTAL / 1024,
used = used / 1024,
free = free / 1024,
Data = Data / 1024,
Data_Used = Data_Used / 1024,
Data_Free = Data_Free / 1024,
Log = Log / 1024,
Log_Used = Log_Used / 1024,
Log_Free = Log_Free / 1024;
DECLARE @GrantTotal dec(18, 2);
SELECT @GrantTotal = SUM(TOTAL) FROM @Tbl_Final;
SELECT
CONVERT(dec(18, 2), TOTAL*100.0/@GrantTotal) AS 'WEIGHT (%)',
DatabaseName AS 'DATABASE',
Databasepropertyex (DatabaseName, 'recovery') as 'Recovery Model',
CONVERT(VARCHAR(12), used) + ' (' + CONVERT(VARCHAR(12), [used (%)]) + ' %)' AS 'USED (%)',
[+],
CONVERT(VARCHAR(12), free) + ' (' + CONVERT(VARCHAR(12), [free (%)]) + ' %)' AS 'FREE (%)',
[=],
TOTAL,
[=],
CONVERT(VARCHAR(12), Data) + ' (' + CONVERT(VARCHAR(12), Data_Used) + ', ' +
CONVERT(VARCHAR(12), [Data_Used (%)]) + '%)' AS 'DATA (used, %)',
[+],
CONVERT(VARCHAR(12), Log) + ' (' + CONVERT(VARCHAR(12), Log_Used) + ', ' +
CONVERT(VARCHAR(12), [Log_Used (%)]) + '%)' AS 'LOG (used, %)'
FROM @Tbl_Final
WHERE DatabaseName LIKE ISNULL(@TargetDatabase, '%')
ORDER BY DatabaseName ASC;
IF @TargetDatabase IS NULL
SELECT CASE WHEN @Unit = 'GB' THEN 'GB' WHEN @Unit = 'KB' THEN 'KB' ELSE 'MB' END AS 'SUM',
SUM (used) AS 'USED',
SUM (free) AS 'FREE',
SUM (TOTAL) AS 'TOTAL',
SUM (Data) AS 'DATA',
SUM (Log) AS 'LOG'
FROM @Tbl_Final;
END
RETURN (0)
GO
No comments:
Post a Comment