Menu
header photo

SQL-Talk

Talks all about SQL Server and Azure

Follow

SQL Server Pages Demo

--Pages Demo
--step1: Creating table
create table Books(bid int,bname varchar(40),qty int,price money)
--step2: Checking for pages
dbcc ind(0,'Books',-1)
--step3: Inserting sample data
insert Books values(1,'SQL',40,100),(2,'SQL Performance',40,120)
--Step4: Check for pages
dbcc ind(0,'Books',-1)
--step5: 
insert Books values(1,'SQL',40,100),(2,'SQL Performance',40,120)
go 10000
dbcc ind(0,'Books',-1)
--step6:
delete from Books
dbcc ind(0,'Books',-1)
--step7
truncate table Books
dbcc ind(0,'Books',-1) --0 pages
--step8: Index
insert Books values(1,'SQL',40,100),(2,'SQL Performance',40,120)
create nonclustered index indx1 on Books(bid)
dbcc ind(0,'Books',-1)
--step9: To view page contents
dbcc traceon(3604)
go
dbcc page(0,1,370,1)

Transaction Log File Demo

--step1: creating database
create database TLogDemo
--step2: View T.Log file contents
dbcc log(TLogDemo)
--step3: To view complete details
dbcc log(TLogDemo,3)
select * from fn_dblog(null,null)
--step4: Creating sample table
use TLogDemo
go
create table Employees(empid int,ename varchar(40),sal money)
--step5: Insert row
insert Employees values(1,'Rehan',4000),(2,'Ravi',4500)
update employees set sal=4540 where empid=1
--step
dbcc log(TLogDemo,3) --Check two inserts at the end 
--step6: Filtering T.Log file for INSERT
select * from fn_dblog(null,null)
where [Transaction ID] IN(
select [Transaction ID] from fn_dblog(null,null)
where [Transaction Name] like '%insert%')
--step7: To check changes made on Employees table
select * from fn_dblog(null,null)
where AllocUnitName like '%Employees%'
--step8
--Finding a transaction in the log for a particular user
SELECT
    [Current LSN],
    [Operation],
    [Transaction ID],
    [Begin Time],
    LEFT ([Description], 40) AS [Description]
FROM
    fn_dblog (NULL, NULL)
WHERE
    [Transaction SID] = SUSER_SID ('OPTIMIZE\REHAN');
GO
 
SELECT
    [Current LSN],
    [Operation],
    [Transaction ID],
    [Begin Time],
    LEFT ([Description], 40) AS [Description]
FROM
    fn_dblog (NULL, NULL)
WHERE
    [Transaction SID] = SUSER_SID ('OPTIMIZE\REHAN')
AND ([Begin Time] > '2015/06/03 11:18:15' AND [Begin Time] < '2015/06/03 11:18:25');
GO
SELECT
    [Operation], [Transaction Name], [Transaction SID],
    SUSER_SNAME ([Transaction SID]) AS [WhoDidIt?]
FROM fn_dblog (NULL, NULL)
WHERE [Operation] = N'LOP_BEGIN_XACT'
AND [Transaction Name] = 'UPDATE';
GO

Backup history of SQL Server Database

SELECT s.database_name, 
m.physical_device_name, 
cast(s.backup_size/1000000 as varchar(14))+' '+'MB' as bkSize, 
CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS VARCHAR(4))+' '+'Seconds' TimeTaken,
s.backup_start_date, 
CASE s.[type] 
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as BackupType, 
s.server_name, s.recovery_model
FROM msdb.dbo.backupset s 
inner join msdb.dbo.backupmediafamily m
ON s.media_set_id = m.media_set_id
WHERE s.database_name = 'Sales' --Change db name
ORDER BY database_name, backup_start_date, backup_finish_date

Checkpoint Demo

--step1: Creating table
create table customers(cid int,cname varchar(40),city varchar(30))
--step2: Inserting data
insert customers values(1,'Rakesh','Hyd')
--Get clean and dirty pages count in a database
SELECT Page_Status = CASE WHEN is_modified = 1 THEN 'Dirty'
ELSE 'Clean' END,
DBName = CASE WHEN database_id = 32767 THEN 'RESOURCEDB'
ELSE DB_NAME(database_id) END,
Pages = COUNT(1)
FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID()
GROUP BY database_id, is_modified
ORDER BY 2
--Run Checkpoint
CHECKPOINT
--clear clean pages
DBCC DROPCLEANBUFFERS()
GO

How to rename SQL Instance

--Step1: Check the current server name

SELECT   @@SERVERNAME

--Step2: Drop the current server

SP_DROPSERVER  [OLDNAME]

--Step3: Add the new server name

SP_ADDSERVER  [NEWNAME],LOCAL

--Step4: Restart the instance and check again

SELECT  @@SERVERNAME

SQL Server Execuction Plans

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)

SQL Server Backup and Restore FAQs

FAQ: - In which scenarios T.Log is truncated?

  1. When CHECKPOINT occurs in SIMPLE recovery model.
  2. When T.Log backup was generated in other recovery models.

FAQ: - Which transactions are not truncated by the checkpoint even if the recovery model is simple.

  1. Un committed transactions are not truncated.
  2. Unreplicated transactions.

FAQ: - How can we imagine or calculate size of backup?

  1. Use
  2. Go
  3. sp_spaceused
  4. Check reserved space  (Approx Size)

FAQ: - What is your backup strategy?

  • Backup strategy refers to types of backups and frequency which we have scheduled.
  • It depends on
  1. Size of database
  2. Recovery model
  3. Transaction Rate Availability (24/7)

Scenario1

            * My database size is 6GB and daily 500 transactions.

Suggestible strategy

  1. Daily Full backup
  2. Every 6hrs differential backup
  3. Every 2hrs T.Log backup

Scenario2

            * My Database size is 800GB and daily 50000 transactions.

Suggestible strategy

  1. Weekly Full backup
  2. Daily differential backup
  3. Every 1hr T.Log backup

Exercise:     Recovering crashed database. (17204)

1.Check error log.

2.If the error no 17204 (Data file was damaged)

3.Take tail log backup using

4.BACKUP  LOG  <DBNAME>  TO  DISK=’PATH’    WITH NO_TRUNCATE

5.Restore FULL BACKUP  with  NORECOVERY

6.Restore latest differential backup if any WITH  NORECOVERY

7.Restore all log backup after recent differential backup then Tail log backup  WITH  RECOVERY

FAQ: - Difference between Transaction Log backups and Tail Log backup?

Transaction Log backup

Tail Log backup

  1. It is scheduled log backup which truncates T.log file
  1. It is not scheduled. It cannot truncate T.Log file
  1. It forces checkpoint
  1. Skip checkpoint
  1. Required to truncate T.Log file
  1. To recover data when data file was damaged.
  1. Taken with BACKUP LOG command
  1. Taken with BACKUP LOG … WITH NO_TRUNCATE

Scenario1

We have configured every Sunday 9pm FULL backup. Every day 9pm differential backups and every 1hr T.Log backups. Database was failed at 9:30pm on Friday. Then what are the db recovery steps?

Steps

  • Take tail log backup to get 9- 9:30pm transactions.
  • Restore last Sunday Full backup.
  • Restore Friday 9pm differential backup
  • Restore tail log backup

Scenario2

            My database recovery model was SIMPLE and I have taken FULL backup. I was unable          to take T.Log backup hence I have changed recovery model to FULL. Can I take T.Log             backup now?

Not allowed. Full backup of SIMPLE recovery model cannot work as base for T.Log backups. We have to take first full backup then T.Log backup.

Scenario3

My Backup was failed. What may be the possible scenarios?

  • Disk was full
  • Server was busy.
  • Problem with the n/w
  • Msg 3201, Level 16, State 1, Line 1

Cannot open backup device '\\node-1\Backups\master.bak'. Operating system error 1326(The user name or password is incorrect.).

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

  • If domain is not running and SQL Server Service is running with domain account.

Message

The job failed.  Unable to determine if the owner (OPTIMIZE\rehan) of job Inventory backup has server access (reason: Could not obtain information about Windows NT group/user 'OPTIMIZE\rehan', error code 0x54b. [SQLSTATE 42000] (Error 15404)).

  • Problem with MSDB and SQL Agent
  • If there is disk I/O error while reading from data or T.Log files.
  • CHECKSUM errors
  • Database has entered into suspect or restoring.
  • T.Log file was full. (Only log backups are allowed other backups fail)

Transferring Logins between SQL Server instances

First 2 steps should be executed in primary server, copy 2nd step output and run in standby or second server.
 
--step1
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR
      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR
      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group
      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END
  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
--step2
EXEC sp_help_revlogin