prashantjayaram

[prashantjayaram]

Administering Relational Databases on Microsoft Azure: DP 300 study guide

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…

[prashantjayaram]

SQL Power Doc to Discover, Diagnose and Document SQL Server

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…

[prashantjayaram]

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…

[prashantjayaram]

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…

[prashantjayaram]

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

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…

[prashantjayaram]

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

[prashantjayaram]

Top 50 PowerShell bloggers of 2018

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…

[prashantjayaram]

How to automatically Discover SQL Server instances

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…

[prashantjayaram]

SQL Server Essentials Series

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…

[prashantjayaram]

SQL Date functions in SQL Server

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…

[prashantjayaram]

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…

[prashantjayaram]

Overview of the SQL Insert statement

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…

[prashantjayaram]

SQL Pivot and Unpivot relational operatots

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…

[prashantjayaram]

Overview of the SQL Update statement

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…

[prashantjayaram]

Overview of SQL UNION

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…

[prashantjayaram]

Overview of SQL Joins in SQL Server

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…

[prashantjayaram]

SQL Substring function in SQL Server

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…