I assume that the database is checking always default values, even if I supply the correct value, hence I'm doing the same job twice.

Um, why would you assume that? ;-). Given that Defaults exist to provide a value when the column that they are attached to is not present in the INSERT statement, I would assume the exact opposite: that they are completely ignored if the associated column is present in the INSERT statement.

Fortunately, neither of us needs to assume anything due to this statement in the question:

  I'm mostly interested on performance.

Questions about performance are nearly always testable. So we just need to come up with a test to allow SQL Server (the true authority here) to answer this question.

SETUP

Run the following once:

SET NOCOUNT ON;

-- DROP TABLE #HasDefault;
CREATE TABLE #HasDefault
(
  [HasDefaultID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  [SomeInt] INT NULL,
  [SomeDate] DATETIME NOT NULL DEFAULT (GETDATE())
);

-- DROP TABLE #NoDefault;
CREATE TABLE #NoDefault
(
  [NoDefaultID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  [SomeInt] INT NULL,
  [SomeDate] DATETIME NOT NULL
);

-- make sure that data file and Tran Log file are grown, if need be, ahead of time:
INSERT INTO #HasDefault ([SomeInt])
  SELECT TOP (2000000) NULL
  FROM   [master].sys.[all_columns] ac1
  CROSS JOIN [master].sys.[all_columns] ac2;

Execute tests 1A and 1B individually, not together as that skews the timing. Run each one several times to get a sense of the average timing for each one.

Test 1A

TRUNCATE TABLE #HasDefault;
GO

PRINT '#HasDefault:';
SET STATISTICS TIME ON;
INSERT INTO #HasDefault ([SomeDate])
  SELECT TOP (1000000) '2017-05-15 10:11:12.000'
  FROM   [master].sys.[all_columns] ac1
  CROSS JOIN [master].sys.[all_columns] ac2;
SET STATISTICS TIME OFF;
GO

Test 1B

TRUNCATE TABLE #NoDefault;
GO

PRINT '#NoDefault:';
SET STATISTICS TIME ON;
INSERT INTO #NoDefault ([SomeDate])
  SELECT TOP (1000000) '2017-05-15 10:11:12.000'
  FROM   [master].sys.[all_columns] ac1
  CROSS JOIN [master].sys.[all_columns] ac2;
SET STATISTICS TIME OFF;
GO

Execute tests 2A and 2B individually, not together as that skews the timing. Run each one several times to get a sense of the average timing for each one.

Test 2A

TRUNCATE TABLE #HasDefault;
GO

DECLARE @Counter INT = 0,
        @StartTime DATETIME,
        @EndTime DATETIME;

BEGIN TRAN;
--SET STATISTICS TIME ON;
SET @StartTime = GETDATE();
WHILE (@Counter < 100000)
BEGIN
  INSERT INTO #HasDefault ([SomeDate]) VALUES ('2017-05-15 10:11:12.000');
  SET @Counter = @Counter + 1;
END;
SET @EndTime = GETDATE();
--SET STATISTICS TIME OFF;
COMMIT TRAN;
PRINT DATEDIFF(MILLISECOND, @StartTime, @EndTime);

Test 2B

TRUNCATE TABLE #NoDefault;
GO

DECLARE @Counter INT = 0,
        @StartTime DATETIME,
        @EndTime DATETIME;

BEGIN TRAN;
--SET STATISTICS TIME ON;
SET @StartTime = GETDATE();
WHILE (@Counter < 100000)
BEGIN
  INSERT INTO #NoDefault ([SomeDate]) VALUES ('2017-05-15 10:11:12.000');
  SET @Counter = @Counter + 1;
END;
SET @EndTime = GETDATE();
--SET STATISTICS TIME OFF;
COMMIT TRAN;
PRINT DATEDIFF(MILLISECOND, @StartTime, @EndTime);

You should see that there is no real difference in timing between tests 1A and 1B, or between tests 2A and 2B. So, no, there is no performance penalty to have a DEFAULT defined but not used.

Also, besides merely documenting intended behavior, you need to keep in mind that it is mostly you who cares about the DML statements being completely contained within your stored procedures. Support folks do not care. Future developers might not be aware of your desire to have all DML encapsulated within those stored procedures, or care even if they do know. And whoever maintains this DB after you are gone (either another project or job) might not care, or might not be able to prevent the use of an ORM no matter how much they protest. So, Defaults, can help in that they give folks an "out" when doing an INSERT, especially an ad-hoc INSERT done by a support rep, as that is one column they no need to include (which is why I always use defaults on audit date columns).

AND, it just occurred to me that it can be shown rather objectively whether or not a DEFAULT is checked when the associated column is present in the INSERT statement: simply provide an invalid value. The following test does just that:

-- DROP TABLE #BadDefault;
CREATE TABLE #BadDefault
(
  [BadDefaultID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  [SomeInt] INT NOT NULL DEFAULT (1 / 0)
);

INSERT INTO #BadDefault ([SomeInt]) VALUES (1234); -- Success!!!
SELECT * FROM #BadDefault; -- just to be sure ;-)

INSERT INTO #BadDefault ([SomeInt]) VALUES (DEFAULT); -- Error:
/*
Msg 8134, Level 16, State 1, Line xxxxx
Divide by zero error encountered.
The statement has been terminated.
*/
SELECT * FROM #BadDefault; -- just to be sure ;-)
GO

As you can see, when a column (and a value, not the keyword DEFAULT) is provided, the Default is 100% ignored. We know this because the INSERT succeeds. But if the Default is used, there is an error as it is finally being executed.

  Is there a way to avoid DEFAULT constraint within a trigger execution?

While needing to avoid Default Constraints (at least in this context) is completely unnecessary, for the sake of completeness it can be noted that it would only be possible to "avoid" a Default Constraint within an INSTEAD OF Trigger, but not within an AFTER Trigger. According to the documentation for CREATE TRIGGER:

  If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution and before the AFTER trigger execution. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not fired.

Of course, using an INSTEAD OF Trigger would require:

Disabling the Default Constraint
Creating an AFTER Trigger that enables the Constraint

However, I wouldn't exactly recommend doing this.


What do these pstart and pstop values mean?

Broadly, they mean that the partition(s) that need to be accessed will be determined at runtime.  In the first plan, where both are KEY, some earlier step in the plan is producing one or more values for the partition key.  Those partition keys are then used to determine which partitions Oracle needs to scan the index for.  In the second plan, the ROWID from the global index tells Oracle with partition to probe for each row that is being retrieved.


Tips on improving/refactoring slow query?

Changing it to use a CTE will be faster probably - they normally give the optimizer a better chance at optimising the query.

with currentdraw as (
select k1,k2,k3 from THREES
inner join DrawNumbers d1 WITH (NOLOCK) ON K1 = D1.DRAWNUMBER
INNER JOIN DRAWNUMBERS D2 WITH (NOLOCK) ON K2 = D2.DRAWNUMBER AND D1.DRAWID = D2.DRAWID
INNER JOIN DRAWNUMBERS D3 WITH (NOLOCK) ON K3 = D3.DRAWNUMBER AND D1.DRAWID = D3.DRAWID
WHERE D1.DRAWID = @CURDRAW
), previousdraws as (
select d1.drawid, k1,k2,k3 from THREES
inner join DrawNumbers d1 WITH (NOLOCK) ON K1 = D1.DRAWNUMBER
INNER JOIN DRAWNUMBERS D2 WITH (NOLOCK) ON K2 = D2.DRAWNUMBER AND D1.DRAWID = D2.DRAWID
INNER JOIN DRAWNUMBERS D3 WITH (NOLOCK) ON K3 = D3.DRAWNUMBER AND D1.DRAWID = D3.DRAWID
WHERE D1.DRAWID < @CURDRAW
)
select previousdraws.*
from previousdraws p
inner join currentdraw c
on p.k1 = c.k1 and p.k2 = c.k2 and p.k3 = c.k3
order by drawid desc

Your best indexes will probably be:

create index ix1 on THREES (k1,k2,k3);
create index ix1 on DrawNumbers (drawid, drawnumber);


Switching from a SQL Server 2016 Failover Cluster to Availability Groups

Do you need to have the same server configuration for both nodes?

No, this is not required, however it is a best practice. In the event that your secondary replica has fewer cores and less memory then you would see a degradation in performance when failed over to the lower powered machine. This might still give you sufficient performance, only you can make that decision, but it is not a requirement.

One thing I would recommend is to always ensure that you keep the drive letters and paths the same on all machines in an AG, otherwise you'll run into a lot of problems attempting to add data or log files.

  How does Disaster Recovery work?

You will need to license your secondary if you allow read intent routing. That is something that gets configured at the AG level. But ALWAYS check with a licensing representative to ensure that you are compliant.

You should not need additional bandwidth for AG traffic if you are already using mirroring. Packets are compressed prior to sending between machines (when running in asynchronous commit mode).

  Can I create a primary for one AG and a different AG for another?

Yes.

The limitation here is that a database can only reside within a single AG, and that all nodes must be in the same WSFC. Be aware though (going back to your licensing question) that if a server is a primary for databases then it must be appropriately licensed.


Identifying blocking queries on MySQL 5.5

This query may assist in your search for locking and blocking ids.

# IS-tx-wait-block-qrys.sql to help research BUSY systems Last Updated 20191227 wh
# if this helps you, please email SHAREABLE comments to info@mysqlservertuning.com
# From article at
# https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-server-activity/
# 20191227 tested on MariaDB 10.3.10

SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;


Prevent row from being inserted with a trigger

Remove the Exception block so you no longer catch the error.  Then the error will propagate out and prevent and prevent the insertion from occurring.


Resolving TNS Permision Denied for XE on Windows 7

So I still don't know exactly what caused my issues.  But after spending way to much time trying to research it and tweak things, I got it working again.  The two items that I believe were most critical to the process were:

Updating/syncing my .ora files.  In particular I had some with references to localhost that had to be changed to my full machine name.

Granting my individual user full control on my oracle directory.  I found a reference elsewhere that noted that the permissions inherited from a group were not being honored correctly.


If you require change tracking across your entire database, you might consider implementing SQL Server Change Tracking.

If you want to track changes to a single table, you might consider rolling your own solution, as in the example I've created below:

The sample table we want to track changes made against:

CREATE TABLE dbo.SomeData
(
    ID int NOT NULL
        CONSTRAINT PK_SomeData
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , SomeVal varchar(100) NOT NULL
    , SomeDate datetime NOT NULL
);

The table where we'll keep track of changes:

CREATE TABLE dbo.SomeData_Changes
(
    ChangeID bigint NOT NULL
        CONSTRAINT PK_SomeData_Changes
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ChangeGroup int NOT NULL
    , ChangeDate datetime
        CONSTRAINT DF_SomeData_Changes_ChangeDate
        DEFAULT ((GETDATE()))
    , OpType varchar(1) NOT NULL
    , ID int NOT NULL
    , SomeVal varchar(100) NOT NULL
    , SomeDate datetime NOT NULL
);

The change-tracking infrastructure we need includes a sequence to group changes together:

CREATE SEQUENCE dbo.SomeData_Changes_Group
AS int
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 1000;
GO

This is a trigger that will track all changes made to the SomeData table:

CREATE TRIGGER SomeData_ChangeTracker
ON dbo.SomeData
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @Group int;
    SET @Group = NEXT VALUE FOR dbo.SomeData_Changes_Group;

    IF EXISTS (
        SELECT 1
        FROM deleted
        )
    BEGIN
        INSERT INTO dbo.SomeData_Changes (OpType, ChangeGroup, ID, SomeVal, SomeDate)
        SELECT 'D'
            , @Group
            , d.ID
            , d.SomeVal
            , d.SomeDate
        FROM deleted d
    END

    IF EXISTS (
        SELECT 1
        FROM inserted
        )
    BEGIN
        INSERT INTO dbo.SomeData_Changes (OpType, ChangeGroup, ID, SomeVal, SomeDate)
        SELECT 'I'
            , @Group
            , i.ID
            , i.SomeVal
            , i.SomeDate
        FROM inserted i
    END
    PRINT N'In trigger';
END
GO

Here, we test the change tracker:

INSERT INTO dbo.SomeData (SomeVal, SomeDate)
VALUES ('test row 1', GETDATE());

SELECT *
FROM dbo.SomeData_Changes;

╔══════════╦═════════════╦═════════════════════════╦════════╦════╦════════════╦═════════════════════════╗
║ ChangeID ║ ChangeGroup ║       ChangeDate        ║ OpType ║ ID ║  SomeVal   ║        SomeDate         ║
╠══════════╬═════════════╬═════════════════════════╬════════╬════╬════════════╬═════════════════════════╣
║        1 ║           1 ║ 2018-03-02 10:17:21.710 ║ I      ║  1 ║ test row 1 ║ 2018-03-02 10:17:21.703 ║
╚══════════╩═════════════╩═════════════════════════╩════════╩════╩════════════╩═════════════════════════╝

An update:

UPDATE dbo.SomeData
SET SomeVal = 'Test Row 1'
WHERE ID = 1;

SELECT *
FROM dbo.SomeData_Changes;

╔══════════╦═════════════╦═════════════════════════╦════════╦════╦════════════╦═════════════════════════╗
║ ChangeID ║ ChangeGroup ║       ChangeDate        ║ OpType ║ ID ║  SomeVal   ║        SomeDate         ║
╠══════════╬═════════════╬═════════════════════════╬════════╬════╬════════════╬═════════════════════════╣
║        1 ║           1 ║ 2018-03-02 10:17:21.710 ║ I      ║  1 ║ test row 1 ║ 2018-03-02 10:17:21.703 ║
║        2 ║           2 ║ 2018-03-02 10:17:21.710 ║ D      ║  1 ║ test row 1 ║ 2018-03-02 10:17:21.703 ║
║        3 ║           2 ║ 2018-03-02 10:17:21.710 ║ I      ║  1 ║ Test Row 1 ║ 2018-03-02 10:17:21.703 ║
╚══════════╩═════════════╩═════════════════════════╩════════╩════╩════════════╩═════════════════════════╝

The above output shows ChangeGroup "2", which is the update.  It consists of both a "delete" and an "insert", which is how many table updates are actually accomplished behind the scenes in SQL Server.

A delete:

DELETE
FROM dbo.SomeData
WHERE ID = 1;

SELECT *
FROM dbo.SomeData_Changes;

╔══════════╦═════════════╦═════════════════════════╦════════╦════╦════════════╦═════════════════════════╗
║ ChangeID ║ ChangeGroup ║       ChangeDate        ║ OpType ║ ID ║  SomeVal   ║        SomeDate         ║
╠══════════╬═════════════╬═════════════════════════╬════════╬════╬════════════╬═════════════════════════╣
║        1 ║           1 ║ 2018-03-02 10:17:21.710 ║ I      ║  1 ║ test row 1 ║ 2018-03-02 10:17:21.703 ║
║        2 ║           2 ║ 2018-03-02 10:17:21.710 ║ D      ║  1 ║ test row 1 ║ 2018-03-02 10:17:21.703 ║
║        3 ║           2 ║ 2018-03-02 10:17:21.710 ║ I      ║  1 ║ Test Row 1 ║ 2018-03-02 10:17:21.703 ║
║        4 ║           3 ║ 2018-03-02 10:17:21.713 ║ D      ║  1 ║ Test Row 1 ║ 2018-03-02 10:17:21.703 ║
╚══════════╩═════════════╩═════════════════════════╩════════╩════╩════════════╩═════════════════════════╝

The delete is ChangeGroup 3 above.

Cleanup:

IF OBJECT_ID(N'dbo.SomeData', N'U') IS NOT NULL
BEGIN
    DROP TRIGGER SomeData_ChangeTracker;
    DROP TABLE dbo.SomeData;
END
IF OBJECT_ID(N'dbo.SomeData_Changes', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.SomeData_Changes;
    DROP SEQUENCE dbo.SomeData_Changes_Group;
END
GO

You could trim rows from the change-tracking table using a SQL Server Agent Job that performs the following every day at midnight:

DELETE
FROM dbo.SomeData_Changes
WHERE dbo.SomeData_Changes.ChangeDate < DATEADD(DAY, -5, GETDATE());


Removing sp_execute_external_script Start-Up Lag

As of writing, there isn't a feature (that I've ever heard of) and I imagine anything in this area would just be implemented as a performance fix as the external scripting feature matures.

Two options in the meantime would be:

Run a dummy script at startup.
Quit rebooting your server! If this is a testing environment or one being spun up dynamically, speed probably isn't a priority. If it is production, you you might need to ask yourself why you're rebooting with any amount of regularity.


What are the most effective techniques to reduce SQL Server disk space use when updating all rows in a very large table?

I've just gone through a similar process just couple of weeks ago. After several tries, with couple of the bigger tables (one of them more than 100million rows, near 80Gb) I came up with these steps to speed up things and keep transaction log small:

ALTER DATABASE [my_db] SET RECOVERY SIMPLE WITH NO_WAIT (check here for more info)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED (check here for more info)
drop nonclustered indexes: this step will avoid the hassle with the need of updating them on each operation (check Brent Ozar blog for more details)
use batches: as suggested by mrdenny, do whatever you need to do, update, delete, inserts, in smaller chunks.

Here is a sample of batch processing for an update, 1000 rows at a time:

 UPDATE TOP(1000) your_table
 SET    col1 = new_value
 WHERE  <your_condition>
 WHILE  @@rowcount > 0
 BEGIN
     UPDATE TOP(1000) your_table
     SET    col1 = new_value
     WHERE  <your_condition>;
 END;
 GO

restore nonclustered indexes
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
ALTER DATABASE [my_db] SET RECOVERY FULL;


how to select rows matching a condition with a default

Here are a couple of approaches you can try.

DECLARE @lng INT;

SET @lng = 3;

/*Approach 1*/
SELECT y.*
FROM   YourTable AS y
WHERE  y.lng IN (1,@lng)
       AND y.lng = (SELECT MAX(y2.lng)
                    FROM   YourTable AS y2
                    WHERE  y2.vi_id = y.vi_id
                           AND y2.lng IN (1,@lng))
ORDER BY id;

/*Approach 2 (assumes id is PK and all vi_id have a lng=1 row) */
SELECT
    CASE WHEN y2.id IS NULL THEN y1.id ELSE y2.id END AS id,
    CASE WHEN y2.id IS NULL THEN y1.title ELSE y2.title END AS title,
    CASE WHEN y2.id IS NULL THEN y1.lng ELSE y2.lng END AS lng,
    CASE WHEN y2.id IS NULL THEN y1.vi_id ELSE y2.vi_id END AS vi_id
FROM   YourTable y1
       LEFT JOIN YourTable y2
         ON y1.vi_id = y2.vi_id
            AND y2.lng = @lng
WHERE  y1.lng = 1
ORDER BY id;


Strictly speaking, the term "stored procedures" points to SQL procedures in Postgres, introduced with Postgres 11. Related:

When to use stored procedure / user-defined function?

There are also functions, doing almost but not quite the same, and those have been there from the beginning.

Functions with LANGUAGE sql are basically just batch files with plain SQL commands in a function wrapper (and therefore atomic, always run inside a single transaction) accepting parameters. All statements in an SQL function are planned at once, which is subtly different from executing one statement after the other and may affect the order in which locks are taken.

For anything more, the most mature language is PL/pgSQL (LANGUAGE plpgsql). It works well and has been improved with every release over the last decade, but it serves best as glue for SQL commands. It is not meant for heavy computations (other than with SQL commands).

PL/pgSQL functions execute queries like prepared statements. Re-using cached query plans cuts off some planning overhead and makes them a bit faster than equivalent SQL statements, which may be a noticeable effect depending on circumstances. It may also have side effects like in this related question:

PL/pgSQL issues when function used twice (caching problem ?)

This carries the advantages and disadvantages of prepared statements - as discussed in manual. For queries on tables with irregular data distribution and varying parameters dynamic SQL with EXECUTE may perform better when the gain from an optimized execution plan for the given parameter(s) outweighs the cost of re-planning.

Since Postgres 9.2 generic execution plans are still cached for the session but, quoting the manual:

  This occurs immediately for prepared statements with no parameters;
  otherwise it occurs only after five or more executions produce plans
  whose estimated cost average (including planning overhead) is more
  expensive than the generic plan cost estimate.

We get best of both worlds most of the time (less some added overhead) without (ab)using EXECUTE. Details in What's new in PostgreSQL 9.2 of the PostgreSQL Wiki.

Postgres 12 introduces the additional server variable plan_cache_mode to force generic or custom plans. For special cases, use with care.

You can win big with server side functions that prevent additional round-trips to the database server from your application. Have the server execute as much as possible at once and only return a well defined result.

Avoid nesting of complex functions, especially table functions (RETURNING SETOF record or TABLE (...)). Functions are black boxes posing as optimization barriers to the query planner. They are optimized separately, not in the context of the outer query, which makes planning simpler, but may result in less than perfect plans. Also, cost and result size of functions cannot be predicted reliably.

The exception to this rule are simple SQL functions (LANGUAGE sql), which can be "inlined" - if some preconditions are met. Read more about how the query planner works in this presentation by Neil Conway (advanced stuff).

In PostgreSQL a function always automatically runs inside a single transaction. All of it succeeds or nothing. If an exception occurs, everything is rolled back. But there is error handling ...

That's also why functions are not exactly "stored procedures" (even though that term is used sometimes, misleadingly). Some commands like VACUUM, CREATE INDEX CONCURRENTLY or CREATE DATABASE cannot run inside a transaction block, so they are not allowed in functions. (Neither in SQL procedures, yet, as of Postgres 11. That might be added later.)

I have written thousands of plpgsql functions over the years.


I'd suggest a more radical approach. For databases of your size a fulltext search is not only ineffective, but inefficient. I'm guessing that there's some kind of user-driven search function requiring your index.

How about you employ a real search engine? This would take the load of key generation and reordering off of your database. It would give you the chance to offload the load to even another machine.

Take a look at Apache Solr, a well-received, fast implementation based on Lucene. Many large non-profit and commercial websites use it with success.

Then, remove the fulltext indexing from your table. Inserts should then fly into the table with only the ID key left.

Also, if you regularily remove rows from the table, OPTIMIZE TABLE should be done regularily.

For backup purposes, you might consider replication. There are multiple ways to implement the replication, and all of them spread the load out over time, instead of creating a downtime for it, which is what you have now. As an added benefit some replications can produce a database that can be used as a stand-in replacement if the primary database fails, so that the application can be up again in little to no time.


Amount of Data transferred out in mongorestore

MongoDB 3.4 and newer servers can negotiate network compression if MongoDB clients/drivers have also been updated to support the compressed message format. Network compression was introduced as an optional feature in MongoDB 3.4 and is enabled by default for MongoDB 3.6+ servers.

Unfortunately the mgo driver used by mongodump (as at MongoDB 4.0) does not support network compression and is no longer actively maintained. The expected outcome is that mongorestore or mongodump data transfer will roughly match your dataSize (plus some network overhead).

An officially supported MongoDB Go driver is currently in development and will include full support for the latest MongoDB features. Once the new Go driver is ready, the server command line tools will be updated to be able to take advantage of additional options. A relevant issue to upvote/watch in the MongoDB issue tracker is TOOLS-1668 (Support wire protocol compression in mongodump and mongorestore).

In the interim there are a few possible workarounds if network compression will significantly improve your transfer time to a remote server:

Copy the gzipped mongodump file to your remote server and restore locally
Use an SSH or VPN connection which supports network compression


Search database name in information_schema database

information_schema in this context is a string value, not an object name, so you need to enclose it in quotes like so:
SELECT *
FROM TABLES W
WHERE TABLE_SCHEMA NOT IN ('information_schema')


Can db_datareader user block ALTER TABLE statement?

Yes. The first query has left an open transaction. SQL Server doesn't check if the current user has permissions at compile time. It will find out at run time. If you were writing a stored procedure you wouldn't want to check permissions at compile time as a different user may execute the procedure.

Wrapping the UPDATEs, in both queries, in a TRY CATCH would avoid leaving an open transaction.

BEGIN TRY
    BEGIN TRAN

    UPDATE [TestDB].[dbo].[Table_1]
      SET Col1 = 'Test';

    COMMIT TRAN
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK;

    ;THROW;
END CATCH


What you are describing seems exactly like PartitionDB*.  You can create your multi-tenant database design in more than one mode. Look at their configuration page.

Basically, you have a master database which populates metadata structure information between the database nodes. They support SQL Server and also a builtin replication mechanism if you like to use.

* I am a beta user, but not affiliated with this product otherwise.


SQL Server - Why am I getting TCP connections without sessions?

I'll assume you've restarted SQL Server after enabling the force encryption option.
In addition to the reasons you mentioned in your question, you may see a connection without an associated session if your run the query while a client is in the process of connecting. The encrypt_option will show FALSE immediately after the physical connection is established but change to TRUE after the client sends a TDS PRELOGIN message as part of the handshake process. The session will be created once the login process completes.
You can filter out in-progress login noise in your monitoring query either by using an inner join instead of outer join to sys.dm_exec_sessions or by adding AND con.protocol_version > 0.


You would need to build a custom backup script which uses dynamic SQL to build the name of the .bak file. The maintenance plan could execute this with the 'Execute T-SQL Statement Task' item.

The T-SQL would look something like this

Select @SQL = 'BACKUP DATABASE [' + @DBName + '] TO  DISK = N''' + @Path + '\' + @DBName + '_backup_' + @Date + '.bak'' WITH NOFORMAT, INIT,  NAME = N''' + @DBName + '_backup_' + @Date + ''', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10 '

EXEC SP_ExecuteSql @SQLToExecute = @SQL


how to find tables with either sparse or a column set column?

Just use the sys.columns table:

SELECT name, is_sparse, is_column_set
FROM sys.columns

Column sets are:

  Tables that use sparse columns can designate a column set to return all sparse columns in the table. A column set is an untyped XML representation that combines all the sparse columns of a table into a structured output. A column set is like a calculated column in that the column set is not physically stored in the table. A column set differs from a calculated column in that the column set is directly updatable.


question about MySQL5.7 server(Master) and a MariaDB 10.3(Slave)

There are some lesser-known settings that should be set on the Slave.  Pehaps you are thinking about log_slow_slave_statements.  More details here:  http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

Note: MySQL / MariaDB / Percona -- these have diverged somewhat in what options are available.  Also, newer versions have more settings.  The above like provides my recommendations, but check the details with the manual.


Please try this format string:

0,,.0 \M $

This trick is a bit buried in the documentation.


UTL_RAW "invalid identifier" error message

By default, execute privilege on UTL_RAW package is granted to PUBLIC, meaning any database user can execute it.

Getting "invalid identifier" error means the package or referenced function does not exist, or you do not have the necessary privilege to execute it.

Either way, you have a non-standard environment.

Maybe it has fallen victim of overzealous security hardening and the execute privilege was revoked from PUBLIC.

Be Cautious When Revoking Privileges Granted to PUBLIC (Doc ID 247093.1)

Or it was revoked following documents such as:

Master Note for Oracle XML Database (XDB) Install / Deinstall (Doc ID 1292089.1)

To view the above documents, you need an Oracle account with a valid support contract associated with it.

As a DBA, you should know why the privilege was revoked. If you are not the DBA, ask the DBA who is responsible for this database.

If you want to restore the default state of this:

grant execute on sys.utl_raw to public;

That is how the package was originally created, you can view this in its definition:

$ grep utl_raw $ORACLE_HOME/rdbms/admin/prvtrawb.plb
CREATE OR REPLACE PACKAGE BODY utl_raw wrapped
create or replace public synonym utl_raw for sys.utl_raw
grant execute on sys.utl_raw to public


I believe every implementation of a graph will vary on how they go about writing and reading from the disk.
On page 2, section 2.2 of Dgraph: Synchronously Replicated, Transactional and Distrubuted Graph Database by Manish Jain, Data Storage discussion is introduced with:

Dgraph data is stored in an embeddable key-value database
called Badger for data input-output on disk. Badger is
an LSM-tree based design, but differs from others in how it
can optionally store values separately from keys to generate
a much smaller LSM tree, which results in both lower write
and read amplification.

You asked:

How the data might be structured into files for a graph database

The data is stored into posting lists by subject-predicate groups. Any node that contains a list of edges would store that list in a single posting list until it reaches a certain threshold in size and needs to be split across two or ore lists. This could be understood that any one to one relationship edge or predicate is saved in its own list and one-to-many relationship edge or predicates are grouped together and saves the linking uid or object value into a list. The main take away is that Badger stores values separate from keys to generate a smaller tree which increases read and write performance.

What must happen when querying/updating in terms of loading stuff into memory at a high level.

Manish just released a blog post about memory management and can explain that aspect better than myself.


I think this will be easier with an example:
Database A has table C.  Database B has table D.  C and D are very similar but not identical and the data needs to be cleaned up before being loaded in to D.

ETL
An ETL program (SSIS, Pentaho, whatever) pulls the data from table C.  It then makes some changes to the data to clean it up and put it in the format that is needed.  The ETL process then moves/copies that data up to table D on Database B.

ELT
The data from Table C is extracted.  It is uploaded to Database B as table C.  Database B now has two tables, C and D.  A database process (SQL, a job, whatever) now makes some changes to the data in table C and puts it in the format that is needed.  That same process then copies the now clean data in to table D.  The temporary Table C is no longer needed in Database B and can be deleted or truncated.

In both cases the data is now loaded in Database B, Table D.  The front end application continues to use Table D.


sql server: updating fields on huge table in small chunks: how to get progress/status?

I was not aware of this question when I answered the related question ( Are explicit transactions needed in this while loop? ), but for the sake of completeness, I will address this issue here as it was not part of my suggestion in that linked answer.

Since I am suggesting to schedule this via a SQL Agent job (it is 100 million rows, after all), I don't think that any form of sending status messages to the client (i.e. SSMS) will be ideal (though if that is ever a need for other projects, then I agree with Vladimir that using RAISERROR('', 10, 1) WITH NOWAIT; is the way to go).

In this particular case, I would create a status table that can be updated per each loop with the number of rows updated thus far. And it doesn't hurt to throw in the current time to have a heart-beat on the process.

Given that you want to be able to cancel and restart the process, I am weary of wrapping the the UPDATE of the main table with the UPDATE of the status table in an explicit transaction. However, if you feel that the status table is ever out of sync due to cancelling, it is easy to refresh with the current value by simply updating it manually with the COUNT(*) FROM [huge-table] WHERE deleted IS NOT NULL AND deletedDate IS NOT NULL. and there are two tables to UPDATE (i.e. the main table and the status table), we should use an explicit transaction to keep those two tables in sync, yet we do not want to risk having an orphaned transaction if you cancel the process at a point after it has started the transaction but has not committed it. This should be safe to do as long as you don't stop the SQL Agent job.

How can you stop the process without, um, well, stopping it? By asking it to stop :-). Yep. By sending the process a "signal" (similar to kill -3 in Unix), you can request that it stop at the next convenient moment (i.e. when there is no active transaction!) and have it clean itself up all nice and tidy-like.

How can you communicate with the running process in another session? By using the same mechanism that we created for it to communicate its current status back to you: the status table. We just need to add a column that the process will check at the beginning of each loop so that it knows whether to proceed or abort. And since the intent is to schedule this as a SQL Agent job (run every 10 or 20 minutes), we should also check at the very beginning since there is no point in filling a temp table with 1 million rows if the process is just going to exit a moment later and not use any of that data.

DECLARE @BatchRows INT = 1000000,
        @UpdateRows INT = 4995;

IF (OBJECT_ID(N'dbo.HugeTable_TempStatus') IS NULL)
BEGIN
  CREATE TABLE dbo.HugeTable_TempStatus
  (
    RowsUpdated INT NOT NULL, -- updated by the process
    LastUpdatedOn DATETIME NOT NULL, -- updated by the process
    PauseProcess BIT NOT NULL -- read by the process
  );

  INSERT INTO dbo.HugeTable_TempStatus (RowsUpdated, LastUpdatedOn, PauseProcess)
  VALUES (0, GETDATE(), 0);
END;

-- First check to see if we should run. If no, don't waste time filling temp table
IF (EXISTS(SELECT * FROM dbo.HugeTable_TempStatus WHERE PauseProcess = 1))
BEGIN
  PRINT 'Process is paused. No need to start.';
  RETURN;
END;

CREATE TABLE #FullSet (KeyField1 DataType1, KeyField2 DataType2);
CREATE TABLE #CurrentSet (KeyField1 DataType1, KeyField2 DataType2);

INSERT INTO #FullSet (KeyField1, KeyField2)
  SELECT TOP (@BatchRows) ht.KeyField1, ht.KeyField2
  FROM   dbo.HugeTable ht
  WHERE  ht.deleted IS NULL
  OR     ht.deletedDate IS NULL

WHILE (1 = 1)
BEGIN
  -- Check if process is paused. If yes, just exit cleanly.
  IF (EXISTS(SELECT * FROM dbo.HugeTable_TempStatus WHERE PauseProcess = 1))
  BEGIN
    PRINT 'Process is paused. Exiting.';
    BREAK;
  END;

  -- grab a set of rows to update
  DELETE TOP (@UpdateRows)
  FROM   #FullSet
  OUTPUT Deleted.KeyField1, Deleted.KeyField2
  INTO   #CurrentSet (KeyField1, KeyField2);

  IF (@@ROWCOUNT = 0)
  BEGIN
    RAISERROR(N'All rows have been updated!!', 16, 1);
    BREAK;
  END;

  BEGIN TRY
    BEGIN TRAN;

    -- do the update of the main table
    UPDATE ht
    SET    ht.deleted = 0,
           ht.deletedDate = '2000-01-01'
    FROM   dbo.HugeTable ht
    INNER JOIN #CurrentSet cs
            ON cs.KeyField1 = ht.KeyField1
           AND cs.KeyField2 = ht.KeyField2;

    -- update the current status
    UPDATE ts
    SET    ts.RowsUpdated += @@ROWCOUNT,
           ts.LastUpdatedOn = GETDATE()
    FROM   dbo.HugeTable_TempStatus ts;

    COMMIT TRAN;
  END TRY
  BEGIN CATCH
    IF (@@TRANCOUNT > 0)
    BEGIN
      ROLLBACK TRAN;
    END;

    THROW; -- raise the error and terminate the process
  END CATCH;

  -- clear out rows to update for next iteration
  TRUNCATE TABLE #CurrentSet;

  WAITFOR DELAY '00:00:01'; -- 1 second delay for some breathing room
END;

-- clean up temp tables when testing
-- DROP TABLE #FullSet;
-- DROP TABLE #CurrentSet;

You can then check the status at any time using the following query:

SELECT sp.[rows] AS [TotalRowsInTable],
       ts.RowsUpdated,
       (sp.[rows] - ts.RowsUpdated) AS [RowsRemaining],
       ts.LastUpdatedOn
FROM sys.partitions sp
CROSS JOIN dbo.HugeTable_TempStatus ts
WHERE  sp.[object_id] = OBJECT_ID(N'ResizeTest')
AND    sp.[index_id] < 2;

Want to pause the process, whether it is running in a SQL Agent job or even in SSMS on someone else's computer? Just run:

UPDATE ht
SET    ht.PauseProcess = 1
FROM   dbo.HugeTable_TempStatus ts;

Want the process to be able to start back up again? Just run:

UPDATE ht
SET    ht.PauseProcess = 0
FROM   dbo.HugeTable_TempStatus ts;

UPDATE:

Here are some additional things to try that might improve the performance of this operation. None are guaranteed to help but are probably worth testing out. And with 100 million rows to update, you have plenty of time / opportunity to test some variations ;-).

Add TOP (@UpdateRows) to the UPDATE query so that the top line looks like:
UPDATE  TOP (@UpdateRows) ht
Sometimes it helps the optimizer to know how many rows max will be affected so it doesn't waste time looking for more.
Add a PRIMARY KEY to the #CurrentSet temporary table. The idea here is to help the optimizer with the JOIN to the 100 million row table.

And just to have it stated so as to not be ambiguous, there shouldn't be any reason to add a PK to the #FullSet temporary table as it is just a simple queue table where the order is irrelevant.
In some cases it helps to add a Filtered Index to assist the SELECT that feeds into the #FullSet temp table. Here are some considerations related to adding such an index:

The WHERE condition should match the WHERE condition of your query, hence WHERE deleted is null or deletedDate is null
At the beginning of the process, most rows will match your WHERE condition, so an index isn't that helpful. You might want to wait until somewhere around the 50% mark before adding this. Of course, how much it helps and when it is best to add the index vary due to several factors, so it is a bit of trial and error.
You might have to manually UPDATE STATS and/or REBUILD the index to keep it up to date since the base data is changing quite frequently
Be sure to keep in mind that the index, while helping the SELECT, will hurt the UPDATE since it is another object that must be updated during that operation, hence more I/O. This plays into both using a Filtered Index (which shrinks as you update rows since fewer rows match the filter), and waiting a little while to add the index (if it's not going to be super helpful in the beginning, then no reason to incur the additional I/O).


Overloaded Database - Batch Updates vs Atomic/Single Updates

I agree with @gbn, can you share more details about what constitutes "load".

First take a look at your server level wait types to get a feel for where your resource crunch points are.

Also identify your poorest performing queries and those that are most frequently executed. These are where you should target your performance tuning efforts.

To get you started quickly, grab a copy of Glenn Berry's SQL Server Diagnostic Queries which contain a number of useful scripts that you can use.


SELECT query slowing down the wal replay in replication server

Replication can be delayed if a query on the standby needs data the master has removed. The WAL from the master with the resource removal cannot be replayed until the standby query finishes, or the standby query must be cancelled.

You can:

Let the standby cancel the query after a time limit - see the max_standby_archive_delay and max_standby_stremaing_delay parameters; or
Enable hot_standby_feedback on the standby so the master won't remove resources the standby needs while its queries are running. This isn't perfect, conflicts that delay replication or cause query cancels can still happen when the standby just recently started up. But it helps.


Issues setting up Oracle 18c XE on Windows Server 2012 r2

So I ended up accidentally solving this problem...

After trying to setup a new database in DBCA (Database Configuration Assistant), I went to delete the newly created one only to realize that I had accidentally deleted the "XE" instance. I went ahead and deleted the new instance I had attempted to create as well and then went back into DBCA and created a new instance with SID of "XE" (if I tried with a different SID Oracle XE would complain about the restriction of one instance per install. I also adjusted the sga_target &  pga_aggregate_target values to sum to 2GB (another restriction on XE) and lowered the processes value (changed from initial of 4400 to 320 since that is why my laptop used and it worked) and the instance was recreated with all the necessary control & data files.

  In summary:

  When doing a fresh install of Oracle on Windows Server 2012 r2 (may work for other supported Win Server OS', but I only have 2012 r2 to test), if the default instance does not have the needed control and/or data files do the following (using Advanced Configuration in DBCA):

  Delete the default instance (this was 'XE' in my case). If there are any other instances you attempted to create, delete them as well.
  Create a new instance using the default SID (this was 'XE' in my case) - if needed, set it as a container databases for any pluggable databases you create in the future.

    If using Oracle 18c XE, ensure that the sga_target and pga_aggregate_target values sum to the restricted memory limits (2GB) set by XE (my values were sga_target=1536M & pga_aggregate_target=512M).

    Set processes value to an appropriate value. I'm not sure if this was necessary but , I kept getting ORA-47500 if this was not lowered after I had adjusted sga & pga values.

  Create pluggable databases for your instance as needed.

Like I mentioned this solved my issue - please feel free to correct this if I made any incorrect assumptions. Hopefully this will help others if they run into a similar situation - I'm assuming these directions will be tweaked depending on what server OS the install will be accomplished on.


Using same table in a UNION multiple times

Use
Select X.Column1, X.Column2, Sum(X.Column3) AS Test, Types."Type"
From Table X
CROSS JOIN ( SELECT "First Query" AS "Type"
             UNION ALL
             SELECT "Second Query"
             UNION ALL
             SELECT "Third Query" ) AS Types
Where X.Column1 = "Fly"
Group by X.Column1, X.Column2, Types."Type"

Maybe SELECTs in Types needs additional FROM DUAL or similar, or DB2 uses some another method for to select scalar value - test.
UPDATE (copied from the comment)

You can shorten the CROSS JOIN part with:
CROSS JOIN ( values ('First Query'),('Second Query'),('Third Query')) types (type)

FWIW, Db2 also allows
CROSS JOIN ( values 'First Query','Second Query','Third Query') types (type)

but that's non standard I beleive – Lennart


You can use a SUM(CASE... for this purpose.

SELECT
    user_uuid,
    COUNT(*) cnt,
    ROUND (100.0 * (SUM(CASE WHEN value = '111' AND state = true THEN 1 ELSE 0 END)) / COUNT(*), 1) AS percent
FROM
    t
GROUP BY
    user_uuid;

user_uuid | cnt | percent
:-------- | --: | ------:
uuid_2    |   3 |     0.0
uuid_3    |   1 |     0.0
uuid_1    |   6 |    16.7
uuid_4    |   1 |     0.0

As a_horse_with_no_name has pointed out, you can use COUNT(*) FILTER(WHERE...) syntax too.

SELECT
    user_uuid,
    COUNT(*) cnt,
    ROUND (100.0 * (COUNT(*) FILTER (WHERE value = '111' AND state = true)) / COUNT(*), 1) AS percent
FROM
    t
GROUP BY
    user_uuid;

db<>fiddle here


Linux Volumes for SQL Server + Docker on Linux

When running SQL Server in a container you'll want to use either bind mounts or named volumes to persist the databases.
By using either bind mounts or named volumes what you're doing is separating out the data from the SQL instance so that if a container has an issue and needs to be removed, you can spin up another one and mount the databases into it.
Bind mounts are mounting volumes from the host into the container and named volumes are volumes created within docker that can be mapped from one container to another.
Both are specified by using the --volume flag in the docker container run statement.
By using these you can determine where on the host the SQL Server databases will persist and request storage accordingly.
An example of bind mounts: -
mkdir /mnt/mssqlsystem
mkdir /mnt/mssqluser

docker container run -d \
--publish 1433:1433 \
--volume /mnt/mssqlsystem:/var/opt/mssql/data \
--volume /mnt/mssqluser:/var/opt/sqlserver \
--env ACCEPT_EULA=Y \
--env MSSQL_SA_PASSWORD=Testing1122 \
--env MSSQL_DATA_DIR=/var/opt/sqlserver \
--env MSSQL_LOG_DIR=/var/opt/sqlserver \
--name sqlcontainer1 \
mcr.microsoft.com/mssql/server:2019-CU6-ubuntu-18.04

What's happening here is that two directories have been created on the host and mapped to /var/opt/mssql/data (the location of the system databases) and /var/opt/sqlserver within the container. /var/opt/sqlserver is also the default location for the user data and log directories set by ENV variables.
An example of named volumes: -
docker volume create mssqlsystem
docker volume create mssqluser

docker container run -d \
--publish 1433:1433 \
--volume mssqlsystem:/var/opt/mssql \
--volume mssqluser:/var/opt/sqlserver \
--env ACCEPT_EULA=Y \
--env MSSQL_SA_PASSWORD=Testing1122 \
--env MSSQL_DATA_DIR=/var/opt/sqlserver \
--env MSSQL_LOG_DIR=/var/opt/sqlserver \
--name sqlcontainer1 \
mcr.microsoft.com/mssql/server:2019-CU6-ubuntu-18.04

Here two named volumes are created and mapped to /var/opt/mssql/data and /var/opt/sqlserver within the container. You don't actually need to individually create the volumes, if they're specified in the docker container run statement and don't already exist, docker will create them.
You can see the location of the named volume on the host by running
docker volume inspect mssqlsystem
docker volume inspect mssqluser

The location where named volumes are created can be altered by using a docker volume plugin. More details are here: -
https://dbafromthecold.com/2018/05/02/changing-the-location-of-docker-named-volumes/
Full run throughs of using bind mounts and named volumes are detailed in The SQL Server and Containers guide here: -
https://github.com/dbafromthecold/SqlServerAndContainersGuide/wiki


SSAS Cube Process Manually works but MDX query doesnt work

If you want to make sure the code you are running is exactly the code run when you process manually you should click the script button in SSMS and use that XMLA script. If you execute a series of steps you should script each step.

This will generate the scripts as XML so you can execute in SSMS and that will 100% do the same as clicking the OK button on the dialog.

If that works you should get output like this (empty result)

<return xmlns="urn:schemas-microsoft-com:xml-analysis">
  <results xmlns="http://schemas.microsoft.com/analysisservices/2003/xmla-multipleresults">
    <root xmlns="urn:schemas-microsoft-com:xml-analysis:empty" />
  </results>
</return>

If that doesn't work you should get a response indicating an error like this

<return xmlns="urn:schemas-microsoft-com:xml-analysis">
  <results xmlns="http://schemas.microsoft.com/analysisservices/2003/xmla-multipleresults">
    <root xmlns="urn:schemas-microsoft-com:xml-analysis:empty">
      <Exception xmlns="urn:schemas-microsoft-com:xml-analysis:exception" />
      <Messages xmlns="urn:schemas-microsoft-com:xml-analysis:exception">
        <Error ErrorCode="3239313412" Description="Errors in the metadata manager. Either the database with the ID of 'testA' does not exist in the server with the ID of 'MES-TVG-80-U15', or the user does not have permissions to access the object." Source="Microsoft SQL Server 2016 Analysis Services" HelpFile="" />
      </Messages>
    </root>
  </results>
</return>

Once you have the correct script you can paste that in your agent job. If that doesn't work you should be looking for either errors in your agent job, or checking to see if the agent job is connecting to the right server, processing the right database, using the correct credentials etc.

Side note: If you do have multiple steps, and as such multiple XMLA scripts you can combine them using a  tag like I show here: how to delete multiple ssas database using xmla script


SQL Server Trigger does not update all table records

Currently, your trigger is updating all rows.  Modify it to use the inserted virtual table, something like this:

ALTER TRIGGER [dbo].[TRIG_Update_Inventory_Ind_Trees_Abs_Plot_Numb]
ON [dbo].[Plot_Location]
AFTER INSERT
AS
BEGIN
    UPDATE Inventory_Ind_Trees
    SET Inventory_Ind_Trees.Abs_Plot_Numb = Plot_Location.Abs_Plot_Numb
    FROM Plot_Location
    WHERE EXISTS (
            SELECT 1
            FROM inserted i
            WHERE i.Invent_Plot_ID = Plot_Location.Invent_Plot_ID
        )
        AND Plot_Location.Invent_Plot_ID = Inventory_Ind_Trees.Invent_Plot_ID;
END

This may not be exactly correct; it's hard to tell exactly where to join the inserted virtual table with your other tables.  Essentially, the idea is the inserted virtual table only contains rows that were either inserted or updated in the Plot_Location table.

You shouldn't check the @@ROWCOUNT system function at the start.  It may not reflect the  rowcount you think it does.  Instead you should select the count of rows in the inserted table.  Like this:

DECLARE @rc INT;
SELECT @rc = COUNT(1)
FROM inserted;

IF @rc > 0
BEGIN
    --body of trigger  goes here.
END


INSERT MASTER.sys.sysdatabases result into a temporary table

Dan Guzman was right (in a comment he deleted) that you can just use master.sys.master_files, and that you should certainly avoid old views like sysdatabases and sysfiles, but if you need to do a similar thing using data that is not conveniently rolled up into master for you, you can do something like this:

DECLARE @db sysname, @sql nvarchar(max), @exec nvarchar(max);

DECLARE c CURSOR LOCAL FAST_FORWARD
  FOR SELECT name FROM sys.databases
    WHERE database_id > 4
      AND state = 0;

OPEN c;

CREATE TABLE #files(db sysname, fn nvarchar(1000), sz int);

SET @sql = N'INSERT #files(db,fn,sz)
    SELECT @db, physical_name, size
    FROM sys.database_files;';

FETCH NEXT FROM c INTO @db;

WHILE @@FETCH_STATUS <> -1
BEGIN
  SET @exec = QUOTENAME(@db) + N'.sys.sp_executesql';
  EXEC @exec @sql, N'@db sysname', @db;
  FETCH NEXT FROM c INTO @db;
END

CLOSE c; DEALLOCATE c;

SELECT db, fn, sz FROM #files;

DROP TABLE #files;

Notice no clunky string concatenation, no messing with 16 consecutive single quotes, and what happens in your code if a database is named oh'crap? I keep databases like this around for exactly this reason:

  Msg 105, Level 15, State 1, Line 1
  Unclosed quotation mark after the character string 'as DB_name, SF.filename, SF.size FROM sys.sysfiles SF'.

However, you could just change the script you have right now to just do this:

INSERT #temptable(columns) EXEC sys.sp_executesql @Command;

You might also be interested in the procedure I wrote, sp_foreachdb, as surely along the way someone will suggest you use sp_MSForEachDB, which is undocumented, unsupported, and horribly broken.

Making a more reliable and flexible sp_MSforeachdb

It's also part of Brent Ozar's First Responder Kit.


Since you want to copy your database to another server I'd suggest doing this by the concept of backup restore. This is example code but you can easily also use the GUI (http://www.sqlteam.com/article/backup-and-restore-in-sql-server-full-backups), make sure that the service account has access the place you are going to write your backup too

BACKUP DATABASE [YourDatabaseName] TO  DISK = N'\\someshare\YourDatabase.bak' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'YourDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

and then Restore the database on the destination server, also here make sure that the service account of SQL Server has access to the location where the backup is stored

USE [master]
RESTORE DATABASE [YourDatabase]
FROM  DISK = N'\\someshare\YourDatabase.bak' WITH  FILE = 1,
MOVE N'DatabaseFileName' TO N'SomeDisk\YourDatabase.mdf',
MOVE N'DatabaseFileName2' TO N'SomeDisk\YourDatabase.ndf',
MOVE N'N'DatabaseLogFileName' TO N'SomeDisk\YourDatabase.ldf',  NOUNLOAD,  STATS = 5

Or you can use DBA-Tools to do it for you using powershell

Check out
https://dbatools.io/functions/backup-dbadatabase/
&
https://dbatools.io/functions/restore-dbadatabase/

EDIT

Using dba-tools you can easily script this for a list of databases.

Because you want minimum of downtime, I suggest to restore the full db-backup to the server & leave it in recovery mode, and then start applying all logs to the database in recovery until you have all data in sync which you want in sync and restore the last log with recovery

For the destination server where you already have the databases on, I would suggest restoring the db's to a different name and then renaming the old db when you have restored the database to that side. This way the downtime will only be the rename of the db, which will require a DB level lock.

The way of Backup restore is more reliable, especially for the amount of data that has to be copied.


Messaging:- Need to find last child of its parent with in the same table

Because of my hard work I got the answer:-

SELECT
    parent.pkUserDiscussionID,
    child.UserDiscussionMessage AS childmsg,
    parent.UserDiscussionTitle,
    parent.UserDiscussionMessage AS parentmsg
FROM user_discussion parent
LEFT JOIN user_discussion child
    ON child.fkDiscussionParentID = parent.pkUserDiscussionID
    AND child.pkUserDiscussionID =
    (
        SELECT max( pkUserDiscussionID )
        FROM user_discussion AS c
        WHERE c.fkDiscussionParentID = parent.pkUserDiscussionID
    )
WHERE
    parent.fkDiscussionParentID =0
GROUP BY
    parent.pkUserDiscussionID
ORDER BY
    parent.pkUserDiscussionID ASC
LIMIT 0 , 30

Can anyone suggest anything better than this?


If an INNER JOIN in a query returns no rows, do additional INNER JOINS add a significant performance overhead?

The order in which tables are joined isn't defined in the query (unless you use a join hint, which I wouldn't recommend), so you can't be sure that A is joined to B before B is joined to C.

To answer your question, in most cases SQL Server can tell from the statistics on the table (or other criteria) that the query won't return any rows. In that case, another JOIN won't make any difference from a pure performance perspective.


Can I disable an account in mysql after x failed login attempts?

Also you can use fail2ban for this goal as too easily way . In this case you just install it on you linux OS, then enable the section for [mysqld-iptables] in the /etc/fail2ban/jail.local.

[mysqld-iptables]
enabled  = true
filter   = mysqld-auth
action   = iptables[name=mysql, port=3306, protocol=tcp]
       sendmail-whois[name=MySQL, dest=root, sender=fail2ban@example.com]
logpath  = /var/log/mysqld.log
maxretry = 5

This program check the mysql logs by its own given pattern and then blocks the IP addresses which they try to login more than 5 times, in iptables.


What other databases have a feature similar to Oracle's Flashback Query?

PostgreSQL does not currently have it.  But you are right, with MVCC it should theoretically be possible.  In fact, the old university Postgres had an equivalent feature called time travel, but it was later removed because it caused too much overhead and the practical use was limited.  While it would probably be possible to resurrect it, since then there have been many optimizations to the MVCC setup that would make it much harder.


Will query continue to run even after network times-out

If your database connection is terminated without properly closing it (from the server side: if the client just silently dies), and running query will continue until one of these two things happen:

The query produces a result row, the server tries to send it to the client and gets a network error.
The operating system starts sending keepalive packages, because the connection has been idle for a long time (by default, 2 hours). The system will lend a couple of these packages, and when there is no answer, the connection is closed. As soon as PostgreSQL checks for interrupts, the running query should terminate.

You can set the tcp_keepalives_idle parameter to adjust that timeout. This can help the server to detect broken connections more quickly.

When the client is gone, and the query is still running, system resources are used, but there is no danger that this will cause any modifications in the database: eventually the transaction (be it a single statement or a longer transaction) will encounter an error and will be rolled back.


automatic undo for version control in mysql

I'd like to be able to make a backup, execute migration script and get undo script automatically.

Why?

If the migration fails so badly that it's going to be abandoned, then restore your backup of the database.
That's the only way to guarantee that you'll get back to the point you started from.

I don't know of any way to create an "undo" script but, even if you could, there's no guarantee that it would work, potentially failing part way through its "recovery" and leaving you in an even worse position, somewhere between the last known Good State and the point where the migration failed but you won't be exactly sure where.  From then on, you're clutching at straws to try and clean up the mess.

Far better ( / easier / more reliable) to toss the "corrupted" database in the bin and restore it from the backup.


You can go through the following embedded json document to implement
  your message collection and can ensure index on "mail_id" key to avoid
  scanning all documents. For your information here collection serve as
  table and each document serve as row of a table.

{
mail_id:string
sentMails:{
            [
                {
                sent_at: datetime,
                subject: string,
                sent_to: string,
                cc_to:[array of mailid strings],
                bcc_to:[array of mailid strings],
                attachments:[
                                {
                                attachment_name:string,
                                attachment_type:strng
                                }
                            ],
                sent_mail_content: string
                }
            ]
            }

receivedMails:{
                [
                    {
                    received_at: datetime,
                    subject: string,
                    received_from: string,
                    cc_to:[array of mailid strings],
                    attachments:[
                                    {
                                    attachment_name:string,
                                    attachment_type:strng
                                    }
                                ],
                    received_mail_content: string
                    }
                ]
            }
}

I hope it will help.


Updateable Subscriptions: Rows do not match between Publisher and Subscriber

Several years ago Hilary Cotter responded to a similar problem.  In his post he mentioned that synchronous replication is more reliable than asynchronous replication.

Rows Do Not Match Between Publisher and Subscriber

Part of Hilary's response is included:

  Your chances of having this work successfully would be better if you did a
  sync subscription (i.e. the subscriber does not have the schema and
  data in place option).

  I would advise you to drop and recreate the subscription and push it
  again, again making sure that nobody else was working on the
  publication database while you are doing this.

The poster of the question responded to this direction with:

  I have since dropped and re-created the table at the subscriber that
  wasn't updating properly and this seems to have cured the problem. ...
  I can only guess that something had gone awry in the system tables
  regarding this object.

The poster also noted that that he was using asynchronous replication and noted that it was extra work to tear down and recreate the affected referential integrity.

Nonetheless, that apparently got his replication running again.


I found the following in the source code:

  This is version 07.05.0001. For those that don't know or don't
  recall how the versioning works, the major and minor number track
  the latest release of PostgreSQL that the driver is expected to work
  with. The revision number is a bit more complex. The first two digits
  are the version number, where even numbers are development versions,
  and odd are release. The last two digits are the build number.

  In other words, this driver is build 01 of development version 00, and
  is expected to work with PostgreSQL 7.5dev. :-) The leading zeros are
  an ODBC thing...

I guess one cannot get any better answer than this :)


Unless there was some trouble with StackOverflow code formatting - It looks like the problem is the use of quotations around the 'temp' in the max select query.

(SELECT max(time1) FROM 'temp')

Since temp is a table in this case, quotations will not work. Change these single quotations to backticks as shown in the rest of your update statement.

UPDATE `temp` SET `time2`=NOW() WHERE user = 'kraso' and time1 = (SELECT max(time1) FROM `temp` where user = 'kraso')


How to use SSL/TLS between server and client with Postgres?

Do you just want to use a certificate on the server which the clients use to authenticate the server?  (This is the normal mode of operation).  The server then authenticates the clients using password challenges just as if ssl were not being used.

Or do you want to use client certificates to authenticate the clients to the server as well as the server to the clients?  This is rare, as it requires a certificate authority to issue certs to all of the users, and requires users to properly manage them.

Assuming the first of these, you don't have need root.crt files on the server.  All that you need to do now is edit postgresql.conf to turn ssl=on, and edit pg_hba.conf to either require or allow your clients to use ssl (hostssl requires it, or host allows it.)

Note that with the default client configuration (at least if the client is psql), all that you really get from using ssl is encryption, not true server authentication.  That is because the client just accepts whatever certificate the server sends it, and uses that cert to set up an encrypted channel.  So a bad guy can run a PostgreSQL server with a self-signed certificate, and if it can trick your clients to connect to it rather than your real PostgreSQL server, they will happily connect to it and send their queries to it.


SQL Server - SQL script to restore database backup file into a new database

Putting here as the comment I made did solve the problem.

The source of the MOVE syntax is the logical name of the file, not it's old physical name.  You can get the logical name by doing a FILELISTONLY restore or by querying sys.database_files if you still have access to the source.

MOVE <logical name> TO <path and name of file to use>

Additionally, most (all?) of the various wizards through Management Studio have an option to script the commands out for you.  This is invaluable for learning the syntax and various options.


Postgres, `where` condition, sort by first column, but I don't want that

You can do that if you change the way you pass the values to the WHERE clause. One way is to join to an unnested array and use the array index for sorting.

As you need a different sort order for the distinct on () you have to wrap the query in a derived table. The inner part uses an order by on the "distinct column". And the result can then be sorted by the array index.

select "HOBIS", "NAME", "URL", ... -- all columns but the `idx`
from (
  select distinct on ("HOBIS")
            product_shop_id as "HOBIS",
            ...,
            x.idx
  ...
  from s_product
    join unnest(array['237002','230041','467173','464431']) with ordinality as x(shopid, idx)
      on s_product.product_shop_id = x.shopid
    left join ...
    ...
  order by "HOBIS"
) t
order by t.idx

This behaves a bit different to an IN condition, so you have to make sure that every shop ID only appears once in the array.

  And why PG sort results when I have no sort condition in my sql?

Postgres does not sort your results.

Without an order by clause, the database is free to return the rows in any order it wants. The "order" results from the different steps in the execution plan. If you see some perceived "order", then this probably stems from retrieving rows through an index.

But the "order" you see there is in no way guaranteed. It can change because rows have been changed or inserted into the table, or because the query optimizer chooses to use a different execution plan. Or because some other transaction is running a similar statement.


It seems like its not added to SSMS 17. There is a connect report regarding this:

Link to the Connect Report


Ensure correct username when using pg_restore

If you want to create the plpgsql extension I believe you have to be Superuser.  My recommendation is to make app_user temporarily a superuser and then revert this.  Superusers also are treated as owners of everything.

ALTER USER app_user WITH SUPERUSER;

run the restore, then when you are done

ALTER USER app_user WITH NOSUPERUSER;

This should avoid any other permissions issues but still leave you with the permissions you want.


How to create a form that allows user to enter a variable number of items

You need your answer to be an application - this is too broad.

I try to give basic idea.

You need one form which will be saved to the database each time a person fills the list.

It must contain 1 text box for Persons names. One List with all cars that are on the show and one empty list. And 2 buttons - cancel and save.

For each new person operator write his name of the person. Then operator selects cars from fisrt list and transfer its to the second. This can be done by double click or by selecting a car and pressing additional button. This must work in the opposite direction too.

Then when pressing save button the data will be saved inside database and the form will be reset - first list will contain all cars from the show, second will be empty and text box will be empty.

If operator presses cancel button the form will be reset without saving data to database.

You can try serach internet how to implement those lists in MS Access 2010.


How do I access Postgres when I get an error about "/var/run/postgresql/.s.PGSQL.5432"?

If your Postgres service is up and running without any error or there is no error in starting the Postgres service and still you are getting the mentioned error, follow these steps

Step1: Running pg_lsclusters will list all the postgres clusters running on your device

eg:

Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

most probably the status will be down in your case . Try restarting Postgres clusters and service

Step 2: Restart the pg_ctlcluster

#format is pg_ctlcluster <version> <cluster> <action>
sudo pg_ctlcluster 9.6 main start

#restart postgresql service
sudo service postgresql restart

Step 3: Step 2 failed and threw an error

If this process is not successfull it will throw the error.
My error was(You can see the error log on /var/log/postgresql/postgresql-9.6-main.log)

FATAL: could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied
Try adding `postgres` user to the group `ssl-cert`

Step 4: check ownership of postgres

Make sure that postgres is the owner of /var/lib/postgresql/version_no/main
eg: sudo chown postgres -R /var/lib/postgresql/9.6/main/

Step 5: Check Postgres user belongs to ssl-cert user group

It happened to me and it turned out that I removed erroneously the Postgres user from "ssl-cert" group. Run the below code to fix the user group issue and fixing the permissions

#set user to group back with
sudo gpasswd -a postgres ssl-cert

# Fixed ownership and mode
sudo chown root:ssl-cert  /etc/ssl/private/ssl-cert-snakeoil.key
sudo chmod 740 /etc/ssl/private/ssl-cert-snakeoil.key

sudo service postgresql restart


Method 1 : Comes with a cost

You can use a third party software which will do everything for you in terms of gathering the data and presenting the reports for database growth and predicts the same depending upon the gathered data as explained here

Method 2: Create table, run the stored proc using SQL agent job and scheduled accordingly to gather the data in the created table and then query it over a period of time to see what has been collected and predicts with all you're calculations handy:

You can use this as explained here

Method 3: Little more on Manual side but works good, is using the default trace. Use the default trace to capture the growth events on data and log for that database , depending upon how frequent or how long the default trace file stays there and does not get rolled over:

Method 4 My personal favorite and the one i have been using:

This excellent article from Chad Miller Database capacity planning which uses T-SQL and Powershell along with SSRS reporting to give you the desired database growth over a period of time.


how can I get only the SQL server logins, when we use Export-dbalogin

As you can see from the Get-DbaLogin source, the -Type parameter accepts a value of 'SQL' to filter to only SQL Logins and exclude AD logins. You can pipe this directly into the Export-DbaLogin function like so...

Get-DbaLogin `
    -Type 'SQL' `
    -SqlInstance localhost |
Export-DbaLogin `
    -FilePath 'foo.sql'

Note that some logins are excluded by default as shown in the warning feed.


SQL Server Express on Windows 8 isn't locking MDF database files

SQL Express creates database with AUTO_CLOSE ON by default. Auto close databases automatically close themself (and thus release the file handles) whenever there are no sessions using the database. This works 'as advertised', you just have to read the small print:

  True for all databases when using SQL Server 2000 Desktop Engine or
  SQL Server Express, and False for all other editions, regardless of
  operating system.

If you don't like the default behavior, simply turn auto-close off on the databases:

ALTER DATABASE [<dbname>] SET AUTO_CLOSE OFF;


Start MongoDB With Authentication Enabled

Regarding the issue described in above mentioned post I suggest you to check  authentication options into mongodb configuration file.

# Turn on/off security.  Off is currently the default
#noauth = true
auth = true

And after it initialize mongod service using auth mode.

sudo mongod --auth --dbpath /var/lib/mongodb

Auth configuration option should be set to true and accordingly mongo service will start in authentication mode.


What are all the XTP_THREAD_POOL processes doing?

Information about these tasks is, indeed, hard to come by.

The docs (Creating and Managing Storage for In-Memory OLTP -> Scalability) say that

  In SQL Server 2016 (13.x), there are multiple concurrent threads responsible to persist changes to memory-optimized tables.

And while the docs don't mention the "cmd" value that might be associated with these concurrent threads, there is a PowerPoint from Bob Ward that links to the doc page above and adds more details about this process:

  You may see these as command = XTP_THREAD_POOL or XTP_PREEPMTIVE_TASK

So these tasks are keeping the data and delta files used by in-memory OLTP up-to-date.  They run in the background all the time.  While they are idle, they rack up DISPATCHER_QUEUE_SEMAPHORE waits.

The presentation also mentions that:

  Pools should get no bigger than # logical CPUs...

So it's a little surprising to see 6 of these processes running on a 2-processor box.  This might be a bug, or things could have changed since Bob's PowerPoint presentation.  Hopefully someone with more Hekaton smarts than me will come by and weigh in.

HTH


Column does not exists when accessing inner type field in ORDER BY

Explanation for the error

You get the error message:

  ERROR: column "tp" does not exist

because you cannot use output columns in another expression in the ORDER BY clause. You can only reference output column names as a whole. Expressions must be built on input columns. The manual:

  Each expression can be the name or ordinal number of an output column
  (SELECT list item), or it can be an arbitrary expression formed from
  input-column values.

Bold emphasis mine.

Solution

You would need the row type as input to your query to involve it in expressions in ORDER BY. You can do that with a LATERAL join:

SELECT
    something_a,
    something_b,
    ARRAY (
      SELECT (a_uuid, my_child)::a_schema.type_parent AS tp
      FROM   a_schema.a_table
      LEFT   JOIN LATERAL (SELECT get_a_type_child(a_type_child_id) AS my_child) t ON true
      ORDER  BY (t.my_child).an_order
      )
FROM ....

Why LEFT JOIN LATERAL () ON true?

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

Asides

The solution is merely a proof of concept. You need intimate knowledge of row and composite type handling to cope with this setup, and it will still be awkward and slow. Your setup is more complicated than necessary to begin with. I would have to type so much to explain everything, it's just not worth it.

I agree with @a_horse: a properly normalized schema will save you a lot of headache.

The function can be simpler, but there is nothing wrong with PL/pgSQL per se. SECURITY DEFINER functions are exempt from function inlining anyway. There are other pros and cons for SQL vs. PL/pgSQL functions:

Difference between language sql and language plpgsql in PostgreSQL functions

Note: functions. Your term stored procedure is a bit off, since Postgres does not have true stored procedures. Just functions - doing almost but not quite the same.


It seems like insufficient info to tell something definitely.  For example, are you deleting through Transact-SQL or through Win32API, etc.

The FILESTREAM data is not deleted immediately from file system because SQL Server transaction logging under full and bulk recovery models permit the crash recovery.

Have you deleted with CHECKPOINT

delete from tablename CHECKPOINT

or tried to execute CHECKPOINT statement or use simple recovery model?

Also, IMO,:

changing of FILESTREAM (re)configuration require restart of SQL Server
FILESTREAM data is restricted to LOCAL only drives
FILESTREAM operations depend on hardware, see Paul Randal's white paper
"deleting or renaming any FILESTREAM files directly through the file system will result in database corruption"

Check Security and Reliability sections of Paul Randal's white paper

Related  discussions containing many subreferences:

SQL Server Filestream - Delete “speed”
How to force the filestream garbage collector to complete its work with the highest priority?

Update:
Have you checked permissions of FILESTREAM (Windows) share/container vs. account under which SQL Server runs  under. It should have local administrator permissions.   It is recommended that no other account be granted permissions on the data container

Update2:
From Paul Randal's white paper:

"The file system access open operations do not wait for any locks. Instead, the open operations fail immediately if they cannot access the data because of transaction isolation. The streaming API calls fail with ERROR_SHARING_VIOLATION if the open operation cannot continue because of isolation violation"
"Antivirus software ... access to the BLOB data in the affected file will be prevented, and to SQL Server the file will appear to have been deleted."
"Note that for a table to have one or more FILESTREAM columns, it must also have a column of the uniqueidentifier data type that has the ROWGUIDCOL attribute. This column must not allow null values and must have either a UNIQUE or PRIMARY KEY single-column constraint"
"anything that can prevent transaction log truncation may also prevent a FILESTREAM file being physically deleted. Some examples are:"
FILESTREAM data containers cannot be nested

Run
DBCC CHECKDB

Update3:
I cannot coach you online. This is Q&A board. I already had been banned many times for infringing the rules on StackExchange having discussions instead of posting question or answer

Run

DBCC CheckDB (QPS8, repair_rebuild)

DBCC CheckDB (QPS8) WITH No_INFOMSGS, ALL_ERRORMSGS

and put output here.

To check/set NTFS permissions: right-click on folder in Windows --> Properties --> Security

To check/set MSSQLServer:  in command line type

SQLServerManager10.msc

navigate to SQL Server Services and double-click corresponding instance of SQL Server

Update 3b:
Probably, you should drop and recreate index or you have further deeper issues and you should restore database from backup or perform database repair permitting loss of data. This is quite separate topic/question.
I do not want to take any responsibility to guide you on it.


What happens if sys.aud$ is full in Oracle?

Your database essentially freezes and does not allow any new connections or transactions that would be writing into the audit logs.  Your only bet would be to connect directly on the server, e.g. "sqlplus / as sysdba" and then add space (or truncate it if auditing is not important to that database).

For this reason we have scripts to backup aud$ and truncate it regularly.  We don't want horrible freezing going on in production...

You also want to move aud$ away from SYSTEM, something like this:

alter table sys.aud$ move tablespace aud_data;
alter table sys.aud$ move lob (sqlbind) store as (tablespace aud_data);
alter table sys.aud$ move lob (sqltext) store as (tablespace aud_data);
alter index sys.I_AUD1 rebuild tablespace aud_idx;


Time Series data for controllers connected to other devices

I just did a basic calculation: assuming a row size of 200Byte, you'll create more than one TB raw data per year, 3GB per day.
This raised a few questions:
What is your strategy for data compression (e.g. get the min/max values per day/month, store it separately and remove/archive the raw data)?
How will the data be used (what kind of queries, how often)?
How often will controllers be added or removed?
What is the data distribution of devices per controller?

These are the key question in order to modify the (normalized) design concept, if needed.

In a normalized form I'd go with the following design:
tables:
Controllers (ControllerId, ConSerialNumber,ConDescription,...)
Installations (InstallId, ControllerId, InstLocation, InstInstallDate, InstRemovedDate,..) -- to allow a reuse of controllers at different locations
Devices (DeviceId, DevSerialNumber,DevDescription,...)
Device_per_Installation (DpIId,InstallId,DeviceId,DevInstallDate, DevRemovedDate,...)
DeviceData (DpIId, SendDate, Param1, Param2,...) -- I assume you'll shred the JSON data to get the actual values instead of the JSON file as a whole in order to save overhead

Add a controller can be done automatically (without any manual code or even data manipulation): the new data can be inserted in the controllers and installations tables if the information does'n already exist.
In order to deal with the data volume I'd use horizontal partitioning (per Installation, most probably)

Alternatively, you could use a separate table per controller,
But this would either force you to use dynamic SQL or to modify the code whenever a new controller is added. This will reduce the size per table to about 30GB per year, but the overall data volume will still be about 1TB.

Your idea to have one column per device indicates that you're not planning to "shred" the JSON data.
If you ever plan to query those columns to aggregate the values of a specific parameter, it'll take a significant amount of time, since all rows have to be scanned and "shredded" to get the values.
But if you don't plan to query the data anyway, I'd question the need to store it in a database in the first place. Store it on the file system instead...

To summarize:
The database design might be influenced by the specific business case due to the data volume. I recommend to get a database expert involved to help you design the best performing solution, not only for the table design, but for the code to insert/query/aggregate/archive the data as well as database maintenance, too. Otherwise you'll end up with major performance or maintainability issues sooner or later.


Not sure whether this is actually correct, but I'll use it as an estimate until I figure out something better:

CREATE OR REPLACE FUNCTION GetInxSize(indschema VARCHAR(128), indname VARCHAR(128))
RETURNS INTEGER
SPECIFIC GETINXSIZE
READS SQL DATA
DETERMINISTIC
NO EXTERNAL ACTION
    RETURN
        SELECT SUM(
                    CASE c.TYPENAME
                       WHEN 'SMALLINT'        THEN length
                       WHEN 'INTEGER'         THEN length
                       WHEN 'BIGINT'          THEN length
                       WHEN 'REAL'            THEN length
                       WHEN 'DOUBLE'          THEN length
                       WHEN 'DECFLOAT'        THEN length
                       WHEN 'DECIMAL'         THEN TRUNC(length / 2) + 1
                       WHEN 'CHARACTER'       THEN 4*length
                       WHEN 'VARCHAR'         THEN 4*length + 4
                       WHEN 'GRAPHIC'         THEN length * 2
                       WHEN 'VARGRAPHIC'      THEN length * 2 + 4
                       WHEN 'XML'             THEN 4*c.inline_length + 3
                       WHEN 'DATE'            THEN length
                       WHEN 'TIME'            THEN length
                       WHEN 'TIMESTAMP'       THEN length
                       ELSE raise_error('78000', 'Unknown type')
                    END +
                       CASE WHEN NULLS = 'Y' THEN 1 ELSE 0 END
                 )
        FROM SYSCAT.INDEXES i
        JOIN SYSCAT.INDEXCOLUSE ic
            ON i.indschema = ic.indschema
            AND i.indname = ic.indname
        JOIN SYSCAT.COLUMNS c
            ON i.tabschema = c.tabschema
            AND i.tabname = c.tabname
            AND ic.colname = c.colname
        WHERE i.indschema = GetInxSize.indschema
          AND i.indname = GetInxSize.indname
@

The DB:s in question don't use compression, and I also skipped types that can not be part of an index.

SELECT rtrim(indschema) || '.' || rtrim(indname)
FROM SYSCAT.INDEXES i
JOIN SYSCAT.TABLES t
    ON i.tabschema = t.tabschema
    AND i.tabname = t.tabname
JOIN SYSCAT.TABLESPACES ts
    ON coalesce(t.index_tbspace, t.tbspace)
WHERE t.tabschema not like 'SYS%'
  AND getinxsize(indschema, indname) > ts.pagesize / 4;

alt:

SELECT distinct rtrim(t.tabschema) || '.' || rtrim(t.tabname)
FROM SYSCAT.INDEXES i
JOIN SYSCAT.TABLES t
    ON i.tabschema = t.tabschema
    AND i.tabname = t.tabname
JOIN SYSCAT.TABLESPACES ts
    ON coalesce(t.index_tbspace, t.tbspace) = ts.tbspace
WHERE t.tabschema not like 'SYS%'
  AND getinxsize(indschema, indname) > ts.pagesize / 4;

EDIT: Example of XML column in index

A table like:

CREATE TABLE TESTING_APA.ATOM_FEED
( FEED_XML XML
, FEED_XML_IS_NULL SMALLINT NOT NULL
      GENERATED ALWAYS AS (CASE WHEN FEED_XML IS NULL THEN 1 ELSE 0 END)
) IN USERSPACE1
ORGANIZE BY ROW;

Generates two indexes (not sure why though) where type name of the column is XML:

select tabschema, tabname, indname, colnames
from syscat.indexes
where indname in ('SQL190304085605540','SQL190304085605550')

TESTING_APA  ATOM_FEED  SQL190304085605540  +FEED_XML
TESTING_APA  ATOM_FEED  SQL190304085605550  +FEED_XML

In addition, explicit xml indexes also result in indexes in the catalog whose column type is XML.

Since the source database is not UTF-8, there can't be any JSON columns. For a UTF-8 database, it may be worthwhile to investigate whether indexes for JSON comes into play. The function GetInxSize is however specially designed to handle non-utf -> utf, so it will have to be changed for utf-8 databases.


PostgreSql allocate memory for each connection

When we configure the memory setting for the Postgres DB what is the
  recommended memory allocation for each connection ? Is there any
  formula to apply ?

There is no per-connection pre-allocated memory that a DBA would define. A session will allocate dynamically what is needed, or use the shared memory pre-allocated at server start depending on the kind of usage.
 See Resource consumption in the doc about what can be defined.

  I know 25% of the memory of the Server should be allocated. But how do
  we allocate based on the DB connections? How do we know, what is the
  maximum number of connections should be allocated?

The maximum number of client connections (max_connections) is factored into the amount of shared memory that is pre-allocated at server start.
In Managing Kernel Resources, the doc says that its size in bytes is:
(1800 + 270 * max_locks_per_transaction) * max_connections

But memory is not the main factor in deciding the maximum number of connections, it's the raw power and number of cores of the machine, and wether a connection pooler is going to be associated to the server. The point is mostly to avoid too many concurrent active queries provoking the server to halt to a crawl.

  Also in a multi-nodes environment can we allocate more connections for
  each node (in Postgres-ds.xml max connections) than what is actually
  allocated in the DB?

If there are multiple client nodes that point to the same PostgreSQL instance, the combined count of their connections cannot outreach max_connections.

However when using a connection pooler, connection as a word becomes ambiguous. You want to know whether it's a connection from the client to the pooler or a connection from the pooler to the database server. Generally there are much more allowed connections to the pool than to the database.


Differential backups are backups of any changes to the DB since the last full backup. So in theory, yes, you could have it do a diff backup since some date (if you did a full backup on that date). Otherwise, no. It'll always be since the last full backup.


As always it depends...

Whats the complexity of your instance?
How many SSIS packages / SSRS reports do you need to move along?
How much time do you have to perform this upgrade?
Whats your maintenance windows within which the service can be inaccessible?
And many many more...

Before you start looking for any kind of answers I would recommend to create an inventory of what exactly do you have.
Very helpful thing is to add source control over your entire DB server. With that in hand you can "mask it a bit" and then share with the community - much higher chances for good answers.


How to solve replication in MongoDB only with two machines?

We know that the Replica Set does not work by itself with two machines, but we have this limitation.

If your goal is high availability with automated failover I would definitely aim for a solution that does not involve reconfiguring the replica set or allow for the possibility of multiple active primaries.

Ideally this does require a third machine so you have a tie-breaking vote to allow  either MongoDB 1 or MongoDB 2 to become primary in the event of a partition between the two. The third machine can either host another secondary (recommended) or a voting-only arbiter (not recommended for HA as it cannot contribute to majority write acknowledgement).

  Whenever Wildfly 1 drops, Wildfly 2 raises by reconfiguring MongoDB 2 to be primary by changing the settings of the ReplicaSet.

If MongoDB 2 is a secondary, this scenario only works if you force reconfigure (since a majority of voting replica set members are not available).

As noted in the documentation, force reconfiguration is a last resort for recovering a replica set rather than a procedure you should frequently use or automate:

Use this procedure only to recover from catastrophic interruptions. Do not use force
every time you reconfigure. Also, do not use the force option in any automatic scripts
and do not use force when there is still a primary.

  If a split occurs on the network, Wildfly 1 continues to work writing in MongoDB 1, Wildfly 2 is also active, changes the MongoDB 2 configuration to primary, and begins writing to it.

A primary can only be elected or sustained in a partition with a majority of voting members. With only two voting members, a network split will result in the current primary stepping down (since it can't see a majority of the voting members) and no primary elected.

  What happens to MongoDB when reestablishing the network and Wildfly 1 take over as principal?

If you forced MongoDB 2 to become primary, it will have a replica set configuration with a significantly higher replica set configuration version. MongoDB 2 may also have writes that haven't replicated to MongoDB 1 yet, so there are two stages of recovery:

If MongoDB 1 accepted any writes that were not replicated to MongoDB 2, MongoDB 1 will enter rollback mode to try to revert write operations to a common point in the oplog. If too many operations have been written on MongoDB 2 or MongoDB 1 no longer has a common point in its oplog, MongoDB 1 will have to be resynced.
Assuming both members now have a consistent oplog history, MongoDB 1 will resume syncing (as a secondary) to try to catch up write operations that have been applied on MongoDB 2

If both of those stages are successful, MongoDB 1 should be eligible to become primary. MongoDB 1 won't automatically become primary unless it has a higher priority than MongoDB 2.


Why does the service broker require a master key after restoring to a different server?

When you restore a database, TRUSTWORTHY is automatically set to OFF.  For Service Broker, if you don't use encryption and do use cross-database message transmission, TRUSTWORTHY needs to be set to ON.

Try

ALTER DATABASE MyDB SET TRUSTWORTHY ON

...for all restored databases involved with Broker.


innodb_log_file_size not upating even after changing my.cnf

The buffer_pool_size seems rather low.  How much RAM do you have?

innodb_log_file_size can be virtually any size.  Make it as big as innodb_buffer_pool_size.

But, and this may be the problem, the setting must be in a section started by

[mysqld]

See also: https://www.percona.com/blog/2016/05/31/what-is-a-big-innodb_log_file_size/


I know it's not your main question, but you mentioned slow performance. When checking if a certain character or substring exists within a larger string, you should try using CHARINDEX('x', strColumn) instead of LIKE '%x%'. This will boost your performance drastically. Using LIKE with % surrounding your search-term is very slow and costly.


Oracle function based index with empty function name

That is not an unnamed/empty function. That is a column expression made from a constant, between parentheses.

And it is listed as function-based index, because:

CREATE INDEX

  When you specify column_expression, you create a function-based index.

The below is a function-based index too by definition, while it is nothing more than the a constant for all rows.

CREATE INDEX IDX3 ON t('a');


How to create a tablespace on Windows 7 for Postgresql 9.2?

For PostgreSQL 9.2 and above there is no postgres system user (by default). It runs as NETWORK SERVICE.

Create d:\tablespaces and give the NETWORK SERVICE (SERVICE RÉSEAU in french) "Full control" over it. That should do the trick.


How to know if mysql tmpdir is being used

In *nix, a file can be creat'd, rm'd, then written to and read from by the same process.  No flavor of ls can see it, since it is already rm'd from the directory, yet the program can see it.  This is a trick that makes sure that the file is not left behind if the program crashes.  MySQL often uses the rm trick.

ls cannot see the rm'd-but-not-closed files, but lsof can see them as (deleted).

df will change because of the space being consumed.

If you show us the SELECT, SHOW CREATE TABLE and EXPLAIN SELECT.  We may be able to give you tips on avoiding the tmp table.  (I assume your real question is about performance?)


SQL query for all combinations without repetition

You can use regular joins:

SQL> WITH DATA AS
  2   (SELECT 'Tom' NAME
  3      FROM dual
  4    UNION ALL
  5    SELECT 'Bob' NAME
  6      FROM dual
  7    UNION ALL
  8    SELECT 'Paul' NAME
  9      FROM dual
 10    UNION ALL
 11    SELECT 'Tina' NAME
 12      FROM dual)
 13  SELECT *
 14    FROM DATA d1
 15    JOIN DATA d2 ON d1.name > d2.name
 16    JOIN DATA d3 ON d2.name > d3.name;

NAME NAME NAME
---- ---- ----
Tom  Tina Bob
Tom  Tina Paul
Tom  Paul Bob
Tina Paul Bob


How does mysql deal with queries that touches myisam and innodb tables?

There are three aspects to doing a JOIN query that is can be alarming
ASPECT #1 : Locking Behavior
Whenever there are joins involving MyISAM and InnoDB, InnoDB tables will end up behaving like MyISAM in terms of table-level locking instead of its normal row-level locking.MVCC and ACID compliance cannot be applied to the MyISAM data. It could possibly be stagnated for InnoDB tables as well.
ASPECT #2 : MyISAM's Involvement
If any MyISAM table is being updated via INSERTs, UPDATEs, or DELETEs, the MyISAM table involved in a JOIN query would be locked from other DB Connections, forcing the JOIN query to wait until the MyISAM table can be read. In light of this, if there is a mix of InnoDB and MyISAM in a JOIN query, the InnoDB tables would be subject to intermittent locking of PRIMARY KEY entries (in the Clustered Index).
Keep in mind that MVCC will still allow READ-UNCOMMITTED and REPEATABLE-READ transactions to work just fine and let certain views of data be available for other transactions. The same cannot be said for READ-COMMITTED and SERIALIZABLE transactions.
ASPECT #3 : Query Optimizer's View of the JOIN
MyISAM
MySQL will rely on the index cardinality of tables to determine an optimized EXPLAIN plan. Index cardinality is usually stable for a MyISAM table until it is inundated with INSERTs, UPDATEs, and DELETEs. Consequently, you will needs to periodically run OPTIMIZE TABLE against the MyISAM table.
InnoDB
InnoDB's index cardinality is NEVER STABLE !!! If you run SHOW INDEXES FROM innodbtable;, you will see the index cardinality change each time you run that command. That's because InnoDB will do dives into the index to estimate the cardinality. Even if you run OPTIMIZE TABLE against an InnoDB table, that will only defragment the table. OPTIMIZE TABLE will run ANALYZE TABLE internally to generate index statistics against the table. That works for MyISAM. InnoDB would usually ignore it. You could disable innodb_stats_on_metadata to get stable EXPLAIN plans, but that would just cause you to do ANALYZE TABLE maintenance on InnoDB tables as much as you would a MyISAM table.
EPILOGUE
Believe it or not, there is still an open ticket on InnoDB/MyISAM joining during a SELECT FOR UPDATE. If you read it, it sums up the resolution as follows : DON'T DO JOINs THAT MIX InnoDB and MyISAM, please.


High-resolution CPU usage information for batches and statements

Durations made visible by SQL Server vary in their resolution and accuracy.

SqlWorldWide has an excellent answer showing some of the details.

Be aware of over-profiling SQL Server.  Just like in quantum physics trying to measure the duration of a query too accurately will decrease the performance of the query itself.  In other words, the very act of measuring undermines that which you are attempting to measure.  Measure the performance of a group of queries instead of trying to measure the performance of each individual statement.  If you need to understand why a single particular statement is running slowly, isolate that statement and evaluate it while letting the rest of the group run normally.  Being aware of how SQL Server operates can be tricky enough, let alone trying to see if a query duration was 6 or 600 microseconds.


MariaDB FULLTEXT search with apostrophe, and mandatory words

Works for me in both MyISAM and InnoDB.

But note:  out of 185,082 rows, 17 have "O'Brien".  That's less than 50%, so the search term is not ignored.  I verified the count with LIKE "%O'Brien%".

But, when adding the +, it needs to be outside the quotes for InnoDB.  (Yet another difference between the implementations.)

(I have updated my blog to add this diff.)