aiDeeva.com

Category: SQL Server 2008 R2

  • ORIGINAL_LOGIN() and SUSER_SNAME() functions

    ORIGINAL_LOGIN() function returns the name of the original login that connected to the instance of SQL Server and is used to identify original login in all sessions. Even though you will do the security context switch it will return the original login name.

    SUSER_SNAME returns the name of user in the current security context. 

    --connect SQL Using LoginDEMO account
SELECT ORIGINAL_LOGIN() Original_Login_func, SUSER_SNAME() Suser_Name_Login_Func

--Executing query using LoginTest account
execute as login = 'LoginTest'
SELECT ORIGINAL_LOGIN() Original_Login_func, SUSER_SNAME() Suser_Name_Login_Func
revert

--Again executing query be reverting the change
SELECT ORIGINAL_LOGIN() Original_Login_func, SUSER_SNAME() Suser_Name_Login_Func

    Check below output image for more information. I connected SQL Server using LoginDemo account.
    New

  • Script to get the Last Backup Date of database full and t-log backup

    To get the max backup date of the database or to check whether database is backed up or not. You can execute below script to check the last backup date.

    SELECT   d.name,
         MAX(b.backup_finish_date) AS backupfinishdate
FROM     master.sys.sysdatabases d
         LEFT OUTER JOIN msdb..backupset b
         ON       b.database_name = d.name
         AND      b.type          = 'D'
GROUP BY d.name
ORDER BY backup_finish_date DESC


SELECT   d.name,
         MAX(b.backup_finish_date) AS backupfinishdate
FROM     master.sys.sysdatabases d
         LEFT OUTER JOIN msdb..backupset b
         ON       b.database_name = d.name
         AND      b.type          = 'L'
GROUP BY d.name
ORDER BY backup_finish_date DESC

  • Script to get the database backup history

    For the point time recovery or in case of failure it is essential that you should have the backup history of the database. You can execute the below query against the database to get the backup history.

    SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.expiration_date, 
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Database'  
       WHEN 'L' THEN 'Log'  
   END AS backup_type,  
   msdb.dbo.backupmediafamily.logical_device_name,  
   msdb.dbo.backupmediafamily.physical_device_name,   
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE 
(CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= '2013-01-08 00:04:49.000')  and (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) <= '2013-01-08 23:59:59')  
and  msdb.dbo.backupset.database_name = 'Mention the database name'
ORDER BY  
   msdb.dbo.backupset.backup_finish_date desc

  • Important Trace Flags for DBA

    A trace flag is used to set specific server characteristics or to switch off a particular behavior of the SQL Server.

    You can enable the trace flag from Startup Parameters, Session and Global Session level.

    DBCC TraceON(Flag Number) – at session level
DBCC TraceON(Flag Number, -1) -- Global

    Trace Flag 610 : Minimally logged DML operations into Indexed tables, transaction log and allow bulk loading
    Trace Flag 4199 : enable query optimizer fixes
    Trace Flag 1222 : writes the deadlock information in XML format into error log
    Trace flag 835 : Enable the Lock Pages in memory for standard edition
    Trace flag 1118 : Directs SQL Server to allocate full extents to each tempDB objects
    Trace flag 1204 : Write information about the deadlock in text format
    Trace flag 1211 : Disable the lock escalation based on the memory pressure
    Trace flag 3226 : Prevents successful backup operation being logged
    Trace flag 3014 : Returns more information to error log about backup operation
    Trace flag 3502 : Write information about the checkpoint in the error log
    Trace flag 3505 : Disables automatic checkpoint
    Trace flag 3004 : Returns more information about the instant file initialization
    Trace flag 1806 : Disables the instant file initialization