SQL-Server-Scripts

Navigation

• License
• Others License
• Contributions
• Purpose
• Scripts
  • ADMIN_1ST_Aid_1_Running_Tasks.sql
  • ADMIN_1ST_Aid_1_Running_Tasks_Extended.sql
  • ADMIN_2ND_AID_Check_Blocking_Quick.sql
  • ADMIN_Assign_DTS_Permissions.sql
  • ADMIN_Backup_Script_Multi_Database.sql
  • ADMIN_Retrieve_Statistics_Info_Outdated_and_Update.sql
  • ADMIN_Blocking_Locking_Hierarchical.sql
• Stored Procedures
  • spdeletehistory.sql

License

The SQL-Server-Scripts use the GNU General Public License v3.0. except for ...

Others License

Some scripts are not my copyright or copyleft. If the scripts are the intellectual property of someone else, then the copyright/license is duely noted in the script/file/program.

Contributions

Certain code elements and/or whole scripts have been contributed by others. Following is a list of contributors to this repository:

• John Ness (Owner)
• Hannah Vernon (Contributor) 1 2

Purpose

The purpose of this repository it to enable me to find the scripts/procedures/programs I use most over and over again. I would also like to make these various scripts available to the community.

Scripts

Following is a list of scripts that may be of interest.

ADMIN_1ST_Aid_1_Running_Tasks.sql

The current script quickly checks the running tasks of a SQL Server instance.

ADMIN_1ST_Aid_1_Running_Tasks_Extended.sql

Display a list of running tasks in a given SQL Server instance. Comment out any of the various LEFT elements to reduce the amount of information displayed.

ADMIN_2ND_AID_Check_Blocking_Quick.sql

This script quickly checks for blocks/blocking on a SQL Server instance.

ADMIN_Assign_DTS_Permissions.sql

This script is a summary of permissions that can be assigend to SQL Server Logins (Windows Authenticated / Native) to facilitate the use of SSIS/DTS packages in a SQL Server instance.

ADMIN_Backup_Script_Multi_Database.sql

Backup script for multiple databases. Just when you need a simple script to dump one or more databases to a disk drive and don't want to install any other of the great scripts out there.

ADMIN_Retrieve_Statistics_Info_Outdated_and_Update.sql

Small non-parameterized statement to retrieve statistics that have become outdated and may or may not be triggered by SQL Server's "auto update statistics" algorithm.

ADMIN_Blocking_Locking_Hierarchical.sql

A script that displays a list of blocked/locked SQL Server sessions.

Back to top

Stored Procedures

Following is a list of stored procedures that may be of interest.

spdeletehistory.sql

The stored procecdure I wrote here is a wrapper for the internal SQL Server stored procedure sp_delete_backuphistory. It uses some basic parameters to go back in time amd delete the backup history in lumps. The pre-defined default is to go back 1080 days and delete the backup history in steps of 1 up until 180 days ago.

The pre-defined default values are specified in the code itself and are:

set @iDaysBackToStart_CONST = 1080
set @iDaysToKeep_CONST = 180
set @iDayStep_CONST = 1

These pre-defined default values should be modified before you create the procedure in your environment to meet your requirements. I set them as fail-safes.

The basic run-time parameters to be used are:

@iDaysBackToStart int = 0
@iDaysToKeep int = 0
@iDayStep int = 0
@iDebug int = 0 

You could run the script with the follwoing values:

spdeletehistory @iDaysBackToStart=3000, @iDaysToKeep = 200, @iDayStep = 5

If you specify run-time values that are smaller than the pre-defined defaults (set during stored procedure creation) then you will receive an error message.

Back to top

History

• 2020-12-21 Switched master branch to main
• 2020-12-23 Simple change to allow commit
• 2022-11-16 Added Contributors section