SQL Server's Maintenance 'SHAUP'

Maintenance 'SHAUP'

There are certain activities that are very useful in day to day maintenance.

S:- Setting Up Log File for Maintenance Jobs to find the route cause in case of failures.

H:- History Cleanup - We can automate History cleanup for text/Backup files and Logs in msdb database.

A:- Apply the latest Service Pack and C-Updates.

U:- Update Statistics and Index Rebuild/Re-Org

P:- Perfmon and Profiler to capture Performance Bottlenecks in one of the Table in DBA database (Note:- Please ensure to run profiler in client system to avoid system hang/temp file space Full).

To run the maintenance either during off hours or for SOMETIME there is maintenance window approved by application owner and Your Goodself can schedule the jobs accordingly.

THERE are certain things that can be found from SQL Server such as Deadlocks, Logins and Job failure etc.

It's necessary to find ONGOING activities from DMVs and sys.sysprocesses such as Long running/High CPU queries and Blocking.

When we say 'SIT' in regards to SQL Server, its nothing but 'SQL Internal Tutorial'

https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/sql-server-in-memory-oltp-internals-for-sql-server-2016?view=sql-server-ver16

Additionally,

1. Two things that can be checked thoroughly while facing performance issue.

- SQL Server Error Logs
- Windows Event Logs

2.

True

- The permissions for DDL and DML activities can be given / managed separately.

False

- Truncate cannot be rolled back.

3.

Please also read

Data Transfer Techniques

- Replication
- Log Shipping
- Linked Server
- Import Export

Related Links...

https://learn.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/tasks/transferring-data?view=sql-server-ver16

https://learn.microsoft.com/en-us/troubleshoot/sql/security/transfer-logins-passwords-between-instances

Best Regards

Amit Vaid