SQL Server Disk Usage Statistics?

Posted on 2009-04-17
Last Modified: 2013-11-30
We have an issue with our BizTalk server currently. It runs very slowly. So we ran some diagnostics tools (BizTalk Message Box viewer) and discovered some potential issues with The sql server backend. My question relates to disk usage versus space allocated in sql 2005. When I click on the properties for the BizTalkMgmtDb it says the size of the DB is 1755.19 MB and there is on .42 MB space available. The amount listed inspace available would be bad I would think. But my question I guess is essentially what does this number really represent ?

I ask this because when I run a disk usage report it says 99% of the transaction log space is unused. Would this would be 99% of the space allocated for the transaction log I would think ? Statistics for the data files state( 62% used, 8.5 % unused,25% index, and 3.7% unallocated) I guess part of the problem is I really don't completely understand these numbers. Can somebody explain these numbers to me and tell me if I have a potential disk space problem.

Any help would be greatly appreciated.


Question by:jazzcatone
    LVL 12

    Expert Comment

    When you look at the database report, total is the total of size of data and the space available inside the data files.
    Used is the ammount of data you have in the database while available is the space within the database files that's available for growth in case your data increases. This means that your data will grow without the file size increasing. It's kind of a reserved buffer.
    Index is the space used by the indexes hence the total size of indexes, data is the space used by the heaps of data inside the database which does not necessarily sit in the index, unused is already allocated but not used by a spacific datatype while unallocated is free space.
    When a database is growing, it increases in the volumes of data, forcing SQL server to claim more space from the OS and adds them to the database files (data or log) as defined by the growth parameters.
    LVL 12

    Expert Comment

    To add on to that, In this case there's no problem having your log with 99% unused space. SQL server will sometimes slow down due to no maintenance (if you have fragmented indexes that need a defrag or rebuilding and reorganising), also ensure that you have a 20% buffer of free disk space on the hard drive: SQL server will slow down in case you have less free space on your system.
    I think you need a whole performance tuning exercise: Check your tempdb database and see if it's not seated on a drive with little space. Look through your logs and check to see if you have any errors that might point to you to performance bottle necks.
    Try to do proper paging layout for your operating system: it's best to fix your pagefiles to a size that's 225% of your RAM. Letting pagefiles grow will cause system issues.
    Look at other applications that might be using your database drive and the like.
    Let us know how you progress.
    Chris Musasizi
    LVL 12

    Accepted Solution

    Lastly but not least, perform a full system analysis using performance monitor, SQL profile and the like.
    Also do a disk check just in case you have some disk issues on your drive. Ensure that these are all fixed.
    With those options, you will be able to almost pin-point where your performance issues are coming from.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Suggested Solutions

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how the fundamental information of how to create a table.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now