MySQL slave stuck at 'System lock' most of the time, high CPU usage Let me describe what we are facing now:

We have a MySQL Master-Slave, Row Based Replication setup and at the moment, the slave is stuck with a perpetual System Lock. The Master is an active server with many updates and deletes running, but the slave will just not replicate anything. There are no errors in the log and the slave is reading binary logs fine from the master, but it does nothing on the slave. The Seconds_Behind_Master value keeps increasing. This is what show processlist on slave shows:

mysql> show processlist ;
+----+-------------+-----------+------+---------+-------+----------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time  | State                            | Info             |
+----+-------------+-----------+------+---------+-------+----------------------------------+------------------+
| 10 | system user |           | NULL | Connect |  4985 | Waiting for master to send event | NULL             |
| 11 | system user |           | NULL | Connect | 53715 | System lock                      | NULL             |
| 14 | root        | localhost | NULL | Sleep   |  2958 |                                  | NULL             |
| 16 | root        | localhost | NULL | Query   |     0 | init                             | show processlist |
+----+-------------+-----------+------+---------+-------+----------------------------------+------------------+
4 rows in set (0.00 sec)

And show slave status shows:

SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: System lock

The only sign of life is that Relay_Log_Pos from the slave status is changing values but very slowly. Does it mean that it is running the queries from the binary log, but just that it is too slow?

Pasting some info from show engine innodb status :

LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 14, OS thread handle 0x7f7824698700, query id 216193 localhost root init
show engine innodb status
---TRANSACTION 230426904, not started
mysql tables in use 1638, locked 1638
MySQL thread id 11, OS thread handle 0x7f7824920700, query id 216192 System lock
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)

Also noticed that the mysqld process for this particular mysql instance has high CPU usage. What could be causing the Slave_SQL_Running_State: System lock and blocking the slave from running the queries? There are no disk issues with the machine and there is another MySQL instance running which is a slave to another MySQL instance (myisam) on the same master and that is running fine.

MySQL version : 5.6.20.
OS : RHEL 6.5
We have some tables that are partitioned (if that matters).

Edit: Just noticed that Slave_SQL_Running_State is occasionally changing to 'Reading event from the relay log'. So it looks like the replication is just too slow.

first we need to understand , there is no specific solution for this .
So as per provided information in Question, what good is :

You have mentioned that relay log position is keep on changing which means "SQL thread" is working

You have mentioned that Slave_SQL_Running_State is also getting change which means I/O thread is also working.
What bad is :"relay log space is increasing" which means data is coming but taking time to execute.

Lets dwell more here :

Are you observing slowness in Master as well ? Any slow Query in master ? If answer is No , proceed towards 2

Is configuration of both server same ? In this you must check mysql buffers pool configuration and isolation method. I have personally experience that Isolation level can also be culprit ( Repeatable read which is default in MySQL ) If same , proceed towards 3

Have you checked what query is executing at time when you observed slowness.
Just do , pager grep Query followed by show processlist;

This will give you an idea what is stucking.
Lets go more inside , check relay log position and check in relay log what is the query and try to optimize it. But before that be ensure if it is really performing slow .
You can use performance schema database and in this database , just enable instrument for SQL,IO,Replication and take help of this link : https://dev.mysql.com/doc/refman/5.7/en/performance-schema-replication-tables.html
Apart from this , you can also use SYS schema to diagnose whats going wrong.
What Rolando told is one of the cause and he is correct . Load file can generally cause this issue.
Let me know if i make sense :)


PROPOSED QUERY

select SEC_TO_TIME(IF(WorkSec<0,UNIX_TIMESTAMP(),0)+WorkSec) WorkTime
from (select SUM(UNIX_TIMESTAMP(concat(a_date,' ',a_time))*
IF(a_status='IN',-1,1)) WorkSec
from worktable where emp_id = 2) A;

SAMPLE DATA

mysql> use test
Database changed
mysql> drop table if exists worktable;
Query OK, 0 rows affected (0.13 sec)

mysql> create table worktable
    -> (a_id int not null auto_increment,
    -> emp_id int not null,a_date date,
    -> a_time time,a_status varchar(45),
    -> primary key (a_id),key (emp_id));
Query OK, 0 rows affected (0.52 sec)

mysql> insert into worktable
    -> (emp_id,a_date,a_time,a_status) values
    -> (2,'2014-04-01','09:02:00','IN'),
    -> (2,'2014-04-01','13:00:00','OUT'),
    -> (2,'2014-04-01','16:30:00','IN'),
    -> (2,'2014-04-01','21:30:00','OUT');
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from worktable;
+------+--------+------------+----------+----------+
| a_id | emp_id | a_date     | a_time   | a_status |
+------+--------+------------+----------+----------+
|    1 |      2 | 2014-04-01 | 09:02:00 | IN       |
|    2 |      2 | 2014-04-01 | 13:00:00 | OUT      |
|    3 |      2 | 2014-04-01 | 16:30:00 | IN       |
|    4 |      2 | 2014-04-01 | 21:30:00 | OUT      |
+------+--------+------------+----------+----------+
4 rows in set (0.00 sec)

mysql>

RUN PROPOSED QUERY FOR EMPLOYEE 2 (WHO IS OUT)

mysql> select SEC_TO_TIME(IF(WorkSec<0,UNIX_TIMESTAMP(),0)+WorkSec) WorkTime
    -> from (select SUM(UNIX_TIMESTAMP(concat(a_date,' ',a_time))*
    -> IF(a_status='IN',-1,1)) WorkSec
    -> from worktable where emp_id = 2) A;
+-----------------+
| WorkTime        |
+-----------------+
| 08:58:00.000000 |
+-----------------+
1 row in set (0.00 sec)

mysql>

BRING EMPLOYEE 2 IN TO WORK TODAY

mysql> insert into worktable
    -> (emp_id,a_date,a_time,a_status) values
    -> (2,'2014-04-02','09:02:00','IN');
Query OK, 1 row affected (0.06 sec)

mysql> select * from worktable;
+------+--------+------------+----------+----------+
| a_id | emp_id | a_date     | a_time   | a_status |
+------+--------+------------+----------+----------+
|    1 |      2 | 2014-04-01 | 09:02:00 | IN       |
|    2 |      2 | 2014-04-01 | 13:00:00 | OUT      |
|    3 |      2 | 2014-04-01 | 16:30:00 | IN       |
|    4 |      2 | 2014-04-01 | 21:30:00 | OUT      |
|    5 |      2 | 2014-04-02 | 09:02:00 | IN       |
+------+--------+------------+----------+----------+
5 rows in set (0.00 sec)

mysql>

RUN PROPOSED QUERY FOR EMPLOYEE 2 (WHO IS IN TODAY)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2014-04-02 10:20:49 |
+---------------------+
1 row in set (0.00 sec)

mysql> select SEC_TO_TIME(IF(WorkSec<0,UNIX_TIMESTAMP(),0)+WorkSec) WorkTime
    -> from (select SUM(UNIX_TIMESTAMP(concat(a_date,' ',a_time))*
    -> IF(a_status='IN',-1,1)) WorkSec
    -> from worktable where emp_id = 2) A;
+-----------------+
| WorkTime        |
+-----------------+
| 10:16:51.000000 |
+-----------------+
1 row in set (0.00 sec)

mysql>

EPILOGUE

When an employee is out, the WorkTime is evidently a static datetime value.

When an employee is in, the WorkTime is evidently a moving datetime value.

I will leave it to you to tweek the query to set it for a day, a week, for a group of employees, or whatever you boundaries you need.

GIVE IT A TRY !!!


SQL database disaster recovery in a second datacenter using storage I have the following:

Data Center (1) has a set of two Microsoft SQL Server databases. They are clustered using MSCS and shared NetApp storage FAS3250 system. This provides me with local fail-over / redundancy. I want now to extend this to a second data center, let’s call it Data Center (2). DC# 1 and DC# 2 and connected to a flat high speed network, so we can share the same IP subnet between the two data centers.  In DC# 2 there is another Microsoft SQL Server database connected to another NetApp storage FAS3250 system.  Here are my questions:

I wanted DC# 2 to be my disaster recovery for the SQL Server database.
I wanted the solution to be based on NetApp solution using SQL standard
Need to make sure any ODBC connections and client connections are now migrated to the SQL database server in DC#2

Any thoughts or ideas to make this happen?  Thanks,

Skaher

With a standard edition you basically have two options, log shipping and mirroring.  Unfortunately, neither of these solutions gives you an automated or managed solution for your third requirement of migrating connections.  However, you could base connections on a set of configuration files and swap them out for a "failover" scenario.

If you can swing an Enterprise upgrade, AvailabilityGroups become a much better holistic solution for data and connections.  You have both hardware and data redundancy and connections to the database server are managed via a listener service, directing database requests to the active server.  Downtime is almost non-existent.


How to loop SQL query in mysql? I have a SQL query as following:

SET @thedate := CURDATE();

create temporay table tmp1(
...
where thedate=@thedate
);

create temporay table tmp2(
...
where thedate=@thedate
);

delete from tbl1;

replace into tbl1(
select *
from tmp1
inner join tmp2
);

I need to have a loop, that @thedate is from 2014-1-1 to today. For each day, a new tbl1 generate, I also need t record it as a tsv, I wrote a script to do it but, I do not know how to do it from 2014-1-1 to today. the script is as following:

now=$(date +"%Y%m%d")
mysql mydb -e "select * from tbl1">$now.tsv

Excuse the mess I am about to create, but here it goes ...

STEP 01

Create a file called /tmp/ProcessDate.sql which contains this

create temporary table tmp1(
...
where thedate=@thedate
);

create temporary table tmp2(
...
where thedate=@thedate
);

delete from tbl1;

replace into tbl1(
select *
from tmp1
inner join tmp2
);

STEP 02

Create a Shell Script call /tmp/ProcessDays.sh which contains this

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"

TS_NOW=`date +"%s"`
TS_NDX=1
TS_DAY=0

while [ ${TS_DAY} -lt ${TS_NOW} ]
do
    SQL="SELECT DATE(FROM_UNIXTIME((UNIX_TIMESTAMP(MAKEDATE(YEAR(NOW()),${TS_NDX})))))"
    DT=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
    echo "SET @thedate = '${DT}';" > /tmp/ProcessOneDay.sql
    cat /tmp/ProcessDate.sql >> /tmp/ProcessOneDay.sql
    OUTFILE=Data_${DT}.tsv
    mysql ${MYSQL_CONN} -AN < /tmp/ProcessOneDay.sql
    mysql ${MYSQL_CONN} -AN"SELECT * FROM tbl" > ${OUTFILE}
    (( TS_NDX++ ))
    SQL="SELECT UNIX_TIMESTAMP(MAKEDATE(YEAR(NOW(),1) + INTERVAL ${TS_NDX} DAY)"
    TS_DAY=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
done

STEP 03 : GIVE IT A TRY !!!


Indexed VIEW in SQL Server with PIVOT A simplified version of my problem is as follows:

I have three tables Records, Inputs, and Outputs.

Records

Id

      DTS

1

2015-01-01 00:00

...

Inputs

Id

RecordId

InputId

Value

1

1

1

100

...

N

1

1

105

...

Outputs

Id

RecordId

OutputId

Value

1

1

1

200

...

M

1

1

10

...

This fragmentation was done to avoid limiting the total number of inputs or outputs due to constraints on the number of columns in a table. I am looking for the best (fastest) way to run queries against something similar to the following:

      DTS

Input1

...

InputN

Output1

...

OutputM

2015-01-01 00:00

100

...

105

200

...

10

...

My initial plan was to create a view based on pivoting the requisite inputs and outputs and joining them appropriately. The data only needs to be readonly and is essentially static, so I want to prevent it from being expanded for each query. I read that I could add an index to the view to 'materialize' it, but this can't be done if the view uses PIVOT.

Does anyone have any advice on how I might accomplish this? Or if I am approaching this the wrong way, I would happily take suggestions.

Given that the data is changing very infrequently and there will therefore be very little overhead in maintaining the indexed views, an approach using indexed views could help performance.  As you likely noticed when trying to create an indexed view that uses PIVOT, there are a lot of restrictions on what syntactic constructs can be used in indexed views.

To get around this limitation, you can use a combination of aggregation (via SUM) and CASE statements.  However, you'll then run into another limitation, which is that a view that uses UNION cannot be materialized.  However, it is easy to make a separate view for the aggregated inputs and outputs in this case, and that approach is worth testing on your specific workload to see if it yields a performance benefit.  Below is a sample script that illustrates both approaches:

-- Set up test data with 3 records, and each record has 2 inputs and 3 outputs
CREATE TABLE dbo.records (Id INT IDENTITY(1,1) NOT NULL, DTS DATETIME NOT NULL)
GO
INSERT INTO dbo.records (DTS)
VALUES  (GETDATE()-2),
        (GETDATE()-1),
        (GETDATE())
GO
CREATE TABLE dbo.inputs (Id INT IDENTITY NOT NULL, recordId INT NOT NULL, inputId INT NOT NULL, value FLOAT NOT NULL)
GO
INSERT INTO dbo.inputs (recordId, inputId, value)
SELECT r.Id AS recordId, i.inputId, r.Id * i.inputId AS value
FROM dbo.records r
CROSS JOIN (VALUES (1), (2)) AS i(inputId)
GO
CREATE TABLE dbo.outputs (Id INT IDENTITY NOT NULL, recordId INT NOT NULL, outputId INT NOT NULL, value FLOAT NOT NULL)
GO
INSERT INTO dbo.outputs (recordId, outputId, value)
SELECT r.Id AS recordId, i.outputId, -1 * r.Id * i.outputId AS value
FROM dbo.records r
CROSS JOIN (VALUES (1), (2), (3)) AS i(outputId)
GO

-- Create the indexed view for inputs
CREATE VIEW dbo.recordInputs WITH SCHEMABINDING AS
SELECT r.Id AS recordId,
    r.DTS,
    -- A COUNT_BIG() is mandatory to create an indexed view
    COUNT_BIG(*) AS numInputs,
    -- Generate the columns input1, ..., inputM
    SUM(CASE WHEN i.inputId = 1 THEN i.value ELSE 0 END) AS Input1,
    SUM(CASE WHEN i.inputId = 2 THEN i.value ELSE 0 END)Input2
FROM dbo.records r
JOIN dbo.inputs i
    ON i.recordId = r.Id
GROUP BY r.Id, r.DTS
GO
CREATE UNIQUE CLUSTERED INDEX UQ_recordInputs ON dbo.recordInputs (recordId)
GO

-- Create the indexed view for outputs
CREATE VIEW dbo.recordOutputs WITH SCHEMABINDING AS
SELECT r.Id AS recordId,
    r.DTS,
    -- A COUNT_BIG() is mandatory to create an indexed view
    COUNT_BIG(*) AS numRows,
    -- Generate the columns output1, ..., outputM
    SUM(CASE WHEN o.outputId = 1 THEN o.value ELSE 0 END) AS Output1,
    SUM(CASE WHEN o.outputId = 2 THEN o.value ELSE 0 END) AS Output2,
    SUM(CASE WHEN o.outputId = 3 THEN o.value ELSE 0 END) AS Output3
FROM dbo.records r
JOIN dbo.outputs o
    ON o.recordId = r.Id
GROUP BY r.Id, r.DTS
GO
CREATE UNIQUE CLUSTERED INDEX UQ_recordOutputs ON dbo.recordOutputs (recordId)
GO

-- Create the overall view
CREATE VIEW dbo.recordInputsAndOutputs WITH SCHEMABINDING AS
SELECT i.recordId, i.DTS, i.input1, i.input2, o.output1, o.output2, o.output3
FROM dbo.recordInputs i WITH (NOEXPAND) /* In non-enterprise versions of SQL, you will likely need this hint to avoid expanding the indexed view */
JOIN dbo.recordOutputs o WITH (NOEXPAND)
    ON o.recordId = i.recordId
GO

-- Test the view (turn on "Include Actual Execution Plan" first in SSMS to confirm usage of the indexed views)
SELECT *
FROM dbo.recordInputsAndOutputs
GO

-- Cleanup
DROP VIEW dbo.recordInputsAndOutputs
DROP VIEW dbo.recordInputs
DROP VIEW dbo.recordOutputs
DROP TABLE dbo.inputs
DROP TABLE dbo.outputs
DROP TABLE dbo.records
GO

One caveat to point out is that these indexed views aren't really doing any aggregation.  We still have the same number of data elements as before, but they are simply pivoted into a smaller number of rows (and larger number of columns).  Even so, I have often observed significant performance gains from operating on a pivoted row set that contains fewer rows to represent the same data.  I'll defer to deeper experts on why this might be, but it is fairly intuitive given the row-based processing model where SQL Server needs to pull each row through each query plan operator, presumably incurring some overhead for each row while doing so (at least for row-mode execution, which covers everything except parallel columnstore plans).

One last note is that you will often get better and faster responses if you publish a snippet of SQL code that generates some basic test data in your schema (similar to the "set up test data" section of the script above) in addition to just showing the schema.  This makes it much easier for someone to help you out and lowers the possibility of mis-interpreting the question.


Good ratio for data, journal, and log drive sizes for MongoDB? Is there a good ratio for drive sizes for MongoDB?

For example if you have a 8Gig drive for the data, then how big should my drive for the log be? How about the drive for the journal?

Just a little more background I'm following this tutorial, which states:

According to this MongoDB tutorial which explains how to manually deploy MongoDB on EC2, one of the steps states that you should have:

"Individual PIOPS EBS volumes for data (1000 IOPS), journal (250 IOPS), and log (100 IOPS)."

You can combine it on 1 disk, if you wish. Not obligated to split.

Journal

Journal will take 3GB (or less than 400MB if you use --small-files option)

Journal + Pre-Allocation

Be aware. If you don't use --small-files, then at least 8GB (journal and oplog included) will be pre-allocated to your disk. This is not lost space, but just reserved to improve the speed of mongo.  Using --small-files, only 1.4GB will be preallocated.

For discovering and testing purposes. Start with --small-files.

Logfiles

Logfiles will depend on the verbosity and insensitivity of the system. But for as you speak about a 8GB data disk. Then it won't be that much. Default is only some system messages and errors. (http://docs.mongodb.org/manual/reference/configuration-options/)

To let logfiles rotate, send "kill -SIGUSR1 pid" or
mongo --port 27017 --eval "db.runCommand({logRotate:1});" admin
(http://docs.mongodb.org/manual/reference/command/logRotate/). And then I delete daily via a crontab the logs older than 3 days.

Splitting on different drives

The reason is to optimize the disk for the purpose. Journal is a capped collection that just writes in sequence. So less IOPS needed. And logs are logs, just adding information. And then Data, well, reading jumps around a lot, and writing sometimes also, filling up gaps that were freed. And while journal and log are written away on other disks, the data disk doesn't lose time on that. Every little bit can help on intensive systems. The next step then is Replication and Sharding to spread the load.

On the https://university.mongodb.com, you can get more information about this if you are interested. Following M202 MongoDB Advanced Deployment and Operations now, what offers some specific information to optimize.


I need to find mismatches between tables So my problem is basically that I have an accounting table that has transaction references, and supporting document numbers.  Some of the document numbers have become mismatched from their actual transaction number.  The worst part is that the bad document numbers are actually valid, but reference a bad transaction. So here's the important part:

Every transaction has two parts.
Reference#, Type (Type A and Type B), and a document number.

I need to find all transactions where either (REF#, Type A)'s DOC# doesn't equal (REF#, Type B)'s DOC#, or where there is no matching Type A or Type B transaction at all.

Nevermind, I think I got this.

Used subselect to create a TypeA table and a TypeB table, joined on REF# and searched for TypeA.DOC# != TypeB.DOC#


Best practice for tempdb log file I have read many blogs here and there about how to configure tempdb data files but i didnt found any information regarding the tempdb log file.

Here's the strategy that I'm presently using with my tempdb:

I have used the recommendations of Paul Randal on how to split my tempdb data files
I have set the size of my tempdb data files to their maximum and disabled autogrowth. For example, i have 100gb of free disk space and set the size of my 8 tempdb data files to 10gb each. This prevent fragmentation on the disk as recommended by Brent Ozar and also I have 20gb free for my log file.

But like I said, nobody is talking about the tempdb log file. What should I do with it? On my setup, this file is at the same place as the tempdb data files. What is the size and the autogrowth value that I should use with the tempdb log file?

It really depends on how much data is going to flow through the transaction log.  Look at how big the log gets today.  You need to configure the log to be at least that size when SQL starts up.  For most of my clients they end up with a 3-4 Gig transaction log for the tempdb, which contains just a few VLFs and everything works nice and smoothly.


Error on PostgreSQL streaming replication I have the exact same problem as Wal segment still exists on master, but logs on slave and master say it's been removed except that it is in Production !

PostgreSQL 9.4 / Debian Jessie

postgresql.conf:

wal_level = hot_standby
max_wal_senders = 5

pg_hba.conf

host    replication     repl        x.x.x.0/20               trust

On the slave replica : Recovery.conf

standby_mode = on
primary_conninfo = 'host=<master_server_ip> port=xxxx user=xxxx password=xxxx'
trigger_file = '/var/lib/postgresql/trigger_failover'

Error on Master:

ERROR:  requested WAL segment 000000030000007E00000054 has already been removed

Error on slave replica:

LOG:  started streaming WAL from primary at 7E/54000000 on timeline 3
UTC [31252-2] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000030000007E00000054 has already been removed

What can I do to correct it without stopping the production ?

It is not working for several months now, so I guess there is a lot to recover. I can't even find the archives in ./postgresql/9.4/main/mnt/server/archivedir/

It sounds like the standby is now several months out of date.  It's probably not useful at this point.  You can replace the standby server with a new basebackup and start up the streaming again.  You may want to look into using replication slots this time to ensure that WALs won't be prematurely removed from the primary.


How to sample records by time So I've got a table in SQL Server 2014 that stores events.  Events are generated no faster than once per 10 seconds, but can be much slower (minutes or hours between events).  The time between events is not uniform.

I'd like to sample the records by time.
For instance, I'd like to ask for a 10 minute sampling - so it would grab record 1, and the next record should be one that is at least 10 minutes later, and then the next is at least 10 minutes later than row 2, and so on.

I can achieve this with an iterative approach, looping over each record, but I'd like to let the database do the work.  The problem is, I don't know how to do this using T-SQL set/window statements.

Can anyone help me out?

Example data:

EventID | RecordTime
--------+--------------------
   1    | 2017-04-01 12:00:00
   2    | 2017-04-01 12:00:10
   3    | 2017-04-01 12:00:20
   4    | 2017-04-01 12:00:32
   5    | 2017-04-01 12:05:42
   6    | 2017-04-01 12:09:00
   7    | 2017-04-01 12:24:12
   8    | 2017-04-01 12:36:46
   9    | 2017-04-01 12:36:57
  10    | 2017-04-01 15:00:00

Desired result:

EventID | RecordTime
--------+--------------------
   1    | 2017-04-01 12:00:00
   7    | 2017-04-01 12:24:12
   8    | 2017-04-01 12:36:46
  10    | 2017-04-01 15:00:00

EventIDs 2-6 are all within 10 minutes of record 1, so I don't want them.
EventID 7 is 24 minutes 12 seconds later than the last record in my result set (EventID 1), so I do want it.
EventID 8 is 12 minutes 34 seconds later than the last record in my result set (EventID 7), so I do want it.
EventID 9 is 11 seconds later than the last record in my result set (EventID 8), so I do not want it.
EventID 10 is apx. 2.5 hours later than the last record in my result set (EventID 9), so I do want it.

If you don't want event at 12:10:01, then answer is very simple - use LEAD or LAG to compare timestamps of the two consecutive rows. Just be aware that if you have a long sequence of events in which each pair of events is less than 10 minutes apart, then the query will return only the first event of this sequence.

In other words, there has to be gap of more than 10 minutes between two events to add a row to the result set.

Sample data

DECLARE @T TABLE(EventID int, RecordTime datetime2(0));
INSERT INTO @T (EventID, RecordTime) VALUES
( 1, '2017-04-01 12:00:00'),
( 2, '2017-04-01 12:00:10'),
( 3, '2017-04-01 12:00:20'),
( 4, '2017-04-01 12:00:32'),
( 5, '2017-04-01 12:05:42'),
( 6, '2017-04-01 12:09:00'),
( 7, '2017-04-01 12:24:12'),
( 8, '2017-04-01 12:36:46'),
( 9, '2017-04-01 12:36:57'),
(10, '2017-04-01 15:00:00');

Query

WITH
CTE
AS
(
    SELECT
        EventID
        ,RecordTime
        ,LAG(RecordTime) OVER (ORDER BY EventID) AS PrevRecordTime
    FROM @T
)
SELECT
    EventID
    ,RecordTime
FROM
    CTE
    CROSS APPLY
    (
        SELECT DATEDIFF(second, PrevRecordTime, RecordTime) AS RecordDiffSeconds
    ) AS CA
WHERE
    RecordDiffSeconds IS NULL
    OR RecordDiffSeconds > 600
ORDER BY EventID;

Result

+---------+---------------------+
| EventID |     RecordTime      |
+---------+---------------------+
|       1 | 2017-04-01 12:00:00 |
|       7 | 2017-04-01 12:24:12 |
|       8 | 2017-04-01 12:36:46 |
|      10 | 2017-04-01 15:00:00 |
+---------+---------------------+


External authentication using Oracle Standard I want to authenticate my client connections externally, using a custom external application. Is this possible using Oracle Standard 11g? If so, how?

(our custom external application will not have access to the users password, it'll just relay the requisition elsewhere)

The only documentation I found speaks of OS authentication, or using a wallet.

I'm afraid it is practically impossible. In Oracle's terminology external authentication means: "If users's name on client machine is identical to user's username in database, then user does not have to enter ANY password". This way of authentication is very weak and must be combined with other security features, for example you allow users to login only from one trusted, secured host.

Oracle does not support any PAM modules (like Informix for example) or whatever. The only option is how to connect Oracle to an external auth system are:

Kerberos/RADIUS - requires Enterprise edition, plus Advanced security option, which might require extra licencing
N-tier authentication - something like Unix su on Oracle level. But this requires support on application side.
Password protected role enabled by Logon trigger.


How to grant privileges to use DBMS_OUTPUT in Oracle? SET SERVEROUTPUT ON, DBMS_OUTPUT work perfect in my user, System or DBA. But when i create new user, it's not work!
How to grant privileges to use this?

connect system / [PASSWORD] as sysdba;
grant create session to [USER];
grant execute on DBMS_OUTPUT  to [USER] with grant option;

I can solved with it.


The total number of locks exceeds the lock table size, even after increasing buffer pool size I am trying to partition a table with 200 million records in it. This table already exists:

CREATE TABLE linkmap_ntoken (
  target_nToken varchar(200) NOT NULL,
  ntoken_hashcode int(15) NOT NULL,
  targeturl varchar(2000) NOT NULL,
  targetbreadcrumb varchar(400) NOT NULL,
  sourceurl varchar(2000) NOT NULL,
  sourcebreadcrumb varchar(400) NOT NULL
 ) ENGINE=InnoDB.

Now I am trying to create 500 partition with this statement:

ALTER TABLE linkmap_ntoken partition by HASH (ntoken_hashcode) partitions 500;

It throws exception: total number of locks exceeded the lock table size.

I read few suggestions online and changed my.cnf file. Here are the configurations:

# PER CLIENT SETTINGS #
# bit high but I got tons of ram here #
sort_buffer_size                = 2M
read_buffer_size                = 2M
binlog_cache_size               = 1M
wait_timeout                    = 31536000
interactive_timeout             = 300
max_allowed_packet              = 12M
thread_stack                    = 128K
table_cache                     = 1024
myisam_sort_buffer_size         = 1M
tmp_table_size                  = 12M
max_heap_table_size             = 12M

# LOGGING #
log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = /var/lib/mysql/slowquery.log

# MyISAM #
key_buffer_size                = 32M
myisam_recover                 = FORCE,BACKUP

# SAFETY #
max_allowed_packet             = 16M
max_connect_errors             = 1000000

# BINARY LOGGING #
log_bin                        = /var/lib/mysql/mysql-bin
expire_logs_days               = 14
sync_binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 40G

I have restarted mysql after making these changes. Still it throws the exception. My server has 12 cores and 48GB Ram and I can dedicate this server for this partitioning process for time being. Are there any suggestions to bypass this exception (total number of locks exceeded)?

When I did show global variables, it is showing buffer pool size as 8388608. I changed innodb_buffer_pool_size under [mysqld] block and restarted. It doesn't seems to be affected. It is still 8388608.

Regarding "why partitioning", I do not have a unique column to have a primary key. Even If I add one I won't be able to query with it. I am using this table to query for an online application. I want my query to return results in less than a second.

Try this in my.cnf file,

[mysqld]
innodb_buffer_pool_size = 80M

And you need to restart the Workbench as well as the Sql service.


SQL Server Deadlock between two selects and one update on one table Our application encounters a deadlock every now and again (about once a week).

The main culprit seems to be a query with two selects. One of them is to fill a temp table for performance reasons, the other is a relatively complex select with many joins to return the list of all Appointments with many details. The only potentially special thing I see about the second select is that it includes a self-join. The two-select-query is always part of the deadlock event report by SQL Server.

The other query is a simple DML query (insert or update) on the same table, though this is not always the same DML query. Both queries run with standard READ COMMITTED isolation and not within a explicit transaction.

The two queries are roughly as follows (I've shortened them for clarification)

DECLARE @futureAppointments TABLE(clientId int, StartDate date)
INSERT INTO @futureAppointments SELECT clientId, StartDate FROM Appointments where StartDate >= @startDate

SELECT *, (SELECT COUNT(*) FROM @futureAppointments fa WHERE fa.clientId = a.clientId AND fa.StartDate > a.StartDate)
FROM Appointments a
join b on a.fk_b = b.id
join c on a.fk_c = c.id
join Appointments d on c.somefield = d.anotherfield
WHERE a.StartDate >= @startDate AND a.StartDate <= @endDate

UPDATE Appointments SET someField = @value WHERE id = @id

Example 2: deadlock2.xml,
Example 3: deadlock3.xml,

How would I try to prevent deadlocks from happening in this scenario? Also, does anyone know why the first statement with two selects would acquire a U lock on the selected table's PK as in Example 3? I don't think that it matters, but it seems strange.

When looking at the deadlock graph:

Update query - SPID 75

The update query locking (spid 75) is pretty straightforward, an X (exclusive) lock is requested on the key/row value, while that row is currently locked by the select query.

The update query also holds an IX (Intent exclusive) lock on a page, as is expected when taking an exclusive lock on the row  that belongs to the page. The IX lock is compatible with the IS (Intent shared) lock issued by the select query (as a result of the S lock on the row).

See: lock compatibility matrix

Select query - SPID 103

The unusual part of your deadlock is that the select query (spid 103) wants a S (shared) lock on both the row & the page that has the row data (and possibly other row data). Especially since lock escalation from row to page is not possible (row to table & page to table). A previous transaction holding the lock is also ruled out.

The explanation seems to be in the double access to the dbo.Appointments table.
Locks are taken twice and one of these table accesses will want the page lock, while the other one already aquired the rowlock.

The update fires in between these shared locks being aquired.

An example of locks requested / acquired in order

A S Lock on the key/row is taken by the first read access on the dbo.Appointments table as part of the select query
An IX Lock is taken on the page that has the row data by the update query
X lock is requested on the row by the update query
The S lock on the page that also has the row data is requested
by the second access to dbo.Appointments as part of the select query

All this means that this part of the select query is your deadlock problem:

SELECT *, (SELECT COUNT(*) FROM @futureAppointments fa WHERE fa.clientId = a.clientId AND fa.StartDate > a.StartDate)
FROM Appointments a
join b on a.fk_b = b.id
join c on a.fk_c = c.id
join Appointments d on c.somefield = d.anotherfield
WHERE a.StartDate >= @startDate AND a.StartDate <= @endDate;

  How would I try to prevent deadlocks from happening in this scenario?

Reduce

To reduce the probability of the deadlock happening you could look into optimizing the select query by rewriting or adding indexes.

Remove

To remove the possibility of deadlocks between these two queries entirely you could split the self join in two parts by using a temp table, or take a shared page or table lock on the Appointments table by using WTIH(PAGLOCK) / WITH(TABLOCK). Remember that this impacts concurrency.

  Also, does anyone know why the first statement with two selects would
  acquire a U lock on the selected table's PK as in Example 3? I don't
  think that it matters, but it seems strange.

The owner (select query) holds the shared lock on the key.

An U (update) lock (coming from the update query) is compatible with a shared lock see: Lock Compatibility Matrix.

The update query tries to convert the U lock to an exclusive lock:

<waiter id="process24b7826bc28" mode="X" requestType="convert" />.

The info is correct when opening the XML with SentryOne Plan Explorer (free tool).


grant permission to SHOW FIELDS I create a user to make a dump twice a week  but when I try to execute mysqldump I have this error

mysqldump: Couldn't execute 'SHOW FIELDS FROM `contabilidad_amortizaciones_completas_por_id_vista`': execute command denied to user 'dump'@'%' for routine 'bbbdd57_antes_del_cambio.calcula_dotacion_acumulada_fc' (1370)

The grants to this user are these

GRANT SELECT, SHOW DATABASES, LOCK TABLES, EVENT ON *.* TO 'dump'@'%'

The funcion call 'bbbdd57_antes_del_cambio.calcula_dotacion_acumulada_fc' have this code:

CREATE DEFINER = 'root'@'%'
FUNCTION
bbbdd57_antes_del_cambio.calcula_dotacion_acumulada_fc(_id_amortizacion int)
RETURNS decimal(10,2)
  BEGIN
    SET @resultado = 0;
    ( SELECT SUM(R.acumulado) FROM (
             SELECT MAX(`cadm`.`acumulado`) AS `acumulado`,
              cadm.id_amortizacion
              FROM `contabilidad_amortizaciones_detalles_meses` `cadm`
              WHERE ((`cadm`.`id_amortizacion` = _id_amortizacion)
              AND (`cadm`.`contabilizado` = 1))
              GROUP BY `cadm`.`anio_detalle`, `cadm`.`contabilizado`
            ) AS R
         GROUP BY R.id_amortizacion INTO @resultado
    );
 RETURN @resultado;
 END

I have other instances with other users-dump with this permissions and I dont have any problem.

Thanks

mysqldump

will dump the CREATE VIEW.  However, it shows it twice.  See the second version for the correct output.

Including the option --no-data is optional.

You do need SHOW VIEW permissions to when using mysqldump or SHOW CREATE VIEW.


Re-designing a Database cost and advice The web application I am working with has a huge Database that is a real mess, no foreign keys, no constraints, no indexes, no optimization, no normalization, nothing. Until now, the logic has been: If you need a field, add it, if you need a table, create it as fast as you can. At the beginning, I couldn't understand the severity of these mistakes, but having to deal all the time with obstacles created from a bad database, has made me decide to re-design the entire database. What i would need to know is:

Where should i start, normalization, adding keys and indexes, or anywhere else?
Since the cost of these changes will be huge for my work (I will have to redesign the entire code from scratch to adapt it), is it worth? I mean, what are the cons and pros of such a huge thing (I say huge, because I am a junior level in database matter). If anyone could guide me, would be awesome

Generally speaking when you want to fix a big-ball-of-mud architecture it is best to focus on one small aspect that you can fix relatively quickly with maximum payoff. What that thing is will vary depending on the system. Once you identify that thing then implement it and look for an opportunity to fix something else.  Over time your big-ball-of-mud will get more and more under control.  This is assuming that the rest of the team is on board with fixing the mess you're in.  If they aren't committed to moving away from the status quo then you'll be fighting a battle you can't hope to win.
Adding a few indexes seems like a quick and easy win, so that will probably be a good place to start, but YMMV.
In parting keep in mind what Warren P mentioned in a comment:

A mess that was made over several years is seldom less than several years work to clean up.

You and the rest of the team need to be committed to this clean-up over the long term if there is any hope for success.

Responses to OP's comments:

Wouldn't it be better to start with database normalization? I mean in a development environment, in order to end up with a fully structured database? There are like 6 tables which HAVE TO be separated, in order to be more "elastic" for future requests.

Normalization is likely to touch many queries/statements across the system. That is a very intrusive change that will take lots of time and effort to show a benefit. Making small incremental steps will be easier, quicker, and steadily improve your life. Before considering normalizing I would move all data access into stored procedures. That will give you tangible benefits quickly, allow you to spread out the work, and make the normalization step easier.

[T]he entire team is ready, and we are talking about a 6 month - 9 month project of restructuring everything, the big bosses are still undecided however.

Read this post by Joel Spolsky before advocating stopping everything and pursuing the grand redesign. Blocking updates to the existing system while you restructure the old system over the next 6 to 9 months makes it hard for the business to succeed. Supporting the current product while concurrently improving the same code base is the smartest play IMO (in my opinion). In this way the code base improvements will just be a symbiotic extension of supporting the current product.
Note: I'm not suggesting you have one team doing a rewrite (version 2.0) while another is supporting the current system (version 1.x).


Is it okay for me to truncate the postgresql serverlog file? I am running PostgreSQL 13.0 on RedHat, 64-bit but my serverlog file has grown so big (29 GB).
I would like to know if its safe to clear contents for this file by using > serverlog or any other command that will clear all the contents in the file.
I looked around and could not find any documentation on whether its safe to do this. Most documentation talks of changing the logging strategy or configs but I do not have that level of access to achieve the configuration changes. Sample advice - Can an active Postgres serverlog be truncated?
Current space utilization for the serverlog file
-rw-r--r--  1 postgres postgres  29G Dec 10 14:16 serverlog

Truncating the log file with
> logfile

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


DATEADD and DATEPART with milliseconds yields strange results I have a table containing the date in unix time and a separate milliseconds field. I now try to create a date from the two fields for later calculation (e.g. filtering on a time range). After adding the milliseconds to the date created via ...
 dateadd(S, [timestamp_s], '1970-01-01')

by adding another DATEADD ...
dateadd(MS, [timestamp_ms], dateadd(S, [timestamp_s], '1970-01-01')) eventdate

... and then output the date the milliseconds are sometimes one millisecond off. Out of curiousity I then tried to extract the milliseconds just to see what this gives and its again 1 millisecond off.

I think it has to do with internal floating point precision but I don't see any rule in the data. Sometimes each operation takes 1 MS off, sometimes the first will subtract 1 but the DATEPART will then add 1 again, etc.
As this might cause frustration with some of the users I would like to understand the behaviour and ideally find a solution to the problem. Thanks in advance.

This is due to a strange limitation1 in the accuracy of the datetime data type, as documented:

Accuracy | Rounded to increments of .000, .003, or .007 seconds

The solution would be to use datetime2, which provides better accuracy, as you can see in this dbfiddle demo:

The return type of the DATEADD is dynamic based on what you send into it.  So the important thing is to make sure you are passing datetime2 to the step where you add the milliseconds on.

1 Randolph West has a great blog series on how SQL Server data types are stored, including one on dates and times (How SQL Server stores data types: dates and times).  That post has a useful comment from Data Platform MVP Jeff Moden:

The time portion of DATETIME is actually an integer count of the number of 1/300’s of a second that have passed since midnight. That translates to a 3.3 millisecond resolution but that level of resolution isn’t possible for the way the data is stored so 0.0 and 3.3 ms are rounded down to 0ms and 3ms and 6.6 milliseconds is rounded up to 7 milliseconds.


Cannot connect to server. Too many symbolic links. Postgres Error I get this error when I try connecting to a PostgreSQL server:

psql: could not connect to server: Too many levels of symbolic links
    Is the server running locally and accepting
    connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

What can I do to fix this?

I have Postgres installed on my Mac.

Check what is /var/pgsql_socket/.s.PGSQL.5432. And if it is symlink, what it points to.


Restore System Databases from MDF/LOG files We recently experienced a hardware failure that caused our SQL 2005 server to take it in the dump.  The good news is that we have the MDF and LOG files for all the databases, the bad news is that we did not have .bak files for the system databases.

Is there any way to restore the System Databases (msdb, master, model) from the MDF/LOG files, or is there a process I can follow to create a bak file that I can then restore them from?

We have quite a few configuration items that were lost in the recovery.  I have been working on getting the mission critical application stuff back in working order and now I'm trying to restore all of our jobs and such.

Any help is greatly appreciated.

msdb and model you may be able to recover data from by following the same process as you would if moving them. Use a test/dev instance for a dry run.

Stop SQL
Start with trace flag 3608 (master only recovery mode) NET START MSSQLSERVER /f /T3608
ALTER DATABASE msdb|model MODIFY FILE( NAME=xyz, FILENAME='filepath')
Stop SQL, move files in to place, restart SQL

KB914171 details one possible error that may be reported and a fix/workaround by:

use msdb
go
delete from msdb.dbo.syssubsystems
exec msdb.dbo.sp_verify_subsystems 1
go


Select all rows from a table where a value is NOT found? I have 3 tables:

ve_survey_questions

Qid | Question | CategoryId

ve_survey_answers

AnswerID | QuestionId | Answer

ve_survey_useranswers

id | UserId | QuestionId | AnswerId | Datetime

This is a multiple choice test. So ve_s_Q is all possible questions, ve_s_A is the possible multiple-choice answers, and ve_s_useranswers links the answer chosen by that particular user.

I'm trying to display every question that the user has NOT yet answered with a two step query:

    //check user answers
     $surveyanswerQ = "SELECT * FROM ve_survey_useranswers ORDER BY QuestionId";
     $surveyanswerResult = mysqli_query($db, $surveyanswerQ);
     $surveyanswer=  mysqli_fetch_assoc($surveyanswerResult);

     //select all unanswered questions
     $surveyQ = "SELECT * FROM ve_survey_questions q
                          JOIN ve_survey_answers a
                          ON q.Qid=a.QuestionId
                          WHERE q.Qid <> {$surveyanswer['QuestionId']}
                          ORDER BY a.QuestionId LIMIT 4 ";
     $surveyResult = mysqli_query($db, $surveyQ);

This does not work as supposed to... How can I write a better query?

So if I'm understanding the schema right, this should work with a LEFT OUTER JOIN with a subquery:

SELECT q.*, ua.UserId, ua.Answer
FROM ve_survey_questions q
LEFT JOIN (
     SELECT u.*, a.QuestionId, a.Answer
     FROM ve_survey_useranswers u
     INNER JOIN ve_survey_answers a ON u.AnswerId = a.AnswerID
     WHERE u.UserId = @userid
     ) AS ua
ON q.Qid = ua.QuestionId
WHERE ua.QuestionId IS NULL
ORDER BY q.Qid

So the subquery returns a row for each answered question for the given user (along with the answer they selected), then we use an outer join to see what questions are still unanswered.

You can eliminate the WHERE ua.QuestionId IS NULL if you instead want the full question list for that user, along with their answer, if there is one.


Deploy SSRS 2012 report to a distant Sharepoint 2010 server My installation :
- SQL server 2008 r2 + Sharepoint server (main farm) = VM1
- SQL server 2012 + SSRS 2012 (not native mode) = VM2

I would like, if possible , make my SSRS configuration manager (VM2) to point to the report server database (VM1).

The problem is that as I installed SSRS in Sharepoint integrated mode (not native), the configuration manager opens and asks for a server and instance. Whatever I try to put in the server case, VM 1 (what I want ) or even VM2 , it can not establish the connection.

Machines ping each others.

Any ideas ?

Problem solved.

I think it came from the absence of an Active directory to manage users. With it, users are defined precisely and the installation runs perfectly.


Find a longest sequence that doesn't have duplicates inside I have database with Id - autoincrement, and Text- varchar.

My database is simple:

+---+--------+----+----+
| Id| Text   |Col1|Col2|
+---+--------+----+----+
| 1 | adf    |1   |0   |
| 2 | qwer   |5   |1   |
+---+--------+----+----+

This harder than I thought. What I want to do is to cut the table at some record and delete the tail.

Reason for this is that I had unique column that at some point in time I started receiving duplicates (because i changed key UNIQUE(Text) into UNIQUE(Text, Id))

Rob Farley's answer was what I asked originally in question but it wasn't what i really needed. I tried a revised version based on Rob's answer but results are still wrong:

SELECT MIN(Id) AS SmallestId, Text FROM table
GROUP BY Text HAVING COUNT(*) <2
ORDER BY SmallestId DESC

Problem with Rob's answer is that if first record (Id=1) is duplicate that lowest Id will equal 1 and i'd have to cut whole table.

Problem with my way is that it reports last (highest) occurrence of duplicate so I would have to leave some duplicates inside after the cut.

I think that what I want in human language is:

Find a longest sequence that doesn't have duplicates inside, starting from Id 1 and ordered by Id ASC.

It's a side project now so there is no pressure on answering, reason might be to learn more SQL. I'm the asker of original question but I had to edit as another because I accidentally created two accounts.

You want the smallest id for each Text that appears multiple times.

SELECT MIN(id) AS SmallestId, Text
FROM Table
GROUP BY Text
HAVING COUNT(*) > 1;

Edit:
Now that you've changed your question...

What about deleting later instances of the same text?

DELETE t
FROM Table t
WHERE EXISTS (SELECT *
    FROM Table t2
    WHERE t2.Text = t.Text
    AND t2.Id < t.Id);

This should leave you with just the smallest Id for each text. It doesn't answer what you're asking, but is hopefully closer to what you need.

If you have anything referring to this table, you will need to keep a copy of the rows you delete.


Why does a parameterised query lead to an index scan but hardcoded values leads to index seek? I'm getting significantly different performance from a query that uses parameters vs the same query where the same values are hardcoded in the where clause.

It comes down to 2 child tasks in the execution plan. The hardcoded query can perform an index seek but the parameterised query does an index scan. Since the table contains a million rows the difference is massive.

The query is pretty ugly...

DECLARE @MethodOfDelivery nvarchar(3) = 'AAA'
DECLARE @OutwardPostcode nvarchar(4) = 'BBB'
DECLARE @InwardPostcode nvarchar(3) = 'CCC'

SELECT TOP 1
    slr.*
FROM
    [dbo].[SortLevelRecord](NOLOCK) slr
    INNER JOIN dbo.RoutingDataVersion(NOLOCK) v ON v.Version = slr.Version
    INNER JOIN dbo.Routing(NOLOCK) psr ON
        (
            slr.SortLevelKey = psr.Day1SortLevelKey OR
            slr.SortLevelKey = psr.Day2SortLevelkey OR
            slr.SortLevelKey = psr.Day3SortLevelkey OR
            slr.SortLevelKey = psr.Day4SortLevelkey OR
            slr.SortLevelKey = psr.Day5SortLevelkey OR
            slr.SortLevelKey = psr.Day6SortLevelkey OR
            slr.SortLevelKey = psr.Day7SortLevelkey OR
            slr.SortLevelKey = psr.Day8SortLevelkey OR
            slr.SortLevelKey = psr.Day9SortLevelkey OR
            slr.SortLevelKey = psr.Day10SortLevelkey OR
            slr.SortLevelKey = psr.Day11SortLevelkey OR
            slr.SortLevelKey = psr.Day12SortLevelkey OR
            slr.SortLevelKey = psr.Day13SortLevelkey OR
            slr.SortLevelKey = psr.Day14SortLevelkey
        ) AND
        psr.Version = v.Version AND
        psr.MethodOfDelivery = slr.MethodOfDelivery AND
        psr.OutwardPostcode = @OutwardPostcode AND
        (
            psr.InwardPostcode = @InwardPostcode OR
            psr.InwardPostcode = SUBSTRING(@InwardPostcode, 1, 1) OR
            psr.InwardPostcode = ''
        )
WHERE
    slr.MethodOfDelivery = @MethodOfDelivery
ORDER BY
    LEN(psr.InwardPostcode) DESC

The index scan/seek is on the Routing table.

Something else that seems a bit strange is that the parameterised query uses Parallelism to gather streams but there are no parallel operations, only 1.

This is on Azure SQL server. I'm not sure what version that correlates to.

This is what is called parameter sniffing. What happens is when you execute a query, the underlying plan is stored and reused however that particular plan may not suit your variable. This is a normal and expected behavior in SQL Server. Because compiling queries is expensive and takes time, SQL server stores plan in the cache and as a DBA we want our SQL Server to re-use them as much as possible so that we can save our time and resources.

There is a very nice video from SQL Server SME Mr. Brent Ozar listed here which can give you full details on parameter sniffing, possible work-around and what should be done in order to avoid the same.

What you have asked in the question, almost same thing has been explained by Mr. Greg Larsen and you would get detailed info on variable and hard coding the values at this link.

You may also read details of parameter sniffing for azure on this link.

Few more interesting reads on the same topic:

https://www.sqlshack.com/query-optimization-techniques-in-sql-server-parameter-sniffing/
https://www.mssqltips.com/sqlservertip/3257/different-approaches-to-correct-sql-server-parameter-sniffing/
https://hackernoon.com/why-parameter-sniffing-hurts-your-sql-query-performance-d73c0da71fbc

I hope above helps.


Expired archived logs not deleted from RMAN catalogue If I issue a LIST EXPIRED command in RMAN, I see a list of files with expired status, that is, they exist in the catalogue, but not on the disk (verified by ls). After issuing CROSSCHECK and DELETE EXPIRED commands, I would expect LIST EXPIRED to return nothing... but they are all still here.

This may or may not be causing my backups to sporadically fail on ORA-19588. I have checked and am 100% certain that I am not running two RMANs at the same time. Any ideas? Target is 10g and catalogue is 11g.

Doing a LIST EXPIRED ARCHIVELOG and then a DELETE EXPIRED ARCHIVELOG should cause a new LIST EXPIRED ARCHIVELOG to not show any entries in most situations.  Here are a few situations that could cause this to not be the case.

If FORCE is not specified on the delete command then it follows the archived log deletion policy which determines when archived redo log files are eligible for deletion.
The deletion policy does not apply to foreign archived redo log files, which are logs received by a logical standby database for a LogMiner session.

These possibilities are from this documentation.  If neither is applicable then perhaps you are running into a bug and should open an SR with support.

I assume you are running commands like this:

   list expired archivelog all;
   crosscheck archivelog all;
   delete expired archivelog all;
   list expired archivelog all;

Perhaps the LIST EXPIRED entries are for items not being deleted by the specific delete command you are doing.  For example, a DELETE EXPIRED BACKUP will not delete expired archivelogs or expired copies.

Since the problem is sporadic, you shouldn't be having the problem referenced in MOS Doc ID 1189883.1 in which the backup command follows the form backup archivelog all archivelog...


Should I worry for this number of Aborted clients & connects? I am getting below number of Aborted clients & connects

mysqladmin ext | grep Abort

Which results as:

| Aborted_clients                               | 719          |
| Aborted_connects                              | 4458         |

One thing to note is, I am getting frequent "MySQL Server has gone away" error.
Not sure if above number of Aborted-** can cause the

  MySQL Server has gone away

Those are counters; they are useless without dividing by Uptime or Connections.  I would say something is wrong if

Aborted_clients / Uptime > 0.3 / second
Aborted_connects / Uptime > 1 / second
Aborted_clients / Connections > 30%
Aborted_connects / Connections > 8%

If you exceed any of these guidelines, look into

making sure that your clients do disconnect, and/or
increasing max_connections (but not too much), and/or
increasing wait_timeout

"gone away" comes from network glitches and/or trying to hang onto a connection "too long".  See the various %wait_timeout settings.


Patterns for Deleting large amounts of data So I'm looking at how to delete a large amount of data from a handful of tables that do not have the key I need to easily isolate the rows that need to be deleted.  The situation is as so:

I have a ID let's say a RequestID.  I've decided that all of an arbitrary RequestID's data entries are invalid and so I want to purge them out of my tables to clean up.

Table A is a dimensional table that has my RequestID and it also has all the associated URL IDs for any particular RequestID.

Table B also contains dimensional data but does not have RequestID, so I have to use Table A to look up which records in Table B are valid delete candidates.

These tables are anywhere from 1 million to a billion rows, so the deletes have to be batched to work properly.

My thought was to do something like this but it doesn't seem very performant:

WHILE EXISTS (SELECT TOP 1 1 FROM TableB JOIN TableA ON TableA.URLID = TableB.URLID)
BEGIN
DELETE TOP 50000 a
FROM TableB a
JOIN TableA ON TableB.URLID = TableA.URLID
WHERE TableA.RequestID = <some_value>
END

I'm not sure how better I could delete the data

Edit: sorry I forgot to include requestID in the delete code example

but you are missing RequestID
hopefully you have index on URLID  and RequestID

select 1
while (@@rowcount > 0)
BEGIN
  DELETE TOP (50000) b
  FROM TableB b
  JOIN TableA a
    ON b.URLID = a.URLID
   AND a.RequestID = @RequestID
END

Disable FK can help but be sure you are not breaking any FK


In SQL Server, is there a way to determine the values of the parameters passed to an executing stored procedure One way to determine the executing stored procedure is to use "dynamic management" methods, like so:

SELECT
    sqlText.Text, req.*
FROM
    sys.dm_exec_requests req
OUTER APPLY
    sys.dm_exec_sql_text(req.sql_handle) AS sqltext

However, this only displays the text of the stored procedure's create statement. e.g.:

CREATE PROCEDURE IMaProcedure @id int AS SELECT * FROM AllTheThings Where id = @id

Ideally I'd like to see what the parameters were for the running procedure that are causing it to run so long for the particular set of offending parameters.

Is there a way to do that?  (In this question Aaron Bertrand mentions DBCC InputBuffer, but I don't think that's appropriate for this problem.)

This information -- run-time parameter values passed into a Stored Procedure (i.e. RPC call) or parameterized query -- is only available via a SQL Trace (and I assume the equivalent Extended Event in the newer versions of SQL Server). You can see this by running SQL Server Profiler (it comes with SQL Server) and selecting the various "Completed" events, such as: RPC:Completed, SP:Completed, and SQL:BatchCompleted. You also need to select the "TextData" field as the values will be in there.

The difference between my answer and @Kin's answer on this Question is that @Kin's answer (unless I am mistaken, in which case I will remove this) focuses on getting either:

your own query plan (in which case it can have the runtime parameter info in it, but not for other Sessions/SPIDs), or
plans from the DMVs (in which case they should only have the compiled parameter values, which are not runtime values).

My answer focuses on getting the parameter values for other sessions that are currently running. When relying on the DMVs, there is no way to know if the runtime parameter value is the same as the compiled parameter value. And the context of this question is tracking down the runtime value of queries being submitted via other Sessions/SPIDs (and in SQL Server 2005, whereas Extended Events were introduced in SQL Server 2008).


Better Way of Releasing Massive Unused storage from Data Files to Server After we did massive truncate of data, there is a huge unused space in multiple databases data files, and I wonder what would be the best way of releasing it back. I know most of the experts are against it, I am not talking about single digit GB space, its more 56% unused space out of 800GB data files, and there are many more files in the server.

SQL Version - 2014 Standard ( this cannot be changed, and no more storage can be added)

I know the below options could work, but also potentially cause fragmentation.

DBCC SHRINK (causes massive fragmentation and uses back some space while update status).
Moving the Primary File Group table to Secondary File Group.
Adhoc shrink in Chunks (this also causes fragmentation).

Any suggestions above this and new ideas are greatly appreciated.

Paul Randal who led the storage engine team in SQL 2005 recommended moving the data to a different filegroup.  Personally I like this approach because it caused the least amount of free space left at the end of the process.  I believe this was in his old MCM training videos.

Also having our databases in the PRIMARY filegroup led to a potential disaster when the GAM/SGAM for the primary filegroup was corrupted due to all the writes on a bad IO subsystem.  Since the DB boot page is on the primary file group and other reasons, I now always keep the PRIMARY filegroup empty to limit corruption potential to it.  Also if you do piecemeal restore and you restore the Primary filegroup, your database is technically online reducing the errors you are spitting out while the rest of the DB is up.


When is the SQL Server database ready to accept queries? In the SQL Server error log file I found the following lines:

2018-02-22 14:10:58.95 spid17s     Starting up database 'msdb'.
2018-02-22 14:10:58.95 spid16s     Starting up database 'ReportServer'.
2018-02-22 14:10:58.95 spid18s     Starting up database 'ReportServerTempDB'.
2018-02-22 14:10:58.95 spid19s     Starting up database 'XYZ'.

If I check for the status of the database XYZ before this time, it is ONLINE using the following statement:

SELECT state_desc FROM sys.databases WHERE name='XYZ'

...but when I try to connect to this database using a C# application, it can't connect to the database.

The error is:

  Login failed for user 'asd'.
  Reason: Failed to open the explicitly specified database.

I tried three different users (Windows user, sa, SQL Server user defined for the application). The problem happens when I run the application in the start up of the OS, but if I start it manually after the start up, no errors happen, so I think all SQL Server settings and firewall settings are correct.

I also checked before this that the service status is running.

What else should I check to make sure the database is actually online and ready for queries?

I'm looking for a key that tells me it's ok to query the database, instead of delaying for a time (even not based on a clear reason).

I thought of scanning the error log for the text "Starting up database 'XYZ'", but this means I have to add a setting for the application for the path of the SQL Server error log. It also means reading the file many times until I find this phrase.

The SQL Server database is ready to accept queries as soon as:

SELECT DATABASEPROPERTYEX(N'database name', 'Collation')

does not return NULL.

From the documentation for DATABASEPROPERTYEX (Transact-SQL):

  Note: The ONLINE status may be returned while the database is being opened and is not yet recovered. To identify when a database can accept connections, query the Collation property of DATABASEPROPERTYEX. The database can accept connections when the database collation returns a non-null value. For Always On databases, query the database_state or database_state_desc columns of sys.dm_hadr_database_replica_states.


What is the different between compression on the PK vs. on the Table? Data compression can be set on the table:

CREATE TABLE dbo.SomeTable(
    SomeId [bigint] NOT NULL,
    OtherId [bigint] NOT NULL,
    IsActive [bit] NOT NULL,
 CONSTRAINT [PK_Some] PRIMARY KEY CLUSTERED
 (
    SomeId Desc
 )
) ON SomePartitionScheme(SomeId) WITH (DATA_COMPRESSION=PAGE)

And it can be defined on the Primary key:

CREATE TABLE dbo.SomeTable(
    SomeId [bigint] NOT NULL,
    OtherId [bigint] NOT NULL,
    IsActive [bit] NOT NULL,
 CONSTRAINT [PK_Some] PRIMARY KEY CLUSTERED
 (
    SomeId Desc
 ) WITH (DATA_COMPRESSION=PAGE)
) ON SomePartitionScheme(SomeId)

But if you put it on both then you get this error:

  The DATA_COMPRESSION option was specified more than once for the table, or for at least one of its partitions if the table is partitioned.

Is there any difference to putting it on the PK vs on the table?

It's not a matter of putting compression on a primary key, but instead a matter of putting the compression on the clustered index. For SQL Server, a clustered index is organizing the table's physical structure on that index. Or, in shorter form, the clustered index is the table. This means that compressing the clustered index and compressing the table are functionally equivalent. If you were to create your primary key as a non-clustered index and keep the base table as a heap, these two structures would be different and compressed separately.


Oracle optional select value I'm writing query to select students with their Program, Country and Teacher. So far, I have the following query:

SELECT H.STUDENT_CODE,
       H.FIRSTNAME,
       H.LASTNAME,
       H.PROGRAM,
       H.TEACHER_CODE,
       H.COUNTRY_CODE,
       AC.COUNTRY_NAME,
       P.PROGRAM_NAME,
       T.TEACHER_NAME
    FROM HTUD H,
         PROGRAM P,
         TEACHER T,
         AP_COUNTRY AC
    WHERE H.FIRSTNAME LIKE '%ark%'
        AND H.PROGRAM = P.PROGRAM
        AND H.TEACHER_CODE = T.TEACHER_CODE
        AND H.COUNTRY_CODE = AC.COUNTRY_CODE
    ORDER BY H.STUDENT_CODE ASC

These two columns H.TEACHER_CODE and H.COUNTRY_CODE have some empty values. If I run the query above, it returns only students that have data in these two columns H.TEACHER_CODE and H.COUNTRY_CODE.

However, I want to select all students wether they have data in these two columns or not. If they have data in H.TEACHER_CODE and H.COUNTRY_CODE column, then return Teacher and Country also, if they don't have, just return students without Teacher or Country.

So, how do I select all students with or without Teacher or Country?

You want to make an outer join. In Oracle 8i one has to use the (+) notation.

SELECT H.STUDENT_CODE,
   H.FIRSTNAME,
   H.LASTNAME,
   H.PROGRAM,
   H.TEACHER_CODE,
   H.COUNTRY_CODE,
   AC.COUNTRY_NAME,
   P.PROGRAM_NAME,
   T.TEACHER_NAME
FROM HTUD H,
     PROGRAM P,
     TEACHER T,
     AP_COUNTRY AC
WHERE H.FIRSTNAME LIKE '%ark%'
    AND H.PROGRAM = P.PROGRAM
    AND H.TEACHER_CODE = T.TEACHER_CODE(+)
    AND H.COUNTRY_CODE = AC.COUNTRY_CODE(+)
ORDER BY H.STUDENT_CODE ASC

Starting with Oracle 10g xou can use the ANSI join syntax,too

SELECT H.STUDENT_CODE,
   H.FIRSTNAME,
   H.LASTNAME,
   H.PROGRAM,
   H.TEACHER_CODE,
   H.COUNTRY_CODE,
   AC.COUNTRY_NAME,
   P.PROGRAM_NAME,
   T.TEACHER_NAME
FROM HTUD H JOIN PROGRAM P ON (H.PROGRAM = P.PROGRAM)
    LEFT OUTER JOIN TEACHER T ON (H.TEACHER_CODE = T.TEACHER_CODE)
    LEFT OUTER JOIN AP_COUNTRY AC ON (H.COUNTRY_CODE = AC.COUNTRY_CODE)
WHERE H.FIRSTNAME LIKE '%ark%'
ORDER BY H.STUDENT_CODE ASC


A transaction log grows unexpectedly I am trying to understand what process is making my transaction log file to grow. For example, currently it is 120 GB and only 5-10 MB of it are in use.

Before:

FULL recovery mode
a backup of the transaction log file is made each 5 minutes
AlwaysOn Availability Groups is enabled
the transaction log file size is 128 MB (only 1-5 percent of it are in used)

After several days:

The transaction log file size is 120 GB (again only few MB of it are in use)

I am trying to detect what is causing this huge grow. One of the possible reasons explained here is The database is participating in an AlwaysOn availability group.

I have try to find this particular error:

  Error: 9002, Severity: 17, State: 9.  The transaction log for database
  '%.*ls' is full due to 'AVAILABILITY_REPLICA'

But I have only errors for the transaction lot of the tempdb:

The transaction log for database 'tempdb' is full due to 'active_transaction'

Is there any message or error which will help me to understand when or why this is happening?

Is there any other way to detect the event?

As far as I read, I can only use DBCC OPENTRAN but it will give me current transaction which is opened which is useless for me as I do not know when the event happened.

You are just not catching it at the right times, there are lots of operations that will use the transaction log.

When do you do maintenance if any (defragment indexes, CHECKDB, etc.)?  These all use the transaction log.
Do you have any jobs that load/modify data at night?  These operations can use the transaction log as well.

As Jon Siegel suggested, use the disk usage report to get the times when it's filling up and correlate that with other activity.


Error converting data type nvarchar to float. Oracle Linked Server Upon executing the following query

select value, cast(value as float) float_value
from OPENQUERY([ORACLESERVER],
'select 3/5 as value from table')

I am getting the following error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to float

.

I can run this same query on another server with the same Windows 2012 R2 OS, same SQL Server 2008 R2 Server and identical Oracle 12.1.0 client version and get back the correct result :

value   float_value
.6  0,6

The linked servers on both servers are identically configured.

The Oracle Server version is

select * from OPENQUERY([ORACLESERVER],'SELECT * FROM V$VERSION')
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE   10.2.0.3.0   Production
TNS for HPUX: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 – Production

Are there any other configuration options I have overlooked in SQL Server and/or the Oracle Client that may be causing these issues.

It is in fact a configuration issue in the Oracle Client

Set NLS_LANG to AMERICAN_AMERICA.WE8MSWIN1252 and no more conversion error.

UPDATE

Why? Because 0.6 in French would be read as a character value whereas in English it remains numeric.  0,6 in French is 0.6 in English.

Defining NLS_LANG as American means that 0.6 stays numeric thus allowing the cast as float to occur without error.

Restart required.


How would you design a table for a booking system? I have a design database for a booking system. The booking system requirement are as follows:

The booking system allows user to book a time slot (1 hour) between 8 am to 1 pm for Monday to Friday.
The user can book a venue for that 1 hour time slot.
The booking system is open for 13 weeks, then rest for 13 weeks and open for another 13 weeks and rest for 13 weeks for each year.

The problem that I face when I design the table is that there are 3 important controlling variable - timeslot, venue and 13 weeks.

I have draft the design by having a table that contain a fixed amount of rows. (So, for a 8 am to 1 pm, there will be 5 slots, and thus each week will have 25 slots. Suppose there are 3 venue, then I will have 25 slots x 3 venue, which give me 75 rows. Then I multiply 75 to 26 weeks, because there are two 13 weeks that the system will be open. Thus, this will give me 1950 fixed rows.

But, the problem will be that if I increase the number of hours by ending it at 10pm, then I will have 14 slots per day, which mean that each week will have 70 slots. What if the venue increase to 10 venue, then, I will have 70 slots x 10 venue, which give me 700 rows. Of course, multiply it by 26 weeks, I will need to have a table that contain a 18,200 fixed rows. The design will be hard to manage since there are so many rows.

The table would look something like the following:

+----------------------------------------------------------------------+
|  ID      |   Week    |   Time   |   Venue   |   User   |    Status   |
+----------------------------------------------------------------------+
|  000001  |   1       |  8 am    |  Room 1   |          |  Available  |
|  000002  |   1       |  9 am    |  Room 1   |          |  Available  |
|  000003  |   1       |  10 am   |  Room 1   |          |  Available  |
|                                                                      |
|  018200  |   26      |  10 pm   |  Room 10  |          |  Booked     |
+----------------------------------------------------------------------+

The ID will be the PK for this table.

Is there a way that I can keep track of each booking slot but without creating a fixed amount of rows for each booking slot? (Each booking slot will contain 3 important data - the week, time, venue)

If the concern is manual maintenance of a large number of rows, you could solve this (potentially) by creating tables for each of your dimensions: weeks, time slots, and venues.  The complete set of possible slots for booking would be the cross product of these three dimensions. Actual bookings would be another table with foreign keys pointing to all three of these dimension tables.

With this type of design, instead of maintaining 3 x 25 x 26 records you will maintain 3 + 25 + 26 records.  Note that if you segregate day of week and time of day into two tables you can reduce the number of records to be maintained even further (3 + 5 + 5 + 26).

The problem with this approach is when (and if) you have an exception.  This design assumes that there are no blackouts in your schedule.  For example, what if you close a room for a couple of weeks to be renovated?  One way to handle this issue is to create booking records that cover the blackouts.  If you have enough exceptions, then managing them may be almost as bad as just using the brute force method.

The question I would seriously consider is whether or not generating the initial list of slots available for booking is really that big a deal.  You could easily automate the  process to generate your big pile of available slots.  This is really just a single query.


log_reuse_Wait_desc always return LOG_BACKUP even right after log backup We are using SQL Server 2012 standard Edition (SP4)- 11.0.700.0(x64) (Build 14393)(HYpervisor).

Data File Size = 171 GB
Log File Size = 106 GB

When I queried sys.databases to see the log_reuse_Wait_desc it is always LOG_BACKUP- even right after log backup!
When I run DBCC SQLPERF(logspace) I see "Log space Used %" is less than 1%.
DBCC loginfo returns 773 which is high.

FYI- This database is not involved in Log Shipping/replication/mirroring.  The recovery model for this DB is Full and log backups are taken every 30 minutes. I know log backups are happening as I checked from msdb.dbo.backupset.

This database had % autogrowth setting on log file until last week. I changed it as part of config change.

How do I find why the log_reuse_desc is returning LOG_BACKUP always?

I would like to explore some options to reduce the log file size on the disk. (I know it is a bad bad thing to trucate and shrink the log file, but at this point I would like to know my options available). Can someone please suggest a graceful way to do this.

When I queried sys.databases to see the log_reuse_Wait_desc it is always LOG_BACKUP- even right after log backup! how do I find why the log_reuse_desc is returning LOG_BACKUP always.

This is nicely explained by Paul Randal:

  Imagine a database where there’s very little insert/update/delete/DDL
  activity, so in between your regular log backups there are only a few
  log records generated, and they’re all in the same VLF. The next log
  backup runs, backing up those few log records, but it can’t clear the
  current VLF, so can’t clear log_reuse_wait_desc. As soon as there are
  enough changes in the database that the current VLF fills up and the
  next VLF is activated, the next log backup should be able to clear the
  previous VLF and then the log_reuse_wait_desc will revert to NOTHING.
  Until the next log backup occurs and isn’t able to clear the current
  VLF, in which case it will go back to LOG_BACKUP again.

  So LOG_BACKUP really means “either you need to take a log backup, or
  the log records that were backed up were all in the current VLF and so
  it could not be cleared.”

Full article: Why is log_reuse_wait_desc saying LOG_BACKUP after doing a log backup?

I would like to explore some options to reduce the log file size on the disk.

Before you shrink the log files it is also important for you to understand how you end up with too many or too less number of VLF's. I suggest you read the following posts. These will give you a better idea about VLF and transaction log management.

A Busy/Accidental DBA’s Guide to Managing VLFs by David Levy
How do you clear the SQL Server transaction log? by Aaron Bertrand
Aaron added 4 more links at the bottom of his answer, all those are very useful and related to your question.
Transaction Log VLFs – too many or too few? by Kimberly Tripp


T-SQL Optimizing a Join on TOP Value from another table I've got a data warehouse that goes through a full refresh each night that can take about an hour to process 16 million rows/25gigs of data and we're looking for ways to reduce this time without going the incremental approach.

The basic format of our queries is as below, only I've stripped out about 20 more joins and 30+ more columns that would also be included.  The stripped out columns and joins are very straightforward with no aggregation, subqueries, or other types of calculation involved.  What's left is the main fact table (First_Source_Table) and the most problematic datapoint to collect.  Second_Source_Table consists of many records for each Account_ID, but we only want to include the first record for each Account_ID.

Now my constraints.  This in a replicated environment on SQL Server 2008.  Unfortunately I have no control over the source tables, and while I can add new indexes on them, they will be lost the next day.  I've tried calculating an in-between table off of Second_Source_Table before I do the full-table, but as that would need to be re-calculated each night, it didn't have a material impact on the overall calculation time.

The code below works, but if you look at the execution plan and IO Stats, the logic associated with Second_Source_Table constitutes about 80% of all resources used, but changing this field to NULL only cuts execution time in half.  I'll also point out again that being a replicated environment, there are no issues to worry about with locking or other users writing to the tables we're in.

INSERT INTO
    New_Table
SELECT
    First_Source_Table.Account_ID,
    (
        select
            top 1
            Second_Source_Table.Code
        FROM
            Second_Source_Table
        WHERE
            Second_Source_Table.Account_ID = First_Source_Table.Account_ID
        ORDER BY
            Second_Source_Table.ID
    ) as Code
FROM
    First_Source_Table

You may want to consider partitioning instead of a scalar query.

So something like

insert into New_Table
    select
        [fst].Account_ID,
        [sst].Code
    from
        First_Source_Table as [fst]
            inner join (select
                            row_number()    over(
                                partition by Account_ID
                                order by Account_ID ) as [topN],
                            Account_ID,
                            Code
                        from
                            Second_Source_Table) as [sst]
            on     ( [sst].Account_ID = [fst].Account_ID )
    where
        ( [topN] = 1 ) --This is your topN query


Update column from Base64 to string (natively in SQL) I've been trying to figure this out for two days now and would really appreciate some help. I've imported data from a csv where one field contained html data encoded in base64.

The idea is to loop over every row and run FROM_BASE64 on it.

How do I structure a query that:

Loops over all lines
Calls FROM_BASE64 for each line
Runs UPDATE (or similar functionality) on that same row and column

I'm running MariaDB (MySQL equivalent).

Since you haven't given us any information about the table or column in question you'll have to update my example with the correct information.

update thetable set thecolumn = FROM_BASE64(thecolumn)


Running queries with SQLCMD vs. Running queries with SSMS I've noticed something which i'm not sure about. can it be that running queries directly with SSMS is much faster than running queries with SQLCMD? I've noticed that the results of queries that ran with SSMS are written much faster to the disk.

What are the differences?

Thanks.

The differences are probably due to SET settings, such as SET ARITHABORT. What happens is that these settings are taken into account when deciding on the plan to use, and sometimes you can get stuck with a really bad plan with one setting (and this probably happened because that application used non-optimal parameters), and the opposite setting has a good plan.

You can discover what settings are in play by checking sys.dm_exec_requests and sys.dm_exec_sessions when the query is running. The latter has columns for various settings (but you'll have to figure out the session_id from the former). Once you've discovered the differences, you can manually set the setting to the "better" setting from sqlcmd, and hope that you now get the better plan. Or, you can simply issue WITH RECOMPILE to avoid the parameter sniffing issue in the first place.

A really useful resource to read thoroughly is:

Slow in the Application, Fast in SSMS?


node is not in primary or recovering state My config file is:

systemLog:
    destination: file
    logAppend: true
    path: c:\data\log\mongod.log
storage:
    dbPath: c:\data\db
    journal:
        enabled: true
replication:
   replSetName: "rs0"
net:
   bindIp: 127.0.0.1
   port: 27017
security:
     authorization: enabled

So, I'm successfully connecting via mongod process:

Then connecting to mongo and trying to open databases and collections:

And getting an error:

2018-07-07T15:40:25.092+0300 E QUERY    [thread1] Error: error: {
        "operationTime" : Timestamp(0, 0),
        "ok" : 0,
        "errmsg" : "node is not in primary or recovering state",
        "code" : 13436,
        "codeName" : "NotMasterOrSecondary",
        "$clusterTime" : {
                "clusterTime" : Timestamp(0, 0),
                "signature" : {
                        "hash" : BinData(0,"FshG5mLBvAQUizPHXGfCITV4ZKA="),
                        "keyId" : NumberLong("6573732769795407873")
                }
        }
}

To avoid this error, I'm using command rs.initiate() like:

But what does this error mean? Thanks.

You've configured this node as a replica set member:

 replication:
     replSetName: "rs0"

On startup a new MongoDB server does not have a replica set configuration yet. The server will be in an
unknown replica set member state
until you either add this member to an existing replica set configuration or
use rs.initiate() to establish it as the first member of a new replica set.

You can confirm the current state using rs.status(). A server without any
configuration will report something similar to the following in MongoDB 4.0
(the exact output may vary by server version):

> rs.status()
{
    "operationTime" : Timestamp(0, 0),
    "ok" : 0,
    "errmsg" : "no replset config has been received",
    "code" : 94,
    "codeName" : "NotYetInitialized",
    "$clusterTime" : {
        "clusterTime" : Timestamp(0, 0),
        "signature" : {
            "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
            "keyId" : NumberLong(0)
        }
    }
}

Once added to a replica set, you can use rs.status() and rs.conf() to check the current replica set status and configuration respectively.


Single service account running all SQL Servers Is there any negative performance impact for using the same service account and agent account to run SQL Server and SQL Agent respectively for all SQL server instances running at a small company of say 35 servers? The biggest database is 0.5 GB. any suggestions are welcome. Thank you.

Is there any negative performance impact for using the same service account and agent account to run SQL Server and SQL Agent respectively for all SQL server instances running at a small company of say 35 servers?

No. Service accounts do not affect performance in any way. Its all about security !

From the SQL Server 2012 security best practice whitepaper (warning: word doc) :

  When choosing service accounts, consider the principle of least privilege. The service account should have exactly the privileges that it needs to do its job and no more privileges.

  You also need to consider account isolation; the service accounts should not only be different from one another, they should not be used by any other service on the same server.

If you are running Windows Server 2008 R2 & SQL Server 2012 and up, then best is to use

Virtual accounts or

  Virtual accounts are managed local accounts that is automatically provisioned and managed. In SQL Server 2012, they are the default service account specified during setup. It can access the network. A virtual service account has a well-known name in the form of NT SERVICE\ and can access the network using the credentials \$.

managed service accounts

  A managed service account is a special type of domain account that can be assigned to a single computer and used to manage a service. It must be provisioned by the domain administrator prior to being used. This type of account cannot be used to log in to a computer and provides automatic SPN and password management, once provisioned.


Migrate SELECT query from oracle to postgresql I want to migrate an oracle query to postgresql :

here is the oracle version :

SELECT
replace(replace(replace(
cf.NO_FACTURE||';'||
cf.MODULE||';'||
replace(replace(replace(cf.REF_FACTURE_SYBEL,'&',' '),'''',''''''),';','')||';'||
nvl(replace(to_char(cf.SOLDE_VERSE),',','.'),0)||';'||
cf.DATE_PAIEMENT_FACTURE||';'||
nvl(replace(to_char(cf.MONTANT_ACOMPTE_VERSE),',','.'),0)
, CHR(10),''),CHR(13),''),'*',' ')
        FROM
            (SELECT distinct td_inf.NO_FACTURE
             FROM tac_dossier td_inf,
                  com_agent ca,
                  com_habilitation ch
             WHERE td_inf.etat_dossier = 'id.15.ClosFacturation'
             AND   MONTHS_BETWEEN(sysdate,td_inf.date_chgt_etat) > $nb_mois_clos
             AND   td_inf.cod_ag_resp = ca.cod_ag(+)
             AND   ch.cod_ag = ca.cod_ag
             AND   ch.cod_ent in (select cod_ent from com_ent_ft where pole = td_inf.cod_ent or cod_ent = td_inf.cod_ent)) td,
             COM_FACTURE cf
        WHERE cf.NO_FACTURE = td.NO_FACTURE;

and here is my postgresql version of the query :

SELECT
replace(replace(replace(
cf.NO_FACTURE||';'||
cf.MODULE||';'||
replace(replace(replace(cf.REF_FACTURE_SYBEL,'&',' '),'''',''''''),';','')||';'||
COALESCE(replace(to_char(cf.SOLDE_VERSE),',','.'),'0')||';'||
cf.DATE_PAIEMENT_FACTURE||';'||
COALESCE(replace(to_char(cf.MONTANT_ACOMPTE_VERSE),',','.'),'0')
, CHR(10),''),CHR(13),''),'*',' ')
        FROM
            (SELECT distinct td_inf.NO_FACTURE
             FROM
                  tac_dossier td_inf
                  LEFT OUTER JOIN com_agent ca ON td_inf.cod_ag_resp = ca.cod_ag
                  INNER JOIN com_ent_ft cef ON (cef.pole = td_inf.cod_ent or cef.cod_ent = td_inf.cod_ent)
                  INNER JOIN com_habilitation ch ON (ch.cod_ag = ca.cod_ag and ch.cod_ent = cef.cod_ent)
             WHERE td_inf.etat_dossier = 'id.15.ClosFacturation'
             AND   EXTRACT(month from age(NOW(),td_inf.date_chgt_etat)) > 2
             ) td,
             COM_FACTURE cf
        WHERE cf.NO_FACTURE = td.NO_FACTURE;

The query is working there is no problem in the syntax But the results of the two queries are different.

Any note on this ?

Thank you.

This:

AND   EXTRACT(month from age(NOW(),td_inf.date_chgt_etat)) > 2

is something different then the original Oracle condition.

The extract functions does not get the equivalent of the interval in months, but simply the "month" field. So if the interval is "1 year  0 months 2 days 16 hours", extract(month from ...) will return 0.

What you want is:

and age(now(), td_inf.date_chgt_etat) > interval '2 months'

or if you want to make that use an index on the column:

and td_inf.date_chgt_etat < now() - interval '2 months'

Your query is also missing the

AND   ch.cod_ent in (...)

You have translated that to a JOIN which does something different. Whether or not that changes the result depends on how the two tables com_ent_ft and com_habilitation are related.


ISO Week vs SQL Server Week Okay so I have a report that does a this week vs last week comparison and our customer noticed that their data was "funky". Upon further investigation we found it was not doing weeks correctly according to the ISO standards. I ran this script as a test case.

SET DATEFIRST 1
SELECT DATEPART(WEEK, '3/26/13')
    , DATEPART(WEEK, '3/27/12')
    , DATEPART(WEEK, '3/20/12')
    , DATEPART(WEEK, '1/2/12')
SELECT DATEPART(ISO_WEEK, '3/26/13')
    , DATEPART(ISO_WEEK, '3/27/12')
    , DATEPART(ISO_WEEK, '3/20/12')
    , DATEPART(ISO_WEEK, '1/2/12')

When run I got these results.

I thought this was peculiar and so I did some more digging and found that SQL Server counts January 1st as the first week of the year where ISO counts the first Sunday in January as the first week of the year.

The question then ends up being two fold. Question 1 why is this? Question 2 is there any way to change this so I'm not having to modify all of my code to use ISO_Week everywhere?

Back when SQL Server first implemented the WEEK date/part, they had to make a choice. I don't think there was really much consciousness about it, except to align to the most common standard at the time - remember this was at a time where conforming to the standards was not a top priority (else we'd not have things like timestamp, IDENTITY and TOP). They later added ISO_WEEK (2008 I believe) because the workaround in the meantime was to write your own, slow, crappy scalar UDF - in fact they even created a really bad one and put it in the official documentation (it has since been removed as far as I can tell).

I don't know of a way to make DATEPART(WEEK pretend it is DATEPART(ISO_WEEK - I think you will have to change the code (and if you are using source control, this shouldn't be very hard - how many places are you performing this calculation? Have you thought about computing it somewhere so your code doesn't have to be riddled with it? Since you're changing the code now, this might be the time to consider this...).

And if you really want the answer to why? I think you'll have to grab some of the original developers to determine why they chose the default that they did. Again, I think it wasn't an actual "F the standards!" choice, but rather, "What standards?"

There is some information here that may be useful:

https://stackoverflow.com/questions/348880/getting-week-number-off-a-date-in-ms-sql-server-2005

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/iso-week-in-sql-server


Backing up SQL DB more frequently I currently have a scheduled task that fires off each night at 2 AM that calls SQLCMD.exe and passes it a .sql script to run for the backup (shown below).  We're a pretty small company with growing needs due to major growth on the business side.  Losing 1 days of data at this point would cost tens of thousands of dollars vs a couple hundred this time last year.  Until I can migrate this DB platform to a different solution where data mirroring occurs with major redundancy like SQL Azure, what is the best thing I can do to get more frequent backups?  Does this script below force the DB to be offline?  Can I run this script with users interacting with the DB?

USE CompanyCRM;
GO
BACKUP DATABASE CompanyCRM
TO DISK = 'D:\CRMBackups\CompanyCRMCRM.Bak'
   WITH FORMAT,
      MEDIANAME = 'CompanyCRM_Backup',
      NAME = 'Full Backup of CompanyCRM';
GO

Update

Wow, obviously a much more dedicated DBA community over here than on SO.  Thanks for the feedback so far.  Only thing missing is the "hows."  I have shown the SQL command above that I'm using to do daily backups, but the incremental log backup examples are MIA.  This is not a large DB, it currently runs on SQLExpress.  When I say HA or SQL Azure, I'm specifically referring to the architecture in place that we do not have as a small business.  This instance is currently running on our ONLY server.  If that server crashes, our time to recover becomes a sticking point.  This is why SQL Azure becomes attractive.

Backups in SQL Server are non disrupting. I.e. the database stays operational. Read the documentation.
Do a full backup every day, followed by shipped (copied off) LOG backups (again, the documentation has... documentation) more regularly - like every 15 minutes or so.


Index and rows placement within database files Where are the indexes and data tables stored within a physical database file or a page of a physical file :

Can there be different rows from different tables in the same
physical page ?
How does MySQL decide where to place a row in a
certain page?
What about indexes placement too ?

I know a little about that for Network Database (IDMSX database), with direct calculated placement on a physical page (and other records stored just near if possible).

Technically speaking, there is no single answer for "MySQL" itself, because MySQL has a pluggable storage engine architecture and the server itself is essentially unconcerned with and unaware of how the physical storage of data is accomplished.  Those details are handled by the storage engines.
The most relevant storage engine in MySQL is InnoDB.

Can there be different rows from different tables in the same physical page?

No, there can't.  In fact, tables do not typically even store tables in the same file, if you use the recommended configuration, where each table has its own separate tablespace file; however, even if innodb_file_per_table isn't enabled, pages aren't shared among tables.  By default the innodb_page_size is 16K bytes, although 8k and 4k sizes can be used, instead.

How does MySQL decide where to place a row in a certain page?
What about indexes placement too ?

There's only one place any particular row or index entry can go, because rows are stored sorted by the primary key columns and index entries are stored sorted by the indexed columns.

The pages that make up each table are arranged in a tree data structure called a B-tree index. Table data and secondary indexes both use this type of structure. The B-tree index that represents an entire table is known as the clustered index, which is organized according to the primary key columns. The nodes of the index data structure contain the values of all the columns in that row (for the clustered index) or the index columns and the primary key columns (for secondary indexes).
— http://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-overview.html

Row data that doesn't fit the page, int he case of BLOB AND sometimes VARCHAR columns are stored outside the b-tree page, in an overflow page, referenced by a pointer to the off-page location.  The column may be stored fully off-page or partially (so that some number of prefix characters are available on-page to reduce the need for off-page accesses in some cases).  This behavior varies by row format and whether you are using internal table compression.  Table compression can improve performance when your system is I/O-bound but has available CPU cycles, since it reduces the overall disk I/O.

If index records are inserted in a sequential order (ascending or descending), the resulting index pages are about 15/16 full. If records are inserted in a random order, the pages are from 1/2 to 15/16 full. If the fill factor of an index page drops below 1/2, InnoDB tries to contract the index tree to free the page.
— http://dev.mysql.com/doc/refman/5.6/en/innodb-physical-structure.html

Of course, when new data is inserted, the entire contents of the tablespace aren't "moved down" to make room for additional pages.  The pages are linked, so how consecutive on disk the pages are stored relative to the internal ordering on each page depends on whether the data was inserted sequentially or randomly.
Significantly, the InnoDB buffer pool makes the underlying storage implementation less relevant -- as it should be, in an RDBMS -- less relevant, by providing an intermediate buffer between the physical storage and the higher layers.  This buffer is a fixed amount of memory, allocated at server daemon startup, that makes the physical disk storage less significant.

InnoDB maintains a storage area called the buffer pool for caching data and indexes in memory. Knowing how the InnoDB buffer pool works, and taking advantage of it to keep frequently accessed data in memory, is an important aspect of MySQL tuning.
The larger the buffer pool, the more InnoDB acts like an in-memory database, reading data from disk once and then accessing the data from memory during subsequent reads. The buffer pool even caches data changed by insert and update operations, so that disk writes can be grouped together for better performance.

The pool insert/update operations are, of course, committed to disk immediately, but they are not written directly to their place on disk in the pages where they are stored.  The changes to be written are logged (using a sequential disk write) and updated in-memory, on-page, in the InnoDB buffer pool, and then the pages that were changed in the buffer pool are flushed (written out) to their place in the tablespace file by a background thread.