SQL DBA tips Archives » Page 2 of 3

Browsing: SQL DBA tips

SQL Database Restore Stuck at 100 Percent

by Dev on

Hello there, today in this article we are going to find out how to check the reason for database restore Stuck at 100 Percent. DBAs often get the question “how much time it will take for the database to restore? ”

Generally DBAs provide a rough estimation based on the size of the database or by checking prior restore time. But while restoring, it may take more time due to different reasons. You as a DBA need to know how to find out the exact reason and let customer know.

Best way to trace it using Extended event session. There are multiple ways of creating extended event sessions. I have used “Backup_restore_progress_trace” which documents the internal steps of the restore process. You can use the below query to create the extended event.

Query to Create Restore Extended Event

CREATE EVENT SESSION [Restore Trace] ON SERVER 
ADD EVENT sqlserver.backup_restore_progress_trace(
    ACTION(sqlos.task_time,sqlserver.database_name))
ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\Restore Trace.xel') --Change the path as required
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

Database Restore Stuck at 100 Percent — Fig:1

Now navigate to Extended Events > Expand Sessions > Right Click on the newly created Ex event and click on Start Session:

After this you are ready to start database restore. Once the restore is started you can come back and click on “View Target Data” as shown below. Straightaway to the right you will be able to see the default view.

Now in order to analyze the data, you need to add few columns to the view. Especially “database_Name” “operation_type” and “trace_message”.

For example refer the below screenshot to add “trace_message” to the table, highlight the row > Right Click > click “Show Column in Table”:

Now you can see the there are a number of steps in this process.

SQL Database Restore Stuck at 100 Percent — Fig: 4

The below screenshot shows 100% completion step. Important to realize here is 100% processed bytes is the same amount of data to be transferred from backup file as shown in Fig:4. Moreover you can see that VLF processing continues after that.

Now you know why restore status can stuck/take longer even though it shows 100% completion.

Tagged as: Database restore, Extended event, Restore

{ Add a Comment }

Start SQL server using command line without local admin

by Dev on

Start SQL server using command line without local admin. As we all know you need to have local admin rights on the Server to start and stop SQL Services from Command line.

If you don’t know this easy trick, you may end up spending lot of time finding out how to get this done.

Now imagine a situation where you are a remote DBA and client is reluctant to provide you with the local admin access. Further WMI (Windows Management Instrumentation) is not working on the Server and you need to start SQL Server services in single user mode. As a result you are not able to launch SQL Server Configuration Manager.

At this point you want to convey to your customer that it is not possible to start or stop the SQL Services from Command Line without having local admin access.

The example SQL Instance is a default instance. The commands were discussed in this article.

Error if you try to Start/Stop SQL Server Services without starting cmd “Ran As Admin”

Start SQL Server using Command Line Without Local Admin — Error: Access Denied while Stopping SQL Service from Command Prompt

The client finally agreed to provide elevated permission on the Server. Now what? Is that enough to do your job. To find out you right clicked on the Command Prompt and see no option to open it with elevated permission.

The Options While You Do a right Click on cmd after having Elevated Permission

Instantly you used your favorite search engine and can’t find anything useful on this. The below simple steps will save big time. Believe me when I found it could not believe it was so easy.

Steps To Start SQL server using command line without local admin:

Type cmd in Search Box and Right Click >> Choose Open File Location

Right Click on the Command Prompt and now you get the option “Run Elevated” as shown below

The Command Prompt opens with elevated permission:

Important to realize the command prompt opens in Administrator mode as shown in the above screen shot. Same way you can open any tool like (SSMS, PowerShell etc.).

You might want to check other tips:

Interesting facts about SQL Server Startup Parameter -f

Steps in SQL Server Always On during OS Patching

Event id 133 from source report server cannot be found

Tagged as: Elevated Access, Local Admin

{ Add a Comment }

Interesting facts about Sql Server Startup Parameter -f

by Dev on

This article is about very interesting facts about SQL Server startup parameter “-f”. First let’s warm you up with the well known facts about the parameter.

The parameter -f starts an instance of SQL Server with minimal configuration. This is useful if the setting of a configuration value (for example, over-committing memory) has prevented the server from starting. Starting SQL Server in minimal configuration mode places SQL Server in single-user mode.

Make sense? This is exactly what is written in Microsoft Documentation. And if you are not so experienced in SQL Server I could imagine your facial expression.

With this in mind I shall explain with example what it actually means. At the same time the text “Starting SQL Server in minimal configuration mode places SQL Server in single-user mode” is self explanatory. That means you don’t have to specify -m ( Single user Mode) explicitly when you are using -f.

Let me show you by starting SQL Server with -f parameter:

SQL Server Startup Parameter — -f puts SQL In single user mode

Not to mention the command is for a default SQL Instance. If you have a named instance then you need to use either of the below:

net start “SQL Server ( instancename )”

net start MSSQL$ instancename

As you can see I haven’t mentioned /m (Single User Mode) parameter but the error still says the SQL Instance is in Single User Mode.

Error message: Server is in single user mode. Only one administrator can connect at this time.

Now let me explain the very first sentence which says “minimal configuration”. What exactly is minimal configuration.

When SQL Server starts with -f parameter:

It remains in Single User mode as mentioned above
SQL Server does not execute the Checkpoint process which it normally does while starting SQL Services
It disables any startup Stored Procedures and Triggers

Now that you understood what is minimal configuration in this context, let me tell you when we need to use it.

When to use -f startup parameter to start SQL Sever:

You may get the following error message while connecting to SQL Instance:

Likewise you may get the below insufficient memory error while executing a query:

Interesting fact about -f Startup Parameter

Without any further delay let me show you how the parameter helps SQL Instance to start though it has insufficient memory.

For this demo I have configured the Max Server Memory to 128 MB:

Now let me stop and then start SQL Server service with -f startup parameter again:

Here I want you to pay attention to the “run_value” for ‘Max Server Memory’ which is the maximum default value though the config value is 128. This is how -f parameter allows a user to connect even though SQL has insufficient memory.
Now does the SQL Instance has that much memory? of course not, it is just configuring to the max possible value and utilizes the amount of memory it needs to login.
Hope you have learned something interesting today. Let me know if you have any questions.

Tagged as: SQL Server Startup Parameter, Startup Parameter

{ Add a Comment }

Event id 133 from source report server cannot be found

by Dev on

<<Previous

This is about a fix for the error “Event id 133 from source report server cannot be found”. This is in continuation of SQL Server Reporting Services Not Starting.

If you have landed here directly and you are getting the exact error in the Application event log, you are in right place. In summary I had explained the troubleshooting steps and showed the exact error message in the first article.

Further troubleshooting steps:

Once I had checked the Application Event log, next I checked the Reporting Services log. The location of the log files is your SSRS install directory. In case you have observed, the error in the application event log contains the install directory:

event id 133 from source report server cannot be found — Application Event Log Showing SSRS Install Directory

Hence you can find the SSRS logs in the following location: Considering the above example.
“C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\LogFiles”

Looked at the latest log file and could find the following error message:

The error indicates some issues with the SSRS Config file. Refer the first screen shot in this article to find the path of the file.

“C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\RSReportServer.config”

The most compelling evidence is the following part in the error message:

“configmanager!DefaultDomain!20!03/27/2020-04:21:32:: e ERROR: Error loading configuration file: The ‘SemanticQuery’ start tag on line 220 does not match the end tag of ‘Extension’. Line 251, position 6.”

With this in mind I opened the config file in Visual Studio and searched for “SemanticQuery” and “Extension”.

Note: Please create a copy of the original RSReportServer.config file before making any changes.

Visual Studio View of RSReportServer.config file:

The arrow shows the <SemanticQuery> block. If you have observed, once I put the cursor in the </Extension> it showed the dialog box saying “Expecting end tag </SemanticQuery>.

The next step is to observe the difference of <Extension> blocks between “SemanticQuery” block. If you can see the red underlines does not have the ending ‘/’ except the one (second last red circle).

Where as the last <Extension> before the ending </SemanticQuery> has ‘/’. I removed the ‘/’ from the “Extension” block which was expecting end tag </SemanticQuery> and saved it.

Bingo! that worked and Reporting Services came online. This is how I fixed it, there may be multiple issues which you may need to fix.

If you have faced any other issues let me know by leaving your comments below.

Tagged as: SQL Server Reporting Services, SSRS

{ 2 Comments }

SQL Server Reporting Services Not Starting

by Dev on

This article is about troubleshooting SQL Server Reporting services not starting issue. It was a nice weekend until I got a call saying SSRS is not working. First thing I asked the on shift DBA if the services online and he says “No”. Of-course the DBA tried to bring it up and it failed saying “The service did not respond to the start or control request in a timely fashion‘.

Further he informed that Windows Team performed maintenance activity and as part of that they rebooted the server. After reboot all services came online except SSRS service.

Not to mention, you must have already guessed the situation. It required deeper troubleshooting and I am going to explain the steps in the following section.

The SQL Server version: SQL 2012 (SP4-GDR), Standard Edition.

Troubleshooting SQL Server Reporting Services Not Starting

First thing I tried to start the service from Services.msc to check if I get a different error. And It threw the following generic error:

The above error was of not much help but it will log the error at the top (mostly) in application event log. This is what I could find in the event log:

Log Name: Application
Source: Report Server
Date: 3/27/2020 6:40:30 AM
Event ID: 133
Task Category: (4)
Level: Error
Keywords: Classic
User: N/A
Computer: <<ServerName>>
Description:
The description for Event ID 133 from source Report Server cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

If the event originated on another computer, the display information had to be saved with the event.

The following information was included with the event:

C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\RSReportServer.config

The error image shows as follows:

The very first thing comes to mind after seeing the error is the SSRS service got corrupted and needs to be repaired/reinstalled. But that should always be the last resort. Continue to read to find the solution without reinstall. Next Page>>

{ Add a Comment }

Create SSMS shortcuts for different Windows Users

by Dev on

<<Previsous

In this article I am going to show how to create SSMS shortcuts for different Windows Users. This article is in continuation of Use Windows Authentication With a Different User in SSMS. If you haven’t read the parent article then please read that article first and then come back here.

In some cases you may have many Windows Authenticated logins for different SQL Instances and if you can create shortcuts for each, then it makes life a little easier.

Steps to Create shortcut for different Windows Authenticated Users:

First of all locate the path for ssms.exe. I have explained in the previous article how to quickly find the file location on your workstation.

e.g. The path is C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\ssms.exe

Go to your desktop (Windows Key+D). Right Click > New > Shortcut as shown below:

Create SSMS shortcuts for different Windows Users — Create New Shortcut

Put the runas command with the ssms.exe file loation:

In this example the command would be:

The runas command would be:

C:\WINDOWS\system32>runas /user:Domain\username "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\ssms.exe"

Type the name for the shortcut:

You can create multiple shortcuts for different Windows logins. You can also use /savecred in the runas command so that you don’t have to enter the password each time you open SSMS.

The command would be as follows:
C:\WINDOWS\system32>runas /user:Domain\username /savecred "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\ssms.exe"

Hope this helps.
You may also like the below links:

Remove or Delete Tempdb data file without restarting SQL Server

User Defined Profiler Template Location

Myth about Remote Query Timeout Option

Facts about Dedicated Administrator Connection

Tagged as: SSMS

{ Add a Comment }

Use Windows Authentication With a Different User in SSMS

by Dev on

Today I am going to show how you can use Windows Authentication With a Different User. Yes, you heard it right, it is possible and it’s one of those Wow factors in SQL Server.
First of all let’s talk about why even someone would need to use the functionality.

Customer may login to their laptop with a different domain account than the account used to connect to SQL Server. This is because of security reasons.

Another situation is customer have different domain account to connect to different SQL Servers. In some organizations this is very common practice.

The below screen shots are of Window 1o and SQL Server 2017.

How to run SSMS as a different user

Create a shortcut of SSMS in taskbar.
Hold Shift Key and right click on the shortcut as shown below:

Windows Authentication With a Different User

The next screen will need the credentials for the different user:

Once you click on Ok the login screen of SSMS will appear with the domain user grayed out just like the way we use windows authentication for normal account (the account used to login to the laptop\desktop).

You can open SSMS as a different user without creating a shortcut as well. You need to go to the path of ssms.exe and use the same method. The file resides in Binn folder of SQL install directory.

Well, you do not have to remember the path. There is an easy way to locate the path as shown below.

Go to Window Search (Windows Key + S) and type SSMS. Right Click on the SSMS icon and choose Open file location.

Windows Authentication With Different User — Open SSMS file Location

This will open the SSMS location of Windows Start Menu as shown below. Again right click and open file location.

Windows Authentication With a Different — Open SSMS file location

This is Open the physical file location of ssms.exe as shown below:

Once you reach here you can use the same method as described above to open SSMS using Windows Authentication with a Different User.

Do you have to do this every time you need to use a different windows authenticated user? Let’s see how we can create different shortcuts for different logins. Continue Reading>>

Tagged as: SQL DBA Tips

{ 1 Comment }

Uninstall SQL Server Components Continue

by Dev on

This is in continuation of Completely Uninstall SQL Server Components article. If you have not read that please click on the above like and then comeback to this page.

If msiinv.exe as mentioned in the first page works fine then it is good for you. Run the command once again and verify the output file.

PS C:\msiinv> .\msiinv.exe -s | Select-String "SQL Server" -Context 1,1 > C:\msiinv\msiinv_result2.txt

This time there should not be any SQL Components which you removed. If you see the components that means even msiinv.exe did not work. What is next?

You need to use Windows Installer CleanUp Utility. It is developed by Microsoft for Windows OS to resolve uninstallation problems of programs that use the Windows Installer technology. It wipes Invalid or corrupted entries from registry for the component.

There are many links available in the internet to download the tool. You can directly get it from here as well Windows CleanUp Utility

The utility displays a list of all the Windows Installer-based applications on the system as shown below:

Uninstall SQL Server

Select the component you want to uninstall and click on Remove:

Uninstall SQL Server

Note: Never Ever click on Select All. Tools are good until you make a mistake.

Here is the techNet article:

https://blogs.technet.microsoft.com/tnmag/2008/07/30/utility-spotlight-windows-installer-cleanup-utility/

Tagged as: Uninstall SQL Server

{ Add a Comment }

Completely Uninstall SQL Server Components

by Dev on

In this article I am going to show you how to Completely Uninstall SQL Server Components which are left behind. In other words what to do if the regular uninstall process from Control Panel fails.
Even more, sometimes the control panel shows that the SQL component has been uninstalled. But when you try to reinstall, it fails again. And this is because of the left behind components.

There are many articles in the internet suggests to change registry entry. Modifying registry is always associated with risk. For me it didn’t work, may be because the registry was corrupted.

You need to use the MSI inventory tools to check and uninstall the components. I have made it available for you in the following link Download msiinv.zip

The original source is Download from One Drive

Once downloaded open Powershell and run the below command. In the below example I have downloaded the msiinv in D drive under msiinv folder. Please put valid paths and then run the command. This will create the out put file with all the GUID of the SQL Components.

PS D:\msiinv> .\msiinv.exe -s | Select-String "SQL Server" -Context 1,1 > D:\msiinv\msiinv_result1.txt

If the execution is successful it will go to the prompt again as shown below:

Uninstall SQL Server

The result set will look like as below:

Uninstall SQL Server

You can find out the components you want to remove and note the product code from the file.
Execute the below command in PowerShell to uninstall the component.

 msiexec /x "{6BD8D100-B16C-409E-B0EA-BF508D7874EC}"

This will ask for final confirmation:

Uninstall SQL Server

If you are not comfortable with PowerShell or facing issues, then you can use command prompt to use the msiinv tool as shown below:

Command to execute is as follows:

msiinv.exe -p > D:\msiinv\msiinv_result1.txt

Uninstall SQL Server

The output file will have all the Windows components and not only SQL Server. You have to search through the file for SQL Server and use the same command as shown above to Uninstall SQL Server.

Sample file:

Uninstall SQL Server

Thanks to the following post:
https://www.mssqltips.com/sqlservertip/4050/cleanly-uninstalling-stubborn-sql-server-components/

Tagged as: Uninstall SQL Server

{ 4 Comments }

Issues with Uninstall OLE DB Provider for DB2

by Dev on

After reading this article you’ll be able to fix if standard method of uninstall OLE DB Provider for DB2 does not work. The requirement was to upgrade OLE DB provider for DB2 from version 3.0 to version 5.0. As per Microsoft Document you cannot upgrade OLE DB Provider for DB2 without uninstall of prior versions.

The following instructions are listed in Microsoft Document named “Microsoft OLE DB Provider for DB2 Version 5.0

Upgrade from Previous Version

Microsoft OLE DB Provider for DB2 V 5.0 does not upgrade previous releases. If you have the following previous versions installed, then you must remove them prior to installing the Microsoft OLE DB Provider for DB2 V 5.0.”

To uninstall the product

You can use Windows Programs and Features to remove the product.

Click Control Panel, click Programs, and then click Programs and Features. The Uninstall or change a program dialog appears.
In the Name list, double click Microsoft OLE DB Provider for DB2 Version 5.0. The Data Provider Installation Wizard appears.
Click Next to get started.
On the Program Maintenance dialog, click Remove.
On the Remove the Program dialog, click Remove.
When prompted by Windows User Account Control, click Yes.
On the Completion page, click Finish

This works in ideal condition. Uninstall of OLE DB Provider for DB2 does not always go well and throw weird error messages. The Uninstall may fail due to different reasons like existing DB2 version was not properly installed, someone deleted some of the MSI files or the registry corruption etc.

One of the error message looks like as follows:


MSI (s) (78:64) [21:44:40:923]: Note: 1: 1725
MSI (s) (78:64) [21:44:40:923]: Product: Microsoft OLE DB Provider for DB2 -- Removal failed.
MSI (s) (78:64) [21:44:40:923]: Windows Installer removed the product; Product Name: Microsoft OLE DB Provider for DB2 Product Version: 8.0.4294.0. Product Language: 1033. Manufacturer:Microsoft.Removal success or error status: 1603

As I mentioned earlier the error messages are strange and generic and googling did not help to resolve this particular issue. After this article it may help though 😉
In our case what worked was voiding the existing version of DB2 by changing the registry settings.
Please be careful and make sure to take a backup of your registry settings before following the below mentioned steps.

Steps to Rename The Registry Settings For Existing Version of DB2:

Go to the path HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Sna Server and rename “Sna Server” to “_Sna Server” as shown in the following screen shot:

Uninstall OLE DB Provider for DB2

Similarly make the following changes:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\_Host Integration Server
HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\ _Sna Server
HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\ _Host Integration Server
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Products\_00FAC227E6ED834428ED6E468B6B22B2
HKEY_CLASSES_ROOT\Installer\Products\ _00FAC227E6ED834428ED6E468B6B22B2
Than go to the Program Files Directory and for the Microsoft OLEDB Driver for DB2 and just put an (Underscore) in front of the directory name.

You may be thinking why only (Underscore), you can put anything meaningful like “DB2V3_Old”. It’ll work, but if you put symbol it’ll appear at the top and easy to identify what you have changed or for future reference.

I am leaving that decision up to you. The below screen shot shows how it looks in the registry after renaming:

Uninstall OLE DB Provider for DB2