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: Monitoring Disk Space with FSUTIL command line utility

Table of Contents

Introduction

This article talks about one of SQL method to fetch the disk usage details and use of T-SQL in conjunction with invoking FSUTIL utility using SQL Windows Shell interface xp_cmdshell. The FSUTIL…

Read more

0 comments, 87 reads

Posted in PowerSQL By Prashanth Jayaram on 27 September 2016

SQL Server Operations: Monitoring Disk Space with WMI and OLE Automation Objects

Abstract

This article talks about various SQL methods to fetch the disk usage details and use of T-SQL in conjunction with invoking OLE automation procedures and WMI  class libraries using SQL Windows Shell interface.
The step by step  details about “OLE automation and WMI Query” approach to capture disk space…

Read more

0 comments, 969 reads

Posted in PowerSQL By Prashanth Jayaram on 12 September 2016

Understanding the Different States of SQL Database

Overview

There are multiple numbers states of a SQL Database. These states determine the current status or mode of database in SQL server.

At once, the database is only in one state, which may be any one of the following:

  • Online
  • Offline
  • Restoring
  • Recovering
  • Recovery Pending
  • Emergency
  • Suspect

In this… Read more

0 comments, 153 reads

Posted in PowerSQL By Prashanth Jayaram on 27 August 2016

Disk Space GUI Tool – Multi Server(s) – Grid and Email Output

Monitoring server resources is critical and important job of any Administrator. Keeping things organized might improve application availability and server availability. Being a database administrator for 1o years have faced and handled/managed lot of issues with disk space. There are many times we end up with I/O bottleneck and disk… Read more

0 comments, 170 reads

Posted in PowerSQL By Prashanth Jayaram on 8 August 2016

Minimizing SQL Server DBCC CHECKDB Performance Impacts

Overview

SQL Server is the most popular relational database management system, which is used by many organizations to maintain their data. Every user expects that data should be stored in a consistent and integrated form. SQL Server is equipped with an in-built DBCC command, which allow users to check the… Read more

1 comments, 550 reads

Posted in PowerSQL By Prashanth Jayaram on 3 August 2016

PowerSQL – Get SQL TraceFlag SQL Service and Config Information

Get SQL Server configuration, service info, start type along with enabled Trace Flags of local or remote servers.

This script outputs sql server configuration, service, start type, Service Account along with startup trace flags. The script will work on SQL Server 2005 and up.

function Get-SQLAndGlobalTraceFlagsDetails { 
<# 
.SYNOPSIS 
Returns…

Read more

0 comments, 178 reads

Posted in PowerSQL By Prashanth Jayaram on 26 July 2016

SQL – MultiServer SQL Inventory – PowerShell

Inventory is a vital information,whether you’re a consultant or an in-house DBA, you need to have a reliable inventory of the servers you manage.This inventory can take any number of forms but, ideally, will allow some aggregation of information. When server hardware or software changes are taking place, it’s easy… Read more

0 comments, 248 reads

Posted in PowerSQL By Prashanth Jayaram on 19 July 2016

SQL and NoSQL

Despite the traditional relational DB world, a new trend is emerging which is called NoSQL. It’s really hard to define NoSQL. You will get a lot of information, opinions on what NoSQL is all about. This is just my understanding and research on NoSQL.

It’s all about

  • Non-relational,
  • Open source,

Read more

0 comments, 501 reads

Posted in PowerSQL By Prashanth Jayaram on 15 July 2016

Tips to Fix Database Consistency Errors – Reported By DBCC CHECKDB

Overview

While working on SQL Server, sometimes users get stuck in annoying situations when database become inaccessible. After executing DBCC CHECKDB command to check the problem, user gets database consistency errors.

Causes Behind DBCC CHECKDB Consistency Errors

The reason behind the database consistency error can be varied from file system… Read more

0 comments, 961 reads

Posted in PowerSQL By Prashanth Jayaram on 11 July 2016

Why MongoDB?

MongoDB is Object-Oriented, simple, dynamic and scalable NoSQL database. It is based on the NoSQL document store model, in which data objects are stored as separate documents inside a collection instead of storing the data into columns and rows of a traditional relational database. The motivation of the MongoDB language… Read more

0 comments, 204 reads

Posted in PowerSQL By Prashanth Jayaram on 6 July 2016

Securing MongoDB – User Administration

The db.createUser(user, writeConcern) method used to create users.We need to provide the username, password and roles

The definition of createUser as follows

{ 
user: "<name>",
pwd: "password>",
customData: { <User Tag> },
roles: [
    { role:

Read more

0 comments, 140 reads

Posted in PowerSQL By Prashanth Jayaram on 1 July 2016

PowerShell – Install PowerShell

Recently got a question regarding PowerShell installation hence this post took an existence.

How to Install PowerShell various versions

http://technet.microsoft.com/en-us/library/hh847837.aspx

http://social.technet.microsoft.com/wiki/contents/articles/21016.how-to-install-windows-powershell-4-0.aspx

Can we have more than one version of Powershell?

Yes, You can have multiple versions PowerShell.

How to switch between Powershell version?

ps:\>Powershell -version 4

ps:\>Powershell -version 3

ps:\>Powershell… Read more

0 comments, 300 reads

Posted in PowerSQL By Prashanth Jayaram on 6 June 2016

PowerShell : Script Method Error -Method invocation failed because [Microsoft.SqlServer.Management.Smo.ServerRole] does not contain a method named ‘Script’.

I was working on generating scripts for logins, users and server roles and I could able to to generate scripts for users and logins but where as for server roles, the script() method is not working.

The script is throwing following error

Method invocation failed because [Microsoft.SqlServer.Management.Smo.ServerRole] does not contain… Read more

0 comments, 191 reads

Posted in PowerSQL By Prashanth Jayaram on 1 June 2016

Restore a SQL Server Database with Different Methods

Database backups are generated as part of the business continuity planning procedure. Disasters with server or its database are a common scenario that takes place every now and then lead to the loss of an enormous amount of data. In order to sustain the same, administrative ensure backup plans as… Read more

0 comments, 2,785 reads

Posted in PowerSQL By Prashanth Jayaram on 28 May 2016

The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

“The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)” error may occurs when executing PowerShell scripts using WMI query.

There can be a few reasons for this error:

  1. The remote computer is blocked by the firewall.
    Solution: Open the Group Policy Object Editor snap-in (gpedit.msc) to edit the Group Policy…

Read more

0 comments, 293 reads

Posted in PowerSQL By Prashanth Jayaram on 25 May 2016

XML-> JSON document Example

Convert the XML document to JSON document example

<person>
  <name>John</name>
  <age>25</age>
  <address>
    <city>New York</city>
    <postalCode>10021</postalCode>
  </address>
  <phones>
    <phone type="home">212-555-1234</phone>
    <phone type="mobile">646-555-1234</phone>
  </phones>
</person>

 

{
“name” : “John”,
“age” : 25,
“address” : { “city” : “New York”, “postalCode” : “10021” },
“phones” :
[
{“phone”:”212-555-1234″, “type” : “home”},
{“phone”:”646-555-1234″,… Read more

0 comments, 209 reads

Posted in PowerSQL By Prashanth Jayaram on 24 May 2016

PowerShell : Scripting Logins, Role Assignments and Server Permissions Using PowerShell

This post is a continuation of Multiple DB refresh automation using PowerShell post . The OP requested me to automate the login transfer process as well. The script should generate an outfile and execute the file on the target server.

<#
.ProblemStatement

Copying SQL server logins to file is little… Read more

3 comments, 249 reads

Posted in PowerSQL By Prashanth Jayaram on 23 May 2016

MongoDB : Point in Time Restore/Recovery of MongoDB

This post demonstrates the methods of PIT recovery of the database. The step by step details of the recovery process is explained below

The requirement for PIT restore/recovery is to setup a single node replica so that the oplogs can be used to “replay transactions” for point in time recovery.… Read more

0 comments, 305 reads

Posted in PowerSQL By Prashanth Jayaram on 17 May 2016

PowerShell – SQL Database Refresh -Restore – Multiple Databases

.SYNOPSIS

The purpose of the script is to restore database/s from a database backup. The requirement is to refresh ‘n’ databases.

.DESCRIPTION

The advantage is that the flexibility of passing restoreDbList to restore specific group of databases.

The Restore databases has three mandatory paramters

param ([Parameter(Mandatory=$true,Position=0)][String]$SourceServer – Source where you… Read more

7 comments, 904 reads

Posted in PowerSQL By Prashanth Jayaram on 12 May 2016

PowerShell – Backup Specific group of SQL Databases

<#
.SYNOPSIS

The purpose of the script is to initiate backup only for desired group of databases. The requirement is to refresh databases.

.DESCRIPTION

The advantage is that the flexibility of passing a list of specific group of databases for backup.

The backup databases has three mandatory parameters

$SQLServer –… Read more

2 comments, 249 reads

Posted in PowerSQL By Prashanth Jayaram on 11 May 2016

Older posts