Problem joining log-shipped secondaries in STANDBY to AlwaysOn group I have a configuration where one database is log shipping to three different servers hosted in a disaster recovery site.  These three disaster recovery servers are joined to the same AlwaysOn availability group (AG).

In the event of failover, we recover the database on the server acting as the primary replica of the AG and then add the database to the AG using the 'Join only' synchronization option.  Since the databases on the secondary replicas are already in a non-recovered state, the operation succeeds and we end up with a database synchronized across the AG.  This is 100% great.

New problem:  Our monitoring software does not like it when databases are not in a readable state.  So while we are in our primary site and are log shipping to our NORECOVERY secondaries at the disaster recovery site, our monitoring software opens high-priority tickets because it thinks the secondary databases are down (because it can't read them).

Making the secondaries readable by switching them from NORECOVERY to STANDBY solves this issue, but creates a new one.  When we failover to the disaster recovery site and try to add the database to the AG (as outlined above), it fails because the databases on the secondary replicas need to be in NORECOVERY in order to successfully join the AG.

If we switch these databases from STANDBY to NORECOVERY before attempting to add the database to the AG, we receive a message saying the databases on the secondary replicas are not restored far enough in order to be joined to the AG and the join fails.  If at this point, we take a transaction log backup of the database on the primary replica and apply it to the secondaries with NORECOVERY, we can re-initiate the join procedure successfully.

It would seem that changing the secondaries from STANDBY to NORECOVERY is causing the engine to determine the databases are no longer in sync but I can't for the life of me figure out why.  Anyone have any ideas?  The only thing I can think of is that the act of recovering the primary database itself was enough to bring them out of sync, but if this were true, shouldn't it also be the case when we simply leave the secondaries in NORECOVERY to begin with (like our original plan)?

When restoring WITH STANDBY SQL Server has to undo uncommitted transactions (the relevant data pages get written to the undo file that you specify). The NORECOVERY option does not care about the state of the log, or performing any potential rollbacks, that would happen if the database were recovered. Because the two leave the DB in different states you would have to reapply the final transaction log WITH NORECOVERY in order to be in a good state. There's an excellent write-up on this at https://askmesql.blogspot.com/2011/01/log-shipping-norecovery-vs-standby-mode.html

As for your monitoring software, I would recommend putting in a temporary exclusion for certain databases, or even instances while getting the AG setup so that you do not get those spurious alerts.


Oracle 11g ORA-01190: control file or data file 1 is from before the last RESETLOGS I am running Oracle 11GR2, Enterprise Edition Release 11.2.0.3.0.  I got one of my Test DBs corrupted today.  Luckily, I have 2 weeks of backups in the Net Backup server, so I thought, "This is easy, I just restore from the latest backup and I should be good in about 2 hours!"

But I kept getting this Oracle ORA-01190 error.  So I changed my restore time about 30 minutes earlier, than I got the same error.

  SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '+DATA/t1/datafile/system_01.dbf'

After I made the restore time changes, I got this error:

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '+DATA/t1/datafile/system_01.dbf'

This is my rman restore scrpt

run
 {
 allocate channel D1 type sbt_tape parms='ENV=(NB_ORA_CLIENT=t2.abc.com,NB_ORA_COPY_NUMBER=3)';
 allocate channel D2 type sbt_tape parms='ENV=(NB_ORA_CLIENT=t2.abc.com,NB_ORA_COPY_NUMBER=3)';
 allocate channel D3 type sbt_tape parms='ENV=(NB_ORA_CLIENT=t2.abc.com,NB_ORA_COPY_NUMBER=3)';
 allocate channel D4 type sbt_tape parms='ENV=(NB_ORA_CLIENT=t2.abc.com,NB_ORA_COPY_NUMBER=3)';
 set until time "to_date('16-MAR-2016 15:20:00','DD-MON-YYYY HH24:MI:SS')";
 restore controlfile;
 restore database;
 alter database mount;
 recover database;
 alter database open resetlogs;
 }

Shall I start to use SCN method to restore my database?  Is there a clear indicator for me to see the time I can surely restore my database from its backup files?

Thank you so much!

Since you've done an open resetlogs, you've changed the incarnation and are now trying to restore to a previous incarnation.  From the Backup and Recovery User's Guide:

  The procedure for DBPITR within the current incarnation is different
  from DBPITR to an SCN in a noncurrent incarnation. In the latter case,
  you must explicitly execute the RESET DATABASE to reset the database
  to the incarnation that was current at the target SCN. Also, you must
  restore a control file from the database incarnation containing the
  target SCN

After the above paragraph the manual goes on to detail the procedure, step by step.

It doesn't matter if you specify 'to time' or 'to scn'.  If you specify 'to time', rman will internally convert that to an SCN.

.


Do I need an index on this? I have a sql server db and have a question regarding foreign key indices.

Here's a snippet from the db:

In programme_affiliates, I have a computed field, this is always set to '2' in this instance.

The reason for it is that it maintains integrity between programmes and programmes_affiliates.

I also have a link to programme_types to ensure that '2' is always valid as a programme type.

I believe that you should always provide an index for a foreign key, but as it is a calculated fixed field, is this necessary in this case?

Go through this link:
http://blog.sqlauthority.com/2010/08/22/sql-server-computed-columns-index-and-performance/

Since all values are constant and same creating non clustered index will not help here.


What date/time literal formats are LANGUAGE and DATEFORMAT safe? It is easy to demonstrate that many date/time formats other than the following two are vulnerable to misinterpretation due to SET LANGUAGE, SET DATEFORMAT, or a login's default language:

yyyyMMdd                 -- unseparated, date only
yyyy-MM-ddThh:mm:ss.fff  -- date dash separated, date/time separated by T

Even this format, without the T, may look like a valid ISO 8601 format, but it fails in several languages:

DECLARE @d varchar(32) = '2017-03-13 23:22:21.020';

SET LANGUAGE Deutsch;
SELECT CONVERT(datetime, @d);

SET LANGUAGE Français;
SELECT CONVERT(datetime, @d);

Results:

  Die Spracheneinstellung wurde auf Deutsch geändert.
  Msg 242, Level 16, State 3
  Bei der Konvertierung eines varchar-Datentyps in einen datetime-Datentyp liegt der Wert außerhalb des gültigen Bereichs.
  Le paramètre de langue est passé à Français.
  Msg 242, Level 16, State 3
  La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.

Now, these fail as if, in English, I had transposed the month and day, to formulate a date component of yyyy-dd-mm:

DECLARE @d varchar(32) = '2017-13-03 23:22:21.020';

SET LANGUAGE us_english;
SELECT CONVERT(datetime, @d);

Result:

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

(This isn't Microsoft Access, which is "nice" to you and fixes the transposition for you. Also, similar errors can happen in some cases with SET DATEFORMAT ydm; - it isn't just a language thing, that's just the more common scenario where these breakages happen - and aren't always noticed because sometimes they aren't errors, it's just that August 7th became July 8th and nobody noticed.)

So, the question:

Now that I know there are a bunch of unsafe formats, are there any other formats that will be safe given any language and dateformat combination?

In the documentation, it is very explicitly stated that the only safe formats are the ones I demonstrated at the very beginning of the question:

yyyyMMdd                 -- unseparated, date only
yyyy-MM-ddThh:mm:ss.fff  -- date dash separated, date/time separated by T

However, it was recently brought to my attention that there is a third format that is equally immune to any language or dateformat settings:

yyyyMMdd hh:mm:ss.fff    -- unseparated date, no T separator

TL;DR: This is true. For datetime and smalldatetime.

Read on for the longer version, and about as much proof as you're going to get.

There is a loophole that explains this - while the main text body fails to acknowledge yyyyMMdd hh:... as a format safe from transposed language or date format interpretations, there is a little blurb that says the date portion of such a string is not validated depending on dateformat settings:

It is rather unlike me to just take the documentation at its word, usually. You can say I'm a bit skeptical. And the language is ambiguous here, too - it just states that this is about the combination of date and time, not calling out the space explicitly (that could be a carriage return, for all I know). It also says that it isn't multi-language, which means it could fail in certain languages,  but we'll find out shortly that that is incorrect as well.

So I set out to prove that no combination of language/dateformat could make this specific format fail.

First, I created a little block of dynamic SQL for each language:

EXEC sys.sp_executesql @sql, N'@lang sysname', N'us_english';

This produced 34 rows of output like this:

EXEC sys.sp_executesql @sql, N'@lang sysname', N'us_english';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'Deutsch';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'Français';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'日本語';
...
EXEC sys.sp_executesql @sql, N'@lang sysname', N'简体中文';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'Arabic';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'ไทย';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'norsk (bokmål)';

I copied that output to a new query window, and above it, I generated this code, which would hopefully try to convert that same date (the 13th of March) to the 3rd day of the 13th month in at least one case:

DECLARE @sql nvarchar(max) = N'
SET LANGUAGE @lang;
SET DATEFORMAT ydm;
SELECT @@LANGUAGE, CONVERT(datetime, ''20170313 23:22:21.020'');';

Nope, every language worked just find in ydm. I tried every other format, too, and also every date/time data type. 34 successful conversions to March 13th, every time.

So, I do concede to @AndriyM and @ErikE that, indeed, there is a 3rd safe format. I will keep this in mind for future posts, but I've pounded the drums about the other two in so many places, I'm not going to hunt them all down and correct them now.

By extension, you would think this one would be safe, but no:

yyyyMMddThh:mm:ss.fff    -- unseparated date, T separator

I think in every language, this will yield the equivalent of:

  Msg 241, Level 16, State 1, Line 8
  Conversion failed when converting date and/or time from character string.

For completeness, there is a fourth safe format, but it is only safe for conversions to the newer date/time types (date, datetime2, datetimeoffset). In these cases the language settings can't interfere:

yyyy-MM-dd hh:mm:...

However, I highly recommend against its use because it only works for the newer types, and the old ones are still in a massive amount of use, in my experience. Why have the dashes there when everywhere else (or in fact in that same code, if the data type changes) you have to remove them?

SET LANGUAGE Deutsch;
DECLARE @dashes char(10) = '2017-03-07 03:34';
DECLARE @d date = @dashes, @dt datetime = @dashes, @dt2 datetime2 = @dashes;

SELECT DATENAME(MONTH,@d), DATENAME(MONTH,@dt), DATENAME(MONTH,@dt2);

Even given the same source string, the conversions yielded quite different results:

März    Juli    März

The format that works for datetime (yyyyMMdd) will also always work for date and the other new types. So, IMHO, just always use that. And given the third format for types with date/time (yyyyMMdd hh:...), this will actually allow you to be more consistent - even if the date component is always a little bit less readable.

Now it will just take me a few years, give or take, to get into the habit of demonstrating the three safe formats when I'm talking about string representation of dates.


Is it possible to migrate a postgresql database without downtime? I have a Postgresql Database on my Centos, I use barman to backup my database, my question is that is it possible to migrate my database to another server without downtime?
I know some solutions with small downtime with pg_start_backup(), barman also can handle it, but is there anyway to do it without down time?
And one more question is, what is the best way to migrate it?

If you need to migrate to the same version of postgres, then you can setup slave of your db on new server with streaming replication and then just promote it to master. There will be no downtime while doing pg_basebackup, maybe just some load on disks and network caused by your whole cluster moving to new server.


Improve execution time of query in PostgreSQL with multiple joins I'm having the trouble with the following query due to the growing size of the database ( websites is around a million rows, misspelled goes up to a couple millions )

It can reach up to 165 seconds in run time, I was wondering if there was a way to improve this without having to add additional columns to the tables, maybe using some king of index ?

SELECT contacturl, url
FROM websites
WHERE contactable = true
  AND websites.contacted = false
  AND url IN (
    SELECT url
    FROM scans
    WHERE scanid IN (
      SELECT distinct(scanid)
      FROM misspelled
      WHERE word = ?
    )
  )
;

Edit :
As requested, the output of the explain analyze :

Edit (1) :
After Checking the explain statement output, this sing select seems to be taking most of the time :

select distinct(scanid) from misspelled where word = ?

Try going with an EXISTS test. First, let's be explicit about where the columns are coming from, also you don't need col = TRUE and col = FALSE on bools. You can simply say WHERE col or WHERE NOT col, I think that's a bit cleaner.

SELECT w.contacturl, w.url
FROM websites AS w
WHERE w.contactable
  AND NOT w.contacted
  AND w.url IN (
    SELECT s.url
    FROM scans AS s
    WHERE s.scanid IN (
      SELECT distinct(m.scanid)
      FROM misspelled AS m
      WHERE m.word = ?
    )
  )
;

Now, with EXISTS,

SELECT w.contacturl, w.url
FROM websites AS w
WHERE w.contactable
  AND NOT w.contacted
  AND EXISTS (
    SELECT 1
    FROM scans AS s
    WHERE s.scanid IN (
      SELECT distinct(m.scanid)
      FROM misspelled AS m
      WHERE m.word = ?
    )
    AND w.url = s.url
  )
;

But you can do even better. DISTINCT just reduces duplicates for IN. With EXISTS duplicates are simply ignored.

SELECT w.contacturl, w.url
FROM websites AS w
WHERE w.contactable
  AND NOT w.contacted
  AND EXISTS (
    SELECT 1
    FROM scans AS s
    WHERE w.url = s.url
    AND EXISTS (
      SELECT 1
      FROM misspelled AS m
      WHERE m.scanid = s.scanid
      AND m.word = ?
    )
  )
;

That should be a lot faster, normally.


Restore from SQL Server 2008 to 2014 When a full-backup is restored by the DBA, does the DBA need to restore permissions, users, and roles separately? Or does it all come with the full restore?

I am from the dev team. We are migrating from SQL Server 2008 to 2014. We asked the DBA to restore a full backup.

They have restored the full backup to the new server, but I can see discrepancies in users, permissions, and roles between the old and new servers.

Why it is happening?

When you move a database from one server to another via backup /restore, the users within the database stay intact .However, the logins associated with the users might not exist in the destination server.

For this to work as expected, you will need to move the associated logins and then check for any orphan users.

Use this link to get the script for transferring the logins. You can verify the logins with the users in the database and move only those:

How to transfer logins and passwords between instances of SQL Server (Microsoft Support)
Once the logins associated with the particular databases are moved, check for any orphan users. This blog will help you understand more about this:

Understanding and dealing with orphaned users in a SQL Server database by  Atif Shehzad

EXEC sp_change_users_login 'Report'

EXEC sp_change_users_login 'Auto_Fix', 'user'


In DB2 LUW 9.5, is there any way to change the collating sequence without recreating the db? The documentation says "no" :-)

I'm looking for some heady old DBA to whisper dark secrets about cludgy hacks that I'll get fired for sure for using ;-)

No, you can't change the collating sequence without recreating the database.


get postgreSQL client computer name Is there a way in postgres to SELECT the current clients name? I wish to create an audit field using the clients computer name.

Yes.

select *
from pg_stat_activity
where pid = pg_backend_pid();

client_hostname And client_addr are relevant.

log_hostname needs enabling in order for the name to be resolved correctly. See this link:

  By default, connection log messages only show the IP address of the
  connecting host. Turning this parameter on causes logging of the host
  name as well. Note that depending on your host name resolution setup
  this might impose a non-negligible performance penalty. This parameter
  can only be set in the postgresql.conf file or on the server command
  line.


what is the best approach for messaging system with mysql? I want to build a messaging system and looking for best approach for DB schema.

Here's the system that i want to build.

There will be registered users. They can send message to each other.
These users will belong to one or more organizations. So admin can send msg to organizations.
These users may also belong to one or more groups and admin or users can send msg to groups.

Also i want to add status for messages like new, old, deleted.

Can someone recommend me table schema ?

Important note : one user can be in 2 groups so if sender wants to send a msg to those 2 groups, the user should get only one message not two.
It's the same for organizations.

My advice to you would be to download a few (3 - 5 of the most popular) apps which perform similar functionality to what you want to achieve.

Then, I would adopt and adapt [some | a few | various] ideas from these to obtain what you want - or as close as you can reasonably come with reasonable effort. Particularly, check out their schemas - no matter what the database.

Just a word about database selection - if you haven't started this project yet, then permit me to recommend PostgreSQL instead of MySQL. It has windowing (analytic) functions, CTEs (Common Table Expressions - AKA WITH clauses), proper SET operators and CHECK constraints. This is just IMHO, but it is my firm belief that having this functionality integral to the database will save you a lot of time, grief and debugging from having to write all this functionality into your app yourself - or having to choose restricted functionality.


Does the optimized column order for a PostgreSQL table always have variable length types at the end? There's a popular and seemingly authoritative blog post called On Rocks and Sand on how to optimize PostgreSQL tables for size to eliminate internal padding by re-ordering their column length. They explain how variable-length types incur some extra padding if they're not at the end of the table:

This means we can chain variable length columns all day long without introducing padding except at the right boundary. Consequently, we can deduce that variable length columns introduce no bloat so long as they’re at the end of a column listing.

And at the end of the post, to summarize:

Sort the columns by their type length as defined in pg_type.

There's a library that integrates with Ruby's ActiveRecord to automatically re-order columns to reduce padding called pg_column_byte_packer. You can see the README in that repo cites the above blog post and in general does the same thing that the blog post describes.
However, the pg_column_byte_packer does not return results consistent with the blog post it cites. The blog post pulls from from PostgreSQL's internal pg_type.typelen which puts variable-length columns always at the end via an alignment of -1. pg_column_byte_packer gives them an alignment of 3.
pg_column_byte_packer has an explanatory comment:
    # These types generally have an alignment of 4 (as designated by pg_type
    # having a typalign value of 'i', but they're special in that small values
    # have an optimized storage layout. Beyond the optimized storage layout, though,
    # these small values also are not required to respect the alignment the type
    # would otherwise have. Specifically, values with a size of at most 127 bytes
    # aren't aligned. That 127 byte cap, however, includes an overhead byte to store
    # the length, and so in reality the max is 126 bytes. Interestingly TOASTable
    # values are also treated that way, but we don't have a good way of knowing which
    # values those will be.
    #
    # See: `fill_val()` in src/backend/access/common/heaptuple.c (in the conditional
    # `else if (att->attlen == -1)` branch.
    #
    # When no limit modifier has been applied we don't have a good heuristic for
    # determining which columns are likely to be long or short, so we currently
    # just slot them all after the columns we believe will always be long.

The comment appears to be not wrong as text columns do have a pg_type.typalign of 4 but they've also got a pg_type.typlen of -1 which the blog post argues gets the most optimal packing when at the end of the table.
So in the case of a table that has an integer column, a text column, and a smallint column, pg_column_byte_packer will put the text columns right in between the two. They've even got a unit test to assert that this always happens.
My question here is: what order of columns actually packs for minimal space? The comment from pg_column_byte_packer appears to be not wrong as text columns do have a pg_type.typalign of 4, but they've also got a pg_type.typlen of -1.

The comment from pg_column_byte_packer appears to be not wrong as text columns do have a pg_type.typalign of 4, but they've also got a pg_type.typlen of -1.

Had me puzzled, too, when I researched it a couple of years back. typlen = -1 just indicates varlena storage, which nominally has typalign = 'i' (integer alignment, needs to start at 4-bytes offset). But that's not the whole story. Eventually, I found the explanation in a note in the source code:

Note also that we allow the nominal alignment to be violated when storing "packed" varlenas; the TOAST mechanism takes care of hiding that from most code.

So a varlena datum < 127 bytes (after possible compression) only adds 1 byte of overhead (signifying its length) and requires no alignment padding "on disk". (Hardly any "disks" any more these days.)
See:

Calculating and saving space in PostgreSQL (where I coined the term "Column Tetris")

To answer the question in the title:

Does the optimized column order for a PostgreSQL table always have variable length types at the end?

True-ish. Datums exceeding 127 bytes cannot be stored in optimized form and fall back to requiring "integer alignment". If we don't know that a varlena-type column will stay below that threshold (most of the time), we can't say for sure.
Also, there are additional considerations for optimized storage. With many columns in a row, it's cheaper to compute tuple storage offsets with NOT NULL, fixed-size length columns first. Placing frequently accessed columns first also yields a tiny advantage. All of this is further complicated by the TOAST mechanism and index(-only) access.
But all of these effects are typically tiny. And losing 3 bytes to alignment padding pales in comparison when a column occupies 200 bytes. So mostly not worth the bother. The rule of thumb covers most of it:
Sort columns by their required alignment typalign:
d --> i --> s --> c.
But typlen = -1 ("varlena") last (typically), even though formally typalign = 'i'.
The manual:

c = char alignment, i.e., no alignment needed.
s = short alignment (2 bytes on most machines).
i = int alignment (4 bytes on most machines).
d = double alignment (8 bytes on many machines, but by no means all).

Your example

So in the case of a table that has an integer column, a text column, and a smallint column, pg_column_byte_packer will put the text columns right in between the two.

pg_column_byte_packer is living up to its name. int --> text --> smallint is as tight as it gets.
For the typical case of short strings, the only relevant decision is to put int first. smallint can at most force 1 additional byte of alignment padding at odd byte offsets. Since tuple space is always allocated in multiples of 8 bytes, this can never result in a bigger tuple.
Strings exceeding the threshold of 127 bytes on disk (including 1 leading length-byte), flip to 4 leading length bytes and require the nominal integer alignment. That's where putting text before smallint can safe 8 bytes effectively. With random string length that happens in 25 % of cases, so 2 bytes on average for tuples of at least 144 bytes.
There's that. But putting the smallint first typically has tiny advantages, and most text columns stay well below the length threshold.
The thing to remember is not to intersperse multiple smallint and text columns. Multiple offsets can add up in this case.


Add an additional listener to Oracle Database Appliance After deployment of Oracle Database Appliance with OAK 2.3.0 the standard listeners are configured for the public network on bond0. That includes SCAN listeners and one VIP listener per node. This last one seems to listen on both the public network interface and the VIP interface.

However, I would also like something to listen on the management network that I have configured at bond1.

What would be the preferred way of accomplishing this? Can I make the VIP listener also listen to bond1 or must I add a new listener for this?

Can I just add a listener with:  srvctl add listener -p TCP:1521 -o /u01/app/11.2.0.3/grid?

I'm at a loss to see where the ip address or interface goes into the configuration.

Update:
Support note 1063571.1 "How to Configure A Second Listener on a Separate Network in 11.2 Grid Infrastructure" covers the addition of a second production listener with associated VIP interfaces (but no second SCAN listener) to a generic 11gR2 RAC.

In this case, as this is meant to be a back door for operators and DBAs, I think it might be OK to have a basic listener directly on the physical interface on each node - eg. one that does not fail over, and only connects to the instances on that specific node.

I suppose there could also be ODA specific considerations that need to be taken into account, although none are too clear to me at this point.

According to Oracle Support, there should not be any special considerations on Oracle Database Appliance, and thus we can use steps common to Oracle 11gR2 RAC on Linux.

Typically this involves

Adding the additional interfaces to /etc/hosts
Adding a network resource and virtual ip resources to CRS with srvctl
Creating a new listener with netca
Adding the new listeners to the LISTENER_NETWORKS initialization parameter

If you want the new listener to live on the default port of 1521, we need to create it on a different port initially, and then manually remove the listener with srvctl and finally re-add the listener on the correct port with srvctl using the -s option. Note that due to a bug in 11.2.0.1 this only works properly on 11.2.0.2 and higher.

Note that the network and vip resources must be added as root, while the rest is done as the grid infrastructure owner. On oda this is "grid".

I wrote at blog post with a more detailed walk-through of how I went about this at http://www.dolicapax.org/?p=116


PostgreSQL SSD vs HDD - why is there no difference in insert performance? I have a PostgreSQL12 on Ubuntu 20.04 with following tablespaces:

The default uses a HDD and there is also another tablespace on an SSD.
Then I run a test to see the differences in insert/update speed for tables placed in different tablespaces:
create table test_hdd
(v int);

create table test_ssd
(v int)
tablespace ssd1;

insert into test_hdd
select * from generate_series(1, 10000000); --~10sec

insert into test_ssd
select * from generate_series(1, 10000000); --~10sec

update test_hdd set v = 1; --~25sec
update test_ssd set v = 1; --~25sec

The expected outcome is that the operations on table test_ssd should be faster, but that is not the case. All the operations on both tables take almost exactly the same amount of time to complete.
My question is: why? How do I get a write speed boost from using an SSD?
Bonus:
I compared the disk write speed using dd to make sure that the disk for /dev is faster than the HDD disk, and it indeed is. The HDD write speed was around 224 MB/s, and for SSD it was around 1.3 GB/s.

I would not expect disk to be the bottleneck for this task on a reasonable machine.  All writes but one (the final commit) should be either buffered, or done in the background.  The bottleneck would be CPU.
If each row was inserted in a separate transaction and your HDD lacks a non-volatile write cache and your system is configured properly for durability, then having WAL on the SSD rather than HDD should be a lot faster due to the fsync speed.
Even with your single-statement INSERT, if you increased the size to the point that the temp files used for generate_series actually had to reach disk and be evicted from RAM, and then be read back in, that could also highlight the difference between SSD AND HDD.


creating table result in errno 150 I'm trying to create the following schema, but I'm getting errors when I'm trying to create project and staffphone tables.

Not sure what is it complaining about.

mysql> CREATE TABLE staff(
-> StaffID INT AUTO_INCREMENT PRIMARY KEY,
-> FirstName VARCHAR(15) NOT NULL,
-> LastName VARCHAR(15) NOT NULL,
-> Address VARCHAR(30) ,
-> City VARCHAR(15) ,
-> State VARCHAR(3) ,
-> Position VARCHAR(20) NOT NULL,
-> Wage INT NOT NULL
-> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE projects (
-> StaffID INT NOT NULL,
-> ProjectName VARCHAR(20) NOT NULL,
-> Allocation INT NOT NULL,
-> PRIMARY KEY (StaffID, ProjectName) ,
-> FOREIGN KEY (StaffID) REFERENCES Staff(StaffID) ON DELETE CASCADE
-> );
ERROR 1005 (HY000): Can't create table 'staff.projects' (errno: 150)

mysql> CREATE TABLE staffphone (
-> StaffID INT NOT NULL,
-> PhoneNumber VARCHAR(15) NOT NULL,
-> PRIMARY KEY (StaffID, PhoneNumber) ,
-> FOREIGN KEY (StaffID) REFERENCES Staff(StaffID) ON DELETE CASCADE
-> );
ERROR 1005 (HY000): Can't create table 'staff.staffphone' (errno: 150)
mysql>

Any idea would be helpful.

Thank you

As was stated in the comments by @yercube, you have a case sensitivity issue.

SOLUTION #1

Create the first table with the name Staff

CREATE TABLE Staff(
   StaffID INT AUTO_INCREMENT PRIMARY KEY,
   FirstName VARCHAR(15) NOT NULL,
   LastName VARCHAR(15) NOT NULL,
   Address VARCHAR(30) ,
   City VARCHAR(15) ,
   State VARCHAR(3) ,
   Position VARCHAR(20) NOT NULL,
   Wage INT NOT NULL
);

Then, create the other two tables. It should work.

SOLUTION #2 (based on @ypercube's suggestion)

Keep the table staff, but create the other two like this:

CREATE TABLE projects (
    StaffID INT NOT NULL,
    ProjectName VARCHAR(20) NOT NULL,
    Allocation INT NOT NULL,
    PRIMARY KEY (StaffID, ProjectName) ,
    FOREIGN KEY (StaffID) REFERENCES staff(StaffID) ON DELETE CASCADE
 );

CREATE TABLE staffphone (
    StaffID INT NOT NULL,
    PhoneNumber VARCHAR(15) NOT NULL,
    PRIMARY KEY (StaffID, PhoneNumber) ,
    FOREIGN KEY (StaffID) REFERENCES staff(StaffID) ON DELETE CASCADE
);

Give it a Try !!!


Need help with a query for a helpdesk application I have a helpdesk application I'm modifying.

I need to pull data into an ASP.Net DataGrid.

In the problems table, there is a field for employeeID, which is the employee id of the person entering the ticket. There is also a rep field which holds the employee id of the rep handling the ticket.

I'm trying get the employee NAME for both the person entering the ticket and the rep handling the ticket from a users table.

Here are the relevant fields...

Problem Table:

pID
employeeID
sLocation
sRoom
pRep

Users Table:

fName
lName
empID

Here's an example of a query I'm using to pull the problems associated with just one rep:

SELECT p.pID, u.fName + ' ' + u.lName as FullName, p.sLocation, p.sRoom, p.pStatus, p.pTitle
FROM Problems p INNER JOIN tblUsers u ON p.employeeID = u.empID
WHERE p.pRep=Session("EmpID")

If I want to pull all the problems and list the rep who is handling the issue, what would that query be?

Would it look something like this (thought I feel sure there's more to that join statement)

SELECT p.pID, u.fName + ' ' + u.lName as EmpFullName, p.sLocation, p.sRoom, p.pStatus, p.pTitle, u.fName + ' ' + u.lName as RepFullName
FROM Problems p INNER JOIN tblUsers u ON p.employeeID = u.empID
WHERE p.pStatus='OPEN'

Simply add another join to the tblUsers table, to get the name of the other employee.

SELECT p.pID
    , EmpFullName = uEmp.fName + ' ' + uEmp.lName
    , p.sLocation
    , p.sRoom
    , p.pStatus
    , p.pTitle
    , RepFullName = uRep.fName + ' ' + uRep.lName
FROM dbo.Problems p
    INNER JOIN dbo.tblUsers uEmp ON p.employeeID = uEmp.empID
    INNER JOIN dbo.tblUsers uRep ON p.repID = uRep.empID
WHERE p.pStatus='OPEN';

As you can see, there are two instances of the dbo.tblUsers table referenced in the FROM clause, each with a different alias.  This allows you to return separate rows from tblUsers, one for the EmployeeID and one for the RepID

You'll likely need to add to the WHERE clause to pulls rows from the Problem table for a particular EmployeeID or a particular RepID depending on how you want to display results.  This might look something like:

WHERE p.pStatus='OPEN'
    AND p.empID = 42;

Notice, I've prefaced all tables in the FROM clause with the name of the schema where those tables reside.  This is a great habit to have since it will prevent issues down the road if you decide to have more than a single schema.

I also listed each column on its own row, and moved the column aliases in front of the actual column name in the SELECT clause; this makes it easier to find the particular column later when debugging.

You should also get in the habit of appending a semi-colon to the end of all T-SQL statements, since that can mitigate problems with the WITH CTE construct, among others.


Shared LUN for SQL Server Backup files I have a SQL Server 2012 instance running on a 2 Node Failover Cluster Instance. Both nodes are VMs running Windows 2012 Server R2.
The FCI has four shared LUNs: 1 for data files, 1 for log files, 1 for TempDB and 1 for Quorum.

The Backup volume however is not a shared LUN. There’s a separate disk drive added on the VM (Node 1) for a backup drive. The other VM, Node 2 has its own local disk drive for backup location.

I use Ola Hallegren’s script for DB maintenance.

I am thinking about adding another shared LUN in the FCI and set it up as the backup location for both nodes. My thinking is that, this way if the SQL Server role fails over to Node 2, the backup files will still get saved on the same shared LUN for backup files.

Question:
Are there any drawbacks, implication or gotchas with this setup? Any insights is much appreciated! Thank you.

Technically there should be no issue with backups being written to a shared LUN.

As you are using 2012 you do have the option of sending backups to a network share as well which could reduce the complexity of the cluster if you have a fileserver that you can use just to reduce the overhead in a DR situation (as in not having to add the LUN to the cluster etc etc).

Looking at a couple of guides and articles around the place backup drives shared to the nodes is a common thing.

https://mssqltrek.com/2012/06/28/how-to-add-a-new-diskdrive-to-sql-server-failover-cluster/

Fairly verbose example of said process but confirms the validity of backups on shared storage in a cluster.


Must declare the scalar variable what is wrong with the following query  ??

declare  @dbName varchar (50)
declare  @fldName varchar (50)

set  @dbName ='student';
set  @fldName='stID';

declare  @gID smallint
set  @gID =4;

DEClARE @sqlstr nvarchar(max);

SET @sqlstr = 'SELECT  MAX([' + @fldName +'])
  FROM  [dbo].['+ @dbName +']
  WHERE [dbo].['+ @dbName +'].[gID ] = @gID';

  EXEC (@sqlstr)

Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable

Execution of the variable @sqlStr is done under a separate context than that in which you are currently running. Therefore the variables that you have declared in your session will not be available.

Try this instead:

declare @params = N'@gID smallint';
exec sp_executesql @sqlStr, @params, @gID = @gID;

Documentation for sp_executesql can be found here:

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

Full code example:

declare @dbName varchar (50) = 'student';
declare @fldName varchar (50) = 'stID';
declare @gID smallint = 4;
declare @params nvarchar(max) = N'@gID smallint';
declare @sqlstr nvarchar(max) =
        N'select max([' + @fldName +'])
            from [dbo].['+ @dbName +']
           where [dbo].['+ @dbName +'].[gID ] = @gID';
exec sp_executesql @sqlStr, @params, @gID = @gID;


How would you make your case for RDP access to a Data Warehouse? We are moving our Data Warehouse to a hosting company, and they don't want to give RDP access to the server. This kind of bothers me, because it will make my life more difficult. Generally, I tend to shoot myself in the foot when making my case for things like this. My go-to script is that I can make it work with whatever I'm given, because I'm that awesome. If my server enviroment was a space ship, it would be like Rick's from Rick and Morty. That's starting to bother me.

I know that you actually don't need to RDP into SQL server very often. But I still want the ability to do so.

Question: How would you make your case for getting RDP access to your SQL Server?

I'd do the following:

Make a list of the reasons why you connect to the server's console today.
Identify things that you easily could do another way, and start to work around them.
For the rest, identify what can only be done from the console, and what can be done without the console, but is significantly more difficult.

First, share this list internally - make sure your request to the hosting company won't blindside your own employer.
Then, talk to the hosting company, and ask them for their solutions to these problems. I'd expect three possible answer to each of your items:

contact the hosting company, and they will do it themselves;
hosting company provides a tool that lets you do it without RDP; and
you can't do that.

If the hosting company will do it, get details on turnaround time; it may not be acceptable.

Follow up by:

trying out the tools, to make sure they do what you need.
review the "call the hosting co" and "you can't do that" items; identify the impact of each; and review with your company.

The issue could resolve at many points. Your review may convince you that you don't absolutely require RDP. Your conversation with the hosting company may give you satisfactory answer (no "you can't do", and SLAs on "call us" are acceptable. Alternately, your concerns may convince the hosting company you do need RDP.

Ultimately, if you can show RDP access is absolutely necessary, and the hosting company won't provide it on request, then your company may need to be ready to walk away unless the hosting company relents. That requires sign in from your company, which is why it's important to involve them. You don't have to say you're heading for such a confrontation (even if you believe you are). You have to sound reasonable to your own people.


Troubleshooting Mirroring Configuration I am configuring database mirroring on SQL Server 2012.  After configuration I am getting the following error when attempting to start the mirroring session:

tcp server cannot be reached or does not exist.  Check the network address name and the ports for the local and remote are operational.
an Exception occured while executing a Transactional-SQL Statment.

You should check the error log on both Principal and Mirror server.

One common error is that you run your Database Engine Service with a different accounts on your servers, in that case your need to grant connect permissions to the accounts on respective endpoints.

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Mydomain\username4service];
GO


Mariadb processes stuck after aborted dump restore. What to do? I tried to restore a database dump on my local server.
I have created the database locally myself. The dump creates the tables and insert all the data.

I used 'source "C:\my_dump_file.sql"' to start the restore.
Everything goes well until nothing happens at all. No disk activity, no CPU activity, mysql is stuck waiting for something.
I killed the process (I tried CTRL-D and after that, CTRL-C), but now my mariadb installation is in an awful state (see below). It refuses to drop that database. It starts well (all my other databases work) but won't stop.

MariaDB [(none)]> show processlist;
+----+-------------+-----------------+------+---------+------+--------------------------+------------------------+----------+
| Id | User        | Host            | db   | Command | Time | State                    | Info                   | Progress |
+----+-------------+-----------------+------+---------+------+--------------------------+------------------------+----------+
|  1 | system user |                 | NULL | Daemon  | NULL | InnoDB purge coordinator | NULL                   |    0.000 |
|  2 | system user |                 | NULL | Daemon  | NULL | InnoDB purge worker      | NULL                   |    0.000 |
|  3 | system user |                 | NULL | Daemon  | NULL | InnoDB purge worker      | NULL                   |    0.000 |
|  4 | system user |                 | NULL | Daemon  | NULL | InnoDB purge worker      | NULL                   |    0.000 |
|  5 | system user |                 | NULL | Daemon  | NULL | InnoDB shutdown handler  | NULL                   |    0.000 |
| 10 | root        | localhost:50556 | NULL | Killed  |  677 | Unlocking tables         | DROP DATABASE failzzzz |    0.000 |
| 12 | root        | localhost:50576 | NULL | Query   |    0 | init                     | show processlist       |    0.000 |
+----+-------------+-----------------+------+---------+------+--------------------------+------------------------+----------+
7 rows in set (0.00 sec)

Also, here is what the event viewer tells me every time I try to stop the mysql service

InnoDB: Starting shutdown...

For more information, see Help and Support Center at http://www.mysql.com.

InnoDB: Waiting for buf_dump_threadto exit

For more information, see Help and Support Center at http://www.mysql.com.

Please note this is actually the second time I see this issue. I encountered it for another (smaller) database, and it wrecked my mariadb installation just the same. Luckily it was my only database at the time, so I ended up throwing mariadb out of the window and starting fresh, which was the quickest thing to do. After I set innodb_buffer_pool_size to 2G for the restore, I retried and everything eventually work. There should be a better way that to try and cross fingers that the value is high enough, right ?

I'm not so lucky this time, I cannot go through a restore process for every database my mariadb is currently running (too many databases, that would be too long).
I have a recent backup of my entire data folder (prior to my restore attempts), if needed.

So, what can I do ?

how can I fix my mariadb install ? Is it possible at all ?
if it's not fixable, can I use my data folder backup to start over
fresh (WITHOUT the need to restore every database one by one, which is MUCH more time consuming). Information available online seems to indicate that it is possible (see https://dba.stackexchange.com/a/5933/147390)

Thanks

you can kill the mysqld.exe process

taskkill /im mysqld.exe /f

from the elevated command prompt. Then start service again.
But before you do this, it would be nice of you to take a process dump (with procdump for example, e.g procdump -mp mysqld.exe) , and file a bug in mariadb bug tracker https://jira.mariadb.org , attaching the dump.


Why does backup file size become enormous when Agent schedule is changed? I have a database that is currently backed up every two hours.  This is a secondary backup, done in addition to the hosting provider's backup strategy.  I created a procedure (stored within master) to perform the following actions on a scheduled basis in SQL Agent:

If the backup file doesn't exist create it and perform a full (copy only) backup
If the backup file does exist, perform a transaction log backup

The code creates a backup file with a weekly date stamp (e.g. 2017_Week37_myDbName.bak), then drops and re-creates a new file each week.

The code is currently scheduled to run every two hours, which for my level of understanding meant I would have a maximum of two hours data loss in the event of failure.  The initial backup file created in step 1 is 230MB, which obviously increases every two hours as the transaction log backup is added.  Each week, the file reaches a size of around 7GB, before being deleted by the procedure.

I then changed the schedule to run every 15 minutes, but noticed that the backup file went over 230GB - a gigantic increase.  There may be a bug (which I cannot figure), but I'm wondering why this would happen?  I thought that the transaction log backup would only contain the transactions backed up since the last backup, so regardless of the frequency, the backup file would always be a similar size before it was deleted.  I should clarify that at no point was the database used any more frequently than normal.  Basically, I am trying to create differential-style backups after the initial full backup.

CREATE PROC [dbo].[BackupMyDB]

    @myDbName nvarchar(50)

AS

    DECLARE @year nvarchar(4) = CAST(YEAR(GETDATE()) AS nvarchar);
    DECLARE @weekNumber nvarchar(2) = CAST(RIGHT('0' + RTRIM(DATEPART(wk, getdate())), 2) as nvarchar);
    DECLARE @backupFileName nvarchar(max) = @year + '_Week' + @weeknumber + '_' + @mydbname; -- '2013_Week37_myDbName'
    DECLARE @backupPhysicalPath nvarchar(100) = N'C:\SqlScheduledBackups\';
    DECLARE @fullPath nvarchar(150) = @backupphysicalpath + @backupFileName;
    DECLARE @backupName nvarchar(200);

    IF NOT EXISTS (SELECT * FROM sys.backup_devices WHERE (name=@backupFileName))
        BEGIN

            -- create backup device and perform full backup
            EXEC master.dbo.sp_addumpdevice  @devtype=N'disk', @logicalname=@backupFileName, @physicalname=@fullpath;
            SET @backupname = @myDbName + '-Full Database Backup';
            BACKUP DATABASE @mydbname TO  @backupFileName WITH COPY_ONLY, COMPRESSION, RETAINDAYS=45, NOFORMAT, NOINIT,  NAME=@backupname, SKIP, NOREWIND, NOUNLOAD, STATS=10;

            -- delete the previous backup file
            DECLARE @previousBackupName nvarchar(200);
            SELECT
                  TOP 1 @previousBackupName = o.name
            FROM
                sys.backup_devices o
            WHERE
                (o.name <> @backupfilename) AND
                (o.name LIKE @year + '_Week_%_' + @mydbname)
            ORDER BY
                o.name ASC;

            IF @previousBackupName IS NOT NULL
                BEGIN
                    EXEC master.dbo.sp_dropdevice @logicalname = @previousBackupName, @delfile='delfile';
                END
        END
    ELSE
        BEGIN
            SET @backupname = @myDbName + '-Transaction Log Backup';
            BACKUP LOG @mydbname TO  @backupFileName WITH COPY_ONLY, COMPRESSION, NOFORMAT, NOINIT,  NAME=@backupname, SKIP, NOREWIND, NOUNLOAD, STATS=10;
        END
GO

You don't indicate how often your provider issues BACKUP LOG commands (which I assume are not COPY_ONLY), but here is my guess at to what is happening.

Let's assume a 'regular' transaction log backup has occurred at 8am which truncates the log.  Also, let's assume that 1gb of transaction log is being created every 15 minutes.

8:00am - Your BACKUP LOG COPY_ONLY runs and doesn't really backup
anything because the log has just been truncated by the normal log
backup.
8:15am - Your BACKUP LOG COPY_ONLY runs and backs up up everything
since 8:00am  (because that's when the log was last truncated). Let's say 1gb.
8:30am - Your BACKUP LOG COPY_ONLY  runs and backs up everything
since 8:00am (because that's when the log was last truncated) - now 2gb.
8:45am - Your BACKUP LOG COPY_ONLY  runs and backs up everything
since 8:00am (because that's when the log was last truncated) - now 3gb.
9:00am - Your BACKUP LOG COPY_ONLY  runs and backs up everything
since 8:00am (because that's when the log was last truncated) - now 4gb

You are also 'appending' this data to your existing backup because you are using NOINIT.


Correct Code Page or Collation in SQL Server for Portuguese characters I'm inserting data (Bulk insert and merge update) for a Portuguese data source (CSV files) into my database. Although the DB collation is Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data and data stored in NVARCHAR columns, the portuguese charaters are not correctly being saved.

i.e : Original Data

Regularização de Importação de mercadorias das ZEEs com Pagamento  diferido, com Isenção

Data in database after insert

RegularizaÃ§Ã£o de ImportaÃ§Ã£o de mercadorias das ZEEs com Pagamento  diferido, com IsenÃ§Ã£o

Here the import statement:

CREATE PROCEDURE [dbo].[SP_Import_Temp_CPC]
-- Add the parameters for the stored procedure here
@FilePath [nvarchar](1000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

TRUNCATE TABLE Temp_CPC;

-- Insert statements for procedure here
EXEC ('BULK INSERT Temp_CPC
FROM ''' + @FilePath + '''
WITH ( FIELDTERMINATOR = ''<,>'', ROWTERMINATOR =''\n'', FIRSTROW = 2, KEEPIDENTITY, CODEPAGE = ''ACP'',TABLOCK,ORDER(ITEMID ASC) );');
END

I've tried with "ACP,RAW and OEM" but get the same result.

Am I doing something wrong here?

You're not going to get the correct result until you tell BULK INSERT the code page of your .csv file. You tried ACP, RAW and OEM but you did not try the correct one. Which one is the correct one you ask? That would be something only you can answer, since you have the file and you (should) know what code page was originally created as! Perhaps  MS-DOS Portuguese?

the other alternative is to create the .csv file itself as an Unicode file, but that goes back to your source of the .csv file and depends how that file is created (you bcp out, you have it from a friend, was always used by the original program author, etc etc).

  the file is an Unicode one (UTF8 without BOM)

SQL Server does not support UTF-8 files for bulk insert. You must save your file as UCS-2LE or as ASCII with codepage 860. You can use libiconv to do the conversion if you cannot save the file again in the proper encoding.


Duplicate non-clustered index on Primary Key, can I drop one of them? Looking over on of our older databases I noticed that several large tables had duplicate indexes on the Primary Key.

Both indexes are Non-clustered, Unique and only includes the PK-column. The clustered index is on a different column

Below is the SQL to create the indexes

ALTER TABLE [dbo].[TableName] ADD  CONSTRAINT [PK_TableName_1] PRIMARY KEY NONCLUSTERED
([id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]

and

CREATE UNIQUE NONCLUSTERED INDEX [PK_TableName] ON [dbo].[TableName]
( [id] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FGIndex]

Both these statements will create a index that to my eyes looks like duplicates.
The PK-index will be indicated with the "yellow key" in the indexlist in SSMS.

Is it safe to drop the the index? According to statistics the 2nd index is often the one that the Query Plan Engine chooses. The second index also uses less space.

Thanks for any input.

I think it's safe to remove the non-primary key as it's an exact duplicate of the primary key.

If one of them is used more and takes less space, did you check the fragmentation and last statistics date on both? Maybe one of them is more fragmented or has better stats.


Database architecture for big data store? My team has developed service software with a PHP framework, so we are flexible to change database engine: MySQL, PostgreSQL etc. We have already more than 30 customers who wait to start using this service. My uncertainties are following:

Which DB engine is the best for use in such situations?
Is there also MySQL ok. or better to use PostgreSQL?
We use AWS so we plan to have own machine for DB hosting, is this the right decision?

More information:

Database will grow up to 10 GB in year.
Lots of selects but also inserts
Storing many texts and numbers. Files and images are stored on other storage.
Need 24/7 availability

The specified data is quite common on any LAPP or LAMP scenario. I think both databases would handle this kind of loads easily. If you really need high availability, and you are on AWS; I would use RDS, with either PostgreSQL or MySQL, depending on which database you feel more comfortable with. All the necessary steps to configure and maintain the databases are taken care of for you.

If you want to prepare for High Availability, the streaming replication options of both MySQL and PostgreSQL are good alternatives. The intermediate software, that should monitor the master and slave(s) databases and decide when the master is 'dead' and a slave needs to be promoted is the most difficult part (and many times, becomes your SPOF).

Every DB has its pros and its cons. I use mostly PostgreSQL, but also MySQL for some applications that require it. If I have to make a choice, 90% of the times I'll go for PostgreSQL. However, there is not a "good one" or a "bad one". There's normally one that fits best your needs. Sometimes your SQL needs are not that complicated, but you want to use a DB that is known by your team, and maybe your team knows better MySQL. Sharding is easier (better integrated) using MySQL, for instance.


Can I use symlink or data directory for ARCHIVE MySQL tables? All mention of either using a symlink or data/index separate data directories state that MyISAM is the only supported (https://dev.mysql.com/doc/refman/5.5/en/symbolic-links.html).  However, I found this bug report (http://bugs.mysql.com/bug.php?id=49808), that states "only supported for MyISAM and ARCHIVE tables".  This also may be just for partitioning.

I believe just "testing" this is not an option, because the MySQL documentation mysteriously states "For files used by tables for other storage engines, you may get strange problems if you try to use symbolic links. "

Does the ARCHIVE storage engine support symlink or separate data directories?

UPDATE: Related question

All mention of either using a symlink or data/index separate data directories state that MyISAM is the only supported

Almost, but not quite "all."

There are three ways of symlinking in MySQL:

manually symlink the individual table files - only supported for MyISAM and even then the documentation says "as a last resort."
automatically symlink MyISAM MYD and MYI files with DATA DIRECTORY and INDEX DIRECTORY directives.
manually symlink a database (i.e. a single schema) directory to a directory in a different location

This last one is the one you want, and will work transparently with any of the normal storage engines.

Stop reading the page from the manual that you mentioned above before you get to section 8.11.3.1.2, and this will make more sense.  Everything from that point forward is related to table files being symlinked, not directories.  The information that follows is not relevant if your symlinks are done at the database directory level.

Though not directly related to your question, I'll point out that your entire /usr/local/mysql/data directory can also be a symlink to a directory elsewhere.  I almost always do this instead of actually telling MySQL to use a different directory, so that it's obvious to any sysadmin looking at the server itself where the actual data lives.


Freeing disk space after dropped database I'm working on a dev system, and I have been restoring to a database, say "foo", that I'm using for dev purposes. As I'm working through the kinks, I've just been running DROP DATABASE foo. However, I quickly realized I ate up all the space on my disk. Crap.

Does VACUUM FULL, from a different logical database, free up space from the database that I previously dropped (foo)? I tried this from a different logical database, and free space was reclaimed, but I don't THINK it was enough to account for all of the CREATE DATABASE/DROP DATABASE calls I made. It might have just VACUUM'ed the logical database I ran from.

There must be a way to reclaim that space without doing a total database init?

EDIT

So I reinitialized the database from a backup, roughly following these steps. After the restore, I've reclaimed a TON of space on disk! This works for now, but any help regarding how to cleanup a dropped database would still be useful.

EDIT 2

So I've managed to collect some more information about this issue... Here's what I've come up with as an example:

Initial partition size:
                       Size   Used  Avail Use% Mounted on
                        25G   8.1G    16G  35% /apps1

After creating my new database and populating it:
                        25G    18G   6.4G  73% /apps1

After Dropping the database using "DROP database mydb" from a separate logical DB:
                        25G    13G    11G  56% /apps1

So it appears to me that the new DB took up ~9.6 GB on disk. However, after dropping it, the reclaimed disk space only grew by ~4.6G. So, there's roughly 5 GB of space that makes me wonder what's going on!?

And it continues this cycle when I recreate, populate, and drop again.

Does anyone have any idea what's lingering after a "DROP DATABASE" command is issued?

Try sudo lsof| grep deleted and check if any PostgreSQL process appears. This command looks for files which have been deleted but its file descriptors are still open by any process. Another side effect is that df -h and du -sh / differs. This is because du looks at the file system and sum up the size of all files, and df looks at the physical device.

I have just had an issue with a database which didn't free any space after a DROP table and that was the cause.

The only solution I know is to restart the database. Maybe you can try to send a reload (SIGHUP).


Scheduled backup errors I am trying to do the following. I have about 50 DBs and a network NAS that acts as backup machine. I have made an sql script(lets say backup.sql) with 50 lines each of these say "BACKUP DATABASE ABC TO DISK='Y:\ABC.bak'". Also, I have a .bat file with a single command "sqlcmd -i backup.sql -o logs.txt". When I execute it I take a message in the log that say:

  HResult 0x2, Level 16, State 1
  Named Pipes Provider: Could not open a connection to SQL Server [2].
  Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
  Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

I have to mention that I want to make the backup from sql server 2008. Any ideas?
Thanks in advance.

Why are you using a batch file to backup the databases?  Why not put the code in a SQL Agent job and have that kick off the backup?


How can I execute a stored procedure whose name is in a variable? Given a variable that contains a stored procedure name:

DECLARE @stored_procedure_name varchar(512);
SET @stored_procedure_name = 'some_stored_procedure_name';

How can I execute the stored procedure (without passing in any arguments)?

You can use the variable directly:

exec @stored_procedure_name;

Please see the BOL Reference on EXECUTE.


Oracle SQL 12c How to correctly use compound foreign keys in compound primary keys? DISCLAIMER: I am a student. I do not work for an airline. I do not pretend to be a professional programmer.

Greetings, I want understand what I need to correct in order for these tables to work together. I'm sure it is obvious, but it isn't to me. To me, it looks like I'm matching my columns correctly. I'm sure it has to do with using a compound foreign key as part of my compound primary key - but I don't know how to correct this. My full code, with many 'TODOs' still remaining, is on Github Gist here.

I do not have access to an Oracle DBMS at this time, and won't for several days, so I'm using SQLFiddle; which doesn't give the full output SQLPlus or Developer could. It is also not optimal because I'm studying 12c, but SQLFiddle only offers 11g support. Thank you ahead of time.

This is SQL fiddle's error output:

ORA-02256: number of referencing columns must match referenced columns

And here are the two tables which don't agree with each other.

CREATE TABLE FLIGHT (
  FLIGHT_ID NUMBER(3),
  AIRCRAFT_ID VARCHAR(8),
  FLIGHT_DATE DATE,
  CONSTRAINT PK_flight_comp_pk PRIMARY KEY (FLIGHT_ID,FLIGHT_DATE),
  CONSTRAINT FK_flight_route_ID FOREIGN KEY (FLIGHT_ID)
    REFERENCES FLIGHT_ROUTE(ROUTE_ID),
  CONSTRAINT FK_flight_serial_ID FOREIGN KEY (AIRCRAFT_ID)
    REFERENCES AIRCRAFT(AIRCRAFT_ID)
);

CREATE TABLE RESERVATIONS (
  CUSTOMER_ID NUMBER(4),
  FLIGHT_ID NUMBER(3),
  QTY_RESERVED_SEATS NUMBER(3),
  CONSTRAINT PK_reservations PRIMARY KEY (CUSTOMER_ID,FLIGHT_ID),
  CONSTRAINT FK_reservations_customer_ID FOREIGN KEY (CUSTOMER_ID)
    REFERENCES CUSTOMER(CUSTOMER_ID),
  CONSTRAINT FK_reservations_flight_ID FOREIGN KEY (FLIGHT_ID)
    REFERENCES FLIGHT(FLIGHT_ID,FLIGHT_DATE)
);

ORA-02256: number of referencing columns must match referenced columns

  Cause: The number of columns in the foreign-key referencing list is not equal to the number of columns in the referenced list.
  Action: Make sure that the referencing columns match the referenced columns.

The problem is the foreign key should reference both columns used in the composite primary key.

Add the FLIGHT_DATE column to the RESERVATIONS table and change your code:

CONSTRAINT FK_reservations_flight_ID FOREIGN KEY (FLIGHT_ID)
    REFERENCES FLIGHT(FLIGHT_ID,FLIGHT_DATE)
);

To:

CONSTRAINT FK_reservations_flight_ID FOREIGN KEY (FLIGHT_ID,FLIGHT_DATE)
    REFERENCES FLIGHT(FLIGHT_ID,FLIGHT_DATE)
);


Possible to view historical login and database information from backup of master database About a month ago, we migrated a production database and logins to a new instance.  The client is currently troubleshooting an issue and wants to confirm that the login permissions in the new instance match exactly what they were in the old instance.

We have long since deleted the old logins as well as the database that was migrated from the old instance.  However we have backups of the master database.  I though I could restore the master as a regular user database and query the views to peek at the server and database principals as they existed before the migration, but the views appear to be returning current information rather than historical.

So for one, there's a fundamental misunderstanding on my part regarding what is contained in the master database (or at least how the views work).  If anyone can shed some light on this, that would be great.  But mostly, does anyone know if what I'm trying to do is possible?

Edit:
As a test, I restored the same backup of the master database onto a completely different instance, naming the restored database:  master_temp.  Then I tried the following:

SELECT * FROM master_temp.sys.server_principals

The results that were returned only contained information from the instance the database was restored onto.  Not a single user login from the instance the backup was taken from was returned in the result set.

While this is possible to do with the Dedicated Admin Connection and a modified version of sys.server_principals, it is unsupported.

I would rather spend this time to restore master properly on a new instance (on Developer Edition under a VM makes the most sense), and then access it from there.


In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions? I understand the distinction between:

Scalar functions
Set-Returning-Functions (SRF)s
Internal functions
Window functions
Aggregate functions of all sorts
User-Implemented functions (which in PostgreSQL can be implement in any language)
Etc.

In SQL Server "Stored Procedures" are permitted through EXEC. What does that provide over any other function executed with SELECT that returns NULL?

When PostgreSQL gets Stored Procedures what will they bring me, and what is the formal distinction if any between a function and a stored procedure in the spec?

I read this question but it seems to predate the announcement of the implementation

Since Postgres functions (CREATE FUNCTION) only run in a (single) transaction context, several important commands cannot be executed inside a function body. Like CREATE DATABASE or CREATE INDEX CONCURRENTLY or VACUUM. The manual:

  VACUUM cannot be executed inside a transaction block.

Functions are often called "stored procedures", which has always been a misleading term - probably carried over from other RDBMS. With the arrival of SQL procedures (CREATE PROCEDURE) in Postgres 11 that misnomer should be avoided completely.

SQL procedures can begin and end transactions. But the commands mentioned above are not allowed inside any transaction block at all, so those cannot be included in SQL procedures, either (yet).

Multiple result sets are planned for the future, but not implemented, yet.

Related:

When to use stored procedure / user-defined function?
Function Performance


What is the best choice for MySQL high availability (zero downtime)? As I know there are many solutions for MySQL HA, but most of them are congenitally not zero downtime solution, like MHA, AWS RDS...

That said, if zero downtime is "must have", the only choice is MySQL cluster/MariaDB Galera ?

There is not such a thing as 100% availability. Even big providers like Google or Amazon only guarantee "three nines". The most critical systems tend to give you high availability "five nines" ("guaranteed" is different from "measured").

Some software solutions, including MySQL-related ones, can provide you something around 99,999%, but with very specific hardware, network, geographical and infrastructure provider redundancy, and probably suffering a lot of efficiency loss.

Almost all solutions will have at least some (mili)seconds of delay when doing geographical failover, making it not 100% available. Unless, of course, you redefine availability as "online schema upgrades", "floodings-do-not-count" or "hot software patches" which is possible with almost any modern MySQL/cluster-like setup (Galera, NDB, which by the way they are very different solutions).


Migrate SQL server data to new schema I have a database that was originally created by a couple of people for a small business unit (about 8 staff and 10 customers) in MS Access.   The original 'developer' was a non-geek who had been sent on a two day ms access course by a previous employer. It was poorly designed but worked well enough, as the unit grew there was the need to have more than one person administrating the data.  At this time I had joined the unit and suggested using SQL server as the back end and keeping the access front end allowing multiple users, three or four at that time.

It now has approx 40 users servicing 50+ business customers, and is essentially a complete mess.  But we need this tool to keep track of our jobs queue, who is doing what, when it is due, who for and every thing.  Without it we could not function.

So I now want to throw it away and rebuild from scratch and do it properly, and ditch the access front end in the process.  While doing this I need to keep the current system running, develop the new one and some how keep the data in sync and run them in parallel for a while until the higher powers are satisfied it's not going to cause any problems.

Does any one have an suggestions or pointer on how to keep the data in sync given that I will be taking several giant tables and normalizing them into sensible tables in the new schema?

It is not a huge database, the main table listing all jobs grows by about 1500 records per month for example, and currently has about 100,000 rows.

My thoughts were to add a row version column to the tables, and then hack something together to monitor it and translate to the new schema.

Is there a better way?

Migrating the Data is a Job in itself
Keeping Data in sync is a job is yet another job.
Creating a new front end is yet another job.

there's lots of ways to tackle the problem.

there's a lot that can go wrong with data migrations, so if you've never done one before unless the business is yelling for it I'd move very slowly and carefully.

1st I'd replicate the Schema and just do a simple import into sql.

From there build out what you want your new schema to look like and build your scripts for moving data around into your new schema, make sure you're logging every single insert update delete etc, it's easiest if you log the command that you ran to move it, and the Command to undo the operation in a central table.

then build your new front end against that and test it thoroughly.

then test your scripts for exporting from access and importing into your new schema, then you can automate the Export import process until it runs perfectly every time I'd probably do at least 100 or so executions to test the process before the move.

I wouldn't worry about keeping it in sync until you've got buy in from the business with your new process and application then I'd just set a time for the Switch.

If you have to have 2 simultaneous systems In place you've got a whole different set of problems.

Which one becomes the source of truth? do you need to be able to export everything back out to access at any given time.

Just about any change in scope will add additional time to the Project.

Hit me up if you have any other questions. I've done a couple around 50 or so of these small scale data migrations from flat files to sql, from access to sql, from sql to access, oracle to MongoDB, from SQL to Mongodb, mysql to sql, mongodb to SQL and they take a lot longer than you'd think if you want to do it right.


Getting database usage of all the database at once in Sybase How can I get the database usage of all the databases at once?

sp_spaceused and sp_helpdb <dbname> only gives the usage one at a time.

I use the Sybase Central client.

Below is the script that I use --- master..sysdatabases&  master..sysusages

--- Source : http://benohead.com/sybase-size-of-data-and-log-segments-for-all-databases/

select db_name(d.dbid) as db_name,
ceiling(sum(case when u.segmap != 4 then u.size/1048576.*@@maxpagesize end )) as data_size,
ceiling(sum(case when u.segmap != 4 then size - curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end)/1048576.*@@maxpagesize) as data_used,
ceiling(100 * (1 - 1.0 * sum(case when u.segmap != 4 then curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end) / sum(case when u.segmap != 4 then u.size end))) as data_used_pct,
ceiling(sum(case when u.segmap = 4 then u.size/1048576.*@@maxpagesize end)) as log_size,
ceiling(sum(case when u.segmap = 4 then u.size/1048576.*@@maxpagesize end) - lct_admin("logsegment_freepages",d.dbid)/1048576.*@@maxpagesize) as log_used,
ceiling(100 * (1 - 1.0 * lct_admin("logsegment_freepages",d.dbid) / sum(case when u.segmap in (4, 7) then u.size end))) as log_used_pct
from master..sysdatabases d, master..sysusages u
where u.dbid = d.dbid  and d.status not in (256,4096)
group by d.dbid
order by db_name(d.dbid)


whats the fastest way to copy data from table to new one? Whats the fastest way to copy table "table_1" to new table called "table_2" ?

I'd like the constraints copied as well as the data.

try alter table exchange partition, as this will not phsycaily move data but it will alter the dictionary, but keep in mind that the source table will be empty as this is exchnage


Efficient PostgreSQL Updates Using a Temporary Table I've written a small program to import product-detail updates from file which takes much longer than expected. (I'll use trimmed-down examples for the sake of brevity.)

The program does the following:

Reads in data from file.
Performs certain modifications and creates an in-memory file.
Creates a temporary table to hold the processed file data.
COPYs the modified data into the temporary table.
Updates the actual table from the temporary table.

This all works fine, except the UPDATE query takes ~20 seconds for a small file of ~2000 rows.

The temporary table looks like this:

CREATE TEMPORARY TABLE tmp_products (
  product_id integer,
  detail text
);

And my update query is really straightforward:

UPDATE products
SET detail = t.detail
FROM tmp_products t
WHERE t.product_id = products.product_id

To speed things up, I tried the following with very little success:

Create a BTREE index on the temporary table.

CREATE INDEX tmp_products_idx
  ON tmp_products
  USING BTREE
  (product_id);

Creating a HASH index:

CREATE INDEX tmp_products_idx
  ON tmp_products
  USING HASH
  (product_id);

Neither index improved the update time significantly. Then I thought perhaps clustering the table would help, but that meant I couldn't use the HASH index. So I modified the queries in the program to use a BTREE index and then CLUSTER/ANALYZE:

CREATE INDEX tmp_products_idx
  ON tmp_products
  USING BTREE
  (product_id);

-- Program inserts data

CLUSTER tmp_products USING tmp_products_idx;
ANALYZE tmp_products;

This didn't help anything either. I took one more stab at it by using both a BTREE and HASH index hoping that the CLUSTER would use the BTREE and the UPDATE would use the HASH:

CREATE INDEX tmp_products_btree_idx
  ON tmp_products
  USING BTREE
  (product_id);

CREATE INDEX tmp_products_hash_idx
  ON tmp_products
  USING BTREE
  (product_id);

-- Program inserts data

CLUSTER tmp_products USING tmp_products_btree_idx;
ANALYZE tmp_products;

And again, nothing helped. I'm still right where I left off - 20 seconds for 2000 rows. Normally 20 seconds for an update is tolerable in my workplace, but the 2000-row file is a small sample I'm using for testing. Larger files will take way too long.

A few details about the products table if they matter:

Rows: ~630k
Columns: 54
Indexes: 19
Triggers: 14
Table Size: ~1.2GB
Index Size: ~2.2GB

I strongly suspect the bottle-neck is in one ore more of the triggers, however I'm not able to remove/modify those triggers. Is there anything I can do to improve the efficiency of my update?

Things that can be done, although whether they help or not... is another story:

Given that your UPDATE is quite simple, my first guess is your triggers are hurting your performance. Processing triggers is normally time-consuming, specially if they're written in any interpreted language (which means, more or less, they're not written in C). If you have possibility of checking with a development machine, test disabling all the triggers, and see what effect it has (time your queries!). Then reenable them one by one, and see which effect each one has on timings. You can find a few triggers hurting (a lot). If there are a few that consume a lot of time, have someone qualified revise them, optimize them, and even if necessary, rewrite them using C. My experience is that any kind of logging or auditing your inserts might make the process slower by (easily) a factor of 10. Take into account that, on top of your 14 triggers, the database might have added some more to make sure all constraints are met (CHECK, REFERENCES, UNIQUE, ...). It's not normally a good idea to try to disable those (and doing it is not straightforward, if possible at all).
Try to find out whether you really need all the indexes in your setup. Check the explanations about Unused Indexes on PostgreSQL wiki. The way your query is working (only updating the column detail), if detail is not part of any index, this wouldn't have much of an influence. PostgreSQL should be able to perform a Heap Only Tuple (HOT) update, and the indexes wouldn't have any big effect.
For HOT updates to succeed, you need some free space in your tables. So, make sure your tables have a fillfactor less than 100. From the docs on CREATE TABLE:

  fillfactor (integer)

  The fillfactor for a table is a percentage between 10 and 100. 100 (complete packing) is the default. When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page. For a table whose entries are never updated, complete packing is the best choice, but in heavily updated tables smaller fillfactors are appropriate. This parameter cannot be set for TOAST tables.

(emphasis mine)
Consider having a covering index on your temporary table. That is:

CREATE INDEX tmp_products_idx
ON tmp_products
USING BTREE
(product_id, detail);

ANALYZE tmp_products;

This makes sense only if the length of detail is moderate. I don't think this will make much of a difference... because this might allow for an Index Only Scan for the source part of your update, but you won't be certain unless you try it.

Some more information about your execution plans will be necessary to provide finer advice.


How do I know that the high number of compilation/sec is because of memory pressure not bad queries? My server is having a high compilation/sec compared to batches/sec (more than 10%). How do I know that this is because of memory pressure that forces removing cached plans from memory, or because of bad ad-hoc queries that looks new to SQL Server every time it sent to be executed?

Note that:

CPU is keeping above 80%.
Target memory and total memory PerfMon counters are always equal.
PLE has a very good value.
buffer cache hit ratio is 99%
Memory grand pending is always 0

You can see what types of queries are in your plan cache with the following query (source: http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/)

SELECT objtype AS [CacheType],
COUNT_BIG(*) AS [Total Plans],
SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs],
AVG(usecounts) AS [Avg Use Count],
SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes
    ELSE 0
    END) AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs – USE Count 1],
SUM(CASE WHEN usecounts = 1 THEN 1
    ELSE 0
    END) AS [Total Plans – USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs – USE Count 1] DESC

This should show you if most of your saved plans are ad-hoc or for procedures. If most of them are only being used once, then try using the "optimize for ad-hoc workload" option mentioned like so:

sp_configure 'show advanced options',1
GO
reconfigure
GO
sp_configure 'optimize for ad hoc workloads',1
GO
reconfigure
GO


Prevent replication of ALTER commands I am using MariaDB 10.0 multi-source replication for a specific use case.
For security reasons, I would like to prevent ALTER commands on master to replicate (such as CREATE, ALTER, DROP...) whatever user run these commands (even root) but of course let SELECT, INSERT, UPDATE, DELETE commands to replicate....
I do not want to use SET SQL_LOG_BIN=0|1 on client side. In fact, I never want to replicate schema modification.
In practice, I wish I could revoke alter permissions to my replication user (who currently has REPLICATION SLAVE permission)
Is there a way to achieve this?
EDIT 2018-02-19
Since my requirements seems nonsense for some readers, I give some additional information about this use case.
I created one (or more) MariaDB Proxy database(s) with tables using BLACKHOLE Storage Engine. So data is not stored on this proxy server, but binlogs are.
I have other MariaDB servers running the same database schema but with INNODB storage engine that replicates data from proxy server(s) using MariaDB Multi-source Replication.
On the proxy server, I can safely recreate, for example, a table schema with a CREATE OR REPLACE TABLE mytable (id int) ENGINE=BLACKHOLE statement as there is no data stored in it.
But this kind of statement MUST NOT run as is on the "slaves" (which are not real slaves as you noticed) as they must remain in their original storage engine or any other option they may have at the table level.
I can do this by issuing a SET SQL_LOG_BIN=0 before executing my command, but I was looking for a way to make sure that I will not break the slaves in case I forget to do it.

APPROACH #1

The easiest thing to do would be to revoke ALTER, DROP, and CREATE from all users except root

UPDATE mysql.user SET alter_priv='N',drop_priv='N',create_priv='N' WHERE user <> 'root';
UPDATE mysql.db   SET alter_priv='N',drop_priv='N',create_priv='N' WHERE user <> 'root';
FLUSH PRIVILEGES;

APPROACH #2

You said that you do not want to issue

SET sql_log_bin = 0;

on the client side. A very adventurous approach would be to use a Stored Procedure to do your ALTER TABLE commands. You could then apply SET sql_log_bin = 0; in the Stored Procedure. In effect, that happens on the server side.

DELIMITER $$

DROP PROCEDURE IF EXISTS `Local_Alter_Table` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Local_Alter_Table`
(
    SQLStatement VARCHAR(2048)
)
BEGIN

    SET sql_log_bin = 0;

    SET @sql = SQLStatement;
    PREPARE s FROM @sql;
    EXECUTE s;
    DEALLOCATE PREPARE s;

    SET sql_log_bin = 1;

END $$

DELIMITER ;

Then, require users to run

CALL Local_Alter_Table('ALTER TABLE ...');

GIVE IT A TRY !!!


Server Wide DDL Trigger Permissions Issue I am trying to implement a server wide DDL Trigger that will audit the DDL changes across the databases on the server using script very similar to the article found here

The trigger will log the DDL events into one table within one database, however I am coming up against permissions issues and some users, even myself with sysadmin privileges, receive the following error message.

Msg 297, Level 16, State 1, Procedure LogDDLEvent, Line 19
The user does not have permission to perform this action.

I've read about using the WITH EXECUTE AS clause and creating a login specifically for the purpose of executing the trigger though this doesn't appear to have resolved the issue.

Could someone advise on the correct method to implement a trigger of this kind and what permissions are required to users or to a specific login to be used in the WITH EXECUTE AS?

EDIT: Some further info

The server is running SQL Server 2008 R2 and the DDL Trigger is set to DDL_EVENTS as follows:

CREATE TRIGGER LogDDLEvent
ON ALL SERVER
FOR DDL_EVENTS
AS

DECLARE     @eventInfo XML
SET         @eventInfo = EVENTDATA()

INSERT INTO Tools.audit.DDLEvent
VALUES
(
      REPLACE(CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' ') -- EventTime
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/EventType)')) -- EventType
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/LoginName)')) -- LoginName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/UserName)')) -- UserName
      , CAST(HOST_NAME() AS VARCHAR(128)) -- MachineName
      , (SELECT   CAST(client_net_address AS VARCHAR(128))
            FROM  sys.dm_exec_connections
            WHERE Session_id = CONVERT(INT, @eventInfo.value('data(/EVENT_INSTANCE/SPID)[1]', 'int'))) -- IPAddress
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/DatabaseName)')) -- DatabaseName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/SchemaName)')) -- SchemaName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/ObjectName)')) -- ObjectName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/ObjectType)')) -- ObjectType
      , CONVERT(VARCHAR(MAX), @eventInfo.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')) -- DDLCommand
      , @eventInfo -- DDLEventXML
)

The above script is how I originally created the trigger and this worked fine for me and some other users, though a user who had db_owner role to a particular database kept receiving the error message above.

After trying to implement the WITH EXECUTE AS I found that the trigger would not work for myself.

Setting the trigger to EXECUTE WITH 'sa' appeared to do the trick. Not sure if this introduces any security concerns. I tried creating a separate login with permissions only to the Tools database and DDLEvent table but non sysadmin users got errors.

CREATE TRIGGER LogDDLEvent
ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR DDL_EVENTS
AS

DECLARE     @eventInfo XML
SET         @eventInfo = EVENTDATA()

INSERT INTO Tools.audit.DDLEvent
VALUES
(
      REPLACE(CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' ') -- EventTime
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/EventType)')) -- EventType
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/LoginName)')) -- LoginName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/UserName)')) -- UserName
      , CAST(HOST_NAME() AS VARCHAR(128)) -- MachineName
      , (SELECT   CAST(client_net_address AS VARCHAR(128))
            FROM  sys.dm_exec_connections
            WHERE Session_id = CONVERT(INT, @eventInfo.value('data(/EVENT_INSTANCE/SPID)[1]', 'int'))) -- IPAddress
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/DatabaseName)')) -- DatabaseName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/SchemaName)')) -- SchemaName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/ObjectName)')) -- ObjectName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/ObjectType)')) -- ObjectType
      , CONVERT(VARCHAR(MAX), @eventInfo.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')) -- DDLCommand
      , @eventInfo -- DDLEventXML
)


Optimize MySQL update statement I have an update query that selects data from a table (HST) and updates it into another table (PCL). The PCL table contains ~30000 rows while the HST table contains about 1.1 million rows. I have multiple (composite) indexes on HST and despite being a large table all queries on it are fairly quick (~2-3 sec). However, when I attempt to select rows from this table and update PCL, it takes 3-4 hours, and in most cases I get "Lock wait timeout exceeded; try restarting transaction" errors and I have to keep trying until it works.

UPDATE PCL
SET `T2A` = (SELECT HST.`Date` from `HST` WHERE HST.`SYM`=PCL.INST AND
HST.`DATE` >=  PCL.Date AND HST.`HP` >= PCL.T2 order by HST.`Date` limit 1)
WHERE `BS` = 'B' AND `T1A` IS NOT NULL;

Is there a way I can rewrite the above query so that it runs faster?

Think about something like

UPDATE PCL, ( SELECT PCL.SrNo, MIN(HST.Date) minDate
              FROM PCL, HST
              WHERE HST.SYM=PCL.INST
                AND HST.DATE >=  PCL.Date
                AND HST.HP >= PCL.T2
                AND PCL.BS = 'B'
                AND PCL.T1A IS NOT NULL
              GROUP BY PCL.SrNo ) subquery
SET PCL.T2A = subquery.minDate
WHERE PCL.SrNo = subquery.SrNo;

Of course, proper indices on both tables is safe.


Arithmetic operations on a table can anyone give me some advice on the following situation:

I have two basic tables:

Then a view created by the two:

select product.IDPRODUCT, sum(quantity) as totalOut, PRODUCTSTORAGE.DATAINPUT, PRODUCTSTORAGE.TOTALIN
    from PRODUCT
    inner join COMMANDPRODUCT on commandproduct.idproduct = product.idproduct
    inner join PRODUCTSTORAGE on PRODUCT.IDPRODUCT = PRODUCTSTORAGE.IDPRODUCT
    group by product.idproduct, PRODUCTSTORAGE.DATAINPUT, PRODUCTSTORAGE.TOTALIN order by datainput asc;

How can you obsserva i have 4 records with idProduct = 28, and totalOut for all together is 18, and not individually.
To me that the result should look like the following:

I would like the result to look like this:

How could I do that?

Well, perhaps not the best way, however, I managed to do it via this: (I ignored that extra third table, wasn't sure ... not sure how it affects things) ;)
(the w_tab1 and w_tab2 are just your sample data ... )
Tested on Oracle 12.

  with w_tab1 as (
           select 28 idproduct, 18 totalout from dual union all
           select 26 idproduct,  8 totalout from dual union all
           select 27 idproduct,  2 totalout from dual union all
           select 16 idproduct, 17 totalout from dual
        ),
     w_tab2 as (
           select 16 idstorage, 16 idproduct, 20 totalin, 0    purchaseprice, null                                 datainput, 2 idprovider, null status from dual union all
           select 17 idstorage, 17 idproduct, 20 totalin, 0    purchaseprice, null                                 datainput, 2 idprovider, null status from dual union all
           select 22 idstorage, 22 idproduct, 20 totalin, 4500 purchaseprice, null                                 datainput, 2 idprovider, null status from dual union all
           select 23 idstorage, 23 idproduct,  6 totalin, 4300 purchaseprice, to_date('18-may-2015','dd-mon-yyyy') datainput, 2 idprovider, null status from dual union all
           select 24 idstorage, 24 idproduct,  4 totalin, 3700 purchaseprice, to_date('18-may-2015','dd-mon-yyyy') datainput, 2 idprovider, null status from dual union all
           select 25 idstorage, 25 idproduct, 20 totalin, 40   purchaseprice, to_date('18-may-2015','dd-mon-yyyy') datainput, 2 idprovider, null status from dual union all
           select 26 idstorage, 26 idproduct, 20 totalin, 4300 purchaseprice, to_date('18-may-2015','dd-mon-yyyy') datainput, 1 idprovider, null status from dual union all
           select 27 idstorage, 27 idproduct, 10 totalin, 4300 purchaseprice, to_date('18-may-2015','dd-mon-yyyy') datainput, 1 idprovider, null status from dual union all
           select 28 idstorage, 28 idproduct, 10 totalin, 4300 purchaseprice, to_date('12-may-2015','dd-mon-yyyy') datainput, 1 idprovider, null status from dual union all
           select 29 idstorage, 28 idproduct, 10 totalin, 4300 purchaseprice, to_date('18-may-2015','dd-mon-yyyy') datainput, 1 idprovider, null status from dual union all
           select 30 idstorage, 29 idproduct, 10 totalin, 4300 purchaseprice, to_date('18-may-2015','dd-mon-yyyy') datainput, 1 idprovider, null status from dual union all
           select 31 idstorage, 28 idproduct, 40 totalin, 3000 purchaseprice, to_date('18-may-2015','dd-mon-yyyy') datainput, 1 idprovider, null status from dual union all
           select 32 idstorage, 28 idproduct, 30 totalin, 3000 purchaseprice, to_date('22-may-2015','dd-mon-yyyy') datainput, 1 idprovider, null status from dual union all
           select 33 idstorage, 30 idproduct, 10 totalin, 4300 purchaseprice, to_date('22-may-2015','dd-mon-yyyy') datainput, 1 idprovider, null status from dual
        ),
     w_sub as (
           select a.idproduct,
                  a.totalout, sum(b.totalin) over (partition by a.idproduct
                                   order by b.datainput, b.idstorage) sumin,
                  b.datainput, b.totalin, b.idstorage
             from w_tab1  a,
                  w_tab2  b
            where a.idproduct = b.idproduct
        )
  select idproduct,
           least(totalin, nvl(lag(greatest(0,totalout-sumin)) over (partition by idproduct
                                order by datainput, idstorage),greatest(0,least(totalout,sumin))))       totalout,
           datainput, totalin
    from w_sub
   order by idproduct, datainput, idstorage
  /

Result (on Oracle 12):

  IDPRODUCT   TOTALOUT DATAINPUT               TOTALIN
  ---------- ---------- -------------------- ----------
          16         17                              20
          26          8 18-may-2015 00:00:00         20
          27          2 18-may-2015 00:00:00         10
          28         10 12-may-2015 00:00:00         10
          28          8 18-may-2015 00:00:00         10
          28          0 18-may-2015 00:00:00         40
          28          0 22-may-2015 00:00:00         30

  7 rows selected.


Instead of static list, using a table select result for IN key I use a static list for some queries WHERE clause like

WHERE STypeID IN ('12', '13', '14', '265')

This list will be changed in the future; new values will be added. When list changed, I don't want to check all queries which has this list to change, want to control this list from 1 place; so I created a table with two columns; Key and Value, inserted my static values into table.

      Key             Value

SupportedTypeID        12
SupportedTypeID        13
SupportedTypeID        14
SupportedTypeID        265

I modified my where clause like

WHERE STypeID IN (SELECT Value FROM LOVs WHERE Key LIKE 'SupportedTypeID')

but it causes performance issue; queries results come too late.

In most cases replacement IN clause with EXISTS clause gives performance boost.
Try this:

WHERE exists (SELECT 1 FROM LOVs WHERE value = STypeID )


SQL Server backup medianame If I make full/differential/log backups to disk under full recovery model it is important to specify MEDIANAME?

Is it true that MEDIANAME is only for tape backups? If not, when and why should I specify MEDIANAME in the backup statement?

Additional question:
msdb.dbo.backupmediaset.media_set_id actually means backup file id if backup media is disk, it that true?

If I make full/differential/log backups to disk under full recovery
  model it is important to specify MEDIANAME?

NO, it is not important

  Is it true that MEDIANAME is only for tape backups? If not, when and
  why should I specify MEDIANAME in the backup statement?

No it is not just for tape backups,  you can take local disk backup and specify media name. More over its a name given to particular backup set.For example

BACKUP DATABASE AdventureWorks2012
TO DISK='X:\SQLServerBackups\AdventureWorks1.bak',
DISK='Y:\SQLServerBackups\AdventureWorks2.bak',
DISK='Z:\SQLServerBackups\AdventureWorks3.bak'
WITH FORMAT,
   MEDIANAME = 'AdventureWorksStripedSet0',
   MEDIADESCRIPTION = 'Striped media set for AdventureWorks2012 database;
GO

I just took backup of Adventureworks database on my local machine and provided media name below screenshot will show you the result.

  Additional question: msdb.dbo.backupmediaset.media_set_id actually
  means backup file id if backup media is disk, it that true?

No they both are different. Below query can easily show it to you. I used media name we just created above in the backup query

select bm.media_set_id,
bs.backup_set_id,
bm.media_family_count,
bm. name,
bm. description
from msdb.dbo.backupmediaset  bm
inner join  msdb.dbo.backupset  bs
ON bm.media_set_id=bs.media_set_id
where bm.name='AdventureWorksStripedSet0'

And the output is

So you can see backup_set_id and media_set_id are different.


Is it worth the time to review indexes suggested by index tuning advisers? I find I am spending a lot of time lately examining lists of proposed indexes that were generated by an index tuning adviser or some other automated process.

Back in the day (circa 2005) I found the suggestions to be rather awful in that they seemed geared to one specific query and more often than not were merely a covering index that had every column in the table whether or not those colums were already indexes.

As time has progressed, I feel the index tuning advisers have improved dramatically, but I still have a built-in mistrust and still want to spend the time to review each one.

I am trying not to be pessimistic, but it is difficult when a vendor (Microsoft) suggests to add multiple overlapping covering indexes to improve performance, especially when we are paying for storage space.  I also don't want to spend my own time needlessly.  I would rather be fixing poorly written queries.

Remember it is just an advisor...just as in life they are not always right on the path that you should take. It's free advise to take or leave.

For the most part it is basing it off the DMV data in SQL Server so it can even fluctuate on what it sees as being an issue. I generally will them as a guide.

Example:

I see a common set of tables showing "suggested" indexes. Well that would tell me then maybe the current indexes, for those tables, need to be reviewed against the current load that is running. Index architecut can change over the life of an application, the indexes created when during release version 1.0 may not be the same once you reach version 5.0.

I would then maybe go use sp_BlitzIndex or Jason Strate's Index Analysis to look at the table and indexes. Maybe go through and capture the execution plans for thost tables, or pull them from cache.

Remember though, it is worth testing any index you add to a table if you can. If not and you are going through modifying indexes, then make sure you make a script of the current indexes so you can restore them.