Script: view information of currently running SQL Server Jobs

Below script returns five (5) columns result set as output. You can further enhance this query to accommodate your requirement.

1. Job_Name: This is job’s registered name on the server’s instance.
2. Last_Executed_Step_ID: This is the ID of the last executed step in the job. E.g. if a job has 4 steps, and currently it is running 3^rd step, so last executed step will be 2 and it will be populated with 2 against Job’s 2^nd step row, while all other steps rows will have 0.
3. Step_ID: This query returns all steps included in the job along with their id, while 2^nd column, returns id same as in this column.
4. Step_Name: This reflects job step name that was given during job creation.
5. Start_Execution_Date: This column returns execution date and time of this job. So you can get an idea when this started execution.

SELECT 
LTRIM(RTRIM(CONVERT(VARCHAR(100),j.name))) job_name

,ISNULL(a.last_executed_step_id,'') last_executed_step_id

,s.step_id

,RTRIM(CONVERT(VARCHAR(100),s.step_name))step_name

,a.start_execution_Date

FROM msdb.dbo.sysjobs j WITH (NOLOCK)

INNER JOIN msdb.dbo.sysjobsteps s WITH (NOLOCK) ON j.job_id = s.job_id

LEFT OUTER JOIN msdb.dbo.sysjobactivity a WITH (NOLOCK) ON j.job_id=a.job_id

AND (s.step_id=a.last_executed_step_id OR a.last_executed_Step_id IS NULL)

AND run_requested_date>=CAST(CONVERT(VARCHAR,GETDATE(),101) AS DATETIME)

AND stop_execution_Date IS NULL

WHERE j.enabled=1

AND j.category_id IN (0,1,2,3,4,5,98,99)

AND j.job_id in 

(SELECT job_id FROM msdb.dbo.sysjobactivity WITH (NOLOCK)

WHERE start_execution_date>= CAST(CONVERT(VARCHAR,GETDATE(),101) AS DATETIME) 

AND stop_execution_date IS NULL )

ORDER BY 1,3

Compound Operators - SQL SERVER 2008/R2

You are aware about arithmetic, bitwise and assignment operators, don’t worry if you are not, see below you will get an idea about them.

Arithmetic Operators: There are 5 arithmetic operators that are Plus (+), Minus (-), Divide (/), Multiply (*) and Modulo (%)

Bitwise Operators: There are three bitwise operators that are Bitwise AND (&), Bitwise OR (|) and Bitwise Exclusive OR (^).

Assignment Operators: There is only one assignment operator Equal to (=)

Example to use them (in Older version):

-- Arithmetic

DECLARE @var INT

SET @var = 1 -- Assignment

SET @var = @var + 2 -- Increment and them assignment

SELECT @var

SET @var = @var - 1 -- decrement and them assignment

SELECT @var

SET @var = @var & 3 -- Bitwise AND and then assignment

SELECT @var

SET @var = @var | 3 -- Bitwise AND and then assignment

SELECT @var

You can see above old method for increment or decrement and then assignment by using Plus (+) and Minus (-) operators. You can use other operators as well in the same way. But this was an old way for of manipulation and then result assignment. We will see enhancement in operator utilization from SQL Server 2008 that is Compound Operators.

Compound Operators:

These operators defined by combining one operator from Arithmetic/Bitwise and 2^nd operator from Assignment. It first performs associated operation on left operand with right operand and then saves result to left operand.

These are 8 in numbers.

1. += (Add EQUALS), you can use it for string concatenation as well.
2. -= (Minus EQUALS)
3. *= (Multiply EQUALS)
4. /= (Divide EQUALS)
5. %= (Modulo EQUALS)
6. &= (Bitwise AND EQUALS)
7. |= (Bitwise OR EQUALS)
8. ^= (Bitwise Exclusive OR EQUALS)

Example:

Compare this example with above one:

DECLARE @var INT

SET @var = 1 -- Assignment

SET @var += 2 -- Increment and them assignment

SELECT @var

SET @var -= 1 -- Increment and them assignment

SELECT @var

SET @var &= 3 -- Bitwise AND and then assignment

SELECT @var

SET @var |= 3 -- Bitwise AND and then assignment

SELECT @var

Enhanced VALUES Clause (ROW Constructor) – SQL SERVER 2008 / R2

 Old Style:

If you see at previous versions of SQL Server (Older than 2008), you were able to insert only a single row by using values clause.

INSERT INTO dbo.mytable(id, val) VALUES(1, 'First Value');

Or

If you were inserting through SELECT statement, you were able to specify a single row values or by combining multiple rows by using UNION/UION ALL operator.

INSERT INTO dbo.mytable(id, val)

SELECT 1 AS ID, 'First Value' AS VAL

UNION ALL SELECT 2, 'Second Value'

UNION ALL SELECT 3, 'Thrid Value'

New Style:

Now, using SQL Server 2008 or Later, you can find enhanced version of VALUES clause that works as a collection of row(s). You can specify up to 1000 rows by using this clause. Further, it treats it a single transaction, in case of failure, all rows will be rolled back.

Try this example:

CREATE TABLE dbo.mytable

(ID INT,

 VAL VARCHAR(100)

 );

 -- Inertion by using Values clause

 INSERT INTO dbo.mytable (ID, VAL )

 VALUES (1, 'First Value')

 ,(2, 'Second Value')

 ,(3, 'Third Value')

-- Insertion by using Values clause with Select Statement

 INSERT INTO dbo.mytable (ID, VAL )

 SELECT *

 FROM (VALUES (1, 'First Value')

 ,(2, 'Second Value')

 ,(3, 'Third Value') ) as temp(ID, VAL)

Further, you can use VALUES clause to parse a string of multiple parameter values to get them in rows.

Try below script:

DECLARE @StrValues NVARCHAR(100)

DECLARE @Str NVARCHAR(500)

SET @StrValues = '(''A''),(''B''),(''C'')'

SET @Str = 'SELECT * FROM (VALUES ' + @StrValues + ' ) as Temp(Col1)'

EXEC SP_EXECUTESQL @Str

SQL Server 2008 SP2 Release (download link)

What's New:
15K partitioning Improvement. Introduced support for a maximum of 15,000 partitions in tables and indexes in Microsoft SQL Server 2008 Service Pack 2 in the Enterprise, Developer and Evaluation Editions.

Reporting Services in SharePoint Integrated Mode. SQL Server 2008 SP2 provides updates for Reporting Services integration with SharePoint products. SQL Server 2008 SP2 report servers can integrate with SharePoint 2010 products. SQL Server 2008 SP2 also provides a new add-in to support the integration of SQL Server 2008 R2 report servers with SharePoint 2007 products.

SQL Server 2008 R2 Application and Multi-Server Management Compatibility with SQL Server 2008.

SQL Server 2008 Instance Management.With SP2 applied, an instance of the SQL Server 2008 Database Engine can be enrolled with a SQL Server 2008 R2 Utility Control Point as a managed instance of SQL Server.

Data-tier Application (DAC) Support.Instances of the SQL Server 2008 Database Engine support all DAC operations delivered in SQL Server 2008 R2 after SP2 has been applied. You can deploy, upgrade, register, extract, and delete DACs. SP2 does not upgrade the SQL Server 2008 client tools to support DACs. You must use the SQL Server 2008 R2 client tools, such as SQL Server Management Studio, to perform DAC operations. A data-tier application is an entity that contains all of the database objects and instance objects used by an application. A DAC provides a single unit for authoring, deploying, and managing the data-tier objects.

Moving User Database Files in SQL Server 2000

0. Take Full backup before moving files

1. Set database in single user mode
use master
go

exec sp_dboption testdb2000,'single user','true'
go

2. Get path of database files

Exec sp_helpdb testdb2000
go

3. Save location of all files

C:\Program Files\Microsoft SQL Server\MSSQL$INS2000\data\TestDB2000_Data.MDF
C:\Program Files\Microsoft SQL Server\MSSQL$INS2000\data\TestDB2000_Log.LDF

4. De-attach this database

Exec sp_detach_db @dbname = 'testdb2000','true'
go

5. Copy all files available in step 2 and paste them to a new location
6. Rename them on their old location, just in case of any issue to revert back changes
7. After copying them to a new location, attach these files from new location to this database, you can attach upto 16 files through this procedure.

Exec sp_attach_db @dbname = 'testdb2000', @filename1 = 'd:\testdb2000\TestDB2000_Data.MDF', @filename2 = 'd:\testdb2000\TestDB2000_Log.LDF'
go

8. Connect to user database and verify changes, after verification, remove old file from old location