Who should own the staging database right At my company we are re-assessing which developers will take on the deployment for xyz ticket. At the moment when a developer is done with his ticket, a deployment ticket is created and another developer will take it, do the code review and test the change(s).

We have 3 databases

Development
Staging
Production

The problem is that we don't really have a real DBA, only someone assigned to validate and run the databases changes. In the development environment we(developers) own the DB and have all the rights, but when we make a ticket for the deployment(which requires the staging DB for testing) we don't have the necessary rights and this is where, i think, there is a problem since we need to create a new ticket to have our SQL changes run on the staging DB and it may take some time before those changes are made. After all the review, test and feedback processes we're ready to push the new changes into production, however we're blocked again and need to make another ticket to have our SQL run in the production environment.

My 2 cents on this is that since developers do the deployment and the staging database is for the deployment we should be the owners and making ticket after ticket is just a waste of time because it slows down the deployment process and has side effects for our clients because new features/bug fixes don't come as fast as they should because we're stuck with ticket after ticket.

Who should own the staging database and why?

You say

  however we're blocked again and need to make another ticket to have
  our SQL run in the production environment.

Why? This should surely all be part of one ticket - bug xyz.

What I would do is have the original developer push it out to production and the original tester repeat their tests on the production system (or get the client to do it) and verify that the results are a) bug-free and b) work as the original tester saw fit to pass.

What bug-tracking software are you using? I thought that most of them (all) would consider deployment part of the process for one bug-fix - or that they would allow you configure your system to make that the default behaviour?


Joining tables on DB server vs App-Backend server I have two tables stored on the DB server. I need to join these two tables and perform further calculations. I am sure that the calculations part should  be done on backend server. But I am wondering if I should perform the join in the DB server and then bring the new table to the backend or if I should bring the two tables to backend and there perform operations replicating join (using ADO.NET) before continuing with the further calculations.

NOTE:

Tables have few millions records.
It will be great if you provide some references to support your answer.

JOINing is an important, standard, operation in SQL.  It is possible, but not that standard in app languages.  Do it in SQL.

Ditto for other operations, such as aggregation (eg SUM with GROUP BY).

Ditto for sorting (ORDER BY) and limiting (LIMIT).

Also, consider the amount of data that might need to be transferred.  In most example like the above, less data need be shoveled to the client.  This is another argument for doing the work in SQL.

Another argument...  SQL can handle a 1-row table or a billion-row table.  It does not care.  It will probably use totally different code for each (RAM-based code versus caching versus disk-based).  Do you really want to compete with that level of sophistication?

SQL loves doing "set-based" operations; client code loves tedious 'for'-loops.  Why write a for-loop when a single SQL statement will do the 'for' and the stuff inside it?

Use SQL for what it good at.

In rare cases, some tasks are better done in client code.  Your Question comes nowhere near such.


How to check if restored database is the same as original Is there a way to check if restored backup of Oracle database is the same as the original database? Is there a simple way to check it if I have the original database?

You can use SQL Developer, Tools - Database Diff, this would give you an idea. There is a tutorial here http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52sqldev-1735911.html. Only use the compare/diff section.


SQL Server service account change from virtual account to AD account Had a question about how local permission are granted(like Log on as a service and Replace a process-level token) when you change the SQL Server service account from Virtual account (NT SERVICE\MSSQLSERVER) to an Windows AD account.

I first installed sql server without specifying an windows account.  After installation when I check "Local Security Policy" I could see "NT SERVICE\MSSQLSERVER" added to permissions like "Log on as a service" and "Replace a process-level token".

Then I changed service account to a windows AD account using configuration manager and went back and checked Local Security Policy again and I couldn't find the new windows AD account added in any of them.

So I am wondering how the windows AD account gets those permissions?  Is it somehow linked to the virtual accounts?

SQL Server 2014, Windows 2012R2

I have gone through the below questions, but this specific question is not covered.

SQL Server service account Windows privileges and rights

The virtual accounts tied to the service sid (in this case NT SERVICE\MSSQLSERVER) will retain any permissions given to them. This is because they don't go away, they are still tied to the service via the service sid.

When you change the account, for the example you gave, other than a few house keeping items there is nothing else given. It is very well possible to change to a domain account that does not have the proper permissions to even start the SQL Server service. Nothing is transferred.

For example, if you had an account that had full access to read and write to a directory not in the SQL Server standard locations (that are part of the install process) and changed the account, the permissions stay with that account. The new account would need to be granted those permissions in order for SQL Server to properly work with anything in that folder.

The last part of being linked to the virtual account. No, they are not linked. The virtual account and service sid will stay with whatever they had.


Can't connect mongo shell to Atlas cluster There is some issue coming while connect mongo shell to the atlas cluster. It is saying that No primary detected for set Cluster0-shard-0.

ashish@ashish-Not-Specified:~$ mongo "mongodb://cluster0-shard-00-00-pth8i.mongodb.net:27017,cluster0-shard-00-01-pth8i.mongodb.net:27017,cluster0-shard-00-02-pth8i.mongodb.net:27017/test?replicaSet=Cluster0-shard-0" --authenticationDatabase admin --ssl --username m001-student --password m001-mongodb-basics
    MongoDB shell version: 3.2.17
    connecting to: mongodb://cluster0-shard-00-00-pth8i.mongodb.net:27017,cluster0-shard-00-01-pth8i.mongodb.net:27017,cluster0-shard-00-02-pth8i.mongodb.net:27017/test?replicaSet=Cluster0-shard-0
    2017-10-25T22:12:00.759+0530 I NETWORK  [thread1] Starting new replica set monitor for Cluster0-shard-0/cluster0-shard-00-00-pth8i.mongodb.net:27017,cluster0-shard-00-01-pth8i.mongodb.net:27017,cluster0-shard-00-02-pth8i.mongodb.net:27017
    2017-10-25T22:12:00.759+0530 I NETWORK  [ReplicaSetMonitorWatcher] starting
    2017-10-25T22:12:09.080+0530 W NETWORK  [thread1] No primary detected for set Cluster0-shard-0
    2017-10-25T22:12:18.389+0530 I NETWORK  [ReplicaSetMonitorWatcher] Socket recv() timeout  34.204.152.215:27017
    2017-10-25T22:12:18.610+0530 W NETWORK  [thread1] No primary detected for set Cluster0-shard-0
    2017-10-25T22:12:18.610+0530 E QUERY    [thread1] Error: connect failed to replica set Cluster0-shard-0/cluster0-shard-00-00-pth8i.mongodb.net:27017,cluster0-shard-00-01-pth8i.mongodb.net:27017,cluster0-shard-00-02-pth8i.mongodb.net:27017 :
    connect@src/mongo/shell/mongo.js:223:14
    @(connect):1:6

    exception: connect failed

@ashishkumar148, I would like to say that as per MongoDB BOL  Here , MongoDB Atlas cluster only able to connect with MongoDB 3.4 version, due to Mongo Shell with TLS/SSL support.

I have gone through you mongo shell log , where i have found out that as you are using  MongoDB shell version: 3.2.17.

I am also attaching the Sandbox MongoDB Cluster connect screen shot to you.

Note:- Before making the connection with MongoDB Atlas with MongoDB shell, make sure that you have successfully configured your IP address through Mongo Atlas IP whitelist Here .And the IP status is Active. And during the connection the User ID & Password should be used, which you have Assigned in MongoDB Atlas cluster creation time.


How to return several arrays of doubles as a table in C# SQLCLR function What I have is a CLR SqlFunction that produces 3 arrays of doubles. I would like this function to return something appropriate so that the FillRowMethod can output it for me as a table in T-SQL. It works fine for 1 array but I'm having trouble extending it to several. I'm mostly unsure on what to return from my method. Some code below:
[SqlFunction(DataAccess = DataAccessKind.Read, FillRowMethodName = "FillRow",
    TableDefinition = "impliedVol float, maturity float, strike float")]
public static IEnumerable getStrippedCapletVolatilitiesFromCapVolatilityCurve(
     string uploadDate, double strike, double yearsForward, double intervalDuration,
     string curve, string surface)

//Create 3 arrays of doubles
    double[] array1;
    double[] array2;
    double[] array3;

    return [???];
}

public static void FillRow(object obj,
     out SqlDouble impliedVol, out SqlDouble maturity, out SqlDouble strike)
{
   //impliedVol = (double)obj;  //This is what I do if only returning one array
}

EDIT:
Based on feedback this is my new attempted solution.
public static IEnumerable getStrippedCapletVolatilitiesFromCapVolatilityCurve(string uploadDate, double strike, double yearsForward, double intervalDuration, string curve, string surface)
    {
            //omitted code above this line.
        CapletStipping thisCapletStripping = new CapletStipping(maturities, forwardRates, discountingRates, intervalDuration);
        double[][] theseStrippedCapletVols = thisCapletStripping.getCapletCurveForGivenStrike(flatVols, strike);

         List<capletVolatilityNode> capletVolatilitiesList = new List<capletVolatilityNode>(theseStrippedCapletVols[0].Length);

        for (int i = 0; i < theseStrippedCapletVols[0].Length; i += 1)
        {
            capletVolatilityNode thisCapletVolatilityNode = new capletVolatilityNode(theseStrippedCapletVols[0][i], theseStrippedCapletVols[1][i], theseStrippedCapletVols[2][i]);
            capletVolatilitiesList[i] = thisCapletVolatilityNode;
        }

        return capletVolatilitiesList; // theseStrippedCapletVols;
    }

    public class capletVolatilityNode
    {
        public double impliedVol;
        public double maturity;
        public double strike;
        public capletVolatilityNode(double impliedVol_, double maturity_, double strike_)
        {
            impliedVol = impliedVol_;
            maturity = maturity_;
            strike = strike_;
        }
    }

public static void FillRow(Object obj, out SqlDouble impliedVol, out SqlDouble maturity, out SqlDouble strike)
{

    capletVolatilityNode row = (capletVolatilityNode)obj;

    impliedVol = Convert.ToDouble(row.impliedVol);
    maturity = Convert.ToDouble(row.maturity);
    strike = Convert.ToDouble(row.strike);

}

If you are wanting to return the 3 arrays as 3 separate result sets, that is not possible for a function, whether SQLCLR or T-SQL. You would need to create a stored procedure instead in order to return multiple result sets.
If the 3 arrays represent 3 columns of a single result set such that they will all have the same number of items and the index value of one is conceptually associated with the same index value of the others (i.e. array1[x] is associated with array2[x] and array3[x], while array1[y] is associated with array2[y] and array3[y], and so on), then a simple array is the wrong type of collection. You can only return a single collection where each item/element in the collection represents a row in the result set (or at least enough info to construct the desired row). That singular collection, when returned from the SqlFunction method, is iterated over, calling the FillRowMethod for each item/element. One item/element is passed into the FillRowMethod which constructs the final result set structure and values and passes them back (hence you have the opportunity to create and/or translate values from the original item before passing them back).
In this latter case, you would need to create a class similar to:
private class volatility
{
    public double impliedVol;
    public double maturity;
    public double strike;
}

Then, create a generic list of those in your getStrippedCapletVolatilitiesFromCapVolatilityCurve method, add a new item to that collection for every row to return, and then return that list/collection. Your FillRowMethod will be called with the first parameter (as object) being of type volatility. That is where you will populate the out params from those properties of volatility. For example:
private static void FillRow(object obj,
     out SqlDouble impliedVol, out SqlDouble maturity, out SqlDouble strike)
{
    volatility row = (volatility)obj;

    impliedVol = new SqlDouble(row.impliedVol);
    maturity = new SqlDouble(row.maturity);
    strike = new SqlDouble(row.strike);
}

Now, it might be possible to handle this as a two-dimensional array (i.e. double[][]) that you return from the main SqlFunction, but then the FillRow method would be sent a double[] since the first dimension is broken out into individual calls to the FillRow method. I've never tried this specific approach, but it should work as follows:
private static void FillRow(object obj,
     out SqlDouble impliedVol, out SqlDouble maturity, out SqlDouble strike)
{
    double[] row = (double[])obj;

    impliedVol = new SqlDouble(row[0]);
    maturity = new SqlDouble(row[1]);
    strike = new SqlDouble(row[2]);
}

ALSO:
It just occurred to me that you could even forgo the generic list / collection and stream the contents of the double[][] array out to the result set, one item / row at a time. Go ahead and try this:
public static IEnumerable getStrippedCapletVolatilitiesFromCapVolatilityCurve(...)
{
    //omitted code above this line.
    CapletStipping thisCapletStripping =
       new CapletStipping(maturities, forwardRates, discountingRates, intervalDuration);
    double[][] theseStrippedCapletVols =
       thisCapletStripping.getCapletCurveForGivenStrike(flatVols, strike);

    // THIS PART IS DIFFERENT -- begin
    capletVolatilityNode thisCapletVolatilityNode = new capletVolatilityNode();

    for (int i = 0; i < theseStrippedCapletVols[0].Length; i += 1)
    {
        thisCapletVolatilityNode.impliedVol = theseStrippedCapletVols[0][i];
        thisCapletVolatilityNode.maturity = theseStrippedCapletVols[1][i];
        thisCapletVolatilityNode.strike = theseStrippedCapletVols[2][i];

        yield return thisCapletVolatilityNode; // return rows individually
    }

    return; // cannot return anything when using "yield return"
    // THIS PART IS DIFFERENT -- end
}

private class capletVolatilityNode
{
    public double impliedVol;
    public double maturity;
    public double strike;
}

There are some restrictions when using yield return, but if your process allows for this construct, then this will not only be faster, but also take up less memory. These benefits are due to this code skipping the step of copying the results of the getCapletCurveForGivenStrike() method to a separate collection (i.e the Generic List) just to return it to T-SQL (in which case you need to wait while it copies the collection and consumes more memory).

On a related note: use the Sql* types for the input parameters instead of standard .NET types. Meaning, use SqlString instead of string and SqlDouble instead of double. You can then easily get the .NET native type from those via the Value property that all of the Sql* types have (e.g. SqlString.Value passes back a string, etc).
For more info on working with SQLCLR in general, please visit: SQLCLR Info


Which are the differences and advantages on single INDEX or compound INDEX? I have two INDEX on a n:m relationship table as you can see on pics below:

Now I can have this also:

My questions:

What is the advantage from last way(pic compound) over the two first?
Can the # have some weight or significance in the composite INDEX?

Here's some explanation of which index you could use and when:

First, indexes speed up retrievals but slow down inserts and deletes, as well as updates of values in indexed columns. That is, indexes slow down most operations that involve writing. This occurs because writing a record requires writing not only the data row, it requires changes to any indexes as well. The more indexes a table has, the more changes need to be made, and the greater the average performance degradation. In the section "Loading Data Efficiently," we'll go into more detail about this phenomenon and what you can do about it.

Second, an index takes up disk space, and multiple indexes take up correspondingly more space. This might cause you to reach a table size limit more quickly than if there are no indexes.

Example:

mysql> ALTER TABLE customer ADD INDEX idx_sub
 (subscription)
mysql> EXPLAIN SELECT name FROM customer
WHERE subscription='2009-01-01' ORDER BY name
*************************** 1. row ***************************
 ...
 type: ref
 key: idx_sub
 rows: 4370
 Extra: Using where; Using filesort

The ideal way:

mysql> ALTER TABLE customer ADD INDEX
 idx_sub_name (subscription,name)
mysql> EXPLAIN SELECT name FROM customer
WHERE subscription='2009-01-01' ORDER BY name
*************************** 1. row ***************************
 ...
 type: ref
 key: idx_sub_name
 rows: 4363
 Extra: Using where; Using index

Benchmarks:

Avg number of sec to run the query:

Without index: 3.743
Index on subscription: 0.435
Covering index: 0.012

Covering index:

35x faster than index on subscription
300x faster than full table scan

Related:

Better Indexing for MySQL?
Covering Indexes


Percona pt-table-sync Can't determine Master We have following Multi master replication scenario. We are using MariaDB 10.1.34 and Percona 3.0.11

Master: Name: db-master1 (192.168.14.5)

Slave:
Name: db-slave2 (192.168.15.5) Replica Connection name: master1 (192.168.14.5)

SHOW SLAVE 'master1' STATUS\G on slave server is working fine.

I deliberately produced the data inconsistency and deleted 5 records in Test.dummy table on slave. Once I ran the below command to sync:

pt-table-sync --replicate --sync-to-master h=localhost,u=checksum_user,p=checksum_password --check-master --channel=master1

I got the error:

  Can't determine the Master of h=localhost

pt-table-sync --execute --verbose --databases Test -t dummy --no-foreign-key-checks h=192.168.14.5,u=checksum_user,p=checksum_password h=192.168.15.5

The above command successfully synchronized the data from Test.dummy on Master (192.168.14.5) to Test.dummy on Slave (192.168.15.5).


SQL Server 2008 SP1 to SQL Server 2008 R2 upgrade One of our project is planning to upgrade from SQL Server 2008 SP1 to SQL Server 2008 R2. I would like to know from this community the suggestions on:

Is there any configuration or any change required for migrating the data?
We have some SSIS packages so does this upgrading require any code change?
Any other suggestions or best practices that we should use to get this done.
What should be the approach?

I would slipstream Service Pack 2 (and perhaps Cumulative Update 2 as well) into the R2 installer prior to running the upgrade, especially if you have enabled the extended partition support. This guide walks you through slipstreaming (it talks about a different service pack and CU, but the process is the same). Slipstreaming will save you a bunch of time and service restarts, since you should be installing the latest service pack at least and not be leaving your new instance at RTM (it is no longer a maintained branch).

You shouldn't have to change SSIS packages.

If you can afford a side-by-side upgrade (e.g. you have another machine or adequate resources for another instance on the current machine, and can re-point your apps later), it will be slightly safer and require less downtime than an in-place upgrade (though it will be more work). You can install another instance, get your logins and jobs set up, then mirror or log ship your user databases to the new instances. When you've ready, you can fail over, take the old databases offline, then the new instance is the primary and you just need to re-point your apps.

Personally, I haven't had any problems with in-place upgrades, but I would probably opt against them if possible in a mission critical environment. The main point is if you do side-by-side and something goes wrong, you can take the new instance down and rebuild from scratch, and it doesn't affect your production system. If something goes wrong in the middle of an in-place upgrade, then you'll be in scramble mode... do we have current backups? Where do we restore them?

In either case, you'll want to make sure you take proper backups before the upgrade.

You will want to update statistics once the upgrade is complete, and if you're on Enterprise Edition and using data compression on tables with nvarchar columns, you may want to rebuild those indexes, since one of 2008 R2's primary advantages over 2008 in the OLTP world is Unicode compression. I wrote a few blog posts about this:

https://sqlblog.org/2009/08/11/sql-server-2008-r2-a-quick-experiment-in-unicode-compression
https://sqlblog.org/2009/08/23/sql-server-2008-r2-digging-deeper-into-unicode-compression
https://sqlblog.org/2009/10/04/more-testing-of-unicode-compression-in-sql-server-2008-r2


How to properly configure SQL Database Mail to send through an Office 365 SMTP server without using an on-prem SMTP server? We used to use SQL Database Mail through an on-prem SMTP server, but have since transitioned to Office 365.

I created a new profile for O365 and tried configuring a new account in that profile like this:

I've also tried smtp.office365.com as the "Server name".

When I try sending a test email, I never receive it.
The error message I get in the Database Mail Log is below:

It turned out we were using the wrong "Server name" and "Port" all along.
Inputting the correct server and port fixed our issue.
(It really is that simple to setup database mail through O365 :)


first row and last row fields value from where data changed Assume I have a table like this:

I'm trying the have a output like following picture

How best to achieve this?

SELECT job_title,
       charge_center,
       MIN(id) start_id,
       MAX(id) end_id
FROM source_table
GROUP BY job_title, charge_center;

If there exists 2 separate blocks with the same (job_title, charge_center) pair values the query will give wrong result.


anonymous blocks inside a transaction I have the following script:

DROP TABLE IF EXISTS a;
CREATE TABLE a(
val   VARCHAR NOT NULL
);

DO
$do$
BEGIN
    INSERT INTO a(val) VALUES('a1');
    INSERT INTO a(val) VALUES('a2');
END;
$do$

When I place it in file (boo.sql) and execute it with:

psql -v --quiet -X -U some-user --no-password -d some-db -f boo.sql

... the table gets created and the rows are inserted.
When I try to wrap the anonymous block inside a transaction (so that I can explicitly set and control the transaction isolation level):

DROP TABLE IF EXISTS a;
CREATE TABLE a(
val   VARCHAR NOT NULL
);

BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
DO
$do$
BEGIN
    INSERT INTO a(val) VALUES('a1');
    INSERT INTO a(val) VALUES('a2');
END;
$do$
COMMIT;

... I get instead:

psql:boo.sql:16: ERROR:  syntax error at or near "COMMIT"
LINE 8:     COMMIT;
            ^

Am I doing something nonsensical? Is there a way to wrap a single or multiple anonymous blocks inside a transaction and if not, then on what transactions do these anonymous blocks get executed and how can I control its transaction isolation level?

There is a semicolon missing after the DO statement:

...
END     -- semicolon is not necessary here
$do$;   -- but here it is
COMMIT;

The first example works, because the semicolon is optional at the end of a script. That's also why the semicolon after END inside the block is optional.


SELECT highest quantity in related table, exclude if there is a tie I have a SELECT statement that pulls in product information from a related table, and finds the product with the highest quantity associated to it for that transaction. This is shown below and working as expected:

SELECT
qty.[Id],
qty.[Product_Name],
qty.[Product_Type]

FROM
( SELECT td.[Id], prd.[Product_Name], prd.[Product_Type],
         ROW_NUMBER() OVER (PARTITION BY prd.Receipt_Number ORDER BY prd.Quantity DESC) AS Row
  FROM [Transactions] td
  INNER JOIN [Products] prd
  ON prd.[Receipt_Number] = td.[Receipt_Number]
  WHERE prd.Quantity > 1
) qty

WHERE
qty.Row = 1

The issue is that there may be multiple products that have the exact same quantity. Current the system will pull in the first record it finds of the multiple matches, but I would like to exclude this inner query entirely if there is a tie on the highest quantity, returning a null value. Is this possible?

It's a bit unclear what you want, but you can use rank() to collect duplicates, and then another window function that counts those, and filter them out if there is more than one:

SELECT qty.Id
     , qty.Product_Name
     , qty.Product_Type
FROM (
    SELECT qty.Id
     , qty.Product_Name
     , qty.Product_Type
     , COUNT(Rnk) OVER () as cnt
    FROM (
        SELECT td.Id, prd.Product_Name, prd.Product_Type
        ,  RANK() OVER (PARTITION BY prd.Receipt_Number
                        ORDER BY prd.Quantity) AS Rnk
        FROM Transactions td
        JOIN Products prd
            ON prd.Receipt_Number = td.Receipt_Number
        WHERE prd.Quantity > 1
    ) qty
    WHERE qty.Rnk = 1
) WHERE cnt = 1;

If you want the rows, but with null instead of values you can use a case expression:

SELECT CASE WHEN cnt = 1 THEN qty.Id END
     , CASE WHEN ... qty.Product_Name ...
     , ...
FROM (
...
);


Collect data with central management servers and keep them in local table I have 50 servers. I manage them from a central management server on SQL Server 2014 Enterprise Edition. I don't use repository.

I could open a query and get result for each one at the same time. But I want to do it in an automated way, like a job, to collect data from each server and store/handle it locally.

Is this possible and if so, how?

Surely you can automate via CMS as explained in Automate Your Central Management Server Registrations (SQL Server Central, free registration required), which I've used and works well in 2012 and should be the same for 2014 as mentioned:

  Script 03_populate_cms_folders_from_inventory.sql could be scheduled as a SQL Agent job to regularly refresh the CMS folder
  contents
  Adding a section to [dbo].[ap_CMS_Populate_CMS_folder] to allow for SQL 2014 could be done (though I haven’t tested it)

Therefore by running the stored procedures from above article at the desired frequency via SQL Server Agent you can gather the data at your local table and later query for same.

One more way of doing this (apart from CMS) is to use Data Collector, which has quite significant changes in 2014 as explained in the article which also will help you automate the process.

Adding: Also you can use both as explained in:

Centralize Your Database Monitoring Process (SimpleTalk)

I hope the above articles/methods will help you in automating the tasks.


Column not like any value in column in other table - function The two tables in question are:

completed_types

id | type
---|--------
1  | XXX
2  | YYY

all_types:

id | type  | sub_type
---|-------|---------
1  | XXX X | aaa
2  | XXX-Y | bbb
3  | YYY-X | ccc
4  | YYY.Y | ddd
5  | QQQ-G | fff
6  | RRR+Q | ggg

What I want is to use the type column of completed_types to filter all_types.

Something like SELECT type FROM all_types WHERE type NOT LIKE ANY(SELECT type FROM completed_types.

The expected output based on the above tables would be:

id | type  | sub_type
---|-------|---------
5  | QQQ-G | fff
6  | RRR+Q | ggg

However it does not seem to remove any results from the output. I tried appending a wildcard '%' to the end of the subquery:

SELECT type FROM all_types WHERE type NOT LIKE ANY(SELECT type||'%' FROM completed_types)

But this has also not reduced the number of results.

My plan is to use this in a function, as all_types.type could grow quickly while completed_types.type will be slower, and will likely never be completely the same or even close, so if that would change your answer please let me know, but any help with this is appreciated.

While your solution should basically do the job:

SELECT type FROM all_types WHERE type NOT LIKE ALL(SELECT type||'%' FROM completed_types)

Note some corner-case problems:

If completed_types.type isn't defined NOT NULL and you introduce even a single NULL, your query will not return any rows. So make sure it's NOT NULL.
If all_types.type allows NULL, those rows are never returned. So make sure that one is NOT NULL as well or adapt the query like below.
If one of the special characters %\_ is allowed, you may want to escape their special meaning when transforming the string to a LIKE pattern. See:

Escape function for regular expression or LIKE patterns

I suggest (using the simple function f_like_escape() outlined in the link above):

SELECT type
FROM   all_types a
WHERE  NOT EXISTS (
   SELECT FROM completed_types c
   WHERE  a.type LIKE f_like_escape(c.type) || %
   );

Also returns rows with all_types.type IS NULL - as opposed to your original.

Or save the readily escaped pattern as additional column in completed_types to avoid the escaping over and over.

Or, if completed_types.type always holds the leading 3 characters like your data sample suggests:

SELECT type
FROM   all_types a
WHERE  NOT EXISTS (
   SELECT FROM completed_types c
   WHERE  a.type = left(c.type, 3)
   );


Does rebuilding an index update the last_ddl_time attribute in ALL_OBJECTS/DBA_OBJECTS? I rebuilt an index and the last_ddl_time was modified, but I am not sure that's what caused it (someone else might have updated it in other way).

las_ddl_time is defined as follows in the Oracle documentation under ALL_OBJECTS:

Timestamp for the last modification of the object resulting from a DDL statement (including grants and revokes)

Thanks.

Yes.

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> create table tttt1(c1 number);

Table created.

SQL> create index iiii1 on tttt1(c1);

Index created.

SQL> select last_ddl_time from user_objects where object_name = 'IIII1';

LAST_DDL_TIME
-------------------
2018-05-07 16:32:46

SQL> alter index iiii1 rebuild;

Index altered.

SQL> select last_ddl_time from user_objects where object_name = 'IIII1';

LAST_DDL_TIME
-------------------
2018-05-07 16:33:10


DBCC loginfo contains thousands of VLF entries with status 2 I've inherited this huge SQL Server 2000 prod database and suddenly the transaction log ran out of space.  The transaction log gets backed up every hour but I think over the weekend some existing jobs to re-index some tables grew the log file to the point where it ran out of space.  The first thing I did was backup the log (the database is in full recovery mode) and then ran dbcc sqlperf to check the log space used percentage.  It was still at over 99%.  So I did some more research and found another dbcc command, loginfo.

When I saw that output, there were almost 15K rows returned with all of status 2 and some of the CreateLSN seemed dated a while back.  I checked for any open transactions and there weren't any.

It seems like the active portion of the log file is permanently keeping the file very large forcing any new transactions to request more space.  As far as I can tell, there's no replication going on requiring the VLFs to stay active.

if this is happening only for one db, check the default initial size of the log file for that DB. I suspect the minimum size has to be large enough so that after restoring or even after backing up the log SQL server increases the log size to specified minimum size and initialize all VLF with zero.

Alternately, if you have already taken a full backup of the db try to create dummy DDL stmt, backup the DB again, restore it on another machine, and confirm the DDL operation you did. now you can detach and attach the DB w/o using the existing log file that will create new log file with the default size you have in your instance.


What is the best way to mirror an ASPState database? I'm not having issues with the actual mirroring part, but because of the use of temporary tables, IIS is throwing a bit of a wobbler if a failover occurs (I'm still testing a few things, so it's me causing the failover).

Is it possible (or even a good idea) to mirror tempdb? Or should I just accept that some people might get kicked out if the principal database is not accessible for any reason.

I'm using ASPState so that the two web servers (behind a load balancer that enables sticky sessions) can both get to the same session data.

Mirroring tempdb, that's a no. Have you checked Step-by-step SQL Mirroring the ASPState database?

  If the -sstype:p parameter is not specified, then by default, the
  ASP.NET sessions will be put into the TempDB database and not the
  ASPState database. This confused me for a while. This is fine for a
  normal non-mirrored environment because the ASP sessions will be
  cleared if the server is restarted. But, this is not fine for the
  mirrored environment, because we want to mirror the ASP session data
  itself, not just the Stored Procedures! Also, mirroring is not
  possible on the TempDB database. The -sstype:p parameter makes ASP.NET
  install the session tables into the ASPState database, and they'll be
  persisted if the server is restarted. This is exactly the behaviour we
  want for a mirrored environment.


Correct SQL Server linked server connection settings to MySQL that will allow inserts I'm in linked server hell!  I've created a linked server to a remote MySQL database.  I'm able to query and do updates.  However when I attempt an insert I get an error:

"[MySQL][ODBC 5.1 Driver][mysqld-5.0.92-log]Commands out of sync; you can't run this command now".

What are the correct Settings I should have?  I realize there are 3 components with settings (MySQL ODBC Driver, MSDASQL Provider, Linked Server Object).  Here's what I have so far:

ODBC Driver:
Connection Tab: Nothing checked.
Metadata Tab: Nothing checked.
Cursors/Results Tab:
     Don't Cache Results of forward-only cursors - checked
     Force use of forward-only cursors - checked
     Return mached rows instead of affected rows - checked
Debug Tab:  Nothing checked.
SSL Tab: Nothing checked.
Misc Tab: Nothing Checked.

MSDASQL Provider:
     The following are checked:
     Nested Queries
     Level zero only
     Allow inprocess

Linked Server:

EXEC master.dbo.sp_addlinkedserver @server = N'HOMEPROJECTADMIN', @srvproduct=N'MySQL', @provider=N'MSDASQL', @datasrc=N'HOMEPROJECTADMIN', @provstr=N'DSN=HOMEPROJECTADMIN;OPTION=3'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'HOMEPROJECTADMIN',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'HOMEPROJECTADMIN', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Just a quick observation: you using MySQL 5.1 Driver against MySQl 5.0.92

I recommend you should either

downgrade to MySQL 5.0 ODBC Driver (easier option)
upgrade to MySQL 5.1.61


Setting up log shipping with an availability group In my production environment I have a SQL Server 2012 AlwaysOn AG.

The Server1 there is database "MessagesDB" and in an AG to secondary Server2 (in synchronous mode).

I want to setup log shipping from Server1.MessagesDB to a data warehouse (Server3).

Is it possible?

Robert Douglas describes the exact solution to my problem in his post:

Why log shipping backups fail in an Always On Availability Group – Resolved

Robert writes:

  ...the availability group was trying to enforce a backup being taken off the secondary replica, and the log shipping job didn’t like that.

  Here's how to correct it.

  In Management Studio expand AlwaysOn High Availability and then Availability Groups.
  Right click on your chosen group and choose properties.
  In the left pane select backup preferences.
  Select 'Primary' as your backup source.  Click Okay.


How to create a server instance of just ComputerName instead of ComputerName\SQLEXPRESS I'm currently hosting a database on the server MyComputerName using SQL Server Express 2014.

I want to move the database to another computer, so I installed SQL Server Express 2014 on it but that computer only has the instance ThatComputerName\SQLEXPRESS. When I try to connect to just ThatComputerName it throws the error

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

However, I can't enable remote connections on ThatComputerName because I can't even connect to that instance.

I don't remember what I did on the previous computer to allow me to connect both to MyComputerName and MyComputerName\SQLEXPRESS, how can I create that instance on the new computer? Do I have to reinstall?

You cannot change the Instance Name after it has been installed.  But you can uninstall that SQL Server instance and reinstall as a DEFAULT instance.  Ideally you should make a backup of the server, then restore the backups to the new server.

You can technically detach databases and attach again after the reinstall, but that includes the risk of losing your detached databases. See Aaron Bertrand's post:

https://blogs.sentryone.com/aaronbertrand/bad-habits-file-backups/

When installing a default instance of SQL Server Express at the Instance Configuration page, you must choose the Named Instance radio button and type in the default instance name of MSSQLSERVER.

All default instances use the MSSQLSERVER internally, but they just do not make it visible to you.


Split database per pool I need to split a database of users by X pool and each pool need to have nearly the same number of users based on their email provider...

For explain to you better, if my database content:
100 users outlook.com, 50 live.com, 10 gmail.com and 1 toto.com
And one ask me to split database by 5 (for example, it's can be 6, 9 or something else)

I need to have pool A / B / C / D with 20 outlook.com, 10 live.com, 2 gmail.com
and the pool E like above but with 1 toto.com

Actually I already splited the database per each email provider but I don't find how can I balancing the pools per ESP for be nearly equal.
The database content more than 500 different domains and some domains have only 2 or 3 users. I have to dispatch all that smartly :p

UPDATE #1
The split can be randomly, the goal if one ask me needs 8 pools it's to provide the pools with each pool content +/- 1/8 of database but with nearly each ESP.

UPDATE #2
About the split, I will create the new columns named Pool_ESP and with data like A, B, C... I need to have a history of action we will do on each pool, example we send message type 1 to pool B, type 2 to pool C...
About distro it can be randomly but by ESP (like above) the goal it is to respect the criteria of balancing (here per ESP and per number of pool they will ask me)

If somebody looking something for solve the similare issue look for by the side of

MySQL NTILE()

You can split database on equal part based on a column value.
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_ntile


Error Code: 1205 Lock wait timeout exceeded in MySQL I have set InnoDB lock wait time-out to 120 seconds.

When I run the query below for first time, it works fine.  However, the second time, the same query with a different value gives me:

  Error Code: 1205 Lock wait timeout exceeded; try restarting transaction

INSERT INTO PM_SPEC_DOC_APPROVE (
    SDA_SYS_NUM,
    SDA_SPEC_SYS_NUM,
    SDA_SYS_REV,
    SDA_APPR_UID,
    SDA_APPR_DATE,
    SDA_CR_UID,
    SDA_CR_DT,
    SDA_SRNO,
    SDA_NOTE)
VALUES (
    '7414',
    '24510',
    NULL,
    'E13462',
    '2012-07-25 16:35:49',
    'E13462',
    '2012-07-25 16:35:49',
    '3',
    NULL)

We are using MySQL v5.1

innodb_lock_wait_timeout

The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction will hang for at most this many seconds before issuing the following error:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

When a lock wait timeout occurs, the current statement is not executed. The current transaction is not rolled back. (Until MySQL 5.0.13 InnoDB rolled back the entire transaction if a lock wait timeout happened. You can restore this behavior by starting the server with the --innodb_rollback_on_timeout  option

By default, the transaction will not be rolled back. It is the responsibility of your application code to decide how to handle this error, whether that's trying again, or rolling back.

Also You can see more details of the event by issuing a "SHOW ENGINE INNODB STATUS" on server after getting this error.


Can I write SELECT query which returns table name after FROM keyword? I query a table which has another table name in one column. I must get that column value for searching that table. To do that I try something like this;

select cap from (select table_name from table2 where mi_prinx=(select mip1 from table1 where mip1=184425 and startmi1=0 group by mip1,startmi1 having count(*)>=4);

Inner Select statement returns table3 name. But this does not working. Can I write something like that or should I use Pl/Sql for that.

I am using something like this for now;

declare
table_name varchar2(100);
obj_mi_prinx number;
cap varchar2(100);
begin
select table_name,obj_mi_prinx into table_name, obj_mi_prinx from table2 where mi_prinx=(select mip1 from table1 where mip1=184425 and startmi1=0 group by mip1,startmi1 having count(*)>=4);
dbms_output.put_line('table_name: ' || table_name);
if(table_name = 'table3') THEN
select cap into cap from table3 where mi_prinx=obj_mi_prinx;
END IF;
dbms_output.put_line('mi_prinx in yb_ob_boru: ' || obj_mi_prinx);
end;

If I can do this without using IF statement, I prefer it.

If you have a somewhat limited number of tables that could be returned by the query, you could have the query select from all of them, but only return results for the one matching the data returned by the table1/table2 query.  That would look something like this:

With TableVariable As (
   SELECT table_name FROM table2
   WHERE mi_prinx=(
      SELECT mip1 FROM table1 WHERE mip1=184425 AND startmi1=0 GROUP BY mip1,startmi1 HAVING count(*)>=4)
   )
SELECT cap FROM TableA WHERE 'TableA' = (SELECT Table_Name FROM TableVariable)
UNION ALL
SELECT cap FROM TableB WHERE 'TableB' = (SELECT Table_Name FROM TableVariable)
UNION ALL
SELECT cap FROM TableC WHERE 'TableC' = (SELECT Table_Name FROM TableVariable)
UNION ALL
SELECT cap FROM TableD WHERE 'TableD' = (SELECT Table_Name FROM TableVariable)
UNION ALL
SELECT cap FROM TableE WHERE 'TableE' = (SELECT Table_Name FROM TableVariable)
UNION ALL
SELECT cap FROM TableF WHERE 'TableF' = (SELECT Table_Name FROM TableVariable)
;

If the number of tables is too long then you'll need to use pl/sql, which may include the dynamic SQL mustaccio mentioned.


Schedule Full, Differential and Transactional Backup in SQL Server 2016 I studied about scheduling backups with SQL Server Agent and Maintenance Plans (which leads to the SQL Server Agent).

My first concern is, what is the best way to schedule my backups?

I want full backup every day, differential backup every hour and transactional log backup every 15 mins.

My second concern is, is this a good practice?

I noticed an issue when I used SQL Server Agent. My full and differential backups are overwritten. I am ok with having the full backup overwritten but not for the differential since when I need to recover it, I would only have one differential backup which is not the purpose of differential backups.

How can I not let this overwrite on previous backups?

The last concern and question is regarding the way to implement the backup scheduling. I am going to use Maintenance Plans , and may schedule three different backups. One for full, one for differential and one for transnational.

Is this the best practice for a database that is used everyday by users? (at least 2000 transactions per day)

Adding to the answer of Shekar Kola.

Personally I always suggest and use custom scripts over maintenance plans as these are easier to deploy on multiple servers, and easier to configure.

If you're going for the route of the custom scripts instead of the maintenance plans, consider using Ola Hallengren scripts.
Ola hallengren website

These are some amazing well documented and frequently used scripts that cover everything about backup, index maintenance and itegrity checks. These scripts get recommended by some of the best DBA's around.

The backups wont overwrite eachother and the script will add dates to the .bak files.

The only thing you have to do is run the scripts, and add some schedules yourself.
IF you want more configuration, check the documentation, there's a lot you can add/change yourself if you'd like.

UPDATE:

  Is it fine to have system database backup everyday?

Yes nothing wrong with it.

  and Differential users database backup every 4 hours and Log backup every 15 mins.

Doing a diff during work hours can put a lot of stress on your database, and can hurt performance A LOT. Often this is not acceptable. Again, depends on your case, how long backups take, how often your data changes.

  Do you think this is good enough for a a database that has 2000 transaction per day (approximately)?

The amount of backups are normally decided by the business. You should look up RTO and RPO SQLSkills RTO/RPO. How many backups and which backups you need is related to those numbers, and to the amount of disk space you can use. An often used schedule is, 1 full each week, 1 diff each day, log backups every 10 - 15 min. But it of course depends on your case.

  I do have other jobs such as 'DatabaseIntegrityCheck - USER_DATABASES' , 'IndexOptimize - USER_DATABASES' ,etc. Should I schedule them too? or they will be used automatically with backups?

No they will not be fired with the backups, they are totally separate jobs. But yes, you should have some kind of Integritychecks and index maintenance running. Read up on the documentation of what exactly they do. Very short: Integrity checks check your data for corruption, and IndexOptimize will check your indexes for fragmentation.

But as a starter, just make sure you take backups, it's much more important to have backups not scheduled perfectly, than not having backups at all.


Slow While Loop, Query Improvment Assistance I am working on creating a Datawarehouse.
I have created a Time Dimension (Dim_Time), at 5-minute intervals. Hour Aggregations will have [Minutes] = NULL.
For the purpose of this example:

CREATE TABLE [dbo].[Dim_Time](
    [TimeID] [int] IDENTITY(1,1) NOT NULL,
    [StartDateTime] [datetime] NULL,
    [Hour] [int] NULL,
    [Minute] [int] NULL,
  CONSTRAINT [PK_Dim_Time] PRIMARY KEY CLUSTERED
  ([TimeID] ASC)
  ) ON [PRIMARY]
GO

Then I have an Incoming Table, which is updated every 5 minutes from the OLTP Database.

CREATE TABLE [dbo].[Stg_IncomingQueue](
    [IncomingID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerID] [int] NOT NULL,
    [TimeID] [int] NULL,
    [InsertTime] [datetime] NULL,
 CONSTRAINT [PK_IncomingQueueMonitor] PRIMARY KEY CLUSTERED
([IncomingID] ASC)
) ON [PRIMARY]
GO

I then have the following While loop. It's purpose is to get the correct 5-Minute time slot (TimeID) that relates to a particular incoming row:

WHILE 0 < (SELECT COUNT(*) FROM [dba_local].[dbo].[Stg_IncomingQueue] WHERE TimeID IS NULL)
BEGIN

    SELECT TOP 1 @IncomingID = IncomingID, @RowInserTime = InsertTime
    FROM [dba_local].[dbo].[Stg_IncomingQueue] WHERE TimeID IS NULL

    ;WITH DimTime
    AS (
        SELECT MAX(TimeID) AS MaxTimeID FROM [dba_local].[dbo].[Dim_Time]
        WHERE StartDateTime < @RowInserTime AND [Minute] IS NOT NULL
    )
    UPDATE [dba_local].[dbo].[Stg_IncomingQueue]
    SET TimeID = (SELECT MaxTimeID FROM DimTime)
    WHERE IncomingID = @IncomingID

END

It's such a simple process, and yet I cannot figure out a simpler way to update the TimeID. As per the CTE SELECT in the loop, I need to get the MAX(TimeID) where the StartDateTime is less then the rows InsertTime.
Because time is the only relationship, I am struggling with all options to do this in 1 query without the loop, but I feel it is possible

Please can someone help me out here either with a better option or confirming that this is the simplest way.

Thank you very much for your time and assistance.
Wade

I created the following minimally complete and verifiable example, based on the two tables in your original question.  It uses the LEAD T-SQL statement to obtain a time range from the dbo.Dim_Time table, which can be compared to the incoming rows quite easily.

IF OBJECT_ID(N'dbo.Stg_IncomingQueue', N'U') IS NOT NULL
DROP TABLE dbo.Stg_IncomingQueue;

IF OBJECT_ID(N'dbo.Dim_Time', N'U') IS NOT NULL
DROP TABLE dbo.Dim_time;

CREATE TABLE dbo.Dim_Time(
    TimeID int IDENTITY(1,1) NOT NULL,
    StartDateTime time(0) NULL,
  CONSTRAINT PK_Dim_Time PRIMARY KEY CLUSTERED
  (TimeID ASC)
  ) ON [PRIMARY]
GO

;WITH src AS
(
    SELECT TOP (10) sv.number
    FROM master.dbo.spt_values sv
    WHERE sv.type = N'P'
    ORDER BY sv.number
)
INSERT INTO dbo.Dim_Time (StartDateTime)
SELECT TOP(289) CONVERT(time(0), DATEADD(minute, (s3.number * 100 + s2.number * 10 + s1.number) * 5, CONVERT(time(0), '00:00:00')))
FROM src s1
    CROSS JOIN src s2
    CROSS JOIN src s3
ORDER BY s3.number * 100 + s2.number * 10 + s1.number

CREATE TABLE dbo.Stg_IncomingQueue(
    IncomingID int IDENTITY(1,1) NOT NULL,
    CustomerID int NOT NULL,
    TimeID int NULL,
    InsertTime datetime NULL,
 CONSTRAINT PK_IncomingQueueMonitor PRIMARY KEY CLUSTERED
(IncomingID ASC)
) ON [PRIMARY]
GO

INSERT INTO dbo.Stg_IncomingQueue (CustomerID, InsertTime)
VALUES (1, DATEADD(SECOND, CONVERT(int, CRYPT_GEN_RANDOM(4), 0), '1901-01-01 00:00:00'))
    , (2, DATEADD(SECOND, CONVERT(int, CRYPT_GEN_RANDOM(4), 0), '1901-01-01 00:00:00'))
    , (3, DATEADD(SECOND, CONVERT(int, CRYPT_GEN_RANDOM(4), 0), '1901-01-01 00:00:00'))
    , (4, DATEADD(SECOND, CONVERT(int, CRYPT_GEN_RANDOM(4), 0), '1901-01-01 00:00:00'));

This piece replaces your entire WHILE loop, with a single UPDATE statement, which is both more efficient, and easier to understand.

UPDATE dbo.Stg_IncomingQueue
SET TimeID = t.TimeID
FROM dbo.Stg_IncomingQueue iq
    INNER JOIN (
        SELECT dt.TimeID
            , dt.StartDateTime
            , EndDateTime = LEAD(dt.StartDateTime, 1) OVER (ORDER BY dt.StartDateTime)
        FROM dbo.Dim_Time dt
        ) t ON CONVERT(time(0), iq.InsertTime) >= t.StartDateTime AND CONVERT(time(0), iq.InsertTime) < t.EndDateTime;

The results, compared side-by-side with the Dim_Time table:

SELECT *
FROM dbo.Stg_IncomingQueue iq
    INNER JOIN dbo.Dim_Time dt ON iq.TimeID = dt.TimeID;

The output looks like:

╔════════════╦════════════╦════════╦═════════════════════════╦════════╦═══════════════╗
║ IncomingID ║ CustomerID ║ TimeID ║       InsertTime        ║ TimeID ║ StartDateTime ║
╠════════════╬════════════╬════════╬═════════════════════════╬════════╬═══════════════╣
║          1 ║          1 ║    271 ║ 1875-06-30 22:31:49.000 ║    271 ║ 22:30:00      ║
║          2 ║          2 ║    116 ║ 1857-07-01 09:38:59.000 ║    116 ║ 09:35:00      ║
║          3 ║          3 ║    218 ║ 1854-09-18 18:08:39.000 ║    218 ║ 18:05:00      ║
║          4 ║          4 ║    221 ║ 1860-05-31 18:22:25.000 ║    221 ║ 18:20:00      ║
╚════════════╩════════════╩════════╩═════════════════════════╩════════╩═══════════════╝

Assuming there isn't a massive amount of incoming rows, this may work fairly well.  Be aware, I'm using CONVERT() to convert the incoming datetime column into a time(0) value, which comes at a cost of the query optimizer not being able to use available statistics to help create a great plan.  The "actual" query plan for the insert statement shows this warning:

  Type conversion in expression (CONVERT(time(0),[iq].[InsertTime],0)>=[dt].[StartDateTime]) may affect "SeekPlan" in query plan choice, Type conversion in expression (CONVERT(time(0),[iq].[InsertTime],0)<[Expr1002]) may affect "SeekPlan" in query plan choice.

If you need to avoid the type-conversion during the update, you can move that workload to the insert operation by updating the definition of dbo.Stg_IncomingQueue to include a persisted computed column, as in:

CREATE TABLE dbo.Stg_IncomingQueue(
    IncomingID int IDENTITY(1,1) NOT NULL,
    CustomerID int NOT NULL,
    TimeID int NULL,
    InsertTime datetime NULL,
    InsertTime0 AS CONVERT(TIME(0), InsertTime) PERSISTED
 CONSTRAINT PK_IncomingQueueMonitor PRIMARY KEY CLUSTERED
(IncomingID ASC)
) ON [PRIMARY]
GO

The update statement then becomes:

UPDATE dbo.Stg_IncomingQueue
SET TimeID = t.TimeID
FROM dbo.Stg_IncomingQueue iq
    INNER JOIN (
        SELECT dt.TimeID
            , dt.StartDateTime
            , EndDateTime = LEAD(dt.StartDateTime, 1) OVER (ORDER BY dt.StartDateTime)
        FROM dbo.Dim_Time dt
        ) t ON iq.InsertTime0 >= t.StartDateTime AND iq.InsertTime0 < t.EndDateTime;


Spatial Index Compression I am looking to turn page compression on on a spatial index. The instance is 2008 Enterprise. The index is pre-existing on a vendor supplied app. In searching through BOL, it states that compression and fillfactor are both allowed.

ALTER INDEX [ix_city_citypoint] on city
REBUILD WITH (FILLFACTOR = 90, DATA_COMPRESSION = PAGE)

  Msg 153, Level 15, State 4, Line 12
  Invalid usage of the option data_compression in the ALTER INDEX REBUILD statement.

original create:

CREATE SPATIAL INDEX [ix_city_citypoint] ON [dbo].[city]
(
    [citypoint]
)USING  GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM),
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [primary]
GO

I can always ignore the index as it's small but now it's morphed into one of those "teachable" moments. Thank you for any insight

Your syntax for ALTER INDEX...REBUILD WITH (DATA_COMPRESSION...) is correct.  The problem is actually revealed in the error message:

  Invalid usage of the option data_compression in the ALTER INDEX
  REBUILD statement.

For a spatial index in SQL Server 2008, using the data_compression option is not supported. According to Microsoft Documentation, support for data compression was added for spatial indexes in SQL Server 2012:

  DATA_COMPRESSION = {NONE | ROW | PAGE}

  Applies to: SQL Server 2012 through SQL Server 2017, SQL Database.

If/when you upgrade to a newer version of SQL Server, your original ALTER INDEX statement would work to rebuild the index using PAGE compression. Alternatively, as @AaronBertrand points out in a comment, you can also use CREATE...WITH (DROP_EXISTING=ON, DATA_COMPRESSION=PAGE... to change the compression settings, however this requires re-supplying the entire index definition, where the ALTER requires you only specify the options you are altering.


How to correctly create a table that has multiple columns self reference another row? EmpNo |Designation     | ReportsTo | Room
10    | Director       | NULL      | 1
20    | Senior Manager | 10        | 2
30    | Typist         | 20        | (THIS GUY REFERS TO empno 10 room)
40    | Programmer     | 30        | 4

How do I correctly make EmpNo 30's room refer to his boss', but not make EmpNo 40 refer to his boss'?

In the relational model, each row represents a dataset representing a single item and so you do not 'refer' to another row's value.

The concept of an employee sharing a room of another employee is similar in fact to the relationship you already have of an employee having a manager. This could be represented similarly in the table like so:

EmpNo |Designation     | ReportsTo | SharesRoomWith | Room
10    | Director       | NULL      | NULL           | 1
20    | Senior Manager | 10        | NULL           | 2
30    | Typist         | 20        | 10             | NULL
40    | Programmer     | 30        | NULL           | 4

Where a NULL in SharesRoomWith means that an employee has their own room.
You could then query the list of employees and their rooms:

-- Employees that have their own room
select
    e.EmpNo,
    e.Designation,
    e.Room
from Employee e
where e.SharesRoomWith is NULL

UNION
-- Employees that share a room
select
    e.EmpNo,
    e.Designation,
    e.Room
from Employee e

join Employee e2
on e.SharesRoomWith = e2.EmpNo

to get:

EmpNo | Designation    | Room
10    | Director       | 1
20    | Senior Manager | 2
30    | Typist         | 1
40    | Programmer     | 4

This achieves the result that if the Director changes rooms, an update is only made in one location (the room number) and the typist's room will also reflect that change.

Note this design is not fully normalised so may not be an ideal solution, but demonstrates at least how you could represent that relationship.


An aggregate may not appear in the WHERE clause I'm attempting to execute the following query to return a data set where the Updated Date is the most recent. I'm thinking my joins are causing the issue, but I'm not sure, and have not made any progress.

   SELECT wd.SAPID, rd.RequestID, vd.VersionTableID,rd.ProjectReference,contactinfo.FirstName,contactinfo.LastName,
                 wd.ActualEndDate, wd.IsIndependentContractor,we.AssignmentStartDate, vd.VersionNo, we.CreatedDate, we.UpdatedDate
   FROM RequestDetails rd
   INNER JOIN  WorkerDetails wd ON  rd.RequestTableID = wd.RequestTableID
   INNER JOIN WorkerExtension we ON we.WorkerID=wd.WorkerID
   INNER JOIN dbo.Address_Info addressinfo ON addressinfo.WorkerID = wd.WorkerID
   INNER JOIN dbo.Contact_Info contactinfo ON contactinfo.WorkerID = we.WorkerID
   INNER JOIN VersionDetails vd ON   vd.RequestTableID = rd.RequestTableID
   WHERE we.AssignmentEndDate >= '2/16/2016' AND EndDate <= '2/20/2016' AND contactinfo.FirstName = 'Barsegyan' AND we.UpdatedDate = (SELECT MAX(we.UpdatedDate))
   GROUP BY wd.SAPID, rd.RequestID, vd.VersionTableID,rd.ProjectReference,contactinfo.FirstName,contactinfo.LastName,
                 wd.ActualEndDate, wd.IsIndependentContractor,we.AssignmentStartDate, EndDate, vd.VersionNo, we.CreatedDate, we.UpdatedDate

A sample of the data I'm querying:

And the error:

AND we.UpdatedDate = (SELECT MAX(we.UpdatedDate)) is the offending statement.

Two easy ways to solve this are to get the Max(we.UpdateDate) in a variable first, or add an inner join (select max(we.UpdateDate) from WorkerExtension ) and joing that back to the "we" aliased table.


DROP LOGIN fails on SQL Server 2014 We have a database with an owner 'dbowner'. Our install procedure drops the login for user 'dbowner' and creates it again.

On any other version of SQL Server the following SQL query succeeds:

USE [master]
DROP LOGIN 'dbowner'

On SQL Server 2014 the same query returns the following error:

  Login 'dbowner' owns one or more database(s). Change the owner of the database(s) before dropping the login.

Can anyone explain why this would be different on SQL Server 2014? Dropping of login works on all other versions of SQL Server (2005, 2008 and 2012).

Is there any setting to force the same to work on SQL Server 2014?

Update:

I can replicate this with an example that uses Windows users. Any ideas?

CREATE LOGIN [comp\winuser] FROM WINDOWS;
GO
CREATE DATABASE mydb;
GO
ALTER AUTHORIZATION ON DATABASE::mydb TO [comp\winuser];
GO
DROP LOGIN [comp\winuser];
GO

Update 2:

Thank you for your comments. I agree that dropping the login of owner is not desirable (but works with Windows user everywhere except on SQL Server 2014). However our installation scripts currently depend on it and the extremely complex database installation procedure was made by some guy 15 years ago (he no longer works with us). It is an area that we like to avoid changing if possible (as long as it works).

As I see it, we have three options:

Make SQL 2014 to act like the previous versions and leave the scripts as they are. We prefer this option and I posted here to see if this could be done.
Change the owner to some other user, drop and create owner login and move the owner back. This is a hack but might work.
Change the way we handle users and stop dropping login of the owner. This would be the correct way but also the biggest change.

For SQL Authentication logins, this is no different in SQL Server 2014 than in previous versions. I just did this in 2008:

CREATE LOGIN dbowner WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
CREATE DATABASE mydb;
GO
ALTER AUTHORIZATION ON DATABASE::mydb TO dbowner;
GO
DROP LOGIN dbowner;
GO

  Msg 15174, Level 16, State 1
  Login 'dbowner' owns one or more database(s). Change the owner of the database(s) before dropping the login.

I can't drop the database until I do this:

ALTER AUTHORIZATION ON DATABASE::mydb TO sa;

For Windows Authentication logins, As Dan pointed out, prior to SQL Server 2014, this check wasn't performed for Windows logins, so you could leave databases in a weird state by dropping their owner. Your second option sounds best to me, and it only means adding the above line to your script. Another option is to just not make a Windows login the owner (I'm not sure why you're making them owners instead of just giving them appropriate rights in the database), or stop dropping the logins as part of the process, or both.


Windows native backups makes SQL Server think that databases backup has been done community.
I've Windows Server 2008 R2 which hosts few virtual machines.
One of machines is Windows 2008 R2 server with SQL server 2008 R2 Express installed.
Here's the thing:
I wrote a script that backups databases at 05:00AM everyday.
Mon, Wed, Fri - Full backups.
Tue, Thu, Sat, Sun - Transaction log backups.

A few days ago i had to try to restore data from backups and i couldn't. I recieved an error message that my Transaction log is to recent to use. It was on Tuesday. So basicaly i had to restore monday's full backup and tuesday's early morning transaction log backup.

I started to research the cause of it and soon discovered that everyday at 04:00AM and 11:00PM sql server backups all the databases to some VIRTUAL_DEVICE with two different SIDs.
I realized that at 11:00PM starts Windows Server Backups on that concrete virtual machine. Backup contains only Bare metal recovery + System state + C:
Later i understood from where comes backup at 04:00AM. There's similar story. At 04:00AM starts backup of Hyper-v Host with the same parameters(it's strange but sql server somehow realizes that machine is being backed up.

So we have:

Backup of Virtual machine at 11:00PM
Backup of Hyper-V host at 04:00AM

After procedures described above EventLog recieves message that SQL server has been backed up. But There's no physical place where those "backups" are stored. Since SQL Server thinks that he'd been backuped he change LSNs so any correct backups of transaction logs can't realy be restored after another correct full backup.

Please note: Hyper-v host and virtual machine itself are being backed up to the personal colume for backups. After assigning letter to the volume i can see VHDs of disk that had been backed up. And no sign of sql databases backup inside.

The main problem is to stop SQL from reacting on system backups.

Looking forward for your replies and thanks in advance,
Alexey.

This KB isn't directly related but the quote in it is:

MSKB2615182: Windows Server Backup May Fail Because of the SQL VSS Writer:

  When Windows Server Backup attempts to backup a disk volume, a Volume Shadow Copy Snapshot is created for the volume. When the snapshot is created any VSS writer associated with the volume is called.

If there are SQL Server VSS writer backups being taken then you will see records in the MSDB.dbo.BackupMediaFamily table in the physical_device_name column. You might want to look into disabling the SQL Writer Service.

TechNet: SQL Writer Service


ORDER BY on varchar Column with Numbers I need help putting together a query so I can take action when the following occurs:
"Trigger an email when regression result fails three or more label versions consecutively for a given block"
EDIT- had to remove img
\The example above is a good case. But if I have a block with
"IP_BLOCK_X.info#9"
"IP_BLOCK_X.info#8"
"IP_BLOCK_X.info#11"
The result of the query will be ordered incorrectly (9, then 8, then 1 is evaluated, not '11').
EDIT: Here is the table with column names and data types for reference. Table name is 'dbo.regression_full'

The BIG Picture
In my mind, what I need to do for a given block:

Get the last 3 unique labels from Regression_name (top/distinct?)

Out of those, get Block, Timestamp, Regression_name, and Result

ANSWER:
I got it!

Query

Results

Thanks for all the help, this website is amazing! I'll definitely contribute more in areas I know. This was my first post, thanks!

If your Label has fixed pattern, i.e. IP_BLOCK_X.info#NNN, where NNN is an integer.
You can easily do this way

use tempdb
drop table #t;
create table #t (Block varchar(100), Label varchar(100));
go
insert into #t (Block, Label) values ('Block_A','IP_BLOCK_X.info#9'), ('Block_A','IP_BLOCK_X.info#8'), ('Block_A','IP_BLOCK_X.info#11')
go

select * from #t
order by cast(substring(Label, len('IP_BLOCK_X_info#')+1, 100) as int)

The result is

Block   Label
Block_A IP_BLOCK_X.info#8
Block_A IP_BLOCK_X.info#9
Block_A IP_BLOCK_X.info#11

Edit:

After the question is updated, as promised, here is my best guess for the answer.

use tempdb
--drop table #t;
create table #t (Regression_name varchar(100), [TimeStamp] datetime, Block varchar(100), Label varchar(100));
go
insert into #t (Regression_name, [TimeStamp], [Block], Label)
values ('Regression_1', '2016-11-01','Block_A','IP_BLOCK_X.info#8')
, ('Regression_1', '2016-11-02','Block_A','IP_BLOCK_X.info#9')
, ('Regression_1', '2016-11-03','Block_A','IP_BLOCK_X.info#11')
, ('Regression_1', '2016-11-04','Block_A','IP_BLOCK_X.info#21')
, ('Regression_2', '2016-11-04','Block_B','IP_BLOCK_X.info#8')
, ('Regression_2', '2016-11-05','Block_B','IP_BLOCK_X.info#9')
, ('Regression_2', '2016-11-06','Block_B','IP_BLOCK_X.info#11')
, ('Regression_2', '2016-11-07','Block_B','IP_BLOCK_X.info#21')
go

-- find the latest (by [TimeStamp] top 3 Regression_name for each [Block] and order by [Label]'s last numeric number
; with c as (
select distinct Regression_name, [Block], [TimeStamp]
, rnk = ROW_NUMBER() over (partition by [Block], Regression_name order by [TimeStamp] desc)
from #t) --select * from c
select t.*
from #t t
inner join c
on t.Regression_name = c.Regression_name
and t.Block = c.Block
and t.TimeStamp = c.TimeStamp
where c.rnk <=3
order by t.Regression_name, t.Block, t.[TimeStamp],  cast(substring(t.Label, len('IP_BLOCK_X_info#')+1, 100) as int)


AWS EBS I/O Bottleneck I'm hoping someone can help me understand some EBS Bottlenecks that we are experiencing with a new Windows SQL Server instance we're configuring. We're running some tests by conducting a full backup so we can see how the I/O performs. However, the backup causes I/O blocking issues.
Its an r3.4xlarge instance which should have 250MB/s throughput and we've set up EBS with 7500 IOPs
However, we're only ever achieving a maximum of the following according to Cloudwatch:

Read Bandwidth: 140,486 KiB/s
Write Bandwidth: 22,448 KiB/s
TOTAL: 162934 KiB/s or 166 MB/s
Read Ops: 3468 OPS/s
Write Ops: 592 OPS/s
TOTAL: 4060 OPS/s

We cant understand why we're never achieving maximum IOPs or maximum throughput. Where else could it be bottlenecking?

We have had this answer back from AWS Support which shows where the bottleneck is occurring:

If you would like to reach 250MB/s, the maximum throughput of r3.4xlarge, you can change the volume to Provisioned IOPS(io1). IO1 has maximum throughput of 320 MiB/s. Another approach is to build RAID 0 array with 2 gp2 EBS volume. You can see the guidance from link [2].


Error trying to run SSIS Packages in SQL Server 2008 SQL Server Agent I am trying to run a number of SQL Server 2008 stored procedures that are wrapped in SSIS packages consisting mainly of Execute SQL Tasks. They run fine in Visual Studio 2008 after deploying to a folder on the SQL Server server.
The problem is when I created a job in SQL Server Agent and tried to run the job.  That is when I get the error:

Executing the query "EXEC ? = dbo.usp_Clear_All_JDAWG_Tables" failed with the following error:

"The EXECUTE permission was denied on the object 'usp_Clear_All_JDAWG_Tables' database 'JDAWGD' schema 'dbo'.".

Possible failure reasons:
Problems with the query
"ResultSet" property not set correctly
parameters not set correctly
or connection not established correctly.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).

The usp_Clear_All_JDAWG_Tables is the name of the first of many packages this job is trying to run.  The stored procs themselves do not use parameters but in the SQL Task I have commands like this to run the stored proc:
EXEC ? = dbo.usp_Load_JDAWG_Tables_From_Persons_All_Agencies

Is there a better/different way to run a stored procedure in the SQL Task?
As a side note, I was able to create and successfully run an Agent job that directly executed the stored procedures that the packages run.

It sounds like your connection manager is setup to use windows Authentication.  If that is the case, when you execute the package via the SQL Server Agent it will run with credentials of the agent.

You need to do one of three things:

Give the account that the SQL Server Agent runs as appropriate permissions to execute SPs in your database.
Setup a proxy account for the agent with the appropriate permissions (as suggested by Jon Seigel).
Use a different authentication method - i.e SQL Server Auth.

I would go with option 1 if possible, but it depends what your company's policies are.


DELETE seems to hang The following command:

DELETE FROM hours WHERE id IN (SELECT id FROM holdkey WHERE persons_id=60)

seems to hang indefinitely. However, the corresponding SELECT * command completes in a fraction of a second, see 'explain' command below.

Deleting rows individually is fast too.

Since the field 'id' is the primary key, I don't see any reason for the DELETE command to take any significant amount of time.

Is there an explanation and/or a work-around?

The table 'hours' contains approx 1 million rows, and 161 rows are to be deleted using the above command.

I'm using MySQL version 5.5 with the InnoDB engine. The server is completely idle.

Removing the foreign key from the 'hours' table and then changing the table engine to MyISAM makes no difference.

The table hours looks like this:

MariaDB [MSG_PLAN]> show create table hours \G;
*************************** 1. row ***************************
       Table: hours
Create Table: CREATE TABLE `hours` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `persons_id` int(6) NOT NULL,
  `year_nr` int(6) NOT NULL,
  `week_nr` int(6) NOT NULL,
  `week_day` int(6) NOT NULL,
  `activity_id` int(6) NOT NULL,
  `hours` decimal(3,1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `persons_id` (`persons_id`),
  CONSTRAINT `hours_ibfk_1` FOREIGN KEY (`persons_id`) REFERENCES `persons` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1057141 DEFAULT CHARSET=latin1

MariaDB [MSG_PLAN]> EXPLAIN SELECT *  FROM hours WHERE id IN (SELECT id FROM holdkey WHERE persons_id=60);
+------+--------------+-------------+--------+---------------+---------+---------+---------------------+------+-------------+
| id   | select_type  | table       | type   | possible_keys | key     | key_len | ref                 | rows | Extra       |
+------+--------------+-------------+--------+---------------+---------+---------+---------------------+------+-------------+
|    1 | PRIMARY      | <subquery2> | ALL    | distinct_key  | NULL    | NULL    | NULL                |  851 |             |
|    1 | PRIMARY      | hours       | eq_ref | PRIMARY,id    | PRIMARY | 4       | MSG_PLAN.holdkey.id |    1 |             |
|    2 | MATERIALIZED | holdkey     | ALL    | NULL          | NULL    | NULL    | NULL                |  851 | Using where |
+------+--------------+-------------+--------+---------------+---------+---------+---------------------+------+-------------+

MariaDB [MSG_PLAN]> show create table holdkey \G;
*************************** 1. row ***************************
       Table: holdkey
Create Table: CREATE TABLE `holdkey` (
  `id` int(6) NOT NULL DEFAULT '0',
  `persons_id` int(6) NOT NULL,
  `year_nr` int(6) NOT NULL,
  `week_nr` int(6) NOT NULL,
  `week_day` int(6) NOT NULL,
  `activity_id` int(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

You found a (known) limitation of the optimizer of MySQL.

The problem with DELETE FROM table WHERE column IN (SELECT ...) is that the subquery (SELECT) is executed for each row of the table that has a different value for column.  In your case, as it is a PRIMARY KEY, it is executed a million times.  More details in http://dev.mysql.com/doc/refman/5.6/en/subquery-restrictions.html.

The reason explaining why the SELECT * FROM table WHERE column IN (SELECT ...) is faster is that the optimizer is rewriting the query as a JOIN.  You can see that as the EXPLAIN results of the 2 queries are different (MySQL 5.6.21):

mysql> EXPLAIN SELECT * FROM test_jfg.hours WHERE id IN (SELECT id FROM test_jfg.holdkey WHERE persons_id=60);
+----+-------------+---------+--------+---------------+---------+---------+---------------------+------+------------------------------+
| id | select_type | table   | type   | possible_keys | key     | key_len | ref                 | rows | Extra                        |
+----+-------------+---------+--------+---------------+---------+---------+---------------------+------+------------------------------+
|  1 | SIMPLE      | holdkey | ALL    | NULL          | NULL    | NULL    | NULL                |  920 | Using where; Start temporary |
|  1 | SIMPLE      | hours   | eq_ref | PRIMARY,id    | PRIMARY | 4       | test_jfg.holdkey.id |    1 | End temporary                |
+----+-------------+---------+--------+---------------+---------+---------+---------------------+------+------------------------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN DELETE FROM test_jfg.hours WHERE id IN (SELECT id FROM test_jfg.holdkey WHERE persons_id=60);
+----+--------------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type        | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+--------------------+---------+------+---------------+------+---------+------+--------+-------------+
|  1 | PRIMARY            | hours   | ALL  | NULL          | NULL | NULL    | NULL | 997457 | Using where |
|  2 | DEPENDENT SUBQUERY | holdkey | ALL  | NULL          | NULL | NULL    | NULL |    920 | Using where |
+----+--------------------+---------+------+---------------+------+---------+------+--------+-------------+
2 rows in set (0.00 sec)

According to http://dev.mysql.com/doc/refman/5.6/en/explain-output.html#explain_select_type, DEPENDENT SUBQUERY means:

  For DEPENDENT SUBQUERY, the subquery is re-evaluated only once for each set of different values of the variables from its outer context.

To solve that, I do not see other solutions that doing 2 (or more) queries:

Get the results from the SELECT,
If the resultset is small, do a DELETE ... IN (explicit list)

If the resultset is large, you will need to do many DELETE like above with a subset of the list.


Foreign Key violation - not sure why I have the following two tables with a foreign key on status_id:

mysql> describe usr_cookbook;
+-----------+-----------+------+-----+-------------------+-------+
| Field     | Type      | Null | Key | Default           | Extra |
+-----------+-----------+------+-----+-------------------+-------+
| user_id   | int(11)   | NO   | PRI | NULL              |       |
| recipe_id | int(11)   | NO   | PRI | NULL              |       |
| status_id | int(11)   | NO   | MUL | NULL              |       |
| added_ts  | timestamp | NO   |     | CURRENT_TIMESTAMP |       |
+-----------+-----------+------+-----+-------------------+-------+

mysql> describe usr_cookbook_status;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| status_id | int(11)     | NO   | PRI | NULL    |       |
| name      | varchar(45) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

There's two rows in usr_cookbook_status:

mysql> select * from usr_cookbook_status;
+-----------+-----------+
| status_id | name      |
+-----------+-----------+
|         1 | Try Soon  |
|         2 | Favorites |
+-----------+-----------+

So I should be good to insert into usr_cookbook right?

mysql> insert into usr_cookbook (user_id, recipe_id, status_id) values (3, 5, 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`tomato`.`usr_cookbook`, CONSTRAINT `fk_cookbook_status` FOREIGN KEY
(`status_id`)
REFERENCES `usr_cookbook_status` (`status_id`) ON DELETE NO ACTION ON UPDATE
NO ACTION)

Why the constraint violation?  The status_id I'm using (1) exists in the parent table.  I'm sure there's something simple I'm missing here...

When I looked at the DDL per Brian's suggestion the problem immediately became clear: The usr_cookbook_status table was using MyISAM engine.  I switched it to InnoDB and everything works now.


How can I setup users/groups in PostgreSQL so that each user has privileges on objects created by other users in the same group? I have created a group (role) called "employees" and I've created some users that are its member and that inherit its rights. I have a database owned by the group "employees".

The goal: To setup things in a way that allows all of the users to work with all of the objects in the database.

The problem: I can't expect the users to set the owner to "employees" when they create a new object, because they use various limited interfaces to work with the database. When they create a schema or a table, it gets created with the user as its owner, which means that the other users don't have rights on that schema/table.

I'm using PostgreSQL 11.2.

Assuming you already have

GRANT employees TO john;

Just do

ALTER USER john SET ROLE TO employees;

Now when john connects he will automatically have role employees and when he creates an object it will be owned by employees;


Table column length is fine in MySQL 5.5 and 5.6, but upgrading to 5.7 results in too many columns So I have a CMS that is being upgraded from MySQL 5.5 -> 5.7. One table has a 1000+ columns, but it works fine in 5.5 and 5.6. When reaching 5.7, queries involving this table result in a 1117 error. (all tables in DB are MyISAM).

It works fine in MySQL 5.5 (the related CMS works fine). I upgrade to MySQL 5.6 and everything continues to work fine.

Then when I upgrade to MySQL 5.7(.23), I get an error about too many columns, and even after removing 20+ columns, I still get the error.

What could have changed? I upgraded in order (5.5 -> 5.6 first, tested, then 5.6 -> 5.7)

Is there possibly a setting I'm missing? Does the mysql_upgrade for 5.7 change columns somehow and screws up their limit by adding extra meta data that causes each column to take up more data? I know that column limit is theoretical but I don't know why it works in the previous two versions and not in 5.7.

Any thoughts?

Edit

After removing ~300 columns, the error stops. But this is a show stopper, and I'd really like to know why 5.7 can't handle what 5.5 and 5.6 can, and if it is a configuration issue or something. Thanks for any help.

A guess...  See what you get for

SHOW VARIABLES LIKE '%engine%';

This may indicate that you are not really using MyISAM everywhere.

In the future, MyISAM is likely to go away.  So, start thinking about how to deal with InnoDB.

TINYTEXT is perhaps always worse than a suitable VARCHAR.

More than a hundred columns in a single table is usually a bad schema design.  Think about how your table can be logically broken up.

Don't splay an array across columns.


Reducing query cache for mostly INSERT-heavy database? What sort of MySQL query cache should I choose considering the following...

Large, rapidly growing database (I got 500,000 rows).
UPDATEs practically never happen.
SELECTs are rare, but when they do happen, they'll usually happen in huge batches.
Lots of small INSERTs.
A row is not usually SELECTed multiple times within a small period of time.
I want to reduce memory footprint (server is using lots of memory).

Would I be looking for a large cache, or small cache?

From what I understand a cache isn't hugely important as repeated SELECTs are very rare.

query_cache_type = 0
query_cache_size = 0

Seriously, turning off the QC is probably best. especially based on what you said.  Keep in mind that every INSERT (or UPDATE) to a table causes all entries in the QC to be purged.  Furthermore, if the QC size is large, (say, over 50M), the purge time slows down the write.

The ENGINE's cache is important to both reads and writes.  Generally MyISAM's key_buffer_size should be 20% of available RAM.  OR InnoDB's innodb_buffer_pool_size should be 70% of available RAM.


How to design database table to store process status? I have an application (ProcessMonitor) that monitor some constantly running process at fixed interval (like 30 seconds, but can be changed by user) and store the process status to a database table, the table looks like this:

| id         | int             | NO   | PRI | NULL    | auto_increment |
| pid        | int             | YES  |     | NULL    |                |
| pstatus    | int             | YES  |     | NULL    |                |
| timestamp  | datetime        | YES  |     | NULL    |                |

Obviously, this table grows very fast because for every single process, there will be 2880 new records inserted into table per day if the interval is 30 seconds. Given the fact that the process's status is not changed very often  may keep one state for several hours), I decide change the logic of ProcessMonitor to write to database only when the status of process changes using the same table design. There are two problems for the new approach:

There is no way to track when and how long ProcessMonitor is stopped.
Unable to keep track of the change of monitoring interval.

I am feeling that I may need to change the design of the table, but I've not found a good solution, could anyone suggest me a better way to store the process status?

You'll be averaging about a million records a year, which shouldn't be a problem given the table is fairly narrow. I don't see anything wrong with your design (other than a NULLable primary key?). You should be able to easily track when and how long each process takes by comparing timestamps. This is accomplished using a self join.


Free space of tablespace deleting table content (rows) I have a theorical question, last week we have found this error in one database. The error has been given by our app when trying to save some WS data.

ORA-01691: unable to extend lob segment RS2.SYS_LOB0000501482C00010$$ by 128 in tablespace RS2QDDA1

In absence of the dba who has de sysdba rights we have a discussion :

If we delete data of some tabla stored in this tablespace, the tablespace will have free space ? And could be back to work for the application.

We questions that because we configure a purge batch to delete old rows from some tables, but it fails in execution time throwing the same error message defined before ORA-01691: unable to extend lob segment RS2.SYS_LOB0000501482C00010$$ by 128 in tablespace RS2QDDA1

Without understanding the nature of the data contained within this object contains, it is difficult to offer solid advice.

If this were a regular table, then yes; you would delete rows from it and thereby release space within the Tablespace.
The fact that a delete statement results in more data being written to this object makes me think it is non-standard; perhaps an "Auditing" feature of some sort that records everything - including deletions.  That means your only option is to give the Tablespace more room to work with, by adding more datafiles to it or by extending the underlying disk space.

Thinking about performing either operation without your DBA?

My recommendation: Don't.

Very little about Oracle database is as straightforward as we would like it to be, so you need someone who knows what they're doing (and knows what to do when it goes wrong).

Furthermore, you shouldn't even be able to!

If your DBA has given you sufficient privileges to even attempt this operation, then they need their head examining, having little or no concept about database Security (or, most generously, a highly over-developed sense of Trust).


Can I set the maximum locking level that a transaction can acquire? What I want to achieve is something like this:
SET LOCAL max_locking_level="ACCESS SHARE";
ALTER TABLE foo ALTER COLUMN bar SET NOT NULL; -- Error, since it needs an ACCESSS EXCLUSIVE lock

But I haven't been able to find the right setting in the documentation yet.
For some context: we're using flywheel for scripted database migrations, and part of the review process is to make sure that migrations can be applied without downtime. One of the ways we're achieving this is by enforcing (configurable) statement/lock timeouts on all migrations. This is working well, but it still leaves room for error: some migrations need a long statement/lock timeout, which can be acceptable if it doesn't use exclusive locks. It has happened that developers underestimated the lock level an operation needed, and as a result created migrations that hold long ACCESS EXCLUSIVE locks, which will cause downtime when deployed. The only thing that prevents this is a good eye from code reviewers, which isn't reliable enough.
I'm hoping there is a way that we can enforce a max lock level, as this will make it easier to ensure prevent long exclusive locks (i.e. only allow long migrations if the max lock level is low)

In the case of ACCESS SHARE, you could do that with
SET default_transaction_read_only = on;

That will cause an error as soon as anybody wants to take a lock higher than ACCESS SHARE.
But it seems that your real objective is to detect if a transaction takes ACCESS EXCLUSIVE locks. You could do that by querying the pg_locks table after the statements are done:
BEGIN;

/* run statements that lock objects */

SELECT locktype, relation::regclass, objid, classid::regclass
FROM pg_locks
WHERE mode = 'AccessExclusiveLock';

ROLLBACK;

You'd run that on a test database, and it should show you all relations (tables, indexes, sequences, materialized views, or views) and other objects (where classid is the name of the catalog for the object) on which the statements take an ACCESS EXCLUSIVE lock.


Best Way to Partition By City / State in MySQL 5.5 I have a table with ~20M rows and every query against it will include state or city or a combination so I was thinking of partitioning the table using a combo of the two fields like so:

ALTER TABLE `test`.`test`
PARTITION BY RANGE COLUMNS (city, state) (
    PARTITION p0 VALUES LESS THAN ('a','AE'),
    PARTITION p1 VALUES LESS THAN ('c','AE'),
    PARTITION p2 VALUES LESS THAN ('e','AE'),
    PARTITION p3 VALUES LESS THAN ('g','AE'),
    PARTITION p4 VALUES LESS THAN ('i','AE'),
    PARTITION p5 VALUES LESS THAN ('k','AE'),
    PARTITION p6 VALUES LESS THAN ('m','AE'),
    PARTITION p7 VALUES LESS THAN ('o','AE'),
    PARTITION p8 VALUES LESS THAN ('q','AE'),
    PARTITION p9 VALUES LESS THAN ('s','AE'),
    PARTITION p10 VALUES LESS THAN ('u','AE'),
    PARTITION p11 VALUES LESS THAN ('w','AE'),
    PARTITION p12 VALUES LESS THAN ('y','AE'),
    PARTITION p13 VALUES LESS THAN ('a','AK'),
    PARTITION p14 VALUES LESS THAN ('c','AK'),
    PARTITION p15 VALUES LESS THAN ('e','AK'),
    PARTITION p16 VALUES LESS THAN ('g','AK'),
    PARTITION p17 VALUES LESS THAN ('i','AK'),
    PARTITION p18 VALUES LESS THAN ('k','AK'),
    PARTITION p19 VALUES LESS THAN ('m','AK'),
    PARTITION p20 VALUES LESS THAN ('o','AK'),
    PARTITION p21 VALUES LESS THAN ('q','AK'),
    PARTITION p22 VALUES LESS THAN ('s','AK'),
    PARTITION p23 VALUES LESS THAN ('u','AK'),
    PARTITION p24 VALUES LESS THAN ('w','AK'),
    PARTITION p25 VALUES LESS THAN ('y','AK'),
    PARTITION p26 VALUES LESS THAN ('a','AL'),
    PARTITION p27 VALUES LESS THAN ('c','AL'),
    PARTITION p28 VALUES LESS THAN ('e','AL'),
    PARTITION p29 VALUES LESS THAN ('g','AL'),
    PARTITION p30 VALUES LESS THAN ('i','AL'),
    PARTITION p31 VALUES LESS THAN ('k','AL'),
    PARTITION p32 VALUES LESS THAN ('m','AL'),
    PARTITION p33 VALUES LESS THAN ('o','AL'),
    PARTITION p34 VALUES LESS THAN ('q','AL'),
    PARTITION p35 VALUES LESS THAN ('s','AL'),
    PARTITION p36 VALUES LESS THAN ('u','AL'),
    PARTITION p37 VALUES LESS THAN ('w','AL'),
    PARTITION p38 VALUES LESS THAN ('y','AL'),
    PARTITION p39 VALUES LESS THAN ('a','AR'),
    PARTITION p40 VALUES LESS THAN ('c','AR'),
    PARTITION p41 VALUES LESS THAN ('e','AR'),
    PARTITION p42 VALUES LESS THAN ('g','AR'),
    PARTITION p43 VALUES LESS THAN ('i','AR'),
    PARTITION p44 VALUES LESS THAN ('k','AR'),
    PARTITION p45 VALUES LESS THAN ('m','AR'),
    PARTITION p46 VALUES LESS THAN ('o','AR'),
    PARTITION p47 VALUES LESS THAN ('q','AR'),
    PARTITION p48 VALUES LESS THAN ('s','AR'),
    PARTITION p49 VALUES LESS THAN ('u','AR'),
    PARTITION p50 VALUES LESS THAN ('w','AR').....

However, this fails to work with this error:

ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

What would be a valid solution for partitioning this table?

Why not add two indexes:

a) A two column index for (state, city) - handles queries for state only, then state and city

b) An index on city - queries on cities only

To tune for performance further you may be able to add lookup tables for state and city then use numeric keys to speed up query performance.


Mysql: does full-text cover normal index? I have seen this question everywhere but the answer is just explaining some technical stuff without a clear verdict so I have to make my question so narrow that no one mistakes it with another question:

I have a table with millions of records. This table has a string field and I need to search it normally and full-text. So of course I need it to be fulltext but does it make it faster for my normal search if I add "index" as well?

Another word: Does full-text cover simple index?

No.

(Is that answer short enough and decisive enough?)

A regular index can be used for these, yet a FULLTEXT index cannot:

WHERE price = 12.34  -- numeric comparison
WHERE quantity > 100  -- not "equality"
WHERE country_code = 'US'  -- possibly to short for FULLTEXT
WHERE col LIKE '% foo %'  -- note explicit check for spaces
WHERE col LIKE '%=%'  -- punctuation

But for searching for a "word" inside a "text" column, FULLTEXT is excellent.  You have to live within its limitations.


System parameter in catalog tcpip node What is the purpose of the "system" parameter when cataloging tcpip nodes?

>>-CATALOG--+-------+--+-TCPIP NODE--+--nodename---------------->
            '-ADMIN-'  +-TCPIP4 NODE-+
                       '-TCPIP6 NODE-'

>--REMOTE--+-hostname-----+--SERVER--+-service-name-+----------->
           +-IPv4 address-+          '-port number--'
           '-IPv6 address-'

>--+----------------+--+--------------------------------+------->
   +-SECURITY SOCKS-+  '-REMOTE_INSTANCE--instance-name-'
   '-SECURITY SSL---'

>--+---------------------+--+-------------------------------+--->
   '-SYSTEM--system-name-'  '-OSTYPE--operating-system-type-'

>--+------------------------+----------------------------------><
   '-WITH--"comment-string"-'

What is the difference with "hostname"? I always ignore this value, but I want to know its purpose. Is it just informative? where can I see the effect of including or ignoring it?

I think it gives you an opportunity to supply a human-friendly name for the system if its hostname is not defined or is not informative, such as d33a891p.z30b.l8.mycompany.com


Cannot drop the user because it does not exist or you do not have permission I am getting this message for some users when executing sp_revokedbaccess procedure for database users. Users I am trying to remove are selected from sys.database_principals with type = S, W or G (sql server or windows users) and are not system users (principal_id > 4).

I checked if the user I can't remove owns any schema and it is not the case. The user is not an orphan user also. I am comparing a user that was removed and a user that gave me this error and can't find any differences. I do see the user in the database security folder so it does exist and has a corresponding server login (no sid mismatch).

Also, I am a sysadmin on a server level and should have permissions. I have been stuck with this for the last couple of days and can't solve it. I am not a DBA, just a developer and maybe there is something else I am missing. Please help.

Here is a screenshot of sys.database_principals. No problem with the first user (principal id = 7) but getting "Cannot drop the user...." with the second one (principal_id = 8):

I don’t have an answer, just a handful of suggestions, each possibly less useful than the last.

DB created on server A, moved.copied to server B, and database user does not “synch up” with instance Login. Happens with SQL Authentication. I reall don’t think this is it, but it’s worth a mention.

Poorly chosen name (user “Table”), or oddball characters (“My Login”)? Also extremely unlikely.

Try running this, then run the generated script? Rival DBAs may have created a login with a trailing space?

SELECT 'DROP USER [' + name + ']'
 from sys.database_principals
 where principal_id = XX  --  Target Id goes here

Similarly, go through SSMS, Object Explorer, drill down to Security, Users, right click the problem entry, select “Script User as”, “DROP to”, send to a new window, try that script?

Check if you do have sysAdmin rights? The following should return 1:

PRINT is_srvrolemember('sysadmin')

Might be tied in with something subtle and stealthy like replication, or its the database owner, or something equally obtuse and obscure

Run DBCC CHECKDB, see if any internals are out of whack?

The irritating thing is this is vaguely familiar, but I can’t remember where I came across it before. Please post if/when you figure it out!

If all else fails, nuke it from orbit (drop and recreate database from scratch). It’s the only way to be sure.