Categories
Database Development

Date Conversion Error SSIS

I am importing from a pipe delimited flat file via SSIS Package were date in source text file is in format MMDDYYYY using data conversion for field as date [DT_DATE] is there another conversion type I should use in the data conversion task?

Categories
Database Development

I need to do periodic commits in my purge function

I wrote a purge function that runs in Postgres 9.6. The function goes through all of the tables in our application starting from the bottom most child table and ending with the top most parent table and purges based on date or client. When we run this in production and beta, there are other processes […]

Categories
Database Development

Availability Group SQL Server Databases Always fall back to Restoring State

I have two Databases out of several others which I constantly need to re-add to a SQL Server Availability Group (all other 50 or so Databases from the same AG cause no issue). Indeed, they always go back to a "Restoring" status on the Secondary Replica instead of having a Synchronised status like the other […]

Categories
Database Development

How to determine what is the potential max worker thread count on a server where the server property is set to 0?

I have a SQL Server instance where the Max Worker Threads server property is set to the default of 0. Periodically I’m receiving the the following errors (in the SQL Error Log): Error: 17189, Severity: 16, State: 1. SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or […]

Categories
Database Development

SQL Backup routine regarding DB Logs

I recently had an incident that got me rethinking my backup plan: One of our MSSQL driven aplications stalled because the log fle had grown too big on the filesystem (it was 50 GBs IIRC). It turned out that the aplication developer is setting the DB recovery method to ‘Full’, and the full backup nighlty […]

Categories
Database Development

PostgreSQL functions for creating and dropping declarative partioning

I created function for creating declararative partitions. CREATE OR REPLACE FUNCTION createpartition(table_name varchar(25), forecast_id integer) RETURNS varchar(30) AS $$ DECLARE partition_name varchar(30); sql_query text; BEGIN partition_name = table_name || ‘_’ || forecast_id::varchar(10); sql_query = format(‘CREATE TABLE %I PARTITION OF %I FOR VALUES IN (%L)’, partition_name, table_name, forecast_id); EXECUTE sql_query; RETURN partition_name; EXCEPTION WHEN duplicate_table THEN […]

Categories
Database Development

MySQL router on server with tomcat memory leak

Memory is slowly used by mysql router. I am running mysql router version 8.0.20 (I found there was a memory leak on mysql router 2.1.2 but mysql router 8.x is newer) I have a 3 node single-primary cluster on MySQL 5.7.30 with 13 application servers pointing to it from localhost and 1 server dedicated to […]

Categories
Database Development

Dynamic config file generation using powershell

I am trying to build a config file for a monitoring app for all our SQL Servers. For this I need to add all instance details of a server in a config file(i.e. one file will be generated per server and not instance). So even if server has multiple instances, it will only have one […]

Categories
Database Development

ORA-01031: insufficient privileges, cannot login as any user

I ran below command and restarted docker container. alter system set processes = 1 scope = spfile; after this I am not able to login to DB at all. root@30b2f9030f89:/u01/app/oracle/product/11.2.0/xe/bin# sqlplus /nolog SQL*Plus: Release 11.2.0.2.0 Production on Wed Aug 5 14:59:14 2020 Copyright (c) 1982, 2011, Oracle. All rights reserved. SQL> conn / as sysdba […]

Categories
Database Development

What is best for a primary key? Bigint of 20 numbers or varchar / varbinary of 15 characters?

I have already seen some posts making comparisons of varchar with numeric fields, but most only compare with equal values ​​and do not talk about performance in primary keys. I have a user registration table where each user needs to have a unique code and the auto-incrementing sequential numbers (1,2,3 …) are not a good […]