Hi Experts i have doubt

[n]

have MS SQL 2008 R2, 500 databases. What is the most efficient, easiest and 'modern' way to query all databases sizes.
The output should have columns:
•DatabaseName
•DataFilesSize
•LogFilesSize

[rja.ca...@gmail.com]

I've only got SQL Server 2005; here, the system catalog view,
sys.master_files, has the information you're looking for.
You'll need DB_NAME([database_id]) for the first column, and,
to have the MDF/NDF and log file sizes side by side in a row,
you're going to need one of those "PIVOT" bits, I suppose.
I can never remember how to do that. But it is "modern". ;-)

[Erland Sommarskog]

(rja.ca...@gmail.com) writes:
> On Tuesday, 21 May 2013 14:27:33 UTC+1, n wrote:
>> have MS SQL 2008 R2, 500 databases. What is the most efficient, easiest
and 'modern' way to query all databases sizes.
>>
>> The output should have columns:
>>

>> ?DatabaseName
>>
>> ?DataFilesSize
>>
>> ?LogFilesSize

>
> I've only got SQL Server 2005; here, the system catalog view,
> sys.master_files, has the information you're looking for.
> You'll need DB_NAME([database_id]) for the first column, and,
> to have the MDF/NDF and log file sizes side by side in a row,
> you're going to need one of those "PIVOT" bits, I suppose.
> I can never remember how to do that. But it is "modern". ;-)

Here is a query. Fine-tune it to produce the units you want the data in.

SELECT d.name,
SUM(CASE WHEN mf.type <> 1 THEN convert(bigint, mf.size) * 8192 END) /
1000000000 AS [DataFileSize in GB],
SUM(CASE WHEN mf.type = 1 THEN convert(bigint, mf.size) * 8192 END) /
1000000000 AS [LogFileSize in GB]
FROM sys.databases d
JOIN sys.master_files mf ON d.database_id = mf.database_id
GROUP BY d.name
ORDER BY d.name



--
Erland Sommarskog, Stockholm, esq...@sommarskog.se

[SUBBU N]

Hi...Am using sql2008R2 .i try to find out all db sizes through this query, but it is not shows any size of the databases.that result is it showas only all db size is 'o'and log file size also '0'.please verify and replay to me

thank you

[Erland Sommarskog]

Maybe all your databases are less than 1GB size? Rewrite the query to
show the size in megabytes.

When I tested it, I got 0 for most databases file, since I only have a
bunch of toy databases here at home. But I did see a couple of non-zero
values.

[rja.ca...@gmail.com]

On Monday, 27 May 2013 22:24:15 UTC+1, Erland Sommarskog wrote:
> Maybe all your databases are less than 1GB size? Rewrite
> the query to show the size in megabytes.
>
> When I tested it, I got 0 for most databases file, since
> I only have a bunch of toy databases here at home. But I did
> see a couple of non-zero values.

I wondered why you used integer arithmetic.

I've also been wondering: is it just you here? (and me,
and Subbu).

I /think/ I remember this or a similar group being
/much/ more active, in former days.

[Erland Sommarskog]

(rja.ca...@gmail.com) writes:
> I wondered why you used integer arithmetic.

Because I figured that size in GB would be enough. Do you really want
to know that that a database is 234.768 GB?