SQL Server Forums
Profile | Register | 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 ?
 New Topic  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
30265 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
11751 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
22761 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
2067 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  
 New 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.14 seconds. Powered By: Snitz Forums 2000