Is there really no way in SQL to update all fields in a table without explicitly typing field names As indicated in this post Update all columns from another table?

I need to update a table with 50 columns with values from another table, and then insert new records from a different table (with 50 columns).  I really don't want to list 50 name-value pairs (or use the bulk updating syntax here Bulk update of all columns, which SQL Server doesn't seem to like anyway).

So, I can conclude one of the following:

There IS a way after all,
It's ridiculous to have a table with 50 columns, or
Database Admins are masochists.

Unless I've missed something here - and I may have - I think this is fairly straightforward.

Essentially the only problem here is you don't want to type out all the column names- that can easily be automated with a bit of dynamic SQL using the sys tables as hinted at by Randolph West.

Something like

select @Sql = COALESCE(@Sql + ', ', '') + COLUMN_NAME
from
    INFORMATION_SCHEMA.COLUMNS
where
    TABLE_NAME='Table'

Which will list out all the columns separated with commas, as if you typed them ready for insert/update (or select).

This post: Selecting column name dynamically in an insert query is a stack overflow question that goes into the detail.

EDIT:

Just thought I'd add for clarity and to consolidate some comments:-

Anything can be added to the @Sql 'building' bit, for example if you want the table name put before the column name, the select part would be:

select @Sql = COALESCE(@Sql + ', ', '') + TABLE_NAME + '.' + COLUMN_NAME

You can execute that variable using

sp_executesql @Sql

Or just

SELECT @Sql

to give it to you in a query execution window, which you can copy & paste and run (which I would usually do - you can double check the code first and edit if needed)


Mysql multi cpu (vs multi core) usage I've read many posts on internet and stack exchange about multi core usage by MySQL.

From 5.1.38 on MySQL has multi core support. But what about multi CPU?

I'm running 5.1.73 on a Dell PowerEdge with 2 Xeons each with 12 cores. The top command shows 24 cores, but only the first 12 (first CPU) are always busy. The last 12 cores (second CPU) always seem idle.

Is (a newer version of) MySQL capable of using more than 1 CPU?

Cores, processes, CPUs, threads, hyperthrading, connections -- they are mostly interchangeable at this level of discussion.

MySQL has always been able to handle multiple connections and keep things straight.  Back in the single-core single-cpu days, two connections would start up two threads (or processes, depending on OS details) and run seemingly simultaneously.  With 12 CPUs / 24 cores, the 'only' difference is that there is now less contention for CPU resources.

But...  One connection will use only one core.  This may explain what you are seeing.  100 connections may use (and share) all 24 cores.

So, if you question is really about "parallel processing" where one connection uses multiple cores, the answer is "no" in all versions of MySQL/MariaDB.  (A slight exception:  InnoDB has some 'helper' threads that run in the background, possibly making use of more cores.)  This still applies in the hot-off-the-press version 8.0.

In my experience, a dedicate MySQL server rarely goes past one core's worth of CPU resources.  When it does, there is probably a query that needs a better index, or something else.


What is the fastest way to verify that a json[] array is not empty? I want to verify that an json[] column the_array is not empty. For a normal array, I could check on:

the_array != '{}'

However, that does not work for a json[], as demonstrated below:

select '{}'::json[] != '{}'
ERROR:  could not identify an equality operator for type json

I could use the following check instead:

array_length(the_array, 1) != null // yes, array_length on an
                                      empty array returns null

My fear is that array_length() would iterate over the whole array to count the number of items and then return that count. In my case, I don't need the actual size of the array, I just need to know whether it is empty or not.

So, does array_length() iterate over the whole array? And if so, is there a better way to check if a json array is empty?

This is not returning a JSON Array,.

SELECT '{}'::json[]

The type json[] is not a "JSON Array", it's a SQL Array of type JSON. You should never be using this kind of structure. It's almost certainly a red flag of bad design.

What you want to check is that a Real JSON Array is not empty, you can do that with jsonb_array_length(jsonb). JSON is stored as JSON, or JSONB for binary json.

SELECT jsonb_array_length( '[]'::jsonb );

To see if it's empty,

SELECT jsonb_array_length( '[]'::jsonb ) = 0;

Warning, this just counts those elements, they may be null,

SELECT jsonb_array_length( '[null]'::jsonb );

So you may want to call jsonb_strip_nulls() on the array first.


Postgresql select row with minimum value by certain column? I've data like this

id  student  grade subject
-----------------------------
0   Amy      5     Art History
1   Amy      2     Ethics
2   Jhon     7     Calculus
3   Joe      4     Poetry
4   Joe      2     Cooking
5   Jhon     2     PE

Find out everyone's lowest scores

Results
--------------
1   Amy      2     Ethics
5   Jhon     2     PE
4   Joe      2     Cooking

Tried Solution

SELECT *, min(grade)
FROM data
GROUP BY student

I get error must appear in the GROUP BY clause or be used in an aggregate...

If you're not worrying about ties, then use the PostgreSQL extension for DISTINCT ON

SELECT DISTINCT ON (id) id, student, grade, subject
FROM table
ORDER BY id, grade;


How to choose a good value for MySQL relay-log-space-limit? I just read this blog post about relay-log-space-limit on MySQL. I am convinced that the 'unlimited' default is indeed not very good. But how do I choose a good value for it?

We are using master-master circular replication if that would matter.

That would depend on four(4) factors

How much diskspace you are willing to have for relay logs
If you have expire_logs_days set to a nonzero value on the Master
How quickly you react to any monitoring of Relay_Log_Space from SHOW SLAVE STATUS\G (See my Feb 09, 2012 post Programatically testing slave status - What fields in SHOW SLAVE STATUS indicated slave status is OK / in ERROR? on relay_log_space_limit)
How quickly you react to broken replication

If Slave_IO_Running is No
If Slave_SQL_Running is No

With Relay_Log_Space reaching relay_log_space_limit no log rotation occurs until every SQL statement in the oldest relay is processed.

What if the SQL thread breaks due to some SQL error? You need to resolve the error with the number of days set by expire_logs_days. If this is missed, when replication is fixed, the SQL that was downloaded to relay logs but rotated away from the Master will not make it the Slave. If not fixed, relay logs just grow because the IO thread would still be up and collect new SQL from the Master.

What if the IO thread breaks due to some communication error? This could be more critical if the last relay log gets logically corrupted (a possibility). Once Communication between Master and Slave is known to be OK, you must get

Relay_Master_Log_File (like mysql-bin.000128)
Exec_Master_Log_Pos (like 82323232)

from SHOW SLAVE STATUS\G. Use these commands to cleanup

STOP SLAVE;
CHANGE MASTER TO
MASTER_LOG_FILE='mysql-bin.000128',
MASTER_LOG_POS=82323232;
START SLAVE;

This will erase all relay logs and start over with a fresh set.

EXAMPLE

I have a DB Hosting client that generates a full 1GB binary log every 18 minutes (Yes, 80GB a day). Since client has expire_logs_days = 3, client has 240 GB a day.

Picture the above scenarios and trying to deal with them without have the right alerts in place or enough diskspace of relay logs. This gets more complicated since with Circular Replication because binary logs and relay logs share the same space by default. Perhaps stored binary logs on a separate disk can help mitigate some diskspace issues.

RECOMMENDATION

If you can get to fix replication errors quickly or if diskspace is at a premium, you could set relay_log_space_limit low (something like 10G)

You may want to determine how much space you crank for binary logs in one hour and set relay_log_space_limit to something like 12 hours worth.


Collecting VLF information on all versions of SQL server Based on my monitoring collection, i am having an issue while collecting info regarding how many VLF' i have for # of databases in SQL instance.
1st issue - Is there a single script which can be used from 2012 - 2019 SQL versions as standard to get the VLF info across all databases in an instance
2nd issue - how can i get 1st done with least privilege because login we use to collect info is not sysadmin and cant be. So how can i achieve this?
Much appreciated, thanks!

The first problem goes away if you solve the second with a Signed Stored Procedure installed in Master.  If there are any version differences required, you can have different versions of the procedure for different SQL Server versions.
Then the script is a just a call to the procedure.
exec sp_GatherVLFStats


Is it ever harmful to set a database owner in SQL Server? There are a bunch of databases on one of our SQL servers that have no owner.  Generally speaking, is it harmful to give them one?  For example:

USE dbName;
GO

EXEC sp_changedbowner 'sa';
GO

I know sa may not be the best choice, but it is just an example.  My primary concern is whether or not creating an owner when there is none may cause issues with software that can currently connect OK.

You should be using DDL instead of backward compatibility stored procedures:

ALTER AUTHORIZATION ON DATABASE::dbName TO sa;

And the owner of the database (never mind sa) should probably not be the account that your applications use, so this should not really have any effect on your applications. If it does then you should update the connection strings your application uses (which shouldn't be a major change) and make sure the account has sufficient - but not God - privileges.


How to export oracle database version 12.1.0.2.0 with expdp Release 12.2.0.1.0 I am running following command to dump my database

./expdp username/password@//host:port/database directory=/home/ubuntu/oracle/instantclient_12_2 dumpfile=dumpfilename.dmp content=METADATA_ONLY

and getting following error

Export: Release 12.2.0.1.0 - Production on Fri Sep 14 06:14:28 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
UDE-00018: Data Pump client is incompatible with database version 12.1.0.2.0

I tried searching and there were suggestion to use VERSION parameter which I did still no success.

Does anyone have idea what should i do to overcome the issue?

I cant use instantclient-tools-macos.x32-12.1.0.2.0.zip as it doesnt have expdp command.

Are you sure about the target and source version?
I did here and worked right.
My source DB version was 12.1.0.2.0 and the target was 12.2.0.1.0.
Please, see my example below:
$ cat myfile.par
directory=DUMP
dumpfile=MYFILE.DMP
logfile=MYFILE.LOG
content=ALL
schemas="DIEGO","HR","USER"

$ expdp user@mypdb parfile=myfile.par

Exporting and Importing Between Different Database Releases

In an upgrade situation, when the target release of a Data Pump-based migration is higher than the source, the VERSION parameter typically does not have to be specified because all objects in the source database will be compatible with the higher target release. An exception is when an entire Oracle Database 11g (release 11.2.0.3 or higher) is exported in preparation for importing into Oracle Database 12c Release 1 (12.1.0.1) or later. In this case, explicitly specify VERSION=12 in conjunction with FULL=YES in order to include a complete set of Oracle internal component metadata.
In a downgrade situation, when the target release of a Data Pump-based migration is lower than the source, the VERSION parameter should be explicitly specified to be the same version as the target. An exception is when the target release version is the same as the value of the COMPATIBLE initialization parameter on the source system; then VERSION does not need to be specified. In general however, Data Pump import cannot read dump file sets created by an Oracle release that is newer than the current release unless the VERSION parameter is explicitly specified.


Moving an ibd file temporarily to free up disk space I want to optimize an InnoDB table but I do not have enough disk space. I have enabled innodb_file_per_table, so each table has *.ibd for each of their partitions. For example:

512MB /var/lib/mysql/ib_logfile0
512MB /var/lib/mysql/ib_logfile1
 70GB /var/lib/mysql/ibdata1
100GB /var/lib/mysql/dbname/table_a.ibd    (*)
100GB /var/lib/mysql/dbname/table_b.ibd    (*)
 12KB /var/lib/mysql/dbname/table_a.frm
 12KB /var/lib/mysql/dbname/table_b.frm
  4KB /var/lib/mysql/dbname/table_a.TRG
  4KB /var/lib/mysql/dbname/table_b.TRG

Is it okay to move table_a.ibd to another disk, optmize table_b, and move table_a.ibd back to its original place? I don't want to lose any data, or break anything.

MySQL server version 5.1.73

It would make more sense to me to take a backup, remove the table instead of moving it, perform your optimize and then restore the table you dropped. This would avoid moving tables around unnecessarily and leaving your database in an incoherent state. You would also want to prevent incoming connections from the outside by putting bind-address = 127.0.0.1 in my.cnf and then restarting mysql.

Backup your whole database first with mysqldump

mysqldump
--single-transaction \
--routines \
-u root -p nameofdatabase | gzip -9 > nameofdatabase.gz

--single-transaction is the safest way to backup innodb

--routines backsup stored procedures.

gzip just compresses the backup nicely, you can open it backup with zcat.

1: Backup the table in question

mysqldump
--single-transaction \
-u root -p nameofdatabase nameoftable | gzip -9 > nameoftable.gz

2: Make sure the backup you created of the table has your data with zcat

zcat nameoftable.gz | less

Drop the table

mysql> drop table nameoftable;

3: Perform your maintenance with optimize

4: Restore the table

zcat nameoftable.gz | mysql -u root -p nameofdatabase


Grant execute on xp_logevent to sa using sa user to do it Running a SQL Server 2012 CLR stored procedure with this code:

const string executeLog = "EXEC master..xp_logevent @errorNumber, @message, informational";
using (SqlCommand cmd = new SqlCommand(executeLog, connection))
{
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("@errorNumber", 60001);
    cmd.Parameters.AddWithValue("@message", url);

    cmd.ExecuteScalar();
}

I get this error:

  System.Data.SqlClient.SqlException: The EXECUTE permission was denied
  on the object 'xp_logevent', database 'mssqlsystemresource', schema
  'sys'.

I've tried to do this as sa user:

grant execute on xp_logevent to sa

But I get another error:

  You can not find user 'sa' that does not exist or the user does not
  have permission.

How can I grant execute permissions to sa user?

How can I grant execute permissions to sa user?

You don't need to grant execute anything to sa as sa is in the sysadmin server role and can pretty much do anything. Hence, if you are getting an error executing xp_logevent, then you aren't logging in as sa. Is the connection string using context connection = true; or a regular / external connection? If using context connection = true; then the permissions are based on whoever is executing the SQLCLR stored procedure. If using a regular connection, the permissions are based on the logon account for the SQL Server NT Service (MSSQLSERVER / SQLEXPRESS if it is a default instance) by default, else it can be the permissions of whoever is executing the SQLCLR stored procedure if code has been added to do impersonation.

So, how is the SqlConnection being made? The answer to that question will help determine the appropriate fix. If this SQLCLR stored procedure is to be called by anyone (meaning, Users who are not necessarily in the db_owner database role or the sysadmin server role), then you can do the following to grant this permission to the smallest scope possible.

In the database that contains the SQLCLR stored procedure:

Create an Asymmetric Key (see CREATE ASYMMETRIC KEY)
Create a User from that Asymmetric Key (see CREATE USER)
Add this new User to the db_owner Database Role
Create a T-SQL Stored Procedure (maybe call it dbo.LogEvent) that accepts the two parameters you are passing in (@errorNumber and @message) and simply executes EXEC xp_logevent @errorNumber, @message, informational;
Grant EXECUTE on this new Stored Procedure to any Users and/or Database Roles that will be calling the SQLCLR Stored Procedure
Sign this new Stored Procedure with the same Asymmetric Key used to create the new User (see ADD SIGNATURE)
Update your SQLCLR code to execute this new T-SQL Stored Procedure (i.e. dbo.LogEvent) instead of xp_logevent

You can then change your using statement to have new SqlCommand("dbo.LogEvent", connection)
then change cmd.CommandType = CommandType.Text; to be cmd.CommandType = CommandType.StoredProcedure;
remove the const string executeLog line

Other notes:

Please do not use AddWithValue as there are issues with it. Instead, just create the SqlParameter with an explicitly set datatype and then add it to the SqlCommand.SqlParameters collection.
No need to call ExecuteScalar if you are not capturing something from a result set. Instead, call ExecuteNonQuery


If the standby server is promoted to master, will it work without any replica? If I have 1 master and 1 standby server, and my master server is down and the standby server is promoted to master. Does my new master work fine without a replica?
Does it act like a standalone server?

Definitely, Your new primary will act like a standalone. If your configuration file is good, you'll even be able to add the old-primary as a new secondary without any restart.

Just be sure your old primary is not running and modifying data or you'll loose some of it!

  If the primary server fails and the standby server becomes the new
  primary, and then the old primary restarts, you must have a mechanism
  for informing the old primary that it is no longer the primary. This
  is sometimes known as STONITH (Shoot The Other Node In The Head),
  which is necessary to avoid situations where both systems think they
  are the primary, which will lead to confusion and ultimately data
  loss.

You may want to read these documentation page :
https://www.postgresql.org/docs/9.1/static/warm-standby-failover.html


Pattern matching in PostgreSQL? I have a string in which need to extract the date,

Example: S01_XXX001_000_min_01_20150526T123440583.arc ,
S01_XXX001_000_20150526T123440583_min_01.arc

based on the pattern like 'Numeric T Numeric',

I am trying to extract with the below sql , can any one help me.

SQL:

select  substring('S01_ZAR001_000_min_01_20150526T123440583.arc','^\d')

select  substring('SAPCC_XX_ZZZ_BitClass_XXX_20150623T45656566456_XXX_XX_XXXXXX.arc' like '_%T%_',1,8)

select  substring('SAPCC_XX_ZZZ_BitClass_XXX_201506235T335656_XXX_XX_XXXXXX.arc','[^_]+([^0-9]{1,8})$')

SELECT SUBSTRING('XY1234Z', 'Y*([0-9]{1,3})');

select substr(
        substring('SAPCC_XX_ZZZ_Bit1T1Class_XXX_201506235T335656_XXX_XX_XXXXXX'
                  from
                  '_[0-9]+T[0-9]+_'
                 ), 2, 8
             )::date


MariaDB/InnoDB: What to do on "Locks in Status-Log but no locked table found" We migrated lately from MySQL to MariaDB.

SELECT VERSION()

  10.0.13-MariaDB-log

When executing this:

SHOW ENGINE INNODB STATUS

I get (shortened):

...
---TRANSACTION 279257007, ACTIVE 23981 sec
10 lock struct(s), heap size 2936, 18 row lock(s), undo log entries 903
MySQL thread id 119634, OS thread handle 0x7f62c96e0700, query id 48830504 XXX.XXX.XXX.X admin cleaning up
Trx read view will not see trx with id >= 279257008, sees < 279256916
TABLE LOCK table `db1`.`table_a` trx id 279257007 lock mode IX
TABLE LOCK table `db1`.`table_b` trx id 279257007 lock mode IX
...

I guess this means that this transaction was running for several hours. However, when executing SHOW OPEN TABLES, all tables show no-lock. When doing SHOW FULL PROCESSLIST, the result of the thread 119634 is:

  Command: SLEEP, State: '', Info: Null, Progress: 0

I have no idea what to do. When running a script I get lock-timeouts. But just increasing the timeout, I suspect will not help either.

Question: How can I get resolve this issue? Or, how should I tackle this?

You are right that the transaction is 'running' for several hours. This basically means you have a query that has failed to run 'commit' and is holding the transaction open.

If you are running in the default REPEATABLE READ transaction isolation level, you will also see your 'history list length' growing large in the TRANSACTION section of the SHOW ENGINE INNODB STATUS output

The lock status in SHOW OPEN TABLES, assuming you meant 'name_locked', is only for whether the name is locked for operations such as dropping or renaming the tables src

I am curious if SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS returns anything, especially if db1.table_a and db1.table_b are InnoDB tables. If they are MyISAM I would urge not using BEGIN TRANSACTION or SET autocommit=0, as you can run into some serious issues with potentially mixing InnoDB and MyISAM tables in a transaction.

Unfortunately, getting access to what that transaction has done is fairly impossible unless you're running the PERFROMANCE_SCHEMA to capture statemen. See: http://www.markleith.co.uk/2012/07/13/monitoring-processes-with-performance-schema-in-mysql-5-6/ Even still, it might not be possible for a transaction active that long depending on your server's activity and performance_schema settings.

So, you're really only left with the choice to kill the thread, which will rollback that transaction (again, assuming it's InnoDB tables being modified). Letting it run until it hits a timeout will result in the same rollback. And rollbacks might take a really long time, depending on what it's done!

I would scour the application's code-base to make sure you are closing/committing every transaction you are creating to prevent this in the future, as long-active transactions and an unruly history list length (undo space) can have a severe impact on the performance of your queries.


Autosync master and slave when master goes offline I have a master slave setup using MySQL 5.0.
I have very frequent writes to the master and slave is used only for backup purpose (no explicit writes).

In my current configuration, the slave database is synced with the master by MySQL automatically via VPN. But if the system (win 7) consisting of Master goes down for some reason, after reboot the synchronization doesn't continue from the last synced record. Instead I have to stop the services at both the ends, delete and recreate the config files, move the sql of the backup from master to slave and restart the sync. All this has to be done every time my system goes down for any reason.

Is it possible to avoid this so that whenever master system goes down, the syncing starts again as usual ?

No.

Replication will survive the VPN going down, but a crash of the master host does not reliably flush the master's binary log, so on reboot there is no guarantee that the master's Binlog Dump thread does not send the same events to the slave's I/O thread.

You should find a solution that involves the master server staying online. Failing that, you might get away with having the slave run for short periods during which the master is less likely to crash, so that you reduce the possibility of the server crashing during log transfer, but it is not a reliable solution.


Indices for a join query with datetime range Given the following query:

SELECT DISTINCT COUNT(DISTINCT "patients"."id")
FROM "patients"
INNER JOIN "transactions" ON "transactions"."patient_data_id" = "patients"."patient_data_id"
WHERE "patients"."practice_id" = 6
AND "patients"."species" = 'canine'
AND "transactions"."practice_id" = 6
AND (("transactions"."transaction_date" BETWEEN '2015-01-01' AND '2015-12-31' OR 1=0));

This query is generated by Rails, and that's where the OR 1=0 shorcut comes from.

What indices would you make sure were in place to ensure that this query runs as quickly as possible?

I'm working with a large database, where the transactions table is around 200 million records. The patients table is much smaller.

Execution time with just one query running is around 2.3 seconds, but we need to be able to run about 50 of these queries simultaneously. When that happens, they bog down incredibly, to about one query/minute.

How would you make this run faster?

For this specific query, I would create indexes on patients(practice_id, species) and transactions(patient_data_id, practice_id, transaction_date) and then rewrite the query as follows:

select count(distinct p.id)
from patients p
where p.practice_id = 6 and p.species = 'canine'
    and exists (select 1
                from transactions
                where patient_data_id = p.patient_data_id
                and practice_id = 6
                and transaction_date BETWEEN '2015-01-01' AND '2015-12-31')

The database won't go through all the transactions but will stop once it finds the first transaction that meets the conditions, as that's all you need to find out the final result (basically, patients that have at least one transaction). I also removed the first distinct as you don't need it - count will return only one row.


sql error on script execution. try deleting repository before creating repository I want to migrate my mysql databse to oracle.
I have followed the migration demo from this url http://www.oracle.com/technetwork/database/migration/connect-step-mysql-1946352.html

but I got this message when try to associate migration repository

sql error on script execution. try deleting repository before creating repository

I am using
Oracle Sql Developer v 4.1.1.19Oracle express edition 11g releas 2

see the picture bellow what I have tried step by step

step1: Creating a user named migration_repo

step 2: Creating connection named migration_repo using migration_repo user

step3: try to associate migration repository

step4: executing the command

but after few seconds I got this error message.

Now. how can I solve this? what I have done wrong?

It seems that the video tutorial missed a grant, the correct command is the following:

GRANT
    CONNECT,
    RESOURCE,
    CREATE SESSION,
    CREATE VIEW,
    CREATE MATERIALIZED VIEW
TO migration_repo
IDENTIFIED BY migration_repo;

The database must also have the USERS tablespace.
I found the previous information in this thread.


How could one monitor MySQL replication? I am not a DBA, but I am interested in knowing how replication monitoring is done on MySQL severs.

In order to know if a slave is in sync with its master, one would typically check Seconds_Behind_Master, Slave_SQL_Running, Slave_IO_Running.

There are tools available like mk-table-checksum to verify replication integrity.

My question is, how else would a DBA like to monitor replication?

Once I have asked the similar question there was ultimate Answer. Please have a look at it.

Then you will be clear with the

Seconds_Behind_Master

The Seconds_Behind_Master is not a good Parameter for monitoring the replication.

Try to use mk-heartbeat monitoring script. I use that and it's really reliable.

For documentation of mk-heartbeat, click here.

UPDATE

Try MONyog Web based application which has separate Replication tab and pretty easy to use, with lots of features

This will let you know Status of all slaves and Master on Replication tab for example: Replication topology, Master detail, Slave detail in a tabular format. Also, using Monitors page, you can get alert if seconds_behind_master lags behind certain threshold value.

You can also use the MySQL Community Replication Monitor

The community driven MySQL replication monitoring tools is a collection of scripts in SQL and other languages, to achieve the same result that others can get with expensive applications.


Unable to shrink log file due to transactional replication I have a transactional replication configured as per below:

ServerA.DBA (pub) --> ServerB.DBB (Sub)
ServerB.DBB (pub1) --> ServerC.DBC (Sub)
ServerB.DBB (pub2) --> ServerD.DBD (Sub)
ServerB.DBB (pub3) --> ServerE.DBE (Sub)
ServerB.DBB (pub4) --> ServerF.DBF (Sub)
ServerB.DBB (pub5) --> ServerG.DBG (Sub)

Today, I got an alert saying that my replication ServerA.DBA (pub) --> ServerB.DBB (Sub) is failing with below error:

  Replication-Replication Distribution Subsystem: agent failed. The
  transaction log for database is full due to 'REPLICATION'.

Upon investigation, I found that my log file was capped at 100GB on ServerB.DBB which was causing the issue. To fix this, I added a new log file as I was unable to extend the existing log file due to same error. This fixed my issue and replication caught up in some time. However, now I am unable to shrink the log file and it always shows my log file utilization as 99% and increasing. This raised some doubts in my mind whether my replication from Server B to C,D,E,F and G is actually running or not and why I am unable to shrink the log file.

Log_reuse_wait_desc on my ServerB.DBB always shows 'REPLICATION'. I ran DBCC OPENTRAN on my ServerB.DBB and it shows below:

Replicated Transaction Information:
Oldest distributed LSN     : (0:0:0)
Oldest non-distributed LSN : (10417370:9406:1)

This value is not changing since many hours now. Does it mean my replication from Server B to C,D,E,F and G is not working? However when I see in replication monitor it shows everything as fine. i.e. logreader, all distributor agents are running and moving transactions for all publications. Why I am unable to shrink the log file and what should I do to shrink the log file?

Also, want to add that CDC is not enabled on any of my DBs involved in replication. DBCC LOGINFO shows all my VLFs in use.

In case the solution i gave in comment doesn't works try this :

As you said :
Log_reuse_wait_desc on ServerB.DBB always shows 'REPLICATION'

This means Replication is causing the log not shrinking problem.

DBCC OPENTRAN gives you the traction that is blocking as output which in your case is following :

Replicated Transaction Information:
Oldest distributed LSN     : (0:0:0)
Oldest non-distributed LSN : (10417370:9406:1)

To see which database still needs to be replicated use :

DBCC loginfo

If you are sure everything is replicated to all subscribers run the following query on the each database where the replication is completed.I will repeat make sure everything is replicated before executing this query as it marks all replications as successful and your replication queue will be empty.

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

Back up the log file and then try shrinking it now it should free up the space.

Source:
https://stackoverflow.com/questions/1238491/how-to-shrink-transaction-log-in-sql-server-database-in-replication


sp_Blitz fails with collation error SQL Server version (output from select @@VERSION): Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64) (Build 14393: )

Installed sp_Blitz from Brent Ozar, stored procedure creates successfully.

Version info from the code: SELECT @Version = '7.93', @VersionDate = '20200217';

The instance has the 4 system DBs with collation SQL_Latin1_General_CP1_CI_AS and 2 user databases with collation SQL_Latin1_General_CP1_CS_AS

When I try to run sp_Blitz I get this:

  Msg 468, Level 16, State 9, Procedure sp_Blitz, Line 896 [Batch Start Line 0]
  Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Appreciate any assistance in troubleshooting (I'm mainly an Oracle guy who's been handed a SQL Server for review and remembered this as a thing that might be a good start for obvious things to start with).

Adding the resolution from the comments: the stored procedure was created in a case-sensitive user database, and it was trying to copy things into a case-sensitive TempDB.

After removing sp_Blitz from the case-sensitive user database, and installing it in master (which would then reflect the system database collations), it worked fine.


What is the maximum latency / distance mysql replication can tolerate without having errors or wrong data? I have two machines in a master - slave replication. I want to know if they are in two seperate places e.g. London, England and Tokyo, Japan are there going to be any latency issues.
What exactly is the maximum latency (if possible measured using distance) that mysql replication can tolerate before an error happens or before the two databases are different enough that the second one can give wrong data or any other error?

You should consider what distance does to any DB connection.
I have some posts from myself and others on this subject

Jul 25, 2012 : Mysql database replication on different vlan/subnet/another site (From Me)
Aug 26, 2012 : Mysql database replication on different vlan/subnet/another site (Shlomi Noach)
Aug 20, 2012 : Best solution for cross-datacenter MySQL master-slave replication (From Me)

Another member of this StackExchange, Aaron Brown, wrote a nice blog about measuring latency even with semisynchronous replication. Although his blog is 8 years old and technology has improved since, you should write the same test Aaron did to test the latency yourself. After all, London to Tokyo is intercontinental where US West to US East is within the same country. Latency should be expected, but only your testing will reveal how tolerable it will be.
Please review them and see if you can setup semisynchronous replication.
CAVEAT : if you are using Amazon RDS or Google GCP, you are at their mercy when it comes to latency.


Oracle: How do I set a context variable? Here's a user context, and a package that sets a value in it.

create or replace context t1_ctx using t1_pkg accessed globally;

create or replace package t1_pkg
as
    procedure setx;
end t1_pkg;

create or replace package body t1_pkg
as
    procedure setx is
    begin
        dbms_session.set_context('t1_ctx','x','1');
    end;
end t1_pkg;

But, it seems that I can't get the context variable set.  Both before and after the call to setx, 'x' remains NULL.

mh> select sys_context('t1_ctx', 'x') from dual;
sys_context('t1_ctx','x')
-------------------------
NULL

mh> exec t1_pkg.setx;

mh> select sys_context('t1_ctx', 'x') from dual;
sys_context('t1_ctx','x')
-------------------------
NULL                      <---- I expect this should be '1' ???

How do I set a variable in my user context?

The above works just fine.

SQL> create or replace context t1_ctx using t1_pkg accessed globally;

Context created.

SQL>

create or replace package t1_pkg
as
    procedure setx;
end t1_pkg;
/

Package created.

SQL>

create or replace package body t1_pkg
as
    procedure setx is
    begin
        dbms_session.set_context('t1_ctx','x','1');
    end;
end t1_pkg;
/

Package body created.

SQL> select sys_context('t1_ctx', 'x') from dual;

SYS_CONTEXT('T1_CTX','X')
--------------------------------------------------------------------------------

SQL> exec t1_pkg.setx;

PL/SQL procedure successfully completed.

SQL> select sys_context('t1_ctx', 'x') from dual;

SYS_CONTEXT('T1_CTX','X')
--------------------------------------------------------------------------------
1


DB2 9.5 How to get tablespace monitor information? How can I get information about tablespace in DB2 9.5?

I need to review information about if the autoresize option is enabled or disabled.

Use the TBSP_UTILIZATION administrative view - Retrieve table space configuration and utilization information. See the "Essential DB2 Health Check" for a recommendation on how to use it, especially the sections on File System Free Space and DMS Tablespace Free Space.

Example:

select substr(tbsp_name,1,10) "Name", tbsp_utilization_percent "Used%", tbsp_free_pages "FreePages", tbsp_usable_Pages "UsablePages", tbsp_used_pages "UsedPages", tbsp_page_top "HWM", tbsp_auto_resize_Enabled "AutoResize 1=Yes", tbsp_increase_size "Increase Size", tbsp_max_size "MaxSizeBytes -1=ulimited", tbsp_last_resize_failed "ResizeFailed 1=Yes", tbsp_page_size "PgSize Bytes"
from SYSIBMADM.TBSP_UTILIZATION
where tbsp_type = 'DMS';

Specifically, you are after the tbsp_increase_size field from the SYSIBMADM.TBSP_UTILIZATION table, where tbsp_type = 'DMS' as show in the query above. (The query has a few other things that are helpful in it too.)


How to load more than one MS-Access-db tables with data in SQL-Server-DB I'm just getting into SQL-server 2008 R2 ssis, so forgive me if this is a basic question. I'm building an application using integration service.

One of my computer folders contains more than one MS-Access db, want to load all of this access tables with data in SQL-Server db. To load all tables I want to use ssis. Which control of ssis will help me to load all access tables on my SQL-Server db?

I want to create (SSIS) package for importing an MS Access table into SQL Server, then running some stored procedures on this database and finally exporting the result to Excel.

I am comfortable in writing queries and working in Management Studio but I don’t know how to actually design an SSIS package. Need helping hand to do this?

If have any query please ask.

In SSMS, you can right click on your target database, select Tasks...Import Data, then choose your Access database as the Source and then choose your target SQL Server database as the Destination.


Sql Server trace? Who is running it and how to stop it? Can you please help me out in finding who is running SQL Server Trace(profiler) on Server machine.?

It looks like, some people are running trace on live system on SQL DB, which make application super slow.

We need to find out who is running that or who ran that in the past? any idea how to do this?

Thanks.

Run the SQL command below:

   select
          [Status] =
          case tr.[status]
                when 1 THEN 'Running'
                when 0 THEN 'Stopped'
          end
          ,[Default] =
                case tr.is_default
                      when 1 THEN 'System TRACE'
                      when 0 THEN 'User TRACE'
                end
           ,[login_name] = coalesce(se.login_name,se.login_name,'No reader spid')
          ,[Trace Path] = coalesce(tr.[Path],tr.[Path],'OLE DB Client Side Trace')
          from sys.traces tr
                left join sys.dm_exec_sessions se on tr.reader_spid = se.session_id

Source:- https://mssqlwiki.com/2010/04/26/how-to-find-all-the-profiler-traces-running-on-my-sql-server/


dcexec login failure I have a SQL Server 2008 R2 instance that I have been trying to get Data Collection setup on. I get everything setup, but there is no data that gets uploaded. I've traced the root of the problem to be a failure in the SQL Agent jobs where it calls dcexec.

Isolating that out, I can reproduce the issue by running:

& "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\dcexec.exe" -c -s 1 -i "localhost\MSSQLSERVER" -m 1

I run the above command from a PowerShell window that I've opened with RunAs Administrator. The account I am running the above with is a domain account that is a local administrator on the server. In addition, the account also has the sysadmin role on the SQL instance.

The error that I get is:

SSIS error. Component name: GenerateTSQLPackageTask, Code: -1073548540,
Subcomponent: Generate T-SQL Package Task,
Description: An error occurred with the following error message:
"An error occurred while verifying the result set schema against the output table schema.
The data collector cannot connect to the management data warehouse. : Login failed.
The login is from an untrusted domain and cannot be used with Windows authentication."..

The master package exited with error, previous error messages should explain the cause.

That is the same error I see in the history on the SQL Server Agent job.

Note that if I execute dcexec with a bad instance name, such as:

& "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\dcexec.exe" -c -s 1 -i "localhost\MSSQLSERVER1" -m 1

Then, as expected, I get a different error:

Failed to get the SQL server instance id from the given instance name.

Any ideas on how to successfully login to the SQL instance with dcexec?

The problem has been solved with some hints from:

Performance Data Collector in 2008
Data Collector - Cannot Find Database

When I setup Data Collection I did the following:

Setup

SSMS > serverA
Management > Data Collection
Configure Management Data Warehouse
Next
 > Check: Create or upgrade a management data warehouse
Next
New
 > Database name: DBA_MDW
OK
 > Server name: serverA.company.com
 > Database name: DBA_MDW
Next
New Login
 > Login name: DOMAIN\SQLmdw
OK
 > Check: mdw_admin
 > Check: mdw_reader
 > Check: mdw_writer
Next
Finish
Close

Configuration

Configure Management Data Warehouse
Next
 > Check: Set up data collection
Next
 > Server name: serverA.company.com
 > Database name: DBA_MDW
 > Cache directory:
Next
Finish
Close

To uninstall Data Collection, I used the script from Remove associated data collector jobs. I then followed the above setup and configuration steps, but this time under the configuration step I simply put serverA instead of serverA.company.com. That seems to have made all the difference and I'm now collecting data.


Estimate space left in Oracle I have a large Oracle table that need to be re-partition. I was wonder there is a way to know that I have enough space to create a copy of this table with new partition and swap the new one with the old one.

A rough estimate of your table size is:

select sum(BYTES) / 1024 / 1024 as SIZE_MB
from   USER_SEGMENTS
where  SEGMENT_NAME = '<tablename>';

However, see the question How do I calculate tables size in Oracle for better detail as the real size of a copied table on disk can differ from the original.
You can then check how much space you have in the tablespace using:

select TS.TABLESPACE_NAME
      ,nvl(nvl(DF.BYTES, 0) / 1024 / 1024, 0) + nvl(nvl(FS.BYTES, 0) / 1024 / 1024, 0) as AVAIL_SPC_MB
from   DBA_TABLESPACES TS
       left join (select   TABLESPACE_NAME, (sum(MAXBYTES) - sum(BYTES)) BYTES
                  from     DBA_DATA_FILES
                  group by TABLESPACE_NAME) DF
         on TS.TABLESPACE_NAME = DF.TABLESPACE_NAME
       left join (select   TABLESPACE_NAME, sum(BYTES) BYTES
                  from     DBA_FREE_SPACE
                  group by TABLESPACE_NAME) FS
         on TS.TABLESPACE_NAME = FS.TABLESPACE_NAME
       left join DBA_TABLES TAB
         on TS.TABLESPACE_NAME = TAB.TABLESPACE_NAME
where  TAB.TABLE_NAME = '<tablename>';

If you don't have enough space then check your other tablespaces as you can temporarily create the table in there if you have permissions.


SQL AlwaysOn Availability 2 synchronous nodes on either side of an asynchronous link Say we have two datacentres, DC-A and DC-B. Say in each DC, there are two SQL servers. DC-A has SQL-A1 and SQL-A2 and DC-B has SQL-B1 and SQL-B2.

Is it possible to have SQL with an AlwaysOn availability group running synchronously within each datacentre, and an asynchronous push from the primary site to the secondary? Such that:

SQL-A1 and SQL-A2 are synchronously replicating
and
SQL-B1 and SQL-B2 are synchronously replicating
and
SQL servers in DC-A are asynchronously replicating their synchronous copies across to DC-B SQL servers.

Is this possible?

No that is not possible.  The commit mode (sync/async) is between primary and secondary logical roles, and not between secondary and secondary (which is why are you aiming for).

I'm not sure what you'd even gain from that.  All your logical design would be adding (even if it was possible) would be a second hop of log blocks for SQL-B2.  You would want your data to go from SQL-A1 (primary) -async-> SQL-B1 (secondary) -sync-> SQL-B2 (secondary).  Like I said before, that isn't possible.  But what is possible would be SQL-A1 (primary) -async-> SQL-B2 (secondary).

The only thing your projected design would optimize would be network traffic between the data centers.  To reiterate, this discussion is just theory only.  Functionality dictates moving forward.


Verify Query Request Received I have a software system which queries data via OLEDB from an external SQL Server 2005. The query request is sent every 15 minutes. I want to verify that the SQL Server is receiving the query requests. How can I?

Create a SQL Trace (server-side) and capture the SQL:StmtCompleted event.  You can filter on the TextData column to ensure you're only getting the query that you are looking to ensure is happening.

The easiest way to create a server-side trace would be to create it in SQL Server Profiler (which is innately a client-side trace), do a quick start and stop and script out the trace definition and run it in SSMS.  Please see this TechNet reference on Server-Side Tracing and Collection for an exhaustive reference on the details how to do this.


Specific format/formula when changing column datatype Can one modify a column's datatype using a very specific conversion format?

For example, when converting from datetime to varchar I'd like to use the yyyy-MM-dd hh:mm:ss format, not the system default which is 109 I believe.

Is there any way to achieve this without creating a temporary column?

First and foremost:

Why are you storing dates as text?

Second, as I pointed out in comments, there is an accepted answer of Aaron Bertrand that states: 'Sorry, not possible', you cannot change the default conversion behaviour. And that is why I flagged this question as duplicated.

And third, having a look at Microsoft SQL Server 2008 Internals, at chapter: Internals of Altering Tables. (bold is mine)

  Another negative side effect of altering tables happens when a
  column is altered to increase its length. In this case, the old
  column is not actually replaced. Rather, a new column is added to
  the table, and DBCC PAGE shows you that the old data is still there.
  I’ll let you explore the page dumps for this situation on your own,
  but we can see some of this unexpected behaviour by just looking at the
  column offsets using the column detail query that I showed you earlier
  in this chapter.

  Another drawback to the behaviour of SQL Server in not actually dropping the old column is that we are now more severely limited in the size of the row. The row size now includes the old column, which is no longer usable or visible (unless you use DBCC PAGE).

You can check the length by using DATALENGTH function:

DECLARE @dt datetime = GETDATE();
DECLARE @sdt varchar(20) = CONVERT(varchar, GETDATE(), 20);

SELECT @dt AS dt, DATALENGTH(@dt) AS dt_lenght,
       @sdt AS sdt, DATALENGTH(@sdt) AS sdt_lenght
GO

dt                      | dt_length | sdt                 | sdt_length
:---------------------- | --------: | :------------------ | ---------:
2020-05-30 10:50:51.587 |         8 | 2020-05-30 10:50:51 |         19

db<>fiddle here


PostgreSQL text search email parser does not handle + correctly Default email parser does not parse emails with plus symbol (tested on 11.5):

For test@example.com it works fine:

select ts_debug('test@example.com');
+-----------------------------------------------------------------------------+
| ts_debug                                                                    |
|-----------------------------------------------------------------------------|
| (email,"Email address",test@example.com,{simple},simple,{test@example.com}) |
+-----------------------------------------------------------------------------+

But for test+fragment@example.com it treats + as a token separator:

select ts_debug('test+fragment@example.com');
+-------------------------------------------------------------------------------------+
| ts_debug                                                                            |
|-------------------------------------------------------------------------------------|
| (asciiword,"Word, all ASCII",test,{english_stem},english_stem,{test})               |
| (blank,"Space symbols",+,{},,)                                                      |
| (email,"Email address",fragment@example.com,{simple},simple,{fragment@example.com}) |
+-------------------------------------------------------------------------------------+

Is there an easy way to fix the email parser? I know that it is possible to implement custom parser but it sounds like an overkill for this issue. Also I can't find relevant example of such parser.

I don't have good news for you.  If you could convince the PostgreSQL hackers that such a change is desirable, it probably still would not be made in the default parser. Because then a run of pg_upgrade would invalidate all existing FTS indexes, and they would have to be rebuilt.  Even if the change in behavior was acceptable to impose between major versions, the index invalidation probably would not be.

You could fork the default parser to implement your own, but that is unreasonably difficult.  While the CREATE TEXT SEARCH PARSER doc does document the names of the functions you need to implement, it gives no inkling into what those functions need to do, or even what their call signature is.  It just refers you elsewhere, which in turn describes the behavior of the default parser, but in a way which is not useful for trying to implement your own parser. Finally, "contrib" offers no examples from which you could find inspiration, and I am not aware of reasonable examples anywhere else, either.  There is some very old documentation, but it isn't clear how relevant it still is.

If you come up with a more satisfying solution, please do post your own answer about it.

Is your goal to have '+' behave the same as it currently does in every case except where it appears in an email?  Or would you want '+' to be treated in general like a word character, not just in emails?  Is '+' the only thing that is giving you trouble?  Some of the other allegedly-valid examples on wiki also are not processed correctly, but those may be of no consequence to you.

Before starting to implement my parser, I would test to see if I could implement something acceptable at an earlier level, for example by just replacing '+' with '.' in both the documents and queries.


Can Oracle SQL developer export data as a set of Update commands? Oracle SQL developer gives us the option of exporting data. But it only gives us the option to export as a set of Insert statements.

I have two databases with a mirrored table. One of them has some column data missing from it. I tried truncating the data from that table but it has foreign keys associated and SQL doesn't let me drop it.

Is there a way to export the data from the larger/fuller table in the form of "Update Table..." commands? I tried googling first, failed. I bet there's a solution for this out there.

If nothing else, I am thinking of making a small python script that modifies the Insert command set into an Update command set after export.

What you want is MERGE.

See Oracle docs for MERGE . Allows you determine if a row should be updated or inserted.

SQL Developer doesn't support generating MERGE statements today. You can go vote  for that feature to make a future version though!


how to generate scripts for database objects specific to an application? There was a application which, earlier, pointed to a database. It used a specific set of tables. Those tables had triggers, constraints, etc. There were also stored procs which insert data into these tables.

There was a new application built on the same lines (with minimal changes), but to make that application work in our test box, we had to backup the existing database, and restore that one with a different name. The new app database pointed to this newly restored db.

But the restored db has objects which were totally unnecessary.

Now I am faced with the task of creating deployment scripts that should run in our production box, and create the database (with the required objects for the new app to work).

Are there any tools that would make this job easier?

PS: My test box has a sql server 2005 db. Where as the production box has a sql server 2000 db.

Even though you did not say it explicitly, I guess you removed the unnecessary objects from the restored database and now want to migrate the structure, and maybe some of the data, from the new (restored plus modifications) database to Production.

If this is correct, then I would recommend Red Gate’s SQL Compare (Structure) and SQL Data Compare (Data, of course) products.  Both of these work very well and are able to generate change scripts which you may modify further or let the products execute the changes directly.  Both products work by comparing the "From" and "To" databases to determine the needed changes.  I am not sure if both products are aware of version differences (Never had a downgrade issue) so I would check the generated scripts to see if they add anything which is not compatible with SQL Server 2000.  Also, both products are available on a trial basis.

If you are a user of Visual Studio and have the appropriate version level (With VS 2010, I believe it is Pro and higher), you might also consider using Visual Studio for Database Professionals.


Index not used in "not like" query I am using PostgreSQL 9.4 and having tough time with slow queries. Some of these use "like" and others use "not like" searches. For like searches I could resolve by using trigram indexes, but those with not like are a headache.

e.g.

select * from mytable where user_code not like 'TE%'

EXPLAIN ANALYZE says "Seq scan". How can I make Postgres use index in such cases?

The query with NOT LIKE is not using any index because the optimizer decides that it is cheaper to do a sequential scan. Nothing unexpected here, it is probably much cheaper.

The condition user_code LIKE 'TE%' will probably keep a very small number of rows (say 1%), so it is good to find this small number by using an index.

But the query with user_code NOT LIKE 'TE%' will probably return a big number of rows (99% if LIKE returns 1%) so it is easier to do a sequential scan and just remove the 1% afterwards. If an index was used, it would have to do the index seek and then still go and read (that 99% of rows) from the table anyway, so almost the entire heap.


PostgreSQL: Switching schema name in a loop I am trying to switch the schema name dynamically to do some data manipulation.
I am running the following.
DO $$
DECLARE schema_name TEXT;
BEGIN
FOR schema_name in
        select "name" from public.tenants
    Loop
        RAISE NOTICE 'The current schema is %', schema_name;
        INSERT INTO schema_name.campuses (select * from public.campuses where code = schema_name);
    End Loop;
END $$;

However I am presented with the following

NOTICE:  The current schema is NTU
ERROR:  relation "schema_name.campuses" does not exist LINE 1: INSERT
INTO schema_name.campuses (select * from public.campu...
^ QUERY:  INSERT INTO schema_name.campuses (select * from public.campuses where code = schema_name) CONTEXT:  PL/pgSQL function inline_code_block line 8 at SQL statement SQL state: 42P01

I have tried wrapping the schema_name in "" as well as [].
Is there a solution to this as my googling is not helping me at present.

This happens when you raise a WARNING try a INFO instead
Additional you need dynamic sql see https://www.postgresql.org/docs/13/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
DO $$
DECLARE schema_name TEXT;
BEGIN
SET client_min_messages TO INFO;
FOR schema_name in
        select "name" from test.public.tenants
    Loop
        RAISE info 'The current schema is %', schema_name;
        EXECUTE CONCAT('INSERT INTO ', schema_name , '.campuses (select * from public.campuses where code =  $1);')
   USING  schema_name;
    End Loop;
END $$;


get number of events per day from profiler trace I am trying to do something that I thought would be simple but turns out it is not (for me).
I have 5 days of profiler trace files that I have imported to a single table.
I want to query the table and get the number of events per day.

I just want a simple result that looks something like this

StartTime count
2016-02-03 35600
2016-02-02 44000
2016-02-01 55055 etc...

so I found some examples but they don't seem to work.

select cast(StartTime as DATE) as StartTime, count(*) as [count]
from trace_table
group by StartTime

This is not working as I expected.  It's bringing back over five million rows with a count of only small digits for a count number.  I think I should get back five rows with a count for each day.  If there is an easier way to accomplish this I am very interested.
Thanks,
Don

Try:

GROUP BY cast(StartTime as DATE)
ORDER BY cast(StartTime as DATE);

Grouping by the raw, unconverted value means you are guaranteed to have a row for every unique StartTime. (And it probably makes sense to sort that way, otherwise your rows could come back in an arbitrary and unintuitive order.)


Improving query to generate a million empty rows I have been asked to review a process that sadly is highly inefficient but we are held in the short term to the design.

We currently generate based on a range a set of rows inside a table to hold future receipts. The ReceiptData table for every Receipt id has its rows generated first and as the receipts are use the next is populated.  Again this process for the short term is in stone.

When a customer adds a receipt we then need to generate a million rows within the receiptData table that will then be used in the future.

DECLARE @tempT TABLE
(
    number INT
)
DECLARE @recId AS INT -- represents an id a parent table id
DECLARE @receiptStart AS INT --represents where in the sequence to begin normmaly 1 but could be 1500 etc.
DECLARE @receiptEnd AS INT -- represents then end in relation to the start normally 999999 but could be 456789, defined by customer

SET @recId = 12
SET @receiptStart = 120
SET @receiptEnd = 999999

    DECLARE @iCounter AS INT = @receiptStart

WHILE ( @iCounter <= @receiptEnd)
  BEGIN
      INSERT INTO @tempT
      VALUES      (@iCounter)
      SET @iCounter += 1
  END

INSERT receiptdata
       (recid,
        receiptnum,
        negativereceipt)
SELECT @recID,
       Cast(number AS VARCHAR),
       0
FROM   @tempT
WHERE  NOT EXISTS ( SELECT  1
                    FROM    receiptdata
                    WHERE   ffid = @recId
                            AND receiptnum = number
                  )

I ran this and it took over 13 minutes to process...there has to be a better way.  The initial population of @tempT takes over 30 seconds; the insert into ReceiptData takes the majority of the time.

The annoying piece of this is the receiptnum within the receiptData table is a varchar.  while we plan out a future design we are stuck with all of this in the short term.

Any suggestions? it it rather straight forward but insanely slow.
we usually go with ranged from 1 to 999999 for receiptStart and receiptend.

To generate the 1 million rows for @tempT you could use a cartesian product of the master..spt_values table, like such:

declare @tempT table (number int)

declare @maxno int
select  @maxno = 1000000

insert into @tempT
select top(@maxno) row_number() over(order by v1.number)
from   master..spt_values v1,
       master..spt_values v2

select min(number),max(number),count(*) from @tempT

(No column name) (No column name) (No column name)
---------------- ---------------- ----------------
               1          1000000          1000000

Takes about 2 seconds to run this dbfiddle

NOTE: master..spt_values should have at least a few thousand records so a 2-way cartesian product should be sufficient to generate 1 million rows; if you need to go a lot higher you could provide an additional ,master..sp_values v3.

You could likely push this cartesian product into your insert receptdata / select query thus eliminating the requirement for the @tempT table variable, ymmv ...

The next (much bigger) performance issue is likely the 1 million not exists() operations.

Without knowing a bit more about the relationship between @recId and receiptdata.ffid it's kinda hard to address this query, eg, do you really need to test all 1 million @tempT rows against receiptdata?

Also, do you have an index on receiptdata the optimizer can use for the not exists() operations?


Get max url from PostgreSQL statement I have a very complex query which generates a view in PostgreSQL.
Now the query returns duplicated rows, well almost duplicated: one field, the "url" column is different.

Now we still want to group those "almost duplicated" rows. We can do this by skipping the url column, but we need that column. We already got the latest date by using this statement:

max("cdata".date) AS date

So we would like to do the same thing, but with url. So get the latest row by date and show that url.

Is this possible?

Like I said, the query is complex, so creating a subquery which executes the same query again with some changes wouldn't be a good idea. Also I don't have a primary key, so that makes it even more difficult. I hope you guys have some kind of idea to fix this issue.

The current query:

CREATE MATERIALIZED VIEW cdataview AS
 SELECT md5(ROW("cdata".acc_id, "cdata".adgcid, "cdata".adgid, "cdata".kid, "cdata".sterm, "cdata".mtype)::text) AS id,
    "cdata".acc_id,
    "cdata".kword,
    "cdata".kid,
    "cdata".url,
    "cdata".adgcid,
    "cdata".adgname,
    "cdata".adgid,
    "cdata".adgstatus,
    "cdata".sterm,
    "cdata".mtype,
    sum("cdata".cost) AS total_cost,
    sum("cdata".cval) AS cval,
    sum("cdata".impr) AS impr,
    sum("cdata".clicks) AS clicks,
    sum("cdata".convcl) AS convcl,
    ( SELECT accs.name
           FROM accs
          WHERE accs.id = "cdata".acc_id) AS accname,
    ( SELECT cmps.name
           FROM cmps
          WHERE cmps.id = "cdata".adgcid) AS campname,
    ( SELECT kpr.qscore
           FROM kpr
          WHERE kpr.id = "cdata".kid
          ORDER BY kpr.date DESC
         LIMIT 1) AS qscore,
    round(
        CASE
            WHEN sum("cdata".impr) = 0 THEN 0::numeric
            ELSE sum("cdata".clicks)::numeric / sum("cdata".impr)::numeric
        END, 3) AS ctr,
    round(
        CASE
            WHEN sum("cdata".clicks) = 0 THEN 0::numeric
            ELSE sum("cdata".cost) / sum("cdata".clicks)::numeric
        END, 3) AS cpc,
    round(
        CASE
            WHEN sum("cdata".convcl) = 0::numeric THEN 0::numeric
            ELSE sum("cdata".cost) / sum("cdata".convcl)
        END, 3) AS cpa,
    round(
        CASE
            WHEN sum("cdata".impr) = 0 THEN 0::numeric
            ELSE sum("cdata".impr::numeric * "cdata".avgpos) / sum("cdata".impr)::numeric
        END, 2) AS avgpos,
    max("cdata".date) AS date
   FROM sqpr "cdata"
     LEFT JOIN kpr kpr ON "cdata".kid = kpr.id AND kpr.date = (( SELECT max(kpr.date) AS max
           FROM kpr
          WHERE kpr.id = "cdata".kid))
  GROUP BY "cdata".kword, "cdata".kid, "cdata".acc_id, "cdata".adgstatus, "cdata".adgcid, "cdata".adgname, "cdata".adgid, "cdata".sterm, "cdata".mtype, "cdata".url

Guess I have figured it out.

By first selecting the first url:

select min("cdata".url) as url

And then order the query by date:

order by date asc

It does return the right url and it combines the rest of the columns.

EDIT:
Above answer did not work as expected. This didn't either: https://wiki.postgresql.org/wiki/First/last_(aggregate)

EDIT 2:
Alright, I ran several tests and this seems to go well:

last("cdata".url) AS url

in combination with the max(date). So if someone has a better solution, please let me know :)

EDIT 3:
Alright, I think I just fixed it. I just select the url by executing a subquery and select the right row by comparing all "group by" fields, guess this is the best solution to my problem:

(SELECT url from sqpr as cd where"cd".kword = "cdata".kword and cd.kid="cdata".kid and cd.accid="cdata".accid and cd. adgstatus ="cdata". adgstatus and cd. adgcid ="cdata". adgcid and cd. adgname ="cdata". adgname and cd. adgid ="cdata". adgid and cd. sterm ="cdata". sterm and cd. mtype ="cdata". mtype ORDER BY date DESC LIMIT 1) AS url


Need to choose ASM performance for production database We have a 1.5 TB of data (nonasm) and we plan to move into ASM by choose normal redundancy.

Normal Redundancy ==> Two Way Mirroring , So we need 3TB data storage for 1.5TB data including mirroring(SAN disk).

Please clarify below:

A) If we choose single RAW disk 3TB.

How many disk group we can split?

either each 500gb into 6 parts? (1 disk group)

This will be efficient for asm operation?

If the failure group we need to specify mandatory?

In our case, we have a single 3TB disk for storage, so no need to specify failure group right?

From blogs,

((For small numbers of disks (<20) it is usually best to use the default Failure Group creation that puts every disk in its own Failure Group))

so It can take by default itself. So no need to specify right?

B) If we have separate two disks each 1.5 TB

How many disk group we can split, either each 500gb into 3 parts?
(Total 2 disk group a and b)

If the failure group we need to specify mandatory?

Shall we specify manually failure group of A disk group is B and failure group of B is A?

Its a better performance?

If you cause a failure group specification it causes a spindle failure?

Either A or B case, which one is better performance approach for I/O operation in ASM?

ASM is not magic.

Each disk has a maximum number of IOPS it can support.  Each disk has a maximum throughput.  The more spindles (HDDs) you have, the more I/O you can get.  If one disk decides to take a permanent vacation and you don't have data on another disk.... I really hope you have viable tested backups.

From my tests, partitioning a single HDD into multiple LUNs and then using those LUNs for ASM will have a negative effect (albeit, ~-1%).

If you want performance, you will buy 6+ 500GB HDD (or SSD).  Each disk will have 1 LUN - the whole thing.  ASM will take care of the stripe and mirror.


Static archive database: SQL Server can't shrink it and leaves ~400G of unallocated space We are having an issue with our databases. Here's what happens:

All databases have around 50% of unallocated space and DBCC SHRINKDATABASE/SHRINKFILE won't reclaim the space back. These are archive databases which have been recently cleaned up and are supposed to be read-only, so performance is not an issue (yes, I'm fully aware of shrink and clustered indexes fragmentation and I couldn't care less). The main objective here is to have all datafiles reduced to save $$ on storage space. Again, I don't care about performance, these data files must be reduced at all costs! I'm talking about databases at 800GB, 1,5TB and 2,1TB. So nothing ordinary or small.

So:

shrink database shows ~50% of wasted space
a post-shrink database with mandatory page reorganization (NOTRUNCATE) and mandatory 1% free space keeps showing with 50%
unallocated space
so an 800 GB database, appears in sp_spaceused as having ~410 GB of unallocated space, even after the shrink
a shrink file acknowledged the database has only 1% of free space
when we started using SELECT INTO to migrate tables to another database, we started noticing that the original tables were occupying hundreds of thousands of pages, but after migration they were taking no more than a few hundreds of pages
DBCC also shows that the original database (before SELECT INTO) uses an huge amount of extents

My analysis of this is the following:

shrink is not freeing unallocated space
SELECT INTO yes frees up a lot of space

What I would like some help with:

why shrink isn't working
why SELECT INTO works
why the original database has so much wasted space (yes, it had auto-growth enabled for a long time before becoming an archive)

We're already looking at things like fill-factor, page and extents usage, but what we would like to have is at least an explanation for so many lost space and how to reclaim it without SELECT-INTO all terabytes of data. Any help would be appreciated.

There's 2 potential things at play here.  Shrink's algo takes the last page and moves it to the first empty spot, this creates massive fragmentation and could also create forwarding records.  The better idea is to rebuild your database on a different filegroup using a clustered index that's already defined pre data population.

The other item could be a ghost clean up bug that was around in 2008.  I don't know what version you're running, but if it is 2008, ensure you are fully up to date on patches.

https://codingsight.com/moving-existing-table-from-primary-filegroup-to-different-filegroup/


Can I use MariaDB as a replication slave for MySQL? I've been a MySQL user/administrator for many years and I've been considering a switch to MariaDB. We have a statement-replicated setup with a MySQL slave and I've been thinking that one way to ease-into a MariaDB environment might be to swap the slave out for MariaDB until we're comfortable, then switch the master to MariaDB.

Is that something that is possible? I suspect it is, since the replication interface is fairly simple, especially for a statement-replicated setup.

Is that something that makes any sense?

UPDATE
I happen to be using MySQL 5.5 as the master and MySQL 5.6 as the replicated slave in my current environment, just in case that has any bearing on anything. But I'm curious if different versions might be better or worse for a mixed-vendor setup.

Yes, this is possible, and probably makes sense. See e.g. Replicating from MySQL Master to MariaDB Slave. So with MySQL 5.5 as the master, you can use MariaDB 5.5 or later as the slave.


Creating backups on Alwayson without differential backups I went through this link on stack exchange and also Brent Ozar's . I don't plan to create any differential backups for the AAG group. Can you please let me know if enabling this option should work. I will be adding a new database for an application, and I then plan to create and schedule Ola's scripts. Is it true that for new databases, only full backup happens and not the log backup? What can I do to resolve the issue? Please let know. Thanks!

. I don't plan to create any differential backups for the AAG group. Can you please let me know if enabling this option should work.

Yes, it'll work.

  Is it true that for new databases, only full backup happens and not the log backup?

I don't understand the question. If it's specific to Ola's script then I do not know, if it's a SQL Server question then you must take at least one full backup and one log backup before adding the database to the AG, so the answer would be no it isn't true.

  What can I do to resolve the issue?

I currently do not see an issue. It's a valid backup strategy, now where or not it meets your recovery SLA in a disaster is another question.


Indications from obscenely high Operator Costs in Query Plan? I have a monthly ETL process that's too complicated to explain here, but basically we get a large amount of customer data, bulk load it into various tables, then run a series of build processes.

The volume of total data is large, we add 25-30m records monthly to the largest table, which has around 700m records total.  There are support tables from 2m-300m records each as well.  Total data for this set is around 3.5TB.

This month, some processes are taking exponentially longer than usual.  One example is a proc that ordinarily completes in 30 minutes took around 40 hours before I killed it (it still hadn't completed).

Not all processes are affected, and some run even faster than usual.

Obviously the root cause is beyond the scope of something I can ask here, but there is something that bothers me:

We have actual query plans showing in the millions of % for individual operators.

Like a clustered index scan for 791,358,704%, a merge join for 75,566,494%, etc.  This occurs in multiple query plans, and these are plans taken from sys.dm_exec_query_plan.

Are these query plans indicative of some other larger issue?

I know with a certainty the problem is not out of date stats - we ran fullscans on all the major tables, and the biggest table and DB I manually rebuilt with pre-sizing to eliminate fragmentation.

JNK,

You meant something like this?

http://sankarreddy.com/wp-content/uploads/2011/03/CropperCapture2.jpg

This is an estimated execution plan that's generated based on the statistics by the client tools and note that its NOT an engine issue. If you want MSFT to fix this issue then please vote for this connect item item.

https://connect.microsoft.com/SQLServer/feedback/details/436184/huge-operator-cost-in-estimated-execution-plan

Since you mentioned SQL Server 2008, I would encourage you to look at the WAIT STATS information when the job is being run. Look at why the execution is NOT moving along and figure out the bottleneck.

http://blogs.technet.com/b/sqlos/archive/2008/07/18/debugging-slow-response-times-in-sql-server-2008.aspx

Also, look at the sys.virtualfilestats information and see if there are significant IO lags and as well as memory usage.

HTH


Is "CREATE INDEX` in MySQL a Linear Operation? What I mean is the following:

If creating an index on a table with n rows takes t time. Will creating an index on the same table with 1000*ntake approximately 1000*t time.

What I'm trying to achieve is to to estimate the time it takes to create the index on the production database by creating the same index on the much smaller test database.

Index creation is essentially a sort operation, so is at best has a growth complexity of the order n log n on average (you might find it does better in some cases, and is not likely to do much worse).

If all your relevant data pages fit into RAM and are already in RAM, and the index will fit also, and your DBMS does not force index pages to be written before the creation is complete (so index blocks are not updated on disk multiple times during the operation), then the speed of writing the resulting index to disk will be more significant than the time taken to perform the sort - so you might find you get closer to a linear relationship between number of rows and the time the index creation takes - but if you assume the worse case you are less likely to be unpleasantly surprised!

Remember that unless you are not going to stop access to the production database during the operation any index create will be competing for IO bandwidth and/or locks with other activity, so you should try to account for this if you are doing your timing estimation tests on another system even if it is identically configured.


How to get sum of 3 sub count queries and group them using an attribute I have 3 queries as follows:

For the date 2019-12-02, I am calculating 3 type of values. The total value for 2019-12-02 must be the sum of these 3 queries.

Q1

SELECT count(*)
FROM XAXBXC_charge_info_ABC_tbl
WHERE date = '2019-12-02'
AND cg_response_code = 0

Q2=>

SELECT count(*)
FROM XAXBXC_charge_info_ABC_tbl
WHERE date = '2019-12-02'
  AND topup_id = 10
  AND topup_cg_response_code LIKE '0'

Q3=->

SELECT count(*)
FROM XAXBXC_charge_info_ABC_tbl
WHERE date = '2019-12-02'
  AND topup_id = 11
  AND topup_cg_response_code LIKE '0'

I am trying to calculate and display sum as follows,

Date      | Sum
2019-12-02|  234

eg:-

For a particular date, sum should be equal to the sum of Q1+Q2+Q3 and it must be grouped by the date column.

Can someone help me to achieve this? I have been stuck here for while now.

SELECT '2019-12-02' AS `Date`,
       SUM(cg_response_code = 0)
     + SUM(     topup_id IN (10 , 11)
            AND topup_cg_response_code LIKE '0'
          ) AS `Sum`
FROM XAXBXC_charge_info_ABC_tbl
WHERE `date` = '2019-12-02'

PS. LIKE '0' condition is too strange. I'd recommend to replace it with = 0 or = '0', depending on the topup_cg_response_code datatype.

  This will work for a particular day, but I want records for each day – sadeee nadeee 2 mins ago

SELECT `date`,
       SUM(cg_response_code = 0)
     + SUM(     topup_id IN (10 , 11)
            AND topup_cg_response_code LIKE '0'
          ) AS `Sum`
FROM XAXBXC_charge_info_ABC_tbl
/* WHERE `date` BETWEEN @date1 AND @date2 */
GROUP BY `date`


Extract data from Oracle DBF files I'm completely new to Oracle and I was provided with what appears to be an Oracle database backup (170 DBF files and 3 CTL files).

I'd like to extract the information using SSIS or Pentaho's Kettle but I'm not sure if that's possible

Can you please let me know if that's possible, and if so, what data provider should I use?

I don't have Oracle installed, so I guess it'll be hard and expensive to restore the DBFs

Thanks in advance

If you happen to be using the same platform (cpu/OS) it is fairly simple to get this up and running, if you know from which version of Oracle this backup is AND the backup is taken in the correct way. In your case, since all you have is ctl and dbf files, it should have been a COLD backup.

If all of the above is not in place: forget it.

If all of the above is in place:

create a init.ora file containing the dbname, db_unique_name, and control_files parameter. The latter one points to at least one of your ctl files. With a little luck you can guess the dbname from the file names.
install the correct oracle rdbms version in a location called ORACLE_HOME
export ORACLE_HOME=/where/your/Oracle/product/version/home_1 is
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus / as sysdba
startup nomount pfile=your_full_name_of_init.ora from step 1
alter database mount - this accessed the ctl files and checks dbname. Fix dbname in init.ora if reported wrong and shutdown abort the instance to start over at step 5.
now you are able to look into the original files locations. If your files are in exactly the same locations as the original database thi sis easy: alter database open read only;

If any error: hire a dba to help you. If step-8 gives problems because of incorrect file locations: you can rename the files one by one, or create a text dump of the controlfile, edit that dump for the correct locations and use it to create a new controlfile.

Oracle is very flexible and many situations can be handled.
In your case, your database is 9.2.0.6 on Windows. You could try to request a download from oracle support and perform an upgrade. This is the easiest is your dbf locations are the same as on the original system (they are in the controlfile)


Entity-Relationship Diagram Software I saw this E-R diagram in an answer on Stack Overflow:

Does anyone know which software was used to create it?

I use LucidChart but that output is really nice!

Perhaps unsurprisingly, you're not the first person to be intrigued by the fancy, fancy pictures in that post. If you look at the comments section below, you can see user @dangerousdave asks...

  nice diagram, what tool did you use?

...to which the poster @joel-brown replies...

  ... I use Visio with custom smart shapes that I built to use the James Martin ERD visual convention and a custom line pattern that gives it a hand-drawn look.

You might try reaching out to Joel at his public contact info and asking if he's willing to share the extensions he used (although the original post is around ~6 years old at the time of this writing so don't be too disappointed if he's using something else by now).


How to access data inside a "before insert trigger" for every statement I'm searching my ass of so you're my last hope. The task is quite simple:

I want to execute a trigger for each statement attached to a parent table (parent of partition tables) in Postgres 10.1. The triggered function will create a partition in case the corresponding range does not yet exist. The following function does not yet work for multiple years, I know ;).

Now my problem is, that I have no clue how to access the new data. When creating FOR EACH ROW, I have a variable 'NEW' (not allowed for partitions). When using INSTEAD OF INSERT, I should have something like 'inserted' as variable (not allowed for tables). So how can I access the new rows when using BEFORE INSERT FOR EACH STATEMENT? I just want all unique years which would be inserted afterwards.

Table:

CREATE TABLE ts AS (
  id BIGSERIAL         NOT NULL,
  start_ts TIMESTAMPTZ NOT NULL
);

Partitions:

CREATE TABLE ts_2014
  PARTITION OF ts
  FOR VALUES FROM ('2014-01-01') TO ('2015-01-01');

Trigger Function:

CREATE OR REPLACE FUNCTION create_ts_partition()
  RETURNS TRIGGER AS $$
DECLARE
  row_year           INT;
  dst_partition_name VARCHAR;
  partition_found    BOOLEAN;
  lower_bound        VARCHAR;
  upper_bound        VARCHAR;
BEGIN
  row_year = (
    SELECT
      extract(YEAR FROM start_ts)
    FROM NEW
    LIMIT 1);

  dst_partition_name = tg_table_name || '_' || row_year;

  partition_found = (
    SELECT
      exists(pg_c1.relname)
    FROM pg_class AS pg_c1
    WHERE pg_c1.relname LIKE dst_partition_name
  );

  IF NOT partition_found
  THEN
    lower_bound = row_year || '-01-01';
    upper_bound = (row_year + 1) || '-01-01';
    EXECUTE 'CREATE TABLE ' || dst_partition_name || ' [...]';
  END IF;
END;
$$ LANGUAGE 'plpgsql' PARALLEL UNSAFE;

Trigger:

CREATE TRIGGER ts_partition_creation
  BEFORE INSERT
  ON ts
  FOR EACH STATEMENT
EXECUTE PROCEDURE create_ts_partition();

Thanks in advance.
Kenneth

-- edit
Thanks to Daniel Vérité, I've encountered some inconsistencies with the names inside the example. Thanks!

The doc for CREATE TRIGGER in PostgreSQL 10 says:

  Triggers that are specified to fire INSTEAD OF the trigger event must
  be marked FOR EACH ROW, and can only be defined on views

Assuming nvme_ts is a view, you're trying to define a FOR EACH STATEMENT trigger in a case that the doc explicitly disallows.

Besides, the code shown in create_ts_partition() is meant for a single row, not for multiple rows with potentially different years.
You probably simply need for a FOR EACH ROW trigger, if you're set on using a trigger at all to manage the partitions.


Error converting VARCHAR(MAX) to XML due to "UTF-8" attribute I need to dig into a logs table with a schema similar to this:
CREATE TABLE t (
  id int PRIMARY KEY,
  data varchar(max)
);

Column data stores a XML text received from a web service in this format:
This is a reduced version
<?xml version="1.0" encoding="UTF-8"?>
<PARAM>
  <TAB DIM="30" ID="ZC3D2_1" SIZE="5">
    <LIN NUM = "1">
      <FLD NAME = "ZDOC" TYPE = "Char">Ferran López</FLD>
    </LIN>
  </TAB>
</PARAM>

When I try to cast this text to XML I get next error:

XML parsing: line xx, character 48, illegal xml character

It can be solved by removing the <xml> tag, or at least, the encoding attribute.

NOTE: It works fine if there is no special characters like ó, even if I don't remove <xml> tag.

Question
Is there a way to convert it to XML without replacing or removing <xml> tag?
CAST(REPLACE(data, 'encoding="UTF-8"', '') as XML)

db<>fiddle here
UPDATE
The server collation is: Latin1_General_BIN
But even if I try to change the collation to my usual servers collation, it doesn't work.
SELECT
  id,
  CAST((data COLLATE Latin1_General_CI_AS) as XML)
FROM
  t;

Your XML stored in a varchar(max) column should look like this.
<?xml version="1.0" encoding="UTF-8"?>
<PARAM>
  <TAB DIM="30" ID="ZC3D2_1" SIZE="5">
    <LIN NUM = "1">
      <FLD NAME = "ZDOC" TYPE = "Char">Ferran LÃ³pez</FLD>
    </LIN>
  </TAB>
</PARAM>

The ó should be represented with a double byte value Ã³.
If you don't have a UTF-8 encoded string stored in your column, the right way to go about this is to remove the encoding from the XML before you convert the value to the XML datatype.


Designing a database for a scenario with multiple user types I'm working on a project with multiple user types such as Contributor, Student, Sales, CS, Operator, etc. I usually separate them into different tables, and for each entity which is Authenticable I create a table called Credentials with a one-to-one relationship.

My questions are:

What is exactly the best practice when dealing with multiple user that are "authenticable" like that?
What are the deficiencies of my design?
Is it necessary to "move" Username and Password into each type ofUser entity?

Thank you for your help. Please give me a suggestion if my question is hard to understand because I'm not an English expert.

I don't believe there is one best design that fits all scenarios. It all comes down to the business rules that you are trying to enforce. If all types share the same attributes, it's probably best to use a single table with a classifier attribute. At the other end of the spectra there is the situation where the types share no attributes (unlikely in your case).

Typically though there are a set of common attributes that can be represented in a common table, say:

CREATE TABLE ORGANISATIONS -- I just invented a name here
( ORGANISATION_ID ... NOT NULL PRIMARY KEY
, ORGANISATION_TYPE ... NOT NULL
, <other attributes>
, CONSTRAINT ... CHECK ORGANISATION_TYPE IN (...)
);

Then a set of tables that represent attributes that are not common to all types:

CREATE TABLE CONTRIBUTORS
( ORGANISATION_ID ... NOT NULL PRIMARY KEY
, <other attributes>
, CONSTRAINT ... FOREIGN KEY (ORGANISATION_ID)
                     REFERENCES ORGANISATIONS (ORGANISATION_ID)
);

SQL99 (might even be SQL92, not sure) allows selects in CHECK constraints so we can add a CHECK constraint with a select that guarantees that the information corresponds with the organisation_type in organisations. However, not many vendors implements this so a common approach is to "inherit" the type attribute. First add a super key in ORGANISATIONS:

ALTER TABLE ORGANISATIONS ADD CONSTRAINT AK1_ORGANISATIONS
    UNIQUE (ORGANISATION_ID, ORGANISATION_TYPE);

Then we add the type attribute to the "sub-tables":

CREATE TABLE CONTRIBUTORS
( ORGANISATION_ID ... NOT NULL PRIMARY KEY
, ORGANISATION_TYPE ... NOT NULL
, <other attributes>
, CONSTRAINT ... FOREIGN KEY (ORGANISATION_ID,ORGANISATION_TYPE)
                     REFERENCES ORGANISATIONS (ORGANISATION_ID,ORGANISATION_TYPE)
, CONSTRAINT ... CHECK ORGANISATION_UNIT_TYPE IN (...) -- subset of types in ORGANISATIONS
)

The check constraint and the foreign key guarantees that the correct type is used.

Note that a table such as CONTRIBUTORS may be used for two or more types if they have a set of attributes in common. For adding additional attributes to such types the same technique can be used.

Yet another approach is to use nullable attributes for not applicable. In my experience it soon gets messy, and I would only use it if there are few such  attributes.


What's the difference between INSERT ... SELECT and SELECT INTO Let's say I have a table item and an other one versionned_item. From time to time, I want to copy data from item to versionned_item. What I did is

   WITH
     subset AS (SELECT ...)
     INSERT INTO versionned_item SELECT ... FROM subset

But in the documentation I came across the SELECT INTO statement that seems to do the exact same thing so I am wondering if there are differences / good practices about those 2 kind of inserts.

SELECT INTO is usually used to select specific set of data into a table, esp., during scenarios when the data in the table is the priority and not the constraints. It automatically creates a table if there is no such table already. But, INSERT INTO is used when you already have a table that has specific defined constraints and need to add data from a different table.


How to represent class table inheritance (current DBMS-specific way please)? I want to implement class either-or table inheritance (Account, CatAccountDetails, DogAccountDetails) in SQL Server.

I found a recommendation for MySQL:

How do I map an IS-A relationship into a database?

Basically, it is:

use identical primary keys between the parent table (Account) and the subtype tables (CatAccountDetails, DogAccountDetails)
add a subtype discriminator column to the parent table and the subtype tables
include both the shared primary keys and subtype columns in a foreign key constraint from subtype to parent type tables

Adding a type column that is constrained to a single value (e.g. a 'C' column in CatAccountDetails) feels just a little hackish, so I wondered if there is a feature in SQL Server to enable this scenario?

Also, if this is the way to do it, should I define something additionally to prevent this 'unused' column from degrading performance when my ORM (Entity Framework) requests a table join, like create an additional foreign key on just the PK?

The main problem is that CatAccountDetails will have different columns from DogAccountDetails. Cats have both different and far more properties than dogs in my application. Side note, 'cats' and 'dogs' represent three categories of website members in my current scenario.

Ultimately, I implemented ypercube's suggestion from comments:

  The "type" column can be defined as a computed (but constant) column in SQL-Server. I think it has to be PERSISTED though so it can participate in the Foreign Key constraint.

This worked well both for performance and compatibility with with my tools (Entity Framework <= 6.0):

CREATE TABLE [dbo].[Account](
    [Id] [int] IDENTITY(1000,1) NOT NULL PRIMARY KEY CLUSTERED,
    [CommunityRole] [int] NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
CONSTRAINT [UX_Derived_Relation] UNIQUE ([Id], [CommunityRole]))

CREATE TABLE [dbo].[Recruiter](
    [Id] [int] NOT NULL PRIMARY KEY CLUSTERED,
    [CommunityRole]  AS ((1)) PERSISTED NOT NULL,
    [RecruiterSpecificValue]  [int] NOT NULL,
FOREIGN KEY ([Id], [CommunityRole]) REFERENCES Account([Id], [CommunityRole]))

CREATE TABLE [dbo].[Candidate](
    [Id] [int] NOT NULL PRIMARY KEY CLUSTERED,
    [CommunityRole]  AS ((2)) PERSISTED NOT NULL,
    [CandidateSpecificValue]  [int] NOT NULL,
FOREIGN KEY ([Id], [CommunityRole]) REFERENCES Account([Id], [CommunityRole]))

This mapped well to my implementation of multiple discrete account types, Recruiter and Candidate, on my Job Board.


SQL Server Availability Group LeaseTimeout and slow IO Our 5 main databases runs on a physical (2 * 8 cores, 512GB, Hypertreading) SQL Server 2016 SP2 Enterprise in a single Availability Gruop and sometimes we get errors that the lease-timeout has expired. My understanding is that if the lease can't get updated there is a system-wide problem.
When I check the output of sp_server_diagnostics (*SQLDIAG*.xel files), in the log folder of the primary replica, around the time of the timeout I always find pending IO operations.

<ioSubsystem ioLatchTimeouts="0" intervalLongIos="0" totalLongIos="1">
<longestPendingRequests>
<pendingRequest duration="26566" filePath="\?\F:\SqlLogs\db1.ldf" offset="80824832" handle="0x8d10" />
<pendingRequest duration="1987" filePath="\?\O:\SqlLogs\db2.ldf" offset="3880740352" handle="0x1330" />
<pendingRequest duration="1093" filePath="\?\O:\SqlLogs\db3.ldf" offset="288143360" handle="0x132c" />
<pendingRequest duration="974" filePath="\?\O:\SqlLogs\db3.ldf" offset="288145408" handle="0x132c" />
<pendingRequest duration="937" filePath="\?\O:\SqlLogs\db3.ldf" offset="288146944" handle="0x132c" />
</longestPendingRequests>
</ioSubsystem>

This is what I find in the clusterlog of the primary replica:

WARN  [RES] SQL Server Availability Group: [hadrag] Failed to retrieve data column. Return code -1
ERR   [RES] SQL Server Availability Group: [hadrag] Failure detected, diagnostics heartbeat is lost
ERR   [RES] SQL Server Availability Group <AG_Name>: [hadrag] Availability Group is not healthy with given HealthCheckTimeout and FailureConditionLevel
ERR   [RES] SQL Server Availability Group <AG_Name>: [hadrag] Resource Alive result 0.
ERR   [RES] SQL Server Availability Group: [hadrag] Failure detected, diagnostics heartbeat is lost
ERR   [RES] SQL Server Availability Group <AG_Name>: [hadrag] Availability Group is not healthy with given HealthCheckTimeout and FailureConditionLevel
ERR   [RES] SQL Server Availability Group <AG_Name>: [hadrag] Resource Alive result 0.
WARN  [RHS] Resource AG_Name IsAlive has indicated failure.

This are the erros in the SQL Server errorlog:

Error: 19407, Severity: 16, State: 1
SQL Server hosting availability
group 'AG_Name' did not receive a process event signal from the
Windows Server Failover Cluster within the lease timeout period.
Error: 19407, Severity: 16, State: 1
The lease between availability
group 'AG_Name' and the Windows Server Failover Cluster has
expired. A connectivity issue occurred between the instance of SQL
Server and the Windows Server Failover Cluster. To determine whether
the availability group is failing over correctly, check the
corresponding availability group resource in the Windows Server
Failover Cluster.
Always On: The local replica of availability group 'AG_Name' is
going offline because either the lease expired or lease renewal
failed. This is an informational message only. No user action is
required.

This is the output from SELECT @@version:

Microsoft SQL Server 2016 (SP2-CU15) (KB4577775) - 13.0.5850.14 (X64)
Sep 17 2020 22:12:45   Copyright (c) Microsoft Corporation  Enterprise
Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2
Standard 6.3  (Build 9600: )

In our monitoring there are no signs of high cpu usage. Also no memory dumps are created at the time of the problem.
As a result of this timeout the WSFC-service restarts the cluster resource 'AG_Name'. After that this resource is restarted everything works perfect again.
What I don't understand is: how can slow IO-requests cause a lease-timeout? Can to many pending IO-requests cause a lease-timeout?

how can slow IO-requests cause a lease-timeout? Can to many pending IO-requests cause a lease-timeout?

No, slow I/O requests can't cause a lease timeout directly.
However, if the server is completely overloaded (CPU at 100%), than can cause pending I/O requests and lease timeouts.  The default lease timeout is 20 seconds, and your pending I/O is 26 seconds.  High CPU or some other server / OS level problem is more likely the issue here.
Another cause is that SQL Server encountered a serious error, and is generating dump files (which causes the process to pause, potentially long enough for WSFC to think the lease timed out).
See the documentation for a couple more possibilities:

OS not responding, low virtual memory, working set paging, generating dump, pegged CPU, WSFC down (loss of quorum)

You should review the SQL Server error log to see if there are dumps being created.  If you have monitoring from the time of these incidents, you could also check for maxed out CPU.


How do you generate large OLTP and reporting workloads? My current skillset lies towards plain vanilla data warehousing, with < 20k users, our setups are rather simple..

I am looking for ways to enhance my technical skills, however, as much theory as I read, I'm struggling to figure out how I can actually generate workloads of 100k+ transactions/second  , or 10k users hitting a report from different parts of the world.

How do people normally gain expertise in large scale database systems outside of what they do at work?  (I have a MSDN subscription, so $150/month on Azure to play with)

I think the TPC-H benchmark (or TPC-DS) might be a good start. The council publishes the tool to generate data and the workload.