Performance

SCOM – Check Size of Databases (SQL Standard Reports)

If you want to know how much space your OperationsManger database is using you could run a SQL Report. Because it is a feature from SQL Server 2008 R2 it’s easily overseen I just want to show an example.

Open SQL Server Management Studio navigate to OperationsManager database and make a right mouse click. Click “Reports/Standard Reports/Disk Usage”…

image

After a few seconds a pie chart will be shown. On top you see the total size of the database including log file and also each file size of the data and log file. Usually a database should have “Unallocated” (green) space available.

My left pie just shows that “Data”, “Unused” and “Index” uses the entire space. This means my database is full. Since OperationsManager database does not autogrow it just stays this way.

image

The side effects are several critical alerts and the eventlog will show e.g. errors like event id 10801…

image

To resolve this, just resize your database. First calculate how much space for your OperationsManager database you need by using the Sizing Helper Tool . Then go into the database properties and set the appropriate settings. SCOM 2012 configures by default the transaction log half the size of the database log, keep this in mind.  Click o.k. and SQL Server will resize the database. You don’t need to reboot your servers nor anything else.

image

Next run the SQL Server Report again and finally you get “Unallocated” space…

image

Your errors in the eventlog and in the SCOM console should disappear.

9 Replies to “SCOM – Check Size of Databases (SQL Standard Reports)

  1. Thx, very usefull…and what about the DW?Excepting size (that depends on scenario), these considerations are applicable to datawarehouse DB configuration?
    Thanks again,
    Andy

  2. Hi Stefan,
    you’re right, I was not much clear (and sorry even for my bad english in advance… 🙂 )…I meant if your considerations about Transaction Log sizing can be extended also to the DW DB…in my deployment scenario i’ve configured 30GB for OpDB (and hence 15GB for Tran.Log), because i found some articles that suggest this as a standard size for medium-large environments…for now i’ve also resized the DW DB to 30GB, but using sizing tool I found that probably i’ll need a DW DB of at least 500GB when the installation will be complete (note that if i well understand this DB should be minimized as possible to increase performance)…should I consider a Trans.Log of 250GB? (and this was the question/doubt)…I’m looking for info regarding an adequate Backup/Restore plan for all Database…
    Thanks again.

    Best Regards,
    Andy

    1. Hi Andy,

      No Problem :).

      Be carefull, don’t make a database as small as possible! In general make a database as big a necessary the SCOM sizing sheet (http://blog.scomfaq.ch/2012/04/02/scom-2012-operations-manager-2012-sizing-helper-tool/) will assist you in determine the appropriate size. Make your DW 500GB if the sizning sheet this suggests. Otherwise your DW will be full immediately and you would need to adjust it’s size.

      About the transaction log it is also 50% of the data log.

      Regards,

      Stefan

  3. Or as an sql statement
    SELECT DB_NAME(database_id) AS DatabaseName,
    Name AS Logical_Name,
    Physical_Name, (size*8)/1024 SizeMB, (size*8)/1048576 SizeGB
    FROM sys.master_files
    order by size desc
    GO

  4. hi if i want to find the Memory , CPU and IOPS utilization of database what SQL quires i should use ?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.