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”…
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.
The side effects are several critical alerts and the eventlog will show e.g. errors like event id 10801…
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.
Next run the SQL Server Report again and finally you get “Unallocated” space…
Your errors in the eventlog and in the SCOM console should disappear.
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
Hi
You can run the report against any database. The considerations about the size differ though, what other considerations do you mean?
Regards,
Stefan
Hi Stefan,
Excellent one. I like your blogs and posts.
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
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
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
hi if i want to find the Memory , CPU and IOPS utilization of database what SQL quires i should use ?
Hi
Well, there are performance counters within SQL Server provided by a System view http://blog.sqlauthority.com/2009/06/10/sql-server-performance-counters-from-system-views-by-kevin-mckenna/ but if you cannot find your desired Counter I guess you would need to look into Windows Performance Counters (Perfmon).
Cheers,
Stefan