counting VLF's

Question

counting VLF's

SQLAssAS

SSCertifiable

Points: 7246
More actions
March 8, 2016 at 4:53 am

#305469

Is there another way to count VLF count other than using DBCC Loginfo?
I can only get DBCC Loginfo working using SA Permission and i dont want to assign this out.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I don't know offhand any other way to do that, but it would need for sure sysadmin permissions. Checking VLFs is definitely a sysadmin task: why are you trying to give this ability to somebody else?

-- Gianluca Sartori

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 2

Hello!

its not someone else its a service account.

I basically have a process which connects to all of our SQL Servers, queries various aspects such as security info, vlf counts, instance config settings, missing backups, all databases and their info etc etc..

I want to automate this but I am uneasy about putting an account on each instance with SA, even if it is automated

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Granting sysadmin permissions to monitoring services is an established practice, exactly for this reason: they have to query data which is available only to sysadmins.

-- Gianluca Sartori

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 4

agreed - but if there is a different way to do it it would rather do it without SA

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

You can encapsulate the call to DBCC LOGINFO into a stored procedure with EXECUTE AS.

Here's a guide on how to do that: http://sommarskog.se/grantperm.html

-- Gianluca Sartori