Is there any other way that we can take auto backup in oracle 11g apart from RMAN?

The only correct way to back up an Oracle database is to use RMAN. There are alternatives but they are not as reliable as RMAN.

RMAN has a ton of features and this might make it look complicated. But in fact it's not difficult and should always be the choice to back up a database.

RMAN does not do logical backups like tables, indexes, procedures. RMAN does physical backups which means that it backs up the datafiles block wise. It does not care what is inside this block.

I think there are a lot of beginner tutorials on the internet. I personally like the descriptions on http://oracle-base.com/.

To automatically back up a database use the operating system scheduler and run a shell or batch script. This script should do the following tasks:

call RMAN an execute an RMAN script

backup database
backup archivelogs
delete archivelogs

delete old backups (with RMAN commands)
send the logfile to the admin

Some people additionally run an export with datapump after the daily RMAN backup. This can be useful sometimes. But I never use this option. And it should never replace an RMAN backup!


How to store a 64-digit key of alpha numerical values into MySQL Database

Storing:  Use a field with datatype BINARY(64) or CHAR(64) CHARSET ascii COLLATE ascii_bin.

Generating:

This is not an SQL question because it should not be done in the database.  It should be done in the UI layer that receives and checks passwords, etc.

So, given that you should write this in Perl/PHP/VB/Java, you have much stronger tools with which to generate the corporate password.

Of what use is a password that everyone knows?  As soon as a disgruntled employee gets fired, the password may become known to the world.

I'm serious -- some things do not belong in SQL; instead they should be in the code that access the database.

But if you must do it (for the class) in SQL, consider creating a table of the possible characters, one per row, plus another table of (at least) 64 distinct integers.  Then use a JOIN, GROUP_CONCAT, ORDER BY RAND(), LIMIT 64, and probably a subquery to get the INTs.


How does PostgreSQL store timestamp internally?

That information from the 9.6 documentation is wrong. Timestamps are stored as microseconds since 2000-01-01 00:00:00.
See include/datatype/timestamp.h:
/*
 * Timestamp represents absolute time.
 *
 * [...]
 *
 * Timestamps, as well as the h/m/s fields of intervals, are stored as
 * int64 values with units of microseconds.  (Once upon a time they were
 * double values with units of seconds.)
 *
 * [...]
 */

typedef int64 Timestamp;
typedef int64 TimestampTz;

Back in 9.6, there was also the option to store timestamps as floating point values, but that has been removed since.
That should answer your first question.
The reason why 2000 was picked probably stems from the time when you could store timestamps as floating point values. Here, the precision got better for values close to the epoch, and it made sense to pick something more future-proof than 1970 (mind you, that was before 2000).
Subtracting a constant in order to calculate the Unix epoch is no great effort.


VACUUM FULL restored very small amount of disk space

If there is an open snapshot that can theoretically still see the deleted data, then that data has to be copied over into the new table by VACUUM FULL.  Check pg_stat_activity.


Two-column foreign key constraint only when third column is NOT NULL

Proper solution

The core of the problem is the data model. In a normalized schema, you wouldn't store name and email redundantly. Could look like this:

CREATE TABLE name (
  name_id          SERIAL PRIMARY KEY,
  name             TEXT NOT NULL,
  email            TEXT NOT NULL,
  verified         BOOLEAN NOT NULL DEFAULT FALSE,
  UNIQUE (name, email)
);

CREATE TABLE address (
  address_id       SERIAL PRIMARY KEY,
  name_id          INT REFERENCES name(name_id)
  ...
);

If not-yet verified names should be allowed to break the UNIQUE constraint, you can replace it with a partial UNIQUE INDEX (much like you had in mind):

CREATE UNIQUE INDEX name_verified_idx ON name(name, email) WHERE verified;

Work with what you have

While stuck with your unfortunate design, the solution you already found yourself fits your requirements perfectly. A FOREIGN KEY with the default MATCH SIMPLE behavior matches the

  partial index syntax with a clause like WHERE verified_name_id IS NOT NULL.

Quoting the manual:

  MATCH SIMPLE allows any of the foreign key columns to be null; if
  any of them are null, the row is not required to have a match in the
  referenced table.

A (somewhat less reliable and more expensive) alternative would be a trigger on INSERT / UPDATE in address and trigger(s) on INSERT / UPDATE / DELETE in verified_name.


MySQL: Does the "length" setting affect table size in bytes?

As explained in this answer to a very similar question, the "length" is a hint, provided in result set metadata for applications that want it, of the "typical" maximum number of digits expected to be seen in the column.  As documented, this doesn't change the storage requirements and does not change the range of values that can be stored in a column.
As far as I know, this is essentially a holdover from the early evolution of MySQL when fixed-width terminals were common.

MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type. For example, INT(4) specifies an INT with a display width of four digits. This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.)
The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.
When used in conjunction with the optional (nonstandard) attribute ZEROFILL, the default padding of spaces is replaced with zeros. For example, for a column declared as INT(4) ZEROFILL, a value of 5 is retrieved as 0005.
— http://dev.mysql.com/doc/refman/5.6/en/numeric-type-attributes.html


Using SSMS, register the servers you're interested in and group them in a folder. Then you can connect to them all at once like this:


One approach (you will need to test this for performance if the dataset is large) is this:

Select `sc1`.`product_id`
  From `subcategory_product` As `sc1`
  Join (Select `product_id` From `subcategory_product` Where `subcategory_id` = 3) `sc3`
    On `sc1`.`product_id` = `sc3`.`product_id`
  Join (Select `product_id` From `subcategory_product` Where `subcategory_id` = 4) `sc4`
    On `sc1`.`product_id` = `sc4`.`product_id`
  Where `subcategory_id` = 1
;

Basically, you're scanning the subcategory_product table once for each of the values, and then doing an INNER JOIN on the results to only get rows back where all three exist.


Is it possible to create encrypted backups using encrypted by password certificate?

As far as I can see: No, it is not possible to use a Certificate that is protected by a password with the BACKUP command; the Certificate or Asymmetric Key needs to be protected by the Database Master Key (DMK).

The syntax of the BACKUP command statement does not have means of specifying a password for either a Certificate or Asymmetric Key. No, you do not OPEN a Certificate (or even an Asymmetric Key). You only OPEN Symmetric Keys and Database Master Keys (which are symmetric keys).
When attempting to use a Certificate that has been protected with a password, the following error occurs (emphasis added):

  Msg 33101, Level 16, State 1, Line 123
  Cannot use certificate 'BackupEncryption', because its private key is not present or it is not protected by the database master key. SQL Server requires the ability to automatically access the private key of the certificate used for this operation.

I had high hopes that creating a Certificate that was protected by the DMK which had its protection by the Service Master Key (SMK) removed would have worked since that usually forces you to first execute OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'; in order to do anything with the DMK, and I did have to do just that in order to create the Certificate, BUT, once the Certificate was created (and yes, I did execute CLOSE MASTER KEY;), the BACKUP statement still worked without the DMK needing to be explicitly opened. That is odd since the private key of the Certificate shouldn't be usable without executing OPEN, and the private key is definitely required for encrypting stuff (trying to do an encrypted Backup with a Certificate that has had its private key removed will fail).

Sooooo, it looks like you will need to handle this outside of SQL Server. As in:

Execute the backup
encrypt the .bak file

You can accomplish this in a few ways:

Via stored procedure:

BACKUP...
xp_cmdshell to encrypt

SQL Agent Job

T-SQL step executing BACKUP...
OS command OR PowerShell step doing the encryption

CMD script:

SQLCMD.EXE executing BACKUP...
encrypt


Can I somehow ignore uncomitted transactions which are causing the database to go into recovery?

I wouldn't delete it, just in case you run into problems. If space isn't an issue create a copy of the original files with the instance offline, rename the original (so they aren't found on restart) then try:

CREATE DATABASE MyDatabase
ON (FILENAME = [mdf path]) FOR ATTACH_REBUILD_LOG


How to index range condition and ordering by a different colum?

No, you can't do that with any of the core index types.  You might be able to use the RUM extension, but last time I tried something like this with it I couldn't get it to work for this purpose.
Once you have enough data that you actually need improvement (sorting two rows is not laborious, as you note), you might get a big improvement by range partitioning on created_at, and indexing on (user_id, amount).  That way it could consult only the partitions covering possibly-qualifying times, walking each partition in order by the index, and filtering out any rows that didn't actually qualify based on created_at but which was in a partition which couldn't be ruled out en masse.  Each individual partition scan can stop once it meets the LIMIT, and then they can be "merge appended" maintaining the existing order, and have the LIMIT applied again to the merged set.
After playing with this some more, I have to amend my answer.  A GiST index on (user_id, created_at, amount) can do this as long as you can reformulate your query a bit.  You can use the KNN mechanism by comparing "amount" to a 'sentinel' value which is greater than any possible real amount in your table:
select *
from transactions
where user_id=1234
  and created_at >= current_timestamp - interval '7 days'
order by amount <-> 999999999
limit 10;

You do need a real max amount here, you can't use 'Infinity' because the distance between any finite value and infinity is infinite, and they will all be tied.
GiST indexes can be much slower to build and maintain than btree indexes, and that can also be hard to predict.  So even though it is possible, it might not be worthwhile if other techniques with btree indexes will get the job done.


How do I exclude unchanged fields in Flashback query resultset?

Is there a way to excluded unchanged fields from versioned response?

Not really.
Flashback uses the information found in UNDO. Flashback Data Archive stores the results from the Log files in a table.
So, you get ALL of the data when you do a Flashback query.
The common method is to use LAG + DECODE.  But, I'm afraid that this will problematic for your LOB columns.
Personally, I'd pawn off the problem to the UI (Display Tier).


SQL Server 2012 cannot use file server for log shipping

Make sure your log shipping primary instance's SQL Server and SQL Server Agent services are configured using a domain account and has write access on the specified backup share. If these are in place it should work fine.

Note: The SQL Server Agent can be configured to use a local account as well. The primary issue is that whatever account is used must have read/write access on folder where backup is happening.

To check it manually, you can try to take a manual backup on the share specified to see if that works and make sure write access is in place. I am assuming that your SQL Server and SQL Server Agent accounts are configured to use the same domain account.

Run the below commands. Just change the DBName and FileserverPath as you have in your environment:

BACKUP DATABASE [DBName]
TO DISK = N'\\[FileserverPath]\DBName_FULLBackup.bak'
WITH INIT;
GO
EXECUTE sys.xp_cmdshell 'dir \\[FileserverPath]\';

If this gives an error, create a folder on your fileserver, share it and grant write permissions to the SQL Server Agent account, then try again.


Why is a BIGINT index larger than a CHAR 13 index?

Benchmarks tend to be flawed.  Here are some questions that affect the outcome of your test:

How did you measure the sizes?
What are the values in the column?  In particular, are they in the same order as the order of insertion?
Did you have the index in place before populating the table?  Or did you ALTER TABLE .. ADD INDEX .. later?
Have you done any DELETEs or UPDATEs?  These tend to mess with BTrees.
Are there trailing spaces in your CHAR values?  (There are some situations where CHAR removes them -- I don't know about this situation.)
Are all the CHAR values the same?  Or consecutive values have the same first several characters?  (There may be optimizations.)

All of those questions have a bearing on the size of a MyISAM index.  (The list for InnoDB is similar.)

To answer the question...

A MyISAM index is structured as a BTree in 1KB blocks.  Each "record" contains:

Data (8 bytes for BIGINT or 13 bytes for CHAR(13) latin1)
5 (by default) byte pointer to the row in the .MYD
Some overhead?

Plus there is some overhead for the BTree.

If the rows for a BTree are inserted in order, each BTree block (except for the last) will be "full".
At the other extreme, the rows will be half full due to block splits due to inserting rows at just the wrong time.
On average, after random inserts and/or deletes, a BTree settles to about 69% full.  (UPDATEs don't matter in your case, since the records seem to be FIXED.)

Now for some math.

BIGINT: 5M rows * (8+5) bytes/row = 65MB.  This could easily grow to 81MB with overhead, 69%, etc.

CHAR: 5M * (13+5) = 90MB.  So this must be incorrect.

CHAR: 5M * (2+5) = 35MB.  This could inflate to 51MB.  Suppose you inserted 'x' and MyISAM turned it into VARCHAR(13).

CHAR, working backwards, assuming random insertions:  51MB * 69% = 35MB.  Divide by 5M rows.  That leaves 2 bytes per CHAR.  If all are the same, or all in a block have the same prefix, then it fits.

CHAR, working backwards, assuming ordered insertion:  51MB/5MB = 10 bytes per row.  5 for pointer leaves 5 for CHAR.  Still possible.

Comments on benchmarking with CHAR(13):

There are very few cases where CHAR is preferred over VARCHAR.
The database world is moving toward utf8mb4, not latin1.
It is unrealistic to put x in all rows.
If you want to test with effectively random, fixed length, latin1 values:  LEFT(MD5(id), 13)

One more note:  There are two ways that MyISAM might [re]build an index:

Via key_buffer -- this is likely to leave the index at 69% full.
Via sorting -- this is likely to leave it almost 100% full.

A quick experiment:  ALTER TABLE msisdns_key ENGINE=MyISAM;  Watch SHOW PROCESSLIST; to see which repair method is used.  See if some of the indexes shrink.

MyISAM is going away; don't spend too much time on it.

ALTER TABLE .. ADD INDEX .. happens in one of these ways:

Copy over the table and rebuild all the indexes, or
Add the new index, not effecting anything else, and not return until finished, or
Add the new index in the background.  The point here is that ls -l may not be updated yet when you look at it.

FIXED is almost never better than DYNAMIC, even with MyISAM.  The places where it might help:

Delete a short record, then insert a longer record.
Update a record, increasing the size of some column(s).

If you have a lot of those and the 'fixed' size is not much bigger than the 'dynamic' size, then maybe fixed is better.

Dynamic is usually better when the average row size is smaller.  This is because Smaller --> More cacheable --> Faster.

About the only case for MyISAM is that the disk footprint is smaller than InnoDB.  In virtually any other contest, InnoDB meets or exceeds MyISAM.

My 10% Rule of Thumb.  When trying to optimize something (usually speed or space), I guesstimate how much a proposed change will help.  If less than 10%, I move on in search of 'bigger fish'.

Is it big?.  5M records; 150MB data + index.  This is not terribly big.  I would rank it at about 80th percentile as MySQL tables go.  It should be easily cached in RAM, even if implemented in InnoDB.  If it will be several GB, let's look at specific schemas, not "oh, by the way the BIGINT is for phone numbers".

Smallest phone.  A 12 digit number can be crammed (without length info) into BINARY(5).  But, I suggest that the messiness in the code makes it not worth the effort.  (Perhaps my 10% rule kicks in?)


archive_command: Can hardlink (ln) be used instead of cp?

No, that is not an option and will lead to trouble.
The first and most obvious reason is that the WAL segments exists only once, even if there is a hard link to it in another directory. If anything happens to the disk with the file, you have no copy and cannot recover.
The second reason is more subtle and will lead to data corruption. When a WAL segment is archived and older than the latest checkpoint, it is not necessarily deleted. If PostgreSQL determines that it needs new WAL segments (max_wal_size or – in older versions – checkpoint_segments has not been exceeded and there is much data modification activity going on), it will recycle the WAL segment rather than deleting it.
If that happens, and PostgreSQL starts writing to the recycled WAL segment before you have managed to copy it to safety, the original WAL entries will be overwritten and are lost.  If you recover using such a WAL segment, PostgreSQL will notice that there are unexpected data in the WAL and will stop recovering.


Longest matching suffix while using aggregate functions

What is wrong with the (maybe too obvious?):

select * from noun n, noun_inflection ni
where
  n.label = 'handlebar' and
  n.label ilike '%'||ni.label_singular
order by
  char_length(ni.label_singular) DESC
limit 1;


Sort Order has been change due to DB server movement

If you don't have an ORDER BY clause, the order of the result set is never guaranteed.

Connor Cunninghan No Seatbelt Expecting Order Without ORDER BY. In case it gets archived again
Aaron Bertrand showing another exmaple of this

Also, you mentioned "report". If you are using SSRS or any reporting suite really, they have sort functions within them as well which means you wouldn't have to change any of your procedures or views.


PXC (and other Galera solutions) require one network hop at COMMIT time.  If your application can combine writes into a single transaction, that will help at some level.  However, still each COMMIT will take 300ms (or whatever it is).

Regular MySQL can do Master-Master and have both writable.  That replication is asynchronous.  However, there are a number of caveats.  Auto_increments should be configured with auto_increment_offset and _increment.  UNIQUE indexes are problematic; the app must figure out how to avoid 'simultaneous' insertion of the same UNIQUE value on both continents.  Note that replication will simply stop until you manually do something.

Another possibility is NDB Cluster.  However, that has a lot of differences in the syntax and functionality.  The advantage is that it is "eventually consistent".  You set up rules about what to do if, say, duplicate keys are inserted.

There's an old saying:  You can have any of X or Y or Z; pick 2.  So, I ask, what are your 2 reasons for wanting multi-Master?  And what 3rd item are you willing to sacrifice.  (It sounds like latency is not #3.)


Get column difference between same table in different schemas

Find column names which exists in the table in schema_a.products and not exists in schema_b.products:

SELECT t1.column_name
FROM (SELECT column_name
      FROM information_schema.columns
      WHERE table_name = 'products'
        AND table_schema = 'schema_a') t1
LEFT JOIN (SELECT column_name
           FROM information_schema.columns
           WHERE table_name = 'products'
             AND table_schema = 'schema_b') t2 ON t1.column_name = t2.column_name
WHERE t2.column_name IS NULL

Find column names which exists in the table in one schema and not exists in another schema

SELECT column_name, table_schema
FROM (SELECT column_name, table_schema
      FROM information_schema.columns
      WHERE table_name = 'products'
        AND table_schema IN ('schema_a', 'schema_b')) both
GROUP BY column_name
HAVING GROUP_CONCAT(table_schema ORDER BY table_schema) != 'schema_a,schema_b'

If only_full_group_by is enabled, wrap table_schema field in output with some aggregate function applicable to string data type (MIN, MAX, GROUP_CONCAT, etc.).

If tablenames in schemas are not equal, replace WHERE condition with

WHERE (table_name, table_schema) IN (('tablename1', 'schemaname1'),
                                     ('tablename2', 'schemaname2'))

and add table_name field (maybe wrapped too) to output list.


What does Error: 17828, Severity: 20, State: 4 mean?

It's a timeout error, basically, and it's fixed (i.e. error suppressed) in SS 2008.

Here's the relevant connect item.


How can I optimise an Update on a large table?

Is the process I/O bound (there is lots of disk activity) or CPU bound (which is possible if the functions are complex)? It is most likely the former and if so then if there are indexes covering those columns it is usually better to drop them before operations that update every row and recreate them afterwards (don't drop the PK and/or clustered index though). "Usually" is an important word here: check it out in a test environment to confirm for your data.

  Would it be faster to create a new table rather then update it?

Probably not, unless the new table is on physically separate media so reading from the old and writing to the new does not cause head-thrashing on the physical disks.

  Would it be better to create a loop and update 100000 records each time and commit after each iteration?

If you and your users can cope with the data being partially updated at any given time then this (probably with a smaller number of rows then 100,000) gives you the advantage of not blocking your application for other uses as you don't have an update lock on the entire table for the length of time it takes to update all the rows. I've not used postgres much but assuming it behaves similarly to MS SQL Server (in simple recovery mode) using smaller updates inside explicit transactions can greatly reduce the amount of log file growth but is unlikely to significantly affect overall run-time.

If running the update in batches and allowing normal operations to continue on the application then obviously you shouldn't follow the first recommendation to drop indexes at the start & recreate them at the end - those indexes are likely to be essential to reasonable performance of the application.


How can I use with differential backup of SQL Server

Differentials are useful only in the presence of log backups. A restore starts from the last full, and then all log backups are applied. In a typical scenario of backing up the log, say, every 15 minutes, the number of log backups to be applied can quickly escalate. Having differentials allow the sequence to be shortened to last full -> last differential -> all log backups after that.

  I'm looking for a solution to restore my data from the primary database
  to the standby one

That is called Log Shipping and is built into the product. You must change the recovery model to full (or at least bulk_logged) and start taking log backups. There is just no way to avoid this.


MySQL Add/Grant user if it does not exist

What you need is Dynamic SQL

SET @sql_found='SELECT 1 INTO @x';
SET @sql_fresh='GRANT ALL ON *.* TO randomuser@''%'' IDENTIFIED BY ''randompassword''';
SELECT COUNT(1) INTO @found_count FROM mysql.user WHERE user='randomuser' AND host='%';
SET @sql=IF(@found_count=1,@sql_found,@sql_fresh);
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

If @found_count is 1, then SELECT 1 INTO @x; will execute. (this query does nothing)

Otherwise, GRANT ALL ON *.* TO randomuser@'%' IDENTIFIED BY 'randompassword';

Give it a Try !!!


Modelling constraints on subset aggregates?

As we have to span multiple rows it cannot be implemented with a simple CHECK constraint.

We can also rule out exclusion constraints. Those would span multiple rows, but only check for inequality. Complex operations like a sum over multiple rows are not possible.

The tool that seems to best fit your case is a CONSTRAINT TRIGGER (Or even just a plain TRIGGER - the only difference in the current implementation is that you can adjust the timing of the trigger with SET CONSTRAINTS.

So that's your option 2.

Once we can rely on the constraint being enforced at all times, we need not check the whole table any more. Checking only rows inserted in the current transaction - at the end of the transaction - is sufficient. Performance should be ok.

Also, as

  The accounting data is append-only.

... we only need to care about newly inserted rows. (Assuming UPDATE or DELETE are not possible.)

I use the system column xid and compare it to the function txid_current() - which returns the xid of the current transaction.
To compare the types, casting is needed ...
This should be reasonably safe. Consider this related, later answer with a safer method:

How to view tuples changed in a PostgreSQL transaction?

Demo

CREATE TABLE journal_line(amount int); -- simplistic table for demo

CREATE OR REPLACE FUNCTION trg_insaft_check_balance()
    RETURNS trigger AS
$func$
BEGIN
   IF sum(amount) <> 0
      FROM journal_line
      WHERE xmin::text::bigint = txid_current()  -- consider link above
         THEN
      RAISE EXCEPTION 'Entries not balanced!';
   END IF;

   RETURN NULL;  -- RETURN value of AFTER trigger is ignored anyway
END;
$func$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER insaft_check_balance
    AFTER INSERT ON journal_line
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW
    EXECUTE PROCEDURE trg_insaft_check_balance();

Deferred, so it is only checked at the end of the transaction.

Tests

INSERT INTO journal_line(amount) VALUES (1), (-1);

Works.

INSERT INTO journal_line(amount) VALUES (1);

Fails:

  ERROR:  Entries not balanced!

BEGIN;
INSERT INTO journal_line(amount) VALUES (7), (-5);
-- do other stuff
SELECT * FROM journal_line;
INSERT INTO journal_line(amount) VALUES (-2);
-- INSERT INTO journal_line(amount) VALUES (-1); -- make it fail
COMMIT;

Works. :)

If you need to enforce your constraint before the end of the transaction, you can do so at any point in the transaction, even at the start:

SET CONSTRAINTS insaft_check_balance IMMEDIATE;

Faster with plain trigger

If you operate with multi-row INSERT it is more effective to trigger per statement - which is not possible with constraint triggers:

  Constraint triggers can only be specified FOR EACH ROW.

Use a plain trigger instead and fire FOR EACH STATEMENT to ...

lose the option of SET CONSTRAINTS.
gain performance.

DELETE possible

In reply to your comment: If DELETE is possible you might add similar trigger doing a whole-table balance check after a DELETE has happened. This would be much more expensive, but won't matter much as it rarely happens.


Client lib provides the API for the App to access the MongoDB server. At startup, the client lib will connect to some member (based on a seed list) of the Replica set and issue a isMaster command to gather the current picture of the set (who is the primary and secondaries). After that, the client lib connect to the single primary (where it will send all DB modification request) and some number of secondaries (where it will send read-only queries). The client library will periodically re-run the isMaster command to detect if any new members have joined the set. When an existing member in the set is crashed, connections to all existing clients will be dropped and forces a resynchronization of the latest picture.

I suggest you check the mongod.log for details.


innodb_log_file_size set to a lower value than the size of ib_lofile0 and iblogfile1

The InnoDB buffer pool is different from the InnoDB log file, i.e. the undo log (a.k.a. the redo log), whose size is controlled by the innodb_log_file_size and innodb_log_files_in_group variables.

The buffer pool is a cache for frequently used data and indexes, so that MariaDB doesn't have to read them from disk. On the other hand, the undo log stores old versions of data being modified by write transactions.

If you need to change the size of the buffer pool, the variable to modify is innodb_buffer_pool_size. This is a dynamic variable starting with MariaDB 10.2, so can also be modified without restarting by executing a command like SET GLOBAL innodb_buffer_pool_size=32G;


Would I lose data if I change a column from varbinary(8000) to varbinary(max)?

No, you won't lose data.

SQL Server will do an in place conversion, as a transaction. This means if anything fails, it will rollback.

Note, this is more than a simple metadata change so it may take some time because data has to be moved around


The clustered index key is included in every non-clustered index as the row identifier.

SQL Server just ignores an INCLUDE of the clustered index key.

If your PK index is NONCLUSTERED then it will not be included in every row of all other NC indexes.

You can test this yourself by making two identical indexes, one with INCLUDE(ClusterKey) and one without, and compare the sizes.  They will be identical, even on hundreds of millions of rows.


No, you cannot rely on system or NTFS backups as an appropriate disaster recovery strategy for SQL Server.  If all your current solution is doing is backing up the database files (i.e. mdf, ldf, ndf) and if you needed to revert to those "backups", then you could potentially run into a horrific situation where SQL Server can't use those database files.

You need to ensure that you're taking SQL Server backups (full, differential, transaction log [unless you're in simple recovery]).  This will provide you with the correct way to recover from disaster in the event you need to do a restore.

A few notes:

Store your backup files separately from the server in order to not have a single point of failure
You need to test your backed up databases for corruption(i.e. restore database, run DBCC CHECKDB against restored database).  If you have been backing up a corrupt database unknowingly for 3 weeks, and your backup retention is only 2 weeks, then you could have only a corrupt database that has been backed up


in SQL databases does an update rewrite all columns in the row or only those in the SET tuple?

This is going to vary between databases, but there are things that we can say are generally (but certainly not always) true of common databases such as SQL Server, Postgres, etc.

Drive IO generally works in blocks, usually these days of 4Kbyte or 0.5K, and to write any of a block the drive will write the whole block. Database engines organise their structures into pages for various reasons including to mach the IO subsystem. In MS SQL Server each page is 8K, done databases allow you to choose on a per DB or per server basis. As with the underlying drives writing to a page will result in rewriting the whole page (which may be multiple disk blocks in size) not just the part constituting the updated row(s) or the individual columns in the row(s). So the same amount of IO will be done in your two examples (SET a & SET a, b, c).

In fact more than just that page will be affected: an entry will be made in the transaction log first, and relevant index pages may need to be updated too. The MVCC model used (or supported as an option) by some database systems as extra details to the question, but that detail may be getting a bit deeper than the intended scope of your question.

Another point that does seem relevant is that this IO will happen even if nothing really changes: if you are seeing column a to 42 and it is already 42 the writes will still happen. Further more, any relevant triggers will still fire, constraints will be checked, etc. This is why you sometimes see statements like UPDATE someTable SET aColumn = @aValue WHERE aColumn <> @aValue, to avoid excess activity (if doing this in your code, be sure to be careful to account for NULLs if they might be present, which my example does not for the sake of being concise, also be aware that there are circumstances where you might want triggers to fire even if an update otherwise effectively does nothing).

It is worth reiterating before finishing this answer, that none of this is true for all databases, or all IO more generally, but is only commonly true.


Complain to RDS.

Meanwhile...

Plan A:  Design code to gather the rows up and do a multi-row INSERT or LOAD DATA.

Plan B:  Put BEGIN and COMMIT around small batches of rows.  (Suggest 1 second or 100 rows or 1MB, whichever comes first.)


Listener refused the connection with following error: ORA-12505, TNS: listener does not currently know of SID given in connect descriptor

How are you trying to make a connection? If it is SQLplus here's what happens:

c:>sqlplus user/password@database

The entry "database" is looked up in the tnsnames.ora file. Here is how this may look:

database=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = databas)
    )
  )

This says that database "database" runs on server "server" with SID "databas".
A connection is made with the listener on server "server" on port 1521 with a request to connect to database with SID "databas".
The listener responds with the message that it does not know a database with SID "databas".

One way of getting some more information about this is to use TNSPING.

C:\>tnsping database

TNS Ping Utility for 64-bit Windows: Version 11.2.0.3.0 - Production on 25-SEP-2
012 08:21:21

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:
D:\app\rwe\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = SERVER)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SID = databas)))

This will tell you the location of sqlnet.ora. Your tnsnames.ora file is in the same location. Also you get to see what the entry for database is in your tnsnames.ora file.


How to update one column in table from select statement?

UPDATE syntax:

UPDATE [table]
SET [est date] = toCONVERT(datetime,
                           SWITCHOFFSET(CONVERT(datetimeoffset, table.[utc date]),
                           DATENAME(TzOffset, SYSDATETIMEOFFSET())))
WHERE [utc date] > '2017-10-01'


Clean up space used by moveChunk in file system

As per the MongoDB official documentation:

Is it safe to remove old files in the moveChunk directory?

  Yes. mongod creates these files as backups during normal shard
  balancing operations. If some error occurs during a migration, these
  files may be helpful in recovering documents affected during the
  migration.

  Once the migration has completed successfully and there is no need to
  recover documents from these files, you may safely delete these files.
  Or, if you have an existing backup of the database that you can use
  for recovery, you may also delete these files after migration.

  To determine if all migrations are complete, run
  sh.isBalancerRunning() while connected to a mongos instance.


Local SSMS client is not connecting one particular server

Due to your comments on the main answer, I would write my suggestions below. You described that your neighbor can still connect to the machine but you not. This doesn't seem like a normal behavior of an SQL Server. This just brings me to another conclusion. It seems to be more likely a network error. In fact, you also described that you could connect to the server too, just before an hour.

I would suggest the following solution approaches:

Check if your neighbor still can connect to the server.
Check if you can connect to all other systems your normally used to connect with.
Logon on the PC of your neighbor, try to connect to Server X. If this will work, it's nothing with your permissions or with your user.
Due to the fact, that it seems to be network related, I would
suggest to check the network in your company. Check at your
neighbor's PC which IP is assigned to Server X. You can do this by
using the cmd and typing nslookup serverx. Compare it with the
IP which was provided after you tried to ping serverx. Do they
match? If not, you probably have a DNS error. Which means that
Server X has another IP, but your PC hasn't recognized it in his
ARP. You may want to flush your your dns-cache. You can do this using ipconfig /flushdns on the command line.
If this 4 doesn't apply or help, you can try an tracert to server x from your neighbor using tracert serverx. Check which hops occur. Try the same on your machine. Do they match or is there any difference? If you found a timeout on a special ip, you can try to ping the corresponding ip from your pc and from the pc of your neighbor. If you can't reach the ip, but your neighbor can - it looks like a port error on a specific switch between you and your neighbor. By the way, the whole story at all seems to be a defect port in my opinion. Just due to the fact that this just occurs from one minute to the next is kind of specific for an port error. Even the fact that just you cannot connect to the server. You should try this!
If all of the above doesn't help, you still check the firewalls between you and server x. Some companies use smart switches or IDS application (Intrusion detection system) which will detect misbehavior and close specific ports. If your company use such a system and it's not proper configured it may cause such a port close. I've had a similar solution in my old company in the first days of our IDS. You can also try to connect to server x from your neighbors PC using RDP and try to ping your PC from the side of the Server.

If nothing else helps, you need to contact your system administrator (if your not the one :-D) and try to figure out which cable or port is broken.

I really don't think that this is an SQL Server specific error. I would guess that this is some hardware or maybe software error in your network/pc.


Database Migration - Create New Filegroup and Move Objects

This can be done if you have clustered indexes (either as the primary key constraint or on their own) on the tables that you wish to move.

So restore the database, add the new filegroup and then...

For primary keys:-

 ALTER TABLE [SCHEMA].[TABLE NAME] DROP CONSTRAINT [CONSTRAINT NAME]

 ALTER TABLE [SCHEMA].[TABLE NAME] ADD PRIMARY KEY CLUSTERED
 (
   [Column]  ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [NEW FILEGROUP]
 GO

For clustered indexes:-

CREATE CLUSTERED INDEX [CONSTRAINT NAME] ON [SCHEMA].[TABLE NAME]
(
    [Column] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON,
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [NEW FILEGROUP]
GO

Please be warned that this may take a while to do on a VLDB!


Always Encrypted after restoring an old database backup using C#

I could achieve always encrypted using c#.
generate powershell script using management studio.

Add below import modules in script at top.

Import-Module -Name "SqlServer" -MinimumVersion "21.0.17262"

Then run PS script using below code

 using (PowerShell powerShellInstance = PowerShell.Create())
            {
                powerShellInstance.AddScript(
                    "Set-ExecutionPolicy -Scope Process -ExecutionPolicy ByPass");
                powerShellInstance.AddScript(scriptFileText, true);
                var psOutput = powerShellInstance.Invoke();

                foreach (PSObject outputItem in psOutput)
                {
                    if (outputItem != null)
                    {
                         //Log output if error
                    }
                }
                success = true;
            }


Defaulting a column to the 1st of next month

It think this would be the simplest way to do it.

DATEADD(DD,1,EOMONTH (GETDATE()))

So you query would be like

ALTER TABLE Foo ADD
Bar datetimeoffset(0) NOT NULL DATEADD(DD,1,EOMONTH (GETDATE()));

Thanks!


When should I use shared lock over exclusive lock?

In general you should not worry about such.  This pattern covers most common cases adequately.

BEGIN;
SELECT ... FOR UPDATE; -- touch the rows you _might_ update
do some stuff
UPDATE ...;    -- then update them (or not)
COMMIT;

Note that shared locks get 'promoted' in some circumstances for you.

If you would like to explain your situation we can be more specific.


For future reference, after asking IBM we verified that DB2 10.1 PureScale feature cannot run on Fujitsu, only the hardware listed in the manual.

Maybe in future versions...


This particular error comes with a hint mentioned in the doc,
in Routine vacuuming (8.4):

  ERROR:  database is not accepting commands to avoid wraparound data
  loss in database "mydb" HINT:  Stop the postmaster and use a
  standalone backend to VACUUM in "mydb".

It's a critical situation which normally should not occur if autovacuum can do its job. When you're back online, you should try to find out why this could have happened (wrong vacuum settings?).

In the meantime, what must be done is simply what the HINT tells: VACUUM without additional parameter. As mentioned in the comments , VACUUM FULL is a different command, even though it's not obvious by its name. It rewrites entirely the tables, is much slower and not desirable in this situation.

More details at https://wiki.postgresql.org/wiki/VACUUM_FULL


MySQL 5.6 - Query Performance Issue. Searching for Slug

Upgrade to MySQL-5.7 and then MySQL-8.0 (for safety, 1 major version at a time).

Then use Secondary indexes on virtual colums as such:

ALTER TABLE geoname
ADD slug VARCHAR(20) GENERATED ALWAYS AS (slugify(name)),
ADD INDEX slug_country_fclass (slug, country, fclass);

If you wish to stay on 5.6, and you don't change geoname value often, you can denormalise as:

ALTER TABLE geoname
ADD slug VARCHAR(20) NOT NULL DEFAULT '',
ADD INDEX slug_country_fclass (slug, country, fclass);

Populate the column with:

UPDATE geoname SET slug=slugify(name);

After doing one of the above steps, change the query to use the slug generated column as:

SELECT name, latitude, longitude FROM geoname
WHERE
slug = 'lisboa'
and geoname.country = 'PT'
and geoname.fclass in ('A', 'T')

The new index can then be used for all the criteria of your query.


Inserting into multiple tables simultanteously (HEIDISQL)

The overhead and complication of spinning off two threads to run the queries simultaneously are almost always more costly than doing them sequentially.

Copying an entire table over is normally a rare task.  As such, the performance is often not a big deal.  Are you doing this as part of the normal flow?  If so, let's look at it in more detail to see if there is a better way to achieve the ultimate goal.

Don't use TINYTEXT.  VARCHAR(255) (or some smaller number) is virtually equivalent, yet more efficient in some situations.

Redundancy:

PRIMARY KEY (`componentno`, `flatpackid`),
INDEX `componentno` (`componentno`),    -- DROP, since the PK handles it.


MySQL InnoDB log files on different location

Setting the two files on two different disks may not help you, because speaking generally, they are only written serially:

  The redo log files are used in a circular fashion. This means that the redo logs are written from the beginning to end of first redo log file, then it is continued to be written into the next log file, and so on till it reaches the last redo log file. Once the last redo log file has been written, then redo logs are again written from the first redo log file.

(from the InnoDB blog).

Setting them both on a separate disk from the tablespaces is a good idea, as if your problem is lots of writes, as having exclusive IO will be an advantage on magnetic disks. They are also good candidates for SDDs because (again, if you have problems with transaction log IO) they are relatively small and usually written synchronously to disk, unlike the indexes and data.

Another way to load-balance IO is to use a RAID level with improved throughput. RAID 1 will provide you better reads and writes thanks to its striping, although the recommended setup is RAID 10 for redundancy.

One disadvantage of being on separate disks is that in most cases, you will lose the option of performing consistent MySQL datadir snapshots.


How to write database creation scripts when coding software setup?

Well, you can always generate a script of the current database using right-click > Tasks > Generate Scripts in Object Explorer (this should create the tables and keys in the right dependency order). Or you could generate such a script with a 3rd party tool like Red Gate SQL Compare1 by comparing the current version of your schema against an empty database. Or you could ship a .bak file (a backup of an empty copy of your database) with your software and the setup could restore it.

1. Probably dated now, but a few years ago I blogged about many alternatives here.


SSIS PL/SQL task hangs with message "Multiple-step OLE DB operation generated errors."

The reason the task was hanging was that I had run a separate update statement against MY_TABLE in a SQL Developer client window, and had not completed the transaction. Because the transaction was still open, the Execute SQL Task could not complete. Committing the transaction in SQL Developer allowed the SSIS package to complete, however, it still generated the same Warning message.

It appears the Warning message is harmless, though I still don't know how to interpret it.

Attention Microsoft SQL Server DBAs! The SQL Developer Oracle query window DOES NOT work like SQL Server Management Studio! DML queries are not implicit transactions, so and INSERT, UPDATE, OR DELETE statement leaves an open transaction unless you click the COMMIT or ROLLBACK button on the toolbar. Learn from my mistake.


What does a green icon mean and why do the package bodies not expand when they have it?

The green bug you're seeing (look closely) means the package have been compiled with DEBUG option. It is also seen on bare procedures and functions. It is a coincidence they don't expand - maybe a bug (duh) in SQLdeveloper :) On mine, these expand without problem.


SQL Server doesn't log the query text of an update. You can find the log records using 3rd party log reader tools (or fn_dblog), and you may be able to find out who did it (depends on authentication mode and how your users log in), and you can find the previous version of the data by restoring to a point in time before the update, but you're not going to be able to find the query after the fact.

It may be time to start taking away rights, adding auditing (the full-blown auditing feature or your own triggers), or forcing data manipulation through stored procedures and rolling your own logging.


Remove Dot at end of line of the string in SQLserver

You can use like to check for a . at the end of the string.

declare @S nvarchar(max) = 'ABC Private''s co., ltd.';

select case when @S like '%.'
         then left(@S, len(@S) - 1)
         else @S
       end;


Error while running sp_blitz in 2008 SQL Server and lower versions

One way you could go about this if you want to influence data types or column names is create an intermediate table to store the result of the stored procedure and then selecting from it.

Something along the lines of

CREATE TABLE #temp (< column definitions like the stored procedure returns >);

INSERT INTO #temp EXEC sp_Blitz;

SELECT < CAST columns to data types you want > AS < alias column names to names you want >
FROM #temp;


Percentile rank that takes sorted argument (or same functionality) in PostgreSQL 9.3.5

In Postgres 9.4+, you can use the hypothetical-set aggregate function percent_rank() in a LATERAL join like this:

SELECT *
FROM   daily_data d, LATERAL (
   SELECT round(percent_rank(d.daily_val) WITHIN GROUP (ORDER BY daily_val)::numeric
              , 6) AS pctl_calc
   FROM   daily_data
   WHERE  company_id = d.company_id
   AND    trade_date < d.trade_date
   ) x
ORDER  BY company_id, trade_date;

The CROSS JOIN LATERAL (, LATERAL for short) is safe, because a subquery with an aggregate function always returns a row. (Else you might want LEFT JOIN LATERAL (...) x ON true.)

Minor difference: The first row in each group returns 0, not 1 like in your example. Easily fixed, if you need it.

This would do the same job in Postgres 9.3:

SELECT d.*
     , round(CASE WHEN x.ct = 0 THEN numeric '1'  -- or 0 ?
                  ELSE x.ct_lt / x.ct END, 6) AS pctl_calc
FROM   daily_data d, LATERAL (
   SELECT count(daily_val) AS ct
        , count(daily_val < d.daily_val OR NULL)::numeric As ct_lt
   FROM   daily_data
   WHERE  company_id = d.company_id
   AND    trade_date < d.trade_date
   ) x
ORDER  BY company_id, trade_date;

I use count(daily_val) for the full count, not count(*) to ignore NULL values - necessary if daily_val can be NULL, .

SQL Fiddle.

Or, without LATERAL join for even older versions. May be faster, too:

SELECT company_id, trade_date, daily_val, pctl
     , round(CASE WHEN ct = 0 THEN numeric '1'  -- 0 ?
                  ELSE ct_lt / ct END, 6) AS pctl_calc
FROM  (
   SELECT d.*
        , count(x.daily_val) AS ct
        , count(x.daily_val < d.daily_val OR NULL)::numeric AS ct_lt
   FROM   daily_data d
   LEFT   JOIN daily_data x ON x.company_id = d.company_id
                           AND x.trade_date < d.trade_date
   GROUP  BY d.id  -- pg 9.1+
   ) sub
ORDER  BY company_id, trade_date;

Assuming id is the PK for a simplified GROUP BY, requires pg 9.1 though.


To avoid the multiplication effect you are experiencing, you can do the aggregation first, and then join:

select a.agent_id, a.TotalApplication, b.TotalLoginHours
     , b.TotalLoginHours / a.TotalApplication as ApplicationPerHour
     , 1.75 * b.TotalLoginHours / a.TotalApplication as RPH
from (
    SELECT a.agent_id as agent_id
         , COUNT(a.id) as TotalApplication
    FROM forms a
    WHERE a.created_at = '2015-07-17'
    GROUP BY a.agent_id
 ) as a
 join (
     select b.user_id as agent_id
          , SUM(b.loginhours) as TotalLoginHours
     from loginhours b
     group by b.user_id
 ) as b
     ON a.agent_id = b.agent_id
 ;

Not sure I got all the details right, but it should give you a hint of a technique that can be used. I modified your fiddle in:
http://sqlfiddle.com/#!15/8d88b/9


There are several whitepapers on optimizing SSAS that you can find on James Serra's blog here - http://www.jamesserra.com/archive/2011/12/top-ssas-white-papers/

I would particularly look at:

The SSAS performance guide in the settings and configuration sections
https://technet.microsoft.com/en-us/library/dd542635(v=sql.100).aspx

and for memory management
http://sqlcat.com/technicalnotes/archive/2008/07/16/running-microsoft-sql-server-2008-analysis-services-on-windows-server-2008-vs-windows-server-2003-and-memory-preallocation-lessons-learned.aspx

I'm not sure what your cost limitations are, but if you're hosting SQL Server Service and the Analysis Service on the same box, I would advise against it if you have fairly large cubes.


Online resource for writing Access SQL statements

Here is Microsoft's page about Access SQL syntax:

http://office.microsoft.com/en-us/access-help/access-sql-basic-concepts-vocabulary-and-syntax-HA010256402.aspx

and here is a tutorial about Access SQL syntax for Access 2000.  A bit dated, but the concept is still there:

http://www.cwnresearch.com/resources/databases/access/tutorials/access2000/SQLQueries/SQLQueries.html


character varying vs. text as function parameter types in PostgreSQL

When it comes to performance, these data types are exactly the same, down to the implementation in C.

If in doubt, use text, because that is the preferred type of its type class, and the data type resolution rules for functions will resolve all unknown strings to text implicitly. With character varying you often have to use explicit type casts.


.psqlrc - setting keepalives_idle connection parameter

With TCP Keepalives, it doesn't matter who sends it and who receives it. It just matters that it happened so the route isn't lost by a NAT device or closed at either end. I don't know why you'd ever want to set this on the client, as libpq offers, both methods accomplish the same thing.

How to set keepalive transmissions from the server

This only sets it for your session

To set the tcp_keepalives_idle from the server to the client, use

SET tcp_keepalives_idle = 42;

You can put anything you want in your ~/.psqlrc and it gets executed by psql (except under -c and -x). So append this command to the end if you want it to occur for ever psql session.

echo "SET tcp_keepalives_idle = 42;" >> ~/.psqlrc

How do set keepalive transmissions from the client

According to this mailing list post, you can set it on the client by typing

psql "host=127.0.0.1 dbname=myDB keepalives=1"


Mysql: privileges to get access to "mysql" database

The very minimal permissions would be:

GRANT USAGE ON *.* TO [user@host] ;
GRANT SELECT ON mysql.help_topic TO [user@host] ;

Do not believe this answer is sufficient. Please check the manual first, because the privilege system in MySQL is far from being obvious.


What is the best way to upgrade a MySQL version in a Linux Server

I found the question relevant, however it is answered before elsewhere

You want to turn off you database (take the backup, but only for the reason of having a backup)
Go to you config file (.ini), in there locate the datadir
take a copy of this directory, or make sure it is not removed when reinstalling
Reinstall, and check if everything works, make sure the datadir is the same

This is how i would do it, but i haven't tested this, so you might bump into issues

(this idea/theory is backup up by this guy) http://dacosta9.wordpress.com/2008/10/25/moving-data-files-to-another-disklocation-mysql-sql-server/

And from Server fault
https://serverfault.com/questions/68146


When you say, "it doesn't transfer all the info" what exactly do you mean? Do you want to insert into the history table before the data is updated AND after for auditing purposes?

  ...the mysql statement delimiter so that you can use the ; statement
  delimiter within the trigger definition.

Check the MySQL trigger reference section at MySQL for more info.

Either way, try adding he DELIMITER // at the beginning and the end.

DELIMITER //
CREATE TRIGGER `Bank_after_update` AFTER UPDATE ON `Bank`
 FOR EACH ROW BEGIN

    IF NEW.Deleted THEN
        SET @Change_Type = 'DELETE';
    ELSE
        SET @Change_Type = 'EDIT';
END IF;
INSERT INTO Bank_History (bank_id, Change_type) VALUES (NEW.id, @Change_Type);
END
//

I hope this helps.


ODBC connection fail when running AD account for SQL Server services

As you have found out the SQL Server Service runs the backup but not the user giving the backup command.

Changing the service user should be done using the SQL Server configuration manager which in the background sets correct permissions for the service account, all of which are documented on MSDN, giving the user running the service Administrative rights on the computer will make it possible to run the  service but is not a good security practice.

You have to be sure to run the correct version of the Configuration manager so try running MMC.exe and adding the correct snapin for your version of SQLServer so check that first and if it failes reregister the snapin by running in a command prompt the following command

mofcomp "%programfiles(x86)%\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

If this failes, use the information in the msdn article to manuallyl set the correct permissions.

Your SSPI context error is due to incorrect SPN for the sql server, The SQL server creates a SPN automatically when  you start it and the domain admin has permissions to to that which fixes the SSPI error. After you change the service account for the server to a normal user download the Microsoft Kerberos configuration manager for SQL Server and make sure to create the correct SPN


Set a default value for a jsonb property in PostgreSQL

You can resort to row_to_json for things like this.

insert into document (title, content)
select 'The title of the document', row_to_json(n)
from (
  select 'The lead' as lead,
         'An URL to the video' as video,
         uuid_generate_v4() as picture
  ) n


unable to run step 3 of syspolicy_purge_history job on SQL Server 2012

Please look at this connect item

The SQL PowerShell wrapper SQLPS.EXE seems to execute a PS command Set-ExecutionPolicy restricted -scope Process -Force during its initialization. Then PS engine should then be initialized with command import-module SQLPS  -DisableNameChecking which is blocked because of the "Restricted" execution policy. This raises a critical error and the PowerShell engine terminates.

The workaround is adding/changing a registry key:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps110]
"ExecutionPolicy"="RemoteSigned"

See this and this for additional reference.


SQL Server not using all NUMA memory with 20 core limit and affinity mask

In general, SQL Server grows used memory only as needed. You cannot conclude that memory local to a NUMA node is not available to SQL Server because each NUMA node has 128 GB of memory and a query suggests that only 90 GB of memory is in use. If you'd like to validate your configuration I know of a number of ways to do that, but not all of them are production friendly. In all cases, you can compare the results that you get now with what you had with the default affinity mask.

1) Look at the results of sys.dm_os_memory_nodes. If you configured SQL Server correctly you should see rows for memory_node_id 0 and 1. That means that SQL Server has access to all of the memory on your server.

2) Look at the results of sys.dm_os_memory_clerks for your favorite memory clerk that is NUMA aware. For example, you can see if the buffer pool is spread over multiple NUMA nodes:

SELECT memory_node_id, pages_kb
FROM sys.dm_os_memory_clerks
WHERE [type] = N'MEMORYCLERK_SQLBUFFERPOOL';

You are likely to see pages for both memory node 0 and 1. It's possible that you won't, and that doesn't necessarily represent a problem.

3) Look at the results of sys.dm_exec_query_resource_semaphores. Target_memory_kb should be around 70-80% of max server memory. If it's closer to 96 GB than 192 GB then you might have a problem.

4) Restart SQL Server after enabling TF 834 as a startup parameter. With trace flag 834, SQL Server upon startup grows the buffer pool using large pages to the maximum size that it is able to do. You might not see max server memory if the memory within the guest OS is fragmented. Enabling this trace flag will have some effect on your workload, so I can only recommend running a test like this in a non-production scenario. However, it might be a good option if you truly need to see SQL Server using all of the memory that it can with your own eyes.


Migrating all MySQL databases from from one server another. Getting error during import

Perhaps you should dump the users and load the users into the VM

Here is my post : Export all MySQL users

After running the code in it and saving it to a file called MyDatabaseUSers.sql, you log into mysql on the VM and run this:

mysql> source MyDatabaseUSers.sql

This will create all the users with the same grants and passwords. Don't worry: Passwords are encrypted.

After importing the users, you should be able to reload the mysqldump.

GIVE IT A TRY !!!

NOTE: If you loaded all your data successfully, the error might have occurred loading a MySQL object that has a DEFINER associated with it

stored procedures
triggers
views
events.

Do the following:

cat newdump.sql | grep -A 2 -n "dc_reporter"

This will show you what might not have loaded. Your data may all be there.

You could try dumping your stored procedures and triggers and reloading them instead of reloading everything. Please read my old post Can mysqldump dump triggers and procedures?, go dump your triggers and stored procedures, and reload them into the VM. If you have views to import, please read my old post Modify DEFINER on Many Views and use the code to mysqldump just the views.


Which is better MySQL search strategy, varchar x 2 or text column?

Search using col LIKE '%xyz%' is bad on varchar and text alike. You probably want to use fulltext index which can work well with a text column.

Your proposed scheme is unnecessarily complex and you might even "split" the word you are searching for so it won't work.


Turn on sql_warnings, sql_note, log_warnings (or log_error_verbosity after 5.7.2), and lots of things in sql_mode.  See doc for explanations.


How to detect JSON data type support in MariaDB/MySQL

I don't think there is any such feature in MariaDB or MySQL themselves at the moment (MariaDB 10.3, MySQL 5.7, 8.0).

So the only way I can imagine actually detecting support for specific data types and built-in functions on-the-fly would be to try using the data type or funtion, and then catching the exception, if any.

It's also possible that certain software libraries can give you a hint as to what features the underlying RDBMS can offer. If so, this is probably done by storing a list of features associated with each RDBMS version inside the software library.


How to set cache on windows 10 pro 64bit machine

I found a free tool that will set SetSystemFileCacheSize here with 16 gigs of RAM I set it to 1024 4096 and the mongod warning went away


sp_WhoIsActive vs sp_BlitzFirst @ExpertMode = 1

They're just different scripts.

Adam's sp_WhoIsActive is powerful, free, and copyrighted. I've used it for at least a decade, and I love it, and I still continue to use it personally. However, it's not open source, which means you can't contribute code to it, and you're not allowed to distribute it to others.

We needed something we could redistribute to our clients for commercial purposes, and let them distribute it to THEIR clients (because some of our clients are software vendors and service providers). That's why we wrote sp_BlitzWho and licensed it with the MIT License, enabling us (and others) to do just about anything with it.

They're both great tools. I still teach people how to use sp_WhoIsActive because most folks don't need the ability to redistribute code or make improvements to it.

About why I still use sp_WhoIsActive - when I troubleshoot blocking chains, I prefer sp_WhoIsActive because of its get_locks parameter that shows locking in objects. We don't need the ability to edit that code or redistribute it, so we haven't bothered building that logic into sp_BlitzWho.


SELECT r.rm_number
FROM   hotel h,
     reservation rr,
     room_booking b,
     room r
WHERE  rr.res_id = b.res_id
     AND b.rm_id = r.rm_id
     AND r.htl_id = h.htl_id
     AND h.htl_name = 'Holiday Inn Express'
     AND rr.res_checkin IS NOT NULL
     AND rr.res_checkout IS NULL ;


SQL Server 2014 SSIS Package Ignores Script Task when running as AGENT

That occurred because VS 2015 was built to support only SQL server 2016 and you probably deployed the package under SQL Server 2014 or lower(unsupported versions).

With the latest version of Data tools in VS 2015, you can chose the targeted SQL server version in the package to allow SSDT to chose the correct .NET libraries used in the script component.

See this link for details


Why does innodb_flush_method = O_DSYNC provide such a performance boost with SAN-backed storage?

O_DSYNC is faster than O_DIRECT because it performs writes without verification.
O_DIRECT is paranoid but more data consistent

I wrote about this before : Clarification on MySQL innodb_flush_method variable


The sys.backup_devices table should contain details on existing backup devices, so try checking to see if there's a row there first:

IF NOT EXISTS (SELECT NULL
               FROM   sys.backup_devices
               WHERE  [Name] = N'TestTest'
                      AND [Type] = 2)            -- 2 = 'Disk'; see MSDN
  EXEC master.dbo.Sp_addumpdevice
    @devtype = N'disk',
    @logicalname = N'TestTest',
    @physicalname = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.PTC_SQLPROD\MSSQL\Backup\TestTest.bak'

The documentation for the sys.backup_devices table can be found here: http://msdn.microsoft.com/en-us/library/ms178018%28v=sql.100%29.aspx


Indexing a GROUP BY Select with Multiple Inequalities

It seems there are a lot of rows which fit the conditions and database server needs to sort them for grouping. It probably even needs to create a temp table on disk if your data doesn't fit into memory. In-memory temporary table size is limited by max_heap_table_size and tmp_table_size variables, but what is more important - MEMORY tables use a fixed-length row-storage format. Variable-length types such as VARCHAR are stored using a fixed length. It means every varchar(20) value will consume 20 bytes of RAM. Add 3 bytes for dates and 4 for doubles and your row size will be 31 bytes. If 5% of 100 million rows fit the conditions than you need 150 MB temporary table to group your data. You can start with increasing the max temporary table size and check if it helps.

But also you can try to use another approach:

SELECT Symbol
    ,(SELECT Date FROM price_daily
        WHERE Symbol = s.Symbol
            AND Date BETWEEN '2002-07-31'AND '2017-07-31'
            AND MarketCap >= 1000
            AND PriceToSales BETWEEN 2 AND 5
        ORDER BY Date LIMIT 1) AS min_date
FROM symbols AS s;

I assumed you have a table with list of symbols (Symbol data type should be the same).


How can I change collation for all tables and columns in MySQL database?

In order to change the collation for all of the tables in certain database you should create a custom script and execute it on the server.

Here is a sample PHP script:

<?php

$database_name='dbname';
$database_username='dbusername';
$database_password='dbpassword';

$connection = mysql_connect('localhost',$database_username,$database_password);

if(!$connection) {
    echo "Cannot connect to the database – incorrect details";
} else{
    mysql_select_db($database_name);
    $result=mysql_query('show tables');

    while($tables = mysql_fetch_array($result)) {

        foreach ($tables as $key => $value) {
            mysql_query("ALTER TABLE ".$value." CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
        }
    }
echo "Successfull collation change!";
}
?>


Getting data from 2 tables by JOIN, when @Foreign Keys exist to the same Table/Object

You must use the User table twice:

SELECT N.text, S.name, S.email, R.name, R.email
FROM   Note as N
   LEFT JOIN User as S on N.sender_id = S.id where S.staff is True
   LEFT JOIN User as R on N.sender_id = R.id where R.staff is True


MySQL - terrible performance for simple query on large table

key_buffer_size=48G  -- lower to 50M unless you are using MyISAM tables
innodb_buffer_pool_size=96G  -- why not 300G
max_heap_table_size=12G  -- limit to 1% of RAM
tmp_table_size=12G       -- ditto

Queries 2 and 3 are "covering", as you paraphrased with "use only the index without needed access to the actual data".
Queries 1 and 4 are the same, except for the number of rows involved.
Did you run the first query twice?  Probably the first time, the necessary data blocks were not cached in the buffer_pool and needed to be fetched from disk.  In an extreme case, that could be 2M disk reads.  Even NVMe probably takes more than 220s to do 2M reads.
If you have a lot of "average" and similar queries, consider building and maintaining "summary tables".  They can make things significantly faster for huge tables.  http://mysql.rjweb.org/doc.php/summarytables


Alter disabled trigger in SQL Server without enabling it

You can't. You just need to disable it again. There is no ALTER TRIGGER syntax that prevents this.

The disable trigger topic explicitly calls out that

  Changing the trigger by using the ALTER TRIGGER statement enables the
  trigger.


How to query Last inserted row for each group in oracle 10

What you want to do is something like this with ROW_NUMBER() and PARTITIONing by cust_id and ORDERing BY contact_time DESC so that the first one will be the latest. A fiddle is available here - Oracle has microsecond precision, so it's very unlikely that you'll get simultaneous remarks by the same customer at the same time.

Create and populate customers table:

CREATE TABLE customers
(
  cust_id INTEGER NOT NULL,
  cust_name VARCHAR (25) NOT NULL
);

INSERT INTO customers VALUES (1234, 'John Doe');
INSERT INTO customers VALUES (1235, 'Ram');
INSERT INTO customers VALUES (1236, 'Rahim');

Do the same for customer_review:

CREATE TABLE customer_review
(
  review_id VARCHAR (25) NOT NULL,
  cust_id   INTEGER NOT NULL,
  remarks   VARCHAR (250) NOT NULL,
  contact_date DATE NOT NULL,
  contact_time TIMESTAMP NOT NULL
);

INSERT INTO customer_review VALUES
('NEW123', 1235, 'I am new user',
TO_DATE('2019-10-27', 'yyyy-mm-dd'), TO_TIMESTAMP('2019-10-27 06:10:13.278',  'yyyy-mm-dd hh24:mi:ss:ms'));
INSERT INTO customer_review VALUES
('CUST123', 1234, 'This is awesome product',
TO_DATE('2019-10-26', 'yyyy-mm-dd'), TO_TIMESTAMP('2019-10-26 11:15:15.123', 'yyyy-mm-dd hh24:mi:ss:ms'));
INSERT INTO customer_review VALUES
('CUST124', 1234, 'This is not good product. I have changed my mind now',
TO_DATE('2019-10-27', 'yyyy-mm-dd'), TO_TIMESTAMP('2019-10-27 16:25:15.567', 'yyyy-mm-dd hh24:mi:ss:ms'));
INSERT INTO customer_review VALUES
('CUST125', 1234, 'After upgrade, it is now working fine!',
TO_DATE('2019-10-27', 'yyyy-mm-dd'), TO_TIMESTAMP('2019-10-27 19:52:56.345', 'yyyy-mm-dd hh24:mi:ss:ms'));

Then run the following SQL:

SELECT c.cust_name, t.cust_id, t.remarks FROM
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY cust_id ASC, contact_time DESC) AS rn,
    *
  FROM customer_review
) t
JOIN customers c
  ON t.cust_id = c.cust_id
WHERE t.rn = 1;

Result:

CUST_ID     REMARKS                                  CUST_NAME
   1234     After upgrade, it is now working fine!   John Doe
   1235     I am new user                            Ram

(after edit - these are now the correct answers - the remark by John Doe is his latest one as should be the case! Ram has only made one remark so by definition, it is the latest).

A few words of advice. You have your customers table with a plural and customer_review in the singular - pick one or the other and stick to it (you can search for "SQL style guide" and choose one you like. Personally, I recommend singular - a table is a set of things and therefore singular.

Next time you post, could you please include your table definitions as DDL text (CREATE TABLE xxxx (field_1 type_1...);) and your data as DML text (INSERT INTO xxxx VALUES (val_1, val2...);). Here are a few reasons why posting screenshots is not the best approach for questions on DBA.SE. There are a few articles on how to ask questions here on my profile - you might want to take a look?

Finally, I recommend that you use an integer for review_id - it makes sorting easier - otherwise you might be faced with having to use messy code like

SELECT CAST(SUBSTR('CUST1235',  5, LENGTH('CUST1235')) AS SMALLINT) AS no;

to extract the INTEGER from the review_id field.

It is possible to solve this problem using the Oracle ROWNUM pseudo-column, but this is non-standard and it's better to develop good habits - and it will make your application more portable in the event that you/your company decide to do this. I hope this has helped - if it doesn't correspond to your requirements, please let me know and we can try and modify it. p.s. welcome to the forum! :-)

Edit:

I finally got the Oracle fiddle working here - to microsecond precision.

Another point that struck me on rereading the question and my answer. You don't need to declared a contact_date field - it can be easily derived from the contact_time field. The data (date in this case) is therefore only stored in one field and one field only - which is conformant with RDBMS best practice.

There are two ways of doing this

You can use the TO_DATE function when calling the table. Your table will now look like this (note contact_date is commented out. You then just call TO_DATE(contact_time) when you just want to get the date (example shown in the fiddle - table customer_review_bis).

CREATE TABLE customer_review_bis
(
  review_id VARCHAR (25) NOT NULL,
  cust_id   INTEGER NOT NULL,
  remarks   VARCHAR (250) NOT NULL,
  -- contact_date DATE NOT NULL,
  contact_time TIMESTAMP NOT NULL
);

You can also use a GENERATED AS (aka COMPUTED BY or CALCULATED) column. These are very convenient and once defined, you have an effective guarantee that the date will always match the value in contact_time. This functionality used to be performed by TRIGGERs - it really is worth learning about these. The syntax column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL] is described here (oracle-base is a super site). Your table definition now becomes (also shown in fiddle - customer_review_ter):

CREATE TABLE customer_review_ter
(
  review_id VARCHAR (25) NOT NULL,
  cust_id   INTEGER NOT NULL,
  remarks   VARCHAR (250) NOT NULL,
  contact_time TIMESTAMP NOT NULL,
  contact_date DATE GENERATED ALWAYS AS (TO_DATE(contact_time)) VIRTUAL
);  -- you just treat contact_date as a normal field when you call it, in
    -- exactly the same way as you did in your original example - table
    -- customer_review (first one) in the fiddle.

If you choose the GENERATED AS, you have to slightly modify the INSERTstatement by adding the fields that are updatable:

INSERT INTO customer_review_ter
(review_id, cust_id, remarks, contact_time)
VALUES (.... data for specified fields ....);

Finally, as @EdStevens says, Oracle 10 is EOL (End of Life) - you really should think about upgrading!


SQL Server sometimes uses a lot more cpu for query

That sounds like it is using an inappropriate query plan. There are two obvious paths this query could take and it is presumably deciding between them depending on the values of @row_offset and @row_count: for a small offset it is probably better to scan SomeCol,SomeOtherCol in order and apply the complex filtering clauses (in the WHERE and JOIN ON clauses) to each row until enough have been found to satisfy @row_count. For many other circumstances this would be a terribly bad approach.

To be more specific we'd need details on those other tables, the indexes/keys, and the sort of size/distribution of each table's contents.

What I suspect is happening is a cached query plan is being used when that plan is inappropriate for the current input values. When you run DBCC FREEPROCCACHE the cache is purged so it is forced to create a new plan and use that.

You can try the RECOMPILE hint by appending OPTION(RECOMPILE) to direct SQL and WITH RECOMPILE on the stored procedure definition if it is a procedure rather than an ad-hoc query - this tells SQL Server not to bother checking the procedure cache and always generate a fresh plan. This of course means that you lose the benefit on occasions where using a cached plan instead of having to reassess stats and replan would have been faster - this may be an acceptable tradeoff but obviously you need to benchmark and make sure you are not degrading overall performance more than is acceptable (if so you'll need a different strategy)).


Performance tuning large mysql databases for zabbix

"running 16k values per second" -- Do you mean "rows inserted per second"?  SELECTs per second?  Something else?

The most important setting for MySQL is innodb_buffer_pool_size, which should probably be 300G for your setup.  innodb_buffer_pool_instances=16.

innodb_flush_log_at_trx_commit is 1 by default.  1 is safer; 2 leads to less I/O.

If you are doing 16K INSERTs per second, there are several techniques to decrease the I/O (which you imply is 'too high').  Batching (doing multiple rows in a single INSERT), putting them a single transaction, using LOAD DATA, or "staging".  Please describe things more.

VARIABLES/STATUS analysis

There were some interesting things.  Here are the highlights:

( innodb_max_dirty_pages_pct ) = 30 -- When buffer_pool starts flushing to disk -- Why so low?  (Probably does not matter)

( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((23406029 + 990332255) ) / 769924 = 1316 /sec -- InnoDB I/O
-- Increase innodb_buffer_pool_size?  Increase innodb_max_dirty_pages_pct?

( Created_tmp_tables ) = 300,386,596 / 769924 = 390 /sec -- Frequency of creating "temp" tables as part of complex SELECTs.

( Created_tmp_disk_tables ) = 16,281,268 / 769924 = 21 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs
-- increase tmp_table_size and max_heap_table_size.
Check the rules for temp tables on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM.
Better indexes and reformulation of queries are more likely to help.

( Select_scan ) = 22,715,682 / 769924 = 30 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Sort_merge_passes ) = 412,662 / 769924 = 0.54 /sec -- Heafty sorts
-- Increase sort_buffer_size and/or optimize complex queries.

( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 1 (and turn on slowlog) -- would help in locating naughty queries.

Threads_running = 45 -- likely to indicate that queries are stumbling over each other

Innodb_buffer_pool_pages_flushed / Questions = 0.49 -- Perhaps indicates buffer_pool is not being an efficient cache.

Select_range / Com_select = 22% -- High, but not necessarily bad.  (Select_range = 360/sec)

Opened_files = 86/sec.  I'm concerned about the table_open_cache, but there are not metrics to indicate whether it is "big enough".

Can you group writes into bigger transactions?  There is a lot of I/O; this would decrease it.
Com_begin and Com_commit are currently 257/sec.

A lot of the Writes are UPDATEs.  Please elaborate on what they are doing.  If they are incrementing counters, it may be worthwhile to collect them in some way.

innodb_io_capacity = 6000 -- I do not know if this is optimal for your system.

Innodb_x_lock_spin_waits = 5500/sec -- A huge number, but I don't have a good handle on what to do about it.

large_page_size -- I have not experienced this; how is it going for you?

slave_skip_errors = 1062 -- Maybe you need to use INSERT IGNORE or INSERT .. ON DUPLICATE KEY UPDATE..?

Slow log

Many of the queries have

    from  items t,hosts r
    where  t.hostid=r.hostid
      and  r.proxy_hostid=13242
      and  r.status in (0,1)
      and  t.type in (0,7,1,4,6,12,2,3,9,10,11,13,14,16,17,5)
    order by  t.itemid

Which needs these composite indexes:

items:  INDEX(hostid, type)  -- in this order
hosts:  INDEX(proxy_host_id, status, hostid)  -- this order; helps WHERE; 'covering'

My as well toss KEY hosts_3, since it will be redundant.

I see cases of redundant indexes, such as

KEY `c_items_4` (`interfaceid`),
KEY `items_6` (`interfaceid`),

Recommend DROPping one of them.

There are issues with many of the other tables, but I won't go into them; Zabbix should fix them.

more

UPDATE item_discovery ...
    WHERE itemid BETWEEN ... AND ...
       OR itemId IN (...);

This is probably very inefficient because of the OR.  The best solution (for MySQL; don't know about Zabbix) is to split it into two UPDATEs:

UPDATE item_discovery ...
    WHERE itemid BETWEEN ... AND ...;
UPDATE item_discovery ...
    WHERE itemId IN (...);

more 2

It should not take 6-10 seconds to insert 1000(?) rows into one partition of a table.  It might help if you limited these inserts to to 100 rows at a time.

more 3

Speeding up queries, as indicated above, will help regardless.  But I am now seeing a different problem.  That slowlog snippet from from one 30-minute period?  And that was some kind of spike that does not normally happen?  What this when you reached Max_used_connections = 523?  What happens in a case like that:  523 threads, all getting some tiny fraction of 32 cores, etc, is stumbling over each other.  I see a few queries taking more than 3 seconds, yet they should not take more than about 3 milliseconds.

What I recommend in such situations is to figure out how to limit the number of connections upstream, that is in Zabbix client.  Meanwhile, you could decrease max_connections to, say, 200.  This is likely to trip up the client; but that might be better than having a long period when nothing seems to be getting done.

Also, 5.5 is getting pretty old; start laying plans for an upgrade.

Nasty DELETE

@12:37 (and perhaps every xx:37) there is a 123-second query finishing:

DELETE FROM events WHERE source = 0 AND object = 0 AND clock < ...;

Although this is the optimal index for such

KEY `events_2` (`source`,`object`,`clock`)

It is apparently not good enough.  123 seconds of disk-intensive deletes, including building rollback stuff, can slow down other things -- which I think is what happened.

Cures?

Plan A:  Instead of doing the DELETE hourly, do it every, say, 5 minutes (with a different clock cutoff each time`).  This should speed it up to 10 seconds instead of 123, possibly faster.

Plan B:  Make it a PARTITIONed table and use DROP PARTITION, which is much less intensive on the system.  (But that adds a lot of complexity, and Zabbix probably will get in the way.)

Plan C:  "Continually" delete the stuff, using another process.  Perhaps

Loop:
    DELETE FROM events WHERE source = 0 AND object = 0
            AND clock < ...   -- update this cutoff as needed
        LIMIT 1000;
    sleep 1 second
repeat

Plan D:  See my Big Delete blog for other techniques (and some details on Plans A, B, C.)

Continue to pursue the other suggestions; you are probably near the breaking point -- That is, if the dataset doubles in size, nothing will bring the system back to life.  My various suggestions will delay, but not eliminate the doomsday.


Where pages are used for internal purposes like sort runs, the maximum row size is 8094 bytes. For data pages, the maximum in-row size including internal row overhead is 8060 bytes.

Internal row overhead can expand significantly if certain engine features are in use.  For example, using sparse columns reduces the user-accessible data size to 8019 bytes.

The only example of external row overhead I know of up to SQL Server 2012 is the 14 bytes needed for versioned rows.  This external overhead brings the maximum space usage for a single row to 8074 bytes, plus 2 bytes for the single slot array entry, making 8076 bytes total. This is still 20 bytes short of the 8096 limit (8192 page size - 96 byte fixed header).

The most likely explanation is that the original 8060 byte limit left 34 bytes for future expansion, of which 14 were used for the row-versioning implementation.


It really depends on which flavor of RDBMS you're working with.  Oracle database management is significantly different from SQL Server database management, for instance.

I answered a similar question recently here, but one of the best (Oracle) DBA books that I've read is Darl Kuhn's Pro Oracle Database Administration.  I used it as a textbook in graduate school, and it covers everything from install, config, backups, partitioning, etc, as well as common real-life problems and their solutions.

I wouldn't classify it as being for "beginners", but I had never had any database management before either, and I did ok.


Select only one duplicate with different time column

You can achieve this goal by using the windowing functionlag and self joining the table onto itself.  I've included an example below.  You may need to tweak some of the code to get exactly what you want, but I think this comes really close.

The Setup

DECLARE @TestData AS TABLE
    (
    ID INT NOT NULL
    , Reference CHAR(3) NULL
    , RefName CHAR(20) NULL
    , RefTime DATETIME2(7) NULL
    );

INSERT INTO @TestData
(ID, Reference, RefName, RefTime)
VALUES (1,'abc', 'John', '2016-02-08 08:25:35')
    , (2, 'abc', 'John', '2016-02-08 08:25:37')
    , (3, 'bnm', 'Eliza', '2016-02-08 08:25:39')
    , (4, 'abc', 'John', '2016-02-08 08:25:42')
    , (351, 'cdf', 'Eliza', '2016-06-01 15:25:42')
    , (352, 'abc', 'John', '2016-06-02 15:25:42');

The Query

WITH CTE_FirstPass AS
    (
    SELECT T.ID, T.Reference, T.RefName, T.RefTime
        , LAG(T.ID) OVER (PARTITION BY T.RefName, T.Reference ORDER BY T.RefTime) AS PrevIDForReference
    FROM @TestData AS T
    )
    , CTE_SecondPass AS
    (
    SELECT P.*
        , PriorT.RefTime AS PriorRefTime
        , ABS(DATEDIFF(SECOND, PriorT.RefTime, P.RefTime)) AS PriorTimeDiff
        , IncludeData = CASE    WHEN P.PrevIDForReference IS NULL THEN 1
                                WHEN ABS(DATEDIFF(SECOND, PriorT.RefTime, P.RefTime)) <= 20 THEN 0
                                ELSE 1
                                END
    FROM CTE_FirstPass AS P
        LEFT OUTER JOIN @TestData AS PriorT ON PriorT.ID = P.PrevIDForReference AND PriorT.Reference = P.Reference AND PriorT.RefName = P.RefName
    )
    SELECT P.ID
        , P.Reference
        , P.RefName
        , P.RefTime
    FROM CTE_SecondPass AS P
    WHERE P.IncludeData = 1
    ORDER BY P.ID;


Find all version of SQL Server on a network?

SQLPing should do the job

I've used it on medium and small networks and it seemed to find all stray SQL Server installs


In Oracle, how can I give one user privileges to alter the schema of another user?

You can use CONNECT THROUGH to allow Dogbert to connect as Wally:

alter user wally grant connect through dogbert;
grant create session to dogbert;

To connect in sqlplus:

sqlplus dogbert[wally]/scottadams@DATABASE

Some tools may not support these proxy logins.


Not really anything obvious.

The easiest would be if you have a backup you could restore to view it.

Otherwise you'll properly try - either yourself, daunting, or via commercial tools to find a tool that lets you explore the transaction log for the given changes.
For example something like: http://www.apexsql.com/sql_tools_log.aspx

I've not tried out such tools myself so I cannot vouch for them.
I usually get by either by having backups to search, or in some cases, I've set up change tables to log changes to critical data.


I guess you want to see partitions in scripted objects.
All you need to do is to go to Tools-Options-Scripting and play with checkboxes there:


Example optimisic offline lock in SQL/PL?

Typically I think that systems would select the ora_rowscn value when they read the data, and when they write it back they would specify that value as part of the update statement. If no rows are updated then either the record has been deleted or the record has changed.


Iterate through all non-system databases and empty them

Try using sp_MSforeachdb - Aaron's version.

That is more reliable than the undocumented sp_MSforeachdb.

Also, it would be more plausible if you can just recreate the databases from scratch (like Aaron Suggested above), make sure you have Instant File initialization - Enabled.


SQL Server 2012 Tuning process exited unexpectedly. DTAEngine

As per KB 2728419

You have to install atleast SQL Server 2012 + SP1 + CU1.

On a sidenote, CU8 is out as well which is the latest one for SQL Server 2012 SP1.

Also, you need to be a member of SYSADMIN role. DB_OWNER wont work as per the above KB.


slow UPDATE of rows by COUNTing from another table

Remove correlation:

UPDATE t1
JOIN ( SELECT t2.id, COUNT(*) c1
       FROM t2
       GROUP BY t2.id ) t3 ON t3.id=t1.id
SET t1.c1 = t3.c1;


Is there any way to test which clustered index is better for specific queries?

The Clustered Index should be defined with a narrow and ever increasing value (this is why so many of them use an auto-incrementing integer).

Having worked with large tables like this before, your best bet is to continue to use an auto-incrementing primary key as the definition for the clustered index.  Then create non-clustered indexes that support your queries, with the understanding that other ad-hoc queries will probably perform terribly.  You get the added benefit that the index on your large table should never need defragmentation.

Given that, creating a non-clustered index on Timestamp, EventType should give sufficient performance.  You typically want the most specific column referenced first, and timestamps are good candidates for this, especially if you always (or almost always) include it in your query.

The advantage of non-clustered indexes is of course that they are typically much narrower than their base table and so require much less memory.

CREATE NONCLUSTERED INDEX TimestampEventType
ON IndexTest1 (Timestamp, EventType)

You can also benefit from casting to the proper datatype (I'm assuming DATETIME)

SELECT * FROM IndexTest1
WHERE Timestamp >= CAST('2018-03-14' AS DATETIME) AND Timestamp <= CAST('2018-03-20' AS DATETIME) AND EventType = 'String'

Finally, check that statistics are up to date for your query and that the execution plan is actually doing an index seek or range scan with a key lookup back to the main table.


Migrating master databases between clusters by detaching/attaching them

SQLServer can't work without a master database at all as it stores essential configuration information in it (such as the table of databases in the system), so it wouldn't allow you to detach it. However, provided the SQLServers being the exact same version, you'll probably run into no problems if you stop the SQLServer services in both ends, copy the master database files from the one system to another (keep the old ones just in case), and start the services agan. It will be safer if neither server has any other databases in the system.


Best approach to get last value inserted

No, please don't store this data redundantly - you already have all of the information you need in the base table, without incurring the overhead of triggers or additional writes.

Just create an index that supports your query, somewhat similar to what @Colin suggested, but I'm adding the output columns to avoid key lookups:

CREATE INDEX ix_last_position
  ON dbo.[master] -- terrible table name btw
  (device_id, date_inserted_desc)
  INCLUDE(pos_x, pos_y);

Note that this assumes the query in your question is the only (or most frequent) query pattern against this data. If you have other queries that perform the same kind of searches but have different output columns or additional filters, a different index might be better.


Trying to return a complete column from one table, and a count from another table

I think I was able to accomplish what you want using the following query:

SELECT c.name, coalesce(billcount, 0) FROM customers c
LEFT JOIN (
    SELECT c.name, c.customerid, COUNT(DISTINCT b.billid) as billcount from customers c
    JOIN bills b
    ON b.customerid = c.customerid
    WHERE b.createdate = CURDATE() -INTERVAL 1 DAY
    GROUP BY b.customerid
) bc
on bc.customerid = c.customerid

Essentially I just put your original query in a subquery (the indented area between the parenthesis), and left joined the columns you wanted with the results from that subquery.

This will always return a full list of customers and the number of bills they have, if any. Right now it's returning 0 for the customers that don't have any bills in the given time range. If you prefer it to return null, omit the coalesce() function and just use billcount in the column list on the first line. (Obviously you can rename this column to whatever you want.)


Copying a database for testing in a safe way

Is this procedure correct ?

If you have no foreign key constraints, then yes this is correct.

If you have foreign key constraints, some data may be inaccessible. You would have to fix it.

  Can I be sure that the original database will not be modified whatsoever?

After the mysqldump do the following:

grep -c "DROP DATABASE" test.sql

If you get back 0, then yes the original database will not be modified whatsoever


Sql Database server Remote access not working

The first thing you want to check is if Remote Connections are enabled on your SQL Server database.
right click the server… Go to properties  then Connections.

Under the hood of 'remote server connections' . It should be checked box tick of 'Allow remote connections to this server'

For further Ref Here


Try using Ola Halengren's SQL Server Maintenance Solution.

It has a lot of options, most important thing - it skips indexes that doesn't need maintenance (depending how You configure it).

There are few thing about index maintenance to remember. From my experience 3-4 days for full rebuilds on bigger databases isn't that much, depending on Your storage.

In some cases rebuild isn't needed, index reorganize is often enough for fragmentation between 5% and 30% (as stated in Microsoft recommendations in Books Online, and any kind of index maintenance is recommended only for indexes bigger than 1000 pages, while difference in performance might be noticable on indexes bigger than 50000 pages (as stated here).

In most cases index defragmentation isn't source of performance problems either, it just masks other issues hidden beneath. Defragmenting them too often won't improve Your performance and will stretch Your maintenance windows.

Another thing is, that maintenance creates noticeable load on IO and CPU, rebuild/reorganize operations are written in transaction log (for databases in FULL RECOVERY mode). So Your log files will get bigger, same with log backups.

More read about index maintenance on Brent Ozar's blog posts:

https://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/

https://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/

https://www.brentozar.com/archive/2014/04/index-fragmentation-bad-statistics-arent-always-problem-video/


I'm not a big fan of changing stuff directly in the registry.

You can use "computer management" and remotly connect to your core server. From there, you can enable FileStream and configure it.

I hope this will help


You can RESTORE the database and leave it in STANDBY mode.  Then it will be read-only, but you can still apply additional backups.  EG
drop table if exists AdventureWorks2017.dbo.foo
create table AdventureWorks2017.dbo.foo(id int identity, a int)

go
backup database AdventureWorks2017 to disk='c:\temp\aw.bak' with init, format

insert into AdventureWorks2017.dbo.foo(a) values (1)

backup database AdventureWorks2017 to disk='c:\temp\aw.diff.01.bak' with init, format, differential

insert into AdventureWorks2017.dbo.foo(a) values (1)

backup database AdventureWorks2017 to disk='c:\temp\aw.diff.02.bak' with init, format, differential

go

RESTORE DATABASE [AdventureWorks2017_copy]
FROM  DISK = N'C:\temp\aw.bak'
WITH
MOVE N'AdventureWorks2017' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2017_copy.mdf',
MOVE N'AdventureWorks2017_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2017_copy_log.ldf',
NOUNLOAD,
STATS = 5,
STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2017_standby.bak'

go
select * from AdventureWorks2017_copy.dbo.foo

go

RESTORE DATABASE [AdventureWorks2017_copy]
FROM  disk='c:\temp\aw.diff.01.bak'
WITH STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2017_standby.bak'

go

select * from AdventureWorks2017_copy.dbo.foo

go

RESTORE DATABASE [AdventureWorks2017_copy]
FROM  disk='c:\temp\aw.diff.02.bak'
WITH STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2017_standby.bak'

go

select * from AdventureWorks2017_copy.dbo.foo

outputs
id          a
----------- -----------

(0 rows affected)

Processed 112 pages for database 'AdventureWorks2017_copy', file 'AdventureWorks2017' on file 1.
Processed 1 pages for database 'AdventureWorks2017_copy', file 'AdventureWorks2017_log' on file 1.
RESTORE DATABASE successfully processed 113 pages in 0.056 seconds (15.651 MB/sec).
id          a
----------- -----------
1           1

(1 row affected)

Processed 112 pages for database 'AdventureWorks2017_copy', file 'AdventureWorks2017' on file 1.
Processed 1 pages for database 'AdventureWorks2017_copy', file 'AdventureWorks2017_log' on file 1.
RESTORE DATABASE successfully processed 113 pages in 0.056 seconds (15.651 MB/sec).
id          a
----------- -----------
1           1
2           1

(2 rows affected)


Managing deleted entries in large database table

Partitioning is particularly useful for this.

See https://stackoverflow.com/questions/12929624/partitions-and-update for an example of how an update can cause rows to be moved to a different partition.

This enables you to keep your table structure as it is, including almost all existing code, while queries for active data will only query the appropriate parition.


Loop over all records in a table and edit a column in SQL Server

No, don't loop. Any time you think in SQL "I need to loop over this set and do something to every row" change your thinking to "I need to do something to every row in this set."

DECLARE @start SMALLDATETIME = '2015-08-06T15:00:00';

;WITH x AS
(
  SELECT id, creatie, rn = ROW_NUMBER() OVER (ORDER BY id) - 1
  FROM dbo.Topic
  --WHERE creatie = '2131-08-06T15:00:00'
)
SELECT id, creatie, DATEADD(MINUTE, rn, @start) FROM x ORDER BY id;
-- UPDATE x SET creatie = DATEADD(MINUTE, rn, @start);

When you are happy that this does what you expect, comment the SELECT and uncomment the UPDATE.


Rather than abort the update, just override it?

...
FOR EACH ROW BEGIN
    SET NEW.Schueler_ID = OLD.Schueler_ID;
    SET NEW.Klasse_ID = OLD.Klasse_ID;
END