Database Free Space Sp_spaceused
-
derekr 43208 (10/25/2011)
Apologies for that - Doesnt look goodPlease see attached
C:\Users\derekr\Desktop\Result.jpg
I can't see what's on your c drive. You need to upload it here.
If you want you can copy to url of the file uploaded and put it in img tags. That way it's visible in your post.
-
With that
Nothing
-
Ninja's_RGR'us (10/25/2011)
derekr 43208 (10/25/2011)
Apologies for that - Doesnt look goodPlease see attached
C:\Users\derekr\Desktop\Result.jpg
I can't see what's on your c drive. You need to upload it here.
If you want you can copy to url of the file uploaded and put it in img tags. That way it's viisble in your post.
Thats what I did
I put the location between the img tags
-
derekr 43208 (10/25/2011)
Ninja's_RGR'us (10/25/2011)
derekr 43208 (10/25/2011)
Apologies for that - Doesnt look goodPlease see attached
C:\Users\derekr\Desktop\Result.jpg
I can't see what's on your c drive. You need to upload it here.
If you want you can copy to url of the file uploaded and put it in img tags. That way it's viisble in your post.
Thats what I did
I put the location between the img tags
You didn't upload the file to this server. Hence only you can see it.
Bottom right when you write on post (Edit attachements)
-
Done
-
In a nutshell - What I wanted was to be able to report on the free space in a database as a percentage
With what you gave me, I did this
CREATE TABLE #dbs(
DBNAME sysname
, DBID INT
, [Total Size in MB] INT
, [Available Space In MB] INT
, DriveLetter CHAR(1)
)
INSERT INTO
#dbs
(
DBNAME
, DBID
, [Total Size in MB]
, [Available Space in MB]
, DriveLetter
)
EXEC sp_MSforeachdb '
USE [?];
SELECT
DB_NAME() As DBNAME
, DB_ID() AS DBID
, SUM(size / 128) AS ''TotalSizeMB''
, SUM(size / 128 - CAST(FILEPROPERTY(name , ''SpaceUsed'') AS int) / 128) AS ''AvailableSpaceMB''
, LEFT(physical_name, 1) AS DriveLetter
FROM
[?].sys.database_files
WHERE
type_desc = ''ROWS''
GROUP BY LEFT(physical_name, 1) '
Select DBNAME, [Total Size in MB], [Available Space in MB]
,PercentFree = cast([Available Space in MB]as decimal (8,2))/cast([Total Size in MB] as decimal (8,2))*100
from #dbs
drop table #dbs
-
The real purpuse of the script I gave you was to do forecasting and growth planning.
Maybe when you're ready ;-).
-
Thanks
I get the jist of it but not yet confident enough to deploy
I like to understand every single line of the code in the script I use before deploying.
Thanks for your effort.
-
Hi Ninja
When using the following
EXEC sp_MSforeachdb 'USE [?];
SELECT
DB_NAME() As DBNAME
, DB_ID() AS DBID
, SUM(size / 128) AS ''TotalSizeMB''
, SUM(size / 128 - CAST(FILEPROPERTY(name , ''SpaceUsed'') AS int) / 128) AS ''AvailableSpaceMB''
, LEFT(physical_name, 1) AS DriveLetter
FROM
[?].sys.database_files
WHERE
type_desc = ''ROWS''
GROUP BY LEFT(physical_name, 1) '
I need to get an accurate free space amount, down to the decimal points
How can I get this to give me the decimal points after the amount, eg. 250.49
I tried the CAST function but it returns 250.00 and I know that it's definately 240.49
Thanks
-
How's this?
DECLARE @sql AS VARCHAR(MAX)SELECT @sql = COALESCE(@SQL + '; ', '') + sql_command
FROM (SELECT 'USE ' + QUOTENAME(name) +
' SELECT DB_NAME() As DBNAME,
DB_ID() AS DBID, SUM(size / 128.0) AS TotalSizeMB,
SUM(size / 128.0 - CAST(FILEPROPERTY(name , ''SpaceUsed'') AS int) / 128.0) AS AvailableSpaceMB,
LEFT(physical_name, 1) AS DriveLetter
FROM sys.database_files
WHERE type_desc = ''ROWS''
GROUP BY LEFT(physical_name, 1)' AS sql_command
FROM sys.databases
) a
EXEC(@SQL)
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
You're that short on space that a few KB matters??
That's a whole other problem ;-).
As previously mentionned use / 128.0. That way it auto-casts to decimal.
-
Ha Ha - No
Not short at all
I'm just trying to get a 100% percentage accurate amount before I add in the
PercentFree = AvailableSpaceMB/TotalSizeMB*100 column -
1 of those numbers need to be a decimal.
int / int * int = int.
Try with 100.0 (yes 100 dot 0).
-
Hi Ninja
I tried this
CREATE TABLE #dbs(
DBNAME sysname
, DBID INT
, [Total Size in MB] INT
, [Available Space In MB] INT
, DriveLetter CHAR(1)
)
INSERT INTO
#dbs
(
DBNAME
, DBID
, [Total Size in MB]
, [Available Space in MB]
, DriveLetter
)
EXEC sp_MSforeachdb '
USE [?];
SELECT
DB_NAME() As DBNAME
, DB_ID() AS DBID
, SUM(size / 128.0) AS ''TotalSizeMB''
, SUM(size / 128.0 - CAST(FILEPROPERTY(name , ''SpaceUsed'') AS int) / 128.0) AS ''AvailableSpaceMB''
, LEFT(physical_name, 1) AS DriveLetter
FROM
[?].sys.database_files
WHERE
type_desc = ''ROWS''
GROUP BY LEFT(physical_name, 1) '
Select DBNAME, cast([Total Size in MB] as decimal (8,2)) , cast([Available Space In MB] as decimal(8,2))
,PercentFree = cast([Available Space in MB]as decimal (8,2))/cast([Total Size in MB] as decimal (8,2))*100.0
from #dbs
drop table #dbs
It shows the percent free as a decimal with the proper decimal numbers
It does not show the proper decimals for the Total and Available size
-
Cadavre (10/26/2011)
How's this?DECLARE @sql AS VARCHAR(MAX)SELECT @sql = COALESCE(@SQL + '; ', '') + sql_command
FROM (SELECT 'USE ' + QUOTENAME(name) +
' SELECT DB_NAME() As DBNAME,
DB_ID() AS DBID, SUM(size / 128.0) AS TotalSizeMB,
SUM(size / 128.0 - CAST(FILEPROPERTY(name , ''SpaceUsed'') AS int) / 128.0) AS AvailableSpaceMB,
LEFT(physical_name, 1) AS DriveLetter
FROM sys.database_files
WHERE type_desc = ''ROWS''
GROUP BY LEFT(physical_name, 1)' AS sql_command
FROM sys.databases
) a
EXEC(@SQL)
Cadavre
Unfortuanately I need to be able to put it into a Temp table
I tried but it said that I cannot insert from a select statement that has a variable
Something like that
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply