How to provide a access to the data base if the user is not having the privilleges

With SQL Server 2008 onward local Windows Administrators are not added as SQL Server Administrators by default.

If you do not have access to any Administrative account to SQL Server then you need to start SQL Server in single user mode, in this mode local Windows Administrators can login to SQL Server. Once logged in you can create a new Administrative Account.

Refer link for step by step tutorial: SQL Server – How to connect to SQL Server when ‘sa’ account is disabled


How to evenly distribute a value over varying time periods with T-SQL?

To solve this problem you'd need to figure out how many days are recieving a budget in each relevant year. I don't have the time to create a perfectly coded answer but this should definitely get you started.

DECLARE @Budget int = 1000000
DECLARE @Startdate date = '2001-06-01'
DECLARE @Enddate date = '2003-09-30'
DECLARE @nYears int = 1 --1 is required to make sure the current year is taken into account
DECLARE @dailyBudget int = DATEDIFF(D, @startdate,@endDate)
SET @nYears += (YEAR(@endDate)-YEAR(@Startdate))

DECLARE @cnt int = 0;
DECLARE @yearBudget int;

WHILE @cnt < @nYears
BEGIN
    SET @yearBudget = @dailyBudget * (DATEDIFF(d, CAST(YEAR(@Startdate) + @cnt AS CHAR(4)) + '-01-01', CAST(YEAR(@Startdate) + @cnt AS CHAR(4)) + '-12-31') + 1 )

    IF @cnt = 0 --If this is the starting year, calculate remaining days
        SET @yearBudget = @dailyBudget * (DATEDIFF(d, @Startdate, CAST(YEAR(@Startdate) AS CHAR(4)) + '-12-31') +1)
    IF @cnt = @nYears-1 --If this is the ending year, calculate elapsed days
        SET @yearBudget = @dailyBudget * (DATEDIFF(d, --DailyBudget * days elapsed
            CAST(YEAR(@Enddate) AS CHAR(4)) + '-01-01', --get the first day of the last year
                @Enddate) +1) --Last day +1 so you get a budget for the last day as well

    PRINT @yearBudget
    SET @cnt += 1
END

The key element of this answer is the DATEDIFF function. This is used to calculate the amount of days between a startdate and enddate. The current solution will print the budget for each year. Please note that I did NOT fully test this so you'd have to manually calculate if the results are correct.


Plan A:

Create temp table with num and max(id).  400K rows
Chunk that table to do only 1000 deletes at a time; there would be a JOIN in the DELETE to the temp table.

More on Chunking for DELETE.

Plan B:

You need to keep 60% of the table, correct?  So, it may be faster to rebuild the table with only the desired rows.

CREATE TABLE new LIKE activity;
ALTER TABLE new ADD UNIQUE(num);   -- in order to catch dups
INSERT IGNORE   -- silently ignore dup nums
    INTO new SELECT * FROM activity
    ORDER BY num, id DESC;   -- same effect as MAX
RENAME TABLE activity TO old, new TO activity;
DROP TABLE old;

Plan C:

ALTER TABLE IGNORE activity
    DROP KEY num,
    ADD UNIQUE(num);

Problem (with Plan C): you cannot predict which of dup rows will be deleted.


Looks like one of the pages in movies.ibd is corrupt, so it can't read it.

I see two options here.

1) There is a script fetch_data.sh that can copy as many records as possible from a corrupt InnoDB table into a MyISAM table. Start MySQL with innodb_force_recovery=6, create a MyISAM table with same structure and use the script to copy records.

2) From the same toolkit use c_parser/stream_parser to fetch records from movies.ibd. You can't import it anyway, because it's corrupt.

Ah, and as a TwinDB author I can suggest one more option :-) - use https://recovery.twindb.com/ to recover movies.ibd. But it's going to cost $$$.


Database connections and related issues?

In general the max number of running sessions has a relation with the number of CPU cores on the database server. If you have 32 cores it does not make sense to allow more connections and expect them all to run concurrently.
Because in most of the times there will be some waiting, you could allow a few more but the database system itself also needs some CPU to work for you.
So your database connection pool should not have many more connections than there are available CPU cores to run them on.
When all CPU's are loaded, the application will have to wait for connections to return results and putting more stress on the database will slowdown the application more.
The optimal connection pool size can be smaller but depends on the transaction length and the number of concurrent transactions. If every transaction returns it's connection to the pool the system can handle a lot of work.


Encrypted Query locking server from time to time

Looking at what you posted, it's most likely an Agent job trying to insert data into a table in msdb clashing with an Agent job trying to select data.

This can take a long time, especially if you've never cleaned up Agent job history. These tables can get quite large and contentious on old or busy servers.

Have a look here (full disclosure, it's my company's blog): https://www.brentozar.com/blitz/msdb-history-not-purged/


Why do maintenance plans exist as a feature?

Underlying SSIS task that do not require SSIS to be installed to run, nor require you to edit via BIDS or SSDT-BI.

They are there more or less for easy deployment of basic database maintenance that does not require much knowledge to build. Is it something that fits any environment? I would say no, it is mostly for the one man/woman shops that just need something up and running quickly and is sufficient.

Now that is not to say in large environments maintenance plans don't have a purpose, just find they may not used as often. You might consider them a stepping stone to SSIS packages.


When using xtrabackup the binary logs aren't copied per file (but in one binlogfile).
You need to restore this differently, see docs: Preparing a Full Backup with innobackupex.

Or, with more extensive explanations:

Percona documentation - Prepare with xtrabackup
Percona documentation - Restore with xtrabackup

In relation to your database not starting after restore, you might want to paste your my.cnf and server log hostname.err.


SSIS deployment on databases part of AAG

You have 2 options.

Make the user a member of ssis_admin role which of course give more privilege than you intend to.

  This role provides full administrative access to the SSIS Catalog
  database.

You can give permission at folder level under integration services catalogs.  Here you have a better control, but you have to do this for each folder.
Details explained here: SSIS Catalog


Dividing values from a single column into 2 based on a rule

Use a CASE statement in the column list to check if ParentID is 1 or null.

SQL Fiddle

PostgreSQL 9.3 Schema Setup:

create table YourTable
(
    Name varchar(10),
    ItemID int,
    ParentID int
 );

 insert into YourTable(Name, ItemID, ParentID) values('A', 1, null);
 insert into YourTable(Name, ItemID, ParentID) values('B', 2, null);
 insert into YourTable(Name, ItemID, ParentID) values('C', 3, null);
 insert into YourTable(Name, ItemID, ParentID) values('D', 4, null);
 insert into YourTable(Name, ItemID, ParentID) values('A1', 5, 1);
 insert into YourTable(Name, ItemID, ParentID) values('A2', 6, 1);
 insert into YourTable(Name, ItemID, ParentID) values('A3', 7, 1);
 insert into YourTable(Name, ItemID, ParentID) values('B1', 8, 2);
 insert into YourTable(Name, ItemID, ParentID) values('B2', 9, 2);
 insert into YourTable(Name, ItemID, ParentID) values('B3', 10, 2);

Query 1:

select case when ParentID is null then T.Name end as Column1,
       case when ParentID = 1 then T.Name end as Column2
from YourTable as T;

Results:

| column1 | column2 |
|---------|---------|
|       A |  (null) |
|       B |  (null) |
|       C |  (null) |
|       D |  (null) |
|  (null) |      A1 |
|  (null) |      A2 |
|  (null) |      A3 |
|  (null) |  (null) |
|  (null) |  (null) |
|  (null) |  (null) |


What's worse, too many entities or too many tables?

The number of entities is driven by the functional specs on the database.  You discover entities by analyzing the subject matter.  This is different from inventing entities because it feels good.  You determine whether any given subject matter entity is relevant to the database by looking at the scope of the database, per the requirements.

The number of tables is driven by the database design.  You create tables as needed to fulfill the design goals.  You are typically pursuing multiple goals:  normalization, simplicity, flexibility, and speed, among others.  Typically each entity will have a table of its own, and there will be extra tables for relationships that are not simple enough to just add FKs to existing tables.

The difference between analysis and design is crucial to all projects beyond simple exercises.


How does MySQL return a result set from a stored procedure?

Each SELECT statement that does not insert into a table or a variable will produce a result set.

If you want your stored procedure to return only one result set, make sure that you only have one SELECT statement. If you have other SELECT statements, make sure that they insert results into a table or variable.

UPDATE
Here are examples of stored procedures.

This stored procedure would return one result set:

DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name()
BEGIN
    DECLARE local_variable_name INT;

    SELECT column_name FROM table_1 LIMIT 1 INTO local_variable_name;

    SELECT * FROM table_1;
END;;
DELIMITER ;

This stored procedure would return two result sets:

DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name()
BEGIN
    DECLARE local_variable_name INT;

    SELECT column_name FROM table_1 LIMIT 1 INTO local_variable_name;

    SELECT * FROM table_1;

    SELECT * FROM table_2;
END;;
DELIMITER ;


Root question

Why would using the Project Deployment Model provide the lowest cost of logging assuming the desire is to have

Centralized
automated
SQL Server based

Answer

Because it's built in. The new project model automatically logs at the Basic level when executed on the server. For completeness, your available logging options are None, Basic, Performance, Verbose. Basic is going to have everything you need for a standard run. If it fails, you will know information about the failure - what failed and why. You'll have start and stop times for all your executables and as the first sentence mentions, you don't have to do a single thing to get it.

Prior to this, a new package is created, goes through all the SOX/SAS-70 deployment checks and what not and it blows up in production. Look at the logs to see what happened and oh snap, nobody turned logging on. The package must then be edited which requires running back through all the approval and testing process and generally is a PITA.

Finally, if you add on OnError event handler to the package great, but now what? You will need to write your own logic, maybe a stored proc, whatever, to do something with the error. That's probably not going to meet the automated requirement.

dtexecui is just a gui for dtexec which is used whether you're using the project deployment or the classic package deployment model. It's just a matter of whether you indicate a package lives inside an ispac/ssisdb or not.

Final thoughts

Study up on the Project Deployment Model. Really dig in and play through how all this works. The test is trying to ascertain whether you understand what comes part and parcel with it so you can deliver the most effective solutions.


Scheduled Database Backup job fails due to lack of disk space

(Can post this as comment due to lack of rep)

This link could be helpful to you:
http://www.mssqltips.com/sqlservertip/2531/sql-server-compressed-backups-disk-space-needs/


Using multiple PIVOTs JOINs and GROUP BYs for advanced report generation

I've managed to get the required result by dropping the second PIVOT and AttributeId GROUP BY argument and instead using a LEFT JOIN on the attributes.

This answer was provided by visahk16 on the SQL Team forums:

SELECT
    *
    FROM
    (
    SELECT
    T.UserId,
    T.RoleId,T.GeographyId,T.CategoryId,
    TA.[-1],TA.[-2],TA.[-3],TA.[-4],TA.[-5],
    TDA.DutyActivityId AS TypeId,
    SUM(TDA.PeriodMinutes) AS Total

    FROM
    Timesheet AS T

    LEFT JOIN
    (SELECT TimesheetId,
     SUM(CASE WHEN AttributeId ='1' THEN -1 ELSE 0 END) AS [-1],
     SUM(CASE WHEN AttributeId ='2' THEN -1 ELSE 0 END) AS [-2],
...
     SUM(CASE WHEN AttributeId ='5' THEN -1 ELSE 0 END) AS [-5]
     FROM TimesheetAttribute
     GROUP BY TimesheetId
     )AS TA ON
    TA.TimesheetId = T.TimesheetId

    LEFT JOIN
    TimesheetDutyActivity AS TDA ON
    TDA.TimesheetId = T.TimesheetId

    GROUP BY
    T.UserId,
    AttributeId,
    T.RoleId,T.GeographyId,T.CategoryId,
    TDA.DutyActivityId
    ) AS SourceTable

    /* PIVOT against the known DutyActivityIds */
    PIVOT
    (
    SUM(Total)
    FOR TypeId IN ([1],[2],[3],[4],[5])
    )
    AS PivotType

    ORDER BY UserId ASC


Cannot Start mySQL Daemon with Error "mysqld from pid file ... ended"

InnoDB: Unable to lock ./ibdata1, error: 11

Perhaps another copy of mysqld is still running?

Only run it as a service.


mongorestore to --oplogReplay or not to --oplogReplay?

An OpLog is like a transaction log, thus if you had a 'full' backup of the system but wanted to get incremental data after the full backup was taken you can replay the opslog to get a 'point in time' restore of the database.  It let's you have more control on being able to address at exactly what point you want to restore everything.  Let's say someone dropped a critical collection at 12:05:23.  You could do a opslog replay up to 12:05:22 for example.


How to update table1 column from table3, with matching id from table2

Your backticks indicate MySQL, where this syntax works:

UPDATE customer AS c
JOIN   garages  AS g ON g.owner_id = c.id
JOIN   the_cars AS t ON t.car_id   = g.the_car_id
SET    c.car  = t.car
WHERE  c.car IS NULL;

In Postgres you would use a different UPDATE syntax:

UPDATE customer AS c
SET    car  = t.car
FROM   garages  AS g
JOIN   the_cars AS t ON t.car_id = g.the_car_id
WHERE  c.car IS NULL
AND    g.owner_id = c.id;

The FROM clause is not standard SQL.

You have to make sure that the query finds at most one car per customer, or the result is not deterministic.


How to denormalize many-to-many relation without nested select?

Nothing forbids you from adding another join, to games. Then you can add the games.game column in the SELECT list:

select
  s.sid, s.name, xy.type, h.nature, g.game
from
  students s
  inner join sex xy on s.fk_type = xy.ROWID
  inner join house h on s.fk_house = h.ROWID
  inner join likes l on l.fk_student = s.sid
  left join games g on g.ROWID = l.fk_game
order by
  s.sid,
  l.fk_game;


How was the "rotation" done?  Normally you should never need to "rotate the binlog".  1.1G is normal when max_binlog_size is 1G; it overshoots, then automatically rotates.

You could change that setting to 100M with no harm.  That will cause for frequent rotation, and each file will be about 100MB.


Avoid foreign keys on an 800-GB database?

...a foreign key is another index that the database has to maintain

I think your colleague may be laboring under the idea that columns containing foreign keys must be indexed, which isn't always the case.  (See ypercube's comment below.)  Foreign keys aren't anything other than values that the schema gives a special meaning.  The decision to index such a column is the same as for any other: if it will frequently be the subject of selection criteria (i.e., part of a WHERE clause), index it according your DBMS's preferred practices.

Indexes come into play in the parent table's primary key column because those columns meet the criteria described above.  More on that in a minute.

  ...and takes more space.

Almost everything in this field is a space/time trade-off, and indexes are one of them: you consume more storage during writing to consume less time during reading.  It would be very hard to argue that adding an index to a table doesn't consume additional space, because it does.  Making those kinds of decisions without the context of the whole schema is failing to see the forest for the trees.

As Codd described it, normalization was a very early application of what would become the DRY principle.  Non-normalized data is inherently repetitive; normalized data is not.  References in child tables are (necessarily) repetitive but have the benefit of being smaller than the original.  (Normalization is more for preserving integrity rather than space, but that's another discussion.)

For example, a million copies of the string 1600 Pennsylvania Avenue stored in a million rows of a non-normalized table occupy 25 MB.  Move that string to a separate table of addresses (where it's stored exactly once), refer to it a million times using an eight-byte, numeric foreign key and the whole mess occupies 8 MB plus 25 bytes for the actual string.  That's space savings of about 67%.

This foreign-key relationship may work better performance-wise if the table of addresses (parent) is indexed by its primary key.  That index is a lot smaller because the N:1 nature of normalization means that, on average, there will be fewer rows to index and the index won't need updating as often.

It's hard to say without seeing the schema, but a lack of normalization may be the reason the database grew to 800 GB in the first place.

  Just how much more space can an index take?

That's a hard question to answer in general terms.  The idea behind most forms of indexing is to allow the traversal of a small amount of data to locate something in a large corpus.  A side effect is that the data structures used to represent it tend to end up much smaller.  That said, a poorly-designed or poorly-selected mechanism combined with pathologically-bad data can lead to indexes that are much larger than the original data.  The opposite, more-usual case is that the index is a small fraction.  I've done a lot of work with full-text indexers and find they tend to chew up less than 5% of the original data.  But that's just for my use cases and may not apply to anyone else's.


Why does "DELETE WHERE (VARCHAR)=0" execute successfully?

When a string (VARCHAR) is compared to a number (0), any digits at the beginning of the string are converted to a number.  If there are no digits (eg, 'Mike'), the numeric value is 0.

WHERE 'Mike' = 0 -- succeeds
WHERE 'Mike' = '0' -- fails

Coding like that cannot be automatically prevented.  This is part of debugging your application.

Similarly, if you use an INT in a string context (eg, CONCAT()), the INT value will be converted to a string.


InnoDB storage for multiple BIT(M) columns?

that has nothing to do with smart 4 columns are 4 columns. so it woud use 4 byte
Save your bit patterns as Integer.
Depending what you need you can use tinyint 1 Byte (8bits) till BIGINT 8 Byte (64 bits)
If you have or want to save space, you have no better solution.
if you want to store more bit you have to switch to BLOB and use that
For example

2 is 00000010
3 is 00000011

Any language can transform an integer too a byte array and back again


Several stays: many-to-many relationship

Based on your diagram above what you have is a one to many, not a many to many relationship.  You have your Researcher table and your Stay table.  If you add the researcher id in the stay table to the primary key you have a relationship of:

Researcher.ResearcherId -> Stay.ResearcherId

Where a given researcher can have multiple stays.  For example

Researcher John Smith has an Id of 2.

In the stay table you would have

From       Till       ResearcherId
1/1/2013   1/5/2013   2
2/6/2013   3/1/2013   2

etc


Prevent duplicates in MySql without using unique constraint

Here's a complete test case, annotated:
CREATE TABLE t_rearr (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    priority INT NOT NULL,
    PRIMARY KEY (id),
    UNIQUE(priority)
) ENGINE=InnoDB;

INSERT INTO t_rearr (priority) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

BEGIN;

SELECT GROUP_CONCAT(id ORDER BY priority) FROM t_rearr FOR UPDATE;
-- Expect 1,2,3,4,5,6,7,8,9,10
-- Note the FOR UPDATE

-- 3 steps to swap:
-- First, move out of way (this assumes 0 is not a valid priority)
UPDATE t_rearr SET priority =  0 WHERE priority =  5;

UPDATE t_rearr SET priority =  5 WHERE priority = 10;

UPDATE t_rearr SET priority = 10 WHERE priority =  0;

COMMIT;

SELECT GROUP_CONCAT(id ORDER BY priority) FROM t_rearr;
# Expect 1,2,3,4,10,6,7,8,9,5


If a Parent Task and Subtask are essentially identical, save their rank in a hierarchy, then yes, a single table is appropriate. You can use a nullable foreign key to the primary key in the same table like this:

create table tasks (
  task_id bigserial primary key,
  subject text,
  ...,
  parent_task_id bigint null references tasks(task_id)
);

If your database server does Recursive Common Table Expressions (example PostgreSQL), then use those for querying. Here are some examples.


Microsoft SQL Server MERGE deadlock issue

I think what you are talking about is not "deadlock issue", it's not even blocking issue.

Full backup locks nothing, but when it happens you have intesive I/O operations, it's just increasing server load. Full backup should be done with the less possible activity on the server, but your activity is not even readonly, it's data modification. If your db is in simple recovery model, making merge during full backup means fully logging, so you have more writelog on inserts.

Update statistics really can deadlock with merge when it reads indexes in opposite order, I saw the cases. But it's surely not your case:

  The system task Deadlock Monitor wakes up every five seconds and
  checks if there  are any deadlocks in the system. When a deadlock is
  detected, SQL Server rolls back one  of the transactions with the
  error 1205. That releases all locks held in that transaction  and
  allows the other sessions to continue.

Expert SQL Server Transactions and Locking: Concurrency Internals for SQL Server Practitioners
By Dmitri Korotkevitch

So when you say that your task completes in 10 minutes it's definitely not a case of a deadlock: even if it could be UPDATE STATISTICS to be rollbacked, it happened in some seconds, then it was killed and your merge continued as usual.

To find out the exact cause of slowness you should monitor sys.dm_os_waiting_tasks during the execution to detect for what your merge is waiting for.


Efficient approach to Query DB with data in text file

Every server is different, but you might be surprised how quickly you can query through a million rows. Are you inserting the data into a table, or are you just trying to query against it?

If you're just trying to query against the file but don't need to store the data in the database, you could insert it into a Temp Table and run your queries against it.

If you're inserting the file into a table I would go with your first option. One query is going to cause less load on the server than running 1 million queries (if I understood you correctly). Without knowing your query I can't help with performance/load. It may not be applicable, but a UNION is typically faster than a JOIN. Just something else to keep in mind.

(Sorry for the answer and not a comment, I don't have the rep yet)


Sorry, but you have designed yourself into a corner.

Strike 1:  EAV (key-value) schema sucks when the tables get large.  Further discussion and possible workarounds:  http://mysql.rjweb.org/doc.php/eav

Strike 2:  GUIDs (and UUIDs, MD5s, etc) suck as KEYs because they are so random.  Further discussion and a possible (but unlikely) workaround:  http://mysql.rjweb.org/doc.php/uuid

Your best choice may be to keep buying RAM so that everything can be cached.  And/or switch from spinning drives to SSDs.  Of course, neither hardware solution scales well.

Software solution involves a major rework of the schema and application code.  That is beyond the scope of this forum, other than to point you at those two blogs.

As for your specific questions:

  On the explain I always get a “Using where; Using index; Using temporary; Using filesort” on the Person table.  Why does MySql need to sort the Person table before joining to PersonData?

What you are seeing is bogus.  EXPLAIN likes to put those flags on the first line, even though they might apply to some other line.  If you have a new enough version of MySQL, do EXPLAIN FORMAT=JSON SELECT ...; it should spell out things correctly.

  Why does MySql generally decline to use the PersonID/FieldID/Value index? Is there a better index I could use? I assumed I needed the PersonID in the index to help with the join.

I need to see SHOW CREATE TABLE.  But here is a guess...  If the WHERE clause matches more than ~20%, it will choose to do a table scan because it is likely to be faster.  Another guess:  You have PRIMARY KEY(person, field) and INDEX(person, field, value)??  And you are using InnoDB??  The PK includes all columns and is sorted by (person, field).  That's identical to the secondary key!  So, the optimizer may as well flip a coin to decide between them.

Anyway, it says "Using index"; that about as efficient as it gets.


Which DBMS are fast enough for an online game (few thousand players)?

This is a really tough question to answer. You can take the fastest database platform in the world, design a horrible schema, write a crappy application around it, and then be tempted to blame the database platform. At the same time, you could take the free SQL Server Express and, with the right design, application logic, and an approach at sensible scalability (e.g. scaling out reads by data caching, etc), you could write an application that handles 1000s of users no problem.

Do I believe SQL Server can handle 1000s of users? Absolutely. Do I believe Oracle and DB2 can do so as well? Certainly. MySQL? Not sure, not enough experience there. Access? Probably not a wise choice at all. If you are familiar with SQL Server, then I suggest that is the route you consider, just keep in mind that your choice of RDBMS will not in and of itself dictate success or failure.


How can I prevent having inconsistent data under isolation level serializable in Oracle SQL?

You're not quite right in what you expect.

When T2 issues its UPDATE, it has the sum, 95, in its local variable 's'.  That is, the T2 query is complete and the T2 'if' statement happens before T1 commits and because the sum is greater than 90 it proceeds to the update.

There is no reason why the local variable 's' would be updated to track the T1 commit.

I think that you should experiment with using SELECT FOR UPDATE NOWAIT in a loop, e.g.

<<do_tr3quart1sta_thing>> declare
   totqty number;
   lock_nowait_exception exception;
   pragma exception_init (lock_nowait_exception, -54);
begin

   <<try_for_lock>> loop
       begin
           select sum (qty) into totqty
           from instores where prodid = 'p1'
           FOR UPDATE OF qty;
           exit try_for_lock if sql%rowcount > 0;
       exception
           when lock_nowait_exception then
               exit try_for_lock;
       end;
       dbms_lock.sleep (2); -- for heaven's sake please don't run these in a tight spin!
   end loop try_for_lock;

   if totqty > 90
   then
       update instores
       set qty = qty - 30
       where prodid = 'p1'
         and storeid = 's2';
   end if;
   commit; -- release the lock!
end do_tr3quart1sta_thing;
/

Don't have experimentation time - I think the NOWAIT will raise exception -54 if it's locked, but in my usual belt-and-suspenders way I'm allowing for the possibility that the SELECT FOR UPDATE will just return zero rows.

Also note that this will let any other exception raised bubble through and blast out.

How in the world can you get T2 to block in this case?  You could run this thing hundreds of thousands of times a second!


I need to create a process where anything older than six months we would write to an archive table and delete the records from the inventory table

If you have the ability to change the data model and the structure of this table, partitioning by date is the best way to handle maintenance like this. A partitioned table is a just a special type of table that is divided into segments, called partitions, that make it easier to manage individual segments. If you map each partition to a date, then dropping or moving data for a specific data is as easy as a truncate or a partition switch.
There is some documentation here, and some examples that could be helpful.
If you're stuck with this table structure and still need to do this operation, this can become difficult if the number of rows being deleted is substantial, so a few comments:

A DELETE is a fully logged operation so if that's touching many rows, you could end up filling up SQL's transaction log. In those cases, batching the deletes to handle the top X rows at a time can help with committing smaller transactions.
If your database is in the SIMPLE recovery model, it's sometimes more performant to just insert the rows you want to keep into a new table and use that as  means to delete rows. That insert can be minimally logged using the WITH (TABLOCK) hint and can be more performant than a fully logged DELETE depending on the volume of data.
It's worth mentioning you won't encounter these issues if your table is partitioned and can truncate individual partitions.


Creating Linked Server - Impersonation of Windows Authentication

Check to see if service account on source server is trusted for delegation. If it is not, enable it. You can identify which account needs this setting with the following query

SELECT servicename,service_account FROM sys.dm_server_services WHERE filename LIKE '%sqlservr.exe%';

you can do so via powershell

Get-ADUser serviceaccount -Properties trustedfordelegation|select trustedfordelegation
Set-ADUser -Identity serviceaccount -TrustedForDelegation $true

or with AD users and computers: on the properties of service account, delegation tab.

A restart of SQL Server may be required after making this change.


Postgresql / split one record on two rows depending of two fields

SELECT name, email FROM users
UNION
SELECT name, email2 FROM users;

email and email2 have to be of the same type


CREATE TABLE not issued on other masters

If you have MultiMaster Replication, every DB server needs this
[mysqld]
log-slave-updates

This will tell a Slave to log completed SQL commands from the Relay Logs into the Slave's binary logs. Just add that option to every Slave and restart mysql.
If this does not work, you may need to explain more things to us.
Here are my other posts where I suggested using log-slave-updates

Oct 09, 2013 : Multi-master and multi slave
Dec 05, 2012 : MySQL Slave Relay Logging but not logging Binary Log
May 29, 2012 : mysqld-multi with first DB as Slave and second DB as Master
May 07, 2012 : Setting Circular Replication in mysql

Give it a Try !!!


INSERT failed in SQL Job because of incorrect SET options 'QUOTED_IDENTIFIER'

It's not the way the table was created, it's the options your query runs with.

Agent does indeed run with the wrong settings.

SELECT *
FROM   sys.dm_exec_sessions
WHERE  is_user_process = 1
AND    (   ansi_nulls = 0
      OR   ansi_padding = 0
      OR   ansi_warnings = 0
      OR   arithabort = 0
      OR   concat_null_yields_null = 0
      OR   quoted_identifier = 0 );

The simple fix would be to use the correct options at the beginning of the job step that does the insert.

For instance, if I create this table, an insert works just fine:

USE master

CREATE TABLE dbo.whatever (id INT, thing AS id * 2);

INSERT dbo.whatever ( id )
VALUES ( 0 )

But from an Agent job step it fails, unless I put this first:

SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;

For reference: Create Indexed Views


mariadb slow to response due to high query

3500 -- is that Max_used_connections? If so, that is terrible. Imagine 3500 people, each with a grocery cart, in a grocery store. Imagine them trying to move through the aisles to pick up food items. It is a mess; essentially it is too crowded for anyone to get anything done. The same thing happens with MySQL/MariaDB.  In this case it is mutexes, and other things.

Put another way, each process gets 2/3500 of the CPU time.  That is, practically none.  So, each connection takes really long to get anything finished.

So, the cure is to find the slow queries and speed them up.  See:  http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

(Use long_query_time=1 so you can catch some of the shorter, but frequent, queries.)


insert all record into table1 from table2 using where like clause

If you want all rows from table1 to be inserted into table2 (and only some modifications in column t000) then you can't use WHERE. You could use a CASE expression:

INSERT INTO table2
  ( ID, Metrics, t000 )
SELECT
    ID, Metrics, CASE WHEN t000 LIKE '%BTC%' THEN t000 ELSE 'N/A' END
FROM
    table1 ;

If you want only rows that have t000 LIKE '%BTC%' or t1000 is NULL then yes, you need a WHERE clause::

INSERT INTO table2
  ( ID, Metrics, t000 )
SELECT
    ID, Metrics, COALESCE(t000, 'N/A')
FROM
    table1
WHERE
  ( t000 LIKE '%BTC%' OR t1000 IS NULL ) ;


Is it common to mark rows with a batch number? Yes it is. In accounting contexts I have seen this used widely for audit and reconciliation purposes.

Is having a separate table to store batch-related values a good idea? Yes it is. Good practice in relational DB design is to have one table per entity type. Since your problem domain recognises "batch" as a concept, with its own values, it should have a table.

Should this table use an autoincrement column? Well, this is very much an implementation detail. Does your problem domain allow for gaps, negatives, re-use after delete, out of order values etc. etc? Does your DBMS software guarantee the necessary features? If so then, sure, go for it. If not, write your own generator with the needed guarantees.

Will denormalising timestamp from the "batch" table to the "data" table improve performance? Can't say. It will make the data table wider, which should cause more IO. There may be enough spare space in each page that this doesn't happen, however. If all the data, or at least the woking set, fits in memory, this consideration is void.

If there are good indexes in place and they are being used I'd guess denormalisation won't change much. You could try rewriting the query. Sometimes if the optimizer starts from a different place it ends up with a different plan. Put select..from a group by b_id in a subquery, then join that to b to get the timestamp.

You need to understand where the time is consumed and address that. And of course test, test, test.


Can I restore the defult SQL Server Public Role?

Well, this would be the script to restore the connection permissions to the defaults.

use [master]
GO
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO [public] AS [sa]
GRANT CONNECT ON ENDPOINT::[TSQL Default VIA] TO [public] AS [sa]
GRANT CONNECT ON ENDPOINT::[TSQL Local Machine] TO [public] AS [sa]
GRANT CONNECT ON ENDPOINT::[TSQL Named Pipes] TO [public] AS [sa]

If that doesn't work, try stopping SQL Server, starting it in single user mode ("sqlservr.exe -m" from an administrator command prompt), connecting with sqlcmd.exe (which will default to the local default instance with Windows authentication), and running the script that way.


In PostgreSQL is the row ordering preserved in functions and CTEs?

In SELECT statements the ordering of the returned rows is not guaranteed if the ORDER BY clause is not specified. This is true for all tables, simple or complicated queries.
Now, having that as basis, we should consider that Postgres has implemented CTEs in a peculiar way. They are always materialized (see: PostgreSQL’s CTEs are optimisation fences).
That doesn't mean that you should rely on this. When using a CTE, like in the first query, an ORDER BY should be added as well when referencing the CTE, as the ORDER BY inside the CTE may be removed (I'm not saying that it will always be removed as my tests show that a sort is performed. But it may be removed in a future optimizer change, as an ORDER BY without LIMIT is redundant):
WITH ordered AS ( SELECT * FROM table1 ORDER BY col1 )    -- redundant ORDER BY
SELECT sum(col2) result FROM
generate_series(0,50) nr,
LATERAL (SELECT * FROM ordered ORDER BY col1        -- ORDER BY added
         LIMIT 100 OFFSET nr*100) a
GROUP BY nr ORDER BY nr;

Update, from Craig Ringer, after a comment I made in his above linked  blog post:

It’s like quite a few other cases – right now PostgreSQL will always return the rows in the order they’re output by the CTE, but you shouldn’t technically rely on it. Who knows what future features could change that?
Unfortunately IIRC it’s also not smart enough to recognise that the rows are ordered correctly if you add another ORDER BY in the outer query. (Haven’t tested, but pretty sure). So it’s not necessarily free to do it the “right” way.

So, if you want the results ordered in a specific ordering, add an ORDER BY in the final SELECT. You could get away without it now (and you might get some slight performance gain) but there is no guarantee that this will not change in a future release.
Another reason for explicitly using ORDER BY is that you may not be always maintaining the code you write now. Another developer may try to reorganize the query without CTEs, using derived tables or LATERAL joins. They would need to know that your query relies in the order provided by the CTE so it needs to be either used in the final SELECT or commented or documented somewhere.

Now, another way to write the query would be adding a LIMIT in the CTE and using ROW_NUMBER():
WITH ordered AS
    ( SELECT col2, (ROW_NUMBER() OVER (ORDER BY col1) - 1) / 100 AS nr
      FROM table1
      ORDER BY col1 LIMIT 5100 )
SELECT n.nr, sum(o.col2) AS result
FROM generate_series(0, 50) AS n (nr)
    LEFT JOIN ordered AS o
      ON n.nr = o.nr
GROUP BY n.nr
ORDER BY n.nr ;

This way, the CTE will only be materialized for the 5100 rows you want and not for all (possibly millions) rows of the table. Even better if there is an index on (col1, col2). You could even remove the generate_series() and/or remove the CTE:
SELECT o.nr, sum(o.col2) AS result
FROM
    ( SELECT col2, (ROW_NUMBER() OVER (ORDER BY col1) - 1) / 100 AS nr
      FROM table1
      ORDER BY col1 LIMIT 5100
    ) AS o
GROUP BY o.nr
ORDER BY o.nr ;


Migrating a maintenance plan from ms sql 2008 to 2016

It might not be the exact answer you're looking for, but consider using jobs instead of maintenance plans. Ola hallengren has a full (free) maintenance solution that you can download and deploy on your server. ola.hallengren.com It's used by a lot of DBAs, and has been around for years.

All you have to do is deploy the scripts and add some schedules, you can also fine tune everything to fit your needs. There's code examples and options on the Ola site.

A big benefit you get from this, is that you can simply script out everything, and deploy it on any server you want, without much hassle.

It might be worth your time checking this out.


SQL Replication using Two Availability Group

Sure. The docs have plenty of detail on how, and specific details that might affect your implementation.
I'd recommend configuring your distributor as a third, dedicated AG (assuming 2017+).


To setup the default Time Zone, you can edit the global variable system_time_zone (SET GLOBAL). You most likely want to change that on the configuration file so it persists after a reboot.

Timezones can be numerical or can use alias if setup properly.

To setup a client custom timezone, the right way is to SET SESSION time_zone ='<your timezone>';

Details at https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html

Most connection pools will destroy or cleanup the connection after usage and reset to the default, in any case, if they can vary per client, you want to set those up after each connection by running the SET SESSION command above.

Having said that, I would strongly recommend to handle timezones on application (presentation) layer and work only in a single timezone on server side (on UTC, preferably, or the local timezone only). While MySQL support is not bad, it can be confusing when handling multiple timezones (a 23:59:60 second, daylight saving changes, etc.). It may take a hard one-time conversion but it will be worth in the long term.


Mongodb: too many namespaces raise on setProfiling

You might have missed this little bit of the nsSize docs (highlighting mine):

  Use this setting to control size for newly created namespace files.
  This option has no impact on existing files

So, you can't just change the parameter and increase the number of collections for an existing database - that will still be at the old value.  The setting actually alters the size of the <dbname>.ns file when it is created - if that is already in existence (for an existing database), then it stays at its original size (default 16MiB) until you cause it to be recreated.

If you are running a replica set, then the best way to cause that recreation to happen is to shutdown a secondary, make sure it will restart with the new nsSize parameter, and then wipe its data files and allow it to sync from scratch.  Since that will include creating a new .ns file it will pick up the setting and create the larger one.  Then repeat for all secondaries before finally stepping down the primary and doing it there.  Once complete you will have the setting on all nodes.

If you don't have a replica set, then I think your only options are either:

Running a repair, which will mean down time and you will need 2x your data size free at least, probably more since you are now creating 2GiB namespace files that were previously 16MB
Copying all the data to a newly created database to take advantage of the new namespace sizing

Neither of those options are going to be particularly quick, so hopefully this is a replica set and not a standalone instance.

You could also look to clean up some of the collections in that database to give yourself some head room and time to sort it out.  Are you actually using all 24000 collections, can some be pruned?


Can I tell if a stored procedure has executed against a particular database?

Starting from SQL Server 2008,use can sys.dm_exec_procedure_stats which gives you the status  on when was the stored procedure last executed

select db_name(database_id) as dbname,last_execution_time
from sys.dm_exec_procedure_stats


Are there relations that cannot be normalized to second normal form (2NF)?

Note that the normalization of a relation is an operation that in general is performed to eliminate or reduce anomalies like redundancy, while in general preserving the meaning of the data, and more formally, preserving two essential properties: the dependencies holding on the original data, and the data itself (through the concept of lossless decomposition).

So, the normalization decomposes a relation in a set of relations that are in a so-called Normal Form, and the normal forms used in a decomposition are usually the Boyce-Codd Normal Form, the Third Normal Form, or even higher level ones. For these decompositions, there are formal algorithms that can be used, and that guarantees a set of properties, like being lossless and  preserving the dependency (not in all cases, for instance it is known that certain relations cannot be decomposed in BCNF without the loss of some dependency).

Note that the second normal form is not used in practice, has only an historical interest, and there are no published formal algorithm since it is more weaker that the 3NF and the BCNF.

All these topics are discussed in detail in any good book on databases, and you can find even some of them free on line.

In your example, assuming that the dependencies that you shown are a cover of all the dependencies of the relation, the decomposition of R in the the relations R1(CE) and R2(ABD) does not preserves the functional dependencies (E->A and C->B are lost), and is not lossless.

A decomposition which preserves data and dependencies, and that can be found both with the synthesis algorithm to produce the 3NF, and with the analysis algorithm to produce the BCNF is the following (each relation schema is followed by a cover of the dependencies holding in it):

R1(A B D) {AB -> D}

R2(B C) {C -> B}

R3(A E) {E -> A}

R4(C E) {}

where each schema is in BCNF.

Of course, since a schema in BCNF is also in 2NF, the previous decomposition also respects the Second Normal Form. This answers to your original question: since each relation can be normalized at least in 3NF without loss of data or dependencies, each relation can be normalized at least in 2NF.


SQL Server 2008 Replication - Fixed Overhead of Replication?

I'll throw an answer. The physical overhead of a new replication should not be too big, on an existing replicator. The more difficult part of a replication is the configuration and the monitoring.

You said that right now you have replication only to the reporting server, and you actually "...like to have replicated to our various production databases around the world", so you'll have subscribers all over the world for this new database.

This means you'll have to establish many physical connection between the home server and those new ones (firewall, permissions, SQL Server installation ..etc). You'll have new replications to monitor, new jobs to check, new logs to verify...etc.

In the case of a small database I'd go with a simpler case of copying it's data: just copy the backup over the wire and restore in the new places, use log shipping, anything that would not require cumbersome monitoring and configuration.


You could utilize the binary log method by adding a init_connect to log the user into a table*.

CREATE TABLE admin.connections (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    connect_time DATETIME NOT NULL,
    user_host VARCHAR(50) NOT NULL,
    connection_id INT UNSIGNED NOT NULL,
    INDEX idx_connect_time_user_host (connect_time, user_host)
);

SET GLOBAL init_connect = "INSERT INTO admin.connections (connect_time, user_host, connection_id)
     VALUES (NOW(), CURRENT_USER(), CONNECTION_ID());";

Some caveats:

Most importantly, this will only work for users without the SUPER privilege, since the init_connect is bypassed for those users.
Make sure all your users have INSERT privilege into the admin.connections table

You can then use the values in admin.connections to match up with the binary_log threadID and timestamp

*This idea was taken from here and I haven't personally tried it.

Originally this had a unique index on connect_time/user_host. But this would cause issues with same user connecting in same second. If that is not a problem in your scenario, leave it that way. If it is an issue, just make it a normal index.


Can't DROP GROUP, cannot be dropped because some objects depend on it

What I found is that in order to drop a group you have to revoke all privileges that the group might have been assigned. A group can have default privileges, usage on schema, and grants on tables. I used following 5 statements and then was able to drop the group.

--Drop All users from the group
alter group <groupname> drop user <username>;
--REVOKE usage on the group
REVOKE ALL ON SCHEMA <schema1>,<schema2>,<schema3>,<schema4>,<schema5>,public
    FROM group <groupname>;
--REVOKE grants on tables
REVOKE ALL ON ALL TABLES IN SCHEMA <schema1>,<schema2>,<schema3>,<schema4>,<schema5>,public
    FROM group <groupname>;
--REVOKE Default privileges on TABLES
ALTER DEFAULT PRIVILEGES for USER <username> IN SCHEMA <schema1>,<schema2>,<schema3>,<schema4>,<schema5>,public
    REVOKE ALL ON TABLES FROM group <groupname>;
--REVOKE Default privileges on FUNCTIONS if any
ALTER DEFAULT PRIVILEGES for USER <username> IN SCHEMA <schema1>,<schema2>,<schema3>,<schema4>,<schema5>,public
    REVOKE ALL ON FUNCTIONS FROM group <groupname>;
--drop the group finally
drop group <groupname>;


dependant subquery processed despite Extra Impossible WHERE noticed after reading const tables

Look at the Handler counts to see if it is actually touching any rows:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts

And use EXPLAIN FORMAT=JSON SELECT ... to get more details.

Anyway, since it is a DEPENDENT subquery, it will be asked to run as often as needed.  But that is zero times.

Also, EXPLAIN, is not perfect.  The subquery did not get 4 rows; EXPLAIN is estimating that it will touch 4 rows each time it the subquery is invoked.  Again, 0*4 = 0.


No connection could be made because the target machine actively refused it. MYSQL work bench?

You should check mysql user permission, specially the host field.
Mysql documentation


Sounds like a "many:many" relationship.

See my tips on writing an optimal many:many table.

I assume you have a PRIMARY KEY for the Event table; perhaps the event name?  It should be used in the many:many table.


Is it okay for me to truncate the postgresql serverlog file?

Truncating the log file with
> logfile

should work.
If you have logging_collector = on (typically, if you are not using the Ubuntu or Debian binaries), you can also set log_rotation_size to the desired maximum and reload (not restart) PostgreSQL.


Best practices in securing Oracle Standard edition one

You may want to work through the Oracle Project Lockdown documentation.  It takes you though the security process with increasing levels of security over time.


Using Resource Governor and allocate more memory to satisfy high Memory Grant request by queries?

This is the opposite of what you should do.

When you have queries waiting on RESOURCE_SEMAPHORE, they're waiting for other queries to finish running, and give up their memory. The queries that are waiting aren't even running.

Letting all queries use more memory will only make the problem worse. You'd wanna configure resource governor to make queries ask for smaller grants. This could also cause problems, though, because any spills to disk would get worse.


Why does InnoDB store all databases in one file?

The architecture of InnoDB demands the use of four basic types of info pages

Table Data Pages
Table Index Pages
Table MetaData
MVCC Data (to support Transaction Isolation and ACID Compliance)

Rollback Segments
Undo Space
Double Write Buffer (background writing to prevent reliance on OS   caching)
Insert Buffer (managing changes to non-unique secondary indexes)

See the Pictorial Representation of ibdata1
By default, innodb_file_per_table is disabled. This causes all four info page types to land a single file called ibdata1. Many people try to spread out the data by making multiple ibdata files. This could lead to fragmentation of data and index pages.
This is why I often recommend cleaning up the InnoDB infrastructure, using the default ibdata1 file and nothing more.
Copying is very dangerous because of the infrastructure under which InnoDB works. There are two basic infrastructures

innodb_file_per_table disabled
innodb_file_per_table enabled

InnoDB (innodb_file_per_table disabled)
With innodb_file_per_table disabled, all these types of InnoDB info live within ibdata1. The only manifestation of any InnoDB table outside of ibdata1 is the .frm file of the InnoDB table. Copying all InnoDB data at once requires copying all of /var/lib/mysql.
Copying an individual InnoDB table is totally impossible. You must MySQL dump to extract a dump of the table as a logical representation of the data and its corresponding index definitions. You would then load that dump to another database on the same server or another server.
InnoDB (innodb_file_per_table enabled)
With innodb_file_per_table enabled, table data and its indexes live in the database folder next to the .frm file. For example, for the table db1.mytable, the manifestation of that InnoDB table outside of ibdata1 would be:

/var/lib/mysql/db1/mytable.frm
/var/lib/mysql/db1/mytable.ibd

System Tablespace ibdata1
All the metadata for db1.mytable still resides in ibdata1 and there is absolutely no way around that. Redo logs and MVCC data also still live with ibdata1.
When it comes to table fragmentation, here is what happens to ibdata1:

innodb_file_per_table enabled: you can shrink db1.mytables with ALTER TABLE db1.mytable ENGINE=InnoDB; or OPTIMIZE TABLE db1.mytable;. This results in /var/lib/mysql/db1/mytable.ibd being physically smaller with no fragmentation.
innodb_file_per_table disabled: you cannot shrink db1.mytables with ALTER TABLE db1.mytable ENGINE=InnoDB; or OPTIMIZE TABLE db1.mytable; because it resides with ibdata1. Running either command actually, make the table contiguous and faster to read and write to. Unfortunately, that occurs at the end of ibdata1. This makes ibdata1 grow rapidly. This is fully addressed in my InnoDB Cleanup Post.

WARNING (or DANGER as the Robot would say in Lost in Space)
If you are thinking of just copying the .frm and .ibd file, you are in line for the world of hurting. Copying the .frm and .ibd file of an InnoDB table is only good if and only if you can guarantee that the tablespace id of the .ibd file matches exactly with the tablespace id entry in the metadata of the ibdata1 file.
I wrote two posts in DBA StackExchange about this tablespace id concept

Table compression in InnoDB? (under the heading 'Restoring Databases')
How to Recover an InnoDB table whose files were moved around

Here is an excellent link on how to reattach any .ibd file to ibdata1 in the event of mismatched tablespace ids : http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file. After reading this, you should come to the immediate realization that copying .ibd files is just plain crazy.
For InnoDB, you only need to something this to move
CREATE TABLE db2.mytable LIKE db1.mytable;
INSERT INTO db2.mytable SELECT * FROM db1.mytable;

to make a copy of an InnoDB table.
If you are migrating it to another DB server, use mysqldump.
With regard to mixing all InnoDB tables from all databases, I can actually see the wisdom in doing so. At my employer's DB/Web hosting company, I have one MySQL Client that has a table in one database whose constraints are mapped to another table in another database within the same MySQL instance. With one common metadata repository, it makes transactional support and MVCC operability possible across multiple databases.


Proper resizing of Log File after shrinking - SQL

I think you'll find a good starting point here - Set transaction log size and growth

To summarize from the link:

Set your transaction log large enough so it will not grow during
normal operation. All update activity stops in the database when the
logs grows, so you want to minimize log growths.
For an initial log size, you could roughly estimate the log size
needed to rebuild your largest table in db (roughly, that is the size
of that table) and set the initial log size to that value. That would
be the “fit largest transaction” method. Imprecise, but is very
simple.
Log file growth should be changed from a percentage (as is the
default) to a fixed MB size and monitored.
The link references the usage of BULK RECOVERY model to limit the
size of the transaction log due to 'minimally logged' transactions
like BULK INSERT and INDEX REBUILDS. You can only switch to BULK
RECOVERY from FULL RECOVERY, but you should be aware that you cannot
restore to a point of time that encompasses a 'minimally logged'
transaction because individual log (insert/update/delete) records are
not generated during that process. While the actual log file will not
be large for a 'minimally logged' transaction, the log backup will
still be large due to backing up the changed data extents that were
affected during the 'minimally logged' transaction.
In FULL RECOVERY, the transaction log will continue to grow until a
log backup occurs (assuming there aren't any open transactions left)
In SIMPLE RECOVERY, the transaction log is 'basically' available for
reuse after a transaction commits work.
Generally speaking, shrinking the log is discouraged. At some point,
there was activity that caused it to grow to that size (index
rebuild, etc.). There may be situations where an out-of-the-normal
issue occurred and caused the transaction to grow large than normally
needed and in those cases you may need to shrink the log down to a
more realistic size

On a side note, your question states you are taking transaction log backups 8 times per day (6 days per week).  I would highly encourage you to read Back Up Transaction Logs Every Minute. Yes, Really.


First question - are you using SSD or hard disks. If you are using SSD you probably don't need to do index maintenance at all (maybe once or twice a year) as doing a random read has no cost disadvantage over doing a sequential read.
That said if your database is not in use a rebuild ought to be perfectly fine, be aware that the transaction log should be sized to be equal in size to the largest table as the rebuild will be done in a single transaction.


Remote MySQL connection fails (10060) - "Can't connect to MySQL server on domainname.com(10060)"

you need to make sure there is:

a user created that has remote access for example user@'x.x.x.x'

the two machines can establish a connection (they can ping each
other for example or access each another by another method) the port
is opened between the two machines

check your configuration file  my.ini/my.cnf for parameters like bind-address or skip-networking. Is there a blocking connection parameter
like:bind-address=a.a.a.a or skip-networking?

the service is up and running when you try to connect from the
other machine


Install SSDT for VS 2017 - Missing Integration Services

Repairing the installation of SSDT resolved the issue.


It's usually up to you to do that via normalisation. Have a table with unique URLs in it, and link to that by its ID.


This query will identify Table and Index Scans where no seek predicate is present in the query plan.  Keep in mind this query is pretty computationally and I/O heavy, so you should probably not run this on a schedule in production.

Also, keep in mind, since this looks at the plan cache, there are a variety of circumstances where queries may be performing scans that are not reported, including plans that have been evicted from the cache, single-use plan stubs when ad-hoc query plans are enabled, queries with OPTION (RECOMPILE), zero-cost plans, etc.

By design, I've filtered out #temp tables and @table variables.  Depending on your requirements, it might be good to eliminate those filters.

Also, if you have plans with over 128 levels of nesting, the xquery processor will report an error.

The first column of the results shows the number of times the plan has been used - this number is reset if the plan is evicted, or if the server restarts, or if the plan cache is cleared.  The 2nd column is the entire query plan - if you click the value in the results pane of SQL Server Management Studio, you'll see the nice graphical plan pop up.  The TableScans and IndexScans columns contain details about each particular table or index that was scanned, including the columns pertinent for the query.

;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS qp)
SELECT decp.usecounts
    , QueryPlan = deqp.query_plan
    , TableScans = CONVERT(xml,
        (
        SELECT ts.x.query('..')
        FROM deqp.query_plan.nodes('/qp:ShowPlanXML//qp:TableScan/qp:Object') ts(x)
        WHERE ts.x.exist('@Table[contains(., "@")]') = 0 --exclude scans of table variables
            AND ts.x.exist('@Table[contains(., "#")]') = 0 --exclude scans of temp tables
            AND ts.x.query('..').exist('//qp:SeekPredicates') = 0 --exlude seeks
        FOR XML PATH (''), ROOT ('xml'))
        )
    , IndexScans = CONVERT(xml,
        (
        SELECT ts.x.query('..')
        FROM deqp.query_plan.nodes('/qp:ShowPlanXML//qp:IndexScan/qp:Object') ts(x)
        WHERE ts.x.exist('@Table[contains(., "@")]') = 0 --exclude scans of table variables
            AND ts.x.exist('@Table[contains(., "#")]') = 0 --exclude scans of temp tables
            AND ts.x.query('..').exist('//qp:SeekPredicates') = 0 --exlude seeks
        FOR XML PATH (''), ROOT ('xml'))
        )
FROM sys.dm_exec_cached_plans decp
CROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) deqp
WHERE (
    deqp.query_plan.exist('/qp:ShowPlanXML//qp:TableScan') = 1
        AND EXISTS (
            SELECT 1
            FROM deqp.query_plan.nodes('/qp:ShowPlanXML//qp:TableScan/qp:Object') ts(x)
            WHERE ts.x.exist('@Table[contains(., "@")]') = 0 --exclude scans of table variables
                AND ts.x.exist('@Table[contains(., "#")]') = 0 --exclude scans of temp tables
                AND ts.x.query('..').exist('//qp:SeekPredicates') = 0 --exlude seeks
            )
    )
    OR (
    deqp.query_plan.exist('/qp:ShowPlanXML//qp:IndexScan') = 1
        AND EXISTS (
            SELECT 1
            FROM deqp.query_plan.nodes('/qp:ShowPlanXML//qp:IndexScan/qp:Object') ts(x)
            WHERE ts.x.exist('@Table[contains(., "@")]') = 0 --exclude scans of table variables
                AND ts.x.exist('@Table[contains(., "#")]') = 0 --exclude scans of temp tables
                AND ts.x.query('..').exist('//qp:SeekPredicates') = 0 --exlude seeks
            )
    )
ORDER BY decp.usecounts DESC;

Another caveat emptor for the above query; it filters #temp tables by looking for table names that have a # anywhere in the name.  So, [this#is#a#dumb#name] will be excluded.  As would [hell@].

Also, the wonderful sp_BlitzCache, by the Brent Ozar team, does a great job looking for scans.  FYI, I'm not affiliated with them, I just think they do great work.


Is It really any way to speed up the execution time

You are running Select * from tblUsers and expecting it to be fast? You are selecting all columns and all rows from a table, so it will take its own time to return the result set. If there is a clustered index, an index scan would be used, and if the table is a heap, a table scan would be used.

If you want to increase the speed, you either have to add a WHERE clause to make query sargable or limit the number of columns, although the latter will not affect the execution time so much.

Also, if your data file lies on a fast storage (SSDs, Flash Drives, as compared to SATA disks), the query is likely to become faster.

Moral: If you are running select * on a table, don't expect the query to be fast, it would take its own time to retrieve all rows. If you are on a production system, running select * would really be a performance killer, so avoid it.


DBATools offline install - export-dbainstance was not found

You need to import the module using import-module dbatools

See Properly importing the module

  Specifying the path to the .psd1 file

hope that helps you !

Also, what Scott commented is documented in the same link you have (just read the page carefully to see if you are not missing any steps):

  Once you’ve extracted the folder, make sure you rename it from dbatools-master or dbatools-development to plain o’ dbatools.


The documentation for Indexed Views is pretty clear about what's allowed, and what's not allowed.

Specifically:

  ROWSET functions (OPENDATASOURCE, OPENQUERY, OPENROWSET, AND OPENXML)

There's no 'workaround' because it's not allowed.


Query Performance depending on parameter 21seconds vs. > 14hours

I don't know about sqlserver but in an Oracle database, I would suggest making a trace of the sql execution, one that includes all waits and events that cause the query to spend time. This shows the exact circumstances where the sql is executing in and that might be very different than those in the environment where you did the explain plan. Sqlserver without doubt has a similar feature to show the real execution, including waits.

In Oracle we have sql plan stability. Maybe sqlserver has something similar? In that case, try to use that.


Mysql 8.0 and read-only not working as expected

@Rossi, you don't have to update your "CONNECTION_ADMIN" privilege to make your slave read only for SUPER users like you.

you can set super_read_only (available from MySQL 5.6.21) and your server will not allow any updates even for the users that have SUPER privilege.

super_read_only Documentation


How to take Snapshot of Restoring Database

Its a bit round about. But first you have the option in the configuration to change the resulting database to 'standby mode'.

This results in a destination database which is grey, and is marked as 'Standby / Read-Only'. You can look at the tables and query it etc.

Take the database offline (TestReplication is what mine is called), and copy the mdf and ldf files. Put it back online again.

Create a new database called 'TestReplicationNew'. Take it offline, and copy the mdf and ldf files from the last step over top of the TestReplicationNew mdl and ldf files.

Take it back online and you have a read write database that you can test works.

If I don't do it like this I get an error,

cannot attach a database that was being restored


SQL is producing CROSS JOIN but I want INNER JOIN (records being multiplied)

The answer becomes obvious when valid values are inserted for the PRIMARY KEYs of the two tables you are joining and you look at the results:

An example:

The two tables:

CREATE TABLE fred (int_1 INTEGER, ref1 INTEGER);   -- equivalent of GRNHISTORY
CREATE TABLE bill (int_2 INTEGER, ref2 INTEGER);   -- of Inventorytransferlog

The data:

INSERT INTO fred VALUES (1, 55), (2, 56), (3, 57);
INSERT INTO bill VALUES (21, 55), (22, 56), (23, 57);
       -- NOTE: only 1 matching value of the JOINing refrerence field per table!
       -- and NOTE: different PRIMARY KEYs for the two tables!

The (your) SQL:

SELECT * FROM fred f
INNER JOIN bill b ON f.ref1 = b.ref2

The result (and fiddle):

int1 ref1 int2 ref2
   1   55   21   55
   2   56   22   56
   3   57   23   57

But, if the data is now changed as follows:

INSERT INTO fred VALUES (1, 55), (2, 55), (3, 55);
INSERT INTO bill VALUES (21, 55), (22, 55), (23, 55);

       -- NOTE: 55 - ALL the values of JOINing reference columns in each record match!

Note that the joining field now has the same value in both tables for all records, then run the same SQL as above. Result (and fiddle):

  int1  ref1 int1 ref2
    1   55  21  55
    2   55  21  55
    3   55  21  55
    1   55  22  55
    2   55  22  55
    3   55  22  55
    1   55  23  55
    2   55  23  55
    3   55  23  55

And the result is a 9 (i.e. 3 x 3) records (effective) CROSS JOIN because your data makes it so! Because you've asked the SQL to JOIN on very common values in BOTH tables so therefore you can expect a lot of records in the result!

You can check this out by doing a

SELECT COUNT(DISTINCT(ref1)) AS distinct_ref FROM fred;

answer = 1, (and the same result for bill with ref2).

I'm willing to bet that in your tables where you are getting a CROSS JOIN (or something very similar), that the SQL above using the ReferenceEntryID will get you very low counts (which also match in both tables) leading to many matching records in your SELECT.

There's the explanation for your apparent multiplication or CROSS JOIN - you have too many matching records in both tables - et voilà!


This is called a pivot

SELECT fetcha,
SUM(CASE WHEN Dato='siguiendo' THEN valor END) as siguiendo,
SUM(CASE WHEN Dato='seguidores' THEN valor END) as seguidores
FROM t
GROUP BY fetcha


Postgresql 9.2 won't start after rerunning installer

Sorry, I don't have enough rep to participate in the comments yet, but I use PostgreSQL a lot on Linux and Windows.

I ran into this same problem the other day. I ran the 9.1.7 installer and it broke my clusters. As Mike Christensen said, the key is running pg_ctl START in a command window to get a useful error message.

I got the command to use from the Services panel. When you view properties on your cluster definition, you'll see the pg_ctl command to start the service. Copy that command and paste it into the command window, then change the "runservice" parameter to "start", and use runas.exe to launch the command as the postgres user (9.1 and below) or LOCALSERVICE account (later versions).

It should already contain the path to your cluster data directory. This is what mine looks like:

C:/PROGRA~1/PostgreSQL/9.1/bin/pg_ctl.exe runservice -N "PostgreSQL Dev Cluster" -D "C:/PROGRA~1/PostgreSQL/9.1/data"

Check the error message you get from that command, and check the database logs also.

In my case I had two problems. First, I couldn't get the installer to work until I ran it under postgres local admin account. But when I did that, my domain account no longer had privileges. I had to re-grant myself full control on PostgreSQL directory tree. ( Edit CR: Don't mess with permissions on the PostgreSQL data directory unless you know exactly what you are doing, you are likely to make any problem much worse not better.)

Then I tried starting it again and got a different message indicating port 5432 was already in use. This turned out to be true. Somehow the installer had left an orphaned postgres.exe process open on port 5432, even though the Services panel showed that service as not started.

I had to use netstat -ano to find the PID and kill the orphan from task manager. Then everything started normally.

Hope that helps.

Brian


Pull the time from the highscores table separately
MySQL doesn't respect ANSI standard aggregations by default: hence your dodgy data

SELECT
    u.UID, u.ALIAS, u.EMAIL, h.SCORE, h.value1 AS TIME
FROM
   (
    SELECT user_uid, MAX(score) as SCORE
    FROM highscores
    GROUP BY user_uid
   ) x
   JOIN
   highscores h ON x.user_uid = h.user_uid AND x.SCORE = h.SCORE
   JOIN
   users u ON h.user_uid = u.uid
ORDER BY h.SCORE DESC
LIMIT 0, 10

This assumes that each user does not have multiple high scores with the same value.
If you do, you need a 2nd derived table with an aggregate to nest the x/h JOIN


How can I join one table or other depending on a field value?

Short answer, you can't. Maybe you can do something like:

select ...
from t
left join t1
    on t.type = 1
    and t.x = t1.x
left join t2
    on t.type = 2
    and t.x = t1.x

to return something (must be same type) from either t1 or t2 you can use:

select coalesce(t1.y, t2.y) ...


Here are three four options:

Option 1 - consider how long the jobs that populate the summary tables take to run. If that time is not prohibitively long, then consider running the jobs nightly regardless of whether there's been a change or not. That actual frequency of changes, especially in the biggest tables, would influence your decision here.

Option 2 - @SQLWorldWide suggested an alternative from a Pinal Dave article; using the sys.dm_db_index_usage_stats view to detect the last time the table was changed. (Clustered indexes have to change whenever any column changes; for tables without a clustered index, a "dummy" index (index_id = 0) will be shown, again showing the time when anything was inserted, updated, or deleted in the table). However, this has one flaw - these views are reset each time the server is restarted. So, if the server was restarted five minutes before your job ran, you might not update any of the summary tables.

(See "UPDATE" below, for an even better alternative):

Again, depending on how long it takes to refresh all the summary tables, and how often your server is restarted, there is an alternative:

SELECT MAX(last_user_update) as last_change
  FROM (SELECT last_user_update
          FROM sys.dm_db_index_usage_stats
         WHERE database_id = DB_ID('<dbname>')
           AND OBJECT_ID=OBJECT_ID('<tablename>')
        UNION ALL
        SELECT sqlserver_start_time FROM sys.dm_os_sys_info
       ) q
;

(Replace <dbname> and <tablename> with the appropriate values for your database and table, of course.)

This query will return the time of the most recent change to <tablename> of the last time your server was restarted, whichever came last.

Have your job store the time and date when it started running as its last step, and retrieve that last run time as its first step. If the last run time is less than last_changed, then update the summary table.

As noted, this will mean that all the summary tables will be updated after each restart; if that is a major issue, then this option wil lprobably not work for you.

Option 3 - This is your idea, tweaked slightly.

Instead of writing the table name down each time the trigger runs, I'd have UpdatedTables have two columns (more if needed, for instance for the name of the procedure that updates the summary table, or whatever you'd need to run that process when appropriate): tablename, and is_changed, a bit column set to 0 by default. Your trigger would update the bit column to one where the tablename matched and the bit column was zero; after the summary tables are updated, just reset the bit column to 0.

Note that you'd want to lock the table while updating the summary table - otherwise, a change might happen during that update, and no record would be made of it. That's a problem that would hold with your solution as originally written if you prefer that - and is the only thing that jumps out at me as a possible problem with it.

If locking the table isn't an option (and it probably isn't, if a large import/export could be going on when the job that checks for updates runs), you could reset is_changed before updating the summary table. However, in that case, you'd want to be sure to set is_changed back to one if the summary table update failed for some reason, so it would get run again next time.

With the tweaked solution, hopefully the trigger would run very quickly - and, you don't run the risk of have millions of records in UpdatedTables that all say 'Name_of_Updated_Table' - that does consume disk space, after all.

The last few paragraphs answer your first two questions. I would not turn the trigger off while a large job is updating a table (if you are using a trigger), unless the job manually updates your table noting it was changed when it finishes. If the summary table update job ran while your large import update was running, but after your first change had run the trigger, the summary job would clear the update flag for the table - and the rest of the import/update wouldn't get flagged as a change.

UPDATE:

Option 4 - Use a combination of options 2 and 3.

Credit to @SQLWorldwide again, as his comment made me think of this option.

For this option, UpdatedTables should be as described in option 3.

As in option two, record the time when you start the summary table update process each night after it's successfully completed. Record it in table LastSummaryRun, column RunDate. TRUNCATE the table and INSERT the value, so the table only has one row.

At the end of that process, clear the is_changed flags in UpdatedTables and run this:

UPDATE ut
   SET is_changed = 1
  FROM UpdatedTables ut
         CROSS JOIN LastSummaryRun ls
 WHERE EXISTS (SELECT 1
                 FROM sys.dm_db_index_usage_stats
                WHERE database_id = DB_ID('<dbname>')
                  AND OBJECT_ID=OBJECT_ID(ut.tablename)
                  AND last_user_update > ls.RunTime
              )
;

(Again, replace <dbname> and <tablename> with the appropriate values for your database and table, of course.)

This will catch any changes that happened while your job was running, and will make sure we've primed all the values, in case there's a restart.

Execute this same statement in a job that runs every 5 minutes or so.

UpdatedTables should be correct unless:

the server restarted, AND
the only change that day happened in the five minutes before the restart.

If that seems like an acceptable risk, then this is probably the best of all the options. Note that the job can be run more often than every 5 minutes


Sql Server 2005 will install just fine on a single user system. A better question is why you would want to. Most of the time when I've seen people want to do this, they're using it as a simple data store for a desktop application they are building. If that's what you're doing, you should probably choose an in-process database like Sqlite, Sql Server Compact Edition, or even MS Access instead. Of course there are exceptions, but really if this is part of an app that must coexist with other apps on a machine that will serve as some user's primary desktop, you should really avoid trying to push a whole server at them.


Is it always bad to have an own table for value objects?

Normalization is a set of techniques to avoid certain problems viz data update anomolies.  In a perfect world you would not store structured content but split it into its constituent parts across however many table are required and re-construct each complex object at runtime (1st normal form).

However .. if pre-parsing the object and storing the interesting / frequently used bits somewhere else resolves a performance issue then do this as long as you do it with your eyes open.  If you, and everyone else who may code against the database, understands that extra work must be done in the application in order to achieve the necessary response times then that's a design choice and necessary in the real world.

Response to comment:

My reading of your question is that content.text contains structured text in which links are embedded.  Your application shreds content.text from time to time to find and use these links.  This is expensive and as a performance optimisation you would like to store the links separately in content_link.link_id.

This is where the update anomalies can creep in.  If content is updated so that content.text has a different set of links, but content_link is not also updated then there is inconsistent data in the database.  It works the other way, too - a row can be deleted from content_link without content.text changing and, again, the data is inconsistent.  This is why the application has to be diligently coded and tested.

There is another rare-but-possible case to consider, too.  Changing the data will require two statements - update content and update content_link.  RDBM systems use pre-emptive multitasking.  This means that at any time the RDBMS may choose to halt one workload and run another instead.  So it could, for example, halt the updating stream after the first update statement and before the second.  If there is another workload which is trying to read content or content_link at that point it may run, and get inconsistent values, if the reading transaction's isolation level allows it to do so.  As I said, rare but possible.

If these problems are acceptably unlikely in your application and the implications of it happening are not severe enough to worry about then denormalisation can serve performance advantages.  If the risk of data inconsistency is too great then you'll have to find other solutions.


Is it overkill to encrypt an already hashed BCrypt password?

Adding such an additional secret - and, ideally, storing it securely in an HSM - is indeed a normal practice. It's not common, exactly; many platforms are using hashes alone (and often poorer hashes than bcrypt). But an additional layer definitely makes things harder for the attacker - if done right.

Some further advice: roll as little of the implementation yourself as possible. Instead, find well-established libraries to do as much of the crypto as you can (because it's hard to get right - and getting it wrong can be very bad indeed).

Note that bcrypt has some limitations - notably that the maximum plaintext length allows is 72 bytes. It also is not memory-hard, and therefore is subject to attack with (relatively) lower-cost specialized hardware with many cores in parallel. The modern options - scrypt and the Argon2 family - are resistant to low-memory parallelization. Look for well-baked implementations of those. Also, use bcrypt with as high of a work-factor cost as your users (and projected hardware) can tolerate (cost 12 is often where it starts to get close to the break-even point, but YMMV; test on your hardware).


MySQL Replication : 1 Slave / Multiple Masters

This is actually an interesting scenario because I addressed this in the DBA StackExchange back on March 29, 2011.

The basic idea would be to use MySQL Replication in conjunction with DRBD and ucarp

Here is what you would need:

Two DRBD Clusters, one in each DataCenter
DRBD Pair at each DataCenter connected via a Crossover Cable (perhaps using 192.168.x.x subnet)
DRBD Primary has MySQL Running
DRBD Primary at one DataCenter running MultiMaster Replication with the DRBD Primary at other DataCenter via ucarp (in both directions)
Any needed read slaves at each Data Center would the DBVIP established for use within MySQL at that DataCenter via ucarp

Having this setup gives you block-level replication within both data centers. If you are using MySQL 5.5, you can have Semisynchronous Replication send SQL to the other DataCenter without waiting for the SQL to be executed, only acknowledged. This keeps any intermittency that standard MySQL Asynchronous Replication would normally cause a MySQL Master to a minimum. In the event of any sudden network latency, MySQL Replication between DataCenters would switch from Semisync to Async. Once conditions improve, then MySQL Replication would it switch back to Semisynch.

CAVEAT

This setup will not prevent table corruption, particularly if MyISAM is involved. Even with DRBD, block-replication of a corrupt MyISAM table would produce...you guessed it, a corrupt MyISAM table in the DRBD Secondary. Therefore, it is preferable if all user data were InnoDB.


Use array:

UPDATE callcenter.pausa
SET permissao_ver = array(SELECT cod_grupo
                          FROM crm.usuariosgrupos
                          WHERE habilitar = 1)
WHERE habilitado = 1
  AND permissao_ver is null


How to find SSL root cert that made connection to the database in PostgreSQL?

If you use ssl-mode=verify-full, then the server certificate is always tested against the root certificate you pass to the client. So the mere fact that the connection was established successfully is proof that your root certificate has been used.


How to group by items and calculate the quantities after calculation MySQL

you should normalize your tbales
the columns order_items.type and orders.order_type should be Tinyint  and you need also two small tables ordertype and orderItemtype that canes joined when the text is needed
Schema (MySQL v5.7)
CREATE TABLE order_items (
  `id` INTEGER,
  `order_id` INTEGER,
  `item_id` INTEGER,
  `quantity` INTEGER,
  `type` VARCHAR(6)
);

INSERT INTO order_items
  (`id`, `order_id`, `item_id`, `quantity`, `type`)
VALUES
  ('1', '1', '1', '10', 'Accept'),
  ('2', '1', '2', '15', 'Accept'),
  ('3', '1', '3', '20', 'Reject'),
  ('4', '2', '1', '5', 'Accept'),
  ('5', '2', '2', '10', 'Accept'),
  ('6', '2', '4', '10', 'Accept'),
  ('7', '3', '1', '2', 'Accept'),
  ('9', '3', '2', '5', 'Accept'),
  ('10', '4', '1', '5', 'Accept'),
  ('11', '4', '2', '10', 'Accept');

CREATE TABLE orders (
  `id` INTEGER,
  `order_type` VARCHAR(8),
  `order_from` INTEGER,
  `base_order_if_purchase` VARCHAR(4),
  `amount` INTEGER,
  `status` VARCHAR(10)
);

INSERT INTO orders
  (`id`, `order_type`, `order_from`, `base_order_if_purchase`, `amount`, `status`)
VALUES
  ('1', 'sales', '1', NULL, '8500', 'incomplete'),
  ('2', 'purchase', '1', '1', '2500', 'complete'),
  ('3', 'purchase', '1', '1', '5000', 'complete'),
  ('4', 'sales', '1', NULL, '7000', 'incomplete');

Query #1
SELECT
    `row_num`,`item_id`
    ,SUM(`sumitem`)
   FROM
   (SELECT
         `item_id`,o.`row_num` , IF (`order_type` = 'sales',`quantity`,-`quantity`) sumitem
    FROM order_items o_i
   INNER JOIN (  SELECT o.`id`, o.`order_type`,row_num
  FROM `orders` o INNER JOIN (
  SELECT `id`, @rn := @rn + 1  row_num FROM orders ,(SELECT @rn := 0) a WHERE `base_order_if_purchase` IS NULL ORDER BY `id`) o1
  ON o.`id` =  o1.`id` OR o.`base_order_if_purchase` =  o1.`id`) o ON o_i.`order_id` = o.`id`
   WHERE o_i.`type` = 'Accept') t1
  GROUP BY `row_num`,`item_id`;

| row_num | item_id | SUM(`sumitem`) |
| ------- | ------- | -------------- |
| 1       | 1       | 3              |
| 1       | 2       | 0              |
| 1       | 4       | -10            |
| 2       | 1       | 5              |
| 2       | 2       | 10             |

View on DB Fiddle


No IMPLICIT_CONVERSION warning in this query plan

Your parameters have a lower Data Type Precedence than the columns they are compared to.  In this case the parameter values will be implicitly converted.  This is a good thing.  Converting the column values to the parameter’s type is problematic and will trigger the warning.


The error message is telling you exactly what the problem is:

  You can only create audit actions on objects in the current database.
  (Microsoft SQL Server, Error: 33221)

The audit can only be on objects in the current database. Your screenshot shows that you are trying to audit two different databases: audit_test and db_aiudit

You will need to create two database audit specificiations - one for each of the two databases listed above a the respective database level


Memory usage on select for large number of rows

By default, the results are entirely buffered in memory for two reasons:

1) Unless using the -A option, output rows are aligned so the output cannot start until psql knows the maximum length of each column, which implies visiting every row (which also takes a significant time in addition to lots of memory).

2) Unless specifying a FETCH_COUNT, psql uses the synchronous PQexec function directly on the query, which buffers the entire resultset. But when setting a FETCH_COUNT, it will use a cursor-based method with successive fetch calls and freeing or reusing the client-side buffer every FETCH_COUNT rows.

So a big resultset should be fetched by a command like:

psql -A -t --variable="FETCH_COUNT=10000" \
     -c "select columns from bigtable" \
     > output-file

With FETCH_COUNT reduced if the rows are very large and it still eats too much memory.

The -t stands for --tuples-only, which suppresses the output of headers and footers.


SSAS Tabular: ImpersonationMode that is not supported for processing operations

I agree the documentation on this is a bit terse, but the error message on the other hand is very clear. ImpersonateCurrentUser is not supported for models attached to a SSAS instance.

This is mentioned in the documentation of the ImpersonationMode Enumeration:

  ImpersonateCurrentUser: Not supported for tabular model databases attached to an Analysis Services instance.

As well as in the documentation on Impersonation (SSAS Tabular) under the Options header:

  When configuring impersonation, or when editing properties for an
  existing data source connection in Analysis Services, you can specify
  one of the following options:

  ImpersonateWindowsUserAccount
  ImpersonateServiceAccount


Well, all you really need to do is decide on your date range, then pick a random number between 0 and the delta in days (+1), then add that many days to the start of the range.

DECLARE @start DATE = '19700101', @end DATE = SYSDATETIME();

SELECT DATEADD
(
  DAY,
  (ABS(CHECKSUM(NEWID())) % (DATEDIFF(DAY, @start, @end)+1)),
  @start
);

There are other methods you can use to achieve randomness, including RAND() and CRYPT_GEN_RANDOM(). In my experience, distribution is not particularly fantastic in any of them, and some will behave differently in a query than others, so they may or may not be adequate depending on your needs. I followed up on a comment from Paul White, but saw similarly poor distribution with the following query (of course quality of distribution is also a factor of the size of the range):

DECLARE @start DATE = '19700101', @end DATE = SYSDATETIME(), @r INT;
SET @r = RAND(CHECKSUM(NEWID())) * (DATEDIFF(DAY, @start, @end)+1);
SELECT DATEADD(DAY, @r, @start);

Documentation:

NEWID()
RAND()
CRYPT_GEN_RANDOM()


Explain PostgreSql ssl_cipher values: ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH

This is a parameter that is processed by the OpenSSL library. The format and options are documented in the ciphers(5) man page.

For the ones you mention:

  ALL

  all cipher suites except the eNULL ciphers which must be explicitly enabled; as of OpenSSL, the ALL cipher suites are reasonably ordered by default

  ADH

  anonymous DH cipher suites, note that this does not include anonymous Elliptic Curve DH (ECDH) cipher suites.

  LOW

  ``low'' encryption cipher suites, currently those using 64 or 56 bit encryption algorithms but excluding export cipher suites.

  EXP, EXPORT

  export encryption algorithms. Including 40 and 56 bits algorithms.

  MD5

  cipher suites using MD5.

You can combine them and specifically include or exclude classes of ciphers by using prefixes:

  Each cipher string can be optionally preceded by the characters !, -
  or +.

  If ! is used then the ciphers are permanently deleted from the list.
  The ciphers deleted can never reappear in the list even if they are
  explicitly stated.

  If - is used then the ciphers are deleted from the list, but some or
  all of the ciphers can be added again by later options.

  If + is used then the ciphers are moved to the end of the list. This
  option doesn't add any new ciphers it just moves matching existing
  ones.

@STRENGTH is a bit special:

  The cipher string @STRENGTH can be used at any point to sort the current cipher list in order of encryption algorithm key length.


You sent the wrong query to the database.
You hit a mysql extension that is described in the manual.
A query like

SELECT city, MAX(salary)
from employee

will not work in standard SQL. It raises an error in Oracle (error message: "ORA-00937: not a single-group group function") , in MSSqlServer 2012 (error message: "Column 'employee.city' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause")  or postgresql (error message:"ERROR: column "employee.city" must appear in the GROUP BY clause or be used in an aggregate function").

In standard SQL expressions in the select list can only be expressions of columns that are also used in the group by clause and of  aggregate functions. A column values  that is used in the group by clause is the same for all rows in these group. The aggregate is also unique for a group.
The select returns one row for each group containing this uniquely defined values for a group.

If the aggregate query does not have a group by clause ther is only one group of rows.

The standard does not allow to use an arbitrary column in the select list of an aggregate query because its value not uniquely defined: the column has different values for the rows of the query so which one should be returned for this group by the query?

In mysql there is the extension that if there is a column in an expression (not an in an aggregate function expression) that is not in the group by clause the value returned by the select statement for this group is the value of this column of an arbitrary row of this group.

So the query

SELECT city, MAX(salary)
from employee

returns the sumof all employees' salaries and the city of one of these rows. But the query return exactly one reow becuase it is an aggregate query and there is only one group that contains all records from employee table.

The query

SELECT city, MAX(salary)
from employee
group by city

returns a row for each city with the city and of the salaries of each city.

The query

SELECT city, salary
from employee
group by city

returns arow for each city with and salaty of an arbitrary employee of this city.  The server decides which employee of a city is choosen.

The query

SELECT city, zipcode
from employee
group by city

also return a row for each city with the zipcode of this city. If we assume that every city haas only one
zipcode than the rows of each group defined by a city contain al the same zipcode. so independent of the row the server selects the retuned zicode wil always be the zipcode of the city.

In standard SQL this query would be written as

SELECT city, zipcode
from employee
group by city, zipcode

to create the expected result

group by city,zipcode and group city define the same groups because of the one to one correspondence between zipcdoe an city.

The query

SELECT city, SUM(salary)
from employee
group by city

will contain not only  the needed rows but more.

A way to filter out the needed rows from this query is the following

SELECT city, SUM(salary)
from employee
group by city
having SUM(salary) >= all (select SUM(salary)  city_salary
     from employee
     group by city)

There are other ways to filter.

The largest salary sum can be found by the following query

select MAX(city_salary)
   from (select SUM(salary)  city_salary
     from employee
     group by city) tab

(Note: In Oracle the same can be achieved by

select MAX(SUM(salary))  city_salary
  from employee
  group by city

)

So we can use it to filter out the needed one:

SELECT city, SUM(salary)
from employee
group by city
having SUM(salary) = (select MAX(city_salary)
   from (select SUM(salary)  city_salary
     from employee
     group by city) tab)

This  solution was  already presented by @Mihai in his comment.


Was 'ActiveX Scripting' step type secretly removed from SQL Server 2016?

Discontinued Database Engine Functionality in SQL Server 2016 indicates

ActiveX subsytem is discontinued. Use command line or PowerShell scripts instead.


The cause is probably due to I/O.

Use "iostat -xm 2" to see I/O, or iotop to monitor performance, or check the "wa" (i/o wait) in top for a rough estimate.

To delete everything immediately, use truncate:

truncate bb_gamelist_league

this will run immediately, emptying your table with the added benefit of releasing the disk space (DELETE would not release it).

Be careful with constraints/references from other tables though. Check the manual for more info.


Turns out we needed to remove the full text catalogs from 2005 then take the backup. We then recreated the catalogs in 2012 and all is good. Following a single full backup, the t-log maintenance plan runs fine.


PostgreSQL closing connection during requests

This kind of error looks like a bug in PostgreSQL:

  server process (PID 20569) was terminated by signal 11: Segmentation
  fault

Try to post to https://www.postgresql.org/list/pgsql-bugs/


Select FirstName, LastName, Date_1 as date, City_1 as city, State_1 as state
  From performertours
Union all
Select FirstName, LastName, Date_2, City_2, State_2
  From performertours where date_2 is not null
Union all
Select FirstName, LastName, Date_3, City_3, State_3
  From performertours where date_3 is not null
Union all
Select FirstName, LastName, Date_4, City_4, State_4
  From performertours where date_4 is not null


How do I safely upgrade xampp without losing database i have created?

First thing is to listen to the doctors' motto - "Do no harm".

Take a backup of your database before doing anything else - that way, if you make a mistake, you can always retrace your steps and you won't have lost any important (or even unimportant) data.


Why do I get ORA-27102 ON WINDOWS 64 BIT if I increase the memory to 2.5GB

How much real memory free does Windows task manager show, before you start up Oracle?  If less than 2.5 GB, that's the problem.  You can try increasing memory_max_target slowly from its current value, say in 256 MB increments, until it errors again, then I'd back it off by maybe 512 MB to give some headroom.


High autovacuum CPU/IO usage from database with more than 10k append-only tables

Disabling autovacuum is a very bad idea. Autovacuum removes dead lines and updates statistics. Disabling autovacuum will quickly lead to performance loss. Also, when autovacuum is disabled, sooner or later you will encounter a wraparound problem.
Since you are using TimescaleDB, why not use the drop_chunks() function to remove old data? This will significantly reduce the need to perform vacuum on tables.
However, autovacuum will still need to handle tables to prevent the wraparound I wrote about above. In addition, you can change the size of the chunk so that the size of the data to be processed by autovacuum is not so large.
There are no universal values for the autovacuum setting. Try adjusting the following settings for the best performance in your case:
autovacuum_vacuum_threshold
autovacuum_vacuum_scale_factor
autovacuum_vacuum_cost_delay

For statistics:
autovacuum_analyze_threshold
autovacuum_analyze_scale_factor

You can also configure cost parameters for the autovacuum daemon to process pages:
vacuum_cost_page_hit
vacuum_cost_page_miss
vacuum_cost_page_dirty
vacuum_cost_limit