Welcome to my box of tricks (SQL scripts) for Microsoft SQL Server instance and database administration!

I've put considerable effort, over many years, into creating my own scripts, as well as adapting the efforts of many other talented folk in the SQL Server community, to help with my day-to-day job as a Database Administrator on Microsoft SQL Server. Now that I'm approaching the twilight of my professional IT career, I wanted to ensure that this accumulated DBA knowledge store is not lost but is instead shared as widely as possible.

My experience is that having the right tool for the job is half the battle (and knowing how to wield it is the other!), and so I wanted to be able to share as widely as possible these tools that I use: some every day, some frequently, and some only occasionally. But they all deserve a place in my toolbox!

When faced with a SQL Server DBA challenge, don't re-invent the wheel or give it up as too hard: check my toolbox and the blogs of the many generous SQL giants out there on whose shoulders we stand!

I hope you find them useful.

• Age Calculation
• Agent Jobs
• Auditing
• Availability Groups
• Backup and Restore
• Collation
• Compliance
• Compression
• Configuration
• Constraints
• Corruption
• Database Design
• Deadlocks
• Disk Space
• Extended Events
• Fragmentation
• Indexing
• Low-Level
• Operational
• Parallelism
• Performance
• Permissions
• Plan Cache
• Query Store
• Security
• Statistics
• TempDB
• VLFs

Back to top

• Calculating Age in Years (a simple way to calculate someone’s current age, or age as at a particular date)

Back to top

• Change the SQL Agent job history purge period from 30 days to 90 days in the Ola Hallengren sp_purge_jobhistory job
• Remove the @StatisticsSample parameter for all Ola Hallengren IndexOptimize - USER_DATABASES jobs
• List running jobs in SQL Server with Job Start Time

Back to top

• Create audit for database
• Drop server audits for which there is no corresponding database
• Query an audit file

Back to top

• Failover all Availability Groups
• Manually add database to an AG - needed for databases with a database master key
• Read-only routing url generation script
• View read-only routing configurations

Back to top

• Generate RESTORE script for all user databases
• Progress of BACKUP and RESTORE
• sp_RestoreGene - generate RESTORE DATABASE commands
• Update database backup schedules

Back to top

• Changing Database Collation

Back to top

• Find deprecated data types on all databases
• SQL Server naming and design standards compliance review

Back to top

• Find indexes that aren't compressed in all databases
• Suggest compression strategies for tables and indexes
• Tracking page compression success rates

Back to top

• 0 to 60 - Switching to indirect checkpoints
• Adding trace flags to a SQL instance through Registry
• Check for Instant File Initialization
• Check for Locked Pages In Memory
• Check that SQL Agent is configured to send Database Mail
• Correct database file logical names
• Correct database file physical names
• Determine SQL Server version and edition
• Find all instances that have all databases offline (to be run against all instances using a Central Management Server)
• Find database for a nominated data file
• Find databases at the wrong compatibility level
• Find databases that don't have Accelerated Database Recovery enabled
• Find databases with LEGACY_CARDINALITY_ESTIMATION turned on
• Find databases with non-standard Automatic Tuning settings
• Find databases with non-standard Query Store settings
• Find non-default configuration options
• Find SQL Server service info
• Generate script to change all users to have default schema of dbo
• Find databases with default auto-growth values
• Set all databases offline
• Set all databases to 130 compatibility level
• Set all databases to 140 compatibility level
• Set all databases to 150 compatibility level
• Set all databases to auto-grow data files by 100MB
• Set all databases to auto-grow log files by 100MB
• Set all databases to be owned by sa
• Set all databases to CHECKSUM page verification
• Set all databases to have Automatic Tuning with Force Last Good Plan turned on
• Set all databases to have Query Store enabled with query_capture_mode set to Auto
• Set all databases to maximum compatibility level
• Set all databases to simple recovery
• Set all jobs to be owned by sa
• Set AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS ON for all databases
• Set fillfactor to 100
• Set notification for all jobs to email SQL Administrator
• sp_foreachdb
• sp_ineachdb

Back to top

• Create indexes on all foreign keys
• Drop and re-create all foreign key constraints in SQL Server
• Find all non-indexed foreign keys
• Find foreign keys and check constraints that are not trusted
• Re-trust untrusted foreign keys
• Run DBCC CHECKCONSTRAINTS for all foreign key and check constraints

Back to top

• Emergency repair for when Windows Update leaves a FileStream database in Recovery Pending
• Find the last known good date when was DBCC CHECKDB was run on each database Back to top

• Identity values check
• spa_ShrinkColumnSizes

Back to top

• List deadlocks using the system_health Extended Events session

Back to top

• All files ordered by descending free space
• DBCC SHRINKFILE iteratively
• Find all Heaps ordered by increasing size and generate CCI SQL
• Find largest table in all databases
• Get all databases sizes
• List all Clustered Columnstore indexes
• Move Primary data file
• Shrink all log files over 1000 MB to 1000 MB
• Turn all Heaps into Clustered Columnstore with Archive Compression

Back to top

• Capture execution plan warnings using Extended Events
• Extended Events session DurationOver500ms
• Identifying large queries using Extended Events
• List all extended events sessions and whether they are running
• Looking for undesirable events
• Monitoring blocked processes with Extended Events
• Monitoring errors with Extended Events
• Track activity on a table using the Lock_Acquired event
• Track activity on a table
• Track calls to a stored procedure using a wildcard
• Tracking problematic page splits in Extended Events

Back to top

• Rebuild active heaps

Back to top

• Find columnstore indexes than are more than 10 percent fragmented
• Find duplicate indexes using sp_SQLskills_finddupes
• Find large tables for potential Clustered Columnstore Indexes
• Find missing indexes from the Missing Index DMVs
• Find missing indexes from the Plan Cache
• Find missing indexes from the Query Store
• Find queries that use an index from the Plan Cache
• Find queries that use an index from the Query Store
• Find tables without a primary key or clustered index
• Find unused indexes from sys.dm_db_index_usage_stats
• Find unused non-clustered indexes by checking Query Store
• Identifying which databases have index fragmentation problems
• Modify all fillfactors from 70 to 100
• Rebuild all fragmented heaps
• SQLSkills index script 1 - sp_SQLskills_exposecolsinindexlevels
• SQLSkills index script 2 - sp_SQLskills_helpindex
• SQLSkills index script 3 - sp_SQLskills_finddupes (modified)

Back to top

• Find table from page
• How far has my update got

Back to top

• Create a text file with specified contents
• Last instance restart date
• List of all server names from the DBA_REP ServerList_SSIS table
• Locks summary
• Open transactions with text and plans
• Query the Default Trace

Back to top

• Calculate MAXDOP
• Cost Threshold For Parallelism - Plan Cache spread of query costs
• Determining a setting for Cost Threshold for Parallelism
• MAXDOP setting algorithm for SQL Server
• Recommend MAXDOP settings for the server instance

Back to top

• Analyzing 'death by a thousand cuts' workloads
• Breakdown of buffer cache usage by database
• Breakdown of buffer cache usage for a specific database
• Calculate rows inserted per second for all tables
• Find mismatching column types in database schema
• Find non-zero fill factors for identity columns (set back to 100)
• Find non-zero fill factors
• Finding the worst-performing TSQL statement
• Finding the worst running query in a stored procedure
• sp_WhoIsActive extended info
• sp_WhoIsActive in a loop
• sp_WhoIsActive
• Wait statistics, or please tell me where it hurts - Paul Randal

Back to top

• Comprehensive security audit
• Find all permissions & access for all users in all databases
• Fix all orphaned users in all databases
• Script DB level permissions

Back to top

• Find your most expensive queries in the Plan Cache
• Plan Cache queries - find queries using any index hint
• Plan Cache queries - implicit column conversions
• Plan Cache queries - index scans
• Plan Cache queries - key lookups
• Plan Cache queries - missing index
• Plan Cache queries - probe residuals
• Plan Cache queries - query plans that may utilize parallelism
• Plan Cache queries - warnings

Back to top

• Mining the Query Store - looking for index usage in queries
• Mining the Query Store - looking for Key Lookups in queries
• Mining the Query Store - looking for text strings in queries
• Most expensive queries using Query Store
• sp_QuickieStore - Erik Darling

Back to top

• Create logins on AlwaysOn Secondary with same SID as Primary
• Delete all database user accounts for a given server login
• Drop all orphan users
• Enumerate Windows Group members
• Find all orphaned SQL Server users
• Find Windows logins that are no longer in AD
• Last user access for each database
• Orphaned users search and destroy
• Who are the sysadmins in this Instance

Back to top

• Drop all statistics
• Find auto-created statistics objects that overlap with index statistics
• Generate DROP STATISTICS statements for all user-created statistics

Back to top

• Find tempdb data files with differing sizes
• Find tempdbs with uneven initial size or growth
• Who owns that #temp table

Back to top

• Detect too many VLFs
• Reduce VLF count
• Visualizing VLFs

Back to top