02270. 00000 - "no matching unique or primary key for this column-list"

This:

CREATE TABLE CONFERENCESESSION (
SESSIONID CHAR(4),
BUILDINGNO CHAR(2),
ROOMNO CHAR(2),
SPEAKERID CHAR(2),
SESSIONDATE DATE,
SESSIONPRICE NUMBER(4,2),
CONSTRAINT CONFERENCESESSION_PK PRIMARY KEY(SESSIONID),
CONSTRAINT CONFERENCESESSION_FK1 FOREIGN KEY(BUILDINGNO) REFERENCES
BUILDING(BUILDINGNO),
CONSTRAINT CONFERENCESESSION_FK2 FOREIGN KEY(ROOMNO) REFERENCES ROOM(ROOMNO),
CONSTRAINT CONFERENCESESSION_FK3 FOREIGN KEY(SPEAKERID) REFERENCES
SPEAKER(SPEAKERID));

Should be changed to:

CREATE TABLE CONFERENCESESSION (
SESSIONID CHAR(4),
BUILDINGNO CHAR(2),
ROOMNO CHAR(2),
SPEAKERID CHAR(2),
SESSIONDATE DATE,
SESSIONPRICE NUMBER(4,2),
CONSTRAINT CONFERENCESESSION_PK PRIMARY KEY(SESSIONID),
CONSTRAINT CONFERENCESESSION_FK1 FOREIGN KEY(BUILDINGNO, ROOMNO) REFERENCES
ROOM(BUILDINGNO, ROOMNO),
CONSTRAINT CONFERENCESESSION_FK2 FOREIGN KEY(SPEAKERID) REFERENCES
SPEAKER(SPEAKERID));

A foreign key requires a unique or primary key constraint on the referenced columns in the parent table. A multi-column constraint is not interchangable with single-column constraints.


how to add option(recompile) within this stored procedure?

No, this does not look to be a good candidate for recompile. From the code, it looks like activityId is unique and will result in a trivial plan so you'll get the same plan regardless of the parameters passed. Add DDL to your question if this is not the case in your situation.

In cases where the optimal plan differs based on the actual parameter values passed (because these are used in WHERE/JOIN clauses and the optimal plan varies accordingly), you could specify the OPTION(RECOMPILE) query hint on specific problem queries to avoid reusing non-trivial plans. However, if the query is executed quite frequently, the compilation cost could outweigh the benefits. It would be better in that case to use an OPTIMIZE FOR hint or use the query store (or plan guides) to coerce a specific plan.


Setting up Data Level Application Alerts in SQL Server

No, there is not any built-in feature that does exactly this.  I'm also not aware of an off-the-shelf product that does what you're looking for.

However, based on the description of your requirements, it seems like a SQL Server Agent job would work really well!

Set up an agent job that runs every 15 minutes, has steps that run the different checks you're interested in, and sends emails to your support team using sp_send_dbmail.

To get started, find the "SQL Server Agent" node in SSMS, right click on "Jobs," and choose "New Job."  There's a detailed walkthrough of all the options here: Create a Job | Microsoft Docs


Install Access Database Engine on Linux Centos

I solved it by adding some extra command in terminal, like this:

isql -v mydb
and the result :
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

nb: "mydb" get it from odbc.ini file


How to check if feature (JSON) exists, and/or version

/*!50700 ... */ says:  If you are running a version older than 5.7.0, this is a comment.  Else it is sql.  It can be entire statements or fragments of statements.

Example (taken from mysqldump output):

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

Presumably, @@TIME_ZONE was implemented in 4.1.3.

(Note:  I think the two spaces are part of the syntax.)


SSIS SQL Server Destination Good or Bad?

The source/target databases and ssis packages are located on the same server.

Since you have above, you can use SQL Server destination. From Data loading performance guide :

The SQL Server destination is the fastest way to bulk load data from an Integration Services data flow to SQL Server. This destination supports all the bulk load options of SQL Server – except ROWS_PER_BATCH.
Be aware that this destination requires shared memory connections to SQL Server. This means that it can only be used when Integration Services is running on the same physical computer as SQL Server.

Internally, SQL Server destination uses Bulk Insert .. uses Shared Memory which is extremely fast (you can confirm running a trace to see what it is executing behind the hood).


MySQL MyISAM Table Locking and Delayed Inserts for Webserver Access Logs

FWIW, I think this is the wrong way to do this; unless you need to do real-time reporting on webserver access write the log as a file and bulk-load it.

Having said that, there is a way to do what you want, which if I understand it correctly is to queue up pending inserts while doing maintenance on your database. In other words, decoupling the webserver's INSERT operation from the actual INSERT in the database in some sort of buffer. The way to do it using two instances, one that is directly written to in which you will use the blackhole storage engine, and one that you will replicate to, which will be your "real" database. The pending inserts will live in the binlogs; you simply stop replicating to the slave, perform your maintenance, then resume replication again and the slave/real database catches up. The slave is available for SELECTs for your reporting. You can even multi-master for load balancing, or have multiple slaves and switch between them during maintenance activities.


Database size increases after creating a partition

One reason is that new data was allocated but old not cleared. This is partially because it is not available immediatly, the other reason may be that SQL Server has to wait until partitioning finishes to delete the old table. if you are considered by that (which I find funny - heck, it is even funny partitioning a very small table on a tiny database of 5gb) then you may shrink the file to reclaim the space. THAT SAID: perforamnce oriented databases should not autogrow to start with ;)


sql syntax error 1064 in workbench-generated script

Give this a try, your issue was the syntax with the foreign key and the references.

CREATE TABLE IF NOT EXISTS `project2`.`REF_AUTH` (
  `REF#` INT(11) NOT NULL AUTO_INCREMENT,
  `RAUTHOR` VARCHAR(45) NOT NULL,
   PRIMARY KEY (`REF#`, `RAUTHOR`),
   CONSTRAINT `REF#`
      FOREIGN KEY (`REF#`) REFERENCES `REF` (`REF#`)
      ON DELETE CASCADE
      ON UPDATE CASCADE)
   ENGINE = InnoDB
   DEFAULT CHARACTER SET = big5

Syntax link

Another helpful link


How to Log All mysql queries into log file?

[mysqld]
# Set Slow Query Log
long_query_time = 1
slow_query_log = 1
slow_query_log_file = /usr/log/slowquery.log
log_queries_not_using_indexes = 1

#Set General Log
general_log = on
general_log_file=/usr/log/general.log

Note that enabling general_log on a production server has overhead you should avoid it. You can check problematic queries from slow log.


Executing SSIS Package from a stored procedure with different user privileges

For posterity I got this working via the following:

Altering the stored procedure called by the users (Admin.RunImport) to 'Execute as' the account used by the SQL Service
The SQL Server service account (an AD Managed Service account) is modified to have permissions to execute the Admin sproc allowing the use of execute as above
The SQL Server service account is modified to have ssisdb.ssis_admin and msdb.SQlAgentOperator roles.
This Admin.RunImport stored procedure queues up one of 3 Agent Jobs using sp_start_job dependent on a passed parameter

This redirection via SQL Agent is required to bypass the ssis security context error above
The agent jobs are owned by 'sa', and simply execute an underlying stored procedure (Raw.hp_Execute_Import_Impl) passing in a different parameter per job.
This means that the Agent job runs as sa due to the ssis_admin privelege above, the same as the scheduled jobs

The Raw.hp_Execute_Import_Impl stored procedure queues the SSIS Package as sa the same as normal.

In lieu of being able to create dedicated windows accounts for this purpose I think this as good as I'm going to get at the moment.

Thanks for the help guys!


mongodb could not find host matching read preference { mode: \"primary\" } for set?

I faced the same issue then looked into Mongos log file and it was saying

can't authenticate to hostname as internal user, error: Authentication failed.

Please see here: internal user authentication and happens with SecKey file and auth- Enabled.
Resolution
I opened mongod.conf file and
security: authorization: enabled
keyFile: /var/run/mongodb/secKey.key

was missing so I added that and restart the mongod. Then I created shard again from Mongos and it is working now.


CREATE FUNCTION inside BEGIN TRY and END TRY

Many DDL statements must either start a batch or be the only statement in a batch.  So the general solution is to use dynamic SQL.  EG
I want to use CREATE FUNCTION in a block that will "swallow" errors, but this
BEGIN TRY

exec('
CREATE FUNCTION test (@ID int)
RETURNS int
AS
BEGIN
    RETURN(2 * @ID)
END
')

END TRY
BEGIN CATCH
END CATCH


Show rows with non-unique value in one field

Select fruit_name, some_value
  From tooty_fruity
  where fruit_name in (
    Select fruit_name from tooty_fruity
      Group by fruit_name
      having count(*) > 1)


Creating sequence for longer numeric values

If you can live with losing some values to the maximum value, you could combine a sequence with a fixed offset to get the 20 digits. I would also define a check constraint on the table to to make sure that accidental inserts without using the default value insert the wrong value:

create sequence my_sequence_name;
create table foo
(
   id numeric(20,0) default 10000000000000000001 + nextval('my_sequence_name'),
   constraint check_range check (id between 10000000000000000001 and 99999999999999999999)
);

That will give you a maximum generated value of 19223372036854775808 which doesn't use the complete range of allowed values, but it might be enough for your needs.


SQL Server - storing ulong as primary key

I'm assuming by ulong that you mean a 64-bit unsigned integer.

Your best option if you truly need to span the entire range of a ulong is to use NUMERIC(20,0) or larger.  If you need to enforce the range of values to those valid for your data type, use a check constraint to restrict the values to between 0 and 18,446,744,073,709,551,615.  Alternately, you could use VARBINARY(8), but your application would have to be base-aware.


best ETL design to transfer transaction tables records into the data-warehouse

Do the transactions have an audit timestamp ? It has to be one that goes up only (no late-arriving facts. insert/update audit timestamps are good for this)
If so you could use this to define a range to extract. It's a common technique for this type of thing:

For each extraction, at the start, determine the range you want to extract (let's call the minimum timestamp min_ts and the maximum timestamp max_ts)
At the start of the extraction, put 1 line in a separate table (let's call it extraction_log and also give it a PK) with fields: extraction_id = a unique key, min_ts, max_ts, status = 'Starting Extraction'.
Use min_ts & max_ts to extract the data, either in 1 go (select * from where ts > min_ts and ts <= max_ts) or in chunks if needed.
Ad the end of a successful extraction, update the line and set status to 'Finished OK'

how to determine min_ts and max_ts ?

You could take min_ts from the extraction_log, using the last successful max_ts. select max(max_ts) from extraction_log where status = 'Finished OK'
You could take max_ts from the source db at the start of your extraction. select max(audit_ts) from source_table

There are alternatives here. If you're extracting these into a temporary staging table (a best practice), you could also take too much, ie the last 5 days and deal with duplicates later when upserting the entries in your ODS. For max_ts, if you're absolutely certain that the clocks between your dwh and the source are and will remain in sync (this is a very dangerous assumption - not recommended), you could even use sysdate()

Technically, you can get away with less effort. You don't really need the statuses, or a extraction_log table that keeps track of each batch. But I have found having it like this helps a lot with debugging and troubleshooting later on.
Additionally, if you need a routine to remove entries from a load that failed halfway, to find gaps for ranges of past extractions, and so on, the extraction_log will help. You might even want to include the extraction_id as an additional column in your ODS.

some further thoughts

if you don't have a good timestamp candidate, a technical key in the source system with the same properties (must go up only, no late-arriving facts) should be fine too.
if the timestamps are generated by the source application and there is a risk that 2 transactions with the exact same timestamp are not inserted at the same time (quite common), then it's safer to use a max_ts that is slightly in the past (select max(max_ts) - 5 minutes from source_table.)
regarding failed loads. if you're only concerned with cleaning up entries of the last failed loads, you can add it as a first step in your ETL flow. (delete from dwh_table where ts > min_ts) This will remove the entries from any failures (if any) after the last successful extraction. It won't deal with failures between earlier successful extractions.


Why does SQL Browser service get disabled?

The SQL Server Browser Service is installed often in Disabled state (and this is what my SQL Slammer link is about, it gives the history why the Browser service is preffered to be disabled unless required). It is only required for named instances or instances listening on non-default ports. Your application installer should only restart the Browser service if needed, and certainly should check for a disabled service and ask an administrator permission to enable the service (ie. display a confirmation dialog) before messing with it.


Which programming languages will have problems pulling dash named column names?

I have no experience in clojure, but it looks very similar to LISP. Not sure what languages that will get in to trouble, but every major DBMS will. I tested with Mariadb:

[test]> create table tx (a-b int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MariaDB server version for the right
syntax to use near '-b int)' at line 1

and with DB2:

~]$ db2 "create table tx (a-b int)"
DB21034E  The command was processed as an SQL statement because it was
not a valid Command Line Processor command.  During SQL processing it
returned: SQL0104N  An unexpected token "-b int)" was found following
"create table tx (a".  Expected tokens may include:  "<space>".
SQLSTATE=42601

I suspect that all other DBMS:s will have problem as well. You can quote your way around that:

]$ db2 "create table tx (\"a-b\" int)"
DB20000I  The SQL command completed successfully.

but I would strongly advise against that.

I would create a mapper in Clojure that translates "-" to "_" and vice versa for all DBMS objects.


From the documentation on sp_who:

  In SQL Server 2000, all orphaned DTC transactions are assigned the SPID value of '-2'. Orphaned DTC transactions are distributed transactions that are not associated with any SPID. Thus, when an orphaned transaction is blocking another process, this orphaned distributed transaction can be identified by its distinctive '-2' SPID value. For more information, see KILL.

You can find the actual owner of the work using:

USE master;
GO
SELECT req_transactionUOW -- UOW GUID
  FROM syslockinfo
  WHERE req_spid = -2;

You can then use:

KILL 'GUID_from_above';


Restarting SQL Server if shrinking database files

As you mentioned yourself, often shrinking is a bad idea, and should only be done on special occasions. Eg. You just archived half of your db, and you're really in need of some extra space.

Rebooting the server will NOT solve your performance issues, what this will do is give you a bunch of new issues.

SQL likes to keep data pages (Buffer pool) and plan caches in memory, once you reboot the server these are gone, which means all queries will need to get new plans compiledand new data filled in buffer pool from disk, which of course is a lot slower.
A lot of the DMVs that return server information will have lost all useful information
Rebooting causes downtime, can you afford to have your services go down?

What you should do: Allocate enough space for your data files so they don't need to grow, and keep some extra free space in case of unexpected data growth.
If there's not enough disk space to use for your normal day to day use you could:

Increase disk size
Take a look at unused/duplicate indexes, perhaps some space can be freed up here
Take a look at your data compression
Archive unneeded data


How to fill a tabe automatically with a procedure in MySQL

Ok i got your answer !
Go to Talend download page and download the Talend Open Studio for Data Integration. Is free and uses Java to work.
After you downladed and installed talend go to this link to see how to generate what ever you need for free
 I am using Talend for stress testing and ETL process
You can throw the generated data mysql directly or into a CVS file and then load it into the mysql table !


How to prove the lack of implicit order in a database?

I see three ways to try to convince them:

Let them try the same query but with bigger table (more number of rows) or when the table is being updated between executions. Or new rows are inserted and some old ones are deleted. Or an index is added or removed between executions. Or the table is vacuumed (in Postgres). Or indexes are rebuilt (in SQL Server). Or the table is changed from clustered to a heap. Or the database service is restarted.

You can suggest that they prove that different executions will return the same order. Can they prove it? Can they provide a series of tests that proves that any query will give the result in the same order, no matter how many times it is executed?

Provide the documentation of various DBMS in that matter. For example:

PostgreSQL:

Sorting Rows
After a query has produced an output table (after the select list has been processed) it can optionally be sorted. If sorting is not chosen, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on. A particular output ordering can only be guaranteed if the sort step is explicitly chosen.

SQL Server:

SELECT - ORDER BY Clause (Transact-SQL)
Sorts data returned by a query in SQL Server. Use this clause to:
Order the result set of a query by the specified column list and, optionally, limit the rows returned to a specified range. The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified.

Oracle:

order_by_clause
Use the ORDER BY clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.


Updating a big replicated Dimension (SQL Server PDW)

A few questions. 20 million rows isn't necessarily that large.

What process are you using to perform your updates and deletes right now?

Is the dimension a CLUSTERED COLUMNSTORE INDEX, CLUSTERED INDEX or HEAP?

Are you saying that there is movement whilst you update and delete this table or did you just see movement when you changed the table from replicated to distributed?

If it is the latter that is not surprising. You are unlikely to be join and aggregation compatible. If you are doing something to trigger the movement through your update / delete then we could look at that - although a concrete example would be helpful.

In general terms I would start by trying to keep the ETL simple.

Use CTAS against the dimension selecting only the rows you want to keep, union in any new rows and use CASE to pick up any changes (converting the UPDATE into a transform within the CTAS). Once complete you can then use a pair of RENAME OBJECT commands to switch from the current table to the new table. This gives you the added benefit of having a historic view of your table - which you can drop at your leisure.


The error message is stating that SQL Server cannot locate a login named Hiru, or you do not have the privilege required to create a user.

The SQL Server security model consists1 of a server-level login that provides access at the instance level.  Logins are then associated to a user inside each database.  The user typically has the same name as the login, although that is not required.

Creating a login and user is easy to do with T-SQL, as in:

USE master;
CREATE LOGIN [Hiru] WITH PASSWORD = 'pwd';
USE mydb;
CREATE USER [Hiru] FOR LOGIN [Hiru];

Using a T-SQL script provides more direct control over the process, and allows better troubleshooting.

1 - unless you're using the contained database security model


"Linked server does not contain table" error when using VFPOLEDB provider linked server

Eventually found the answer when attempting to work around the problem using SSIS and the VFP ODBC driver. VFPODBC provided a more verbose error message, stating that the .fpt file was missing for the dbf file I was attempting to query.

The staff that did the original data copy (the VFP data is hosted off server) failed to include all the data available (.cdxs, .fpts, .etc). After recopying the data, the linked server described above functions correctly.


DB2 doesn't start after change db2 hostname

You could just try applying those steps.
If that fails, another option would be to create a new instance and then restore the database(s) over to the new instance.
Another option could be uninstalling the instance and then reinstalling it.
Another could be taking a backup of the database, saving it off somewhere and then doing an image restore (pending you have one of those) on the system.

If it were me, I'd just try applying the steps in the rename the DB2 hostname link first. Then I'd probably go for either creating a new instance or uninstalling and reinstalling the existing instance.


Why does a query on 4 times more data take 300 times more processing?

You need to provide more information for us to be sure: the table definitions including all the indexes and other keys defined upon them, if any of the referenced objects are views rather then tables then their definitions too and those of their dependant objects, and the query plan (I'm assuming sybase can be asked to present you with that as easily as other DBMSs).

A super-linear growth in effort compared to data size usually means that the database is having to scan or partial scan one object for every row output from another. Another reason for unexpected time for lager data is that the data was previously small enough to all be processed in memory but with the new size the database is having to either read more from disc (this compounds very badly with having to scan something multiple times) or spool intermediate results out to disc or both.

We could hazard some guesses, but if you edit the question to include more info (query plan and table/index/key/view definitions) we can make much better suggestions with less guess work (and tell you have to interpret that information yourself if the same problem comes up elsewhere in future).


Is it possible to get execution call stack in a trigger?

Yes, it is possible to identify the running code, by using the @@procid system function, and better OBJECT_NAME(@@PROCID) to have the complete name.

Definition: "Returns the object identifier (ID) of the current Transact-SQL module. A Transact-SQL module can be a stored procedure, user-defined function, or trigger. @@PROCID cannot be specified in CLR modules or the in-process data access provider."

You can read about it here.

Another option would be to check the sql plan of the current spid and save that info in a logging table.
A sample query to be used in each procedure to save audit data would be :

select sp.hostname, sp.program_name, sp.loginame,
    st.text as query_text
from sysprocesses sp
cross apply sys.dm_exec_sql_text(sp.sql_handle) as st
where sp.spid = @@spid

Maybe there are too many details there..but I believe that you get the idea.

A third option would be to use the context_info information to the current SP's session. And associate somewhere the context information saved there with each procedure. For example in procedure1 you write 111 to the context, in procedure2 you write 222.. and so on.

A lot more info regarding context_info you can read in this SO question.


If your SQL Developer connection is working, then your database is not 'myschema', it is 'XE'.


Iterating through results and performing sum on values

I can give you some guidance, at a minimum. I don't use MySQL in detail, but in general:

Step 1: get the date ranges

I assume that for each row returned from mytable that you want the date in that row as a start date, and the date in the following row as the end date. Check MySQL documentation on "windowing functions" (related to aggregate functions). There may be a function that can do this for you (in SQL Server, the function is LEAD. If you can't find that, you can always do a subquery in each row, returning the smallest date (matching the same criteria) bigger than the date in the current row.  This would look something like:

SELECT id, dates as startdate
      ,(SELECT MIN(dates) FROM mytable WHERE id = a.id and dates > a.dates) as enddate
  FROM mytable a
 WHERE id = '123'

(Exact syntax may be different).

Step 2: Get the SUMs

Given that we have the start and end dates for each range, getting the SUM of the age ranges is fairly easy. We join the query we did above against Table2 and use GROUP BY and SUM. I assume that dates that fall on a boundary should not be included in both groups, so we'll toss them into the later group:

SELECT dtr.startdate, dtr.enddate, SUM(ag.age) as sum_Age
  FROM (SELECT id, dates as startdate
              ,(SELECT MIN(dates) FROM mytable WHERE id = a.id and dates > a.dates) as enddate
          FROM mytable a
         WHERE id = '123'
       ) dtr
       LEFT OUTER JOIN Table2 as ag ON (ag.date >= dtr.startdate AND ag.date < dtr.enddate)
 GROUP BY dtr.startdate, dtr.enddate;

(Again, syntax may need adjusting for MySQL). Note that this does not sum up ages for any dates either before the first date, or on or after the last date, as those are not part of valid ranges.

Hope this helps point you in the right direction.


`Invoke-SqlCmd` does not display RAISERROR messages where `sqlcmd` does

According to the documentation for Invoke-Sqlcmd, you can use -verbose to display the output messages.  From that post:
Example 5: Run a query and display verbose output
PS C:\> Set-Location "SQLSERVER:\SQL\MyComputer\MainInstance"
PS C:\> Invoke-SqlCmd -Query "PRINT N'abc'" -Verbose
VERBOSE: abc

I have bat file processes that use this technique and pipe the output of the messages to a file.  Example:
-Verbose 4>&1> 'C:\AperioSync\SyncOutput.txt'"


Can I control which algorithm SQL Server uses to hash symmetric key passphrases

For instance, can I tell SQL to use sha2_256 for example when encrypting my certificate?

Unfortunately this is currently not possible or exposed via the CREATE CERTIFICATE DDL. From the documentation you can see:

 ... SQL Server 2017 uses the SHA2 hashing algorithm to hash the passphrase.
SQL Server 2016 and earlier versions of SQL Server use the SHA1 algorithm ...

  And the suggested fix is to ensure you have CU2 installed and to globally enable trace-flag #4631

That is correct, in fact I put in the request for that TF to be made available.

  Perhaps it is possible to do something like: decrypting all of the data, re-creating the certificate and then re-encrypting everything. - As we are sceptical about relying on trace-flags

You could do a backup and restore from 2016 to 2017, which we tested and worked without issue (for the tests we ran) instead of migrating the symmetric key. You could also decrypt everything in 2016 as you say, load it into 2017 and create a new key in 2017 but that would be a bit over the top to not use a trace flag for was is literally less than a second of wall clock time.

  Also, do you have any more information about TF4631, I have been unable to locate any official documentation from Microsoft which describes this flag / what it does / how it behaves

The KB article you linked has the most information, I did write a blog post about it before CU2 came out (note that I haven't updated it since CU2 did come out so the Azure part may be incorrect as of today). In general, though, it doesn't go into the details of what the TF does. The TF changes the use to the older hashing algorithm (2012-2016 implementation) while it is enabled. That's why all you need to do is enable it, create the key, then immediately disable it.


How to handle large database updates with node js

Please provide proof that 500k rows is a lot for your database.

Hint In most cases,  it isn't.

Although this answer could befit a Comment, this is a valid Answer.

Databases deal with MILLIONS of rows at a time without a hiccup.  500k is nothing.

Things you need to be aware of when testing the single DML approach

Undo tablespace (or MySQL's equivalent)
possibly temp tablespace (for indexes)
locks on a heavily modified table.

If you have proof that one (or more) of those is causing issues, then you should consider Chunking your UPDATE statement.

You haven't given any indication that any of those is a problem.  So, the answer is:  Neither.  Use a single DML.

As far as "process on separate server"...I don't see the need.  I foresee too many headaches with synchronizing the processed rows with any modified data that occured during the Processing.


Creating and editing my.cnf by MySQL Workbench?

You many need to take a look at the comprehensive list of all Server Variables. Some of them you can change for the duration of your session, some you may change with the SET GLOBAL command, and other options may require a restart of MySQL.

Here is the list of all options. This will also show which options can be used in option files.


Oracle Database Security - Audit Trail for specific user and specific module application

You can do this with Unified Auditing, which is available starting with version 12.1.

Example:

create user u1 identified by u1 quota unlimited on users;
grant create session, create table to u1;
grant audit_viewer to u1;

Now create a Unified Audit policy, that audits all actions when the user is U1, and module is not Golden6:

create audit policy mypolicy1
actions all
when 'sys_context(''userenv'', ''current_user'') = ''U1'' and sys_context(''userenv'', ''module'') != ''Golden6'''
evaluate per statement
;

SQL> audit policy mypolicy1;

Audit succeeded.

Then test it:

SQL> conn u1/u1
Connected.

SQL> select sys_context('userenv', 'current_user') as current_user, sys_context('userenv', 'module') as module from dual;

CURRENT_USER         MODULE
-------------------- --------------------
U1                   SQL*Plus

SQL> create table t1 (c1 number);

Table created.

SQL> exec dbms_application_info.set_module('Golden6', null);

PL/SQL procedure successfully completed.

SQL> create table t2 (c1 number);

Table created.

SQL> exec dbms_application_info.set_module('SQL*Plus', null);

PL/SQL procedure successfully completed.

SQL> create table t3 (c1 number);

Table created.

Now check the contents of the audit trail:

SQL> select event_timestamp, DBUSERNAME, ACTION_NAME, sql_text, RETURN_CODE from unified_audit_trail where dbusername = 'U1' and sql_text like 'create%' order by event_timestamp;

EVENT_TIMESTAMP                DBUSERNAME ACTION_NAME          SQL_TEXT                       RETURN_CODE
------------------------------ ---------- -------------------- ------------------------------ -----------
17-APR-18 02.04.29.381770 PM   U1         CREATE TABLE         create table t1 (c1 number)              0
17-APR-18 02.04.59.233856 PM   U1         CREATE TABLE         create table t3 (c1 number)              0

As you can see, the creation of table t2 statement, that was issued with the module set to Golden6, was not audited.


SQL Server 2014 In Memory OLTP - Garbage Collection on Transaction Rollback

My understanding from the SIGMOD paper Hekaton: SQL Server’s Memory-Optimized OLTP Engine is that it gets handled the same as other types of garbage.
A couple of relevant sections are

6.2.3 Transaction Rollback
Transactions can be rolled back at user request or due to failures in commit processing. Rollback is achieved
by invalidating all versions created by the transaction and clearing
the end-timestamp field of all versions deleted by the transaction. If
there are any other transactions dependent on the outcome of the
rolled-back transaction, they are so notified. Again the write-set of
the transaction is used to perform this operation very efficiently.

Note that it says invalidated not garbage collected.
Also see the Garbage Collection section (emphasis mine)

8.1.1 GC Correctness
First, care must be taken to identify which versions might be garbage. Potential garbage versions may be created
by one of two processes. First, a version becomes garbage if a) it
was deleted (via explicit DELETE or through an UPDATE operation) by a
committed transaction and b) the version cannot be read or otherwise
acted upon by any transaction in the system. A second, and less common
way for versions to become garbage is if they were created by a
transaction that subsequently rolls back. The first and most important
property of the GC is that it correctly determines which versions are
actually garbage. The visibility of a version is determined by its
begin and end timestamps. Any version whose end timestamp is less
than the current oldest active transaction in the system is not
visible to any transaction and can be safely discarded. A GC thread
periodically scans the global transaction map to determine the begin
timestamp of the oldest active transaction in the system. When the GC
process is notified that it should begin collection, transactions
committed or aborted since the last GC cycle are ordered by their end
timestamps.

But in practice it does seem the garbage from rollbacks is dispensed with quicker than for commits so perhaps the aborting thread does clean up its own garbage at least some of the time.
To see this I created the following proc (in a memory optimised database)
CREATE PROC GetStats
AS
SELECT *
FROM   sys.dm_db_xtp_table_memory_stats
WHERE  object_id = object_id('dbo.Test')

SELECT name AS 'index_name',
       s.*
FROM   sys.dm_db_xtp_hash_index_stats s
       JOIN sys.indexes i
         ON s.object_id = i.object_id
            AND s.index_id = i.index_id
WHERE  i.object_id = object_id('dbo.Test')

SELECT name AS 'index_name',
       s.index_id,
       scans_started,
       rows_returned,
       rows_expired,
       rows_expired_removed
FROM   sys.dm_db_xtp_index_stats s
       JOIN sys.indexes i
         ON s.object_id = i.object_id
            AND s.index_id = i.index_id
WHERE  object_id('dbo.Test') = s.object_id;

And then ran the following, experimenting with toggling the ROLLBACK and COMMIT
CREATE TABLE [dbo].[Test]
  (
     [Id]     [INT] NOT NULL CONSTRAINT PK PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 8),
     [String] CHAR(900) COLLATE Latin1_General_100_BIN2 NOT NULL INDEX IX NONCLUSTERED HASH WITH (BUCKET_COUNT = 8)
  ) WITH ( MEMORY_OPTIMIZED = ON )

INSERT INTO [dbo].[Test]
VALUES
    (1,REPLICATE('A',900)),
    (2,REPLICATE('A',900)),
    (3,REPLICATE('A',900)),
    (4,REPLICATE('A',900)),
    (5,REPLICATE('A',900)),
    (6,REPLICATE('A',900)),
    (7,REPLICATE('A',900)),
    (8,REPLICATE('A',900))

/*Get initial position*/
EXEC GetStats

BEGIN TRAN
    UPDATE [dbo].[Test]
    SET    [String] = REPLICATE('B', 900)

/*Mid Transaction*/
EXEC GetStats

COMMIT

/*Force a scan of both indexes*/
SELECT COUNT(*)
FROM   dbo.Test WITH (INDEX=IX)
EXCEPT
SELECT COUNT(*)
FROM   dbo.Test WITH (INDEX = PK)

EXEC GetStats

DROP TABLE [dbo].[Test]

I consistently got results summarized as follows.
The effect of the UPDATE was the same for both committed and rolled back transactions on memory_allocated_for_table_kb (Grew from 7KB to 15KB)
Bucket stats
The initial bucket stats for both tests was the same with
+------------+--------------------+--------------------+------------------+------------------+
| index_name | total_bucket_count | empty_bucket_count | avg_chain_length | max_chain_length |
+------------+--------------------+--------------------+------------------+------------------+
| IX         |                  8 |                  7 |                8 |                8 |
| PK         |                  8 |                  7 |                8 |                8 |
+------------+--------------------+--------------------+------------------+------------------+

Mid transaction (before rollback or commit) the bucket counts are
+------------+--------------------+--------------------+------------------+------------------+
| index_name | total_bucket_count | empty_bucket_count | avg_chain_length | max_chain_length |
+------------+--------------------+--------------------+------------------+------------------+
| IX         |                  8 |                  6 |                8 |                8 |
| PK         |                  8 |                  7 |               16 |               16 |
+------------+--------------------+--------------------+------------------+------------------+

Following the rollback they instantly revert to the initial count but after the commit they remain as in the second table showing that garbage is now present.
The single bucket that apparently all the PK values hash to now has 16 linked rows rather than the previous 8.
And now the hash index on the other column has two buckets in use with 8 linked rows in each (for both the "before" and "after" string values of AAA... and BBB...)
Rows Expired
After Commit
+------------+----------+---------------+---------------+--------------+----------------------+
| index_name | index_id | scans_started | rows_returned | rows_expired | rows_expired_removed |
+------------+----------+---------------+---------------+--------------+----------------------+
| IX         |        2 |             1 |             8 |            8 |                    0 |
| PK         |        3 |            10 |            16 |            8 |                    0 |
+------------+----------+---------------+---------------+--------------+----------------------+

After Rollback
+------------+----------+---------------+---------------+--------------+----------------------+
| index_name | index_id | scans_started | rows_returned | rows_expired | rows_expired_removed |
+------------+----------+---------------+---------------+--------------+----------------------+
| IX         |        2 |             1 |             8 |            8 |                    8 |
| PK         |        3 |            10 |            16 |            8 |                    8 |
+------------+----------+---------------+---------------+--------------+----------------------+

The rows_expired is 8 in both cases but rows_expired_removed is 0 following the transaction commit whereas all of them were removed after the rollback.


SQL Server Agent - Running SSIS Package with Proxy

I know that the post is old, but to make this work be sure that the user you set as Run As in the Job, has access to the proxy and is in the list of the proxy Principals. If the user is systemadmin in sql it will not get saved in the list and you will get the error.


How can one select a static value as a column based when using UNPIVOT?

You are missing a new column generated by unpivoting attributes

SELECT itemNumber, attributes AS [SourceName], Value As [SourceValue]
FROM
(
  SELECT itemNumber, type, code, description
  FROM myTable
) AS tb
UNPIVOT
(
  Value FOR attributes IN ( type, code, description)
) AS myValues


First, use EXPLAIN ANALYZE (not mere EXPLAIN) and show the result of \d on the table.
(psql). As a first point this,

ST_GeographyFromText('POINT(47.4667 8.3167)')

should be written as ST_MakePoint(47.4667, 8.3167)::geography

Your problem here is this pattern,

SELECT ST_Distance( ST_MakePoint(47.4667, 8.3167)::geography, geo_pt2) AS dist
...
ORDER  BY dist
LIMIT 100 OFFSET 1000;

Every time you do that, you must at least calculate the distance to 1100 rows. That said, it shouldn't be slow. It's slow because in order to that like that you're having to calculate the ST_Distance on all rows. We can stop that using KNN there using the <-> operator. MySQL doesn't support KNN.

SELECT hash_id, town
     , ST_Distance(ST_MakePoint(47.4667, 8.3167)::geography, geo_pt2) AS dist
FROM   member_profile
WHERE  ST_DWithin(ST_MakePoint(47.4667, 8.3167)::geography, geo_pt2, 250000)
ORDER  BY ST_MakePoint(47.4667, 8.3167)::geography <-> geo_pt2
OFFSET 1000
FETCH NEXT 100 ROWS ONLY;

As a style critique, I personally prefer OFFSET/FETCH (the standardized method limit/offset).

Pagination

I'm not sure this will work. but, it may be worth trying (keep us updated).

SELECT hash_id, town
     , ST_Distance(ST_MakePoint(47.4667, 8.3167)::geography, geo_pt2) AS dist
     , ST_MakePoint(47.4667, 8.3167)::geography <-> geo_pt2 AS myknn
FROM member_profile
WHERE ST_DWithin(ST_MakePoint(47.4667, 8.3167)::geography, geo_pt2, 250000)
  AND ST_MakePoint(47.4667, 8.3167)::geography <-> geo_pt2 > OLD_VALUE
ORDER BY ST_MakePoint(47.4667, 8.3167)::geography <-> geo_pt2
FETCH NEXT 100 ROWS ONLY;

So the first time you run this you save the value of last value of myknn then the second time you run this, you can replay that value back in this clause as OLD_VALUE,

AND ST_MakePoint(47.4667, 8.3167)::geography <-> geo_pt2 > OLD_VALUE

So each time you run it you're saving the new point from which to continue at, and using FETCH NEXT x ROWS ONLY.

myknn and dist may be the same for you, if so you can just remove one of them.


You need to take dump from Master to replicate.

  master has purged binary logs containing GTIDs that the slave
  requires.

Master delete its binary logs and create a new binary logs file. That's the reason when you implementing replication after taking dump is working because it's getting gtid_executed and gtid_purged that is available in the current binary log.

But when you are implementing without dump, The gtid_executed and gtid_purged getting matched because Mater already purged it.

So you must have to take dump and even sometimes you need to set the gtid_purged manually on slave.

You can read this, it has explained very well.
https://www.percona.com/blog/2013/02/08/how-to-createrestore-a-slave-using-gtid-replication-in-mysql-5-6/


What is the difference between NORECOVERY and RECOVERY when restoring database?

NORECOVERY specifies that roll back not occur. This allows roll forward to continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll them forward.
RECOVERY (the default) indicates that roll back should be performed after roll forward is completed for the current backup.
Recovering the database requires that the entire set of data being restored (the roll forward set) is consistent with the database. If the roll forward set has not been rolled forward far enough to be consistent with the database and RECOVERY is specified, the Database Engine issues an error.

SOURCE

Restore Sequence and Understanding NORECOVERY and RECOVERY


How to enable all the possible backup options of MySQL

Add the following to my.ini

[mysqld]
log-bin=mysql-bin

Then, restart mysql

As for mysqldump, most of the options you need are defaulted already in --opt. Here are my choice parameters:

mysqldump -h... -u... -p... --master-data=2 --routines --triggers --flush-privileges --all-databases > DataDump.sql

Here is an explanation for my choices (including default --opt)

--opt               Same as --add-drop-table, --add-locks, --create-options,
                    --quick, --extended-insert, --lock-tables, --set-charset,
                    and --disable-keys. Enabled by default, disable with
                      --skip-opt.
-R, --routines      Dump stored routines (functions and procedures).
--triggers          Dump triggers for each dumped table.
--flush-privileges  Emit a FLUSH PRIVILEGES statement after dumping the mysql
                    database.  This option should be used any time the dump
                    contains the mysql database and any other database that
                    depends on the data in the mysql database for proper
                    restore.
--master-data[=#]   This causes the binary log position and filename to be
                    appended to the output. If equal to 1, will print it as a
                    CHANGE MASTER command; if equal to 2, that command will
                    be prefixed with a comment symbol. This option will turn
                    --lock-all-tables on, unless --single-transaction is
                    specified too (in which case a global read lock is only
                    taken a short time at the beginning of the dump; don't
                    forget to read about --single-transaction below). In all
                    cases, any action on logs will happen at the exact moment
                    of the dump. Option automatically turns --lock-tables
                    off.

You will need --master-data if you want to setup the dump to prepare replication slaves. The master log and position at the time of the dump will be recorded as a comment (using --master-data=2) or as a command (using --master-data=1) on line 22 of the dump file.

UPDATE 2011-07-26 15:20 EDT

If you would like to dump separate databases and separate tables, please follow the instructions at this URL.


RMAN Recovery In Oracle 11g : Backup Database To Tape

The "device" in RMAN is a misnomer, it should be really called "storage".

The "sbt" (synonym of "sbt_tape") is a misnomer again, as it has NOTHING to do with any tapes, it should be simply called "non-rman". This is just an empty placeholder, to be filled with any "plugin"; the plugin is called by Oracle either the "Media Manager library" or SBT_LIBRARY. This plugin allows rman to store and retrieve files through it, so rman only tells that it needs a file handle "xyz" (file is identified by a string handle) and doesn't need to know how the file is delivered, from tape or anything. The plugin is normally a part of an independent backup software, such as IBM TSM or Symantec NetBackup or many others. Oracle provides a simple emulator SBT_LIBRARY=oracle.disksbt for testing.

Since you didn't fill that placeholder with any "Media Manager library", you receive an error message as expected.


The only issue seems to be the FILTER clause. You can  rewrite:

COUNT(*) FILTER ( WHERE tsrange(login_start, login_end, '[]')
                  && tsrange(time, time + '30 minutes'::interval, '[]')
                )

as:

COUNT(CASE WHEN tsrange(login_start, login_end, '[]')
                && tsrange(time, time + '30 minutes'::interval, '[]')
           THEN 1 END
     )

I would also use inclusive-exclusive intervals: [). Otherwise some rows will be counted twice.


First, you can try to monitor index usage.
You will take the answer used index or not used.
Index monitoring can be switched on/off several times to get more information.

For example, here: https://oracle-base.com/articles/9i/performance-enhancements-9i#IndexMonitoring

Also, you can look into v$sql_plan view to see what recent queries used this index:

select s.sql_id, s.sql_text
  from v$sql_plan p, v$sql s
 where p.sql_id = s.sql_id
 and object_name = 'YOUR_INDEX_NAME_HERE' ;

Also, and most importantly, you can make the index invisible for the optimizer.
That way you can clearly see if it affects performance of your queries.

Examples - https://oracle-base.com/articles/11g/invisible-indexes-11gr1


Why/when does the SQL Server query optimizer use EQ_ROWS?

My best guess is, the optimizer uses EQ_ROWS every time the predicate equals (hence the EQ) the value in the sampled histogram.


What you describe is more like "turn columns into rows". You can do this with UNION [ALL].

The difference between UNION and UNION ALL is, that UNION does not return duplicates, it acts like DISTINCT. If you know, there won't be duplicates, you might as well use UNION ALL and spare your server the work to detect any.

SELECT
d, 1 as h, h1 as 'value'
FROM t
UNION ALL
SELECT
d, 2 as h, h2 as 'value'
FROM t
UNION ALL
SELECT
d, 3 as h, h3 as 'value'
FROM t
# optionally you can add
ORDER BY d, h, value

see it working live in an sqlfiddle


Update code works in manual stored procedure but not when run as SQL Server Agent Job

I resolved the issue - but do not understand it.

The system has a View corresponding to each Table.  One of the Views was used in my FOR XML PATH view.

I substituted the no-permission Table for the View, and it works.

The View has Public SELECT permissions.  The Table has no named permissions.

I think the SQL Server Agent Job should be able to SELECT from the View because of the public permission, but it cannot.


could not save history to file "/var/lib/postgresql/.psql_history": No such file or directory

Yes, the PGSQL system user (usually postgres) has its home directory set to /var/lib/postgresql. I'm presuming you installed your database in a different location. Since that directory doesn't exist, the PSQL program cannot access that folder to create its history file. (PSQL is the interactive command line for PGSQL).

Honestly, it's not that big of a deal. That history file is just a list of commands that were executed inside the PSQL interface.

Since you're done installing, I doubt you'll ever see the message again. That is, unless you connect to PSQL using your own account and you continue to get a similar error message. In that case, your HOME variable might be wrong. You're not logged in to your system as postgres are you? If you are planning on using that user interactively (not advised at all), then you'll have to set its HOME directory to some valid folder.

Here are some reference solutions.


Oracle: Sum function "not a single-group group function error"

Unless I'm missing something, you just need to add GROUP BY ORDER_NUM, don't you?

SELECT ORDER_NUM, SUM(NUM_ORDERED * QUOTED_PRICE) AS TOTAL_AMOUNT
FROM
ORDER_LINE
GROUP BY ORDER_NUM
ORDER BY ORDER_NUM;


MySQL Trigger to Update a Row in a Separate Table via Joins if Only One Row is Updated

Triggers are procedural, so my inclination would be to perform the join and update as separate steps; this IMHO would make for simpler and more readable DML.This code should do what you want:

DELIMITER $$

CREATE TRIGGER owed
    AFTER UPDATE ON bookings
    FOR EACH ROW
  BEGIN
    DECLARE dept VARCHAR(20);
    DECLARE rate DECIMAL(3, 2);
    IF NEW.odemeter_end <> OLD.odemeter_end THEN
      SELECT fm.department, vt.price
        INTO dept, rate
        FROM faculty_members fm, vehicles v, vehicle_types vt
        WHERE NEW.idMember = fm.idMember
          AND NEW.idVehicle = v.idVehicle
          AND v.type = vt.type;

      UPDATE departments
        SET owed_to_tfbs = (((NEW.odemeter_end - NEW.odemeter_start) * rate)
                           + owed_to_tfbs - NEW.fuel_purchased)
          WHERE department = dept;
    END IF;
  END;
$$

DELIMITER ;

Note that I haven't tried to compile this, so I make no promises regarding syntax errors, but the semantics are sound. Also, I've used a WHERE-clause join instead of a JOIN-clause join, as I'm not sure you can use the latter to join on NEW.


As Phil Says I replaced hostname with Actual IP but the connection prblem was for

(PROGRAM = extproc)

In listener.ora file, Removing it solved the issue.

To let external procedures work these changes should be done:

Adding new SID entry in SID_LIST_LISTENER SID_LIST with SID_NAME = SID in tnsnames.ora at EXTPROC_CONNECTION_DATA and PROGRAM = extproc and a proper ORACLE_HOME(Environment variables can be added)

In ADDRESS_LIST in LISTENER part of listener.ora new ADDRESS Entry should be added with PROTOCOL = ipc and KEY = EXTPROC


Most likely the problem is that testuser does not have the VIEW SERVER STATE server-level permission. That permission (assigned to the Login in the [master] database, not to the User in the database with the DDL Trigger) is required to access the sys.dm_exec_connections DMV. So you need to do one of the following:

GRANT the testuser Login the VIEW SERVER STATE server-level permission, or

Create a Certificate-based Login that is then assigned the VIEW SERVER STATE server-level permission, and then sign the DDL Trigger with that Certificate using ADD SIGNATURE. The steps for this are provided in the follower answer:
What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

From a security stand-point, I would recommend doing the little bit of extra work to handle the Certificate-based Login and signing the Trigger. This approach doesn't grant any actual Login / User any extra permissions, and will apply to any User that has the proper DDL permissions to perform the actions that would cause the DDL Trigger to fire. Also, assuming that testuser is merely a test case and that these permissions will need to apply more broadly in the final system, then you probably don't want to grant VIEW SERVER STATE to all of these Users or even to a Role.
Along these same lines, I thought that Ownership Chaining would take care of the INSERT permission on DBChangesLogs such that the permission did not have to be explicitly granted to any User(s). If this is not the case, then rather than granting this permission to one or more Users -- or certainly better than adding any User to the db_datawriter Role that grants access (and more than just INSERT) to all tables -- you can simply do two additional, simple steps beyond what is already outlined in that other answer to grant this Trigger the ability to insert into the DBChangesLogs table:

(start with the steps outlined in the "What minimum permissions..." answer linked above, the last part of it being to create the Certificate in the database where the DDL Trigger exists)
Create a User from the Certificate-based Login created in those prior steps (or from this same Certificate that is now also in this database -- the User created will be the same in either case)
GRANT this User the INSERT permission on the DBChangesLogs table

CREATE USER [MrDoStuff] FROM LOGIN [MrDoStuff];
GRANT INSERT ON dbo.DBChangesLogs TO [MrDoStuff];

PS: you declare and set the @ip variable. I assume you will use it once this permission issue is settled?


You really don't need partitioning for this to work efficiently, which is what I told you on your last question as well...even if you have billions of rows.

If you cluster on the BarcodeID (which I am assuming is unique) and put a nonclustered index on LoyaltyCardID it should work just fine.  These are NOT complicatd queries with a lot of additional logic from the sounds of things, and simple seek operations are extremely efficient on their own.

Are you getting pressure to partition or is it just something that you have decided to do?


how do i update mulitple rows with dynamic data?

UPDATE item
SET Qty = Qty -
(SELECT sum(Bask_Qty) FROM user_basket
 Where item.item_ID = user_basket.item_ID and user_basket.user_ID = 1);

This would update all items, though you may not notice it ( minus zero! ).
Better add your
WHERE item_ID IN (SELECT item_ID FROM user_basket WHERE user_ID = 1);


SQL Server 2012 Very Slow to Consume Memory

My first question where are you looking for SQL Server memory consumption. Please don't use task manager or process monitor, these do not show correct value when SQL Server service account has Locked pages in memory privilege.  just use below query to see memory consumed by SQL Server

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

There was notable change in SQL Server memory code in SQL Server 2012 but no where its related to what you are seeing. SQL Server memory allocation is dynamic in nature when SQL Server boots up it would grow its memory notification till the highMemoryResourceNotification resource notification is revoked.SQL Server will keep allocating memory based on its need as long as there is memory available I.e. as long as MEMPHYSICAL_HIGH (HighMemoryResourceNotification )notification is signaled in widows  and will scale down its usage when there is MEMPHYSICAL_LOW (LowMemoryResourceNotification) signaled in windows. When available memory is between the low memory and high memory SQL Server will try to maintain the memory usage stable( RESOURCE_MEM_STEADY) with some exceptions.

With 48G memory you have set max server memory to 44G and only given 4 G to OS. IMO this is not correct you must give at least 6 G OS. You should give enough memory to OS so that it would have enough to allocate otherwise it would start signaling low memory notifications and SQL Server would have to trim its memory consumption.

Does SQL Server service account has Locked pages in memory privilege. If not you should give it, but before doing that please leave 6-7 G for OS( I am assuming here that this is dedicated system for SQL Server)

If you want SQL server to consume more memory create a dummy table and then start inserting large number of records in it at same time start running multiple Select * statements you would see SQL Server memory consumption rising. If you want to check SQL Server memory consumed by SQL Server use query posted above


What is the max network latency between synchronous-commit AlwaysOn Availability Groups

There is no maximum for the supported latency for synchronous replicas.  You will sometimes opt for a higher-latency deployment in return for a RPO=0 for a broader range of failures.

The most common mission-critical configuration is to have a pair of synchronous replicas on separate racks in the same datacenter, and a third, asynchronous replica in a separate datacenter.

But guidance for your scenario should probably come from SAP, or from the Microsoft/SAP Engineering Alliance.  See eg https://techcommunity.microsoft.com/t5/running-sap-applications-on-the/bg-p/SAPApplications/label-name/AlwaysOn


If I change the database to online status and created a mapped user to
  the database, would that be okay to bring it back to a standby again
  and continue the restoring process without hassle?

No, that will break log shipping.

Unfortunately, until SQL Server 2014 you don't have much for other options.

  Grant access via stored procedures or views in another database. This
  will require enabling cross database ownership chaining, which can be
  a treacherous security road. And a lot of things could go wrong over
  time as tables are added, modified, and dropped.

  Create the login on the log shipping primary instance with the
  associated database user, and disable the login on the log shipping
  primary instance. If you’re using SQL authentication, you may have to
  use a special script to transfer the SID to the log shipping secondary
  to get it all to work.

In 2014+, you can use CONNECT ANY DATABASE and SELECT ALL USER SECURABLES IN SQL SERVER to grant read only access.


I've worked on a project where all files where stored inside the database (SQL Server). The decision was made because the system used 2 machines with Mirroring for High Availability and saving the files to the file disk would be troublesome to handle.

The engineer that decided this at the time didn't know how easy, fast and cheap is to store files in the Cloud and we end up with a 11 GB database where 10 GB consists only of files. It was expensive to save backups and the db restore process (for testing or disaster recovery) was much slower than needed.

Using MongoDB GridFS is quite easy and you can start by following the documentation here or with this tutorial, but I would not recommend it. There are many discussions of pros and cons of using the DB to store files and you may agree with me after reading these links:

Storing Images in DB - Yea or Nay?
Storing a file in a database as opposed to the file system?
What are the pros and cons of storing files in a database?

The two major pros are:

Performance (that is critical for your use case)
Cost (DB storage is much more expensive than Cloud storage)

If you store the image in S3, you will end up with a link similar to this:
https://s3-sa-east-1.amazonaws.com/mybucketname/myimage.png

This is a very lightweight info that you can store in your Mongo document. To retrieve this file, you have two common options:

If its a file URL that's open to the world, you can give the URL to the client browser and make the Angular app to download it. If you use Amazon CloudFront (CDN service), the low latency server will be a real boost for your user.
If the file is private, you can use your Node.js app to download it and send a copy to your user.


Does SentryOne Plan Explorer count the reads in UDFs?

The biggest problems we have here are:

Like @JNK says, SQL Server obfuscates away the use of a UDF, and does terrible things with them anyway (like always estimates one row). When you generate an actual plan in SSMS, you don't see its use at all either. Plan Explorer subject to the same limitations because it can only provide the information about a plan that SQL Server provides.
The code relies on different sources for runtime metrics when generating an actual plan. Unfortunately the plan XML does not include function calls, and SQL Server doesn't reveal the I/O incurred by a function when using SET STATISTICS IO ON; either (this is how the Table I/O tab gets populated).

Consider the following view and function against AdventureWorks2012. This is just a silly attempt at returning a random row from the detail table given a random row from the header table - mostly to make sure we generate as much I/O as possible, every time.

CREATE VIEW dbo.myview
WITH SCHEMABINDING
AS
  SELECT TOP (100000) rowguid, SalesOrderID, n = NEWID()
    FROM Sales.SalesOrderDetail ORDER BY NEWID();
GO

CREATE FUNCTION dbo.whatever(@SalesOrderID INT)
RETURNS UNIQUEIDENTIFIER
WITH SCHEMABINDING
AS
BEGIN
  RETURN
  (
    SELECT TOP (1) rowguid FROM dbo.myview
     WHERE SalesOrderID = @SalesOrderID ORDER BY n
  );
END
GO

What Management Studio Does (and Does Not) Tell You

Take the following query in SSMS:

SET STATISTICS IO ON;

  SELECT TOP (5) SalesOrderID, dbo.whatever(SalesOrderID)
    FROM Sales.SalesOrderHeader ORDER BY NEWID();

SET STATISTICS IO OFF;

When you estimate a plan, you get a plan for the query and a single plan for the function (not 5, as you might hope):

You don't get any I/O data at all, obviously, since the query wasn't actually executed. Now, generate an actual plan. You get the 5 rows you expected in the results grid, the following plan (which makes absolutely no visible mention of the UDF, except in the XML you can find it as part of the query text and as part of the Scalar Operator):

And the following STATISTICS IO output (which makes absolutely no mention of Sales.SalesOrderDetail, even though we know it had to read from that table):

  Table 'SalesOrderHeader'. Scan count 1, logical reads 57, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

What Plan Explorer Tells You

When PE generates an estimated plan for the same query, it knows about the same thing as SSMS. However it does show things in a slightly more intuitive way. For example, the estimated plan for the outer query shows how the output of the function is combined with the output of the query, and it is immediately clear - within a single plan diagram - that there is I/O from both tables:

It also shows the function's plan by itself, which I'm only including for completeness:

Now, let's take a look at an actual plan, which is thousands of times more useful. The downside here is, again, it only has the information that SQL Server decides to show, so it can only expose the graphical plan diagram(s) that SQL Server provides. This isn't a situation where someone decided not to show you something useful; it just doesn't know anything about it based on the plan XML that is provided. In this case, it is just like in SSMS, you can only see the outer query's plan, and it's as if the function isn't being called at all:

The Table I/O tab, too, still relies on the output of STATISTICS IO, which also ignores any of the activity performed in the function call:

However, PE gets the entire call stack for you. I have occasionally heard people ask, "Pffft, when will I ever need the call stack?" Well, you can actually break down the time spent, CPU used, and number of reads (and, for TVFs, number of rows produced) for every single function call:

Unfortunately, you don't have the ability to correlate that back to which table(s) the I/O is coming from (again, because SQL Server doesn't provide that information), and it isn't labeled with the UDF name (because it's captured as an ad hoc statement, not the function call itself). But what it does allow you to see, that Management Studio doesn't, is what a dog your UDF is being. You still have to join some dots, but there are fewer dots and they're closer together.

About Profiler

Finally, I would strongly recommend staying away from Profiler, unless it is to set up a server-side trace that you are going to script and then run outside the scope of any UI tool. Using Profiler against a production system is almost certainly going to cause more problems than it will ever solve. If you want to get this information, please use a server-side trace or extended events, and be sure to filter very wisely. Even without profiler, a trace can impact your server, and retrieving showplans through extended events is not the most efficient thing in the world either.


Why some files in data directory of a Postgresql cluster are symlink to files of another cluster?

This is the result of a pg_upgrade with the -k option, and these are not symbolic links, but hard links.
If you didn't manage to start the old cluster after pg_upgrade everything is fine. You just forgot to remove the old cluster after upgrading. Do that right now:
rm -rf /var/lib/postgresql/9.6/main

If you use -k, the upgrade process doesn't copy, but link the files. That makes the upgrade much faster, but renders the old cluster unusable.


IntelliSense alternative using SSMS 2012 in a contained database as a contained user?

I really don't think this is a problem with Management Studio, just that the user the tool has to act on behalf of does not have the permission to pull the metadata required to support IntelliSense. So I doubt other tools will fill the need. Your choices are:

Try other tools like SQL Prompt and SQL Complete
Use a server-level login with adequate DB privileges for development (vs. application runtime)
Live without IntelliSense

Hopefully they will fix this problem at some point. Even my Connect item incorrectly blames the UI when the problem is with the permissions (not) granted to the contained user.


There is no answer to that - it depends. If you are a smaller outfit that with locality (like a dozen shops in the USA) then all you really care about is the price in USD. If you are an international retailer with websites in many countries with local pricing, you need to keep likely even multiple prices per product in different currencies.

  Let's say I have Product table, this product has a price, but what currency
  should the price be in?

It should not be there. it assumes not only one currency (which wold be the base currency the system runs in) but also no rebate system at all. Pricing, except for really simplistic systems, is a separate table.

Suggested reading: Data Model REssouce Book, Volume 1 - whole chapter on storing prices.

If yo ustore it like this, it is either base currency (stored somewhere central) or yes, you add a currency id field to reference the currency. I suggest using the ISO currency code for that, and adding negative numbers if needed when no official code is assigned.


querying a linked PostgreSQL database in SQL Server 2008

If you are OK with the reports being run directly off of the Progress database, look into SQL Server Reporting Services and the Report Builder 2.0 in SQL Server 2008 R2.  If you can deploy Reporting Services 2012 (part of SQL Server 2012) and you've got SharePoint 2010 installed look into using PowerView so allow people to build reports off of data sets which you've pre-defined as part of a data model.

If you need to get the data into SQL Server then you'll probably want to look at building a SQL Server Integration Services package which would then copy the data from the needed tables into the reporting database on a set schedule.


Is it possible to convert a .mysql file to a .sql file?

For the records, I finally managed to load my dump into Acquia Dev Desktop after renaming the file to .sql and specifying/using exactly the same database name as the original database name.

I don't know what caused the original issue or what solved it, since I don't believe the database name should be an issue at all.


Finally! 6 - 8 hours of work later:

The problem in my case was that the slave server has 2 network interfaces and the usable/public IP address is assigned to the second interface. The first, which I assume that MySQL/OpenSSL is using as a default, does not have an IP address.

I solved by adding MASTER_BIND to my CHANGE_MASTER statement.


Does MySQL have a version of Change Data Capture?

LinkedIn has open-sourced its CDC tool, Databus, which supports MySQL. You can read about how to support MySQL on the wiki of the project: Databus for MySQL and read the blog statement here: Open sourcing Databus: LinkedIn's low latency change data capture system

However, you should note that there may be some limitations:

  The current implementation although fully functional should be considered a proof-of-concept. Its main goal is to demonstrate how to write a MySQL fetcher for Databus. The basic functionality of the fetcher has been tested but has not been deployed in our production environment. At LinkedIn, we run a slightly different implementation which relies on a custom-patched MySQL.

Another open-source CDC tool is FlexCDC (implemented in PHP) which was written as a part of the FlexViews project.

  [...] FlexCDC which will aim to provide a flexible CDC layer for MySQL based on RBR logging, and Flexviews, which uses FlexCDC to collect the changes necessary to incrementally maintain materialized views.

  From: Why are there no (other) change data capture utilities for MySQL 5.1?


What are static tables & dynamic tables?

Static tables are the master tables that are populated with some
  canned data at the time of creation of the database in a typical
  system setup. Rather they have a pre defined set of data populated in
  them that hardly changes. There is no specific term like Static table
  and Dynamic table, but all tables that are used for inserts, updates
  and deletes with data getting modified can be termed as dynamic tables

Source: https://www.sqlservercentral.com/forums/topic/static-and-dynamic-table#post-663097


Personally, I would have friends be a one-way association; this would double the length of your friends table but eliminate many places where you would need to duplicate logic depending on whether the current user was listed on the left or the right of each entry.

+------+------+
| uid1 | uid2 |
+------+------+
|    1 |    2 |
|    2 |    1 |
+------+------+

Given a choice between saving RAM/hard drive space, and simpler business logic, the choice should almost always fall to simpler rather than smaller. I suspect that most use-cases will have this performing better as well.


Is Using Execute Immediate Bad Practice in Oracle?

Well, it may seem kludgy, but compared to using DBMS_SQL, it's downright elegant.


How to specify with a parameter a field to extract from a RECORD variable

Just do

        RAISE NOTICE 'specified field: %', input_field_name;

It's already a variable at the top. That will work if you want the column/field name. If you want the column's value you'll need to use something that provides a dynamic accessor to a row type. For that, you need.

jsonb (as mentioned in the comments by Abelisto)
hstore

That said, This whole thing is a really bad idea. When possible, you should just create a function that processes a scalar and run it over the result set.

CREATE FUNCTION f(a text)
RETURNS text
AS $$
  SELECT 'foo' + a;
$$ LANGUAGE sql;

SELECT f(x) FROM t;

Rather than doing something that's more complex and statically types the table name burried within.

CREATE FUNCTION fThatOpensACursor(col text)
RETURNS SETOF text
AS $$
  SELECT to_jsonb(t)->>col
  FROM statically_typed_table AS t;
$$ LANGUAGE sql;

SELECT fThatOpensACursor('t.myCol');


(By support I'm assuming that you are talking about software assurance.)

SA gets you two basic things.

Phone Support
Ability to upgrade

It also gets you some other stuff.

If you have a cluster and you have an Enterprise Agreement then you get something called license mobility which allows you to have a passive node for free.  If you aren't clustered but are running within a VM license mobility gives you the ability to move the VM from host to host without issue.  If you don't have license mobility then you can only fail over the cluster (or move the SQL VM from host to host) once every 90 days.

Given that Microsoft is moving to a short release cycle (18-24 months give or take) having SA makes sense if you plan on upgrading any time soon after the new version comes out.

Now if you don't care about that stuff and you ditch SA you can still call in and get support for SQL Server, but keep in mind that you'll be the last one in the queue to get a call back, and at the lowest priority.  Now if you never call support that's fine, but when you do having SA in place pushes you up the chain from level 1 support to starting at level 2 support.

Now given that you are on SQL 2008 R2 today if you ever plan on upgrading to SQL 2012 or SQL 2014 if you don't have software assurance you have to purchase all new licenses to upgrade using the new licensing model.  If you have software assurance you get to upgrade for free.  So if you are using SQL 2008 R2 standard edition CPU licensing today a SQL 2014 upgrade might be really expensive all of a sudden due to the core licensing change which started in SQL Server 2012.


Postgres 10.0 upsert syntax error not sure what to google for

Remove table name in:

ON CONFLICT (hostname)

db<>fiddle here

Quoted from docs:

ON CONFLICT Clause

[ ON CONFLICT [ conflict_target ] conflict_action ]

  conflict_target can perform unique index inference. When performing inference, it consists of one or more index_column_name columns and/or index_expression expressions, and an optional index_predicate. All table_name unique indexes that, without regard to order, contain exactly the conflict_target-specified columns/expressions are inferred (chosen) as arbiter indexes.


Nonclustered index is faster than clustered index?

Yes the clustered index has fewer rows per page than the non clustered index as the leaf pages of the clustered index must store the values for the other two columns (FirstName and LastName).

The leaf pages of the NCI store only the BusinessEntityId values and a row locator (RID if the table is a heap or the CI key otherwise).

So the estimated costs reflect the greater number of reads and IO requirement.

If you were to declare the NCI as

nonclustered index on Persontb_NC(BusinessEntityId) INCLUDE (FirstName, LastName)

then it would be similar to the clustered index.


Try joining to a subquery:

SELECT e.*, m.datetime AS unread_last, m.id AS unread
  FROM TAB_EVENT e
  LEFT JOIN (
    SELECT event_id, MAX(datetime) AS `datetime`, COUNT(id) AS id
    FROM TAB_MESSAGE
    WHERE read IS NULL OR read = 0
    GROUP BY event_id
  ) m ON e.id=m.event_id
  ORDER BY m.datetime DESC, e.id ASC
  LIMIT 10;


SQL Server 2016 ColumnStore Clustered Index - Query plan showing a scan, performance not great

I have a columnstore clustered index on the table and no other indexes ... The query plan shows a scan

Well, pretty much that is the only option available for a CCI. With CCIs the performance gain come from column elimination, compression and segment elimination, all contributing to reduce IO and thus allow scans to perform fast. For your case I would look at two things:

check segment health: sys.column_store_row_groups. For 1.1B rows you should have ~1000 rowgroups, all closed and compressed.
check that you loaded the data in a manner that allows segment elimination. Read Understanding Segment Elimination. Given your date range filter your query should scan only 1 (one) of those 1000 segments, and skip the other 999. If this doesn't happen, it means you loaded the data in a manner that prevents segment elimination. Read Columnstore indexes - data loading guidance.

Now, your query:

Select *
From dbo.PanelWorkflow
Where ReadTime Between '4/1/2016' And '4/5/2016' And Lineage = 'PBG11A' And ProcessNumber = 5400

This is not what columnstores and big tables are about. You are selecting all columns that satisfy a date range and some other criteria.  What for? Analytical data is all about computing aggregates and making analysis. Don't ask for *. Compute relevant analysis on the server side. Redesign your app in a manner that does not require displaying pages upon pages of raw data.


How do I calculate the average weight of riders by competition?

You need to join the rider and participants tables together using the common column (name->rider), group all of the participants for a given competition together, and calculate the average, like so:

select competition, avg(weight) as average_weight
from participants
join rider on rider.name = participants.rider
group by competition
;

Fiddle here.


To answer your questions:

Set the checkpoint_timeout lower, as you did, to 30 seconds or so. I made sure to enable log_checkpoints in my postgresql.conf and used check_postgres.pl to monitor the number of segments and replication lag.
You can also set checkpoint_completion_target=0 to adjust the checkpointing behavior as well. But really, on a replica, checkpoint_timeout is really the only way to keep the pg_xlog directory down in size on a replica.
Additional reference here: pgsql bugs #7801
Adjusting checkpoint_timeout is unfortunately the best option. There are some tips on dealing with pg_xlog filling up in this blog post: Solving pg_xlog out of disk space problems, but these mostly apply to a primary that's run out of space, and the panic related to that.
PostgreSQL WAL segments take up approximately this much room, according to the documentation for 9.1 WAL Configuration:

(2 + checkpoint_completion_target) * checkpoint_segments + 1 or checkpoint_segments + wal_keep_segments + 1 files

So if you do the math like this for example, with a checkpoint_completion_target=0.9 and checkpoint_segments = 50:

(2 + 0.9) * 50 + 1 = 146 * 16MB = 2336 MB

Which is only about 45% of your 5 GB of your pg_xlog filesystem. So long as you aren't getting checkpoint warnings in your logs, and the space isn't in danger of being completely used up, 5 GB should be fine. Using these formulas, you should be able to appropriately size your pg_xlog filesystem for your usage.

Greg Smith has a lovely chart in PostgreSQL 9.0 High Performance, referenced here: Server Configuration Tuning Practices. I would also recommend the book as a reference, even though it's a bit old, because a lot of the information in it is still quite applicable.

Hope that helps. =)


I had a similar problem with a box I was diagnosing for performance issues, it turned out that there was an incorrect / misplaced heading entry within the my.cnf for that server, so MySQL was ignoring all the settings within that section...

For example, if you had the following;

[mysqld]
######################################################
# First, the generic server configuration items
port                    = 3306
socket                  = /var/lib/mysql/mysql.sock
datadir                 = /mysql/data

[client]
#password               = your_password
port                    = 3306
socket                  = /var/lib/mysql/mysql.sock

key_buffer_size         = 256M
table_open_cache        = 1024
query_cache_size        = 32M

Then the buffer tuning section is ignored as it is within the client section and not the mysqld section... Re-arrange the sections, and all works as intended.

Hopefully, it is as simple as this for you!


Is there any way to get original query based on lsn

Is there any way or tool to get original query against the lsn.

No, there isn't a way to get the "original query text". I quote this, because the log records record what changes happen in that database. Since select statements, cte's, etc., do not generate* log records there is no way to get that information. Additionally, the log records describe the changes that have happened, of which a query could be generated to create the same changes again, reverse them, or show what was changed... however there is no way to find what the larger part - if any - it may play in the batch.

Let's take a quick and simple example:

Suppose we wanted to find all of the accounts using gift cards and give them an extra $1 if they made a purchase over $5 in the last 7 days. The log records would show the $1 increase to any of the accounts that met the above logic, but there is no place for us to see what that predicate was... we'd just see that some accounts (if any) were updated to have 1 added to a column (or whatever your model was).

Thus we could re-create the UPDATE of the individual record, but no way to figure out what the original query was, however we could look at the entire transaction and make a logically equal batch.

What if you wanted to do this in the future - to know all of the queries that have happened?

Native to SQL Server there are various ways to log all of the queries that happen. Additionally there are 3rd party services and applications that can log this as well.

SQL Audit
(deprecated) SQL Trace
Extended Events


Can I use Backup/Restore to clone a database? Running out of ideas

Yes, backup & restore is a perfectly reasonable way to copy a database.  You will need to use the MOVE option to re-name the files.  Here is an example using a backup of a database called DBA and restoring it to DBA_Test

RESTORE DATABASE [DBA_Test]
FROM  DISK = N'DBA_backup_2015_01_11_001025_4030475.bak'
WITH  FILE = 1,
MOVE N'DBA' TO N'D:\DATA\DBA_test.mdf',
MOVE N'DBA_log' TO N'E:\LOG\DBA_test_log.ldf',
NOUNLOAD,  STATS = 5

Kin's concern in the link mentioned by @JohnM is valid.  You will be creating an additional database on the instance that will be competing for resources (depending on it's usage) with your other databases.  However if you are doing this to create another production database that you need anyway this isn't really a big deal.

I'd only be concerned if you are using it to create a test environment on a production server.  In that case you need to decide if the risks of bringing down your production database while working with your test environment outweigh the costs of another server/instance.  For example if you are testing and blow up your test transaction log you will still cause problems for your production database.

If you need a cheap environment remember the Express is free and if you need a full blown copy the Developer license is usually only around $50-60.  Of course that license is a single person license only, not for production use, but it is equivalent in functionality to a full Enterprise license.


Postgres Search Path between connections

You should clean the state when you return a connection to the pool.

This is typically done with DISCARD ALL.

If all you need is to reset configuration parameters, you should use RESET ALL.


The resolution order is described in the documentation:

  Oracle Database attempts to qualify the first piece of the name
  referenced in the SQL statement. For example, in scott.emp, scott is
  the first piece. If there is only one piece, the one piece is
  considered the first piece.

  A) In the current schema, the database searches for an object whose name
  matches the first piece of the object name. If it does not find such
  an object, it continues with step b.

  B) The database searches for a public synonym that matches the first
  piece of the name. If it does not find one, it continues with step c.

  C) The database searches for a schema whose name matches the first piece
  of the object name. If it finds one, it returns to step b, now using
  the second piece of the name as the object to find in the qualified
  schema. If the second piece does not correspond to an object in the
  previously qualified schema or there is not a second piece, the
  database returns an error.

  If no schema is found in step c, the object cannot be qualified and
  the database returns an error.


You be able to delete it without issues. Why ?

Look for the file relay-log.index and display it

# cat relay-log.index

You will see a list of relay logs MySQL Replication needs and rotates. If relay.000724 is not in relay.index, you can delete relay.000724 without breaking anything.

Why is is still around ? It could be anything. Between setting up replication after an outrage, skipping SQL Errors, and just plain Starting and Stopping, along with a low expire_logs_days could leave one or more relay logs lingering.

In light of these things, if a relay log does not appear in relay.index, you can delete it.

Why is the relay log so empty ?

It has to do with the mechanism Replication uses to rotate to the next relay log. You will not see these annoying little files very often. The only time I usually see them is with one of the following:

when the SQL thread is broken
when the SQL thread is extremely busy with a long running query

In either case, the IO thread keeps collecting binlog events. Instead of triggering a rotation when the relay log reaches 1G or whatever max_binlog_size or max_relay_log_size is, two little relay logs with nothing in them mark the place where a rotation would have occurred.

UPDATE 2014-07-21 15:28 EDT

Since the relay-log.index both relay logs, there are one of two things you could do:

Leave it alone and let MySQL Replication rotate it away on its own
Reset all the relay logs back to relay-log.000001 with the following

Run STOP SLAVE;
Run SHOW SLAVE STATUS\G
Find Relay_Master_Log_File in the Status (say it is mysql-bin.001234)
Find Exec_Master_Log_File in the Status (say it is 123789456)
Run CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.001234',MASTER_LOG_POS=123789456;
Run START SLAVE;


PostgreSQL Bloat Warning: Vaccuum does not help

Vacuuming will not remove the existing bloat.  It will free up that space for future use.  Until that future use has happened, it will still look bloated.

To free it up now, you would have to do something drastic like VACUUM FULL, CLUSTER, or use some external tool like pg_repack.  That is a lot of churn to put your database through just to fix a problem that is not actually a problem.

I would just do as @KookieMonster said, and increase the threshold.  Then if things start to get worse, eventually you will start getting warnings again.  (Actually, I wouldn't run check_postgres in the first place, but if I were, then...see above.)


Hierarchical structure with limited number of records per user

The following requirement can be easily implemented with a constraint: any user can refer only 3 other users at max.

    CREATE TABLE users
    (
      id serial NOT NULL,
      reference_number SMALLINT NOT NULL,
      CONSTRAINT CHK_reference_number CHECK(reference_number BETWEEN 1 AND 3),
      CONSTRAINT UNQ_reference_number UNIQUE(id, reference_number),
      referred_by integer NOT NULL,
(snip)

Note that your design does not prevent cycles. We can easily enforce that via constraints as well. I can elaborate if you are interested.

Regarding the efficiency of finding qualifying descendants, we can add some redundant data and get much better speed. For example, E is a descendant of A, but not a direct one - B is between them. We can store the following rows:

Ancestor: A
AncestorLevel: 1
Descendant: B
DescendantLevel: 2

Ancestor: B
AncestorLevel: 2
Descendant: E
DescendantLevel: 3

Ancestor: A
AncestorLevel: 1
Descendant: E
DescendantLevel: 3

Once we have this redundant data, finding descendants is easy and fast - just one simple query without recursion. Naturally, with this approach we need substantially more storage.

Of course, with redundant data there is always the risk that it is inconsistent. We can use constraints to enforce the integrity of redundant data. This is complex but doable.


Can you run two WSFC instances on two servers where each node is the primary for a different availability group?

Yes, you can have more than one availability group on a cluster.  Each availability group is completely independent of the others, and can be failed over to any node in the cluster separately.  From Configure SQL Server 2012 AlwaysOn Availability Groups for SharePoint 2013:

  Because SQL Server 2012 can host multiple availability groups on a single server, you can configure AlwaysOn to fail over to SQL Server instances on different servers. This reduces the need to have idle high performance standby servers to handle the full load of the primary server, which is one of the many benefits of using availability groups.

The is of course relevant regardless of whether SharePoint is involved.

You will not need additional instances--just one default instance of SQL Server on each node.  And I think you may be confused about WSFC instances.  You simply set up the cluster, install SQL Server on each node, and then create the availability groups.  When you create the AGs, the setup process will create all of the cluster roles and resources that are required.

Search YouTube and watch one being set up and I think that will make things clearer (https://www.youtube.com/watch?v=VKCqRgqLAuo).  The documentation gets murky because it continually refers to WSFC instances and availability groups, but if you're using straight AGs, you aren't going to be using WSFC instances.  It makes it appear far more complex than it is.


Do it with a subquery,assuming unique values on id_product

SELECT id_product,date  FROM products
WHERE id_product IN
(SELECT MAX(id_product) as maxid FROM products WHERE
fk_stat = '1' and id_order = '77185')


Number of rows summed up by grouping them, each group has a different number of rows

You seem to need rolling sums over windows of 4 rows - so there need to be some correction for the first 3 rows of each group inside every partition:

with cte as
(   select
        RetailerID, Category, WeekId, value,
        row_number() over
            (partition by RetailerID, Category
             order by WeekId)
            as rn,
        count(*) over
            (partition by RetailerID, Category)
            as tc,  -- total count per partition
        sum(value) over
            (partition by RetailerID, Category
             order by WeekId
             rows between 3 preceding
                      and current row)
            as sv
    from temptable
    where Weekid between 1 and 12
      and RetailerID = 34
)
select
    RetailerID, Category, WeekId, value,
    case when rn <= floor(tc / 4) * 4
        then case (rn % 4)
                 when 1 then lead(sv, 3) over w
                 when 2 then lead(sv, 2) over w
                 when 3 then lead(sv, 1) over w
                 else sv
             end
        else sv
    end as sum_value
from
    cte
window w as
    (partition by RetailerID, Category order by WeekId) ;

Test at dbfiddle.uk.

Works in MySQL 8 and SQL Server (2012+).


What is the difference between NULL and \N while loading data from a csv using load data local infile

Depends on your options FIELDS and LINES used in the LOAD DATA statement.

MySQL documentation

From MySQL doc:

  Handling of NULL values varies according to the FIELDS and LINES
  options in use:

  For the default FIELDS and LINES values, NULL is written as a field
  value of \N for output, and a field value of \N is read as NULL for
  input (assuming that the ESCAPED BY character is “\”).

  If FIELDS ENCLOSED BY is not empty, a field containing the literal
  word NULL as its value is read as a NULL value. This differs from the
  word NULL enclosed within FIELDS ENCLOSED BY characters, which is read
  as the string 'NULL'.

  If FIELDS ESCAPED BY is empty, NULL is written as the word NULL.

  With fixed-row format (which is used when FIELDS TERMINATED BY and
  FIELDS ENCLOSED BY are both empty), NULL is written as an empty
  string. Note that this causes both NULL values and empty strings in
  the table to be indistinguishable when written to the file because
  both are written as empty strings. If you need to be able to tell the
  two apart when reading the file back in, you should not use fixed-row
  format.


SQL Server Transactional Replication delay

If the tracer token goes through in about 5 seconds and your updated data is taking 30 minutes, then the data has not been committed on the publisher yet. The tracer token would not have gone through if there was an un-replicated row ahead of it. Run a trace or an XE session on the publisher to see what is going on there.


Need help finding inactive users in my database

You can find the logins on an instance by querying sys.server_principals and checking the is_disabled column to know if they are still useable.

SELECT name, type_desc, is_disabled
FROM sys.server_principals
WHERE type IN ('U','S')

The other notes you have in your question though indicate that this request is based on an application table. You state things like Active and/or CanLogIn values set to 1 and associated by OrganizationID to an Organization. All of that would tell me this is custom tables within an application database. Which unless you provide the table definition(s) there is not much we can do to help.


Passing database name to input parameter from a stored procedure

Ok. This is solved. I had to enter as input parameter the value [WIN-9SEBDMDC9O9_HMI#B5N2_ALG_202012020924] instead of WIN-9SEBDMDC9O9_HMI#B5N2_ALG_202012020924
Also for anyone who might want the final and working code; Here it is !
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE WinToAlarms22
    -- Add the parameters for the stored procedure here
    @NomDBAlarms sysname
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SET XACT_ABORT ON

    IF DB_ID(@NomDBAlarms) IS NULL
    BEGIN
    RAISERROR('Nom de base de donnée invalide',16,1)
    RETURN
    END

    DECLARE @Sql NvarChar(max)

    -- Insert statements for procedure here
    SET @Sql = N'INSERT INTO Reporting.dbo.LiaisonAlarmes
    SELECT * FROM ['+@NomDBAlarms+'].dbo.AlgRtTextsFRA'

    Exec sp_executesql @Sql
    END
GO

Thank you for your support and have a nice day !


One of the of goals of a relational database is to eliminate double storage of data. If you can find the right User for the Lead through the Order then you can leave out the reference in one of the tables (they are always the same as you mentioned in a comment). Keeping it in both tables makes your application more complicated.


Select All Records From Table That Do Not Exist In Secondary Table

Using NOT EXISTS

Select DISTINCT
    bm.Location
    ,bm.[Beer (Upcoming Menu)]
FROM
    ___SampleRequired bm
WHERE NOT EXISTS
   (SELECT 1
    FROM ___Sample td
    WHERE LTRIM(RTRIM(bm.[Beer (Upcoming Menu)])) = LTRIM(RTRIM(td.Beer_name)))
AND bm.Type LIKE '%Mandate%'
AND bm.Location = 'StapleChase'

Also, Max Vernon has a nice answer comparing LEFT JOIN vs NOT EXIST It's also more common to see this in the wild versus the LEFT JOIN method where you also need to add table.key IS NULL in the WHERE clause. i.e td.Beer_name IS NULL

And, Martin Smith comparing NOT IN vs NOT EXIST.


Secondary replica database file location in Availability Group

If you try and open the database properties window on a secondary replica, SSMS will throw you an error, so my guess is that you're not actually connected to the secondary replia when you're checking these paths.
Run the following on both nodes:
select db_name(database_id), name, physical_name
from sys.master_files

This will show you the physical paths that SQL Server is using to access each file, and I would expect will show you different paths on each replica.


nulls on pg_stat_get_progress_info('VACUUM') columns on pg_exporter role

You are missing the required privileges.
To see all values in a line of pg_stat_progress_vacuum, one of the following must be satisfied:

you are a superuser

the VACUUM is run by the same user as your current connection

you are a member of the system role pg_read_all_stats


Forcing uncommitted read access via view?

Readers should not block readers, so if no one writes to the table I don't think using UR instead of CS will make much of a a difference. There is a small overhead I guess for applying and releasing the share lock, but my guess is that it will be negligible. I guess only way to find out for sure is to try and see if it affects throughput.

I was unaware that you could add WITH UR in a view, but I tried and according to the plan the isolation level for the table actually changes from CS to UR. I find it a bit weird to be honest that the isolation level is lowered for the statement, without notifying the caller.

Since you are on 10.5 you might want to give CURRENTLY COMMITED a try. Keep an eye open on usage of the log buffer.


What are the pitfalls when storing data retrieved from an api

Advice:

Normalize data stored.
Consider carefully and figure out your write model.  For orders, can you overwrite?  do you need to mark superceded and append?

The above are generic data entry pieces of advice, but regarding automated application to application interfaces, you have the issue of erroneous data entry.  To deal with this it is probably better to accept orders etc. but have the invoicing done by humans, who can cross check with the real world.  Also this keeps people in control of your books.  So I would add to that:

Separate automated entry from homan review and approval.