Infrastructure at your Service

Category

Database Administration & Monitoring

Stéphane Haby

SQL Server: “Cannot resolve the collation conflict” in a Stored Procedure

By | Database Administration & Monitoring | No Comments

This week I have a new little challenge by a customer. By executing a “standard” stored procedure, I have the error: Msg 468, Level 16, State 9, Procedure Get_logsessions, Line 56 [Batch Start Line 119] Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “Latin1_General_CI_AS_KS_WS” in the equal to operation. The Stored Procedure named Get_Logsessions is to log all sessions running on the server in a table. Looking with the query I develop and present you…

Read More
Stéphane Haby

SQL Server SCOM database: A lot of errors 777970008 in the errorlog file

By | Database Administration & Monitoring | No Comments

Few weeks ago, during a audit, I have a lot of error 777970008 in the errorlog file from the SCOM instance. All 5 minutes, we had 4 lines more in the ErrorLog and the file growth a lot. After using google to find a cause of this message and resolve it, I finally find a good blog from Kevin Holman. I downloaded the script here and executed the part for the error 777970008 only: DECLARE…

Read More
JĂ©rĂ´me Dubar

Manage ODA patching with Data Guard or Dbvisit Standby

By | Database Administration & Monitoring, Database management, Operation systems, Oracle | No Comments

Introduction Building an Oracle infrastructure today without thinking about a Disaster Recovery solution (DR) is quite rare. This became obvious that a backup or a dump will not help if you do not know where to restore or import once your production server is down. And restoring a backup is definitely not the fastest way to bring back your database to life. As a consequence, Data Guard or Dbvisit Standby, depending on which edition you’re…

Read More
Stéphane Savorgnano

Automate restore from Rubrik with PowerShell (part 2)

By | Database Administration & Monitoring, Database management, SQL Server | No Comments

As promised during my last Rubrik blog post I’m writing a second one to explain how to: log your different steps in a file add an integrity check after the restore and also improve my first scripting I will start by the end and improve my first scripts by creating functions for each step of the process. With Rubrik you can have multiple appliances (servers), it means that each server/instance of you SQL Server environment…

Read More
Steven Naudet

SQL Server: table variables performance limitations

By | Database Administration & Monitoring | No Comments

Doing some performance troubleshooting for one of my customers I identified some issues with very large table variables inside Stored procedures. Table variables limitations are not well understood by developers although they are now well documented. Table variable rows estimation Let’s have a look at an example with my customer context which is SQL Server 2016, so compatibility level 130 at the database level. You can reproduce this demo with the Wide World Importers database….

Read More
Karsten Lenz

Setup pgpass for replication

By | Database Administration & Monitoring | No Comments

This script provides functionality to setup passwordless authentication in PostgreSQL for defined Server Names. This is very helpful by using replication via repmgr and is not Linux Distribution specific, it was used till now with SLES 12, SLES 15 and RHEL 8 Clones. As all of my scripts it provides help how the usage is with -h parameter: $ [[[email protected] ~]$ sh pgpass.sh -h $ [[[email protected] ~]$ Usage: $ [[[email protected] ~]$ pgpass.sh [OPTION] $ [[[email protected]

Read More
Daniel Westermann

Creating simple extensions for PostgreSQL

By | Database Administration & Monitoring | No Comments

When you are using PostgreSQL you should already know that PostgreSQL comes with a set of extensions by default. It might be that you need to install an additional package if you installed PostgreSQL with a package manager to get those extensions. Usually it is called something with “contrib” in its name. There is also the PostgreSQL Extension Network which list a lot of external extensions that might be useful for you. If you can’t…

Read More
Daniel Westermann

What are these *.ready and *.done files for in PostgreSQL?

By | Database Administration & Monitoring | No Comments

When you run PostgreSQL workloads in production you must have a backup and restore implementation. Even for development instances, which are like production for the developers, a well-tested backup and restore procedure sometimes must be in place. Community PostgreSQL comes with pg_basebackup to help you with creating a full consistent backup of your PostgreSQL cluster. If you want to be able to do point in time recovery (PITR) you need to archive the WAL segments…

Read More
Steven Naudet

SQL Server: Fixing another huge MSDB database – 80GB+

By | Database Administration & Monitoring, SQL Server | No Comments

I have blogged several times about unusually large MSDB databases like here. Most of the time the problem comes from the backup history which is never purged. This time it’s different. Try to guess, if not the backup history, what can cause MSDB to increase abnormally in size? Let’s start by looking at the total size of the database: It’s huge. The database size is almost 90GB. The 10% configuration on data files causes large…

Read More
Daniel Westermann

New predefined roles for PostgreSQL 14

By | Database Administration & Monitoring | No Comments

Time is moving fast and PostgreSQL 14 is already in beta 2. PostgreSQL 14 will ship with a lot of new features and in this post will look at a smaller one: There are three new predefined roles: pg_read_all_data, pg_write_all_data and pg_database_owner. While it seems to be obvious what the first two roles are about, the third one might sound strange at the beginning. Let’s have a look.

Read More