Welcome to my Non-Blog!

 

Under construction? …always!

“Dammit Jim, I’m a DBA, not a travelographer!”

blog_wallpaper

Sometimes, I need to do something out of the ordinary, that is, not a day-to-day task.  Maybe I need to clone my SSD to a larger one, or setup a service to start on boot in Linux,  or move from a 2D desktop to a 3D ‘cube’ desktop, maybe setup some SSH tunnels.  Who knows.  But it’s usually stuff that I need to do very rarely, and when the time comes when I need to do it again, maybe a couple of years or more later, I’ve completely forgotten how I did it and have to re-research and re-learn everything.
For tasks such as this, I’m creating a library of how-to style articles – you can find these top right under Article Listing.

For day-to-day problem solving, I’ve started keeping a record of how I solved certain problems and these can be found under Solved-Issues, again, top right.

Article Listing and Solved Issues are both searchable and you’ll find a search box on the right.

I initially started this for my own benefit, but I hope you will find it useful too.

Sybase: List SP execution count with last execution and creation dates

I was recently tasked with extracting a list of all stored procedures with names matching a certain pattern and the number of times they were executed, who by, when it was last executed, and their creation dates.  Below is an example execution with output using ‘rpt’ as a search parameter. You can run the SP without supplying a search parameter in which case it will return results for all stored proc executions that it can find in the auditing tables.
Note: you might need to modify the code slightly if the names of your auditing tables are different, or if you have more than two audit tables for switching in the sybsecurity database.

1> sp__spexecs rpt
2> go
Login Name                     Stored Procedure Name                    DB Name                        Execution Count Last Execution      Creation Date
------------------------------ ---------------------------------------- ------------------------------ --------------- ------------------- -------------------
CharltonH                      sp_rpt_check_notification_type           BIG_PRODUCTION_DB1                     5247823 Aug  1 2018  3:52PM Nov 11 2016  8:34AM
JuliaR                         sp_rpt_st_find_customer                  BIG_PRODUCTION_DB2                      423871 Jul 12 2018  6:37PM Jan 15 2015  6:55PM
KurtR                          sp_rpt_apply_discount_pct                BIG_PRODUCTION_DB1                      305388 Jul  2 2018  1:21PM Sep 26 2017  8:34AM

NOTE: This SP uses information in the Sybase auditing tables, so for this to work, you will need turn on auditing for the stored procedures you want to report on.  The execution count and date of last execution depend on the auditing information, so the results will depend on how long you run auditing for against those stored procs.

Side Note: Assuming you have auditing already enabled, as a quick reference you can turn on auditing of a stored procedure with the following command
exec sp_audit exec_procedure, ‘all’, <stored proc name>, ‘on’

So, once you’ve got your auditing on and have been running it for a period of time you feel is satisfactory to get a good sample of the usage characteristics of those stored procs, then it’s time to have a look at the results.
First though, we need to create some indexes to help the report run nice and quickly, especially if you have a very large ‘sysaudits’ table in sybsecurity_archive, eg. greater than 5 million rows.
I added 3 indexes to match the clauses in my queries – turns out the one in red is the most important one:

1> use sybsecurity_archive
2> go
1> create index event_idx on sysaudits(event)
2> go
1> create index objname_idx on sysaudits(objname)
2> go
1> create index loginname_idx on sysaudits(loginname)
2> go

Here’s the query plan showing which index the optimiser chose:

|SCAN Operator (VA = 0)
| FROM TABLE
| sybsecurity_archive..sysaudits
| s1
| Index : objname_idx
| Forward Scan.
| Positioning by key.
| Keys are:
| objname ASC
| Using I/O Size 16 Kbytes for index leaf pages.
| With LRU Buffer Replacement Strategy for index leaf pages.
| Using I/O Size 16 Kbytes for data pages.
| With LRU Buffer Replacement Strategy for data pages.

Below is the code to create the stored proc – remember to modify it to match your auditing tables setup:

create procedure sp__spexecs 
(
@search_pattern varchar(40) = null
)
as
------------------------------------------------------------------------------------------------------------
--Part 0: Setup variables -- Cursors can only be declared after temp tables created
-- create tables and declare cursors and variables
create table #SP_users (loginname varchar(30))
create table #SP_names (sproc_name varchar(40), dbname varchar(30))
create table #SP_execs_per_user (loginname varchar(30), objname varchar(40), dbname varchar(30) null, exec_total int, last_exec datetime null, crdate datetime null)

declare user_cursor cursor for select loginname from #SP_users
declare sp_cursor scroll cursor for select distinct sproc_name, dbname from #SP_names

declare @current_user varchar(30)
declare @sproc_name varchar(40)
declare @dbname varchar(30)
declare @execs int
declare @maxdatetime datetime

if @search_pattern is null
begin
print "Searching executions of ALL stored procs..."
select @search_pattern = "%%"
end
else
begin
print "Searching executions of stored procs matching %1!...", @search_pattern
select @search_pattern = "%" + @search_pattern + "%"
end
------------------------------------------------------------------------------------------------------------


------------------------------------------------------------------------------------------------------------
--Part 1: gather list of users who execute matching proc names into temp table
--
set nocount on
insert #SP_users select distinct s1.loginname from sybsecurity..sysaudits_01 s1 
where s1.event = 38 and lower(s1.objname) like @search_pattern and s1.loginname not in (select loginname from #SP_users)
insert #SP_users select distinct s1.loginname from sybsecurity..sysaudits_02 s1 
where s1.event = 38 and lower(s1.objname) like @search_pattern and s1.loginname not in (select loginname from #SP_users)
insert #SP_users select distinct s1.loginname from sybsecurity_archive..sysaudits s1 
where s1.event = 38 and lower(s1.objname) like @search_pattern and s1.loginname not in (select loginname from #SP_users)
-- drop table #SP_users
------------------------------------------------------------------------------------------------------------


------------------------------------------------------------------------------------------------------------
--Part 2: create list of unique SP names
insert #SP_names select distinct s1.objname, s1.dbname from sybsecurity..sysaudits_01 s1 
where lower(s1.objname) like @search_pattern
insert #SP_names select distinct s1.objname, s1.dbname from sybsecurity..sysaudits_02 s1 
where lower(s1.objname) like @search_pattern
insert #SP_names select distinct s1.objname, s1.dbname from sybsecurity_archive..sysaudits s1 
where lower(s1.objname) like @search_pattern
-- drop table #SP_names
------------------------------------------------------------------------------------------------------------


------------------------------------------------------------------------------------------------------------
--Part 3: loop through users, gather executions of matching procs into a total per SP
open user_cursor
fetch user_cursor into @current_user 
open sp_cursor
while @@sqlstatus = 0 -- loop users
begin
fetch first sp_cursor into @sproc_name, @dbname
while @@sqlstatus = 0 -- loop sprocs
begin
select @execs = count(*) from sybsecurity..sysaudits_01 s1 where s1.event = 38 and loginname = @current_user and s1.objname = @sproc_name 
select @execs = @execs + count(*) from sybsecurity..sysaudits_02 s1 where s1.event = 38 and loginname = @current_user and s1.objname = @sproc_name 
select @execs = @execs + count(*) from sybsecurity_archive..sysaudits s1 where s1.event = 38 and loginname = @current_user and s1.objname = @sproc_name 
insert #SP_execs_per_user values (@current_user, @sproc_name, @dbname, @execs, null, null)
select @maxdatetime = max(eventtime) from ( 
select eventtime from sybsecurity..sysaudits_01 s1 where s1.event = 38 and loginname = @current_user and s1.objname = @sproc_name
union all
select eventtime from sybsecurity..sysaudits_02 s1 where s1.event = 38 and loginname = @current_user and s1.objname = @sproc_name
union all
select eventtime from sybsecurity_archive..sysaudits s1 where s1.event = 38 and loginname = @current_user and s1.objname = @sproc_name ) et_dt
update #SP_execs_per_user set last_exec = @maxdatetime where loginname = @current_user and objname = @sproc_name
--dynamic sql here
exec ('update #SP_execs_per_user set crdate = (select crdate from ' + @dbname + '..sysobjects where name = @sproc_name) where loginname = @current_user and objname = @sproc_name and dbname = @dbname')
fetch sp_cursor into @sproc_name, @dbname
end
fetch user_cursor into @current_user
end
select loginname "Login Name", objname "Stored Procedure Name", dbname "DB Name", exec_total "Execution Count", convert(char(21),last_exec) "Last Execution", convert(char(21),crdate) "Creation Date" from #SP_execs_per_user order by exec_total desc
--select * from #SP_execs_per_user order by exec_total desc -- where exec_total <> 0 
------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------
-- tidy up
close user_cursor
close sp_cursor
deallocate cursor user_cursor
deallocate cursor sp_cursor

drop table #SP_users
drop table #SP_names
drop table #SP_execs_per_user

set nocount off

Happy reporting!

SSMA – Database Owner Mismatch

While performing a Sybase to SQL Server migration recently, I encountered the following error:

‘The database owner SID recorded in the master database differs
from the database owner SID recorded in the database
‘<dbname>’. This may lead to an error during
synchronisation. You should correct this situation by resetting the
owner of the database ‘<dbname>’.

Here’s the actual dialogue:
ssma_db_owner_mismatch_error

In this case, I wanted to retain the ownership as per the properties of the database itself, which was ‘sa’, as opposed to what was in master..sysdatabases.  To do this, I used the following command:
ALTER AUTHORIZATION ON DATABASE::YourDatabaseName TO sa

This had the effect of updating master..sysdatabases changing the entry for that database to have the SID for ‘sa’.

This could have been more problematic if it were something other than ‘sa’.

Others have suggested using sp_changedbowner, but this is a legacy command and will not be supported in future versions of SQL Server.

Availability Groups – Endpoint Authentication Error

When working with MS Windows, you can encounter errors which don’t always make a whole lot of sense.  Here’s one I encountered recently when setting up an Availability Group:

“The Endpoints tab lists at least one endpoint that uses only Windows Authentication. However, the server instance might be running under a nondomain account. To use the listed endpoint, change the corresponding SQL Server service account to a domain account. To continue using the nondomain account, alter the endpoint to use a certificate.”

The dialogue looks like this:Availability_Group_Endpoint_Error

“But, but, I’m using a Domain account! Look!”
AG_Endpoints_bad

So, what’s wrong!?
Answer: The format of the domain account.

It’s user@domain vs DOMAIN\user.  Here’s what I mean:
AG_Endpoints_right_wrong

If you supplied the account details via the Service/Properties dialogue and you used the Browse and then Check Names buttons, then it brings it back in the wrong format, thus:

AG_services_properties_dom_account_setup

You cannot change it within the AG setup wizard, it picks it up from the Services value and will not allow you to change it.

Two ways to fix.
1) Solution 1 – Via SQL Server Configuration Manager
If you’ve spent some time reading documentation on SQL Server, you should have seen somewhere that it says to make any account changes via MS SQL Server Configuration Manager, and that’s one way to fix it.
Go to said application and specify the account there – when you use the Browse/Check Names buttons there, it will bring back the account in the correct DOMAIN\username format.

SQLSrvMgr_ss_1

SQLSrvMgr_ss_2

2) Solution 2 – Just type it in manually
Go back into Services, SQL Server service, Properties, Log On tab and MANUALLY just type it in.
Enter the password and that’s job done.

Services_Log_On

Whether you used method 1 or method 2 above, you don’t need to restart the service. Just go back to the AG configuration dialogue, hit the Refresh button and it will now pick up the account details in the correct format.  Do this for any remaining servers which are incorrect and when you hit Next it will then allow you to move on to the next screen.