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: Discuss Execute(SQL) At LinkedServer

Abstract

This article details the use of “Execute AT LinkedServer” clause. It’s best in some ways when you are trying to run dynamic complex queries  across heterogeneous data source. There are many instances that OpenQuery/OpenRowSet and four-part qualifier calling might not work in the complex SQL design.  The limitation of…

Read more

0 comments, 400 reads

Posted in PowerSQL By Prashanth Jayaram on 9 November 2016

SQL : How to Find Unused Indexes details

The Dynamic Management View (DMV) named sys.dm_db_index_usage_stats that track  index usage details of the database. This DMV gives an information about an index which is being updated but not used in any seeks, scan or lookup operations.

The below query list

  1. Table name
  2. Index name
  3. No of Rows
  4. Size of…

Read more

0 comments, 210 reads

Posted in PowerSQL By Prashanth Jayaram on 1 November 2016

PowerShell : CSV – Disk Space Report – Excel

Abstract

Monitoring disk space utilization of server(s) is a critical and important job for any administrator. Keeping things organized might improve application availability and server availability. Being a database administrator for 10 years, I have faced and handled/managed lot of issues with disk space. This article takes us through the…

Read more

1 comments, 134 reads

Posted in PowerSQL By Prashanth Jayaram on 24 October 2016

PowerShell: Extending Modules – System Parameter measurement

The Microsoft Technet Guru Award  (August 2016) winning article  – Silver

PowerShell: Extending Modules – System Parameter measurement

PowerShell: Extending Modules – System Parameter measurement

Abstract

The objective of this post to illustrate the various methods of creating PoSH Modules. To explain different methods, have considered PoSH GUI functions which…

Read more

2 comments, 197 reads

Posted in PowerSQL By Prashanth Jayaram on 3 October 2016

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, 359 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, 1,091 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, 201 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, 211 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, 660 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, 226 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, 305 reads

Posted in PowerSQL By Prashanth Jayaram on 19 July 2016

SQL vs NoSQL

Introduction

Despite the traditional relational DB world, there’s also a trend for NoSQL. The NoSQL databases that enable storing unstructured and heterogeneous data. The scalability features have risen it’s popularity to a greater extent in the current market. Horizontal scaling leads the organization to not to worry about the underlying…

Read more

0 comments, 573 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, 1,159 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, 246 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, 174 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, 346 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, 226 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,912 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, 360 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

1 comments, 227 reads

Posted in PowerSQL By Prashanth Jayaram on 24 May 2016

Older posts