Script to get all the jobs in SQL Server from SQL Server Management Studio

This is very interesting fact that people always look for shortcuts especially while accomplishing the big tasks. Here i have came up with a scenario: I have got a task to setup a new server B (lets assume) exactly as a replica of Existing Server A.

It involve a lot of steps in which there is a step to copy all the EXISTING JOBS FROM SERVER A to SERVER B (Please note i have written All the Jobs).
 Here is a easiest way i have found which might be helpful for many people.
Get the list current Jobs in SQL Server:

SELECT js.server,js.database_name,js.database_user_name,j.enabled,j.name, js.step_name, js.command, j.date_created, j.date_modified,  js.last_run_date,js.last_run_duration,js.last_run_outcome  FROM msdb.dbo.sysjobsteps js WITH(NOLOCK) LEFT JOIN msdb.dbo.sysjobs j WITH(NOLOCK) ON j.job_id = js.job_id   order by name Output: server database_name database_user_name enabled name step_name command date_created date_modified last_run_date last_run_duration last_run_outcome AUZUDC6DD SCDistribution NULL 1 Agent history clean up: SCDistribution Run agent. EXEC dbo.sp_MShistory_cleanup @history_retention = 48 2012-11-21 14:29:27.410 2012-11-21 14:29:27.417 20140123 0 1

Step1: Enable Object explorer detail.
Step2:In SQL Server 2005 or earlier:  goto view->Summary
In SQL Server 2008 or older:  goto view-> Object Explorer Details
Step3:
Expand SQL Server Agent and click on Jobs.
All the jobs will be visible in summary/Object Explorer Detail window. (See Screenshot).

Step4:Here you can select all the jobs or the desired jobs which you want to script out.
After select right click -> Script Job As -> Create To -> and select the desired location where you want to script all the selected jobs (In the screenshot i have selected New Query Editor Window).

Note: The above screenshots/Examples are of SQL Server 2008R2 you can follow the same steps with SQL Server 2005 or earlier versions

SQL Server Data Management Report - Get Drive Size and Drive Free Space

One of the regular DBA practice is to maintain the free space on Drives where the data files are located.

Here i have created a script (Space Management) which gives us the database files, its location,size, growth rate, the drive where its located, drive size, drive free space etc...

Most of the thing is very easy to retrieve through SQL Queries as those are maintained in tables under msdb databases, but the free space and size of drive is something which require system interaction using master..xp_cmdshell and that part of query is a bit complex :

Step1: Enable sp_configure 

-- -- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO -- To update the currently configured value for advanced options. RECONFIGURE GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1 GO -- To update the currently configured value for this feature. RECONFIGURE GO

Step2: Excute the following script

declare @Drive table(DriveName char,freespace decimal(9,2), totalspace decimal(9,2),percentfree decimal(9,2)) BEGIN      SET NOCOUNT ON;     DECLARE @v_cmd nvarchar(255)     ,@v_drive char(99)     ,@v_sql nvarchar(255)     ,@i int     SELECT @v_cmd = 'fsutil volume diskfree %d%'     SET @i = 1     -- Creating temporary tables to retrive system space information.     CREATE TABLE #drives(iddrive smallint ,drive char(99))     CREATE TABLE #temp1(drive char(99),shellCmd nvarchar(500));     CREATE TABLE #results(drive char(99),freespace decimal(9,2), totalspace decimal(9,2));     INSERT #drives (drive)      EXEC master..xp_cmdshell 'mountvol'     DELETE #drives WHERE drive not like '%:\%' or drive is null     WHILE (@i <= (SELECT count(drive) FROM #drives))     BEGIN          UPDATE #drives           SET iddrive=@i          WHERE drive = (SELECT TOP 1 drive FROM #drives WHERE iddrive IS NULL)          SELECT @v_sql = REPLACE(@v_cmd,'%d%',LTRIM(RTRIM(drive))) from #drives where iddrive=@i          INSERT #temp1(shellCmd)           EXEC master..xp_cmdshell @v_sql          UPDATE #temp1           SET #temp1.drive = d.drive          FROM #drives d          WHERE #temp1.drive IS NULL and iddrive=@i         SET @i = @i + 1     END         INSERT INTO #results         SELECT bb.drive         ,CAST(CAST(REPLACE(REPLACE(SUBSTRING(shellCmd,CHARINDEX(':',shellCmd)+1,LEN(shellCmd)),SPACE(1),SPACE(0))         ,char(13),SPACE(0)) AS NUMERIC(32,2))/1024/1024/1024 AS DECIMAL(9,2)) as freespace         ,tt.titi as total         FROM #temp1 bb         JOIN (SELECT drive         ,CAST(CAST(REPLACE(REPLACE(SUBSTRING(shellCmd,CHARINDEX(':',shellCmd)+1,LEN(shellCmd)),SPACE(1),SPACE(0))         ,char(13),SPACE(0)) AS NUMERIC(32,2))/1024/1024/1024 AS DECIMAL(9,2)) as titi         FROM #temp1         WHERE drive IS NOT NULL         AND shellCmd NOT LIKE '%free bytes%') tt         ON bb.drive = tt.drive         WHERE bb.drive IS NOT NULL         AND bb.shellCmd NOT LIKE '%avail free bytes%'         AND bb.shellCmd LIKE '%free bytes%';     -- getting all system specific formatted data into @Drive table     INSERT INTO @Drive     SELECT LEFT(RTRIM(LTRIM(drive)),1) as drive      ,freespace      ,totalspace      ,CAST((freespace/totalspace * 100) AS DECIMAL(5,2)) as [percent free]     FROM #results     ORDER BY drive     -- Dropping temporary tables     DROP TABLE #drives     DROP TABLE #temp1     DROP TABLE #results     SELECT           @@Servername as Server,          sd.name as Database_name,         mas.name as File_Name,          mas.size * 8 / 1024 File_Size_MB,         CASE [is_percent_growth]                   WHEN 1 THEN CAST(mas.growth AS varchar(20)) + '%'                   ELSE CAST(mas.growth*8/1024 AS varchar(20)) + ' MB'           END as Growth_rate,               drv.DriveName as Drive_Name,          drv.FreeSpace,         drv.totalspace,         CASE type                WHEN 0 THEN 'Data'                   WHEN 1 THEN 'Log'           END as File_type,           mas.physical_name as File_Location      FROM sys.master_files mas     LEFT JOIN @Drive drv ON LEFT(mas.physical_name, 1) = drv.DriveName     LEFT JOIN sys.databases sd ON mas.database_id = sd.database_id     --WHERE sd.name IN ('db1','db2','db3') -- Specify the database name if this report is needed for specific databases     --AND mas.growth <>0 -- Condition to retrive the record for growing files only. END

• You can restrict the output for some of the databases by providing the database name which is commented by default.

             --WHERE sd.name IN ('db1','db2','db3')  

•  You can also restrict the output for growing files of the databases only by providing the growth <>0 which is commented by default.

             --AND mas.growth <>0

There are further filters also possible like if someone want to restrict the query upto a size limit of files etc. simply write it down in where clause.


Output:

Output should be like this:

This could be very useful where someone have multiple servers and DBA has to generate weekly/Monthly report for all of the server, in such scenario one can create a SSIS package and execute this query on all the server and generate a single report for all the servers.

Hope this will be helpful for people out there :) let me know if anyone has any issue.