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 2008 Forums
 Transact-SQL (2008)
 size all db's in my server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

inbs
Aged Yak Warrior

843 Posts

Posted - 11/16/2012 :  08:03:09  Show Profile  Reply with Quote
i want to know the size all the database in my server

1.

db1 - 100 M
db3 - 200 M
db3 - 300 M

i want to get one column -600 M

2.and another query that include the log
1000M (include the log)

thanks

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/16/2012 :  08:25:38  Show Profile  Reply with Quote
You can use the following query. Sum it up if you need space used by all databases on the server.
SELECT instance_name, cntr_value 
FROM sys.dm_os_performance_counters 
WHERE counter_name IN ('Data File(s) Size (KB)')
Log file is similar - counter_name is 'Log File(s) Size (KB)'. If you want to find only the used space in the log file, the counter_name is 'Log File(s) Used Size (KB)'.

EDIT:

Sorry, correction to above. You should look only for object_name = 'SQLServer:Databases'
SELECT instance_name, cntr_value 
FROM sys.dm_os_performance_counters 
WHERE counter_name IN ('Data File(s) Size (KB)')
AND object_name = 'SQLServer:Databases'

Edited by - sunitabeck on 11/16/2012 08:29:07
Go to Top of Page

inbs
Aged Yak Warrior

843 Posts

Posted - 11/16/2012 :  08:57:06  Show Profile  Reply with Quote
thanks how can i get it in MG?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/16/2012 :  09:50:44  Show Profile  Reply with Quote
You mean Mega Bytes or Giga Bytes? For MB, divide by 1000, and by 1000000 for GB.
SELECT instance_name, cntr_value/1000.0 [In MB]
FROM sys.dm_os_performance_counters 
WHERE counter_name IN ('Data File(s) Size (KB)')
AND object_name = 'SQLServer:Databases'
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.05 seconds. Powered By: Snitz Forums 2000