SQLGeeksPro » Page 3 of 7 » SQL Server DBA

Junior SQL Server DBA Interview Questions and Answers

by Dev on

Hey there, this article is about Junior SQL Server DBA Interview Questions and Answers. This will help you learn and prepare for Interview. If you are an Interviewer then also it will help you to access a Junior DBA’s skill. You can ask these questions for 0 – 2 years of experience.

Please do not mug up the answers, always understand the concepts. That will help you to grow your technical skills. I have taken many interviews and it is easy to understand if the candidate has just read a blog without understanding the concepts. The below questions are from real Interviews hence learn well and all the best.

1. What is a Database?

Ans: A database is an collection of structured data. Electronically stored and organized in a computer system. The data can be easily accessed, managed, modified, updated and controlled.

Know more: https://en.wikipedia.org/wiki/Database

2. What are the main differences between DBMS and RDBMS?

Ans: DBMS stands for Database Management System and RDBMS is the achroname for Relational Database Management System.

DBMS is basically a software to maintain/manage a database with ability to provide controlled access to the data. DBMS stores data as a file.

RDBMS is a an advanced version of DBMS where the data is organized in tables which are related to each other or linked together.

DBMS has low software and hardware requirements whereas RDBMS has higher hardware and software requirements

Any DBMS supports single users, while RDBMS supports multiple users.

DBMS does not support client-server architecture but RDBMS supports client-server architecture.

Examples of DBMS — MS Access, FoxPro, dBase etc.

Example of RDBMS – SQL Server, Oracle, MySQL etc.

3. What is a Transaction in SQL Server?

Ans: Transaction is a set of SQL statements/commands. i.e. Batch of SQL Statement(s) performing a single or multiple activities on a database. It can be a single SQL Statement as well.

Example:

SQL Transaction

4. What are ACID properties?

Ans: ACID stand for Atomicity, Consistency, Isolation, and Durability − commonly known as ACID properties of a transaction.

Atomicity:

Either all or none. Either the entire transaction takes place at once or doesn’t happen at all. There is nothing like partial completion of transactions.

e.g. You are withdrawing money from ATM. The action (Transaction at the background) either has to complete or does not happen at all.

Consistency:

The database must be in consistent state before and after the transaction.

e.g. If you are transferring money from one account to another account. The total amount of both the accounts should be same before and after the transfer.

Example:
A transferring 100 dollars to B. A’s initial balance is 200 and B’s initial balance is 500.

Before the transaction:
Total of A+B = 200 + 500 = 700$

After the transaction:
Total of A+B = 100 + 6000 = 700$

Isolation:

A transaction occurs independently. It shouldn’t interfere with the execution of another transaction. Isolation ensures the occurrence of multiple transactions concurrently without a database state leading to a state of inconsistency.

e.g. An account A is having a balance of 500$ and two transactions started simultaneously to transfer 100$ each to account B and C. Let’s say these transactions run concurrently and both the transactions read 500$ balance, in that case the final balance of A would be 400$ instead of 300$. So data will become inconsistent.

Isolation makes sure one of the transactions complete first followed by the second one. Hence second transaction would read the correct balance of A as 400$ then deduct 100$.

Durability:

Once a transaction completes successfully, the changes/updates it has made into the database is permanent even if there is a system failure.

e.g: Consider any of the above examples. Once the transaction completes even if the system fails/power down, the account balance will remain same.

5. What is DDL and DML statements?

Ans: DDL – Data Definition Language consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema. It is used to create and modify the structure of database objects in the database.

e.g. CREATE, DROP, ALTER, RENAME etc.

DML – Data Manipulation Language deals with the manipulation of data present in the database.

e.g. INSERT, UPDATE, DELETE etc.

6. What is the difference between Implicit, Explicit and Auto Commit transactions?

Ans: In the implicit transaction mode, SQL Server begins the transactions implicitly but it waits for the commit or rollback commands from the user.

e.g. SET IMPLICIT_TRANSACTIONS ON UPDATE T1 SET i=2 WHERE i=3 COMMIT;
Until you run the COMMIT statement the transaction will not complete.

In Explicit transaction mode, user must start the transaction with the BEGIN TRANSACTION statement and end with either COMMIT TRANSACTION or ROLLBACK TRANSACTION statements.

e.g. BEGIN TRAN UPDATE T1 SET i=2 WHERE i=3 COMMIT TRAN;

Auto Commit: This is the Default mode of SQL Server. When you run a query without specifying BEGIN TRAN/SET IMPLICIT_TRANSACTIONS, the transaction opens internally and closed automatically.

e.g. UPDATE T1 SET i=2 WHERE i=3

7. What are system databases and User databases?

Ans: The databases which got installed during the Installation of SQL Server are called as system databases. These are basically Master, Model, Msdb, Tempdb and Resource db.

System databases are used to manage SQL Server and contain information about the SQL Server system like logins, databases, linked servers, jobs, schedules, reports, etc.

User databases are created by users/application. These databases contain the data of application.

8. Explain the use of system databases in SQL Server?

Ans:

Master Database:

Contains all the system-level information for an instance of SQL Server. The master database is the most important as it is necessary for starting the SQL Server.

This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings.

Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server. Therefore, SQL Server cannot start if the master database is unavailable.

Model Database:

The model database is used as a template for other databases created in SQL Server, especially when creating user-defined databases.

The entire configuration of the model database, including database options, are copied to the newly created database.

Every time SQL Server is started, it creates tempdb from model hence if model is corrupted, SQL will not come up normally.

Msdb Database:

The msdb database is used mainly by the SQL Server Agent, and also by other features like SQL Server Management Studio, Service Broker, and Database Mail.

SQL Server automatically maintains a complete online backup-and-restore history within tables in msdb.

Tempdb Database:

TempDB is a global resource that is accessed by all the users in the SQL Server Instance

This stores temporary user objects that you create like temporary tables (Global and Local), temporary variables, temporary stored procedures that hold intermediate results during the sorting or query processing, and cursors.

Internal objects like Work Tables, Work files. Intermediate sort results for operations such as creating or rebuilding indexes.

Version stores, which are collections of data pages that hold the data rows that support features for row versioning.

Resource database:

The Resource database is a read-only database that contains all the system objects that are included with SQL Server.

SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

The Resource database does not contain user data or user metadata.

Continue reading Junior DBA Interview Q&A -Part2

Please spread the word:

https://sqlgeekspro.com/junior-sql-server-dba-interview-questions-answers/

{ Add a Comment }

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

Please spread the word:

https://sqlgeekspro.com/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 >>

Please spread the word:

https://sqlgeekspro.com/sql-agent-job-schedule-got-changed/

{ 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

Please spread the word:

https://sqlgeekspro.com/sql-agent-job-schedule-resolved/

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.

Please spread the word:

https://sqlgeekspro.com/sql-database-restore-stuck-at-100-percent/

Tagged as: Database restore, Extended event, Restore

{ Add a Comment }

Powershell Script to Validate SQL Servers

by Dev on

This article is about a Powershell script to Check SQL Server Database Status and Services remotely. This will help in validating SQL Server after Windows patching.

To put in another way, DBAs are responsible for validating SQL Servers after server maintenance. Usually DBAs need to do a sanity check of the SQL Server after planned or unplanned maintenance.

The most common checks while doing a SQL Server validation:

Validate SQL Services are up and running
SQL Databases are online
There is no error in the error log
Authentication protocol after the restart (Kerberos Or NTLM)

Now imagine if you have a large number of servers, how much time you need to spend on such activity. The below simple yet effective powershell script will save a lot of time.

Powershell script to Check SQL Server Database Status and Services:

###########################################################################################################
#Objective: To Check SQL Service status, Database status, Errors in Errorlog and Authentication protocol  #
#Author: Tridib Dev                                                                                       #
#Source:https://sqlgeekspro.com/                                                                          #
#Parameter: SQL Instance Name                                                                             #
###########################################################################################################
param ([parameter(Mandatory = $true)] $SQLServer)
        
Write-host 'SQL Service Status for: ' $SQLServer
Write-host '---------------------------------------------'
Write-host " "
Get-WmiObject -Class Win32_Service -ComputerName $SQLServer | Where-Object {$_.Name -like "*SQL*"} | Format-List -Property Displayname, state, startName
Import-Module SQLPS -DisableNameChecking
$q1 = "select name as DatabaseName, state_desc as Status from sys.databases where state_desc <> 'online'"
$q2 = @"
CREATE TABLE #ErrorLog(
   LogDate DATETIME,
   ErrorSource NVARCHAR(MAX),
   ErrorMessage NVARCHAR(MAX)
)

INSERT INTO #ErrorLog(LogDate,ErrorSource,ErrorMessage)
       EXEC xp_readerrorlog 0,1, "Error"
select LogDate, ErrorMessage from #ErrorLog 
drop table #ErrorLog
"@
$q3 = "select auth_scheme from sys.dm_exec_connections where session_id=@@spid"

$r1 = Invoke-Sqlcmd -ServerInstance $SQLServer $q1
$r2 = Invoke-Sqlcmd -ServerInstance $SQLServer $q2
$r3 = Invoke-Sqlcmd -ServerInstance $SQLServer $q3

write-host 'Database status'
Write-host '-----------------'
Write-host " "
Write-host $r1.itemArray
Write-host '-----------------'
write-host 'ErrorLog:-'
Write-host '-----------------'
Write-host " "
Write-host $r2.itemArray
Write-host '-----------------'
Write-host " "
Write-host 'Authentication Type'
Write-host '-----------------'
Write-Host $r3.itemArray

Sample Output:

Powershell to Check SQL Database Status — SQL Service Status

Note: You can run the script in PowerShell ISE or download it from ValidateSQLServerThe output will be in console. I have written this script keeping it simple and have not used advanced modules so that you don’t have to install any modules. You just need SQLPS module.
Hope this will help in saving lot of your time in validating SQL Servers after any maintenance. You may also like Powershell Script to find SQL Instance Name for a Remote Server

Please spread the word:

https://sqlgeekspro.com/powershell-script-validate-sql-server-remotely/

Tagged as: PowerShell, Validate SQL Server

{ 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

Please spread the word:

https://sqlgeekspro.com/start_sql_without_local_admin/

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.

Please spread the word:

https://sqlgeekspro.com/sql-server-startup-parameter/

Tagged as: SQL Server Startup Parameter, Startup Parameter

{ Add a Comment }

PowerShell Script to find SQL Instances on remote server

by Dev on

This article is about a PowerShell script to find sql instances on remote server. Not to mention the remote server can be standalone or Cluster.
When I got the requirement to develop the script, I thought it is not a big deal. Generally speaking, PowerShell will have some direct commands to do so and my script is few searches away.
But when I put my feet in, I got to know it was not as easy as I thought.
Especially the real challenge comes in when you have to list the SQL Instances for a SQL Cluster. The script should be intelligent enough to find out if the server is cluster or standalone and accordingly list the SQL Instances.

Note:The script should run from a server where FailOver Clustering Feature is installed.

PowerShell script to find sql instance name on remote server

Param(
    [Parameter(Mandatory=$true)]
     $ComputerName
)

if ((Get-WMIObject -Class MSCluster_ResourceGroup -ComputerName $ComputerName -Namespace root\mscluster -ErrorAction SilentlyContinue) -ne $null)
 {  
    Import-Module FailoverClusters
    get-clusterresource -Cluster $ComputerName  -ErrorAction SilentlyContinue|
    where-object {$_.ResourceType -like “SQL Server”} | 
    get-clusterparameter VirtualServerName,InstanceName | group-object ClusterObject | 
    select-object @{Name = “SQLInstance”;Expression = {[string]::join(“\”,($_.Group | select-object -expandproperty Value))}} 
    } 
else {
$SQLInstances = Invoke-Command -ComputerName $ComputerName {
(Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
}
    foreach ($sql in $SQLInstances) {
       [PSCustomObject]@{
           ServerName = $sql.PSComputerName
           InstanceName = $sql
       }
   }  
   }

Not to mention, you can execute it from PowerShell ISE or from PowerShell Command prompt. Let’s see both examples.

Execute The Script Through Windows PowerShell ISE:

PowerShell Script to find SQL Instances on remote server Once you execute the script you’ll be prompted to provide the Server Name as you can see in the last line in above screen shot.
Sample OutPut:

Execute the Script from Windows PowerShell Command Prompt:

Only difference is to provide the server name while running the script as shown in the screen shot. Also the SQL Instance “PC01” is a cluster. Hence you don’t have to bother if the server is standalone or cluster. Hope this helps.
Let me know if you have any questions or comments.

Please spread the word:

https://sqlgeekspro.com/powershell-script-find-sql-instances-remote-server/

Tagged as: PowerShell, SQL Instance

{ 2 Comments }

Script To List SQL Agent Jobs and Schedules

by Dev on

This article is to keep a handy script to list SQL Agent Jobs and Schedules. In the previous article I had provided a script to change the job schedule. This script may be very next one you need.

Once we changed the job schedules in all SQL Servers in the environment, we needed a script to verify. There are many scripts available in the net. Hence I kind of tweaked one of the script. It’s been long time and hence not able to provide the original source.

Note:Please change the Job Names in the Where clause of the query to verify the job schedule of the jobs you made changes to.

Query to get list of jobs in sql server with schedules

SELECT	 [JobName] = [jobs].[name]
		,[Enabled] = CASE [jobs].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
		,[Scheduled] = CASE [schedule].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
		,[Occurs] = 
				CASE [schedule].[freq_type]
					WHEN   1 THEN 'Once'
					WHEN   4 THEN 'Daily'
					WHEN   8 THEN 'Weekly'
					WHEN  16 THEN 'Monthly'
					WHEN  32 THEN 'Monthly relative'
					WHEN  64 THEN 'When SQL Server Agent starts'
					WHEN 128 THEN 'Start whenever the CPU(s) become idle' 
					ELSE ''
				END
		,[Occurs_detail] = 
				CASE [schedule].[freq_type]
					WHEN   1 THEN 'O'
					WHEN   4 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' day(s)'
					WHEN   8 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' weeks(s) on ' + 
						LEFT(
							CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN 'Sunday, '    ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN 'Monday, '    ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN 'Tuesday, '   ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN 'Wednesday, ' ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, '  ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, '    ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, '  ELSE '' END , 
							LEN(
								CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN 'Sunday, '    ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN 'Monday, '    ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN 'Tuesday, '   ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN 'Wednesday, ' ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, '  ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, '    ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, '  ELSE '' END 
							) - 1
						)
					WHEN  16 THEN 'Day ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)'
					WHEN  32 THEN 'The ' + 
							CASE [schedule].[freq_relative_interval]
								WHEN  1 THEN 'First'
								WHEN  2 THEN 'Second'
								WHEN  4 THEN 'Third'
								WHEN  8 THEN 'Fourth'
								WHEN 16 THEN 'Last' 
							END +
							CASE [schedule].[freq_interval]
								WHEN  1 THEN ' Sunday'
								WHEN  2 THEN ' Monday'
								WHEN  3 THEN ' Tuesday'
								WHEN  4 THEN ' Wednesday'
								WHEN  5 THEN ' Thursday'
								WHEN  6 THEN ' Friday'
								WHEN  7 THEN ' Saturday'
								WHEN  8 THEN ' Day'
								WHEN  9 THEN ' Weekday'
								WHEN 10 THEN ' Weekend Day' 
							END + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)' 
					ELSE ''
				END
		,[Frequency] = 
				CASE [schedule].[freq_subday_type]
					WHEN 1 THEN 'Occurs once at ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':')
					WHEN 2 THEN 'Occurs every ' + 
								CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Seconds(s) between ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
					WHEN 4 THEN 'Occurs every ' + 
								CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Minute(s) between ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
					WHEN 8 THEN 'Occurs every ' + 
								CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Hour(s) between ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
					ELSE ''
				END
FROM	 [msdb].[dbo].[sysjobs] AS [jobs] WITh(NOLOCK) 
		 LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] AS [jobschedule] WITh(NOLOCK) 
				 ON [jobs].[job_id] = [jobschedule].[job_id] 
		 LEFT OUTER JOIN [msdb].[dbo].[sysschedules] AS [schedule] WITh(NOLOCK) 
				 ON [jobschedule].[schedule_id] = [schedule].[schedule_id] 
		where [jobs].[name] in ('1_DBA job Name', '11_DBA job Name') -- Change the job names or add/remove as applicable
GO

Again please change the values for Where clause. I know it is a long query hence mentioning it again.

The OutPut of the above query

List SQL Agent Jobs And Schedules
The query provides details on how the jobs are scheduled. If you want to to list the schedules of all the SQL Agent Jobs then just remove the where clause.

Please spread the word:

https://sqlgeekspro.com/list-sql-agent-jobs-schedules/

Tagged as: SQL Server Agent, T-SQL Script

{ Add a Comment }