Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

PowerSQL By Prashanth Jayaram

Technology enthusiast with 7+ years of experience in Database Technology. I am Microsoft Certified Professional with certificates of OCA, MCP, MCTS, MCITP developer, MCITP administration and backed with a degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication, PowerShell and Performance Tuning. Hobbies are Drawing, playing soccer and listening to Melodies songs.

MongoDB – Calculating Document Size

  • We can get the size of a document is by using the shell’s Object.bsonsize() function.
  • We pass the object_id of the document to get the size

For example in the foo collection, we can find the size of the document using any document id.

Read more

0 comments, 288 reads

Posted in PowerSQL By Prashanth Jayaram on 4 April 2016

Simple Methods to Recover master.mdf File Password


MS SQL Server is the relational database management system used to store information in form of tables, views, indexes, triggers, constraints and stored procedures. Master Database File (MDF) is the main database file with the file extension .mdf used in SQL Server. These MDF files are protected with password… Read more

1 comments, 1,383 reads

Posted in PowerSQL By Prashanth Jayaram on 15 March 2016

Different ways to find Data, log and DB Size

Different ways to get the size of the data , log files and Databases

  • Using Counters
  • Querying sysaltfiles & sys.databases
  • Querying sys.master_files


SELECT instance_name AS DatabaseName,
 [Data File(s) Size (KB)]/1024.00 [DataInMB],
 [LOG File(s) Size (KB)]/1024.00 [LogInMB],
 [Data File(s) Size (KB)]/1024.00 + [LOG File(s) Size (KB)]/1024.00 [TotalSize]

Read more

4 comments, 720 reads

Posted in PowerSQL By Prashanth Jayaram on 10 March 2016

Get the Total Item Size for an Exchange database

Hey there!

This one is for those Exchange admins who think it is a sin to use hands for something that a computer can do—those Exchange admins love their PowerShell console more than the Exchange Management Console.

There are some instances where we need to get the total item size… Read more

0 comments, 186 reads

Posted in PowerSQL By Prashanth Jayaram on 6 March 2016

Find failed SQL Job in last 24 Hours using Powershell

This post is based on the request to get failed SQL jobs in last 24 hours and output should be displayed in a HTML format.

The verification of SQL job steps code is taken from the below blog.

The only difference is that the output, it’s formatted HTML output. Read more

10 comments, 192 reads

Posted in PowerSQL By Prashanth Jayaram on 2 March 2016

PowerShell – Backup Individual database and delete all it’s respective files by keeping a recent file

Problem Statement

The requirement is to backup individual database and after successful backup the script should retain the most recent file and delete the rest from a directory for that specific database.

The below Powershell script is used to backup a specific database on a given directory

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null…

Read more

4 comments, 1,276 reads

Posted in PowerSQL By Prashanth Jayaram on 29 February 2016

SQL – List Server and DB Permsission

Database Level Permission

DECLARE @DBuser_sql VARCHAR(4000)
DBName VARCHAR(200),
UserName VARCHAR(250),
LoginType VARCHAR(500),
AssociatedDatabaseRole VARCHAR(200)
SET @DBuser_sql='
SELECT "[?]" AS DBName, AS Name,
a.type_desc AS LoginType,…

Read more

1 comments, 225 reads

Posted in PowerSQL By Prashanth Jayaram on 24 February 2016

PowerShell- Monitoring Multiple Services On Multiple Servers Using WMI Class -Win32_Service

The requirement is to check only those services where startup mode set to Auto and services that stopped. In my previous post have used Get-Service cmdlet which do not bind any such information hence I’m querying Win32_Service. This class has StartMode and State attributes.

Function Get-ServiceStatusReport  

Read more

0 comments, 230 reads

Posted in PowerSQL By Prashanth Jayaram on 14 January 2016

Mailbox Statistics report with Email addresses

Script to collect and export the mailbox properties from Get-Mailbox and Get-MailboxStatistics cmdlets to a CSV file

This script can be extended based on the required attributes

Get-Mailbox -ResultSize Unlimited  | 
Select-Object DisplayName, 
@{label="ItemCount";expression={(Get-MailboxStatistics $_).ItemCount}},

Read more

0 comments, 194 reads

Posted in PowerSQL By Prashanth Jayaram on 7 January 2016

MongoDB -Access different databases and Collections

You can use db.getSiblingDB() method to access another database without switching the database.

To List Collections of PP database and query collection named “restaurants”


The PP Database has three collections

  1. first
  2. restaurants
  3. second

Accessing PP database temporarily from Test database

To access… Read more

0 comments, 1,145 reads

Posted in PowerSQL By Prashanth Jayaram on 7 January 2016

XenApp/XenDesktop 7.6 FP3 VDA Deployment Issue

During the upgrade to XenApp 7.6 FP3 VDA encountered the following message.

I was wondered on seeing this popup to restart the server during the upgrade. After restarting the server for couple of times I was encountered with the same popup window. Well , after further investigation found there is… Read more

0 comments, 205 reads

Posted in PowerSQL By Prashanth Jayaram on 6 January 2016

Install MongoDB 3.2 on Windows

We can install MongoDB as a windows service. The steps are given below

Prerequisite –

Install Hotfix kb2731284 on the MongoDB Server on Windows Box

STEP1 – Download MongoDB

Download the latest release of MongoDB from and select the Mongo package based on an OS version

Have downloaded the… Read more

2 comments, 1,054 reads

Posted in PowerSQL By Prashanth Jayaram on 5 January 2016

2015 in review

The stats helper monkeys prepared a 2015 annual report for this blog.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 48,000 times in 2015. If it were a concert at Sydney Opera House, it would take about 18…

Read more

0 comments, 167 reads

Posted in PowerSQL By Prashanth Jayaram on 4 January 2016

Learn How to Insert Data From Stored Procedure Into Table?

Stored Procedures are considered most efficient way for calling and retrieving data and hence users always look forward to encapsulate logic in it so that it can be called when needed. While encapsulating data in Stored Procedure, it is also important to know how to retrieve data they return. Now,… Read more

0 comments, 276 reads

Posted in PowerSQL By Prashanth Jayaram on 26 December 2015

SQL to generate Asset Information – Configuration Manager SCCM 2012

SELECT   DISTINCT  s.Netbios_Name0 AS ComputerName,  
            s.Operating_System_Name_and0 AS OSName,  
            pr.Name0 AS ProcessorTypeSpeed,  
            pr.Manufacturer0 Manufacturer, 
            pr.NumberOfCores0 Cores, 
            pr.NumberOfLogicalProcessors0 LgicalProcessorCount, 
            case when pr.DataWidth0=64 then '64 bit' else '32 bit' end DataWidth, 
            m.TotalPhysicalMemory0/1024.00 AS MemoryMB,  
            GS1.TotalVirtualMemorySize0 VirtualMemory, 
            GS1.TotalVisibleMemorySize0 VisibleMemory, 
            T1.COL AS TotalDriveSize, 
            DATEDIFF(Day,GS1.LastBootUpTime0, GETDATE()) AS [Days since last boot]           

Read more

0 comments, 246 reads

Posted in PowerSQL By Prashanth Jayaram on 11 December 2015

Use FILEPROPERTY to find free space in all the database

The use of SpaceUsed property of the FILEPROPERTY function gives how much space is used also we can derive lot of other attributes of it such as free space and percentage of free space.

For other versions of SQL you can refer the below SQL.

DECLARE @command VARCHAR(5000)…

Read more

0 comments, 293 reads

Posted in PowerSQL By Prashanth Jayaram on 26 August 2015

T-SQL – Find Number of Rows of Database(s) tables Using Undocumented Procedure – sp_MSforeachdb and sp_MSforeachtable

Added few more examples

Prashanth Jayaram

On Technet forum, Op was looking for a way to get table cardinality for all databases in an instance.  There are many ways to get the result. This post gives you an idea and SQL in which undocumented SP’s being called  “sp_MSforeachdb and sp_MSforeachtable”.

Read more

0 comments, 470 reads

Posted in PowerSQL By Prashanth Jayaram on 17 March 2015

How to Replace Multiple Strings in a File using PowerShell

Replace the Data Source and Initial Catalog values of WebConfig.XML

Content of XML file

<Configuration ConfiguredType=”Property” Path=”\Package.Connections[ConnStaging].Properties[ConnectionString]” ValueType=”String”>
<ConfiguredValue>Data Source=localhost;Initial Catalog=Stage;Integrated Security=SSPI; Connection Timeout = 10</ConfiguredValue>


Read more

0 comments, 7,942 reads

Posted in PowerSQL By Prashanth Jayaram on 13 March 2015

T-SQL – How to get the Financial Quarter details of a date field

declare @table table
[Paid Date] date

insert into @table

;WITH Quarters AS (
   SELECT Q = 'Q1', MonthBegin = 1, MonthEnd = 3 UNION
   SELECT Q = 'Q2', MonthBegin = 4, MonthEnd = 6 UNION
   SELECT Q = 'Q3', MonthBegin = 7, MonthEnd = 9 UNION

Read more

4 comments, 6,726 reads

Posted in PowerSQL By Prashanth Jayaram on 11 March 2015

T-SQL :- How to Search String in all Stored Procedures across All User Defined Databases

There are many different ways to accomplish this tasks.

Download Link

The below examples uses undocumented sp’s to loop through all the user defined database to search the SP’s using given string

CREATE TABLE  #ProcSearch  (databaseName VARCHAR(100),ROUTINE_CATALOG varchar(50),ProcName VARCHAR(128),RoutineType varchar(100),CreateDate date)

DECLARE @command varchar(1000) 
SELECT @command = 'IF ''?''…

Read more

0 comments, 6,544 reads

Posted in PowerSQL By Prashanth Jayaram on 24 February 2015

Newer posts

Older posts