Author |
Topic |
hirani_prashant
Yak Posting Veteran
93 Posts |
Posted - 2008-05-26 : 08:24:39
|
Hi,How to know database size by using Query ?-- RegardsPrashant |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-26 : 08:32:13
|
select * from sys.dm_db_file_space_usageselect *from sysfiles E 12°55'05.25"N 56°04'39.16" |
 |
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-05-26 : 09:00:15
|
use yourdbexec sp_spaceused_______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
 |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-26 : 10:03:45
|
sp_databases |
 |
|
mdubey
Posting Yak Master
133 Posts |
Posted - 2008-05-26 : 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_namedb_size dbidcreated Statuscompatibility_level ManojMCP, MCTS |
 |
|
ksampatk
Starting Member
3 Posts |
Posted - 2010-04-12 : 11:00:00
|
Try below query to fetch SQL Database Sizeselect 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 descGOSampath Karnatakam - DBA Architect |
 |
|
andyansryan
Starting Member
2 Posts |
Posted - 2010-11-11 : 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 #DatabaseInfoexec SP_HELPDBSELECT *FROM #DatabaseInfodrop table #DatabaseInfo |
 |
|
vaddesuresh
Starting Member
5 Posts |
Posted - 2010-11-15 : 01:40:27
|
sp_databases |
 |
|
humbertomoreno
Starting Member
1 Post |
Posted - 2012-10-12 : 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 sdinner join sys.sysaltfiles sf on sd.dbid = sf.dbidWHERE sd.name NOT IN ('master','model','msdb','tempdb')group by sd.nameorder by 2 |
 |
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2012-10-15 : 05:56:29
|
I like to use following query to get all databases size on an instance:SELECT d.name, ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBsFROM sys.master_files mf INNER JOIN sys.databases d ON d.database_id = mf.database_idWHERE d.database_id > 4 -- Skip system databasesGROUP BY d.nameORDER BY d.namehttp://connectsql.blogspot.com/2012/01/sql-server-get-all-databases-size.html--------------------------http://connectsql.blogspot.com/ |
 |
|
suneeloduru1
Starting Member
3 Posts |
Posted - 2012-10-16 : 11:07:12
|
I am going to post a small script to get current database size of all databases SET NOCOUNT ONDECLARE @counter SMALLINTDECLARE @counter1 SMALLINTDECLARE @dbname VARCHAR(100)DECLARE @size INTDECLARE @size1 DECIMAL(15,2)SET @size1=0.0SELECT @counter=MAX(dbid) FROM master..sysdatabasesIF EXISTS(SELECT name FROM sysobjects WHERE name='sizeinfo')DROP TABLE sizeinfoCREATE TABLE sizeinfo(fileid SMALLINT, filesize DECIMAL(15,2), filename VARCHAR(1000))WHILE @counter > 0BEGIN 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.0ENDSET NOCOUNT OFF |
 |
|
suneeloduru1
Starting Member
3 Posts |
Posted - 2012-10-18 : 05:41:35
|
-- You can use below code snippet to find the size of all databasesdeclare @db_size table (db_name varchar(100), size float )insert into @db_sizeExec sp_msforeachdb "select '?' db_name, sum(size/128.0) [db_size(inMB)] from sysfiles"select * from @db_size--- Suneel |
 |
|
|