Query AWS CLI to get Instance Name and Instance Type

Remove Grep if you want to list or keep it for search and example here search by name with ‘db’


aws ec2 describe-instances  --region eu-west-1 --query 'Reservations[*].Instances[*].[Placement.AvailabilityZone, State.Name, InstanceId,InstanceType,Tags[?Key==`Name`] | [0].Value]' --output text |grep db
Advertisements

Auto Fix orphan users on all Databases

DECLARE @DB_Name varchar(100)  
DECLARE @Command nvarchar(2000DECLARE database_cursor CURSOR FOR  
SELECT name 
FROM  sys.databases where database_id>4 
and name not like '%master%' 
 
OPEN database_cursor 
 
FETCH NEXT FROM database_cursor INTO @DB_Name 
 
WHILE @@FETCH_STATUS = 0  
BEGIN  
                          
SELECT @Command ='  
                            use '+ @DB_Name+';  
                            declare @query varchar(1000) 
                            declare @executequery cursor 
                            set @executequery=cursor for 
                            select '' sp_change_users_login ''+CHAR(39)+''update_one''+CHAR(39)+'',''+CHAR(39)+name+CHAR(39)+'',''+CHAR(39)+name+CHAR(39) 
                            from sysusers 
                            where issqluser = 1 and (sid is not null and sid <> 0x0AND SUSER_SNAME(sidIS NULL 
                            open @executequery 
                            fetch next from @executequery into @query 
                            while @@fetch_status=0 
                            begin  
                             exec (@query) 
                             print (@query) 
                            fetch next from @executequery into @query 
                            end 
                            close @executequery; 
                            deallocate @executequery; 
                            go' 
                             
                            print @Command 
                           
  FETCH NEXT FROM database_cursor INTO @DB_Name  
END 
 
CLOSE database_cursor  
DEALLOCATE database_cursor

Database Mirroring Failover- all databases in a instance

--Script to failover all databases in a instance. 
declare  @mirroring  table (query varchar(200)) 
insert into  @mirroring 
select 'use master;' 
insert into  @mirroring 
SELECT ' ALTER DATABASE '+quotename(db_name(database_id))+' SET PARTNER FAILOVER ;'   
FROM sys.database_mirroring WHERE 
mirroring_role_desc = 'PRINCIPAL' 
select * from @mirroring

-- Script to Remove Database Mirroring for all databases after failover (useful in cut-over)

declare  @mirroring  table (query varchar(200))  
insert into  @mirroring  select 'use master;'  
insert into  @mirroring  
SELECT ' ALTER DATABASE '+quotename(db_name(database_id))+' SET PARTNER OFF ;'    
FROM sys.database_mirroring 
WHERE  mirroring_role_desc = 'PRINCIPAL'  
select * from @mirroring

How to Delete Millions of Rows From SQL Server Table ?

Sometimes there will be the requirement to delete million rows from a multi million table, as it is hard to run a single Delete Statement Like below Query 1 because it could eventually fill up your transaction log  and may not be truncated from log until all the rows have been deleted and the statement is completed because it will be treated as open transaction. Instead we can achieve the same by running the delete in multiple batches in an easy way.  Once we knew the number of rows we need to delete, we will choose the batch size and number of batches we need to run like in Query 2 where I need to Delete to 1 million rows, I chose batch size as 10000 and number of batches to 100 , so that 10000*100 equals to 1 Million rows. Number of batches could be more approximately.

Query 1:  Query to Delete records older than ‘1/1/2015’

Select Count(*) from History Where Date <= ‘1/1/2015’

1000000 – 1 Million rows and we need to delete all 1 million rows.

Delete from History Where Date <= ‘1/1/2015’

Query 2:

DELETE Top (10000) from History Where Date <= ‘1/1/2015’

Go 100

The Tricky part is ‘Go ‘ which is batch separator , the number we pass to the Go is the number of times you want to run that Query and each run time is considered as single batch.