December 7, 2012 at 12:22 pm
Hi how to do you find the available free space when you right click the db > select tasks > shrink > files > in the pop up window you see "available free space"?
I know you can use sp_spaceused to get available free space, but this available free space is different from when you navigate on mgt studio and try to shrink a file.
My ultimate goal is to find all db's that have available shrink file free space of let's say over 500mb? So I know exactly which db's has room to shrink.
I know you have to be careful with fragmentation with shrinking data files but this is a dev environment our goal here is not performance just space. Let me know thank you very much.
December 7, 2012 at 1:40 pm
the easiest way to get all the information is the FILEPROPERTY function, such as:
SELECT DB_NAME() AS database_name, name AS file_name, physical_name,
size/128.0 AS size_meg,
size/128.0 - FILEPROPERTY(name, 'SpaceUsed')/128.0 AS available_meg
FROM sys.database_files
December 7, 2012 at 3:49 pm
THank you so much for leading me in the right direction Chris! I have modified your script to run against all db's and return the necessary information...the query is set up in WHERE clause to look for all DBs with available free space of 1gb and of that 1gb over 50% is available to shrink
exec sp_MSforeachdb '
use ?
SELECT DB_NAME() AS database_name, name AS file_name, physical_name,
size/128.0 AS size_meg,
size/128.0 - FILEPROPERTY(name, ''SpaceUsed'')/128.0 AS available_meg,
round(size/128.0 - FILEPROPERTY(name, ''SpaceUsed'')/128.0,4) / round(size/128.0,4) * 100 AS AvailableShrinkPercent
FROM sys.database_files
WHERE size/128.0 - FILEPROPERTY(name, ''SpaceUsed'')/128.0 > 1000 and round(size/128.0 - FILEPROPERTY(name, ''SpaceUsed'')/128.0,4) / round(size/128.0,4) * 100 > 50
'
I don't know if it's a bug on our systems or what but for some reason only the tempdb in one of our sql2005 instance returns the wrong available free space compared to what the shrink file gui in mgt studio shows...
anyways problem solved thanks for the help!
December 7, 2012 at 4:47 pm
JP10 (12/7/2012)
THank you so much for leading me in the right direction Chris! I have modified your script to run against all db's and return the necessary information...the query is set up in WHERE clause to look for all DBs with available free space of 1gb and of that 1gb over 50% is available to shrinkexec sp_MSforeachdb '
use ?
SELECT DB_NAME() AS database_name, name AS file_name, physical_name,
size/128.0 AS size_meg,
size/128.0 - FILEPROPERTY(name, ''SpaceUsed'')/128.0 AS available_meg,
round(size/128.0 - FILEPROPERTY(name, ''SpaceUsed'')/128.0,4) / round(size/128.0,4) * 100 AS AvailableShrinkPercent
FROM sys.database_files
WHERE size/128.0 - FILEPROPERTY(name, ''SpaceUsed'')/128.0 > 1000 and round(size/128.0 - FILEPROPERTY(name, ''SpaceUsed'')/128.0,4) / round(size/128.0,4) * 100 > 50
'
I don't know if it's a bug on our systems or what but for some reason only the tempdb in one of our sql2005 instance returns the wrong available free space compared to what the shrink file gui in mgt studio shows...
anyways problem solved thanks for the help!
You can use the script on this link to get various space used analysis of all databases on a server.
Get Server Database File Information
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058
Show Database File Details
Total by Database and File
Total by Database and Filegroup
Total by Database and Filegroup Type
Total by Disk, Database, and Filepath
Total by Disk and Database
Total by Database
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply