SQL Server database -- a read only node specifically (not exactly sure how that works).

Our database keeps crashing due to memory issues - various error messages indicate this. It's hard to tell if it's various user queries, or other background processes.

Our team does not administer said database. But -- as you might intuit, there are no strong resources (re: anybody) that really is dba'ing the database effectively.

It seems using the SQL Server Profiler (alter trace) is almost, if not actually, at the sys-admin level of privileges, which the actual sys-admins are reluctant to grant.

Is there a way to grant alter-trace without sys-admin-level security risks?

  • Is your end goal to be able to run the Profiler to monitor what queries are running on the server?
    – J.D.
    Commented Jun 16, 2022 at 18:29
  • Hmm. Well I guess the idea is to diagnose and prevent crashes of the server. Either there is abnormal behavior happening on the server (not blame game stuff, maybe errant backup processes or something) --- OR -- the server hardware provisioning is anemic and not sufficient for the queries being placed on it. Without visibility or a dedicated DBA really, it's hard to figure out the best potential path forward.
    – user45867
    Commented Jun 16, 2022 at 19:53
  • 1
    Can you edit the question and add the exact error messages?
    – vonPryz
    Commented Jun 16, 2022 at 20:04
  • 2
    I guess my point is you shouldn't need to ALTER TRACE to be able to run the Profiler. I don't even think you need SysAdmin privileges either (though I'd have to check). Also, agreed with vonPryz, you're better off posting details on the exact errors you're getting and we should be able to help diagnose. Your database shouldn't be crashing with pegged Memory per se, just slow and potentially timing out if your application specifies a query execution timeout.
    – J.D.
    Commented Jun 16, 2022 at 20:20
  • You should use an XEvent session instead. You need VIEW SERVER STATE to view the results and ALTER ANY EVENT SESSION to create or change one. Not too terrible. Commented Jun 17, 2022 at 8:18


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.