Finding contiguous ranges in grouped data I have a table that records the data of a car race with the following structure:

ID int, (id of car)
Duration timestamp, (time taken to drive)
Tour int,(no of laps made)
Pilot int,(drivers)

where Duration is in time and tour=no of laps which consists of values from 0 to 3000 and pilot has two values 2,3 (2 drivers with id 2 and 3)
The table shows the no of laps done within given time for each pilot.
The pilots drive the car within the time.Assume pilot 2 has drove the car first next pilot 3 and so on..

Now,I want total tour(difference) and total duree(difference) done by pilot having an id value 2 ?

SAMPLE DATA:

ID|DURATION|TOUR| PILOT
1 |15:00:01|216 |  2 \
2 |15:00:02|216 |  2 /   1 sec.
3 |15:00:03|217 |  7
4 |15:00:04|218 |  7
5 |15:00:05|219 |  7
6 |15:00:06|220 |  2 \
7 |15:00:07|221 |  2 /   1 sec.
8 |15:00:08|222 |  7
9 |15:00:09|222 |  7
10|15:00:10|223 |  2 \
11|15:00:11|224 |  2  |
12|15:00:12|225 |  2 /   2 sec.

Now I need the desired result:

For pilot id = 2, I need the tour difference along with duree(total)

Ans: 219 i.e 216 + (221-220) + (225-223)

First tour ID: 216 + (Dif of laps of each group) = 216 + 1 + 2 = 219

And duree(total) = Sum(Dif time of each group) = 00:00:04

I've set up the next sample on rextester:

DROP TABLE IF EXISTS race;
CREATE TABLE IF NOT EXISTS race (ID int, DURATION time, TOUR int, PILOT int);
INSERT INTO race VALUES (1 , '15:00:01', 216, 2);
INSERT INTO race VALUES (2 , '15:00:02', 216, 2);
INSERT INTO race VALUES (3 , '15:00:03', 217, 7);
INSERT INTO race VALUES (4 , '15:00:04', 218, 7);
INSERT INTO race VALUES (5 , '15:00:05', 219, 7);
INSERT INTO race VALUES (6 , '15:00:06', 220, 2);
INSERT INTO race VALUES (7 , '15:00:07', 221, 2);
INSERT INTO race VALUES (8 , '15:00:08', 222, 7);
INSERT INTO race VALUES (9 , '15:00:09', 222, 7);
INSERT INTO race VALUES (10, '15:00:10', 223, 2);
INSERT INTO race VALUES (11, '15:00:11', 224, 2);
INSERT INTO race VALUES (12, '15:00:12', 225, 2);

First it flags involved groups:

| ID | DURATION | TOUR | PILOT | GRP |
|----|----------|------|-------|-----|
| 1  | 15:00:01 | 216  | 2     | 1   |
| 2  | 15:00:02 | 216  | 2     | 1   |
| 6  | 15:00:06 | 220  | 2     | 2   |
| 7  | 15:00:07 | 221  | 2     | 2   |
| 10 | 15:00:10 | 223  | 2     | 3   |
| 11 | 15:00:11 | 224  | 2     | 3   |
| 12 | 15:00:12 | 225  | 2     | 3   |

Then calculates time difference of each group:

| DUREE | DIF_TOUR | MIN_TOUR |
|-------|----------|----------|
| 1     | 0        | 216      |
| 1     | 1        | 220      |
| 2     | 2        | 223      |

And finally returns aggregated values:

SELECT SUM(DUREE) DUREE, MIN(MIN_TOUR) + SUM(DIF_TOUR) TOURS
FROM
     (SELECT MAX(DURATION) - MIN(DURATION) AS DUREE,
             MAX(TOUR) - MIN(TOUR) AS DIF_TOUR,
             MIN(TOUR) AS MIN_TOUR
      FROM (SELECT ID, DURATION, TOUR, PILOT,
                   CASE WHEN TOUR <> @LTOUR AND TOUR <> @LTOUR + 1
                        THEN @GRP := @GRP + 1 ELSE @GRP END AS GRP,
                   @LTOUR := TOUR
            FROM   (SELECT @GRP := 0, @LTOUR := 0) X,
                   (SELECT ID, DURATION, TOUR, PILOT
                    FROM race
                    WHERE PILOT = 2
                    ORDER BY PILOT, TOUR) Y) Z
      GROUP BY GRP) W;

| DUREE | TOURS |
|-------|-------|
| 4     | 219   |

Rextester here

Duration have to be called timestamp.

SELECT MAX(duration) - MIN(duration) AS timediff
     , MAX(tour) - MIN(tour) AS tourdiff
  FROM table
 WHERE pilot = 2
 GROUP BY pilot
;


How check that the Oracle Database is up? For our application we have a configuration file for connecting to the Oracle database (URL, username, password).

The application takes 2-5 minutes to start.  If there is something wrong with database I see this in logs, but I have to wait 2-5 minutes. This is a lot of time.

So I want to find out quickly and simply a way to determine that the database is all OK and online.

Do you have any idea how I might do it ?

If you run the following query:

SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS FROM V$INSTANCE;

It should return:

INSTANCE_NAME    STATUS       DATABASE_STATUS
---------------- ------------ -----------------
YOUR_DBASE       OPEN         ACTIVE

To check general database status, I recommend:

Check if database processes are running. For example, from a Unix shell, running:

$ ps -ef | grep pmon

will show the running databases, startup times, and which Oracle users started each one.
Check if listeners are running using $ ps -ef | grep tns and $ lsnrctl status LISTENER
Selecting gv$resource_limit will show if database reached some configured limit.
To check if there were any database errors, you should check alert.log.

For more details see my blog post.


Get rows changed between two dates (latest and previous) I have a table _sync_history with this data:

+----+----------------+---------------------+
| id | object_name    | sync_time           |
+----+----------------+---------------------+
|  1 | territory      | 2015-07-21 22:29:02 |
|  2 | user           | 2015-07-21 22:29:19 |
|  3 | userterritory  | 2015-07-21 22:29:38 |
|  4 | account        | 2015-07-21 22:30:05 |
|  5 | accountshare   | 2015-07-21 22:39:04 |
|  6 | address_vod__c | 2015-07-22 00:47:38 |
|  7 | group          | 2015-07-22 01:23:30 |
|  8 | recordtype     | 2015-07-22 01:23:51 |
|  9 | user           | 2015-07-23 13:04:20 |
| 10 | account        | 2015-07-23 13:04:59 |
+----+----------------+---------------------+

I want to get the rows on user between 2015-07-21 22:29:19 and 2015-07-23 13:04:20 meaning the rows between last sync_time and previous sync_time for a given object_name. I was doing this:

SELECT * FROM `user` WHERE lastmodifieddate > (SELECT sync_time FROM `_sync_history` WHERE object_name='user' ORDER BY sync_time ASC LIMIT 1)

But that will give me just the ones from last sync_time and above which is not right, how do I get the ones I need?

You need to find both times, one using what you have (LIMIT 1) and the second using LIMIT 1 OFFSET 1.

I'd also use ORDER BY ... DESC, not ASC. Your wording suggests you want the rows between the last 2 appearnces of 'user':

SELECT *
FROM user
WHERE lastmodifieddate >
        (SELECT sync_time
         FROM _sync_history
         WHERE object_name = 'user'
         ORDER BY sync_time DESC
         LIMIT 1 OFFSET 1
        )
  AND lastmodifieddate <
        (SELECT sync_time
         FROM _sync_history
         WHERE object_name = 'user'
         ORDER BY sync_time DESC
         LIMIT 1 OFFSET 0
        ) ;

From what I gather I would do:

SELECT *
FROM user
WHERE lastmodifieddate IN ( SELECT min(sync_time), max(sync_time)
                            FROM _sync_history
                            WHERE object_name= "user" );


Log in as another SQL Server user via SSMS to check their permissions? I am getting a little further into the database administration side of things and starting to set up security access to SQL server databases for users. I have a question:

Once I have given a set of permissions to an AD authenticated user via SSMS, is there a way I can 'log in' as that user to the SQL server to check and confirm that I've given them the correct privileges? I'm also logged in as an AD user with my own permissions so I wouldn't be able to log in as their AD account ID/password.

Thanks :)

You can run SSMS as a different user via runas /netonly as described here.

However for that method you would need to know the other user's password.

You can also use EXECUTE AS to impersonate that user from inside a query window logged in as you (as long as you have the required permissions to do so).

EXECUTE AS 'user_name';

/* Some SQL Statements */

REVERT;

Or you could adopt a policy of never granting permissions to users directly but always granting them to Windows groups or database roles. Then you just need to have another dummy user that you can grant the same role membership to.

To do my process you need AD username and password. Keep'em handy.
1) Click on Start->All Programs->Microsoft SQL Server XXXX (Here XXXX is the Version of SQL Server you have).
2) Hold the Shift button and Right-Click on the application "SQL Server Management Studio".
3) Click on Run as different user.
4) Provide the AD username & password.


DB2 removing white spaces in field values In a table I have a column called column_xml defined as CLOB(1000000). The fields in the column contain XML formatted information. My problem is that there are lots of white spaces in these fields that I need to get remove if there are more than 3 whitespaces in a row.

This is what the output looks like now

column_xml
--------------------------------------------------------------------------------
<values>
 <comment>test test test                               </comment>
 <rights></rights>
 <title>TEST MAN                                            </title>
</values>
--------------------------------------------------------------------------------

How can I remove all the white spaces before </comment> and </title> so it looks like this?

column_xml
--------------------------------------------------------------------------------
<values>
 <comment>test test test</comment>
 <rights></rights>
 <title>TEST MAN</title>
</values>
--------------------------------------------------------------------------------

If you define your XML document in a XML column, you can control how the whitespaces are stored.

For XML parsing, you can take a look a this page: http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.xml.doc/doc/c0023533.html

To define the XML Schema, and how the whitespaces should be treated: http://www.w3schools.com/schema/schema_facets.asp

However, you are using a LOB and you are not exploiting the hybrid motor of DB2.

I think this can be done with a simple replace:

UPDATE {table} set {field} = REPLACE(REPLACE(REPLACE({field}, '   ', ''), '  ', ''), ' </', '</')

With the input:

<values>
 <comment>test test test                               </comment>
 <rights></rights>
 <title>TEST MAN                                            </title>
</values>

Gives the result:

<values>
 <comment>test test test</comment>
 <rights></rights>
 <title>TEST MAN</title>
</values>


mysql: counting number of tickets which are open per day basis I have a table tabName with the following rows:

id(PK) .. Created(datetime)     Resolved(datetime) Status(new/resolved/open)
1          2015-05-10 00:00:00   1970-01-01 ..       open
2          2015-05-10 ..         1970-01-01 ..       new
3          2015-05-10            2015-05-12          resolved
4          2015-05-11 ..         1970-01-01 ..       open
5          2015-05-11 ..         1970-01-01 ..       new
6          2015-05-11            2015-05-11          resolved
7          2015-05-12 ..         1970-01-01 ..       open
8          2015-05-12 ..         1970-01-01 ..       new
9          2015-05-12            2015-05-12          resolved

NOTE:  1970-01-01 .. is default time for all the tickets which are still open.

I have to frame a query to get the count of tickets still open, date wise. Say like on 10 May I have 3 tickets opened. Noting that for id = 3, resolved date is not 10 but it's closed on 12 May.

I framed a query like

SELECT DATE_FORMAT(Created, '%d-%m') AS createdd, COUNT(1)
FROM tabName
WHERE DATE_FORMAT(Resolved, '%d-%m') > DATE_FORMAT(Created, '%d-%m')
      AND YEAR(Created) = '2015'
GROUP BY createdd;

The above query gets all the tickets whose Resolved date is greater than the date which is considered for the count. This may count newer tickets as well. And also if the older tickets which are not resolved yet is not counted.

Is there any other way to frame the required query? I have been juggling with this query from last 3 days. Not able to find out still.

Updated:
As suggested by Abhik, i have tried in sqlfiddle site.

Here is what i created:

CREATE TABLE test
   (idint,createddatetime,resolveddatetime, status varchar(15), PRIMARY KEY (id));
   INSERT INTO test
      (id,'created','resolved',status`)
VALUES
      (1,'2015-05-10 00:00:00','1970-01-01 00:00:00','open'),

  (2,'2015-05-10 00:00:00','1970-01-01 00:00:00','new'),

  (3,'2015-05-10 00:00:00','2015-05-12 00:00:00','resolved'),

  (4,'2015-05-11 00:00:00','1970-01-01 00:00:00','open'),

  (5,'2015-05-11 00:00:00','1970-01-01 00:00:00','new'),

  (6,'2015-05-11 00:00:00','2015-05-11 00:00:00','resolved'),

  (7,'2015-05-12 00:00:00','1970-01-01 00:00:00','open'),

  (8,'2015-05-12 00:00:00','1970-01-01 00:00:00','new'),

  (9,'2015-05-12 00:00:00','1970-01-01 00:00:00','open')

  ;
  `

When i run

`
select
created_date,
@open:= @open+open_ticket-resolved as open
from(
 select
 date_format(created,'%d-%m') as created_date ,

sum( case when status='open' then 1 else 0 end )+

sum( case when status='new' then 1 else 0 end )+

sum( case when status='resolved' and ( date(resolved) > date(created) or

date(resolved) = date(created)) then 1 else 0 end ) as open_ticket,

sum(case when status = 'resolved' and date(resolved) = date(created) then 1

else 0 end) as resolved

from test

where

YEAR(Created) = '2015'

group by created_date
)x,

(select @open:=0)r order by created_date ; `

I got following output

created_date   open
10-05   3
11-05   5
12-05   8

Here count of still open tickets for 12-05 must be 7. The above query includes ticket id "3" , which is closed on 12-05. So is there any way to exclude this? If i am able to get this right, then i will have full fledged query.

You can use conditional sum for this calculation something as

select
date_format(created,'%d-%m') as created_date ,
sum( case when status='open' then 1 else 0 end )+
sum( case when status='new' then 1 else 0 end )+
sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
from table_name
where
YEAR(Created) = '2015'
group by created_date ;

Here is a test case

mysql> select * from test ;
+------+---------------------+---------------------+----------+
| id   | created             | resolved            | status   |
+------+---------------------+---------------------+----------+
|    1 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | open     |
|    2 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | new      |
|    3 | 2015-05-10 00:00:00 | 2015-05-12 00:00:00 | resolved |
|    4 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | open     |
|    5 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | new      |
|    6 | 2015-05-11 00:00:00 | 2015-05-11 00:00:00 | resolved |
+------+---------------------+---------------------+----------+
6 rows in set (0.00 sec)

mysql> select
    -> date_format(created,'%d-%m') as created_date ,
    -> sum( case when status='open' then 1 else 0 end )+
    -> sum( case when status='new' then 1 else 0 end )+
    -> sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
    -> from test
    -> where
    -> YEAR(Created) = '2015'
    -> group by created_date ;
+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    2 |
+--------------+------+
2 rows in set (0.00 sec)

UPDATE
The count of open is a incremental as per the comment. So the following should do the trick

select
created_date,
@open:= @open+open_ticket-resolved as open
from(
 select
 date_format(created,'%d-%m') as created_date ,
 sum( case when status='open' then 1 else 0 end )+
 sum( case when status='new' then 1 else 0 end )+
 sum( case when status='resolved' and ( date(resolved) > date(created) or date(resolved) = date(created)) then 1 else 0 end ) as open_ticket,
 sum(case when status = 'resolved' and date(resolved) = date(created) then 1 else 0 end) as resolved
 from test
 where
 YEAR(Created) = '2015'
 group by created_date
)x,(select @open:=0)r order by created_date ;

From the above sample data it will have

 +--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
+--------------+------+

UPDATE:

Count the previous tickets which got resolved on a given date and deduct that from the current day open ticket, for that you need a left join something as

select
created_date,
@open:= @open+open_ticket-resolved_ticket as open
from(
 select
 date_format(t.created,'%d-%m') as created_date ,
 sum( case when t.status='open' then 1 else 0 end )+
 sum( case when t.status='new' then 1 else 0 end )+
 sum( case when t.status='resolved' and ( date(t.resolved) > date(created) or date(t.resolved) = date(created)) then 1 else 0 end ) as open_ticket,
 coalesce(tot,0) as resolved_ticket
 from test t left join(
    select count(*) as tot, date(resolved) as resolved from test where status='resolved' group by date(resolved)
 )x on date(x.resolved) = date(t.created)
 where
 YEAR(t.created) = '2015'
 group by created_date
)x,(select @open:=0)r order by created_date ;

OUTPUT :

+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
| 12-05        |    7 |
+--------------+------+


Oracle - Is a separate database more secure than using the same database and a separate schema We have very few developers and the ask from management is insane (computer vision, AI, IoT, etc.). Management wants to split the production database which uses Oracle Apex because the Apex application will be on the public internet and therefore security is an issue. Is a separate database more secure than using the same database and a separate schema when using the Oracle database? I hate REST and all the complications like authentication that comes with it, so I would much rather be on the same database and just do a straight SQL select. Can I just grant select on specific tables between two schemas and thereby keep sensitive financial data equally secure as when having it on a separate database and sharing data using REST or a slow database link?

Management wants to split the production database which uses Oracle Apex because the Apex application will be on the public internet and therefore security is an issue.

If Management don't want the database server that hosts the Apex application on the "public internet" then that's what they want.  That database server will move to somewhere "inside" the Firewall.

Is a separate database more secure than using the same database and a separate schema when using the Oracle database?

From an Information Security stand-point?  Almost certainly.
Machines tend to be compromised, not Applications.
If this database server were to be compromised then the attacker would presumably target the root account and thereby gain access to any other account on the machine.  If, like most other DBAs, you've allowed O/S authentication for your "oracle" account, then the whole database is vulnerable.
From a cross-application perspective?  Not if you've set up your security model properly.
You can (and should) set up Roles that grant specific permissions on specific objects and grant these Roles to the Application Accounts that need to use them.  In that way, Application A can access the tables in Schema B that it needs to use but nothing more.
REST and other API-based strategies move this into the Application layer, where Developers are move "comfortable", but even these still need access to database objects so the creation/granting of Roles still applies.

I am also in a very small team, are these aspects of the same production system?
Why is it that they want you to keep them separate?
If security is an issue I would not want to grant select on the other schema as that allows anyone connected to the database to read anything in the other schema, you might want to look into Oracle Rest Data Services, which also handles REST authentication etc for you. It also allows only certain SQL statements to be run. I'm not sure how familiar you are with it, but it's been an absolute lifesaver for me. Let's talk in the comments :)
In short, yes another database should make it more secure. Check out ORDS.


Win-Loss Record in SQL Server I'm building a database on Microsoft SQL Server to teach myself SQL. I am a big UFC fan so I'd like to build a database on UFC fighters.

I'm building two tables fight and fighter. The fighter table will have stats like weight, height, birthday, etc. as well as a column for wins, losses, draws, and NC (no contest). The fight table will have a winner_id column and a loser_id column as well as is_draw and is_nc columns.

How can I best create a link between the winner_id/loser_id columns from the fight table to the wins and losses columns for the fighter table?

There is one caveat. Usually MMA fighters will come to the UFC with experience in other organizations. So they will already have a record of wins and losses prior to the UFC. My database will only record UFC fights. So I can't have those previous wins and losses in the [fight] table. For that reason, I've decided to use triggers for now.

Question: is there a better way to do this other than triggers? I'm thinking setting the wins column = a base number + the number of times their fighter_id is listed in the winner_id column. But how would I do that? Would I need to use a view?

Here is the trigger I have created.

CREATE TRIGGER tr_fight_ResultUpdateFighter
ON fight
FOR INSERT
AS
BEGIN
    -- Win
    DECLARE @winner_id int
    DECLARE @loser_id int
    DECLARE @is_draw bit
    DECLARE @is_nc bit
    SELECT
        @winner_id = winner_id,
        @loser_id = loser_id,
        @is_draw = is_draw,
        @is_nc = is_nc
        FROM inserted

    --Wins
    UPDATE fighter
    SET wins = wins+1
    WHERE fighter_id = @winner_id

    --Loss
    UPDATE fighter
    SET losses = losses+1
    WHERE fighter_id = @loser_id

    --Draw
    UPDATE fighter
    SET draws = draws +
        CASE
            WHEN @is_draw = 1
            THEN 1
            ELSE 0
        END
    WHERE fighter_id = @winner_id
    AND fighter_id = @loser_id

    --NC
    UPDATE fighter
    SET nc = nc +
        CASE
            WHEN @is_nc = 1
            THEN 1
            ELSE 0
        END
    WHERE fighter_id = @winner_id
    AND fighter_id = @loser_id

END

I would start by determining the stats at run-time. Only if that turns out to be too expensive, I would denormalize the statistics:

select f1.fighter_id
     , count(case when f2.is_draw = 0 and f1.fighter_id = f2.winnner_id then 1 end) as #wins
     , count(case when f2.is_draw = 0 and f1.fighter_id = f2.looser_id then 1 end) as #loss
     , count(case when f2.is_draw = 1 and f1.fighter_id in (f2.winner_id, f2.looser_id) then 1 end) as #draws
     , ...
from fighter f1
join fights  f2
    on f1.fighter_id in (f2.winnner_id, f2.looser_id)
group by f1.fighter_id;

When it comes to trigger, for anything but a check, use caution. Some things in life are "wrong way go back" and triggers are definitively one of them!
Since what you need is a calculated column, I would say best go with a "View". As you've said:

  wins column = a base number + the number of times their fighter_id is listed in the winner_id column

View are more flexible, easier to spot and easier to maintain. Also, the trigger should insert a record twice per game: one for the loser and one for the winner. Too much work. A view like the one below could do better:

The code would look like this:

 SELECT COUNT (winner_id),  winner_id AS Fighter,  'win' as ResType
 FROM Results
 WHERE is_draw = 0
 GROUP BY winner_id
 UNION ALL
 SELECT COUNT (*),  loser_id as Fighter 'loss' as ResType
 FROM Results
 WHERE is_draw = 0
 GROUP BY loser_id
 UNION ALL
 SELECT SUM(CAST is_draw as INT)  winner_id,  "draw" as res_type
 FROM results
 GROUP BY winner_id

Now you have all the wins, loses grouped by the fighter_id.


How to get last inserted rows where a condition is satisfied table1:

esps
--------------
A
B
C
--------------

table2:

id      _name       esp
------------------------------
1       Ana        A
2       Ana        B
3       Mario      A
4       Jorge      A
5       Jorge      C
6       Ana        A
------------------------------

My query:

SELECT * FROM table2 where esp = 'A' group by _name having id = max(id);

Query result:

id      _name      esp
-----------------------------
3       Mario      A
4       Jorge      A
-----------------------------

Expected result:

id      _name      esp
-----------------------------
6       Ana        A
3       Mario      A
4       Jorge      A
-----------------------------

Any advice or sugestion will be appreciated. Mysql version 8.0.16

If you want the 'last' A:

SELECT * FROM table2
    where esp = 'A'
    ORDER BY id DESC
    LIMIT 1;

If you need something more complicated, see "groupwise-max".

Maybe that will work for you?

SELECT * FROM table2 where esp = 'A' group by _name,esp order by esp desc Limit 1

OR

SELECT * FROM table2 where id = (SELECT max(id) FROM table2 where esp = 'A' group by _name)

EDIT
This should work

SELECT * FROM table2 where esp = 'A' group by _name,esp order by esp desc


Can't connect to SQL Server I'm trying to set up Peer-to-Peer Transactional Replication and I'm stuck at "Add a New Peer Node" step - I can't connect to another server.

In server name field I specify serverIP\DBNODE2 and SQL Server credentials.

I'm 100% sure that the credentials are correct, but I'm not sure about the server name part.

When I try to connect I get the following error:

Any ideas what am I doing wrong?

EDIT: I've checked:

Started SQL Server Browser service
SQL Server service for MSSQLSERVER is running
Firewall is off
Remote connections are enabled in Configuration Manager
TCP ports are enabled

Server name is DBNODE2.
SQL Server instance name is MSSQLSERVER.

The only thing, that I don't understand why it's asking server name? This server is in remote geographical location, so I don't think, that it could find the right server without IP address.
I've tried bunch of different combinations and none of them worked:

IP\DBNODE2
IP\MSSQLSERVER
DBNODE2\MSSQLSERVER
IP
DBNODE2
Also tried double backslashes
Etc.

EDIT2:
SQL Server service is running under 1433 port.
I've used Port Query tool, which I've found here.

    =============================================
    Starting portqry.exe -n {IP} -e 1433 -p TCP ...
    IP address resolved to ec2-*******.compute-1.amazonaws.com
    TCP port 1433 (ms-sql-s service): LISTENING
    =============================================
    Starting portqry.exe -n {IP} -e 1434 -p UDP ...
    IP address resolved to ec2-*******.compute-1.amazonaws.com
    UDP port 1434 (ms-sql-m service): LISTENING or FILTERED
    Sending SQL Server query to UDP port 1434...
    Server's response:
    ServerName DBNODE2
    InstanceName MSSQLSERVER
    IsClustered No
    Version 11.0.3000.0
    tcp 1433
    ==== End of SQL Server query response ====

ANSWER: Probably server IP address is not allowed in server name field. The workaround is to modify hosts file like @MarkIannucci decribed below.

If your server's ip address is static, you can modify your hosts file with a reference to DBNODE2's ip address and specify DBNODE2 in your Connect to Server Server Name file.

It's not a good practice to use the SQL Server name when configuring the connection. To connect to a SQL server, you need to specify 2 parameters: Server IP address and port. If you only specify the SQL Server name, then you will depend on the naming resolution, it may or may not work as you expect. So the best practice is to use SQL Alias, for more info on how to set up, check out at https://randynghiem.wordpress.com/2015/01/29/best-practices-on-setting-data-connection-for-ssis-packages/


Row level security with a single DB user and connection pooling I'm using node-postgres to connect to a PostgreSQL 9.6 database with connection pooling enabled. All connections user the same database user. One reason I can't use multiple DB users is that as far as I read, row level security and views don't work well together in that case as the owner of the view is used for RLS.

I'm now looking at using row level security in Postgres, and I'd like to make sure I'm doing this correctly.

I'm using SET LOCAL to set the current application user id which is then used by the row level security USING clause. The only way I can think of achieving this with connection pooling is to wrap every query in a transaction with node-postgres and execute the SET LOCAL command like the following in every transaction.

SET LOCAL postgres.my_user = 20;

The following code is a simplified example of how I want to define the row level security, the real version has a few more conditions:

CREATE POLICY table_a_read_policy ON table_a FOR SELECT
USING (
    EXISTS (
    SELECT *
    FROM permissions
    WHERE
        user_id = current_setting('postgres.my_user')::int AND
        permission_type = 'read'
    )
);

My understanding is that any SQL injection in this case would also give the attacker the ability to just set any user id and circumvent row level security this way. But I don't see any way to avoid this as the db user I connect with must have the ability to see all data for the entire application.

There are a few points I'm not entirely sure about:

Is using SET LOCAL as a way to store the current application user identity for row level security safe? Especially in the context of connection pooling?
Is there a better way than wrapping every query in a transaction in node-postgres and executing the SET LOCAL command every single time?
Is there a way to structure this (with the restriction that the database connection is made using a single database user) in a way where an SQL injection would not automatically give the ability to circumvent row level security?

I have a similar setup on a new project.

The issue here is that you have a connection pool that uses a single login. The application that holds that connection pool will need to set the user correctly to get the required row level permissions. If your application is incorrectly configured / coded, then the permissions can be subverted.

It seems to me that row level permissions are only really useful when you have different users/roles with different logins reading from tables directly. In that case there is no where user = xxxx style clause that can be subverted. In this situation they make different connections.

I was also looking to do something similar to what you had said. Came across this blog post and found it useful. Hope this helps.

blog.2ndquadrant.com/application-users-vs-row-level-security

  Signed session variables

  The first solution is a simple improvement of the session variables – we can’t really prevent the users from setting arbitrary value, but what if we could verify that the value was not subverted? That’s fairly easy to do using a simple digital signature. Instead of just storing the username, the trusted part (connection pool, application) can do something like this:

  signature = sha256(username + timestamp + SECRET)

  and then store both the value and the signature into the session variable:

  SET my.username = 'username:timestamp:signature'

  Assuming the user does not know the SECRET string (e.g. 128B of random data), it shouldn’t be possible to modify the value without invalidating the signature.


Log Shipping copy job doesn't delete old files on a secondary node that is part of AlwaysOn configuration I have the following setup, all servers are SQL 2014 Enterprise:

2 node AlwaysOn availability group;
third server for off-site DR.

Log backups are being taken on the primary node, copied to the secondary node (restore job is disabled of course) and copied and restored to the DR server.

Everything is working great, except the fact that the log backup files are not being deleted on the secondary node. I noticed it because the folder kept growing, when I opened it I saw all the logs are there since day 1 when log shipping was set up. All jobs are executed without errors and the interval to keep the log backups is set. How can I troubleshoot this?

UPDATE: Apparently, the process of deleting the old files is actually part of the RESTORE job and hot the COPY job.

Now the question is, can I safely run the RESTORE job on a secondary AlwaysOn node as well?

Wouldn't you want to ensure the secondary logs are restored to the secondary server at certain intervals? With log shipping, I thought you ship the logs over to your secondary DB and then restore from those to keep the secondary up-to-date as close as possible to your primary.

Do you see any reason why you would not want to automate the log shipping restore on your secondary DB in the log shipping configuration?

Once those log files are applied to secondary, then the job will purge the logs that are not needed any longer since those are reflected in the secondary DB at that time.

Test it out and see how it goes.

EDIT: @nulldotzero

Okay, since you cannot run restore operations on primary or secondary in an AlwaysOn configuration, and because you are also copying the TRN log files to the seconary as well as the DR for redundancy, then you could just setup a SQL Agent job on the secondary instance to do the below for whatever hours you feel need to be purged.

--This will set the date time stamp to pass onto the Stored Proc for 72 hours from the current date when it runs
--The stored proc will recursively delete all TRN files from the parent level specified all the way down
declare @DeleteDate nvarchar(50)
declare @DeleteDateTime datetime
set @DeleteDateTime = DateAdd(hh, -72, GetDate())
set @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

--5th argument below "0 - don't delete recursively (default)" and  "1 - delete files in sub directories"
EXECUTE master.dbo.xp_delete_file 0,N'S:\MSSQL\Backup\TransactionLog\',N'trn', @DeleteDate,1

Since the log backup files are purged from the locations which the LSRestore for DR runs and grabs those, then that job will take care of those once the restore job completes successfully.

look in the msdb log shipping tables, or run select * from msdb.dbo.log_shipping_monitor_secondary

and history retention period usually 5760, by adjusting this value you can have the transaction logs that are restored to be purged.


Too many WAL files in archive folder PostgreSQL Streaming Replication I have two Postgresql 9.5 servers configured in streaming replication. There are no errors in log files and the servers seem to be in sync.

MASTER postgresl.config

archive_command = 'test ! -f mnt/server/archivedir/%f && cp %p mnt/server/archivedir/%f'
wal_keep_segments = 32

Everything else is pretty much default. And yet I have 1600+ files in the archive directory! Because of space constraints I have dared to delete some of them. I would like to figure out what is causing this. I also see that there are only very old files on the slave for the same directory.

The only thing I can think of is that I recently did a restore on a database. I dropped the database, created it and did a backup restore. I did this several times. The slave seems to have caught up with the changes. But I have a ton of WAL files in the archive folder for each of the backup restores. Would appreciate some insight into what could be happening.

wal_keep_segments tells it how many files to keep in the pg_xlog directory, not the archive directory.

PostgreSQL doesn't manage the archive directory for you, that is up to you.  It doesn't even know where (or what) the archive location is, that is why it asks you for an archive_command, rather than just a directory.  As far as PostgreSQL is concerned, your archive command could pass the files through od, send the output to a line printer with acid-free paper, then put that in a 3 ring binder and store them in your attic.  Although more common would be encrypt them and upload them to Amazon's S3, say.

It is kind of weird to use both wal_keep_segments and archive_command together, at least for relatively simple setups.  If you just want a streaming replica and don't need the ability to do point-in-time recovery, you could turn off archiving altogether.

If you really want to keep using an archive but have it cleaned up when the files are no longer needed, you can use the archive_cleanup_command setting.  But now that we have streaming replication and replication slots, that is almost obsolete.

As I said in this post https://stackoverflow.com/a/63401901/10043142, PostgreSQL Wiki (https://wiki.postgresql.org/wiki/Streaming_Replication) says something important about this in the following post, step 5:
# To enable read-only queries on a standby server, wal_level must be set to
# "hot_standby". But you can choose "archive" if you never connect to the
# server in standby mode.
wal_level = hot_standby

# Set the maximum number of concurrent connections from the standby servers.
max_wal_senders = 5

# To prevent the primary server from removing the WAL segments required for
# the standby server before shipping them, set the minimum number of segments
# retained in the pg_xlog directory. At least wal_keep_segments should be
# larger than the number of segments generated between the beginning of
# online-backup and the startup of streaming replication. If you enable WAL
# archiving to an archive directory accessible from the standby, this may
# not be necessary.
wal_keep_segments = 32

# Enable WAL archiving on the primary to an archive directory accessible from
# the standby. If wal_keep_segments is a high enough number to retain the WAL
# segments required for the standby server, this is not necessary.
archive_mode    = on
archive_command = 'cp %p /path_to/archive/%f'


Run Unix command using PL/SQL Is it possible to run a Unix command using a query in Oracle?

I want to run simple commands (like df -h) using a query.

Is this at all possible or am I wasting my time? I don't want to use a different language like Java or C to call a procedure, it needs to purely PL/SQL.

You can use the DBMS_Scheduler

DBMS_SCHEDULER.create_program
(
program_name => 'testScript',
program_type => 'EXECUTABLE',
program_action => '/home/someone/scripts/script.sh',
number_of_arguments => 0,
enabled => TRUE,
comments => 'Just a Test'
);
end;
/

and a little hosekeeping:

exec DBMS_SCHEDULER.drop_program(PROGRAM_NAME => 'testScript');

I just want to share an alternative approach for calling operating system commands. In this approach a simple web-service interface is set up for calling OS commands from SQL or PL/SQL. Details about this approach can be found here.

Using the same approach, a step-by-step description of how to invoke SQL*Loader from SQL is described here.

Please note that in the above approach the web-service is defined in Python. Python comes pre-installed with most common Linux distributions (including Oracle Linux), however if you are on a Windows platform you may need to install Python 2.7.3


Save Results as CSV *with* headers in SSMS With SQL Server 2008 R2, when I save the results as a CSV there are no headers. I can work around this by copying and pasting with the "Copy with Headers" , or just grab the headers and paste them in the CSV etc, but is there a better way to do this?

In SSMS you need to change some options:

Tools - Options - Query results - sql server - results to grid (or text) -> Include column headers when copying or saving the results.

Changed settings are applied to new, but not existing query windows.

SSMS isn't a data exporting tool.  Use either the Import/Export wizard or BCP both of which were designed as data export tools and will do what you are looking to do.


Does MySQL support replicating all databases? I’ve been doing some investigation into properly configuring MySQL replication.

From what I understand after reading the documentation, you can only replicate specific databases, and each database name must be specified: In the master configuration, in order for the relevant database writes to be written to the binary logs, and in the slave configuration, in order to replay those logs onto the slave.

Is this correct, or is it possible to configure MySQL to replicate any and all databases?

To answer your question about how MySQL replicates all databases, here is how it works...

Master has Binary Logging enabled
Master records completed SQL Statements
Master will inject 'USE dbname;' among the SQL Statements it records
Slave connects to Master (IO Thread)
Slave IO Thread requests next SQL statement from the Binary Logs
Slave IO Thread copies it to its Relay Logs
Slave SQL Thread Processes its Relay Log Entries FIFO (Queue)

Once USE dbname; is issued out of the relay logs,  multiple SQL statements will be processes chronologically until another USE dbname; (a different database) is issued from the Relay Logs.

To see this, pick any Binary Log (except the Current One) on the Master, and run this:

mysqlbinlog binarylogname > SQLStatements.sql

To see all the binary logs and the current binary log on the Master, run these:

SHOW BINARY LOGS;
SHOW MASTER STATUS;

Long story short: By default, all DBs are replicated due to the USE dbname; command within the binary logs on the master and the relay logs on the slave.

Abdul's answer shows how to filter in or filter out databases from being processed by the slave.

From what I have understood from RolandoMySQLDBA answer is you just add to your my.cnf replicate-ignore-db=mysql to skip mysql system database replication.


Creating an Index based on Execution Plan I've been rewriting a view that was performing poorly - it had 28 LEFT JOINs to another view and I have managed to modify this to a single LEFT JOIN and we are seeing a pretty good improvement in the performance.
But as I am looking at the execution plan to see where I could possible gain more performance, I noticing this - SQL is basically having to read 5 times the number of records that are being returned here from this one particular table and Plan Explorer is telling me this is where 24% of the query cost is coming from.

But when I look at the index that is mentioned, these are the columns that are included in the index ( no columns are included ) :

So my question is - as I try to understand what exactly I am looking at here - is there anything I can do to the existing index to help performance here?
I tried to use Brent Ozar's Paste the Plan but the XML was too large for that and since the TSQL itself is about 800 lines, I have created a folder in my Google Drive that contains the .sql file, .sqlplan file and a .pesession file ( for Sentry One Plan Explorer ).
Link to files.

You may find this answer to be helpful. The index contains all needed columns for the query so it can be used by the query. However, the order of the columns does not allow for as much filtering as possible using the index keys. About 14.3 million rows are located in the index using an index seek but almost 12 million of those rows are discarded after the predicate filter is applied. It's also worth mentioning that the table only has 10.7 million rows. Using nested loops and an index to read 14 million rows from a table with 10.7 million rows may not be the best choice. With that said, you can make the index seek more efficient by changing the index key order to the following:

PolicyNumber
CovPerilId
TransactionId
All other columns

Changing the key order of the index may impact other queries in positive or negative ways. I can't say if it's the right decision to make overall for the server's workload.
I noticed a few other things while looking at your query plan:

About 164 seconds (38% of total execution time) was spent during query execution waiting to send results to the client. Returning almost a million rows to the SSMS results grid can take a while. Be sure to run your query in a way most similar to what the application does to get the most accurate tuning results. You may find it useful to discard to query result set in SSMS while doing performance testing.
About 42 seconds (10% of total execution time) was spent waiting on CPU. The query will complete faster if it can be changed to use less CPU, if other queries on the server can be used to use less CPU, or if the total core count of the server is increased.
There are other index seeks in the plan where more rows are read from the index than are present in the corresponding table. '[AfterShock].[dbo].[LineOfCoverage].[LineOfCoverage11]' in node 53 is one example. A different index or join type may improve performance of the query.
There are some poor cardinality estimates that lead to tempdb spills. Explaining why is outside the scope of this Q&A. The query is fairly complex so perhaps splitting it into multiple queries that use temp tables will result in better performance.

Transaction Id in the index is not the leading key column in both the table it seems.
You are probably filtering with transaction id  in where clause which is an inequality predicate i.e <= or > = .
Therefore you might prefer creating an index with transaction Id as leading key column to see if that helps in avoiding the spills


Did a SQL Server instance ever participate in replication? How can I tell whether a SQL Server instance ever had replication in use or not? I'm debugging an issue with a 3rd party software. This software (Red Gate SQL Backup) reports the distribution database to be missing. The company has asked to find out whether this server has ever used replication or not. How can I find out?

Replication does not seem to be installed and the distribution database is not present. None of the databases use replication.

I'm not sure if you'll be able to reliable tell if the server has ever used replication or not.  For example, if someone drops replication properly, there will be no trace left behind.

Please check

select * from sys.servers;

Check these column values.

is_publisher
is_subscriber
is_distributor
is_nonsql_subscriber


Using a join to return multiple values of the same column? I have two tables

user
Rank

In the Rank table, I saved rank_id and supervisor_id. In the Rank table, I stored the rank name. The rank_id and supervisor_id are foreign keys from the Rank table.

Since I have to select both rank_id and supervisor_id from same table(Rank), how do I join both tables to get the result with name, rank name and supervisor?

DDL

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `Name` varchar(90) NOT NULL,
  `rank_id` int(4) NOT NULL,
  `supervisor_id` int(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `user`
--

INSERT INTO `user` (`id`, `Name`, `rank_id`, `supervisor_id`) VALUES
(1, 'john', 3, 2),
(2, 'james', 5, 1),
(3, 'geore', 4, 3),
(4, 'aby', 3, 2),
(5, 'john', 2, 1);

CREATE TABLE IF NOT EXISTS `rank` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `rank_name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `rank`
--

INSERT INTO `rank` (`id`, `rank_name`) VALUES
(1, 'president'),
(2, 'vice president'),
(3, 'prime minister'),
(4, 'cabinet minister'),
(5, 'minister');

expected result

I need result like below mentioned table

CREATE TABLE IF NOT EXISTS `result` (
  `id` int(7) NOT NULL,
  `name` varchar(90) NOT NULL,
  `rank` varchar(50) NOT NULL,
  `supervisor` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `result`
--

INSERT INTO `result` (`id`, `name`, `rank`, `supervisor`) VALUES
(4, 'aby', 'prime minister', 'vice president');

You have to join the user table to the rank table twice

SELECT u.id,u.Name,r1.rank_name Rank,r2.rank_name Supervisor
FROM user u
INNER JOIN rank r1 ON u.rank_id = r1.id
INNER JOIN rank r2 ON u.supervisor_id = r2.id
WHERE u.id = 4;

or

SELECT u.id,u.Name,r1.rank_name Rank,r2.rank_name Supervisor
FROM user u
INNER JOIN rank r1 ON u.rank_id = r1.id
INNER JOIN rank r2 ON u.supervisor_id = r2.id
WHERE u.Name = 'aby';

Give it a Try !!!

SELECT tbl_trip.trip_id, tbl_trip.location, tbl_member.gender
FROM (tbl_trip)
Left JOIN tbl_member ON tbl_member.trip_id = tbl_trip.trip_id


Providing that you'll always close a job before opening another under the same ID (although personally you should never have two seperate jobs running under the same ID without another unique identifier to distinguish (You've opened 4909 and 4910 twice))

if you join the table on itself distinguishing them between Open and Closed you can get it all in one short statement

select opened.jobOrderId as [Job Order ID],opened.dateadded as [opened date], closeed.dateadded as [closed date]
FROM JobHistory as opened
LEFT JOIN Jobhistory closeed on opened.jobOrderID = closeed.jobOrderID and closeed.status='Close' and closeed.dateadded > opened.dateAdded
WHERE opened.status='OPEN'

If you've got an actual unique identifier for the job I'd recommend joining on that rather than the JobOrderID column and you won't then need do the statement:

and closeed.dateadded > opened.dateAdded

as that is simply there to ensure that you don't close something that was opened in the future

Ste

create table #jobhistory (JobOrderID int, dateAdded datetime, Status varchar(10))
go
insert into #jobHistory (JobOrderID, dateAdded, Status)
select 4909, '2015-08-26 18:15:07.527', 'OPEN' union all
select 4909, '2015-08-28 13:35:38.997', 'CLOSE' union all
select 4909, '2015-08-31 12:16:29.787', 'OPEN' union all
select 4910, '2015-08-27 12:16:42.72 ', 'OPEN' union all
select 4910, '2015-08-28 17:04:43.617', 'CLOSE' union all
select 4910, '2015-08-31 17:01:27.337', 'OPEN' union all
select 4911, '2015-08-27 16:08:39.467', 'OPEN';
go
; with c as(
select jobOrderID, dateAdded, Status, rank() over (partition by jobOrderID order by dateAdded asc) as rnk
from #jobHistory
)
select c.jobOrderID, OpenDate=convert(char(10), c.dateAdded, 120), CloseDate=convert(char(10), c2.dateAdded, 120)
from c
left join c as c2
on c.jobOrderID = c2.jobOrderID
and c.Status='OPEN'
and c2.Status='Close'
and c2.rnk = c.rnk+1
where c.Status='Open';


SQL Server restore database file access error I am trying to restore a database from a file on a remote machine. The script I am using is as follows:

RESTORE FILELISTONLY
   FROM disk = '\\server\path\to\file.dmp'
RESTORE DATABASE MyDB
   FROM disk = '\\server\path\to\file.dmp'
   WITH REPLACE,
   MOVE 'MyDB_Data' TO 'D:\path\to\file.mdf',
   MOVE 'MyDB_Log' TO 'D:\path\to\log.ldf',
   stats = 20
GO

I am using this script because the SQL Server Management wizard appear to dislike the use of a .dmp file instead of a .bak file, though my team has used .dmp files in the past without issue. However, whilst the RESTORE FILELISTONLY command appears to complete successfully, the RESTORE DATABASE command fails with the following error.

Msg 3203, Level 16, State 1, Line 3
Read on "\\server\path\to\file.dmp" failed: 38(failed to retrieve text for this error. Reason: 15105)
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

This seems unusual, as it appears to be an error with the .dmp file, but the previous RESTORE command did not complain at all. On top of this, the database in question is now stuck in a neverending "Restoring..." state which I cannot seem to get it out of. I tried RESTORE DATABASE MyDB WITH RECOVERY but that gave the same error, tried adding RECOVERY to the original script but just got the same error.

Any idea what's going on? I am new to DBA, myself, so I have limited experience to draw upon.

Double check that the paths you are trying to restore to exist on the D drive. SQL Server will not create the directory path if it doesn't exist.

If you haven't already, run the restore command with the verify only option MSDN.

If that is successful here are a few additional things you can try to isolate why it is failing

If you have the space on the SQL Server that created the backup, try to restore the backup there under a different file path and different database name (again make sure the temporary restore path(s) exist.)  This will confirm if the backup is valid and can be read by SQL Server.  Be very careful here to change the database name to something different and to give the file names a different name to not cause a problem with the current database.  (Example: change MyDB to Restored_MyDB, MyDB.mdf to Restored_MyDB.mdf and MyDB.ldf to Restored_MyDB.ldf in the restore statement.)
Confirm the account that you are using to do the restore,if it's not the same, has the same permissions that the account that did the backup does.
Copy the backup locally to the machine you wish to restore it to and restore from the local drives.
Change the file extension from dmp to bak.

• Run DBCC CHECKDB: It checks & reports all the error message in error log, if there is any problem with the database. Try to analyze & understand the error message logged in the error log. Re-run DBCC CHECKDB with the recommended minimum repair option to repair the file.


App domain is marked for unload due to memory pressure In my sql server log (2005 SP4) I have this message every couple of seconds :

"AppDomain XXX [DataBase.dbo[runtime], YYY] is marked for unload due to memory pressure"

Our server :

Windows Server 2003 R2 Enterprise Edition SP2
32 Gb RAM
Physical Address Extension
Intel Xeon CPU X5675 x 2 (6 core) 3.07Ghz
Awe enabled
We have CLR code in SQL Server
-g2048 on startup (I'm no DBA, and this value was set by a former emplyee)

I read many article about memtoleave or vas, but I don't get how to diagnose my problem.

Any idea ?

Microsoft has a KB article about this behavior. You got a 32-bit OS with 32-bit application, so the process' address space is 4 GiB. AWE can be used to access more memory, but it is limited only for buffer pool usage. As per the documentation:

  The SQL Server buffer pool can fully utilize AWE mapped memory;
  however, only database pages can be dynamically mapped to and unmapped
  from SQL Server's virtual address space and take full advantage of
  memory allocated through AWE. AWE does not directly help supporting
  additional users, threads, databases, queries, and other objects that
  permanently reside in the virtual address space.

Short-term solution: review that your CLR code minimizes memory usage.

For a long-term solution, upgrade to 64-bit.

Addendum

Kimberly Tripp has written a white paper about CLR stuff from DBA point of view. She's got a few nice queries and Perfmon counters that are of use for tracking CLR resource use.

I also had the same problem and here is how I fixed it:

  Don’t enable Hyper-V Dynamic Memory for your virtual machine but set it static with a value of e.g. 8GB RAM


Same query hash for totally different queries Query_hash definition from  sys.dm_exec_query_stats is:

  query_hash: Binary hash value calculated on the query and used to identify queries with similar logic. You can use the query hash to determine the aggregate resource usage for queries that differ only by literal values.

But when I search for one particular query_hash (as shown below), I am getting multiple query texts that differ heavily in logic and size_in_bytes (for plan).

Note: I am using SQL Server 2016

DECLARE @QueryHashTest BINARY(8)
SET @QueryHashTest = CONVERT(BINARY(8), 'Ð…U¹üŒv¿')

SELECT
        QCP.objtype
        ,qStat.query_hash,
        CONVERT(VARCHAR(100),  qStat.query_Hash) AS VARCHAR_query_hash
        ,sText.text AS QueryText
        ,QCP.size_in_bytes
        ,qStat.creation_time
        ,qp.query_plan
FROM (
        SELECT query_hash,
               COUNT(query_hash) AS PlanCount
        FROM sys.dm_exec_query_stats
        GROUP BY query_hash
    ) AS MultipleQ
INNER JOIN sys.dm_exec_query_stats qStat ON MultipleQ.query_hash = qStat.query_hash
INNER JOIN sys.dm_exec_cached_plans  QCP
    ON QCP.plan_handle = qStat.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qStat.sql_handle) AS sText
CROSS APPLY sys.dm_exec_query_plan(qStat.plan_handle) AS qp
WHERE PlanCount > 1
AND QCP.objtype = 'Proc'
AND qStat.query_hash= @QueryHashTest
AND (size_in_bytes >= 2179072 OR size_in_bytes <= 262144)
ORDER BY size_in_bytes DESC

Result

Why are the unrelated queries showing same query_hash? Is it a bug or is there something unexpected going on in the database?

While hash collisions are possible, I suspect what's happening is you're pulling the text for the whole batch, not the particular statement. A procedure with multiple statements can get a cache entry for each statement. And if some statements are identical across procedures, they'll have the same query hash. Let me demonstrate:

USE TestDB
GO

--DROP TABLE dbo.employees
CREATE TABLE dbo.employees (
ID INT IDENTITY PRIMARY KEY,
title VARCHAR(20),
salary TINYINT,
has_been_fired_yet BIT)

INSERT dbo.employees
VALUES ('Senior DBA',30,0),('Grumpy DBA',50,0)
GO

CREATE OR ALTER PROC dbo.Payroll
AS

SELECT 'Your total payroll is '+CONVERT(VARCHAR(10),SUM(salary))+' query bucks'
FROM dbo.employees
WHERE has_been_fired_yet = 0
GO

CREATE OR ALTER PROC dbo.RightSizing
AS

UPDATE dbo.employees
SET has_been_fired_yet = 1
WHERE salary > 40

SELECT 'Your total payroll is '+CONVERT(VARCHAR(10),SUM(salary))+' query bucks'
FROM dbo.employees
WHERE has_been_fired_yet = 0
GO

--EXEC to get them in the cache
EXEC dbo.Payroll
EXEC dbo.RightSizing

Use the below query to see the how the same statement that is in each proc shows up separately.

SELECT
st.text AS batch_text,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2) + 1
) AS statement_text,
qs.execution_count,
qs.query_hash
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
WHERE st.objectid IN (OBJECT_ID('dbo.Payroll'),OBJECT_ID('dbo.RightSizing'))

If you use the query in your question, it fails to pick up the identical statement, since it looks at only the batch text.

Hashing means "mapping" something potentially large to something limited in space (the query_hash). The query hash is a binary(8). So in 8 bytes, you want to identify something that can, essentially, have an endless possibility of values (the query). Bottom line is that you can get collisions - different queries resulting in the same hash. This is expected.


media family on device is incorrectly formed. SQL Server cannot process this media family I am trying to restore a .BAK in SQL server but get the following error:

  Msg 3241, Level 16, State 7, Line 1 The media family on device 'c:\glyn\JA.bak' is incorrectly formed. SQL Server cannot process this media family. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

I have tried restoring using 2012, 2008 and even 2005 but nothing works, I have used the following query and through the Tasks >> Back up but nothing works, here's my SQL:

RESTORE DATABASE JA FROM DISK='c:\glyn\JA.bak'

Does anyone have any suggestions? I've read that the bak could be corrupt, it was sent to my by another developer on DVD who is working on the project.

If you are attempting to restore newer database to older SQL version you can always simply manually create the database in the older SQL version and utilize the task - generate scripts and include the data.

Save to File --> Advanced--> Types of data to script --> Schema and data.

Also make sure to Script for Server Version.

This might be the only supported option if you're trying to go from newer SQL Server to older version.

Related: Restore SQL Server 2012 backup to a SQL Server 2008 database?

delete corrupted file and re-run the backup


MySQL does not start - Ubuntu 14.04 LTS after migration to new disk I have installed in a Virtualbox VM a 64-bit desktop version of Ubuntu 14.04 LTS and MySQL Server 5.6. I had some databases in the machine's main virtual hard disk but then I was going to open a SQL dump with a size of 6 GB, and the disk didn't have enough space to open it. So, I decided to create a new virtual disk (13 GB) and attach it to the VM.

After formatting the new disk I created a new folder called mysql-db-new in the new volume. Now I will give you the entire process I did to migrate the databases from the main volume to the new volume:

Stop MySQL server:

sudo /etc/init.d/mysql stop

Move MySQL data to the new drive:

sudo mv /var/lib/mysql /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/

Edit /etc/mysql/my.cnf to point the new location (initially /var/lib/mysql):

datadir = /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql

Configure AppArmor with the new path:

sudo gedit /etc/apparmor.d/usr.sbin.mysqld

All lines with /var/lib/mysql were replaced with the new location /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql
Change permissions and group of the new location:

sudo chgrp mysql /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql

sudo chmod 755 /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql

Restart MySQL:

sudo /etc/init.d/mysql start

Then after all this steps, MySQL start failed. Then I watched the /var/log/mysql/error.log and this is the output:

140904 15:25:14 mysqld_safe Starting mysqld daemon with databases from /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql
140904 15:25:14 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
140904 15:25:14 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
140904 15:25:14 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Table 'plugin' is read only
140904 15:25:14 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
140904 15:25:14 InnoDB: The InnoDB memory heap is disabled
140904 15:25:14 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140904 15:25:14 InnoDB: Compressed tables use zlib 1.2.8
140904 15:25:14 InnoDB: Using Linux native AIO
140904 15:25:14 InnoDB: Initializing buffer pool, size = 128.0M
140904 15:25:14 InnoDB: Completed initialization of buffer pool
140904 15:25:14 InnoDB: highest supported file format is Barracuda.
140904 15:25:14  InnoDB: Waiting for the background threads to start
140904 15:25:15 InnoDB: 5.5.38 started; log sequence number 1637757
140904 15:25:15 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
140904 15:25:15 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
140904 15:25:15 [Note] Server socket created on IP: '127.0.0.1'.
140904 15:25:15 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'host' is read only
140904 15:25:15 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

So I don't know what's really going on. My first thought was on the permissions but I gave enough permissions on the new MySQL database location folder.

I will appreciate your help.

You only changed the ownership and permissions for the mysql datadir, not all the files below it. You should apply those changes recursively with the -R option.

I would do it this way:

$ sudo chown -R mysql:mysql /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql

$ sudo chmod -R g+rwX /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql

The datadir doesn't need to be other-accessible.

Also make sure that the full chain of directories above (/media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/) are readable & executable by mysql:mysql.

I also face this problem.My solution is to give all the 'r' and 'x' permissions for the target datadir's parent path and it work.


How to find the MySQL query that got rolled back I want to know if a certain transaction got rolled back. If we analyze

  SHOW ENGINE INNODB STATUS\G;

we get the list rolled back transactions with transactionid , but how to get the actual query from this

eg I have

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 89504501
Purge done for trx's n:o < 88503595 undo n:o < 0 state: running but idle
History list length 2341
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 531230733218221, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 531230733221873, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 531230733213665, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

I get some transcation id = 531230733218221, but what is the INSERT/UPDATE query corresponding to this.
Here is my mysql config:

Version: 5.7
Binary Log: ON
Slow Query Log: ON
General Query Log: OFF
Performance Schema: ON
Profiling: OFF
Transaction Isolation Level: REPEATABLE-READ

Is there any table in performance_schema that we can explore. Since MySQL do not have any mechanism to store the last updated timestamp at row/column level, though it exists at the table level.

Look in the SHOW ENGINE INNODB STATUS output for

LATEST DETECTED DEADLOCK

and between this line and the

WE ROLL BACK TRANSACTION

you will find QUERY content that was involved for each contributor to the deadlock (there may be thousands of lines of detailed info).


Twice job results in sql server, last one is left in progress I have the job MP - Create CSV which execute a package SSIS in sql server 2008, when I check for the job history I see the result at step 0, I expand and I have 2 results for the step 1.
Fist result have the icon for Success with the result :

  Message
  Executed as user: companyname\sa. The step succeeded.

The second one have the step 1 also, but with the icon for In Progress with the result :

  Message
  Microsoft (R) SQL Server Execute Package Utility
  Version 10.50.4000.0 for 64-bit
  Copyright (C) Microsoft Corporation 2010. All rights reserved.

  Started:  11:50:00 PM
  DTExec: The package execution returned DTSER_SUCCESS (0).
  Started:  11:50:00 PM
  Finished: 11:50:26 PM
  Elapsed:  26.645 seconds

My problem is I have a report to show all the job with their status not equal to 1 (success), my report do not work anymore because of this result "In progress".
Here is the sql I use to run my report :

SELECT *  from  msdb..sysjobhistory WHERE run_status != 1

My questions are why do I have 2 results for my step ? I think I should have only one. And what should I do when I have a job with a step status left "In progress" ?

For more information, my job is running daily and succeed every time, thank you.

This will give you the desired results :

select h.server as [Server],
    j.[name] as [Name],
    h.message as [Message],
    h.run_date as LastRunDate,
    h.run_time as LastRunTime
from msdb.dbo.sysjobhistory h
    inner join msdb.dbo.sysjobs j on h.job_id = j.job_id
        where j.enabled = 1 -- only check for enabled jobs
        and h.instance_id in
        (select max(h.instance_id)
            from msdb.dbo.sysjobhistory h group by (h.job_id))
        and h.run_status != 1
/*0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled*/

Check the advanced properties of the job step this is happening on.
Are "Output file:", "Log to table" and "Include step output in history" checked?
If so, uncheck them and see if this issue goes away.


MySQL query optimization with aggregate ORDER BY I have an analytics table that stores metrics aggregated the day level. I need to ask questions such as "For a given date range and client, what are the most popular items."

The schema is as follows:

CREATE TABLE `t1` (
    `imps` bigint(20) DEFAULT NULL,
    `n_id` int(11) NOT NULL,
    `b_id` bigint(20) NOT NULL,
    `date` date NOT NULL,
    `m1` bigint(20) NOT NULL,
    `m2` bigint(20) NOT NULL,
    `is_b` tinyint(1) NOT NULL,
    `i_id` varchar(128) NOT NULL,
    KEY `idx1` (`b_id`,`date`,`i_id`,`n_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

(This table is split into 256 partitions by HASH(b_id), in case that matters)

A representative query:

SELECT i_id, n_id, SUM(m1), SUM(m2)
FROM t1
WHERE b_id=1234 AND date>='2013-06-01' AND date<='2013-08-31'
GROUP BY i_id, n_id
ORDER BY (SUM(m1) + SUM(m2)) DESC
LIMIT 10 OFFSET 0

And the EXPLAIN output:

+----+-------------+-------+------+---------------+------+---------+-------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows  | Extra                                        |
+----+-------------+-------+------+---------------+------+---------+-------+-------+----------------------------------------------+
|  1 | SIMPLE      | t1    | ref  | idx1          | idx1 | 8       | const | 1395772 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+-------+-------+----------------------------------------------+

On a table with ~110M records these queries take > 10s to execute. I believe the SUM function in the ORDER BY clause forces the entire resultset satisfied by the WHERE clause to be written to disk, hence the "Using temporary; Using filesort" messages.

Do I have any recourse in improving the performance of these queries short of redesigning the schema and application?

Edit: Here is a SQLFiddle with the schema and dummy data: http://sqlfiddle.com/#!2/beb63/1

Did you tried in the following way:

SELECT * FROM (
SELECT i_id, n_id, SUM(m1) AS sum1, SUM(m2) sum2
FROM t1
WHERE b_id=1234 AND date>='2013-06-01' AND date<='2013-08-31'
GROUP BY i_id, n_id
LIMIT 10 OFFSET 0)x
ORDER BY sum1 + sum2 DESC

I'm in SQL Server and there it's helping me.


Cannot connect to DB2 database after restoring an offline backup This is for DB2 9.7 Enterprise Server Edition on AIX.

I had a full, offline backup taken of several databases within our instance in a performance environment.

I wanted to restore these databases to our integration environment.

I ran the command

db2 restore database database_name from
/bkp/instance_name/OfflineBackups/RestorePoint/my_restore_point
taken at 20120321103846 into database_name without prompting;

for each of my databases (modifying the database name and timestamp appropriately of course).

Each database restored successfully according to DB2. However, now when I wish to check if the database did indeed restore what I thought, I try to connect to a database and I get the error

    SQL1117N  A connection to or activation of database "database_name" cannot be made
because of ROLL-FORWARD PENDING.  SQLSTATE=57019

I didn't have any in process transactions in the previous database because all connections were forced off and the database was down for an offline backup. What do I do?

Found the answer here.

I needed to run the command

db2 rollforward db database_name to end of backup and complete;

This commits everything to the logs and places the database in a no longer pending state, thus allowing connection to it. I needed to do this for each database restored.

  EDIT: Found this nifty nugget while attending a DB2NightShow episode. With 9.7 FP2, they introduced a registry variable called
  DB2_RESTORE_GRANT_ADMIN_AUTHORITY. This is handy if you are restoring
  a backup from one instance to another, as it automatically grants
  instance owner SECADM, DBADM, DATAACCESS, and ACCESSCTRL on the
  databases restored. With 9.7 FP5, the setting is dynamic and does not
  require bouncing of the instance. I realize my original question had
  nothing to do with security, but it can be encountered and thought
  that this would be a handy tidbit placed here.

  EDIT #2: I recently found out that if the backup is an offline backup based on an archival logged database, I could have included the
  clause WITHOUT ROLLING FORWARD in my RESTORE command. This would
  have taken the database out of roll forward pending state immediately
  upon successful restore, which would mean that I would not have had to
  then issue the ROLLFORWARD DB command.

If the backup type may be in Online backup.

Use Log path ("/db2logtarget")

Eg:-
db2 => rollforward db userdb to end of logs and complete overflow log path("/home/user/target")


Indexed view in SQL Server I have a table and an indexed view on it like

Create table mytable1 (ID int identity(1,1), Name nvarchar(100))

Create table mytable2 (ID int identity(1,1), Name nvarchar(100))

Create view myview
with schemabinding
as
   select a.name, b.name
   from mytable1 a
   join mytable2 b on a.Id = b.Id

Now if I run the following query

select a.name, b.name
from mytable1 a
join mytable2 b on a.Id = b.Id

It does not use my indexed view. Is there any hint (or other way) to force SQL Server to use indexed view instead?

I have a big system, and need to optimise it. I can't change all my SQL scripts to select from the view instead of tables. I want to create indexed views and force SQL Server to get data from them instead of tables.

I am using SQL Server 2014 Enterprise Edition.

I build indexed views in SQL Server all the time to tune existing products. The optimizer is smart enough to use the index if you are utilizing the appropriate columns.

Using your example, it looks like you created the view but did not actually create an index upon it.

if object_id(N'mytable1') is not null
drop table mytable1
if object_id(N'mytable2') is not null
drop table mytable2
go

Create table mytable1 (ID int identity(1,1), Name1 nvarchar(100))
GO
Create table mytable2 (ID int identity(1,1), Name2 nvarchar(100))
GO

insert into mytable1 values ('steve')
insert into mytable1 values ('jack')
insert into mytable1 values ('mike')
insert into mytable1 values ('ralph')
insert into mytable1 values ('simon')

insert into mytable2 values ('smith')
insert into mytable2 values ('jackson')
insert into mytable2 values ('mikaelson')
insert into mytable2 values ('montalvo')
insert into mytable2 values ('singer')
go

if object_id(N'myview') is not null
drop view myview
go

Create view myview
with schemabinding
as
select a.id, a.name1, b.name2
from dbo.mytable1 a
join dbo.mytable2 b on a.Id = b.Id
GO

select a.name1, b.name2
from mytable1 a join mytable2 b on a.Id = b.Id
GO

Since there is no index on this view, we scan on the base tables:

But once we add an index, the optimizer can use it:

CREATE UNIQUE CLUSTERED INDEX [ix_cl_names] ON [myview]
(
    [name1] ASC,
    [name2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

This appropriately used the view:

  I can't change all my SQL scripts to select from the view instead of tables. I want to create indexed views and force SQL Server to get data from them instead of tables.

There is no hint or other method to force SQL Server to use an indexed view when it is not referenced in the query.

Additional information (from Geoff Patterson)

One extra point is that while the optimizer can, in Enterprise Edition only, use the indexed view in this case, it may make sense to directly reference the view using the NOEXPAND hint if you need to be 100% sure of the view index being used or if you ever want it to be used in Standard Edition.

I've frequently seen queries even in Enterprise Edition where the optimizer does not pick up on the fact that the view index can be used unless NOEXPAND is used. It's more common with complex queries, but can happen with simple queries also.

Paul White has one of the better articles I've read exploring the nuances of NOEXPAND; beyond just usage of the view index, the hint can also impact things like whether statistics are automatically created on the indexed view and cardinality estimates for the plan.

And from Zane: As a side note, be careful with indexed views as like any other index it will add to your update, insert, and delete times.

If you can't change your application code to new objectnames, maybe you can change your application user to use a new default schema and create the indexed views in another schema usng same object names? For instance:

create view iv.MyTest
as
 select Col1, Col2 from dbo.MyTest

Of course this will work only if you have not used the schema names in the application code.

If you have, you could try to move all objects to a new schema and introduce the views in the old schema instead.


How can I get a list of tables contained in a database diagram? I need to get a list of the tables contained in a diagram in SQL Server. How can I do it using an SQL script?

I don't need a list of tables in the database, but the list of tables contained in a diagram of the database in SQL Server 2008r2/2012.

Although this question is a year old, if anyone stumble over this, I've posted a solution to make that possible here:

http://www.codeproject.com/Messages/4914888/Search-through-the-binary-for-all-contained-texts.aspx

The trick is to extract all ASCII characters in the diagram binary, you'll see that all table names are in there as simple text. That seems to be the reason why a diagram is also destroyed if you rename a table with a script - because it doesn't use a table ID to identify the table but the table name. If it's no longer there, the diagram is not usable anymore.

With this method you can identify which diagrams contains a specific table.

In SQL Server Management Studio:

USE YourDBName
GO
SELECT * FROM sys.Tables
GO

Or in MySQL:

use YourDBName;
show tables;


Only do log backup if necessary in SQL Server We have transaction log backups taken every few minutes from many databases. Is there a way to determine if there were any committed transactions since the last backup and only then do a log backup?

Some of the databases don't have any changes during the night, for example. But we'll still get many tiny log backups for these databases every time the log backup script runs.

I already tried scripting something based on the previous backup's last_lsn recorded in msdb and using fn_dblog to see if there were new committed transactions. I was not successful so far.

A bit of background:

These log backups are taken every few minutes and saved to Azure Blob Storage. They are not restored to another database (aka log shipping). During a single day we'll get a few hundred log backups per database. In case we ever have to restore something from Azure, I want to avoid having 100s or even 1000s of log files without any user transactions in them lengthening the restore process.

One alternative here is to calculate the size of active transactions in your transaction log by using DBCC LOGINFO.  Your logic would look something like this:

CREATE TABLE #vlfs(
   RecoveryUnitID int
  ,  FileID      int
  , FileSize    bigint
  , StartOffset bigint
  , FSeqNo      bigint
  , [Status]    bigint
  , Parity      bigint
  , CreateLSN   numeric(38)
);

DECLARE @MBthreshhold int

SELECT @MBthreshhold = 5 --Enter your threshhold in MB here

INSERT INTO #vlfs
EXEC ('DBCC LOGINFO')

IF (select sum(filesize/1024/1024)
    from #vlfs
    where [Status] = 2) > @MBthreshhold
BEGIN
    BACKUP LOG [foo] to <<backup location>>
END

Please note there are a lot of caveats here.  Your transaction log will never be completely empty and there is always something to backup to it.  You can use this to determine at what size point you want to actually run your backup, but this can be a HUGE risk to your Recovery Point Objective (RPO).  Your log backups will happen at variable times, meaning you can never guarantee the maximum amount of data loss you will incur in a disaster.  This sort of strategy should be pursued with extreme caution.

What you can do is

1- do an initial backup followed by an DBCC SHRINKFILE

BACKUP LOG DatabaseName TO DISK = N'D:\Backup\DatabaseName_log.bak'
GO
DBCC SHRINKFILE('MyDatabase_Log', 1)
GO

2- Before running the backup run a check to see if the transaction log size increased from the 1 MB size we shrieked it to ;
If is bigger then 1 MB then you backup it.
Se script to check this :

SELECT (s.size * 8.0)/1024.0 AS size_in_mb FROM sys.master_files s
INNER JOIN sys.databases d
on s.database_id=d.database_id
WHERE d.name = 'db_name' and s.type_desc='LOG';


Updating a WHERE clause to check if a value is NOT in a separate table I have a query which uses a WHERE clause, and I happen to use the exact same WHERE clause in many queries on this table (et al).

The query is:

SELECT
    DATENAME(DW, [AtDateTime]) AS [Day of Week]
    ,COUNT(*) AS [Number of Searches]
    ,CAST(CAST(COUNT(*) AS DECIMAL(10, 2))
         / COUNT(DISTINCT CONVERT(DATE, [AtDateTime])) AS DECIMAL(10, 2))
       AS [Average Searches per Day]
    ,SUM(CASE WHEN [NumFound] = 0 THEN 1 ELSE 0 END)
       AS [Number of Searches with no Results]
    ,CAST(CAST(SUM(CASE WHEN [NumFound] = 0 THEN 1 ELSE 0 END)
         AS DECIMAL(10, 2)) / COUNT(*) AS DECIMAL(10, 4))
       AS [Percent of Searches with no Results]
FROM [DB].[dbo].[SearchHistory]
WHERE
    [CustomerNumber] <> '1234' AND [CustomerNumber] <> '5678'
GROUP BY DATENAME(DW, [AtDateTime]), DATEPART(DW, [AtDateTime])
ORDER BY DATEPART(DW, [AtDateTime])

The part I wish to change is the WHERE clause, to instead allow me to use a table so that if I have to add a customer number to be ignored, I don't have to update all my queries. (And there are quite a few queries that have this same WHERE clause.)

CREATE TABLE dbo.CustomerExclusions
(
  CustomerNumber VARCHAR(32) PRIMARY KEY -- Is CustomerNumber *really* a string?
);

INSERT dbo.CustomerExclusions(CustomerNumber) VALUES('1234'),('5678');

Now your WHERE clause across all queries becomes:

WHERE NOT EXISTS
(
  SELECT 1 FROM dbo.CustomerExclusions AS c
  WHERE c.CustomerNumber = SearchHistory.CustomerNumber
)

There are important questions/ potential problems with your proposed approach. For sure, you can exclude easily enough via a 'Customer Number exclusion' work table:

WHERE NOT EXISTS (
  SELECT 1 FROM [dbo].Work_ExcludeCustomer
  WHERE CustomerNumber = SearchHistory.CustomerNumber
)

But now, what were "query parameters" -- fully dynamic & independent, per-query and per-user -- are turning into "shared persistent state in the database".

Some questions & relevant points:

should the Customer Exclusion info be separate, per-User or per-Session?  you can add a 'SessionID' parameter to distinguish these, but essentially you're recreating a hoary old "Work Table" pattern.
perhaps a NOT IN(...) clause could be preferable? that can be parameterized dynamically, up to the limit of 2100 parameters.
perhaps revisit your code/ infrastructure to build queries & binds parameters, if you currently rely on fixed parameter numbers;  improving this will enable modularity & use of an IN or NOT IN (?, ?, ?..) clauses with variable number of parameters.

Suggested approach:

WHERE [CustomerNumber] NOT IN (?, ?, ?)

With bindings '1234', '5678', '6789' etc to the NOT IN() parameters & subsequent logical query parameters bound to the appropriate numbering dynamically.


Over use/correct use of schemas? Having asked This question on Stackoverflow, I wondered where what I have done is correct/best practise.

Basically, every object that I create is going into a schema with the schema name reflecting a usage. For example, I have the schemas Audit and Admin (amongst others).

This in turn leaves no objects in dbo. Is this ok? Is there anything else that I need to do?

Schemas are not only a great security tool (which is reason enough to use them), but they are also perfect for logical separation.  And it seems as though this is what you are practicing.

Even if the current requirement doesn't need special security, say down the road all of the database objects that are related to Auditing should be secure to a database role.  If these objects were scattered throughout the dbo schema, then you would have to explicitly deny permissions on the individual objects.  But with the Audit schema, you do a single deny and you're set.

I personally practice the use of schemas.  Like all things in databases, though, there is a happy medium.  I wouldn't create a schema for each granular aspect of the data layer.  There is such thing as too many schemas and separation.  But I'm guessing you aren't anywhere close to that.

Best practices may be different for sql server than for oracle however my experience is that the less schemas, the better.
I like to have a schema for the dba/programmer that has special privileges and some code to do maintenance.  All the business data should go in one schema so you know where it is.  Naming conventions are enough to differentiate between the use of the table or stored code.
I can see a case where you have multiple business units owing different data with little overlap each having their own schema.  Otherwise keep it simple.


What is the best practice when an index with key/include columns are inversed for a second index on the same table I have an index on a table of the form:

NONCLUSTERED INDEX [IDX1] ON [TABLE]
(
    [COL_ID_A] ASC
)
INCLUDE
(
    [COL_ID_B]
)

For a different query on the same table MSSMS proposes the following index where the key and include columns are reversed:

NONCLUSTERED INDEX [IDX2] ON [TABLE]
(
    [COL_ID_B] ASC
)
INCLUDE
(
    [COL_ID_A]
)

What is the best practice (and why)?:

one index with keys and indexes combined or
two separate indexes

You may read more about non-clustered index here from Brent Ozar and Team. Thumb rule for column in the index should be used in where clause and include is the column which is in the select statement.

Since, you have not posted query here, you may understand with below examples:

/*Query1
********/
select [COL_ID_B]
from [TABLE]
where [COL_ID_A] = some value

Above query Query1 will use index IDX1, whereas Query2 will use IDX2.

/*Query2
*********/
select [COL_ID_A]
from [TABLE]
where [COL_ID_B] = some value

Now, there is difference between index column and include column, you can read more about this on article written by Mr. Ahmad Yaseen. There is also concept of hidden column in case you have clustered index on this table, clustered column will be part of all non-clustered index, you may refer this article.

Whether one index should contain multiple column or multiple index should be created with one column, it depends on application and query pattern. Thumb rule is to have five non-clustered index on 5 columns however this varies from application to application.

First column in index is extremely important, if your query selects col1 and looks mostly for colA followed by colB, colC and colD respectively then, you should go for a non-clustered index with below definition:

CREATE NONCLUSTERED INDEX [IDX3] ON [TABLE]
(
    colA,
    colB,
    colC,
    colD
)
INCLUDE
(
    col1
)

however if your queries have where clause on all columns and heavily uses all of them i.e. colA, colB, colC and colD then, five separate non-clustered index on each of them would help. Again column in select could be chosen as include.

There are tonnes of article on each of these topic by many experts. you may refer below for more details:

https://www.brentozar.com/archive/2018/11/tales-from-overindexing-too-many-one-column-indexes/

https://www.brentozar.com/archive/2015/04/index-included-columns-multi-column-statistics/

Similar question was asked and you may find answers here

Please let us know if this helped.

you should try to create only one index. creating seprate index would consume more IO and create overhead for DML operations.
  NONCLUSTERED INDEX [IDX1] ON [TABLE]
  (
    [COL_ID_A],[COL_ID_B]
  )
this index would cover both columns and suitable for both types of queries coming for col a and col b as predicates.

Scneraio:
1. Query one will search values inside the index and get correspondinf values of column b on same leaf level page. Same will be applied when query two search for Column b and find column a in select list.

Note :- statistics for both columns should be updated to get optimal execution plan.


Why would SequelPro only import 23k rows out of 130k? I use SequelPro for MySQL on a Mac OS X. I used the import function to upload a 130k .csv file to my database. Everything seems to work fine, then I get the message:

  File Read Error: An error occurred when reading the file, as it could not be read using the encoding you selected (Auto-detect - Unicode (UTF-8)). Only 23,000 rows were imported.

When I hit "Ok," everything else seems to work relatively fine, I'm just missing about 107,000 rows.

Any idea as to what it could be? Maybe I should use something other than auto-detect during the import? I thought that it might have been some extra commas floating around in the actual .csv file, which there were, but I got rid of those and the same thing happened.

Out of 130,000 rows, there's definitely the possibility for some non-English characters. Which ones doesn't MySQL accept and how would I find and replace them?

This is what I'm getting when I run the character set query:

show variables like 'character_set%';

Variable_name               Value
character_set_client        latin1
character_set_connection    latin1
character_set_database      latin1
character_set_filesystem    binary
character_set_results       latin1
character_set_server        latin1
character_set_system        utf8
character_sets_dir          /usr/local/mysql-5.6.10-osx10.7-x86_64/share/charsets/

This may depend on where you generated the CSV file. If the CSV file was generated on a Windows machine, there could be some character set issues

See https://code.google.com/p/sequel-pro/issues/detail?id=1629

See the following URLs as SequelPro's character set problems are not new

https://stackoverflow.com/questions/8910528/understanding-mysql-charset-and-collation
https://stackoverflow.com/questions/11185324/special-characters-get-lost-in-mysql-export-import
http://forums.mysql.com/read.php?28,284657,284657

If the CSV file was generated on another Mac OSx server, you should not be having this issue.

You may have to resort to setting the default character set to match that CSV file. Sounds weird to here it goes:

Please run this query and you will see something like this:

mysql> show variables like 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql>

You can also see the character set of the database

mysql> show create database mydb\G
*************************** 1. row ***************************
       Database: mydb
Create Database: CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)

mysql>

Perhaps you should load another table that has the matching character set:

CREATE TABLE anothertable LIKE mytable;

Change the whole table's character set

ALTER TABLE anothertable CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];

or change a column's character set

ALTER TABLE anothertable MODIFY col1 CHAR(50) CHARACTER SET utf8;

Then, have SequalPro load anothertable.

I guess to be less aggressive, just change the column's character set.

I had the same issue with a large data base. I switched the encoding to western latin (iso-1) and it resolved the issue.


MySQL Front - Workbench We have MySQL Front installed in our server, I can connect to it with no problems, there is a db with tables, everything fine.

Now I installed MySQL workbench (client) in the same server, but can't connect, I receive a "Malformed packet" error when I test connection. The port is 3306, hostname is localhost (as I'm logged in the server), user and password are the same.

Why shouldn't I be able to connect to the same server with workbench? Actually I need to connect to it using Entity Framework, but I can't due to this error.

I don't know what info would be useful (feel free to ask anything), I can only guarantee the windows service is running, the firewall is not blocking (I even tried disabling it).

If I try to connect with workbench using an "invalid" username (e.g "foobar"), the connection is sucessful (which is strange imo), but then I can only see the "test" database, and I can't see the actual database I want to (as in MySQL Front).

After reseting root password, I could connect with workbench. I still can't connect with old users (which were already created), but connecting with root and creating new users with workbench allows me to connect with these new users.

It looks it has something to do with the password, or the way MySQL Server stores password for users. I don't know the actual reason for the error, but this solved my problem.

Thanks for the suggestions!

This problem still exists. It may be caused by password related issues, but this problem is only MySqlWorkbench related, while a bunch of third party tools like Heidi, Database Browser, DBDesigner etc, works as a charm with no problem.


Availability Group database stays in Reverting for too long after a failover Architecture:
I have 2 Node Sync-Commit AlwaysOn configuration running on Multi-Subnet Failover Cluster. Primary node is in Europe and Secondary node is in US. I have only one database in the Availability Group which is OperationsManager db of SCOM.

Primary and Secondary Hosts are identical.
SQL Server Version on both boxes: 13.0.5237 & Windows Core
Update: I patched both servers to 10.0.5270.0 , it didn't help.
DB VLF count is only 27.

Problem:
When I initiate a failover, database fails over from Primary to Secondary node successfully in seconds. However, new secondary(old primary) database goes into Reverting / In Recovery phase and stays there for 30 minutes approximately.I also experienced same thing while failing back to original primary box so it's a problem which occurs both ways.

Findings:
I searched about this on internet and read documentation to investigate the issue. When role change from Primary to Secondary is finished, new secondary database goes through 3 phases:

Synchronization State: “NOT SYNCHRONIZING” ; Database State: ONLINE

Synchronization State: “NOT SYNCHRONIZING” ; Database State: RECOVERING

Synchronization State: “REVERTING” ; Database State: RECOVERING

In my case, all time has spent on last step. I also monitored the undo process by looking into perfmon counter "SQLServer:Database Replica Log remaining for undo"

I checked primary site before failover tests to spot any long running transactions or open transactions but couldn't find one. After failover, "Log remaining for undo" was around 30MB and it took 30 minutes for secondary database to go back to "Synchronized" state. When taking into consideration that we are running in Sync-Commit mode and there is a little workload on primary, it shouldn't take 30 minutes for redo phase imho.

SQL Server Error Log: I found this strange messages.

Remote harden of transaction 'RECEIVE MSG' (ID 0x000000004d52c65a 0001:01c4e415) started at Feb 22 2019  2:55PM in database 'OperationsManager' at LSN (2558:107841:1) failed.
Remote harden of transaction 'GhostCleanupTask' (ID 0x000000004d6d15aa 0001:01c4eaa0) started at Feb 22 2019  2:59PM in database 'OperationsManager' at LSN (2558:107843:46) failed.

Failover starts:

Failover ends:

All in all

Have you ever seen this problem before? Do you have any recommandations?

One thing to check whenever database recovery is running long, whether it's a normal restore or an AG failover, is your VLF count.  Having lots of VLFs (thousands or tens of thousands), or VLFs of unusual size (one or two extremely large VLFs) will cause this process to slow to a crawl.

Run the following command on your the database in question:

USE YourDatabaseName;
GO

DBCC LOGINFO;

Note: if you're on SQL Server 2016 SP2 or newer, you can use this dynamic management function instead of the DBCC command: sys.dm_db_log_info

The number of rows that comes back is the number of VLFs you have.  If that number is very large, or if the FileSize column shows extreme outliers among your VLFs, then you can likely resolve the slow recovery problem by (at a high level):

shrinking the log file as small as possible
growing it back out to its target size
making sure autogrowth is set to a reasonable number based on your typical log growth rate and the frequency of your transaction log backups

The details of fixing VLF sizing issues have been covered extensively elsewhere, here is one example: A Busy/Accidental DBA’s Guide to Managing VLFs

This code always works for me in the similar situation:

ALTER DATABASE [Your Database] SET HADR SUSPEND;

GO

ALTER DATABASE [Your Database] SET HADR RESUME;

GO