SQLServer Upgrade Advisor 2014 hanging I try to run SQL Server Upgrade Advisor 2014 against a SQL Server 2008 server.

Everything looks fine when connecting to the 2008 server and I can select which database I would like to analyze.

But once launched, it runs forever, without doing anything (staying at step Analyzing Rules: 0/112).

What's happening and how can I fix that ?

Both SQL Server 2008 and 2014 are Express versions and are installed on my laptop. Unfortunately, this is the only way I found to run the Upgrade Advisor (there are too many restrictions to run it on real prod / dev databases).

The SQL 2008 instance is with SP3 as needed.

Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)   Sep 21 2011 22:45:45   Copyright (c) 1988-2008 Microsoft Corporation  Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

When the Advisor is frozen, we can see 4 sleeping queries (no active ones):

SELECT name,
       cmptlevel
FROM
  (SELECT name,
          dbid,
          cmptlevel,
          DATABASEPROPERTYEX(name, N'UserAccess') AS 'UserAccess',
          DATABASEPROPERTYEX(name, N'Status') AS 'Status',
          DATABASEPROPERTYEX(name, N'IsInStandBy') AS 'IsInStandBy'
   FROM master.dbo.sysdatabases) t
WHERE LOWER(name) NOT IN ('tempdb',
                          'master',
                          'model',
                          'msdb')
  AND HAS_DBACCESS(name) = 1
  AND dbid NOT IN (32767)
  AND UserAccess != 'SINGLE_USER'
  AND Status = 'ONLINE'
  AND IsInStandBy = 0
ORDER BY name;

and

(@dbname nvarchar(256))
SELECT COUNT(*)
FROM master.dbo.sysdatabases
WHERE name=@dbname

and

SELECT 'Edition'=SUBSTRING(@@VERSION,PATINDEX(N'%Corporation%',@@VERSION)+DATALENGTH('Corporation')+2,PATINDEX(N'% on %',@@VERSION)-(PATINDEX(N'%Corporation%',@@VERSION)+DATALENGTH('Corporation')+2))

and

SELECT N'Job.Step',
        j.name + N'.' + js.step_name
FROM msdb.dbo.syssubsystems ss
JOIN msdb.dbo.sysjobsteps js ON ss.subsystem = js.subsystem
JOIN msdb.dbo.sysjobs j ON js.job_id = j.job_id
WHERE ss.description_id=14555
UNION
SELECT N'Proxy',
        p.name
FROM msdb.dbo.sysproxysubsystem ps
JOIN msdb.dbo.sysproxies p ON ps.proxy_id = p.proxy_id
JOIN msdb.dbo.syssubsystems ss ON ss.subsystem_id = ps.subsystem_id
WHERE ss.description_id=14555

Of course, when we launch them manually, they work.

And there's nothing suspicious when I launch a SQL trace on the server...

As far as I know there is issue with SQL Server 2014 upgrade advisor, it sometime hangs when analyzing the workload for migration. What you can do is download SQL Server 2016 upgrade advisor which is now called as Data Migration Assistant V 3.1. As per Microsoft the upgrade advisor 2016 is deprecated and is replaced by Data migration assistant.

I am not quite sure but I believe MS folks knew about this that is why they moved to much better tool data migration assistant.

This does not comes inbuild with SQL Server installation media it is a separate download.


Trying to connect to PostgreSQL with Entity Framework via Npgsql and C# So, I'm new to databases and only have done a little with PostgreSQL before. I have C# (visual studio 2012) and have downloaded the Entity framework (6). I have also downloaded the latest Npgsql data connection driver and used this tutorial:

Using Entity Framework 6 with Npgsql 2.1.0

Now I have of course installed PostgreSQL and referenced the two dll's in my C# project:

npgsql.dll and npgsql.entityframework.dll

Now I need to know how to add, view, delete stuff via C# but I can't find any material on using C#, Npgsql and Entity Framework together.

With no material on using Npqsql and Entity Framework 6, I have instead found this tutorial for version 5:

Create Entity Data Model in Entity Framework 5.0

However, under choose your data connection, when I add a new connection, there is no option to use Npqsql. I really am confused and have been trying to control PostgreSQL via C# for about a week now, I have tried almost every link I can find on Google and am close to giving up. A lot of tutorials show how to drive PostgreSQL directly via Npgsql without using Entity Framework, but I really wanted to use the Entity Framework because I've been told it makes manipulating the database much easier.

I recommend not using Entity Framework with anything else other than SQLSERVER. And then LINQ to SQL may be better. lightspeed was recommended to me as a better product. I personally have not used it.


Running a query within a scheduled job I am looking at running the following query within a scheduled job weekly:

Select TOP 10
      s.database_name,
      m.physical_device_name,
      CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date)
        AS VARCHAR(100)) + ' ' + 'Seconds' TimeTaken,s.backup_start_date,
      CASE s.[type]
       WHEN 'D' THEN 'Full'
       WHEN 'I' THEN 'Differential'
       WHEN 'L' THEN 'Transaction Log'
      END AS BackupType,
      s.server_name,
      s.recovery_model
 FROM msdb.dbo.backupset s
 INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
 ORDER BY backup_start_date DESC, backup_finish_date
 GO

Not sure what I am doing wrong but I continue to get an error, I have tried running it from within the @query tag for the sendmail option.

As per the code in your comment:

USE msdb

EXEC sp_send_dbmail
    @profile_name = 'SQL Mail',
    @recipients = 'vince.chan@ufa.com',
    @subject = 'T-SQL Query Result',
    @body = 'The result from SELECT is appended below.',
    @query = '(include script from above)'

The reason this won't work is the same reason why this wouldn't work:

declare @query varchar(2048);

set @query = 'Select TOP 10
      s.database_name,
      m.physical_device_name,
      CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) AS VARCHAR(100)) + ' ' + 'Seconds' TimeTaken,s.backup_start_date,
      CASE s.[type]
      WHEN 'D' THEN 'Full'
      WHEN 'I' THEN 'Differential'
      WHEN 'L' THEN 'Transaction Log'
      END AS BackupType,
      s.server_name,
      s.recovery_model
 FROM msdb.dbo.backupset s
 INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
 ORDER BY backup_start_date DESC, backup_finish_date
 GO';

 exec (@query);

You're not taking into account the single quotes in the query string.  Something like this should be what you need:

declare @query varchar(2048);

set @query = 'Select TOP 10
      s.database_name,
      m.physical_device_name,
      CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) AS VARCHAR(100)) + '' '' + ''Seconds'' TimeTaken,s.backup_start_date,
      CASE s.[type]
      WHEN ''D'' THEN ''Full''
      WHEN ''I'' THEN ''Differential''
      WHEN ''L'' THEN ''Transaction Log''
      END AS BackupType,
      s.server_name,
      s.recovery_model
 FROM msdb.dbo.backupset s
 INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
 ORDER BY backup_start_date DESC, backup_finish_date';

 exec (@query);

Therefore, your constructed query string should be:

'Select TOP 10
      s.database_name,
      m.physical_device_name,
      CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) AS VARCHAR(100)) + '' '' + ''Seconds'' TimeTaken,s.backup_start_date,
      CASE s.[type]
      WHEN ''D'' THEN ''Full''
      WHEN ''I'' THEN ''Differential''
      WHEN ''L'' THEN ''Transaction Log''
      END AS BackupType,
      s.server_name,
      s.recovery_model
 FROM msdb.dbo.backupset s
 INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
 ORDER BY backup_start_date DESC, backup_finish_date'

You need to use two single quotes in a string that contains a single quote.


What privileges do I need to change the password of sys in an Oracle Database? Our identity management tool wants to change the sys password regularly. It fails with the ORA-01031. What privileges or roles do I need to grant the identity management tool user to be able to change the sys password?

You'll need to be connected as a user with the SYSDBA privilege.  Even "alter any user" will not be enough.
SQL> select *
  2  from   session_privs
  3  where  privilege like 'ALTER ANY%';

PRIVILEGE
----------------------------------------
ALTER ANY TABLE
ALTER ANY CLUSTER
ALTER ANY INDEX
ALTER ANY SEQUENCE
ALTER ANY ROLE
ALTER ANY PROCEDURE
ALTER ANY TRIGGER
ALTER ANY MATERIALIZED VIEW
ALTER ANY TYPE
ALTER ANY LIBRARY
ALTER ANY OPERATOR
ALTER ANY INDEXTYPE
ALTER ANY DIMENSION
ALTER ANY OUTLINE
ALTER ANY EVALUATION CONTEXT
ALTER ANY RULE SET
ALTER ANY RULE
ALTER ANY SQL PROFILE
ALTER ANY EDITION
ALTER ANY ASSEMBLY
ALTER ANY MINING MODEL
ALTER ANY CUBE DIMENSION
ALTER ANY CUBE
ALTER ANY SQL TRANSLATION PROFILE
ALTER ANY MEASURE FOLDER
ALTER ANY CUBE BUILD PROCESS
ALTER ANY ATTRIBUTE DIMENSION
ALTER ANY HIERARCHY
ALTER ANY ANALYTIC VIEW

29 rows selected.

SQL> alter user sys identified by newpass;
alter user sys identified by newpass
*
ERROR at line 1:
ORA-01031: insufficient privileges


Monitoring free space in SQL Server data files I have manually resized mdf/ndf files to a big size to avoid autogrow operations on SQL Server databases.
Since the files are bigger there is very little free space on disk partitions and the sysadmins keep alerting me that I'm running out of space.

Because I resized them, there is a lot of free space in the data files but one can't notice it looking at file sizes/disk free space.

How can I monitor the real % usage of data files? I would prefer using perfmon counters. I am conerned that when the file really runs out of space SQL Server won't be able to allocate enough space and will crash.

Not sure why you want to use performance counters for this when you can get it from a simple query. And in fact while you can get this information about log files from performance counters (Log File(s) Size (KB) / Log File(s) Used Size (KB)), there is no such counter for how much space is used in a data file.

;WITH f AS
(
  SELECT name, size = size/128.0 FROM sys.database_files
),
s AS
(
  SELECT name, size, free = size-CONVERT(INT,FILEPROPERTY(name,'SpaceUsed'))/128.0
  FROM f
)
SELECT name, size, free, percent_free = free * 100.0 / size
FROM s;


Do I relate entities that are indirectly related when creating a student-course ERD? I'm trying to create an ERD for a tutor center, this is what I understood from their operations:

There is the tutor center director, then under her there are sections/departments. It, math/calculus, languages etc.
Each section has its own section manager.
Each section has full time employees that analyse the section's data (no. of registered students, teachers etc).
Students that want to get tutored contact the appropriate section manager to get registered in a class
The section manager contacts one or more teachers from outside or inside of the tutor center to teach a class that many students requested
Some classes offer tests at the end (Ex. It classes offer ICDL tests while other classes don't).
At the end of the class students submit a teacher evaluation paper to the section manager.
For some classes many teachers could cooperate to teach that class

Now in a way I think all the entities are kind of related. For example the students didn't actually contact the teacher but they will be taught by the teacher so do I establish a many to one relationship between these two entities?

The other question is I ended up with many to many relationships and I don't know what kind of associate entity I should add between them.

This is the ERD I created:

This is a great question!  You have a great start on the ERD because you took notes on how the world of the Tutoring Center works.  You are right in that all of these entities are related.  The difficult part is in developing a sound model of what those relationships are.  Before you get into relationships though, you first want to uncover if any of the entities are really the same thing.  In this case, employee, teacher, director, and section manager are really the same thing - a person working for the tutoring center.  So I would say you really have a single entity there - Employee.  Now that employee may be a contract worker or be a full time employee.  The other words - teacher, director - identify roles those employees play.  In one of the sentences from your notes you identify the employee, but the role is really say analyst.  The employee analyzes the section's data.

The best way to handle these employee roles is to realize that each employee is really holding a position in the tutoring center.  Each position would be of a given position type, and in this case those types are Teacher, Director, Analyst, and Manager.  Now that you've identified the entity types, you begin to look for the relationships.  For example, your question "where students didn't actually contact the teacher but they will be taught by the teacher so do you establish a many to one relationship between these two entities?"  Absolutely there is a relationship because the student is related to the teacher through the class.  But there a subtle distinction here that will cause you to introduce more entity types to properly model what is going on.

A thing type is distinct from a thing.  We know we have a student and we know we have a class.  But really, there is the kind of class - say "Into to Math" - and the offering of that class on a given date and time.  This is a very common modeling pattern you will find everywhere.  In fact you have already seen it with the position type - a kind of job an employee can do, and the position - where an employee is performing that position type at a moment in time.  There are two ways to handle this from a naming standpoint.  The first is to use the words class type and class.  The second is to come up with words whose natural meaning is associated with a type or an instance.  In this case I like the word course to represent the kind of class being offered, and the word offering to represent the scheduling of a teaching team to actually deliver that course to students.  So while it isn't in the notes directly, I would say that since each section is broken down by academic area, each section likely offers one or more courses, and each offering may be taught by many teachers, and many teachers may teach one offering.

This now leads into your second question:  "I ended up with many to many relationships and I don't know what kind of associate entity I should add between them."  If your ERD is a pure conceptual model with no attributes being shown, then you don't need to add an associative entity.  You can use the Many to Many relationship to represent this.  Only when you add the attributes do you need to resolve M to M relationships with associative entities so they may hold the attributes.  But, if the relationship itself will then be associated to new entities, you have to introduce the associative entity.  That is the case with offering, as we realize that a student must register for an offering that is being taught cooperatively by many teachers.  Registration becomes an associative entity to show a student registers for one or more offerings, and an offering can be registered by one or more students.  It isn't the case though with the cooperative teaching, as we can simply show a M to M between offering and teacher as there are no further associations.  Then, when you add attributes (even if there are none there are still the foreign keys), you would add an associative entity perhaps called instruction team member.  We say member as in ER modeling each entity type is always named for a single occurrence - exactly as you have done.  I bring this up only because its very easy to think you are naming an occurrence but have really named the set - in this case to call the associative entity instruction team.

To finish up, you note that a class may offer a test at the end.  I would say the relationship you have is actually 1 to M from class to test, as I would suspect each course has its own test type that it uses that is only for that course and no other.  Now if we needed to show that students in the class actually take the test and make a grade on it, then we again have test type and test, where test is a particular student taking the test on a given date and making a given grade.  Along these lines, you note that a student submits an evaluation.  This is modeled by showing that for a registration, which is a student taking a given offering, an evaluation is performed.

Here is an example ERD I prepared to answer this using the Oracle Data Modeler which is a great free tool you can download that uses what I consider to be the best notation for conceptual models - Barker-Ellis notation.

There is so much more we could discuss about the model and various patterns to accurately depict the tutoring center.  One important addition would be prepositional phrases that precisely describe each relationship.  A really good book to check out when it comes to doing this kind of conceptual modeling is Enterprise Model Patterns by David Hay.  You can also pick up the original text on the Barker-Ellis notation for just the cost of shipping!  Good luck with the project and I hope my comments have helped answer your questions!


Are innodb_large_prefix and innodb_file_format settings backwards compatible? I got the following message when I tried to install a piece of software trough the command line. I use a mySQL db.

* innodb_large_prefix = ON  is required.
* innodb_file_format = Barracuda is required.

I have several questions I couldn't find an answer for by just searching Google:

Can I just change these to the required settings without breaking other software of the server?
Suppose it does break, can I just switch back?
Do I need to check for my mySQL db version prior to  changing this?

First of all, never trust what a random internet person tells you about your environment; the only way to be sure is by doing your own testing. It is very difficult to give an absolute correct answer without knowing all the details of your environement.

Answering broadly your question "Is it safe to enable barracuda and large_prefix_index?" the answer is yes, it is safe and backwards compatible.

barracuda is a new file format that allows 2 extra row formats: Dynamic and Compressed. In general, the internal row format is transparent for the application, although some new features may require the new formats (large_prefix_index and optimized blob storage). Both of those features are enabled by default on the newest MySQL and MariaDB versions, which normally only happens for mature and backwards compatible options. In my personal opinion, there not a reason to use a legacy format on the latest server versions unless you are maintaining older installations.

Note that older data doesn't automatically get converted to the new format, that is why if something is wrong, you can just return to the old options and nothing will have changed. To get advantage of the new format you will need to create new tables or rebuild those after the options change (they are just "defaults", they don't automatically change anything on the tables).

In particular, large_prefix_index allows for larger indexes, which is usually necessary to allow for full UTF-8 compatibility. In particular, I have seen many applications fail because they require the utf8mb4 character set, which require up to 4 bytes per character, and not having that option enabled makes table creation fail. That is likely your case.

"Do I need to check for my mySQL db version prior to changing this?" Indeed you do, older versions of MySQL/MariaDB don't support that option. Newer versions don't have the option available (so they may fail to boot if provided such options) because the option is hardcoded to the new value. In general, MySQL supports older formats, but warns you to rebuild them when running mysql_upgrade if they are considered incompatible.

Is there any scenario where that can fail?- I can see it failing in very very specific scenarios where redundant or compact row formats are expected, but those should be rare- in my opinion all applications that are maintained should be compatible with barracuda/compressed row formats (Antelope and file formats in general are deprecated). If you have legacy applications that require a very special mysql confuguration consider keeping an older mysql installation or not rebuilding the tables to the new format, but I would consider that a huge technical debt and not best practices.


T-SQL for starting Log Reader Agent I am facing a problem regarding SQL Log Reader Agent. Whenever, I start SQL Server Agent, Log Reader Agent does not start. I have to start manually.

Do I have start Log Reader Agent manually every time?
How can I start Log Reader Agent via T-SQL?

Do I have start Log Reader Agent manually every time?

That depends - what schedule is attached to the log reader agent job? The log reader agent runs as a SQL Server Agent job and typically is assigned a schedule to start when SQL Server Agent starts. This schedule might be disabled or deleted hence why you're having to start it manually.

  How can I start Log Reader Agent via T-SQL?

You need to start the SQL Agent job. First, get the name of the job, then add it to this script and execute it:

EXEC msdb..sp_start_job @job_name = '<lo reader agent job name goes here>';

This shouldn't be necessary though, you just need to find and fix the problem with the schedule for this agent.

NOTE: To get the name of the job, the simplest way is to open Job Activity Monitor and look in the Category column for Repl-Log Reader.


Does SQL profiler affect server performance? I am facing a problem where the sql server 2008 fails for some high load. I need to find that load case and need to optimize the code so that it can handle the load.
I have found on the internet that SQL profiler can be used to track the database interactions which can later be analyzed to find the exact point where the problem occurs.
I have also found as to how we can start the sql profiler.
But now my question is that by starting the sql profiler am i going to affect the server performance?

SQL Server Profiler is a GUI that utilizes SQL Server Trace through the client-side.  Due to this nature, yes you can potentially see a mild to severe performance impact depending on the environment.

SQL Server Profiler is suited mainly for a quick glimpse at what is happening on the server (provided the database server can handle the extra latency).  It is not intended to be run for long periods of time.  For longer traces, use a server-side trace or Extended Events.


MySQL InnoDB logfiles resized - review mysql-err.log for potential problems I restarted mysqld this morning, to increase the size of the innodb logs and increase the amount of memory allocated to the innodb buffer pool.  (MySQL 5.1.53-Community on Ubuntu)

To increase the size of the innodb logs, I modified the my.cnf file, stopped mysqld, renamed the existing innodb log files, and then started mysqld again.

     root@mysql01:# /etc/init.d/mysql.server stop
     Shutting down MySQL
     ............ *
     root@mysql01:# ls -al ib_logfile*
     -rw-rw----  1 mysql mysql  251658240 2013-02-08 08:21 ib_logfile0
     -rw-rw----  1 mysql mysql  251658240 2013-02-08 08:21 ib_logfile1
     root@mysql01:# mv ib_logfile0 ib_logfile0.OLD
     root@mysql01:# mv ib_logfile1 ib_logfile1.OLD
     root@mysql01:# /etc/init.d/mysql.server start
     Starting MySQL
     ................................ *
     root@mysql01:#

The mysql01-err.log shows

130208  8:21:27 [Note] /opt/mysql/bin/mysqld: Normal shutdown

130208  8:21:27 [Note] Event Scheduler: Purging the queue. 0 events
130208  8:21:30  InnoDB: Starting shutdown...
130208  8:21:39  InnoDB: Shutdown completed; log sequence number 661 1658279774
130208  8:21:39 [Note] /opt/mysql/bin/mysqld: Shutdown complete

130208 08:21:39 mysqld_safe mysqld from pid file /opt/mysql_data/mysql01.pid ended
130208 08:22:17 mysqld_safe Starting mysqld daemon with databases from /opt/mysql_data
130208  8:22:17 [Note] Plugin 'FEDERATED' is disabled.
130208  8:22:18  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 1536 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500
130208  8:22:29  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 1536 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
130208  8:22:37  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Last MySQL binlog file position 0 886154568, file name ./mysql-bin.001636
130208  8:22:48  InnoDB: Started; log sequence number 661 1658279948
130208  8:22:48 [Note] Event Scheduler: Loaded 0 events
130208  8:22:48 [Note] /opt/mysql/bin/mysqld: ready for connections.
Version: '5.1.53-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)

I'm concerned with the line, after the new innodb log files are written, that says

130208  8:22:37  InnoDB: Database was not shut down normally!

My questions are:

Is that line expected in the log file, is this the behavior I should expect? Does this indicate a problem, or is this a normal result from creating new log files?

Is there anything in the tail of the mysql01-err.log that indicates that there was a real problem?

Was the InnoDB "crash recovery" successful? Or is there a chance that something is screwed up?

Are there any additional steps that I can take to verify that everything is fine with InnoDB, and that the steps I performed did not cause any corruption?

(I'd rather know about any problems now. It would be much better to do any additional steps to get a full recovery, sooner, rather than later.)

It is sort of yes and no. Why would I say both?

There are still some transactional data embedded in the old ib_logfiles in conjunction with the ibdata1 file. (See pictorial representation)

What you should have done is this:

mysql -u... -p... -ANe"SET GLOBAL innodb_fast_shutdown = 0"
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

If you did not know of disabling innodb_fast_shutdown, ther you steps should have been

/etc/init.d/mysql.server stop
/etc/init.d/mysql.server start
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

Doing either of these coordinate the purging of all uncommitted writes to tables and indexes. Just looking at the error log, I think you dodged a bullet in this instance. In future, please follow this protocol.

If you are not sure either way, you could put back the old log files, set the old size of innodb_log_file_size, restart mysql. Then, start the process over again as I prescribed above.


Using CoreData on a client-side application This may be a bit more of an SO question, but...

If I'm writing a client-side application for OS X that's going to sync with an online MySQL database, is there any difference on if I have CD use SQLite or XML?

It depends, as XML is not an RDBMS.

SQLite allows the querying of relations. XML (and JSON) allow for the navigation of hierarchies. Completely different ontological mappings of reality.

If you need an RDBMS backend, SQLite is for you. If you need record storage without any sophisticated query requirements, XML may work, depending on your language.


Access Drizzle databases through MySQL This is an odd question but is it possible to manipulate a Drizzle database through MySQL stored procedures? The reason I need to do this is that I'm migrating my MySQL database (which contains a lot of stored procedures) to Drizzle (which doesn't support stored procedures). This was essentially one of my 'grand' short term ideas for this.

Any ideas for doing this, or some other ones?

Noting that you didn't ask whether it was a good idea, but only whether it is possible :) I would venture to say it seems like it should be possible, since Drizzle speaks the MySQL protocol over TCP -- as evidenced by the fact that you can use the "mysql" CLI client and other MySQL client libraries to connect to a drizzle server.

The "federated" storage engine in MySQL allows your "local" server to connect to a "remote" MySQL (Drizzle?) server using the standard MySQL C-client library (aha!), and remotely access one or more tables on the remote server as if they were tables on the local server... the "local" server essentially spoofs a client connection to the remote server... one connection per table, from what I've observed.

CREATE TABLE (
-- column definitions on the local server
-- should be identical to the remote server
-- although the engine is more forgiving than
-- the documentation will lead you to believe
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://user:pass@remote_server:port/schema/table';

-- this is one of two ways to configure the actual connection

I believe this will work because the remote server doesn't know anything unusual is going on -- it thinks it's being accessed by a client, issuing ordinary INSERT/DELETE/SELECT/UPDATE statements, and queries on the local server interact with the federated tables just as they would with any other table.

If the "local" server were MySQL with your stored procedures, and federated tables configured to connect to the "remote" server -- the Drizzle server -- where your data lives, this should accomplish what you want, if I understand the question correctly.  I use "local" and "remote" to describe their roles, of course, not their placement.  For this application, they would most likely be on the same machine, or a very fast LAN.

Your application would connect to the MySQL server to call the stored procedures, which would then manipulate data on the Drizzle server via the federated connections.

How efficient will it be?  Not nearly as efficient as having the stored procedures and data on the same DBMS.

Is it a good idea?  I'm not too sure about that.  But in principle, it's probably worth a shot.

The federated engine has gotten some bad press, but at least in MySQL 5.5, I find the criticism to be unmerited.  I have numerous federated connections from 5.5 → 5.5 and 5.5 → 5.1 in my production systems.

There are a number of caveats that go along with the federated engine, such as no support for transactions, and queries involving federated tables aren't cached on the local server (there's no way to invalidate them, after all).  They don't directly support foreign keys, but the foreign keys are still enforced by the remote server, which will throw an error that propagates back to you locally if you violate them (though it may not be the same MySQL error or SQLSTATE).

You also want be sure you define at least the same indexes on the "local" server as exist on the "remote" server -- and sometimes it may even make sense to declare indexes on columns that aren't really indexed on the far-end -- because if the optimizer decides a full table scan is needed, it actually requests the entire contents of the remote table over the wire from the far-end... usually not what you want.  The indexes don't actually "exist" locally or take up any space on the local server -- but the query sent to the far end includes a WHERE clause to get only the appropriate rows, only if the optimizer realizes this is a possibility.  (This fact does not seem quite as strange when you remember that federated is implemented as a storage engine and the core of MySQL server interacts with it as though it were indeed a storage engine.)

This document is several years old but provides some very good background on the federated storage engine.

Final note: yes, the federated engine is one of the things that got stripped out of Drizzle, but that's fine -- we only need it on the MySQL side for this application.


MySQL privileges for roles aren't applying to users? I've done what I consider to be sufficient research with no answers to justify posting this question here.  I am attempting to grant users permission to execute certain stored procedures.  And because I have to grant permission for several of these roles, but not all of them, I've chosen to do this using roles.  But when I grant one of these roles to a test user and try to call a stored procedure, I see this error message:

1370 (42000): execute command denied to user 'me'@'localhost' for routine 'db.MyProc'

The SQL script to create a role looks like this:

CREATE ROLE 'MyRole';
GRANT USAGE ON db.* TO 'MyRole';
GRANT EXECUTE ON PROCEDURE db.MyProc TO 'MyRole';
-- grant execute on more procedures
FLUSH PRIVILEGES;

And then I grant the role to the test user and set it as default:

GRANT MyRole TO 'me'@'localhost';
SET DEFAULT ROLE MyRole TO 'me'@'localhost';
FLUSH PRIVILEGES;

Naturally, I do all of this as root.  I think that the grants have worked because of the output of SHOW GRANTS.

 SHOW GRANTS FOR MyRole;
+--------------------------------------------------------------+
| Grants for MyRole@%                                          |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `MyRole`@`%`                           |
| GRANT EXECUTE ON PROCEDURE `db`.`MyProc` TO `Contributor`@`%`|
|(grant execute on more procedures)                            |
+--------------------------------------------------------------+

SHOW GRANTS FOR 'me'@'localhost';
+---------------------------------------+
| Grants for contrib@localhost          |
+---------------------------------------+
| GRANT USAGE ON *.* TO `me`@`localhost`|
| GRANT `MyRole`@`%` TO `me`@`localhost`|
+---------------------------------------+

I have found no one else with this problem.  And nothing I read from MySQL documentation suggested that this would not work.  What is going wrong?

I'm using MySQL 8.0.11 on Windows 10.

The problem (it seems) was that MySQL didn't like the capital letters in my procedure names.  I went though the entire script and renamed all of my procedures with lower case letters.  I updated the roles accordingly, and then it worked properly.


QueryStore plan forcing limitations I have a DELETE statement running against a table with a full-text indexed column, a handful of foreign keys with cascade enabled. It looks like this:

DELETE FROM dbo.STUDENTS WHERE STUDENTID=@STUDENTID

Occasionally a plan is compiled which includes very high row estimates for all index operations, such that the DELETE takes a very long time and causes locking.

I have attempted to force a good plan in QueryStore but this doesn't actually work,  showing last forced plan failure description of NO_PLAN.

I have ensured that there are no schema changes that could be invalidating the plan.

Looking at the execution plan, I see that the DELETE involves a join to a system table holding the FT index:

Does this join to the FT index mean that plan-forcing is not supported?

When looking at the plan-forcing limitations one of the bullet points is:

  Distributed query or full-text operations

However, this limitation does not mean that a delete should fail the plan forcing.

Consider these 4 queries, two SELECT statements and two DELETE statements on a table with a fulltext index on the val column:

SELECT *
FROM  dbo.fulltextindexed;

SELECT val
FROM  dbo.fulltextindexed
WHERE CONTAINS(val, N'WOW');

DELETE top(1)
FROM dbo.fulltextindexed;

DELETE top(1)
FROM dbo.fulltextindexed
WHERE id <500 and id > -1;

The only query that is failing is the 2nd one, which is using the CONTAINS statement:

With the failure reason being DQ_NO_FORCING_SUPPORTED.

The plans for the two delete statements, which also show the same clustered index merge operators get forced:

This source gives more information on DQ_NO_FORCING_SUPPORTED:

  Cannot execute query because plan conflicts with use of distributed
  query or full-text operations

To answer the question

  Does this join to the FT index mean that plan-forcing is not
  supported?

My answer would be that this is not the reason as you should see the DQ_NO_FORCING_SUPPORTED instead of the NO_PLAN error.
The reason could be due to another limitation rendering the plan invalid, index changes, added foreign keys, ....

Extra tests

When adding a table and creating a foreign key with on delete cascade that references the table used in the queries above.We would expect the delete to fail since we cannot use the forced plan any longer.

CREATE TABLE dbo.fulltexreference(id int identity(1,1) primary key not null, t1id int, val varchar(max));
ALTER TABLE dbo.fulltexreference add constraint FK_test foreign key(t1id) REFERENCES dbo.fulltextindexed(id) ON DELETE CASCADE;

As expected, we get the NO_PLAN on the two delete statements

Re-forcing the plans gives us salvation:

And removes the NO_PLAN issue

The same is true for a table with a fulltext index that has a foreign key (with on delete cascade) that references the main table where the delete query is running on.

Regarding the high / low estimates

If the delete is not executed that much, adding OPTION(RECOMPILE) could help with the high row estimates as this can give you a better estimation due to the optimizer 'seeing' the variable at runtime.

Posting a different question with the query plan might bring up a different workaround / solution.


postgres promote hot_standby (archive_command = ':') to master (archive_command = '') Assume we have

master server (archive_command = '')
hot_standby server (archive_command = ':')
pg_receivewal server (from master slot)

What would be the correct procedure to promote hot_standby server to master, change archive_command to '' and change pg_receivewal slot?
My concern is a duplicate wal file mess between all 3 servers if done incorrectly.

It's not normally recommended to promote the hot standby but have you read this article on it?
https://help.theatremanager.com/book/export/html/3679


compare/sync databases from different computers I have a server online and I have installed MySQL and database A server on it.   I also have mysql installed on my local computer and have a copy of database A on it.  I want to compare the contents of the two databases, and possibly sync them using federated tables.

But after trying out federated table, it seems that I can’t even connect to the server.
The federated table that's supposed to be connected to (an online, not-empty) table from the server is empty.

Am I doing this right? Is this even possible?

If not, can you please suggest any tools/applications that can do this.

Thanks!

You can use Percona's pt-table-sync tool, even if the two MySQL instances are not setup for replication, with this caveat:

  pt-table-sync does one-way and bidirectional synchronization of table data. It does not synchronize table structures, indexes, or any other schema objects

So something like this command (if they're not set up in replication..there's other options for the tool if they are):

pt-table-sync --dry-run host1 host2

After you're comfortable with the report, run it:

pt-table-sync --dry-run host1 host2

When using this tool, I would first make a backup of both instances. Always backup in case an issue arises!


Get all items from list table which don't have a successful log entry for 'sent' in log table How might one retrieve items that when joined either don't have a specific field (sent), or if they do have that field (sent) then they have an additional field (failure_reason) as well for every sent row?

Consider a database with two tables:

list
  id INT
  name VARCHAR

log
  id INT
  list_id INT REFERENCES (list.id)
  action VARCHAR
  failure_reason VARCHAR

Typical list contents:

ID | Name

1  | John
2  | Paul
3  | George
3  | Atwood

Typical log contents:

ID | List_id | Action  | Failure Reason
---+---------+---------+---------------
1  | 1       | entered | NULL
2  | 1       | sent    | NULL

3  | 2       | entered | NULL
4  | 2       | sent    | Connection Error
5  | 2       | sent    | NULL

6  | 3       | entered | NULL
7  | 3       | sent    | Cosmic Ray

8  | 4       | entered | NULL

It can be seen that List_id 1 has a log entry for both entered and sent, with no failure_reason. Thus, this item has left my responsibility.

Likewise, List_id 2 has a log entry for entered, and two for sent. This is because the first sent failed. We know that the first sent failed as the failure_reason is not NULL. Since there is a successful sent row, this item has also left my responsibility.

However, List_id 3 has a log entry for entered, but the only sent row is a failure. Thus, this item is still my responsibility and should be retrieved in the query.

Additionally, List_id 4 has a log entry for entered, but no sent row. Thus, this item is also still my responsibility and should be retrieved in the query.

I have tried using some subquery magic to get / exclude rows based on the sent values, however as this table is growing huge (expected thousands of new records per day) I need to avoid queries that require returning all results in the entire database.

This is in MySQL 5.1 on CentOS 6.x.

Query itself seems to be quite straightforward (from data sample provided I assume that a record with action:sent , failure_reason:NULL is the final state for a given list_id)  :

SELECT a.*
FROM list a
WHERE NOT EXISTS (SELECT NULL FROM log b WHERE b.list_id = a.list_id
and b.action='Sent' and b.failure_reason IS NULL)

However, if tables are huge , performance will suffer even if you have a covering index in log table which is probably not good - name "log" implies that you are mostly inserting data into it (and insert has to be as fast as possible), so each extra index defeats the main purpose of this table.
It looks to me though that date/datetime field is missed in the model; I guess you typically want failed tasks for a particular (presumably relatively small) date interval .


sqlserver 2014 DEFAULT in VALUES using MERGE I'm trying to insert or update multiple rows in an SQL Server 2014 database. Some of the columns may need to be set to DEFAULT, however sqlserver barfs when there is a DEFAULT. Incorrect syntax near the keyword 'DEFAULT'.

Here is a small representative example of a query. The real query is actually being generated using clojure's hugsql library, using data generated in the clojure program.

MERGE INTO [dbo].[IMOS] [T]
USING (VALUES ('!Default_Materials_1', DEFAULT),
              ('00_NEW',30)) AS [S] ([NAME], [TYP])
ON [T].[NAME] = [S].[NAME]
WHEN MATCHED THEN
UPDATE SET [TYP]=[S].[TYP]
WHEN NOT MATCHED THEN
INSERT ([NAME], [TYP]) VALUES ([NAME], [TYP]);

In case anyone is interested here is the hugsql query:

-- :name insert-or-update-values! :!
-- :doc Insert the values into the database or if it matches the supplied where columns update the row
/* :require [wcc-variables.db.sql-macros :refer [columns values merge-set-  columns merge-on-columns]] */
MERGE INTO :i:table [T]
  USING ( VALUES --~ (values params options)
        ) AS [S] --~ (columns params options)
        ON --~ (merge-on-columns params options "T" "S")
WHEN MATCHED THEN
 UPDATE
 SET --~ (merge-set-columns params options "S")
WHEN NOT MATCHED THEN
 INSERT --~ (columns params options)
 VALUES --~ (columns params options)

And the output of a simple query it creates in hugsql's queryvec format:

[MERGE INTO [dbo].[IMOS] [T]
  USING ( VALUES (?,?,?,?,?,?,DEFAULT,?,?,DEFAULT,DEFAULT,DEFAULT,?,DEFAULT,DEFAULT,?)
        ) AS [S] ([CATEGORY],[OPTINFO],[PRODUCER],[FAMILY],[WERT],[NAME],[POS],[ORDERID],[SOURCE],[CATALOG_ID],[DATE_LASTCHANGE],[LENGTH],[OPTNR],[SYS],[WORKPLAN_ID],[TYP])
        ON [T].[NAME] = [S].[NAME] AND [T].[ORDERID] = [S].[ORDERID] AND   [T].[TYP] = [S].[TYP] AND [T].[OPTNR] = [S].[OPTNR]
WHEN MATCHED THEN
 UPDATE
 SET [CATEGORY]=[S].[CATEGORY] AND [OPTINFO]=[S].[OPTINFO] AND [PRODUCER]=[S].[PRODUCER] AND [FAMILY]=[S].[FAMILY] AND [WERT]=[S].[WERT] AND [POS]=[S].[POS] AND [SOURCE]=[S].[SOURCE] AND [CATALOG_ID]=[S].[CATALOG_ID] AND [DATE_LASTCHANGE]=[S].[DATE_LASTCHANGE] AND [LENGTH]=[S].[LENGTH] AND [SYS]=[S].[SYS] AND [WORKPLAN_ID]=[S].[WORKPLAN_ID]
WHEN NOT MATCHED THEN
 INSERT ([CATEGORY],[OPTINFO],[PRODUCER],[FAMILY],[WERT],[NAME],[POS],[ORDERID],[SOURCE],[CATALOG_ID],[DATE_LASTCHANGE],[LENGTH],[OPTNR],[SYS],[WORKPLAN_ID],[TYP])
 VALUES ([CATEGORY],[OPTINFO],[PRODUCER],[FAMILY],[WERT],[NAME],[POS],[ORDERID],[SOURCE],[CATALOG_ID],[DATE_LASTCHANGE],[LENGTH],[OPTNR],[SYS],[WORKPLAN_ID],[TYP]) Wardrobe Set - 1 Wardrobe Trim Stile Rail Zone Reveal JEFFWCC 1_WRDB_TM_FAM_STIR 0 1_WRDB_TM_STIR_ZR  IMOSADMIN 0 100]

According to MS-DOCS:

Table Value Constructor (Transact-SQL)

  DEFAULT

  Forces the Database Engine to insert the default value defined for a column. If a default does not exist for the column and
  the column allows null values, NULL is inserted. DEFAULT is not valid
  for an identity column. When specified in a table value constructor,
  DEFAULT is allowed only in an INSERT statement.

You cannot use DEFAULT in a MERGE statement. I suppose it is because it cannot evaluate DEFAULT value for a single TVC.

SELECT * FROM (VALUES(1, 'D')) AS T(A,B);
GO

 A | B
-: | :-
 1 | D

SELECT * FROM (VALUES(1, DEFAULT)) AS T(A,B);
GO

Msg 156 Level 15 State 1 Line 1
Incorrect syntax near the keyword 'DEFAULT'.

But, as far as you know DEFAULT values of your table, you could change the word DEFAULT by the actual default value:

MERGE INTO [dbo].[IMOS] [T]
USING
    (VALUES ('!Default_Materials_1', 0), ('00_NEW',30)) AS [S] ([NAME], [TYP])
ON [T].[NAME] = [S].[NAME]
WHEN MATCHED THEN
UPDATE SET [TYP]=[S].[TYP]
WHEN NOT MATCHED THEN
INSERT ([NAME], [TYP]) VALUES ([NAME], [TYP]);

Example:

CREATE TABLE TBL
(
    ID INT IDENTITY PRIMARY KEY,
    FOO INT DEFAULT 0,
    BAR VARCHAR(10) DEFAULT 'X'   --< DEFAULT value for BAR
);
GO

INSERT INTO TBL VALUES (1, 'A'), (2, 'B'), (3, 'C');
GO

Now replace DEFAULT by 'X':

MERGE INTO TBL
USING (VALUES (1, 'X')) AS T(A,B)
ON T.A = TBL.FOO
WHEN MATCHED THEN
UPDATE
    SET BAR = B
WHEN NOT MATCHED THEN
INSERT (FOO, BAR) VALUES (T.A, T.B);

SELECT * FROM TBL;
GO

ID | FOO | BAR
-: | --: | :--
 1 |   1 | X
 2 |   2 | B
 3 |   3 | C

db<>fiddle here

In HugSQL:

MERGE INTO TBL
USING (VALUES (?, 'X')) AS T(A,B)
ON T.A = TBL.FOO
WHEN MATCHED THEN
UPDATE
    SET BAR = B
WHEN NOT MATCHED THEN
INSERT (FOO, BAR) VALUES (T.A, T.B);

Using a TYPE

On SQL-Server 2014 you could use a TYPE (alias data type).

CREATE TABLE dbo.MyTest
(
    Id int primary key,
    Val char(3) DEFAULT 'aaa'
);

INSERT INTO dbo.MyTest VALUES (1,'V1'),(2,'V2'),(10,'V10'),(11,'V11');

CREATE TYPE dbo.Mytype AS
TABLE
(
    Id  int PRIMARY KEY,
    Bar char(3) DEFAULT 'aaa'
);

Obviously table and type schema must be the same.

DECLARE @myData Mytype;

INSERT INTO @myData(Id, Bar) VALUES (1, DEFAULT),(3, 'V3')

MERGE INTO dbo.MyTest
USING (SELECT Id, Bar FROM @myData) src
ON MyTest.Id = src.Id
WHEN MATCHED THEN
UPDATE SET Val = src.Bar
WHEN NOT MATCHED THEN
INSERT (Id, Val) VALUES (src.Id, src.Bar);

SELECT * FROM MyTest;
GO

Id | Val
-: | :--
 1 | aaa
 2 | V2
 3 | V3
10 | V10
11 | V11

db<>fiddle here


How to set up Amazon RDS parameter group for Postgres? I'm trying to create a parameter group in Amazon Web Services for an Postgres 9.5 RDS instance. I'm a little confused by the units being used for things such as shared_buffers and min_wal_size. Typically these would be specified in postgresql.conf with units, like 4GB or 128kB. If I enter the same in AWS I get an error that the value I specified was invalid, but strictly numeric values seem to be fine. Does anyone know what units these parameters are expecting?

Amazon RDS documentation provides a table of units for each parameter under the "Working with PostgreSQL Parameters" topic.  Units are also usually included in the description of each parameter in the parameter group edit UI from the Amazon RDS web console.

min_wal_size (Postgres 9.5 Parameter Group Family)

Default: 16
Units: MB
Allowed Values: 2-201326592
Description: (16MB) Sets the minimum size to shrink the WAL to.

shared_buffers (Postgres 9.5 Parameter Group Family)

Default: {DBInstanceClassMemory/32768}
Units: 8kB
Allowed Values: 16-1073741823
Description: (8kB) Sets the number of shared memory buffers used by the server.

Regarding shared_buffers specifically, the documentation has this to say:

  Note that some parameters use units that you might not be familiar with; for example, shared_buffers sets the number of 8 KB shared memory buffers used by the server.


How to represent the following ternary relationship The following image shows that each department can be
associated with several employees and locations, and each location can be associated
with several departments and employees; however, each employee is associated with a single department and location.

Question:-
what if I want to depict that an employee can work at multiple departments but only at a single location?
i.e., I want to represent a many to many relationship between employees and departments and between departments and locations
but a many to one relationship between Employees and Locations

This is a great question that shows the importance of breaking down n-ary relationships based on the business rules contained in them and is a good example of the application of forth normal form which deals with multi-value dependencies (MVD).

The following business rules exist:

Each employee may be assigned to one or more departments.
Each department may be composed of one or more employees.
Each department may be operating in one or more locations since some point in time.
Each location may be an address of one or more departments since some point in time.
Each employee must be found in one and only one location which is by definition a location that is an address of a department the employee is assigned to.
Each location may be an address of one or more employees.

Following is an ERD in Barker-Ellis notation created using Oracle SQL Developer Data Modeler showing these rules:

The key to the solution is to decompose the ternary relationship into the binary relationships supported by the business rules.  Those were the many to many relationships between employees and departments, and between departments and locations.  Then, implement the one to many relationship between location and employees that shows that, while an employee may be assigned to many departments, they can be found at only one of that department's many locations.

Note that it is not possible to depict the portion of rule 5 above that states "...which is by definition a location that is an address of a department the employee is assigned to."  This is a good example of the limitations of ERDs.  Their strength is the ability to visually depict entities and their relationships, and their weakness is they cannot depict more complex business rules beyond relationships.  This is why it is vital to investigate and discover all the business rules and not just the referential rules governing associations.  Fabian Pascal's paper Business Modeling for Database Design provides excellent detail on all the necessary rules to explore.  Ronald Ross has generated a large body of work on this topic as well.  Chris Date also wrote a short book on business rules.


Get tablespace of a given table in DB2 What is the command to get the name of tablespace of a given table in DB2 database ?

On Db2 for LUW you can use this query. You get one row per table data partition (1 row for a non range partitioned table).

SELECT
    SD.TBSPACE AS DATA_SPACE
,   SL.TBSPACE AS LONG_SPACE
,   SI.TBSPACE AS INDEX_SPACE
FROM
          SYSCAT.DATAPARTITIONS P
JOIN      SYSCAT.TABLESPACES   SD ON SD.TBSPACEID = P.TBSPACEID
LEFT JOIN SYSCAT.TABLESPACES   SL ON SL.TBSPACEID = P.LONG_TBSPACEID
LEFT JOIN SYSCAT.TABLESPACES   SI ON SI.TBSPACEID = P.INDEX_TBSPACEID
WHERE
    TABSCHEMA = 'YOUR_SCHEMA'
AND TABNAME   = 'YOUR_TABLE'


Diagnosing Blocking issues using Blocked Process report I have an application that is going down as a result of timeouts. During these times we see a massive spike in response times from the database that holds ASP session state.

In an effort to to diagnose the issue I have used SP_CONFIGURE'blocked process threshold',15 and ran a trace to catch any events that are fired. During the next incident the trace capture some blocking events in the Session State database.

I've identified the lead blocker and can see that it's suspended and waiting.

<blocked-process-report>
    <blocked-process>
        <process id="process53fb4c8"
                 taskpriority="0" logused="0"
                 waitresource="KEY: 10:72057594039238656 (594b17b7493c)" waittime="20631"
                 ownerId="9710782702" transactionname="UPDATE"
                 lasttranstarted="2013-06-27T17:29:38.130" XDES="0xc429df950" lockMode="U"
                 schedulerid="14" kpid="6912" status="suspended" spid="359" sbid="0" ecid="0"
                 priority="0" trancount="2" lastbatchstarted="2013-06-27T17:29:38.130"
                 lastbatchcompleted="2013-06-27T17:29:38.107"
                 clientapp=".Net SqlClient Data Provider" hostname="WEB03" hostpid="7520"
                 loginname="State" isolationlevel="read committed (2)" xactid="9710782702"
                 currentdb="10" lockTimeout="4294967295"
                 clientoption1="536870944" clientoption2="128056">
            <executionStack>
                <frame line="18" stmtstart="1114" stmtend="4494"
                       sqlhandle="0x03000a00210f020741a1be0058a000000100000000000000"/>
            </executionStack>
            <inputbuf>
                Proc [Database Id = 10 Object Id = 117575457]
            </inputbuf>
        </process>
    </blocked-process>
    <blocking-process>
        <process status="suspended" waittime="1046" spid="217" sbid="0" ecid="0"
                 priority="0" trancount="1" lastbatchstarted="2013-06-27T17:29:29.113"
                 lastbatchcompleted="2013-06-27T17:29:29.010"
                 clientapp=".Net SqlClient Data Provider" hostname="WEB02" hostpid="6236"
                 loginname="CState" isolationlevel="read committed (2)" xactid="9710776428"
                 currentdb="10" lockTimeout="4294967295" clientoption1="536870944"
                 clientoption2="128056">
            <executionStack>
                <frame line="18" stmtstart="1114" stmtend="4494"
                       sqlhandle="0x03000a00210f020741a1be0058a000000100000000000000"/>
            </executionStack>
            <inputbuf>
                Proc [Database Id = 10 Object Id = 117575457]
            </inputbuf>
        </process>
    </blocking-process>
</blocked-process-report>

My questions are

What the unit of time is for waittime="1046"?
What resource is the wait on?
Where to go from here?

waitresource="KEY: 10:72057594039238656 (594b17b7493c)" waittime="20631"
             ownerId="9710782702" transactionname="UPDATE"

The blocked process is waiting on a KEY (therefore on a row), for 20631 ms (~20s).

The blocking transaction is still executing a statement, likely the UPDATE that causes the blocking. It is not blocked, if it would be blocked then it wouldn't be the tip of the wait chain. Is executing, right now is suspended (not blocked!) meaning it just yielded CPU.

Looks like you have a long running UPDATE statement, most likely it has a missing index and triggers a table scan which blocks all readers. Use sys.dm_exec_sql_text on the handle 0x03000a00210f020741a1be0058a000000100000000000000 to retrieve the exact statement being executed.

BTW your blocked process is exactly the same UPDATE (same handle, same proc, same offsets) which is not surprising at all. Looks like your stored proc with object id 117575457 contains an UPDATE that is not optimized.


SQL 2014 Availability group of Same DB produces different size Backup from Node A to Node B I have this code in a SQL Job to run a database backup on the hour.

IF sys.fn_hadr_backup_is_preferred_replica('AdventureWorks') = 1
BEGIN
  declare @seq varchar(7) = '-' + Right('00' + cast(datepart(HOUR, getdate()) % 24 as varchar(2)), 2) + '.bak'
  ...
  WITH INIT, FORMAT, COPY_ONLY, CREDENTIAL = 'AdventureWorksAzureBackupStorage', compression, stats=1
END

The question I have is if I take out the check for fn_hadr_backup_is_preferred_replica
and just run

BEGIN
  declare @seq varchar(7) = '-' + Right('00' + cast(datepart(HOUR, getdate()) % 24 as varchar(2)), 2) + '.bak'
  ...
  WITH INIT, FORMAT, COPY_ONLY, CREDENTIAL = 'AdventureWorksAzureBackupStorage', compression, stats=1
END

on first A, then on B, then the size of the databases are vastly different, e.g. Active Node database backup is 8 times larger than passive node database backup.

Is there an explanation for this please? Is the Active node not compressed perhaps? And the passive node is compressed?

In AGs writes can only occur on the primary. Since the AlwaysOn Availabiliy Groups feature was introduced, we got new options to make the backups strategy more complete, but also more complex. Taking an advantage of secondary replicas, we can offload both, the FULL and even the Transaction Log backups from the Primary Replica to the Secondary, leaving the Primary replica dedicated to serve the production application.

For example i am attaching here a 'Backup preferences' Window screen for your better understanding.

Note

1)Full and differential backups always run on the primary replica.
2)Full copy only and transaction log backups run either on the primary replica, if the primary replica is set as the preferred replica for running backups, or on the secondary replica with the highest backup priority.
3)For successful backup of availability databases, verify that the secondary replica can communicate with the primary replica, and that the replicas are either Synchronized or Synchronizing.

As per mssqltips blog documentation  Here

Microsoft has provided us with a system function called sys.fn_hadr_backup_is_preferred_replica. This function will return 0 if the current instance is not the preferred backup location or 1 if it is the preferred replica for backups according to the preference and priority settings

IF sys.fn_hadr_backup_is_preferred_replica(@DatabaseName) = 1
BEGIN
  --Perform backup
END

The AG behavior of the “Backup Priority” setting:

The replicas with the lower value will be in the end of the queue.
Those replicas will only be used if no other is available.
The replicas with the higher priority value will be on the top of the backup replica choice.
In the case of have two or more replicas with the same priority value (weight) SQL Server will give priority based on the alphabetical order.

For Your further refence Understanding backups on AlwaysOn Availability Groups & Here


Different table size on sp_spaceused and standard report "Disk Usage by Top Tables" I ran sp_spaceused and Disk Usage by Top Tables standard report for a table.

The results for sp_spaceused are:

name rows    reserved    data    index_size  unused
SomeTable  <1301755>    <7691344 KB>    <3931672 KB>    <3673840 KB>    <85832 KB>

However Disk Usage by Top Tables report shows:

Table Name  # Records   Reserved (KB)       Data (KB)   Indexes (KB)    Unused (KB)
SomeTable   1.301.755   4.340.216       3.931.672   324.776 83.768

Record count is the same but there is a big gap between the two in terms of space used. sp_spaceused shows 7.691.344 KB as reserved while the report shows 4.340.216 KB. Which one is correct?

Frankly, I wouldn't use either. You can find your biggest tables immediately - with more flexibility - and not worry about where @updateusage has been set.

CREATE PROCEDURE dbo.TopTables
  @NumberOfObjects INT = 100,
  @MinimumSizeInMB INT = 10
AS
BEGIN
  SET NOCOUNT ON;

  SELECT TOP (@NumberOfObjects)
    [object] = QUOTENAME(s.name) + N'.' + QUOTENAME(t.name),
    index_count = COUNT(i.index_id),
    size_in_MB = SUM(p.reserved_page_count)*8/1024.0
  FROM sys.schemas AS s
  INNER JOIN sys.objects AS t
  ON s.[schema_id] = t.[schema_id]
  INNER JOIN sys.indexes AS i
  ON t.[object_id] = i.[object_id]
  INNER JOIN sys.dm_db_partition_stats AS p
  ON t.[object_id] = p.[object_id]
  AND i.index_id = p.index_id
  WHERE t.is_ms_shipped = 0
  GROUP BY s.name, t.name
  HAVING SUM(p.reserved_page_count)*8/1024.0 >= @MinimumSizeInMB
  ORDER BY size_in_MB DESC;
END
GO


Proper way to index table columns which can be filtered in multiple ways I have a comments table with the usual fields in it, i.e. author, title, body, created_at, etc. There's also a field called "user_id" which stores the id of the user the comment is directed at.

On the front end I've created a filter UI that basically allows users to filter comments based on date, whether user has responded to a comment, full text body search, category, etc. All these filters are optional and can be combined to create one fairly large query. It essentially looks like this:

SELECT * FROM comments WHERE user_id = ? AND .... AND ...

My first approach was to index user_id and then leave it at that. However, after reading more about it I'm not sure that's the best way. On the other hand, creating an index for each column that's "filterable" doesn't seem like a good idea either.

Any suggestions?

Edit: Postgres documentation seems to answer my question https://www.postgresql.org/docs/9.6/static/indexes-bitmap-scans.html

Every index adds some value, and comes at a cost.  It's up to you to figure out whether the value outweighs the cost.

In order to get a handle on the value, you have to find out which queries use the index, and how much slower those queries run without the index in place.

In order to get a handle on the cost, you have to find out how much slower updates run on the underlying table, and how much space the index itself takes up.

There are tools to help you evaluate these facts.  Facts are objective.  The value you place on them is not.  How much value you place on the query speed up and how much cost you assign to the update slowdown depends on what you are trying to achieve, and on your relative priorities.  That's up to you.


Capitalize only the first letter of each word of each sentence in SQL Server I want to capitalize only the first letter of each word of each sentence in a SQL column.

For example, if the sentence is:

  'I like movies'

then I need the output:

  'I Like Movies'

Query:

declare @a varchar(15)

set @a = 'qWeRtY kEyBoArD'

select @a as [Normal text],
upper(@a) as [Uppercase text],
lower(@a) as [Lowercase text],
upper(left(@a,1)) + lower(substring(@a,2,len(@a))) as [Capitalize first letter only]

Here I did upper, lower, and capitalize first letter only in my column (here I put just a random word).

Here are my results:

Is there any possibilities to do that?

Any possibilities to get results without using user defined function?

I need the output Qwerty Keyboard

declare @a varchar(30);

set @a = 'qWeRtY kEyBoArD TEST<>&''"X';

select stuff((
       select ' '+upper(left(T3.V, 1))+lower(stuff(T3.V, 1, 1, ''))
       from (select cast(replace((select @a as '*' for xml path('')), ' ', '<X/>') as xml).query('.')) as T1(X)
         cross apply T1.X.nodes('text()') as T2(X)
         cross apply (select T2.X.value('.', 'varchar(30)')) as T3(V)
       for xml path(''), type
       ).value('text()[1]', 'varchar(30)'), 1, 1, '') as [Capitalize first letter only];

This first converts the string to XML by replacing all spaces with the empty tag <X/>. Then it shreds the XML to get one word per row using nodes(). To get the rows back to one value it uses the for xml path trick.


Speed problem with a simple select statement I'm having a problem with a select statement taking way too long to run.  I would like to know if I'm doing something wrong or if it taking this long makes sense. I am using Microsoft SQL 2005 standard.  The select I am running takes about 30 seconds to run and return about 60000 records.  Here's the sql:

SELECT l.ID, l.Number, l.Date, l.FirstName + ' ' + l.LastName Name,
    isnull(l.Amount,0) Amount1, isnull(l.Interest,0) Interest1,
    isnull(l.Interest2,0) Interest2, l.LastPayment,
    l.NextDueDate, isnull(l.Aging,0) Aging, isnull(l.InterestRate,0) InterestRate,
    ls.DisplayValue Status, lt.DisplayValue Type, l.LocationID
    FROM Table1 l
    JOIN Table2 ls ON (l.Table2ID=ls.ID)
    JOIN Table3 lt ON (l.Table3ID=lt.ID)
    WHERE
    l.Table3ID IN (SELECT Value FROM fn_Split('5,4,3', ','))
    AND l.Table2ID IN (SELECT Value FROM fn_Split('8,20,9,14,18,21,22,26,13,10,27,25,23,24,16,3,11,6,4,12,19,17,5,28,7,15', ','))
    AND l.LocationID IN (SELECT Value FROM fn_Split('4,6,8,9,10,14,11,12,13', ','))

I've added indexes but nothing seems to speed it up.  If I remove everything in the select except for l.ID and l.Number I can get it to complete in about 8 seconds, if that helps at all.  This DBA stuff is not my area of expertise so any help at all would be greatly appreciated. Thank you.

Tough to say just from looking at it, but here's a good way to figure out where to start:

Run your query from SSMS with the execution plan turned on (right click anywhere and choose "Include Actual Execution Plan"). Read from right to left, top to bottom. Each icon will display a percentage - figure out where the query is spending the most time then start there.

General advice: try to turn scans into seeks by adding indexes; look for big discrepancies between estimated and actual number of rows - this indicates statistics are out of date; look at "Number of Executions" - I suspect those functions are executing many many times.


How to get the summary(sum) from Name wise and product wise I have a 3 tables,

1.Payment Collector

Product

Payment

Payment collectors are collecting payments product wise and stored into payment table. products are assigned to payment collectors some products are not assign to collectors.. question is how to retrieve all the payment collectors product wise total collected amount result should be,

Note: data should be retrieve horizontal way and vertical way  (two queries)

Thanks.

This is a long one, but it should work for you, accomplishing what you want.

The Setup

USE [test];
GO;

CREATE TABLE dbo.PaymentCollector
    (
    PaymentCollectorID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , CollectorName VARCHAR(100) NULL
    );

CREATE TABLE dbo.Product
    (
    ProductID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , ProductName VARCHAR(100) NULL
    );

CREATE TABLE dbo.Payment
    (
    PaymentID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , PaymentCollectorID INT NOT NULL REFERENCES dbo.PaymentCollector(PaymentCollectorID)
    , ProductID INT NOT NULL REFERENCES dbo.Product (ProductID)
    , Amount DECIMAL(7,2) NOT NULL
    );

INSERT INTO dbo.PaymentCollector
(CollectorName)
VALUES ('John')
    , ('Anna')
    , ('Lee')
    , ('Andrew');

INSERT INTO dbo.Product
(ProductName)
VALUES ('Card')
    , ('Loan')
    , ('OD');

INSERT INTO dbo.Payment
(PaymentCollectorID, ProductID, Amount)
VALUES (1,1,100.00)
    , (2,3,50.00)
    , (4,1,200.00)
    , (1,2,30.00)
    , (3,2,40.00)
    , (1,3,10.00)
    , (2,2,100.00)
    , (1,3,250.00)
    , (3,2,40.00)
    , (4,3,60.00);

Query 1 - Horizontal

WITH CTE_Data AS
    (
    SELECT P.PaymentCollectorID
        , P.ProductID
        , SUM(Amount) AS Total
    FROM dbo.Payment AS P
    GROUP BY P.PaymentCollectorID
        , P.ProductID
    )
    , CTE_AllList AS
    (
    SELECT PC.PaymentCollectorID
        , PR.ProductID
    FROM dbo.PaymentCollector AS PC
        CROSS JOIN dbo.Product AS PR
    )
SELECT PC.CollectorName
    , PR.ProductName
    , C.Total
FROM CTE_AllList AS A
    LEFT OUTER JOIN CTE_Data AS C ON C.PaymentCollectorID = A.PaymentCollectorID AND C.ProductID = A.ProductID
    INNER JOIN dbo.Product AS PR ON PR.ProductID = A.ProductID
    INNER JOIN dbo.PaymentCollector AS PC ON PC.PaymentCollectorID = A.PaymentCollectorID;

Query 2 - Vertical (using Pivot)

WITH CTE_Data AS
    (
    SELECT P.PaymentCollectorID
        , P.ProductID
        , SUM(Amount) AS Total
    FROM dbo.Payment AS P
    GROUP BY P.PaymentCollectorID
        , P.ProductID
    )
    , CTE_AllList AS
    (
    SELECT PC.PaymentCollectorID
        , PR.ProductID
    FROM dbo.PaymentCollector AS PC
        CROSS JOIN dbo.Product AS PR
    )
    , CTE_Pivot AS
    (
    SELECT PC.CollectorName
        , PR.ProductName
        , C.Total
    FROM CTE_AllList AS A
        LEFT OUTER JOIN CTE_Data AS C ON C.PaymentCollectorID = A.PaymentCollectorID AND C.ProductID = A.ProductID
        INNER JOIN dbo.Product AS PR ON PR.ProductID = A.ProductID
        INNER JOIN dbo.PaymentCollector AS PC ON PC.PaymentCollectorID = A.PaymentCollectorID
    )
    SELECT CollectorName, [Card], [Loan], [OD]
    FROM CTE_Pivot
        PIVOT (SUM(Total) FOR ProductName IN ([Card], [Loan], [OD])) AS PT;


Tree Node Calculation - SQL Server I'd appreciate some help with something I am trying to achieve please.

The parts that my company make are built from sub assemblies and sub assemblies of sub assemblies.
This looks like:

             1
         /   \  \
        /     |   \
       2      3    7
      / \    / \
     4   5  4   6

Where 4 & 5 come together to make 2, 4 & 6 make 3, then assemblies 2, 3 & 7 make 1.

My table looks like this;

+------+-----+-------+----------+----------------+
| ROOT | SUB | LEVEL | LEADTIME | TOP LEVEL PART |
+------+-----+-------+----------+----------------+
|    1 |   1 |     0 |        4 |              1 |
|    1 |   2 |     1 |        2 |              1 |
|    1 |   3 |     1 |        5 |              1 |
|    1 |   7 |     1 |       20 |              1 |
|    2 |   4 |     1 |        4 |              1 |
|    2 |   5 |     1 |        1 |              1 |
|    3 |   4 |     2 |        4 |              1 |
|    3 |   6 |     2 |        5 |              1 |
+------+-----+-------+----------+----------------+

Each part has a lead time of how long it takes to make it. What I want to do is pass a top level part number and it calculate the overall lead time for the part.

Originally I thought I could just group by the Level and take the maximum lead time per level and add them together but that wouldn't be accurate. Level 1 Part 7 takes 20 days to make so we would be able to be making all of the other parts whilst 7 was being made. The calculation needs to be based on the part that consumes its parent (for want of a better phrase).

What I need to do is calculate the lead time for every route through the tree and add up the lead times as it goes. So I would like to return a table something like;

+----+----------------+-------------------------+------+------------+
| ID | Top Level Part | Parts Nodes Combination | Days | [BASED ON] |
+----+----------------+-------------------------+------+------------+
| 1  | 1              | 1 >> 2 >> 4             | 10   | 4 + 2 + 4  |
| 2  | 1              | 1 >> 2 >> 5             | 7    | 4 + 2 + 1  |
| 3  | 1              | 1 >> 3 >> 4             | 13   | 4 + 5 + 4  |
| 4  | 1              | 1 >> 3 >> 6             | 14   | 4 + 5 + 5  |
| 5  | 1              | 1 >> 7                  | 24   | 4 + 20     |
+----+----------------+-------------------------+------+------------+

I can then find the MAX of the days column which will tell me that the overall lead time for Part 1 is 24 days.

I really hope I've made my post understandable and all is formatted correctly. I've tried to read up on recursive SQL queries but I'm struggling to get my head around them. The most number of levels I am ever likely to have is 4 so I'd hope it wouldn't be too pc intensive. If so, I'd much rather be able pass all of my parts through, rather than doing them one part at a time (hence why I've got a top level part column in my table).

Any help or advice would be greatly appreciated.

This solution also gets the intermediate nodes

;WITH RecursiveCTE
AS
(
SELECT  [SUB] ,
        [SUB] as [Top Level Part],
        CAST([SUB] as varchar(255)) as [Parts Nodes Combination],
        0 AS [LEVEL],
        [LEADTIME]  as [Days],
        CAST([LEADTIME] as varchar(255)) as [BASED ON]
FROM dbo.Levels s1
WHERE [LEVEL] = 0

UNION ALL

SELECT  s1.SUB as [Top Level Part],
        RecursiveCTE.[Top Level Part],
        CAST(RecursiveCTE.[Parts Nodes Combination]+ ' >> ' + cast(s1.[SUB] as varchar(255)) as varchar(255)),
        RecursiveCTE.[LEVEL] + 1 as [LEVEL],
        RecursiveCTE.[Days] + s1.[LEADTIME] as [Days],
        CAST(RecursiveCTE.[BASED ON] + ' + ' + CAST(s1.[LEADTIME] as varchar(255))  as  varchar(255))
FROM dbo.Levels s1
INNER JOIN RecursiveCTE
ON s1.[ROOT] = RecursiveCTE.[SUB]
WHERE s1.[LEVEL] > 0
)
SELECT * FROM RecursiveCTE
WHERE [LEVEL] != 0;

Result

SUB Top Level Part  Parts Nodes Combination LEVEL   Days    BASED ON
2       1               1 >> 2                  1       6       4 + 2
3       1               1 >> 3                  1       9       4 + 5
7       1               1 >> 7                  1       24      4 + 20
4       1               1 >> 3 >> 4             2       13      4 + 5 + 4
6       1               1 >> 3 >> 6             2       14      4 + 5 + 5
4       1               1 >> 2 >> 4             2       10      4 + 2 + 4
5       1               1 >> 2 >> 5             2       7       4 + 2 + 1

DB<>Fiddle

If you want to filter out the intermediate nodes:

;WITH RecursiveCTE
AS
(
SELECT  [SUB] ,
        [SUB] as [Top Level Part],
        CAST([SUB] as varchar(255)) as [Parts Nodes Combination],
        0 AS [LEVEL],
        [LEADTIME]  as [Days],
        CAST([LEADTIME] as varchar(255)) as [BASED ON]
FROM dbo.Levels s1
WHERE [LEVEL] = 0

UNION ALL

SELECT  s1.SUB as [Top Level Part],
        RecursiveCTE.[Top Level Part],
        CAST(RecursiveCTE.[Parts Nodes Combination]+ ' >> ' + cast(s1.[SUB] as varchar(255)) as varchar(255)),
        RecursiveCTE.[LEVEL] + 1 as [LEVEL],
        RecursiveCTE.[Days] + s1.[LEADTIME] as [Days],
        CAST(RecursiveCTE.[BASED ON] + ' + ' + CAST(s1.[LEADTIME] as varchar(255))  as  varchar(255))
FROM dbo.Levels s1
INNER JOIN RecursiveCTE
ON s1.[ROOT] = RecursiveCTE.[SUB]

WHERE s1.[LEVEL] > 0

)

SELECT * FROM RecursiveCTE
WHERE [LEVEL] != 0
AND NOT EXISTS
(
SELECT * FROM
dbo.Levels s2
WHERE RecursiveCTE.SUB = s2.[ROOT])
;

Result

SUB Top Level Part  Parts Nodes Combination LEVEL   Days    BASED ON
7           1               1 >> 7           1      24      4 + 20
4           1               1 >> 3 >> 4      2      13      4 + 5 + 4
6           1               1 >> 3 >> 6      2      14      4 + 5 + 5
4           1               1 >> 2 >> 4      2      10      4 + 2 + 4
5           1               1 >> 2 >> 5      2      7       4 + 2 + 1

DB<>Fiddle

Table & Data used

CREATE TABLE dbo.Levels ([PARTID] INT,[ROOT] int,[SUB] int,[LEVEL] int, [LEADTIME] int,[TOP LEVEL PART] int);
--PartID added
INSERT INTO dbo.Levels
 ([PARTID], [ROOT] ,[SUB] ,[LEVEL] , [LEADTIME] ,[TOP LEVEL PART])

VALUES
(1,1 , 1 ,0 ,4 ,1 ),
(1,1 , 2 ,1 ,2 ,1 ),
(1,1 , 3 ,1 ,5 ,1 ),
(1,1 , 7 ,1 ,20 ,1 ),
(1,2 , 4 ,1 ,4 ,1 ),
(1,2 , 5 ,1 ,1 ,1 ),
(1,3 , 4 ,2 ,4 ,1 ),
(1,3 , 6 ,2 ,5 ,1 );

Edit

Op comments:

  I've noticed the data is repeating a lot (particularly on larger
  assemblies)

  Basically I have Sub Router 1691, on top level router 1731. the Parts
  node combination is 1731 -- 1727 -- 1691 and this repeats exactly the
  same columns 8 times. Then, when it introduces another node (so 1731
  -- 1727 -- 1691 -- 1682), this one repeats 168 times. This is the case all of the way through. It appears that any 3 level nodes repeat 8
  times & any 4 level nodes 168 times.

  On a different one it repeats far fewer times. I'm wondering whether
  it has something to do with the number of unique parts in the tree.

  I think I've figured it out! A single sub can appear on several
  different top level parts. On the join to the recursiveCTE table I've
  also added 'and RecursiveCTE.[Top Level Part] = s1.TopLevelRouter' and
  I think it's done the trick.

Part of the query that needed to be changed:

 ...
FROM dbo.Levels s1
INNER JOIN RecursiveCTE
ON s1.[ROOT] = RecursiveCTE.[SUB]
and RecursiveCTE.[Top Level Part] = s1.TopLevelRouter
WHERE s1.[LEVEL] > 0
)
SELECT * FROM RecursiveCTE
WHERE [LEVEL] != 0;


Check if encrpytion key is valid in DB2 I have a DB2 table where one of the columns is encrypted using the two parameter variant of ENCRYPT (so the password is supplied in the SQL itself). Unfortunately due to an ETL issue the column got populated by data with two different encryption keys. I want to fix this, by running some kind of script, but unfortunately I cannot do a mass update as DECRYPT_CHAR will throw an exception on the first row where it couldn't use my encryption key.

Is there a way to write a SQL script that would go through all of the rows and re-encode the rows which use the wrong encryption keys without throwing an exception? I'd prefer plain SQL if possible.

Yes there is. You could do this with SQL PL to create a stored procedure to do this. Or if you prefer other script languages you could use KSH, BSH, or even Perl.

The point being to catch the errors or exceptions (a little easier to do with SQL PL IMHO) and then you can react to them the way you wish.


How to delete Missing archive log file I’d needed to stop archive log mode for some reason.

When I tried to change the mode message told me to some file have been missing.

Alter database noarchivelog;

ORA-01111: name for data file 5 is unknown - rename to correct file

ORA-01110: data file 5: 'C:\APP\JAC484\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00005'

SQL> select FILE#,ERROR from v$recover_file where FILE#=5;

FILE# ERROR

---------- ------------------------------------------------

 5 FILE MISSING

SQL> select file_id ,file_name from dba_data_files where file_id in (select FILE
    # from v$recover_file where ERROR like 'FILE MISSING' and FILE# = 5);

FILE_ID

----------

FILE_NAME

--------------------------------------------------------------------------------

5

C:\APP\JAC484\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00005

I’d like to recover or delete above MISSING00005 file.

However, there isn’t any physical file.

Does anyone know how to operate?

I've already delete archivelog all; from RMAN.

Il looks like you (or someone else) deleted the file n°5 BEFORE you were putting DB in noarchivelog.
You have to restore this file from a previous backup and recover it to the time you stopped the DB for your operation.


Select last non-null value for given partition - Postgres 10 I have a table with the following structure:

date                city cntry  free  occ
2018-08-13 16:30:00 2    12           5
2018-08-13 16:30:00 2    12     0
2018-08-13 14:30:00 2    12     1
2018-08-13 11:00:00 2    12     2
2018-08-12 13:00:00 2    12     1
2018-08-12 13:00:00 2    12           4
2018-08-12 08:00:00 2    12           3
2018-08-12 08:00:00 2    12     2
2018-08-10 15:30:00 2    12           4

date is timestamp without timezone
the rest are numbers

I want to get the previous non-null value for a given city_id/country_id combo, for both the free and occupied:

date                city cntry  free  occ
2018-08-13 16:30:00 2    12     0     5
2018-08-13 14:30:00 2    12     1     4
2018-08-13 11:00:00 2    12     2     4
2018-08-12 13:00:00 2    12     1     4
2018-08-12 08:00:00 2    12     2     3
2018-08-10 15:30:00 2    12     1     4

partition is by city_id, country_id, order by date
for the first date of each partition, if there is a NULL values, it becomes zero
for an existing partition, it uses the previous value
I'd like the ability to filter by city_id and country_id AFTERWARDS

Basically I'm merging two timelines and I want to keep the previous values (free/occupied) for each partition of <city_id, country_id>.

I tried playing with window functions to no avail. I can get my data for either free values or occupied values, but not both.

How can I accomplish this?

You can try to use MAX function with coalesce to make it.

Schema (PostgreSQL v9.6)

CREATE TABLE T(
   date date,
   city_id int,
   country_id int,
   free int,
   occupied int
);

insert into T values ('2017-01-01',2,3,null, 2);
insert into T values ('2017-01-02',2,3,4, null);
insert into T values ('2017-01-02',2,3,null, 5);
insert into T values ('2017-01-04',3,4,2, null);

Query #1

SELECT
  date,
  city_id,
  country_id,
  coalesce(MAX(free),0) free  ,
  coalesce(MAX(occupied),0) occupied
FROM T
GROUP BY date,city_id,country_id
order by date;

| date                     | city_id | country_id | free | occupied |
| ------------------------ | ------- | ---------- | ---- | -------- |
| 2017-01-01T00:00:00.000Z | 2       | 3          | 0    | 2        |
| 2017-01-02T00:00:00.000Z | 2       | 3          | 4    | 5        |
| 2017-01-04T00:00:00.000Z | 3       | 4          | 2    | 0        |

View on DB Fiddle


Merge multiple tables in a database I have a MySQL database with table names like 1,2,3... upto 1000

Each tables have 2000 rows and has the same structure.

This is how all my 2000 table's structure look like

id,url,title,content

id has type int, primary key,auto increment.

Now I would like to create a new database with name merged. Also a table with name merged_table.

Can someone tell me how to import all my tables in merged_table?

My problem here is all my tables has id from 1 to 2000. So I have no idea how to import them.

I would like to have unique ids from 1 to 2000000 in my new merged_table

I'm using ubuntu. So if someone suggest me a terminal command, that would be awesome.

Create the first table, then load each table into the merged_table:

CREATE TABLE merged_table LIKE `1`;
INSERT INTO merged_table (url,title,content) SELECT url,title,content FROM `1`;
INSERT INTO merged_table (url,title,content) SELECT url,title,content FROM `2`;
INSERT INTO merged_table (url,title,content) SELECT url,title,content FROM `3`;
...
...
;
INSERT INTO merged_table (url,title,content) SELECT url,title,content FROM `999`;
INSERT INTO merged_table (url,title,content) SELECT url,title,content FROM `1000`;

Here is a way to script it (if the database is mydb)

DB="mydb"
MYSQL_CONN="-uroot -ppassword"
echo "CREATE TABLE merged_table LIKE \`1\`;" > MergeData.sql
SQL="SELECT CONCAT('INSERT INTO merged_table (url,title,content)"
SQL="${SQL} SELECT url,title,content FROM \`998\`')"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_name REGEXP "$[0-9]"
mysql ${MYSQL_CONN} -ANe"${SQL}" >> MergeData.sql

Look at the output of the file

less MergeData.sql

If it looks like the output I gave at the top of my answer, then run

mysql ${MYSQL_CONN} -D${DB} < MergeData.sql

Give it a Try !!!


Does NULL have a type? Various sources (eg Wikipedia, PSOUG) state that Oracle's null does not have a type. Is this true?

What about other RDBMSs?

Oracle:

The null literal does not have a type, but

null can be cast to any type, and this may be necessary when

calling overloaded procedures or functions
controlling the return type of the decode function, eg:

select decode('A','B',to_char(null),'A','1') from dual;
DECODE('A','B',TO_CHAR(NULL),'A','1')
-------------------------------------
1

select decode('A','B',to_number(null),'A','1') from dual;
DECODE('A','B',TO_NUMBER(NULL),'A','1')
---------------------------------------
                                      1

controlling column types of set operators like union when the first query block includes a null

null values stored in the database always have a type:

create table t(n integer, s varchar(10));
insert into t values(null, null);

select decode('A','B',n,'A','1') from t;
DECODE('A','B',N,'A','1')
-------------------------
                        1

select decode('A','B',s,'A','1') from t;
DECODE('A','B',S,'A','1')
-------------------------
1


Updating across row and column in SQL Bit of a strange question. Entries in my table have three relevant columns:

drug
date_1
date_2
seq_id

I am trying to make it such that every drug with the lowest seq_id has a date_2 equal to the date_1 of the drug with the second lowest seq_id (the second lowest will always be lowest + 1) with the same drug name.

Does this make sense?

I don't have any real clue how to go about doing this. I've managed to make a query that returns the date_1 and seq_id of every second-lowest record, and now I want to make it such that every row with a seq_id exactly one lower than any seq_id in this set of records has date_2 = date_1 of the record with the 1-higher seq-id.

God this is really confusing, I hope it comes through.

The SQLServer version of this could look like this.

First we want the drug table and some values

create table #drugs (drug nvarchar(10),date_1 date, date_2 date, seq_id int)
 insert into #drugs values ('sildenafil',NULL,NULL,1)
                          ,('sildenafil',NULL,NULL,2)
                          ,('sildenafil',NULL,NULL,3)
                          ,('tadalafil',NULL,NULL,1)
                          ,('tadalafil',NULL,NULL,2)
                          ,('tadalafil',NULL,NULL,3)
                          ,('vardenafil',NULL,NULL,1)
                          ,('vardenafil',NULL,NULL,2)
                          ,('vardenafil',NULL,NULL,3)

Then we need to update the next lowest seq_id with a date

update #drugs
  set date_1 = getdate()
   where seq_id =
   (select min(seq_id)+1
    from #drugs d2
    where #drugs.drug = d2.drug)

And the update the lowest date_2 value with the date_1 value from the second lowest seq_id - This is update with a join with the strange looking SQL-Server syntax and is

update D1
  set D1.date_2 = d2.date_1
  From #drugs D1
   inner join
    (select drug,date_1,seq_id
     from #drugs d1
     where seq_id = (select min(seq_id)+1 from #drugs)
    ) as d2
     on d1.drug = d2.drug and d1.seq_id = d2.seq_id-1

And Clean up

Drop Table #drugs


Script out objects that depend on in SQL Server I have a requirement wherein I need to script out the definition of all objects that depend on a single table. See the below example.

Let's say I have a table and there are 15 objects(views, SPs, Functions, triggers etc) that depend on it. Then I need definition of all those 15 objects.

This needs to be done on 15 servers. Is there a way to script all those objects using a T-SQL script? Currently I am noting all the objects down in an excel and scripting out definitions of all the objects manually which is very tedious. :(

In SSMS...

Rt Click on the Database in question, navigate to Tasks and then Generate Scripts...
Select Choose Objects on the left and then click the radio button to Select specific database objects
Select the object you wish to script
In Save to file click the Advanced button
6th option down, set Generate Script for Dependent Objects to true
Set any other options you want and click the OK button
Set the File name and path or Save to Clipboard or Save to new query window
Click the Next buttons to finish.


Multiple Agent Jobs Running same SSIS Package I have the following doubt, is there a possibility to have faild a Job when are running simultaneously as the following example:

4 SQL Agent Jobs running the same SSIS in different schedules, I've heard that the max number of possible execution is 4, but not sure if that is a parameter that can be changed or not, if so, have you heard about it ?

You may be referring to the MaxConcurrentExecutables property.  It controls how many parallel threads may run during the execution of the package and it can be changed on the package properties.

The default is -1 which translates to 'how many cores ya got'+2.
I haven't ever heard of anything that would stop either the SQL Agent or the Integration Server to limit the number of concurrent executions of a single package.

Check out this article on MaxConcurrentExecutables.


performance benefits of shrinking a database I have a database of 1.5 TB, which had only 200 GB free space before.Due to performance issues, business decided to reduce 1 month data retention policy to 1 week.Due to this 3 weeks data was deleted and now it has 700 GB free space.

The 500 GB free space increased due to data delete is no more required, and there no chance to grow data again to occupy this 500 GB space.

I am planning to shrink the database(using shrink file command ) and run index optimization process to  rebuild indexes, if fragmentation is greater than 30%.
The released space wont be used by other systems, however can be used for log file and temp db growth efficiently in near future.

Is there any benefits on performance by reducing the size of database, other than space release ?

Previous questions related to shrinking(in stack exchange) does not properly answer to my queries.

Thanks in advance for your kind reply !!!

If you are maintaining your indexes as you say, there will be no performance benefit.

Performance will actually get worse from shrinking, and your next index rebuild job will likely have significantly more work to do than usual.

The most common reason people think that the shrinking of a database works to increase performance is because the engine has to read fewer pages for queries, however by shrinking you're not actually freeing up any pages, you're just shifting them around in the database, leading to fragmentation and degraded performance. You do reduce the overall number of pages for the database, but not the number of used pages holding data.

Another point is it's likely to take an extremely long time to shrink that database, due to the shrink process being a single-threaded process. It will likely also cause performance issues during the shrink, and potentially blocking.

Check out When is it OK to shrink a database DBA SE question

I would highly recommend against shrinking.

One option for you might be to transfer the data into a fresh, empty copy of the same database, then delete the old one - this is likely to be quicker and tidier.


Creating Compound Indexes on Many Flags So, according to this article, low-cardinality indexes may negatively impact performance:
http://www.ibm.com/developerworks/data/library/techarticle/dm-1309cardinal/

Let's say you have 15-20 yes/no flags you want to be able to search on in any combination, with a kicker of having the final column of the index be your SORT BY column of high-cardinality values. Let's say we have 5 or so different columns we want to be able to sort on. We want our queries to always use an index without having to do any extra table scans or use temporary tables or do any extra sorting.

To create all of these indexes, it would require a permutation of all of these combinations - you're gonna have a lot of indexes to update. And it's possible it's going to be very inefficient if I understand the above article correctly.

Is it possible to consolidate all of your flags into one column using some kind of bitmasking technique? I'm vaguely familiar with the concept but pretty clueless when it comes to the execution.

P.S. If it makes a difference, I'm using MySQL.

Bottom line:  Can't be optimized.

But, you can speed things up some by minimizing the size of the rows.  Such will be especially beneficial if the table is too big to be cached.

Here's one approach.

Put the flags in a single MEDIUMINT UNSIGNED column.  That will give you up to 24 yes/no values in a mere 3 bytes.  DATE is 3 bytes.  Your id is perhaps 4 bytes.  Put those three things into an index -- total of 10 bytes (plus a lot of overhead).

I suggest this ordering:  INDEX(date, flags, id).  Then have code like:

SELECT id FROM tbl
    WHERE date >= ... AND date < ...
      AND (flags & $mask) = $value;

The steps will be

Range scan using the first column of the index (date) to limit the number of index 'rows' to search.
Perform the boolean operation to filter out rows based on the flags
Get the id so you can go off to the table to get whatever other info you need.

Something like:

SELECT b.*
    FROM (
        SELECT ... -- as above
         ) AS filter
    JOIN tbl AS b  USING(id)
    ORDER BY ...

The ORDER BY on the end won't be able to use any index, but it will benefit from having all the filtering already done, so only the desired number of rows will be involved.


Join to get most specific record for tenant without PIVOT I have a Settings table and a Tenant table.
There is a hierarchy for that an Account can have 1 or many Companies and a Company can have 1 or many `Facilities'.
Account 1
   ---> Company 1
          ---> Facility 1
          ---> Facility 2
   ---> Company 2
          ---> Facility 3
          ---> Facility 4

They may have a default setting that applies to their entire account....
| FacilityId | CompanyId | AccountId | SettingValue |
|------------|-----------|-----------|--------------|
|     (null) |    (null) |         1 |            5 |

...except they have one override for Facility 3 that only applies to Facility 3, all other facilities will use the default setting value at the account level.
| FacilityId | CompanyId | AccountId | SettingValue |
|------------|-----------|-----------|--------------|
|          3 |    (null) |         1 |            6 |

I want to create a join between them to get the most specific setting for each tenant. Most specific is defined as the Setting record that matches the Tenant's FacilityId is more specific than a match on CompanyId which is more specific than a match on AccountId and finally, if no match is found, use the setting that has NULL for all 3 values.
I do not want to use the PIVOT feature as the code uses Entity Framework and LINQ and there is no LINQ to SQL for PIVOT. Basically need simple SQL that you could create a view for...so no temp tables, etc. Not looking for a stored proc solution if at all possible.
SQLFiddle
Table: Settings
| FacilityId | CompanyId | AccountId | SettingValue |
|------------|-----------|-----------|--------------|
|          1 |         1 |         1 |            5 |
|     (null) |         2 |         2 |            7 |
|     (null) |         1 |         1 |            4 |
|     (null) |    (null) |         2 |            6 |
|     (null) |    (null) |         1 |            3 |
|     (null) |    (null) |    (null) |            2 |

Table: Tenants
| FacilityId | CompanyId | AccountId |
|------------|-----------|-----------|
|          1 |         1 |         1 |
|          2 |         2 |         2 |
|          3 |         3 |         3 |

So join on these would have this desired output:
| FacilityId | CompanyId | AccountId | SettingValue |
|------------|-----------|-----------|--------------|
|          1 |         1 |         1 |            5 |
|          2 |         2 |         2 |            7 | --> this account would match to a setting value of 6 or 7, but the 7 value matches more specifically
|          3 |         3 |         3 |            2 | --> there is no match on Facility, Company, or Account so match to all nulls.

In code, I am doing the following to get the most specific Setting for a given Tenant, but I now need to do this for a large set of Tenant data and hence want to do it by a SQL Join. For those unfamiliar with LINQ the double pipe (||) is equivalent to OR.
private SettingViewModel GetSettingBy(string strKey)
{
    var allSettings = GetAllSettings();
    var settingQuery = allSettings.Where(x => x.SettingKey == strKey);

    if (_accountCompanyFacilityViewModel.AccountId.HasValue)
    {
        settingQuery = settingQuery.Where(x => (x.AccountId == _accountCompanyFacilityViewModel.AccountId || x.AccountId == null));
    }

    if (_accountCompanyFacilityViewModel.CompanyId.HasValue)
    {
        settingQuery = settingQuery.Where(x => (x.CompanyId == _accountCompanyFacilityViewModel.CompanyId || x.CompanyId == null));
    }

    if (_accountCompanyFacilityViewModel.FacilityId.HasValue)
    {
        settingQuery = settingQuery.Where(x => (x.FacilityId == _accountCompanyFacilityViewModel.FacilityId || x.FacilityId == null));
    }

    var setting = settingQuery
            .OrderByDescending(x => x.FacilityId)
            .ThenByDescending(x => x.CompanyId)
            .ThenByDescending(x => x.AccountId)
            .FirstOrDefault();

    return setting;
}

Here's the SQL Fiddle for the Answer

Thanks for the great data setup. Here's one way to get these hits in SQL.
As Aaron mentioned, this can be a CTE without any varibles.
WITH cte AS (
                SELECT  t.AccountId
                        ,t.CompanyId
                        ,t.FacilityId
                        ,CASE
                             WHEN s.AccountId IS NOT NULL THEN 1
                             ELSE 0
                         END + CASE
                                   WHEN s.CompanyId IS NOT NULL THEN 1
                                   ELSE 0
                               END + CASE
                                         WHEN s.FacilityId IS NOT NULL THEN 1
                                         ELSE 0
                                     END AS HitCount
                        ,s.SettingValue
                FROM    dbo.Tenant AS t
                        LEFT OUTER JOIN dbo.Settings AS s ON t.FacilityId LIKE ISNULL(CAST(s.FacilityId AS VARCHAR), '%')
                                                             AND   t.CompanyId LIKE ISNULL(
                                                                                CAST(s.FacilityId AS VARCHAR)
                                                                                ,'%'
                                                                                )
                                                             AND   t.AccountId LIKE ISNULL(
                                                                                CAST(s.AccountId AS VARCHAR)
                                                                                ,'%'
                                                                                )
            )
SELECT  t.AccountId
        ,t.CompanyId
        ,t.FacilityId
        ,t.SettingValue
FROM    cte AS t
        CROSS APPLY (
                        SELECT  MAX(t2.HitCount) AS MaxHits
                        FROM    cte AS t2
                        WHERE   t.AccountId = t2.AccountId
                                AND t.CompanyId = t2.CompanyId
                                AND t.FacilityId = t2.FacilityId
                    ) AS hc
WHERE   1 = 1
        AND hc.MaxHits = t.HitCount;