SharePoint Secure Store Audit Management

Have you ever experienced uncontrolled growing size of SQL Server Data Drive for a SharePoint Environment? If yes, the below could be the reason.

There is a data base in SharePoint environment whose name started with “Secure_Store_Service_DB_”

In that database there is a table called SSSAudit, which keep growing if the Audit is enabled.

There can be 2 way to control this.

1.   Disable the Auditing Feature
2.   Change the Purge frequency

These above configuration is available in a table called SSSConfig

Which can be updated by simple SQL Query.

 

  UPDATE dbo.SSSConfig SET EnableAudit = 0

 

  UPDATE dbo.SSSConfig SET PurgeAuditDays = 10

Aborting/Killing a Long Running Stored Procedure

Take a scenario where you wrote a SQL Stored Procedure in MS SQL Server DB and make some mistake of not handling a scenario where the SP keep running for infinite time or taking hours to finish the required task.

In that case basically you are stuck with SQL Server consuming huge amount of memory and blocking the server for further use.

There can be 2 way to handle this.
1. Shutdown the MS SQL Server
2. Abort/Kill/Stop the SP

While option 1 is not a practical way to handle in some environment where other people are using the SQL Server.
I will show you how to make it using option 2.

This has 2 step.
Step 1: Identify the Thread/Process which is executing the SP
Step 2: Kill the Process

declare @spid int

select  session_id,*
from    sys.dm_exec_requests handle 
outer apply sys.fn_get_sql(handle.sql_handle) spname
where   spname.text like '%USP_LongRunningSP%'
print @spid

exec ('kill ' + @spid)

Where USP_LongRunningSP is the Procedure name.