CJSmith dot me

Just another blog

Category: SQL Server 2012

SQL Server 2008 – Find which stored procedure is part of a scheduled

Published / by Chris Smith / Leave a Comment

Find a stored procedure (or any text) inside a scheduled on on SQL Server 2008 and 2012 by querying the sysjobs and sysjobsteps system tables.
The following provides the name of the job and the step name. It uses the standard % wildcard so replace myjob in “%myjob%” below

More info on SQL system tables here:
MS Docs
MS Docs

Maintain MSDB email tables

Published / by Chris Smith / Leave a Comment

Credits:
SQL Server Central
SQL Authority
Microsoft Docs
Microsoft Docs

We recently had an issue where MSDB was growing very quickly, it turned out it was due to a huge increase in data we was directly emailing to users.
We now run a weekly job just to maintain the MSDB email tables as whole copies of emails, attachments and logs are kept. I first run a stored procedure designed to maintain the tables and then directly delete from the tables (I would expect these to delete nothing).

Find processes which have been using tempdb

Published / by Chris Smith / Leave a Comment

Credit: Microsoft TechNet
On some occasions a few MS SQL databases have exhausted tempdb space, this was due to users leaving computers running for weeks on end and the program using different isolation levels or open transactions.
I use the following in SQL in see which process ID have been running the longest, as its a select statement it can of course be amended to your requirements (Adding an order by clause or just selecting the columns you require).

You should see something like the following, rather than kill of the transaction if it is a user try and educate the user to come out of transactions when finished.

Add a user to DatabaseMailUserRole on msdb

Published / by Chris Smith / Leave a Comment

I had a user unable to use a script which generated an email, I found the user didn’t have permission, Microsoft state:
To reduce the surface area of SQL Server, Database Mail stored procedures are disabled by default.
To send Database Mail, you must be a member of the DatabaseMailUserRole database role in the msdb database.

You can add the user manually by:
Opening SQL Server Management Studio
Connecting to the server in question
Expand Databases
Expand System Databases
Expand msdb
Expand Security
Expand Users, right click the user or group and click properties
If the user is not in the list right click users and add the user or group
On the Membership tab ensure the user or group is a member of DatabaseMailUserRole

If you are lazy use a script like the one below (It will return a 1 if SQL can’t find the user or the user already has access, 0 means it was successful)

Source: Microsoft TechNet
Microsoft MSDN

Notes:
I found using Windows Active Directory Groups required an extra step, by default all users have access to the Public Profile but not groups on SQL Mail.
Groups need to be added via “Manage Profile Security” –> “Private Profiles” tab.
As the group would be added select the AD group from the drop down box.
Ensure Access is ticked and click Next and OK.
As I have only done this the once I haven’t looked up if this can be done directly in T-SQL.