SQL DBA Blog

Author: Peter Whyte

  • Granting the VIEW SERVER STATE Permission in SQL Server

    In SQL Server, the VIEW SERVER STATE permission allows a user to view server-wide dynamic management views (DMVs) and functions. This is often useful for troubleshooting, monitoring, and performance tuning. In this blog post, I’ll share how to check which users have the VIEW SERVER STATE permission, and…

    Read More

  • Why Is the Database in “In Recovery” Mode?

    In this post I’m hoping to help you resolve issues if your SQL Server Database is stuck “in Recovery” Mode. SQL Server enters recovery mode to ensure data integrity by:> Analyzing Transactions: Identifies changes since the last checkpoint.> Redoing Transactions: Reapplies committed changes not yet written to the…

    Read More

  • How to View the Definition of a Stored Procedure

    In this post, I’m sharing some SQL Scripts that will help you view the definition text of Stored Procedures and other objects in SQL Server. Ways to Query Stored Procedure Text Like a lot of tasks in MSSQL, there’s various ways we can get this information, including:> sys.sql_modules:…

    Read More

  • SQL Script to Show Exclusive Locks on Tables

    In this post I’m sharing a SQL script that will show exclusive locks for all tables in a database, with added info to help you understand what we’re looking at. Exclusive locks are a natural thing in SQL Server which ensures data integrity during updates or deletions by…

    Read More

  • How to Get SQL Server’s IP Address

    When managing or troubleshooting SQL Server, you might need to get the IP address your SQL Server instance is listening on. While servers can listen on multiple IP addresses, SQL Server offers a straightforward way to determine which address is used for your current connection. In this post…

    Read More

  • Identify Missing & Unused Indexes in SQL Server

    Indexes are crucial for SQL Server query performance. Everyone needs an Index. However, improper indexing strategies can degrade database performance, leading to slow queries and cause resource bottlenecks. Effectively managing indexes involves not only identifying the missing ones that could improve performance, but also pinpointing unused indexes that…

    Read More

  • SQL Server Performance Tuning: Index Fragmentation Scripts

    In this post I’m sharing more scripts to help with SQL Server performance tuning. This time, we’re looking at index and heap fragmentation which are useful for pinpointing inefficiencies on your database. Fragmentation, whether in indexes or heaps, can lead to slower queries, inefficient storage, and increased maintenance…

    Read More

  • Show User Activity and Blocking in SQL Server

    In this post I’m sharing a SQL script that will show currently running and blocking queries in SQL Server, with some other useful info along the way. This is script I run often as a DBA. It’s useful for identifying blocking or long running transactions, or checking in…

    Read More

  • Download and Install SQL Server Management Studio (SSMS)

    In this short post, I’ll share how to easily download and install SQL Server Management Studio (SSMS) on your Windows computer. I’ll include some tips to help improve your SSMS experience too! Step 1: Download SSMS Go to the official Microsoft website to download the latest version of…

    Read More

  • SQL to SQL: A Practical Guide from a DBA

    If you’re looking to connect, migrate, or convert between SQL databases, this post is aimed at you. Whether you’re working with SQL Server, MySQL, PostgreSQL, or others, I’m hoping to help explain some common tasks you might need to perform. Contents: > Connecting SQL Databases > Linked Servers:…

    Read More

Recent Posts

Categories

– AWS
– DBA Stories
– Linux
– MySQL
– Other
– Postgres
– PowerShell
– Redshift
– SQL Server
– Windows Server

Tags

Always On Availability Groups (AAG) (3) Certificates & Encryption (3) Change Data Capture (CDC) (2) Database Admin (74) Database Backups & Recovery (13) Database Mirroring (2) Deleting Data (1) Error Messages (4) Git Commands (5) Importing & Exporting Data (2) Linked Servers (3) Linux Admin (2) Logging & Monitoring (1) Measuring Databases (10) Microsoft Patching (2) PowerShell Scripts (1) SQL Server Agent (4) SQL Server Database Files (1) SQL Server Data Types (2) SQL Server Management Studio (SSMS) (14) SQL Server Network Connectivity (3) SQL Server on Linux (1) SQL Server Patching (2) SQL Server Performance (5) SQL Server Permissions (2) SQL Server Processes (5) SQL Server Replication (2) SQL Server Scripts (13) Windows Admin (21) Windows Authentication (2) Windows Automation (1) Windows Events (2) Windows Firewall (4) Windows Subsystem for Linux (WSL) (19)