Fix deleted transaction log file in SQL Server

Backup contains the log file as well.

Just restore the database using tsql

Restore database db_name from disk = ' path to backup file.bak' with replace, stats=10

Based on your screenshot, it seems a connection issue .. you are not able to connect to SQL server with ssms.

Also, see this blog by Aaron https://sqlblog.org/2011/01/14/troubleshooting-error-18456


Error connecting to MSSQL server from Linux

The same issue was reported here. Apparently a change to the settings and the ODBC fixed the issue, you may want to give a try.
Also I'm seeing Trusted_Connection=yes is the authentication possible between the linux and the SQL Server? If not maybe you should go with SQL Authentication.


How can we use transaction in mongodb standalone connection?

This is not possible and clearly documented in the manual.

Ref: Retryable Writes

Retryable writes allow MongoDB drivers to automatically retry certain write operations a single time if they encounter network errors, or if they cannot find a healthy primary in the replica sets or sharded cluster.

Retryable writes have the following requirements:

Supported Deployment Topologies

Retryable writes require a replica set or sharded cluster, and do not support standalone instances.

Supported Storage Engine

Retryable writes require a storage engine supporting document-level locking, such as the WiredTiger or in-memory storage engines.


Mysql takes up to 50% cpu after aborting DELETE query

Since sv is a MyISAM table, I would expect a backlog of DB Connections attempting to write to the table in a high-traffic environment.

In fact, any DML (INSERT, UPDATE, DELETE) against the sv table will result in a full table lock on sv.

You should still run a CHECK TABLE sv; as a precaution.

Please keep in mind that data for MyISAM tables are never cached. It is possible that the OS is just swapping used memory as a result of the heavy activity against the MyISAM.

All you can really  do in that instance is reboot the OS


What makes an Oracle Applications DBA different from an Oracle DBA?

As your own question answers: "Oracle Applications is an [ERP]." An "Oracle Apps DBA" will be someone who is experienced in working with the particular schema and tooling around this ERP. There are hundreds (if not thousands) of packages, tables, stored procedures, and interfaces that are specifically built for the ERP. It is not a trivial database to admin. You can get a brief glimpse into some of the things in their ERP by looking around - for example, this blog - or by logging into a Metalink account as someone who is a customer of their ERP solution, where you can get ERDs and other documentation.


ProxySQL equivalent of MaxScale schemarouter

You can find the answer here: Can proxysql have multiple listener? (Google Groups).

In short: ProxySQL's query rules support routing by schemaname.

For simplicity, let's assume you have 3 distinct clusters and we call the 3 clusters HG11, HG21 and HG31. The servers are 10.10.X.Y. To add some complexity, we will also enable read/write split, where the readers are HG12, HG22 , HG32.

INSERT INTO mysql_servers (hostgroup_id,hostname) VALUES
(11,"10.10.10.1"),
(12,"10.10.10.1"), (12,"10.10.10.2"), (12,"10.10.10.3"),
(21,"10.10.20.1"),
(22,"10.10.20.1"), (22,"10.10.20.2"), (22,"10.10.20.3"),
(31,"10.10.30.1"),
(32,"10.10.30.1"), (32,"10.10.30.2"), (32,"10.10.30.3");

Enable replication hostgroups

INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup) VALUES
(11,12),(21,22),(31,32);

Create rules for read/write split

INSERT INTO mysql_query_rules (rule_id, active, match_digest, flagOUT) VALUES
(1,1,'^SELECT.*FOR UPDATE',100),
(2,1,'^SELECT',200),
(3,1,'.*',100);

Sharding, sending traffic to masters

INSERT INTO mysql_query_rules (active, flagIN, schemaname,     destination_hostgroup, apply) VALUES
(1,100, "shard001", 11, 1),
(1,100, "shard002", 11, 1),
(1,100, "shard003", 11, 1),
(1,100, "shard004", 11, 1),
...
(1,100, "shard050", 21, 1),
(1,100, "shard051", 21, 1),
(1,100, "shard052", 21, 1),
(1,100, "shard053", 21, 1),
(1,100, "shard054", 21, 1),
...
(1,100, "shard100", 21, 1),
(1,100, "shard101", 31, 1),
...
(1,100, "shard150", 31, 1);

Sharding, sending traffic to slaves

INSERT INTO mysql_query_rules (active, flagIN, schemaname, destination_hostgroup, apply)
SELECT 1, 200, schemaname, destination_hostgroup+1 , 1 FROM mysql_query_rules WHERE flagIN=100;

Load everything to runtime:

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;

Eventually, save everything to disk:

SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;

About "There is no way to have proxysql deal with the question of which schema is on which hg by itself?" : the answer is no, and this is intentional.
Each HG may have identical schemas: beside the classic "mysql", "information_schema", "performance_schema", you can have other schemas that are used for other purposes (anything really). We can't ask ProxySQL to understand what are these schemas and automatically create rules.

Also, it is possible that you have created a tenant_1 schema in two different servers, but one has production data while the other has testing data: you don't want ProxySQL to automatically add both of them.

Finally, because ProxySQL is easy reconfigurable using simple SQL queries executed on the admin interface, if you want to automate the loading of new schema you can simple create a script that connects to each HG, lists the schemas, and connected to ProxySQL creating the rules if missing. The script can be very simple, but should also have the logic to exclude schemas that shouldn't be included.

Or you can create configurations that are pushed to ProxySQL using configuration management tools like Chef, Ansible, Puppet, Consul, etc.


How to update pg_opclass as non-postgres superuser

It turns out there's a (somewhat hidden) permission for users, namely whether they have permission to "update system catalogs directly," which is specified by the rolcatupdate column in the pg_authid system table:

https://www.postgresql.org/docs/9.3/static/catalog-pg-authid.html

This other answer talks about how to set it:

https://serverfault.com/a/300421

Anyhow, once I set it to TRUE for my user, the UPDATE was allowed.

That column in pg_authid appears to have gone away as of PostgreSQL 9.5.


See Date's book:

Date on Database: Writings 2000-2006

Chapter 4: On the Notion of Logical Difference

Salient quote:

  you'll be aware that I often appeal in my writings to the notion of
  logical difference. That notion is one I find extraordinarily useful
  in my own work; it's a great aid to clear and precise thinking… The
  intent of what follows [in chapter 4 of this book], then, is to offer
  a brief introduction to the concept of logical difference.

…bearing in mind this is Chris Date, for whom "brief introduction" actually amounts to 15 pages of closely-typed prose including footnotes and endnotes!

Follow the above link to the Google Books copy of the book, which should open in your browser, where the chapter seem to be available in full. After you've read the chapter, I'm sure you shall have a good understanding of the concept of logical difference.


Invalid use of side-effecting operator 'INSERT EXEC' within a function

use this code to join rowset to delimited single value

sql,

use this code instead:

    DECLARE @IDs NVARCHAR(max)=''

    SELECT @IDs += ID +','
    FROM dbo.SomeTable

select @IDs

you can add where clause and design your query to return  rowset with CTE and UNION ALL


Postgres requires commit or rollback after exception

The problem I have with your example is that you're talking about JDBC behaviour, but also using explicit "start transaction" etc commands, which seems a bit of a clash, since I'd expect you'd use JDBC's auto-commit mode to manage transactions.

If you are in auto-commit mode, then the two inserts will each be in their own transaction, and the throw of a SQLException for the first one will not affect the second.

If you are not in auto-commit mode, then an implicit "start transaction" is generated before the first insert, and the second insert cannot be processed until the transaction is rolled back. This behaviour is quite different from if you were executing the script with psql.

(JDBC does not specify whether drivers/connections should default to auto-commit on or off, you should always explicitly set it)

Postgresql treats any error processing a statement as immediately aborting the transaction-- essentially like the XACT_ABORT mode in SQL Server. The intent being that if you submit a sequence of commands as a transaction, each one is dependent on the previous ones, so the failure of any one invalidates all the subsequent ones.

If this isn't the behaviour you want inside a transaction, you need to surround the potentially-aborting updates with creating a savepoint, and rolling back to that savepoint in case of an error.

Beware of looking at very old discussions of behaviour (bugs over ten years old definitely count), as at some point in Postgresql's history, there was a session variable called autocommit, and the behaviour could have been quite different. That variable is gone now, replaced (as I understand it) with the concepts of the database or the JDBC driver automatically wrapping commands inside transactions (so in fact there is not really any such thing as non-transactional interaction with postgresql).

Here is what happens when you execute the script you suggest with psql:

steve@steve@[local] =# start transaction;
START TRANSACTION
steve@steve@[local] *=# create table test(id int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
steve@steve@[local] *=# insert into test values (1);
INSERT 0 1
steve@steve@[local] *=# commit;
COMMIT
steve@steve@[local] =#
steve@steve@[local] =# -- Following statement throws a SQLException(duplicate key) in
steve@steve@[local] =# -- PG, SS and ORacle
steve@steve@[local] =# insert into test values (1);
ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (id)=(1) already exists.
steve@steve@[local] =#
steve@steve@[local] =# -- Following statement behaves differently for different DBMS:
steve@steve@[local] =# -- SS and OR: No error...statement runs fine
steve@steve@[local] =# -- PG: Another SQLException thrown...must rollback or commit
steve@steve@[local] =# insert into test values (99);
INSERT 0 1

In order to get the same behaviour as you wrote in the script, you'd have to turn off auto-commit before doing the insert- that stops the JDBC driver from issuing an implicit "start transaction" before it executes the next statement. If you put that implicitly-generated transaction into the psql script, it produces the error you describe:

steve@steve@[local] =# start transaction; -- generated by JDBC driver
START TRANSACTION
steve@steve@[local] *=# -- Following statement throws a SQLException(duplicate key) in
steve@steve@[local] *=# -- PG, SS and ORacle
steve@steve@[local] *=# insert into test values (1);
ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (id)=(1) already exists.
steve@steve@[local] !=#
steve@steve@[local] !=# -- Following statement behaves differently for different DBMS:
steve@steve@[local] !=# -- SS and OR: No error...statement runs fine
steve@steve@[local] !=# -- PG: Another SQLException thrown...must rollback or commit
steve@steve@[local] !=# insert into test values (99);
ERROR:  current transaction is aborted, commands ignored until end of transaction block

As an illustration of why this behaviour exists, consider what happens if I run the first transaction again. The intent is "create the table and populate it with a single row":

steve@steve@[local] =# start transaction;
START TRANSACTION
steve@steve@[local] *=# create table test(id int primary key);
ERROR:  relation "test" already exists
steve@steve@[local] !=# insert into test values (1);
ERROR:  current transaction is aborted, commands ignored until end of transaction block
steve@steve@[local] !=# commit;
ROLLBACK

So as soon as a problem is detected ("test" already exists), the remaining data manipulation isn't appropriate (the row already existed too, anyway)


Is Query Performance different for different versions of SQL Server?

Yes it will be different.  Every version will have tweaks to the optimiser, or code path, or caching algorithms or something.  I would expect the later versions to run faster than the earlier ones.

  I am using indexing to speed up the performance.

No, you're not.  If every row in the table has to be updated then an index is, at best, irrelevant.  If the index contains columns which you are updating then the index's pages also have to be updated incurring additional write overhead.

Have you checked the query plan for the three different instances?  My guess is the 2008R2 has less memory available and the optimiser's choosing to spill to TempDB, or the operating system's forcing paging to the virtual memory pagefile.  Or it may be that the transaction log files for the 8R2 instance started out small and what you're seeing is an artifact of autogrowth to accommodaate six million log records.

I would always recommend batching to my devs when processing this many records.  Update the first N records in cluster key sequence.  Then do the next N, and so forth until all 2M are done.  "N" can be altered to give the best rows/minute on your particular installation, though the tens of thousands tends to be about right.  In this way log files remain smaller, transactional locking is reduced and it is restartable in the case of a failure.


Update a value after an hour when a record is inserted

First thing you should do: be self-critical with your original data model:

Why do you actually need to have a Status column in the table?

The status can be computed from the StartDateTime and EndDateTime. You just have one straightforward alternative: compute its value when needed. If (current_time < start + 1 hour) and (end time is null) => we're in use, otherwise, it's completed. You can have this as a computed column in a VIEW. In MySQL you'd do:

CREATE VIEW job_log_with_status AS
SELECT
    ID, ResourceID, StaffID, StartDateTime, EndDateTime,
    TimeDiff(Coalesce(EndDateTime, now()), StartDateTime) AS TimeSpent,
    CASE
        WHEN EndDateTime IS NOT NULL THEN 'Completed'
        WHEN TimeDiff(now(), StartDateTime) > time '01:00:00' THEN 'Completed'
        ELSE 'In Use'
    END AS status
FROM
    job_log ;

(Assumption: your original table is called job_log)

When used, this view would output something similar to:

SELECT * FROM job_log_with_status;

ID | ResourceID | StaffID | StartDateTime       | EndDateTime         | TimeSpent | status
-: | :--------- | :------ | :------------------ | :------------------ | :-------- | :--------
 1 | CM-01      | 175124N | 2017-07-13 18:41:53 | null                | 02:00:00  | Completed
 2 | CM-02      | 175124N | 2017-07-13 20:11:53 | null                | 00:30:00  | In Use
 3 | CM-01      | 175124N | 2017-07-13 19:26:53 | 2017-07-13 20:26:53 | 01:00:00  | Completed
 4 | CM-02      | 175124N | 2017-07-13 19:56:53 | 2017-07-13 20:06:53 | 00:10:00  | Completed

You can check the setup and some sample data at dbfiddle here


mysqldump - select table in database only if it contains a specific string

What you need is a tool for controlling migrations and upload them to git a.k.a. a version control for MySQL. While you can deploy your own, this is a very well known problem and there are many tools available, some of them free and open source. I am not going to recommend you anything in particular, as they are different on the goals/features/integration with frameworks, but here it is a list of options:

Liquibase
Flyway
c5-db-migration
dbdeploy
MyBatis
Autopatch
Sqitch
online-migration

Some recommended readings: http://en.wikipedia.org/wiki/Schema_migration, http://www.liquibase.org/quickstart and http://www.percona.com/live/mysql-conference-2013/sites/default/files/slides/PLMCE13_online-migration.pdf

If you still want to deploy your own tool (not recommended), have a look at least at mysqldbcompare from mysqlutils and some of the percona-toolkit tools.


@Phil's suggesting of the special char causing an issue was the problem. Corrected and the connection string works fine.


Option #3

NULLs are your friend.  After you select the platform, study how NULLs work for the platform and embrace them.  In this case they accurately reflect the absence of a check-in.  In Oracle NULLS are not indexed, so you could create a function based index that swaps the NULL state giving you a very small index that contains only the entries not checked-in.

Option #1 would need a join anytime you want the Check-In date or status.  Joins are your friend too, but in this case there isn't a benefit to separating this data unless there are times when a single checkout can produce multiple checkins.  Option #2 requires repeating data or even more NULLS than option #3.  Here are the three concepts fleshed out to tables columns and data.

#1
CheckOut
   Asset CheckInOut User DateTime
   1     1          1    3/10/2013
   2     2          1    3/10/2013
   3     3          2    3/11/2013

CheckIn
   CheckInOut DateTime
   1            3/11/2013

#2
CheckInOut
   Asset CheckInOut User DateTime   InOrOut
   1     1          1    3/10/2013  O
   2     2          1    3/10/2013  O
   1     1          1    3/11/2013  I
   3     3          2    3/11/2013  O

#3
CheckOut
   Asset CheckInOut User OutDateTime InDateTime
   1     1          1    3/10/2013   3/11/2013
   2     2          1    3/10/2013
   3     3          2    3/11/2013

SQL Fiddle's showing Option #2 and Option #3 with the additional requirements Joel Brown added and how various questions would have to be answered in SQL.

#2 - http://www.sqlfiddle.com/#!4/55e72/25

#3 - http://www.sqlfiddle.com/#!4/e2f27/6


How to select recursively in a child parent design situation (in MySQL)?

Without additional information such as transitive closure or nested sets, you need some kind of iteration (well, if you know that the length of the chain cant be longer than n you could do that number of left joins). Since 5.7 does not support recursive CTE:s you can express the recursion in a stored procedure instead:

DELIMITER $$
CREATE OR REPLACE PROCEDURE materialized_path
    (IN x BIGINT, IN acc text, OUT path TEXT)
BEGIN
    DECLARE parent_ID INT DEFAULT NULL;
    DECLARE tmppath text default '';

    select original_action_id
    from recurring_actions
    where recurring_action_id = x into parent_ID;

    IF parent_ID IS NULL THEN
        SET path = rtrim(acc);
    ELSE
        CALL materialized_path(parent_ID, rtrim(acc)||parent_ID||',', path);
    END IF;

END$$

DELIMITER ;

call materialized_path(5,'',@path); select @path;
Query OK, 0 rows affected, 1 warning (0.23 sec)

+--------+
| @path  |
+--------+
| 3,2,1, |
+--------+
1 row in set (0.00 sec)

Now you need another procedural part that splits @path into its components and there ordinal numbers.


Best db setup to accomplish yes/no list of clients for daily calls

Sounds like you need a proper CRM  (:
For the days of the week I think you have it right, have a column for each day of the week and if a day is a "no call day" you check it off.  Your query would then say, "ok it's Monday who can I call?".

As far as the adhoc days (on vacation etc).  You may just want to have a notes field that shows on each record on the call list.  This can be reviewed before calling and modified or skipped based on the content of the note.


Convert the table PROCESSLIST from InnoDB to MYISAM

There is no way, because they are virtual tables. This is also the reason why you can't do anything with them, except for SELECT.


Can you use BigQuery to run on top of Bigtable

Yes you can. Link to Bigtable using it as an external table. It is not then created in BigQuery itself. Per the documentation:

  To query Cloud Bigtable data using a permanent external table, you:

  Create a table definition file (for the CLI or API)
  Create a table in BigQuery linked to the external data source
  Query the data using the permanent table

It is in Beta, but this is standard for Google and usually only lasts 6 months or so. In my experience the features and products and alot more stable in Beta than you find on other providers.


Can't accept pgbouncer 6432 port on PostgreSQL server

Your PgBouncer is not listening on IPv6 address because this line tells it so:

listen_addr = 127.0.0.1

You should change it to something which includes IPv6 addresses, like

listen_addr = 127.0.0.1, ::1

or maybe (to listen on all interfaces):

listen_addr = *

Side note: you should probably not publish your real addresses, even if  "440a:90a0:302:4525:lda0:1:2:3" is not a valid ipv6 address :-)


Give users permission to read subsets of data in tables in SSRS

You can use this code to obtain all the Windows groups of which current login is a member:

select distinct name
from sys.login_token
where type = 'WINDOWS GROUP';

You can insert all these groups into a table variable/temp table and then use it in a join with your table that maps Win Groups to StoreKeys which you further join with the table of interest


Will increase in Innodb_buffer_pool_size increases insert query performance?

128M is pathetically low.

If you are running a mixture of InnoDB and MyISAM on a MySQL-only box, I recommend

innodb_buffer_pool_size = 1/3 of RAM (32G for your 96GB machines)
key_buffer_size = 1/10 of RAM (10G)

That leaves some room for data caching for MyISAM since it leaves that up to the OS.

Some of the "Why"...

InnoDB uses 16KB blocks for data and indexes; it caches these blocks in (roughly) a "least-recently-used" fashion in the buffer_pool.  How big should the buffer_pool be?  There are several rules:

It does not have to be much bigger than the actual data+index lengths.
If the "working set" is small, then small is good enough.  After all, that is what "caching" is all about.

MyISAM uses its "key_buffer" for caching index blocks, which are 1KB each.  It depends on the OS for buffering/caching the data, which is not "blocked" (from MyISAM's point of view -- it is just a stream).

Since you are using both engines, and the entire dataset is pushing the amount of RAM, you have a juggling game to play.  You might try 50G and 6G for those two settings.

If you are using UUIDs, you are screwed -- InnoDB and MyISAM are likely to blow out their caches and become I/O-bound as the dataset grows.

But... You said that the machine with the bigger buffer_pool became slower than the other boxes?  This does not make sense.  Unless some query (eg, ALTER TABLE) was hogging resources on it.


I ask about the PROCESSLIST in hopes of discovering whether only certain types of queries cause the leaked tmp table.  Knowing that might expedite finding a solution.

Based on a recent comment in the bug report, it sounds like a cron job doing this might be a workaround (but apparently not):

STOP SLAVE;
START SLAVE;


Suggestions for SQL query repair tool or tips on building one?

Argh! I wanted to open a chat panel to ask you some more questions, but actually inviting doesn't work unless you are already chatting. Oh well.

You have indicated that these are java strings, which means that there are two problems.

Parsing the SQL and making decisions (mark ambiguous items for user)
Finding the SQL inside the java code

Hopefully the SQL should be fairly recognizable. It exists in a string, and sql follows a fairly ridged format. Also, your non-aliased items will follow a simple format too. They will be in columnname AS aliasname. Skip those, and only add aliases for columnnames not in that format. If you load in the db schema, you even get all valid columnnames to look for. Finally, if there is an ambiguous item, then write some context to stdout, and ask the user for input.

I would suggest using python and the PLY library. You get a full blown lex/yacc combo, with an easier language than C. Open a new question on SO if you want more help/or to scream at me. ;)


How does one query a SQL Server Linked Server when SQL Server Authentication is specified?

Open the properties of your linked server. Select Security, and click the "be made using this security context" radio button. Put your SQL Server login in this section. Clear out any logins listed in the box above "Local server login to remote server login mappings", and try again.


MySQL - Turkey/Istanbul Daylight Saving Time Change

DATETIME is like a picture of a clock.  There is no going back to fix it.  A DATETIME column reflects what the user would see on a clock at the time he/she does the INSERT.

TIMESTAMP is stored internally as UTC, but converted going in and going out.  So, if the timezone is set correctly, and if the tz table is correct, TIMESTAMP always reflects a point of time in the cosmos.

So, in addition to getting the tz code 'right', and setting the timezone correctly, use the 'right' datatype.


Script to drop all databases except one not working

How about something like below :

change your output in SSMS to text and once you are happy run the generated sql statements in another query window :

use master
go
select 'Alter database '+ quotename(name) + char(10) +'set single user with rollback immediate' +char(10)+ 'go'+ char(10)+ 'Drop database '+quotename(name) + char(10)+ 'go'
from sys.databases
where database_id > 4 -- exclude system databases
and name not in ('') -- exclude databases that you want to SAVE !!

Update :  Below is the script that will work in sql agent as well...

/****************************************************************************************
Author      :   Kin Shah
                written for dba.stackexchange.com

Purpose     :   Drop all the databases except system and user defined

Notes       :   1. EXCLUDE databases name not in ('drop1','drop2','drop3')
                2. Remove the commented ---exec (@sqltext) to ACTUALLY drop the databases
******************************************************************************************/

declare @dbname sysname
declare @sqltext nvarchar(max)

begin
    select @dbname = min(name) from sys.databases where database_id > 4 and
    name not in ('drop1','drop2','drop3')  ---- CHANGE HERE FOR Databases that needs to be excluded !!

    while @dbname is not null

    begin
        select @sqltext = 'ALTER DATABASE '+quotename(name)+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
        FROM sys.databases where name = ''+@dbname+''

        select @sqltext = @sqltext+ char(10)+ 'DROP DATABASE '+QUOTENAME(name)+';'  FROM sys.databases where name = ''+@dbname+''

        print @sqltext

        ---exec (@sqltext)

        select @dbname = min(name) from sys.databases where database_id > 4 and name > @dbnamename and
        name not in ('drop1','drop2','drop3') ---- CHANGE HERE FOR Databases that needs to be excluded !!
    end
end


Should you ever not assign an unique ID to each row in a table?

Yes, for example in case of a mapping table representing an N:M association between two tables. In its simplest form, it contains only two foreign keys, and these form its compound key, so it needs no separate ID column. In fact, adding an ID to such a table (without a uniqueness constraint, as pointed out by @Jeff) would allow one to add multiple associations between the same two rows, which is usually not desired.


SELECT from Clustered Columnstore results in LOB Reads

Multiple sources appear to indicate Columnstore Indexes store its data as LOB data, which is happening in this case.  My understanding and take-away is that it should always be expected for LOB reads to occur when using Columnstore indexes.

What's the anatomy of a Columnstore index?

How to use Columnstore indexes in SQL Server

Inside the SQL Server 2012 Columnstore Indexes

SELECT      COUNT([partition_number]) AS [Number of Partitions],
            SUM([in_row_used_page_count]) AS [SUM_in_row_used_page_count],
            SUM([lob_used_page_count]) AS [SUM_lob_used_page_count],
            SUM([used_page_count]) AS [SUM_used_page_count],
            SUM([row_count]) AS [SUM_row_count]
FROM        [sys].[dm_db_partition_stats]
WHERE       [partition_id] IN   (
                                    SELECT  [p].[partition_id]
                                    FROM    [sys].[partitions] AS [p]
                                    INNER JOIN  [sys].[objects] AS [o] ON [o].[object_id] = [p].[object_id]
                                    WHERE   [o].[object_id] = 971254615
                                );

Results:

Number of Partitions SUM_in_row_used_page_count SUM_lob_used_page_count SUM_used_page_count  SUM_row_count
-------------------- -------------------------- ----------------------- -------------------- --------------------
847                  0                          5630558                 5630558             5175958971


How to Remedy Inconsistent MySQL Replication SYSDATE Value

Virtually everything that is replicated is frozen before replication.  For example, RAND, NOW, UNIX_TIMESTAMP are all passed through the replication stream so that the Slave will have exactly what the Master has.

But... SYSDATE is an exception.  You should not use it if you want identical data.

Or...  "sysdate-is-now" is an option (either on the server commandline or via some setting).  This makes SYSDATE act like NOW.

What version are you running?  See http://bugs.mysql.com/bug.php?id=15101


How to automatically stagger transaction log shipping to minimize bandwidth usage peaks?

DBCC SQLPERF (LOGSPACE) will return the size of the log file in MB & the current percentage used. From that you can calculate the current utilised portion of the log file in MB.

That by itself will not be sufficient to calculate log backup size, not least because you are using backup compression. However collecting this data over time & correlating it with the size of the backups might reveal a linear relationship.

You could insert a step into the Log Shipping backup job to record the size of the utilized portion of the log file and a another step after the backup to record the new size of the log file and the size of the backup.

Running this for a period and analyzing the results could allow you to estimate the size of the backup file.

Not the most straight forward method and to be honest it might not even work. If you are interested in giving it a go I would be interested in helping.


How to insert UNIQUE IDs values from several tables into events table

You can use this insert statement to insert some totally useless data in there (assuming the FK accuracy is not important here) but it satisfies your conditions.

    DELIMITER $$
DROP PROCEDURE IF EXISTS Events_insertion$$
CREATE PROCEDURE Events_insertion()
BEGIN
    DECLARE i  INT;
    DECLARE dTime DATETIME;
    SET i = 1;
    SET dTime = NOW();
    WHILE i  <= 10000 DO
       INSERT INTO EVENTS_TEST VALUES
        (
        null,
        (ELT(0.5 + RAND() * 6, "rep_login",
            "rep_share",
            "email_viewed",
            "brand_page_opened",
            "media_viewed",
            "rep_contacted")),
        LAST_INSERT_ID()+2,
        LAST_INSERT_ID()+3,
        LAST_INSERT_ID()+4,
        LAST_INSERT_ID()+5,
        LAST_INSERT_ID()+6,
        (dTime + interval (LAST_INSERT_ID()) second),
        (dTime + interval (LAST_INSERT_ID()) minute)
        );
       SET  i = i + 1;
    END WHILE;
END$$
DELIMITER ;

You can call this procedure like this

call Events_insertion();

and also try not to use name events as your table name since it is a reserved word.
Hope this could help you :)


Obfuscate data during migration with MySQL Workbench

No, that's not possible. Better you do the migration first and then run a script over the copy to obfuscate fields.


Modelling a second level table relationship

I recently answered a question just like this one here:

How to structure Tables with changing status without duplicating data

You have two options, create the foreign key from Orders using both the AddressId and CustomerId or remove the CustomerId entirely from the Orders table as you can obtain it through the relation anyway.


Less than operator not working on where clause in mysqldump

Put the options first.  That is, all parameters that start with - or -- before the non-option parameters (database  account) and redirection (> ... or < ...)

If that does not suffice, then remember that the command line processor is handling the first level of quotes, so change to

"--where=insertDateTime < '2019-08-13 18:00:00'"

(but I am not confident that this will help)

The quotes are to deal with spaces that are not separators -- as in the middle of the path or the 'where' expression.


Using parameters in stored procedure with plpgsql

Problems:

You cannot parameterize identifiers (like table names) in plain SQL. You need dynamic SQL using EXECUTE for this.
amount is integer, but count() returns bigint.
RETURN amount AS id; is invalid syntax. To force a particular column name for a returned scalar (not a set-returning function), use an OUT parameter. Example:

Get latest timestamp from column in given table dynamically

We are talking about a function, not a stored procedure. See:

In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?

This function would work:
CREATE OR REPLACE FUNCTION any_overlap (_tbl regclass, OUT id bigint)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format(
   $q$
   SELECT count(*)
   FROM   %1$s a
   JOIN   %1$s b ON a.polygon && b.polygon AND st_relate(a.polygon, b.polygon, '2********')
   WHERE  a.ctid <> b.ctid
   $q$, _tbl)
   INTO id;
END
$func$;

Call:
SELECT any_overlap ('public.mytable');

Be wary of SQL injection when using dynamic SQL! Note the type regclass for the passed table name. See:

Table name as a PostgreSQL function parameter


Update statistics for all databases all tables in a server

Right off the bat, your script completely ignores schema, so it will fail unless all of your production servers only use dbo explicitly as the namespace for all tables (and nobody has a default schema other than dbo). You're also using a deprecated data type (NTEXT) for seemingly no reason. Also I don't think the WHERE clause against sys.tables is necessary (I don't know how anything but a U could ever be in there). Not sure why you bother ordering by file size; the job isn't going to finish faster if you handle the biggest databases first (also the join could return multiple rows for a database if it has more than one file in data_space_id = 1, which means you would potentially process that database multiple times).

Finally, I have to wonder if it is really wise to blindly update stats on every table in every database across your entire instance, regardless of size... and why you don't bother checking for indexed views, which also require maintenance. You could save quite a bit of work if you add some logic to exclude any tables that don't need it.

My partial solution

This code will be slightly harder to maintain, doesn't have the TRY/CATCH wrappers yours does, and doesn't add any additional logic about which tables to actually pursue, but since I get a kick out of making automation tasks out of dynamic SQL (and really enjoy solutions that cause no horizontal or vertical scrollbars on this site), here you go. When you're happy with the output, comment the print and uncomment the exec.

DECLARE @sql NVARCHAR(MAX) = N'', @stats NVARCHAR(MAX) = N'';

SELECT @sql += N'EXEC ' + QUOTENAME(name) + '.sys.sp_executesql @stats;'
  FROM sys.databases
  WHERE [state] = 0 AND user_access = 0; -- and your other filters

SET @stats = N'DECLARE @inner NVARCHAR(MAX) = N'''';
  SELECT @inner += CHAR(10) + N''UPDATE STATISTICS ''
    + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name) + '';''
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s
    ON t.[schema_id] = s.[schema_id];
  PRINT CHAR(10) + DB_NAME() + CHAR(10) + @inner;
  --EXEC sys.sp_executesql @inner;'

EXEC [master].sys.sp_executesql @sql, N'@stats NVARCHAR(MAX)', @stats;


Can't restart Postgres on Windows 10 with external drive

What type of partitioning scheme and USB chipset (USB-sata bridge) does the drive use? There are all sorts of unexpected limits you can still hit on a modern USB drive with certain partition types/sizes and chipsets. I've run into issues with 2TB limits imposed by some USB drive controllers. You can format a partition larger than that but when you try to read/write to a higher block number you get errors.

You can also hit limits imposed by the older MSDOS partition scheme which can be overcome by reformatting as GPT (obviously move/backup your data first).

There's a more indepth explanation here: here and here

So your solution might simply be to buy a more modern/high-end external enclosure.

Another possibility is you're hitting some sort of bug related to writeback caching. You could try adjusting the drive cache settings as described in this article

The fact it works in win7 but not win10 could also indicate a bad driver but not sure if there's anything you can do about that other than check the manufacturers website.

You can test all of the above theories by trying to write some really big files to the drive and then testing the files for corruption, one way to do that is to record a giant MPEG with your webcam or VLC screen capture. To be a useful test the files need to be really big (like 2TB+)


How is INFORMATION_SCHEMA implemented in MySQL?

The INFORMATION_SCHEMA database is made up of temporary tables using the MEMORY storage engine.

Example: Here is the table INFORMATION_SCHEMA.TABLES in MySQL 5.5.12 (Windows Version)

mysql> show create table information_schema.tables\G
*************************** 1. row ***************************
       Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

There is no physical folder for those tables, not even .frm files. You cannot mysqldump it. You cannot drop it. You cannot add tables to it. You cannot drop tables from it. So, where are the tables ???

All tables in the INFORMATION_SCHEMA database are stored directly in memory as MEMORY storage engine tables. They are totally internal to MySQL, so the .frm mechanisms are handled in mysqld. In my answer, I first showed the table layout of INFORMATION_SCHEMA.TABLES. It is a temporary table in memory. It is manipulated using storage engine protocols. Thus, when mysqld is shutdown, all information_schema tables are dropped. When mysqld is started, all information_schema tables are created as TEMPORARY tables and repopulated with metadata for every table in the mysql instance.

The INFORMATION_SCHEMA database was first introduced in MySQL 5.0 to give you access to  metadata about tables of other storage engines. For example, you could do SHOW DATABASES to get a list of databases. You could also query for them like this:

SELECT schema_name database FROM information_schema.schemata;

You could retrieve table names in a database in two ways:

use mydb
show tables;

or

SELECT table_name from information_schema.tables WHERE table_schema = 'mydb';

Since its inception, MySQL has expanded the INFORMATION_SCHEMA database to have the processlist (as of MySQL 5.1). You can actually query the processlist looking for long running queries that are still running at least 10 minutes:

SELECT * FROM information_schema.processlist WHERE time >= 600\G

You can use the INFORMATION_SCHEMA to do every elaborate things: such as :

Get counts of all tables using specific storage engines:

SELECT COUNT(1) TableCount,IFNULL(engine,'Total') StorageEngine
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','mysql')
AND engine IS NOT NULL
GROUP BY engine WITH ROLLUP;

Get the recommended MyISAM Key Buffer Size in MB

SELECT CONCAT(ROUND(KBS/POWER(1024,IF(pw<0,0,IF(pw>3,0,pw)))+0.49999),
SUBSTR(' KMG',IF(pw<0,0,IF(pw>3,0,pw))+1,1)) recommended_key_buffer_size
FROM (SELECT SUM(index_length) KBS FROM information_schema.tables WHERE
engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) A,
(SELECT 2 pw) B;

Get the recommended InnoDB Buffer Pool Size in GB

SELECT CONCAT(ROUND(KBS/POWER(1024,IF(pw<0,0,IF(pw>3,0,pw)))+0.49999),
SUBSTR(' KMG',IF(pw<0,0,IF(pw>3,0,pw))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,(SELECT 3 pw) B;

Get Disk Usage of all Databases By Storage Engine in MB

SELECT Statistic,DataSize "Data Size",IndexSize "Index Size",TableSize "Table Size"
FROM (SELECT IF(ISNULL(table_schema)=1,10,0) schema_score,
IF(ISNULL(engine)=1,10,0) engine_score,
IF(ISNULL(table_schema)=1,'ZZZZZZZZZZZZZZZZ',table_schema) schemaname,
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,"Storage for All Databases",
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,CONCAT("Storage for ",B.table_schema),
CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') DataSize,
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') IndexSize,
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') TableSize
FROM (SELECT table_schema,engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY table_schema,engine WITH ROLLUP) B,
(SELECT 2 pw) A) AA ORDER BY schemaname,schema_score,engine_score;

Believe me, there are still more wonderful uses for INFORMATION_SCHEMA that time does not permit me to discuss further.

Please keep in mind that the INFORMATION_SCHEMA is so sensitive that if mysql is running and you do the following:

cd /var/lib/mysql
mkdir junkfolder

and then go into mysql run

mysql> SHOW DATABASES;

You will see junkfolder as one of the databases.

Knowing it is very vital for DBAs and Developers. Chapter 20 (developers) and Chapter 31 (DBAs) of the book MySQL 5.0 Certification Study Guide

are there for preparing for the Developer and DBA Certification Exams. Get the book, study those chapters well, and you could do great things with MySQL's INFORMATION_SCHEMA.

The INFORMATION_SCHEMA database as of MySQL 5.5, now features plugins, global variables (status and static), session variables (status and static), storage engine status, performance metrics instrumentation, trigger map, events (programmable) and much more.

Sorry this may seem like WTMI but I am a big proponent of using the INFORMATION_SCHEMA database.


Why is Microsoft SQL Server creating a ton of dump files?

You can check the SQL Server Error Log to get more information as to why dump files were generated. Microsoft also provides the sys.dm_server_memory_dumps dmv for troubleshooting, but it isn't clear to me how that dmv would be helpful.


Can I bring a warm standby SQL Server database online and then return to standby mode?

From what you are describing about what Actifio needs you should be able to put the secondary database in readonly mode after you restore the latest log file. See Kendra Little's post on log shipping secondaries in read only mode, she goes over some things that you need to account for. One of the things she mentions you'll need to account for putting the database in standby readonly is in order to continue restoring the next transaction log file you need to kick all of the users out of the database. That means you need to setup your restore scripts and the Actifio tool to work around each other.


Restore db with encrypted column in SQL Server, without overwriting existing Service Master Key

Server1 running SQL Server 2012 with Service Master Key A, db1 with Database Master Key 1, symmetric key and certificate available.

I assume that the db1 master key is encrypted with the SMK. This makes everything encrypted by the database master key 'available' to applications, w/o having to explicitly open the database master key.

What you need to do is to restore the database, open the database master key using the DBMK password and then add the Server 2 SMK encryption  to the DBMK:

RESTORE DATABESE ... FROM ...;
USE ...;
OPEN MASTER KEY DECRYPTION BY PASSWORD = ...;
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;


After mysql server restarts I cannot see my own database

Make the sure the datadir in new cnf file is the same as the old one then restart the service and try again.


Transform all columns records to lowercase

You can do this:

UPDATE table_name SET column=lower(column)

Refer to www.postgresql.org/docs/9.1/static/functions-string.html


Consults and counts in multiple databases

You can join the tables and then use (conditional) aggregation.

SELECT c.id client_id,
       c.name client_name,
       count(p.id) total_items,
       count(CASE
               WHEN ps.status = 'new'
                 1
             END) items_new,
       count(CASE
               WHEN ps.status = 'used'
                 1
             END) items_used
       FROM clients c
            LEFT JOIN products p
                      ON p.client_id = c.id
            LEFT JOIN product_status ps
                      ON ps.product_id = p.id
       GROUP BY c.id,
                c.name;

This shows data for all clients. If you only want them actually having a product, you need to change the first LEFT JOIN to INNER JOIN.

It also counts products which have no status in items. If you don't want to include such products, change the second LEFT JOIN to INNER JOIN as well.


Cannot add Network Service as User/Login

It can be a bit misleading, when working with the German version of SQL Server and/or a Windows Client/Server OS.

When you select the <HOSTNAME>\Network Service from the Active Directory pop-up in the SQL Server Login dialog, then SQL Server automatically displays the login (when closing all the dialogues with OK) as:

 <HOSTNAME>\Network Service

This is actually wrong.

German Solution

If you enter the following German string then it will resolve correctly:

NT-Autorität\Netzwerkdienst

English Solution

If you were to use an English Windows / SQL Serer installation then you would have to enter:

NT-Authority\NetworkService

If you click on OK, then the SQL Server Login will be created.


Enforce a single NULL end date per foreign key value using an index

You can do this with a unique filtered index.

Table setup.

create table dbo.OtherTable
(
  FK int primary key
);

create table dbo.YourTable
(
  PK int identity primary key,
  FK int references dbo.OtherTable(FK),
  DateEnd date
);

Add the unique index on FK filtered on DateEnd is null.

create unique index UX_YourTable_FK_DateEnd on dbo.YourTable(FK)
  include (DateEnd)
  where DateEnd is null;

Add one row to OtherTable.

insert into dbo.OtherTable(FK) values(1);

Add a couple of rows to YourTable.

insert into dbo.YourTable(FK, DateEnd) values(1, '2001-01-01');
insert into dbo.YourTable(FK, DateEnd) values(1, null);

Add another row with a value in DateEnd will work just fine.

insert into dbo.YourTable(FK, DateEnd) values(1, '2001-01-01');

Add a second row with null in DateEnd will fail.

insert into dbo.YourTable(FK, DateEnd) values(1, null);

  Msg 2601, Level 14, State 1, Line 33 Cannot insert duplicate key row
  in object 'dbo.YourTable' with unique index 'UX_YourTable_FK_DateEnd'.
  The duplicate key value is (1).

Create Filtered Indexes
Create Unique Indexes

When scripting indexes from SSMS:

Make sure "Script Indexes" is True in the Advanced Scripting Options. It is default True if you script the entire database but False if you specify a specific table.
Also make sure the setting under "Tools / Options / SQL Server Object Explorer / Scripting" is set to "Script for server version 2008" (at least) otherwise the filtering condition will be silently omitted (Connect bug report).


How to handle reporting of an extreme amount of databases in SQL Server 2005?

In our shop at my previous job, we had a secondary set of servers where we tested our restores. For our busiest customers we would restore tonight's backup, mark it as read_only, and their reporting tomorrow would connect to that copy of the database for all reports from yesterday back. This offloaded about 90% of the reporting workload and doubled as a backup/restore validation method. So if most reports don't need today's data, you could consider alleviating some of the production workload this way with some cheaper hardware - if you're not using Enterprise features you could even use Express for all the databases that are < 10GB. (Well, I see it's 2005, which had a lower DB size limitation, but you could always restore your copy forward into 2008/R2.) This would allow you to really distribute the databases to many low-end commodity servers (VMs or pizza boxes).


Finding which DATE columns have no time data using TRUNC

This is because the data representation of dates depends on your NLS settings.

You can change the representation of dates for your current SQL statements before you issue your first command by using the following command:

alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

Or seeing as you are using the slash notation:

alter session set nls_date_format = 'dd/mm/yyyy hh:mi:ss am';

If you then having a column containing a date format, then it should be returned as:

29/06/2002 1:50 pm

References

Setting Up a Globalization Support Environment (Oracle Docs)


Table properties in SSMS and sp_spaceused don't match

Data space used

The query that gets used to calculate the data space on the table via ssms when selecting the storage properties of my dbo.Employees table

exec sp_executesql N'
        declare @PageSize float
        select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=@_msparam_0 and v.type=@_msparam_1

SELECT

        case
        when (tbl.is_memory_optimized=0) then
        ISNULL((select @PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
        FROM sys.indexes as i
        JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
        JOIN sys.allocation_units as a ON a.container_id = p.partition_id
        where i.object_id = tbl.object_id),0.0)
        else
        isnull((select (tms.[memory_used_by_table_kb])
        from [sys].[dm_db_xtp_table_memory_stats] tms
        where tms.object_id = tbl.object_id), 0.0)
        end
       AS [DataSpaceUsed]
FROM
sys.tables AS tbl
WHERE
(tbl.name=@_msparam_2 and SCHEMA_NAME(tbl.schema_id)=@_msparam_3)
OPTION (FORCE ORDER)
',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000)',@_msparam_0=N'1',@_msparam_1=N'E',@_msparam_2=N'Employees',@_msparam_3=N'dbo'

Executing this query gives me:

DataSpaceUsed
20008

This is the data in KB's, for the calculation, keep reading.

Via SSMS it gives me:

Lets explore a part of this query further, for normal, non memory optimized tables.

when (tbl.is_memory_optimized=0) then
        ISNULL((select @PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)

With the pagesize variable as a value of dbo.spt_values / 1024

select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=@_msparam_0 and v.type=@_msparam_1

Which can be changed to

    declare @PageSize float
    select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=1 and v.type='E'
  select @PageSize

= 8

Which is the 8KB page size as used by sql server

Then the next part, CASE WHEN a.type <> 1 THEN a.used_pages

With the type 1 being:

  1 = In-row data (all data types, except LOB data types)

Source

And the next part

WHEN p.index_id < 2 THEN a.data_pages ELSE 0

To get the clustered / heap data pages, without the nonclustered index pages.

So in short, get the lob data & data pages belonging to the heap or clustered index that is in sys.allocation_units

Where does this take us?

This gets us to link to a different Q/A, with the asker trying to get a performant and correct space used query by using the DMV's.

Same goes for the other linked answer, both excellent answers by by Solomon Rutzky

  The following base query works. It gets the same output as
  sp_spaceused, using the same algorithm, but much more efficiently...

So yeah, sp_spaceused will be more correct as the algorithm used to calculate the size accounts for more information, the query in the linked Q/A does the same but more efficiently.

You can take a look at the dmv's touched by running sp_spaceused with the actual execution plan enabled with CTRL+P, or look at this one, when I ran sp_spaceused on dbo.Employees

The usage of sys.dm_db_partition_stats in sp_spaceused and the query linked
vs.  the usage of sys.allocation_units via ssms is also apparent.


You can have two fields in schedule table:

operation_type
operation_id

Then you can select everything like this:

SELECT s.*, l.weight, d.driver, d.fuel, mo.mechanic, mo.parts
  FROM schedule AS s
  LEFT JOIN loading_operation AS l ON s.operation_id=l.id
                                  AND s.operation_type='l'
  LEFT JOIN delivery_operation AS d ON s.operation_id=d.id
                                  AND s.operation_type='d'
  LEFT JOIN maintenance_operation AS mo ON s.operation_id=mo.id
                                  AND s.operation_type='m';

and the the output of this query would be something like:

id  date_from  date_to    operation_type operation_id weight driver fuel mechanic
 1 2019-02-06 2019-03-14       m            5                               874
 2 2019-01-16 2019-02-24       d            3                 431     838
 3 2019-03-16 2019-03-03       m            5                               874
 4 2018-12-16 2019-02-11       m            2                               975
 5 2019-02-12 2019-03-03       d            1                 825     438
 6 2019-02-12 2019-03-14       d            7                 604     534
 7 2019-01-11 2019-02-25       l            9          304
 8 2019-03-15 2019-02-14       m            8                               188
 9 2019-03-05 2007-04-20       m            2                               975
10 2019-02-25 2007-04-22       m            5                               874


How to add a new column if this doesn't exist

You can use INFORMATION_SCHEMA.COLUMNS to check if the column exists or not.

CREATE TABLE t(rid int, foo int);

INSERT INTO t VALUES (1,1),(2,2),(3,3);

CREATE FUNCTION fnn(id int, new_val int) RETURNS void AS $$
BEGIN

  IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't' AND COLUMN_NAME = 'bar') THEN
      ALTER TABLE t
        ADD bar int;
  END IF;

 UPDATE t
 SET    bar = new_val
 WHERE  id = rid;

END; $$
LANGUAGE plpgsql;

SELECT fnn(1, 15);

SELECT * FROM t;

rid | foo |  bar
--: | --: | ---:
  2 |   2 | null
  3 |   3 | null
  1 |   1 |   15

db<>fiddle here


LocalDB v14 creates wrong path for mdf files

UPDATE

As of CU 6 for SQL Server 2017, this bug has been fixed. It is now possible to execute the following successfully:

CREATE DATABASE [CreateDatabaseTest];
DROP DATABASE [CreateDatabaseTest];

The problem, and the fact that it is fixed in CU6, is documented in the following KB article:
FIX: "Access is denied" error when you try to create a database in SQL Server 2017 Express LocalDB

To get the Cumulative Update, please go to the following page and grab the top (i.e. latest) build, which might be newer than CU6 depending on when you see this:

SQL Server 2017 build versions

BELOW INFO OBSOLETE AS OF SQL SERVER 2017 CU6 (Released 2018-04-17)

The lack of a backslash in the combined Path + File name appears to be a bug with SQL Server 2017. I just ran into it myself. I even tried editing the Registry to add a DefaultData string Value for C:\Users\MyAccountName\ in both of the following Keys (the 3 default paths are not in any of the LocalDB registry keys that I looked through):

Computer\HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\UserInstances\{some-GUID-value}
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14E.LOCALDB\MSSQLServer

And yes, I did shutdown and start up again the LocalDB instance in both attempts.

However, I am not convinced that not being able to change the default paths is a bug as it might just be poor documentation and poor error handling combined. I say this because I just tried editing the default locations for SQL Server LocalDB versions 2014, 2016, and 2017, and all resulted in the exact same error, which in itself is odd due to being from RegCreateKeyEx(), which should be dealing with the Registry and not the file system.

Not being able to change the path is unfortunate due to the lack of backslash when creating a new Database without specifying the files to use. However, I was able to create a new Database using the full CREATE DATABASE syntax as follows:

CREATE DATABASE [XXXXX]
 CONTAINMENT = NONE
 ON PRIMARY
( NAME = N'XXXXX_sys', FILENAME = N'C:\Users\MyAccountName\XXXXX_sys.mdf',
  SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ),
 FILEGROUP [Tables] DEFAULT
( NAME = N'XXXXX_data', FILENAME = N'C:\Users\MyAccountName\XXXXX_data.ndf',
  SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON
( NAME = N'XXXXX_log', FILENAME = N'C:\Users\MyAccountName\XXXXX_log.ldf',
  SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 COLLATE Latin1_General_100_CS_AS_KS_WS_SC;
GO


Keep empty string instead of null for /copy command

Figured it out. I need to escape and output the single quotes

\copy agltransact FROM 'dbo_agltransact.csv' with null as E'\'\'' CSV HEADER;


Sql Server - Select while insert on another transaction gives unexpected results

I haven't managed to reproduce this after running your code a few times.

I presume that it must happen when a later row gets inserted onto an earlier page in the file though.

So the order of operations is (for example)

Rows inserted into heap on pages 200, 207, 223
Select statement starts and performs an allocation ordered scan. Finds that the first page is 200 and is blocked waiting on a row lock to be released.
Other rows are inserted by the first transaction. Some of them are allocated on a page before 200. Insert transaction commits.
Row lock released and continues allocation ordered scan. Rows earlier in the file are missed.

The table comprised 10 pages. By default the first 8 pages will be allocated from mixed extents and then it will be allocated a uniform extent. Maybe in your case space was available in the file for a free uniform extent prior to the mixed extents that were used.

You can test this theory by running the following in a different window after you have reproduced the issue and seeing if the missing rows from the original SELECT all appear at the beginning of this resultset.

SELECT [SomeData],
       Moment,
       SomeInt,
       file_id,
       page_id,
       slot_id
FROM   [SomeTable]
/*Undocumented - Use at own risk*/
CROSS APPLY sys.fn_PhysLocCracker(%% physloc %%)
ORDER BY page_id, SomeInt

The operation against an indexed table will be in index key order rather than allocation order so will not be affected by this particular scenario.

An allocation ordered scan can be carried out against an index but it is only considered if the table is sufficiently large and the isolation level is read uncommitted or a table lock is held.

Because read committed generally releases locks as soon as the data is read it is possible  at for a scan against the index to read rows twice or not at all (if the index key is updated by a concurrent transaction causing the row to move forward or back) See The Read Committed Isolation Level for more discussion about this type of issue.

By the way I was originally envisaging for the indexed case that the index was on one of the columns that increases relative to insert order (any of Id, Moment, SomeInt). However even if the clustered index is on the random SomeData the issue still doesn't arise.

I tried

DBCC TRACEON(3604, 1200, -1) /*Caution. Global trace flag. Outputs lock info
                               on every connection*/

SELECT TOP 2 *,
             %%LOCKRES%%
FROM   [SomeTable] WITH(nolock)
ORDER BY [SomeData];

SELECT *,
       %%LOCKRES%%
FROM   [SomeTable]
ORDER BY [SomeData];

/*Turn off trace flags. Doesn't check whether or not they were on already
  before we started, with TRACEOFF*/
DBCC TRACEOFF(3604, 1200, -1)

Results were as below

The second resultset includes all 1,000 rows. The locking info shows that even though it was blocked waiting on lock resource 24c910701749 when the lock was released it doesn't just continue the scan from that point. Instead it immediately releases that lock and acquires a row lock on the new first row.


One way is to use a Derived Table (subquery) and get all the maximum id values for every unique remote_id value. We can then JOIN this subquery  to the original table to get the rows corresponding to highest id.

SELECT
  t.*
FROM table_name AS t
JOIN (SELECT
        remote_id,
        MAX(id) AS max_id
      FROM table_name
      GROUP BY remote_id) AS dt
  ON dt.remote_id = t.remote_id
     AND dt.max_id = t.id

Another way is to use a Correlated Subquery inside the WHERE clause. We will get the Max() id value for every remote_id and match against the same.

SELECT
  t.*
FROM table_name AS t
WHERE t.id = (SELECT MAX(t2.id)
              FROM table_name AS t2
              WHERE t2.remote_id = t.remote_id)


How to see entire longtext column in MySQL client

The query column on x$statement_analysis shows more than 1000 characters. This was big enough for the problem I was working on. I do not know if it has some limit.
Here is the max size on the production database that I was working on:
--------------
select max(length(query))
from x$statement_analysis
--------------

+--------------------+
| max(length(query)) |
+--------------------+
|               1747 |
+--------------------+
1 row in set (0.08 sec)

So the answer is to use sys.x$statement_analysis instead of sys.statement_analysis and your query text will not be as limited.
Bobby


Referring to a network location without volume label in VBA for Access 2013

Using the Linked Table Manager dialog, you can relink the front-end to the back end tables.  When you browse to select the back-end file, use a standard UNC network path instead of mapped drives.  For example, you would browse to "\filesvr2\NetworkFolder\SubFolder\Database.accdb".  This makes the front-end portable between any PC on your LAN that can resolve that file server by name.


How can I use LEAD LAG-like functions on multiple columns simultaneously

I guess one could order by coalesce(start_data, end_date)

WITH cte AS (
SELECT COALESCE(start_data, end_date) dt,
       ROW_NUMBER() OVER (ORDER BY COALESCE(start_data, end_date)) rn
FROM datatable
)
SELECT MIN(dt) start_data, MAX(dt) end_date
FROM cte
GROUP BY ROUND((rn-1)/2)

This query is a model. The final text is dependent by DBMS.

Maybe you will need to replace MIN/MAX with CASE WHEN rn%2 = [0|1]


"It depends"

If you add a column that does not require adding data to the rows, then it can be quite quick.

For example, adding an int or char requires physical row movements. Adding a nullable varchar with no default shouldn't (unless the NULL bitmap needs to expand)

You need to try it on a restored copy of production to get an estimate

Creating a new table, copying, renaming may take longer if you have to re-add indexes and keys on a billion row table.

I have changed billion row tables that took a few second to add a nullable column.

Did I say to take a backup first?


You can use a CROSS JOIN LATERAL ( ... LIMIT 1 ) to accomplish this, along with btree_gist and <->

CREATE EXTENSION IF NOT EXISTS btree_gist;

SELECT f1.id AS base, f2.id AS after
FROM foo AS f1
CROSS JOIN LATERAL (
  SELECT id, ts
  FROM foo AS f2
  WHERE f2.ts BETWEEN f1.ts+'15 minutes' AND f1.ts+'25 minutes'
  ORDER BY f1.ts+'20 minutes' <-> f2.ts ASC
  LIMIT 1
) AS f2(id,ts);

 base | after
------+-------
    1 |     3
    2 |     4
    3 |     5
(3 rows)

There are a lot of tricks going on here. We use <-> as a distance operator for your "closest to". Essentially, it can use a GiST index to determine this with KNN. We also use a CROSS JOIN LATERAL ... LIMIT 1 to get the list of all potential roles, and then limit it to just the one that is "closest to" as determined by our knn operator above.

This will work fastest with a gist index on ts.

CREATE INDEX ON foo USING gist ( ts );

See also another question using <-> on a time stamp


Are there any in-memory hybrid databases available?

H2 and HSQLDB will cache the tables in memory, even if you use "persistent" tables.

In HSQLDB you can control the amount of memory that is used to hold table data, using the SET FILES CACHE SIZE statement. I think H2 has something similar.

So if you create cached tables and make the data cache large enough you will wind up with tables stored on disk but held in memory.


MySQL Community Edition: Can I replicate my entire server with another server in one go?

I answered a question on how to use rsync to copy a live DB to another location.

You would repeat rsync process multiple times until two rsync's run in the same time.

Then, you would shutdown mysql on the master, perform a final rsync to the new server, and start mysql on the master.

You then properly prep the new server, make sure /var/lib/mysql has everything, make sure you erase all binary logs on the new server, and make sure you have the same my.cnf on both machines (you may want to change the server_id)

Here is an excerpt from that link:

Step 01) install the same version of mysql on ServerB that ServerA has

Step 02) On ServerA, run SET GLOBAL innodb_max_dirty_pages_pct = 0; from mysql and about 10 minutes (This purges dirty pages from the InnoDB Buffer Pool. It also helps perform a mysql shutdown faster) If your database is all MyISAM, you can skip this step.

Step 03) rsync /var/lib/mysql of ServerA to /var/lib/mysql on ServerB

Step 04) Repeat Step 03 until an rsync takes less than 1 minute

Step 05) service mysql stop on ServerA

Step 06) Perform one more rsync

Step 07) scp ServerA:/etc/my.cnf to ServerB:/etc/.

Step 08) service mysql start on ServerB

Step 09) service mysql start on ServerA (optional)

Give it a Try !!!


Find Source & Details of APL Heap Table Insert

I found an indirect way of addressing the issue posted in my question as I couldn't find any tool or script which could monitor all the inserts happening at database level, I analyzed the procedure(query) which runs 24 hours a day and is using maximum resource at Production database. To simulate, I executed that procedure manually in Test environment with all its parameters and kept sysmon running in the background for the same period as much it takes for procedure to execute. And I managed to gather close to same number of inserts in the test environment.

In order to prove that this is the only procedure which is doing this much high number of inserts every seconds, I also ran sysmon before and after the problematic procedure and captured that number of inserts too, which is in hundreds and not even in thousands.

Sysmon During procedure execution:

Transaction Profile
-------------------

  Transaction Summary             per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ----------
    Committed Xacts                   0.1           n/a           9     n/a

  Transaction Detail              per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ----------
  Inserts
    Fully Logged
      APL Heap Table              89575.5      895754.9     8061794     100.0 %
      APL Clustered Table             0.0           0.0           0       0.0 %
      Data Only Lock Table            0.0           0.0           0       0.0 %
      Fast Bulk Insert                0.0           0.0           0       0.0 %
      Fast Log Bulk Insert            0.0           0.0           0       0.0 %
    Minimally Logged
      APL Heap Table                  0.0           0.0           0       0.0 %
      APL Clustered Table             0.0           0.0           0       0.0 %
      Data Only Lock Table            0.0           0.0           0       0.0 %
  -------------------------  ------------  ------------  ----------  ----------
  Total Rows Inserted             89575.5      895754.9     8061794     100.0 %

Sysmon Before/After procedure execution(when its not running):

Transaction Profile
-------------------

  Transaction Summary             per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ----------
    Committed Xacts                   0.1           n/a          13     n/a

  Transaction Detail              per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ----------
  Inserts
    Fully Logged
      APL Heap Table                127.6         883.7       11488     100.0 %
      APL Clustered Table             0.0           0.0           0       0.0 %
      Data Only Lock Table            0.0           0.0           0       0.0 %
      Fast Bulk Insert                0.0           0.0           0       0.0 %
      Fast Log Bulk Insert            0.0           0.0           0       0.0 %
    Minimally Logged
      APL Heap Table                  0.0           0.0           0       0.0 %
      APL Clustered Table             0.0           0.0           0       0.0 %
      Data Only Lock Table            0.0           0.0           0       0.0 %
  -------------------------  ------------  ------------  ----------  ----------
  Total Rows Inserted               127.6         883.7       11488     100.0 %

This way, I managed to find the procedure which was doing 60k inserts every second. Also to add, these inserts were because of improper joins of table(containing huge records) and number of inserts were in the form of worktable as below:

Table: Worktable1 scan count 1, logical reads: (regular=88517 apf=0 total=88517), physical reads: (regular=52316 apf=0 total=52316), apf IOs used=0
Table: Worktable1 scan count 0, logical reads: (regular=8092124 apf=0 total=8092124), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable2 scan count 1, logical reads: (regular=11 apf=0 total=11), physical reads: (regular=2 apf=0 total=2), apf IOs used=0
Table: Worktable2 scan count 0, logical reads: (regular=10 apf=0 total=10), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable33 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable9 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable7 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable4 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable1 scan count 0, logical reads: (regular=8180641 apf=0 total=8180641), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable17 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable15 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable12 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable31 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable29 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable27 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable25 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable23 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable21 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable19 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable2 scan count 0, logical reads: (regular=21 apf=0 total=21), physical reads: (regular=0 apf=0 total=0), apf IOs used=0

I hope above will help others in case they face similar issue which I faced.


Try this:

SELECT
       t.user_id,
       t.audit_event
FROM
        (SELECT ua.user_id, ua.audit_event FROM user_audit ua, user_balance_transactions ubt WHERE
        (audit_event = 'RGL_TOURNAMENT_ENTRANCE_PAID' OR audit_event = 'RGL_TOURNAMENT_ENTRANCE_REFUNDED')
        AND ubt.regular_tournament_id='{$tournament_id}'
        AND ubt.audit_event_id=ua.id
        ORDER BY ua.create_timestamp DESC LIMIT 1) t
        WHERE t.audit_event != 'RGL_TOURNAMENT_ENTRANCE_REFUNDED'


high concurency issue with read-write on mysql table

Please provide SHOW CREATE TABLE; it is more descriptive than DESCRIBE.

Please show us samples the INSERTs and UPDATEs and SELECTs; we need to talk about the indexes.

Purging 'old' data can be done 'instantly' if you use PARTITION BY RANGE(TO_DAYS(datetime)) ...  See here .

Are most of the fields really NULLable?  Use NOT NULL where appropriate.


One table to rule them all, or thousand smaller?

Exactly NOT 10k! - it always bad idea, for each open table MySQL will spend memory, cpu time, disk resources and etc

Much better give all this resources back to MySQL and it will manage queries for big table. With proper queries and indexes - millions and billions of rows in production databases.

One single table or may be few depend from other logic.

Possible solutions:
1 Table for active events
Second for archived

Other split ways like event types - also possible. But generally - for open table MySQL can spend same or more time than for search single event from VERY big table


Tool for quickly backup and restore a table

BCP is the tool you're looking for.
 Here's some links that should put you in the right direction:

https://msdn.microsoft.com/en-us/library/ms162802.aspx

https://www.simple-talk.com/sql/database-administration/working-with-the-bcp-command-line-utility/


Alternative to cross server query in SSIS

you need two datasources and you have to use the lookup transformation to filter the Server1.


MySQL proxy to replication configuration

The new MySQL Router is one possible solution. Please take a look and let us know what you think!

I'd love to hear what features you most want in future versions.

http://mysqlhighavailability.com/mysql-router-on-labs-the-newest-member-of-the-mysql-family/

http://mysqlhighavailability.com/easy-load-balancing-and-high-availability-using-mysql-router/


Distribution Agent is failing in Transaction Replication

Know Your Limitations

The Transaction Log has data files on (hopefully by itself) one or more drives. How large is this?
How much of the Log data file is already full from uncommitted entries?
For every transaction, it must be logged. So large batches takes a lot of space.

Replication uses BCP files that use MSReplicated objects that insert, update, and delete rows one at a time. You can see these procedures in the target subscriber database under the names

[dbo].[sp_MSdel_<article>]
[dbo].[sp_MSins_<article>]
[dbo].[sp_MSupd_<article>]

So if you wish to see what is happening on the session level, you can see this during your testing.

  Fun Fact: This is also why Replication cannot have IDENTITY constraints allowed
  on the target table (you will see the NOT FOR REPLICATION ) because
  the batches are not necessarily in that the table has settled in.

Batches Represent a set of transactions that are committed at the same time.

Set the CommitBatchThreshold flag to a much lower number. Such as 100 and go lower if need be.

Your "fix" actually made some of this worse. From the BOL:

  -CommitBatchSize Is the number of transactions to be issued to the Subscriber before a COMMIT statement is issued. The default is 100.

  -CommitBatchThreshold Is the number of replication commands to be issued to the Subscriber before a COMMIT statement is issued. The
  default is 1000.

  replication distribution agent | Microsoft Docs

A Batch is a connected set of transactions, usually between a GO statement.

  A batch of SQL statements is a group of two or more SQL statements or
  a single SQL statement that has the same effect as a group of two or
  more SQL statements.

  Batches of SQL Statements | Microsoft Docs

Advanced Techniques: You can guesstimate the amount of space a typical row soaks up using the DATALENGTH() function, if you want to understand more about your LOG.

There is much more to replication, but I am sure this is enough to get you where you want to be. :)


To Change the Log file location of SQL Server 2014 in AlwaysOn

The Always on logs are written in the same location as the errorlog.

By moving the errorlog you can change this location. You will need to change the service startup parameter by opening the SQL Server configuration manager and changing the -e parameter and restarting the service.


"SHOW EVENTS" fails with "Error Code: 1545. Failed to open mysql.event"

Since mysql.event is empty you can use one of the following two options:
1- use --skip-events to skip events from backup.
2- try to recreate the table (firs you need to drop it):

DROP TABLE IF EXISTS `mysql`.`event`;

if you are using MySQL 5.7

CREATE TABLE `mysql`.`event` (
  `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `name` char(64) NOT NULL DEFAULT '',
  `body` longblob NOT NULL,
  `definer` char(93) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `execute_at` datetime DEFAULT NULL,
  `interval_value` int(11) DEFAULT NULL,
  `interval_field` enum('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_executed` datetime DEFAULT NULL,
  `starts` datetime DEFAULT NULL,
  `ends` datetime DEFAULT NULL,
  `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',
  `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
  `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `originator` int(10) unsigned NOT NULL,
  `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM',
  `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `body_utf8` longblob,
  PRIMARY KEY (`db`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Events';

MySQL 5.1

Compared to MySQL 5.7, the definer field is smaller.

CREATE TABLE `mysql`.`event` (
  `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `name` char(64) NOT NULL DEFAULT '',
  `body` longblob NOT NULL,
  `definer` char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `execute_at` datetime DEFAULT NULL,
  `interval_value` int(11) DEFAULT NULL,
  `interval_field` enum('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_executed` datetime DEFAULT NULL,
  `starts` datetime DEFAULT NULL,
  `ends` datetime DEFAULT NULL,
  `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',
  `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
  `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `originator` int(10) unsigned NOT NULL,
  `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM',
  `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `body_utf8` longblob,
  PRIMARY KEY (`db`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Events';

MariaDB 5.5

Compared to MySQL 5.1 the NOT_USED under sql_mode changes to IGNORE_BAD_TABLE_OPTIONS.

CREATE TABLE `mysql`.`event` (
  `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `name` char(64) NOT NULL DEFAULT '',
  `body` longblob NOT NULL,
  `definer` char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `execute_at` datetime DEFAULT NULL,
  `interval_value` int(11) DEFAULT NULL,
  `interval_field` enum('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_executed` datetime DEFAULT NULL,
  `starts` datetime DEFAULT NULL,
  `ends` datetime DEFAULT NULL,
  `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',
  `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
  `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `originator` int(10) unsigned NOT NULL,
  `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM',
  `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `body_utf8` longblob,
  PRIMARY KEY (`db`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Events';


Postgres heap vs SQL Server clustered index

PostgreSQL simply doesn't implement this feature.  There is no trick to not implementing it.  It isn't implemented in the straight forward, uncomplicated way of just not doing it.  To use one bit of jargon, all btree indexes in PostgreSQL are "secondary indexes", not "primary indexes".  Even the primary key's index is a "secondary index".

There are some cases where clustered keys (or index organized tables, as another product calls them) are important, and in those cases PostgreSQL fails to "supply exceptional performance".  You can argue about how common those cases are, of course, but they certainly do exist and it is unfortunate that PostgreSQL doesn't offer a solution for them.  There have proposals to address this, but I don't think any of those efforts are currently active.

In some cases, you can ameliorate the problem by using the CLUSTER command, or by implementing partitioning, or by using covering indexes, but none of these is entirely satisfactory as an alternative to real clustering.


Oracle - Connecting as a proxy user via connection string

I finally found out how to represent the proxy user login via my connection string.

Proxy User ID = MyUser
Proxy Password = MyUser password
User Id = SchemaName
User Password not set.

The dime finally dropped  after visiting https://www.devart.com/dotconnect/oracle/docs/ProxyUserConnection.html and running the SQL query below, after having logged in via Toad. Here it was clear to me, that I had misunderstood, that MyUser was in fact the proxy user...

SELECT SYS_CONTEXT('USERENV', 'PROXY_USER'), SYS_CONTEXT('USERENV', 'SESSION_USER') FROM dual


You can query sys.dm_exec_sessions on the last_request_end_time column to find open yet "sleeping" connections.

From this, you can find out the last SQL executed

SELECT session_id, TEXT
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS ST
WHERE c.session_id = <suspicious one from sys.dm_exec_sessions>


MYSQL matching one column to another in a different table via a FULLTEXT index

AGAINST() only accepts a string literal or a variable containing a string -- something the optimizer can deterministically resolve to a constant.

AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because that can differ for each row.
-- http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html

This rules out any construct I can think of where an ordinary query could be used to join tables based on a fulltext index match.
...however...
You can wrap the fulltext query in a stored function:
DELIMITER $$

DROP FUNCTION IF EXISTS `best_fulltext_match` $$
CREATE FUNCTION `best_fulltext_match` (search_string TEXT) RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN

  RETURN (SELECT id FROM title_table WHERE MATCH(title) AGAINST(search_string)
           ORDER BY MATCH(title) AGAINST (search_string) DESC LIMIT 1);

END $$

DELIMITER ;

Now...
SELECT best_fulltext_match('your title here');

...returns the id of the best match according to the fulltext index in title_table, and this function will accept a variable, no problem.
You could then use this to update your random_title_table.
UPDATE random_title
   SET title_id = best_fulltext_match(title);

The function is invoked once per row in random_title, and gives the fulltext search a static value to search with.
You could also use that same function when creating new entries in random_title.


For exposing it to public, we kept ID field. Its user friendly to see an ID vs UUID in URL. We replaced ID field from primary key and introduced UUID primary key. ID and UUID both exists for different reasons. For searchabilty, showing in URL, we still continue showing ID field.

This suggests something similar: https://stackoverflow.com/questions/52414414/best-practices-on-primary-key-auto-increment-and-uuid-in-rdbms-and-sql-databas

Some additional reading: https://tomharrisonjr.com/uuid-or-guid-as-primary-keys-be-careful-7b2aa3dcb439


Query Plans Change Performance Worse in SQL Server 2014

This is not an issue, it is by design and is considered a performance improvement.

If your queries do not run as expected you will need to address this in the code of your database/application.

You can force the old carnality estimator by using the trace flag 9481 at the server, session, or query level (using OPTION (QUERYTRACEON 9481)). This will force it no matter what the compatibility level of the database is.

There is also a ton of information that the CSS Team posted here.

A more detailed post on the Cardanility Estimator and changes around that process in SQL Server 2014, includes example.


Mixed data type case expression PostgresSQL

I could not find the definition of <@, but from your description, I assume that you  mean that ss.value is the same year as fiscal_year.f_year:

Case
   when c.student_age > 19
     and c.count_forms > 3
     and ss.value :: date BETWEEN (fiscal_year.f_year::varchar || '-01-01')::date
                              AND (fiscal_year.f_year::varchar || '-12-31')::date
   then 'Y'
   else 'N'
end as student_r_year

An alternative is to extract the year from ss.value:

Case
   when c.student_age > 19
     and c.count_forms > 3
     and extract(year from ss.value :: date) = fiscal_year.f_year
   then 'Y'
   else 'N'
end as student_r_year


Determine Oracle session client character set?

I am a little doubtful that this is exactly what you are looking for, but
host echo %nls_lang%;

ENGLISH_UNITED KINGDOM.WE8ISO8859P1

shows the client nls_lang environment variable on the client.
I don't think there will be a SQL query you can run to give the 'current' setting because AFAIK the server is not aware of what translation is done client-side, so any command to show the current setting will have to be native to the client - I used SQL Developer for the above command, but I assume it will work the same in SQL*Plus
--edit
from AskTom:

only the client knows their character set as well -- it is not available "in the
database"

and

the character set describes what is stored in database.
the client makes their desired translated to character know [sic] to the database via the NLS_LANG
settting.

If you were on 11.1+, you might have some joy with v$session_connect_info, because:

This information is pushed by OCI to the server ats login time.

But I discovered it would still depend on how you are connecting, eg from the JDBC Thin Driver you aren't using OCI and so the information isn't pushed


If you're using a current releases, then it will always have default services.  Check dba_services.  Unless there's an application constraint, you should connect with SERVICE_NAME.


Is it possible to backup and restore InnoDB tables on Oracle's MySQL 5.7.19 Server with Percona Xtrabackup?

After doing some research and re-reading the manuals I found the next:

I had done so called partial backups, that mean that the restore process is a little bit different - you need to specify --export option when doing the restore process (see Restoring Individual Tables)

If you are on Percona Server you can try to use innodb_expand_import option and avoid of some routine operations.
If you are on MySQL server - you must also to do the schema dumps. Then do the tricks with ALTER TABLE xxx DISCARD TABLESPACE and ALTER TABLE xxx IMPORT TABLESPACE for an every single table. (See here)

I did the restore without specifying the --export, so had the problems with those .frm's.
If you have stored procedures/etc - maybe you need to backup them as well. I have no chance to check that.


I fixed it. A little ridiculous but it was enough to change the 'current user' to 'all users' tab objects


how to query the plan cache to find how healthy it is?

Starters

Setting the SQL Server option optimize for ad hoc workloads is not really a solution to fix high recompilation values in the query plan cache.

It is however a good solution when your application is performing lots of ad hoc (hence the name) queries that run only once and which would otherwise pollute (waste) the query plan cache space. This can be, for example, an application that allows the users to select the columns of tables dynamically that they wish to see the results of.

Main Course

When SQL Server Database Engine (DBE) executes a query and this query has never been executed before, then the Database Engine has to determine how it will access the data. As soon as the DBE has determined the best way to access the data it will store this information in the Query Plan Cache (QPC), so that the users will benefit the next time the application performs the same query again (albeit, probably with slightly different values).

The SQL Server DBE will search the QPC each time the application requires a statement be run. If the DBE finds an adequate Query Plan in the QPC then it will select that Query Plan to retrieve the data. If however, the DBE is unable to determine an adequate Query Plan in the QPC (not found or timeout value reached for querying the QPC), then the DBE will create a new Query Plan. This is the resulting compiles/s you are observing.

Dessert

The root cause however can vary.

SQL Server may be under memory pressure and is unable to store enough compiled Query Plans in the QPC. The DBE will kick out old plans and insert the new ones.
 (Solution: Add more memory to the SQL Server instance)

The application is indeed generating a large amount of queries that have never before been executed and/or have slightly different values than the Query Plans stored in the QPC. (Solution: Remove complexity in application)

Answers to your questions

You can't. The query plans belong in the query plan cache. You can either clear the QPC or leave the DBE to do its best. (Selective deletion of query plans can be achieved with DBCC FREEPROCCACHE(, but I wouldn't recommend this.)
Determine what query plans are stored in the QPC and optimise the application and/or the memory settings for the SQL Server instance. (See script in Cigar Lounge and join with sys.dm_exec_sql_text and/or other DMV according to description for plan_handle column in sys.dm_exec_cached_plans documentation)

Cigar Lounge

The following query will list all cached plans stored in the QPC, and can be linked to other relevant DMVs to retrieve additional information:

SELECT plan_handle, ecp.memory_object_address AS CompiledPlan_MemoryObject,
    omo.memory_object_address, pages_allocated_count, type, page_size_in_bytes
FROM sys.dm_exec_cached_plans AS ecp
JOIN sys.dm_os_memory_objects AS omo
    ON ecp.memory_object_address = omo.memory_object_address
    OR ecp.memory_object_address = omo.parent_address
WHERE cacheobjtype = 'Compiled Plan';
GO

Reference: sys.dm_exec_cached_plans (Transact-SQL) (Microsoft Docs)

  Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

Reference Material

Why would I NOT use the SQL Server option “optimize for ad hoc workloads”? (DBA Stackexchange)
sys.dm_exec_query_plan (Transact-SQL) (Microsoft Docs)
sys.dm_exec_cached_plans (Transact-SQL) (Microsoft Docs)
Troubleshooting Plan Cache Issues (Microsoft MSDN)
sp_BlitzFirst® Result: High Compilations per Second (Brent Ozar)
Forcing Query Plans (Microsoft Technet)


Do signed certificates from public certificate authorities add any value for always encrypted?

Certs from trusted CAs (like VeriSign) are used when you need a certificate that must be able to prove its issuer, purpose, validity, etc... Certs for data encryption like Always Encrypted typically do not require such proof since your certs typically don't float beyond your org. I don't know of any use case where you would benefit from using a 3rd party CA cert in an AE deployment. Even when you have strict management/rotation policies, I can't think of any common policy that you can't implement using a corporate issued cert or even a self-signed cert (though the latter will have a bit more manual work).


If MySQL needs to create a temporary table it generates the result before sending it to a client.

Normally you want to avoid that and make Mysql to generate the result as it accesses the table(s) and as fast as the client reads it.