SQL Server Database Administration Tips
All Tips
Administration
Administrators
Archiving
- Archiving Data in SQL Server
- Creating a Table Warehouse to Retain Historical Data for a SQL Server Table
- SQL Server Archive and Data Retention Considerations
Attach and Detach
- Access is denied error when attaching a SQL Server database
- Attach SQL Server Database Without Log File
- Auto generate SQL Server database attach and detach scripts
- Database Attach Failure in SQL Server 2008 R2
- How to Attach a SQL Server Database without a Transaction Log and with Open Transactions
- How to use DBCC CHECKPRIMARYFILE to attach several SQL Server databases in seconds
- Limitations when attaching a SQL Server Database
- SQL Server Attach and Detach Database Examples
Auditing
Automation
- 4 Factors When Choosing to Automate SQL Server Tasks
- Desired State Configuration and the SQL Server DBA
- Desired State Configuration for SQL Server - Configurations
- Internals of a Desired State Configuration (DSC) Resource
- Python vs PowerShell for System and SQL Server Administration
Capacity Planning
- SQL Server Data Mining Add ins for Office 2007 to Predict Database Growth Part 2
- SQL Server Data Mining with Office 2007 to Predict Database Growth Part 1
Command Line
Configuration
- Automatic Statistics Update Slows Down SQL Server 2005
- Different ways to make a table read only in a SQL Server database
- How does AUTO_UPDATE_STATISTICS_ASYNC work with Sql 2005?
- How to move a SQL Server database within the same instance without losing settings
- Identify SQL Server Instance and Database Collation Using T-SQL and SSMS
- Retrieving SQL Server Database Properties with DATABASEPROPERTYEX
- Snapshot Isolation in SQL Server 2005
- SQL Server SERIALIZABLE isolation level and duplicate key insertion attempts
Connectivity
- Capture SQL Server Connectivity Errors in the ERRORLOG
- Remote Desktop Tools for Mac and Linux Machines to Connect to SQL Server
Data
- Building Smarter Scripts for SQL Server Data Updates
- Generate INSERT scripts from SQL Server queries and stored procedure output
- How to use the SQL Server sys.fn_PhysLocFormatter undocumented function
- Including Predetermined Datasets in a Microsoft Database Project
Database
- Create SQL Server Database using SQL Server Management Studio
- How to Bring SQL Server Database Online
- How to Take SQL Server Database Offline
- Rename logical database file name for a SQL Server database
- Rename SQL Server physical database file names using detach and attach method
- Rename SQL Server physical database files using T-SQL
- Renaming Physical Database File Names for a SQL Server Database
Database Dictionary
Database Objects
Databases
- Build an Automated SQL Server Database Inventory System
- How to Find Unused Databases in SQL Server
- Script to Drop and Delete Offline SQL Server Databases and Data Files
- SQL Server Database Decommissioning Check List
- Understanding how SQL Server stores data in data files
DDL Trigger
Delete
Deployment
- Create a Self-Service Framework for SQL Server DBA Tasks
- Developing Similar Structured Multi-Customer Databases with SQL Server Data Tools (SSDT)
- Execute and Track T-SQL Script Executions with PowerShell
- Recover a SQL Server database prior to database changes
- SSIS Package for Automated Code Deployment of SQL Server Database Changes
- Tool to Deploy SQL Server Database Changes
- Troubleshoot TFS SQL Server Slowdowns
- Using Advanced Publish Settings for Visual Studio Database Project
- Using SQLCMD to Execute Multiple SQL Server Scripts
Development
Duplicate Data
Features
File System
Filegroups
- Filegroups in SQL Server 2005
- How to remove a SQL Server Data file for a multi-data file database
- Move data between SQL Server database filegroups
- Move SQL Server Tables to Different Filegroups
- Using Multiple Filegroups for a Database and Changing the Default Filegroup
- Using Multiple Filegroups in a SQL Server Database
Free Space
- Determining Free Space Per SQL Server Database
- Different ways to determine free space for SQL Server databases and database files
- Find SQL Server data and log files that are almost out of space
- Getting more details with an enhanced xp_fixeddrives for SQL Server
- How To Find Space Used by Tables and Indexes in SQL Server – Part 3
- Out of space on the C drive of your SQL Server and ways to reclaim disk space
- Script to Get Available and Free Disk Space for SQL Server
- SQL Server does not reclaim space used when inserts fail
- SQL Server File and Filegroup Space Usage – Part 1
- SQL Server Index and Partition Space Usage - Part 2
Growth
- Graph SQL Server Database Growth Using R and PowerShell
- How to Check Monthly Growth of Database in SQL Server
- Monitor SQL Server Database Size, Growth and Expected Growth with PowerShell
- SQL Server Video on Database Growth
Installation
- Find SQL Server Installation Date for all Instances with PowerShell
- SQL Server Download Quick Links
- Using SQL Server Discovery Tool to list installed components
Meta Data
- Keeping Track of your SQL Servers with a DBA Configuration Repository
- Maintaining SQL Server database metadata to identify object changes
- Managing SQL Server Database and Application Metadata
- Using SQL Server meta data to list tables that make up views
Miscellaneous
Monitoring
- Check current patch levels for all SQL Servers in enviornment
- Quick SQL Server Health Check Report
- Understanding SQL Server Schedulers, Workers and Tasks
Naming Conventions
- Enforce SQL Server Database Naming Conventions Using DDL Triggers
- How to rename a SQL Server database
- Script to rename constraints and indexes to conform to a SQL Server naming convention
Passwords
Planning
- DBAs Checklist for Designing and Reviewing SQL Server System Architectures
- Infrastructure Planning for a SQL Server Data Warehouse
- New SQL Server Database Request Questionnaire and Checklist
PowerShell
Scripting
- Introduction to C# Scripting for SQL Server DBAs
- Learn SQL Server from Microsoft’s Own Code
- MSSQL-Scripter Tool and Examples to Generate Scripts for SQL Server Objects
Scripts
- Automate Gathering SQL Server Information and CPU Count
- Automatically Build a SQL Server Script Database Library from Your Script Files
- Find the Last Windows Server Reboot Time and Last SQL Server Restart
- Finding SQL Server views with (or without) a certain property
- Making SQL Server metadata queries easier with these new Views
- Over 40 queries to find SQL Server tables with or without a certain property
Search
Servers
Service Packs
- No More Service Packs for SQL Server
- Slipstream Installation of Service Packs and Cumulative Updates on SQL Server 2016 and Higher
- SQL Server HotFix History Analysis
Services
- Checking SQL Server Services Owner
- Checking SQL Services Status - An Evolution Part 1
- How to stop and start SQL Server services
- Overview of the SQL Server Browser service
- Pausing SQL Server Services to Stop New Connections
- Remotely Shutdown, Restart or Log Off the local or network computers
- SQL Services Status Check - An Evolution Part 2
- SQL Services Status Check - An Evolution Part 3
- Start, Stop, Pause and Query Windows SQL Server Services Remotely
Shrink
- How to Shrink SQL Server Database Files
- How to shrink the transaction log file in SQL Server
- Incrementally shrinking a large SQL Server data file using PowerShell
- Manual cleanup Change Data Capture for a SQL Server database
- Shrink SQL Server Transaction Log for all Databases
Snapshots
SQLCMD
- Execute SQL Server Script Files with the sqlcmd Utility
- Introduction to SQL Server's sqlcmd utility
- SQL Server scripts to use with sqlcmd
- sqlcmd User Variables and Variable Precedence Examples
- Using SQL Server sqlcmd scripting variables
Storage
- Build a quota system for SQL Server user defined tables
- Capacity Planning for SQL Server 2000 Database Storage
- Clean unused space when a SQL Server table with a variable length column is dropped
- Configuring Windows Instant File Initialization for SQL Server 2005
- Determine Free Disk Space in SQL Server with TSQL Code
- Determine Free Space, Consumed Space and Total Space Allocated for SQL Server databases
- Determining Drive Letters in Use by SQL Server Databases
- Easing the SQL Server Database Capacity Planning Burden
- Effect of Instant File Initialization within SQL Server
- Identify SQL Server Database Growth Rates
- Importing and reporting physical database file size for a SQL Server database using SSIS and SSRS
- Managing the size of the SQL Server SSIS catalog database
- Move SQL Server data between data files in a single filegroup
- Options to Move a Big SQL Server Database to a New Drive with Minimal Downtime
- Reviewing Logical Hard Drive details using T-SQL and OLE Automation Procedures
- SQL Server Move Database Files Step By Step
Synchronization
System Objects
- SQL Server System Objects in User Defined Databases
- System Information in SQL Server 2000 vs SQL Server 2005
Tables
TempDB
Third Party Apps
- SQL Server Third Party Application Requirements Document and Checklist
- Third Party Application Checklist for SQL Server
Tools
- Creating a SQL Server DBA's Library
- Install DBATools with Azure VM Custom Script Extension - Github
- Install DBATools with Azure VM Custom Script Extension – Azure Portal
- SQL Server Database Change Management Use Cases with Liquibase - Part 3
- SQL Server Database Change Management with Liquibase
- SQL Server Database Change Management with Liquibase - Advanced Features
Transaction Logs
- Find SQL Server databases where log file is too large
- How to determine SQL Server database transaction log usage
- How to find user who ran DROP or DELETE statements on your SQL Server Objects
- How to read the SQL Server Database Transaction Log
- Manage the size of SQL Server Transaction Logs
- Managing SQL Server 2000 Transaction Log Growth
- Move SQL Server transaction log files to a different location via TSQL and SSMS
- Performance Issue with large number of Virtual Log Files in SQL Server Transaction Log
- Read SQL Server Transaction Log Backups to Find When Transactions Occurred
- Recover deleted SQL Server data and tables with the help of Transaction Log and LSNs
- Remove Unnecessary SQL Server Transaction Log Files
- SQL Server 2008 Transaction Logging Enhancements
- SQL Server Transaction Log Grows And Fills Up Drive
Troubleshooting
