SQLPS Powershell in SQL Server -4

Lets see some more cmdlets

1.Backup-Sqldatabase
first we need to check examples how to use Backup_Sqldatabase

Run this command
Get-Help Backup-Sqldatabase -Examples



It will give us examples
Similarly for all cmdlets we can get examples how to use it.


Now we will run a sample how take backup

Backup-Sqldatabase -Database Test




It will take backup of Database Test.

How to redirect  result to HTML page?

Run this command

 Invoke-Sqlcmd "select * from sys.tables" | ConvertTo-Html > c:\test.html




Lets see the result in browser by opening test.html in browser.


















Managed lock priority -SQL 2014

Managed lock priority 

Its available in SQL 2014 and used for online operation s
Mainly used for switching partition and rebuilding indexes online.

Till now its issue to switch partition while tables are in use.
Its because that requires schema modification lock SCH-M lock.
As its not possible to get this lock until any operation is running on table and hence switching partition is a nightmare for any developers.
This request for SCH-M lock was creating a chain of locks and hence affecting performance of OLTP operations.


Now in 2014 we have option that we can define priority for switching or online reindexing with other operations.
Lets see how to use this feature

If we see at syntax of alter table or alter index we will find this option


WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ], ABORT_AFTER_WAIT =
{ NONE | SELF | BLOCKERS } )

MAX_DURATION is time for which we want to wait before completing the operation

ABORT_AFTER_WAIT :- What action we want to take after waiting time completes
NONE :- Will take no action and continue waiting
SELF :- It will kill self session
BLOCKERS:- It will kill session which are creating blocking and will do switching or
 reindexing.

But this killing session has one drawback it will lead transaction to rollback and if we dont know which operations are in rollback it may impact OLTP operations.
So we need to be careful while doing this and should be done when there is low activity or switching/reindexing has higher priority compare to other tasks.

Lets create a table and check this behavior


CREATE PARTITION FUNCTION part_test_func( datetime)
AS RANGE LEFT FOR VALUES( '2014-09-10' , '2014-09-20' , '2014-09-30');


CREATE PARTITION SCHEME part_test_scheme
AS PARTITION part_test_func TO( [primary],[primary],[primary],[primary]);


CREATE TABLE part_test( id int IDENTITY( 1 , 1) ,
                        data varchar( 100) ,
                        date datetime)
ON part_test_scheme( date);

CREATE TABLE part_test1( id int IDENTITY( 1 , 1) ,
                        data varchar( 100) ,
                        date datetime)
ON part_test_scheme( date);


INSERT INTO  part_test
VALUES( 'a' ,
        '2014-09-05'
      );
INSERT INTO part_test
VALUES( 'a' ,
        '2014-09-15'
      );
INSERT INTO part_test
VALUES( 'a' ,
        '2014-09-25'
      );
INSERT INTO part_test
VALUES( 'a' ,
        '2014-10-05'
      );



Now in other window I will run this query 

BEGIN TRAN

SELECT *
FROM   part_test WITH(updlock)









Now we will go for partition switching in first window 
We will run this query
ALTER TABLE part_test switch PARTITION 1 TO part_test1 PARTITION 1
WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES,
ABORT_AFTER_WAIT = BLOCKERS))
It will wait for 1 minute and then kill the blocking and statement and complete 
switching.

But we also need to know which session were killed. 
We can find this details in errorlog




Popular Posts