Auto generated statistics by query optimiser Can we consider removing the auto generated statistics if the update statistic within a given database is taking too long? We do a weekly maintenance using UPDATE STATISTICS WITH RESAMPLE but often have to stop the script as it overruns into business hours.

When is it most suitable to use RESAMPLE and FULLSCAN?

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

Dropping all auto created statistics

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

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

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

Dropping duplicate statistics

Duplicate statistics could exist.

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

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

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

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

Update statistics impact

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

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

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

  When is it most suitable to use RESAMPLE and FULLSCAN?

FULLSCAN <> RESAMPLE

Resample definition from microsoft docs

  RESAMPLE

  Update each statistic using its most recent sample rate.

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

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

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

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

An example of when updating stats with FULLSCAN does not work

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

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

When FULLSCAN could work

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

When doing weekly fullscan stat updates on these tables.

Consequences of doing fullscan updates

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

RESAMPLE

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

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

What you could start out with

You could start with:

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

Or

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

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

UPDATE STATISTICS ... WITH SAMPLE 10 PERCENT

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

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

More info on that here.

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

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

Extra considerations

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

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

And change the sample rate with StatisticsSample = 10

@ErikDarling mentioned:

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

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

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

 SQRT(number of rows * 1000)

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

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

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

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

In the resultset returned below:

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

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


What is the recommended Buffer Pool size on a 2GB RAM PC? I installed DB2 Express-C on a PC with 2 GB RAM and running Windows Server 2008 R2 OS. While creating a new database, there is an option to set Buffer Pool size with default set to 4K.

How much is the recommended pool size for the above mentioned configuration?

I also want to know whether to use default Buffer Pool or a separate Buffer Pool for every database.

The default size is 4K. You can also specify 8K, 16K, or 32K. The size of the buffer pool should really be based on the size of your tables (ie, how many bytes is it possible for one row of data to hold). If a row of your data won't fit into a 4K page, then it won't fit into a 4K buffer pool. And hence you would need larger.

The same is also true of table spaces (the "physical containers" that your tables are stored in). A table that needs an 8K buffer pool, needs to reside in an 8K table space. And actually, you would then associate that table space to the buffer pool as well....

That being said, don't just "oversize" and go to 32K table spaces and buffer pools if you really only have 4K tables. You will waste a lot of disk space and memory then.

Also, you can have as many buffer pools as you want (I'm sure there is an upper limit, though I wouldn't know off hand). But you can have any combination of them. You could have five 4K pools, one 8K pool, one 16K pool, and three 32K pools.

The biggest thing is more based on your table sizes and understanding that as mentioned before.

Although, here is the nice thing....after you create your buffer pools and table spaces, DB2 is smart enough to put the table into the correct size table space (pending it exists). You can specify it in your DDL code, but DB2 is smart enough to put an 8K table in an 8K table space (and thus it will also be associated with an 8K buffer pool).

OK...now to answer your last two questions. There is no "recommended" size really. The default of 4K is fine. Most of our tables where I work fit in a 4K table space and 4K buffer pool.

What we tend to do though, as a best practice... We set up our own 4K, 8K and 32K table spaces and buffer pools (including temporary tables spaces for reorgs). Why we don't set up 16K, I'm not sure. But we set up several sizes in all our databases. This way if any developer creates a table that will be bigger than 4K, it will automatically get placed into the appropriately bigger tables pace (and thus buffer pool).

Hopefully this was clear enough for you. I have also found this article helpful with my own understanding of buffer pools and table spaces.


SQL Server 2017 in Azure Cloud config DB Mail I have set up a SQL Server 2017 in Azure cloud. I have created a new GMX email address and enabled it for pop3 external programs

I have set up db mail on the SQL Server but I am getting the following auth error

  The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2020-01-25T12:14:37).

  Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: Authentication required).  )

I have tried ports 25 and 587 with/without SSL enabled on server mail.gmx.com.

I have checked the network for Azure ports and AllowInternetOutBound is enabled and set to any.

Any assistance would be great, thanks in advance

SQL Server only supports sending email via SMTP using Basic Authentication without TLS/encryption. Basic Authentication means that the client application passes the username and password with every request. Basic Authentication makes it easier for attackers armed with today’s tools and methods to capture users’ credentials.

Many email providers are ending support for unencrypted/non-secure SMTP connections because of the risk involved. I don't know if mail.gmx.com supports non-secure connections, but it would not be surprising if they are forcing secure connections, even on port 25 (traditionally for non-secure connections).

Additionally, I notice you mention that you have "created a new GMX email address and enabled it for pop3 external programs."  I'm not familiar with GMX, but you will need to have SMTP enabled, not POP to send mail. Perhaps GMX has one configuration switch that controls POP3 & SMTP jointly, but it's worth double-checking to make sure you really have SMTP enabled.

At most of my prior employers, we white list our DB Servers to send mail without authentication via corporate mail relay servers. This vastly simplifies the mail setup, by eliminating the authentication hassle. This is possible because the network & security is more tightly controlled on an internal network, minimizing the possibility of an unauthorized user running a spambot via unauthenticated SMTP.


Increase TempDB Files, Shrink VLF's with FCI I am planning to resize and increase the number of tempDB files and shrink the VLF's on an FCI. In this case, the tempdb files are stored on the network drive, rather than the local disk. I couldn't find anything that addresses situation, but I need to know the impact the increase would have on a FCI. Thanks!

There should be no negative impact on performance esp when you have encountered PFS page contention. Just make sure you dont blindly over create files.

This script from Tigertoolbox (Microsoft Tiger Team) will help.

Depending on the version of sql server you are using, you might need or might not need below trace flag :

Trace Flag 1118 – Full Extents Only (tells SQL Server to NOT use “mixed extents” and use “full extents”)

I have not listed Trace Flag 1117 (Grow all files in a filegroup equally) as it affects instance wide. Its better to pre-allocate tempdb files and make sure that they are ALL sized equal.

Finally, make sure that you have set autogrowth to a sensible value.

Few good links to refer :

Troubleshooting: Tempdb Contention
sp_whoisactive: Analyzing Tempdb Contention


What does SELECT MIN(+ - Column) do? I ran into a query today that did something I haven't seen before. It did something like this:

SELECT
    MIN(+ - schema_id) AS [WhatInTheWorld?],
    MIN(schema_id) AS MinimumID,
    MAX(+ - schema_id) AS [Uhhh],
    MAX(schema_id) AS MaxID
FROM sys.objects

Which gives the following results:

WhatInTheWorld? MinimumID    Uhhh       MaxID
-4              1            -1         4

What exactly are the "+" and "-" doing?

MIN(+ - schema_id) is parsed as MIN( + (- schema_id) ) which is the same as MIN(- schema_id), so it's (mathematicaly)* the same as -MAX(schema_id). Likewise for the MAX.

*: There are some edge cases when the MIN/MAX(-x) might raise an error while the - MAX/MIN(x) will not (because the negation is applied in all values of the column vs only to the MAX/MIN value).


Table value functions vs UDF SQL Server I am currently watching this video on Entity Framework and about 15 minutes in, the speaker says that table value functions occasionally out perform UDF's.

My question is in what scenario do TVF's out perform UDF's?

A scalar UDF with table access acts like a lookup inside a RBAR curso. That is, it executes once per row. And it's black box to the optimier
TVF is a macro that expands like a view and is consider part of the outer query

Scalar UDF have uses, but not in this case.

The performance difference can be huge: I've achived x100,000+ improvements by removing idiotic uses of UDFs


Update Column from Recursive CTE (MySQL/MariaDB) I need to update the same table I'm using to create a recursive cte. The cte works as a select statement, but I can't get it to actually insert those values back into the table.
My code is below- basically I want to:
UPDATE tbl_1min
SET tbl_1min.ema9 = x.NewEma
WHERE x.id = tbl_1min.id

Can anyone help? I'd really appreciate it!
SET @NDays := 9;

WITH RECURSIVE anchor AS
    (
        SELECT
            a1.id,
            a1.close,
            a1.ema9
        FROM
            tbl_1min a1
),
x AS
    (
        SELECT
            anchor.id,
            anchor.close,
            x2.ema9 AS PrevEma,
            anchor.ema9,
            anchor.ema9 AS NewEma
        FROM
            anchor
        INNER JOIN
            anchor x2
        ON
            anchor.id - 1 = x2.id
        WHERE anchor.id = @NDays -- FIRST SEED ROW
UNION
   SELECT
    anchor.id,
    anchor.close,
    x.PrevEma,
    anchor.ema9,
    (((2 / (1 + @NDays)) * (anchor.close - x.PrevEma)) + x.PrevEma) AS NewEma
    FROM
        x
    JOIN anchor
    ON anchor.id = x.id + 1   -- `X` WALKS ITSELF
    )
SELECT
    id,
    NewEma
FROM
    x
ORDER BY x.id
;

You can use any query typo with the created (with statements
The update can be de by joining the original with the cnstructed

CREATE tABLE tbl_1min(id int, close int,ema9 varchar(30))

INSERT INTO tbl_1min VALUES (1,1,'test')

SET @NDays := 9;
WITH RECURSIVE anchor AS
    (
        SELECT
            a1.id,
            a1.close,
            a1.ema9
        FROM
            tbl_1min a1
),
x AS
    (
        SELECT
            anchor.id,
            anchor.close,
            x2.ema9 AS PrevEma,
            anchor.ema9,
            anchor.ema9 AS NewEma
        FROM
            anchor
        INNER JOIN
            anchor x2
        ON
            anchor.id - 1 = x2.id
        WHERE anchor.id = @NDays -- FIRST SEED ROW
UNION
   SELECT
    anchor.id,
    anchor.close,
    x.PrevEma,
    anchor.ema9,
    (((2 / (1 + @NDays)) * (anchor.close - x.PrevEma)) + x.PrevEma) AS NewEma
    FROM
        x
    JOIN anchor
    ON anchor.id = x.id + 1   -- `X` WALKS ITSELF
    )
UPDATE tbl_1min t1
        INNER JOIN
    x ON t1.id = x.id
SET t1.ema9 = x.NewEma;

SELECT * FROM tbl_1min

id | close | ema9
-: | ----: | :---
 1 |     1 | test

db<>fiddle here


Partitioned mariadb table files(the .ibd ones) placed outside of data directory return back to data directory itself after altering the table in use While Partitioning large tables in Mariadb we want some partitions to be placed in another storage device with different speed and capacity. That is our requirement.
For that we are trying to perform some testing in our local set up,where we have placed some partitions of a table(Actually partitions of the innodb tables files, the .ibd ones) outside of the data directory by means of alter table statements with data directory clause.
But the problem is that whenever we alter the table after partitioning ,the partitioned .ibd files placed outside of datadir comes back to the datadir which is really unexpected.
So can anyone please help in this context to clarify whether this is a issue with mariadb or  with the concept itself described as above.

That's a problem with ALTER.  File a bug report if you like.

I consider RAID striping to be better than manually placing pieces of the database across physical volumes.

What kind of ALTER are you doing?  Adding an index is different than adding a column is different than fiddling with the partition structure.  There may be a workaround.


Creating SQL Azure login for single database I want to create separate logins for each database we create on a SQL Azure database server which only have read / edit permissions for a single database and do not have drop permissions, and to automate this process as much as possible. One of the main reasons for doing this is to make sure that Entity framework drop and recreate scripts can't accidentally run against the database.

I found the following which looks very similar to what I'm trying to do

Allow user to do anything within his own schema but not create or drop the schema itself

Am I right in thinking I need to first create a login, then create a user, then create a role and add the user to this role?

CREATE LOGIN dblogin WITH password='m77hHmSk';

CREATE USER dbuser FROM LOGIN dblogin;

CREATE ROLE dbuserrole AUTHORIZATION dbo;
EXEC sp_addrolemember 'dbuserrole ', 'dbuser';

CREATE SCHEMA myschema AUTHORIZATION dbo;

GRANT ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, UPDATE, VIEW DEFINITION ON SCHEMA::myschema TO dbuserrole ;

I've come up with the above.. am I close? Where would I specify the database which can be accessed?

Connect to master database and do the following:

CREATE LOGIN YourNewUser WITH PASSWORD = '&lt;strong password here&gt;';

Connect directly to database that the new user should own:

CREATE USER YourNewUser FOR LOGIN YourNewUser;

EXEC sp_addrolemember 'db_owner', 'YourNewUser';


How to do an =ANY(SELECT...query in postgresql? I'm trying to select all the latitudes and longitudes for a group of users based on their id being in an array stored in another table. Here's my attempt:

SELECT latitude, longitude
FROM userloc WHERE id = ANY( SELECT interested FROM donedeals WHERE deals_id=67);

But it gives me the following error:

ERROR:  operator does not exist: integer = integer[]
LINE 1: SELECT latitude, longitude FROM userloc WHERE id = ANY( SELE...
                                                         ^
HINT:  No operator matches the given name and argument type(s).
       You might need to add explicit type casts.

donedeals has an int column for deals_id and an int array column for interested, which contains id's corresponding to the id column of userloc, which stores latitude and longitude:

 deals_id |  interested
----------+---------------
       67 | {377,387,376}
       64 | {381,384}
       66 | {377,387}

  latitude  |  longitude  | id
------------+-------------+-----
 40.6439417 |  -73.964927 | 384
 40.7554919 |  -73.925891 | 380
 40.6434067 | -73.9657654 | 385
  40.746452 |   -73.90732 | 378
  40.643459 |  -73.964586 | 381
 40.6430341 | -73.9656954 | 382

This is all in Postgres 9.3.5.

I'd like to select all latitudes and longitudes for id's corresponding to the interested array for a given deals_id. This seems like it should be doable in a single call, but I can't seem to figure out the syntax. Any recommendations would be greatly appreciated.

Unfortunately = ANY (array) only works with an array literal on the right hand side, not a sub-select.

You need to "normalize" your de-normalized model, using unnest():

SELECT latitude, longitude
FROM userloc
WHERE id IN (SELECT unnest(interested)
             FROM donedeals
             WHERE deals_id = 64);

If deals_id is unique in the donedeals table, another option is to "convert" the id on the left side to an array and then use the "is contained by" operator: <@:

SELECT latitude, longitude
FROM userloc
WHERE array[id] <@ (SELECT interested
                    FROM donedeals
                    WHERE deals_id=64 );

Not sure which one would be faster. You will need to check the execution plan.


SpringData MongoDB - infinite getmore on mongo logs Doing 2 queries on MongoDB through Spring Data on around 160 000 rows, query 1 is executed in few seconds (about 20 results) but query 2 never finish. I'm getting this line over and over on mongodb log:

Sun Apr 27 13:59:06.507 [conn16] getmore analysis.analysisDTO query: { annualPercent: { $gte: 5, $lte: 10 } } cursorid:12575735099603120 ntoreturn:0 keyUpdates:0 numYields: 1 locks(micros) r:255198 nreturned:16 reslen:4264353 133ms
Sun Apr 27 13:59:16.125 [conn16] getmore analysis.analysisDTO query: { annualPercent: { $gte: 5, $lte: 10 } } cursorid:12575735099603120 ntoreturn:0 keyUpdates:0 numYields: 3 locks(micros) r:242018 nreturned:16 reslen:4264832 131ms
Sun Apr 27 13:59:25.748 [conn16] getmore analysis.analysisDTO query: { annualPercent: { $gte: 5, $lte: 10 } } cursorid:12575735099603120 ntoreturn:0 keyUpdates:0 numYields: 4 locks(micros) r:240837 nreturned:16 reslen:4264885 135ms
Sun Apr 27 13:59:35.496 [conn16] getmore analysis.analysisDTO query: { annualPercent: { $gte: 5, $lte: 10 } } cursorid:12575735099603120 ntoreturn:0 keyUpdates:0 numYields: 2 locks(micros) r:195126 nreturned:16 reslen:4265303 114ms
Sun Apr 27 13:59:45.418 [conn16] getmore analysis.analysisDTO query: { annualPercent: { $gte: 5, $lte: 10 } } cursorid:12575735099603120 ntoreturn:0 keyUpdates:0 numYields: 4 locks(micros) r:230168 nreturned:16 reslen:4265473 126ms
Sun Apr 27 13:59:55.007 [conn16] getmore analysis.analysisDTO query: { annualPercent: { $gte: 5, $lte: 10 } } cursorid:12575735099603120 ntoreturn:0 keyUpdates:0 numYields: 2 locks(micros) r:228439 nreturned:16 reslen:4265817 135ms
Sun Apr 27 14:00:13.490 [conn16] getmore analysis.analysisDTO query: { annualPercent: { $gte: 5, $lte: 10 } } cursorid:12575735099603120 ntoreturn:0 keyUpdates:0 numYields: 2 locks(micros) r:180845 nreturned:16 reslen:4265940 102ms
Sun Apr 27 14:00:32.978 [conn16] getmore analysis.analysisDTO query: { annualPercent: { $gte: 5, $lte: 10 } } cursorid:12575735099603120 ntoreturn:0 keyUpdates:0 numYields: 2 locks(micros) r:132410 nreturned:16 reslen:4265572 106ms
Sun Apr 27 14:03:06.571 [conn16] getmore analysis.analysisDTO query: { annualPercent: { $gte: 5, $lte: 10 } } cursorid:12575735099603120 ntoreturn:0 keyUpdates:0 numYields: 2 locks(micros) r:205062 nreturned:16 reslen:4266662 107ms

Query 1:{ annualPercent: { $gte: 10, $lte: 1000000 } }

Query 2:{ annualPercent: { $gte: 5, $lte: 10 } }

I'm doing a big update of every rows just before theses queries, should I leave time to mongodb to re-index all ?

What the logs means ?

And how to solve that ?(I'm not using any special features of mongo driver or spring data)

The lines mean that each [getMore][1] (essentially each iteration on the cursor) is returning 16 documents and that each one is 4MB (the default max batch size limit).  Essentially it is taking a little over 100ms to fetch the documents and return them, and they seem to be around 250KB each.

Note that the queries are yielding the lock, so they are likely not in memory and are being paged off disk (this is not the only reason that it might yield but it is the most likely).

Here's a quick breakdown of the interesting fields:

nreturned:16 = number of documents returned by the getmore
numYields: 4 = number of times that getmore yielded the lock
locks(micros) r:240837 = the time in microseconds that the read lock was held in total (r), which can be greater than the total execution time
reslen:4265572 = the size of the result in bytes (~4MB)
135ms = the time to complete the getmore operation (100ms is the default threshold for logging slow queries)

If I were to hazard a guess I would think that the { $gte: 5, $lte: 10 } matches an awful lot more data than the { $gte: 10, $lte: 1000000 } criteria, and that all of that data is not fitting into memory, but it is hard to be sure with such a limited snapshot of the activity.


How SQL Server determines the data transfer rate We are having some issues with the data transfer rate when extracting data from SQL Server. Our scenario is as follows:

SQL Server 2008, in a Windows Server 2003 with 32 GB RAM (located in Guatemala).
Windows Server 2012 R2, in a EC2 instance in AWS (Virginia USA), with 16 GB RAM.
For extraction we are using Pentaho Spoon, extracting ~13 tables at the same time.

The issue is because sometimes tables are loaded fast, but sometimes one or two tables are loading very slow. Looks like the data transfer sometimes is really bad for those tables, transferring at 50 rows/s, instead of 2,000 rowd/s (should be normal).

Almost all the times, SQL Server shows in the Activity monitor a Wait Type: ASYNC_NETWORK_IO, which I guess is due the network activity. Even when the data is loaded fast, that wait type is displayed.

How SQL Server determines the transfer rate for each table?

Is it based on indexes or network activity?

The application which is consuming the data, is processing in batches of 5000 rows. It is receiving the data and inserting into a stage database every 5000 rows, even inserting into a flat file. Therefore, I don't think the application is processing the data row by row.

Locally, the performance is better (it's reasonable) but there is always a difference among some tables. Some of them are loaded faster than others. It's weird. I'm not a DBA, so I think it could be something related to indexes? Or something about DB tuning.

The ASYNC_NETWORK_IO wait type indicates that SQL Server has finished doing the work it has been asked to do and is waiting for the consuming application to confirm that it has received all of the data.

This is normally due to the application consuming the data in an inefficient manner. The classic is if the application is processing the data row by row. Another possibility is that the server, that the application is running on, is under performing and causing the application to slow.

With that said it is possible the network is contributing to the problem. A ping from the application server to the SQL Server will show the time it takes to get information across the pipe. If this is near to your wait time then it may indicate your pipe is not fast enough.

  The issue is because sometimes tables are loaded fast, but sometimes
  one or two tables are loading very slow. Looks like the data transfer
  sometimes is really bad for those tables, transfering at 50 rows/s,
  instead of 2,000 row/s (should be normal).

At a guess, this sounds like the application is processing the data row by row.  The fact that the slow processing is intermittent I would say that it's very unlikely to be the network.


Merge join turns to hash match when adding a 2nd join I would like to educate myself on what goes on under the hood in SQL Server, so I'm about to delve into Grant Fritchey's book Dissecting SQL Server Execution Plans. As it's 181 pages I just wanted to ask this simple question, which is what first got me interested in this in the first place - hopefully it will pique my interest (plus I'm too impatient to wait until I've waded through this tome to get some kind of answer!)

I am using this miniature version of Northwind to run this simple query:

SELECT Orders.OrderID
  ,Orders.OrderDate
  ,[Order Details].UnitPrice
FROM Orders
JOIN [Order Details]
  ON Orders.OrderID = [Order Details].OrderID

Which gives the following execution plan:

Which seems sensible, given both tables have indexing / sorting on the joining fields. But if I add another table (products) like so:

SELECT Orders.OrderID
  ,Orders.OrderDate
  ,[Order Details].UnitPrice
  ,Products.ProductName
FROM Orders
JOIN [Order Details]
  ON Orders.OrderID = [Order Details].OrderID
JOIN Products
  ON Products.ProductID = [Order Details].ProductID

Suddenly I have Hash matches. This seemed odd to me (remember I'm just beginning to learn this stuff!), as I thought hash matches were for large, unsorted joins. Why would SQL think one join type is okay in the first query and not the second, even though surely they are joining the same number of rows / same indexes etc between Orders and Order Details in both queries?

Orders has 830 rows, Order Details 2155 and Products 77.

OrderID has a clustered indexes on Orders and Order Details, and ProductID has a non-clustered index on Order Details and clustered on Products.

Thanks

I think this stack exchange link gives a good rundown of why it is doing what it is doing, and I believe it has most to do with the size of the results sets and how they are indexed/sorted in your case.    Order and Order Details, from this database are 830 rows  and 2155 rows respectively.   These are similar results sets where one has 2 rows (roughly) for every one row in the other table.   Order Details is clustered on  OrderID, ProductID - meaning that the table is also sorted that way.   Orders is obviously clustered on OrderID as well, meaning that it is also sorted on orderid.   I believe this makes it easy to merge these two data sets that are already sorted in this way together.

Then you throw in Products.    If you run the query

SELECT * FROM dbo.[Order Details] AS od
ORDER BY OrderID, od.ProductID

This is basically showing you what the logical order of the Order Details table is.   And if you look at it from a products perspective it looks basically random.   So now you are throwing in a join to a table that is only 77 rows and where the sorting does not really match up.

I believe that is why you see the Merge Join for the first and the Hash for the second.

Read that link above as it gives a nice description of these things and how they work.    Another good one is at this other SE article.

Lots of information in those two and at many other places (including your book).


Best practices to secure mysql I don't have that much knowledge about mysql and i would like to secure a MySQL server.

We have a few VPS running around the world (6 currently) and  they all need to store information in our mySQL server.

Each VPS will run 1 to 3 scripts and each script is storing 100 rows in our main table.
With our estimation our main table will contain 20 000 000 rows and then will get 10k to 20k rows a day.

Currently I was thinking about allowing those 6 vpn to connect to our mysql server and a different username/password for each script. I was also thinking of limiting the rights to insert only for the script.

What do you guys think? Are my plans on the right directions? Any other advice?

When it comes to securing mysql, as sson as you install mysql, you must run the script mysql_secure_installation. If mysql is already in production for a while, you can perform the steps of the script manually with a lot of caveats.

For example, you have to erase all entries in mysql.db whose db column's first four(40 characters are 'test'. : Please see MySQL : Why are there "test" entries in mysql.db? (February 17, 2012)

You must drop anonymous users

MySQL error: Access denied for user 'a'@'localhost' (using password: YES) (January 19, 2012)
Weird MySQL Users Been Created (eg. bug115166_10073) and not by me  (September 28, 2011)

Here are my other posts on securing mysql

Limiting database security (June 6, 2012)
Is this a normal set of MySQL privileges? (September 11, 2011)


Create A Pivot With No SUM() in MySQL I've seen a lot of examples using MySQL to create a PIVOT but they all use SUM() - using this DDL how can I create a PIVOT in MySQL
Create Table `data`
(
   callDate text
  ,callerName text
  ,callLength text
);

Insert Into `data` (`callDate`, `callerName`, `callLength`) Values
('07/01/2020', 'Client A', '0:00:23'),
('07/01/2020', 'Client B', '0:15:23'),
('07/01/2020', 'Client C', '0:7:10'),
('07/02/2020', 'Client A', '0:01:23'),
('07/02/2020', 'Client B', '0:12:23'),
('07/02/2020', 'Client C', '0:3:10'),
('07/03/2020', 'Client A', '0:011:23'),
('07/03/2020', 'Client B', '0:22:23'),
('07/03/2020', 'Client C', '0:23:10');

As i said in the comment simply Replace the SUM with what ever you need in your

Create Table `data`
(
   callDate text
  ,callerName text
  ,callLength text
);

Insert Into `data` (`callDate`, `callerName`, `callLength`) Values
('07/01/2020', 'Client A', '0:00:23'),
('07/01/2020', 'Client B', '0:15:23'),
('07/01/2020', 'Client C', '0:7:10'),
('07/02/2020', 'Client A', '0:01:23'),
('07/02/2020', 'Client B', '0:12:23'),
('07/02/2020', 'Client C', '0:3:10'),
('07/03/2020', 'Client A', '0:011:23'),
('07/03/2020', 'Client B', '0:22:23'),
('07/03/2020', 'Client C', '0:23:10');

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
          'MAX(IF(callDate = "',
          callDate,
          '" ,`callLength`, NULL)) AS "',
          callDate,'"'
        )
  ) INTO @sql
FROM
   `data`;
SET @sql = CONCAT('SELECT callerName,', @sql, ' FROM  `data` GROUP BY callerName');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

callerName | 07/01/2020 | 07/02/2020 | 07/03/2020
:--------- | :--------- | :--------- | :---------
Client A   | 0:00:23    | 0:01:23    | 0:011:23
Client B   | 0:15:23    | 0:12:23    | 0:22:23
Client C   | 0:7:10     | 0:3:10     | 0:23:10

db<>fiddle here
It is quite ugly and it has the same limitation as the explained here

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
          'MAX(IF(callDate = "',
          callDate,
          '" ,`callLength`, NULL)) AS "',
          callDate,'"'
        )
  ) INTO @sql
  FROM
   `data`;
  SET @sql2 = NULL;
  SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
          'SEC_TO_TIME( SUM( TIME_TO_SEC(TIME_FORMAT(IF(callDate = "',
          callDate,
          '" ,`callLength`, NULL), "%H:%i:%s")))) AS "',
          callDate,'"'
        )
  ) INTO @sql2
FROM
   `data`;
SELECT @sql2;
SET @sql = CONCAT('SELECT callerName,', @sql, ' FROM  `data` GROUP BY callerName UNION SELECT "total",',@sql2,' FROM  `data`');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

| @sql2                                                                                |
| :---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| SEC_TO_TIME( SUM( TIME_TO_SEC(TIME_FORMAT(IF(callDate = "07/01/2020" ,`callLength`, NULL), "%H:%i:%s")))) AS "07/01/2020",SEC_TO_TIME( SUM( TIME_TO_SEC(TIME_FORMAT(IF(callDate = "07/02/2020" ,`callLength`, NULL), "%H:%i:%s")))) AS "07/02/2020",SEC_TO_TIME( SUM( TIME_TO_SEC(TIME_FORMAT(IF(callDate = "07/03/2020" ,`callLength`, NULL), "%H:%i:%s")))) AS "07/03/2020" |

callerName | 07/01/2020 | 07/02/2020 | 07/03/2020
:--------- | :--------- | :--------- | :---------
Client A   | 0:00:23    | 0:01:23    | 0:011:23
Client B   | 0:15:23    | 0:12:23    | 0:22:23
Client C   | 0:7:10     | 0:3:10     | 0:23:10
total      | 00:22:56   | 00:16:56   | 00:56:56

db<>fiddle here


What are the drawbacks of using Galera Cluster instead of Master/Slave Replication? What are the drawbacks of using Galera Cluster instead of regular Master/Slave Replication? Galera's 0 slave lag time, Synchronous replication and no Single point of failure seem very appealing then why isn't Galera cluster as common?

Because like any other optimization, it doesn't fit every workload.

Galera can get overwhelmed by a high rate of transactions, or when transactions update many rows. It can also make your applications experience delays on COMMIT as the cluster is synced up.

Galera doesn't update other nodes synchronously, either. It just transmits worksets synchronously. In this way, it's a bit like standard replication in semi-synchronous mode. Therefore there's still a small chance of reading stale data from another cluster node. There's an option you can set to force SELECT to wait until the queue of worksets has updated the database, but this means you have delays on SELECT. And even a chance of getting a deadlock on SELECT, which seems counter-intuitive.

Galera is brilliant, but not a one-size-fits-all technology. There are still good reasons to use asynchronous replication.


PostgreSQL: Function to reformat a string Suppose I have a string such as:

abcdefghi

and I would like to reformat it as:

abc def ghi

(that is, I have added some formatting spaces to the string).

Is there a PostgreSQL function which will do that? Something like:

reformat(abcdefghi,'xxx xxx xxx')

I know there isn’t the above function, but there might be a built in function to do something like that.

If not, I am happy to write such a function, but I though I would check.

Use regexp_replace

SELECT regexp_replace('abcdefghiabcdefghi', '(.{3})', '\1 ', 'g');
      regexp_replace
--------------------------
 abc def ghi abc def ghi
(1 row)

This inserts a space every three characters.


The Filegroup PRIMARY for the database CRM running out of space I have a db for which I am getting error as shown below.

The Filegroup «PRIMARY» for the database "CRM" in SQL instance "MSSQLSERVER" on computer "prod" is running out of space.

sqace details in MB are as below: mdf file now has reached 249 GB.

CRM mscrm       D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\CRM.mdf  249157
CRM mscrm_log   E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\CRM_log.LDF 5137

D and D drives are having more than 50 GB free space. C and temp has more than 30 GB and 20 GB free space each .. No drive is running out of space.

growth is set as auto with no restriction at this database.

Any clues ?

Thanks
Hungry DBA

This isn't an error from your SQL Server. At least I couldn't find it in sys.messages. (If you didn't post the exact error message or if you have an additional error number, please adjust your post.)

You have some monitoring software which assumes that you want to get notified when a database is close to full (for some filegroup). You can choose to ignore that notification and rely on the autogrow functionality in SQL Server, or you can expend the size of the file manually. Up to you.

Manually growing can be better since you control when you do this. Not that growing a file block general operations, but if you wait for an autogrow to kick in, then you have one or more users that suffers during that autogrow (those who need more space at that point in time).

Also, make sure that you have reasonable autogrow settings and consider whether you want to turn on Instant File Initialization.


How far should you go with normalization? I have a decent amount of data in a database. I have well formed tables and good relationships between them with some redundancy in my data. But how far should I go with normalization? Are there performance drawbacks to too much normalization?

You should go as far as you should, and no further. Of course. ~ The problem may be that this
is a bit of an art, and it's why this isn't a pure science.

Our main product is an analysis and reporting system, and so in that regard, we have quite a few detail records. We initially had it designed with lots of joins on a common ID for some of the child records, but we found that if we denormalized a couple of fields we could cut out a LOT of joins and we could take away a lot of performance headaches.

But we only knew that because we 1) created a "normalized" design, 2) started using it, 3) profiled the actual performance after hundreds of millions of rows across dozens of tables.

The end story is that until we profiled we couldn't know for sure what was going to work for us. We liked the idea of normalizing so we could update more easily, but in the end actual performance was the deciding factor. That's my advice for you: Profile, profile, profile.


will replication from 5.5.20 to 5.0.XX server work? I gave two machines which run on two entire different versions of MySQL server. I want to do the replication from 5.5.20 to 5.0 server. Will that be possible?

Because I remember when I transferred data from 5.0 to 5.5.20, I had to run the command upgrade table to work on on 5.5.20?

Please suggest what the best possible way, if replication is not possible.

It is impossible. The reason?

The binary logs are incompatible.

To be more specific, the binary logs are not backwards compatible.

Here is why:

An empty binary in ...

MySQL 5.0 and back is 98
MySQL 5.1 is 106
MySQL 5.5 is 107

I have made reference to this subtle difference in ServerFault

https://serverfault.com/questions/231369/mysql-master-binlog-corruption/231386#231386
https://serverfault.com/questions/273407/yum-update-mysql-from-5-1-to-5-5/278829#278829

You can setup replication from an older MySQL version to a newer MySQL version, not the other way around.

If you ever need data from MySQL 5.5 ported back to an older version, your only recourse to mysqldump all databases EXCEPT the mysql schema.

DBLIST=`mysql -u... -p... -AN -e"select group_concat(schema_name separator ' ') from information_schema.schemata where schema_name not in ('information_schema','mysql')"`
MYSQLDUMP_OPTIONS="--add-drop-database"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines --triggers"
mysqldump -u... -p... ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} > MySQLData.sql

You can extract the grants as SQL using pt-show-grants. Here is my personal emulation if it:

mysql -u... -p... --AN -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -u... -p... --skip-column-names -AN | sed 's/$/;/g' > MySQLUserGrants.sql
echo "flush privileges;" >> MySQLUserGrants.sql

The reason you have to dump the grants as SQL is the fact that the columns in the mysql.user table changes upon upgrade.

When you do desc mysql.user; or select count(1) from information_schema.columns where table_schema='mysql' and table_name='user'; you get the following:

37 columns in MySQL 5.0
39 columns in MySQL 5.1
42 columns in MySQL 5.5

All the mysql upgrade does is morph mysql.user to accommodate new permissions. Performing the dump of the grants yields SQL that is independent of the mysql.user layout.

Once you have made these two dump files (MySQLData.sql and MySQLUserGrants.sql), simply load them into a new installation of an older MySQL version.


mysql is locked; can't kill query I have a mediawiki running on my local machine and just started running mysqldump last night:

$ mysqldump -h localhost -u  wikiuser --default-character-set=UTF8 wikidb --add-locks --skip-lock-table> ~/Dropbox/admin/wikimedia_backup/backup.sql

But now mediawiki protests

  Warning: The database has been locked for maintenance, so you will not
  be able to save your edits right now. You may wish to cut-n-paste the
  text into a text file and save it for later. The administrator who
  locked it offered this explanation: Dumping Database, Access will be
  restored shortly

This sent me looking for locks in the wikidb database. I ran

UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

, but problem persists.

My sqldump statement runs with the  --skip-lock-table options (I think I should add or replace with  --add-locks?)

I reran the command with both options.

I also tried to kill the process outright:

mysql> SHOW FULL PROCESSLIST \G;
*************************** 1. row ***************************
     Id: 77
   User: wikiuser
   Host: localhost
     db: wikidb
Command: Query
   Time: 0
  State: NULL
   Info: SHOW FULL PROCESSLIST
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> kill query 77;
ERROR 1317 (70100): Query execution was interrupted

So, two questions:

how do I lift the existing lock;
how should my options be in the mysqldump call to avoid the problem, prospectively?

The commenter was correct -- your kill query 77 was killing your own thread's query... as was indicated by the fact that the row from the processlist indicated that thread 77 (you) was the thread that was currently running the SHOW FULL PROCESSLIST command.
The way you fix your problem is by finding the step you took before running mysqldump, and undoing it.  This does not appear to be a MySQL issue.
From the Mediawiki Manual: Backing Up a Wiki...

Mysqldump from the command line
The most convenient way to create a dump file of the database you want to back up is to use the standard MySQL dump tool mysqldump from the command line. Be sure to get the parameters right or you may have difficulty restoring the database.
First insert the following line into LocalSettings.php
$wgReadOnly = 'Dumping Database, Access will be restored shortly';
this can be removed as soon as the dump is completed.

Your database isn't locked... your wiki is locked, because you locked it before you ran the backup.
If the $wgReadOnly variable in Mediawiki's LocalSettings.php configuration file is set, this locks the wiki against writes and offers the message you put in the string as an explanation to users of the wiki.
This suggests that you added this line while reading the Mediawiki docs but did not remove the line when you were done with the backup, unrelated to mysqldump or MySQL.  When mysqldump (or any other client) disconnects, it is impossible for locks to linger even accidentally inside MySQL because locks can only be held as long as the session/thread holding the locks is still connected.
If your wiki isn't massive, and having your wiki become briefly unresponsive while the dump is running isn't a problem, then you don't need to lock the wiki at all.  I have a scheduled job that runs against the MySQL server that runs my Mediawiki installation and I don't lock the wiki to take backups.  If your wiki is mission-critical and you want zero potential disruption, you could also set up a MySQL replication slave and make backups against the slave instead of the master.  If your server has the resources for it, you could run the slave on the same machine as the master, on a different port other than the default 3306.


Prevent users creating constraints or indexes with no name I have done some searching but haven't found a solution to my problem.

I started with some databases where users didn't care to name PK constraints, foreign keys or indexes, ending up with system generated names like PK__CarRenta__3213E83F2E5BD364. On mssqltips there's a great article by Aaron Bertrand about how to rename these objects using a stored procedure.

So that's great...but how do I avoid users or applications creating new objects without names which are then assigned a system generated name by SQL Server?

I have already read an article by the same great Aaron Bertrand on how to use policy based management to enforce naming conventions. However it seems that this is only good for notification after the horse has already bolted (or the object has been created).

Can you think of a way to automatically roll back statements that try to create an object without a name?

The simplest way to do this (though it makes me shudder a bit) is to use a database level DDL trigger with EVENTDATA. There is nothing specific here for constraints, so you'd have to use your imagination (depending on what you're attempting to actually accomplish) with the DDL Event groups, and some manual parsing. Here's an example to get you started.

CREATE DATABASE IndexRollback;
GO

USE Indexrollback;
GO

CREATE TRIGGER IndexNamingTrigger
ON DATABASE
FOR CREATE_INDEX, ALTER_INDEX
AS
BEGIN

    -- this doesn't need to be here (can be inlined), just did it for readability
    DECLARE @ObjName sysname
    SELECT @ObjName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')

    -- check our naming, if it doesn't start with IDX, throw it away and be super mean about it
    IF(LEFT(@ObjName, 3) <> 'IDX')
    BEGIN
        --log to a table or something
        PRINT 'Dont''t be daft, look at the naming convention spec!'
        ROLLBACK
    END
END
GO

CREATE TABLE Test
(
ID  INT NULL
)
GO

CREATE CLUSTERED INDEX IDX1 ON dbo.Test(ID)
GO
--success

CREATE NONCLUSTERED INDEX HAHAHAHAHA ON dbo.Test(ID)
GO
-- sad trombone


SQL Server transaction log file refuses to grow Problem: I have a SQL Server database sitting on a 1 TB drive. The log will not grow past 5 MB even when the MAXSIZE is set much larger than 5 MB.

Error received:

  The transaction log for database 'Temp' is full. To find out why space
  in the log cannot be reused, see the log_reuse_wait_desc column in
  sys.databases

Questions:

How do I fix this?
Why is this happening?

Code:

CREATE DATABASE Temp ON PRIMARY(
       NAME = Temp
       , FILENAME = 'C:\\Temp.mdf'
       , SIZE = 2MB
       , FILEGROWTH = 10%)
LOG ON (
       NAME = Temp_Log
       , FILENAME = 'C:\\Temp.ldf'
       , SIZE = 1MB, MAXSIZE = 70MB
       , FILEGROWTH = 10%)

Result of select name, log_reuse_wait_desc from sys.databases where name = 'temp'; :

  name, log_reuse_wait_desc
  Temp, NOTHING

Additional Information:

Sql Server manager says maxsize = 5mb and restricted growth
Recovery mode Simple
Database is handling hundreds of individual SQL Statements as it is for a data integration tool

Updated Addition information:

When the SQL statement is run directly in the SQL server manager it creates successfully and does not cause errors on implementation...On the other hand if the create database statement is run progmatically using System.Data.SqlClient and the exact same syntax it creates the Database with all defaults. I have to additionally supply alter database statements to achieve the proper behavior.

Code to run Queries:

public void AcuConvert()
        {
            using (DestD)
            {
                SqlCommand command = new SqlCommand();
                DestD.Open();
                command.Connection = DestD;
                foreach (var item in Entity.SqlDestinationQueries.ToList())
                {
                    command.CommandText = item.Query;
                    command.ExecuteNonQuery();
                }
                foreach (var item in Entity.SystemQueries.ToList())
                {
                    command.CommandText = item.Query.Replace("@Sys", SysD.Database);
                    command.ExecuteNonQuery();
                }
                foreach (var item in Entity.InsertQueries.ToList())
                {
                    command.CommandText = item.Query.Replace("@Source", SourceD.Database); ;
                    command.ExecuteNonQuery();
                }

            }
        }

To fix the issue in the short term manually grow the log file since that's best practice anyway.

If the log file will end up being multiple GB grow it in 1 - 4 GB increments to get there.

If you don't know how big it's going to get I would recommend using at least 1 GB increments.  This is a blocking operation so it should be done during a low use time if at all possible.

To discover why your problem is happening check for errors in the SQL error log, not just error message from the failed statement.  There may be an error stating why it couldn't grow.

You can also look at activity monitor to see what the sessions that are having problems are waiting on.  Right click on the instance in SQL Server Management Studio (SSMS) and click on "Activity Monitor."  Once you have it open look at the blocking chain (Blocked By and Head Blocker) and the Wait Type to see what is causing the issue.

To determine why the DB is getting created with a MAXSIZE of 70 run Profiler to catch the CREATE DATABASE statement.  That will tell you what query is getting run so you know if SQL is getting the correct query or not.


Why does TLS1.2 break connections to SQL Server? NOTE: Requested to move the question from StackOverflow to dba

Don't know if this is really a SQL Server problem or not.

Client moved to TLS 1.2, it broke an app that connects to SQL Server 2017 and it got dumped on my lap.

Being a Linux person all I can say is when they went back to TLS 1.1 it worked and here is the only error message I can glean in all this mess:

  dbnetlib connectionopen secdoclienthandshake ssl security error

In SS Studio, when I click on the DB I get this error message:

  Error connecting to 'hostname'.

  Login failed for user 'CORP\myaccount'. (.Net SqlClient Data Provider)

  Server Name: hostname
  Error Number: 18456
  Severity: 14
  State: 1
  Line Number: 65536

  Program Location:

  at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
  at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
  at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
  at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
  at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
  at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
  at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry)
  at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
  at System.Data.SqlClient.SqlConnection.Open()
  at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
  at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ConnectToServer(UIConnectionInfo connectionInfo, IDbConnection liveConnection, Boolean validateConnection)

TLS 1.2 was turned/put on the server that hosts SS.

Older versions of SQL Server and SQL Server clients simply don't support TLS 1.2.  Please check out  TLS 1.2 support for Microsoft SQL Server to see what you might need, or just upgrade SQL Server to the latest service pack/update, and the client to the latest version.  Since I see you're using SQL Server 2017, you should be fine on the server side and you should just need to address this on the client side.  Based on your stack trace, I'm thinking that a .NET upgrade will address this for you.

We have numerous connections using various versions and platforms of SQL clients (including jTDS) connected using TLS 1.2 with various versions of SQL Server.  The latest official release of jTDS doesn't support TLS 1.2, so you have to download an unreleased dev version, or convert it to JDBC, which is really the best option as jTDS is a dead project.


Suspect (Torn) Pages regarding tempdb? I have a virtualised SQL Server 2005 (SP3) that reported the following message:

Database_Name, Database_Id, File_Id, Event_Type, Event_Type_Desc, Error_Count, Last_Update_Date
tempdb, 2, 1, 3, Torn page., 1, 10 Dec 2014 06:32:36:033
tempdb, 2, 1, 3, Torn page., 1, 10 Dec 2014 06:32:36:043
tempdb, 2, 1, 3, Torn page., 1, 10 Dec 2014 06:32:36:050
tempdb, 2, 1, 3, Torn page., 1, 10 Dec 2014 06:32:36:057
...

It is the second time in the past month that I've seen this error on this particular server - and in both cases it was in tempdb, with Event_type = 3 (i.e. Torn Page) only. It has never been Event_type = 1, which corresponds to an 823 (disk error) or 824 error.

Running the command:

DBCC CHECKDB WITH ALL_ERRORMSGS, PHYSICAL_ONLY

returns:

DBCC results for 'tempdb'.
DBCC CHECKDB will not check SQL Server catalog or Service Broker consistency because a database snapshot could not be created or because WITH TABLOCK was specified.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'tempdb'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

In other words, everything seems fine, but why then is suspect pages getting recorded?

Other actions taken:

HDDScan / chkdsk run to make sure its not a drive error (there is also nothing relevant recorded in the windows event logs around the time of the error).
PAGE_VERIFY is currently set to NONE (default?) - investigated the possibility of setting it to CHECKSUM, but that's only possible for tempdb in SQL Server 2008 onwards.
Stopped SQL server, deleted existing tempdb files, restarted it (based upon a comment in
the question -> How might a corrupt partition in TempDB result in DBCC CHECKDB reporting no error
Read the similar questions (to the right) but none of them are relevant.

Is there anything else I should be checking / doing, or should I simply disregard this because its TempDB (i.e. transient data)?

Thanks for any assistance/input.

You need to work with your storage vendor to figure out why the writes are being torn. If it's happening to tempdb it's very likely that it's happening to another database as well.

This is especially true if it's happened more than once.


SQL database scripts in EXE or DLL format I work with SQL Server 2016 and I will need to send the generated scripts in some releases to our client, so they can install the package in UAT and Production.
I would like to send the scripts in DLL or EXE format, so they can't see the content. Is it possible?
Do I need some third party applications? If yes, which ones?

send the generated scripts ... to our client ... they can't see theirs content.

This is technically impossible, but may be sort-of implemented depending on what your threat model is. The problem is that the resulting distributable must be able to unpack/decode/decrypt the scripts such that it can submit them to SQL Server so it must contain all the required algorithms and keys.

If your threat model involves trying to stop a determined snooper (a competitor perhaps, or a black-hat looking to find holes that as-yet-unpatched installations are still vulnerable to) from seeing the code then that is impossible with a simple distributable. No matter how many layers you add they can just unpick them by analysing the distributable.

Secondly, if you make it enough hassle that they don't want to do that they have another inspection route: they could just intercept the communication with SQL Server and watch the code as it flows through. Yes TDS streams are usually encrypted, but they are running the package against their SQL server so that control both the client and the server so can control (and therefore break for their own purposes) that encryption. Thirdly, they can always just look in the database, and compare the current to a pre-patch backup if that would make the changes more obvious, the only way around this is for you to host the DB perhaps running your product in a SaaS-only manner.

If your concern is merely more opportunistic snooping, then there are many installation packagers[1] out there that support creating self-extracting executables which would run a program in the archive to run the scripts also in the archive and clean up as best as is possible afterwards, and support encrypting or otherwise obfuscating the content in transit.

[1] I'll not mention specific ones as there are many (free, Free, OpenSource, and commercial), I don't know enough about any specifically to authoritatively compare them, I know nothing of your dev and production environments (other than they include SQL Server), and anyway shopping/recommendation questions/answers are discouraged on StackExchange for reasons explained in detail elsewhere.

I would recommend against using this sort of tool for this purpose though: you are adding a layer of complication that is otherwise unnecessary and might make debugging installation problems that might occur more problematical for you and therefore your client (which won't look good).

If you do use such a product, do so as a convenience: making installation a single (or at least minimal) step process for your clients in order to reduce the risk of human error, rather than as a security measure.

If your concern is more "human factors" such as bad language in code comments or other sources of embarrassment, then discipline and lack of code reviews is the problem, not distribution security, and this needs to be fixed earlier in your process.

  I will need to send the generated scripts in some releases

As a side note: if by this you mean that you are generating scripts from SQL Server in order to distribute, then you may need to improve your dev process. You should be able to build everything including upgrade packages from source control rather than generating scripts from your dev databases. This may take effort to setup to run smoothly, but it could save you from a collection of failure types in future.


Should there be a temporary table to hold Order and OrderItems? After trying out a few open-source restaurant  POS, I still couldn't decide if there should be a temporary to hold Orders and OrderItems?

I was initially thinking to have temporary tables and permanent tables for Orders and OrderItems and move a record from temporary tables to permanent tables after an order is Closed/Voided. So, temporary table is not immutable and permanent table is immutable. But it seems having both temporary table and permanent tables complex the process because records have to move from temp to permanent table, delete records in the temp table.

Will it be better to have just one permanent table?

I personally don't see an advantage to separating them, especially given that you will probably have a primary key on the Orders table that the OrderItems table will need to reference.  I normally handle this by having a nullable column for "closed" and/or "voided" that is stamped with now() on the appropriate event but left NULL until then.  If you're concerned about having too many open orders, you could have a garbage collection script that runs during off-peak hours to remove them.


Transactional Replication to DB on Same Server instance We have a SQL Server 2012 instance that contains a database with data for many product lines.

We are looking for a way to take subsets of this data into another database on the same instance.

This doesn't have to be continuous as we are looking for ways to make it easy to export backups of the subsets that we can send out to vendors (we have a company that loads this data into their own sql server to produce a product we sell, naturally its huge and we want to trim it down to just the product lines they work with on a regular enough basis) or use to load SQLite DB's for use with offline applications.

ProductDB - the superset of all product line data.

ProductLine1DB - A subset identical in structure to ProductDB, but with data only relevant to product line 1.

ProductLine2DB - A subset identical in structure to ProductDB, but with data only relevant to product line 2.

This can be done nightly, weekly, as often as we need to but not a continuous replication.

We don't want to add in distributor servers or even extra subscriber servers. We would like the replication to happen on the same server with the subscriber databases being on the same server.

Is this a good candidate for transactional replication?

Why or why not?

We obviously have other options to load this data, but it seems like this might be a good case to use something like transactional replication. I've heard its powerful but prone to issues when not setup optimally.

Note: I am not a DBA, simply a developer, but we are currently looking for a new DBA and some of us are taking over duties like this temporarily.

Is this a good candidate for transactional replication?

I would use SSIS to incrementally load data (if the data set is too big) or just freshly load the data (if the data is less) every night (or anytime).

This way you dont have to create any publication, distribution database or subscription.

Easy to manage when you do upgrades on your main database - especially changing schema.

Another option would be to use BCP OUT and BULK INSERT IN. I have a script to help if you go with this. Make sure to adjust the script to filter out the data you need using where clause :-)

Note: Don't get me wrong, as I am not telling, you can't use T-Rep, but I don't see it to fit perfectly in your situation. There are better tools that you can use to cater your scenario.


Excel Values Uploading as NULL to database I have an Excel file in which I have many records. When I upload this file to a table in a database (SQL Server) some values save as NULL even though the Excel file does not have NULL or blank fields.

I double checked all data types of the columns to make sure they are of the correct data type but no luck.

However, I tried pasting the values in question into a Notepad file, and pasted  them back into the Excel from Notepad and this worked. The values no longer save as NULL if I do this.

What could be the reason this occurs? Why does the database not "see" these values in Excel?
Is there a quicker way I can check the data before uploading?

Answer originally left as comments

This is a data type issue on Excel's side, and the file was probably created by someone else. By copying and pasting using Notepad, you're stripping all formatting and effectively converting the data types to strings. I'm betting it's a date field or a number that is being formatted in an odd way and the SQL implicit conversion is failing because of a leading apostrophe or something ridiculous. – Randolph West

Unfortunately, importing Excel data is rife with difficulties like this. You'd be far better off to get the source data in another format such as CSV, or better yet, a binary format of some type. – Max Vernon


JOIN and concatenate between tables and UPDATE The server is SQL Server 2008 R2 and SQL Server 2017. Probably a simple answer to this one, but it has me a bit stumped.
Ok so I have two tables, one has basic vendor inventory information and the other is a vendor code lookup to match it to our product database.  The vendor inventory is updated nightly from a downloaded XML file so I don't have control over how it comes in sadly.

What I'm needing to do is JOIN the two tables and UPDATE one of the columns with the concatenation of two of the columns from two different tables.

I have the concatenation done:

SELECT concat(SFXVencode, partnumber) as Pcode
  FROM KeyInventory JOIN Keybrands
    ON keybrands.vencode = keyinventory.vencode

But what I need is the ability to save this into a column in the keyinventory table.

So the concatenated productcode (what matches to our product database) needs to be saved in a column.

This is how far I've gotten, but it's not working currently.

UPDATE KeyInventory
SET KeyInventory.Productcode = (

SELECT concat(SFXVencode, partnumber) as Pcode
  FROM KeyInventory JOIN Keybrands
    ON keybrands.vencode = keyinventory.vencode
        )

I'm needing the KeyInventory.Productcode to be updated to the Pcode

I hope this all makes sense and I would be forever grateful for a fix for this.
KeyBrands

            SFXVenCode
            VenCode

            ACC
            A51

KeyInventory

            PartNumber
            VenCode
            ProductCode

            3102
            A51
            (what I need)

So the Concatenated would be ACC3102 and I need it saved into the KeyInventory table.

Sorry, cannot use html to format the table data.. argh.

Does this work?

UPDATE KeyInventory
SET KeyInventory.Productcode = concat(SFXVencode, partnumber)
FROM KeyInventory
   INNER JOIN Keybrands ON keybrands.vencode = keyinventory.vencode

Might need a little tweaking once you post your DDL...


Turning off auto growth for particular file We have a primary file group with several files each on different disks.  One of the disks is nearing capacity and as such we have had to switch off the auto growth for that file.  Is there anything I need to consider when I do this?

I don't believe I can force a table to be in a particular file when they are all in the same filegroup, so from an admin point of view I haven't been able to think of anything.

I don't believe I can force a table to be in a particular file when they are all in the same filegroup

That's correct, you do not have the direct granular control over file allocations, only filegroup.

With that in mind, you have a few options.  The first one would be to create a new filegroup and isolate whatever tables you need to in order to give you your desired end result of not filling up the drive.  Another one, which I'd argue is probably one of the better options, is to put the file in question in a location with more space, or tack on more space to the existing volume.  I'm guessing you wouldn't be asking this question if that was a possibility, though.

Another route would be to take advantage of the proportional fill behavior that SQL Server implements (which dictates it'll attempt to fill up all filegroup files at the same time) and size out (larger) the other files in the filegroup.  That should translate to a larger percent of the allocations in those other files, as they'll demand more by the ratio of free space (reference this BOL document to further understand proportional fill).  Although I personally think that's a hack and a workaround at best, and will only prolong the inevitable of filling up your drive.


Storing additional FK in a table I've recently seen the following schema and was wondering what the reasons behind it could be. Let's suppose we have three tables A, B and C.

A table has one to many relationship with B, so B has an A_id.
B table has one to many relationship with C, so C has a B_id.

Looks pretty logical so far. However, in the schema I saw C also had A_id as well as B_id. The question is, what could be possible reasons to do so?

The only one that comes to my mind is to speed up the query to get all C entities which belong to A, though I am not sure it's that significant. Has anyone also used the aforementioned schema and why?

The question might seem broad so I'm not sure if it's really Q/A format. I couldn't find any information to read about this design decision since I don't know whether it has a name. If it has, I'd appreciate if someone told me how it's called.

This could be to resolve a data model issue known as the FAN TRAP. There is quite a good youtube video describing it at https://www.youtube.com/watch?v=YKqJ_FjfyVM. The video also goes on to describe another common modeling issue; the CHASM TRAP.


MySQL replication master-slave I'm learning master-slave replication with MySQL and I could get it to work and also use the Percona backup tool to restore a slave. I learnt from this project https://github.com/vbabak/docker-mysql-master-slave

Now I wonder if the master-slave replication must be two separate mysql instances of if it is possible to configure master-slave replication between two databases only instead of needing two separate mysql instances. I think it's not possible.

The reason I want to know is that I want to automate failover and restore scenario and in my environment a new mysql instance always runs on the default port because of infrastructure automation, and therefore it is not possible today to start two mysql instances on the same host machine server, therefore it looks like I need to create two VMs with one master and one slave just to perform the test which is quite overkill for a test scenario and would be slow.

VMs are fine for testing, performance overhead exists but it is at most 30% under high load, not orders of magnitude.

If you want to run multiple database instances on the same machine, what you are looking for is mysqld_multi. You should probably avoid it, though, it is a terrible, messy solution. VMs are a better approach. Or containers if you really feel you have to avoid VMs at all cost.


Query to check the timestamp of a table accessed recently I would like to list out the timestamp of tables which are accessed from my application recently. I would like to know at what time they were accessed so that I can pull which part of application accesses which table?

The query

select * from all_tab_statistics;

provides only the timestamp of  deleted,updated,truncated details but not the timestamp of read details.

I am a normal user and not DBA. Hence I do not have access to all system tables.

If you are the application guy, it may be easier to configure the application to log activity directly from the app layer.  Maybe configure some sort of verbose logging option?

The ALL_TAB_MODIFICATIONS table, which is what I think you mean, is not a reliable source for what you're trying to identify, as it's not updated immediately after a change is made.

  For performance reasons, the Oracle Database does not populate these
  views immediately when the actual modifications occur.

If logging via the application isn't a feasible approach, hopefully you can enlist the help of your DBA.  Point him/her to the Auditing functionality within Oracle, specifically the section on Auditing SQL Statements.  This should be available on both Standard and Enterprise Editions, and this is one way to identify when people are reading data from a table.

What you can do is specify an audit for a given DML command, and log that acitvity to the audit log.  The Tutorial on that page will run you through the entire process, so I'm not going to go into specific syntax in this post.

A few words of warning though.  If you have a lot of activity, you will want to be very particular about what you audit and for how long.  You will want to make sure you setup the appropriate maintenance process to purge the log after a reasonable amount of time.  A good script can be found here.  Additionally, you will also want to ensure your audit table is moved out of the SYSTEM tablespace.  To do that, you'll need to take advantage of the DBMS_AUDIT_MGMT package.

Auditing is not something I would ever recommend you setup quickly or without forethought.  Be careful about what you collect as the logs can fill quickly, especially within environments under heavy activity.


Delete inactive users - how to prevent jobs and reports to stop working We have several Microsoft SQL servers in our organization and the previous DBA have not performed user reviews on any of them, so some servers are filled with inactive local users. I have a script that looks at their last logon date:

SELECT MAX(login_time) AS [Last Login Time], login_name [Login]
FROM sys.dm_exec_sessions
GROUP BY login_name
ORDER BY [Login] ASC;

But how can I create a similar script that lists all jobs and reports made by the user, so that they won't stop working when and if I delete the inactive users? Are there any best practices when it comes to deleting users from Microsoft SQL Servers?

The company I work for writes a free script called sp_Blitz that will warn you when Agent jobs are owned by users.

It'll warn you about a whole bunch of other stuff too, and is a pretty decent way to give your SQL Servers a free health check.


An error occurred on the always on dashboard I have a setup of Windows Server Failover Cluster and i am using SQL Server Service with one DC, One Primary Server and One Secondary Server.

During Using SQL Server Management Studio, when i click at Show Dashboard Under "Always On High Availability", It shows that error i.e.

An error occurred on the always on dashboard

How to resolve this issue ?

In short

The issue is due to the dashboard executing queries referencing the required_copies_to_commit column from the master.sys.availability_groups dmv that was changed to required_synchronized_secondaries_to_commit.

The solution is to upgrade SSMS (testing done on the 17.4 version).

SSMS 17.0

When retrying this on a machine with SSMS 17.0 installed

The same error occurs:

  invalid column name required_copies_to_commit

This is due to the query being different when opening the dashboard on 17.0:

      select * into #tmpag_availability_groups from master.sys.availability_groups
    ...
    SELECT
    CAST(
            serverproperty(N'Servername')
           AS sysname) AS [Server_Name],
    AG.name AS [Name],
    ISNULL(AG.automated_backup_preference, 4) AS [AutomatedBackupPreference],
    ISNULL(AG.failure_condition_level, 6) AS [FailureConditionLevel],
    ISNULL(AG.health_check_timeout, -1) AS [HealthCheckTimeout],
    AR2.replica_metadata_id AS [ID],
    ISNULL(arstates2.role, 3) AS [LocalReplicaRole],
    ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
    AG.group_id AS [UniqueId],
    ISNULL(AG.basic_features, 0) AS [BasicAvailabilityGroup],
    ISNULL(AG.db_failover, 0) AS [DatabaseHealthTrigger],
    ISNULL(AG.dtc_support, 0) AS [DtcSupportEnabled],
    ISNULL(AG.is_distributed, 1) AS [IsDistributedAvailabilityGroup],
    ISNULL(AG.cluster_type, 0) AS [ClusterType],
    ISNULL(AG.required_copies_to_commit, 0) AS [RequiredCopiesToCommit]
    FROM
    #tmpag_availability_groups AS AG
    LEFT OUTER JOIN #tmpag_availability_group_states as agstates ON AG.group_id = agstates.group_id
    INNER JOIN #tmpag_availability_replicas AS AR2 ON AG.group_id = AR2.group_id
    INNER JOIN #tmpag_availability_replica_states AS arstates2 ON AR2.replica_id = arstates2.replica_id AND arstates2.is_local = 1
    ORDER BY
    [Name] ASC
   ...

A column that no longer exists is used

ISNULL(AG.required_copies_to_commit, 0) AS [RequiredCopiesToCommit]

Instead of the correct column on newer versions of SSMS:

ISNULL(AG.required_synchronized_secondaries_to_commit, 0) AS [RequiredSynchronizedSecondariesToCommit]

SSMS 17.4

Disclaimer: versions 17.1, 17.2 and 17.3 might also show the correct dashboard. I did not test these yet

When tracing the queries being run by ssms (17.4 or higher) when the dashboard correctly opens, this query runs

select * into #tmpag_availability_groups from master.sys.availability_groups
        ...
SELECT
    CAST(
            serverproperty(N'Servername')
           AS sysname) AS [Server_Name],
    AG.name AS [Name],
    ISNULL(AG.automated_backup_preference, 4) AS [AutomatedBackupPreference],
    ISNULL(AG.failure_condition_level, 6) AS [FailureConditionLevel],
    ISNULL(AG.health_check_timeout, -1) AS [HealthCheckTimeout],
    AR2.replica_metadata_id AS [ID],
    ISNULL(arstates2.role, 3) AS [LocalReplicaRole],
    ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
    AG.group_id AS [UniqueId],
    CAST(ISNULL(AG.basic_features, 0) AS bit) AS [BasicAvailabilityGroup],
    CAST(ISNULL(AG.db_failover, 0) AS bit) AS [DatabaseHealthTrigger],
    CAST(ISNULL(AG.dtc_support, 0) AS bit) AS [DtcSupportEnabled],
    CAST(ISNULL(AG.is_distributed, 0) AS bit) AS [IsDistributedAvailabilityGroup],
    ISNULL(AG.cluster_type, 0) AS [ClusterType],
    ISNULL(AG.required_synchronized_secondaries_to_commit, 0) AS [RequiredSynchronizedSecondariesToCommit]
    FROM
    #tmpag_availability_groups AS AG
    LEFT OUTER JOIN #tmpag_availability_group_states as agstates ON AG.group_id = agstates.group_id
    INNER JOIN #tmpag_availability_replicas AS AR2 ON AG.group_id = AR2.group_id
    INNER JOIN #tmpag_availability_replica_states AS arstates2 ON AR2.replica_id = arstates2.replica_id AND arstates2.is_local = 1
    ORDER BY
    [Name] ASC
...

 select * into #tmpag_availability_groups from master.sys.availability_groups

SSMS version

(17.4)

As a side note, some of these columns have not been added to the sys.availability_groups dmv reference

In the end, the dashboard opens correctly.

Due to the correct columnname being used

required_synchronized_secondaries_to_commit

The solution

You have to upgrade to a higher version (17.4 worked for me) of SSMS to get the correct and existing DMV columns in the query.


Re-installing Oracle 19c on Windows 10: Error in Process: ORACLE_HOME\perl\bin\perl.exe. Error while executing ORACLE_HOME\rdbms\admin\dbmssml.sql Question edited for clarity.
Goal: install Oracle 19c on windows 10 and make a PDB.
Background: I installed Oracle on 2 computers incorrectly (unzipped the oracle 19c file into C:\App\db_home1, instad of into C:\App\oracle\product\19.3\db_home1). I tried fixing this by uninstalling (manually by following these instructions: https://www.youtube.com/watch?v=EHFbk_6ThAQ ). Now i'm trying to re-install.

Problem reinstalling on computer 1:
When i run setup.exe I get an error on the installation step that says [INS-20802] Oracle Database Configuration Assistant Failed.
When I open "installActions2020-09-05_03-48-30PM" I get this error at the bottom: INFO:  [Sep 5, 2020 4:38:02 PM] [FATAL] Error while executing "C:\App\oracle\product\19.3\db_home1\rdbms\admin\dbmssml.sql". Refer to "C:\App\oracle\cfgtoollogs\dbca\orclglobal\dbmssml0.log" for more details. Error in Process: C:\App\oracle\product\19.3\db_home1\perl\bin\perl.exe
When i go to C:\App\oracle\cfgtoollogs\dbca\orclglobal\dbmssml0.log, it's blank.
Trying Joe's suggestions:
C:\>echo %ORACLE_HOME%
%ORACLE_HOME%

C:\>where sqlplus
'where' is not recognized as an internal or external command,
operable program or batch file.

C:\>C:\App\oracle\product\19.db_home/deinstall/deinstall
The system cannot find the path specified.

C:\>

^ but i have sql plus
^ I never had an env variable named oracle_home. this returns the env variable for the current oracle installation (that isn't complete)
C:\>echo %path%
C:\App\oracle\product\19.3\db_home\bin;C:\Program Files (x86)\Applied Biosystems\QuantStudio Design
'Analysis' is not recognized as an internal or external command,
operable program or batch file.

I think the new installation that i'm trying to install is the only thing the installer is picking up. But I don't know what to do with this message. Where do i run this?

Update: I can connect to the CDB i create, but there's no PDB$SEED.

But I have a seed directory C:\App\oracle\oradata\ORCL\pdbseed

Problem reinstalling on computer 2:
INS-30014: Unable to check whether the location specified is on CFS
Here's what Oracle has to say about this error:

Cause: The location specified might not have the required
permissions.

Action: Provide a location which has the appropriate
required permissions.

All the users (me, admin, all possible users) have full permissions on the D drive.
Another solution I found to INS-30014 is to disable the network adapter. I tried that and it still didn't work. (My ethernet was already disabled, so i disabled the internet and that didn't change anything).

Never delete manually on Windows instead use deinstall.batfrom elevated command prompt from root drive C:\%ORACLE_HOME%\deinstall\deinstall.bat (don't initiate deinstall.bat from inside oracle home)
Remove ORACLE_HOME variable from system/user and ORACLE_HOME/bin from path .
If you removed previous installation and trying to install new database not sure why are you starting with DBCA instead of setup.exe.You mentioned you're trying to install PDB implies you already installed CDB i.e ORCLGLOBAL or am I missing something
Edit:-Deleting registry entries,oracle inventory,services and oracle home directory manually is dirty uninstall if not careful can lead to unintended consequences(current error might be or may not related to previous dirty uninstallation). Oracle provides clean uninstall tools in oracle home directory deinstall.bator gui setup.exe(OUI directory) in oracle home directory (you can check in unzipped folder for directories deinstall and 'oui`).
DBCA should be used only after post install to add new instance/database,new pluggable database or after installing software only.
Since you dirty uninstalled double click setup.exe in oracle home directory (check the image below).

Uninstall tools are shown in red rectangles boxes
Edit:-
On computer one you have conflict in environment from previous installation or leftovers .Verify from command prompt echo %ORACLE_HOME% and where sqlplus if either of the command returns remove those variables alternatively easy way is go to OUI folder as depicted in above picture and next go to bin directory and double click setup.Select all remove everything and go to environment tab select remove everything and start setup again.(I'm assuming you don't have any other Oracle product installations)


size of a primary key and foreign key Do a primary key and a foreign key referencing it must have the same column size in sql server 2008 ? could someone indicate me a link to a documentation explaining this ?

Thank you for help.

Yes. It's in the MSDN documentation pages: Foreign Key relationships

  A FOREIGN KEY constraint specified at the table level must have the same number of reference columns as the number of columns in the constraint column list. The data type of each reference column must also be the same as the corresponding column in the column list.

That page does not provide much more details but testing reveals that "same datatype" means same type and same size. Also, if one tries to make a FOREIGN KEY constraint between for example, a VARCHAR(20) and a VARCHAR(30) column,

CREATE TABLE a
( aid VARCHAR(20) PRIMARY KEY ) ;

CREATE TABLE b
( bid INT PRIMARY KEY,
  aID VARCHAR(30) ) ;

ALTER TABLE b
  ADD CONSTRAINT test_different_sizes
    FOREIGN KEY (aid)
    REFERENCES a (aid) ;

we get the explanatory error:

  Schema Creation Failed: Column a.aid is not the same length or scale as referencing column b.aid in foreign key test_different_sizes. Columns participating in a foreign key relationship must be defined with the same length and scale.

Also note that some DBMS (like Oracle, MySQL, Postgres) allow foreign keys between columns of same datatype (i.e. VARCHAR or CHAR) but different size.

Postgres is even less strict. It allows foreign keys between columns of CHAR, VARCHAR or TEXT datatypes in any combination.


Adding ReadOnly Domain-less replica to existing AG with two replicas on two nodes WSFC I have AG setup as follows:

Multi sub-net WSFC (Windows Server 2016) - All nodes part of same
domain
AG with two replicas (SQL 2017) - Each node (mentioned above) contains one standalone instance.

All working good. Now, is it possible to add another AG replica with separate SQL instance that is not on the same domain and not clustered. the requirement here is to have third replica for read-only workload of an external application and that should not be part of same production domain.

Since you got SQL 2017 on Windows Server 2016, you can achieve that (what your looking for) with anyone of following options:

Domain-independent Availability Group
For this to happen, you probably have to re-build WSFC as Active Directory-Detached Cluster since your existing WSFC would expect all nodes to be part of same domain. Consider it's bit complex setup compare to LS and required additional maintenance of master keys and certificates as AG Replica's end-points communication completely depends on those.
Log-shipping
Configure the log shipping pointing Listener name (existing AG) as Primary server and new node as Secondary server in Log Shipping topology


Using Postgres COPY TO for data export without overwriting file I'm running several queries with a script and psql. I am also exporting the results to a.csv using the COPY TO command. However, this overwrites the file a.csv when I would like to instead append the results to a.csv instead.

Is it possible using the COPY TO syntax perhaps there is some parameter I am missing? Or should I look at using other tools?

You could use the meta command \copy in psql (which encapsulates SQL COPY) on the command line, send it to stdout and and redirect output to be appended to a file - with >> instead of >:

psql -c '\copy (SELECT 1, 2) to stdout' >> /path/to/file_to_append_to.csv

(You probably need more connection parameters.)
You can use SQL COPY as well:

psql -c 'COPY (SELECT 1, 2) TO STDOUT' >> /path/to/file_to_append_to.csv

But it's often more convenient to use \copy instead because (quoting the manual):

  file accessibility and access rights depend on the client rather than
  the server when \copy is used.

Or, like Abelisto commented, from inside psql or any other client - if the program cat is available (basically on all *nix OS):

COPY (SELECT 1, 2) TO PROGRAM 'cat >> /path/to/file_to_append_to.csv'

Or, still from inside psql, on any system, you can set the current directory and an output file with:

\cd /path/to/
\o file_to_append_to.csv

The file gets created (only) if it does not exist yet.
Then you can also SQL COPY (or still \copy as well):

COPY (SELECT 1, 2) TO STDOUT;

The output is appended to the file.


How to design tables for players in a tournament? I need help with my database table design.

I have group which contains a list of players. In every group each player plays games with other players. Game has also some result for example 1:0 and there can be overtime too.

I already created some tables but I dont think this is a good solution. There is problem when I create java classes from these tables.

PlayerResult has lists of games. Game has opponent which is again PlayerResult which have listOfGame again etc... I dont know how to solve this cycle because every playerResult should know all their game to count score and point. Please help me improve the table design.

CREATE TABLE IF NOT EXISTS `GROUP` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `NAME` (`NAME`,`GROUP_ID`),
  KEY `TOURNAMENT_ID` (`TOURNAMENT_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `PLAYER_RESULT` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `POINTS` int(11) DEFAULT NULL,
  `RANK` int(11) DEFAULT NULL,
  `GROUP_ID` int(11) NOT NULL,
  `PLAYER_ID` int(11) NOT NULL,
  `SCORE` varchar(10) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `PLAYER_ID` (`PLAYER_ID`),
  KEY `GROUP_ID` (`GROUP_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `RESULT` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `LEFT_SIDE` int(11) DEFAULT NULL,
  `RIGHT_SIDE` int(11) DEFAULT NULL,
  `OVERTIME` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;

CREATE TABLE IF NOT EXISTS `GAME` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `PLAYER_RESULT_ID` int(11) NOT NULL,
  `OPPONENT_ID` int(11) NOT NULL,
  `RESULT_ID` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `VYSLEDKYHRACA_HRACA_ID` (`PLAYER_RESULT_ID`),
  KEY `RESULT_ID` (`RESULT_ID`),
  KEY `OPPONENT_ID` (`OPPONENT_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

UPDATED version based on feedback:

CREATE TABLE group (
    group_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255),
    PRIMARY KEY(team_id)
);

CREATE TABLE player (
    player_id INT NOT NULL AUTO_INCREMENT,
    last_name VARCHAR(50),
    first_name VARCHAR(50),
    group_id INT,
    player_rank INT,
    PRIMARY KEY(player_id)
);

CREATE TABLE game (
    game_id INT NOT NULL AUTO_INCREMENT,
    match_date DATE,
    overtime TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (game_id)
);

CREATE TABLE player_game (
    game_id INT NOT NULL,
    player_id INT NOT NULL,
    player_score INT,
    PRIMARY KEY (game_id, player_id)
);

...and UPDATED explanation:
I think group and player are self-explanatory.  The table game will have one record per match.  The player_game table records the players in a match and the results; each match will have two records, one for each player.

Since you appear to be using InnoDB as the storage engine you may want to use foreign keys to enforce referential integrity, and obviously you can add other constraints and indexes as needed.  If you need some functionality that this doesn't seem to cover, ask and I will either explain or add it.


YYYY-MM-DD has changed to YYYY-DD-MM after moving SQL Server to new domain how do I switch back? I have moved the domain of my SQL Server and while everything is mainly working the date format for queries seems to have changed.

So if I run this:

select convert(datetime,'2012-02-13')

I get

  Msg 242, Level 16, State 3, Line 1
  The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

When I run

select @@Language, @@Langid

I get

  British | 23

I have tried to set sp_defaultlanguage but this seems to make no difference.

I have also checked the regional settings in control panel and that seems to also be set to British.

Even this code sample causes a problem:

set language british

select convert(datetime, '2012-02-13')

With the result

  Changed language setting to British.
  Msg 242, Level 16, State 3, Line 3 The conversion of a varchar data type to a datetime data type
  resulted in an out-of-range value.

The Windows setting do not affect SQL Server parsing dates. There is a server level default language that can be overridden by one set at the login level.

As it stands, the "safe" format for SQL Server is yyyymmdd anyway. Try to use that

From CREATE LOGIN

  Specifies the default language to be assigned to the login. If this option is not included, the default language is set to the current default language of the server. If the default language of the server is later changed, the default language of the login remains unchanged.

For the server, you'd use "default language" via sp_configure

And you can run SET LANGUAGE or SET DATEFORMAT anytime you want of course


dbo.DTA_* tables in MSDB I ran Brent Ozar's sp_blitz on one of my production server.  IT tagged a few tables in the MSDB database as being user created.  They all follow the pattern dbo.DTA_*.  Do these table come with MSDB, or were they added by something/someone?

These are likely left over from Microsoft's Database Tuning Advisor. You should be able to get information about the sessions with this stored procedure:

EXEC sp_DTA_help_session

After identifying the sessions that you do not need you can remove them with this stored procedure supplying the session id using one as an example:

EXEC sp_DTA_delete_session 1


Pull data from sys.columns in a different database I am running the following on one instance on the server:

SELECT c.*
FROM sys.columns c
WHERE c.object_id = OBJECT_ID(@tablename);

Everything works great.

I would like to run the same statement but point it to another database on the server.

  databaseName.dbo.tablename

I've tried the following (and other variations), but it does not work.

SELECT c.*
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('databaseName.dbo.tablename');

Is there a way to get the column information on one database from another?

Code:

SELECT @SQL = @SQL + 'INSERT INTO myTable
  (ColumnName,ColumnValue,SID,SName,RID,RName)
  VALUES (''' + QUOTENAME(c.name) + ''',NULL,0,NULL,0,NULL);'
            FROM sys.columns c
            WHERE c.object_id = OBJECT_ID(@tablename);

EXEC(@SQL);

Is there a way to do this dynamically?  If I had a @DBName parameter that I was passing in.

Add in the database you are looking to query from in front of the sys.columns.

SELECT c.*
FROM <other database name>.sys.columns c
WHERE c.object_id = OBJECT_ID(@tablename);