SQL Server – DBA Scripts

03 October,2012 by Jack Vamvas

There are powerful DBA scripts available for DBAs in this post that will help you build a strong foundation in managing  Database Servers.

Click here for  Powershell scripts to manage SQL Servers

I’ll keep adding more DBA scripts to this post. Contact me  for any other DBA  scripts

Last Updated 22nd January 2016

Troubleshooting

SQL Server – Current queries executing on SQL Server

SQL Server – Find sql text in active query with a sql derived table

SQL Server - Who made DDL table changes on the database.

SQL Server - Cached blocking history with sys.dm_db_index_operational_stats

SQL Server - Monitoring a Rollback and sys.dm_exec_requests

SQL Server - Last time a stored procedure was executed

SQL Server Error Log Check

SQL Blocking script

SQL Server – MSDB Suspect pages table and bad pages  New

SQL Server - How to find Open Transactions New

SQL Server - Use sys.dm_tran_locks to find requests waiting for an exclusive lock

How to monitor Blocked Processes with SQL Alert and email sp_whoisActive report

Security Management

How to Script database role permissions and securables

SQL Server - Find sql database users and Find database roles

SQL Database roles and members

SQL Server - Orphaned users and how to Synchronise Users and Logins after SQL Server 2005 restore

SQL Server - List all SQL Server users

Find database users mapped to db_owner role 

How to List SQL logins and database user mappings

SQL Server Performance Management

SQL Index Usage

DBCC MEMORYSTATUS

SQL Server – Find tables with page locks using sys.dm_db_index_operational_stats

SQL Server – Find high impact queries with sys.dm_exec_query_stats

SQL Server – Performance Top Queries by Total CPU Time 

Find Table Scans in Query Plan Cache

SQL Script : How to find Nested Loops in Execution Plans for a specific table New

How to find Longest running queries in SQL Server New

 

Backup\Restore Management

SQL Server - Predict SQL BACKUP DATABASE finish time with sys.dm_exec_requests

SQL Server – Restore Database Estimated Finish Time

SQL Server - Display restore history for a single database

SQL Server - Display sql backup history for a single database

SQL Server - Move MDF/LDF files to a designated path

SQL Server - BackUp All Databases

SQL Server BACKUP folder in windows registry

SQL Server – How to Predict the DBCC SHRINKDATABASE finish time New


Index Management

SQL Server - How to find the largest sql index and table size

SQL Server - Predict ALTER INDEX REORGANIZE finish time

SQL Server Disable Indexes and Rebuild Indexes dynamically

SQL Index Fragmentation and sys.dm_db_index_physical_stats

SQL Server - Index missing Statistics

SQL Server - List all indexes of all tables

sys.dm_db_index_usage_stats

SQL Server - Delete all statistics from a table

SQL Server Update Statistics with FULL SCAN

SQL Server - sp_MSforeachtable

SQL Index Usage

 

Object Management

SQL Server - Find the Database File Size and Log File Size

SQL Server – sys.master_files and list all database files

SQL Server - DBCC checktable

SQL Server – refresh all Views

SQL Server - Table sizes and percentages

SQL Server - All tables row count

List Foreign Key Constraints -MS SQL

SQL Server Database size Check

Search for text in stored procedures

OpenXML - more than 8000 characters

SQL Server – Check Data and Log files are on separate drives

SQL Server – Get sql table size and last updated for all tables

SQL Server – Find last time of update on a table

How to predict DBCC CHECKDB finish time New

 Storage

SQL Server Database Size and Free Disk Space Daily Report

SQL Server - Space used for all databases

SQL Server - Space Integrity Check

 

SQL Server Agent

SQL Server Agent Job Steps - SQL Server DBA

 

TempDB

SQL Server – List current tempdb statements New

VMWARE

How to find VMWare ESX Host event messages using VPXV_EVENTS New

Query VMWare vCenter VCDB database for guest and host details New

Misc

SQL Server - Sp_send_dbmail and bcc

 

Copyright c 2016 – +Jack Vamvas. All rights reserved. No

part of these scripts may be reproduced, translated, posted or shared in any

form, by any means.

The information provided in this post is provided "as is" with no implied

warranties or guarantees.

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server – DBA Scripts


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer