SQL Server - Tech Antons

Tech Antons

Antons is derived from the word 'Antonym' which is another way of normal practice from SQL Server Technical Best Practices.

It is suggested to run below maintenance activities on daily basis which should take around 15-30 mnts.

1. Disable SQL Server Agent

sp_configure 'Agent XPs', 0;  

GO  

RECONFIGURE  

GO  

2. Enable the Profiler Trace - How to Enable Profiler Trace? Click here

3. Dettach the User Databases by Dropping the connections and Re-attach. Refer the screenshot below

To get the full benefit of re-attach DB, please delete the log file before executing below query would initialize the new log file by attach DB.

exec sp_attach_db DB_Admin, 'D:\MSSQL\DATA\DB_Admin.mdf'

go

4. Run 

DBCC dropcleanbuffers

DBCC freeproccache

DBCC freesystemcache ('all')

5. Memory Capping

Step 1. Note the 'value_in_use' from below query

SELECT * FROM sys.configurations

WHERE name = 'Max Server Memory (MB)'

The below steps for memory recapping

Step 2.

SP_Configure 'show advanced options', 1

Go

Reconfigure

Go

SP_CONFIGURE 'max server memory (MB)', 128

GO

RECONFIGURE

GO

SP_CONFIGURE 'max server memory (MB)', 'Value_Noted_from_Step1'

GO

RECONFIGURE

GO

6. sp_configure 'Agent XPs', 1;  

GO  

RECONFIGURE  

GO 

 

7. Deactivate the Trace and delete the Trace files (.trc) and its tables after reviewing.

PLEASE MAKE SURE TO RUN THIS ON PRODUCTION SQL SERVER TO GET FULL BENEFIT OUT OF IT.

Best Regards

Amit Vaid

(Be Healthy)