SQL Server Disk Usage Statistics?

Posted on 2009-04-17
Medium Priority
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
  • 3
LVL 12

Expert Comment

by:Chris M
ID: 24168830
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

by:Chris M
ID: 24168919
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

Chris M earned 1500 total points
ID: 24168946
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

830 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