How to see unclosed PostgreSQL connection while using psycopg2?

Python answer

Given a psycopg2 connection object called conn, initialized by, e.g.:

import psycopg2

conn = psycopg2.connect(database="my_database",
                        user="username",
                        password=password,
                        host="localhost",
                        port="5432")

If I'd like to see if conn is closed or not, what I usually do in python 3 is;

conn.closed

If the .closed method returns 1, then the connection is closed.
If the .closed method returns 0, then the connection is still opened.

As stated in the documentation:

  closed

  Read-only integer attribute: 0 if the connection is open, nonzero if
  it is closed or broken.

https://www.psycopg.org/docs/connection.html

You can also see that by just typing in the name of your connection in a python console and watch at the closed key at the very end;

conn
Out[4]: <connection object at 0x7fe3d042e2b8; dsn: 'user=postgres
        password=xxx dbname=my_database host=localhost port=5432', closed: 0>

conn.close()

conn
Out[6]: <connection object at 0x7fe3d042e2b8; dsn: 'user=postgres
        password=xxx dbname=my_database host=localhost port=5432', closed: 1>

You can have more help on the connector methods by using:

help(conn)

Notice

It's really important to close a connection once you are done with your changes. Do not forget to commit() them before, of course, otherwise your changes would be lost as if you performed a rollback as stated in the documentation of psycopg2:

  close()

  Close the connection now (rather than whenever del is executed). The
  connection will be unusable from this point forward; an InterfaceError
  will be raised if any operation is attempted with the connection. The
  same applies to all cursor objects trying to use the connection. Note
  that closing a connection without committing the changes first will
  cause any pending change to be discarded as if a ROLLBACK was
  performed (unless a different isolation level has been selected: see
  set_isolation_level()).

Source: https://www.psycopg.org/docs/connection.html

psql and system tools answer

From psql, you can also list connections.

For example by using this command:

select pid as process_id,
       usename as username,
       datname as database_name,
       client_addr as client_address,
       client_hostname,
       client_port,
       application_name,
       backend_start,
       state,
       state_change
from pg_stat_activity;

or as you simply stated select * from pg_stat_activity;.
The interesting thing to notice here is the value of client_port.

The former command being modified after: https://dataedo.com/kb/query/postgresql/list-database-sessions

In any cases, the question and answer given here:    What exactly is Psycopg2?
may be helping understanding that it's barely impossible to differentiate a psycopg2 connection from a standard connection from outside Python using psql; a connection open with psycopg2 will look like any other opened connections.

But...

Now, if you open a psycopg2 connection and you carefully watch at the output of the aforementioned psql command, you will of course see an extra line while the connection is opened (compared to a closed psycopg2 connection).
In an example on my system, the line which corresponds to the connection open by psycogp2 looks like:

 process_id | username | database_name | client_address | client_hostname | client_port |     application_name     |         backend_start         | state  |         state_change
------------+----------+---------------+----------------+-----------------+-------------+--------------------------+-------------------------------+--------+-------------------------------
      12693 | postgres | my_database   | 127.0.0.1      |                 |       59218 |                          | 2020-03-22 10:58:12.143291+01 | idle   | 2020-03-22 10:58:12.149866+01

It opened a connection on client_port 59218.
As I said, from here, you cannot differentiate from any other connections.

Then, if I look at the output of ps -ef | grep "PID\|59218" (59218 being the client_port of the previous psql output) I have:

$ ps -ef | grep "PID\|59218"

UID        PID  PPID  C STIME TTY          TIME CMD
postgres 14663 31224  0 10:59 ?        00:00:00 postgres: 10/main: postgres my_database 127.0.0.1(59218) idle

Again, this looks exactly the same as any other connection, so I cannot know which application has opened this connection.

But you can further explore the outputs of netstat:

$ sudo netstat -ap | grep -i "Proto\|59218"
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 localhost:59218         localhost:postgresql    ESTABLISHED 859/python3
tcp        0      0 localhost:postgresql    localhost:59218         ESTABLISHED 14663/postgres: 10/
(...)

Where localhost:postgresql corresponds to localhost:5432 (this can be seen by activating/deactivating the -P flag of lsof.) Here you can also see the same postgres PID as the one in the ps output above.
So postgresql is ready to accept IO operations with python3 on the client port 59218 which was opened. This port closes when the psycopg2 connection is closed.

So you can try to figure out that client_port for the connection between python3 and postgresql, e.g. by:

sudo netstat -ap | grep -i "python3" | grep "postgres"

and then grep for its value in the output of either psql and ps.

Notice

I also tried to use the sockstat tool:

$ sudo sockstat | grep -i "PID\|5432\|59218"
USER     PROCESS              PID      PROTO  SOURCE ADDRESS            FOREIGN ADDRESS           STATE
username python3              859      tcp4   127.0.0.1:59136           127.0.0.1:5432            ESTABLISHED
postgres postgres             14663    tcp4   127.0.0.1:5376            127.0.0.1:59218           ESTABLISHED

Here, I unfortunately don't know what is this 59136 port used by Python itself in the source address, nor the 5376 port used by postgres as both doesn't match the default port specified by my pscopg2 connection which is 5432.
There is probably underlying protocols/connections being used in socket connections and I'm not an expert in networking to explain those.

I also found this thread: https://serverfault.com/questions/128284/how-to-see-active-connections-and-current-activity-in-postgresql-8-4

It also mentions an interesting tool called pg_top` but which doesn't completely answer your question.


What does Page Life Expectancy say about the instance?

Taken from MSDN:- https://msdn.microsoft.com/en-us/library/ms189628.aspx

Page life expectancy - Indicates the number of seconds a page will stay in the buffer pool without references.

SQL always looks for data pages in memory. If a data page is not in memory SQL will have to go to disk (perform a physical IO operation) in order to retrieve the data that it needs to fulfil a request. If your PLE counter is low that indicates that data pages in memory are regularly been overwritten with new pages coming from physical IO operations. Physical IO operations are expensive meaning that the performance of your SQL instance will be adversely affected. So you will want your PLE counter to be as high as possible.

Ignore any advice that you see online that mentions 300 as a good threshold for this counter

This threshold comes from the days when memory was limited (think 32-bit systems). Now we have 64-bit systems which can have TBs of RAM so this advice is very out of date.

First thing, have you limited SQL's memory? If so, how much available memory is left? Can the limit be increased?

Second thing I would be looking for on your servers is, are there any maintenance jobs running? Check for jobs performing index rebuilds, update stats or DBCC CHECKDB operations. These perform a large amount of reads and could be the reason for your PLE flat lining,

Next, as you're using SQL Server 2008 +, you can setup an Extended Event session to capture queries coming in that are performing a large amount of reads. Here's the code to do so:-

CREATE EVENT SESSION [QueriesWithHighLogicalReads] ON SERVER
ADD EVENT sqlserver.sql_batch_completed(
   ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username)
     WHERE ([logical_reads]>200000))
ADD TARGET package0.event_file(SET filename=N'C:\SQLServer\XEvents\QueriesWithHighLogicalReads.xel')
GO

This will capture all queries on your server that perform over 200000 logical reads. I don't know how much memory you have on each server so you may want to tweak that figure. Once this has been created you can start the session by running:-

ALTER EVENT SESSION [QueriesWithHighLogicalReads]
ON SERVER
STATE = START;
GO

And then query the session by running:-

WITH CTE_ExecutedSQLStatements AS
(SELECT
[XML Data],
[XML Data].value('(/event[@name=''sql_statement_completed'']/@timestamp)[1]','DATETIME')    AS [Time],
[XML Data].value('(/event/data[@name=''duration'']/value)[1]','int')                        AS [Duration],
[XML Data].value('(/event/data[@name=''cpu_time'']/value)[1]','int')                        AS [CPU],
[XML Data].value('(/event/data[@name=''logical_reads'']/value)[1]','int')                   AS [logical_reads],
[XML Data].value('(/event/data[@name=''physical_reads'']/value)[1]','int')                  AS [physical_reads],
[XML Data].value('(/event/action[@name=''sql_text'']/value)[1]','varchar(max)')             AS [SQL Statement]
FROM
    (SELECT
    OBJECT_NAME              AS [Event],
    CONVERT(XML, event_data) AS [XML Data]
FROM
    sys.fn_xe_file_target_read_file
('C:\SQLServer\XEvents\QueriesWithHighLogicalReads*.xel',NULL,NULL,NULL)) as v)

SELECT
[SQL Statement]     AS [SQL Statement],
SUM(Duration)       AS [Total Duration],
SUM(CPU)            AS [Total CPU],
SUM(Logical_Reads)  AS [Total Logical Reads],
SUM(Physical_Reads) AS [Total Physical Reads]
FROM
CTE_ExecutedSQLStatements
GROUP BY
[SQL Statement]
ORDER BY
[Total Logical Reads] DESC
GO

Be careful when running this! The file can grow quite large in size so test it out on a development instance first. You can set the max. size of the file but I haven't included that here. Here's the MSDN link for Extended Events:- https://msdn.microsoft.com/en-us/library/hh213147.aspx

Monitor this session routinely and hopefully it should pick up any queries coming in that are flat lining your PLE.

Further reading -

MSDN blog on PLE - http://blogs.msdn.com/b/mcsukbi/archive/2013/04/12/sql-server-page-life-expectancy.aspx

Video on setting up Extended Events - https://dbafromthecold.wordpress.com/2014/12/05/video-identifying-large-queries-using-extended-events/
(It's from my own blog so sorry about the shameless self promotion)


What you are asking for can be done by getting the list of all tables from the database. However, you should use the INFORMATION_SCHEMA database. In your particular case, you can use the INFORMATION_SCHEMA.TABLES table.

TABLES IN DESCENDING TABLE_NAME ORDER (Database mydb)

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Choose Database
#
DB_TO_DUMP=mydb
#
# Choose Ordering
# Descending Order By Table Name
#
ORDER_BY="table_name DESC"
#
#
#
SQL="SET group_concat_max_len = 1048576;"
SQL="${SQL} SELECT GROUP_CONCAT(table_name ORDER BY ${ORDER_BY} SEPARATOR ' ')"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB_TO_DUMP}'"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
MYSQLDUMP_OPTIONS="--single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} -B ${DBLIST} > ${DB_TO_DUMP}_Tables_Desc.sql

TABLES IN DESCENDING SIZE ORDER (Database mydb)

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Choose Database
#
DB_TO_DUMP=mydb
#
# Choose Ordering
# Descending Order By Table Size
#
ORDER_BY="(data_length+index_length) DESC"
#
#
#
SQL="SET group_concat_max_len = 1048576;"
SQL="${SQL} SELECT GROUP_CONCAT(table_name ORDER BY ${ORDER_BY} SEPARATOR ' ')"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB_TO_DUMP}'"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
MYSQLDUMP_OPTIONS="--single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} -B ${DBLIST} > ${DB_TO_DUMP}_Tables_Desc.sql

GIVE IT A TRY !!!

I have suggested techniques like this before

Sep 09, 2011 : How can I optimize a mysqldump of a large database?
Dec 16, 2011 : How do you mysqldump specific table(s)?
Aug 23, 2012 : Creating dump file with use command

UPDATE 2014-12-20 21:09 EST

Your last comment

  It just seems inflexible to me that mysqldump doesn't offer any kind of table ordering. Reason is I have a table that I want it to be restored last, but in the dump order its among the first couple of tables. It's app dependence that this specific table is not present until the whole dump is fully restored.

TABLES IN ORDER BY tb1,tb2,tb3 BEING LAST (Database mydb)

Let's say you want tables tb1,tb2,tb3 to be last

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Choose Database
#
DB_TO_DUMP=mydb
#
# Order By List of Tables to Make Last
#
LAST_TABLES=tb1,tb2,tb3
ORDER_BY="IF(LOCATE(CONCAT(',',table_name,','),',${LAST_TABLES},')>0,1,0)"
#
#
#
SQL="SET group_concat_max_len = 1048576;"
SQL="${SQL} SELECT GROUP_CONCAT(table_name ORDER BY ${ORDER_BY} SEPARATOR ' ')"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB_TO_DUMP}'"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
MYSQLDUMP_OPTIONS="--single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} -B ${DBLIST} > ${DB_TO_DUMP}_Tables_Desc.sql

UPDATE 2014-12-21 23:02 EST

In your comment you said mysqldump doesn't offer any kind of ordering unless you specify table names as arguments, and there are not going to. Why ? Please keep in mind that the question MySQL dump - how to order tables? is very broad. There are many ways to ask for tables from a mysqldump.

In my old post How can I monitor the progress of an import of a large .sql file? (May 02, 2012) : When you execute a mysqldump of a single database, all tables are dumped in alphabetical order. Naturally, the reload of the mysqldump into a database would also be in alphabetical order.

The mysqldump client program sees a list of options and names names

  Syntax

  There are in general three ways to use mysqldump—in order to
  dump a set of one or more tables, a set of one or more complete
  databases, or an entire MySQL server—as shown here:

shell> mysqldump [options] db_name [tbl_name ...]
shell> mysqldump [options] --databases db_name ...
shell> mysqldump [options] --all-databases

Given the first example of syntax, MySQL (now Oracle) expect us developers and DBAs to give the database names and table names.

Since the mysqldump client program has the --databases (-B) option, we are given the opportunity to dump the databases in a specific order or with a specific list.

The point I am making is that it is left up to us to order the tables when running mysqldump. Since MySQL (now Oracle) uses storage engines that use plug-in frameworks, MySQL was not going to make mysqldump compliant to ordering anything for us. Imagine trying to use a storage engine whose physical files you cannot get access to. That leaves no choices but to use the INFORMATION_SCHEMA as I just demonstrated.

Unless someone downloads the source code for mysqldump and creates a version of mysqldump that can do custom ordering, this is all that mysqldump client program gives us to work with. We just have to do the ordering.


Moving a SQL Server DB from Windows to Linux fails

In the first release of SQL Server on Linux, I saw that SQL Server translates paths to related terms in windows, for example, root '/' will be C:.
so if this logic exists, you may try to make it something like:

  FROM  DISK = N'C:\var\opt\mssql\backup_file_name.bak' WITH  FILE = 1

but I think it's different now as mentioned here
maybe you should first check the version of your SQL Server on Linux and then check the steps on that article.


SQL Server spatial filter with additional conditions performance issue

The answer is yes it is possible to get performance with filters over multiple columns including spatial columns.

I've tried to replicate your tables and closely as I can to your brief description using RAND to create prices, location points and link them to events.  But I can't duplicate you performance problem.  Have I got the basic table details right?

--  Event table
SELECT TOP 30000000
    N EventID,
    RAND(CAST(NEWID() AS VARBINARY)) * 10000 + 5000 Price
    into Event
FROM Tally;

ALTER TABLE Event ALTER COLUMN EventID INT NOT NULL;
GO
ALTER TABLE Event ADD CONSTRAINT Event_PK PRIMARY KEY (EventID);
GO
CREATE INDEX Event_Price_IDX ON Event(Price);
GO

-- Location Table
SELECT TOP 35000000
    N LocationID,
    CEILING(RAND(CAST(NEWID() AS VARBINARY)) * 30000000) EventID,
    Geometry::Point(RAND(CAST(NEWID() AS VARBINARY)) * 710000 + 40000, RAND(CAST(NEWID() AS VARBINARY)) * 710000 + 40000, 0) Location
    into Location
FROM Tally;

Running the 3 versions of your query with hopefully sensible parameters

-- Test Query
DECLARE @price FLOAT = 13000.00 -- ~20% of records
-- 10 sq km query window
DECLARE @BoundingBox Geometry = Geometry::STGeomFromText('POLYGON(( 300000 300000, 310000 300000, 310000 310000, 300000 310000, 300000 300000))',0)

SELECT TOP 2000 [E].*
FROM [dbo].[Event] AS [E]
INNER JOIN [dbo].[Location] AS [L] ON [E].[EventId] = [L].[EventId]
WHERE
    --[E].[Price] >= @Price
    --AND
    @BoundingBox.STContains([L].[Location]) = 1

SELECT TOP 2000 [E].*
FROM [dbo].[Event] AS [E]
INNER JOIN [dbo].[Location] AS [L] ON [E].[EventId] = [L].[EventId]
WHERE
    [E].[Price] >= @Price
    --AND
    --@BoundingBox.STContains([L].[Location]) = 1

SELECT TOP 2000 [E].*
FROM [dbo].[Event] AS [E]
INNER JOIN [dbo].[Location] AS [L] ON [E].[EventId] = [L].[EventId]
WHERE
    [E].[Price] >= @Price
    AND
    @BoundingBox.STContains([L].[Location]) = 1

I get 93ms, 4ms and 93ms respectively, removing the TOP 2000 from the queries causes the price filtered query to blow out to a minute or so.

Are you able to give more details (execution plans, DDL, etc) to help us replicate your issue.  My initial thought was that the TOP 2000 was hiding the underlying problem.  I think that may still be the case.


Starting mongod daemon with a config file for a shard

So, you created mongod.service file, did you remember tell to systemd that you are forking that pid to background? If not, systemd will kill that process soon after start.

Then there is that what @stennie wrote, if you start even once mongod as root user, some of the files are created as root:root and after that, nobody else (like user mongod) can not start mongod-process. So, what are those files and directories, which ownership must be set to mongod (or what ever user you use to start mongod-process)

mongod.log /var/log/mongod/-directory (or what you wrote at config file)
/var/run/mongodb/mongod.pid (or what ever is set at config-file)
directory (and files) where your data is.

Log file is important, because after mongod-process can write to it, it will tell what went wrong if something went wrong...


SQL Server 2000 : Upgrade from Personal Edition to Enterprise Edition

I would like to post this as answer what is point in upgrading to SQL server 2000 which is outdated completely without any support. Even it is not good for testing purpose. Its like using typewriter in era or super computers.

Dont uninstall personal edition first. Install enterprise move databses by backup restore method. If you restore master msdb databases you would not need to move logins and jobs.

Please refer to below link

https://www.barracudaware.com/files/docs/9.00/help/restoring_microsoft_sql_server_2000_master_database.html


Display two digits after decimal point in SQL from average value

Switch your AVG and CAST. Instead of

SELECT AVG( cast(`page_rate` as decimal(10,2)))

Use

SELECT CAST(AVG(`page_rate`) as decimal(10,2))

Then you are casting your average with 2 decimal places.


Fastest way to check if record is related to a particular user in postgres

You can use PERFORM 1 test with a control statement using FOUND special variable. Your query will get like this;

PERFORM 1
    FROM ks.shares
    WHERE
        id = Pshareid AND userid = Puserid
    LIMIT 1;

IF FOUND THEN
  -- Cannot like your own share
ELSE
    --INSERT the new record
END IF;

Referenced sections;

Executing a Command With No Result

Available Diagnostics Items (special variable named FOUND)


What is the significance of the principal_id in sys.schemas?

It should be the default "owner" of all objects in the schema. Schema-bound objects have a principal_id column that is NULL by default, in which case it uses the principal_id of the Schema that the object is in.

The object owner is used for ownership chaining, and most likely also when determining if rights exist to perform operations that can be done by the object owner or a User in a the db_owner Database Role, such as TRUNCATE TABLE, SET IDENTITY_INSERT, etc.

The principal_id in sys.schemas can be any User if changed via ALTER AUTHORIZATION.

If using Schemas for logical separation of objects more than security separation, then it would make sense to use the same principal_id across multiple Schemas. For example, executing the following in the AdventureWorks2012 Database:

SELECT * FROM sys.schemas;

shows that the following Schemas are all owned by "dbo":

dbo
HumanResources
Person
Production
Purchasing
Sales

Having the same "owner" across Sales and Purchasing (and the others) allows ownership chaining to imply permissions of referenced objects within top-level objects. Meaning, if you execute a Stored Procedure in the Sales Schema, and it selects from a View in the Purchasing Schema, then permissions won't be re-checked and everything just works. But, if you then specify the principal_id of the View in the Purchasing Schema such that the column is no longer NULL (and not the same as the "dbo" User) then it will re-check permissions to see if the User executing the Stored Procedure has SELECT permissions on that View.


Need to find a pattern in SQL Data base and then write a query to delete the duplicate "NULL" Value

Assuming your table name is tab1, this should work.

delete tab1
from tab1 t
join (select practice from tab1
        group by practice
        having count(1)>1) sub
    on t.practice=sub.practice
where q1 is null
    and q2 is null
    and q3 is null
    and q4 is null
    and ytd is null
    and avg is null


You have tmp_table_size set to 16G and max_heap_table_size set to 8G. Way Too Big !!!
If you have temp tables that exceed 8G, you can do one of three(3) things
SUGGESTION #1
Use smaller values. Set tmp_table_size and max_heap_table_size to 16K. That way, the temp table will go to disk faster. BTW Yes, they should be the same value. If you don't set these small, this is what happens:

Temp Table Grows to 8G in memory
mysqld suspends the query processing
mysqld offloads temp table to disk (all 8G
mysqld resumes filling the temp table
mysqld continue query processing

CAVEAT: You may need to map disk-based temp tables to a separate disk. You can use tmpdir to point all such temp tables to that designated volume. The reason I am giving you a head-up on this ?
According to MySQL 5.0 Certification Study Guide

Page 408,409 Section 29.2 Bulletpoint 11 says:

If you run out of disk space while adding rows to a MyISAM table, no error occurs. The server suspends the operation until space becomes available, and then completes the operation.

Thus, you need tons of room for temp tables on disk. I have mentioned this before (See my old post "Site Offline" MySQL server failing to start and stop)
SUGGESTION #2
Tune other per-connection settings. If you setting join_buffer_size and sort_buffer_size, this can change the execution plan of the query for the better.
SUGGESTION #3
Tune your query. If you can change the query to take advantage of indexes or create indexes that will support the query you have, you can generate temp tables faster and, if possible, smaller.
UPDATE 2014-12-27 11:05 EST
You originally asked

Should we perhaps adjust the sizes of tmp_table_size and max_heap_table_size to have the same value?

Not necessarily. When used together, max_heap_table_size puts a cap on how large and in-memory temp table can be before offloading to disk. The only exception is when a table is create using CREATE TABLE ... ENGINE=MEMORY; For more information on this, please read MySQL Documentation on How MySQL Uses Internal Temporary Tables


SQL Server recovery with LVM snapshot as base

Going from this: "I can at this point restore a full backup and recover with the logs, but I wondered if it was possible to skip the full backup/restore step and use the snapshot as a base?".

In order to restore the log backups the database needs to be put in a 'restoring' state. Now I'm not actually sure if you can put the online database in that state without actually making a real restore of a full backup. The normal way would be to restore the db from full backup using the NORECOVERY option (expects log backups) and then restore the following log backups.

I'm not sure if there's a way to circumvent it.

PS: Jack, you don't need to mark this as the answer yet, let's let the wiser SQL Server admins here speak their own experience, maybe I'm just plain dumb and missing something fairly obvious :-).


How do I fix the Code Page in SSIS Lookup Transformation to be 65001?

With the disclaimer that I'm a "dumb American" who doesn't deal with non-English data but did work with a friend recently on using bulk import with UTF-8 data, here's what I see.
I have a pipe separated value file that looks like this
level|name
7|"Ovasino Poste de Santé"

Notepad++ indicates I have saved it as UTF-8.
I created two flat file connection managers in SSIS: Codepage65001STR and Codepage65001WSTR. They both use a Code Page of 65001 (UTF-8)

In the advanced tab for the STR variant, I left the data type as DT_STR

In the advanced tab for WSTR variant, I changed the data type to DT_WSTR

I also created a table and loaded it with the same data
DROP TABLE IF EXISTS dbo.dba_286478;
CREATE TABLE dbo.dba_286478
(
level int NOT NULL
,   name varchar(75) COLLATE Latin1_General_100_CI_AS_SC_UTF8
)
INSERT INTO dbo.dba_286478
(
    level
,   name
)
VALUES
(
    7 -- level - int
,   'Ovasino Poste de Santé' -- name - varchar(75)
);

DROP TABLE IF EXISTS dbo.dba_286478;
CREATE TABLE dbo.dba_286478
(
    level int NOT NULL
,   name varchar(75) COLLATE Latin1_General_100_CI_AS_SC_UTF8
);

I then created a data flow task with a Flat File Source using the different Flat File Connection Managers and added data viewers between them and an empty derived column (so I had an anchor point for the data viewer).
I did the same thing with an OLE DB Source pointing at my table as well as a custom query of
SELECT
    T.level
,   CAST(T.name AS varchar(75)) AS name
FROM
    dbo.dba_286478 AS T;

as well as explicitly defining the collation as it makes no different in SSIS
,   CAST(T.name COLLATE Latin1_General_100_CI_AS_SC_UTF8 AS varchar(75)) AS name

The results all show the same, the final word is an accented Sante. If the UTF-8 hadn't happened, it'd show as SantÃ©

At this point, it doesn't matter whether we DT_STR or DT_WSTR in our flat file source column definition, the component understands UTF-8 and UTF-16.
Properties, metadata of each. The Codepage 65001 STR looks as expected. code page of 65001 and data type DT_STR

Unicode, DT_WSTR looks good

The OLE components however, they're a different animal. The component is returning a metadata of DT_WSTR (full Unicode/UTF-16) regardless of whether we do an explicit cast to DT_STR, optionally specifying collation, or let the natural metadata flow through.
Either way, it doesn't detect the code page/collation stuff and just says Nope, you're Unicode

So, when we get to trying to use a Lookup task with an OLE DB connection manager, we can expect and receive the same inability to delineate between UTF-8 string/varchar and UTF-16/nvarchar

The error would indicate and that's true, DT_STR can't match DT_WSTR

Cannot map the input column, 'name', to the lookup column, 'name', because the data types do not match.

So what do I do?
You must have type alignment to make the lookup component work which means the source data needs to be of type DT_WSTR. You can either bring the data in from the Flat File as Unicode or leave it as string with code page 65001. If you go the latter route, then you need to make a copy, Derived Column or Data Conversion work, of that column and use it in the Lookup component.
If you're pulling text out of the lookup component, that's now in your pipeline as Unicode so you probably want to then convert that to a string type with code page. Again, Derived Column or Data Conversion will be used.

SSIS OLE components don't understand UTF8
We saw with the source and lookup component that SSIS is going to treat the UTF-8 strings as UTF-16 but I assumed it would handle storing to the table just fine. Not so much.
My server collation is Latin1_General_100_CI_AI_SC_UTF8 and while I switched accent sensitivities between server and table definition of dbo.dba_286478, it doesn't matter in this case as it's UTF-8 all the way down.
For my Flat File Source, I use the STR based file which has the metadata shown above with the yellow highlighting. The Codepage 65001 for data type DT_STR is what we want.
I added an OLE DB Destination and pointed it at my table which again has the "name" column defined as UTF-8
name varchar(75) COLLATE Latin1_General_100_CI_AS_SC_UTF8

Check this error!

Validation error. Data Flow Task OLE DB Destination [138]: The column "name" cannot be processed because more than one code page (65001 and 1252) are specified for it.

We only have code page 65001 at play in this data flow and yet, something in SSIS space is inferring/defaulting to a 1252 code page during validation.
Making it work
The componentry in a data flow task was built with OLE DB connections in mind. That's why the Lookup task supported OLE DB Connections for 2005, 2008 and maybe 2008R2? Long time ago now, I know but the Cache Connection Manager (aka anything else) option was added in later iterations because of the need to use something besides OLE connection managers especially given the push then was to deprecate the OLE driver.
An ADO.NET Connection Manager does slightly better than OLE in this case and that's likely what you're going to have to use to work with UTF8 data in an SSIS package. It will be implicitly converting to UTF-16 when it presents to the table and then SQL Server is going to snap it back into UTF-8 space (best I can tell).
For reference, bringing UTF-8 data into the pipeline with ADO Source will still be flagged as DT_WSTR/UTF-16/unicode.

But you can land DT_STR code page 65001 to an ADO.NET Destination without a code page mismatch error like I'm seeing for OLE DB Destination.

The data from the database is going to appear as DT_WSTR regardless of how you bring it into the pipeline. That means you can define and OLE and an ADO connection manager to use the Lookup component as is.
Or you can add a precursor data flow step to populate the Cache Connection Manager and only have an ADO.NET connection manager. Were you to go that route, convert the DT_WSTR data to DT_STR with codepage 65001 and store that data into the cache.
DFT - Populate Cache -> DFT - Load data

DFT - Populate Cache
ADO.NET Source -> Data Conversion -> Cache Connection Manager

DFT - Load Data
Flat File Source -> Lookup Component -> ADO.NET Destination


Optimising MySQL count() with INNER JOIN (550k rows)

First I would try index on establishments (latest_inspection_id, deleted_at).
Also it may has a sense to eliminate join with ratings table using condition directly on inspections.rating_id instead. I mean AND inspections.rating_id IN (..list of corresponding ids..)


Recommended innodb_file_per_table value for AWS MySQL RDS database

Complex answer

All tables are "small"?  Use =0.
All tables are "large"?  Use =1.
A mixture?  You could do some one way, some the other.  Such as using file_per_table=1 for tables over 10MB.

Simple answer

OK, that is more complexity than you would like.  In that case, let's go with the "short answer":  Use whatever AWS has it set to.  It won't matter much.

In particular, none of the metrics you quoted raise any alarms saying that you must go one way or the other.


MySQL thinks Master & Slave have same server-id, but they don't

The server-id is stored in the /var/lib/mysql/auto.cnf in addition to the standard my.cnf config file.

When cloning the data directory from master to slave, you need to delete the auto.cnf file in the data directory (i.e. /var/lib/mysql) before starting the slave.


Is there a compact way to use a NOT IN (1,2,3) syntax when setting a variable?

There is no boolean datatype in SQL Server which is why select @ignoretype = (@record_type <> 2) doesn't work.

IF @record_type NOT IN ( 2, 3, 7, 18, 19, 20, 21, 12, 13, 22 )
  SET @ignoretype=1
ELSE
  SET @ignoretype=0

works fine. Or you could also do

SET @ignoretype = CASE
                    WHEN @record_type NOT IN ( 2, 3, 7, 18, 19, 20, 21, 12, 13, 22 )
                    THEN 1
                    ELSE 0
                  END

Or (if you are on 2012+)

SET @ignoretype = IIF(@record_type NOT IN ( 2, 3, 7, 18, 19, 20, 21, 12, 13, 22 ) ,1,0)


mysql 8 datetime default throwing and error when forward engineering

As @akina pointed out in the comments:

Default value datatype and column datatype MUST MATCH STRICTLY ! If you create DATETIME(6) column then you must use CURRENT_TIMESTAMP(6) default value. Test - add (6) to default value for created_at only - and the error message will point you to updated_at (the first syntax error detected)


PostgreSQL not using varchar index when ordering by primary key

PostgreSQL thinks that it can avoid the expensive sort by using an index that returns the rows in sorted order and filter out the rows that do not match the conditions. This strategy turns out to be bad, either because of mis-estimates or an adversarial distribution.

Sorting by primary key is in no way faster than sorting by other indexed criteria. And in this case you are better off not having an index on the ORDER BY expression.

You should at least upgrade to the latest 9.4 bugfix release, but better to a recent version, because it is dangerous to use old and unsupported versions. They can contain bugs that may eat your data. Just because no such bug has manifested so far is no guarantee that it won't in the future.


Dump of a mysql table on import replaced existing records

The mysqldump, by default, will drop the table. You should specified the --no-create-info option like this:

mysqldump -u... -p... --no-create-info --skip-extended-insert mydb t1 > mydb_table.sql

That way, you only have inserts to deal with. Using --skip-extended-insert will insert one row at a time. This help deal with duplicate issues, but you will have import like this:

mysql -u...-p... --force mydb < mydb_tables.sql

The --force option is for the sole purpose of continuing INSERTs in the event a duplicate key is encountered. In that instance, the offending INSERT's error is ignored and on to the next INSERT.


is restart necessary after a config change in mongodb?

As per Jira ticket and MongoDB documentation, it does not mention of requiring a restart.

ISSUE SUMMARY

  Client cursors that are no longer used may not be closed by the
  server. These cursors continue to consume resources inside mongod. By
  default, inactive cursors expire after 10 minutes, this option makes
  it possible to configure this timeout.

RESOLUTION DETAILS

  By specifing the cursorTimeoutMillis option, administrators can
  configure mongod or mongos to automatically remove idle client cursors
  after a specified interval. The timeout applies to all cursors
  maintained on a mongod or mongos, may be specified when starting the
  mongod or mongos and may be modified at any time using the
  setParameter command. Consider the following examples.

cursorTimeoutMillis

  New in version 3.0.2.

  Default: 600000 (i.e. 10 minutes)

  Available for both mongod and mongos.

  Sets the expiration threshold in milliseconds for idle cursors before
  MongoDB removes them; i.e. MongoDB removes cursors that have been idle
  for the specified cursorTimeoutMillis.

  For example, the following sets the cursorTimeoutMillis to 300000
  milliseconds (i.e. 5 minutes).

But some users saw the value did not take effect till restart.

https://groups.google.com/forum/#!msg/mongodb-user/_nYxRY0iDr0/HeRpqRIIBwAJ


Use locate() to get the position of . and then left() to get the text left of it.

SELECT *
       FROM table1 t11
            INNER JOIN table1 t12
                       ON left(t12.id, locate('.', t12.id) - 1) = left(t11.id, locate('.', t11.id) - 1);


Query to convert the count to million or thousands

This type of formatting is generally best done in your application if possible.

The problem is that the case expression returns a result based on the highest datatype precedence of any branch.

So you would need to cast the final COUNT branch of your CASE to VARCHAR too as int has higher precedence than varchar.

Also you should probably add year into your order by except if you actually want to order Jan 2014 and Jan 2015 together followed by Feb and so on.

Though I might well be minded to take that concatenated string out of the GROUP BY too and rewrite it (Along the way fixing a couple of other issues with integer division and inconsistent boundary conditions) as.

WITH T(mmmyyyy, FORMATTED_COUNT, Yr, Mnth)
     AS (SELECT DATENAME(MONTH, MIN(CREATEDTS)) + ', ' + DATENAME(YEAR, MIN(CREATEDTS)) AS Month,
                CASE
                  WHEN COUNT(*) BETWEEN 1000 AND 999999
                    THEN ( CONVERT (VARCHAR(10), ( CAST(ROUND(COUNT(*) / 1000.0, 1) AS NUMERIC(4, 1)) )) ) + 'k'
                  WHEN COUNT(*) >= 1000000
                    THEN ( CONVERT (VARCHAR(10), ( CAST(ROUND(COUNT(*) / 1000000.0, 2) AS NUMERIC(6, 2)) )) ) + 'm'
                  ELSE CONVERT (VARCHAR(10), COUNT(*))
                END,
                YEAR(CREATEDTS),
                MONTH(CREATEDTS)
         FROM   USAGEDATA
         GROUP  BY YEAR(CREATEDTS),
                   MONTH(CREATEDTS))
SELECT mmmyyyy,
       FORMATTED_COUNT
FROM   T
ORDER  BY Yr,
          Mnth;


Error Updating Record on Table with Partition on a Read-Only Filegroup

Ordinarily, SQL Server only checks for a read-only filegroup just before attempting to write to the partition. No error is thrown when reading from a read-only filegroup, as you would expect.
SQL Server can therefore touch any rows it likes, so long as only rows located on read-write filegroups qualify for the update. To put it another way: SQL Server can fully scan all partitions (e.g. using a Clustered Index Scan) looking for rows to update, so long as the only rows that arrive at the Clustered Index Update are on read-write partitions.
In this scenario, there are two separate storage engine rowsets involved: one for the read, and one for the update. The reading rowset will not throw an error if it encounters a read-only partition because it is only reading. The writing rowset will throw an error, because it is configured to perform changes.
The catch
In execution plan shapes where there is no blocking operator between reads and writes to the base table, SQL Server may apply an optimization ("Rowset Sharing"), whereby the two operators share a single storage engine rowset.
In this situation, reading from a read-only partition will throw the error reported in the question. The single rowset is configured for both read and write - touching a read-only partition results in the error.
Adding a key from the unique clustered index to the set clause means a split, sort, collapse combination is required to avoid transient key violations. As a side-effect, the sort (a blocking operator) prevents the rowset sharing optimization from being applied.
For more details, see my article Changes to a Writable Partition May Fail Unexpectedly


how to make a particular field into seperate columns?

This query gives you the answer you want including "NULL" values if for a given No one of the Usernames is null.

SELECT  t1.NO No,
        t1.VALUE AS PORT,
        t2.VALUE AS Vip1
FROM table1 t1 FULL OUTER JOIN table1 t2
     ON t1.NO = t2.NO
WHERE t1.USERNAME = 'Port' AND t2.USERNAME = 'Vip1';

On the table:

SQL> select * from table1 where username = 'Port';

NO USERNAME             VALUE
-- -------------------- ----------------------------------------
01 Port                 Funds
02 Port                 Bank

I changed the column "USER" to "USERNAME", because "USER" is a reserved word in oracle.

This gives the output (in oracle 11g):

No PORT VIP1
-----------------
01 Funds Systems
02 Bank Authority

Another way to achieve this is a PIVOT table. A caveat is, that PIVOT tables only work with aggregate functions. For example, if you had two different entries for "Port" for No = 01, this would only return one of them.

SELECT * FROM
    (SELECT No, Username, VALUE from table1)
    PIVOT (MAX(Value)
           FOR (Username)
           IN ('Vip1', 'Port'))
ORDER BY No;

A slight variation of this gives you a pivot table for unspecified Usernames.
Unfortunately, oracle11g only provides XML output for this.

 SET LONG 10000 <-- to see the output on the terminal
 SELECT * FROM (
    SELECT No, Username, VALUE FROM table1)
    PIVOT XML (MAX(Value)
               FOR (Username)
               IN (SELECT DISTINCT Username FROM table1));

For more information on oracle pivot tables see http://www.oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php


Use t1."MaxID" literally the same as in with or drop quotes in with.


Get the first and last names of all employee who entered the project at the same time as at least one other employee

You are missing conditions that say that the employee is working on the project in w1, and that the project in w2 is the same as the project in w1 but that the employee in w2 is not the same as the employee in w1.

The reason you get all rows is because the where condition always evaluates to true, for all employees. That is because the sub query simply asks for at least one row that has the same date in both w1 and w2. Consider that WorksOn_m has only one row:

  WorksOn_m
  -------------- EnterDate 12/03/2014

Is there a record in both w1 and w2 with the date 12/03/2014? Yes, because they're the same table. Are you applying any other conditions in the WHERE clause? No. So you'll get all employees, because you're basically saying "Give me all employees WHERE 1=1".


Help needed in creating a SQL statement containing four joins

I have done it myself.

select
    userID
from
    UserCities
where
    CityID IN (select CityID
               from school sc, student st
               where sc.SchoolID = st.SchoolID and StudentID = 382)
    and userID IN (select userID
                   from users
                   where tagNumber = 4521) ;


Convert Login from Windows Authentication to SQL Server authentication?

I think your best bet would be to script out the server level permissions granted to the Windows Login and save the SID, delete the Windows Login and recreate it as a SQL Login with the same SID, and finally grant the permissions back on the Login.

This way the login will have it's server level permissions recreated and retains it's database level permissions since the login will match the database user via SID.

    CREATE LOGIN [LoginName] WITH PASSWORD=N'PASSWORD'
            ,DEFAULT_DATABASE=[master]
            ,CHECK_EXPIRATION=OFF
            ,CHECK_POLICY=OFF
            ,SID=[SID]


First.. You are not adding another shard, just new replica set member. Error means that this (primary) could not connect that "node75" at port 27017 to check that it is live.

Problem is probably that, that this name node75 cannot be resolved. There is no IP address for that name.

Try to add line to /etc/resolv.conf

xxx.xxx.xxx.xxx  node75

where that xxx is IP address of that machine.

Or use IP-address at that rs.add("xxx.xxx.xxx.xxx:27017")

Of course you need to check that firewall (at node75) is open for that port 27017.


This will be a lot faster, but has flaws:

SELECT  id,
      ( SELECT  count(*)
            FROM  tickets
            WHERE  order_id = o.id
      ) as attached_tickets
    FROM  orders AS o
    LIMIT  1;

It will need INDEX(order_id) on tickets.

Which order.id do you want?  There is no ORDER BY to say which one LIMIT 1 will pick.
This may not generalize well.  What is your real query?


Unable to start Oracle Database Server : Get an error as I try to start it

Here are the instructions on how to start the database:
Starting Oracle Database XE

Normally you first need to start the database and then you can access it with that link. I don't remember if the database is started after you do the installation.


How do I access the full text of an error in SSIS GUI?

Design-time errors, warnings, and informational messages show up in the Error List pane, which can be shown by going to View | Error List.


Pull data from sys.columns in a different database

Add in the database you are looking to query from in front of the sys.columns.

SELECT c.*
FROM <other database name>.sys.columns c
WHERE c.object_id = OBJECT_ID(@tablename);


measure the traffic throughput on mysql table

Try MONyog which has Disk Info tab which shows tables size
also you will come to know most accessed tables using Real-Time feature of MONyog.


Convert rows into columns and group some colums

Thankfully SQL Server has a built in function called a PIVOT that should do exactly what you are looking for.  There is a great article at SQLServerTutorial.Net which has some great examples on how to use this.

I think that this query should give you what you are looking for.  I built a table variable called @Table for the purpose of my testing.:

SELECT UserID,
[Date],
[O] AS 'O(Open)', --The value in the [Action] Column called out below, becomes the column name up here
[C] AS 'C(Click)', --The value in the [Action] Column called out below, becomes the column name up here
[P] AS 'P(Purchase)', --The value in the [Action] Column called out below, becomes the column name up here
Tracking,
Zip,
[Subject]
FROM
(
    SELECT UserID,
    [Action],
    [Date],
    Tracking,
    Zip,
    [Subject]
    FROM @Table
) T
PIVOT
(
    COUNT([Action])
    FOR [Action] IN([O], [C], [P]) --Call out the values in the Action Column
) AS Pivot_Table

For Me It Produced a Table Like Below:

UserID  Date        O(Open) C(Click)    P(Purchase) Tracking    Zip     Subject
cbg10   2020-01-02  2       1           1           Amz         30105   $1 Sales journey
cbg10   2020-04-18  1       1           1           TG          30105   Summer arrives
kel64   2020-01-02  3       2           1           Amz         45685   $1 Sales journey

I can break this down in a little more details if you need me to, since there are several nuances in what you asked for, grouping by multiple columns, mostly.

Hope this helps!


Database with replication and mirroring stuck in LOG_BACKUP after log file shrink

I wonder if your operations generated some gaps in the transaction log which may be preventing the virtual log file from looping back around to the front of the physical log file. Conceptually this is what should be happening within your database when the end of the physical file is reached:

If DBCC LOGINFO returns records indicating the VLFs at the beginning of the physical log file have a status of 2, these VLFs need to be cleared, which in Full Recovery mode, means a TLog backup must be taken.  I believe this situation can also cause the log_reuse_wait_desc column to show LOG BACKUP as well. To fix this, you must force the logical header back toward the beginning of the physical file to clear out these status 2 VLFs (and hopefully get your log_reuse_wait_desc column back to showing NOTHING).  To do this, perform the following steps (which may need to done on both DB1 and DB2 databases):

Perform a Log Backup (e.g. BACKUP LOG [DBNAME]...) against the database
Perform a DBCC SHRINKFILE operation on the transaction log file specifying 1 MB as the target size
Perform another Log Backup against the database
Perform another DBCC SHRINKFILE operation on the transaction log file specifying 1 MB as the target size
Manually increase the size of the log file, incrementally per Kimberly Tripp's article, to grow the TLog back to an appropriate size.

So what's happening here?

The first Log Backup simply generates a backup of the completed transactions that have been flushed to disk and deletes inactive virtual log files from the logical transaction log, freeing space for reuse up to the logical log header.
The first DBCC Shrinkfile operation shrinks the physical TLOG down to where the current logical log header is located, freeing as much space as possible.
The second Log Backup operation is now able to loop back around within the logical log and backup/clear any residual inactive VLFs located before the logical log header.  These were probably never flushed in the past for whatever reason, but should be cleared at this point.  This step is critical to fixing your issue and sometimes this backup can take a bit of time depending on how much data needs to be written down to disk.
The second DBCC SHRINKFILE operation moves the logical log header to the front of the physical log file, eliminates excessive VLFs that are likely there now, and reduces the TLOG file back down to a very small size, in this case 1MB.  It is imperative to manually grow the file in step 5 so you don't run into VLF issues again down the road
Manually increasing the size of the tlog will allow you to control the size and number of VLFs within your physical log file.  This is an opportune time to configure this properly, so take advantage.

One final note on the process.  If your tlog is highly active, you may need to perform this a few times or during a window of reduced activity.  High activity may result in another errant auto-growth event occurring before you execute the second log backup.  This basically turns what you thought was step 3 back into step 1.  Generally Steps 1 and 2 go by quickly, and step 3 takes the longest to complete.  Make sure to follow-through with steps 4 and 5.


This may not be a normalization problem. Almost every table has metadata or audit information -- the date the record was inserted and/or updated and by whom, app data such as page id, etc. This is not entity data, just other information that is needed when working with the entity data. As such, they don't really fall under the normal design rules, including normalization.

It sounds like your data concerning documents and other app info fits into this metadata role. If so, you don't have to worry about how it relates to entity data. Keep it as it is.


Using GROUP BY and ORDER BY in my query works in MySQL but not in SQL Server

To make this run all of the columns in the select have to be in the group by portion of the query.

Select B.BBarcode , Name, C.Country, Manufacturer,W.id,
 Packtype, B.TovarId, sum(WCount) as count, Price, Series, ExpDate
  from Warehouse W, Tovar T, Barcodes B,
  Countries C
  where C.CId=T.Country and
   B.WarehouseId=W.id and WCount>0 and
   T.id=W.TovarId +'=@param'
   GROUP BY B.BBarcode , Name, C.Country, Manufacturer,W.id,
 Packtype, B.TovarId, Price, Series, ExpDate
   ORDER BY Name

However I’m not sure the query will run with the where syntax you have, specifically the T.id=W.TovarId +'=@param' portion.

That can be refactored to be either

T.id=W.TovarId and T.id = @param

Or

T.id=W.TovarId and W.TovarId= @param

Version that should execute

Select B.BBarcode , Name, C.Country, Manufacturer,W.id,
 Packtype, B.TovarId, sum(WCount) as count, Price, Series, ExpDate
  from Warehouse W, Tovar T, Barcodes B,
  Countries C
  where C.CId=T.Country and
   B.WarehouseId=W.id and WCount>0 and
   T.id=W.TovarId and T.Id = @param
   GROUP BY B.BBarcode , Name, C.Country, Manufacturer,W.id,
 Packtype, B.TovarId, Price, Series, ExpDate
   ORDER BY Name

I would also look at some documents on how sql server does joins and the group by. While I believe sql server will do the joins correctly for an inner join between the tables based on how you’ve written them, the syntax structure they have make it a little more clear what is joining to what as well as the options to do other types of joins


TempDB internal space usage slowly growing from service broker

TLDR; Check for conversations being left open completely.

In our system we re-use conversations and have a table dedicated to holding these conversations that are usable, however the dev team setup a new service broker without my knowledge ages ago while I was off, didn't set up these conversation points and didn't set any thresholds on the alerting.

When the new system has been turned on the conversations are being opened but not closed properly and since there aren't any in the pool it is just creating a new conversation (we reached 7.1 million conversations for one service broker)

My steps for fixing was to create and record the 20 conversation handlers that I require for that service broker and record them into our table. This stopped the growth of the tempDB to stop the risk of the DB going down.

Then came the long process of closing off all the un-used conversations

select
    se.[conversation_handle]
from
    sys.conversation_endpoints se with (nolock)
    left outer join [queue].[maintained_Conversations] qh with (nolock) on qh.[conversation_handle] = se.[conversation_handle]
where
    qh.service_name is not null
    and se.far_service = @service

Gives the list of all the IDs cursor through those values and simply run END CONVERSATION @id; on each of them.

After the process has finished the temp DB space will loosen up (Note it does not do it as you close them, it seems to do it in large chunks when you're no longer working on creating / ending them (I cant guarantee this is how it works, just what I observed after stopping mid process and tempDB getting some space back)


For clients using the extended query protocol, durations of PARSE, BIND, and EXECUTE steps are logged independently. See also the description of log_min_duration_statement.

Note: when not using csvlog as log_destination, one can still collect all the necessary information by configuring the log_line_prefix setting in postgresql.conf (and reload PostgreSQL when done).

log_line_prefix = '%t %h %p %u '        # special values:
                                        #   %a = application name
                                        #   %u = user name
                                        #   %d = database name
                                        #   %r = remote host and port
                                        #   %h = remote host
                                        #   %p = process ID
                                        #   %t = timestamp without milliseconds
                                        #   %m = timestamp with milliseconds
                                        #   %i = command tag
                                        #   %e = SQL state
                                        #   %c = session ID
                                        #   %l = session line number
                                        #   %s = session start timestamp
                                        #   %v = virtual transaction ID
                                        #   %x = transaction ID (0 if none)
                                        #   %q = stop here in non-session
                                        #        processes
                                        #   %% = '%'
                                        # e.g. '<%u%%%d>'


disable constraints before using pg_restore.exe

Have you tried the --disable-triggers option to pg_restore?

Per the documentation: Use this if you have referential integrity checks or other triggers on the tables that you do not want to invoke during data reload.

Please note that this only is valid for a --data-only restore and requires the --superuser=username option to be passed, as well.


SQL Server 2000 : List and Disable all triggers

SQL Server 2000 has ALTER TABLE foo ENABLE TRIGGER ALL too

And to generate the required ALTER TABLE script use this

SELECT
    DISTINCT 'ALTER TABLE ' + OBJECT_NAME(parent_obj) + ' /*DISABLE*/ ENABLE TRIGGER ALL'
FROM
    sysobjects
WHERE
    xtype = 'TR'

I wouldn't bother testing status of the triggers, personally. A disabled trigger should not exist IMHO. Not least because OBJECTPROPERTY does not support it in SQL Server 2000

Edit:

My mistake, OBJECTPROPERTY in SQL Server 2000 has ExecIsTriggerDisabled as per comment.
So you can track status before and after


The best way to migrate tables between servers is to do it in binary "native" format. Doing a serial logical dump (as mysqldump does) not only may take days on a very large database, but it will take even more for recovery.

If you need to maintain availability on the source server for InnoDB tables, the best way is using an utility like MySQL Enterprise Backup (part of Oracle subscription) or the open source similar-tool Percona XtraBackup. Please note that the last one will only work for Linux hosts.

If most of your data is in MyISAM/Archive format, I am afraid that you cannot guarantee availability and consistency at the same time for the performing of the backup. In that case, the best way is to perform a snapshot (your filesystem or environment -VM, LVM- must allow it). Tools like mylvmbackup simplify the process.
Worst possible scenario, you can LOCK those tables -FLUSH TABLES WITH READ LOCK- (your are already doing that for the mysqldump), or even just shutdown the whole server, and just copy the .frm, MYI and MYD files away (for MyISAM). Please note that copying InnoDB (.ibd) files away does not simply work; you need MySQL 5.6 and the LOCK TABLES ... FOR EXPORT + ALTER TABLE...DISABLE/ENABLE TABLESPACE construction. Rsync may help lowering the time MySQL is unavailable.

Please note that binary files of MySQL are almost 100% compatible between different nodes, architectures and OSs.

Last option, if you do not have access to the filesystem, is to perform a logical backup in a more optimized way and do it in a parallel fashion. Tools like mydumper simplifies that greatly, and it is very used where XtraBackup or Enterprise backup do not work. It will be slower, but 5-10 times faster that mysqldump in a good scenario.


I think your best option is to use your favorite OS scheduling tool to run ANALYZE <table> explicitly and pre-emptively at the time of your choosing, say at 3 a.m.

PostgreSQL's autovacuum and autoanalyze are activity-counter driven.  This has the unfortunate effect that the time when the activity counters cross the thresholds is mostly likely to be exactly when the database is most active, which is when you least want those maintenance tasks to run.  By running the task preemptively during a off-peak time you will reset the activity counters, so that the auto-versions rarely will find work to do.  But in case of something going wrong, they are still there to save your bacon.

  Will the query planner eventually suffer from not having auto analyze running on a table that never gets updates or deletes?

This can't be answered with the information you provide.  If that table gets nothing but single-row lookups via a unique index, for example, out-of-date statistics are unlikely to be a problem.  If it gets complicated analytics queries, it is likely to be much more of a problem.


fighting filter (nested loops) execution plan from Oracle

On 11R2 I usually start with gathering more statistics for the tables:

exec DBMS_STATS.GATHER_TABLE_STATS ('SCHEMA, 'SMALL_TABLE', estimate_percent => '100');
exec DBMS_STATS.GATHER_TABLE_STATS ('SCHEMA, 'LARGE_DATA_TABLE', estimate_percent => '10');

If that helps then table preferences for automatic jobs can be set:

EXEC DBMS_STATS.SET_TABLE_PREFS ('SCHEMA', 'SMALL_TABLE', 'ESTIMATE_PERCENT', '100');
EXEC DBMS_STATS.SET_TABLE_PREFS ('SCHEMA', 'LARGE_DATA_TABLE', 'ESTIMATE_PERCENT', '10');

If large_data_table is really big (tens GB and more) then 1% or something like that may be needed.

And do not believe that in dba_tables sample_size=num_rows. For big tables actual auto sample size is much much lower. I had the SR with Oracle about that. They found actual sample percentage only from session trace file. It was ~0.004% for 170GB table.


Log Shipping with Clustered Instance- back up

Not at all. You can continue to have the backups on the FCI go to your network share. The log shipping job can be configured to copy from the network share to a local drive on the new instance, or read right from the network share.


Retrieving the top 5 users with the most views on their posts in total

nbk has answered already, I have set up your tables in a SQL fiddle, this makes it easier to play around. Consider using this tool for any more questions, because that makes it easier to talk about table structure etc.
http://sqlfiddle.com/#!9/45220f/2
To elaborate more on the question/answer already given: You need to group by a column, so that all columns sharing the same value will be grouped to a single row in the result. Then you can use aggregate functions on this group, like count. There are other aggregate functions like sum (to add numbers in a group). For more information see the documentation at https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html .


Microsoft Access Move Multiple Rows In Table1 To A Single Row In Table2

With help from Jonathan Fite who pointed me in the correct direction.
Via SQL:
TRANSFORM FIRST(RESULTS)
SELECT RECORDNUM
FROM Table1
GROUP BY RECORDNUM
PIVOT TYPE

Via GUI choose Totals and Crosstab then:


PostgreSQL extremely slow after upgrade from 9.3 to 9.4

Since the entire database seemed to be affected, I decided to try a VACUUM (FULL, ANALYZE). It took 50 hours and 4 minutes to complete, but it was totally worth it. Our database disk usage went from 1259 GB down to 747 GB (reclaiming a whopping 512 GB of space), and performance across the board has returned back to the levels we were seeing before the upgrade.


Combine column from multiple rows into single row with id

This seems like a simple self join:

SELECT
    enter.id,
    enter.date_
    enter.time AS enter_time
    leave.time AS leave_time
FROM
    tablename AS enter
  JOIN
    tablename AS leave
  ON  enter.id = leave.id
  AND enter.date_ = leave.date_
  AND leave.EorL = 2
WHERE
    enter.EorL = 1 ;

The query assumes there are (max) 2 rows for any id and date_.

If there are cases where people have entered without leaving, use LEFT join instead. If there are people who have left without entering, use RIGHT join. If there might be both cases, use FULL join. For RIGHT and FULL, you'll have to adjust the SELECT list, using leave.id, leave.date_ and COALESCE(enter.id, leave.id), COALESCE(enter.date_, leave.date_).


You can have 3 grids all with the same data source and a filter on the year and month. This would be a static filter though so you would be better off having 1 grid that step grouped on year and month.

This link shows you everything you need to know in order to do it in one grid.


How to exclude rows containing null values within columns?

SELECT * FROM (
SELECT student_id,
       salutation,
       CASE
           WHEN salutation IN('Dr.') THEN 'Doctor'
       END AS NewSalutation
FROM student.student
)
WHERE NewSalutation IS NOT NULL;

If that's your only section in the CASE statement, a logically equivalent query would be:

SELECT student_id,
       salutation,
       CASE
           WHEN salutation IN('Dr.') THEN 'Doctor'
       END AS NewSalutation
FROM student.student
WHERE salutation = 'Dr.';

... as anything other than Dr. will produce a NULL NewSalutation.


regexp_replace() vs. replace() to replace all matches in a string

Why?

Because Postgres replace() is a standard SQL function that works the same as in other RDBMS. Example: replace() in SQL Server:

  Replaces all occurrences of a specified string value with another string value.

While regexp_replace() is used to ...

  Replace substring(s) matching a POSIX regular expression.

The handling of regular expressions is obviously guided by the POSIX standard and works the same as other tools implementing it. The manual:

  POSIX regular expressions provide a more powerful means for pattern
  matching than the LIKE and SIMILAR TO operators. Many Unix tools such
  as egrep, sed, or awk use a pattern matching language that is similar
  to the one described here.


Using Composite Keys To Define "Dynamic" Relationship Types

The relationships can be simplified as

Entity <---Realtionship---> Entity

Like this

create table entity (id, entity_type /** etc **/)
create table relation (id, from_id, to_id, relation_type_id)
create table relation_type (id, name)

Pet/owner would then be

Entity(1, 'Dog')
Entity(2, 'Person')
Relation(n, 1, 2, 'Owner')

This concept also accomodates shared ownership, i.e. a dog owned by two people:

create table relation(id, from_id, to_id, relation_type_id, share)
Entity(3, 'Person')
Relation(n, 1, 2, 'Owner', 50.0)
Relation(n, 1, 3, 'Owner', 50.0)

You should of course create constrains/checks to ensure that share is always 100 etc.

If you need to enforce business rules, for example A pet can only be owned by a person, and a person can not be owned by anybody

create table allowed_relations(from_entity_type_id, to_entity_type_id, type_id)

In order to enforce this you will need to create a trigger on the     relation table and check if the proposed relation matches the allowed_relations table. If the number of business rules is small (and seldom changes), the rules can be hardcoded as a check constraint on the relation table.


Should I create a maintenance plan for my database or use jobs?

I can't think of any benefits, but I am also biased towards using my own jobs for maintenance.

I think that MPs are very good for new or accidental DBAs, as they allow for some maintenance activities to be done (which is better than having none at all). But as most DBAs progress in their experience they tend to start developing their own custom scripts instead.

HTH


What does "Sending data" mean in the mysql process list?

If someone knows more, please do fill in the gaps.

TL;DR "Sending data" seems to mean we are sending data about the query to the function that gathers that information together. It is NOT sending data to the MySQL or MariaDB client. This probably has something to do with the assembly of a JOIN command.

Finding very little good clarification on the Internet, I started looking through the MariaDB 10.2 code for "Sending Data" (yea, open source). Take all this with a some caution as I am not a C++ hacker.

You can find that in Line 10155 of sql/mysqld.cc where it is associated with stage_sending_data. This is found in sql_select.cc Line 3561 where it is at the end of the JOIN::exec_inner function. This function is called from the function JOIN::exec(). JOIN::exec is called from the function mysql_select(). This is called from handle_select(). It appears that "Sending data" should only show up on queries with JOINs.

I think I see mysql_select() set the status "Init" or stage_init. Then "Executing" or stage_executing in JOIN::exec_inner() while it does some checks for various things that I assume break JOINs or take special attention.

Then it sets that status to "Sending data" or stage_sending_data. In the next few lines after this status is set, it runs do_select(this, procedure);. At this point it appears to actually start gathering the information needed to provide an answer to the query. Contrary to my expectations, it is pretty clear that "Sending data" is not sending data to the client (over the network or otherwise), but to another function. It isn't yet ready to give a response. do_select() proceeds to do things like sorting temporary tables, evaluating conditions on HAVING, WHERE and stuff. It also removes locks. All that is after "Sending data" status is set.

It might be the last Status setting before actually returning the data. I haven't dug enough to state that clearly. A lot happens in do_select() so I expect the status could get changed in there somewhere, but it probably is dependent on various factors. At the end of mysql_select it does set the status to "end" or stage_end.


Updating mysql trigger via information_schema -- access denied?

What you are doing is impossible because the INFORMATION_SCHEMA database is a database made up of in-memory read-only temporary tables (See my post How is INFORMATION_SCHEMA implemented in MySQL?)

You are going to have to drop the triggers and recreate them

I wrote a post on that before (Oct 02, 2011 : Can mysqldump dump triggers and procedures?)

Here is what you can do for all triggers in the foo database

STEP 01 : mysqldump the triggers

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump -${MYSQL_CONN} -d -t --skip-routines --triggers foo > foo_make_triggers.sql

For clarification

-d is the same as --no-data
-t is the same as --no-create-info

STEP 02 : Create a script to drop all triggers

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('DROP TRIGGER ',trigger_name,';')"
SQL="${SQL} FROM information_schema.triggers"
SQL="${SQL} WHERE trigger_schema = 'foo'"
mysql ${MYSQL_CONN} -ANe"${SQL}" > foo_drop_triggers.sql

STEP 03 : Verify Contents of Each File

vi -R foo_drop_triggers.sql
vi -R foo_make_triggers.sql

STEP 04 : Edit all the definers in foo_make_triggers.sql as you know how

STEP 05 : Login to mysql and run this

use foo
source foo_drop_triggers.sql
source foo_make_triggers.sql

Test this on a staging server please

GIVE IT A TRY !!!


Query MongoDB Collection Compression Settings?

found the solution by myself.

use db.printCollectionStats()

in the output you will find the mentioned algorithm in the "wiredTiger.creationString" section.

"wiredTiger" : {
            "metadata" : {
                    "formatVersion" : 1
            },
            "creationString" : "allocation_size=4KB,app_metadata=(formatVersion=1),block_allocation=best,block_compressor=zlib,cache_resident=0,checkpoint=(WiredTigerCheckpoint.1=(addr=\"018181e4de542a6a8281e4f2e09da0808080808080e21fc0dfc0\",order=1,time=1440667794,size=8192,write_gen=2)),checkpoint_lsn=(2,25344),checksum=on,collator=,columns=,dictionary=0,format=btree,huffman_key=,huffman_value=,id=15,internal_item_max=0,internal_key_max=0,internal_key_truncate=,internal_page_max=4KB,key_format=q,key_gap=10,leaf_item_max=0,leaf_key_max=0,leaf_page_max=32KB,leaf_value_max=64MB,memory_page_max=10m,os_cache_dirty_max=0,os_cache_max=0,prefix_compression=0,prefix_compression_min=4,split_deepen_min_child=0,split_deepen_per_child=0,split_pct=90,value_format=u,version=(major=1,minor=1)",
            "type" : "file",

just look for block_compressor


MariaDB 5.5.47 but cannot Install TokuDB

What I did to get the built in TokuDB was 'remove' mariadb, mariadb.server and reinstall it using the steps here.

This setup even keeps the database files in a different dir rather than /var/lib and it still worked fine as long as you call mysql_upgrade --socket=[your custom socked location]


Linked tables versus stored procedure performance in MySQL

When I use Access, I use both techniques as some form of Command Query Separation (CQS)

That is, the form can read the base tables but all writes are via stored procedures.

In my case, I chose this for

security/audit reasons
easier to deal data integrity
re-use of write code for other clients

Note: I used a view for Access not a base table also for some security.

Edit, for clarity, after comments:

"Read only" refers to server-side permissions that are transparent to Access. Access would try to write if we let it (instead of intercepting the call and invoking a stored procedure), and we'd get an error from the DB engine.


Is there a way to do the following query with joins instead of a sub query?

this just trying to figure out your syntax:

    SELECT eu.[Name],
           pp.[MonthDay] --date type,
           pp.[Value] *  ( SELECT TOP 1 [Factor]
                             FROM [em].[CO] co
                            WHERE co.[EUID] = eu.[Name]
                              AND co.[MonthDay] <= pp.MonthDay
                            ORDER BY [MonthDay] DESC
                         ) AS [CO]
    FROM em.[PPGenData] pp
    JOIN em.[PPEUID] eu
             ON eu.ID = pp.ID

I think this will work:

select *
(
    SELECT eu.[Name],
           pp.[MonthDay] --date type,
           pp.[Value] * co.[Factor] as [ValueTimesFactor],
           row_number over(partition by co.[EUID] order by co.[MonthDay] DESC) as rn
      FROM em.[PPGenData] pp
      JOIN em.[PPEUID] eu
               ON eu.ID = pp.ID
      JOIN em.[CO] co
               on co.[EUID] = eu.[Name]
              AND co.[MonthDay] <= pp.MonthDay
)     tt
where tt.rn = 1

or

SELECT eu.[Name], pp.[MonthDay], pp.[Value],
       pp.[Value] * max(co.[Factor]) as [ValueTimesFactor],
  FROM em.[PPGenData] pp
  JOIN em.[PPEUID] eu
           ON eu.ID = pp.ID
  JOIN em.[CO] co
           on co.[EUID] = eu.[Name]
          AND co.[MonthDay] <= pp.MonthDay
 GROUP BY eu.[Name], pp.[MonthDay], pp.[Value]


Why Oracle 11g installs in folder C:\app\

This is explained in the documentation, here. See "B.2 Changes to the Optimal Flexible Architecture for Oracle Database".


Cannot restore SQL database backup to new database because new database is in use

You should offline the database and restore over it

Alter database db-name
Set offline with rollback immediate
Restore database .. with recovery

Ditch SSMS GUI and use tsql.


SQL Server Analysis Services - where do I store user logins?

SSAS works with windows authentication only. Being in a domain is the normal way to do this, hence Active Directory is usually the way to go. You have not provided much details in terms of suggesting something else.


What normalization rules does this table break

This is a terrible pattern, but it doesn't actually break any normalization rules.  The reason is that it's actually a change in what you are modeling.  Instead of your database modeling, say, Employees, it models Entities, Attributes, and Values.


You don't mention what engine this is going against so I'm assuming it's Microsoft SQL Server.  One word of advice is that you put the join predicate and specify the type of join in the FROM clause, it will make errors like this much more visible as well as making reading the query easier.  You forgot to specify the join type for the db1.dbo.Products table, which I think makes it a cross join, so vastly multiplying the number of rows you would get back.  See below.

merge into factOrders fo
using (
    select CustomerKey
        ,ProductKey
        ,CategoryKey
        ,dt1.TimeKey as [OrderDateKey]
        ,dt2.TimeKey as [RequiredDateKey]
        ,dt3.TimeKey as [ShippedDateKey]
        ,o.OrderID
        ,Freight
        ,od.UnitPrice as [UnitPrice]
        ,od.Quantity as [Quantity]
        ,od.Discount as [Discount]
        ,od.UnitPrice * od.Quantity * (1 - Discount) as [TotalPrice]
        ,sh.CompanyName as [ShipperName]
        ,sh.Phone as [ShipperPhone]
    from db1.dbo.Orders o
        INNER JOIN db1.dbo.[Order Details] od ON o.OrderID = od.OrderID
        INNER JOIN db1.dbo.Shippers sh ON sh.ShipperID = o.ShipVia
        INNER JOIN dimCustomers dc ON dc.CustomerID = o.CustomerID
        INNER JOIN dimProducts dp ON dp.ProductID = od.ProductID
        INNER JOIN dimCategories dct ON dct.CategoryID = p.CategoryID
        INNER JOIN db1.dbo.Products p ON
        INNER JOIN dimTime dt1 ON dt1.date = o.OrderDate
        INNER JOIN dimTime dt2 ON dt2.date = o.RequiredDate
        INNER JOIN dimTime dt3 ON dt3.date = o.ShippedDate
    ) o
    on (
            fo.ProductKey = o.ProductKey
            and fo.CustomerKey = o.CustomerKey
            and fo.CategoryKey = o.CategoryKey
            and fo.OrderDateKey = o.OrderDateKey
            )


Can Oracle databases start automatically when Windows machine is booted/rebooted?

on Windows system you have to use oradim.exe to admnister the Oracle instance behavior.
This is the documentation:
Using ORADIM to Administer an Oracle Database Instance

You have to check:

if the Windows service exists (if not create it with oradim);
how it is configured (using oradim)


returning set of records in stored procedure

There are a few ways of doing this in PostgreSQL depending on the versions you have to support.  The simplest is:

create or replace function plpgSqlProc () returns setof integer as $$
BEGIN

RETURN QUERY select * from foo;
END;
$$ LANGUAGE PLPGSQL;

This being said, I find where you don't need helper procedural code, plain SQL functions are usually better.  They are usually fast and sometimes they are even planner-transparent.


Using MVIEWS to take in time data snapshot of tables

DBMS_MVIEW.REFRESH

  list - Comma-delimited list of materialized views that you want to
  refresh. ... Alternatively, you may pass in a PL/SQL index-by table of type DBMS_UTILITY.UNCL_ARRAY, where each element is the name of a materialized view.

  atomic_refresh - If this parameter is set to true, then the list of materialized views
  is refreshed in a single transaction. All of the refreshed
  materialized views are updated to a single point in time. If the
  refresh fails for any of the materialized views, none of the
  materialized views are updated.

Maybe flashback query. Using Oracle Flashback Query DBMS_FLASHBACK.ENABLE_AT_TIME.


MySQL get_lock timeout misunderstanding?

The timeout applies to how long GET_LOCK will wait to acquire the lock (before timing out), in the event the lock is already held by another client.   It does not indicate how long a LOCK will be held as you mention


What's the most effective way to duplicate an instance on SQL 2008?

You've hit the nail on the head: copy the files. I've used this to good effect.

I'd say you have to define "cheap" to include investment of time to prepare a solution that doesn't involve xcopy


SQL Server 2016: Table can't grow while 313 MB is still available in the database

You mentioned you don't expect the delete operation to have released space but that is not the case. Pages that are emptied as a result of the delete become unused space (which may be reused for the same object) and emptied extents become unallocated space (which may be used for any object).
Consequently, the DELETE looks to have freed enough space within the data file to avoid the space problem. Consider reorganizing the indexes to further reclaim space from partially filled pages since your database is still over 95% full.


Avoiding a divide by zero error in a computed column

Just add a special case for division by 0:

CREATE TABLE [dbo].[TblDivision]
  (
  [Numerator] int NOT NULL,
  [Denominator] int NOT NULL,
  [Result] AS case when Denominator=0 then 0 else (Numerator/ Denominator) end
  );


How to log verbose output from mysqldump?

Your 1st mysqldump makes table structures and INSERTs and puts it in dump.sql.

Your 2nd dump is a remote dump that is piped straight into mysql in localhost.

If you are trying to catch any output based on errors, try this:

mysqldump -alv -h 123.123.123.123 --user=username --password=p@ssw0rd --add-drop-table databasename 2> output.log | mysql --user=username --password=p@ssw0rd -h localhost localdatabase

Using 2> will catch any error-based output (aka stderr). The mysqldump should still pipe normal console output (aka stdout) to the other mysql session and load the data as intended.

EXAMPLE : I have a small database called sample on my PC.

I ran this:

C:\LWDBA>mysqldump -u... -p... --verbose sample 2>sample.txt > sample.sql

C:\LWDBA>type sample.txt
-- Connecting to localhost...
-- Retrieving table structure for table users...
-- Sending SELECT query...
-- Retrieving rows...
-- Disconnecting from localhost...

C:\LWDBA>type sample.sql
-- MySQL dump 10.13  Distrib 5.5.12, for Win64 (x86)
--
-- Host: localhost    Database: sample
-- ------------------------------------------------------
-- Server version       5.5.12-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `users_tbl_points` int(11) NOT NULL,
  `users_tbl_rank` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `users_tbl_points` (`users_tbl_points`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `users`
--

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` VALUES (1,785523,9),(2,443080,20),(3,858830,7),(4,964909,3),(5,248056,24),
(6,345553,21),(7,983596,2),(8,881325,6),(9,455836,19),(10,635204,16),(11,808514,8),
(12,136960,28),(13,259255,22),(14,885399,5),(15,649229,15),(16,589948,18),(17,2055,30),
(18,240429,25),(19,195981,26),(20,258620,23),(21,705158,12),(22,749931,11),(23,634182,17),
(24,921117,4),(25,703038,13),(26,751842,10),(27,650093,14),(28,994943,1),(29,24437,29),
(30,137355,27);
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2012-03-02 15:49:54

C:\LWDBA>

Give it a Try !!!


For each of the databases you can set a max size per file.  Post about setting grow and file size

Here is Microsoft’s details on the alter file command

The advantages of doing it this way is that you can set each database to have a different file size and you don’t have to go to the trouble of creating the new drives and moving each database.

You can do this through click ops in the files section of each database setting the max file size for each file.

Alternatively you can script out the statements by doing a select on the sys.files table with some string building.

declare @usage table(Fileid int, FileGroup int, TotalExtents float, UsedExtents float, Name sysname, FileName nchar(520))
insert  @usage exec('dbcc showfilestats');
select [name], ((totalextents * cast(64 as float))/1024) [total]
,((usedextents * cast(64 as float))/1024) [used]
,((totalextents * cast(64 as float))/1024) - ((usedextents * cast(64 as float))/1024) [free]
, ((totalextents * cast(64 as float))/1024) - (((totalextents * cast(64 as float))/1024) - ((usedextents * cast(64 as float))/1024)) + ( (((totalextents * cast(64 as float))/1024) - ((usedextents * cast(64 as float))/1024)) * .3  ) [reduce]
,(1-(((usedextents * cast(64 as float))/1024) / ((totalextents * cast(64 as float))/1024))) [free %]
, 'alter database ' + DB_NAME() + ' modify file (name=' + rtrim([name]) + ', MAXSIZE = 50MB);' [Set Max File]
,'DBCC shrinkfile ([' + [name] + '],' +
    cast(
        cast((((usedextents * cast(64 as float))/1024) + ( (((totalextents * cast(64 as float))/1024) - ((usedextents * cast(64 as float))/1024)) * .3  ))
        as int)
     as varchar)
+ ');' [shrink]
, len(substring([name], 1, 36))
--, (select gender + ' ' + sport from v_sportseason where id = substring([name], 1, 36))
, [filename]
, 'alter database ' + DB_NAME() + ' modify file (name=' + rtrim([name]) + ', filename=''' + rtrim([filename]) +''');' [Move File]
 from @usage


How to identify hung queries, their importance, and then take action using command line?

There are two utilities that you may want to look at:

1) mytop

2) innotop

They are, as their names imply, the MySQL equivalent of the "top" system utility.

They won't help you figure out how "important" a query is because this requires deep knowledge of the system issuing the queries.

For a deeper, longer term (rather than real-time, catch-it-while-it's-running) insight, you can capture the slow log (adjust long_query_time appropriately to your opinion on what is problematic), and analyze the slow log using one of the following two tools:

1) mysqldumpslow

2) pt-query-digest

These will give you insight into which query "templates" are the ones consuming the largest amount of the server's CPU time.

When you outgrow the capabilities of the command line tools and you find you need a deeper long-term insight, I suggest you look at PMM (Percona Monitoring and Management), it is an extremely useful took for identifying performance issues on database (and not just database) servers.


When to use JSON or JSONB array vs SQL array in postgres

I think this is mainly a matter of personal preference, so I guess your question will be closed soon.
If you deal with JSON values in your application anyway, going with JSON might be easier (but then you can easily convert a native array to a json array using to_jsonb()).
There are a bit more functions for changing native arrays than there are for JSON arrays. So if you need to remove or find elements by value, native arrays might be easier. If those arrays contain integers, you can have even more functions for manipulating them through the intarray extension
And then of course there is the underlying question: wouldn't a properly normalized model be the best solution anyways, so that you avoid arrays completely?


How do I host and separate multiple projects on one Oracle Database?

What if you want to create 3 users and only use one schema by granting privileges to only one schema? Will there be 2 empty, unused schemata?
[...] Would that create 3 schemata, empty and unused?

Essentially yes. You'll use the users to log in to the database, but some of them won't have any objects, i.e. they'll have empty schema. (This is not uncommon at all.)
But none of this "creates empty schemas" in a technical sense. A schema is just a concept in Oracle, it doesn't have a concrete existence. You have users, and they own objects. Users and objects exist in the database in a very concrete sense, "schema" does not. It is just a term used to describe a user and its objects.

If I wanted to create a schema called "KillerWeb", would I have had to create a "user" called "KillerWeb"?

Yes, you need to create that user, even if no-one/nothing ever uses it to log in. (This not uncommon either. And you should actually prevent logins if no-one should.)

How do I host multiple projects on one Oracle database and separate them?

One option is creating a user for each application. By default, they won't be able to access each other's objects (unless you grant them the appropriate privileges).
Another option is Oracle's multitenant (additional cost, >= 12g only) option, a.k.a. pluggable databases, which gives you something in between one database with separate users and multiple, independent databases.
Or you could just give each app its own database.
How you decide between those depends on a lot of factors, including your budget, whether there is any shared data (and how you want/must share it), security considerations, backup/restore considerations, whether you suspect you'll want to split them up later, how you plan to scale, etc.


Changing SQL Server (MSSQLSERVER) Service Account

Correct, it is the service account for the database engine that matters here. Agent is irrelevant (it only tell SQL Server to produce the backup). It shouldn't break anything, but every change should be done with care. And make sure you do the change with the right tool (SQL Server Configuration Manager).


So - each tag can have one or more synonyms, and each synonym belongs to a particular tag.  Seems straightforward.  A few things that I'd suggest, are:

(1) Make sure that the synonyms as such are thoroughly CHECKed before they get INSERTed.  I have seen situations where photography / Photography / photography{blank} was in a stored in a lookup table - although these 3 words have one and the same meaning for an end user (you could use UPPER() or LOWER() and RTRIM()/LTRIM() to prevent this).  Write the CHECKs in your DDL code wherever possible.

(2) I'd use a UNIQUE constraint on the "description" column in the SYNONYMS table (eg if you don't want to have duplicate synonyms - because the FK alone will probably not prohibit this).

(3) Also: are you absolutely sure that a synonym that has a rather broad meaning (something like the name of a supertype) cannot be used for several tags?  In your synonyms example photography, photographers, photo - suppose we have a TAG called "images", then "photo" could be a synonym for this, too (apart from being a synonym for "photographer").
You can still model this, but you'd probably need an "intersection" table for this purpose, as you suddenly end up with a many-to-many relationship between TAGS and SYNONYMS.  (Please feel free to ask some more questions about this, if it is not clear to you).


A dimension key is the primary key of a dimension table.

In the example below, the "Time_key" column is the dimension/primary key of the Time dimension. This column will be used to join the dimension with fact tables, just like any other key.

in https://www.kimballgroup.com/1997/07/its-time-for-time/

A degenerate dimension is, however, a different and slightly more advanced concept to model. In the degenerate dim case, a fact table contains a foreign key that, by design, does not link to a dimension.


Format value to configured number of decimal places

The STR function seems to be what you're looking for:

DECLARE @NumberOfDecimalPlaces tinyint = 3;
DECLARE @ActualValue decimal(16,5) =  3.50

SELECT FormattedActualValue =
    STR(
        @ActualValue,
        CAST(@ActualValue / 10 AS int) + @NumberOfDecimalPlaces + 2,
        COALESCE(@NumberOfDecimalPlaces,2)
    )


First of all, the code you posted is not the code executed by the application. There is no Fetch Queryoperator so you aren't retrieving any rows.

As you noted in chat you constructed the code yourself but it's hard to guess what exactly will be the code executed by AX, that depends on some settings and calculations done by the AX kernel.

First of all, your cursor is likely to be FAST_FORWARD and the number of rows fetched could either be everything at once, or a number of records at a time.

The number of rows fetched at once is defined by the Maximum Buffer Size parameter/the row size. See Microsoft Dynamics AX 2012 Server Configuration

The cursor definition will look something like this

declare @p1 int
set @p1=189527589
declare @p3 int
set @p3=16
declare @p4 int
set @p4=1
declare @p5 int
set @p5=2
exec sp_cursoropen @p1 output,N'SELECT A.JOBTYPE FROM PRODROUTEJOB A WHERE ((A.DATAAREAID=N''IW'') AND ((A.CALCTIMEHOURS<>0) AND (A.JOBTYPE<>3))) AND EXISTS (SELECT ''X'' FROM PRODROUTE B WHERE ((B.DATAAREAID=N''IW'') AND (((((B.PRODID=A.PRODID) AND ((B.PROPERTYID=N''PR1526157'') OR (B.PRODID=N''PR1526157''))) AND (B.OPRNUM=A.OPRNUM)) AND (B.OPRPRIORITY=A.OPRPRIORITY)) AND (B.OPRID=N''GRIJZEN''))) AND NOT EXISTS (SELECT ''X'' FROM ADUSHOPFLOORROUTE C WHERE ((C.DATAAREAID=N''IW'') AND ((((((C.WRKCTRID=A.WRKCTRID) AND (C.PRODID=B.PRODID)) AND (C.OPRID=B.OPRID)) AND (C.JOBTYPE=A.JOBTYPE)) AND (C.FROMDATE>{TS ''1900-01-01 00:00:00.000''})) AND ((C.TODATE={TS ''1900-01-01 00:00:00.000''})))))) GROUP BY A.JOBTYPE ORDER BY A.JOBTYPE ',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

or this

declare @p1 int
set @p1=NULL
declare @p2 int
set @p2=0
declare @p5 int
--
-- Fast Forward(16)+Parameterized(4096)+AutoFetch(8192)+AutoClose(16384)
--
set @p5=16+4096+8192
declare @p6 int
set @p6=8193
declare @p7 int
--
-- Number of Rows for AutoFetch.
-- This is calculated by Maximum Buffer Size (24K default) / Row Length
--
set @p7=4
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 nvarchar(5),@P2 nvarchar(21)',N'SELECT A.SALESID,A.RECID FROM SALESLINE A WHERE ((DATAAREAID=@P1) AND (SALESID>@P2))',@p5 output,@p6 output,@p7 output,N'ceu',N'SO-100004'
-- @p2 contains cursor handle for fetch call
exec sp_cursorfetch @p2,2,1,@p7

That being said, most of the time when I get a poor performing query in AX the query behaves the same with or without a cursor, and it's rare that I need the actual code including the cursors to be able to reproduce the problem in Management Studio, however if you really need the cursor definition the easiest is if you can reproduce it in a test environment.

Once you can reproduce the issue you need to locate the offending query in code.
If you can reproduce the issue in a test environment (because AX is fairly prone to parameter sniffing) you can use the built-in SQL Statement trace log to find the call stack for the query.

You can also look into the Trace Parser tool to capture traces with the actual parameter values and the code trace up to the query. That can help a lot too.

Once you know where the query is executed you could put a breakpoint just before the query and when you hit it fire up SQL Server Profiler to capture the exact code being executed while you step through the select in your AX debugger.

Once you have the problematic code (if you really need it including cursors) the regular tuning principles are valid.

Your first attempts should be indexing because your options to change the query are a bit limited in AX. Then again the estimated plan you posted only has an estimated cost of 0.04 and uses index seeks I'm not sure this is your problem.

AX is very prone to parameter sniffing because of all the select * resulting in a lot of key lookups.

You could try to use some keywords to influence the plan, namely forceliterals to disable parameterization and force a new plan to be compiled for each execution or you could set some row goals using firstfast and that's about it.

See the Select statement syntax documentation to see which options you have, but don't overdo it with the keywords (as I suggested in my answer to your other question). My first guess is you are running into parameter sniffing.


CHECKPOINT stealing connection in SINGLE_USER mode

Your script is failing on the ALTER DATABASE not on the RESTORE. Try setting it to RESTRICTED_USER first:

EXEC('
USE master;
ALTER DATABASE '+@db_name+' SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
USE '+@db_name+';
ALTER DATABASE '+@db_name+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
USE master;
DROP DATABASE '+@db_name+';
');

This script so far has never failed me.

There are two differences to your script. The main difference is that setting the database to RESTRICTED_USER first allows the script to get executed even if the database is in SINGLE_USER to start with.

The second is that it drops the database instead of restoring it. You could try to replace the DROP with your restore directly, but that might open a new "gap" for something else to sneak in. As you are restoring from a file and not from the database-backup-history, dropping the database first should not be a problem for you. Just execute the restore after the database is gone.


Does openquery use distributed transactions?

It can use distributed transactions if the OLE-DB provider supports it. Otherwise it tries to limit the commands to read-only operations.

Reference: http://msdn.microsoft.com/en-us/library/ms177403%28v=sql.90%29.aspx


32-bit versus 64-bit ODP.NET to Oracle connection

Did you control PATH Environment Variable to which comes first, 32 or 64. Which ever client is installed last,  change PATH accordingly. I have encountered similar issue. Windows uses PATH environment variable to decide which dlls to load. I have two clients installed in my machine and used PATH variable to force which one to use.

C:\oracle\product\11.2.0>dir
 Directory of C:\oracle\product\11.2.0

20.09.2011  10:07    <DIR>          .
20.09.2011  10:07    <DIR>          ..
20.09.2011  10:08    <DIR>          client32Bit
20.09.2011  10:04    <DIR>          client64Bit

C:\oracle\product\11.2.0>echo %PATH%

XXXX;C:\oracle\product\11.2.0\client64Bit\BIN;


Make sure you TCP/IP is enabled under SQL SErver Network Configuration

If it is disabled than please enable it and restart services for that instance.

you will be able to log-in.


Reporting on a normalized Key-Value pivot

A friend offered me this solution, although he doesn't have an account for SE.

Note from 2 years later: This provides the number of times each answer occurs per question. Which is kind of helpful, but doesn't tell us, "what are all of the questions and answers given on this one survey submission?" Maybe I should have emphasized that 'ResponseValue' can be free-form text.

SELECT SIK.Name AS [QuestionKey], SIV.JsonValue AS [ResponseValue], COUNT(SIV.Id) AS [Frequency]
FROM [Surveys] S
JOIN [SurveyInputKeys] SIK ON SIK.SurveyId = S.Id
JOIN [SurveyInputValues] SIV ON SIV.SurveyInputKeyId = SIK.Id
WHERE S.Id = @surveyId
GROUP BY SIK.Id, SIV.JsonValue, SIK.Name

Psuedo-output...

   SurveyId    |   QuestionKey   |   ResposeValue   |  Frequency
      1        |   Best Singer   |     "Adele"      |     5
      1        |   Best Singer   |   "John Doe"     |     2
      1        |   Best Singer   | "Free Form Text" |     1