Charindex, Getting specific data from a string Hello I am very new to charindex and have tried to create a query that would extract my needed information but am running into an error.  I searched for similar problems on stackexchange as well as a few other sites and it seems I am still unable to fully grasp the concept to get what I need.

My biggest issue is the data that I have in the column is not in any set pattern.  I found a stackexchange question that answered this but my setup is a bit different.  Here are a few examples from column descr and table facility:

ID | Descr |
21 | Playhouse, Virginia Series, 98 Stage, PSDD, House|
35 | Playhouse, Virginia Series, 111 Stage, Inconel|
53 | Playhouse, FX Series, 125 Stage, House F31|
76 | FX Series, 134 Stage, F31, Onconel|

A bit more information.  There should always be a comma before the number I am looking for.  I have found one case out of thousands that this isn't the case, but to make this easier we will assume it always has a leading comma and space.  The number I am looking for can be either two or three digits.  It is also always followed by a space and the word stage.

Here is what I attemped but received and error:
select

with cterecords(descr, position) as
(
    select
        descr, charindex(',', descr) position
        from facility
)
    select
        substring(descr, position +1,
        charindex('stage', descr,position) - position -1)
    from cterecords;

I am receiving this error:
Msg 537, Level 16, State 3, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

But if I get through the error, I am not sure if I have the query written to get exactly what I want.

Here is SQL Fiddle with test data: SQL Fiddle Example Data

I use charIndex and patIndex to resolve it.

CASE WHEN PatIndex('%, [0-9][0-9]% Stage%',Descr) > 0
    AND LEN(SUBSTRING(Descr,1,CharIndex(' Stage', Descr)-1))>=3
    THEN RIGHT(SUBSTRING(Descr,1,CharIndex(' Stage', Descr)-1),3)
    ELSE NULL END
    AS NewNo,
CASE WHEN PatIndex('%, [0-9][0-9]% Stage%',Descr) > 0
    AND LEN(SUBSTRING(Descr,1,CharIndex(' Stage', Descr)-1))>=3
    THEN SUBSTRING(Descr, PatIndex('%, [0-9][0-9]% Stage%',Descr)+2,3)
    ELSE NULL END
    AS NewNo2

With the output:

NewNo NewNo2
----- ------
 98   98
111   111
125   125
134   134

Some explanation on it:
I used to CharIndex(' Stage', Descr)to find the position when starts ' Stage'.
Then I use SUBSTRING(Descr,1,CharIndex(' Stage', Descr)-1) to cut the text so that your number will be in the right part. (like this :Playhouse, Virginia Series, 98).Then you can use different technique (right,another charIndex,reverse) to obtain the Number.


How I can copy a collection from a Mongo Cluster to another Mongo Cluster? I have a collection with more than 1 TB size and wanted to move it another MongoDB cluster

PS: Both clusters are sharded over 5 shards and they are on-prem infra.

As per MongoDB documentation here through the db.cloneCollection(from, collection, query) it's Copies data directly between MongoDB instances.

use users
db.cloneCollection('mongodb.example.net:27017', 'profiles',
                    { 'active' : true } )

This operation copies the profiles collection from the users database on the server at mongodb.example.net into the users database on the local server. The operation only copies documents that satisfy the query { 'active' : true }.

For further your ref here , here , here and here


VS 2012 SSIS Data Flow Designer Source Assistant Error: [SSIS.Pipeline]: Component ", clsid {4CAC ... Could not be created I am creating my first SSIS package to support the E of our ETL from an external Oracle database to our internal SQL Server 2012 databse.
Using Visual Studio 2012 and I have installed the Microsoft Oracle Connection Manager by Attunity 2.0
I had this working, then renamed and deleted some files, and tried to build it again when I ran into this problem.
When running Source Assistant, I receive the Visual Studio Error:

The component could not be added to the Data Flow task.  Could not initialize the component.  There is a potential problem in the ProvideComponentProperties method.

There is an "Additional Information" section that references:

Error at TERM [SSIS.Pipeline]: Component ", clsid {4CAC6073-BCA7-430E-BD29-68A9F0012C6D}" could not be created and returned error code 0x80070005 "Access is denied.", Make sure that the component is registered correctly.
Error at TERM [ [3]]: The component is missing, not registered, not upgradeable, or missing required interfaces.  The contact information for this copmonent is "".

Note: "TERM" is the name of the Data Flow task (named after the table I want to copy with SSIS.
This clearly looks like registry damage to me, so I had uninstalled the Attunity copmnent and re-installed it; and I ran a repair on Visual Studio 2012 (in that order); rebooted, etc.
No luck so far.
Anyone ever seen this before and/or have any ideas how to get back to a working SSIS development?
I am working on a development box, my Oracle Connection tests fine, and I can also connect fine to my SQL Server 2012 instance as well.

Repairing and/or Re-installing Microsoft Connection Manager for Oracle by Attunity, VS 2012, and SSDT 2012 followed by a reboot enabled me to finally see the Oracle Source and Oracle Destination SSIS components in the SSIS Toolbox window under the Common category.

While using Source Assistant component still causes the same error, dragging over an Oracle Source component succeeds.  Configuring the component to set the Connection Manager, Data Access mode ("Table" in my case) and "Name of the Table" work great now.

Clicking on the Preview button proved it's working by showing me actual data from my Oracle Source database.


Database restoring taking too long I have backuped sql database from my server and tried restore on my machine. Restoring window disappeared by saying successfully restored. In the database list my database is not accessible.

myDatabase(Restoring...)

This is how it looks like. When I try to access it gives this window. username/passwords were different on each machine.
From googling I found from microsoft msdn: sp_change_users_login (Transact-SQL)
What I tried is:

USE myDatabase;
GO
EXEC sp_change_users_login 'Auto_Fix', 'user', NULL, '12';
GO

In the messages panel:

  Msg 927, Level 14, State 2, Line 2
  Database 'myDatabase' cannot be opened. It is in the middle of a restore.
  The number of orphaned users fixed by updating users was 0.
  The number of orphaned users fixed by adding new logins and then updating users was 0.

How can I fix it?
UPDATE
I restored the database on another machine it worked properly.

When you restore (either via SQL or GUI), you have to be sure that you don't leave the database expecting more restores (of differentials or logs).

This is done in SQL using WITH RECOVERY

In the GUI, it is the "Recovery state" panel (point 12):

  Leave the database ready to use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY)


Which isolation level do I need? I've read a lot of articles about Isolation levels, but I'm confused which one to choose for my MySQL 5.6 database.
This is my situation:

Let's say I have two group of users:

Group A (executes selects)
Group B (executes inserts and updates)

Every individual in group A is executing huge select query that takes 5 minutes and in the meanwhile a couple members from group B are inserting and updating records.
Which database isolation do I need to achieve:

The select queries that are executed by people from Group A should
NOT cause a lock to eachother. So simultaneously reads should be
allowed, they must be able to access the same data at the same time.
The select queries that are executed by people from Group A should
NOT cause a lock to people from Group B who are inserting and
updating. So simultaneously writes and reads should be allowed and
people from Group B shouldn't be waiting 5 minutes until the individual select
queries from Group A are finished.
The inserted and updated records should be included in the select
query results from Group A.

A slow SELECT that can be sped up by adding a composite index or reformulating the query will mostly avoid getting blocked.

Ditto for inefficient INSERTs and other queries.

What is your setting for tx_isolation?  Changing that to READ UNCOMMITTED for the long SELECT may help.

Is this a Data Warehouse application and the SELECT is a 'report'?  If so, consider building and maintaining a Summary table.  This makes reports much faster.

Consider Master-Slave setup, with the long SELECTs going to the Slave.

You are using InnoDB, not MyISAM, correct?

If you want more specific advice, please provide (at least) SHOW CREATE TABLE and the SELECT.


Filling not applicable fields with N/A rather than leaving them empty or Null I have a database of few million records, many of the fields (call them Ec1 to EcN) have either Null or empty string in them. When I looked at them more closely, I found that for many records, several of this Ec fields are not applicable. So, there should not be any value there and hence nothing is wrong as instance issue.

However, I found several records that should have had value in these Ec fields which don't. So, here are some instance issues in data entry. So, my question is what would be the best approach to distinguish N/A fields from those user failed to enter and avoid further confusion?

One idea is to fill those Ec fields with N/A value and leave the rest with value. In this setting we won't have any NULL or '' field in the entire database. But, it might use more space (in MB scale).

What is cons and pros of using N/A?

Thanks

Let me change the direction of the question...

If you are making an 'array' of Ec's as columns, that is poor schema design.  Instead, you should have another table something like this:

CREATE TABLE Ec (
    id ... -- id in the main table
    ec ... -- one of several Ec values
    PRIMARY KEY (id, ec)
) ENGINE=InnoDB;

There would be any number of rows in this table, depending on the number of Ec values you need.  There is no need for NULL or N/A.


Best high availability solution to avoid maintenance downtime? We currently only have one production SQL Server 2008 server.  Anytime we need to perform maintenance or install patches, it causes downtime as the server reboots.  In addition, if the server ever fails, that will obviously cause a great deal of downtime.

What is the best solution to avoid this maintenance downtime?  We are looking for a solution that will automatically switch to a failover server so we can apply rolling updates.

Have you considered a Failover Cluster Instance?  It'll give you the high availability of the entire instance.  And provided you have more than one node in the FCI, you would be able to failover while patching the other server.

There are quite a few details regarding failover clusters, but please see this MSDN reference to get an overview and other how-to topics.

  We currently only have one production SQL Server 2008 server.

You will need a cluster (WSFC), and you will benefit from multiple servers/nodes to ensure maximum uptime.

  We are looking for a solution that will automatically switch to a failover server so we can apply rolling updates.

You can have automatic failover of the FCI to a different node in the cluster.  When are you patching a SQL Server instance, you will basically want to shuffle current and possible owners and apply the patch to the passive node.  This will give you a solution of minimized downtime for updates.

The real question you need to answer is, at what level are you looking for redundancy and high availability?  The instance?  A database?  Database objects?  By your question, I'm guessing at the instance level, which is why I recommend an FCI.  But if a database, you could consider database mirroring.


Oracle server requirements calculation Is there a standard methodology for determining how much CPU, memory and disk space is needed to support an Oracle database with 500 million rows of sensor data (and counting) ? The system in question is a SCADA database gathering sensor data from hundreds of field devices on a daily basis. The same database is used for reporting and analysis (a watershed and wastewater management system for a major municipality).

You can roughly calculate current disk space requirements by using guidelines from the Oracle documentation, once you have defined your schema, including the necessary indexes and materialized views, if any. Do not forget to add space for backups and archived logs.

As for the CPU and memory requirements, they would mostly depend on the exact characteristics of your workload, given the schema defined earlier. In this day and age you should be able to spin up a virtual server with one of the cloud providers and run your load tests (you do have a load test suite, right?) against it much faster than trying to find some magical generic formula on the internets.


Record of schema related changes - Oracle Is it possible to get the all changes that is made to schema as in what privileges was first assigned to the schema on creation, creation date, if later modified with different privilege what was that priv, when the schema was dropped, basically this would be needed for audit purpose at the end of the year where they would be asking for all the info for the whole year for select schemas.

Is is possible to fetch this data from the database? I have enabled standard DB auditing for sys operations to be TRUE. Version 12.1.0.2 and 11gR2.

That's exactly the kind of thing that oracle AUDIT is created for. The basic view is DBA_AUDIT_TRAIL, though there are several others.  By defalt, Oracle has some auditing already enabled, but you may want to review and modify. Also you want to use the dbms_audit_mgmt package to move the audit tables out of the SYSTEM tablespace into their own dedicated tablespaces, as well as to do normal housekeeping.  Details are in Part VI of the Security Guide.


MS SQL - User access to schema not data I look after some infrastructure that includes some MS SQL Servers (SQL Server 2017). I am by no means a SQL expert and have been asked the following:
"Create a user that can only access the schema for database X, Y and Z and not the data"
I am able to create a user that can only access X, Y and Z DB and not A, B, C etc. But I cannot work out have to restrict it to viewing the schema only. Can anyone provide any assistance?
Most of the DBs in question use the "dbo" schema only but some have multiple
Thanks

You can grant "View Definition" permissions at the schema level in most SQL databases. I would create a new role in the database, for example schemareader, and assign permission like this:
GRANT SELECT, VIEW DEFINITION ON SCHEMA::[dbo] TO schemareader;


Making decision between Count() & Top queries in terms of performance We are using SQL Server and this is a performance related question.

We have a relatively big table and intend to perform an action in case of having more than 1 record with the same ID. Consider the simple queries below:

select top 2 [TenantID] from [dbo].[tblTenant] where TenantID = 1

vs

select COUNT ([TenantID]) from [dbo].[tblTenant] where TenantID = 1

I would guess in the first query, SQL Server stops scanning the entire table after finding the first possible two TenantIDs. But in the second query, operation is done over all the records in the table.

Is that a correct assumption? I mean, could we say the first query is better in terms of performance?

I would guess in the first query, SQL Server stops scanning the entire table after finding the first possible two TenantIDs.

Correct, it will stop after finding two matching rows. But it doesn't necessarily scan the whole table. If there is a suitable index, on (TenantID), it will only need an index seek and a sequential scan on the index - and stop at the second row, whether it is a match or not. The index guarantees that the rows with same TenantID will be one after the other in the index structure.

  But in the second query, operation is done over all the records in the table.

Yes again. With the same note, if there is an index, it only has to do an index seek and a scan of the index, until it finds the first row with a different (than 1) TeanantID. If there is no index, it will have to do a full scan on the table.

  Is that a correct assumption? I mean, could we say the first query is better in terms of performance?

Yes, that is a correct assumption. The first query will be (slightly or more) efficient than the second. The difference will probably be negligible though in most cases. Reading 2, 3 or 10 values from an index will not make any noticeable difference. If however, there are many rows with the same TenantID value, say tens of thousands or millions, then you'll start noticing.

To summarize, if you need to find whether there are N or more rows that match a condition, I suggest you always use the TOP (N) method, and then count:

...
WHERE (SELECT COUNT(*) FROM
        (select top (2) * from [dbo].[tblTenant] where TenantID = 1) AS t
      ) = 2 ;


Is there a way to use a SQL Server 2000 database in SSIS 2012? I just got my first chance to install SQL Server 2012 on a test machine.  I also installed all of the management and Visual Studios components.  I created a copy of one of my SSIS 2008 packages and converted it to 2012.  The conversion worked jsut fine, but I'm now getting an error stating that connections to SQL Server 2000 and earlier versions are no longer supported.

Is there a workaround for this?  Is there another way to create a connection to a 2000 database?  We're not currently in a place to move this database off of 2000.

The connection is currently set up as an OLE DB connection for a couple of data sources.

Apparently, the SQL Server Native Client 11.0 (the one that ships with SQL Server 2012) does not support SQL Server 2000. All you have to do is change the data source to use a .Net Provider (SqlClient Data Provider) instead of the Native OLE DB provider. The .Net Provider probably doesn't perform quite as well as the Native OLE DB but it was definitely a lot less work that having to build a bunch of new ETLs to stage the data and a lot less risky too.


I accidentally removed SELECT permission from my user on SQL Server I accidentally removed SELECT and other permissions from my user on SQL Server 2014 via Properties.

I checked all the permissions except the Guest in the Owned Schemas list and also in Membership - including db_denydatareader and db_denydatawriter. That's what made me this mistake. There are some permissions (db_denydatareader and db_denydatawriter) that if you check them, you would remove your own permission without being able to regrant them. I checked all of them.

  Cannot alter the role 'sp_denydatareader', because it does not exist or you do not have permission.

Unfortunately I can't use SELECT and other function on my queries or selecting the Properties again in order to uncheck the permissions.

What should I do? I'm not the admin on the box. Is there any way to restore it without asking the sysadmin?

Checking all the items in Membership means you are adding yourself to all the roles defined in the database. That includes the sp_denydatareader role, which is probably the main reason you cannot open the Properties box any more.

If you have been given the permission to alter roles previously, it is likely that you have not removed it by your erroneous actions. So, you could just try to remove yourself from the sp_denydatareader role by executing this command in a query window:

ALTER ROLE sp_denydatareader DROP MEMBER your_user_name;

If that succeeds, you can then try opening the Properties box again and undoing your mistakes.


SSIS convert flat file cell to HH:MM:SS I see similar questions, but most have to do with datetime or fairly different requirements.

I have a CSV report that has a time column stored as HH:MM[:SS] (if over 1 day, it adds :00 for seconds... annoyingly, and I don't have control over how the report itself runs.) I think, in the derived column task, we can drop seconds.

Examples:

23:54 = 23H 54M
27:35:00 = 27H 35M 00S

My questions are:

If the CSV loads the column as text, what expression do I need for the derived column?
What should I set the data type as in the SQL database?

Pertinent info: SQL Server 2014 and SSDT for VS 2013

Thank you,
Wes

2) Time/Date datatypes are often problematic choices for storing durations. Instead use an integer or decimal for whatever resolution is appropriate (seconds or milliseconds are popular, but minutes may work for you in this case).

1) In this instance, the simplest option may be: (DT_I4)TOKEN(Column,":",1) * 60 + (DT_I4)TOKEN(Column,":",2).

Other methods:

If you will always have two characters each for hours and minutes, you can use something like (DT_I4)SUBSTRING( [Column], 1, 2 ) * 60 + (DT_I4)SUBSTRING( [Column], 4, 2 ).
If hours can be 1 or more characters, but minutes is always two: (DT_I4)SUBSTRING(Column,1,FINDSTRING(Column,":",1) - 1) * 60 + (DT_I4)SUBSTRING(Column,FINDSTRING(Column,":",1) + 1,2)


Is Audit plugin available for Community edition? MySQL version 5.7.26 MySQL Community Server (GPL)

OS : CentOS 7.6

I was trying to install audit plugin for MySQL database. But, I encountered the following error

mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';
ERROR 1142 (42000): INSERT command denied to user 'root2'@'10.80.16.236' for table 'plugin'
mysql>

I would like to know if there is an Audit plugin available for Community edition.
If so, how can I install it?

I don't see the audit plugin in the plugin directory

# pwd
/usr/lib64/mysql/plugin
# ls -l
total 59556
-rwxr-xr-x. 1 root root   103728 Apr 13 14:36 adt_null.so
-rwxr-xr-x. 1 root root   356976 Apr 13 14:36 authentication_ldap_sasl_client.so
-rwxr-xr-x. 1 root root    43552 Apr 13 14:36 auth_socket.so
-rwxr-xr-x. 1 root root   940312 Apr 13 14:36 connection_control.so
drwxr-xr-x. 2 root root     4096 May  3 04:03 debug
-rwxr-xr-x. 1 root root 21640312 Apr 13 14:36 group_replication.so
-rwxr-xr-x. 1 root root   483520 Apr 13 14:36 ha_example.so
-rwxr-xr-x. 1 root root   968432 Apr 13 14:36 innodb_engine.so
-rwxr-xr-x. 1 root root   957088 Apr 13 14:36 keyring_file.so
-rwxr-xr-x. 1 root root   460064 Apr 13 14:36 keyring_udf.so
-rwxr-xr-x. 1 root root  1184680 Apr 13 14:36 libmemcached.so
-rwxr-xr-x. 1 root root  8973984 Apr 13 14:36 libpluginmecab.so
-rwxr-xr-x. 1 root root    21424 Apr 13 14:36 locking_service.so
-rwxr-xr-x. 1 root root    53928 Apr 13 14:36 mypluglib.so
-rwxr-xr-x. 1 root root    41088 Apr 13 14:36 mysql_no_login.so
-rwxr-xr-x. 1 root root 22243648 Apr 13 14:37 mysqlx.so
-rwxr-xr-x. 1 root root    49504 Apr 13 14:36 rewrite_example.so
-rwxr-xr-x. 1 root root   590936 Apr 13 14:36 rewriter.so
-rwxr-xr-x. 1 root root   933904 Apr 13 14:36 semisync_master.so
-rwxr-xr-x. 1 root root   159928 Apr 13 14:36 semisync_slave.so
-rwxr-xr-x. 1 root root   209520 Apr 13 14:36 validate_password.so
-rwxr-xr-x. 1 root root   506320 Apr 13 14:36 version_token.so
#

Based on your error, you just need to grant the insert privilege to the 'root2' user:

GRANT INSERT ON *.* TO 'root2'@'10.80.16.236'

(you'll need to modify this based on your actual 'root2' user)

In terms of whether there is a plugin available, technically the mysql audit log plugin is only for the enterprise edition, and not the community edition.

That said you can usually use the one supplied by Percona or Mariadb with MySQL (there may be others).

I use the Percona one with my 5.5 databases, and the Mariadb one with my 5.7 databases, as I couldn't get the Percona version to work with 5.7.

You install them in exactly the same way. Download them, and place the file in your plugins directory first,then

Percona
    INSTALL PLUGIN audit_log SONAME 'audit_log.so';

Mariadb
    INSTALL PLUGIN audit_log SONAME 'server_audit.so;


How to create a user with a particular user_id? Is it possible to specify the user_id of a user while creating it?

SQL> create user user1 identified by user1;

User created.

SQL> select user_id, username from all_users where username='USER1';

   USER_ID USERNAME
---------- ------------------------------
        72 USER1

SQL>

Instead of 72, can it be a number that I specify?

Not in a supported fashion, no.

Why would you care what the USER_ID of a new user was?  That would seem to raise a pile of red flags in my mind.


Update multiple rows with different date for each row I want to update all records in my database table with a fixed value for pwDays and an increasing value in pWDate.

My current statement:

update [POSUSER] set passDays = '60', passChangeDate = '2017-01-01 00:00:00'

But I do not want all users to have to reset their passwords on the same day. So I would like to increment the passChangeDate by 1 day for every 10 to 20 users, is there a "simplistic" way to do this in a single SQL statement?
(All users must have a new passChangedDate, but no more than 20 users must have the same date.)

The table has a primary key field that is a integer value. (USERID)

Note I want to only increase the date after every +/-10% users.

Since you have a primary key userid, you can do it with a query like :

update p
set passDays = '60',
    passChangeDate = dateadd(dd, offset, '2017-01-01 00:00:00')
from [POSUSER] p
join (
    select  userid,
            row_number() over(order by userid) / 10 as offset
    from [POSUSER]
    ) t on p.userid = t.userid

This query will work the same with any type of primary key (numeric or not) or any set of unique columns. But if there is no way to build a unique identifier of a row, the query won't work and you'll have to make your update another way.


Why would sp_helprolemember show different MemberSID for same member? Have two databases on same SQL Server instance.

If I run EXEC sp_helprolemember I get different MemberSID values for dbo on each. (the 2nd half of the SID is different, the start is the same)

db #1

DbRole = db_owner, MemberName = dbo,
MemberSID = 0x0105000000000005150000003D85B1B0F17DF0097CD12B

db #2

DbRole = db_owner, MemberName = dbo,
MemberSID = 0x0105000000000005150000008641B613459B05E16185E7

(note 2nd half of SID mangled by removing last 10 chars in case its sensitive)

What does the second half of the SID signify - in this case (dbo being a 'legacy style sql server login')

Would this SID difference account for any permission differences between the two databases?

dbo is a user in a DB. When a user is created (whatever is name would be), it gets its own unique SID. If you later create a second DB and add a new user with a similar name (or not), it will get another unique SID. Both user on DB1 and DB2 have nothing in common and SID will be different.

  This was based on the unedited question about sp_helpsrvrolemember...

Look at the view sys.server_principals which is mentionned in the text below from the technet link:

When a SQL Server login is created, it is assigned an ID and a SID. These are visible in the sys.server_principals catalog view as principal_id and SID. The ID (principal_id) identifies the login as a securable within the server. It is assigned by SQL Server when the login is created. When a login is deleted, its ID number is recycled. The SID identifies the security context of the login and is unique within the server instance. The source of the SID depends on how the login is created. If the login is created from a Windows user or group, it is given the Windows SID of the source principal; the Windows SID is unique within the domain. If the SQL Server login is created from a certificate or asymmetric key, it is assigned a SID derived from the SHA-1 hash of the public key. If the login is created as a legacy-style SQL Server login that requires a password, the server will generate a SID.

https://technet.microsoft.com/en-us/library/ms403629%28v=sql.105%29.aspx


Starting a Postgres slave that is in hotstandy - stuck after start I have a postgres 9.1 hot-standby that has been down for a few days and having problems starting back up. When I try to restart the server I see the following:

-sh-4.1$ /usr/local/pgsql/bin/pg_ctl start -D /usr/local/pgsql/data
server starting
-sh-4.1$ LOG:  database system was shut down in recovery at 2013-02-13 05:11:29 UTC
LOG:  entering standby mode
cp: cannot stat `/usr/local/pgsql/wals/000000010000003B000000BA': No such file or directory
LOG:  redo starts at 3B/BA0022E0
LOG:  record with zero length at 3B/BA820590
cp: cannot stat `/usr/local/pgsql/wals/000000010000003B000000BA': No such file or directory
LOG:  streaming replication successfully connected to primary

What does this mean? Why isn't the standby getting the data it needs from the master and making itself available? Any suggestions on how to diagnose and resolve? Thanks

Although this message could look like an error:

  cp: cannot stat `/usr/local/pgsql/wals/000000010000003B000000BA': No
  such file or directory

it's actually harmless, it's just the way of your restore_command to check for a new WAL file that's not necessarily there yet.
Presumably this command looks like:

cp /usr/local/pgsql/wals/%f "%p"

Should you want to get rid of that message, you could provide a more sophisticated version of the command to silently check for the existence of the file before attempting to copy it.

In the rest of the log output, there is nothing that looks wrong. If it's a hot standby, you should be able to connect to it with psql.


How to reduce mysql memory used I'm using MySQL 5.1 and want to upgrade to latest version 5.6.17 (from Windows Installer)
But when I installed, it used too much memory, about 420Mb although 5.1 vesion run only 8Mb memory. The setup type I choose:

Setup Type: Server Only

Config Type: Development Machine

I don't want it uses too much memory, how can I do, or what is lastest version of MySQL that use minimum memory like 5.1
I change config type to another type but It still uses than 400Mb ram

I have an article on this here: http://www.tocker.ca/configuring-mysql-to-use-minimal-memory.html:

  The MySQL defaults have to balance performance with what is considered reasonable for what may be a development system with other applications needing to run alongside MySQL. In many cases, this will mean 4-8GB, but on virtual machines (or in my case with 7 copies of mysqld running), there is a lot less available.

  Obligatory warning: If you are running these settings on a machine with 1GB+ RAM, you should expect worse performance when compared to the defaults.

Setting                     Default      Minimum
innodb_buffer_pool_size     128M         5M
innodb_log_buffer_size      1M           256K
query_cache_size            1M           0
max_connections             151          1 (although 10 might be more reasonable)
key_buffer_size             8388608      8
thread_cache_size           (autosized)  0
host_cache_size             (autosized)  0
innodb_ft_cache_size        8000000      1600000
innodb_ft_total_cache_size  640000000    32000000
thread_stack    262144      131072
sort_buffer_size            262144       32K
read_buffer_size            131072       8200
read_rnd_buffer_size        262144       8200
max_heap_table_size         16777216     16K
tmp_table_size  16777216    1K
bulk_insert_buffer_size     8388608      0
join_buffer_size            262144       128
net_buffer_length           16384        1K
innodb_sort_buffer_size     1M           64K
binlog_cache_size           32K          4K
binlog_stmt_cache_size      32K          4K

  To summarize these changes:

# /etc/my.cnf:
innodb_buffer_pool_size=5M
innodb_log_buffer_size=256K
query_cache_size=0
max_connections=10
key_buffer_size=8
thread_cache_size=0
host_cache_size=0
innodb_ft_cache_size=1600000
innodb_ft_total_cache_size=32000000

# per thread or per operation settings
thread_stack=131072
sort_buffer_size=32K
read_buffer_size=8200
read_rnd_buffer_size=8200
max_heap_table_size=16K
tmp_table_size=1K
bulk_insert_buffer_size=0
join_buffer_size=128
net_buffer_length=1K
innodb_sort_buffer_size=64K

#settings that relate to the binary log (if enabled)
binlog_cache_size=4K
binlog_stmt_cache_size=4K


Concept of Cardinalilty in Oracle As per wikipedia: Cardinality refers to uniquess of data:

  In SQL (Structured Query Language), the term cardinality refers to the
  uniqueness of data values contained in a particular column (attribute)
  of a database table. The lower the cardinality, the more duplicated
  elements in a column. Thus, a column with the lowest possible
  cardinality would have the same value for every row. SQL databases use
  cardinality to help determine the optimal query plan for a given
  query.

BUT, Oracle documentation explains it as:
"The cardinality is the estimated number of rows that will be returned by each operation"

So, is this term has different meaning in SQL and in Oracle ? or  i am missing something here ?

The concept of cardinality does not change whether it is used in reference to the Oracle database or some other SQL database. Cardinality is the size of a set of some elements. From the mathematics point of view a set contains only distinct elements. When you reference values of a certain column as a set its cardinality is the number of unique values. When you reference rows returned by an operation they all comprise the result set (each has a unique row ID, to put it simply) and therefore the result set cardinality is the number of rows returned.


Mysql not closing connections I have a Java application that configured with a connection pool. I have ensured that the application terminates the connections properly.

However when I run this command on mysql command line -

show status like '%onn%';

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Aborted_connects         | 2     |
| Connections              | 72    |
| Max_used_connections     | 10    |
| Ssl_client_connects      | 0     |
| Ssl_connect_renegotiates | 0     |
| Ssl_finished_connects    | 0     |
| Threads_connected        | 2     |
+--------------------------+-------+

Why does mysql keep the connections open when they have been terminated from the client?

The contents of my.cnf file are (comments and unimportant sections omitted) -

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld]
user        = mysql
pid-file            = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
lower_case_table_names = 1
skip-external-locking

key_buffer      = 16M
max_allowed_packet  = 16M
thread_stack    = 192K
thread_cache_size   = 8

myisam-recover      = BACKUP

query_cache_limit   = 1M
query_cache_size    = 16M

expire_logs_days    = 10
max_binlog_size     = 100M

According to MySQL documentation the Connections status variable shows "The number of connection attempts (successful or not) to the MySQL server." This means that it does not represent current open connections, but all connection attempts since the server has been started.

The variable showing number of current open connections, again according to MySQ docs, is Threads_connected.


When to use sort_in_tempdb when rebuilding indexes? We are debating whether to use the SORT_IN_TEMPDB option for our DW tables. My understanding is that there are more writes when using this option, although they are more sequential. We have a SAN (which has has been notoriously slow at times), so in our case we want to limit the number of writes as much as possible. I believe tempdb is on a separate LUN (set of disks).

We have plenty of disk space in our data file and on our tempdb file. In this case, would we benefit from using SORT_IN_TEMPDB?

One thing that struck me was this comment on this Answer

  When rebuilding an index you would need twice the space of the index +
  20% for the sorting. So in general to rebuild every index in your db
  you only need 120% of your biggest index in your DB. If you use
  SORT_IN_TEMPDB, you only win 20%, you still need an aditional 100% in
  your data file. Further more, using sort in tempdb increases your IO
  load drastically, since instead of Writing the index one time to the
  datafile, you now write it one time to the tempdb and then write it to
  the data file. So that is not always ideal.

We definitely don't want to increase our IO load with our slow/possibly misconfigured SAN.

What would be the best way to test this? By simply rebuilding the table with and without the option and log the times?

Edit: We have 8 tempdb files, each 15GB. We do have TF 1117/1118 flags set and IFI is enabled. We currently do a mixture of rebuilding with the sort_in_tempdb option and without it.

Thanks!

SQL Server 2012 Enterprise

SORT_IN_TEMPDB means that SQL server will use tempdb to allocate the temporary space as opposed to allocating space in the user database whose index is being rebuild. This means you will need less free space in your user database during an index rebuild operation and more free space in tempdb.

It gives you better advantage when tempdb is on a different set of disks (LUNs) from the user database.

From SORT_IN_TEMPDB Option - BOL :

  If the SORT_IN_TEMPDB option is set to ON and tempdb is on a separate set of disks from the destination filegroup, during the first phase, the reads of the data pages occur on a different disk from the writes to the sort work area in tempdb. This means the disk reads of the data keys generally continue more serially across the disk, and the writes to the tempdb disk also are generally serial, as do the writes to build the final index. Even if other users are using the database and accessing separate disk addresses, the overall pattern of reads and writes are more efficient when SORT_IN_TEMPDB is specified than when it is not.

Make sure you read the disk space requirements when SORT_IN_TEMPDB is ON.

  slow/possibly misconfigured SAN

You know the pain point. Why dont you work with your SAN admin to fix it ? Misconfigured and or slow SAN will cause all sort of problems like slowness.

Some important points to note :

Use MAXDOP=1 when doing an online index rebuild - else it will cause increase fragmentation.
Intelligently reorg/rebuild your indexs based on the fragmentation level as well as page fragments.
Read SQLFool's experience with Creating a 60 GB Index
Make sure you have configured tempdb properly - TF 1117 (GROW ALL FILES IN A FILEGROUP EQUALLY) and 1118 (FULL EXTENTS ONLY) and have more tempdb datafiles.

  What would be the best way to test this?

Yes, you have to test it by analyzing the waitstats when you rebuild the index with and without SORT_IN_TEMPDB. Measure run time as well and when doing in PROD, make sure you do it during a maintenance window or less server activity. Also check your read/write data and log latency.

I am not sure you have Instant file initialization, but it will benefit when restoring, during autogrowth of data files and when creating a new database (just mentioning for completeness).


How to make use of Batch Mode with an UNPIVOT (a loop join)? I have a query of the following form:

SELECT ...
FROM ColumnstoreTable cs
CROSS APPLY (
    SELECT *
    FROM (VALUES
        ('A', cs.DataA)
        , ('B', cs.DataB)
        , ('C', cs.DataC)
    ) x(Col0, Col1)
) someValues

This takes every row from a Columnstore-backed subquery (ColumnstoreTable) and multiplies those rows. This is essentially an UNPIVOT. The real query is larger than this. This part of the query feeds into other processing.

The problem here is that this CROSS APPLY is implemented as a loop join which is a reasonable choice. Unfortunately, loop joins do not support batch mode.

This part of the query is very performance critical and I suspect that running it in batch mode could be very beneficial to performance.

How can I rewrite this query so that I do not transition out of batch mode?

I did try using a temporary table instead of VALUES, but that did not change the fact that there is no equality join condition to hash join on.

One approach might be to use a #temp table for the values and also introduce a dummy equijoin column to allow for a hash join. For example:

-- Create a #temp table with a dummy column to match the hash join
-- and the actual column you want
CREATE TABLE #values (dummy INT NOT NULL, Col0 CHAR(1) NOT NULL)
INSERT INTO #values (dummy, Col0)
VALUES (0, 'A'),
        (0, 'B'),
        (0, 'C')
GO

-- A similar query, but with a dummy equijoin condition to allow for a hash join
SELECT v.Col0,
    CASE v.Col0
        WHEN 'A' THEN cs.DataA
        WHEN 'B' THEN cs.DataB
        WHEN 'C' THEN cs.DataC
        END AS Col1
FROM ColumnstoreTable cs
JOIN #values v
    -- Join your dummy column to any numeric column on the columnstore,
    -- multiplying that column by 0 to ensure a match to all #values
    ON v.dummy = cs.DataA * 0

Performance and query plan

This approach yields a query plan like the following, and the hash match is performed in batch mode:

If I replace the SELECT statement with a SUM of the CASE statement in order to avoid having to stream all those rows to the console and then run the query on a real 100MM row columnstore table I have lying around, I see fairly good performance to generate the requisite 300MM rows:

CPU time = 33803 ms,  elapsed time = 4363 ms.

And the actual plan shows good parallelization of the hash join.

Notes about hash join parallelization when all rows have same value

The performance of this query depends heavily on each thread on the probe side of the join having access to the full hash table (as opposed to a hash partitioned version, which would map all rows to a single thread given that there is only one distinct value for the dummy column).

Fortunately, this is true in this case (as we can see by the lack of a Parallelism operator on the probe side) and should reliably be true because batch mode builds a single hash table that is shared across threads. Therefore, each thread can take their rows from the Columnstore Index Scan and match them to that shared hash table. In SQL Server 2012, this functionality was much less predictable because a spill caused the operator to restart in Row mode, both losing the benefit of batch mode and also requiring a Repartition Streams operator on the probe side of the join which would cause thread skew in this case. Allowing spills to remain in batch mode is a major improvement in SQL Server 2014.

To my knowledge, row mode does not have this shared hash table capability. However, in some cases, typically with an estimate of fewer than 100 rows on the build side, SQL Server will create a separate copy of the hash table for each thread (identifiable by the Distribute Streams leading into the hash join). This can be very powerful, but is much less reliable than batch mode since it depends on your cardinality estimates and SQL Server is trying to evaluate the benefits versus the cost of building a full copy of the hash table for each thread.

UNION ALL: a simpler alternative

Paul White pointed out that another, and potentially simpler, option would be to use UNION ALL to combine the rows for each value. This is likely your best bet assuming that it's easy for you to build up this SQL dynamically. For example:

SELECT 'A' AS Col0, c.DataA AS Col1
FROM ColumnstoreTable c
UNION ALL
SELECT 'B' AS Col0, c.DataB AS Col1
FROM ColumnstoreTable c
UNION ALL
SELECT 'C' AS Col0, c.DataC AS Col1
FROM ColumnstoreTable c

This also yields a plan that is able to utilize batch mode and provides even better performance than the original answer. (Although in both cases the performance is fast enough that any selecting or writing the data to a table quickly becomes the bottlneck.) The UNION ALL approach also avoids playing games like multiplying by 0. Sometimes it's best to think simple!

CPU time = 8673 ms,  elapsed time = 4270 ms.


Primary key added automatially to seconday indexes on MySQL 5.5? I have a table (purchaseorders) that has the following colums:

PurchaseOrderID, PurchaseDate, CreatorID, TotalValue

and a primary key consisting of the field PurchaseOrderID

As I often have queries that do something like this:

select * from purchaseorders where creatorID=100 order by purchaseorderID asc;

I thought of creating a secondary index with the fields creatorID, purchaseorderID

Recently I came across a PPT file on Percona's homepage (Indexing Best Practices for MySQL 5.6) which status (on page 15 in case you have the file too) that
"Primary key is implicitly appended to all indexes), key(A) is really key(A,ID) internally.

Questions:

Anyone know if that also applies for MySQL 5.5?

Do I understand right that I should therefore not (or don't need to) specify purchaseorderID when creating the secondary index, as MySQL adds it anyways.

Yes, this applies to MySQL 5.5 too, but only for InnoDB tables.


Column suitable for natural key? I have read many articles now about natural vs surrogate primary keys, and came to the conclusion there is no single best practice.

I have a table that will have around 2000 definite unique values, each value will range from 5 characters to 40 in length.

This seems like a partial choice as a natural key, although the values which are 40 characters in length may cause some performance and storage issues when they are referenced elsewhere.

As the total maximum rows in this table is fixed as 2000 and 35% of these rows contain value length of 25-40 characters(65% have length 6-25), shall I go with a natural key here?

With your experience, what would you do here?

As a general rule every table ought to have a natural key because it usually isn't a good idea to duplicate information in a table. Only add a surrogate key if and when you find there is a reason to do so.


Database Technology for Large Scale Logging I am stuck looking for a suitable database technology to suit our application. I am stuck between going cloud-based or keeping it local.
Scenario:
We have a service that receives a log stream for thousands of devices. We have to store every log in order to do queries and analytics on that data. We do about 4-6 GB a day.
We currently have a local machine running SQL Server Enterprise with daily partitions. But the database has reached 1TB now and queries have become very slow and when running big queries it starts affecting the service that is continuously inserting in to the DB.
Local machine specs:

2 * 2TB 7200RPM Drive RAID 1
16GB RAM
i7

So we are stuck between spending a fair amount of money on upgrading the local machine vs moving it to the cloud. However, when looking at cloud solutions, they seem to get very expensive when running at such levels of performance.
I am also open to moving away from SQL Server. I quite like the idea of Azure table storage and how it does easy partitioning. Is there possibly a similar technology that we can run on a local machine?
So my question is: what are my options here?

Considering the volumes you are discussing moving it to the cloud isn't going to be magic fix.

"There is no such thing as a free lunch"

A computer still has to process that volume, and that comes at a cost. If you are asking a 3rd party vendor to provide that theyre not just covering costs, but trying to make a profit too.

Changing to a different db platform can also have a lot of hidden costs. Even though licensing might be "free", there can be additional costs in maintenance, integration and compatibility. Even learning a new system is an overhead.

The spec you mention is not a big machine by server standards. I would be confident saying thats undersized for the volumes you describe.

What performance tuning have you done already? What are the bottlenecks? RAM/CPU/IO?

How much faster does it need to be? How fast is fast enough? Do you need a 20% improvement? 50% 100% You might easily get that with more RAM and some SSD's.

If you cant add more resources, is there anyway you can make the server do less work?

Archiving: Could you move old data off to a separate db. Or even better could you aggregate the data and purge the raw records?


32-bit Enterprise SQL 2005 on 64 bit Windows 2008 Server Standard. Memory configuration Working on a project we need to first stabilize and then upgrade.

We have a 32bit Enterprise edition SQL Server 2005 installed on a 64 bit Windows 2008 standard Server.

The server has 64GB RAM installed, however in taskmgr there are only 32GB available. I believe this is a Standard Windows Server limitation, just want to confirm.

On other side, we have checked the SQL Server memory counters on perfmon, and the value we get for target and total memory is about 3.500.000, so I guess SQL server is using about 3,5GB??

I want to ask about the configurations we need to make in order to make SQL Server use as much memory as possible, hopefully 28 GB, leaving 4GB for OS, as we have no other services in this machine.

This is what I'm planning so far, but as it's a production environment, I would ideally try to nail it on one attempt, coz don't want to stop the services many times:

The service account is Local System, but I cant see the "Using locked pages for buffer pool" in the SQL Server logs. Do I need to swtich to another user account to take advantage of the "lock pages in memory" policy? What I can see in the SQL Server log is this: "Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required."
The Max server memory property is set to 10240; I think after enabled lock pages in memory, I will need to increse this to 28000
As this is 64 bit OS, no need to enable AWE. However, in the SQL server log, I can see this message: "Set AWE Enabled to 1 in the configuration parameters to allow use of more memory."

Anything else I need to take into consideration in order to enable more memory for SQL Server??

The server has 64GB RAM installed, however in taskmgr there are only 32GB available. I believe this is a Standard Windows Server limitation, just want to confirm.

You are correct max memory limitation for Windows Server 2008 Standard is 32 GB. This is mentioned in Memory Limits of Windows Server Machines.

  On other side, we have checked the SQL Server memory counters on perfmon, and the value we get for target and total memory is about 3.500.000,

When 32 bit SQL Server is installed on 64 bit machine it is called WOW System. For this the Virtual Address Space limit is 4GB. And in normal and default configuration SQL Server cannot see/utilize more memory than the VAS limit so you are seeing 3.5 GB in Perfmon counters.

  The service account is Local System, but I cant see the "Using locked pages for buffer pool" in the SQL Server logs

Since you are on 32 bit system you should not look for this message instead look for "Address Windowing Extensions is enabled", if you see this then it is enabled. If you have 64 bit system then the message "Using Locked pages for buffer pool" makes sense. Actually this is how AWE works in 32 and 64 bit respectively.

  As this is 64 bit OS, no need to enable AWE.

No, this is true for 64 bit SQL Server not if you have 32 bit SQL Server.

Now for SQL Server to see more memory, you have to enabled AWE so that SQL Server data and index pages can see more memory than 4 GB. Since your windows can see more than 4 GB( In your case 32 GB) AWE would help SQl Server data and index pages accessing larger part of memory. Please note I mentioned data and index pages, apart from this no other memory consumer can see those extra memory.

PS: At the time of writing the answer most of the blogs.msdn links are not opening as they are being migrated to new platform, I will add the links in answer as soon as they start working so that you have some reference to read


Determine the server that sent a query For the distributed queries, is it possible to determine the server that sent a query?

For example, I have stats:

SELECT DB_NAME(st.dbid),
       text,
       request_time,
       query_plan,
       (requested_memory_kb / 1024),
       (granted_memory_kb / 1024),
       query_cost
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS ph
ORDER BY requested_memory_kb DESC

How can I get server that sent each query? What tables should I join or functions to apply?

The DMV sys.dm_exec_sessions has column host_name which is the computer at the client end of the connection.  You can join that to your posted query through the session_id.


Setting up Mirroring in SQL Express Edition I know doing something like mirroring in SQL Server express edition is not possible, but can we do real-time db backup and restore and setup some automated task which acts like mirroring and keep them in sync.

Express Edition doesn't include database mirroring, but you can do something similar with log shipping. Log shipping is a technique, not a feature - it's just a matter of frequently taking backups on your primary server, and then restoring them on the secondary.

The normal way of doing this requires SQL Server Agent to run the backup & restore jobs - but again, that's not something Express Edition includes. You'll need some kind of job scheduler.

Kathrine Villyard has a post on doing this with PowerShell.

When it comes to failing over, the basic concepts are the same as failing over Standard Edition's log shipping. They'll be manual steps on Express Edition - but they're also manual on the big brother, too, so don't feel bad.


Write Locks in Mongo DB I read somewhere that MongoDB holds db level write lock, does this mean that when someone updates any collection others are not allowed to read other collection or how it works. Also when someone writes anything to the db how MongoDB acquires write lock and what is its granularity?

Your interpretation matches the "How granular are locks in MongoDB?" part in MongoDB FAQ:

  How granular are locks in MongoDB?

  Beginning with version 2.2, MongoDB implements locks on a per-database
  basis for most read and write operations. Some global operations,
  typically short lived operations involving multiple databases, still
  require a global “instance” wide lock. Before 2.2, there is only one
  “global” lock per mongod instance.

  For example, if you have six databases and one takes a write lock, the
  other five are still available for read and write.

As for the way the locking works:

  What type of locking does MongoDB use?

  MongoDB uses a readers-writer
  [ed: also known as “multi-reader” or “shared exclusive”] lock that allows concurrent
  reads access to a database but gives exclusive access to a single write
  operation.

  When a read lock exists, many read operations may use this lock.
  However, when a write lock exists, a single write operation holds the
  lock exclusively, and no other read or write operations may share the
  lock.

  Locks are “writer greedy,” which means writes have preference over
  reads. When both a read and write are waiting for a lock, MongoDB
  grants the lock to the write.


T-SQL Fulltext search by first word How can I perform "starts with" logic with full text search?

For example there is a Contains logic :

WHERE contains(Note,' "*word*" ')

I guess I need search by first word in the string, but how can I do it?

WHERE CONTAINS (Note, ' "word*" ' );

Assuming "word" is the word that you are actually searching for.


Performance problems with TOP and user defined functions in views I have problem with performance of queries on simple query on view.

To keep things simple, assume my view is defined as follows:

CREATE VIEW [mon].[ViewDevicesWithGroups]
AS
SELECT     Id, Name, Deleted, mon.SlowFunction(Id) AS Groups,
FROM         mon.Devices

And query I'm trying to optimize looks like this:

SELECT TOP 10
Id218_0_, Groups, Name218_0_, DeletedD6_218_0_ FROM (
    SELECT this_.Id as Id218_0_, this_.Name as Name218_0_, this_.Deleted as Deleted218_0_, Groups, ROW_NUMBER() OVER(ORDER BY this_.Name) as __hibernate_sort_row
    FROM mon.ViewDevicesWithGroups this_
    WHERE (this_.Deleted = 1 and not (1=0))) AS query
WHERE query.__hibernate_sort_row > 100000
ORDER BY query.__hibernate_sort_row

My problem is, SQL server keeps executing this query in REALLY wrong order - SlowFunction is really slow, and should be delayed as long as possible:

As you can see, this query was generated by ORM (NHibernate in this case), and I can't change it directly (otherwise this problem would be trivial to solve by just moving function call to outermost select).

Can performance of my query be improved without changes in application code (maybe I kind of hint on view/function)? Because that query is abstracted deeply inside DAL in my application, and changing it would require A LOT of work.

Why don't you call a stored procedure via NHibernate instead of asking it to query the view?  That would allow you to write your own query and tune as needed....

I don't know NHibernate very well but this blog post looks like a reasonable resource.  In case that link dies here are the basic steps:

Write your stored procedure to select the data in the most efficient manner.
Write a class that represents the data you're selecting (a DTO would be perfect).
Write a NHibernate mapping that tells it how to associate the fields
Specify the name of the stored procedure to use and any parameters when you query the database via NHibernate.

It might be a good idea to check out this article on StackOverflow related to tuning performance when using NHibernate.


Transferting large SQL Databases to Azure (>300MB) I want to transfer a local SQL Database to Microsoft Azure.
There are files stored in the db, so the db is about 300MB in size.

From SQL Server Management Studio I tried the following:

  Tasks->Generate Scripts

I was able to create the .sql script but couldn't load it.

I also tried the

  Tasks->Deploy Database to SQL Azure...

but after about 30 mins, it crashed.

Any ideas?

You should use the Azure migration wizard instead of using SSMS create script.

How to: Use the SQL Azure Migration Wizard

Behind the scenes it uses bcp to transfer your data to Azure.

For the future, SSMS is not meant to handle large .sql files. Even a 1 or 2 MB file will crash SSMS. Instead you should use command line tools like sqlcmd or bcp - depending on your needs.


MySQL deleting lots of rows by id - where in or loop over each row in transaction I have a delete query which is potentially going to delete tens of thousands of rows.

Currently it's taking quite a long time in the more extreme case (over 100000 rows to delete) using this:

DELETE FROM tablename WHERE id IN (?, ?, ?...)

However, I was wondering whether it might be better to create a transaction and use php to loop through each of the deletes.

Something like...

START TRANSACTION;
DELETE FROM tablename WHERE id=?;
DELETE FROM tablename WHERE id=?;
DELETE FROM tablename WHERE id=?;
.....
COMMIT;

I hope this makes sense... I'm not the best at using the correct terminology.

Thanks

Where did you get the list of ids?  If you got them from another table, then do a multi-table DELETE.  This would be best, except that it stills suffers from #1, below.

A single delete with 100K rows will take a long time because that is an awfully large number of things to save for possible ROLLBACK.
Deleting one row at a time is awfully slow if each is a separate transaction (autocommit=ON).
Deleting individual rows in a huge transaction has the same ROLLBACK issue, plus the overhead of doing separate commands (extra parsing, roundtrips, etc).

Here's another thought:  Use PHP, but gather 1000 ids at a time.  That is:

SET autocommit=1;
DELETE FROM tablename WHERE id IN (... 1000 ids ...);
DELETE FROM tablename WHERE id IN (... 1000 ids ...);
...

If convenient, sort the 100K ids before starting this; that will help the "locality" a bit.  This breaking up of the ids softens the negative issues mentioned above.

Not quite the same: Blog on Big Deletes , but it might give you some more insight and ideas.  (It discusses, among other things, the impact on Replication, and a benefit of using PARTITION.)


Postgres won't shut down due to wal archiving I commanded Postgres to shut down using the init.d scripts (Linux) over 18h ago.

I can still see the processes running:

-bash-3.2$ ps -fe | grep postg
postgres  2299  3265  0 16:06 pts/5    00:00:00 ps -fe
postgres  2300  3265  0 16:06 pts/5    00:00:00 grep postg
root      3263 10185  0 May23 pts/5    00:00:00 su - postgres
postgres  3265  3263  0 May23 pts/5    00:00:01 -bash
root      5985 13676  0 May20 pts/3    00:00:00 su - postgres
postgres  5987  5985  0 May20 pts/3    00:00:01 -bash
postgres 14266     1  0 May23 ?        00:06:34 /usr/pgsql-9.1/bin/postmaster -p 5432 -D /var/lib/pgsql/9.1/data
postgres 14268 14266  0 May23 ?        00:01:51 postgres: logger process
postgres 14270 14266  0 May23 ?        00:01:30 postgres: writer process
postgres 14281 14266  0 May23 ?        00:00:09 postgres: archiver process   last was 000000010000028F000000A3
postgres 14282 14266  0 May23 ?        00:03:07 postgres: stats collector process
postgres 14283 14266  0 May23 ?        00:56:49 postgres: wal sender process postgres 10.40.227.238(12032) streaming 28F/A4000650
postgres 14306 14266  9 May28 ?        04:01:55 postgres: opera_man om 10.40.227.146(44745) SELECT

On the standby server (running normally) I see that:

$ ps -fe | grep postg
cluser   20724  7090  0 09:54 pts/0    00:00:00 psql -U postgres report
postgres 20726 21475  0 09:54 ?        00:01:12 postgres: postgres report [local] idle
postgres 21475     1  0 Apr24 ?        00:00:03 /usr/pgsql-9.1/bin/postmaster -p 5432 -D /var/lib/pgsql/9.1/data
postgres 21477 21475  0 Apr24 ?        00:00:01 postgres: logger process
postgres 21478 21475  0 Apr24 ?        05:34:10 postgres: startup process   recovering 000000010000028F000000A4
postgres 21485 21475  0 Apr24 ?        00:07:16 postgres: writer process
postgres 21486 21475  0 Apr24 ?        00:00:18 postgres: stats collector process
postgres 24091 21475  0 May23 ?        00:46:49 postgres: wal receiver process   streaming 28F/A40006E0
cluser   32136 30224  0 16:09 pts/16   00:00:00 grep postg

The log shows 'FATAL:  the database system is shutting down'. What could be the reason of this and how do I get it to run again?

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

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

Yet there is this process:

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

that does not want to quit.

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

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


Query for lowest price for product in same row I have two tables: first for products and second for prices.

I need to calculate and display the minimum price from all 5 prices for one product.
I already have query to select five prices and dont know what to do next:

SELECT
    wp_prices.price_one,
    wp_prices.price_two,
    wp_prices.price_three,
    wp_prices.price_four,
    wp_prices.price_five
FROM wp_products
JOIN wp_prices ON wp_products.id = wp_prices.product_id
WHERE wp_prices.product_id = ' . $current_product_id . '

Of course your data is denormalized, otherwise it would be a simple MIN(price) :-)

If you got luck your DBMS supports a LEAST function:

LEAST(price_one,price_two,price_three,price_four,price_five),

A huge CASE is supported in every DBMS:

CASE
  WHEN price_one <= price_two
   AND price_one <= price_three
   AND price_one <= price_four
   AND price_one <= price_five
  THEN price_one

  WHEN price_two <= price_three
   AND price_two <= price_four
   AND price_two <= price_five
  THEN price_two

  WHEN price_three <= price_four
   AND price_three <= price_five
  THEN price_three

  WHEN price_four <= price_five
  THEN price_four

  ELSE price_five
END

Hopefully there are no NULLs...


MySQL for Administrators course from Oracle: worth it? This is a question probably experienced MySQL admins can answer, but is not really a technology question.

I am looking into MySQL Administraton course from Oracle/MySQL AB.

It seems interesting as it will fill the knowldge gap that I have being a MySQL admin. But is this course worth it. Would be interested to know from some one who has taken the course or peeked at the content. I can find the topics which would be discussed, but they are there in almost any MySQL book any way.

Considering that this course is almost an entry level software developers whole month salary, it is not a cheap buy and does require 5 working days so for a small company it's not small affair.

They have the same course in  CD format but I think that the instructor led course is probably the best.

One can get trained by reading books, blogs, manuals and practicing. But this training does seem like a ramp to quick learning.

I previously taught that class.

It's not the worst one Oracle offers, that would be the Performance Tuning course (which I hear was recently refreshed, so maybe it is better).

It depends a lot on your learning style, but you will never learn as much through a CD or internet based training as you will with having the right instructor in front of you who can answer all your questions, and challenge you.

There's not 5 days worth of material in it, since now on the last day they do the DBA exam.  You may also spend the first half of day 1 being marketed to if your instructor follows the schedule (some just exercise civil disobedience and skip the garbage).

You may also find it disturbing the over-focus on things like stored-procedures.  That's more marketing, because MySQL is trying to make sure everyone knows they have them.


Installing SQL server 2008R2/2012 on windows server 2012 box we are trying to install SQL2k8R2 and SQL server 2012(Standard edition) on the windows server 2012 box (Standard 64 Bit).

For SQL 2k8r2, i used slipstream method so as to bring set up to atleast SP1 for support as compatibility on windows 2k12.

But the problem comes when we are running the set up, i.e. SQL2k8R2 and even SQL2012 , we are getting the below error for missing .Net 3.5

If we try to enable 3.5 manually from roles and features, we get the below erro

I've read on many articles, that we need to have the set up for windows to find the required source files for 3.5 for this to proceed. But before going to the team who actually set up the box, is there something we may be missing here and any action or alternative that can be given a try?

Thanks.

I have used option 2 from this article many times to fix this issue.

Basically insert the windows install disk, enable the .net 3 feature but select the option to 'specify an alternate path' at the end of the wizard. The different path should be D:\sources\sxs (if the disc is in your D drive)


UTL_FILE directory name changes oracle 19c I've scripts where UTL_FILE.FOPEN is used and the parameter passing for directory is an absolute path i.e., /asr/file/path and the corresponding oracle directory name as ASR_ABC but after up-gradation to oracle 19c the parameter is expected to be direcotry name ASR_ABC instead of absolute path /asr/file/path.
If I pass an absolute path I get error as Invalid path.
Do I have to change all the files from absolute path to directory name? or is there any work around which can be done from database to avoid changes in all files?

Starting with 18c, the UTL_FILE_DIR parameter no longer exists, thus the usage of directory paths instead of directory objects in UTL_FILE is no longer possible.
Desupport of UTL_FILE_DIR Initialization Parameter

Starting in Oracle Database 18c, the UTL_FILE_DIR parameter is no
longer supported. Instead, specify the name of a directory object.

Even in earlier releases, the parameter and usage of paths existed only for backward compatibility.


SQL db2 remove specific item from the query Can you please help me out with the below query?
What I am looking for is a code to exclude items with wording '90003' from column COR_ID.
So far I have a code that extracts data with date older than today from column VAL_DT, excluding items with wordings MAN, NOSTRO, SCREV and SCRIN for which query shows also dates newer than today.

All I need is for the below code is to remove '90003' from column COR_ID

WHERE
    VAL_DT < CURRENT DATE AND COR_ID <> '90003'
    OR (
       MVT_DS = 'NOSTRO'
       OR MVT_DS = 'SCREV'
       OR MVT_DS = 'SCRIN'
       OR MVT_DS = 'MAN'
       )

Following WHERE clause shold do the job

WHERE COR_ID <> '90003'
  AND (VAL_DT < CURRENT DATE  OR MVT_DS in ('NOSTRO', 'SCREV', 'SCRIN', 'MAN')  )


Installing Oracle Express Database 18c (XE) On Oracle Cloud Free Tier Fails With ORA-00821: Specified value of sga_target | minimum requirements Scenario

Tried to install Oracle Express Database 18c (XE) on Oracle Cloud Free Tier ( Centos 7 machine ).
Followed steps suggested in : Oracle installation doc
As Oracle Cloud Free Tier machine is limited to 1000MB memory, installation fails with 2 different major issues :
Encountered 2 major issues :

Issue 1 : [SEVERE] Oracle Database 18c Express Edition requires a minimum of 1GB of physical memory (RAM).  This system has 971 MB of RAM and does not meet minimum requirements."
Issue 2 : [WARNING] ORA-00821: Specified value of sga_target 296M is too small, needs to be at least 400M
ORA-01078: failure in processing system parameters

How to Install Oracle Database 18c Express Edition on Oracle Cloud Free Tier (for life) | Centos 7

Credits :
1
2

 STEP 1 - Download Oracle Required Software

oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
oracle-database-xe-18c-1.0-1.x86_64.rpm

1. mkdir Downloads
2. cd /home/opc/Downloads
3. curl -L -O https://download.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-18c-1.0-1.x86_64.rpm
3. curl -L -O https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
4. sudo yum localinstall oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm (Pre-Requistis only)

 STEP 2 - Change RPM To Avoid Issue 1 :

( Issue 1 : This system has 971 MB of RAM and does not meet minimum requirements )

Install & run rpmrebuild (if you don't have it already)

1. sudo yum install rpmrebuild
2. cd /home/opc
3. rpmrebuild -e -p /home/opc/oracle-database-xe-18c-1.0-1.x86_64.rpm
   ( this might take few mins )

Once rpmrebuild is opened :

1. Scroll down ( to bottom ) -> search for this line : [ $PhyMem -lt 1024 ]
2. Change to : [ $PhyMem -lt 102 ]
3. Press Esc -> type : wq!
4. Wait for RPM to be re-build ( it's auto and take few mins ).

Do the rpmrebuild modification :

1. Scroll down ( to bottom ) -> search for this line : [ $PhyMem -lt 1024 ]
2. Change to : [ $PhyMem -lt 102 ]
3. Press Esc -> type : wq!
4. Wait for RPM to be re-build ( it's auto and take few mins ).

Install Database from new RPM  :

1. cd /home/opc/rpmbuild/RPMS/x86_64/
2. sudo yum localinstall oracle-database-xe-18c-1.0-1.x86_64.rpm

 STEP 3 - Change Config File Before Database Configuration To Avoid Issue 2 :

( Issue 2 : sga_target 296M is too small )

Once step 2 completed - Edit File : /etc/init.d/oracle-xe-18c

1. sudo -s
2. cp /etc/sysconfig/oracle-xe-18c.conf /etc/sysconfig/oracle-xe-18c.orig
3. vi /etc/sysconfig/oracle-xe-18c.conf
4. search for "sga_target=" (/sga_target= in vi)
5. change from : MEMORY_CONSTRUCT="-initParams sga_target=${sga}M,pga_aggregate_target=${pga}M"
          To   : MEMORY_CONSTRUCT="-initParams sga_target=500M,pga_aggregate_target=${pga}M"
6. Save file.

 STEP 4 - Run Database Configuration :

( This may take up to 30 mins )

1. /etc/init.d/oracle-xe-18c configure


SQL Server USE ignores IF block I want to shrink the transaction log file of several databases using a maintenance plan.
Those databases are mirrored, therefore the shrink task cannot be executed on the MIRROR databases, but the maintenance plan has to be configured on both servers.
I'm using SQL Server 2012 Standard (SP1) on our development system but the code needs to work on 2008 R2 too.

I tried to use the following script, which handles one database called DR, within an Execute T-SQL Statement Task:

IF EXISTS (SELECT NULL FROM sys.databases WHERE [name] = 'DR' AND [state_desc] = 'ONLINE')
BEGIN
    PRINT 'test';
    USE DR;
    DBCC SHRINKFILE (DR_log, 2048);
END
GO

This works fine on the PRINCIPLE, however it results in the following error message on the MIRROR:

Msg 954, Level 14, State 1, Line 4
The database "DR" cannot be opened. It is acting as a mirror database.

It seems the IF block is ignored by the USE command.
The Print is not executed if USE is commented out. The Shrink Database maintenance task which is available in SSMS provides an option to ignore databases where the state is not online. However, the log file does not reveal how this is accomplished. This Task cannot be used because it also shrinks data files, not only log files.

How does one make sure the USE is only executed if the database is in Online state? Another option I can think of would be the use of dynamic SQL, which I'd like to avoid.

Note: This task will not be executed frequently but rather subsequent to an exceptionally big transaction which caused the log file to grow beyond it's usual size.

Solution: we used dynamic sql to workaround this issue, see also the answer provided by @Aaron Bertrand

This is happening at parse time, and there is little you can do to change the actual behavior. SQL Server does some checking of the statements within the batch, and for all intents and purposes, pretends that your conditional logic does not exist.

For the same reasons, you can't use conditional logic to determine which of two possible #temp tables to create (the most common symptom for this problem). Even though looking at this as a human, you see that only one version of #t could ever be created, SQL Server doesn't see it that way. It will report the error message whether you choose Parse (Ctrl+F5) or Execute (just F5):

IF (1=1)
BEGIN
  CREATE TABLE #t(a INT);
END
ELSE
BEGIN
  CREATE TABLE #t(b INT);
END

Result in either case:

  Msg 2714, Level 16, State 1, Line 7
  There is already an object named '#t' in the database.

The error message makes very little sense when all you've done is parse the batch; obviously #t can't exist yet, but it demonstrates that SQL Server is evaluating the statements in your batch, but ignoring those statements that deal with conditional logic.

To get around this problem, you can use dynamic SQL, which will not be parsed prior to execution:

IF EXISTS (SELECT NULL FROM sys.databases WHERE [name] = 'DR' AND [state_desc] = 'ONLINE')
BEGIN
    EXEC DR.sys.sp_executesql N'DBCC SHRINKFILE (DR_log, 2048);';
END

However, this seems really, really, really wrong to me. You are constantly shrinking your log file? Why? This is such a self-defeating process. Please read this post in its entirety:

Why Does the Transaction Log Keep Growing or Run Out of Space?

Then, size your log file appropriately - ONCE - and make sure you are using the right recovery model so that you're not constantly playing this grow-shrink-grow-shrink-grow-shrink cycle. If the log is too big for the mirror, get a bigger disk. Doesn't seem right to cripple the primary to compensate for the mirror's inadequacies.


How to avoid empty rows in SSIS Excel Destination? Does anyone have a way to avoid empty rows when using SSIS to export to Excel. Here's a simple example of one data flow task:

OLE DB Source:

Data Conversion (to handle the annoying UNICODE / NON-UNICODE deal):

The end result is either of the two below depending on value of "FirstRowHasColumnName" in the Excel Connection Manager. Note, the blank rows.

I tested this in SSIS from a 2008 R2 install and Excel 2010.

Using the Excel 97-2003 output format in the connection manager, I was able to reproduce the case where there was the header name in the first row, then a blank, then the data. But changing the "First row has column names" had a different effect for me: I still got the header row, but no blank.

Using the Excel 2007 format, I wasn't able to suppress the header row either, though in both cases, it didn't output a blank row.

You didn't say if you want the header row or not, or which output format you're using; based on my testing, you're using 97-2003, so all you might have to do is switch to the 2007 format.

Attaching a Data Viewer just before the Excel Destination shows only data rows, so the extra blank is being added by the Destination component. So if the Destination isn't configurable to get rid of the blank, you're out of luck.

Suffice to say, though, since the behaviour of this component seems very buggy and the whole setup process to even get this to work at all was such a pain, I would strongly consider switching to a more stable component like the Flat File Destination to output CSV, which you can open directly in Excel, too.


How are "DEFAULT PRIVILEGES" implemented in Postgresql? If I create a new database cluster (via initdb), then create a new database. How are the "default privileges" and "global default privileges" implemented?

For example, when I run \ddp in psql, it shows no rows. Does this mean  ALTER DEFAULT PRIVILEGES adds/removes rows in a table somewhere?

information_schema is an ANSI-standard set of read-only views. You can easily learn what that views actual query are by reading the code which is here. The real catalog information is stored in System Catalogs.

As documentation says 'There is no ALTER DEFAULT PRIVILEGES statement in the SQL standard.' So there shouldn't be anything related to information_schema.

Your records should be stored in pg_default_acl which stores initial privileges to be assigned to newly created objects.


MySQL Workbench can either Dump or Restore - How Do I Configure To Do Both? I have previously posted this question on Stack Overflow, but have not solved the problem. Hopefully it is not incorrect to post it here in the hope that it will be seen by a some different individuals who have encountered the problem and can explain what is happening and have a solution.

Having used MySQL on a previous laptop running Linux Mint 13 I recently installed it on a newer laptop (also running Mint 13). Although I could carry out a Data Dump via MySQL Workbench a Data Import/Restore fails when it encounters a larger BLOb (of ~1.9 MB) with Error 2006 (HY000) : MySQL server has gone away. The same error is reported if I try to add the BLOb via my Python 2.7 app. After talking with a former colleague I edited /etc/mysyl/my.cnf. The section I changed was:-

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp

to:-

    [mysqld]
    user        = mysql
    pid-file    = /var/run/mysqld/mysqld.pid
    socket      = /var/run/mysqld/mysqld.sock
    port        = 3306
    basedir     = /usr
    datadir     = /var/lib/mysql
    tmpdir      = /tmp
    lc-messages-dir = /usr/share/mysql
    explicit_defaults_for_timestamp
    max_allowed_packet = 128M
    net_buffer_length = 32M
    wait_timeout    = 6000

   [mysqldump]
   user            = mysql
   max_allowed_packet = 128M
   net_buffer_length = 32M

After a reboot the Data Import/Restore worked (as did the Python app) but a Data Dump fails with cannot parse version from mysqldump. My former colleague was thrown by this and I am now feeling somewhat confused. Changing /etc/mysql/my.cnf and rebooting every time you want to carry out a different action is not really viable. How should I edit /etc/mysql/my.cnf so that Dumps and Restores work consistently.

Thank you...

there are two common solution for cannot parse version from mysqldump: the first one you need to make sure path for MySQLDUMP is correct you can check it from the workbench by going to Edit->Preferences->Administrator and the second one the environment variable is not correctly configured on the server.


How to disable replication when Distribution db is lost? I am in the situation that the Distribution database in SQL Server 2014 is lost and I want to disable the replication. I have found a solution how to achieve that when data files are present (also asked here). Unfortunately, I do not have data files of Distribution database. Is it possible to disable the replication when Distribution database is completely lost?

Finally, I have found a solution. Problem was that I was calling the stored procedures from query windows of Management Studio (some stored procedures must be called from sqlcmd).

Then, it is enough to follow instructions for TSQL (with @no_checks and @ignore_distributor).


Ascending Key Problem - Leading column branded "Stationary" - SQL Server I've been researching slow running queries in our database, and have concluded that this is a classic Ascending Key Problem.  As new rows are inserted almost constantly, and a given piece of SQL to pull the latest data out of the DB runs every 30 minutes, the first option of updating stats every 30 minutes sounded like it could be a waste of resource.

So, I looked into Trace Flag 2389 which in principle should help, however that requires the Leading column to be branded as Ascending, and when I used Trace Flag 2388 to check the (PK) index stats, I see that the leading column is actually branded as Stationary - as it is for several of the PK indexes on other tables updated at the same time.

There doesn't seem to be much guidance on what results in a branding of Stationary, however I did find KB2952101 that says if less than 90% of inserts were greater than the old maximum value, it would be classed as Stationary.  All our inserts are new submissions, and the leading column is a bigint IDENTITY column, so 100% of inserts should be greater than the maximum previous value.

So my question is why would the column be branded as Stationary, when it is obviously Ascending?

An earlier attempt to resolve this problem for some daily running SQL (which worked really well) resulted in a job being setup to update statistics for this table nightly.  The update doesn't do a FULLSCAN, so could it be that the sampled scan is missing the new rows sometimes, so it's not always showing as ascending?

The only other thing I can think of that might affect this, is that we have an archive job running behind the scenes deleting rows over a certain age. Could this have an affect on the branding?

The server is SQL Server 2012 SP1.

Update: Another day, another stats update - same stationary branding.
There have been 28049 new inserts since the previous stats update.  Each row has a timestamp of when it was inserted, so if I select max(id) from table where timestamp < '20161102' I get 23313455
Similarly, if I do that for when the stats were updated today, I get 23341504.

The difference between these is the 28049 new inserts, so as you can see, all the new inserts were given new ascending keys (as expected), suggesting the leading column should be branded as ascending rather than stationary.

During the same period, our archiving job deleted 213,629 rows (we're slowly clearing old data).  Is there any chance that a reducing rowcount could contribute to the stationary branding?  I've tested this before and it didn't look like it made any difference.

Update 2: Another day, another stats update, and the column is now flagged as Ascending!
As per the theory about deletes affecting this, I checked the percentage of updates being inserts compared to deletes, and yesterday 13% were inserts, whereas the previous two days inserts accounted for about 12%. I don't think that gives us anything conclusive.

Interestingly, a related table that gets on average 4 rows inserted for each row inserted into this main table, and has it's stats updated at the same time, has it's IDENTITY PK column still as Stationary!?

Update 3:
Over the weekend we get more inserts.  This morning the leading column is back to Stationary.  On the last stats update, we had 46840 inserts and only 34776 deletes.

Again, interestingly, the related table I mentioned above now has it's leading column branded as Ascending.  Is there no documentation that can explain this?

Update 4:
It's been a week or so now, the archiving job has cleared the backlog, so we're consistently deleting about two thirds of the number of rows being inserted.  The stats are showing mixed results across the related tables, with one showing stationary, and two showing ascending, despite them all being updated proportionally similarly.

There doesn't seem to be much guidance on what results in a branding of Stationary, however I did find KB2952101 that says if less than 90% of inserts were greater than the old maximum value, it would be classed as Stationary. All our inserts are new submissions, and the leading column is a bigint IDENTITY column, so 100% of inserts should be greater than the maximum previous value.
So my question is why would the column be branded as Stationary, when it is obviously Ascending?

It'll be branded stationary if, like you've already stated, that 10% or more of the inserts aren't ascending. If 100% of your inserts were as you say... then you might not have this problem, until of course you delete but then it would go back to unknown.
Here is a repro of your issue:
use master;
go
-- create a database for this to test
create database AscendingKey;
go

use AscendingKey;
go
-- create a test table
create table dbo.AscendingKeyTableTest
(
    SomeData        char(100) default('A'),
    AscendingKey    bigint not null,
);
go

-- insert some dummy data
set nocount on
go

declare @i int = 1

while(@i <= 1000)
begin
    insert into AscendingKeyTableTest(AscendingKey) VALUES (@i);
    set @i += 1
end
go

-- create stats on the ascendingkey column
create statistics AscendingKeyStats on dbo.AscendingKeyTableTest(AscendingKey);
go

-- look at the stats
dbcc traceon(2388);
dbcc show_statistics('dbo.ascendingkeytabletest', ascendingkeystats);
dbcc traceoff(2388);
-- unknown

-- now insert a few more ascending
declare @i int;
declare @j int = 1;

SELECT @i = max(ascendingkey) from dbo.AscendingKeyTableTest;

while(@j <= 10)
begin
    insert into AscendingKeyTableTest(AscendingKey) VALUES (@i+@j);
    set @j += 1
end
go

-- check again
dbcc traceon(2388);
dbcc show_statistics('dbo.ascendingkeytabletest', ascendingkeystats);
dbcc traceoff(2388);
-- unknown

-- update the stats
update statistics ascendingkeytabletest(ascendingkeystats) with fullscan;

-- now insert a few more ascending
declare @i int;
declare @j int = 1;

SELECT @i = max(ascendingkey) from dbo.AscendingKeyTableTest;

while(@j <= 10)
begin
    insert into AscendingKeyTableTest(AscendingKey) VALUES (@i+@j);
    set @j += 1
end
go

-- update the stats
update statistics ascendingkeytabletest(ascendingkeystats) with fullscan;

-- check again
dbcc traceon(2388);
dbcc show_statistics('dbo.ascendingkeytabletest', ascendingkeystats);
dbcc traceoff(2388);

-- now insert a few more ascending
declare @i int;
declare @j int = 1;

SELECT @i = max(ascendingkey) from dbo.AscendingKeyTableTest;

while(@j <= 10)
begin
    insert into AscendingKeyTableTest(AscendingKey) VALUES (@i+@j);
    set @j += 1
end
go

-- update the stats
update statistics ascendingkeytabletest(ascendingkeystats) with fullscan;

-- check again
dbcc traceon(2388);
dbcc show_statistics('dbo.ascendingkeytabletest', ascendingkeystats);
dbcc traceoff(2388);
-- ascending!
-- we hit the 3x stats updates to have it 'learn'

-- what happens if we insert more than 10% that isn't ascending
declare @i int = 1;

while(@i <= 10)
begin
    insert into AscendingKeyTableTest(AscendingKey) VALUES (@i);
    set @i += 1
end
go

-- still says ascending... but...
dbcc traceon(2388);
dbcc show_statistics('dbo.ascendingkeytabletest', ascendingkeystats);
dbcc traceoff(2388);
go
-- what if we update again?
update statistics ascendingkeytabletest(ascendingkeystats) with fullscan;
go
-- stationary
dbcc traceon(2388);
dbcc show_statistics('dbo.ascendingkeytabletest', ascendingkeystats);
dbcc traceoff(2388);
go
-- get it back to ascending
declare @i int;

SELECT @i = max(ascendingkey) from dbo.AscendingKeyTableTest;

insert into AscendingKeyTableTest(AscendingKey) VALUES (@i+1);
update statistics ascendingkeytabletest(ascendingkeystats) with fullscan;

insert into AscendingKeyTableTest(AscendingKey) VALUES (@i+2);
update statistics ascendingkeytabletest(ascendingkeystats) with fullscan;

insert into AscendingKeyTableTest(AscendingKey) VALUES (@i+3);
update statistics ascendingkeytabletest(ascendingkeystats) with fullscan;
go

dbcc traceon(2388);
dbcc show_statistics('dbo.ascendingkeytabletest', ascendingkeystats);
dbcc traceoff(2388);
go

-- what about the deletes?
delete from AscendingKeyTableTest where AscendingKey % 3 = 0
go

update statistics ascendingkeytabletest(ascendingkeystats) with fullscan;
go

dbcc traceon(2388);
dbcc show_statistics('dbo.ascendingkeytabletest', ascendingkeystats);
dbcc traceoff(2388);
go
-- back to unknown

-- cleanup
use master
go

drop database AscendingKey
go


TempDB not selectable for DBCC maintenance plan I'm trying to clean up some of our maintenance plans and noticed that our TempDB is not getting it's weekly dose of DBCC CHECKDB goodness.  I've tried to go into the maintenance plan and make sure it's there, but it's not showing up in the checkbox area.  Any ideas why that would happen or have any pointers to make tempDB selectable again? I know I can always just add an Execute SQL Task but I'd like to have this all taken care of in one step by the maintenance plan.  I've also tried creating a brand new plan and attempted to select it that way, still no joy.

It's a DW environment, 2008R2, 256GB RAM, 64 cores (HT enabled).  Our TempDB files/logs are on a FusionIO card by themselves so there's no contention.

Thanks again!

Tempdb won't be available for you in the Mantainance Plan database list box, but you sure can run it as a script:

DBCC CHECKDB(N'tempdb')  WITH NO_INFOMSGS

According to Mr. Brent Ozar, you SHOULD check tempdb for integrity, and if Brent is saying that, that is good enough for me.

regards


MySQL schema for recurring events - Query by specific date I've just asked this exact same question on stackoverflow so apologies for cross posting but I only just noticed dba so thought I'd ask across here as well.

I'm just about to start putting together a schema for a system that will have recurring events and keep going through different options. None of which seem ideal.

The requirements are that I need to store the events and the pattern by which they occur I also need to be able to query this data with a specific date to determine if the date/time specified is covered by any of these events.

An example that demonstrates the functionality I am after works along the lines of a train time lookup system.

I'd like to be able to insert the time at which a train departs for a specific journey. Say 10am every day Monday - friday.

Then I would like to be able to query this data set to see if any trains are leaving at 10am (with 15 minutes leeway either side so 9:45 - 10:15) on Wednesday 30th April 2014.

I keep going backwards and forwards on this without making any real progress so any ideas would be much appreciated.

Edit :

My initial thoughts on the table are as so:

The issue with this is that it is very static in it only allows for 24 hour granularity for the frequency of the trips. Ideally a system that allowed me to enter a start date and altering periods between trips would be the optimal solution. So for example, once a month, once a fortnight etc.

Let's say you made the table like this

CREATE TABLE TrainEvents
(
    id INT NOT NULL AUTO_INCREMENT,
    station_arr_id INT NOT NULL,
    station_dep_id INT NOT NULL,
    arr_dt DATETIME NOT NULL,
    dep_dt DATETIME NOT NULL,

    ...
    PRIMARY KEY (id),
    KEY station_arr_index (station_arr_id,arr_dt),
    KEY station_dep_index (station_dep_id,dep_dt)
);

Next, you insert into the table the event

Arriving From Station 21 on April 30, 2014
Arrived at Station 29 at 10:00 AM on April 30, 2014
Departing From Station 29 1 minute later

Here is that INSERT query

INSERT INTO TrainEvents (station_arr_id,arr_dt,station_dep_id,dep_dt)
VALUES (21,'2014-04-30 10:00:00',29,'2014-04-30 10:01:00');

OK, now let's see how you can query the following departure

All Departures 10:00 AM on April 30, 2014 from Station 29
Check Departures 15 minutes before and after

Here is that SELECT query

SET @MinuteWindow = 15;
SET @DepartureDT = '2014-04-30 10:00:00';
SET @DepartureDTMinBef = @DepartureDT - INTERVAL @MinuteWindow MINUTE;
SET @DepartureDTMinAft = @DepartureDT + INTERVAL @MinuteWindow MINUTE;
SELECT * FROM TrainEvents
WHERE station_dep_id = 29
AND dep_dt >= @DepartureDTMinBef;
AND dep_dt <= @DepartureDTMinAft;

EPILOGUE

Given your initial idea, you could change the station_arr_id and station_dep_id into a single route_id and store the routes in a route table

CREATE TABLE TrainRoutes
(
    station_id INT NOT NULL AUTO_INCREMENT,
    station_name VARCHAR(128) NOT NULL,

    ...
    PRIMARY KEY (station_id),
    KEY station_name_ndx (station_name)
);
CREATE TABLE TrainEvents
(
    id INT NOT NULL AUTO_INCREMENT,
    route_id INT NOT NULL,
    arr_dt DATETIME NOT NULL,
    dep_dt DATETIME NOT NULL,

    ...
    PRIMARY KEY (id),
    KEY arr_index (route_id,arr_dt),
    KEY dep_index (route_id,dep_dt)
);

You could also make the event record arrivals and departures separately.

CREATE TABLE TrainRoutes
(
    station_id INT NOT NULL AUTO_INCREMENT,
    station_name VARCHAR(128) NOT NULL,

    ...
    PRIMARY KEY (station_id),
    KEY station_name_ndx (station_name)
);
CREATE TABLE TrainEvents
(
    id INT NOT NULL AUTO_INCREMENT,
    route_id INT NOT NULL,
    event_dt DATETIME NOT NULL,
    event_type TINYINT NOT NULL # 1 for arrival, 2 for departure

    ...
    PRIMARY KEY (id),
    KEY event_index1 (route_id,event_dt,event_type),
    KEY event_index2 (route_id,event_type,event_dt),
);

I am just giving ideas. I'll leave it to you to implement. Perhaps, to give you some UI ideas, see the site I use to commute : http://as0.mta.info/mnr/schedules/sched_form.cfm . The result of the route you pick in that site shows route departures within a 5-hour window (2.5 hours before and 2.5 hours after).

Give it a Try !!!


Restore with Standby - Tail-log backup is greyed out I got a .bak file from one of our vendors.  The instructions from them says that I should RESTORE WITH STANDBY and select "Tail-log backup" under options.  Problem is that "Tail-log backup" is greyed out in SSMS.  I am logged in as sysadmin and my SQL Server version is MSSQL14

A tail-log backup is only relevant for databases in full or bulk-logged recovery, and you can only perform a tail-log backup if you have access to an online copy of the same database.

If you're just restoring a copy of a backup of some other database in a new location (regardless if you're replacing an older copy), and all you have is a static .bak file, you don't need to check this box.

I think they just sent you instructions for how they restore this backup internally, maybe, but this portion is irrelevant to you if you don't have access to the original source database in order to take a tail-log backup.

(Also, I would ask your vendor for RESTORE DATABASE commands, instead of a guide to pointing and clicking.)


Why is my SQL Server reporting CLR_AUTO_EVENT waits when I don't even use SQL CLR? When I run this query, I see high wait time for CLR_AUTO_EVENT:

SELECT
    *
FROM
    sys.dm_os_wait_stats
WHERE
    wait_type IN
    (
        'CLR_AUTO_EVENT',
        'CLR_CRST',
        'CLR_JOIN',
        'CLR_MANUAL_EVENT',
        'CLR_MEMORY_SPY',
        'CLR_MONITOR',
        'CLR_RWLOCK_READER',
        'CLR_RWLOCK_WRITER',
        'CLR_SEMAPHORE',
        'CLR_TASK_START',
        'CLRHOST_STATE_ACCESS',
        'ASSEMBLY_LOAD',
        'FS_GARBAGE_COLLECTOR_SHUTDOWN',
        'SQLCLR_APPDOMAIN',
        'SQLCLR_ASSEMBLY',
        'SQLCLR_DEADLOCK_DETECTION',
        'SQLCLR_QUANTUM_PUNISHMENT'
    )
ORDER BY
    wait_time_ms DESC,
    wait_type ASC;

In a related question What is the SQLCLR wait type in Activity Monitor within SQL Server Management Studio (SSMS)?
, I read that it appears when running .NET managed code within SQL Server.

However, I don't think I'm using any of that. When I execute EXEC sp_configure 'clr enabled', I see:

It looks to me like it's not even enabled. Can anybody please explain what's going on?

PS: I'm running SQL Server 2017 Developer Edition (14.0.3026.27).

SQL Server uses CLR internally for several features, and these waits track CLR background threads just sitting around waiting on a ManualResetEvent or a AutoResetEvent.  According to High waits on CLR_MANUAL_EVENT and CLR_AUTO_EVENT these are background waits (ie the wait time is not happening to user sessions), and can safely be ignored unless you have unsafe CLR code using these events directly.


Can we directly upgrade Oracle 12.1 to 19 c or do we need to upgrade 12.1 to 12.2 first and then 19c Can we directly upgrade Oracle 12.1 to 19 c or do we need to upgrade 12.1 to 12.2 first and then 19c, and when 19c will be available for download

The term used by Oracle is "direct upgrade". You have to take into consideration if your database is a multitenant (CDB) database.

From https://mikedietrichde.com/2018/12/14/premier-support-extension-for-oracle-19c-and-more/ :

  Oracle 12.2.0.1, Oracle 18c and Oracle 19c all together sail under the cover of the “Oracle 12.2 Release Family” and hence have a common support frame

So direct upgrade should be available with the following versions: 11.2.0.3, 11.2.0.4, 12.1.0.1, 12.1.0.2, 12.2.0.1

I'm not adding the MOS support hyperlinks since if you are posting the question here I'm guessing you dont have a MOS account.