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

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 size all db's in my server

Author  Topic 

inbs
Aged Yak Warrior

860 Posts

Posted - 2012-11-16 : 08:03:09
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-16 : 08:25:38
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'
Go to Top of Page

inbs
Aged Yak Warrior

860 Posts

Posted - 2012-11-16 : 08:57:06
thanks how can i get it in MG?
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-16 : 09:50:44
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
   

- Advertisement -