Will Re-Seeding an Identity column back to 0 cause page splits? Simple table with an identity table as a clustered primary key. We unexpectedly hit the max value for the column, so I requested our DBA re-seed the table back to 0 to give us some time to examine updating the column. The table is also purged based on time, so we have a substantial gap in Id's to use up before we collide with anything. Since this is a high insert table, the DBA brought up the potential of page splits since we are no longer inserting at the end of the table.

My question is, how will SQL Server behave when the value of the identity column wraps around? Will inserting into gaps cause a performance issue? I want to consider maybe creating a sequence that will automatically cycle rather that up the id column to bigint.

My question is, how will SQL Server behave when the value of the identity column wraps around?

Assuming the index is 100% full the first few inserts at the new key value will cause one or two page splits, where half the rows on the full page are copied to the new page, and then the new row will be inserted into one of the half-full pages.

However after that you resume low-cost inserts and page allocations.  When the page fills up a new, empty page is allocated and inserted into the doubly-linked list of leaf pages.  No rows are copied to the new page, and the new row is written to the new page.

The only difference between this and an end-of-index insert is the presence of a next page after the new page.  And the next page must be updated with a reverse page pointer to the new page.

See this post for details: Good Page Splits and Sequential GUID Key Generation

  Will inserting into gaps cause a performance issue?

No.

As SQL Continues inserting into the "middle" of the index, when the target page for an insert is full, SQL will always allocate a new page.  It won't put the row on the next existing page, even if that page is not full.  The non-leaf levels of the clustered index store the starting value for each leaf-level page, so SQL can't insert the new row onto the next existing page without rewriting the non-leaf level of the index.

You can see this behavior easily by forcing page locks and looking at the lock footprint of your inserts (or less easily with DBCC PAGE). In the example below a clustered index table that can fit exactly 4 rows on a page take a series of mid-index inserts.

  Assume every page can hold 4 rows. You have full page existing rows
  with ascending key values from 10-20, and now you want to insert key
  values 1-9.

use master
drop database ps_test
go
create database ps_test
go
use ps_test
go
drop table if exists ps_test
set nocount on

go

--8,060=4*(4+2011)
create table ps_test(id int primary key, data char(2011) not null default '')

go
DBCC TRACEON(3604)
go

insert into ps_test(id) values (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21)--,22

go

dbcc ind('ps_test','ps_test',1)
--three data pages
/*

PageFID PagePID         PageType
------- ------- / /     --------
1       336             10
1       328             1
1       329             2
1       330             1
1       331             1
*/

insert into ps_test(id) values (1)

dbcc ind('ps_test','ps_test',1)
--four data pages 332 has been inserted between 328 and 330
/*

PageFID PagePID        PageType PrevPagePID NextPagePID
------- ---------/ /   -------- ----------- -----------
1       336            10       0           0
1       328            1        0           332
1       329            2        0           0
1       330            1        332         331
1       331            1        330         0
1       332            1        328         330
*/

dbcc page(ps_test,1,328,3)
-- 328 has rows 1,10 and 4048 bytes free
-- the page is split
/*
PAGE: (1:328)
...
pminlen = 2019                      m_slotCnt = 2                       m_freeCnt = 4048
...
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 1
...
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 10

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/

dbcc page(ps_test,1,332,3)
--page 332 has 11,12,13 and 2024 bytes free
/*

PAGE: (1:332)
...
pminlen = 2019                      m_slotCnt = 3                       m_freeCnt = 2024
...
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 11
...
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 12
...
Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4
id = 13
...
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/

insert into ps_test(id) values (2)
insert into ps_test(id) values (3)

dbcc ind('ps_test','ps_test',1)
--no page split.
/*

PageFID PagePID     PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- -------- ---------- ----------- ----------- ----------- -----------
1       336         10       NULL       0           0           0           0
1       328         1        0          1           332         0           0
1       329         2        1          0           0           0           0
1       330         1        0          1           331         1           332
1       331         1        0          0           0           1           330
1       332         1        0          1           330         1           328

the new rows went on page 328, which now has

pminlen = 2019                      m_slotCnt = 4                       m_freeCnt = 2024
*/

So now we have page 328 is full and the next page 332 has only 3 rows.  But the lowest value on 332 is 11.  So when we insert the value 4, does go on 332, which will cause another bad page split when 5 is inserted?

insert into ps_test(id) values (4)
dbcc ind('ps_test','ps_test',1)

/*
A new page 333 has been inserted in the list between 328 and 332

PageFID PagePID     PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- -------- ---------- ----------- ----------- ----------- -----------
1       336         10       NULL       0           0           0           0
1       328         1        0          1           333         0           0
1       329         2        1          0           0           0           0
1       330         1        0          1           331         1           332
1       331         1        0          0           0           1           330
1       332         1        0          1           330         1           333
1       333         1        0          1           332         1           328

*/

dbcc page(ps_test,1,333,3)

/*

PAGE: (1:333)
...
pminlen = 2019                      m_slotCnt = 1                       m_freeCnt = 6072
...
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 10
...
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

*/

So another "bad page split", but this one only moved the row with id=10.  And leaves page 328 full with values 1,2,3,4

dbcc page(ps_test,1,328,3)
/*
PAGE: (1:328)
...
pminlen = 2019                      m_slotCnt = 4                       m_freeCnt = 0
...
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 1
...
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 2
...
Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 3
...
Slot 3 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 4
...
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

*/

Now all the larger key values are on seperate pages, and from here on out there are no more bad page splits.  The next insert requires a new page, and only the new row is on the new page:

    insert into ps_test(id) values (5)
    dbcc ind('ps_test','ps_test',1)

/*

PageFID PagePID      PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- -----------  -------- ---------- ----------- ----------- ----------- -----------
1       336          10       NULL       0           0           0           0
1       328          1        0          1           334         0           0
1       329          2        1          0           0           0           0
1       330          1        0          1           331         1           332
1       331          1        0          0           0           1           330
1       332          1        0          1           330         1           333
1       333          1        0          1           332         1           334
1       334          1        0          1           333         1           328

*/

The new page 334, has only the last-inserted row.

 dbcc page(ps_test,1,334,3)
/*
PAGE: (1:334)
...
pminlen = 2019                      m_slotCnt = 1                       m_freeCnt = 6072
...
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 5
...
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
id = 5

*/

So after a couple of initial "bad page splits" that required moving rows, subsequently new pages are allocated and spliced into the middle of doubly-linked list of leaf pages.  The new pages are allocated when a new row wont fit on the previous page and no rows are migrated to the new page.

Your DBA is correct. Once you reset the clustering key value to cycle over and become smaller than the existing minimal value, all inserts will need to go to the first page of the index. Since new EMPTY page allocations only happen at the end of the index, when it will fill up, you will get a split for that, and practically for every page written. Depending on your disk subsystem performance and capacity, and a variety of other factors, this may or may not be an operational challenge.

I don't really see a way around it, except for dropping and recreating the index in DESC order, and using a negative sequence increment to go from the high boundary of your gap, and count backwards towards 0 and beyond.


Indexing order by columns Based on the query below it was suggested that I create an index on the column in the order by clause. My understanding is the sorting is done on the result set and not the table.

The person suggesting this insists it helps with performance. I don't think it does but I have been wrong once or twice before.

Thanks.

SELECT TOP 1 [Process_Activity_Id]
,[Process_Id]
,[Transaction_Type]
,[Company_Code]
,[Entity_Identifier]
,[Item_Request]
,[Item_Response]
,[Item_Description]
,[Status]
,[Status_Detail]
,[Start_Date]
,[End_Date]
,[Last_Update_Date]
FROM [dbo].[Process_Activity] (NOLOCK)
WHERE [Entity_Identifier] = @EntityIdentifier
  AND [Status] = 'In_Progress' AND [Process_Id] = @ProcessId
ORDER BY [Message_Sequence] ASC

Based on:

WHERE [Entity_Identifier] = @EntityIdentifier
AND [Status] = 'In_Progress' AND [Process_Id] = @ProcessId
ORDER BY [Message_Sequence] ASC

...the best thing is to have an index on

([Entity_Identifier], [Process_Id], [Message_Sequence]) WHERE [Status] = 'In_Progress'

As Status is filtered with a constant, a WHERE clause is going to be useful for this, so that the index only holds the "In_Progress" rows.

But you need to find the rows that match the Entity and Process, and to have those rows in Message_Sequence order (to save getting all the Message_Sequence values and then sorting them when the query runs).

The composite index like this will let the database engine seek into the appropriate Entity and Process, knowing that the first row it finds is the smallest Message_Sequence.

So the advice about indexing on Message_Sequence is kinda right, so long as you're only indexing on Message_Sequence within an index that's on Entity and Process, because those columns are more important.

If you only had an index on Message_Sequence, you'd be searching through the index for the first one that matched Entity and Process, which is only good for non-equality predicates (oh - I'm talking about this at the PASS Summit in Seattle this October).

In general, index are used during row selection. So, creating an index on combination of fields in the where clause, such as Entity_Identifier, Status, and Process_Id would help the performance. The exact field combination would depend on the nature of data you have in your table.

You are right, creating an index on the Message_Sequence would not help the performance because the sorting is done post selection.

Hope this helps.


Making a server with multiple instances a member of two effectively independent clusters We're in the early stages of planning a new database environment for our ERP and CRM applications (third-party). The goal is to use Always On Availability Groups for HA, rolling patches, read-only secondaries, etc. We want to use automatic failover, without the client application needing to be aware of the HA.

We've got one physical server that is quite massively specced. It's going to be running two instances of SQL Server: one for the ERP application that requires SQL Server 2016, and one for the CRM application that is better suited by 2017 (and this also allows us to give sysadmin rights to the CRM application vendor's support without affecting the ERP application).

We also have a pretty mature ESX infrastructure, and want to use some VMs as failover nodes. These won't be as decadently built as the primary server, but we will ensure acceptable performance for short outages or maintenance windows on the primary server.

My initial thought was to have two failover VMs: one for the ERP instance and one for CRM. It doesn't appear that a single server can be a member of multiple Windows failover clusters, i.e. I can't make an ERP cluster with the ERP VM and the physical server, plus a CRM cluster with the CRM VM and the same physical server.

If I were to instead build a three-node cluster, and control which nodes each instance can run on, would I be inviting any unpleasant side effects? My main fear is that quorum voting could get a bit weird if we start adding more database servers to the cluster, and we could have an instance go down unexpectedly even if all the nodes that can host that instance are still up. Is that even a risk, and are there reasonable ways to mitigate it? Are there enough possible complications that I should use a single, beefier VM that can handle both instances?

tl;dr:

Would I be shooting myself in the foot by building a three-node cluster where instance 1 can only run on servers A and B, and instance 2 can only run on servers A and C? Should I just build a two-node cluster (plus quorum member) and make sure server B can handle both SQL Server instances?

I cannot see any reasons as to why you would have any issues. Just be sure to add a file share or disk witness to the cluster, as that will ensure there is no "weirdness" in the quorum. As long as you set the AG's up correctly, I cannot see any reasons not to use your suggested approach.
The only negative i can think of, is that it will be an additional environment to manage and maintain, but you are already doing that having 2 instances on your primary. Will you be using the VM's for the Read-Only connections?

Given that you are

  planning a new database environment for our ERP and CRM applications

and

  also have a pretty mature ESX infrastructure

This:

  running two instances of SQL Server:

Is not really a good idea.  Sharing a Windows VM and a larger cluster creates unneeded complexity and risk, with basically nothing useful in exchange.

Just create seperate VMs for each of the two instances, and run them on the same ESX host if you want.

For each one you can add a secondary VM with an AG replica for HA.


Extract one MySQL database from a FULL MYSQL Server backup file I have a small shared windows server on which i'have installed MySQL 5.1 engine for hosting my user's mysql databases.

I backup the complete MySQL databases each day by running this script as batch file in SCHEDULED TASKS:

"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqldump.exe" -uroot -p"**********" --force --flush-logs --all-databases > c:\dbbackup\alldatabases.sql

So alldatabases.sql will be a SQL file which contains the complete data of MySQL server which can be restored on cases that all of the databases are lost or migrating to another server.

I have never extracted one specific mysql database from this "alldatabases.sql" backup file and i don't have any clue on this.

**The must idiot solution is to restore this big backup file in another server and then simply create an individual backup from a specific database.

Could you help me on extracting one specific MySQL database from this big backup file?
Lets say that MySQL database name is  JOOMLAdb

** Also do you have any idea on changing my backup script, so instead of having a big backup file i may have individual backups for each database (Databases will increase/decrease through the time and we don't have a fix list of available databases)
Thank you for your care

Best Regards

Your same question seems to be answered here:

https://stackoverflow.com/questions/2342356/import-single-database-from-all-databases-dump

And as far as an improved backup script, here's what I'm currently using for several years and it rocks:

http://sourceforge.net/projects/automysqlbackup/

Idiot solution 1
Restore entire database(s) on some temporary machine, then dump on the single database.
Be sure to test the single database restores properly, can be issues with host/user permissions.

Solution 2,
I just wrote a script that does the single database dump restore.  It fairly simple to do. I would send you the code but i wrote it centos which most likely will not work on your windows.


Best way to automate the loading of data daily into google big query from SQL Server Best way to load/copy/insert data from a SQL Server database into a  table in Google Big Query on a daily schedule.

I.E data from a table in a SQL Server database to a table in Google Big Query

I was hoping to use SSIS but there are no native data sources/destinations only 3rd party offerings?

I was thinking of using SSIS to create a CSV file then using Google BQ commandline push the CSV file to big query?

Does anyone have any other better suggestions/methods?

See this Q&A at Stack Overflow: Migrate SQL Server database to BigQuery

SQLCMD is your friend. It dumps the results of a query into a CSV.


measure the traffic throughput on mysql table In order to split mysql database into two different databases on different hosts. I would like to know few things:

what is the size of the tables.
what is the traffic throughput to those tables.

Size of the tables can be determined easily, but how to find traffic throughput to tables.

How to identify how many data was retrieved from specific table?

Try MONyog which has Disk Info tab which shows tables size
also you will come to know most accessed tables using Real-Time feature of MONyog.

mytop mytop is a console-based (non-gui) tool for monitoring the threads and overall performance of a MySQL 3.22.x, 3.23.x, and 4.x server


Replicated timestamp column in different timezone I have the master running Mysql 5.5.38 on Ubuntu 14.04 LTS. The timezone in the master is UTC+5:30. The slave is in AWS RDS which runs in UTC. The slave is primarily used for running for SQL queries by staff.

To maintain their sanity, I setup the timezone hack. This also applies to the replication user.

One of our innodb tables records scheduled appointments. The relevant parts of the table are below:

`startDateTime` timestamp NOT NULL DEFAULT '2000-01-01 12:00:00',
`endDateTime` datetime DEFAULT NULL,
`createdOn` timestamp NULL DEFAULT NULL,

I am seeing the following time on the master when querying the table:

startDateTime - IST
endDateTime - IST
createdOn - IST

However on the slave:

startDateTime - UTC
endDateTime - IST
createdOn - IST

Running SELECT NOW(); on either servers shows current time in UTC+5:30. The bin_log format on both master and slave is STATEMENT

How do I ensure that the replicated column startDateTime is also in IST?

Update:

RDS is a hosted mysql with no access to my.cnf. Futher time_zone variable is restricted and hard coded to UTC

The current time zone setting does not affect values displayed by functions such as UTC_TIMESTAMP() or values in DATE, TIME, or DATETIME columns. Nor are values in those data types stored in UTC; the time zone applies for them only when converting from TIMESTAMP values.

http://dev.mysql.com/doc/refman/5.6/en/time-zone-support.html

Change your binlog format to ROW to eliminate the vagaries of statement-based replication and then change your DATETIME columns to TIMESTAMP (on the master, let the change replicate).

This solves the issues you're experiencing.

Although... it solves entirely the wrong problem, which is that it's typically better to store times properly in UTC and localize them as needed.  Of course, that's what TIMESTAMP columns essentially do -- they store datetimes as UTC after converting then from the current session time zone, and then they convert back when you read them.

  If you want locale-specific arithmetic for DATE, TIME, or DATETIME values, convert them to UTC, perform the arithmetic, and then convert back.

I had similar issues a month or 2 ago. There is a solution for this. But this has been implemented in CentOS.

There are 2 steps for the resolution [run this on slave]:-

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -uroot -p mysql
This command should copy all the time from unix timezone directory to mysql.time_zone_name table
After this you need to tell mysql which timezone to use, to do this
Edit /etc/my.cnf to add a line default_time_zone=Asia/Kolkata , Asia/Kolkata could be changed to any timezones you want.  This change will require mysql restart to take in effect.   If you do not want to restart then simply use this in mysql command
set global time_zone = 'Asia/Kolkata'; set session time_zone = 'Asia/Kolkata';

Hope this helps!!


Can't restart or stop Firebird 1.5 on linux I have an ubuntu server running firebird 1.5. It was working fine, but recently it started hanging (firebird does not respond anymore). Even when trying to restart or stop the server, it shows an error:

>sudo service firebird1.5-super stop
 * Stopping Firebird 1.5 server manager...
Unable to complete network request to host "localhost".
-Failed to establish a connection.
-Connection reset by peer
can not attach to server

What can be happening?

I think I found the solution, but I believe it is more related to software then database (So if someone knows how to move this to stack overflow please do this).

The problem was that when hibernate (the persistence layer) works with Firebird 1.5, it doesn't create the temporary tables used by hibernate like in other new databases, instead it creates actual tables with the prefix HT_ (HT_SOMETABLE). I tried to deactivate this with a "hack", until now it's working fine, no more hanging and nothing unusual has happened because of this hack.

This is what I did:

I created a custom Firebird dialect to stop hibernate from creating these tables:

import org.hibernate.dialect.FirebirdDialect;

public class FirebirdHackedDialect extends FirebirdDialect {

    @Override
    public String getCreateTableString() {
        String sql = "HackParaNaoCriarTabelasNoFirebird: ";
        return sql;
    }

}

And set this dialect to the persistence.xml:

<property name="hibernate.dialect" value="com.yourpackage.etc.FirebirdHackedDialect"/>


Inserting a list of emails into multiple rows on the same column I have a list of emails of staff members and would like to:

insert them into multiple rows on the same column (single column only for emails)
the same question above but would like to replace them with old ones.

You should provide details about:

The table you want to update
The format of the data of the email list.

The obvious problem is that just a list of email adresses wont't help much, since you don't know who they belong to. So what is the unique key for the table identifying the employee, and is that key also in the list of email adresses?

If the list provides the key, what you can do is load that list into a temp table (using sql loader), and then run a procedure or script that updates the table based on the values and unique key of the employee from the temp table, and then when done and no errors, remove the temp table.

I do not know the answer in sql, but you can do it in excel using concatenate formula like:

=CONCATENATE(A1,"@gmail.com").


MySQL on MACOS Sierra secure-file-priv setting I cannot find where to set secure-file-priv. I get this message:

  1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

when running this statement:

SELECT COLUMN_NAME, data_type FROM tabledefs INTO  OUTFILE  'xpeter'

If I remove OUTFILE 'xpeter'  it works.  OUTFILE '/tmp/xpeter does not work either.

I tried to add --secure-file-priv="/tmp" , no change. Variable stays as NULL:

 $bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" --secure-file-priv="/tmp"    $other_args >/dev/null &

/usr/local/mysql/bin/mysqld --verbose --help | grep secure-file
  --secure-file-priv=name
secure-file-priv                                             NULL

There is no my.cnf file.  I run through whole filesystem, looking for any *.cnf file.  Could not find any other than: ./mysql-5.7.17-macos10.12-x86_64/data/auto.cnf and ./mysql-5.7.17-macos10.12-x86_64/support-files/my-default.cnf.  The line $bindir/mysqld...  is in support-files/my-default.cnf.

I rerun the query without OUTFILE 'xpeter',  query OK, no messages in log files except apache2 log.  Then I run

SELECT COLUMN_NAME, data_type INTO  OUTFILE  'xpeter' FROM tabledefs

no errors in  logfiles, only in phpmyadmin (where I run the query statement from, logged in as root): same message as before.

I tried the etc/my.cnf and also:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
      <key>ProgramArguments</key>
        <array>
   <string>--secure-file-priv=/mysql_exp</string>
        </array>
    <key>WorkingDirectory</key>  <string>/usr/local/mysql</string>
</dict>
</plist>

In both cases after reboot I was still not allowed to write a file.

I did not change the folder owner. If I add --secure-file-priv=/mysql_exp to Daemons and reboot MySQL does not run at all.

on mac os x startup parameters of mysql You can change by edit .plist file

location:

/Library/LaunchDaemons
com.oracle.oss.mysql.mysqld.plist

Use Xcode or for example BBEdit, and add line in section ProgramArguments

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
    <key>Disabled</key>
    <false/>
    <key>ExitTimeOut</key>
    <integer>600</integer>
    <key>GroupName</key>
    <string>_mysql</string>
    <key>KeepAlive</key>
    <false/>
    <key>Label</key>
    <string>com.oracle.oss.mysql.mysqld</string>
    <key>LaunchOnlyOnce</key>
    <false/>
    <key>ProcessType</key>
    <string>Interactive</string>
    <key>Program</key>
    <string>/usr/local/mysql/bin/mysqld</string>
    <key>ProgramArguments</key>
    <array>
        <string>/usr/local/mysql/bin/mysqld</string>
        <string>--user=_mysql</string>
        <string>--basedir=/usr/local/mysql</string>
        <string>--datadir=/usr/local/mysql/data</string>
        <string>--plugin-dir=/usr/local/mysql/lib/plugin</string>
        <string>--log-error=/usr/local/mysql/data/mysqld.local.err</string>
        <string>--pid-file=/usr/local/mysql/data/mysqld.local.pid</string>
        <string>--secure-file-priv=/mysql_exp</string>
    </array>
    <key>RunAtLoad</key>
    <false/>
    <key>SessionCreate</key>
    <true/>
    <key>UserName</key>
    <string>_mysql</string>
    <key>WorkingDirectory</key>
    <string>/usr/local/mysql</string>
</dict>
</plist>

restart MySQL and check:

mysql> show variables like 'secure%';
+------------------+-------------+
| Variable_name    | Value       |
+------------------+-------------+
| secure_auth      | ON          |
| secure_file_priv | /mysql_exp/ |
+------------------+-------------+
2 rows in set (0.00 sec)

Add:

You can achieve same result if will create my.cnf file straight in /etc/

sudo nano /etc/my.cnf

You can copy example file from

/usr/local/mysql/support-files/

just notes about comments - "copy/past" it's not a way, even on Your Mac You really do not need file import/export at all
just use normal tools like:

MySQL Workbench
Navicat for MySQL
JetBrain IDE (most of them have database module)
DBVisualizer

any of them can help You:

export result of query into different format - csv, delimited file, json, xls
Import popular format of files direct into table, with mapping columns to datatypes

manual file level import/export could be useful in production environment where You need automate processes, but also for now it is more backup way, primary and proper - ETL scripts and tools.

echo "[mysqld]\nsecure_file_priv\t\t= ''\n" | sudo tee /etc/my.cnf

And then restart mysql. If brew was used to install the mysql run the following command:

brew services restart mysql


Using SQL Server with BitLocker I have an application that is running on a device that has two partitions: C and D. The application runs off of C while D contains the SQL Server database (mdf file). In addition, D is also encrypted using BitLocker.

We are using the code-first approach where the database is created on the fly, based on the specified entities within our code. If the database has not yet been created and I turn BitLocker on, the application cannot create the DB and I get a 'Directory lookup exception', with the message 'This drive is locked by BitLocker'. Though, as soon as I turn off BitLocker, it gets created as expected and everything is fine. I turn on BitLocker again, and this time I get a different exception, 'the login failed for user sa'. Once again, this works when I turn BitLocker off. Is there something different that I need to do since we are using the SQL sa user to login?

According to Using SQL Server 2012 Standard with Bitlocker

I  need to make sure that the SQL Server Service account is the one used for turning BitLocker on. I've changed the account (Services -> Sql Server -> Properties -> LogOn) to the user account I used for turning BitLocker on, but that does not seem to do the trick.

I agree with Jithin R and ensuring the SQL Server "service" account (what you have set in the services.msc for the login as, and ensuring it has at least MODIFY or FULL permissions at the NTFS folder level where the MDF and LDF files are created when they code dynamically creates the DB).

It sure sounds like an NTFS permission issue with whatever security context is being used to created the LDF and MDF files does NOT have permission on the disk partition to create the DB files when initiated.

The security context will need at least LIST to traverse all folders and subfolders to the level of the folder where the DB files will be created, and where those files are created, it'll need MODIFY or FULL permissions there but MODIFY access should suffice.

I'm not sure why BitLocker being enabled or disabled on this disk would have an effect on this but perhaps BitLocker enabling in your configuration changes the NTFS permissions of the folders where the DB is being created.

disable bit-locker from the drive which has .mdf,.ldf,.ndf files. Give permission to those files ie right click-> security -> add -> full permission(read,write,exec,etc.) for that system . I hope you will be get rid of your problem.


SQL Server 2008 R2 - Specify the Full Backup to use when doing a Differential Backup When I backup my Database, I do a Full Backup every day who goes on a tape.
I am trying to use the Sunday's Full backup to set up an additionnal Differential Backup, how can I do this? It only takes the last Full Backup.

Full (Run on Sunday) :

BACKUP DATABASE [DATABASE] TO  DISK = N'J:\DATABASE Backup\MyDatabase_Full.bak' WITH NOFORMAT, NOINIT,  NAME = N'DATABASE-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Partial (Run each Day, but use the wrong Full Backup) :

BACKUP DATABASE [DATABASE] TO  DISK = N'J:\DATABASE Backup\MyDatabase_Partial_20120105.bak' WITH  DIFFERENTIAL ,  RETAINDAYS = 10, NOFORMAT, NOINIT,  NAME = N'DATABASE-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10

You should do the daily backups as copy_only, so that it doesn't break the chain for transaction log backups or differential backups. After that the daily full backup won't be taken into account as basis for the differential backups.

Quote from MSDN:

"COPY_ONLY
    Specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. A copy-only backup is created independently of your regularly scheduled, conventional backups. A copy-only backup does not affect your overall backup and restore procedures for the database.

Copy-only backups were introduced in SQL Server 2005 for use in situations in which a backup is taken for a special purpose, such as backing up the log before an online file restore. Typically, a copy-only log backup is used once and then deleted.

When used with BACKUP DATABASE, the COPY_ONLY option creates a full backup that cannot serve as a differential base. The differential bitmap is not updated, and differential backups behave as if the copy-only backup does not exist. Subsequent differential backups use the most recent conventional full backup as their base."

sqlserver remembers what the most recent backup was. When you request a differential backup it looks to see what the most recent full backup was and then makes a differential backup that contains everything since that most recent full backup.
If you use copy_only backups you cannot use those copy_only backups with differential backups at a later point to recover. Do not use copy_only backups as the basis for our backups.


Determine Which Parameters Passed Into Stored Procedure Is there a way to determine (from within a stored procedure) which parameters were passed into a stored procedure (vs. those which just took the default value)?

You can run a Profiler Trace using the default template.  It will include the RPC:Completed Event.  When you review the trace log you will see the values of the parameters passed and you can determine which were left out, therefore using the default values.


MySQL - ERROR 1045 (28000): Access denied for user I just installed a fresh copy of Ubuntu 10.04.2 LTS on a new machine.  I logged into MySQL as root:

david@server1:~$ mysql -u root -p123

I created a new user called repl.  I left host blank, so the new user can may have access from any location.

mysql> CREATE USER 'repl' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.00 sec)

I checked the user table to verify the new user repl was properly created.

mysql> select host, user, password from mysql.user;
+-----------+------------------+-------------------------------------------+
| host      | user             | password                                  |
+-----------+------------------+-------------------------------------------+
| localhost | root             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| server1   | root             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| 127.0.0.1 | root             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| ::1       | root             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| localhost |                  |                                           |
| server1   |                  |                                           |
| localhost | debian-sys-maint | *27F00A6BAAE5070BCEF92DF91805028725C30188 |
| %         | repl             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-----------+------------------+-------------------------------------------+
8 rows in set (0.00 sec)

I then exit, try to login as user repl, but access is denied.

david@server1:~$ mysql -u repl -p123
ERROR 1045 (28000): Access denied for user 'repl'@'localhost' (using password: YES)
david@server1:~$ mysql -urepl -p123
ERROR 1045 (28000): Access denied for user 'repl'@'localhost' (using password: YES)
david@server1:~$

Why is access denied?

The reason you could not login as repl@'%' has to do with MySQL's user authentication protocol. It does not cover patterns of users as one would believe.

Look at how you tried to logged in

mysql -u repl -p123

Since you did not specify an IP address, mysql assumes host is localhost and tries to connect via the socket file. This is why the error message says Access denied for user 'repl'@'localhost' (using password: YES).

One would think repl@'%' would allow repl@localhost. According to how MySQL perform user authentication, that will simply never happen. Would doing this help ?

mysql -u repl -p123 -h127.0.0.1

Believe it or not, mysql would attempt repl@localhost again. Why? The mysql client sees 127.0.0.1 and tries the socket file again.

Try it like this:

mysql -u repl -p123 -h127.0.0.1 --protocol=tcp

This would force the mysql client to user the TCP/IP protocol explicitly. It would then have no choice but to user repl@'%'.

This could be an issue with corruption of your mysql database. Tables inside mysql database like user table can get corrupt and may cause issued.

Please do a check on those

myisamchk /var/lib/mysql/mysql/ *.MYI

Usually while checking or fixing myisam tables we would like to take mysql down first. If this problem is still not solve please try this out aswell.

If they are corrupt then you can fix them using

myisamchk --silent --force --fast  /path/table-name.MYI

Thanks,

Masood


Oracle Linked Server query error We have a problem with a query from a SQL Server 2008R2 - 64bit
to an Oracle Linked Server.

We use the Oracle 64-bit driver for Windows.

When we query a certain table we get the following error:

  Msg 7347, Level 16, State 1, Line 1 OLE DB provider 'OraOLEDB.Oracle'
  for linked server 'SERV_VISION2' returned data that does not match
  expected data length for column
  '[SERV_VISION2]..[VISION].[INV_M001].CUS_NM'. The (maximum) expected
  data length is 30, while the returned data length is 24.

The problem seems to be the data in the CUS_NM column.
In the rows where he gives this error the data is like this:

¿¿¿

So like a question mark but upside down. There doesn't seem to be a problem with the
length of the column, for one reason he can't handle this data I guess.

Did anyone experience this problem?
Anyone a clue how to solve this?

That's most likely an NLS (language) problem and you've got customer names with characters your database cannot handle. You'll need to determine the actual type of the CUS_NM column in the Oracle database.

Try below way-

CAST(CUS_NM AS VARCHAR(50))

here casting can vary as per your data type.


SQL Server - difference between instance name and instance id I'm a bit confused with SQL Server instances. The Instance Name and Instance ID are different properties, yet Instance ID tends to be defaulted to a capitalized version of the Instance Name. The installation allows you to set a different value for the ID than the Name, although typically they're the same.

Obviously, ID refers to internal reference and Name refers to user-friendly reference. But what are the fundamental differences between the two? When should one be used rather than the other?

I need to understand this while deploying / installing SQL Server 2008 R2 Express on our clients' server machines from a custom installation process.

By default, the instance name is used as the Instance ID. Instance ID is used to identify installation directories and registry keys for your instance of SQL Server. This is the case for default instances and named instances

(source: http://msdn.microsoft.com/en-us/library/ms143531(v=sql.120).aspx )

Install SQL 2008 r2 steps: http://blogs.msdn.com/b/petersad/archive/2009/12/17/sql-server-2008-r2-sysprep-step-by-step.aspx

Instance Name is like SQL20mm_64ENT  e.g. you're installing PC_102\SQL20mm_64ENT

Instance ID is like MSSQLnn.SQL2016_64ENT


Mysql 5.7 cannot start again For the first time in my life, I have Ubuntu, so I do not know much how to manage and what should I be looking for; I'm using version 18.04.
I installed MySQL 5.7, it worked great for a couple of days and suddenly it can't even restart, doesn't start again (with service mysql start or anything)...

Using systemctl status mysql.service I get:

● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor
preset: enabled)
Active: failed (Result: exit-code) since Thu 2018-07-26 14:24:49 CEST;
10min ago
Process: 3758 ExecStart=/usr/sbin/mysqld --daemonize --pid-
file=/run/mysqld/mysqld.pid (code=exited
Process: 3749 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre
(code=exited, status=0/SUCCESS)

јул 26 14:24:49 maax systemd[1]: mysql.service: Service hold-off
time over, scheduling restart.
јул 26 14:24:49 maax systemd[1]: mysql.service: Scheduled
restart
job, restart counter is at 5.
јул 26 14:24:49 maax systemd[1]: Stopped MySQL Community Server.
јул 26 14:24:49 maax systemd[1]: mysql.service: Start request
repeated too quickly.
јул 26 14:24:49 maax systemd[1]: mysql.service: Failed with
result 'exit-code'.
јул 26 14:24:49 maax systemd[1]: Failed to start MySQL Community
Server.

and using journalctl -xe I get:

јул 26 14:24:49 maax systemd[1]: mysql.service: Scheduled
restart job, restart counter is at 5.
-- Subject: Automatic restarting of a unit has been scheduled
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Automatic restarting of the unit mysql.service has been scheduled,
as the result for
-- the configured Restart= setting for the unit.
јул 26 14:24:49 maax systemd[1]: Stopped MySQL Community Server.
-- Subject: Unit mysql.service has finished shutting down
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Unit mysql.service has finished shutting down.
јул 26 14:24:49 maax systemd[1]: mysql.service: Start request
repeated too quickly.
јул 26 14:24:49 maax systemd[1]: mysql.service: Failed with
result 'exit-code'.
јул 26 14:24:49 maax systemd[1]: Failed to start MySQL Community
Server.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Unit mysql.service has failed.
--
-- The result is RESULT.

What should I do to fix this and continue to use Mysql every day normally?
Mysql works okay when I reinstall it, but after some time, it crushes again...

The error log of MySQL records the details why MySQL can not restart, check the error log and update it in Questions would be helpful.

If MySQL is installed by apt-get or some other system's ways and uses the default configurations, error log always in /var/log/mysql.

If my.cnf changes, check the modified my.cnf in /etc/mysql/my.cnf or other paths to find the log_error param.


Always On Synchronous Commit Mode With Large Delays For SQL Server Enterprise's Always On availability groups in Synchronous Commit mode, is there some amount of latency that can't be exceeded?  For example, can it work between very distant data centers, such as between the US and Europe?

The answer you're looking for is in the docs

⚠ Note
If primary's session-timeout period is exceeded by a secondary replica, the primary replica temporarily shifts into asynchronous-commit mode for that secondary replica. When the secondary replica reconnects with the primary replica, they resume synchronous-commit mode.

When this happens, a synchronous secondary will fall back to be asynchronous (what some folks call "pseudo synchronous" because you set it to synchronous, but it's actually async). This is not specific to network latency, or physical distance, but can happen under a number of scenarios. As an example, if you restart a synchronous replica and that server is offline, your AG will fall back to asynch and continue to process transactions.
Synchronous AGs only pay a performance penalty on writing to the transaction log (which needs to write to the secondary replica). Write activity is often a minority of the database activity, with the majority of traffic being read operations, which are not affected by synch/async.
Running an AG in sync commit mode between US & EU is totally possible. Write transactions will be slower, as they need to write to the log on the other side of the ocean. Generally, a better design is to keep synch replicas closer to avoid the network latency. However, depending on the IO patterns of your database, your business needs, and your network backbone between geographies, it's possible. On the systems I support, we can tolerate synchronous commit latency across hundreds of miles--but when the distance gets longer than that the performance impact becomes too great. It's not that SQL Server can't support it--it's that the users will not tolerate that slowness.
Generally, I find most folks end up running synchronous commit within a data center/geography for high availability, and asynchronous commit between geographies/data centers for disaster recovery. For a planned failover across geographies, you can temporarily switch the remote replica to synchronous mode to ensure no data loss during the failover.
The easiest way to find out the actual impact on your application is to test it.

A Secondary replica should almost never be configured in synchronous mode and in particular if there is latency between the primary and secondary as in your case as it will result in a degraded performance at the primary replica leading to contention issues and lots more.
In addition, remember that the 2 data centers can always have a network disconnection...
When you work in synchronous mode you can think of it as almost like a 2-phase commit.


Tabled Value Function returns incorrect or no results I recently learned of TVF's and it seemed like it was exactly what I was looking for. I did my first attempt at one and it is not going well.

Here is my code:

USE [RMC_Tracker] GO
/****** Object:    UserDefinedFunction [dbo].[TFV_Dashboard]
9:08:08 AM ******/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO

Script Date: 4/13/2020

--------------------------------------------
Author:    John Clark
Create date: 4/12/2020
Description: Returns a recordset for the RMC Tracker

ALTER FUNCTION [dbo].[TVF_Dashboad]
(
-- Add the parameters for the function here
@CompDate datetime = NULL

)
RETURNS TABLE AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT    dbo.ACTIVE.RECORD_ID, dbo.ACTIVE.CMD_ID, dbo.ACTIVE.EQUIPMENT, dbo.ACTIVE.EquipDes,
          dbo.ACTIVE.CASREP,dbo.ACTIVE.[UPDATE], dbo.ACTIVE.TYAST,
          dbo.ACTIVE.STATUS,dbo.ACTIVE.JCN, dbo.COMMANDS.Trigrpah

FROM      dbo.ACTIVE INNER JOIN dbo.COMMANDS ON dbo.ACTIVE.CMD_ID = dbo.COMMANDS.CMD ID

WHERE    (dbo.ACTIVE. RECCLS = @CompDate)
)

I call this Function from within MS Access via a Pass Through Query:

SELECT * FROM dbo.TVF_DashBoard(default)

It returns an empty set. What I am trying to do is create a simple Function that allows me to return a table that can be toggle based on whether a field (RECCLS) is either Null or Not Null.

Any assistance would be appreciated.

The default value of @CompDate is NULL. I suppose you actually want to find rows where dbo.ACTIVE.RECCLS IS NULL. So you need to modify your function code:

ALTER FUNCTION [dbo].[TVF_Dashboad]
(
-- Add the parameters for the function here
@CompDate datetime = NULL

)
RETURNS TABLE AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT    dbo.ACTIVE.RECORD_ID, dbo.ACTIVE.CMD_ID, dbo.ACTIVE.EQUIPMENT, dbo.ACTIVE.EquipDes,
          dbo.ACTIVE.CASREP,dbo.ACTIVE.[UPDATE], dbo.ACTIVE.TYAST,
          dbo.ACTIVE.STATUS,dbo.ACTIVE.JCN, dbo.COMMANDS.Trigrpah

FROM      dbo.ACTIVE INNER JOIN dbo.COMMANDS ON dbo.ACTIVE.CMD_ID = dbo.COMMANDS.CMD ID

WHERE    (dbo.ACTIVE.RECCLS = @CompDate)
    OR (@CompDate IS NULL AND dbo.ACTIVE.RECCLS IS NULL)
)

Notice: such function may suffer from parameter sniffing issue.

You have two syntax issues:

A difference in the name of the function and the calling command.
[dbo].[TVF_Dashboad] is not the same as TVF_DashBoard
dbo.ACTIVE. RECCLS there is a space after the dot

Also the default value is NULL and it will return all the rows according to the
WHERE    (dbo.ACTIVE. RECCLS = @CompDate)

Where dbo.ACTIVE.RECCLS has the same value as the parameter, meaning only NULL values


Insert and select the row autoincremented id What I want to do is insert a row and than select its auto incremented id number, In a multhi-threaded enviroment.

I know how to do it with a Stored Procedure.

Will this line of code work:

BEGIN TRANSACTION;
INSERT INTO table (r1) VALUES (...);
SELECT id FROM table ORDER BY id DESC LIMIT 1;
COMMIT;

And are there any other options?

No.  Instead:

INSERT ...
SELECT LAST_INSERT_ID();

LAST_INSERT_ID() is session-specific, so there is never any confusion with other threads also inserting into the same table.

Also, there is no need for BEGIN ... COMMIT for this pair of statements.  (You may need it if there are other statements.)

I have practiced that as follows:

BEGIN TRANSACTION;
INSERT INTO table (r1) VALUES (...);
SELECT id FROM table ORDER BY id DESC LIMIT 1;
COMMIT;

It gives the following result:

BEGIN
INSERT 0 1
id
----
  4
(1 row)

COMMIT


Deadlocks With Trigger Based Replication A table ORIGINAL exists with the following structure:

ID    VARCHAR Length 10 (key)
VALUE VARCHAR Length 10 (non-key)

A table REPLICATION exists with the following structure:

ID               VARCHAR Length 10 (key)
CHANGE_TIMESTAMP NUMBER  Length 15 (non-key)

I want to log every changed primary key to the REPLICATION table, with the latest change time stamp.

Therefore I have created this trigger in oracle:

CREATE OR REPLACE TRIGGER REPLICATION_TEST
  AFTER INSERT OR UPDATE ON ORIGINAL
  FOR EACH ROW

  DECLARE timestamp DEC(15);
  BEGIN
    SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'yyyymmddhh24miss'))
      INTO timestamp
      FROM dual;

    INSERT INTO REPLICATION
      FIELDS ("id", "change_timestamp")
      VALUES (:NEW."id", timestamp);
    EXCEPTION WHEN dup_val_on_index THEN
      UPDATE REPLICATION
        SET "change_timestamp" = timestamp
        WHERE "id" = :NEW."id"
END;

Functionally, this works just fine. But in a productive environment with multiple sessions where arbitrary data changes can happen at any time, this infrequently leads to deadlocks. Presumably because of the UPDATE statement.

An alternative approach would be to add the CHANGE_TIMESTAMP field as additional key field to do only INSERTS into the REPLICATION table and skip the UPDATE in case of duplicates. This would work functionally just fine, but would obviously result in much more data being produced which I'd like to avoid.

What else can I do?

Before rows  are updated in the REFERENCE table the corresponding rows must be updated in the ORIGINAL table. If a session waits for a lock of a row of the REFERENCE table to update it must get the lock of the corresponding ORIGINAL table row lock. But this is hold by the session that holds the REFERENCE table row lock.
I can't see any problem with this design, but I am not 100% sure. Nevertheless I think it i easier to read if you create an update statement that is triggered for updates and an insert statement that is triggered for inserts. But maybe this is a matter of taste.

I have limited knowledge of oracle internals, however, based on my experience of deadlocks you can create two separate triggers for Inserts / Update and do not check the "EXCEPTION WHEN dup_val_on_index". I think because of that check its holding lock on index longer.


How does one create a data base for a survey/questionnaire? I need to create a database for a wordy questionnaire. At the end I want to be able to graph my information. Where can I do this?


How do i crack this non-ansi code blob I've just started a new job about a month ago working for a software company that is really understaffed. I've been tasked with modernizing some of this old non-ansi SQL code and I'm having a particularly tough time trying to crack open this blerb. I'm recently out of school so I'm marginally familiar with modern SQL standards and from the research I've done the deprecated piece would relate to the non-ansi joins *= . I've tried pretty much every way I can think of altering this mess and I'm at a loss.
Thanks for your assistance.

Select @SQLCmd =
'SELECT ''' + @InvType + ''' AS Inv_Type, ' + @TableName + '_Number AS     Item_Number, Alternate_ID, Description1, ' +
    'Company_ID, Department_ID, Group_Id, Section_Id, Media_Code, ' +
    'Location_Current, Location_Home, Hold_Status, Record_Type,   Disposition_Status, ' + @TableName + '.Retention_Code, ' +
    'Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status ' +
'FROM ' + @TableName + ', RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS ' +
'Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV ' +
'WHERE ' + @TableName + '.Retention_Code *= RETENTION.Retention_Code AND ' +
    @TableName + '_Number *= RESV.Item_Number AND ''' + @InvType + ''' = RESV.Item_Type ' +
Case LTrim(@RetnType)
    When '' Then ''
    Else 'AND ' + @TableName + '.Retention_Type = ''' + @RetnType + ''''
End
+ ' AND ' + @WhereCond +
' UNION ' +
'SELECT ''' + @InvType + ''' AS Inv_Type, ' + @TableName + '_Number AS Item_Number, Alternate_ID, Description1, ' +
    'Company_ID, Department_ID, Group_Id, Section_Id, Media_Code, ' +
    'Location_Current, Location_Home, Hold_Status, Record_Type, Disposition_Status, ' + @TableName + '.Retention_Code, ' +
    'Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status ' +
'FROM ' + @TableName + ', RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS ' +
'Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV ' +
'WHERE ' + @TableName + '.Retention_Code *= RETENTION.Retention_Code AND ' +
    @TableName + '_Number *= RESV.Item_Number AND ''' + @InvType + ''' = RESV.Item_Type ' +
Case LTrim(@RetnType)
    When '' Then ''
    Else 'AND ' + @TableName + '.Retention_Type = ''' + @RetnType + ''''
End

Consider these two simple tables:

CREATE TABLE dbo.t1
(
    t1_ID int NOT NULL
);

CREATE TABLE dbo.t2
(
    t2_ID int NOT NULL
);

"old style" JOINs used in SQL Server 2000 and earlier, that look like this:

SELECT *
FROM dbo.t1
    , dbo.t2
WHERE t1.t1_ID *= t2.t2_ID;

Are equivalent to an ANSI-style LEFT JOIN:

SELECT *
FROM dbo.t1
    LEFT JOIN dbo.t2 ON t1.t1_ID = t2.t2_ID;

This:

SELECT *
FROM dbo.t1
    , dbo.t2
WHERE t1.t1_ID =* t2.t2_ID;

becomes:

SELECT *
FROM dbo.t1
    RIGHT JOIN dbo.t2 ON t1.t1_ID = t2.t2_ID;

Although, I'm not a fan of using RIGHT JOIN, and would rewrite this to its equivalent LEFT JOIN, as:

SELECT *
FROM dbo.t2
    LEFT JOIN dbo.t1 ON t2.t2_ID = t1.t1_ID;

The dynamic SQL in your question is complex enough that I'd first use PRINT to print out a sample of the generated code, then I'd use the above rules to convert the old style joins into ANSI-compliant ones.

I've mocked-up the following from your code, in an attempt to simplify it:

DECLARE @SQLCmd nvarchar(max);
DECLARE @InvType nvarchar(100) = 'InvType';
DECLARE @TableName nvarchar(128) = 'TableName';
DECLARE @RetnType nvarchar(100) = 'RetnType';
DECLARE @WhereCond nvarchar(1000) = 'WhereCond';

Select @SQLCmd =
'SELECT ''' + @InvType + ''' AS Inv_Type, ' + @TableName + '_Number AS     Item_Number, Alternate_ID, Description1,
    Company_ID, Department_ID, Group_Id, Section_Id, Media_Code,
    Location_Current, Location_Home, Hold_Status, Record_Type,   Disposition_Status, ' + @TableName + '.Retention_Code,
    Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status
FROM ' + @TableName + ', RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS
Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV
WHERE ' + @TableName + '.Retention_Code *= RETENTION.Retention_Code AND ' +
    @TableName + '_Number *= RESV.Item_Number AND ''' + @InvType + ''' = RESV.Item_Type
    ' +
Case LTrim(@RetnType)
    When '' Then ''
    Else 'AND ' + @TableName + '.Retention_Type = ''' + @RetnType + ''''
End
+ '
    AND ' + @WhereCond +
' UNION
SELECT ''' + @InvType + ''' AS Inv_Type, ' + @TableName + '_Number AS Item_Number, Alternate_ID, Description1,
    Company_ID, Department_ID, Group_Id, Section_Id, Media_Code,
    Location_Current, Location_Home, Hold_Status, Record_Type, Disposition_Status, ' + @TableName + '.Retention_Code,
    Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status
FROM ' + @TableName + ', RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS
Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV
WHERE ' + @TableName + '.Retention_Code *= RETENTION.Retention_Code AND
' + @TableName + '_Number *= RESV.Item_Number AND ''' + @InvType + ''' = RESV.Item_Type
' +
Case LTrim(@RetnType)
    When '' Then ''
    Else 'AND ' + @TableName + '.Retention_Type = ''' + @RetnType + ''''
End

PRINT @SQLCmd;

This results in:

SELECT 'InvType' AS Inv_Type, TableName_Number AS     Item_Number, Alternate_ID, Description1,
    Company_ID, Department_ID, Group_Id, Section_Id, Media_Code,
    Location_Current, Location_Home, Hold_Status, Record_Type,   Disposition_Status, TableName.Retention_Code,
    Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status
FROM TableName, RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS
Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV
WHERE TableName.Retention_Code *= RETENTION.Retention_Code AND TableName_Number *= RESV.Item_Number AND 'InvType' = RESV.Item_Type
    AND TableName.Retention_Type = 'RetnType'
    AND WhereCond UNION
SELECT 'InvType' AS Inv_Type, TableName_Number AS Item_Number, Alternate_ID, Description1,
    Company_ID, Department_ID, Group_Id, Section_Id, Media_Code,
    Location_Current, Location_Home, Hold_Status, Record_Type, Disposition_Status, TableName.Retention_Code,
    Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status
FROM TableName, RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS
Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV
WHERE TableName.Retention_Code *= RETENTION.Retention_Code AND
TableName_Number *= RESV.Item_Number AND 'InvType' = RESV.Item_Type
AND TableName.Retention_Type = 'RetnType'

Which I'd reformat so that each column is on a separate line:

SELECT 'InvType' AS Inv_Type
    , TableName_Number AS     Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , TableName.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status
FROM TableName
    , RETENTION
    , (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status
        FROM RESERVATION
        GROUP BY Item_Type, Item_Number
        ) AS RESV
WHERE TableName.Retention_Code *= RETENTION.Retention_Code
    AND TableName_Number *= RESV.Item_Number
    AND 'InvType' = RESV.Item_Type
    AND TableName.Retention_Type = 'RetnType'
    AND WhereCond
UNION
SELECT 'InvType' AS Inv_Type
    , TableName_Number AS Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , TableName.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status
FROM TableName
    , RETENTION
    , (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status
        FROM RESERVATION
        GROUP BY Item_Type
            , Item_Number
        ) AS RESV
WHERE TableName.Retention_Code *= RETENTION.Retention_Code
    AND TableName_Number *= RESV.Item_Number
    AND 'InvType' = RESV.Item_Type
    AND TableName.Retention_Type = 'RetnType'

A bit tedious, but now we can see what the query actually does.

Converting the "old-style" joins into ANSI-style joins results in:

SELECT 'InvType' AS Inv_Type
    , TableName_Number AS     Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , TableName.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status
FROM TableName
    LEFT JOIN RETENTION ON TableName.Retention_Code = RETENTION.Retention_Code
    LEFT JOIN (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status
        FROM RESERVATION
        GROUP BY Item_Type, Item_Number
        ) AS RESV ON TableName_Number = RESV.Item_Number
WHERE 'InvType' = RESV.Item_Type
    AND TableName.Retention_Type = 'RetnType'
    AND WhereCond
UNION
SELECT 'InvType' AS Inv_Type
    , TableName_Number AS Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , TableName.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status
FROM TableName
    LEFT JOIN RETENTION ON TableName.Retention_Code = RETENTION.Retention_Code
    LEFT JOIN (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status
        FROM RESERVATION
        GROUP BY Item_Type
            , Item_Number
        ) AS RESV ON TableName_Number = RESV.Item_Number
WHERE 'InvType' = RESV.Item_Type
    AND TableName.Retention_Type = 'RetnType'

Folding that back into dynamic SQL results in:

DECLARE @SQLCmd nvarchar(max);
DECLARE @TableName nvarchar(128);
DECLARE @InvType nvarchar(100);
DECLARE @RetnType nvarchar(100);
DECLARE @WhereCond nvarchar(1000);

SET @SQLCmd = 'SELECT ''' + @InvType + ''' AS Inv_Type
    , TableName_Number AS     Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , TableName.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status
FROM ' + @TableName + '
    LEFT JOIN RETENTION ON ' + @TableName + '.Retention_Code = RETENTION.Retention_Code
    LEFT JOIN (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status
        FROM RESERVATION
        GROUP BY Item_Type, Item_Number
        ) AS RESV ON TableName_Number = RESV.Item_Number
WHERE ' + @InvType + ' = RESV.Item_Type
    AND ' + @TableName + '.Retention_Type = ''' + @RetnType + '''
    AND ' + @WhereCond + '
UNION
SELECT ''' + @InvType + ''' AS Inv_Type
    , ' + @TableName + '_Number AS Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , ' + @TableName + '.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status
FROM ' + @TableName + '
    LEFT JOIN RETENTION ON ' + @TableName + '.Retention_Code = RETENTION.Retention_Code
    LEFT JOIN (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status
        FROM RESERVATION
        GROUP BY Item_Type
            , Item_Number
        ) AS RESV ON ' + @TableName + '_Number = RESV.Item_Number
WHERE ''' + @InvType + ''' = RESV.Item_Type
    AND TableName.Retention_Type = ''' + @RetnType + ''''

Looks like this to me:

SELECT @SQLCmd =
    'SELECT ''' + @InvType + ''' AS Inv_Type, ' + @TableName + '_Number AS Item_Number, Alternate_ID, Description1, ' +
        'Company_ID, Department_ID, Group_Id, Section_Id, Media_Code, ' +
        'Location_Current, Location_Home, Hold_Status, Record_Type,   Disposition_Status, ' + @TableName + '.Retention_Code, ' +
        'Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status ' +
    'FROM ' + @TableName +
    'LEFT OUTER JOIN RETENTION ON ' + @TableName + '.Retention_Code = RETENTION.Retention_Code' +
    'LEFT OUTER JOIN (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS Reservation_Status ' +
                        'FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV ' +
        'ON ' + @TableName + '.Retention_Code = RETENTION.Retention_Code ' +
    'WHERE ''' + @InvType + ''' = RESV.Item_Type ' +
        @TableName + '_Number *= RESV.Item_Number AND ''' + @InvType + ''' = RESV.Item_Type ' +
        CASE LTRIM(@RetnType)
            WHEN '' THEN ''
            ELSE 'AND ' + @TableName + '.Retention_Type = ''' + @RetnType + ''''
        END +
    ' AND ' + @WhereCond +
    ' UNION ' +
    'SELECT ''' + @InvType + ''' AS Inv_Type, ' + @TableName + '_Number AS Item_Number, Alternate_ID, Description1, ' +
        'Company_ID, Department_ID, Group_Id, Section_Id, Media_Code, ' +
        'Location_Current, Location_Home, Hold_Status, Record_Type, Disposition_Status, ' + @TableName + '.Retention_Code, ' +
        'Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status ' +
    'FROM ' + @TableName +
    'LEFT OUTER JOIN RETENTION ON ' + @TableName + '.Retention_Code = RETENTION.Retention_Code' +
    'LEFT OUTER JOIN (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS Reservation_Status ' +
                        'FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV ' +
        'ON ' + @TableName + '.Retention_Code = RETENTION.Retention_Code ' +
    'WHERE ''' + @InvType + ''' = RESV.Item_Type ' +
    CASE LTRIM(@RetnType)
        WHEN '' THEN ''
        ELSE 'AND ' + @TableName + '.Retention_Type = ''' + @RetnType + ''''
    END

Although I can't tell if this bit:

''' + @InvType + ''' = RESV.Item_Type ' +

is supposed to be part of the join or where condition.

A few things to note:

This is highly prone to SQL Injection attack. The @TableName variable, for instance, could be used to do some nasty stuff, if a malicious user wanted to. (imagine passing in '; DROP TABLE Customers' or something similar).
There is probably a better way of doing this in terms of performance. See if you can put this into a stored procedure and use parameters for where statements
This is hard to debug, make it easier for the future by finding a better way! Stored procs/views etc... not building SQL on the fly.


Can I add a unique constraint that ignores existing violations? I have a table which currently has duplicate values in a column.

I cannot remove these erroneous duplicates but I would like to prevent additional non-unique values from being added.

Can I create a UNIQUE that doesn't check for existing compliance?

I have tried using NOCHECK but was unsuccessful.

In this case I have a table which ties licensing information to "CompanyName"

EDIT: Having multiple rows with the same "CompanyName" is bad data, but we can't remove or update those duplicates at this time. One approach is to have the INSERTs use a stored procedure which will fail for duplicates... If it was possible to have SQL check the uniqueness on its own, that would be preferable.

This data is queried by company name. For the few existing duplicates this will mean that multiple rows are returned and displayed... While this is wrong, it's acceptable in our use case. The goal is to prevent it in the future. It seems to me from the comments that I have to do this logic in the stored procedures.

The answer is "yes".  You can do this with a filtered index (see here for documentation).

For instance, you can do:

create unique index t_col on t(col) where id > 1000;

This creates a unique index, only on new rows, rather than on the old rows.  This particular formulation would allow duplicates with existing values.

If you have just a handful of duplicates, you could do something like:

create unique index t_col on t(col) where id not in (<list of ids for duplicate values here>);

Another alternative is to write a scalar function that checks if a value already exists in the table and then call that function from a check constraint.

This will do horrible things to performance.


Slow select Queries on Log shipping standby server We have a database in primary DC around 15 TB with partitioned tables with largest one around 7 TB.

To avoid users from reporting a LS set up was done for above database in different DC where the LS restore job runs once nightly.

on Primay- update stats and update stats AYNC are both set to True and hence reflected the same on secondary. I am not sure why someone kept both enabled and is that how it should be?

Now on secondary, i see lots of blocking occurring when a SELECT query runs from users results in select statman statement for auto update stats for that read only database.

  I am not able to get why there would be one when auto update async is
  enabled?

  Also, will the update stats done on primary (once a week) be restored during
  LS restore? if not, whats the better way to run update stats on
  secondary database involved in LS standby mode, may be to run daily for better select performance out there?

Please advise

Statistics in read only databases.

TL;DR

In Read only databases, regular permanent statistics can be leveraged to satisfy query plans. If sql server needs new statistics for the read only db  or these permanent statistics in the database have become stale, then temporary statistics can be created / updated. These statistics reside in TempDB and are managed by SQL Server (you can only drop them).

There are two kinds of temporary statistics, these that get created due to missing statistics and those that get 'updated'.

The creation of temporary statistics can be removed by adding the statistics manually on the primary database, or generating the estimated execution plans of the reporting queries (also on the primary instance/db). More on that further in this answer.

Permanent statistics can be updated and 'transformed' to temporary statistics on a read only database.

The temporary stat updates can be resolved by either updating your statistics on the primary db more often or disabling the auto stat updates on the read only db after log restores are done.

To disable the auto update stats you could execute this on the read only database: ALTER DATABASE [Database] SET AUTO_UPDATE_STATISTICS OFF; This will stop the updates of temporary statistics on this Read only database.

Another important part regarding Restores to a standby database & temporary statistics is that when applying log backups, the temporary statistics get  updated again, even though they still  exist in sys.stats.

In your case explaining the daily issues with Statman queries (recalculating the temp statistics each day if they need to be created / updated).

Norecovery --> Standby --> Norecovery ... removes temporary statistics

Another interesting part regarding temporary statistics is that they will be gone when the db state changes to restoring using RESTORE DATABASE ... WITH NORECOVERY.

use MASTER
GO
RESTORE DATABASE [ReadOnly2] with NORECOVERY
RESTORE DATABASE [ReadOnly2] WITH STANDBY = 'D:\temp\ReadOnly_Standby.bak'

Effectively flushing the temporary statistics on all objects

SELECT * From sys.stats where is_temporary = 1;

And recalculating the same 2 stats between each state change + test query running.

Both of these permanent statistics show up in the xml of our execution plan

   <StatisticsInfo Database="[ReadOnly2]" Schema="[dbo]" Table="[Bla]" Statistics="[IX_Bla_indexedval]" ModificationCount="12000000" SamplingPercent="15.8812" LastUpdate="2019-06-12T10:52:32.25" />
      <StatisticsInfo Database="[ReadOnly2]" Schema="[dbo]" Table="[Bla]" Statistics="[PK__Bla__3214EC075017BD54]" ModificationCount="12000000" SamplingPercent="15.2345" LastUpdate="2019-06-12T10:52:35.34" />

With the modificationcount, samplingpercent & lastupdate changed after executing the query again with the temporary statistics 'updated'.

ModificationCount="0" SamplingPercent="5.71018" LastUpdate="2019-06-13T11:32:36.5"

Creation of temporary statistics

Regular, non temporary statistics do not get updated / you cannot update (not even temporary) statistics  on read only databases.

What you are seeing on the reporting instance is the creation / 'updating' of temporary statistics.

These statistics reside in TempDB & SQL Server Creates & Updates them.

Replicating the behaviour

I was able to replicate the behaviour of temporary statistics creation on a table with 100M rows in a read only database (Addendum #1)

With the troublesome StatMan Queries you mentioned.

SELECT StatMan([SC0], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000]  FROM (SELECT [NonIndexedVal] AS [SC0] FROM [dbo].[Bla] TABLESAMPLE SYSTEM (7.707678e-001 PERCENT) WITH (READUNCOMMITTED) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SB0000] ) AS _MS_UPDSTATS_TBL  OPTION (MAXDOP 16)

Maxdop 16 (max cores) due to me having MAXDOP set as 0 on my test machine, YMMV

Restarting the instance

When I restart the instance I see the same behaviour, showing that these are in fact, temporary statistics. Some Q&A on temporary statistics can be found here.

Now what we are seeing is the creation of temporary statistics and they get created before the query runs.

For big tables, they do note that sample rate is still applied to temporary statistics.

  Other point to note is that statistics created as part of auto-stats
  use data sampling so the creation of these statistics is fast and does
  not depend on the size of the table

Source

How could you resolve the creation of the statistics when sql server restarts / the database is restored?

If it is possible to do so, you could script them out and create them on the main 'primary' database.

Finding the temporary statistics

SELECT OBJECT_ID, name, auto_created,
user_created, is_temporary
FROM sys.stats
WHERE is_temporary = 1;

Scripting the statistics

Without T-SQL
Or by using the T-SQL answer on the question How to script statistics in Sql Server? (using T-SQL) By Martin Smith

Which could solve your main issue with the statistics being created time and time again.

Another idea if the issue is due to temp stats being created & you cannot script them out correctly, could be creating the estimated execution plans for the reporting queries on the primary database. This should create the statistics needed when auto create statistics = on.

Temporary statistic updates

The other problem that could arise are stale permanent statistics.
As noted in this aforementioned blog, stale permanent statistics can be updated and set to is_temporary=1.

This means that permanent statistics on a read only database can become temporary statistics until the instance is restarted. When you update the statistics on the primary it should get carried over to the secondary when the log is applied.

Asynchronous temporary statistic updates

We see the asynchronous statistic updates also working for these temporary statistics!

After running addendum #1, we run the next snippet:

   USE MASTER
GO
ALTER DATABASE [ReadOnly] SET  READ_WRITE;

ALTER DATABASE [ReadOnly] SET AUTO_UPDATE_STATISTICS OFF

USE [ReadOnly]
GO
INSERT INTO dbo.Bla WITH(TABLOCK)(Indexedval,NonIndexedVal)
SELECT TOP(10000000) --10M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum1,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum2
FROM master..spt_values spt1
CROSS JOIN master..spt_values spt2
CROSS JOIN master..spt_values spt3;

ALTER DATABASE [ReadOnly] SET AUTO_UPDATE_STATISTICS ON

USE MASTER
GO
ALTER DATABASE [ReadOnly] SET  READ_ONLY;
SELECT Indexedval
FROM dbo.Bla
WHERE  Indexedval =999999
AND 1= (SELECT 1);

The above SELECT runs instantly.

After running the query, the aftermath is shown.

The temp statistic updates run after query execution.

SELECT StatMan([SC0]) FROM (SELECT TOP 100 PERCENT [Indexedval] AS [SC0] FROM [dbo].[Bla] WITH (READUNCOMMITTED)  ORDER BY [SC0] ) AS _MS_UPDSTATS_TBL  OPTION (MAXDOP 1)

Disabling auto update statistics on read only databases

You could disable the updating of permanent statistics to temporary statistics by running the next statement, you can change this setting on a read only database and it will still work.

ALTER DATABASE [ReadOnly] SET AUTO_UPDATE_STATISTICS OFF;

Solution for the temporary stat upgrades.

  Also, will the update stats done on primary (once a week) be restored
  during LS restore?

Updating your stats will be brought over to the secondary, updating them more where possible will result in less stale statistics and less stale permanent statistics being converted to temporary statistics.

Looking into both of these would be my go to in tackling this problem.

Disabling temporary statistics by using a traceflag

When massively enabling traceflags to help a colleague and digging deeper between 2 and 3 thousand, it appears that traceflag 2362 can be used to disable temporary statistics.

You can enable them like this:

DBCC TRACEON(2362,-1);

And all newly temp stats will not be created. Existing temporary statistics will remain until they are removed. For example by setting the db offline and online again.

Applying log backups to a standby database & Temporary statistics

Addendum 2

When running the queries in addendum #2, and applying log backups to the standby database the stats get updated after each restore.

Even after applying an 'empty' log backup.

In addendum 2, between each log backup restore the following query runs:

SELECT Indexedval
FROM dbo.Bla
WHERE  Indexedval =999999
AND 1= (SELECT 1);

These trigger temp stat updates each time.

Proof

All this means that applying logs at night will make the temp stat updates run each day, without a restart of the instance.

Resolving this issue

set auto update stats off on the standby db after applying logs. ALTER DATABASE [ReadOnly2] SET AUTO_UPDATE_STATISTICS OFF;
Run your reporting queries when the restores finish to create the temp stats
Update the statistics on your primary database more often before
applying the logs.
Enabling traceflag 2362

When restoring a log backup they still exist:

SELECT name, is_temporary From sys.stats where is_temporary = 1;

name    is_temporary
PK__Bla__3214EC075017BD54   1
IX_Bla_indexedval   1

But they do get recalculated when rerunning the query.

Addendum #1 (Table with 100M records in a read only db.)

CREATE DATABASE [ReadOnly]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'ReadOnly', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL02\MSSQL\DATA\ReadOnly.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON
( NAME = N'ReadOnly_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL02\MSSQL\DATA\ReadOnly_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [ReadOnly] SET COMPATIBILITY_LEVEL = 140
ALTER DATABASE [ReadOnly] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF)
ALTER DATABASE [ReadOnly] SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE [ReadOnly] SET AUTO_UPDATE_STATISTICS_ASYNC ON
ALTER DATABASE [ReadOnly] SET  READ_WRITE
ALTER DATABASE [ReadOnly] SET RECOVERY SIMPLE
ALTER DATABASE [ReadOnly] SET  MULTI_USER
ALTER DATABASE [ReadOnly] SET PAGE_VERIFY CHECKSUM

USE [ReadOnly]
GO
CREATE TABLE dbo.Bla(Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL, Indexedval INT,NonIndexedVal INT);
CREATE INDEX IX_Bla_indexedval on dbo.Bla(Indexedval);

INSERT INTO dbo.Bla WITH(TABLOCK)(Indexedval,NonIndexedVal)
SELECT TOP(10000000) --10M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum1,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum2
FROM master..spt_values spt1
CROSS JOIN master..spt_values spt2
CROSS JOIN master..spt_values spt3;
GO 10

USE MASTER
GO
ALTER DATABASE [ReadOnly] SET  READ_ONLY;

USE [ReadOnly]
GO
SELECT NonIndexedVal
FROM dbo.Bla
WHERE  NonIndexedVal = 999999;

#Addendum 2

ALTER DATABASE [ReadOnly] SET  READ_WRITE;

ALTER DATABASE [ReadOnly] SET RECOVERY FULL

BACKUP DATABASE [ReadOnly] to disk = 'D:\temp\ReadOnly.bak'
WITH COMPRESSION, STATS=5

RESTORE FILELISTONLY FROM DISK = 'D:\temp\ReadOnly.bak'

RESTORE DATABASE [ReadOnly2] FROM disk = 'D:\temp\ReadOnly.bak'
WITH MOVE  'ReadOnly' to 'D:\temp\ReadOnly2.mdf'
,MOVE 'ReadOnly_log' to 'F:\temp\ReadOnly_log2.ldf'
, STANDBY = 'D:\temp\ReadOnly_Standby.bak'

USE [ReadOnly2]
GO
ALTER DATABASE [ReadOnly2] SET AUTO_UPDATE_STATISTICS ON

SELECT Indexedval
FROM dbo.Bla
WHERE  Indexedval =999999
AND 1= (SELECT 1);

USE [ReadOnly]

INSERT INTO dbo.Bla WITH(TABLOCK)(Indexedval,NonIndexedVal)
SELECT TOP(2000000) --2M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum1,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum2
FROM master..spt_values spt1
CROSS JOIN master..spt_values spt2
CROSS JOIN master..spt_values spt3;

USE MASTER
GO
BACKUP LOG [ReadOnly] to disk = 'D:\temp\ReadOnlyLog.trn'
WITH COMPRESSION, STATS=5

RESTORE LOG [ReadOnly2] FROM DISK='D:\temp\ReadOnlyLog.trn'
WITH STANDBY = 'D:\temp\ReadOnly_Standby.bak'

USE [ReadOnly2]
SELECT Indexedval
FROM dbo.Bla
WHERE  Indexedval =999999
AND 1= (SELECT 1);

BACKUP LOG [ReadOnly] to disk = 'D:\temp\ReadOnlyLog2.trn'
WITH COMPRESSION, STATS=5

RESTORE LOG [ReadOnly2] FROM DISK='D:\temp\ReadOnlyLog2.trn'
WITH STANDBY = 'D:\temp\ReadOnly_Standby.bak'

USE [ReadOnly2]
SELECT Indexedval
FROM dbo.Bla
WHERE  Indexedval =999999
AND 1= (SELECT 1);
SELECT * From sys.stats where is_temporary = 1

A secondary database server was set up for reporting so that the production server wasn't dealing with that traffic, great!

Auto-updating statistics is still a debate-ridden topic. (Keep reading)
If you manually update the statistics after your nightly log shipping/restore/update process, then you may be able to turn both of those options off. If your data changes throughout the day, I would leave auto-update on, but turn off asynchronous.

I've learned from a few of the lurkers here, that have blogs elsewhere, that the asynchronous setting is only "useful" when auto-updating stats will delay queries longer than it's worth spending the time to update. MSSQL will then try to run the stats update whenever is most convenient for the load of the server, so that it's not as intrusive. Mind you, this means that bad stats may be used in queries until MSSQL decides to complete them.


copy table with all constraint how I would copy a table with all constraints?

I run select * into myNewTable
from baseTable
where 1=0

This copy a table but no with constraints

You have to use the GUI management tool to create a script and then run that - you cannot do it directly - take a look here (or here) for relatively clear explanations.


How to grant or allow a particular SQL Server Login/User to always view a Column Level Encrypted data? I used Column Level Encryption to encrypt one of the columns in my SQL Server database. Now everytime I want to SELECT to see that encrypted data, I have to open the symmetric key to decrypt the column and then close it.

I want to a particular user or login to always view the encrypted data. Is it possible in Column Level Encryption, if yes, how?

There is no out of the box way to automatically open a key for a particular user. You could create a logon trigger that opens the symmetric key. Something like this:

CREATE TRIGGER open_sec_key
ON ALL SERVER
FOR LOGON
AS
BEGIN
  IF ORIGINAL_LOGIN() = '<user>'
   OPEN SYMMETRIC KEY [key_name] DECRYPTION BY CERTIFICATE [cert_name];
END;

Logon Triggers.

Try the following command if you want specific user to access data on specific database

use [mydb]

GRANT VIEW Definition TO User1


Undo tablespace full, how to free it My undo tablespace is full after some modifications. Las column is % of space used.

xxx_undo_01.dbf UNDO    2147483648  16777216    2048    2032    16  0,78    99,22

Now i cannot execute any query according to this. How can i free the undo tablespace ?

Thanks in advance.

You can execute below query.

DROP TABLESPACE xxx_undo_01 INCLUDING CONTENTS AND DATAFILES;


How to make postgresql SUM to be more accurate on large amount of floating point data? I'm trying to SUM 8 million floating point (REAL) values with simple query like this:

SELECT SUM(metric) FROM metrics;

However, it returns very inaccurate result.
It should return 137,586.77, but it returns 137,303 (283.77 difference)

Is there a way to force a query to be more precise?

According to the documentation of the REAL datatype http://www.postgresql.org/docs/9.2/static/datatype-numeric.html#DATATYPE-FLOAT it has a precision of 6 decimal digits. That's what you are seeing. If you need more precision you need to use double or a number with fixed precision.

Maybe casting the reals to something more precise and summing the result will give you what you need, eg:

SELECT SUM(metric::double) FROM metrics;


MySQL varchar(255) limitation & Anomaly I have defined a column in my MySQL database as :

ALTER TABLE `my_table` CHANGE `desc` `desc` VARCHAR( 255 )
    CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL

However, when I enter text that is longer than 233 characters from my front end the data is truncated.

Changing the size of the column to varchar(511) has made no difference.

I counted the number of characters using PHP's strlen() function and it revealed 233 characters.

Why is MySQL doing this, and how can I save the full string?

MySQL is not cutting it at 233. The problem is likely in your save method which cuts it to 233 before the data even reaches MySQL.

Also, don't forget that 233 limit is not character limit, and as some character might need more han 1 byte to be stored, you might see less than 233 characters.

Also please make sure that data in MySQL server is really stored as latin1, this can be accomplished with:

show variables like 'char%';

255 is used because it's the largest number of characters that can be counted with an 8-bit number. It maximizes the use of the 8-bit count, without frivolously requiring another whole byte to count the characters above 255.

When used this way, VarChar only uses the number of bytes + 1 to store your text, so you might as well set it to 255, unless you want a hard limit (like 50) on the number of characters in the field.


Sql server sqllogship.exe...full docs and/or source code? Are the internals and all the logic of sqllogship documented?

Eg so i can manage it directly, without the wizard? Or replace it with my own tool? (For example, to rewrite it in powershell or C# -- the goal being to instrument it more fully and allow for different transports for the logs [other than a file share].)

I find this syntax (which is real, from a live SQL 2014 instance, but created by the log ship wiz) to be impossible to fully parse:

"C:\Program Files\Microsoft SQL Server\120\Tools\Binn\sqllogship.exe" -Backup DB46C321-3453-48FC-9B20-C1823E2B534A -server WIN-2OOC0JPLR5K

What does the guid point to?

(Would be awesome if this were open sourced... Or better yet, ported to powershell and offered to the community...)

I am not sure of any other tool which you are referring to, but i prefer to use this sql logshipping app and yes its documented, refer to below article!

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

Not surprisingly, the sqllogship utility is specific to log shipping, which must be enabled and working in order to use the tool. The utility lets you perform copy, restore, and backup operations for an existing log shipping configuration.


How to read MongoDB .mdmp files My MongoDB 3.6.2 craches randomly.

It provides .mdmp files in the location where I installed MongoDB. The only problem is I don't know how to read these binary files.

mongod.2017-12-21T08-01-49.mdmp

Does anyone knows how to read these files?

You might want to consider opening an issue on MongoDB's Issue Tracker and providing the *.mdmp file in the issue.

*.mdmp files are considered to be Microsoft Dump Files.

  Compressed file created by Windows after a program error or crash; contains data "dumped" from the program's memory space; saves information about the error, which can be used for debugging; commonly created along with an uncompressed .HDMP file and can be sent to Microsoft as part of a bug reporting process.

Reference: .MDMP File Extension (fileinfo.com)

If you have a Microsoft debugger and the symbols for the relevant progams, then you can have a look at the dump file yourself:

  A dump file is a snapshot of an app at the point in time the dump is taken. It shows what process was executing and what modules were loaded. If the dump was saved with heap information, the dump file contains a snapshot of what was in the app's memory at that point in time. Opening a dump file with a heap in Visual Studio is like stopping at a breakpoint in a debug session. Although you cannot continue execution, you can examine the stacks, threads, and variable values of the app at the time the dump occurred.

But be aware:

  Debugging dump files of optimized code can be confusing. For example, compiler inlining of functions can result in unexpected call stacks and other optimizations might change the lifetime of variables.

Debugging a kernel-mode dump file requires you to...

  To debug kernel-mode dump files in Visual Studio 2013, download the Windows 8.1 Version of Debugging Tools for Windows. See Kernel Debugging in Visual Studio.

Reference: Using Dump Files (Microsoft MSDN)

There are some third-party tool from NirSoft that might help in finding the culprit.

And then there is the following list that tells you which tools could possibly open the *.mdmp file for you:

.mdmp Extension - List of programs that can open .mdmp files (Extension NirSoft)

  Disclaimer:
  I am in no way affiliated with NirSoft. I only use their software.

What is an MDMP file?

I am sure you are doing mistake, as (MDMP) extension file is not supported to MongoDB. As per MSDN BOL Here MDMP file is Compressed file created by Windows after a program error or crash; contains data "dumped" from the program's memory space; saves information about the error, which can be used for debugging; commonly created along with an uncompressed .HDMP file and can be sent to Microsoft as part of a bug reporting process.

Compressed Heap Dump files are saved as Minidump (.MDMP) files and may be sent to Microsoft as part of an error reporting and bug fixing process.

Windows Error Reporting settings are stored in the Windows Registry at "HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\Windows Error Reporting."

NOTE: You typically should not open HDMP files unless diagnosing a program crash in a controlled software environment.

Note: - MongoDB supports JavaScript Object Notation (JSON) and Binary JSON (BSON) format only.BSON extends the JSON model to provide additional data types, ordered fields, and to be efficient for encoding and decoding within different languages.

For further your ref Here and Here


sql error on script execution. try deleting repository before creating repository I want to migrate my mysql databse to oracle.
I have followed the migration demo from this url http://www.oracle.com/technetwork/database/migration/connect-step-mysql-1946352.html

but I got this message when try to associate migration repository

sql error on script execution. try deleting repository before creating repository

I am using
Oracle Sql Developer v 4.1.1.19Oracle express edition 11g releas 2

see the picture bellow what I have tried step by step

step1: Creating a user named migration_repo

step 2: Creating connection named migration_repo using migration_repo user

step3: try to associate migration repository

step4: executing the command

but after few seconds I got this error message.

Now. how can I solve this? what I have done wrong?

It seems that the video tutorial missed a grant, the correct command is the following:

GRANT
    CONNECT,
    RESOURCE,
    CREATE SESSION,
    CREATE VIEW,
    CREATE MATERIALIZED VIEW
TO migration_repo
IDENTIFIED BY migration_repo;

The database must also have the USERS tablespace.
I found the previous information in this thread.

alter user mwrep
  quota unlimited on users;


Ensure a Row is unique on insert SQL Server simple problem - ensure that only 1 batch is active at one time. Each batch has an assigned BatchID# - however that ID# might not be unique if a batch is re-run at some point. However, for a given ID# there should only ever be one "active" batch - that is only one batch with BatchComplete equal to NULL.

So the table I have is:

CREATE TABLE BatchTable (
     BatchNumber INT IDENTITY(1,1) PRIMARY KEY
     ,BatchID INT NOT NULL
     ,BatchStart DATETIME2(2) NOT NULL DEFAULT GETDATE()
     ,BatchComplete DATETIME2(2) NULL
     ,BatchLastOperation DATETIME2(2) DEFAULT GETDATE()
     BatchState INT NOT NULL
);

Then I have a constraint:

ALTER TABLE [dbo].[BatchTable]
ADD CONSTRAINT bt_OnlyOneBatchNumber UNIQUE (BatchID, BatchComplete)
GO

But last night this failed -- two inserts occurred in the same moment and both were inserted - chaos ensued.

I am guessing the problem was that ("20160619", NULL) != ("20160619", NULL) and therefore the rows were seen as unique. So the solution would be to create a non-null "IsComplete" column rather than relying upon the existence of the datetime in BatchComplete column.

So my questions are:

Is my guess correct?
Is there a better way? Surely this kind of pattern has to be pretty common (I have done something similar before though I used triggers rather than a constraint).

The BatchState might work rather than IsCompete -- but the constraint would need to be for a given BatchID all other rows must have a BatchState greater or equal to 4. Not sure how to make that constraint.

I don't have access to SQL Server so I googled the syntax for generated columns, but there's probably syntax errors in here anyhow. The idea is to use a generated column that has a unique value (BatchNumber) whenever BatchComplete is not null, and a non-unique number (BatchId) otherwise:

CREATE TABLE BatchTable
( BatchNumber INT IDENTITY(1,1) PRIMARY KEY
, BatchID INT NOT NULL
, BatchComplete DATETIME2(2) NULL
, Active AS (CASE WHEN BatchComplete IS NULL
                  THEN BatchID
                  ELSE BatchNumber
             END) PERSISTED
, CONSTRAINT ONLY_ONE_ACTIVE_AT_ATIME UNIQUE ( BatchID, Active )
);

If BatchComplete IS NULL then UNIQUE (BatchId, BatchId) must hold, i.e only one Active batch at a time.

If BatchComplete IS NOT NULL then UNIQUE (BatchId, BatchNumber) must hold, but BatchNumber itself is unique, so this will always be true.

As mentioned I can't test this, but you might be able to use the idea one way or another.

You can Use ajax request for checking and in codeigniter has built in function Is_Unique[databse.table_name]


Advice on choosing a primary key I will have to do this process in EXCEL first before it is made into a database. I will have many files, but there is about 7 - 9 files that work all together as one e.g.

X192AD1-test.docx
X192AD1-test.avi
X192AD1-test.mpg
X192AD1-test-small.jpg
X192AD1-test-preview.jpg
X192AD1-test-pdf

All the above works as ONE file.

I do not want to use a numbering system, e.g. 00001, 00002, 00003 because people can determine the age of the files, 00001 is older than 000032.

Can anyone give me some advice regarding this because of course I cannot repeat the primary key and I am not sure how to approach this.

You need 2 tables: one called say, Fileset and the other FilesetFile

Fileset contains information about the group: source, owner, who and when etc
FilesetFile contains a foreign key to Fileset.

Example:

Fileset

FilesetID, PK
FilesetOwner
FilesetAddedBy
...

FilesetFile

FilesetID, PK, FK
FileSuffix, PK
FileContent (BLOB column say)
...

You can then create a computed column "FileName" that concatenates FilesetID and FileSuffix to generate the pattern you need above

Trying this with one table will end in failure.

Create a UUID for each entry, checking for duplicates on insert.


Replication with only update and insert I have set up my replication but I want to delete some outdated data from my master tables. Is it possible that say if I have 10 records at master and I delete 5 of them but I want those 10 records to remain on the slave?

If you're just going to delete this extra data manually on the odd occasion, you can simply turn off the binary logging for the current session. On the MySQL prompt run the following:

set sql_log_bin=0;

Then any query you enter won't be sent to the binary log, and thus won't be sent across to the slaves.

Once you're finished, re-activate binary logging with

set sql_log_bin=1;

Reference: MySQL manual

Solution for PostgreSQL:

 We can avoid using rules

 - Stop slave daemons
 - Create ON DELETE and ON UPDATE DO INSTEAD NOTHING

    CREATE RULE void_delete AS ON DELETE TO reptest DO INSTEAD NOTHING

 - rule Apply RULE on table

    ALTER TABLE reptest ENABLE REPLICA RULE void_delete;

 Now, you can notice below that DELETE has no impact on Slave Node

EDIT

For MySQL we can create Trigger like:

DELIMITER $$

CREATE TRIGGER PreventDeletion
BEFORE DELETE ON table1
FOR EACH ROW
BEGIN

  IF old.id IN (1,2) THEN -- Will only abort deletion for specified IDs
    SIGNAL SQLSTATE '45000' -- "unhandled user-defined exception"
      -- Here comes your custom error message that will be returned by MySQL
      SET MESSAGE_TEXT = 'This record is sacred! You are not allowed to remove it!!';
  END IF;

END
$$

DELIMITER ;