March 2022 » SQLGeeksPro

Month: March 2022

Powershell Script to Create Folders

by Dev on

This article will help you with Powershell Script to Create Folders. Being DBA you may get into many situations when you need to create many folders.

Let me give a scenario. You have a SQL Instance of around 100 databases and need to create directory with the database names.

Powershell Script to Create Folders

Go to Start > Search and type “Powershell”. You can use any of the highlighted option.

Powershell Script to Create Folders

Here in this example I’ll be showing the example from first option. i.e. PowerShell ISE. Right Click > Run As Administrator

Powershell Script to Create Folders

The one liner code is as follows. Here I am creating a new folder named “folder” in the path ‘C:\BackupData\’
New-item -Path 'C:\BackupData\folder' -ItemType Directory

The following screen shot shows how it looks in the console.

Powershell Script to Create Folders

Now lets see how you can use this to create multiple folders.
First create a folder list in a notepad and save it in a notepad. In this example the file is “FolderNames.txt”
Powershell Script to Create Folders

$foldername= Get-Content -Path 'C:\Temp\Test\FolderNames.txt' #Provide the Path where the list is kept
$dirpath='C:\Temp\Test' #The Path where you need to create the folders
foreach ($folder in $foldername)
{
New-Item -ItemType Directory -Path $dirpath\$folder
}

Copy the code in the PowerShell ISE and click on the Play button to execute it. The folder names will be displayed in the bottom section as you can see in the below screen shot.
Powershell Script to Create Folders

Validate the path:
Powershell Script to Create Folders

Tagged as: PowerShell

{ Add a Comment }

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 }