How to do Row Level Security in SSRS 2008 R2 (or 2012) with Active Directory Groups?

SSRS in the Report Model is available in sql 2008 R2, but not in express edition

check
http://msdn.microsoft.com/en-us/library/ms365305(SQL.105).aspx

and here is how to apply it on sql 2008 R2
http://msdn.microsoft.com/en-us/library/ms365343(SQL.105).aspx

and if you want to create a view and get TSQL and AD to talk

you can use the following functions to get username

select suser_name() as 'usrname'
or select suser_sname() as sname
or select system_user as system_

or if you want to get data from ADSI, you will need to create a linked server connection to ADSI and get users info
http://blog.namwarrizvi.com/?p=254
http://www.kodyaz.com/articles/active-directory-services-queries-using-openquery.aspx

and use the where clause

finally check this article "SSRS 2008 R2: Implementing Row-level Security"
http://www.sqlservercentral.com/blogs/sqldownsouth/2011/10/13/ssrs-2008-r2_3A00_-implementing-row_2D00_level-security/


Return the first row of a group in MySQL

The next query is not doing exactly what you ask for in your title, but it conceptually does what you're looking for.

The inner SELECT computes the distances for all geocodes (JOIN company_address). The outer SELECT GROUPs BY company_id, gets the "minimum distance", sorts through it; the LIMIT 50 is respected.

SELECT
    companys_id, min(distance) AS distance
FROM
(
    SELECT
         company_has_address.companys_id,
         (3959 * ACOS(COS(RADIANS(33.2003486)) * COS(RADIANS(geocodes_latitude)) * COS(RADIANS(geocodes_longitude) - RADIANS(-86.7852465)) + SIN(RADIANS(33.2003486)) * SIN(RADIANS(geocodes_latitude)))) AS distance
    FROM
         geocodes
         LEFT JOIN company_has_address
             ON company_has_address.address_id = geocodes.address_id
    WHERE
         company_has_address.companys_id != 5884
) AS s0
GROUP BY
     companys_id
ORDER BY
     distance ASC
LIMIT
     50 ;

This query might not be efficient because it will scan your whole geocodes (x company_has_address) tables, but it does the job.

I would change the LEFT JOIN to a JOIN (does it make sense to have a geocode (address) with no company?).

Check it at http://rextester.com/NCBT28574

Side note: I actually don't know of any simple way to accelerate your search if the table is big; but you could probably "prefilter" your search within a certain (lat+delta_lat, lng+delta_lng)-(lat-delta_lat, lng-delta_lng) "box", and have proper indexes on lat and lng; this may not be perfect, or "prefilter" too much, but may speed up a lot your queries if the number of points is big enough.

Addendum

If you want to retrieve (lat, lng), use:

SELECT
    companys_id, min(distance) AS distance, geocodes_latitude AS lat, geocodes_longitude AS lng
FROM
(
    SELECT
         company_has_address.companys_id,
         (3959 * ACOS(COS(RADIANS(33.2003486)) * COS(RADIANS(geocodes_latitude)) * COS(RADIANS(geocodes_longitude) - RADIANS(-86.7852465)) + SIN(RADIANS(33.2003486)) * SIN(RADIANS(geocodes_latitude)))) AS distance,
         geocodes_latitude, geocodes_longitude
    FROM
         geocodes
         LEFT JOIN company_has_address ON company_has_address.address_id = geocodes.address_id
    WHERE
         company_has_address.companys_id != 5884
) AS s0
GROUP BY companys_id
ORDER BY distance ASC
LIMIT 50 ;

This is non-standard SQL (you cannot mix aggregates with values not GROUPed BY, however, mySQL allows for it, and may give you the (lat, lng) corresponding to the min distance. Other ways of getting to the same place involve the use of CTE (not yet on mySQL, already on MariaDB) or TEMP tables.


Support to SQL Server 2000 from Visual Studio 2013(SQL Server 2012)?

Are these backup files from SQL Server 2000?  If so, you really should try to get these converted to SQL Server 2008/2012 formats.  A blog post from Ahmed Ibrahim walks through a set of scripts that you can run in SQL Server 2008 or 2012 that will allow you to restore these backups into 2008/2012 and convert them.  This will allow you to access easily from VS2012/2013.


There is no problem, just confusing notation.  It is saying that there are 835482 blocks in instance #6.

Here is a buffer_pool of 3G and 8 instances:

mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool%';
...
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_size             | 3145728000     |  -- 3GB

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
...
---BUFFER POOL 7
Buffer pool size        23999       -- 3G / 8 / 16K
Buffer pool size, bytes 393199616   -- 3G / 8

In your case, 835482 = 100G / 8 / 16K


Get current time, on commit, with oracle database

To get an approximate value (+/- 3sec or so) you may use ORA_ROWSCN

You must define the table with ROWDEPENDENCIES to have the SCN stored for each row.

Here an simple example:

create table t1
(c1 number,
c2 date)
ROWDEPENDENCIES;

insert into t1 values(1,sysdate);
-- wait 5 sec
commit;

insert into t1 values(2,sysdate);
-- wait 10 sec
commit;

SELECT ORA_ROWSCN, SCN_TO_TIMESTAMP(ORA_ROWSCN), c1,c2 FROM t1;

ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)         C1 C2
---------- ---------------------------- ---------- -------------------
   1.4E+13 19.05.16 19:14:15,000000000           1 19.05.2016 19:14:09
   1.4E+13 19.05.16 19:14:30,000000000           2 19.05.2016 19:14:21


Can you rollback a query in state 'committing alter table to storage engine'

I am sorry to have to inform you, but ALTER TABLE cannot be rolled back. In fact, ALTER TABLE triggers an implicit commit.

According to Page 418 Paragraph 3 of the Book

the following commands can and will trigger an implicit commit

ALTER TABLE
BEGIN
CREATE INDEX
DROP DATABASE
DROP INDEX
DROP TABLE
RENAME TABLE
TRUNCATE TABLE
LOCK TABLES
UNLOCK TABLES
SET AUTOCOMMIT = 1
START TRANSACTION

I have mentioned this before

Aug 21, 2015 : Transactional DDL workflow for MySQL
Feb 12, 2014 : row locking within ACID transaction innodb
Mar 15, 2013 : MySQL backup InnoDB

In your case, the ALTER TABLE will either finish or you will have a temp table left hanging around. In either case, it will not be a rollback in a transactional sense.


How can I solve postgresql problem after deleting wal files?

You have corrupted your database by manually deleting files from within the data directory. Never delete files from within the data directory manually.

Safely removing WAL

If you want to remove WAL, either let the server do it at CHECKPOINT time, or use pg_archivecleanup. Note that the server will remove WAL is no longer needs automatically, unless:

archive_mode is on, but archive_command is failing, so the server keeps on retrying the archive attempts until they succeed or the admin intervenes;
It's still preserved by wal_keep_segments
(in 9.4) It's still needed by a replication slot.

If none of those apply, a CHECKPOINT (either automatic, or manually issued via SQL) will remove all WAL that's not currently required. So you don't have to delete it by hand.

In unusual circumstances you might need to use pg_archivecleanup, like if you've run out of disk space due to WAL accumulation after archiving has failed for a long time. You might decide to accept that you'll have to re-create your replicas as a result of throwing away WAL they still need and use pg_archivecleanup to free space to get the master running.

But you should never delete WAL segments by hand.

Recovering with archived WAL

If you kept the archived WAL somewhere else, you might just be able to copy the files back into pg_xlog, or create a recovery.conf with a restore_command to do so. See the manual on PITR and log shipping for details.

Recovering without archived WAL

If you don't have a copy of those WAL files somewhere else, you should restore from a backup if you have a recent backup, because you have corrupted your database.

If you do not have a backup, follow the instructions in the corruption wiki page and only once you have made a complete copy of the current state of the database, as a last resort only, use pg_resetxlog to throw away the transaction logs and force the DB to start with incomplete transactions.

You must then pg_dump the database, stop it, initdb a new one, and restore to it. Do not keep using the database you damaged. Never keep using a database you used pg_resetxlog on, and never use it except as a last resort.


This really isn't an SQL Server issue - it seems more of a firewall issue.

If you are in a situation where your SQL Servers are directly accessible from dudes in china - I'd be sorting out why no one in your outfit is handling networking/firewalls etc

It sounds quite possible that you need to be worried about more servers than just the one you've noticed getting tickled!


Better understand table indexing best practices

Don't randomly add indexes.  Look at your queries to decide which indexes are needed.  See my cookbook.

InnoDB really needs a PRIMARY KEY.  Keep in mind that a PK is, an index, is UNIQUE, and is clustered.  So don't add any index(es) that start with the same column(s).

WHERE a=2 AND b=4 begs for a "composite" index:  INDEX(a,b) or INDEX(b,a).  Those are different than two separate indexes: INDEX(a), INDEX(b).

Don't use BIGINT unless you really expect to have huge numbers.

DESC is ignored on an INDEX declaration.  However, that does not stop the optimizer from running through an index in reverse order to have the effect of 'descending'.

FULLTEXT is useful only if you use MATCH(...) AGAINST(...).

"Too many" indexes slows down inserts some, and slows down UPDATEs that change indexed columns.

Don't index "flags"; such indexes will 'never' be used.


How to prevent parallel running of SQL Agent jobs

Aside from the comments, here's something you can use to accomplish what you're trying to do (since I'm not a fan of reinventing the wheel for locking systems).
You can use the system stored procedure msdb.dbo.sp_help_job to determine if your other jobs are running as the first step in each job. Then from there you can choose what logic you want it to take next when another job is running such as ending the job (if it's on a routine schedule) or implement a mock sleep using WAITFOR DELAY ('00:00:01') -- Sleeps for 1 second, which you can put inside a WHILE loop to recheck job status.
You can call the sp_help_job by passing in the job name:
EXEC msdb.dbo.sp_help_job @Job_name = 'TheJobName'

And you can use the columns current_execution_status and current_execution_step of the procedure's result set, to help you determine when it's safe to run the current job.
There's also some system job tables that might be helpful to you to query as well, which you can read up on in this StackOverflow answer.


Joining two fast subqueries is very slow

This pattern is very slow:

FROM ( SELECT ... )
JOIN ( SELECT ... )

especially when both subqueries have lots of rows.  This is because there are no indexes to speed up the "join".  MySQL 5.6 will create the optimal index (on the fly), but you appear to be using an older version.

Suggest upgrading.


I think this will solve the problem. It shows users that have associated less distinct files than data.

Since the files associated for a user are less than the data, at least one file must be associated with more than one data (for that user):

SELECT
    ud.user
FROM
    data_1 AS ud
      JOIN data_2 AS df
      ON ud.data = df.data
GROUP BY
    ud.user
HAVING
    COUNT(DISTINCT df.file) < COUNT(*) ;

The query assumes you have UNIQUE constraints on data_1 (user, data) and on data_2 (data, file).


LATCH_EX Waits on Resource METADATA_SEQUENCE_GENERATOR

(Updated February 2019)

This is an old post, that said I've finally managed to convince Microsoft that the very fact this happens is indeed a defect.

Update: MS Confirmed the defect and assigned it a bug # of 12628722.

I had seen this post this past November 2018 when we to began to suffer much the same after we'd upgraded from Sql Server 2005 to Sql Server 2017. A 3.3 million row table that used to take 10 seconds to bulk insert suddenly started taking 10 minutes on tables with Identity columns.

Turns out there are two issues behind this:

Microsoft changed the behavior in Sql Server 2014 to force Bulk Inserts to run parallelized - in prior versions Bulk Inserts were given a Serialized plan.
Once running in parallel on our 32 core box, the engine spent more time with the cores locking each other than actually doing the work.

Took me 4 weeks but just after the holidays I got a belated present from Santa - confirmation that the issue was indeed a defect.

There are a few possible workarounds that we found until this is fixed:

Use Option (MaxDop 1) in  the query to turn the bulk insert back into a serialized plan.
Mask the Identity column by casting it (e.g. Select Cast(MyIdentityColumn As Integer) As MyIdentityColumn)

this prevents the identity property from being copied when using SELECT...INTO

Remove the identity column as described above.
Change the database compatibility mode to Sql Server 2012 or lower to re-establish a serialized plan.

Update:
The fix MS will be implementing will be to return these sorts of Inserts back to using a Serialized plan. This is planned for Sql Server 2017 CU14 (no news on other versions of Sql Server - sorry!).
When implemented, Trace Flag 9492 will need to be turned on, either at the server level, or via DBCC TraceOn.


Listing IDs that match values in two columns of a table

SELECT DISTINCT abbreviations.*
FROM attachments
JOIN abbreviations ON abbreviations.Abbreviation IN (attachments.`To`, attachments.`From`)
WHERE attachments.RowID IN ('2019.06.05',
                            '2019.06.11',
                            '2019.06.12',
                            '2019.06.15',
                            '2019.06.22');

?


Azure Backup SQL VM - Not shrinking log file after transaction log backup

I think there may be some confusion here on the word "Shrinking" - The log file is not ever "Shrunk" by a backup. It can be "truncated" by the log backup, but not shrunk.

When a log is truncated, the segments in the log that are no longer needed for recovery (either because you are in SIMPLE recovery model and a checkpoint has ensured that the data is in the data file securely, or you are in FULL recovery model and the log records have been backed up with a transaction log backup) are effectively marked for overwrite. The log is allowed to use the space those "old/unneeded" log records consumed because they aren't required for recovery.

Note I didn't say "Shrink". The truncation means you can possibly continue writing to the space already allocated to the log file - if there is enough. But truncation will NEVER shrink a log file. It just means it may not have to grow more.

Truncation is more or less an internal log file maintenance type operation. I love what they used to call SIMPLE recovery model back in SQL Server 2000 - "Truncate on checkpoint" mode.

Shrinking is a physical operation on the file itself. I have a few opinions on shrinking data and log files in a rather long answer here. But, in general, don't fear a bigger file. Fear an out of control file that is growing unconstrained. If you are truly taking log file backups, and SQL is showing that you have - you likely just have a file with mostly empty space - meaning the file can be rewritten, and will. It's rather healthy to have a file that is "right-sized" to not have to shrink/grow and shrink/grow between log backups.


Using if statement outside of a stored procedure or function

Use the IF() function

mysql> set @the_message = 'Rolando Bank of America';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT IF(@the_message LIKE '%Bank%','h','nothing here to see') result;
+--------+
| result |
+--------+
| h      |
+--------+
1 row in set (0.00 sec)

mysql> set @the_message = 'Rolando Bk of America';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT IF(@the_message LIKE '%Bank%','h','nothing here to see') result;
+---------------------+
| result              |
+---------------------+
| nothing here to see |
+---------------------+
1 row in set (0.00 sec)

mysql>

Give it a Try !!!


How To Remove Spaces Between Characters In PostgreSQL?

simplest way - use REPLACE function

select REPLACE('NES 123_4_5', ' ', '')
Result - NES123_4_5

full list of string functions - https://www.postgresql.org/docs/9.6/static/functions-string.html


Using (NOLOCK) in a READ COMMITTED transaction block

The hint overrides the isolation level of the connection so you would indeed be allowing a dirty read from the clients table.


You can install it via Homebrew

brew install mysql

This will install the mysql deamon and the cli tool.


MySQL open_files_limit - cannot change this variable

I needed to edit /usr/lib/systemd/system/mysqld.service and add

LimitNOFILE=infinity
LimitMEMLOCK=infinity

Then run systemctl daemon-reload and systemctl restart  mysql.service.

Now the variable is capped at 65536 for some reason unknown, but I can live with that for now.

Found this at https://unix.stackexchange.com/questions/152186/mysql-max-open-files-more-than-1024#answer-157910


Is there any special configuration for SQL Server implementing Routing List in a heterogeneous enviroment (Client and Server in different domains)?

I dig a little bit and found that adding the FQDN in the Reporting Services node the functionality works just fine!
I also request to Security to check those entries to make sure works fine.

Thanks Sean Gallardy your clue was key for success. Cheers.


Ignoring Null (optional) Variables in Stored Procedure

If you set the value of a given parameter to it's default value (when it has one), you'd get the same functional equivalent as omitting it.  Therefore, I'd suggest doing something like the following:

EXEC dowork
    @param1 = ISNULL(@param1, SELECT default_value FROM sys.parameters p WHERE object_Id = OBJECT_ID('dowork') AND name = '@param1' AND has_default_value = 1),
    @param2 = ISNULL(@param2, SELECT default_value FROM sys.parameters p WHERE object_Id = OBJECT_ID('dowork') AND name = '@param2' AND has_default_value = 1),
    ...@param30 = ISNULL(@param30, SELECT default_value FROM sys.parameters p WHERE object_Id = OBJECT_ID('dowork') AND name = '@param30' AND has_default_value = 1)

The only flaw being values that do not have a default value, which will result in passing a NULL vs causing the error that would occur when omitted.  If that is a concern in your particular case, I would do some checking before hand and throw an error.

The only other alternative I could suggest would be dynamic SQL.


There is no way to do that in PostgreSQL; you have to look at all ACLs on all objects.
Perhaps an extension like pg_permissions can be useful.
One other crude method I can think of:
BEGIN;
DROP ROLE dev_role;
ROLLBACK;

The DROP ROLE will give you a list of permissions it can find that are granted to the role.


How can I make a select statement get blocked?

Deadlocking by SELECTs can be done in a variety of ways. I have written posts about them

You can have SELECTs get deadlocked by UPDATEs and DELETEs

Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?

You can have UPDATEs and DELETEs blocked by SELECTs

How are DB locks tied to connections and sessions?
Is Oracle DB immune to the InnoDB deadlocks found in MySQL?

You can just lock the table before the SELECT and unlock it afterwards

LOCK TABLES table READ;
SELECT ... ;
UNLOCK TABLES;

You could perform a large UPDATE in a transaction with the SERIALIZEABLE isoaltion level. The MySQL Documentation on SERIALIZEABLE isoaltion level says:

  This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)

Cutting right to the chase, you could just use

SELECT ... FOR UPDATE, then try SELECT against those rows
SELECT ... LOCK IN SHARE MODE, then try to UPDATE or DELETE those rows
See the  MySQL Documentation

Give it a Try !!!


What are user connections - when are the created and destroyed?

Please notice the makeup of mysql.user:

mysql> show create table mysql.user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `plugin` char(64) COLLATE utf8_bin DEFAULT '',
  `authentication_string` text COLLATE utf8_bin,
  PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.00 sec)

Notice the column max_user_connections. It puts a moratorium on the number of connections a user may make per hour. Check and make sure the user '557574_prod' isn't connecting to mysql a ton of times within less than an hour.

This is used in conjunction with the option max_user_connections that you can set in /etc/my.cnf

Please change either max_user_connections in /etc/my.cnf or, if the individual user has this column set, that column mysql.user.max_user_connections. If you have no config control over my.cnf and you cannot do SQL against the mysql schema, then you beg and implore your hosting company to raise those limits for you.

I believe you can set the max_user_connections as a session variable, but you may need certain privileges, probably SUPER.

As for establish connections, you need to keep those connections open. Perhaps sending a heartbeat of some kind by doing a SELECT. But guess what ??? The db columns max_questions monitor SELECT queries per hour and max_updates monitors INSERTs, UPDATEs, DELETEs per hour. YOu may need to check these columns in mysql.user. You can quickly check those number with:

SHOW GRANTS;

I am not sure if the the user's per-hour connection limits are per thread or per user. You could answer that quickly by connecting to the DB aggressively using 2 or 3 DB connecitons at the same time and the error message should manifest itself. It is up you how to count the number of connections, perhaps "SHOW STATUS LIKE 'Connections';" It should reach either the max limits of the connection within the one connections or it could be the sum of all connections' connection status value.

Please also check the wait_timeout and interactive_timeout values. The default is 28800 in a standalone server. They may be set already. You cannot set them within a current connection. You must be able to set them as follows: (for new conenctions coming)

SET GLOBAL interactive_timeout = 86400;
SET GLOBAL wait_timeout = 86400;

If you cannot run even these commands, my condolences !!! :(


drastically different runtimes in BIDS and the SSRS Web Portal

If your query is returning a lot of rows across a slow WAN link then you might be seeing a slowdown due to network traffic.  I have that class of problem where I'm working now.

If you report server is on the same fast LAN as the database server it would be quicker under these circumstances.  This might explain the difference in performance.

Also, if you have virus scanning software running on your PC it could interfere with the local processing in a variety of ways - scanning the .data file etc.  Often real-time scanning also hooks into memory allocation and scans data segments of applications.  This really slows down managed applications.

Those are a couple of possibilities that come to mind.  There may be other causes as well.


Large MySQL import stuck at "Waiting for table flush"

I have always found large mysqldumps to be problematic. The best tactic I know is to reduce the size of the import. aka split the database dump into many dumps that let you run a per table restore this will let you make tangible progress and reduce the size and time each import takes.
https://github.com/kedarvj/mysqldumpsplitter


Can we restore backup for standby from external hard drive with differnet drive labels

Yes, you can. The backup entries in the controlfile point to 'E:...', so after restoring the controlfile, you will have to catalog the backup pieces in RMAN by:

catalog start with 'G:\';

After this you can restore the database.


If you have an index on LastLoggedIn, deleting a few tens of thousands of records should be fast without needing to partition.  I ran a small test on MS SQL:

CREATE TABLE Temp.Temp
(
UserID  INT NOT NULL PRIMARY KEY,
LastLoggedIn    DATETIME2(0) NOT NULL INDEX,
Dummy1  VARCHAR(30),
Dummy2  INT
)

I insert ~57 M dummy records, then selected a LastLoggedIn date such that 35,000 records were at least that old.  Deleting these records took less than a second.  This is on a four-core machine with 128 GB of memory, under light load; your mileage may vary.

Of course, if you run this purge only every few weeks, you may have millions of records to delete rather than thousands (300 K records took seven seconds for me; 1.3 M records took 30 seconds).  But in that scenario, it's part of your monthly maintenance window rather than daily upkeep, and a brief delay should be acceptable.

If you get poor performance with just an index, then sure, consider partitioning.  As others have pointed out, there will be a significant cost to UPDATE operations, a greater overhead than maintaining an index.  You will have many more updates than purges, and the latter can happen during relatively idle periods, so I would worry about the updates more.

If you need always-on uptime, and the delete locks the table for an unacceptable length of time, that might justify using partitioning, but even there I'd look for alternatives.  You could SELECT a list of old users and then delete them one at a time (confirming their LastLoggedIn dates first of course) with a cursor, for example.

I'm an MS SQL guy, so my apologies if any of this is incorrect for PostgreSQL.


Translate SQL Server update statement to MySQL

MySQL requires a distinct approach: An INSERT with ON DUPLICATE KEY UPDATE.
Example above works like this:

INSERT INTO TBEstabelecimento (
    DSEndGeoSys, DSPrecisaoGeoSys, NRCGCCEICPF, NRLatGeoSys, NRLngGeoSys, TPInscricao
)
VALUES  (   'Icém - SP, 15460-000, República Federativa do Brasil',
            'APROXIMADA', '06291846001771', -20.4053212, -49.1625989, '1'
) ON DUPLICATE KEY UPDATE
DSEndGeoSys = VALUES(DSEndGeoSys),
DSPrecisaoGeoSys = VALUES(DSPrecisaoGeoSys),
NRCGCCEICPF = VALUES(NRCGCCEICPF),
NRLatGeoSys = VALUES(NRLatGeoSys),
NRLngGeoSys = VALUES(NRLngGeoSys),
TPInscricao = VALUES(TPInscricao)

Of course, the big benefit of such approach is when you have several VALUES like (csv1), (csv2), ... so you can do the whole update with only one command


What happens when Core Cluster Group is failed over to another node? Will it take the whole cluster down? Single point of failure?

When you failover a cluster resource group from a node to different node SQL Server instance and other resources which are part of this resource group is stopped on current node and then ownership is changed to node which you are failing over to. After ownership is changed SQL Server is restarted with owner as new node. During failover SQL Server might go in crash recovery mode so any Inflight transactions( transactions which were running) will be rolled back. SQL Server goes through process of recovery to rollback and rollforward transactions so as to bring database in consistent state as it was just at time when failover happened.

Cluster disk, netwrok name and MSDTC are also part of resource group so they also move to different node.


Compare the database compatibility_level to the server version?

I found a couple of options that I think will work:

First, you could multiply the first two digits of SERVERPROPERTY('productversion') by 10:

SELECT name, compatibility_level
FROM master.sys.databases
WHERE compatibility_level <>
    10 * convert(int,CONVERT(char(2),SERVERPROPERTY('productversion')))

Or, probably easier, you could just compare to the highest compatibility_level on the server:

SELECT name, compatibility_level
FROM master.sys.databases
WHERE compatibility_level < (SELECT max(compatibility_level) FROM sys.databases)

FYI, you can't just compare to the compatibility_level of MASTER or MODEL, as those can be left lower after an upgrade, or deliberately changed. TempDB, however, I think is always the current version.


PG_bulkload utility postgresql need help

Your simple solution is to import into a separate table and then use another query to copy the data back, converting to proper format.


T-SQL CTE Compare previous non-NULL and current row to filter out by difference value

Here is a recursive CTE solution using a technique that Paul White blogged about in Performance Tuning the Whole Query Plan.

declare @T table
(
  Eventdate date index IX_Eventdate clustered,
  Val int
);

insert into @T(Eventdate, Val) values
('2012-03-23',  3965),
('2012-03-26',  3979),
('2012-03-27',  3974),
('2012-03-28',  3965),
('2012-03-29',  3967),
('2012-03-30',  3959),
('2012-04-02',  3951),
('2012-04-03',  3961),
('2012-04-04',  3944),
('2012-04-05',  3935),
('2012-04-09',  3901),
('2012-04-10',  3822);

with C as
(
  select top(1)
         T.Eventdate,
         T.Val,
         T.Val as PrevVal1,
         T.Val as PrevVal2
  from @T as T
  order by T.eventdate
  union all
  select T.Eventdate,
         T.Val,
         T.PrevVal1,
         T.PrevVal2
  from (
       select T.Eventdate,
              T.Val,
              iif(abs(T.Val - C.PrevVal1) <= 12, C.PrevVal1, T.Val) as PrevVal1,
              iif(abs(T.Val - C.PrevVal1) <= 12, null, T.Val) as PrevVal2,
              row_number() over(order by T.Eventdate) as rn
       from @T as T
       inner join C
         on C.Eventdate < T.Eventdate
       ) as T
  where T.rn = 1
)
select C.Eventdate,
       C.PrevVal2 as Val
from C
order by C.Eventdate
option (maxrecursion 0);


Server response time high when InnoDB tables active but not being accessed

[SOLVED]

I enabled query_cache_type=1 and query_cache_size=256M.

The problem went away and the server response reduced from 6 seconds to 1 second. Very strange considering most recommend that Query cache should be disabled!


Monitor Oracle query plans for PARTITION LIST (ALL)

I would write a job procedure which would get all running queries:

SELECT sql_id, sql_child_number FROM V$SESSION WHERE SQL_ID IS NOT NULL AND TYPE='USER';

and then loop through that cursor and get explain plan

select * from table(dbms_xplan.display_cursor(v_sql_id, v_sql_child_number));


Is this considered a "light load" trace to run on SQL Server Profiler

Using Profiler via the UI can cause serious loads on Production systems. What you should bear in mind is that you must always consider using a Server-Side Trace instead of using Profiler's User Interface.

You might think of it as of almost the same. There is very nice article by Gail Shaw that you should definitely read and understand.


This will work for the special case:

SELECT *
FROM your_table
WHERE sort < (SELECT MIN(t.sort)
              FROM your_table AS t
              LEFT JOIN your_table AS t2
                  ON t.sort = t2.sort+1
              WHERE t2.sort IS NULL
                AND t.sort > (SELECT MIN(sort) FROM your_table))


You are just looking at an old page. It even says that it is only being maintained for reference purposes only:

  This page is being retained for reference purposes only. The information on this page, including statements about upgrade and/or substitution options, is not being updated and may no longer be accurate.

For the certification track you wish to take use the link below instead:

MCSA: SQL 2016 Database Administration

In brief, you just need to pass your 70-764 and 70-765 to earn the MCSA: SQL 2016 Database Administration Certificate. No more, no less.


Use mysqli or PDO libraries for PHP, do not use mysql, as it is deprecated
Use prepared statements: they are cleaner, less prone to SQL injection and, in some cases, faster to execute
Use exceptions to capture errors throughout your code -query fails, query gets killed, database crashes, unable to connect, ...
If you feel intimidated by SQL, an ORM can help you build your queries, but they are not without issues
Students' id should have a foreign key referencing user_id, assuming you want a 1:1 relationship. To use foreign keys on MySQL you will want to use InnoDB stored engine.
You cannot insert to two tables with a single statement, so you should use transactions. Start a transaction before inserting to the first table, then insert to the second one, then COMMIT the changes. That way, either the two inserts succeed or both fail. Transactions also require InnoDB.
student_id/user_id are probably going to be unique arbitrary keys. You can use the auto_increment property for that column and do:

START TRANSACTION;
INSERT INTO student (last_name, fist_name, ...) VALUES ('Crespo', 'Jaime', ...);
INSERT INTO users VALUES (last_insert_id(), 'jynus', 'hashedpassword', 'student');
COMMIT;


How to upgrade MYSQL from 5.6.12 to 5.6.21?

First take a backup of everything using your normal backup software.

Take a dump (mysqldump using the --all-databases) option.

Export:

mysqldump -uroot -p --all-databases > alldb.sql
mysqldump -uroot -p --opt --all-databases > alldb.sql
mysqldump -uroot -p --all-databases --skip-lock-tables > alldb.sql

I cordially invite you to look at the docco for the options that suit
you best - or just run mysqldump with the --help option only.

Uninstall your MySQL 5.6.12. (I'm assuming yum or apt - redhat or Ubuntu - again
check your docco)

Install your MySQL 5.6.21.

Check the release notes to ensure that there are no parameters
that have changed - given that your migrating between minor
versions, this should not be an issue.

Perform a restore using mysql and the dump.

Import:

mysql -u root -p < alldb.sql

Et voilà!


Error after removing sql server 2008 R2 Express edition and instaling Entreprise edition: Unable to connect to MyHost\MSSQLSERVER

For a default instance (MSSQLSERVER), you only specify the computer name when connecting; the instance name (which is actually a ficticious placeholder) is implied.

Also, if you're connecting from another machine now, you may need to configure the Windows Firewall to allow connections through. The error message you get will be the same as the one you're seeing now. See my video here for more on that.


Daily restore from sql server transaction log

Is it possible to just restore the daily transaction log?

As you already pointed out, restoring just the transaction log is NOT possible you have to either restore full backup or full and differential backup to restore transaction log backups.

  Does it mean on everyday in order to restore the daily transaction log we have to first restore the full dump and leave the db in NORECOVERY mode and restore all the transaction logs before current day and leave the db in NORECOVERY mode?

Yes. Are you aware about transaction log shipping this might suit your requirement.


Are there problems with using SQL Server AlwaysOn Availability Groups with Windows 2008R2?

We have multiple customers using SQL Server 2012 with availability groups on Windows Server 2008 R2. The fixes listed in the pre-requisites for implementing availability groups needs to be applied for Windows to ensure that you don't run into known issues.


Is there a way to get the isolation level from sp_whoisactive out of the xml into the plain result?

Using the built-in functionality of sp_whoisactive to return the schema of the result set, you can store that information in a temp table and then use CROSS APPLY to extract out one or more xml nodes from the additional information

--Drop temp table
IF OBJECT_ID('tempdb.dbo.sp_whois_active') IS NOT NULL
    DROP TABLE tempdb.dbo.sp_whois_active

--Use 'return_schema' to create a temp table to hold the results of sp_whoisactive
DECLARE @s VARCHAR(MAX)

EXEC sp_WhoIsActive @output_column_list = '[%]'
    ,@return_schema = 1
    ,@schema = @s OUTPUT
    ,@get_additional_info = 1

SET @s = REPLACE(@s, '<table_name>', 'tempdb.dbo.sp_whois_active')

EXEC (@s)

--Populate the temp table by executing sp_whoisactive
EXEC sp_WhoIsActive @output_column_list = '[%]'
    ,@destination_table = 'tempdb.dbo.sp_whois_active'
    ,@get_additional_info = 1

--Select all of the columns from the temp table and
--use CROSS APPLY to extract one or more xml nodes
SELECT who.*
    ,N.C.value('transaction_isolation_level[1]', 'varchar(100)') IsolationLevel
FROM tempdb.dbo.sp_whois_active who
CROSS APPLY additional_info.nodes('//additional_info') N(C)


Many-To-Many relationship between Students and heights (Is it necessary?)

Height is not an entity. A student can't have multiple heights, unless your measurements are sometimes incorrect. Unless there's been some time between measurements. Measurements could be an entity, identified by time_taken and student_id, attributes height, weight, and things perhaps too private


You can use a CASE expression:
order by case "type"
            when 'article_invitation' then 1
            when 'new_article' then 2
            when 'article_read' then 3
            when 'friend_request' then 4
         end,
         updated_at desc


What is the correct way to ensure unique entries in a temporal database design?

The safest way to do this is to enforce your business rules using the built-in referential integrity constraints, as Alexander Kuznetsov describes in his article, "Storing intervals of time with no overlaps".

Applying the techniques listed there to your sample table results in the following script:

CREATE TABLE [Z_STORE_TEAM](
    [STORENUM] [int] NOT NULL,
    [TEAM] [varchar](10) NULL,
    [EFFECTIVE] [date] NOT NULL,
    [FINISHED] [date] NULL,
    PRIMARY KEY CLUSTERED
    (
        [STORENUM] ASC,
        [EFFECTIVE] ASC
    )
) ON [PRIMARY];

INSERT [Z_STORE_TEAM]
    ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED])
VALUES
    (1, N'1', CAST(0x01380B00 AS Date), CAST(0x81380B00 AS Date)),
    (1, N'2', CAST(0x81380B00 AS Date), NULL),
    (2, N'1', CAST(0x01380B00 AS Date), NULL);

Modifications:

-- New column to hold the previous finish date
ALTER TABLE dbo.Z_STORE_TEAM
ADD PreviousFinished date NULL;
GO
-- Populate the previous finish date
UPDATE This
SET PreviousFinished = Previous.FINISHED
FROM dbo.Z_STORE_TEAM AS This
CROSS APPLY
(
    SELECT TOP (1)
        Previous.FINISHED
    FROM dbo.Z_STORE_TEAM AS Previous
    WHERE
        Previous.STORENUM = This.STORENUM
        AND Previous.FINISHED <= This.EFFECTIVE
    ORDER BY
        Previous.FINISHED DESC
) AS Previous;
GO
ALTER TABLE dbo.Z_STORE_TEAM
ADD CONSTRAINT UQ_STORENUM_PreviousFinished
UNIQUE (STORENUM, PreviousFinished);
GO
ALTER TABLE dbo.Z_STORE_TEAM
ADD CONSTRAINT CK_PreviousFinished_NotAfter_Effective
CHECK (PreviousFinished = EFFECTIVE);
GO
ALTER TABLE dbo.Z_STORE_TEAM
ADD CONSTRAINT UQ_STORENUM_FINISHED
UNIQUE (STORENUM, FINISHED);
GO
ALTER TABLE dbo.Z_STORE_TEAM
ADD CONSTRAINT FK_STORENUM_PreviousFinished
FOREIGN KEY (STORENUM, PreviousFinished)
REFERENCES dbo.Z_STORE_TEAM (STORENUM, FINISHED);
GO
ALTER TABLE dbo.Z_STORE_TEAM
ADD CONSTRAINT CK_EFFECTIVE_Before_FINISHED
CHECK (EFFECTIVE < FINISHED);

An attempt to insert the fourth row of sample data now fails with an error message:

INSERT [Z_STORE_TEAM]
    ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED])
VALUES
    (2, N'2', '20140201', NULL);

Please read Alex's article to understand how these constraints ensure your table data will always be valid. Having a set of constraints enforce your data integrity means no trigger code is required.

Related article by the same author:

Modifying Contiguous Time Periods in a History Table


How to join by most recent preceding date?

select *
from dbo.Sales s
  cross apply (
  select top (1) *
  from dbo.PriceHistory ph where ph.itemId = s.ItemId and ph.Date <= s.Date
  order by ph.Date desc
) ca;


Limiting query to certain amount of rows evenly distributed

You have a dynamic number of rows in the table, and want a specific number of evenly distributed results each time?

I'm asking because the solution you linked is already nearly there:

   SELECT *
FROM (
    SELECT
        @row := @row +1 AS rownum, [column name]
    FROM (
        SELECT @row :=0) r, [table name]
    ) ranked
WHERE rownum % [n] = 1

Just replace [n] with the following:

(CEIL(@row/[m]))

where [m] is the number of results you want to have returned.


In replication A->B->C, can A be binlog_format=STATEMENT and B be binlog_format=ROW?

STATEMENT → ROW is absolutely a valid configuration.

The thing you've probably overlooked is that Server B needs log_slave_updates enabled, or it won't replicate anything from A to C.

But to address the question at hand, consider the following table, which describes the comparison between configured @@binlog_format and what is actually written to the server's binlog.  Assume A is master, B is slave of A,  no changes are made directly to B, and of course log_slave_updates is enabled on B:

A binlog_format | A actually writes | B binlog_format | B actually writes
-------------------------------------------------------------------------
STATEMENT         STATEMENT           STATEMENT         STATEMENT
STATEMENT         STATEMENT           MIXED             MIXED
STATEMENT         STATEMENT           ROW               ROW

MIXED             MIXED               STATEMENT         TOTALLY FAILS EVENTUALLY
MIXED             MIXED               MIXED             MIXED
MIXED             MIXED               ROW               ROW

ROW               ROW                 STATEMENT         TOTALLY FAILS VERY QUICKLY
ROW               ROW                 MIXED             ROW
ROW               ROW                 ROW               ROW

What's going on here?

When B writes to its binary log, it writes an entry to replicate the change it made.  It writes these entries in the format its local optimizer determines should be used.  When STATEMENT or ROW mode is in effect, that's the only choice, so incoming STATEMENT becomes outgoing ROW, in your case.  Server B easily translates because it executes the incoming event and then simply logs which rows it inserted/updated/deleted in its local data, and the values of the columns in those rows.

  Each MySQL Server can set its own and only its own binary logging format (true whether binlog_format is set with global or session scope). This means that changing the logging format on a replication master does not cause a slave to change its logging format to match. [...]

  Changing the binary logging format on the master while replication is ongoing, or without also changing it on the slave can cause replication to fail with errors such as Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.'

  http://dev.mysql.com/doc/refman/5.7/en/binary-log-setting.html

That's what I was referring to, above, with TOTALLY FAILS.  The only invalid configuration is for a server to be in STATEMENT mode while at least one upstream master is in ROW or MIXED.

Why?  Every possible query that can be represented in STATEMENT format can always be subsequently represented in ROW format, because ROW format is absolutely deterministic.  Conversely, no query replicated in ROW format can ever be subsequently replicated in STATEMENT format.

Note that MIXED format simply gives the local optimizer the option on a query by query basis of how to replicate -- STATEMENT or ROW -- but if the incoming replication event is ROW the only outgoing choice is also ROW.

Also, STATEMENT mode is seriously terrible.  Get away from it as soon as you can.  Before then, it's vitally critical to verify that the data on B is identical to the data on A in every way, because the data on B can slip out of sync much easier and go undetected when A is in STATEMENT mode.


List of IDs that have an assignement to ALL other Ids in another table

You can try something like:

select a.assignmentID, c.city
from #cities c
join #assignements a on a.cityId = c.cityId
where c.cityId in ( select c.cityId
                    from #cities c
                    left join #assignements a on a.cityId = c.cityId
                    group by c.cityId
                    having count(*) = (select count(distinct assignmentID)
                                       from #assignements)
                  )


Does InnoDB Log Files take RAM space or Disk Space

InnoDB log files are stored on disk and so consume disk space - http://dev.mysql.com/doc/refman/5.5/en/innodb-configuration.html. However the innodb_buffer_pool_size does impact memory usage.

I find this a good reference for memory sizing - http://www.mysqlcalculator.com/


Trouble Connecting to SSIS in SQL Server 2017 (14.0.1)

If anyone else has a similar setup with SQL Server 2017 (14.0.1000) and SSIS (14.0) as of the creation of this question/answer SSMS 18.4 will not allow you to connect to SSIS. The only fix I have found is following Jacob's suggestion and downloading SSMS 17.9 which resolved the issue.


You'll want to use the built-in extension pg_stat_statements, or the external pg_stat_plans enhanced approach.

There aren't any network I/O statistics kept by PostgreSQL yet. There's someone working on it for 9.4, though I don't know if it'll get accepted into 9.4.


Performance hickups after Postgres 9.4 upgrade

The problem had to do with a kernel feature called Transparent Huge Pages (THP.)  You can diagnose this with perf top:

 59.73%       postmaster  [kernel.kallsyms]      [k] compaction_alloc
  1.31%       postmaster  [kernel.kallsyms]      [k] _spin_lock
  0.94%       postmaster  [kernel.kallsyms]      [k] __reset_isolation_suitable
  0.78%       postmaster  [kernel.kallsyms]      [k] compact_zone
  0.67%       postmaster  [kernel.kallsyms]      [k] get_pageblock_flags_group
  0.64%       postmaster  [kernel.kallsyms]      [k] copy_page_c
  0.48%           :13410  [kernel.kallsyms]      [k] compaction_alloc
  0.45%           :13465  [kernel.kallsyms]      [k] compaction_alloc
  0.45%       postmaster  [kernel.kallsyms]      [k] clear_page_c
  0.44%       postmaster  postgres               [.] hash_search_with_hash_value
  0.41%           :13324  [kernel.kallsyms]      [k] compaction_alloc
  0.40%           :13561  [kernel.kallsyms]      [k] compaction_alloc

The compaction_alloc function points at a problem.  You can turn off THP with:

echo never > /sys/kernel/mm/redhat_transparent_hugepage/enabled

Postgres versions before 9.4 do not specifically ask for huge pages, but it can be forced on them with always.

Here's a link to RedHat discouraging THP for database workloads.


How to query large sets of data using server resources to the max

there is no such hint, if your sessions are the only workload, server resources will be distributed equally among them
It could definitely help. Perhaps 1 session per core. Make sure that clustered index built on ID, so data retrieval can be distributed by chunks using that column and query table with the most efficient operator for such scenario: clustered index seek (with range scan)
Consider to increase TCP packet size: Performance Best Practice: Network Packet Size
Antivirus/Network Security agents on a database server. Some companies have it installed by default. Such middle-ware can hit both IO and network layers, therefore consider to add appropriate exception rules or even disable them during the export.


mysql on duplicate key update the entire row

You used:

INSERT INTO contacts
    (id, ssn, name, gender, dob, address, profession, phone)
VALUES
    (default, 1, 2, 3, 4, 5, 6, 7)
ON DUPLICATE KEY UPDATE
    ssn = VALUES(1) ;

where 1, 2, 3, .. are the values passed form the application (check your method by the way, seems like it's vulnerable to SQL injection)

The error you get is because VALUES(1) is not valid syntax. The last line should be either:

    ssn = 1 ;               -- the value , repeated

or:

    ssn = VALUES(ssn) ;     -- reference to the value that was
                            -- to be inserted to column "ssn"


Return an error where the ID matches, but the owner_id doesn't

Try this query. I don't know if its gonna work, please let me know. My idea is to select the owner in a A table and the id in a B table. If you get the owner_id and not the id, that means the id is missing. If it returns no data, that means there was no owner.... Maybe you can try different joins like FULL OUTER JOIN and see what happens... Good luck!

select A.owner_id ,
B.id
from(select distinct owner_id from  posts where owner_id=234) A left outer join
(UPDATE posts SET (foo=bar) WHERE id=123 AND owner_id=234 RETURNING owner_id, id) B on A.owner_id = B.owner_id


Using sql server alias via management studio on another computer

No, aliases are local to the client machine. Use DNS if you want a central configuration place.


In Oracle 10G, regardless of whether dedicated or shared mode is used the client
always reaches the listener first: to determine whether the instance is available.
In dedicated server mode, from then on, there is one server process for each
client (user process) that directly handles the client.
In shared server mode, the listener passes back the address of a dispatcher process
to the client to submit requests.

Oracle has 3 types of processes:

Server processes connect to user processes and run on the database server.

User processes run on the database client.

Background processes run on the database server and populate the PGA memory areas
read and owned by the server processes.  In dedicated server mode, the PGA memory
area is held by the server process and contains the data needed by the client.
In shared server mode all the data that would have been in the PGA is stored
in the SGA except for the server process stack space.

In Dedicated Server Mode:
There is One Server Process for Every User Process.

In Shared Server Mode:
The user processes all connect to a dispatcher and there are fewer
server processes to reduce the process count on the server.
There is one request queue shared by all the requestors, but
there are individual response queues for each client.

The TNS connect string is different for shared server mode.
After the client program connects, the client program doesn't know
whether the database is running in dedicated or shared server
mode, but the performance of each mode is different (shared server
is not intended for high throughput applications)..


When you created the clustered index with ename the rows in the table were shuffled to be in that order. When you dropped the index the rows were not reshuffled.

There is no guarantee that the rows will be returned by a query in the order that they are stored, but often this is what happens.

If the order is important then you would add an ORDER BY (and would be well advised to cluster the table on that field too to avoid sorting every time).

But don't choose a clustering key just because of that. For a more thorough discussion of clustering and heaps see http://kejser.org/clustered-indexes-vs-heaps/


Change the ft_min_word_len value to specify the minimum length of the word to be included in a MyISAM FULLTEXT index.

ft_min_word_len
The minimum length (default: 4) of the word to be included in a MyISAM FULLTEXT index.
Note: FULLTEXT indexes on MyISAM tables must be rebuilt after changing this variable.
Use REPAIR TABLE tbl_name QUICK.

Source: https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_ft_min_word_len


Fast backup & restore of MySQL database to revert recent changes

Copying the data directory, as you mentioned, is one way to accomplish what you need. Here are the steps, and if you face any error, please post it.

Shut MySQL server down; or in a terminal flush tables with lock FLUSH TABLES WITH READ LOCK [Important: Keep the terminal open]
Copy the data directory: cp, or rsync
Restart the server if you shut it down; or go back to the terminal and unlock the tables: UNLOCK TABLES
Make multiple copies of the copy you already made
Chnage the owner of the newly created directories to mysql: chown mysql:mysql dir_name (I think it is chown -r, so it runs recursively)

So far, you have multiple copies of the data at the point of time that you need.

In order to "restore" a copy, you can do one of the following:

Change the value of datadir in my.cfg (or my.ini) to a location of one of the data copies, and restart the server; OR
Shut down the server and copy the contents of a copy of the data back to the original data directory, and start the server again.


Well, I don't know about ever, since SQL Server doesn't keep that information around forever.

Also, it's unclear if you are asking about tables or databases. Since you mentioned both, here is a query against the default trace (based on this question) that will return either, but only going back as far as the default trace goes. It shows who dropped what, when, and from where.

DECLARE @path nvarchar(260) = (SELECT
    REVERSE(SUBSTRING(REVERSE([path]),
    CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
  FROM sys.traces WHERE is_default = 1);

SELECT ObjectType,
       ObjectName,
       DatabaseName,
       LoginName,
       HostName,
       ApplicationName,
       StartTime
  FROM sys.fn_trace_gettable(@path, DEFAULT)
  WHERE EventClass = 47 AND EventSubClass = 1
  ORDER BY StartTime DESC;

The enum for ObjectType is documented here, but if you want to filter, you can use ObjectType = 16964 for databases and ObjectType = 8277 for tables.

The default trace rolls off and purges the oldest rows/files as it adds new ones. If you need to go back further than that, you will need to resort to restoring older backups, if you still have them.


How can I append a total to a table and split the table by group (order_id)?

Try this.  I may or may not work, depending on whether you have multiple rows for a given order_id:

select order_id as 'Order ID',
    DATE_FORMAT(date, '%d/%m/%Y') as 'Date',
    prod_title as 'Item',
    SUM(amount) as 'Amount'       -- changed
from transactions
where date >= '2016-10-01'
GROUP BY order_id  WITH ROLLUP    -- added
order by order_id,
    amount desc;

If that fails, then

( SELECT ... as you have, but without ORDER BY ... )
UNION ALL
( SELECT NULL, NULL, 'Total', SUM(amount)
       from transactions
       where date >= '2016-10-01' )
ORDER BY order_id,
    amount desc;

This will put the Total at the top; that can be fixed by changing to something like

ORDER BY order_id IS NOT NULL, order_id, amount DESC


Enterprise Manager Database Control on host with an underscore in it's name

This is happening because an underscore in a hostname is not a valid character according to the RFC.

  "The Internet standards for protocols mandate that component hostname
  labels may contain only the ASCII letters 'a' through 'z' (in a
  case-insensitive manner), the digits '0' through '9', and the hyphen
  ('-')."


Availability Group Listener has a DNS name, for example AG-Listener-01.domain.com

In the connection string, you just specify

Server=AG-Listener-01,1433;Initial Catalog=YourDatabase;User ID=SQLLogin;Password=SQLPassword;
MultiSubnetFailover=True


Large Sleep counts causing THREADPOOL wait?

What I really want to know is do sleeping sessions hold a thread?

No, they do not,

  A SQL Server worker thread, also known as worker or thread, is a
  logical representation of an operating system thread. When executing
  serial requests, the SQL Server Database Engine will spawn a worker to
  execute the active task.

But instead of actually spawning a new worker, one is typicall available in a pool

  The max worker threads option enables SQL Server to create a pool of
  worker threads to service a larger number of query requests, which
  improves performance.

Thread and Task Architecture Guide -- SQL Server task scheduling

which you can see from running

select count(*)
from sys.dm_os_workers

select count(*)
from sys.dm_exec_sessions

Since you typically have more sessions than workers.


Differential backup successfully completes without taking the backup of few databases

If you are saying that some databases are being skipped, then I would bet money that you are using sp_MSforeachdb. Stop using that procedure, it is an unreliable pile of unsupported and undocumented crap that is known to skip databases in certain scenarios (usually where > 100 databases are used on a busy system).

Solution: write your own cursor with sensible options:

CREATE PROCEDURE dbo.my_own_foreachdb
  @command NVARCHAR(MAX)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @db SYSNAME, @sql NVARCHAR(MAX);

  DECLARE c CURSOR
    LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR SELECT name FROM sys.databases;

  OPEN c;

  FETCH NEXT FROM c INTO @db;

  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @sql = 'EXEC ' + QUOTENAME(@db) + '..sp_executesql @command';

    EXEC sp_executesql @sql, N'@command NVARCHAR(MAX)', @command;

    FETCH NEXT FROM c INTO @db;
  END

  CLOSE c;
  DEALLOCATE c;
END
GO

Sorry, untested, busy day over here - but should demonstrate the approach.

Or see this and this (I wrote a more reliable and flexible replacement).


SQL Server Agent Job Step "Run As" dropdown

The "Run As" is so that you can specify a proxy account to run the particular step as.  The proxy account defines the security context that a job step can run as.


Want to know whether an 'UPSERT' operation performed INSERT or UPDATE

According to MySQL documentation:

  With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the CLIENT_FOUND_ROWS flag to mysql_real_connect() when connecting to mysqld, the affected-rows value is 1 (not 0) if an existing row is set to its current values.

So, if your statement would affect just one row, if the number of affected rows is 1, then it is an INSERT; otherwise, an UPDATE. To retrieve the number of affected rows using PDO, use rowCount method.


sql server: sp_send_dbmail : Can I build the data to send in discrete operation?

That might be the case of checking the attachment limit size set in your mail server, or the Maximum File Size (Bytes) setting in the database mail configuration. The default value for this setting is 1000000(bytes), if your result set is larger than this value it won't be sent.


Truncating Table Partitions Containing Data and Global Indexes

Are the 37 indexes you have on the table global indexes?
Option 1:
Where you truncate and then rebuild
This method is most appropriate if the partition that you are truncating has at least 10% of the total data in the table.  But rebuilding these 37 indexes is going to take time.
Option 2:
Where you delete and truncate the partition
The delete statement here updates any global indexes, fires triggers and generates redo and undo logs. To avoid the generation of logs and make this faster, Just before the deletion, you may alter the partition as
ALTER TABLE sales MODIFY PARTITION dec98 NOLOGGING;

and then proceed with deletion and truncation.  But for partitions containing a large percentage of the total data in table, this will still be slower
Option 3:
If you have a global index on the partition, UPDATE INDEXES will rebuild this index as well. Local Indexes are truncated automatically while partition truncate .
My suggestion here would be use Option 2 with NOLOGGING; as below,
ALTER TABLE sales MODIFY PARTITION dec98 NOLOGGING;
DELETE FROM sales PARTITION (dec98);
ALTER TABLE sales TRUNCATE PARTITION dec98 UPDATE INDEXES;


There is no easy way to suppress specific events (with the exception of log backups using TF3226 or using the -n startup parameter which suppresses all events as ably described here).

What you should probably be doing is addressing the root cause of the issue.  If you have decided not to do that, you can simply suppress the ids when you report on your event log.  There are a few ways to do this:

Use Filters in Windows Event Viewer
Click the 'Filter' button on the right-hand side, and enter the ids you want to suppress preceded with a minus sign, eg

Using Powershell cmdlet Get-EventLog and filtering the output, eg

Get-EventLog Application -Newest 100 | Where-Object { $_.EventID -ne 3014} | Format-Table TimeWritten, EventID, Message -auto

In summary, try filtering the event ids at your reporting time, rather than trying to suppress them altogether.  At least if they are recorded in the event log, they could be analysed by someone else or for another purpose.

HTH


Can somebody help me normalise this database/check my workings

You need a one-to-many table to list the types of pets that a person has, like

create table people_pet_types (
  person_name varchar(...) references people(name),
  pet_type varchar(...),

  primary key (person_name, pet_type)
);

Assuming name is the primary key of People (which it shouldn't be, else you can't have two people with the same name).

This allows a person to have many pets types.

Also, "Age" and "Alive" should probably be derived from date_of_birth and date_of_death (nullable). Otherwise, you need to update the table to re-calculate the age at least every year. You can use a VIEW to calculate the age, and alive values for you.


Receiving connection timeout error when call PQconnectdbParams to connect to Postgres

You are mixing up the different connection functions.

Your call would be correct with PQconn. PQconnectdbParams takes two parameters per connection string keyword

Replace PQconnectdbParams with PQconn and you should be fine.


SQL Server Agent job returns a failure status message after the job succeed.

For me This looks a clash between job schedules. if the job is executing one day and fail the next day,there may be other job running that might contradict with this schedule. you might have to check all your job schedules and take appropriate action accordingly.

This link might help you to check all the jobs schedules running in your server.
http://www.sqlprofessionals.com/blog/sql-scripts/2014/10/06/insight-into-sql-agent-job-schedules/


Increment of integer PK values by 1 doesn't throw duplicate key error. Why?

The checks (about UNIQUE, PRIMARY KEY and other, NOT NULL and CHECK constraints) are done after the whole UPDATE statement has finished. This is the expected behaviour in SQL. Operations/statements are performed on sets (of rows), not on each row individually.(*)

And while for NOT NULL and CHECK constraints, it doesn't matter if the checking is done after each row change or after the whole statement, for unique constraints (UNIQUE and PRIMARY KEY) it does matter.

In fact, the SQL standard has 2 options. The checks are either done after the statement (when the constraint is NOT DEFERRABLE) or even later, at the end of the transaction (when the constraint is DEFERRABLE) and the statement is part of a transaction.

SQL-Server supports only NOT DEFERRABLE constraints, so checks are after every statement and not at the end of transactions.

(*)
Perhaps you have been used to MySQL, which shows this non-standard behaviour, checking constraints after each row update (or insert.) See the SQL-Fiddle where the update fails (if you change the DBMS from SQL-Server to MySQL.)


Putting some calculations on a database entry from phpmyadmin

You could do this in phpMyAdmin, but you shouldn't.

If you insist, you could use an input transformation to do so. You'd have to write one, but doing so shouldn't be too difficult if you have a basic knowledge of PHP. However, this is a bad idea for many reasons, the biggest of which is that you shouldn't be giving your users access through phpMyAdmin. phpMyAdmin is meant as a tool for a system administrator to manage their databases and by giving your users direct access, they in turn can do whatever they want, including changing the percentage to work in their benefit or modifying costs, quantities, or order details that they shouldn't have access to. Plus the interface is not what you want to present to a user, anyway. You should write your own custom application code (or use some application designed for sales and purchase) rather than forcing your users to interact with your database through phpMyAdmin.

I know you said you don't want to do it through code, but the two ways to handle this properly are through your application code or through code on the MySQL side (either through a stored procedure or a trigger on that column).


Postgres ignoring wal_level setting in conf

Take a look at the postgresql.auto.conf file - it's populated if you changed it using alter system.
Also, make sure to restart the instance and not just reload it as this kind of change requires restart.


Oracle, Create table with inline constraint declarations using a different tablespace

Like this:

create table THIS_TABLE (
  id number NOT NULL,
  constraint THIS_TABLE_PK PRIMARY KEY(id)
             USING INDEX TABLESPACE INDEX_TABLESPACE
) tablespace DATA_TABLESPACE;

USING INDEX TABLESPACE is the syntax - you weren't far off.

As far as good/bad practice is concerned, that's opinion-based, so not really something that should be asked here. The alternative is to use the ALTER TABLE .... syntax after table creation.


How about using a PL/Sql block:
DECLARE
    v_sessions NUMBER;
BEGIN
    WHILE true LOOP
        SELECT COUNT (*) INTO v_sessions FROM v$session;
        DBMS_OUTPUT.PUT_LINE ('Sessions: ' || v_sessions);
        DBMS_SESSION.SLEEP (300); -- 5 minutes
    END LOOP;
END;

If it really is 8i, use DBMS_LOCK.SLEEP (300) instead.


SELECT all rows with sequential id after a specific id

A query that doesn't use recursive CTEs or window functions or variables:

SELECT i.*
FROM Inheritance AS i
    JOIN
    ( SELECT ui.id
      FROM Inheritance AS ui
      WHERE ui.id > 5
        AND NOT EXISTS
            ( SELECT 1
              FROM Inheritance AS pi
              WHERE pi.id = ui.id - 1
            )
      ORDER BY ui.id
      LIMIT 1
    ) AS up
    ON  i.id < up.id
WHERE i.id > 5 ;


EventClass 15 is "Audit Logout" there's no TextData to associate with that. It's simply the connection closing. TextData would only be associated with events like "SQL:StmtStarting", etc...

See here for more info: http://weblogs.sqlteam.com/mladenp/archive/2007/11/09/Map-SQL-Server-Profiler-EventClass-ID-to-its-name-in.aspx

From comments: The duration it shows in the log is the total connection duration.


Can I grant privileges to a Postgres user on all schemas belonging to a database with one command or script?

Yes, using DO, cursors, execute, and the pg_namespace table:

DO $$DECLARE _schema record; _user name := '<your_username>'
BEGIN
  FOR _schema IN
    SELECT * FROM pg_namespace WHERE nspowner > 10 OR nspname = 'public'
  LOOP
    EXECUTE 'GRANT SELECT, INSERT ON SCHEMA '||
            quote_ident(_schema.nspname)||' TO '||quote_ident(_user);
  END LOOP;
END;$$;


Migrate large (200GB) mysql databases to new hosts with as little downtime as possible

If you can tolerate a little downtime you can make a snapshot and share the snapshot with the new account.  This is a simple and cheap approach though you will be down for the time it takes to make the snapshot, share and then restore it and then point your clients to the new instance.

For a solution with much less downtime, if you are using RDS you can make use of AWS DMS (Data Migration Service).  It can be used as a method for either a one-off data migration, or you can use it to replicate data from one VPC to another within the same AWS Account, or even cross account, so that you can replicate data written to the old RDS into the new one, until you are ready to switch to using the RDS in the new account.

Good documentation on AWS DMS can be found here:
https://docs.aws.amazon.com/dms/latest/sbs/DMS-SBS-Welcome.html

If you can, to minimise downtime, set up the current database with a CNAME and have your clients address the RDS via that CNAME.


Prevent inserts to a non existing Synonym

Check if the synonym exists before the insert/update.

IF EXISTS (SELECT * FROM sys.synonyms
WHERE name = 'VirtualTable')
 PRINT 'do your stuff'


Multi-site Failover Cluster Instance for HA and DR to alwaysON Availability Groups

The best practice for your scenario is: 2 nodes in Location A with their subnet say Network_A 192.168.1.x and one node in location B (DR) with subnet Network_B 192.168.2.x.

The weight in Location A is more so you don't need to add disk or folder to your cluster. Your failover Cluster will take 2 IPs one from Network_A and the other from Network_B and your Listener too. The Network_B IPs appears in your cluster offline unless you Failover the DR site, in normal cases you can use SQL database in your DR as read-only.

One important thing: if you have SAN replication don't make your Databases as part of this plan nor part of RDM (raw mapped drive).


Merge multiple "is exists" queries into one statement

UNION removes duplicate rows from the overall result, UNION ALL will preserve them.

To know from which table the result came, add a constant value:

SELECT 'one' as source,
       1
FROM table_one
WHERE id = 'a'
UNION ALL
SELECT 'two',
       1
FROM table_two
WHERE id = 'aa'


Bag union operation in relational algebra

After giving this question little thoughts, I found, I was missing the projection part which means the only attribute A is considered for the union. So, basically all we have to do is grouping using GAMMA and then select attribute A and perform union.

Derivation:

\gamma(x,y,avg(z)) => {(2,6,5),(5,6,3),(9,0,4)}

renaming avg(z) to A and projecting that column results in:

{5,3,4}

Remaining 2,3 and 4 with the same method then becomes:

{11,8}
{5,1,1}
{5,9}

So we end up with the bag:

{5,3,4, 11,8, 5,1,1 ,5,9}

Clearly statement a, b and d is False and statement c is True


How to update a column in a table with data from another, unrelated tables columns data jus in order it selected?

Create a column in each table that you can update with a sequence. Then you have something to join on.


Get the total usage (in percent) between two specified dates

if I understand your requirment correctly your command should be like this, if not please provide example result.

Select DeviceName, (Count(ConnectStatistics)* 100 / (Select Count(*) From [dbo].[Statistics])) as Score, pcname
From [dbo].[Statistics]
where datetime = '2019-02-27 09:40:00'
Group By DeviceName,pcname


SQLcl Oracle to export data to CSV without blank first line and without carriage return character at the end

Why don't you just use the UNLOAD TABLE command ?
sql -s scott/tiger@localhost:1521/spatialdb <<EOF
unload table us_cities
EOF

Or even this simple one-liner:
echo "unload table us_cities" | sql -s scott/tiger@localhost:1521/spatialdb

This creates file US_CITIES_DATA_TABLE.csv in your current directory.
Check the help about the UNLOAD command to get details about various options for formatting the output.


Can a MySQL trigger include a where clause

You're almost there. Your trigger should probably look like this:

CREATE TRIGGER update_sire_name before UPDATE ON table_name
for each row

    set new.sireName =
    (select name from table_name where regNo = new.sireRegNo)

dbfiddle

1 Yes, there can be a where clause in a trigger.

2 Consider using numeric data types (e.g. INTEGER) where values are clearly numeric (e.g. regNo)


Your question does not indicate you are using any type of HA/DR (Clustering, Availability Groups, Mirroring, etc.), so my answer is based on the assumption of a relatively plain install of Sql Server.

This list may not be all inclusive (and I’m interested in what others might do), but it’s a list I have cobbled together over the years from many different web posts.  Since I don’t even remember where each item came from, my apologies to the original authors for not citing them directly.

Review the Readme for Service Pack/cumulative update/hotfix. Any concerns/recommendations will be found in the readme.
Always apply Service Packs and Updates on non-production Instances first. Once the Instance with the new Service Pack is up and running without any issues for a week or two, plan the same steps on your Production.
Before applying any service pack or cumulative update, run SELECT @@VERSION to retrieve your current install level and save it somewhere.  If you need to fall back from a failed upgrade, you’ll need this information.
Backups, Backups, Backups! - As I understand it, Sql Server service packs and cumulative updates only affect ‘system’ databases, so you should ‘at least’ take full backups of master, model and msdb.  I’m paranoid, so I usually back up my user databases as well.  You should also run RESTORE WITH VERIFYONLY on all backups.
Plan for failure. Depending on the type of failure, you ‘may’ be able to simply restart the upgrade and it will be successful.   A failure during the upgrade may force you to uninstall the upgrade using Add/Remove programs AND force you to restore your system databases.  If you haven’t taken the time to get comfortable with restoring the system databases (master, model, msdb), now is the time to practice that on a test server.  A failed upgrade is not the time to try and learn that process.  Search the internet for “sql server restore system databases”.  Make sure you have the ‘original’ base install media and service pack/cumulative updates for your ‘current’ @@VERSION.  You ‘might’ need to remove the current instance and reinstall up to the level you were at before the upgrade – then, you could restore your system databases and at least be back where you were before the upgrade.
I usually stop the Sql Server Agent before starting the upgrade.
Now it’s time to apply the service pack.  Hopefully all goes well and you’ll get to a successful completion screen.
You should immediately take new full backups (at least master, model, msdb)
Restart the Sql Server Agent, if stopped.
If possible, I usually restart the entire server.


SQL - Get the values of another column for values in a given column

with data as
(
  select 'Apple' as item, 'Rome' as destination from dual union all
  select 'Apple' as item, 'Barcelona' as destination from dual union all
  select 'Apple' as item, 'Moscow' as destination from dual union all
  select 'Apple' as item, 'Paris' as destination from dual union all
  select 'Apple' as item, 'London' as destination from dual union all
  select 'Orange' as item, 'Cairo' as destination from dual union all
  select 'Orange' as item, 'Cairo' as destination from dual union all
  select 'Orange' as item, 'Cairo' as destination from dual union all
  select 'Grapes' as item, 'Berlin' as destination from dual union all
  select 'Rice' as item, 'Manila' as destination from dual
)
select Item,
listagg(Destination, ',') within group (order by Destination) as Destination
from data
group by Item having count(distinct destination) > 1;

ITEM   DESTINATION
------ --------------------------------------------------
Apple  Barcelona,London,Moscow,Paris,Rome


The where clause is evaluated after the join. A left join B means all tuples  from A together with the matching tuples from B, if no tuples from B match null is the result. Given:

create table t1 (studentid int not null, class int not null);
create table t2 (studentid int not null);
insert into t1 values (1,1),(2,2);
insert into t2 values (1);

select * from t1 left join t2 on t1.studentid = t2.studentid;

STUDENTID   CLASS       STUDENTID
----------- ----------- -----------
      1           1           1
      2           2           -

i.e. there is no row in t2 that matches studentid 1 so a null is the result. For:

select * from t1
left join t2
    on t1.studentid = t2.studentid
   and t1.class = 1;

Nothing changes. For:

select * from t1
left join t2
    on t1.studentid = t2.studentid
where t1.class = 1;

on the other hand, the where clause is evaluated after the join so t1.studentid  2 is removed from the result.


Does DB2 LUW v10.5 Support Windows Authentication and How is it Set?

DB2 supports various authentication models, special models for Microsoft Windows included. I recommend starting at DB2 and Windows Security in the DB2 Knowledge Center for your options and details.


Auto increment id column vs index VARCHAR columns

UUIDs are terrible when the table becomes too large to be cached.  (If your table will never get very big, you can ignore the rest of my rant.)

The problem comes with the randomness of UUIDs.  You fetch one row; it's in one place.  You fetch another row, it is extremely likely to be in some other block.  This leads to lots of I/O.

In contrast, AUTO_INCREMENT ids are inserted in roughly chronological order.  A bunch (say, 100) rows will land in one block before moving on to the next block.  This makes INSERTs efficient.  It often makes reads efficient, too -- because often you are looking for records of similar vintage -- such as with a date range.

Think of News articles.  Most people look at the latest news; the older records collect dust in the cache until they are bumped out.  So, the cache tends to keep the latest rows.  With UUIDs, old and new articles share the same block.

More on UUIDs.


Cleanup of AG databases on secondaries nodes

The current design is that the stored procedure will decide if the database should be backed up. Only if the database should be backed up, it will go into the code that does the work (creates sub-directories, backup, verify, and cleanup).

It could make sense that if you are running with @CleanupMode = 'BEFORE_BACKUP', then it should delete backups, even if the database should not be backed up.

Could you create an issue for this on GitHub.

https://github.com/olahallengren/sql-server-maintenance-solution/issues


SSIS 2012 metadata not refreshing on re-deploy

Honestly, you shouldn't be editing the package to point to another environment before deploying it. Please look into package configurations to learn about methods to deploy your packages without editing the package itself.

Likely your connection strings and other settings should just be a variable read from a configuration file.

Since you are using SQL 2012 you should probably be using environments (thanks @MartinSmith)


"Unspecified Error" when executing SSIS Package

I was not able to get a more specific error message than: "Unspecified Error".

I solved the problem by improving the performance of the queries used.

There was a particular expensive proc which took 10 minutes to run. It joined 8 tables, one of which had 13m rows, had 5 where clauses, and produced 13 million rows. It was being run almost 20 times in the course of the various SSIS sub-packages.  Sometimes it would be run 2-3 times in a single package, producing the same set of results, but obviously table-scanning each time.

So I created a table to store the results of that expensive query, made a scheduled job that runs daily at midnight to populate it once.  Then i just joined the results of that table in each place where previously the expensive proc was being run.

Now the job runs in 25 minutes consistently (instead of 3 hours), and i haven't seen a single failure in the so far 8 runs it executed.

So I suspect that the under-the-hood issue with the "Unspecified Error" was an out of memory exception that may or may not have been linked to the fact that the query was being executed over VPN linked server connection.


Starting MySQL with SSL option using systemd

You can the SSL variables into the my.cnf file and load on regular systemctl call.


General info on Resources for Running SQL Server queries

One popular site is SQL fiddle that currently provides online test facilities for MS SQL Server 2008 and MS SQL Server 2014 (among others).

Another one is "our very own" data.stackexchange.com, where you can run arbitrary queries against recent snapshots of the site data on SQL Server.


Another question about finding the last time a value changed

I believe the easiest way would be using LAG or LEAD functions. They allow you to accesses data from a previous or subsequent row in the same result set without the use of a self-join. But they're available starting with SQL Server 2012 (11.x).
So your solution would require the a self-join like this:
WITH CTE_MostRecentChange
AS
(
    SELECT
        TimeStartTz,
        RateID,
        ROW_NUMBER() OVER (ORDER BY TimeStartTz DESC) AS RowNumber
    FROM MostRecentChange
)
SELECT TOP(1)
    CTE_1.RowNumber,
    CTE_1.TimeStartTz,
    CTE_1.RateID
FROM CTE_MostRecentChange CTE_1
    LEFT JOIN CTE_MostRecentChange CTE_2
        ON CTE_1.RowNumber = CTE_2.RowNumber - 1
WHERE CTE_1.RateID <> CTE_2.RateID
    OR CTE_2.RowNumber IS NULL
ORDER BY CTE_1.TimeStartTz DESC;


Faster alternative to count value from 2 queries and select with WITH

It's a bit tricky to do query rewriting without knowing any keys etc, but if I'm not mistaken cm can be rewritten as:

SELECT Count(distinct m.message_id) AS count_messages
FROM   messages AS m
JOIN   order_items AS o
    ON m.product_id = o.product_id
JOIN   orders ox
    ON ox.order_id = o.order_id
WHERE m.message_type = 0
  AND m.seller_id IN( 3, 2, 6, 1, 9 )
  AND current_date >= ox.order_creation_date

using the same technique and the fact that A |x| B <=> B |x| A give us:

SELECT Count(distinct o.order_item_id) AS count_order_items
FROM   messages AS m
JOIN   order_items AS o
    ON m.product_id = o.product_id
JOIN   orders ox
    ON ox.order_id = o.order_id
WHERE m.message_type = 0
  AND m.seller_id IN( 3, 2, 6, 1, 9 )
  AND current_date >= ox.order_creation_date

for co. This can be combined to:

SELECT 100*Count(distinct m.message_id) :: Decimal
         / Count(distinct o.order_item_id) :: Decimal
FROM   messages AS m
JOIN   order_items AS o
    ON m.product_id = o.product_id
JOIN   orders ox
    ON ox.order_id = o.order_id
WHERE m.message_type = 0
  AND m.seller_id IN( 3, 2, 6, 1, 9 )
  AND current_date >= ox.order_creation_date


SQL - I have just Differential backup but database exists

You are pretty much out of luck unless you have a full backup previous to the differential backup you want to restore. Your backup chain is incomplete if you do not and with that your data is simply gone.

It sounds as if you received the error for the need of a full backup, and then took a full backup and restored that, then your differential. Sorry to tell you that is not going to work either.

Your only hope is that the data can be recovered manually because you are not going to be able to restore that database with out a previous full backup.

I would highly suggest you adjust your backup process now so this can be prevented. You need to ensure your backup chain is complete in order to provide a proper recovery process (along with ensuring integrity).


can I copy-paste SQL Server 2005 Express on XP Pro or Vista machine and make it work without installer?

No, you need to go through the installer to get SQL Express working on the other machines.


pg_commit_ts growing in postgres instance

came to find out, that unless the XIDs are cleared by vacuum / autovacuum pg_commit_ts would not be cleared. So basically this instance needs attention on tuning autovacuum parameters to run autovacuum efficiently


Performance of dynamic SQL Server reporting queries?

Dynamic SQL isn't in and of itself a performance problem. After all, the queries run against SQL Server regardless of how they were assembled, and still generate (and cache) the same execution plans as identical queries not constructed dynamic SQL. In ancient SQL Server versions, things like stored procedures were compiled as a whole, but SQL Server has been able to optimize at the statement level for a long time now.

What you need to be sure of is:

that the rows you are after can be seeked efficiently. You're never going to be able to create a covering index for all 60 columns, but if you can reduce the number of rows, any lookups or other mechanisms to get the columns not in the index shouldn't be too terrible. Hard to get into more specifics here, without any specifics to start with. I'm not sure if a columnstore index is going to give you what you need, especially because in SQL Server 2012 at least, there are a lot of limitations that most shops can't work around.
another option is to ensure that your index(es) used for searching to locate rows have all the output columns that aren't used as predicates as INCLUDE columns. These don't take up space in the index key but can help prevent lookups without creating a different index for every output combination. The trick here is to simply always return all columns, but move the logic of which columns to show to the reporting tool. Another nice thing about this is that if the user decides they want to add these three columns and hide those other two, the application should be able to do that without making another round-trip and re-querying all the data.
make sure you are using the optimize for ad hoc workloads server setting. This prevents single-use plans from occupying space in the plan cache wastefully.


We had the same problem with our RDS instances for a stretch of a week, but AWS support patched it on Sept 16. Now all RDS instances we launch with postgres 9.4.4 have functional and normal-logging postgis 2.1.5 on them. Your instances should be working as expected now too.


Does anybody has other ideas to solve this problem?

Do as error message says, perhaps you connected wrong SQL Instance this time.
Using following query, you can verify whether the database part of AG or not:
select name, IIF(group_database_id IS NULL, 'NO', 'YES') is_joined_AG
from sys.databases;

select DB_NAME (database_id) mirrored_db
from sys.database_mirroring
where mirroring_guid is not null;


Check and remove iteratively without an index

The current version does not allow for specifying different priorities for different operations.  It is likely that maintaining an index on the collection to allow this periodic process to execute much quicker than a full collection scan will give you better overall performance.


Randomly generated password for mysql admin over time?

Why would you want to do this?  What risk does it mitigate?

This sounds like a great way to lock yourself out of your database in a way that requires downtime to recover.  Limit your exposure by securing the servers with firewalls and SSH and keep MySQL authentication simple.  If you are worried about brute force attacks from within your trusted network, set max_connect_errors to a low value so the remote host will get blocked.  And don't use "root" as your root user.  Disable that account and create a new super user with a different name.

That device that your friend has was likely built by RSA and is a SecurID tag which uses two-factor authentication to secure workstations.  An enormous amount of R&D  was built into that product.  Your VB script will not have that luxury.

Quite simply, MySQL authentication is not hardened enough to warrant this type of infrastructure.  An attacker worth his salt provided with access to an open MySQL port is going to look for more general exploits rather than waste time brute forcing.

Just off the top of my head, some of the problems with this:

What happens if your clocks get off by a minute or two?
What if the job or trigger fails?  How do you know what state your DB password is in.
How do you generate the same random string within your VB script and on the server?
How do you store the generated password securely?

Don't do it.  Invest your effort into picking strong passwords (not l33tsp34k) and locking down the server itself rather than attempting to reproduce what a company with thousands of employees at their disposal has managed to do.


Firstly, your datatypes and precision should always be identical on your FK references.

In Habitat you have HNome as a VARCHAR(100). In Cotem, you have HNome as a VARCHAR(64).  These need to be the same.

It would help if you would post the error message you are getting, but I suspect the error is you are trying to insert a NULL value into ANome in Cotem.


Poor cardinality estimate disqualifies INSERT from minimal logging?

Why is it that the second query does not qualify for minimal logging?

Minimal logging is available for the second query, but the engine chooses not to use it at runtime.

There is a minimum threshold for INSERT...SELECT below which it chooses not to use the bulk load optimizations. There is a cost involved in setting up a bulk rowset operation, and bulk-inserting only a few rows would not result in efficient space utilization.

What can be done to improve the situation?

Use one of the many other methods (e.g. SELECT INTO) that does not have this threshold. Alternatively, you might be able to rewrite the source query in some way to boost the estimated number of rows/pages over the threshold for INSERT...SELECT.

See also Geoff's self-answer for more useful information.

Possibly interesting trivia: SET STATISTICS IO reports logical reads for the target table only when bulk loading optimizations are not used.


Postgres transactions with persistent connection

Conceptually, one connection can deal with only one transaction at a time; but you can have more than one in a single session, as far as you commit (or rollback) your transaction before you start a new one.

This would be an example that works:

CREATE TABLE
    "user" (id text, "name" text, primary key(id)) ;
CREATE TABLE
    "login" (id serial, auth_id text, user_id text references "user"(id), primary key(id));

BEGIN;
  INSERT INTO "user"(id, "name") VALUES ('ce1b346cf35493d30', 'John Doe');
  INSERT INTO login(auth_id, user_id) VALUES ('123456', 'ce1b346cf35493d30') RETURNING id;
COMMIT;

BEGIN;
  INSERT INTO "user"(id, "name") VALUES ('strangeid', 'Nick');
  INSERT INTO login(auth_id, user_id) VALUES ('56789', 'ce1b346cf35493d30') RETURNING id;
COMMIT;

Some connection tools (for instance, pgAdmin in default configuration) will have an autocommit feature (in pgAdmin, you control it by means of menu checks at "Query > AutoCommit" and "Query > AutoRollback"). If these are checked, a transaction that fails would be AutoRollbacked. If you disable these checks, the next example would behave like you indicate:

DROP TABLE IF EXISTS "user" ;
DROP TABLE IF EXISTS login ;
CREATE TABLE
    "user" (id text, "name" text, primary key(id)) ;
CREATE TABLE
    "login" (id serial, auth_id text, user_id text references "user"(id), primary key(id));

-- This transaction block will work
BEGIN;
  INSERT INTO "user"(id, "name") VALUES ('ce1b346cf35493d30', 'John Doe');
  INSERT INTO login(auth_id, user_id) VALUES ('123456', 'ce1b346cf35493d30') RETURNING id;
COMMIT;

-- This one will not. As AutoRollback is off, the transaction won't be
-- commited (because of the error) nor Rolled Back
BEGIN;
  INSERT INTO "user"(id, "name") VALUES ('strangeid', 'Nick');
  INSERT INTO login(auth_id, user_id) VALUES ('56789', 'ce1b346cf35493d30') RETURNING id;
COMMIT;

-- It will generate the following error
ERROR: current transaction is aborted, commands ignored until end of transaction block
SQL state: 25P02

The way to make it work again, is by "finishing" your transaction. As you cannot commit it, you have to roll it back:

ROLLBACK;

this now works...

BEGIN;
  INSERT INTO "user"(id, "name") VALUES ('anotherid', 'Mike');
  INSERT INTO login(auth_id, user_id) VALUES ('56789', 'ce1b346cf35493d30') RETURNING id;
COMMIT;

Your Node.js client connection framework might have an autocommit/autorollback feature that might automatically finish the job for you [don't know enough about Node.js DB clients to advise on them]. If not, you get out of the "aborted" state by just rolling back and finishing your transaction.


How to delete all records which are not referenced from other tables

I can think of no better way than doing:
DELETE FROM referenced_table AS r
WHERE NOT EXISTS (SELECT 1 FROM referencing_table_1
                  WHERE ref_id = r.id)
  AND NOT EXISTS (SELECT 1 FROM referencing_table_2
                  WHERE ref_id = r.id)
  AND ...;

This is a very unusual requirement.
If you need to do that often, you could even consider denormalizing your database:

add a column reference_count to the referenced table

define triggers that update reference_count whenever rows in the referencing tables are added, deleted or modified

Then you can simply delete the rows with reference_count = 0. Processing will be fast (if all your foreign keys are indexed!!), but you buy that at the price of slowing down all data modifications on all referencing tables.


How to model common data for different kind of objects in a relational database?

What you're asking for is a way to make your relational data model fit an arbitrary code constraint, namely: all "attached" items use a single ORM class.  Rather than tell you how you might do that, I'm going to try to explain why this is not a good idea and what you should do instead.

Code reuse is a great principle to apply to code.  Data isn't code, and relational databases shouldn't be designed primarily to facilitate code reuse.  You should design your database for data integrity not for code reuse.  Designing for data integrity means that you should treat distinct types of things in distinct ways, even if they happen to "look similar" at present.  This is what relational databases are designed and built to do.  They aren't designed to optimize for code reuse.

Furthermore, ORM code is generally not reusable, because it's meant to reflect the structure of and to operate on specific tables.  Even if you happened to have multiple tables that all looked alike structurally and even if you defined interfaces which allowed these tables to work through a common set of code, you still have the need for the data to be persisted in different tables.  There's also the matter of ORM code generators, which are just not built to look for code reuse opportunities like what you're trying to achieve.

You could conceivably develop your own ORM (I built one myself in the 1990's).  If you did this you could build it in such a way as to make it meta-data driven at runtime.  This would give you the feel of using a document database in code while storing your data in a relational database.  However, what you're really doing is mashing a square peg into a round hole.  If your application calls for a document database you'd be better off using one instead of a relational database.

The best you could (and probably should) try to achieve is to make a reusable code component of the middle tier/model code (and the front-end/view, if possible) which branches internally at the back-end/data tier to use per table ORM classes.  This lets you achieve the benefits of using relational databases, the benefits of using ORMs for persistence code, and the benefits of components in your business layer and user interface code.


If I understand what you are asking for: A given person can have multiple roles in a team but can only be on one team I would do it like this:

By putting the TeamId in the People table you enforce the fact that a Person can only be on one team.  You then have a many:many relationship using a cross join table between People and Roles.  This allows a person to have multiple roles.  By putting the email in the PeopleRole table you enforce that a person has to have a separate email for each role.  You would also want to put a unique index on the PeopleRole.Email column to enforce that all email address have to be different.

The only thing I believe I'm missing is enforcing that a team can only have one leader and one contact.  You might be able to do that by adding TeamId to the PeopleRole and then creating a filtered unique index on TeamId & RoleId WHERE the RoleIds are those that can only have one per team.  I haven't tried it though.


Nested loops estimate way too low, causes tempdb spills

I was able to get the estimates corrected by creating an index on the temp table after inserting. This solved the tempdb spills, but performance didn't improve much. Ultimately, a combination of separating the data query from the aggregation, and shuffling some other queries around to get maximum benefit of parallelization, combined to reduce the speed by 4x. Thanks everyone, especially Joe Obbish