Archive

Archive for the ‘Catalog Views’ Category

SQL Server – How to Move Table to Another Schema

May 13, 2013 2 comments

Starting with SQL Server 2005 all tables are grouped into schemas. While creating a table if the schema name is not specified it is created in the default schema of the user creating it. you can use ALTER SCHEMA command to move tables between schemas.

For example, if I create a table using below script it will be created under my default schema which is dbo:

USE [SqlAndMe]

GO

 

CREATE TABLE Employee

(

       ID     INT,

       Name VARCHAR(20)

)

GO

SELECT name, [schema] = SCHEMA_NAME(schema_id)

FROM   sys.tables

WHERE  name = 'Employee'

GO

Result Set:

name          schema

Employee      dbo

 

(1 row(s) affected)

As you can see from the output the table is currently in dbo schema.

Now to move this table to another schema using ALTER SCHEMA command, first we need to create the schema if it does not exist already. After that we can move table to new schema.

USE [SqlAndMe]

GO

 

CREATE SCHEMA HumanResources

GO

 

ALTER SCHEMA HumanResources

TRANSFER dbo.Employee

GO

 

SELECT name, [schema] = SCHEMA_NAME(schema_id)

FROM   sys.tables

WHERE  name = 'Employee'

GO

Result Set:

name          schema

Employee      HumanResources

 

(1 row(s) affected)

As you can see from the output the Employee table is now moved to HumanResources schema.

 

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

SQL Server – Finding out Database creation time

February 19, 2013 1 comment

Finding database creation time is simple if database has not been detached from server. You can find database creation time using below methods.

Using sp_helpdb:

You can find the creation time of database using sp_hepldb system procedure.

EXEC sp_helpdb 'SqlAndMe'

GO

This will return database creation time along with other details:

image

Using catalog view sys.databases:

Database creation time is also available in sys.databases catalog view:

SELECT      create_date

FROM        sys.databases

WHERE       name = 'SqlAndMe'

GO

Result Set:

create_date

2012-11-14 15:19:31.987

 

(1 row(s) affected)

 

However, if the database has been detached and reattached to server, the creation time will be changed to attach time. Also, if the database has been restored from a backup after dropping the database creation time will be changed.

Actual creation time of database is stored in the boot page of the database which is retained after restore or detach/attach. This is stored as dbi_crdate.

You can use DBCC PAGE to read creation time of database from boot page:

DBCC TRACEON(3604)

GO

 

DBCC PAGE('SqlAndMe', 1, 9, 3)

GO

 

Result Set (Trimmed):

dbi_createVersion = 661              dbi_ESVersion = 0                   

dbi_nextseqnum = 1900-01-01 00:00:00.000        dbi_crdate = 2011-11-07 21:12:46.357

dbi_filegeneration = 2              

dbi_checkptLSN

 

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

SQL Server – Check SQL Agent Job History using T-SQL

May 14, 2012 6 comments

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:

image

 

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

SQL Server – Purging Database Mail History

May 10, 2012 2 comments

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

SQL Server – Four-part object names

April 30, 2012 Leave a comment

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

Categories: Catalog Views, SQLServer

SQL Server – Creating and using Dump Devices for Backups

September 26, 2011 Leave a comment

A dump device is simply a logical device which redirects to a specified physical device. The main advantage of using a dump device for backups is it reduces the change required to backup code. For example, if you are backing up to a Network location. If you have hard-coded the backup location then the network location change will require a code change as well.

 

You can create a dump device using SSMS or T-SQL.

To create a dump device using SSMS:

1. Expand "Server Objects" in Object Explorer,

2. Right Click on "Backup Devices" and choose "New Backup Device"

image

3. Provide a logical Device name and the physical backup file location for dump device

image

4. Click "OK" to create the dump device.

 

To create a dump device using T-SQL:

To create a dump device using T-SQL you can use system stored procedure sp_addumpdevice.

USE [master]

GO

EXEC master.dbo.sp_addumpdevice

@devtype          = N'disk',

      @logicalname      = N'MyBackups',

      @physicalname     = N'C:\Database\Backup\BackupDisk.bak'

GO

Result Set:

(1 row(s) affected)

 

View all dump devices:

You can get the list of existing dump devices on server using sys.backup_devices catalog view:

SELECT      *

FROM        sys.backup_devices

GO

Result Set:

Name          type   type_desc     physical_name

———–   —–  ———–   ——————————–

MyBackups     2      DISK          C:\Database\Backup\BackupDisk.bak

 

(1 row(s) affected)

 

Using the dump device for BACKUP:

Once the dump device is created you can use it to store backups, The dump device will be listed in "Select Backup Destination" dialog box:

image

You can use it in T-SQL as below:

/*

      BACKUP DATABASE [SqlAndMe]

      TO DISK = N'C:\Database\Backup\SqlAndMeBackup.bak'

      WITH NOFORMAT, NOINIT, NAME = N'SqlAndMe – Full Backup',

      SKIP, NOREWIND, NOUNLOAD,  STATS = 10

      GO

*/

 

BACKUP DATABASE [SqlAndMe]

TO [MyBackups]

WITH NOFORMAT, NOINIT, NAME = N'SqlAndMe – Full Backup',

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

Result Set:

10 percent processed.

20 percent processed.

30 percent processed.

40 percent processed.

50 percent processed.

60 percent processed.

70 percent processed.

80 percent processed.

90 percent processed.

Processed 44680 pages for database 'SqlAndMe', file 'SqlAndMe' on file 2.

100 percent processed.

Processed 1 pages for database 'SqlAndMe', file 'SqlAndMe_log' on file 2.

BACKUP DATABASE successfully processed 44681 pages in 23.542 seconds (14.827 MB/sec).

 

Changing Physical File location for dump devices:

To change the physical file location for dump device, you need to drop and recreate the dump device. If you need to move the current physical file to a new location, it can be done using Windows Explorer or any other File Manager. The physical file is not locked unless a BACKUP/RESTORE is in progress.

USE [master]

GO

EXEC master.dbo.sp_dropdevice

      @logicalname = N'MyBackups'

GO

 

EXEC master.dbo.sp_addumpdevice

      @devtype          = N'disk',

      @logicalname      = N'MyBackups',

      @physicalname     = N'C:\NewLocation\BackupDisk.bak'

GO

 

 

SELECT      *

FROM        sys.backup_devices

GO

Result Set:

Device dropped.

 

 

Name          type   type_desc     physical_name

————  —— ————  ———————–

MyBackups     2      DISK          C:\NewLocation\BackupDisk.bak

 

(1 row(s) affected)

 

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

Using Catalog Views – sys.server_role_members & sys.database_role_members

June 24, 2011 Leave a comment

sys.server_role_members contains the membership of each server principal with server roles. It returns a pair of role_principal_id and member_principal_id for each membership as below:

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT role_principal_id, member_principal_id

FROM   sys.server_role_members

Result Set:

role_principal_id member_principal_id

—————– ——————-

3                 1

3                 259

3                 260

3                 261

3                 262

9                 279

 

(6 row(s) affected)

sys.server_role_members can be joined with sys.server_principals to retrieve role/member name:

SELECT        A.name AS 'Role', B.name AS 'Login'

FROM          sys.server_role_members C

INNER JOIN    sys.server_principals A ON A.principal_id = C.role_principal_id

INNER JOIN    sys.server_principals  B ON B.principal_id = C.member_principal_id

Result Set:

Role                 Login

——————-  ———————-

Sysadmin             sa

Sysadmin             NT AUTHORITY\SYSTEM

Sysadmin             NT SERVICE\MSSQLSERVER

Sysadmin             ***********************

Sysadmin             NT SERVICE\SQLSERVERAGENT

Dbcreator            Vishal

 

(6 row(s) affected)

sys.server_role_members contains information for server level roles, for database level roles sys.database_role_members can be used. It returns a pair of role_principal_id and member_principal_id for each membership:

SELECT role_principal_id, member_principal_id

FROM   sys.database_role_members

Result Set:

role_principal_id member_principal_id

—————– ——————-

16384             1

16384             5

16384             6

16385             5

16385             6

16386             5

16387             5

16387             6

16389             5

16390             5

16390             6

16391             5

16391             6

 

(13 row(s) affected)

It can be joined with sys.database_principals to retrieve role/member name as below:

SELECT        A.name AS 'Role', B.name AS 'User'

FROM          sys.database_role_members C

INNER JOIN    sys.database_principals A ON A.principal_id = C.role_principal_id

INNER JOIN    sys.database_principals B ON B.principal_id = C.member_principal_id

Result Set:

Role                 User

——————– ——————–

db_owner             dbo

db_owner             sqladmin

db_accessadmin       sqladmin

db_securityadmin     sqladmin

db_ddladmin          sqladmin

db_backupoperator    sqladmin

db_datareader        sqladmin

db_datawriter        sqladmin

db_owner             NewUser

db_accessadmin       NewUser

db_ddladmin          NewUser

db_datareader        NewUser

db_datawriter        NewUser

 

(13 row(s) affected)

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