script

Question

Post reply

script

ramyours2003

SSChampion

Points: 12268
More actions
February 24, 2012 at 12:43 am

#251227

I want to find the total size , free space and used space by all the databases in sql ?
can nay one send the script .

Viewing 13 posts - 1 through 12 (of 12 total)

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

sp_msforeachdb 'USE [?];

select

DatabaseName = DB_NAME() ,

CollectionDate = CONVERT(DATE,GETDATE()),

a.FILEID,

[FILE_SIZE_MB] =

convert(decimal(12,2),round(a.size/128.000,2)),

[SPACE_USED_MB] =

convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)),

[FREE_SPACE_MB] =

convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) ,

a.NAME,

a.FILENAME

from

dbo.sysfiles a'

Loundy SSCertifiable Points: 6080 More actions · Answer 2

anthony.green (2/24/2012)
sp_msforeachdb 'USE [?];
select
DatabaseName = DB_NAME() ,
CollectionDate = CONVERT(DATE,GETDATE()),
a.FILEID,
[FILE_SIZE_MB] =
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) ,
a.NAME,
a.FILENAME
from
dbo.sysfiles a'

This is a nice script bud - cheers for this 🙂

[font="Times New Roman"]There's no kill switch on awesome![/font]

ramyours2003 SSChampion Points: 12268 More actions · Answer 3

thanks for sending , i would like

DB name, DB size, Free space ,used space

instead of geeting the file sze deatails separatley .

Ant-Green SSC Guru Points: 113507 More actions · Answer 4

anthony.green (2/24/2012)
sp_msforeachdb 'USE [?];
select
DatabaseName = DB_NAME() ,
CollectionDate = CONVERT(DATE,GETDATE()),
a.FILEID,
[FILE_SIZE_MB] =
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) ,
a.NAME,
a.FILENAME
from
dbo.sysfiles a'

Not a problem, its how I manage growth, it runs every day, inserts into a table, then does yesterdays free space - todays free space and then sends an email. I use it in conjunction with xp_fixeddrives to get drive space as well so I can plan drive increases

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

ramyours2003 (2/24/2012)
thanks for sending , i would like
DB name, DB size, Free space ,used space
instead of geeting the file sze deatails separatley .

use that script as a starting point and use group by instead and only select the fields you want

Cadavre SSC-Forever Points: 41690 More actions · Answer 6

sp_msforeachdb is known to miss databases and has problems with names of databases (e.g. call you database "I am a [bracket]" then see what happens to sp_msforeachdb)

This is ~~how~~one way to do it dynamically: -

DECLARE @sql NVARCHAR(MAX)

SELECT @sql = REPLACE(CAST((

SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +

'SELECT ' + CHAR(13) + CHAR(10) +

'DatabaseName = DB_NAME(), ' + CHAR(13) + CHAR(10) +

'CollectionDate = CONVERT(DATE, GETDATE()), ' + CHAR(13) + CHAR(10) +

'a.FILEID, ' + CHAR(13) + CHAR(10) +

'[FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)), ' + CHAR(13) + CHAR(10) +

'[SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +')

/ 128.000, 2)), ' + CHAR(13) + CHAR(10) +

'[FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +'))

/ 128.000, 2)), ' + CHAR(13) + CHAR(10) +

'a.NAME, a.FILENAME ' + CHAR(13) + CHAR(10) +

'FROM dbo.sysfiles a;' + CHAR(13) + CHAR(10)

FROM sys.databases

FOR XML PATH('')) AS NVARCHAR(MAX)),' ',CHAR(13) + CHAR(10))

EXECUTE sp_executesql @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/

Ant-Green SSC Guru Points: 113507 More actions · Answer 7

Thanks Cadavre. Touch wood, not had a problem with sp_MSForEachDB so far, but thats not to say that I wont in the furture.

I'll review your script and build it into my monitoring DB creation script.

Thanks

Ant-Green SSC Guru Points: 113507 More actions · Answer 8

seems to be a problem with the chars putting in &#x0D

USE [master]; SELECT DatabaseName = DB_NAME(), CollectionD

Sure I can figure it out, time to brush up on my FOR XML, its been a while

Cadavre SSC-Forever Points: 41690 More actions · Answer 9

anthony.green (2/24/2012)
seems to be a problem with the chars putting in &#x0D
USE [master]; SELECT DatabaseName = DB_NAME(), CollectionD
Sure I can figure it out, time to brush up on my FOR XML, its been a while

The problem is with the forum, which replaced "& # x 0 D ;" (no spaces or quotations) with a line break in the code block. If you add "& # x 0 D ;" (no spaces or quotations) back into the replace, just before the CHAR(13) + CHAR(10) at the end, then it'll work.

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/

Ant-Green SSC Guru Points: 113507 More actions · Answer 10

Ant-Green

SSC Guru

Points: 113507

February 24, 2012 at 6:09 am

#1451343

yep thanks got it. cheers again

salliven SSCrazy Points: 2644 More actions · Answer 11

Please check this query:

SELECT name, physical_name, type_desc, size as used_space,

CASE max_size WHEN -1 THEN 'N/A' ELSE CAST(max_size - size AS NVARCHAR) END as free_space,

CASE max_size WHEN -1 THEN 'No limit' ELSE CAST(max_size as nvarchar) END as max_size

FROM master.sys.master_files

ORDER BY DATABASE_ID, name, physical_name

Ant-Green SSC Guru Points: 113507 More actions · Answer 12

thanks but that doesnt get free space if the file has unrestricted growth. how do you determine how full the file is to stop SQL auto growing the file and manually sizing DB's as per best practise with this?

it also doesnt give you the actual file size so you cant see how big the DB is, only how much of the DB is used

I might have a 200GB DB with only 1GB used, I would want to know that I have 199GB free which is what the OP wants

he wants, total size, free space and used space