How to design SQL schema that treats historical data as first-class citizen? I'm currently designing a SQL Server database schema for an application that consists of highly structured "tree" data. So, a table might look like this:

id
parentId (references id in same table)
text

This is simple enough, but I also need to be able to update either the parentId of a node or the text of a node (or delete nodes), and access each revision of the tree at the same speed that the current revision can be retrieved. My current solution is to have two tables. First a revision table:

id
revisionDate

And then a modified node table:

id
UID (same for all revisions of a specific node)
revisionId (references id of revision table)
deleted (flag for deleted node)
parentUID (references UID of parent node)
text

So to get the tree for a specific revision, you take the revisionId and all revisionIds before it, then query the node table for all nodes with those revisionIds, then take only the highest revisionId nodes for nodes that share a UID, then delete any node that has the deleted flag set.

This works, but gets ugly fast (I'm using a simplified example) I actually have about 10 tables like this that need revision tracking, with each table having cross references to each other. This approach also breaks foreign keys, since UIDs are not unique. I cannot simply copy the full tree data for each revision, since there may be hundreds of revisions per hour.

What would the best practice be for a problem like this? I'd be willing to use a nonrelational database if it would be clearly superior.

Trying to do FKs will just frustrate you - you don't actually need it.

If you handle versioning by using an InsertDateTime concept, then you're basically describing a Type2 dimension as used by many data warehouses. There is quite a lot of material out there about tuning systems that sit over the top of data warehouses, but from a T-SQL perspective, consider using APPLY. Like this:

SELECT ...
FROM ... AS t
CROSS APPLY (
    SELECT TOP (1) ...
    FROM ... AS rev
    WHERE rev.UID = t.UID
    AND rev.InsertDateTime < @revdatetime
    ORDER BY rev.InsertDateTime DESC) AS something
WHERE something.IsDeleted = 'N'

This kind of construct benefits from an index on (IsDeleted, UID, InsertDateTime DESC).

Alternatively, if you want to look at the whole table as at a particular time, use ROW_NUMBER() like:

WITH numbered AS
 (  SELECT *, ROW_NUMBER() OVER (PARTITION BY UID ORDER BY InsertDateTime DESC) AS rownum
    FROM ...
    WHERE InsertDateTime < @revDateTime
),
VersionAtTime AS
(   SELECT *
    FROM numbered
    WHERE rownum = 1
    AND IsDeleted = 'N'
)
SELECT ...
FROM VersionAtTime
....

(edited from original to move the IsDeleted predicates outside the sub-queries, removing all versions at that particular time)


Converting date string to SQL DATETIME - hopefully without a udf I am taking an Excel file that has dates in different formats but one of these formats is giving me some grief when I try converting it or casting it to a SQL DATETIME. For example:

Thu Oct 16 13:40:19 2014 ,Tue Apr  1 12:20:40 2014, etc.

Essentially the format is ddd MM dd HH:mm:ss yyyy.

I was wondering if there's some kind of date style that would allow me to do a convert in one line. I could write a function for this but I was wondering if there was a more elegant solution. I Googled with no luck.

This works:

DECLARE @mycol VARCHAR(30) = 'Tue Apr  1 12:20:40 2014'

SELECT CAST(SUBSTRING(@mycol, PATINDEX('% %', @mycol), 100) AS DATETIME)

The day name at the beginning is what prevents it from being able to convert directly.

Instead of declaring the variable, you can just substitute the column containing your dates for @mycol in the script above.


Migrate azure sql database to Azure SQL Database Managed Instance We need to migrate one of the database into MI from Azure Sql. Has anyone done it before.Looking for any documenatation/Guidance on this as not much is available. Will be testing the below method soon.

One way would be to import the bacpac to blob and then use to restore in the MI. Any suggestions?

Since the Managed Instance was in preview, the first option we have is to export your Azure SQL Database as a bacpac then import that bacpac to your Managed Instance.

A better option we have now is using the Azure Data Migration service. It is not only faster than the method I described above, but it gives you the option to perform a data validation at end and it rebuilds all indexes on the database. Here you have a step-by-step tutorial.


how to combine multiple timestamps and keep the most recent one? I have a table with many timestamps in it, some of which may be null.  Is there a way to combine them all and keep the most recent timestamp so I don't have to reference each timestamp individually?

For example, given this over-simplified example:

SELECT blah
FROM foo
WHERE
    ts1 >= '2018-04-17' OR
    ts2 >= '2018-04-17' OR
    ts3 >= '2018-04-17';

I suspect there must be a way to turn it into something like this:

SELECT blah, somefunc(ts1, ts2, ts3) AS ts
FROM foo
WHERE ts >= '2018-04-17';

You need the greatest() function:

SELECT greatest(now(),
                now() + interval '1 minute',
                CURRENT_DATE::timestamptz,
                NULL);

           greatest
───────────────────────────────
 2018-04-17 19:40:04.568827+02

About NULLs:

  NULL values in the list are ignored. The result will be NULL only if all the expressions evaluate to NULL.

Please note that comparing timestamps (especially timestamps with time zones) to simple date values sometimes result in surprises.  I always try to use something like date_trunc('day', '2018-01-05'::date) to get a type that can be compared cleanly.


Why Java Pool is not a required component of Oracle SGA? Why Java Pool is not a required component of Oracle SGA?

As we know SGA has many components, I mean:

Java Pool
Shared pool
Large Pool
Data Buffer cache
Others...

I took an exam and I saw that afirmation that i never understood: Java Pool is not a required component of SGA.

Java inside the database is optional. If it is not installed, why waste memory on it?

Since 11.2.0.4, it is possible to "disable" it if patch 20681008 is installed, with the below parameter:

"_bug20681008_force_java_pool_zero" = true

Starting with 12.2, the below can be used:

"_force_java_pool_zero" = true


Ordering rows based on cross reference between columns I have a table like this:

Lat1  Lng1  Lat2  Lng2
----  ----  ----  ----
1     1     2     2
2     2     5     5
3     3     4     4
4     4     3     3
5     5     4     4

Is it possible to return the rows in the following order?

Lat1  Lng1  Lat2  Lng2
----  ----  ----  ----
1     1     2     2
2     2     5     5
5     5     4     4
4     4     3     3
3     3     4     4

There is a unique rowid column.

The (Lat1,Lng1) and (Lat2,Lng2) pairs will always be unique.

lat1 and long1 of row n+1 must be the same as lat2 and long2 of row n.

You can do this with a hierarchical recursive CTE.

The only problem is that you have a loop in your data, due to there being 3,3,4,4 and 4,4, 3,3.

drop table #tmp;

create table #tmp
(
lat1 int,
lng1 int,
lat2 int,
lng2 int
);

insert into #tmp values (1,1,2,2);
insert into #tmp values (2,2,5,5);
insert into #tmp values (3,3,4,4);
insert into #tmp values (4,4,3,3);
insert into #tmp values (5,5,4,4);

WITH cte AS
(
  SELECT
   lat1,lng1,lat2,lng2,
    0 AS level, cast('('+cast(lat1 as varchar) + ',' + cast(lng1 as varchar)+')' as varchar) as path
  FROM #tmp
  WHERE lat1=1 and lng1=1
  UNION ALL
  SELECT
  t.lat1,t.lng1,t.lat2,t.lng2,
  level+1, cast(path+','+'('+cast(t.lat2 as varchar) + ',' + cast(t.lng2 as varchar)+')' as varchar)
  FROM #tmp t
  INNER JOIN cte c
    ON c.lat2 = t.lat1 and c.lng2 = t.lng1
)
select * from cte;
;

The WHERE lat1=1 and lng1=1 part specifies the base of the hierarchy (which is probably the start of a journey, in this case).


Elements inside a container: How to perform atomic operations on them? In my DB (Postgres 9.4) I have the following items: Tshirts, Jeans, Shoes and Boxes.

Tshirts, Jeans, Shoes can be placed inside a box and they can have a color (the color may be null).
Each element of the box MUST have the same color of the other.

To check (and also to ensure) the last condition I use a trigger.

/*
If I try to INSERT (OR UPDATE) an item in a box
containing elements of different color, it raises an exception.
*/
BEGIN
   IF  (
         SELECT color FROM tshirt WHERE tshirt.box_id = NEW.box_id
         UNION SELECT color FROM jeans WHERE jeans.box_id = NEW.box_id
         UNION SELECT color FROM shoes WHERE shoes.box_id = NEW.box_id
       ) <> NEW.color THEN

          RAISE EXCEPTION 'Error..';
          RETURN NULL;

    END IF;
END;

Now, let's suppose I want to change the color from 'Blue' to 'Red' for any item inside a Box (let's say box_id = 1):

UPDATE Tshirts SET color = 'Red' WHERE box_id = 1;
UPDATE Jeans SET color = 'Red' WHERE box_id = 1;
UPDATE Shoes SET color = 'Red' WHERE box_id = 1;

This code will fail because of the trigger.

The only way I can change the color of each item in the box is:

Remove all items from the box (setting their box_id to null).
Change the color of all the elements.
Put all the elements inside the box (setting back their box_id).

Is there any way (through modeling or triggers) to avoid this tricky situation? It would be great if I could change the elements' color atomically.

Thank in advance and sorry for my bad english.

I'd suggest adjusting your model and doing away with the trigger altogether.  While the example is somewhat mind-bending, your data model contains a duplicated dependency which serves as the root of your update anomaly problem.

T-shirts ( T ), Jeans ( J ) and Shoes ( S ) can be placed inside a Box ( B ) and they can have a Color ( C ).
Each Element ( E ) of the Box ( B ) MUST have the same Color ( C ) of the other.

Okay, so T → B, T → C, B → C.  That is, if we know the T-shirt, we know the Box, if we know the T-shirt, we know the Color, and if we know the Box, we know the Color.  Since Color is dependent on Box and Box is dependent on T-shirt, while perhaps not particularly intuitive, Color cannot be directly dependent on T-shirt since it is already dependent through a transitive dependency.  That is to say, if we know the T-shirt, we cannot know the Color ( the Color is NULL ), since we must first know the Box.
The existing trigger is actually attempting to compensate for a potential update anomaly a 3NF+ data model would mitigate.  If the color of a T-shirt is known at creation time, such a T-shirt could ( and likely should ) be immediately added to a box of the same color.  In this way, the need to change the color of all items in a box is performed by merely updating the color attribute of the box itself.
Also, unless there's good reasons to split T-shirt, Jeans and Shoes into their own tables, it might be very much preferable to just consolidate those to an Element table with and ElementType lookup attribute, but much like @dezso, I suspect this is not even kind of really about t-shirt, jean and shoe colors as they are arranged in various boxes.


MySQL: List databases with tables Is there a way to list all my databases with tables in a single command?

Expectation:

mysql> SHOW DATABASES, TABLES;

    DB1 table1
        table2
    DB2 table1
        table2
        table3
        table4
        table5
    DB3 table1
        table2

Use the information stored in INFORMATION_SCHEMA:

SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables
ORDER BY table_schema, table_name;

table_schema is the database name.

To show also the databases without any tables:

SELECT s.schema_name, t.table_name
FROM INFORMATION_SCHEMA.schemata AS s
  LEFT JOIN INFORMATION_SCHEMA.tables AS t
    ON t.table_schema = s.schema_name
-- optional, to hide system databases and tables
-- WHERE s.schema_name NOT IN ('information_schema', 'mysql', 'performance_schema')
ORDER BY schema_name, table_name ;

Documentation links: INFORMATION_SCHEMA.tables,  INFORMATION_SCHEMA.schemata


MySQL got signal 11 error and slave is down My slave server is down and mysqld unable to start. What should I do?

Below follows the error logs:

2014-09-26 12:51:53 14166 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
2014-09-26 12:51:53 14166 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2014-09-26 12:51:54 14166 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
2014-09-26 12:51:54 14166 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2014-09-26 12:51:55 14166 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
2014-09-26 12:51:55 14166 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
(...)

The reason for this ? Please note the following diagram of the InnoDB Architecture

From this diagram, you can see an insert buffer in the InnoDB Buffer Pool and a connection to the insert buffer in ibdata1. This thread is known as the Master Thread. In its current design, InnoDB can only have one Master Thread per MySQL Instance.

Apparently, given the messages above, mysqld is trying to start and another mysqld process is already running. You need to go to the OS do the following:

Locate the mysqld_safe process and kill it
Locate the mysqld process and kill it
Run service mysql start
If mysqld complains about the PID file already existing, delete the PID file and try to start mysql again.


SQL Server Fulltext search against big amount of search terms over some period of time I've got a task where I must do a fulltext search in SQL Server 2008 against big amount of search terms (30-60 search terms) combined with OR. Some search terms may have partial match (asterisks at the end). So the fulltext query is something like:

'"term one" OR "term two" OR "term*" OR .... OR "term last"'

The query is pretty straightforward, I just select primary keys (which are integers) into a temporary table (for further processing):

insert into @processed_rules(rule_id, story_id)
select @rule_id, Stories.id
from Stories
inner join CONTAINSTABLE(Stories, (header, body), @formatted_keywords) as s
   on s.[KEY] > @oldest_id AND s.[KEY] = Stories.id

The main problems is a performance. The table contains several millions of records, each containing 2 fields which are subject to search: "header" with content of a size ~600-700 bytes and body with content of a size of several kilobytes.

The search shown above executes in ~1.5 seconds. I need to search against several search term sets in my stored procedure, so the overall time becomes something like ~10 seconds for me, which is unacceptable.

Despite the fact that the table contains records for past several years, I KNOW that I need results only from past 1-2 weeks maximum.

As you can see in the query I am trying to limit the output with some recent date/time range (s.[KEY] > @oldest_id where @oldest_id is a previously calculated record id for some moment in the past), but it doesn't seem to help much. Select TOP N also doesn't affect the execution time. As far as I understand that is because fulltext search first produces results for the whole table no matter what constraints are set in the query and only after that filters it against given constraints.

So I have several questions.

1) Is any way to restrict fulltext search only to recent weeks' records, in order to boost the search performance? I was thinking about partitioning table, indexed views and stuff like that but unfortunately didn't find in the Internet a recipe how to use them for my problem. Any advice in this direction?

2) The search for several search terms works pretty quickly for me (matter of several milliseconds), is it normal that when I give 30-60 search terms combined with OR the performance degrades so much?

P.S. The server has a lot of memory and disk space installed and it's multiprocessor, it has very high performance itself so it's hardly the bottleneck.

Execution plan XML would be useful here but as a shot in the dark:

Daft as it may sound, try pushing either the [id] or date predicate into the CONTAINSTABLE query. See SQL Server 2005 Full-Text Queries on Large Catalogs: Lessons Learned - Consider embedding filter conditions as keywords in the indexed text.
I don't remember where but I recall reading an article or blog post some time ago that flagged big OR full-text queries as problematic. IIRC the suggested hack/workaround was to issue a UNION query instead.

For your example, the UNION for 2) would be along the lines of:

INSERT
    @processed_rules
    (
    rule_id
    , story_id
    )
SELECT
    @rule_id
  , s.[key]
FROM
   CONTAINSTABLE(Stories, (header, body), '"term one"')

UNION

SELECT
    @rule_id
  , s.[key]
FROM
   CONTAINSTABLE(Stories, (header, body), '"term two"')

UNION

  SELECT
    @rule_id
  , s.[key]
FROM
   CONTAINSTABLE(Stories, (header, body), '"term three"')


Error in SQL Server 2012 OUTPUT clause Given the following SQL code against the AdventureWorks Database:

begin transaction;

drop trigger Sales.iduSalesOrderDetail;
alter table Sales.SalesOrderDetail
    add LineNumber int null;
go

with sod as (
    select
        SalesOrderID,
            /* other cols */
        LineNumber,
        NewLineNumber = row_number() over
            (partition by SalesOrderID order by SalesOrderDetailID) + 1
    from Sales.SalesOrderDetail
    where SalesOrderID <= 44000)
update sod
set LineNumber = NewLineNumber,
    ModifiedDate = getdate()
output
    inserted.SalesOrderID,
    /* other cols */
    inserted.LineNumber;

select
    SalesOrderID,
    /* other cols */
    LineNumber
from Sales.SalesOrderDetail
where SalesOrderID <= 44000;

rollback transaction;

When running this code on SQL Server 2008R2, it runs fine; that is to say, it updates the data in Sales.SalesOrderDetail and outputs the results, which match the data from the following SELECT statement.

However, when running this code on SQL Server 2012, I get the message:

  Msg 4196, Level 16, State 1, Line 2
  Column 'deleted.NewLineNumber' cannot be referenced in the OUTPUT clause because the column definition contains an expression using a window function.

This is odd because I am not referencing deleted.NewLineNumber in any way.  I have attempted removing various OUTPUT columns and get the same result.  The only two ways I have found to stop the error have been to

make the NewLineNumber column be exactly the result of the row_number() (that is to say: NewLineNumber = row_number() over (...)); or
remove the OUTPUT clause altogether.

Is this possibly a bug in how SQL Server is processing the expressions in the CTE? Has anyone else run into this?

I'm working on possible workarounds, but I am curious about this.

This is a bug in SQL Server 2012, and will be fixed in SP2:

http://connect.microsoft.com/SQLServer/feedback/details/779077/output-clause-fails-when-doing-update-using-cte

A workaround is to remove the + 1 from the ROW_NUMBER() calculation; this spurious error message goes away. Rewrite this portion:

  NewLineNumber = row_number() over
    (partition by SalesOrderID order by SalesOrderDetailID)
    ---------------- remove the +1 here --------------------^^^
  from Sales.SalesOrderDetail
  where SalesOrderID <= 44000)
update sod
  set LineNumber = NewLineNumber + 1,
  ------- and put it here -------^^^


Using DISTINCT and COUNT in a query I'm having issues using DISTINCT and COUNT in the same query. I would like to get unique items then get a total count for each month. (I've done this in two queries but I know there's a better way.)

Query:

SELECT
  FORMAT_TIMESTAMP('%Y-%m', Sell_Date) AS Sell_Date,
  count(*) Total_Sold
FROM
  `project.dataset.items`
GROUP BY
  Sell_Date
ORDER BY
  Sell_Date

Results (snippet):

Row     Sell_Date   Total_Sold
 1       2010-05        15
 2       2010-06        40
 3       2010-07        75
 4       2010-08        20

This is what I would like, however, this contains duplicate Item_Id entries.

If I replace the SELECT above with this:

SELECT
  DISTINCT Item_Id,
  FORMAT_TIMESTAMP('%Y-%m', Sell_Date) AS Sell_Date,

I get this error:

  Error: SELECT list expression references column Item_Id which is neither grouped nor aggregated at [2:12]

If I replace the GROUP BY with:

GROUP BY
      Sell_Date, Item_Id

Results (snippet):

Row     Item_Id      Sell_Date   Total_Sold
 1       992         2010-05         1
 2       118         2010-05         1
 3       855         2010-05         1
 4       846         2010-05         1
 5       989         2010-05         1
 6       505         2010-05         1
 7       997         2010-05         1
 8       983         2010-05         1
 9       122         2010-05         1
 10      601         2010-05         1
 11      845         2010-05         1

How can I get DISTINCT items then count the total for each month?

count (distinct item_id)

SELECT
  FORMAT_TIMESTAMP('%Y-%m', Sell_Date) AS Sell_Date,
  count(*) Total_Sold,
  count (distinct item_id) as distinct_item_id

FROM
  `project.dataset.items`
GROUP BY
  FORMAT_TIMESTAMP('%Y-%m', Sell_Date) AS Sell_Date
ORDER BY
  Sell_Date


Pgpool install - libpq is not installed or libpq is old Based on the documentation here, I want to create a pgpool II with PostgreSQL databases. When I try to install, I encounter this error:

  configure: error: libpq is not installed or libpq is old

Searching the pgpool website, they mention to run ./configure with this command:

  --with-pgsql

OR

   --with-pgsql-includedir

OR

   --with-pgsql-libdir

But still I'm facing the same problem. Can anyone guide me what can I do from here?

I am running PostgreSQL 9.2 intalled from a yum package on CentOS 5.

Instead of compiling PgPool, why not just install it from package management?

yum install postgresql-pgpool-II

I don't use CentOS, but that's it's package name in Fedora 17, and the two are usually consistent.

Since you're using a PostgreSQL 9.2 installed via Yum on CentOS 5, you're obviously using a 3rd party packaging of PostgreSQL, so you're likely to be using the yum.postgresql.org repository. This repository claims to contain PgPool-II, so try:

yum search pgpool

and see if you find any packages. Get details on them with:

yum info packagename

Otherwise, for compiling PgPool you need the development package for PostgreSQL installed:

yum install postgresql-devel

If that alone doesn't do the trick, try making sure pg_config is on the PATH:

export PATH=$PATH:/usr/pgsql-9.2/bin
./configure

You shouldn't need to specify a libdir or includedir explicitly, as the're in /usr/include and /usr/lib (or /usr/lib64) on CentOS and those are default search locations for configure and gcc. It's most likely that you just don't have the development headers installed.


exception: hosts cannot switch between localhost and hostname I created a replication set.
I added localhost in the set in the beginning, but when I try to edit the member with the actual hostname. I get error "exception: hosts cannot switch between localhost and hostname"

I need to get rid of localhost:27017 because, otherwise, it doesn't let me enter any other member as hostname (i.e. non-localhost address)

my-rs0:PRIMARY> cfg=rs.conf();
{
    "_id" : "my-rs0",
    "version" : 1,
    "members" : [
        {
            "_id" : 0,
            "host" : "localhost:27017"
        }
    ]
}
my-rs0:PRIMARY> cfg.members[0].host="my-server04:27017"

my-rs0:PRIMARY> cfg
{
    "_id" : "my-rs0",
    "version" : 1,
    "members" : [
        {
            "_id" : 0,
            "host" : "my-server04:27017"
        }
    ]
}

using rs.reconfig(cfg);

my-rs0:PRIMARY> rs.reconfig(cfg);
        {
            "errmsg" : "exception: hosts cannot switch between localhost and hostname",
            "code" : 13645,
            "ok" : 0
        }

no luck with rs.add("my-server04:27017") or rs.remove("localhost:27017") as well.

 my-rs0:PRIMARY> rs.add("my-server04:27017");
    {
        "errmsg" : "exception: can't use localhost in repl set member names except when using it for all members",
        "code" : 13393,
        "ok" : 0
    }

I have tried all the reconfiguration methods mentioned here Replica Set Reconfig steps
But, none fixing above issue. Already spent hours, I am really frustrated.

You need to basically reset the config, which is stored in the local database, and then start from scratch.  Assuming you still just have the one server configured as above, restart the instance without the --replSet option, then drop the local.system.replset collection.  Once that is done you may restart again with --replSet and you can re-run rs.initiate() but this time pass in the configuration document instead of letting it auto-discover, something like this:

rs.initiate({"_id" : "my-rs0", "version" : 1, "members" : [{"_id" : 0, "host" : "my-server04:27017"}]})

You can also solve this by making sure that the name resolves correctly and so is automatically populated by the default rs.initiate() command, but the above removes any doubts.


Vacuuming database termination for postgreSQL I was doing database vacuuming to free up disk space for postgreSQL database. The corresponding table size was large and it was taking long time for vacuuming to get complete. But unfortunately because of slow network connection , the network connection got terminated which resulted vacuuming getting terminated in middle.

I am worried whether any database corruption will occur. Can I once again issue database vacuum command?

vacuum full message_log;

Please clarify.

I am worried whether any database corruption will occur

No database corruption will occur due to an interrupted VACUUM, whether it was whole-database or just one table, and whether it was a an ordinary VACUUM or a VACUUM FULL. VACUUM is completely safe to abort at any time. At worst it'll leave some extra table and index bloat that a later VACUUM run (that completes) will clean up.

In fact, it's also crash-safe so long as the underlying storage subsystem respects fsync(), which is a basic requirement for PostgreSQL reliability. It wouldn't even matter if PostgreSQL crashed, or if the machine was reset suddenly. In that case PostgreSQL would just recover from transaction logs.


Can SSRS used to Update or Modify data tables? We all know SSRS (SQL Server Reporting Services) is to read data.

However, can SSRS be utilized to update rows or insert rows, and should it?

I am seeing resources here, where you can take user input parameters and update rows.
Just inquiring if it is advisable/allowed to use that tool in such a way. Does Microsoft have anything against doing that?

Resources stating this:

madeiradata
sqlservergeeks
experts-exchange

If Madeira Data shows you how to do it, then I think it's safe to say:

It's possible.

It can be made to work, as evidenced by this paragraph from them:

It’s especially good when your organization doesn’t have skilled programmers that can create a pretty-looking graphical user interface (GUI). Or when there are budget and / or time restrictions.

Deciding if this kind of setup will work for you is entirely your decision.


How Can I Read Where TempDB's Files Will Be Created After Restoring Master DB? I'm restoring to an alternate server in a DR drill and have restored master but can't restart the instance, even with /f and /m switches. The log is indicating tempdb can't be created, probably looking for the paths from the source instance. How can I determine which path it's trying to create tempdb's DB files in?

I was able to restore the master backup under an alternate name on a different instance so I can browse the system tables. Is there a spot where I can look for them?

I have to go with the assumption that access to the source server is lost so I can only rely on the backups to determine the correct configuration.

Refer to Instance will not start

There is a section called: TempDB location does not exist, you should be able to see the location in the SQL server log


Why provide database name when starting postgresql base backup According to the postgresql 8 manual, before starting a base backup (i.e. copying the files out of the data directory), you need to issue a command like this:

psql -d DATABASENAME -c "SELECT pg_start_backup('label');

But, since the postgresql files in the data directory contain ALL databases held by postgresql, what is the point of specifying DATABASENAME?  (eg: mydb).  Is it equally effecting to just use template0 as the databasename?

Because you must connect to a specific DB in all cases. In psql if you don't specify a database it tries to connect, at least in *nix (any Unix like OS), to a database with the same name as the user in the operating system. Not sure Windows, but I would think it probably behaves the same.

The authentication mechanism doesn't know, or care, what operation you are about to do. It just needs to verify:

Does the user exist?
Did we get proper password for the user (unless you are setup with trust connections)?
Is the user trying to connect to a database that exists and it has rights to connect?


Is there a limit to the number of objects that can be dropped and recreated (frequently) in SQL Server? The datatype for field [object_id] in table sys.objects is "int"
So for all the objects in a database this is unique and has a limit of 2,147,483,647 (2 billion'ish)
What happens when there is some process (connecting to this database) that drops and (re)creates a large number of (user defined) tables every hour every day. Does it get to a point, where there can be no more objects created in the database because it ran out of assigning unique object_ids? I read somewhere that sql server does not reuse an object_id that was assigned earlier, if thats the case, it can run out pretty quick, in this situation, right?
Please guide me to some links/articles if this was just a silly question.
Thanks

Looks like the object_ids increase rapidly and roll over frequently.  EG
declare @i int = 0
while @i < 10000
begin
  drop table if exists t
  create table t(id int)

  set @i += 1
  if @i % 100 = 0
  begin
    declare @objectid int = object_id('t')
    RAISERROR ('object_id %i', 0 , 1, @objectid) WITH NOWAIT
  end
end

outputs
object_id 834515239
object_id 287037291
object_id 1887042991
object_id 1339565043
object_id 792087095
object_id 244609147
object_id 1844614847
object_id 1297136899
object_id 749658951
object_id 202181003
object_id 1802186703
object_id 1254708755
object_id 707230807
object_id 159752859
object_id 1759758559
object_id 1212280611
object_id 664802663
object_id 117324715
object_id 1717330415
object_id 1169852467


How many connections reads queue Is there a way to get to know how many external tasks (console applications) is reading queue?

I am using Service Broker external activator.

What i want is something similar to information i get from sys.dm_broker_activated_tasks, just for external queue readers.

Sort of. You can see though sys.dm_exec_requests executing a RECEIVE statement (including a WAITFOR RECEIVE). By simply peeking into the the currently executing text sys.dm_exec_sql_text(sql_handle) between the statement_start_offset and statement_end_offset you can see if the statement is RECEIVE or not (with some parsing...). Figuring out if an active transaction has issued a RECEIVE and is now processing returned messages is more complicated.

Is somehow of an unusual request, why do you need to know?


What does [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'WIN-443PT0LULQU\Administrator', error code 0x534 I'm going slightly nuts.  I'm a programmer by trade - and I only dabble in NT security and DB setup issues.

However, we had a server fail to reboot after a MS Update.  I've restored the entire machine from Windows Backup - but I've realized that I cannot rebuild the machine because one cannot realistically use a Windows Backup to restore a database to a different machine, AFAICT.  Rather, I need actual DB backups to do that.

In pursuit of having regular SQL Server backups, I'm running into the title error:

[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'WIN-443PT0LULQU\Administrator', error code 0x534

Essentially, I've enabled SQL Server Agent viz SSMS, then I created a management plan to do full backups on our SQL server instance, and scheduled it for daily operations.

However, when I manually ask it to execute that job - to ensure all is working correctly - I get the error:

[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'WIN-443PT0LULQU\Administrator', error code 0x534

That sounds to me like an NT permissions issue - that whatever it is - XP Agent? - attempts to retrieve the permissions of the aforementioned user - and that fails.

Part of what is bizarre here - is there is not machine 'WIN-443PT0LULQU' - that doesn't exist - WTH is SQL Server forcing that in there for in the first place?!

I want this to run as the Administrator - on the local machine 'BUILD-SERVER' - not that garbage name it keeps forcing...

I'm way out of my depth here and this whole process is crazy-making.

How in 2018 is it not possibly to click a few wizard NEXT style buttons and configure Windows / SQL Server to back its freakin' self up regularly correctly?

Okay - after struggling with this for a couple of days (I had tried posting variations on this questions to stackoverflow and to superuser), it seems to be that the SQL Instance had the wrong machine name stored in master - so I had to change that to the correct current machine, and ensure that the correct admin account was enabled as an sa account.

This mostly got me going:
http://www.sqlservercentral.com/articles/SQLServer/135372/

I had to do run these to figure out that the instance had the wrong servername, then to drop the bad one, then to add the correct one:

use master
select SERVERPROPERTY('servername')
select @@SERVERNAME
select SERVERPROPERTY('machinename')
go

use master
exec sp_dropserver "WIN-443PT0LULQU\SOURCEGEARVAULT"
go

use master
exec sp_addserver "BUILD-SERVER","local"
go

I also had to mess around with logins - but I cannot say for sure what I did exactly that ended up working.  I renamed "WIN-443PT0LULQU\Administrator" to "BUILD-SERVER\Administrator" at one point, and after doing the above, I had to restart the instance.

After that, things seem to be working properly, and I am able to execute my maintenance plan successfully.


Is there a way to get count based on multiple columns but still have it be distinct in SQL? I have included two pictures that are in Excel just for ease of viewing but I am doing the queries in SQL.

Hopefully, this is possible as the algorithm I have is not giving me the correct results.

Not sure I understand the problem, but if you first pick distinct rows, and then count number of rows per date

select dt, count(1)
from (
    select distinct dt, job, title, name
    from t
) as x
group by dt

I used dt instead of date since it is a reserved word


Copy large object field into remote PostgreSQL DB We have configured postgres_fdw to move data from a DB to remote DB. It works fine but the large object field's data in source db not getting copied to remote DB.

This is the statement used to copy:

INSERT INTO archive_remote.target_db(id, oid_field)
SELECT a.id, a.oid_field
FROM appl.source_db a
ON CONFLICT DO NOTHING;

Both databases are Postgres 9.6.

What is the right way to fix this?

I assume that the oid in question is the Oid of a large object, and you are wondering why the large object isn't copied when the oid field is copied.

Large objects cannot be copied via postgres_fdw. You'd have to use the large object API (or pg_dump) to move them from one database to the other. If the Oids are already taken on the second database, you'll have to use different ones and update the table accordingly.

Large objects are cumbersome to handle.


Convert raw or hex value to signed 2's complement via Oracle SQL Is there a way to convert either HEX or RAW value to signed 2's complement via Oracle SQL?
example:
hex: 8508 > dec: 34046 > dec from signed 2's complement: -31480
The number that I am trying to get within my SQL statement is -31480.

Using the expression
result = -(x & mask) + (x & !mask)

where mask=2^(n-1) yields the result you want.  The (x & mask) isolates the sign bit and (x & !mask) is the offset.  When the sign bit is not set, (x & mask) == 0 and (x & !mask) is the result.  When the sign bit is set, -(x & mask) == -2^(n-1) and (x & !mask) is the offset from the most negative number.
Next we convert the hexadecimal string to a raw representation by using HEXTORAW().  The raw representation is cast to an integer with UTL_RAW.CAST_TO_BINARY_INTEGER().
Assuming 16 bit:

select
  x,
  -BITAND(utl_raw.cast_to_binary_integer(hextoraw(x)),32768)
    +BITAND(utl_raw.cast_to_binary_integer(hextoraw(x)),32767)
  from (select '8508' x from dual);

DB Fiddle example
Note:  You probably want to do the utl_raw.cast_to_binary_integer(hextoraw(...)) once instead of twice.


Optimal table design for prizes that can be int or str I'm storing data about prizes won by competitors during rounds of various events.  Although most prizes are cash, non-cash prizes are awarded too.  A common query will be summing a participant's lifetime cash winnings across events.

A single-table solution with a prize_type attribute doesn't allow me to store cash prizes as INT, so I can't easily sum them.  Using separate tables feels bad.

CREATE TABLE event_prizes_cash (
  event_id INTEGER,
  event_round INTEGER,
  person_id INTEGER,
  place INTEGER,
  amount INTEGER,
  notes TEXT )

CREATE TABLE event_prizes_other (
  event_id INTEGER,
  event_round INTEGER,
  person_id INTEGER,
  place INTEGER,
  prize TEXT,
  notes TEXT )

What's the optimal way to store this data?

Make a prize_money column with the proper currency/float/decimal type and a prize_other column that is text/varchar. Depending on the type of prize, use one or the other.


Why am I getting "Snapshot isolation transaction aborted due to update conflict"? We have two tables

Parent (Id int identity, Date datetime, Name nvarchar)
Child  (Id int identity, ParentId int, Date datetime, Name nvarchar)

The Child having a foreign key relationship to the Parent.

We have enabled database level read committed snapshot isolation.

We only ever insert and delete rows for Parent and Child (no updates)

We have one process (transaction) which deletes old data from Child (and then Parent)

We have multiple other processes (transactions) which insert new data into Parent (and then Child)

The delete process regularly (but not all of the time) gets rolled back, even though the insert process does not insert new Child rows which refer to the Parent rows which the delete wants to delete - it simply creates new Parent rows and one ore more new Child rows which refer to the new Parent

The error when deleting the Parent rows is:

  Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.Child' directly or
  indirectly in database 'Test' to update, delete, or insert the row
  that has been modified or deleted by another transaction. Retry the
  transaction or change the isolation level for the update/delete
  statement.

I am aware the people suggest having an index on the foreign key column - we'd prefer not to have to do this ideally (for space/performance reasons) - unless this is the only reliably way to get this to work.

Noted this: https://stackoverflow.com/questions/10718668/snapshot-isolation-transaction-aborted-due-to-update-conflict

And pretty good article: https://sqlperformance.com/2014/06/sql-performance/the-snapshot-isolation-level

But neither of these gives me the understanding I would like to have :)

When deleting from the parent table, SQL Server must check for the existence of any FK child rows that refer to that row. When there is no suitable child index, this check performs a full scan of the child table:

If the scan encounters a row that has been modified since the delete command's snapshot transaction started, it will fail with an update conflict (by definition). A full scan will obviously touch every row in the table.

With a suitable index, SQL Server can locate and test just the rows in the child table that could match the to-be-deleted parent. When these particular rows have not been modified, no update conflict occurs:

Note that foreign key checks under row versioning isolation levels take shared locks (for correctness) as well as detecting update conflicts. For example, the internal hints on the child table accesses above are:

PhyOp_Range TBL: [dbo].[Child]
    Hints( READ-COMMITTEDLOCK FORCEDINDEX DETECT-SNAPSHOT-CONFLICT )

Sadly this is not currently exposed in execution plans.

Related articles of mine:

The SNAPSHOT Isolation Level
Data Modifications under Read Committed Snapshot Isolation


help in multi-language medical database? My project is to design a medical database for many countries.

This database contains many parts: medicines, medical centers, patients, etc. for every country with its own language.

I started my design with medicine part but I'm having many issues continuing:

How can I put all these complex parts in one database with different languages? I can't imagine any suitable design for that complex idea.
How can I deal with the database growth from data coming fast for each part from each country?
I read about primary files and secondary files and how they increase performance of a database. Should I made them for my database? Or should I use the default files?
Should I separate every country with its own parts in a separated database?

I uploaded 2 pics for the initial design for medicines part in English language only.

NOTE: the 2 pics are only one but it's too large so I divided it into 2 halves

I use SQL Server 2008.

How can i put all these complexed parts in one DB with different languages??

Define the requirements

I can give you an example of how to model multilingual name data by assuming these business requirements:

The database should store the names of the ingredients of drugs in English, French and Spanish, and more languages yet to be defined.
Each ingredient is known by one name in each langauge.

Identify the languages

You need a way to identify langauges in your database so that you can associate drug names with a particular langauge.

A simple standard for identifying languages is ISO 619-1, which defines a two-letter code for the world's most common languages. For example, the code for English is en, French is fr, and Spanish is es.

The following SQL creates a table with a language code column, then populates the table with the example languages:

CREATE TABLE Languages (
  LanguageCode CHAR(2) NOT NULL,
  CONSTRAINT PK_Languages PRIMARY KEY (LanguageCode),
  CONSTRAINT CK_LowerAlpha CHECK (LanguageCode NOT LIKE '%[^a-z]%' COLLATE Latin1_General_BIN2)
);

INSERT INTO Languages (
  LanguageCode
)
VALUES
  ('en'),
  ('fr'),
  ('es');

There is nothing in your entity model that corresponds to this table, which could explain why you found it difficult to model multiligual data.

Identify the chemicals

Next you need a way to identify the chemicals that compose each drug.

A common way to identify chemicals is CAS registry numbering, which defines a numerical code for every chemical known to Science. For example, the code for water is 7732-18-5, formaldehyde is 50-00-0, and mustard oil is 8007-40-7.

The following SQL creates a table with a chemical code column, then populates the table with the example chemicals:

CREATE TABLE Chemicals (
  ChemicalCode VARCHAR(12) NOT NULL,
  CONSTRAINT PK_Chemicals PRIMARY KEY (ChemicalCode),
  CONSTRAINT CK_Dashes CHECK (ChemicalCode LIKE '%_-__-_'),
  CONSTRAINT CK_Digits CHECK (REPLACE(ChemicalCode, '-', '') NOT LIKE '%[^0-9]%')
);

INSERT INTO Chemicals (
  ChemicalCode
)
VALUES
  ('7732-18-5'),
  ('50-00-0'),
  ('8007-40-7');

The Chemicals table roughly corresponds to the Drugs_Ingredients entity in your diagram, but lacks a column for the name.

For simplicity, the table does not enforce the checksum constraint. You should enforce the checksum constraint before using CAS registry numbers in production.

Identify the chemical name

If you were to store the name in a column in the Chemicals table, it would be difficult to support more than one language. You could have one column per langauge, but you would have to modify the schema every time you wanted to support a new langauge.

To free yourself from these limitations, you should model the chemical name as an identifiable entity in itself. You can identify the chemical name using a compound identity of the chemical code and the language code.

The following SQL creates a table with columns for the chemical code, the language code, and the checmical name, then populates the table with one name in each language for each chemical:

CREATE TABLE ChemicalNames (
  ChemicalCode VARCHAR(12) NOT NULL,
  LanguageCode CHAR(2) NOT NULL,
  ChemicalName NVARCHAR(50) NOT NULL,
  CONSTRAINT PK_ChemicalNames PRIMARY KEY (ChemicalCode, LanguageCode),
  CONSTRAINT FK_ChemicalNames_ChemicalCode FOREIGN KEY (ChemicalCode) REFERENCES Chemicals(ChemicalCode),
  CONSTRAINT FK_ChemicalNames_LanguageCode FOREIGN KEY (LanguageCode) REFERENCES Languages(LanguageCode)
);

INSERT INTO ChemicalNames (
  ChemicalCode,
  LanguageCode,
  ChemicalName
)
VALUES
  ('7732-18-5', 'en', 'Water'),
  ('7732-18-5', 'fr', 'Eau'),
  ('7732-18-5', 'es', 'Agua'),
  ('50-00-0', 'en', 'Formaldehyde'),
  ('50-00-0', 'fr', 'Méthanal'),
  ('50-00-0', 'es', 'Formaldehído'),
  ('8007-40-7', 'en', 'Mustard oil'),
  ('8007-40-7', 'fr', 'Huile de moutarde'),
  ('8007-40-7', 'es', 'Aceite de mostaza');

The ChemicalName column is of type NVARCHAR so that it can store any Unicode string. You should always use Unicode in international applications to store text so that you can handle the writing systems of different langauges in a uniform way.

Query the chemical names

I can think of two questions that you might reasonably ask of your database. With the ChemicalNames table, you can answer both of them with simple queries.

What are the names of all the chemicals in Spanish?

Query:

SELECT ChemicalCode, ChemicalName
FROM ChemicalNames
WHERE LanguageCode = 'es';

Result:

CHEMICALCODE    CHEMICALNAME
50-00-0 Formaldehído
7732-18-5   Agua
8007-40-7   Aceite de mostaza

What is water called in each langauge?

Query:

SELECT LanguageCode, ChemicalName
FROM ChemicalNames
WHERE ChemicalCode = '7732-18-5';

Result:

LANGUAGECODE    CHEMICALNAME
en  Water
es  Agua
fr  Eau


Archive old data in a large table in DB2 I have a very large non-partitioned table with ~4,500,000,000 records: ODS.LARGE_TABLE.

The table has stored 3 months of data with ~50,000,000 rows per day, therefore resulting total of ~4,500,000,000 records.

My objective is to archive the records in the oldest month (i.e. Sep) to another new table ODS.LARGE_TABLE_old.

1.) My first approach is to load the old records to ODS.LARGE_TABLE_old, then delete them in ODS.LARGE_TABLE. The loading step is fine but the delete step is problematic,
since deleting such many records in this large table will cause transaction log full error.

I knew that one can alter table to not logged initially to avoid this problem, but it seems to be risky in my situation since any interruption of the delete operation will make the table inaccessible and I dont have any backup copy of this large table.

2.) My second approach may sound stupid but it does not involve of delete operation. I first rename ODS.LARGE_TABLE to ODS.LARGE_TABLE_tmp,
then create 2 new tables: ODS.LARGE_TABLE and ODS_LARGE_TABLE_old, I load the old records to ODS.LARGE_TABLE_old from ODS.LARGE_TABLE_tmp; load the rest of the records (i.e. Oct & Nov) to ODS.LARGE_TABLE from ODS.LARGE_TABLE_tmp, finally, I drop the table ODS.LARGE_TABLE_tmp.

I think the drawback with this approach is that the 2nd load operation is taking me so much time, since it involves of loading data with 2 months.

My question is that are there any best practices/ways to archive old records from a large table in DB2 LUW? I am finding an efficient way to do so such that I can apply the same approach in all of my large tables.

Thanks in advance.

You may try ADMIN_MOVE_TABLE routine to convert your table to a range partitioning one online. Use COPY_USE_LOAD option to speed up the process and avoid excessive logging.


ORA-39165 Schema was not found in export I'm getting the following error on a data pump export:

ORA-39165: Schema OFDAT was not found.

First I verified that I had full export permission:

GRANT EXPORT FULL DATABASE TO OFDAT;

Then I tried the admin option in case that would do it:

GRANT EXPORT FULL DATABASE TO OFDAT WITH ADMIN OPTION;

I looked at the error more closely in oerr:

[oracle@OptTekOracleDB dpdump]$ oerr ora 39165
39165, 00000, "Schema %s was not found."
// *Cause:  If exporting or importing over the network, either the user
//          specified a schema name that was not found in the source database
//          or else the user lacked the proper EXP_FULL_DATABASE or
//          IMP_FULL_DATABASE role that would allow them to access another
//          schema.  For importing from files, the user specified a schema
//          name not found in the dump file set.
// *Action: Retry the operation using the correct schema name.

Aha! I thought.  Even though I have the grant, this is saying I need the role.  So I added the role to the user:

GRANT EXP_FULL_DATABASE TO OFDAT;

I am still getting the same error, schema was not found. Can't think of anything else to try.

@phil - here is my expdp parameter file:

DUMPFILE="dev_ofdat_2-1-3-0_afterbackfill.dmp"
LOGFILE="exp_dev_ofdat_2-1-3-0_afterbackfill.log"
DIRECTORY=DATA_PUMP_DIR
COMPRESSION=METADATA_ONLY
CONTENT=ALL
SCHEMAS=('OFDAT')

Very simple.  The command line to kick it off is:

expdp ofdat PARFILE=exp_dev_ofdat_2-1-3-0_afterbackfill.dat

When I run into issues like this, I like to simplify the command as much as possible.  I would try putting all of the parameters directly in the command line and removing the parameters that are not required.  And I would use a dba user to do it.  Something like this.

expdp {dbausername}/{dbapassword} SCHEMAS=OFDAT DUMPFILE=dev_ofdat_2-1-3-0_afterbackfill.dmp


How to create table with default DATE column using { ts }? Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

create table my_table (
    transaction_date DATE DEFAULT { ts '1900-01-01 00:00:00' } NULL
)

error --

ORA-00911: invalid character
00911. 00000 -  "invalid character"
*Cause:    identifiers may not start with any ASCII character other than
           letters and numbers.  $#_ are also allowed after the first
           character.  Identifiers enclosed by doublequotes may contain
           any character other than a doublequote.  Alternative quotes
           (q'#...#') cannot use spaces, tabs, or carriage returns as
           delimiters.  For all other contexts, consult the SQL Language
           Reference Manual.

As the above create statement is generated by a third-party software, what's the correct syntax without using date or to_date()?

Whatever syntax that is, that is not correct in Oracle.

Use this:

create table my_table (
    transaction_date DATE DEFAULT timestamp'1900-01-01 00:00:00' NULL
);


How to determine which SQL Server user or database overuses server resources I have a Microsoft SQL Server instance on my server with more than 100 databases and users. Sometimes, the SQL Server process use 100% of server CPU and I want to know which query/user/database is causing this and using so much resources.

For MySQL, I use the mysqladmin pr command to see which users are currently connected. Also, the slow query log shows the queries that take more than X seconds to be completed.

I would like to know if there are similar commands for SQL Server.

There's a ton of dynamic management views (DMVs) you can use to get the data, but the easiest way is to use Adam Machanic's sp_WhoIsActive stored proc.  Here's a video on how to use it:

http://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/

And you can download it from here:

http://whoisactive.com/

The output includes columns for the database name, CPU cycles used, query duration, and more.


Why is Oracle's number datatype so HUGE? and why do I seem to have no alternatives? Oracle has a favorite numerical class called NUMBER.  It is a 22 byte value that just seems ludicrous in an index.  I have some values that would easily fit in a 6 byte integer and would therefore fit in a nice native datatype that is 64 bits long.  However oracle keeps making my numbers 22 bytes long.

The only place I can see this is if I put the value in an index, and there is shows me the value is a 22 byte number.

I understand it is nice to have numbers that don't have rounding errors, I can appreciate that, but I just want an integer that can be used as a ForeignKey to another table, and rounding won't be an issue.

Mysql, Postgres, MSSQL all have variants that let me specify a datasize (via some mechanism).  Oracle wants to map almost every other numeric type back to NUMBER(size, precision), but no matter what values I use for size or precision, oracle insists the number takes up 22 bytes in my index.  That is nearly 3x longer than I need and effectively cuts the M of my btree.

Oracle does have BINARY_FLOAT and BINARY_DOUBLE, it even has a ROWID which is nothing like an integer at all.

Is there some secret sauce that Oracle has that makes NUMBER uber efficient?  Are there secret docs somewhere that show datatypes that map to native datatypes?

See here ixora.

Number has variable length, like string.
each digit occupies one nibble (4bits, half byte)
NUMBER implementation is platform independent
you can validate size of number value in a column by calling dump() function

PS: Oracle also supports native intergers like PLS_INTEGER, but these can only be used in PL/SQL context only.


Do I need an Availability Group Listener if I have "Failover Partner" in my connection string? I am a developer trying to work with a new Sql Server 2016 Always On cluster.

In reading documentation I am getting conflicting information.

Some documentation tells me that I just need to add "failover partner=MySecondaryServer" to my connection string.
Other sites talk about using an "Listener".  And in my Always On section in SSMS 2016 there is an Availability Group Listeners section.

What is the difference between these options?

Are these competing options (meaning you pick just one) or do they work together?

Notes: I have tried the "failover partner" and got limited success.  When the primary rebooted, I had to restart my Entity Framework service before it "failed over" to the secondary server.  I am thinking about setting up a Listener to see if it will work more dynamically.

Failover partner is for when you are using database mirroring, you should not use it for Availability Groups. While it, strictly speaking, will work, it will only do so when the primary server is offline, and is not designed for this kind of scenario.

Using a listener within an Availability Group is the way to go. This is a virtual network resource that will point you to the correct primary (and is required in the event that you want to use read-routing at any point in the future). Using a listener you should not have to perform an app restart to have client redirection, it should just work.


Is there a way to implement a cross-database task on SQL Server 2012 with the Availability Groups feature? We use SQL Server 2012 and its new Availability Groups (AG) feature. There is a task for moving old data of some tables from one database to another database. Both databases are included into different availability groups.

Previously (before using the AG feature) the task was resolved by adding the second server instance as a linked server (sp_addlinkedserver) and executing a distributed transaction in the following way:

begin transaction
insert old data into server2.table2 from server1.table1
delete old data from server1.table1
commit transaction

Unfortunately, distributed transactions are not supported for AG because databases may become inconsistent in case of failover (http://technet.microsoft.com/en-us/library/ms366279.aspx).

Is there some way to implement this task with keeping the AG feature and without implementing the rollback logic in case of exceptions?

First, you can technically do it with DTC or linked server transactions - it's just not supported, and it's a bad idea for performance anyway. Let's talk about a better method.

The way the question is written, it sounds like you're doing archival - moving data from the "current" tables on Server1.Table1 to a set of "archive" tables on Server2.Table2. In that case, it's okay if the data is in both places for a short period of time, so I'd break it up into two separate tasks:

Phase 1: The Copy - Insert old data into server2.table2 from server1.table1. Assuming this is an archival process, you could have this as a scheduled job that runs nightly at midnight (or whatever day/times work for you.)

Phase 2: The Delete - Reading from the read-only replica of server2.table2, delete data that still exists in server1.table1. Do this as a scheduled job that runs X minutes after phase 1, every X minutes, for an hour (or however often you want to retry.) This way, if the read-only replica is running several minutes behind, your delete will still happen eventually.

It's important to read from the read-only replica rather than server2 directly - after all, your goal is to make sure the data is actually protected. (This is why the cross-database stuff isn't supported.)

To design this for performance, I wouldn't actually join between server2.table2 and server1.table1, either - that's a recipe for a blocking mess. Instead, grab the max ID in server2.table2, and that will tell you the data to delete on server1. If record ID# 500 has made it to server2.table2, then you can delete all IDs <= 500 on server1.table1. You can use the same trick with dates. Bonus points if you use the fast ordered delete technique.

If you're archiving individual records rather than going by an ID or date field, you'll still have to join directly between the tables - just don't join on server2 directly, but instead its read-only replica.

Yes, this is more work than a single delete statement wrapped in a transaction, but as you start to push performance and availability boundaries (like scaling data across multiple servers with AlwaysOn AGs), you have to do more of this stuff yourself rather than hitting the easy button, heh.


SQL Server Agent initiating self-termination For the past month, one of my servers is facing issues with SQL Server Agent. It stops automatically with below error, which happens randomly:

  Sql Agent Output file
  [097] Memory leak detected [204 bytes]
  Exception 5 caught at line 403 of file e:\sql9_sp2_t\sql\komodo\src\core\sqlagent\src\alerter.cpp. SQLServerAgent initiating self-termination

I googled this error but did not get much info about it. Both SQL Server and Agent are running with the same domain account; however, the issue is only with the SQL Server Agent service.

I am also unable to see the SQL Server Agent service in SQL Server Configuration Manager. I checked the SQL Server error log, and Windows Event Viewer logs, but did not see any suspicious activity.

My memory settings are configured correctly. The server has 32GB RAM and SQL Server is assigned to use max memory of 26GB.

System details:

SQL Server 2012 SP2 Std Edition 11.0.5058 64 bit
Windows Server 2008 R2 Ent Edition 64 bit
Dedicated to SQL Server (no other apps run on the server)
The server was upgraded a few months ago from SQL Server 2005 EE to 2012 SE.
We are not using an Events Forwarding Server.

I checked the msdb database as well. Ran checkdb on it and it completed successfully without any errors. I need your help in fixing two things:

To fix the random stopping of SQL Server Agent service
To add SQL Server Agent service in SQL Server Configuration Manager

We have third-party software for monitoring, so we get notified immediately if the agent service stops. For now we are just restarting the service. But we want to find the root cause.

I ran the following query suggested by Sean Gallardy and got 95 rows back. I don't know how to interpret the result.

select *
from sys.dm_os_loaded_modules
where name not like '%microsoft%'

Unfortunately I cannot post the results for security reasons.

Until I find the solution to this problem I have added retry attempts to the SQL Server Agent so that it can restart automatically when it gets stopped.

Since you recently upgraded a few months ago from SQL Server 2005 EE to 2012 SE, you should save off and clear your event logs because SqlAgent may crash when it sees Event Log IDs from an older build of SQL Server. For example, the Agent may crash because it reads an event logged by a SQL Server build earlier than the current build, which only contains data with ONE insertion string and the agent will try to format it using the latest resource information that requires TWO strings.  This would lead to a brief memory leak just before it shuts down since a buffer is allocated for the string that it is trying to print into when the crash occurs.

As far as the agent service not listed in configuration manager, there are multiple possibilities:
https://support.microsoft.com/en-us/kb/941823

Ensure you are running 2012 Configuration Manager

You can try rebuilding the WMI Repository:
https://blogs.technet.microsoft.com/askperf/2009/04/13/wmi-rebuilding-the-wmi-repository/


How do loop through a dynamic SQL statement using a cursor? I have the code below. When i run it i get the same value for all the rows fetched by the cursor. I want it to display the result fetched by the dynamic SQL and if the value returned is null i want the code to display 'N'. Please help.

ALTER PROCEDURE [dbo].[DIM_MASTER] AS

DECLARE

@V_DIM VARCHAR(80),
@V_STMT nVARCHAR(max),
@result varchar(10),
@res varchar(10),
@CURSOR_DIM cursor

set @CURSOR_DIM = CURSOR
 FOR
 SELECT FieldName FROM desc_user.dim_master

open @CURSOR_DIM

fetch next from @CURSOR_DIM into @V_DIM

WHILE @@FETCH_STATUS = 0
BEGIN

set @V_STMT =
'select  @res = '+'''Y''' +'
        where exists(select * from [desc_user].[ticket_master]
        where @v_dim is not null)'

 EXECUTE sp_executesql @V_STMT, N'@v_dim varchar(80),@res varchar(10)         OUTPUT',@v_dim = @v_dim,@res = @resULT OUTPUT
print @v_stmt

select @res as result

UPDATE desc_user.Dimension_Details set FieldValue = @res
where FieldName = @V_DIM

FETCH NEXT FROM @CURSOR_DIM
                INTO @V_DIM
                END

                CLOSE @CURSOR_DIM
                DEALLOCATE @CURSOR_DIM

The problem is that your dynamic query never produces a null. When the condition in the dynamic query's WHERE clause is met, the output parameter is assigned a Y. But when the condition is not met, nothing happens, and the @res parameter keeps the value assigned at the sp_executesql line – consequently, the @result variable keeps its previous value too.

In order to produce a null, you need to change the structure of the dynamic query. Instead of this:

SELECT @var = … WHERE …

make it this:

SET @var = (SELECT … WHERE …)

That way, when the condition is not met, the SELECT will return nothing, which will be interpreted as a scalar null and assigned to the @var accordingly. You can then turn your null into N or anything else as necessary. You can, of course, do that immediately in the dynamic query if that is the only way you want to treat those nulls:

SET @res = ISNULL((SELECT 'Y' WHERE …), 'N');


Update field with new string made from characters in existing value field is meta_value and current value is like 01/01/2006

i want to change it to a string 20060101 so thought i could get the value and update it with the characters in the correct order.

ie

UPDATE wp_postmeta SET meta_value = meta_value[9]meta_value[8]meta_value[7]meta_value[6]

Assuming that meta_value is always dd/mm/yyyy and you are looking for yyyymmdd (which isn't clear), try this:

UPDATE wp_postmeta
SET meta_value = CONCAT(SUBSTRING(meta_value FROM 7),
     SUBSTRING(meta_value, 4, 2),
     SUBSTRING(meta_value, 1, 2));

Run that as a SELECT first to verifying the output before update and make sure it's what you're really looking for.


get row count of last select query I am using a scheduled script which exports the (new) rows of the PostgreSQL database into a textfile. There are also several instances of this script running for several DBs:

COPY (SELECT ... FROM ... GROUP BY zzz) TO STDOUT >file.csv

In order to have a consistency check of the file.csv, I then run a query to get the number of lines which the COPY query had:

SELECT count(0) FROM ... GROUP BY zzz >linecount

In the bash script there is a check if both are equal, if that's the case the script proceeds, if not it stops and reports an error (which happens rarely, but it does - and i haven't figured out why yet)

The problem, the query result is rather large (takes around 5-10min) and it is basically running twice (although the count(0) takes shorter, but still 3-7min)

Is there a PostgreSQL function to get the number of rows the last query had? If that would be the case, I could cut down DB load quite a load and shorten the export time.

I have looked into ROW_COUNT, but it only seems to be valid for UPDATE/INSERT and not SELECT, I'd also like to avoid using functions for simplicity reasons.

I am open to alternative suggestions, eg first query into a temp table, then export from there (maybe count(0) is faster then)?

Debian 7 Wheezy, SSD, 32GB Ram, PostgreSQL 9.1

UPDATE @Craig Ringer

I agree that the consistency check is somewhat 'flawed' by design. I noticed once that the import-check had a problem i couldnt explain, I then ran the export again, and the file had different lines than the one before:

Rowcount DB/LinecountF:532395/532014

rowcount: SELECT count(0)... as described originally
linecount  = wc -l

They are running as cronjobs and I havent figured out why those lines were missing. Running the same script second time and everything was fine. It happens very rarely, but the logfile didnt say anything (or mail result from cron).

I didnt check the exit status of the PSQL tough (as I built in my own checks). I will do that.

If you're willing to forgo writing to STDOUT, you can get the number of rows that COPY exports with a PL/pgSQL function along the lines of:

CREATE OR REPLACE FUNCTION copyout(query text, output_path text) RETURNS integer AS $fn$
DECLARE
    result integer;
BEGIN
    EXECUTE 'COPY (' || query || $$) TO '$$ || output_path || $$'$$;
    GET DIAGNOSTICS result = ROW_COUNT;
    RETURN result;
END
$fn$ LANGUAGE plpgsql;

Execute with something like this (note the path is on the server not the client):

> SELECT copyout('SELECT * FROM mytable', '/tmp/myfile.csv');
 copyout
---------
     981
(1 row)

However - just found out this was added in 9.3 (see 9.3 Release Notes). Sorry.


How to check programmatically which replica I'm connected to I have an AlwaysOn Availability Group on MS-SQLServer (2 replicas) and a C# application which sends queries to the Availability Group Listener.

How can I check which replica is responding my queries?

I know it's possible, because I've seen it in MS educational videos.

Thank you in advance

You can run SELECT @@SERVERNAME which will give you the current servername you are connected to.  Would this suit your requirements?


Creating a copy of primary database and attaching the copy database in the same sql instance I just got into a project in which we have only one SQL Server 2012 copy. We have 12 databases attached to the primary instance of SQL Server 2012.

As I don't have SQL Server 2012 or supporting OS, I have to depend on the primary instance of SQL Server 2012.

I am not sure whether it is good to create copy/clone of all 12 databases and attach it to same instance and work on the copy for data manipulation and query testing purpose.

Based on Kin's query

SELECT
   SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
   SERVERPROPERTY ('ServerName') AS SQLServerName,
   SERVERPROPERTY ('Edition') AS ServerEdition,
   CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) +  ' + '
       + CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build'
       + CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion,
    RIGHT(@@version, (Len(@@Version)-charindex('Windows',@@version, 1))+1) AS [O.S.],
 SERVERPROPERTY ('Collation') AS Collation

Following is the output. I ignored PhysicalMachineName and SQLServerName.

ServerEdition : Business Intelligence Edition (64-bit)
ProductVersion : SQL Server 2012 + RTM + (Build11.0.2100.60)
O.S.           : Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Collation      : SQL_Latin1_General_CP1_CI_AS

IF the server is NOT a PRODUCTION server, then you can backup the databases you need for Data manipulation and restore them with databaseName_test.

Keep in mind that you will still be competing for resources on the server by creating another set of databases.

Why cant you install another instance of sql server ? Even the enterprise edition is free for 30 days (for evaluation purpose ONLY) or just buy a Developer edition which is much cheaper. If you have MSDN subscription, you will be able to get one. Windows Azure is also another option.

Below will give you the correct version and some more info :

     SELECT
   SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
   SERVERPROPERTY ('ServerName') AS SQLServerName,
   SERVERPROPERTY ('Edition') AS ServerEdition,
   CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) +  ' + '
       + CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build'
       + CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion,
    RIGHT(@@version, (Len(@@Version)-charindex('Windows',@@version, 1))+1) AS [O.S.],
 SERVERPROPERTY ('Collation') AS Collation


Checking vulnerabilities for Oracle Data Integrator version 12.2.1.3.0 I understand October 2020 Oracle patches included security updates for Oracle Data Integrator (ODI) version 12.2.1.3.0.
The application admin who is managing ODI for us is really new and used to be with Windows desktop support. He is willing to help but needs guidance on what to look for.

How can we determine if the Oracle Data Integrator version 12.2.1.3.0 we are running is vulnerable to CVE 2020-5398 or not? The application admin ran a Qualys scan and it did not detect vulnerability for CVE 2020-5398 but an auditor is going to be visiting us soon so we need another confirmation that we are not impacted for CVE 2020-5398. The application admin asked people he knew and did not hear that anyone was using Spring framework whose vulnerability is fixed in CVE 2020-5398 and did not hear anything but since he is new, he is not sure if CVE 2020-5398 affects us or not.

Is there any tool/script by Oracle by which we can definitively know what vulnerabilities affect us for the Oracle Data Integrator version 12.2.1.3.0?

3.How can we ensure that we are patched for Oracle released updates and did not miss anything? We use Qualys application to scan our systems for vulnerabilities but our auditor needs a second confirmation.

Logon to My Oracle Support and search for "CVE 2020-5398".  I just did that and there are many hits listing patches that address this.
But let's suppose a hypothetical question.  Suppose the vulnerability is not fixed by any available patch.  What is your next course of action?


ANSI/ISO plans for LIMIT standardization? Are there currently plans to standardize one best way of limiting the number of results returned by a query?

The stack overflow question at Is there an ANSI SQL alternative to the MYSQL LIMIT keyword? lists the various ways to handle this behavior in different languages:

DB2 -- select * from table fetch first 10 rows only
Informix -- select first 10 * from table
Microsoft SQL Server and Access -- select top 10 * from table
MySQL and PostgreSQL -- select * from table limit 10
Oracle -- select * from (select * from table) where rownum <= 10

I don't play in DBs that often so I'm speaking from ignorance here, but it seems like this is a pretty important functionality - at least important enough that I scratch my head when I see it has been left up to vendors.

The latest draft SQL standard that I could find on the internet (dated 21/12/2011) has the following available for use in a query expression:

<result offset clause> ::=
    OFFSET <offset row count> { ROW | ROWS }

<fetch first clause> ::=
    FETCH { FIRST | NEXT } [ <fetch first quantity> ] { ROW | ROWS } { ONLY | WITH TIES }


Best Practice in storage allocation for compressed and partitioned tables I have a fairly large database that basically consists of one table, this table is paritioned by month and all partitions are compressed with page compression. After the initial load of the data there have been update operations (basically one column was set from NULL to an tinyint value). After that I noticed that in the disk usage report showed on the files that the space reserved did grow very large compared to the space used
(81 GB vs. 24 GB). This obviously results in a huge waist of disk space. Compressing the partition and shrinking the file results in an awfully fragmented clustered index for that partition.

What would be best practice to reduce the disk space usage to the absolute minimum ?

Rebuild the index to write out all pages freshly in the optimal way.

If you want to absolutely minimize space usage, specify ONLINE = OFF. Online operations add a small amount of per-row space overhead.

MAXDOP = 1 can be good to reduce fragmentation. Not sure if that can help with space usage, though. I can't think of anything that a DOP of 1 might save.

If the target file of the rebuild already contains fragmented freespace the newly built index/partition can turn out to be fragmented immediately. Consider building into an empty target filegroup that is pre-sized to approximately the right size.


How to disable annoying MySql update console I have installed mysql 5.7, and from time to time (once per several days) it looks for updates (as I understand). Console window opens for several seconds, and then dissappear. It doesn't care, that I'm watching a movie in this time, and this console is on the top of my player. It's really annoying, why MySql is updating in this user unfriendly way? Can I configure it to do it silently?

I may be running a more recent version of MySQL, but what I had to do to disable this was:

Run the MySQL Installer (located at C:\Program Files (x86)\MySQL\MySQL Installer for Windows)
Click the wrench icon
Un-check the box to check for updates


Seting MAXDOP for SQLserver 2016 and above I was going through MS documentation reg: setting MAXDOP starting SQL2016 as that Automatic soft NUMA enabled by default and getting confused on how to make the changes to current SQ2014 and SQL2012 versions:

MSDN link-- https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-2017#Guidelines

Current SQL2012/2014 setup:

Logically Processors =80
Numa Node=4
Physical RAM= 512GB
MAXDOP=8 and CTOP=5
HyperThreading enabled ='True'

So whats the change after moving them to SQL2016 and above? Will MAXDOP of current setup does not hold any more and needs to tune ? if yes what is MS proposing?

Please share your experiences on setting this up.

With Hyperthreading enabled, you currently have 4 hard NUMA nodes with 10 physical cores and 20 logical cores each. On SQL Server 2012 and 2014 you will have 4 matching soft NUMA nodes with 20 logical cores each.

Upon upgrading to SQL Server 2016 or higher, auto soft NUMA will automatically kick in. You'll now have 2 soft NUMA nodes per hard NUMA node for a total of 8. The soft NUMA nodes will stripe across even and odd CPUs per hard NUMA node. For example, soft NUMA node 0 might contain schedulers 0, 2, 4, 6, 8, 10, 12, 14, 16, 18 and soft NUMA node 1 might contain schedulers 1, 3, 5, 7, 9, 11, 13, 15, 17, 19.

The blog post that you referenced is difficult to understand, but I believe you are meant to fall under the "Server with multiple NUMA nodes" category (you have 4) and the "Greater than 16 logical processors per NUMA node" category (you have 20). The recommendation as a result is "Keep MAXDOP at half the number of logical processors per NUMA node with a MAX value of 16", so Microsoft is recommending that you change MAXDOP to 10.

That doesn't match my experience with large servers. I would leave MAXDOP set to 8 unless you run into some kind of issue. IMO changing it from 8 to 10 in your scenario isn't meaningful and is just introducing a new change without a clear benefit. I blogged about how I disagree with the published guidance here.


How to enhance speed of UPDATE statement Out of practice , some tips on indexes efficiency appreciated.
Creating test data ... changing consultant name on a large table using an update statement. one of my tables has 10 rows, second table has 4500 rows ...

Tips appreciated to reduce the process time...

currently getting this error message which I presume is a timeout ...

  Lookup Error - MySQL Database Error: Lock wait timeout exceeded; try
  restarting transaction

drop table if exists cons_temp;

create temporary table cons_temp
as select distinct consultant_id, consultant
from testdata_biactivity_rob;

update testdata_biactivityx a
inner join cons_temp b on b.consultant_id = a.consultant_id
set a.consultant = b.consultant;

Change the temp table to:

CREATE TEMPORARY TABLE cons_temp (
    PRIMARY KEY(consultant)
    ) ENGINE=InnoDB
    SELECT DISTINCT consultant_id, consultant
          FROM testdata_biactivtity_rob;
There is no need for the create table to be in the transaction, so start with

SET autocommit=ON;

This will isolate the "wait timeout" to the UPDATE.


How to calculate cache misses for PostgreSQL I'm administering a server where a tool using a PostgreSQL runs. The tool takes care of most PostgreSQL configurations by itself, but I'm observing some performance problems. I could confirm at OS level that a lot of I/O is taking place, therefore I suspect that a lot of cache misses are taking place.

If you look for "cache miss" or "cache miss postgresql" or similar searches in the Internet, you will find a lot of references to "cache_miss statistics". But nowhere is explained how to get them! I kind of understood, that that value has to be calculated subtracting hits from fetches. But since I'm no experienced DB admin, I don't really understand with values are meant :-S

I found the PostgreSQL - Monitoring Database Activity documentation, but I'm not sure if following formula is all I need:

cache_miss = "result_of" pg_stat_get_db_blocks_fetched(oid) - "result_of" pg_stat_get_db_blocks_hit(oid)

An explanation for dummies would be highly appreciated.

Thank you in advance!

I use this query to show disk x cache hits:

-- perform a "select pg_stat_reset();" when you want to reset counter statistics
with
all_tables as
(
SELECT  *
FROM    (
    SELECT  'all'::text as table_name,
        sum( (coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) ) as from_disk,
        sum( (coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0))  ) as from_cache
    FROM    pg_statio_all_tables  --> change to pg_statio_USER_tables if you want to check only user tables (excluding postgres's own tables)
    ) a
WHERE   (from_disk + from_cache) > 0 -- discard tables without hits
),
tables as
(
SELECT  *
FROM    (
    SELECT  relname as table_name,
        ( (coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) ) as from_disk,
        ( (coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0))  ) as from_cache
    FROM    pg_statio_all_tables --> change to pg_statio_USER_tables if you want to check only user tables (excluding postgres's own tables)
    ) a
WHERE   (from_disk + from_cache) > 0 -- discard tables without hits
)
SELECT  table_name as "table name",
    from_disk as "disk hits",
    round((from_disk::numeric / (from_disk + from_cache)::numeric)*100.0,2) as "% disk hits",
    round((from_cache::numeric / (from_disk + from_cache)::numeric)*100.0,2) as "% cache hits",
    (from_disk + from_cache) as "total hits"
FROM    (SELECT * FROM all_tables UNION ALL SELECT * FROM tables) a
ORDER   BY (case when table_name = 'all' then 0 else 1 end), from_disk desc


MySQL audit and general log May ask for the difference of MySQL Audit log plugin and general log? I looking for any activities done in a MySQL server.

Audit Plugin

MySQL Enterprise Audit is a new feature for security and compliance. It's not included in the Open Source / Community edition.

  As of MySQL 5.6.10, MySQL Enterprise Edition includes MySQL Enterprise
  Audit, implemented using a server plugin named audit_log. MySQL
  Enterprise Audit uses the open MySQL Audit API to enable standard,
  policy-based monitoring and logging of connection and query activity
  executed on specific MySQL servers. Designed to meet the Oracle audit
  specification, MySQL Enterprise Audit provides an out of box, easy to
  use auditing and compliance solution for applications that are
  governed by both internal and external regulatory guidelines.

have a look at the manual for more details.

By the way, MariaDB has its own implementation Audit Plugin. You can specify what exactly you wish to log.

CONNECT
QUERY
QUERY_DDL
QUERY_DML
QUERY_DCL

General Query Log

The general log is a quite old feature for the DBA and developer (to troubleshoot issues). The general log has a big performance impact and should be only enabled during short time slots.

  The general query log is a general record of what mysqld is doing. The
  server writes information to this log when clients connect or
  disconnect, and it logs each SQL statement received from clients. The
  general query log can be very useful when you suspect an error in a
  client and want to know exactly what the client sent to mysqld.

more details here.


Table is dropped but ibd file still exists I was dropping some unwanted tables I have in the MySQL. When I run a drop table, its showing table doesnot exists. But when a check in the data directory, the .ibd file exists and its almost 1GB. How can i free that space/drop table now ?

You can go to the OS and drop the file because the data dictionary in the system tablespace (ibdata1) does not know of the .ibd file's existence any more.
I wrote a post 2 months ago about deleting temporary .ibd files : temp table (#sql-7a87_230c32.ibd along with its .frm) still exists on slave. It applies in your case as well.
Give it a Try !!!


Is MongoDB secondary index size affected by primary key size? If I understand correctly, secondary index leaf nodes in MySQL InnoDB engine hold the table primary key value (at least for a unique index).

Thus, looking up a value in the secondary index results in two BTREE lookups: one for the secondary index and another one for the clustered index (clustered on the primary key). This also means that the primary key size affects the size of all secondary indexes.

Is this how MongoDB WiredTiger secondary indexes work too? Or do MongoDB secondary indexes store a reference to the physical block where the document resides? (I believe this is how Postgres handles indexes)

I understand correctly, secondary index leaf nodes in MySQL InnoDB engine hold the table primary key value (at least for a unique index).

InnoDB tables have a clustered index which determines where data for rows is stored. The clustered index for an InnoDB table is either the PRIMARY KEY (if set), the first unique index with all key columns NOT NULL, or hidden index containing synthetic row ID values. Secondary index entries will include a reference to the clustered index for the record location.

  Is this how MongoDB WiredTiger secondary indexes work too?* Or do MongoDB secondary indexes store a reference to the physical block where the document resides?

MongoDB (as at 4.0) does not support clustered indexes. The WiredTiger storage engine uses an approach similar to the third option described for InnoDB: an internal unique RecordID which is a 64-bit integer.

The WiredTiger storage engine currently uses a separate file for each collection and index. Collection data (collection-*.wt) is indexed using the internal RecordID; indexes (index-*.wt) map keys to RecordIDs in the associated collection data. These implementation details aren't relevant for the MongoDB API, which provides a consistent end user interface for indexing, querying, and manipulating data. Storage engines have ownership over how data is represented on disk and in memory. The approach is analogous to what happens under the hood in other database engines like InnoDB (where end users are interacting via the MySQL API).

The size of your primary key (which would be the _id field in MongoDB) currently has no impact on the size of secondary indexes in WiredTiger.

Incidentally, if you happen to be using the older (and deprecated) MMAPv1 MongoDB storage engine it does store references to the disk location (file and offset) where data resides. A significant downside of this approach is that document moves in storage (due to document growth) also require updating all secondary indexes. WiredTiger only needs to update secondary indexes when associated field values are changed.


sys.dm_sql_referencing_entities not returning results No doubt this is syntax or typo...but when I query sys.dm_sql_referencing_entities
for a parent table, I get zero results.  No error, just no results.

USE [source];
GO
SELECT *
FROM sys.dm_sql_referencing_entities ('[Test].[USERS]', 'OBJECT');
GO

When I view the dependencies from SSMS, I get

I've never used this before, so maybe I am misunderstanding how this works or what it should be returning?  I tried against the SUBSCRIPTIONS table as well, same result...nothing.

Referenced/referencing entity functions return instances where an object, column etc. is referenced directly (usually in other objects like stored procedures, but Mark has the full list from the documentation).

These functions are not meant to analyze foreign key dependencies, which you can get from the foreign key catalog views. This simple query assumes all tables are in the dbo schema, but you would need to add joins to sys.schemas in order to flesh those out otherwise:

SELECT 'FK points from ', f.name, ' to ', t.name
  FROM sys.foreign_keys AS fk
  INNER JOIN sys.objects AS f
    ON fk.parent_object_id = f.[object_id]
  INNER JOIN sys.objects AS t
    ON fk.referenced_object_id = t.[object_id];

The query in your question will return results if you create an object that references the table, e.g.:

CREATE VIEW [Test].MyView_DeleteMe
AS
  SELECT * FROM [Test].Users;


Will running a large query on a secondary database in an availability group affect transaction performance in the primary database? I need to provide real-time, or almost real-time, data for SSRS and Tableau reporting. I don't want the production OLTP system to be negatively impacted by long running queries. Will running a large query on a secondary database in an availability group affect transaction performance in the primary database?

Will running a large query on a secondary database in an availability group affect transaction performance in the primary database?

It depends on the sync mode you have used when configuring Availability group - Sync or Async !

On Secondary replica, all the transactions use Snapshot Isolation level ONLY and all locking hints are ignored as well. That's why it's important to test your workload when embracing AlwaysON.

From : Minimizing blocking of REDO thread when running reporting workload on Secondary Replica

  While mapping reporting workload to snapshot isolation eliminates blocking between DML workload as applied by the REDO thread on the secondary replica and the read or reporting workload, it does not eliminate the potential blocking of REDO thread when it is executing a DDL operation.

If using

Synchronous mode

Blocking issue on your secondary replica will impact performance of your queries on Primary replica. So a read workload (select) ran on secondary might blocked the redo thread from applying the changes that comes from primary replica. This means that the primary replica has to wait for changes to be applied to all secondary SYNC replica before it commits locally & might end-up in timeouts or blocking or deadlocking.

The REDO thread can be seen on the Readable Secondary as the DB STARTUP command in sys.dm_exec_requests. If that thread is being blocked, then your read workload on the secondary could be causing an impact on the primary.

For more details check - Scenario 1:  Blocked REDO due to large query on the secondary replica

Asynchronous mode

The primary does not wait for acknowledgement from secondary. A blocking issue on secondary is just isolated to secondary wherein the redo queue will grow on the secondary until the locks are clear and the redo thread is able to apply the log blocks. This won't affect the primary replica.

Your definition of "real-time or almost real-time" needs more thought keeping in mind the sync method used, the network latency and how busy is primary replica and the log activity that needs to be transported secondary.

SQL Server 2016 has done some major enhancements in AlwaysON realm e.g.

Improved compression algorithms & Parallel compression of log block data
4x to 5x improvement in log block transport throughput
and many others - It Just Runs Faster !


Remove default value from NOT NULL column I have an existing NOT NULL column which has a default value. I want to remove the default value so that the user is forced to enter a value.

mysql> SHOW CREATE TABLE items;
CREATE TABLE `items` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `ordering` int(11) NOT NULL DEFAULT '0',
   PRIMARY KEY (`id`)
)

However, when I try to modify the column and set the default to null, mysql throws an error:

mysql> ALTER TABLE items MODIFY ordering INT(11) NOT NULL DEFAULT NULL;
ERROR 1067 (42000): Invalid default value for 'ordering'

How can I remove the default from this column without having to do something crazy like change it to nullable, then remove the default, then change it back to not null?

The answer is to use the following (slightly odd) syntax:

ALTER TABLE items ALTER ordering DROP DEFAULT;


How to find all records in MongoDB whose primary key is a user defined character string I would like to know the command to find all records in MongoDB whose primary key (_id) is a user defined character string, or find all records where all primary keys (_id) are not the 12-byte ObjectID BSON type.

For example, in SQL the query might be similar to:

select id from restaurants where id not like "%ObjectId("574d35bda039438e02236caf")%"

or

select id from restaurants where id in $regex[a-z];

You can only use the $regex operator to match values that are strings. As you've noted, an ObjectID is a 12-byte binary value stored as a specific BSON type.

You should instead use the $type operator to query based on the BSON data type of a field.

Example usage of $type:

// Find all restaurants where `_id` is not an ObjectId (type 7)
db.restaurants.find({_id: { $not: { $type: 7}} })

// Find all restaurants where `_id` is a string (type 2)
db.restaurants.find({_id: { $type: 2} })

Note: the BSON type is a numeric value, but MongoDB 3.2+ also has friendlier string aliases (i.e. the string "objectId" is equivalent to the numeric $type value of 7 in MongoDB 3.2).

If you already know the _id value you are searching for is a string, you also have the option of using the $regex operator to match a more specific pattern without specifying $type; the non-string values will be ignored by $regex.

For example:

// Find all restaurants with an `_id` starting with Happy (case-sensitive)
db.restaurants.find({_id: /^Happy/})

// Find all restaurants with an `_id` only using word characters (`\w` is `[A-Za-z0-9_]`)
db.restaurants.find({_id: /^[\w]+$/})

If you are running any of these queries regularly against a large collection (or have concerns on performance impact), you should review index usage via explain results. In particular, negated queries and $regex queries have some caveats on efficient index usage.


What would be the correct size of redo logs? I installed and put in production a 11g database. I was adviced to size redo logs to 200MB each one. Now I see that my archived redo logs are each one 200 MB and all of them occupy a lot os disk space. Now I would like to change that size to 100MB or 50MB.

My question is: what would be the OK size for a redolog, depending on what?

Changing the size of your redo logs won't affect how much space is occupied by archived redo logs.  If you reduce the size of your redo logs from 200 MB to 100 MB, you'll end up with twice as many archived redo logs each of which is half the size.  The volume of archived logs that you generate is dependent on the amount of REDO your system generates not on the size of your redo logs.

In general, you want the redo logs to be large enough that you are not constantly swapping logs (generally not more than every 15 or 20 minutes) and small enough that you aren't risking too much data should there be a catastrophic server failure (assuming that archived redo logs are written to a different server and that you aren't using something like DataGuard to replicate the redo to a backup server in real time).


Why can't I insert an email on a table that has one more dot than an existing one? I'm using the official Heroku add-on for PostgreSQL and I have a database running on version 12.4 of PostgreSQL.
I'm facing an issue where on my users table I have an email like this fakeemail@domain.com and I want to add another one that is fake.email@domain.com. This email column has an associated index that was defined like this:
CREATE UNIQUE INDEX users_lower_email_index
    ON public.users USING btree
    (lower(email::text) COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

It basically assures that I am not able to insert the same email twice if I for some reason try to add the same email with upper case letters. Beside this index, the only other index is the primary key which is a GUID, and there are no triggers or other constraints.
Whenever I try to insert the fake.email@domain.com I get the following error:
ERROR:  duplicate key value violates unique constraint "users_lower_email_index"
DETAIL:  Key (lower(email::text))=(fake.email@domain.com) already exists.

I have queried this table numerous times and I can guarantee that such an email does not exist. So the next thing I assumed could be wrong, is that the index could be somehow corrupted. So I rebuilt the index but I still wasn't able to insert that email - the same error is thrown. I also tried vacuuming the table and then re-indexing the index again but it still didn't work with the same error.
I have no idea what could be wrong or what to try next.

rdms usually don't make mistakes
So run a query
SELECT * FROM users WHERE lower(email::text) = 'fake.email@domain.com'

and see where it gets you a result, with upper letters


Convert a date to yyyymmdd format Which SQL command is recommended to convert a date to yyyymmdd format?

convert(varchar(8), getdate(), 112); or
convert(varchar, getdate(), 112)

I notice that if I use the second one, it will append two spaces after the date. (e.g. [20130705] - notice the two space after the value 20130705)

Is it recommended to use the first SQL statement?

By default, as documented in MSDN, if no length is specified for varchar it will default to 30 when using CAST or CONVERT and will default to 1 when declared as a variable.
To demonstrate, try this :
DECLARE @WithLength varchar(3),@WithoutLength varchar;
SET @WithLength = '123';
SET @WithoutLength = '123';

SELECT @WithLength,@WithoutLength

This is very dangerous, as SQL Server quietly truncates the value, without even a warning and can lead to unexpected bugs.
However, coming to the topic in question, in the given scenario, with or without length does not make any difference between the two statements and I am unable to see the 2 trailing spaces that you are talking about. Try this:
SELECT CONVERT(varchar(8), GETDATE(), 112)
UNION ALL
SELECT DATALENGTH(CONVERT(varchar(8), GETDATE(), 112))
UNION ALL
SELECT CONVERT(varchar, GETDATE(), 112)
UNION ALL
SELECT DATALENGTH(CONVERT(varchar, GETDATE(), 112))

You will notice that the DATALENGTH() function returns 8 in both cases.
Raj