But from what I can tell, max_binlog_cache_size is
  184467440737095*47520* and the maximum for a BIGINT is
  184467440737095*51615* so it should fit, right?

The maximum value of BIGINT SIGNED is 9223372036854775807.
The maximum value of BIGINT UNSIGNED is 18446744073709551615.
SIGNED is the default behavior. Hence the ERROR 1264 (22003): Out of range value error.

In the subsequent examples, you are expecting the @input1 variable to act like a BIGINT. But in MySQL, the datatype of a session variable depends on the value that is being assigned at runtime. For example:

rds@int[(none)]> set @i := 9223372036854775807;
Query OK, 0 rows affected (0.00 sec)

rds@int[(none)]> select @i + 0.0;
+-----------------------+
| @i + 0.0              |
+-----------------------+
| 9223372036854775807.0 |
+-----------------------+
1 row in set (0.00 sec)

In the above example, the variable @input1 is acting like a BIGINT.
But if we increase the value of @input1 by 1(**beyond the range of **BIGINT SIGNED****) the behavior changes.

rds@int[(none)]> set @i := 9223372036854775808;
Query OK, 0 rows affected (0.01 sec)

rds@int[(none)]> select @i + 0.0;
+------------------------+
| @i + 0.0               |
+------------------------+
| -9223372036854775808.0 |
+------------------------+
1 row in set (0.00 sec)

I think that in this case the session variable is acting like a FLOAT.

As suggested by Rick James, CAST the variable to the desired datatype to avoid such an unpredictable situation.


Can simultaneous updates and deletes on a set of tables lead to AccessExclusiveLock being taken?

You are quoting from the wrong section of the manual. That passage is from the Table-level Locks section. The lock type you are interested in is specified to be tuple. That means you need to refer to the manual's Row-level Locks section in order to find out when that kind of lock is acquired.

And in the beginning of Row-level Locks it says (original emphasis preserved):

  In addition to table-level locks, there are row-level locks, which can be exclusive or shared locks. An exclusive row-level lock on a specific row is automatically acquired when the row is updated or deleted. The lock is held until the transaction commits or rolls back, just like table-level locks. Row-level locks do not affect data querying; they block only writers to the same row.

Therefore, the lock could be acquired either by Tx 1 or by Tx 2, because one is updating rows and the other is deleting rows.

Moreover, as pointed out by ypercubeᵀᴹ in a comment, your update statements are not restricted by a filter, thus each running on the entire table. When run in parallel with a delete, even a filtered one, such an update can naturally be expected to cause collisions acquiring exclusive locks.


Accessing Memory Optimised Tables Under Snapshot Isolation

If you intend to be able to update/insert through the view, it will likely not work. Please see my post here:
http://nedotter.com/archive/2017/07/dml-for-memory-optimized-tables-in-partitioned-views/
Rows cannot transition to/from the memory-optimzed side by changing the key/partitioning value. Depending on the version of SQL you are running, it will either cause a stack dump or give an error saying it's unsupported.
Though Microsoft has marketed memory-optimized tables as a hot/cold solution, it can't be done in most cases, at least not without tons of manual intervention. There is no way to validate a FK across the traditional/memory-optimized divide. See my post here:
http://nedotter.com/archive/2016/05/in-memory-oltp-relationship-status-its-complicated/
I've written a few posts on memory-optimized tables and isolation levels here:
http://nedotter.com/archive/2017/08/all-about-in-memory-isolation-levels-part-1/
http://nedotter.com/archive/2017/08/all-about-in-memory-isolation-levels-part-2/
Have you proven that the performance issues you are experiencing would be solved by In-Memory OLTP? The use cases are quite narrow.


Let's go through a few points.

1- Why Even Shrink? What Are The Benefits And Cons?

First is the 700GB extra space causing any issues?  If the DB is already at 2.3TBs, it will probably continue to grow, if so, then consider just leaving the space.  In fact, you WANT free space in your DB if it's still growing!  You don't want it to expand often, as that causes physical file fragmentation and causes blocking/performance issues when it's growing unless you have Instant File Init. enabled, then the blocking/locking issues are largely negated.

When you take backups those free 700GB are not actually copied in the backup, just pointers to empty pages which will then be populated on restores, thus doing this will not reduce backup time, restore time, backup space, but will take up restore space.  The only time it'll affect restore time is if you do not have instant file initialization enabled and your SQL Server Service user is not an administrator.

Reindexing again will increase the free space for it to hold temp info and such, but again, if you end up growing and using that free space then there's no problem.
Perhaps if you are restoring to a smaller dev server that doesn't have as much space, then this would be warrented.

2-To Shrink Or Not To Shrink

Now assuming you absolutely need to reduce your DB size, then read Paul Randal's post on moving to a different filegroup instead of shrinking.  This ensures that your shrink does not create additional free space.  If you have a clustered key it also reorders the base clustered index as well.


Does adding non-clustered indexes to Transactional Replication increase latency

@Dina I would still prefer that you write an answer. This was too long to put in comments.

From my experience, I agree with your statement no transaction were sent to subscribers but log reader still have to read every entry in the transaction log. Does not matter if that log entry is being replicated or not. If you are rebuilding large indexes with high number of maxdop option your replication latency can increase very quickly. I use to pause in between index rebuilds for the logreader to catch up so I do not exceed latency SLA.

Below comments are copy pasted from 2 articles I mentioned, which are relevant to your question.

Ref:

Frequently Asked Questions for Replication Administrators

Performance Tuning SQL Server Transactional Replication: A Checklist by Kendra Little

How do I rebuild indexes or tables in replicated databases?

There are a variety of mechanisms for rebuilding indexes. They can all be used with no special considerations for replication, with the following exception: primary keys are required on tables in transactional publications, so you cannot drop and recreate primary keys on these tables.

How do I add or change indexes on publication and subscription databases?

Indexes can be added at the Publisher or Subscribers with no special considerations for replication (be aware that indexes can affect performance). CREATE INDEX and ALTER INDEX are not replicated, so if you add or change an index at, for example, the Publisher, you must make the same addition or change at the Subscriber if you want it reflected there.

Are You replicating non-clustered indexes blindly?

It’s very easy to set up transactional replication and send all the indexes over to the subscriber: you just set “Copy nonclustered indexes” in the articles property to “true”. But you’re only required to replicate the Primary Key and unique indexes. There’s two big problems with replicating all the nonclustered indexes:

It can make re-initialization slower. By default the subscriber will
have objects created, bulk load the articles, create “extra”
nonclustered indexes, then “catch up” on any changes that came in
after the snapshot was pushed. You definitely want to make sure that
all “extra” nonclustered indexes are disabled or don’t exist while
that bulk load is happening. But if a lot may have changed since the
snapshot ran, you may not want the indexes to be created until the
very end, anyway. Handling the nonclustered index creation outside of
replication gives you that flexibility.
It’s very rare for the exact same queries to run on the publisher and
subscriber. You usually want nonclustered indexes that are specific
to the workload on the subscriber, anyway.

Identify the “extra” nonclustered indexes specific to the queries that run on the subscriber. Script them out, check them into your source control, and have a process to deploy them whenever replication needs to be re-initialized.


If you are using psql 9.6+, there is a very convenient command called \gexec:

  Sends the current query input buffer to the server, then treats each
  column of each row of the query's output (if any) as a SQL statement
  to be executed. [...] The generated queries are executed in the order
  in which the rows are returned, and left-to-right within each row if
  there is more than one column. NULL fields are ignored. The generated
  queries are sent literally to the server for processing, so they
  cannot be psql meta-commands nor contain psql variable references.

So, your script could look like:

SELECT format('create database slave%s template master_db', l_num)
  FROM generate_series(1, 30) t(l_num);

\gexec

Note that you can use the newer psql version, even if the server version is lower.  There might be some rare cases of incompatibility (when using \d* commands, because some system views may change between server versions), but I haven't yet seen such a case.

If you are still stuck on an earlier client version, you can just use the SELECT and copy the output manually to a script file.

If your database names are in a file, you have multiple options.  Either treat that file with an editor (something like sed -i -e 's/^/CREATE\ DATABASE\ /' -e 's/$/\ TEMPLATE\ master_db' in any OS where sed exists), and then run the resulting script.

Alternatively, you can also do something like this:

CREATE TEMPORARY TABLE db_names (db text);

\copy db_names FROM 'path/to/your/file'

SELECT format('create database slave%s template master_db', db)
  FROM db_names;

\gexec

Important note: only use \gexec when you absolutely trust the source you are working with.  The above approach is prone to SQL injection (which, interestingly, not so easy when one of the commands on the same line is CREATE DATABASE, but it's always better to be careful than sorry later).


And that is why we never rely on an unspecified date format (or the default in this case).

SQL> create table t(d date);

Table created.

SQL> insert into t values (date'2016-09-29');

1 row created.

SQL> insert into t values (date'2116-08-26');

1 row created.

SQL> select * from t where d BETWEEN TO_DATE('24-AUG-16') AND TO_DATE('30-SEP-16');

D
---------
29-SEP-16

SQL> select * from t order by d;

D
---------
29-SEP-16
26-AUG-16

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> select * from t order by d;

D
-------------------
2016-09-29 00:00:00
2116-08-26 00:00:00

Do not use date format such as DD-MON-RR, as it does not show the century.

Also, do not use TO_DATE without specifying the date format explicitly.

Instead of TO_DATE('24-AUG-16'), use TO_DATE('24-AUG-16', 'DD-MON-RR'), but you still miss the century part. TO_DATE('24-AUG-2016', 'DD-MON-YYYY') is better, but I prefer using the date'2016-08-27 syntax that has a fixed format.


Restrict Postgres roles by IP address outside of the pg_hba.conf file

There is no way to do this with native SQL in PostgreSQL. Also, Postgres manages the privileges and access in a very different way than MySQL.

I've built a VERY BASIC function (needs a lot of tweak and work around, specially handlers when the user already exists) that it adds straightly a user into the pg_hba file, create the user and grant the access to the database.

CREATE OR REPLACE FUNCTION addRemoteUser(
  username text,
  iptarget text DEFAULT '0.0.0.0/0',
  dbtarget text DEFAULT 'all',
  passtext text DEFAULT 'CHANGEME',
  methodauth text DEFAULT 'md5')
  RETURNS boolean
AS $$
  import os
  hbaLine = "host " + str(dbtarget) + "\t" + str(username) + "\t" + str(iptarget) + "\t" + str(methodauth)
  createuser = "CREATE USER " + str(username) + " WITH PASSWORD '" + str(passtext) + "'"
  grantuser = "GRANT connect ON DATABASE " + str(dbtarget) + " TO " + str(username)
  hbaFilePath = plpy.execute("select setting from pg_settings where name = 'hba_file'", 1)
  hs = open(hbaFilePath[0]['setting'],"a")
  hs.write(hbaLine + "\n")
  addUserCommand = plpy.execute(createuser, 1)
  grantUserCommand = plpy.execute(grantuser, 1)
  reload = plpy.execute("select pg_reload_conf()", 1)
  hs.close()
  return True
$$ LANGUAGE plpythonu;

(also posted here on GitHub.)

Obviously is not for production yet, but at least you can have the rough idea how to build the function to do so. It needs PL/Python on the database for this particular example.

I know is not the ideal for this particular case, however I think it's a valid example on how to extend your Postgres installation.

Then, once the function is compiled, you can use SQL to call the function:

SELECT addRemoteUser('mynewuser');


When do we usually map all partitions to the same filegroup?

Partitioning a table with all of the partitions on the same filegroup gives you the following benefits:

Partition elimination in queries
Ability to partition switch within the filegroup
Rowgroups cannot be split across partitions for CCIs which can lead to better rowgroup elimination in queries
Incremental statistics as of SQL Server 2014
Other maintenance operations can be done at the partition level such as rebuilds
Different behavior for parallel scans which can be helpful in some cases.

These benefits are also present when putting partitions on different filegroups, except when noted otherwise.

One reason to put partitions on different filegroups is when you want to put older data on a filegroup that's mapped to a slower tier of storage.


How to change primary key's data type from int to bigint in a huge transactional database under tremendous load?

Assuming your current table is something like

CREATE TABLE YourTable
  (
     Id           INT IDENTITY PRIMARY KEY,
     OtherColumns VARCHAR(10)
  )

And that your existing code that references the table just performs basic DQL and DML commands against the table (i.e. SELECT/UPDATE,MERGE,INSERT,DELETE)

And that YourTable(Id) isn't referenced by a foreign key anywhere.

Then possibly the best way of getting up and running quickly (if you can't afford the downtime of rebuilding the whole table in one go) would be to rename that table (e.g. as YourTableInt) and create a new table

CREATE TABLE YourTableBigInt
  (
     Id           BIGINT IDENTITY(2147483648, 1) PRIMARY KEY,
     OtherColumns VARCHAR(10)
  )

You could then create a view with the same name as your original table name.

CREATE VIEW YourTable
AS
  SELECT Id,
         OtherColumns
  FROM   YourTableInt
  UNION ALL
  SELECT Id,
         OtherColumns
  FROM   YourTableBigInt

You would need to write INSTEAD OF triggers that route the Inserts/Updates/Deletes to the appropriate table. This could initially be based on whether Id was <= 2147483647 or not but that isn't going to work if you try and migrate rows in the background from the legacy table to the new one so probably best to do the following.

Delete trigger
    Apply deletes against both tables by joining on id

Update trigger
    Apply updates to both tables by joining on id

Insert trigger
    Route all inserts into the new "YourTableBigInt" table. It shouldn't be possible for an insert through the view to enter an explicit identity that might clash with anything in the original table as any attempt to set identity_insert YourTable will fail now that is actually a view.

You could then have a background process that deletes batches of rows from YourTableInt and outputs them into YourTableBigInt. Once the original table is empty you can drop it and the view and rename YourTableBigInt to YourTable.


Backing up SQL Server Express Edition on a VPS

Your best bet would be to use ola Hallengren's utilities

https://ola.hallengren.com/

from the faq

How do I get started with the SQL Server Maintenance Solution on SQL Server Express?

SQL Server Express has no SQL Server Agent. Therefore, the execution of the stored procedures must be scheduled by using cmd files and Windows Scheduled Tasks. Follow these steps.

    Download MaintenanceSolution.sql.
    Execute MaintenanceSolution.sql. This script creates the stored procedures that you need.
    Create cmd files to execute the stored procedures; for example:
    sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType = 'FULL'" -b -o C:\Log\DatabaseBackup.txt
    In Windows Scheduled Tasks, create tasks to call the cmd files.
    Schedule the tasks.
    Start the tasks and verify that they are completing successfully.

Another option would be to use the DBATools Powershell module and create and schedule powershell scripts.


mysql off-site replication ( file only )

It might work.  But you probably need to have a separate instance of mysqld running on your machine, using the cloud "drive" as the directory for holding the mysql files.

Assuming you now have two instances of mysqld running on the same machine (your machine), they would need to be segregated -- either by being in different VMs, or by using different "ports".  (3306 is the default port; some use 3307 or 4306.)

Then set up replication.  I would make your mysql on your machine with 3306 be the Master and the "drive" instance be the Slave.

If you try this, please report back on whether it worked or what issues you had.


Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed

This is known as the "double hop" issue between SQL Server and IIS. It looks like you may not have trusted the IIS server for delegation.

Here's a good checklist of things to do when setting up SQL Server with IIS using Kerberos.

Here's a great primer on the condition. The anonymous login is a give-away of the problem.


Design: How to account for a custom product in a transaction item table

Since first starting to type up an anser I have argued myself into, and out of, a set position. I think this question steps over the border from the science of normalisation into the craft of data modelling. The answer will be determined by how you (or, more importantly, your users) understand the semantics of the situation. Is "extra cheese" a new "thing" which is sold, just like "burger" is a thing which is sold, or do they see "extra cheese" as a simple modification of the component list and "burger" is still the product?

I'll take a different business area to show the difference more clearly. If two people each bought a Ford Focus but with different engine, radio, wheels, paint etc., would you think they had bought esentially the same "thing"? Chances are you would. Even though different specifications go into the factory, the item that emerges is thereafter treated as a single unit for sales, transport, handling, support and all other purposes. However, if one customer orders, say, a tow bar that would be a post-factory option and would be handled through different sales, distribution, stock keeping, and maintenance channels, so is likely to be seen by the business as a separate product.

So to the burger. If a burger with extra cheese is still a burger then model it as variations to the composition. If "extra cheese" and "no cheese" are seen by the users as products in their own right model them thus, with their own composition and price, which may be negative.

In term of line items, treating the variations as separate products is the easiest. Once set up in Product and Composition they will flow naturally through to the invoice and stock totals just like the standard products will. You may need to add a product dependency table to ensure the variations make sense ("no cheese" only applies to cheese burgers; can't order "cola with extra cheese").

For the scenario where there is a single product with variaitons I would model it thus:

Customer -< Order -< LineItem >- Product -< Composition >- Component
                        |                                   |
                        +-< ProductCustomization >----------+

ProductCustomiation will have an AddRemove column to say whether its "extra cheese" or "no cheese". It will also have a price variation column which is used with Product.Price when calculating the LineItem.Value.

By making ProductCustomization bridge LineItem and Component I'm allowing for a customer to specify any variation she chooses. If the business only want certain combinations to be available these have to be modelled separately. I specifically do not link variations to Product as her ordering extra cheese does not changes the basic definition of what a burger is, and you do not want to be defining new Products for every possible combination of Components.

The price of a customization can be hard to pin down. Is cheese on a burger the same Component as cheese on a pizza? Is extra cheese on a 10" pizza the same value as on a 20" pizza? Where you store the price of a Component-as-variation will depend on the rules your business uses to determine this price.

Similarly with product/ component dependencies. If you're allowing complete freedom of customization it is easy. Once you start limiting, the combinatorial explosion can get really large quickly.


write table names in a file and use following command to take backups

  for i in `cat table_name_file`
  do
  mysqldump -uroot -p -T $i
  done

Hope it helps


SQL Server Agent - Do not show job step details and column headers in output file

You can do this with SQLCMD

Query

remember to add nocount so it does not add the rows affected to the file

sqlcmd -Q"set nocount on; select * from sys.databases;"  -S localhost -D master -o  "c:\temp\test3.csv"

Your job step needs to be SQLCMD however

The file

Part of the output


Discarding results from CTE based on another CTE

if im not mistaken, you can get the result you want in R3,
simply use

select min(id) as Id,Name,ProductReference
from Products
group by Name,ProductReference

the result will return exactly what you are looking for
in your example, n1 will be grouped to its minimum id, thus the result set will be

  Id,Name,PR,OC
  1,n1,...,... 3,n2,...,... 4,n3,...,...


Key/value table: what is a good way to organize it if value could be foreign key?

If this is not explicitly intended for some very flexible system, you better normalise this into (at least) 3NF tables with columns for each fact type (not data type). In a key/value architecture like this it is very hard to implement constraints (as you write yourself in thought 1)

If you are sure this key/value architecture is what you want (ask yourself twice) then:
Use a separate table for each datatype. A key/value table for string values, a key/value table for integers, a key/reference table for references. At least type checking is possible and storage is efficient. Still no other constraints.
Takes lots of SQL to get all the values for one key, but clean tables.


EXPDP error ORA-39374 + ORA-13158 Oracle object does not exist

It is recommended to exclude statistics when doing an export. It is better to export the stats in a different way (DBMS_STATS) or re-gather once the data is imported. I know it does not solve the root cause, but Oracle actually recommends that you do not export the statistics.

Try to do the export again with LOGTIME=ALL and METRICS=Y to get a better idea of what it is spending time on. Otherwise, you might have to turn on 10046 tracing to see what query it is stuck in.

Do you have many objects (10.000+) to export or many partitions?


How do you remove reported non existing tables from mysql?

If using MyISAM, you should be able to get rid of the tables by deleting the .frm files from the data folder while the database is down.

Remember to backup everything you can before doing anything like that!


Using PostgreSQL functions instead of joining?

A function is never going to speed up what SQL already does. In fact, if you don't actually add IMMUTABLE to it, it's likely to slow it down.

I would personally never write such a trite function.

If you're returning one row, I would use a correlated subquery.
If you're needing multiple users, I would do the join.
I would look probably retrieve this kind of information upon auth, like in the initial response, and send it back to the client to handle.

But above and beyond all of that, it's not merely about complexity for your function. You have a DSL that explicitly queries your schema and was created for just that purpose -- namely, SQL. What are you going to do when those functions need more logic and complexity. Been there seen that, the next step is CASE statements in functions. And, then slowly but surely you recreate a dynamic query generation mechanism. It's easier to just dynamically generate the SQL when needed.


Trigger function using current row and current table name as variables (final part)

Yes, this should work (untested):
CREATE OR REPLACE FUNCTION trfn_tbl_log_timetypespan()  -- generic name
  RETURNS trigger AS
$func$
DECLARE
   _timetype varchar;
   _timetypespan_resume interval;
   _ct int;
BEGIN

CASE NEW.timetype
WHEN 'lap' THEN
   EXECUTE format($$
      SELECT timetype, timetypespan, age($1, timestmp)
      FROM   %s
      WHERE  fnname = $2
      AND    timetype IN ('start', 'resume')
      ORDER  BY stmtserial DESC
      LIMIT  1$$
    , TG_RELID::regclass)
   USING NEW.timestmp, NEW.fnname
   INTO  _timetype, _timetypespan_resume, NEW.timetypespan;

   CASE _timetype
   WHEN 'start' THEN  -- do nothing

   WHEN 'resume' THEN
      EXECUTE format($$
         SELECT age($1, timestmp) - _timetypespan_resume
         FROM   %s
         WHERE  fnname = $2
         AND    timetype = 'start'
         ORDER  BY stmtserial DESC
         LIMIT  1$$
       , TG_RELID::regclass)
      USING NEW.timestmp, NEW.fnname
      INTO  NEW.timetypespan;

   ELSE
      RAISE EXCEPTION 'There is no previous row.';
   END CASE;

WHEN 'resume' THEN
   EXECUTE format($$
      SELECT age($1, timestmp)
      FROM   %s
      WHERE  fnname = $2
      AND    timetype IN ('start', 'lap')
      ORDER  BY stmtserial DESC LIMIT 1$$
    , TG_RELID::regclass)
   USING NEW.timestmp, NEW.fnname
   INTO  NEW.timetypespan;

   GET DIAGNOSTICS _ct = ROW_COUNT;

   IF _ct > 0 THEN  -- do nothing
   ELSE
      RAISE EXCEPTION 'There is no previous row.';
   END IF;

WHEN 'total' THEN
   EXECUTE format($$
      SELECT COALESCE(SUM(timetypespan), $1)
      FROM (
         SELECT floor(timeidx)::int, max(timetypespan) AS timetypespan
         FROM   %1$s
         WHERE  fnname = $2
         AND    timetype = 'lap'
         AND    stmtserial > coalesce(
                 (SELECT stmtserial
                  FROM   %1$s
                  WHERE  fnname = $2
                  AND    timetype = 'total'
                  ORDER  BY stmtserial DESC
                  LIMIT  1), 0)
         GROUP  BY 1
         ) sub$$
    , TG_RELID::regclass)
   USING NEW.timetypespan, NEW.fnname
   INTO  NEW.timetypespan;

   GET DIAGNOSTICS _ct = ROW_COUNT;

   IF _ct > 0 THEN  -- do nothing
   ELSE
      RAISE EXCEPTION 'There is no previous row.';
   END IF;
END CASE;

RETURN NEW;

END
$func$  LANGUAGE plpgsql;

This is a follow-up to my previous answer to your previous question. Find explanation there:

Trigger function using current row and current table name as variables

While being at it, I simplified a couple of things. Like: the second SELECT in your first ELSIF branch was just a repetition of the SELECT one level above. I merged that for no cost.
I also removed a couple of unnecessary intermediary steps and assigned to fields of NEW directly where applicable. That's why I could remove most of your variables.
Aside: If timeidx only has positive numbers you can use the cheaper trunc(timeidx) instead of floor(timeidx).
To understand the dynamic part more easily ...
If you would implement the same just for tbl_log_a - i.e., the effectively executed code for tbl_log_a (after applying format() and EXECUTE) looks like this:
...

CASE NEW.timetype
WHEN 'lap' THEN
   SELECT timetype, timetypespan, age(NEW.timestmp, timestmp)
   FROM   tbl_log_a
   WHERE  fnname = NEW.fnname
   AND    timetype IN ('start', 'resume')
   ORDER  BY stmtserial DESC
   LIMIT  1
   INTO  _timetype, _timetypespan_resume, NEW.timetypespan;

   CASE _timetype
   WHEN 'start' THEN  -- do nothing

   WHEN 'resume' THEN
      SELECT age(NEW.timestmp, timestmp) - _timetypespan_resume
      FROM   tbl_log_a
      WHERE  fnname = NEW.fnname
      AND    timetype = 'start'
      ORDER  BY stmtserial DESC
      LIMIT  1
      INTO  NEW.timetypespan;

...


64bit SQL Server with total virtual address space of 4GB. How could this be?

The reason is because it isn't a 64-bit installation.

  Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (Intel X86)

Intel X86 is 32-bit. X64 is 64-bit.


Isolation and atomiticy of SQL Server UPDATE

The rows that qualify for an update are always stabilized during discovery by using (at least) U locks (Update locks, see Lock Compatibility). When is time to actually update them, the U lock is upgraded to X lock. Because of this stabilization the row cannot disappear nor can it be modified between the discovery and the update. Nor can a second UPDATE statement discover the same row: a concurrent UPDATE will have to wait for first to commit even if it only needs to 'check' the row and not actually update it. So Batch A is 100% bulletproof. For Batch B, most times the engine will understand what you mean and scan the join with update semantics (your IN clause will be rewritten as a JOIN during query normalization).

There are also differences in behavior depending on whether the plan requires Halloween Protection or not, but that only means the U locks is longer lived or not.

As a general comment the queries you show are the hallmark queries of using table row state where you should be using queues. The concurrency of SET InProgress = 1 WHERE Completed = 0 is basically 1, because it will always require a scan. An equivalent solution using queues for events to signal requests to transition state will scale basically X CPUs.


Need to create batches of 2000 records within a subset of records


The Merge Agent failed because the schema of the article at the Publisher does not match the schema of the article at the Subscriber.

I have run into this in the past.  In my experience typically this will resolve it's self by just restaring the sync process.  From the client in a pull situation, or restart from the publisher in a push situation.

In short the application of DDL changes ran into a conflict but restarting the sync will usually resolve the issue.  And no, it doesn't start from the beginning, it should just pick up from the point of failure.

It also wouldn't hurt to make sure that your snapshot is up to date and current.


Should you design the database before the application code is written?

In addition to other answers...

Capturing your conceptual model first should define scope and requirements. From this, you can derive your logical and physical data models.

Once this is mostly static, then you have a stable database to build your application against. This is contrary to your first option.

Your second point will end in a messy, unmaintainable ball of mud. The data model will never be fixed: if you didn't design it up front, you won't have time to fix it before shipping. You'll be too busy hacking things together.

Minor changes to the schema, combining or splitting tables, changing relationships, etc. will happen, but in localised "islands," and your model + basic design will be unchanged.


Creating 8M new records should take seconds, not hours.  You just need to do Bulk Loading instead of slow-by-slow loading.  (calling COMMIT after every row would be a "bad idea" as it will limit you to 1row/5ms).

Same concept goes for UPDATE of the data.  (Although. I would expect minutes).  Bulk operations is key.

From my experience,  your main concern will be Disk I/O performance.  You improve this by using RAID systems.  You'll probably have an extra terabyte or 2, but your performance,  and redundancy, should be greatly improved over current setup.

Which RDBMS?

I would go with Oracle.  But, that is because local resources (me) are experts in that technology.  (It also comes with a cool web based application development frame work).

Actually, I recommend you try out each of them with your actual data and pick the one that fits your company the best.


How to make a query using "where MONTH(datetime)" faster?

Using the MONTH() function on a column automatically disqualifies any index from usage
You can state the range in the WHERE clause
SELECT s.id, s.player,
COUNT(case when dg.winner = 1 AND dp.colour <= 5 then 1 when dg.winner = 2 AND dp.colour > 5 then 1 else null end) as totalwin,
COUNT(case when dg.winner = 2 AND dp.colour <= 5 then 1 when dg.winner = 1 AND dp.colour > 5 then 1 else null end) as totallose,
COUNT(dg.winner) as totalgames
       FROM dotaplayers AS dp
       LEFT JOIN gameplayers AS gp ON gp.gameid = dp.gameid and dp.colour = gp.colour
       LEFT JOIN stats AS s ON s.player_lower = gp.name
       LEFT JOIN dotagames AS dg ON dg.gameid = dp.gameid
       LEFT JOIN games AS g ON g.id = dp.gameid
       LEFT JOIN bans as b ON b.name=gp.name
       WHERE g.datetime >= '2015-04-01 00:00:00'
       AND   g.datetime <  '2015-05-01 00:00:00'
       GROUP by gp.name
       ORDER BY totalwin DESC LIMIT 0,10

or move  the range from the WHERE to the LEFT JOIN
SELECT s.id, s.player,
COUNT(case when dg.winner = 1 AND dp.colour <= 5 then 1 when dg.winner = 2 AND dp.colour > 5 then 1 else null end) as totalwin,
COUNT(case when dg.winner = 2 AND dp.colour <= 5 then 1 when dg.winner = 1 AND dp.colour > 5 then 1 else null end) as totallose,
COUNT(dg.winner) as totalgames
       FROM dotaplayers AS dp
       LEFT JOIN gameplayers AS gp ON gp.gameid = dp.gameid and dp.colour = gp.colour
       LEFT JOIN stats AS s ON s.player_lower = gp.name
       LEFT JOIN dotagames AS dg ON dg.gameid = dp.gameid
       LEFT JOIN games AS g ON g.id = dp.gameid AND g.datetime>='2015-04-01 00:00:00' AND g.datetime<'2015-05-01 00:00:00'
       LEFT JOIN bans as b ON b.name=gp.name
       GROUP by gp.name
       ORDER BY totalwin DESC LIMIT 0,10

GIVE IT A TRY !!!
UPDATE 2015-07-08 15:19 EST
Try using INNER JOIN on games since you need a specific month
SELECT s.id, s.player,
COUNT(case when dg.winner = 1 AND dp.colour <= 5 then 1 when dg.winner = 2 AND dp.colour > 5 then 1 else null end) as totalwin,
COUNT(case when dg.winner = 2 AND dp.colour <= 5 then 1 when dg.winner = 1 AND dp.colour > 5 then 1 else null end) as totallose,
COUNT(dg.winner) as totalgames
       FROM dotaplayers AS dp
       LEFT JOIN gameplayers AS gp ON gp.gameid = dp.gameid and dp.colour = gp.colour
       LEFT JOIN stats AS s ON s.player_lower = gp.name
       LEFT JOIN dotagames AS dg ON dg.gameid = dp.gameid
       INNER JOIN games AS g ON g.id = dp.gameid
       LEFT JOIN bans as b ON b.name=gp.name
       WHERE g.datetime >= '2015-04-01 00:00:00'
       AND   g.datetime <  '2015-05-01 00:00:00'
       GROUP by gp.name
       ORDER BY totalwin DESC LIMIT 0,10


Joining tables with all rows for each unique column value

You can use a recursive CTE to produce the complete list of days for each locationid then join it back to table_b to get your values

Setup the tables:

CREATE TABLE dbo.table_a (
    ty_date DATE NULL,
    ly_date DATE NULL
);

CREATE TABLE dbo.table_b (
    locationid INT NULL,
    ty_date DATE NULL,
    value INT NULL
);

Add 365 entries to table_a, one for each day:

;WITH Dates AS (
    SELECT 1 AS [RowID], CAST('2019-01-01' AS DATETIME) AS ty_date
    UNION ALL
    SELECT r1.RowID + 1 AS RowID, DATEADD(DAY, 1, r1.ty_date) AS ty_date
    FROM Dates r1
    WHERE r1.RowID <= 364
)

INSERT INTO dbo.table_a(ty_date)
SELECT ty_date
FROM Dates
OPTION (MAXRECURSION 400)

Add entries in table_b for 3 unique locationid's. One entry for each day until Aug:

;WITH Dates AS (
    SELECT 1 AS [RowID], CAST('2019-01-01' AS DATETIME) AS ty_date
    UNION ALL
    SELECT r1.RowID + 1 AS RowID, DATEADD(DAY, 1, r1.ty_date) AS ty_date
    FROM Dates r1
    WHERE r1.RowID <= 242
)

INSERT INTO dbo.table_b(locationid, ty_date, value)
SELECT 1,
    ty_date,
    RowID
FROM Dates
UNION
SELECT 2,
    ty_date,
    RowID
FROM Dates
UNION
SELECT 3,
    ty_date,
    RowID
FROM Dates
OPTION (MAXRECURSION 400)

Use the below query to return the full 365 days, one row per day per locationid. The Value column is only filled for values that exist in table_b, otherwise they are NULL

DECLARE @MinDate DATE,
    @MaxDate DATE

SELECT @MinDate = MIN(ty_date), @MaxDate = DATEADD(DAY, -1, MAX(ty_date)) FROM table_a

;WITH AllData AS (
    SELECT DISTINCT 1 AS RowID, locationid, @MinDate AS ty_date
    FROM table_b
    UNION ALL
    SELECT r1.RowID + 1 AS RowID, r1.locationid, DATEADD(DAY, 1, r1.ty_date) AS ty_date
    FROM AllData r1
    WHERE r1.ty_date BETWEEN @MinDate AND @MaxDate
)

SELECT ad.ty_date, ad.locationid, b.value
FROM AllData ad
LEFT JOIN table_b b ON b.locationid = ad.locationid AND b.ty_date = ad.ty_date
ORDER BY ad.ty_date, ad.locationid
OPTION (MAXRECURSION 1100)

Note that MAXRECURSION must be equal to or higher than 365 * the number of distinct locationid values in table_b minus 1

The results:

ty_date     locationid  value
-----------------------------
2019-01-01  1           1
2019-01-01  2           1
2019-01-01  3           1
2019-01-02  1           2
2019-01-02  2           2
2019-01-02  3           2
2019-01-03  1           3
2019-01-03  2           3
2019-01-03  3           3
2019-01-04  1           4
2019-01-04  2           4
2019-01-04  3           4
.....
2019-08-31  1           243
2019-08-31  2           243
2019-08-31  3           243
2019-09-01  1           NULL
2019-09-01  2           NULL
2019-09-01  3           NULL
2019-09-02  1           NULL
2019-09-02  2           NULL
2019-09-02  3           NULL
2019-09-03  1           NULL
2019-09-03  2           NULL
2019-09-03  3           NULL
.....
2019-12-28  1           NULL
2019-12-28  2           NULL
2019-12-28  3           NULL
2019-12-29  1           NULL
2019-12-29  2           NULL
2019-12-29  3           NULL
2019-12-30  1           NULL
2019-12-30  2           NULL
2019-12-30  3           NULL
2019-12-31  1           NULL
2019-12-31  2           NULL
2019-12-31  3           NULL

See here for a db<>fiddle demonstrating this.


Why are SELECT queries in sleeping status holding open transactions

what about you start using this query below, and then tailor it for your own purposes:

select *
from sys.dm_tran_database_transactions t

join sys.dm_tran_session_transactions st on t.transaction_id = st.transaction_id

join sys.dm_exec_sessions s on s.session_id = st.session_id

join sys.dm_exec_connections c on c.session_id = s.session_id

cross apply sys.dm_exec_sql_text(most_recent_sql_handle)
where 1=1

and text <> 'begin tran'

and text not like '%sp_MS%'

and datediff(minute, last_request_end_time, getdate()) > 60

and database_transaction_begin_time is not null

I use this one below, it helps in most cases:

    SELECT
     es.session_id AS session_id

    ,CASE WHEN eS.LOGIN_NAME = eS.ORIGINAL_LOGIN_NAME
     THEN eS.LOGIN_NAME
     ELSE eS.LOGIN_NAME + ' (' + eS.ORIGINAL_LOGIN_NAME + ')'
     END AS LOGIN_NAME

    ,es.host_name AS hostname
    ,es.status
    ,es.last_request_start_time
    ,es.last_request_end_time
    ,es.CPU_TIME AS CPU_TIME_MS
    ,es.MEMORY_USAGE AS MEMORY_USAGE_PAGES
    ,es.ROW_COUNT
    ,NULL AS blocked_by
    ,NULL AS waittype
    ,NULL AS waittime
    ,NULL AS lastwaittype
    ,NULL AS waitresource
    ,dbid=trans.database_name
    ,NULL as  cmd
    ,t.text AS [QUERY]
    ,NULL AS PARENT_QUERY

   ,TRANSACTION_ISOLATION_LEVEL = NULL

    ,NULL as cpu

    ,NULL AS physical_io

    --,es.open_transaction_count as [open_tran]
    ,es.program_name

    --,es.login_time
    --,ota.task_state
    --,ota.pending_io_count
    --,ota.pending_io_byte_count

    FROM sys.dm_exec_sessions AS es
    INNER JOIN sys.dm_exec_connections AS c
        ON es.session_id = c.session_id

    --LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id

    CROSS APPLY (

        SELECT MAX(DB_NAME(dt.database_id)) AS database_name
        FROM sys.dm_tran_session_transactions AS st
        INNER JOIN sys.dm_tran_database_transactions AS dt
            ON st.transaction_id = dt.transaction_id
        WHERE is_user_transaction = 1
        GROUP BY st.session_id
        HAVING es.session_id = st.session_id

    ) AS trans

    CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t
    WHERE es.session_id NOT IN (

            SELECT session_id
            FROM sys.dm_exec_requests

        )
        AND es.session_id IN (

            SELECT request_session_id
            FROM sys.dm_tran_locks
            WHERE request_status = 'GRANT'
        )
        AND STATUS = 'sleeping'
        AND is_user_process = 1
        AND es.session_id <> @@spid

  Please avoid using SYS.SYSPROCESSES


What should be the command for following role creation requirement?

I am not sure of any other alternative but i believe user needs to be there in MSDB or should have the admin rights to execute or perform any required action on the job. There is a connect item about this as well, SQL2005 SQLAgent MSDB security roles

Or you can use an option as mentioned here:

  You could create a stored procedure that runs the job. You can use
  with execute as owner to run the stored procedure as the database
  owner. That way, the users themselves don't need permissons on
  sp_start_job

Also, if feasible, you can read Launching SQL Agent Job from stored procedure not on [msdb] database, or, How Necessary is SET TRUSTWORTHY ON?


How to model a three-way association that involves Product, Category and Label?

Note: This answer presents an approach to cover specifically the business rules derived by way of the earliest series of comment and chat interactions (most of which can be seen in this question revision). Nevertheless, after more in-depth clarifications and deliberations took place, @yemet indicated that the business context may require a somewhat distinct method, due to the successive identification of dfferent business rules.

The fact that you have determined that there exists a three-way association (also known as ternary or diamond shaped relationship) that involves the entity types (tables once implemented) of interest shows that you are heading in the right direction.
Business rules
The objective should be to handle the three distinct relationships at hand separately, starting with a logical level analysis before considering the implementation aspects in full. In this regard, it is quite helpful to write down some formulations that describe the relevant business rules, e.g.:
First, for the following many-to-many (M:N) relationship:

A product is classified by one-to-many categories
A category classifies zero-one-or-many products

Which implies the existence of an associative entity type that I am going to call product_category.
Second, for a distinct M:N relationship:

A category is integrated by zero-one-or-many labels
A label integrates zero-one-or-many categories

Situation that suggests that there is another associative entity type, in this case the one  that I will name category_label.
Then, it is time to manage another M:N relationship, this time between the two associative entity types discussed above:

A product_category may receive zero-one-or-many label_assignments
A category_label may take part in zero-one-or-many label_assignments

As noted, I have included a new entity type that I denominated label_assignment but, naturally, you may name it using a term that is more meaningful with respect to your business domain.
I have assumed, based on the structure of the categories table contained in your question (specifically the column categories.category_parent_id), that there is a self-recursive one-to-many (1:M) relationship concerning the entity type called category. Later, you confirmed such situation, so the following rule applies as well:

A category comprises zero-one-or-many categories

Logical Model
Then I have derived an IDEF1X1 logical model from the business rules formulations presented above, that is shown in Figure 1:

With this arrangement you can solve much of your needs, since:

Each product has to first be related to a certain category before it receives a label assignment.
A label cannot be assigned to a particular product if it has not been connected to a certain category previously.
A product can be related to a certain category without having to be involved in a relationship with a label.

Expository DDL structure
Consequently, I coded the following DDL structure (tested on SQL Fiddle):
-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.

-- As one would expect, you are free to make use of
-- your preferred (or required) naming conventions.

CREATE TABLE product
(
     product_id       INT      NOT NULL,
     product_code     CHAR(30) NOT NULL,
     name             CHAR(30) NOT NULL,
     description      CHAR(90) NOT NULL,
     created_datetime DATETIME NOT NULL,
     CONSTRAINT PK_product             PRIMARY KEY (product_id),
     CONSTRAINT AK_product_code        UNIQUE      (product_code), -- (Possible?) ALTERNATE KEY.
     CONSTRAINT AK_product_name        UNIQUE      (name),         -- ALTERNATE KEY.
     CONSTRAINT AK_product_description UNIQUE      (description)   -- ALTERNATE KEY.
);

CREATE TABLE category
(
    category_number         INT      NOT NULL,
    parent_category_number  INT      NULL, -- Set up as ‘NULLable’, in order to focus on the main aspects of the approach exposed.
    name                    CHAR(30) NOT NULL,
    description             CHAR(90) NOT NULL,
    created_datetime        DATETIME NOT NULL,
    CONSTRAINT PK_category                         PRIMARY KEY (category_number),
    CONSTRAINT AK_category_name                    UNIQUE      (name),        -- ALTERNATE KEY.
    CONSTRAINT AK_category_description             UNIQUE      (description), -- ALTERNATE KEY.
    CONSTRAINT FK_FROM_category_TO_parent_category FOREIGN KEY (parent_category_number)
        REFERENCES category  (category_number)
);

CREATE TABLE label
(
    label_number     INT      NOT NULL,
    name             CHAR(30) NOT NULL,
    description      CHAR(90) NOT NULL,
    created_datetime DATETIME NOT NULL,
    CONSTRAINT PK_label             PRIMARY KEY (label_number),
    CONSTRAINT AK_label_name        UNIQUE      (name),       -- ALTERNATE KEY.
    CONSTRAINT AK_label_description UNIQUE      (description) -- ALTERNATE KEY.
);

CREATE TABLE product_category -- Associative table.
(
    product_id          INT      NOT NULL,
    category_number     INT      NOT NULL,
    classified_datetime DATETIME NOT NULL,
    CONSTRAINT PK_product_category                  PRIMARY KEY (product_id, category_number),
    CONSTRAINT FK_FROM_product_category_TO_product  FOREIGN KEY (product_id)
        REFERENCES product  (product_id),
    CONSTRAINT FK_FROM_product_category_TO_category FOREIGN KEY (category_number)
        REFERENCES category (category_number)
);

CREATE TABLE category_label -- Associative table.
(
    category_number     INT      NOT NULL,
    label_number        INT      NOT NULL,
    integrated_datetime DATETIME NOT NULL,
    CONSTRAINT PK_category_label                  PRIMARY KEY (category_number, label_number),
    CONSTRAINT FK_FROM_category_label_TO_category FOREIGN KEY (category_number)
        REFERENCES category (category_number),
    CONSTRAINT FK_FROM_category_label_TO_label    FOREIGN KEY (label_number)
        REFERENCES label    (label_number)
);

CREATE TABLE label_assignment -- Associative table that ‘concretizes’ a relationship between two distinct relationships.
(
    product_id        INT       NOT NULL,
    category_number   INT       NOT NULL,
    label_number      INT       NOT NULL,
    assigned_datetime DATETIME  NOT NULL,
    CONSTRAINT PK_label_assignment                          PRIMARY KEY (product_id, category_number, label_number), -- Composite PRIMARY KEY.
    CONSTRAINT FK_FROM_label_assignment_TO_product_category FOREIGN KEY (product_id, category_number)   -- Composite FOREIGN KEY.
        REFERENCES product_category (product_id, category_number),
    CONSTRAINT FK_FROM_label_assignment_TO_category_label   FOREIGN KEY (category_number, label_number) -- Composite FOREIGN KEY.
        REFERENCES category_label   (category_number, label_number)
 );

Pay especial attention to the two composite FOREIGN KEY definitions of the label_assignment table, because the category_number attribute is included in both of them.
You brought up a requirement that stipulates that:

A product has to be assigned to category in order to be visible in the shop.

Therefore, you should guarantee that each time that you INSERT a product row you as well link it with a certain category by means of the INSERTion of a row in the associative table here called product_category. In this way, both operations should be executed within the same ACID TRANSACTION, so that they either succeed or fail as a single unit.
Similar scenarios
You might find of help my answer to

Best data modelling approach to handle redundant foreign keys in relational model,

and also the @Ypercubeᵀᴹ answer to

Many to Many and Weak Entities.

Endnote
1. Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) theoretical work authored by the originator of the Relational Model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown. It is worth noting that IDEF1X was formalized by way of first-order logic.


Configuring SSIS,SSRS, and SQL Server - three server model

What you are asking about is broad architecture rather than specific issues. I dont believe there will be a single document that gives you a step by step guide.

If your vendor is making this reccomendation you could ask them to explain thier design with at least some basic documentation and diagrams that help you understand the layout.

If you are unfamiliar with either of these products you have a big learning curve ahead of you. MSDN is probably the best place to start. If you have specific issues then you should google those.

Assuming your main database is already running, i would start by installing an instance for SSRS. Then work through designing and deploying a report that connects to your source db.

Then repeat for SSIS. Set up the server and design and deploy a basic package.

A key part of learning about both SSRS & SSIS is learning about datasources,  connection managers and connection strings.

If you are unfamiliar with these concepts and your company is expecting you to install configure and develop with these products they might be expecting too much of you and I suggest that you ask for assistance.


Database structure for a bilingual dictionary

In a document store:

{
    {
        "language": "English",
        "word": "cat"
    },
    {
        "language": "Spanish",
        "word": "gato"
    }
}

Of course you'll have to choose a working language for the keys.

In a graph DB, add each word from each language as a node. Label the nodes with the language. Alternatively have a separate node for the language itself and connect the word-node to the language-node. Then connect word-nodes in one language to the corresponding word-node in another language. Note that English has a lot of words imported from other languages so decide whether you have one word-node connected to several languages or separate word-nodes.

In a relational database I would have a table for the languages, one for word lists and a third for equivalences

table: Languages
    Language_name   primary key

table: Words
    language_name
    word

Both columns together make a composite primary key

table: Transaltions
    first_language_name
    first_word
    second_language_name
    second_word

The primary key is all four columns. During lookup should you compare the given word to first_word or second_word? Either maintain a conventions and stick with it (Spanish = first_, English = second_) or query both and deal with the few (but non-zero) collisions).

Some concepts do not translate word-for-word. This could be handled by renaming column "word" to "idea" or "phrase".

For multi-lingual (rather than just bilingual) lists having an over-arching concept which has word-forms in each language would be one way to go:

Table: Concepts
    concept_id

Table: Translations
    concept_id
    language_name
    word

To go from "cat" (English), find the corresponding row in Translations. From that find the concept_id. Then read the concept_id, language_name row in Spanish. Homonyms make this difficult, however.


Threshold for sort spill and random cardinality estimates

The amount of memory needed to perform a sort is not as simple as computing the raw size of the input data. The main sorting algorithm used by SQL Server is a variation on merge sort, which includes extra steps like key normalization to ensure all combinations of data column types can be sorted efficiently. Due to these extra steps, it is not easy to predict the amount of memory that would be needed to avoid a spill at runtime; the memory grant is an estimate. Spilling is part of the design. You should not be overly concerned by small spills in edge cases like this.
SQL Server 2014 includes a new cardinality estimation module, which is used if the context database is compatibility level 120. You use tempdb for your test, which will be 120 level by default. You can get the pre-2014 CE behaviour by changing the compatibility level, or using trace flag 9481. Regarding the different number of estimated rows, the statistics may be sampled unless you use the WITH FULLSCAN option with UPDATE STATISTICS.


Pre-existing shared memory block (key 5432001, ID 32768) is still in use

It's vital that if you forcibly terminate PostgreSQL then all postgres processes must be killed. If you don't terminate them all then the shared memory block may still be held and the database files may still be open and being written to. In this case starting PostgreSQL again may cause massive database corruption, which is why PostgreSQL tries to stop you doing this. Overriding those safety measures may cause data loss.

You should preferably cleanly stop PostgreSQL with pg_ctl as part of the container shutdown process.

In any case, it's very important that stopping the container terminates all postgres processes.

You should not delete postmaster.pid, it's protecting you from data corruption by allowing PostgreSQL to notice that the shared memory segment is still in use. Unless Docker somehow gets in the way of this process.


Adding two more columns to existing script (backup summary)

You should be able to tweak this for your needs

SELECT bs.database_name AS DbName
    ,CAST(bs.backup_size / 1024.0 / 1024 / 1024 AS DECIMAL(10, 2)) AS BackupSize_GB
    ,bs.backup_start_date AS Backup_StartDate
    ,bs.backup_finish_date AS Backup_EndDate
    ,bs.NAME AS BackupName
    ,bmf.physical_device_name AS DeviceName
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = 'DB_Name'
  AND  bs.NAME = 'BackupSetName'
ORDER BY bs.backup_start_date DESC


Insert Into with Select works in once instance, but not the other. Subquery error or incorrect data

I'll start after you has generated #SurveyTemp table.

I'm not sure to understand your expected result, take a look at your table ordered by demo:

SELECT   *
FROM     @table_survey
WHERE    demo IN ('A18+','A25+')
ORDER BY com, survey_id, demo, wk;

com | survey_id | demo | wk | time | audience |
--: | :-------- | :--- | -: | ---: | :------- |
  1 | LO2017    | A18+ |  1 |  300 | 4.7      |
  1 | LO2017    | A18+ |  2 |  100 | 3.7      |
  1 | LO2017    | A25+ |  1 |  300 | 2.3      |
  1 | LO2017    | A25+ |  2 |  100 | 4.3      |

There are 2 records for each demo. If you want to add new record as a result of some operation between these two records, you should decide if you want to group by wk and time (that I think it's the correct way), or not.

In your insert command:

insert into #SurveyTemp (Com, Survey, Demo, Wk, TimeBlock, Aud, AudOne, AudTwo)
(select   Com, Survey, 'A1824', Wk, Time, 0.0,
          (select sum(aud) from table_survey where demo = 'A18+'),
          (select sum(aud) from table_survey where demo = 'A25+')
 from     table_survey
 where    surveyid = 'LO2017'
 group by Com, survey, Wk, Time,
)

You're grouping by Com, Survey, Wk, Time, but you're calculating overall audience of all demo records, (without considering wk and time):

(select sum(aud) from table_survey where demo = 'A18+')
(select sum(aud) from table_survey where demo = 'A25+')

IMHO, at least wk should be added to the new records, and you should decide what to do with time.

My proposed solution: GROUP BY WK AND TIME

SELECT   Com, survey_id, 'A1824', wk, time,
         (SELECT sum(audience) FROM @table_survey
          WHERE demo = 'A18+' AND wk = t1.wk AND time = t1.time)
          -
         (SELECT sum(audience) FROM @table_survey
          WHERE demo = 'A25+' AND wk = t1.wk AND time = t1.time) AS audience
FROM     @table_survey t1
WHERE    survey_id = 'LO2017'
GROUP BY Com, survey_id, Wk, time;

NOTE: I've added wk and time to calculate sum(audience) for every demo.

Com | survey_id | (No column name) | wk | time | audience
--: | :-------- | :--------------- | -: | ---: | :-------
  1 | LO2017    | A1824            |  1 |  300 | 2.4
  1 | LO2017    | A1824            |  2 |  100 | -0.6

As you can see, first record match your expected result.

Without group by wk and time

SELECT   Com, survey_id, 'A1824', 0 AS wk,
         avg(time) as time,
         (SELECT sum(audience) FROM @table_survey WHERE demo = 'A18+')
          -
         (SELECT sum(audience) FROM @table_survey WHERE demo = 'A25+') AS audience
FROM     @table_survey t1
WHERE    survey_id = 'LO2017'
GROUP BY Com, survey_id;

This is the result:

Com | survey_id | (No column name) | wk | time | audience
--: | :-------- | :--------------- | -: | ---: | :-------
  1 | LO2017    | A1824            |  0 |  200 | 1.8

NOTE: You don't need to INSERT & UPDATE, it can be done on a single INSERT operation.

Check it: dbfiddle here


Product certifications typically include version numbers up to the patchset version, the 4th number (10.2.0.5), which will not change by installing those patches.

I applied those patches last year at a customer and we had no issues at all, although they were using a custom application, not Peoplesoft.


The error appears due to a corrupted configuration file or some dummy MySQL services (Leftovers from server installations not completely removed, this is due to the auto-detect mysql services functionality). Please make sure you're running the latest Notifier version available (that can be downloaded at mysql downloads page).

How to solve the corrupted file configuration:

See the answer from Colin Pickard.

How to solve the dummy MySQL services leftovers:

If the problem persists, open a Run command window and type the following:

  services.msc

Take note of any MySQL service that shouldn't be listed (attempted uninstall before).
Open an Admin rights command window and type the command:

  sc delete "< Dummy_service_name_here >".

Restart Notifier and see if that corrected the problem.

If a similar error still appears afterwards, check if the stack trace shows the word CreateScheduledTask somewhere, that would mean something is wrong when Notifier tries to add a schedule task at startup to check for updates of any installed MySQL product.

A workaround is to open in notepad the settings.config file that was re-generated. Inside the file, look for a property called "AutoCheckForUpdates" and change the default value of "True" for "False" and run Notifier again, as noted in this post.


What circumstances could a SQL DateTime column hold an invalid date?

SQL Server will not allow an invalid date. Internally, the date part of a DateTime column is just a number/four bytes that represents dates between January 1, 1753, through December 31, 9999.  SQL Server does not know anything about and will not use invalid dates. An attempt to save an invalid date will result in an error.

If you want to confirm this, write some code that attempts to store all possible number combinations for the digit groups in a column.

It is possible, however, to inadvertently store the wrong date, due to SQL Server DateTime supports multiple formats, however that should be handled by your business logic layer. For example, someone may enter 07/06/2018, intending the date to be June 7. In most cases this can be avoided by using ISO 8601 DateTime format when a DateTime needs to be represented as a string.

https://en.wikipedia.org/wiki/ISO_8601


Automatically convert views to underlying query

I don't think there is a way to get the "merge", unless it is in the JSON; see below.

Meanwhile, run EXPLAIN SELECT ... and EXPLAIN FORMAT=JSON SELECT ... to see what it says.  I think it will, with dubious clarity, tell you whether it is doing the "merge" or "materialization".


Is it possible today to replicate 30 TB from multiple databases to PostgreSQL close to real-time?

How fast is the network connection?  Do some math -- 30TB through a single pipe will take how many days?  That's just to get the data started.  Then redo the calculation based on how fast the data grows.

There is some amount of overhead; I guestimate that the initial 30TB would take a few weeks.  You have not provided numbers for how much traffic per hour in steady state.  You can estimate that by watching the  size of the binlogs on a representative server, then multiply by 20.

Once you have the 30TB on a single server, have you figured out what you can do with it?  Again, some math would compute how many days or weeks it would take to do something with all that data.

Oh, I forgot about the overhead that you might incur to get the index(es) built.  Are you using SSDs?


cant truncate tables with a pk/ fk relationship?

To reseed, use DBCC CHECKIDENT

DBCC CHECKIDENT ('table', RESEED, 1)

Or you'd have to drop the FK, truncate, then recreate the FK.

This is by design: TRUNCATE TABLE is simple page deallocation and constraints are not checked row by row. This limitation means you can't truncate when there are FKs.. even if you disable the FK.


You could do something like this:

WITH your_mom
    AS (   SELECT   T.ID, CAST(T.ID AS VARCHAR) + ',' AS [FORMATED ID], NTILE(3) OVER ( ORDER BY T.ID ) AS [GROUP]
           FROM     #TEST AS T )
SELECT  *
FROM    your_mom AS b
WHERE   b.ID = (   SELECT   MAX(b2.ID)
                   FROM     your_mom AS b2
                   WHERE    b2.[GROUP] = b.[GROUP] );


ORA-01830 Error for 11.2.0 client but not for 10.2 client

Is possible that for the 10g client you either have a the date format set in a local login.sql file or set in the $ORACLE_HOME/sqlplus/admin/glogin.sql. If you can find this date formatting, then you should set it the same for your 11g client and it should work.

Essentially, when you put in a date string Oracle look at the nls_date_format to see how a date is formatted by default. If that is different than how you have it in a sting, Oracle might have an issue handling the date. you can also use the to_Date funtion to force a particular date format to work.


You can only know when the Statistics were last updated using STATS_DATE.

You can use either of these queries:

SELECT name, STATS_DATE(object_id, index_id), *
FROM   sys.indexes
WHERE  name LIKE 'index name'

or

SELECT name, STATS_DATE(object_id, index_id), *
FROM   sys.indexes
WHERE  OBJECT_ID('schema.table') = object_id

When you rebuild an index, it updates its statistics:

  If statistics correspond to an index, the stats_id value in the sys.stats catalog view is the same as the index_id value in the sys.indexes catalog view, and the following query returns the same results as the preceding query. If statistics do not correspond to an index, they are in the sys.stats results but not in the sys.indexes results.


ORA-00932 on subquery join from subquery join

LOB Rules and Restrictions

  ...

  You cannot specify LOB columns in the ORDER BY clause of a query, the
  GROUP BY clause of a query, or an aggregate function.
  You cannot specify a LOB column in a SELECT... DISTINCT or SELECT...
  UNIQUE statement or in a join. However, you can specify a LOB
  attribute of an object type column in a SELECT... DISTINCT statement,
  a query that uses the UNION, or a MINUS set operator if the object
  type of the column has a MAP or ORDER function defined on it.

  ...


SSDT - Unresolved Reference to temporary table in linked server

I know this is an old question, but I want to throw this out there in case anyone else happens upon this post.  Create a "scripts" folder in your SQL Database Project, and add a Pre-Deploy script that will create the temporary table in Oracle; right click on the script and set the Build Action to PreDeploy.  I know it's not the most elegant solution, but it's quick and dirty, and will get the job done.

You'll have to "Build" the project before you publish it, which may still generate the unresolved object error; in that case you'll have to create the table before you build, and then drop it after, but it should work.


Memory-Optimized Table Memory Allocated issues

You should check out this blog post from the king of In-Memory OLTP, Ned Otter:

Row version lifecycle for In-Memory OLTP

In particular, this passage:

  2. Deallocating rows from memory

  ...

  So the two triggers for Garbage Collection are memory pressure and/or transactional activity. Conversely, that means if there’s no memory pressure – or transactional activity is low – it’s perfectly reasonable to have row versions that aren’t garbage collected. There’s also no way to force garbage collection to occur.

So this is just to clarify that there are situations where garbage collection may not occur, or may occur slowly.

The post goes on to discuss that long-running queries on in-memory tables will completely block garbage collection, so make sure to check for any of those.

The reason the screenshot of SSMS is off (used it higher than allocated) is because it's purely based on the DMVs, so the NULL memory_allocated_for_table_kb throws off the calculation.  This is what runs when you open the database properties window:

SELECT
    isnull((
        select convert(decimal(18,2),(sum(tms.memory_allocated_for_table_kb) + sum(tms.memory_allocated_for_indexes_kb)))
        from [sys].[dm_db_xtp_table_memory_stats] tms)
    , 0.00) AS [MemoryAllocatedToMemoryOptimizedObjectsInKB]

SELECT
    isnull((
        select convert(decimal(18,2),(sum(tms.memory_used_by_table_kb) + sum(tms.memory_used_by_indexes_kb)))
        from [sys].[dm_db_xtp_table_memory_stats] tms)
    , 0.00) AS [MemoryUsedByMemoryOptimizedObjectsInKB]

I'm still not really sure what the purpose of that object_id = 0 row is in dm_db_xtp_table_memory_stats.  I can repeat the behavior locally (create a table with LOB data, then ALTER the column to fit in-row, suddenly I have that same 0 result), but I don't know if it's a problem.

It's possible that it's normal overhead related to in-memory OLTP, but it might be a bug.  If it's causing significant problems, I'd suggest opening a case with Microsoft.  There have been bugs before related to memory usage with LOB data, see MSDN Forums | In Memory OLTP for an example, and this CU: FIX: Out-of-memory error when you run a query to access LOB columns through In-Memory OLTP in SQL Server 2016.


Using dynamic query to loop through Db for a truncate statement

As Jacob H mentioned in the comments, you'll need to query sys.tables with the appropriate USE statement for each database, or by explicitly using the three-part syntax {database}.sys.tables for database.

One way to do this is to query all databases, and while doing so aggregate a string that queries sys.tables for that database. Since you didn't specify a version of SQL Server, I'll use the STRING_AGG() function that is now available in SQL Server 2017. On earlier SQL Server versions, you can use your choice of methods, such as the (officially undocumented and unsupported) variable concatenation used for @DynamicSQL in your question.

-- Print the first 8,000 bytes of TRUNCATE TABLE statements for each database
-- (Comment out the PRINT and enable the EXEC if you want to execute)
DECLARE @sql NVARCHAR(MAX) = ''
SELECT @sql = 'DECLARE @truncateTableSql NVARCHAR(MAX)
        ' + STRING_AGG('
        USE ' + CONVERT(NVARCHAR(MAX), QUOTENAME(name)) + '
        SET @truncateTableSql = NULL
        SELECT @truncateTableSql = STRING_AGG(
            CONVERT(NVARCHAR(MAX),''TRUNCATE TABLE '') +
            QUOTENAME(DB_NAME()) + ''.'' +
            QUOTENAME(SCHEMA_NAME(schema_id)) + ''.'' +
            QUOTENAME(name),
            CHAR(10))
        FROM sys.tables
        WHERE is_ms_shipped = 0 /* Avoid Microsoft-provided tables */
            AND name LIKE ''log%''
        -- Note that PRINT will truncate the string to the first 8,000 bytes
        PRINT(@truncateTableSql)
        --EXEC(@truncateTableSql)
    ', CHAR(10))
FROM sys.databases
WHERE state = 0 /* ONLINE */
    AND is_read_only = 0
    AND name NOT IN ('master','model','msdb','tempdb')

EXEC(@sql)

Simplified query for a single table across databases

An update makes it sounds like only the dbo.log table needs to be truncated. If you only need to target a single table with a known name, you can simplify the script quite a bit. For example:

-- Print the first 8,000 bytes of TRUNCATE TABLE statements
-- (Comment out the PRINT and enable the EXEC if you want to execute)
DECLARE @sql NVARCHAR(MAX) = ''
SELECT @sql = STRING_AGG('TRUNCATE TABLE ' + QUOTENAME(name) + '.dbo.log', CHAR(10))
FROM sys.databases
WHERE state = 0 /* ONLINE */
    AND is_read_only = 0
    AND name NOT IN ('master','model','msdb','tempdb')
    AND OBJECT_ID(name + '.dbo.log') IS NOT NULL
PRINT(@sql)
--EXEC(@sql)

Simplified query for a single table across databases in earlier versions of SQL Server

There are many methods for concatenating strings in SQL Server, and many blog posts explore and vigorously debate these options. I prefer STRING_AGG() if you are using SQL Server 2017 or higher, but using XML to concatenate strings is one option if you are unable to upgrade to SQL Server 2017. For example:

-- Print the first 8,000 bytes of TRUNCATE TABLE statements
-- (Comment out the PRINT and enable the EXEC if you want to execute)
DECLARE @sql NVARCHAR(MAX) = ''
SELECT @sql = CONVERT(NVARCHAR(MAX), (
    SELECT CONVERT(NVARCHAR(MAX), 'TRUNCATE TABLE ') + QUOTENAME(name) + '.dbo.log; '
    FROM sys.databases
        WHERE state = 0 /* ONLINE */
        AND is_read_only = 0
        AND name NOT IN ('master','model','msdb','tempdb')
        AND OBJECT_ID(name + '.dbo.log') IS NOT NULL
    FOR XML PATH('')
))
PRINT(@sql)
--EXEC(@sql)


How to put the results of a query into a variable?

I assumed you were using a pl/pgsql block (probably a function).

For the generic case, you can use record, it can basically take a row of any structure, often used like

DECLARE
    i record;
BEGIN
    FOR i IN SELECT this, that, something, else FROM some_table
    LOOP
        ...
    END LOOP;

There is also a possibility of defining a view:

CREATE VIEW v_portal_data_with_config AS
select a.portal.*, a.p_fb_config.*, a.p_gplus_config
-- note that this does not work straight away, there will be a column naming collision,
-- so you'll have to add column aliases (which then makes using * impossible)
from a.portal
   left outer join a.p_fb_config on portal.pid = p_fb_config.pid
   left outer join a.p_gplus_config on portal.pid = p_gplus_config.pid;

Having this, you can then

DECLARE
    i v_portal_data_with_config%ROWTYPE;
BEGIN
    SELECT * FROM v_portal_data_with_config WHERE ... ;

Sometimes, when having the view makes sense anyway (the query appears in multiple functions, for example), this may me a more convenient way.  However, you introduce an additional dependency which makes harder to change the logic.


Fast way to split primary key into ranges


Login Failed for Domain\ServerName$ when trying to create a new SQL Server Reporting Services Application in SharePoint 2013

Add BOTH Domain\Servername$ login and NT AUTHORITY\Network Service login to SQL Server instance giving you above problem and give both logins the right permissions.

If you are working in a Spanish environment or another language try adding NT AUTHORITY\Servicio de Red  or whatever the equivalent is in your language.

For steps on adding logins see links: http://support.microsoft.com/kb/889646/en-us  and http://www.c-sharpcorner.com/UploadFile/specialhost/how-to-fix-login-failed-for-user-nt-authority-iusrs-in-share/ and https://stackoverflow.com/questions/2806438/login-failed-for-user-domain-machinename

If you have a SQL Server Instance with logins that have been working for you when accessed by application pools and the like, you might want to check which logins are not in the troublesome SQL Server instance and that you might need.

Other comments:

Ok, even though the error said: Login Failed for user 'Domain\Servername$' and I added that login to the problematic SQL Server instance per what I'd seen described on the internet, it seems that the login I ALSO needed to add was NT AUTHORITY\Network Service.

I basically decided to add any logins that were in my normal SQL Server instance that I was using for the regular SharePoint databases and that weren't in the SQL Server instance I was using for SharePoint Reporting Services to the latter. Something else that tripped me up when I was looking originally to add the NT AUTHORITY\Network Service login was that I couldn't find it. It turns out I had to add it in Spanish as NT AUTHORITY\Servicio de red, which is wierd because the app pool has its Identity in English. I guess this happens when some systems have English as default language and some others have Spanish


Why can I not DENY permissions through module signing?

Actually, DENY permissions do get applied when used via module signing. If your test indicated that nothing happened, then most likely you were experiencing the wonder of ownership chaining ;-). If a User has an explicit DENY for INSERT on a Table, but the Stored Procedure does an INSERT and has the same authorization as the Table, then the User's permission isn't even checked for what the Stored Procedure does, only that the User can EXECUTE the Stored Procedure. In order to test you need to prevent ownership-chaining from interfering, and you do that by making the statement(s) within the Stored Procedure Dynamic SQL (i.e. wrap the statement(s) in EXEC (N'...'); ). Then you can (and will, as my testing shows) see the effect of DENY permissions assigned to the Certificate- / Key- based User.
As to why the recommendation to not use Module Signing to restrict access, I would say that:

The default state is that permission is already implicitly denied for something, else there is no need for the module signing. There is no purpose to adding restrictions in this manner. If restrictions are needed, they can be added to the User / Login / Role.
Meaning: module signing exists because there are restrictions that need to be overcome (and impersonation is not a great way of overcoming them), not because the system is wide-open and restrictions are needed.

The permissions apparatus is, in itself, conceptually different: regular permissions (where DENY does work) deals with who can do certain operations (i.e. Users / Logins / Roles), but module signing deals with what can do certain operations (i.e. code such as Stored Procedures, Triggers, etc). Generally speaking, if you don't want a module to be able to do a particular operation, just don't code it to do that operation in the first place.
You can't prevent someone from attempting to execute queries that do operations that they shouldn't do, hence we have implicit and explicit DENYs. But you do have control over the code ;-)

While probably not a reasoning for Microsoft to have made that statement, I would say that a minor reason to avoid it is simply the confusion caused by situations such as your testing (where it appeared to not even work due to ownership-chaining doing its job).

The only time that I can think of, at the moment, to have a DENY in module signing is if module signing is used to allow Dynamic SQL to work, and the Dynamic SQL is structured to allow for something along the lines of the User passing in a Table name to do an operation on and you want to ensure that some tables are considered "off limits", though I am not sure that the DENY is even needed in this scenario (could perhaps be a case of it simply being easier to GRANT access to a Schema but DENY one or two Tables within it).


Meaning of "The database X needs to be upgraded" in ClearTrace

It seems to be that the Options dialogue is for the database that ClearTrace uses to store its own application data.  It created a dedicated database for it and it added its own tables to that database, during the 'upgrade'.


Unique indexed column in where clause and mysql execution after finding first row

If the constraint on login is unique... any serious sql database vendor will stop searching at the first and only possible occurence met.

In the second case, it will stop at the first and only possible occurence, and returns the record if the password is '123', nothing if not.


Fill-in Missing Dates with calendar table

FROM DATE_TBL
LEFT JOIN LOG_TBL USING(date)


How do you log onto courgette?  Would that username identify you?

You can check that by running

select sys_context('userenv', 'os_user')
from dual;

The USERENV namespace can retrieve a lot of different information about the user and their environment.  Find out more.


Issue is the following.

Let a Hyper-V VM is configured with high startup memory (like 10240MB), and dynamic memory limits active and are like 512-10240 range.

VM starts up with full amount of RAM available. Short after load the host reclaims excess memory from the VM. At the same moment SQL Server process starts to create that unexplainable CPU load.

It doesn't happens though, if startup memory set to 512MB.


Is this package missing and if so, how do I fix?

Data Pump is a fully integrated feature of Oracle Database 11g. Data
  Pump is installed automatically during database creation and database
  upgrade...more

It should be installed during the database creation. I found logical error in your query to check whether or not the data pump package is installed. The object type PACKAGE should be in upper case to be selected.

Demo:

You query has returned the following only.

SQL> SELECT object_name FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE','package') and object_name like '%DATA%' order by OBJECT_NAME;

OBJECT_NAME
------------------------------
DATABASE_NAME
DBMS_FEATURE_DATABASE_VAULT
DBMS_FEATURE_DATA_GUARD
DBMS_FEATURE_DATA_REDACTION
DBMS_FEATURE_EXADATA
EXF$VALIDATEDATAITEM
ODCIANYDATADUMP
XDB_DATASTORE_PROC

8 rows selected.

And the query with object type PACKAGE in upper case has returned the following. You can see the DATAPUMP package is there.

SQL> SELECT object_name FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE','PACKAGE') and object_name like '%DATA%' order by OBJECT_NAME;

OBJECT_NAME
------------------------------
...
CWM1_OLAP_VALIDATE_METADATA
CWM2_OLAP_METADATA_REFRESH
CWM2_OLAP_VALIDATE_METADATA
DATABASE_NAME
DBMS_DATAPUMP
DBMS_DATAPUMP_UTL
DBMS_DATA_MINING
DBMS_DATA_MINING_INTERNAL
...


no more data read from socket error with triggers

This Error can happen with PL/SQL and came up with ORA-600 [kqlidchg0],ORA-00001 and/or ORA-01578 see MOS Doc ID 17210525.8.

For 12.1 and 11.2 install latest Patch (April 2016)

ORA-600 [kqlidchg0] with PL/SQL

ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated

Try:

ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:NONE';


Why use multiple full & differential backups when I can use transaction log backups?

The difference in functionality is minimal - the difference in what happens when you come to actually do a restore is tremendous.

Log backups are sequential, so in the above scenario to restore to 00:55 you would need to restore

full_backup_2018_05_09_000000.bak
diff_backup_2018_05_09_004500.bak
tran_backup_2018_05_09_005000.trn
tran_backup_2018_05_09_005500.trn

VS

full_backup_2018_05_09_000000.bak
tran_backup_2018_05_09_000500.trn
tran_backup_2018_05_09_001000.trn
tran_backup_2018_05_09_001500.trn
tran_backup_2018_05_09_002000.trn
tran_backup_2018_05_09_002500.trn
tran_backup_2018_05_09_003000.trn
tran_backup_2018_05_09_003500.trn
tran_backup_2018_05_09_004000.trn
tran_backup_2018_05_09_004500.trn
tran_backup_2018_05_09_005000.trn
tran_backup_2018_05_09_005500.trn

Utilizing DIFFs and FULLS means you don't need to replay through all those log files, which can be time consuming and potentially put a huge dent into your RTO (Recovery Time Objective)

You have to ask yourself in the event of a disaster, or any recovery scenario, how long are you willing to wait for the database to become available again - this normally dictates what methods you can then use.

Alternative answer: If you're a masochist, use just log backups.


INSERT performance difference between temporary tables and table variable

The obvious difference between the two plans is that the fast one is parallel and the slower one serial.

This is one of the limitations of plans that insert into table variables. As mentioned in the comments (and it seems as though it had the desired effect) you could try doing

INSERT INTO @DATA ( ... )
EXEC('SELECT .. FROM ...')

to see if that gets around the limitation.


Why isn't the primary (clustered) key being used in this query?

The clustered index is partitioned on ReadTime so it couldn't use the PK as you describe. It would need to find the Max(Id) for each partition and then find the max of those. It is possible to rewrite the query to get such a plan however.

Using an example based on the article here a possible rewrite might be

SELECT MAX(ID) AS ID
FROM   sys.partitions AS P
       CROSS APPLY (SELECT MAX(ID) AS ID
                    FROM   [dbo].[CDSIM_BE]
                    WHERE  $PARTITION.MonthlyArchiveFunction9(ReadTime)
                                                    = P.partition_number) AS A
WHERE  P.object_id = OBJECT_ID('dbo.CDSIM_BE')
       AND P.index_id <= 1;

To process each partition in turn.

Note the plan still has a scan (with a seek predicate to select the partition) but this is not a full scan of the partition.

The scan is in index order with direction "BACKWARD". The TOP iterator can stop requesting rows from the scan after the first one is received.


Find fragmented space free below HWM Oracle

Answer is complex and involves the knowing of different Oracle concepts. I try to explain (sorry if I am going to be too wordy).

The High Water Mark (HWM) is the point in a segment beyond which data blocks are unformatted and have never been used (Oracle Concept definition). Considering that you are using ASSM (Automatic Segment Space Management)

all blocks above HWM are unformatted and never been used. All blocks below HWM might be:

Formatted and contain data
Formatted and empty because the data was deleted
Allocated, but currently unformatted and unused

DML operations do not release free space from the table below the HWM. Therefore a lot of DML on a table might leave the table fragmented. DDLs always reset the HWM. More consideration about the block use can be done considering two impotant storage parameters: PCTFREE and PCTUSED.

What about HWM in files? The high water mark of a file shows you the smallest size the file could be "shrunk" down to. Any DBA has its own query to find the HWM in a datafile. Personally I use one taken by Tom Kyte and is this:

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
/

The HWM position in a datafile does no represent necessarily the free space available in a datafile. You may have a 20Gb tablespace where dba_free_space shows 19gb free but latest HWM is set at 18Gb position.

In conclusion: the position of HWM in a datafile does not represent the real space freely available in the datafile. For this, the query on sm$ts_free is right. Additionally tables can have allocated more space than one is really used.


Select query to retrieve rows with null values

This example retrieves only "name" and the "SUM(credit)", when the
  "credit" has values.

The query you presented will retrieve a row for every present name, even if all associated credit columns are NULL. You get a row with a NULL value for SUM(credit) then. Null values are just ignored by the aggregate function sum():

You only get no row for a particular name if no row for that name exists in the table expenses for the given WHERE expressions.

I am assuming you want
.. only names matching 'vendor0%'
.. but all of those, even if they have no expenses in 2013.

Your query could work like this:

SELECT name, SUM(CASE WHEN date LIKE '2013%' THEN credit END) AS credit
FROM   expenses
WHERE  name LIKE 'vendor0%'
GROUP  BY name
ORDER  BY name

CASE defaults to NULL if no ELSE branch is given.
Aside: You shouldn't store date / time values as text. Use an appropriate type, it has many advantages.
And don't use "name" or "date" as identifiers. "name" is not a descriptive name and "date" is a reserved word in standard SQL and a function and base type name in Postgres.


Getting error while creating a New Connection using Studio 3T for Mongo DB

As per MongoDB articles blog here The Connection Manager in Studio 3T makes it easy to connect to MongoDB, whether it is cloud-hosted or on-premise, or through a direct connection or a replica set.

Create a new connection

Open Studio 3T.
Click on the Connect button either in the global toolbar or under
the Recent Connections tab. This will open the Connection Manager.
Click on New Connection. This will open the New Connection window.

  Please note that with the release of Studio 3T 2018.6 and support for
  SCRAM-SHA-256, it will no longer be possible to connect to MongoDB
  version 2.6, which reached its end of life in October 2016. Only
  MongoDB versions 3.0 and higher are supported.

Studio 3T supports various settings nested under the Server, Authentication, SSL, SSH Tunnel, and Advanced tabs:

Server

Direct Connection
Replica Set or Sharded Cluster

Authentication

Basic (SCRAM-SHA-256)
Legacy (MongoDB-CR or SCRAM-SHA-1)
X.509
Kerberos (GSSAPI) - Enterprise only
LDAP (PLAIN) - Enterprise only

SSL

Accept any server SSL certificate
Use own Root CA file
Accept trusted server SSL certificates
Use Client Certificate
Allow invalid hostnames
User Server Name Indication

SSH Tunnel
 - Use SSH tunnel to connect

Advanced

Max. connection idle time
Server selection timeout
Socket timeout
Connect timeout

As I am able to see from attached screen shot as Studio 3T is showing the TRIAL License. Instead of Studio 3T IDE you can use the Robo 3T (formerly Robomongo) is the free lightweight GUI for MongoDB.

For example the simple new connection of Robo 3T (Robomongo) will be as follows:

For further your ref here , here and here


Special function slow in where but not in select

Here's the difference between the two statements:

The first statement calls the levenshtein function only on the retrieved resultset.

The second statement calls the levenshtein and the length function as a so called "predicate" for every row in the table to retrieve the resultset. Even if levenshtein is only evaluated if the first condition is true, then there's the overhead of doing additional comparisons.

Thus, the second query is naturally slower.

IMHO this is a CPU bound problem. To make this query faster, either supply more predicates that can be evaluated (means: add more restrictions to the where clause), add more CPU power and try parallel selects (if mysql supports that).


There are perfmon counters that you can probably use for what you're trying to do. Since it's a counter provided by SQL, it's available in sys.dm_os_performance_counters. Try select * from sys.dm_os_performance_counters where object_name like '%repl%'. Slice and dice as necessary.


What is the correct way to present the disks to ASM?

How did you "format" them? Oracle expect them to be block devices, not formatted with a file system. Did you read this documentation?

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/ladbi/configuring-device-persistence-manually-for-oracle-asm.html#GUID-70D50812-CCB2-41E4-AA3B-4689E1DA934E
https://oracle-base.com/articles/linux/udev-scsi-rules-configuration-in-oracle-linux#load_updated_block_device_partitions"
https://dba010.com/2019/07/22/udev-rules-for-configuring-asm-disks/
https://www.thegeekdiary.com/centos-rhel-7-how-to-set-udev-rules-for-asm-on-multipath-disks/


What server property makes TransactionScope escalating to MSDTC?

Here are some of the things you can try to get it to work:

Compare Enlist=false in your connection strings
Compare MultipleActiveResultSets=true in your connection strings
User instance databases appear to work differently for some reason - we couldn't get it to work consistently with them - use full databases all of the time
You can reuse the same connection (close/reopen), or use more than one connection in a TransactionScope, but you cannot use more than one connection at the same time. You CAN however keep a ThreadStatic connection and pass that around (or, as we did, use ThreadLocal<> in a db wrapper class) - this solved a concurrency problem for us in ASP.NET which was causing unnecessary escalation
Don't bother if your db < 2008 or if compatibility level < 100
When using connection strings, don't use the ConnectionString property of another connection to create your new connection - it may not be the same as the original, which will cause escalation
Keep your connections open only for as long as you need to, close them at your earliest opportunity.

Stuff I found useful:

A ConnectionScope class gave me some ideas: http://blogs.msdn.com/b/dataaccess/archive/2006/02/14/532026.aspx
There's more to TransactionScope than the default ctor! http://blogs.msdn.com/b/dbrowne/archive/2010/06/03/using-new-transactionscope-considered-harmful.aspx
Note that there was nothing new I could find that would be different between 2008 and 2008 R2 - here is a list of stuff that was new in that release: http://msdn.microsoft.com/en-us/library/bb500435%28SQL.105%29.aspx


Wrong Last Execution Time sys.dm_exec_procedure_stats

The core problem is that your query can potentially match on two different procedures in two different databases that happen to have the same object_id. While there isn't a reliable way to reproduce it on demand, I've seen it enough times to know it is common, and your query needs to protect against it. You can do that with a WHERE clause:

WHERE s.database_id = DB_ID();

When pulling stats for other databases, you can use:

...
FROM sys.dm_exec_procedure_stats AS s
INNER JOIN [other_database].sys.procedures AS p
ON s.[object_id] = p.[object_id]
WHERE s.[database_id] = DB_ID(N'other_database');

Note 1: The stats DMV does not persist data beyond service restarts, failovers, or any action that causes the procedure cache to be flushed (for the instance, for the database, or for a specific procedure). You also won't see any entry in the DMV for stored procedures that are intentionally not cached, e.g. EXEC dbo.procedurename WITH RECOMPILE;. So it isn't necessarily reliable to conclude that procedure A is unused simply because it isn't present in the DMV.

Note 2: It's possible for a database named foo to have different database_id values over its lifetime (consider the case where you drop foo, create another database, then create another instance of foo). This is unlikely, but is still worth mentioning, because you could potentially miss executions of a stored procedure that happened in the older copy of foo.

Note 3: If you want to truly measure determine the set of procedures that are unused, you will have to use heavy methods like auditing / extended events / server-side trace, or manually add logging to your stored procedures as I describe in this tip:

Simple process to track and log stored procedure use

And even then, you need to leave that running for a full business cycle, to be sure you don't miss reports that are only run, say, once a quarter or other fiscal period.

Once you have identified the set of stored procedures you don't think are being used, don't drop them; put them in a separate schema (I'm using zzz here so it sorts at the bottom of the list in Object Explorer, but the name is unimportant - just make sure none of your users have it set as their default schema, and maybe even implement DENY EXECUTE).

CREATE SCHEMA zzz;
GO
ALTER SCHEMA zzz TRANSFER dbo.UnusedProcedureName1;
ALTER SCHEMA zzz TRANSFER dbo.UnusedProcedureName2;
...

This doesn't alleviate clutter quite as effectively as dropping the procedures, but is kind of like a very low-cost insurance policy (only the storage for the stored procedure text in the data file and backups), making it very easy to restore a procedure that was deemed unused in error.


Why am I getting ORA-00937 error using only aggregate functions?

Aggregate functions, but not on the same object, in a subquery, with addition. Use this:

SELECT
  (SELECT COUNT(T1.FIELD) FROM T1) +
  (SELECT COUNT(T2.FIELD) FROM T2)
FROM DUAL;


Refresh (replicate) MySQL database from server to server

I would imagine that a nightly "refresh" between two databases like you say would involve dumping one database and importing the dump into the other database. That would lead to major performance drags every time you do that process.

Personally, I don't think that is a great idea when there are tools that allow you to replicate data between database servers. I am afraid I don't entirely understand what your use case entails, but I would recommend reading up on MySQL Replication


Help with a complicated MySQL Query inserting data using select from 2 tables

Okay its done, the cursor usage worked

here is the code m using

UPDATE player_list_items SET player_list_item_cellX = cell_X, player_list_item_cellY = cell_Y
             WHERE player_list_id IN (
                 SELECT player_list_id FROM (
                 SELECT player_list_id FROM player_list_items
                 WHERE player_list_player_id = playerID AND player_list_list_id = @listID AND player_list_item_cellX IS NULL
                 ORDER BY player_list_id LIMIT 1
                 ) tmp
             );

please let me know if this solution is not a good one... as I found nothing besides this to update multiples rows with different values..

its in a loop against cursor that have all the map_details data needs to be put in this table


Unexpected index scan when doing query against varchar column with numeric parameter

I'd assume it's because numbers can be represented multiple ways in text. According to the docs on How MySQL Uses Indexes:

  Comparison of dissimilar columns may prevent use of indexes if values cannot be compared directly without conversion. Suppose that a numeric column is compared to a string column. For a given value such as 1 in the numeric column, it might compare equal to any number of values in the string column such as '1', ' 1', '00001', or '01.e1' (sic). This rules out use of any indexes for the string column.

(I think 01.e1 is a typo which should read 0.1e1.)

Since a number does not equate to one and only one text value, the index can't be used.

On the other hand, this same situation does not apply in the opposite direction. A text representation of a number does equate to one and only one numeric value. In other words:

While numeric 1 could equate to texts '1', ' 1', '00001', or '0.1e1',
the texts '1', ' 1', '00001', and '0.1e1' all equate only to numeric 1.

So, if instead you had a numeric column and were comparing string values to it, the index can be used as you expect. (Really, I just tried it.)


You can use Transactional Replication, but you have to exclude the DELETEs. Just replicate INSERTs and UPDATEs.

Here's a related article.


How can I find out which statistics are evaluated by the optimizer?

I don't know of any exposed or documented way that SQL Server keeps track of this as a separate statistic (no pun intended). But if you monitor your server for expensive queries (long duration, high CPU, high I/O etc) and collect the plans for those queries, you may be able to derive some of that information from the plans you've collected - but keep in mind that not all statistics will be represented (some details on that here). If you know of queries off-hand that are slow and you suspect the problem may be due to specific statistics or missing statistics, you can generate an actual execution plan for that query and inspect the plan - you'll often find evidence such as a missing statistics warning or a huge discrepancy between estimated and actual rows (and the latter is why generating an estimated plan only is kind of worthless).

SentryOne Performance Advisor product is one way to help with the collection of high-impact queries ("Top SQL") and the execution plans associated with them at runtime, since they are collected and saved for you over time. Also Plan Explorer is a free tool that can generate plans or open existing plans you've collected, often highlighting some of these issues much easier than viewing the graphical showplan in Management Studio or parsing the XML yourself.*