Why Does the Transaction Log Keep Growing or Run Out of Space?

A Shorter Answer:

You probably either have a long running transaction running (Index maintenance? Big batch delete or update?) or you are in the "default" (more below on what is meant by default) recovery mode of Full and have not taken a log backup (or aren't taking them frequently enough).

If it is a recovery model issue, the simple answer could be Switch to Simple recovery mode if you do not need point in time recovery and regular log backups. Many people, though, make that their answer without understanding recovery models. Read on to understand why it matters and then decide what you do. You could also just start taking log backups and stay in Full recovery.

There could be other reasons but these are the most common. This answer begins to dive into the most common two reasons and gives you some background information on the why and how behind the reasons as well as explores some other reasons.

A Longer Answer:
What Scenarios can cause the log to keep Growing? There are many reasons, but usually these reasons are of the following two patterns: There is a misunderstanding about recovery models or there are long running transactions. Read on for details.

Top reason 1/2: Not Understanding Recovery Models

(Being in Full Recovery Mode and Not Taking Log Backups - This is the most common reason - the vast majority of those experiencing this issue are.)

While this answer is not a deep dive in SQL Server recovery models, the topic of recovery models is critical to this problem.

In SQL Server, there are three recovery models:

Full,
Bulk-Logged and
Simple.

We'll ignore Bulk-Logged for now we'll sort of say it is a hybrid model and most people who are in this model are there for a reason and understand recovery models.

The two we care about and their confusion are the cause of the majority of the cases of people having this issue are Simple and Full.

Intermission: Recovery in General

Before we talk about Recovery Models: let's talk about recovery in general. If you want to go even deeper with this topic, just read Paul Randal's blog and as many posts on it as you want. For this question, though:

Crash/Restart Recovery
One purpose of the transaction log file is for crash/restart recovery. For the rolling forward and rolling back of work that was either done (rolling forward/redo) before a crash or restart and the work that was started but not finished after a crash or restart (rolling back/undo). It is the job of the transaction log to see that a transaction started but never finished (rolled back or crash/restart happened before the transaction committed). In that situation It is the log's job to say "Hey.. this never really finished, let's roll it back" during recovery. It is also the log's job to see that you did finish something and that your client application was told it was finished (even if it hadn't yet hardened to your data file) and say "Hey.. this really happened, let's roll it forward, let's make it like the applications think it was" after a restart. Now there is more but that is the main purpose.
Point in Time Recovery
The other purpose for a transaction log file is to be able to give us the ability to recover to a point in time due to an "oops" in a database or to guarantee a recovery point in the event of a hardware failure involving the data and/or log files of a database. If this transaction log contains the records of transactions that have been started and finished for recovery, SQL Server can and does then use this information to get a database to where it was before an issue happened. But that isn't always an available option for us. For that to work we have to have our database in the right recovery model, and we have to take log backups.

Recovery Models

Onto the recovery models:

Simple Recovery Model
So with the above introduction, it is easiest to talk about Simple Recovery model first. In this model, you are telling SQL Server: "I am fine with you using your transaction log file for crash and restart recovery..." (You really have no choice there. Look up ACID properties and that should make sense quickly.) "...but once you no longer need it for that crash/restart recovery purpose, go ahead and reuse the log file."

SQL Server listens to this request in Simple Recovery and it only keeps the information it needs to do crash/restart recovery. Once SQL Server is sure it can recover because data is hardened to the data file (more or less), the data that has been hardened is no longer necessary in the log and is marked for truncation - which means it gets re-used.
Full Recovery Model
With Full Recovery, you are telling SQL Server that you want to be able to recover to a specific point in time, as long as your log file is available or to a specific point in time that is covered by a log backup. In this case when SQL Server reaches the point where it would be safe to truncate the log file in Simple Recovery Model, it will not do that. Instead It lets the log file continue to grow and will allow it to keep growing, until you take a log backup (or run out of space on your log file drive) under normal circumstances.

Switching from Simple to Full has a Gotcha.

There are rules and exceptions here. We'll talk about long running transactions in depth below.

But one caveat to keep in mind for Full Recovery Mode is this: If you just switch into Full Recovery mode, but never take an initial Full Backup, SQL Server will not honor your request to be in Full Recovery model. Your transaction log will continue to operate as it has in Simpleuntil you switch to Full Recovery Model AND take your first Full Backup.

Full Recovery Model without log backups is bad.

So, what is the most common reason for uncontrolled log growth?
Answer: Being in Full Recovery mode without having any log backups.

This happens all the time to people.

Why is this such a common mistake?

Why does it happen all the time? Because each new database gets its initial recovery model setting by looking at the model database.

Model's initial recovery model setting is always Full Recovery Model - until and unless someone changes that. So you could say the "default Recovery Model" is Full. Many people are not aware of this and have their databases running in Full Recovery Model with no log backups, and therefore a transaction log file much larger than necessary. This is why it is important to change defaults when they don't work for your organization and its needs)

Full Recovery Model with too few log backups is bad.

You can also get yourself in trouble here by not taking log backups frequently enough.
Taking a log backup a day may sound fine, it makes a restore require less restore commands, but keeping in mind the discussion above, that log file will continue to grow and grow until you take log backups.

How do I find out what log backup frequency I need?

You need to consider your log backup frequency with two things in mind:

Recovery Needs - This should hopefully be first. In the event that the drive housing your transaction log goes bad or you get serious corruption that affects your log backup, how much data can be lost? If that number is no more than 10-15 minutes, then you need to be taking the log backup every 10-15 minute, end of discussion.
Log Growth - If your organization is fine to lose more data because of the ability to easily recreate that day you may be fine to have a log backup much less frequently than 15 minutes. Maybe your organization is fine with every 4 hours. But you have to look at how many transactions you generate in 4 hours. Will allowing the log to keep growing in those four hours make too large of a log file? Will that mean your log backups take too long?

Top reason 2/2: Long Running Transactions

("My recovery model is fine! The log is still growing!)

This can also be a cause of uncontrolled and unrestrained log growth. No matter the recovery model, but it often comes up as "But I'm in Simple Recovery Model - why is my log still growing?!"

The reason here is simple: if SQL is using this transaction log for recovery purposes as I described above, then it has to see back to the start of a transaction.

If you have a transaction that takes a long time or does a lot of changes, the log cannot truncate on checkpoint for any of the changes that are still in open transactions or that have started since that transaction started.

This means that a big delete, deleting millions of rows in one delete statement is one transaction and the log cannot do any truncating until that whole delete is done. In Full Recovery Model, this delete is logged and that could be a lot of log records. Same thing with Index optimization work during maintenance windows. It also means that poor transaction management and not watching for and closing open transactions can really hurt you and your log file.

What can I do about these long running transactions?

You can save yourself here by:

Properly sizing your log file to account for the worst case scenario - like your maintenance or known large operations. And when you grow your log file you should look to this guidance (and the two links she sends you to) by Kimberly Tripp. Right sizing is super critical here.
Watching your usage of transactions. Don't start a transaction in your application server and start having long conversations with SQL Server and risk leaving one open too long.
Watching the implied transactions in your DML statements. For example: UPDATE TableName Set Col1 = 'New Value' is a transaction. I didn't put a BEGIN TRAN there and I don't have to, it is still one transaction that just automatically commits when done. So if doing operations on large numbers of rows, consider batching those operations up into more manageable chunks and giving the log time to recover. Or consider the right size to deal with that. Or perhaps look into changing recovery models during a bulk load window.

Do these two reasons also apply to Log Shipping?

Short answer: yes. Longer answer below.

Question: "I'm using log shipping, so my log backups are automated... Why am I still seeing transaction log growth?"

Answer: read on.

What is Log Shipping?

Log shipping is just what it sounds like - you are shipping your transaction log backups to another server for DR purposes. There is some initialization but after that the process is fairly simple:

A job to backup the log on one server,
a job to copy that log backup and
a job to restore it without recovery (either NORECOVERY or STANDBY) on the destination server.

There are also some jobs to monitor and alert if things don't go as you have them planned.

In some cases, you may only want to do the log shipping restore once a day or every third day or once a week. That is fine. But if you make this change on all of the jobs (including the log backup and copy jobs) that means you are waiting all that time to take a log backup. That means you will have a lot of log growth -- because you are in full recovery mode without log backups -- and it probably also means a large log file to copy across. You should only modify the restore job's schedule and let the log backups and copies happen on a more frequent basis, otherwise you will suffer from the first issue described in this answer.

General troubleshooting via status codes

There are reasons other than these two, but these are the most common. Regardless of the cause: there is a way you can analyze your reason for this unexplained log growth/lack of truncation and see what they are.

By querying the sys.databases catalog view you can see information describing the reason your log file may be waiting on truncate/reuse.

There is a column called log_reuse_wait with a lookup ID of the reason code and a log_reuse_wait_desc column with a description of the wait reason. From the referenced books online article are the majority of the reasons (the ones you are likely to see and the ones we can explain reasons for. The missing ones are either out of use or for internal use) with a few notes about the wait in italics:

0 = Nothing
What it sounds like.. Shouldn't be waiting
1 = Checkpoint
Waiting for a checkpoint to occur. This should happen and you should be fine - but there are some cases to look for here for later answers or edits.
2 = Log backup
You are waiting for a log backup to occur. Either you have them scheduled and it will happen soon, or you have the first problem described here and you now know how to fix it
3 = Active backup or restore
A backup or restore operation is running on the database
4 = Active transaction
There is an active transaction that needs to complete (either way - ROLLBACK or COMMIT) before the log can be backed up. This is the second reason described in this answer.
5 = Database mirroring
Either a mirror is getting behind or under some latency in a high performance mirroring situation or mirroring is paused for some reason
6 = Replication
There can be issues with replication that would cause this - like a log reader agent not running, a database thinking it is marked for replication that no longer is and various other reasons. You can also see this reason and it is perfectly normal because you are looking at just the right time, just as transactions are being consumed by the log reader
7 = Database snapshot creation
You are creating a database snapshot, you'll see this if you look at just the right moment as a snapshot is being created
8 = Log Scan
I have yet to encounter an issue with this running along forever. If you look long enough and frequently enough you can see this happen, but it shouldn't be a cause of excessive transaction log growth, that I've seen.
9 = An AlwaysOn Availability Groups secondary replica is applying transaction log records of this database to a corresponding secondary database.
About the clearest description yet..


PostgreSQL update table then insert on another table

You can use a data modifying CTE to do this in a single statement:
with changed_user as (
  UPDATE users
    SET free_shipping_until = spu.created_at::date + interval '1 year'
  FROM shipping_priority_users AS spu
  WHERE spu.role_id = #{role.id}
    and users.id = spu.user_id
    and spu.created_at is not null
  returning *
)
insert into log (user_id, created_at, data)
select id,
       current_timestamp,
       jsonb_build_object('free_shipping_until', array[null, free_shipping_until::text])
from changed_user;

from changed_user


Failed Upgrade from SQL Server 2005 sp2 to SQL Server 2005 sp4

Almost every time I've had an SP installation issue on a stand-alone server, it was because the resource database was moved or it was not in the same directory as the master database.

Many articles I'm finding correlate your error to this exact conclusion including this one.

I would start there and if you find it to be the case, move the system databases back to where the resource database is.

If that doesn't work, try re-applying SP2.  If all of the components weren't patched during the SP2 upgrade, you could experience this issue.

Lastly, verify service account permissions and permissions of the account you are running the SP4 with.


SQL Server Transactional replication failed due to snapshot agent failure

It sounds like you may have set Force Encryption on the instance.  In SQL Server Configuration Manager, you can see this setting (server-side encrypting enforcement) by expanding SQL Server Network Configuration and then right-clicking on Protocols for YOURINSTANCENAME, and selecting Properties.  You will see the option there to toggle Force Encryption, as well as a tab for Certificate config.

I am by no means saying to disable forced encryption (if that is indeed your problem, I'm guessing somebody set that for a reason), but you will need to ensure that the communication between those two is properly configure.  Here is a good KB article that seems to explain what you're running into.


It sounds like you don't have direct access to the dba views and have it granted through a role. You can check out this answer on Stack Overflow for a little more information.

https://stackoverflow.com/questions/28936449/grant-create-view-using-system-view-with-dba-privilage-in-oracle


MySQL Replication, Can i add a trigger to the SLAVE db?

"Is it possible to do so? The trigger will insert some rows into a unique table that exists only on the slave."

yes, you can

A lot of warehousing architectures are based on the slave only.

The title of your question is wrong. Change replica DB with SLAVE ONLY


best approach to model relation between customers - dimension dim_customer (SCD2) which can be a HEAD/SUB customer

All you need is your one-to-many bridge table, and it really only needs the head_dep_key field and the dep_key field. That can be joined back to your dim_department table when needed and should be able to answer all questions you'd ask of this data.


The index use seems irrelevant to the concatenation.
The other 2 solutions are awkward.

Demo

create table t (x int,code varchar(100))

insert into t values
    (1,'X'),(1,'X'),(1,'Y'),(1,'Y'),(1,'Y'),(1,'Z'),(2,'X'),(2,'X'),(2,'Y')

select  *
from    t pivot (count(code) for code in([x],[y],[z])) p

  Msg 265, Level 16, State 1, Line 13 The column name "x" specified in
  the PIVOT operator conflicts with the existing column name in the
  PIVOT argument.
  Msg 8156, Level 16, State 1, Line 13 The column 'x'
  was specified multiple times for 'p'.

select  *
from    (select x,'my_prefix_' + code as code from t) t
            pivot (count(code) for code in([my_prefix_x],[my_prefix_y],[my_prefix_z])) p

+---+-------------+-------------+-------------+
| x | my_prefix_x | my_prefix_y | my_prefix_z |
+---+-------------+-------------+-------------+
| 1 | 2           | 3           | 1           |
+---+-------------+-------------+-------------+
| 2 | 2           | 1           | 0           |
+---+-------------+-------------+-------------+


Postgres RDS: is there a way to use LDAP authentication?

As much I am aware this may not be possible.
Currently, AWS has IAM authentication for postgres which in my humble opinion has a bit further to go before it can be adopted. ie once the IAM user is mapped to the DB Role any once can create a token on behalf on a DB role.


postgresql 9.1 Multiple Cluster on same host

First, please post error messages.  Otherwise this is shooting in the dark.

You could have a bunch of problems.  I would suggest the following:

If you simply get a connection refused, try netstat -p | grep 5433 so you can see what programs are listening on the port.  Is it something unexpected?
If you are getting pg_hba.conf errors, double check your settings there.
Please check again your listen settings in your postgresql.conf file.
Check your postgresql log on the cluster you are having trouble with.  Please post relevant sections if this doesn't get you to your answer.


How to change type from one owner to two different types of owners

I have a feeling you will benefit if you borrow party concept from Party-Role-Relationship, one of universal data models, which is capable of keeping all information you needed.

So you will have party_type table that will currently have 2 records, person and organization, one common party table that holds all people and companies (you may want to add 2 more tables , person and company that hold attributes specific to people and organizations respectively), and your rating will have a reference to party.


Running TRUNCATE PARTITION works fine

DROP DATABASE IF EXISTS partest;
CREATE DATABASE partest;
USE partest
CREATE TABLE rolando (
  id int not null auto_increment,
  LogDate DATE,
  PRIMARY KEY (id,LogDate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (DAYOFYEAR(`LogDate`))
(PARTITION p1 VALUES LESS THAN (8) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (22) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (29) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (36) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (43) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (50) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (57) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (64) ENGINE = InnoDB,
 PARTITION p51 VALUES LESS THAN (358) ENGINE = InnoDB,
 PARTITION p52 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
ALTER TABLE rolando TRUNCATE PARTITION p4;

Here is the output

mysql> DROP DATABASE IF EXISTS partest;
Query OK, 1 row affected (1.55 sec)

mysql> CREATE DATABASE partest;
Query OK, 1 row affected (0.00 sec)

mysql> USE partest
Database changed
mysql> CREATE TABLE rolando (
    ->   id int not null auto_increment,
    ->   LogDate DATE,
    ->   PRIMARY KEY (id,LogDate)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    -> PARTITION BY RANGE (DAYOFYEAR(`LogDate`))
    -> (PARTITION p1 VALUES LESS THAN (8) ENGINE = InnoDB,
    ->  PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB,
    ->  PARTITION p3 VALUES LESS THAN (22) ENGINE = InnoDB,
    ->  PARTITION p4 VALUES LESS THAN (29) ENGINE = InnoDB,
    ->  PARTITION p5 VALUES LESS THAN (36) ENGINE = InnoDB,
    ->  PARTITION p6 VALUES LESS THAN (43) ENGINE = InnoDB,
    ->  PARTITION p7 VALUES LESS THAN (50) ENGINE = InnoDB,
    ->  PARTITION p8 VALUES LESS THAN (57) ENGINE = InnoDB,
    ->  PARTITION p9 VALUES LESS THAN (64) ENGINE = InnoDB,
    ->  PARTITION p51 VALUES LESS THAN (358) ENGINE = InnoDB,
    ->  PARTITION p52 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
Query OK, 0 rows affected (2.60 sec)

mysql> ALTER TABLE rolando TRUNCATE PARTITION p4;
Query OK, 0 rows affected (0.25 sec)

mysql>

OK, fine. It works. However, doing this is logically unsafe. Why ? The partitioned table is based on DAYOFYEAR(datetime_col)). Since each partition contains a week for multiple years, running alter table t1 truncate partition p4; would wipe out week 4 from multiple years.

OK, let's try dropping and adding the partition

mysql> ALTER TABLE rolando DROP PARTITION p4;
Query OK, 0 rows affected (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE rolando ADD PARTITION
    -> (PARTITION p4 VALUES LESS THAN (29) ENGINE = InnoDB);
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
mysql>

This makes sense since MAXVALUE already exists in the definition. This prevents you from adding (appending) a partition if MAXVALUE is present.

While you could play games with ALTER TABLE ... REORGANIZE PARTITION, the complexity isn't worth it and you won't achieve the result of wedging in a partition.

Rather that explanining further partition machinations, just do the following

Make sure an index exists on the date field
Perform the DELETE SQL already mentioned

An alternative would be to create a table with a weekly partition then drop the exact week.


I would change your table layout. If you used something like:

(LineId int, BallSequence tinyint, BallVal int)

The combination of (LineId, Ballsequence) would be your PK.

Then your query would be:

SELECT COUNT(*)
FROM
( SELECT 1
  FROM dbo.Lines
  GROUP BY LineID
  HAVING SUM( CASE WHEN
              BallVal IN (@ball1, @ball2, @ball3, @ball4, @ball5, @ball6)
              THEN 1 ELSE 0 END) >= @matchCount
) AS t ;

...and it could be covered by an index in BallVal.


How to emulate performant date dimension for SSAS model in tabular mode / direct query

Thanks to @GregGalloway to set me to right track.
I ended up with following schema:

1) I've added new mapping table [DateMap] that materialize mapping between UTC datetime (in those weird quarter-hours) to local time zone (again in  quarter-hours)

CREATE TABLE [dbo].[DateMap] (
  -- time zone code (set as context info for given user)
  [TimeZoneCode] smallint NOT NULL,
  -- quarter hours means datetime trimmed to xx:00, xx:15, xx:30, xx:45
  [UTC_QuarterHours] smalldatetime NOT NULL,
  -- local time zone
  [LTZ_QuarterHours] smalldatetime NOT NULL,
  CONSTRAINT [PK_tmp_DateMap] PRIMARY KEY ([TimeZoneCode] ASC, [UTC_QuarterHours] ASC)
)

2) [DimDate] has primary key set to LTZ_QuarterHours

CREATE TABLE [dbo].[DimDates](
  [LTZ_QuarterHours] smalldatetime NOT NULL,
  [Date] date NOT NULL,
  [Year] int NOT NULL,
  [Month] int NOT NULL,
  [Quarter] int NOT NULL,
  CONSTRAINT PK_tmp_DimDates PRIMARY KEY ([LTZ_QuarterHours] ASC)
)

3) [Entity] table has new column [CreatedDateUTC_QuarterHours] - which means original utc date trimmed to quarter hours

CREATE TABLE [dbo].[Entity] (
  [EntityID] int NOT NULL PRIMARY KEY,
  [CreatedDateUTC] datetime,
  [CreatedDateUTC_QuarterHours] smalldatetime
)

4) Entity in model is defined as SQL query like

SELECT e.*, m.[LTZ_QuarterHours] FROM [Entity] e
JOIN [DateMap] m on e.CreatedDateUTC_QuarterHours = m.UTC_QuarterHours and
  m.TimeZoneCode = fn_GetCurrentUserTimeZoneCode()

Which is pretty fast.


Dynamically convert hstore keys into columns for an unknown set of keys

This can be done, very efficiently, too. Not in a single statement, though, since SQL demands to know the return type at call time. So you need two steps. The solution involves a number of advanced techniques ...

Assuming the same table as @Denver in his answer:

CREATE TABLE hstore_test (
  id serial PRIMARY KEY
, hstore_col hstore
);

Solution 1: Simple SELECT

After I wrote the crosstab solution below it struck me that a simple "brute force" solution is probably faster. Basically, the query @Denver already posted, built dynamically:

Step 1a: Generate query

SELECT format(
     'SELECT id, h->%s
      FROM  (SELECT id, hstore_col AS h FROM hstore_test) t;'
    , string_agg(quote_literal(key) || ' AS ' || quote_ident(key), ', h->')
   ) AS sql
FROM  (
   SELECT DISTINCT key
   FROM   hstore_test, skeys(hstore_col) key
   ORDER  BY 1
   ) sub;

The subquery (SELECT id, hstore_col AS h FROM hstore_test) is just to get in the column alias h for your hstore column.

Step 1b: Execute query

This generates a query of the form:

SELECT id, h->'key1' AS key1, h->'key2' AS key2, h->'key3' AS key3
FROM  (SELECT id, hstore_col AS h FROM hstore_test) t;

Result:

 id | key1  | key2  | key3
----+-------+-------+-------
  1 | val11 | val12 | val13
  2 | val21 | val22 |
  3 |       |       |        -- for a row where hstore_col IS NULL

Solution 2: crosstab()

For lots of keys this may perform better. Probably not. You'll have to test. Result is the same as for solution 1.

You need the additional extension tablefunc which provides the crosstab() function. Read this first if you are not familiar:

PostgreSQL Crosstab Query

Step 2a: Generate query

SELECT format(
   $s$SELECT * FROM crosstab(
     $$SELECT h.id, kv.*
       FROM   hstore_test h, each(hstore_col) kv
       ORDER  BY 1, 2$$
   , $$SELECT unnest(%L::text[])$$
   ) AS t(id int, %s text);
   $s$
 , array_agg(key)  -- escapes strings automatically
 , string_agg(quote_ident(key), ' text, ')  -- needs escaping!
   ) AS sql
FROM  (
   SELECT DISTINCT key
   FROM   hstore_test, skeys(hstore_col) key
   ORDER  BY 1
   ) sub;

Note the nested levels of dollar-quoting.

I use this explicit form in the main query instead of the short CROSS JOIN in the auxiliary query to preserve rows with empty or NULL hstore values:

LEFT   JOIN LATERAL each(hstore_col) kv ON TRUE

What is the difference between LATERAL and a subquery in PostgreSQL?

Related:

Dynamic alternative to pivot with CASE and GROUP BY

Step 2b: Execute query

This generates a query of the form:

SELECT * FROM crosstab(
     $$SELECT h.id, kv.*
       FROM   hstore_test h
       LEFT   JOIN LATERAL each(hstore_col) kv ON TRUE
       ORDER  BY 1, 2$$
   , $$SELECT unnest('{key1,key2,key3}'::text[])$$
   ) AS t(id int, key1 text, key2 text, key3 text);

You may want to inspect it for plausibility before running the first time. This should deliver optimized performance.

Notes

Both solutions work for any number of keys up to the physical limit of ~ 1600 columns in a Postgres table.
Both also work for keys of any shape or form up to the maximum length for identifiers, which is 63 bytes per default.
Besides the hstore function each() that was already mentioned by s.m., I also use the related function skeys() to identify keys.
Be sure to quote column names correctly to avoid possible SQL injection attacks by way of maliciously formed key names. I take care of that with quote_literal() and quote_ident().


The primary key in SQL Server is by default also the clustering key - and the data is physically ordered by the clustering key in a table.

So if this primary key is listed, maybe it's because it is a non-clustered primary key - something that you have to do explicitly - but it's entirely possible.

The primary key is a logical construct - it's job is to make sure each row in a table can be uniquely and reliably identified. It has no impact whatsoever on the physical structuring of the table.

The clustering key (and thus the clustered index) is what determines the physical ordering of the data in a SQL Server table. The table's primary key is the clustering key by default - but it doesn't have to be - you can have two totally separate sets of columns for the primary key and the clustering key.

But all these things aside: the DMV's that identify missing indexes aren't perfect - do take their recommendations with a grain of salt! Don't just blindly implement everything that DMV recommends - sometimes it recommends stuff that's already in place, or some of its recommendations are even contra-productive! Don't blindly trust it - it's a helping tool - but it's not perfect!


This will solve your problem, use SUM and join in 1 query.

SELECT SUM(`kilo`) total_kilo
FROM `person`
    INNER JOIN `products`
    on `person`.`product` = `products`.`id`;

Or selecting/counting for user 1 alone

SELECT SUM(`kilo`) total_kilo
FROM `person`
    INNER JOIN `products`
    on `person`.`product` = `products`.`id`
WHERE `person`.`user` =1;

Or selecting/counting for every user

SELECT `person`.`user`, SUM(`kilo`) total_kilo
FROM `person`
    INNER JOIN `products`
    on `person`.`product` = `products`.`id`
GROUP BY `person`.`user`;


MongoDB duplicate "_id " for an array "_id"

I realised order matters to MongoDB when using an object as the value for a unique field such as the _id field.

Not sure if it can be termed a bug.

That said, with reference to your query:

db.testCol.find({"_id.age":22,"_id.empId":1})

what you are actually doing is querying based on individual components of the _id field and not the _id field as a whole.

You will be better served if your query targets the _id as a whole. This query should achieve what you intended.

db.testCol.find({"_id": {"age" : 22, "empId" : 1}})


How to normalize a lookup table containing NULL? - or should I bother?

Perhaps a better policy:

When registering a user, store the fee charged into the user's history.
Derive the fee from some business logic that may or may not be stored in a database table.

Note how this policy freezes the fee as soon as it is billed.  This is safer than having it in some other table that might accidentally be changed for unrelated reasons.


Create a trigger on all the last_modified columns in PostgreSQL

spi module: moddatetime

moddatetime — Functions for Tracking Last Modification Time

  moddatetime() is a trigger that stores the current time into a timestamp field. This can be useful for tracking the last modification time of a particular row within a table.

  To use, create a BEFORE UPDATE trigger using this function. Specify a single trigger argument: the name of the column to be modified. The column must be of type timestamp or timestamp with time zone.

  There is an example in moddatetime.example.

Example / Synopsis

From the above referenced file,

DROP TABLE mdt;

CREATE TABLE mdt (
    id      int4,
    idesc       text,
    moddate timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TRIGGER mdt_moddatetime
    BEFORE UPDATE ON mdt
    FOR EACH ROW
    EXECUTE PROCEDURE moddatetime (moddate);

INSERT INTO mdt VALUES (1, 'first');
INSERT INTO mdt VALUES (2, 'second');
INSERT INTO mdt VALUES (3, 'third');

SELECT * FROM mdt;

UPDATE mdt SET id = 4
    WHERE id = 1;
UPDATE mdt SET id = 5
    WHERE id = 2;
UPDATE mdt SET id = 6
    WHERE id = 3;

SELECT * FROM mdt;

Your Application

So this is what you would need to.

CREATE EXTENSION moddatetime;

CREATE TRIGGER mdt_table
  BEFORE UPDATE ON table
  FOR EACH ROW
  EXECUTE PROCEDURE moddatetime (prefix_last_modified);


MySQL consumes memory for each portion of read

MySQL has several "caches".  Some of them grow as needed up to a pre-determined limit.  This probably explains (at a high level) what you are seeing.

A well-tuned MySQL-only server will consume nearly all of RAM, then sit there quite happy.

Do you have other applications running on the same server?  If so, you need to tune MySQL not to reach for filling up the server.

If you want to discuss tuning, please prove the amount of available RAM (after accounting for other processes), plus the my.cnf settings.


How to read the MySQL Audit.log file in a SQL table?

In this page you find information about Audit.log:

https://mysqlserverteam.com/audit-logs-json-format-logging/

Audit Log File Reading
Audit Log can store log files as the XML or JSON format, depending on the specified output format. When the JSON logging is enabled, we can read events using the provided audit_log_read() UDF.

  {
  "timestamp": "2018-05-24 23:15:07",
  "id": 0,
  "class": "connection",
  "event": "connect",
  "connection_id": 12,
  "account": { "user": "user",
               "host": "localhost" },
  "login": { "user": "user",
             "os": "",
             "ip": "::1",
             "proxy": "" },
  "connection_data": { "connection_type": "tcp/ip",
                       "status": 0,
                       "db": "bank_db" } }


Why Can't I find my databases from Mysql on linux?

Your problem has nothing to do with missing databases. You real problem stems directly from the way you logged in to MySQL. First, look at your comment
SHOW DATABASES; => information_schema, test.
SELECT USER(); => root@localhost
SELECT CURRENT_USER(); => @localhost
SHOW GRANTS; => GRANT USAGE ON *.* TO ''@'localhost'

Note the difference between USER() and CURRENT_USER()

USER() reports how you attempted to authenticate in MySQL ('root'@'localhost')
CURRENT_USER() reports how you were allowed to authenticate in MySQL (''@'localhost')

Bottom Line : You have no rights to see anything. From the the output of USER() and CURRENT_USER(), there is no root@localhost defined in the table mysql.user.
The further proof of this is the fact that you cannot see the mysql and performance_schema databases. A fresh install of MySQL would still have these databases fully visible if you had all rights enabled.
You can verify this by going into the OS and running the following:
cd /var/lib/mysql
ls -l

You should see the mysql and performance_schema databases as Linux folders. Thus, they are physically there. The problem is just the rights you have after user authentication.
The only way to fix it is to create the root@localhost user with all the rights needed. I am going to show you how to create the MySQL user root@localhost in the Linux server
Step 01) Create a script to create user
If root had a password (such as mys3cr3t), run this
SQLSTMT="GRANT ALL PRIVILEGES ON *.* to root@localhost"
SQLSTMT="${SQLSTMT} IDENTIFIED BY 'mys3cr3t' WITH GRANT OPTION;"
echo ${SQLSTMT} > /var/lib/mysql/init.sql
chown mysql:mysql /var/lib/mysql/init.sql

Step 02) Restart mysql using the init.sql file
service mysql restart --init-file=/var/lib/mysql/init.sql

Step 03) Remove /var/lib/mysql/init.sql
rm -f /var/lib/mysql/init.sql

Now, you have root@localhost with the password mys3cr3t
Give it a Try !!!


Drop & re-add subscribers to transactional replication

When you create a publication, you can choose whether or not you want to create a snapshot. Similarly (and more importantly), when you create a subscription, you can say if you want it to initialize or not. Choosing not to is probably what you need, but I suggest you test it before doing it in production


The PL/SQL equivalent would be something like this:
create or replace function get_article_total
    ( inProjectId    ata_articles.projectid%type
    , inAtaID        ata_articles.ataid%type
    , inPaymentType  number )
    return number
as
    articleTotal number;
begin
    if inPaymentType in (2,3) then
        select sum(aa.total) into articleTotal
        from   ata_articles aa
               join ata a on a.ataid = aa.ataid
               join invoices i on i.id = a.financeid
        where  aa.projectid = inProjectId
        and    aa.ataid = inAtaID
        and    i.status = 6;

    else
        select sum(aa.total) into articleTotal
        from   ata_articles aa
               join weekly_report wr on wr.id = aa.wrid
        where  aa.projectid = inProjectId
        and    aa.ataid = inAtaID
        and    wr.status = 5;

    end if;

    return articleTotal;
end get_article_total;


SQL Server Transactional Replication With subscriber trigger returning a value

this isn't so much an answer as a workaround. I am saving the value generated in the trigger on a table on the subscriber with the primary key of the transaction from the publisher. Then the application is querying that table on the subscriber immediately after the insert.


How to return a single composite type from a plpgsql function without repeating the composite type's parts explicitly as OUT parameters?

You can return a row type by using an expression with that type.
here it's effectively select my_table from my_table

create function my_func4(f1_to_look_up int) returns my_table language plpgsql
as $$
    begin
        return my_table from my_table where f1 = f1_to_look_up;
    end;
$$;

or via a variable

create or replace function my_func4(f1_to_look_up int) returns my_table language plpgsql
as $$
    declare
        retval my_table;
    begin
        select into retval * from my_table where f1 = f1_to_look_up;
        return retval;
    end;
$$;

You can also use a row constructor, which would be especially useful if the query is more complicated and you need to return values taken from more than one table joined together:

create function my_func5(f1_to_look_up integer)
 returns my_table
 language plpgsql
as $function$
begin
  return row(t.*) from my_table as t where f1 = f1_to_look_up;
end
$function$
;

The key is that where the plpgsql syntax documentation indicates that you can write return expression, "expression" can be everything that would ordinarily follow "select" in a normal SQL query.


Why my mongodb Shell not getting started even after my mongodb server is running?

You have managed to kill your mongod middle of index build.

  found 2 interrupted index build(s) on datastalk.stalk

Build of first index ({ v: 2, key: { _id: 1 }) didn't take long. Second index ({ v: 2, unique: true, key: { EMAIL: 1.0 }, name: "EMAIL_1", ns: "datastalk.stalk" }) is taking longer.. And at point of 35%, system got access violation when reading memory address 0x0000005648530000

Do your machine have enough memory and memory DIMMs are good?

You can try to solve this problem with wiredTiger command line tools. Use dump to get your data from database to file, re-create your mongodb instance (remove all files from dbpath OR point your dbpath to other directory and restart mongod) and then import data back to database (re-creating needed indexes)


Can you stage it twice?  First, shred the data into rows of XML, and then read the columns from the rows.  Something like:

with XmlNamespaces ( 'urn:xpaykj-report-xml-1.0' as xkj)
    Select
     xItem.query('.')  as RowXML
Into #loadtemp
    From @xmldoc.nodes('xkj:output-data/xkj:childs/xkj:child[@name="output"]/xkj:childs/xkj:child[@name="lvla"]/xkj:childs/xkj:child[@name="lvlb"]/xkj:childs/xkj:child[@name="lvlc"]/xkj:properties') as x(xItem)

should give you rows of XML; then apply your value statements to shred the table into columns.


Design Sql Server physical configuration from existing VM

That means that a VM with 16 CPUs will have 16 vCPUs with 1 core each.

Not exactly, that means initially a Host with a CPU of 16 cores should only have 1 VM with 16 vCPUs or 2 VMs with 8 vCPUs each instead. But as the chapter 3.5.2 Allocating vCPU says:

  if the physical server that the various SQL Server
  workloads currently run on equates to 16 physical CPU cores, avoid
  allocating more than 16 virtual vCPUs for the VMs on that vSphere host
  during the
  initial virtualization effort.

Notice it says initial virtualization effort. So, after some time, you will probably find out that it's not the best configuration for your environment. And it continues:

  After you have determined that there is excess capacity to be used,
  you can consider increasing density by adding more workloads into the vSphere cluster and allocating virtual vCPUs beyond the available
  physical cores. Consider using monitoring tools capable to collect,
  store and analyze mid- and long-terms data ranges.

It's not that there is a faster ratio then 1:1, it's just that apart from being the fastest it's the most expensive ratio. In a VMware environment you can find performance charts that will give you some information about the workload on a VM.

  Can we calculate physical server dimensions using revese engeneering?
  I mean : 16 vCPU equals 16 physical core?

Yes, but

lets say that VM has been using only 4 vCPUs at peak to process its
workload. When you create the physical server with 16 physical cores,
12 of them will be sitting doing nothing and you paid for that;
Now let's say that VM has been using all 16 cores at peak and you
build a physical server with exactly 16 cores. Next month, when the
workload increases requiring 17 cores to run properly, you'll have to
by new hardware.

So, if you really decide to go for a physical environment and just replicates physical copies of your VMs without the monitoring information of your servers, chances are good of sizing them ineficiently.

If your only motivation to change from VM to physical is the size of your VMs, check the Configuration Maximums of vSphere and you might find out that you still have plenty of room to grow your VMs (provided you have hardware on the host).


SQL Delete rows where a dataname will equal mulitple variables

You could use the IN operator for example

WHERE id IN (1,2,3)

How are you deciding which rows you want deleting, that might help us answer?


SQL Server Diff not restoring from Azure BLOB storage

Most likely, you have multiple Differential backups in your DB_NAME_DIFF.bak file, and the restore is picking one that occurred before the most recent FULL.  As I hinted in my comment, and Scott stated in his.

Edit the following to check for multiple files in a backup device:

RESTORE HEADERONLY
FROM DISK = N'C:\AdventureWorks-FullBackup.bak'
WITH NOUNLOAD;
GO


I would go for:

SQL Server Express with Tools (with LocalDB, Includes the database engine and SQL Server Management Studio Express)

This gives you MS SQL Express and the Management Studio which will give you all the playground to experiment.


getting error while starting memecached service with ndb engine

Make sure that you have at least 2 unused API slots ([mysqld] sections) in your configi.ini file (and perform a rolling restart of your Cluster after adding them). These slots will then be used by memcached.

Note that you can choose to run mysqld processes on the host as well and have them part of the cluster (can access the same data through SQL and NoSQL) - you just need to make sure that you include enough [mysqld] sections in your config.ini file so that there are a couple left for memcached.


Oracle Loop through values from one table and join with another table

Two ways to do this would be with NOT EXISTS with an INNER JOIN or MINUS

INNER JOIN

SELECT *
FROM table1 t1
WHERE NOT EXISTS
(
SELECT t2.contract_id
FROM table2 t2
INNER JOIN table1 t3
ON t3.contract_id = t2.contract_id
WHERE t2.status = 'ACTIVE'
AND t3.package_id = t1.package_id
);

MINUS

SELECT *
FROM table1 t1
WHERE EXISTS
(
SELECT t2.contract_id
FROM table2 t2
WHERE t2.status = 'ACTIVE'
MINUS
SELECT t3.contract_id
FROM table1 t3
WHERE t3.package_id = t1.package_id
);

DB <> Fiddle


Merge replication (link) might be a suitable solution for synchronising your data. Controlling the access so that your branches can only edit certain parts of the data should be controlled within your application.


Why does SSRS not update a report's description?

This is a "known" issue/feature according to Microsoft.  Descriptions, parameter defaults, subscriptions, etc. all fall under report "meta data" and are maintained separately on the Report Manager server.  The only way to get these to change on the Report Manager is to either manipulate them manually or delete the report and upload anew.  The links below deal specifically with this issue in regards to report parameters, but the same is true for descriptions.
Here is an excerpt from a Technet Article regarding parameters

Parameter properties that you set in Report Manager for a published report are generally preserved if you republish the report definition from Report Designer. If the report definition is republished as the same report, and parameter names and data types remain the same, your property settings are retained. If you add or delete parameters in the report definition, or change the data type or name of an existing parameter, you may need to change the parameter properties in the published report.

This is a related Connect issue

Parameter defaults do not get updated when re-deploying existing reports. These either have to be updated manually or the reports deleted and re-deployed. The latter regenerates all report ID's (GUID's) and makes traking usage from the ExecutionLog more difficult.
This is explained here (link) as being by design however I can't envisage parameter defaults and prompts being maintaned by an administrator.
An override mechanism similar to OverwriteDataSources should be added to Reporting Services projects to allow deploymnent from Visual Studio.


How to find exact string from a sentence

You could do something like this:

When considering that a word like water can only be surrounded by spaces, comma's and square brackets.

SELECT * FROM b WHERE list LIKE '%[water,%'
OR list LIKE '%, water,%'
OR list LIKE '%, water]%';

And you could technically remove some of the wildcards:

SELECT * FROM b WHERE list LIKE '[water,%'
OR list LIKE '%, water,%'
OR list LIKE '%, water]';

SQLliteOnline example #1

SQLliteOnline example #2

Both returning:

  NOTE: I'm looking for an answer that uses ignore cases, for instance
  if I want to find water I want to get wAtER and WatER, and so...

Like operator should be case sensitive.

  Note that SQLite LIKE operator is case-insensitive. It means "A" LIKE
  "a" is true. However, for Unicode characters that are not in the ASCII
  range, SQLite LIKE operator is case sensitive e.g., "Ä" LIKE "ä" is
  false.

Source

If it wasn't, COLLATE NOCASE could be used
More on that here


How do I send a database to someone else?

Select a.name, b.physical_name
from sys.databases a
join sys.master_files b
    on a.Database_id = b.database_id
Where a.name = 'NAME_OF_YOUR_DATABASE'

This will show you the location on disk.. (can't move it though).

Backup the database:

BACKUP Database NAME_OF_YOUR_DATABASE
To Disk = N'C:\Databases\NAME_OF_YOUR_DATABASE.bak With Compression


Because probably you haven't created an 'admin' user in Oracle yet. There's no built-in 'admin' user in Oracle DB. Try to connect as SYS "as sysdba", or maybe you need to run some prerequisite install procedure in your DreamCoder IDE.


extract State Entity from String using regex_substr

You could create a table with a list of state names, and use that to cross reference against the list.

Something like:

SELECT s.state_name
FROM states s
    INNER JOIN addresses a ON a.address LIKE '% ' || s.state_name || ' %';

Since you cannot create tables, you could use a CTE with defined values for each state name, something like:

WITH states (state_name) AS
(
    SELECT 'Alabama' FROM dual
    UNION ALL SELECT 'Georgia' FROM dual
    UNION ALL SELECT 'Alaska' FROM dual
    ...
)
SELECT s.state_name
FROM states s
    INNER JOIN addresses a ON a.address LIKE '% ' || s.state_name || ' %';

I'm not an Oracle rockstar; who knew || was concatenation


The documentation at Distributed availability groups states:

  The individual WSFC clusters and their corresponding availability groups follow traditional rules. That is, they can be joined to a domain or not joined to a domain (Windows Server 2016 or later).

Regarding your question about all four nodes being in the same cluster--if they are in the same cluster, then there would be no need to have a distributed availability group (DAG), you would just use an availability group with four replicas.  Additionally, the documentation referenced above states:

  A distributed availability group spans multiple availability groups, each on its own underlying WSFC cluster.

So if it is possible to set up a DAG with all nodes in one cluster, it is contrary to the documentation and may not be supported.


You can install as many instances of SQL Server as you like on a server, up to 50. The licensing is by the server, not by the instance (it used to be, once upon a time) so you have some freedom to do so.

The real question I would counter with is: Do you need another instance? Most people don't.


Do I need SQS queues to store remote data in the Amazon Web Services (AWS) cloud?

No SQS is not required. SQS is a publicly available service that only requires an authentication token in the request header.  You can grant that using an IAM user account with security keys or if the calling service is another AWS account you can grant access by IAM roles.  S3 is also a public service that works similar to SQS with regards to access. The process for setting up cross AWS account access is a bit different but the concept is the same.  You also can send data directly to an EC2 instance by either assigning it a public IP address in a public subnet or by using an Elastic Load Balancer (ELB).

SQS is a queue service that only holds onto the data for another service to pull down. However if you are looking to have a service broadcast a dataset to multiple destinations you are looking for SNS. SNS will allow you to send to multiple destinations and each destination can have its own type of delivery. For example if you posted a message to SNS you could have it deliver that message to a lambda function in AWS and a web endpoint that accepts JSON.

The only caveat to all of this is that SNS takes the message it receives and sends it on to the destinations without any transformation so each destination will receive the exact same message and be responsible for extracting the data.


How to schedule emails out with SQL Server

First of all, are you really sure you need to reinvent the wheel? There are open source service desk software such as http://www.otrs.com/software/

Secondly, sending e-mail using database engine is not a good idea. Given that SQL Server box is really expensive, try to avoid spending CPU time on tasks that can be done outside of SQL Server. For example, you can send emails using your application.

But if you really want to stick with your idea, like you said, there is no better way than to create a scheduled job.


Workbench freezes on connecting after Ubuntu 18.04 upgrade

After further investigation I managed to resolve this issue, it seems that after the upgrade my settings must've got screwed up. The connection that wasn't even the one I was attempting to use which was a bit strange.

I went into the paths that mentioned in the log:

11:39:08 [WRN][            grt]: /usr/share/mysql-workbench/modules/data/mysql_rdbms_info.xml:1401: link 'om.mysql.rdbms.mysql.driver.native_sshtun' <object > key=owner could not be resolved
11:39:08 [WRN][            grt]: /home/<REPLACED USERNAME>/.mysql/workbench/connections.xml:27: link '6d8ecd74-acad-11e8-9c35-408d5cdbf11f' <object GrtObject> key=owner could not be resolved

And deleted the 'value' tags as well as the connection that was throwing the error and restarted mysql workbench. After the restart some of the connections I had setup were gone (which I can just recreate) but it seems that I can connect fine now.


Don't show local databases in SQL Always-On AG

When testing the features we notice that all databases from a node are listed when we connect to a listener.

Correct, this is the expected behavior as the listener only "points" you to the instance where the primary databases for that availability group are currently located. It does not scope permissions or any other securable.

  Is there a way to only show the databases that are part of the AG and not the databases from other AG's or local DB's on the node that hosts the listener?

No, not in the way you're thinking due to what I states above that a listener is not a securable. If you only want an application to "see" a specific set of databases then set the permissions to those databases only. If you scope the permissions in this way, they will not show up under SSMS but the login will have access to them (assuming the login is not the database owner [this is not the same as the db_owner role]).


Is there any way to speed up a query with 3x UNION on the same large table?

You can certainly project foo, bar, baz and qux in a single scan (SELECT foo, bar, baz, qux FROM T1).  A CROSS APPLY TVF can transform each row (4 columns) into 4 rows (1 column) and then apply DISTINCT on top. If the original plan had 4 scans of T1 which would dominate the cost, this may be better.


This is a "relational division" problem and there are several ways to solve.

One way is with two EXISTS subqueries:

SELECT p.id
FROM photos AS p
WHERE EXISTS
      ( SELECT 1
        FROM photos_tags AS pt
        WHERE p.id = pt.photo_id
          AND pt.tag = 'cute'
      )
 AND  EXISTS
      ( SELECT 1
        FROM photos_tags AS pt
        WHERE p.id = pt.photo_id
          AND pt.tag = 'cool'
      ) ;

Another with two joins (assuming that the photo_tags table has a unique constraint on (photo_id, tag_id), i.e. no photo can been tagged with the same tag twice):

SELECT p.id
FROM photos AS p
  JOIN photos_tags AS pt1
    ON  p.id = pt1.photo_id
    AND pt1.tag = 'cute'
  JOIN photos_tags AS pt2
    ON  p.id = pt2.photo_id
    AND pt2.tag = 'cool' ;

One more way is @Abhik's answer, with one join and GROUP BY.

You can see a few more ways (more than 13) in this awesome answer: How to filter SQL results in a has-many-through relation. Not all of them will work in MySQL as the question is for Postgres but the problem is identical.

Strictly speaking - and if there is a FOREIGN KEY from photo_tags to photos - the photos table can be removed from all the above queries. You can return the photo_id instead. But I guess you may want more columns from the photo table in the result set.


Impact of switching a column's encoding from utf8 to utf8mb4 in MySQL

First, a syntax error:  it's MODIFY, not CHANGE, unless you are changing the column name, too.

I ran an experiment with only 1/2 million rows.  ALTER to change one column from utf8 to utf8mb4 took 1.6 seconds.  I conclude that the table was copied over; that is O(n).

When the developers added the various speedups for ALTER, I suspect they overlooked the case of utf8->utf8mb4, or decided that it was a risky special case -- what if later the charsets diverged?  Or the collations diverged?  Granted, without any index on the column, that should not matter.  (But that would be more more thing to test for before doing the Alter some faster way.)


Performing data update operations while backing up a large SQL Server database

Essentially, the backup will be of the state of the database when it finishes the data-reading portion of the backup (so all of the data will be backed up), plus whatever amount of transaction log is required to ensure transactional consistency (the start time of the included log is MIN(most recent checkpoint time, oldest active transaction start time)). Paul Randal covers this here (with aid of a diagram, which makes it all so much easier). In your example, A would be committed (or rolled back if a ROLLBACK TRANSACTION was issued instead of a COMMIT) and B would be rolled back (regardless of the end result of that transaction).

(The other reason you try and do backups at a quiet time, aside from I/O contention, is that all of the transaction log generated during a backup normally has to be included with the backup.)

The recovery phase of a database restore takes all the committed transactions from the log included in the backup and applies them to the database, and rolls back all the un-committed transactions. (This is why WITH RECOVERY/WITH NORECOVERY is important. WITH RECOVERY and you can use the database, but you can't apply any further log backups, you need to restore it WITH NORECOVERY in order to roll in log backups. Recovery breaks the log chain by rolling back uncommitted transactions.)

Further reading:

30 SQL Server backup myths
Debunking a couple of myths around full backups
Logging and recovery in SQL Server


Column-oriented database engines, not just MariaDB ColumnStore, are specifically optimized for read operations on large volumes of data. For this they use auxiliary data structures (like ColumnStore extent maps), query parallelism, compression etc. This comes at the cost of increased overhead for standard DML operations. This is often explicitly stated in various technical documents, like this quote from the InfiniDB technical overview:

Note that because InfiniDB is a column-oriented database, insert and delete statements will not normally run as fast on InfiniDB as they will on row-oriented databases (more blocks must be touched).

or this from the MariaDB architecture presentation (see slide 22):

DML Writes

Slow compared to other engines

INSERT is very slow compared to cpimport

Vendors often provide special tools for bulk loading data into columnar tables (e.g. the cpimport utility mentioned above) to address this problem. This fits well into the typical columnar engine use case, that of an OLAP system with (relatively) infrequent mass updates and heavy read-only workloads.
In other words, you should not judge a column-oriented database by its DML performance, because that's not what it is designed for.


System to track what Zip Codes my vendors service

I would say you would have:

Table: vendor
  id, name, etc

Table: vendor_area
  id, vendor_id, area_code_id

Table: area_code
  id, area_code, country_id, region_id (etc)

I'm suggesting that not knowing your business. But I would hate you go in a naive route assuming that you'll do US only and in a couple year you have to redesign the whole thing. The above will give you flexibility to expand and add more meta data to your areas/regions being covered.


To do this, you don't want to have any filtering on the the Master A server. Instead you will want to use replicate filters on the slave, Server B

For a full understanding of replication filters, have a read through this documentation and its sublinks. I will highlight two caveats (emphasis mine):

  On the master, you can control which databases to log changes for by using the --binlog-do-db and --binlog-ignore-db options to control binary logging.....You should not use these options to control which databases and tables are replicated. Instead, use filtering on the slave to control the events that are executed on the slave.

and

  To make it easier to determine what effect an option set will have, it is recommended that you avoid mixing “do” and “ignore” options, or wildcard and nonwildcard options.

With these two pieces of information, I'd recommend setting up server B with --replicate-wild-do-table=db_name.%. This is because:

  If you need cross-database updates to work, use --replicate-wild-do-table=db_name.% instead.
  [src]


MySql UPDATE of tiny table is costly (0.2sec) - how to analyse further

Your innodb system variables indicate the innodb buffer pool is at 128M (the default). Its quite possible that the slowness of this single PK based update was due to this table not being in the innodb buffer pool. Look at the SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool%' and adjust the pool size up until the Innodb_buffer_pool_reads vs Innodb_buffer_pool_read_requests is very small (<1%).
This will be the case if 128M of more recently used innodb tables are in memory.
Another potential cause if there is a significant amount of write activity on the the server the innodb flushing /writing could be causing a delay because of storage limits.
If a f{data}sync of the disk can take 0.2 seconds occasionally that could correspond to the query time.


do partitions make sense when dates are not involved?

You did not explain why you want to partition the table and what do you expect from partitioning. You only mention table size, which is hardly a criteria for partitioning. Performance wise partitioning will make everything slower, not faster. The best you can hope for is on-par performance with the unpartitioned table. Some scenarios that do make sense with partitioning are:

ETL needs like load jobs that manipulate data intensively in staging tables and then switch in the entire staging table in one fast operation
need to remove large volumes of data that has past retention period (monthly switch out and truncate)
administrative reasons, like the need to rebuild individual partitions

Many quote reasons like 'move old data to slower disks' but I don't buy that argument much. And another oft quoted reason is to distribute data in multiple files, but that is incorrect thinking as a filegroup can contain multiple files across many volumes and the engine would distribute IO across them anyway w/o any partitioning need.

You mention that your developers quote a case when partitioning by data increased performance. Perhaps is a case of a time series data that was clustered by id, not by date and in which all range queries (typical for time series) had to do table scans. Partitioning appeared to help because partition elimination reduced the amount of data scanned. But a proper clustered index would had addressed the problem much better (no surprise there, indexing is the usually correct answer to query performance issues).

An extreme case when partitioning is useful is hash partitioning to help spread insert last page latch contention.

But when push come to shove the partitioning is an 'all-or-nothing' approach that has a very heavy impact (consider that you can no longer have an unique primary key that does not include the partitioning field anymore) and percolates through the data model everywhere (eg. a lot of foreign keys have to be re-designed). It requires careful administration. Query Optimizer can misfire some horrible plans in presence of partitioning.

Kendra Little has a nice article explaining pros and cons: How To Decide if You Should Use Table Partitioning


MS SQL difference between two latest records

You can use lead() window function to get next record value (order by time desc for each event). row_number()=1 will give you just the first row per event_type

select t1.event_type, t1.value - t1.prev_value as diff
  from (
     select t.event_type,
            t.value,
            lead(t.value) over (partition by t.event_type order by time desc) prev_value,
            row_number() over (partition by t.event_type order by time desc) rn
       from events t) as t1
where rn=1
  and prev_value is not null-- this will prevent event_types with just one row


The service specification is the name of the service used for query notifications.  The tutorial is focused on SqlDependency, which creates dynamically-named services (GUID appended).  No need to explicitly grant SEND permissions on the service in that case because the creator of the service will have CONTROL permissions on the service anyway.

If you were to use the lower-level SqlNotificationRequest object, then you would need to grant SEND on the service, REFERENCES on the contracts, and RECEIVE on the queues.  That allows a less privileged account to subscribe to query notifications.


ORA-01017 when connecting remotely as sysdba

Filenames (and Oracle instance names) on Linux are case sensitive. You keep using MYSID but you created the password file as orapwmysid. MYSID as a TNS alias is not case sensitive. MYSID as an instance name is case sensitive and having the password file named lowercase will prevent logging in using SYS with the above error.

[oracle@o71 ~]$ ps -ef | grep smon
oracle     923 31725  0 16:56 pts/0    00:00:00 grep --color=auto smon
oracle   31761     1  0 May15 ?        00:00:37 ora_smon_MIN18
oracle   32529     1  0 16:49 ?        00:00:00 ora_smon_MIN19
[oracle@o71 ~]$ ls -l $ORACLE_HOME/dbs/orapwMIN19
-rw-r-----. 1 oracle oinstall 2048 Apr 30 00:17 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwMIN19
[oracle@o71 ~]$ sqlplus sys/Oracle123@min19 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 29 16:56:37 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@o71 ~]$ mv /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwMIN19 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwmin19
[oracle@o71 ~]$ sqlplus sys/Oracle123@min19 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 29 16:56:45 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:

Even if you change the SYS password with ALTER USER..., the password file will not be generated correctly in this case:

[oracle@o71 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 29 16:58:19 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter user sys identified by Oracle123;

User altered.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@o71 ~]$ ls -l /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapw*
-rw-r-----. 1 oracle oinstall 2048 Apr 30 00:17 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwmin19
[oracle@o71 ~]$


Centralize Oracle RMAN scripts in one server

Forget copying binaries around. I would integrate remote RMAN executions into your normal backup application/solution (i.e. filesystem backup to fire a script) for the sake of consistency. Especially scheduling of backups would be easier.

Re: "In this case parameterfile.par must exist in SRV_PROD_1". Not a problem if you dig into RMAN's own CREATE SCRIPT syntax - you can store the run{ } script inside your catalog.

Re: "rman.log will be created also on SRV_PROD_1". Not a problem if you take a look at catalog schema contests - surely there is one large table per each catalog that holds the logs anyway (I can't remember if it's ROUT or RMAN_OUT?).


Setting up cloud database for syncing to local SQLite database

While CDC is one useful methodology in SQL Server, it can also be a little performance heavy too depending on how much data and how frequently it changes.
You might want to consider a 3rd party tool to manage your synchronization between SQL Server and SQLite, which can help minimize the performance and maintenance overhead of a homebrew solution.
For example SQLiteSync by Amplifier is supposed to work well for this kind of use case (note I haven't used it myself though).
https://ampliapps.com/sqlite-sync/


Fetch data from database from multiple tables matching condition

SELECT
    *
FROM
    listing_master_residential res
    INNER JOIN listing_master_condo con ON res.Ml_num = con.Ml_num
    INNER JOIN listing_master_commercial com ON com.Ml_num = res.Ml_num
WHERE
    res.Ml_num = '$mls'

Also, instead of selecting ALL (*), select only the columns you will be using, this will help your query perform better.


The syntax error you are getting is from the $() this is taken as a token in SQL Server Agent context...so it will always bark at that; removing it should fix the syntax error.

On the copy issue you will likely need to specify the property you want to pass into Copy-Item. Just passing $myfile is not going to work because that contains the full object of the output from gci. So change it to something like:

$myfile = gci $mypath | sort LastWriteTime | select -last 1 -ExpandPropety FullName
Copy-Item -path $myfile -destination $mydestination

Now $myFile will contain the full path to your last file:


If I understand you properly - you want to have a second reporting instance and you are asking how you can keep that updated.

It also appears you are on SQL Server 2005 so your options are limited more so than, say, if you were on SQL Server 2012 with Availability Groups.

Your options are really going to be in the spirit of:

Log Shipping
And here you can actually restore your logs in WITH STANDBY mode - this means that you can effectively query your log shipped database for read only queries anytime. You can read more about that here and at the related links on that article. You cannot add indexes, add statistics, do any writes, etc. But you can avoid creating a snapshot. But you still require the blip of connectivity when the standby restore happens - so you haven't saved much.

Mirroring
Same idea of log shipping (at a very basic level that is) - and here you can read off of a standby. Still ned that downtime of the reporting users to rebuild your snapshot. You can do A/B switching and effectively abstract some of that through views, content switching/load balancing but you still need a downtime for the snapshot change.

Replication
You can use transactional replication and replicate data. This is near real time and updates just happen as they happen on the primary and get replicated (or you can control the frequency). This has more of an overhead on the source, latency really matters, requires a bit more DBA overhead, but it allows you to report on the secondary with no need to worry about standby restores, or new snapshots. Benefit here is you can choose what comes across and what doesn't if the reports only care about certain tables.

ETL
You could always roll your own process to extract, transform and load just the data you care about. This way you can create a reporting optimized version of the database, index it appropriately and stream data in at the frequency you want. You need to worry about concurrency and blocking risk during your loads if it needs to be updated throughout the day, but there are ways of dealing with that. More overhead and developer time required.

And I would really encourage you to think about SQL Server 2012/2014 - AlwaysOn Availability Group Active Secondaries are a benefit here potentially. The main benefit being the read only secondary database. There are some optimizations here including the ability to have temporary statistics created and stored in the TempDB database on the secondary instance. The read only doesn't require snapshots, with standby, etc. It is truly read only - so you cannot write into it - but for reporting this can be a big win. Granted the move from 2005 to 2012 is a bigger conversation, requires licensing conversations, etc. but it is an option here for sure.

If you are talking about HA  for SSRS that is a separate conversation.


When I connect to DB, always connects as tempdb

It seems the DBA who has created your login has set tempdb as your default database. You may request him/her to do the change at login definition level as per this article. By default, if none of them are mentioned, user connects to master database. You may use below command:

sp_modifylogin login_name, defdb, "your_database"

Kindly note that above command needs to be run at Sybase Database level and only a System Administrator or System Security Officer can execute sp_modifylogin.

As already suggested by Markp, you can issue the command use <database_name> for changing your database. Alternatively you may use database name in the connection string itself.

Below is an example of isql for connecting to "your_database"

isql -S"Server_Name" -U"Login" -D"Your_Database" -P"Password"

You may refer below for isql options:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc30191.1570100/doc/html/san1367605044069.html

Hope above helps.


Is there a limit to how many backups can be appended to a single file?

TL:DR; It is possibly to put 32,000+ backups on a single file.  If this is good thing or if you can recover from a backup on this file is not addressed here.

I started taking tlog backups last night, on an existing database (231682) with no activity.  I used a while loop and a counter so I could get a running total.

DECLARE @counter int
SET @counter = 1
While 1=1
Begin
BACKUP LOG [231682] TO
DISK = N'G:\SQLBackups\Test_Tlog.trn' WITH NOFORMAT, NOINIT,
NAME = N'231682-Log Database Backup', SKIP, NOREWIND, NOUNLOAD
SET @counter = @counter + 1
print @counter
End

16 hours later the count is at 5,967 with a file size of 356MB. This might take a while. At the start was completing 24 log backups per second.
1 day 16 hours: count is 8,401 with a file size of 700MB, sp_whoisactive shows wait info (2029ms)BACKUPTHREAD
4 days 18 hours: count is 12,834 with a file size of 1.6GB sp_whoisactive shows wait info (52113ms)BACKUPTHREAD time between backups has slowed to about 70 seconds per log backup.
5 days and some hours; an external event (server reboot) caused the tlog backups to halt. count of completed backups is 13,717 file size is 1.8GB, time between backups is about 80 seconds. Validated with
restore headeronly from disk='G:\SQLBackups\Test_Tlog.trn' Did not attempt a database restore, as it would be beyond painful. Set counter to start SET @counter = 13717 and restart adding backups to the same file with the same code.  Backups resume and are taking about 80 seconds
1 week; second restart due to external issues.  Count is 15,186 file size 2.3GB each backup is taking about 90 seconds.
Week and half; Count is 17,919 file size is 3.2GB each backups is taking about 2 minutes 25 seconds.
2 Weeks; Count is 19,645 file size is 3.8GB each backup is taking about 2 minutes 45 seconds.
3 Weeks; count is 22,919 file size is 5.2GB each backup is taking about 3 minutes 30 seconds (17 per hour) as I am running the endless t-log backups in a job now, I have added this to the code RAISERROR(N'Count equals :%d', 16, 1, @counter ) WITH LOG; so the running count displays in the SQL error log Thank you @Erik Darling
4 weeks; count is 25,587 file size is 6.6GB each backup is taking about 3 minutes 45 seconds (16 per hour)
5 Weeks; count is 28,242 file size is 8GB each backup is taking about 4 minutes 35 seconds, (13.5 per hour)
6 weeks; count is 30,037 file size is 9.1GB each backup is taking about 5 minutes (12 per hour)
~7 weeks: Running out of space on the dedicated backup disk. Backups start failing when insufficient space, but the job keeps running and trying. Stop everything else, made a bit more space by deleting some files. The count in SQL logs is wrong, as it counts both failed and successful attempts.

Stop job
File size is 10.4GB each backup is taking about 5 minutes (12 per hour), time is the same on success or failure.
Check backup with restore headeronly from disk='G:\SQLBackups\Test_Tlog.trn' Count is 32,021
Header implies all the successful backups are fine.
Most backups show a value of 75,776 for BackupSize and a value of ~4,000 for CompressedBackupSixe, Compressed size varies on each backup.
I did not attempt a Restore with the t-log file. I suspect there will be issues, and I would want to try it before I made everything as ugly as it is now.
Delete the t-log file, restart normal backups.

I am compressing backups by default on this instance.

Week 3 Note: File size and backup time is growing out of proportion to number of backups. Looking at tlog headers, we see the backup in position 2 has a size of 75766 bytes and a start to finish time of one second or less.  The backup in position 22919 also has a size of 75766 bytes and a start to finish time of one second or less.  The overhead of appending the backups to the same file seems be causing the slow down. The abnormal growth is probably related to weekly maintenance tasks I have running on the instance.

Off site backups, It looks like my offsite backup solution (IBM Spectrum) is not backing up the trn file.  I suspect this is because the file is constantly being edited.

Edit, some time later.  I was considering doing another experiment to test recovery at around 30,000 backups. To avoid the issues of trying to restore multiple t-logs I looked at using differential backups.  I created an empty database, took a full backup and then took 10 differential backups.  Then I took 10 t-logs backups and using RESTORE HEADERONLY FROM DISK I compared the size, the differential backups are significantly larger then the t-logs, I don't have a enough space to perform a good test.

Differential backups 2-10 (first is always a bit bigger)

BackupSize = 1126400
CompressedBackupSize = 62271

T-Logs backups 2-10 (first is always a bit bigger)

BackupSize = 75776
CompressedBackupSize = 3800 (average, it varies)

Differential backups are about 16 times larger, best case I could only get about 2,000 of them, I am not doing further testing at this time.


Let's try again, but with the correct statement ;)

When you cancel a query in psql, what it does internally is to send a pg_cancel_backend() to the server. This returns directly, but the statement might still be running on the server.

When a query is run, there are a few times it checks if it safely can cancel the statement, and in that case do so. This applies to a drop database statement as well.

There is a short time between the database files are removed from disk and the statement is committed where things can get interesting. It is not possible to cancel the query during that phase, but if there is a server crash then there is a risk that the database still exists in the meta tables, but the files are removed from disk.

Update, clarification of when it can be cancelled

When the server receives a cancel request, it marks a flag that the statement should be cancelled, and the statement checks that flag once in a while during execution. To explain what happens when we cancel the drop database we need to understand what it does:

Statement is received from the server.
Implicit transaction is started.
Lock is taken on the database.
Checks are made to make sure that the database is not being used.
The database is removed from the meta tables.
A checkpoint is written in the WAL.
The database files are removed.
The transaction is committed.

At any point up until #6 the statement can be cancelled (the final check of the flag is done at #6)


Stuck in "query end" status every 5 minutes

All uses of that table will run faster (some will run a lot faster) if you jettison id and make name the PRIMARY KEY.

Your UPDATE is currently looking in 2 BTrees, plus updating 2 BTrees.  The PK change would change those to 1 and 2.  More importantly it would simply the row locking.

How big is the table?  How much RAM do you have?  What is the value of innodb_buffer_pool_size?  innodb_io_read_threads?  What the heck, let's see SHOW VARIABLES LIKE 'innodb%';


Disk space efficient foreign key index for insert intensive scientific database?

The referencing columns don't need to be indexed at all.  If not, then some operations on the referenced table might be extremely slow (e.g. verifying that a row to be deleted on the referenced table has no referencing rows, or if it does than cascading actions to them) but if you never do those operations it wouldn't matter.


How to calculate running balance for many accounts?

There is something niot right, but you can take it from here.

I replaced your FROM clause, because you need a sorted list to do that coalculation and mariadb  takes the definition, that tables are unsorted literary and eliminates all sorting in subqueries. so you have to circumvent it

Check the numbers, i think you must work on that.

SELECT  id,account_no,account_type,transaction_id,
    DATE_FORMAT(transaction_date,
            '%d/%m/%Y') transaction_date,
    debit_balance,
    credit_balance,
    IFNULL(afp_id, - 1),
    (@current_period_id:=IF(IFNULL(afp_id, - 1) <> @current_period_id,
        IFNULL(afp_id, - 1),
        @current_period_id)) current_period_id,
    (@running_balance:=IF(@curr_account_no < account_no
            OR (@current_period_id <> IFNULL(afp_id, - 1)
            AND account_type > 5),
        0,
        @running_balance)) prev_runnng_bal
        ,
    (@curr_account_no:=IF(@curr_account_no < account_no,
        account_no,
        @curr_account_no)) curr_account_no
        ,

    (@running_balance:=@running_balance
    + (debit_balance - credit_balance)) AS running_balance
FROM

    (SELECT aat.*,at.debit, at.credit, at.transaction_date, aac.account_type
    ,afp.id as afp_id FROM
    acc_account_transactions aat
        INNER JOIN
      acc_transactions at ON at.id = aat.transaction_id
        INNER JOIN
    acc_accounts aac ON aac.id = aat.account_no
        LEFT JOIN
    acc_finacial_periods afp ON DATE_FORMAT(transaction_date, '%Y/%m/%d') >= DATE_FORMAT(startdate, '%Y/%m/%d')
        AND DATE_FORMAT(transaction_date, '%Y/%m/%d') <= DATE_FORMAT(enddate, '%Y/%m/%d')
        ORDER BY account_no,aat.id LIMIT 18446744073709551615 ) aat1
    ,(SELECT
        @running_balance:=0,
            @curr_account_no:=0,
            @current_period_id:=- 1
    ) r

db<>fiddle here

To yxpand the idea

SELECT aat.*,at.debit, at.credit, at.transaction_date, aac.account_type
    ,afp.id as afp_id FROM
    acc_account_transactions aat
        INNER JOIN
      acc_transactions at ON at.id = aat.transaction_id
        INNER JOIN
    acc_accounts aac ON aac.id = aat.account_no
        LEFT JOIN
    acc_finacial_periods afp ON DATE_FORMAT(transaction_date, '%Y/%m/%d') >= DATE_FORMAT(startdate, '%Y/%m/%d')
        AND DATE_FORMAT(transaction_date, '%Y/%m/%d') <= DATE_FORMAT(enddate, '%Y/%m/%d')
        ORDER BY account_no,aat.id LIMIT 18446744073709551615

Builds the basis for the calculation.

It is a new sub query that sorts the data given so that you can get the wanted result.

the LIMIT 18446744073709551615  is there,because when mariadb processes the query above, it would remove the order by that is needed.

SELECT aat.*,at.debit, at.credit, at.transaction_date, aac.account_type
    ,afp.id as afp_id

are the columns that are needed for the calculation, if you need more column you first have to include them here. be aware mysql/mariadb doesn't like the same column name, so i have to afp.id as afp_id give the secnd id fiekld an alias.


Find out when your index was last rebuilt / reorganized

SQL Server does not maintain when an Index was last rebuild, instead it keeps information when stats were last updated.

That can be found using the STATS_DATE function.

You can use Ola's Index maintenance solution or Michelle Ufford's - Index Defrag Script. These scripts are widely tested in the community and are much flexible so that you can adapt as per your needs in your environment.

SQL Server  SP2 for 2008R2 and up has a new DMF sys.dm_db_stats_properties which tells you when your stats were last updated with other info like

  modification_counter: number of modifications for the column which leads the statistic, since the last update


How to import a.csv file into a postgresql database using \copy?

You tell the COPY command to look for commas as delimiters (DELIMITERS ','), but there are no commas in your CSV.  Use the 'text' format instead (it's the default, so you don't have to specify it) and do not specify a delimiter:

  The default is a tab character in text format.

(source)


Installing MySQL server 4.1 on Ubuntu 11.04

If you can run 6.06 in a VM on top of 11.04 server, it looks like MySQL 4.1 can be installed on that


Error while duplicating Oracle database after failover, using 11.2.0.4

The message indicates that no archivelog exists with this sequence in the current incarnation.

Failover starts a new incarnation, and log sequences start from 1 again. Let's say your database is at sequence 10 now, and the log sequence was 159 before the failover. V$ARCHIVED_LOG at this point still contains entries about previous incarnation, so the above query will return 159 because it doesn't consider the start of a new incarnation (resetlogs).

For these kind of queries always check resetlogs_change# or resetlogs_time, e.g:

select max(sequence#) from v$archived_log join v$database using (resetlogs_change#);

Fix this and try duplicating again. Or just skip the UNTIL clause and let log shipping take care of the archivelogs.


High CPU usage on SQL server - Slow queries

Affinity does not "adjust the CPU usage" (e.g. in your case make the CPUs perform less work), it allows you to either turn off a CPU (perhaps to make it available to another instance on the same machine) or set a CPU to aid with I/O only. Even if you had multiple CPUs, you wouldn't be able to use the former to help with your goal, and it is impossible for us to guess on the latter because we don't know what is driving your CPU usage so high. It could be due to extremely poor indexing, excessive compilations, abundance of scalar UDFs, I/O thrashing, who knows? (And the reason I/O could be the cause is that if your database is larger than 3 GB or so, it will constantly have to swap data in and out of buffer pool memory, and this takes its toll on CPU.)

CPU cache, also, is a rabbit hole you don't need to be going down. I highly doubt your CPU is thrashing at 95% because of problems with your CPU cache.

To help narrow down the source of CPU pressure, and assuming you're using stored procedures, you can take a look at this diagnostic query from Glenn Berry (sourced from here) - make sure you run it in the context of the right database:

-- Top Cached SPs By Total Worker time (SQL Server 2012).
-- Worker time relates to CPU cost  (Query 44) (SP Worker Time)

SELECT TOP (25)
  p.name AS [SP Name],
  qs.total_worker_time AS [TotalWorkerTime],
  qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
  qs.execution_count,
  ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0)
    AS [Calls/Second],
  qs.total_elapsed_time,
  qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
  qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

-- This helps you find the most expensive cached stored procedures from a CPU perspective
-- You should look at this if you see signs of CPU pressure

If you're not using stored procedures, then this example from John Samson can help isolate ad hoc queries (sourced from here):

SELECT TOP (25)
    qs.sql_handle,
    qs.execution_count,
    qs.total_worker_time AS Total_CPU,
    total_CPU_inSeconds = --Converted from microseconds
    qs.total_worker_time/1000000,
    average_CPU_inSeconds = --Converted from microseconds
    (qs.total_worker_time/1000000) / qs.execution_count,
    qs.total_elapsed_time,
    total_elapsed_time_inSeconds = --Converted from microseconds
    qs.total_elapsed_time/1000000,
    st.text,
    qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

You can also take a look at Adam Machanic's sp_WhoIsActive, a stored procedure that can quickly analyze all of the currently running queries, and allow you to sort it however you want (e.g. in your case @sort_order = '[CPU] DESC').

The first thing I would do, though - particularly if this really is mission critical for search and rescue teams - is buy better hardware. You should have more CPUs and more RAM to service your application. You also absolutely need better high availability (e.g. clustering, mirroring or Availability Groups). There is no reason that a reboot of a physical machine should take your application completely offline - we have better solutions for that problem. And finally, I presume this "server" only has one spinny disk drive. This means that all I/O - from the OS, from SQL Server data files, log files, tempdb, etc. all go through a single controller and share read/write activity on a single drive. Get more disks. Get SSDs if/where you can. Use RAID and try to spread the I/O out as much as possible.

That all said, throwing hardware at the problem is not going to be the only part of the fix. You need to isolate exactly what is causing excessive CPU usage and then attack those problems no matter what hardware you're on.

Also see this StackOverflow question for some other ideas:

https://stackoverflow.com/questions/945063/how-do-i-find-out-what-is-hammering-my-sql-server


How does Postgres store many varchars on disk?

Your considerations are sound, but all of this is already taken care of automatically by PostgreSQL:

A NULL value takes up no space at all in a PostgreSQL table row.
A varchar will only occupy as much space as the value actually has bytes. The length limit (type modifier) does not waste any space.
For rows that are really wide (2000 bytes and above), the varchars are first compressed and then, if the result is still too large, stored externally in a TOAST table. If you SELECT from such a row, the toasted values are not read unless you specify the column values are requested.

But if the size limit for a row is 850 bytes, that is too small to get in that range.

You can check the estimated average row size in bytes by looking at the width column in the output of EXPLAIN SELECT * FROM tablename.

PostgreSQL v12 introduced the toast_tuple_target storage parameter which would enable you to reduce the limit for TOASTing data. If you tune that, and after rewriting the table with VACUUM (FULL), PostgreSQL can store the data in the way you want to. Then you can see if that actually gets you better performance.


Query runs fast but occasionally never completes

It looks like the statistics are off. From the plan you posted it's estimating that it's going to read 64,000 rows, but it's actually reading zero. That's a very wide disparity. I'd suggest a few things. First, update the statistics with a full scan. Any index rebuilds ought to have taken care of that, but with this disparity I'm wondering if something is up there. Next, make sure that the constraints are all in place (although, this plan isn't referencing constraints since it's a straight index seek with a TOP operation). Finally, capture the wait statistics for the system to see what's actually causing things to run slowly. You can use extended events to capture the wait metrics for just this query, so that's an even better approach. Also, a 64,000 row range scan from a seek is a little excessive unless you have millions of rows. However, this could still be a part of the statistics being off.


Postgres Row Level Security policy optimizes poorly compared to inline version

Without sample data it’s difficult to reproduce your exact scenario.

It seems to me you should make the expression in the policy as simple as possible. In your case that would be:

CREATE POLICY select_member_of ON post FOR SELECT USING (group_id IN (SELECT group_id
            FROM group_member
            WHERE account_id = current_setting('current_account_id', false)::INT));


How can I determine the date of a snapshot in SQL Server 2008r2?

MSSnapshot_History has snapshot information for replication.

You can join to MSSnapshot_Agents to narrow it down to your specific agent, publication, and job.

These tables are found in the [distribution] database on your distribution instance.

Here's a basic query to get the last start time of your snapshot agents.

select a.name, max(start_time) laststart
    from MSsnapshot_agents a
        join MSsnapshot_history h
            on a.id = h.agent_id
            where h.runstatus = 2 -- this will get you the last successful execution
        group by a.name


I suspect you are running MySQL on Windows, although you did not mention it. If so, look in your Services and makes sure the MySQL service is started. Here is a link explaining what to do at dev.mysql.com.

If you are trying to connect to SQLExpress, the process is the same as above to make sure the service is running. Then, here is a link explaining PHP SQLExpress connectivity using PDO.


This is not a great design. You have a couple of options to improve it:

Go with your alternative (separate keys). You don't list your requirements, but if it's imperative you only have ONE child record per master record you can enforce this with check constraints.
Put the parent key in the child table. If every child record refers to one master record, just put the masterid in the child table. This also gives you the option of multiple child records per master/parent. If you want to have only one child per parent, remove the surrogate key in the child table and just have the related master id.

Whatever you choose, I would recommend against storing the child table name in a column in the master record. You can make this a "Type" field, but putting meta data into your data always leads to pain down the road. It's also unnecessary if you have separate keys, as you know if you have a FooID value the record type is Foo.


I would actually verify how it scales up: set up a test environment, expose to to the workload you are expecting, and see what happens. That done, describing your tests would be easy.

If you skip this essential step, your write-up might look good on paper and be completely useless.


Query returns results on pgAdmin running on remote client but not directly through psql on db server

Found the resolution to my issue. To check query results you need to terminate every query in psql with a semicolon ;. If the semicolon is not used you don't see the query result when using the psql command line.

Quote from the manual

  Entering SQL Commands

  ...

  At the prompt, the user can type in SQL commands. Ordinarily, input lines are sent to the server when a command-terminating semicolon is reached. An end of line does not terminate a command. Thus commands can be spread over several lines for clarity.


I don't think that tkprof is the problem. It is a tool that summarizes information from trace file. Tracing is the problem. If you enable tracing of a process, this may slow down the process considerable. Besides that the trace files may fill up your disks. Finally you should be aware  that security relevant information is written to trace files.


Unable to connect to SQL Server 2016 remotely

should i construct the server name as tcp:ipaddress\instance, 1433

Absolutely no.

All that the network library needs to connect to SQL Server instance is it's IP + port.

When you are using default instance, IP address is sufficient as the default port is known, it's 1433, so when you pass IP only, or PC name only, and your instance is listening on 1433 (even if it is named, but still configured to listen 1433), the connection will succeed unless the port is unreachable due to firewall.

When named instance is used, you should pass it's IP address + port or IP address + instance name, but not both port + instance name.

In case you pass in IP + port, the network library just uses them.

In case tou pass IP\instanceName, SQL Server Browser should run. Is's SQL Server Browser that listens on UDP 1434 (that should be open), takes InstanceName as the input and gives out instance's port.

When you pass in both InstanceName and a port, InstanceName is ignored. So not only it serves to nothing, it cheats you in that you may think there is an attempt to resolve InstanceName to the corresponding port, but it's no attempt at all: the port you are passing in is used.

In your particular case if 49689 port is used, all you need to connect to your instance is 192.168.1.182,49689 in SSMS + 49689 shoul be open.

If you are not sure what port is in use, just open SQL Server error log (you can do it using SSMS, notepad or any other text editor, or executing

exec xp_readerrorlog 0,1,N'Server is listening on'

Configuration Manager reflects the last configured value, but if the server was not restarted after configuring, the value for the port is not currently in use while SQL Server error log always gives you effective port values.


Trigger to change Database collation on creation

You would need to use dynamic SQL, and the EVENTDATA() function.

USE master
GO
CREATE TRIGGER trg_DDL_ChangeCOllationDatabase
ON ALL SERVER
FOR CREATE_DATABASE
AS
SET NOCOUNT ON;
DECLARE @databasename NVARCHAR(256) = N''
DECLARE @event_data XML;
DECLARE @sql NVARCHAR(4000) = N''

SET @event_data = EVENTDATA()

SET @databasename = @event_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(256)')

SET @sql += 'ALTER DATABASE ' + QUOTENAME(@databasename) + ' COLLATE al''z a-b-cee''z'

PRINT @sql

EXEC sys.sp_executesql @sql

GO

Just sub in your collation for my fake one.

Now when I create a database...

CREATE DATABASE DingDong

I get this message (from the print):

  ALTER DATABASE [DingDong] COLLATE al'z a-b-cee'z

Just note that if other databases (including tempdb) use different collations, you can run into problems comparing string data. You'd have to add COLLATE clauses to string comparisons where casing or accents matter, and even when they don't you can hit errors. Related question where I ran into a similar code problem here.


SQLIOSIM has identified at least one disk that is failing. You could look in the system event log for errors or warnings that concern the same disk subsystem's IO. The active SQL Server errorlog (C:\Program Files\Microsoft SQL Server\MSSQLx\MSSQL\Log, which is a text file), will also contain the 823 error. The 823 error being raised in the system event log could concern a driver. If SQL Server is failing to start, one of the system databases' disk(s) is likely to have failed.

Failing over SQL Server resets the driver and sets up a new IO path, but failover clustering is designed for high availability, not fault tolerance. Each time an IO failure occurs, a database can become corrupted, which database backups can backup. You should restore your backups on a DR server, run checkdb upon the restored backups. In the meanwhile, ask your system administrator to resolve the disk IO failures. The longer you wait, the higher the risk of corruption being backed up.