SQL DBA tips Archives » Page 2 of 3

Browsing: SQL DBA tips

How SQL Agent Job Schedule Got Changed Automatically

by Dev on

Have you ever wondered how SQL Agent Job Schedule Got Changed Automatically for one job while you changed the schedule of another job.

Problem Summary:

Your team got a requirement to change the schedule of a job to fix the conflict with another job/process. Team changed the schedule, but another job starts failing/taking longer to complete. Now you are troubleshooting. After scratching your head for quite sometime you realized that it is running on a different schedule. You checked with your team and got confirmation that nobody changed the schedule of the this job.

How SQL Agent Job Schedule Got Changed Automatically:

First thing to check is all recent changes on the SQL Instances. Important point to realize is no matter how minor change you are doing in production environment, you should always have a change task to track.

Straightaway you found a change task performed to create new job. Now this is first clue to check the activity. In our case we found that script of another job was used in the SQL Instance to create the new Job.

Now let’s see what happens when you script out a job. In the following demo setup I have one job named “Agent Job A”. Will script out the job and create another job “Agent Job B”.

How SQL Agent Job Schedule Got Changed Automatically — Fig: 1

Right Click on the job and script out:

The below highlighted portion of the job code is for Schedule.

Let’s see what happens if we just change the job name in the script to create another job and then change the schedule of the new job.

Execute the script and you can see another job is created with name “Agent Job B”

Now let’s check the schedule of both the jobs. It should be same as we haven’t changed anything.

You can get the script from the following link Script To List SQL Agent Job Schedule to get the output as above. As you can see both jobs have exactly same schedule. Now let’s manually change the schedule of “Agent Job B” to run only on Saturday as an example.

Right Click on the job “Agent Job B” > Go to Properties > Schedules > Edit

After making the change click on Ok and then again Ok. Now execute the same query again to check the schedules:

You can see “Agent Job A” schedule also same without even touching the job. Continue reading to know the reason, fix and how to prevent it.

Continue >>

{ Add a Comment }

SQL Agent Job Schedule got Changed -Resolved

by Dev on

This article is in continuation of How SQL Agent Job Schedule Got Changed Automatically. Here we’ll see how to find the root cause and resolve the issue. Please go through the first article if you have directly landed here to understand what issue we are talking about.

Script to check the SQL Agent Job Schedule details

Execute the below script on the SQL Instance:

SELECT sj.name, jc.schedule_id, jc.job_id 
FROM msdb..sysjobschedules jc
INNER JOIN msdb..sysjobs sj
ON jc.job_id = sj.job_id

Output:

SQL Agent Job Schedule got Changed — Fig: 1

As you can see the schedule_id (9 in this example) is same for both the jobs “Agent Job A” and “Agent Job B”. This is the point often overlooked. To put it another way, when you script out one job to create another, the schedule_id remains same. Hence whenever you change schedule of one job, it will change all other job’s schedule having the same schedule_id. Now lets see how to fix the issue.

Script to change the schedule_id of a SQL Agent Job

Execute the below query to change the schedule_id of the job “Agent Job B”. Not to mention that you need to change the cheduler_id and job_id in the script.

UPDATE msdb..sysjobschedules
SET schedule_id=1  --Put a unique value
WHERE job_id='9AC70265-8620-402E-BAD2-87F77D528B5C'  -- Change the job_id as per your job

Now if you run the first script again to check the SQL Agent Job Schedule Id, you can see change as highlighted in the screen shot.

After you change the schedule_id, you can edit the “Agent Job B” schedule without affecting “Agent Job A”.

Until now we have seen how to resolve the existing issue. Now let me show you how to prevent this while creating a job from another job script.

How to prevent having same schedule_id

Yes, you can very well avoid this by minor change while executing the script of another job. In this example I am going to create another job named ” Agent Job C” by scripting out “Agent Job A”.

Script out the job:

You just need to comment/Delete the reference of @schedule_uid in the job code as shown below:

Now, let’s execute the first query provided in this article again to check the schedule_id of the jobs:

Summary:

SQL Agent Job schedules are tied with Schedule_id. If you ever come across a situation when changing the schedule of one SQL Agent job affects other Job schedules you need to change the schedule_id of one of the jobs. Always remember to comment out/delete the reference of schedule_uid whenever you script out a job to create another job.

<<Previous

Tagged as: schedule_id, SQL Agent Job Schedule

{ Add a Comment }

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 }