SQL Jobs on Always On

This script will check the status of the node in Always On and enable or disable the jobs according to our requirement.

HashBytes Nvarchar(MAX)

SQL SERVER Includes a Hashbytes function, however it's limited to VARCHAR(8000). This can cause a problem when trying to build hashes over wide datasets.

Solution

Expose the C# Hashbytes function using CLR

List file name, size of all databases

lists file name, size of all databases in sql server

Database Backup Information Scripts

Scripts used to validate the backups information as below

1) Database Backups for all databases For Previous Week
2) Most Recent Database Backup for Each Database
3) Most Recent Database Backup for Each Database - Detailed
4) Databases Missing a Back-Up Within Past 24 Hours

Design Errors : PK's with multiple columns on table with identity

Find Tables with identity, which should have PK with 1 column but have a PK which is clustered but has multiple columns.

Design Errors : PK's with multiple columns on table with identity

Find Tables with identity, which should have PK with 1 column but have a PK with multiple columns or where the unique identity is contained in other indexes.

Figure out recovery mode for each database

It is anoying that someone created a new database and left its recovery mode in simple and it caused your backup jobs to fail.

Temporary exclusive database access

This simple script disconnects any other session that is connected to the specified database by temporarily switching the database to single user.

Disk Space Alerts Based on Threshold Value

This script will drop a mail for sql drives if space on drives is below set threshold value. Log drives with 15 and data files drives with value of 10% as threshold value of free space.

Constraints renaming

Rename constraints to enforce naming conventions.