SQL Server Query For Getting data in Specific Format

Here is an example.

        Declare @PriceGroup TABLE
    (   id INT
        ,Catalog_Code INT
        ,Name VARCHAR(50)
    )

    INSERT INTO @PriceGroup (id,Catalog_Code,Name)
    VALUES (1,1,'PriceGroup1'),(2,1,'PriceGroup2'),(3,1,'PriceGroup3')

    Declare @Price TABLE
    (
        id INT
        ,articol_id INT
        ,catalog_code INT
        ,price_group_ID INT
        ,price DECIMAL(10,2)
    )

    INSERT INTO @Price (id,articol_id,catalog_code,price_group_ID,price)
    VALUES  (89,57,1,1,11.01)
            ,(90,57,1,2,11.01)
            ,(91,58,1,3,11.01)

    ;WITH PriceSource
    AS (
        SELECT
            articol_id
            ,Name
            ,price
        FROM
            @Price as P
            INNER JOIN @PriceGroup AS PG
            ON P.price_group_ID  = PG.id
            AND P.catalog_code = PG.Catalog_Code
    )

    SELECT *
    FROM PriceSource AS PS
    PIVOT
    (   MAX(Price)
        FOR Name IN ([PriceGroup1],[PriceGroup2],[PriceGroup3])
    )P

and the output:

articol_id  PriceGroup1 PriceGroup2 PriceGroup3
57  11.01   11.01   NULL
58  NULL    NULL    11.01


Why isn't the result of an OpenQuery refreshing?

The lastLogon attribute reflects the last interactive logon, not the last network-based logon

More info found here

This is some excellent information from this site

  Another twist: Active Directory does NOT replicate the LastLogon
  attribute across domain controllers. So in order to get an accurate
  value, you need to obtain the LastLogon value for the same user from
  all your domain controllers and accept the value that is highest.


How to start indexing a new data warehouse

Your plan of starting with clustered indexes on primary key is a great start.

Ideally, your clustered indexes (to start with) should be an INTEGER IDENTITY for the DW's surrogate keys to:

keep the clustered index as narrow as possible,
and so that the Dimension and Fact tables are not heaps.

If, and only if, you expect a table to surpass 2,147,483,647 rows (the max INT value), use BIGINT IDENTITY instead of INT. (See Numeric Data Types).

Carefully consider non-clustered indexes on EVERY foreign key. It might not be necessary, and may be more harm than good to put an NCI on EVERY foreign key. Instead, focus on creating non-clustered indexes only on Foreign Keys for which they would be used in JOINs or WHERE clauses in your queries. However, don't just create one Non-Clustered index for every FK -- if a query uses multiple FK's in joins, consider creating a single NCI to support those joins. Play with the order of the index's key columns to see which works best. TEST TEST TEST.

EDIT 9/26/2016
Here's a blog post by Kendra Little discussing indexes on Foreign Keys:
http://www.littlekendra.com/2016/09/22/indexing-foreign-keys-guidelines/

Heaps are bad for SELECT / UPDATE / DELETE performance (i.e. SSRS and SSAS queries), especially with any kind of JOIN or WHERE clauses in queries, because all queries on heaps would be Table Scans -- there's no such thing as a Heap Seek. Fragmentation is often a problem with HEAP tables.

Heaps are great for INSERT performance.
Heaps are generally known to be the fastest for INSERTing new data, because there would be no index changes (i.e. updates to an index's b-tree) or sorting of the data necessary that SQL Server would need to manage if there were indexes. This is most useful for STAGING tables. (See What are valid usage scenarios for heap tables). The degree to which HEAPS are better than CLUSTERED INDEXES while INSERTing new data will vary.

EDIT: 9/7/2016
Here's a blog post by Daniel Hutmacher discussing additional use cases for heaps:
https://sqlsunday.com/2016/09/01/compelling-case-for-heaps/

Be aware that clustered indexes (and non-clustered indexes) will reduce performance of your (assumed) daily nightly loads, because any data changes (INSERTs, UPDATEs, DELETES, MERGEs) will cause indexes that use those columns with changed data (I'll combine INSERTed, UPDATEd, DELETEd, MERGEd as "changed" data) to also be updated, which causes I/O writes to the indexes. It might be a small hit to performance; it might be a big hit to performance -- it depends on the size of the table and how much data is being changed (I/U/D/M), and other factors. A "small" dimension table would probably be fine to keep the Clustered Index and Non-Clustered indexes on for the ETL. A "large" dimension table might benefit from the disable/re-build method. TEST TEST TEST. (See Performance of Non Clustered Indexes on Heaps vs Clustered Indexes).

One method (not necessarily a recommendation, but an option) to combat the performance reduction for data changes of a (large) table with existing indexes is to:

disable or delete the indexes of table(s) as part of your SSIS ETL jobs prior to loading the data, in order to optimize INSERT performance,
then rebuild or re-create the index after data load is complete.

If you try this route, test and track performance to ensure this method is genuinely faster than loading the data to the table with indexes -- it might not be faster. TEST TEST TEST.

While performance tuning queries, take measurements using
SET STATISTICS IO, TIME ON; in front of your SELECT queries. Focus on reducing "logical reads" with your non-clustered indexes.
Document and track logical reads before the index and after the index; keep a history. If you are successful, you can show these numbers to your team and/or boss.

Your data warehouse will feed SSRS reports and SSAS cube processing runs. Create non-clustered indexes that support/tune these queries (maybe the queries that SSRS and SSAS use are views or stored procedures?). In other words, tune your SSRS/SSAS queries (via non-clustered indexes). You'll want to have your SSRS report views or procedures query code to be static/stabilized (i.e. no code changes) for some time (say, 1 week of steady executions) for the plan cache, and then proceed with tuning. You could do it sooner, but you might not get the same results.

For optimizing SSAS, use Profiler (or Extended Events, if you prefer) during your SSAS cube processing runs to capture the SELECT queries the cube uses when pulling data from your DW -- it might not be what you expect. These are the queries you tune for improving SSAS cube processing time. Don't forget to turn off profiler when done, as it does add some overhead.

The bonus of index tuning your DW, is that you are also tuning your SSRS reports and SSAS cube processing runs at the same time.

Index performance tuning is an on-going practice; you're never done. You might need to make changes to your index strategy if:

New tables get created / joined on
New where clauses or new joins to the views / stored procedures
New columns get added to existing tables
Data changes (i.e. now there's 1 million, or 10 million, or 100 million more rows than when you first index tuned)

Index performance tuning can be challenging and tricky, yet highly rewarding when you get a big performance win. I encourage you to search for free webinars that are dedicated to talking about index strategies. Learn about the different types of non-clustered indexes (i.e. "covering" indexes, filtered indexes, columnstore indexes, etc.) and how and when they are useful, and when they might do more harm than good.

When you're ready to step up above free webinars, consider finding books to buy, and/or paid training to further improve your indexing skills -- maybe your employer will pay for the training and/or books. Since you mentioned Brent Ozar team's sp_BlitzIndex, Kendra Little has done great in-person paid classes (which I've attended) and recorded webinars on index performance tuning. There are also plenty of other great MVPs and great SQL Pros that offer index training too.

Good luck, I hope this was helpful and insightful.


After upgrade from 9.1 to 9.3, commands still point to 9.1

Option 1: remove createdb for 9.1

When in doubt, use dpkg -S to learn which packages provide a certain command.

Example:

$ dpkg -S createdb
postgresql-doc-9.1: /usr/share/doc/postgresql-doc-9.1/html/tutorial-createdb.html
postgresql-doc-9.1: /usr/share/doc/postgresql-doc-9.1/html/app-createdb.html
postgresql-client-9.1: /usr/lib/postgresql/9.1/bin/createdb
postgresql-client-common: /usr/bin/createdb
postgresql-doc-9.1: /usr/share/doc/postgresql-doc-9.1/html/manage-ag-createdb.html
postgresql-client-9.1: /usr/share/postgresql/9.1/man/man1/createdb.1.gz
wwwconfig-common: /usr/share/wwwconfig-common/pgsql-createdb.sh

From this it appears that /usr/lib/postgresql/9.1/bin/createdb is provided by postgresql-client-9.1, and that it's still installed. You may uninstall it since presumably you have now postgresql-client-9.3

Option 2: address the right instance without removing anything

Debian allows multiple simultaneous PostgreSQL instances, which is why it allows to have different createdb at the same time. Assuming you'd need to keep postgresql-client-9.1, that is possible too.

The /usr/bin/createdb command, in the standard $PATH (as opposed to deep under /usr/lib), is actually a wrapper that will call the real binary for the correct version depending on the context.

That context may be given through the additional option, specific to Debian/Ubuntu, called --cluster to specify which version and server it should target. It's also available as an environment variable PGCLUSTER, as documented in pg_wrapper, and defaults can also be specified in /etc/postgresql-common/user_clusters.

Weird: createdb pointing to 9.1.14 and psql to 9.3.5

The one thing that doesn't look right in your question is this issue.
Normally pgsql and createdb should reach the same version/cluster, since in fact they should point to the same script to start with:

$ ls -l /usr/bin/createdb /usr/bin/psql
lrwxrwxrwx 1 root root 37 sept.  8  2012 /usr/bin/createdb -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root 37 sept.  8  2012 /usr/bin/psql -> ../share/postgresql-common/pg_wrapper

This would require further digging into the environment to find out how your situation differs from the default.


What is the MS_PerfDashboard schema in msdb?

You can find the Performance Dashboad here:
http://www.microsoft.com/en-us/download/details.aspx?id=22602

From memory there is a setup.sql file which should recreate the objects for you.


Query error: there is no matching unique or primary key constraint in the referenced table

The error message is clear, you are trying to incorporate a FOREIGN KEY on table comm_Customer that says that the CustomerID (which is it's primary key) is also related to table's comm_ShoppingCart CustomerID column. This would mean that every customer might have a shopping cart (but only 1), since CustomerID has to be a primary key (or unique) on the comm_ShoppingCart to correctly identify a unique row of that table with the foreign key relationship.

I believe the relationship is the other way arround, comm_ShoppingCart CustomerID should reference table's comm_Customer CustomerID which IS a primary key and won't display this error message.

If this is the case, you can add the FOREIGN KEY with an ALTER TABLE if it's already created:

ALTER TABLE comm_ShoppingCart
    ADD CONSTRAINT FK_comm_ShoppingCart_comm_Customer
    FOREIGN KEY (CustomerID)
    REFERENCES comm_Customer (CustomerID)

If you really want the other way around, you have to create a UNIQUE constraint (or index) on CustomerID column on the comm_ShoppingCart table:

ALTER TABLE comm_ShoppingCart
    ADD CONSTRAINT UNI_comm_ShoppingCart_CustomerID
    UNIQUE (CustomerID)

... which will fail if you have more than 1 cart for a particular customer.


From Wikipedia:

The DUAL table is a special one-row table present by default in all Oracle database installations. It is suitable for use in selecting a pseudocolumn such as SYSDATE or USER. The table has a single VARCHAR2(1) column called DUMMY that has a value of 'X'.

Thus, the dual table is a way to perform operations against what amounts to be an empty but not null table. This is useful when one doesn't care about the table, but needs to perform operations through a select statement. If the table had more than one row or column, multiple results would be returned (due to operating over the entire set of tuples when performing the operation.)
It shouldn't be used in production, unless you specifically need to invoke certain procedures through SQL.
4*5 is a mathematical operation, just as 'Foo'  is a string. Thus, just as one can select 4*5 from any table, just as one can select 'Foo' from any table, DUAL is a way of selecting it from a known-good table that will never have multiple results.
From the documentation (CONCEPTS):

DUAL is a small table in the data dictionary that Oracle Database and user-written programs can reference to guarantee a known result. The dual table is useful when a value must be returned only once, for example, the current date and time. All database users have access to DUAL.
The DUAL table has one column called DUMMY and one row containing the value X.

And the SQL Reference:

DUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in the schema of the user SYS but is accessible by the name DUAL to all users. It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value X. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once. Alternatively, you can select a constant, pseudocolumn, or expression from any table, but the value will be returned as many times as there are rows in the table. Refer to "About SQL Functions" for many examples of selecting a constant value from DUAL.
Beginning with Oracle Database 10g Release 1, logical I/O is not performed on the DUAL table when computing an expression that does not include the DUMMY column. This optimization is listed as FAST DUAL in the execution plan. If you SELECT the DUMMY column from DUAL, then this optimization does not take place and logical I/O occurs.


Some simple arithmetic:

100k users * 1m events per user per year * 100 bytes per event =~ 10 TB per year

In MySQL, single table is limited in size at about 4TB. I would think it should be bigger than that, but this is what official doc says.

In other words, you would be effectively forced to split or partition your huge table into set of smaller ones.

Alternatively, you can use NoSQL databases like Hadoop or MongoDB.
But, it can be painful if you are used to do everything in SQL.


I would re-write the whole proc something like this.....

ALTER PROCEDURE Member_WS
    @Program        int             = NULL,
    @Card           nvarchar(30)    = '',
    @Account        nvarchar(128)   ='',
    @Member         bigint          = NULL,
    @Email          nvarchar(256)   ='',
    @Phone          nvarchar(20)    ='',
    @ResponseType   nvarchar(100)   ='',
    @FirstName      nvarchar(100)   =''
AS
BEGIN
  SET NOCOUNT ON;
    DECLARE @sql nvarchar(max);

set @sql = N' SELECT * FROM TableName WHERE 1 = 1 ' --<-- for example your main query
         + CASE WHEN @Program IS NOT NULL
           THEN N' AND [Program].ProgramID =  @Program ' ELSE N' ' END
          + CASE WHEN @FirstName <> ''
           THEN N' AND [member].FirstName = @FirstName ' ELSE N' ' END
          + CASE WHEN  @Card <> ''
            THEN N' and [account].IIN + REPLICATE(0, (8 - len (LS001..[account].AccountNumber)))
                   + ltrim(str(LS001..[account].AccountNumber))
                   +  ltrim(str(LS001..[account].verificationDigitSufix))= @Card ' ELSE N' ' END
          + CASE WHEN @Account <> ''
            THEN N' and [Account].AccountID = @Account '  ELSE N' ' END
          + CASE WHEN @Member IS NOT NULL
            THEN N' and [member].MemberID = @Member' ELSE N' ' END
          + CASE WHEN @Email <> ''
            THEN N' and [user].email=  @email ' ELSE N' ' END
          + CASE WHEN @Phone <> ''
            THEN N' and [user].PhoneNumber = @Phone ' ELSE N' ' END

Exec sp_executesql @sql
                  ,N'@Program int , @Card nvarchar(30), @Account nvarchar(128)
                  ,@Member bigint , @Email nvarchar(256), @Phone nvarchar(20)
                  ,@FirstName nvarchar(100)'
                  ,@Program
                  ,@Card
                  ,@Account
                  ,@Member
                  ,@Email
                  ,@Phone
                  ,@FirstName

END


How can I join two tables of an Oracle database containing all rows (not the intersection)

I know that I should test my SQL script as well, but I don't have time to make a test before leaving for work!  I can make a SQL Fiddle later, but for now it seems that no one has quite answered the question correctly.

SELECT COALESCE(t1.cod, t2.cod) cod, t1.text1, t2.text2
FROM t1 FULL OUTER JOIN t2 ON t1.cod = t2.cod
ORDER BY cod;

If you want to test the performance, use the link  to the SQL Fiddle below!

SQL Fiddle

EDIT:

So, a commentor below asked why I used COALESCE.  It's because of the FULL OUTER JOIN.  In this case, you are asking the query to return rows WHERE t1.cod = t2.cod, but the FULL OUTER JOIN ensures that NULL entries are returned whenever there is no match.

In these cases, you can't know if a particular cod will be in table t1, table t2, or in both, so by using COALESCE you ensure that so long as a one of the tables has the field cod (which is guaranteed, since you did a FULL OUTER JOIN), this will be filled in.

WARNING: One of the alternative posted responses is an incorrect solution.

Applying that erroneous query

select t1.cod, t1.text1, t2.text2 from t1 full outer join t2 on t1.cod=t2.cod ORDER BY cod;

yields NULLs in the cod fields if the cod was only present in table t2.

See this SQL Fiddle for confirmation of the error.

However, applying my query with COALESCE will yield the desired results.

Use this SQL Fiddle, to confirm.


How to get a list of global temporary tables in the current database?

For SQL Server 2005+, all global temp tables (tables that are created with a ## prefix) will be found in tempdb.  This should work for you:

SELECT * FROM tempdb.sys.tables
WHERE name LIKE '##%'


MySQL Master Slave Replication and backup

Operations and issues on slaves will not impact the Master. I dont't understand your concept of "the master -> slave replication running every 5 minutes". All transactions on Master will immediatelty be replicate on slaves.

If your tables are InnoDB, your dump will not lock any tables (with the option --single-transaction) so you can have normal activity on master and slave too.

To backup your slave you can also stop the replication, shutdown your MySQL (Slave) and copy your datafiles or use a hot backup solution like Percona XtraBackup.

Max.


See https://www.sqlskills.com/blogs/paul/pfs-corruption-after-upgrading-from-sql-server-2014/
If this database was upgraded some time from SQL Server 2014 or earlier, you may have had the problem before the upgrade, but it was undetected by CHECKDB.
You should contact Microsoft Support, which will probably tell you to run a DBCC CHECKDB (or CHECKALLOC) with REPAIR_ALLOW_DATA_LOSS (if that's the only error, it should not cause data loss; it would only fix the PFS type). You may need to run that twice to fix it; after the first run, you may still have an error such as:
Page (1:111) in database ID 11 is allocated in the SGAM (1:222) and PFS (1:333), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED   0_PCT_FULL'.


While I can tell you that in my case, I found the job causing the error by it's frequency, (the error was happening every five minutes) and there were only 2 jobs running every 5 minutes.  However, after knowing which job was causing the error, I spent a lot of time to determine why the error was happening, given that the job itself was not failing.

Turns out that in the SQL Server Agent, Job Activity Monitor, Job Properties, Steps, (Edit),  Advanced tab, someone checked the option: "Append output to existing entry in table", which caused that the output continued to grow over time, resulting in the error: "length of the result exceeds the > length limit (2GB) of the target large type".  Unchecking the option circled below solved the issue for me.


Why would MySQL request a shared-lock after setting transaction level to READ UNCOMMITTED

"Read chunks, but only update a few of them"...

SELECT a chunk -- separate transaction, don't care about locking, etc
munch on that chunk to figure out the 'few' ids to update
BEGIN;
    SELECT ... WHERE id IN (list of the few) FOR UPDATE;
    minimal other work
    UPDATE them;
COMMIT;   -- plus checks for deadlocks, etc, and restart the code.

The idea is to pull as much time-consuming code out of the transaction as possible, thereby making the transaction faster, thereby minimizing the likelihood of conflict.  (No, it probably won't eliminate all deadlocks, but it should help.)

Another note:  If possible, go through the table in the same order in all threads.  Deadlocks happen when one thread plays with id IN (1,2) but another does IN (2,1).  If both do IN (1,2), then you get delays, not deadlocks.

Another tip:  Make the chunks smaller.


Increase in IO Wait after adding indexes with postgres

Yes, adding indexes could cause IO wait to increase.

Perhaps without the index, you are doing a lot of full scans of the table, thousands or millions of blocks, to get just one piece of data.  But the IO wait is very low, because the kernel read ahead keeps the pump primed so your process doesn't wait on IO (instead it uses a lot of User CPU to filter through the blocks to find the one row it wants).

With the index, it jumps exactly to the row it needs, but it needs to wait for it get read from disk, because read-ahead doesn't help you out.

  What steps can I take to reduce this?

Why does it need to be reduced?  Is there an actual problem?


Normalization: Should common columns of two tables be pulled out into a third table?

I see 3 options

Do nothing
Superkey/subtype, that is put the common fields into empl
Change to a self referencing table to build a hierarchy

Now,

Option 2 is similar to what you've described (mostly)
Option 3 is where you'd use a recursive CTE

However, I can't help thinking that Option 3 or some variety would be better. Perhaps some more info about what the columns mean? (Personally I don't abbreviate column names because the names are opaque)


Is it possible to schedule table update/refresh in Oracle Application Express?

You can do the following steps in a procedure:
1) User UTL_HTTP to read the csv file;
2) Save the file on a oracle directory;
3) Create an external table that will map this CSV file;
4) Schedule this procedure accordingly, using dbms_job_scheduler

This assumes your user has permissions to execute UTL_HTTP, UTL_FILE and DBMS_JOB_SCHEDULER, besides read/write permission in the given oracle directory.


Except duplicates from duplicates based on columns

Using window functions will likely be simpler here:

DB<>Fiddle

WITH cte AS
(
    SELECT
        ColumnA,
        ColumnB,
        ColumnC,
        COUNT(*) OVER (PARTITION BY ColumnA, ColumnB)
          AS count_ab,
        COUNT(*) OVER (PARTITION BY ColumnA, ColumnB, ColumnC)
          AS count_abc
    FROM MainTable
         ...SOME joins(INNER AND left)
    WHERE ColumnA IS NOT NULL
)
SELECT
    ColumnA,
    ColumnB,
    ColumnC,
    count_ab
FROM
    cte
WHERE
    count_ab > 1
  AND
    count_abc = 1 ;


Can i SELECT two user names by id in another table with join?

For MySQL, see here: https://www.db-fiddle.com/f/sjtJBAccd4gvZYZChbuWmx/1

create table sb_bans (
bid     int,
bannerid int,
unbannerid int
);

create table sb_admins (
aid int,
name varchar(10)
);

insert into sb_bans values (1,26,26);
insert into sb_bans values (2,27,13);
insert into sb_bans values (3,10,13);
insert into sb_bans values (4,11,27);
insert into sb_bans values (5,13,10);

insert into sb_admins values (10, 'Tomi');
insert into sb_admins values (11, 'Dominik');
insert into sb_admins values (13, 'Michal');
insert into sb_admins values (26, 'Kristian');
insert into sb_admins values (27, 'Daniel');

select b.bid,
       s.name as banner_id,
       s1.name as unbannerid
  from sb_bans b
  join sb_admins s
    on b.bannerid = s.aid
  join sb_admins s1
    on b.unbannerid = s1.aid


Supplemental logging options are treated as contraints by Data Pump.
create table t1(c1 number);
alter table t1 add supplemental log data (all) columns;

$ expdp bp/bp tables=t1
...
$ impdp bp/bp sqlfile=t1.sql
...
$ cat /u01/app/oracle/homes/OraDB19Home1/rdbms/log/t1.sql
-- CONNECT BP
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "BP"."T1"
   (    "C1" NUMBER
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 ROW STORE COMPRESS ADVANCED LOGGING
  TABLESPACE "USERS" ;
-- new object type path: TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT --   <---------------------
ALTER TABLE "BP"."T1" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;    --   <---------------------
-- new object type path: TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

EXCLUDE=CONSTRAINT will take care of it, which you already have in your parfile. However this will exclude the real constraints too. If you want to keep the other constraints, just drop the supplemental log groups after the import.


MongoDB Sharded Cluster: Balancing chunks not working, stuck at "step 2"

The definition of the steps referenced in a chunk migration are a little fluid, and can depend on the version you are running. However, based on what you have provided I suspect that the primary on rs01 and the number of chunks you are trying to move is the source of the issue here.

Last time I looked at this in detail (around version 2.6), step 2 was basically a "sanity check" on the source shard primary to make sure it was ready to kick off another migration.  There are two common reasons (and some not so common) for the primary to get "stuck" in step 2:

The primary is "too busy" - i.e. it is processing a lot of deletes from previous migrations and does not want to take on more migrations until some of those finish
There is an error being hit (chunk too big for example) and the migration is being aborted

Given that your two existing shards were not yet balanced when you added a third shard, though rs03 will now be the new preferred destination for migrating chunks since it has the lowest total, adding a third shard will not make this happen any more quickly.  In fact it just means there are more chunks to move than before you added the third shard.

To confirm what the root cause is here, you will need to take a look at the logs on the primary of shard rs01. The shard with the most chunks will always be chosen as the source of a migration, and the primary will be the one doing all the work.  The logs should tell you why the migrations are being aborted or taking a long time.

If it is a case of being too busy, you do have an option that will free it up, temporarily at least (it may get stuck again later). You can step down the primary, thereby killing any background delete jobs it is running for cleanup.

However, this will mean leaving behind "orphaned chunks" and you will need the clean up command to fix that later - you are not solving anything per se, you are just putting the work off until later. There are also only limited options to tweak this behavior, for more see here.

There are other options in terms of balancing chunks more quickly (mitosis), but they are way beyond the scope of an answer on this site I'm afraid.

If there is an error being hit, and the cleanup is not the issue, then it would probably be best to get the primary log messages and post a separate question to figure out the cause.


Why is there automatic failover for HA and manual failover for DR?

It is tricky with generalizations and simplifications. However, here's an attempt:
My feeling is that HA generally is closer geographically. In such cases it is more realistic to use synchronous methods, like sync Availability Groups. In such cases, it is reasonable to failover automatically.
But when we talk DR, it is reasonable to imagine longer distances, since having some distance is part of the resilience architecture itself. And with longer distance, it is likely that you have async methods to get changes across. Like async AGs. Log shipping is by its very nature async. I.e., failover with an async solution and you lose data! Not something you want to happen "behind the scenes".


You're missing the end quote, this should get you there.

WHILE (@7DaysEarlierPartitionIntegerId <= @CurrentPartitionIntegerId)
    BEGIN
        Set @7DaysEarlierPartitionId
            = CAST(@7DaysEarlierPartitionIntegerId AS char)
        set @sqlCommand
            = 'Select * from '
              + quotename(@RequestUsage_Partition + @7DaysEarlierPartitionId)
              + 'where UserLogin like ''r2\rohit.kharade'''
        exec(@sqlCommand)
        set @7DaysEarlierPartitionIntegerId
            = @7DaysEarlierPartitionIntegerId + 1
    END

I generally like to use CHAR(39) in place of multiple quotes, just to make code more readable

WHILE (@7DaysEarlierPartitionIntegerId <= @CurrentPartitionIntegerId)
    BEGIN
        Set @7DaysEarlierPartitionId
            = CAST(@7DaysEarlierPartitionIntegerId AS char)
        set @sqlCommand
            = 'Select * from '
              + quotename(@RequestUsage_Partition + @7DaysEarlierPartitionId)
              + 'where UserLogin like ' + CHAR(39) + 'r2\rohit.kharade' + CHAR(39)
        exec(@sqlCommand)
        set @7DaysEarlierPartitionIntegerId
            = @7DaysEarlierPartitionIntegerId + 1
    END

Bonus thought, just as another, more readable version, you could also QUOTENAME

WHILE (@7DaysEarlierPartitionIntegerId <= @CurrentPartitionIntegerId)
    BEGIN
        Set @7DaysEarlierPartitionId
            = CAST(@7DaysEarlierPartitionIntegerId AS char)
        set @sqlCommand
            = 'Select * from '
              + quotename(@RequestUsage_Partition + @7DaysEarlierPartitionId)
              + ' where UserLogin like ' + QUOTENAME('r2\rohit.kharade', CHAR(39))
        exec(@sqlCommand)
        set @7DaysEarlierPartitionIntegerId
            = @7DaysEarlierPartitionIntegerId + 1
    END


SQLite only implements a very limited subset of ALTER TABLE statements. You'll have to drop and recreate the table, preferably in one transaction:

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE tbl_tmp(col1, col2);
INSERT INTO tbl_tmp SELECT col1, col2 FROM tbl;
DROP TABLE tbl;
CREATE TABLE tbl(col1 integer, col2 text DEFAULT 'foo'); -- with new default
INSERT INTO tbl SELECT col1, col2 FROM tbl_tmp;
DROP TABLE tbl_tmp;
COMMIT;


Make a temporary table visible for a function inside a function (or a subfunction)

You can pass the name of the table:

create or replace function f2(p_table_name text) returns text
  language plpgsql
as
$body$
begin
  execute $$insert into $$||quote_ident(p_table_name)||$$ (c1) values ('text1');$$;
  return 'ok';
end;
$body$;

create or replace function f1() returns text
  language plpgsql
as
$body$
declare
  v1 text;
begin
  create temporary table t1 (
    c1 text
  );
  perform f2('t1');
  select c1 into v1 from t1 limit 1;
  return v1;
end;
$body$;

select f1();

See it working at: http://rextester.com/FYMX28296


How to know if a particular DW project is using Kimball methodology

As I'm only familiar with Kimball approach and my Inmon book gathers dusk, unopened and unloved, I'm going to cite this article which indicates the difference between a Kimball model for a slowly changed dimension and an Inmon would be the design.

  The common way to track changes in an Inmon or 3rd Normal Form data warehouse is to use the Natural/Business Key combined with a start and end date

The Kimball approach would be to use a surrogate key instead of the natural/business keys.


Execution of a Java program by a trigger.

Yes, using the MySQL User-Defined Function (see MySQL 5.5 FAQ: Triggers) and installing the lib_mysqludf_sys

Then, for example, you can write your own trigger calling the sys_exec like this:

delimiter |
 CREATE TRIGGER testtrigger BEFORE UPDATE ON T1
  FOR EACH ROW BEGIN
   DECLARE result int(10);
   IF NEW.Flag <> OLD.Flag THEN
     SET result = sys_exec('/path/to/javabin -jar your.jar');
     -- other kind of works and checks...
   END IF;
 END;
|

The result contains the exit code of the external program

There are other useful functions in this library:

sys_eval : executes an arbitrary command, and returns it's output.
sys_get : gets the value of an environment variable
sys_set : create an environment variable, or update the value of an existing environment variable
sys_exec : executes an arbitrary command, and returns it's exit code

More info here

Try it on a dev env and...

  Be very careful in deciding whether you need this function. UDFs are
  available to all database users - you cannot grant EXECUTE privileges
  for them. As the commandstring passed to sys_exec can do
  pretty much everything, exposing the function poses a very real
   security hazard.

But anyway, I agree with the proposal of Remus Rusanu


Force replication to statement for INSERT to a table to fire trigger on slave

As documentation say

With statement-based replication, triggers executed on the master also execute on the slave. With row-based replication, triggers executed on the master do not execute on the slave. Instead, the row changes on the master resulting from trigger execution are replicated and applied on the slave.
This behavior is by design.

So in anycase statement/row, you don't need to be worrying about the data on slave and it should match the master.
but still if you want and can fit-the-logic on an event, this might be a possibility on slave at the risk of inconsistency!!
As you said this is dw slave, would you consider a separate process to do the task you're looking to do, say a procedure?


MySQL - how to dynamically add columns with subquery in Select clause based on date range

You must construct different queries to get 1 column of output versus 3 columns.  That is best done in your application language.  If you are trying to do it entirely in SQL, it would require a messy Stored Procedure.


On Ubuntu apparmor doesn't let mysql write to directories other than listed in /etc/apparmor.d/usr.sbin.mysqld

apparmor is enabled by default, but on dedicated MySQL servers I don't see much sense in it.

From https://help.ubuntu.com/14.04/serverguide/apparmor.html:
"AppArmor can be disabled, and the kernel module unloaded by entering the following:

    sudo service apparmor stop
    sudo update-rc.d -f apparmor remove

"


How to effectively query and aggregate a normalized SQL db?

This is how I would do it.  I regularly use Coalesce to place rows into delimeted fields and it always performs and scales well (as long as you realize that a subquery is ALWAYS going to cause some performance hit).

If you didn't like running it as a stored procedure you could also easily rewrite as a table valued function.

Another approach would be a CTE I suppose but I am not as familiar with that approach to type from scratch.

CREATE PROCEDURE GetMessageById
    @pMessageID int
AS
BEGIN

SET NOCOUNT ON;

Declare @pTo varchar(max)
Declare @pCC varchar(max)
Declare @pBC varchar(max)

SELECT @pTo = COALESCE(@pTo + ', ', '') + [EmailAddress]
FROM MessageRecipient
WHERE MessageID = @pMessageID AND RecipientTypeID = 1 /** or whatever the id of TO field is */

SELECT @pCC = COALESCE(@pCC + ', ', '') + [EmailAddress]
FROM MessageRecipient
WHERE MessageID = @pMessageID AND RecipientTypeID = 2 /** or whatever the id of CC field is */

SELECT @pBC = COALESCE(@pBC + ', ', '') + [EmailAddress]
FROM MessageRecipient
WHERE MessageID = @pMessageID AND RecipientTypeID = 3 /** or whatever the id of BCC field is */

SELECT Message.*, @pTo as [ToField], @pCC as [CCField], @pBC as [BCCField], (SELECT TOP 1 [EmailAddress] FROM MessageRecipient Where RecipientTypeID = 0 /**<sender id>*/ AND MessageID = @pmessageID) AS [FromField] FROM Message Where Message.ID = @pMessageID

END
GO

You might ask yourself how Coalesce works when used in this way ( I did, when I first saw it used ).  Basically it creates a recursive query returning each subsequent non-null value in the set, in turn, until the end of the return set.  Coming out the other end you get a coma delimited list of all results as a single string.


First, never, ever, ever delete anything from pg_xlog, ever. You will corrupt your database. Now, on to what you've misunderstood about all this:

  I have checked multiple times by dropping tables and trying to restore the database to an earlier state. Is there anything that I am missing?

PITR doesn't work like that. It doesn't revert a running database to an older point in time.

How it works is that you set it up first, before you need to restore:

Set up WAL archiving, so you record WAL to a safe location.
Before you need to revert, you make a base backup. You can use pg_start_backup() and rsync for this, but it's easiest and safest to use pg_basebackup(). See the documentation for details on creating base backups. You must do this after setting up WAL archiving, so the archive contains all WAL from the moment the basebackup is taken onward.
Continue to run with WAL archiving, periodically taking a new base backup to reduce the amount of old WAL you have to keep and the amount of recovery time you need. Automated tools like pgbarman help with this.

When you need to create a historical copy of the database state to recover from something, you:

Make a copy of the base backup to writeable storage
Set the copy up with a recovery.conf file that specifies an appropriate restore_command for fetching WAL and the recovery_target_time you want to replay up to.  Setting primary_conninfo makes no sense, since you won't be using this as a warm or hot standby streaming replica.
Start the copy, and allow it to replay WAL until it reports that recovery is complete.
Connect to the copy, and do whatever you need to grab the data you wish to recover out of it. You may use pg_dump to extract a database or parts of it, use COPY to extract contents of tables, etc.

If you wish to completely revert a database you can delete the datadir, replace it with the copy, and follow the procedure above to replay the copy up to a point in time.

What you cannot do is take an existing database and roll it back to some point in the past. WAL replay only goes forward in time. So you must restore an old base backup then allow it to replay WAL up to the point in time you want.


SQL Server's `mssql-conf setup`, error Aborted sudo

sudo disables core dump

 To prevent the disclosure of potentially sensitive information, sudo disables core dumps by default while it is executing (they are re-enabled for the command
 that is run).  This historical practice dates from a time when most operating systems allowed setuid processes to dump core by default.  To aid in debugging
 sudo crashes, you may wish to re-enable core dumps by setting “disable_coredump” to false in the sudo.conf(5) file as follows:

       Set disable_coredump false

The Aborted above is actually generated from this

sudo -EH -u mssql /opt/mssql/bin/sqlservr
Aborted

Which is actually,

sudo -EH -u mssql sh
$ /opt/mssql/bin/sqlservr
Aborted (core dumped)

Make sure you have a new copy. In the event you updated your distro, you may have to re-add the repository and key. A core dump, may or may not be normal practice for Microsoft, from their own docs, license_Eval_Linux.txt

  TIME-SENSITIVE SOFTWARE. The software will stop running one hundred eighty days after you install it. You will not receive any other notice. You may not be able to access data used with the software when it stops running.


Error ORA-12560 when trying to connect Windows SQL*Plus client to Oracle 11g Linux server

On your Windows client, you need to specify the TNS alias @ORCL, or else it assumes you're trying to connect to a local database running on your Windows client, which there isn't one.  Also, system as sysdba generally isn't used, not even sure if that makes sense.  SYS as sysdba and just plain system are used.

Enter user-name: system@ORCL
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option

If you don't have tnsnames.ora set up on your Windows client, you can still connect via EZCONNECT string @"hayer:1522/orcl" instead of setting up tnsnames (yes, the quotes are needed).

Enter user-name: system@"hayer:1522/orcl"
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option


Find missing entries across several tables by SQL call

SELECT *
FROM SysN sy
WHERE NOT EXISTS ( SELECT NULL
                   FROM summary su
                   WHERE (su.name, su.date, su.sys) = (sy.name, sy.date, 'SysN') )

Execute 7 separate queries or combine them via UNION ALL into one query.


Why am I able to query a remote database without a dblink?

What you described here is absolutely normal and intended. This feature is called a global database link:

http://docs.oracle.com/cd/E24693_01/server.11203/e17120/ds_concepts002.htm#i1007888

It is a "side effect" when for example Oracle Internet Directory is in use, I have seen this at a few companies who have a lot of databases.

More and better explanation can be found in the support note: What are Global Database Link and How do you Disable them? (Doc ID 1632329.1)

  You are able to make a distributed connection using a Database link,
  However you have not created the database link used.

  You expect an ORA-2019 since the Database link Object itself does not
  exist but somehow the connection works. How is this possible?

  This is because of a default feature Called GLOBAL DATABASE LINKS. The
  feature is due to LDAP usage.

  Global Database links were introduced in earlier versions of Oracle
  when the Oracle Names Server was in use. You could create a Database
  Link that was stored in the Oracle Names server and accessed by those
  who accessed the Oracle Names Server. Once Oracle Names was deprecated
  the feature was moved to the replacement LDAP Naming.

  When a Database link is called the database link name will be checked
  against the PRIVATE link then PUBLIC links then GLOBAL links. If the
  Database link name matches the entry listed in the ldap server the
  actual Database link object is not required. The DBLINK will use the
  current user to connect therefore it only works if the user has access
  to both instances and has the same user id and same password on both.

  This is default behavior when LDAP naming is used.

  Currently the only way to disable the feature is to stop using LDAP
  naming.


Sql server full text search performance with additional conditions

Kin made a couple of suggestions which should be helpful.

First: Make sure that your statistics for the CallTime index are up to date.

Your plan shows that the time filter is being made by seeks to the clustered index.  So, for some reason the CallTime index is not being used.  What is the definition of that index?  If it is a multicolumn index, be sure that the most specific column is first. Example:

IDX_Calls_CallTime
  NOT: CallID, CallTime
  USE: CallTime, CallID

Second: Consider using CONTAINSTABLE.

I doubt that you will ever want to return 1.200.000 rows for "efendim".  By using CONTAINSTABLE and ranking you can set the top number of rows that you want, thus reducing the number of FullText results to process to 10, 100, 1000, or whatever fits your needs.


tsql to know when a database was last shrunk

Because for shrinking of database one uses DBCC SHRINKDATABASE or DBCC SHRINKFILE, you can try to solve your problem with server audits from the group DBCC_GROUP (see http://technet.microsoft.com/en-us/library/cc280663.aspx and http://msdn.microsoft.com/en-us/library/cc280386.aspx)


Difference between partitioning and fragmentation

The terminology depends on the RDBMS. In SQL server: Partitioning is a DB feature that can be used to improve performance and/or manageability of your DB.

Fragmentation happens when your rows in the data pages are non-contiguous due to insert/delete operations. Your DB performance will decrease if you have high fragmentation of your data.


MS SQL - Can I find a source of prior log growth?

Not directly.  Depending on the version of SQL you have, some of the DMVs may give you a little information about hard hitting queries:

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

The first thing I would check is for a database maintenance plan that is doing a DBCC or Index Rebuild.


Configure SQL Server to associate a user login with a specific port

See the instructions at:

Configure the Database Engine to Listen on Multiple TCP Ports

To set up a new TDS endpoint a code sample is:

USE master;
GO
CREATE ENDPOINT [CustomConnection]
STATE = STARTED
AS TCP
   (LISTENER_PORT = 1500, LISTENER_IP =ALL)
FOR TSQL() ;
GO

The side-effect of creating a Custom Connection is that you will need to reestablish the Default Connection:

GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public];

Then to grant access to the CustomConnection endpoint to the SQLSupport group (as an example), you could use something like this:

GRANT CONNECT ON ENDPOINT::[CustomConnection] to [corp\SQLSupport] ;
GO

Read the other details in that post that may need configuring.


Help defining a chained one-to-many relationship tables

Since Template must be related to a Region, you need a foreign key from Template.region_id to Region.id

Template.region_id will be NOT NULLable.

Template.country_id will be NULLable.

Make a unique constraint on the composite key (Country.id, Country.region_id)

Then you can create a foreign key from (Template.country_id , Template.region_id) to (Country.id, Country.region_id)

This ensures that if the Template is related to a Country, then the Template.region_id is the same as the related Country.region_id

Luckily (for you) when a row's composite foreign key contains one or more null values, then the row is "valid" regardless of the existence of rows in the referenced table.  So having a NULL Template.country_id and a NOT NULL Template.region_id will be fine.

One interesting side effect of this "doubly constrained" system is that it is difficult to update the region_id column of a Country.  Any attempt to update Country.region_id will cause a constraint violation if there are any Templates that contain references to that Country, since the (Template.country_id , Template.region_id) pair will not refer to an existing (Country.id, Country.region_id).

If you must allow this type of update, then you can use an ON UPDATE CASCADE clause when creating the (Template.country_id , Template.region_id) foreign key.


MySQL how to change a slave to a new master?

You're correct.  If the slaves are in sync to the same point on Alpha, START SLAVE UNTIL would serve no purpose.

Effectively, they're having you do is stop the slave on Gamma so that it is at the same point in Alpha's binlog as Beta.  In a live, high traffic condition, that can be slightly trickier to do, so I assume that's their motivation for suggesting that approach.

If Gamma and Beta have identical Relay_Master_Log_File and Exec_Master_Log_Pos values, then they are at the same point relative to Alpha, and you can safely switch Gamma to Beta at Beta's current master log file and position.

However... to state the (presumably) obvious, you can't be doing any writing on Beta (other than via replication) around the time you make the cut, because if you're writing to Beta, then Gamma would miss any events that hadn't replicated from Beta to Gamma via Alpha... but the START SLAVE UNTIL wouldn't help you with that, either.

This means you need to actually verify that all three servers are in agreement in all possible directions, so there's nothing outstanding from Beta back to Alpha.  Alpha's Exec_Master_Log_Pos (SHOW SLAVE STATUS) needs to match Beta's Position (SHOW MASTER STATUS) and both Gamma and Beta need to match Alpha and each other in the opposite direction (and of course the actual names of the binlogs need to match, not just the positions).

If you want to be super safe and paranoid, verify that everything is in agreement, then issue FLUSH TABLES WITH READ LOCK; on both Alpha and Beta (leaving the connections open to hold that global lock, which is yours once the prompt returns), then verify that everything remains in agreement at the same binlog positions with nothing written to either master server's binlog that the other hasn't already played back.  Stop, re-home and restart replication on Gamma, verifying that it is connected and waiting for incoming events.  Finally, verify agreement has persisted in each direction between Alpha and Beta once again, then UNLOCK TABLES; on Alpha and Beta.

Note that above, I  never stopped the slave on Beta.  It's not really necessary if you have a global read lock, since the slave is stalled and binlog coordinates can't advance under that condition, but you could stop it if desired, so long as the coordinates continue to match Alpha.


Filesystem copying inactive DBs on active MySQL server

Whether it is safe or not -- do you want to be stalled until it finishes?  You may as well stop MySQL for the dump.

Even better would be to set up LVM, then

Stop mysqld
Do snapshot; this takes a minute or so, regardless of the size.
Restart mysqld


Select from two different tables and group by dates

;WITH First_table
AS (SELECT '2015-03-08' AS [Date],'Diag1' AS Diagnostics, 2932 AS ID
    UNION ALL SELECT '2015-03-09','Diag2', 2932
    )
,Second_table
AS (SELECT '2015-03-09' AS [Date], 'Eval1' AS Evaluation ,2932 AS ID
    UNION ALL SELECT '2015-03-09','Eval2',2932
    UNION ALL SELECT '2015-03-10' ,'Eval3' , 2932)

SELECT
    KT.[Date] AS [Date]
    ,FT.Diagnostics AS [Diagnostics]
    ,STUFF(ST.Evaluation,1,1,'') AS [Evaluation]
FROM
    (
        SELECT [Date] ,[ID]
        FROM First_table
        --GROUP BY [Date] ,[ID]

        UNION

        SELECT [Date] ,[ID]
        FROM Second_table
        --GROUP BY [Date] ,[ID]
    ) AS KT  --keyTAble
    LEFT JOIN First_table AS FT
        ON kt.[Date] = FT.[Date]
        AND kt.[ID]= FT.[ID]
    OUTER APPLY
        (
            SELECT
                ',' + Evaluation
            FROM Second_table AS ST
            WHERE
                KT.[DATE] =ST.[Date]
                AND kT.[ID]= ST.[ID]
            FOR XML PATH('')
        )ST(Evaluation)


First you do something like this,

SELECT id, jsonb_agg(jsonb_build_array(lat,lon)) AS j
FROM foo
GROUP BY id;

That aggregates the values into an JSON Array. You get

 id |          j
----+----------------------
  2 | [[56, 67], [58, 64]]
  1 | [[34, 45], [45, 56]]

From there you need to build an JSON Object..

SELECT jsonb_object_agg(id,j)
FROM (
  SELECT id, jsonb_agg(jsonb_build_array(lat,lon)) AS j
  FROM foo
  GROUP BY id
) AS t;


You can use the environment variable PGOPTIONS.  Either set it permanently or just when you call psql, e.g.,

PGOPTIONS='--statement-timeout=1min' psql ...

This works for any libpq client, including psycopg.

Of course you could also just put the SET statement in the  psqlscript.


This issue usually happens when we have Pulse Secure installed, I have uninstalled Pulse Secure as it was no longer needed and that fixed the problem.

Related Post:
https://stackoverflow.com/questions/43569917/documentdb-emulator-not-working-service-unavailable?rq=1


PHP MySQL library version 5.0.51a differs from your MySQL server version 5.1.61

There is a connection otherwise phpmyadmin wouldnt know what version the server is.

In most cases you can ignore that as only slight differences exist (for a full difference check the changelogs between the versions)

apt-get install php5-mysql

yum install php5-mysql

one of those should update your php library and not show that warning


Getting InnoDb internal Errors on every query run

The problem is related with the internal representation of "timestamp" fields, probably caused by a bug in the database conversion procedure, occurred while upgrading from a previous version.

Exporting and importing the database "mysql" has fixed the problem in my debian server.

During the repair procedure, it's preferable to stop Apache and any other software that make use of the database, just in case.

Export the database:

mysqldump --events --quick --single-transaction mysql > mysql-dump.sql

Import back immediately after the export:

mysql mysql < mysql-dump.sql

Important: You must restart mysql service at this point to reload privileges.

service mysql restart

Of course I recommend to repair all your databases, because they could be affected.
You can do it one by one by hand, or all at once with a simple script:

mysqldump="mysqldump --events --quick --single-transaction"
exclude="information_schema|performance_schema"

cd /tmp

# Backup and re-import each database on the system
for db in $(mysql -e "show databases;" -s --skip-column-names | grep -vE "($exclude)")
do
        echo "Repairing database $db"
        $mysqldump $db > $db.sql
        mysql $db < $db.sql
done


Considering the presence of high HADR_DATABASE_FLOW_CONTROL waits on the primary, and the combination of PARALLEL_REDO_FLOW_CONTROL and PARALLEL_REDO_TRAN_TURN waits on the secondary, it sounds like parallel redo might not be a good fit for your workload.

  Can anyone think of any ddl,dml operations or other task that could cause this type of behavior?

Microsoft calls out one example in their blog post on parallel redo:

  PARALLEL_REDO_TRAN_TURN - Only happens in a readable secondary replica when new insert triggers page-split system transaction, or record update in a heap table generates a forwarded record

If your workload results in lots of page splits or forwarded records, then you should consider disabling parallel redo on the secondary by turning on documented trace flag 3459 (note: as a permanent solution, you will want to set this as a startup trace flag in SQL Server Configuration Manager):

DBCC TRACEON (3459,-1);

If you need to revert back to using parallel redo on your build, you will need to restart the SQL Server service after disabling the trace flag.  This was fixed (to not require a restart) in SP1 CU10 and SP2 CU2.  It doesn't look like it's fixed in the RTM branch.  See this KB for details: https://support.microsoft.com/en-us/help/4339858/fix-parallel-redo-does-not-work-after-you-disable-trace-flag-3459-in-a

More broadly, there are several fixes for issues with parallel redo in SP1 and SP2.  You should consider patching SQL Server to see if that resolves your issue as well.

If you want to confirm that page splits or forwarded fetches are actively occurring, try running sp_BlitzFirst:

EXEC dbo.sp_BlitzFirst;

If you want to confirm that your heaps have lots of forwarded records, you can use a DMV query like this one (note: replace Your_Table_Name with the name of a heap in your database):

SELECT
    OBJECT_NAME(object_id) AS table_name,
    forwarded_record_count,
    page_count
FROM sys.dm_db_index_physical_stats
(
    DB_ID(),
    OBJECT_ID(N'Your_Table_Name'),
    DEFAULT,
    DEFAULT,
    'DETAILED'
);


Do I have to commit after an exception has been raised?

You always need to either commit or rollback a transaction when the relevant unit of work is complete. Firebird transactions are not automatically aborted on errors (but usually the effects of the statement causing the error are undone). If you don't commit or rollback and the connections gets closed, open transactions will be rolled back eventually, but this is inefficient and can cause performance degradation (depending on how long that transaction remains open).

So, no, you don't have to commit (or rollback) immediately after an error, you commit when your work is complete, or rollback when your unit of work needs to be aborted.

However, very long running transactions can be problematic for other transactions, so make sure that your unit of work (transaction) is as short as possible (but not shorter than necessary for the work to be done).

However, failure to commit or rollback cannot cause errors about orphan pages. Orphan pages are a result of Firebird allocating database pages, but failing to add/register them to an object (table, index, etc), which means they exist and take space, but are unusable because nothing points to them.

This is usually the result of Firebird crashing or being killed in the middle of a write, or allocation bugs. You may want to check your logs for errors.


Sqlserver 2019 Partitioning based on Data Classification?

No. And it isn't just that this feature is not yet implemented, it is literally not possible to combine these two features in the way you're thinking.

Table partitions are row (record) based. You define a partitioning rule that determines which partition each record is stored in, based on a value stored in that record (for whichever column you've defined the rule).

Data classification labels are column-based. They are simply meta-data (like extended properties) attached to columns in a table that categorize the sensitivity (or lack thereof) of the data stored in the entire column. These categorizations are used by some pre-defined reports, but otherwise don't impact the behavior of the database.

Some further thoughts:

SQL partitioning is used to break a table into separate chunks, but to still appear as the same logical table. You can put those chunks in different filegroups (and put the different filegroups on different physical disks), but they all still have to reside in the same SQL instance and database.
If you are using the term "partition" less formally, sure, you could manually split a table into two tables based on sensitivity (say EmployeePublic and EmployeePrivate) with a 1-to-1 relationship, with different permissions or even encryption on each. You'd have to change your application queries, though, or create an Employees view that combines them back together in the way your application expects. This would be an entirely manual process, though, and has nothing to do with either the 2019 sensitivity flag or SQL table partitioning.


Assuming kgs_id is the primary key of both tables (which is a little strange), join the tables before doing the connect by:

select kgs_code, kgs_name
from (select * from dic_kgs join dic_kgs_strings using(kgs_id))
start with kgs_parent_id is null
connect by kgs_parent_id = prior kgs_id;

alternatively look up kgs_name after the connect by (this approach can be helpful in more complicated situations such as if not all records in dic_kgs have a corresponding record in dic_kgs_strings):

select kgs_code, ( select kgs_name
                   from dic_kgs_strings
                   where kgs_id=kgs_code.kgs_id ) as kgs_name
from dic_kgs
start with kgs_parent_id is null
connect by kgs_parent_id = prior kgs_id;


How do I get the PostgreSQL query execution time in PHP using the pg_* functions?

I'm 99% sure that both PostgreSQL and all other SQL database softwares send this information back for each query somehow, but how?

It doesn't. PHP's pg_query uses the simple query protocol and pg_query_params the extended query protocol, and in both cases what the client gets back for successful queries is essentially a CommandComplete message, where the only metadata available is a command tag saying:

what type of query it was
how many rows where affected by the query.

If there was a server-side execution time, it would be there, and it's not.
From the documentation:

CommandComplete (B)
Byte1('C')
Identifies the message as a command-completed response.
Int32
Length of message contents in bytes, including self.
String
The command tag. This is usually a single word that identifies which SQL command was completed.
For an INSERT command, the tag is INSERT oid rows, where rows is the number of rows inserted. oid used to be the object ID of the
inserted row if rows was 1 and the target table had OIDs, but OIDs
system columns are not supported anymore; therefore oid is always 0.
For a DELETE command, the tag is DELETE rows where rows is the number of rows deleted.
For an UPDATE command, the tag is UPDATE rows where rows is the number of rows updated.
For a SELECT or CREATE TABLE AS command, the tag is SELECT rows where rows is the number of rows retrieved.
or a MOVE command, the tag is MOVE rows where rows is the number of rows the cursor's position has been changed by.
For a FETCH command, the tag is FETCH rows where rows is the number of rows that have been retrieved from the cursor.
For a COPY command, the tag is COPY rows where rows is the number of rows copied. (Note: the row count appears only in PostgreSQL
8.2 and later.)


An execution plan could change for the same parameters for numerous reasons. Different set options such as ANSI_NULLS etc., statistics updates or data variance between executions.

As you are using SQL Server 2008 R2, you can control the plan using query hints. However, these should be avoided where possible as they are hard-coded and will remain in place when data volumes change in the future. Far better to let the optimiser choose the best plan after you maintain statistics and indexes appropriately.

If you really need to fix a query plan, a plan guide is another option as you don't have to modify any code.

If you ever upgrade to SQL Server 2016, the Query Store also allows you to fix the plan for a query with a single click.


JOIN in 4 tables gets phpmyadmin unresponsive

It's likely something more like this,but I'm not sure if you want to use INNER JOINs or not:

SELECT c.CompanyName
, s.categoryname
, cprs.strenght
, s.parentid
, ss.Risklevel
, ss.ColumnType

FROM (
       (
         company_details AS c INNER JOIN category_master AS s ON s.id = c.categoryid
       ) INNER JOIN company_strenght AS cprs ON cprs.companyid = c.id
     ) INNER JOIN subsector_detasils AS ss ON s.parentid = ss.subcategoryid

make sure you're sure it's what you want, and I would consider sorting it as well to make sure everything you're expecting is there...

Also, to make this more readable, you might consider using some pre-made VIEWs, using a CREATE <view-name> VIEW AS <one-inner-join>


Clever repeatable benchmarking and bottleneck detection test bench - PostgreSQL

While I can't speak to measuring specific metrics like CPU, storage throughput, etc. in your environment, I have found HammerDB to be a good tool for running a good, standardized benchmark against your databases.  This tool runs an unofficial TPC-C benchmark against your database. You can configure for concurrent user connections and workloads.  Using this conjunction with other metric tracking will help you evaluate your system for bottlenecks in an application agnostic manner.

As a note, HammerDB supports the following platforms at this time:

Oracle
SQL Server
MySQL
PostgreSQL
Redis


Is Information in sys.dm_db_index_usage_stats reliable

No.

The sys.dm_db_index_usage_stat view reflects only, at best, data since the last database startup (last instance startup, or last time the DB was brought online). Furthermore the entries may clear under memory pressure. It will give accurate positives (if a table has non-zero stats then it is used)) but may give false negatives (0 usage in stats may not reflect actual usage). Also there are many systems that use certain tables only once per week, once per month or even once per year.


1st suggestion to consider for your my.cnf [mysqld] section:

query_cache_type=0  # from 1 (ON) to OFF for NO qc to conserve CPU cycles
query_cache_size=0  # from 8M to conserve RAM for more useful purposes
innodb_io_capacity=1800  # from default (200) to increase SSD IOPS limit
innodb_flushing_avg_loops=5  # from 30 to reduce innodb_buffer_pool_pages_dirty count

# added 20181221 8 PM CT USA

innodb_purge_threads=4  # from 1 to expedite purge activity

# this will require stop/start after my.cnf change

You may monitor innodb_buffer_pool_pages_dirty count with the MySQL command:

SHOW GLOBAL STATUS LIKE '%dirty%';

If you check it every 10 minutes, you should see reduction and CPU busy will reduce as well until you get near 0 pages dirty.

I still need additional information requested to allow for additional performance improvements.

Please view my profile, Network profile and get in touch when you have a moment.


Is there a way to log each and every executing query and its corresponding result in MySQL?

For having all queries in a log file you need to activate general log file by adding this into the configuration file of mysql below section [mysqld] :

general_log
general_log_file=/path/to/the/general_log_file

Next if you want to have output of specific queries in a file you need to add the following instruction at the end of query :
into outfile /tmp/output_query

for example :
select version() into outfile /tmp/output_query

If you want to have output in a file from an entire MySQL client session write this in mysql client :

tee /tmp/output

That command tells MySQL to log both the input and output of your current MySQL login session to a file named /tmp/my.out.

As far as now there is no other way to have queries + output in a same log file, but maybe someone with much knowledge can confirm this.


How to find the stored procedure which executed an event?

Getting the SQL from a DDL Trigger for whatever query that is dropping this Stored Procedure will only help so much. If the query is coming from Dynamic SQL from a Stored Procedure, or from a release script, or an integration test, application code, etc, then you will likely only capture the DROP PROCEDURE ... which doesn't give much of a clue as to where that is being executed from.

However, this does not mean that DDL Triggers are not the way to figure this out. Rather than merely capturing the SQL and trying to infer the source, since this action is unwanted (and likely breaks whatever code calls the Stored Procedure that is being deleted), it should simply be disallowed. You can capture the DROP PROCEDURE event using a DDL Trigger, and then check which procedure is being deleted via the XML returned from the EVENTDATA() function. If the Stored Procedure being deleted is the one in question, then execute something along the lines of:

RAISERROR('Ah ha! Caught you red-handed (whatever that means). No DROP for you!', 16, 1);
ROLLBACK;

Doing this:

will prevent the stored procedure from being dropped (which is the desired outcome anyway).
will identify the source(s) of the DROP query. There could be more than one source, especially if procedure calls are nested. This method will bubble the error up so that whoever, or whatever, is originating the call, will see an error message and likely alert you since their action is not completing successfully.
will not prevent any other object from being dropped.

The following is a more complete example, including the ability to also log the event, just in case, since it can at least give some insight into which person or process is doing this, and how often:

CREATE TRIGGER [PreventDropGetPendingConfiguration]
ON DATABASE
FOR DROP_PROCEDURE
AS
  SET NOCOUNT ON;

  IF (EVENTDATA().value(N'(EVENT_INSTANCE/ObjectName/text())[1]', 'sysname')
        = N'usp_GetPendingConfiguration')
  BEGIN

    -- store values in variables as ROLLBACK will erase EVENTDATA()
    DECLARE @EventTime DATETIME,
            @LoginName sysname, -- lower-case for case-sensitive servers
            @UserName sysname, -- lower-case for case-sensitive servers
            @CommandText NVARCHAR(MAX),
            @SPID INT;

    DECLARE @InputBuffer TABLE
    (
      EventType NVARCHAR(30),
      [Parameters] SMALLINT,
      EventInfo NVARCHAR(4000)
    );

    SELECT @EventTime =
                 EVENTDATA().value(N'(EVENT_INSTANCE/PostTime/text())[1]', 'DATETIME'),
           @LoginName =
                 EVENTDATA().value(N'(EVENT_INSTANCE/LoginName/text())[1]', 'sysname'),
           @UserName =
                 EVENTDATA().value(N'(EVENT_INSTANCE/UserName/text())[1]', 'sysname'),
           @CommandText =
                EVENTDATA().value(N'(EVENT_INSTANCE/TSQLCommand/CommandText/text())[1]',
                'NVARCHAR(MAX)'),
           @SPID = EVENTDATA().value(N'(EVENT_INSTANCE/SPID/text())[1]', 'INT');

    -- RollBack now else logging will also get Rolled Back ;-)
    ROLLBACK;

    IF (OBJECT_ID(N'dbo.LoggyLog') IS NULL)
    BEGIN
      CREATE TABLE dbo.LoggyLog
      (
        LoggyLogID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
        EventTime DATETIME NOT NULL,
        LoginName sysname, -- lower-case for case-sensitive servers
        UserName sysname, -- lower-case for case-sensitive servers
        CommandText NVARCHAR(MAX) NOT NULL,
        SPID INT NOT NULL,
        EventInfo NVARCHAR(4000) NULL
    );
    END;

    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = N'DBCC INPUTBUFFER ( ' + CONVERT(NVARCHAR(10), @SPID)
               + N' ) WITH NO_INFOMSGS;';

    INSERT INTO @InputBuffer (EventType, [Parameters], EventInfo)
      EXEC(@SQL);

    INSERT INTO dbo.LoggyLog (EventTime, LoginName, UserName, CommandText,
                              SPID, EventInfo)
      SELECT  @EventTime, @LoginName, @UserName, @CommandText, @SPID, tmp.EventInfo
      FROM    @InputBuffer tmp;

    RAISERROR('Ah ha! Caught you red-handed (whatever that means **). No DROP for you!',
              16, 1);
  END;

GO

Any attempt to drop this Stored Procedure will receive the following error:

  Msg 50000, Level 16, State 1, Procedure PreventDropProcedure, Line 7
  Ah ha! Caught you red-handed (whatever that means). No DROP for you!
  Msg 3609, Level 16, State 2, Line 1
  The transaction ended in the trigger. The batch has been aborted.

The reason I used DBCC INPUTBUFFER instead of sys.dm_exec_sql_text is that sys.dm_exec_sql_text returns the current query being executed. If sys.dm_exec_sql_text is queried natively within the Trigger itself, you end up with the CREATE TRIGGER... statement. If that DMV is queried in Dynamic SQL or a sub-stored procedure call, then you would get those particular queries and not even the CREATE TRIGGER that they were called from. All of that is useless.

In contrast, DBCC INPUTBUFFER reports the first batch (not just the current query) in the chain, and that can at least be used to trace any number of subsequent calls that lead to the DROP call.

Also, given that this only happens sometimes, it is possible that someone forgot a GO in a release script that was doing a CREATE PROCEDURE just before the DROP PROCEDURE was called, and accidentally made the DROP query part of the Stored Procedure that was being created (this happens more often with GRANT EXECUTE statements since they typically follow CREATE PROCEDURE statements). This can happen due to something like the following being in a release script:

CREATE PROCEDURE dbo.ProcName
AS
...

-- missing GO !!!!

IF (OBJECT_ID(N'dbo.ProcGettingDropped') IS NOT NULL)
BEGIN
  DROP PROCEDURE dbo.ProcGettingDropped;
END;
GO -- this GO terminates the CREATE PROCEDURE statement

You can search for occurrences of this in the database containing the Stored Procedure that is getting dropped by running the following query:

SELECT OBJECT_NAME([object_id]) AS [ObjectName], *
FROM   sys.sql_modules
WHERE  [definition] LIKE N'%DROP%';

** etymology of red handed (thanks to @MartinSmith)


mysqldump dumps different data with and without --no-create-info

Try dumping using --hex-blob
mysqldump --hex-blob test_db test > dump_test.sql
mysqldump --hex-blob --no-create-info test_db test > dump_test2.sql

Please let us know if the hex value remains the same between the two dumps.
UPDATE 2021-01-08 14:15 EST
Try changing character set
The MySQL Docs say that mysqldump will use utf8 if not specified

Internationalization Options The following options change how the
mysqldump command represents character data with national language
settings.
--character-sets-dir=dir_name
The directory where character sets are installed. See Section 10.15,
“Character Set Configuration”.
--default-character-set=charset_name
Use charset_name as the default character set. See Section 10.15,
“Character Set Configuration”. If no character set is specified,
mysqldump uses utf8.
--no-set-names, -N
Turns off the --set-charset setting, the same as specifying
--skip-set-charset.
--set-charset
Write SET NAMES default_character_set to the output. This option is
enabled by default. To suppress the SET NAMES statement, use
--skip-set-charset.

Just add -N to ignore character sets or use --set-charset to explicitly name the character set you want to use when loading the dump in.


Why SQL Server Express 2012 take automatically backup in "Virtual Device" without virtual device configuration?

why it show most of the automatically backup taken in virtual device with
  "NT AUTHORITY\SYSTEM" user name & by default it shows the backup with
  virtual device & their some virtual location.

This is because some tool or windows server native backup feature is taking a Snapshot backup of SQL Server. Such backups normally use NT AUTHORITY\SYSTEM and SQL Server VSS writer service to connect to perform Snapshot backup.

Please note since you said there is no third party backup scheduled I have guessed here that it might be windows native backup. If you want to find more about such backup start the profiler and capture the events when backup is running. Or you can schedule the profiler. I must say profiler can create load so be aware about that  so be selective in events when configuring the profiler.

Workaround:

If you are very much certain that you don't need this backup, which IMO is useless as compared to native TSQL backup, you can go ahead and disable SQL Server VSS writer service. You can go to Services.msc and you would find this service there and then disable it.

  If this backup will going on continuation manner. Is there any impact of my Audit Data?

No, such backup is not going to have any affect on your data but yes it would be utilizing system resource when running.


Setup standalone with all the data from cluster, mongodb

As per MongoDB documentation Replica Set Arbiter An arbiter does not have a copy of data set and cannot become a primary. Replica sets may have arbiters to add a vote in elections for primary. Arbiters always have exactly 1 election vote, and thus allow replica sets to have an uneven number of voting members without the overhead of an additional member that replicates data.

  Changed in version 3.6: Starting in MongoDB 3.6, arbiters have
  priority 0. When you upgrade a replica set to MongoDB 3.6, if the
  existing configuration has an arbiter with priority 1, MongoDB 3.6
  reconfigures the arbiter to have priority 0.

Primary with a Secondary and an Arbiter

A three member replica set with a two members that store data has:

One primary.
One secondary member. The secondary can become primary in an election.
One arbiter. The arbiter only votes in elections.

Since the arbiter does not hold a copy of the data, these deployments provides only one complete copy of the data. Arbiters require fewer resources, at the expense of more limited redundancy and fault tolerance.


Determine whether there is any blocking while deleting large number of records

The title says blocking, but the question says locking. I'll deal with the former since the latter should be a given: yes, locks need to be taken in order to perform a delete.

To check for blocking, in the window where you're performing the deletes:

SELECT @@SPID; -- make note of this

In another window:

SELECT blocking_session_id, wait_type
  FROM sys.dm_exec_requests
  WHERE session_id = <spid from above>;

I know this DMV was available in the last two SQL Database refreshes, but not quite sure what version you're on.

Now, if you get a blocker, check what they are doing.

SELECT r.[status], r.command, r.blocking_session_id,
    r.wait_type, query = SUBSTRING(CONVERT(NVARCHAR(MAX),
    t.[text]), r.statement_start_offset/2,
    COALESCE(NULLIF(r.statement_end_offset,-1),
    LEN(CONVERT(NVARCHAR(MAX),t.[text]))*2)/2)
  FROM sys.dm_exec_requests AS r
  OUTER APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t
  WHERE r.session_id = <blocking session>;

You can't run DBCC INPUTBUFFER in this case because in SQL Database this is somewhat protected - you can only run this against your own @@SPID.

It might also be that log file autogrowth is the problem - if the clustered index delete here actually has to go and delete rows from many indexes and/or indexed views, the problem could be a log bottleneck. My usual go-to here is the default trace, but I don't believe you have access to that in Azure. Thankfully in your scenario we can see that this is the case due to the wait type shown - your query is actually being slowed down intentionally by the system because you're generating too much log activity. I'm not sure exactly how this algorithm works, but if it is happening when you delete 100 rows, it may be time to migrate to an instance with better "guaranteed" performance (which may mean paying more). Simply spinning up a new instance on the same plan may even help, as you'll likely end up on newer generation hardware which should naturally perform log operations quicker and may help you avoid hitting the governor.

You should also be committing transactions at some level of frequency within the loop. Otherwise chunking doesn't really help because you're likely still holding the locks on the first 100 rows while you're trying to delete the last 100 rows. Committing smaller chunks more frequently will have less impact on the log and potentially on locking/blocking too.

DECLARE @RowsDeleted INT = 1, @counter INT = 1;

BEGIN TRANSACTION;

WHILE (@RowsDeleted > 0)
BEGIN
    DELETE top(100) FROM dbo.table WHERE ScenarioID = @ID;
    SELECT @RowsDeleted = @@ROWCOUNT, @counter += 1;
    IF @counter % 10 = 0
    BEGIN
      COMMIT TRANSACTION;
      BEGIN TRANSACTION;
    END
END

COMMIT TRANSACTION;

You can also put a waitfor in the loop - maybe pausing long enough will prevent tripping the log rate governor.


Why does dropping a MVIEW trigger a full refresh?

After running some tests in an isolated environment it seems that Raj is right: the refresh background job that I saw while waiting for the DROP wasn't triggered by the DROP. As far as I can tell this was caused by the refresh settings provided during the creation of the mview.

It seems that

refresh start with trunc(sysdate) + 1/8  next sysdate + interval '8' hour

together with the implicit build immediate causes the mview to be refreshed during the creation and then again immediately after the creation has finished.

Apparently start with trunc(sysdate) + 1/8 causes this. When running

create materialized view foobar
   BUILD DEFERRED
   refresh start with trunc(sysdate) + 1/8  next sysdate + interval '8' hour
as
select ...
from ...;
The create statement finishes quickly and when I then look into the active sessions I do see one background session that does the refresh.


Is having a 92MB MDF with a 58MB LDF cobo ok? How do I manage the log to keep things running well?

If your database is in FULL or BULK_LOGGED recovery mode, you need to backup your database and log files on a regular basis. If your database is in SIMPLE recovery mode, then you only need to backup your database on a regular basis.

Please read the following articles for more info:

http://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx
http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(3030)-backup-myths.aspx
http://www.sqlskills.com/blogs/paul/post/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it.aspx
http://www.sqlskills.com/blogs/paul/post/Why-you-should-not-shrink-your-data-files.aspx


How to create Sql Server login for a domain account?

Don't create a local account with the same name as the domain account. If you want to add a domain login as a sql admins do as follows:

create a login for the domain account: create login [AD\Sql1] from windows;
add the login to sysadmin group: exec sp_addsrvrolemember 'AD\Sql1', 'sysadmin';

Done. You would achieve the same result if you'd simply add the AD\Sql account to the local administrators via net localgroup Administrators /add AD\Sql1 (from a CMD shell) but that is not the correct solution as it grants AD\Sql1 all NT administrator privileges in addition to granting him SQL admin, which is not stated as a requirement therefore is unnecessary elevation. BTW the rule that members of the local Administrators group are SQL admins is not implicit, is an explicit privilege granted by default during SQL Setup and it can be revoked so you have to check for it.


You can use Transaction Replication, if you can fulfill its considerations regarding:

Transaction log space.
Disk space for the distribution database.
Primary keys for each published table.
Triggers.
Large object (LOB) data types.

Transaction Replication allows data modified at Publisher to be delivered to the Subscriber as they occur (in near real time). The data changes are applied to the Subscriber in the same order and within the same transaction boundaries as they occurred at the Publisher; therefore, within a publication, transactional consistency is guaranteed.

You can also look at this using transaction replication or this using merge replication.


AlwaysOn Availability Groups log_send_rate

Yes, this was fixed recently in Service Pack 2, Cumulative Update 3. Here's the KB article: http://support.microsoft.com/kb/3012182

"FIX: Log_Send_Rate column in sys.dm_hadr_database_replica_states cannot reflect the rate accurately in SQL Server 2012"


Should I use a composite or single-column index?

Using SELECT * is bad practice especially in a stored procedure.  Even though you have a WHERE clause to filter the rows returned, I would explicitly state the columns.

As for indexes you will probably have to do the tuning yourself by looking at the execution plan for each type of index applied.  However I would have a clustered index on ID and 1 non clustered index on MedicineName, BrnadName, MedicineCode since it is them 3 columns you are selecting from.

CREATE NONCLUSTERED INDEX IX_Medicines_MedicineBrands ON Medicines (MedicineName, BrandName, MedicineCode)

I'd then personally include the execution plan and see how your stored procedure performs compared to having the following indexes:

CREATE NONCLUSTERED INDEX IX_Medicines_MedicineCode ON Medicines (MedicineCode)

CREATE NONCLUSTERED INDEX IX_Medicines_MedicineName ON Medicines (MedicineName)

CREATE NONCLUSTERED INDEX IX_Medicines_BrandName ON Medicines (BrandName)


I don't think so, when you failover you failover the whole AG. So in order to achieve this you'll need another AG. And probably another SQL Server License since you usually only license the active node in an AlwaysOn.


The impact is a very severe security risk. It might be that your application is hard-coded to use 'sa'; often very poorly written and implemented 3rd party apps do this kind of awful thing.

The question you need to ask is, "what can I do to fix this?", because it definitely needs fixing urgently.

If it is a 3rd party app, you may need to escalate to your support provider.

Ideally you will create a new database role, assign minimum required permissions to that role [e.g. exec on certain Stored Procedures] , then create logins/groups for your users, add them to this role, and modify the application's connection settings to use their own logins instead of sa.

You should also then create a new sysadmin login NOT called 'sa', for your own use, and then if possible, remove sa itself from the sysadmin fixed server role, and/or disable it.

Having all your users connect using a sysadmin-privileged login (even if it is one other than sa), is, to put it mildly, far from ideal.


Postgresql Streaming Replication Error: WAL segment removed

From Streaming Replication in the PostgreSQL documentation:

  If you use streaming replication without file-based continuous archiving, the server might recycle old WAL segments before the standby has received them. If this occurs, the standby will need to be reinitialized from a new base backup. You can avoid this by setting wal_keep_segments to a value large enough to ensure that WAL segments are not recycled too early, or by configuring a replication slot for the standby. If you set up a WAL archive that's accessible from the standby, these solutions are not required, since the standby can always use the archive to catch up provided it retains enough segments.


OLAP linked server fails connection test

Enable OLEDB Driver in SQL Server

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

If you are still facing the error after running above statement, please post error message


Prevent a single query from hogging all resources

If you're using Enterprise Edition, you can use the Resource Governor (I wrote a whitepaper for Microsoft on this topic a few years ago). This is especially effective if you can identify just this user (by SUSER_SNAME() or HOST_NAME()). Unfortunately this can't be used to place restrictions on just the one query - it is implemented at login time and affects all queries for the life of their session, but you can certainly constrain their CPU in general. Note that in SQL Server 2008 & R2 this constraint is only enforced when there is other contention on the box. In SQL Server 2012 there is a new setting (CAP_CPU_PERCENT) that allows you to constrain CPU for a resource pool even when they're the only one on the box.

Another way (or an additional way) to attack the problem, assuming you have control over the query text itself (e.g. it's not ad hoc being assembled by the user or their app), is to have that specific query always run with OPTION (MAXDOP 1) - it will still cause high CPU, and the query most likely will take longer, but you can use that setting to limit the number of schedulers it affects. So on a 16-core box, you would only see one CPU spiking as a direct result of this specific query.


Auto rollback Explicit transactions after X amount of time

You could create a job that checks for open transactions and kills them if they've been open for a period of time and are causing blocking. But you'd have to be careful with this as you could have a large, necessary transaction that takes a while that you don't want to get killed. Imagine rolling back an index rebuild that's been running for an hour on Standard Edition.

You can use sp_WhoIsActive to find open transactions that are causing blocking, though it'll take a bit of work.

But I would recommend that you don't do this and instead are diligent with your transactions in Management Studio. Any window that I ever started a transaction in gets closed when I am done. And if there's still an open transaction in it, SSMS will warn me about it so that I can commit/rollback.


Postgres - Trying to backup Functions only

There are at least two problems with this:

Don't include functions installed by an extension. Just install the extension to "restore" that function to any other database.

Aggregate functions are completely different and have to be treated separately. The CREATE AGGREGATE syntax differs from CREATE FUNCTION.

Don't you want to include the COMMENT for each function? (If one exists.)

I suggest these two queries:
Functions
-- SET LOCAL search_path = '';  -- force schema-qualification everywhere
SELECT pg_get_functiondef(p.oid) AS function
     , 'COMMENT ON FUNCTION ' || p.oid::regprocedure
       || ' IS ' || quote_literal(obj_description(p.oid, 'pg_proc')) AS comment
FROM   pg_proc p
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname !~ '^(pg_|information_schema)'    -- exclude system schemas
-- AND    n.nspname =  ...                         -- or from selected schema only?
AND    NOT EXISTS (                                -- exclude functions from extensions
   SELECT FROM pg_depend
   WHERE  objid = p.oid
   AND    deptype = 'e'
   )
--    AND    NOT p.proisagg                        -- exclude aggregate functions! for pg 10 or older
AND    NOT p.prokind = 'f'                         -- only normal functions! pg 11+
ORDER  BY n.nspname, p.proname;

Various types of dependencies are stored in the system catalog pg_depend. deptype = 'e' indicates it's part of an extension.
About proisagg vs prokind:

PostgreSQL 11 error: column p.proisagg does not exist

Aggregates (= aggregate functions)
-- SET LOCAL search_path = '';  -- force schema-qualification everywhere
SELECT format('CREATE AGGREGATE %s (SFUNC = %s, STYPE = %s%s%s%s%s)'
            , a.aggfnoid::regprocedure
            , a.aggtransfn
            , a.aggtranstype::regtype
            , ', SORTOP = '    || NULLIF(a.aggsortop, 0)::regoper
            , ', INITCOND = '  || a.agginitval
            , ', FINALFUNC = ' || NULLIF(a.aggfinalfn, 0)
            , CASE WHEN a.aggfinalextra THEN ', FINALFUNC_EXTRA' END
            --  add more to cover special cases like moving-aggregate etc.
              ) AS aggregate
     , 'COMMENT ON AGGREGATE ' || aggfnoid::regprocedure
       || ' IS ' || quote_literal(obj_description(aggfnoid, 'pg_proc')) AS comment
FROM   pg_aggregate a
JOIN   pg_proc      p ON p.oid = a.aggfnoid
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname !~ '^(pg_|information_schema)'    -- exclude system schemas
-- AND    n.nspname = 'public'                     -- or from selected schema only?
AND    NOT EXISTS (
   SELECT FROM pg_depend
   WHERE  objid = a.aggfnoid
   AND    deptype = 'e'
   )
ORDER  BY n.nspname, p.proname;

This only covers plain aggregate functions. Further reading:

How to get definition/source code of an aggregate in PostgreSQL?

To make it complete, you might want want add ownership (ALTER FUNCTION ... OWNER TO ...;) and privileges (GRANT / REVOKE).


Oracle Enterprise Manager (OEM) locking SYSMAN account

Benoit, you actually had a partial answer, everything you said was correct, but after next reboot, the account locked. After several hours of more research, the following issue was discovered, with the appropriate resolution.

Enterprise Manager had the default SYSMAN password stored for its credentials, and was trying to lock in with that password in rapid succession (300 times a minute or so according to the Event Log). Hence, after several rapid tries with the same wrong password, Oracle locks the account.

Setting a new password in SQL*PLUS or SQL Developer doesn't reset the password used to login to Enterprise Manager, to reset the password in enterprise manager, you need to open a command prompt and do the following:

emctl config oms sso -remove
emctl stop oms
emctl start oms

During the above commands, it'll prompt you for the current SYSMAN password, which you can enter, and it should remove the SYSMAN password used by your Enterprise Manager Console, replacing it with the one you just entered.


Find columns with different values compared with a reference row

This answer shows an implementation of the techniques mentioned by Michael Green in comments to the question. It uses SQL Server, but the basic idea is applicable to any SQL-compatible database engine that supports dynamic SQL. Support for UNPIVOT and PIVOT is not required.

The broad idea is:

Convert rows to key-value pairs
Compare with the reference row to identify differences
Convert the differences to one row per WERKS using dynamic SQL

It is the use of dynamic SQL that gets around the issue of not knowing which columns to SELECT - they are determined dynamically.

Sample table and data

CREATE TABLE #MARC
(
    c1 integer PRIMARY KEY,
    c2 integer NULL,
    c3 integer NULL,
    c4 integer NULL,
    c5 integer NULL
);

INSERT #MARC
    (c1,c2,c3,c4,c5)
VALUES
    (1, 2, 3, 4, 5), -- The reference row
    (2, 2, 3, 4, 5),
    (3, 2, 3, 4, 5),
    (4, 2, 3, 7, 9), -- Differences: 7 in c4; 9 in c5
    (5, 6, 3, 4, 8); -- Differences: 6 in c2; 8 in c5

Unpivot and find differences

-- Holds differences found (WERKS keys and column key-value pairs)
CREATE TABLE #Differences
(
    c1 integer NOT NULL,
    name varchar(2) NOT NULL,
    value integer NOT NULL
);

WITH
    Ref AS
    (
        -- Unpivoted reference row
        SELECT
            M.c1,
            CA.name,
            CA.value
        FROM #MARC AS M
        CROSS APPLY
        (
            VALUES
                ('c2', M.c2),
                ('c3', M.c3),
                ('c4', M.c4),
                ('c5', M.c5)
        ) AS CA (name, value)
        WHERE
            M.c1 = 1
    ),
    NonRef AS
    (
        -- Unpivoted non-reference rows
        SELECT
            M.c1,
            CA.name,
            CA.value
        FROM #MARC AS M
        CROSS APPLY
        (
            VALUES
                ('c2', M.c2),
                ('c3', M.c3),
                ('c4', M.c4),
                ('c5', M.c5)
        ) AS CA (name, value)
        WHERE
            M.c1 <> 1
    ),
    Differences AS
    (
        -- Find column value differences
        SELECT
            NonRef.c1,
            NonRef.name,
            NonRef.value
        FROM NonRef
        JOIN Ref
            ON Ref.name = NonRef.name      -- Same column name
            AND Ref.value <> NonRef.value  -- Different value
    )
INSERT #Differences
    (c1, name, value)
SELECT
    D.c1,
    D.name,
    D.value
FROM Differences AS D;

Differences

-- Show differences for illustrative purposes
SELECT
    D.c1,
    D.name,
    D.value
FROM #Differences AS D;

╔════╦══════╦═══════╗
║ c1 ║ name ║ value ║
╠════╬══════╬═══════╣
║  4 ║ c4   ║     7 ║
║  4 ║ c5   ║     9 ║
║  5 ║ c2   ║     6 ║
║  5 ║ c5   ║     8 ║
╚════╩══════╩═══════╝

Dynamic SQL

-- Header
DECLARE @sql varchar(max) =
    'SELECT D.c1' + CHAR(13);

-- Add aggregation
WITH D AS
(
    SELECT DISTINCT D2.name
    FROM #Differences AS D2
)
SELECT @sql +=
    (
        SELECT
            ', MAX(CASE WHEN D.name = ' + CHAR(39) + D.name + CHAR(39) +
            ' THEN D.value END) AS ' + QUOTENAME(D.name) + CHAR(13) AS [text()]
        FROM D
        FOR XML PATH (''), TYPE
    ).value('(./text())[1]', 'varchar(max)')

-- Footer
SET @sql +=
    'FROM #Differences AS D' + CHAR(13) +
    'GROUP BY D.c1;'

The constructed SQL statement in @sql is:

SELECT D.c1
, MAX(CASE WHEN D.name = 'c2' THEN D.value END) AS [c2]
, MAX(CASE WHEN D.name = 'c4' THEN D.value END) AS [c4]
, MAX(CASE WHEN D.name = 'c5' THEN D.value END) AS [c5]
FROM #Differences AS D
GROUP BY D.c1;

That is the manual PIVOT syntax. The final result is obtained by executing the SQL:

EXECUTE (@sql);

Results:

╔════╦══════╦══════╦════╗
║ c1 ║  c2  ║  c4  ║ c5 ║
╠════╬══════╬══════╬════╣
║  4 ║ NULL ║ 7    ║  9 ║
║  5 ║ 6    ║ NULL ║  8 ║
╚════╩══════╩══════╩════╝

This is the minimal set of columns describing the column differences with respect to the reference row, as required.

-- Clean up temporary tables
DROP TABLE
    #Differences,
    #MARC;

There is some SQL Server-specific syntax above, but the manual UNPIVOT can be achieved in other ways if APPLY or LATERAL is not supported e.g. with a cross join to a suitable identity matrix table. Generating the dynamic SQL uses FOR XML PATH as an efficient way to concatenate the strings, but again this can be done in any language.

Stack Exchange Data Explorer Demo


SQL is specifically meant to express simple queries more or less like you would ask them as a question in English. For instance, your question is like:

  "What are all the id from users that have no
  associated call in calls with a created_date greater or equal than date $1 ?"

SQL translation:

SELECT id FROM users
  WHERE NOT EXISTS (
    SELECT 1 FROM calls WHERE call.user_id=users.id AND created_date >= $1
  );

It's a rather direct translation except maybe for SELECTing 1, which is an arbitrary value.

There is no reason to assume that PostgreSQL would produce a non-optimal execution plan for this query. The optimizer is certainly smart enough for that kind of queries. In general if there's a straightforward way to express your query, there's no reason to pretend to be smarter than the optimizer.

When you doubt that the optimizer found the best possible plan (say for more complex queries), you can start from the output of EXPLAIN to figure out if/why it's not optimal and typically try to improve the query by trial and error.

If you want to compare how different variants of the same query are planned, compare the outputs of EXPLAIN ANALYZE of the queries.

https://wiki.postgresql.org/wiki/Slow_Query_Questions is a good start too.


Why does Oracle SQL*Plus consider two queries executed together on the command line as a syntax error?

It is a syntax error.

SQL*Plus expects either:

A single SQL command, terminated by either a ";" character or a "/" on a line by itself.
A PL/SQL block
A SQL*Plus command

What you have entered is 2 queries on a single line, which SQL*Plus will send to the RDBMS - Oracle will then try and parse the string sent as a single query and fail because it is not valid SQL.

See the documentation for more details: http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_four.htm#i1039424


How to run update queries saved in a permanent table?

DECLARE @Query1 nvarchar(max) = N'';

SELECT @Query1 += Query + N';' FROM dbo.PermaTable1;

PRINT @Query1; -- to see the first 8,192 characters

-- EXEC sys.sp_executesql @Query1; -- uncomment this when you trust it

But I have questions. Shouldn't those update queries have where clauses? Otherwise each one is going to overwrite all rows and basically undo all the updates that came before it. Also are these update commands really stored in the same table they affect?