How do I compare Schemas for Multiple Databases to a True Copy?

This will compare database scripts. Did not want to utilize sql compare, as it may take additional schema lock time on server.

#Set-ExecutionPolicy RemoteSigned

#OriginalDatabaseScript, this can be generate from source control or Master Copy database in Production
$OriginalScript = 'C:\DatabaseCompare\OriginalScript.sql'

#Source: Place in test file below, list databases by running select * from sys.databases where database_id > 4
$DatabaseSourceList = 'C:\DatabaseCompare\DatabaseList.txt'

#Destination: Database Files Generate Scripts
$Filepath='C:\DatabaseCompare\scripts' # local directory to save build-scripts to

foreach($line in Get-Content $DatabaseSourceList)
{

$DataSource='localhost' # server name and instance
$Database=$line # the database to copy from
# set "Option Explicit" to catch subtle errors
set-psdebug -strict
$ErrorActionPreference = "stop" # you can opt to stagger on, bleeding, if an error occurs
# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$ms='Microsoft.SqlServer'
$v = [System.Reflection.Assembly]::LoadWithPartialName( "$ms.SMO")
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
[System.Reflection.Assembly]::LoadWithPartialName("$ms.SMOExtended") | out-null
   }
$My="$ms.Management.Smo" #
$s = new-object ("$My.Server") $DataSource
if ($s.Version -eq  $null ){Throw "Can't find the instance $Datasource"}
$db= $s.Databases[$Database]
if ($db.name -ne $Database){Throw "Can't find the database '$Database' in $Datasource"};
$transfer = new-object ("$My.Transfer") $db
$CreationScriptOptions = new-object ("$My.ScriptingOptions")
$CreationScriptOptions.ExtendedProperties= $true # yes, we want these
$CreationScriptOptions.DRIAll= $true # and all the constraints
$CreationScriptOptions.Indexes= $true # Yup, these would be nice
$CreationScriptOptions.Triggers= $true # This should be included when scripting a database
$CreationScriptOptions.ScriptBatchTerminator = $true # this only goes to the file
$CreationScriptOptions.IncludeHeaders = $false; # of course
$CreationScriptOptions.ToFileOnly = $true #no need of string output as well
$CreationScriptOptions.IncludeIfNotExists = $true # not necessary but it means the script can be more versatile
$CreationScriptOptions.Filename =  "$($FilePath)\$($Database)_Build.sql";
$NewFilename =  "$($FilePath)\$($Database)_Build2.sql";

$transfer = new-object ("$My.Transfer") $s.Databases[$Database]

$transfer.options=$CreationScriptOptions # tell the transfer object of our preferences
$transfer.ScriptTransfer()
"Created Database " + $Database

#Get-Content $CreationScriptOptions.Filename | Where { $_ -notmatch "/" } | Set-Content $NewFilename

if(Compare-Object -ReferenceObject $(Get-Content $OriginalScript) -DifferenceObject $(Get-Content $NewFilename))
    {$Database + " Files are different"}
Else
    {$Database + "Files are the same"}

 }

Edited from here:
Automated Script-generation with Powershell and SMO


Help with designing sales and inventory management system

Your Product table shouldn't store the Price at all. You need a separate table called something like Purchases which has a row for every purchase made, the Price and PurchasedDate, and a foreign key field to reference the Product table.
Also, tables like Purchases and Sales are normally broken out further into two tables each. One table having header information and the other the line information. For example, PurchaseOrders would store the header information like PurchasedDate, PurchasedBy, maybe the Company purchased from (if only one Company can be on one Order), where the PurchaseLines table would be a row for every item purchased on that PurchaseOrder, with the Quantity (per item), Price, and foreign key field to relate back to the PurchaseOrders table. Similar structure for SalesOrders and SalesLines.
PurchaseOrders are one-to-many PurchaseLines. (SalesOrders are one-to-many SalesLines.)


As the linked answer on SO explains, a cast is only applicable to the exact input data type declared in the CREATE CAST definition. And a string literal is of data type unknown initially - even though the default data type of a string literal is text.

Postgres 10 ties up some loose ends for the data type unknown with this commit (with explanation).
And unknown is a pseudo-type now. Details in this commit.

But your problem still does not go away, because you try to cast a string literal directly, before the new default kicks in.

You write:

  PostgreSQL parses string literal as record before calling cast

That's almost but not exactly what happens: A string literal representing a composite or row type is required to be enclosed in parentheses. That's the immediate cause of the error. The literal is not "cast as record" and the cast would never be called. This would actually work with the default "I/O conversion cast":

SELECT '(my,widget,value)'::widget;

You might do without the custom cast altogether and just use your function directly instead:

SELECT text_to_widget('my widget value');

Does exactly the same anyway. And you don't need to cast the function parameter because Function Type Resolution is smart enough to chose the right function (as long as the call is unambiguous).


Switching alias in subquery equality leads to different result

Think about the meaning of your queries as well, not just the structure.
It is not an order, but an employee-manager (boss) hierarchy.

First query means: list all employees who are managers of other employees.

There are 18 different manager_id values in the table, so you will get the data of 18 employees (as seen on your picture):

SQL> select count(distinct manager_id) from employees;

COUNT(DISTINCTMANAGER_ID)
-------------------------
                       18

Second query means: list all employees that have managers. There are 107 employees and only one of them has no manager, so you get the data of 106 employees (as seen on your picture):

SQL> select count(*), count(manager_id) from employees;

  COUNT(*) COUNT(MANAGER_ID)
---------- -----------------
       107               106


Cores, processes, CPUs, threads, hyperthrading, connections -- they are mostly interchangeable at this level of discussion.

MySQL has always been able to handle multiple connections and keep things straight.  Back in the single-core single-cpu days, two connections would start up two threads (or processes, depending on OS details) and run seemingly simultaneously.  With 12 CPUs / 24 cores, the 'only' difference is that there is now less contention for CPU resources.

But...  One connection will use only one core.  This may explain what you are seeing.  100 connections may use (and share) all 24 cores.

So, if you question is really about "parallel processing" where one connection uses multiple cores, the answer is "no" in all versions of MySQL/MariaDB.  (A slight exception:  InnoDB has some 'helper' threads that run in the background, possibly making use of more cores.)  This still applies in the hot-off-the-press version 8.0.

In my experience, a dedicate MySQL server rarely goes past one core's worth of CPU resources.  When it does, there is probably a query that needs a better index, or something else.


Unable to Restore a Database to a previous point in time from a Full Backup Set

You didn't say what backups you have. If you only have a full backup, then there's no point-i-time restore. It can only be restored to the "snapshot in time" it reflects (towards the end of the backup period).

If you do have also transaction log backups, then I agree that you should test SSMS and see what RESTORE commands it generates (you have a script button to see the TSQL) and then take it from there.


The hostname component is the reference to the remote address of the connection. See create user.
Recommend keeping this constrained to a subnet.
Domain based grants while they look good, need reverse DNS setup to resolve this to an IP before allowing a connection. I recommend avoid them and using skip-name-resolve as a configuration option for extra speed and reliability.


You'll know when you need to shrink your database files, because it'll most likely come from a requirement to reclaim disk storage.  Even if there is a database that has a large amount of free space in it, I usually still like to keep it as such without shrinking.  No matter what people try to claim that it'll never use all that space, unless the database is read only then there is no guarantee for that.  Obviously, that's within reason.  If you for some reason have 1 TB free space on a 10 GB database, then that may warrant a shrink, unless it's expected to regrow at a quick rate.

The most important aspect about database file shrinking is to only do it when it is a hard requirement and do not do it routinely.


Perhaps you can disable foreign key checks and unique checks:

SET FOREIGN_KEY_CHECKS=0;
SET UNIQUE_CHECKS=0;
DROP TABLE ...;

This has to work since a standard mysqldump embeds commands like these and a DROP TABLE before creating and reloading it. I have written about before

Jun 11, 2013 : Speeding up mysqldump / reload
May 26, 2013 : Is it possible to force drop of mysql tables with FK?
Jul 09, 2012 : What can cause TRUNCATE TABLE to take a really long time?

Further Suggestion : Based on a post from mysqlperformanceblog, you should get Percona Server or upgrade to the latest version of MySQL (5.5.23 has improvements to DROP TABLE, so 5.6 should have those improvements as well).

As for your current DROP TABLE, you must let it ride for maintain InnoDB's stability.


Oracle database creation - recording response file option not available

What version of the installer are you running?  Saving response files in DBCA is available starting with version 12.2.  You can maybe launch the installer using the -record option to create a response file:

https://docs.oracle.com/cd/B19306_01/em.102/b16227/oui4_response_files.htm#i1005622


MVCC applies to isolation levels read-committed and repeatable read (default).

You don't need to specify anything for both of these features to work together.  Maybe one way to think about it, is that row level locking is important so that you can update multiple rows at a time, and MVCC is so that the updates don't affect read operations at all.


If wsrep_local_state_comment, wsrep_ready, and wsrep_connected show the correct values, then that particular node is good.

There's no Seconds_Behind_Master equivalent, because Galera is synchronous -- a healthy node can't be lagging, by definition and by design... a slow node slows down all the others, it doesn't lag behind them.


Working across multiple PostgreSQL database servers

Foreign data wrappers are currently read-only.  This will change in the future, but that's the way it is.  You can also use dblink to access other databases.  As I understand it a big issue will be commit handling.  I would recommend against this approach unless you are trying to aggregate lots of data that is really external.  It is a huge complexity cost and the benefits have to be worth it.

Edit:  Note that foreign tables and foreign servers are how you use foreign data wrappers to map in foreign data to your application.  You can't choose between them.  It's all three or nothing.


SSMS 2012 is not showing the tables of the system databases

As per this Connect item it will be fixed in the next major release of SQL Server.


Why do sys.columns and INFORMATION_SCHEMA.COLUMNS display different number of columns

The difference between INFORMATION_SCHEMA.COLUMNS and sys.columns is the object types they cover. INFORMATION_SCHEMA.COLUMNS is restricted to tables and views.  You can take a look at the code behind it by doing this:

EXEC sp_helptext [INFORMATION_SCHEMA.COLUMNS]

If you look at the bottom in the where clause you will see

o.type IN ('U', 'V')

This is where it is restricted to the tables and views only.

If you then go look at the definition of sys.columns you will see that it includes columns for a number of other objects:

Table-valued assembly functions (FT)
Inline table-valued SQL functions (IF)
Internal tables (IT)
System tables (S)
Table-valued SQL functions (TF)

Run this and you will be able to see what the extra object is and what type it is.

SELECT OBJECT_SCHEMA_NAME(object_id), OBJECT_NAME(object_id),
    OBJECTPROPERTYEX(object_id, 'BASETYPE'), *
FROM sys.columns


Query against a view vs. query against the underlying base table

It depends on the construction of the view. SELECTing from a view with NOLOCK or READUNCOMMITTED table hints should avoid blocking the underlying tables (with the risks that entails).

Indexed views (SQL Server term - more generally called materialized views) actually are stored on disk separately from their underlying tables, and are updated as the underlying tables are updated. This not only consumes more disk space, but can lead to contentions and delays if the underlying tables are frequently modified. SELECTing from the view can still block updates to the tables underneath, since when those tables are updated, the index for the view is also updated.

And, as you thought, SELECTing from a normal view can temporarily block queries that are changing the underlying tables, just like SELECTing from the tbles directly can.


How can I detect broken stored procedures after a schema change?

How about you run your your unit, functional, integration and performance tests? If you don't have any tests then is serious time to start considering your database schema as code and treat it as such, including version control and testing. Alex Kuznetsov has an entire book dedicated to this subject: Defensive Database Programming with SQL Server.


This solved the issue for me:

grant execute on sys.dbms_crypto to myuser;

I had a similar problem with the random function.


What SSMS and/or Visual Studio tools sync SQL Server environments?

You can purchase SQL Compare from Red-Gate.


Impact of deleting 100GB table on Mirroring

What is type of mirroring Sync or Async

Yes there would be impact and impact would be in terms of amount of transaction log being generated. Delete logs each row which is being deleted so logging will be heavy. Your approach should be to delete in batches this would minimize lock escalation, blocking and thus would not hamper concurrency much. You should also consider performing this activity when load is relatively less

How strong is your network connecting principal and mirror. You are going to put heavy load on it if you delete 100G table so becareful with that aspect also its good to ask network team to have a look on network during this period of time you would not like your mirror to lag behind principal.

Can you truncate the tables or selectively truncate tables, it is super quick and logging is very very less as compared to delete but NOTE there are limitations with truncate table and before proceeding please read below Microsoft documentation

http://msdn.microsoft.com/en-gb/library/ms177570.aspx


What exactly is the datatype input into a DATETIME value in MySQL

You must provide the value as string literal or as a numeric literal.
Possible formats are described in MySQL 8.0 Reference Manual  /  ...  /  Date and Time Literals.
Alternatively you may provide the data in any custom format and apply STR_TO_DATE() to it in INSERT query with according parsing pattern.
DEMO


In FoxPro, under the Wizard menu, choose "upsizing". From there, choose an ODBC connection to the SQL Server database and step through it. Also, there is a much improved upsizing Visual FoxPro project that you can download here.

I have had trouble upsizing to SQL Server 2008 and above so if you have a SQL Server 2005 database around, you might have more success upsizing to it and migrating your tables to there final destination.


How to enter records with keys to an initially empty B+ tree?

I think you have your page creation upside down. When a node splits it doesn't create more nodes down the hierarchy (son nodes in your nomenclature). Instead it creates more upwards, toward the root. As the book says

  Note that the growth is at the top of the tree, and this is an intrinsic characteristic of a B–tree to ensure the important properties that it always have all the leaves at the same level, and each node different from the root is at least 50% full.

(My emphasis.)

From the linked ebook:

  Definition 5.1 A B–tree of order m (m ≥ 3) ... each node contains at most m − 1 keys

The exercise is for m=3, so at most 2 keys per node.

The first two keys are easy -- they go into the first page:

A:[1,2]

I'm going to use ASCII art. I'll label each page in the sequence they're created and show the keys/ pointers within the page. So page P containing key values k1 and k2 will be P:[k1,k2].

Now key 3 comes along. According to Section 5.2.1 ... Insertion, the first task is to search. This determines key 3 should be on page A - the only page we have. Further "if [that node] is full, it will be split into two nodes." The page is full so it must split. We now have

A:[1,2]    B:[3, ]

But this is not a tree! As the book says:

  the pointer to [the new node], .. is inserted into the father node .. of [the current node], repeating the insertion operation in this node [i.e. the father node]. This splitting and moving up process may continue if necessary up to the root, and if this must be split, a new root node will be created ..

(My emphasis to show processing continues up the tree toward the root, not down toward leaves.)

So we must put a pointer to the new page (B) into the father of the current page (A). There must be a new root node:

      C:[2,3]
      /     \
A:[1,2]    B:[3, ]

I have the pointers in non-leaf pages pointing to the highest value in a child (son) node. Your linked text may do this differently but the result will be equivalent.

Key value 4 arrives; following the algorithm we search to find which page it should be on. It should be page B. There is room for it so we update that page and the pointer on page C:

      C:[2,4]
      /     \
A:[1,2]    B:[3,4]

Next we insert key 5. It should go in page B but it's full. Therefore it splits

      C:[2,4]
      /     \
A:[1,2]    B:[3,4]   D:[5, ]

We must update the father node. It, too, is full so it splits:

      C:[2,4]    E:[5, ]
      /     \         \
A:[1,2]    B:[3,4]   D:[5, ]

The split propagates up and a new root node forms:

            F:[4,5]
            /     \
      C:[2,4]    E:[5, ]
      /     \         \
A:[1,2]    B:[3,4]   D:[5, ]

By growing upwards the tree maintains identical depth in every branch. This is important for predictable performance. (Some say the B in B-Tree stands for "balanced" for this very reason.)

As for the second part -- "Is it possible to enter the records with keys in a different order to have a tree of less height?" With 5 keys and two keys per node we need at least 3 leaf nodes to hold all values and a height of 3 to form the tree. So my arrangement is optimal for the given data, sequence and algorithm.

The book uses a very different pointer arrangement to what I use, and a different page split arrangement. This will be significant, leading to part-full pages. That there is a section on page 42 called "Data Loading" that shows how fuller pages can be achieved by loading out of key sequence supports my hunch. However, I hope I've given you sufficient pointers and you'll be able to use the book's pointer structure to work that out for yourself.

I've come across this interactive simulation of how a B-Tree grows.


SQL Server contains no facility to only backup the schema of a database.  Backup-and-restore can only be used to restore the entire contents of a database or, in the case of piecemeal restore, individual filegroups of a database, assuming there are multiple filegroups.

However, there are multiple tools available that can be used to export and synchronize schemas between versions of databases.

Just a couple of options, listed alphabetically:

ApexSQL SQL Diff
dbForge Schema Compare
Idera SQL Comparison Toolset
SQL Server Data Tools - MSBuild Support for schema comparison


How does SQL Server reduce index fragmentation?

No, there is no auto-magical defragging of indexes. If you have fragmentation, you need to REBUILD or REORGANIZE.

Reorganizing an index defragments the leaf level of an index by physically re-ordering pages to match the logical order. Lock durations are short and will cause minimal blocking of queries.

Rebuild drops an index and builds a new one. With Enterprise edition this can be done as an online operation if the index doesn't include any LOB types. With Standard edition or where LOB types exist, it will cause blocking. See How Online Index Operations Work for an explanation of how online rebuilds can occur concurrently with user operations.

The Reorganize vs Rebuild recommendation is roughly the threshold at which the amount of work required to defragment by reorganizing is comparable with a rebuild i.e. at >30% fragmentation it will require more resources and greater elapsed time to complete a reorganize than a rebuild.

Multiple reorganize runs would not further defragment the index.


Unpivot with rows converted to columns as well as columns converted to rows

This query uses both UNPIVOT and then PIVOT:

SELECT piv.[Data], piv.x, piv.y
FROM (
    SELECT [Type], ColA = CAST(ColA as varchar(10)), ColB = CAST(ColB as varchar(10)), ColC = CAST(ColC as varchar(10)), ColD = CAST(ColD as varchar(10))
    FROM @data
) d
UNPIVOT (
    [value] FOR [Data] IN (ColA, ColB, ColC, ColD)
) as unpiv
PIVOT (
    MAX([value])
    FOR [Type] IN ([x], [y])
) as piv
;

It first unpivots your data in order to get a regular table
It then pivots this table back to the new required format

I have to cast everything as varchar. Based on your data model, you may have to cast them to another type. An aggregate function is required by PIVOT. Since this is a 1 to 1 match, it works with either of MAX or MIN.

With SQL Server >= 2008 you can also replace UNPIVOT by Table Value Constructor and CROSS APPLY along with PIVOT:

SELECT piv.[Data], piv.x, piv.y
FROM (
    SELECT v.[Type], [value], [Data]
    FROM @data d
    CROSS APPLY (values
        (d.[type], CAST(d.ColA as varchar(10)), 'ColA')
        , (d.[type], CAST(d.ColB as varchar(10)) , 'ColB')
        , (d.[type], CAST(d.ColC as varchar(10)), 'ColC')
        , (d.[type], CAST(d.ColD as varchar(10)), 'ColD')
    ) as v([type], [value], [Data])
) unpiv
PIVOT (
    MAX([value])
    FOR [Type] IN ([x], [y])
) as piv
;

Without PIVOT/UNPIVOT you can use this query with any (old) version of SQL Server:

SELECT [Data]
    , [x] = MAX(CASE WHEN [type] = 'x' THEN [value] END)
    , [y] = MAX(CASE WHEN [type] = 'y' THEN [value] END)
FROM (
    SELECT [type], [value] = CAST(ColA as varchar(10)), [Data] = 'ColA' FROM @data
    UNION ALL
    SELECT [type], [value] = CAST(ColB as varchar(10)), [Data] = 'ColB' FROM @data
    UNION ALL
    SELECT [type], [value] = CAST(ColC as varchar(10)), [Data] = 'ColC' FROM @data
    UNION ALL
    SELECT [type], [value] = CAST(ColD as varchar(10)), [Data] = 'ColD' FROM @data
) as v
GROUP BY [Data]

Output:

Data    x       y
ColA    123456  654321
ColB    $500    $200
ColC    6       36
ColD    30      90

Data:

Declare @data table([Type] char(1), ColA bigint, ColB varchar(10), ColC int, ColD int);
INSERT INTO @data([Type], ColA, ColB, ColC, ColD) VALUES
    ('x', 123456, '$500', 6, 30)
    , ('Y', 654321, '$200', 36, 90);


What's the difference between betwwen 2 DB and 2 Schema

By default, PostgreSQL's databases cannot see each other and you cannot query between them.

You can query between, or have foreign keys between PostgreSQL schemas.

If you want strong multi-tenant client segregation, use distinct databases.


Oracle how do I find out the max number of concurrent sessions?

The below returns the maximum number of concurrent sessions in the instance since it started, example:

SQL> select max_utilization from v$resource_limit where resource_name = 'sessions';

MAX_UTILIZATION
---------------
             94


I address this question back on Jun 08, 2012 : Update rank on a large table

First, make sure you have an index on rank and last_rank in the rank table

Next, make sure you have a points index on the score table

If you do not have such indexes, run the following:

ALTER TABLE rank  ADD INDEX (rank);
ALTER TABLE rank  ADD INDEX (last_rank);
ALTER TABLE score ADD INDEX (points);

You are going to have to update the last_rank on the fly using an UPDATE...JOIN:

SET @r=0;
UPDATE (SELECT id score_id FROM `score` ORDER BY points DESC) A
    LEFT JOIN `rank` B USING (score_id)
    SET B.last_rank = B.rank,B.rank = @r:= (@r+1)
;

Here is a more paranoid version

DROP TABLE IF EXISTS score_rank;
CREATE TABLE score_rank
(
    id        INT NOT NULL AUTO_INCREMENT,
    score_id  INT NOT NULL,
    rank      INT,
    last_rank INT,
    PRIMARY KEY (id),
);
SET @r=0;
INSERT INTO score_rank (last_rank,rank,score_id)
    SELECT B.rank,@r:= (@r+1),B.score_id FROM
    (SELECT id score_id FROM `score` ORDER BY points DESC) A
    LEFT JOIN `rank` B USING (score_id)
;
UPDATE rank A INNER JOIN score_rank B SET
    A.rank = B.rank,
    A.last_rank = B.last_rank
;
DROP TABLE IF EXISTS score_rank;

Give it a Try !!!

CAVEAT

If the table is very large, it may take time. As I suggested in Update rank on a large table, you may want to limit the ranking to the first 10,000 when ranking frequently, and then do the fuill ranking maybe once a week or once a month.

Therefore, ranking top 10,000 should look this

SET @r=0;
UPDATE (SELECT id score_id FROM `score` ORDER BY points DESC LIMIT 10000) A
    LEFT JOIN `rank` B USING (score_id)
    SET B.last_rank = B.rank,B.rank = @r:= (@r+1)
;

or this

DROP TABLE IF EXISTS score_rank;
CREATE TABLE score_rank
(
    id        INT NOT NULL AUTO_INCREMENT,
    score_id  INT NOT NULL,
    rank      INT,
    last_rank INT,
    PRIMARY KEY (id),
);
SET @r=0;
INSERT INTO score_rank (last_rank,rank,score_id)
    SELECT B.rank,@r:= (@r+1),B.score_id FROM
    (SELECT id score_id FROM `score` ORDER BY points DESC LIMIT 10000) A
    LEFT JOIN `rank` B USING (score_id)
;
UPDATE rank A INNER JOIN score_rank B SET
    A.rank = B.rank,
    A.last_rank = B.last_rank
;
DROP TABLE IF EXISTS score_rank;


How to tune the query on all_constraints in Oracle 12c?

Often the RULE-hint helps when querying dictionary views.

select /*+ RULE */ constraint_name,table_name
                from  all_constraints
         where  r_constraint_name in
                               (select  constraint_name
                                  from  all_constraints
                                where table_name='SUPPLIER');

But your query is not precise. Objects have an owner, too.

select /*+ RULE */ owner, constraint_name,table_name
                from  all_constraints
         where  (r_owner, r_constraint_name) in
                               (select  owner,constraint_name
                                  from  all_constraints
                                where table_name='SUPPLIER'
                                      and owner='SOMEONE');

But this query can be formulated as a join.

select /*+ RULE +*/ ref.owner, ref.constraint_name,ref.table_name
  from  all_constraints ref, all_constraints cons
  where ref.r_constraint_name=cons.constraint_name
    and ref.r_owner=cons.owner
    and cons.owner='&OWNER'
    and cons.table_name='&TABLE';

If you prefer the modern JOIN syntax

select /*+ RULE */ ref.owner, ref.constraint_name,ref.table_name
  from  all_constraints ref JOIN all_constraints cons
    on (ref.r_owner=cons.owner and ref.r_constraint_name=cons.constraint_name)
  where
  cons.owner='&OWNER'
  and cons.table_name='&TABLE';

here are some timing results, with and without RULE hint on a 12c database (12.1.0.2.0)

SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.44
SQL> select ref.owner, ref.constraint_name,ref.table_name
  2    from  all_constraints ref, all_constraints cons
  3    where ref.r_constraint_name=cons.constraint_name
  4    and ref.r_owner=cons.owner
  5    and cons.owner='OWNER'
  6    and cons.table_name='TABLE';

no rows selected

Elapsed: 00:00:03.95
SQL> select ref.owner, ref.constraint_name,ref.table_name
  2    from  all_constraints ref, all_constraints cons
  3    where ref.r_constraint_name=cons.constraint_name
  4    and ref.r_owner=cons.owner
  5    and cons.owner='OWNER'
  6    and cons.table_name='TABLE';

no rows selected

Elapsed: 00:00:00.00
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.02
SQL> select ref.owner, ref.constraint_name,ref.table_name
  2    from  all_constraints ref, all_constraints cons
  3    where ref.r_constraint_name=cons.constraint_name
  4    and ref.r_owner=cons.owner
  5    and cons.owner='OWNER'
  6    and cons.table_name='TABLE';

no rows selected

Elapsed: 00:00:03.94
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.01
SQL> select /*+ RULE */ ref.owner, ref.constraint_name,ref.table_name
  2    from  all_constraints ref, all_constraints cons
  3    where ref.r_constraint_name=cons.constraint_name
  4    and ref.r_owner=cons.owner
  5    and cons.owner='OWNER'
  6    and cons.table_name='TABLE';

no rows selected

Elapsed: 00:00:00.27
SQL> select /*+ RULE */ ref.owner, ref.constraint_name,ref.table_name
  2    from  all_constraints ref, all_constraints cons
  3    where ref.r_constraint_name=cons.constraint_name
  4    and ref.r_owner=cons.owner
  5    and cons.owner='OWNER'
  6    and cons.table_name='TABLE';

no rows selected

Elapsed: 00:00:00.16

I also gathered statistics  on the SYS objects using

SQL> exec dbms_stats.gather_schema_stats('SYS',options=>'GATHER', -
> estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
> method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

PL/SQL procedure successfully completed.

Elapsed: 00:03:41.58

but that didn't help.

On 11g systems (11.2.0.4.0) the query without hint performs far better, but even there the hint accelerates the query

SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.01
SQL> select  ref.owner, ref.constraint_name,ref.table_name
  2    from  all_constraints ref, all_constraints cons
  3    where ref.r_constraint_name=cons.constraint_name
  4    and ref.r_owner=cons.owner
  5    and cons.owner='OWNER'
  6    and cons.table_name='TABLE';

no rows selected

Elapsed: 00:00:00.65
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.01
SQL> select  ref.owner, ref.constraint_name,ref.table_name
  2    from  all_constraints ref, all_constraints cons
  3    where ref.r_constraint_name=cons.constraint_name
  4    and ref.r_owner=cons.owner
  5    and cons.owner='OWNER'
  6    and cons.table_name='TABLE';

no rows selected

Elapsed: 00:00:00.66
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.01
SQL> select /*+ RULE */ ref.owner, ref.constraint_name,ref.table_name
  2    from  all_constraints ref, all_constraints cons
  3    where ref.r_constraint_name=cons.constraint_name
  4    and ref.r_owner=cons.owner
  5    and cons.owner='OWNER'
  6    and cons.table_name='TABLE';

no rows selected

Elapsed: 00:00:00.18
SQL>


Best Isolation Level to avoid deadlocks in Sql Server

I would recommend to rewrite this code to avoid the deadlocks.  In your case:

p1:

set transaction level L1
    delete from t1 where id=2
    select * from t1 where id=3

p2:

set transaction level L2
    select * from t1 where id=2
    delete from t1 where id=3


Creating a function for a simple select statement?

You could write an inline table valued function, or use a view to encapsulate this code, but it would depend on what you hope to get out of it.

There's no performance gain by doing so. It can make the application query simpler, or obfuscate table names, but that's about it.

Obfuscating table names may provide an additional, though minimal layer of security. Any escalation of privileges could result in someone seeing the view definition and exposing the table names anyway.


Code generators are great! Code generators are evil!

10-15 years ago, I would have said that having a code generator for quickly creating the boiler plate code for database driven applications would have been a great gift to mankind.

5-10 years ago, I would have said code generator sucks, they generate too much duplicate code and that having a data-driven user interface (where the fields on the screen, validation, etc is driven by meta-data in a database instead of coding the screens one by one) would have been a great gift to mankind that supplanted the code generators.

Today I would say - write each screen individually. Use existing framework that wire fields and model objects and possibly ORM when doing simple CRUD. But do design each screen to the exact purpose of the screen. Application screens that mirror a RDMS table too much is only good for managing lookup tables. Don't annoy the user with geeky interface that are designed against a computer storage model (RDMS)... make a screen that has only what they need. That may mean that it will save to multiple tables, etc. Who cares. The user isn't a database.

So my thought? Don't waste your time making a code generator.


SQL server 2012 service pack installation

The service packs are cumulative, so sp2 includes any pieces from sp1 that were not subsequently updated in sp2.


Postgres won't shut down due to wal archiving

The error message FATAL: the database system is shutting down is probably due  to new incoming connections that are denied because of the shutdown state of the master process.

According to Shutting Down the Server in the documentation, for a fast shutdown, the existing server processes have received a SIGTERM signal to ask them to terminate as soon as possible.

Yet there is this process:

postgres 14306 14266  9 May28 ?        04:01:55 postgres: opera_man om 10.40.227.146(44745) SELECT

that does not want to quit.

You may try the next option, sending SIGQUIT to the master process (pid 14266). This is likely to make it quit and it remains to be seen whether the subprocess with pid 14306 will quickly abort or stay stuck.

It it's stuck, I would kill it with SIGKILL as the last resort and then restart postgres normally with the init script.


MySQL select all records unless user must be present in favorites

I made a simple solution to my problem with the following condition;

CASE
    WHEN items.favorite_only = 1 THEN
    (SELECT favorites.user_id FROM favorites WHERE favorites.owner_id = items.owner_id AND favorites.user_id = 1 LIMIT 1)
    WHEN items.favorite_only = 0 THEN 1
END

Is there performance wise a better solution?


How to query the names of encrypted stored procedures?

SELECT name FROM sys.procedures
WHERE OBJECTPROPERTY([object_id], 'IsEncrypted') = 1;


What exactly happens when optimizer_prune_level = 1?

EAV (Entity Attribute Value) schema may benefit from ..level=1.  This is because of messy WHERE clauses that have lots of essentially the same clauses

WHERE a1.e = 123 AND a1.attr = 'color' AND a1.val = 'red'
  AND a2.e = 123 AND a2.attr = 'shape' AND a2.val = 'round'
  AND a3.e = 123 AND a3.attr = 'size'  AND a3.val = 'big'
  AND a4.e = 123 AND a4.attr = 'style' AND a4.val = 'xyz'

Normally, the Optimizer would try a large number of different orders to look at the table.  In reality, they are equally efficient, setting the parameter to 1 keeps the Optimizer from wasting effort.

In your example, the WHERE clauses should be in ON clauses since they describe how the tables are related.  And your example has no WHERE clause for filtering.

Without filtering, a JOIN of 3 tables with sufficient indexes can be done by starting with any of the tables.  For 3 tables, there would be 6 (3 factorial) orderings.  For 10 tables, it would be millions.  But, again, the Optimizer should not waste its time trying all combos.

If you tack a simple WHERE on for filtering, the Optimizer will quickly gravitate toward that table as being the best to start with, and the "level" won't come into play as much.


How can I add a rowversion column to a large table with minimal downtime

Consider creating a new table with the same schema plus the rowversion column, and add a view atop both tables that does a union all. Have people use the view, and write instead-of triggers against the underlying tables & views.

Inserts should be sent to the new table, updates should move data to the new table, and deletes should be applied to both tables.

Then do batch moves in the background, moving as many records at a time as you can over to the new table. You can still have concurrency issues while this is going on, and some craptacular execution plans, but it lets you stay online while the moves are happening.

Ideally, you start the process on a Friday afternoon to minimize the effect on end users, and try to get it done before Monday morning. Once it's in place, you can change the view to point to just the new table, and the craptacular execution plans go away. Ideally.

To avoid the triggers firing when the data is being migrated in batches, look at the number of rows in the deleted/inserted tables in the trigger, and skip the activities if they're near to the number of rows in your batch.

In the finish, Michael decided to skip the view (and not delete from the original table) to get more stable plans. The trade off was holding essentially two copies of the table. He turned it into a series of blog posts.


How to select users that doesn't in the role

SELECT ...
  FROM users u
  LEFT JOIN userroles ur ON ur.user_id = u.user_id AND ur.role_id = 2
 WHERE ur.role_id IS NULL
 ORDER BY u.user_id ASC;

Examine all rows of user, and their matching row from userroles with a role_is = 2 if it exists, eliminating the rows where it does indeed exist by requiring the role_id in the (non-existent) row to be null... which, since the row does not exist, is indeed null.

Alternately,

SELECT ...
  FROM users u
 WHERE NOT EXISTS (SELECT * FROM userroles ur WHERE ur.user_id = u.user_id AND ur.role_id = 2)
 ORDER BY u.user_id ASC;

Note that the SELECT * in the subquery doesn't actually select everything, it's just a way to express the test of whether such a row exists.


So after working through the info that Phil gave me.. and doing some research after the fact..

It appeared that the Set Until with a future date screwed things up by "removing, or de-cataloging my backups".  Even if that's possible (I don't know enough about Oracle to say) it wasn't the case.

What had happened was that my initial Full Backups were gone due to some poor data-retention.  So when I put a future date, it appeared like my PITR would work because it had a Full backup from a day ago.  However, I never did have a Full backup from a week ago.     I had taken one a couple months ago. but it had been deleted.

I'm using EM Console for my backups and haven't yet figured out how to tell it to do incrementals every day and fulls once a week.  I'll get there though.

Thanks for all the help.


Questions about Free Space Map of PostgreSQL

This will contain some PostgreSQL internals; sorry, you asked for it.
Autovacuum doesn't run on temporary tables (because they are invisible outside your session), and at any rate, one or two updates won't trigger autovacuum. Now it is VACUUM that creates and maintains the free space map, so there is no free space map in your case. pg_freespacemap does not error out, but reports that as 0.
Lacking a free space map, PostgreSQL has to look at the individual blocks to find a block with free space in it. That leaves the riddle why the second update could find space to create a tuple in block 0.
To answer the riddle, let's discuss your statements and their effect. You can verify all this using the pageinspect extension.

the first UPDATE:
UPDATE test SET value = -1 WHERE id = 1;

That marks tuple (0,1) as updated (it sets xmax) and creates the new tuple (1,26), just as you expected.

the following query:
SELECT ctid, * FROM test WHERE value <> 0;

This query can get a page lock on block 0 and performs heap pruning, that is a “micro-vacuum” that frees the space occupied by dead tuples. Note that it cannot remove (0,1), because it is still referenced in an index, but line pointer (0,1) becomes a “stub” marked LP_DEAD that does not occupy any space. The space occupied by that dead tuple is freed.
Note that if you don't run that SELECT, the following UPDATE will perform heap pruning on block 0, so the effect will be the same.

the second UPDATE:
UPDATE test SET value = -1 WHERE id = 2;

Lacking a free space map, the statement looks a block 0 and notices that there is enough free space, so it performs a HOT update and puts the new tuple into block 0.


UTL_TCP how to monitor number of connections?

UTL_MAIL strongly rely on TCP (Network) and the number of active TCP connections that can be open for a session is 16, per documented Bug 8262564.  Bug 8262564 got fixed in Oracle 11g 11.2.0.1 or later and also on Oracle 11g 11.1.0.7 as one-off patches.  Bug 8262564 is quoted as reference to state that the number of active TCP connections that can be open for a session is 16.  While UTL_MAIL does not have restrictions but the TCP that serves as the base for Mail related packages such as UTL_MAIL and UTL_SMTP does have an active session limit of 16.

You say: "the number of UTL_SMTP connections being made and closed". Are you sure about the closing part? When you open a mail connection, you have to explicitly close it:

--SMTP example
declare
my_conn utl_smtp.connection;

begin
-- open smtp connection
my_conn := utl_smtp.open_connection(
host => 'www.testlink.com',
port => 25);

-- Explicitly close the connection oppened
utl_smtp.quit(c => my_conn);

exception
when others then
raise_application_error (-20002,'An error has occurred.');

end;

Please, note that If UTL_MAIL throws an exception and emails are resend from the exception, the smtp connections are lift open and after 15 successive exceptions UTL_MAIL throws errors.
Connection is getting closed only in those cases when UTL_MAIL.SEND succeeds without any error. Since everything related to a connection is done within the UTL_MAIL package it is not possible to close the connection from outside the package.
I do not know a v$ to count open smpt/tcp connections for an Oracle session.
I think you can do it by yourself, for example using the following code:

set serveroutput on
alter session set smtp_out_server='<smtp_server_name:25>';

declare
cnt number := 1;
cnt1 number;
begin
for i in 1..20 loop
begin
UTL_MAIL.send('valid_email@domain.com','invalid_email@domain.com',NULL,NULL,'testing invalid email','testing invalid email' );
Exception
When Others then
UTL_MAIL.send('valid_email@domain.com','valid_email@domain.com',NULL,NULL,'send from exp block','send from exp block');
dbms_output.put_line(cnt);
cnt := cnt + 1;
end;
end loop;
End;
/

I found this code on Oracle Metalink knowledge base. After each error, you increase a counter to monitor the number of open connections.
Regards
Giovanni


I think this query does what you are asking. The sub select gets the counts for each id. I then joined it back to t1. The outer query gets the max for each id. I didn't put the WHERE clause since I don't know the criteria but you can easily add it.

Here's a link to SQL Fiddle

select id,
  class,
  counts,
  max(numOcc) as Occ
from t1
left join
(SELECT event_id, reconstruction_algorithm, count(1) as numOcc from t2
  group by event_id, reconstruction_algorithm) a
on t1.id = a.event_id
group by t1.id, t1.class, t1.counts


What's the easiest and most accurate way to visualize parallel thread usage in SQL Server?

I've tried using a query like this, but the number of "running" threads can exceed DOP sometimes.

The join from dm_os_tasks to dm_os_workers is one to one, because a worker thread runs exactly one task. The join from tasks/workers to dm_exec_query_profiles is one to many, because each thread may run multiple plan nodes.
You need to look at the number of unique tasks/execution contexts/schedulers that are associated with RUNNING status:
SELECT
    COUNT(DISTINCT DOT.scheduler_id)            -- or task_address / exec_context_id
FROM sys.dm_os_tasks AS DOT
WHERE
    DOT.task_state = N'RUNNING'
    AND DOT.session_id = 72                     -- specify the session id
    AND DOT.parent_task_address IS NOT NULL;    -- additional workers only

I've also tried using the query_thread_profile Extended Event, but for a plan with multiple parallel branches, it shows the same N number of Thread IDs.

The extended event description for this debug channel event is imprecise:

The documentation for sys.dm_exec_query_profiles gives a better description of the thread_id:

Distinguishes the threads (for a parallel query) belonging to the same query operator node.

Within each branch, each thread is given an ID, similar to the per-thread breakdown in showplan. At DOP x, there will be thread ids from 1 to x for each branch, but it is simply a sequential number (think row number).
You can tell the threads apart in the extended event by collecting task/worker/system thread id. If you are interested in thread ids for the whole executing plan, the best proxy for that is execution_context_id. Each execution context is associated with exactly one thread.
Visualization
Visualization of parallel execution can be done in many different ways, depending on which particular aspect is under investigation. The DMVs can be joined and ordered in many ways.
For example, the following shows what each thread did most recently, and what, if anything, it is currently waiting on:
DECLARE @session_id smallint = 72;

SELECT
    DOT.task_state,
    DOT.scheduler_id,
    DOT.exec_context_id,
    DEQP.physical_operator_name,
    DEQP.node_id,
    DEQP.thread_id,
    DEQP.row_count,
    DOWT.wait_duration_ms,
    DOWT.wait_type,
    DOWT.resource_description
FROM sys.dm_os_tasks AS DOT
OUTER APPLY
(
    -- What each thread did most recently
    SELECT *
    FROM sys.dm_exec_query_profiles AS DEQP
    WHERE
        DEQP.session_id = DOT.session_id
        AND DEQP.request_id = DOT.request_id
        AND DEQP.task_address = DOT.task_address
    ORDER BY
        DEQP.last_active_time DESC
    OFFSET 0 ROWS FETCH FIRST 1 ROW ONLY
) AS DEQP
OUTER APPLY
(
    -- Longest wait, if any
    SELECT *
    FROM sys.dm_os_waiting_tasks AS DOWT
    WHERE
        DOWT.session_id = DOT.session_id
        AND DOWT.exec_context_id = DOT.exec_context_id
    ORDER BY
        DOWT.wait_duration_ms DESC
    OFFSET 0 ROWS FETCH FIRST 1 ROW ONLY
) AS DOWT
WHERE
    DOT.session_id = @session_id
    --AND DOT.task_state = N'RUNNING'
ORDER BY
    DEQP.node_id,
    DOT.exec_context_id;

Example output for the demo query:


Parameter value in Data Factory Triggered pipeline

Finally I was able to fix this by creating the trigger using a JSON code as shown below:

{
    "name": "yourTriggerName",
    "properties": {
        "runtimeState": "Started",
        "pipelines": [
            {
                "pipelineReference": {
                    "referenceName": "YourPipelineName",
                    "type": "PipelineReference"
                },
                "parameters": {
                    "windowStart": "@trigger().scheduledTime"
                }
            }
        ],
        "type": "ScheduleTrigger",
        "typeProperties": {
            "recurrence": {
                "frequency": "Day",
                "interval": 1,
                "startTime": "2018-07-11T17:00:00Z",
                "timeZone": "UTC",
                "schedule": {
                    "minutes": [
                        20
                    ],
                    "hours": [
                        19
                    ]
                }
            }
        }
    }
}

And making emphasis of course, in line below:

"parameters": {
                        "windowStart": "@trigger().scheduledTime"

After that, the copy activity started to work as expected.


Is inserting into a table with a foreign key constraint slower than inserting without constraint?

INSERT (etc) must check that there are no FOREIGN KEY violations.  Hence it is slower.  Think of it as checking to see if an entry is in an index.
The actual cost depends heavily on whether the block of the other index is currently cached, which depends on size of entire dataset, workingset size, speed of disk, etc. I have not encountered a case where FKs made the difference between the system being 'viable' versus 'too slow'.


Calling stored procedure with select and update statement

You can do this in a single statement using the OUTPUT clause:

UPDATE dbo.Table
SET Retrieved = 'Y'
OUTPUT inserted.x, inserted.y, inserted.z;

If you don't really mean to update the entire table every time:

UPDATE dbo.Table
SET Retrieved = 'Y'
OUTPUT inserted.x, inserted.y, inserted.z
WHERE <...where clause...>;


@sqlband I can confirm that this a bug ( or feature) of SSMS 2016.

I was able to reproduce this in SSMS version Microsoft SQL Server Management Studio    13.0.16106.4.

USE [master]
GO

CREATE LOGIN [ssmsspeed] WITH PASSWORD=N'ssmsspeed', DEFAULT_DATABASE=[WideWorldImporters], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [WideWorldImporters]
GO
CREATE USER [ssmsspeed] FOR LOGIN [ssmsspeed]
GO
USE [WideWorldImporters]
GO
ALTER ROLE [db_owner] ADD MEMBER [ssmsspeed]
GO

Now connecting to SSMS using ssmsspeed login. When I expand the database node it takes about 10 seconds to expand as explained by OP. I can see this in my error log and in my security audit file.

  Message Login failed for user 'ssmsspeed'. Reason: Failed to open the
  explicitly specified database 'AdventureWorks2014'. [CLIENT: ]

  Error: 18456, Severity: 14, State: 38.

If I list my user databases order by name AdventureWorks2014 is the first one.

AdventureWorks2014
GMODBA
hacked
test
Warehouse
WideWorldImporters

I also tested this by making Master database as Default_database but same result.

USE [master]
GO

CREATE LOGIN [ssmsspeed1] WITH PASSWORD=N'ssmsspeed1', DEFAULT_DATABASE=[Master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [WideWorldImporters]
GO
CREATE USER [ssmsspeed1] FOR LOGIN [ssmsspeed1]
GO
USE [WideWorldImporters]
GO
ALTER ROLE [db_owner] ADD MEMBER [ssmsspeed1]
GO

When I do the same test in Microsoft SQL Server Management Studio  12.0.4100.1 I cannot reproduce this. Expanding database node is instantaneous and no login fail message in error log.


Modify Backup Directory Path Ola Maintenance Script

You can search the command column of the sysjobsteps table for the path you have in your current jobs with the following statement:

SELECT * FROM msdb.dbo.sysjobsteps AS s WHERE s.command LIKE '%\\comp1\path%'

You should then be able to update the sysjobsteps with the following statement:

USE msdb
GO
UPDATE sysjobsteps SET command = REPLACE(command, '<your_old_path_here>', '<your_new_path_here>')

You have to be careful to run the SELECT first to check if the correct commands are being returned. You could also backup the msdb before you start as a precaution.

To be on the safer side, you can right click a job and "Script Job As | Drop and Create To | New Query Editor Window" and then modify the path and execute.

Be sure to remove the line containing @schedule_uid=N'<GUID>' in the sp_add_jobschedule part before you attempt to recreate the job.


You can use an audit plugin, such as MariaDB's audit plugin which also works for MySQL, see:
https://www.percona.com/blog/2016/02/15/mysql-mariadb-with-mariadb-auditing-plugin/

For MariaDB's plugin, you can configure it to log DCL queries only, which should cover CREATE/DROP PROCEDURE.


Store the resulting list of a SELECT in a variable to count AND use the result

If you want to put a query result in a table variable and iterate through the rows that can be easily done. Be careful with your loops of course.

DECLARE @Value int, @Label varchar(25)
DECLARE @Tmp table (value int, label varchar(25))

INSERT INTO @Tmp (value, label)
    SELECT 1, 'One'
UNION SELECT 2, 'Two'
UNION SELECT 3, 'Three'

WHILE (SELECT count(1) FROM @Tmp) > 0
BEGIN
    SELECT TOP 1 @Value = value, @Label = label FROM @Tmp ORDER BY value
    DELETE FROM @Tmp WHERE value = @Value
    SELECT @Value 'value', @Label 'label'
END


I'd say the easiest possible way to do this is through PowerShell and SMO.  Take the following code for instance:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |
    Out-Null

$SqlServerNames = "Server1", "Server2"
$SqlJobName = "YourJob"

foreach ($SqlServerName in $SqlServerNames) {
    $SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

    $SqlServer.JobServer.Jobs[$SqlJobName].Start()
}

All this does it loop through a list of servers (explicitly stated at as string array variable, but can easily be obtained also from a SQL Server database table, a text file, etc.), connects to the current server, and then starts the job with the job named whatever you set the $SqlJobName variable to.

It's also worth noting that the Start() function will not wait for the job to complete before continuing code execution.

As you can see, in very few lines of PowerShell code you are able to accomplish this task.  But I'd take it a step further, such as error handling.  You don't want this to bomb out and not know what failed, when it failed, and what servers it did/didn't run on.

foreach ($SqlServerName in $SqlServerNames) {
    try {
        $SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

        $SqlServer.JobServer.Jobs[$SqlJobName].Start()
    }
    catch {
        # add your error handling code here
        ## for instance, write the error to a text file
    }
}

And, as always with any code, test this out in a non-production environment to ensure that it does what you think it will and should do.  Re-work it for your needs/environment.

As per RoKa's comment:

  Can this be edited to check for whether the job is currently running, error handling?

Great point, and definitely something to check for (as well as if the job actually exists by checking for a non-null value):

foreach ($SqlServerName in $SqlServerNames) {
    try {
        $SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

        $SqlJob = $SqlServer.JobServer.Jobs[$SqlJobName]

        if (($SqlJob) -and ($SqlJob.CurrentRunStatus -eq [Microsoft.SqlServer.Management.Smo.Agent.JobExecutionStatus]::Idle)) {
            $SqlJob.Start()
        }

        else {
            # handle/log accordingly if the job doesn't exist or it's not idle
        }

    }
    catch {
        # add your error handling code here
        ## for instance, write the error to a text file
    }
}

EDIT: While re-reading your question, I see SQL Server 2000. Are those going to be the target instances? If that is the case, and you do decide to go with the PowerShell/SMO answer I provided, then definitely definitely definitely test and ensure it is going to do what you want.  Test this out very far from production and be 100% sure it does what you think it will.

EDIT: It looks like SMO versions before SQL Server 2012 (i.e. 2005, 2008, and 2008 R2) support managing SQL Server 2000 instances.  I'm finding this information through this BOL reference.


What does the word "SARGable" really mean?

The term "sargable" was first introduced by P. Griffiths Selinger et al. in their 1979 paper "Access Path Selection
in a Relational Database Management System", published by ACM. For non-ACM members there's a copy of that paper at http://cs.stanford.edu/people/chrismre/cs345/rl/selinger.pdf

The term is defined in this paragraph:

  Both index and segment1 scans may optionally take a set of predicates, called search arguments (or SARGS), which are applied to a tuple before it is returned to the RSI2 caller. If the tuple satisfies the predicates, it is returned; otherwise the scan continues until it either finds a tuple which satisfies the SARGS or exhausts the segment or the specified index value range. This reduces cost by eliminating the overhead of making RSI calls for tuples which can be efficiently rejected within the RSS. Not all predicates are of the form that can become SARGS. A sargable predicate is one of form (or which can be put into the form) "column  comparison-operator  value". SARGS are expressed as a boolean expression of such predicates in disjunctive normal form.

In other words, a sargable predicate is such that can be resolved by the storage engine (access method) by directly observing the table or index record. A non-sargable predicate, conversely, requires a higher level of the DBMS to take action. For example, the outcome of WHERE lastname = 'Doe' can be decided by the storage engine by simply looking at the contents of the field lastname of each record. On the other hand, WHERE UPPER(lastname) = 'DOE' requires execution of a function by the SQL engine, which means the storage engine will have to return all rows it reads (provided they match possible other, sargable predicates) back to the SQL engine for evaluation, incurring additional CPU costs.

You can see from the original definition that sargable predicates can apply not only to index scans, but also to table (segment in System R terminology) scans, as long as the conditions "column comparison-operator value" are met and therefore they can be evaluated by the storage engine. This is indeed the case with Db2, a descendant of System R in many ways:

  Index sargable predicates are not used to bracket a search, but are evaluated from the index if one is chosen, because the columns involved in the predicate are part of the index key. These predicates are also evaluated by the index manager.

  Data sargable predicates are predicates that cannot be evaluated by the index manager, but can be evaluated by Data Management Services (DMS). Typically, these predicates require the access of individual rows from a base table. If necessary, DMS will retrieve the columns needed to evaluate the predicate,

The fact that in SQL Server-speak sargable predicates are only those that can be resolved using index seeks is probably determined by its storage engine's inability to apply such predicates during table scans.

Sargable and non-sargable predicates are sometimes described as "stage 1" and "stage 2" predicates respectively (this also comes from Db2 terminology). Stage 1 predicates can be evaluated at the lowest level of query processing, while reading table or index records. Rows that match stage 1 conditions, if any, are sent to the next level, stage 2, of evaluation.

1 -- Segment in System R is the physical storage of a table's tuples; a segment scan is somewhat equivalent to a table scan in other DBMSes.

2 -- RSI -- RSS3 Interface, a tuple-oriented query interface. The interface function relevant to this discussion is NEXT, which returns the next row matching query predicates.

3 -- RSS, or Research Storage System, the storage subsystem of System R.


Job management: How to get the next running date by querying MSDB

You can use the (documented) stored procedure sp_help_job to get the next execution time, however progratically capturing the results can be difficult due to the limitation around nested INSERT...EXEC calls. As an alternative, many folks use the (undocumented) stored procedure xp_sqlagent_enum_jobs which captures much of the same info.
Like you, I prefer to capture the data from the base tables directly using an alternate approach that I wrote about in a blog post, and have published a T-SQL function on GitHub as part of my DBA Database.
The table-valued function (TVF) definition itself is just a single SELECT statement that takes a job name, and returns a bunch of stuff, including last & next run time, and whether the job is currently running:
SELECT TOP 1
    IsRunning = CASE WHEN ja.job_id IS NOT NULL AND ja.stop_execution_date IS NULL THEN 1 ELSE 0 END,
    LastRunTime = ja.start_execution_date,
    NextRunTime = ja.next_scheduled_run_date,
    LastJobStep = js.step_name,
    JobOutcome = CASE
                    WHEN ja.job_id IS NOT NULL AND ja.stop_execution_date IS NULL THEN 'Running'
                    WHEN run_status = 0 THEN 'Failed'
                    WHEN run_status = 1 THEN 'Succeeded'
                    WHEN run_status = 2 THEN 'Retry'
                    WHEN run_status = 3 THEN 'Cancelled'
                END
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobactivity ja
    ON ja.job_id = j.job_id
    AND ja.run_requested_date IS NOT NULL
    AND ja.start_execution_date IS NOT NULL
LEFT JOIN msdb.dbo.sysjobsteps js
    ON js.job_id = ja.job_id
    AND js.step_id = ja.last_executed_step_id
LEFT JOIN msdb.dbo.sysjobhistory jh
    ON jh.job_id = j.job_id
    AND jh.instance_id = ja.job_history_id
WHERE j.name = @JobName
ORDER BY ja.start_execution_date DESC;

Because it’s a TVF, you can pass a value directly to it and get a result set back:
SELECT *
FROM dbo.SqlAgentJob_GetStatus('Test Job');

Or APPLY it across a table for a set of results (in this case I’m getting all job in job_category 10):
SELECT sts.*
FROM msdb.dbo.sysjobs j
CROSS APPLY dbo.SqlAgentJob_GetStatus(j.name) sts
WHERE j.category_id = 10;

Or get a list of all SQL Agent jobs that are currently running:
SELECT sts.*
FROM msdb.dbo.sysjobs j
CROSS APPLY dbo.SqlAgentJob_GetStatus(j.name) sts
WHERE sts.IsRunning = 1;

What about sysschedules?
You'll notice that my code doesn't use sysschedules, because it just isn't necessary.
If you wanted to make use of sysschedules in code, you would do something like this, joining from sysjobs to sysjobschedules to sysschedules. Keep in mind that jobs can have zero to many schedules.
SELECT JobName      = j.name,
       ScheduleName = s.name,
       ...other columns...
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.dbo.sysjobschedules AS js
     ON j.job_id = js.job_id
LEFT JOIN msdb.dbo.sysschedules AS s
     ON js.schedule_id = s.schedule_id;

To determine the "next run date/time" you would need to essentially identify active schedules for the job, and take the next run date out of multiple schedules. sysjobactivity does that heavy lifting for you by computing the next run date (you can think of it as the "actual next execution")--but it simply doesn't keep track of which schedule is responsible for that execution. If that matters to you, you'll need to hunt that down yourself by looking at what schedules are active for that job.


How to run mysql_upgrade on mysql workbench

mysql_upgrade is a shell script and does not have a GUI interface from MySQL Workbench. Run it from the command line (depending on your operating system).


Finding the odd row between two tables in Oracle

One-to-one based on what?

You have no reliable and permanent way of identifying/sorting rows in TableA. You have A03, Pineapple twice. Which will be the 3rd and which will be the 4th?

Sure, you can do the below, but forget this right away and fix your data. Rowids can change when moving data so the order is never guaranteed.

create table tablea(id varchar2(3 char), fruit varchar2(9 char));
create table tableb(id varchar2(3 char), fruit varchar2(9 char));

insert into tablea values ('A01', 'Apple');
insert into tablea values ('A02', 'Orange');
insert into tablea values ('A03', 'Pineapple');
insert into tablea values ('A03', 'Pineapple');
insert into tablea values ('A05', 'Banana');

insert into tableb values ('A01', 'Apple');
insert into tableb values ('A02', 'Orange');
insert into tableb values ('A03', 'Pineapple');
insert into tableb values ('A04', 'Pineapple');
insert into tableb values ('A05', 'Banana');

commit;

select
  a.id ida, a.fruit fruita,
  b.id idb, b.fruit fruitb
from
  (select t.*, row_number() over (order by id, rowid) as rn from tablea t) a
join
  (select t.*, row_number() over (order by id, rowid) as rn from tableb t) b
on (a.rn = b.rn and (a.id != b.id or a.fruit != b.fruit));

IDA FRUITA    IDB FRUITB
--- --------- --- ---------
A03 Pineapple A04 Pineapple


1 - What message do you get when you try to restart mysql ? : /etc/init.d/mysql restart

2 - Verify if the /var/run/mysqld directory is owned by mysql, if it's not th case, create a new directoy and assign it to mysql :

sudo mkdir /var/run/mysqld
sudo chown mysql:mysql mysqld

And restart the mysqld daemon :

sudo mysqld_safe


SQL Server: Database integration and synchronization

If your update/insert always happens on a single one of the n databases AND you read a low number of rows on each request, you can use Linked Servers and Distributed Partitioned Views (DPV) to create the illusion of a single database: http://technet.microsoft.com/en-us/library/ms188299(v=sql.105).aspx. This can be done without introducing the complexity of replication.

DPV have several limitations and may give you poor performance if you are not careful. Without knowing more details of the queries you want to run - I can only advise that you try it and see if solves your problem.


Can I physically split a SQL Server database?

Yes, is possible to move old data to filegroups that are still part of the database, but store on files located on cheaper, slower, storage, adnd mark these file groups as read-only. You still need to do a full database backup once, but subsequent backups can leave the read-only file groups out of the backup, significantly reducing the size (and time!). I recommend you read Performing Piecemeal Restores.

Another useful feature is partitioning, which allow you to split a table automatically into partitions and place partitions onto this slow, untouched and read-only file groups. See How to Implement an Automatic Sliding Window in a Partitioned Table.


How do I limit the resources that pg_dump uses?

My assumption is that RAM isn't your problem. But that you're so IO-bogged down that you can't write to a file as a quick as you can read from the database.

There is no solution here, pg_dump is made it work as fast as it can. If it can read data faster than it can write it, your ram usage may go up. Until the table is flushed to disk. You may want to enable compression, see man psql and look for -Z,

   -Z 0..9
   --compress=0..9
       Specify the compression level to use. Zero means no compression. For the custom archive format, this specifies compression of individual table-data
       segments, and the default is to compress at a moderate level. For plain text output, setting a nonzero compression level causes the entire output file
       to be compressed, as though it had been fed through gzip; but the default is not to compress. The tar archive format currently does not support
       compression at all.

As far as I know, processes don't know how much ram a system has or how much they should take. Normally, setting a limit just does what happened here and kills the process. If it didn't kill the process, they process may just keep drinking ram until it starts paging, and then beyond.


Perhaps you can use some user defined variables. I once wrote an answer to this : Disable trigger for just one table. I suggested setting up a user defined variable to disable a trigger.

In your case, dynamic SQL is not necessary if you are simply changing parameter values for a SQL statement. If you are changing table names and column names for the query, you got to construct some good IF...THEN blocks in the trigger like my post demonstrates. If there are too many tables or too many column names to make IF...THEN blocks, then stick with the Stored Procedure approach.


Add additional disks to FRA diskgroup in ASM

This assumes that you've already partitioned the presented disk(s) (and will be using /dev/sd[whatever]N), and that you're using asmlib. There will be a kernel module loaded if you are:

[root@oel61 disks]# lsmod | grep oracle
oracleasm              53865  1
[root@oel61 disks]#

As root, scan for candidate disks:

[root@oel61 ~]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@oel61 ~]#

Then, check to see if the disk has already been "discovered" by ASM:

oracle@oel61 ~]$ asmcmd -p
ASMCMD [+] > lsdsk
Path
/dev/oracleasm/disks/DISK1
/dev/oracleasm/disks/DISK2
/dev/oracleasm/disks/DISK3
ASMCMD [+] >

If not, we need to stamp the device:

[root@oel61 ~]# /etc/init.d/oracleasm createdisk NEWFRA /dev/sdc1
Marking disk "NEWFRA" as an ASM disk:                       [  OK  ]
[root@oel61 ~]#

Scan for candidate disks again, then list - the new device should be there:

[root@oel61 ~]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@oel61 ~]#

# /etc/init.d/oracleasm listdisks
DISK1
DISK2
DISK3
NEWFRA
#

Or use asmcmd:

oracle@oel61 ~]$ asmcmd -p
ASMCMD [+] > lsdsk
Path
/dev/oracleasm/disks/DISK1
/dev/oracleasm/disks/DISK2
/dev/oracleasm/disks/DISK3
/dev/oracleasm/disks/NEWFRA
ASMCMD [+] >

Now do a scandisks on the other node and check that everything is as it is on the first node (should be fine if you're using the same /dev device names).

Now the disk is ready to be added to the group.

List the groups:

[oracle@oel61 ~]$ export ORACLE_SID="+ASM"
[oracle@oel61 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jan 31 15:35:27 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Automatic Storage Management option

SQL> select name
  2  from V$ASM_DISKGROUP;

NAME
------------------------------
DATA
FRA

SQL>

Add disk to the group:

SQL> ALTER DISKGROUP FRA ADD DISK '/dev/oracleasm/disks/NEWFRA';


quickly jump to an object in Object Explorer in SSMS

Try using the Object Explorer Details window.

It's the second option under the View menu or F7 to open it.  Once there you can just select the category in Object Explorer you want to look at (tables for example) and then in the OE Details window you can just type the name of the table.  As an added bonus you can sort by name, schema, create date and a number of other really useful columns.


Why would a job call a batch file with dtexec.exe instead of just calling the SSIS package directly?

Before SQL Server 2012 and the project deployment model, the only way to run an SSIS package from the context of SSMS was either a SQL Agent job or xp_cmdshell.

SQL Agent is calling DTEXEC.exe as it has a special parameter /X86 that is only respected when passed from SQL Agent. Otherwise, you'll want to specify the explicit path to your dtexec.exe to control whether it's a 32 vs 64 bit operation.

  This option is only used by SQL Server Agent. This option is ignored if you run the dtexec utility at the command prompt.

Option A

That could be a reason why they use a batch file to kick off the SSIS packages. They want to ensure the correct dtexec is being used to run the package. Shamless self promotion on determining whether it's 32 vs 64 http://billfellows.blogspot.com/2015/02/is-my-ssis-package-running-in-32-or-64.html Also https://stackoverflow.com/a/13092260/181965

Option B

Another reason I've seen batch scripts used as they didn't use configuration (file, table, whatever) but wanted to get a different behaviour per environment. Assuming I had a ConnectionString variable in my SSIS package and the design time value was Dev but now I'm in production, one way to change that value is to always set the property when we run, psuedocode is

dtexec.exe /file MyPackage /SET ConnectionString;Prod

If I'm lazy, I could use the same job definition of across all my environments but change the contents of the /SET value as need be. I don't see it being that useful but some people view tasks with varying degrees of difficulty.

The above doesn't have to be just for /SET. They can set logging or anything else up really. Maybe they want to log the output to an execution log because they didn't know SSIS supports that natively. There might be /REP EWIP > CurrentRun.txt in the dev batch file but production only have /REP E

Option C

Another reason would be the illusion of security. At a former employer, they used the batch script approach to run SSIS packages. Developers were not granted access to SQL Agent. xp_cmdshell - oh hell no to that. Nor could we be trusted to log onto the pre-production boxes (Load, stage, UAT) but we still had the need to run SSIS packages on demand.

So, after a few weeks of submitting urgent request tickets every time the packages need run, they created a pair of stored procedures that provided a gated package execution experience. RunSSISPackage and RunSSISPackage32 which would take the name of the package to be run. For every package we created, they would create a batch script that ran it. Lots of busy work for all parties but at the time it was the only way they were letting us run packages. The procedures used xp_cmdshell and EXECUTE AS to allow us to run the packages as they would be - with full sysadmin rights. By the way, when you allow people to deploy SSIS packages without auditing, they could add an Execute SQL Task in there that adds the developer group into the sysadmin role. I think. Maybe. Hypothetically.

Option D

Not SQL Agent. I've seen other scheduling tools used at clients that weren't SQL Agent. The support for SSIS specifically has been spotty but they've all supported batch files so perhaps you have some Enterprise scheduler that wasn't compatible with SSIS.

Otherwise, if it's just dtexec.exe /file foo.dtsx ... I got nothing


Update column with another table's value

When trying to create a situation that resembles the one you are describing, I have used the following DDL code (notice that there are no constraints), using Oracle 12c:

Test setup

create table users (
  email varchar2(32)
, name varchar2(32)
, sid varchar2(32) -- <- assumption: data type NOT number
);

create table user_details (
  username varchar2(32)  -- <- populated with a mix of ids and emails
, address varchar2(64)
, details varchar2(64)
);

After inserting some test data, the tables contain ...

SQL> select * from users;
EMAIL                  NAME    SID
user.mail1@domain.org  name_1  1
user.mail2@domain.org  name_2  2
user.mail3@domain.org  name_3  3

-- user_details: username contains "a mix of ID's and Email's"
SQL> select * from user_details;
USERNAME               ADDRESS    DETAILS
1                      address_1  details_1
user.mail1@domain.org             more details (user 1)
2                      address_2  details_2
user.mail2@domain.org             more details (user 2)
3                      address_3  details_3
user.mail3@domain.org             more details (user 3)
3                      ---        some more details (user 3)

Problems

When using your original UPDATE, we get "ORA-01732"

update
(select distinct
    u.sid as new_id,
    ud.username as id
from user_details ud
    inner join users u on
        lower(ud.username) = u.email) up set up.id = up.new_id ;
Error at Command Line : 2 Column : 1
Error report -
SQL Error: ORA-01732: data manipulation operation not legal on this view
01732. 00000 -  "data manipulation operation not legal on this view"

When running the same UPDATE, without DISTINCT, we get "ORA-01779"

update (
  select
    u.sid as new_id,
    ud.username as id
  from user_details ud
    inner join users u on lower(ud.username) = u.email
) up
set up.id = up.new_id ;
SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"
*Cause:    An attempt was made to insert or update columns of a join view which
           map to a non-key-preserved table.
*Action:   Modify the underlying base tables directly.

Let's see if we can do this with a subquery in SET ... (still no joy)

UPDATE user_details
SET user_details.username =
(
    SELECT DISTINCT users.sid AS new_id
    FROM users, user_details
    WHERE LOWER(user_details.username) = users.email
);
Error report -
ORA-01427: single-row subquery returns more than one row

Solution

Maybe you want to consider the following solution: leave the original user_details table as it is.  Create a new table by SELECTing everything you need from user_details.

create table userdetails_new
as
select
  u.sid       as new_id -- <- sid corresponds to an email address in users
, ud.address  as address
, ud.details  as details
from user_details ud
  join users u on lower(ud.username) = u.email
union
select
  username -- <- username that is NOT an email address
, address
, details
from user_details
where username not like '%@%' ;

The new "user details" table now contains ...

SQL> select * from userdetails_new;
NEW_ID  ADDRESS    DETAILS
1                  more details (user 1)
1       address_1  details_1
2                  more details (user 2)
2       address_2  details_2
3                  more details (user 3)
3                  some more details (user 3)
3       address_3  details_3

Now we can also add some constraints, such as:

alter table users add unique(sid);

alter table userdetails_new
add constraint fkey_userdetails
foreign key (new_id) references users (sid) ;

I'm sure that there are other solutions for this.  However, the suggested procedure allows us to keep the original "user_details" data as long as we want ie until we have checked that the update/transformation was successful and correct.
Dbfiddle here.


Best way to design a database and table to keep records of changes?

When designing versioning capabilities in your data, there are several minimal (I would think) requirements:

Each version of the data should be self-contained and independent of other versions. This means no flag or other indicator showing which is the current version and which are "history." It also means updating the entity means inserting a new version only -- no updating of previous versions needed.
Avoid what I call Row Spanning Dependency. That is where one field (End_Date) of a row must remain in synch with another field (Start_Date) of a different row. This makes working with the data more difficult and is an excellent source of anomalies.
The current version and all past versions should be in the same table. This makes it possible to use the same query to view past data "as of" a particular date and to view the current data.
Foreign keys to data that has been versioned should work the same as normal (unversioned) data.
The design should be so simple or universally understood that the learning curve for new developers is minimized.

Here are the slides of a presentation I have made a few times at tech fairs. It covers how all the above can be done. And here is a document that goes into more detail. I must make apologies for the document -- it is a work in progress and not all sections are completed. But it should give you all the information needed to implement anything from simple versioning to full-on bi-temporal access.


Whole numbers from sql column using SSIS

I think you're asking about using IsNumeric and CharIndex in your source query and then throwing away the things that are not whole numbers.  I would change my source query to be

SELECT
    S.*
,   CASE
        WHEN
        -- This filters for only the numeric type of data
        ISNUMERIC(S.Value) > 0
        -- this filters the non whole numbers
        AND CHARINDEX('.', S.Value) = 0
            THEN S.Value
        ELSE NULL
    END AS NewValue
FROM
    dbo.Source1 AS S;

Otherwise, you're looking at either using a series of Derived Column Components or a Script Component to do the same actions.

References

CHARINDEX
ISNUMERIC


This is definitely a case where you should use one table IMHO.

The entities involved are virtually identical only differing by 1 important field - that makes them very good candidates for a single table.

Have the SOA_account_number in the table and set it to NULL if the quote hasn't been approved and have a value set when the approval process has been successfully completed.

This simplifies your schema and all you have to do to distinguish between pending and approved quotes is to use a WHERE clause - i.e.

WHERE SOA_account_number IS (or IS NOT) NULL

You haven't given us the full table structure (SHOW CREATE TABLE My_Table\G), but I would suggest having a couple of DATE fields (or possibly DATETIME) - something like quote_offered_date and quote_accepted_date. Just a couple of thoughts.


Index with high fragmentation percentage

Short definition of Index Reorganize, made by Kendra Little:

  Reorganize: This option is more lightweight. It runs through the leaf level of the index, and as it goes it fixes physical ordering of pages and also compacts pages to apply any previously set fillfactor settings. This operation is always online, and if you cancel it then it’s able to just stop where it is (it doesn’t have a giant operation to rollback).

Even single reorganize should be efficient in Your case, but remember that it depends on Your data (how well pages get filled with row data).

Another advantage of reorganize in Your situation, other than being online operation, is that it can be stopped anytime (while index rebuild would have to rollback), so it's great to combine with Ola Hallengren's Maintenance Solution options: TimeLimit and WaitAtLowPriorityMaxDuration with WaitAtLowPriorityAbortAfterWait.

One disadvantage of reorganize I could think of, is that it can't be used to rebalance data between db files (for which index rebuild is often used).

As for fragmentation itself, even that high level shouldn't have much of impact on performance. It should free up a lot of space in data files, though.
Still, the best approach is to have index maintenance scheduled regularly, just don't stress over it.

Kendra Little wrote another article about dealing with fragmentation recently, it's worth reading.


PL/SQL block running without printing any output

You must run SET SERVEROUTPUT ON before you execute the PL/SQL script.


from the MySQL doc:

  If a table-full error occurs, it may be that the disk is full or that
  the table has reached its maximum size. The effective maximum table
  size for MySQL databases is usually determined by operating system
  constraints on file sizes, not by MySQL internal limits

Also check the Section C.10.3, “Limits on Table Size”.

Highlight for the OS limits.

+------------------------+------------------------------+
|    Operating System    |       File-size Limit        |
+------------------------+------------------------------+
| Win32 w/ FAT/FAT32     | 2GB/4GB                      |
| Win32 w/ NTFS          | 2TB (possibly larger)        |
| Linux 2.2-Intel 32-bit | 2GB (LFS: 4GB)               |
| Linux 2.4+             | (using ext3 file system) 4TB |
| Solaris 9/10           | 16TB                         |
| OS X w/ HFS+           | 2TB                          |
+------------------------+------------------------------+

And the list under "If you do encounter a full-table error, there are several reasons why it might have occurred:"


Is there any collection of PowerShell scripts that help the task of SQL Server maintenance?

I don't know that anyone has written something along the lines of Ola's script within PowerShell. I know that PowerShell scripts for SQL Server are slowly growing over at the TechnNet Script Center. Then MSSQLTips.com also has started publishing tips working with SQL Server and PowerShell.

Then you also have SQL Server PowerShell Extenstions project over on CodePlex. I don't guess you would consider it a repository but a good number of folks blog on how to use it and such. Some task within SQL Server are made much easier using SQLPSX.

I also tend to check out a few MVP blog sites that tend to focus on PowerShell scripts:
Allen White
Aaron Nelson
Sean McCown (MidnightDBA) (Click on the PowerShell tab for all the videos on the subject)


Enabling both local and remote connections to a mysql server on linux (ubuntu)

You can set it to:

bind-address = 0.0.0.0

Which will allow both. "If the address is 0.0.0.0, the server accepts TCP/IP connections on all server host IPv4 interfaces. "

More info: https://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_bind-address


Using MyISAM for reading and InnoDB for writing data

I recommend against using this. Consider:

This would only be of some advantage if on two separate servers (no point in mixing both on same MySQL server, naturally, since reads & writes will take place on both)

Which means you would follow RolandoMySQLDBA's advice, and set up an InnoDB master with MyISAM slave.

Do you have foreign keys on your InnoDB tables? If so, you're into integrity issues. If you happen to have ON DELETE CASCADE or ON DELETE SET NULL - prepare for bad news -- the cascading will not propagate on the slave. See: Impact of foreign keys absence on replicating slaves

OK, so maybe it's just this one table, and no foreign keys. But then you get to inherit all of MyISAM's other disadvantages, such as the likely possibility of not being able to recover from crash. Transactions won't work; the slave may find itself trying to execute queries on a table that hasn't really recovered from a crash; contains incorrect data. You may run into replication failures.

Phil's comment is also very valid: by synchronizing the tables you actually mean any write to your InnoDB table must propagate to your MyISAM table. This means same amount of writing.

If you are willing to relax some constraints, such as only batching (somehow?) the writes to the slave every once in a while, then that makes somewhat more sense.

So, in general, while the solution could work, you would have a constant headache to take care of.


The logical processing of a query is on MSDN (written by Microsoft SQL Server team, not 3rd parties)

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

A derived table follows this, then the outer query does it again etc etc

This is logical though: not actual. No matter how SQL Server actually does it, these semantics are honoured to the letter. The "actual" is determined by the Query Optimiser (QO) and you avoid the intermediate Cartesion product you mentioned.

It's worth mentioning that SQL is declarative: you say "what" not "how" like you would for a procedural/imperative programming (Java, .net). So saying "this happens before that" is wrong in many cases (eg assumption of short circuits or L-to-R WHERE order)

In your case above, the QO will generate the same plan no matter how it is structured because it is a simple query.

However, the QO is cost based and for a complex query it may take 2 weeks to generate the ideal plan. So it does "good enough" which actually isn't.

So your first case may help the optimiser find a better plan because the logical processing order is different for the 2 queries. But it may not.

I have used this trick on SQL Server 2000 to get 60x speed performance improvement on reporting queries. As the QO improves version to version it gets better at working these things out.

And the book you mentioned: there is some dispute over it
See SO and the subsequent links: https://stackoverflow.com/q/3270338/27535


MySQL only supports triggers for BEFORE / AFTER INSERT, UPDATE, DELETE, see documentation for the CREATE TRIGGER syntax.

An alternative solution might be to create a MySQL EVENT that runs at at a specified interval and checks whether there are any tables that don't have corresponding views, and if so, create them. (Make sure to enable the event scheduler. The default is OFF until MySQL 8.0. See documentation here.)

Maybe another option would be to create the view "if not exists" whenever a row of data is INSERTed into the table, in which case you could use a BEFORE INSERT trigger. (One obvious disadvantage of this approach is that the trigger will be fired every time you INSERT to the table, so there is a performance penalty on INSERT.)

Edit: Maybe yet another option could be that you only allow CREATE TABLE through a special stored procedure which will automatically also create the view.


Is this an excessively large 'Lock' time and is it indicative of a problem?

So the elephant in the room. Is that wait time excessive?

The lock waits from the screenshot show 18,024,389 ms / sec as the recent (average) wait time over the last couple of minutes.  That means for every second of "wall clock time," there are 18,000 seconds (5 hours?!) of lock waits accumulated by queries.  That's so tremendously bad that I wonder if it's just a bug in the Activity Monitor UI.
Depending on how many cores the server has, and how many queries are running concurrently, even the smaller number (2.5 seconds of lock waits per second of wall clock time) is not ideal.
Those waits could imply a blocking chain (you can use sp_WhoIsActive to identify the lead blocker and then try to fix why it's blocking everything).  Regardless, they can definitely contribute to these client side timeouts you've described - every second that a query waits on locks is a second that it's not making progress on the actual query being run.


SQL Server trace based on client TCP port

Is there a way to trace a SQL session based on the client's TCP port?

Yes. You can query sys.dm_exec_connections to identify a session from the client's TCP port (column client_tcp_port).

For example:

SELECT DEC.session_id
FROM sys.dm_exec_connections AS DEC
WHERE DEC.client_net_address = '192.168.0.100'
AND DEC.client_tcp_port = 63465;


Linked Server Security under SQL Agent Job Context

Put NT AUTHORITY\SYSTEM user in sysadmin server role
Add this user into linked server login mappings with remote user name and pwd


Does fast bcp replicate in sybase sql statement replication?

No. Fast BCP is an unlogged operation.  Sybase Replication Server relies on scans of the Transaction log for data to be replication.  The Rep Agent thread scans the transaction log in the Primary Database (PDB) for commits.  Once a committed transaction is found in the Transaction log, the SQL statements are forwarded to the Replication Server.  If an action is not logged, like Fast BCP, then no entry is created in the transaction log, and therefore it can't be replicated.

Slow BCP transactions will replicate, as Slow BCP is a logged operation.

In Sybase ASE 15.7, Fast-logged BCP has been introduced, but I am not sure about whether that is supported for Replication yet.


With the need of 10TB to be backed up this gets a bit complicated.

Replicas are no replacement for proper backups

While delayed replica set members can provide an relatively easy way to help you with accidental operations, there are no replacement for proper backups, very much like RAID isn't a replacement for file system based backups.

Recommendations

That heavily depends on how your setup looks like.

SAN snapshots

With 10TB, I assume you have some sort of SAN attached. The easiest way for backing up MongoDB in those environments is to make sure you have journaling activated both on the filesystem and MongoDB and simply take a snapshot on of the SAN volume of one of the secondaries, propably a hidden one to make sure your operations don't get interrupted. This usually takes mere seconds, but please _make sure_that your replication oplog window is sufficient. Otherwise, you might need to resync the secondary.

Do not use mongodump

I have to disagree with RolandoMySQLDBA about the usage of mongodump. First of all, it imposes locks on the server. Though they are lifted relatively fast, the shere number of locks might add up and interfere with your operations, unless run on a hidden node or when there is no read preference hitting the secondaries. Plus, it is not exactly fast. I'd expect it to run for hours, at least, most likely taking longer than your backup window. Side note: Always run mongodump with the --oplog option. Also keep in mind that mongodump does not backup indices, but the operations to create indices. Those indices have to be recreated during a restore, which may massively increase the time you need for it. From my experience, if you have to restore a database, you want to have it as fast as possible. Another point why mongodump isn't suited for backing up 10TB.

Notes on LVM snapshots

You can do an LVM snapshot on a running mongod instance provided that you have journaling enabled in mongod (and from my experience, it does not hurt to have it enabled on the FS level, too). However, LVM snapshots come with some implications. First, you obviously need to have enough disk space that can take the changes during the backup operations. Let me clarify that.

Let's assume you have an hourly change rate of 500GB. And that you want to have your backup blipped before it is uploaded to some storage. Even when using parallel bzip2, the compression of 10TB would need some hours to finish, simply because the fact that most likely you mass storage throughput would become your limiting factor. Let's assume it would take 2 hours to compress the data to 2TB. So by now we would need some 2TB + 2*500GB of free disk space total, 1TB needed for the LVM snapshot. This would create the need of over provisioning your filesystem by at least 30%. In case you want to have a proper safety margin, this could easily increase to 60-70% (20% for a utilization factor of 0.8 for the original file system, the same for the snapshot size plus the space needed for the bzipped backup itself). In most production environments, that would be inacceptable, since that over provisioning would be static (You would not want a backup script to mangle with your LVM dynamically, would you?).

MMS backup

While MMS backup has some awesome features (continuous backup, easy point in time recovery), it comes with some serious drawback: its's price tag for large deployments can easily be in the thousands. With an assumed hourly change rate of 500GB on those 10TB, it would be a medium six-figure sum for cloud backups. Monthly.

My suggestion he would be to take an enterprise subscription for your servers for being eligible to have an on premise MMS instance, including backup.

Summary

Here are the options I would take in descending order of preference.

SAN snapshots: easy to implement, relatively cheap
Enterprise subscription: Best features. Install it, configure it, forget it, it's there when you need it
LVM snapshots: easy to implement, but the costs of the necessary over provisioning may sum up over time.


How to switch master server and warm standby (with streaming replication) in PostgreSQL?

Keep in mind that currently you cannot demote a master to a standby.  Doing so will corrupt your database!

The basic documentation for doing this is at: http://www.postgresql.org/docs/9.1/static/warm-standby-failover.html

However the basic thing is you can't switch these other than:

Turn off primary
Promote secondary
Rebuild former primary as new secondary off the new primary.


Does Oracle Instant Client 12c support LDAP?

http://www.oracle.com/technetwork/database/features/oci/ic-faq-094177.html#A8434

  Can LDAP be used with Instant Client?

  Yes, LDAP can be used with Instant Client, as of 10.1.0.4.


Is there a product to auto-build indexes based on slow queries?

No, there is no product that does what you describe automatically. At least not for MySQL.

There are tools that identify slow queries, and even run EXPLAIN automatically, for example pt-query-digest. But deciding on the right indexes is up to you. There are too many exception cases where you might choose to create a different index, or avoid creating the recommended index, regardless of the advice of the tool.

You might like to read my presentation, How to Design Indexes, Really. It's not that hard to learn to do it.


Unsure of Rank / Dense_Rank - Mulitple Columns - change rank when unique combination changes

You need to use:

DENSE_RANK() OVER (ORDER BY CustomerID, Value)

Test at SQL-Fiddle


OK, I believe that I have a satisfactory answer.

SELECT tableName, rowId
  FROM tags
  WHERE tag IN ('tag1', 'tag2')
  GROUP BY tableName, rowId
  HAVING COUNT(tag) = 2;

I'm pretty new to this, but this query allows it to find all matches and group them by tableName and rowId. Before returning the results it limits the rows to those that matched exactly the number of tags being searched on.

If anyone knows a better way to do this I'm up for suggestions. Thanks.

Edit 07/06/2018

@akina and @RickJames both pointed out that this query only works if the rows are unique and offered two solutions to address this. See the comments below.


Breaking up long running query into batches runs fine for a while and then produces fatal error

Jon,

One possible option with the statement you posted would be to put an explicit begin transaction before the first delete statement and a commit statement right before the set of the @LargestKeyProcessed variable. This would commit the delete before you move to next loop iteration and should help with the log file as it's breaking the batch up into small transactions instead one one large one that is generated implicitly by the loop.

Additionally if your database is in FULL recovery mode you will need to take frequent backups of the transaction log to allow the transactions to be removed,once the have committed or rolled back, to prevent the log file from needing to grow. If your database is in SIMPLE recovery mode the transactions will get overwritten once the transaction commits or rolls back.


Identify top logins\users consuming resources

There's nothing built in to SQL Server that will do this. If you don't feel like using sp_who3 and partying like it's 2002, here's another method.

Grab sp_WhoIsActive

If you just want to see what's happening now, run it like this:

EXEC dbo.sp_WhoIsActive @get_plans = 1, @get_locks = 1

If you want to use it long term to find out what users are up to, follow the instructions here to set it up to log results to a table. I'd probably skip collecting query plans here on a busy server, that could really bloat things.

How does this help? It returns wait info, tempdb use, blocking, cpu, reads, writes, locking, memory use, along with login name, host name, and program name so you can (if you choose to log it to a table) group by connection info to aggregate resource usage over time. The aggregations won't be perfect, but it might get you close enough to what you need (for free).

If you want to spent money on it, buy a monitoring tool. Sentry One Performance Advisor and Quest Spotlight are both pretty nifty.

Hope this helps!