January 22, 2016 at 11:18 am
Brandie Tarvin (1/22/2016)
Okay. This is driving me batty.New code (with datatypes fixed) works fine ....
Where it fails on that DBFileName error again. (and yes, I deliberately left the DEFAULT columns as NULLABLE for the moment).
I still can't figure out why having the driveletter column "fixes" the code when I'm totally ignoring the DBFileName column in both versions. <headdesk>.
The easy fix is to through DBFileName into the code properly. But I really want to know why SQL is acting the way it is.
In the "working" code with the drive letter included, you're joining to sys.database_files for the INSERT into #alldbstate. That means you only get files from the current database. You're also joining sys.databases and sys.database_files on the database name equaling the file name, which I'm sure isn't what is intended. I assume you meant to join to sys.master_files, and on database_id. At any rate, since the query joining sys.databases and sys.database_files on file name=database name won't return any rows for offline databases (and only happens to return anything at all if there's a file in the current database with a name that is the same as the current database name), the next INSERT into #dbsize doesn't try to INSERT any rows, which is why it "works".
If you fix the INSERT into #alldbstate to join to master_files on database_id, then you have the same NULL problem in both versions.
Cheers!
EDIT: Fixed to quote the correct post.
January 22, 2016 at 3:03 pm
Brandie Tarvin (1/22/2016)
Okay. This is driving me batty.New code (with datatypes fixed) works fine ....
(code snipped)
...until I remove that damn driveletter column.
(code snipped again)
Where it fails on that DBFileName error again. (and yes, I deliberately left the DEFAULT columns as NULLABLE for the moment).
I still can't figure out why having the driveletter column "fixes" the code when I'm totally ignoring the DBFileName column in both versions. <headdesk>.
The easy fix is to through DBFileName into the code properly. But I really want to know why SQL is acting the way it is.
If you change:
INSERT INTO #alldbstate (dbname,driveletter,DBstatus,R_model)
SELECT sd.name,SUBSTRING(df.physical_name,1,1), CONVERT(VARCHAR(20),DATABASEPROPERTYEX(sd.name,'status')),sd.recovery_model_desc
FROM sys.databases sd
INNER JOIN sys.database_files df
ON sd.name = df.name;
to:
INSERT INTO #alldbstate (dbname,driveletter,DBstatus,R_model)
SELECT sd.name,SUBSTRING(df.physical_name,1,1), CONVERT(VARCHAR(20),DATABASEPROPERTYEX(sd.name,'status')),sd.recovery_model_desc
FROM sys.databases sd
INNER JOIN sys.master_files df
ON sd.database_id = df.database_id
Then this also generates the error for the null value in the DBFileName column.
The query makes the changes that Jacob mentioned:
1. Joins to sys.database_files - this gets just the files from the current database. Changed to sys.master_files to get all files for all databases.
2. Changed join condition from name=name to database_id = database_id. The name=name will only match if the database name is the same as the logical name of the database file... which it usually isn't.
Does this help? Need anything else?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 22, 2016 at 3:07 pm
WayneS (1/22/2016)
Brandie Tarvin (1/22/2016)
Okay. This is driving me batty.New code (with datatypes fixed) works fine ....
(code snipped)
...until I remove that damn driveletter column.
(code snipped again)
Where it fails on that DBFileName error again. (and yes, I deliberately left the DEFAULT columns as NULLABLE for the moment).
I still can't figure out why having the driveletter column "fixes" the code when I'm totally ignoring the DBFileName column in both versions. <headdesk>.
The easy fix is to through DBFileName into the code properly. But I really want to know why SQL is acting the way it is.
If you change:
INSERT INTO #alldbstate (dbname,driveletter,DBstatus,R_model)SELECT sd.name,SUBSTRING(df.physical_name,1,1), CONVERT(VARCHAR(20),DATABASEPROPERTYEX(sd.name,'status')),sd.recovery_model_desc
FROM sys.databases sd
INNER JOIN sys.database_files df
ON sd.name = df.name;
to:
INSERT INTO #alldbstate (dbname,driveletter,DBstatus,R_model)SELECT sd.name,SUBSTRING(df.physical_name,1,1), CONVERT(VARCHAR(20),DATABASEPROPERTYEX(sd.name,'status')),sd.recovery_model_desc
FROM sys.databases sd
INNER JOIN sys.master_files df
ON sd.database_id = df.database_id
Then this also generates the error for the null value in the DBFileName column.
The query makes the changes that Jacob mentioned:
1. Joins to sys.database_files - this gets just the files from the current database. Changed to sys.master_files to get all files for all databases.
2. Changed join condition from name=name to database_id = database_id. The name=name will only match if the database name is the same as the logical name of the database file... which it usually isn't.
Does this help? Need anything else?
If you look at her original script you'd see that the query is run in each database using the sp_msforeachdb as it is getting additional info for the table.
January 22, 2016 at 3:16 pm
Lynn Pettis (1/22/2016)
WayneS (1/22/2016)
Brandie Tarvin (1/22/2016)
Okay. This is driving me batty.New code (with datatypes fixed) works fine ....
(code snipped)
...until I remove that damn driveletter column.
(code snipped again)
Where it fails on that DBFileName error again. (and yes, I deliberately left the DEFAULT columns as NULLABLE for the moment).
I still can't figure out why having the driveletter column "fixes" the code when I'm totally ignoring the DBFileName column in both versions. <headdesk>.
The easy fix is to through DBFileName into the code properly. But I really want to know why SQL is acting the way it is.
If you change:
INSERT INTO #alldbstate (dbname,driveletter,DBstatus,R_model)SELECT sd.name,SUBSTRING(df.physical_name,1,1), CONVERT(VARCHAR(20),DATABASEPROPERTYEX(sd.name,'status')),sd.recovery_model_desc
FROM sys.databases sd
INNER JOIN sys.database_files df
ON sd.name = df.name;
to:
INSERT INTO #alldbstate (dbname,driveletter,DBstatus,R_model)SELECT sd.name,SUBSTRING(df.physical_name,1,1), CONVERT(VARCHAR(20),DATABASEPROPERTYEX(sd.name,'status')),sd.recovery_model_desc
FROM sys.databases sd
INNER JOIN sys.master_files df
ON sd.database_id = df.database_id
Then this also generates the error for the null value in the DBFileName column.
The query makes the changes that Jacob mentioned:
1. Joins to sys.database_files - this gets just the files from the current database. Changed to sys.master_files to get all files for all databases.
2. Changed join condition from name=name to database_id = database_id. The name=name will only match if the database name is the same as the logical name of the database file... which it usually isn't.
Does this help? Need anything else?
If you look at her original script you'd see that the query is run in each database using the sp_msforeachdb as it is getting additional info for the table.
No, both in the original script and the recently posted versions, that code is run after the sp_msforeachdb bits. That makes sense because it's used to populate the table with information for databases that are not online (so you couldn't use sp_msforeachdb to switch context to those DBs).
Just go to the original post and scroll all the way to the bottom of the first code block posted; she even bolded the query causing the error, and it's clearly not within sp_msforeachdb.
Cheers!
January 24, 2016 at 6:09 am
Here is an alternative method for collecting this (almost the same) information in a single dynamic sql query.
😎
DECLARE @SQL_STR NVARCHAR(MAX) = N'';
DECLARE @OUTER_SQL NVARCHAR(MAX) = N'
;WITH FILE_INFO AS
(
SELECT
SDB.database_id
,QUOTENAME(SDB.name) AS DATABASE_NAME
,STUFF(
(
SELECT DISTINCT
NCHAR(44) + SUBSTRING(SSMF.physical_name,1,1)
FROM sys.master_files SSMF
WHERE SSMF.database_id = SDB.database_id
FOR XML PATH(''''),TYPE
).value(''(./text())[1]'',''NVARCHAR(64)'')
,1,1,N'''') AS DRIVE_LETTERS
,SDB.state_desc
,SDB.recovery_model_desc
,SUM(CASE WHEN SMF.type_desc = N''ROWS'' THEN CONVERT(NUMERIC(28,2),SMF.size * 0.0078125,0) ELSE 0 END) AS DB_SIZE
,SUM(CASE WHEN SMF.type_desc = N''LOG'' THEN CONVERT(NUMERIC(28,2),SMF.size * 0.0078125,0) ELSE 0 END) AS LOG_SIZE
,SUM(CASE WHEN SMF.type_desc = N''ROWS'' THEN
CASE
WHEN SMF.max_size IN (268435456,-1) THEN -1
ELSE CONVERT(NUMERIC(28,2),SMF.max_size * 0.0078125,0)
END
ELSE 0
END) AS DB_MAX_SIZE
,SUM(CASE WHEN SMF.type_desc = N''LOG'' THEN
CASE
WHEN SMF.max_size IN (268435456,-1) THEN -1
ELSE CONVERT(NUMERIC(28,2),SMF.max_size * 0.0078125,0)
END
ELSE 0
END) AS LOG_MAX_SIZE
FROM sys.databases SDB
INNER JOIN sys.master_files SMF
ON SDB.database_id = SMF.database_id
GROUP BY SDB.database_id
,SDB.name
,SDB.state_desc
,SDB.recovery_model_desc
)
SELECT
FI.database_id
,FI.DATABASE_NAME
,FI.DRIVE_LETTERS
,FI.state_desc
,FI.recovery_model_desc
,FI.DB_SIZE
,FI.LOG_SIZE
,FI.DB_MAX_SIZE
,FI.LOG_MAX_SIZE
,XINF.SYS_TBL_TOTAL
,XINF.SYS_TBL_USED
,XINF.SYS_TBL_DATA
,XINF.INT_TBL_TOTAL
,XINF.INT_TBL_USED
,XINF.INT_TBL_DATA
,XINF.USR_TBL_TOT_IRD
,XINF.USR_TBL_TOT_LOB
,XINF.USR_TBL_TOT_ROD
,XINF.USR_TBL_TOT_DRP
,XINF.USR_TBL_USED_IRD
,XINF.USR_TBL_USED_LOB
,XINF.USR_TBL_USED_ROD
,XINF.USR_TBL_USED_DRP
,XINF.USR_TBL_DPG_IRD
,XINF.USR_TBL_DPG_LOB
,XINF.USR_TBL_DPG_ROD
,XINF.USR_TBL_DPG_DRP
FROM FILE_INFO FI
LEFT OUTER JOIN
(
{{@SQL_STR}}
) AS XINF
ON FI.DATABASE_NAME = XINF.DATABASE_NAME
ORDER BY FI.DATABASE_NAME;';
DECLARE @DB_QRY NVARCHAR(MAX) = N'
UNION ALL
SELECT
N''{{@DBNAME}}'' AS DATABASE_NAME --OBJECT_NAME(SIDX.object_id)
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''SYSTEM_TABLE'' THEN SAU.total_pages ELSE 0 END ) AS SYS_TBL_TOTAL
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''SYSTEM_TABLE'' THEN SAU.used_pages ELSE 0 END ) AS SYS_TBL_USED
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''SYSTEM_TABLE'' THEN SAU.data_pages ELSE 0 END ) AS SYS_TBL_DATA
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''INTERNAL_TABLE'' THEN SAU.total_pages ELSE 0 END ) AS INT_TBL_TOTAL
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''INTERNAL_TABLE'' THEN SAU.used_pages ELSE 0 END ) AS INT_TBL_USED
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''INTERNAL_TABLE'' THEN SAU.data_pages ELSE 0 END ) AS INT_TBL_DATA
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''IN_ROW_DATA'' THEN SAU.total_pages ELSE 0 END ) AS USR_TBL_TOT_IRD
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''LOB_DATA'' THEN SAU.total_pages ELSE 0 END ) AS USR_TBL_TOT_LOB
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''ROW_OVERFLOW_DATA'' THEN SAU.total_pages ELSE 0 END ) AS USR_TBL_TOT_ROD
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''DROPPED'' THEN SAU.total_pages ELSE 0 END ) AS USR_TBL_TOT_DRP
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''IN_ROW_DATA'' THEN SAU.used_pages ELSE 0 END ) AS USR_TBL_USED_IRD
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''LOB_DATA'' THEN SAU.used_pages ELSE 0 END ) AS USR_TBL_USED_LOB
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''ROW_OVERFLOW_DATA'' THEN SAU.used_pages ELSE 0 END ) AS USR_TBL_USED_ROD
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''DROPPED'' THEN SAU.used_pages ELSE 0 END ) AS USR_TBL_USED_DRP
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''IN_ROW_DATA'' THEN SAU.data_pages ELSE 0 END ) AS USR_TBL_DPG_IRD
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''LOB_DATA'' THEN SAU.data_pages ELSE 0 END ) AS USR_TBL_DPG_LOB
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''ROW_OVERFLOW_DATA'' THEN SAU.data_pages ELSE 0 END ) AS USR_TBL_DPG_ROD
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''DROPPED'' THEN SAU.data_pages ELSE 0 END ) AS USR_TBL_DPG_DRP
FROM {{@DBNAME}}.sys.indexes SIDX WITH (NOLOCK)
INNER JOIN {{@DBNAME}}.sys.partitions SPRT WITH (NOLOCK)
ON SIDX.object_id = SPRT.object_id
AND SIDX.index_id = SPRT.index_id
INNER JOIN {{@DBNAME}}.sys.allocation_units SAU WITH (NOLOCK)
ON SAU.container_id = CASE
WHEN SAU.type = 2 THEN SPRT.partition_id
ELSE SPRT.hobt_id
END
LEFT OUTER JOIN {{@DBNAME}}.sys.internal_tables SITT WITH (NOLOCK)
ON SIDX.object_id = SITT.object_id
LEFT OUTER JOIN {{@DBNAME}}.sys.objects SOBJ WITH (NOLOCK)
ON SIDX.object_id = SOBJ.object_id
';
SELECT @SQL_STR =
STUFF(
(
SELECT
REPLACE(@DB_QRY,N'{{@DBNAME}}', QUOTENAME(SDB.name))
FROM sys.databases SDB
WHERE SDB.state_desc = N'ONLINE'
FOR XML PATH(''),TYPE
).value('(./text())[1]','NVARCHAR(MAX)'),1,11,'');
SELECT @OUTER_SQL = REPLACE(@OUTER_SQL,N'{{@SQL_STR}}',@SQL_STR);
--SELECT @OUTER_SQL
EXEC (@OUTER_SQL);
January 24, 2016 at 6:13 am
Brandie Tarvin (1/22/2016)
... I have a bit of code that monitors my database growth.
Quick question, are you intentionally returning one row for each database file?
😎
January 26, 2016 at 7:42 am
Eirikur Eiriksson (1/24/2016)
Brandie Tarvin (1/22/2016)
... I have a bit of code that monitors my database growth.Quick question, are you intentionally returning one row for each database file?
😎
Yes, because some of these databases are spread out among multiple drives.
But I'll cop to the fact that I got this script off the internet and didn't make too many changes to it in order to use it for my needs. So maybe I need to readdress that. Actually, I'll wait until we get our new servers where we'll have more than enough space to chunk everything on the same drives again.
As far as database_files vs master_files... I'll check that as soon as I get free time today. Thank you everyone for your responses. Now this whole thing is starting to make sense to me.
January 26, 2016 at 7:51 am
Other than me setting a VARCHAR column as NOT NULL in #dbfreesize, I did see the difference between using the local file tables (you'd think I would know better) and the master_files table.
Now I get to play with getting the size of an offline database. Fun stuff, Maynard!
Thanks everyone for your help. I appreciate it.
January 26, 2016 at 10:22 am
Final (working) code for those who are interested.
------------------------------Data file size----------------------------
IF (SELECT OBJECT_ID('tempdb..#dbsize')) IS NOT NULL
DROP TABLE #dbsize;
CREATE TABLE #dbsize
(Dbname SYSNAME NOT NULL,driveletter CHAR(1) NULL, DBFileName SYSNAME NOT NULL,
dbstatus VARCHAR(50),Recovery_Model VARCHAR(40) NULL DEFAULT ('NA'),
file_Size_MB DECIMAL(30,2) NULL DEFAULT (0),
Space_Used_MB DECIMAL(30,2) NULL DEFAULT (0),
Free_Space_MB DECIMAL(30,2) NULL DEFAULT (0));
GO
INSERT INTO #dbsize(Dbname, DBFileName, driveletter,dbstatus,Recovery_Model,
file_Size_MB,Space_Used_MB,Free_Space_MB)
EXEC sp_msforeachdb
'use [?];
SELECT DB_NAME() AS DbName, name AS DBFileName, SUBSTRING(physical_name,1,1),
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, name, physical_name';
GO
-------------------log size--------------------------------------
IF (SELECT OBJECT_ID('tempdb..#logsize') ) IS NOT NULL
DROP TABLE #logsize;
CREATE TABLE #logsize
(Dbname SYSNAME NOT NULL, DriveLetter CHAR(1) NULL,
Log_File_Size_MB DECIMAL(38,2) NULL DEFAULT (0),
log_Space_Used_MB DECIMAL(30,2) NULL DEFAULT (0),
log_Free_Space_MB DECIMAL(30,2) NULL DEFAULT (0));
GO
INSERT INTO #logsize(Dbname, DriveLetter,Log_File_Size_MB,
log_Space_Used_MB,log_Free_Space_MB)
EXEC sp_msforeachdb
'use [?];
SELECT DB_NAME() AS DbName, SUBSTRING(physical_name,1,1),
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, physical_name';
GO
--------------------------------database free size
IF (SELECT OBJECT_ID('tempdb..#dbfreesize') ) IS NOT NULL
DROP TABLE #dbfreesize;
CREATE TABLE #dbfreesize
(name SYSNAME NOT NULL, database_size VARCHAR(50) NOT NULL,
Freespace VARCHAR(50) NOT NULL DEFAULT (0.00));
INSERT INTO #dbfreesize(name,database_size,Freespace)
EXEC sp_msforeachdb
'use [?];SELECT database_name = db_name()
,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize)
+ convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'')
,''unallocated space'' = ltrim(str((
CASE
WHEN dbsize >= reservedpages
THEN (convert(DECIMAL(15, 2), dbsize)
- convert(DECIMAL(15, 2), reservedpages)) * 8192
/ 1048576
ELSE 0
END
), 15, 2) + '' MB'')
FROM (
SELECT dbsize = sum(convert(BIGINT, CASE
WHEN type = 0
THEN size
ELSE 0
END))
,logsize = sum(convert(BIGINT, CASE
WHEN type <> 0
THEN size
ELSE 0
END))
FROM sys.database_files
) AS files
,(
SELECT reservedpages = sum(a.total_pages)
,usedpages = sum(a.used_pages)
,pages = sum(CASE
WHEN it.internal_type IN (
202
,204
,211
,212
,213
,214
,215
,216
)
THEN 0
WHEN a.type <> 1
THEN a.used_pages
WHEN p.index_id < 2
THEN a.data_pages
ELSE 0
END)
FROM sys.partitions p
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
LEFT JOIN sys.internal_TABLEs it
ON p.object_id = it.object_id
) AS partitions';
-----------------------------------
IF (SELECT OBJECT_ID('tempdb..#alldbstate') ) IS NOT NULL
DROP TABLE #alldbstate;
CREATE TABLE #alldbstate
(dbname SYSNAME NOT NULL, driveletter CHAR(1) NULL,
DBFileName SYSNAME NOT NULL,
DBstatus VARCHAR(55) NOT NULL, R_model VARCHAR(30) NOT NULL);
INSERT INTO #alldbstate (dbname,driveletter,DBFileName,DBstatus,R_model)
SELECT sd.name,SUBSTRING(df.physical_name,1,1), df.physical_name ,
CONVERT(VARCHAR(20),DATABASEPROPERTYEX(sd.name,'status')),
sd.recovery_model_desc
FROM sys.databases sd
INNER JOIN sys.master_files df
ON sd.name = df.name;
INSERT INTO #dbsize(Dbname,driveletter,DBFileName,dbstatus,Recovery_Model)
SELECT dbname,driveletter,DBFileName,dbstatus,R_model FROM #alldbstate
WHERE DBstatus NOT IN ('ONLINE','RESTORING');
INSERT INTO #logsize(Dbname)
SELECT dbname FROM #alldbstate WHERE DBstatus NOT IN ('ONLINE','RESTORING');
INSERT INTO #dbfreesize(name,database_size)
SELECT name, LTRIM(STR((CONVERT(DECIMAL(15, 2), dbsize)
+ CONVERT(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2)
+ 'MB') AS DatabaseSize
FROM (
SELECT SUM(CONVERT(BIGINT, CASE WHEN type = 0 THEN size
ELSE 0 END)) AS dbsize,
SUM(CONVERT(BIGINT, CASE WHEN type <> 0 THEN size ELSE 0 END)) AS logsize,
d.Name
FROM sys.master_files mf
INNER JOIN sys.databases d
ON mf.database_id = d.database_id
WHERE d.state_desc NOT IN ('ONLINE','RESTORING')
GROUP BY d.name
) AS files
INNER JOIN #alldbstate ad
ON ad.dbname = files.name
WHERE DBstatus NOT IN ('ONLINE','RESTORING');
INSERT INTO CreditDBA_Admin.dbo.DBSizeMonitor (DBName, DBFileName,
DriveLetter, DBStatus, Recovery_Model, DBSize, File_Size_MB,
Space_Used_MB, Free_Space_MB, Log_File_Size_MB, Log_Space_Used_MB,
Log_Free_Space_MB, DB_FreeSpace, LogDateTime)
SELECT d.Dbname,d.DBFileName, d.driveletter, d.dbstatus,d.Recovery_Model,
(file_size_mb + log_file_size_mb) as DBsize, d.file_Size_MB,d.Space_Used_MB,
d.Free_Space_MB,l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,
fs.Freespace as DB_Freespace, GETDATE() AS LogDateTime
FROM #dbsize d
INNER JOIN #logsize l
ON d.Dbname=l.Dbname
INNER JOIN #dbfreesize fs
ON d.Dbname=fs.name
ORDER BY d.driveletter DESC, d.Free_Space_MB DESC, d.Dbname;
January 26, 2016 at 10:28 am
Brandie Tarvin (1/26/2016)
Final (working) code for those who are interested....
That's going to give incorrect results. For the insert into #alldbstate, you're still joining sys.databases and sys.master_files on the database name being equal to the file name. That's not guaranteed to be true for any file, and can be true for at most one file per database.
You'll probably want to change the join between sys.master_files and sys.database_files to be on database_id instead.
Cheers!
January 26, 2016 at 10:32 am
Jacob Wilkins (1/26/2016)
Brandie Tarvin (1/26/2016)
Final (working) code for those who are interested....
That's going to give incorrect results. For the insert into #alldbstate, you're still joining sys.databases and sys.master_files on the database name being equal to the file name. That's not guaranteed to be true for any file, and can be true for at most one file per database.
You'll probably want to change the join between sys.master_files and sys.database_files to be on database_id instead.
Cheers!
GAHHHH. I thought I fixed that. I must have copied from the wrong window.
Thank you for that catch.
January 26, 2016 at 10:37 am
Final, final (we hope working) code for those who are interested.
------------------------------Data file size----------------------------
IF (SELECT OBJECT_ID('tempdb..#dbsize')) IS NOT NULL
DROP TABLE #dbsize;
CREATE TABLE #dbsize
(Dbname SYSNAME NOT NULL,driveletter CHAR(1) NULL, DBFileName SYSNAME NOT NULL,
dbstatus VARCHAR(50),Recovery_Model VARCHAR(40) NULL DEFAULT ('NA'),
file_Size_MB DECIMAL(30,2) NULL DEFAULT (0),
Space_Used_MB DECIMAL(30,2) NULL DEFAULT (0),
Free_Space_MB DECIMAL(30,2) NULL DEFAULT (0));
GO
INSERT INTO #dbsize(Dbname, DBFileName, driveletter,dbstatus,Recovery_Model,
file_Size_MB,Space_Used_MB,Free_Space_MB)
EXEC sp_msforeachdb
'use [?];
SELECT DB_NAME() AS DbName, name AS DBFileName, SUBSTRING(physical_name,1,1),
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, name, physical_name';
GO
-------------------log size--------------------------------------
IF (SELECT OBJECT_ID('tempdb..#logsize') ) IS NOT NULL
DROP TABLE #logsize;
CREATE TABLE #logsize
(Dbname SYSNAME NOT NULL, DriveLetter CHAR(1) NULL,
Log_File_Size_MB DECIMAL(38,2) NULL DEFAULT (0),
log_Space_Used_MB DECIMAL(30,2) NULL DEFAULT (0),
log_Free_Space_MB DECIMAL(30,2) NULL DEFAULT (0));
GO
INSERT INTO #logsize(Dbname, DriveLetter,Log_File_Size_MB,
log_Space_Used_MB,log_Free_Space_MB)
EXEC sp_msforeachdb
'use [?];
SELECT DB_NAME() AS DbName, SUBSTRING(physical_name,1,1),
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, physical_name';
GO
--------------------------------database free size
IF (SELECT OBJECT_ID('tempdb..#dbfreesize') ) IS NOT NULL
DROP TABLE #dbfreesize;
CREATE TABLE #dbfreesize
(name SYSNAME NOT NULL, database_size VARCHAR(50) NOT NULL,
Freespace VARCHAR(50) NOT NULL DEFAULT (0.00));
INSERT INTO #dbfreesize(name,database_size,Freespace)
EXEC sp_msforeachdb
'use [?];SELECT database_name = db_name()
,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize)
+ convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'')
,''unallocated space'' = ltrim(str((
CASE
WHEN dbsize >= reservedpages
THEN (convert(DECIMAL(15, 2), dbsize)
- convert(DECIMAL(15, 2), reservedpages)) * 8192
/ 1048576
ELSE 0
END
), 15, 2) + '' MB'')
FROM (
SELECT dbsize = sum(convert(BIGINT, CASE
WHEN type = 0
THEN size
ELSE 0
END))
,logsize = sum(convert(BIGINT, CASE
WHEN type <> 0
THEN size
ELSE 0
END))
FROM sys.database_files
) AS files
,(
SELECT reservedpages = sum(a.total_pages)
,usedpages = sum(a.used_pages)
,pages = sum(CASE
WHEN it.internal_type IN (
202
,204
,211
,212
,213
,214
,215
,216
)
THEN 0
WHEN a.type <> 1
THEN a.used_pages
WHEN p.index_id < 2
THEN a.data_pages
ELSE 0
END)
FROM sys.partitions p
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
LEFT JOIN sys.internal_TABLEs it
ON p.object_id = it.object_id
) AS partitions';
-----------------------------------
IF (SELECT OBJECT_ID('tempdb..#alldbstate') ) IS NOT NULL
DROP TABLE #alldbstate;
CREATE TABLE #alldbstate
(dbname SYSNAME NOT NULL, driveletter CHAR(1) NULL,
DBFileName SYSNAME NOT NULL,
DBstatus VARCHAR(55) NOT NULL, R_model VARCHAR(30) NOT NULL);
INSERT INTO #alldbstate (dbname,driveletter,DBFileName,DBstatus,R_model)
SELECT sd.name,SUBSTRING(df.physical_name,1,1), df.physical_name ,
CONVERT(VARCHAR(20),DATABASEPROPERTYEX(sd.name,'status')),
sd.recovery_model_desc
FROM sys.databases sd
INNER JOIN sys.master_files df
ON sd.database_id= df.database_id;
INSERT INTO #dbsize(Dbname,driveletter,DBFileName,dbstatus,Recovery_Model)
SELECT dbname,driveletter,DBFileName,dbstatus,R_model FROM #alldbstate
WHERE DBstatus NOT IN ('ONLINE','RESTORING');
INSERT INTO #logsize(Dbname)
SELECT dbname FROM #alldbstate WHERE DBstatus NOT IN ('ONLINE','RESTORING');
INSERT INTO #dbfreesize(name,database_size)
SELECT name, LTRIM(STR((CONVERT(DECIMAL(15, 2), dbsize)
+ CONVERT(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2)
+ 'MB') AS DatabaseSize
FROM (
SELECT SUM(CONVERT(BIGINT, CASE WHEN type = 0 THEN size
ELSE 0 END)) AS dbsize,
SUM(CONVERT(BIGINT, CASE WHEN type <> 0 THEN size ELSE 0 END)) AS logsize,
d.Name
FROM sys.master_files mf
INNER JOIN sys.databases d
ON mf.database_id = d.database_id
WHERE d.state_desc NOT IN ('ONLINE','RESTORING')
GROUP BY d.name
) AS files
INNER JOIN #alldbstate ad
ON ad.dbname = files.name
WHERE DBstatus NOT IN ('ONLINE','RESTORING');
INSERT INTO CreditDBA_Admin.dbo.DBSizeMonitor (DBName, DBFileName,
DriveLetter, DBStatus, Recovery_Model, DBSize, File_Size_MB,
Space_Used_MB, Free_Space_MB, Log_File_Size_MB, Log_Space_Used_MB,
Log_Free_Space_MB, DB_FreeSpace, LogDateTime)
SELECT d.Dbname,d.DBFileName, d.driveletter, d.dbstatus,d.Recovery_Model,
(file_size_mb + log_file_size_mb) as DBsize, d.file_Size_MB,d.Space_Used_MB,
d.Free_Space_MB,l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,
fs.Freespace as DB_Freespace, GETDATE() AS LogDateTime
FROM #dbsize d
INNER JOIN #logsize l
ON d.Dbname=l.Dbname
INNER JOIN #dbfreesize fs
ON d.Dbname=fs.name
ORDER BY d.driveletter DESC, d.Free_Space_MB DESC, d.Dbname;
January 26, 2016 at 10:40 am
Eirikur Eiriksson (1/24/2016)
Here is an alternative method for collecting this (almost the same) information in a single dynamic sql query.😎
DECLARE @SQL_STR NVARCHAR(MAX) = N'';DECLARE @OUTER_SQL NVARCHAR(MAX) = N'
;WITH FILE_INFO AS
(
SELECT
SDB.database_id
,QUOTENAME(SDB.name) AS DATABASE_NAME
,STUFF(
(
SELECT DISTINCT
NCHAR(44) + SUBSTRING(SSMF.physical_name,1,1)
FROM sys.master_files SSMF
WHERE SSMF.database_id = SDB.database_id
FOR XML PATH(''''),TYPE
).value(''(./text())[1]'',''NVARCHAR(64)'')
,1,1,N'''') AS DRIVE_LETTERS
,SDB.state_desc
,SDB.recovery_model_desc
,SUM(CASE WHEN SMF.type_desc = N''ROWS'' THEN CONVERT(NUMERIC(28,2),SMF.size * 0.0078125,0) ELSE 0 END) AS DB_SIZE
,SUM(CASE WHEN SMF.type_desc = N''LOG'' THEN CONVERT(NUMERIC(28,2),SMF.size * 0.0078125,0) ELSE 0 END) AS LOG_SIZE
,SUM(CASE WHEN SMF.type_desc = N''ROWS'' THEN
CASE
WHEN SMF.max_size IN (268435456,-1) THEN -1
ELSE CONVERT(NUMERIC(28,2),SMF.max_size * 0.0078125,0)
END
ELSE 0
END) AS DB_MAX_SIZE
,SUM(CASE WHEN SMF.type_desc = N''LOG'' THEN
CASE
WHEN SMF.max_size IN (268435456,-1) THEN -1
ELSE CONVERT(NUMERIC(28,2),SMF.max_size * 0.0078125,0)
END
ELSE 0
END) AS LOG_MAX_SIZE
FROM sys.databases SDB
INNER JOIN sys.master_files SMF
ON SDB.database_id = SMF.database_id
GROUP BY SDB.database_id
,SDB.name
,SDB.state_desc
,SDB.recovery_model_desc
)
SELECT
FI.database_id
,FI.DATABASE_NAME
,FI.DRIVE_LETTERS
,FI.state_desc
,FI.recovery_model_desc
,FI.DB_SIZE
,FI.LOG_SIZE
,FI.DB_MAX_SIZE
,FI.LOG_MAX_SIZE
,XINF.SYS_TBL_TOTAL
,XINF.SYS_TBL_USED
,XINF.SYS_TBL_DATA
,XINF.INT_TBL_TOTAL
,XINF.INT_TBL_USED
,XINF.INT_TBL_DATA
,XINF.USR_TBL_TOT_IRD
,XINF.USR_TBL_TOT_LOB
,XINF.USR_TBL_TOT_ROD
,XINF.USR_TBL_TOT_DRP
,XINF.USR_TBL_USED_IRD
,XINF.USR_TBL_USED_LOB
,XINF.USR_TBL_USED_ROD
,XINF.USR_TBL_USED_DRP
,XINF.USR_TBL_DPG_IRD
,XINF.USR_TBL_DPG_LOB
,XINF.USR_TBL_DPG_ROD
,XINF.USR_TBL_DPG_DRP
FROM FILE_INFO FI
LEFT OUTER JOIN
(
{{@SQL_STR}}
) AS XINF
ON FI.DATABASE_NAME = XINF.DATABASE_NAME
ORDER BY FI.DATABASE_NAME;';
DECLARE @DB_QRY NVARCHAR(MAX) = N'
UNION ALL
SELECT
N''{{@DBNAME}}'' AS DATABASE_NAME --OBJECT_NAME(SIDX.object_id)
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''SYSTEM_TABLE'' THEN SAU.total_pages ELSE 0 END ) AS SYS_TBL_TOTAL
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''SYSTEM_TABLE'' THEN SAU.used_pages ELSE 0 END ) AS SYS_TBL_USED
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''SYSTEM_TABLE'' THEN SAU.data_pages ELSE 0 END ) AS SYS_TBL_DATA
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''INTERNAL_TABLE'' THEN SAU.total_pages ELSE 0 END ) AS INT_TBL_TOTAL
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''INTERNAL_TABLE'' THEN SAU.used_pages ELSE 0 END ) AS INT_TBL_USED
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''INTERNAL_TABLE'' THEN SAU.data_pages ELSE 0 END ) AS INT_TBL_DATA
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''IN_ROW_DATA'' THEN SAU.total_pages ELSE 0 END ) AS USR_TBL_TOT_IRD
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''LOB_DATA'' THEN SAU.total_pages ELSE 0 END ) AS USR_TBL_TOT_LOB
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''ROW_OVERFLOW_DATA'' THEN SAU.total_pages ELSE 0 END ) AS USR_TBL_TOT_ROD
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''DROPPED'' THEN SAU.total_pages ELSE 0 END ) AS USR_TBL_TOT_DRP
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''IN_ROW_DATA'' THEN SAU.used_pages ELSE 0 END ) AS USR_TBL_USED_IRD
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''LOB_DATA'' THEN SAU.used_pages ELSE 0 END ) AS USR_TBL_USED_LOB
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''ROW_OVERFLOW_DATA'' THEN SAU.used_pages ELSE 0 END ) AS USR_TBL_USED_ROD
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''DROPPED'' THEN SAU.used_pages ELSE 0 END ) AS USR_TBL_USED_DRP
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''IN_ROW_DATA'' THEN SAU.data_pages ELSE 0 END ) AS USR_TBL_DPG_IRD
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''LOB_DATA'' THEN SAU.data_pages ELSE 0 END ) AS USR_TBL_DPG_LOB
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''ROW_OVERFLOW_DATA'' THEN SAU.data_pages ELSE 0 END ) AS USR_TBL_DPG_ROD
,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''DROPPED'' THEN SAU.data_pages ELSE 0 END ) AS USR_TBL_DPG_DRP
FROM {{@DBNAME}}.sys.indexes SIDX WITH (NOLOCK)
INNER JOIN {{@DBNAME}}.sys.partitions SPRT WITH (NOLOCK)
ON SIDX.object_id = SPRT.object_id
AND SIDX.index_id = SPRT.index_id
INNER JOIN {{@DBNAME}}.sys.allocation_units SAU WITH (NOLOCK)
ON SAU.container_id = CASE
WHEN SAU.type = 2 THEN SPRT.partition_id
ELSE SPRT.hobt_id
END
LEFT OUTER JOIN {{@DBNAME}}.sys.internal_tables SITT WITH (NOLOCK)
ON SIDX.object_id = SITT.object_id
LEFT OUTER JOIN {{@DBNAME}}.sys.objects SOBJ WITH (NOLOCK)
ON SIDX.object_id = SOBJ.object_id
';
SELECT @SQL_STR =
STUFF(
(
SELECT
REPLACE(@DB_QRY,N'{{@DBNAME}}', QUOTENAME(SDB.name))
FROM sys.databases SDB
WHERE SDB.state_desc = N'ONLINE'
FOR XML PATH(''),TYPE
).value('(./text())[1]','NVARCHAR(MAX)'),1,11,'');
SELECT @OUTER_SQL = REPLACE(@OUTER_SQL,N'{{@SQL_STR}}',@SQL_STR);
--SELECT @OUTER_SQL
EXEC (@OUTER_SQL);
Now that I've got my code working, I'll deconstruct this. I need more larnin' on the XML stuff anyway.
Viewing 13 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply