Shrinking Tempdb in SQL Server won’t work?

Problem statement

When I was working DML operation on the database, It is noted that the tempdb has grown significantly larger and I didn’t have much space on the drive of that server. I tried shrinking the mdf file of tempdb. It executes successfully but no space is released to OS. Is restarting the SQL Server is the only way to release the space. what are other steps available to troubleshoot this issue? Is there any way I can do it without restarting the SQL service?.

Let’s explore the options.

Here are quick steps to analyze the tempdb resource contention issue.

DBCC SHRINKFILE (‘tempdev’, 1024)

The query executed successfully but the size of the database did not change.

SELECT * FROM sys.dm_exec_requests WHERE database_id = 2

No open transactions! Alright, any process holding locks on tempdb?

select * from sys.dm_tran_locks where resource_database_id= 2

No locks! There was a mention of sys.dm_db_session_space_usage DMV which helps to track the number of page allocation and deallocation by each session on the instance. Hence tried my luck with this query.

select * from sys.dm_db_session_space_usage where user_objects_alloc_page_count<> 0

Any session would use tempdb for creating some temporary objects. Since the database was not shrinking, obviously some user-defined tables would be there

SELECT * FROM tempdb..sys.all_objectswhere is_ms_shipped = 0

Check for user tables on tempdb

After performing all the above steps, Space is big constraint than execute the below command to free up the cache.

Note: It is not a recommended step. 

DBCC FREEPROCCACHE

This should free up tempdb

7)DBCC SHRINKFILE (‘tempdev’, 1024)

If this doesn’t help then the last step would restart the sql server.

Posted in Uncategorized | Tagged , , , , | Leave a comment

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 know that PowerShell is built on .Net automation framework. I would prefer PowerShell to design any such requirements than any other languages.

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 PowerShell script as an Input and output lists the ServerName, InstanceName, TCPPort and it’s the corresponding value

Method 1: Iterating over the Input file

The input file Server.csv contains the list of servers

For example,

Now, run the following PoSH script. In the script, you need to change the Inputfilename parameter.

#input file name
$Inputfilename='c:\server123.csv'
#An array to hold the output values
$Results=@()
#the servers are listed. Make WMI service is enabled on all the target machine
#Import the server list - Read the servers from the c:\server123.csv file.
Import-Csv $Inputfilename |% {
$namespace = Get-WmiObject -ComputerName $_.server -Namespace "root\microsoft\sqlserver" -Class "_Namespace" -Filter "name like 'ComputerManagement%'" | sort desc | select -ExpandProperty name -First 1
$port=Get-WmiObject -ComputerName $_.server -Namespace "root\microsoft\SqlServer\$namespace" -Class ServerNetworkProtocolProperty | select instancename,propertystrval,PropertyName,IPAddressName,ProtocolName | where{$_.IPAddressName -eq 'IPAll' -and $_.propertystrval -ne ''} foreach ($p in $port)
{
$Properties = @{
Servername = $_.Server
Instancename = $p.instanceName
PropertyName = $p.PropertyName
port=$p.propertystrval
}
$Results += New-Object psobject -Property $properties
}
}
$Results |select ServerName,InstanceName,PropertyName,Port |Format-Table -AutoSize

Output

Method 2: Direct feed of Servers as an array

In some scenarios where you don’t prefer to take input from a file. In this case, you can directly feed the server names as an array to the script.

#input file name
$Inputfilename='c:\server123.csv'
#An array to hold the output values
$Results=@()
'hqdbt01','hqdbsp18','hqdbsp17'|%{
$namespace = Get-WmiObject -ComputerName $_ -Namespace "root\microsoft\sqlserver" -Class "_Namespace" -Filter "name like 'ComputerManagement%'" | sort desc | select -ExpandProperty name -First 1
$port=Get-WmiObject -ComputerName $ -Namespace "root\microsoft\SqlServer\$namespace" -Class ServerNetworkProtocolProperty | select instancename,propertystrval,PropertyName,IPAddressName,ProtocolName | where{$_.IPAddressName -eq 'IPAll' -and $_.propertystrval -ne ''}
foreach ($p in $port)
{
$Properties = @{
Servername = $_
Instancename = $p.instanceName
PropertyName = $p.PropertyName
Port=$p.propertystrval
}
$Results += New-Object psobject -Property $properties
}
}
$Results |select ServerName,InstanceName,PropertyName,Port |Format-Table -AutoSize

Posted in SQL | Tagged , , | Leave a comment

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, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

At line:36 char:1

+ Select-AzureRmProfile -Path c:\AzureDataLakeprofile.json

+ ~~~~~~~~~~~~~~~~~~~~~

+ CategoryInfo : ObjectNotFound: (Select-AzureRmProfile:String) [], CommandNotFoundException

+ FullyQualifiedErrorId : CommandNotFoundException

Select-rmAzureDL

Workaround:

The Select-AzureRmProfile is no-more a validate cmdlet. If you encounter Select-AzureRmProfile cmdlet in the script, you may need to replace with Save-AzureRmContext and Import-AzureRmContext.

PS:\>Save-AzureRmContext -Path c:\AzureDataLakeprofile.json

PS:\>Import-AzureRmContext -Path c:\AzureDataLakeprofile.json

Output:

Now, you can see that the profile got loaded successfully

Azureaccount

Tagged , | Leave a comment

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 SQLShack family 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 for always supporting me.

Top 50 PowerShell bloggers of 2018

ranking

Thank you SQLShack and the team for the great recognition.

 

 

 

Posted in awards | Tagged | Leave a comment

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 you’re a consultant or an in-house DBA; you must have a reliable inventory of the servers that you manage. The inventory can be defined in different ways since it takes many different dimensions but at its core, it’s an aggregation of information about the system installation topography. However, often, we tend to forget to update the inventory when we add a server or update software. We deploy new servers, decommission existing servers, keeping track of servers in the environment, upgrade process, patching, migration from physical to virtual machines, moving to the cloud, license validation, IT forecasting and many more such areas strongly rely on the inventory list.

Further reading

Auto-discover SQL Server instances

 

 

Posted in SQL | Tagged , , , , | Leave a comment

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 spectrum

One of the most interesting data types that are supported in the relational database world is DateTime. In this article, we’re going to take a look at working with date time data types in SQL Server. We’ll understand the basics of date-time data-type and also, we’ll see various examples of how to query the date-time fields using built-in functions within SQL Server for manipulating the data, transforming date-time values and in few cases, perform arithmetic operations.

Further reading…

SQL Date functions

 

Posted in SQL | Tagged , | Leave a comment

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 getting any form of data from the table, and we don’t have to worry about working with any data that we get back from the table(s). We just simply tell the database to delete a specific record, and it either does or it doesn’t. It’s that simple.

First, let’s quickly review what an SQL delete statement looks like.  We need to tell the database and table from where it should delete the data. It’s a good idea to add a condition clause to set the scope of data deletion. Otherwise, it will delete everything on the table.

Further reading

SQL Delete 

 

 

 

 

Posted in SQL | Tagged | Leave a comment

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 the SQL insert statement to add data into those columns. Inserting is usually a straightforward task. It begins with the simple statement of inserting a single row. Many times, however, it is more efficient to use a set-based approach to create new rows. In the latter part of the article, let’s discuss various techniques for inserting many rows at a time.

Pre-requisite

The assumption is that you have the following the permission to perform the insert operation on a table

  • Insert operation defaults to the members of the sysadmin fixed server role, the db_owner and db_datawriter fixed database roles, and the table owner.
  • Insert with the OPENROWSET BULK option requires a user to be a member of the sysadmin fixed server role or of the bulkadmin fixed server role.
  • Download AdventureWorks2014 here

 

further reading…

https://www.sqlshack.com/overview-of-the-sql-insert-statement/

 

 

Posted in SQL | Tagged , , | Leave a comment

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 Introduction

There are several ways to transpose a dataset from rows to columns and columns to rows. SQL Pivot is one of the techniques that allows transposing of rows to columns and performs possible aggregations along the way. SQL PIVOT and SQL UNPIVOT relational operators transpose a table-valued two-dimensional data into another form of data. SQL PIVOT transposes a table-valued expression from a unique set of values from one column into multiple columns in the output and performs aggregations. SQL UNPIVOT performs the opposite operation of SQL PIVOT by transforming the columns of a table-valued expression into column values.

 

Continue reading…

SQL Pivot and Unpivot

 

 

 

Posted in SQL | Tagged , , | Leave a comment