Procedures / Commands used in Sql Server Jobs

It is very difficult to find a procedures / Commands used in SQL Server Jobs from the huge cluster of Jobs through SQL Server Management Studio. You can get a list of all the Procedures / commands that are used in Sql Server Jobs.


USE msdb
GO
SELECT sj.name AS job_name, st.command
FROM sysjobs sj
JOIN sysjobsteps st
ON sj.job_id = st.job_id

You can use a where clause to get to know about procedures / command used in a particular jobs:

where st.command like '%Proc_toFind%'  

Changing the Default Database

By default SQL Server loads master databse.
Sometimes it is required to change the default database in SQL Server. Use below query to chage the default database:

ALTER LOGIN [ArunL\Arun] with default_database = DemoData

Find database file size

Use below query to get the database file size. I have used system view “sysaltfiles” from “master” database which keeps information about each and every database file available in current SQL Server instance. 


SELECT DB_NAME(dbid) AS DatabaseName, 
Name AS LogicalFileName, 
CASE WHEN GroupID=1 THEN 'Data' ELSE 'Log' END AS FileType, 
FileName AS FilePath, size AS TotalPage, 
(Size*8192E)/1048576 AS FileSizeInMB
FROM master..SysAltFiles

Open failed. Could not open file mdf for file number 2. OS error 5(Access denied)

Today my one client told me that their database could not access in yesterday, we try to use the database and got message error 

the database could not access due to disk space or insufficient memory

First I checked the disk space and found that there is ample disk space.

Then I checked SQL Server error log, It shows the below error:

FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file DATABASE_log.ldf ‘. Diagnose and correct the operating system error, and retry the operation.

Generally an OS error 5 means there is no permission to modify a particular file. Than I suspect that the account that is running SQL Server Service does not have permissions to modify the LDF and MDF files.

So, then I checked the write permission to the file by right click on the files (both mdf and LDF) and select properties and than from security TAB checked the write permission to these files. I set the write permission. 

Now problem solved.

Swap data between columns

Sometime we may mistakenly insert data into a wrong column if we have two identical columns with same data type. To correct this we have to delete the data from the wrong column and insert that in the proper column. Here we had to do lot of work. Swapping can be done :
                              
1.with the help of a temporary variable .
   or
2.directly.

Suppose we have a table named Customer with 3 columns: CustomerID, FName, LName.
       
              CustomerID: int 
              FName: varchar(35)
              LName: varchar(35)

and we have inserted some rows to the table.

Insert into Customer values(1,'Ladha','Arun')
Insert into Customer values(2,'Daga','Ashit')
Insert into Customer values(3,'Shah','Nishi')
Insert into Customer values(4,'Singh','Ayan')

Here I had inserted FName data in LName and LName data into FName.
Now, I want to swap them.

1. Swap with the help of temporary variable:
-------------------------------------------------
DECLARE @temp AS varchar(10)

UPDATE Customer SET @temp=LName, LName=FName, FName=@temp

  The resulting  table can be verified.

2.directly:
------------
UPDATE Customer SET LName = FName, FName = LName

This query can also swap the column data.