Database Free Space

Hi,
I have below SQL Qurey which returns the used space for the database,
SELECT sum(convert(dec(15), reserved)) FROM DBNAME..sysindexes WHERE
indid in (0,1,255)
The above query returns the value in MB's or kb's which I am unable to
verify ?
Below is another SQL query which returns the database size in MB's
which I have verified with sp_spaceused.
SELECT sum(convert(dec(15),size))/128 FROM DBNAME..sysfiles
If the above queries are correct then if I calculate the free space, I
get incorrect results.
FreeSpace = select ((SELECT sum(convert(dec(15),size))/128 FROM
master..sysfiles) - (SELECT sum(convert(dec(15), reserved)) FROM
master..sysindexes WHERE indid in (0,1,255)))
I have crosschecked the same with sp_spaceused. Is anything wrong here?
Thanks,
Regards,
PramodHi
Have you looked at the source code for sp_spaceused as you seem to be trying
to replicate what it does!
John
"ipramod@.gmail.com" wrote:
> Hi,
> I have below SQL Qurey which returns the used space for the database,
> SELECT sum(convert(dec(15), reserved)) FROM DBNAME..sysindexes WHERE
> indid in (0,1,255)
> The above query returns the value in MB's or kb's which I am unable to
> verify ?
> Below is another SQL query which returns the database size in MB's
> which I have verified with sp_spaceused.
> SELECT sum(convert(dec(15),size))/128 FROM DBNAME..sysfiles
> If the above queries are correct then if I calculate the free space, I
> get incorrect results.
> FreeSpace = select ((SELECT sum(convert(dec(15),size))/128 FROM
> master..sysfiles) - (SELECT sum(convert(dec(15), reserved)) FROM
> master..sysindexes WHERE indid in (0,1,255)))
> I have crosschecked the same with sp_spaceused. Is anything wrong here?
> Thanks,
> Regards,
> Pramod
>|||Hi, Pramond
For normal indexes (between 2 and 254), the reserved column in
sysindexes returns the number of pages allocated for that index. A page
has a size of 8KB. If an index is a clustered index (indid=1), then it
also contains the number of pages allocated for the data in the table.
If a table has no index, there is a row in sysindexes with indid=0,
which returns the number of pages allocated for all the data in the
table. For tables that contain text and image data, there is also a row
in sysindexes with indid=255, which returns the number of pages
allocated for text and image data.
In conclusion, you need to consider all indexes, not only the ones with
indid in (0,1,255), so try:
select ((SELECT sum(convert(dec(15),size))/128 FROM master..sysfiles)
- (SELECT sum(convert(dec(15), reserved)/128) FROM master..sysindexes))
The above query returns the size (in MB) of unallocated pages. You may
have more unused space in the allocated data pages, which is not
returned by the above query.
Razvan
ipramod@.gmail.com wrote:
> Hi,
> I have below SQL Qurey which returns the used space for the database,
> SELECT sum(convert(dec(15), reserved)) FROM DBNAME..sysindexes WHERE
> indid in (0,1,255)
> The above query returns the value in MB's or kb's which I am unable to
> verify ?
> Below is another SQL query which returns the database size in MB's
> which I have verified with sp_spaceused.
> SELECT sum(convert(dec(15),size))/128 FROM DBNAME..sysfiles
> If the above queries are correct then if I calculate the free space, I
> get incorrect results.
> FreeSpace = select ((SELECT sum(convert(dec(15),size))/128 FROM
> master..sysfiles) - (SELECT sum(convert(dec(15), reserved)) FROM
> master..sysindexes WHERE indid in (0,1,255)))
> I have crosschecked the same with sp_spaceused. Is anything wrong here?
> Thanks,
> Regards,
> Pramod|||Hi
Thanks for your feedback.
I think the value returned by the query specified by you, still does
not give the correct result as per sp_spaceused.
Suppose I have a test database for which I execute sp_spaceused which
is as below:
database_name database_size unallocated space
----
test 1200.00 MB 929.55 MB
reserved data index_size unused
-- -- --
--
72136 KB 54712 KB 9544 KB 7880 KB
Now if I execute the query suggested by you...
select ((SELECT sum(convert(dec(15),size))/128 FROM test..sysfiles)
- (SELECT sum(convert(dec(15), reserved)/128) FROM test..sysindexes))
It gives me the unallocated space as 1134.570332 MB which is not
correct as per sp_spaceused (sp_spaceused returned 929.55 MB).
I think the query specified by you does not the exact space used by the
database.
Please give me the correct query to get the unallocated space for a
database.
Thanks,
Regards,
Pramod
Razvan Socol wrote:
> Hi, Pramond
> For normal indexes (between 2 and 254), the reserved column in
> sysindexes returns the number of pages allocated for that index. A page
> has a size of 8KB. If an index is a clustered index (indid=1), then it
> also contains the number of pages allocated for the data in the table.
> If a table has no index, there is a row in sysindexes with indid=0,
> which returns the number of pages allocated for all the data in the
> table. For tables that contain text and image data, there is also a row
> in sysindexes with indid=255, which returns the number of pages
> allocated for text and image data.
> In conclusion, you need to consider all indexes, not only the ones with
> indid in (0,1,255), so try:
> select ((SELECT sum(convert(dec(15),size))/128 FROM master..sysfiles)
> - (SELECT sum(convert(dec(15), reserved)/128) FROM master..sysindexes))
> The above query returns the size (in MB) of unallocated pages. You may
> have more unused space in the allocated data pages, which is not
> returned by the above query.
> Razvan
> ipramod@.gmail.com wrote:
> > Hi,
> >
> > I have below SQL Qurey which returns the used space for the database,
> > SELECT sum(convert(dec(15), reserved)) FROM DBNAME..sysindexes WHERE
> > indid in (0,1,255)
> >
> > The above query returns the value in MB's or kb's which I am unable to
> > verify ?
> >
> > Below is another SQL query which returns the database size in MB's
> > which I have verified with sp_spaceused.
> > SELECT sum(convert(dec(15),size))/128 FROM DBNAME..sysfiles
> >
> > If the above queries are correct then if I calculate the free space, I
> > get incorrect results.
> > FreeSpace = select ((SELECT sum(convert(dec(15),size))/128 FROM
> > master..sysfiles) - (SELECT sum(convert(dec(15), reserved)) FROM
> > master..sysindexes WHERE indid in (0,1,255)))
> >
> > I have crosschecked the same with sp_spaceused. Is anything wrong here?
> >
> > Thanks,
> > Regards,
> > Pramod|||If you want the value returned by sp_spaceused, then use somethink like
this (adapted from the code found in sp_spaceused):
select ltrim(str(((
select sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 = 0)
) - (
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
)) / (
select 1048576 / low
from master.dbo.spt_values
where number = 1 and type = 'E'
),15,2)+ ' MB')
Razvan|||Hi Razvan,
I have sorted out the issue with the help of storedproc sp_spaceused
Below is the query to get the database size, unallocated size and the %
free space and all in MB's
declare @.pages bigint
,@.dbname sysname
,@.dbsize bigint
,@.logsize bigint
,@.reservedpages bigint
,@.unallocatedsize bigint
,@.totalsize bigint
select @.dbsize = sum(convert(bigint,case when status & 64 = 0 then size
else 0 end)), @.logsize = sum(convert(bigint,case when status & 64 <> 0
then size else 0 end))
from dbo.sysfiles
select @.reservedpages = sum(a.total_pages)
from sys.partitions p join sys.allocation_units a on p.partition_id =a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
select @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
(15,2),@.logsize))/128.00
select @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert (dec
(15,2),@.reservedpages)) * 8192 / 1048576
select 'DatabaseSize(MB)'=@.totalsize,
'UnallocatedSize(MB)'=@.unallocatedsize,
'DatabaseFreeSpaceInPercent(MB)'=(@.unallocatedsize*1.00/@.totalsize)*100.00
Please let me know if I am wrong.
Thanks,
Regards,
Pramod
Razvan Socol wrote:
> If you want the value returned by sp_spaceused, then use somethink like
> this (adapted from the code found in sp_spaceused):
> select ltrim(str(((
> select sum(convert(dec(15),size))
> from dbo.sysfiles
> where (status & 64 = 0)
> ) - (
> select sum(convert(dec(15),reserved))
> from sysindexes
> where indid in (0, 1, 255)
> )) / (
> select 1048576 / low
> from master.dbo.spt_values
> where number = 1 and type = 'E'
> ),15,2)+ ' MB')
> Razvan|||Hi Razvan,
Thanks for your feedback.
Now, I have another question.
I have a variable @.dbsize to which I am assigning the value of database
size and I am using the variable value in the code
Below is my SQL query which returns the database free space in percent
for all the databases.
SET nocount on
DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
master..sysdatabases
OPEN AllDatabaseInfo
IF object_id('tempdb..#test2') IS NOT NULL
BEGIN
DROP TABLE #test2
END
CREATE TABLE #test2 (
[Database Name] [varchar] (1000),
[Database Space Available] [varchar] (1000)
)
IF object_id('tempdb..#test3') IS NOT NULL
BEGIN
DROP TABLE #test3
END
CREATE TABLE #test3 (
[dbsize] [varchar] (1000),
[logsize] [varchar] (1000)
)
DELETE FROM #test2
DECLARE @.DBName nvarchar(1000)
DECLARE @.sql nvarchar(1000)
DECLARE @.str sysname
SET @.sql = ''
SET @.DBName = ''
DECLARE @.pages bigint
,@.dbsize bigint
,@.logsize bigint
,@.reservedpages bigint
,@.unallocatedsize bigint
,@.totalsize bigint
FETCH NEXT FROM AllDatabaseInfo into @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
--
--EXEC sp_MSForeachdb 'use [?]; select
db_name();select @.dbsize =sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
@.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
0 end))FROM ?.dbo.sysfiles'
SELECT @.dbsize = sum(convert(bigint,case when status
& 64 = 0 then
size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
<> 0 then size else 0 end))
FROM dbo.sysfiles
SELECT @.reservedpages = sum(a.total_pages)
FROM sys.partitions p join sys.allocation_units a on
p.partition_id
= a.container_id
left join sys.internal_tables it on p.object_id =it.object_id
SELECT @.totalsize=(convert (dec (15,2),@.dbsize) +
convert (dec
(15,2),@.logsize))/128.00
SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize)
- convert
(dec (15,2),@.reservedpages)) * 8192 / 1048576
--
SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
15,2)
SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName
+ ''', ' +
@.str
EXEC sp_executesql @.sql
FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
END
CLOSE AllDatabaseInfo
DEALLOCATE AllDatabaseInfo
SELECT * FROM #test2
SET nocount off
Now this code returns the free space value in percent only for one
database because I am unable to substitute the database name when I
calculate the @.dbsize.
Can you help me?
Thanks,
Regards,
Pramod
ipramod@.gmail.com wrote:
> Hi Razvan,
> I have sorted out the issue with the help of storedproc sp_spaceused
> Below is the query to get the database size, unallocated size and the %
> free space and all in MB's
> declare @.pages bigint
> ,@.dbname sysname
> ,@.dbsize bigint
> ,@.logsize bigint
> ,@.reservedpages bigint
> ,@.unallocatedsize bigint
> ,@.totalsize bigint
> select @.dbsize = sum(convert(bigint,case when status & 64 = 0 then size
> else 0 end)), @.logsize = sum(convert(bigint,case when status & 64 <> 0
> then size else 0 end))
> from dbo.sysfiles
> select @.reservedpages = sum(a.total_pages)
> from sys.partitions p join sys.allocation_units a on p.partition_id => a.container_id
> left join sys.internal_tables it on p.object_id = it.object_id
> select @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
> (15,2),@.logsize))/128.00
> select @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert (dec
> (15,2),@.reservedpages)) * 8192 / 1048576
> select 'DatabaseSize(MB)'=@.totalsize,
> 'UnallocatedSize(MB)'=@.unallocatedsize,
> 'DatabaseFreeSpaceInPercent(MB)'=(@.unallocatedsize*1.00/@.totalsize)*100.00
>
> Please let me know if I am wrong.
> Thanks,
> Regards,
> Pramod
>
> Razvan Socol wrote:
> > If you want the value returned by sp_spaceused, then use somethink like
> > this (adapted from the code found in sp_spaceused):
> >
> > select ltrim(str(((
> > select sum(convert(dec(15),size))
> > from dbo.sysfiles
> > where (status & 64 = 0)
> > ) - (
> > select sum(convert(dec(15),reserved))
> > from sysindexes
> > where indid in (0, 1, 255)
> > )) / (
> > select 1048576 / low
> > from master.dbo.spt_values
> > where number = 1 and type = 'E'
> > ),15,2)+ ' MB')
> >
> > Razvan|||Hi,
I have sorted out the issue by using the temporary tables. I have used
your suggestion and in the 'exec' itself I have inserted the variable
values in the temporary table and it worked. Thanks for your feedback
guys :)
I am copying the solution here, plz take a look and let me know if I am
wrong and if possible give me another solution. Also, can you tell me
is there any disadvantages of having temp tables in the query?
SET nocount on
DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
master..sysdatabases
OPEN AllDatabaseInfo
IF object_id('tempdb..#test2') IS NOT NULL
BEGIN
DROP TABLE #test2
END
CREATE TABLE #test2 (
[Database Name] [varchar] (1000),
[Database Space Available] [varchar] (1000)
)
DELETE FROM #test2
IF object_id('tempdb..#test3') IS NOT NULL
BEGIN
DROP TABLE #test3
END
CREATE TABLE #test3 (
[DatabaseSize] [bigint],
[LogSize] [bigint]
)
DELETE FROM #test3
DECLARE @.DBName nvarchar(1000)
DECLARE @.sql nvarchar(1000)
DECLARE @.str sysname
SET @.sql = ''
SET @.DBName = ''
DECLARE @.pages bigint
,@.dbsize bigint
,@.logsize bigint
,@.reservedpages bigint
,@.unallocatedsize float
,@.totalsize float
FETCH NEXT FROM AllDatabaseInfo into @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = N'DECLARE @.dbsize1 bigint,@.logsize1
bigint;
SELECT @.dbsize1 = sum(convert(bigint,case when
status & 64 = 0 then
size else 0 end)), @.logsize1 = sum(convert(bigint,case when status & 64
<> 0 then size else 0 end))
FROM ['+ @.DBname +'].dbo.sysfiles;
INSERT INTO #test3 SELECT @.dbsize1, @.logsize1;'
EXEC sp_executesql @.sql
SELECT @.dbsize=[DatabaseSize], @.logsize=[LogSize]
FROM #test3
SET @.sql = N'DECLARE @.reservedpages1 bigint;
SELECT @.reservedpages1 = sum(a.total_pages)
FROM ['+ @.DBname +'].sys.partitions p join ['+
@.DBname
+'].sys.allocation_units a on p.partition_id = a.container_id
left join ['+ @.DBname +'].sys.internal_tables
it on p.object_id =it.object_id;
INSERT INTO #test3 SELECT @.reservedpages1, 0;'
EXEC sp_executesql @.sql
SELECT @.reservedpages=[DatabaseSize] FROM #test3
SELECT @.totalsize=(convert (dec (15,2),@.dbsize)*1.00
+ convert (dec
(15,2),@.logsize))*1.00/128.00
SELECT @.unallocatedsize=(convert (dec
(15,2),@.dbsize)*1.00 -
convert (dec (15,2),@.reservedpages)*1.00) * 8192.00 / 1048576.00
SET @.str =str((@.unallocatedsize*1.00/@.totalsize*1.00)*100.00, 15,2)
SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName
+ ''', ' +
@.str
EXEC sp_executesql @.sql
FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
END
CLOSE AllDatabaseInfo
DEALLOCATE AllDatabaseInfo
SELECT * FROM #test2
SET nocount off
Thanks,
Pramod
ipramod@.gmail.com wrote:
> Hi Razvan,
> Thanks for your feedback.
> Now, I have another question.
> I have a variable @.dbsize to which I am assigning the value of database
> size and I am using the variable value in the code
> Below is my SQL query which returns the database free space in percent
> for all the databases.
> SET nocount on
> DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
> master..sysdatabases
>
> OPEN AllDatabaseInfo
>
> IF object_id('tempdb..#test2') IS NOT NULL
> BEGIN
> DROP TABLE #test2
> END
>
> CREATE TABLE #test2 (
> [Database Name] [varchar] (1000),
> [Database Space Available] [varchar] (1000)
> )
>
> IF object_id('tempdb..#test3') IS NOT NULL
> BEGIN
> DROP TABLE #test3
> END
>
> CREATE TABLE #test3 (
> [dbsize] [varchar] (1000),
> [logsize] [varchar] (1000)
> )
>
> DELETE FROM #test2
> DECLARE @.DBName nvarchar(1000)
> DECLARE @.sql nvarchar(1000)
> DECLARE @.str sysname
> SET @.sql = ''
> SET @.DBName = ''
> DECLARE @.pages bigint
> ,@.dbsize bigint
> ,@.logsize bigint
> ,@.reservedpages bigint
> ,@.unallocatedsize bigint
> ,@.totalsize bigint
>
> FETCH NEXT FROM AllDatabaseInfo into @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> --
> --EXEC sp_MSForeachdb 'use [?]; select
> db_name();select @.dbsize => sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
> @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
> 0 end))FROM ?.dbo.sysfiles'
>
> SELECT @.dbsize = sum(convert(bigint,case when status
> & 64 = 0 then
> size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
> <> 0 then size else 0 end))
> FROM dbo.sysfiles
>
> SELECT @.reservedpages = sum(a.total_pages)
> FROM sys.partitions p join sys.allocation_units a on
> p.partition_id
> = a.container_id
> left join sys.internal_tables it on p.object_id => it.object_id
>
> SELECT @.totalsize=(convert (dec (15,2),@.dbsize) +
> convert (dec
> (15,2),@.logsize))/128.00
> SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize)
> - convert
> (dec (15,2),@.reservedpages)) * 8192 / 1048576
> --
>
> SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
> 15,2)
> SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName
> + ''', ' +
> @.str
> EXEC sp_executesql @.sql
> FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
> END
>
> CLOSE AllDatabaseInfo
> DEALLOCATE AllDatabaseInfo
>
> SELECT * FROM #test2
> SET nocount off
>
> Now this code returns the free space value in percent only for one
> database because I am unable to substitute the database name when I
> calculate the @.dbsize.
>
> Can you help me?
>
> Thanks,
> Regards,
> Pramod
>
> ipramod@.gmail.com wrote:
> > Hi Razvan,
> >
> > I have sorted out the issue with the help of storedproc sp_spaceused
> >
> > Below is the query to get the database size, unallocated size and the %
> > free space and all in MB's
> >
> > declare @.pages bigint
> > ,@.dbname sysname
> > ,@.dbsize bigint
> > ,@.logsize bigint
> > ,@.reservedpages bigint
> > ,@.unallocatedsize bigint
> > ,@.totalsize bigint
> >
> > select @.dbsize = sum(convert(bigint,case when status & 64 = 0 then size
> > else 0 end)), @.logsize = sum(convert(bigint,case when status & 64 <> 0
> > then size else 0 end))
> > from dbo.sysfiles
> >
> > select @.reservedpages = sum(a.total_pages)
> > from sys.partitions p join sys.allocation_units a on p.partition_id => > a.container_id
> > left join sys.internal_tables it on p.object_id = it.object_id
> >
> > select @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
> > (15,2),@.logsize))/128.00
> > select @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert (dec
> > (15,2),@.reservedpages)) * 8192 / 1048576
> >
> > select 'DatabaseSize(MB)'=@.totalsize,
> > 'UnallocatedSize(MB)'=@.unallocatedsize,
> > 'DatabaseFreeSpaceInPercent(MB)'=(@.unallocatedsize*1.00/@.totalsize)*100.00
> >
> >
> > Please let me know if I am wrong.
> >
> > Thanks,
> > Regards,
> > Pramod
> >
> >
> > Razvan Socol wrote:
> > > If you want the value returned by sp_spaceused, then use somethink like
> > > this (adapted from the code found in sp_spaceused):
> > >
> > > select ltrim(str(((
> > > select sum(convert(dec(15),size))
> > > from dbo.sysfiles
> > > where (status & 64 = 0)
> > > ) - (
> > > select sum(convert(dec(15),reserved))
> > > from sysindexes
> > > where indid in (0, 1, 255)
> > > )) / (
> > > select 1048576 / low
> > > from master.dbo.spt_values
> > > where number = 1 and type = 'E'
> > > ),15,2)+ ' MB')
> > >
> > > Razvan|||For SQL Server 2005, I would use something like this:
select ltrim(str(
case
when reservedpages<dbsize
then dbsize-reservedpages
else 0
end * 8192 / 1048576
,15,2)+ ' MB')
from (
select sum(convert(dec(15,2),size)) as dbsize
from dbo.sysfiles
where (status & 64 = 0)
) x, (
select sum(convert(dec(15,2),total_pages)) as reservedpages
from sys.allocation_units
) y
If you need the unallocated space for all databases, you can use this:
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#AllDatabaseInfo') IS NOT NULL
DROP TABLE #AllDatabaseInfo
CREATE TABLE #AllDatabaseInfo (
DatabaseName sysname PRIMARY KEY,
UnallocatedSpace decimal(15,2) NOT NULL
)
DECLARE AllDatabaseInfo CURSOR LOCAL FOR
SELECT name FROM master..sysdatabases
OPEN AllDatabaseInfo
DECLARE @.DBName sysname, @.SQL nvarchar(4000)
WHILE 1=1 BEGIN
FETCH NEXT FROM AllDatabaseInfo into @.DBName
IF @.@.FETCH_STATUS<>0 BREAK
SET @.SQL = N'insert into #AllDatabaseInfo
select '+QUOTENAME(@.DBName,'''')+',
convert(dec(15,2),
case
when reservedpages<dbsize
then dbsize-reservedpages
else 0
end * 8192 / 1048576
)
from (
select sum(convert(dec(15,2),size)) as dbsize
from '+QUOTENAME(@.DBName)+'.dbo.sysfiles
where (status & 64 = 0)
) x, (
select sum(convert(dec(15,2),total_pages)) as reservedpages
from '+QUOTENAME(@.DBName)+'.sys.allocation_units
) y
'
--PRINT @.SQL
EXEC (@.SQL)
END
CLOSE AllDatabaseInfo
DEALLOCATE AllDatabaseInfo
SET NOCOUNT OFF
SELECT * FROM #AllDatabaseInfo ORDER BY DatabaseName
Razvan