How to find allocated space and used space

Question

Post reply

How to find allocated space and used space

RK2012

SSC Eights!

Points: 960
More actions
December 17, 2012 at 10:32 pm

#393809

Hello Team,
How to find allocated space and used space for all databases in sqlserver instance ?
Is there any query.
Thanks in advance

Viewing 10 posts - 1 through 9 (of 9 total)

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

SQL Show SSCertifiable Points: 6143 More actions · Answer 1

SQL Show

SSCertifiable

Points: 6143

December 17, 2012 at 10:40 pm

#1569369

Google it sp_spaceused.

Richard Fryar Hall of Fame Points: 3457 More actions · Answer 2

You can see this in Management Studio.

1. Click on "Databases" in Object Explorer

2. Select menu item "View -> Object Explorer Details" - a table is displayed showing all databases and few properties, including Size (MB)

3. Right click on one of the column headings and make sure "Space Available (KB)" is checked. You can uncheck other columns if you don't need them

Voila

Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com

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

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) +

'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)

),

'& # x 0 D ;',CHAR(13) + CHAR(10)

)

--SELECT @sql

EXECUTE sp_executesql @sql

This loops through all DB's pulling out the free space, used space, total space per file.

Just remove the spaces between '& # x 0 D ;'

RK2012 SSC Eights! Points: 960 More actions · Answer 4

Thanks anthony

I have removed '& # x 0 D ;' in script it is not working

Can you please give me modified script

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

RK2012 (12/18/2012)
Can you please give me modified script

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) +

'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)

)

print @sql

EXECUTE sp_executesql @sql

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

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

don't remove the whole thing, just remove the spaces.

the string of characters is special and they are removed from posts hence why I need to put spaces in so that you can see the full script

'& # x 0 D ;'

'&#x 0D;'

Just remove the space between the x and the 0

jyuan68 Old Hand Points: 310 More actions · Answer 7

For database log file space stats,you can run " dbcc sqlperf ".

dbcc sqlperf('LOGSPACE')

RK2012 SSC Eights! Points: 960 More actions · Answer 8

Hello Team,

I am using below query to get size_mb and unused space in database

Is there any way to get instance level or report ?

Query:

select

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)) ,

NAME = left(a.NAME,15),

FILENAME = left(a.FILENAME,30)

from

dbo.sysfiles a

Result

---------

Field id FILE_SIZE_MB Space_used_mb free_space_mb name filename

14.00 2.94 1.06 masterC:\Program Files (x86)\Microso

21.25 0.64 0.61 mastlogC:\Program Files (x86)\Microso

Kindly help me

Thanks in advance

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

There is a script here to help you with that.

http://jasonbrimhall.info/2012/07/17/a-trio-of-eachdb/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events