Sql-server – Collecting VLF information on all versions of SQL server

performancesql serversql server 2014sql-server-2012

Based on my monitoring collection, i am having an issue while collecting info regarding how many VLF' i have for # of databases in SQL instance.

1st issue – Is there a single script which can be used from 2012 – 2019 SQL versions as standard to get the VLF info across all databases in an instance

2nd issue – how can i get 1st done with least privilege because login we use to collect info is not sysadmin and cant be. So how can i achieve this?

Much appreciated, thanks!

Best Answer

The first problem goes away if you solve the second with a Signed Stored Procedure installed in Master. If there are any version differences required, you can have different versions of the procedure for different SQL Server versions.

Then the script is a just a call to the procedure.

exec sp_GatherVLFStats

Related Solutions

Sql-server – dcexec login failure

The problem has been solved with some hints from:

When I setup Data Collection I did the following:

Setup

SSMS > serverA
Management > Data Collection
Configure Management Data Warehouse
Next
 > Check: Create or upgrade a management data warehouse
Next
New
 > Database name: DBA_MDW
OK
 > Server name: serverA.company.com
 > Database name: DBA_MDW
Next
New Login
 > Login name: DOMAIN\SQLmdw
OK
 > Check: mdw_admin
 > Check: mdw_reader
 > Check: mdw_writer
Next
Finish
Close

Configuration

Configure Management Data Warehouse
Next
 > Check: Set up data collection
Next
 > Server name: serverA.company.com
 > Database name: DBA_MDW
 > Cache directory:
Next
Finish
Close

To uninstall Data Collection, I used the script from Remove associated data collector jobs. I then followed the above setup and configuration steps, but this time under the configuration step I simply put serverA instead of serverA.company.com. That seems to have made all the difference and I'm now collecting data.

Sql-server – Required to use Oracle Performance Manager to monitor SQL Server

I think you are probably looking for Oracle Enterprise Manager or OEM as it is normally referred as, a good starting point for more information is here.
- a. Oracle database starting with 10G+ do their own data collection (Automatic workload Repository), however, you need to be licensed to access much of that data. You can still use statspack (which is free). OEM actually uses this collected performance metrics and uses them for display via its pages.
- b. Live views are available to use (the V$ and GV$) in Oracle, no experience with SQLServer, but I suspect you have similar views available.
- c. OEM can send you alerts on events and conditions that exceed user defined thresholds. This is configurable via templates or custom metrics (aka User Defined Metrics).
- d. OEM has some performance reporting built-in, but you can use OEM repository to roll your own reports, it is not very difficult (have done it in the past). OEM repository is simply a different database, the SYSMAN schema contains tables and views which expose all collected data. You can adjust retention, only constrained by available storage. It can provide your historic information as well as realtime performance for Oracle. You will have to check the plug-in for similar capability for SQLServer.
OEM can (with using optional plugins) monitor SQL Server, check this link for a brief overview. In my shop there is no SS, so I don't have first hand experience in using these plugins.
Licensing information for OEM is here, I'd definitely recommend reading this manual to you and your customers.
There could be, but once you read through the information linked above, you will get a better idea. Then a quick chat with your Oracle Sales team will yield more information as well.
Some of the extension packs for OEM are listed here.

Hope this gives you enough information to get started on it.

Best Answer

Related Solutions

Sql-server – dcexec login failure

Sql-server – Required to use Oracle Performance Manager to monitor SQL Server

Related Question