datafiles and logfiles size - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

datafiles and logfiles size

Author

Topic

TinyJimbo
Starting Member

3 Posts

Posted - 2010-08-06 : 15:03:16

I am new to T-SQL and I need help with an operation. It's all about getting the size of datafiles and logfiles for each databases.

select 'SELECT ''' + name + ''' AS DBNAME,' +
'sum(size * 8 /1024) AS MB from ' + name + '.dbo.sysfiles union '
from sysdatabases
order by name

(This query gives me queries for each databases in the instance)

Result :

SELECT 'master' AS DBNAME,sum(size * 8 /1024) AS MB from master.dbo.sysfiles union
SELECT 'model' AS DBNAME,sum(size * 8 /1024) AS MB from model.dbo.sysfiles union
SELECT 'msdb' AS DBNAME,sum(size * 8 /1024) AS MB from msdb.dbo.sysfiles union
SELECT 'Northwind' AS DBNAME,sum(size * 8 /1024) AS MB from Northwind.dbo.sysfiles union
SELECT 'pubs' AS DBNAME,sum(size * 8 /1024) AS MB from pubs.dbo.sysfiles union
SELECT 'tempdb' AS DBNAME,sum(size * 8 /1024) AS MB from tempdb.dbo.sysfiles

Then I have to launch another query with these to get my results.

What I'm trying to do it skip that middle part and have it execute the queries by itself.

The query below works well but I don't like how the results are displayed.

EXEC sp_MSforeachdb 'USE ? SELECT name,filename AS Path,(size*8)/1024 AS MB

FROM dbo.sysfiles'

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-08-06 : 16:08:12

An alternative is to use SMO and powershell - which would allow you instantiate such as and allow you format as you please. It depends how you want to manage \ and report on servers :
$instance = 'MYSERVER\INSTANCE1'
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
$dbs = $s.Databases
foreach ($db in $dbs)
{
$db.size
}

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources