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.

SQL Server : Could not load file or assembly ‘Microsoft.SqlServer.BatchParser, Version=10.0.0.0

I have recently experienced an issue with a third-party  backup application (Commvault) that was trying to backup the databases from the SQL 2014 . It was throwing a below error

“Description: Error encountered during backup. Error: [ Could not load file or assembly ‘Microsoft.SqlServer.BatchParser, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of… Read more

3 comments, 304 reads

Posted in PowerSQL By Prashanth Jayaram on 9 May 2016

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, 240 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

4 comments, 296 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, 1,093 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, 277 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, 323 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, 307 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,179 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, 157 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, 210 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, 145 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, 261 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, 176 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, 332 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,514 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, 814 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, 253 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, 240 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,381 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, 311 reads

Posted in PowerSQL By Prashanth Jayaram on 24 February 2016

Newer posts

Older posts