| Author |
 Topic  |
|
|
hirani_prashant
Yak Posting Veteran
93 Posts |
 Posted - 05/26/2008 : 08:24:39
|
Hi,
How to know database size by using Query ?
-- Regards
Prashant |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 05/26/2008 : 08:32:13
|
select * from sys.dm_db_file_space_usage
select *
from sysfiles
E 12°55'05.25"
N 56°04'39.16" |
Edited by - SwePeso on 05/26/2008 08:35:03 |
 |
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 05/26/2008 : 10:03:45
|
| sp_databases |
|
|
|
mdubey
Posting Yak Master
USA
133 Posts |
Posted - 05/26/2008 : 19:52:59
|
You can use SP_HELPDB.
It will give you execute this stored proc, you will get some sort of result sets.
Ex.
DB_name
db_size
dbid
created
Status
compatibility_level
Manoj
MCP, MCTS |
|
|
|
ksampatk
Starting Member
India
3 Posts |
Posted - 04/12/2010 : 11:00:00
|
Try below query to fetch SQL Database Size
select db_name(dbid),str(convert(dec(15),sum(size))* 8192/ 1048576,10,2)+ N' MB' as Size from sys.sysaltfiles
group by dbid order by 2 desc
GO
Sampath Karnatakam - DBA Architect |
|
|
|
andyansryan
Starting Member
USA
2 Posts |
Posted - 11/11/2010 : 11:25:08
|
CREATE TABLE #DatabaseInfo (name varchar(100),
db_size varchar(255),
owner varchar(20),
dbid smallint,
created smalldatetime,
status varchar(250),
compatibility_level varchar(250))
INSERT INTO #DatabaseInfo
exec SP_HELPDB
SELECT *
FROM #DatabaseInfo
drop table #DatabaseInfo |
|
|
|
vaddesuresh
Starting Member
India
5 Posts |
Posted - 11/15/2010 : 01:40:27
|
| sp_databases |
|
|
|
humbertomoreno
Starting Member
1 Posts |
Posted - 10/12/2012 : 15:12:31
|
This is probably better. I am SUMming all the sizes of files per-database and grouping them. I think it's more accurate.
select sd.name, SUM(sf.size)
from sys.sysdatabases sd
inner join sys.sysaltfiles sf
on sd.dbid = sf.dbid
WHERE sd.name NOT IN ('master','model','msdb','tempdb')
group by sd.name
order by 2 |
|
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1773 Posts |
|
|
lionofdezert
Aged Yak Warrior
Pakistan
864 Posts |
|
|
suneeloduru1
Starting Member
3 Posts |
Posted - 10/16/2012 : 11:07:12
|
I am going to post a small script to get current database size of all databases
SET NOCOUNT ON
DECLARE @counter SMALLINT
DECLARE @counter1 SMALLINT
DECLARE @dbname VARCHAR(100)
DECLARE @size INT
DECLARE @size1 DECIMAL(15,2)
SET @size1=0.0
SELECT @counter=MAX(dbid) FROM master..sysdatabases
IF EXISTS(SELECT name FROM sysobjects WHERE name='sizeinfo')
DROP TABLE sizeinfo
CREATE TABLE sizeinfo(fileid SMALLINT, filesize DECIMAL(15,2), filename VARCHAR(1000))
WHILE @counter > 0
BEGIN
SELECT @dbname=name FROM master..sysdatabases WHERE dbid=@counter
TRUNCATE TABLE sizeinfo
EXEC ('INSERT INTO sizeinfo SELECT fileid,size,filename FROM '+ @dbname +'..SYSFILES')
SELECT @counter1=MAX(fileid) FROM sizeinfo
WHILE @counter1>0
BEGIN
SELECT @size=filesize FROM sizeinfo WHERE fileid=@counter1
SET @size1=@size1+@size
SET @counter1=@counter1-1
END
SET @counter=@counter-1
SELECT @dbname AS DBNAME,CAST(((@size1)*0.0078125) AS DECIMAL(15,2)) AS [DBSIZE(MB)]
SET @size1=0.0
END
SET NOCOUNT OFF
|
|
|
|
suneeloduru1
Starting Member
3 Posts |
Posted - 10/18/2012 : 05:41:35
|
-- You can use below code snippet to find the size of all databases
declare @db_size table (db_name varchar(100), size float )
insert into @db_size
Exec sp_msforeachdb "select '?' db_name, sum(size/128.0) [db_size(inMB)] from sysfiles"
select * from @db_size
--- Suneel |
|
|
| |
Topic |
|
How to know database size by using Query ?
