What simple checks can I do?

SQL Server Administration
1

I have reluctantly been given a number of databases to "own". What simple checks can I do each morning to ensure all is good?

I have

select cmd,* from sys.sysprocesses where blocked > 0

SELECT DISTINCT(request_owner_guid) as UoW_Guid
FROM sys.dm_tran_locks WHERE request_session_id =-2

Anything else?

2

Are you tasked just to make sure the database is running and not locked etc, or are you also tasked to somehow insure correctness and data accuracy?

1 Like
3

There's a few checklists here:

Probably good to review several of them just to be sure you're not overlooking something.

Blocking/deadlocking and some other minor issues are best alerted, rather than you running a query periodically. Blocking and deadlocking can be picked up in extended events and event notifications, although XEvents can't alert you.

Blocking is not worth alerting on unless it lasts for at least a certain time. You can control this with the blocked process report:

And there's some tools to help you view them:

There are similar scripts etc. for deadlocks. In a previous job I have an XEvent session that captured both, plus an event notification that would email me when they happened.

You can also set SQL Agent alerts for some of these, they can email you when they arise. I found the SQL Agent alerts not as useful, as they didn't give you details, but just basically sent an email. They can, however, kick off a job that can query your other capture data and email those details.

You might want to look into some monitoring tools, Redgate, Idera, etc. There are also some not-quite-free, but also not as expensive tools you can trial.

2 Likes
4

Yeah really just to ensure everything is running ok, at busy periods users often click buttons on screens multiple times which freezes everything etc

5

install and use Brent Ozar's Blitz https://www.brentozar.com/blitz/

1 Like