Import Local SQL Server Database to Azure – Pick the Best Method

It is not unknown to the SQL users that some of the SQL Server versions have become outdated. This is the main reason why most of the SQL Server users are migrating SQL server database to Azure. Along with this, many are also moving to Azure due to its unique features. If you are switching the platform, you may also need to import local SQL Server database to Azure. In this post, we will discuss, two different approaches for the migration from on-premises SQL Server to Azure SQL database.

“Hello, all. I am writing here as I am in need to know the method to import data from SQL server to Azure. I have tried moving the database using the manual method, but that was unsuccessful as various issues came up. Please suggest me some effective method to perform this database migration smoothly and successfully.”

Techniques for Migrating SQL Server Database to Azure

There are different methods that will allow users to move their database to Azure. But not all of them are equally fruitful. To help our readers, we will talk about two methods in this post. One is manual and another one is automated. Let us not waste more time and go straight to the methods.

Manual Method to Import Local SQL Server Database to Azure

If users want to move their database manually, they can use the Deployment Wizard of SQL Server Management Studio (SSMS). This method should be used when the cloud migration process is not being hampered by any code compatibility problems. To start the migration in this method, run SQL Database Deployment Wizard on SSMS of any SQL Server instance.

Working Steps for migrating SQL server database to Azure

1. Select the database name from SSMS.

2. Right-click on it and select Tasks >> Deploy Database to Microsoft Azure SQL Database.

3. Fill in required Deployment Settings like Server connection, database name, the maximum size of the database, Azure SQL version, etc. Click Next.

4. Review the summary of the Settings and click on Finish.

5. Wait till the wizard finishes database validation, BACPAC file creation, and use Azure SQL for database creation.

6. When the process is completed, the new database has been created in the SQL Azure server.

Automated Method to Move Local SQL Database to Azure

If you think the manual method is time-consuming and requires a lot of attention, you can choose to use SQL Server to Azure Database Migrator Software. This application will allow you to seamlessly import local SQL Server database to Azure. Here are the complete working steps for the migration from on-premises SQL Server to Azure SQL database:

Step 1: Download and install the software on the Windows system.

Step 2: Launch the tool and click on the Open button.

Step 3: If the database is corrupt, choose Advance Scan mode. Otherwise, choose Quick Scan.

Step 4: Select the SQL Server version of the database. If you do not know the version, choose the Auto-detect option as pointing out the wrong version will cause trouble.

Step 5: For adding NDF file of the database, select NDF Options tab. Either manually select the NDF file or let the tool choose automatically. Make sure the NDF file is associated with the chosen MDF file.

Step 6: When file addition is done, users will be given a summary of the MDF file. If they want, they can also save the scan result for the future.

Step 7: Complete database records of MDF file including Tables, Triggers, Views, Stored Procedures, Keys, Indexes, Columns can be previewed. Click on Migrate button to import the database in Azure.

Step 8: Add Azure Database details and login credentials. Click on Connect and wait for the green tick mark.

Step 9: Select the destination database name from the list.

Step 10: On the left side of the tool, mark the database components you wish to move to Azure SQL Database.

Step 11: Also, select whether to transfer “With schema” or “With schema & data.” If required, users can also migrate deleted records.

Step 12: Click on Migrate and the software will successfully import local SQL Server database to Azure SQL Server database.

Final Words

Azure is gradually becoming a popular platform for SQL Database. Therefore, many people are migrating SQL server database to Azure. To meet their requirement, we have shared guidelines for two different techniques to perform this database migration. Users can go through the steps and choose that one they find useful. Since the software can import local SQL Server database to Azure in a short and simple manner, most of the users prefer this one over the manual method.

Advertisements
Posted in SQL | Tagged , | Leave a comment

Microsoft SQL Server Error 5243 – Top 3 Ways To Fix This Issue

Maintaining the SQL server database is always the first priority of the SQL administrators. But sometimes SQL database users have to face Microsoft SQL server error 5243 and error 5242. These are common errors. Due to this problem, the SQL users won’t be able to access their databases. Let us understand the situation with the help of an example.

“Please help! I was working with my SQL database and doing some test. Everything was fine. But suddenly when checked my error log I received an error like this. Can anyone help me to fix this Microsoft SQL Server Error 5243? Thanks!

Msg 5242, Level 22, State 1, Line 1

An inconsistency was detected during an internal operation in database ‘Invoices'(ID:11) on the page (1:35397). Please contact technical support. Reference number 4.

SQL server introduced two errors types of errors 5243 or 5242. When the users receive this kind of error it means that the SQL server database is corrupt. This type of error is clearly visible in the error log. If this type of error occurs, then you then the user has to figure out what has happened to the I/O system. This error describes record type is wrong for the current type of page.

Methods to Fix Microsoft SQL Server Error 5243

  1. Run the DBCC CHECK DB command – The user can try to run DBCC CHECKDB T-SQL command to restore the database. This command helps to check the physical and logical integrity of all the database objects in the specified database. For complete information about DBCC CHECKDB. If the database is too big then the user can use the REPAIR REBUILD option.
  2. By Restoring The Corrupt Page – In case if the page is corrupt and if the user knows the corrupt page then it is possible to restore the correct page.

In Microsoft SQL Server Error 5243 the user can view the corrupted page. Also, the user can detect the corrupt page by using a select in the MSDB database to the suspect_pages table.

Follow this T-SQL command in order to restore the page

Restore Database < Your Database Name>

Page = File:Page

File <device_name >

WITH NORECOVERY

Resolve Microsoft SQL Server Error 5243 By Using Expert Solution

In case if the user cannot be able to restore the data then the user can use SQL Database Repair tool. With the help of this software, the user can easily fix Microsoft SQL Server Error 5243. This software is a simple, easy to use and smartly developed software. It can be used to restore SQL server database in case of database corruption. This utility can easily repair the corrupted MDF and NDF file data. By using this software the user can scan and recover triggers, stored procedure, functions, tables. Moreover, this tool is compatible with SQL server 2017 and its below versions.

download

Final Words

In this article, we have discussed how to fix Microsoft SQL server error 5243. The user can try to restore the database by using manual methods. But these methods require strong technical skills and knowledge. So to avoid data loss situation the user can take the help of MDF Recovery Software to resolve this problem.

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

Restore SQL Server 2014 Database from Backup – Step By Step Guide

Microsoft SQL server database contains the most crucial data of organizations. But while using MS SQL server many users often face problems like abrupt termination, database connection errors, transient errors, malicious software attacks. In order to save their important SQL server data, many organizations are prepared for the worst case scenarios to avoid data loss situations. In some situations, various users want to know how to restore SQL Server 2014 database from backup.

Well backing up your Microsoft SQL server data not only helps the user to restore the data but it also helps the user in such situations like disaster recovery. The general practice of creating the backup is very crucial when SQL data is the key factor for the business. Before proceeding to the solution part to restore SQL server 2014 database from backup. Let us first understand the importance of SQL server backups.

Importance of SQL Server Data Backups

Here are the points which help the user to understand the Importance of creating backups.

  1. It helps to protect from power failures – As we know that computer systems are prone to damage due to power failures. So due to sudden power failures, the SQL data might get corrupt and it will lead to a data loss situation. Therefore with the help of backups, the user can easily restore the data.
  2. Safety from Virus and other Malware attacks – Its nothing new that files and folder turn into an inaccessible state because of Virus and malware attacks. And by multiple trials of accessing the affected files and folders. The viruses spread throughout the system. So in this way backups helps to restore databases.
  3. Recover data in case of Operating system failure – So in case of an operating system crash or failure, all the files and the data in the hard drive will be gone. Backing up the data is the only thing that remains in court to get things back.
  4. Other Reasons – Data backup is also important in case if the general user’s errors like deleting a table by mistake. In that case, the user can easily restore the data from the backup.

Backing up the files and folders is not only essential things but also a good practice. That assures that your important data won’t go anywhere no matter how worse the situation is.

Restore SQL Server 2014 Database From Backup – By Using Manual Technique

#Technique 1: By Using SQL Server Management Studio

  1. First, the user has to connect to the appropriate instance of the Microsoft SQL Server database engine.
  2. After connecting, then go to the Object Explorer, and then click on the server name to Expand the server tree.
  3. Now click on databases, and then choose either a system database or user database.
  4. Perform the right click on the database and then go to Task and after a click on restore.
  5. Choose the type of restoration operation. Such as database file, filegroups or transaction log.
  6. Now on General page, the user has to click on from Device.
  7. After that click on browse button to open the specify backup box dialogue box.
  8. Now the user has to select the backup device in the backup media text box and after that click on Add button to open select backup device dialogue box.
  9. Choose the device that you want to use for the restore operation. After selecting the restore option choose backup file and then click on OK.

Automated Solution to Restore SQL Server 2014 Database From Backup

The manual solution is not a reliable solution to perform this task. Because it requires strong technical knowledge. So to avoid in any data loss situation the user can take the help of SQL Backup Restore Tool. This software gives two export options SQL server database or SQL server compatible scripts to export SQL data. The user can easily recover and preview database Tables, views, stored procedure, Triggers, columns, functions, etc. This utility supports .bak file of SQL server 2017 and below versions.

Note: If the user wants to repair the corrupted MDF (primary database file) and NDF (secondary database file) then the user can try SQL database recovery software to resolve all the SQL database corruption issues.

Final Words

Many time SQL users want to know how to restore SQL server 2014 database from backup. So in this article, we have discussed the step by step procedure to perform this task. We have also given the importance of SQL server data backups. The above discussed manual solution to perform this task has various limitations. To avoid any data loss situations the user can take the help of Automated solution to resolve this issue.

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

Know How to Repair SQL Database Marked as Suspect Mode in SQL Server

Repair SQL Database Marked as Suspect Mode

MS SQL server is a widely used relational database management system developed by Microsoft. It is a software product which primarily helps to store and retrieve the data of other applications. SQL database can have a specific state at a given time and it runs in various different modes which are online, offline, restoring, recovering, recovery pending, suspect, emergency. But sometimes when the user connects to the SQL server. The user will find the database is marked as a suspect state. The user will not be able to connect to the database. So in this article, we will discuss how to repair SQL database marked as suspect. Also, the forthcoming article will also discuss the various reasons for this error.

“I am currently using SQL server 2014 addition on my Windows 8 machine. Recently when I tried to connect to the SQL database I got an error like this database cannot be opened it has been marked suspect by recovery error. I don’t know what to do in this situation, Kindly guide me how to tackle with MS SQL database marked as suspect error problem.”

sql suspect error

Possible Reasons Behind MS SQL Database Marked as Suspect

When the SQL server starts up, then it attempts to obtain an exclusive lock on the server device file. In case of the device, the file is being used by another program or its found missing then SQL server starts displaying errors. Here are the possible reasons for this problem.

  1. Due to unexpected SQL server shutdown, power failure, or hardware failure.
  2. In the case of a database, files are being held by the operating system or third-party backup software.
  3. The system failed to open the device where the data or log files resides.
  4. In case if the SQL cannot complete a rollback or roll forward operation.
  5. SQL database cannot be opened due to inaccessible files or insufficient disk space.

Know How to Repair SQL Database Marked as Suspect – Manually

Here are the steps to fix MS SQL database marked as suspect mode problem.

  1. First Open SQL server management studio and then connect to your database.
  2. Now select the new query option.
  3. Then turn on the suspect Flag on the database and then set it to Emergency
  4. EXEC sp_resetstatus ‘database name’;

    ALTER DATABASE databasename SET EMERGENCY

  5. Now perform a Consistency check on the Master database.
  6. DBCC CHECKDB (‘database name’)

  7. Now Bring the database into single user mode and after that roll back the previous transactions.
  8. ALTER DATABASE databasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  9. Take the backup of the complete SQL database.
  10. Then attempt the database repair allowing some data loss.
  11. DBCC CHECKDB (‘database name’, REPAIR_ALLOW_DATA_LOSS)

  12. Now bring the database into multiuser mode.
  13. ALTER DATABASE databasename SET MULTI_USER

  14. Now refresh the server and verify the connectivity of the database.

Note: By performing the above steps the MS SQL database marked as the suspect problem will be solved. In case if the problem is still there then the user can take the help of an automated solution.

Automated Solution to Resolve MS SQL Database Marked as Suspect Error

If you have tried the above procedure and you are unable to resolve this issue or your database still marked as Suspect then you can try SQL Database Recovery. This utility is an ultimate solution to recover corrupted SQL database. By using this software the user can restore database objects like Triggers, views, functions, stored procedure, etc. This software is capable to recover deleted records from database tables. It provides two scan options which are quick scan and advanced scan to repair corrupted SQL database. This software is compatible with SQL 2017 and its below versions.

download

Final Conclusion

In this article, we have discussed how to repair SQL database marked as suspect. Also, we have discussed the various causes of this problem. We have given the manual solution to solve MS SQL database marked as suspect error issue. If the user doesn’t want to face any data loss situation then, in that case, the user can take the help of an automated solution MS SQL recovery tool to recover your most crucial SQL database.

Posted in SQL | Tagged , | Leave a comment

Kernel Migrator for SQL Server – Expert Product Review

Ease of use, accuracy, integrity, efficiency, and security are some characteristics which a good software should have, and when we speak of software products that focus on business users, the scale of expectations increases. Therefore, there remains no room for software functionality mistakes and bluffs; otherwise, the risk of data loss or corruption can surface.

Kernel Migrator for SQL Server claims to migrate both corrupt and healthy SQL server database file(s) to distinct cloud platforms, which I’ve tested carefully, to find out how well it gets the job done.

I’ll walk you through my Kernel Migrator for SQL Server review dwelling the associated benefits and my experience, explaining how well the job of SQL Server database migration went.

Let’s have a look at specifications first:

Supported Versions of MS SQL Server: 2000 to 2017

Processor: Intel Pentium.

RAM: 256 Minimum.

Storage: 50MB

For more info, you can visit the Kernel website.

User Interface

Software should please you not only with its functionality but with looks as well; the User-Interface plays an important role in deciding the level of user acceptance & friendliness; otherwise, it’ll be like any other typical software product.

Kernel’s Migrator for SQL Server boasts of modern UI, and if I compare with what it has to offer, it doesn’t have multiple options which generally turns the look & feel into bulky.

Open, Migrate, and Help are the main options available in the menu bar. Each time you launch the tool, the Select SQL Database window pops on-screen automatically saving your time to reach to the Open option, but in case if you close the window you can reach to the option and launch it again whenever needed.

My Experience

Support is one of the important things which most of the users seek from a software product whether it is for personal use or business use. Kernel tool with its wide support for older software versions eliminates the accessibility gap.

I’ve SQL Server 2017 installed on my system, and I was not hoping that Migrator for SQL Server tool would be supporting this version, but unexpectedly it did, and that’s not all, the tool support all versions of SQL Server ranging from SQL Server 2000 to 2017 – which is the latest version of SQL Server available.

Kernel Migrator for SQL Server is accompanied with the built-in ability to detect the database version, but in case if someone wants to select the same manually, he/she can do so. For me, it did the job automatically.

The tool comes with the feature of Live Preview, which in all senses is quite nice. Once you load the file and the database file scan is complete, it lists all database objects in a tree structure – you can expand or collapse the objects to check what’s inside and click on the object to preview it.

For many, it can be confusing for why does this feature even matter? It matters as it helps you in confirming the data integrity & hierarchy.

For cross-check, I’d tried a demo database file, and you all can see the results below.

At first, I thought of Kernel’s Migrator for SQL Server as a simple tool that serves for the only purpose of database migration to Azure SQL & Amazon RDS with limited functionalities, but after I used it for a while, I was quite surprised to know that I can migrate either all or selected database objects.

From destination database selection to server authentication credentials input, everything is combined and given in unified Migration Options window; no extra efforts are required to first configure the tool with SQL Server and login credentials.

Compatibility

The issue of compatibility sometimes comes as a big question especially for those companies or organizations who are still using an older build of SQL Server for their database needs; there could be many reasons why these organizations practice such habit.

Kernel has done a good job in terms of compatibility; the tool is made wide compatible with a range of Microsoft SQL Server versions form SQL Server version 2000 to 2017.

Pricing & Availability

Kernel Migrator for SQL Server is made available in two distinct product license categories which are Corporate and Technician License. For both the license types, it offers lifetime support, software updates, and 100% money back guarantee.

The Corporate license costs around $149 whereas Technician license costs for $299 including other associated benefits. It’s not proper to suggest you invest your money right after learning what I’ve experienced, and so you can download the free version and check on your own. The trial version is bounded with some limitations but to prove the claims, it can generate Live preview of database objects.

Pros

  • Easy to use.
  • Minimal User-Interface.
  • Advanced Scan Mode.
  • Compatible with older SQL Server versions.

Live Preview.

Cons

  • Cannot migrate database using the Free Trial version.
  • Cannot select more than one database file at a time for migration.

Summary

SQL Migration tool is a lightweight tool that doesn’t require high-end hardware to run. It is easy to use too. In my Kernel Migrator for SQL Server review, I’ve shared my experience to let you all better understand how it performs the migration. The free trial version allows you to understand better about what you’ll be getting when you will make the purchase. You too can share your views over the same and ask questions in case of any queries you might have.

Posted in Uncategorized | Tagged | Leave a comment

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

| 2 Comments

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