SQL Server database backup status notification I have scheduled a SQL Server database backup. But how to notify when backup has failed? There is option like e-mail notification. But that is not suitable for us because we does not checks mails every time.

Is there any another way to display notification on Windows if backup fails?

Backups and Restores should be your Nbr 1 alerts, a failure here can trigger a "CV modification Event". There are several ways to send alerts on a job failure, here you have a screen shot of a job Notifications window. You can use email, pager (do someone still uses this?), net send, write to app log and even delete the job when it finishes (for one time job execution for example).

Email is very common, you just need to notify when the job fails, not every time it succeeds or alike, to avoid unnecessary emails. You will need to enable and setup your Database Mail (if not already done), add an operator (who will receive these emails?), then check Email option and select that operator.

Same with the Alert window, at the end you will end up using an email to notify. This other answer explains this option really well.

But you comment that email is not an option. If you have some kind of monitoring tool for your servers, you can use the Write to Windows Application Event Log and then let that app warn you through whatever means they have configured.


barman + backup and restore + how to enable auto deletion from backup files I am using the barman tool to backup the postgresql DB

barman is working fine but I notice that backup files that created under
/var/lib/barman/main-db-server/base folder are not automatically being deleted after so many days.

I found the variable last_backup_maximum_age - but this only for reporting

 last_backup_maximum_age
 This option identifies a time frame that must contain the latest backup. If
 the latest backup is older than the time frame, barman check command will
 report an error to the user. If empty (default), latest backup is always
 considered valid. Syntax for this option is: “i (DAYS | WEEKS | MONTHS)”
 where i is a integer greater than zero, representing the number of days |
 weeks | months of the time frame. Global/Server.

I can use

barman delete <server_name> oldest

in order to delete every time the oldest file; however, I want to understand if I've missed something that can automatically delete the files once they reach a certain age; an auto-rotate or auto-delete functionality.

You should configure retention_policy.

Here's the syntax :

retention_policy = {REDUNDANCY value | RECOVERY WINDOW OF value {DAYS | WEEKS | MONTHS}}

You will find documentation here


Group by array overlapping I have a PostgreSQL table with id and clusters like this:

CREATE TABLE w (id bigint, clst int);
INSERT INTO w (id,clst)
VALUES
  (1,0),
  (1,4),
  (2,1),
  (2,2),
  (2,3),
  (3,2),
  (4,2),
  (5,4),
  (6,5);

If you aggregate clusters grouped by id, you can see that there are overlapping values in the cluster arrays:

select id, array_agg(clst) clst from w group by id order by id;
 id |  clst
----+---------
  1 | {0,4}
  2 | {1,2,3}
  3 | {2}
  4 | {2}
  5 | {4}
  6 | {5}

i.e. cluster 4 covers id 1 and 5, cluster 2 covers id 2, 3 and 4, whereas cluster 5 corresponds only to one id.

How can I now aggregate ids grouped by cluster arrays overlapping?
i.e. the expected result is:

 id      | clst
---------+-------
 {1,5}   | {0,4,4}
 {2,3,4} | {1,2,3,2,2}
 {6}     | {5}

I don't care much about the cluster column just need ids properly aggregated.

There is no restriction on number of possible overlappings. Number of clusters per id is not restricted either (it can be hundreds or even more). Clusters are assined to ids not sequenctially.

There are millions of rows in the table!!!

Using PostgreSQL 11.

I don't care much about the cluster column just need ids properly aggregated.

In that case we can make use of the uniq and sort functions in the intarray extension:

with recursive a as (
  select id, array_agg(distinct clst) clst from w group by id)
, t(id,pid,clst) as (
  select id,id,clst from a
  union all
  select t.id,a.id,t.clst|a.clst
  from t join a on a.id<>t.pid and t.clst&&a.clst and not t.clst@>a.clst)
, d as (
  select distinct on(id) id, clst from t order by id, cardinality(clst) desc)
select array_agg(id), clst from d group by clst;

array_agg | clst
:-------- | :------
{6}       | {5}
{2,3,4}   | {1,2,3}
{1,5}     | {0,4}

db<>fiddle here

Bear in mind that this is unlikely to perform well on millions of rows.


Replication : Delete from Master Database Is it possible to delete certain data from Master Database and
retain that data in the slave ?

For, example I want to delete data of before 5 years from master.
And I'm sure that data is not being used in any current transactions.

Will this cause any synchronization problems ?

Is it not recommended ?

I researched for it, but couldn't find any perfect solution !

set sql_log_bin=0;
<delete_query>;
set sql_log_bin=1;

That should take care with the delete on master and will not replicate to the slave.

I'd recommend against this though. I think it's better to transfer data older than 5 years to a separate table then drop the table from the master side and retain the one on the slave.


I need help optimizing a not that complicated query that returns unexpedcted performance problems I have a table that stores details about incoming and outgoing phone calls such as when a call was made, or when a call was received, the phone number of the caller, and the phone number of the receiver.
Before you tell me to separate the data in two tables, one for incoming calls and another for outgoing calls, I tried to do it but my thesis partners didn't agree so I had to go along with a single table, differentiating them with an "incoming" int column.

We are trying to match each outgoing call to an incoming call. The criteria for that is that the incoming call will have been created within 30 seconds of the outgoing call, and that their numbers match.

select
  *
from
  tesis.call oc --outgoing calls
join tesis.call ic --incoming calls
  on oc.sourceNumber = ic.targetNumber
  and ic.dateCreated > oc.dateCreated
  and ic.dateCreated <= (oc.dateCreated + interval 30 second)
where
  oc.incoming = 0
  and ic.incoming = 1
  and oc.neighborhood_id is not null

We have an index for "incoming", an index consisting of (sourceNumber, targetNumber), and for in dateCreated.
tesis.call has around 13000 rows
sourceNumber and targetNumber are varchar(20)
dateCreated is DateTime
incoming is int

For some reason, the query takes around 100 seconds to execute, tested in two computers with the DB's on localhost, running with an i5 processor, one of them on MySQL and the other on MariaDB.
I tried many index combinations and none of them work.

This is the execution plan with the provided indexes:

Thank you.

Right now the provided query with the specified indexes took: 0,780 sec. (+ 116,455 sec. network) to execute.
The network part looks very weird to me, since "select * from tesis.call" takes 0,000 sec. (+ 0,062 sec. network) to execute so it doesn't look like a connectivity issue.

EDIT: Here is the result of SHOW CREATE TABLE tesis.call:

CREATE TABLE `call` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `dateCreated` datetime(6) DEFAULT NULL,
  `incoming` int(11) DEFAULT NULL,
  `operatorName` varchar(20) DEFAULT NULL,
  `sourceNumber` varchar(20) DEFAULT NULL,
  `targetNumber` varchar(20) DEFAULT NULL,
  `batteryLevel` double DEFAULT NULL,
  `currentSignal` double DEFAULT NULL,
  `locationLat` double DEFAULT NULL,
  `locationLon` double DEFAULT NULL,
  `dispatchDate` datetime DEFAULT NULL,
  `insertionDate` datetime DEFAULT NULL,
  `matchId` int(10) unsigned DEFAULT NULL,
  `callTime` int(10) DEFAULT '0',
  `neighborhood_id` int(11) DEFAULT NULL,
  `Geom` point DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  UNIQUE KEY `matchId_UNIQUE` (`matchId`),
  KEY `Geom` (`Geom`(25)),
  KEY `incoming` (`incoming`) USING HASH,
  KEY `dateCreated` (`dateCreated`),
  KEY `sourceNumber_targetNumber` (`sourceNumber`,`targetNumber`),
  KEY `dateCreated_incoming_targetNumber` (`incoming`,`targetNumber`,`dateCreated`),
  KEY `dateCreated_incoming_sourceNumber` (`incoming`,`sourceNumber`,`dateCreated`)
) ENGINE=InnoDB AUTO_INCREMENT=15154 DEFAULT CHARSET=utf8

EDIT 2: I uploaded a script to recreate the table in case anyone wants to do some tests.
https://www.mediafire.com/?ph0gk06dxntohmg

My bet is on creating the following indexes:

(incoming, targetNumber, dateCreated)
(sourceNumber, incoming, dateCreated)

But you have to rewrite your query to do:

select *
from tesis.call oc --outgoing calls
join tesis.call ic on
    oc.sourceNumber = ic.targetNumber
    and ic.dateCreated > oc.dateCreated
    and (ic.dateCreated - interval 30 second) <= oc.dateCreated --incoming calls
where oc.incoming = 0
    and ic.incoming = 1
    and oc.neighborhood_id is not null

This seems better than doing the JOIN in this direction oc -> ic, where the indexes would be:
(incoming)
(targetNumber, dateCreated)

Please note that this is long shot, as I do not know the cardinality of the conditions, I can only imagine them. Please test it and show the results of EXPLAIN to check it.


Is it ever good practice to use a distinct database account for each user of an application? The applications I'm used to are server based and use one database account for many users, with the application code controlling what the user can do, or single-user.

Are there any successful complex business applications where each person needs their own database account, and the database server is relied on to enforce policy rules about what each user should be allowed and not allowed to do?

I'm thinking about applications where multiple people contribute to information in one database, and can access information stored by others - e.g. colleagues in an organisation who all need to access customer records.

Also is there a name for this type of set up?

If you need really tight control enforced at the data level.  For example extensive auditing.  Auditing is not much good if several users share the same account.   If you have some users that have a need to access the data database directly.

If security is that tight you typically don't even expose the database directly.  You have a service and the client must get data from the service.

In a web application the database (e.g. port 1433) is typically not exposed directly so you have a level of security.  Even if the web application accesses the database directly the users still does not have direct access to the database.

If the login and password is in the client application then it can be hacked.  If a domain you can use integrated security.

At the database you can have pretty fine controls. But row level control is a bit of work. A database is not a good tool for business rules. Business rules and detailed security are typically enforced at the application level.

You can have a mixed mode where there are some stored procedures used by admins and you want to track which admin.  And you may give read only access to users that generate report directly.


Fulltext search not working properly I have pers table and applied combine fulltext index on (prenom, nom) fields. Below are data in that table.

persID prenom      nom (pers table)
--------------------------------
 116    te          te
 117    te test     test te

Now, I tried to fetch above record using MATCH and AGAINST. Below is my query.

SELECT `Pers`.`persID`, Pers.prenom, Pers.nom
FROM `bdrplus`.`pers` AS `Pers`
LEFT JOIN `bdrplus`.`pers_detail` AS `PersDetail`
     ON ( `PersDetail`.`persID` = `Pers`.`persID` )
WHERE `Pers`.`etat` =1
AND `Pers`.`persID` !=55
AND MATCH(`Pers`.`prenom`, `Pers`.`nom`) AGAINST('te*' IN BOOLEAN MODE)

Result
------------------------
persID   prenom      nom
117     te test     test te

Now I tried using mysql LIKE(%..%) operator. Below is the query which I tried.

SELECT `Pers`.`persID`
FROM `bdrplus`.`pers` AS `Pers`
LEFT JOIN `bdrplus`.`pers_detail` AS `PersDetail`
     ON ( `PersDetail`.`persID` = `Pers`.`persID` )
WHERE `Pers`.`etat` =1
AND `Pers`.`persID` !=55
AND (Pers.prenom LIKE 'te%' OR Pers.nom LIKE 'te%')

Result
-----------------------
 persID prenom      nom (pers table)
 116    te          te
 117    te test     test te

I don't understand why I am not getting both records using MATCH AGAINST operator in fulltext index query?

By default MySQL uses ft_min_word_len=3 setting which tell MySQL full-text engine to ignore all the words LESS than 3 letters. You need to decrease this variable. It's not dynamic so you may need to restart MySQL. However setting it to less than 3 may affect performance.


Help with Query Tuning EDIT - drachenstern: see this question for more information on this problem and on the source of his query below:

  Group data by non-unique keys by distinct time range

Can someone please advise some tuning options for the following query? When running for one project it is fast but for all the records it takes hours to finish running. NUM_ROWS in PA table: 2,101,528

I want to group data by non-unique keys by a distinct time range

SELECT project_nbr, status, MIN(aud_timestamp) start_dt, end_dt
FROM (
    SELECT a.project_nbr
         , status
         , aud_timestamp
         , ( SELECT MAX(p.aud_timestamp)
             FROM pa p
             WHERE p.project_nbr = a.project_nbr
               AND p.status = a.status
               AND p.aud_timestamp >= a.aud_timestamp
               AND NOT EXISTS (
                            SELECT NULL
                            FROM pa q
                            WHERE q.project_nbr = p.project_nbr
                              AND q.status <> p.status
                              AND q.aud_timestamp < p.aud_timestamp
                              AND q.aud_timestamp > a.aud_timestamp
                               )
            ) end_dt
     FROM pa a
     )
GROUP BY project_nbr, status, end_dt

This is the offending code causing your query to take hours to return results:

           and not exists (
                        select null
                        from pa q
                        where q.PROJect_NBR = p.project_nbr
                          and q.status <> p.status
                          and q.aud_timestamp < p.aud_timestamp
                          and q.aud_timestamp > a.aud_timestamp
                           )

You should really convert that to a join instead, because as it stands you are generating a new lookup for each record in the 2million source rows.


Close MongoDB dead connections I have a script that connects to my local mongos and do some processing. However, during the process, I encountered many cursor timeout exceptions, which (I suspected) left many dead connections on my server.

When I run mongostat on my host, I see 450+ connections:

insert  query update delete getmore command  vsize    res faults  netIn netOut  conn repl       time
     0      0      0      0       0       1   977m   195m      0    62b   726b   458  RTR   02:11:07
     0      0      0      0       0       1   977m   195m      0    62b   726b   458  RTR   02:11:08
     0      0      0      0       0       1   977m   195m      0    62b   726b   458  RTR   02:11:09
     0      0      0      0       0       1   977m   195m      0    62b   726b   458  RTR   02:11:10

How can I make sure that these are idle resources that I should free up? If they are, how can I free them?

This should be taken care programmatically. Programmers have to close the connection whenever they are done with dB access else memory leaks could happen.
I'm not sure how these processes can be killed as an admin.


adjecency list model vs nested sets I've stumbled upon an article describing nested sets model for storing hierarchical data and was quite intrigued by the supposed performance boost.

after implementing this in our database, i've found that the adjecency list model performs far better. though the article I've read and a couple of SA posts (one, two) stated that any tree traversal is supposed to benefit, my tests demonstrate the opposite.

Am I missing something?

there are roughly 1.1 Million entries in the following table:

SQL> show table tax_nodes;
TAX_ID                          INTEGER Not Null
PARENT_TAX_ID                   INTEGER Nullable
RANK                            VARCHAR(50) Nullable
DIVISION_ID                     INTEGER Nullable
TREE_RIGHT                      INTEGER Nullable
TREE_LEFT                       INTEGER Nullable
CONSTRAINT INTEG_202:
  Primary key (TAX_ID)

Adjecency List Query to fetch parents

with recursive lineage as
(select tax_id, parent_tax_id
 from tax_nodes
 where tax_id = 365612
 union ALL
 select tax_id, parent_tax_id
 from tax_nodes t
 join lineage l on l.parent_tax_id = t.tax_id
)
select l.tax_id
from lineage l

Nested Sets Query to fetch parents

select
  p2.tax_id from tax_nodes as p1,
  tax_nodes as p2
where
    p1.tree_left between p2.tree_left and p2.tree_right
    and p1.tax_id = 365612

Result

365612 336487 10260 10241 10240 35237 10239 1  //1 is the root

Why is the Adjecency List performing better though most resources suggest otherwise?

I'm running Firebird 2.5 superserver by the way.

UPDATE

On @MarkRotteveel's hint, I took a look at the execution plans and I suspect the NATURAL being the culprit here. Question now is, how do I get rid of it?

adjecency list execution plan

PLAN (LINEAGE TAX_NODES INDEX (RDB$PRIMARY108))
PLAN (LINEAGE T INDEX (RDB$PRIMARY108))

nested sets execution plan

PLAN JOIN (P1 INDEX (RDB$PRIMARY108), P2 NATURAL)

The execution plan of your adjacency list example shows that it is able to use the primary key index (RDB$PRIMARY108) for both parts of the UNION. This is very efficient, mainly because you start with a single value, and then recursively look up its descendents (which is probably just a small set of the entire set of records).

For the nested set execution plan it is not able to use an index, because it has to scan all records for the values of p2.tree_left and p2.tree_right. It can only use the primary key index for looking up p1.tax_id = 365612.

You might be able to improve execution if you add a descending index for tax_nodes.tree_left and an ascending index for tax_nodes.tree_right. You may need to swap ascending/descending because I usually do it wrong initially, even if I account for usually doing it wrong. However you may also need to change the query condition so the optimizer picks the indexes for tree_left and tree_right:

p2.tree_left <= p1.tree_left AND p2.tree_right >= p1.tree_left


Log file shrink attempts are futile I have got a question relating to SQL server log files in a production environment,  however what I am trying to do is first run some testing on a smaller Test Database (E10Test) before I move on to the production database. However I am having some issues on shrinking the Log file.

Here are the Database properties of E10Test

Logical Name | File Type | Filegroup | Initial Size (MB) | Autogrowth / Maxsize

E10Test | Rows Data | PRIMARY | 7337 | By 100 MB, Limited to 10248 MB

E10Test_log | Log | Not Applicable | 256 | By 20 MB, Limited to 2048 MB

When running DBCC sqlperf ('logspace')
Here is the output

E10Test | 255.875 | 0.1463651 | 0

Log_reuse_wait = 0
Log_reuse_wait_Desc = NOTHING

Looking at the information above the log is not even 1% used. In theory I should be able to shrink the log file to a smaller size right?

Db is on Full recovery mode and I have done a full backup of Db and Log file backup.

I have tried the following without much luck:

Changing the size from the Db properties (Outcome is the same as before)
Using both script and GUI to shrink the Log file (Outcome is the same as before)
Set Db to simple recovery and repeat the above (Outcome is the same as before)

No matter what I do the log is still on 256Mb and it does not go any lower.
Is this something caused by Microsoft or is there something I have missed out on?

The documentation to dbcc shrinkfile says:

  Because a log file can only be shrunk to a virtual log file boundary, shrinking a log file to a size smaller than the size of a virtual log file might not be possible, even if it is not being used. The size of the virtual log file is chosen dynamically by the Database Engine when log files are created or extended.

So it isn't possible to shrink the logfile to an arbitrary small size. To check your virtual log files, you can use

  DBCC LOGINFO

There you can see the virtual log files and their size. Probably you hit the virtual log file size.


Distribute data into x groups of at-most n records each I have a set of Z entries(e.g. 100 records), which I want to insert into Header and Detail tables.

I want to divide this group into X groups, with each group containing at-most N entries. The last group may contain <= N entries. Here X is variable and N is static. BatchID is the IDENTITY key of the header table.

Simple Approach

a. dump all the data in Temp table
b. Find number of Loops : CEILING(COUNT/N)
c. For every loop insert 1 entry in Header, find IDENTITY KEY and use that as Batch ID and insert N records in Detail Table with that Batch ID like:

UPDATE TOP(N) #Temp
SET IsPickedUp = 1
OUTPUT @BatchID,deleted., deleted....
INTO Detail(BatchID, ...)
WHERE IsPickedUp = 0

Optimized Approach

a. Find number of batches to be created of N records, where last Batch Count will be <= N
b. Insert all records from temp table to Detail table with correct BatchID

I need help for the second approach.

Here is my existing Code that works fine but take 40 min in production. One crucial requirement is that either the batches Are created or it is not created, we don't want incomplete population (e.g. transaction in while loop)

BEGIN TRY
BEGIN TRAN
    CREATE TABLE #TotalCID (ConsumerID VARCHAR(16),IsPickedUp BIT NOT NULL CONSTRAINT DF_IsPickedUp DEFAULT (0))
    CREATE CLUSTERED INDEX  IX_ConsumerID  ON #TotalCID(IsPickedUp,ConsumerID)

    INSERT INTO #TotalCID (ConsumerID)
    SELECT ConsumerID FROM ConsumerTable WHERE SeedingStatus IN (0,2) -- 2Cr Records

    DECLARE @TotalCount DECIMAL(11,2) = 0
    DECLARE @BatchCount DECIMAL(9,2) = 0
    DECLARE @LoopCnt INT = 0
    DECLARE @BatchSize INT = 25000
    DECLARE @batchId INT = 0

    SELECT @TotalCount=COUNT(ConsumerID) FROM #TotalCID

    SET @LoopCnt = CEILING (@TotalCount/@BatchSize)

    WHILE (@LoopCnt > 0)
    BEGIN

        INSERT INTO ETLHeader(Batch_Date,Total_Rec_count)
        VALUES(GETDATE(),0)

        SET @batchId = SCOPE_IDENTITY() -- BatchID in ETLHeader is PK

        UPDATE TOP (CONVERT(INT,@BatchSize)) #TotalCID
        SET IsPickedUp=1
        OUTPUT @batchId,deleted.ConsumerID
        INTO ETLDetail(Batch_ID,ConsumerID)
        WHERE IsPickedUp = 0

        SET @BatchCount = @@rowcount

        UPDATE ETLHeader
        SET  Total_Rec_count = @BatchCount
        WHERE Batch_Id = @batchId

        SET @LoopCnt=@LoopCnt-1
    END

        UPDATE ConsumerTable
        SET SeedingStatus = 1
        WHERE ConsumerID IN (SELECT ConsumerID FROM #TotalCID Where IsPickedUp= 1)
        AND SeedingStatus IN (0,2)

IF OBJECT_ID('tempdb..#TotalCID') IS NOT NULL
    DROP TABLE #TotalCID
COMMIT TRAN
END TRY
BEGIN CATCH
-- ERROR and Open TRAN Handling
END CATCH

From the example code it seems that you want to transfer all outstanding rows, or none. This can be done in a couple of statements, though there are good reasons not to. More on that later.

If there's a foreign key in the detail table referencing the header table that must be honoured or worked around. Either the header table must be populated first, then the detail table, or the foreign key must be disabled, then re-enabled once the data is in place. Either way some data will be read twice.

I'll not use the temporary table (#TotalCID) either, saving the read and write this consumes. Instead I'll create a view on the source table (ConsumerTable). Even though there are a lot more rows in the base table compared to the temporary table (twenty million versus one hundred thousand) you have to read the base table at some point to find the rows to transfer. I believe a filtered index will be useful here. If there are concurrent writes to the base table they can be avoided by using a "high water mark" if there's an identity column or a suitable isolation level. It's OK to retain #TotalCID if you're more comfortable with that approach.

First I will show all the code, then I'll explain my thinking.

drop table if exists ConsumerTable;
drop table if exists ETLDetail;
drop table if exists ETLHeader;
go

create table ConsumerTable
(
    ConsumerID      varchar(16) primary key,    -- Note A
    SeedingStatus   int         default (0)     -- Note B
);

go
-- Note C
create index ToTransfer on ConsumerTable (ConsumerID)
where SeedingStatus in (0,2);
go

insert ConsumerTable(ConsumerID)
values ('a'), ('b'), ('c'), ('d'), ('e'),
       ('f'), ('g'), ('h'), ('i'), ('j');
go

create table.ETLHeader
(
    HeaderId        int     primary key,
    Batch_Date      date    not null,
    Total_Rec_count int     not null
);

create table ETLDetail
(
    Batch_ID    int not null,
    ConsumerID  int not null
);

go
create or alter view ConsumersToTransfer as
with DataRows as
(
    select
        ConsumerID,
        RowId       = ROW_NUMBER() over (order by ConsumerID)   -- Note D, E
    from ConsumerTable
    where SeedingStatus in (0, 2)                               -- Note F
)
select
    ConsumerID,
    RowId,
    BatchId = (RowId - 1) / (select BatchSize = 4)              -- Note G
from DataRows;
go

Notes

A For testing I'll make this the PK. It doesn't have to be for this method to work.

B The default is a convenience for my test data. It is not required for this solution.

C I think this filtered index will be useful, but you must have code to find these rows efficiently already. Continue to use that.

D To pre-calculate BatchID I will use integer division. From the docs

  If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.

For that I'll need a unique, gap-less sequence of integers, one for each source row. I'll use ROW_NUMBER() to build this. If your table has an identity column which provides the same conditions it can be used instead. Note that identities can leave gaps on occasion, or rows may have been deleted. If so the batch sizes will not be uniform.

E The OVER() clause is requried. We don't care which rows are together in a batch, however. So use the clustering index's leading column to avoid a sort. If you have a need to keep some rows near others you can change this clause, though there'll likely be a performance hit.

F The predicate matches that of the filtered index defined above.

G I'll use a batch size of 4 for testing. Change this to your production value (25,000) for your tests. I subtract 1 from RowId because ROW_NUMBER() is 1-based and I want the Nth row to end a batch rather than start a new batch.

Here is the code to transfer rows:

begin transaction;

    declare @NextBatchStart int
              = 1 + (select ISNULL(MAX(HeaderId), 0) from ETLHeader);       -- Note H

    insert ETLHeader                    -- Note I
    (
        HeaderId,
        Batch_Date,
        Total_Rec_count
    )
    select
        BatchId + @NextBatchStart,
        Batch_Date = GETDATE(),
        COUNT(*)
    from ConsumersToTransfer
    group by BatchId;

    insert ETLDetail                    -- Note J
    (
        Batch_ID,
        ConsumerID
    )
    select
        BatchId + @NextBatchStart,
        ConsumerId
    from ConsumersToTransfer;

    update ct
    set SeedingStatus = 1
    from ConsumerTable as ct
    inner join ETLDetail as d                   -- Note K
        on d.ConsumerId = ct.ConsumerId
        and d.Batch_ID >= @NextBatchStart       -- Note L
    where ct.SeedingStatus in (0, 2);           -- Note M

commit;

Notes

H This plays the role of your SCOPE_IDENTITY() ensuring new header rows follow on from existing rows.

I Each INSERT will read all source rows. Since this is will be in clustered key sequence (because of the OVER() clause) it should be fast.

J If there are concurrent writes to ConsumerTable these two statements could return different results and, hence, different batches. Specifically Total_Rec_count will be off for the last (non-full) batch.

K An inner join is logically equivalent to your .. IN (select ..).

L Only need to consider batches created in the current run.

M Predicate matches the filtered index.

Wrap this in the error-catching and rollback logic to handle your desired all-or-nothing outcome.

I think part of the reason your current approach takes so long is because the system does not use IX_ConsumerID because the leading column (IsPickedUp) is not selective enough. I touch on this for another question here.

I understand you want all-or-nothing outcomes. It is obvious your hardware can handle this because you have written code that runs. However it takes a toll on the system. All those rows written have to be held in the transaction logs. The logs must grow to accommodate the rows and cannot be truncated until the transaction completes. The system must hold locks, preventing other work from continuing.

A better way may be to process each batch in a transaction. Fewer resources will be needed for each batch compared to the whole. Locks will be released sooner. Restart logic will ensure no row is processed twice or missed.


Why does Oracle consider a larger number to be smaller? For some reason when I try to do greater than or less than, it says that 100 is less than 2. I think it's only reading the first digit when it determines the greater/less value. Not sure why or if its suppose to be like this? Trying to create a Short Ship report for my database course.

Your quantity columns are being stored as strings, rather than numbers, causing (for example) 10 to be "less" than 9 - a string comparison is obviously based on the characters in the string rather than whole numbers, hence the result you are seeing.

Oracle is not converting the values to numbers when you do a comparison ('<' and '>' in your case), but it is implicitly converting them to numbers when you perform mathematical operations on them, hence the - works as expected.

If you cast the VARCHAR columns to a NUMBER when doing the comparison your query will work correctly:

select *
from orders
where TO_NUMBER(qty)<TO_NUMBER(qtyshipped);

Having said that, you're better off recreating your table with the correct datatypes.

Example DB Fiddle to demonstrate all of this is here.


How do uninitialized variables differ from NULL in Mysql? Related, please read first for context: MySQL stored procedure: loop through table, delete rows. Logic problem: won't exit loop because of LIMIT option in query

I think this is really strange. I don't understand why the solution in that problem works the way it does, can someone explain the solution please?

This is stated in the mysql documentation:

  If you refer to a variable that has not been initialized, it has a value of NULL and a type of string.

why does it make a difference if you set a variable to NULL before you use it, if that is the default for uninitialized variables anyway?

(I am aware, that it is very dirty programming, not to initialize variables, but still I want to understand the behaviour.)

There are two ways to set a variable from a query, and they behave differently, especially when no record is matched.

You can SELECT ... INTO the variable...

select first_name into @z from actor where actor_id = 999999;

Or you can SET it to the value returned by a scalar subquery.

set @z = (select first_name from actor where actor_id = 999999);

Illustrating the difference with the sample database...

mysql> use sakila;
Database changed

Our variable starts out NULL.

mysql> select @z;
+------+
| @z   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> SELECT first_name INTO @z FROM actor WHERE actor_id = 1;
Query OK, 1 row affected (0.00 sec)

We matched a row and our variable is set.

mysql> select @z;
+----------+
| @z       |
+----------+
| PENELOPE |
+----------+
1 row in set (0.00 sec)

We try to select a record that doesn't exist:

mysql> select first_name into @z from actor where actor_id = 999999;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

So what's in our variable now?

mysql> select @z;
+----------+
| @z       |
+----------+
| PENELOPE |
+----------+
1 row in set (0.00 sec)

I think this explains the confusion.  When no row is found, the variable is unchanged from its previous value... so it's not that you have to "set it to null before you use it," it's that you have to reset it to null before you use it again, if you are doing something like SELECT ... INTO that won't reset the value if nothing is found.

On the other hand, if we use the other construct, the variable is reset when no record is matched.

mysql> select @z;
+----------+
| @z       |
+----------+
| PENELOPE |
+----------+
1 row in set (0.00 sec)

mysql> set @z = (select first_name from actor where actor_id = 999999);
Query OK, 0 rows affected (0.00 sec)

mysql> select @z;
+------+
| @z   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql>


SQL Server 2008 R2 - Change distributor schedule We have configured SQL Server 2008 R2 replication. Distributor is configured in continuous mode, publisher is scheduler is set to execute after every six hours.

Now we want to change distributor schedule from continuous to schedule frequency without re-creating new replication.

We have tried below stored procedure, but it throws an error. Please help to distributor settings.

EXEC sp_changepublication
@publication = 'your_publication_name',
@property = 'allow_anonymous',
@value = 'false'
GO

EXEC sp_changepublication
@publication = 'your_publication_name',
@property = 'immediate_sync',
@value = 'false'

Right click distributor job and change its schedule.


Performing efficient searches over two columns Disclaimer: I am extremely inexperienced with databases, so forgive me if this is an ignorant question.

I have a table which contains several columns, but the ones that are important are the columns containing timestamps and MAC addresses. I need to be able to quickly determine if a certain MAC address appears within a certain range of timestamps. For example lets say I need to see if MAC address 01:02:03:04:05:06 appears in any record on last Monday. Can this be done in sublinear time?

My intuition says that we can construct the timestamp range of interest in logarithmic time, but would we then need to linearly search over this subset of the data to determine if the desired MAC address appears, or can we search for the desired MAC address in logarithmic time?

Does this have anything to do with creating indices? Thanks for any help.

An index in 2 columns in the order (MACAddress, TimeStamp) will benefit any reasonable query construct for both

for a specific MAC address
for a MAC address within a range

Why?

The first query uses "equality" (= MacAddress)
The second uses "equality" then "inequality" (= MacAddress AND TimeStamp BETWEEN X and Y)

An RDBMS should traverse the index to find the MACAddress quickly, then looks with the subset for the time range.

It should be sublinear performance

Some reading to explain more (SQL Server specific)

https://www.toadworld.com/platforms/sql-server/w/wiki/9635.index-selectivity-and-column-order
https://stackoverflow.com/questions/797830/how-to-order-fields-on-index-creation-sql-server-2005


postgresql recovery after reboot I installed Postegresql and added a database which I was using as part of a local server. However, I restarted the computer and my Postgresql database seems to be gone. The Postegresql documentation says that the data should be placed in /usr/local/pgsql/data however I did not do this and this directory is empty on my system. Also, I  cannot find a postgresql config file so I am not sure where Postgreql may have been putting my databases.

Has this happened to anyone before? If so, is there a way to recover my databases? Fortunately these databases are not so old that there is irrevocable memory loss but i would prefer to recover my data if possible.

If I can't find my data I will need to restart /recreate my data although I would prefer not to.  Any suggestions would be greatly appreciated.

thanks,
zach cp

To locate your data try the following command:

sudo find / -name postgresql.conf

The problem probably is that your server isn't automatically started. You can manually start it with a command like:

pg_ctl start -D <your data dir>

If you need help starting it automatically please specify which os/distribution you are running and how you installed postgresql (source/yum/rpm/...)


List ranges and the total count based on condition Disclaimer: I have already posted this question yesterday here. As there were no response there re-posting it in dba forum now.

Table Schema:

CREATE TABLE [dbo].[TblMaster](
    [SID] [int] IDENTITY(1,1) NOT NULL Primary Key,
    [VID] [int] NOT NULL,
    [CreatedDate] [datetime] default (getdate()) NOT NULL,
    [CharToAdd] [varchar](10) NULL,
    [Start] [int]  NOT NULL,
    [End] [int] NOT NULL
)
GO

CREATE TABLE [dbo].[TblDetails](
    [DetailsID] [int] IDENTITY(1,1) NOT NULL Primary Key,
    [SID] [int] NOT NULL,
    [Sno] [int] NOT NULL,
    [ConcatenatedText] [varchar](20) NOT NULL,
    [isIssued] [bit] default (0) NOT NULL,
    [isUsed] [bit] default (0) NOT NULL
)
GO

Sample data:

Insert into dbo.TblMaster Values (1,default, 'CA', 1, 5)
Insert into dbo.TblMaster Values (1,default, 'PA', 1, 5)
GO
Insert into dbo.TblDetails values(1, 1, 'CA1', 0,0)
Insert into dbo.TblDetails values(1, 2, 'CA2', 0,0)
Insert into dbo.TblDetails values(1, 3, 'CA3', 0,0)
Insert into dbo.TblDetails values(1, 4, 'CA4', 1,0)
Insert into dbo.TblDetails values(1, 5, 'CA5', 0,0)
Insert into dbo.TblDetails values(2, 1, 'PA1', 0,0)
Insert into dbo.TblDetails values(2, 2, 'PA2', 0,0)
Insert into dbo.TblDetails values(2, 3, 'PA3', 1,0)
Insert into dbo.TblDetails values(2, 4, 'PA4', 0,0)
Insert into dbo.TblDetails values(2, 5, 'PA5', 0,0)
Insert into dbo.TblDetails values(3, 1, '1', 0,0)
Insert into dbo.TblDetails values(3, 2, '2', 1,0)
Insert into dbo.TblDetails values(3, 3, '3', 1,0)
Insert into dbo.TblDetails values(3, 4, '4', 0,0)
Insert into dbo.TblDetails values(3, 5, '5', 0,0)
GO

Expected Output:

Query I have build till now (result doesn't match the expected output)

Declare @VID INT  = 1

    ;WITH Tmp as
    (
      SELECT
            TM.CharToAdd as Prefix,
            sno,
            sno - ROW_NUMBER() OVER(ORDER BY sno) as grp
      FROM dbo.TblDetails TD
      LEFT JOIN dbo.TblMaster TM on TM.[SID] = TD.[SID]
      WHERE isIssued = 0 and isUsed = 0
            AND  TM.VID = @VID
    )
    SELECT  Prefix,
            MIN(sno) as RangeStart,
            MAX(sno) as RangeEnd,
            COUNT(*) as [Count]
    FROM Tmp
    GROUP BY grp, Prefix

In the TblDetails table want to find the range of available values and its total counts from all records whose bit columns are 0. If bit column is 1 then it means it is already used so I am trying to skip it and list rest as available records. Doubtful whether am I explaining the problem statement well so have provided the sample data and expected output for better understanding. I did try doing some recursive function but the result isn't matching the expected output. So looking for help to resolve this.

You were close with what you had. Just needed a couple of changes.

Declare @VID INT  = 1

    ;WITH Tmp as
    (
      SELECT
            TM.CharToAdd as Prefix,
            sno,
            sno - ROW_NUMBER() OVER(PARTITION BY TM.CharToAdd ORDER BY sno) as grp
      FROM dbo.TblDetails TD
      LEFT JOIN dbo.TblMaster TM on TM.[SID] = TD.[SID]
      WHERE isIssued = 0 and isUsed = 0
            AND  (TM.VID = @VID OR TM.VID IS NULL)
    )
    SELECT  Prefix,
            MIN(sno) as RangeStart,
            MAX(sno) as RangeEnd,
            COUNT(*) as [Count]
    FROM Tmp
    GROUP BY grp, Prefix

You need to partition on TM.CharToAdd to make the counter restart for each prefix value and you need to change AND  TM.VID = @VID to AND  (TM.VID = @VID OR TM.VID IS NULL) if you want the rows from TblDetails that does not have a match on SID in TblMaster.


How to shard 14 gb of data for archival database? We have 14 GB worth of CSV's containing product information, with each CSV being all the records for a US state.  We now want to store this in a database.  There would be ~130 million records.

The data only needs to be written once.  No new data will be added to this database.  It will be read from ~10 times per hour.  Almost all of the queries will use one indexed field.  We're looking for a response time under 5 seconds.

Would MySQL be appropriate for this, and if so, what is the minimum amount of RAM you would think the server needs?  Should it be sharded by state?  If not MySQL, what other options do we have?

Any full featured database will be able to handle this with ease. My preference would be for Postgres, however SQL server, Oracle and MySQL are all valid options.

When designing the tables you need to consider how you will read the data so that you can optimize read performance. There are a couple of things to look at.

1> Appropriate indexes for the queries you are going to run.
2> The use of Partitioning (read the appropriate manuals). You would use partitioning in this case perhaps by having a partition per state assuming the queries that need to perform affect only one state's data at a time.
3> Disk and network.  These can often be the cause of slow reads so make sure that they are as fast as you can afford.  Consider if you need to have a RAID type replication or can live without this.  i.e.: do you need 100% up-time [RAID] or can you afford to have some downtime [single disk and backup] if there is a disk failure.
4> If you have a choice of storage methods with the chosen DB, choose the most appropriate t the task at hand. Again refer to the DB's documentation for advice.
5> Maintenance plan. Ensure you regularly rebuild fragmented indexes and tables, this will help prevent slowing performance over time.

When it comes to RAM, get as much as you can fit into the server you have.


SQL Server rollback schema update Say I have a hand-written script which performs a bunch of schema changes, eg: (plucked from air):

Adds 2 columns to an existing table
Creates a new stored procedure
Alters an existing stored procedure

And, after running this, we discover that we need to revert back to before this entire script was run.

Is this possible?

In the meantime, data will have been flowing in.  We can't throw away the new data (ie: we can't restore a backup of the whole database), but we don't care about any data from the 2 new columns.

I'm looking for a better solution than writing up an explicit "undo" script (with, for example, DROP COLUMN statements and old procedure definitions) every time we make schema changes.

There are many good schema comparison tools out there. Redgate Schema Compare and SQL Server Data Tools for Visual Studio are two that come to mind (I'm not affiliated with either).

In a development environment, create two databases which match the the current "live" schema. Deploy your changes to one database only. Use the tool to compare the updated DB to the current DB. Call the generated script "Deploy.sql" or something similar. Now use the tool to compare current to updated -- NB the order has switched. Call the generated script "Rollback.sql".

An important aspect of both roll-forward and roll-back scripts is that they should be idempotent i.e. running the script once or multiple times against the same DB should produce the same output. Generally this requires a lot of "if exists .." statements. The tools can generate these for you.

If space is tight you can use the actual production database as the "current" DB. I prefer not to because of the (small) extra load and the (small) risk of mistakes.


Postgres: Vacuum taking too long I have a database currently sitting on ~6B live tuples, ~4B dead tuples.

I've ran the VACUUM command for 3 days now, and then suddenly my internet died. Meaning i lost all progress. How can i speed up the VACUUM speed? It's currently running on AWS RDS.

Is there a way to run command in background, e.g. disconnect/turn off PC?

Btw running on 9.3

You may want to turn on AUTO VACUUM? AWS Docs suggests that as a best practice

Set the autovacuum and once it is done, you can turn it off if you wish.

If you want to really run it in a terminal and then disconnect, refer to this answer:
How to execute a query from psql without waiting for the result?


Create New Database user to login to SQL Server Management Studio Please see the T-SQL below:

create database db;
go

use [db]

create login [standard] with password ='dkdkdkqajHGgbh56'
CREATE USER [standard] FROM LOGIN [standard];

exec sp_addrolemember 'db_owner', 'standard'

After running this I try to login to SQL Server Management Studio as standard and I am prompted with this error:

  login failed for user: standard

What am I doing wrong?

PS: I cannot even login as sa. I have enabled 'SQL Server Windows Authentication mode'. I am a developer (not a DBA) trying to setup a local instance of SQL Server.

I had to enable: 'SQL Server and Windows Authentication Mode', then restart the SQL Server 2012 service.


SSMS - How to Enable Word Wrap for Messages Pane Sometimes I need to take screen shots of error messages in SQL Server. How can I turn on word wrap in the messages pane so I can capture all the text in the screen shot?

Unfortunately, there isn't an option to enable word wrap in the messages pane.  You can enable for the text editor window (through Tools -> Options -> Text Editors), but that doesn't really help in your situation.

In case you didn't know, though, you can click into that messages pane and edit the text.  So you could insert the line breaks yourself.  Not ideal, but good enough to get the desired screenshot.

Before:

After manual editing:

I checked Azure Data Studio as well, it does not have a word wrap feature for output messages either.


Which isolation levels can cause a serialization failure? The quote below from Wikipedia (here) says that READ COMMITTED doesn't cause serialization failure! Can you please explain the reason? Which isolation levels cause serialization failure?

Under multiversion concurrency control, at the SERIALIZABLE isolation
level, both SELECT queries see a snapshot of the database taken at the
start of Transaction 1. Therefore, they return the same data. However,
if Transaction 2 then attempted to UPDATE that row as well, a
serialization failure would occur and Transaction 1 would be forced to
roll back.
At the READ COMMITTED isolation level, each query sees a snapshot of
the database taken at the start of each query. Therefore, they each
see different data for the updated row. No serialization failure is
possible in this mode (because no promise of serializability is made),
and Transaction 1 will not have to be retried.

It's impossible to give a brief answer to this.
I suggest that you start reading here:

Think twice before dropping ACID and throw your CAP away
follow the links, particularly to jepsen.io

What developers don't know about transactions will hurt them!

Martin Kleppmann's take on isolation levels (quoted in the 2nd link - and his explanation)

Recommended further reading: Kleppmann's book (which I've purchased) is highly regarded! Of 1525 reviews, 94% are 4 (8%) or 5 (86%) star rated - that's the best I've ever seen!

If you can read and grasp all of that material (and the links within - the book is the cherry on the cake but not strictly required just to answer this question), you'll be well on the way to understanding transactions and isolation levels!
Edit:
In response to the OP's comments - by serialization failure, what I believe the author of the wiki (which is here - please quote references in future if possible) means is that in the first case, there will be a failure of T1 due to the isolation level being set to SERIALIZABLE - whereas, in the second case, a level of READ COMMITTED will mean that the T1 will not fail.
The important think to realise about SERIALIZABLE transactions is that (from here):

a transaction schedule is serializable if its outcome (e.g., the
resulting database state) is equal to the outcome of its transactions
executed serially, i.e. without overlapping in time.

So, T1 will fail because its entire view of the data can't be consistent over the entire time (i.e. for all of the queries) of the transaction due to the modification by T2.
I have found (and this confused me for a while until I teased it through) is that there is a BIG DIFFERENCE between a query AND a transaction - a query may or may not be a proper subset of a transaction. On most modern RDBMS's, if you don't explicitly specify a BEGIN TRANSACTION or an isolation level, the default is that:

a) a transaction is started for you automatically beneath the hood and,

b) that transaction is read-committed - which normally doesn't matter much since it's one query.

The distinction really becomes important when you start running multi-query transactions - that's when the wheat is sorted from the chaff and the developer has to understand what's going on in the server re. transaction isolations levels
There is, as with all things in IT and databases in particular there are balances, trade-offs and compromises. Why, you might ask, is SERIALIZABLE not the default and/or not always specified? The answer is simple: Performance - there's your trade-off!
I would urge you now to read the references I've provided - do some experiments with two CLI windows on your server of choice (or multiple servers if you really want to get into it!) running some of the code referenced!


Set up AlwaysOn in virtual environment: How to add nodes to domain? On a beefy server, I have a few Virtual Machines (VMs) running. On one of those, I have Domain Controller (DC) with a domain name (ex:SQL.AD.COM) running successfully. Now, I need to add the other VMs into this domain. How do I do that?

For AlwaysOn, all nodes must in a domain. So, for that to happen, all nodes need to be added to the domain created by the DC. When I go to My Computer >> Properties >> Computer Name >> Change Settings >> Computer Name (tab) >> Change >>

I can change the computer name, but could not add the VM to the domain. What is missing?

This is the error I get:

I have the following network properties for each VM.

Domain Controller has 1 NIC

First:

Name: AD
Type: Host-Only Adapter
Static IP Address (of VM): 200.201.202.1

What IP address settings do I need to configure for VM's to be able to join the domain? I have 3 nodes (VMs)

After @Phil comments, I removed the 200.201.xx.xx IP and added 192.168.1.x IP address pattern.

Thanks,
    _UB

Finally after some reading more articles on Windows and AD Networking and speaking with abunch of people, this is what helped.

Settings on DC machine:

IP:                  192.168.1.10
Subnetmask:          255.255.255.0
Gateway: (none)

Preferred DNS: 127.0.0.1
Alternate DNS: (none)

On Each VM:

IP:                  192.168.1.11  (/12/13.../15 - on each node)
Subnetmask:          255.255.255.0
Gateway: (none)

Preferred DNS: 192.168.1.10
Alternate DNS: (none)

Restart the Network Adapter. BOOM !!

Now I can ping the DC and add them to the domain.

Why does it work now (but not before):

For DC: I made the DNS as loopback IP as preferred DNS (rather than its own IP - 192.168.1.10). This way it knows that the machine itself is the DNS.

For VMs: Gave them valid static IPs, rather than dynamic IP. Set the DNS to the DC (this was set correctly even before).

Glad I learnt something new,
_UB


pg_stat_statements not found even with "shared_preload_libraries = 'pg_stat_statements' I have followed the instructions given below:

http://www.postgresql.org/docs/9.3/static/pgstatstatements.html

... to the effect of adding the following lines:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

In file postgresql.conf and then restarting the server but the table pg_stat_statements is still not visible:

$ cat /usr/share/postgresql/9.3/postgresql.conf | grep -A3 ^shared_preload_libraries
shared_preload_libraries = 'pg_stat_statements' # (change requires restart)
pg_stat_statements.max = 10000
pg_stat_statements.track = all

$ sudo /etc/init.d/postgresql restart
* Restarting PostgreSQL 9.3 database server          [ OK ]

$ psql -U postgres
psql (9.3.10)
Type "help" for help.

postgres=# select count(*) from pg_stat_activity;
count
-------
      1
(1 row)

postgres=# select count(*) from pg_stat_statements;
ERROR:  relation "pg_stat_statements" does not exist

update

After executing:

sudo apt-get install postgresql-contrib-9.3

And then doing:

$ psql -U postgres
psql (9.4.5, server 9.3.10)
Type "help" for help.

postgres=# create extension pg_stat_statements;
CREATE EXTENSION
postgres=# \dx
                                     List of installed extensions
         Name        | Version |   Schema   |                        Description
--------------------+---------+------------+-----------------------------------------------------------
 pg_stat_statements | 1.1     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
 (2 rows)

 postgres=# \quit
$ sudo /etc/init.d/postgresql restart
 * Restarting PostgreSQL 9.3 database server  [OK]

... I now get:

postgres=# select * from pg_stat_statements ;
ERROR:  pg_stat_statements must be loaded via shared_preload_libraries

system details

I am running on Ubuntu 14.04.03 LTS. PostgreSQL was installed with apt-get install.

log trace of PostgreSQL during a restart

While doing a sudo /etc/init.d/postgresql restart I get the following log trace:

$ tail -f /var/log/postgresql/postgresql-9.3-main.log
2015-12-21 11:11:31 EET [23790-2] LOG:  received fast shutdown request
2015-12-21 11:11:31 EET [23790-3] LOG:  aborting any active transactions
2015-12-21 11:11:31 EET [7231-1] esavo-user@RegTAP FATAL:  terminating connection due to administrator command
2015-12-21 11:11:31 EET [23903-5] postgres@postgres FATAL:  terminating connection due to administrator command
2015-12-21 11:11:31 EET [23822-7] esavo-user@RegTAP FATAL:  terminating connection due to administrator command
2015-12-21 11:11:31 EET [23795-2] LOG:  autovacuum launcher shutting down
2015-12-21 11:11:31 EET [23815-1] esavo-user@RegTAP FATAL:  terminating connection due to administrator command
2015-12-21 11:11:31 EET [23792-1] LOG:  shutting down
2015-12-21 11:11:31 EET [23792-2] LOG:  database system is shut down
2015-12-21 11:11:32 EET [16886-1] LOG:  database system was shut down  at 2015-12-21 11:11:31 EET
2015-12-21 11:11:32 EET [16886-2] LOG:  MultiXact member wraparound  protections are now enabled
2015-12-21 11:11:32 EET [16885-1] LOG:  database system is ready to accept connections
2015-12-21 11:11:32 EET [16890-1] LOG:  autovacuum launcher started
2015-12-21 11:11:33 EET [16892-1] [unknown]@[unknown] LOG:  incomplete startup packet

Recent versions of Postgres install conf files in different locations than they used to be in.  There is sometimes a template .conf file that exists (but is not actually used), and then also an active .conf file (in a completely different location).  I discovered this by scouring through the /etc/init.d files.  It's possible that the .conf file you edited is not actually the one that is being used by the database.

You can find the location of the conf file used by your database by connecting to the database and querying SHOW config_file

Here is a link to your follow up question (in case anyone in the future finds it useful)


Allow user to create databases on server and to see only the databases that are created by him, without creating login first I want to implement the following use case using C# application and MS SQL Server Express 2014. I have a centralized database server in which there are already created some database (which have the same tables). In my c# application I want to allow users to create databases (with same format but different names) and users can assign other users to have access to the newly created database.
Also I want to assure that at the first use of the c# app the user shall not be able to see any database from the server address. Only after he executes the "Create db" button , he can view only the database which was previous created by him. Same thing shall happen for the other users when they will receive access on a certain database.
Another requirement is that the database creator and the other users mapped on the recently created database to not be able to have access on the database server via SSMS.
So an example can be : suppose you have 3 databases (A,B,C)  in sql server xyz . Now user AA opens the c# app and in the list of available database he see nothing so it creates a new one db1. After that db1 is present in the list. Then he assign users AB, AC to db1. Now AB, AC can see also db1. AA, AB, AC shall not have access to db1 database via SSMS.
What I've tried until now:

I have configured a laptop as a remote MS SQL Express 2014.
Using SSMS I first create a login, then I deny view to any database and after I allow access only on certain database

.
USE [master]
GO

CREATE LOGIN [xx] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

-- Step 2:(deny view to any database)
USE master;
GO
DENY VIEW ANY DATABASE TO [xx];

 -- step 3 (then authorized the user for that specific database , you have to use the  master by doing use master as below)
USE master;
GO
ALTER AUTHORIZATION ON DATABASE::DB1 TO [xx];
GO

Now I don't know if it is possible to allow the creation of databases on server and to map users on database without creating a login first. Also I don't know how to restrict the access for SSMS usage.

Instead of making Logins for each user, you should use an Active Directory Group that each user is a part of (e.g. DOMAIN USERS), and assign permissions directly on that AD Group or on a Role you can create for that AD Group. Then you only need to DENY VIEW ANY DATABASE once.
Then when your user creates a database via your application is when you can run the CREATE LOGIN (if one doesn't exist) and ALTER AUTHORIZATION ON DATABASE scripts for that User and Database.
The benefit of doing it this way is all your Users will be unable to access any databases that aren't their own automatically, even via SSMS. You also can set the default database of the new Login to their first database they create too, and set appropriate permissions like giving them the db_owner role.


Trouble understanding locking logic While trying to create deadlock I've faced locking issue that I'm having trouble to understand:
I'm updating large table partitioned by timekey column:

I am trying to run two update statements:

BEGIN TRAN
    UPDATE [Transform].[AllCommunications_Arch]
        SET Callid = 10300503454
    WHERE Callid = 10348103154
    AND TimeKey = 20161205

and

BEGIN TRAN
    UPDATE [Transform].[AllCommunications_Arch]
        SET Callid = 1234576704
    WHERE Callid = 4321276791
        AND TimeKey = 20160720

Each one should update one row, each one runs on different partition, but for some reason one statement still blocks another. DB has default isolation lever(read committed).

Execution plan:

CREATE TABLE [Transform].[AllCommunications_Arch](
    [SessionId] [varchar](100) NULL,
    [TimeKey] [int] NULL,
    [Callid] [bigint] NULL,
    [MemberId] [int] NULL,
    [Duration] [int] NULL,
    [CalledAt] [datetime] NULL,
    [EndTime] [datetime] NULL....
) ON [Transform_Timekey_Daily_Arch_PS]([TimeKey])
WITH
(
DATA_COMPRESSION = PAGE
)

GO

CREATE CLUSTERED INDEX [CL_Transform_AllCommunications_Arch] ON [Transform].[AllCommunications_Arch]
(
    [TimeKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = PAGE) ON [Transform_Timekey_Daily_Arch_PS]([TimeKey])
GO

I've changed LOCK_ESCALATION from default table to auto and now they do not block each other, but still I don't understand why server escalates lock in this case.

I've changed LOCK_ESCALATION from default table to auto and now they
  do not block each other, but still I don't understand why server
  escalates lock in this case.

This is specifically called out in the SQL Server Books Online CREATE TABLE topic.  The note states:

  After you create a partitioned table, consider setting the
  LOCK_ESCALATION option for the table to AUTO. This can improve
  concurrency by enabling locks to escalate to partition (HoBT) level
  instead of the table.

The default table will escalate to the table level, which includes all partitions.

You could also improve both concurrency and performance of this query by adding Callid to the clustered index key and specify unique. This would touch only the single row needed by the query instead of scanning all rows in the partition with an exclusive lock.


Optimizing and maintaining test databases for a web development team Right now when we test, we mysqldump from the production database, load it into the test database server, and then use scripts to scrub sensitive data. As the database is getting bigger, this process is taking longer, there becomes a risk where as the database is still being scrubbed, developers can connect to the test database still and look at sensitive data. Is there a way to scrub and then load without adding too many steps? It already takes about half hour to reload the test database. The objective here is the make sure the sensitive data is safe at all times and somehow increase the speed of reloading the test environment's database.

As a specific answer to "speed of reloading the test environment's database": you probably do not need to rebuild the test database (copy producion then randomise) every time it needs to be reset. Instead at key times take a copy, randomise it, and store that copy - then each time you reset the test environment all you have to do is restore a new copy of that database. You could speed things up further by having more than one restored at any given time: to reset drop the current DB, rename one of the other copies to take its place (testing work can now start immediately), restore another copy to replace the one you are now actively using ready for the next reset.

More generally:

Wanting to use production data as it is a good representation of the real world but not wanting to give people access to real data are, if the data is truely sensitive, completeley mutually exclusive. If you randomise the data enough that there is absolutely no way a sufficiently determined data miner couldn't infer useful real information from it then you've randomised it to the point that it no longer represents reality any more than made up test data would anyway.

An alternative is to create your own test data in a scripted manner which has a number of beneifts:

You don't have to worry about disguising sensitive data taken from production environments at all. From a regulatory and legal standpoint this can be a big win (if you work with banking information or personal data for instance).
You can control the size of the data-set. Just want a small set of data to develope a new feature against? Then generate a few tens or hundreds of people with relevant records going back a few months. Testing the performance of a new feature over large data? Generate a many Gb database with thousands upon thousands of people with data spanning many years.
Controlling the size of the data-set means you can generate small test sets very quickly. Larger ones may take more time but are still possible (or for larger tests you can fall back to using a munged copy of produciton data, with extra precautions to make sure no devs can touch the DB until the munging is complete).
You can control the inclusion of difficult edge cases. Keeping them out completly for initial delevopment of new ideas, and including every single problem case you have ever seen in production or otherwise can think of if you are about to preform regression testing on new code changes. This can then become a useful part of your QA cycle.
Your devs can have the flexibility to create their own local test databases easily, as and when they want without needing to coordinate with the shared testing environmemnts until they have something ready to deliver that far up the chain.

For "people" in the above substitude what-ever units your system's size is dependent on. There may be more than one key unit of course: for a training and competence tracking system with its own test managment and training material the key size determinants are the size of the question bank and the number of people, so the request to the sample data generating scripts would be something like:

Create a new DB
Make up a question bank of 10,000 questions spread over 20 subjects and 200 topics
Add 1,000 people in teams of about 8 with a supervisory heirarchy to match
Add 6 months data for each person having:
A realistic number of test records inclduing passses, fails and resits
A realistic number of observitions, 1:1 meetings and other interventions for that user type
and so on.

Of course there is work involved here, which is why the "take real data and try to disguise it" approach is a common short-cut (I do it myself often enough!):

You have to write the scripts in the first place
As new features are added to your product, or features change, the scripts need to be enhanced or otherwise altered to reflect these changes
Generating truely realistic test data takes some domain knowledge. Generating a random question bank is pretty easy (many instances of "This is a multiple choice question, the answer is B? A: The answer is A; B: The answer is B; C: The answer is C" is a place to start). For more complex/variable data you can use some analysis of your exsiting real-but-randomised data to guide the creation of your model.
You need to keep an eye on how your users are actually behaving, as changes to their use of your service need to be reflected in your test data model (and if you have widely differening clients, you may need to branch the model).

There are products out there that are designed to help create and maintain this sort of test data, though for small systems or particularly complex ones hand-rolling by a good local dev may produce better results cheaper.


Cursoring over sys.databases skips databases I know this question has been asked several times and I know the solution, but I am trying to understand the underlying cause of the problem:

I have the following code to perform database backups.

DECLARE @Filename   VARCHAR(256)
DECLARE @FileDate   VARCHAR(15)
DECLARE @Path       VARCHAR(50)
DECLARE @Name       VARCHAR(50)

-- specify database backup directory
SET @Path = '\MyPath'

-- specify filename date
SELECT @FileDate = CONVERT(VARCHAR(20), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108),':','')

DECLARE db_cursor CURSOR FOR
    SELECT [name]
        FROM master.sys.databases
        WHERE [name] NOT IN ('master', 'msdb', 'model', 'tempdb')
            AND [state_desc] = 'ONLINE'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @Name

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @Filename = @Path + @Name + '_Full_Backup_' + @FileDate + '.bak'
    BACKUP DATABASE @Name
    TO DISK = @Filename
    WITH CHECKSUM,
        COMPRESSION

    FETCH NEXT FROM db_cursor INTO @Name
END

CLOSE db_cursor
DEALLOCATE db_cursor

Sometimes, only some databases are backed up, suggesting that the cursor is not iterating over all of the rows returned or that the query itself is not returning the names of all the databases it should be.

I am trying to understand why this happens. I know that the fix is to use a STATIC cursor, suggesting the issue is with the results in the underlying query

SELECT [name]
    FROM master.sys.databases
    WHERE [name] NOT IN ('master', 'msdb', 'model', 'tempdb')
        AND [state_desc] = 'ONLINE'

are changing, but I can't see what would be changing (no database names would change and the Error log doesn't suggest that the database state has changed)

sys.databases is a complex view. On SQL Server 2016 it is:
CREATE VIEW sys.databases AS
    SELECT d.name,
        d.id AS database_id,
        r.indepid AS source_database_id,
        d.sid AS owner_sid,
        d.crdate AS create_date,
        d.cmptlevel AS compatibility_level,
        -- coll.value = null means that a collation was not specified for the DB and the server default is used instead
        convert(sysname, case when serverproperty('EngineEdition') = 5 AND d.id = 1 then serverproperty('collation')
                                 else CollationPropertyFromID(convert(int, isnull(coll.value, p.cid)), 'name') end) AS collation_name,
        iif ((serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x10000000) = 1), cast (3 as tinyint), p.user_access) AS user_access,
        iif ((serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x10000000) = 1), 'NO_ACCESS', ua.name) AS user_access_desc,
        sysconv(bit, d.status & 0x400) AS is_read_only,         -- DBR_RDONLY
        sysconv(bit, d.status & 1) AS is_auto_close_on,         -- DBR_CLOSE_ON_EXIT
        sysconv(bit, d.status & 0x400000) AS is_auto_shrink_on,     -- DBR_AUTOSHRINK
        case when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000020) = 1) then cast (1 as tinyint) -- RESTORING
             when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000080) = 1) then cast (7 as tinyint) -- COPYING
             when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000100) = 1) then cast (4 as tinyint) -- SUSPECT
             when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x08000000) = 1) then cast (8 as tinyint) -- QUORUM_RECOVERY_PENDING
             when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x04000000) = 1) then cast (9 as tinyint) -- CREATING
             else p.state
             end AS state, -- 7 is COPYING and 4 is SUSPECT state for database copy (UNDO: Need to have a clean way to set states in dbtable for a user db)
        case when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000020) = 1) then 'RESTORING'
             when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000080) = 1) then 'COPYING'
             when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000100) = 1) then 'SUSPECT'
             when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x08000000) = 1) then CONVERT(nvarchar(60), N'QUORUM_RECOVERY_PENDING')
             when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x04000000) = 1) then 'CREATING'
             else st.name
             end AS state_desc,
        sysconv(bit, d.status & 0x200000) AS is_in_standby,     -- DBR_STANDBY
        case when serverproperty('EngineEdition') = 5 then convert(bit, 0) else p.is_cleanly_shutdown end AS is_cleanly_shutdown,
        sysconv(bit, d.status & 0x80000000) AS is_supplemental_logging_enabled, -- DBR_SUPPLEMENT_LOG
        case when (serverproperty('EngineEdition') = 5) then sysconv(tinyint, sysconv(bit,(d.status & 0x00100000)))
             else p.snapshot_isolation_state end AS snapshot_isolation_state,
        case when (serverproperty('EngineEdition') = 5) and (sysconv(bit, d.status & 0x00100000) = 1) then 'ON'
             when (serverproperty('EngineEdition') = 5) and (sysconv(bit, d.status & 0x00100000) = 0) then 'OFF'
             else si.name end AS snapshot_isolation_state_desc,
        sysconv(bit, d.status & 0x800000) AS is_read_committed_snapshot_on,     -- DBR_READCOMMITTED_SNAPSHOT
        case when (serverproperty('EngineEdition') = 5)
             then case
                    when sysconv(bit,(d.status & 0x00000008)) = 1
                        then cast(3 as tinyint)
                    when sysconv(bit,(d.status & 0x00000004)) = 1
                        then cast(2 as tinyint)
                    else
                        cast(1 as tinyint)
                   end
              else p.recovery_model
        end AS recovery_model,
        case when (serverproperty('EngineEdition') = 5)
             then case
                    when sysconv(bit,(d.status & 0x00000008)) = 1
                        then CONVERT(nvarchar(60), N'SIMPLE')
                    when sysconv(bit,(d.status & 0x00000004)) = 1
                        then CONVERT(nvarchar(60), N'BULK_LOGGED')
                    else
                        CONVERT(nvarchar(60), N'FULL')
                  end
              else ro.name
        end AS recovery_model_desc,
        p.page_verify_option, pv.name AS page_verify_option_desc,
        sysconv(bit, d.status2 & 0x1000000) AS is_auto_create_stats_on,         -- DBR_AUTOCRTSTATS
        sysconv(bit, d.status2 & 0x00400000) AS is_auto_create_stats_incremental_on,    -- DBR_AUTOCRTSTATSINC
        sysconv(bit, d.status2 & 0x40000000) AS is_auto_update_stats_on,        -- DBR_AUTOUPDSTATS
        sysconv(bit, d.status2 & 0x80000000) AS is_auto_update_stats_async_on,  -- DBR_AUTOUPDSTATSASYNC
        sysconv(bit, d.status2 & 0x4000) AS is_ansi_null_default_on,            -- DBR_ANSINULLDFLT
        sysconv(bit, d.status2 & 0x4000000) AS is_ansi_nulls_on,                -- DBR_ANSINULLS
        sysconv(bit, d.status2 & 0x2000) AS is_ansi_padding_on,                 -- DBR_ANSIPADDING
        sysconv(bit, d.status2 & 0x10000000) AS is_ansi_warnings_on,            -- DBR_ANSIWARNINGS
        sysconv(bit, d.status2 & 0x1000) AS is_arithabort_on,                   -- DBR_ARITHABORT
        sysconv(bit, d.status2 & 0x10000) AS is_concat_null_yields_null_on,     -- DBR_CATNULL
        sysconv(bit, d.status2 & 0x800) AS is_numeric_roundabort_on,            -- DBR_NUMEABORT
        sysconv(bit, d.status2 & 0x800000) AS is_quoted_identifier_on,          -- DBR_QUOTEDIDENT
        sysconv(bit, d.status2 & 0x20000) AS is_recursive_triggers_on,          -- DBR_RECURTRIG
        sysconv(bit, d.status2 & 0x2000000) AS is_cursor_close_on_commit_on,    -- DBR_CURSCLOSEONCOM
        sysconv(bit, d.status2 & 0x100000) AS is_local_cursor_default,          -- DBR_DEFLOCALCURS
        sysconv(bit, d.status2 & 0x20000000) AS is_fulltext_enabled,            -- DBR_FTENABLED
        sysconv(bit, d.status2 & 0x200) AS is_trustworthy_on,               -- DBR_TRUSTWORTHY
        sysconv(bit, d.status2 & 0x400) AS is_db_chaining_on,               -- DBR_DBCHAINING
        sysconv(bit, d.status2 & 0x08000000) AS is_parameterization_forced, -- DBR_UNIVERSALAUTOPARAM
        sysconv(bit, d.status2 & 64) AS is_master_key_encrypted_by_server,  -- DBR_MASTKEY
        sysconv(bit, d.status2 & 0x00000010) AS is_query_store_on,          -- DBR_QDSENABLED
        sysconv(bit, d.category & 1) AS is_published,
        sysconv(bit, d.category & 2) AS is_subscribed,
        sysconv(bit, d.category & 4) AS is_merge_published,
        sysconv(bit, d.category & 16) AS is_distributor,
        sysconv(bit, d.category & 32) AS is_sync_with_backup,
        d.svcbrkrguid AS service_broker_guid,
        sysconv(bit, case when d.scope = 0 then 1 else 0 end) AS is_broker_enabled,
        p.log_reuse_wait, lr.name AS log_reuse_wait_desc,
        sysconv(bit, d.status2 & 4) AS is_date_correlation_on,      -- DBR_DATECORRELATIONOPT
        sysconv(bit, d.category & 64) AS is_cdc_enabled,
        case
            when (d.id = db_id('tempdb')) then sysconv(bit, p.is_db_encrypted)
            else sysconv(bit, d.status2 & 0x100)                    -- DBR_ENCRYPTION
            end AS is_encrypted,
        convert(bit, d.status2 & 0x8) AS is_honor_broker_priority_on,               -- DBR_HONORBRKPRI
        sgr.guid AS replica_id,
        sgr2.guid AS group_database_id,
        ssr.indepid AS resource_pool_id,
        default_language_lcid = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(smallint, p.default_language) else null end,
        default_language_name = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(sysname, sld.name) else null end,
        default_fulltext_language_lcid = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(int, p.default_fulltext_language) else null end,
        default_fulltext_language_name = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(sysname, slft.name) else null end,
        is_nested_triggers_on = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(bit, p.allow_nested_triggers) else null end,
        is_transform_noise_words_on = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(bit, p.transform_noise_words) else null end,
        two_digit_year_cutoff = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(smallint, p.two_digit_year_cutoff) else null end,
        containment = sysconv(tinyint, (d.status2 & 0x80000)/0x80000), -- DBR_IS_CDB
        containment_desc = convert(nvarchar(60), cdb.name),
        p.recovery_seconds AS target_recovery_time_in_seconds,
        p.delayed_durability,
        case when (p.delayed_durability = 0) then CAST('DISABLED' AS nvarchar(60)) -- LCOPT_DISABLED
             when (p.delayed_durability = 1) then CAST('ALLOWED' AS nvarchar(60)) -- LCOPT_ALLOWED
             when (p.delayed_durability = 2) then CAST('FORCED' AS nvarchar(60)) -- LCOPT_FORCED
             else NULL
             end AS delayed_durability_desc,
        convert(bit, d.status2 & 0x80) AS
        is_memory_optimized_elevate_to_snapshot_on,             -- DBR_HKELEVATETOSNAPSHOT
        sysconv(bit, d.category & 0x100) AS is_federation_member,
        convert(bit, isnull(rda.value, 0)) AS is_remote_data_archive_enabled,
        convert(bit, p.is_mixed_page_allocation_on) AS is_mixed_page_allocation_on
    FROM sys.sysdbreg d OUTER APPLY OpenRowset(TABLE DBPROP, (case when serverproperty('EngineEdition') = 5 then DB_ID() else d.id end)) p
    LEFT JOIN sys.syssingleobjrefs r ON r.depid = d.id AND r.class = 96 AND r.depsubid = 0  -- SRC_VIEWPOINTDB
    LEFT JOIN sys.syspalvalues st ON st.class = 'DBST' AND st.value = p.state
    LEFT JOIN sys.syspalvalues ua ON ua.class = 'DBUA' AND ua.value = p.user_access
    LEFT JOIN sys.syspalvalues si ON si.class = 'DBSI' AND si.value = p.snapshot_isolation_state
    LEFT JOIN sys.syspalvalues ro ON ro.class = 'DBRO' AND ro.value = p.recovery_model
    LEFT JOIN sys.syspalvalues pv ON pv.class = 'DBPV' AND pv.value = p.page_verify_option
    LEFT JOIN sys.syspalvalues lr ON lr.class = 'LRWT' AND lr.value = p.log_reuse_wait
    LEFT JOIN sys.syssingleobjrefs agdb ON agdb.depid = d.id AND agdb.class = 104 AND agdb.depsubid = 0 -- SRC_AVAILABILITYGROUP
    LEFT JOIN master.sys.syssingleobjrefs ssr ON ssr.class = 108 AND ssr.depid = d.id -- SRC_RG_DB_TO_POOL
    LEFT JOIN master.sys.sysclsobjs  ag ON ag.id = agdb.indepid AND ag.class = 67 -- SOC_AVAILABILITY_GROUP
    LEFT JOIN master.sys.sysguidrefs sgr ON sgr.class = 8 AND sgr.id = ag.id AND sgr.subid = 1 -- GRC_AGGUID / AGGUID_REPLICA_ID
    LEFT JOIN master.sys.sysguidrefs sgr2 ON sgr2.class = 9 AND sgr2.id = ag.id AND sgr2.subid = d.id -- GRC_AGDBGUID
    LEFT JOIN sys.syspalvalues cdb ON cdb.class = 'DCDB' AND cdb.value = CASE WHEN (d.status2 & 0x80000)=0x80000 THEN 1 ELSE 0 END
    LEFT JOIN sys.syslanguages sld ON sld.lcid = p.default_language
    LEFT JOIN sys.fulltext_languages slft ON slft.lcid = p.default_fulltext_language
    LEFT JOIN sys.sysobjvalues coll ON coll.valclass = 102 AND coll.subobjid = 0 AND coll.objid = d.id  -- SVC_DATACOLLATION
    LEFT JOIN sys.sysobjvalues rda ON rda.valclass = 116 AND rda.objid = d.id AND rda.valnum = 0 -- SVC_STRETCH & STRETCH_DB_IS_STRETCHED
    WHERE d.id < 0x7fff
        AND has_access('DB', (case when serverproperty('EngineEdition') = 5 then DB_ID() else d.id end)) = 1

By not specifying specific cursor options, you are implicitly requesting a local, dynamic, updatable, optimistic, forward-only cursor over this view.
SQL Server cannot produce a dynamic cursor plan, so it converts the cursor to a local, keyset, updatable, optimistic, forward-only cursor.
Keyset means the minimal keys needed to locate rows in the underlying system tables are stored in tempdb when the cursor is opened. If any of these key values change in the underlying system tables, a row will not be returned and @@FETCH_STATUS will return -2. Your loop will prematurely exit because @@FETCH_STATUS = 0 fails, and no further databases from the cursor will be processed. An example that could cause a key change is altering the ALLOW_SNAPSHOT_ISOLATION state for a database.
A keyset cursor is unwise when concurrent key changes are possible. We don't control the underlying tables, so using a keyset cursor on sys.databases is asking for trouble. It is also important to check @@FETCH_STATUS for failures, rather than assuming success until the end is reached.
Optimistic cursors use checksums to detect changes to rows since the cursor was opened, so updates are not lost. This is not directly important here, because you are not updating rows through the cursor, but SQL Server doesn't know that. It has to build a plan that collects, stores, and compares checksums.
Side note: Much of the work done in the open and fetch execution plans is redundant because you are only interested in database names and the state_desc column. Most of the redundant system table accesses cannot be removed because the optimizer doesn't have sufficient key relationship information to allow outer join removal. You can work around this by adding DISTINCT name (so the outer joins cannot duplicate rows), but that also forces a cursor downgrade to static (snapshot). Nevertheless, you might be interested to look at the effect on the execution plans of adding DISTINCT.
The lesson here is to specify the type of cursor you want explicitly, and not rely on the defaults. This use of a cursor mandates a static (snapshot) type:
DECLARE db_cursor CURSOR
    LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR ...


sql server 2012 master database log is corrupted sql 2012 master database is corrupted, i have recent backup of master, what will happen to my user databases after restoring master, do i have to restore all db's from latest backups?

Thanks

If any database is corrupted, I start to worry about the storage it lives on. If you haven't identified the root cause of the corruption yet - like a bad storage driver, a bad storage cable, a failing RAID array, or maybe even a known bug in SQL Server - then I'd start by assuming that the server and its storage is not completely reliable.

I'd start by failing over to a different server on different storage, and run CHECKDB against all of your user databases (not just master) to make sure they're okay. (Don't think that a clean CHECKDB on the existing server means you're out of the woods - master might have just been the first place that storage decided to corrupt.)

For much more details, check out my post, What to Do When DBCC CHECKDB Reports Corruption, which includes a checklist on:

Turn off your backup-delete jobs
Query the msdb.dbo.suspect_pages table
If you're using a SAN, alert the storage team
Alert the application owners and stakeholders
Review the history of your CHECKDB and backup jobs

And more.


I have accidently deleted ibdata1 on an ubuntu server I have accidently deleted the 'ibdata1' file on the server having innodb, Is there any way I can recover it?

the mysql service has restarted since the deletion.

As you probably know, you've deleted your InnoDB data file. Your options are basically to restore from backup if you have one, or hire a data recovery company with MySQL knowledge to see what they can salvage off the disks. Some of the MySQL consulting firms like Percona and Pythian might be able to help you there, but it won't be cheap, and they might not be able to recover much. If you want to try recovering the deleted file, stopping all write activity on the filesystem that held ibdata1 or remounting it read-only as soon as possible will increase the likelihood that someone will be able to retrieve anything.


We need to do encryption in transit in mongodb so which one is recommended ssl or tsl ? and what is the difference between ssl and tls? We are using MongoDB 4.0 community version with 1 primary 2 secondary replicaset, we need to do encryption in transit so which one is recommended ssl or tsl ? and what is the difference between ssl and tls ?

TLS (Transport Layer Security) is the successor to the now deprecated SSL (Secure Sockets Layer). Early versions of TLS included some backward compatibility with SSL, but modern security protocols are now fully TLS. Older versions of SSL (and TLS) have known weaknesses and are not recommended (and often no longer supported).

The mongod and mongos configuration files have a net.ssl.disabledProtocols configuration option to disallow usage of some protocol versions for improved security. Starting in version 4.0, MongoDB disables the use of TLS 1.0 if TLS 1.1+ is available on the system.

The two protocols are often described as TLS/SSL since SSL is the older (and typically more familiar) reference.

MongoDB configuration parameters for TLS/SSL in MongoDB 4.0 include SSL in the name for historical reasons, but starting from MongoDB 4.2 there are new TLS options (which are renamed equivalents of the older SSL options) to provide clarity that the protocol is in fact TLS.


MySQL won't start, no log ins /var/logs/mysql* MySQL sudently stop working, when start manually mysqld with `mysqld`` command:

# mysqld
160805 11:34:10 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
160805 11:34:10 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
160805 11:34:10 [Note] Plugin 'FEDERATED' is disabled.
160805 11:34:10 InnoDB: The InnoDB memory heap is disabled
160805 11:34:10 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160805 11:34:10 InnoDB: Compressed tables use zlib 1.2.7
160805 11:34:10 InnoDB: Using Linux native AIO
160805 11:34:10 InnoDB: Initializing buffer pool, size = 128.0M
160805 11:34:10 InnoDB: Completed initialization of buffer pool
160805 11:34:10 InnoDB: highest supported file format is Barracuda.
InnoDB: Error: trying to access page number 46616 in space 0,
InnoDB: space name ./ibdata1,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.
InnoDB: If you get this error at mysqld startup, please check that
InnoDB: your my.cnf matches the ibdata files that you have in the
InnoDB: MySQL server.
160805 11:34:10  InnoDB: Assertion failure in thread 140324139591456 in file fil0fil.c line 4578
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
09:34:10 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 346700 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x30000
mysqld(my_print_stacktrace+0x29)[0x7f9fc2d3f0b9]
mysqld(handle_fatal_signal+0x3d8)[0x7f9fc2c26418]
/lib/x86_64-linux-gnu/libpthread.so.0(+0xf0a0)[0x7f9fc23d40a0]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x35)[0x7f9fc0c65165]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x180)[0x7f9fc0c683e0]
mysqld(+0x5f3333)[0x7f9fc2df6333]
mysqld(+0x5cee49)[0x7f9fc2dd1e49]
mysqld(+0x5cf7ec)[0x7f9fc2dd27ec]
mysqld(+0x5c02c8)[0x7f9fc2dc32c8]
mysqld(+0x59d55f)[0x7f9fc2da055f]
mysqld(+0x592d44)[0x7f9fc2d95d44]
mysqld(+0x593aac)[0x7f9fc2d96aac]
mysqld(+0x59541a)[0x7f9fc2d9841a]
mysqld(+0x582b23)[0x7f9fc2d85b23]
mysqld(+0x54f0ff)[0x7f9fc2d520ff]
mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x41)[0x7f9fc2c287a1]
mysqld(+0x3329d7)[0x7f9fc2b359d7]
mysqld(_Z11plugin_initPiPPci+0xa73)[0x7f9fc2b38a53]
mysqld(+0x2b9155)[0x7f9fc2abc155]
mysqld(_Z11mysqld_mainiPPc+0x45b)[0x7f9fc2abcdcb]
/lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xfd)[0x7f9fc0c51ead]
mysqld(+0x2b0f59)[0x7f9fc2ab3f59]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

Tryied in my.cnf

[mysqld]
innodb_force_recovery = 1

without change. Where can i investiguate ?

As the log indicates, you might have changed the values of the following parameters:

key_buffer_size
read_buffer_size
sort_buffer_size
max_threads

If you have changed one of these values, try to revert the change and start the MySQL server again.

If you have not changed the values, calculate the memory usage by:

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads

If you find the system installed memory is sufficient, you need to check if there is a corrupted table in your databases:

[mysqld]
innodb_force_recovery = 6

Then, to find out which database table is corrupted:

# mysqlcheck --all-databases

Write down all the tables listed as corrupted, you have to export the corrupted tables by:

# mysqldump -u{username} -p{password} {database_name} {table_name} > table_backup.sql

After that, you can drop the table in Mysql console:

Mysql> drop table {corrupted_table_name};

Then, we have to change back the Mysql configuration:

[mysqld]
innodb_force_recovery = 0

The MySQL server should be able to start up with normal operation mode.

Next, we have to import the data of corrupted tables:

# mysql -u{username} -p{password} {database_name} < table_backup.sql

If everything runs fine, you should have your table data back and working Mysql Server.

Reference: InnoDB table recovery step-by-step-guide


Which table should a foreign key live on I'm learning about databases. In my very simple database, I have 2 tables.

WorkingHoursDetail

ID
NameOfWeek
Hours

and

WorkingHours

ID
Name

As you can see, they currently do not relate.

Now, I'm faced with the question of making them relate. I understand I do this by adding a foreign key, something like MyOtherTableID and linking this to the other tables ID.

In the example above, I could add a foreign key to EITHER

WorkingHoursDetail

ID
NameOfWeek
Hours
WorkingHoursId

with

WorkingHours

ID
Name

OR

WorkingHoursDetail

ID
NameOfWeek
Hours

with

WorkingHours

ID
Name
WorkingHoursDetailId

I've tried this, and it seems to work but by having this option frightens me. Is there a 'right' answer or will this change only affect the order in which data can be written to the database. If this is the case, then surely this will dictate the flow of the program using it (the users GUI).

As a rule of thumb, you should add a foreign key on the child table referencing the parent table. In your case it appears that WorkingHoursDetail is the child table and WorkingHours the parent table.

You can identify the parent table by asking which table can exists on its own without the presence of the other table. In your case WorkingHours can exists without WorkingHoursDetails (but not the other way around, so WorkingHours appears to be the parent table.

Alternatively you can look at the data flow when inserting new records. If both tables are empty, you should have to insert a row in WorkingHours first and then a record in WorkingHoursDetail. This makes WorkingHours the parent table and therefore the foreign key should be added on WorkingHoursDetail referencing WorkingHours.