too many rows for one update

If so use simple

UPDATE users_ids_mapping m,
       remote_users u
SET u.user_id = m.new_user_id
WHERE u.user_id = m.current_user_id
LIMIT 1000

Replace 1000 with a chunk size which is safe for you - both for resource consumption and for progress displaying.

Repeat until "Affected rows = 0". Set approximate upper limit of the progressbar with = 1 + (count of records in users_ids_mapping) / (chunk size)

UPDATE

  For multiple-table syntax, ORDER BY and LIMIT cannot be used.

UPDATE remote_users u
SET user_id = ( SELECT new_user_id
                  FROM users_ids_mapping
                  WHERE u.user_id = current_user_id )
WHERE EXISTS ( SELECT new_user_id
               FROM users_ids_mapping
               WHERE u.user_id = current_user_id )
LIMIT ??? ;

fiddle


mysqldump locks all tables even when specifically grabbing a few?

Use --single-transaction option, and it won't lock any, provided you are using a transaction-aware storage engine (pretty much anything except MyISAM and MEMORY engines)


Database under heavier load -- all SQL queries start timing out after a period of hours/days

I was setting indices on my PKs, but I did not have indices on my FKs. Most of my queries were of the form: "Select Playlists p where p.UserId = 'XXX'"

Even though I had an index on User's ID, I did not have one on Playlist's UserID. This was root cause.


Stored procedure with case statement for 'AND' based on parameter value

I don't think you even need dynamic SQL here. You can use variables in the query.

SELECT *
       FROM [audit].[LoginAttempt]
       WHERE [TimeStamp] <= dateadd(day, -1 * @backDays, getdate())
             AND CASE
                   WHEN @successArg IS NOT NULL
                     THEN (SELECT 1
                                  WHERE [Success] = @successArg)
                   ELSE 1
                 END = 1;

(Not tested as no sample data was provided. Just to demonstrate the idea.)

If you look close at it, you'll see, that the entire CASE ... END is the left operand of an = operation. The right side of the operation is 1.

Unfortunately SQL Server knows no Boolean type we could have directly returned from the CASE ... END. (Others, e.g. PostgreSQL do.) So we have to trick it using an expression it can use for a Boolean operation. That's why we use the = operation.

Now we need a way of making this operation evaluate to true when the conditions we actually want to test are met. So the idea is, if we come to the decision, that they are met, we return one designated value for our left side of the =. On the other side, we just use that value literally. The = will be true then. If our conditions don't meet, we return any other value for the left side, and the = won't be true. As a value to indicate the conditions were met, lets choose the 1. It's close to a representation of a Boolean we might have in mind. (But we could have chosen nearly anything (NOT NULL, otherwise we had to change the = operation to IS NULL).).

So how can we make our left expression to return 1, when the conditions are met?

Well, we can use a CASE ... END statement to return a value based on certain conditions. You already know the CASE ... END. It's somewhat similar to a switch or if else constructs in C-like languages (or with different names in other procedural languages).

What we need to test is if the input variable @successArg is null or not. If it's null, that means the caller doesn't care, whether the logged login attempts were successful or not. Otherwise the value of @successArg indicates whether he wants only successful logins (@successArg = 1) or only unsuccessful (@successArg = 0). This will give us to cases on top: Either ignore the successfulness or take it into account. Hence we have two (main) branches in our CASE ... END branching about @successArg IS NOT NULL;

Let's start with the easier case, when @successArg IS NULL. That's the 'ELSE' branch. Here, we don't care about if the login attempt was successful or not. Being successful or not successful (think of it a Boolean expression) is always true, for any row. So we just return our indicator, that this was a match, 1.

When @successArg IS NULL, @successArg holds the value, the rows must have in the column [Success]. This is given only if @successArg = [Success]. As already mentioned, @successArg = [Success] alone won't be something SQL Server could handle in that context. So we need our true indicating value 1 to be returned when @successArg = [Success] for the current row, something else otherwise.

One way of doing this would be another, inner CASE ... WHEN or we might use a correlated subquery. It's "correlated" as it uses values of columns from the current row of the outer query. And it's "sub", well because it's "sub", viewed from the position of the outer query. Furthermore, we use the feature in SQL Server that a SELECT with no FROM will produce one record if the WHERE of that query is true (or if there is none), otherwise an empty set. So let's produce a record with one column holding 1 when @successArg = [Success]. Such a record, with only one column, will implicitly be casted to a scalar if needed, hence it's suitable for our = operation outside. When @successArg <> [Success], that subquery will produce an empty set. The empty set in that scalar context will be implicitly casted to NULL. As NULL = 1 isn't true, this will do it for us.

One side note on this: It also can be done without a CASE ... END but simply a Boolean expression:

SELECT *
       FROM [audit].[LoginAttempt]
       WHERE [TimeStamp] <= dateadd(day, -1 * @backDays, getdate())
             AND (@successArg IS NULL
                   OR [Success] = @successArg);

That follows our thoughts from above. In case @successArg IS NULL we want "true" regardless of anything else. Otherwise we only want "true" when [Success] = @successArg. In any other case "false". (Note: The parenthesis are needed to override the precedence of AND over OR because the previous condition.)

In terms of how the query is executed, hence performance, I don't think this makes a significant difference, if any (but frankly I don't know this for sure). The solution with a CASE ... WHEN though, might be easier to read and therefore maintain. The Boolean only solution, on the other hand, also works in systems, that are not capable of a CASE ... END.


Why have one function for tables and a separate function for views? You can handle both just by removing the restrictive filter on type = 'u' (and actually you don't need sys.objects at all). Here is a rewrite with some other minor enhancements:

CREATE FUNCTION dbo.get_columns
(
  @object_name nvarchar(513)
  -- needs to support Unicode!
  -- should pass schema.object
)
RETURNS nvarchar(max) -- can contain Unicode!
AS
BEGIN -- we don't need any variables
  RETURN
  (
    SELECT STUFF((SELECT N', ' + name
      FROM sys.columns WITH (NOLOCK)
      WHERE [object_id] = OBJECT_ID(@object_name)
      AND name <> N'row_timestamp'
      FOR XML PATH(''), TYPE).value(N'.[1]',N'nvarchar(max)'),1,2,'')
  );
END


PostgreSQL 8.0 - Windows Server 2008 R2 install - Service won't start

Try uninstalling postgres, install this:

Microsoft Visual C++ 2008 Redistributable Package (x86)

Reinstall postgres and see if it works then.


How to unnest and display elements of a JSON array?

According to the documentation, the #> operator comes in useful:

-- name and genre of people (1 row per person)
select
    json_array_elements(
      t.test#>'{clients, info}'
        )::json
    ->>'nom' as nom,
    json_array_elements(
      t.test#>'{clients, info}'
        )::json
    ->>'genre' as genre
from my_table t

-- number of people, grouped by gender (1 row per gender)
select
      json_array_elements(
         t.test#>'{clients, info}'
           )::json
           ->>'genre' as genre,
      count (t.*) as count
from my_table t
group by 1

SqlFiddle


How can I execute multiple DDL statements within a transaction in Oracle?

You can issue multiple DDL statements in a single transaction using the CREATE SCHEMA command though you are limited to just the CREATE TABLE, CREATE VIEW, and GRANT statements.

CREATE SCHEMA AUTHORIZATION <<Oracle username>>
  CREATE TABLE a( col1 number, col2 number ) -- Note no semicolon
  CREATE TABLE b( col3 number, col4 number );

If you are trying to upgrade an existing database, depending on the Oracle edition and configuration, I would tend to suspect that you'd be better served with something like Oracle Flashback Database.  If you create a guaranteed restore point before you start the upgrade, for example, you can simply flashback the database to that restore point if the upgrade fails regardless of how many transactions you've committed during the upgrade.


mongorestore only one field of a document

Import your user data (.bson) to other temporary collection and then use forEach to update documents created_at keys.


Find "Mystery processes" on a MS SQL Sever environment? (SSIS, databases, etc)

Assuming this IT contact has top-level access to every Instance/
  Database on this server, should I just access him for a high-level
  mapping of every instance-database and potentially access? (I'm
  literally the only one working with these databases). A lot of stuff
  I do not have access to, I'm either unaware of its existence, or its
  hidden.

If you're now the defacto DBA you should, in accordance with your IT/Security policies, arrange for administrative access to each instance. This will likely be sysadmin access to each instance. There is no collective 'master admin' across all instances on a server. Instances are isolated from a security perspective and admins on one do not necessarily get/need access to another instance.

  What happens if this IT contact does not have admin access to some "pieces" or databases here? Are we screwed?

It is possible for local administrators of the actual server to regain access to a server when all sysadmins are lost. See this link for more information. Your IT contact will need local admin access to the server to use this fix.

  Should I request remote access to the actual Server (probably Windows Server 2012) that this database is hosted on? I'm wondering
  if that would help me investigate/ find where these Mystery SSIS
  scripts are.

Potentially yes. If there are SSIS packages stored on the file system then you may need remote access to view and manage those packages.

  How do I find out what robot-script is writing to a bunch of tables? Should I set up SQL Server Profiler? I guess it's called
  Extended Events now? What access/ permissions are required for this
  (I assume I just need trace permissions on the database where the
  tables are being updated)? Again, I'm well aware of the database
  where the updated tables are located --- there are SSIS scripts in
  that Instance, but none that write to these tables. Some "Mystery
  Process/ Script" is doing so daily.

Profiler and Extended Events are different tools, but both can be used for this purpose.

Check out this article series on SQLServerCentral.com for information on Extended Events. This will help you put together a session to capture the information you're after.

One additional note, don't be like the DBA who just left your company. As you start to piece together the packages, scripts, jobs and other bits of information about this server, document it. It will not only help anyone who comes in to provide SQL Server support, but you'll find it an invaluable tool yourself.


It sounds like the linked server may not have a QUOTENAME function. Perhaps it's not a SQL Server itself, or is older than SQL 2008?

Check what that server is, and the functions it can perform. Note that:

QUOTENAME([column1])

is equivalent to:

'[' + REPLACE(column1, ']', ']]') + ']'

I think most SQL variants will have some version of REPLACE.

UPDATE: Based on you comments, you actually wanted to format column1 for inclusion in the CSV file, assuming it may have commas in it.

To do that, you need to put double quotes around the contents of column1, and replace any double quote characters in column1 with two double quotes (which acts as an escape).

So, again, assuming that the destination server does have some form of the REPLACE function, you should be able to use:

'"' + REPLACE([column1], '"', '""') + '"'

An alternative:  If there's no equivalent to the REPLACE function on the remote server, you could try setting up an SSIS package to do the job. You can replace column1 in the data flow with the above, using a Derived Column component. However, this is probably not a great option if you're not familiar with SSIS packages already.


Why ORA-00932: Inconsistent Types error?

When you are casting a varchar to a number you have to make sure this is possible for ALL values in this table. So in your case also for the values you think are filtered by "WHERE UPPER(NAME)!='XXX'".
The reason is the optimizer does not have to follow a specific order for the logic in your query. So it could do the casting and joining the tables frist and only after do the filtering specified in the inner query.
Changing the * to a specific field can change the execution plan the optimizer chooses, hence you could have the problem with one version and not the other.

You could use your own function to avoid this error:

create or replace function tonumberorzero(txt in varchar2) return number
 is
   retval number;
 begin
    return to_number(txt);
 exception
    when invalid_number then return 0;
 end;


Looking for a utility to move data between databases (prod to dev) SQL Server 2012

Since you mentioned you want to copy table data only I guess SSIS would be perfect. I have been using it and it works just fine for me. It also depends on level of competency you hold with SSIS. You can go for backup restore but it would also restore tables whoes data you dont want to change. If you want complete refresh then of course no better option than backup restore.

You can go for import export wixard as well and its more easy than SSIS because it almost does all data conversion(if required) for you.
You should use OLEDB provider always when data transfer is to be done within SQL server.There are tools but personally I found SSIS more relaiable than tools.

Have a look at below link

http://www.mssqltips.com/sqlservertip/2684/importing-sql-server-data-using-ssis--which-option-is-fastest/

Best practice for SSIS packages

http://blogs.msdn.com/b/sqlcat/archive/2013/09/16/top-10-sql-server-integration-services-best-practices.aspx


Do OR conditions in Oracle SQL queries spoil index use / cause full table scan?

I had a very similar issue in Oracle 12c. In experimenting, the PRECOMPUTE_SUBQUERY optimizer hint 'forced' the subquery to execute first, thus making use of the defined indexes.  In your snippet, that would look like this:

...
where t.ObjectId = 12345
or t.Id in (
    select /*+ PRECOMPUTE_SUBQUERY */ TaskId
    from ObjectAffectingTasks where ObjectId=12345
)


Alternative way to compress NVARCHAR(MAX)?

Both page and row compression do not compress BLOBs.

  Because of their size, large-value data types are sometimes stored separately from the normal row data on special purpose pages. Data compression is not available for the data that is stored separately.

If you want to compress BLOBs you need to store them as VARBINARY(MAX) and apply your stream compression algorithm of choice. For example GZipStream. There are many examples how to do this, just search for GZipStream and SQLCLR.


Efficient way to copy/sync a table - ideas on different approaches

You have many options, depending on what you want to achieve and the constraints you have on the source/copy database:

Merge Replication: can be bi-directional. Can be filtered and implement complex JOIN filters. The subscriber database is writable. Needs to have a rowguidcol on each table (uniqueidentifier UNIQUE NOT NULL ROWGUIDCOL column), so it can be a deal breaker if you can't touch the schema of the source database. Ships incremental changes quite efficiently.
Snapshot replication: uni-directional. Can be filtered. The subscriber database is writable. No schema changes needed. Doesn't ship incremental changes: it's all or nothing.
Transactional Replication: can be bi-directional (but updateable subscriptions is deprecated and needs an additional column on each updateable article. Peer-to-peer is enterprise-only, so I'm mentioning it so that you know it exists, but it won't work for you). Can have static filters. Every table needs to have a PRIMARY KEY defined, which is not typically the case in ugly, hairy, legacy third-party applications.
Mirroring: the database at the mirror server can be read from a snapshot, which is an Enterprise features, unless you use SQL Server 2016 SP1 or later, where snapshots have been included in all editions. Wouldn't work for HA, just for scale-out. Uni-directional, no filters, no schema changes needed. The snapshot is a static copy of the data at a given time, so you need to take snapshots at different times to see new data. This means you have to kill connections to older snapshots before you create the new one.
Log Shipping: can be used to obtain a read-only copy of the primary database by using RESTORE WITH STANDBY. Needs to kill all connections before restoring additional t-logs. No filters, no schema changes needed. Interferes with existing transaction log backup schedules.
Custom sync process: you could combine different techniques to build your own sync process, but I really advise against that. The complexities are countless and it's hard to outsmart existing scale-out or data distribution tecnhnologies built in the product.


Index fragementation ? is it unavoidable

That may work for your clustered indexes, but you'll also have nonclustered indexes to contend with. They can suffer a lot too, and that's often where it can matter the most.

But you should consider the impact. There are times when fragmentation can be a big thing (such as when you delete data from heaps a lot), and times when it's really not such a huge problem. There are typically other matters that are more important to tune than trying desperately to avoid fragmentation.


How to copy a database in sharded cluster

If you're using 3.2 and above you can use mongodump (Docs found here) and mongorestore (Docs found here).  Note that the docs state it can be problematic to backup sharded databases.  You will have to consider how in sync you want the data and take proper action.  Mongo docs going deeper on that can be found here.

Mongo 3.2 supports backing up sharded clusters with a snapshot, if you want to use that option check out the docs found here.

Finally, restoring the databases using the various processes can be found on Mongo's docs on restoring sharded clusters found here.  Again this will differ based on what version you're running, so be careful to read the docs.  They all go through the process in depth and clearly.

I thought you could use copyDatabase, but the docs state that it does not work with sharded databases.


How to get the SUM between dates in a WHERE clause?

You might laugh when you here this, by there is an aggregate clause that triggers summary between breaks in values. The aggregrate clause is WITH ROLLUP. (Look in MySQL Documentation under GROUP BY Modifiers)

let's take your query and make the following changes

Remove ORDER BY
Substitute WITH ROLLUP
Remove name

You get this

SELECT
cdr.datefield AS 'date',
a.id AS 'id',
SUM(t.debit_amount - t.credit_amount) AS 'balance'
FROM calendar cdr
JOIN transactions t ON (cdr.datefield >= t.value_date)
JOIN accounts a ON (a.id = t.account_id)
WHERE cdr.datefield IN ('2014-03-31', '2013-03-31', '2012-03-31')
GROUP BY cdr.datefield, a.id
WITH ROLLUP;

Here is what should happen

In between each datefield, there will be

datefield
id NULL
a sum of balance for that date

The last row return

datefield NULL
id NULL
a sum of balance for all dates

Based on the desired output, you want Cash, Payables and Issued Capital as zero in case there is no transaction type for the given date. You also done want the last row because you don't want the grand total. What you do is take the new query, make it a subquery, and LEFT JOIN the accounts table to the subquery, returning zero for balance if it is NULL and exclude any row whose date is NULL.

Here is the new query

SELECT
    BB.date,AA.id,AA.name,IFNULL(BB.balance,0) balance
FROM accounts AA LEFT JOIN
(SELECT
cdr.datefield AS 'date',
a.id AS 'id',
SUM(t.debit_amount - t.credit_amount) AS 'balance'
FROM calendar cdr
JOIN transactions t ON (cdr.datefield >= t.value_date)
JOIN accounts a ON (a.id = t.account_id)
WHERE cdr.datefield IN ('2014-03-31', '2013-03-31', '2012-03-31')
GROUP BY cdr.datefield, a.id
WITH ROLLUP) BB USING (id) WHERE AA.date IS NOT NULL
ORDER BY BB.date DESC,AA.id;

I have used WITH ROLLUP to answer many posts in the DBA StackExchange

Jun 20, 2014 : Get only overall summary WITH ROLLUP and GROUP BY for multiple fields
Aug 12, 2014 : Fetch data from same table using two group by clauses in mysql
Apr 11, 2014 : Why is MySQL not using the index with the higher cardinality?
Sep 21, 2014 : Monthly report by time
Jul 31, 2013 : TokuDB database size unknown in phpmyadmin
Jul 10, 2013 : How to estimate/predict data size and index size of a table in MySQL
Jul 03, 2013 : Information about Disk Storage MySQL
Apr 25, 2013 : WITH ROLLUP WHERE x IS NULL

Give it a Try !!!


PostgreSQL Update and Delete are not (logically) replicated while having REPLICA IDENTITY

Add a primary key constraint on gid on the replica so that rows can be identified.
The default value for REPLICA IDENTITY of a table is documented with ALTER TABLE:

This option has no effect except when logical replication is in use. DEFAULT (the default for non-system tables) records the old values of the columns of the primary key, if any. USING INDEX records the old values of the columns covered by the named index, which must be unique, not partial, not deferrable, and include only columns marked NOT NULL. FULL records the old values of all columns in the row. NOTHING records no information about the old row.

That is, the default setting logs enough data under the assumption that the replica has the same primary key as the source table.
If you don't have a primary key or unique index on the table, you could use REPLICA IDENTITY FULL so that a row is identified by all its columns, but if you have duplicate rows, you will still get in trouble (deleting one of them on the primary will delete all on the standby). I don't think that is a smart thing to do, and all tables should have a primary key.


AlwaysOn Architecture - SQL Server 2012 for creating a dev environment?

As Aaron Bertrand mentioned in his comment, AlwaysOn is only designed to support offloading of read-only operations, in addition to its (really neat) redundancy capabilities.

If all you're looking for is a place where your developers can check out the production data without putting load on the production server, then perhaps it's a technology that might be useful - but you can achieve pretty much the same thing with log shipping.

You can build out your dev environment from a standby server - whether it uses AlwaysOn, Mirroring or Log Shipping - by using a Storage Appliance that supports writeable snapshots, or a server that can present writeable snapshots via iSCSI (e.g. a ZFS server). However, you'll probably need extra servers for this, some latitude to mess around with your storage configuration, and possibly someone's shoulder to cry on while you're trying to set it up.

The little "Cloning" video on NetApp's SnapManager website provides a useful overview of how this works - unfortunately I can't seem to find a nice description that's not vendor-specific.


Is there an upper limit to the SqlDbType.Structured ADO.NET type?

There is no inherent limit to the size of a table-valued parameter.  However, you could run into client memory constraints if you pass the value without streaming, such as using a DataTable or IEnumerable<SqlDataRecord> (without a stream source implementation) instead of a DataReader.

On the server side, the limit is tempdb storage.

2K rows won't be a problem as I've passed millions of rows without issues. One consideration, though, is to be sure to specify the MaxLength for string columns with a DataTable value. See my blog post SQL Server TVP Performance Gotchas.


Oracle Custom Sequence of a partitioned group (Insert trigger)

Not Possible

Your suggested Implementation of your Business Requirement is not possible.

You cannot select from table T1 from within a trigger on T1.  There are hacks around such limitation.  But, that is what they are: hacks.  One of them has a side effect of preventing you from performing a rollback.

In all cases: you'll still want to define a sequence for group_id.

create sequence group_id_seq;

Work Around - Correct your schema design

If you want to ensure that each Unique group_name contains a Unique group_id, then you need to create a table that enforces such data logic.

create table unique_groups (
   group_id    int generate by default on null as identity
  ,group_name  varchar2(50) not null
  ,constraint unique_group_pk primary key (group_id)
  ,constraint unique_group_uq1 unique (group_name)
);

alter table t1
  add constraint t1_fk1 foreign key (group_id) references unique_group( group_id );
alter table t1
  add constraint t1_fk2 foreign key (group_name) references unique_group( group_name );

Then, and only then, you can use a trigger.  (I'd still not advise it.)

create or replace
trigger t1_tr
before insert
on t1
for each row
begin
  select a.group_id into :new.group_id
  from unique_groups a
  where a.group_name = :new.group_name;
exception
  when no_data_found then
    insert into unique_groups (group_name)
    values (:new.group_name)
    returning group_id into :new.group_id;
end;
/

Work Around - TAPI

One work is the use of Table APIs (TAPI).

This is a Package that performs all necessary tasks to perform an insert,update, or delete.  Instead of your application performing the DML, the application calls the TAPI to perform the DML.

If necessary, you can wrap the Package with a VIEW and an INSTEAD OF trigger.

Package Spec:

create or replace package t1_tapi_pkg as
    -- I like to customize known exceptions for the application.
    data_corruption_detected   exception;
    PRAGMA EXCEPTION_INIT (data_corruption_detected, -20000);

    table_in_use_try_again     exception;
    PRAGMA EXCEPTION_INIT (lock_timeout, -20001);

    lock_timeout               exception;
    PRAGMA EXCEPTION_INIT (lock_timeout, -54);  -- These are caught and re-raised as -20001

    function ins (
        group_name in t1.group_name%type
    ) return t1.group_id%type;

    -- update logic has not been defined.

    -- regular SELECT and DELETE statements can be used.

end;
/

Package Body:

create or replace
package body t1_tapi_pkg
as

    function ins (
        group_name in t1.group_name%type
    ) return t1.group_id%type as
        group_id   t1.group_id;
    begin
        -- You should change to "WAIT 3"
        lock table t1 in exclusive mode nowait;

        begin
            -- find existing
            select distinct a.group_id
                into ins.group_id
            from t1 a
            where a.group_name = ins.group_name;

            -- re-use that value
            insert into t1 a (group_name, group_id)
            values ( ins.group_name, ins.group_id );

        exception
            when no_data_found then
                -- create a new unique value
                insert into t1 a (group_name, group_id)
                values ( ins.group_name, group_id_seq.nextval )
                returning a.group_id
                    into ins.group_id;
            when too_many_rows
                -- the GROUP_NAME does not have a unique GROUP_ID
                then raise data_corruption_detected;
        end;

        return ins.group_id;
    exception
        -- not necessary.  I like to customize my exceptions.
        when lock_timeout then
            raise table_in_use_try_again;
    end;
end;
/


Unless the users is mapped to the sysadmin fixed server role (where they will connect to the database as the user dbo) then they can be limited by security on the database.  (i.e. A member of db_owner will receive a security check where dbo will not.)

A quick test to see if it is a permissions issue is to temporarily grant a user sysadmin role and then see if they can delete data....  if not then I'd start looking for constraints on tables.

If they can then run the following against the database (you may get more you may need to add DELETE to your where clause):

select * from sys.database_permissions
where state_desc = 'DENY'

If permissions are set it will give you something like:

class class_desc                                                   major_id    minor_id    grantee_principal_id grantor_principal_id type permission_name                                                                                state state_desc
----- ------------------------------------------------------------ ----------- ----------- -------------------- -------------------- ---- -------------------------------------------------------------------------------------------------------------------------------- ----- ------------------------------------------------------------
0     DATABASE                                                     0           0           10                   1                    DL   DELETE                                                                                D     DENY
1     OBJECT_OR_COLUMN                                             1501607433  0           10                   1                    DL   DELETE                                                                                D     DENY

(2 row(s) affected)

If deny delete is set at the DATABASE level then well then there is your problem.  Adjust permissions so required users are not actually DENYed access.


SQL Server transaction replication agent error

Note: This answer is based on OP's comment under the question. So credit goes to them for finding the solution.

Faced this issue my self, which is how I ended up here.
Like @PMSawyer said, there was a negative value in the run_duration column of sys.jobhistory for the Agent job.

The following query can be used to find if there are any negative values.

  SELECT * FROM msdb.dbo.sysjobhistory
  WHERE job_id = (
  SELECT job_id FROM msdb.dbo.sysjobs
  WHERE name = 'INPUT_YOUR_AGENT_JOB_NAME_HERE'
  ) AND run_duration < 0
  ORDER BY run_date DESC

If this query produces an output, it is very likely that that is what is  causing the problem. The negative value will need to be removed by purging the agent job's history, the following query can be used for that.

  msdb.dbo.sp_purge_jobhistory
  @job_name = 'INPUT_YOUR_AGENT_JOB_NAME_HERE'

The output would look something like: 12 history entries purged..

Purging the job history doesn't affect replication in any way, in case that's a concern.

Also, in my case the reason this negative value came to be was that the server time had changed in accordance with Day Light Savings. The server clock and moved an hour behind.

On an unrelated note, I don't know fancy techniques like linking to OP's comment that provided the basis for this answer, if anyone cares enough to do that, it'd be great.


DB2 Transaction Logs - can they be COPY-PASTE on another Disaster Recovery Server?

Perhaps you wish to look into HADR on DB2. It actually does log shipping to the standby, so if there is a fail over you can either manually take over on the standby, or if you install TSA (Tivoli Systems Automation), then you can have it detect a failure and automatically fail over to the standby.

If you don't want HADR, then yes, you could copy logs. Or even use the alternate/mirror log/archive parameters to get DB2 to copy the logs (possibly to a shared mount).


how to check which background process are running in my oracle database?

17:31:15 SYSTEM@sandbox> ed
Wrote file S:\spool\sandbox\BUFFER_SYSTEM_34.sql

  1  select sid,  process, program
  2    from v$session s join v$bgprocess using (paddr)
  3   where s.status = 'ACTIVE'
  4*    and rownum < 5
17:31:21   5  /

       SID PROCESS                  PROGRAM
---------- ------------------------ ----------------------------------------------------------------
         2 1332                     ORACLE.EXE (PMON)
         3 480                      ORACLE.EXE (PSP0)
         4 976                      ORACLE.EXE (VKTM)
         5 992                      ORACLE.EXE (GEN0)

Elapsed: 00:00:00.05

However, this doesn't make much sense as they're always stated as active, so I guess you should define what you mean saying active.


How to query and group based on matched multiple conditions?

select      name
           ,mainID

           ,decode
            (
                "3" || "12" || "19"

               ,'ABC' ,'X'
               ,'AB'  ,'YY'
               ,'BC'  ,'Y'
               ,'AC'  ,'YYY'
               ,'B'   ,'Z'
               ,'A'   ,'V'
               ,'C'   ,'Q'
            )               as subID

from        mytable pivot (max(decode(subID,3,'A',12,'B',19,'C')) for subID in (3,12,19))

order by    name
;

select      name
           ,mainID

           ,decode
            (
                "3" || "12" || "19"

               ,'ABC' ,'X'
               ,'AB'  ,'YY'
               ,'BC'  ,'Y'
               ,'AC'  ,'YYY'
               ,'B'   ,'Z'
               ,'A'   ,'V'
               ,'C'   ,'Q'
            )               as subID

from        (select name,mainID,subID from ...) t
                 pivot (max(decode(subID,3,'A',12,'B',19,'C')) for subID in (3,12,19))

order by    name


mySQL Quickly find rows linked to another row

Presumably, any given user can only like a given post once, so the likes table does not need and should not have an id column... Instead, define that table with PRIMARY KEY (post_id,user_id).  The likes for a given post will be quickly retrievable using the leftmost column of the primary key for lookups and you then have automatic prevention of duplicate likes for the same post by the same user as a secondary benefit.


Many-to-many relationship when one entity is without attributes

The 'Right' thing to do depends.

If there's any chance the cluster might end up having attributes in the future, then yes you should have a join table.

Otherwise, if you are trying hard to normalize your data, then you should have a join table, if you are more concerned about minor size/performance boosts then you could skip the extra table. Personally, since I don't have a time machine to see the future, I'd make the extra table anyway.


User created automatically because of AD Group?

This is expected / documented behavior as noted in the documentation for CREATE SCHEMA (in the Remarks section):

  Implicit Schema and User Creation

  In some cases a user can use a database without having a database user account (a database principal in the database). This can happen in the following situations:

  A login has CONTROL SERVER privileges.
  A Windows user does not have an individual database user account (a database principal in the database), but accesses a database as a member of a Windows group which has a database user account (a database principal for the Windows group).

  When a user without a database user account creates an object without specifying an existing schema, a database principal and default schema will be automatically created in the database for that user. The created database principal and schema will have the same name as the name that user used when connecting to SQL Server (the SQL Server authentication login name or the Windows user name).

  This behavior is necessary to allow users that are based on Windows groups to create and own objects. However it can result in the unintentional creation of schemas and users. To avoid implicitly creating users and schemas, whenever possible explicitly create database principals and assign a default schema. Or explicitly state an existing schema when creating objects in a database, using two or three-part object names.

I emphasized part of that final statement: you / they need to specify a schema name when creating objects (i.e. CREATE TABLE Sales.TableName instead of CREATE TABLE TableName). The schema name that you specify does not need to be dbo, it just needs to already exist.


What does a round relationship icon mean in MySQL Workbench Visual Data Model

The icon shows that that side of the relationship is not mandatory, meaning the field can be NULL.
You can change that if you click on the relationship line, go to Foreign Key tab and select the Mandatory check box.


Disable Oracle auditing for a particular user?

There is no "audit..." command option to exclude selected users.

you can do it by enabling audit for all other users, example:

audit update table, delete table,
      insert table by TESTUSER by access;


if this is your exact code, you forgot to put a space at the end of this line:

FROM [RFD_New].[dbo].[RfD_]" & _


As documented in the manual the first "column" of the pg_hba.conf must be one of local, host, hostssl, hostnossl, hostgssenc or hostnogssenc. You can't put an IP address into the first column, it goes into the 4th column (if the first is not local).
The database needs to be put into the third column, then the username, then the IP address.
So this line:
::1     vmdb_production root            trust

should be:
host   vmdb_production   root  ::1  trust

This assumes you do have a Postgres user root which would be highly unusual. It might even indicate that you are trying to run that software under root which is a really, really bad idea (espcially in combination with trust)
But the root cause, why Postgres isn't starting, is the invalid entry in pg_hba.conf


Does a unique non-clustered index help with locks?

Theoretically yes, practically no.

If SQL Server can guarantee that a field is unique, then it can build execution plans differently. Rob Farley blogged an example with AdventureWorks where the unique index isn't even shown in the execution plan, and yet its existence let SQL Server build a better plan.

If your data really does qualify for a unique index, then specifying it as unique will ensure that SQL Server always estimates 1 row to come back when you search for one value. That'll mean you're more likely to get more granular locks instead of table locks.

However, if you're worried about lock escalation, it's probably because you're not searching for equality. You're joining to other tables, or using range searches. In those cases, the uniqueness of an index isn't going to save you.


How to create a column with rows in postgres?

You could try adding a json array column (json[]), but it depends on your access patterns  what the best column type would be.

Example:

ALTER TABLE tableName
ADD COLUMN timeTableRows json[];

And then:

INSERT INTO tableName
(
    timeTableRows
)
VALUES
( array['{
   "type": "DEPARTURE",
   "scheduledTime": "2020-01-26T04:57:00.000Z",
   "causes": []
 }',
 '{
   "type": "ARRIVAL",
   "scheduledTime": "2020-01-26T05:02:00.000Z",
   "causes": []
 }']::json[])


Which error codes are safe for `slave_skip_errors` with MySQL Innodb

To get the ball rolling, let us start with the manual

option_mysqld_slave-skip-errors

  MySQL 5.7 supports an additional shorthand value ddl_exist_errors,
  which is equivalent to the error code list
  1007,1008,1050,1051,1054,1060,1061,1068,1094,1146.

Which includes:

Error: 1007 SQLSTATE: HY000 (ER_DB_CREATE_EXISTS)

  Message: Can't create database '%s'; database exists
  An attempt to create a database failed because the database already exists.
  Drop the database first if you really want to replace an existing database, or add an IF NOT EXISTS clause to the CREATE DATABASE statement if to retain an existing database without having the statement produce an error.

Error: 1008 SQLSTATE: HY000 (ER_DB_DROP_EXISTS)

  Message: Can't drop database '%s'; database doesn't exist

Error: 1050 SQLSTATE: 42S01 (ER_TABLE_EXISTS_ERROR)

  Message: Table '%s' already exists

Error: 1051 SQLSTATE: 42S02 (ER_BAD_TABLE_ERROR)

  Message: Unknown table '%s'

Error: 1054 SQLSTATE: 42S22 (ER_BAD_FIELD_ERROR)

  Message: Unknown column '%s' in '%s'

Error: 1060 SQLSTATE: 42S21 (ER_DUP_FIELDNAME)

  Message: Duplicate column name '%s'

Error: 1061 SQLSTATE: 42000 (ER_DUP_KEYNAME)

  Message: Duplicate key name '%s'

Error: 1068 SQLSTATE: 42000 (ER_MULTIPLE_PRI_KEY)

  Message: Multiple primary key defined

Error: 1094 SQLSTATE: HY000 (ER_NO_SUCH_THREAD)

  Message: Unknown thread id: %lu

Error: 1146 SQLSTATE: 42S02 (ER_NO_SUCH_TABLE)

  Message: Table '%s.%s' doesn't exist

But in support of Dave's comment:

  Normally, replication stops when an error occurs on the slave, which
  gives you the opportunity to resolve the inconsistency in the data
  manually. This option causes the slave SQL thread to continue
  replication when a statement returns any of the errors listed in the
  option value.

  Do not use this option unless you fully understand why you are getting
  errors. If there are no bugs in your replication setup and client
  programs, and no bugs in MySQL itself, an error that stops replication
  should never occur. Indiscriminate use of this option results in
  slaves becoming hopelessly out of synchrony with the master, with you
  having no idea why this has occurred.


How to improve estimate of 1 row in a View constrained by DateAdd() against an index

A less comprehensive answer than Aaron's but the core issue is a cardinality estimation bug with DATEADD when using the datetime2 type:
Connect: Incorrect estimate when sysdatetime appear in a dateadd() expression
One workaround is to use GETUTCDATE (which returns datetime):
WHERE CreatedUtc > CONVERT(datetime2(7), DATEADD(DAY, -365, GETUTCDATE()))

Note the conversion to datetime2 must be outside the DATEADD to avoid the bug.
An incorrect cardinality estimation reproduces for me in all versions of SQL Server up to and including 2019 CU8 GDR (build 15.0.4083) when the 70 model cardinality estimator is used.
Aaron Bertrand has written an article about this for SQLPerformance.com:

Performance Surprises and Assumptions : DATEADD()


What's the best way to maintain a writable DB slave which is always overwritten by the master?

You could use symmetricds to perform the bidirectional replication. It allows for conflict management in which you can define which side wins in the conflict in your case the master.


Get a list of orders by excluding the recents one in the same category?

You can speed up the query by adding an index as follows:

ALTER TABLE `orders` ADD KEY ix_executed(executed,category_id);

Create table orders as follows:

CREATE TABLE `orders` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `name` varchar(250) NOT NULL,
  `executed` timestamp NULL DEFAULT '0000-00-00 00:00:00',
  `category_id` tinyint(3) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_executed` (`executed`,`category_id`)
) ENGINE=InnoDB;

Rest of your query seems good to me.


How to increase index row maximum size in Postgresql?

That would require significant changes, and I doubt it can be done easily.

See these excerpts from src/include/access/itup.h:

/*
 * Index tuple header structure
 *
 * All index tuples start with IndexTupleData.  If the HasNulls bit is set,
 * this is followed by an IndexAttributeBitMapData.  The index attribute
 * values follow, beginning at a MAXALIGN boundary.
 *
 * Note that the space allocated for the bitmap does not vary with the number
 * of attributes; that is because we don't have room to store the number of
 * attributes in the header.  Given the MAXALIGN constraint there's no space
 * savings to be had anyway, for usual values of INDEX_MAX_KEYS.
 */

typedef struct IndexTupleData
{
    ItemPointerData t_tid;      /* reference TID to heap tuple */

    /* ---------------
     * t_info is laid out in the following fashion:
     *
     * 15th (high) bit: has nulls
     * 14th bit: has var-width attributes
     * 13th bit: AM-defined meaning
     * 12-0 bit: size of tuple
     * ---------------
     */

    unsigned short t_info;      /* various info about tuple */

} IndexTupleData;               /* MORE DATA FOLLOWS AT END OF STRUCT */
[...]

/*
 * t_info manipulation macros
 */
#define INDEX_SIZE_MASK 0x1FFF
#define INDEX_AM_RESERVED_BIT 0x2000    /* reserved for index-AM specific
                                         * usage */
#define INDEX_VAR_MASK  0x4000
#define INDEX_NULL_MASK 0x8000

The limit you are hitting is INDEX_SIZE_MASK, and to increase it, you'd have to change the tuple header so that t_info has more than two bytes.

Perhaps it is as simple as that, but it might have repercussions in other parts of the code.


If the application is obfuscating the error message, try connecting with a Powershell script.  Perhaps this will provide more information about the cause of the connection error.  Even if you don't have the Native Client installed, the legacy SQLOLEDB provider that ships with Windows should still work:

$connection = New-Object -ComObject ADODB.Connection;

Write-Output "Connecting using provider SQLOLEDB";
$connection.Open("Provider=SQLOLEDB; Data Source=localhost\SQLEXPRESS; Initial Catalog=Calendar; User Id=userName; Password=password");

Write-Output "Connecting using provider SQLNCLI11.1";
$connection.Open("Provider=SQLNCLI11.1; Data Source=localhost\SQLEXPRESS; Initial Catalog=Calendar; User Id=userName; Password=password");

To use SQL Server Native Client, check to ensure SQL Server 2012 Native Client is listed as an installed program.  It can be downloaded and installed from the Feature Pack downloads: https://www.microsoft.com/en-us/download/confirmation.aspx?id=29065.


How to GRANT object permission to a user defined server role?

To access all databases you should give the CONNECT ANY DATABASE permission.

To view all the definitions you should grant the VIEW ANY DEFINITION permission.

To select all data from all user tables you can grant SELECT ALL USER SECURABLES starting with SQL Server 2014, but it will permit you to select data only from all accessible databases, so without CONNECT ANY DATABASE it gives you nothing.

So, even if you have SQL Server 2014 and higher, you should give all three permissions to accomplish what you want:

CONNECT ANY DATABASE
VIEW ANY DEFINITION
SELECT ALL USER SECURABLES

There is no need to grant VIEW ANY DATABASE as it's already granted to public.


Speed up 180M+ rows returned by simple SELECT with PostgreSQL

As you mentioned you are trying to get 80% of the rows of your table and it is normal that postgreSQL optimizer decide to scan the table rather than get data from the index. Unfortunately postgres does not support queries with index hints like MS SQL server, so it is not possible to compare the results of index scan vs sequential scan but I am absolutely sure that you will not get better results with index scan.

Another question is why you need 180mln rows? What do you want to do with this data?
I assume that this is an analytical query so you can try some column store technologies.
Postgres have cstore_fwd extension available so you can try it out.


How to monitor activity in SSMS only with T-SQL?

Here's a few popular ways to do it:

sp_WhoIsActive

Pretty much the industry standard tool from Adam Machanic. Download it from WhoIsActive.com, install it in the master database (so you can call it from anywhere), and then run it whenever you want to know what's running now. Here's what it looks like:

Queries are sorted from longest-running to shortest. You can click on the sql_text or query_plan to see that stuff (note that the latter is only included when you run it with @get_plans = 1). It'll also show you who's blocking who, what machine the queries are coming from, and much more.

It's free, and it works with all currently supported versions of SQL Server. It's used by thousands of database professionals all over the world. I've used it myself in production all over the place, and never had a problem with it.

sp_BlitzWho

Erik Darling wrote this because he's really into query tuning, and he wanted to see more details than sp_WhoIsActive provides. Starting with SQL Server 2012, Microsoft has added a ton of cool DMV info like memory grant utilization, and Adam hasn't been updating sp_WhoIsActive to leverage it. You can get sp_BlitzWho from the public Github repo or from our First Responder Kit. Here's what it looks like:

At first, it basically looks like sp_WhoIsActive, but here's some of the additional columns it shows:

Parallelism - last_dop, min_dop, max_dop, last_reserved_threads, min_reserved_threads, last_used_threads, and more
Memory grants - last_grant_kb, min_grant_kb, max_grant_kb, last_used_grant_kb, min_used_grant_kb, max_used_grant_kb, last_ideal_grant_kb, and more
Resource Governor - workload_group_name, resource_pool_name

The data can be pretty overwhelming - there's a lot of it - so start with Adam's sp_WhoIsActive, and only graduate to sp_BlitzWho if you need to do live query tuning.

sp_BlitzFirst

I wrote sp_BlitzFirst to go a little deeper and ask, "WHY is the server slow right now?" It takes a snapshot of a bunch of dynamic management objects, waits 5 seconds, takes another snapshot, and compares the difference between the two to figure out what's happening now. You can get it from our site or from the public Github repo. Here's what it looks like:

It gives you a prioritized list of server issues, and URLs to copy/paste into your browser to learn more about each problem. For example, in the screenshot above, the server's slowing down because some meatball is running a backup right now. You can click on the "ClickToSeeDetails" line to see, uh, details:

Backup of StackOverflow database (93GB) is 0.896017% complete,
has been running since Sep 22 2017  5:19AM.
Login: Brent
Over the last 60 days, the full backup usually takes 4 minutes.

This way, you can give this tool to less-experienced folks like your help desk and have them do some initial triage.


sql server 2014 - Find gaps in sequence on 2 different fields group by third field

You can use CROSS APPLY to "unpivot" multiple columns. This query will give you a list of orders (unpivoted). I've added a third column, which is the LEADing (i.e. next) OrderID, if there is any:

SELECT o.SellerID, x.OrderID,
       LEAD(x.OrderID, 1) OVER (
           PARTITION BY o.SellerID
           ORDER BY x.OrderID) AS _nextOrderID
FROM Orders AS o
CROSS APPLY (
    SELECT o.OrderID
    UNION ALL
    SELECT o.OID AS OrderID WHERE o.OID IS NOT NULL
    ) AS x(OrderID);

This gives you:

SellerID   OrderID     _nextOrderID
---------- ----------- ------------
seller1    123453      123455
seller1    123455      123456
seller1    123456      123457
seller1    123457      123458
seller1    123458      123459
seller1    123459      123460
seller1    123460      123460
seller1    123460      123463
seller1    123463      123466
seller1    123466      123470
seller1    123470      NULL
seller2    234567      234568
seller2    234568      234569
seller2    234569      234570
seller2    234570      234570
seller2    234570      234572
seller2    234572      NULL

Now, if we subtract the OrderID from the _nextOrderID, we get the gap (how many orders are "missing"):

SELECT o.SellerID, x.OrderID,
       LEAD(x.OrderID, 1) OVER (
           PARTITION BY o.SellerID
           ORDER BY x.OrderID)-x.OrderID AS _gap
FROM Orders AS o
CROSS APPLY (
    SELECT o.OrderID
    UNION ALL
    SELECT o.OID AS OrderID WHERE o.OID IS NOT NULL
    ) AS x(OrderID);

.. which looks something like this:

SellerID   OrderID     _gap
---------- ----------- -----------
seller1    123453      2
seller1    123455      1
seller1    123456      1
seller1    123457      1
seller1    123458      1
seller1    123459      1
seller1    123460      0    <- 0 because you have two OrderID=123460
seller1    123460      3
seller1    123463      3
seller1    123466      4
seller1    123470      NULL
seller2    234567      1
seller2    234568      1
seller2    234569      1
seller2    234570      0
seller2    234570      2
seller2    234572      NULL

So where _gap<=1, we don't want to return any rows. Where _gap=4, we want to insert 3 rows, from OrderID+1 to OrderID+3. There are a few ways to do this, but I'm going to stick with CROSS APPLY here as well.

I'm putting the query above in a subquery (called sub in my example), and for each row in that result, I'm going to CROSS APPLY any dummy table. If you expect large gaps, you may want to create a separate table with a single IDENTITY column for this purpose, but I'm just going to use the Orders table along with a ROW_NUMBER():

SELECT sub.SellerID, sub.OrderID+n.rownum AS OrderID
FROM (
    SELECT o.SellerID, x.OrderID,
           LEAD(x.OrderID, 1) OVER (
               PARTITION BY o.SellerID
               ORDER BY x.OrderID)-x.OrderID AS _gap
    FROM Orders AS o
    CROSS APPLY (
        SELECT o.OrderID
        UNION ALL
        SELECT o.OID AS OrderID WHERE o.OID IS NOT NULL
        ) AS x(OrderID)
    ) AS sub
CROSS APPLY (
    --- For each row in sub, where _gap>1, return
    --- (_gap) number of rows, starting with 1, 2, 3, ..., (_gap-1).
    SELECT TOP (sub._gap-1)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rownum
    FROM Orders    -- or any dummy table
    ) AS n
--- Only where there's actually a gap:
WHERE sub._gap>1;

Finally, at the top, we add n.rownum to sub.OrderID to get the missing OrderID.

Here's the final output:

SellerID   OrderID
---------- --------------------
seller1    123454
seller1    123461
seller1    123462
seller1    123464
seller1    123465
seller1    123467
seller1    123468
seller1    123469
seller2    234571


In theory, absolutely. In practice, it depends and most likely not.

Data warehouses will be a lot more likely to do full scans and disk reads/writes due to the nature of a DW.  Optimizing a DW can be very important to get reports out in time, saving money in not buying expensive disks if not needed, and also to reduce wear/tear on the machines doing too many IOPS trying to get the same data.  They will often do a big (or a few) table scan(s) and get all the relevant data which pushes a lot of items out to disk.

A lot of OLTP can be ran out of memory in a relatively managed environment where bad ad hoc queries aren't causing memory buffer issues.  If for some terrible reason your OLTP environment is doing a lot of table scans, you see memory buffer pool pressure from table data, with high disk read/sec and disk write/sec metrics in perfmon, AND you're not able to fix the code, this would be helpful.

Note though with customization comes overhead.  The additional complexity have to be properly documented, perhaps automated, and maintained.  If you don't see any benefits to it then why introduce additional complexity?


Securing Always Encrypted SQL Server from "sa"

The column encryption key (CEK) is used to encrypt the data and is stored in the database. The CEK is secured using  a column master key (CMK).

The CMK is stored outside of SQL Server. Metadata about the key is stored within SQL Server.

If you restrict access to the CMK your database administrators / system administrators (sa) will not have the ability to decrypt.


PAL Report - 64-bit Windows Server 2008 R2, issues

Less than 5 percent of RAM is available or less than 64 MB of RAM is available

  Less than 500 MB of free disk space

  (Total Latch Wait Time) / (Latch Waits/ Sec) < 10

Looking at first two alerts it says that SQL Server might be facing memory crunch. It would be better to determine using perfmon counters. I use below counters to check how much memory SQL Server needs. Taken from Here

SQLServer:Buffer Manager--Buffer Cache hit ratio(BCHR):

If your BCHR is high 90 to 100 Then it points to fact that You don't have memory pressure. Keep in mind that suppose somebody runs a query which request large amount of pages in that case momentarily BCHR might come down to 60 or 70 may be less but that does not means it is a memory pressure it means your query requires large memory and will take it. After that query completes you will see BCHR risiing again

SQLServer:Buffer Manager--Page Life Expectancy(PLE):

PLE shows for how long page remain in buffer pool. The longer it stays the better it is. Its common misconception to take 300 as a baseline for PLE.   But it is not,I read it from Jonathan Kehayias book( troubleshooting SQL Server) that this value was baseline when SQL Server was of 2000 version and max RAM one could see was from 4-6 G. Now with 200G or RAM coming into picture this value is not correct. He also gave the formula( tentative) how to calculate it. Take the base counter value of 300 presented by most resources, and then determine a multiple of this value based on the configured buffer cache size, which is the 'max server memory' sp_ configure option in SQL Server, divided by 4 GB.   So, for a server with 32 GB allocated to the buffer pool, the PLE value should be at least (32/4)*300 = 2400. So far this has done good to me so I would recommend you to use it.

SQLServer:Buffer Manager--CheckpointPages/sec:

Checkpoint pages /sec counter is important to know about memory pressure because if buffer cache is low then lots of new pages needs to be brought into and flushed out from buffer pool,  due to load checkpoint's work will increase and will start flushing out dirty pages very frequently. If this counter is high then your SQL Server buffer pool is not able to cope up with requests coming and we need to increase it by increasing buffer pool memory or by increasing physical RAM and then making adequate changes in Buffer pool size. Technically this value should be low if you are looking at line graph in perfmon this value should always touch base for stable system.

SQLServer:Buffer Manager--Freepages:

This value should not be less you always want to see high value for it.

SQLServer:Memory Manager--Memory Grants Pending:

If you see memory grants pending in buffer pool your server is facing SQL Server memory crunch and increasing memory would be a good idea. For memory grants please read this article: http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx.

SQLServer:memory Manager--Target Server Memory:

This is amount of memory SQL Server is trying to acquire.

SQLServer:memory Manager--Total Server memory

This is current memory SQL Server has acquired.

Few Points

1.If Target server memory is greater than Total server memory there can be memory pressure. Let me put emphasis on word can be ,it is not a sure shot signal.Please refer to this MSDN forum thread where OP had target server memory greater than total server memory but because there were no memory grants pending ,and page life expectancy was high so there was no memory pressure .

Generally on stable system these 2 values are equal.
Free Pages counter is removed from SQL Server 2012. And also its value does not holds importance as the values for BCHR,PLE,Target server memory and Total Server  memory

The last one is number of latches which could not be granted immediately. Are you seeing queries going to suspended state. Latches are common and necessary they are lighter form of locks. Did you checked top waits does they include latch_XX as top wait types


Pros for Single table:
Fewer tables therefore less maintenance. If a record could change from inbound to outbound you just need to change the indicator.

Cons for single table:
There's a bit more effort in code logic.
You can't use foreign key constraints on one type versus another.

Pros for separate tables:
Logically easier to comprehend and code.
Simple way of splitting large tables if volumes are large.
Allows for different attributes and table definitions.
Allows for different foreign keys for each type. (I have to give @ypercube credit for this)

Cons for separate tables:
If a shipment is in transit moving from inbound to outbound it could fall between tables. (Less likely with single table)
More tables/indexes to maintain.


How can it be possible that sum operation is so much faster than count?

Typically, there is not much difference between sum() and count(), the number of data pages that have to be read are the dominant factor for performance, but count() is generally faster, especially count(*):

For absolute performance, is SUM faster or COUNT?

Your sum uses Parallel Seq Scan, which turns out to be much faster. The count, on the other hand uses an Index Only Scan, which is typically the fastest way, but no Parallel support there. That's what probably makes the difference. (May be a limitation of pg 9.5, not sure.)

You are using Postgres 9.5. Parallel queries were pretty new and the cost estimation not too accurate - similar considerations apply for index-only scans, to a lesser degree. So the planner makes at least one bad cost estimate: 489270.15 for the count with and Index Only Scan, vs. 658027.40 for the sum - for which an index-only scan is probably not possible (no index covering amount and exchange). Maybe you also have cost settings that don't reflect reality well, which is typically involved in bad estimates. See:

Keep PostgreSQL from sometimes choosing a bad query plan

And I see Heap Fetches: 4085161 for the count, which strikes me as surprisingly high for an index-only scan on 11M rows. Not sure why that is, maybe a VACUUM ANALYZE on the table might change things.

All that aside, while t.id is defined NOT NULL, use count(*) instead of count(t.id), which is a bit faster, since Postgres does not have to look into the stored (index) tuple at all. The mere existence of rows is sufficient.

And upgrade to a current version of Postgres, where either variant will be substantially faster. There have been major improvements to big data and parallel queries.

BTW, if the count does not have to be exact, there are much faster options:

Fast way to discover the row count of a table in PostgreSQL


What is the "Default Timeout" value in SQLite connection string used for?

I found this out by looking at the source code: The timeout value of a command is initialized to DefaultTimeout and passed to the internal SQLite3.Prepare function.

This is used to control a loop that automatically retries statements that fail with a Locked or Busy error.  When the timeout has expired (as measured by Environment.TickCount), then an exception is thrown with whatever the most recent error code was.  Otherwise, it sleeps for a random amount of time (between 1 and 150 ms) and tries again.


Aborted connection on an Update statement only

I sort of found the solution:
I tried running the same query directly on a mysql client, and the error was more enlightening: "MySQL error 1449: The user specified as a definer does not exist".

Which led me to this post:
https://stackoverflow.com/questions/10169960/mysql-error-1449-the-user-specified-as-a-definer-does-not-exist

I did apply the proposed solution to "Change the definer". However I was never able to make it work, as the client kept trying to reach the initial definer user (which no longer existed). It seems the user credentials were cached somewhere. So my only solution was to recreate the initial definer. Which stopped the error


Are there any reasons to avoid using sp_rename for renaming objects?

Update

sp_rename upates the name in sys.objects but it does not update the definition or objects referencing it.

Object definition

  Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. Therefore, we recommend that sp_rename not be used to rename these object types. Instead, drop and re-create the object with its new name. (MSDN)

When you run this query:

Create Procedure Test
as
begin
    set nocount on
end
Go
Exec sp_rename 'Test', 'Tests'
GO
Select * From sys.sql_modules
sp_helptext 'Tests'

The definition column in sql_modules still contains Test:

object_id   definition ...
599673184   Create Procedure Test as begin set nocount on Select id From T1 end

Rename a Table (or an object)

When an object is rename, any object using it and referencing the old name will fail.

Here are a View and a Table beining renamed:

Create Table T1(id int)
Go
Create View V1 AS Select id From dbo.T1
Go
Exec sp_rename 'T1', 'T2'

Again if you look at the definition, both the procedure and the view reference T1 (which does not exist anymore):

object_id   definition
759673754   Create Procedure Test as begin set nocount on Select id From T1 end
791673868   Create View V1 AS Select id From dbo.T1

And both will fail with this error when you Select from it or Execute it:

Invalid object name 'dbo.T1'.
Could not use view or function 'V1' because of binding errors.

You get the same error if you refresh the view:

sp_refreshview 'v1'

All objects with references to the old name will break.

Dependencies

Before renaming an object (table, procedure, function, ...), you can look at its dependencies using sys.sql_expression_dependencies (MSDN) (deprecated: sys.sql_dependencies MSDN, sys.sysdepends (MSDN)


How to add multiple rows to a table that contains only a generated always identity column?

One way is to use MERGE (best thing since sliced bread?).

MERGE INTO T x
USING (
    SELECT 1 FROM SRCTABLE
) y (dummy)
    ON 1=0 -- Always false
WHEN NOT MATCHED THEN
    INSERT (x) VALUES (DEFAULT);

This will add one row to T for every row in SRCTABLE.

It would be cool if we could grab the generated values with the use of transition tables, but it appears as if this does not work for MERGE (db2 10.5, have not checked 11 yet):

SELECT * FROM NEW TABLE (
    MERGE INTO T x
    USING (
        SELECT 1 FROM SRCTABLE
    ) y (dummy)
        ON 1=0 -- Always false
    WHEN NOT MATCHED THEN
        INSERT (x) VALUES (DEFAULT)
);

SQL0104N  An unexpected token "select * from new table (" was found
following "BEGIN-OF-STATEMENT".  Expected tokens may include:  "<space>".
SQLSTATE=42601

Compare with:

SELECT * FROM NEW TABLE (
    INSERT INTO T (x)
    VALUES (DEFAULT)
);


SQL> create table t1 (c1 clob) lob (c1) store as securefile c1_clob;

Table created.

SQL> select table_name, column_name, segment_name from user_lobs;

TABLE_NAME           COLUMN_NAME          SEGMENT_NAME
-------------------- -------------------- --------------------
T1                   C1                   C1_CLOB

SQL>

More details at: LOB_storage_clause


Best way to insert error information from catch block in the table for stored procedure?

The below is what i use to catch errors and send it of to the developers. It generates a mail every time an error occurs

BEGIN CATCH

(SELECT @Servername AS ServerName,

 ERROR_NUMBER() AS ErrorNumber

,ERROR_SEVERITY() AS ErrorSeverity

,ERROR_STATE() AS ErrorState

,ERROR_PROCEDURE() AS ErrorProcedure

,ERROR_MESSAGE() AS ErrorMessage

INTO #ERROR )

/*************************************************************/

/****************** HTML Preparation *************************/

/*************************************************************/

DECLARE @HTML  VARCHAR(MAX),

    @table VARCHAR(MAX)

 SET @HTML = --HTML layout--

            '' +

            '
Error Executing GetAllJobStatuses
' +

            '' +

            '' +

            '
' +

            '
'+

            'Server Name	' +

            'Error Number	' +

            'Error Severity	' +

            'Error State	' +

            'Error Procedure	' +

            'Error Message
';

 SELECT @table = CONVERT(nvarchar(max) ,

(SELECT

 td = ServerName, '',

 td = CAST(ErrorNumber as varchar(30)), '',

 td = CAST(ErrorSeverity as varchar(30)), '',

 td = CAST(ErrorState as varchar(30)), '',

 td = ISNULL(CONVERT(VARCHAR(50),[ErrorProcedure] ),''), '',

 td = ErrorMessage, ''

 FROM

#ERROR

FOR XML PATH(N'tr'), TYPE));

SET @HTML = @HTML + @table + CHAR(10) +

         N'
';

SET @HTML = @HTML + '

End of Report
';

--PRINT @HTML

Declare @MailSubject varchar(100)

SET @MailSubject = 'Error Executing GetAllJobStatuses!! - ' +
DATENAME(weekday, getdate())

IF LEN(@HTML) > 10

EXEC msdb.dbo.sp_send_dbmail

@recipients = 'tester@test.com',

@body_format =  'HTML',

@body= @HTML,

@subject = @MailSubject,

@profile_name = 'SQLDBA'

    --SELECT * FROM #ERROR

 DROP TABLE #ERROR

 END CATCH;


Indexes for SQL query with WHERE condition and GROUP BY

Another idea, that also uses the groups table and a construction called LATERAL join (for SQL-Server fans, this is almost identical to OUTER APPLY). It has the advantage that aggregates can be calculated in the subquery:

SELECT group_id, min_ts, max_ts
FROM   groups g,                    -- notice the comma here, is required
  LATERAL
       ( SELECT MIN(ts) AS min_ts,
                MAX(ts) AS max_ts
         FROM counter c
         WHERE c.group_id = g.group_id
           AND c.ts BETWEEN timestamp '2011-03-02 00:00:00'
                        AND timestamp '2013-03-05 12:00:00'
       ) x
WHERE min_ts IS NOT NULL ;

Test at SQL-Fiddle shows that the query does index scans on the (group_id, ts) index.

Similar plans are produced using 2 lateral joins, one for min and one for max and also with 2 inline correlated subqueries. They could also be used if you need to show the whole counter rows besides the min and max dates:

SELECT group_id,
       min_ts, min_ts_id,
       max_ts, max_ts_id
FROM   groups g
  , LATERAL
       ( SELECT ts AS min_ts, c.id AS min_ts_id
         FROM counter c
         WHERE c.group_id = g.group_id
           AND c.ts BETWEEN timestamp '2012-03-02 00:00:00'
                        AND timestamp '2014-03-05 12:00:00'
         ORDER BY ts ASC
         LIMIT 1
       ) xmin
  , LATERAL
       ( SELECT ts AS max_ts, c.id AS max_ts_id
         FROM counter c
         WHERE c.group_id = g.group_id
           AND c.ts BETWEEN timestamp '2012-03-02 00:00:00'
                        AND timestamp '2014-03-05 12:00:00'
         ORDER BY ts DESC
         LIMIT 1
       ) xmax
WHERE min_ts IS NOT NULL ;


Not sure if it will solve your issue but you can try something like this:

start /wait <CD or DVD Drive>\setup.exe /qn INSTANCENAME=<InstanceName> REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword>

See more at: https://technet.microsoft.com/en-us/library/ms144259(v=sql.90).aspx


How can I set up SQL Server 2008 users without Windows/Domain accounts?

The first part is to create a login for the new user, if you can't login to the server you can't perform queries on a database.

In MSSMS Expand the Security tab, right click on Login and select New User. Click the SQL Server authentication radio button and fill in the password for the new user. You will probably want to uncheck the box for "Enforce password expiration" and "User must change password at next login" if you are using the login from an application.

Click the User Mapping page in the left panel and map the new login to a database and give them the desired roles, click OK and you're done.

With all of that said there are two things you should be aware of.

Questions like this are better asked on https://serverfault.com/ or apparently http://dba.stackexchange.com :-)
It is still better to create windows / domain accounts and either run your application pool under that context or use impersonation.


MSSQL - Insert Into adding data into the middle of the table?

There is no "middle of the table" - a table is an unordered set of rows. Think of it as a bag of marbles - if you empty it on the floor, can you predict which marble will stop moving first, last, etc.?
If you don't use ORDER BY in your query, SQL Server is free to return the data in any order it chooses, and it will do based on what is most efficient. This won't necessarily align with order of insert, especially if you have a clustered index that is unrelated.
If you want a select to return a predictable order, use an ORDER BY (which may require adding a column like an identity column or an auto-generated datetime column.)
If you want predictable order without using ORDER BY, you are pretty much out of luck. You could use a naturally increasing key for your clustered index, which will make it more likely to observe that order, but SQL Server still doesn't have to return data in that sequence, and it could change at any time due to changes in indexes, stats, etc. See #3 here.
What you remember from all of your previous experiences probably involved a clustered index on an identity column, which is highly likely to exhibit that behavior without any other influencing factors.


Your options to have a good picture of the situation when everything is crawling:

use server side traces to see the longest and most heavy sessions
use the Who is Active stored procedure by Adam Machanic - to save the information of the moments when the server is loaded - fantastic free script
use the Activity Monitor from Management Studio just to get a visual glance (Cpu and IO are the ones that I found the most useful) - pretty good tool included in Management Studio
use a 3rd party free tool - Confio Ignite Free - that's very good for moments when everything is slow and need to see details about the wait stats, blocking..etc. - fantastic free tool
check statistics to be up to date
check if indexes are fragmented and, if so, defrag them
follow the other advices of checking missing indexes, design
check the possibility of using the snapshot isolation level in your database (you have high blocking so it's good to see if you really need your current isolation level)

And good luck investigating the problems :-).


SQL How to Convert Single Tenant Databases to a Multitenant Database

sql-server-2016
This can be made to work using SESSION_CONTEXT (new in SQL Server 2016). In previous versions CONTEXT_INFO could be used, but it is less robust (cannot be read-only, only one slot per session).
Example
First, set a DEFAULT on the underlying table's CustomerId column:
CREATE TABLE ReportingDB.[dbo].[CustomerTransaction]
(
    [CustomerTransactionid] [int] identity(1,1) primary key NOT NULL,
    [Customerid] [int] NOT NULL
        DEFAULT TRY_CONVERT(integer, SESSION_CONTEXT(N'CustomerID')),
    [QuantityBought] [int] NULL,
);

Then define the view as follows:
CREATE OR ALTER VIEW dbo.CustomerOneTransaction
AS
    SELECT
        CT.CustomerTransactionid,
        CT.Customerid,
        CT.QuantityBought
    FROM ReportingDB.dbo.CustomerTransaction AS CT
    WHERE
        CT.Customerid = 1
        AND TRY_CONVERT(integer, SESSION_CONTEXT(N'CustomerID')) = 1
    WITH CHECK OPTION;

Each session would need to set the CustomerID key once using sys.sp_set_session_context:
EXECUTE sys.sp_set_session_context
    @key = N'CustomerID',
    @value = 1;

If the key should be immutable for the life of the connection (typically true), use the @read_only option:
EXECUTE sys.sp_set_session_context
    @key = N'CustomerID',
    @value = 1,
    @read_only = 1;

This arrangement allows your insert query to work, and also ensures that CustomerID always matches the session key when the view is used to insert or update, otherwise a CHECK_OPTION error is returned. For deletion, illegal operations are ignored rather than raising an error.
This answer addresses the question asked, but in practice I would almost always prefer a database per customer, for security, manageability, and availability reasons.
Demo: db<>fiddle
Additional Information
You can also use this pattern with Row-Level Security to enforce customer isolation without views. Database-per-customer is the pattern used most for Software-as-a-Service companies (not banks and finance companies - the benefits of customer database isolation don't really make any sense for many other industries).

Multi-tenant applications with elastic database tools and row-level security - Microsoft Docs
SQL Server Databases in the Cloud – Single Tenant or Multi Tenant? - Dr Greg Low
Row Level Security Limitations, Performance and Troubleshooting - Aaron Bertrand


How to change database encoding in an Amazon RDS Postgresql instance?

You must pass a locale that matches your desired encoding and is supported by the host as well. For example, if you don't want localised collations etc, you could pass --locale=C. Otherwise you might want --locale=en_US.ISO-8859-1 (or just en_US).


This composite index may help:

INDEX(account_id, lastname)

When adding it, you may as well remove INDEX(account_id), since it will then be redundant.

This may be even better, but I am not sure:

INDEX(deleted, account_id, lastname)

However it does not supersede INDEX(account_id).


Looks like we're not getting any more information, so I'll go ahead and post a partial answer that might or might not be helpful to you. For future questions, including information about data types, indexes, how many rows the tables have ("quite large" means different things to different people), and your expected output can be very helpful for those of us trying to answer your question.

This answer contains a "set-based solution" that assumes that you always have at least twice as many rows in Table B compared to Table A for every age. This is well within your 20X estimate, but I do not meet the requirement of returning NULL if there aren't enough rows. With that said, that case will be obvious because the query will return NULLs for the UserId from Table B.

For the data set, I assumed that Table A had 1 million rows spread evenly over 100 different ages and Table B had 20 million rows spread evenly over 100 different ages. There are no duplicate UserIds in either table for the same age, but rows can share an UserIds for different ages in the same table. Neither table has any indexes. Code to create this data set:

DROP TABLE IF EXISTS dbo.TableA;
CREATE TABLE dbo.TableA (UserId BIGINT, Age TINYINT);

INSERT INTO dbo.TableA WITH (TABLOCK)
SELECT 1 + (t.RN % 10000)
, (t.RN - 1) / 10000
FROM
(
    SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t
OPTION (MAXDOP 1);

DROP TABLE IF EXISTS dbo.TableB;
CREATE TABLE dbo.TableB (UserId BIGINT, Age TINYINT);

INSERT INTO dbo.TableB WITH (TABLOCK)
SELECT 1 + (t.RN % 200000)
, (t.RN - 1) / 200000
FROM
(
    SELECT TOP (20000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
    CROSS JOIN master..spt_values t3
) t
OPTION (MAXDOP 1);

The algorithm for matching can be expressed in the following way:

Process rows for a single age at a time. Suppose that TableA has m rows for that age and TableB has n rows for that age. As stated before, n >= 2 * m.
Assign random positive integers to the rows from TableA from 1 to m.
Assign random positive integers to the rows from TableB from 1 to n/2. Each number is repeated twice, so the set looks like 1, 1, 2, 2, ... n /2, n/2.
Join the two intermediate result sets on matching age, matching random numbers, and not matching UserIds.
Each UserId from TableA will match to either 1 or 2 UserIds from TableB. This is because the ids are unique within each table and age. The result set will have at most 2 * m rows.
Reduce the UserIds from TableB that match to 2 UserIds from TableB to a single row. Keep an arbitrary UserId from TableB.

My query to implement that is a bit of a mess:

SELECT
  t.Age
, t.UserId UserIdFromTableA
, CAST(RIGHT(magic_column, 20) AS BIGINT) UserIdFromTableB
FROM
(
    SELECT
      ta.Age
    , ta.UserId
    , MAX(
          RIGHT(REPLICATE('0', 10) + CAST(tb.RN AS VARCHAR(10)), 10)
        + RIGHT(REPLICATE('0', 20) + CAST(tb.UserId AS VARCHAR(20)), 20)
    ) magic_column
    FROM
    (
        SELECT
          UserId
        , Age
        , ROW_NUMBER() OVER (PARTITION BY Age ORDER BY NEWID()) RN
        FROM dbo.TableA
    ) ta
    LEFT OUTER JOIN
    (
        SELECT
          UserId
        , Age
        , FLOOR(0.5 + 0.5 * ROW_NUMBER() OVER (PARTITION BY Age ORDER BY NEWID())) RN
        FROM dbo.TableB
    ) tb ON ta.Age = tb.Age AND ta.RN = tb.RN AND ta.UserId <> tb.UserId
    GROUP BY ta.Age
    , ta.UserId
) t
OPTION (MAXDOP 1, HASH JOIN);

There are a few important tricks that I did in that query for performance reasons. The string casting in the aggregate is so that I can take an arbitrary row to resolve ties with a single GROUP BY. Normally I would just take the minimum or maximum UserId but you said that you wanted random results and that would slightly bias the UserIds chosen from TableB.

I mapped rows from TableB in the way I did so that I could have an extra equality condition in the join: ta.RN = tb.RN. Doing the mapping in a simpler way could lead to a join condition like this: tb.RN IN (2 * ta.RN, 2 * ta.RN - 1) which cannot be efficiently checked in a hash or merge join.

The query hint at the bottom is to help the query optimizer get a more efficient plan for my machine. All of the ROW_NUMBER() stuff makes it difficult for the query optimizer to come up with good cardinality estimates and to cost the plan correctly. For example, it has no idea that we've arranged the random sequences so that at most two rows match for every row in TableA. You may not need that query hint or a different query hint may be better for your server and data.

On my machine, the query returns 1000000 rows as expected in about 40 seconds. The query plan has a horrifically high estimated cost but that doesn't reflect reality. Here's what the plan looks like:

Here's a sample of the query's results:

Again, I need to stress that this query will not return accurate results unless you have at least twice as many rows in Table B compared to Table A for every different age.


You need 2 tables: one called say, Fileset and the other FilesetFile

Fileset contains information about the group: source, owner, who and when etc
FilesetFile contains a foreign key to Fileset.

Example:

Fileset

FilesetID, PK
FilesetOwner
FilesetAddedBy
...

FilesetFile

FilesetID, PK, FK
FileSuffix, PK
FileContent (BLOB column say)
...

You can then create a computed column "FileName" that concatenates FilesetID and FileSuffix to generate the pattern you need above

Trying this with one table will end in failure.


Restoring tab exports to a mysql database with integrity

It doesn't really make sense that you're able to do the backup with the --tab option but not without it, and I think that should be investigated.

Concerns about integrity are generally concerns with the backup, and you should be concerned about the backup, because --single-transaction issues a START TRANSACTION WITH CONSISTENT SNAPSHOT before the backup, ensuring that what gets written to the file represents the state of all the tables at one consistent point in time.

Without this, your "user" table, being alphabetically later than your "history" table could (for example) in the backup file contain a new user but not have caught history of that account's creation, since the backup of "history" finished before the backup of "user" was started.

You say you want to restore within a transaction, but that's not possible (by default) and probably not really what you intend at any rate.

It's not possible, by default, because of the way mysqldump works.  Examine a backup file

DROP TABLE IF EXISTS `action`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `action` (
  `id` int(11) NOT NULL ...

Both CREATE TABLE and DROP TABLE cause an implicit commit.  This isn't the only concern, because non-conflicting writes to the tables could still be done by other threads.

There are two other ways to prevent changes on a MySQL server:

FLUSH TABLES WITH READ LOCK;

This attempts to close all tables and acquire a global lock that prevents any thread from writing.  The problem here again in with the standard behavior of mysqldump, and what it writes to the dump file for execution during the restore:

LOCK TABLES `action` WRITE;
/*!40000 ALTER TABLE `action` DISABLE KEYS */;
/*!40000 ALTER TABLE `action` ENABLE KEYS */;
UNLOCK TABLES;

These individual table locks are not compatible with the global lock:

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.16 sec)

mysql> lock tables t1 write;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

You can disable this behavior with --skip-add-locks but then you wouldn't be able to write data into the tables if you had the global read lock because it prevents you from writing as well as other threads... so that one seems to be a non-starter.

Alternately, you could force the entire server to read_only mode.

FLUSH TABLES WITH READ LOCK; -- force a wait until all ongoing writes have finished
SET GLOBAL READ_ONLY = 1;    -- set entire server to read-only except for users with `SUPER` privilege
UNLOCK TABLES;               -- release the global read lock
--- restore backup
SET GLOBAL READ_ONLY = 0;

If you're concerned with other threads modifying the tables during the restore, but you don't want the entire server to be read-only for the duration of the operation, I only see  one really solid option, and that would be to restore all of the tables with an alternate name, and then, in a single SQL statement, rename them all to their correct names.

When multiple tables are renamed in a single statement, the entire statement is atomic.

RENAME TABLE t1 TO t1_old, t1_new to t1;

This acquires a global mutex that prevents t1 from being accessed until all of the renames are complete... but it would require manipulating your dump file to give the tables alternate names, thought it might also work if you restored to an alternate database name and renamed the tables in a fully-qualified form.

Another approach would be to drop and add all of the tables and lock all of them for write, before inserting the data, after removing any LOCK and UNLOCK from the dump file(s).  The one additional step you'd need to do, here, would be after acquiring the locks, you'd need to run through every table and delete any data that got inserted by other threads between the time you created the table and acquired the locks, or the restore would fail.  SET FOREIGN_KEY_CHECKS = 0 is a session-level setting that would allow your session to freely remove any stray data without regard to foreign key constraints.

The bottom line is that there is not a simple, straightforward way to keep all hands off of the data you are attempting to restore, and if faced with that requirement, I would be inclined to shut down the application server or otherwise prevent access to the server with a mechanism outside of mysql itself, such as IP-level filtering.


What things can influence log_shipping_monitor_secondary - last_restored_latency values?

Most significant variable is the size of the transaction log backup that you are trying to ship, larger files :-
take longer to copy,
longer to restore &
more time to create the standby file.
If tlog backup size doesn´t explain more than 90% of the variation then you can start to troubleshoot whether server CPU or network congestion is involved.


Oracle SQL: Outer Join Not Working As Expected

Perhaps changing your JOIN condition slightly might help if you want
the value B_GRADUATION_TERM that doesn't match TERM_CODE without creating a Cartesian join :

From:

grad.B_GRADUATION_TERM = prog_enroll.TERM_CODE;

To:

 (grad.B_GRADUATION_TERM = prog_enroll.TERM_CODE or grad.B_GRADUATION_TERM is NULL);

As shown completely here:

SELECT prog_enroll.TERM_CODE, prog_enroll.STU_INST_UID, prog_enroll.MAJOR_DESC,prog_enroll.MAJOR_CIP, grad.B_GRADUATION_TERM FROM
(SELECT DISTINCT TERM_CODE, STU_INST_UID, LISTAGG(CIP_CODE,'+') WITHIN
GROUP(ORDER BY CIP_CODE) as MAJOR_CIP,
LISTAGG(CIP_DESCR,'+') WITHIN GROUP(ORDER BY CIP_CODE) as MAJOR_DESC
FROM
T_ADMSTU_PROG_ENROLL
WHERE
DEGREE_LEVEL_CODE = 'B' AND COLL_TYPE = 'MT'
GROUP BY TERM_CODE, STU_INST_UID) prog_enroll
FULL OUTER JOIN
(SELECT DISTINCT STU_INST_UID, B_GRADUATION_TERM
FROM
T_ADMGRAD_STUDENT
WHERE
STUDENT_ACADEMIC_LEVEL = 'US'
)  grad
ON
grad.STU_INST_UID = prog_enroll.STU_INST_UID
AND
(grad.B_GRADUATION_TERM = prog_enroll.TERM_CODE or grad.B_GRADUATION_TERM is NULL);


Can select read index page while modify this page in innodb?

Yes, that's possible, that's what the change buffer is for. It caches changes to secondary indexes. When data is read via an index and (parts of) the index is in the change buffer, it's read from there. The changes are written to disk periodically.

  The change buffer is a special data structure that caches changes to secondary index pages when affected pages are not in the buffer pool. The buffered changes, which may result from INSERT, UPDATE, or DELETE operations (DML), are merged later when the pages are loaded into the buffer pool by other read operations.
  ...
  Periodically, the purge operation that runs when the system is mostly idle, or during a slow shutdown, writes the updated index pages to disk.

Read about it in more detail here.

Also creating/dropping/changing an index is possible while selects/insert/updates/deletes can be performed at the same time with online DDL (at least with MySQL >= 5.6).

Read more about it here.


How to increase max_locks_per_transaction in MacOSX

Check the postgres.conf file. The parameter is defined there. On windows I found it in the data directory so I guess it will be the same for Mac OSX.


Recommendation on changing Azure SQL Database tier weekly

Azure SQL Database is designed to scale up and down based on your workload. I cannot answer your question "is it ok to change the tier to S0 every weekend for cost savings and change back to s6 on Monday" with yes or no.

You also mentioned usage is very minimal. S0 gives you maximum 10 DTU. Do you know for your "very minimal" workload 10 DTU is enough or no. Is your application written to retry disconnected sessions?

From books online:

  Existing connections to the database in the original compute instance
  are dropped. Any new connections are established to the database in
  the new compute instance. For some combinations of service tier and
  compute size changes, database files are detached and reattached
  during the switch. Regardless, the switch can result in a brief
  service interruption when the database is unavailable generally for
  less than 30 seconds and often for only a few seconds. If there are
  long running transactions running when connections are dropped, the
  duration of this step may take longer in order to recover aborted
  transactions. Accelerated Database Recovery can reduce the impact from
  aborting long running transactions.

Few other resources for your third point "any other steps to perform prior tier change and suggestions please".

Scale single database resources in Azure SQL Database
Recovering from a possibly failed scale-up
Stop / Cancel ongoing Azure SQL DB scale operation
sys.dm_operation_status (Azure SQL Database)


Why does a SELECT statement dirty cache buffers in Postgres?

This has a simple reason.

In PostgreSQL a row has to go through a visibility check. On the first read, the system checks if a row can be seen by everybody. If it is, it will be "frozen". This is where the writes come from. Similarly, VACUUM also sets bits.

There is a detailed explanation: http://www.cybertec.at/speeding-up-things-with-hint-bits/.


There are two separate transactions (T1 and T2) that each add ₽100 to the customer's balance.

The intended outcome is:

T1 reads the current balance as ₽1000, adds ₽100, and writes ₽1100
T2 reads the current balance as ₽1100, adds ₽100, and writes ₽1200

Or the other way around (T2 then T1). The important point is that both increments of ₽100 are applied.

In the example of a lost update, something like the following occurs:

T1 reads the current balance as ₽1000
T2 reads the current balance as ₽1000
T2 adds ₽100 (to the ₽1000 it read), and writes ₽1100
T1 adds ₽100 (to the ₽1000 it read), and writes ₽1100

This way the final balance is ₽1100, not ₽1200, so the customer has lost ₽100.


Referencing Aaron Bertrand's answer to a similar question, he says:

  Depending on what change you are making, sometimes it can be easier to
  take a maintenance window. During that window (where nobody should be
  able to change the data in the table) you can:

  1). drop any indexes/constraints pointing to the old column, and
  disable triggers

  2). add a new nullable column with the new data type (even if it is
  meant to be NOT NULL)

  3). update the new column setting it equal to the old column's value
  (and you can do this in chunks of individual transactions (say,
  affecting 10000 rows at a time using UPDATE TOP (10000) ... SET newcol
  = oldcol WHERE newcol IS NULL) and with CHECKPOINT to avoid overrunning your log)

  4). once the updates are all done, drop the old column

  5). rename the new column (and add a NOT NULL constraint if
  appropriate)

  6). rebuild indexes and update statistics

  The key here is that it allows you to perform the update incrementally
  in step 3, which you can't do in a single ALTER TABLE command.

  This assumes the column is not playing a major role in data integrity
  - if it is involved in a bunch of foreign key relationships, there are more steps.


Oracle equivalent to MS-Access character range/set

Use regular expressions:

SELECT
    table_name
FROM
    all_tab_columns
WHERE
    NOT REGEXP_LIKE(table_name,'^(A|D|S)[0-9]')