SQL Server Backup and Restore Reports

SQL Server Backup and Restore Reports

sql server backup and restore reports

This post can help you to quickly find SQL Server Backup and Restore Reports from a given SQL Server instance. For a typical DBA a most common task is to check the latest backup and restore history for a database. We usually required this information while we are working on a RCA (Root Cause Analysis) to resolve a specific issue, to check the backup frequency or to check the last backup status details. Here are the quick T-SQL scripts which can help us in checking SQL Server Backup and Restore Reports.

SQL Server Backup History Report:

It captures the backup history for a given database (or all databases) based on the parameter @DBName. If @DBName is NULL then it get all databases backup history, if @DBName is assigned with a database name then it get the backup history for the given database.

Continue reading

Posted in Uncategorized | Tagged , , , , , , , , | Leave a comment

SQL Server Blocking Report

SQL Server Blocking Report

SQL Server Blocking Report

This post can help a SQL DBA / Developer to quickly find the SQL Server Blocking Report using T-SQL scripts. There are various ways to get the SQL Server Blocking Report but here we are going to discuss only on T-SQL scripts which can be handy for any DBA or Developer to quickly get the details. Usually we need not worry about the process blocking when it occurs occasionally for a short time periods but we can consider it’s as a critical problem when the same issue repeating multiple times or blocking exists for a long time. Below are the different ways to identify and retrieve SQL Server Blocking Report:

  • System Stored Procedures
  • DMV
  • Activity Monitor
  • SSMS Reports
  • SQL Server Profiler

Here is the link for the detailed information.

Script to Get SQL Server Blocking Report:

Execute the below script, capture the details and analyze these details to identify the root cause then we can provide the resolution based on the root cause.

 

Script to Get SQL Server Blocking Report – Detailed:

Above script can get the maximum information. But sometimes we can see that the blocking might occur between two stored procedures. In that case the above script returns the entire stored procedure code instead of the exact query. Below script can help us in identifying the exact SQL Statement that is blocked / blocking from the stored procedure.

Here is the Script File: sql_server_blocking_report

Posted in SQL Development, SQL Scripts, SQL Server DBA | Tagged , , , , , , , , , , , , | Leave a comment

SQL Server Space Usage

SQL Server Space Usage

SQL Server Space Usage

Hello there, this post can explain various ways to know the SQL Server Space Usage information. Here are the different ways available:

  • XP_FIXEDDRIVES
  • SP_SPACEUSED
  • SP_HELPDB
  • DBCC SQLPERF (LOGSPACE)
  • sys.dm_os_volume_stats
  • Database and file wise Space usage
  • Used Space and Free Space For a given Database
  • Table wise space usage in a given database
  • Using SSMS Standard Reports
  • XP_CMDSHELL
  • PowerShell Script

Continue reading

Posted in SQL Scripts, SQL Server DBA | Tagged , , , , , , , , , , , , | Leave a comment