SQL Server Disk Usage Statistics?

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.

Thanks,
Jason

jazzcatoneAsked:
Who is Participating?
 
Chris MConnect With a Mentor Consulting - Technology ServicesCommented:
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.
0
 
Chris MConsulting - Technology ServicesCommented:
Hi,
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.
 
Regards,
Chris.
0
 
Chris MConsulting - Technology ServicesCommented:
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.
regards,
Chris Musasizi
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.