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 comes to automating even requirements such as configuration management. I got an opportunity to assess SQL Power Doc recently. I have worked with PowerShell a lot (I have even written a book on it). I have built my share of automation and designing tools using PowerShell. When I took a look at SQL Power Doc, I felt the tool does have its share of capabilities to help DBAs in its own way. Let me detail some of my experience while exploring this tool. Let’s get started.

What is SQLPower Doc?

SQL Power Doc was written by Kendal VanDyke. It is a command line PowerShell manifest consisting of several PowerShell modules that will help you build a solid foundation to Discover, Document and Diagnose SQL Server instances and Windows Operating system details. Additionally, you’ll gain some insight into what directions you can go for future exploration. It basically invokes PowerShell cmdlets and give you the necessary details front and center.

Further reading

Using SQL Power Doc to Discover, Diagnose and Document SQL Server

Advertisements
| Leave a comment

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 milestone in my professional life, and all the credit go to our captain and the SQLShack family.

To view all my articles, click the following link

https://www.sqlshack.com/author/prashanth/

Posted in awards | Leave a comment

How to Change Suspect Mode to Normal Mode in SQL Server Database

If you are reading this, you are desperately looking for solutions to change suspect mode to normal mode. It also means that your database has gone into the suspect mode. In that case, you have already experienced one of the most irritating situations possible for a DBA.

Worry no more, as you will soon get out of this situation. In this post, we have compiled some of the prominent solutions to the query, “how to change suspect mode to normal mode in SQL”. We will also tell you in detail in which situations a database can turn into suspect mode and what can be done to get back the database into normal mode.

How to Change Suspect Mode to Normal Mode – Possible Scenarios

In this section, let us check some of the common scenarios responsible for suspect mode of SQL database.

Scenario 1: “Some inexperienced employees used SQL database yesterday and now the database is in suspect mode. I guess they may have misplaced some data. How to bring back a Database Online from Suspect mode?”

What happens in this case is some data or log files get deleted (or misplaced) from the database when the Server is in offline mode. As a result, the database fails to start and goes into the suspect mode.

Solution: If the suspect mode situation occurs due to this reason, the error message of SQL Error Log will contain the name and directory of the missing file. Place back the file and then run this command to put the database online in normal mode without any data loss.

RESTORE DATABASE WITH RECOVERY

Scenario 2: “Some applications have locked our database files and now the database cannot come online. How can we remove the suspect mode and bring in online in the normal mode?”

In this particular situation, SQL Server fails to put an exclusive lock on the log file or data when it tries to come online. It also fails to access a lock placed on data/ log file by some other tools like anti-virus applications (happens if SQL Server is shared with these tools).

Solution: If the above reason has put the database in suspect mode, you have to kill the file handler responsible for placing lock on the file. Take the help of Process Explorer to do this and the involvement of System Admins is recommended to perform this particular step. Later, run this command to put SQL database into normal mode again.

RESTORE DATABASE WITH RECOVERY

Scenario 3: “Due to power surge, SQL Server got shut down suddenly while it was in the middle of a transaction. Now the database is offline with suspect mode because of the corrupt transaction. Tell me how to bring the database back into normal mode.”

Here, a corrupt transaction that took place in the database has put the database into the suspect mode. This is often considered as the worst possible scenario as it contains high chance of data loss if there is no good backup. This is also the main reason for suspect mode in OLTP databases. If SQL Server is been closed abruptly or restarted while in the middle of a transaction and then it failed to complete the transaction (whether commit or rollback), this scenario occurs.

Solution: If you have a good backup of your database available, you can restore the database up to a suitable point. If you do not have this option available, you need to run the DBCC CHECKDB Repair_Allow_Data_Loss command. These are the steps you have to perform on SQL Server Management Studio.

Remember: Following the below steps can result in data loss from your database. So, we recommend you to try this only after you have tried every other solution.

  • Use SQL Server Management Studio to connect to SQL database and find out which database has gone into suspect mode. To do this, run the command:
  • USE master
    GO
    SELECT NAME, STATE_DESC FROM SYS.DATABASES
    WHERE STATE_DESC='SUSPECT'
    GO
  • Expand Management Node > SQL Server Error Logs and open the SQL error log. Check if the database is marked suspect in the error log.
  • Now, put the suspect database into Emergency mode, since it is not feasible to connect to a database that is in suspect mode. Run this command:
  • USE master
    GO
    ALTER DATABASE DBO SET EMERGENCY
    GO

    Note: Before you enable Emergency Mode, remember that it cannot be rolled back. So, we suggest you to take data backup if possible before applying this command.

  • Now the database has gone into read-only state and only system admin privileges can access the database. You can run this command to verify the physical and logical integrity of all database objects:
  • DBCC CHECKDB (DBO)
    GO
  • This command will enable single user mode for the database. It will also check if rollback is available:
  • ALTER DATABASE DBO SET SINGLE_USER WITH ROLLBACK IMMEDIATELY
    GO
  • It is time to repair the database and you need to use the below DBCC CHECKDB command:
  • DBCC CHECKDB (DBO, REPAIR_ALLOW_DATA_LOSS)
    GO

    Warning: Once you have run this command, there is no rollback available. Any lost data will be lost forever.

  • Put the database into MULTI_USER mode so that anyone can use the database. Run this command:
    ALTER DATABASE DBO SET MULTI_USER
    GO

You should have your database back in online mode by now. You have to check how much data you have lost.

Here are some of the other reasons that can put SQL database into suspect mode:

  • SQL database MDF file corruption
  • Problems during rollback or transaction completion
  • Errors due to limited space on disc or system drive.
  • Transaction log corruption
  • When the Server cannot access the drive where the log files are stored
  • Corruption of hard disk

Change Suspect Mode to Normal Mode Without Any Data Loss

Manual approaches that users implement to turn the suspect database into normal mode involves loss of valuable data. Most of the SQL users cannot afford to lose their data and hence they look for some better solution. SQL Database Repair Software is such an application that can fix all the scenarios responsible for suspect mode and put in back to normal mode without losing any data. To run this application, you have to perform these steps:

  1. Add the database file with suspect mode in the software.
  2. Apply Advanced scan mode to fix any discrepancy.
  3. The tool will scan the database and repair it.
  4. The complete recovered database will be available for preview.Click Export the save the required database components in any location.

Conclusion

Since the suspect mode is a common issue faced by SQL users, these users keep asking in forums, “how to change suspect mode to normal mode in SQL?” Their search should end here as we have depicted some verified methods to put SQL database into normal mode. Use any of the solutions mentioned here to get rid of the suspect mode SQL database.

Posted in SQL | Leave a comment

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