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 – List All the databases

The function getDBs() function returns all the database list

db.getMongo().getDBs()

The runCommand is a special type of query called a database command and its is used database administration. The listDatabases parameter provides a list of all existing databases.

You can see all commands by running the db.listCommands() command.

db.runCommand({ “listDatabases”:… Read more

0 comments, 285 reads

Posted in PowerSQL By Prashanth Jayaram on 13 April 2016

SQL DB Details Of Multiple Servers Using PowerShell

 

One of my blog reader requested me to get the DB details(Server,DBName,Size,SpaceAvailable,LogSize,PercentUsed Log Space,compatiblilty mode, recovery model etc:-) using PowerShell hence this post is written.

The power of PowerShell is to capture an output of DBCC commands and store it an variable for later processing of the desired result Read more

4 comments, 1,137 reads

Posted in PowerSQL By Prashanth Jayaram on 11 April 2016

MongoDB -Get Total datasize of all DB’s

var sum = 0; db.getMongo().getDBs()[“databases”].forEach(function(x) { sum += db.getMongo().getDB(x.name).stats().dataSize }); print(sum );

OR
db = db.getSiblingDB(“admin”);
dbs = db.runCommand({ “listDatabases”: 1 }).databases;
var sum1=0;dbs.forEach(function(database) { sum1+=db.getMongo().getDB(database.name).stats().dataSize }); print(sum1);


Read more

0 comments, 136 reads

Posted in PowerSQL By Prashanth Jayaram on 11 April 2016

MongoDB – Drop database

The dropDatabase command drops the current database, deleting the associated data files.

Different methods to drop database

Method 1:

1. select the database which you want to delete
>use < database name >
2. Then issue the below command
>db.dropDatabase()

Method 2:

1. Define the name of the database
>… Read more

0 comments, 177 reads

Posted in PowerSQL By Prashanth Jayaram on 8 April 2016

MongoDB -Get Size, RowCount, CollectionSize

There are two different ways of getting the desired result

The first method is iterating through each collection using for loop and displaying the result

cols = db.getCollectionNames();
for (index = 0; index < cols.length; index++) {
var coll = db.getCollection(cols[index]);
var stats = coll.stats();
print(stats.ns, stats.count, stats.size, stats.totalIndexSize); } Read more

0 comments, 127 reads

Posted in PowerSQL By Prashanth Jayaram on 7 April 2016

MongoDB – Local and Remote Connections in Mongo Shell

To Install and Getting started with MongoDB refer the below links

How to access Mongo instance

  • Open a terminal session or command window.
  • For a local connection to mongod, at the operating system prompt, enter the following command to…

Read more

0 comments, 241 reads

Posted in PowerSQL By Prashanth Jayaram on 6 April 2016

MongoDB -Calculating Collection Size

Calculating Collection Size

We can get the size of a collections by using the shell’s functions

  • db.collection.dataSize() : Returns data size in bytes for the collection.
  • db.collection.storageSize() : Returns allocation size in bytes, including unused space.
  • db.collection.totalSize() : Returns the data size plus the index size in bytes.

Calculating Size…

Read more

0 comments, 153 reads

Posted in PowerSQL By Prashanth Jayaram on 5 April 2016

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, 308 reads

Posted in PowerSQL By Prashanth Jayaram on 4 April 2016

Simple Methods to Recover master.mdf File Password

Overview

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,443 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]
FROM
(
 SELECT…

Read more

4 comments, 780 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, 216 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.

http://www.sqlservercentral.com/blogs/sqlsandwiches/2012/01/29/find-failed-sql-jobs-with-powershell/

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

10 comments, 209 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,330 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)
DECLARE @DBuser_table TABLE
(
DBName VARCHAR(200),
UserName VARCHAR(250),
LoginType VARCHAR(500),
AssociatedDatabaseRole VARCHAR(200)
)
SET @DBuser_sql='
SELECT "[?]" AS DBName,a.name AS Name,
a.type_desc AS LoginType,…

Read more

1 comments, 270 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  
{  
param(  
[String

Read more

0 comments, 256 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, 
SamAccountName,servername,database,
RecipientTypeDetails,PrimarySmtpAddress,
HiddenFromAddressListsEnabled,
@{label="ItemCount";expression={(Get-MailboxStatistics $_).ItemCount}},
@{

Read more

0 comments, 211 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”

db.getSiblingDB('PP').getCollectionNames()

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,183 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, 223 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 http://www.mongodb.org/downloads and select the Mongo package based on an OS version

Have downloaded the… Read more

2 comments, 1,095 reads

Posted in PowerSQL By Prashanth Jayaram on 5 January 2016

2015 in review

The WordPress.com 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, 173 reads

Posted in PowerSQL By Prashanth Jayaram on 4 January 2016

Newer posts

Older posts