Database Mail works for one Instance but not three others I have a test box with 4 Instances of SQL Server on it. One 2012 instance and three 2008 R2 instances.

Database mail works fine on one of the named 2008 R2 Instances but not on any of the others.

I've been going through the troubleshooting steps on Microsoft's website and everything checks out. All of the settings are correct and it's starting just fine, it just won't send an email.

The profiles are public and the logins have the appropriate permissions.

The mail log contains:

Log ID           9137
Process ID       7740
Mail Item ID     6031
Last Modified    3/5/2018 11:34:23 AM
Last Modified By sa

Message
The mail could not be sent to the recipients because of the mail server failure.
(Sending Mail using Account 1 (2018-03-05T11:34:23).
Exception Message: Could not connect to mail server.
(A non-recoverable error occurred during a database lookup).

I have tried both sending the test emails through the UI (via Send Test Email), and through my own procedures. When I look in the database mail log I see the same errors stretching back to when I built the instances. I see messages in the log file about database mail successfully starting and shutting down.

I've rebooted several times and all of the settings are exactly the same between instances. One instance works perfectly and three others don't. If it were a firewall or AV issue it would more than likely be the same for all instances.

I'm not sure what the actually issue here was but after dropping the account and profile and re-creating them everything worked.

Looking back, I realize that I had set it up on the first instance with the GUI, and then scripted it out and ran the script for the other instances. Why that didn't work I'm not sure.

A big thank you to everyone who tried to help me.


MongoDB 3.6 create local Shard db without replica This is what I want to do:

Create a Config Server
Create 2 Shard
Create a Mongos Router

I don't want to replicate my data using replica set, I want to simulate that each shard has only a part of data like it says is shard theory.
I'm following this guide, but I understood that the guide create many replicas for config server and shared server and I don't want that. So this is what I do:
Config file of Config Server:
sharding:
  clusterRole: configsvr
replication:
  replSetName: localhost
net:
  bindIp: localhost
storage:
  dbPath: /home/christian/mongo/confsvr/metadata

Execute Config Server:
mongod --config /mongo/confsvr/mongod.conf --port 27019

Now doc uses rs.initiate() for adding a replica of config server (if I understood right), so I dropped this part.
After that, I created two config file for Shard Server.
sharding:
 clusterRole: shardsvr
replication:
  replSetName: localhost
net:
  bindIp: localhost
storage:
  dbPath: /home/christian/mongo/shard[1|2]

I started two shard:
mongod --config mongo/shard[1|2]/shard.conf  --port 2703[1|2]

Created conf file for Mongos Router:
sharding:
  configDB:localhost/localhost:27019
net:
  bindIp: localhost

So i tried to start mongos:
mongos --config /home/christian/mongo/router/mongos.conf

But terminal print in loop this:

Unable to reach primary for set localhost
Cannot Reach any nodes for set localhost

Last steps were connecting to mongos, and add the Shards to DB
How could I obtain what I want?

Reference:

Sharded Cluster Components
Deploy Sharded Cluster
Configuration File Options

There are few things wrong in your approach. I suggest you first understand the concept and test every step. For example once you create your config server make sure you can connect to your Mongod and then only move to next step. Same is true for each of your shard.

My answer is pointing to those and some more reading material so you can do this by yourself.

First of all starting from MongoDB 3.6 shards must be deployed as Replica set and not stand alone:

  A MongoDB sharded cluster consists of the following components:

  shard: Each shard contains a subset of the sharded data. As of MongoDB
  3.6, shards must be deployed as a replica set.

  mongos: The mongos acts as a query router, providing an interface between client applications and the sharded cluster.

  config servers: Config servers store metadata
  and configuration settings for the cluster. As of MongoDB 3.4, config
  servers must be deployed as a replica set (CSRS).

  For testing and development, you can deploy a sharded cluster with a
  minimum number of components. These non-production clusters have the
  following components:

  A replica set config server with one member.
  At least one shard as a single-member replica set.
  One mongos instance.

What you mentioned below is not possible. You will need Replica Set to use Sharding but for development/test you can have a one member Replica set.

  I'm following this guide, but i understood that the guide create much
  replica for config server and shard server and i don't want that.

You are using same name for Config Server, Shard1 and Shard2 Replicaset. You need to use different name and make sure mongod is running on separate port for all instances.

If all your Mongod is running in one host you need to mention the port in your configuration file. See example here.

See the documentation for using rs.initiate() here.

I do not see you adding your shards to the cluster. See this.


Two tables, same Attributes unable to count, I am working on Two table together for count how many SKU.

As I'm able to sum them up in how much amount with no problem. but I need a count on how many QTY in SKU.

I am able to get a count in SKU in one table only but unable to do two table. I have tried UNION SELECT but nothing working

SELECT
     transaction_type, sku, count(quantity_purchased) as SKU_ORDER

FROM
    settlements_qty_test
WHERE
        transaction_type = 'order'
        AND amount_description = 'Principal'
        AND amount_type = 'ItemPrice'

UNION SELECT
     transaction_type, sku, count(quantity_purchased) as SKU_ORDER

FROM
    settlements_qty_test1
WHERE
        transaction_type = 'order'
        AND amount_description = 'Principal'
        AND amount_type = 'ItemPrice'

GROUP BY sku
ORDER BY sku

I have used this similar method for how much, and seems it working fine but not this one. Seems I am missing something?

I am still rookie MySQL.

You could try to fetch all affected records of both tables using UNION ALL and then COUNT the records. (I'm not sure if you want to COUNT records or to SUM quantities.)

Notice you should add transaction_type to the GROUP BY clause.

If you want you can move the WHERE clause to the main query.

SELECT transaction_type, sku, COUNT(quantity_purchased) as SKU_ORDER
FROM   (
        SELECT transaction_type, sku, quantity_purchased
        FROM   settlements_qty_test
        WHERE  transaction_type = 'order'
        AND    amount_description = 'Principal'
        AND    amount_type = 'ItemPrice'
        UNION ALL
        SELECT transaction_type, sku, quantity_purchased
        FROM   settlements_qty_test1
        WHERE  transaction_type = 'order'
        AND    amount_description = 'Principal'
        AND    amount_type = 'ItemPrice'
       ) t1
GROUP BY transaction_type, sku
ORDER BY sku;


Creating a clustered index and want to add log files We are adding a clustered index to a 6 billion row table. This is an online operation (15 hours into it).

If we add another log file while this is taking place, does the alter database add log file block command applications?

If we add another log file while this is taking place, does the alter
  database add log file block command applications?

No, this won't be a "blocking" operation, but there may be a write pause while the space intended for the log file is zeroed out. Instant File Initialization doesn't apply to log files. If you're going to create one, don't make it very large.

The other thing is that log files aren't written to in parallel, the way multiple data files can be. There's no guarantee it'll be used immediately, unless the other log file is completely full.

Hope this helps!


Associate BAK file with SQL Server database One of our users deleted a lot of data from one of our database applications, and I need to find a way to restore it.  I have a bak file and full recovery mode, but there is a catch.

This bak file is not recognized as belonging to this database, so I am not given the option of a timeline restore.  I can restore the bak as is, but it does not contain all of the data I need.  My only other option seems to be somewhat expensive (~$2000) recovery tools which can recover the data from the transaction logs.

Is there a way to convince SQL Server that this bak file really does belong to this database?

Do I have any other options?

This bak file is not recognized as belonging to this database

It may just be that you haven't performed a tail log backup on the target database.  But you should restore the backup and all the log files to a new database first to determine if it has the data you need.  Then you can either proceed with the restore, or manually copy the data from the new database.

  It was my assumption that I could use the LDF in conjunction with a BAK to reconstitute the database at any given point. Does it not work that way?

No.  The LDF will be overwritten in a restore.  You must move the transaction log records from the LDF to a log backup first.  That's why RESTORE over an existing database will fail if you haven't taken a Tail Log backup: otherwise you would lose data.

So before you start the restore sequence, ensure you've taken a Log Backup of the database.  This will move all the log records currently in the .LDF file into the log backup file and make them available for use in a point-in-time restore.


Setting up a listener for sql server alwayson availability groups without computer object rights in the AD Is there a way to create a sql server alwayson listener in an active directory (AD) environment where I don’t have create computer object rights? The AD admin created a computername for me. Is there a way to connect it to the Listener?
The following TSQL fails because I don’t have create objects rights in the AD.

USE [master]
GO
ALTER AVAILABILITY GROUP [VG-AdventureWorks2014]
ADD LISTENER N'DNSCOMPUTERNAME' (
WITH IP
((N'XXX.XXX.XXX.XXX', N'255.XXX.XXX.XX')
)
, PORT=1431);
GO

Company policy does not allow AD rights for the DBA.

There is one proper way to pre-stage the listener and one way to allow the cluster to create the listener itself. Please note that YOUR account is not what is used to authorize to AD to create the listener when creating it through FCM/Powershell or SQL Server, the CNO is used as security context.

The official pre-stage way

Find the OU with the CNO in it
Create a computer object, name it your DNS listener name
Set the security on the new VCO and give the CNO full control over it (full isn't required but the list of needs is fairly long so just give it full control)
Create the listener through SSMS/TSQL/Powershell

The official automatic creation way

Give the CNO Create computer objects, list properties, read properties, write properties over the OU it resides in.
Create the listener through SSMS/TSQL/Powershell

They way that is listed in the other answer given is a mix of the pre-staging way with how to create multiple listeners. Additionally it has improper configuration when it comes to the security and will most likely come back to bite you if you do it that way.


65G SQL Server database has 5.5G of data in tempdb when not in use I have a SQL Server instance with 1 production database (also ReportServer dbs) that is 65G.  tempdb is 5.5G (well, actually much bigger but 5.5G is in use) when server is not in use.  I believe this is not normal.  How do I diagnose what is causing this space to be filled and not released?

Non-temporary user objects

Maybe there are non-temporary user tables in tempdb.  These wouldn't get cleaned up automatically (unless the server is restarted).  You can check for them like this:

USE tempdb;
GO
SELECT * FROM sys.objects WHERE [type] = 'U';

To release this space, you'll need to manually drop these tables using DROP TABLE statements.  Make sure no one is still using them first

Sleeping sessions keeping temp objects alive

Another possibility is that someone created a temp table, and their session is still active.  I created a temp table using the Stack Overflow sample database and left the session open:

SELECT * INTO #Users FROM dbo.Users;

Checked space usage:

All 4 tempdb files are using about 15 MB of space.

You can see that the session is still open by checking sp_WhoIsActive with the option to show sleeping spids:

EXEC master.dbo.sp_WhoIsActive @show_sleeping_spids = 2;

Look for sessions that have tempdb allocations, or otherwise suspicious sessions that have been open for a long time.

If I close the window for session 53 in SSMS, and then re-check sp_WhoIsActive, the query is gone.  And now I check tempdb space usage again:

You can see that "Space Used" dropped in all 4 files.

To release space that's being held like this, you'll need to either:

track down the person in the login_name field and ask them to end their session, or
kill the session using the KILL command


galera cluster how to run an ALTER without using RSU/TOI I need to run an alter statement but would like to prevent locking/stall the cluster. The Galera docs advised using:

SET wsrep_OSU_method='RSU';

Then Run the ALTER statement, once finich:

SET wsrep_OSU_method='TOI';

Problem is that this needs to be run in every node, and I have only access to one node behind a load balancer, any alternatives or workarounds?

RSU:  You must bypass the load balancer and go straight to each node in turn.

TOI:  Simply run the ALTER on any node.  The downside is that if the ALTER takes a long time, it will, as you say, lock/stall the cluster.

gh-ost:  This depends on the binlog, which (I think) is not used in Galera.

pt-online-schema-change:  This requires adding TRIGGERs to the table -- perhaps a problem?  And it requires a script to continually talk to the server in question -- will your load balancer allow such?  And it probably wants to treat the system as Master-Slave(s), not Multi-Master -- apparently you have set up Galera set up Multi-Master?


When rows of a resultset are generated? Consider a client which asks MySQL server for 100000 rows, but retrieves rows later one-by-one.

Does MySQL server generate the entire resultset before sending to client, or does it generate rows during sending to the client?

When most memory is used by the server: when generating the resultset or when sending it to the client?

If MySQL needs to create a temporary table it generates the result before sending it to a client.

Normally you want to avoid that and make Mysql to generate the result as it accesses the table(s) and as fast as the client reads it.


Difference between avg Disk sec/write and io_stall_write_ms We have a SQL Server 2016 SP2 Enterprise with latest CU with the database files spread out over different disks.
So we have data,log, tempdb and system db's have each their own drive. Date and log only contain one file.Those drives each has their own LUN on a All-Flash SAN.
To monitor the latency I capture sys.dm_io_virtual_file_stats every 15 minutes and then calculate the latency using the previous snapshot.
For the write latency I'm using following calculation:
(io_stall_write_ms - lag (io_stall_write_ms,1,0) over (order by checkdate))/(num_of_writes - lag (num_of_writes,1,0) over (order by checkdate)) write_latency

I'm getting an average write latency of 10ms but when I'm starting perfmon (with duration set to 900 seconds) and monitor the avg. Disk sec/Write in the same period for the same drive I'm only getting an average write latency of 3 ms.
I'm also capturing the wait stats for the same period, when I look at PAGEIOLATCH_EX waits and calculates how long every wait took, I'm also getting a value of approx 3ms.
I thought io_stall_write_ms represented the same as avg. Disk sec/Write or am I missing something?
Can somebody explain this behavior?

I think your conclusion that these two numbers should match up is fair.  That measurement of write latency from sys.dm_io_virtual_file_stats should provide similar numbers to the "Logical Disk" ➡ "Avg. Disk sec/Write" Perfmon counter.
Make sure that you are comparing "apples to apples" as much as possible.  The default for that Perfmon counter is to show you latency across all disks, so make sure you've selected the disk you're interested in (not "Total"):

Similarly, on the SQL Server DMV side, make sure you're only aggregating and comparing data for files on that same disk.  The sys.dm_io_virtual_file_stats is giving you data per file, which could be across multiple disks.
It could just be the differences in sample rates between the different ways of measuring that are throwing things off.  For instance, you're getting the DMV data every 15 minutes.  But presumably you were viewing the live output of the default Perfmon, which will show you the average over 100 seconds.  There might just be outliers in the 15 minute interval that lead to the average being higher for the DMV than what you're seeing in Perfmon.  To try and rule this out, you could (at least temporarily) sample virtual file stats more frequently to see if the numbers match up better.
I expect, based on the lower latency-related values you see in Perfmon and wait stats, that you're not actively having disk latency issues, and you're just curious about the difference measurements you're getting from the different tools.
If you are having issues, you might be in for a deeper dive into "what's getting between SQL Server and the disk writes."  Top Microsoft Man Sean Gallardy talks about that pretty in depth here: Slow checkpoint and 15 second I/O warnings on flash storage


pg_dump: No matching schemas were found I am trying to dump a schema from Postgres 9.4, running on Cents 6.5, but I get an error:

  pg_dump: No matching schemas were found

Here is the command I am running:

pg_dump -U postgres -n "OLD-TODELETE-bushes" --verbose site > output_nf

I am a little confused by this as when I run select schema_name from information_schema.schemata, I can see the schema name.

If I run pg_dump for all schemas the OLD-TODELETE-bushes is dumped to file, as expected.

There are no error's in the Postgres logs.

Anyone have any ideas?

Fixed this by changing the quotes from "schema_name" to '"schema_name"'

See: pg_dump doesn't dump datascheme with uppercase letters in

...where Tom Lane replied:

  The problem is that there are two levels of quoting needed: one for the shell and one for SQL.  As you wrote it, the double quotes are stripped off by the shell and so pg_dump gets Schema, which is case-folded per normal SQL rules.  So you need to write:

pg_dump -n '"Schema"' -U postgres database

  Now, the shell eats the single quotes and passes "Schema" to pg_dump.


SQL Server 2016 Always ON high availability group listener configuration I have 2 servers running Windows Server 2016 with Microsoft SQL Server 2016 Enterprise. I have created a fail over cluster in Windows and joined both nodes without Active Directory. Then I created an availability group with both nodes and everything is fine and the database is synchronized.

Now I created the AG listener, I am not using DHCP, so I assigned a static IP. What I don't understand is how the fail over is going to work; when I connect to the listener everything is fine when Node1 is primary, when I do manual failover to Node2, I can't connect.

How will the listener switch to node2? Should the listener have 2 IPs for both nodes?

BTW, the servers are not on a private LAN.

What I don't understand is how the fail over is going to work

This is documented in a high level in Microsoft Docs.

  when I connect to the listener everything is fine when Node1 is primary, when I do manual failover to Node2, I can't connect.

That depends on your connection string, DNS infrastructure, and availability group setup.

If it's a single subnet, there shouldn't be an issue. If it's multi-subnet, you'll want to instruct the client driver to resolve all ips in parallel using the MultiSubnetFailover=true keyword in older version of .net/java/php/etc. Make sure you're using an updated client driver version that supports said keyword.

There are additional windows clustering private property settings for net names that can influence how these things work, but it doesn't sound like you've made any manual adjustments to these (defaults used) and I won't muddy the waters with it but did want you to be aware that there are additional settings.

  How will the listener switch to node2?

This happens at the WSFC level. A failure is detected or SQL Server initiates a failover which moves the resource group (and all contained resources) logically to the new node (where ever and which ever that may be). The resources are then attempted to come online. Part of the process to come on-line for a network name (listener) is to have at least one dependent IP address online and register with DNS among other items. This may or may not be the same IP Address based on how your networking is setup and the location of the new node owning the resources.

  Should the listener have 2 IPs for both nodes?

The listener needs at a minimum an IP Address for each subnet it could possibly be online in. It may have 1, 2, 3, ... 7, etc., depending on your specific setup and infrastructure.


When does Log shipping restore job mark itself to completion in standby mode I am editing my previous question to be more precise as i am still unable to get the logic behind the log shipping restore job.

May be there is something i am missing to explain but will try my best.

We have night Log Shipping restore job running on secondary at 10:00 PM daily once. When it starts to restore it picks the log backups from where it completed on its last run. I understand how log restores are done per Kins link, but i see LS restore will try to get as close to current run time and completes. But that time varies with a gap of 30 min, 20 or even an hour sometime.

Its still not clear how ls restore is checking this is the best time it will complete. This is to let users know till what time of the day data is getting restored via LS job, so that they can query and fetch data accordingly from Standby database:

Previous question marked as duplicate @ How log shipping restore works

Eg:  What i see is LS restore completes at 3:30 AM. For log backups restored , i see the files which arrived at 2:45 AM only restored but not the one at 3 and 3:15 AM. So just thinking if these 2 files are copied, why LS restored ignored them and got completed. LS backup and LS copy happens every 15 mins while restore once at10PM.So i need to understand how is LS restore deciding on what basis will it complete?

I would try utilizing the built-in stored procedures to investigate and hopefully correct this.

For example, try running this on the Secondary server while in the master database:

EXEC sp_help_log_shipping_secondary_database @secondary_database = '<database name>'

This will return a result set that contains a column named 'restore_all' that may be of interest to you.  If that is 0, then when the restore job runs it will stop after restoring only 1 transaction log file.  Otherwise, it will apply as many as possible.

If it is configured incorrectly for your situation, you can change it like this:

EXEC sp_change_log_shipping_secondary_database @secondary_database = '<database name>', @restore_all = 1

Hopefully, this will help you correct your issue or at least give you a boost in your search for the root cause.


Slow Query with LIMIT We have this query:

SELECT "messages".*
FROM "messages"
WHERE "messages"."account_id" = 1
AND "messages"."status" = 'unread'
AND (created_at >= '2014-04-24 01:00:00.000000')
ORDER BY id DESC

It runs fine under 0.1ms. But when we add LIMIT:

SELECT "messages".*
FROM "messages"
WHERE "messages"."account_id" = 1
AND "messages"."status" = 'unread'
AND (created_at >= '2014-04-24 01:00:00.000000')
ORDER BY id DESC
LIMIT 5

It runs over 20,000ms. Some notes:

The messages table has over 300,000 rows, not that big.
We have indexes on all 3 columns in the WHERE condition.
The account_id and created_at conditions filter out 3,000 messages from the 300,000 rows. Out of the 3,000 messages in that account_id, there are only 3 unread messages.

So we isolated each WHERE condition and found out about a certain behaviour -- we tweaked the LIMIT to correspond with the amount of unread messages:

SELECT "messages".*
FROM "messages"
WHERE "messages"."account_id" = 1
AND "messages"."status" = 'unread'
AND (created_at >= '2014-04-24 01:00:00.000000')
ORDER BY id DESC
LIMIT 3

It runs under 0.100ms. Any explanation for the difference in performance?

Adding LIMIT to the query often makes Postgres choose a different query plan. If statistics or cost estimates stray too far from actual data distribution / actual costs, you may end up with a slower query, even though Postgres reckoned it would be faster. Irregular data distribution may cause the query planner to misjudge the selectivity of WHERE conditions.

In particular, increasing the STATISTICS target for status, account_id, created_at and id might help.

Details:

How can I avoid PostgreSQL sometimes choosing a bad query plan for one of two nearly identical queries?

Aside from that, a (partial) multi column index may help. Depends on the complete situation. Which of your WHERE conditions are constant? How can they vary? Is some additional maintenance cost and disk space acceptable for a special index?

For example:

CREATE INDEX messages_special_idx ON messages (account_id, created_at, id)
WHERE status = 'unread'
AND   created_at >= '2014-01-01 01:00:00';

Run ANALYZE messages; before you try your queries again.


Cannot connect from elsewhere than a console I am quite a beginner with MySQL, but I already got problems with setting up a connection. I tried to use MySQL Workbench, but I can't connect to the server. I use SparkyLinux, a Debian-based distribution. Here is what I did so far :

I created a user and checked that I could log in a console :

adrien@sahelantropus:~$ mysql -u adrien -p

Enter password:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 10.1.23-MariaDB-9+deb9u1 Debian 9.0

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.

MariaDB [(none)]>

(maybe it is interesting to notice I can either give my password or not, and log in with exactly the same output)
I tried to setup a connection with MySQL Workbench, but when I test it, I get the following output :

  Failed to Connect to MySQL at 127.0.0.1:3306 with user adrien
  Access denied for user 'adrien'@'localhost'

I tried to use MySQL Connector in Python, and got the following exception :

  1698 (28000): Access denied for user 'adrien'@'localhost'

I am sorry if something obvious is going on and I cannot see it, and I thank anyone who could help me.

Probably mysql doesn't listen the TCP sockets at all and console client is connected via filesocket. Try to list all network sockets listened at your host:

ss -l | grep 3306

The other possibility is that you have turned skip_name_resolve variable ON that disable using of localhost instead of 127.0.0.1.


HierarchyID--multiple root values in one table Currently I am trying to work my way around the HierarchyID concept in order to convert a few tables for our programmers.  I have the gist of it understood, but one of the cases I'm working on has me stumped.

One particular table deals with different kinds of equipment failure codes using a parent/child relationship model.  The problem is, the way the codes are laid out means that the (seemingly) most obvious solution would be to use multiple different trees for each base category.  While the CLR methods work for single-root cases, using multiple root values throws off the methods (for example, getting the ancestor of one particular node/ID will also get the values in the same general spot for the other trees).

For reference, the table code:

CREATE TABLE FailCode
(
  IDOrg hierarchyid,
  ID int IDENTITY,
  Name nvarchar(200),
  ParentID int
CONSTRAINT PK_FailCode_ID
  PRIMARY KEY CLUSTERED (ID)
);
GO

I'm looking at my options.
I've figured one option would be to use a dummy root value and have the categories as child nodes of the dummy root.  My other idea would be to create a separate table for each category on its own and use a single-root hierarchy for each table.

Beyond that, I'm drawing blanks.  My question is:
What other options are there to represent multiple-root hierarchy setups in SQL Server?

EDIT:  I should probably add this--the table in question used to have another column for each category, which I had used in my queries.  This column is being struck from the table at the behest of another DBA and a couple of programmers, so it is no longer an option for me.

(Revised question begins here; I have left the original for the sake of anyone reading.)

@Avarkx:
I humbly thank you for the answer. Unfortunately, there's a bit of a problem--I spoke with the other DBA and he insisted that there have to be three separate root nulls (listed as NULL with parent ID AND having just '/' for the ToString representation).

The table currently looks something like this (sorted by level for ease of reference):

       IDOrg   ID   Name    ParentID
---------------------------------
/      0x       1   Root1   NULL
/      0x       2   Root2   NULL
/      0x       3   Root3   NULL
/1/    0x58     4   P1C1    1
/1/    0x58     9   P2C1    2
/1/    0x58     13  P3C1    3
/1/1/  0x5AC0   11  P9C1    9
/1/1/  0x5AC0   15  P13C1   13
/1/1/  0x5AC0   7   P4C1    4
/1/2/  0x5B40   8   P4C2    4
/1/2/  0x5B40   16  P13C2   13
/1/2/  0x5B40   12  P9C2    9
/2/    0x68     14  P3C2    3
/2/    0x68     10  P2C2    2
/2/    0x68     5   P1C2    1
/2/1/  0x6AC0  17   P5C1    5
/2/1/  0x6AC0  21   P10C1   10
/2/1/  0x6AC0  23   P14C1   14
/2/2/  0x6B40  24   P14C2   14
/2/2/  0x6B40  22   P10C2   10
/2/2/  0x6B40  18   P5C2    5
/3/    0x78    6    P1C3    1
/3/1/  0x7AC0  19   P6C1    6
/3/2/  0x7B40  20   P6C2    6

So I suppose I need to revise my original question:
Is it possible to use this table setup and manipulate queries in order to differentiate between specific trees when retrieving information regarding the hierarchy?
For example, if I needed to get the ancestor for '19', would it be possible to filter things out by tree in a query (or perhaps a query within a stored proc.) so that I just get '6' and not '10' and '14' as well? Adding records is simple enough, but queries like this example are problematic.

(If the answer is "no", I suppose I'll have to work something out with him...that adjacency pair solution is downright brilliant, and I see no reason why we couldn't use that if we weren't willing to do so.)

The dummy root is probably the right track, except you don't actually have to create a dummy root to make multiple roots.  Your table also appears to be simultaneously an adjacency pair as well, so you've got a couple of immediate options on how you want to go using the table.  Both should probably be tested for suitability, as I have a sneaky feeling that depending on read-a-lot or write-a-lot usage, your mileage from either case may vary.

Since the needs of both your developers and fellow DBAs must be considered, most of the other options are probably out of the question ( though there are loads and loads ).  This SQL Fiddle illustrates a couple of ways to leverage the materialized paths inherit in your structure, first through leveraging a multiple-root HIERARCHYID, then through adjacency pair traversal through recursive CTEs.

To implement multiple roots, the only real "trick" to it is to preserve the sub-sequence on the root level.  Instead of simply calling NULL descendants during root node creation, like so,

INSERT INTO dbo.FailCode ( IDOrg, Name )
VALUES ( HIERARCHYID::GetRoot().GetDescendant( NULL, NULL ), '1' );

each root node can be created using the root as the base level ( though the first one obviously doesn't matter ):

INSERT INTO dbo.FailCode ( IDOrg, Name )
VALUES ( HIERARCHYID::GetRoot().GetDescendant( (
            SELECT  MAX( IDOrg )
            FROM    dbo.FailCode
            WHERE   IDOrg.GetAncestor( 1 ) = HIERARCHYID::GetRoot() ), NULL ),  '1' );

INSERT INTO dbo.FailCode ( IDOrg, Name )
VALUES ( HIERARCHYID::GetRoot().GetDescendant( (
            SELECT  MAX( IDOrg )
            FROM    dbo.FailCode
            WHERE   IDOrg.GetAncestor( 1 ) = HIERARCHYID::GetRoot() ), NULL ),  '2' );

If you're in more of an UPDATE situtation, you can create the multiple root nodes off the primary key, if you like:

SELECT  CAST( '/' + CONVERT( VARCHAR( 16 ), ID ) + '/' AS HIERARCHYID ).ToString()
FROM    dbo.FailCode
WHERE   Name IN ( '1', '2' );

Children can then be added to each root node as necessary, business as usual stuff from there.

On the adjacency pairs side of your structure, either of the above inserts would do the trick - the ParentID for any root node just has to be NULL.  Again, at this point, adding children is pretty evident.

Either way you go about it, the hierarchy capabilities are largely equivalent and either solution can be effective and efficient ( though the adjacency list model will certainly appear to be a little more involved! ).

HIERARCHYID

SELECT  Org = IDOrg.ToString(), ID, Name
FROM    dbo.FailCode
ORDER BY ID;

Adjacency List

;WITH cte_AdjacencyList AS (
    SELECT  Lvl = ROW_NUMBER() OVER (
                ORDER BY ID ),
            Org = CAST( '/' + CONVERT( VARCHAR( 16 ), ROW_NUMBER() OVER (
                ORDER BY ID ) ) + '/' AS VARCHAR( 32 ) ),
            ParentID, ID, Name
    FROM    dbo.FailCode
    WHERE   ParentID IS NULL
    UNION ALL
    SELECT  Lvl = ROW_NUMBER() OVER (
                ORDER BY fc.ID ),
            Org = CAST ( al.Org + CONVERT( VARCHAR( 16 ), ROW_NUMBER() OVER (
                ORDER BY fc.ID ) ) + '/' AS VARCHAR( 32 ) ),
            fc.ParentID, fc.ID, fc.Name
    FROM    dbo.FailCode fc
    INNER JOIN cte_AdjacencyList al
        ON  fc.ParentID = al.ID )
SELECT  Org, ID, Name
FROM    cte_AdjacencyList
ORDER BY ID;


Searching from array (complex criteria) without unnesting? I have table with arrays of dates. How to search in them?

create table if not exists somedates (
startdate date,
otherdates date[]);

insert into somedates (startdate, otherdates) values
('2000-11-14', '{2001-10-30}'),
('2001-09-26', '{2002-09-20,2004-09-21,2006-09-19,2010-09-17}'),
('2005-05-10', '{2006-08-03,2008-08-12}'),
('2004-09-29', '{2005-09-29,2006-10-02,2007-10-04,2009-09-22,2010-08-06,2011-09-01,2013-09-17}');

I would want to find all rows, where in otherdates there is "1 year" (0.6 – 1.4) AND "5 year" (3.5 – 5.5) followup-dates found.

Following doesn't work:

SELECT *
FROM somedates
WHERE any(otherdates) - startdate > 0.6*365
      and any(otherdates) - startdate < 1.4*365
      and any(otherdates) - startdate > 3.5*365
      and any(otherdates) - startdate < 5.5*365
;

Following works, but if there is (and there is in the real case) lots of dates, I should put lots more OR-cases.

select * from
    (SELECT *, generate_subscripts(otherdates, 1) as s
    FROM somedates) as foo
where (otherdates[s] - startdate) >= 0.6*365
    and (otherdates[s] - startdate) <= 1.4*365
    and (
           ((otherdates[s+1] - startdate) >= 3.5*365
           and (otherdates[s+1] - startdate) <= 5.5*365)
        or ((otherdates[s+2] - startdate) >= 3.5*365
           and (otherdates[s+2] - startdate) <= 5.5*365)
        or ((otherdates[s+3] - startdate) >= 3.5*365
           and (otherdates[s+3] - startdate) <= 5.5*365))
;

2001-09-26   {'2002-09-20','2004-09-21','2006-09-19','2010-09-17'}   1
2004-09-29   {'2005-09-29','2006-10-02','2007-10-04','2009-09-22','2010-08-06','2011-09-01','2013-09-17'}   1

Using some kind of ANY-structure somehow would make more sense than needing to enumerate all indexes.

I know by unnesting I can do the search also, but surely there should be a way to do this without unnesting?

Even if the syntax were valid, your first query wouldn't do what you describe anyway, as you need to ensure it is same date from the array which is > one thing and < another thing, not two different dates.  You need to use ranges, both to be syntactically valid, and to be semantically valid.

select * from somedates where
    daterange((startdate+interval'0.6 years')::date,(startdate+interval'1.4 years')::date) @> ANY (otherdates) and
    daterange((startdate+interval'3.5 years')::date,(startdate+interval'5.5 years')::date) @> ANY (otherdates);

You can make it a bit less ugly like this:

create or replace function daterange2(date, interval, interval) returns daterange
  as $$ select daterange(($1+$2)::date, ($1+$3)::date) $$
  language sql immutable parallel safe;

select * from somedates where
    daterange2(startdate,'0.6 years','1.4 years') @> ANY (otherdates) and
    daterange2(startdate,'3.5 years','5.5 years') @> ANY (otherdates);


sys.system_internals_partition_columns view on Azure SQL The sys.system_internals_partition_columns view on Azure SQL doesn't seem to exist. Is there another view I can use to examine index modifications on a per column basis against an Azure SQL DB?

Not sure exactly what you want to grab out of the referenced system view. Much of the data you care about is likely still exposed to you, but through the Database related DMVs which have more or less been around in some fashion since SQL Server 2005 with additions in versions since.

You might have some luck with querying a few of these DMVs. You can explore with SELECT * FROM

sys.dm_db_partition_stats
sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) (specifying the parameters required for your usage)
sys.dm_db_index_usage_stats

etc.

There are quite a few database level and connection information level Dynamic Management Views exposed to you in Azure SQL DB.


PostgreSQL Generate create Foreign keys from information_schema or pg_constraint Recently on a testing infrastructure, to restore multiple tables (via copy command) I dropped all the foreign keys.
I have restored this database schema dump into a different database before dropping the FK.
Is there any way that I can generate the create FK from information_schema or pg_constraint?
I tried to get it from a dump file, but the create table statement itself having the FK (not an Alter table statement).
Any suggestions?

I think you are looking for pg_constraint_def, like in the following example:
SELECT pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conname = 'child_accountid_itemid_fkey';


How long will an index take to create? I have a table with over 70 million rows in and I have viewed the execution plan which has identified a missing index.  I took a snapshot of data a short while ago and created this index on a local test database (which was fast to create) and the improvement is exactly what is needed in the live system.  What I'd like to know is, is there anyway to tell how long it will take to create on the live system?

Actually - it depends on many things like

hardware you use
wideness of index
current load
...

If index is relatively wide - it can take from minutes to hours.

but

if you have your DB on Enterprise Edition of Sql Server, than you can choose to create an index WITH ONLINE=ON - it will take a little bit more time and space to complete, but will not stop other processing with the table involved


PostgreSQL Trigger to keep track of table changes I am trying to create a trigger (Postgres 9.6) to track changes made to a table. This is my approach:

CREATE OR REPLACE FUNCTION taxon_history() RETURNS trigger AS
$BODY$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO history.taxon(operacao, "data", tecnico, original_oid, taxon)
        VALUES ('DELETE', current_timestamp, current_user, old.oid, old.taxon);
        RETURN old;

    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO history.taxon(operacao, "data", tecnico, original_oid, taxon)
        VALUES ('DELETE', current_timestamp, current_user, old.oid, old.taxon);
        RETURN old;

    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO history.taxon(operacao, "data", tecnico, original_oid, taxon)
        VALUES ('INSERT', current_timestamp, current_user, new.oid, new.taxon);
        RETURN old;
    END IF;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER history_taxon
AFTER INSERT OR UPDATE OR DELETE ON taxon
FOR EACH ROW EXECUTE PROCEDURE taxon_history();

However when something changes in the (public).taxon table, no record is added to the history.taxon table. I also don´t get any error message so I am in the dark on why nothing is happening. What am I doing wrong?

In the end I solved this through 'Divide and conquer' - I split the trigger into three parts and now it works (though I am not sure yet why the original trigger does not work):

    CREATE OR REPLACE FUNCTION taxon_history_delete() RETURNS trigger AS
$BODY$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO history.taxon(operacao, data, tecnico, original_oid, taxon)
        VALUES ('DELETE', current_timestamp, current_user, old.oid, old.taxon);
        RETURN old;
    END IF;
END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION taxon_history_update() RETURNS trigger AS
$BODY$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        INSERT INTO history.taxon(operacao, data, tecnico, original_oid, taxon)
        VALUES ('UPDATE', current_timestamp, current_user, old.oid, old.taxon);
        RETURN old;
    END IF;
END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION taxon_history_insert() RETURNS trigger AS
$BODY$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO history.taxon(operacao, data, tecnico, original_oid, taxon)
        VALUES ('INSERT', current_timestamp, current_user, new.oid, new.taxon);
        RETURN new;
    END IF;
END;
$BODY$
LANGUAGE plpgsql;

create TRIGGER taxon_history_delete
Before DELETE ON taxon
FOR EACH ROW EXECUTE PROCEDURE taxon_history_delete();

create TRIGGER taxon_history_update
Before UPDATE ON taxon
FOR EACH ROW EXECUTE PROCEDURE taxon_history_update();

create TRIGGER taxon_history_insert
AFTER INSERT ON taxon
FOR EACH ROW EXECUTE PROCEDURE taxon_history_insert();


Can you Mirror an Microsoft SQL compact Edition database We have a PLC inputting summary data into a Microsoft Compact SQlce Edition database. We would like to run queries to on this data, but are unable due to the type of database it is, compact. It, as far as I understand, exposes nothing to the network so we're unable to connect with ODBC connection, or anything of that sort. The company that put it in suggested a database mirror to an existing SQL server database we already have. Which seems plausible except for the extra overhead, which might cause some latency issues. Is it possible to Do a database mirror with the SQLce as the principle database? I have seen nothing on-line that makes me think this can be done, in fact I've seen hints that it can't. I am not set on Database mirror we just need a way to get the information off this device.

You can copy the database file to the desktop and use a tool like SQL Compact Toolbox to query the data
Or you could place a copy of the file on the SQL Server disk drive, and acces it via some SQLCLR code (this will allow you to mix with SQL server data)
Or you can use some synchronization mechanism to transfer the data to SQL server (requires programming/configuration)

Mirroring is not an option at all


ORACLE12 - Connect Via SqlPlus I have a local installation of Oracle
Before i can connect directly via SqlPlus
but now when i try to connect i have this error :

  ERROR:
  ORA-01034: ORACLE not available
  ORA-27101: shared memory realm does not exist
  Linux-x86_64 Error: 2: No such file or directory
  Additional information: 3640
  Additional information: 684464879
  Process ID: 0
  Session ID: 0 Serial number: 0

Can you tell me what is the cause of this Error ?
how can i solve it ?

Make sure the database is actually running, and if not, start it. If you have an init script, then it's typically something like:

sudo service oracle start

This has been asked before for Oracle 11g - the answers and comments to that question may be helpful here:
ORA-01034: ORACLE not available in oracle 11g


sp_whoisactive keeps returning create procedure statements When I run sp_whoisactive on my server it often shows create statements being run for stored procedures but this doesn't make any sense since the procedure already exists.

See the following example:

dd hh:mm:ss.mss session_id  sql_text
00 00:00:00.120 103 CREATE PROCEDURE [dbo].[TempGetStateItemExclusive3]
00 00:00:00.140 274 CREATE PROCEDURE [dbo].[TempGetStateItemExclusive3]
00 00:00:00.133 324 CREATE PROCEDURE [dbo].[TempGetStateItemExclusive3]

This is from one run of sp_whoisactive it looks like there are three sessions creating the procedure. Is this something I am misinterpreting or if not how can I find out where this is coming from? These create statements are blocking actual inserts.

I am executing

EXEC sp_WhoIsActive
     @find_block_leaders = 1,
     @sort_order = '[blocked_session_count] DESC',
     @get_full_inner_text = 1

The batch being executed is (which is a stored procedure from asp.net SQL Server based session state but I am seeing the exact same thing for other stored procedures as well)

CREATE PROCEDURE [dbo].[TempGetStateItemExclusive3]
        @id         tSessionId,
        @itemShort  tSessionItemShort OUTPUT,
        @locked     bit OUTPUT,
        @lockAge    int OUTPUT,
        @lockCookie int OUTPUT,
        @actionFlags int OUTPUT
AS
    DECLARE @textptr AS tTextPtr
    DECLARE @length AS int
    DECLARE @now AS datetime
    DECLARE @nowLocal AS datetime

    SET @now = GETUTCDATE()
    SET @nowLocal = GETDATE()

    UPDATE dbo.ASPStateTempSessions
    SET Expires = DATEADD(n, Timeout, @now),
        LockDate = CASE Locked
                      WHEN 0 THEN @now
                      ELSE LockDate
                   END,
        LockDateLocal = CASE Locked
                           WHEN 0 THEN @nowLocal
                           ELSE LockDateLocal
                        END,
            @lockAge = CASE Locked
                WHEN 0 THEN 0
                ELSE DATEDIFF(second, LockDate, @now)
                END,
            @lockCookie = LockCookie = CASE Locked
                WHEN 0 THEN LockCookie + 1
                ELSE LockCookie
                END,
            @itemShort = CASE Locked
                WHEN 0 THEN SessionItemShort
                ELSE NULL
                END,
            @textptr = CASE Locked
                WHEN 0 THEN TEXTPTR(SessionItemLong)
                ELSE NULL
                END,
            @length = CASE Locked
                WHEN 0 THEN DATALENGTH(SessionItemLong)
                ELSE NULL
                END,
            @locked = Locked,
            Locked = 1,

            /* If the Uninitialized flag (0x1) if it is set,
               remove it and return InitializeItem (0x1) in actionFlags */
            Flags = CASE
                WHEN (Flags & 1) <> 0 THEN (Flags & ~1)
                ELSE Flags
                END,
            @actionFlags = CASE
                WHEN (Flags & 1) <> 0 THEN 1
                ELSE 0
                END
        WHERE SessionId = @id
        IF @length IS NOT NULL BEGIN
            READTEXT dbo.ASPStateTempSessions.SessionItemLong @textptr 0 @length
        END

        RETURN 0

Please show us the parameters of the execution of SP_whoIsActive. I remember there was a parameter switched between showing the actual statement or the batch/procedure that contained that statement.

If I'm not wrong:

@get_full_inner_text = 0 -- 1-shows SP/batch, 0-shows statement;

should be the one.
Switch it to 0 and see if it's changing the way the output is shown.


MySql | How to compute this I have this table

Grade        Lec         Lab
1.5           3           0
1.5           3           0
1.5           2           1

The formula will be like this

1.5 * 3
1.5 * 3
1.5 * (2+1) = 13.5 / 9 = 1.5

13.5 is the total then 9 is the total sum of the lec + lab

What query should I use?

How can i add this to query WHERE Lec + Lab = 15. If lec + lab is 15 above will show but if lower than 15 it will not show then i want to limit the show in 20


SSRS : How to give parameter values in URL Http requests? I created a dataset in an SSRS report and this dataset uses a parameter. Here is the request:
select { [Measures].[ID Distinct Count]} on COLUMNS,
non empty filter({ [DIM ANESTH].[PBM ANE NOM].[PBM ANE NOM] },
not InStr( [DIM ANESTH].[PBM ANE NOM].CURRENTMEMBER.MEMBER_VALUE, 'Unknown') = 1) on ROWS
from BDD_PAT_ANESTH where ( StrToSet(@param_req, Constrained))

param_req is a parameter name I gave, and SSRS created a corresponding report parameter and joined both. Here are some screenshots:

All it works, but I want to call the report server via http requests (in C#), and provide with some parameter values. Usually, SSRS http URLs finish by the parameters : ...&param1=value1&param2=value2 and so on. But I don't see how I can provide with the value "[DIM ANESTH].[ID].&[3]" for example.
An other way could be to define a custom parameter whose values are integers, and to define a second parameter whose origin is the first one and whose values are some members like "[DIM ANESTH].[ID].&[3]".
Do you know how I can achieve either method (I think the first one could be easier).
thank you.
PS: I found this about parameters but it seems to be only partially relevant.
EDIT : @McNets
I tried to follow your advice, but with no success: considering the third screenshot, the one with default values, I tried to put in my browser this url :

http://localhost/reportService?%2fSSRS%2fReport6_tryParamFrom0&rs:Command=PDF&param_req=5

and I got this error:

Reporting Services Error Default value or value provided for the
report parameter 'param_req' is not a valid value.
(rsInvalidReportParameter)

Please see : here from microsoft.
What am I missing?

Done.
Defining a parameter 'param2' whose values are integers and captions are strings, the point is the MDX query:
select {[Measures].[ID Distinct Count]} on COLUMNS,
  non empty filter({[DIM ANESTH].[ID].MEMBERS  },
  [DIM ANESTH].[ID].CURRENTMEMBER.MEMBERVALUE = @param2 ) on ROWS from BDD_PAT_ANESTH


Identify Full Text Catalog Files I'm trying to write a query for a report to show how much free space is in our data files so we can plan storage for the future. I'm pulling data out of sys.database_files, and I can narrow it down to just type 0 (Rows) data - we're not worried about log growth at this point.

The problem I'm having is that a number of databases have a full text catalog. The Full Text Catalog file is also displaying as type 0. We want to exclude these from the report as well. Is there anyway we can tell from sys.database_files (or join back to that) so we can just see the data files?

I notice any newly created Full Text Catalog files in SQL Server 2016 will have type = 4, but that doesn't really help me on 2014 (or upgraded databases in 2016).

I believe type = 4 only applies to pre-2008 fulltext files which have been upgraded, because since then there has been no way to create separate fulltext files -- only separate filegroups. (See type = 4 doc for SQL 2012 at https://msdn.microsoft.com/en-gb/library/ms174397(v=sql.110).aspx which confusingly says the same as for 2016 except version number.)

Something like http://www.connectsql.com/2011/01/sql-server-how-to-get-physical-path-of.html may technically do what you are looking for, but won't actually be relevant for your report.


Qcache_free_memory not full yet I get alot of Qcache_lowmem_prunes I just started dabbling with the query cache for our CMS.

Can anyone tell me (or at least give a good guess) why I get a lot of Qcache_lowmem_prunes when more than half of Qcache_free_memory is free?

query_cache_size=512M
query_cache_limit=1M

This is how it looks after about 12 hours

show status like '%qcach%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 10338     |
| Qcache_free_memory      | 297348320 |
| Qcache_hits             | 10254104  |
| Qcache_inserts          | 6072945   |
| Qcache_lowmem_prunes    | 725279    |
| Qcache_not_cached       | 2237603   |
| Qcache_queries_in_cache | 48119     |
| Qcache_total_blocks     | 111346    |
+-------------------------+-----------+

This is how it looked after flush query cache;

show status like '%qcach%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 1         |
| Qcache_free_memory      | 443559256 |
| Qcache_hits             | 10307015  |
| Qcache_inserts          | 6115890   |
| Qcache_lowmem_prunes    | 725279    |
| Qcache_not_cached       | 2249405   |
| Qcache_queries_in_cache | 26455     |
| Qcache_total_blocks     | 54490     |
+-------------------------+-----------+

The query cache is a very nice feature, but don't be tempted to pay too much attention to it and don't be tempted to make it too large.  Understanding some of its internals will probably help in that regard.

The query cache starts out as one big contiguous chunk of available memory.  Then "blocks" are carved out of this big block:

each cached query takes a block
its companion resultset takes a block
each table referenced by any cached query (no matter how many queries referencing that table are in the cache) also takes a block, one per table.

Block size is dynamic, but the server allocates a minimum of query_cache_min_res_unit bytes per block, with a typical default of 4096 bytes.

Any time queries, their accompanying results, and table references are removed from the cache, either by becoming invalidated by the underlying tables changing or by pruning to make room for newer queries, this leaves new holes the size of however large those blocks were, and the number of "free blocks" usually increases... although if two or more contiguous blocks are freed, the number of "free blocks" only increases by 1, and the "free blocks" won't increase at all if the newly-freed blocks are contiguous with an already free block -- the size of that free block just becomes larger.  Any open block of free memory in the query cache is counted as 1 free block.

Of course, a free block smaller than query_cache_min_res_unit won't be used at all.

So, the query cache fragments.  If the server wants to cache a new query and no free blocks of sufficient size can be arranged (that description is deceptively simple, because the underlying algorithm is complicated), something else has to be pruned... that's your Qcache_lowmem_prunes.  There's a "least-recently-used" (LRU) algorithm that decides what gets pruned.

It would be sensible to ask why the server doesn't defragment the memory... but that wouldn't make sense.  The query cache helps when it can but it's not at all strategic.  You don't want to invest processing time (especially time spent in a global lock) with unnecessary maintenance tasks.

It would be counter-productive for the server to spend time rearranging -- defragmenting -- the memory in the query cache, since the cached results are constantly changing and the whole point of the cache is to improve performance.

The global lock is a very good reason why you don't want to use an excessively large query cache... the server will spend too much time there as queries wait their turn to see if they happen to be cached and your performance will suffer.

But the qcache_free_blocks is essentially an indicator of free-space fragmentation.  That's now many noncontiguous blocks of available memory exist in the query cache.  For a new query to be inserted into the cache, there has to be a a large enough chunk of free space to contain the query, its results, and (sometimes) its table references.  If there isn't, then something else has to go... which is what you're seeing.  Note, again, that the available space doesn't always necessarily have to be contiguous (from what I can tell by reading the source code), but not every hole will be filled when there's fragmentation.

But the fragmentation tends to level out over time, for a given workload, since nothing usually stays in the query cache for as long as you might expect.

This is because, in some ways, the query cache is brilliant in its simplicity.

Any time the data in a table referenced by a cached query changes, all of the queries that involved that table are removed from the cache -- even if the change doesn't impact the cached results.  This is even true if a table changes, but doesn't change, as in the case of an InnoDB transaction that is rolled back.  The query cache entries referencing that table were already purged.

Also, the query cache is checked for each incoming query before the server actually parses the query.  The only thing that will match is another query that was precisely the same, byte-for-byte.  SELECT * FROM my_table and select * from my_table are not byte-for-byte identical, so the query cache doesn't realize they're the same query.

FLUSH QUERY CACHE does not empty the query cache.  It defragments the query cache, which is why Qcache_free_blocks becomes "1."  All of the free space is consolidated.

RESET QUERY CACHE actually flushes (clears out all of the contents of) the query cache.

FLUSH STATUS clears the counters, but this isn't something you want to do routinely because this zeroes out most of the status variables in SHOW STATUS.

Here are some quick demonstrations.

Baseline:

mysql> show status like '%qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 67091120 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 1        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+

Run a query...

mysql> select * from junk where id = 2;

The total blocks has increased by 3, inserts by 1 and queries in cache is 1.

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 67089584 |
| Qcache_inserts          | 1        |
| Qcache_queries_in_cache | 1        |
| Qcache_total_blocks     | 4        |
+-------------------------+----------+

Run the same query, but with different capitalization...

mysql> SELECT * FROM junk where id = 2;

This query was cached separately.  Total blocks only increased by 2 because we already had a block allocated for the table.

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 67088560 |
| Qcache_inserts          | 2        |
| Qcache_queries_in_cache | 2        |
| Qcache_total_blocks     | 6        |
+-------------------------+----------+

Now, we change a different row in the table.

mysql> update junk set things = 'items' where id = 1;

Both queries and the table reference are invalidated from the cache, leaving us with 1 contiguous free block, all of the cache memory freed, and all of the free space consolidated in one block.

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 67091120 |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+

MySQL will not store a query in the cache that isn't deterministic -- such as SELECT NOW(); or any query that you tell it specifically not to cache.  SELECT SQL_NO_CACHE ... is the directive to tell the server not to store the results in the cache.  It's useful for benchmarking the true execution time of a query when the cache is giving you a deceptively fast response on subsequent executions.


Restore database from tape error 15105, cannot open backup device I'm using HP Data Protector software to backup my SQL databases. When i try to restore a database from tape i get the following error.

When i click on script, it says this:

RESTORE DATABASE [ICT_TEST] FROM  TAPE = N'Data Protector_STD2008_ICT_TEST_213007077_0' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE LOG [ICT_TEST] FROM  DISK = N'E:\SQLbackup STD2008\ICT_TEST\ICT_TEST_backup_2015_02_27_224513_2053343.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

(I start SQL Management Studio as Domain Administrator)

Does anybody have a tip on how to resolve this issue?

You need to use the HP Data Protector console software to perform the restore operation.


What is the ideal composite index for this query? What is the ideal Composite Index in MySQL for the following query on an InnoDB Table1?

SELECT ColA
FROM Table1
WHERE ColB = 0
AND ColC = 0
AND ColD = 0

ColA is the left-most Primary Key.

ColB has fewer "0" rows than ColC, and ColC has fewer "0" results than ColD.

When using the composite index (ColB, ColC, ColD) on the query above, MySQL is instead using an Intersect of Indices ColB, ColC, and ColD.

Here is the requested SHOW CREATE TABLE Table1 ; results:

CREATE TABLE `Table1` (
 `ColA` int(11) unsigned NOT NULL,
 `ColE` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColF` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColB` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColG` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColC` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColH` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColD` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColJ` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColK` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColM` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColP` mediumint(8) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`ColA`),
 KEY `ColB` (`ColB`),
 KEY `ColG` (`ColG`),
 KEY `ColC` (`ColC`),
 KEY `ColH` (`ColH`),
 KEY `ColD` (`ColD`),
 KEY `ColJ` (`ColJ`),
 KEY `ColK` (`ColK`),
 KEY `ColB_2` (`ColB`,`ColC`,`ColD`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

INDEX(ColB, ColC, ColD)

with the columns in any order, is good.  This takes care of all of the WHERE clause.

Potentially even better is a "covering" index:

INDEX(ColB, ColC, ColD, ColA)

with ColA last.

"Covering means that all the columns in the SELECT are in the index.  That way, the query can be performed in the BTree for the index, and not have to jump over to the data for anything else.

If the table is InnoDB, then the first index will contain ColA.  This is because any secondary index implicitly contains the PRIMARY KEY.

Even in a covering index, the order is important.

Index Merge Intersect is almost never faster than a Composite index that handles the correct columns.  (I think of Index Merge as a clue that you should add a Composite index.)

More on building optimal indexes.

Do not use DESC, it is not as descriptive as SHOW CREATE TABLE !

Drop KEY ColB (ColB) -- it is now redundant.


Error: 468 trying to connect to SQL server with SQL Server management studio I'm trying to connect to SQL Server 12 with SQL Server management studio 2014.

The connection is succesful, but when I try to see properties of a specific database with mouse right button I get this error:

  No se puede resolver el conflicto de intercalación entre
  "SQL_Latin1_General_CP1_CS_AS" y "Modern_Spanish_CI_AS" de la
  operación equal to. No se pudo usar la vista o función
  'dbo.sysdac_instances' debido a errores de enlace. (Microsoft SQL
  Server, Error: 468)

It seems to be a problem related with collation.
How I could specify the proper collation for the connection?

How I could specify the proper collation for the connection?

You can't. And even if you could, that wouldn't help here since the problem is a conflict between two different string columns. The only aspect of this problem that is specific to SSMS is that SSMS is selecting from a specific system view — msdb.dbo.sysdac_instances — that is joining on these two string columns, expecting that they will always be using the same collation (and in most cases they do).

Please see my answer to this related / duplicate question:

Collation conflict … Could not use dbo.sysdac_instances

There I explain why this is happening and what your options are. This should be caused by having a different instance-level collation than the msdb collation. This can happen when restoring / attaching msdb created in another instance that had a different instance-level collation.

The option that should have the least amount of impact would be updating the definition of the dbo.sysdac_instances system view. Not ideal, but given the other options, it is also the easiest to reverse should it prove to be a problem, though I don't see how it could be a problem. In that example I used Latin1_General_CI_AS for the fix as it was the instance-level collation. In this case, I would use SQL_Latin1_General_CP1_CS_AS as I am guessing it is your instance-level default collation.

Please also see the following post of mine if you are considering updating the collation of the instance and database(s):

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

Also, this is another example of why we need to have a special collation similar to DATABASE_DEFAULT but working at the instance level — INSTANCE_DEFAULT — so that there can be a generic solution to this type of issue. This would allow the msdb.dbo.sysdac_instances_internal table to be created using:

...
[instance_name]   sysname COLLATE INSTANCE_DEFAULT,
...

Or, it could be that the msdb.dbo.sysdac_instances view has this at the end:

LEFT JOIN sys.databases sd
    ON dac_instances.instance_name = sd.name COLLATE INSTANCE_DEFAULT

So please vote for the following suggestion:

Add special collation INSTANCE_DEFAULT to work like COLLATE DATABASE_DEFAULT but uses instance's default collation


Postgres: using existing index to support GROUP BY query on custom function I'm trying to optimize a query that looks like this:

SELECT f(a), sum(b) FROM table GROUP BY f(a) ORDER BY f(a) LIMIT 10

I have a b-tree index on column a, and f is a custom function where I know that if x > y, then f(x) >= f(y).

It seems like in a perfect world one ought to be able to execute this query using the index on column a to avoid reading more rows than necessary to calculate the first 10 groups, since an index scan on a will return all the rows in the first 10 groups before it starts returning rows not in the first 10 groups.

I know I could optimize this by creating an index on f(a), but I have multiple function fs so don't want to pay the overhead of doing that for each one.

Is there a way to re-write this query (or my function f, for that matter) so that it could use my existing index on column a to avoid reading the whole table?

I'm not yet sure if this is doable in pure SQL (probably - yes), but here is the brute force solution using cursors. This is just a (working) draft, with some twiddling and dynamic SQL you could add more parameterers, like function name.

create or replace function top_f(_limit int)
returns table (f text, sum int)
language plpgsql
AS $$
declare
    _a text;
    _fa text;
    _asum int  := 0;
    _fasum int :=0;
    _cnt int   := 0;
    _cur cursor for SELECT a, sum(b) FROM t GROUP BY a ORDER BY a;
begin
    open _cur;
    LOOP
        fetch _cur into _a, _asum;
        if upper(_a) <> _fa then
            return query select _fa, _fasum;
            _fasum := 0;
            _cnt   := _cnt + 1;
        end if;
        exit when _cnt >= 10;
        _fasum := _fasum + _asum;
        _fa := upper(_a);
    END LOOP;
end;
$$;

And here is how it works. Please note run times which are the point here.

(filip@[local]:5432) filip=# SELECT a, sum(b) FROM t group by a order by a limit 12;
     a     | sum
-----------+-----
 a         |  11
 A         |  11
 Aachen    |  11
 Aachens   |  11
 Aaliyah   |  11
 Aaliyahs  |  11
 aardvark  |  11
 aardvarks |  22
 Aaron     |  11
 Aarons    |  11
 aback     |  11
 abacus    |  11
(12 rows)

Time: 0.838 ms

(filip@[local]:5432) filip=# SELECT * from top_f(10);
     f     | sum
-----------+-----
 A         |  22
 AACHEN    |  11
 AACHENS   |  11
 AALIYAH   |  11
 AALIYAHS  |  11
 AARDVARK  |  11
 AARDVARKS |  22
 AARON     |  11
 AARONS    |  11
 ABACK     |  11
(10 rows)

Time: 1.524 ms

(filip@[local]:5432) filip=# SELECT upper(a), sum(b) FROM t GROUP BY upper(a) ORDER BY upper(a) LIMIT 10;
   upper   | sum
-----------+-----
 A         |  22
 AACHEN    |  11
 AACHENS   |  11
 AALIYAH   |  11
 AALIYAHS  |  11
 AARDVARK  |  11
 AARDVARKS |  22
 AARON     |  11
 AARONS    |  11
 ABACK     |  11
(10 rows)

Time: 2061.320 ms (00:02.061)

Update: here is the same with parametrized function name:

CREATE OR REPLACE FUNCTION top_f(_function name, _limit int)
RETURNS TABLE (f text, sum int)
LANGUAGE plpgsql
AS $$
DECLARE
    _a text;
    _fa text;
    _newfa text;
    _asum int  := 0;
    _fasum int :=0;
    _cnt int   := 0;
    _cur cursor for SELECT a, sum(b) FROM t GROUP BY a ORDER BY a;
BEGIN
    OPEN _cur;
    LOOP
        FETCH _cur INTO _a, _asum;
        EXECUTE 'SELECT ' || quote_ident(_function) || '($1)' INTO _newfa USING _a;
        IF _newfa < _fa then
            raise exception 'Function is not monotonic: f(%) = % < %', _a, _newfa, _fa;
        ELSIF _newfa > _fa then
            return query select _fa, _fasum;
            _fasum := 0;
            _cnt   := _cnt + 1;
            exit when _cnt >= _limit;
        END IF;
        _fasum := _fasum + _asum;
        _fa := _newfa;
    END LOOP;
END;
$$;


Deleting other files during maintenance plan I have an SQL Server 2012 maintenance plan which does the normal stuff (rebuild indexes and update statistics, back up DBs, deletes .bak files older than X days, etc). Everything is working fine with it, except one Maintenance Cleanup Task:

This should be deleting all .7z archives (equivalent to .zip or .rar) which get accidentally left in the nightly backups directory (despite our best efforts, we keep ending up with some people leaving these files on the server...), but we still have old .7z files present in subdirectories (e.g. E:\Backups\foo\bar.7z ) after the maintenance plan runs.

There are no errors in the SQL Server Logs.

I suspect this is because the "Delete files of the following types: Backup files" option is restricting the file types which can be deleted. I've also tried changing that to "Maintenance Plan text reports" with the 7z extension, with no change.

Is it possible to have a maintenance plan delete arbitrary (non-SQL Server) files during a Maintenance Cleanup Task? If so, any idea what I'm doing wrong?

As per MS here

  The Maintenance Cleanup task removes files related to maintenance
  plans, including database backup files and reports created by
  maintenance plans.

  By using the Maintenance Cleanup task, a package can remove the backup
  files or maintenance plan reports on the specified server. The
  Maintenance Cleanup task includes an option to remove a specific file
  or remove a group of files in a folder. Optionally you can specify the
  extension of the files to delete.

I have not deleted any other extension files via maintenance plans and seems from above that a non sql file may not be deleted via Maint'ce plans:

What we use is a VB script and schedule it via SQL agent job:

Select type as :- Type ActiveX scipt when you create the step for below code in SQL agent job:

On Error Resume Next
    Dim fso, folder, files, sFolder, sFolderTarget
    Set fso = CreateObject("Scripting.FileSystemObject")

    'location of the  files
    sFolder = "B:\FolderA\ABC\"

    Set folder = fso.GetFolder(sFolder)
    Set files = folder.Files

    'used for writing to textfile - generate report on files deleted
    Const ForAppending = 8

    'you need to create a folder named "scripts" for ease of file management &
    'a file inside it named "LOG.txt" for delete activity logging
    Set objFile = fso.OpenTextFile(sFolder & "\Report\LOG.txt", ForAppending)

    objFile.Write "================================================================" & VBCRLF & VBCRLF
    objFile.Write "                     FILE REPORT                " & VBCRLF
    objFile.Write "                     DATE:  " &    FormatDateTime(Now(),1)   & "" & VBCRLF
    objFile.Write "                     TIME:  " &    FormatDateTime(Now(),3)   & "" & VBCRLF & VBCRLF
    objFile.Write "================================================================" & VBCRLF

    'iterate thru each of the files in the respective folder
    For Each itemFiles In files
       'retrieve complete path of file for the DeleteFile method and to extract
            'file extension using the GetExtensionName method
       a=sFolder & itemFiles.Name

       'retrieve file extension
       b = fso.GetExtensionName(a)
           'check if the file extension is abc(whatever is the extension of you're file
           If uCase(b)="abc" Then

               'check if the files are older than 1days
               If DateDiff("d",itemFiles.DateCreated,Now()) >= 1 Then

                   'Delete any old files to cleanup folder
                   fso.DeleteFile a
                   objFile.WriteLine "FILE DELETED: " & a
               End If
           End If
    Next

    objFile.WriteLine "================================================================" & VBCRLF & VBCRLF

    objFile.Close

    Set objFile = Nothing
    Set fso = Nothing
    Set folder = Nothing
    Set files = Nothing

Note* Above scripts work such that you need to create folder Report within you're base folder location and within that place a file LOG.txt which captures or logs the details of files deleted by the process. You can use this additional feature which we use to track, else modify the script as per needs!


Can I write a Select query to update the data too Can I write a Select query under a jdbc connection ,having a resultset object and which would update the records on just passing the select query them in my sql.

This is not possible, at least not with plain JDBC.

But, an ORM might give you what you are looking for. However, the use of ORMs is controversial. Have a look at https://stackoverflow.com/questions/452385/what-java-orm-do-you-prefer-and-why for an interesting collection of opinions. You can find a few example ORMs in there too.


How to model the relationship between these three tables This is my first database, it is for a nutritional info meal tracker program. I have created a model in mySql work bench but I'm not sure if I have the relationships right. See image below..

My thinking is that each user would have a new table for each day and each day would have multiple meals. I was thinking that for each day the meal number would go back to 1 as it is the primary key. Would this be a problem i.e. have multiple meals with the same number or would I be able to identify them from the day and the meal number and if so how would I do that? I'm not sure if this is even the best way to design this database so any help would be appreciated. Cheers.

From  Mr.Brownstone's comment I've come up with this new design. Is it possible to have the user_name in MEALS automatically filled in from USER?

I personally would not design it so that I had to create new tables all of the time. As you already track the user_id in the meals table, I would just add another column for the date to it and remove the day table altogether. I would advise to not make the date_time attribute as the primary key though because it is highly probable that more that one person will want to eat on the same day...

You also have a redundant attribute user_user_id in the meals table because it is already included as user_id.

The user_name field does not belong in the meals table because it is an attribute of a user. You can however include the user_name in the query used to obtain meals items like so:

SELECT `date_time`
     , u.`user_id`
     , u.`user_name`
     , `calories`
     , `fat`
     , `carbohydrates`
     , `carbs_which_sugar`
     , `fibre`
     , `protein`
  FROM `meals` AS m
  JOIN `users` AS u ON u.`user_id` = m.`user_id`;


Why do I have different usernames from mysql.user and select user()? With 10.4.6-MariaDB on my Mac OS 10.14.5
Logged in as root I can see the list of users on my server with

MariaDB [(none)]> select Host,User from mysql.user;
+--------------+-----------+
| Host         | User      |
+--------------+-----------+
| localhost    |           |
| localhost    |           |
| localhost    | root      |
| localhost    | user_Name |
| PCName.local |           |
+--------------+-----------+
5 rows in set (0.001 sec)

but when logged in as user_Name and I check the current user

MariaDB [(none)]> select user()
    -> ;
+-------------------------------+
| user()                        |
+-------------------------------+
| user_Name@localhost@localhost |
+-------------------------------+
1 row in set (0.000 sec)

That doesn't seem right to @localhost written twice.

So my question is why this happening?

What you should run is this

Please run this query:

SELECT USER(),CURRENT_USER();

What do these functions return ???

USER() reports how you attempted to authenticate in mysqld
CURRENT_USER() reports how you were allowed to authenticate by mysqld

Please note that CURRENT_USER() should return something from mysql.user. You said that USER() returns user_Name@localhost@localhost. If CURRENT_USER() comes back with the same response as USER(), then MariaDB has a bug.

Since USER() reports how you attempted to authenticate in mysqld, my guess would be that's probably how you typed it.

mysql -u user_Name@localhost

You should login to MariaDB like this

mysql -u user_Name -hlocalhost

Try it again and see. I discussed this before in this forum : MySQL error: Access denied for user 'a'@'localhost' (using password: YES)


Login in Always On Availability Groups I am new to Always On Availability Groups and I am little bit confused with creation of logins.

Let's assume an environment NODE1(Primary) & NODE2(secondary) & NODE3(Stand by) & VAEWI(Listener).

I need to create a SQL authentication login.

For this, I assume I need to create it by connecting to listener that which will sync in all of the nodes.
I need to create a Windows authentication.

For this I think, as all of the nodes are window-clustered before creating the availability group. So, we need to create a Windows user 1st in primary node and then create a Windows authentication by connecting to the listener.

Can anyone let me know if my assumptions are true?

Putting in a simple language :

A login gives you access to server.

A user gives you access to database.

A login must map a user so that you can access the database.

For SQL Authentication, you should create the login with SAME SID and map it to your user database/s on all the nodes with proper permissions (follow the principle of least privileged). This script will help you or this SSMS addin from sqlskills.

For Windows Authentication, you should create a login and map it to your user database. Create/transfer the same login to other nodes.

A listener is a virtual interface and has nothing to do with logins or users.


Online Schema migration in MySQL : When to start? I'm the tech lead of a Social Media SAAS, where we're currently experiencing a big increase in data amount we're storing in our mySQL-database.

We're having a single MySQL db, and besides many really small tables, we have two tables with approx 500k-1.5m rows.

Currently, when performing DDL queries (adding new columns to these tables), we have to deal with quite a long execution time (around 4-5 mins).

I started to search for online schema migration tips and found the following:

Alter table on live production databases
mysql change schema on the fly
PT Online Schema Change
OpenARK: Online Alter Table
Facebook's Online Schema change

Although these tactics are worth investigating, I started to feel unconfortable at all: I don't think, that tables with such a low number of rows have to use such advanced methods. (What about really huge tables then?)

So I was wondering, if anyone of you have tips regarding the mysql.cnf itself, or other performance tips for the ddl queries?

I think that Percona's pt-online-schema-change might be a better solution for you as it can do an ALTER with very little downtime. It does, however, require adding a TRIGGER to the table.

Runnig DDL on your table will lead you into rebuilding your table, mysql is a row based engine so you will suffer the index rebuild this can be quite expensive depending on your engine, table complexity is factor as well.


Oracle SQL Creating Copy of Table Using DBMS_metadata so I am trying to create a copy of a table using dbms_metadata. I know you can get the DDL of a table by executing DBMS_METADATA.get_DDL('Table','TABLENAME') so my question now is how can I use this to create a new identical table?

I know you can easily create a copy of a table with create table t1 select * from table2; etc. So how can I make use of dbms_metadata to create a new identical table? Any advice would be greatly appreciated. Thanks!

Instead of using simply text based replace, you can do this with the metadata API, which is a more robust solution. Lets say you have a table X.TABLE1:

create table x.table1 (id number, name varchar2(100), primary key(id));

You want to recreate it as Y.TABLE2, you could do the following:

set serveroutput on
declare
  metadata_handle number;
  transform_handle number;
  ddl_handle number;
  result_array sys.ku$_ddls;
begin
  metadata_handle := dbms_metadata.open('TABLE');
  transform_handle := dbms_metadata.add_transform(metadata_handle, 'MODIFY');
  dbms_metadata.set_remap_param(transform_handle, 'REMAP_NAME', 'TABLE1', 'TABLE2');
  dbms_metadata.set_remap_param(transform_handle, 'REMAP_SCHEMA', 'X', 'Y');

  ddl_handle := dbms_metadata.add_transform(metadata_handle, 'DDL');

  dbms_metadata.set_filter(metadata_handle, 'SCHEMA', 'X');
  dbms_metadata.set_filter(metadata_handle, 'NAME', 'TABLE1');

  loop
    result_array := dbms_metadata.fetch_ddl(metadata_handle);
    exit when result_array is null;
      for i in result_array.first..result_array.last loop
      dbms_output.put_line(result_array(i).ddltext);
    end loop;
  end loop;
  dbms_metadata.close(metadata_handle);
end;
/

Which results in:

CREATE TABLE "Y"."TABLE2"
   (    "ID" NUMBER,
    "NAME" VARCHAR2(100),
     PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"

Obviously you can pass it to execute immediate, or just spool it.


When was a Database taken Offline I am trying to find a way to figure out when was couple of my sql databases were taken offline.

I checked the the logs but could not find any such info and moreover there is no default trace enabled.

I just have a info that earlier there was a DBA who took that offline, but no emails or a written communication as such..

Can we find an info on this?

please suggest, thanks!

You can discover all of these events that are in the current event log cycle using sp_readerrorlog:

EXEC sys.sp_readerrorlog @p1 = 0, @p2 = 1, @p3 = N'OFFLINE';

You can cycle through values of @p1 if you don't find it in the current event log. By default you should be able to read the current and prior 6 error log files, so use 0-6 as the arguments there to go back as far as possible (on my system I could not get 0/NULL to aggregate across all log files; YMMV).

Will return something like this:

LogDate        ProcessInfo Text
-------------  ----------- ---------------------------------------------------------
yyyy-mm-dd...  spid72      Setting database option OFFLINE to ON for database 'foo'.

There's a chance, of course, that the error log gets populated enough that the event(s) happened before the current set of error logs. In that case, you are out of luck. To keep a longer running history in the future, you can change the number of error logs that are kept. In Object Explorer, expand Management, right-click SQL Server Logs, and choose Configure. There you can change error log file recycling settings, including keeping up to the previous 99 files. Also see this answer.

Note that sp_readerrorlog is undocumented and unsupported, though many people have written about it. In the end, the error log files are just plain text files, so you could write your own PowerShell, CLR etc. that just parses the files and returns the same information. You can determine where the error log files are for this instance using:

SELECT SERVERPROPERTY('ErrorLogFileName');

The files will be named ERRORLOG, ERRORLOG.1, ERRORLOG.2, etc. You can go and open the files in a basic text editor to see the structure, though I would be cautious about opening the current file in use (ERRORLOG).


JSON_VALUE in SQL-server 2014 I am looking for an equivalent functionalities of JSON_VALUE in SQL Server 2014. Can any of you post an alternate of the below code which is supported in SQL Server 2014

SELECT JSON_VALUE(ERROR_MSG, '$[0].applicationName') AS APPLICATION_NAME
FROM tablename

It errors out as below when run SQL 2014

  'JSON_VALUE' is not a recognized built-in function name.

JSON support was not introduced until SQL Server 2016, so you'll need to use custom functions. Check out Phil Factor's article on this where he provides some code for parsing JSON in earlier versions of SQL Server.


Will the job schedule obey Daylight Saving rules? We have an Oracle 11g Scheduler Job that was created with a start date of 08/AUG/15 05:00:00.100000000 PM AUSTRALIA/SYDNEY and a Repeat_INTERVAL of FREQ=DAILY. Sydney, as a city, observes Daylight Saving. I'm wondering if this job schedule will honour the change in Daylight Saving, or is the AUSTRALIA/SYDNEY component meaningless?

If you want the job to follow DST rules then you need to use a named timezone that follows the DST rules you want to use.

Yes, as you have provided the named time zone in your start date value.

Reference:DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained (Doc ID 467722.1)


For a complex query how do i increase the time SQL Server searches for the best execution plan Our system generates SQL with a very large number of tests. I suspect on some occasions it is taking too long to find the ideal execution plan. So SQL just picks the best plan it has found so far which then takes a long time to run. Is it possible to increase the time SQL searches for the best plan?

There are some undocumented trace flags that allow one to increase or remove some of the limits that the optimizer uses when determining whether a "good enough" plan has been found.

However, all of the results I've ever seen show that plan quality is only minimally improved, if at all, when using these flags.  There is a good reason the timeouts and limits exist.  You're almost certainly better off breaking these queries up into smaller chunks somehow (great YouTube video from Erik Darling on the subject), to make them more manageable for the optimizer to make choices about.

Additionally, these trace flags are undocumented.  So if you do find a better plan using this method (on a test system!), you should try to get that plan using conventional means, like:

rewriting the query,
adding documented query or index hints,
adding / tweaking indexes,
using a plan guide if necessary

The trace flags I'm aware can be enabled at the query level like this:

/* your query here */
OPTION
(
    QUERYTRACEON 8780,
    QUERYTRACEON 8671
)

References:

8780: Optimizer Without Borders (Part 1) by Dmitry Pilugin
8671: Optimizer Without Borders (Part 2) also by Dmitry Pilugin


Faster alternative to scalar UDF with recursion to walk tree hierarchy? I have a scalar function that traverses a simple parent-child hierarchy of customers to find the ancestor that's in charge of billing. Here's a simplified version of the schema.

CREATE TABLE Customer (CustomerID int, ParentCustomerID int, IsBillToCustomer bit)

The UDF walks up the chain until it hits the top parent, recording the IsBillToCustomer setting for each customer in the chain, then returns the CustomerID for the highest customer in the chain with IsBillToCustomer = 1.  The UDF is fast for each customer, but I need to run reports that return thousands of customers with data associated to their billing master and it takes tens of minutes for so many customers.  Is my only alternative to implement SQL Server's hierarchyID data type?  Will that even help?

Creating a SQL job that stores the data I need is not a viable solution because the data needs to be as up-to-date as possible and I can't be running a 40-minute CPU-intensive job over and over.

UPDATE:  I created a temporary table using HierarchyID and set up some indexes, then used IsDescendantOf in my query, and it still took over 13 minutes (and hammered my CPU's).

Let's start with this. Let us know if it looks something like this. Please edit as necessary. I am adding data into the table:

INSERT INTO Customer (CustomerID, ParentCustomerID, IsBillToCustomer)
select  1   ,   0   ,   0   union all
select  2   ,   0   ,   0   union all
select  3   ,   0   ,   0       union all
select  4   ,   1   ,   0   union all
select  5   ,   2   ,   0   union all
select  6   ,   3   ,   1   union all
select  7   ,   1   ,   1   union all
select  8   ,   2   ,   1   union all
select  9   ,   3   ,   1   union all
select  10  ,   1   ,   0   union all
select  11  ,   2   ,   0   union all
select  12  ,   3   ,   0   union all
select  13  ,   1   ,   0   union all
select  14  ,   2   ,   0   union all
select  15  ,   3   ,   1   union all
select  16  ,   1   ,   1   union all
select  17  ,   2   ,   1   union all
select  18  ,   3   ,   1   union all
select  19  ,   1   ,   0   union all
select  20  ,   2   ,   1   union all
select  21  ,   3   ,   1   union all
select  22  ,   1   ,   1   union all
select  23  ,   2   ,   0   union all
select  24  ,   3   ,   0   union all
select  25  ,   1   ,   1   union all
select  26  ,   2   ,   1   union all
select  27  ,   3   ,   1   union all
select  28  ,   1   ,   1   union all
select  29  ,   2   ,   0   union all
select  30  ,   3   ,   0   union all
select  31  ,   1   ,   0   union all
select  32  ,   2   ,   0   union all
select  33  ,   3   ,   0   union all
select  34  ,   1   ,   1   union all
select  35  ,   2   ,   1   union all
select  36  ,   3   ,   1   union all
select  37  ,   1   ,   1   union all
select  38  ,   2   ,   0   union all
select  39  ,   3   ,   1   union all
select  40  ,   1   ,   1   union all
select  41  ,   2   ,   1   union all
select  42  ,   3   ,   0   union all
select  43  ,   1   ,   0   union all
select  44  ,   2   ,   1   union all
select  45  ,   3   ,   1   union all
select  46  ,   1   ,   1   union all
select  47  ,   2   ,   1   union all
select  48  ,   3   ,   0   union all
select  49  ,   1   ,   0   union all
select  50  ,   2   ,   0   union all
select  51  ,   3   ,   0   union all
select  52  ,   1   ,   0   union all
select  53  ,   2   ,   1   union all
select  54  ,   3   ,   1   union all
select  55  ,   1   ,   1   union all
select  56  ,   2   ,   1   union all
select  57  ,   3   ,   0   union all
select  58  ,   1   ,   1   union all
select  59  ,   2   ,   1   union all
select  60  ,   3   ,   1   union all
select  61  ,   1   ,   0   union all
select  62  ,   2   ,   0   union all
select  63  ,   3   ,   1   union all
select  64  ,   1   ,   1   union all
select  65  ,   2   ,   1   union all
select  66  ,   3   ,   1   union all
select  67  ,   1   ,   0   union all
select  68  ,   2   ,   0   union all
select  69  ,   3   ,   0   union all
select  70  ,   1   ,   0   union all
select  71  ,   2   ,   0   union all
select  72  ,   3   ,   1   union all
select  73  ,   1   ,   1   union all
select  74  ,   2   ,   1   union all
select  75  ,   3   ,   1   union all
select  76  ,   1   ,   0   union all
select  77  ,   2   ,   1   union all
select  78  ,   3   ,   1   union all
select  79  ,   1   ,   1   union all
select  80  ,   2   ,   0   union all
select  81  ,   3   ,   0   union all
select  82  ,   1   ,   1   union all
select  83  ,   2   ,   1   union all
select  84  ,   3   ,   1   union all
select  85  ,   1   ,   1   union all
select  86  ,   2   ,   0   union all
select  87  ,   3   ,   0   union all
select  88  ,   1   ,   0   union all
select  89  ,   2   ,   0   union all
select  90  ,   3   ,   0   union all
select  91  ,   1   ,   1   union all
select  92  ,   2   ,   1   union all
select  93  ,   3   ,   1   union all
select  94  ,   1   ,   1   union all
select  95  ,   2   ,   0   union all
select  96  ,   3   ,   1   union all
select  97  ,   1   ,   1   union all
select  98  ,   2   ,   1   union all
select  99  ,   3   ,   0   union all
select  100 ,   1   ,   0   union all
select  101 ,   2   ,   1   union all
select  102 ,   3   ,   1   union all
select  103 ,   1   ,   1

So, let's run a simple CTE with the data we have. Then from here show us where we are and what we're trying to achieve:

;With Parent(CustomerID, ParentCustomerID, IsBillToCustomer)
As
(
   Select c.CustomerID, c.ParentCustomerID, c.IsBillToCustomer
    from Customer c
    WHERE c.ParentCustomerID = 0
    UNION ALL
  Select c.CustomerID, c.ParentCustomerID, c.IsBillToCustomer
    from Customer c
    inner join Customer p on p.CustomerID=c.ParentCustomerID
)

Select *
  from Parent p

Help us understand the problem. Thanks.