Oracle DB administrator-specific security features

I do not know if there is a SQL Server equivalent but they are probably talking about Oracle Database Vault. It allows administrators to take care of operations on the database without having access to the data. Now if your organization doesn't make the distinction between a role where DBAs handle operations functions vs DBAs that have access to data (to run DDL scripts, DML, etc) then this isn't much help. But it can help limit exposure in those situations where only a few people need to access data but where many help keep up with database operations.


Filling in date holes in grouped-by-date SQL data

I recommend using a Calendar table or Date Dimension (whichever name you prefer). Here is an answer with using a quick CTE.

/* Date Range CTE */
-- Updated based on @AaronBertrand's articles linked in the comments
-- Basically ends up being the same query as the last half of @AaronBertrand's post
declare @FromDate date;
declare @ThruDate date;
set @FromDate='2013-01-01';
set @ThruDate='2015-01-01';

with cal as (
  select top (1+datediff(day, @FromDate, @ThruDate))
      DateValue = dateadd(day, v.number, @FromDate)
    from master.dbo.spt_values v
    where v.type = 'P'
      and v.number >= 0
    order by v.number
  )
select EventDate = cal.DateValue
     , TotalEventCount = isnull(sum(EventCount), 0)
  from cal
    left join dbo.EventRecords e on cal.DateValue=e.EventDate
  group by cal.DateValue
  order by cal.DateValue

Some links about calendar tables:

Sql 2005 Calendar Tables - Why You Need One - David Stein
Sql 2008+ Creating a Date Table/Dimension on SQL 2008 - David Stein
Calendar Tables in T-SQL


How to restore SQL Server database just by dbName and physical path

Here is something that you can use. It is not fully automated but this is definitely very useful piece of script.

SET NOCOUNT ON;

DECLARE
    @DBName NVarchar(100),
    @BackupFile NVarchar(1000),
    @DeviceFrom NVarchar(1000),
    @DeviceTo NVarchar(1000),
    @LogicalName NVarchar(1000),
    @PhysicalName NVarchar(1000),
    @SQL NVarchar(MAX),
    @RowsToProcess integer,
    @CurrentRow integer,
    @Comma NVarchar(25);

SET @DBName = 'DB_Order'; --  Change this for each database
SET @BackupFile = 'E:\New folder\Thursday - DB_Order.bak'; --  Change this for each database

SELECT @DeviceFrom = SUBSTRING(physical_name, 1,
CHARINDEX(@DBName + '.mdf',
physical_name) - 1)
FROM master.sys.master_files
WHERE name = @DBName AND FILE_ID = 1;

SET @DeviceTo = 'E:\New folder\'; -- Change this if you are changing your restore location
SET @SQL = 'RESTORE DATABASE ' + @DBName + ' FROM DISK = ''' + @BackupFile + ''' WITH ';
SET @CurrentRow = 0;
SET @Comma = ',';

DECLARE @FileList TABLE (
    RowID int not null primary key identity(1,1)
    ,LogicalName NVARCHAR(128)
    ,PhysicalName NVARCHAR(260)
    ,Type CHAR(1)
    ,FileGroupName NVARCHAR(128)
    ,Size numeric(20,0)
    ,MaxSize numeric(20,0)
    ,FileId BIGINT
    ,CreateLSN numeric(25,0)
    ,DropLSN numeric(25,0)
    ,UniqueId uniqueidentifier
    ,ReadOnlyLSN numeric(25,0)
    ,ReadWriteLSN numeric(25,0)
    ,BackupSizeInBytes BIGINT
    ,SourceBlockSize BIGINT
    ,FilegroupId BIGINT
    ,LogGroupGUID uniqueidentifier
    ,DifferentialBaseLSN numeric(25)
    ,DifferentialBaseGUID uniqueidentifier
    ,IsReadOnly BIGINT
    ,IsPresent BIGINT
    ,TDEThumbprint VARBINARY(32) -- Remove this line for SQL Server 2005
    );

INSERT INTO @FileList
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @BackupFile + '''')
SET @RowsToProcess = @@RowCount;

WHILE @CurrentRow < @RowsToProcess
BEGIN
    SET @CurrentRow= @CurrentRow + 1;
    BEGIN
    IF @CurrentRow = @RowsToProcess
        SET @Comma = ',STATS=1';
    END
    SELECT @LogicalName = LogicalName,@PhysicalName = PhysicalName FROM @FileList WHERE RowID=@CurrentRow;
    SET @PhysicalName = Replace(@PhysicalName,@DeviceFrom,@DeviceTo);
    SET @SQL = @SQL + 'MOVE ''' + @LogicalName + ''' TO ''' + @PhysicalName + '''' + @Comma + '';
END

SELECT @SQL;
--EXEC(@SQL); -- Execute when you are ready.

You can use SQLCMD / Batch file to pass in the parameters of your DB name and physical file name and try to automate it.


Disallow superusers and single-user mode in POSTGRESQL

You will find that you need superuser access for certain tasks, e.g. CREATE EXTENSION.

Vieving database metadata is not a security problem. Just because you know that there is a column with credit card data doesn't get you any closer to accessing it.

There is no way to keep somebody with shell access as PostgreSQL OS user from doing anything they want. Whoever can read the data files has access to all information.

Rather than spending effort on breaking PostgreSQL, invest in proper security:

Restrict shell access to the database machine.
Don't allow remote superuser access.
Encrypt connections.
Use an external, centralized authentication provider.
Grant object permissions as restrictive as possible.


How can I calculate time spent at work in a single column?

DECLARE @d TABLE(dt DATETIME);

INSERT @d VALUES
('2013-02-22 09:09:00'),
('2013-02-22 13:28:09'),
('2013-02-25 09:43:16'),
('2013-02-25 15:15:19'),
('2013-02-25 19:30:00'),
('2013-02-25 21:30:00'),
('2013-02-26 08:27:54'),
('2013-02-26 16:31:51'),
('2013-02-27 08:52:59'),
('2013-02-27 15:08:35'),
('2013-02-28 08:37:54'),
('2013-02-28 16:37:08'),
('2013-02-28 18:30:00'),
('2013-02-28 20:30:00');

;WITH x AS
(
  SELECT
   e = dt,
   r = ROW_NUMBER() OVER (ORDER BY dt),
   p = LAG(dt, 1) OVER (ORDER BY dt),
   m = DATEDIFF(MINUTE, LAG(dt, 1) OVER (ORDER BY dt), dt)
 FROM @d
)
SELECT
  start = p,
  [end] = e,
  [minutes_elapsed] = m,
  [time] = CONVERT(TIME(0), DATEADD(MINUTE, m, 0))
 FROM x
 WHERE r % 2 = 0
 ORDER BY e;

If you need to do this on older versions (2005 -> 2008 R2):

;WITH cte1 AS
(
  SELECT
   e = dt,
   r = ROW_NUMBER() OVER (ORDER BY dt)
  FROM @d
),
cte2 AS
(
  SELECT
    x.r,
    [start] = x.e,
    [end] = y.e,
    m = DATEDIFF(MINUTE, x.e, y.e)
  FROM cte1 AS x INNER JOIN cte1 AS y
  ON x.r = y.r - 1
  WHERE y.r % 2 = 0
)
SELECT
  [start],
  [end],
  minutes_elapsed = m,
  [time] = CONVERT(CHAR(8), DATEADD(MINUTE, m, 0), 108)
 FROM cte2
 ORDER BY [start];

Suggested schema for recording time in/time out is pretty simple:

CREATE TABLE dbo.Shifts
(
  EmployeeID INT NOT NULL, -- probably Foreign Key, right?
  ClockIn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ClockOut DATETIME NULL
);

Now your query is:

SELECT
  EmployeeID,
  ClockIn,
  ClockOut,
  ShiftLength = CONVERT(TIME(0), DATEADD(MINUTE,
    DATEDIFF(MINUTE, ClockIn,
      COALESCE(ClockOut, CURRENT_TIMESTAMP) -- to capture shift in progress
    ), 0))
FROM dbo.Shifts
WHERE ...


Are there any risks on using 'single-transaction' and 'extended-insert' on mysqldump?

this way is faster because

Server does not need to perform fsync() multiple times during data import. fsync is the operation of submitting data into disk
Extended insert enables sequential write to disk which means disk does not need to seek for a locations for every row.
If the table has Auto_Increment values, Server can spare adequate values for the batch insertion at once rather than once for every insertion.
The extended insert builds batch INSERT statements (many rows per INSERT). That inherently speeds things up 10-fold because of amortizing the statement overhead (Added By @Rick James in comments)

I know two side effects for this method.

undo log will get too large while performing this operation.
If an error happen during the operation, the entire import will be reverted (which might be an advantage in your case).


is it possible to pass the message from sp_add_alert to the job?

This article gives the answer. Basically the message is accessible by using SQL Server Agent Tokens and Macros.

In particular this Job Step that is calling a stored procedure worked for me nicely:

exec ProcessMyAlert '$(ESCAPE_SQUOTE(A-MSG))'

Note that I had to

  right-click SQL Server Agent in Object Explorer, select Properties,
  and on the Alert System page, select Replace tokens for all job
  responses to alerts to enable these tokens.

As per the linked article. In addition I had to restart SQL Server Agent so that this setting come into effect - it did not work until I restarted it, the article does not mention the restart however.


SET LOCK_TIMEOUT, is it session or statement based?

From the SET LOCK_TIMEOUT Books Online entry:

  "At the beginning of a connection, this setting has a value of -1.
  After it is changed, the new setting stays in effect for the remainder
  of the connection."

All the tests I have performed have reflected this documented behaviour.  Perhaps you are using different connections but getting the same session id?  Use this query to check:

SELECT
    dec.connection_id,
    dec.connect_time
FROM sys.dm_exec_connections AS dec
WHERE
    dec.session_id = @@SPID;


Should 'FOR system_time ALL' include rows where the start and end times are equal?

I was not able to reproduce what you see. Probably because I have a slow computer. But on the other hand it seems that the SQL Server developers have built this with my slow computer in mind and not your fast one.

If I wrapped the insert and delete in a transaction they got the same start and end timestamp every time.

From Temporal Tables

  FOR SYSTEM_TIME filters out rows that have period of validity with
  zero duration (SysStartTime = SysEndTime). Those rows will be
  generated if you perform multiple updates on the same primary key
  within the same transaction. In that case, temporal querying surfaces
  only row versions before the transactions and ones that became actual
  after the transactions. If you need to include those rows in the
  analysis, query the history table directly.

So, if start and end timestamp are the same they assume it happened in the same transaction.


Case Insensitive ORDER BY clause using COLLATE

If you were using PostgreSQL 12 or newer you would be able to create a new collation via the CREATE COLLATION command that specifies deterministic = false:
CREATE COLLATION ci (provider = icu, locale = 'en-US-u-ks-level2', deterministic = false);

Instead of sqlfiddle, I used db<>fiddle, which offers several versions of PostgreSQL, for the example:
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=a8f4b330d04266947fb11e5c04fa4891&hide=2

This solution is similar to what worked for this related answer (also on DBA.SE):
PostgreSQL nondeterministic collations are not supported for LIKE
The first four db<>fiddle example queries with an ORDER BY clause show three different sort results:

the ORDER BY "string" COLLATE "POSIX" query returns rows in closer to a binary / ordinal ordering with all upper-case (A - Z) grouped before all lower-case (a - z).
the ORDER BY "string" and ORDER BY "string" COLLATE "en_US.utf8" queries return rows in what is effectively case-sensitive order with "A" and "a" grouped together, and lower-case of each letter coming before the upper-case of the same letter (aA bB ...).
the ORDER BY "string" COLLATE "ci" query returns rows in a truly case-insensitive order with "A" and "a" grouped together, but this time the cases of each particular letter are not in a predefined order and the two cases can even be intermixed within a letter. Since it is not guarantee to produce the same order each time, here is a sample of the result set that I'm currently seeing for this query:
-8    banana
-15   Banana
-9    bANana
1116  Banana

The following statement from the Collation Support documentation for version 12 explains that deterministic needs to be set to false:

Note that while this system allows creating collations that “ignore case” or “ignore accents” or similar (using the ks key), in order for such collations to act in a truly case- or accent-insensitive manner, they also need to be declared as not deterministic in CREATE COLLATION;... Otherwise, any strings that compare equal according to the collation but are not byte-wise equal will be sorted according to their byte values.

But the same note from the same Collation Support documentation for version 11 explains that sorting is still effectively case-sensitive:

Note that while this system allows creating collations that “ignore case” or “ignore accents” or similar (using the ks key), PostgreSQL does not at the moment allow such collations to act in a truly case- or accent-insensitive manner. Any strings that compare equal according to the collation but are not byte-wise equal will be sorted according to their byte values.

Taking a closer look at the CREATE COLLATION documentation, it seems that the deterministic property (which defaults to true but needs to be set to false to do insensitive comparisons and sorting) was introduced in Version 12, which doesn't really help you. (@LaurenzAlbe recommended an alternate syntax in a comment that might work on older versions of libicu, and adjusting the db<>fiddle test linked above for that syntax and to run on PostgreSQL 11, it does not appear to work: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=c3c48e111ed1837987524fee2c54a183&hide=2 )
That leaves you with two options:

lower() function
citext datatype (as mentioned by @a_horse_with_no_name in a comment on the question)

The citext datatype calls the lower() function internally, but has the following benefits (according to the documentation, linked directly above):

it applies to implicit indexes created by UNIQUE and PRIMARY KEY constraints
don't need to remember to add lower() to both sides of a comparison

The two main considerations are:

citext is not as efficient as text because the operator functions and the B-tree comparison functions must make copies of the data and convert it to lower case for comparisons. It is, however, slightly more efficient than using lower to get case-insensitive matching.

citext doesn't help much if you need data to compare case-sensitively in some contexts and case-insensitively in other contexts. The standard answer is to use the text type and manually use the lower function when you need to compare case-insensitively; this works all right if case-insensitive comparison is needed only infrequently. If you need case-insensitive behavior most of the time and case-sensitive infrequently, consider storing the data as citext and explicitly casting the column to text when you want case-sensitive comparison. In either situation, you will need two indexes if you want both types of searches to be fast.

It sounds like citext creates a lower-case copy of the data that takes up more space but is slightly faster due to the pre-calculation.
Also, citext needs to be installed. I modified the earlier example queries to make use of citext to show what can be done on PostgreSQL 11, but it seems that citext is not installed on db<>fiddle. I will put in a request for that. But here is are the test queries that should show how it works on your system if you have citext installed:
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=7dd95430aad8f646bfa1b7e79e21871c&hide=2
All of that being said, it is important to note that case-insensitive ordering, for the only or right-most / final column, isn't much different than case-sensitive ordering: the same letters across both cases are going to group together. It's just that with case-sensitive ordering, each case within each letter will group together (i.e. all "A"s before or after all "a"s), while case-insensitive ordering allows for "A"s and "a"s to be intermixed (since neither has priority). Allowing different cases of the same letter to intermix becomes important when there is at least one additional sort criteria in the ORDER BY list, specified to the right of (i.e. after) the string column in question. The final two example queries in the db<>fiddle linked at the top of this answer shows the difference.
With all of this in mind, the main question is: what precisely are you trying to accomplish? The request, as it is currently stated in the question, is for a case-insensitive sort. Does this mean that the comparisons should still be case-sensitive? That would seem like an odd combination given that it is fairly common to have/want a case-insensitive comparison, but the sort is less important as there is often little difference between case-sensitive and insensitive sorting (case-sensitive is consistent, but case-insensitive will have random ordering of strings that differ only in casing, and that randomness sometimes results in the same ordering as what case-sensitive would return), unless there are additional fields being sorted on.
Comment from the O.P.:

MySQL/MariaDB and SQL Server default to case insensitive, and that would certainly make sense when sorting most text. Oracle and SQLite default to case sensitive, but have a relatively simple solution for a case insensitive sort. The default collation for my database is en_US.UTF-8. I’m trying to fill in a few gaps here.

It's still not clear why any "gaps" need to be filled, but it sounds like there is a slight issue with terminology: the term "sort" / "sorting" is being used, but I suspect that the main issue is with comparisons, given that there's often no visible difference between case -sensitive and -insensitive when it comes to sorting. And, without overriding a predicate or ORDER BY field/expression with a COLLATE clause, the same sensitivity (or lack thereof) would be applied to both. This is important because, if I'm correct that O.P. truly means both, then:

Performance-wise, I would guess that lower might actually be slightly better than casting to citext in this specific context because citext is going to call lower anyway but is also doing a datatype conversion. To be fair, I haven't done any testing around this so it's merely an edumacated guess, assuming that when used as a datatype for a column, citext is slightly better due to the values being pre-calculated, but in this case there's no opportunity to precalculate.
Using either citext or lower in the ORDER BY will only solve half the issue (and the less important half given, again, that the distinction is barely noticable)
Most likely you need to stop focusing on the ORDER BY clause and use citext as the datatype for the columns, which will take care of both comparisons and sorting.


I am not 100% sure of the technology they use but their documentation speaks of virtual disk files. I have recently implemented a similar home grown solution using virtual disk files to store restores of databases as images with differencing disks hosting the clone databases. Any write action will go to the differencing disk but reads, unless to an updated page, will go to the base image virtual disk.

Any DBCC commands run against a clone will read from the base image virtual disk file. So, in theory DBCC checks against a clone should be as valid as restoring to a non production environment.

Assuming that data corruption will be backed up and restored, it should be a valid check of production.

Running DBCC checks used to be important when storage hardware was less reliable. The need for them is reduced with more modern storage technology. That is not to say that you should not run them, you really should! Just less often. In previous employment we reduced our DBCC checks to monthly or less. Although, we did have a considerable amount of databases and the IO requirement to peform the checks meant we our hands were forced to reduce the frequency.


MySQL returning only 1 row, multiple entries in the database

Your select statement includes group_concat().  This is an aggregation function.  Because there is no group by, this means that all the rows are treated as one group, and hence one row is returned.

Perhaps you mean:

SELECT publicationsId, title, summary, image, publicationDate, citation, dateAdded,
       GROUP_CONCAT(DISTINCT tagName) AS tagName,
       GROUP_CONCAT(DISTINCT publicationType) AS publicationType,
       employee_employeeId
FROM publications
LEFT JOIN publications_have_publicationTags ON publications_have_publicationTags.publications_publicationsId = publications.publicationsId
LEFT JOIN publicationTags ON publications_have_publicationTags.publicationTags_publicationTagId = publicationTags.publicationTagId
LEFT JOIN publications_have_publicationTypes ON publications.publicationsId = publications_have_publicationTypes.publications_publicationsId
LEFT JOIN publicationTypes ON publications_have_publicationTypes.publicationTypes_publicationTypeId = publicationTypes.publicationTypeId
LEFT JOIN employee_has_publications ON publications.publicationsId = employee_has_publications.publications_publicationsId
GROUP BY publicationsId, title, summary, image, publicationDate, citation, dateAdded, emplyee_employeeid
ORDER BY dateAdded DESC;


mysql says "too many open files" when importing large *.sql file

You will need to increase the openfiles in ulimit.

More detailed info here: https://duntuk.com/how-raise-ulimit-open-files-and-mysql-openfileslimit


Best way to store images in SQL Server 2014

I've dealt with this very issue for a number of years at the hospital where I work. For us, it really doesn't come down to size of the individual file so much, but overall size of ALL the files stored. What's disappointed me most about storing images in a database is that customer's poor forecasting for future growth. We have systems where SQL Server image databases are small but have terabytes of image data on a file share. And we have images stored in SQL where the overall size of the database is contained--like thumbnail images for a finite set of employees.

What you've mentioned with 256K versus1 MB has merit in a pure effectiveness and efficiency (in a technical vacuum apart from reality) but I've found that SQL works well enough even if you depart from this model. Even if the customer would tell you that they'd spare no expense for disk storage for SQL Server, it will cost you greatly if you're tasked with backing up databases that are terabytes in size.

For me it comes down to how much do you trust your customer or vendor of the software. Regardless of what you choose--only store the images within SQL if you:

A. Really trust your customer/software vendor

B. Can ensure a limited/finite scope of both images and size of the image data

C. If the trust level is low yet the customer/management insists on storing images in SQL, ensure they have their own SQL Server and pay continually for disk increases for databases and backups-- to isolate the explosion as much as possible

That said, as  DBA I really like the idea of being able to store images if I should need to. Technically, there is not really an overreaching "best" answer. And quite often the "trust" just isn't there for me to believe that the customer/vendor knows what they're talking about enough to store images on SQL--but occasionally they do.


Try:

INSERT INTO T2
SELECT NULL,T1.KEY,NULL
FROM T1
LEFT JOIN T2 USING (key,id)
WHERE T2.KEY IS NULL;

This should filter your join to select only those rows where there is currently no data value in T2.

(This is your original attempt, plus a new WHERE clause).


How to migrate subset of data from production to develop by scrambling sensitive data?

Since your development environment evidently enforces foreign key relationships, changing the value of a key is a problem.

Options include:

Designing the database so that an important fact like SSN isn't an internal key in the first place (too late to do that).

(If SSN in your database doesn't represent an important fact, then it may not be necessary to change it in the development copy, since it only exposes meaningful data when connected to the real personal data in production, which you are removing.  But you and your colleagues may disagree with me.)

Changing data for one SSN at a time - for instance input a row of 000, Proxy, Proxy in TableA; change all SSN of 111 in other tables to 000; change SSN 111 in TableA to 999; change all SSN 000 in other tables to 999.  I have done this in production data, when someone was recorded with two different service IDs in different databases and we needed to merge together the data from two IDs.

Copying data to a database that does not have referential constraints on the tables; change the data however you like, but finishing with correct relationships; copy the data again into another database where there are  constraints.

If the language and design allows, delete or turn off the foreign key enforcement, change your data, then create or turn on the foreign keys.  But beware of constraints in the database that were already disabled.  If at the end you turn on all constraints that were disabled, you may also turn on the constraints that were already disabled before you started.

Standard tools to produce anonymous identity data may be useful to you.  For names, I've used a method of taking a list of reasonable and distinct forenames, and a list of surnames, two different prime numbers in length (or "co-prime").  Take names in (semi random) order from one list, and from the other, in turn, returning from end of list to the start.  Combinations of names shouldn't be repeated until all pairs have been used once.  My list included one spelling of "Mohamed", and as a surname, because I thought it would be provocative to produce a "Mohamed Singh".  It also didn't occur to me to use "hsggghhgh", but you could use "User101first" and "User101last", etc.


This is how I fixed my issue:

`dpkg -l | grep postgres

will show you the postgresql packages that have been installed.  Run:

 sudo apt-get --purge remove

with each package name, separated by a space, following remove.

root@vps1484:/home# dpkg -l | grep postgres
ii  postgresql                                                  9.3+146really9.1+148             all          object-relational SQL database (supported version)
ii  postgresql-9.1                                              9.1.13-0ubuntu0.13.10            amd64        object-relational SQL database, version 9.1 server
ii  postgresql-client                                           9.3+146really9.1+148             all          front-end programs for PostgreSQL (supported version)
ii  postgresql-client-9.1                                       9.1.13-0ubuntu0.13.10            amd64        front-end programs for PostgreSQL 9.1
ii  postgresql-client-common                                    148                              all          manager for multiple PostgreSQL client versions
ii  postgresql-common                                           148                              all          PostgreSQL database-cluster manager
ii  postgresql-contrib                                          9.3+146really9.1+148             all          additional facilities for PostgreSQL (supported version)
iU  postgresql-contrib-9.1                                      9.1.15-0ubuntu0.12.04            amd64        additional facilities for PostgreSQL
root@vps1484:/home# sudo apt-get --purge remove postgresql postgresql-9.1 postgresql-client postgresql-client-9.1 postgresql-client-comment postgresql-common postgresql-contrib postgresql-contrib-9.1
Reading package lists... Done
Building dependency tree
Reading state information... Done
E: Unable to locate package postgresql-client-comment
root@vps1484:/home# sudo apt-get --purge remove postgresql postgresql-9.1 postgresql-client postgresql-client-9.1 postgresql-client-common postgresql
-common postgresql-contrib postgresql-contrib-9.1
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following package was automatically installed and is no longer required:
  libaio1
Use 'apt-get autoremove' to remove it.
The following packages will be REMOVED:
  postgresql* postgresql-9.1* postgresql-client* postgresql-client-9.1* postgresql-client-common* postgresql-common* postgresql-contrib*
  postgresql-contrib-9.1*
0 upgraded, 0 newly installed, 8 to remove and 8 not upgraded.
1 not fully installed or removed.
After this operation, 16.3 MB disk space will be freed.
Do you want to continue [Y/n]? y
(Reading database ... 98720 files and directories currently installed.)
Removing postgresql-contrib ...
Removing postgresql-contrib-9.1 ...
Removing postgresql ...
Removing postgresql-9.1 ...
 * Stopping PostgreSQL 9.1 database server                                                                                [ OK ]
Purging configuration files for postgresql-9.1 ...
Dropping cluster main...
Removing postgresql-client ...
Removing postgresql-client-9.1 ...
update-alternatives: warning: alternative /usr/share/postgresql/9.1/man/man1/psql.1.gz (part of link group psql.1.gz) doesn't exist; removing from list of alternatives
update-alternatives: warning: /etc/alternatives/psql.1.gz is dangling; it will be updated with best choice
Removing postgresql-common ...
 * No PostgreSQL clusters exist; see "man pg_createcluster"
Removing 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common'
Purging configuration files for postgresql-common ...
dpkg: warning: while removing postgresql-common, directory '/var/lib/postgresql' not empty so not removed
Removing postgresql-client-common ...
Purging configuration files for postgresql-client-common ...
Processing triggers for man-db ...


meaning code at comment part from mysqldump result

It is a executable comment syntax that allows the same sql to be imported into a mysql-5.0.2 or earlier server that doesn't understand the SET modes.


DENY insert,update,delete to user (PostgreSQL)

PostgreSQL does not have DENY ACLs.

Instead you must REVOKE any rights you want them to be missing, leaving only the rights you want.

e.g.

REVOKE ALL ON TABLE blah FROM USER fred;
GRANT SELECT ON TABLE blah TO USER Fred;


How to resolve Error 600122, severity 11, state 1 was raised

They are generated in your code, try this to locate them

SELECT  *
FROM    sys.sql_modules SM
WHERE   [definition] LIKE '%600122%' or [definition] LIKE '%400006%'

Or this will also bring all custom error

SELECT  *
FROM    sys.sql_modules SM
WHERE   UPPER([definition]) LIKE '%RAISERROR%' or UPPER([definition]) LIKE '%THROW%'

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql

Error numbers for user-defined error messages should be greater than 50000. When msg_id is not specified, RAISERROR raises an error message with an error number of 50000.


Why is log reserve negative in the SQL Server compensation log when using fn_dblog command

The function is technically undocumented, so I can't find any official word on this.  However, my understanding is that those "compensation" rows are related to a rollback.  A rollback is essentially erasing some previous log records, so it frees up some space in the transaction log.  The negative values are saying "give this much free space back to the log file."
You can observe this by performing a demo like this:
USE [master]
GO
DROP DATABASE IF EXISTS LogUsageTest;
GO
CREATE DATABASE LogUsageTest;
GO
ALTER DATABASE [LogUsageTest]
SET RECOVERY SIMPLE
WITH NO_WAIT;
GO
USE LogUsageTest;

CREATE TABLE dbo.A
(
    Id int IDENTITY(1,1) NOT NULL,
    Something int NOT NULL

    CONSTRAINT PK_A PRIMARY KEY (Id)
);
GO
CHECKPOINT;

That sets up a database in the SIMPLE recovery model with one table in it, then clears the log (with a checkpoint).  Here's the baseline transaction log usage:

Now I'll run an insert inside a transaction:
BEGIN TRANSACTION;
INSERT INTO dbo.A
    (Something)
SELECT
    v1.number
FROM master.dbo.spt_values v1;

We've gone from 5.5% usage to 13%:

And now we'll roll that insert back:
ROLLBACK;

And we've gone from 13% down to 8.9%.
You might notice that the compensations don't free up all the log space used by the insert.  In my test, the inserts reserve 178 bytes, while the rollbacks free up -74 bytes.
I think this is because the log reservations are extra big in case of page splits, which don't get rolled back.  But that's just an educated guess.


ASMCA(ASM Configuration Assistant) GUI is not rendering properly

This happens because of color display setting of Linux GNOME desktop. I used Xming Software on windows for remote installation.It worked.


How to check if a postgresql backup is finished or not?

You can use the function pg_is_in_backup() in PostgreSQL >= 9.3, see Backup Control Functions

For example:

SELECT pg_is_in_backup();

If the result is false, then the computer is not currently backing up. But this depends on the kind of backup you're doing (i.e. via PgAdminIII, SQL command, or pgdump).


Is there a valid reason to alias a column to the exact same name/case?

In the code as presented in your question, no there is no need but it will not cause any problems. The alias could be there because the column name used to be different and has been renamed, with the references to it being updated by some automated method.

I have sometimes deliberately aliased columns to the same name just to keep line syntax alignment when working with many columns some of which are aliased and some of which are not like so:

SELECT tbl1.col1          AS column1
     , tbl1.anothercolumn AS column2
     , tbl1.column3       AS column3
     , tbl2.somevalue     AS column4
FROM   ...

Here column 3 has an alias specified that is does not need as the output would have the desired name anyway.


Simple. Change the parameter ssl_cert_file to point to your server certificate and ssl_key_file to the private key that belongs to it.

If the private key requires a pass-phrase, you also have to set ssl_passphrase_command appropriately.

To validate client certificates, put the certification authority certificate in ssl_ca_file.


You need to create the root user with a password
SET sql_log_bin = 0;
GRANT ALL ON *.* to root@'%' IDENTIFIED WITH 'rootpass' WITH GRANT OPTION;

I would make it a little more secure. Perhaps by doing the following:

use a username other than root (like remote_user)
instead of %, user the netblock of the IP of PC2 (such as 10.20.30.%)

Therefore, you run this grant command  on PC1 instead
SET sql_log_bin = 0;
GRANT ALL ON *.* to remote_user@'10.20.30.%' IDENTIFIED WITH 'rootpass' WITH GRANT OPTION;

Note : SET sql_log_bin = 0; prevents recording the GRANT into the binary logs.
Give it a Try !!!
In your question, you said you ran this
GRANT ALL ON *.* to '%'@'%' WITH GRANT OPTION;

Wildcard characters are not allowed for the user. In fact, the MySQL Documentation on GRANT says

MySQL does not support wildcards in user names. To refer to an anonymous user, specify an account with an empty user name with the GRANT statement:

Your grant should say
GRANT ALL ON *.* to root@'%' WITH GRANT OPTION;

or
GRANT ALL ON *.* to remote_user@'10.20.30.%' IDENTIFIED WITH 'rootpass' WITH GRANT OPTION;


The Order by clause is the only one that guarantee the order.
But if you really want in back end , then you can add some additional columns/attributes to make it easier.
I added a  grp = group level and a subgroup grp_2 =group level 2.
For 0 ( 01-06 M ),0 ( 07-12 M ),0 ( 12 < M ), the grp = 0
For monthly records, grp = 1
For TOTAL, grp = 2
And for grp_2, we have: 0 ( 01-06 M )grp_2 = 1,0 ( 07-12 M ) grp_2 = 2,0 ( 12 < M ) grp_2 = 3
For monthly records, grp_2 = LAC
For TOTAL, grp_2 = 0
And so, we are ordering by ORDER BY a.grp,a.grp_2
SELECT a.YDS,a.DataSet
FROM
(
    select N'0 ( 01-06 M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet ,0 as grp,1 as grp_2
    from HerdAnalytics_tbl
    where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) <= 6

    union

    select N'0 ( 07-12 M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet,0 ,2
    from HerdAnalytics_tbl
    where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) > 6 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) <= 12

    union

    select N'0 ( 12 < M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet ,0,3
    from HerdAnalytics_tbl
    where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) > 12

    union

    select CAST(LAC as varchar),CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet ,1,Lac
    from HerdAnalytics_tbl
    where GRP = 12
        and LAC in (select LAC from HerdAnalytics_tbl where FARM_FK = 10048 and LAC != 0)
    group by LAC

    union

    select 'TOTAL' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet ,2,0
    from HerdAnalytics_tbl
    where GRP = 12
)as a
ORDER BY a.grp,a.grp_2

output:
YDS             DataSet
0 ( 01-06 M )   NULL
0 ( 07-12 M )   NULL
0 ( 12 < M )    234.56
3               234.56
4               234.56
10              234.56
TOTAL           234.56

dbfiddle


This worked for me on Debian 8 (Jessie) when I installed the pre-2017 vNext for dbfiddle:

apt-get install curl apt-transport-https sudo
add deb http://ftp.debian.org/debian jessie-backports main to /etc/apt/sources.list
apt-get update && apt-get install -t jessie-backports openssl ca-certificates
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list | tee /etc/apt/sources.list.d/mssql-server.list
apt-get update && apt-get install mssql-server
sudo /opt/mssql/bin/mssql-conf setup

However I tried and failed to get the RTM Linux version of 2017 installed on Debian Stretch, ultimately using Ubuntu.


Convert Column values into row values with new row headers

Assuming then, that you are going to add (or derive) another column for DatabaseName (or source name) then this should work for you.

DECLARE @T TABLE
    (
    Col1 INT NOT NULL
    , Col2 VARCHAR(10) NOT NULL
    , Col3 VARCHAR(10) NOT NULL
    , DatabaseName VARCHAR(20) NOT NULL
    )

INSERT INTO @T
(Col1, Col2, Col3, DatabaseName)
VALUES (1, 'EDTU', 'Value1', 'FirstDB')
    , (1, 'EDTP', 'Value1', 'FirstDB')
    , (1, 'Other', 'Value1', 'FirstDB')
    , (1, 'EDTU', 'Value5', 'SecondDB')

;WITH CTE_Source AS
    (
    SELECT S.Col2
        , S.Col3
        , S.DatabaseName
    FROM @T AS S
    )
SELECT DatabaseName
    , EDTU
    , EDTP
FROM CTE_Source AS S
    PIVOT (MAX(Col3) FOR Col2 IN (EDTU, EDTP)) AS P


Is using CLR for regular expressions safer than using external scripts?

my other colleague categorically stated that using CLR here would be a huge risk to security and stability

Well, your colleague is categorically wrong (unless they can offer up actual proof of such claims).
Security
Ever since SQLCLR was introduced in SQL Server 2005 people have been saying that it is "unsafe". However, I have yet to see anyone actually prove that it is indeed unsafe. The only supposed "proof" I have seen is someone loading an assembly that writes a file to disk with the claim that since it can write to disk, it is a security vulnerability. But, that's a false claim due to:

the person already had permission to create the assembly
the assembly already had permission to access external resources via TRUSTWORTHY being enabled or a signature / certificate being used.

That's not proof of gaining elevated permissions if you already had elevated permissions in order to a) create and b) execute the code required to make it look like you were gaining elevated permissions.
SQLCLR is not inherently insecure. Sure, it can be use incorrectly to accidentally allow someone to gain elevated permissions, etc, but by itself it is safe and secure. And, it's easy enough to introduce a security vulnerability simply by using EXECUTE AS 'dbo' in a stored procedure or function, and that's pure T-SQL.
Here are several articles that I wrote on the topic of SQLCLR security:

Stairway to SQLCLR Level 3: Security (General and SAFE Assemblies)
Stairway to SQLCLR Level 4: Security (EXTERNAL and UNSAFE Assemblies)
SQLCLR vs SQL Server 2017, Part 8: Is SQLCLR Deprecated in Favor of Python or R (sp_execute_external_script)?
SQLCLR vs SQL Server 2017, Part 9: Does PERMISSION_SET Still Matter, or is Everything Now UNSAFE?

That being said, within the context of using regular expressions, both SQLCLR and External Scripts are "secure" (assuming that you are not allowing ad hoc access to users to submit whatever random Python code they desire).
Usability
Here is a major difference: External Scripts execute via a stored procedure. Meaning, you can perform your RegEx on a single value. If it's possible to integrate the RegEx function into the SELECT and/or WHERE clauses (I know you can pass in a query and return a result set), then it's at best clunky and certainly not easy to maintain. Whereas with SQLCLR, you can create scalar functions and table-valued functions that are quite easy to integrate into queries for proper set-based solutions (and remember: properly coded SQLCLR scalar functions that don't do any data access can participate in parallel plans).
If you want RegEx functions without having to compile anything, there are quite a few available in the Free version of SQL# (SQLsharp), a SQLCLR library that I wrote.

For more info on working with SQLCLR in general, please visit my site: SQLCLR Info


If the application connects to SQL Server with service account credentials instead of current user credentials, the code will need to set CONTEXT_INFO, SESSION_CONTEXT, or save the user name to a table keyed by session_id as described in this answer. The value can then be used in the trigger code.  SESSION_CONTEXT is the most elegant but is only available in SQL Server 2016 and SQL Azure Database.


How to find cause of SQL-Server Full-Text Index Auto-Population Error?

Using the Microsoft Error Code Lookup Tool shows Error 0x80070005 is "Access Denied".  This is the output from the tool:

# for hex 0x80070005 / decimal -2147024891 :
  COR_E_UNAUTHORIZEDACCESS                                      corerror.h
# MessageText:
# Access is denied.
  DIERR_OTHERAPPHASPRIO                                         dinput.h
  DIERR_READONLY                                                dinput.h
  DIERR_HANDLEEXISTS                                            dinput.h
  DSERR_ACCESSDENIED                                            dsound.h
  ecAccessDenied                                                ec.h
  ecPropSecurityViolation                                       ec.h
  MAPI_E_NO_ACCESS                                              mapicode.h
  STIERR_READONLY                                               stierr.h
  STIERR_NOTINITIALIZED                                         stierr.h
  E_ACCESSDENIED                                                winerror.h
# General access denied error
# 11 matches found for "0x80070005"

Ensure the service accounts used by SQL Server, SQL Server Agent, and SQL Full-text Filter Daemon Launcher Windows Services have the correct access.  Microsoft Docs has some great details about how these services interact.


On a dedicated database server, how much memory to reserve for the OS?

Assuming Windows and SQL Server...

There are two schools of thought.

Leave 2-4 Gigs for Windows (depending on what is installed besides SQL Server).
Leave 10% of your available memory free.  As you get over 64 Gigs this gets to be a crazy large amount of memory to leave for the OS, which it probably won't need.

Personally I'm in the first group.  Windows usually just needs 2-4 gigs, sometimes up to 6.


Table Partitioning Using Inheritance and Triggers

The trigger will never move rows that are already in the table when you create the trigger, if that is what you mean.

Apart from that, your trigger works just fine.

INSERT INTO title_basics VALUES ('dsadas', 2020, 'T', 'dsadsa', 2021, 'tit', 42, 'min', 'type', 'orig');
INSERT 0 0

No row was inserted, because the trigger function returned NULL.

SELECT * FROM title_basics;

 t_soundex | startyear | isadult | genres | endyear | primarytitle | tconst | runtimeminutes | titletype | originaltitle
-----------+-----------+---------+--------+---------+--------------+--------+----------------+-----------+---------------
 dsadas    |      2020 | T       | dsadsa |    2021 | tit          |     42 | min            | type      | orig
(1 row)

Sure, that returns a row, because of the table inheritance.

But the row is actually here:

SELECT * FROM title_basics_2000to2115 ;

 t_soundex | startyear | isadult | genres | endyear | primarytitle | tconst | runtimeminutes | titletype | originaltitle
-----------+-----------+---------+--------+---------+--------------+--------+----------------+-----------+---------------
 dsadas    |      2020 | T       | dsadsa |    2021 | tit          |     42 | min            | type      | orig
(1 row)

If you select only from the parent table itself, you get nothing:

SELECT * FROM ONLY title_basics;

 t_soundex | startyear | isadult | genres | endyear | primarytitle | tconst | runtimeminutes | titletype | originaltitle
-----------+-----------+---------+--------+---------+--------------+--------+----------------+-----------+---------------
(0 rows)

Note: You should really use declarative partitioning instead on v11. It is so much simpler and better.


You could try Windows Synchronization Manager

https://technet.microsoft.com/en-us/library/ms151193(v=sql.105).aspx

Windows Synchronization Manager is a utility available with Microsoft Windows 2000 and later versions. If Microsoft SQL Server is running on the same computer as Synchronization Manager, you can do the following:

Synchronize a subscription.

Reinitialize a subscription.

Remove a subscription.

It is particularly useful for synchronizing subscriptions on SQL Server Express, because this version of SQL Server does not include SQL Server Agent, which, by default, runs the replication agents on other versions of SQL Server.


Mysql Trigger Insert row Another Table using by Barcode

create trigger tr_au_CkisEks
after update on CkisEks
for each row
update UrunPaketDetay t1, Ckis_Detay t2
set t2.Uretici = t1.Uretici,
    t2.Urun = t1.Urun,
    t2.Kilo = t1.Kilo,
    t2.Kalibre = t1.Kalibre
where t1.PaletKod = NEW.PaletKod
  and t2.PaletKod = NEW.PaletKod;


How is Activity Monitor Pulling processes specific memory information?

The Memory Use column - where is it coming?

It corresponds to memory_usage column in sys.dm_exec_sessions.

As a friendly note, I would suggest not to use Activity Monitor.

Use  Adam Machanic‘s free procedure, sp_WhoIsActive (uses DMVs). You can even log into a table for post analysis.

Kendra Little talks about Activity monitor limitations :

It groups wait types
Leaves out wait types
Dont leave it open ... else it will boil down your server :-)


ib_logfile opened with O_SYNC when innodb_flush_method=O_DSYNC

Yes, this O_DSYNC (the option) is fundamentally useless because MySQL/InnoDB engineers saw that it was dangerous in certain cases (corruption), and disabled it in many cases, making it a synonym to O_SYNC. it also does an fsync() for the same reason.

The main thing you want to tune is O_DIRECT vs. default (flushed), and maybe ALL_O_DIRECT on Percona.

In your case, I suppose you have good latency to the filesystem (where O_DIRECT shines). Sometimes, having the transaction logs on the filesystem cache can be hurtful (although such a difference seem a bit too much for me - did you compare it with O_DIRECT, available on all versions of MySQL, which only avoids data file caching?).

Also be careful, because sysbench may not be a good representative of your real-world load. Response times can vary a lot depending on flush_log_at_trx_commit, I assume you have that as 1.

According to Dimitri, from Oracle-MySQL, the solution upstream comes in 5.7. I like it for now in Percona, as you can choose.


Can you implement an INSTEAD OF UPDATE trigger? - a quick test on my local instances indicates you can reference the inserted and deleted tables.  Naturally, you still have to apply the real update.

here was my quick test

create table TextDataTypeTest (Col1 text)
insert into dbo.textdatatypetest (col1) values('test')
----------------
create TRIGGER dbo.TextDataTypeTest_Trigger
   ON  dbo.TextDataTypeTest
   instead of UPDATE
AS
BEGIN

select col1 into #Before from deleted
select col1 into #After from inserted

select * from #Before

select * from #After
update a
set a.col1=b.col1
from dbo.textdatatypetest a join
inserted b on 1=1

END
GO

update dbo.textdatatypetest set col1='oneforalx'


How to drop SQL Server database currently in use and in Single user mode

To identify the session currently using the database you can try

SELECT request_session_id
FROM   sys.dm_tran_locks
WHERE  resource_database_id = DB_ID('G_MAIN_DE')


You can use a CONNECT BY query to generate an arbitrary sequence:

SQL> variable v_step NUMBER
SQL> variable v_maxnumber NUMBER
SQL> exec :v_step := 30; :v_maxnumber := 211;

PL/SQL procedure successfully completed

SQL> WITH sequenceTable AS (
  2     SELECT (LEVEL - 1) * :v_step myNumber
  3       FROM DUAL
  4     CONNECT BY (LEVEL - 1) * :v_step <= :v_maxnumber)
  5  SELECT * FROM sequenceTable;

  MYNUMBER
----------
         0
        30
        60
        90
       120
       150
       180
       210

8 rows selected

You can also use a pipelined function:

SQL> CREATE OR REPLACE TYPE tab_number IS TABLE OF NUMBER;
  2  /

Type created

SQL> CREATE OR REPLACE FUNCTION arithmetic_sequence (p_step NUMBER, p_maxvalue NUMBER)
  2     RETURN tab_number
  3     PIPELINED
  4  IS
  5  BEGIN
  6     FOR i IN 0..p_maxvalue/p_step LOOP
  7        PIPE ROW (i*p_step);
  8     END LOOP;
  9     RETURN;
 10  END;
 11  /

Function created

SQL> SELECT COLUMN_VALUE myNumber FROM TABLE(arithmetic_sequence(30, 211));

  MYNUMBER
----------
         0
        30
        60
        90
       120
       150
       180
       210

8 rows selected


Linked Server Setup Error - SSL Provider: The client and server cannot communicate, because

Ensure both servers have support for TLS 1.2.

The following table, taken from the Microsoft Support site, shows when TLS 1.2 support was added to each particular SQL Server version:

╔═════════════════════════════════════════╦══════════════════════════════════════════╗
║           SQL Server release            ║    First build that supports TLS 1.2     ║
╠═════════════════════════════════════════╬══════════════════════════════════════════╣
║ SQL Server 2014 SP1                     ║ 12.0.4439.1                              ║
║ SQL Server 2014 SP1 GDR                 ║ 12.0.4219.0                              ║
║ SQL Server 2014 RTM                     ║ 12.0.2564.0                              ║
║ SQL Server 2014 RTM GDR                 ║ 12.0.2271.0                              ║
║ SQL Server 2012 SP3 GDR                 ║ 11.0.6216.27                             ║
║ SQL Server 2012 SP3                     ║ 11.0.6518.0                              ║
║ SQL Server 2012 SP2 GDR                 ║ 11.0.5352.0                              ║
║ SQL Server 2012 SP2                     ║ 11.0.5644.2                              ║
║ SQL Server 2008 R2 SP3                  ║ 10.50.6542.0                             ║
║ SQL Server 2008 R2 SP2 GDR (IA-64 only) ║ 10.50.4047.0                             ║
║ SQL Server 2008 R2 SP2 (IA-64 only)     ║ 10.50.4344.0                             ║
║ SQL Server 2008 SP4                     ║ 10.0.6547.0                              ║
║ SQL Server 2008 SP3 GDR (IA-64 only)    ║ 10.0.5545.0                              ║
║ SQL Server 2008 SP3 (IA-64 only)        ║ 10.0.5896.0                              ║
╚═════════════════════════════════════════╩══════════════════════════════════════════╝

If you have a version of SQL Server prior to those listed in the above, you should apply the latest service pack and cumulative update to enable TLS 1.2 support.

https://sqlserverbuilds.blogspot.com and https://sqlserverupdates.com/ contain excellent resources detailing where to get the latest updates for SQL Server.


How can I implement a quick backup and restore strategy for database script testing?

Since you are limited by Edition, I agree with @MartinSmith that you can use Dev edition (to create snapshot and revert when you have to rollback) since you are only testing and no one is connecting or using the data except you - provided you have enough space and memory. Also, I assume that 130GB is compressed backup !

Also, restoring a 130 GB database takes 45 mins .. you need to check if Instant file initialization is enabled or not (should be enabled). Also, you can play with BUFFERCOUNT and MAXTRANSFERSIZE parameters.


MySQL - SELECT on MyISAM Table on index - Does table size (rowcount) matter?

Broadly speaking, on a MyISAM Table with a range scan, the process is:

Find the first Index result using the BTREE (inside the .MYI file) and access the row result (on the .MYD file) - Handler_read_key
Get the next result, using the index (and in the same order), until the value retrieved is larger than the one defined (multiple instances of Handler_read_next)

You can actually get this plan by observing that you get a range join type on EXPLAIN and on the Handler_* counters on SHOW SESSION STATUS.

Theoretically, the first step is O(log n) -where n is the number of records indexed (the table size)- while the second is O(m)- where m is the number of records returned. So, theoretically, a larger table will take more to return the records. Why so I say theorically? Because the O() notation can be deceitful if you do not have into account the constants. Indexes usually end up in memory, while rows (specially on MyISAM, which has not a dedicated buffer for data) can be on disk, so the difference in performance of both operations is large. Also, MyISAM has problems with large tables, so then number of levels tend not to be too large.

Let me show you an unrelated graph:

In the above graph, the full table scan (blue line) should be flat, because all rows are examined, but it is not mainly because at that point, reading and returning 16M rows is more costly than returning only 1.

So the answer is- both operations take time, which one dominates depend on the actual value of m and n, plus the state of the database in terms of speed of hardware (memory, disk) and the state of the buffers (filesystem, key buffer). In conventional usage, an index scan of a single row is a very fast operation, but it depends comparing to what, and if you have into account extreme cases, like large tables where the BTREE index doesn't fit into memory.


SQL Server 2016 Temporal tables metadata

Is DATA_CONSISTENCY_CHECK TURNED ON OR OFF?

This is a one time operation when the table is created. This will not appear anywhere in the database because it is a one time operation.

  When creating a link to an existing history table, you can choose to perform a data consistency check. This data consistency check ensures that existing records do not overlap. Performing the data consistency check is the default. Use this argument in conjunction with the PERIOD FOR SYSTEM_TIME and GENERATED ALWAYS AS ROW { START | END } arguments to enable system versioning on a table.

You can see this yourself:

select * from sys.tables
order by name

select * from sys.key_constraints

select * from sys.check_constraints

select * from sys.sysconstraints

select definition,name
from sys.check_constraints

select definition,name
from sys.default_constraints

Likewise, you can perform this check after the creation of the table too:

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS

MSDN on DBCC CHECKCONSTRAINTS

MSDN Source

  What is the name of history table?

You can use this query to find the history tables:

SELECT NAME
    ,object_id
    ,temporal_type
    ,temporal_type_desc
FROM sys.tables
WHERE temporal_type_desc = 'History_Table'
ORDER BY NAME

If you query around sys.tables, you will find a lot of handy information.

Source: MS SQL Tips by Ameena Lalani

  Is SYSTEM_VERSIONING turned on for the table?

SELECT name, object_id,temporal_type,temporal_type_desc FROM sys.tables
where temporal_type in (1,2)
order by name


Can we have dynamic SQL jobs in SQL Server Availability groups

SQL Agent jobs are not contained within the Availability Group. When using SQL Agent jobs to manipulate a database within an Availability Group, there are all sorts of special considerations that need to be taken, particularly related to failover.

What happens if the job is running during failover? The job will likely fail, as the replica it is running on is now a non-writable secondary, and probably needs to be re-run on the now-primary replica. What if a job has been created, but not started when there is a failover?

What happens if a row is inserted, then immediately updated? The two SQL Agent jobs would have a dependency in the order in which they run. This is an issue with using SQL Agent jobs in general, regardless of whether an AG is part of the equation, but would be exacerbated if the initial INSERT were "lost" in a failover.

Additionally in your specific scenario, you will be generating one SQL Agent Job per insert/update/delete. SQL Agent will only start one job per second. This means you will have a self-imposed limitation of one insert/update/delete operation per second. This will be a serious scalability issue, in addition to the headaches of job management.

Instead, have your CRUD procedures insert directly into the Service Broker Queue. The process to enqueue a message for Service Broker will likely perform much better than creating a SQL Agent job (but I've not tested it to give specific numbers). The Service Broker queue is contained within the database, so you do not have to worry about messages being "lost" in a failover.

If you need to do data manipulation, that can be done from within your Service Broker queue before additional processing/sending is done. Because that manipulation is being done within the Service Broker, it would all be asynchronous to your CRUD operations.

Using SQL Agent jobs for your scenario should not be necessary at all.


SELECT TOP 1 from a very large table on an index column is very slow, but not with reverse order ("desc")

The Clustered Index Scan shows 423,723 logical reads to return the first row, taking 1926 ms:

This seems rather a lot to locate the first row in index order.
Most likely your ghost cleanup task is running a long way behind, or has stopped. You should check the ghost_record_count for the clustered index in sys.dm_db_index_physical_stats and monitor changes over time.
The ordered scan from the end of the index that is seeing constant delete activity has to scan over an awful lot of ghosted records before it finds the first 'alive' row to return. This explains the extra logical reads. A seek down the b-tree to the lowest value of the index will encounter much fewer ghosted records.
Another performance-affecting factor is that the scan itself becomes responsible for removing the ghost records as mentioned in Inside the Storage Engine: Ghost cleanup in depth by Paul Randal.
You should check that trace flag 661 (disable ghost cleanup) is not active.
Solutions

You may find that running sp_clean_db_free_space provides relief.
Changing the process that deletes rows from that end of the index to use a PAGLOCK hint would enable ghost cleanup on the spot, which could well solve the problem as well.

If the ghost cleanup process has stopped completely, the most effective solution is normally to restart the SQL Server instance. You should also ensure that the SQL Server is running one of the latest Cumulative Updates. There have been many ghost cleanup bugs over the years.
In your specific case:

It turned out the problem was caused by another test database on the same server. That test database was restored with "data loss", and is corrupt. Surprisingly, the ghost cleanup process apparently was stuck in that database. Once we deleted that corrupted database from SMSS, the problem resolved by itself (took a long time and might have caused DB to lockup for a short while).


Toad cannot directly read Oracle data files, no.  You would need to recover the database on a local server.  Once you do that, you could connect to Oracle database on the local server from Toad and run whatever queries you'd like.

Assuming that what you've been given is a consistent cold backup of your database, you'd need, at a minimum, exactly the same version of Oracle (patchsets included) on exactly the same operating system that the third party vendor uses.  Assuming that the vendor is vaguely competent about applying patches, that will likely require that you have a My Oracle Support account which would require that your organization has appropriate Oracle licenses.  How you do that will depend on the operating system your Oracle server will be using.

Since it sounds like you are not a DBA and you're not particularly familiar with Oracle, assuming there is a reason that you can't simply access the existing database to run whatever queries you need to run, I would tend to suspect that you would be much better off requesting an export of the database (or potentially the tables you're interested in if you really care about a subset of the data) rather than a backup.  Toad can't read an export directly either but it is a lot easier to import and export file into a database you create on your local server without worrying about matching the version and operating system that the vendor uses or going through the effort of doing a proper database restore.


SQL Server replicating data to an archive

if i understand correctly log shipping and transactional replication is not an option because they basically update on the other side whatever you do on the primary server.
you will get an updated database and if the info is overridden so is the secondary database.
i would maybe try create a planned DWH database that i would copy the fresh data every period of time in some way.. maybe SSIS or bulk insert.

looks like it'll take some work like understanding when is data being overridden: every 5 minutes? at random times when they update it in the app? and then decide accordingly on what to do(maybe even use a trigger on insert or update to bulk insert it to a DWH table in s different server).
I hope this helps.
keep in mind if the updates and insert happen often this triggers may cause performance issues in some cases if done constantly.


Using the accepted answer from List schema permissions, this would probably give you what you want.

--demo setup
CREATE DATABASE listschema
GO

USE listschema
GO

CREATE SCHEMA TestSchema
GO

CREATE USER TestUser WITHOUT LOGIN
GO

GRANT SELECT
    ON SCHEMA::TestSchema
    TO TestUser

DENY INSERT
    ON SCHEMA::TestSchema
    TO TestUser
GO

--The actual query
SELECT state_desc
    ,permission_name
    ,'ON'
    ,class_desc
    ,SCHEMA_NAME(major_id)
    ,'TO'
    ,USER_NAME(grantee_principal_id)
FROM sys.database_permissions AS PERM
JOIN sys.database_principals AS Prin
    ON PERM.major_ID = Prin.principal_id
        AND class_desc = 'SCHEMA'
WHERE major_id = SCHEMA_ID('TestSchema')
    AND grantee_principal_id = user_id('TestUser')
    --AND    permission_name = 'SELECT'
GO

--cleanup
USE tempdb
GO

DROP DATABASE listschema

| state_desc | permission_name | (No column name) | class_desc | (No column name) | (No column name) | (No column name) |
|------------|-----------------|------------------|------------|------------------|------------------|------------------|
| DENY       | INSERT          | ON               | SCHEMA     | TestSchema       | TO               | TestUser         |
| GRANT      | SELECT          | ON               | SCHEMA     | TestSchema       | TO               | TestUser         |


Why does DBCC CHECKTABLE WITH TABLERESULTS only report Error 8990 on corrupt tables?

I'm curious as to why Error 8990 ins't returned in with the non-corrupt table?

I wasn't in the developers mind when it was being thought of, but my guess is to reduce some noise. It'd be redundant to give a summary of the table that had no errors. Since the Check* output is already pretty verbose, my guess is to cut down on some of that since there are no errors.

In fact, looking at the messages they are even labeled in a way that makes me believe they only wanted to give the output when an error occurred.

There are 57 rows in 1 pages for object "SmallTable".
CHECKTABLE found 0 allocation errors and 0 consistency errors in table 'SmallTable' (object ID 645293745).

Since the Check* commands are specifically to investigate and report back their findings (in addition to potentially fixing) you could argue for this either way. To really know, we'd have to ask the original developers if they even remember or had a thought about it.

  Bonus question, why doesn't DBCC CHECKCATALOG have a WITH TABLERESULT arguement?

Again, no clue. Best guess would be no one specifically asked for the output in that format or after the initial creation, no time was allotted to go back and add it.


Should Database Servers Be Defragmented?

Provided you recon 100% that physical disk fragmentation is significantly affecting sql server performance and you can afford downtime, go for disk defrag.

I have never had to defrag a windows disk hosting sql server databases. You should choose a sensible autogrowth value for your databases and have Instant file initialization enabled.


SQL Server 2012 introduced database-level logon/logoff audit tracking as part of the "contained databases" feature, which allows SQL Server logins to be defined at the database level.  In prior versions of SQL Server there is no such concept of login or logout at the database level.
From the Microsoft Docs:

SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
Indicates that a principal successfully logged in to a contained database. Equivalent to the Audit Successful Database Authentication Event Class.
DATABASE_LOGOUT_GROUP
This event is raised when a contained database user logs out of a database. Equivalent to the Audit Database Logout Event Class.

Be aware that unless you are using contained databases, and connections are being made to those databases via contained logins, those audits will not reflect login/logout events.
SQL Server Audit on 2008 R2 includes the ability to audit successful and failed logins at the server level via:
SUCCESSFUL_LOGIN_GROUP
FAILED_LOGIN_GROUP

Alternatively, you can configure "Login Auditing" on the "Security" tab of the "Server Properties" dialog.
In SSMS, right-click the server name in the "Object Explorer" panel.  Then choose "Properties" from the list.  Select the "Security" page, then specify "Both failed and successful logins" from the "Login auditing" section.

Login audit entries are written to the SQL Server Error Log; you can view them with this command:
EXEC sys.xp_readerrorlog 0, 1, 'Login';


Advanced CSV export formatting in MS Access

If you're just trying to put a line between every line after you've exported your file then one solution would be to create macros in Excel. By tweaking your auto-generated macros code with VBA in Excel you can automate all the process and use the same solution in the future with a click of a button.

But if you're familiar with programming languages then it might be much more easier to manipulate your files in scripting languages such as Python etc..

*CSV files are seperated by commas hence "comma seperated values."


You can alter the table header using a script task:

Note: you have to add Microsoft.Office.Interop.Excel.dll file to the following directories (.Net Framework dll directory)  C:\Windows\Microsoft.NET\Framework\v2.0.50727 and (sql server data tools dll directory) C:\Program Files\Microsoft SQL Server\100\DTS\Binn (using vs 2005 and sql 2008) and then add this dll as a reference in your script task

using System;
using System.Collections.Generic;
using System.Linq;
using System.IO;
using System.Text;
using Microsoft.Office.Interop.Excel;

public void Main()
    {

        string filename = @"F:\ExcelLibrary\myexcel.xls";
        if (File.Exists(filename))
        {
            File.Delete(filename);
        }
        Microsoft.Office.Interop.Excel._Application oApp;
        Microsoft.Office.Interop.Excel._Worksheet oSheet;
        Microsoft.Office.Interop.Excel._Workbook oBook;
        oApp = new Microsoft.Office.Interop.Excel.Application();
        oBook = oApp.Workbooks.Add();
        oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oBook.Worksheets.get_Item(1);
        oSheet.Cells[1, 1] = "Date";
        oSheet.Cells[1, 2] = "NMMO";
        oSheet.Cells[1, 3] = "P1 #";
        oSheet.Cells[1, 4] = "P2 #";
        //Microsoft.Office.Interop.Excel.Range oRange = oSheet.Range["A1", "B3"];
        if (oApp.Application.Sheets.Count < 1)
        {
            oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oBook.Worksheets.Add();
        }
        else
        {
            oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oApp.Worksheets[1];
        }
        oBook.SaveAs(filename);
        oBook.Close();
        oApp.Quit();
        Dts.TaskResult = (int)ScriptResults.Success;
    }

Don't forget to set the Delay Validation property to True for DataFlow Task which contains the Excel Source.

References

Create Excel File with Header Name by using C# Coding


You do not need to drop the subscription.

You're getting primary key violations as the replication agent is trying to insert duplicates.

  Delete the records I added so that the replication can copy down the new records?

Yes, you will need to manually remove the records in order to alleviate the primary key violations.


Work out date difference when there is only one date

This should do it:

SELECT e1.ID, DATEDIFF(MINUTE,e1.EventTime,e2.EventTime) [Minutes]
FROM (SELECT * FROM event WHERE Action = 'Start') AS e1
LEFT JOIN (SELECT * FROM event WHERE Action = 'Stop') AS e2
ON e1.ID = e2.ID


Replication of database used by two programs

For the PK violations:

You need to ensure that each node in your Peer-to-Peer topology has an identity range island which will not conflict if inserts originate at that node.  To do this you'll need to run DBCC checkident(talbename, reseed, start of range), ie. DBCC checkident(tablename, reseed, 100000) for Node 1, DBCC checkident(tablename, reseed, 200000) for Node 2, and so on..

For the Update-Delete conflicts:

You need to add a location specific identifier column to your table and extend this column to your PK.  This will reduce the chances of conflicts occurring.  If this is not an option, you will lose data as the change that occurred on the node with the highest Originator ID will win the conflict.  This may or may not be okay for you.


Install Only Pgbench and Not PostgreSQL Server

You can't install the pgbench utilty without installing some required packages. These can be seen in the link you provided in your question:

Package: postgresql-contrib-9.3 (9.3.21-0ubuntu0.14.04 and others) [security]

Each package that has a depends (red dot) in front of it, is required to install the package you are installing.

In your case the postgresql-contrib-9.3 package depends on:

libc6
libossp-uuid16
libpq5
libssl1.0.0
libxml2
libxslt1.1
postgresql-9.3
zlib1g

If you follow the packages you will find addtional dependencies. The postgesql-9.3 package for example depends on:

...
postgesql-client-9.3
postgesql-common
-...

This goes on and on until all the prerequisites are met, so that your desired  package can be installed.

Your question

  Is there a way to only install pgbench without completely installing the PostgreSQL package which includes PostgreSQL server?

My answer

No it is not possible to install the pgbench utility contained in the postgresql-contrib-9.3 package without installing additonal packages, because of the dependencies.


This isn't the most elegant solution, but it'll work to return matching rows if @YEARCOMPLETED is a value, or if it is null. This works decently because it's able to make use of an index on the yearCompleted column, but it basically doubles your code with a near duplicate:

DECLARE @YEARCOMPLETED smallint;
SET @YEARCOMPLETED = NULL;

SELECT *
FROM TABLE1
WHERE yearCompleted = @YEARCOMPLETED
UNION ALL
SELECT *
FROM TABLE2
WHERE yearCompleted = @YEARCOMPLETED
UNION ALL
SELECT *
FROM TABLE1
WHERE yearCompleted IS NULL
AND @YEARCOMPLETED IS NULL
UNION ALL
SELECT *
FROM TABLE2
WHERE yearCompleted IS NULL
AND @YEARCOMPLETED IS NULL;

If you don't care about SARGability and using an index on that column, you could do this. It's less duplicated code, but probably with worse performance:

DECLARE @YEARCOMPLETED smallint;
SET @YEARCOMPLETED = NULL;

SELECT *
FROM TABLE1
WHERE COALESCE(yearCompleted,9999) = COALESCE(@YEARCOMPLETED,9999)
UNION ALL
SELECT *
FROM TABLE2
WHERE COALESCE(yearCompleted,9999) = COALESCE(@YEARCOMPLETED,9999);

The most elegant solution, to balance reducing duplicated code and good performance, is to use dynamic SQL:

DECLARE @YEARCOMPLETED smallint;
SET @YEARCOMPLETED = NULL;

DECLARE @SQL nvarchar(Max);

SET @SQL = 'SELECT *
FROM TABLE1
WHERE ';

IF (@YEARCOMPLETED IS NULL)
BEGIN
    SET @SQL = @SQL + ' yearCompleted IS NULL';
END
ELSE
BEGIN
    SET @SQL = @SQL + ' yearCompleted = @YEARCOMPLETED ';
END

SET @SQL = @SQL + ' UNION ALL
SELECT *
FROM TABLE1
WHERE ';
IF (@YEARCOMPLETED IS NULL)
BEGIN
    SET @SQL = @SQL + ' yearCompleted IS NULL';
END
ELSE
BEGIN
    SET @SQL = @SQL + ' yearCompleted = @YEARCOMPLETED';
END

EXEC sp_executesql @stmt = @SQL, @params = '@YEARCOMPLETED smallint', @YEARCOMPLETED = @YEARCOMPLETED;


If you are using sharding, then the "load balancer" is the mongos process - actually it is more like a router - it keeps an in-memory copy of the config database and can make decisions based on the shard key.

If you mean load balancing across identical replica sets or across the members of the set, then there is a feature request to have mongos handle that scenario too (https://jira.mongodb.org/browse/SERVER-1594), however given how the drivers operate it is not really necessary (it would make the drivers less complex though).

In a single replica set, you cannot distribute writes, they all must go to the primary.  You can distribute reads to the secondaries already, via Read Preferences as you deem appropriate.  The driver keeps track of what is a primary and what is a secondary and routes queries appropriately.


Getting information on users payments via subscriptions

I find that if I try to write out what my query is doing, it tends to illuminate the answer

-- for everything that is in payment
-- that doesn't have a refund date and doesn't have a deleted at
-- And the payments have a subscription and a user
-- give me their subscription id and the count of subscriptions
SELECT
    COUNT(subscription.user_id) as cnt
,   subscription.id
FROM
    payment
    INNER JOIN
        subscription
        ON payment.subscription_id = subscription.id
    INNER JOIN
        user
        ON user.id = subscription.user_id
WHERE
    (payment.refunded_date is null)
    AND (subscription.deleted_at IS NULL)
GROUP BY
    subscription.id
ORDER BY
    cnt

Assuming I read your query correctly, I think your issue is that you are counting how many subscriptions an ID has. You didn't specify how the results were wrong, but I'm guessing you get 1 back.

If you want to know the total count of payments, you would count that instead.

-- for everything that is in payment
-- that doesn't have a refund date and doesn't have a deleted at
-- And the payments have a subscription and a user
-- give me their subscription id and the count of payments
SELECT
    COUNT(payment.subscription_id) as cnt
,   subscription.id
FROM
    payment
    INNER JOIN
        subscription
        ON payment.subscription_id = subscription.id
    INNER JOIN
        user
        ON user.id = subscription.user_id
WHERE
    (payment.refunded_date is null)
    AND (subscription.deleted_at IS NULL)
GROUP BY
    subscription.id
ORDER BY
    cnt


Transaction Log maintenance on Mirror database

We are doing log backups on the principal database, and my question is what are the gotchas with doing a log backup on a mirror?

You can't do a log backup on the mirror database.

  At least one full database backup from the mirror has to be completed before doing a log backup, in that case are there special options that need to be used because it is a mirror?

You can't do a full database backup on the mirror database either.

Take this for instance:  I have Server1 which houses the principal database AdventureWorks2012, and I have Server2 which contains the mirror.  Here's what happens when I try to run backups on the mirror database (on Server2):

use master;
go

backup database AdventureWorks2012
to disk = 'c:\sqlserver\AW_mirror.bak';
go

  Msg 954, Level 14, State 1, Line 2
  The database "AdventureWorks2012" cannot be opened. It is acting as a mirror database.
  Msg 3013, Level 16, State 1, Line 2
  BACKUP DATABASE is terminating abnormally.

backup log AdventureWorks2012
to disk = 'c:\sqlserver\AW_mirror.trn';
go

  Msg 954, Level 14, State 1, Line 1
  The database "AdventureWorks2012" cannot be opened. It is acting as a mirror database.
  Msg 3013, Level 16, State 1, Line 1
  BACKUP LOG is terminating abnormally.

Take a look at this Database Mirroring FAQ by Robert Davis.  I will quote him regarding this operation and the mirror database transaction log maintenance:

  When you backup the log on the principal, the virtual log files (individual units within the log file) are marked as re-writable. The same VLF’s are marked as re-writable in the mirror log file as well. The VLF status is mirrored on the database.

So there you have it.  If you have transaction log backups on the principal then similar log reuse behavior will be mirrored to its partner database.


SQL Server Profiler Exception tracing - Error procedure and line number omitted

If you're able to use Extended Events, this is a lot easier. As you said:

  Doing a full trace of every statement being executed is very overwhelming on active systems

You can set up the XE session like this:

CREATE EVENT SESSION exceptions
    ON SERVER
    ADD EVENT sqlserver.error_reported
    ( ACTION ( sqlserver.sql_text, sqlserver.tsql_frame ))
    ADD TARGET package0.event_file
    ( SET filename = N'c:\temp\exceptions' )
    WITH ( MAX_MEMORY = 4096KB,
           EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
           MAX_DISPATCH_LATENCY = 5 SECONDS,
           MAX_EVENT_SIZE = 0KB,
           MEMORY_PARTITION_MODE = NONE,
           TRACK_CAUSALITY = OFF,
           STARTUP_STATE = OFF );
GO

A simple proc to throw an error:

CREATE PROCEDURE dbo.error_out
AS
BEGIN
    SELECT CASE WHEN 1 = 1
                THEN 1 /0
                ELSE NULL
           END
END
GO

Start the session and run the procedure a couple few times:

ALTER EVENT SESSION exceptions ON SERVER STATE = START;
GO

EXEC dbo.error_out;
GO

Then check your XE session:

SELECT CONVERT(XML, x.event_data) AS x
INTO #xml
FROM   sys.fn_xe_file_target_read_file('c:\temp\exceptions*.xel', NULL, NULL, NULL) AS x;

SELECT  x.x.value('(event/@timestamp)[1]', 'DATETIME2(7)') AS event_date,
        x.x.value('(event/data[@name="message"]/value)[1]', 'VARCHAR(2044)') AS message,
        x.x.value('(event/action[@name="sql_text"]/value)[1]', 'VARCHAR(2044)') AS sql_text,
        ca.c.value('(@line)[1]', 'INT') AS line_number,
        ca.c.value('(@offsetStart)[1]', 'INT') AS offset_start,
        ca.c.value('(@offsetEnd)[1]', 'INT') AS offset_end
FROM #xml AS x
CROSS APPLY x.nodes('//frame') AS ca (c)

You should get results something like this:


Overcoming the SQL Server Express Edition limitation

From Express Edition:

  SQL Server Express includes 10GB of storage per database

and from Features Supported by the Editions of SQL Server 2014, Cross-Box Scale Limits:

  Maximum relational database size: 10 GB

This latter reference makes it clearer that the 10GB limit per database is applied to relational data, so it excludes log files* as well as FILESTREAM and FileTable data (both supported in Express Edition).

* See Database size limitation in SQL Express by Arvind Shyamsundar


How to remove a secondary database from an availability group and rejoin it

This can be done by simply removing the secondary database from the availability group (AG), restoring backups to get it back in sync, and then adding it back to the AG.  SQL Server Management Studio has support for removing a secondary database from an AG and adding it back, but it doesn't  have a wizard (as of this writing) for doing all of the steps required to backup, restore, and rejoin.

Remove the secondary database from the AG by running the following T-SQL on the secondary (SEC-C in the OP example) where the database is not synchronizing:

ALTER DATABASE DB-C SET HADR OFF

See Remove a Secondary Database from an Availability Group.  You can also do this step in SSMS by drilling down to the database under the AG on the secondary, right-clicking on it, and selecting the option to remove it.
Drop the database from the secondary.
Take a full backup and at least one log backup on one of the database replicas that is still in the AG and synchronized, and restore them to the secondary.  If it's a big and busy database and you're restoring to a DR site, this could take more than a day.  Be sure to leave the database in recovery mode (restore with NORECOVERY)!

See Prepare a secondary database for an Always On availability group.
When you've got the secondary database caught up with the latest log backup restored to it, join it to the AG by running the following command on the secondary (SEC-C in the OP example):

ALTER DATABASE DB-C SET HADR AVAILABILITY GROUP = AG_name

See Join a secondary database to an Always On availability group for more information, including how to do this step in SSMS.

This answer is intended to be a high level, succinct guide.  This is all well-documented by Microsoft, but it's spread out over multiple pages, which of course have far more info, and the pages are in different categories.  The page to remove a secondary database is under:

Business continuity -> Always On availability groups -> How-to -> Operation

and the other pages referenced are under

Business continuity -> Always On availability groups -> How-to -> Configure availability group.


pg_repack slows down PostgreSQL replication

I was about to ask a similar question, but it is related to the replication streaming ("physical"/block related) that streams/replicates the actual data writes to the disk(s). With vacuum full (reindexes), and truncates/restores, and now pg_repack, the tables are rewritten to disk, causing a lot of data writes that need to be streamed to the other side...

Thus, no, I don't believe you'll be able to do the "prioritization" as the moment the rebuilt table are "swapped" into active table, the new updates/writes on the master, will be going to the rebuild table, not the old table, and then the replica needs that table "available"!

I've been getting into the habit of killing the replication, doing the major data changes (perhaps a good practise to have it as a "backup" available before the data changes) and then doing a new full pg_basebackup/replication restarts

Hope this helps to explain the situation you are in and how I've been solving it till now :)

That said do go read: https://www.depesz.com/2013/06/21/bloat-removal-by-tuples-moving/

Depesz explains a mechanism that helped him move data to the beginning of the table "on the fly" with data available all the time using code similar to:

with x as (
delete from test where id in (999997,999998,999999) returning *
)
insert into test
    select * from x;

this is then run in batches, with $vacuum$ statements running together to clean up the space. Doing this in a slow/managed method, you could be able to do the "repack" with not too far behind replicas.

Just be careful of triggers on update/insert/delete !


How can a datapump export have its resource usage limited on linux?

Ionice can limit disk usage

Take a look at the ionice utility for linux, as it seems to suit your needs quite well.


This is the explanation I got from IBM support, I have not tested it myself, but it seems reasonable. The workaround suggested will work for all situations that I can think of right now:
The extra spaces padded in CLP is the expected behavior. This is because there are some multiple-byte characters that take more than 1-physical space. See below example that demonstrates the same:
Say, U+FF2D FULLWIDTH LATIN CAPITAL LETTER M
$ db2 "select a, hex(a) from table(values('A' || u&'\FF2D' || 'B'),('A B')) t(a)"
A   2
----- ----------
AＭB 41EFBCAD42 —> Here wide-M is taking more space than a normal multi/single byte character.
A B  41204220202 record(s) selected.

If you are using a lot of functions like get_db_type() in your scripts, you can change the return value in these functions to modify it to OCTETS:
Something like the following:
CREATE OR REPLACE FUNCTION get_db_type()
RETURNS VARCHAR(1 OCTETS)
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
BEGIN ATOMIC
    RETURN 'N';

END @

I'll leave the current workaround as is below:
If nothing else shows up I'll probably do something along the lines of:
x=$(db2 -x "values ('')")
x="${x%"${x##*[![:space:]]}"}"
echo ${x}b
b

At first, I figured:
x=$(db2 -x "values ('')" | xargs echo)

would do, but the pipe introduces a subshell, so:
echo ${x}b
SQL1024N A database connection does not exist. SQLSTATE=08003b

it does not work.


There are a number of things you need to know when using the monitoring tables:

The monitoring tables (like MON$STATEMENTS) provides you a list of the active or prepared statements at the time a query was run.
The query result stays stable until the transaction is committed! This means that repeatedly executing the query without committing will not yield new information.
Only SYSDBA, the databaseowner or a user with role RDB$ADMIN can see all information, other users can only see information about their own connections.
Your Firebird installation contains basic documentation on the tables in  doc/README.monitoring_tables.txt

Now as to your screenshot: all statements are from the same connection (your own): MON$ATTACHMENT_ID = 1521. Only one statement is currently active (MON$STATE = 1) and participating in transaction MON$TRANSACTION_ID = 90964 and was started at 2012-12-06 10:08 (MON$TIMESTAMP), the rest is inactive (MON$STATE = 0) and therefor has no transaction-id and timestamp. The first statement is also the only one that currently has an actual query (MON$SQL_TEXT), which is the query that actual produced the result in the screenshot. In Firebird statements are allocated separately from the actual query to be executed using that statement.

That is not much, but if you had joined to MON$RECORD_STATS, MON$IO_STATS or MON$MEMORY_USAGE based on MON$STAT_ID you would have received statistics on the query (ie the number of reads, records update, number of pages read or written, memory use). The monitoring tables show information at a specific moment in time, and if a query has already finished when you execute it, you don't get any info (I am not 100% sure, but it might remain available until the next execute of a statement, or until the statement is unprepared or closed).

If instead you need a stream of information and statistics, than you might get more out of the Firebird Trace API. I haven't used it much myself yet, but the article Audit and Trace Services in Firebird 2.5 by Thomas Steinmauer provides a good introduction to it.

In your question you say you expected 'more content', but didn't actually specify what content you need or expected; I hope this provides sufficient info.


Trigger to restrict users from creating a new table in the database

If you really must use a trigger you can use a database trigger.

CREATE TRIGGER NoCreateTable ON DATABASE
    FOR CREATE_TABLE
AS
PRINT 'You may not create a table'
ROLLBACK TRANSACTION


The reason that the size_in_bytes field of the sys.dm_exec_cached_plans DMV, at least in terms of "Compiled Plans", is larger than the CachedPlanSize attribute of the QueryPlan node in the XML plan is because a Compiled Plan is not the same thing as a Query Plan. A Compiled Plan is comprised of multiple Memory Objects, the combined size of which equates to the size_in_bytes field. So, the description of "Number of bytes consumed by the cache object" that you found in the documentation is accurate; it's just that it is easy to misinterpret what is meant by "cache object" given the name of the DMV and that the term "plan" has multiple meanings.

A Compiled Plan is a container that holds various pieces of information related to the query batch (i.e. not just a single statement), one (or more) of those pieces being the query plan(s). Compiled Plans have a top-level Memory Object of MEMOBJ_COMPILE_ADHOC which is the row in sys.dm_os_memory_objects that is linked via the memory_object_address field in both DMVs. This Memory Object contains the symbol table, parameter collection, links to related objects, accessor cache, TDS metadata cache, and possibly some other items. Compiled Plans are shared amongst Sessions/Users that are executing the same batch with the same Session settings. However, some related objects are not shared between Sessions/Users.

Compiled Plans also have one or more dependent objects that can be found by passing the plan_handle (in sys.dm_exec_cached_plans) into the sys.dm_exec_cached_plan_dependent_objects DMF. There are two types of dependent objects: Executable Plan (Memory Object = MEMOBJ_EXECUTE) and Cursor (Memory Object = MEMOBJ_CURSOREXEC). There will be 0 or more Cursor objects, one per each cursor. There will also be one or more Executable Plan objects, one per each User executing that same batch, hence Executable Plans are not shared between Users. Executable Plans contain run-time parameter and local variable info, run-time state such as the currently executing statement, object ids for objects created at run-time (I assume this refers to Table Variables, Temporary Tables, Temporary Stored Procedures, etc), and possibly other items.

Each statement within a multi-statement batch is contained within a Compiled Statement (Memory Object = MEMOBJ_STATEMENT). The size of each Compiled Statement (i.e. pages_in_bytes) divided by 1024 should match the CachedPlanSize="xx" values of the <QueryPlan> nodes in the XML plan. Compiled Statements will often have one (possibly more?) associated runtime Query Plans (Memory Object = MEMOBJ_XSTMT). Finally, for each runtime Query Plan that is a query, there should be an associated Query Execution Context (Memory Object = MEMOBJ_QUERYEXECCNTXTFORSE).

With respect to Compiled Statements, single-statement batches do not have separate Compiled Statement (i.e. MEMOBJ_STATEMENT) or separate runtime Query Plan (i.e. MEMOBJ_XSTMT) objects. The value for each of those objects will be stored in the main Compiled Plan object (i.e. MEMOBJ_COMPILE_ADHOC), and in that case, the pages_in_bytes value for that main object divided by 1024 should match the CachedPlanSize size in the <QueryPlan> node of the XML plan. Those values will not equate, however, in multi-statement batches.

The size_in_bytes value can be derived by summing the entries in the sys.dm_os_memory_objects DMV (the items noted above in bold), all related by dm_os_memory_objects.page_allocator_address for that Compiled Plan. The trick to getting the correct value is to first get the memory_object_address from sys.dm_exec_cached_plans for a particular Compiled Plan, then use that to get the corresponding MEMOBJ_COMPILE_ADHOC row from sys.dm_os_memory_objects based on its memory_object_address field. Then, grab the page_allocator_address value from sys.dm_os_memory_objects for that row, and use it to grab all rows from sys.dm_os_memory_objects that have the same page_allocator_address value. (Please note that this technique does not work for the other Cached Object types: Parse Tree, Extended Proc, CLR Compiled Proc, and CLR Compiled Func.)

Using the memory_object_address value obtained from sys.dm_exec_cached_plans, you can see all of the components of the Compiled Plan via the following query:

DECLARE @CompiledPlanAddress VARBINARY(8) = 0x00000001DC4A4060;

SELECT obj.memory_object_address, obj.pages_in_bytes, obj.type
FROM   sys.dm_os_memory_objects obj
WHERE  obj.page_allocator_address = (
                               SELECT planobj.page_allocator_address
                               FROM   sys.dm_os_memory_objects planobj
                               WHERE  planobj.memory_object_address = @CompiledPlanAddress
                              )
ORDER BY obj.[type], obj.pages_in_bytes;

The query below lists all of the Compiled Plans in sys.dm_exec_cached_plans along with the Query Plan and statements for each batch. The query directly above is incorporated into the query below via XML as the MemoryObjects field:

SELECT cplan.bucketid,
       cplan.pool_id,
       cplan.refcounts,
       cplan.usecounts,
       cplan.size_in_bytes,
       cplan.memory_object_address,
       cplan.cacheobjtype,
       cplan.objtype,
       cplan.plan_handle,
       '---' AS [---],
       qrypln.[query_plan],
       sqltxt.[text],
       '---' AS [---],
       planobj.pages_in_bytes,
       planobj.pages_in_bytes / 1024 AS [BaseSingleStatementPlanKB],
       '===' AS [===],
       cplan.size_in_bytes AS [TotalPlanBytes],
       bytes.AllocatedBytes,
       (SELECT CONVERT(VARCHAR(30), obj.memory_object_address, 1)
               AS [memory_object_address], obj.pages_in_bytes, obj.[type]
               --,obj.page_size_in_bytes
        FROM   sys.dm_os_memory_objects obj
        WHERE  obj.page_allocator_address = planobj.page_allocator_address
        FOR XML RAW(N'object'), ROOT(N'memory_objects'), TYPE) AS [MemoryObjects]
FROM   sys.dm_exec_cached_plans cplan
OUTER APPLY sys.dm_exec_sql_text(cplan.[plan_handle]) sqltxt
OUTER APPLY sys.dm_exec_query_plan(cplan.[plan_handle]) qrypln
INNER JOIN sys.dm_os_memory_objects planobj
        ON planobj.memory_object_address = cplan.memory_object_address
OUTER APPLY (SELECT SUM(domo.[pages_in_bytes]) AS [AllocatedBytes]
             FROM   sys.dm_os_memory_objects domo
             WHERE  domo.page_allocator_address = planobj.page_allocator_address) bytes
WHERE  cplan.parent_plan_handle IS NULL
AND    cplan.cacheobjtype IN (N'Compiled Plan', N'Compiled Plan Stub')
--AND cplan.plan_handle = 0x06000D0031CD572910529CE001000000xxxxxxxx
ORDER BY cplan.objtype, cplan.plan_handle;

Please note that:

the TotalPlanBytes field is just a re-statement of the sys.dm_exec_cached_plans.size_in_bytes field,
the AllocatedBytes field is the SUM of the related memory objects that typically matches TotalPlanBytes (i.e. size_in_bytes)
the AllocatedBytes field will occasionally be greater than TotalPlanBytes (i.e. size_in_bytes) due to the memory consumption increasing during execution. This seems to happen mostly due to recompilation (which should be evident with the usecounts field showing 1)
the BaseSingleStatementPlanKB field should match the CachedPlanSize attribute of the QueryPlan node in the XML, but only when using a single query batch.
for batches with multiple queries, there should be rows marked as MEMOBJ_STATEMENT in sys.dm_os_memory_objects, one for each query. The pages_in_bytes field for these rows should match the individual <QueryPlan> nodes of the XML plan.

Resources:

What are the different cached objects in the plan cache?
1.0 Structure of the Plan Cache and Types of Cached Objects
2.0 Sql_Handle and Plan_Handle Explained
Documentation for sys.dm_exec_cached_plans
Documentation for sys.dm_os_memory_objects
Documentation for sys.dm_exec_sql_text
Documentation for sys.dm_exec_query_plan


You can use pgScript:

DECLARE @i, @filename;
SET @i = 1;
WHILE @i <= 100
BEGIN
    SET @filename = 'C:\\Program Files\\PostgreSQL\\9.5\\data\\' + CAST(@i AS STRING) + '.csv' ;
    COPY location FROM '@filename' DELIMITER ',' ;
    SET @i = @i + 1;
END

NOTE: You shouldn't have your CSV files under the PostgreSQL data directory. This directory is for the database engine to use, not users. Mistakenly overwriting or deleting any of PostgreSQL files could have your database rendered useless.

You should keep the files somewhere under C:\Users\user\Documents\CSV\... or a similar location in "userland" and give proper permissions to that directory for the PostgreSQL process to read. For a standard install of this version, you normally need to give read permission to the LocalSystem account. Some installations can use the Postgres account).

NOTE 2: pgScript hasn't had much traction. It's not been ported to pgAdmin 4.


MySQL - Log data with Inner Join 12 Tables or Create View?

Normalize, but don't over-normalize.  Do not normalize things where the id is not significantly smaller than the value.  Do not normalize 'continuous' datatypes (ints, floats, dates); it severely hampers use of 'range' filtering.

Why do you have LEFT?  (If you need it, use it; if you don't then remove it so the Optimizer has more options.)

Do have INDEX order_id and do not put that table after LEFT.  Then, the Index can be used to find the rows more quickly than via a table scan.

When you still have performance problems, start with a minimal SELECT that finds the 30 ids that you need. Then join to the rest of the tables.  (And possibly self-joins back to the table to get the other columns needed.)  Use this as a "derived table" (... FROM ( SELECT ... ) ...).

A VIEW is just syntactic sugar.  Use it if it simplifies your code, but don't expect any performance benefit.


How to copy backup files to remote share in SQL Server Agent job without AD/domain accounts involvement?

To answer the specific question of why it is failing is that the service account on your server does not have the correct permissions to access the share.

I have an environment where I have 2 servers not on a domain.  Using SQL Server Agent proxies is the way I solved this.

I have a special account I use for the 2 machines (Your accounts with the same name and password) which I had to add to the instance level credentials (under security in SSMS Object explorer) (see MSDN article "Create a Credential" )  and add an agent proxy under the Operating System (CmdExec) .

Once those are created you can select the account to run as (the name you gave your proxy) and it will work.  However this is on 2008 and I have not played with the 2012 agent that much.
Should still work the same though.

You would then use your robocopy line "robocopy d:\SQLBACKUP  \10.195.xx.yyy\backup /S /purge /MAXAGE:7 /MT:1 /Z" as your command (I use xcopy but it works the same).


Restart ORACLE OHAS without restarting databases

No, because the Grid Infrastructure generally controls networking and storage access. If you can't start the DB without starting GI first, then you can't restart GI without restarting the database too.


Postgresql : How to login as a NOSUPERUSER and create a database?

https://stackoverflow.com/questions/31819377/psql-login-fails-database-not-found?rq=1
I think I understood it. I should connect to an existing database to login as p5 user. Then I create another database "db5".
If postgresql could have allowed login and then allowed to create databases then it would have been an intuitive way.


When you inspect the binary log add -vvv to mysqlbinlog. It will show an effective query that corresponds to a binlog event.