Unable to Create new Maintenance plan in SSMS 2017 Hi I have reinstalled SQL Server 2016 in my database server last day.
I am able to connect to SQL Instance(SQL-02) using the SSMS 2017 i had earlier in the same server before reinstall.

But when i try to create the maintenance plans,i get the below error.

Exception has been thrown by the target of an invocation. (mscorlib)

Retrieving the COM class factory for component with CLSID {8357986F-76E9-4E0A-A780-FB0541E91ECC} failed due to the following error: 8007007e The specified module could not be found. (Exception from HRESULT: 0x8007007E). (Microsoft.SqlServer.ManagedDTS)

I have done enough research on this and learned from several posts from web that i have to run the below command

REGSVR32.EXE dts.dll

and then disconnect from SSMS and reconnect.
For most of the people ,this solved the issue and for me i still the issue.

I have my sql server in d: drive and i ran the above command as administrator in the below location.

D:\Program Files\Microsoft SQL Server\130\DTS\Binn

I get the message that the .dll is loaded successfuly.
But when i try to create a new maintenance plan i still get the same error.

I didn't find any other solution for this issue.

Any help is appreciated.

Additional Info:

I have another database sever in the same domain.
I can connect the same instance(SQL-02) in that server's SSMS 2017 and can create new maintenance plan for the instance.
I am using the same version of SSMS in both servers.

I was just searching for this error as it just occurred on my instance as well. Although it wasn't identical, I wanted to put this out there if anyone else runs into the same problem when searching.

Server 2019
SQL Server Standard 2017 (14.0.2027.2)
SSMS 2018 (v.18.5.1)

Went to create a new maintenance plan (the first one on this server and named instance) and received a very similar error out of the gate: Exception has been thrown by the target of an invocation. (mscorlib) Invalid Class String.
I checked all components, etc and my fix was to install SSMS 2017 and use it rather than SSMS 2018. Works just fine now.


Sql get results only containing all keywords Let's say I have mysql table called SONGS, with columns:

Artist | Featuredartist | Songname | Year

What I need is to get Artist and Songname, only containing my keywords (and no matter how they are sorted):

For example:

ACDC Metallica 2000 (should get me only results of Metallica featured ACDC (or reversed) and only of year 2000.

The same results I should get using query sorted another way, i.e: 2000 ACDC Metallica.

The same with 2 or one keywords, "Metallica ACDC", for example. It should get me results of songs by metallica feat. ACDC and ACDC feat Metallica, of all years.

I have tried to use UNION, but it gets me results containing one of keywords.

$search = "ACDC Metallica 2000"; // or Metallica ACDC or ACDC 2000, etc.
$sql = '';
$count = 0;
foreach($search as $text)
{
  if($count > 0){

     $sql = $sql."UNION Select * From songs WHERE artist LIKE '%$text%' and featuredartist LIKE '%$text%' and year MATCH '%$text%'";

  }else{

     $sql = $sql."Select * From songs WHERE artist LIKE '%$text%' or featuredartist LIKE '%$text%' and year MATCH '%$text%'";

  }
  $count++;
}

How could I achieve this?

I have found the answer and I will write it here as it may be useful for someone.

First I have altered tables by marking it up on Mysql database and clicking "Fulltext".

To use multiple keywords, use this code (which will get ) before mysql query (in php):

$search="ACDC 2000"; // any keyword
$search=explode(' ', $search );
$Asearch = array();
foreach($search as $result) {
    $Asearch[] .= '+'.$result.'*';
}

Then use this mysql query:

SELECT * FROM songs WHERE MATCH(artist,featuredartist,year) AGAINST('".join(' ',$Asearch)."' IN BOOLEAN MODE)

You will get only results which include all entered keywords.


MySQL QPS debugging? I see 5 Kqps on my DB server during 5 mins interval on zabbix dashboard. But sum of all max queries(Delete/select/Insert/Update/Replace) during that interval come out to be 1300.
Not sure how come max 5kqps is displaying but sum of all max is 1300 ? My objective is to find which specific DML(insert/update/select/delete) is causing high QPS here  and get some direction from here ? Another point is to understand is there anything else that can cause high QPS apart from these DML so that I can move in right direction. I am bit confused how come Max QPS can be some of individual DML QPS or I am missing anything here ?
Also IOPS during that shows 1Kops, if each query is is even hitting disk, shouldn't it be close to 5Kops ? Where can be the gap ?

As SQL, show global status like 'com_%' will show you a break down of the types of queries. Any high volume of update, replace, delete should be an indication that you shouldn't be using MyISAM.
Given there are 5kqps SHOW FULL PROCESSLIST at any instant should give you a sample of activity.
Don't worry about IOPs, focus on the queries and watch the benefits flow from that. If queries are contained to the primary key there is no reason for them to result in disk IOPs. Filesystem cache is also used so that's another buffer preventing IOPs.
If its the IOPs (hardware not handling it) that's the problem consider moving to InnoDB. If you value your data, move to InnoDB.


How to do MySQL User Accounting I can't seem to find any information on this anywhere. I found a question here called Monitor MySQL activity per database? but that's not quite the answer either.

I'd like to be able to log the following to a system log somewhere, preferably via syslog:

User (not master/slave) login
User logout
User connect time
User IP

Something similar to last and lastb for MySQL. Can this be done? Is anything already written and in the Ubuntu and Red Hat Enterprise Linux repositories? Can MaatKit do this?

Thanks!

Your only recourse would be to activate the slow log and use it as a MyISAM table. By default, the slow log would normally be a text file. However, a general_log table was provided in newer release of MySQL as a CSV table.

mysql> use mysql
Database changed
mysql> show create table slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL,
  KEY `start_time` (`start_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.03 sec)

mysql>

Make sure you have this in my.cnf and then restart mysql if you had to add this:

[mysqld]
log-output=TABLE
log-slow-queries
long-query-time=0.00001

Next, convert the table to MyISAM and index it on the needed columns:

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
ALTER TABLE mysql.slow_log ENGINE = MyISAM;
ALTER TABLE mysql.slow_log ADD INDEX starttime_index (start_time);
ALTER TABLE mysql.slow_log ADD INDEX db_starttime_index (db,start_time);
SET GLOBAL slow_query_log = @old_log_state;
SHOW CREATE TABLE mysql.slow_log\G

You can query the table and retrieve the user and host from mysql.slow_log column user_host.

You can crontab some tasks to copy the data from mysql.slow_log where you need it, may a separate database.

CREATE DATABASE IF NOT EXISTS mystatus;

Perhaps, you can run queries like this:

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
ALTER TABLE mysql.slow_log RENAME mystats.slow_log;
CREATE TABLE mysql.slow_log LIKE mystats.slow_log;
SET GLOBAL slow_query_log = @old_log_state;

This will move the slow log elsewhere and start with an empty one.

This may not be a full answer, but I hope this is useful to you as to some direction to take.


Is there risk importing data with auto increment id #'s that are higher than the current numbers? I have to import data into a new database.  That new database already has a small set of data.  I have two large tables I need to import in there.

Is there any risk of a higher id getting over written or causing problems later as new data comes in?

This is the current setup of the database tables.

-- phpMyAdmin SQL Dump
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

Table websites

    CREATE TABLE IF NOT EXISTS `url` (
      `id` int(20) NOT NULL AUTO_INCREMENT,
      `userid` int(16) NOT NULL DEFAULT '0',
      `alias` varchar(10) NOT NULL,
      `special` varchar(160) NOT NULL,
      `website` text NOT NULL,
      `city` text NOT NULL,
      `story` text NOT NULL,
      `date` datetime NOT NULL,
      `pass` varchar(255) NOT NULL,
      `click` bigint(20) NOT NULL DEFAULT '0',
      `meta_title` varchar(255) NOT NULL,
      `meta_description` varchar(255) NOT NULL,
      `ads` int(1) NOT NULL DEFAULT '1',
      `bundle` mediumint(9) NOT NULL,
      `public` int(1) NOT NULL DEFAULT '0',
      `archived` int(1) NOT NULL DEFAULT '0',
      `type` varchar(64) NOT NULL DEFAULT '',
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=127878 ;

Table statistics

    CREATE TABLE IF NOT EXISTS `scores` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `shorr` varchar(255) NOT NULL,
      `curlid` bigint(20) NOT NULL,
      `curluserid` bigint(20) NOT NULL DEFAULT '0',
      `date` datetime NOT NULL,
      `ip` varchar(255) NOT NULL,
      `country` varchar(255) NOT NULL,
      `website` varchar(50) NOT NULL,
      `referal` text NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1807671 ;

LOAD the new data into a separate table (staging), without including the AUTO_INCREMENT field.
Decide whether there are any duplicates that need to be avoided.
INSERT ... SELECT (without id) to copy the "new" data into the real table.
DROP TABLE staging;


which node will execute events in master-master setup Am newbie in this database replication. Right now on server master-master setup with Galera setup.

If I have events running in the database. It means from which master its running.
If one master down then the second master will execute that events or for that something can be done in the configuration.

Anyone like to suggest something related to it ?

Welcome to the MariaDB monitor.  Commands end with; or \g.
Your MariaDB connection id is 4123677
Server version: 10.1.39-MariaDB-1~stretch mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab, and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(test)]>

As far as I know, events in a MariaDB Galera Cluster must be configured to run on only one of the nodes. While CREATE EVENT is replicated to the other nodes, you have to choose which node to use for running the event scheduler: Execute SET GLOBAL event_scheduler = ON; on the node where you want events to run, or set it in your config file if you want it the setting to survive a restart.

If that node is down, then there is no automatic failover for events, so if you'd like to run the events on another node, you'll have to enable the event scheduler on that node.

You could perhaps control the event_scheduler setting through software: a script could poll each node, and make sure the setting is enabled on one and only one node.


How can I configure Ola Hallengren IndexOptimize script to run against a subset of tables I'm using Ola Hallengren IndexOptimize script against a SQL 2016 database that is 7 TB in size, with over 300,000 tables.  I only have a 6 hour window each night to manage indexes.    I'm using the timelimit parameter to stop the job after 6 hours.

The problem is, every night the index job starts at the beginning of the indexes alphabetically, and only gets through about the same 4,000 tables.

What can I do to get the index job to cover all the indexes in the database?   Perhaps by creating multiple jobs, one for each night of the week that do a subset of indexes?   Or is there a way to have the job start back up the next day where it previously left off?

All the tables are in the same database schema.  This is a vendor-supplied database, I'm unable to make changes to the database schema.

Thanks in advance for any guidance.

My current job  steps are as follows:

EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 10,
@FragmentationLevel2 = 40,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@PartitionLevel = 'N',
@MaxDOP = 0,
@SortInTempdb = 'Y',
@TimeLimit = 21600,
@LogToTable = 'Y'

Don't defrag your indexes every night. You don't need to anymore on modern hardware (especially with solid-state storage with zero seek times). For daily maintenance, you only need to update modified statistics, and even then you only need to do this when you notice performance degradation (i.e. check what your monitoring system tells you).

Per Ola's site, you can do something like this:

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

The other thing you can do, for when you do need to defragment indexes (if for example you have deleted a large amount of data), is using SQL Server's Service Broker to set up the maintenance plan for multiple tables in a batch.

That article is in-depth and difficult to summarise in this reply, but it comes down to creating an asynchronous queue, that fires Agent jobs in parallel.


How to replicate MySql database between two 2012 server I am setting up a disaster recovery strategy for two 2012 servers DB1 and DB2 in one location and may be later I plan to set them in different locations, I want DB2 to take control incase of of DB1 failure.
They are installed with MySql 5.5 database application and for now DB2 has no data.

How can can I replicate and synch these two servers? and create backups from DB2?

The replication process on windows servers is similar to doing it in any other server system.

First of all I would recommend reading the official MySQL replication documentation (https://dev.mysql.com/doc/refman/5.6/en/replication.html) and then check out this similar question from a while ago. (mysql replication on windows server)

Personally I would suggest using tools that allow you to replicate data between database servers. They can save you all the messy work and even impove performance.

When I wanted to replicate my MySQL database to Amazon Redshift data warehouse (https://www.alooma.com/integrations/mysql/redshift) I used a tool called Alooma that supports several replication methods and provides a 'Near Realtime View' which allows you to query the latest snapshot of your database in your data warehouse.


Why "Data File(s) Used Size (KB)" PerfMon counter doesn't exist? I wanted to create my own SQL Server baseline by collecting data with the Performance Monitor.

Among all counters that I have decided to monitor, I also wanted to have a historic trend about data file(s) growth for each database, but, unfortunately, there's no counter for that. I only have a "Data File(s) Size (KB)" counter, but that refers to the space that the database takes in advance, not the one that is actually using.

I know already that i can get the actual used size by doing some queries, in fact my question is not about how to retrieve that information but why can't i get it from the PerfMon.

Is there any official motivation for that? Laziness from Microsoft developers, some technical problem, or maybe something else?

Also note that I can get with no problem the used size for the log(s) file, which is kind of funny in my opinion!

While it probably doesn't really answer your question I'll post it anyway, it may be more of a comment though.

I don't think anybody outside of Microsoft can give us a definitive reason why this counter was never added.

There is only minor feedback on this connect item asking for a similar thing, but it hasn't been closed as wontfix either:

"Percent Data Used" Performance Counter - by AussieJohn in which Microsoft's last suggestion is:

  Bill: thanks for the suggestion. sp_spaceused provides this
  information and much more. Do you want to expose all or a subset of
  the following info in perfmon?

  space reserved
  space allocated
  unused space that is reseved but not used
  space allocation between index and data

If you really want to expose this information through perfmon you could look at user settable counters as suggested by Marian in this answer


Find the nearest time from another table and add column Please help. I need to create a view of Assembly details and when they have gone through a particular machine. I have two tables. Table 1 has the main assembly data and Table 2 has the machine data.

The final query has all Table 1 fields and the Machine field of Table 2 where the BuildDate (Table 1) is = to the WaveDate (Table 2) and the Wavetime (Table 2) is less than and nearest to the BuildTime (Table 1).

Hopefully the pictures make more sense than my description :/ Any help is greatly appreciated. Many thanks

This answer might contain some typing erros, and is untested, because i cannot test from a picture...

SELECT
   AssemblyNo,
   SerialNo,
   AssemblyType,
   DefectType,
   BuildDate,
   BuildTime,
   x.Machine
FROM Table1 t1
CROSS APPLY (
    SELECT TOP 1 t2.Machine
    FROM Table2 t2
    WHERE t1.BuildDate=t2.WaveDate AND t2.Wavetime <=t1.BuildTime) x


SSDT-BI SQL Version Compatability I'm working on upgrading some SSIS packages from SQL Server 2008 R2 to 2012 and am getting somewhat confused by the SSDT-BI tools.

I have downloaded "Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2013" from here, which according to this supports "SQL Server 2014, SQL Server 2012, SQL Server 2008 and 2008 R2".

When upgrading a package I don't see an option to upgrade "up to 2012" only. In other words, how can I stop the packages being upgraded too far? Or are 2012/14 pretty much cross compatible?

Also, are there other similar implications for SSRS, SSAS solutions?

If I need a different version to ONLY upgrade to 2012, which would I need?

I would stop using VS2013 for SSIS 2012. It builds SSIS 2014 packages that can't be deployed to SSIS 2012. Instead install SSDT-BI for VS2012.

Another option is installing SSDT preview for VS2015. It is a currently a release candidate for SQL 2016 but is able to support downlevel versions back to SSIS 2012. So the story will be much less confusing soon (or now if you are willing to use beta software).


Best Practice for SQL Server data validation I have CSV files that I would like to import into SQL Server 2014. Each record in the files has around 20 columns, each of them should be validated for data type compliance as well as some other checks per column (min/max ranges, null/not null etc.).

I'm looking for advice regarding a model architecture on doing this in a efficient way. Using SSIS and some error tables seems to me a bit inefficient since it looks as if I only can get one column checked at a time and the error handling (especially the column lockup in SSIS is clumsy at best).

I would prefer to have each record processed and right away have an overview of all errors that are in that record, so that I can provide a feedback of all issues at once to the people providing the data for import.

SSIS is, indeed, clunky when it comes to importing data for all the processing prowess that it seems to tout. One of the commenters, Kin I believe, had it right with creating a temporary table. Here's the code we use to import data directly from Excel into a temporary table to be parsed for compliance. As you can see in the example below, using T-SQL, OPENROWSET reads the specific tab on the Excel Spreadsheet called SQL Server Instance Details and imports the data into the SQL Table TempServerMap.

After, the data is imported into the temporary table you have a world of options. You can insert the data into another table that has all the SQL business rules you need on each of the columns--kicking out the data that doesn't qualify--I say this not as a necessarily "best way" but for using the least amount of code in the beginning fine tuning your process of finding errors. You'd need to control the error handling here to catch the bad apples (rows) so you can review them later. Alternately, you could create functions, or procedures, or triggers or all of the above that check the data in advance to make sure the do comply with business requirements.

And if you're looking to communicate those errors to others, just create an SSRS (Reporting Services) report that queries your new error table and give them the proper permissions to view the report. It's web based and easily accessible in an intranet environment. If you have external associates you can have SSRS e-mail the report to them just as easily.

The main point here is: get your data away from Excel and into SQL Server so it can be manipulated effectively.

Some DBAs might be offended at the replace/exec of the code below. If that's the case, just hardcode the select statement with specific excel file names.

declare  @cmdstring varchar(8000) ='select * into SQLSunSet.dbo.TempServerMap from OPENROWSET (''Microsoft.Ace.OLEDB.12.0'', ''Excel 12.0;Database=<MapsExcelFile_SQLServerUsageTracker>'', ''select * from [SQL Server Instance Details$]'')'
set @cmdstring = replace(@cmdstring,'<MapsExcelFile_SQLServerUsageTracker>',@MapsExcelFile_SQLServerUsageTracker)

if exists (select * from sysobjects where type ='u' and name ='TempServerMap') drop table TempServerMap

        --print (@cmdstring)
     exec (@cmdstring)

Many people seem to turn to SSIS in hopes of not coding T-SQL. But I usually find that SSIS is like faulty four-wheel drive. It just get's you stuck more than you would have been a lot farther down the road.

P.S. to use OPENROWSET effectively you might need to install
the Microsoft Access Database Engine 2010 Redistributable (Really--a Microsoft Office Engine--not just for MS Access). This can be downloaded here:

https://www.microsoft.com/en-us/download/details.aspx?id=13255

Additionally, you might need to configure SQL Server to run OPENROWSET and also use the Office engine within T-SQL using the following code after it's installed:

exec sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
GO

exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO


SQL/Access: Iterate through a query's results to use in another's WHERE clause Just to preface, I'm pretty terrible with databases in general (patience appreciated).

I have a table structured as follows:

    TEAM   |PERSON |GAME |SCORE
    _______|_______|_____|_____|
    Apples |bob    |A    |30
    Apples |bob    |B    |15
    Apples |chris  |A    |13
    Apples |chris  |B    |2
    Oranges|sally  |A    |15
    Oranges|sally  |B    |39
    Oranges|tina   |A    |19
    Oranges|tina   |B    |27
    Bananas|larry  |A    |34
    Bananas|larry  |B    |30
    Bananas|harry  |A    |33
    Banasas|harry  |B    |13
    Bananas|charles|A    |32
    Bananas|charles|B    |28

I would like to generate output that shows who scored the highest in each game and what team they are from.

Example:

    GAME  |TEAM   |PERSON  |SCORE
    ______|_______|________|_____|
    A     |bananas|larry   |34
    B     |oranges|sally   |39

Here is what I have so for:

I can grab the list of games via:

SELECT DISTINCT(GAME) from table;

And I can get the highest scorer from a specific game via:

SELECT TOP 1 GAME,TEAM,PERSON,SCORE from table WHERE GAME='A' ORDER BY SCORE DESC;

The part I am having trouble with using the results of the DISTINCT() query as part of the WHERE clause in my second query.  Data is dynamic in my simple database.

Hopefully this can be solved with some SQL kung fu but:
If there is a better way to structure my database, I'm definitely open to those suggestions (right now, it's a single table database, data is ingested/overwritten daily, and dataset size is not expected to grow to problematic sizes, and I don't really know what I'm doing)

Thanks!

You can do this with a subquery to get your max score per game with the outside query pulling the details of the game.
FYI: This was tested in MS SQL, not MS-Access (sorry don't have that installed), but the syntax should be very similar.

SELECT t.*
FROM tablename AS t
JOIN (
    SELECT GAME, MAX(SCORE) AS MAXSCORE
    FROM tablename
    GROUP BY GAME
    ) AS MAXGAMES
    ON ( t.GAME  = MAXGAMES.GAME
     AND t.SCORE = MAXGAMES.MAXSCORE )
ORDER BY t.GAME ;


What is a good auto-generated key for a federated database? I am about to split a large database into a bunch of federated instances.  Currently, most of the primary keys are auto-generated identity ints.  Obviously, that's not going to work in a federated setup.

I've read people using auto-generated GUIDs to replace the ints, but that data type has well-known performance sapping problems on its own.

What are some of the strategies I can use for having unique values for primary keys across federated members?

One alternative is to perform a hash on some existing value or combination of values, for instance the user's login name and autogenerated identity (int):

SELECT
    CONVERT(NVARCHAR(40),
        HashBytes('SHA1', UserName + CAST(UserId AS VARCHAR)) ,2)
    AS FederatedIdentity
FROM Users

This way the value would be consistent across databases. You could persist the hash in a table; I usually create a view to work with generated columns like this.


PostgreSQL: mark current session as "unimportant" If I want to drop a PostgreSQL DB I get this error:

postgres@server:~$ dropdb c123_d
dropdb: database removal failed: ERROR:
        database "c123_d" is being accessed by other users
DETAIL:  There is 1 other session using the database.

I know how to help myself. I can do select * from pg_stat_activity; and see what is still running.

In my case it was a LISTEN.

Is there a way to mark the LISTEN-statement or this session as "unimportant"? In this context this means that the DB is allowed to kill this statement if the database should get shut-down or dropped.

Is there a way to tell PostgreSQL this?

Is there a way to mark the LISTEN-statement or this session as "unimportant"?

Unimportant to you?  Perhaps.

Unimportant to Postgres?  No.

Every connection is as "important" as every other.

If you want to kill off an active connection, look to the pg_cancel_backend function.


Which datatype is used to store video files I am working on a web application using JSP.

I want to ask which datatype(with syntax) is used to store video files in Oracle database.

Oracle Multimedia (formerly known as Oracle interMedia) lets you write applications to manage images, audio,video, and other heterogeneous media data in Oracle databases.

I have referenced interMedia as you are using Oracle 10g.

interMedia User's Guide

Here is a nice answer given by Justin Cave, which explains why would a person store a video file in a BLOB in a database and what are the advantages and disadvantages compared to storing pointers to the location of the video file.

BLOB's in SQL that stores a Video file


Interested in counting number of threshold crossings I would like to determine the number of times that a value has crossed a threshold in either direction in a specified time frame.

As an example, lets say that I am interested in the number of times that an engine has started or stopped. Engine oil pressure is recorded in my table. When this value is < 5, I say that the engine is stopped. When it is > 15, I say that the engine is running. In between those values is a "deadzone"

In the time frame below, the engine has started 3 times and stopped three times.

+-------------+-------------+
| timestamp   | EngineOil_P |
+-------------+-------------+
| 01-01 00:09 |         0.0 |
| 01-01 00:10 |         0.0 |
| 01-01 00:11 |         4.9 |
| 01-01 00:12 |         7.0 |
| 01-01 00:13 |        12.0 |
| 01-01 00:14 |        50.0 | // START!
| 01-01 00:15 |        50.0 |
| 01-01 00:16 |        25.0 |
| 01-01 00:17 |        15.5 |
| 01-01 00:18 |         9.0 |
| 01-01 00:19 |         0.3 | // STOP!
| 01-01 00:20 |         0.3 |
| 01-01 00:21 |         0.0 |
| 01-01 00:22 |         8.0 |
| 01-01 00:23 |        50.0 | // START!
| 01-01 00:24 |        50.0 |
| 01-01 00:25 |        50.0 |
| 01-01 00:26 |        50.0 |
| 01-01 00:27 |        18.0 |
| 01-01 00:28 |         0.0 | // STOP!
| 01-01 00:29 |         0.3 |
| 01-01 00:30 |         6.0 | -
| 01-01 00:31 |         8.0 | - careful!
| 01-01 00:32 |         4.0 |
| 01-01 00:33 |         0.0 |
| 01-01 00:34 |        25.0 | // START!
| 01-01 00:35 |        12.0 | -
| 01-01 00:36 |         9.0 | - careful!
| 01-01 00:37 |        23.0 |
| 01-01 00:38 |         0.0 | // STOP!
+-------------+-------------+

How might I get a result like starts = 3, stops = 3?

The question is tricky because of the deadzone in between 5 and 15.

The first curveball is at 00:29 to 00:33, where the value jumps above 5, but NOT above 15, so that doesn't count as an engine start.

The second curveball is at 00:35 to 00:36, where the value drops below the upper threshold but not below the lower threshold. Here, it would not count as an engine stop

I am using MySQL (Mariadb 10)

See if this meets your specs:

SELECT SUM(turned_on), SUM(turned_off)
    FROM (
        SELECT
                ( @prev <=  5 AND EngineOil_P >  5 ) turned_on,
                ( @prev >= 15 AND EngineOil_P < 15 ) turned_off,
                @prev := EngineOil_P
            FROM ( SELECT @prev := 0 ) init
            JOIN tbl
            ORDER BY timestamp
         ) x;

Note that boolean expressions evaluate to 0 (false) or 1 (true).  Hence the SUM() gets what you want.

There could be a problem with the initial state -- if the first row has > 15, it will be treated as a 'turn on'.


Is it legal for SQL Server to fill PERSISTED columns with data that does not match the definition? I'm following up on this question about strange values in a PERSISTED computed column. The answer there makes a few guesses about how this behavior came to be.

I'm asking the following: Is this not an outright bug? Are PERSISTED columns ever allowed to behave this way?

DECLARE @test TABLE (
    Col1 INT,
    Contains2 AS CASE WHEN 2 IN (Col1) THEN 1 ELSE 0 END PERSISTED) --depends on Col1

INSERT INTO @test (Col1) VALUES
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5))

SELECT * FROM @test --shows impossible data

UPDATE @test SET Col1 = Col1*1 --"fix" the data by rewriting it

SELECT * FROM @test --observe fixed data

/*
Col1    Contains2
2   0
2   0
0   1
4   0
3   0

Col1    Contains2
2   1
2   1
0   0
4   0
3   0
*/

Note, that the data appears "impossible" because the values of the computed column do not correspond to its definition.

It is well known that non-deterministic functions in queries can behave strangely but here this seems to violate the contract of persisted computed columns and, therefore, should be illegal.

Inserting random numbers might be a contrived scenario but what if we were inserting NEWID() values or SYSUTCDATETIME()? I think this is a relevant issue that might practically manifest itself.

This is certainly a bug. The fact that the col1 values happened to be the result of an expression involving random numbers clearly doesn't change what the the correct value for col2 is supposed to be. DBCC CHECKDB returns an error if this is run against a permanent table.

create table test (
    Col1 INT,
    Contains2 AS CASE WHEN 2 IN (Col1) THEN 1 ELSE 0 END PERSISTED);

INSERT INTO test (Col1) VALUES
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5));

DBCC CHECKDB

Gives (for my test run which had one "impossible" row)

Msg 2537, Level 16, State 106, Line 17
Table error: object ID 437576597, index ID 0, partition ID 72057594041008128, alloc unit ID 72057594046251008 (type In-row data), page (1:121), row 0. The record check (valid computed column) failed. The values are 2 and 0.
DBCC results for 'test'.
There are 5 rows in 1 pages for object "test".
CHECKDB found 0 allocation errors and 1 consistency errors in table 'test' (object ID 437576597).

It does also report that

  repair_allow_data_loss is the minimum repair level for the errors
  found by DBCC CHECKDB

And if taken up on the repair option unceremoniously deletes the whole row as it has no way of telling which column is corrupted.

Attaching a debugger shows that the NEWID() is being evaluated twice per inserted row. Once before the CASE expression is evaluated and once inside it.

A possible workaround might be to use

INSERT INTO @test
            (Col1)
SELECT ( ABS(CHECKSUM(NEWID()) % 5) )
FROM   (VALUES (1),(1),(1),(1),(1)) V(X);

Which for one reason or another avoids the issue and only evaluates the expression once per row.


How to setup a PostgreSQL cluster Could you point me to a good book about setting up a PostgreSQL cluster? The goal is to set up an active-passive cluster spread across two different data centers to achieve high availability.

There are a few problems with any recommendations here.  The basic issue is that you have a set of tradeoffs in all replication solutions that you won't find in a single source.

The simple approach is to start with the documentation and work on setting up the built-in replication.  The manual is a good place to start there.  If all you want is a copy of everything that's what you want.  Pay close attention to the hardware and software points there and make sure the systems are essentially the same architecture and OS (i.e. both 64-bit, and both Linux).  Ideally they should be the same OS version and PostgreSQL compiled with the same options (binary packages are good here).

However if that doesn't cut it, here are some book recommendations:

"PostgreSQL 9.0 High Performance" by Greg Smith
and
"The PostgreSQL 9 Admin Cookbook" by Simon Riggs and Hannu Krosing.


"String or binary data would be truncated" error copying nvarchar(10) to char(10) I am inserting values from one SQL table / column into another. The datatypes are different for these columns due to other reasons but I do not understand why nvarchar(10) source and char(10) destination results in an error sometimes in SQL Server 2014:

  String or binary data would be truncated.

len(sourcecol) = 10 and datalength(sourcecol) = 20.

Could it be because of some invisible spaces/characters stored in the source column which is of type nvarchar?

Without having an example of the data as well as the table DDL from the O.P., it is difficult to say for certain what the exact cause of this error is for the O.P. However, this behavior (and hence problem) can happen for others for the following reason:

Some Code Pages (which are determined by the Collation of each CHAR / VARCHAR field) allow for double-byte characters in order to map more than the 256 characters that can fit into a single byte. Since the max length of a string field is really a matter of bytes instead of characters, a CHAR / VARCHAR field with a max length of 10 can only hold 10 bytes, which can hold anywhere from 5 - 10 characters. With the source field being NVARCHAR(10), those 10 characters could map to characters that require more than 1 byte in the VARCHAR field. All it takes to get this error is 9 "regular" single-byte characters and 1 double-byte character which would require 11 actual bytes.

So, check the Collation of the CHAR(10) field in the destination table. Once you know the name of the Collation for that field, that will indicate what Code Page is being used, and if it is either 932, 936, 949, or 950, then this is quite likely the source of the problem. And in this case, just alter the destination field to have a max length of 20 (to be safe in case all 10 characters are double-byte). I would recommend VARCHAR(20), but if you really, really like blank padding, then do CHAR(20) .

A slightly more detailed explanation follows:

It is commonly understood that VARCHAR (i.e. 8-bit Extended ASCII) data is single-byte and NVARCHAR (i.e. Unicode) data is double-byte. This understanding of string data (and of encodings) is always true when working with the US-English alphabet (and with quite a few others, at least most "active" languages). And while it is true more often than not when working with most languages, it is certainly not always true.

The ways in which this understanding is not true for Unicode data is beyond the scope of this Question, and will not be detailed here.

But when it comes to our old friend Mr. VARCHAR, there are some situations that would allow for characters to take up 2 bytes instead of one. Yes, you read that correctly. But how? Well, prior to the existence of Unicode, some cultures with more than 255 characters in their alphabet still wanted to work with their native alphabet. Since that is not possible within a single byte (with a range of 256 values), they came up with Double-Byte Character Sets (DBCS). These are handled as different Code Pages, and Windows and SQL Server support four of them:

932 = Japanese (Shift-JIS within Microsoft, Windows-31J outside of Microsoft)
936 = Chinese Simplified (GB2312)
949 = Korean
950 = Chinese Traditional (Big5)

Don't let the term "Double-Byte" confuse you with the implication that they are just like NVARCHAR which only works in 16-bit sequences. These Code Pages are actually variable-length encodings, similar to UTF-8, and will use a single byte (8 bits) for at least the first 128 values (0 - 127) and some amount of the (128 - 255 range).

How does any of this fit into the truncate error? Well, the max length of both NVARCHAR and VARCHAR datatypes is really expressed in terms of bytes, not characters. Meaning, VARCHAR(10) is 10 bytes max, even if less than 10 characters fit into that 10 bytes. Likewise, NVARCHAR(10) is 20 bytes max, even if less than 10 characters fit into that 20 bytes.

With that in mind, we know that converting from Unicode to a Code Page will attempt to map to the same characters. In most Code Pages, those characters are all 1 byte in size. But in the 4 DBCS Code Pages, there are quite a few characters that exist (and hence can be mapped to) and are 2 bytes (else they wouldn't exist).

The problem here is that a DBCS Code Page is being used (for the destination), and at least one of the characters being mapped is taking up 2 bytes within the VARCHAR type.

The following is a working example of this behavior.

Test Setup

First, run this code to set up the test. The default Collation of the database in which you run this test does not matter. Here we are creating a temporary table to hold each of the first 65,536 Code Points of the main Unicode character set (characters beyond the initial 65,536 require two Code Points and are hence 4 bytes each, but again, out of scope here since they would not change the behavior related to the current issue ;)

SET NOCOUNT ON;

IF (OBJECT_ID(N'tempdb..#Source') IS NOT NULL)
BEGIN
  DROP TABLE #Source;
END;

CREATE TABLE #Source ([Character] NVARCHAR(1));

;WITH nums (num) AS
(
  SELECT TOP (65536) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
  FROM   [master].[sys].[columns] sc1
  CROSS JOIN [master].[sys].[columns] sc2
)
INSERT INTO #Source ([Character])
  SELECT NCHAR(num - 1)
  FROM nums;

SELECT * FROM #Source;

Test 1: Latin1 character set (Code Page 1252)

Running the following will produce no errors. It just works. But it only translates the first 256 values as that is all that can fit into any Single-Byte Character Set (SBCS).

IF (OBJECT_ID(N'tempdb..#Destination_CP1252') IS NOT NULL)
BEGIN
  DROP TABLE #Destination_CP1252;
END;

CREATE TABLE #Destination_CP1252 ([Character] VARCHAR(1) COLLATE Latin1_General_100_CI_AS);

INSERT INTO #Destination_CP1252 ([Character])
  SELECT [Character]
  FROM   #Source;

SELECT [Character],
       LEN([Character]) AS [NumberOfCharacters],
       DATALENGTH([Character]) AS [NumberOfBytes],
       CONVERT(VARBINARY(2), [Character]) AS [BinaryValue]
FROM   #Destination_CP1252;

Test 2: Japanese (Shift-JIS) character set (Code Page 932)

IF (OBJECT_ID(N'tempdb..#Destination_CP932') IS NOT NULL)
BEGIN
  DROP TABLE #Destination_CP932;
END;

CREATE TABLE #Destination_CP932 ([Character] VARCHAR(1) COLLATE Japanese_Unicode_CI_AS);

INSERT INTO #Destination_CP932 ([Character])
  SELECT [Character]
  FROM   #Source;

Running the code shown above will produce the following error:

  Msg 8152, Level 16, State 2, Line 1
  String or binary data would be truncated.
  The statement has been terminated.

If this is an issue of a Double-Byte Character Set (DBCS) conversion, then increasing the field size by 1 byte should fix it. First, let's just make sure that nothing was actually inserted into the table (so that we will know for certain that the next INSERT statement is what put the data in):

SELECT *
FROM   #Destination_CP932;
-- no rows

Great. Now run the following:

ALTER TABLE #Destination_CP932
  ALTER COLUMN [Character] VARCHAR(2) COLLATE Japanese_Unicode_CI_AS;

INSERT INTO #Destination_CP932 ([Character])
  SELECT [Character]
  FROM   #Source;

No errors. Woo hoo! Let's see which characters converted:

SELECT [Character],
       LEN([Character]) AS [NumberOfCharacters],
       DATALENGTH([Character]) AS [NumberOfBytes],
       CONVERT(VARBINARY(2), [Character]) AS [BinaryValue]
FROM   #Destination_CP932
WHERE  1 = 1
--AND    DATALENGTH([Character]) > 1
--AND    [Character] <> '?'

If you scroll through the results, you should pay attention to the [NumberOfBytes] and [BinaryValue] fields.

To see just the double-byte values, uncomment the following line and re-run:

AND    DATALENGTH([Character]) > 1

To see the full range of values in Code Page 932, re-comment-out the DATALENGTH where condition and uncomment the following line and re-run:

AND    [Character] <> '?'

My count shows 9483 characters. And to be fair, add 1 to account for the actual ? character that was excluded, and we get a grant total of 9484 characters represented in a VARCHAR field .


MySQL database backup stuck on 'checking permissions' I'm trying to backup a database on local machine. But the file is only 30MB after hours. Then I thought the hard drive corrupted or something that I tried this on another table and it worked fine.
Even MySQL workbench crashes when I connect to the database. So I used a python connector to print out the size of the databases. Sizes are in MB.
[('mysql', Decimal('18.5')),
 ('information_schema', Decimal('0.0')),
 ('performance_schema', Decimal('0.0')),
 ('sys', Decimal('0.0')),
 ('stocks', Decimal('2875.5')),
 ('test', Decimal('1183.9'))]

The stocks one is the one that got stucked on the checking permissions state. When I export the test it took only 40 mins. Then I went back again to try to export the stocks but still no luck.
I used show processlist to find out what happened
+----+-----------------+----------------+--------+---------+--------+------------------------+------------------------------------------------------------------------------------------------------+
| Id | User            | Host           | db     | Command | Time   | State                  | Info                                                                                |
+----+-----------------+----------------+--------+---------+--------+------------------------+------------------------------------------------------------------------------------------------------+
|  5 | event_scheduler | localhost      | NULL   | Daemon  | 209821 | Waiting on empty queue | NULL                                                                                |
| 77 | root            | localhost:3753 | stocks | Sleep   |   1479 |                        | NULL                                                                                |
| 84 | root            | localhost:4289 | stocks | Query   |      0 | init                   | show processlist                                                                                |
| 86 | root            | localhost:4612 | NULL   | Query   |    442 | checking permissions   | SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SC |
+----+-----------------+----------------+--------+---------+--------+------------------------+------------------------------------------------------------------------------------------------------+

Well at least when I was trying to backup the test it doesn't stuck there.
I used the following command to backup the database
mysqldump -u root -p stocks > stocks.db

When I kill the process the error message was this
mysqldump: Error: 'Lost connection to MySQL server during query' when trying to dump tablespaces
mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'ndbinfo\_version'': MySQL server has gone away (2006)

The test has about 4k tables and the stocks has 20k tables. Is that what's causing this issue?
Any idea what happened? Or is there any other ways I can backup the database?

Rate Per Second = RPS
Suggestions to consider for your my.ini [mysqld] section
innodb_buffer_pool_size=1G  # from 8M for a much better experience
innodb_log_buffer_size=16M  # from 1M to support ~ 30 mn in RAM before write to media
open_files_limit=60000  # from 6209 to better support your opened_* high volume
innodb_open_files=48000  # from 300 to reduce opened_tables RPhr of 24,310
table_open_cache=48000  # from 2000 should always be matched to innodb_open_files
table_definition_cache=6000  # from 1400 to support opened_table_definitions RPhr of 2,152
innodb_io_capacity=1000  # from 200 to use more of your available SSD io capacity

These are just the beginning of tuning for your serious workload.  View profile, Network profile for additional suggestions and free downloadable Utility Scripts to assist with performance tuning.


Create a job that sets Auto Update Statistics on I am using SQL Server 2012. The vendor wants Auto Update Statistics set to false, but my boss wants it set to true.

If I set it to true, after a while it goes back to false by itself. I think the vendor has something to switch it to false in their application.

I want to create a job which checks the whether Auto Update is set false or true. If it is to false, it will turn it to true. If it is true it should leave it true.

This is the script I am going to use in the job:

USE master
GO
ALTER DATABASE AdventureWorks2008
SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT;
GO

But I don’t want the job to do anything when it is already true.

Do I need to change the script or run the way it is?

You need to have a check using:

SELECT name, is_auto_update_stats_on, is_auto_update_stats_async_on
FROM sys.databases

For example:

IF
(
    SELECT COUNT(*)
    FROM [sys].[databases]
    WHERE [name] = 'AdventureWorks_2005'
    AND [is_auto_create_stats_on] = 0
) = 0
BEGIN
    ALTER DATABASE [AdventureWorks_2005]
    SET AUTO_UPDATE_STATISTICS ON
END
ELSE
PRINT 'The auto update stats is ON ... no need to worry !'


Partitioned Tables and Indexes - what are the downsides? When talking about Partitioned Tables and Indexes for tables with less than 100 partitions,

no nonaligned indexes:

with that I mean:

    Nonaligned index

  An index partitioned independently from its corresponding table.

  That is, the index has a different partition scheme or is placed on a
  separate filegroup from the base table.

  Designing an nonaligned partitioned index can be useful in the
  following cases:

  The base table has not been partitioned.

  The index key is unique and it does not contain the partitioning
  column of the table.

  You want the base table to participate in collocated joins with more
  tables using different join columns

Are there other performance drawbacks than:

1 - slowing down some DBCC commands

2 - Queries that use operators such as TOP or MAX/MIN on columns other than the partitioning column may experience reduced performance with partitioning because all partitions must be evaluated.

3 -

  Queries that use partition elimination could have comparable or
  improved performance with larger number of partitions. Queries that do
  not use partition elimination could take longer to execute as the
  number of partitions increases.

Expanding upon your list, here are a couple potential downsides that we have come across in real production workloads:

Seeking into multiple partitions

Expanding on the queries that do not use partition elimination could take longer to execute point, there is a specific pattern that is particularly affected: singleton seeks. This operation will become much slower if all (or even a modest subset of) partitions need to be accessed. The skip scan operation essentially performs a seek into every partition that cannot be eliminated.

Let's say that you have a billion row table (N = 1,000,000,000) with rows divided equally into 1,000 partitions (P = 1,000). A single seek is roughly O(log(N)) ~ 30 in a non-partitioned table. However, this same seek operation becomes roughly O(P*log(N/P)) ~ 20,000 in this hypothetical partitioned table. So the seek now performs over 500x more work if data from all partitions is needed (or sometimes even if it isn't needed, but SQL can't prove that based on your query).

Note that this can come up both when you explicitly query the table for one row (or a small range of rows) and in more complex queries when the partitioned table appears in the innder side of a loop join. The good news is that SQL Server is reasonably good about taking this into account in cost-based optimization, but that still typically means that you get a hash join when a loop-seek into a non-partitioned table would have been far more optimal.

Thread skew in parallel query execution

In parallel query plans, threads are allocated to partitions. If there is one partition that is much larger than the others, queries against the table may be particularly susceptible to thread skew. It's possible that one thread gets too high a proportion of rows and is processing long after the other threads have done their work. This situation can happen with non-partitioned tables as well, but any partition functions that do not equally distribute rows are particularly vulnerable.

See Parallel Query Execution Strategy for Partitioned Objects for a more detailed description of the allocation of threads to partitions. For example:

  The query processor uses a parallel execution strategy for queries that select from partitioned objects. As part of the execution strategy, the query processor determines the table partitions required for the query and the proportion of threads to allocate to each partition. In most cases, the query processor allocates an equal or almost equal number of threads to each partition, and then executes the query in parallel across the partitions.


SQL to XML with multiple values Hello I'm new to converting SQL to XML. I have been trying to get this to work for the past few days.

SELECT Item.foldername AS "foldername",
Item.status,
Item.vhrid,
Item.firstname,
Item.middleinitial,
Item.lastname,
dbo.getEnumDescript(Type, 'Type') AS title,
Item.email,
dbo.getEnumDescript(OfficeLocation, 'OfficeLocation') AS Office,
       (
    select  id.id 'practices/practice/ID',
           id.name'practices/practice/Name'
    from [dbo].[Groups] as aprac
    INNER JOIN [dbo].[PracticeGroups] as practices
    on aprac.PracticeGroupID = practices.ID
    where Item.vhrid = aprac.vhrid
    FOR XML path(''), type
)
FROM [dbo].[Att] as Item
FOR XML path, ROOT ('Item');

It returns this

<Item>
<row>
<foldername>foldername</foldername>
<status>1</status>
<vhrid>3</vhrid>
<firstname>firstname</firstname>
<lastname>lastname</lastname>
<title>title</title>
<email>test@test.com</email>
<Office>office</Office>
<practices>
  <practice>
    <ID>1</ID>
    <Name>test1</Name>
  </practice>
</practices>
<practices>
  <practice>
    <ID>2</ID>
    <Name>test2</Name>
  </practice>
</practices>
<practices>
  <practice>
    <ID>3</ID>
    <Name>test3</Name>
  </practice>
</practices>
<practices>
  <practice>
    <ID>4</ID>
    <Name>test4</Name>
  </practice>
</practices>
</row>

I'm trying to get it in this format.

<Item>
<row>
<foldername>foldername</foldername>
<status>1</status>
<vhrid>3</vhrid>
<firstname>firstname</firstname>
<lastname>lastname</lastname>
<title>title</title>
<email>test@test.com</email>
<Office>office</Office>
<practices>
  <practice>
    <ID>1</ID>
    <Name>test1</Name>
    <ID>2</ID>
    <Name>test2</Name>
    <ID>3</ID>
    <Name>test3</Name>
    <ID>4</ID>
    <Name>test4</Name>
  </practice>
</practices>
</row>

Any help would be thank full.

I took the liberty of assuming that you want the XML to look like this:

<row>
...
<practices>
  <practice>
    <ID>1</ID>
    <Name>test1</Name>
  </practice>
  <practice>
    <ID>2</ID>
    <Name>test2</Name>
  </practice>
  <practice>
    <ID>3</ID>
    <Name>test3</Name>
  </practice>
  <practice>
    <ID>4</ID>
    <Name>test4</Name>
  </practice>
</practices>
</row>

I think this is the solution you're looking for (see my comments for where I've changed your code).

SELECT Item.foldername AS "foldername",
Item.status,
Item.vhrid,
Item.firstname,
Item.middleinitial,
Item.lastname,
dbo.getEnumDescript(Type, 'Type') AS title,
Item.email,
dbo.getEnumDescript(OfficeLocation, 'OfficeLocation') AS Office,
       (
    select  id.id AS [practice/ID],   --- here..
           id.name AS [practice/Name] --- here..
    from [dbo].[Groups] as aprac
    INNER JOIN [dbo].[PracticeGroups] as practices
    on aprac.PracticeGroupID = practices.ID
    where Item.vhrid = aprac.vhrid
    FOR XML path('/practices'), type  --- and here.
)
FROM [dbo].[Att] as Item
FOR XML path, ROOT ('Item');

For obvious reasons, I haven't tried it myself, please let me know if it doesn't work.


SQL SERVER 2016 In-Memory Database In SQL Server 2016, if we create a database in RAM (i.e using memory optimized tables), the database size should not exceed RAM size, am I right in saying that?

Database size can exceed the RAM capacity as it could contain normal disk based tables as well as In-Memory tables. However, the size of memory based tables should not exceed the available memory. This available memory could be less than the RAM capacity as there are other processes that will be consuming some amount of RAM.

List of various out of memory issues that could arise:

Resolve Out Of Memory issues

The memory required for In-Memory tables is not just the table size but it should also include the following:

indexes
row versioning
table variables
growth

Estimating memory requirements for memory optimized tables:

Estimate Memory Requirements for Memory-Optimized Tables


GRANT permission ON database TO user not working on AWS I'm having trouble granting a user permission on AWS.

mysql> GRANT CREATE ON *.* TO 'hmrex_db'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GRANTS FOR 'hmrex_db'@'%';
+------------------------------------------------------------------------------+
| Grants for hmrex_db@%                                                        |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'hmrex_db'@'%' IDENTIFIED BY PASSWORD <secret>         |
| GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON `hmrex_db`.* TO 'hmrex_db'@'%' |
+------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

The user I'm logged in as has GRANT ..., CREATE, ... ON *.* TO 'username'@'%' IDENTIFIED BY PASSWORD <secret> WITH GRANT OPTION
read_only is 0.

I can't work out why this isn't working.

I discovered I had made a typo and was setting the permission on a user that doesn't exist, however, this worked. When I fixed the typo, it still didn't work.

To user/privileges changes be reflected you need to run the following command:

FLUSH PRIVILEGES;

Then you can try SHOW GRANTS againg.


Sqlcmd: Invalid argument - when trying to pass a variable to sqlcmd I'm trying to use a DOS command prompt on Server 2012R2 to run a batch with the following in it.  I want to run a SQL command (sqlcmd) and return the results to the command window.

This is what I'm currently trying but sqlcmd keeps throwing back Sqlcmd: 'test': Invalid argument. Enter '-?' for help.20:56:08.

FOR /F "tokens=* USEBACKQ" %%F IN (`sqlcmd -S localhost -E -i "backup.sql" -v dbname="test"`) DO (
    Echo %%F
)

However if I try it without params/variables it works perfectly!

FOR /F "tokens=* USEBACKQ" %%F IN (`sqlcmd -S localhost -E -i "backup.sql"`) DO (
    Echo %%F
)

Does anyone know a way around getting the variables passed to my sql query using sqlcmd, DOS CMD, as well as a FOR /F loop such as in my first example?

In your command that works, you don't have an equal sign (=).  When you run the command that fails and look at the output, you should notice the equal sign disappears.  That's because that character needs to be escaped with a caret. (in my example below, notice the caret before the equal sign)

Try:

FOR /F "tokens=* USEBACKQ" %%F IN (`sqlcmd -S localhost -E -i "backup.sql" -v dbname^="test"`) DO (
    Echo %%F
)


Is it possible to exclude a database from WAL and basebackup? I'm planning to add a GIS database to an existing PostgreSQL 12 cluster with multiple databases, streaming (hot standby) replication and WAL archiving.
Once created the new database will never be changed, and is easily recreated from the source data. However it would be very large (100s of GB).
The server is currently backed-up using wal-e (periodic base backups plus wal archiving) and has to use physical replication due to use of large objects.
Is there any way to exclude the new database from this process, so that the backups are not significantly slowed and inflated by the new data? It would still need to work for a replication though, making it an even more complex problem.
Is this something that can be done perhaps with tablespaces, which would then be manually copied for initial replications instead of using pg_basebackup?

No, there is no way to do that.
I would recommend that you create a second PostgreSQL cluster and put the database there. That does not incur a big overhead as far as system resources are concerned, and it will solve your problem.


Full outer join with a list in oracle I have a table test:

id    number
name  varchar2(20)
ddate varchar2(20)

I want to select all row from test on specepic date and full outter join with a list of values. For example let's say this is the data:

SQL> select * from test;

id     name     ddate
---    ----     ------
1      a        20-02-18
2      b        21-02-18
3      c        21-02-18
4      d        22-02-18

I want this result:

SQL> --of course this doesn't work.
SQL> select *
from test full outter join ('b','e') list on test.name=list.name
where ddate='20-02-18';

id     name     ddate
---    ----     ------
null   e        null
2      b        21-02-18
3      c        21-02-18

Not something I would recommend doing, but:

select test.id, nvl(test.name, list.column_value) as name, test.ddate
from
  test
  full outer join table(sys.odcivarchar2list('b', 'e')) list
on test.name = list.column_value
where nvl(ddate, date'2018-02-21') = date'2018-02-21'
order by id nulls first
;

        ID NAME       DDATE
---------- ---------- --------------------
           e
         2 b          21-FEB-18
         3 c          21-FEB-18


Performance: where before or after join I am doing a query like this:

select
   *
from
   table1 join table2 on table1.fieldjoin = table2.fieldjoin
where
   table1.field = 'y'

I would ask is this query equivalent in terms of performance to this:

select
    *
from
  (select * from table1 where field = 'y') as t1 join table2
  on table1.fieldjoin = table2.fieldjoin

That is by explicting doing a where inside the joined table

A SQL query is a definition of what data to return. There is a component of the database called the optimizer which determines how to get that data - which indexes to use, if any, in which sequence, which join algorithm to use etc. The output of that component is a query plan, the equivalent of a procedural program in the DBMS's internal execution language. The optimizer is free to choose any plan it likes as long as it is guaranteed to produce the output required by the SQL.
Sometimes queries which are logically equivalent but use different syntax will produce quite different query plans. Sometimes the optimizer recognizes the equivalence and produces the same plan for both statements. It can be difficult to predict which without good understanding of the working of the optimizer.
However, the optimizer does make available to us these plans. In PostgreSQL they can be retrieved using EXPLAIN. If the plans for the two queries are the same there will be no performance difference between them. Different plans will have different performance, though it may be slight.
There are a great many factors which go into constructing a plan other than the SQL. So a change in table cardinality (e.g. as time passes, or between production and development environments), or a different version of Postgres may produce a different plans for the same SQL statement.


Add timestamp to field automatically in PostgreSQL I created a small database in PostgreSQL to manage the data for my thesis. I just started with Postgres, so this is probably a newbie question. I added a column in several tables, that is supposed to hold the last update of a certain field. One of these tables looks like this (The column relevant here would be lastupdate:

-- Drop table

-- DROP TABLE public.finds;

CREATE TABLE public.finds (
    id bigserial NOT NULL,
    id_investigations int8 NOT NULL,
    quote_short varchar NULL,
    "group" varchar NULL,
    "type" varchar NULL,
    subtype varchar NULL,
    description varchar NULL,
    "number" int4 NULL,
    certainty bool NULL,
    note varchar NULL,
    checked bool NULL,
    lastupdate timestamptz NULL,
    CONSTRAINT finds_check_group CHECK ((("group")::text = ANY ((ARRAY['Keramik'::character varying, 'Waffe'::character varying, 'Werkzeug'::character varying, 'Schmuck'::character varying])::text[]))),
    CONSTRAINT finds_pkey PRIMARY KEY (id),
    CONSTRAINT id_investigations FOREIGN KEY (id_investigations) REFERENCES investigations(id)
);

-- Permissions

ALTER TABLE public.finds OWNER TO postgres;
GRANT ALL ON TABLE public.finds TO postgres;

I would like the lastupdate to take the current timestamp upon edit. Is there a simple way to do this?

You could use a trigger like this:

CREATE FUNCTION last_upd_trig() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   NEW.lastupdate := current_timestamp;
   RETURN NEW;
END;$$;

CREATE TRIGGER last_upd_trigger
   BEFORE INSERT OR UPDATE ON finds
   FOR EACH ROW
   EXECUTE PROCEDURE last_upd_trig();


How to make mongos to be in a replica set? I have a shard cluster: one mongos, three shard replica sets and one config replica set. My application only talks to the mongos for read/write to mongodb. I wonder whether I can make the mongos replica. What I mean by this is that in case of a mongos is down, I need a slave of the mongos to become the primary. There is --replSet parameter on mongod but mongos doesn't have such parameter. How can I make it in mongos?

You cannot have mongos as a replica set, it is just "proxy" between application and cluster. You can have multiple mongos-services. Normally you create mongos service to the same machine where you have the application. So, every application node has its own mongos and your application connects to that localhost mongos. If you mongos is down, your (single) application is down already, probably.

Of course, nothing prevents you to create multiple mongos nodes and put load balancer to the front of them. But I wouldn't recommend that.


MySQL Generated columns on non-deterministic function ON MySQL 5.7, generated columns are not allowed with non-deterministic function.

The query that I need to optimize is executing on a bulky table.

Where clause is causing a full scan on the table, need to optimize that.

The column is DateTime type, we have to use the unix_timestamp function as per application

WHERE UNIX_TIMESTAMP(Last_Used_Time) >  1540874005

I am trying to convert UNIX_TIMESTAMP(Last_Used_Time) into a generated column but throws an error disallowed function used in the expression.

Any workaround to allow MySQL to use generated/virtual column with unix_timestamp function.

Don't convert the column's value, convert your criterium:

WHERE Last_Used_Time > from_unixtime(1540874005)


Purge mysql and it's configuration completely from MAC. I, by mistake messed up with mysql's only user and the situation is I can't create new user or  give more privileges to existing one. Access denied is the error. Only visible db is info Schema So I decided to remove mysql from my MAC and install it again. For removing completely(including config data) I tried this post.

After installing again, I see my configuration is the same. And I have the same user who can't do anything and the only database visible to him is info schema. mysql database is not visible either.

How can I completely remove configuration including user privilege data of mysql from MAC ? Command like sudo rm -rf /var/db/receipts/com.mysql.* I am new on MAC, is there any command like we have for linux sudo apt-get remove --purge mysql-server mysql-client mysql-common.

You do not need to purge anything, you just need to follow these instructions to reset the administrator password. If you happened to delete the admin user, just create a new one on the init file:

create user root@localhost;
grant all privileges on *.* to root@localhost;


In PostgreSQL, how do I make it so that either column A or column B must be non-null? I have a table with an "e-mail address" and "P2PMail address" column. The user is required to either enter an e-mail or P2PMail address.
If I set both to NOT NULL, then both must be filled in for a record to be created.
If I allow both to be NULL, then a user would be entirely unreachable.
If I set a UNIQUE CONSTRAINT on both columns, then the combination of them must be unique, but it says nothing about what I'm trying to do... unless I'm mis-thinking here.
What is the correct way to accomplish this? I fear that the answer is something unpleasant such as "triggers" or something. (I was never comfortable using those.)

You need a table-level check constraint:
alter table <name>
  add constraint either_email
    check (email is not null or p2pmail is not null);

If you're only allowed to enter one, but not both:
alter table <name>
  add constraint either_email
    check (email is null <> p2pmail is null);

The second form is possibly a little confusing at first glance: what it does is compare both columns' null status -- they aren't allowed to be the same.
The constraint can also be created simultaneously with the table:
create table <name> (
  ... columns ...
  constraint either_email check (email is not null or p2pmail is not null)
);


How do I group these results? I have this query to calculate the Idle Time of a vehicle.

The Business Rule for Idle Time is - Ignition = 1 AND Speed = 0

So I have came up with the following Query.

DECLARE
      @startDate DATETIME = N'2018-09-08',
      @endDate DATETIME = N'2018-09-10 23:59:59.997';

Select
  iAssetId,
  fSpeedKPH,
  dtUtcDateTime,
  eIgnition,
  IdleSeconds = (CASE WHEN eIgnition = 1 AND fSpeedKPH = 0 THEN DateDiffSeconds ELSE 0 END)

FROM (

    Select
        iAssetId,
        fSpeedKPH,
        dtUTCDateTime,
        eIgnition,
        DateDiffSeconds = DATEDIFF(SECOND, dtUTCDateTime, LEAD(dtUTCDateTime, 1)
            OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime))
    FROM VehicleMonitoringLog

    WHERE
        dtUTCDateTime BETWEEN @startDate AND @endDate
        AND iAssetId = 2
) A

ORDER BY
    iAssetId, dtUTCDateTime

SQL FIDDLE - Link

Now I need to GROUP the Idle Times into Single Times. So the result set should look like this:

------------------------------------------------------------------------
iAssetId   |  Start Idle Time  | End Idle Time | Idle Duration (Seconds)
------------------------------------------------------------------------

How do I achieve this?

EDIT
Expected Result

iAssetId  | Start Idle Time      | End Idle Time        | Idle Duration (seconds)
-------------------------------------------------------------------
2         | 08-Sep-2018 00:04:51 | 08-Sep-2018 00:36:51 |  1986
2         | 08-Sep-2018 00:58:28 | 08-Sep-2018 03:50:29 |  10432

I think this gives you what you want.

My solution uses several common table expression and a variety of Window functions.

There are commented out selects at the bottom of each common table expression that you can use to check individual results along the way.

My first step was to identify the start and end idle times.  I used LAG to check for the previous value of fSpeedKPH and eIgnition ordered by dtUtcDateTime.  If the current values equal the previous values, no change occurred, else a change occurred.  A new column called IsChanged is added to the results.

 ;WITH _BaseData
AS (
    SELECT iAssetId
        ,fSpeedKPH
        ,dtUtcDateTime
        ,eIgnition
        ,IdleSeconds = (
            CASE
                WHEN eIgnition = 1
                    AND fSpeedKPH = 0
                    THEN DateDiffSeconds
                ELSE 0
                END
            )
    FROM VehicleMonitoringLog
    )
--select * from _BaseData
    ,_BaseDataAndIsChanged  --cte to add IsChanged column to results
AS (
    SELECT *
        ,CASE
            WHEN (
                    lag(fSpeedKPH, 1, '') OVER (
                        ORDER BY dtUtcDateTime
                        )
                    ) = fSpeedKPH
                AND (
                    lag(eIgnition, 1, '') OVER (
                        ORDER BY dtUtcDateTime
                        )
                    ) = eIgnition
                THEN 0
            ELSE 1
            END ischange
    FROM _BaseData
    )
    select * from _BaseDataAndIsChanged

My next step was to identify a grouping that I could use later to sum the IdleSeconds for each group.

;WITH _BaseData
AS (
    SELECT iAssetId
        ,fSpeedKPH
        ,dtUtcDateTime
        ,eIgnition
        ,IdleSeconds = (
            CASE
                WHEN eIgnition = 1
                    AND fSpeedKPH = 0
                    THEN DateDiffSeconds
                ELSE 0
                END
            )
    FROM VehicleMonitoringLog
    )
--select * from _BaseData
    ,_BaseDataAndIsChanged  --cte to add IsChanged column to results
AS (
    SELECT *
        ,CASE
            WHEN (
                    lag(fSpeedKPH, 1, '') OVER (
                        ORDER BY dtUtcDateTime
                        )
                    ) = fSpeedKPH
                AND (
                    lag(eIgnition, 1, '') OVER (
                        ORDER BY dtUtcDateTime
                        )
                    ) = eIgnition
                THEN 0
            ELSE 1
            END ischange
    FROM _BaseData
    )
--  select * from _BaseDataAndIsChanged
    ,_BaseDataAndGroups     --cte to add groupno column to results
AS (
    SELECT t.*
        ,(
            SELECT sum(ischange)
            FROM _BaseDataAndIsChanged
            WHERE dtUTCDateTime <= t.dtUTCDateTime
            ) groupno
    FROM _BaseDataAndIsChanged t
    )
select * from _BaseDataAndGroups

The last step is to sum IdleSeconds by groups and find the IdleStartTime and IdleEndTime This is also the final query.

;WITH _BaseData
AS (
    SELECT iAssetId
        ,fSpeedKPH
        ,dtUtcDateTime
        ,eIgnition
        ,IdleSeconds = (
            CASE
                WHEN eIgnition = 1
                    AND fSpeedKPH = 0
                    THEN DateDiffSeconds
                ELSE 0
                END
            )
    FROM VehicleMonitoringLog
    )
    --select * from _BaseData
    ,_BaseDataAndIsChanged --cte to add IsChanged column to results
AS (
    SELECT *
        ,CASE
            WHEN (
                    lag(fSpeedKPH, 1, '') OVER (
                        ORDER BY dtUtcDateTime
                        )
                    ) = fSpeedKPH
                AND (
                    lag(eIgnition, 1, '') OVER (
                        ORDER BY dtUtcDateTime
                        )
                    ) = eIgnition
                THEN 0
            ELSE 1
            END ischange
    FROM _BaseData
    )
    --  select * from _BaseDataAndIsChanged
    ,_BaseDataAndGroups --cte to add groupno column to results
AS (
    SELECT t.*
        ,(
            SELECT sum(ischange)
            FROM _BaseDataAndIsChanged
            WHERE dtUTCDateTime <= t.dtUTCDateTime
            ) groupno
    FROM _BaseDataAndIsChanged t
    )
    --select * from _BaseDataAndGroups
    ,BaseDataAndSums --cte to add SumIdleSeconds to results
AS (
    SELECT *
        ,sum(IdleSeconds) OVER (PARTITION BY groupno) AS IdleDurationSeconds
        ,FIRST_VALUE(dtUtcDateTime) OVER (
            PARTITION BY groupno ORDER BY dtUtcDateTime
            ) AS StartIdleTime
        ,LAST_VALUE(dtUtcDateTime) OVER (
            PARTITION BY groupno ORDER BY dtUtcDateTime RANGE BETWEEN UNBOUNDED PRECEDING
                    AND UNBOUNDED FOLLOWING
            ) AS EndIdleTime
    FROM _BaseDataAndGroups
    )
SELECT iAssetId
    ,StartIdleTime
    ,EndIdleTime
    ,IdleDurationSeconds
FROM BaseDataAndSums
WHERE ischange = 1
    AND IdleDurationSeconds > 0

| iAssetId | StartIdleTime           | EndIdleTime             | IdleDurationSeconds |
|----------|-------------------------|-------------------------|---------------------|
| 2        | 2018-09-08 00:04:51.000 | 2018-09-08 00:36:51.000 | 1986                |
| 2        | 2018-09-08 00:58:28.000 | 2018-09-08 03:50:29.000 | 10432               |


Is left hash join always better than left outer join? I have a query that runs quite slow (see below). While searching for a way to improve it we found that the query runs about ten times faster if we replaced the LEFT OUTER JOIN with LEFT HASH JOIN
The result seems to be the same. Is it? In what cases wont it return the same result? What difference are there? Is there something that i should look out for when running a LEFT HASH JOIN instead of a LEFT OUTER JOIN?
The [ABC].[ExternalTable] table in the query is a view on a different server that i have added as a external table
SELECT t.foo, t.bar, t.data
FROM [dbo].[Table] as t
LEFT OUTER JOIN [ABC].[ExternalTable] as s ON s.foo = t.foo and s.bar = t.bar and s.data = t.data
WHERE s.foo is null and s.bar IS NULL and s.data IS NULL

Semantically, both queries are the same.  The LOOP versus the HASH simply tells SQL Server which option to use to return results.  If you run the query without LOOP or HASH, SQL Server may pick either of those options, depending on which it thinks will perform best.  Both options return identical results by design.  To put it slightly differently1, the keywords HASH and LOOP in this case are what is known as join hints, one of the three kinds of hints in Transact-SQL.  To confuse you even more, you can also specify a join hint as a query hint, though the effect is not necessarily the same in both cases.  Be aware that providing these kinds of join hints implies a FORCE ORDER hint, which specifies that the join order indicated by the query syntax is preserved during query optimization (see Paul's answer below for further details).
SQL Server uses statistics on each table in the query to make an informed choice about what kind of physical operation to take with for each JOIN in the T-SQL query statement.
In this case, since [ExternalTable] is a view referenced through a linked server, SQL Server probably expects there to be 1 row in the table - i.e. it has no idea how many rows to expect.
You can either add a join hint to your query to force a merge join, or simply copy rows from [ExternalTable] into a local #temp table with a clustered index, then run the query against that.
The full syntax for the hash join would be:
LEFT OUTER HASH JOIN [ABC].[ExternalTable] s ON s.foot = t.foo .....

The version with the HASH in the join statement is allowing SQL Server to choose the join type, and in your particular case it's most likely choosing a LOOP join, which you could force with:
LEFT OUTER LOOP JOIN [ABC].[ExternalTable] s ON s.foot = t.foo .....

I typically would not recommend specifying the join type since most of the time SQL Server is very capable of choosing the most appropriate style of join operator.

1 - thanks Andriy for the wording here.


Why is the schema/structure dump so slow? I'm using Rails 6 with activerecord-oracle_enhanced-adapter adapter for Oracle 11 XE database.
At the beginning of the project I was working with Postgres, but due to customer requirements I had to migrate to Oracle.
My question is very similar to https://stackoverflow.com/questions/592444/rails-rake-dbmigrate-very-slow-on-oracle, but it's from 11 years ago, i'm using the updated version of the adapter.
I know that after all migrations are applied to database then rake db:migrate calls db:schema:dump task to generate schema.rb file from current database schema. I only have one schema, with many tables (around 90)
The answer for the previous question was something like

One way how to debug this issue would be if you would put some debug
messages in oracle_enhanced_adapter.rb file so that you could identify
which method calls are taking so long time.

== 20200820164111 RemoveSemesterFromProject: migrated (0.1347s) ===============
real    13m26,038s
user    0m5,494s
sys 0m0,401s

All migrations finished in an reasonable time, so I think the error occurs in the following action "db: schema: dump" but i don't have oracle_enchaced_adapter.rb neither oracle_enhanced_adapter.rb, so i don't know where to look at.
What can i do for improve this behaviour?
Time that takes when i run db:schema:dump
real    9m44,074s
user    0m3,701s
sys 0m0,327s

and this is the time that takes when i run db:structure:dump
real    21m40,073s
user    0m5,046s
sys 0m0,413s

This may be a consequence of slow queries issued by activerecord-oracle_enhanced-adapter to determine the database structure, eg. issues/2004


PostgreSQL >= 10 does not generate WAL if there are no transactions I noticed that PostgreSQL >= 10 does not generate WAL if there are no transactions.
In previous versions, PostgreSQL generated WAL at the expiration of archive_timout, because it wrote some empty transactions.

I want PostgreSQL to still generate WAL if no one writes to it. Does anyone know how to do this in versions starting from 10?

That must be because of commit 6ef2eba3f:

  Skip checkpoints, archiving on idle systems.

  Some background activity (like checkpoints, archive timeout, standby
  snapshots) is not supposed to happen on an idle system. Unfortunately
  so far it was not easy to determine when a system is idle, which
  defeated some of the attempts to avoid redundant activity on an idle
  system.

If you really need to have regular WAL activity, the cheapest way is to have a cron job that runs SELECT txid_current() regularly.

If you want to monitor replication, it is better to measure the lag in bytes, that is immune to this problem.


Restoring Differential Backups Is it possible to restore a differential backup separately?
The production server runs the following every sunday

DECLARE @Pathname VARCHAR(512) = 'N:\Backup\Dir\DBName_' + (select datename(dw,getdate())) + '.bak'
BACKUP DATABASE [DBName] TO DISK = @Pathname
WITH NOFORMAT, NOINIT,  NAME = 'Full Database Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

every other day I have the following as a differential backup

DECLARE @Pathname NVARCHAR(512)
SET @Pathname = 'N:\Backup\Dir\DBName_' + (select datename(dw,getdate())) + '.bak'
BACKUP DATABASE [DBName] TO  DISK = @Pathname
WITH  DIFFERENTIAL , NOFORMAT, INIT,  NAME = 'Diff Database Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Here is the report server restore job

DECLARE @file VARCHAR(255) = '\\RemoteServer\Dir\\DBName_' + (select DATENAME(dw, DATEADD(day, DATEDIFF(day, 0, getDate()) - 1, 0))) + '.bak'
RESTORE DATABASE [DBName]
FROM DISK = @file
WITH REPLACE

I keep getting the following error

The log or differential backup cannot be restored because no files are ready to rollforward.

Anyone help explain what i'm doing wrong?

UPDATE

I had to use WITH MOVE because the paths are different

In order to restore a differential backup, you need a database that is in "restoring" state. Which is to say that recovery can't have run yet. This is accomplished by adding the "norecovery" clause to your full backup restore. So, it goes a little something like this:

DECLARE @file VARCHAR(255) = '\\RemoteServer\Dir\\DBName_' + (select DATENAME(dw, DATEADD(day, DATEDIFF(day, 0, getDate()) - 1, 0))) + '.bak'
RESTORE DATABASE [DBName]
FROM DISK = @file
WITH REPLACE, NORECOVERY

--now you restore your differential backup


How are block nested loop joins optimised for disk reads? I'm trying to understand how the number of disk page reads for a block nested loop join is calculated (depending on the query) and how optimisations are applied to read in as few pages as possible.
If I have two relations (primary keys in bold)

recording(rid,made, budget)
uses(rid, name, opus, royalty)

We have buffer space enough to store 25 pages.
And an example query
SELECT recording.rid
       recording.budget
       uses.opus
       uses.royalty
FROM recording
     JOIN uses USING (rid)
WHERE recording.made = 1978

My first question is as pages, which contain records, are stored on disk, would the optimiser read in the pages which contain the columns we are selecting rather than reading all the columns in? Can the optimiser read in specific columns of a row in a page or does it have to read the entire page in first and then project out the columns it wants? If it's the former, we can read more rows in per page and hence less disk reads right?
Read in

Pages(uses(rid,name,opus,royalty))

or

Pages(uses(opus,royalty))

My second question is, if we do the block nested loop join

uses BNLJ recording

Since filtering is done on recording.made = 1978 and uses is used as the outer loop, when reading in a page of recording, does the optimiser filter out pages which are not made = 1978 first then do the join? i.e check if the page it has read into memory is a page which contains made = 1978? If so, how does the query planner calculate the number of disk page reads done? Because for every 23 pages of uses that is read in, 1 page of recording is read in so it has to store the filtered page somewhere?

To answer your first question, it depends on if the RDBMS has stored the data as rowstore or columnstore. For a rowstore table (much more common), pages are organized into rows. You would read all columns for a row into memory. For a columnstore table, pages are organized into columns. You could just read only the columns relevant to the query into memory.

To answer your second question, why must "recording" be the outer table? What if the "uses" table has zero rows? Wouldn't it be more efficient to pick that as the outer table? Putting that aside, it's a very general question. You haven't listed a specific RDBMS platform. If there are no sufficient indexes the "recording" table then I would expect the optimizer to generally filter out pages using the filter on the "made" column before performing the join. I can't think of an advantage in doing it another way.

Why does the query planner need to calculate the number of disk page reads done? I don't follow that part.


Get statistics of open connection to linked servers How can I get information about connections from my MS SQL 2008 server to linked servers (actually it's an Oracle db)?

Things like number of currently open connections, and history of opened and closed connections, if these informations are stored somewhere. Who used that connection (login name, application, process id, anything)?

Background: there is a web app, and an Oracle linked server, and the Oracle dba says that our MS Sql server is spamming hundred of connections to their server. Currently our app is only accessible for a few users, and only doing simple selects from a view... So I can't really imagine that caused hundreds of connections.

Also what could cause excessive amounts of open connection form the MS Sql db to the Oracle db? Or what could keep the connection open for too long time? Can Sql Server Management Studio go wild, and open unnecessary connections?

Edit:

One thing to add: We are using OpenQuery to query the Oracle db, so the Oracle db does the heavy lifting (selecting data from a pretty big view). The documentation says nothing about having to explicitly close the connection, is there any known issue regarding this?

SQL is usually pretty good about closing linked server connections. BUT, I've seen SQL server 'hang' linked server queries of certain types if all of the result set isn't fully fetched and/or closed properly by the client. IOW, SQL doesn't think that the app has finished retrieving the data, so it doesn't close the connection to the other server. For whatever reason, the offending app didn't report any errors or query time outs (from it's perspective), but we did have to recycle the IIS application every night or it would eventually eat up all of the RAM on the IIS server.

I would start by running my usual "find any connection that is waiting" query:

SELECT
@@servername 'ServerInstance',es.session_id,er.blocking_session_id
,es.status
,es.login_name,DB_NAME(er.database_id) as database_name
,es.host_name
,es.program_name,er.command
,es.reads,es.writes
,es.cpu_time
,er.wait_type,er.wait_time,er.last_wait_type
,er.wait_resource
,CASE es.transaction_isolation_level WHEN 0 THEN 'Unspecified'
    WHEN 1 THEN 'ReadUncommitted'
    WHEN 2 THEN 'ReadCommitted'
    WHEN 3 THEN 'Repeatable'
    WHEN 4 THEN 'Serializable'
    WHEN 5 THEN 'Snapshot'
    END AS transaction_isolation_level
,last_request_start_time
,last_request_end_time
,OBJECT_NAME(st.objectid, er.database_id) as object_name
,SUBSTRING(st.text, er.statement_start_offset / 2,
    (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), st.text)) * 2
        ELSE er.statement_end_offset END - er.statement_start_offset) / 2) AS query_text
,ph.query_plan
FROM sys.dm_exec_connections ec
  LEFT OUTER JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id
  LEFT OUTER JOIN sys.dm_exec_requests er ON ec.connection_id = er.connection_id
  OUTER APPLY sys.dm_exec_sql_text(sql_handle) st
  OUTER APPLY sys.dm_exec_query_plan(plan_handle) ph
WHERE ec.session_id <> @@SPID
-- AND es.status = 'running'
AND (
es.status != 'Sleeping'
OR wait_time > 0
OR es.session_id in (select blocking_session_id from sys.dm_exec_requests where  blocking_session_id != 0)
)
ORDER BY es.session_id

I'd look for any sort of wait type like "OLEDB" (which should indicate linked server activity, and possibly other things) and try to tie those back to code that accesses the oracle server. I'd also pay attention to the last request start and end times.


SQL Server 2008 R2 lack of space? I have upgraded from SQL Server 2005 to SQL Server 2008 R2 because my database was 4G and needed extra space.
I though SQL Server 2008 R2 has a limit of 10Gigs hence I would have 6 Gigs free, however I only got 1Gig free...

How is this possible?

I have upgraded from SQL Server 2003 to SQL Server 2008 R2 because my database was 4G and needed extra space.

There is no SQL Server 2003 .. I believe you meant SQL Server 2005.

  I though SQL Server 2008 R2 has a limit of 10Gigs hence I would have 6 Gigs free, however I only got 1Gig free...

Space Available : Displays the amount of available space in the database in megabytes.

SQL server 2008 R2 Express edition has a 10GB limit. SQL Server 2008 R2 Express is still limited to 1 CPU and 1 GB or RAM.

What you are seeing 1GB free is the free space in the database. When you fill up that left 1GB and need more, SQL Server will kick in autogrow algorithm that will grow your database by the amount that you have specified (provided you have autogrowth enabled). Also, have your database autogrowth set to a sensible value instead of default 1MB for data and 10% for log (ignore the values in the below image since its one of my crap database).

Also, enable instant initialization (only applicable to sql server data files).


What does duration measurement in sql server actually measure? What precisely does the query duration measure when conducting a profiler trace?

I have a stored procedure which performs data access (SELECT), and it is taking an average of around 30 seconds to return results to the application.  But when I run a trace on it I get an average duration of around 8 seconds, max duration 12 seconds, average CPU 5.5 seconds.

What could cause it to take so much longer to return the result set?  I am not accumulating large NETWORKIO waits.  The result set is only around 270 rows of simple text data, around 50 columns total.

Duration represents the total time from start to finish the request by SQL Server, which represents time to send over network, cpu time, waits, and execution. As Aaron mentioned once the client has received the data SQL Server has finished it's timing, and the client is then processing. This should not be part of the duration.


Saving every mysql user login attempts Is there a way to save the username and the IP from which the user tries to login to a MySQL server??

Enabling general-log seems to log everything but that seems to write too much details taking more space and also there is a possibility of saving password details too hence it would be helpful if there are any other options available.

If you're concerned about failed logins, that implies that your server is accessible from the Internet.  It shouldn't be.

There is, however, a way to get the information without resorting to the general log.

According to the manual you can trigger logging these failed connections to the error log with the system variable log_warnings:

  If the value is greater than 1, aborted connections are written to the
  error log, and access-denied errors for new connection attempts are
  written.

This is a dynamic variable, but changing it at run-time does not appear to trigger the logging behavior.  Add this to your my.cnf in the [mysqld] section:

log-warnings = 2

Restart the server daemon.

You should then start to see failed logins in your MySQL error log.

I don't know what you mean by "saving password details."  The password is sent from the client to the server as...

SHA1( password ) XOR SHA1( "20-bytes random data from server" <concat> SHA1( SHA1( password ) ) )

...so you're not going to find the client's attempted password getting logged anywhere.  The server doesn't know what password the client used.  It only knows whether it used the correct one, by doing its own calculation of the hash the client would have returned if the client had known the right password.


How do you drop a corrupted INNODB database in MariaDB? If you try to access a table in a database of type INNODB and MariaDB tells you it isn't here, you have a corrupted database.

(I am posting this question because all of the existing questions have been "closed" by various moderators and NONE of them have a real answer.  They discuss the complexities of INNODB, and ultimately that you have to rebuild the entire database server through a torturous process.)

This may not work for everyone, but it did work for us on several occasions:

Stop the database (service mysql stop)
edit mysql.cnf so that under [mysqld] section it has the:

[mysqld]
innodb_force_recovery = 1

Start the database (service mysql start) and in the logfile
you will see it force loading the bad database/table
log into the
database server as root:  mysql -u root
run the command "show databases[ENTER]" to get a list of database
run he command "drop [dbname][ENTER]" where [thedb] is the database you need to drop. It will drop it but will complain that it cannot delete the directory ./[thedb].
Open a separate shell and go into /var/lib/mysql/[thedb]/ and remove the file(s) there.
Stop the database (service mysql stop)
Turn off the REPAIR mode by commenting out the line in
mysql.cnf

[mysqld]
#innodb_force_recovery = 1

restart the database with the command:

service mysql start[ENTER]

Voila.

Now recreate the database and restore from your backup.

Thanks,
David


Intermittent only_full_group_by errors in AWS Aurora MySQL cluster db We recently upgraded from MySQL 5.6 to 5.7 and are running into errors related to only_full_group_by sql mode. We are only seeing the problem intermittently. Our database lives in an AWS Aurora MySQL cluster which has a single instance. We originally included the only_full_group_by sql mode in the cluster parameter group setting, but began running into these errors and decided the number of queries which required updating, are outside the scope of this project.
Looking for some insight into why the errors are intermittent and the best course of action to resolve the errors completely.

If only_full_group_by is enabled
All Columns of a SELECT must be in the GROUP BY or use a aggregation function
so simply a is in the GROUP BY and b uses an aggregation function
SELECT
 a, MIN(b)
FROM
 mytable
GROUP BY a

So you have now to check all queries with GROUP BY and "correct" them.
see also the manual
Try all queries, if they give the correct answer, that will cost some time.


Percona pt-table-checksum output is in infinite loop "Waiting for the --replicate table to replicate" We have following Multi master replication scenario. We are using Mariadb 10.1.34 and percona 3.0.11

Master:  Name: db-master1 (192.168.14.5)
Slave:   Name: db-slave2 (192.168.15.5) Replica Connection name:
master1 (192.168.14.5)

SHOW SLAVE 'master1' STATUS\G on slave server is working fine.

On execution of following pt-table-checksum command on DB Master (db-master1)

pt-table-checksum --replicate=percona.checksums --ignore-databases mysql,Quotient h=localhost,u=checksum_user,p=checksum_password --recursion-method=dsn=D=percona,t=dsns  --no-check-binlog-format
Checking if all tables can be checksummed ...
Starting checksum ...
Replica db-slave2 is stopped.  Waiting.
Replica db-slave2 is stopped.  Waiting.

On Master1-db, the percona.dsns table have entry for db-slave2

The Above Waiting information is continue in infinite loop.

Look forward any support

On Master DB Server

(i) For safe side, I made copy of original /bin/pt-table-checksum

cp /bin/pt-table-checksum /bin/pt-table-checksumorg

(ii) Open the /bin/pt-table-checksum file

vi /bin/pt-table-checksum

(iii) Go to line number 8590
 Press esc, type 8590 , press shift g

(iv) replace the line
   my @lagged_slaves = map { {cxn=>$_, lag=>undef} } @$slaves;
by
    my @lagged_slaves = ();

the program immediately works and returns the expected results.


Executing Child packages without using master package I have many Fact and Dimension packages which are executed using vb script . Today one of my seniors proposed that these packages should be executed individually instead of creating a master package to execute the child pkgs using execute package task .His reasoning was based on the fact that execute package task will create several threads which will be performance intensive when compared to individually executing the packages.

Leaving aside sharing of config file and other issue how can individually executing it be more performant that creating master package for all the child packages?

By the way the child packages are executed out of process which is configured in the Execute Package Task

I use the following as the guideline:

If you are simply using many packages to structure your solution in a more modular fashion, executing in-process is probably the way to go because you don't have the overhead of launching more processes. I'm happy to pass on that advice here.

Quoted from: http://consultingblogs.emc.com/jamiethomson/archive/2005/05/16/1414.aspx


Should I use null or a separate table? I could have a nullable field or I could do basic normalisation, having another table which may or may not join to a row in this table.
My instinct was to use the second method, as this is the "right" (normalised) way of handling this. However, thinking about this, it can have drawbacks.

The whole entity is not easily visible in its table (you have to - first find and then - jump to its linking tables)
you now have to remember to join the table(s)
It could lead to loads and loads of tables (I don't know if this is an issue for performance)

What do you usually do? It it worth creating a new table just to have an optional property for an entity?

Like all things, the answer is "it depends".
More tables <> bad if the tables are necessary for proper normalization.  Splitting nullable columns off is something I do often, but not always.  What factors into my decision are things like:

How often will the column be NULL? If it's rarely populated, may make sense to have it as an optional column, implemented as its own table.  This acts like a filtered index any time you might need to locate certain records.
What is the context of the optional column(s)?  If it's information you won't have until a later event occurs (shipping/payment info, etc), then logically it may make more sense for you to implement it separately from the parent table.
How will the data be accessed/how wide is the parent table?  I often deal with summary data from vendors that results in very wide tables with a lot of null values.  I usually will split each column into its own table in order to speed up ad hoc queries that only need a small subset of the data.