Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 How to know database size by using Query ?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 05/26/2008 :  08:24:39  Show Profile  Reply with Quote
Hi,

How to know database size by using Query ?

-- Regards
Prashant

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 05/26/2008 :  08:32:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11752 Posts

Posted - 05/26/2008 :  09:00:15  Show Profile  Visit spirit1's Homepage  Reply with Quote
use yourdb
exec sp_spaceused

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 05/26/2008 :  09:40:47  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Also refer http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53843

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 05/26/2008 :  10:03:45  Show Profile  Reply with Quote
sp_databases
Go to Top of Page

mdubey
Posting Yak Master

USA
133 Posts

Posted - 05/26/2008 :  19:52:59  Show Profile  Reply with Quote
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
Go to Top of Page

ksampatk
Starting Member

India
3 Posts

Posted - 04/12/2010 :  11:00:00  Show Profile  Reply with Quote
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
Go to Top of Page

andyansryan
Starting Member

USA
2 Posts

Posted - 11/11/2010 :  11:25:08  Show Profile  Reply with Quote
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
Go to Top of Page

vaddesuresh
Starting Member

India
5 Posts

Posted - 11/15/2010 :  01:40:27  Show Profile  Reply with Quote
sp_databases
Go to Top of Page

humbertomoreno
Starting Member

1 Posts

Posted - 10/12/2012 :  15:12:31  Show Profile  Reply with Quote
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
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2179 Posts

Posted - 10/13/2012 :  07:15:52  Show Profile  Visit jackv's Homepage  Reply with Quote
You can use the sys.master_files - http://www.sqlserver-dba.com/2012/09/sql-server-find-the-database-file-size-and-log-file-size.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

lionofdezert
Aged Yak Warrior

Pakistan
885 Posts

Posted - 10/15/2012 :  05:56:29  Show Profile  Visit lionofdezert's Homepage  Send lionofdezert a Yahoo! Message  Reply with Quote
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_MBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
GROUP BY d.name
ORDER BY d.name

http://connectsql.blogspot.com/2012/01/sql-server-get-all-databases-size.html

--------------------------
http://connectsql.blogspot.com/
Go to Top of Page

suneeloduru1
Starting Member

3 Posts

Posted - 10/16/2012 :  11:07:12  Show Profile  Reply with Quote
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
Go to Top of Page

suneeloduru1
Starting Member

3 Posts

Posted - 10/18/2012 :  05:41:35  Show Profile  Reply with Quote
-- 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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.38 seconds. Powered By: Snitz Forums 2000