Question response time on StackOverflow I thought that this was fun to share. I was wondering when should I give up all hope that my question would ever be answered and begin to start crying. So, I wrote a query for the StackOverflow DB that would give me that answer (in general). It calculates how many and what percentage of questions have been anwsered within an hour, day, week, month, 3 months, year and over a year. So if you like you can find that out yourself. Btw, the difference between the 2010 and full version shows some improvement, so that is very positive. :-P

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('Comments') AND name = 'IX_CreationDate')
CREATE NONCLUSTERED INDEX IX_CreationDate
ON dbo.Comments(CreationDate)
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('Posts') AND name = 'IX_PostID_CreationDate_Title')
CREATE NONCLUSTERED INDEX IX_PostID_CreationDate_Title
ON dbo.Posts(Id, CreationDate,Title)
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('Comments') AND name = 'IX_PostID_Text')
CREATE NONCLUSTERED INDEX IX_PostID_Text
ON dbo.Comments(PostId)
INCLUDE(Text)
GO

WITH BASE AS
(

SELECT
    (SELECT COUNT(*)
    FROM Posts T1
    LEFT JOIN Comments T2 ON T1.Id = T2.PostId
    WHERE T2.CreationDate = (SELECT MIN(T3.CreationDate)
                                FROM Comments T3
                                WHERE T3.PostId = T1.Id)
    AND DATEDIFF(minute, T1.CreationDate, T2.CreationDate) BETWEEN 0 AND 59 ) AS WithinHour,

    (SELECT COUNT(*)
    FROM Posts T1
    LEFT JOIN Comments T2 ON T1.Id = T2.PostId
    WHERE T2.CreationDate = (SELECT MIN(T3.CreationDate)
                                FROM Comments T3
                                WHERE T3.PostId = T1.Id)
    AND DATEDIFF(minute, T1.CreationDate, T2.CreationDate) BETWEEN 60 AND 1440) AS WithinDay,

    (SELECT COUNT(*)
    FROM Posts T1
    LEFT JOIN Comments T2 ON T1.Id = T2.PostId
    WHERE T2.CreationDate = (SELECT MIN(T3.CreationDate)
                                FROM Comments T3
                                WHERE T3.PostId = T1.Id)
    AND DATEDIFF(minute, T1.CreationDate, T2.CreationDate) BETWEEN 1441 AND 10080) AS WithinWeek,

    (SELECT COUNT(*)
    FROM Posts T1
    LEFT JOIN Comments T2 ON T1.Id = T2.PostId
    WHERE T2.CreationDate = (SELECT MIN(T3.CreationDate)
                                FROM Comments T3
                                WHERE T3.PostId = T1.Id)
    AND DATEDIFF(minute, T1.CreationDate, T2.CreationDate) BETWEEN 10081 AND 44640) AS WithinMonth,

    (SELECT COUNT(*)
    FROM Posts T1
    LEFT JOIN Comments T2 ON T1.Id = T2.PostId
    WHERE T2.CreationDate = (SELECT MIN(T3.CreationDate)
                                FROM Comments T3
                                WHERE T3.PostId = T1.Id)
    AND DATEDIFF(minute, T1.CreationDate, T2.CreationDate) BETWEEN 44641 AND 132480) AS Within3Month,

    (SELECT COUNT(*)
    FROM Posts T1
    LEFT JOIN Comments T2 ON T1.Id = T2.PostId
    WHERE T2.CreationDate = (SELECT MIN(T3.CreationDate)
                                FROM Comments T3
                                WHERE T3.PostId = T1.Id)
    AND DATEDIFF(minute, T1.CreationDate, T2.CreationDate) BETWEEN 132481 AND 525600) AS WithinYear,

    (SELECT COUNT(*)
    FROM Posts T1
    LEFT JOIN Comments T2 ON T1.Id = T2.PostId
    WHERE T2.CreationDate = (SELECT MIN(T3.CreationDate)
                                FROM Comments T3
                                WHERE T3.PostId = T1.Id)
    AND DATEDIFF(minute, T1.CreationDate, T2.CreationDate) BETWEEN 525601 AND 7884000) AS OverAYear,

    (SELECT COUNT(*)
    FROM Posts T1
    LEFT JOIN Comments T2 ON T1.Id = T2.PostId
    WHERE T2.CreationDate = (SELECT MIN(T3.CreationDate)
                                FROM Comments T3
                                WHERE T3.PostId = T1.Id)
    AND DATEDIFF(minute, T1.CreationDate, T2.CreationDate) > -1) AS Total
)

,

ResponseTime AS (

SELECT
    WithinHour,
    CAST(CAST(WithinHour AS numeric(10,2)) / CAST(Total AS numeric(10,2)) * 100 AS numeric(5,2)) AS WithinHourPerc,
    WithinDay,
    CAST(CAST(WithinDay AS numeric(10,2)) / CAST(Total AS numeric(10,2)) * 100 AS numeric(5,2)) AS WithinDayPerc,
    WithinWeek,
    CAST(CAST(WithinWeek AS numeric(10,2)) / CAST(Total AS numeric(10,2)) * 100 AS numeric(5,2)) AS WithinWeekPerc,
    WithinMonth,
    CAST(CAST(WithinMonth AS numeric(10,2)) / CAST(Total AS numeric(10,2)) * 100 AS numeric(5,2)) AS WithinMonthPerc,
    Within3Month,
    CAST(CAST(Within3Month AS numeric(10,2)) / CAST(Total AS numeric(10,2)) * 100 AS numeric(5,2)) AS Within3MonthPerc,
    WithinYear,
    CAST(CAST(WithinYear AS numeric(10,2)) / CAST(Total AS numeric(10,2)) * 100 AS numeric(5,2)) AS WithinYearPerc,
    OverAYear,
    CAST(CAST(OverAYear AS numeric(10,2)) / CAST(Total AS numeric(10,2)) * 100 AS numeric(5,2)) AS OverAYearPerc,
    Total

FROM BASE

)

SELECT *
FROM ResponseTime


SSAS Cube in Excel - Show only applicable rows with KPI? I have a cube built in SSAS 2008r2.  I'm exposing it for the moment through an excel PivotTable.

I recently added a KPI to the cube, and when I show the KPI in my PivotTable interface it causes non-applicable dimension values to be shown at the current level.

For instance, suppose I have customers:

CustomerA
CustomerB
CustomerC

And States:

NY
VA
TX

When I look at the PivotTable without the KPI, CustomerB has no rows under NY because they have no locations there.

When I add my KPI in and display just the value of the KPI, all is well.

When I add in the status indicator (i.e. traffic light or whatever),  I see every customer under every state.

Is this expected behavior? Is there a way around it?  I like the KPI but it defeats the purpose if I get every value of my dimension at each level.

Some more details:

The Value Expression for the KPI is just a calculated measure, which works fine.

The Status Expression is:

CASE
    WHEN KpiValue("MyKPI") / KpiGoal("MyKPI") > .90
        THEN 1
    WHEN KpiValue("MyKPI") / KpiGoal("MyKPI") <= .90
        AND KpiValue("MyKPI") / KpiGoal("MyKPI") > .75
    THEN 0
ELSE -1
END

Thanks to @ConcernedOfTunbridgeWells for pointing me in the right direction on this.

The issue was with the status expression.

Since it ALWAYS returns a 1, 0 or -1 it didn't account for NULLs.

I added a check at the beginning of the expression:

CASE WHEN IsEmpty(KpiValue("MyKPI")) THEN NULL

...which correctly leaves  those fields empty.


Can I set a filegroup of database offline,change the location of files and bring it online again? I found that we can only take filegroup offline through t-sql,but to bring that filegroup online we need to restore the whole database.

There is no T-SQL command to take a filegroup offline, though an individual file can be set offline.

From the documentation, ALTER DATABASE File and Filegroup Options (Transact-SQL):

  Use this option only when the file is corrupted and can be restored. A file set to OFFLINE can only be set online by restoring the file from backup. For more information about restoring a single file, see RESTORE (Transact-SQL).

To relocate files, follow the instructions in the documentation, or see the related Q & A:

How do I move SQL Server database files?


Question regarding decimal arithmetic I think my understanding of precision vs scale might be incorrect as the following example produces values that do not make sense to me. decimal(32, 14) rounds the result to 6 decimal places, while the decimal(18, 14) rounds to 19. My understanding of decimal is decimal(p, [s]), where p is the total number of digits and s is the number of digits after the decimal (i.g., decimal(10, 2) would result in 8 digits to the left of the decimal and 2 digits to the right). Is this not correct?

I created a small example that illustrates the seemingly odd behavior:

--------------------
-- Truncates at pipe
-- 1.043686|655...
--------------------
declare @dVal1 decimal(32, 14) = 10
declare @dVal2 decimal(32, 14) = 9.581419815465469

select @dVal1 Val1, @dVal2 Val2, @dVal1 / @dVal2 CalcResult

----------------
-- Most accurate
----------------
declare @dVal3 decimal(18, 14) = 10
declare @dVal4 decimal(18, 14) = 9.581419815465469

select @dVal3 Val3, @dVal4 Val4, @dVal3 / @dVal4 CalcResult

So on to the question, what is it that I am missing to understand this? The articles and msdn blogs I have read don't seem to provide clarity (at least to my thought process). Can someone explain to me why a higher precision seems to result in a loss of scale?

Your understanding is correct though you have 1 too many digits for @dVal2 and @dVal4 which is why you see these being rounded up to 7 (9.58141981546547) for the last digit in your select

As for the division rounding, it's hidden in the middle of the docs.

  In multiplication and division operations we need precision - scale
  places to store the integral part of the result. The scale might be
  reduced using the following rules:

  The resulting scale is reduced to min(scale, 38 – (precision-scale))
  if the integral part is less than 32, because it cannot be greater
  than 38 – (precision-scale). Result might be rounded in this case.

  The
  scale will not be changed if it is less than 6 and if the integral
  part is greater than 32. In this case, overflow error might be raised
  if it cannot fit into decimal(38, scale)

  The scale will be set to 6 if
  it is greater than 6 and if the integral part is greater than 32. In
  this case, both integral part and scale would be reduced and resulting
  type is decimal(38,6). Result might be rounded to 6 decimal places or
  overflow error will be thrown if integral part cannot fit into 32
  digits.

So in your first case decimal(32,14), the scale is being set to 6 digits because the resulting value decimal(64,28) has a scale = 28 which is > 6 and the integral part (64-28) = 36 is > 32 as defined in the last rule above. Thus, decimal(38,6)

In your second case decimal(18,14), the first rule is being applied for your scale for the resulting value of decimal(36,28) to min(28, 38 -(36-28)) = min(28,30) = 28. Thus, decimal(38,28)


Use EMCLI to update OEM Oracle Home Path Is is possible to use the Enterprise Manager Command Line Interface (EMCLI) to change the Oracle Home Path?

We recently upgraded many databases, listeners, and ASMs, and I'd like to automate this process:

Select the target
Go to Target Setup --> Monitoring Configuration
Change "Oracle Home Path", click Next, click Submit.

I looked through the EMCLI Verb Reference and could not find a way to do this.

Assuming your DB target name as seen in EM is ORCL11.WORLD and you want to set it's Monitoring Configuration's Oracle Home to /oracle/product/11.2.0.4, the syntax would be the following:

emcli modify_target -type='oracle_database' -name='ORCL11.WORLD' -properties='OracleHome:/oracle/product/11.2.0.4'

Probably would be similar approach for Listeners, ASM, etc.

Version note: This worked for me on EM 12cR4.


SQL 2008: agent job error in opening excel files using DTS package I have a SQL 2008 server (SQL2008S1) with Excel installed on it. I had to migrate databases and DTS packages from a SQL 2000 server (SQL2000S2) to this server. 1 DTS package is trying to access an Excel file on a remote server like \\SQL2000S2\C$\directory\someExcelFile.xls. The SQL server and agent on SQL2008S1 are both running under same service account (Domain\SQL2008S1_service) and has full permissions on \\SQL2000S2\C$\directory. I even granted Domain\SQL2008S1$ (machine name) full access to \\SQL2000S2\C$\directory\

When I open the DTS package on SQL2008S1 and execute it manually, it runs perfectly fine. But when I schedule it as job, the job reports:

  Executed as user:
  Domain\SQL2008S1_service.
  ...p_DTSExecutePackageTask_5   DTSRun
  OnError:
  DTSStep_DTSExecutePackageTask_5, Error
  = -2147467259 (80004005)      Error string:  DTSStep_DTSExecuteSQLTask_2:
  The Microsoft Jet database engine
  cannot open the file
  '\\SQL2000S2\C$\directory\someExcelFile.xls'.
  It is already opened exclusively by
  another user, or you need permission
  to view its data.

I am 100% sure that the files are not open and I thought I have granted enough rights to the SQL agent/SQL service account.

Any help please?

Have you granted the account Domain\SQL2008S1_service admin rights to the server SQL2000S2?  You will need to do so in order to use the C$ network share.


What objective factors indicate it's time to implement SQL Server replication? I am trying to strike a balance between high performance of our database and ease of maintenance.  We are considering using replication to improve performance, by replicating our SSRS reports to a physically separate database from our transactional database.  However, enabling replication has a number of drawbacks from a developer point of view:

It makes schema changes more difficult
It interferes with our automated integration/build server
It seems to make it difficult to implement SQL source control

My question is: When do you know it's time to go with replication in light of these drawbacks?  How do you decide whether the additional complexity justifies the gains?

We've used it before so setting it up is not a problem.  This is more about making the decision to, or not to, enable it.  I'm looking for some object performance metrics that others have observed with replication.

Of course the best thing would be to do some simulated load testing on our own servers and figure it out ourselves, but I am hoping there are some general guidelines out there.

Replication should be considered during the design phase of the application.  If you have a geographically distributed workforce/userbase, having regional databases and central databases may make sense.  Disconnected databases on laptops can "synchronize" when they finally reconnect with the network (think sales staff on the road).

As for reporting purposes, replication is NOT the answer.  Most of the performance problems in a reporting environment stem from the fact that the reports are being written against a system that is configured for online transaction processing (OLTP).

Replication for reporting purposes is simply moving your OLTP data to another server, but keeping the same report-unfriendly structure.  In essence, you are throwing more hardware at the problem and increasing your maintenance costs, but for marginal gains.

What you should be looking at is how to get the specific data that your reports need, and transform them into a more useful format.  Create a feed that grabs new transactions from your production database and stores it in a reporting database, preferably on a separate server.  Every time the feed runs, it should grab all the data that is newer than the last time it ran, transform that data, and store it.  The reports that you currently are running will give you a good idea of the types of transformations required.

By following this approach, you are going to reap huge performance benefits.


SQL Query on partitioning does not run When I run the following SQL command

select payee_id, start_dt,
       row_number() over(partition by payee_id order by start_dt) as rn
from xxx
where rn = 1

I got an error :

  error Invalid identifier 'rn'

Please advise.

rn cannot be referenced in WHERE because it's defined in the SELECT clause. Try using a subquery instead:

SELECT *
FROM (
    select payee_id,start_dt,
           row_number() over(partition by payee_id order by start_dt) as rn
    from xxx
) AS sub
where rn = 1;

For more reading, google "logical query processing order".


Is there a way to have SQL Server format a number into ordinal form? Data 1, 2, 3, 4, 11, 21, 99

I want to get 1st, 2nd, 3rd, 11th, 21st, and 99th

I could roll my own, but is there any function built into SQL server that could so this for me?
Corollary:  I will also be looking for a function to turn numbers into words, as in:

72,451 ->  seventy-two thousand, four hundred fifty-one

And would expect it (if it exists) to be in the same family of formatting methods.

No, there is not any built in function that will do this. This is something that should be handled in the presentation layer and not in the data coming from the database.

EDIT: based on the presence of a "crummy presentation layer", IF this would have to be done in the database I suggest creating a UDF to convert the values to ordinal or words. IT definitely would not be pretty.

From a comment: For the second part of the question, Daniel used the script found here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86067


Two MySQL query results are different, but shouldn't be To the best of my knowledge, these results (especially the data in the 'destination' field) should be the same, but I can't find out why they're different:

mysql> select * from cc_did_destination where id_cc_did=1988\G
*************************** 1. row ***************************
            id: 2256
   destination: SIP/t564/12505551234
      priority: 1
    id_cc_card: 2157
     id_cc_did: 1988
  creationdate: 2012-01-31 07:59:01
     activated: 1
secondusedreal: 0
     voip_call: 1
     validated: 1
1 row in set (0.00 sec)

mysql> SELECT cc_did.did, cc_did.id, cc_did_destination.destination     FROM cc_did, cc_did_destination  WHERE cc_did.iduser='2157' AND cc_did_destination.id_cc_card=cc_did.id\G
*************************** 1. row ***************************
        did: 7805551234
         id: 1988
destination: SIP/someuser
*************************** 2. row ***************************
        did: 6045551234
         id: 1997
destination: SIP/macaddress-1
2 rows in set (0.01 sec)

In fact, the data for the 'destination' field is correct in the first select statement. The data in the second query is old and should have been replaced with the data we see in the first query.

As per Rachel below, what I'm trying to do is this; Join this table:

mysql> SELECT destination, id_cc_card, id_cc_did FROM cc_did_destination where id_cc_card=2157;
+----------------------+------------+-----------+
| destination          | id_cc_card | id_cc_did |
+----------------------+------------+-----------+
| SIP/t564/12505551234 |       2157 |      1988 |
| SIP/testuser         |       2157 |      1997 |
+----------------------+------------+-----------+
2 rows in set (0.00 sec)

To this table:

mysql> select did from cc_did where id=1988;
+------------+
| did        |
+------------+
| 7805553315 |
+------------+
1 row in set (0.00 sec)

Except when I try to do that with a JOIN statement, I get this instead:

mysql> SELECT destination, id_cc_card, did FROM cc_did_destination as A join cc_did on A.id_cc_card=cc_did.iduser WHERE A.id_cc_card=2157;
+----------------------+------------+------------+
| destination          | id_cc_card | did        |
+----------------------+------------+------------+
| SIP/t564/12505551234 |       2157 | 6045553939 |
| SIP/testuser         |       2157 | 6045553939 |
| SIP/t564/12505551234 |       2157 | 7805553315 |
| SIP/testuser         |       2157 | 7805553315 |
+----------------------+------------+------------+
4 rows in set (0.01 sec)

Which makes even less sense!

UPDATE:
I found the problem, and it's all about matching the wrong fields. It should be:

WHERE cc_did.id=cc_did_destination.id_cc_did;

instead of:

WHERE cc_did.id=cc_did_destination.id_cc_card;

Here's a simpler way of looking at your queries

select *
from A
where A.did = 1988

-- returns record where card = 2157

and

select *
from B join A on A.card = B.id
where B.userid = 2157

Since you want the record where a.card = 2157 and a.card = b.id then either

Change your where statement so it is looking for b.id = 2157 or a.card = 2157
Or change your join so you're joining a.card = b.userid


Fileshare witness for 3 node Multisubnet AAG setup I am planning for a 3 node AG, 2 in DC and 1 in San Jose. Host OS will be Windows Server 2012 R2.

We dont have an option of a 3rd site, so my question is where can I place the FSW if I have to do a planned manual failover?

Also, once manual failover is performed, should I be disabling the votes of the nodes in the primary site?

Please advise.

I am planning for a 3 node AG, 2 in DC and 1 in San Jose. Host OS will be Windows Server 2012 R2.

I'm going to assume that the 2 in DC are your favored site (I feel dirty typing that) and San Jose is truly only for DR.

  We dont have an option of a 3rd site...

That's a shame - if you were running Windows Server 2016 there is a cloud witness.

  ...so my question is where can I place the FSW if I have to do a planned manual failover?

Quite literally... anywhere. The witness may act as a voter (if it has a vote, depends on if dynamic quorum is turned on, the current state of nodes and votes, etc.) and will be used for arbitration.

Doing a manual failover involves having quorum and a synchronized secondary. Nothing in there requires a witness.

Let's dig a little deeper. The point of having a witness is to help with having enough votes to keep quorum and for arbitration so that you don't split brain. Since you only have the single vote (I'm assuming defaults, here) in San Jose - it's a moot point. If you truly had a DR issue you'd have (from the point of SJC) a single vote out of a possible of 3, which isn't enough to have quorum and thus the cluster service would shut down. It doesn't matter what the AG settings are, that's how WSFC works. You'd have to force quorum, regardless of if you can see to witness or not.

We're not going to get into everything with WSFC but that's the gist of your current setup. Thus, I'd put the witness where it will do the most good, which would be at site DC.

  Also, once manual failover is performed, should I be disabling the votes of the nodes in the primary site?

What issue are you trying to solve? If you do a manual failover then it is implied that the replicas are synchronous commit and synchronized also that the cluster has quorum.

If the issue that you are trying to solve is that you don't want a failover to happen or you want the AG to stay in SJC after the failover - then yes, you'll need to adjust the node weight to 0 for the DC replicas. However, if this is done, you're a single point of failure again as any hiccup on the SJC node means your cluster is down (I mean, you removed the voters - we have a bunch of watchers but no voters).

  I mentioned about the manual failover with 2 scenarios in mind 1) To failover the AAG to SanJose in case any maintenance activities have to be performed on the DC nodes.

In that case I'd do maintenance on a node at a time and keep it within DC, not immediately go out to SJC. You could also ADD a node at any time in DC just for the purposes of that. If you application or user base is mostly in DC, the added latency to SJC might not be palatable. I can't tell you if it will or won't be.

Summary: First I would try to keep the AG in DC and only use SJC as DR, unless all replicas are Synchronous commit, then it doesn't really matter. Taking away the votes would be beneficial in the case for keeping the cluster up, but still wouldn't help all that much.

  2) In case of DR - as you mentioned - a force fail over is the only option as I do not have enough votes to sustain quorum. Is it a better idea to have 2 nodes at SanJose as well?

"It depends™" Again, what are you trying to solve? I wouldn't over-engineer a solution to try and solve every possible edge case or problem. Have your top 3-5 true issues and solve for that.

  we do have a 3rd datacenter in Atlanta - can that be used as a third site for the witness?

So you lied to me! I see how it is...

Yes, you can and should absolutely use that location for a FSW :)


INSERT ON DUPLICATE KEY UPDATE to UPDATE multiple rows in single query I've come across people recommending using ON DUPLICATE KEY UPDATE to UPDATE multiple rows using a single query. Unfortunately no one seems to be using clean SQL (defining an obvious primary key of id and using at least two rows) and so I have not been able to confirm the effect to a satisfactory degree.

Presume all the data in the table exists and that some of the quantity rows will be different:

INSERT INTO variations (id, name, quantity)
VALUES
(1, 'John', 4),
(2, 'Amy', 5),
(3, 'Elizabeth', 6)
ON DUPLICATE KEY UPDATE
id=VALUES(id),
name=VALUES(name),
quantity=VALUES(quantity);

If the quantities in the table are 1, 2 and 3 then what I've constituted is that the above query should UPDATE them to 4, 5 and 6. However manually running the command returns Affected rows: 0 and the data in my application is beyond any doubt not changing.

Clarifications:

The name rows may have their values change, I'm trying to keep the code simple.
The id is the primary key and there are no other keys.
No UPDATE has to happen if the all of the data for all of the row are the same between the query and table row.
Sometimes only one column of data might change, sometimes all columns except id might change.
There is no clarification in regards to VALUES() if it is using the $row (e.g. from PHP) data to UPDATE if it differs from what is already in the row.
There is no clarification in regards to if all the columns must be specified (besides the the primary or a unique key).
The documentation at both MariaDB and MySQL leaves a nebulous mystical cloud of unknowns.
I'm running MariaDB 10.2.6 (MySQL 5.5.5).

I'm tempted just to dump the code in to a loop to run an UPDATE query per row as I don't have time to create a huge project out of this. None of the existing threads here or at SO properly address the multiple UPDATE single query issue (examples of using case look like monstrous nightmares to construct) and so I'm hoping for an answer that can clarify this beyond any doubt so I can learn from this frustrating challenge.

Thanks to #danblack and dbfiddle.uk in short I determined that I was missing one of the columns in the ON DUPLICATE KEY UPDATE.

Every single column must be defined for ON DUPLICATE KEY UPDATE to work!

I effective had columns id, a, b, c and d in the table. I forgot to define one of the columns (that wasn't id as that is the first half of the requirement).

Won't Work (missing column):

ON DUPLICATE KEY UPDATE
id=VALUES(id),
a=VALUES(a),
c=VALUES(c),
d=VALUES(d);

Will Work (has all table columns):

ON DUPLICATE KEY UPDATE
id=VALUES(id),
a=VALUES(a),
b=VALUES(b),
c=VALUES(c),
d=VALUES(d);


ACID violation in Postgres 9.1? I am using a Postgres DB to implement a scheduling of jobs for a large number of computers/processes. To make the story short, every job has its id, all scheduling is implemented with three tabes: all jobs, currently running jobs, and already completed jobs.

The key functionality of the scheduling is to (1) request a job and (2) inform DB about a completed job. Requesting a job takes any id from the job list, which is not in the running table and not in the completed table:

insert into piper.jobs_running
select x.fid from (
  SELECT fid FROM piper.jobs
  except
  select fid from piper.jobs_running
  except
  select fid from piper.jobs_completed
 ) as x limit 1
returning(fid)

Completing the job removes it from the running list and inserts it into the completed list. Since it is not concurrency-specific I ommit the SQL commands (it takes something from tens of minutes to a couple of hours to finish a job.)

It came as a nasty surpirse to me, that two processes runing exactly the same query above (pretty much requesting the job at the same time) may get the same job id (fid). The only possible explanation that I am comming up is that Postgres is not rely ACID conform. Comments?

Additional information: I set the transactions to be serializable (in postgresql.conf set default_transaction_isolation = 'serializable'). Now the DBMS fails the transactions in case the isolation is not fullfilled. is it possible to force Postgres to restart them automatically?

Specific issues with the query

PostgreSQL defaults to READ COMMITTED isolation, and it looks like you're not using anything different. In READ COMMITTED each statement gets its own snapshot. It cannot see uncommitted changes from other transactions; it's as if they simply haven't happened.

Now, imagine you run this simultaneously in three sessions, on a setup with three entries in jobs, zero in jobs_running and zero in jobs_completed:

insert into piper.jobs_running
select x.fid from (
  SELECT fid FROM piper.jobs
  except
  select fid from piper.jobs_running
  except
  select fid from piper.jobs_completed
 ) as x limit 1
returning(fid)

Each run will select the three jobs in jobs. Because their snapshots were all taken before any of them committed a change, or even created the uncommitted row, *all of them will find zero rows in jobs_running and jobs_completed.

So they all claim a job. Probably the same job, because even though there's no ORDER BY, the scan order will be the same.

Locking

Row locking crosses transnational boundaries, and lets you communicate between transactions to enforce ordering. So you might think this would solve your problem, but it won't.

If you take a FOR UPDATE lock on the row entry, so the row is locked exclusively, the lock is held until the transaction commits or rolls back. So you'd think that then the next transaction would get a different row, or if it tried to get the same row it'd wait for the lock to release, see that there was now an entry in jobs_running, and skip the row. You'd be wrong.

What'll happen is that you'll lock all rows. One transaction will successfully get the locks on all rows. The other transactions, which will do the same index or sequential scan, will generally try to lock the rows in the same order, get stuck on the first lock attempt, and wait for the first transaction to roll back or commit. If you're unlucky, they might start locking different sets of rows and deadlock against each other instead, causing a deadlock abort, but usually you're just getting no useful concurrency.

Worse though, the first transaction picks one of the rows it locked, inserts a row into jobs_running and commits, releasing the locks. Another transaction is then able to continue, and locks all the rows.... but it doesn't get a new snapshot of the database state (the snapshot gets taken at statement start), so *it can't see that you inserted a row into jobs_running. Thus, it grabs the same job, inserts a row for that job into jobs_running, and commits.

Condition re-checks

PostgreSQL has a quirky feature not included in most databases where, if a transaction blocks on a lock, it re-checks that the selected row still matches the lock condition when it gets the lock after the first transaction commits.

That's why the example in https://stackoverflow.com/questions/11532550/atomic-update-select-in-postgres works - it relies on qualifier re-checks in WHERE clauses after reclaiming a lock.

The use of locking forces everything to run serially, so in practice you might as well have a single connection doing the work.

Isolation, ACID, and reality

Transaction isolation in PostgreSQL is not the perfect ideal where transactions run concurrently, but their effects are the same as if they were executed serially.

The only real world database that would deliver perfect isolation would be one that exclusively locks every table when a write transaction first accesses it, so in practice transactions can only be concurrent access if it's to different parts of the DB. Nobody wants to use such a database in situations where concurrency is desirable or useful.

All real world implementations are compromises.

READ COMMITTED default

PostgreSQL's default is READ COMMITTED isolation, a well-defined isolation level that permits non-repeatable and phantom reads, as discussed in the PostgreSQL manual on transaction isolation.

SERIALIZABLE isolation

You can request stricter SERIALIZABLE isolation on a per-transaction basis or as a per-user, per-database or (not recommended) global default. This provides much stronger guarantees, though still not perfect, at the cost of forcibly rolling back transactions if they interact in ways that couldn't happen if they had run serially.

Because your concurrent queries will always try to grab the first job, no matter how many jobs there are, all but one will abort with serialization failures. So in practice you don't get any useful concurrency and might as well have a single connection handing out jobs to workers.

(Note that prior to PostgreSQL 9.1 SERIALIZABLE offered much weaker guarantees and would not detect quite a lot of cases where transactions were interdependent.)

Auto-re-run SERIALIZABLE

PostgreSQL does not auto-re-run SERIALIZABLE transactions that abort due to serialization failures. There are cases where this would be quite useful, but other cases where doing so would be completely wrong and dangerous - especially where read/modify/write cycles via the application are involved. At this time there is no support for automatically rerunning transactions on serialization failures. The application is expected to retry.

Don't write a DIY queuing system

It looks like what you're trying to do is write a queuing system. Consider not doing that. Writing a robust, reliable and correct queueing system is hard, and there are a few good ones already available that you could adopt. You have to handle things like crash-safety, what happens when someone takes a task then fails to complete it, race conditions around completion just as you give up on the task handler completing it, etc. There are lots of subtle concurrency problems. Don't try to DIY.

9.5 and SKIP LOCKED

PostgreSQL 9.5, still in development, adds a feature that makes queuing quite a bit easier.

It lets you say that if when you SELECT ... FOR UPDATE, if a row is locked, you should ignore it and carry on to find the next not-locked row. This is very useful when combined with a LIMIT as it makes it possible to say "find me the first row someone else isn't already trying to claim". So it becomes very simple to write queuing that grabs jobs safely and concurrently.

Until that feature is available I strongly suggest that you stick to single connection queue management, or use a well-tested task queue system.


SQL simple update returning an error Oracle SQL and Microsoft SQL Server:-

I have a single table in which I wish to set two 'USERFIELD' column values, for all rows having their other column values set to a given literal.

SET ESCAPE ON
UPDATE DOCUMENT
SET USERFIELD4 = 'Europe \& International', USERFIELD7 = 'E\&I Engineering'
WHERE DOCNO in
   (SELECT d.DOCNO FROM DOCUMENT d
    WHERE d.USERFIELD4 = 'Combat Air'
      and d.USERFIELD3 = 'Engineering'
      and d.WITHDRAWN=0)

DOCNO is the table unique key column.

So, what I want is that if for a given row, USERFIELD4 = 'Combat Air' and USERFIELD3 = 'Engineering' and WITHDRAWN=0, then for that same row, set USERFIELD4 to 'Europe & International' and set USERFIELD7 to 'E&I Engineering', and for this to apply to all rows in the table.

I am getting the following error:

  SQL Error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The issue was a poorly written QueryPlan in response to a defined Trigger as per @Aaron Bertrand presumed. I rewrote the QueryPlan to return more than one row which was what was causing the rather misleading error message.


Is is possible to combine local variables in a case statement? DECLARE @CalcQuery varchar(340)
SET @CalcQuery = (SELECT
    sum(T0.[LineTotal])
FROM
    RDR1 T0
        INNER JOIN ORDR T1 ON T0.[DocEntry] = T1.[DocEntry]
        INNER JOIN [@SES_SSNSBRG] T2 ON T0.[ItemCode] = T2.[U_SES_ItemCode]
        INNER JOIN [@SES_SEASONS] T3 ON T2.[U_SES_SEASON] = T3.[Code]
WHERE
    T3.[Name] = 'Wild Heart SP-1'
    AND
    T0.[Project] = 'ATL Market Molina')

SELECT DISTINCT
T0.[Project],
CASE
   WHEN T0.[Project] = 'ATL Market Molina'
   THEN @CalcQuery
   END [Season Total]
FROM
RDR1 T0
INNER JOIN ORDR T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN [@SES_SSNSBRG] T2 ON T0.[ItemCode] = T2.[U_SES_ItemCode]
INNER JOIN [@SES_SEASONS] T3 ON T2.[U_SES_SEASON] = T3.[Code]

Is there a way for me to Nest 50 Project codes and 7 Seasons without writing redundant SQL and Case statements. I think that the only way to go would be to declare variables for the project codes and seasons. The only issue is that I'm unsure on how to merge them with @CalcQuery. I wrote out the SQL without the variables and it's long.

The output would be the total for the specific season 'Wild Heart' and project 'ATL Market Molina'. The company that requested this didn't plan ahead and created many order tags. T3.[Name] = 'Wild Heart SP-1' is the season name.

Is there any reason you aren't just using a group by?

SELECT T0.[Project], T3.[Name] AS [Season],
     sum(T0.[LineTotal]) AS [Season Total]
FROM RDR1 T0
INNER JOIN ORDR T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN [@SES_SSNSBRG] T2 ON T0.[ItemCode] = T2.[U_SES_ItemCode]
INNER JOIN [@SES_SEASONS] T3 ON T2.[U_SES_SEASON] = T3.[Code]
GROUP BY T0.[Project], T3.[Season];


Identical(?) SQL Server 2005 machines; query takes 2sec on one, 15min on the other The Environment:

We have two 32-bit Windows Server 2003 R2 machines running SQL Server 2005.
The hardware configurations are identical servers with Xeon 5160 CPU, 4GB RAM, and 13GB RAID0. AWE and /3GB flags are not enabled.

The servers were set up side-by-side using a predefined installation checklist, and ALL installed software is the same on both machines.

Every SQL server installation setting and patch level we know to check are identical.
One difference is that TEMPDB is 400MB on the fast machine, and 1.2GB on the slow machine. However, in both cases, we don't see any TEMPDB allocation taking place.

The Problem:

There is a stored procedure which runs in 2 seconds on one, but 15 minutes on the other.
During the additional 15 minutes, there is little to no disk activity, no memory usage changes, but one CPU core is pinned at 100% the entire time.

This behaviour persists even when the databases are backed up from one and restored to the other.

Since it is a stored procedure, the activity monitor and profiler don't show us any detail about where in the stored procedure this high CPU activity is taking place.

The Question:

What else should we be looking at?

Followup:

The slowness occurs in the FETCH NEXT statements for the following cursor definition:

DECLARE C CURSOR FOR
    SELECT X, Y
    FROM dbo.A
    WHERE X NOT IN (SELECT X FROM dbo.B)
    AND Z <=0
...
<snip>
...
FETCH NEXT FROM C INTO @X, @Y
FETCH NEXT FROM C INTO @X, @Y
...

Each of the FETCH statements--on a table containing only about 1000 rows--requires about 7.25 minutes. (No, I don't know why it does two in a row, need to ask the developers, but it does run correctly on both servers).

I'm a little suspicious of that "NOT IN (SELECT ...)", since it looks like Virtual Reads is really high.

Using a performance troubleshooting methodology like Waits and Queues identify the reason for the high CPU consumption, then appropriate action can be recommended once the bottleneck is identified.


How to log error details when using using try/catch for dynamic SQL backup commands When issuing a backup command within a stored procedure that uses a try catch and dynamic sql, the error messages are very general when compared to running the backup command directly.

Try/Catch within SP:

    begin try
        execute sp_executesql @sql;  -- a backup command
    end try
    begin catch
        print ERROR_MESSAGE();  -- save to log, etc.
    end catch

Results in

  50000:usp_Backup:117:  BACKUP DATABASE is terminating abnormally.

wheareas issuing the raw command:

    backup DATABASE someDb to disk...

Results in better details:

  Lookup Error - SQL Server Database Error: A nonrecoverable I/O error
    occurred on file "H:\FolderName\Filename.bak:" 112(There is not enough
    space on the disk.).

Is there a way to catch these details into variables within the stored procedure (to log, pass back to caller, for retry logic)?  It seems the details are coming through on the message channel but I would like them available within the SP.

When BACKUP DATABASE generates an error, it actually generates two. Unfortunately TRY/CATCH is not capable of capturing the first error; it only captures the second error.

I suspect your best bet to capture the real reason behind a failed backup is to automate your backups through SQLCMD (with -o to send output to a file), SSIS, C#, PowerShell etc. All of which will give you much greater control over capturing all of the errors.

The SO answer in the comment suggests using DBCC OUTPUTBUFFER - while it's possible, this does not seem like child's play at all. Feel free to have fun with this procedure from Erland Sommarskog's site, but this still doesn't seem to work well in combination with TRY/CATCH.

The only way I seemed to be able to capture the error message with spGET_LastErrorMessage is if the actual error does get thrown. If you wrap it in a TRY/CATCH the error gets swallowed and the stored procedure does nothing:

BEGIN TRY
  EXEC sp_executesql N'backup that fails...';
END TRY
BEGIN CATCH
  EXEC dbo.spGet_LastErrorMessage;
END CATCH

In SQL Server < 2012 you can't re-raise the error yourself, but you can in SQL Server 2012 and newer. So these two variations work:

CREATE PROCEDURE dbo.dothebackup
AS
BEGIN
  SET NOCOUNT ON;
  EXEC sp_executesql N'backup that fails...';
END
GO

EXEC dbo.dothebackup;
EXEC dbo.spGET_LastErrorMessage;

Or in 2012 and above, this works, but to a large degree defeats the purpose of TRY/CATCH, since the original error still gets thrown:

CREATE PROCEDURE dbo.dothebackup2
AS
BEGIN
  SET NOCOUNT ON;
  BEGIN TRY
    EXEC sp_executesql N'backup that fails...';
  END TRY
  BEGIN CATCH
    THROW;
  END CATCH
END
GO

EXEC dbo.dothebackup2;
EXEC dbo.spGET_LastErrorMessage;

In both of these cases, the error is still thrown to the client, of course. So if you're using TRY/CATCH to avoid that, unless there is some loophole I'm not thinking of, I'm afraid you'll have to make a choice... either give the user the error and be able to capture details about it, or suppress both the error and the actual reason.


Repacking RDS database with pg_repack isn't online While running pg_repack on a RDS Postgresql 9.6.3, got this failure for one of writers:

ProgrammingError: permission denied for relation log_24034858
CONTEXT:  SQL statement "INSERT INTO repack.log_24034858(pk, row)
VALUES(CASE WHEN $1 IS NULL THEN NULL ELSE (ROW($1.id)::repack.pk_24034858) END, $2)"

But pg_repack should allow writes to repacked tables, why did it fail here?

We found a similar issue. The issue appears to be that log tables created in the repack schema don't have the correct permissions. Our solution was to use ALTER DEFAULT PRIVALEGES to fix this:

CREATE EXTENSION pg_repack;
ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT INSERT ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT USAGE, SELECT ON SEQUENCES TO PUBLIC;


How to get one complete column in mysql? I have a script that will fetch Last_Error in mysql slave in a particular interval of time and write that to a file.

To fetch Last_Error, currently I am using below command in the script.

mysql -u root -pxxxxxxxxx -e "show slave status\G;" 2>&1 | grep -v "Warning: Using a password" |grep "Last_Error"

The challenge I am facing is that grep "Last_Error" will only show that particular line and not complete error. For example, one of my Last_Error look like below::

Last_Error: Error 'TRIGGER command denied to user 'user'@'xx.xx.xx.xx' for table 'table_name'' on query. Default database: 'Db_name'. Query: 'UPDATE
                    table
                 SET
                    user_status=0
                 WHERE
                    contractid ='id.000.000''

and one another look like ::

Last_Error: Error 'Duplicate entry '117583' for key 'PRIMARY'' on query. Default database: 'labs'. Query: 'insert into User (a,b,c,d) values ("Other","",now(),"")

So basically I can't use A4 kind of switch with grep. Is it possible to get one complete column in mysql or any other work around?

If you remove all newlines and whitespace from the output before grepping, it should work for you.

Use

mysql -u root -pxxxxxxxxx -e "show slave status\G;" 2>&1 | tr -d '\n\r \t' | grep -v "Warning: Using a password" | grep "Last_Error"

where the tr command removes the newline, carriage return, space, and tab characters from the input.


Run Multiple Scripts In One Transaction Across Multiple Servers We have deployment scripts that we need to run on databases that are spread across multiple servers. One script only runs on one database, but the scripts depend on each other. We are looking for a way to run all of the scripts as one big transaction so that all scripts either commit or rollback as a whole.

How do I do this?

I would prefer a way to do this from ADO.NET, but SSMS is cool, too.

My current solution (that does not work) is that I begin transaction in every database, run all my scripts, and then commit/rollback once everything is good. However, I can't run all my scripts since cross-database dependencies are blocking indefinitely.

I've run into a problem like this.  I used ADO.NET 4 with VB.

Here's what I did:

Created a .txt file with list of servers.
Dump the .txt file into a List Box.
Wrote the .NET application to scan through the list box, line by line, dumping each line into a text box on the form.
Replace the connection string on the SQL connection with the database currently in the text box.
Wrote the SQL scripts inside the command clause.
Execute the SQL command on the current database.
Next line.

Example:

    'Loop through the server names.
    For i = 0 To lbxServers.SelectedItems.Count - 1
        txtServer.Text = ""
        txtServer.Text &= lbxServers.SelectedItems.Item(i).ToString() & " "
        'Call your sub procedure which contains your sql connection and sql command.
    Next

That will get your loop going, then you need to write some sub procedures for your queries.
Like so:

    'Declare SQL connection variables.
    Dim connection As New Data.SqlClient.SqlConnection
    Dim dataAdapter As New Data.SqlClient.SqlDataAdapter
    Dim command As New Data.SqlClient.SqlCommand

    'Connect to the server next in the list box.
    connection.ConnectionString = "Server= " & txtServer.Text & "; integrated security=true"

    'SQL Statement to run on the server.
    command.CommandText = "TSQL"
    connection.Open()
    command.Connection = "connection"
    dataAdapter.SelectCommand = "command"

I put all of this inside of a try/catch loop so if there is an error it will rollback.

The TRY/CATCH loop was inside of my CommandText.

    BEGIN TRANSACTION
        BEGIN TRY
        --Run your Statements
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        DECLARE @Msg NVARCHAR(MAX)
        SELECT @Msg=ERROR_MESSAGE()
        RAISERROR('Error Occured: %s', 20, 101,@msg) WITH LOG
    END CATCH

I used my application to grab and dump that local variable for each session into a datagridview. That way if error happened, I saw it in the application.


Query DB2 without using Openquery Is there a way to query a DB2 Linked Server without using Openquery?  IE, is it possible to query DB2 through a linked server by doing something like the following?

select *
from [db2_linked_server].[database].[tablename]

Assuming your linked server provider supports it, you should be able to use a 4-part name (your example only used 3-parts)

I'm referencing Transact-SQL Syntax Conventions-Transact-SQL.

server_name .[database_name].[schema_name].object_name

WHERE

  server_name Specifies a linked server name or remote server name.

  database_name Specifies the name of a SQL Server database when the
  object resides in a local instance of SQL Server. When the object is
  in a linked server, database_name specifies an OLE DB catalog.

  schema_name Specifies the name of the schema that contains the object
  if the object is in a SQL Server database. When the object is in a
  linked server, schema_name specifies an OLE DB schema name.

  object_name Refers to the name of the object.


Chaining remote SQL Server Agent Jobs with success/failure feedback I have 2 Microsoft SQL Server 2014 (SP3-CU4) instances, residing on different servers.

ServerA has a linked server, pointing to ServerB\InstanceB.
Both instances have a job, and JobA triggers the remote execution of JobB.
So the execution of the jobs is chained.

Schematic of setup

ServerA\InstanceA

Server Objects

Linked Servers

LinkedServerToB (target: ServerB\InstanceB)

SQL Server Agent

Jobs

JobA

Step1 = Execute some local code
Step2 = EXEC [LinkedServerToB].[msdb].[dbo].sp_start_job @job_name="JobB"

ServerB\InstanceB

SQL Server Agent

Jobs

JobB

This all works fine.
Except for when JobB fails.
When that happens, JobA.Step2 doesn't know about that.
All JobA.Step2 did was trigger the remote execution of JobB, and then it's done.
So JobA in its entirety runs successfully, despite the failure of JobB.

Although I understand why it's behaving that way, its not the outcome I was looking for.
Instead, I'd like the parent job (JobA) to fail when a child job (JobB) fails.

How can I get JobA to fail when JobB fails?
Maybe some sort of success/failure feedback between them, but I don't know how to implement that.

You need to write a loop which polls serverB and checks whether the job has finished and what the outcome was. On each iteration of the loop make the job sleep using waitfor delay '00:02:00' (a 2 minute delay).

To check if the status on a job see the link below

https://sqljunkieshare.com/2014/11/21/get-sql-agent-job-status/


File growth and location change for system health extended events I am continuing the questions over system health extended event from my previous question How are the system health extended events files rolling over?

I see system health file location by default goes to log folder, can this be changed as its very hard to find for 200+ servers and thus making it a standard location across x servers
Need to understand below behavior per my comments on previous question when number of files were increased from default of 5 to 10 and size from 5 MB to 25 MB

a) Why don't we see additional 5 files yet listed once we change number of files to 10?

b) How are the files filling up the space as I see there is no sequential filing say oldest files [ 3 days old]  are still 3 MB while some of latest ones [ consider today] have already filled with 5 MB and going back to old ones to record data. How is this writing mechanism done? I need to understand this to figure out how many files and how big can they be created before making a change

I see system health file location by default goes to log folder, can this be changed as its very hard to find for 200+ servers and thus making it a standard location across x servers

Yes, you'll have to edit the extended event definition:

ALTER EVENT SESSION [system_health] ON SERVER
DROP TARGET package0.event_file
ALTER EVENT SESSION [system_health] ON SERVER
ADD TARGET package0.event_file(SET filename=N'F:\MyLocation\system_health.xel',max_file_size=(50),max_rollover_files=(40))
GO

  a) Why don't we see additional 5 files yet listed once we change number of files to 10?

Because it writes to the files in a rolling fashion, like SQL Server Errorlogs... so you can set it to keep 99 errorlogs but it won't just go out and make 99. When the data rolls over, a restart occurs, etc., then the new files will be created if needed.

  b) How are the files filling up the space as I see there is no sequential filing say oldest files [ 3 days old] are still 3 MB while some of latest ones [ consider today] have already filled with 5 MB and going back to old ones to record data.

They are used as needed but never "go back" and fill the files up that were previously used. The old ones will eventually be aged out and deleted with a new file created in place of the old deleted one. So yes, everything you're seeing is by design. It means the 3MB one had 3MB of data written to it before something happened (most like a restart of the service) and then the next one had 5MB written to it and rolled over to a new file.

  How is this writing mechanism done?

If it's set for a delay or synchronous it'll wait that long before flushing, otherwise it flushes the buffer out to the file. Not sure what you're looking for with this question.

  I need to understand this to figure out how many files and how big can they be created before making a change

It's going to be different for each instance, though most instances I would hope stay up and running a long time between restarts... if so, most should fall in line with an average amount of testing to keep whatever data you're looking at collecting for however long you're looking to keep it.


How to resolve too many connections and fatal error in mysql running on vps I am running an application PHPlist on my linode server, simultaneously running 12 PHP scripts, each of which opens a MySQL connection.  Now when I access PHPlist it often shows this error:

  Fatal Error: Sorry, the server is currently too busy, please try again
  later.

When I am trying to access phpMyAdmin, it shows me a #1040 error.
Output of my PHP scripts which run through cron jobs shows:

  PHP Warning:  mysqli_connect(): (HY000/1040): Too many connections

I am using the LAMP stack on the server with phpMyAdmin; the top output in terminal shows mysqld using 100-130% CPU. When I am trying to troubleshoot this problem I got some clues:

Increase max_connection variable : I am using 200  (100 by default)
Open table cache: 512 (400 by default)

There are lots of variables to set but I can't determine what specific ones, I am getting some reference from:
too many connections
and http://dev.mysql.com/doc/refman/5.5/en/table-cache.html

But according to my usage how to increase memory and what is the maximum memory difficult for me.

On my server I am using around 12 PHP scripts, PHPlist application for sending emails, and a major database for user registrations.

Kindly help me to resolve this problem.

First you need to do is run this query:

SELECT user,host FROM mysql.user
WHERE super_priv='Y' AND
CONCAT(user,'@',host) <> 'root@localhost';

This will list all users that have SUPER privilege. Most users that do application-related DB processing do not require this privilege. According to the MySQL Documentation, those with SUPER privilege can do the following:

Run CHANGE MASTER TO for controlling replication coordinates
KILL or mysqladmin kill to kill threads belonging to other accounts
PURGE BINARY LOGS to systemically delete binary logs
Make configuration changes using SET GLOBAL to modify global system variables
mysqladmin debug command
enabling or disabling logging
performing updates even if the *read_only* system variable is enabled
starting and stopping replication on slave servers
specification of any account in the DEFINER attribute of stored programs and views
HERE IS THE MOST IMPORTANT ONE FOR YOUR PROBLEM: : Enables you to connect (once) even if the connection limit controlled by the max_connections system variable is reached.

You will need to login as root@localhost and revoke SUPER privilege as follows:

UPDATE mysql.user SET super_priv='N'
WHERE super_priv='Y' AND
CONCAT(user,'@',host) <> 'root@localhost';
FLUSH PRIVILEGES;

Once you do this, whenever all users flood mysql connections, only root@localhost can login. After all, if everybody and his grandmother had SUPER privilege, this would bar root@localhost from ever connecting ahead of everybody else. If max_connections is at 200 and you need to raise it to 300 without having to restart mysqld, you can dynamically increase the max_connections with this command:

mysql> SET GLOBAL max_connections = 300;

That will allow more connections effective immediately, but don't just arbitrarily increase the number on whim. You have to make sure mysql has enough RAM to accommodate the increase.

CAVEAT : If you change max_connections dynamically to 300, please put it in /etc/my.cnf

[mysqld]
max_connections=300

You can run mysqltuner.pl on your MySQL DB Server. If you do not have it, then run the following:

cd
wget mysqltuner.pl
perl mysqltuner.pl

The 3rd line under Performance Metrics has this

-------- Performance Metrics -------------------------------------------------
[--] Up for: 8d 20h 46m 22s (8M q [10.711 qps], 129K conn, TX: 90B, RX: 19B)
[--] Reads / Writes: 4% / 96%
[--] Total buffers: 2.1G global + 5.4M per thread (2000 max threads)
[OK] Maximum possible memory usage: 12.6G (80% of installed RAM)

See the 5.4M per thread? That is multipled by max_connections. In this example, that would be a maximum of about 10.8G of RAM. Therefore, each time you bump up max_connections, you should run mysqltuner.pl and check if you are pressing the OS for too much memory.

In any case, limiting who has SUPER privileges give such users opportunity to mitigate flooding mysqld with DB Connections.


How to increase ETL performance in Informatica for Netezza as a source and SQL Server as a target? What settings or configuration on the Informatica server, in the Informatica software itself, or on the database servers can be changed to increase Informatica ETL throughput?  What are some benchmarks we can set to troubleshoot performance?  We are specifically using Netezza as a source and SQL Server as a target.

Please exclude multi-threading and Informatica partitioning from this question.

This we've done in the past:

restart servers every so often
remove indexes on target tables in SQL Server before ETL load
increase commit level

My experience with Informatica accessing both SQL Server and Netezza can be summed up to this:

Reading is equally fast (100,000 rows/sec; sometimes twice that speed), provided that the select SQL is simple enough, that is:

no joins;
no group by;
no sort;
where clauses only against the cluster key of the SQL Server table.

In all other cases Netezza will beat SQL Server.
Inserts with PowerCenter are of the array type, and SQL Server can usually receive 2000 to 4000 rows per second.

Inserts into Netezza run at 80,000 to 200,000 rows per second if PowerCenter is not the bottleneck.
Updates/deletes with PowerCenter have been notoriously slow on all databases for years, since the SQL executed is singletons and not an array. Therefore, a session typically drops to ~500 rows per second with SQL Server as a target, and a mere 8 rows per second with Netezza.

Due to that, we have defined staging tables per PowerCenter target in Netezza and write all updates/deletes to those tables while the session executes. These changes are then applied in bulk as a target post SQL.
This scales well since all write operations now run at similar speeds to insert.

The best solution for the notorious writer bottleneck with SQL Server as a target is to spend a lot of time inside PowerCenter comparing source with target and only write whatever difference you detect. That will carry you a long way but not scale.

My final question is, why move data from Netezza to SQLserver? If your business has requirements that truly cannot be met on Netezza, please specify which, perhaps they could be addressed.


Is Database Backup Time = Restore Time in SQL Server? I have a SQL Server Database with 10 GB size. When I performed a backup, it took almost 3-4 minutes. When I tried to restore, it took almost the same 3-4 minutes.

Does this mean backup and restore times of a SQL Server database are always equal, or can they differ?

Kindly suggest and point me the logic behind.

Reference:

SQLskills SQL101: Why is restore slower than backup
Backup Time vs Restore Time
How long will this sql server restore take?

  Does this mean backup and restore times of a SQL Server database are
  always equal, or can they differ?

They are not equal and yes they can (and most likely always) differ.

  Kindly suggest and point me the logic behind.

If you read the article from SQLSKills Paul Randal gave high level steps performed during a backup and restore. Which should explain why timing for these two operations are not same.


Should you ever not assign an unique ID to each row in a table? Possible Duplicate:
  Should every table have a single-field surrogate/artificial primary key?

I can't imagine ever not using a unique ID with every table, which is why I'm asking this question. Should you ever not assign an unique ID to each row in a table?

Yes, for example in case of a mapping table representing an N:M association between two tables. In its simplest form, it contains only two foreign keys, and these form its compound key, so it needs no separate ID column. In fact, adding an ID to such a table (without a uniqueness constraint, as pointed out by @Jeff) would allow one to add multiple associations between the same two rows, which is usually not desired.


How to make Postgres autovacuum not impact performance? I am running into problems where Postgres autovacuum processes are interfering with the overall performance of my database. It is making my select, insert, and update queries slower than usual. What are some settings, and optimization strategies that will make autovacuum have less of an impact on my database performance?

Is there a way to make it not consume as much memory/CPU so that client queries can run more efficiently?

Few basic points could alleviate your fear.

Running autovaccum will eventually improve the performance of queries. It is not something to harm the performance. This is because autovaccum does the garbage collection of dead tuples in table. so queries need to scan though less number of tuples.
In your question, it is not clear how you analysed that autovaccum is creating performance problem. if possible please provide details.
Autovaccum algorithms/logic in Postgres is continuouly improved in new versions. for example, Version 9.2 received a patch for skipping a table if there is lock. so autovaccum will do better job in handling bloat. This logic is further improved to skip blocks which are having locks. in latest version 9.6 (which is in development) received a patch to completely skip all the blocks where all tuples are already freezed.
Robert Hass posted about this recently: http://rhaas.blogspot.in/2016/03/no-more-full-table-vacuums.html
You may want to fine tune autovaccum related parameters to suit your system. if possible take the help of expert DBA. Each parameter is documented very well in Postgres documentation : http://www.postgresql.org/docs/9.5/static/runtime-config-autovacuum.html

So Regarding your question : "Is there a way to make it not consume as much memory/CPU so that client queries can run more efficiently?"

Answer is yes. by fine tuning the parameters mentioned in the above link you can achieve the best results for your system

A general advice will be to always upgrade to latest stable version of PostgreSQL to take advantage of all the autovaccum improvements.
Always we should keep in mind that autovaccum is very necessary for keeping the performance of the database. If there is CPU or Memory starvation in your system, consider getting better machine.
otherwise tables will become bloated and performance goes down over a period of time.


I am trying to achieve a Disaster recovery for Mongo DB Right now i have a MongoDb HA in site one and i need another site with asynchronous replication enables in another location.This is a base plan of mine to achieve a disaster recovery .
I dont have that much idea on how to establish async replication of mongo .
Also i need this to be done in windows system .

I've discovered MongoShake (https://github.com/alibaba/MongoShake) which is an open source Golang application which does cluster to cluster replication and has been made by Alibaba.
The documentation is focused on its internal work/behaviour, and can be discouraging but you have to look at the example configuration file instead which describe each configuration options (https://github.com/alibaba/MongoShake/blob/develop/conf/collector.conf).
In the majority of the cases, as far as I understood it (not tried it yet), you have 2 options to configure:

mongo_urls which tells where is/are the MongoDB source(s)
tunnel.address which tells to where the data should be synced

This allows you to replicate, asynchronously, a MongoDB cluster from one datacenter to another one.
There is also a 48 minutes tutorial video on YouTube which explains all the replication you can achieve with MongoShake, and also shows a demo of a MongoDB cluster in Singapour replicating to Jakarta, Indonesia.
This solution is quite young, and there is no English blog article showing it, nor official Docker image on Docker Hub (only 3 images without any documentation ).
To me it look promising and needs more write/test/love to rise.


How do I append all tables from one Azure Database to another? I have two Azure databases (on different Azure instances) with the same schema. I need to append all the records from one of the databases to the other. All keys are GUIDs so should not collide.

Many tables have foreign key constraints. I have worked out the dependencies between tables and have the order in which I want the tables to be copied.

What tool allows me to append tables and specify the order in which the tables are copied? Should I use MS Data Migration Assistant? SSIS? Import and Export Wizard? Some other tool?

The easiest way to do it is going to be to SSIS to migrate the data over. The wizards alone should be able to do something this simple. The most efficient way would be to export everything to Azure blob storage and then use BULK INSERT. I'd do it all using Powershell so that you can control error handling better. It's going to be a lot more work though. If it's not that big and it's a one-time thing, I'd go with SSIS.


Table Size Analysis on SQL Server 2000 Our SQL Server 2000 database .mdf file is 27Gb large which seems much larger than is plausible. Using the following query we tried to analyse table sizes:

select cast(object_name(id) as varchar(50)) AS name,
    sum(CASE WHEN indid<2 THEN rows END) AS rows,
    sum(reserved)*8 AS reserved,
    sum(dpages)*8 AS data,
    sum(used-dpages)*8 AS index_size,
    sum(reserved-used)*8 AS unused
from sysindexes with (nolock)
    where indid in(0,1,255) and id>100
    GROUP BY id with rollup
    ORDER BY sum(reserved)*8 desc

The results were as follows:

Name           Rows       Reserved  Data     Index_Size  Unused
NULL           15274279   26645456  5674592  17361464    3609400
BigTable         875966   16789712   471096  13349816    2968800

How can we find out which objects are causing this massive NULL space usage?
It seems that approx 26GB are "reserved" for NULL, 16GB for BigTable - is this basically a waste of space or are real records involved?

You're not using WITH ROLLUP correctly. WTIH ROLLUP creates a subtotal and/or total based upon what you tell it. You didn't specify what to call the subtotal and/or total so it is calling it NULL.

This query generates a subtotal report:

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
            ELSE ISNULL(Item, 'UNKNOWN')
       END AS Item,
       CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
            ELSE ISNULL(Color, 'UNKNOWN')
       END AS Color,
       SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP

Output:

Item                 Color                QtySum
-------------------- -------------------- --------------------------
Chair                Blue                 101.00
Chair                Red                  210.00
Chair                ALL                  311.00
Table                Blue                 124.00
Table                Red                  223.00
Table                ALL                  347.00
ALL                  ALL                  658.00


Grant user access only to specific tables I want to grant a user access only to specific tables and I want to do it with least administrative effort. All tables are located in a database call 'Reporting'. And I only want the user to access tables which contain the word 'Finance'. Any idea how I could do it with least administrative effort?

So this assumes that you have already created the user on the server/database but a simple script should help

USE [Database]
GO

SELECT
 'GRANT SELECT
  ON [' + schema_name(schema_id) + '].[' + name + ']
  TO [InsertUser]'
FROM sys.tables
WHERE name LIKE '%something%';

and run the results in a new query window.  You could probably create a stored proc to do it as well to save for the future and just pass in the user and table name


How to start Oracle enterprise manager on Windows 64 bits I have installed Oracle 11g on my pc. It has installed fine and everything went fine.

After restating pc, https://localhost:1158/em is not connecting.
The listener is ok and running fine.

How can I resolve this problem?

If I try to start the OracleDBConsoleorcl it pop up an error massage that the OracleDBConsoleorcl could not start on local windows

You need to start the service, e.g.

net start OracleDBConsoleorcl

The service name might be different on your system, but it usually starts with OracleDBConsole and then contains the service name of your database.

If you want it to always run, set the service start automatically through the control panel.


Select one single row from table b for each row from table a I have two tables:

A(name VARCHAR2(20), type VARCHAR2(20))

and

B (type VARCHAR2(20), ... (20 fields more))

I need to select a single row from B for each distinct name,type from A taking into account that table b contains millions of records and my current select runs for 20min+

SELECT *
FROM   (SELECT a.*,
               Row_number()
                 over (
                   PARTITION BY a.TYPE
                   ORDER BY a.TYPE) rn
        FROM   tablea a,
               tableb b
        WHERE  a.name = 'SEARCHED_NAME'
               AND b.TYPE = a.TYPE)
WHERE  rn = 1

How about this:

Select *
from
  tableB b
 inner join
  (select a.*, row_number() over (partition by a.type order by a.type) rn from tableA a) a1
    on b.type=a1.type and a1.name='SEARCHED_NAME' and a1.rn=1

PS. Not tested, as you didn't provide a script to create and populate the tables.


Updating Agent Job's Next Run Value Every so often, my company's BI team will request the today's execution of a job be skipped.  What I've been doing in the past, is going into the job schedule and manually changing the schedule's start date to tomorrow.  Then, I click on OK on the job schedule to save the schedule edit and then OK on the job properties window.

I then see that the Next Run value change from today at noon to tomorrow at noon which is the desired behavior.  Instead of doing it manually, I'd like perform the same task using a script.  I tried updating the active_start_date column for the schedule in [msdb].[dbo].[sysschedules] but the job ended up running at noon and it had to be manually stopped.

Does someone know how to effectively skip the next execution for a job through the use of a script?

Environment: SQL Server 2012 SP1

I have some code that did this; here's the relevant line:

EXECUTE msdb.dbo.sp_update_schedule @schedule_id         = @target_schedule_id
                                   ,@enabled             = 1
                                   ,@active_start_date   = @new_rundate
                                   ,@active_start_time   = @new_active_start_time

NOTE: In may case, I was updating job schedules a few days in advance, to allow a different job to run the last business day before a holiday. I seem to recall that the job scheduler may cache some data, so it's possible that doing this ten minutes before the job is supposed to run will not have the desired effect.

That said: I always double-checked that the scheduled looked right in the Job Activity Monitor after I ran my script, and they always did.


Internal Stored Procedure Failure - ID_TransUserActionCreate On a SQL Server 2014 12.0.4237.0 system, the following errors have been logged periodically (11 times in past 66 days):

  2018-03-22 09:28:11.170 Error: 50101, Severity: 16, State: 10.
  2018-03-22 09:28:11.170 Internal Stored Procedure Failure -  ID_TransUserActionCreate - SQL Error Number 0

As yet I haven't found any reference to an error with code 50101, and Google, amazingly, returns absolutely nothing for "ID_TransUserActionCreate".

As far as "what's happening at the time this is logged," I don't know yet as there are several databases on the system and we haven't yet started collecting detailed information to answer that question.  So this question is to ask if anyone knows, or has a good idea, what this message means and/or if it is something I should be concerned with.

That's a user-defined error message that someone created on that instance.

DOCS

  Is a user-defined error message number stored in the sys.messages
  catalog view using sp_addmessage. Error numbers for user-defined error
  messages should be greater than 50000. When msg_id is not specified,
  RAISERROR raises an error message with an error number of 50000

You can find it in sys.messages

select *
from sys.messages
where message_id = 50101

I would guess ID_TransUserActionCreate was a column name a table--lbut this is just a guess.


How to migrate SQL Server to MySQL I'm trying to migrate a SQL Server db to MySQL, and I'm having a tough time. I've tried:

MySQLWorkbench -- migration tool fails because my login only shows views in SQL Server, not the tables themselves, and so I can't get past the "Schemata Selection" screen.
sqlcmd and bcp -- both fail because they can't export csv properly. They don't quote strings with commas in them. Plus, they don't create table schemas in MySQL.
SQLyog -- just fails. Creates a MySQL table from a MS SQL view ok, but doesn't import the data. Also takes a really long time on small tables, and I have to import millions of rows.

Anyone have suggestions?

If you want to quickly migrate your data from MS SQL Server to MySQL, I suggest using the Migration tool from Oracle. This can really save you some time and effort.

This article can help you get started:

http://www.infoq.com/news/2012/08/MySQL-Migration


How to allow only a certain type of foreign key in a table I'm designing a new DB. I have employee and order table. order table has deliverer_id to indicate who delivered the order. But only employee who is "deliverer" should be allowed.

I thought of 3 methods to enforce this.

Create deliverer table in the middle of employee and order tables to make use of foreign key restrictions: But if I choose this method, I might need to make many other tables like administrator, supervisor, guest and so on for other tables. I fear if this makes things harder unnecessarily. This would be something like this:

CREATE TABLE IF NOT EXISTS temp1_employee
(
    id INT AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE IF NOT EXISTS temp1_deliverer
(
    employee_id INT NOT NULL PRIMARY KEY,
    CONSTRAINT temp_deliverer_temp_employee_id_fk FOREIGN KEY (employee_id) REFERENCES temp1_employee (id)
);
CREATE TABLE IF NOT EXISTS temp1_order
(
    id           INT AUTO_INCREMENT PRIMARY KEY,
    deliverer_id INT NULL,
    CONSTRAINT temp_order_temp_deliverer_employee_id_fk FOREIGN KEY (deliverer_id) REFERENCES temp1_deliverer (employee_id)
);

Add type column in employee table and create a trigger on order table to check if the employee.type is "deliverer": I'm worried about this method since I read that triggers should be used carefully or even avoided if possible.
Since this is a business logic(is it?), it should be enforced from applications: But I already know those applications will cause a lot of trouble if it's not enforced by DBMS.

Which method should be used? Maybe there is another?

Possible realization.

Main table

CREATE TABLE main ( id INT UNSIGNED PRIMARY KEY,
                    type VARCHAR(16)
                  );

Prepare main table for referencing

ALTER TABLE main
    ADD COLUMN for_reference INT UNSIGNED AS (CASE WHEN type LIKE 'allowed%' THEN id END) STORED,
    ADD UNIQUE key_for_reference (for_reference);

Create referenced table

CREATE TABLE slave ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                     reference INT UNSIGNED,
                     CONSTRAINT fk_to_main FOREIGN KEY (reference) REFERENCES main (for_reference)
                   );

db<>fiddle

PS. Pay attention - the field in main table which is used for referencing primarily (id) must be unique but NOT autoincremented (because the expression of generated column cannot refer to AI). In general this field must be unique but not primary key.


How to export base and discover its structure We are with a small problem at work. Our DBA decided to go around the world on a journey of self-discovery. I really love the idea and wish the best for him. The only detail is that he left without leaving anything on the database that he created.

I already searched the site but couldn't find a concrete answer for my trouble or I wasn't searching the right way.

This means that we do not have the schemas, tables with data types for each field, triggers, connections with other tables and bases.
For this same problem I would like to know if it is possible to export from TOAD (the application I use to connect to the base and edit information BUT I can use another one if you tell me) in which you have the tables with names, the fields with the data types, schemas, triggers, connections between tables and other bases.

It's an Oracle Database.

I appreciate all the help you can give me, it's very important to us because we asked for a DBA but it will take long for the company to hire one.

You can generate an SQL file (of metadata) with built-in commandline tools (available on all database installations).

Just log in to the database server, create a full=y, content=metadata_only export:

expdp \'/ as sysdba\' full=y content=metadata_only

Then use impdp to generate an SQL file:

impdp \'/ as sysdba\' sqlfile=metadata.sql

Without specifying a location, Data Pump places its files by default in the directory DATA_PUMP_DIR, but expdp will display the full path of the file it created.


Completeness of Patch Population I'm performing an audit on Oracle database changes and the database is on a Linux OS.

After using the command opatch lsinventory, we learned that this command has not been pulling the complete population of patches on the database. It is only showing the last patch applied. Contrary to many online definition; which is ALL patches.

What could be causing this? What's a foolproof way to pull all changes (patch, scheme, table etc) applied?

Thank you all!

Many times a patch is rolled back before a new one is applied so you may not be able to get a clear picture of everything that has ever been applied. You don't state your Oracle version, but if it is 12c, you can try a similar query to get some information:

set linesize 200
set pagesize 100
column version format a10
column action format a10
column action_time format a25
column BS format a5
column description format a75
select patch_id, version, action,
to_char(action_time, 'yyyy/mm/dd hh24:mi:ss') as action_time, bundle_series as BS, description
from dba_registry_sqlpatch
order by action_time desc;

This won't get non-sqlpatch patches though. Meaning if it is only a binary patch, this won't show that. But that should be in lsinventory.

You can also poke around file: $ORACLE_HOME/inventory/ContentsXML/comps.xml

Take care not to modify this file! But I do believe this shows patches that have been rolled back and ones that are currently applied. I'm not exactly sure what's all in there though.

Another thing to keep in mind is the Oracle Home. Some patches are for GRID home and some are for the RDBMS home. And some shops build an entire new home with each patch... Just ensure you are looking in the correct place.


How to use OPENROWSET with MSOLEDBSQL driver? MSOLEDBSQL driver is installed in my system. I am using the below syntax to connect to a database in a different SQL Server using OPENROWSET:

(in example I am trying to read from the same system)

SELECT c.*
FROM OPENROWSET(
    'MSOLEDBSQL'
    , 'Server=<Computer Name>;Database=<Database Name>;TrustServerCertificate=True;Authentication=ActiveDirectoryIntegrated;'
    , 'SELECT * FROM [database name].[dbo].[table name];'
    ) c;

I am getting the below error:

  OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned
  message "Client unable to establish connection".

  OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned
  message "SSL Provider: The certificate chain was issued by an
  authority that is not trusted. ".

  Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source
  object of OLE DB provider "MSOLEDBSQL" for linked server "(null)".

Any kind of help is appreciated.

Although, the MSOLEDBSQL documentation recommends Authentication=ActiveDirectoryIntegrated to enable new encryption and certificate validation behavior, I don't think the stricter validation will allow a self-signed certificate, trusted or not.

Try removing the Authentication keyword and instead specify Trusted_Connection=Yes if you are using a self-signed cert.

SELECT c.*
FROM OPENROWSET(
    'MSOLEDBSQL'
    , 'Server=<Computer Name>;Database=<Database Name>;TrustServerCertificate=Yes;Trusted_Connection=Yes;'
    , 'SELECT * FROM [database name].[dbo].[table name];'
    ) c;


Override/kill when Code 3/SQLSTATE=57016 Occurs I have encountered issues a few times when a process of mine performs a bulk load to a temp table we use for pre-processing purposes. The Bulk load fails for one reason or another (this we are looking into and addressing separately), and then the table that we were bulk loading to gets locked up. Quiesce/Unquiesce and even restarting DB2 are not releasing the lock on this table.

Here is a screenshot of the error in question that we receive every time:

This error code means The table is in the "Load Pending" state. A previous LOAD attempt on this table resulted in failure. No access to the table is allowed until the LOAD operation is restarted or terminated.

Is there any command or way to circumvent this or regain access to this specific table? We have many other processes running when this bulk load runs so restarting DB2 is a very undesirable option and, in our case right now, has not freed up the table.

Apologies if this is not enough information to go off of, please let me know what else I should provide if needed.

Your LOAD was interrupted so the table was placed into a LOAD PENDING state. To take it out of the LOAD PENDING state you will need to issue one of the following:

LOAD TERMINATE to roll back the operation
LOAD REPLACE to reload the entire table
LOAD RESTART

Also, do not take a backup before you take the table out of LOAD PENDING state.

See Restarting an interrupted load operation in Information Center for details.


How do you install a TFS plugin for SQL Server 2016 Management Studio? How do you install a TFS plugin for SQL Server 2016 Management Studio?

The source control option is no longer in the option list so the existing workarounds no longer appear to work.

Source control integration is being deprecated in Management Studio according to Deprecated Database Engine Features in SQL Server 2016, but that doesn't explain why it's not working in 2016. If you want it, I suggest you vote for this Connect item.

This can now be enabled by modifying the packages SSMS 2016 (Visual Studio 2015 shell) will load. See Source Control in SQL Server Management Studio (SSMS) by Ken Van Hyning (Engineering Manager, SQL Server Client Tools).

You will need to install Visual Studio 2015 (the free Community edition will do) in order to have the packages installed on your computer. You then modify C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\ssms.pkgundef to remove (or comment out) the lines relating to TFS SCC Configuration.

Note that on every SSMS update (which as you probably know are pretty frequent), you'll need to re-apply those changes.

I have put this process in a PowerShell script file. Trying to get it published to the PowerShell Gallery, but in the meantime you can get it from GitHub.

The same issue exists with SSMS 17, with the same solution ($SsmsUndefPath will need to be updated from 130 to 140 in the path).

Update Azure Data Studio offers an extension for source control. What is Azure Data Studio


The command TRUNCATE TABLE is DDL. That would entail doing a full table lock regardless of storage engine. If other DB Connections are waiting to access the table, you can be sure there are MUTEXs.

For the sake of example, let's say the table you are truncating is mydb.mytable.

You can get around the MUTEX problem (as manifested by Waiting for Table...)

Instead of doing this:

TRUNCATE TABLE mydb.mytable;

make an empty table on the fly:

ALTER TABLE mydb.mytable RENAME mydb.mytablezap;
CREATE TABLE mydb.mytablenew LIKE mydb.mytablezap;
TRUNCATE TABLE mydb.mytablenew;
ALTER TABLE mydb.mytablenew RENAME mydb.mytable;
DROP TABLE mydb.mytablezap;

I still run TRUNCATE TABLE mydb.mytablenew;, even though it is empty, in order to reset the auto_increment value. The table should be immediately available for use once you execute  the ALTER TABLE mydb.mytablenew RENAME mydb.mytable;.

The last line, DROP TABLE mydb.mytablezap;, should only be handled by the one DB Connection that runs the above code.

Give it a Try !!!


postgres parsing whitespace before vectorizing Using PostgreSQL 11 to build a tsvector and create index for better search, and while trying to parse text, there is punctuation and/or whitespace characters that I need to handle differently than parser in PostgreSQL .

For example, the term "R&R" keeps getting separated and counted as two r's when I need "R&R".

Also, another problematic string is "Main St./First St./Second St." which separates into "Main" "St./First"  and "St./Second".

I'm thinking the & and / should be processed upstream of parsing punctuation, which I am just learning this stuff, so maybe it's easier than I am finding.

What is best approach?

You can create your own parser, but I think that doing so is unreasonably difficult.  Preprocessing the strings yourself before the parser gets called is probably the way to go.  You just need to make sure it is done consistently, for both tsvector and tsquery.

  Also, another problematic string is "Main St./First St./Second St." which separates into "Main" "St./First" and "St./Second".

In my hands it is separated into './first', etc., not into 'st./first'.  Just preprocessing the '/' into ' ' should do the job of getting rid of the leading './', but will not do anything to make "First St." stay together as one unit if that is what you want  (If that is what you want, I don't know what the solution would be).

To make 'R&R' stick together, you could just replace the '&' with either '.' or '/'.  Both of those symbols are recognized as being part of file names or paths and so don't break up the words they come between (as long as there is not also some other whitespace/punctuation).

create function j(text) returns text immutable language SQL as $$ select translate($1,'&/','/ ') $$;

select to_tsvector(j('The R&R runs between First St./Second St.')) @@ plainto_tsquery(j('R&R'));

This is all pretty grotty, and the 2 examples you give are unlikely to cover all special cases you will want to deal with.  And a more complete list will likely contain internal contradictions, like what if someone wrote 'Main St&First St'?


Notification mail not sent on failure Currently we are having problems with our backups, specifically disk space issues.  On investigation it seems the notification mail isn't being sent when the job fails.  This is a weird issue as a notification mail is sent when the job completes successfully.

Any ideas why this would be? We are using SQL Server 2008.

Open your job in the management studio and go to the backup step properties and check whether it looks like this

I suspect "On failure action" Is not setup properly.


Does MySQL load the entire row into memory, or just the columns used in WHERE, ORDER BY and GROUP BY? I have a table that isn't very large row wise (in the 100ks range), but that contains a lot of raw data that is very large in size. Despite having a relatively little number of rows, it's around 1.5GB.

So it's quite important to know if MySQL loads the entire row into memory, or just the columns used in WHERE, ORDER BY and GROUP BY and indexes when performing the query, and the rest of the columns at the very end?

An example query:

SELECT HugeDataTable.*, Table2.Name
FROM Table1
LEFT JOIN Table2 ON Table1.`ID` = Table2.`Table1ID`
LEFT JOIN HugeDataTable FORCE INDEX(RowOrder) ON Table2.`ID` = HugeDataTable.`Table2ID`
WHERE HugeDataTable.Category = 5
AND HugeDataTable.RowOrder >10000 AND HugeDataTable.ID <> "h4324h534"
ORDER BY HugeDataTable.`RowOrder` DESC LIMIT 18 ;

Using Explain SELECT I've managed to find that MySQL scans around 70k rows per query. The query is rather fast, but I'm not sure if it's due to row caching, as I can't simulate a heavy load on the server.

So, my question is, will the columns containing the large raw data be loaded after the query limits the result to 18 rows, and thus loading only the little raw data needed in the end?

Or will they be loaded before the limit, and so 70k rows, which are around 1GB's worth of data be loaded before the limit? And if it's the latter case, what can be made to prevent such a thing, since the server only has 1GBs of RAM.

Edit: I've added the EXPLAIN.

id  select_type  table          type    possible_keys                       key               key_len  ref                           rows   Extra
1   SIMPLE       HugeDataTable  range   Table2ID,Category,RowOrder          RowOrder          9                                      49591  Using where
1   SIMPLE       Table2         eq_ref  PRIMARY                             PRIMARY           10       const,HugeDataTable.Table2ID  1      Using where; Using index
1   SIMPLE       Table1         ref     PRIMARY                             PRIMARY           2        Table2.Table1ID               1

Because it is missing, I would suppose that your query execution plan shows a full-scan on your raw-data table. If this is the case, then your entire rows will be loaded into memory (including raw columns), which obviously is not ok.

The easiest way to avoid this would be to create an index on the filtering columns, so that the query will scan the index prior, and only if the row is a candidate for your result-set, will reach the table and load it into memory.

From comment discussion:

The query performs fast mostly because of the index (thus the slow performance when removing it) ...but your rows, are still  loaded entirely. As you thought, only some of the rows get into the memory, but this does not mean that only parts of them are loaded. The trick here is that the index points only to those rows that are relevant. You're using the index with a ">" operator, which causes a Range Scan, so it is possible that more rows than you need/get are referenced.

You could try to apply the 18 limit earlier, rather at the end of the query. Or you could make a new table with only the raw column and a foreign key to the first table. Link the result from the first table just at the very end of the query. Remember though, if you want to order the rows, and perform the ORDER BY after you join the two tables, it will still perform the sorting with the raw data loaded into memory.

Try to sort the rows of the main table before joining the raw table. If the main table is leading the join, then you should get the rows sorted after the join too.


How to recover the suspect mode database in SQL Server 2008? To recover the database from suspect mode executes the following query.

EXEC sp_resetstatus ‘yourDBname’;
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb(’yourDBname’)
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (’yourDBname’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER

Then getting

  Check terminated. A failure was detected while collecting facts.
  Possibly tempdb out of space or a system table is inconsistent. Check
  previous errors

How to resolve this?

According to Paul Randal, this seems to be a bug in checkdb. Check this article from Paul which discusses about this issue.

The workaround is to run CHECKDB on the database with TABLOCK


Run multiple stored procedures in sequence I have a stored procedure written in T-SQL (SQL Server 2008 R2). It is a master procedure that essentially call multiple other subprocedures in sequence. The call and error handling is identical for each one except for the name of the procedure.

In an OO language I would use an abstraction such as an interface or functor and loop over a bunch of objects. That does not work in SQL, but I want to find some way to make this code more concise with less copy and paste repetition. Yes, I know that fundamentally SQL is about set operations and does not support what I want to do very well, but if there is a way, it will make the code much more concise. I also need to capture the result of each stored procedure invocation and do something with it which is not relevant to this question.

Here is what I have so far:

CREATE PROCEDURE dbo.testproc
AS BEGIN
  DECLARE @step INT, @result INT
  DECLARE @tbl TABLE([step] INT, [pname] NVARCHAR(40))

  INSERT INTO @tbl ([step], [pname]) VALUES (1, N'proc1')
  INSERT INTO @tbl ([step], [pname]) VALUES (2, N'proc2')
  INSERT INTO @tbl ([step], [pname]) VALUES (3, N'proc3')
  -- Potentially many more procedures here

  SET @step = 1
  WHILE @step <= (SELECT MAX([step]) FROM @tbl)
  BEGIN
    DECLARE @sql NVARCHAR(60)
    SET @sql = N'EXEC @result = dbo.' + (SELECT [pname] FROM @tbl WHERE [step] = @step)
    EXEC (@sql)
    IF @result <> 0
    BEGIN
      INSERT INTO SomeTable error code and step number
      RETURN
    END
   SET @step = @step + 1
  END
END
GO

When I run the procedure, SQL Server gives me an error because the @result variable that is part of the dynamic SQL is not defined as part of the batch that is contained in the @sql variable. If I modify it like this:

    SET @sql = N'EXEC dbo.' + (SELECT [pname] FROM @tbl WHERE [step] = @step)
    EXEC @result = (@sql)

I get a syntax error.

This works fine except for retrieving the return value of the subprocedures. Is there a way to accomplish my stated goal, and if so, how?

NOTE: based on what I asked here, a cursor would sound like a better implementation than a WHILE loop especially given the table variable. Part of the code that is not essential to this question involves knowing the iteration number, hence the use of a loop control variable.

If you don't need the result values later you can do it shorter this way:

-- procedures to test with
create proc proc1 as print '1' return 0
GO
create proc proc2 as print '2' return 1
GO
create proc proc3 as print '3' return 0
GO

if object_id('dbo.testproc') is null exec('create procedure dbo.testproc as return(0)')
GO
alter PROCEDURE dbo.testproc
AS
  DECLARE @result INT
        , @sql nvarchar(max) = N''
  DECLARE @tbl TABLE([step] INT, [pname] nvarchar(513))

  INSERT INTO @tbl ([step], [pname])
  VALUES (1, N'proc1'),
         (2, N'proc2'),
         (3, N'proc3')
  -- Potentially many more procedures here

  select @sql = @sql + 'exec @result = ' + QUOTENAME(pname) + ' if @result <> 0 return;'
  from @tbl order by step

  exec sp_executesql @sql, N'@result int output', @result output

  if @result <> 0
    begin
      print 'do your cleanup'
    end
GO

exec testproc

The variable engine will produce the following query batch

exec @result = [proc1] if @result <> 0 return;
exec @result = [proc2] if @result <> 0 return;
exec @result = [proc3] if @result <> 0 return;

When the batch is executed it will stop executing when there is a @result not zero and keep that value in the output parameter.

More traditional looping

If you want to loop over the procedures. Since there are no parameters (or the parameters are all the same) you can simply call exec @result = @proc

if object_id('dbo.testproc') is null exec('create procedure dbo.testproc as return(0)')
GO
alter PROCEDURE dbo.testproc
AS
  DECLARE @result INT
        , @proc sysname
  DECLARE @tbl TABLE([step] INT, [pname] nvarchar(513))

  INSERT INTO @tbl ([step], [pname])
  VALUES (1, N'proc1'),
         (2, N'proc2'),
         (3, N'proc3')
  -- Potentially many more procedures here

  declare c cursor fast_forward local
  for select pname from @tbl order by step

  open c
  fetch next from c into @proc
  while @@FETCH_STATUS = 0
    begin
      exec @result = @proc
      if @result <> 0
          BREAK

      fetch next from c into @proc
    end
  close c
  deallocate c

  if @result <> 0
    begin
      print 'do your cleanup'
    end

GO

exec testproc