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.

PowerSehll : How to suppress the table heading and remove the blank space from a text file

There are a multiple ways to suppress and remove the first line from text file. The first scenario would be writing a suppressed heading to an output text file Or after writing, we can remove the line using trim function Or using the trick shared in PowerShell magazine.

Just give… Read more

0 comments, 127 reads

Posted in PowerSQL By Prashanth Jayaram on 2 May 2016

PowerShell – SQL Databases Backup Status Report of Multiple Servers

 

This post is a requirement from one of my blog reader.

There are plenty of scripts available to get the backup status of the databases across multiple server. The  requirement is to read a server name and application names from an input file. The server name is used for… Read more

0 comments, 130 reads

Posted in PowerSQL By Prashanth Jayaram on 27 April 2016

MongoDB : How to get Top, Bottom,Middle and Range of records

In MongoDB,the limit() method is used to limit the result set to be returned. You can also use this with various methods such as sort() and skip() for various combination of requirement.

The MS SQL equivalent is TOP function

>SELECT TOP 10 * FROM <TABLENAME>

Some examples are

Top ‘N’Record

Read more

0 comments, 961 reads

Posted in PowerSQL By Prashanth Jayaram on 26 April 2016

MongoDB – Insert,Update,Upsert and Delete Examples – CRUD

In MongoDB we have to use either insert() or save() method to add the document to a collection

Insert Single document

> db.employee.insert( 
    { 
    "employee_id":1101, 
    "name":"Prashanth", 
    "sal":90000, 
    "dob"new Date(1983,2,3,5

Read more

0 comments, 114 reads

Posted in PowerSQL By Prashanth Jayaram on 20 April 2016

MongoDB – How to Copy Database

Using copydb

Run the copydb under admin database with from,to, host parameters

>use admin
switched to db admin
> db.runCommand({ copydb: 1, fromdb: “test”, todb: “new_test”, fromhost: “localhost” })

Using copyDatabase method

The db.copyDatabase() method is used to copy a database.

>db.copyDatabase(“source_test”,”TargetTest”,”LocalHost”)

 


Read more

0 comments, 242 reads

Posted in PowerSQL By Prashanth Jayaram on 14 April 2016

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, 243 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,024 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, 91 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, 134 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, 92 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, 195 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, 115 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, 271 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,350 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, 690 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, 169 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, 175 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,246 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, 203 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, 213 reads

Posted in PowerSQL By Prashanth Jayaram on 14 January 2016

Older posts