Free Space
-
Hi,
I want to be able to run a query on a particular db and find the available free space (for that db only). I know I can use master..xp_fixeddrives to return the free space on the hdds, but I want free space per a particular db. When you go into SQL Ent Manager, you can click on the db name under the particular instance you are in (on the tree on the left), and, making sure you are in TaskPad view, get a summary on the right panel. This summary includes, under the Database heading, an item for Size and Space Available. I want a query I can run that will return this "Space Available" number.
Also, I am NOT DB Admin on this server, therefore I DO NOT have rights to add tables, stored procedures, etc. All I can do is query.
If I run exec sp_spaceused, the values that this returns DO NOT match those in SQL Ent Manager's task pad for this db. Any suggestions would be greatly appreciated.
-
Have u tried sp_spaceused?
-
I did write a query to return this. Will have to dig it out and post.
-
maybe this will help out ....
I don't have the ref where I got it
CREATE TABLE #tables_usage (
name varchar(50) NULL,
rows varchar(15) NULL,
reserved varchar(15) NULL,
data varchar(15) NULL,
index_size varchar(15) NULL,
unused varchar(15) NULL,
)
DECLARE AllUserTables CURSOR
FOR
SELECT so.name as TName, su.name as UName
FROM sysobjects so join sysusers su
ON so.uid = su.uid
WHERE so.type = 'U'
DECLARE @tname varchar(40)
DECLARE @uname varchar(40)
OPEN AllUserTables
FETCH NEXT FROM AllUserTables
INTO @tname, @uname
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
insert into #tables_usage
exec('sp_spaceused ''' +
@uname + '.' + @tname + '''')
END
FETCH NEXT FROM AllUserTables
INTO @tname, @uname
END
CLOSE AllUserTables
DEALLOCATE AllUserTables
SELECT * FROM #tables_usage order by name
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
When I execute sp_spaceused, it returns different results than those displayed in SQL Server Enterprise Manager.
For example, this is what is returned when I run sp_spaceused:
|===============|===============|====================|============|
| database_name | database_size | unallocated space | |
| SC1 | 207447.69 MB | 18006.70 MB | |
|===============|===============|====================|============|
| reserved | data | index_size | unused |
| 127033968 KB | 117723496 KB | 9248592 KB | 61880 KB |
|===============|===============|====================|============|
However, when I look in Ent Manager, this is what it shows:
Size: 207,447.27 MB
Space Available: 81,551.25 MB
No matter how I calculate the query results, I cannot seem to get a value of 81,551.25.
alzdba , I tried your code too, but noticed that your code also uses sp_spaceused, which keeps returning incorrect values to me. Any suggestions? Thanks alot!
Also- I AM converting the KBs to MBs to make them the same format(by taking the KB value /1024 = MB Value).
-
I created a SP that I execute every day to keep track of database growth. The results are inserted into a table that I created prior to this (_dataused). Before you run this you should run "dbcc updateusage(0) with no_infomsgs" to get correct results
CREATE procedure spaceusage
as
declare @id int -- The object id of @objname.
declare @type character(2) -- The object type.
declare @pages int -- Working variable for size calc.
declare @dbname sysname
declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
declare @unallocatedspace float
declare @reserved float
declare @curdate datetime
create table #spt_space
(
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
/*
** If @id is null, then we want summary data.
*/
/* Space used calculated in the following way
** @dbsize = Pages used
** @bytesperpage = d.low (where d = master.dbo.spt_values) is
** the # of bytes per page when d.type = 'E' and
** d.number = 1.
** Size = @dbsize * d.low / (1048576 (OR 1 MB))
*/
select @dbsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 = 0)
select @logsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 <> 0)
select @bytesperpage = low
from master.dbo.spt_values
where number = 1
and type = 'E'
select @pagesperMB = 1048576 / @bytesperpage
select @unallocatedspace=ltrim(str((@dbsize -
(select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
 
) / @pagesperMB,15,2))/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (reserved)
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
select @pages = sum(convert(dec(15),dpages))
from sysindexes
where indid < 2
select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255
update #spt_space
set data = @pages
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
- data
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
select @reserved = ltrim(str(reserved * d.low / 1048576,15,0) )
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
-- print @unallocatedspace
-- print @reserved
select @curdate=cast(floor(cast(GetDate() as float)) as datetime)
insert into dbo._dataused values (@curdate,@reserved+@unallocatedspace,@unallocatedspace,@reserved)
drop table #spt_space
GO
-
Thanks for the procedure. Ran it, and still noticed the same issues I was having with sp_spaceused. I'm sorry I'm being a pain in the butt
, but I still can't get the values returned to match the value listed in Enterprise Manager. What am I doing wrong? If it helps, these dbs have autogrow enabled on them, so the results of the procedure above return a negative value for @unallocatedspace -
RUN dbcc updateusage(0) with no_infomsgs
CLOSE EM
OPEN EM
See the correct results
. -
My procedure have code in it that is part from sp_spaceused.
I ran a Profiler to see what Enterprise Manager executes on my server when I press taskpad.
select sum(convert(float,size)) * (8192.0/1024.0) from dbo.sysfiles
exec sp_spaceused
DBCC SQLPERF(LOGSPACE)
DBCC showfilestats
So combining & calculating results from this queries it shows you that numbers.
However on my server, results from the procedure matches results from Enterprise manager.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply