How to kill all the sessions in postgreSQL database

The below script will be used to kill all the sessions except your session.

SELECT pg_terminate_backend(pg_stat_activity.pid)

FROM pg_stat_activity

WHERE pg_stat_activity.datname = 'dbname' –- Database Name

AND pid <> pg_backend_pid();

If you are looking for specific process id then you can provide the pid value in the where clause as below

SELECT pg_terminate_backend(pg_stat_activity.pid)

FROM pg_stat_activity

WHERE pg_stat_activity.datname = 'dbname' –- Database Name

AND pid <> (processid) -- Provide the process id you would like to kill;

 

Hope you enjoyed the post!

Cheers

Ramasankar Molleti

MSDN:LinkedIn:Twitter

 

 

Posted in PostgreSQL | Leave a comment

Powershell Script to change IIS AppPool

import-module webadministration
dir IIS:\AppPools | ForEach-Object {
#Not able to change the pipeline mode through 'Set-ItemProperty'
&"$env:windir\system32\inetsrv\appcmd.exe" set apppool $_.Name /managedPipelineMode:"Classic"
$poolNme = 'IIS:\AppPools\'+$_.Name
Set-ItemProperty -Path $poolNme -name enable32BitAppOnWin64 -value FALSE
} 
Posted in IIS, Powershell | Leave a comment

How to Migrate SQL Server Database running on EC2 instance to Amazon RDS Instance

Recently i worked on migrating sql server databases running on amazon EC2 instances to Amazon RDS instances. Starting from 27th Jul 2016 Amazon announced you can ship your databases from ec2 to rds using native backup and restore method. Let’s see how it works.

Steps:

  • Assume you have RDS Instance ready with default setting
  • Create a custom option group “NativeBackup” and enable S3 bucket full access. You may use the below IAM rule
RDSAccessToS3
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": "s3:*",
"Resource": "*"
}
]
}
  • Right click on Custom option group and then click on Add option –> Select SQLSERVER_BACKUP_RESTORE option — > Apply Immediately

rds_sql_server_add_backup_option_1

  • Now you have custom group setup with native backup and restore feature and correct permissions to S3 bucket. Now, Add this group to RDS instance. Right click on RDS instance –> Click on Modify –> Navigate to Option group —> Assign the NativeBackup group which was created in the step 2 –> Click Modify instance

1

  • Now, Copy the backup of EC2 instances to S3 bucket. You may use AWS CLI command line tool to copy from one EC2 instance to S3 bucket
Aws s3 cp \\ec2backuplocations <a href="s3://Infra_Bucket" target="_blank" rel="noreferrer" data-mce-href="s3://Infra_Bucket">s3:<wbr>/<wbr>/</a>Buket_Name &nbsp;--profile “your profilename"
  • Once you are done with copying the backup to S3 location, then you may use the below script to restore the database from backup
     

    
    use master
    
    go
    
    exec msdb.dbo.rds_restore_database
    
    @restore_db_name='TestDB',
    
    @s3_arn_to_restore_from='arn:aws:s3:::Bucket_Name/testdb.bak'&amp;amp;amp;amp;nbsp;
  • Restore will begin, you may use the below stored procedure to get the status of the restore process
    exec msdb.dbo.rds_task_statusda
  • That’s all about the restore! Below are the possible errors you may encounter while migrating to RDS instance

Error1:

Aborted the task because of a task failure or a concurrent RESTORE_DB request.
Database TestDB cannot be restored because there is already an existing database wih the same family_guid on the instance.

Reason: If you have same file guid for data or log file exists then you cannot restore to rds instance. The file guid’s are unique to each database. There is a possibility to get the same fileguid based on the name.

You may use sys.master_files view to get the details of file guid

Another reason for this error is you have already restored the same database and trying to restore, hence the error. RDS has the limitation that you cannot restore the same database file names on the same instance.

Resolution: Do not restore the same database names on the same RDS instance

Error2:

Msg 229, Level 14, State 5, Procedure rds_restore_database, Line 1 [Batch Start Line 45]
The EXECUTE permission was denied on the object ‘rds_restore_database’, database ‘msdb’, schema ‘dbo’.

Reason: You do not have permission to perform the restore

Resolution:

Please make sure the login you used have execute permission for the stored procedure rds_restore_database. If you are using master credentials and you still getting the same error then you have not granted full permission to S3 bucket or missing IAM roles for the S3 bucket. Please grant the access and re-try

Error3:

Msg 50000, Level 16, State 0, Procedure rds_restore_database, Line 73 [Batch Start Line 22]
A task has already been issued for database:TestDB  with task Id: 3, please try again later.

Reason: You have issued the restore command multiple times and hence the error.

Resolution: Do not issue the restore command twice for the same database. Kill the multiple restore processes and re-issue the command

References:

https://aws.amazon.com/blogs/aws/amazon-rds-for-sql-server-support-for-native-backuprestore-to-amazon-s3/

https://www.brentozar.com/archive/2016/07/holy-cow-amazon-rds-sql-server-just-changed-everything/

Hope you enjoyed the post!

Cheers

Ramasankar Molleti

MSDN:LinkedIn:Twitter

Posted in Uncategorized | Leave a comment

How to debug SSIS package

You or your team or someone has written an SSIS package which is running flawlessly over few days/months/years. One fine day SSIS package is failing continuously and you need to troubleshoot and fix the issue.

Error message is not showing much information about why the package has been failed and until what step the package was successful or failed.

What will you do in this scenario?

Debug! Debug! Debug! Let’s see how this works.

You can debug this using breakpoints and data viewer in SSIS. Let’s explore this.

Data Viewer:

You can view the data while the ssis package is running in each step. This will help you to know what data is processing in each step in data flow task. You can see that in the below screenshot.

Dataviewer

After you execute the package then the result can be seen as below

Dataviewer2

Break Points:

You can also debug a package by setting the breakpoints on a package task/container as below

Breakpoint

breakpoint2

As you can see in the above screenshot that you can enable any of the above options to debug the ssis package. Also can see each option in the below table.

Break condition Description
When the task or container receives theOnPreExecute event. Called when a task is about to execute. This event is raised by a task or a container immediately before it runs.
When the task or container receives theOnPostExecute event. Called immediately after the execution logic of the task finishes. This event is raised by a task or container immediately after it runs.
When the task or container receives theOnError event. Called by a task or container when an error occurs.
When the task or container receives theOnWarning event. Called when the task is in a state that does not justify an error, but does warrant a warning.
When the task or container receives theOnInformation event. Called when the task is required to provide information.
When the task or container receives theOnTaskFailed event. Called by the task host when it fails.
When the task or container receives theOnProgress event. Called to update progress about task execution.
When the task or container receives theOnQueryCancel event. Called at any time in task processing when you can cancel execution.
When the task or container receives theOnVariableValueChanged event. Called by the Integration Services runtime when the value of a variable changes. The RaiseChangeEvent of the variable must be set to true to raise this event.

** Warning ** The variable associated with this breakpoint must be defined at the container scope. If the variable is defined at the package scope, the breakpoint does not get hit.

When the task or container receives theOnCustomEvent event. Called by tasks to raise custom task-defined events.

Both breakpoints and data viewer options are very useful when you troubleshoot/debug/develop complex ssis packages.

Hope you like the post!

Happy Reading!

Cheers

Ramasankar Molleti

MSDN:LinkedIn:Twitter

 

 

 

Posted in Database Administration, SSIS | 1 Comment

Script to get SQL Server Error logs via Email

This is one of the handy script to get sql server error logs via email.

declare @Time_Start datetime;
declare @Time_End datetime;
set @Time_Start=getdate()-2;
set @Time_End=getdate();

create table #ErrorLog (logdate datetime
, processinfo varchar(255)
, Message varchar(max) )

insert #ErrorLog (logdate, processinfo, Message)
EXEC master.dbo.xp_readerrorlog 0, 1, null, null , @Time_Start, @Time_End, N'desc';

create table #SQL_Log_Errors (
[logdate] datetime,
[Message] varchar (500) )

insert into #SQL_Log_Errors
select LogDate, Message FROM #ErrorLog
where (Message like '%err%'
or Message like '%warn%'
or Message like '%kill%'
or Message like '%dead%'
or Message like '%cannot%'
or Message like '%could%'
or Message like '%fail%'
or Message like '%not%'
or Message like '%stop%'
or Message like '%terminate%'
or Message like '%bypass%'
or Message like '%roll%'
or Message like '%truncate%'
or Message like '%upgrade%'
or Message like '%victim%'
or Message like '%recover%'
or Message like '%critical%'
or Message like '%IO requests taking longer than%')
AND Message not like '%errorlog%'
AND Message not like '%dbcc%'
AND Message not like '%checkdb%'
order by logdate desc

drop table #ErrorLog

declare @cnt int
select @cnt=COUNT(1) from #SQL_Log_Errors
if (@cnt > 0)
begin

declare @strsubject varchar(100)
declare @oper_email nvarchar(100)

set @oper_email = (select email_address from msdb.dbo.sysoperators where name = 'DBA')
select @strsubject='There are errors in the SQL Error Log on ' + @@SERVERNAME

declare @tableHTML nvarchar(max);
set @tableHTML =
N'<H1>SQL Error Log Errors - ' + @@SERVERNAME + '</H1>' +
N'
<table border="1">' +
N'
<tr>
<th>Log Date</th>
' +
N'
<th>Message</th>
</tr>
' +
CAST ( ( SELECT td = [logdate], '',
td = [Message]
FROM #SQL_Log_Errors
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>
' ;

EXEC msdb.dbo.sp_send_dbmail
@from_address='',
@recipients= @oper_email,
@subject = @strsubject,
@body = @tableHTML,
@body_format = 'HTML',
@profile_name='YourProfileName'

end

drop table #SQL_Log_Errors

go

Cheers

Ramasankar Molleti

MSDN:LinkedIn:Twitter

Posted in Uncategorized | Leave a comment

Cumulative Update #4 for SQL Server 2012 SP3

Microsoft announced the 4th cumulative update release for SQL Server 2012 SP3 is now available for download at the Microsoft Support site.

For original post you may refer here 

Cheers

Ramasankar Molleti

MSDN:LinkedIn:Twitter

Posted in Database Administration | Leave a comment

New SQL Server 2016 Online Alter column

Over the few months i have been playing with SQL Server 2016 new features. Microsoft SQL Server 2016 introduced new alter column with ONLINE option. Let’s explore this.

  • Create a table and insert data
CREATE TABLE dbo.test_online (column_1 INT ) ;
GO
INSERT INTO dbo.test_online(column_1) values(10)
GO 
  •    Find what page has been allocated for the above operation. I have used un documented function fn_PhysLocCracker to get the information
SELECT fnph.page_id FROM dbo.test_online
 OUTER APPLY sys.fn_PhysLocCracker(%%PhysLoc%%) AS fnph;

1.png

  • Perform alter column without online option
ALTER TABLE dbo.test_online
ALTER COLUMN column_1 DECIMAL (5, 2)
--WITH (ONLINE = ON);
GO 
  • Verify what page has been allocated for the above alter column

2.png

  • You can see in the above screenshot that same page has been allocated for alter command.
  • Now, let’s Perform alter column with online option. Repeat the above steps
ALTER TABLE dbo.test_online 
 ALTER COLUMN column_1 DECIMAL (5, 2) 
 WITH (ONLINE = ON); 
GO 
  • Now verify the allocated page for the above alter command

3.png

  • You can see in the above screenshot that the new page has been allocated for alter column statement without changing the old page id. This means leaving the old page available  during the operation.

Conclusion: Online operation on alter column is really useful feature for DBA’s to perform the alter table with minimal downtime during the operation especially with table with large data.

Hope you like the post!

Happy Learning!

Cheers

Ramasankar Molleti

MSDN:LinkedIn:Twitter

 

 

 

Posted in Database Administration, SQL Server 2016, T-SQL | Leave a comment