how to upgrade the config servers to use WiredTiger in mongodb 3.0.0

The current wording in the documented steps may be somewhat confusing given the relative references to first/last/second config servers.

I've added some context for the documented steps below, but you should consider the manual the definitive source. It's also worth noting that you do not have to upgrade the config servers to use WiredTiger (even if your shards are using WiredTiger). Config servers typically have a small data set and are not under high write load.

Annotated version of the steps from the MongoDB 3.0 Upgrade Guide

Disable the balancer.

Disabling the balancer ensures any active migrations have completed.
Stop the last config server listed in your mongos' configDB setting (will call that config3 for the purpose of these steps):

At this stage you should have:

config1 (running mmap)
config2 (running mmap)
config3 (stopped)

Stopping one of the config servers ensures there are no changes to the metadata in the cluster (chunk splits or migrations cannot be committed without all three config servers available).
Use mongodump to export the config database from config2

After running the mongodump you should have a dump directory with bson files.
Create a new data directory on config2.

The storage format for WiredTiger data is different from the existing mmap data, and cannot use the same dbpath as mmap.
Restart the config2 server with the WiredTiger and appropriate storage options:

mongod --storageEngine wiredTiger --dbpath <newWiredTigerDBPath> ...

At this stage you should have:

config1 (running mmap)
config2 (running WiredTiger with no data)
config3 (stopped)

Note that config2 doesn't have any data yet, because it has just been started up with the new WiredTiger dbpath.
Use mongorestore to load the config database backup you created in step 3.

At this stage you should have:

config1 (running mmap)
config2 (running WiredTiger)
config3 (stopped)

Shut down config2:

At this stage you should have:

config1 (running mmap)
config2 (stopped)
config3 (stopped)

config2 is stopped at this point to ensure no metadata changes can happen when we start config3 up in the next step.
Restart config3.

At this stage you should have:

config1 (running mmap)
config2 (stopped)
config3 (running mmap)

(steps 9-15) These steps are just repeating the same mongodump and mongorestore for each config server. There's a bit of shuffling to ensure you always have at least one config server available, and do not have all three up while you are still migrating data.

Upload the data into config1

At this stage you should have:

config1 (running WiredTiger)
config2 (stopped)
config3 (running WiredTiger)

Start config2

With all three config servers are available & upgraded, changes to the sharded cluster metadata can now resume.
Re-enable the balancer so normal balancing activity & chunk migration can resume.


In my experience, separating data and log files from the O/S is a frequently found default setup used by experienced DBAs.  Use Windows Backup (or some other full-disk backup system) to backup the O/S drives.  Use SQL Server backup to backup the data and log files.  Backing up the O/S allows you to easily restore the entire thing to a different machine as part of disaster recovery.

There are two schools of thought for the location of SQL Server data files and log files.

Brent Ozar thinks keeping both data and log on the same volume is a good idea since it reduces the chances that a disk problem will result in the database going offline.  As long as you have good backups, this is a sound strategy.
Other people1 recommend having the data separately from the log files which allows you to provide better performance for both since you'll have doubled the I/O capacity.  This can be an important factor if you have dedicated local devices for each volume, and they're not actually on the same SAN or RAID set.

It makes a lot of sense to have tempdb on it's own volume, and best to keep that local if you can.  Throw as many resources as you can at it.  As in putting it on a fast local SSD can have real performance benefits, especially if you have queries that spill to tempdb.

I tend to put the SQL Server installation files on their own volume, but this is not strictly necessary.

If the data you'll be storing in SQL Server is important, and honestly what data isn't, then you must ensure the location where you store backups is wholly unrelated to the location where you store the data and log files.  In other words, don't store backups and data on the same volume, SAN, or RAID set.  Also, no backup should be considered "good" unless and until you successfully restore that backup and validate its integrity via DBCC CHECKDB, or some other method.

1 - myself included


You need to rebuild the table in order to shrink the ibd file. This command will do it:

optimize table abc.test;


Log Shipping - Secondary Database(s) - Which recovery model?

The recovery model for the secondary will be the same as the primary database, because you're backing up the primary, restoring it on the secondary node using NORECOVERY/STANDBY, and then restoring subsequent log files to it.

In a log shipping configuration, you cannot make changes to the secondary database. If you could, then the databases would be out of sync, defeating the purpose of log shipping as a DR solution.

Edit: The Microsoft documentation is fairly comprehensive regarding the basics of log shipping and the requirements for it.


Soccer league ER diagram (implementing a many to many relationship)

Do a Google of "open source soccer league software" and you will find a plethora of offerings already on the "market". My advice to you is to download a few of these, play with them (use last year's results).

See which ones come closest to what you want to do. Then take a look at their database schemas (and code if it's written in something you can read) and adopt and adapt the bits that suit you and leave the rest.

Unless you're planning to do this as a hobby project (and even then), but if you want to get up and running quickly, this is probably a good strategy.


How to select from pdbs with a cdb user?

That error means your listener is not running. Start your listener.
This just works normally without doing anything:
SQL> create user c##myadmin identified by password;

User created.

SQL> grant dba to c##myadmin container=all;

Grant succeeded.

SQL> alter user c##myadmin set container_data=all container=current;

User altered.

SQL> conn c##myadmin/password
Connected.
SQL> select con_id, name from v$containers;

    CON_ID NAME
---------- ----------
         1 CDB$ROOT
         2 PDB$SEED
         3 PDB1
         4 PDB2

SQL> select count(*) from dba_users@pdb1;

  COUNT(*)
----------
        37

SQL> select count(*) from dba_users@pdb2;

  COUNT(*)
----------
        37

SQL> select db_link from dba_db_links;

DB_LINK
--------------------------------------------------------------------------------
SYS_HUB

An alternative is the CONTAINERS clause, but that has some serious limitations (queried object must exist in all containers).


How to enforce lowercase for all data in specific column?

You need a trigger for that:

create function make_lower_name()
  returns trigger
as
$$
begin
  new.name := lower(new.name);
  return new;
end;
$$
language plpgsql;

create trigger ensure_lower_name_trg
   before update or insert on users
   for each row
   execute procedure make_lower_name();


Postgresql connection refused from java connection class

You are connecting using java via TCP/IP and using psql via named pipe. The database server uses pg_hba.conf configuration file for checking connections. This file, when changed, does not require the database to restart, but it should be notified for reloading it. In that file you need at least one line for psql executed locally, and one line for java connection. The line you showed starts wish host, so this is for TCP/IP connection. Before that, you might add a line for psql like local all all trust. The first all is the database name, the second one is the user name.

About the connection: your database is configured correctly, so maybe, your TCP connection is blocked by a firewall. Does you server really have that IP address, or is there any firewall that forward connection from that IP? In the former case, test your java connection from the server, in the latter check your firewall settings.


index used when query process on oracle database

Any Relational Database, not just Oracle, will work out for itself what it considers to be the "best" way of getting the data you ask for.
It bases its "working out" on many, many factors, but most of these are taken from the statistics that it automatically gathers about every table, index, etc.
The fact that your query plan changed when you gathered statistics on the table suggests that the statistics were "stale", that is to say "out of date" compared to what was actually in the table.
An extreme example of this might be if you created an empty table last thing one evening.  The statistics job generally runs overnight and would note that the table had no rows in it.  If, the following morning you loaded it with millions of records, it is quite likely that any query against that table would simply try to Table Scan it, "believing" it (based on the statistics) to be empty.  Updating the statistics, showing the DBMS the "truth" about the table's contents allows it to make a better assessment of how to "get at" rows in that table and would, most likely, improve things dramatically.


Mysql innoDB write operations are extremely slow

innodb_flush_log_at_trx_commit = 2 for better speed (=1 for better safety)
sync_binlog = 0 if you are not using the binlog.
"Batch" INSERTs — many rows in a single INSERT statement. (If you have lots, do 100 at a time.)  LOAD DATA is also excellent for loading.
innodb_buffer_pool_size = 70% of available RAM.
auto_commit = 1, or use BEGIN ... COMMIT
thread_stack = 256K I'm surprised it did not crash with your small value.

Still, the times (1s-6s) are grossly unreasonable.  Even with everything set "wrong", 0.1s for a simple INSERT should be expected.

Is this running in a "cloud"?  Is something else going on?


MySQL trigger to update a key if record exists

Why write a trigger for this ? I have two reasons why you should not do that in this instance.

REASON #1

Let's assume the following:

reference_key

is not an auto_increment column
is the primary key

counter

is the column to increment
is defined as INT NOT NULL DEFAULT 1

You can write the INSERT as follows

INSERT INTO individual_key_log (reference_key,...)
VALUES (...) ON DUPLICATE KEY UPDATE counter = counter + 1;

This is a much cleaner approach than a trigger

REASON #2

I noticed you have a CALL in the middle of your trigger. Believe me, you do not want to make a trigger that breaks midstream unless it is necessary. Calling anything in a trigger requires overhead and can hamper query performance. I wrote about this back on Jan 13, 2012 : Call a stored procedure from a trigger

I also wrote about how jerryrigging a trigger can halt normal operation if not done properly : See my post from Apr 25, 2011 : Trigger in MySQL to prevent insertion where I demonstrate how to halt a trigger to prevent insertion checking a separate table.


Can AWS Data Migration Service be used to integrate non AWS SQL server with Redshift?

According to the following resource link, it seems you can: https://aws.amazon.com/about-aws/whats-new/2016/05/aws-database-migration-service-now-supports-migrations-to-amazon-redshift/
Or
https://aws.amazon.com/about-aws/whats-new/2017/05/aws-schema-conversion-tool-exports-from-sql-server-to-amazon-redshift/


How did you split strings before string_split()

From comments:

  The built-in STRING_SPLIT() function was added in SQL Server 2016, not 2014. And for me, I go the SQLCLR route because I am not using Azure Single DB or AWS RDS and thus have no reason not to. I use the SQLCLR library that I created, SQL#, which contains String_Split and String_SplitInts (optimized for splitting a list of BIGINT / INT / SMALLINT / TINYINT values). The "4k" versions of both are to be used when the input string is guaranteed to never be more than NVARCHAR(4000) as they are faster than the non-"4k" versions. – Solomon Rutzky

  there were lots of User-Defined Table-Valued functions floating around that did this. Here's a survey article by Aaron Bertrand: sqlperformance.com/2012/07/t-sql-queries/split-strings – David Browne - Microsoft

  this is probably the most efficient by Jeff Moden sqlservercentral.com/articles/… – Squirrel


Basically, you're missing a GROUP BY! To answer your question, I did the following:

Created tables (relevant fields only):

CREATE TABLE brand
(
  brand_id INTEGER,
  name VARCHAR(25)
);

CREATE TABLE product
(
  product_id INTEGER,
  brand_id INTEGER,
  name VARCHAR(30) NOT NULL
);

CREATE TABLE product_sale
(
  product_sale_id INTEGER,
  product_id INTEGER,
  quantity INTEGER
);

Then I created some sample records:

INSERT INTO brand VALUES (1, 'Ferrari'), (2, 'Lamborghini'), (3, 'Porsche');

INSERT INTO product VALUES (1001, 1, 'TestaRossa'), (1002, 1, 'Dino'), (2001, 2, 'Countach'), (2002, 2, 'Diabolo'), (3001, 3, 'Cayenne'), (3002, 3, '911'), (3003, 3, 'Boxter');

INSERT INTO product_sale VALUES (500001, 2001, 45), (500002, 1001, 12), (500003, 3003, 7), (500004, 3001, 750), (500005, 3001, 400);

and then ran this query:

SELECT
  b.name AS "Brand name",
--  p.name as "Product name",
  SUM(ps.quantity) AS "Quantity"
FROM brand b
JOIN product p
ON b.brand_id = p.brand_id
JOIN product_sale ps
ON p.product_id = ps.product_id
GROUP BY b.name;  -- p.name

I left in the extra p.name fields (commented out with --) so that you can see how the query was constructed. Take out the comments and have a look.

Result:

Brand name  Quantity
Ferrari     12
Lamborghini 45
Porsche     1157

All of this can be found at db-fiddle here. For future reference, it's better to have a Minimum Complete and Verifiable Example (preferably complemented with a fiddle) which a) means that everyone is singing off the same hymnsheet - i.e. your data and b) there's no duplication of effort. You will obtain quicker and better answers with a well-constructed fiddle. You might also like to take a look here, and maybe take the tour and visit the helpdesk.

These sites are a great resource, all the more so if you take the time and effort to follow the guidelines. p.s. welcome to the forum!


PostgreSQL count 0 using WHEN and GROUP BY with more than one column

SELECT DISTINCT
    BUREAU.id,
    'Credit Card' as type,
    COALESCE(t1.count, 0) as count
FROM
    BUREAU
LEFT JOIN
    (SELECT
        id,
        COUNT(*) as count
     FROM
        BUREAU
     WHERE
        activity = 'Active'
        AND type = 'Credit Card'
     GROUP BY
        id) t1
    ON t1.id = BUREAU.id
ORDER BY
    id;

   id | type        | count
----: | :---------- | ----:
10001 | Credit Card |     0
10002 | Credit Card |     2
10003 | Credit Card |     0

db<>fiddle here

Or using the conditional aggregation pointed out by a_horse_with_no_name:

SELECT
    BUREAU.id,
    'Credit Card' as type,
    COUNT(type) FILTER (WHERE type = 'Credit Card' AND activity = 'Active')
FROM
    BUREAU
GROUP BY
    BUREAU.id
ORDER BY
    BUREAU.id;

db<>fiddle here


postgresql: permission denied for sequence _i_seq

Try to make changes  on all tables
Answer from Alex Soto

Since you're changing the ownership for all tables, you likely want views and sequences too.  Here's what I did:

Tables:

for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" YOUR_DB` ; do  psql -c "alter table \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done

Sequences:

for tbl in `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" YOUR_DB` ; do  psql -c "alter table \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done

Views:

for tbl in `psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" YOUR_DB` ; do  psql -c "alter table \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done

You could probably [DRY][1] that up a bit since the alter statements are identical for all three.


You can write a database trigger like this:

CREATE OR REPLACE TRIGGER account_trigger
   BEFORE ALTER ON DATABASE
   WHEN ora_dict_obj_name = 'USER'
BEGIN
   INSERT INTO LOG_TABLE (...) VALUES (...);
END;

Check this document to get more information

Maybe you can also use AUDIT ALTER USER; to get this information. See AUDIT


How to separate name string by the spaces in the name in T-SQL

Does something like this help you:

With split(id, n, start, pos) as(
    SELECT id, 1, 0, CHARINDEX(' ', fullname) FROM @names
    UNION ALL
    SELECT n.id, n+1, pos+1, CHARINDEX(' ', fullname, pos+1)
    FROM @names n
    INNER JOIN split s ON n.id = s.id
    WHERE CHARINDEX(' ', fullname, start+1) > 0
)
SELECT id
    , lastName = MAX(CASE WHEN v.n = 1 THEN v.val END)
    , firstname = MAX(CASE WHEN v.n = 2 THEN v.val END)
    , middleName = MAX(CASE WHEN v.n = 3 THEN v.val END)
    , title = MAX(CASE WHEN v.n = 4 THEN v.val END)
FROM (
    SELECT n.id, s.n
        , SUBSTRING(n.fullname
                , s.start
                , CASE WHEN s.pos = 0 THEN LEN(n.fullname)+1 ELSE s.pos END -s.start
            )
    FROM @names n
    INNER JOIN split s ON n.id = s.id
) as v(id, n, val)
GROUP BY id
--OPTION(MAXRECURSION  4)
;

The recursive CTE get the start and end position of each name along with a value n from 1 to up to 4.
Based on the value of n the GROUP BY put them in their respective columns.

On a big table, it may not be very efficient though.

Output:

id  lastName    firstname   middleName  title
0   Doe         John        James       NULL
1   Doe         Jane        NULL        NULL
2   Doe         Henry       Albert      Sr
3   Bart        Simpson     NULL        NULL

Data:

declare @names table(id int identity(0, 1), fullname varchar(200), first varchar(50), middle varchar(50), last varchar(50), title varchar(50));
INSERT INTO @names(fullname) values
    ('Doe John James')
    , ('Doe Jane')
    , ('Doe Henry Albert Sr')
    , ('Bart Simpson')
;


If you don't care which you get, you can use something like this:

select max(v.id),
       max(v.purchasedate),
       max(v.customerid),
       v.assetid,
       max(va.description),
       max(vb.title)
  from purchases v,
           asset va,
           assetdescription vb
 where customerid = '$kid'
   and v.assetid = va.id
   and vb.assetid = va.id
 group by v.assetid
 order by v.purchasedate;

If you want e.g. the "latest", then you can use something like this:

select t.id,
       t.purchasedate,
       t.customerid,
       t.assetid,
       t.description,
       t.title
from (
    select v.id,
           v.purchasedate,
           v.customerid,
           v.assetid,
           va.description,
           vb.title,
           row_number() over (partition by v.assetid order by v.purchasedate desc) as rn
      from purchases v,
               asset va,
               assetdescription vb
     where customerid = '$kid'
       and v.assetid = va.id
       and vb.assetid = va.id
) t
where rn = 1
order by v.purchasedate

The decision which one to take is controlled by the ordering inside the window definition (partion by v.assetid order by ....)


visual studio 2017 vs sql management studio

Although Management Studio is based-upon Visual Studio, the two are different products and have different target audiences. You will not be able to use Visual Studio to do all of the tasks that you used to use Management Studio for. Even installing the data tools and creating a SQL Server project within Visual Studio will not give you the functionality available in Management Studio.

Visual Studio is primarily used to make development easier whereas Management Studio is orientated towards making the management of SQL Server easier (with some development components.)

Like I tell a lot of the guys that work with me, choose the right tool for the job. I have both installed, though I do agree that they use a hefty amount of space when installed together.


Connect to Oracle using SQLDeveloper using OS Authentication on a remote machine

These are the default values related to this:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$
remote_os_authent                    boolean     FALSE

Set remote_os_authent to true:

alter system set remote_os_authent=true scope=spfile sid='*';
shutdown immediate
startup

Create a database user with your OS username, prefixed with the above value:

create user ops$mangeles identified externally;
grant create session to ops$mangeles;

This way you can log in without a password because of the remote OS authentication. The problem is, anyone can create an OS user named mangeles on any machine, and they will be able to log in without providing a password.

SYSDBA will not work with this type of authentication, if you try grant sysdba to ops$mangeles, you will get ORA-01997: GRANT failed: user 'OPS$MANGELES' is identified externally.


You can subtract the five hours from the resulting interval, before the formatting takes place:
to_char(resolved_datetime - opened_datetime - interval '5 hour', 'hh24:mi')

Note that to_char(..., 'hh24:mi') on an interval, might not give you what you want, if the difference is greater than 24 hours, e.g. an interval of 2 days 3 hours 5 minutes formatted with that, will display as 03:05, not as 51:05


Here are the two stored procedures that need to be updated.

sp_BlitzWho.sql and

sp_BlitzFirst.sql


Oracle sys SYSDBA audit information to a report

As you already know that SYS user audit information is stored in an OS file there is no such tables or views to query in the database.

Workaround is to create an external table.

Here is an example from AskTom.

Audit file as External Table input


Can Oracle SQL developer export data as a set of Update commands?

What you want is MERGE.

See Oracle docs for MERGE . Allows you determine if a row should be updated or inserted.

SQL Developer doesn't support generating MERGE statements today. You can go vote  for that feature to make a future version though!


It's a pain, but Teradata doesn't support CTE in views (as of 15.00), see SQL Data Manipulation Language > The SELECT Statement > WITH and WITH RECURSIVE Statement Modifiers.

In your case you can create another view with the contents of the CTE, but you probably know that already.


Necessity of having two instances (Reader and Writer) in Amazon Aurora

Yes, you are paying twice for the instances, but only once for the storage, because they share a single Aurora cluster storage volume (there is only one volume per cluster), which is part of why replication is nearly instantaneous.

You can remove the reader instance, as long as you understand that this will slow down recovery if the lone remaining instance fails -- Aurora will have to spin up a new one, instead of automatically promoting the reader to become a writer, which is what it would otherwise do if the current writer fails.


Putting SQL Server databases into individual folders

Now this should really be a feature, I tried to find it on Microsoft connect but did not if anyone has a way to do this via database properties I for one would be very happy.

But as it stands this is not possible except by changing the default database location registry setting either in the server properties or like this.

USE [master]
GO
— Change default location for data files
EXEC   xp_instance_regwrite
   N'HKEY_LOCAL_MACHINE',
   N'Software\Microsoft\MSSQLServer\MSSQLServer',
   N'DefaultData',
   REG_SZ,
   N'C:\MSSQL\Data'
GO
— Change default location for log files
EXEC   xp_instance_regwrite
   N'HKEY_LOCAL_MACHINE',
   N'Software\Microsoft\MSSQLServer\MSSQLServer',
   N'DefaultLog',
   REG_SZ,
   N'C:\MSSQL\Logs'
GO

But if you have multiple databases on the server you would have to remember to change this before adding a file to each of them.

There is also the possibility to to this dynamically, you could create a stored procedure that you call with a database name that will read the current path for the mdf file and add a new file to the same path so to borrow a bit from stackexchange

create procedure USP_ADDFILE

@database sysname,
@logicalname sysname,
@filename sysname,
@filegroup sysname,
@size int
AS
if @database is NULL return
if @filename is null return
if @filegroup is null set @filegroup = 'Default'
if @size is null return
Declare @Path nvarchar(max)
Declare @Sql nvarchar(max)

select @Path = (select LEFT(physical_name
                   ,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1)
    FROM sys.master_files mf
    INNER JOIN sys.[databases] d
    ON mf.[database_id] = d.[database_id]
    WHERE d.[name] = @database AND type = 0)

Set @Sql = 'Alter Database '+ QuoteName( @database )+'
    Add File( Name = '''+ @filename +'''
            , SIZE = '+ CAST (@size as nvarchar(10)) +'GB
            , FileName = ''' +  @Path+@filename+'.NDF'''
            + ') To FileGroup '+QuoteName(@filegroup)  +''
exec ( @Sql )


Error when trying to install SQL Server 2012 Ent in to a Server Core 2008 VM

Nothing seems wrong with your setup. I too am doing the training and noticed the books had some errors in them.

But notice the difference between the 'WHOAMI' output, and your installation command line.
The book has several typos where it calls the domain: Contoso, while before you created a domain called Contso.

Change your command line to: /SQLSYSADMINACCOUNTS="Contso\kim_akers" and it should work.


SQL Server "empty table" is slow after deleting all (12 million) records?

You've already been told about why TRUNCATE would be so much faster/better/sexier than DELETE, but there's still a question left to address:

Why is SELECT slower after DELETE completed?

That is because DELETE has only ghosted the rows. The table is just as big as when it had 12M rows, even though it has none. To count the rows (0), it takes just as much time it took to count 12M rows. In time the ghost cleanup process will garbage collect these ghosted records and deallocate pages that contained only ghosts, and your SELECTs will speed up. But right now if you check Skipped Ghosted Records/sec in perfmon is probably skyrocketing during SELECT COUNT(*). You could also speed up things by rebuilding the table: ALTER TABLE ... REBUILD.

TRUNCATE would had also taken care of this problem, as it leaves no ghosts behind.

See also Inside the Storage Engine: Ghost cleanup in depth.


If my source database uses SPFILE then do I have create a PFILE from the SPFILE?

Yes, you need to create a temporary PFILE to use while duplicating the database. You will later switch the new instance to use the SPFILE.

Use

CREATE PFILE = 'path/to/pfile' FROM SPFILE;

  You only need to create directories that are referenced in the PFILE or SPFILE.
  Which directories need to be created?

You will start the database as NOMOUNT to begin the duplication. This means the controlfiles will not be read, and so you don't need to have the directories in which the datafiles reside present in your new host. However, the directories references in the parameter files must be present. From your transcript,

DB11G.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.audit_file_dest='/u01/app/oracle/admin/DB11G/adump'
*.control_files='/u01/app/oracle/oradata/DB11G/control01.ctl','/u01/app/oracle/oradata/DB11G/control02.ctl'#Restore Controlfile
*.db_recovery_file_dest='/u01/app/oracle/fra'
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_dest_1='location=/u01/app/oracle/archive_logs'

You may find this chapter of the documentation useful: http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmdupdb.htm#i1006474


Swap utilization is high on Oracle server

Oracle database is a database server and not an operating system, it does not know about virtual memory management and swap usage.
First identify the processes using swap to confirm that it is related to the database:

https://stackoverflow.com/questions/479953/how-to-find-out-which-processes-are-using-swap-space-in-linux

Some common pitfalls related to memory management and swapping when using Oracle database on Linux:

Even senior DBAs often treat pga_aggregate_target as a limit, but it is not a limit, it is a target value. PGA can grow way behind that value, and the value of this parameter should not be used when calculating memory requirements, because it may result in undersized memory amount. You can check the maximum PGA size since instance startup from V$PGASTAT. You may see the maximum PGA allocated to be multiple times of pga_aggregate_target. This requires further investigation in the database.
The value of filesystemio_options parameter is by default none. This means the database does not use direct I/O and because of that I/O operations are cached in the filesystem cache (when using a regular filesystem and not ASM). This coupled with a default high value of vm.swappiness often resulted unnecessarily high memory usage and swapping. filesystemio_options should be set to setall, and it is recommended to decrease vm.swappinness from the default 60/30 value (RHEL6/RHEL7).
It is common knowledge that Oracle databases allocate memory for PGA and SGA. Still most people overlook the fact that database server processes also allocate some memory that is not part of the SGA (obviously, it is not shared memory) and also not part of the PGA (required for e.g. online patching). If you have several hundred or thousands of database processes, these small per process memory allocations may add up to a significant amount of memory. Queuing requests and connection pooling should be implemented in the application server layer, not in the database layer. The number of database processes should be kept relatively low.
Linux keeps track of shared memory usage for each database process seperately. With a large number of processes and a large SGA, this accounting information may take even more memory than the PGA+SGA together. You can check the amount of memory used for this with grep PageTables /proc/meminfo. If you see a significant amount of memory here (like several GBs), that memory is kind of wasted memory, and you should configure your operating system and database to use HugePages. With HugePages configured, typically the database uses 2 MB memory pages instead of 4 KB ones and this 500-fold increase in page size results in 500 times less memory required for accounting shared memory.


Open SQL Server Management Studio and connect to the database server. Right-click the database that you want to back up, point to Tasks, and then click Back Up.


Can't run pcs -f cluster1.xml stonith create with PostgreSQL 9.6 on CentOS 7.3

Did you install the fence-agents package? That would provide you with the fence_virsh binary that pcs is complaining isn't there.


Does ' /*+parallel(,4)*/ ' have better impact on query performance in comparison with simple parallel hint '/*+parallel(4)*/'?

Sure there is, but this may or may not be noticeable. We can not know this just by looking at your table definitions.
See below.

The first query does all operations in parallel with degree 4, including table accesses and joins:
Plan hash value: 2107928671

-----------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                 |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                      |        |      |            |
|   1 |  PX COORDINATOR                   |                      |        |      |            |
|   2 |   PX SEND QC (RANDOM)             | :TQ10004             |  Q1,04 | P->S | QC (RAND)  |
|   3 |    HASH JOIN OUTER BUFFERED       |                      |  Q1,04 | PCWP |            |
|   4 |     PX RECEIVE                    |                      |  Q1,04 | PCWP |            |
|   5 |      PX SEND HASH (NULL RANDOM)   | :TQ10002             |  Q1,02 | P->P | HASH       |
|   6 |       HASH JOIN OUTER BUFFERED    |                      |  Q1,02 | PCWP |            |
|   7 |        PX RECEIVE                 |                      |  Q1,02 | PCWP |            |
|   8 |         PX SEND HASH (NULL RANDOM)| :TQ10000             |  Q1,00 | P->P | HASH       |
|   9 |          PX BLOCK ITERATOR        |                      |  Q1,00 | PCWC |            |
|  10 |           TABLE ACCESS FULL       | CUSTOMER_INFO        |  Q1,00 | PCWP |            |
|  11 |        PX RECEIVE                 |                      |  Q1,02 | PCWP |            |
|  12 |         PX SEND HASH              | :TQ10001             |  Q1,01 | P->P | HASH       |
|  13 |          PX BLOCK ITERATOR        |                      |  Q1,01 | PCWC |            |
|  14 |           TABLE ACCESS FULL       | CUSTOMER_TRANSACTION |  Q1,01 | PCWP |            |
|  15 |     PX RECEIVE                    |                      |  Q1,04 | PCWP |            |
|  16 |      PX SEND HASH                 | :TQ10003             |  Q1,03 | P->P | HASH       |
|  17 |       PX BLOCK ITERATOR           |                      |  Q1,03 | PCWC |            |
|  18 |        TABLE ACCESS FULL          | BRANCH_INFO          |  Q1,03 | PCWP |            |
-----------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 4 because of hint

The second query accesses only CUSTOMER_TRANSACTION in parallel. Other tables are not accessed in parallel. Joins to CUSTOMER_TRANSACTION are however processed in parallel.
Plan hash value: 1958681835

-----------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                 |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                      |        |      |            |
|   1 |  PX COORDINATOR                   |                      |        |      |            |
|   2 |   PX SEND QC (RANDOM)             | :TQ10004             |  Q1,04 | P->S | QC (RAND)  |
|   3 |    HASH JOIN OUTER BUFFERED       |                      |  Q1,04 | PCWP |            |
|   4 |     PX RECEIVE                    |                      |  Q1,04 | PCWP |            |
|   5 |      PX SEND HASH (NULL RANDOM)   | :TQ10002             |  Q1,02 | P->P | HASH       |
|   6 |       HASH JOIN OUTER BUFFERED    |                      |  Q1,02 | PCWP |            |
|   7 |        PX RECEIVE                 |                      |  Q1,02 | PCWP |            |
|   8 |         PX SEND HASH (NULL RANDOM)| :TQ10000             |  Q1,00 | S->P | HASH       |
|   9 |          PX SELECTOR              |                      |  Q1,00 | SCWC |            |
|  10 |           TABLE ACCESS FULL       | CUSTOMER_INFO        |  Q1,00 | SCWP |            |
|  11 |        PX RECEIVE                 |                      |  Q1,02 | PCWP |            |
|  12 |         PX SEND HASH              | :TQ10001             |  Q1,01 | P->P | HASH       |
|  13 |          PX BLOCK ITERATOR        |                      |  Q1,01 | PCWC |            |
|  14 |           TABLE ACCESS FULL       | CUSTOMER_TRANSACTION |  Q1,01 | PCWP |            |
|  15 |     PX RECEIVE                    |                      |  Q1,04 | PCWP |            |
|  16 |      PX SEND HASH                 | :TQ10003             |  Q1,03 | S->P | HASH       |
|  17 |       PX SELECTOR                 |                      |  Q1,03 | SCWC |            |
|  18 |        TABLE ACCESS FULL          | BRANCH_INFO          |  Q1,03 | SCWP |            |
-----------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 4 because of table property


current_timestamp at time zone 'UTC' SQL Server equivalent

What you're looking for is the SYSUTCDATETIME function, which is used like this:

SELECT SYSUTCDATETIME();

It returns the current system date and time, adjusted to UTC time.

This seems similar to what the postgres equivalent does (except it looks like current_timestamp includes timezone information, while SYSUTCDATETIME does not).

If you need the time zone information, you can use a similar construction in SQL Server with the SYSDATETIMEOFFSET function:

SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'UTC';

The results look like this:


How to update xml using value from a sql query

You've got nothing to replace in <SiteID/>. Try and use an insert instead:

update Sites SET [SiteInfo].modify('insert text{sql:column("InVal")} as first into (/SiteInfo/SiteID)[1]');


How dependent are backups on msdb tables like backupset?

If you have a single file with all of a set of backups in that single file, you should be able to use the command:

 RESTORE HEADER ONLY FROM backup device ...

This will provide you all of the details within that file, including the log file LSNs and other details you might need.

http://technet.microsoft.com/en-us/library/ms178536.aspx

The EXPIREDATE and the RETAINDAYS are just data that you include with the backup.  You have to decide how to use that information.  In an appended file, it is mystifying indeed. You could perhaps use the information to skip over backups that expired.  (For me, each backup is a separate file.) See Tibor Karaszi's comments at:

http://sqlblog.com/blogs/tibor_karaszi/archive/2012/07/08/backup-and-the-evil-retaindays-option.aspx


Can I stop a SQL database backup after it has already started?

Straight to answer your questions :

  Can I stop, pause, or cancel the backup after it has started if it is taking too long or slowing things down too much?

You cannot pause a backup - either running using Tsql or using sql agent job. If your db is in mirroring, then when you failover the backup gets killed.

Since you are on sql server 2008, recently I ran into a weird situation that caused an outage of one of our critical app is that if you are not on the latest SP (2008 SP4) - we were running sp1 on on node that had a bug and was fixed in SP1+CU4 - It was resolved in CU4 for SP1:https://support.microsoft.com/en-US/help/973602.  Search, “970133” for: FIX: When you create a compressed backup for a database in SQL Server 2008, you cannot stop the backup operation if the backup operation stops responding.

You can stop it or kill it using the kill spid command.

you have to be cautious as if you kill a job that is already in progress, it has to ROLLBACK which will take some or more time.

  Is a backup just a job, that I would just stop the job?

Depends on how you are running the backup. If you run it using SQL Agent job, then its a job which you can STOP or if you are running it directly from SSMS or SQLCMD, then you  can CANCEL or KILL the command.

  When doing a backup, are you able to view the progress, and see an estimated completion time of the backup? If so, how do you view its progress?

You can use DMVs to monitor the backup (or restore) progress (if you are not using WITH STATS = number):

----- find out the ETA time for restore and backup progress

SELECT command,
            s.text,
            start_time,
            percent_complete,
            CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
            CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

Some food for thoughts :

Start checking what is causing "slow performance" - analyze Wait stats, locking and blocking problems, run sp_whoisactive, etc
If you really found out that doing backups is causing the slowness then schedule backups when there is minimal activity and follow Aaron's advise and make sure to read some common backup myths - from Paul Randal.
There are Options to Improve SQL Server Backup Performance as well.


You need to stop using InnoDB (with innodb_file_per_table off) and LVM snapshots as soon as possible. Here is why:
I have a monitoring system which uses MySQL as its database. It has innodb_file_per_table off as well. In a space of 6 months, it grew to 1.3TB.
My employer's company cannot have downtime for this monitoring system.
I attempted to create a Slave for this monster database. I ran an rsync of /var/lib/mysql to the Slave server. Its first pass took 42 hours (That's not a typo, 1 day 18 hours). The second pass took 84 hours (3 days 12 hours) and was only 15% done (220GB copied). As you can see, I abandoned the second rsync.
The problem stemmed from ibdata1 being 1.3TB.
What is inside ibdata1 when innodb_file_per_table is OFF?

Table Data Pages
Table Index Pages
Table Metadata (Tablespace ID Management)
MVCC Data (for Transaction Isolation, ACID Compliance)

In a write-heavy environment, all records of these four types are being read and/or written. The rsync experienced a nightmare trying to get the ibdata1 coalesced enough to write changes to the Slave server. According to my Senior Linux Engineer counterparts, an LVM snapshot would not fare any better.
Your only recourse is to start using innodb_file_per_table.
For you to see this need further, please run this query (should take 5-10 min for you)
SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM
information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

Using the totals for InnoDB, compare it to the size of ibdata1. You will find that there may be some a difference of 30GB of less. I ran than query against ibdata1 in my monitoring system and had only 27GB for table metadata and new MVCC info. Steady growth would always occur on this file.
Here is what you can do: Convert all your InnoDB to use innodb_file_per_table. I wrote a post in StackOverflow on how to do that. Not only will you be able to have easch table exist in a separate file, you will be able to keep ibdata1 rate of growth down to a bare minimum.
UPDATE 2012-02-22 13:00 EST
When executing the CleanUp of InnoDB, make sure you eliminate the making of multiple ibdata files. your setting should be the default:
[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend


You might check out Percona's Xtrabackup solution and see if it will work with MariaDB as all seem to be drop-in replacements.

See: https://www.percona.com/doc/percona-xtrabackup/2.1/xtrabackup_bin/incremental_backups.html

Some articles imply this would work in MariaDB. I only use Percona so cannot confirm unfortunately: https://mariadb.com/kb/en/mariadb/backup-restore-and-import-xtrabackup/


DISTINCT COUNT with GROUP BY query is too slow despite indexes

You'll need no three separate indexes, but one combined (covering) index on  creation_date, zone, vessel_imo (in this column order)

I don't know, how exactly MySQL works on this point, but it could help, when you would group by creation_date instead of the (calculated) date. On the other hand, when creation date contains the time and you do not output the time, then you have to group by by the (calculated) date.


How do I get the current unix timestamp from PostgreSQL?

In postgres, timestamp with time zone can be abbreviated as timestamptz, and timestamp without time zone as timestamp. I will use the shorter type names for simplicity.

Getting the Unix timestamp from a postgres timestamptz like now() is simple, as you say, just:

select extract(epoch from now());

That's really all you need to know about getting the absolute time from anything of type timestamptz, including now().

Things only get complicated when you have a timestamp field.

When you put timestamptz data like now() into that field, it will first be converted to a particular timezone (either explicitly with at time zone or by converting to the session timezone) and the timezone information is discarded. It no longer refers to an absolute time. This is why you don't usually want to store timestamps as timestamp and would normally use timestamptz — maybe a film gets released at 6pm on a particular date in every timezone, that's the kind of use case.

If you only ever work in a single time zone you might get away with (mis)using timestamp. Conversion back to timestamptz is clever enough to cope with DST, and the timestamps are assumed, for conversion purposes, to be in the current time zone. Here's an example for GMT/BST:

select '2011-03-27 00:59:00.0+00'::timestamptz::timestamp::timestamptz
     , '2011-03-27 01:00:00.0+00'::timestamptz::timestamp::timestamptz;

/*
|timestamptz           |timestamptz           |
|:---------------------|:---------------------|
|2011-03-27 00:59:00+00|2011-03-27 02:00:00+01|
*/

DBFiddle

But, note the following confusing behaviour:

set timezone to 0;

values(1, '1970-01-01 00:00:00+00'::timestamp::timestamptz)
    , (2, '1970-01-01 00:00:00+02'::timestamp::timestamptz);

/*
|column1|column2               |
|------:|:---------------------|
|      1|1970-01-01 00:00:00+00|
|      2|1970-01-01 00:00:00+00|
*/

DBFiddle

This is because:

  PostgreSQL never examines the content of a literal string before determining its type, and therefore will treat both […] as timestamp without time zone. To ensure that a literal is treated as timestamp with time zone, give it the correct explicit type…In a literal that has been determined to be timestamp without time zone, PostgreSQL will silently ignore any time zone indication


How do you identify InnoDB table corruption?

Morgan gives a hint in his comment that InnoDB is constantly checking for corrupted pages by doing checksums on the pages it reads. If InnoDB finds a checksum mismatch, it will crash stop the server.

If you want to speed that process up (instead of waiting for InnoDB to read the corrupted page), you can use innochecksum:

  Because checksum mismatches will cause InnoDB to deliberately shut down a running server, it can be preferable to use this tool rather than waiting for a server in production usage to encounter the damaged pages.

An interesting caveat:

  innochecksum cannot be used on tablespace files that the server already has open. For such files, you should use CHECK TABLE to check tables within the tablespace.

So yes, for  an online table CHECK TABLE is probably the tool (or as pointed out in another answer mysqlcheck if you want to do more than a single database at a time.)

If you can shut down your database you can force it the checksums using innochecksum

Anecdotal:
On a innodb tablespace of 29GB  (with innodb_file_per_table=1), this script took about 2 Minutes

#!/bin/bash
for i in $(ls /var/lib/mysql/*/*.ibd)
do
  innochecksum -v $i
done

As a bonus, though, since you are running Percona, they implemented a new method for fast innodb checksum. I've never used it, but it might speed up the process.


Snowflake: how to allow only Key Pair Authentication connections?

I figured out the solution:

Create public/private key pair
Assign public key to a Snowflake user, e.g.
alter user testuser set rsa_public_key='ADNDDBgkqh...';
Set the user's password to null: alter user testuser set password = null;
For connection don't forget to specify properties: private_key_file and private_key_file_pwd


SSRS 2014 - query running fine on management studio - report timing out - how to troubleshoot?

You need to look at the Execution logs. On your Report Server database, there's a view called ExecutionLog3. You can query this view by date and find how the user was running the report.

USE [ReportServer]
GO

SELECT *
FROM dbo.ExecutionLog3 AS el
WHERE el.TimeStart >= '2016-08-02'


Log and Data Drive Configuration in VM environment

It really depends on what's best for your situation as long as you follow some guidelines.  We keep the disks for transaction logs separate because often RAID 6 doesn't give us the IOPS we want during peak usage, but many times it does.  It depends on your peak IOPS requirements, future growth, and file management. Note that these best practices were created when 7200 and 10k RPM magnetic disks were still expensive.

Isolation, Throughput, Management, and Tail Of Log Backups

One issue with putting it all on 1 big array is that different workloads will now affect your log files where before it wouldn't.  If someone does a SELECT * on a huge table and it has to load it into memory, you're fighting for resources off the disk now. All of a sudden you have a different workload when you're trying to troubleshoot what happened.

However, Perhaps you have really good SSDs and your peak writes won't really matter.  Another item is you could configure write cache on different arrays so you could dedicate your entire cache to log files.  Again, if you're on SSDs on your workload, or you don't have those caching options that might not matter.  Another reason to have your own array was to short stroke the disks, that was very helpful for magnetic disks.  Remember that data fragmentation could affect the log file too.  Sure you could have VLFs (logical fragmentation) taken care of easily but what happens when the underlying disk decides to move data around or you get fragmentation due to how the files are growing?  In many cases that won't cause a problem, and in some it will.

Also, note that if anything happens to that array, physical OR logical, you can kiss tail of the log backups goodbye.  If your data loss requirements are OK with that then make sure to get that in writing.


How to cache query for re-use in following UNION of queries?

As you say, temporary tables are only visible within the session, and materialized views are not available in MySQL. There are several options (including the ones you have mentioned). I will name your original query as SELECT ...:

The WITH Oracle keyword is equivalent to CREATE TEMPORARY TABLE:

CREATE TEMPORARY TABLE savedquery AS SELECT ...; -- you execute this for every connection
SELECT id FROM savedquery WHERE .....
UNION
SELECT id FROM savedquery WHERE .....;

You will have to create it for every connection.
A VIEW is shared among connections, but it is not an actual table- it can be executed in two ways, by creating a temporary table at the beginning of the execution or by executing the corresponding subquery:

CREATE VIEW savedquery
ALGORITHM = TEMPTABLE  -- you probably want to use temptable algorithm
AS SELECT ...;         -- you execute this only once

SELECT id FROM savedquery WHERE .....
UNION
SELECT id FROM savedquery WHERE .....;

If you were using 5.6, the subquery algorithm may be better, but this is without knowing the actual query.
As materialized views are not an option, you can emulate them by creating a real table, that will be shared among connections:

CREATE TABLE savedquery AS SELECT ...;
SELECT id FROM savedquery WHERE .....
UNION
SELECT id FROM savedquery WHERE .....;

The problem with this is that it is not automatically updated (you have to do that with triggers or any other way); the positive thing is that you can add indexes (unlike views).

The problem is that your original query and your derived query are probably not going to be very efficient, and you probably could modify it to perform better, but that is another question...


Trim Trailing Whitespace, Tab, Newline, Carriage Returns, etc

The best way would probably be to create a CLR function and use .NET framework libraries.

A TSQL attempt is below.

DECLARE @Pattern NVARCHAR (50) = N'%[^' +
/*List from http://stackoverflow.com/a/18169122/73226 */
NCHAR(0x0009) +  /*tab*/
NCHAR(0x000A) +  /*line feed*/
NCHAR(0x000B) +  /*line tabulation*/
NCHAR(0x000C) +  /*form feed*/
NCHAR(0x000D) +  /*carriage return*/
/*Various spaces from https://www.cs.tut.fi/~jkorpela/chars/spaces.html */
NCHAR(0x0020) +
NCHAR(0x00A0) +
NCHAR(0x1680) +
NCHAR(0x180E) +
NCHAR(0x2000) +
NCHAR(0x2001) +
NCHAR(0x2002) +
NCHAR(0x2003) +
NCHAR(0x2004) +
NCHAR(0x2005) +
NCHAR(0x2006) +
NCHAR(0x2007) +
NCHAR(0x2008) +
NCHAR(0x2009) +
NCHAR(0x200A) +
NCHAR(0x200B) +
NCHAR(0x202F) +
NCHAR(0x205F) +
NCHAR(0x3000) +
NCHAR(0xFEFF) +
N']%' COLLATE Latin1_General_100_BIN2;

WITH T(String) AS
(
SELECT '         leading only' union all
SELECT '

Line breaks

' union all
SELECT '    tab and space    ' union all
SELECT ''
)
SELECT '[' + SUBSTRING(String,Start,len(String + '..') - Start - Finish) + ']'
FROM T
CROSS APPLY
(
SELECT  PATINDEX(@Pattern,String),
        PATINDEX(@Pattern,REVERSE(String))
)ca(Start, Finish)

The [ and ] delimiters are applied just to make it easier to verify there is no white space lurking there. Remove these when you're happy this is the case.


The Cluster service failed to bring clustered service or application 'SQL Server (MSSQLSERVER)' completely online or offline

This means the cluster network name (tcsdb) can't be registered with Active Directory at the moment. It could be that your newly primary node can't see a domain controller, or can't see a writeable domain controller, may have a network interface down, may be having routing problems, etc.


Insert new row into table after performing checks in other tables

For checking if 4 or ciad is the valid value or not you can define ciad as the foreign key in your personal_transaction table referencing the primary key of the commercial_account table.
Please check the usage here:
https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

Try this:

INSERT INTO personal_transaction (id, type, date, fund, paid, caid)
SELECT 3, credit, `2016-12-28`, 30, 2, 4
where (SELECT credit FROM personal_account WHERE id = 2) >= 30;

I have assumed fund and paid are credit value and id fields respectively in personal_account table please adjust query according to actual fields names and try:

INSERT INTO personal_transaction (id, type, date, fund, paid, caid)
SELECT 3, credit, `2016-12-28`, 30, 2, 4
where (SELECT fund FROM personal_account WHERE paid = 2) >= 30;


Exporting from Oracle enterprise edition to standard edition reaches schemas limit

Apparently a full=y is allowed.
Converting An Enterprise Edition Database To Standard Edition (Doc ID 139642.1)

The only way to properly convert from an Enterprise Edition back to a
Standard Edition is through an Export/Import operation. This way you will
get rid of all Enterprise Edition specific Data Dictionary objects, as the
SYS schema objects are not exported. The Standard Edition EXP utility is
preferred to be used to export the data.

So apparently all the EE DD objects are not transfered.
Followed by dropping or excluding while exporting the EE schemas.


Uniquely Append to Array on Conflict PostgreSQL

The function array_append does not filter duplicates entries.
Here is a possible solution:

drop table auto_scope_pre_proc;
DROP TABLE
create table auto_scope_pre_proc
(
  hostname varchar(20) primary key,
  ip inet[]
);
CREATE TABLE
insert into auto_scope_pre_proc (hostname,ip) values ('test.com', '{1.1.1.1, 2.2.2.2}'::inet[])
on conflict (hostname) do update
set ip = array( select distinct unnest(auto_scope_pre_proc.ip || excluded.ip));
INSERT 0 1
select * from auto_scope_pre_proc;
 hostname |        ip
----------+-------------------
 test.com | {1.1.1.1,2.2.2.2}
(1 row)

insert into auto_scope_pre_proc (hostname,ip) values ('test.com', '{1.1.1.1, 3.3.3.3, 4.4.4.4}'::inet[])
on conflict (hostname) do update
set ip = array (select distinct unnest(auto_scope_pre_proc.ip || excluded.ip));
INSERT 0 1
select * from auto_scope_pre_proc;
 hostname |                ip
----------+-----------------------------------
 test.com | {4.4.4.4,2.2.2.2,3.3.3.3,1.1.1.1}
(1 row)


Why does WAL on postgresql takes so long to replay?

Docker shouldn't be crashing all the time.  Maybe whatever is making it do that is also making it very slow when it isn't crashed.  Look in the log files for the OS and for docker (and for PostgreSQL) to see what they say.

Find the pid of the startup process, and do:

strace -y -ttt -T -p <pid>

And see what it is doing.  This will show you, for example, if it is getting stalled on reads or writes or other system calls.  If it isn't, you can also use gdb -p <pid> to see what it is doing internally.  To get the most out of that, you will have to install the debug symbols, which I don't know how to do on the official docker image.


Incorrect result when using 'case when count()=0' & 'where'

just move where conditions to the join part:

select U.Mail, count(P.ID) as PostCount,
  case
    when count(P.ID) = 0 then 'none'
        when count(P.ID) <= 2 then 'few'
        when count(P.ID) <= 5 then 'medium'
    else 'many'
  end PostCountCategory
from Users U
    left join Tagging T on U.Mail = T.Mail
    left join Post P on T.IDPost = P.ID AND datediff(day,P.DatePosted,getdate()) <= 30
group by U.Mail, U.Gender

also your original condition doesn't alloow SQL Server to use index seek. It would be better to rewrite it this way:

P.DatePosted >= dateadd(day, -30, cast(getdate() as date))


How to keep row ordering with DISTINCT command or how to add row number to cross apply

Even with your first query the output order is in no way guaranteed. Unless a specific ordering is demanded via an ORDER BY clause the database is free to hand you the results in any order it see fit. For simple queries that can use an index it may look like the order is guaranteed because the output will be in the order of which ever index is used as the main seek/scan, but for anything more complex than a single table select there will be at least two ways to do it and the one the query planner chooses may change over time as the balance of data in your tables changes.

If you want a specific order, you must specify it in an ORDER BY clause.

In your this case ORDER BY FirstName, LastName, VariableName, Value seems to be what you want.

You don't mention caring if Value is in any particular order. If you don't care about that then go with ORDER BY FirstName, LastName, VariableName instead just in case the engine would need to do extra work to enforce the extra ordering detail. In you example data this would mean the rows FN1/LN1/V2/B and FN1/LN1/V2/C would come out in arbitrary order (i.e. sometimes B will be first, sometimes C will).


Yes, you can do what you're asking.  You should have your database in full recovery mode and then be performing log backups (via jobs/maintenance plans).

https://technet.microsoft.com/en-us/library/ms190982(v=sql.90).aspx


I don't think it makes any sense to define users and roles anywhere else than in admin database. If you define them somewhere else then likely your user/role management get unmanageable.

From db.createRole():

  Except for roles created in the admin database, a role can only
  include privileges that apply to its database and can only inherit
  from other roles in its database.

  A role created in the admin database can include privileges that apply
  to the admin database, other databases or to the cluster resource, and
  can inherit from roles in other databases as well as the admin
  database.

So, creating roles in "non-admin" database is much more limited. However, creating users and roles is usually an admin task and typically admins have full power anyway. Perhaps when you like to provide a MongoDB playground (e.g. https://mongoplayground.net/) then such limits could be useful.

Note, when you run a MongoDB Sharded Cluster then users and roles are commonly defined in the Config Server Replica Set. When you connect to a Shard directly then the shard contacts the Config Server in order to authenticate your connection. However, when your shard is disconnected from the cluster then it cannot contact the Config Server. For this you define a Shard Local User directly on the shards, see Create the shard-local user administrator. I think this is the only useful exception where users are not defined in common admin database.


ROOT CAUSE

The password option is not correct. When expressed like this

nohup mysql -u root -p kohadata < kohaaktarim.sql &

The word kohadata is being understood as the database

SUGGESTION #1

Either remove the space after -p

nohup mysql -u root -pkohadata < kohaaktarim.sql &

or change the option to --password=

nohup mysql -u root --password=kohadata < kohaaktarim.sql &

SUGGESTION #2

If the word kohadata is the database, you need to add the password

MYSQL_DB=kohadata
MYSQL_PASS=rootpassword
nohup mysql -u root -p${MYSQL_PASS} -D${MYSQL_DB} < kohaaktarim.sql &

GIVE IT A TRY !!!


Error creating linked database to oracle

In a database link, you should use the net_service_name, i.e. the OPT string, instead of SERVICE_NAME identifier.

EDIT:

Example:

cat tnsnames.ora|grep TEST
TESTNS=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=THE_HOST)(Port=THE_PORT)))(CONNECT_DATA=(SID=THE_SID)))

oracle@hostname> export ORACLE_SID=ANOTHER_SID
oracle@hostname> sqlplus username
Enter password:
Connected to:
<few lines of sqlplus banner>
SQL> create database link lll connect to <the_username> identified by <the_password> using 'testns';
Database link created.
SQL> select * from global_name@lll;

GLOBAL_NAME
--------------------------------------------------------------------------------
<the_global_name>

SQL>


Automatic database file creation based on database file size in SQL Server 2008 R2

First of all, in SQL Server, there is no automatically confiuration set up to automatically create or add new file to your existing file group when you reach the file size limitation. You have to do it on your own. But you can create script to do it automatically, even though it may not be exactly as you expected.

The logic to split data into different file by your own control is to create a new file group, add new file or add new file to existing file group. But, you have to create new table too.

For example, you have an existing table A. When you create it, you create it on FileGroup1, your FileGroup1 has two files, File1 and File2, you set the File1 and File2 to not autogrow, set them to fixed size 2GB. When you insert data into table A, you can NOT control insert data into which file, File1 or File2, which is controlled by SQL Engine, and because you set them to fixed size, so when both reaches to full, your insert query will throw exception. Certainly you can set them to auto growth. But this will not resolve your issue

What you can do is once table A is getting close to 4GB, create new table B first, after table B create successfully on new file group with new files, take a piece of idle time of table A, rename table A to table A archive and name table B as table A. In this way, the table change is transparent to your application layer.

How to know your table A file size is getting close to 4GB? You can use the following script to get current database file size information, including data file and log file. And you follow certain naming convention to create your FileGroup and Files, then you know what is your table's FileGroup and File name.

select Name,
(convert(float,size)) * (8192.0/1048576) File_Size,
(convert(float,fileproperty(name,'SpaceUsed'))) * (8192.0/1048576) MB_Used,
((convert(float,size)) * (8192.0/1048576) - (convert(float,fileproperty(name,'SpaceUsed'))) * (8192.0/1048576)) MB_Free
from sysfiles

You can check your online table file size periodically, certainly set your file to autogrow, in case your periodically check and table switch fail, you will not lose data.
Once you decide it is time to create new table B, you first add new FileGroup and new file to database.

USE master
GO
ALTER DATABASE TEST
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2012
ADD FILE
(
    NAME = test1dat3,
    FILENAME = 'E:\MSSQL\DATA\TESTdat1.mdf',
    SIZE = 4000MB,
    MAXSIZE = 4000MB,
    FILEGROWTH = 500MB
)
TO FILEGROUP Test1FG1;
GO

After new FileGroup and new file created, you can create new table B

select *
into table B
from table A
where 1<>1

Certainly, you need to add your indices and constraints. Better to save your table DDL somewhere and get it when you need to create new table.
Last step is to find a right time spot to do table switch. Make sure do it in one transcation.

EXEC sp_rename 'Table A', 'Table A archive'
Exec sp_rename 'Table B', 'Table A'

*The methodolgy above is just one approach, it is definitely not the best approach. *
Other approach:

Control the table and file split in your application layer, once you identify the data write to current table reaches some threshold, create new table, basing on timestamp or some naming convention your application knows.
In store procedure layer to control table and file split, normally your application will call store procedure to read and write data, you can use store procedure to create new table, and let the application read and write from new table.
There are more


Large transactional log won't shrink after transactional backup

What is the frequency of the log backups ?

Use dbcc sqlperf(logspace) to check how much % is free space in the logs.

There is something that is holding the logs up. Check the log_reuse_wait_desc for that database and take action accordingly.

Follow the below article :

https://www.brentozar.com/archive/2016/03/my-favorite-system-column-log_reuse_wait_desc/


Failure sending Excel attachments larger than 1.5MB

Database mail has a max file size limit and that might be preventing it. Please check and increase max size in your database mail and I guess it should resolve the issue


Update rows in a table with an index column starting from 0 with consecutive numbers

To update the values in the table, you can use a MERGE statement (alter is for changing the structure of a table, not for changing values. That is done with UPDATE or MERGE)

merge into anexe_h_rows
using (
  select id,
         row_number() over (partition by h_id order by nrcrt) as rn
  from anexe_h_rows
) t ON (t.id = anexe_h_rows.id)
when matched then
  update set nrcrt = rn, index_column = rn - 1;

This assumes that anexe_h_rows.id is the primary key of that table.

It also assumes that value for the column nrcrt is always one bigger than the one for index_column (which raises the question why you have both - because one can easily be derived from the other).


EAV - COUNT()/SUM() on column and row values with and without NULLs

Hi and welcome to the forum!
Your schema requires a totally radical overhaul. This image gives some idea of what's wrong with it:

You have used the EAV (Entity-Attribute-Value) database design anti-pattern - search for the writings of Joe Celko, Bill Karwin and others (and links therein) on this topic.
Also, and very important, you should store VARCHAR()s as VARCHAR()s and DATEs as DATEs - you will see this discussed when you read around EAV - data types go out the window and this means that the query optimiser can't generate an optimal plan. This applies to other types as well.
You can see this effect here:
CREATE TABLE date_test (x VARCHAR(10));

INSERT INTO date_test VALUES ('15-05-2021'), ('25-10-2000');

SELECT * FROM date_test ORDER BY x;

Result:
         x
15-05-2021     -- <<==== WRONG order!
25-10-2000

One of the reasons that correct data types are important! Even if you are obliged to store dates as strings (I realise that programmers are subject to constraints), you should use the ISO date format (i.e. YYYY-MM-DD).
TL;DR - the answer!
If you just want the answer, then it's:
SELECT
  dt.color AS "Color",
  COUNT(dt.c_date) AS "Collected",
  COUNT(*) AS "Total"
FROM demo_tab dt
GROUP BY dt.color
ORDER BY dt.color;

Result:
  Color Collected   Total
   PINK         3       3
    RED         2       2
 YELLOW         1       2

It also has the advantage of being the fastest query of those proposed - see the PROFILING section below!
Tackling the schema:
What you should do is (see fiddle here - adapted from @Akina's - I added a collected garment of yellow colour. I don't know what's going on with the id column and I've ignored it hereafter).
So, you create a VIEW - if you can't change the schema due to other constraints - I appreciate that DBAs/programmers don't always have (complete) control over their environments.

View:
CREATE VIEW demo_view AS
(
  SELECT
    x.user_id AS "uid",
    x.data AS "color",
    CAST(
          CONCAT
          (
            SUBSTRING(y.data, 7, 10),
            SUBSTRING(y.data, 3, 4),
            SUBSTRING(y.data, 1, 2)
          ) AS DATE
        ) AS c_date
  FROM demo x
  JOIN demo y
    ON x.user_id = y.user_id
    AND x.title = 'COLOR'
    AND y.title = 'DATE'
);

Table:
CREATE TABLE demo_tab AS
(
  SELECT * FROM demo_view

  --
  -- Could also construct it as above!
  --
);

Then your query on the table (could be on the view also - see fiddle) will be as simple as:
SELECT
  dt.color AS "Color",
  SUM(CASE
      WHEN dt.c_date IS NOT NULL THEN 1
      ELSE 0
    END
   ) AS "Collected",
COUNT(*) AS "Total"
FROM demo_tab dt
GROUP BY dt.color;

Result (identical for all queries):
 Color   Collected   Total
   RED           2       2
YELLOW           1       2
  PINK           3       3

Now, why, you may ask am I using the "complex" SUM(CASE... instead of the relatively "easy" SUM(t1.data IS NOT NULL)... of Akina's answer here. If you try and run his fiddle using PostgreSQL (see here), it fails. The SUM(... IS NOT NULL) construct is MySQL specific whereas the other works with PostgreSQL, SQLServer and MySQL! If in doubt - test on PostgreSQL - if it doesn't work there, there's usually something fishy going on!
If you want to (or are obliged to) persist with the schema as it currently is, then run this:
SELECT
  t2.data AS "Color",
SUM(CASE
      WHEN t1.data IS NOT NULL THEN 1
      ELSE 0
    END
   ) AS "Collected",
COUNT(*) Total,
CAST(SUM(CASE
      WHEN t1.data IS NOT NULL THEN 1
      ELSE 0
    END
   ) AS FLOAT) * 100 / COUNT(*) AS "% collected"
FROM demo t1
JOIN demo t2 ON t1.user_id = t2.user_id
WHERE t1.title = 'DATE'
  AND t2.title = 'COLOR'
GROUP BY t2.data
ORDER BY 1;

Result  - added a percentage collected field to it!
  Color Collected   Total   % collected
   PINK         3       3           100
    RED         2       2           100
 YELLOW         1       2            50

I did a performance analysis - see fiddle - using profiling - check the manual:
SET PROFILING = 1;

Run your queries
SHOW PROFILES;

Result (typical one shown - ran several times - may vary! Queries may be truncated):
Query_ID        Duration    Query
       1        0.00056325  SELECT
  dt.color AS "Color",
  COUNT(dt.c_date) AS "Collected",
  COUNT(*) AS "Total"
FROM demo_tab dt
GROUP BY dt.color
ORDER BY dt.color
Query_ID        Duration    Query
       2             0.00064050 SELECT
  dt.color AS "Color",
  SUM(CASE
      WHEN dt.c_date IS NOT NULL THEN 1
      ELSE 0
    END
   ) AS "Collected",
COUNT(*) AS "Total"
FROM demo_tab dt
GROUP BY dt.color
3             0.00074950    SELECT
  t2.data AS "Color",
SUM(CASE
      WHEN t1.data IS NOT NULL THEN 1
      ELSE 0
    END
   ) AS "Collected",
COUNT(*) Total,
CAST(SUM(CASE
      WHEN t1.data IS NOT NULL THEN 1
      ELSE 0
    END
   ) AS FLOAT) * 100 / COUNT(*) AS "% collected"
FROM demo t1
JOIN demo t2 ON t1.user_id =
4             0.00075000    SELECT
  v.color AS "Color",
  SUM(CASE
      WHEN v.c_date IS NOT NULL THEN 1
      ELSE 0
    END
   ) AS "Collected",
COUNT(*) AS "Total"
FROM demo_view v
GROUP BY v.color

SET PROFILING = 0;

The table-based query very frequently has the smallest duration (~ 90% of the time - we don't know what's going on with the rest of the dbfiddle server). The query plan for the table-based query is much shorter (EXPLAIN ANALYZE - see fiddle) than the others - good rule of thumb, the shorter the plan, the faster the query!
If you want to use standard SQL (and you should!), you can use this command (see bottom of fiddle) - you can issue the SET sql_mode='ANSI'; command - from the aforementioned Bill Karwin's answer here.
user_id title        data           w            x
      1  DATE  01-01-2021  2021-01-01   2021-01-01
      2  DATE        NULL         NULL        NULL
      3  DATE   04-01-2021  2021-01-04  2021-01-04
      4  DATE   04-01-2021  2021-01-04  2021-01-04
      5  DATE   02-01-2021  2021-01-02  2021-01-02
      6  DATE   08-01-2021  2021-01-08  2021-01-08
      7  DATE   07-07-2027  2027-07-07  2027-07-07

I leave it as an exercise for the reader to put what are now valid dates into the SQL above - it should work the exact same as the MySQL specific construct - you can see this in the PostgreSQL fiddle here.
I also leave it up to you to put in indexes on the necessary fields for large datasets - if you're having problems, get back to us here with a new question containing a link to the this one! EXPLAIN ANALYZE should give a few hints in this regard!


Finding "islands" of holiday periods for employees

This is my attempt at solving this "groups and islands" problem, with comments in-line:

WITH GroupStart AS
(
    -- Identify the start of each new group
    SELECT
        TD.id, TD.holidaydate, TD.holidaycomment,
            -- New group if the holidaycomment has changed
            -- (in holidaydate order)
            CASE WHEN holidaycomment =
                    LAG(TD.holidaycomment) OVER (
                        ORDER BY TD.holidaydate)
                THEN 0
                ELSE 1
            END AS gs
    FROM test_dates TD
),
Islands AS
(
    -- The island identifier is the number
    -- of new groups encountered so far
    SELECT
        id, holidaydate, holidaycomment,
        SUM(GS.gs) OVER (ORDER BY GS.holidaydate) AS grp
    FROM GroupStart GS
)
-- Results are now a pretty trivial aggregation
SELECT
    MIN(I.id) AS id -- Lowest id in group,
    MIN(I.holidaycomment) AS holidaycomment -- All the same per group,
    MIN(I.holidaydate) AS date_from -- First date in group,
    NULLIF(MAX(I.holidaydate), MIN(I.holidaydate)) AS date_to,
    1 + MAX(I.holidaydate) - MIN(I.holidaydate) AS days
FROM islands I
GROUP BY
    I.grp
ORDER BY
    I.grp,
    date_from;

SQLFiddle

I wrote this as my first Oracle query for about 20 years. The translation above is from my native SQL Server.


SQL Server 2008 - get current LOCK_ESCALATION on particular table

As far as I know, you'd have to look using SQL Server Profiler, the Lock:Escalation event

Esclation information isn't in sys.dm_tran_locks or it's predecessors


Yup, I have 2 clusters with TDE in both SQL 2008 R1 and SQL 2012 and nothing special was required. The master key is encrypted by the Windows DPAPl (I think that's the right acryonym) service, but then the generated key is in the Master DB that gets moved between nodes as you stated.


Finding optimal indices, different query competing indices

The query planner seems to think that created is more selective than token.  Are your table statistics up to date?  Check the last_analyze column in:

SELECT  schemaname, relname, last_analyze
FROM    pg_stat_all_tables
WHERE   relname = 'logs'

To really force the issue, you can use a temporary table:

begin;
create temporary table tmp_logs (created timestamp);
insert tmp_logs select created from logs where token = 'token_600_chars_long';
select count(*) from tmp_logs where created >= NOW() - INTERVAL '1 day';
commit;

The first query only uses a condition on token, so the optimizer can only use an index that starts with token.


Creating 7 digit codes in database for every new insertion

If it always needs to start with 51 then you can use a sequence (or identity) that starts at 510,000,001.

CREATE TABLE #Test (
    ID BIGINT IDENTITY(510000001, 1),
    SomeColumn VARCHAR(10)
);

INSERT  INTO #Test (SomeColumn)
VALUES ('Test'), ('Test2');

SELECT * FROM #Test AS t;

Just keep in mind that once the sequence reaches 519,999,999, it will not restart at 510,000,001 (particularly if you use the identity property) but will continue at 520,000,000.

Also, since you are on SQL Server 2014, please consider the fact that starting from the 2008 version IDENTITY values are cached and can have gaps in them after a restart. If you want to have the values follow exactly (i.e. no gaps) you should either use a SEQUENCE starting at 510000001 or apply Trace Flag 272.
This seems to be documented in this connect item


Connecting to SQL Server using cached credentials

Service accounts can't make use of cached credentials.  Only interactive users can use cached credentials.

As everything is running on the same server, create local Windows accounts and setup the services to run under those local Windows accounts.  Local accounts have no requirement that Active Directory actually be online.


I would create three tables instead:

Customer table:

Customer_id, name, address, etc.
PK: customer_id

Boxes table:

Box_id, size, description, etc.
PK: box_id

Orders table:

Customer_id, Box_id, Quantity, Timestamp.
PK: all four fields.
Foreign keys box_id and customer_id to the respective tables.

You will have one entry for each customer, each box type, and as many boxes of different sizes ordered as you wish.


compare 2 tables with minus with a SDO Geometry shape column

I found that the only way to compare with RACDB + SDO_GEOMETRY row is to compose the SDO like this:

select tm.id,tm.nume_sit,tm.numar,tm.x,tm.y,
        MDSYS.SDO_GEOMETRY(2001,NULL,
                           MDSYS.SDO_POINT_TYPE(tm.x,tm.y,NULL),
                           NULL, NULL) as shape
from (
select pub.id,pub.nume_sit,cpu.numar,cpu.x,cpu.y from
    SOL_PUBLIC.PUNCTE_PERIMETRU_SIT@RACDB cpu
    inner join SOL_PUBLIC.SOL_CHESTIONARE@RACDB PUB
        on cpu.FK_ID_CHESTIONAR=PUB.ID
minus
select prod.id,prod.nume_sit,cpr.numar,cpr.x,cpr.y from
    SOL.PUNCTE_PERIMETRU_SIT cpr
    inner join SOL.SOL_CHESTIONARE PROD
        on cpr.FK_ID_CHESTIONAR=PROD.ID
) tm
ORDER BY nume_sit,numar;

as for converting the SDO_GEOMETRY to text, if anyone need this, you have to do something like this:

to_char(substr(cpu.shape.get_wkt(),1,4000)) as shape


Return json list of values in SQL Server 2016 using data from 2 columns

If you are able to use FOR XML PATH('')

SELECT  DISTINCT
 QUOTENAME((STUFF(
    (
      SELECT ',' + try_convert(varchar(4),mt.B)
      FROM [dbo].[myTable] mt
      WHERE mt.a in ('X','C')
      FOR XML PATH('')
    ), 1, 1, '')));

If not, you could do a lot of REPLACE()ments

    SELECT  DISTINCT
 QUOTENAME(REPLACE(REPLACE(REPLACE(REPLACE((STUFF(
    (
      SELECT  mt.B as '}'
      FROM [dbo].[myTable] mt
      WHERE mt.a in ('X','C')
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ), 1, 1, '')),'"',''),'}',''),'{',''),':',''))

Result for both:

(No column name)
[1995,1962]


This should work:

SELECT      *
FROM        (SELECT         ROW_NUMBER() OVER (PARTITION BY p.Partnum
                                               ORDER BY     p.Partnum
                                                            , pd.DueDate
                                                            , pd.Quantity) AS rownumber
                            , p.Partnum
                            , pd.Quantity
                            , pd.DueDate
                FROM        Part       AS p
                INNER JOIN  PartDtl    AS pd
                    ON      p.Company  = pd.Company
                        AND p.PartNum  = pd.PartNum
            ) AS sub
WHERE       sub.rownumber <= 3
ORDER BY    sub.Partnum
            , sub.DueDate
            , sub.Quantity

You were using old SQL syntax, I converted the old join type to an INNER JOIN.
Also changed the numbers in the ORDER BY clause, as this might cause problems when you change the order of your selected columns.


Best way to solve a lot of queries stuck because of no index

found this table has 800,000 rows and does not have any indexes

Is there any safer and more sufficient way to solve this problem?

Only really re-emptive work:

Proper design work up-front so there are no large tables with no indexes, or not common queries that are not well-supported by the existing indexes. This might be out of your hands if you don't work directly with the development team for the application, but as a DBA you could monitor your databases for potentially worrying structures (i.e. a table with no keys/indexes, regular slow running queries if you log those, ...). Recheck after application updates, in case a structure migration has failed silently and left indexes undefined that the developers have added.

Load testing on the application to make sure no such issues are likely (you often can't rule them out entirely, but you can certainly minimise the risk). Again, this may be out of your hands.

but I think this approach is risky

A long index build, especially if done as an offline operation so could hold up all other access to the table, could be problematic, but when this happens adding the index is the only solution. Perhaps go for an online index build if possible - that will take longer but won't completely block part of the application while it does the job.
Also, if you have a dev copy of the system, test the new indexing changes there before applying to production to avoid making a change that will take ages to apply and have little of the benefit you are needing.


Managing and speeding up queries on PostgreSQL table with over 3 trillion rows

Your existing index on DATE is obviously useless for the query. The first obvious step for your query:

SELECT * FROM tbl WHERE column_a = 'value1' AND column_b = 'value2';

is an index for column_a or column_b (which ever is more selective) or possibly a multicolumn index on (column_a, column_b), like:

CREATE INDEX tbl_a_b_idx ON tbl(column_a, column_b);

Details:

Is a composite index also good for queries on the first field?

Next, I would consider partial indexes if you can safely exclude large parts of the table from the queries. Or partition your table in something like 100 partitions. And use constraint exclusion.

"Splitting it by year", like you contemplated, would be just as useless (harmful, even) as your index on DATE for the given query. Partitions need to be based on columns in the predicates of the query (column_a and column_b) to be helpful. Predicates on just one column would be much easier in this respect. If you have important queries filtering on various columns, partitioning is probably not the way to go. (Partial indexes still might.)

If partitioning is no good, a less invasive measure would be to CLUSTER data based on the new index (you can't use a partial index for this). Or simply create a new table from the sorted output of a query. This is particularly interesting since your table is mostly read-only. It should pay to do this at least once, but that one time will be very expensive: the whole table has to be re-written one or the other way. You need enough free space, as much RAM as you can get and an exclusive lock on the table. Or use pg_repack, to avoid the exclusive lock:

Configuring PostgreSQL for read performance
Best way to populate a new column in a large table?

Be sure to run the latest version of Postgres. The upcoming Postgres 9.5 might be particularly interesting for you, since it introduces BRIN indexes (block range indexes), which can reduce the index size dramatically for very big tables. Might be exactly what you are looking for.


How to export all the security-related information from a SQL Server database

I'm sorry you haven't had a response since yesterday; here's at least a starting point for you.

You can try pulling the pieces you need out of.  As always, read the discussion threads (I was, regrettably, unable to find a script endorsed by the big names I recognize, so test thoroughly!  Schema, object, server, and column level permissions are often missing).

I haven't actually used any of these, but they're a set of starting points, in no particular order.

List all permission in all databases

Automated Permissions Auditing With Powershell and T-SQL: Part 1

Permission scripting over all databases

Script DB Level Permissions v2.1

Stored procedure to script out user permissions

Security Queries: Database-level

Security Queries: Server-level


SQL Server 2012 Agent Job not providing full failure message for SSIS Packages

SSIS handles errors in two ways.

One is raising an error.
The other is failing a task/container/package.

The two are actually quite separate, and you can easily see one without the other.

To bubble an error up to the client (in this case, SQL Agent and its log) you would have to avoid catching the error somewhere. I suspect you might have an error handler there in some, or you have some of those convenient red arrows in there...

Of course, I see many times when people have errors that aren't causing anything to actually fail, so the package just keeps running. But that's another story...


How To Update Random ID to a table in mysql?

The simple solution is to materialize the result of the query that identifies the row to update, and fetch that result with a scalar subquery...

UPDATE table_name
   SET prize = 'Watch'
 WHERE id = (SELECT *
               FROM ( SELECT id
                        FROM table_name
                       WHERE City = 'Bandung'
                         AND (Prize != 'Watch' OR Prize IS NULL)
                       ORDER BY RAND()
                       LIMIT 1 ) dt1
             );

This selects a random id FROM the table from the desired set of rows, materializes that as a derived table with the alias `dt1` with one row and one column (the id of the new winner), and then selects that id using a subquery and uses it in the outer where clause to update the row.

There's no chance of 0 rows affected unless everybody already has a watch because we selected the "randomly first" id from among the list of potential winners.


MongoDB database schema designing for big data

Arthur!

I agree with you the most convenient data model here is to work on two collections, but I can see you have 1.5 collections.

Let me explain. Your comments collection stores almost all your data, except section, rank and title fields from news collection. You are embedding all the comments in an array in the comments collection, and this is inefficient due to the large amount of comments you have.

The schema I recommend you for the comments collection is simply to store one document per comment, referencing it to its news, so:

{
  "news_date": "20180126",
  "news_oid": "123",
  "news_aid": "4567890123",
  "commentCount": "7",
  "deletedCommentsCount": "0",
  "comment_id": "0",
  "id": "john_skeet",
  "time": "2018-01-26 07:32:45",
  "upvotes": "2",
  "downvotes": "0",
  "contents": "So true!"
}

In this way your required queries would be:

db.comments.find({ 'news_oid' : '123' })
db.comments.find({ 'news_oid' : '123', 'news_aid' : '4567890123' })
db.comments.find({ 'comment_id' : '0' })

The indexes you'd create would be:

db.comments.createIndex({ 'news_oid' : 1 }, { background : true })
db.comments.createIndex({ 'news_oid' : 1, 'news_aid' : 1 }, { background : true })
db.comments.createIndex({ 'comment_id' : 1 }, { background : true })

For searching I recommend you to use the full text search MongoDB 's capabilities.

Please, tell me anything you need!


Execute a query but not return results to client

You can use SET STATISTICS TIME ON to get the exact amount of time the server spends executing the query.

And Client Statistics can help determine the amount of time the client spends consuming it.

From Brent Ozar:
https://www.brentozar.com/archive/2012/12/sql-server-management-studio-include-client-statistics-button/

  Some really valuable information can be found here, such as the number
  of INSERT, DELETE, UPDATE, and SELECT statements – think of how useful
  that can be when tuning a complex stored procedure. You can see the
  number of rows affected or returned, and the number of transactions
  executed.

  The network statistics lets you see how much traffic is moving from
  your client to the server and back.

  The time statistics tells you how much time was spent processing on
  the client versus how much time was spent waiting for the server.
  These figures are in milliseconds.

I've often used it as a way to compare stored procedure performance executed with different parameters, especially useful when changing those parameters affects the volume of data processed and returned.

From SQL Server Management Studio

Once selected, every execution using the same session, will add a new column with performance results for that execution.

Some more info here as well: https://blog.sqlauthority.com/2009/10/01/sql-server-sql-server-management-studio-and-client-statistics/

Test using sysjobhistory

use msdb
go

set statistics time on

select * from dbo.sysjobhistory
go

From message tab:

(18 rows affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 3 ms.

From Client Statistics Tab

3 milliseconds to execute from SET STATISTICS TIME ON and 5 milliseconds Client processing time from the Client Statistics Tab.


Alternative to MS Acess for in-house user database

You can use the free SQL Server Express Edition, and use Access or many other tools as a front-end.  You can take nightly backups to network storage, or even store the database files directly on the network storage device.

Later the database can be migrated to a enterprise-managed SQL Server with no code changes.


Using MySQL Workbench, what is a good way to do development on an alternate server and copy changes to the main server?

What I traditionally do, if I can't sync between databases, is take a copy of the schema, add  dummy data, and develop on that locally. Then I save all my modification scripts and run them on the main server when I'm ready to push.

I would avoid moving data from development to production. Production -> Development may be fine if you don't have large amounts of data. Otherwise, use a dummy data generator if you can.

Ultimately, you'll have to develop your own methodology just like you do when you introduce version control or backups to a project. It is dependent on the situation.


database functional dependency candidate keys

If you're talking about identifying candidate keys for relational synthesis you need to know the dependencies.  If you have the functional dependencies you can use the relational synthesis algorithm; a synopsis of which can be found here.

Note, if this is a homework question please mark it as such in the tags.


I recommend you have a look at the procedure sp_dbpermissions by Kenneth Fisher.

I have been using it and it allows you to list all the permissions of a particular user in a database, and gives you all the scripts to copy all those permissions and apply them on a different database.

In case you want to apply them on a different server as well, that is fine too, but don't forget to create the server login first.


Actually there is a good microsoft article about it. I had the same problem and Microsofts solution fixed it.
https://support.microsoft.com/en-us/help/4047327/non-applicable-sql-server-cus-are-listed-in-wsus-mu-or-confmgr


Auto generated statistics by query optimiser

Can we consider removing the auto generated statistics if the update
  statistic within a given database is taking too long?

Dropping all auto created statistics

In theory you could drop all your auto generated statistics. Doing so will generate temporary stress on the system as they will be recreated when queries need statistics on objects.

as long as auto create statistics is still on in the database

If you are interested on more of that and a script to remove these statistics, go to this blog by Amy Levin.

Dropping duplicate statistics

Duplicate statistics could exist.

An example of this is due to an index being added after an auto created statistic was made on the column. There should be no issue in dropping these, but as always double check.

More on that, and a way to drop these duplicate statistics by ShaunJStuart here.

The script will only drop the auto created stat that is the same as the stat created by an index

  We do a weekly maintenance using UPDATE STATISTICS WITH RESAMPLE but
  often have to stop the script as it overruns into business hours.

Update statistics impact

Remember that while UPDATE STATISTICS uses a lot of CPU and IO depending on the size of the table+column it has a statistic on, it is not blocking.

In theory you could run it during production hours if the increase in resources used does not impact your queries.

Even though it is not blocking, one would prefer to do it outside of business hours (or has no choice due to hardware / workload limitations). This brings us to the last main point:

  When is it most suitable to use RESAMPLE and FULLSCAN?

FULLSCAN <> RESAMPLE

Resample definition from microsoft docs

  RESAMPLE

  Update each statistic using its most recent sample rate.

Meaning that if the previous update was FULLSCAN, the RESAMPLE will also be fullscan.

FULLSCAN as the name implies scans all the rows in the table / indexed view to
 update the statistic, where RESAMPLE will reuse the previous sample rate.

They are different but can sometimes execute the same UPDATE STATISTICS statements.

Take a look at the following three (simplified) scenario's.

An example of when updating stats with FULLSCAN does not work

When auto update statistics is triggered hourly or even daily due to modifications, the fullscan will be gone. Another consequence of this is plan regression at random times.

If you are updating the stats on a table with fullscan and an hour later they have already been 'auto updated', this did not have a long lasting effect.

When FULLSCAN could work

If you are able to keep the fullscan on the statistics for a specific table for close to or the entire week, fullscan could be an option for these tables.

When doing weekly fullscan stat updates on these tables.

Consequences of doing fullscan updates

When updating with fullscan, auto update statistics can become your enemy as degrading might happen due to different query plans at random times.

RESAMPLE

RESAMPLE is also only used in specific cases where you need to use a previously used stat update, in most real world scenario's you would not need this, unexpected things can happen when doing this.

To make your update statistics go faster you could look at doing it without FULLSCAN or with a SAMPLE RATE

What you could start out with

You could start with:

Dropping the duplicate auto created statistics
Updating the statistics either without FULLSCAN (Plain UPDATE STATISTICS and let SQL Server decide on the sample rate)

Or

Decide on a lower sample rate than FULLSCAN, that will still be
bigger than the sample rate decided on by SQL Server.

An example of the lower sample rate could be a sample rate of 10 PERCENT.

UPDATE STATISTICS ... WITH SAMPLE 10 PERCENT

This means that 10% of the rows in the table / indexed view are scanned to update the statistics.

You do have to watch out that you do not take too high of a sample rate, because there is a tipping point where even FULLSCAN is faster than a SAMPLE RATE.

More info on that here.

You should also look into which statistics are needed to be updated and which don't. Focus on these statistics in non-business hours.

I would not touch RESAMPLE unless done so for an explicit reason.

Extra considerations

Using Ola Hallengren's index and statistics maintenance could help to reduce the time this takes.

Set OnlyModifiedStatistics = 'Y' as to only update the modified statistics.

And change the sample rate with StatisticsSample = 10

@ErikDarling mentioned:

  Ola added StatisticsModificationLevel as another way to control which
  statistics get touched

An example of implementing this would be adding the parameter: @StatisticsModificationLevel = 5 to only update the statistics when 5 Percent of the rows have changed.

Remember that this is a dynamic threshold, meaning that it can update rows faster than 5% depending on the amount of modified rows. The formula for this is:

 SQRT(number of rows * 1000)

You can check how this dynamic threshold is used in a small part of ola's script:

OR (@StatisticsModificationLevel IS NOT NULL
AND @CurrentModificationCounter > 0
AND (@CurrentModificationCounter >= SQRT(@CurrentRowCount * 1000))

A small example to illustrate this (only based on indexes) could be by using this query:

SELECT   rowcnt as CurrentRowCount ,
         rowmodctr as CurrentModificationCounter,
         SQRT(rowcnt * 1000) as sqrtRowCount
FROM sys.sysindexes;

In the resultset returned below:

CurrentRowCount CurrentModificationCounter  sqrtRowCount
2342              262                       1530,3594349041
1118              490                       1057,3551910309
16000             15000                     4000

If it comes down to the dynamic threshold making the decision for these three indexes, only the statistics created by the third index would be updated.


How do I perform daily, monthly SQL Server 2005 maintenance?

Hardware Utilization and Performance (Memory, Available Disk Storage, and then CPU Utilization)

If your site already has SCOM deployed, use SCOM, as is capable of monitoring SQL Server. Otherwise, pick something off the shelve, eg. Bosun

  Database Maintenance (SQL Server Logs, File Growth, Mirroring Monitor , Back up completion, Index Fragmentation)

Use Ola's Index Maintenance and Backup scripts.