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 Comments

  1. 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

  2. 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

  3. Pingback: Check Database Size | ern.nu

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s