SQL DBA Blog

Author: Peter Whyte

  • Database cannot be opened, it is in the middle of a Restore

    If you encounter this error in SQL Server, it means the database is still in the RESTORING state, typically because:> A restore operation is still in progress. > A previous restore failed due to corruption or other issues. > The database is in NO RECOVERY mode and waiting…

    Read More

  • PowerShell Script: List Files with Sizes and Dates

    In this blog post, we’ll go through the steps for using a PowerShell script that lists files in a directory along with their sizes and creation dates. This script is useful for disk space and for checking old files in a folder.  List Files with Size and Last…

    Read More

  • 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

Categories
Tags

Always On Availability Groups (AAG) (4) AWS (4) AWS Redshift (6) Database Admin (72) Database Backups & Recovery (14) Database Mirroring (2) Error Messages (5) Failover Cluster Instances (FCI) (1) Git Commands (6) Importing & Exporting Data (2) Linked Servers (3) Linux Administration (2) Logging & Monitoring (1) Microsoft Patching (2) MySQL (4) Postgres (6) PowerShell Scripts (2) SQL Certificates & Encryption (3) SQL Server Agent (5) SQL Server CDC (2) SQL Server Data Types (2) SQL Server Management Studio (SSMS) (16) SQL Server Networking (3) SQL Server on Linux (1) SQL Server Patching (2) SQL Server Performance Tuning (6) SQL Server Processes (SPIDs) (7) SQL Server Replication (2) SQL Server Scripts (13) SQL Server Security (4) SQL Server Storage (10) Windows Admin (21) Windows Authentication (2) Windows Automation (1) Windows Events (2) Windows Firewall (4) Windows Subsystem for Linux (WSL) (18)