DB Technology Enthusiast

@prashantjayaram

I’m a Database technologist having 7+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell. Very much interested in sports and drawing.

265 posts

Posts

Administering Relational Databases on Microsoft Azure: DP 300 study guide

I am excited to share an important milestone in my life. My third book, “Administering Relational Databases on Microsoft SQL“, is released. Co-authored with Ahmad Yaseen and Rajendra Gupta, who are known to be the databases’ best and bright minds in the database world. I am fortunate to work with Ahmad and Raj. It is never easy to craft a near perfection content, but we tried to bring in the best…

View On WordPress

SQL Power Doc to Discover, Diagnose and Document SQL Server

This article will provide an overview of SQL Power Doc, a powerful script to document SQL Server, including pre-requisites, purpose, installation, processing and output. Automation seems to be an increasing need in IT industry. At present, PowerShell leads the game in the Windows-based environments, since it is capable of handling highly complex workflows. PowerShell is the go-to choice when it…

View On WordPress

Today, I'm making my 100th post on SQLShack

Today, I’m making my 100th post on SQLShack

The journey with the SQLShack family has been a one from the beginning. It is certainly not easy to join an excellent group of elite authors. When I joined this family, I looked at it as a place to showcase my work online, but I never thought it was going to be an interesting one and have such an impact on my career. Never did I imagine that I would write a hundred articles. This is very much a…

View On WordPress

Shrinking Tempdb in SQL Server won't work?

Shrinking Tempdb in SQL Server won’t work?

Problem statement

When I was working DML operation on the database, Tempdb has grown 80 GB. After the completion, It is noted that the tempdb has grown significantly larger and I didn’t have much space on this Server. I tried shrinking the mdf file of tempdb. It executes successfully but no space is released to OS. Is restarting the SQL Server the only way to release the space. Can you please…

View On WordPress

#DBCC FreeProccache #ShrinkFile #sys.dm_db_session_space_usage #sys.dm_exec_requests #tempdb

Automation to list all TCP static and dynamic ports of SQL Server instances using PowerShell

I was asked by the reader on how to get all the port information of SQL Servers instances. As you all knew that PowerShell is an automation framework for any of such requirement.

In this post, I’ll show to how to gather the information without logging on to each server.

Prerequisites

PowerShell 3.0 or above

WMI Service is enabled on all the target machine

The Server List is fed to the…

View On WordPress

#dynamic port #static port #tcp ports

Select-AzureRmProfile : The term 'Select-AzureRmProfile' is not recognized as the name of a cmdlet, function, script file, or operable program

Select-AzureRmProfile : The term ‘Select-AzureRmProfile’ is not recognized as the name of a cmdlet, function, script file, or operable program

I got a chance to work with PowerShell automation script to automate the login process. In the script, the profile is loaded using the Select-AzureRmProfile. If you try to log in using Select-AzureRmProfile you most likely encounter the following error message.

Error Message:

Select-AzureRmProfile : The term ‘Select-AzureRmProfile’ is not recognized as the name of a cmdlet, function, script file,…

View On WordPress

#Import-AzureRmContext #Save-AzureRmContext

Top 50 PowerShell bloggers of 2018

My work is recognized and my blog is listed under Top 30 elite list.

I am pleased, honored and humbled to accept this award and to join other great recipients who I have long admired and respected. A very special thanks to the SQLShackfamily for their effort in identifying and publishing the Top blogs of 2018. And, of course, thank you to my family, readers, friends, supporters, and mentors…

View On WordPress

#Top 50 PowerShell Bloggers

How to automatically Discover SQL Server instances

DBAs have plenty of day-to-day tasks to do as part of the administration, but one overarching task is managing the entire SQL Server environment. To do this, the full SQL Server estate must be discovered and inventoried. To accomplish that all SQL Server instances, that exist, must be found.

Inventory

A system inventory is always the go-to document for vital information for everyone whether…

View On WordPress

#.Net libraries #Inventory #SQL Inventory using PowerShell #SQL Server #SQL Server Inventory automation

SQL Server Essentials Series

SQL is the essential part of SQL Administration and SQL Development. A quick overview of basics can take you a long way in the database career and make you feel more comfortable to use powerful SQL features.

This article introduces the basic and important features of SQL in SQL Server. This guide demonstrates the working of SQL Server starts from creating tables, defining relationships, and…

View On WordPress

#DBAM #DBMS #De-Normalization #Normalization #RDBMS #SQL #SQL Coalesce #SQL delete #SQL Insert #SQL Join #SQL Like #SQL Order #SQL Union #SQL Update #What is SQL?

SQL Date functions in SQL Server

SQL date format functions like the DateDiff SQL function and DateAdd SQL Function are oft used by DBAs but many of us never took the time to fully understand these extremely useful features. For professionals just getting started with SQL Server, these functions are some of the first to become familiar with. So hopefully this article will have a little something for everyone across the skill…

View On WordPress

#DateDiff #sql Date

Overview of DML Operations - SQL Delete

Overview of DML Operations – SQL Delete

This article on the SQL Delete is a part of the SQL essential series on key statements, functions and operations in SQL Server.

To remove a row from a table is accomplished through a Data Manipulation Language, aka DML statement, using the delete keyword. The SQL delete operation is by far the simplest of all the DML commands. On execution of the delete command, we don’t have to worry about…

View On WordPress

#SQL delete

Overview of the SQL Insert statement

This article on the SQL Insert statement is part of a series on string manipulation functions, operators and techniques. The previous articles are focused on SQL query techniques, all centered around the task of data preparation and data transformation.

The general format is the INSERT INTO SQL statement followed by a table name, then the list of columns, and then the values that you want to use…

View On WordPress

#Different ways to insert SQL statment #insert statment in SQL #SQL Insert

SQL Pivot and Unpivot relational operatots

In this article, we’ll walk-through the SQL Pivot and SQL Unpivot operators and how they can be useful to transpose SQL Server data. Also, we’ll discuss both static and dynamic ways to use PIVOT and UNPIVOT relational operators that can be used to transform aggregated distinct values as column(s) in the result-set by specifying all the column values in the PIVOT IN clause.

SQL Server pivot…

View On WordPress

#SQL Dynamic Pivot #SQL Pivot #SQL Unpivot

Overview of the SQL Update statement

In this article, we’ll walk-through the SQL update statement to modify one or more existing rows in the table.

After reading this article, you’ll understand the following topics covering how to use a simple SQL update statement

on multiple columns

with computed value

with the compound operator

with the defaults

with SQL joins

with the Where clause

on a remote table

with use Top(n) clause

with…

View On WordPress

#CTE #ROWCOUNT #ROW_NUMBER #SQL Update #TOP

Overview of SQL UNION

This article will provide a deep dive into the SQL UNION operator, describing its many uses along with examples and explore some common questions like the differences between UNION vs UNION ALL. To address real-world data requirements, we may need to combine result sets from multiple data sources so that we could do data analysis or create new datasets. The datasets may be identical but there are…

View On WordPress

#Set operators #SET THEORY #SQL Union #SQL Union ALL

Overview of SQL Joins in SQL Server

The ability to combine results from related rows from multiple tables is an important part of relational database system design. In SQL Server, this is accomplished with the SQL join clause. It’s the nature of traditional relational database systems where some table contains information related to other tables with a common key value. Using a SQL join, you can easily perform queries on related…

View On WordPress

#equi join #left join #non-equi join #outer joins #right join #SQL Server inner join #SQL Server non-equi join

SQL Substring function in SQL Server

The requirement of data refactoring is very common and vital in data mining operations. In the previous article, you’ll learn the tips for getting started with SQL string functions, including the SQL substring function for data munging with SQL Server. As we all agree that the data stored in one form sometimes require a transformation, we’ll take a look at some common functions or tasks for…

View On WordPress

#SQL Server substring #substring example in SQL Server

DB Technology Enthusiast @prashantjayaram - Tumblr Blog | Tumgag

Trending Blogs

Recently Viewed Blogs

DB Technology Enthusiast