Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1489

Re: Hi Experts i have doubt

Show all headers | View raw

 (rja.carnegie@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, esquel@sommarskog.se

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Hi Experts i have doubt n <nsubbu1986@gmail.com> - 2013-05-21 06:27 -0700
  Re: Hi Experts i have doubt rja.carnegie@gmail.com - 2013-05-21 07:16 -0700
    Re: Hi Experts i have doubt Erland Sommarskog <esquel@sommarskog.se> - 2013-05-21 21:22 +0200
  Re: Hi Experts i have doubt SUBBU N <nsubbu1986@gmail.com> - 2013-05-27 00:44 -0700
    Re: Hi Experts i have doubt Erland Sommarskog <esquel@sommarskog.se> - 2013-05-27 23:24 +0200
      Re: Hi Experts i have doubt rja.carnegie@gmail.com - 2013-05-27 19:02 -0700
        Re: Hi Experts i have doubt Erland Sommarskog <esquel@sommarskog.se> - 2013-05-28 08:29 +0200

csiph-web