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

SQL Server Health Check

SQL Server Health Check

sql-server-health-check

Hello there, if you are a SQL DBA the most common task that you perform is “SQL Server Health Check”. You may already have an automated report but this is for doing a quick SQL Server Health Check using native T-SQL. This script helps you in generating HTML report using T-SQL. There are situations where we need to quickly perform SQL Server Health Check:

  • You got a request to check the health of a SQL Server instance
  • When there is a complaint raised against the instance performance
  • When SQL Server restarted
  • Etc.

This SQL Server Health Check Reports:

SQL Server Instance Details: SQL Version, Edition, Service Pack, Processor Type etc.

Error Log: Errors logged into SQL Server error log from Last 4 days

Instance Last Recycle Information: Last recycle time and total uptime information

Tempdb usage: File location and space available information.

CPU Usage: CPU usage information

Memory Usage: Memory usage details

Performance Counters Data: Major performance counter values

Missing Backup Report: Database with no full backup from last 48 hours or without any backup

Connection Information: Host wise connection information

Log Space Usage Report: Log files usage information for all databases. It highlights the value with red color when the percentage use > 80.

Job Status Report: Reports current running jobs information

Blocking Report: Reports blocking information if any

Long running Transactions: Retrieves the long running transactions if any

Most important Note:

There is only one place (Memory Usage Capturing Buffer Pool information) where you need to comment / un-comment out based on the SQL Server version on which this script is running.

Executing:

Continue reading

Posted in Uncategorized | Tagged , , , , , , , , , , , | 6 Comments

Preparing for SQL DBA Interview

Preparing for SQL DBA Interview

Preparing for SQL DBA Interview

This article list out few tips on “Preparing for SQL DBA Interview” If you are reading this article which means you must be a Database Administrator/professional or getting ready for entering into DBA world. In today’s corporate world attending or taking a technical interview is mandatory whether it’s for a new role in current organization, new customer/client or for a new organization. Preparing for SQL DBA Interview is always a challenge. We have randomly chosen SQL Server database professionals from three different experience levels and had a deep discussion on “Preparing for SQL DBA Interview”.

  • Fresher/Junior – Should know the basics of process and technology
  • Mid-Level – Should be able to expertise in one or two areas and know the process
  • Senior Level – Should be expertise in technology and able to drive the process

There are few points everyone should follow to get succeed in an interview. Below are the key points which can be helpful in “Preparing for SQL DBA Interview”

  • Profile

  • Preparation

  • Communication

  • Interview Process

  • Preparing a Topic

Profile

This is the first piece of information that tells about you so please be careful in preparing your profile:

  • Don’t add the generic skill-set: Include only the topics that you are experienced or learned.
  • Add your professional experience, key skills, education details, your achievements, certifications, training and projects.
  • Number of pages should be restricted to 3 or 4.
  • Maintain a profile on professional network like LinkedIn and add a link to your profile (I have seen a lot of opportunities are hitting through professional networks)
  • Remember you should know / learn / prepare / experience each and everything you mentioned in your profile as the interview questions are always depends on summary points that you showcase in your profile.

Continue reading

Posted in Interview Q&A, Miscellaneous, SQL Server DBA | Tagged , , , , , | 4 Comments

Update Without Where Clause

Update Without Where Clause

Update Without Where Clause

Running Update Without Where Clause might causes a data damage when it is with the critical table that creates a lot of problems and escalations. Recently while working with one of the customer we had a situation where a DBA executed the release script on production and the script contains an update statement without where clause. He immediately realized that he executed an update statement without where clause and he reported us explaining the situation. There were few things we found:

  • The change request implemented on a premium database
  • Update statement provided from Pre-Prod team which is not having any control on that:
    No Where clause
    No Transaction control – Commit / Rollback
    No Validation on update – Validating and Reporting
  • As per the business requirement single record should be updated but in real it updates all records in that table.
  • Usually when a DBA implements a CR on prod server a backup or snapshot has to be taken prior to implementing the change. Since it was a small database (6 GB) he did take a full backup before implementing the CR.

Solution we have provided:

Continue reading

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