Login or Sign Up to become a member!
LessThanDot Sit Logo

LessThanDot

Data Management

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot friendfeed Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Your profile

    Tools

    Tag cloud

    azure book business intelligence ctp database dates denali dmv functions gotcha high availability how to howto indexing mirroring performance sql sql friday sql server sql server 2000 sql server 2005 sql server 2008 sql server 2008 r2 sql server 2012 sql server denali sql server integration services ssis t-sql tip trick

    Authors

    Main Categories

    Search

    XML Feeds

    What is RSS?

    Google Ads

    « SQL Saturday #99 Minnesota - #SQLAwesomeA New Door Opens »
    6
    comments

    Database space use

    by David Forck (thirster42) on Oct 24, 2011 in categories Microsoft SQL Server Admin, Microsoft SQL Server
    Instapaper

    I was doing some research on my SQL Servers and wanted to know just how much disk space an instance's database were taking up. Since the databases on this instance are across a couple of different arrays and backups and other stuff exist on the server, I figured i'd write some t-sql to figure out this question. Here's what I wrote:

    tsql
    Line number Off | Hide | Select all
    1. SELECT SUM(SIZE*8)/1024.0/1024.0
    2. FROM master.sys.sysaltfiles

    That select statement shows me exactly how much space an entire instance (2005+) is taking up (save for any database in any state besides just online). This isn't just the size of the data, but rather the space that the database files have taken up.

    One particular use I have for this is determining how much junk is on my server taking up space and gives me a reason to grumble at people.

    So, what's your biggest instance? Mine's currently 268gb.

    About the Author

    I have an A.S. in Computer Programming. I am currently a Database Developer (April 2008-Present) for a development group at a contract research lab, where we build in-house applications using Microsoft SQL Server (2005 and 2008) and ASP.Net 3.5. I have a part-time job (April 2007-Present) as a dba/db developer/web developer/designer. I use: SQL Server 2005/08/R2, HTML, CSS, PHP, and Java Script. Penguins are awesome, but I'm not a linux person. If you have any questions feel free to email me at dforck@gmail.com
    Social SitingsTwitterFacebookLTD RSS Feed
    703 views
    Read More Posts by David Forck (thirster42)
    Instapaper

    6 comments

    Comment from: SQLDenis [Member] Email
    SQLDenis Mine is all over the spectrum from tens of MBs to multi TB size

    No petabytes yet :-) but if data grows like it has in the last decade I am sure petabytes will be somewhat normal in 10 years and probably not even considered to be a VLDB at that time...oh well time will tell....
    10/24/11 @ 18:42
    Comment from: David Forck (thirster42) [Member]
    A petabyte of data... I still have issues getting used to terabyte
    10/25/11 @ 07:20
    Comment from: Erik [Member] Email
    Erik 1643.4 GB is our largest DB instance.
    10/25/11 @ 11:20
    Comment from: George Mastros (gmmastros) [Member]
    George Mastros (gmmastros) Truncate log and now my db < 1 KB
    10/25/11 @ 16:37
    Comment from: Ted Krueger (onpnt) [Member]
    Ted Krueger (onpnt) I don't get to do this anymore so can't post anything :(
    10/26/11 @ 07:20
    Comment from: SQLDenis [Member] Email
    SQLDenis I just needed something for MB per DB...here it is in case you ever need it


    it pretty much gives the same output as sp_helpdb



    SELECT DB_NAME(dbid) AS DatabaseName,
    CONVERT(DECIMAL(38,2),(SUM(SIZE*8)/1024.0)) AS MB
    FROM master.sys.sysaltfiles
    WHERE dbid <> 32767
    GROUP BY DB_NAME(dbid)
    ORDER BY MB DESC

    11/11/11 @ 11:42

    Leave a comment


    Your email address will not be revealed on this site.

    Your URL will be displayed.
    (Line breaks become <br />)
    (Name, email & website)
    (Allow users to contact you through a message form (your email will not be revealed.)

    Powered by B2Evolution
    Valid XHTML | Valid CSS