SQL Server 2014 log growing just in Navision 2015 just when always on is active My question is about if anybody can help me to find out why my LOG file growing to more than 2T just when I activate ALWAYS ON in my NAVISION database.
I have more databases in my SQL Server 2016 and I have configured for my CRM DB and others and there is no problem. But with NAVISION I have problem.

Any suggestions?

Full recovery model and no log backups?
AlwaysOn not pushing data? (so unable to re-use the log filespace)


.psqlrc - setting keepalives_idle connection parameter Is it possible to set keepalives_idle parameter in .psqlrc so that every connection I open uses it?

I've finally found this option and it's a remedy for my connection dropping problems.

I would really like to set it globally for all sessions I open with psql.

With TCP Keepalives, it doesn't matter who sends it and who receives it. It just matters that it happened so the route isn't lost by a NAT device or closed at either end. I don't know why you'd ever want to set this on the client, as libpq offers, both methods accomplish the same thing.

How to set keepalive transmissions from the server

This only sets it for your session

To set the tcp_keepalives_idle from the server to the client, use

SET tcp_keepalives_idle = 42;

You can put anything you want in your ~/.psqlrc and it gets executed by psql (except under -c and -x). So append this command to the end if you want it to occur for ever psql session.

echo "SET tcp_keepalives_idle = 42;" >> ~/.psqlrc

How do set keepalive transmissions from the client

According to this mailing list post, you can set it on the client by typing

psql "host=127.0.0.1 dbname=myDB keepalives=1"


I want to exclude rows from an SSRS report based upon a field value and display a message if, as a result of filtering, there are no results I have a report where I want to exclude most of the rows based upon a field value. That part is not so hard. The value that I am filtering out is "OK". so in the end the report only displays rows that contain warnings. Typically only one or two rows out of 50-60.
I have been using the expression:
=IIF(Fields!ALERT_VALUE.Value = "OK", True, False) for the row.

Now we get to the tricky part. If there are no warnings then I just get an empty report. What I want to do is display a message that indicates that there are no warnings.
So, I created a new row and set this expression in the first column:
=IIF(Count(Fields!ALERT_VALUE.Value,"DataSet1")=0,"No Commitment Warnings", nothing)

And set visibility as:
=IIF(Count(Fields!ALERT_VALUE.Value,"DataSet1")=0,False,True)

But I get nothing.
I think I read somewhere that I an just hiding the rows and not removing them from the report so I guess that my message row is still seeing the hidden rows and that is whey it does not work.
But I swear I had it working this morning LOL!
So, is there a way to get what I am trying to do? Hide all the no warning rows and if there are no warning rows, show the message?

I'm a little confused on your logic, because the visibility expression =IIF(Count(Fields!ALERT_VALUE.Value,"DataSet1")=0,False,True) will  hide the row if the count is 0 which is opposite the logic it sounds like you're trying to achieve with =IIF(Count(Fields!ALERT_VALUE.Value,"DataSet1")=0,"No Commitment Warnings", nothing) and based on what you said. If so, then all you may need to do is flip False and True around in your visibility expression like this =IIF(Count(Fields!ALERT_VALUE.Value,"DataSet1")=0,True,False).
For reference, SSRS's functions are derived from VBA, so you can find the docs on the IIF Function here.


Allow entering application while in transaction We've got a process that needs to run a certain set of stored procedures in a transaction and fail if one of those fails...
Simple we have something similar to:
BEGIN TRY
        BEGIN TRANSACTION;
            --run some sprocs
            EXEC [dbo].[sproc1];
            EXEC [dbo].[sproc2];
            EXEC [dbo].[sproc3];
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        -- Re-raise error
        EXEC [dbo].[reraise_error];
        RETURN -1;
    END CATCH

These sprocs are pretty long running processes.  However, I noticed the team who developed this application was running this job at 10pm and have a step before this job that takes down the site completely.  They wanted to ensure no one edits any of this data so they have the site down while this process happens and then once its done the site is automatically brought back to life.  This has been fine for that past 3 years until we had a customer join us from overseas...they noticed the site was down  (due to this process) and now has requested a change.
I thought simple I could just disable the taking down of the site and handle edits in the front end of my application.  The problem is the database seems to be locked while running this above transaction, for instance, a user trying to get to the site gets a spinning wheel until this transaction completes.
As I ran tests if I comment out the BEGIN TRANSACTION and just run the sprocs (outside of a transaction) then I no longer have any issue as the tables aren't locked.
But I cannot do that as we want this process to either pass / fail completely.  If it fails we want the whole transaction to fail (as we have a rollback transaction).  The customer still wants the site not to lock up and continue this process so I played around with adding:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
But this did not help in any way as it still seems users cannot do anything in the application until after the transaction is complete.
Am I doing something incorrectly?  What can I do so I do not have to lock up the application but continue this process in a transaction?  I would of thought changing the isolation level would help me out but I get the same results.

As I ran tests if I comment out the BEGIN TRANSACTION and just run the sprocs (outside of a transaction) then I no longer have any issue as the tables aren't locked.

Inside a transaction, the modification locks are held for the duration of the entire transaction. Without the BEGIN TRAN, locks are only held until the end of each statement.  There will still be blocking, but it won't last as long, so your site is probably able to operate more effectively.

The customer still wants the site not to lock up and continue this process so I played around with adding:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

But this did not help in any way as it still seems users cannot do anything in the application until after the transaction is complete.

Data modification queries don't work that way.  Check out the documentation on isolation levels:

Choosing a transaction isolation level does not affect the locks acquired to protect data modifications. A transaction always gets an exclusive lock on any data it modifies, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction.

What you actually want is the opposite - the nightly process is going to take locks, you want the client (website) to be able to bypass them.  But using READ UNCOMMITTED / NOLOCK is not the ideal way to do that.  There's a better way, which is
Use RCSI
If the web site is mostly reading data, then you might have success with the read committed snapshot isolation level (RCSI).  This would allow the website to continue to read from the database without being blocked by the nightly process.
You can read a detailed account of what to consider when turning on RCSI here: Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide


Are 'SELECT *' Sub-Queries inefficient? We have a third party analytics platform, which allows the end users to create their own tables and charts from a selection of pre-defined views. This queries a MS SQL Database.

Unfortunately, it seems the SQL Syntax this software injects into the database to query the data, is massively inefficient, or so it appears, based on my knowledge and understanding.

For example, here is what a query could look like which joins two tables together:

SELECT tblOne.ColumnOne, tblOne.ColumnTwo, tblTwo.ColumnThree
FROM (SELECT * FROM tblOne) AS tblOne
JOIN (SELECT * FROM tblTwo) AS tblTwo ON tblOne.id = tblTwo.id

Now imagine these tables have many columns each, or there are many more joins to additional tables, each following the same pattern - am I correct in assuming this would perform a full table scan in the sub queries, effectively reading more than actually necessary? Am I also correct in assuming that the below would actually be more efficient?

SELECT tblOne.ColumnOne, tblOne.ColumnTwo, tblTwo.ColumnThree
FROM tblOne
JOIN tblTwo ON tblOne.id = tblTwo.id

I just want a second opinion, before I write a strongly worded email to the developers of this analytics solution, just in case I am misunderstanding how the engine would handle a query like this.

Thanks in advance.

am I correct in assuming this would perform a full table scan in the sub queries, effectively reading more than actually necessary?

No.  The query optimizer should see through this.  There's no general rule that subqueries are materialized in query execution.

  Am I also correct in assuming that the below would actually be more efficient?

It may be that with enough joins and subqueries you could get a bad plan.  But it's not the first place to look.  Use the Query Store (SQL 2016+), or the plan cache DMVs, or XEvents to see the query plans and associated resource costs.

For instance on AdventureWorksDW, these two queries use the same plan.

select p.Color, sum(f.SalesAmount) SalesAmount
from
 (select * from FactInternetSales) f
join
 (select * from DimProduct) p
 on p.ProductKey = f.ProductKey
 group by p.Color

 go

select p.Color, sum(f.SalesAmount) SalesAmount
from FactInternetSales f
join DimProduct p
 on p.ProductKey = f.ProductKey
group by p.Color


'Use  ' statement not executing T-SQL Okay, this seems funny for a simple script: but I really need this small piece to execute well.

DECLARE @dbName NVARCHAR(50)
SET @dbName = 'rx_lab'
DECLARE @SQL02 NVARCHAR(MAX)
SET @SQL02 = 'USE ['+ convert(nvarchar(50),@dbName) +']'
exec sp_executesql @SQL02

SELECT DB_NAME()

This still returns the 'master' database (instead of 'Rx_lab')
I have even tried using

exec ('use '+@dbName)

No change. It didn't work even without the conversion to nvarchar.
However, when I use 'USE [Rx_lab]' directly it returns well. This is part of stored procedure where the @dbName is main parameter.

sp_executesql creates a new execution context which ceases to exist when the executed statements return.  The USE only has effect within the sp_executesql.  Try this

SET @SQL02 = 'USE ['+ convert(nvarchar(50),@dbName) +']; SELECT DB_NAME();'

and you'll get what you're after.


Error while opening Oracle 11g Database I have a problem with oracle 11g database,

I have shutdown my computer this morning normally, but in the evening when I tried to connect to my database I fond a problem. I cannot connect as normal user so I tried to connect as sysdba to shutdown the database and remount it (this works normally). Now when I try to open it (the database) I got this error :

ERROR in line 1 :
ORA-00600: Internal error code, arguments : [kcratr_nab_less_than_odr], [1],
[90], [13770], [13771], [], [], [], [], [], [], []

Can any one help me please ???

Your situation looks most like a powerfailure. This kind can hurt the database badly and in this case it looks like the controlfile or the online logfile is damaged. If you have a backup, start with restoring the controlfile from the backup and recover the database using the backup controlfile option.

With a little luck you can open the database afterwards. With a little bad luck, the damage is in the online redolog in which case you can not open the database in a consistent state. This is because your undo segments are active and they lack to recovery information from the redolog file to recover from that.

Still it is possible to open the database using some tricks but it does require extra work, setting hidden parameters etc. After that type of 'recovery' only export -> create new db -> import is a valid option.


Using a select max(blah) as an input parameter This may be a naive question, but is it possible to use a select max(blah) to update a parameter in an input statement?
input a,b,c into table1 values (10, 20, (select max(c) where a=10) + 1) for example?
I tried this in postgresql and got the hint
insert into "MeterData".data(nmi, intervalend, "E1", version) values
('E1234', now(), 12.4, (select max(version) where nmi = 'E1234' and interalend = now()) + 1)

ERROR:  column "version" does not exist
LINE 2: ('E1234', now(), 12.4, (select max(version) where nmi = 'E12...
                                           ^
HINT:  There is a column named "version" in table "data", but it cannot be referenced from this part of the query.
SQL state: 42703
Character: 105

I'm not even sure if this is possible. The idea is that if the entry already exists in the table, I need to add the new data and update the version by 1.
Hope this makes sense.
Thanks to the help here, I've got this working now IF there is a value in version to start with, however, if there is no value yet, it sets to null and so doesn't increment on subsequent calls. I've tried setting a default value on that column to 1 (thought that would have done it, but no), and tried a few different ways unsuccessfully to get the query something like
insert into "MeterData".data(nmi, intervalend, "E1", version) values
('E1234', '2021-02-08 00:00:00+11', 12.4, (select max(d.version) from "MeterData".data d where d.nmi = 'E1234' and d.intervalend = '2021-02-08 00:00:00+11') into value, case value==null then 1, else value+1 end))

but that just ends badly.
Is there a way to do this, or do I need to write a function to handle this (or do it in some other code external to the db?
I have no idea why this question was closed.. what the??? How is this not a relevant question?

As the comments state, you forgot to include the FROM clause in your SELECT query. Try this:
insert into "MeterData".data(nmi, intervalend, "E1", version) values
('E1234', now(), 12.4, (select max(version) from MeterData where nmi = 'E1234' and interalend = now()) + 1)


Move Complete ORACLE from one system to another I have 2 identical server with exact disk partitions (OS CENTOS 6.4 both ) [My college central oracle server].

Entire ORACLE HOME ( bin , control files, data files everything on oracle) is on 2nd Disk (which is not a OS disk)

Since OLD server had reached end of life (from HP) a new HP machine with upgraded RAM (OLD system had 16 GIG where as new system as 64 GIG).

Now Since All oracle Dependencies (RPM, libraries, environmental variables, JAVA, Users and groups) are already made on new server.

So Now Question is ..

Can I remove Disk [Oracle HDD] from OLD server and insert it on NEW server and just start Oracle (sqlplus "/ as sysdba" or dbstart and lnsctrl).

Can it will give any challenge.
(I have cold backups, export backup, & RMAN backup too).

I just want to give a try on above case.

Will this work ??

Check OS users and groups: oracle, oinstall, dba.
Check the contents of /etc/oratab and /etc/oraInst.loc files to verify if they point to an expected location.
Check if these files are placed under the same paths:

init.ora, orapwd and similar
listener.ora, tnsnames and similar
control files
data files
redo log files
Oracle scripts such as oraenv (optionally - only if you use them), which are often placed in /usr/local/bin

Check if you retain the same IP.
Check if you retain the same DNS name.

And that's it, you are good to go.


Want to timeout or skip SQL Server index rebuild statements if taking too long When running index rebuild scripts overnight on large and highly fragmented databases we frequently come across indexes that stop the job because they seemingly never complete their rebuild. The job is halted and in the morning we have to manually cancel the statement, waiting forever for the rollback, causing downtime. We can remedy these occasional indexes my manually dropping and recreating.

Is there a way to automatically timeout an index rebuild statement if there is little progress, so the script can carry on to the next rebuild statement?

Our script is of the form:

ALTER INDEX ALL ON [Table1] REBUILD WITH (FILLFACTOR = 90)
ALTER INDEX ALL ON [Table2] REBUILD WITH (FILLFACTOR = 90)
ALTER INDEX ALL ON [Table3] REBUILD WITH (FILLFACTOR = 90)..

First, I would recommend that you use one of the free scripts/tools to manage index rebuilds. One of the best known is Ola Hallengren's DB maintenance suite. This lets you control how fragmented an index must be before you try to reorganize or rebuild it.

Second - if you still have issues where the process seems to hang, try creating two jobs. The first job would actually run the index rebuild process. The second job would ensure that the first job doesn't run longer than necessary.

Let's call the first job 'Index Maint Job'. Do not schedule this job.

The second job would be scheduled for when you want the index maintenance to be run, and would execute this script:

DECLARE @jobname nvarchar(128) = N'Index Maint Job';
DECLARE @proc_start_date datetime = GETDATE();

EXECUTE sp_start_job @job_name = @jobname;

WAITFOR DELAY '003:00:00';

IF (EXISTS (SELECT 1
              FROM sysjobactivity ja
                     INNER JOIN sysjobs j ON (ja.job_id = j.job_id)
             WHERE j.name = @jobname
               AND start_execution_date >= @proc_start_date
               AND stop_execution_date IS NULL
           )
   )
BEGIN
    Print N'Aborting job ''' + @jobname + N'''...';
    EXECUTE sp_stop_job @job_name = @jobname;
END
ELSE PRINT N'Job ''' + @jobname + N''' finished.';

This launches the job, waits until it's been given whatever time you feel is sufficient to complete, checks if it's still running, and stops it if it is.

(I had originally planned to use TRY ... CATCH to catch the error you get if the job isn't running and you try to stop it; however, that's a SQL Server Agent error, which TRY ... CATCH can't handle. So, we check to see if the job's still running instead.)

The key element to know here is the sp_start_job does not wait for the job to finish; it returns once the job is started. So, this job has control of itself again at that point, and can wait however long you like (current setting [WAITFOR DELAY '003:00:00'] is three hours) to see if the job is still running, and stop it if it is.

FYI: As other have noted, an index reorganization happens gradually; I have a large fragmented PK index to try to defrag, and was planning on implementing this with Ola's scripts to make sure the process didn't run too long.


Reuse SELECT query by adding results in array? I have written a PostgreSQL function, which returns a specific order of products. Now I would like, not only to show, but to put the results of the first SELECT query to an array as well, so I can reuse the ID's inside another select query. I first tried to add an alias to the select query like SELECT * FROM (SELECT id FROM products) as pr and use pr inside the NOT IN(pr) statement of the second query, but that doesn't work ...

I will explain it more clearly with an example, this is a simplified version of the function:

CREATE OR REPLACE FUNCTION featured_products(
    valid_to_in timestamp without time zone,
    taxonomy_id_in integer,
    product_limit_in integer)
  RETURNS SETOF integer AS
$BODY$
  BEGIN
    RETURN QUERY

    (
    -- #1
    SELECT * FROM (
        SELECT "product"."supplier_id" FROM products AS "product"
    ) AS "featured"
    LIMIT 2
    )

    UNION ALL
    SELECT *
        FROM (
            SELECT "product"."supplier_id" FROM products AS "product"
        ) AS "featured"
        WHERE id NOT IN (
            -- #2
            SELECT * FROM (
                SELECT "product"."supplier_id" FROM products AS "product"
            ) AS "featured"
            LIMIT 2
        )
    LIMIT product_limit_in;
  END;
  $BODY$
  LANGUAGE plpgsql VOLATILE;

I deleted some joins and GROUP BY and ORDER BY statements, so the function is a bit more readable. And I added #1 and #2 inside the code above, so you know what I mean with select query 1 and 2.

As you can see the query #2 should return the same results as query #1. In reality these queries are much bigger. So you I just want to replace the second, identical query with just an array of ID's. Less code and probably faster.

I don't know how to add the IDs returned from the first query, to an array and put that in a NOT IN(<id's>) statement instead the second query.

Anyone who does know how to fix this?

It's a textbook case for a CTE, like @Daniel commented.
The example can be simplified some more. And you need to be aware of how LIMIT works in a UNION query.

CREATE OR REPLACE FUNCTION featured_products(valid_to_in      timestamp
                                           , taxonomy_id_in   integer
                                           , product_limit_in integer)
  RETURNS SETOF integer AS
$func$
BEGIN
   RETURN QUERY
   WITH featured AS (SELECT supplier_id FROM products LIMIT 2)
   SELECT supplier_id
   FROM   featured

   UNION ALL
   (
   SELECT p.supplier_id
   FROM   products p
   LEFT   JOIN featured f USING (supplier_id)
   WHERE  f.supplier_id IS NULL
   LIMIT  product_limit_in
   )  -- parens required - or not?
END
$func$  LANGUAGE plpgsql VOLATILE;

LIMIT can only be applied once in a UNION (ALL) query, unless you enclose the leg of the query in parentheses. You may or may not want to add parentheses.

The way I have it, a maximum of product_limit_in rows are returned in addition to the "featured" rows from the CTE.
If you remove the parentheses you get a maximum of product_limit_in rows total - meaning that even "featured" products may be discarded.
Related: Optimize a query on two big tables

Either way, don't ORDER BY the outer (combined) result before you LIMIT, if you can avoid it. Postgres can optimize the query very efficiently and just stop evaluating once enough rows have been returned (possibly fetching tuples from the top of a matching index). That would not be possible any more, which can make a huge difference in performance.
Using LEFT JOIN / NOT NULL to exclude featured rows from the second SELECT, which is probably faster than NOT IN and does not carry "surprises" when dealing with NULL values or empty results.

Select rows which are not present in other table

In Postgres (as opposed to some other RDBMS), you can refer to p.supplier_id and f.supplier_id after joining with USING (supplier_id).

And yes, the CTE is only evaluated once:

  A useful property of WITH queries is that they are evaluated only once
  per execution of the parent query, even if they are referred to more
  than once by the parent query or sibling WITH queries.

Bold emphasis mine.


Date comparsion within subquery fails with: "out-of-range value" I have a an update statement which uses a subquery to filter records. My input table contains dates in a varchar field, and some of them are invalid.

CREATE TABLE [dbo].[input](
    [id] int,
    [START_DATE] [varchar](30) NULL
) ON [PRIMARY]

INSERT INTO [dbo].[input] ([id], [START_DATE]) VALUES(1, NULL) ;
INSERT INTO [dbo].[input] ([id], [START_DATE]) VALUES(2, '') ;
INSERT INTO [dbo].[input] ([id], [START_DATE]) VALUES(3, '01 JUL 0201') ;
INSERT INTO [dbo].[input] ([id], [START_DATE]) VALUES(4, '01 JUL 2016') ;

In the sample data only record 4 is valid.

My query:

select a.[id]
FROM (
    select [id], convert(datetime, [START_DATE],106) as csd
    FROM [dbo].[input]
    where len([START_DATE]) > 0
    and substring([START_DATE],7,5) > 2010
    and isdate([START_DATE]) = 1
    ) a
    Where a.csd < getdate()

I've had a play with various clauses to exclude junk:

len > 0 excludes nulls and ''.
Substring looks for a sensible year.
isdate checks again for a valid date.

I'm sure there's other more efficient ways of doing this, but the point being the subquery executes and correctly returns valid records. in this case id = 4. Which has a valid date 1 July 2016.

The outer query should simply now be comparing the converted date with today's date. But instead i get:

Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Why? What am i missing?

The inner query has filtered and converted the value I am evaluating.

Is there a better way of filtering this list to be used in an update query?

The execution order is in much regards up to the query optimizer. So in your case, even thou the SQL code you wrote looks like the subquery would filter out the invalid dates first, what the actual execution of the query looks like, can be very different.

You can run your query with option(force order), but be careful with that one, very careful, and know what you are actually doing. Another option would be a temp table, to preselect the valid or the invalid dates, but that could be very costly as well.

While it feels dirty, and it is, you could also compare string values, to not run into this issue, just make sure to use a codepage that will sort as a string as it would as a date

select a.[id]
FROM (
    select [id], convert(varchar(23), convert(datetime, [START_DATE],106),121) as csd
    FROM [dbo].[input]
    where len([START_DATE]) > 0
    and substring([START_DATE],7,5) > 2010
    and isdate([START_DATE]) = 1
    ) a
    Where a.csd < convert(varchar(23, getdate(), 121)

all in all, it would be best to save the start_date as an actual date type thou.


Cannot execute as the database principal because the principal "dbo" does not exist I restored a backup of a database from SQL Server 2008 R2 to SQL Server 2012.

When I try to access a particular page of my application, I get this error:

  Cannot execute as the database principal because the principal "dbo" does not exist

It works with my 2008 R2 project perfectly. How can I resolve this issue?

You need to give the database a valid owner. Use the ALTER AUTHORIZATION command to do this:

ALTER AUTHORIZATION
ON DATABASE::[DatabaseName]
TO [A Suitable Login];

Related Knowledge Base article

Note the sp_changedbowner system stored procedure has been deprecated in favour of ALTER AUTHORIZATION.


Storing Large Nested Objects What is the best way to store a large (thousands of properties) nested JSON object in Postgres?  It does not need to be index, queried, or anything.  Just written and read.  Write speed isn't an issue at all, only read speed.  Should I still opt for JSONB, even though it seems like the benefits of JSONB would be rendered inert?  Perhaps JSON?  Or even just a normal text type?

As with most things, it depends.

First of all, if you're not 100% certain that the data will always be valid JSON, use a normal text type instead.

If the data is indeed always JSON, I would use a JSON type if for no other reason that as documentation that the column contains JSON data.

If there will ever be any chance of requiring indexing of data, go for the JSONB datatype. If insert performance is critical, choose JSON (or even a text type) instead. The JSON datatype stores the JSON data as-is, while the JSONB datatype removes empty nodes and whitespace and even duplicate keys (the last one is kept). This means that JSONB is storing less data, but requires minor reconstruction on retrieval.

For all other cases, I would go with JSONB as the "best" generic JSON type in PostgreSQL at present, and this is also the recommendation in the documentation. See http://www.postgresql.org/docs/9.4/static/datatype-json.html


How to design a relational DB to capture the same entity as reported by multiple sources? I need a DB that can capture instruments as reported by a bank and two accounting firms. Let's take Walmart stock as an example. The bank may have a instrument named "Wal-Mart Stock". One of the accounting firms may have it named as "Walmart Stock". The other accounting firm may have "Wal-mart common stock".

Here's the diagram that I have so far:

* Table contains five additional columns. These five column names are the same for MasterInstrument and DataSourceInstrment.

The DataSource represents the bank, accounting firms, or any other entity that can provide data.

The DataSourceInstrument represents the instrument data as provided by the DataSource.

MasterInstrument is a version of the instrument specific to the application itself. So, in our example, it may contain "Walmart Common Stock" for the instrument name. It's different than DataSourceInstrument b/c unlike DataSourceInstrument, it is used extensively throughout the application and it must maintain a history of changes.

The Instrument table provides a way to know that several different data sources are referring to the same instrument. DataSourceInstrument and MasterInstrument with the same instrumentId are referring to the same instrument. (Instrument.id is a surrogate key b/c some instruments do not have a natural key, and the DataSourceInstruments and a MasterInstrument may need to be manually mapped as referring to the same instrument.) So, using the Walmart example, we can know that the bank, two accounting firms and the internal system are referring to the same Walmart instrument.

The Issuer represents the issuer of the instrument such as "Wal-Mart Stores, Inc." Note: It only needs to be captured for MasterInstruments.

I have a couple of doubts on this design:

Is it poor design to have a table (i.e. Instrument) with only a single surrogate column? If this is a problem, how would it be designed differently?
Are the various *Instrument table names confusing? If so, how could it be more clear?

Overall how should the design account for the need to capture multiple sources referring to the same instrument along with the need to capture an internal version of that instrument with history?

Update

The system's primary function / raison d'être is reporting. It must report for a given date what the internal instrument (i.e. MasterInstrument) data was on that date. This is why I have MasterInstrument as a history /temporal table and do not have the columns in Instrument.

Is it poor design to have a table (i.e. Instrument) with only a single surrogate column? If this is a problem, how would it be designed differently?

If that is the only constant property of the entity then that is fine, but I would assume that there are other invariant properties such as a name ("Walmart" in your example). Even if the same "instrument" can be referred to by different names by each source and issuer (those names then belong in the relation table) do they not have a canonical name?

  Are the various *Instrument table names confusing? If so, how could it be more clear?

It depends a lot on how well you document them and/or the names and naming conventions in your target industry.

Table names should be as descriptive as possible (you can always shorten them in queries using aliases) avoiding generic words where possible. Where a generic name is needed because nothing more speciifc isn't too specific then I would suggest the simplest generic word that fits (item, object, thing) rather than instrument, but that is personal preference.

For junction tables (relation tables, or whatever you call them as there are several common names for the same concept) I recommend the name represents the row entity types in the relationship - so IssuerInstrument rather than MasterInstrument in your diagram.


One of my Disaster recovery server every day sp_configure values are changing We have a DR server, Daily databases are restoring from Production to DR server.

From last week onward, the Sp_Configure Value (Show Advanced) is changing.

How do I find out from where these values are coming from, they are different from the Production server Sp_Configure values.

This will be in your Log File.

For example, if I run:

EXEC sys.sp_configure @configname = 'Show Advanced', @configvalue = 0
RECONFIGURE WITH OVERRIDE

EXEC sys.sp_configure @configname = 'Show Advanced', @configvalue = 1
RECONFIGURE WITH OVERRIDE

It shows up in my Error Log like this:

If you want to search through your error log for other instances of this occurring, you can click filter, and then fill out a search condition.

If you want to dig a little bit deeper, you can check the default trace for events as well (adapted from Aaron's code here):

DECLARE @path NVARCHAR(260);

SELECT @path = REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)), 260)) + N'log.trc'
FROM   sys.traces
WHERE  is_default = 1;

SELECT   TOP 1000 TextData,
                  NTUserName,
                  NTDomainName,
                  HostName,
                  ApplicationName,
                  LoginName,
                  ServerName,
                  DatabaseName,
                  SessionLoginName
FROM     sys.fn_trace_gettable(@path, DEFAULT)
WHERE    EventClass = 22
AND TextData LIKE '%Configuration%'
ORDER BY StartTime DESC;


Beginner problems administering users on mysql Very new MySQL user here. Two questions for the community.

I seem to have created an account that I can't delete/drop.
I can't seem to get MySQL to actually require newly created accounts to require passwords even when I specify them during creation.

I ran myisamchk and corrupton doesn't seem to be an issue. Logs below.

mysql -u root -p
use mysql
select * from db\G;

*************************** 3. row ***************************
      Host: localhost
      Db: projectdb
      User: project
      Select_priv: Y
      Insert_priv: Y
      Update_priv: Y
      Delete_priv: Y
      Create_priv: Y
        Drop_priv: Y
       Grant_priv: Y
      References_priv: Y
       Index_priv: Y
       Alter_priv: Y
Create_tmp_table_priv: Y
 Lock_tables_priv: Y
 Create_view_priv: Y
   Show_view_priv: Y
Create_routine_priv: Y
 Alter_routine_priv: Y
     Execute_priv: Y
       Event_priv: Y
     Trigger_priv: Y

mysql> drop user project;
ERROR 1396 (HY000): Operation DROP USER failed for 'project'@'%'

~~~~~

CREATE USER project123 IDENTIFIED BY 'project123';
Query OK, 0 rows affected (0.00 sec)

GRANT ALL ON projectdb.* TO project123 IDENTIFIED BY 'project123';
Query OK, 0 rows affected (0.00 sec)

quit;

MySQL then proceeds to let me log in without a password.

macbook-pro-8: $ /usr/local/mysql/bin/mysql -u project123
Welcome to the MySQL monitor.  Commands end with ; or \g.

What am I doing wrong?

You obviously have anonymous users in mysql.user

Just run the following:

DELETE FROM mysql.user WHERE user='';
DELETE FROM mysql.user WHERE host='';
FLUSH PRIVILEGES;

I have past posts on this subject

Aug 14, 2011 : Cannot drop anonymous user from mysql.user
Jan 18, 2012 : MySQL error: Access denied for user 'a'@'localhost' (using password: YES)
Feb 17, 2012 : MySQL : Why are there "test" entries in mysql.db?
Apr 09, 2012 : Mysql users deleted
Jun 09, 2012 : Why ALL PRIVILEGES ON `db`.* gives access to all databases?
Jun 26, 2012 : Best practices to secure mysql

Another observation: You tried to run mysql> drop user project;

By mysql's viewpoint, a user is identified as user@host

To see all defined users, please run this query:

SELECT CONCAT('''',user,'''@''',host,'''') MySQLUser FROM mysql.user;

You should see something like this:

+--------------------------+
| MySQLUser                |
+--------------------------+
| 'advdb'@'%'              |
| 'anadb'@'%'              |
| 'dmvadb'@'%'             |
| 'dmvanswers'@'%'         |
| 'dmvdb'@'%'              |
| 'dmvqnadb'@'%'           |
| 'icdb'@'%'               |
| 'jjohnston'@'%'          |
| 'localdb'@'%'            |

Thus, when you drop a user, it is best to use this style format as shown.

For example, to drop user icdb, you must run

DROP USER 'icdb'@'%';


Validation of data with checksum vs Binary_checksum Based on this post, I was able to solve the performance issue by adding indexes and thought I completed my project until I ran into an issue with the checksum... I then did research (should have done this before I started the project) and found that the checksum and binary_checksum are not reliable in a system critical environment.

I'm looking for a reliable way, to compute data for a table at a store and then compare it with the same table at the head office.

As explained in the post mentioned above, Data files are transferred from multiple stores (transactions) to the head office as they happen. For each store the table in question is pretty big in size. At the head office, that same table contains the same info from all stores which makes it huge. The data is transferred through files as there isn't any direct connections between the HO and the stores.  Ideally it would be great if I could transfer all the data from the store, line by line and compare it against the head office's table but it's unthinkable since it's way too big.

I've tried to use the HASHBYTES by casting columns to strings but just found out that it has a limitation of 8000 characters up until SQL Server 2014.  Our systems have to support any version of SQL Server from 2008 up until today.

I'm trying to find a reliable way to get some type of aggregated checksum value that can be compared between each store and their head_office table by table.

UPDATE: I've tried to use the Hashbyte but it doesn't work in my case since I'm not comparing row by row due to the amount of data between each store and the head office.  Example: if a store has 100 000 rows for a table, the head office that would have the info for 10 stores would contain 1 000 000 rows in that same table... I can't send all the rows from the store to the head office just to compare and make sure that all the data has been transferred successfully.  So my way of thinking is aggregate the info at the store per date and compare it with the same aggregation for the same store at the head office and if I find a mismatch, I have a process that is triggered to request the info from the store for a specific date to the head office.

I was so happy to find the Checksum_agg of a checksum, which did just that... but after reading and testing different scenarios such as checksum(100.00) giving the same result as checksum(10.00), it killed my whole logic.

Any other ideas? anyone?

The solution that helped me solve this dilemma was to use the hashbytes with SHA2_256 which was then converted to a bigint followed by a conversion to a float to finally be summed up.

Note that the conversion from varbinary to bigint can be dangerous, but in my case it was acceptable.  the conversion directly from varbinary to float is not supported... and to sum up many records with a bigint wasn't enough due to an overflow... hence the reason to convert the bigint to a float.


Composite Key for Gizmo Tracking I'm trying to setup a many to many relationship between an gizmo table and a owner table.

A owner can have many gizmos
And a gizmo can have many owners

When I have a linking table do I set the gizmoID and ownerID as a composite key and then set the foreign keys on the gizmo table to Identity Auto-Increment by 1 and the same on the owner table or do I set composite key to identity auto-increment.

I am working on sql server 2014 Express and fairly new to this and trying to wrap my head around this.

Thanks for the help in advance.

as it will have three table

one table for Owner,  Identity Auto-Increment by 1 will give OwnerId
one table for gizmos, Identity Auto-Increment by 1 will give gizmosId
One table for mapping the many to many relationship as

OwnerId - FK
gizmosId - FK and
 Other columns

Now as the combination of OwnerId and gizmosId will make a row unique you can create primary key constraints that will enforce Uniqueness as well.

ALTER TABLE schemaName.TableName
ADD CONSTRAINT PK_Relation PRIMARY KEY CLUSTERED(OwnerId , gizmosId);


SqlServer output and return value in procedure I'm new to sqlserver, and i have this error that i was not able to fix:

  Msg 245, Level 16, State 1, Procedure info, Line 11 Conversion failed when converting the varchar value 'Steven' to data type int
  Can you please show me where is the error, and how can i fix it? The code is as follow thank you.

CREATE PROCEDURE info (@id int , @howMuch int OUTPUT) AS

select * from emp where empno=@id;

select @howMuch =count (*) from emp;

declare @name varchar(100)

select @name=ename from emp where empno=@id

return @name --with return doesn't work!!
--select @name with select it works !!
--@name as an output parameter it works!!
GO

-- execute the procedure. It gives me error.
DECLARE @num_rows int, @who varchar(100)
EXECUTE @who=info 100, @num_rows OUTPUT

select @num_rows,@who

I have noticed that: If instead of using "return" I use "print" to visualize the "@name", the procedure works, and i see "@name". It also works well if instead of "return" I put "@name" as an output parameter. It seems that the error is only when using "return" to return @name. Why is this? What i'm missing to understand?

It looks like you are attempting to give the value of @name back to the caller, but that is not the purpose of RETURN. When you give an argument after RETURN it must be an integer which indicates the status of the procedure.

Better off declaring @name as an output parameter or SELECT @name.

Ref. https://msdn.microsoft.com/en-us/library/ms174998.aspx


postgresql column referencing issue Facing trouble related to column referencing.detailed problem below
Say I have 2 tables Table Metadata and Table Transactions

Table Metadata has the following information regarding columns

Attribute   Value   desc
EmpID      123456   employee of the month
Name        XYZ     defaulter

The Table Structure of the Transactions table is as follows:

EmpID   Name  Type  Title
0       ABC    A        …
1       DEF    A        …
2       GHI    A        …
3       JKL    A        …
12345   XYZ    A        …

I want to be able to join such that I get the resultset as

EmpID   Name  Type  Title  desc
0       ABC    A        …
1       DEF    A        …
2       XYZ    A        …  defaulter
3       JKL    A        …
12345   PQR    A        …  employee of the month

How can I achieve this? I am using postgresql. So I do understand that I have to use information schema, but confused about the joins. Any help?

This is what you want,

SELECT t.*, coalesce( m1.desc, m2.desc ) AS desc
FROM transactions AS t
LEFT OUTER JOIN metadata AS m1
  ON (m1.attribute = 'Name' AND t.name = m1.value )
LEFT OUTER JOIN metadata AS m2
  ON (m2.attribute = 'EmpID' AND t.empid = m2.value );

What we do is join twice once for each join condition.


What is this database dump format? I have a file which I believed was an Oracle data dump, but Oracle refuses to import it:

There is a problem reading from the import files
ORA-39143
"C:\nm\data\THEFILE.dmp" may be an original export dump file

The Linux "file" utility says it is DBase 3, but I doubt this, because the records number is not realistic and DBase tools fail to load the file:

THEFILE.dmp: DBase 3 data file (1380929624 records)

Here is an hex dump of the beginning of the file:

0003 4501 5058 524f 3a54 3156 2e30 3230

Any idea what database format it could be?

As @a_horse_with_no_name says, it's a file created with the original export utility, i.e an exp command rather than expdp. You'll need to import it with the matching original import utility, using the imp command.


SQL Server 2014 .bak file error - The media family on device 'D:\..' is incorrectly formed Worst case scenario - the server crashed and the 2014 SQL Server .bak file had to be recovered from the failed disk. The .bak file I have is over 20gb in size but is unusable when trying to restore from it. I built a new server and it is running SQL Server 2014 developer, just like the old server (the new server backs up all the database to the cloud, local drives, virtual drives, and network drives. I also have a script on a cron job to verify all the backup media files daily now as well).

I was able to restore some of the data but now my customer is asking us for a certain date range that I can't provide.

I have been reading a lot about this and everything indicates that it is a SQL Server version related issue however I think it has something to do with how the .bak file was recovered (maybe ASCII vs Binary file format?).

I know the data is there or the file wouldn't be over 20gb, I just need to get SQL to recognize it as valid backup media.

Any suggestions is greatly appreciated!

RESTORE HEADERONLY
FROM DISK = 'D:\!Recovered Drives From Old Server\ONTMaster Recovery File\ONTMaster.bak'
GO

RESTORE VERIFYONLY
FROM DISK = 'D:\!Recovered Drives From Old Server\ONTMaster Recovery File\ONTMaster.bak'
GO

Here is the error I receive:

  Msg 3241, Level 16, State 0, Line 1
  The media family on device 'D:!Recovered Drives From Old Server\ONTMaster Recovery File\ONTMaster.bak' is incorrectly formed. SQL Server cannot process this media family.
  Msg 3013, Level 16, State 1, Line 1
  RESTORE HEADERONLY is terminating abnormally.
  Msg 3241, Level 16, State 0, Line 5
  The media family on device 'D:!Recovered Drives From Old Server\ONTMaster Recovery File\ONTMaster.bak' is incorrectly formed. SQL Server cannot process this media family.
  Msg 3013, Level 16, State 1, Line 5
  VERIFY DATABASE is terminating abnormally.

There can be several causes for this error:

The backup is corrupted as it has not been transferred in binary mode If you are using FTP to transfer the backup, make sure the file is transferred in binary mode
The version of your backup is different than the SQL Server instance you are trying to restore it to. You can try to restore to a SQL server instance with the right version and transfer the data with SSIS
The backup is corrupt. In this case your options are limited


mysql - Waiting for table level lock on a table I have a problem with my website.. The last few days, at random times the website has become very slow. I started to investigate the best I could. I saw that the MySQL process was using 85 - 95% of the available memory of my server. (Should I upgrade my memory also?)

I checked my MySQL process log, and I noticed a huge list of queries with: Waiting for table level lock

But what I also noticed, what that ALL of these queries with "table level lock", was only queries which had something to do with my table called "users".

I have 20 other tables, with constant queries, but I don't see them on the list.. So I guess the problem is with the users table?

I want to know how I can improve the table, and eventually remove the table level lock?

I also ran this:

SHOW VARIABLES LIKE 'query_cache%';

Which resulted in this:

query_cache_limit
1048576
query_cache_min_res_unit
4096
query_cache_size
33554432
query_cache_type
ON
query_cache_wlock_invalidate
OFF

Please let me know what I can do to improve my database/mysql.

Perhaps the users table is type/storage engine MyISAM and the others are InnoDB or perhaps the other tables aren't subject to many INSERT, UPDATE, or DELETE queries (IIRC, locking isn't used for SELECT).

See also:

way to prevent queries from waiting for table level lock
https://stackoverflow.com/questions/6543624/what-is-waiting-for-table-level-lock
https://dev.mysql.com/doc/refman/5.0/en/table-locking.html


Performance and profiling on SELECT * FROM [table] ISNULL([column], '') = '' VS EXIST (SELECT * FROM [table] WHERE [condition]) I asked a similar question here. The answerer told me to ask about profiling and performance here on [dba.stackexchange].

I have two condition in my queries that both do the same thing. I was wondering how can I mesure the performance of both to choose the best one. I know I should do profiling, but i don't know how. I've read this and found it to be more confusing than helping. And do do not wish to profile the whole query, only this condition.

IF (SELECT * FROM [table] WHERE ISNULL([column], '')) = ''

IF EXIST (SELECT [column] FROM [table])

I expect the second way to be more performant because it does not call a function, but i am far from being an expert in SQL.

How can i profile them?

or which one is the best?

Well, the IF (SELECT * FROM [table] WHERE ISNULL([column], '')) = '' test isn't really a working test, so it is possible that you made whatever code you are working with too generic when posting here. HOWEVER, in a very general sense, the IF EXISTS should nearly always be better because it is designed to stop processing its inner query upon the first row being returned to it. The first condition you posted does not have that built-in efficiency and would process all rows in the inner query. Of course, sometimes the Query Optimizer can re-write your code to be an IF EXISTS if it recognizes that your non-IF EXISTS query would logically be the same, but I am not sure if that would happen in this specific case. I believe I have seen that the Query Optimizer does rewrite IF ( (SELECT COUNT(*) FROM [table]) > 0 ) as an IF EXISTS.

Regarding how to test the efficiency of each, I would use a base query against the same table so that I could use the same column(s) for filtering (to hopefully make use of the same indexes that the real queries would use) and repeat that query, once for each of the two conditions you are testing for. Then, wrap each in SET STATISTICS TIME, IO [ON | OFF]; as follows:

SET STATISTICS IO, TIME ON;
IF (SELECT * FROM dbo.Table WHERE ISNULL()... )
BEGIN
  PRINT 'First';
END;
SET STATISTICS IO, TIME OFF;

PRINT '-----------';

SET STATISTICS IO, TIME ON;
IF (EXISTS(SELECT * FROM dbo.Table WHERE condition))
BEGIN
  PRINT 'Second';
END;
SET STATISTICS IO, TIME OFF;

Please read the MSDN page for the EXISTS operator for more info regarding how to use it in WHERE conditions.


SQLite - Should I add some indexes even if select queries are fast? I have a SQLite DB with only one table of size ~1.5 MB (in fact, there are ~30 tables in total, but each table is stored in a separate .db file).

When I use EXPLAIN QUERY PLAN for any table, it shows a full table scan, which as far as I know is not good. However, non of the tables have index and also, the speed of select queries are fast.

So, I was wondering whether I should add some indexes on our tables or just keep them as they are? (Now, tables have max 5k rows and in future they may go maximum up to 100k rows).

PS. number of selects and inserts are almost the same..

All ideas are highly appreciated.

Now, tables have max 5k rows and in future they may go maximum up to 100k rows

In cases like this, where time and resource permits, the best way to check this is to manufacture realistic looking data of that size and test your application against that to see how well it scales (or if it doesn't, where the bottlenecks lie). Make sure your data is realistic though: ~100,000 identical rows won't be a good test as the indexes will not be selective enough to be useful, likewise truly random numbers in the column where the data is expected to "bunch" would also not be the most useful test.


COMMIT on top of the pt-query-digest output I've been optimizing my MySQL server and after tweaking a few queries, I ended up with the COMMIT item on top of the pt-query-digest output, as shown in the extract below:

# 322s user time, 770ms system time, 71.75M rss, 223.13M vsz
# Current date: Sat Oct  1 11:12:58 2016
# Hostname: XXXX
# Files: /home/tools/Slow10.log
# Overall: 1.08M total, 1.47k unique, 61.74 QPS, 0.17x concurrency _______
# Time range: 2016-10-01 06:15:09 to 11:06:35
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          2913s     1us     14s     3ms    15ms    27ms   194us
# Lock time           123s       0      2s   113us   167us     3ms    42us
# Rows sent        1019.81k       0   8.58k    0.97    0.99   26.59       0
# Rows examine     314.86M       0   2.13M  305.82  234.30   7.13k       0
# Query size       321.03M       6  14.64k  311.81   1.14k  743.10   88.31

# Profile
# Rank Query ID           Response time   Calls  R/Call V/M   Item
# ==== ================== =============== ====== ====== ===== ============
#    1 0x813031B8BBC3B329 1676.7355 57.6%  70006 0.0240  0.02 COMMIT
#    2 0xBC10C51A724ECD57   61.8928  2.1%    211 0.2933  0.08 SELECT users

And the query is shown as:

# Query 1: 4.00 QPS, 0.10x concurrency, ID 0x813031B8BBC3B329 at byte 462955973
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2016-10-01 06:15:09 to 11:06:34
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          6   70006
# Exec time     57   1677s     9us      2s    24ms    56ms    24ms    16ms
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size     0 410.19k       6       6       6       6       0       6
# String:
# Databases    XXX_XX... (9419/13%)... 40 more
# Hosts        localhost
# Query_time distribution
#   1us  #
#  10us  #
# 100us  #
#   1ms  ####
#  10ms  ################################################################
# 100ms  #
#    1s  #
#  10s+
commit\G

Any word on how can I optimize this?

If further information is required please let me know!

Thanks in advance!

EDIT 2016-02-10: The first analysis before any query optimization looked like:

# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ================ ======= ====== ===== ==========
# 1 0x62057CDB69C17D23 18377.2125 46.3% 139328 0.1319 0.15 SELECT AAAA
# 2 0xEC77079791A0E274 6992.0443 17.6% 127446 0.0549 0.09 SELECT BBBB
# 3 0xB8A934DECC36D811 6283.1217 15.8% 247595 0.0254 0.03 UPDATE users_sessions
# 4 0x813031B8BBC3B329 2042.6094 5.1% 89046 0.0229 0.04 COMMIT

Server has 8Gb RAM with innodb_buffer_pool_size=5G with about 70 databases with 140 tables each.

EDIT 2016-10-06:

# grep innodb /etc/my.cnf
innodb_flush_method=O_DIRECT
innodb_buffer_pool_size=5G
innodb_log_buffer_size=4M

Forensics...

Median time for your COMMITs: 16ms.
Typical time for spinning disk write: 10ms.
Default setting of innodb_flush_log_at_trx_commit: 1, meaning flush to disk on every commit.

Conclusion:  The killer used the candlestick in the library. Or.. Most of what you are doing is very rapidly committing transactions.

Possible ways to speed up:

innodb_flush_log_at_trx_commit = 2 -- This flushes once per second instead of once per transaction.  That is less 'safe', but for some users, the tradeoff is worth it.
Batch you actions.  Instead of one INSERT per COMMIT, do lots of INSERTs.  The main cost in COMMIT is flushing one write to the rollback log, regardless of how much went on in the transaction.
Hardware -- SSD - drops the 10ms to 1ms (or something like that); RAID with battery backed write cache - makes writes take virtually 0 ms, even without loss of safety.
Since a few COMMITs are taking over 1 second, you could look for them and see if their transactions can be improved.


Postgres EXPLAIN ANALYZE is super slow there is Postgres 9.6 on Redhat
I want to EXPLAIN ANALYZE slow query (quite complex one) and it takes too much time. My session with EXPLAIN ANALYZE consume 100% of one CPU and doesn't finish even after 24 hours.
How to analyze the issue if Postgres can't build an execution plan?

If you only want to look at the execution plan, run EXPLAIN without ANALYZE.

ANALYZE will cause the query to be executed, which provides much more useful data, but takes as long as the query runs.


Select and sum data from a oracle table containing duplicates I have a table containing data where some are identical in given columns but different in other columns. I will try to describe this table as follwing:

table1                               table2           table3
+---+------+-----+----------+-----+  +-----+------+  +------------------+
| id|sub_id|block|block date|valid|  | id  |active|  |rec_id| messageId |
|---+------+-----+----------+-----+  +-----+------+  +-------+----------+
|123|123_1 |  0  |          |false|  | 123 |   1  |  | 123   |a123kckcdf|
|123|123_2 |  0  |          |false|  | 321 |   1  |  | 123   |ffsw2234sd|
|123|123_3 |  1  |01.07.2019|true |  | 456 |   0  |  | 123   |kk3jd563sf|
|321|321_1 |  1  |28.06.2019|true |  | 654 |   1  |  | 654   |89hhrlidlf|
|321|321_2 |  0  |          |false|  +-----+------+  | 456   |jf335698ld|
|456|451_1 |  0  |          |false|                  | 321   |fkd003llds|
|456|456_1 |  0  |          |false|                  | 123   |jdjd234lds|
|654|654_1 |  0  |          |true |                  | 654   |djd34lld23|
|654|654_2 |  1  |01.04.2017|true |                  | 453   |jf33lfrd2l|
+---+------+-----+----------+-----+                  +-------+----------+

This what I want to achieve:

Summarize number of records sent to rec_id IF:
 * Block_date has to have occured more than 24 hours ago (presume I will run this query at '01.07.2019')
* The value 'id' in table1, table2 and rec_id in table3 is identical (and can be used in a join)
* The value for "valid" in table1 is can be 'false' for all occureences of sub_id (set manually by mistake in application gui)
* The value for "blacklisted" in table1 must be '1'
* The value for "active" in table2 must be '1'
* The time in "block date" in table1 is in timestamp format really and is not truncated (as it seems like in this example)
* Each message in table3 is a unique message

I would then want to end up with a list like this:

+---------------------+
|  Id  | Num messages |
+------+--------------+
|  321 |     1        |
|  654 |     2        |
+------+--------------+

I have tried with something like this but I have not managed how to get hold of the date period yet and neither how to fully cope with the fact that an records in table1 can be both valid and false and I only want the false ones

SELECT t3.rec_id AS"Id",
COUNT(t3.rec_id) AS"Num messages"
FROM TABLE3 t3
INNER JOIN TABLE2 t2
ON t2.id=t3.rec_id
INNER JOIN TABLE1 t1
ON t2.id=t1.id
WHERE t2.active='1'
AND t1.block='0'
AND t1.valid like 'false'
GROUP BY t3.rec_id
ORDER BY COUNT (t3.rec_id) DESC

(This is to detect faulty messages not beeing detected by our existing routines)

You are almost right, just add below date check for 24 hours.

SELECT t3.rec_id AS"Id", COUNT(t3.rec_id) AS"Num messages"
FROM TABLE3 t3 INNER JOIN TABLE2 t2
ON t2.id=t3.rec_id
INNER JOIN TABLE1 t1
ON t2.id=t1.id
WHERE t2.active='1'
  AND t1.block='0'
  AND t1.valid like 'false'
  AND t1.block_date < sysdate - 1
GROUP BY t3.rec_id
ORDER BY COUNT (t3.rec_id) DESC


SSRS Azure SQL Database Active Directory - Universal with MFA Does anyone know when support for Active Directory - Universal with MFA will be included in Reporting Services?

It's available in SSMS.

Visual Studio 2017 Data Source Properties

Reporting Server Data Source Properties

The MFA auth flow is interactive.  For me, for instance, it opens a browser window, displays an code, and I get a notification on my phone to approve the login.
So you can't use it in background services like SSRS.


Fixing Orphan user on Snapshot mirror I am sort of confused about orphaned users in database snapshots created on the partner server participating in database mirroring.

Server A acts as principal for database X, with server B as its mirror, having database snapshot configured for that database X for reporting purposes.

We recently had a failover. After that I fixed the orphaned users in database X on server A.

However, when I view the same for the database Snapshot on server B, they are not listed.

If i manually try to fix orphans on X-snapshot on server B, it throws the error

  Failed to update database "X-snapshot" because the database is
  read-only

The snapshot job runs every 30 mins.

Please clarify or suggest how to fix the orphaned users in the snapshot

You can transfer the logins that are associated with orphaned users from server A to server B using the method explained in this KB article.

How to transfer logins and passwords between instances of SQL Server

Or you can use Copy-DbaLogin PowerShell command to do it.

Copy-DbaLogin


How to know which record caused an error I have a table named TEMP1 with 5 columns that are all NVARCHAR(100)

I want to transfer the contents of TEMP1 to a table named FINAL, which has the same columns, but this time with targeted data types (DECIMAL, BIT, INT, etc.).

Right now, I do the following:

insert into FINAL
select * from TEMP1

Due to the implicit casting from nvarchar to another type, the statement may fail. If it fails, is there a way to know which line(s) caused the error? I was thinking about doing a WHILE loop to identify the lines in error. Is there a better/more efficient way?

SQL itself doesn't provide any way to do this, an INSERT will always entirely succeed or entirely fail (with one caveat, see #7 below).

So you'll need to do one of the following:

Update/cleanse the data in the source table in advance
Filter the rows to insert, something like WHERE LEN(longfield)<30 will only try to insert rows short enough for the destination field
Properly transform it on the fly: LEFT(longfield,30) will chop off any text that is too long, or use one of the new SQL 2012 functions like TRY_CONVERT, as suggested by Jonathan Fite in the comments.
Insert it one row at a time, using a TRY/CATCH block, and either fix the problem, or log the bad row for action later on
Insert one row at a time, but instead of taking action, just ignore the error on each row, and compare the two tables at the very end.

This assumes you have some key column you can join:

 SELECT TEMP1.*
 FROM TEMP1
 LEFT OUTER JOIN FINAL
 ON TEMP1.Rowid = FINAL.Rowid
 WHERE Final.Rowid IS NULL

SSIS provides a number of tools and options for data transformation and error catching if you're doing large-scale migrations. You can pipe the error rows into a second table, for example.
This won't help you with data conversion errors, but there is one specific kind of error you can "skip and continue": if the error is produced by a unique constraint (a special kind of index that prevents you from entering duplicate data into a table, based on whatever field or set of fields you specify).

This particular constraint can be created with a parameter of IGNORE_DUP_KEY = ON. When this is set to ON only the rows that violate the constraint will fail, the remaining rows will be properly inserted. When it is set to OFF the entire batch will fail.


Remove question marks from database(latin1_general_ci table)? How can I remove question marks(not diamond shaped) from a latin1_general_ci MyISAM table?

I tried going back to utf8 and tried to apply things in https://stackoverflow.com/questions/1476356/detecting-utf8-broken-characters-in-mysql but it did not work, and some cannot work due to fulltext index of the fields.

Example field, viewing from directly database using phpmyadmin or heidisql:

"OSU ??? Oklahoma City"

so, I understand that you just want to remove '?' have you tried this:

UPDATE tablename SET column=REPLACE(column,'?','');


UPDATE statement not consistent when including NULL fields So I want to append Field B to C, then copy field A to B and set field A to "Awaiting update".

I have written the following code

UPDATE Notes

SET

/* append previous month to notes */
Notes=Notes + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10) + '---' + CAST(GETDATE() as         nvarchar(max)) + CHAR(13)+CHAR(10) + cast(LastMonth as nvarchar(max))

/* replace previous month with current month */
, LastMonth=CurrentNote

/* replace previous month with current month*/
, CurrentNote=cast('To be updated' as nvarchar(max))

The problem i've found is that if any of the fields in the row are blank the update statement no longer works as expected.

What is the correct method for going about an update like this?

In SQL Server, when you concatenate string, if one part is NULL the result of the string will be NULL. To avoid this behavior, you have to test all your string with the ISNULL function to ensure a string empty instead of the NULL value.

UPDATE Notes

SET

/* append previous month to notes */
Notes=ISNULL(Notes,'') + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10) + '---' + CAST(GETDATE() as         nvarchar(max)) + CHAR(13)+CHAR(10) + cast(ISNULL(oppo_rota_last_progress_update,'') as nvarchar(max))

/* replace previous month with current month */
, LastMonth=CurrentNote

/* replace previous month with current month*/
, CurrentNote=cast('To be updated' as nvarchar(max))


Optimize rows to columns conversion I have a big Oracle table (around 40 million rows) that looks like this:

ID  Name    Question Answer Reason
3   Name1   1       Yes     blah blah
3   Name1   2       No      NA
3   Name1   3       No      NA
3   Name1   4       Yes     blah2
3   Name1   5       Yes     null
3   Name1   6       Yes     blah3
3   Name1   7       No      null
6   Name2   1       Yes     blah4444
6   Name2   2       No      null
6   Name2   3       Yes     blah3
6   Name2   4       NA      blah5
6   Name2   5       Yes     null
6   Name2   6       Yes     blah6
6   Name2   7       NA      null

I need one row per ID i.e. I will need to add columns for each question's answer (there are 7 questions per ID) and each question's reason. I need to make it look like this:

ID  Name    Q1     Q1-Reason    Q2    Q2-Reason   Q3    Q3-Reason    etc.
3   Name1   Yes    blah blah    No    null
6   Name2   Yes    blah4444     No    null

My query currently looks like this:

select
   A.ID,A.NAME,B1.Q1,B1.Q1-REASON,B2.Q2,B2.Q2-REASON
from
   TABLENAME A
inner join
    (
    select distinct C1.ID,C1.ANSWER as Q1,C1.REASON as Q1-REASON
    from TABLENAME C1
    where C1.QUESTION=1
    ) B1 on B1.ID=A.ID
inner join
    (
    select distinct C2.ID,C2.ANSWER as Q2,C2.REASON as Q2-REASON
    from TABLENAME C2
    where C2.QUESTION=2
    ) B2 on B2.ID=A.ID
...
...

However, as the table is huge, this is taking a VERY long time to retrieve the data. Could someone suggest ways to optimize this query? Any help would be appreciated!

I'm on Oracle 10g and SQLDeveloper 4.0.2.15

Instead of using so many JOINs to get the result, you might get better performance pivoting the data using an aggregate function with a CASE expression.  Oracle 10g doesn't have a PIVOT function so you'd have to use this type of query (aggregate/CASE) if you aren't going to use multiple JOINs on your table.

Since you have a limited number of questions (7), you can easily hard-code the query:

select
  id,
  name,
  max(case when question = 1 then Answer end) Q1_Answer,
  max(case when question = 1 then Reason end) Q1_Reason,
  max(case when question = 2 then Answer end) Q2_Answer,
  max(case when question = 2 then Reason end) Q2_Reason,
  max(case when question = 3 then Answer end) Q3_Answer,
  max(case when question = 3 then Reason end) Q3_Reason,
  max(case when question = 4 then Answer end) Q4_Answer,
  max(case when question = 4 then Reason end) Q4_Reason,
  max(case when question = 5 then Answer end) Q5_Answer,
  max(case when question = 5 then Reason end) Q5_Reason,
  max(case when question = 6 then Answer end) Q6_Answer,
  max(case when question = 6 then Reason end) Q6_Reason,
  max(case when question = 7 then Answer end) Q7_Answer,
  max(case when question = 7 then Reason end) Q7_Reason
from tablename
group by id, name;

See SQL Fiddle with Demo


Copy last modified files from network path into SQL server box and rename them using Agent job I have to copy latest backup from a network path and bring that to my SQL server physical drive for restore, I have to do that using agent job.

This is what i am doing.

Created a SQL server agent job

1) XCOPY source *.bak /Y destination < This copies all the files, but I need the latest file >
2) Ren *bak mydbbackup.bak <**added this into step 2 of job **>

Questions:

What mistake am i doing here ?

*Are there any xcopy **/**commands which can copy the latest files only and ignore old files ?

Is there a way i can do that using another SQLCMD command besides creating a stored procedure*

Thanks,
learner

Found a work around :

I have created ps1 file using the script below....
.
.
.
$source = ''
$destination = ''

@(Get-ChildItem $source -Filter *.bak | Sort LastWriteTime -Descending)[0] | % { Copy-Item -path $_.FullName -destination $("$destination\CMSOPEN_backup.bak") -force}
.
.
.

and then, created bat file which calls this ps1 file, went to sql agent job, created step to execute the bat file later executed the t sql for restore.


Consistent query performance over time We're running an intensive application load (thousands of operations/second) against a SQL Server database with quite a bit of data. Some tables have billions of rows, several of these have lots of inserts and updates.
DB performance is generally quite OK, but periodically we get query performance problems; rather simple queries that previously worked just fine may take 10-100x the time all of a sudden.
This appears to be related to table/index statistics and the query optimiser - most times a statistics update will fix the problem, then again other times an update of the statistics will make the situation worse (re-running the statistics update will then typically solve the problem eventually).
What appears to be happening is that the optimiser decides to use objectively wrong indexes for some queries; all of a sudden, after having used the correct one for days and weeks.
My question is: Why is this happening and what can we do about it?
This database has been running for years with basically the same load, pretty much the same queries, and the same amount of updates. For 99.995% of the queries, there should be no reason to ever decide on different index strategies over time, regardless of the input (and - indeed - doing so will demonstrably completely destroy query performance).
As indicated above, automatically updating statistics on a schedule will often generate horrible problems - if the statistics sample gets skewed (which appears to happen at least 5% of the times) we end up in a world of pain.
Is there any way to tell SQL Server (on certain tables) that the statistics histogram and density will not change over time, so please just continue using the same query plan for queries involving this table? If not, how can we ensure a predictable result of statistics update over time (avoiding the skewed statistics problem described above)?
No stored procedures. We do have control over the SQL, so it can potentially be changed but it's a LOT of code so it would be unfortunate if we had to change every single query (e.g. adding in an additional clause).
A follow-up question: parameter sniffing only appears to be relevant for stored procedures, is that correct?

I suggest you first determine whether its the stats or if its parameter sniffing that is hurting you.
Regardless of above I suggest you read Erland's article on the subject.
What to do about it is difficult to say. We don't know whether it is stats or sniffing.
But possibly adding OPTIMIZE FOR can be "the" solution. It is cheaper than RECOMPILE since you don't have to take the plan production hit on each execution. And it gives you predictability. This, of course, assumes that you don't have the case where the stats differ so much so the same parameter input yield different plans because of stats reasons.
Try to identify one query. See if you have one or many plans for the query. Test with OPTIMIZE FOR and/or RECOMPILE. The one "global" option at database scale you have is to disable parameter sniffing for the database. This means the optimizer optimizes as it doesn't have a clue of the value. All this and more in Erland's article.
Parameter sniffing does not only apply to stored procedures. It also applies to parameterized SQL (typically executed using sp_executesql), which likely is far more common nowadays than stored procedures.


What schema for different relation from two tables to another? There are three tables instructors, meeting_points and bookings. Here are the relation I want:

1 instructor has 0, 1 or n meeting_points
1 booking has 1 meeting_point
1 meeting_point can be linked to 1 instructor + n bookings or just 1 instructor
There are no direct relation between instructors and bookings

I don't know how to represent this properly in a PostgreSQL table (or even in MySQL or other SQL). I can use a foreign key on instructor id in meeting_points table and and add a foreign key on meeting_point id in bookings table but I'm not sure it's the best way (I think it's confusing). How can I represent these relations? Do I need a pivot table for meeting_points with nullable relations?

It's my first post on dba stackexchange, I hope it's clear enough and not a duplicate post (or off topic)

EDIT - Reading answers, I think I must add a little more information.

Meeting points are declared by instructors. So, an instructor says: "you can meet me at X, Y or Z point". And other people (let says these are learners) can book a lesson in a particular meeting point: this is a booking.

In other words, the relation between instructor and meeting point is: an instructor declares available meeting point with him to potential learners. And a learner (learner does not matter in my question) says: "ok, I ask for a booking in that meeting point" (the meeting point is one of the available meeting point declared by the instructor).

Now, other (maybe useless) information:

Meeting points are not shared between instructors, instructors choose and "write" their own named and geo-localized meeting point (it can seems strange, but trust me!).
A booking has different status (accepted, pending, canceled) and is "created" by learners, so there can be n bookings for one lesson on x meeting points (lesson does not matter in this question). But the meeting points are only one of the meeting declared by the instructor who made lesson available.
Bookings have one foreign key on lesson, and one foreign key on learner.

Rearranging / rephrasing your relationships a bit:

1 instructor has 0, 1 or n meeting_points.
1 meeting_point is linked to 1 instructor.

This means there is a simple 1:n (1::0-n) relationship between them. The foreign key should be in meeting_points, referencing instructors.
1 booking has 1 meeting_point.
1 meeting_point is linked to 0 or 1 booking.

This means there is a simple 1:1 (1::0-1) relationship between them. The foreign key should be in bookings, referencing meeting_points.
There are no direct relationships between instructors and bookings.

The implementing is obvious here.

Without further information about other attributes and possible unique keys, I'll use the above, adding surrogate unique keys in the first two tables (this is not needed, the surrogate keys could be replaced by other unique keys you may have identified). The bookings table does not need a surrogate anyway, as it's in a 1:1 relationship with meeting_points and the same column can be used as primary key and for the foreign key constraint:

CREATE TABLE instructors
(   instructor_id SERIAL NOT NULL,
    instructor_name TEXT NOT NULL,
    -- other attributes
    PRIMARY KEY (instructor_id)
);

CREATE TABLE meeting_points
(   meeting_point_id SERIAL NOT NULL,
    meeting_point_name TEXT NOT NULL,
    -- other attributes
    instructor_id INT NOT NULL,
    PRIMARY KEY (meeting_point_id),
    FOREIGN KEY (instructor_id)
        REFERENCES instructors (instructor_id)
);

CREATE TABLE bookings
(   booking_id INT NOT NULL,
    booking_date DATE NOT NULL,
    -- other attributes
    PRIMARY KEY (booking_id),
    FOREIGN KEY (booking_id)
        REFERENCES meeting_points (meeting_point_id)
);

Sidenote: The relationship "a meeting_point has (max) one booking" does not sound very realistic. So, if A is an instructor and M is a meeting_point, A can only make one booking in M? What if she wants to make another booking in the same point tomorrow? This would not be allowed under this design.


Advice For A New Senior DBA I'm finally taking the plunge and moving up from Junior DBA at a large organisation to Senior DBA at a smaller place running SQL Server. It's going to take some time for me to get a good picture of the businesses' requirements and things might be in a bit of a state as they've been without a DBA for 6 months or so.

Is there any advice you would give to a someone starting in this position barring the things like producing a list of SQL Server instances, finding out restore point and recovery time targets, and trying to decipher what state things were left in?

Thanks!

Like you've already suggested, as soon as I got there I'd perform an inventory of all SQL Servers before then spending time running the latest version of sp_Blitz® – Free SQL Server Health Check Script and auditing the results.

Depending on the results I'd then probably look to get in and standardise Ola Hallengrens SQL Server Maintenance Solution. You can then concentrate on running checkDB, index maintenance and getting backups offsite, security (logins, linked servers) etc.

Once the data is safe, I'd then look to either bring in a third party monitoring solution or build a basic one myself. At a minimum, I'd start capturing wait stats every minute or so.


Window aggregate resulting in slow query on postgres 9.6 I'm using Postgresql 9.6 and I'm running into a performance issue. I have a relatively simple query:

explain analyze
 SELECT * FROM (
     SELECT A.person_id, O.session_id,  timestamp_ AS step_ts_min ,rank() over (partition by A.person_id order by timestamp_) as rn
     FROM event_page O
     JOIN alias A ON (O.person_alias = A.alias)
     WHERE O.timestamp_ between '2017-02-18 00:00:00.000' AND  '2017-03-13 00:00:00.000'
     and O.location_host like '2016.myhost.ca'
) as innerstep WHERE rn=1

The event_page table is actually a view over monthly tables union-ed together, you can see that in the plan.

This query takes over 2.5 minutes to run and you can see the lions share of that time is used by quicksort, along with 1.8G of ram!!!

Here is the execution plan:  https://explain.depesz.com/s/v51h

Is there a way to improve performance and optimize the query?

Your window function has to sort 10,453,164 rows. Look at cutting that down. Sorting 10.2 million rows in 158 seconds isn't too bad.

Sort (cost=3,047,865.12..3,073,554.61 rows=10,275,796 width=55) (actual time=144,515.353..148,038.268 rows=10,453,164 loops=1)
Sort Key: a.person_id, event_page_2016_8.timestamp_
Sort Method: quicksort Memory: 1863192kB

I also think there is something wrong with your partitioning.

->  Index Only Scan using ep_2016_8_host_ts_a_ses on event_page_2016_8  (cost=0.55..4.57 rows=1 width=42) (actual time=0.016..0.016 rows=0 loops=1)
                        Index Cond: ((location_host = '2016.myhost.ca'::text) AND (timestamp_ >= '2017-02-18 00:00:00'::timestamp without time zone) AND (timestamp_ <= '2017-03-13 00:00:00'::timestamp without time zone))

Why does this query search a table called ep_2016_8_host_ts_a_ses when the timestamp clearly exists in 2017-02, and 2017-03. The query planner is supposed to know better. Look up constraint exclusion

My guess is here that the query can't make use of the partitions index to sort the table. It should be able to -- if all partitions were indexed by timestamp_ I would think it could walk through the indexes in parallel and get just the first rank fairly easily. I could be wrong though. I may have to play with this later. In the mean time, try getting constraint exclusion working and giving it another go.


Can relationships have relationships? I have the following requirements (as part of a larger system).

In the database, I need to maintain a list of parts, suppliers who sell those parts.

Many suppliers can sell the same part and each part can be sold by many suppliers. However, the prices vary.

In my system, I should be able to keep track of the one, and only one, supplier we purchase a specific part from.

The way I figure it, I need two relationships on the entities Supplier and Part. One relationship Sells recording that the particular supplier carries the particular part and vice versa, and another relationship Purchase that records who we purchase an part from.

However, I don't record the attribute price, twice. I think that would be bad practice. Therefore, I ask, can I relate one relationship to another?

I've included a sample diagram below. The dashed line is what I'm not sure about.

You want to avoid any inconsistency in prices - and you have already identified this in your question.

I'd say use a "Pricing" table which stores Supplier-Parts(PK) and Prices(Float or Decimal value etc).

That way, each part from a different supplier has a different price. In other words, the price depends on what the Part is AND who the Supplier is (Supplier-Part is the Primary Key or Unique Index).

The Purchases table should just include records from this new "Pricing" table - which links to all the info from Parts, Suppliers and Prices.

Makes sense?

EDIT: I just noticed. You are relating Parts and Suppliers again in your Purchases table, when you already do it in your Sells table.

Just have these 4 tables:

Parts: PartID (PK), PartName
Suppliers: SupplierID (PK), SupplierName
Pricing: (PartID,SupplierID) (Unique Index of these two Foreign Keys), Price
Purchases: PurchaseID (PK), (PartID,SupplierID) (Foreign Key), Quantity


Database msdb stuck in recovery I encountered a problem while testing recovering backups (Veritas BackUp Exec 2015, just for you to know).

Main Problem is: I couldnt restore the databases - looking on the target Server where I want to restore the DBs, SSMS tells me, 'msdb' database is being recovered.

I've already tried:

RESTORE DATABASE MSDB WITH NORECOVERY | ...WITH RECOVERY

The command was executed (no Errors), but nothing happened (even after restarting SQL Services & a Server restart).

I tried to set it offline, but running

ALTER DATABASE MSDB SET OFFLINE

tells me, that the msdb-Database can't be set as offline.

I need a way to get the msdb database out of recovery. Unless it will be recovered anyway, the current data is useless for me. Is there a way to just force it out of recovery (even if its damaged after executing the command)?

SELECT [state_desc] FROM [sys].[databases] WHERE [name] = N'msdb';

returns "RESTORING".

Converting my comment to answer :

restore database msdb with replace, recovery

Note : if the file location is same, then you dont need to use with move in the restore clause.


What's a good tool for visualizing SQL Agent jobs? I find the built-in MS SQL Studio tools for managing Agent jobs to be a little frustrating.  What tools out there have you found helpful?

Offhand, there are three things I'd like to see in such a tool:

A graphical summary of which jobs ran when, for how long, and whether they succeeded.
A current status view, like the Job Activity Monitor, but refreshed in near-real time.
A more convenient interface for duplicating or modifying jobs (e.g., compare two job steps without being blocked by modal dialogs).

It would probably be simple to write a little app to handle this, but someone's surely already done it, and done it better.

This is obviously a subjective question, so if a mod of some sort wanders past, feel free to make it a CW.

Did some googling around and here are some software that may interest you:

SQL Job Manager
https://www.idera.com/productssolutions/freetools/sqljobmanager
Query Currently Running SQL Server Agent Jobs
http://sqlconcept.com/2011/06/25/how-to-query-currently-running-sql-server-agent-jobs/

If anyone has more links to add, go ahead!

For point 3, the link query and notes are here -

Today I went on a quest to discover a way to list all currently running SQL Server Agent jobs. As every other quest, this one also started with Google-ing. :)

Within 2 minutes I found this great post by Brent Ozar on SQLServerPedia. Why is this post so great? It is so great because Brent figured that if you query only the sysjobs and the sysjobhistory tables you will not get accurate current job status. I.e. in the sysjobhistory table you have a column run_status, however (even though according to the BOL the possible values for this column are “Status of the job execution: 0 = Failed,1 = Succeeded,2 = Retry,3 = Canceled,4 = In progress”) in reality, the value will never be 4 (In Progress). Actually, in the sysjobhistory table is kept historical data of each job step executed, which means that the status of the step is updated only after the next step is executed. In other words, the table is NOT updated in real time, nor every other second.

So, Brent figured out that there is a undocumented stored procedure sys.xp_sqlagent_enum_jobs, which is part of sp_help_job, which can give the current execution status of the Agent Job.

Even though I found a way to get the currently running jobs, I was not happy with this script because it runs only on SQL 2005 / 2008.

What should I do if I have a SQL 2000 instance and am very curious about the currently running jobs?

With a bit of Tim Chapman’s help (the master of www.SQLServerNation.com) I figured out how to do it. THANK YOU, Tim!

Here is the final script, which will run on SQL 2000, 2005 and 2008, and which will give you the currently running SQL Server agent jobs. (As you can see, the difference with Brent’s script is very little: instead of “sys.xp_sqlagent_enum_jobs” I am using “master.dbo.xp_sqlagent_enum_jobs” and I am limiting the resultset to get only the currently running jobs by using “where x.running = 1″).

Simple as it is. Enjoy.

IF EXISTS (SELECT *
FROM    tempdb.dbo.sysobjects
WHERE   id = OBJECT_ID(N'[tempdb].[dbo].[Temp1]')
)
DROP TABLE [tempdb].[dbo].[Temp1]
GO
CREATE TABLE [tempdb].[dbo].[Temp1]
(
job_id uniqueidentifier NOT NULL,
last_run_date nvarchar (20) NOT NULL,
last_run_time nvarchar (20) NOT NULL,
next_run_date nvarchar (20) NOT NULL,
next_run_time nvarchar (20) NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname
COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
DECLARE @job_owner   sysname
DECLARE @is_sysadmin   INT
SET @is_sysadmin   = isnull (is_srvrolemember ('sysadmin'), 0)
SET @job_owner   = suser_sname ()
INSERT INTO [tempdb].[dbo].[Temp1]

--EXECUTE sys.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
UPDATE [tempdb].[dbo].[Temp1]
SET last_run_time    = right ('000000' + last_run_time, 6),
next_run_time    = right ('000000' + next_run_time, 6);
-----
SELECT j.name AS JobName,
j.enabled AS Enabled,
CASE x.running
WHEN 1
THEN
'Running'
ELSE
CASE h.run_status
WHEN 2 THEN 'Inactive'
WHEN 4 THEN 'Inactive'
ELSE 'Completed'
END
END
AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE
WHEN x.last_run_date &gt; 0
THEN
convert (datetime,
substring (x.last_run_date, 1, 4)
+ '-'
+ substring (x.last_run_date, 5, 2)
+ '-'
+ substring (x.last_run_date, 7, 2)
+ ' '
+ substring (x.last_run_time, 1, 2)
+ ':'
+ substring (x.last_run_time, 3, 2)
+ ':'
+ substring (x.last_run_time, 5, 2)
+ '.000',
121
)
ELSE
NULL
END
AS LastRunTime,
CASE h.run_status
WHEN 0 THEN 'Fail'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancel'
WHEN 4 THEN 'In progress'
END
AS LastRunOutcome,
CASE
WHEN h.run_duration &gt; 0
THEN
(h.run_duration / 1000000) * (3600 * 24)
+ (h.run_duration / 10000 % 100) * 3600
+ (h.run_duration / 100 % 100) * 60
+ (h.run_duration % 100)
ELSE
NULL
END
AS LastRunDuration
FROM          [tempdb].[dbo].[Temp1] x
LEFT JOIN
msdb.dbo.sysjobs j
ON x.job_id = j.job_id
LEFT OUTER JOIN
msdb.dbo.syscategories c
ON j.category_id = c.category_id
LEFT OUTER JOIN
msdb.dbo.sysjobhistory h
ON     x.job_id = h.job_id
AND x.last_run_date = h.run_date
AND x.last_run_time = h.run_time
AND h.step_id = 0
where x.running = 1


#1194 - table 'user' is marked as crashed and should be repaired I have problems getting into Mysql on phpmyadmin
(mysqli_real_connect()(HY000/1130)Host 'localhost'is not allowed to connect to this MariaDB server )
I have found out its a crashed file in mysql called "user'
All the info I have read says to fix a crashed table use the dropdown menu 'With Selcted' . My 'With Selected' is not a dropdown (XAMPP Version: 7.3.7 , Control Panel Version: 3.2.4 ). It is in italic.
Is there a shell command that I can use.
Thanks

Just posted my question and I got a comment answer from my previous question on Stack Overflow:

First try to run the following command: mysqlcheck -u root -p --auto-repair --check --all-databases – Philip Petrov 1 hour ago


How do I form an SQL query to show entire column of dates with modified values? Assume that I have a table Purchases with column named PurchaseDate and with data format of 'YYYY-MM-DD HH:MI:SS'.
Suppose that I want to see the entire column of dates with the days increasing by 5.

Suppose I form the query in the following way:

SELECT PurchaseDate+5 FROM Purchases;

The result will be given in a form of single numbers, and the increased part will be seconds instead of days.
I.e.: This 1982-09-07 00:00 will turn into this 198209070005.

How do I form the right standard SQL query?

UPDATE: I've got answer. Thanks.

-- this will work for standard SQL:

SELECT PurchaseDate, PurchaseDate + INTERVAL 5 DAY FROM Purchases;

-- this will work for MySQL:

SELECT PurchaseDate, ADDDATE(PurchaseDate, INTERVAL 10 DAY) FROM Purchases;

Asuming the column has a datetime type, simply:

SELECT PurchaseDate + INTERVAL 5 DAY FROM

If it was a string type, you would have to parse it first.


"zip" a mySQL (phpMyAdmin) database I've created a large database on my local WAMP webserver.

I would like to send that database with all tables and table data to another computer.

Is this possible?

mySQL ver: 5.5.15
PHP ver: 5.3.8
Apache ver: 2.2.21

Thanks

Yes, if you use phpMyAdmin on your WAMP server, you can use the Export tab to dump your database and all the data in it.

On the Export tab, choose the Custom export method. This opens a bunch of options. Under Save Output to a File, choose Compression and you get to choose zipped from a drop-down.

Then you can Import that zip to another MySQL instance on another computer.


How to Concatenate DateTime to nvarchar field I have a table with a nvarchar(32) column and I want to update the value in this column with the first 10 bytes of another field on the table which is defined as nvarchar(32) after the 10 bytes I want to concatenate the first 22 bytes of the current datetime without the dashes in the date.

Update Bil_ReturnsRepository
  Set PaymentReference = CAST((Left(PolicyNumber, 10) + CONVERT(datetime,GETDATE(), 121)) as nvarchar
Where PolicyNumber = '1234567890'

I would like for the output to look like this for example:

1234567890201802130446386883

Datetime without punctuation and up to 7 second decimals are 21 digits so there would be 1 character (32 - 10 - 21) still missing.

You can use the following to format the way you want.

Update Bil_ReturnsRepository Set
    PaymentReference = CONVERT(NVARCHAR(32),
        CONCAT(
            LEFT(PolicyNumber, 10),
            FORMAT(SYSDATETIME(), 'yyyyMMddHHmmssfffffff0')))
Where
    PolicyNumber = '1234567890'

If PolicyNumber is already NVARCHAR then there is no need to do an explicit CONVERT. Notice that I hard-coded the last zero.


Always On log files bigger than database files We had SQL RAP and we got warning that Transaction log files larger than the database files.

They advised me to remove databases from Always ON, change recovery mode to SIMPLE and then shrink log files and put it back to Always On. And change maintenance plans. T-LOG backup is running every hour. But again next day  transaction log files are larger than the database files.

As this is running SharePoint 2013 databases, they said it could be do to the migration of SharePoint 2010 databases to 2013, but I have same behavior on another Always On cluster, which is not SharePoint. I have tried also to implement Ola Hallengren's maintenance script with same results. On other machines without AO there is no such problems.

So I am not sure why is this happening, is it related to Always On? I know that Always On AG Databases need bigger Log files, so would this be normal behavior in AO?

I have looked at Transaction log is growing very fast. I only have log_reuse_wait=2 in between log backups, everything looks to be working as expected but it is not.

I checked the log send queue and REDO queue size, and it looks good, as it only has "2 Log backup You are waiting for a log backup to occur." while it waits for next log backup. AOAG health is OK and bandwidth is more than enough. Log backup is every 15 minutes.

So I am not sure why is this happening, is it related to Always On?

It is and also isn't related to Always On. Always On Availability Groups requires that the database be in the full recovery model - which is why you need log management in the first place. So long as your secondary replicas are staying very close to the primary (i.e. no lagging secondary replicas for hours or days) log management shouldn't be affected very much due to AGs.

  I know that Always On AG Databases need bigger Log files, so would this be normal behavior in AO?

Always On Availability Groups does not inherently need larger log files. Generally it ends up being the cause because most databases are in the simple recovery model before the change is made to Availability Groups and thus log managements is a new concept to that database.

If the database was in the Simple recovery model and the log grew to X GBs, then the minimum log size will be X GBs regardless of recovery model, Always On, etc. However, since log management isn't automatic anymore a random number (generally 30 minutes or an hour) is arbitrarily chosen and log management happens on those intervals. Now the log will need to grow to accommodate the changes in log management automation.

Much as @Shawn Melton and @RDFozz have stated in the comments - if you don't want the log growing too large and all of your replicas are properly in sync, then have your log management happen more frequently.

  As this is running SharePoint 2013 databases ...

SharePoint is known for having their own timer jobs which run internal maintenance items and storing blob data. This, on its own, will cause poor log management and there is simply nothing you can do about it. Thus it doesn't surprise me that the log the this database is larger than the data files.

It's never a good sign when the datafiles are smaller than the log, but I do believe in this case - unless you change the log management automation to shorter intervals [And even then it depends on what SharePoint does to the DB] it'll most likely end up staying that way - and that might be normal. In that case we can mark it as normal and go about our day.

  We had SQL RAP and we got warning that Transaction log files larger than the database files.

That's a great step in the right direction (I work for Microsoft) of having a risk and health check of your instance(s). This specific check is something that we us to look for databases that may be having improper or no log management. In some cases (I've personally dealt with) the DBA has said, "Ooops that shouldn't be in bulk-logged..." and we've changed it back to Simple because it didn't need any point in time or extended recovery options. That's ok! The check does, however, give everyone a opportunity to look at the databases in question and make sure that something isn't wrong and that's the key distinction. Like I said above, this might just be normal and that's ok - however we wouldn't be doing our jobs properly if we just left it alone and didn't say anything, it's important.

Anecdotally when I was a DBA for a large clothing company, we had a 3rd party application that used a cursor to loop through all employees every 15 minutes and run a stored procedure which deleted all of their history and then rebuilt it from scratch... in a single transaction. The database was 50 GB and the log file for it was.... 280 GB. That's how the app worked, I logged complaints and possible code changes with the 3rd party vendor and was summarily dismissed. It happens and some application just run that way.