Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Towards the innovative SQL ideas
Browse by Tags · View All
sql server 125
sql 124
ms sql server 119
ms sql 118
database 109
tsql 81
#SQL Server 78
t-sql 75
#sql 71
sql server general 67

Archive · View All
April 2011 14
July 2011 12
May 2011 12
August 2011 11
June 2011 10
September 2011 8
December 2011 6
November 2011 6
September 2013 5
June 2013 5

Database size information - SQL Server

Dec 15 2012 12:00AM by Paresh Prajapati   

This is common information which DBA required on daily basis for monitoring or reporting purpose. This is basic details which help us to get the database growth statistics on daily, weekly and monthly basis so we can have idea for the disk space management.  We can dump daily database statistics and make database size and  growth reports too. You all know about the script and you may used too. Even i would like to share. System stored procedures also help us to get the same database information like sp_helpdb stored procedure give details like,

EXEC sp_helpdb 'master';

dbinfo_1

This is all information for database which returned by sp_helpdb by passing database name as a parameter. I f you do not wish to pass database name then it will return all database without physical file details. Same approach receive by sp_spaceused system stored procedure without any parameter passing,

EXEC sp_spaceused;

dbinfo_4

Now i would like to get it with some other scripts which give details for each databases and their physical files,

USE master 
GO 

SELECT db.[name]                  AS 'DBName', 
       af.name                    AS 'LogicalName', 
       af.[filename]              AS 'PhysicalName', 
       ( Cast(( ( ( Cast(af.[size] AS NUMERIC(18, 4)) * 8192 ) / 1024 ) / 1024 ) 
              AS 
                NUMERIC(18, 2)) ) AS 'FileSize_MB' 
FROM   sys.sysdatabases db 
       INNER JOIN sys.sysaltfiles af 
               ON db.dbid = af.dbid 

dbinfo_2

(Click on image to enlarge)

This is details returned by each physical files of all databases but now same script with minor changes return whole database size details without physical files detail,

USE master 
GO 
             
SELECT db.[name]                           AS 'DBName', 
       Sum(( Cast(( ( ( Cast(af.[size] AS NUMERIC(18, 4)) * 8192 ) / 1024 ) / 
                    1024 ) AS 
                        NUMERIC(18, 2)) )) AS 'FileSize_MB' 
FROM   sys.sysdatabases db 
       INNER JOIN sys.sysaltfiles af 
               ON db.dbid = af.dbid 
GROUP  BY db.[name] 

These are the scripts which i want to share with you which may help you and i would like you to share your thoughts and scripts which you are using for database size and growth statistics. You may read my earlier post for Database Backup Statistics and History.

Tags: sql, tsql, sql server, ms sql, ms sql server, t-sql, database, sql server general, SQL Scripts, size, script, query, statistics, space, free space


Paresh Prajapati
6 · 23% · 7444
14
 
0
Lifesaver
 
 
 
0
Incorrect



Submit

4  Comments  

  • Paresh,

    For the important databases I look after I have set up the necessary code to gather

    freespaceindrive

    size_mb

    spaceused_mb

    freespace_mb

    freespace_pct

    for each database data file.

    Once the data is gathered various criteria are checked that send emails when certain things happen, e.g. freespace_pct < 25 or freespaceindrive < 10000.

    I still use something written by Sriram Ramamoorthy back in 2006.

    commented on Dec 15 2012 8:38AM
    dishdy
    16 · 10% · 3269
  • HI Paresh ,

    Nice One.......... And when i was loading data into tables like Facts and Aggregates that time i will use following scripts.

    EXEC SPSPACEUSED 'dbo.AGGDLY_STR'

    so that i will comes to know weather data is loading or not.If so then automatically size will increase.

    commented on Jan 1 2013 11:50PM
    Bala Krishna
    83 · 2% · 676
  • I think you mean sp_spacedused not spspaceused.

    commented on Jan 2 2013 2:29AM
    dishdy
    16 · 10% · 3269
  • Thanks Dishdy,

    Yes Your correct, that is exec spspaceused 'dbo.AGGDLY_STR'

    commented on Jan 2 2013 2:43AM
    Bala Krishna
    83 · 2% · 676

Your Comment


Sign Up or Login to post a comment.

"Database size information - SQL Server" rated 5 out of 5 by 14 readers
Database size information - SQL Server , 5.0 out of 5 based on 14 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]