What does maintenanceMode -7 mean on a mongo primary?

Maintenance mode is used when running certain commands, generally the compact command.  What you have here is the bug mentioned in SERVER-9338 whereby I am guessing you have run the compact command against your nodes several times.  There are some circumstances, especially when run on a primary when it will usually cause a change of state (i.e. it's not primary when the compact finishes), that cause maintenanceMode to be erroneously decremented.

As of writing this answer this is fixed in the development branch and has been marked for backport, though it has not (yet) been targeted for a release in the earlier versions.  Until the fix is released it should be safe to ignore the value, but I will do some testing to confirm and report back.


mysql custom query logging for inter-software data exchange

Ok, after some research, here's the answers I found.

Q1: Is it possible to detect the user that executed the query in MySQL?

It is possible to detect with which user by using: select user();

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_user

Beware: In triggers and events current_user() returns the user who defined the object (in SP and views unless if defined with the SQL SECURITY INVOKER). You really need to use the user() function to return the invoker in those contexts.

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_current-user

Q2: Is it possible to fire more than one action (INSERT, UPDATE, DELETE) on the same trigger?

Most unfortunately, at the current time (2016/02/01), it is not possible to fire two actions on the same time in MySQL. The best we can do is to regroup the common coding within a stored procedure.

The following procedure is one possible way to work around this problem. It expects in input:

callingTable: The name of the table for which we want to record the query (must be in the white list).
action: Either 'DELETE', 'INSERT' or 'UPDATE'.
fieldsValues: a string containing a valid pair of field/value in XML. By example: <field>ID</field><value>1616</value>

CREATE PROCEDURE `dataLinkRecorder` (
IN callingTable VARCHAR (25),
IN action ENUM('DELETE', 'INSERT', 'UPDATE'),
IN fieldsValues TEXT
)
BEGIN
DECLARE callingUser VARCHAR(25) DEFAULT '';
DECLARE fieldCount SMALLINT UNSIGNED DEFAULT 0;
DECLARE fieldCCount SMALLINT UNSIGNED DEFAULT 0;
DECLARE valueCount SMALLINT UNSIGNED DEFAULT 0;
DECLARE valueCCount SMALLINT UNSIGNED DEFAULT 0;

IF (TRIM(fieldsValues) <> '') THEN

    /* the calling table must be one in the white list */
    SELECT CASE
        WHEN UPPER(callingTable) IN (
            'CATEGORY',
            'CUSTOMER',
            'MAKE',
            'MODEL',
            'PRODUCT',
            'VEHICLE'
        )
        THEN TRIM(UPPER(callingTable))
        ELSE ''
        END AS myTable
        INTO callingTable;

     SELECT TRIM(
     UPPER(
        IFNULL(USER(), 'CLIENT')
     )
     ) AS myUser
INTO callingUser;

/* do not record modifications from tier software */
IF (callingTable <> '' AND INSTR(callingUser, 'CLIENT') < 1) THEN

    SELECT IFNULL(
        ((CHAR_LENGTH(fieldsValues) - CHAR_LENGTH(REPLACE(fieldsValues, '<field>', ''))) / 7)
    , 0) AS fCount,
    IFNULL(
        ((CHAR_LENGTH(fieldsValues) - CHAR_LENGTH(REPLACE(fieldsValues, '</field>', ''))) / 8)
    , 0) AS fCCount,
    IFNULL(
        ((CHAR_LENGTH(fieldsValues) - CHAR_LENGTH(REPLACE(fieldsValues, '<value>', ''))) / 7)
    , 0) AS vCount,
    IFNULL(
        ((CHAR_LENGTH(fieldsValues) - CHAR_LENGTH(REPLACE(fieldsValues, '</value>', ''))) / 8)
    , 0) AS vCCount
    INTO fieldCount, fieldCCount, valueCount, valueCCount;

    /* validate the field/value XML pair */
    IF (fieldCount > 0 AND
        valueCount > 0 AND
        fieldCount = fieldCCount AND
        valueCount = valueCCount AND
        fieldCount = valueCCount
    ) THEN

        INSERT INTO `pc_datalink`
            (`table_name`, `query_xml`)
        VALUES(
            callingTable,
            CONCAT(
                '<query>',
                    '<table>', callingTable, '</table>',
                    '<action>', action, '</action>',
                    fieldsValues,
                '</query>'
            )
        );
    END IF;
END IF;
END IF;
END //

Will insert values in the following table:

CREATE TABLE `pc_datalink` (
    `id` int(10) UNSIGNED NOT NULL,
    `table_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
    `query_xml` text COLLATE utf8_unicode_ci NOT NULL,
    `date_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `AK_DATE_ADDED` (`date_added`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

By example, a very simple trigger like this one:

CREATE TRIGGER `dataLinkDeleteVehicle` AFTER DELETE ON `pc_vehicle`
FOR EACH ROW
BEGIN
IF (OLD.original_id <> '') THEN

    CALL dataLinkRecorder(
        'VEHICLE',
        'DELETE',
        CONCAT(
            '<field>ID</field><value>',
            OLD.original_id,
            '</value>'
        )
    );
END IF;
END //

Will generate a line in the table like this one:

Don't hesitate to ask for details if you still have questions,

Good success everyone


What are system columns used for in PostgreSQL?

System columns are primarily meant to be used by the system. These columns are implementation details that might change between major versions (though very unlikely for most of them). You won't find a lot in the manual for when and why to use them because you are not supposed to use them in standard operation. The manual says:

  You do not really need to be concerned about these columns; just know they exist.

That said, there are various special situations and corner cases where each of these columns come in handy. Remember that SELECT * never includes system columns. You have to list them explicitly if you want them in the SELECT list.

For instance, you can use the ctid (tuple ID) to uniquely identify rows (within the same statement) in the absence of a primary key within the same statement:

Numbering rows consecutively for a number of tables

Search for Postgres in combination with each system column here on dba.SE or on stackoverflow to find various other use cases. Example search.

In addition to the manual page about system columns you are linking to, there is at least one other that's relevant:

https://www.postgresql.org/docs/current/static/storage-page-layout.html


1) Will the synchronization still happens between Application and DB server ?

No synchronization will not happen during restore operation

  During the DB restoration will the DB which is part of AOAG is locked out so that the synchronization with Secondary AOAG replica not happens ?

Please note that you cannot just go ahead and restore database which is part of availability group, you would have to evict database from AOAG, restore it and then add it back to AOAG see This Link. The query would look like below(shamelessly copied from This SE thread)

--on primary

ALTER AVAILABILITY GROUP MyAG REMOVE DATABASE AdventureWorks2012;

--on primary

RESTORE DATABASE AdventureWorks2012
   FROM AdventureWorksBackups
   WITH NORECOVERY,
      MOVE 'AdventureWorks2012_Data' TO
'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.mdf',
      MOVE 'AdventureWorks2012_Log'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.ldf';
RESTORE LOG AdventureWorks2012
   FROM AdventureWorksBackups
   WITH RECOVERY;

--on secondary

RESTORE DATABASE AdventureWorks2012
   FROM AdventureWorksBackups
   WITH NORECOVERY,
      MOVE 'AdventureWorks2012_Data' TO
'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.mdf',
      MOVE 'AdventureWorks2012_Log'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.ldf';
RESTORE LOG AdventureWorks2012
   FROM AdventureWorksBackups
   WITH NORECOVERY;

--on primary

ALTER AVAILABILITY GROUP MyAG ADD DATABASE AdventureWorks2012;

--on secondary

ALTER DATABASE AdventureWorks2012 SET HADR AVAILABILITY GROUP = MyAG;


As I am able to see the chunks error such as

  3826 : Failed with error 'moveChunk failed to engage TO-shard in the
  data transfer: can't accept new chunks because there are still 7
  deletes from previous migration', from shard0000 to shard0001 7655 :
  Failed with error 'moveChunk failed to engage TO-shard in the data
  transfer: can't accept new chunks because there are still 4 deletes
  from previous migration', from shard0001 to shard0000

As Asya Kamsky google groups documentation here When importing a large amount of data it is always preferable to pre-split the collection and distribute chunks evenly across the shards and disable balancer  before starting the data load.   Otherwise you will see the migrations and new data load compete for the same resources, reducing the performance of both.

Yes, you should/can re-enable the balancer after the import is done.   But of course it's better to presplit/pre-balance as described  here.

For further your ref here


Return rows one at a time from variable table

This query should do what you want and avoid any issues with 1 to many relationships messing up the SUMs

SELECT i.job_no, installment_billing_amount - transaction_billing_amount
FROM (
        SELECT job_no, SUM(jm_installment.billing_amount) installment_billing_amount
        FROM [FCU_LIVE].[dbo].[jm_installment]
        GROUP BY job_no
    ) i
    INNER JOIN (
        SELECT jm_work_order.job_no, SUM(jm_wo_transaction.billing_amount) transaction_billing_amount
        FROM [FCU_LIVE].[dbo].[jm_work_order]
            INNER JOIN [FCU_LIVE].[dbo].[JM_WO_TRANSACTION] ON jm_work_order.wo_no = jm_wo_transaction.wo_no
                AND jm_work_order.wo_seq = jm_wo_transaction.wo_seq
                AND jm_wo_transaction.override = 'N'
        GROUP BY jm_work_order.job_no
    ) t ON i.job_no = t.job_no
WHERE   i.job_no IN ( SELECT    job_no
                      FROM      @job_nos )

It is easier to follow as a CTE query.

WITH
    installmentSum AS
    (
    SELECT job_no, SUM(jm_installment.billing_amount) installment_billing_amount
    FROM [FCU_LIVE].[dbo].[jm_installment]
    GROUP BY job_no
    )
    ,
    transactionSum AS
    (
    SELECT jm_work_order.job_no, SUM(jm_wo_transaction.billing_amount) transaction_billing_amount
    FROM [FCU_LIVE].[dbo].[jm_work_order]
        INNER JOIN [FCU_LIVE].[dbo].[JM_WO_TRANSACTION] ON jm_work_order.wo_no = jm_wo_transaction.wo_no
            AND jm_work_order.wo_seq = jm_wo_transaction.wo_seq
    WHERE jm_wo_transaction.override = 'N'
    GROUP BY jm_work_order.job_no
    )
SELECT j.job_no, installment_billing_amount - transaction_billing_amount
FROM @job_nos j
    INNER JOIN installmentSum i ON j.job_no = i.job_no
    INNER JOIN transactionSum t ON i.job_no = t.job_no;


Ola Hallengren Scripts and Maintenance Plan Editor - How to execute sequentially on completion?

Why not just run each job as a seperate step?

And so on and so forth


ORA-28031 while attempting to rebuild or drop index

The 150 enabled roles limit is hard coded. There is no way around it other than to remove roles from the user or consolidate the existing roles. Ideally you should do the latter for better long term results. See MOS Doc ID 1101035.1 and Doc ID 780749.1 for more details and some tips on how to identify the relevant roles.


Recreate indexes and constraint when redefining table with DBMS_REDEFINITON

If you are only redefining storage you can do that without redefining the table. You can alter the table definition.  Rearranging column orders requires redefinition.  Most other changes can be done by altering the table.

If you do redefine the table you need to define the constraints and indexes you want on the new table.  These will need new names.  Some objects can be renamed later.

If you want to rebuild the table with the new storage, you can do that with an online move.  If the new storage definition does not include a new tablespace, you will need to move it twice, once to another tablespace, and then back into the original tablespace.


Having trouble with interface table structures in MSSQL

Did you consider to run in loop and extract each time TOP 10000 (or 5000)?

It is possible only if you can identify them later for delete / bitcolumn update. For example, if you have and identity column ID, it could look like

declare @id int
set @id = (select min(id) from yourtable where bitcolumn = false)
while exists (select top 1 1 from yourtable where bitcolumn = false)
begin
    select c1,c2
    from yourtable
    where bitcolumn = false
        and ID between @id and @id + 5000
    [your code here]
    update yourtable
    set bitcolumn = false
    where  ID between @id and @id + 5000

    set @id = @id + 5001
end


Chunking data into a PostgreSQL bytea column?

If I update a row, is it possible to chunk the data into the bytea column in several small steps?

Yes, it's possible. But it's a horrible idea. PostgreSQL, and all MVCC databases, writes a new row with every update so on the last chunk you'll be writing 200 MB not 1 MB.

You may consider large_objects to get around this, or better yet not using a database for this because databases are not file systems, and instead just using an IO buffer or the file system write cache. If you need to link that to data in a database, try a path. That's what they're there for (internally the file system uses inodes). Check out btrfs or zfs if you want the PostgreSQL of file systems.

PostgreSQL is the best tool for the job if you're doing the right job, but it's not the best tool for any job.


I want to find out in SQL SERVER 2012, who is creating SQL Logins and on which date

SQL server wont generally keep a track of this. So you have to make use of the default trace to gather the information for EventClass 109.

Make sure to check that default trace is running using:

SELECT* FROM sys.configurations WHERE configuration_id = 1568

If not enabled, then enable it using:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'default trace enabled', 1;
GO
RECONFIGURE;
GO

Then you can run the query as mentioned here to gather the information for the added SQL logins:

SELECT  TE.name AS [EventName] ,
        v.subclass_name ,
        T.DatabaseName ,
        t.DatabaseID ,
        t.NTDomainName ,
        t.ApplicationName ,
        t.LoginName ,
        t.SPID ,
        t.StartTime ,
        t.RoleName ,
        t.TargetUserName ,
        t.TargetLoginName ,
        t.SessionLoginName
FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1
                                                              f.[value]
                                                      FROM    sys.fn_trace_getinfo(NULL) f
                                                      WHERE   f.property = 2
                                                    )), DEFAULT) T
        JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
        JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id
                                            AND v.subclass_value = t.EventSubClass
WHERE   te.name IN ( 'Audit Addlogin Event', 'Audit Add DB User Event',
                     'Audit Add Member to DB Role Event' )
        AND v.subclass_name IN ( 'add', 'Grant database access' )

Note * There are 5 default trace files and these are rolled over,hence its possible to capture recent information related to objects and not related to old events. Therefore to save the information for future use please read Collecting the Information in the Default Trace


Select products for an order via check boxes in Access 2010

You have an order/order detail set-up and this means the easiest way is to create an order form with a detail subform. The subform should be based on the table Products illustrated above, with a combo box for PID drawing the row source from Lookup products. The link child and master fields will be Order ID.

You can see something like this illustrated in the Northwind sample file. The sample files should not be followed exactly, they are only illustrations. In particular, the code is inclined to be a little dodgy.


With your initial feedback (you are using innodb), I can tell you that your innodb_buffer_pool_size is too small (2M), so most of your queries may be using disk instead of memory. As a rule of the thumb, for a dedicated server, the usual recommendation is reserving between 60-85% of the available memory for the innodb buffer.

Increase it by setting it in the my.cnf file. I have also added extra changes (as it can be seen below), mainly making the transaction logs larger and setting a lower durability by default. Plese make sure to follow the following procedure if you change the log file size:

Shutdown mysql
Change the configuration file, usually found at /etc/my.cnf
Delete the transaction log files, usually found at /var/lib/mysql/ib_logfile0 and /var/lib/mysql/ib_logfile1. Do not delete anything else.
Start mysql again, it may take some extra time to recreate the log files

[mysqld]
# I am assuming you have 500M of memory spare for your data
innodb_buffer_pool_size=500M

# This option is ignored by modern OS, no need to set it
#innodb_additional_mem_pool_size=500K
innodb_log_buffer_size=500K

# let innodb handle concurrency, comment this or it may create a bottleneck
#innodb_thread_concurrency=2

# Add a bigger transaction log, assuming you have 100MB extra disk for it
innodb_log_file_size = 50MB

# The following assumes that durability is not important, but it may provide
# a huge boost in perfomarnace
innodb_flush_log_at_trx_commit = 2

key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8

query_cache_limit = 1M
query_cache_size = 16M

# the following lines are duplicated, remove them:
#innodb_buffer_pool_size=2M
#innodb_additional_mem_pool_size=500K
#innodb_log_buffer_size=500K
#innodb_thread_concurrency=2

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer = 16M

Additional comments:

You may tune the query cache to better manage your load, but only if you have mostly reads
Setting a lower durability may make you lose transactions in case of a crash or a loss of power
You have less than 1MB of indexes, please make sure that your query performance problem is not due to the lack of proper indexing (all tables have primary keys and secondary keys as needed).

Edit: for more automatic examples of my.cnf configuration, have a look at Percona's configuration wizard. For a full review of your queries and configuration, please hire expert mysql consulting services.


Moving data between Oracle and PostgreSQL

Learn and use an ETL tool

You'll save yourself a lot of time and pain if you use ETL tools like Pentaho Kettle or Talend Studio to automate the data transfer and merging. They'll happily connect to the Pg instance and the Oracle instance and do the required copying and merging using quite flexible strategies. They can consume CSV dumps too.

Use CSV dumps

If you need to use dumps, try to dump individual tables using COPY ... FORMAT CSV rather than attempting to wrangle pg_dump into doing Oracle-compatible partial dumps. This will save you a great deal of time when importing them into other DBMses, since pretty much everything understands CSV. Keep the schemas consistent for each instance so you don't need to handle different schemas for different instances.

Use disjoint set or composite keys

Where unique keys are used, make sure you assign disjoint key ranges to different instances (eg the key range is instance_id * 10^7 to (instance_id + 1 * 10^7)-1) or use composite keys of (instance_id, generated_key). If you use disjoint ranges add a CHECK constraint to enforce it; similarly, if using composite keys add a CHECK constraint to force the instance_id part to a constant.

Lowest common denominator data types

Avoid using PostgreSQL arrays, record types, and hstore. They'll all dump as string literals in CSV dumps and you'll have to parse them in order to do something useful with them. For example:

regress=> SELECT ROW( ARRAY[1,2,3], ARRAY['a','b','withquotehere:''inline', 'doublequotehere:"'], ARRAY[ ROW(1,'a'), ROW(2,'b') ] );
                                            row
--------------------------------------------------------------------------------------------
 ("{1,2,3}","{a,b,withquotehere:'inline,""doublequotehere:\\""""}","{""(1,a)"",""(2,b)""}")
(1 row)

You really, really don't want to have to parse that. It's a pretty extreme case, an anonymous record value containing three different arrays, one an array of integers, one an array of strings including quotes, and one an array of anonymous two-field row tuples. But seriously ... use plain relational modelling and CSV export, the convenience of composite types, hstore, and arrays aren't worth the compatibility pain if you know you'll be doing lots of cross-DB work.

It's actually possible to use them in your schema, you'll just need to COPY FROM a set of queries that expands them into appropriate relational structures. Since you're going to have to do that, you might as well use regular relational modelling in the first place and save yourself some pain.

Enums are OK; you can just treat them as strings when loading them. They're just a convenient and performant shortcut for a side table and a FK constraint anyway, allowing you to avoid joins and sorts.

In modern PostgreSQL, bytea is output as hex strings. In older versions, it's output as octal escape sequences. Either way, you can load it into a varchar column in Oracle then convert it if you can't get Oracle to natively understand the representation. I haven't dealt with Oracle binary types, so I can't help much more there.


I fixed the issue by adding below lines in /etc/mongod.conf

replication:
   replSetName: yogi


Your steps are correct and you do not need to truncate or drop as you will be generating new snapshot for that one article.

Few things to check:

When you drop article, also drop subscription.
Make sure immediate_sync property of your publication is set to
false so you do not end up with a full snapshot (of all articles).

These two threads has details about where things might go wrong.

Add article to transactional publication without generating new snapshot
http://ansqldba.blogspot.com/2012/02/adding-new-article-to-existing.html


Oracle Stopping - Can't Login All Of A Sudden -

Start monitoring memory usage. Does your application terminate old sessions? If not, there is a connection leak that eventually takes away all processes your database instance can use and prevent new connections. There also could be a memory leak, that is why monitoring memory and swap could be smart to do.

Most likely to me seems to be the connection leaking. You can easily monitor this by making counts of v$session or v$process. These counts should not grow unlimited.
Since you use jdbc (thin client I asume) Dead Client Detection could also be useful. This gives the rdbms the ability to clean up unused sessions. Unused sessions are those that do not react upon a ping from the server.


Cassandra multi-DC:write on LOCAL and read from any DC

The data is always written to all DCs, the LOCAL consistency levels are just saying that confirmation should come from the local nodes, not from other DCs...
From the DSE Architecture guide (also look to image there):

In multiple datacenter deployments, DataStax Enterprise (DSE) optimizes write performance by choosing one coordinator node. The coordinator node contacted by the client application forwards the write request to one replica in each of the other datacenters, with a special tag to forward the write to the other local replicas.
If the write write consistency levels is LOCAL_ONE or LOCAL_QUORUM, only the nodes in the same datacenter as the coordinator node must respond to the client request for the request to succeed. Use either LOCAL_ONE or LOCAL_QUORUM to reduce geographical latency and lessen the impact on response times of client write requests.

P.S. I recommend to read this guide to understand how Cassandra/DSE works - it makes things much simpler if you have this information.


Can SQL Server 2016 log files be applied on SQL Server 2012

No we won't be able to do that. This is because the internal file structure is different.

You can use third party tools like SQL Compare and SQL Data Compare to do "diffing" between your source and target, generate update scripts from those differences, and then execute those scripts on the target platform; this works across different SQL Server versions.

I hope this helps


The interval calculation is done in whatever timezone your session is set to.  This is documented here:

When adding an interval value to (or subtracting an interval value from) a timestamp with time zone value, the days component advances or decrements the date of the timestamp with time zone by the indicated number of days, keeping the time of day the same. Across daylight saving time changes (when the session time zone is set to a time zone that recognizes DST), this means interval '1 day' does not necessarily equal interval '24 hours'

I don't know what your session timezone is set to (you can do show timezone; to see), but apparently it is not 'Europe/Berlin'.  When set to 'Europe/Berlin', I get the answer you expected.  Whatever your session timezone is set to either doesn't do DST, or doesn't implement the transition during the time period in question. All if its days during this period really are 24 hours.


How to check if range between two numbers is available in table of ranges

select count(*)
from ( select RangeTo, LEAD(RangeFrom, 1, 0) OVER (ORDER BY RangeTo)
                       AS LeadRangFrom
       from table
     ) ss
where @RangeFrom > RangeTo
  and ( @RangeTo < LeadRangFrom or LeadRangFrom is null)

If count(*) is one then you can insert
Is that the question you are asking?


Deleting rows from a table with a very small amount of expansion space

As things stand, the query:

DELETE TOP (4000)
FROM   [OLD_TABLE] WITH (TABLOCKX)
WHERE  [Date] < '2014-01-01 00:00:00'

...has to scan the whole heap table, testing each row it finds, until it eventually finds 4000 to delete. On the next iteration, the whole business starts again from square one. Assuming the scanning process is performed in the same (allocation unit) order each time, these scans will take longer and longer to find 4000 rows as time goes by.

Creating an index on the [Date] column will allow SQL Server to find the 4000 rows much more efficiently. It does add a small overhead to each delete (as the new nonclustered index needs to be maintained as well) but this is nothing compared to the effort that will be saved by not performing a scan each time. The index will also require a certain amount of space, but it should not be too large. You should create the following index before resuming your data removal process:

CREATE NONCLUSTERED INDEX <index_name>
ON dbo.OLD_TABLE ([Date]);

By the way, if the database has snapshot isolation or read committed snapshot isolation enabled (even if not actively used!), the TABLOCKX hint will not be enough to ensure that empty heap pages are deallocated. Your heap table may therefore contain many empty pages - a concern since you are so low on space.

The standard way to address this space management issue is to create a clustered index (or issue an ALTER TABLE REBUILD statement, but that requires SQL Server 2008). It seems that creating a clustered index might also not be an option for you, due to space constraints. There isn't an obvious way to resolve this right now, given the space issue.

One thing to keep an eye on while the delete is progressing is the space used by the transaction log. If the database is in FULL or BULK_LOGGED recovery, you will need to keep on top of backing up the log. If the database is using SIMPLE recovery, you may need to issue a manual CHECKPOINT from time to time to release transaction log space for reuse (otherwise the physical file might grow).

If you can get hold of some additional temporary storage, a better way might be to bulk export the data you want to keep to a safe location, drop the table, recreate it (preferably with a clustered index!) and reload the saved data. This is generally faster than the incremental-delete process, but it depends on your objectives and priorities.


What is the most efficient way to merge topics of a forum or blog?

Instead of storing the merge_id in the topic table store the id of the base topic, the one this topic's posts now appear under. A null would show the current topic has not been merged, or is the base topic into which others have been merged. To merge a new topic into an existing set would only require setting that one row's base_topic_id.


SQL, creating a new table with auto generated passords from other tables

I don't have a Database to test this on, but Potentially, you could use DEFAULT, RAND() and REPLACE() to do this.

CREATE TABLE Users(
     ID int NOT NULL,
     LastName varchar(255) NOT NULL,
     FirstName varchar(255),
     Password varchar(255) DEFAULT REPLACE(FLOOR(RAND()*(987654321-123456789+1))+123456789,'1','!')
 );

Just keep adding more REPLACE to make the Password a bit more Complex with each.


What Method / Formula does a Nested Loops Operator use for row estimation?

SQL Server estimates cardinality for joins, not physical operators. The inner join in question will have the same estimate regardless of the physical operator employed (hash, merge, nested loops, or apply). The physical operator may affect the display, but the selectivity of the logical join is the same.
With that out of the way, logical join estimation is still a complex topic. There are many valid ways to produce an estimate. Two main alternatives are well covered by Dimitry Piliugin in Join Estimation Internals in SQL Server. You can also find general differences between the original and latest CE models in the Microsoft paper Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator.
One main difference occurs when using histograms to estimate join selectivity. The more recent CE model uses coarse alignment, as I describe in SQL Server Join Estimation using Histogram Coarse Alignment. The original CE used fine alignment with linear interpolation for each step, which can give more accurate estimates, but can also be more variable.
You may notice that using Simple Join (with trace flag 9479 given by Dima) gives a 'perfect' estimate for your test query.

There is a TF that forces the optimizer to use Simple Join algorithm even if a histogram is available. I will give you this one for the test and educational purposes.

In other (more common) cases, simple join will give a terrible result. Such is the nature of cardinality estimation.
Related:

How does SQL Server's optimizer estimate the number of rows in a joined table?
Apply cardinality estimation problem in SQL Server


SQL Server Automate refresh of one table only from one environment to another

It's a question of preference. Please see below some advantages/disadvantages I found working with each of them.

Replication (Transactional) - works excellent as long as you don't need to touch the table. It's real time so you will make your developers/testers really happy with "fresh", almost real-time data. It becomes tricky if you need to add a column, remove a column or if your table structure changes because in almost all cases you need to reinitialize the replication from scratch, but for just one table of a few MB it will work fast, this becomes an issue from a few GB on because it takes time. If you set up the replication with your account make sure it will not expire or get disabled because you will crash the replication; best is to use a service account. Another thing to consider is networking & server resources - transactions getting replicated real time will 'suck up' some network and some of the server memory and I/O, but unless your table gets a few hundreds rows (or more) modified every second I don't see that as being a real problem. You have a nice tutorial on how to set this up here.
SSIS - you will not have (almost) real time data in your staging environment. So if any of your developers or testers have the idea of comparing data between prod an stage - refresh time needs to be considered. If you change the table structure you need to modify the package (always make sure to also refresh the metadata inside the package). My experience is that you work more to keep a SSIS package up to date than you work to recreate replication, but this is subjective to personal preference and requirements. Also if you set up your package as a 'truncate & load' you will delete and write again the same data every day no matter if your source table gets updated or not. If you don't want that you need to add some logic in your package and that can be complicated and lead to data loss or duplicates if you don't know your data very well. Another thing to consider is your access rights: if you want to deploy it you need SQL Admin-like rights, if you want to run this from a local folder, you need RDP rights to that machine and write permissions to copy your package to a local drive on the server. You still need a SQL Agent job to automate it (I assume you don't want to manually run this). Please keep in mind that you need to use Visual Studio with BI add-on to create the package. You find documentation about setting up the SSIS package here and a nice tutorial here.
TSQL Script - SQL Agent Job - This is also my personal preference as it is the easiest to set up and maintain, specially if you have no experience with SSIS and/or replication. Just like for SSIS, you will not have  (almost) real-time data. You have much more control and it's easier to change if your table structure changes. Depending on what kind of changes you can have on your source table it can get tricky to synchronize your data, but you always have the option of a truncate table and insert everything from production every time (highly NOT recommended for big tables and even if your table is small today, consider how big it will be in 3-5 years!). When I set up jobs like this I usually take my time to understand the data and all the changes that might happen and I use a merge statement as much as possible. If you have a insert date column in your prod table, then you can work your logic around that. When you set up the SQL Agent job make sure you change the owner to a service account, otherwise the job will crash if your account gets disabled.

For point 2 & 3 consider doing some index work right after you insert/modify the data to keep your performance to a high level.


How to find the specific file in a filegroup in which an object resides

All of them had at least one file with auto-growth enabled and disks hosting them had enough free space available. So then why my index maintenance failed?

As it says in the product documentation

Reorganize and Rebuild Indexes: (emphasis added)

The ALTER INDEX REORGANIZE statement requires the data file containing the index to have space available, because the operation can only allocate temporary work pages on the same file, not another file within the filegroup. So although the filegroup might have free pages available, the user can still encounter error 1105...

But what if I want to continue having my index in same file so that my reorg can continue? Is that possible?

Yes, you just need to ensure that the necessary space exists beforehand, or that the file that needs to grow can do so.

Also, is it possible to find out in which file my DB object resides?

This is not as straightforward as one might expect. To get down to the file level accurately, you need to use the undocumented sys.dm_db_database_page_allocations (SQL Server 2012+) or DBCC IND.
A correct query to do this can be found on this Database Administrators Q & A:
Does there exist a way to determine the exact file that contains an allocation unit in a filegroup of multiple files?


Reasons why batch generates more than one query plan?

Each query in the batch needs a plan. And furthermore, each procedural function (scalar function or multi statement table valued function) in the batch needs a plan too, which is often less obvious.


My understanding of your requirements: For each event calculate a number based on the prior events (including the current one).
That is indeed possible by using a window function:

SELECT id, event, addend, SUM(addend) OVER (ORDER BY id ASC) AS count FROM (
  SELECT id, event,
  CASE WHEN event = 'entry' THEN 1 WHEN event = 'exit' THEN -1 ELSE 0 END AS addend
  FROM events
) AS events_with_addend

The type of event does not contain any information we can use in the aggregate sum, so we have to provide it (in the inner query).

Instead it is preferred to create a separate table event_types containing the name of the event as well as the change applied to count by it, then referencing event_typesfrom events (look up database normalization) and losing the inner query.

The actual window function then just sums up the addends of each row until (including) the current one (the window frame).

It is very possible this approach might lead to performance problems depending on the (growing) count of rows. If it does, have a look into materialized views


MySQL queries to some tables in information_schema kill all connections to server

It turns out there was some corrupt index.

First, I queried my server to find the log file location: show variables like 'log_error'

Then I tail -f /path/to/error_log and ran the offending query again.

It shows up in the logs like this:

2017-07-12T18:12:26.860805Z 3 [ERROR] InnoDB: Flag mismatch in page [page id: space=124998, page number=130] index `index_some_table_on_some_column` of table `some_database`.`some_table`
2017-07-12 13:12:26 0x700000d51000  InnoDB: Assertion failure in thread 123145316274176 in file btr0btr.cc line 173
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
18:12:26 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=402653184
read_buffer_size=2097152
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1013751 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7fa999103c00
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 700000d50e90 thread_stack 0x40000
0   mysqld                              0x00000001061493e7 my_print_stacktrace + 61
1   mysqld                              0x00000001060c9965 handle_fatal_signal + 690
2   libsystem_platform.dylib            0x00007fff8caff52a _sigtramp + 26
3   ???                                 0x0000700000d48820 0x0 + 123145316239392
4   libsystem_c.dylib                   0x00007fff90ebc6df abort + 129
5   mysqld                              0x000000010632df11 _Z23ut_dbg_assertion_failedPKcS0_m + 161
6   mysqld                              0x0000000106171693 _ZL22btr_root_fseg_validatePKhm + 0
7   mysqld                              0x0000000106171f86 _Z12btr_get_sizeP12dict_index_tmP5mtr_t + 51
8   mysqld                              0x00000001061d2467 _ZL24dict_stats_analyze_indexP12dict_index_t + 305
9   mysqld                              0x00000001061d41a9 _Z17dict_stats_updateP12dict_table_t23dict_stats_upd_option_t + 558
10  mysqld                              0x00000001061d45cf _Z17dict_stats_updateP12dict_table_t23dict_stats_upd_option_t + 1620
11  mysqld                              0x0000000106227518 _ZN11ha_innobase4openEPKcij + 1236
12  mysqld                              0x0000000105b89394 _ZN7handler7ha_openEP5TABLEPKcii + 48
13  mysqld                              0x000000010608dc6d _Z21open_table_from_shareP3THDP11TABLE_SHAREPKcjjjP5TABLEb + 3442
14  mysqld                              0x0000000105f9f886 _Z10open_tableP3THDP10TABLE_LISTP18Open_table_context + 3292
15  mysqld                              0x0000000105fa1c7b _Z11open_tablesP3THDPP10TABLE_LISTPjjP19Prelocking_strategy + 1185
16  mysqld                              0x0000000105fa2d9c _Z21open_tables_for_queryP3THDP10TABLE_LISTj + 69
17  mysqld                              0x000000010603a570 _ZL25fill_schema_table_by_openP3THDP11st_mem_rootbP5TABLEP15st_schema_tableP19st_mysql_lex_stringS8_P18Open_tables_backupb + 603
18  mysqld                              0x000000010603a202 _Z14get_all_tablesP3THDP10TABLE_LISTP4Item + 4161
19  mysqld                              0x0000000106040c2a _Z24get_schema_tables_resultP4JOIN23enum_schema_table_state + 721
20  mysqld                              0x000000010602a583 _ZN4JOIN14prepare_resultEv + 129
21  mysqld                              0x0000000105fc1a95 _ZN4JOIN4execEv + 229
22  mysqld                              0x000000010602a0de _Z12handle_queryP3THDP3LEXP12Query_resultyy + 558
23  mysqld                              0x0000000105ffe9cb _ZL21execute_sqlcom_selectP3THDP10TABLE_LIST + 855
24  mysqld                              0x0000000105ff94f7 _Z21mysql_execute_commandP3THDb + 1959
25  mysqld                              0x0000000105ff8361 _Z11mysql_parseP3THDP12Parser_state + 842
26  mysqld                              0x0000000105ff7291 _Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command + 5090
27  mysqld                              0x0000000105ff7e18 _Z10do_commandP3THD + 414
28  mysqld                              0x00000001060b0941 handle_connection + 432
29  mysqld                              0x000000010638013c pfs_spawn_thread + 308
30  libsystem_pthread.dylib             0x00007fff8e60a99d _pthread_body + 131
31  libsystem_pthread.dylib             0x00007fff8e60a91a _pthread_body + 0
32  libsystem_pthread.dylib             0x00007fff8e608351 thread_start + 13

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fa99912ea30): SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS
Connection ID (thread ID): 3
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

In the interest of time, and because this happened to be an old database whose data could not have changed since the last backup I had, I just reimported its data, and that solved the issue.


How to handle MySQL database connections/pooling from Lambda -> RDS with Node

Spinning off thousands of processes to do tiny pieces of a task is simple.  But inefficient.  "Processes" are heavyweight.  Threads are somewhat heavy.  Thousands of connections to MySQL is possible, but not all at the same time.

In any multi-process or multi-threaded environment, having "too many" will slow things down.  This is because the OS (or some entity) is doing a lot of work to share the inadequate resources.  MySQL, for example, can handle hundreds of idle connections, but bogs down with more than a few dozen active connections.  When that is exceeded, it is best to go up the chain to the client, and throttle how often it is spawning new connections.

I am not familiar with the other products you mention, but I would recommend keeping simultaneous activity down to dozens, not hundreds.

Or find a way. in your application, to "iterate" instead of "recurse".  Meanwhile, keep in mind that MySQL is happier to handle "vectors" (tables) of data than to handle individual rows.  Maybe you can push some of what is "parallel" in the app down into MySQL as "vectors"?  Win-win:  Fewer connections; more efficiency in MySQL.


T-SQL Query runs quickly on local PC, but slow on SQL Server?

A few of the most common scenarios for occasionally slow, but usually fast, SELECT queries:

A suboptimal plan is being used - perhaps due to bad stats, parameter sniffing, or other factors. In your case, seems unlikely that it would be parameter sniffing, if you really mean both no explicit parameters and no literals (e.g. hard-coding WHERE x = 1 is still parameterized in most cases). In order to make any useful guesses about this, you'll need to supply actual execution plans after the successful completion of one of the "slow" versions of the query.
Your query is getting blocked - again, this is not likely to be your issue either, since you say you are the only one working on this database. But to determine if you are being blocked, when you observe the query is running slow, do this in one window:

PRINT @@SPID;

Once you have that output, run the slow query in that same window:

SELECT ... query that runs slowly ...

Now, in a new window, run this:

SELECT command, status, wait_type, last_wait_type, blocking_session_id
  FROM sys.dm_exec_requests WHERE session_id = <@@SPID FROM ABOVE>;

Make note of the wait type (see below), but if blocking_session_id is not 0, check what that user is running (run the query above again, changing the session_id to the blocker).
Some other resource is bottlenecked - it could be that at certain times the underlying disk that the database is on or the network you're moving data across can be under extreme pressure, and this will filter down to just about every operation that uses them. This could also be true if your SQL Server is on a virtual machine and the underlying host is under some kind of resource pressure.

In addition to the execution plan you get from the slow version of the query, you should examine the waits that are experienced while your query is running. This is beyond the scope of this localized answer, mostly because it is a very lengthy topic to digest, but you can start with this white paper from Microsoft.

The free execution plan analysis tool, Plan Explorer, tries to make problems with a given execution plan a lot more obvious than it will ever be in Management Studio. As a bonus, the paid version, which you can trial for free for two weeks, also gives details about the exact waits that your session experienced - this is pretty cumbersome to do on your own. Sorry about the ad but it really can be a time-saver that helps isolate the real problem behind a particularly nasty query.


SQL Server 2012 setup fails immediately (Error result: -2067529723)

I rebooted the VM, and I was then able to get the Setup to work as expected. I am disappointed that the error message did not provide any indication of what was wrong or what needed to be done to correct the situation, but rebooting seems to have resolved whatever the issue was.


Issues with mysqldump after upgrade 5.5 -> 5.6

The problem here is just a misunderstanding
Using --flush-logs with mysqldump simply closes and opens all files handles against the following:

Error Log
Engine Logs (Redo Logs Flushed to disk)
Relay Log (If Replication is setup)
General Log (if Enabled)
Slow Log (if Enabled)
Binary Logs (if Enabled)
See MySQL Documentation under Bulletpoint #3 ([log_type] LOGS)

In the case of binary logs, the current binary log is closed and a new binary log is open. In essence, it is the only log file that is numerically rotated For example, suppose you run SHOW MASTER STATUS; and you see the binary log file names mysql-bin.000027. Running FLUSH LOGS; or FLUSH BINARY LOGS; would close mysql-bin.000027 (which stops recording transactions to that file) and open mysql-bin.000028 (which now records incoming transactions).
However, rotation does not imply automatic deletion.
There are two ways to clean up the binary logs
DELETE ALL BINARY LOGS
If the DB Server is not used as MySQL Replication Master, then run
BINARY LOGGING IS ENABLED
mysql> RESET MASTER;

That will erase all binary logs and start with the first one (like mysql-bin.000001)
BINARY LOGGING IS NOT ENABLED
Simply go the the OS and run the delete command (rm for Linux,del for Windows)
PURGE BINARY LOGS
If you want to keep binary logs from the last 48 hours, you run
mysql> PURGE BINARY LOGS BEFORE NOW() - INTERVAL 48 HOUR;

mysqld will erase all binary logs older than 48 hours.
AUTOMATIC PURGING
Simply set the following in my.cnf (or my.ini)
[mysqld]
expire_logs_days=2

Then, either restart mysqld or run the following as root
mysql> SET GLOBAL expire_logs_days = 2;

That way, every manual or automatic flush of binary logs will delete logs older than 2 days.
Once expire_logs_days is set, mysqldump will cleanup old logs every time it does  --flush-logs.


Which index shall I create in Postgres to speedup join of two big (several million rows) tables connected one-to-many?

It is quite reasonable for the planner to think that scanning 70,000,000 rows in sequential order is going to be faster than reading 310,075 in a random order.
If you tried disabling enable_seqscan and the query performance got worse, then it seems likely the planner is making the correct decision there.  Could you post an EXPLAIN (ANALYZE) of what happens in that case?

If it did a merge join on the index attributes_point_id_idx like you suggest, than for each row it returns it would have to jump to some random part of the attributes table to check that that row has not yet been deleted, to check the attribute_id to see if it is 0, and to fetch value so it can be returned.

If you make an index on (point_id, attribute_id, value) and you keep the table vacuumed, then it might choose to use an index only scan (as the input to the merge join) as now it can get all the data it needs from the index, no randomly ordered visit to the table is needed.

It could be a challenge keeping the attributes table adequately vacuumed.  The autovacuum system is only designed to reduce bloat.  Keeping index-only-scans efficient is not yet part of the autovacuum design.


Will an hourly T-Log backup in a Maintenance plan write a file if there are no changes to a DB?

Review the backupset and related tables in msdb as the source for if a successful backup was taken.  If you see gaps in when a scheduled backup was to have occurred, your job either failed or never ran.

Every backup command generates an internal checkpoint, which in turn, moves the log sequence number (lsn).  Review the first_lsn and last_lsn pattern with every log backup in the backupset table.  Likewise, a file will always be generated if your job backs up to a time-stamped filename (default behavior of maintenance plans).  You can verify this behavior by manually/interactively running a log backup command multiple times on a database without an transactions between runs.


No SA password. No SQL Server Management Studio. No OS authentication

Well, you can start by installing Management Studio. (How else do you plan to perform maintenance tasks once you have access to the instance?) I recommend the most recent version of Management Studio which is fully functional, manages 2005 instances just fine, and is completely free of any licensing requirements. You can download from here:

Download SQL Server Management Studio

(If your operating system is 32-bit, you should fix that first; if you can't, you can download the 32-bit version of SSMS 2014 SP1 toward the bottom of this page.)
Once you have Management Studio installed, what you do next will depend on your SQL Server version, as well as whether you can afford to take the SQL Server service down in order to establish new credentials. The first two methods here do not require restarting the instance:

For SQL Server 2005, 2008, and 2008 R2 instances
You can connect using the NT AUTHORITY\SYSTEM account (or other backdoor methods). There are some details in some of the answers here:

How to add sysadmin to user in SQL Server 2008 when no sysadmin accounts exist

I also have a tip over on MSSQLTips.com that addresses this problem:

Recover access to a SQL Server instance

Essentially, you download PSExec from Microsoft, then use it to launch Management Studio once you have it installed:
PsExec -s -i "C:\...\Ssms.exe"

This will connect as NT AUTHORITY\SYSTEM and will allow you to do things in Object Explorer, like:

Change the instance to SQL Server and Windows Authentication mode - right-click the Server name, hit properties, and change the radio button if it's currently set to Windows only:

Set the password for the sa account - expand Security, expand Logins, right-click sa and hit Properties, and in the resulting dialog there will be two password entry fields:

Add your own login as a sysadmin - right-click Logins, New Login... enter your login name (in the form DOMAIN\username) then move to the Server Roles tab and check the sysadmin box and click OK:

(or, if your login is already listed, right-click, Properties, and make sure sysadmin is checked under Server Roles)

For SQL Server 2012 and newer instances
Starting with SQL Server 2012, NT Authority\SYSTEM was no longer given rights to SQL Server by default. So another way to do that in these newer versions has been detailed by Argenis Fernandez:

If the SQL VSS Writer service is running, stop it, and suspend all maintenance plans or 3rd party backup software that might rely on it.

Open regedit.exe and change the value of HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\SQLWriter\ImagePath to point to SQLCMD.exe, which is going to be in C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\**<...110|120|130|140...>**\Tools\Binn. After editing, the registry value should look something like the following (sorry for the scrolling):
"C:Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\SQLCMD.exe" -S .\instancename -E -Q "ALTER ROLE sysadmin ADD MEMBER [YourDomain\YourUserName];"

Try to start the SQL VSS Writer service again (you'll get an error; that's okay).

You should now be able to connect as sysadmin using YourDomain\YourUserName. So stop the SQL VSS Writer service, fix the registry, and restart the service (if you need it to be running, or if it was running before you started this).

I've gone through this in a lot more detail in a second tip:

More on Recovering Access to a SQL Server Instance

Though when I wrote that tip I used a more cumbersome approach of making a copy of SQLCMD.exe and replacing sqlwriter.exe - much easier to just point the service at SQLCMD.exe directly.

If you can afford to take the SQL Server service down
There is an officially supported path from Microsoft that requires restarting the instance in single user mode:

Connect to SQL Server When System Administrators Are Locked Out

There is also a function in dbatools.io, a Powershell solution for managing SQL Server, called Reset-DbaAdmin:

https://dbatools.io/functions/other/reset-dbaadmin/

Security is not the main issue here
I see plenty of people calling for Microsoft to "fix" these so-called "vulnerabilities." These are valid approaches to recovering access to an instance of SQL Server that you rightfully own. They all require elevated privileges on the physical host where SQL Server resides; as I've said to several people, if you don't want developers messing with SQL Server installations, don't make them administrators.


ORA-00942: table or view does not exist- Can it be solved by granting privileges to user?

Take a look at what Oracle says about this type of error.

  ORA-00942 table or view does not exist

  Cause: The table or view specified does not exist, a synonym that is not allowed here was used, or a view was referenced where a table is required. If you are trying to access tables or views of another schema then you might not have required privileges to do it.
  Action: You can-
      check the spelling of the table or view name.
      check that an existing table or view name exists.
      contact the DBA if the table needs to be created or if user or application privileges are required to access the table.

The following is the example to address your situation. It shows how grant SELECT object privilege and how to create synonym.

SQL> create user userA identified by userA;

User created.

SQL> grant connect, resource to usera;

Grant succeeded.

SQL> conn userA/userA
Connected.
SQL> create table tab (id number);

Table created.

SQL> conn / as sysdba
Connected.
SQL> create user schema1 identified by schema1;

User created.

SQL> grant connect, resource to schema1;

Grant succeeded.

SQL> conn schema1/schema1
Connected.
SQL> create table tab1(id number);

Table created.

SQL> select * from usera.tab;
select * from usera.tab
                    *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> conn usera/userA
Connected.
SQL> grant select on tab to schema1;

Grant succeeded.

SQL> conn schema1/schema1
Connected.
SQL> select * from usera.tab;

no rows selected

SQL> create synonym for usera.tab;
create synonym for usera.tab
               *
ERROR at line 1:
ORA-00995: missing or invalid synonym identifier

SQL> create synonym usera_tab for usera.tab;
create synonym usera_tab for usera.tab
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> conn / as sysdba
Connected.

SQL> grant create synonym to schema1;

Grant succeeded.

SQL> conn schema1/schema1
Connected.
SQL> create synonym usera_tab for usera.tab;

Synonym created.

SQL> select * from usera_tab;

no rows selected

SQL>


How to stop MySQL from replicating CREATE TABLE... on Slave for ignored databases

binlog_do_db = dbname says to replicate any statement when the default database is dbname.  So be careful what USE statement precedes the statement:

USE non_repl_db;
CREATE TABLE ...;


SQL Server restore full backup + partial differential backups

Not sure what you expect from partial backups, but they are only useful for read-only filegroups:

  partial backups are designed for use under the simple recovery model to improve flexibility for backing up very large databases that contain one or more read-only filegroups.

Differential backups are a different story, but they are not used as you use them. They are supposed to speed to up a full restore by skipping over some log backups, not as a replacement for full backups.

Is rather unclear what you actually have. I guess you have a full backup and 130 differential backups. In such case you can restore the original full and then apply the last differential.

My suggestion is to implement a correct backup plan (full recovery mode, periodic full backup and frequent log backups). There are many examples how to do this. Start with reading Introduction to Backup and Restore Strategies in SQL Server. Go over some tutorials like the ones from SQL Server Backup
Best Practices and Articles. Understand what you're doing, don't guess.


The file size of the transaction log is in no way related to the size of the data files, so the question you've asked is really a non-starter to troubleshoot performance.

The transaction log stores the physical operations that happen to the database. Since the database is in SIMPLE recovery, the log only holds on to the operations until a CHECKPOINT occurs -- at this time, the transaction log that's no longer required is internally cleared and can be reused. The physical file size would not need to change unless the active transactions take up more space than is physically available. (Yes, I've simplified things a bit in this paragraph.)

That said, your transaction log could suffer from performance issues if there are too many VLFs, likely from the result of small autogrowth increments -- see my answer here for a bit of information about how the transaction log works internally with regards to this. It also depends what the workload is for this database (many small transactions, or few large transactions).


Deny users permission to create tables in master

You'll need to determine how the logins have access to run DDL in master.  The following query will return a list of logins with server-level role membership:

SELECT ServerName = @@SERVERNAME
    , RoleName = roles.name
    , MemberName = members.name
    , IsEnabled = CASE WHEN members.is_disabled = 1 THEN 0 ELSE 1 END
FROM sys.server_role_members srm
    INNER JOIN sys.server_principals roles ON srm.role_principal_id = roles.principal_id
    INNER JOIN sys.server_principals members on srm.member_principal_id = members.principal_id
WHERE members.name NOT LIKE 'NT %' --exclude builtin principals
ORDER BY roles.name
    , members.name;

You can use xp_logininfo to understand how a particular Windows account has access to SQL Server:

EXEC xp_logininfo @acctname = 'DOMAIN\Username', @option = 'all';

If the people in question have membership in the sysadmin role, you won't be able to prevent them from running DDL statements in master (or anywhere else for that matter).

Presuming the people are not members of the sysadmin server-role, then DENY permissions take precedence over permissions that have been GRANTed.

You could create a Windows security group, named "NoMasterDDL" and add your developers to that group.  Create a login for the group, then DENY permissions on the DDL statements you don't want people to perform in master.

USE master;
CREATE LOGIN [DOMAIN\NoMasterDDL] FROM WINDOWS;
CREATE USER [DOMAIN\NoMasterDDL] FOR LOGIN [DOMAIN\NoMasterDDL];
DENY ALTER ON DATABASE::master TO [DOMAIN\NoMasterDDL];

The above will prevent members of the [DOMAIN\NoMasterDDL] group from performing any DDL type statements, such as CREATE TABLE.

As I said in answer to your previous question, and AMtwo mentioned in a comment:

If a user is currently a member of sysadmin, any DENY permissions will have no effect. Particularly in a development environment, developers might have such access. If so, the CONTROL SERVER permission can be used instead; it is the same as sysadmin, but obeys DENY permissions.

So, you can grant CONTROL SERVER instead of making them members of the sysadmin server role, then use DENY ALTER as shown above to prevent DDL in the master database.


InnoDB table vs MEMORY table for a mysql environment with many inserts

Clearly, it will be faster and more efficient to write/read to/from memory. In your case, losing the data is not a big issue as you stated. However, you need to be careful about the memory usage and limit the number of rows stored.


ORDER BY items must appear in the select list if SELECT DISTINCT is specified

The real issue is caused by data type conversion. If you add this expression to the select list:

CASE WHEN c.ID > 10 THEN c.Title
        ELSE c.ID
        END

you will see another error. Something like: "Conversion failed when converting the nvarchar value 'Some title' to data type int". According to data type precedence when you compare varchar with int values (SQL Server will need to compare Title with ID to sort your final result set) varchar should be converted to int because int has higher precedence.
https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver15


Can we assign mysql Key_buffer_size value for particular table?

You can't give this value for a particular table this value indicates Index blocks for MyISAM tables are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache.

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_key_buffer_size

InnoDB engine type does support similar option called innodb_buffer_pool_size

The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The default value is 8MB. The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. However, do not set it too large because competition for physical memory might cause paging in the operating system.


Is it possible to encrypt a column and NOT have to change app code?

This is what Always Encrypted was created for, but you'll have to wait for SQL Server 2016 (and for the jTDS drivers to catch up).

In the meantime, your Java app could call stored procedures, such that the data is encrypted there. That's really not going to change the fact that the decrypted text is being sent over the wire, but it does mean you can isolate query changes from the app...


Dynamic ORDER BY clause in PL/SQL cursor

You can't dynamically change the column you're sorting by using static SQL.  You'd need to use dynamic SQL.  Something like this should work

create or replace FUNCTION someFunction(SortCol1 varchar2 default 'column1',
                                        SortDir1 varchar2 default 'ASC')
RETURN STUFF pipelined
AS
    c1 SYS_REFCURSOR;
    c1_rec table1%rowtype;
BEGIN
    OPEN c1
     FOR 'select * from table1 order by ' || sortCol1 || ' ' || sortDir1;

    LOOP
        FETCH c1 INTO c1_rec;

        <<do something>>
    END LOOP;

END someFunction;


is there a term for databases which are not system databases?

"User Databases".

The same "System" vs "User" distinction is also made for other objects such as tables.

For example

  Members of the db_datareader fixed database role can read all data
  from all user tables.

msdb and the distribution database (if you use replication) are also system databases that you do not mention by the way.


You can't use double quotes for string literals. Do include a single quote in a string literal, it has to be escaped by doubling them:

query := 'unload (sql) to ''s3_loc/user-data.csv'' iam_role ''role''';

Additionally can't use names of variables from PL/pgSQL in a SQL string. You need to include the content of that variable in the query string you build:

query := 'unload ('||sql||') to '''||s3_loc||'/user-data.csv'' iam_role '''||role||'''';


Prevent unique constraint violations caused by concurrent inserts

Failure to wrap in a transaction is causing your problem.
do
    compute random number
    BEGIN;
    SELECT ... FOR UPDATE;
    if row exists, then start loop over
    INSERT ...;
    COMMIT;
until success

And don't worry about transaction isolation mode.
Or...
do
    compute random number
    INSERT ...;
until success

with autocommit = ON; no worry about isolation mode.
Or...  Use an AUTO_INCREMENT column -- MySQL will deal with guaranteeing a unique id every time.  (Caveat: A little more code is needed on multi-master topologies.)


how to enable temporal database capability in mysql.?

Add a from_date field, and a nullable to_date field
expand your primary key to include from_date
keep everything in UTC
consider using PostgreSQL as there is a temporal extension for preventing overlapping time periods.

Here is the book from "THE" temporal db guy (Snodgrass):

http://www.cs.arizona.edu/~rts/tdbbook.pdf

Also try Chapter 4 of Celko's Smarties book


How do foreign keys affect partial and transitive dependencies (databases)

If you quote & use definitions for FD (functional dependency), partial FD & transitive FD & for when they hold in a table (value or variable), you will see that they have nothing to do with FKs (foreign keys) or any other table(s).

Also you will see that they have nothing to do with an everyday notion of "derived from". A set of columns is "(functionally) dependent on" another  set or single column when/iff certain conditions are true, as given in the definitions.

Also you will see that the FDs that hold in a table variable are determined by all the values that can arise for it. That is determined by the condition for rows going in the variable in a business situation & all the possible business situations that can arise. But it has nothing to do with whether (a part of) a variable's value "can change after" it holds some different value.

Also if you quote & use definitions for 2NF & 3NF you will see that it is only certain partial and transitive FDs that matter to those NFs.


First insert

The actual plan for the first insert statement reveals some things you should improve:

I would add an OPTION (RECOMPILE) hint to this query. This will allow the optimizer to produce a plan taking into account the runtime values of variables. This should simplify the plan quite a bit.
Once the hint above is in place, you should ensure the Uploads table has an index with ClientID, UploadCompleteDttm as the leading keys. This will provide the required sort order, and allow a seek when @startdate_local is not null.
The index should also include FileName, FileUploadID, ProcessErrorText to avoid lookups. An example index definition would be:

CREATE INDEX IndexName
ON fileManager.FileUploads (ClientID, UploadCompleteDttm)
INCLUDE (FileName, FileUploadID, ProcessErrorText);

The Clustered Index Scan on FilesReceived looks to be very expensive. Five and a half billion rows are tested (across multiple scans) to return around 5,000 rows. This cost is well hidden by the residual predicate (as shown in Sentry One Plan Explorer):

Add an index to FilesReceived keyed on FileUploadID. If that index already exists, and the scan persists in the actual execution plan after the previous steps listed here have been applied, use a FORCESEEK hint on the FilesReceived reference. You may also try OPTION (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'));.

Second insert

The problematic part that jumps out at me is:

This corresponds to the code:

(select count(1)
     from fileManager.FilesReceived fr
      join fileManager.Tracking t on fr.FilesReceivedID = t.FilesReceivedID
     where parentfileid = z.FilesReceivedID) as 'HasChildren'

The rest of the execution plan is quite large, but does not seem too terrible. I would still try OPTION (RECOMPILE) again, for the same reasons as for the first query.

If that does not improve performance enough on its own: I would remove the code fragment shown above from the query, persist the result rows (only 5,239 for the example given) in a separate temporary table, then find the HasChildren value as a separate step. I would also rewrite the final INSERT to use an explicit TOP (5) clause instead of using SET ROWCOUNT 5.

Ensure the FilesReceived table has an index on ParentFileId.


Finding all joins required to programmatically join a table

I had a script that does a rudimentary version of foreign key traversal.  I adapted it quickly (see below), and you might be able to use it as a starting point.

Given a target table, the script attempts to print the join string for the shortest path (or one of them in the case of ties) for all possible source tables such that single-column foreign keys can be traversed to reach the target table.  The script seems to be working well on the database with a couple thousand tables and many FK connections that I tried it on.

As others mention in the comments, you'd need to make this more complex if you need to handle multi-column foreign keys.  Also, please be aware that this is not by any means production-ready, fully-tested code.  Hope it's a helpful starting point if you do decide to build out this functionality!

-- Drop temp tables that will be used below
IF OBJECT_ID('tempdb..#paths') IS NOT NULL
    DROP TABLE #paths
GO
IF OBJECT_ID('tempdb..#shortestPaths') IS NOT NULL
    DROP TABLE #shortestPaths
GO

-- The table (e.g. "TargetTable") to start from (or end at, depending on your point of view)
DECLARE @targetObjectName SYSNAME = 'TargetTable'

-- Identify all paths from TargetTable to any other table on the database,
-- counting all single-column foreign keys as a valid connection from one table to the next
;WITH singleColumnFkColumns AS (
    -- We limit the scope of this exercise to single column foreign keys
    -- We explicitly filter out any multi-column foreign keys to ensure that they aren't misinterpreted below
    SELECT fk1.*
    FROM sys.foreign_key_columns fk1
    LEFT JOIN sys.foreign_key_columns fk2 ON fk2.constraint_object_id = fk1.constraint_object_id AND fk2.constraint_column_id = 2
    WHERE fk1.constraint_column_id = 1
        AND fk2.constraint_object_id IS NULL
)
, parentCTE AS (
    -- Base case: Find all outgoing (pointing into another table) foreign keys for the specified table
    SELECT
        p.object_id AS ParentId
        ,OBJECT_SCHEMA_NAME(p.object_id) + '.' + p.name AS ParentTable
        ,pc.column_id AS ParentColumnId
        ,pc.name AS ParentColumn
        ,r.object_id AS ChildId
        ,OBJECT_SCHEMA_NAME(r.object_id) + '.' + r.name AS ChildTable
        ,rc.column_id AS ChildColumnId
        ,rc.name AS ChildColumn
        ,1 AS depth
        -- Maintain the full traversal path that has been taken thus far
        -- We use "," to delimit each table, and each entry then has a
        -- "<object_id>_<parent_column_id>_<child_column_id>" format
        ,   ',' + CONVERT(VARCHAR(MAX), p.object_id) + '_NULL_' + CONVERT(VARCHAR(MAX), pc.column_id) +
            ',' + CONVERT(VARCHAR(MAX), r.object_id) + '_' + CONVERT(VARCHAR(MAX), pc.column_id) + '_' + CONVERT(VARCHAR(MAX), rc.column_id) AS TraversalPath
    FROM sys.foreign_key_columns fk
    JOIN sys.columns pc ON pc.object_id = fk.parent_object_id AND pc.column_id = fk.parent_column_id
    JOIN sys.columns rc ON rc.object_id = fk.referenced_object_id AND rc.column_id = fk.referenced_column_id
    JOIN sys.tables p ON p.object_id = fk.parent_object_id
    JOIN sys.tables r ON r.object_id = fk.referenced_object_id
    WHERE fk.parent_object_id = OBJECT_ID(@targetObjectName)
        AND p.object_id <> r.object_id -- Ignore FKs from one column in the table to another

    UNION ALL

    -- Recursive case: Find all outgoing foreign keys for all tables
    -- on the current fringe of the recursion
    SELECT
        p.object_id AS ParentId
        ,OBJECT_SCHEMA_NAME(p.object_id) + '.' + p.name AS ParentTable
        ,pc.column_id AS ParentColumnId
        ,pc.name AS ParentColumn
        ,r.object_id AS ChildId
        ,OBJECT_SCHEMA_NAME(r.object_id) + '.' + r.name AS ChildTable
        ,rc.column_id AS ChildColumnId
        ,rc.name AS ChildColumn
        ,cte.depth + 1 AS depth
        ,cte.TraversalPath + ',' + CONVERT(VARCHAR(MAX), r.object_id) + '_' + CONVERT(VARCHAR(MAX), pc.column_id) + '_' + CONVERT(VARCHAR(MAX), rc.column_id) AS TraversalPath
    FROM parentCTE cte
    JOIN singleColumnFkColumns fk
        ON fk.parent_object_id = cte.ChildId
        -- Optionally consider only a traversal of the same foreign key
        -- With this commented out, we can reach table A via column A1
        -- and leave table A via column A2.  If uncommented, we can only
        -- enter and leave a table via the same column
        --AND fk.parent_column_id = cte.ChildColumnId
    JOIN sys.columns pc ON pc.object_id = fk.parent_object_id AND pc.column_id = fk.parent_column_id
    JOIN sys.columns rc ON rc.object_id = fk.referenced_object_id AND rc.column_id = fk.referenced_column_id
    JOIN sys.tables p ON p.object_id = fk.parent_object_id
    JOIN sys.tables r ON r.object_id = fk.referenced_object_id
    WHERE p.object_id <> r.object_id -- Ignore FKs from one column in the table to another
        -- If our path has already taken us to this table, avoid the cycle that would be created by returning to the same table
        AND cte.TraversalPath NOT LIKE ('%_' + CONVERT(VARCHAR(MAX), r.object_id) + '%')
)
SELECT *
INTO #paths
FROM parentCTE
ORDER BY depth, ParentTable, ChildTable
GO

-- For each distinct table that can be reached by traversing foreign keys,
-- record the shortest path to that table (or one of the shortest paths in
-- case there are multiple paths of the same length)
SELECT *
INTO #shortestPaths
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ChildTable ORDER BY depth ASC) AS rankToThisChild
    FROM #paths
) x
WHERE rankToThisChild = 1
ORDER BY ChildTable
GO

-- Traverse the shortest path, starting from the source the full path and working backwards,
-- building up the desired join string as we go
WITH joinCTE AS (
    -- Base case: Start with the from clause to the child table at the end of the traversal
    -- Note that the first step of the recursion will re-process this same row, but adding
    -- the ParentTable => ChildTable join
    SELECT p.ChildTable
        , p.TraversalPath AS ParentTraversalPath
        , NULL AS depth
        , CONVERT(VARCHAR(MAX), 'FROM ' + p.ChildTable + ' t' + CONVERT(VARCHAR(MAX), p.depth+1)) AS JoinString
    FROM #shortestPaths p

    UNION ALL

    -- Recursive case: Process the ParentTable => ChildTable join, then recurse to the
    -- previous table in the full traversal.  We'll end once we reach the root and the
    -- "ParentTraversalPath" is the empty string
    SELECT cte.ChildTable
        , REPLACE(p.TraversalPath, ',' + CONVERT(VARCHAR, p.ChildId) + '_' + CONVERT(VARCHAR, p.ParentColumnId)+ '_' + CONVERT(VARCHAR, p.ChildColumnId), '') AS TraversalPath
        , p.depth
        , cte.JoinString + '
' + CONVERT(VARCHAR(MAX), 'JOIN ' + p.ParentTable + ' t' + CONVERT(VARCHAR(MAX), p.depth) + ' ON t' + CONVERT(VARCHAR(MAX), p.depth) + '.' + p.ParentColumn + ' = t' + CONVERT(VARCHAR(MAX), p.depth+1) + '.' + p.ChildColumn) AS JoinString
    FROM joinCTE cte
    JOIN #paths p
        ON p.TraversalPath = cte.ParentTraversalPath
)
-- Select only the fully built strings that end at the root of the traversal
-- (which should always be the specific table name, e.g. "TargetTable")
SELECT ChildTable, 'SELECT TOP 100 *
' +JoinString
FROM joinCTE
WHERE depth = 1
ORDER BY ChildTable
GO


I started with the information in Aaron Bertrand's awesome post Use SQL Server's UNPIVOT operator to dynamically normalize output.  You should read the information in that post as Aaron does a great job of explaining the process.

I had to make some changes to Aaron's process due the fact that you had more columns that what Aaron had in his example(s).

--demo setup
drop table if exists table1;
go
CREATE TABLE Table1
    (SalesID int, Order1Name varchar(10), Order1Date date, Order1Amt int, Order2Name varchar(10), Order2Date date, Order2Amt int)
;

INSERT INTO Table1
    (SalesID, Order1Name, Order1Date, Order1Amt, Order2Name, Order2Date, Order2Amt)
VALUES
    (1001, 'first', '2018-01-01', 111.00, 'second', '2018-02-01', 222.00)
;

SELECT
    SalesID,
    ROW_NUMBER() over(partition by SalesID order by OrderDate) as OrderNum,
    OrderName,
    OrderDate,
    OrderAmt
FROM
(
  SELECT SalesID, OrderName, OrderDate, OrderAmt,OrderNames,
    idon = replace(replace(OrderNames,'Order',''),'Name',''),
    idod = replace(replace(OrderDates,'Order',''),'Date',''),
    idoa = replace(replace(OrderAmts,'Order',''),'Amt','')
  FROM
  (
    SELECT SalesID, [Order1Name], [Order2Name], [Order1Date], [Order2Date], [Order1Amt], [Order2Amt]
    FROM dbo.Table1
  ) AS cp
  UNPIVOT
  (
    OrderName FOR OrderNames IN ( [Order1Name], [Order2Name])
  ) AS OrderName
  UNPIVOT
  (
    OrderDate FOR OrderDates IN ( [Order1Date], [Order2Date])
  ) AS OrderDate
  UNPIVOT
  (
    OrderAmt FOR OrderAmts IN ( [Order1Amt], [Order2Amt])
  ) AS OrderAmt
) AS x
WHERE idod = idon and idoa = idon;

| SalesID | OrderNum | OrderName | OrderDate  | OrderAmt |
|---------|----------|-----------|------------|----------|
| 1001    | 1        | first     | 2018-01-01 | 111      |
| 1001    | 2        | second    | 2018-02-01 | 222      |


mongoDB design - to embed or not to embed

I think I found my answer from the mongoDB manual:

In general, use embedded data models when:

you have “contains” relationships between entities. See Model One-to-One Relationships with Embedded Documents.
you have one-to-many relationships between entities. In these relationships the “many” or child documents always appear with or are viewed in the context of the “one” or parent documents. See Model One-to-Many Relationships with Embedded Documents.

http://docs.mongodb.org/manual/core/data-model-design/

The later scenario would apply to me.


Along the lines of this with 2 where conditions.

One will be become '%string%' LIKE '%string%' which is always true

WHERE
    CASE WHEN @casesenstive = 1 THEN COL3 ELSE '%string%' END
           COLLATE SQL_Latin1_General_CP1_CS_AS LIKE '%string%'
    AND
    CASE WHEN @casesenstive = 0 THEN COL3 ELSE '%string%' END
           COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%string%'


2ndQuadrant BDR installation not working anymore

I am not too sure about this, but it seems that BDR is build into postgresql, since I added a shared_preload_library like this:
shared_preload_libraries = 'bdr', it seems to be working the same as BDR or maybe better.


Deleting table entries based on criteria in another table

A simple solution is using IN clause in the following way:
DELETE FROM base_tbl
WHERE (f1, f2) in (select f3, f4 from del_criteria_tbl)


What's the difference between DISTINCT_SCAN and IXSCAN in explain output?

They are different stages for different purposes. I believe DISTINCT_SCAN is only present when there is an index in the field. An IXSCAN stage basically means that the query planner scans an index as one of the stages.

For example:

> db.test.createIndex({a:1})
{
  "createdCollectionAutomatically": true,
  "numIndexesBefore": 1,
  "numIndexesAfter": 2,
  "ok": 1
}

Doing find() on an indexed field will trigger the presence of an IXSCAN stage:

> db.test.explain().find({a:1})
{
  ...
    "winningPlan": {
      "stage": "FETCH",
      "inputStage": {
        "stage": "IXSCAN",
        "keyPattern": {
          "a": 1
        },
        "indexName": "a_1",
        ...

While doing distinct() on the indexed field will trigger the DISTINCT_SCAN stage:

> db.test.explain().distinct('a')
{
  ...
    "winningPlan": {
      "stage": "PROJECTION",
      "transformBy": {
        "_id": 0,
        "a": 1
      },
      "inputStage": {
        "stage": "DISTINCT_SCAN",
        "keyPattern": {
          "a": 1
        },
        "indexName": "a_1",
        ...

Note that in both cases, the index {a: 1} is used. Without the presence of the index, you will see a COLLSCAN stage instead.

One is not "faster" than the other. They're different stages serving different purposes.


Why does restarting MongoDB 3.0.x boost performance?

I believe the slowdown is caused by running in Windows. I am going to attempt this on a Linux box once our new server arrives.

However, the 26GB limit is because of the WiredTiger default configuration which only caches 50% up to max ram.

You can change this using the http://docs.mongodb.org/v3.0/reference/configuration-options/#storage.wiredTiger.engineConfig.cacheSizeGB option.


Reference column alias in same SELECT list

It's inconvenient sometimes, but it's SQL standard behavior, and it prevents ambiguities. You cannot reference column aliases in the same SELECT list.

There are shorter syntax options:

SELECT s.*, s.percent_water * 100 AS percent_water_100
FROM  (
   SELECT id, wet_weight / NULLIF(dry_weight - 1, 0) AS percent_water
   FROM   samples
   ) s;

And you can use a LATERAL join in Postgres 9.3+:

SELECT s.id, s1.percent_water
     , s1.percent_water * 100 AS percent_water_100
FROM   samples s
     , LATERAL (SELECT s.wet_weight / NULLIF(s.dry_weight - 1, 0) AS percent_water) s1;

https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3#LATERAL_JOIN
How to retrieve closest value based on look-up table?

I added NULLIF() to defend against division-by-zero errors.


Oracle scheduler chain priority in data warehouse applications

No,
the reading of your cats, dogs and humans tables wont block other processes that are inserting in them.


Get one row for each value in xml column

you can use xmltable() for that:

select p.product_id, t.option_id
from product_variants p
  cross join xmltable('/options/option_id'
                      passing p.options_xml
                      columns
                         option_id integer path '.') as t

Online example


How can we check for data integrity when we upgrade from MySQL 4.1 to MySQL 5.5?

You can use SQLyog-MySQL GUI (Trial) version to compare Schema as well as Data (records) to make sure consistency of schema and data as well.


ORA-29913 and ORA-30653 error selecting an external table

You can specify the number of rows allowed to be rejected before throwing an error, or you can specify UNLIMITED. I guess you specified a number, but your .tbl file contains rows that the database can't parse appropriately based on your definition.

If you want to ignore all malformed rows, you can simply change the limit to UNLIMITED:

alter table users_load reject limit unlimited;

create table example:

CREATE TABLE foo_load (
    employee_number CHAR(5)
) ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY ext_tab_dir
    ACCESS PARAMETERS (
        ...
    )
    LOCATION ('foo.txt')
)
REJECT LIMIT UNLIMITED; --Use limit, not limited


Multicolumn statistics on large table yielding poor estimations

With a table that large I would consider partitioning.  Unfortunately I can't answer your specific questions (1-3) but in general one of the benefits of using partitioned views (instead of native partitioning) is that the individual tables within the partitioned view are considered separate objects and have their own statistics each with 200 steps.  Here is a post where Kimberly Tripp of SQLSkills recommends for large tables that you consider not only partitioning or partitioned views but combining the two.

In case you don't know a partitioned view is one where you have multiple tables each holding a portion of the data and a view on top with UNION ALLs to combine the tables together.

Here is another of Kimberly's blogs on statistics in case you are interested.  It should help you answer some of your other questions.

Here's an article of Connor Cunningham regarding statistics: Statistics, Damned Lies, and Statistics – What is Statman?


MySQL Error 1114 "table is full" Using MyISAM Engine

A lot of helpfull information and hints can be found here:

https://dev.mysql.com/doc/refman/5.5/en/table-size-limit.html


Microsoft SQL Server 2005 Encryption Options

This article on msdn indicates it's a new feature in SQL Server 2008.  To me that means it's not available in 2005.

There are a lot of resources available with step-by-step guides for other data encryption types in 2005, though.  I think it's probably beyond the scope of a question on a Q&A site, however.


Text file report is not generating of Availability Databases other Databases during auto backup through maintenance plans in MS SQL Server 2012

Maintenance plans are garbage so this is running exactly as designed. However you don't need to take copy only backups on the secondary, you can if you want to but there's little point.

Any errors here are spurious. If you don't like them then your options are to switch to something professional like Hallengren scripts or Minion Backup; or ignore the errors (while maintaining normal testing of your backups to ensure they're viable).


SQL INSERT Query takes 2 seconds and sometimes much longer to execute

We found the solution to the slow random query execution time:

I added this line: SET TRANSACTION ISOLATION LEVEL READ COMMITTED right after the DB connect.

we are using PHP, So it's looks like this:

mysql_select_db($db, $link);
mysql_query("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");

All our problem solved with this magic line.

Now our queries run in 40 minutes and not in 2.5 hours as it use too..

thanks all for your help.


select top 5 accounts in each region in an month per sales

You must init ALL variables on the level where they are used. In your case - in subquery data source. - akina


How to free the unused space for a table

All things being equal, it should be enough to compact the large object (LOB) column OriginalHTML. You don't specify the clustered index name in the question, so:
ALTER INDEX ALL
ON dbo.Articles
REORGANIZE
WITH (LOB_COMPACTION = ON);

See ALTER INDEX (Transact-SQL)
If you have the clustered index name (not just the clustered column(s)), replace the ALL above with that name.
The LOB_COMPACTION option defaults to ON, but there's no harm in being explicit. You may need to run the REORGANIZE repeatedly to finish reclaiming all the unused space.
Unfortunately, the way LOB data is organized and the way LOB compaction is implemented means this method may not always be able to reclaim all the unused space, no matter how many times you run it. It can also be very slow.
You could also try the method in the related Q & A Freeing Unused Space SQL Server Table
If, for whatever reason, the above does not work for you, export the data to a file, truncate the table, then reload it. There are several methods to achieve that, for example the bcp utility.
Example
The following creates a table with 10,000 wide rows:
CREATE TABLE dbo.Test
(
    c1 bigint IDENTITY NOT NULL,
    c2 nvarchar(max) NOT NULL,

    CONSTRAINT PK_dbo_Test
        PRIMARY KEY CLUSTERED (c1)
);

-- Load 10,000 wide rows
INSERT dbo.Test WITH (TABLOCKX)
    (c2)
SELECT TOP (10000)
    REPLICATE(CONVERT(nvarchar(max), 'X'), 50000)
FROM master.sys.columns AS C1
CROSS JOIN master.sys.columns AS C2;

We can see the space usage using the sys.dm_db_index_physical_stats DMV:
SELECT
    DDIPS.index_id,
    DDIPS.partition_number,
    DDIPS.index_type_desc,
    DDIPS.index_depth,
    DDIPS.index_level,
    DDIPS.page_count,
    DDIPS.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats
(
    DB_ID(),
    OBJECT_ID(N'dbo.Test', N'U'),
    1,
    NULL,
    'DETAILED'
) AS DDIPS
WHERE
    DDIPS.alloc_unit_type_desc = N'LOB_DATA';

We now update the LOB content to a smaller size (but one that still requires off-row storage):
-- Change LOB data to a smaller value (that will not move in-row)
UPDATE dbo.Test WITH (TABLOCKX)
SET c2 = REPLICATE(CONVERT(nvarchar(max), 'Y'), 5000);

Notice that some space has been reclaimed, but the remaining pages are much less full than they were.
We can compact the LOB space using:
ALTER INDEX PK_dbo_Test ON dbo.Test
REORGANIZE
WITH (LOB_COMPACTION = ON);

This results in some compaction and space savings, but it is not perfect. Running the compaction again may or may not improve the situation. In my test, it did not, no matter how many times I re-ran it.
Export, truncate, reload
One way to do this entirely from Management Studio involves using xp_cmdshell to export the table data to a file. If xp_cmdshell is not currently enabled, the following will do that:
-- Enable xp_cmdshell if necessary
EXECUTE sys.sp_configure
    @configname = 'show advanced options',
    @configvalue = 1;

RECONFIGURE;

EXECUTE sys.sp_configure
    @configname = 'xp_cmdshell',
    @configvalue = 1;

RECONFIGURE;

Now we can perform the export:
-- Export table
EXECUTE sys.xp_cmdshell
    'bcp Sandpit.dbo.Test out c:\temp\Test.bcp -n -S .\SQL2017 -T';

Note you will need to change the path and -S server name, and possibly provide login credentials.
How we can truncate the table, and reload it using BULK INSERT:
-- Truncate
TRUNCATE TABLE dbo.Test;

-- Switch to BULK_LOGGED recovery model if currently set to FULL
-- Bulk load
BULK INSERT dbo.Test
FROM 'c:\temp\Test.bcp'
WITH
(
    DATAFILETYPE = 'widenative',
    ORDER (c1),
    TABLOCK,
    KEEPIDENTITY
);

The final step is to reset the identity seed:
-- Check and reseed identity
DBCC CHECKIDENT('dbo.Test', RESEED);

This sequence of operations is typically faster than LOB compaction, and should always produce optimal results:

The above is not quite as efficient as it could be due to a long-standing bug: BULK INSERT with IDENTITY column creates query plan with SORT. The workaround listed there is effective, but I would only bother with it if the table is very large.
Don't forget to delete the temporary file used to hold the exported data.
You are of course free to use whichever bulk export/import approach is most convenient to you. It is not required to use xp_cmdshell or bcp.
Additional notes:

FILLFACTOR only applies to index pages. It does not affect off-row LOB storage (which is not stored on index pages).

Row and page compression are not available for off-row storage.

As an alternative, you can compress and decompress data explicitly using the COMPRESS and DECOMPRESS functions available from SQL Server 2016.
An option for those using SQL Server 2014 (which is the case here) or older (down to SQL Server 2005) to get the same compression functionality provided by the COMPRESS and DECOMPRESS built-in functions is to use SQLCLR. Pre-built functions that do just this are available in the Free version of SQL# written by Solomon Rutzky. The Util_GZip and Util_GUnzip functions should be equivalent to COMPRESS and DECOMPRESS, respectively. And, anyone using SQL Server 2012 or newer should make sure that the server running SQL Server is updated with .NET Framework version 4.5 or newer so that the much improved compression algorithm will be used.


What is the quickest way to reset a column to a specific value?

Used the following and it executes in 41 seconds !

USE AcmeDatabase

DROP INDEX IF EXISTS IX_LegacyExtract ON AcmeWidgetTable

ALTER TABLE AcmeWidgetTable DROP CONSTRAINT IF EXISTS DF_Legacy_Extract

ALTER TABLE AcmeWidgetTable DROP COLUMN IF EXISTS LegacyExtract

GO

ALTER TABLE AcmeWidgetTable CHAR NOT NULL CONSTRAINT DF_Legacy_Extract DEFAULT (0)

GO

CREATE NONCLUSTERED INDEX [IX_LegacyExtract] ON [dbo].[AcmeWidgetTable]
(
    [LegacyExtract] ASC
)


This works fine for me:

CREATE TABLE test ( field1 VARCHAR(100) );
INSERT  INTO test
        ( field1 )
VALUES  ( 'XXXXXXXXXXXXXX' );

DECLARE @sql VARCHAR(4000) ,
    @filename VARCHAR(4000);
SET @filename = 'C:\temp\Test.csv';
SELECT  @sql = 'bcp "SELECT ''field1'' UNION ALL SELECT CHAR(34)+CAST([field1] AS VARCHAR(MAX))+CHAR(34) FROM Test" queryout "';
SET @sql = @sql + @filename + '" -c -t, -T -S ' + @@SERVERNAME;
PRINT @sql;
EXEC master..xp_cmdshell @sql;

Providing results:

field1
"XXXXXXXXXXXXXX"

Force text formatting on the column by adding a preceeding = thus:

CREATE TABLE test ( field1 VARCHAR(100) );
INSERT  INTO test
        ( field1 )
VALUES  ( 'XXXXXXXXXXXXXX' );

DECLARE @sql VARCHAR(4000) ,
    @filename VARCHAR(4000);
SET @filename = 'C:\temp\Test.csv';
SELECT  @sql = 'bcp "SELECT ''field1'' UNION ALL SELECT ''=''+CHAR(34)+CAST([field1] AS VARCHAR(MAX))+CHAR(34) FROM Test" queryout "';
SET @sql = @sql + @filename + '" -c -t, -T -S ' + @@SERVERNAME;
PRINT @sql;
EXEC master..xp_cmdshell @sql;


Need to export table into 2 columns, one repeating, one with rest stacked

select col_a, col_b INTO foo2 from (
       select col1 as col_a, col2 as col_b from chandlerV2
       union all
       select col1 as col_a, col3 as col_B from chandlerV2
     ) as myview;

Each part of subquery myview select main column (col1) and one of two secondary column (col2 or col3 respectively). Both part united by UNION ALL clause.

As variant you can CREATE TABLE and after that run INSERT INTO ... SELECT ...


performance of multi field primary key or 'contrived' 'semi artificial' key

I'll answer obliquely...

The natural key is always the natural key and should be enforced with a unique constraint or index. This is the "primary key" that flows from your modelling phase.

The choice of an auto-number/identity surrogate key matters at implementation phase because there are good and bad choices for your clustered index (example: SQL Server, Sybase, MySQL InnoDB, Oracle IOT).

That is, primary key is orthogonal to your clustered index: don't confuse the two issues

I'd suggest using a contrived key adds no value over using an auto-number/identity column in this respect. You lose data from the natural key, probably won't be unique, is just as opaque.

FWIW, I use surrogate keys and composite keys when I need too:

Some natural keys are useful in their own right: ISO currency and country codes
A table with no secondary (non-clustered) indexes and no child table doesn't benefit from a surrogate key
If you have parent-child-grandchild, then I usually need to join parent-grandchild: with composite keys I can do so directly. Simpler JOINs, simpler indexes

Note: this assumes that every table requires a clustered index

Related on dba.se: SQL Server Primary key / clustered index design decision


postgresql: make objects owned by parent role

No postgresql does not inherit permissions in this fashion,  Pretty much anything using the Create command sets the owner to the users that created it.

Use the GRANT command

Or after creating the tables set the Owner with Alter  command

Create table Mytable (list of columns);
Alter table Mytable  owner to producer;


Slow queries on indexed columns (large datasets)

I would try using UNION except for OR condition.
You could try:

( select p.EAN13, p.company_tk, a.attribute_type, a.val_t, t.title, t.field_name, t.seq from product_base p
inner join attribute a on p.EAN13=a.EAN13
inner join attribute_type t on a.attribute_type=t.attribute_type
where p.EAN13=705632109762 and a.attribute_type=1 )
union all
( select p.EAN13, p.company_tk, a.attribute_type, a.val_t, t.title, t.field_name, t.seq from product_base p
inner join attribute a on p.EAN13=a.EAN13
inner join attribute_type t on a.attribute_type=t.attribute_type
where p.EAN13=705632109762 and a.attribute_type=3 )
order by 7;

Maybe MySQL optimizer will choose better execution plan.


Understanding Variations on Multiple Joins

You can't randomize them, the order is important. Maybe you can see it clearer if we add a few parenthesis between the joins.

SELECT
    c.id
FROM
    customers c
        JOIN (sales s JOIN saleitems si ON s.id = si.saleid)
    ON c.id = s.customerid

What this query is doing is joining first sales with saleitems and then their result is being joined with customers. You can't reference a customers column inside the parenthesis join because it's not accessible:

SELECT
    c.id
FROM
    customers c
        JOIN (sales s JOIN saleitems si ON s.id = si.saleid
            AND c.id = c.customerid) -- What is c.customerid here??

  Msg 4104, Level 16, State 1, Line 6 The multi-part identifier
  "s.customerid" could not be bound.

If you ask me, I'd stick with the first way of writing the query, it's way clearer for most programmers that way.


Validation of data with checksum vs Binary_checksum

The solution that helped me solve this dilemma was to use the hashbytes with SHA2_256 which was then converted to a bigint followed by a conversion to a float to finally be summed up.

Note that the conversion from varbinary to bigint can be dangerous, but in my case it was acceptable.  the conversion directly from varbinary to float is not supported... and to sum up many records with a bigint wasn't enough due to an overflow... hence the reason to convert the bigint to a float.