QUERY: GET SPACE USED/FREE in All Databases

Question

QUERY: GET SPACE USED/FREE in All Databases

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

For someone who has made so much hay off helping others slay RBAR processing, I don't blame you one iota for not wanting to review something you have no opportunity of fixing. Just to add insult to injury for the guy who put his best foot forward to get the foreach db proc fixed, the connect item earned the dreaded "Closed as Won't Fix"

https://connect.microsoft.com/SQLServer/feedback/details/752629/disable-sp-msforeachdb-by-default-or-fix-it

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Orlando Colamatteo (2/15/2016)

For someone who has made so much hay off helping others slay RBAR processing, I don't blame you one iota for not wanting to review something you have no opportunity of fixing. Just to add insult to injury for the guy who put his best foot forward to get the foreach db proc fixed, the connect item earned the dreaded "Closed as Won't Fix"

https://connect.microsoft.com/SQLServer/feedback/details/752629/disable-sp-msforeachdb-by-default-or-fix-it%5B/quote%5D

What really ticks me off about that CONNECT item (I've seen it before) is that it IS an internal sproc that IS used by MS somewhere in SQL Server (as you suggested, Maintenance Plans?). It's apparently been proven to skip items and that means it's broken but MS just keeps on using it. Heh... the same person that wrote sp_SpaceUsed (why on Earth would they have written it to handle just one table and still resort to RBAR?) must have written the sp_msforeach procs.

Hat's off to Aaron for giving it the try with MS. I gave up on CONNECT a long time ago. IIRC, the built in numbers table or function request is a decade or so old and it's still open. I even posted about it to try to drum up support for it (got more than 200 additional votes for it) and nothing has happened with it. Erland Sommarskog must be fit to be tied.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

Jeff Moden (2/15/2016)

Orlando Colamatteo (2/15/2016)
For someone who has made so much hay off helping others slay RBAR processing, I don't blame you one iota for not wanting to review something you have no opportunity of fixing. Just to add insult to injury for the guy who put his best foot forward to get the foreach db proc fixed, the connect item earned the dreaded "Closed as Won't Fix"
https://connect.microsoft.com/SQLServer/feedback/details/752629/disable-sp-msforeachdb-by-default-or-fix-it%5B/quote%5D
What really ticks me off about that CONNECT item (I've seen it before) is that it IS an internal sproc that IS used by MS somewhere in SQL Server (as you suggested, Maintenance Plans?). It's apparently been proven to skip items and that means it's broken but MS just keeps on using it. Heh... the same person that wrote sp_SpaceUsed (why on Earth would they have written it to handle just one table and still resort to RBAR?) must have written the sp_msforeach procs.

Now that's something I'm going to investigate when I get home. In the end, MS is going to fix what they want to fix and ignore everything else, but I still want to know.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

SQL_Elvis SSC-Addicted Points: 421 More actions · Answer 4

SQL_Elvis

SSC-Addicted

Points: 421

July 28, 2016 at 9:07 am

#1892500

Removed

usenet SSC Eights! Points: 809 More actions · Answer 5

The last query posted by SQL_Elvis is actually wrong:
CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT) UsedSpaceMB,
should be

CAST(FILEPROPERTY(name,''SpaceUsed'')/128 AS INT) UsedSpaceMB,

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

usenet - Wednesday, April 5, 2017 4:19 AM
The last query posted by SQL_Elvis is actually wrong:
CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT) UsedSpaceMB,
should be
CAST(FILEPROPERTY(name,''SpaceUsed'')/128 AS INT) UsedSpaceMB,

Good eye. Hopefully, it not being caught until 9 months later means that no one has used it yet and hasn't been burned by the result... yet.

@SQL_Elvis... if you're still around, can you edit the code?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

usenet SSC Eights! Points: 809 More actions · Answer 7

actually, there is another "glitch" in the code:
The column "DbName" should read logical_filename instead, since it is definitely not the database name.
I've made some adjustments to the code and added bringing in the growth settings, since I usually need both information at the same time. Revised script is:

DECLARE @DBName varchar(300) DECLARE @sql varchar(max)

SELECT @sql =
'DECLARE @TABLE TABLE
( DBName varchar(255),
LogicalFileName VARCHAR(255),
Filelocation VARCHAR(255),
CurrentSizeMB DECIMAL(20,2),
UsedSpaceMB DECIMAL(20,2),
FreeSpaceMB DECIMAL(20,2),
State tinyint,
max_sizeMB int,
GrowthMB_or_Percent int,
is_percent_growth bit,
can_grow bit,
is_readonly bit,
is_media_read_only bit
) ' + CHAR(13)

EXECUTE(@SQL)

Declare mycursor CURSOR for
select name from SYS.databases s
order by s.name

open mycursor

fetch next from mycursor into @DBName

while (@@FETCH_STATUS <> -1)
BEGIN

SELECT @sql = @sql +
'USE [' +@DBName+']

INSERT INTO @TABLE
SELECT DB_Name() as DBName,
name AS LogicalFileName,
physical_name Filelocation,
size/128.0 AS CurrentSizeMB,
CAST(FILEPROPERTY(name,''SpaceUsed'')/128 AS INT) UsedSpaceMB,
size/128.0 - CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB ,
state ,
case when (max_size = -1 or max_size=0) then max_size else max_size / 128 END,
case when is_percent_growth = 1 then growth else growth / 128 end,
is_percent_growth, case when growth=0 then 0 else 1 end, is_read_only, is_media_read_only

FROM sys.database_files; ' + CHAR(13)

fetch next from mycursor into @dbname
END

CLOSE mycursor
DEALLOCATE mycursor

SELECT @sql = @sql + ' SELECT * FROM @TABLE ORDER BY LogicalFileName ;'
EXECUTE(@SQL)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

usenet - Wednesday, April 5, 2017 7:43 AM
actually, there is another "glitch" in the code:
The column "DbName" should read logical_filename instead, since it is definitely not the database name.
I've made some adjustments to the code and added bringing in the growth settings, since I usually need both information at the same time. Revised script is:
DECLARE @DBName varchar(300) DECLARE @sql varchar(max)
SELECT @sql =
'DECLARE @TABLE TABLE
( DBName varchar(255),
LogicalFileName VARCHAR(255),
Filelocation VARCHAR(255),
CurrentSizeMB DECIMAL(20,2),
UsedSpaceMB DECIMAL(20,2),
FreeSpaceMB DECIMAL(20,2),
State tinyint,
max_sizeMB int,
GrowthMB_or_Percent int,
is_percent_growth bit,
can_grow bit,
is_readonly bit,
is_media_read_only bit
) ' + CHAR(13)
EXECUTE(@SQL)
Declare mycursor CURSOR for
select name from SYS.databases s
order by s.name
open mycursor
fetch next from mycursor into @DBName
while (@@FETCH_STATUS <> -1)
BEGIN
SELECT @sql = @sql +
'USE [' +@DBName+']
INSERT INTO @TABLE
SELECT DB_Name() as DBName,
name AS LogicalFileName,
physical_name Filelocation,
size/128.0 AS CurrentSizeMB,
CAST(FILEPROPERTY(name,''SpaceUsed'')/128 AS INT) UsedSpaceMB,
size/128.0 - CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB ,
state ,
case when (max_size = -1 or max_size=0) then max_size else max_size / 128 END,
case when is_percent_growth = 1 then growth else growth / 128 end,
is_percent_growth, case when growth=0 then 0 else 1 end, is_read_only, is_media_read_only
FROM sys.database_files; ' + CHAR(13)
fetch next from mycursor into @dbname
END
CLOSE mycursor
DEALLOCATE mycursor
SELECT @sql = @sql + ' SELECT * FROM @TABLE ORDER BY LogicalFileName ;'
EXECUTE(@SQL)

Wouldn't it be easier just to add some functions to a call on sys.Master_Files? No cursor required. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

usenet SSC Eights! Points: 809 More actions · Answer 9

Jeff, i tend to disagree.
If you want to get the actual usage information (via fileproperty or sp_spaceused) is only available for the current database. So you need to loop through all databases one way or another and get the connection context into that db.
that's why i use this script.
if there is another way to do it without a cursor, i am all ears - never too old to learn a new trick!

bjorn.c.w Valued Member Points: 73 More actions · Answer 10

Also,
you can add this to the select-statement for the cursor:

select name from SYS.databases s WHERE state = 0

The script will fail if it hits on databases that is put Offline for a reason.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 11

usenet - Wednesday, April 5, 2017 9:01 AM
Jeff, i tend to disagree.
If you want to get the actual usage information (via fileproperty or sp_spaceused) is only available for the current database. So you need to loop through all databases one way or another and get the connection context into that db.
that's why i use this script.
if there is another way to do it without a cursor, i am all ears - never too old to learn a new trick!

Apologies... I didn't see the Used/Free space information in your code before. I do now. But that does bring up another question... what do you actually do with that information? Hopefully, you don't use it to justify a shrink of any data files.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

usenet SSC Eights! Points: 809 More actions · Answer 12

No, of course not. This information is part of my daily run of checks on 50+ SQL Instances, goes into a dashboard and triggers proactive growth of data files when they near fullness.
Or, when databases grow too fast too often, they get migrated to another system. We have instances that are cheaper (mostly for legacy or low frequency usage), others are more expensive (Enterprise Edition, multi node AlwaysOn etc). So we try to have the databases with easy load on the cheaper ones, unless the patterns change. And the actual data usage is one small part of the pattern

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 13

usenet - Friday, September 8, 2017 10:06 AM
No, of course not. This information is part of my daily run of checks on 50+ SQL Instances, goes into a dashboard and triggers proactive growth of data files when they near fullness.
Or, when databases grow too fast too often, they get migrated to another system. We have instances that are cheaper (mostly for legacy or low frequency usage), others are more expensive (Enterprise Edition, multi node AlwaysOn etc). So we try to have the databases with easy load on the cheaper ones, unless the patterns change. And the actual data usage is one small part of the pattern

Heh... sorry... Had to ask that question. A lot of people do such crazy things.

Very good into on what you use the information for. Thanks for taking the time to post it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

usenet SSC Eights! Points: 809 More actions · Answer 14

Yes, I've seen too many instances where the accidental DBA was shrinking data files every night.

Always fun to see their faces when you come in, see that, rebuild all indexes and then ask them "show me your performance issue again, please".

About the actual free / used data, that information is worth a lot in terms of capacity planning. Since I track this for every DB across the clients, it's easy to predict how much storage a new server will need, based on x years of past data for the DBs 🙂

Only reliable to a certain degree, but so far never let me down - with the notable exception of Sharepoint databases, which are a completely different beast

And while I have your attention: I'd like to take the time to say a heartfelt THANK YOU to you for giving so much here and on your blog. You are doing a great job serving the community with your knowledge and experience!

Best regards,

Andy

Squatt Gmail SSC-Addicted Points: 422 More actions · Answer 15

And to add to the warnings about sp_msforeachdb or sp_msforeachtable, if you work with silly people that have used Reserved Names for objects, well... you can guess the rest.

JSC