Search results for: "rename sql agent"

Script to Rename SQL Agent Jobs

by Dev on

This article is about a script to Rename SQL Agent Jobs. First of all let me tell you the requirement.

The environment had a dedicated database for DBA’s which is used for several database maintenance and hence had a bunch of SQL Agent jobs associated. I am sure some of you also have similar setup.

Project team came up with an updated version. As a result in the upgrade process we had to rename all the jobs. I am sure you don’t wanna do it manually for each instance. Especially if you have more than 100 instances 🙂

The below queries will help in following conditions:
  • Rename SQL Agent Jobs having particular naming convention
  • Change the name of SQL Agent Jobs by removing first few characters

Note: The queries can not be fully generic and needs to be customized as per your environment. Basically depends upon the job name.

Query to Rename SQL Agent Jobs having particular naming conventions:

The below example shows job names having names like ‘1_DBA job Name’, ‘2_DBA job Name’, to ‘9_DBA job Name’. Please make the necessary changes as per your environment as provided in the comments.

Script to rename SQL Agent Jobs

DECLARE @SQLStatement VARCHAR(200)
DECLARE @jobName SYSNAME
   DECLARE c1 CURSOR FOR
   SELECT name FROM msdb.dbo.sysjobs_view WHERE name LIKE '%_DBA%'  --Change the LIKE operator
   OPEN c1
   FETCH NEXT FROM c1 INTO @jobName
     IF @@CURSOR_ROWS=0
     PRINT 'No Job found! Please re-check LIKE operator.'
     WHILE @@fetch_status=0
    BEGIN
       SET @SQLStatement= 'EXEC msdb.dbo.sp_update_job @job_name =''' + @jobName + ''', @new_name =''OLD_' +@jobName +''''
       PRINT(@SQLStatement)
     --EXEC (@SQLStatement)   --Uncomment to Execute
       FETCH NEXT FROM c1 INTO @jobName
    END
   CLOSE c1
   DEALLOCATE c1
OutPut will produce set of statements:

Rename SQL Agent Jobs

The result set is for you to verify the way you want to rename the jobs. Once verified either you execute the statements  Or you can uncomment the statement EXEC (@SQLStatement) in the above code and execute.

Screenshot showing the renamed jobs:

Query to rename SQL Agent jobs

Rollback query for the above changes:

The same script can be taken as reference to rename SQL Agent Jobs by removing first few characters.

DECLARE @SQLStatement VARCHAR(200)
DECLARE @jobName SYSNAME
DECLARE @jobName1 SYSNAME
   DECLARE c1 CURSOR FOR
   SELECT name FROM msdb.dbo.sysjobs_view WHERE name LIKE 'OLD_%' 
   OPEN c1
   FETCH next FROM c1 INTO @jobName
   IF @@CURSOR_ROWS=0
     PRINT 'No Job found! Please re-check LIKE operator.'
   WHILE @@fetch_status=0
   BEGIN
       
       SELECT @jobName1 = SUBSTRING(@jobName,5,LEN(@jobName)) --Second parameter may need change as per the job name
       SET @SQLStatement= 'EXEC msdb.dbo.sp_update_job @job_name =''' + @jobName + ''', @new_name =''' +@jobName1 +''''
       --PRINT(@SQLStatement)
       EXEC (@SQLStatement)
           FETCH NEXT FROM c1 INTO @jobName
   END
   CLOSE c1
   DEALLOCATE c1

Hope this is helpful. Furthermore you are most welcome to tweak the queries as per your requirement. I am open to feedback therefore please feel free to comment.
In addition you may also like the below queries:

List all permissions for a user in all or selective databases
Script to List All Users with db_owner role in SQL 2000
Script to find the SQL Agent Job Name in Execution
Script to retrieve database backup information
Script to get SQL Cluster Failover Time and Node Name

{ 2 Comments }

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

 

{ Add a Comment }

Script to Delete SQL Agent Jobs Continues

by Dev on

<< Previous
This article is in continuation of Script to Delete SQL Agent Jobs. If you have landed here directly you might want to check the above article if the first script helps.
Anyways in this continuation article I am going to provide you the script to Delete SQL Agent Jobs in a scenario when you need to delete all the jobs except the default one “syspolicy_purge_history”.

Script to Delete All SQL Agent Jobs except the default one:
DECLARE @SQLStatement VARCHAR(200)
DECLARE @jobName SYSNAME
   DECLARE c1 CURSOR FOR
   SELECT name FROM msdb.dbo.sysjobs_view WHERE name <> 'syspolicy_purge_history' 
   OPEN c1
   FETCH NEXT FROM c1 INTO @jobName
     IF @@CURSOR_ROWS=0
     PRINT 'No Job found! Please re-check LIKE operator.'
     WHILE @@fetch_status=0
    BEGIN
       SET @SQLStatement= 'EXEC msdb.dbo.sp_delete_job @job_name =''' + @jobName + ''', @delete_unused_schedule=1' 
       PRINT(@SQLStatement)
     --EXEC (@SQLStatement)   --Uncomment to Execute
       FETCH NEXT FROM c1 INTO @jobName
    END
   CLOSE c1
   DEALLOCATE c1

The result set will provide you with the SQL delete statements if you run the above query as is. All you need to do is copy the output and  execute them in the SSMS.

Sample result set as shown in the following screenshot:
Delete SQL Agent Jobs
Delete SQL Agent Jobs

There is another way to execute the query. You can directly delete the jobs without generating the delete statements. If you have little T-SQL knowledge you can modify the same query to do it. If you are an accidental DBA, no worries just remove the -- sign from the statement EXEC (@SQLStatement) and comment the statement PRINT(@SQLStatement). I have explained this already in the previous article so if you can refer that.

Now let’s talk about a scenario when you need to delete all sql agent jobs except the replication, mirroring, log shipping or except few jobs.
Yes, you got it right. All you need to do is modify the where condition. Let me show you an example of Deleting All SQL Agents Jobs except the replication ones. The below query shows example of jobs related to transaction replication and the default one “syspolicy_purge_history".

Script To Delete All SQL Agent Jobs Except Few:
DECLARE @SQLStatement VARCHAR(200)
DECLARE @jobName SYSNAME
   DECLARE c1 CURSOR FOR
   SELECT name FROM msdb.dbo.sysjobs_view WHERE name not in
('syspolicy_purge_history' 
,'Agent history clean up: distribution' 
,'Distribution clean up: distribution' 
,'Expired subscription clean up' 
,'INDSBLRHT0VR72-AdventureWorks2017-2' --Change the job name as per your environment
,'INDSBLRHT0VR72-AdventureWorks2017-Adven_Pub-2' --Change the job name as per your environment
,'INDSBLRHT0VR72-AdventureWorks2017-Adven_Pub-INDSBLRHT0VR72-5' --Change the job name as per your environment
,'Reinitialize subscriptions having data validation failures' 
,'Replication agents checkup' 
,'Replication monitoring refresher for distribution' 
)

   OPEN c1
   FETCH NEXT FROM c1 INTO @jobName
     IF @@CURSOR_ROWS=0
     PRINT 'No Job found! Please re-check LIKE operator.'
     WHILE @@fetch_status=0
    BEGIN
       SET @SQLStatement= 'EXEC msdb.dbo.sp_delete_job @job_name =''' + @jobName + ''', @delete_unused_schedule=1' 
       PRINT(@SQLStatement)
     --EXEC (@SQLStatement)   --Uncomment to Execute
       FETCH NEXT FROM c1 INTO @jobName
    END
   CLOSE c1
   DEALLOCATE c1

Another way of achieving the same end result is using the first query and filter out the jobs from the result set.
Hope this helps. Feel free to leave your comments.
You also might find the below scripts useful:

Query To Retrieve All Permissions for a user in Selective databases
Script to Rename SQL Agent Jobs
Script to get SQL Cluster Failover Time and Node Name
Script to find the SQL Agent Job Name in Execution
List all permissions for a user in all or selective databases

Tagged as: ,

{ Add a Comment }

Script to Delete SQL Agent Jobs

by Dev on

This article is about a simple script to delete SQL Agent Jobs. Recently I had faced a situation when I had to remove more than 100 SQL Agent jobs.

First of all let me tell you that there may be different scenarios when you may need to delete all SQL Agent jobs, few of them or all except few required jobs etc. I have tried to include couple of queries in this article.

I had blog about renaming several SQL Agent jobs. All I have done is little modification in the query.

Script To Delete SQL Agent Jobs Having Particular Naming Convention
DECLARE @SQLStatement VARCHAR(200)
DECLARE @jobName SYSNAME
   DECLARE c1 CURSOR FOR
   SELECT name FROM msdb.dbo.sysjobs_view WHERE name LIKE '%DBA%' --Change the LIKE Operator as applicable
   OPEN c1
   FETCH NEXT FROM c1 INTO @jobName
     IF @@CURSOR_ROWS=0
     PRINT 'No Job found! Please re-check LIKE operator.'
     WHILE @@fetch_status=0
    BEGIN
       SET @SQLStatement= 'EXEC msdb.dbo.sp_delete_job @job_name =''' + @jobName + ''', @delete_unused_schedule=1' 
       PRINT(@SQLStatement)
     --EXEC (@SQLStatement)   --Uncomment to Execute
       FETCH NEXT FROM c1 INTO @jobName
    END
   CLOSE c1
   DEALLOCATE c1

 

The following screenshot shows SQL Agent Jobs having naming convention like ‘1_DBA job Name’, ‘2_DBA job Name’ and so on. This is just for example, you may have different names in your environment.

Script to rename SQL Agent Jobs

Besides that there  is another way you can use this query. The result set will provide you with the SQL delete statements if you run the above query as is. Therefore all you need to do is copy the output and  execute them in the SSMS.

The following screen shot shows the result set:

Script to Delete SQL Agent Jobs
Now lets talk about the other way of using the query. If you uncomment the statement EXEC (@SQLStatement) and comment the PRINT(@SQLStatement) statement and then execute the query it will directly delete the jobs.

The output will not provide any sql statements as shown in the following screen shot. Therefore you should run the query this way only if you are pretty sure what you are doing.

Script to Delete SQL Agent Jobs
Script to Delete SQL Agent Jobs

Now lets see another scenario like when you need to delete all the jobs except the default one ‘syspolicy_purge_history’ job Click Next Page >>

Tagged as:

{ Add a Comment }

Script To Provide Database Restore Report

by Dev on

This article is about a script to provide database restore report for all or selective databases.

How many of you got requests to restore database from one environment to other? Yeah, I know it is one of those common activities in day to day life of a DBA. If I ask how many of you had to provide a restore report of a single database? There will be many of you have an assertive answer.

But one of the not so common request is to provide restore report of all databases. Here I am going to provide the queries to pull restore report of all and selective databases. First of all lets see the restore report of all the databases.

Furthermore the result set is more useful if you get the following fields: Source Instance, Destination Instance, Source Database Name, Destination Database Name, Restore Type, Restore Date, the login id/user name who performed the restore and the backup file name used in the restore.

Note: I have commented out few fields in the query which are not so important in all situations. You may need to uncomment them as per your requirement.

Query to provide database restore report of all databases
SELECT mb.server_name AS SourceSQLInstance, @@servername AS DestinationSQLInstance, 
mb.database_name AS SourceDatabaseName,
rh.destination_database_name AS DestinationDatabaseName, rh.restore_date AS RestoreDate, 
CASE WHEN rh.restore_type='D' THEN 'FULL'
     WHEN rh.restore_type='L' THEN 'Log'
     WHEN rh.restore_type='I' THEN 'Differential'
END AS RestoreType,
--sd.collation_name,
--sd.compatibility_level,
--sd.recovery_model_desc, 
rh.[user_name], bf.physical_device_name AS BackupFileName
FROM msdb..[restorehistory] rh
INNER JOIN master.sys.databases sd
ON rh.destination_database_name = sd.name
INNER JOIN msdb..backupset mb
ON rh.backup_set_id=mb.backup_set_id
INNER JOIN msdb..backupmediafamily bf
ON mb.backup_set_id=bf.media_set_id
ORDER BY restore_date DESC
The sample result set:Script to provide database restore report

Most noteworthy field in the above result set is backup file name. The complete path with the file name shown is the location where backup was taken and not the path from where the restore files was picked. In other words if you take a backup on X location and then copy the backup file to Y location and restore process picked the file from Y location the result set will show the X location. Hope I am clear enough.

Query to provide database restore report of single or few databases
SELECT mb.server_name AS SourceSQLInstance, @@servername AS DestinationSQLInstance, 
mb.database_name AS SourceDatabaseName,
rh.destination_database_name AS DestinationDatabaseName, rh.restore_date AS RestoreDate, 
CASE WHEN rh.restore_type='D' THEN 'FULL'
     WHEN rh.restore_type='L' THEN 'Log'
     WHEN rh.restore_type='I' THEN 'Differential'
END AS RestoreType,
--sd.collation_name,
--sd.compatibility_level,
--sd.recovery_model_desc, 
rh.[user_name], bf.physical_device_name AS BackupFileName
FROM msdb..[restorehistory] rh
INNER JOIN master.sys.databases sd
ON rh.destination_database_name = sd.name
INNER JOIN msdb..backupset mb
ON rh.backup_set_id=mb.backup_set_id
INNER JOIN msdb..backupmediafamily bf
ON mb.backup_set_id=bf.media_set_id
WHERE rh.destination_database_name IN ('Test_4','Test_restore')  --Put the database names
ORDER BY restore_date DESC
Result Set:
Script To Provide Database Restore Report

Hope this is helpful. Please feel free to let me know your thoughts by leaving your comments below.
You may like the below articles as well:

Script to List All Users with db_owner role in SQL 2000
List all permissions for a user in all or selective databases
Script to Rename SQL Agent Jobs
Script to find the SQL Agent Job Name in Execution
List of Users with db_owner Role in All Databases

Tagged as:

{ Add a Comment }