Category Archives: SQL

SQL Server – Internals of sp_spaceused

Posted on February 6, 2018 by Prashanth Jayaram

This article is an effort to dissect the output of the sp_spaceused stored procedure. Introduction Understanding the database usage internals and the growth trends play a vital role in the defining the right sizing of the database. sp_spaceused is probably an administrator’s … Continue reading →

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

Different methods to write PowerShell output to a SQL table

Posted on December 26, 2017 by Prashanth Jayaram

PowerShell has become the ultimate choice for many database administrators because of its efficient way of handling and managing automation in a simple, quick way. It’s built on .NET Framework and uses Object Models such as COM, ADSI, ADO, and … Continue reading →

Posted in PowerShell, SQL, T-SQL | Tagged , , , , , | 2 Comments

Monitoring SQL Server database status changes using T-SQL and PowerShell scripts

Posted on October 17, 2017 by Prashanth Jayaram

Monitoring a SQL Server database is a critical component of database administration. Ninety percent of the organizations expect the number of databases to increase over the next twelve months. An increase in data volumes can have negative effects on the … Continue reading →

Posted in SQL, T-SQL, XML | Tagged , , , | Leave a comment

Recover Deleted Data from SQL Table Using Transaction Log and LSNs

Posted on September 21, 2017 by Andrew Jackson

Introduction At times a user may perform UPDATE operation or DELETE operation in SQL server database without applying the WHERE condition. This is a very common reason for encountering loss of data from SQL Server Tables. As the SQL Server … Continue reading →

Posted in SQL, SQL 2016, SQL Server 2017 | Tagged , , , | 1 Comment

SQL Server DB Migration – Cloning a database to another collation

Posted on September 19, 2017 by Prashanth Jayaram

Database migration is a vital task in any environment, complex or otherwise. Seamless migrations are the goal but the efforts required to ensure it are tremendous. A global SQL Server Collation Sequence update is the answer to the puzzle, but … Continue reading →

Posted in SQL, T-SQL | Tagged , , | Leave a comment

How to Capture Database(s) usage stats using sp_spaceused and PowerShell

Posted on September 11, 2017 by Prashanth Jayaram

The objective of this article is to look at the possible ways of capturing database usage trends using the most commonly available tools and techniques. The integration of SQL, the use of sp_spaceused, and the flexibility offered by PowerShell have … Continue reading →

Posted in JSON, SQL, SQL 2012, SQL 2016, T-SQL | Tagged , , , , | Leave a comment

Multi-server Script to Find Orphaned Data Files using PowerShell

Posted on August 23, 2017 by Prashanth Jayaram

In this article, we take the sledgehammer approach in searching for MDFs and then comparing them against files which are available on the listed drive. The idea behind this is to generate a report of detached database files across multiple … Continue reading →

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

Planning a SQL Server Backup and Restore strategy in a multi-server environment using PowerShell and T-SQL

Posted on August 15, 2017 by Prashanth Jayaram

This post demonstrates one of the ways to gather an inventory of database backup information. The output of the script includes various columns that show the internals of a database, backup size, and gives the latest completion statuses and the … Continue reading →

Posted in SQL, SQL Server Backup | Tagged , , , , | Leave a comment

Migrate an SQL database from Windows to Linux using backup-and-restore and SMB Protocol

Posted on May 16, 2017 by Prashanth Jayaram

In an attempt decouple systems, we may modularize them. In most cases, we would need to move databases from one server to another—sometimes, even move them from Windows to Linux. In the case of moving a database to Linux, SQL Server’s … Continue reading →

Posted in SQL, SQL 2016, SQL Server vNext | Tagged , , , | Leave a comment

Understanding Database Backup Encryption in SQL Server

Posted on May 3, 2017 by Prashanth Jayaram

Terabytes of data, millions of rows; the entire business depends on this — a database administrator’s responsibility is as massive as it sounds. Apart from keeping the data secure, a database administrator also has to keep the system up and … Continue reading →

Posted in SQL, SQL 2012, SQL 2016 | Tagged , , , , , , , | Leave a comment