To check SQL Server Agent Job History you can use the Log File Viewer from SQL Server Management Studio.

To Open Log File Viewer,

1. Expand Server Node > 2. Expand SQL Server Agent > 3. Expand Jobs > 4. Right click on the Job and 5. Select "View History" as shown in the screen shot below:

 

Alternatively, you can also use below T-SQL code to check Job History:

SELECT      [JobName]   = JOB.name,

            [Step]      = HIST.step_id,

            [StepName]  = HIST.step_name,

            [Message]   = HIST.message,

            [Status]    = CASE WHEN HIST.run_status = 0 THEN 'Failed'

            WHEN HIST.run_status = 1 THEN 'Succeeded'

            WHEN HIST.run_status = 2 THEN 'Retry'

            WHEN HIST.run_status = 3 THEN 'Canceled'

            END,

            [RunDate]   = HIST.run_date,

            [RunTime]   = HIST.run_time,

            [Duration]  = HIST.run_duration

FROM        sysjobs JOB

INNER JOIN  sysjobhistory HIST ON HIST.job_id = JOB.job_id

/* WHERE    JOB.name = 'Job1' */

ORDER BY    HIST.run_date, HIST.run_time

Output:

JobName  Step StepName      Message       Status     RunDate   RunTime   Duration

Job1     1    Step1         Executed a..  Succeeded  20120416  173935    10

Job1     0    (Job outcome) The job su..  Succeeded  20120416  173935    10

Job2     1    Step1         Executed a..  Succeeded  20120416  174037    10

Job2     0    (Job outcome) The job su..  Succeeded  20120416  174037    10

 

Check dbo.sysjobs and dbo.sysjobhistory on BOL for more information.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe

EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Filed under: Catalog Views, Management Studio, SQL Agent, SQLServer

SQL Server stores all mails and attachments in msdb database. To avoid unnecessary growth of msdb database you should remove these mail history unless it is required for auditing or other purposes.

To check all mails processed by Database Mail, you can use sysmail_allitems catalog view:

SELECT      COUNT(*)

FROM        msdb.dbo.sysmail_allitems

Output:

———–

125

There are 3 siblings of this catalog view sysmail_faileditems, sysmail_unsentitems and sysmail_sentitems which shows mails of different status respectively.

If you are frequently sending larger attachments using database mail this can cause msdb to grow rapidly. All attachments stored in msdb database are available in sysmail_attachments.

To delete mail items you can use system stored procedure sysmail_delete_mailitems_sp, it has below syntax:

sysmail_delete_mailitems_sp [@sent_before] [@sent_status]

You can delete mail using either of the parameters, @sent_before deletes all mail that were sent before specified date, and @sent_status deletes all mails with specified status.

For example, to delete all mails which are sent and are older than current month we can use:

EXEC  msdb.dbo.sysmail_delete_mailitems_sp

      @sent_before = '2012-05-10 00:00:00',

      @sent_status = 'sent'

Output:

(100 row(s) affected)

You can query the sysmail_event_log view to check the deletions that has been initiated.

SELECT      description

FROM        sysmail_event_log

ORDER BY    log_date DESC

Output:

description

Mail items deletion is initiated by user “sa”. 100 items deleted.

DatabaseMail process is started

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Filed under: Catalog Views, Database Mail, SQLServer

Generally it is considered good practice using two-part names for objects. It make the code more readable and avoids confusion if objects with similar names exists in different schemas. Some features requires that two-part naming must be used such as creating a view WITH SCHEMABINDING.

However, you can also use a four-part name to refer objects. Four-part object reference format consists of:

<< Server.Database.Schema.Object >> 

Only object name is required to reference an object, (schema name when the object is not in user’s default schema) all else is optional.

For example, all statements below will work in SQL Server:

USE   msdb

 

/*    Four-part name */

SELECT Name FROM VGAJJAR2.msdb.dbo.backupset

 

/*    Server name omitted */

SELECT Name FROM msdb.dbo.backupset

 

/*    Server/database name omitted */

SELECT Name FROM dbo.backupset

 

/*    Server/database/schema name omitted,

      only default schema name can be omitted */

SELECT Name FROM backupset

 

 

/*    And this works too… */

SELECT Name FROM …backupset

SELECT Name FROM ..dbo.backupset

SELECT Name FROM .msdb.dbo.backupset

SELECT Name FROM .msdb..backupset

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Filed under: Catalog Views, SQLServer

When connecting to a server from SSMS using Windows Authentication, you are limited to using only user which you used to log on to Windows. For example, if you login using User1 on Windows and launch SSMS, you can only use User1 to connect to a server from SSMS as shown below, the User name box is grayed out:

To use a different user without logging of and logging in as different user on Windows, you can use RunAs command to launch an application under different user’s context.

The general syntax for RunAs is as follows:

RunAs /user:DOMAIN\UserName ProgramName

 

To Run SSMS as different Windows user:

1. Go to Start -> Run,

2. Type following command in text box and press enter, this will launch command prompt to get user’s password:

3. Provide user’s password and press enter:

Enter the password for VGAJJAR\User2:

 

This will launch SSMS under User2‘s context.

 

Using this method you can run multiple instances of SSMS side-by-side under different user contexts:

 

To get more information about RunAs parameters, type RunAs /? at command prompt.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Filed under: SQLServer

Yesterday, I received two mails from the Microsoft Certification Program Team about two new certification earned:

1. Microsoft Certified Solutions Associate – SQL Server 2008 Core

2. Microsoft Certified Solutions Associate – Windows Server® 2008

 

These also reflected on my transcript today. These two new certifications are awarded due to changes in Microsoft Certification Program.

I received MCSA – SQL Server 2008 Core as I already have 70-432 and 70-448.

Image Source & More Info: Microsoft SQL Server 2012 Certification

And, I received MCSA – Windows Server 2008 as I already have 70-640, 70-642 and 70-646.

Image Source & More Info: Microsoft Windows Server Certification Overview

 

Check the links above to learn more about new certification programs.

To learn more about SQL Server certification changes, check out this webcast by Kendra Little (b | t):

SQL Server 2012 Certification Changes

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Filed under: Certification, SQLServer

You can easily import data from an Excel file to SQL Server using SQL Server Import and Export Wizard. However, when the data is simple and limited I avoid using it (too lazy to click through 6 screens… ) instead I generate INSERT statements using CONCATENATE function in Excel and execute those. This is much faster than using Import/Export Wizard.

This approach also requires the destination table to be created manually or it should already exists. Here’s how I use it:

1. The destination table I am using already exists with below columns, and some data:

2. The source Excel file contains below data:

3. Now to convert this to INSERT statement, we need to add a few columns to Excel sheet which will contain these texts "INSERT INTO dbo.ProductList VALUES (' ", " '' "," ' " etc. as shown below:

Note: if single quote (') is the first character in the column, as in Column C; you will need to input two single quotes (''). Also, if table has additional columns which are not being imported then you will need to include column list with INSERT statement i.e. "INSERT INTO dbo.ProductList (Name) VALUES (' " if you are importing only Name column.

4. Then you need to concatenate these columns using Excel's CONCATENATE function:

5. Finally, copy the same formula to all rows, these are the INSERT statements you need!:

6. That's all folks, Now copy them to SSMS and execute them…

 

Hope This Helps!

Facebook Page -> SqlAndMe

Filed under: SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Working With Data

This is a follow up post from a comment on my blog.

Earlier I posted about using xp_instance_regread to get default data location for current instance. It translates a given registry path to instance specific registry path. This can return the value for a single instance only. However, if you need to locate default data directories for all available instances on a machine, you need to use xp_regread, which reads from an absolute registry path.

Registry path for default data location is same for different SQL Server versions:

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\MSSQLServer

 

So the first thing we need before we can start reading data location from registry is list of instance name; these also can be read from registry using xp_instance_regenumvalues.

Registry path for instance name is as follows:

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL

We need to retrieve these to a temporary structure so that we can use it further:

CREATE TABLE #tempInstanceNames

(

      InstanceName      NVARCHAR(100),

      RegPath           NVARCHAR(100),

      DefaultDataPath   NVARCHAR(MAX)

)

 

INSERT INTO #tempInstanceNames (InstanceName, RegPath)

EXEC   master..xp_instance_regenumvalues

       @rootkey = N'HKEY_LOCAL_MACHINE',

       @key     = N'SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL'

      

SELECT      InstanceName, RegPath, DefaultDataPath

FROM        #tempInstanceNames

Result Set:

InstanceName  RegPath                    DefaultDataPath

MSSQLSERVER   MSSQL10_50.MSSQLSERVER     NULL

DENALI3       MSSQL11.DENALI3            NULL

SQL08ENT      MSSQL10.SQL08ENT           NULL

SQL05EXP      MSSQL.1                    NULL

We have a list of instance names; now we need to iteratively read registry value for each path; and append it to DefaultDataPath. The paths we need to read from registry will be:

1. HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer

2. HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.DENALI3\MSSQLServer

3. HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL08ENT\MSSQLServer

4. HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer

These needs to be read using xp_regread. I have used dynamic SQL to iterate through the list:

DECLARE     @SQL VARCHAR(MAX)

SET         @SQL = 'DECLARE @returnValue NVARCHAR(100)'

SELECT @SQL = @SQL + CHAR(13) +

'EXEC   master.dbo.xp_regread

@rootkey      = N''HKEY_LOCAL_MACHINE'',

@key          = N''SOFTWARE\Microsoft\Microsoft SQL Server\' + RegPath + '\MSSQLServer'',

@value_name   = N''DefaultData'',

@value        = @returnValue OUTPUT;

 

UPDATE #tempInstanceNames SET DefaultDataPath = @returnValue

WHERE RegPath = ''' + RegPath + '''' + CHAR(13) FROM #tempInstanceNames

 

EXEC (@SQL)

 

SELECT      InstanceName, RegPath, DefaultDataPath

FROM        #tempInstanceNames

Result Set:

InstanceName         RegPath                    DefaultDataPath

MSSQLSERVER          MSSQL10_50.MSSQLSERVER     C:\Database\Data

DENALI3              MSSQL11.DENALI3            C:\Database\Denali\Data

SQL08ENT             MSSQL10.SQL08ENT           C:\Database\2008\Data

SQL05EXP             MSSQL.1                    C:\Database\2005\Data

I have updated the column in #tempInstanceNames so that it can be used further.

You can download the script from here.

Hope This Helps!

Vishal

EMail -> Vishal@SqlAndMe.com
Twitter -> @SqlAndMe
Facebook Page -> SqlAndMe

Filed under: SQL Configuration, SQLServer, SQLServer "Denali", SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, Undocumented Functions

Getting row count for all tables in a database is straight forward. You can display row count for all tables by joining sys.objects and sys.partitions as below:

USE   [AdventureWorks2008R2]

GO

 

SELECT      SCHEMA_NAME(A.schema_id) + '.' +

            A.Name, SUM(B.rows) AS 'RowCount'

FROM        sys.objects A

INNER JOIN sys.partitions B ON A.object_id = B.object_id

WHERE       A.type = 'U'

GROUP BY    A.schema_id, A.Name

GO

Result Set:

Person.Address                    78456

Person.AddressType                18

dbo.AWBuildVersion                1

dbo.BCPTest                       5

Production.BillOfMaterials        8037

Person.BusinessEntity             41554

Person.BusinessEntityAddress      78456
…

However, for views row count is not available in sys.partitions. To get the row count for a view; you must query the view itself.

USE   [AdventureWorks2008R2]

GO

 

SELECT COUNT(*) FROM HumanResources.vEmployee

GO

Result Set:

290

 

(1 row(s) affected)

This can be encapsulated in a stored procedure to query all available views and then display the result set. The procedure can be created as:

USE   [AdventureWorks2008R2]

GO

 

CREATE PROCEDURE dbo.ViewsRowCount

AS

BEGIN
SET NOCOUNT ON

CREATE TABLE #tempRowCount

(

      Name        VARCHAR(100),

      Row_Count   INT

)

 

DECLARE     @SQL VARCHAR(MAX)

SET         @SQL = ''

SELECT @SQL = @SQL + 'INSERT INTO #tempRowCount SELECT ''' +

            SCHEMA_NAME(schema_id) + '.' + name + ''', COUNT(*) FROM ' +

            SCHEMA_NAME(schema_id) + '.' + name +

            CHAR(13) FROM sys.objects WHERE type = 'V'

EXEC (@SQL)

 

SELECT      Name, Row_Count

FROM        #tempRowCount

END

GO

Once created this stored procedure returns row count for all views in database as bellow:

USE   [AdventureWorks2008R2]

GO

 

EXEC  dbo.ViewsRowCount

GO

Result Set:

Name                           Row_Count

dbo.vApplicationSpecialists    3

Person.vAdditionalContactInfo  10

HumanResources.vEmployee       290

Sales.vIndividualCustomer      18508

Sales.vPersonDemographics      19972

HumanResources.vJobCandidate   13

…

Hope This Helps!

Vishal

EMail -> Vishal@SqlAndMe.com
Twitter -> @SqlAndMe
Facebook Page -> SqlAndMe

Filed under: SQLServer

Identifying current service pack installed for SQL Server can be difficult if you don’t know which command to use! Most of the time I have seen people using @@VERSION to check for SQL Server service pack level, which is not correct as it returns the service pack level of operation system and not SQL Server.

SELECT @@VERSION

Result Set:

Microsoft SQL Server 2005 – 9.00.5000.00 (Intel X86)

       Dec 10 2010 10:56:29

       Copyright (c) 1988-2005 Microsoft Corporation

       Express Edition on Windows NT 6.0 (Build 6002: Service Pack 2)

Here the service pack reported is for Operating System. Service Pack for SQL Server can be identified by using SERVERPROPERTY() function as below, This returns SP4 which is the service pack level of SQL Server on my system:

SELECT SERVERPROPERTY('ProductLevel')

Result Set:

SP4

This has been "fixed" with SQL Server 2008+, @@VERSION now also returns service pack level for SQL Server:

SELECT @@VERSION

Result Set:

Microsoft SQL Server 2008 (SP3) – 10.0.5500.0 (Intel X86)

       Sep 22 2011 00:28:06

       Copyright (c) 1988-2008 Microsoft Corporation

       Enterprise Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)

As you can see from the output, both SQL Server and Windows service pack are displayed. You can use SERVERPROPERTY('ProductLevel') for a cleaner output.

 

Hope This Helps!

Vishal

EMail -> Vishal@SqlAndMe.com
Twitter -> @SqlAndMe
Facebook Page -> SqlAndMe

Filed under: SQL Configuration, SQLServer

The sys.dm_exec_requests is a great way to find out how long the BACKUP will take to complete. If you are doing a backup WITH STATS or by GUI, you will not need to use the DMV. But, if someone else is running a backup (or a Job), you can find the percent of backup completed and estimated completion time using this DMV. Another easy way is to ask the person who is taking the backup , but it’s not always possible.

The same thing happened to me recently (yesterday in fact!). I had to deploy a CR for an Application, and a Full database backup was to be taken in case a rollback is required. Now, if things were simple, I will take the backup and then proceed with CR deployment, but the database server is maintained by a different team about 4772 miles away! (managed by customer), and it’s not easy for people like me to keep staring at Outlook while waiting for backup completion notification from customer’s team.

When a backup is running, you can use the below query to check the progress, total_elapsed_time and estimated_completion_time returns milliseconds:

SELECT      command, percent_complete,

            'elapsed' = total_elapsed_time / 60000.0,

            'remaining' = estimated_completion_time / 60000.0

FROM        sys.dm_exec_requests

WHERE       command like 'BACKUP%'

Result Set:

command              percent_complete     elapsed       remaining

BACKUP DATABASE      50.75982             44.594500     41.207166

For a complete list of operation that reports percent_complete, check BOL for sys.dm_exec_requests.

Hope This Helps!

Vishal

EMail -> Vishal@SqlAndMe.com
Twitter -> @SqlAndMe
Facebook Page -> SqlAndMe

Filed under: Backup & Recovery, Management Views and Functions, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2