No SA password. No SQL Server Management Studio. No OS authentication I have this problem.

I need to do some administrative tasks on a MS SQL Database using SA account.

OS authentication is not set.
SQL Server Manager Manager Studio is not installed.
I have no other admin database account.
I do have access to a Windows admin account
The only tools installed on the database server are as shown:

How can I activate OS authentication so I can log into the database an reset the SA password ?

Well, you can start by installing Management Studio. (How else do you plan to perform maintenance tasks once you have access to the instance?) I recommend the most recent version of Management Studio which is fully functional, manages 2005 instances just fine, and is completely free of any licensing requirements. You can download from here:

Download SQL Server Management Studio

(If your operating system is 32-bit, you should fix that first; if you can't, you can download the 32-bit version of SSMS 2014 SP1 toward the bottom of this page.)
Once you have Management Studio installed, what you do next will depend on your SQL Server version, as well as whether you can afford to take the SQL Server service down in order to establish new credentials. The first two methods here do not require restarting the instance:

For SQL Server 2005, 2008, and 2008 R2 instances
You can connect using the NT AUTHORITY\SYSTEM account (or other backdoor methods). There are some details in some of the answers here:

How to add sysadmin to user in SQL Server 2008 when no sysadmin accounts exist

I also have a tip over on MSSQLTips.com that addresses this problem:

Recover access to a SQL Server instance

Essentially, you download PSExec from Microsoft, then use it to launch Management Studio once you have it installed:
PsExec -s -i "C:\...\Ssms.exe"

This will connect as NT AUTHORITY\SYSTEM and will allow you to do things in Object Explorer, like:

Change the instance to SQL Server and Windows Authentication mode - right-click the Server name, hit properties, and change the radio button if it's currently set to Windows only:

Set the password for the sa account - expand Security, expand Logins, right-click sa and hit Properties, and in the resulting dialog there will be two password entry fields:

Add your own login as a sysadmin - right-click Logins, New Login... enter your login name (in the form DOMAIN\username) then move to the Server Roles tab and check the sysadmin box and click OK:

(or, if your login is already listed, right-click, Properties, and make sure sysadmin is checked under Server Roles)

For SQL Server 2012 and newer instances
Starting with SQL Server 2012, NT Authority\SYSTEM was no longer given rights to SQL Server by default. So another way to do that in these newer versions has been detailed by Argenis Fernandez:

If the SQL VSS Writer service is running, stop it, and suspend all maintenance plans or 3rd party backup software that might rely on it.

Open regedit.exe and change the value of HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\SQLWriter\ImagePath to point to SQLCMD.exe, which is going to be in C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\**<...110|120|130|140...>**\Tools\Binn. After editing, the registry value should look something like the following (sorry for the scrolling):
"C:Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\SQLCMD.exe" -S .\instancename -E -Q "ALTER ROLE sysadmin ADD MEMBER [YourDomain\YourUserName];"

Try to start the SQL VSS Writer service again (you'll get an error; that's okay).

You should now be able to connect as sysadmin using YourDomain\YourUserName. So stop the SQL VSS Writer service, fix the registry, and restart the service (if you need it to be running, or if it was running before you started this).

I've gone through this in a lot more detail in a second tip:

More on Recovering Access to a SQL Server Instance

Though when I wrote that tip I used a more cumbersome approach of making a copy of SQLCMD.exe and replacing sqlwriter.exe - much easier to just point the service at SQLCMD.exe directly.

If you can afford to take the SQL Server service down
There is an officially supported path from Microsoft that requires restarting the instance in single user mode:

Connect to SQL Server When System Administrators Are Locked Out

There is also a function in dbatools.io, a Powershell solution for managing SQL Server, called Reset-DbaAdmin:

https://dbatools.io/functions/other/reset-dbaadmin/

Security is not the main issue here
I see plenty of people calling for Microsoft to "fix" these so-called "vulnerabilities." These are valid approaches to recovering access to an instance of SQL Server that you rightfully own. They all require elevated privileges on the physical host where SQL Server resides; as I've said to several people, if you don't want developers messing with SQL Server installations, don't make them administrators.


How can I speed up an ASC sort on a column that only holds an integer between 0 and 9 across multiple millions of rows? CREATE TABLE foo (
  id  TEXT    NOT NULL,
  bar INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

CREATE INDEX foo_bar_idx ON foo(bar) WHERE NOT bar = 0;

I can sort on the bar column in DESC order about 3 times faster than I can sort on the bar column in ASC order. I assume an index on the column isn't helping with the sort since, relatively speaking w/ millions of rows, most of the values are the same.

The default value for the bar column is 0, but I only care about rows that have a value between 1 and 9, so my query includes a where clause for that. Not sure if the partial index is helping.

SELECT id FROM foo WHERE bar > 0 ORDER BY bar DESC;
SELECT id FROM foo WHERE bar > 0 ORDER BY bar ASC;

Across 9M rows, explain analize shows the ORDER BY DESC taking less than a second, and ORDER BY ASC taking a little over 3 seconds. Approximately 117K of the 9M rows get filtered by the WHERE clause.

Is the ORDER BY DESC faster because it doesn't necessarily have to worry about the 0 values? Any way to speed up the ORDER BY ASC query? Or... should I just be happy w/ a 3 second query time for a table w/ 9M rows in it? I'm continually adding rows, so I'm worried about the query time increasing over time...

I would recommend using the same syntax for all of your WHERE clauses (your index would build for negative values of bar, but you never SELECT those)

CREATE INDEX has an implicit order of ASC, so it unlikely that not specifying the indexing order alone is the source of your problem (as you stated the DESC sort is faster).

Naively, I'd also recommend that you include the id column in your index, but this may negatively affect insert/index rebuild performance, as well as overall disk and memory usage.

CREATE INDEX foo_bar_idx
  ON foo(bar ASC, id ASC)
  WHERE bar > 0

You could try converting all zeros to NULL (and setting NULL as the default), then changing your WHERE clauses to WHERE bar IS NOT NULL. So your create table code would look like

CREATE TABLE foo (
  id TEXT NOT NULL,
  bar INTEGER NULL DEFAULT NULL,
  PRIMARY KEY (id)
);

Once you have NULLs established, you could alternatively play around with the NULLS FIRST / LAST parameters in your index, rather than using a WHERE clause, but that is unlikely to improve performance.


Is parallel execution enabled or disabled by default in Oracle Standard Edition database version 12.1? Oracle Standard Edition 12.1 on Red Hat Linux 7

According to the documentation, parallel execution is enabled by default in Oracle SE 12.1.  However, when looking at the database parameters, the following exists:

PARAMETER              VALUE
---------              -----
Parallel execution     FALSE

Is there an error in the documentation?  Shouldn't this be set to TRUE.
What does this parameter refer to if not for enabling parallel execution?
If it needs to be set to TRUE, how to do so?

You say that VLDB and Partitioning Guide, Release 12.2

  About Initializing and Tuning Parameters for Parallel Execution

  Oracle Database computes defaults for the parallel execution parameters based on the
  value at database startup of CPU_COUNT and PARALLEL_THREADS_PER_CPU.

Parameters are described in the Database Reference.

But a lot of features are not possible in the Standard Edition. Either they cannot be enabled or are not allowed. Some of the features of Enterprise Edition need extra license if you use them.

Most of the parallel features need Enterprise Edition but no additional license
Partitioning needs Enterprise Editition and and additional license

More about which edition has which feature can be found in the Licensing Information User Manual


Does SQL92's REPEATABLE-READ preclude Write Skew (A5B)? REPEATABLE READ

In SQL92's definition, REPEATABLE READ is defined by serveral conditions.

P1 ("Dirty read"):

  1) P1 ("Dirty read"): SQL-transaction T1 modifies a row. SQL-transaction T2 then reads that row before T1 performs a COMMIT. If T1 then performs a ROLLBACK, T2 will have read a row that was never committed and that may thus be considered to have never existed.

P2 ("Non-repeatable read"):

  2) P2 ("Non-repeatable read"): SQL-transaction T1 reads a row. SQL-transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted.

Atomicity and no updates will be lost:

  The four isolation levels guarantee that each SQL-transaction will be executed completely or not at all, and that no updates will be lost.

Table 9, which defined REPEATABLE READ must preclude P1 & P2:

  Table 9, "SQL-transaction isolation levels and the three phenomena" specifies the phenomena that are possible and not possible for a given isolation level.

So in SQL92's definition of REPEATABLE READ, it must preclude P1, P2, and support for atomicity, no update lost.

A5B (Write Skew)

On the other side, A5B (Write Skew) is defined in A Critique of ANSI SQL Isolation Levels:

  Suppose T1 reads x and y, which are consistent with C(), and then a T2 reads x and y, writes x,and commits. Then T1 writes y. If there were a constraint between x and y, it might be violated. In terms of histories:

  r1[x]...r2[y]...w1[y]...w2[x]...(c1 and c2 occur)

  (Write Skew)

Is REPEATABLE READ preclude A5B (Write Skew) ?

In the later paper, it claims REPEATABLE READ will preclude A5B (Write Skew) in Table 4. Isolation Types Characterized by Possible Anomalies Allowed., and I am not conviced.

Any thought?

Does REPEATABLE READ preclude Write Skew?

Yes - if you buy into the Critique's view that the ANSI definitions were based on locking behaviours, for example:

  Remark 6. The locking isolation levels of Table 2 and the phenomenological definitions of Table 3 are equivalent. Put another way, PO, PI, P2, and P3 are disguised redefinitions of Locking behavior.

From that viewpoint, the history:

r1[x]...r2[y]...w1[y]...w2[x]...(c1 and c2 occur)

...is prevented when long-duration read shared locks are taken, because w1[y] is blocked by the lock taken by r2[y]. Similarly, w2[x] would be blocked by the lock taken by r1[x]. Therefore, this history cannot arise.

No - if you literally interpret the ANSI definitions. These are widely seen to be incomplete, so this outcome is not really all that surprising.


Backup all databases in the same place I am using SQL Server (2008 to 2016) on Windows. I have multiple servers, with multiple instances for one of them and a lot of databases (Over 1000). All databases are dumped on multiple storage in each server with maintenance plan.

I want to dump all databases on one SAN Volume (different folders)to reduce the number of storage to backup with NetBackup.

Did you see reason to don't do that ? (Network restriction, backup time ...) I imagine do this by change backup directory with network link, maybe not the good way ?

Consider using Ola Hallengren's maintenance scripts. His scripts are community agreed upon gold standard for this type of work. His scripts will break out each server and instance into separate folders for you automatically.

Going to one location is fine. Some companies use a Data Domain which a SAN built for backups. You essentially write to a central network share. Writing to a single location for backups is not uncommon.

Now....some things to consider when doing this

network bandwidth to the device you are writing too
network bandwidth from the device you are backing up
compute power of the device you are writing to

The idea with these items is to think about not overwhelming your backup location. You also do not want to kick off too many backups on one server and use all of the network bandwidth on your server. This would cause timeouts and other client side issues. Maybe break up the workloads by environment. Non-prod starts at 5pm (the end of the developer day), while production starts at 8pm (the end of the business day). This way your backups are not all firing off at all once. You may need to adjust further based on what results you receive.

Test, test and test more with a small subset of servers. Then you can adjust to scale out for the larger subset of servers and databases.


Retrieve Data from a Specific Database Goal:

I want to retrieve the value of [data].[Mobiles] when you use a query document that is for the database [test] (use [test])

Problem:

Based on picture two, I cannot retrieve the value from the database. Don't know what is the problem? The database [wwtest] does exist.

Picture 1:

Picture 2:

You want to use the three-part naming syntax:

<database>.<schema>.<object>
  wwtest  .  data  . Mobiles

...however you seem to be confused between your schema naming. In the table data.Mobiles, data is the schema, not dbo, so the naming should be wwtest.data.Mobiles.

dbo is also a schema, so there is no need to include it in your query when you are selecting from an object in a different schema.

Otherwise you end up with a completely different naming convention. The four-part naming sytax is as follows:

<server>.<database>.<schema>.<object>
 wwtest .   dbo    .  data  . Mobiles

In your example, you are looking for a database called dbo on a server called wwtest.

That is why you get the error:

  Could not find server 'wwtest' in sys.servers.


DB2 Reorg-recommended commands Is there some full list of commands that can cause table to become reorg-pending?

I tried google it for a while, but can't find full list. My exact problem for now - can ADD FOREIGN KEY statement cause table to become reorg-pending?

A table is placed in the reorg pending mode if you alter its physical structure, such as add or drop a column or change the column data type or nullability.

Some operations do not place the table in reorg pending, but reorganization is required for the changes to take effect, for example, changing the table COMPRESS attribute or the inline length of a LOB column.

Adding or dropping a foreign key does not place the table in reorg pending, nor does it require reorganization. However, changing an existing foreign key from not enforced to enforced will put the table in the check pending mode, which, like reorg pending, prevents applications from accessing that table.

I don't know of a single good source to read more about reorg, apart from the DB2 manual, particularly the ALTER TABLE statement and this article:

DB2 10.5 for Linux, UNIX, and Windows: Altering tables


Mongo is using 5-7% of CPU, so why is my computer so slow? I've installed MongoDB (not sharded, single location) on an AWS EC2 instance, and I'm finding that when I'm running large insert operations via pymongo, my entire EC2 instance gets very sluggish. Yet when I check CPU usage, it's reasonably low. Why is this and is there any way to contain it? Thanks for any advice.

Use 'mongostat' to monitor the amount of inserts
Use 'iostat -x 1' to see how much of the disk limitation is used. 'top'/'htop' to see cpy/ram limitations. 'nethogs' for network limits.
If the disk limit isn't reached : Let your python script run from different locations (servers) at the same time, and see if you can increase the amount of inserts. The pymongo driver might have a limitation per connection also.

Or you can use the mongoclient as extra threads. But I found the mongo client even slower inserting than pymongo on my tests:

$ mongo testDB --eval "for( var i = 0; i < 50000; i++ ) { db.test.insert({x:i,y:'hi'}); }" &

$ mongo testDB --eval "for( var i = 0; i < 50000; i++ ) { db.test.insert({x:i,y:'hi'}); }" &

Python speed insert test:

import sys
import datetime

from pymongo import MongoClient
connection = MongoClient("mongodb://10.x.x.x", w=0)

db=connection.testing
test = db.test

tstart = datetime.datetime.now()
d = {'counter' : 0}
while d['counter'] < 1000000:
  d['counter'] += 1
  testjs = {"x":d['counter'], "y":"hi" }
  try:
    test.insert(testjs)
  except:
    print "Unexpected error:", sys.exc_info()[0]

tend = datetime.datetime.now()
tdiff = tend - tstart
print tdiff.total_seconds(), ' ',tdiff.microseconds

Todo extra in the python script:

connect to mongos with a sleep between the inserts to see what happens when a primary of a shard goes down
insert more than "hi" to test splitting of chunks
...


How can I set TIMESTAMP's default to future date? I know I can set default value with current timestamp like this

... DEFAULT CURRENT_TIMESTAMP

Is there any way to set the column with fixed amount of units based on current timestamp?

... DEFAULT CURRENT_TIMESTAMP + 10 days?

Use a trigger:

mysql> CREATE TRIGGER dateinsert BEFORE INSERT ON testtable
    -> FOR EACH ROW
    -> SET NEW.yourdate =  DATE_ADD(CURRENT_TIMESTAMP(),INTERVAL 10 DAY);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into testtable (blah) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from testtable;
+------+------------+
| blah | yourdate   |
+------+------------+
|    1 | 2016-07-25 |
+------+------------+
1 row in set (0.00 sec)

mysql>


Large Variation in Bulk Insert time So I have a simple Bulk Insert process to take data from our staging table and move it into our datamart.

The process is a simple data flow task with default settings for "Rows per batch" and the options are "tablock" and "no check constraint".

The table is fairly large. 587,162,986 with a data size of 201GB and 49GB of index space. The clustered index for the table is.

CREATE CLUSTERED INDEX ImageData ON dbo.ImageData
(
    DOC_ID ASC,
    ACCT_NUM ASC,
    MasterID ASC
)

And the Primary Key is:

ALTER TABLE dbo.ImageData
ADD CONSTRAINT ImageData
PRIMARY KEY NONCLUSTERED
(
    ImageID ASC,
    DT_CRTE_DOC ASC
)

Now we've been having an issue where BULK INSERT via SSIS is running incredibly slow. 1 hour to insert a million rows. The query that populates the table is already sorted and the query to populate takes under a minute to run.

When the process is running I can see the query waiting on BULK insert which takes anywhere from 5 to 20 seconds and showing a wait type of PAGEIOLATCH_EX. The process is only able to INSERT about a thousand rows at a time.

Yesterday while testing this process against my UAT environment I was running into the same issue. I was running the process a few times and attempting to determine what the root cause of this slow insert is. Then all of a sudden it started running in under 5 minutes. So I ran it a few more times all with the same result. Also the number of bulk inserts that were waiting for 5 seconds or greater dropped form hundreds to about 4.

Now this is perplexing because it's not like we had some huge drop off in activity.

CPU during the duration is low.

The times when it's slower there appear to be fewer waits on disk.

Disk latency actually increases during the time frame that the process was running in under 5 minutes.

And The IO was much lower during the times that this process runs poorly.

I've already checked and there was no file growth as the files are only 70% full. The log file still has 50% to go. The DB is on Simple Recovery mode. DB only has one file group but is spread across 4 files.

So what I'm wondering A: why was I seeing such large wait times on those bulk inserts. B: what sort of magic happened that made it run faster?

Side note. It runs like crap again today.

UPDATE it is currently partitioned. However it's done in a method that is at best silly.

CREATE PARTITION SCHEME [ps_Image] AS PARTITION [pf_Image]
TO ([FG_Image], [FG_Image], [FG_Image], [FG_Image])

CREATE PARTITION FUNCTION [pf_Image](datetime) AS
RANGE RIGHT FOR VALUES (
      N'2011-12-01T00:00:00.000'
    , N'2013-04-01T00:00:00.000'
    , N'2013-07-01T00:00:00.000'
);

This leaves essentially all of the data in the 4th partition. However since it's all going to the same file group. The data is currently split pretty evenly across those files.

UPDATE 2
These are the overall waits when the process is running poorly.

This is the waits during the period that I was able to run the process is running well.

The storage subsystem is locally attached RAID, no SAN involved. The logs are on a different drive. Raid Controller is PERC H800 with 1 GB cache size. (For UAT) Prod is a PERC(810) .

We're using simple recovery with no backups. It's restored from a production copy nightly.

We also have set IsSorted property = TRUE in SSIS since the data is already sorted.

I can't point to the cause but I believe the default rows-per-batch for a BULK INSERT operation is "all".  Setting a limit in rows could make the operation more digestible: that's why it's an option.  (Here and going on, I'm looking at the Transact-SQL "BULK INSERT" documentation, so it could be way off for SSIS.)

It'll have the effect of splitting the operation into multiple batches of X rows, each operating as a separate transaction.  If there's an error, the batches that finished will remain committed into the destination table, and the batch that was stopped will rollback.  If that's tolerable in what you're doing, i.e. you can re-run it later and catch up, then, try that.

It's not wrong to have a partition function that puts all current inserts into one table partition, but I don't see how it's useful to partition at all with partitions in the same filegroup.  And using datetime is poor, and actually kind of broken for datetime and 'YYYY-MM-DD' without explicit CONVERT formula since SQL Server 2008 (SQL may cheerfully treat this as YYYY-DD-MM: not kidding: don't panic, just change it to 'YYYYMMDD', fixed: or CONVERT(datetime, 'YYYY-MM-DDT00:00:00', 126), I think is it).  But I think using a proxy for date value (year as int, or year + quarter) to partition on will work better.

Maybe it's a design copied from elsewhere, or duplicated across several datamarts.  If this -is- a true datamart, a dump from the data warehouse to give department managers some data to play with, that isn't (by you) being sent on elsewhere, and probably read-only as far as data users are concerned, then, it seems to me that you could remove the partition function -or- change it to explicitly put all new data into the fourth partition no matter what, and no one would care.  (Perhaps you should check that no one cares.)

It feels like a design where the plan is to drop the contents of partition 1 some time in the future and create another new partition for more new data, but it doesn't sound like that's happening here.  At least it hasn't happened since 2013.


MySQL using up all RAM and SWAP in group replication settings I am using MySQL 8.0.18 in a group replication (multi-master) infrastructure. There are currently 4 master servers connected to each other and everything seems to be working fine but over the course of around 2 weeks all mysqld process on all servers uses up all the swap. ram usage stays at 800Mb as I suspect innodb_log_buffer_size is doing its job.

Server Specs:

Debian 10
1Gb RAM
3GB SWAP
20GB ssd

Mysql.cnf:

max_connections=20
innodb_log_buffer_size=800M
key_buffer_size=50M
super_read_only=off
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = NONE
log_slave_updates = ON
log_bin = binlog
binlog_format = ROW
transaction_write_set_extraction = XXHASH64
loose-group_replication_bootstrap_group = OFF
loose-group_replication_start_on_boot = OFF
loose-group_replication_ssl_mode = REQUIRED
loose-group_replication_recovery_use_ssl = 1
loose-group_replication_unreachable_majority_timeout=80
loose-group-replication-autorejoin-tries=2015
loose-group-replication-transaction-size-limit=0

Related OS config:

vm.swappiness=5

How do I make mysql stop using so much swap?

innodb_log_buffer_size=800M is probably using more than the available memory (after allowing for the OS, MySQL code, etc, etc).

Use the default value.


Full, incremental or differential backup? Using RMAN my database is backed up once a day using a command similar to this

backup incremental level 1 cumulative
[...]
database
include current controlfile spfile

In the recovery catalog database I can see one of these backup jobs like this

    DB_KEY DB_NAME  SESSION_KEY COMMAND_ID              START_TIME          END_TIME            INPUT_BYTES OUTPUT_DEVICE_TYP STATUS        INPUT_TYPE    ELAPSED_SECONDS
---------- -------- ----------- ----------------------- ------------------- ------------------- ----------- ----------------- ------------- ------------- ---------------
   xxxxxxx dbname       xxxxxxx 2016-11-12T05:03:21     12.11.2016 05:02:28 12.11.2016 05:03:21   862159354 SBT_TAPE          COMPLETED     DB INCR                    53

In the table rc_backup_piece_details there are five entries with this backup job's session id

SESSION_KEY DB_NAME      DB_KEY     BP_KEY B INCREMENTAL_LEVEL DEVICE_TYPE          START_TIME          COMPLETION_TIME     ELAPSED_SECONDS S      BYTES
----------- -------- ---------- ---------- - ----------------- -------------------- ------------------- ------------------- --------------- - ----------
    xxxxxxx dbname      xxxxxxx    1000001 I                 1 SBT_TAPE             12.11.2016 05:03:14 14.11.2016 05:03:15               1 A     423549
    xxxxxxx dbname      xxxxxxx    1000002 I                 1 SBT_TAPE             12.11.2016 05:03:05 14.11.2016 05:03:11               6 A  862156486
    xxxxxxx dbname      xxxxxxx    1000003 D                   SBT_TAPE             12.11.2016 05:03:16 14.11.2016 05:03:17               1 A   45324586
    xxxxxxx dbname      xxxxxxx    1000004 I                 1 SBT_TAPE             12.11.2016 05:02:30 14.11.2016 05:03:01              31 A  256422458
    xxxxxxx dbname      xxxxxxx    1000005 I                 1 SBT_TAPE             12.11.2016 05:03:13 14.11.2016 05:03:14               1 A   45624595

As you can see, there is one backup piece wich D as backup type. In the Oracle documentation I found the following:

  The type of backup. Possible values are D for data file or control file backups, I for incremental backups, and L for archived log file backups

Does this mean, that among these backup pieces there is one incremental backup of a data file or control file?

If this is the case, then I am wondering, why this only happens with this particular database. All other databases are backed up with include current controlfile spfile too.

The difference between this and other databases is that most other databases are backed up incremental level=0. So might there be the difference?

Edit: So, if backup type D is not incremental backup, then why is this backup piece contained in a backob job with input_type = 'DB INCR'? AND could  what part of the rman command triggers this datafile backup and if it is include current controlfile spfile then why is there a backup piece with backup type D for any other database?

Does this mean, that among these backup pieces there is one incremental backup of a data file or control file?

No, the Backup type is D means it's backup of data files or control file but it's not incremental its full backup(denoted by NULL for INCREMENTAL_LEVEL).

Other backups are differential incremental backup of the backup type D which is base for these incremental backups.

  The difference between this and other databases is that most other databases are backed up incremental level=0. So might there be the difference?

Incremental Level 0 and Incremental Level 1 Cumulative is different types of backup so there is difference.

  Incremental backups can be either level 0 or level 1. A level 0 incremental backup, which is the base for subsequent incremental backups, copies all blocks containing data, backing the datafile up into a backup set just as a full backup would. The only difference between a level 0 incremental backup and a full backup is that a full backup is never included in an incremental strategy.

For more: RMAN Incremental Backups


SQL Server 2016 Powershell: Restore-SqlDatabase with -ReplaceDatabase and -RelocateFile We have a several-node SQL Server 2016 AlwaysOn cluster, and have a need to move databases from one node/Availability Group to another quite often, so I'm trying to automate the process.

To add database to secondary replica, I need to prepare it first, i.e. backup primary, create new one on secondary, restore backup to secondary. The problem is that often the paths (local drives) do not match, for instance:

PRIMARY: D:\Databases\db.mdf, D:\Logs\db_log.ldf
SECONDARY: E:\Databases\db.mdf, E:\Logs\db_log.ldf

I'm trying to write a PoSh script, assuming that I have to create new database on SECONDARY server first, and came up with the following:

$SQLQuery = "CREATE DATABASE [$Database] ON PRIMARY ( NAME = N'$Database', FILENAME = N'$DestinationDatabaseFile' , SIZE = 1024MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024MB ) LOG ON ( NAME = N'$LogName', FILENAME = N'$DestinationLogFile' , SIZE = 1024MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024MB )"
Invoke-Sqlcmd2 -ServerInstance $DestinationServer -Database "master" -Query $SQLQuery

$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($Database, $DestinationDatabaseFile)
$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($LogName, $DestinationLogFile)

Restore-SqlDatabase -ServerInstance $DestinationServer -Database $Database -BackupFile $LogBackupFile -ReplaceDatabase -NoRecovery -RelocateFile @($RelocateData,$RelocateLog)

As far as I understand on restore I need to relocate the files ( -RelocateFile ), and also replace the database ( -ReplaceDatabase ). The error I'm getting is:

  Restore-SqlDatabase : System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'db' database.

Servers are of the same version, databases of same compatibility levels.

Perhaps somebody could suggest a way around?

Thanks!

The following worked:

$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($Database, $DestinationDatabaseFile)
$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($LogName, $DestinationLogFile)
Restore-SqlDatabase -ServerInstance $DestinationServer -Database $Database -BackupFile $DatabaseBackupFile -ReplaceDatabase -NoRecovery -RelocateFile @($RelocateData,$RelocateLog)
Restore-SqlDatabase -ServerInstance $DestinationServer -Database $Database -BackupFile $LogBackupFile -RestoreAction 'Log' -NoRecovery


Deletion of .frm .MYD .MYI files On my server I had files from 2012, 2013, ... 2016 which were .frm, .MYD, .MYI. I deleted files which were older than 180 days. I left the files from this year only.

Three days later database was gone and phpMyAdmin showed:

  #2002 - The server is not responding (or the local MySQL server's socket is not correctly configured)

Could deletion of these old MySQL files have been the problem here?

Well I guess you just deleted the database's data files (.myd) and the database's index files (.myi) from your database server. To top it off you deleted the *.frm file which are also part of your database.

One reference I came across: Normalize Table File Name Extensions
Another reference describes what you did manually: What happens when you drop your database
The full reference can be found here: MySQL Glossary

I do hope you have a backup of your database?


SQL Backup with Mirror option in maintenance plan I need to do backup ALL databases on an instance and put them in 3 different locations on our network, so i found that you can use backup with mirror, so I set that up.

I have a maintenance plan which runs a job (see job details below).
The problem I have is that if I add a database to the instance I will have to manually add it to the T-SQL job, is there a way I can avoid that.

I know you can have a step in the maintenance plan to backup all databases but you cant have it with the Mirror option (or can you?)

Can i get the maintenance plan to run my job on every database?

My job details: (I know there is only one database at the moment but I am just testing at the moment)

EXECUTE master.dbo.xp_create_subdir N'M:\Backup\DBA Tools'
    GO
    EXECUTE master.dbo.xp_create_subdir N'\\uhssql\sqlbak$\SQLDS\DBA Tools'
    GO
    EXECUTE master.dbo.xp_create_subdir N'\\sqlds02\backupprod$\Daily Backup\SQLDS\DBA Tools'
    GO

    DECLARE @BackupPathandDate VARCHAR(150)
    DECLARE @BackupPathandDateMirror VARCHAR(150)
    DECLARE @BackupPathandDateMirror2 VARCHAR(100)

    SET @BackupPathandDate = 'M:\Backup\DBATools_' + REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 100),':',''),' ','') + '.bak'
    SET @BackupPathandDateMirror = '\\uhssql\sqlbak$\SQLDS\DBATools_' + REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 100),':',''),' ','') + '.bak'
    SET @BackupPathandDateMirror2 = '\\sqlds02\backupprod$\Daily Backup\SQLDS\DBA Tools\DBATools_' + REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 100),':',''),' ','') + '.bak'

    BACKUP DATABASE [DBA Tools]
    TO DISK = @BackupPathandDate
    MIRROR TO DISK = @BackupPathandDateMirror
    MIRROR TO DISK = @BackupPathandDateMirror2
    WITH FORMAT

Many many thanks

Neil

Ola Hallengren automated backup solution will solve your problem for newly added databases.

Check for the MirrorDirectory option:

  MirrorDirectory

  Specify one or multiple directories to perform a mirrored backup.

  The MirrorDirectory option in DatabaseBackup uses the MIRROR TO option
  in the SQL Server BACKUP command.


How to compare two SQL Server 2008 configurations I have a problem with my application that works on my dev SQL Server 2008 (Developer Edition), but not on a production SQL Server 2008. Is there an easy way to export the settings from the production server and compare it to my server configuration?

What I found out is that I can export Facets in SQL Server Management Studio to XML files and compare them in a diff tool.

Is there any other/better way to export and compare settings of two SQL Server instances?

You really just have to get creative.  As we all know, there are many places that settings are stored, depending on what exactly you're looking to compare.  For instance, to compare instance-wide configuration settings, you can simple do an EXCEPT query (you may have to create a linked server, or export/import the data depending on how you want to approach that):

select *
from [YourProdInstance].master.sys.configurations

except

select *
from [YourDevInstance].master.sys.configurations

Use this same type of methodology for all other configuration settings that you will want to compare.

Find the root of the config data
Get both instances' config data in a common place
Write a query that will compare the data (whether it is XML, or not)


Intentionally slow down importing of (Percona) MySQL 5.7 dump I need to move a very large MySQL table (250M rows, 120 columns. 120GB of data, 310GB of index) from one database instance to another (on AWS). For reasons beyond the scope of this question, this is the only option we are considering.

Our set up uses cross-region replication on AWS with many slaves on different regions. Updates to a MySQL table are written to the binlog and replicated to these slaves immediately using a private tunnel.

The host database includes only this one table, and the table's contents are static. The target database includes hundreds of other tables with active transactions that must continue to be replicated to other regions with minimal replication lag. This is the root of the problem: if I simply import this massive dump (mysql -u username -p database_name < file.sql), the writes to this single table with overwhelm the binlog and cause replication lag for other tables on slaves, which is unacceptable.

What I'm looking for is this: Is there a way I can intentionally slow down this import to prevent it from causing a replication lag on transactions on other tables?

I'm looking for the most straightforward approach to accomplish this with minimal effort. You can, of course, write a custom script that batches the writes for you and sleeps for a period of time between each batch, but I'm trying to avoid that if possible.

This is the version of MySQL I'm running on both instances:

$ SHOW VARIABLES LIKE "%version%";

innodb_version  5.7.12-5
protocol_version    10
slave_type_conversions
tls_version TLSv1,TLSv1.1,TLSv1.2
version 5.7.12-5-log
version_comment Percona Server (GPL), Release '5', Revision 'a2f663a'
version_compile_machine x86_64
version_compile_os  debian-linux-gnu

Consider writing a script (in your favorite language) to read the dump file one line at a time and send that line to STDOUT, which will be piped into mysql.  And add a tiny sleep in the loop that does that.

To get fancier, you could fetch Seconds_behind_master to tweak the sleep up or down.  (I think at least one of Percona's tools does this.)


Full text catalog missing a keyword We have a full text catalog on one of our databases but one keyword does not work.

e.g:

select * from table where name = 'jon' -- result set returns as expected
select * from table where Contains(a.Name, '"jon"') -- no result set

What might be causing this?

Thank you.

You might want to have a look at the following article:

Configure and Manage Stopwords and Stoplists for Full-Text Search (Microsoft Docs)

  To prevent a full-text index from becoming bloated, SQL Server has a mechanism that discards commonly occurring strings that do not help the search. These discarded strings are called stopwords. During index creation, the Full-Text Engine omits stopwords from the full-text index. This means that full-text queries will not search on stopwords.

It contains a general overview of how stoplists work and references a further article that goes on to explain how you can modify stoplists to achieve what you are looking for:

ALTER FULLTEXT STOPLIST (Transact-SQL) (Microsoft Docs)

  Inserts or deletes a stop word in the default full-text stoplist of the current database.

The syntax of the command is as follows:

ALTER FULLTEXT STOPLIST stoplist_name
{
        ADD [N] 'stopword' LANGUAGE language_term
  | DROP
    {
        'stopword' LANGUAGE language_term
      | ALL LANGUAGE language_term
      | ALL
     }
;


Make a list of stations by checking next_station column value How to list all station between selected two stations based on next_station_id

For instance, if the user selects origin A station and destination to J than I want to list all stations in ORDER that have set into the next_station_id column.

I have an intermediate level of knowledge in MySQL and have no idea how to write such a query.

SELECT r.number as route, s.name AS station, s.code AS station_code, rl.name AS line, s.line_two_id AS interchange
FROM stations AS s
         JOIN route_stations as rs
              ON rs.station_id = s.id
         JOIN routes r
              ON rs.route_id = r.id
         JOIN route_lines rl
              ON s.line_id = rl.id
ORDER BY rs.station_order ASC;

  I know the above query is just to join table but as I said I have no
  clue how to write a query to get a list of stations that have been set
  in next_station_id

route_stations table

I am also adding a diagram in case anyone wants to look the structure to refer and  suggest

Diagram

WITH RECURSIVE
cte AS ( SELECT station, next_station, 1 route_order
         FROM routes
         WHERE station = @source
           AND route_id = @route_id
         UNION ALL
         SELECT cte.next_station, routes.next_station, cte.route_order + 1
         FROM routes, cte
         WHERE routes.station = cte.next_station
           AND cte.next_station != @source
           AND cte.station != @destination
           AND routes.route_id = @route_id )
SELECT *
FROM cte
WHERE @destination = ( SELECT station
                       FROM cte
                       ORDER BY route_order DESC LIMIT 1 )
;

Wrong route selection (including cycles) allowed - in such case the resultset will be empty.


Inhibit SQL Server trigger execution A table with various prices per item have a trigger that will update price 3 when price 0 is changed.

The ERP that uses this database has a utility that when run updates the database with information coming from other locations.

It is possible, when this utility runs, inhibit execution of said trigger?

If it so, how can I identify when utility runs?

When the utility runs, just have it (or whatever calls the utility) run this command:

DISABLE TRIGGER dbo.trigger_name ON dbo.table_name;

And then afterward:

ENABLE TRIGGER dbo.trigger_name ON dbo.table_name;

It would be better to have the utility do this just around the statement(s) affecting this table specifically (and wrap that in a transaction), since the trigger is disabled for all users, not just the utility.

Another way - if the utility passes a specific application name in the connection string, or is known to be the only thing connecting as a certain user or from a certain host - is to check sys.dm_exec_connections/sys.dm_exec_sessions within the trigger.

ALTER TRIGGER dbo.trigger_name
ON dbo.table_name
FOR INSERT, ...
AS
BEGIN
  SET NOCOUNT ON;

  IF NOT EXISTS
  (
    SELECT 1 FROM sys.dm_exec_connections
    WHERE client_net_address = '10.65.10.5' AND session_id = @@SPID

    /* or
    SELECT 1 FROM sys.dm_exec_sessions
    WHERE host_name = N'MELVIN' AND session_id = @@SPID
    */

    /* or
    SELECT 1 FROM sys.dm_exec_sessions
    WHERE program_name = N'My Utility App Name' AND session_id = @@SPID
    */
 )
  BEGIN
    -- trigger logic here
  END
END

If the utility logs in as a specific login separate from the main app, then you could say:

IF USER_NAME() <> N'user name' /* or SUSER_SNAME() or ORIGINAL_LOGIN() */
BEGIN
  -- trigger logic here
END

If the utility always handles multiple rows, and the main app always only handles one row, then you could do this:

IF (SELECT COUNT(*) FROM inserted) = 1
BEGIN
  -- trigger logic here
END

If the utility could call a stored procedure instead of using ad hoc SQL, your life would be a lot easier, because you could lock the table in a transaction, disable the trigger, do your thing, then enable the trigger again. (Having this control is yet another benefit of using stored procedures and not compiling your SQL code into an app.)

If you can't fix any of these things, then you may want to look into disabling the trigger and achieving its goal in some other way. Without understanding exactly what the trigger does, it's difficult to be any more precise than that.


PowerShell script to access Oracle system views I am using PowerShell script to access oracle 11g's system views
as  SELECT COUNT (*) AS COUNT FROM v$session but I keep getting "ORA-00942: table or view does not exist"

I have created a user with sysdba privilege and all connections are a fine as I can even query other schemas without errors, but I only get error when I access any system views. can anyone tell me what I am doing wrong?

I also gave permission as "GRANT SELECT on v_$session TO mynewuser even tho I know its not needed if the user has sysdba.

What powershell syntax are you using?

If you do not enclose the select between single quotes, Powershell will treat it like a variable. If that's the problem, please try:

$command.CommandText = 'SELECT COUNT (*) AS COUNT FROM v$session'


Applying audit policy on multiple auditing subject or object I have created a policy on execute category and I would like to know if this policy can be applied on multiple auditing subject or object. Since the execute category will generate many audit entries I would like to limit the results by applying this execute audit policy on specific user/table combination.

Is it possible?

Whilst the audit policy created by CREATE AUDIT POLICY is database-wide, it is applied to individual objects or roles by the AUDIT statement. For example, to audit statement executions (successful or otherwise) against a particular table:

CREATE AUDIT POLICY allexec CATEGORIES EXECUTE STATUS BOTH;
COMMIT;
AUDIT TABLE mytable USING POLICY allexec;
COMMIT;

To audit statements executed by a particular role:

AUDIT ROLE somerole USING POLICY allexec;
COMMIT;

The reason for all the COMMITs is that, as the docs note, "[a]n AUDIT-exclusive SQL statement must be followed by a COMMIT or ROLLBACK statement...". See audit policies for more information.


Is it important to release memory back to the server? I have a SQL Server 2008 R2 instance running on a production system. I'm not a DBA; I'm more a developer, but my client is currently asking me: How important is it to release memory from SQL Server back to the server? Would it impact the performance of subsequent queries to the instance if we released memory back to the OS?

How exactly would you 'release' the memory back?

As a general rule SQL Server will steal all the memory from the OS and then use it for it's own purpose. See Dynamic Memory Management:

  The default memory management behavior of the Microsoft SQL Server Database Engine is to acquire as much memory as it needs... The instance then continues to acquire memory as needed to support the workload. As more users connect and run queries, SQL Server acquires the additional physical memory on demand. A SQL Server instance continues to acquire physical memory until it either reaches its max server memory allocation target or Windows indicates there is no longer an excess of free memory;


What should I use as a primary key/index for a table with no unique columns that are mostly written to? I have a table on a SQL server database that stores logging information about various user activity. Date/time, user id, and a few columns that specify where and what was done. None of this data is guaranteed to be unique (date/time will likely be unique, but it's far from guaranteed). This table will be written to often and searched rarely. I'd like to have it optimized for inserting, but at the same time be able to search the large quantities of data in as short a period of time as possible.

I'm still very new to sql server, if I was to have every column indexed, would that be a big performance hit with constantly adding new entries?

Edit: The problem is, it can be important to search for a lot of stuff. Some of the things this logs is who looks at important client personal information. So, ideally, you should be able to search for both which users accessed this specific information (select user where thing_done=looked at that data) as well as what a suspect user has been up to (select alldata where user=suspect). The speed of this is heavily dependent on those indexes though, right?

Q: If were to to have every column indexed, would that be a big
  performance hit?

A: Uh - yes :)  It would :)

Don't do it :)

  Q: I'd like to be able to search the large quantities of data in as
  short a period of time as possible

A: And that's precisely what you want to build your indexes on.

What are you likely to be "searching"?

I'm guessing date/time (a certain range of minutes/hours/days/etc) and some "type".  For example, users might be "eating", "sleeping" or "studying".

You definitely want at least one index, and you definitely want to "strategically determine" what should be your "clustered index".  Clustered indexes are a Sql Server thing.

You can learn more here:

https://stackoverflow.com/questions/9085390/best-practice-for-storing-millions-of-rows-with-tsql-sql-server-2008
http://msdn.microsoft.com/en-us/library/aa933131%28v=sql.80%29.aspx


MySQL - stay in cache priority for queries Is there a way to tell MySQL that some queries has highter priority in cache and mustn't be popped out until it's data become invalid?

The problem is that innodb cache size is rather small and data of "Major" heavy weight queries are popped out quickly because of other frequently used queries.

So we want to say: "these Major query data MUST stay in cache. If cache is full, no Major data will be popped out until it become invalid. If cache is full of Major data, no other queries will use cache".

Some info: engine=InnoDB. Cache is turned on and directives SQL_(NO)_CACHE are not used in application queries. Cache is big enought to store all Major data and much "hot" data, but too small to store even 5% of all queries data. It's desirable to use cache on all queries but they shouldn't replace Major data

First of all, there are two caches relevant to your query.

The Query cache uses SQL_(NO)_CACHE and is the less important cache.  For production systems with lots of writes, you may as well turn off the QC.  Every write to a table flushes all entries in the QC for the table(s) involved.  That leads to another point:  A large QC can lead to a lot of flushing activity.  Limit query_cache_size to no more than about 50M.  The QC can help with CPU.

The buffer_pool is the important cache for InnoDB.  You should give about 70% of RAM to innodb_buffer_pool_size.  That is where blocks of InnoDB data and indexes are cached.  It helps because it avoids much of the I/O.

Often, the most important thing to do is to identify the slowest queries (using SHOW PROCESSLIST and/or the slowlog) and work on speeding them up.  Sometimes adding an index, sometimes a minor change to a SELECT, etc, can speed up the system far more than tweaking the cache sizes.

The buffer_pool has a way to avoid letting a table scan blow out the cache.  The QC has no mechanism like what you are asking for.

If you do keep the QC on, then set query_cache_type=DEMAND and use SQL_(NO)_CACHE.


Query Row Count for All Tables I need help creating an MSSQL query to pass in every table name to the query below, which returns the number of rows.

This is how I get the row count for a single table:

SELECT Total_Rows = SUM(st.row_count)
FROM sys.dm_db_partition_stats st
WHERE object_name(object_id) = 'TABLE_NAME' AND (index_id < 2)

What is the way to loop this query on all tables, returning an output of each table's name and row count?

Example output:

TableName     RowCount
---------------------
FirstTable    109
SecondTable   2195
ThirdTable    0

You can simply use this :

SELECT sc.name +'.'+ ta.name TableName
 ,SUM(pa.rows) RowCnt
 FROM sys.tables ta
 INNER JOIN sys.partitions pa
 ON pa.OBJECT_ID = ta.OBJECT_ID
 INNER JOIN sys.schemas sc
 ON ta.schema_id = sc.schema_id
 WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
 GROUP BY sc.name,ta.name
 ORDER BY SUM(pa.rows) DESC

Various other methods include:here

sys.partitions Catalog View
sys.dm_db_partition_stats Dynamic Management View (DMV)
sp_MSforeachtable System Stored Procedure
COALESCE() Function


How to have MS Analysis server setup of both modes: multi dimension and tabular Ref: Is there a way to install both tabular and multidimensional modes?

The answer was .."You answered your own question. You simply install another instance of SSAS."..

My MS SQL 2014 has a database engine installed with AdventureWorks DB and AdventureWorksDW DB. I also have installed Analysis server with a database containing a multi-dim cube.

I tried to install a another instance of SSAS to allow me to test the tabular mode, but the DVD disk did not give me the option to add another instance of SSAS for tabular.

What am I missing?

How do I do this .."You simply install another instance of SSAS.".. for the new instance of SSAS to source data off my already installed AdventureWorkdsDW dataBase.

Thanks - First response was the answer

When you begin installation wizard via new stand-alone Installation option, you might have missed to select Perform a new installation .. option at Installation type which is first thing to select in the wizard.

After this you would proceed with Product key -> License terms -> Feature selection as Tabular

Or following command straight-away install new SSAS Instance with Tabular mode, command has to be run from the same directory of installation media or relace Setup.exe with media path.

Setup.exe /q /IAcceptSQLServerLicenseTerms /ACTION=install /FEATURES=AS /ASSERVERMODE=TABULAR /INSTANCENAME=SSAS_Tab /INDICATEPROGRESS /ASSVCACCOUNT=.\administrator /ASSVCPASSWORD=<administratorpw> /ASSYSADMINACCOUNTS=.\administrator


Why is MySQL using this index? Can I force it to use another? I have a query, and when I call EXPLAIN for it, it appears that my db is using an unhelpful index and ignoring a useful index.
EXPLAIN SELECT id, full_name, agent_id, brand_id FROM `mytable`
WHERE (
    `mytable`.`brand_id` IN (21264)
    OR
    `mytable`.`agent_id` IN (4, 5, 6, 7, 9, 11)
    )
    AND (full_name IS NOT NULL AND full_name != '')
    AND (full_name LIKE '%kath%' OR id = 12 OR id = 0)
    ORDER BY full_name ASC LIMIT 15

(That query was auto-generated, hence the strange parentheses.)
The table has a few inidices:
+-----------+------------+------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name                               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mytable |          0 | PRIMARY                                |            1 | id          | A         |     1329001 |     NULL | NULL   |      | BTREE      |         |               |
| mytable |          1 | mytable_by_agent_id                    |            1 | agent_id    | A         |        9561 |     NULL | NULL   | YES  | BTREE      |         |               |
| mytable |          1 | mytable_by_brand                       |            1 | brand_id    | A         |        4598 |     NULL | NULL   | YES  | BTREE      |         |               |
| mytable |          1 | mytable_by_full_name                   |            1 | full_name   | A         |     1329001 |     NULL | NULL   | YES  | BTREE          |         |               |
| mytable |          1 | index_mytable_on_brand_id_and_agent_id |            1 | brand_id    | A         |          11 |     NULL | NULL   | YES  | BTREE      |         |               |
| mytable |          1 | index_mytable_on_brand_id_and_agent_id |            2 | agent_id    | A         |        1205 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

And I 'd expect the query to make use of index_mytable_on_brand_id_and_agent_id, but instead, EXPLAIN reveals that it's using mytable_by_full_name:
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: consumers
         type: range
possible_keys: PRIMARY,mytable_by_agent_id,mytable_by_brand,mytable_by_full_name,index_mytable_on_brand_id_and_agent_id
          key: mytable_by_full_name
      key_len: 258
          ref: NULL
         rows: 811112
        Extra: Using where

How can I get this to use the other index? Why did it choose an index which won't help (because of the % at the start of the LIKE)?

No index will help with the WHERE clause -- part has OR, part has LIKE with an initial wild card.
So, the only hope for optimizing is to work on the ORDER BY.  However, that is risky -- it might finish after looking at 15 rows, or it might look at the entire table without finding 15 rows.  (Or some amount in between).
A single OR can usually be turned into UNION to good advantage.  But 2 ORs is too messy to contemplate.


bulk insert from file I am often requested to upload data from file (let's say csv) into a Sql Server 2008 db.

I would like to use BULK INSERT, but I have no access to the db server file system.

Questions

is there a way to pass/upload a remote file to the Sql Server in order to use the BULK INSERT?
is there any other tsql way to import data from file to db?

If you have one of the system administrators or database administrators create a read/write share on the server and grant your domain account access to the share, then you should be able to copy the data up and then use BULK INSERT because it would run in the context of the SQL Server service account which would be able to see the path.  Alternatively, you could make a request that they place the data file on the server for you and give you the local path which you would then use in your BULK INSERT script.

You could also use the SQL Server Import/Export wizard to create a connection to your file as the source on your workstation then create a connection to the server as the destination and import the file that way.  You can get to the Import/Export wizard by right clicking the database in Object Explorer and selecting Tasks > Import Data.


Guidelines on best indexing strategy for varying searches on 20+ columns I’m running a search engine for cars. It’s backed by a postgresql 9.3 installation.

Now I’m unsure about the best approach/strategy on doing index optimization for the fronted search.

The problem:

The table containing the cars holds a around 1,5 million rows. People that searches for cars needs different criteria to search by. Some search by brand/model, some by year, some by mileage, some by price and some by special equipment etc. etc. - and often they combine a whole bunch of criteria together. Of cause some, like brand/mode and price, are used more frequently than others. In total we offer: 9 category criteria like brand/model or body type, plus 5 numeric criteria like price or mileage, plus 12 boolean criteria like equipment. Lastly people can order the results by different columns (year, price, mileage and a score we create about the cars). By default we order by our own generated score.

What I’ve done so far:

I have analyzed the usage of the criteria “lightly”, and created a few indexes (10). Among those, are e.g. indexes on price, mileage and a combined index on brand/model. Since we are only interested in showing results for cars which is actually for sale, the indexes are made as partial indexes on a sales state column.

Questions:

How would you go about analyzing and determining what columns should be indexed, and how?
What is the best strategy when optimizing indexes for searches happening on 20 + columns, where the use and the combinations varies a lot? (To just index everything, to index some of the columns, to do combined indexes, to only do single column indexes etc. etc.)
I expect that it does not make sense to index all columns?
I expect it does not make sense to index boolean columns?
Is it better to do a combined index on 5 frequently used columns rather than having individual indexes on each of them?
Would it be a good idea to have all indexes sorted by my default sorting?
Do you have so experiences with other approaches that could greatly improve performance (e.g. forcing indexes to stay in memory etc.)?

Capture the queries that people actually make.
Analyze data from #1; then devise short, multi-column, indexes (2, maybe 3, columns each)
No.  Especially not for yes/no flags.  It's ok to combine such, as in #2.
See #3.
The order of columns is important in an index.  A 5-column index is (roughly) equivalent to the 1-col index on its first column, but is not useful if you don't query on that column.
Probably.
Forcing into memory is counter-productive because it takes away space for caching other stuff.

More discussion:  http://mysql.rjweb.org/doc.php/eav


Copy data from SQL Server 2000 to SQL Server 2014 Express I need to copy only data (not tables) from a SQL Server 2000 database to a SQL 2014 Express database. I tried to do this using DTS but it loses keys and indices. Backup (2000) and restore (to 2014) does not work. Is there another way?

Copying ONLY data does not copy the keys and indices, since these are metadata describing how to treat the data.

The following article suggests that you create a SQL Server 2008 instance, backup the SQL Server 2000 database and restore to the SQL Server 2008 database. Then backup the 2008 database and restore it to SQL Server 2014.  (Be sure to check Service Pack requirements).

Upgrade path: SQL Server 2000 SP4 to SQL Server 2008
Upgrade path: SQL Server 2008 SP3 or later to SQL Server 2014

http://blogs.technet.com/b/mdegre/archive/2012/06/15/migration-sql-server-2000-to-sql-server-2012.aspx

Then you should have the SQL Server 2000 database upgraded to SQL Server 2014.

Since you want Keys and Indices as well, you need the metadata.  Another approach is to script out all the objects (tables, views, procedures, etc) from your SQL Server 2000 database.  Then execute the create script on your 2014 server. (Fixing any problems that you find.)  After that transfer the data via linked server (per Mark Sinkinson), SSIS packages, BCP, etc.


Movement of data from one data file to another within PRIMARY Filegroup Scneario: We had only 1 data file(.mdf) which is now around 900 GB. We are planning to split it across multiple drives to improve IO performance.

As of now we have created 8 different data files in 8 drives within PRIMARY Filegroup.

what is the most efficient of moving data from 1 data file(.mdf) to another within same filegroup i.e PRIMARY Filegroup.

If that is not possible, then what is the best way of splitting the data across multiple drives in the same database?

We tried using DBCC SHRINKFILE with EMPTYFILE but it throws errors as it cannot move system files from .mdf file.

There's no direct command for this.

You can cause enough data movements using ALTER TABLE or ALTER INDEX with the REBUILD option table by table or index by index, so that the new storage allocated by the rebuild will come from the new files. I.e., this will cause "emptiness" of the mdf file for each rebuild. And then finally a shrink (not using EMPTYFILE, but to the desired size, same size as the other files) will do whatever movements there is left to do (the pages allocated to the part of the mdf file that will be removed).

Note though that this final shrink will fragment your indexes and if you care about index fragmentation you would be in for another rebuild.

The reason for using rebuild first is for you to control the pace, doing bits by bits. And also because the shrink code can be horribly slow (for instance if you have heaps and LOBs).


Query result with X rows discarded In a transaction, if I select results to display to the screen, I get the message Query result with x rows discarded.

How can I view those results?  Something is amiss with my query and I want to see what the values are in a temp table at a certain point.

In a transaction (or in an SQL function) only the results from the last command are returned to the user. You could write the results to a (temporary) table to inspect them later.

Or you run through your transaction interactively, executing one command after the other.


Need demo scripts and sample corrupted databases for corruption databases troubleshoot I am looking for a demo for recovery of corruption databases in different ways.  Is there any link for those examples to practice in LAB machines.

Check out the work by Steve Stedman. He has corruption examples for days, http://stevestedman.com/?s=corruption&submit=Go


one node in cluster got crashed ,how to recover it? we are using percona galera cluster with 3 nodes.
In my cluster one node got crashed due to low disk space ,we have cleared some log files and provided sufficient disk space.Now we are trying to start our cluster in that node.mysql is not up .when i go to error log i found below errors. i was tried with innodb_force_recovery=6 also but no luck. can anybody help me how to fix below issue.

error log

130919 12:55:15 [Note] WSREP: Closed send monitor.
130919 12:55:15 [Note] WSREP: view((empty))
130919 12:55:15 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 1
130919 12:55:15 [Note] WSREP: gcomm: closed
130919 12:55:15 [Warning] WSREP: gcomm: backend already closed
130919 12:55:15 [ERROR] WSREP: gcs/src/gcs_fifo_lite.c:gcs_fifo_lite_close():70: Trying to close a closed FIFO
130919 12:55:15 [Note] WSREP: Flow-control interval: [16, 16]
130919 12:55:15 [Note] WSREP: Received NON-PRIMARY.
130919 12:55:15 [Note] WSREP: Shifting JOINED -> OPEN (TO: 66674748)
130919 12:55:15 [Note] WSREP: Received self-leave message.
130919 12:55:15 [Note] WSREP: Flow-control interval: [0, 0]
130919 12:55:15 [Note] WSREP: Received SELF-LEAVE. Closing connection.
130919 12:55:15 [Note] WSREP: Shifting OPEN -> CLOSED (TO: 66674748)
130919 12:55:15 [Note] WSREP: RECV thread exiting 0: Success
130919 12:55:15 [Note] WSREP: recv_thread() joined.
130919 12:55:15 [Note] WSREP: Closing replication queue.
130919 12:55:15 [Note] WSREP: Closing slave action queue.
130919 12:55:15 [ERROR] WSREP: gcs/src/gcs.c:gcs_close():1321: Failed to join recv_thread(): -3 (Unknown error 18446744073709551613)
130919 12:55:15 [Note] WSREP: Closing replication queue.
130919 12:55:15 [Note] WSREP: /usr/sbin/mysqld: Terminated.
130919 12:55:15 [ERROR] WSREP: gcs/src/gcs_fifo_lite.c:gcs_fifo_lite_close():70: Trying to close a closed FIFO
130919 12:55:15 [Note] WSREP: Closing slave action queue.
130919 12:55:16 mysqld_safe Number of processes running now: 0
130919 12:55:16 mysqld_safe WSREP: not restarting wsrep node automatically
130919 12:55:16 mysqld_safe mysqld from pid file /u01/mysql/folmobileqa2/log/mysqld.pid ended

Ok solved my issue..

My hard drive failed so mysql log got hosed, end of story..

I needed full state transfer and to remove everything except the mysql database with all those myisam tables full of permissions..

Stop the broken node completely..

root@my-galera-clustercontrol:~# s9s_galera --stop-node -i 1 -h10.0.1.111

Rsync out the mysql database folder from mysql data folder..

root@my-galera-percona1:/mnt/data/mysql# rsync -av mysql ../mysqlback/

Delete everything include bad logs from mysql data folder:

root@my-galera-percona1:/mnt/data/mysql# rm -rf *

Restore the mysql data folder:
    root@my-galera-percona1:/mnt/data/mysql# rsync -av ../mysqlback/mysql .

Start the node back with a in cluster control causing a full state transfer:

root@my-galera-clustercontrol:~# s9s_galera --start-node -i 1 -h 10.0.1.111 -d 10.0.1.112

From there you can watch crazy io with iftop until eventually the node came back online as synced..


Recommended MySQL statistics for production environment Other than usual system stats (i/o, ram usage, cpu, load, etc) I am currently collecting questions, qps, running queries and buffer pool hit.

I find qps pretty useless since our production servers uptime is very high, and its average value.

I was wondering about best practices for statistics collection for mysql production server. What other stats should I collect/monitor to understand the load on my server and act quickly without putting more stress on it?

Edit :

I am not looking for a 3rd party solution, I am already using zabbix (and ability to create hand written scripts) to collect stats / monitor our mysql cluster. There is a list of possible stats for collection in this link.  And of course there are stats which are not listed here and can be collected via shell scripts. The real question is what stats must be collected to monitor our cluster efficiently without creating an unnecessary junk full of statistics.

Example : should we get Qcache_hit / Qcache_hit + queries ratio to see if our tables are hot enough ?

I'd highly recommend using MONyog.

  MONyog MySQL Monitor and Advisor is a "MySQL DBA in a box'' that helps
  MySQL DBAs manage more MySQL servers, tune their current MySQL servers
  and find and fix problems with their MySQL database applications
  before they can become serious problems or costly outages.

Our DevOps team uses this extensively for both production and development. These guys have most of the "best practices" baked into the application, so we really don't need to get our hands too dirty in the DBA stuff.


Mysql user accesable with out password from the host machine but not from the remote machine I have setup mysql and created a user 'test' and have given a password for it.

Strange thing I observed is, for test user I am able to access with out password from the machine I have installed mysql but when I try to access remotely then I need to give a password.

Why is this behaviour? any idea?

It is the way MySQL authenticates and current users installed.

When you run this command

SELECT USER(),CURRENT_USER();

you quickly learn that USER() tells you how mysqld sees you trying to login and CURRENT_USER() tells you how mysqld allowed you to login. I discussed this 3 years ago in Why does OS login not exist in the user table, but I can still login to MySQL?

My guess is that whatever CURRENT_USER() is in mysql.user has no password.

You can run SELECT CONCAT(QUOTE(user),QUOTE(host)) userhost,password FROM mysql.user; to see your users. You might note that password is blank.

To solve your issue, you must create a remote username and password.


Unable to import shapefile into PostGisDatabase - problem of connection I have been trying to import/load a shapefile into a PostGis database without any success despite many trials and searching an answer in the documentation.
Configuration
I'm running Windows 64 bits Home (in japanese )
pgAdmin 4
PostgreSQL 12.1
postgis 3
I used 2 differents methods without any results
METHOD 1 - using the  shp2pgsql-gui.exe/postGis shapefile import gui
*Here are the connection details
username postgres
password : i put the same password I usually input in pgadmin4
server host "localhost" then for the port  "5432"
database : gis_analysis  (a postgis database)
*And I get the following error message :

"unable to connect to the database - please check your connection settings"

and the log message

"Connecting:  host=localhost port=5432 user=postgres password='*********' dbname=gis_analysis client_encoding=UTF8
Connection failed."

*I checked TCPIP to check if the port 5432 was opened
I got 3 sessions of pgadmin4 running on remote port 5432  with a state "established" and some sent packets
METHOD 2 - using psql/the windows prompt command
I was planning to use something like this kind of syntax :
shp2pgsql -I -s SRID -W encoding shapefile_name table_name | psql -d database -U
user
But even when I'm running the following command in the prompt command

psql -d gis_analysis -U postgres

*I first get a enter the password : the password does not show up in the terminal  but my understanding is that it's normal.
I typed slowly the
I also copied pasted to make sure I don't make a mistake)
*Eithe way I then  get the following error message (for clarity sake,  I get a japanese message but I put here the standard message in english that seems to be used)

'psql: FATAL: password authentication failed for user "postgress"'

BOTTOMLINE
I can use (import, export, select, alter) a variety of database/tables in pgadmin4 without connection trouble (at worst sometimes it fails because it's too slow but it seems unrelated to me).
I can specifically create a table within my gis_analysis postgis database. I can both import csv file into this table, I can alter/update the table, I can select data from it.
But somehow I cannot import a shapefile
Nor can I use the command prompt, either for the postgis database or any other database.
I am inclined to think it's a username/password problem based on the fact there is no port problem and that I cannot access any kind of database with psql.
But I don't understand why I don't have this connection problem with pgadmin4 and yet when I run either shp2pgsql-gui  or psql  it doesnt work.
I am at my wit's end.
Any insight ?
EDIT : as requested in answer, I m adding a snapshot of connection settings

The pgAdmin4 connection goes to the database named "postgres".
Apparently, a database named "gis_analysis" does not exist, or is not configured to accept connection.


Delete records in Peer to Peer Replication in SQL Server 2008 R2 We have a peer-to-peer replication setup on our real time servers I want to delete records from some of the tables that are involved in the replication. I'm using a stored procedure to do this. Some of the tables have 8+ million records. I want to know if by running the stored procedure on one server is good enough or do i need to run on both the servers? And if I run on just one server how does it impact the performance? Please recommend any best practices for this process. This needs to be run as a job every month.

Peer to Peer is transactional. So the log reader will re-generate the result of the delete, row by row based on the transaction log. The there will be 8+ million delete operations performed on each of the other nodes. And, yes, you only run the procedure on one node. But as you probably understand, having row-by-row as the end result is far from optimal.

An option is to include the procedure in your publication. This mean that SL server will, for you, execute that procedure on the other nodes (instead of sniffing the log).

Above is for transaction replication. I can't say how peer to peer changes things, if they do. Make sure to test thoroughly.

https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/publishing-stored-procedure-execution-in-transactional-replication?view=sql-server-2017


PostgreSQL performance degradation over time on a write intensive db I have observed a weird situation that over time the performance of a query (a combination of queries explained below) degrades, meaning at the start of testing (for a few minutes) the time of the query is 2ms then next day it got to 15ms then day after 30ms.

By query I refer here to a combination of either:

insert a row into table 2, select a row from table 2, select a row in table 3, update a row in table 3, commit
insert a row into table 1, select a row in table 3, update a row in table 3, commit

I wonder what might be the reason of that or which settings from the configuration file should I consider setting and how? I observed the problem on Ubuntu machine where database was set and the primary keys were not added. On the other hand on Win which I develop on it was not observed (it was running constantly on average 3ms per query for 7 days).

I noticed that in the new database (on Ubuntu) there were no primary keys on any table, as oppose to the one I develop on. Could the lack of primary keys have the negative impact on this sort of query?

I thought I will ask this question in the mean time as I am moving my whole db from my development machine to the test one.

On development I used PostgreSQL 8.4 (CPU: Intel i7 740QM, RAM: 6GB), on test there is PostgreSQL 9.1 (CPU: Intel i3-2100, RAM: 3.8GB).

UPDATE: autovacuum related parameters:

#autovacuum = on
#log_autovacuum_min_duration = -1
#autovacuum_max_workers = 3
#autovacuum_naptime = 1min
#autovacuum_vacuum_threshold = 50
#autovacuum_analyze_threshold = 50
#autovacuum_vacuum_scale_factor = 0.2
#autovacuum_analyze_scale_factor = 0.1
#autovacuum_freeze_max_age = 200000000
#autovacuum_vacuum_cost_delay = 20ms
#autovacuum_vacuum_cost_limit = -1

UPDATE2:
It appears that the problem occurs on the development machine as well, but I remember it running fine before. Never the less I did some more testing and did run EXPLAIN ANALYZE on the query, which I takes the most time, and it is an update (I also seen selects take a while on the table as well) presented below:

EXPLAIN ANALYZE UPDATE ais_track SET latest_dynamic = '2012-09-10 22:22:22.222' WHERE mmsi = 123456789 AND ais_system = 1;

The below results are on Win as restore still is in progress on the Ubuntu, and I got this at first:

Index Scan using pk_track on ais_track  (cost=0.00..4.46 rows=1 width=36) (actual time=1.090..2.460 rows=1 loops=1)
  Index Cond: ((mmsi = 123456789) AND (ais_system = 1))
Total runtime: 8.681 ms

Then on 2nd repeat and further repeats of the same update query for a few times I get something of this form:

Index Scan using pk_track on ais_track  (cost=0.00..4.46 rows=1 width=36) (actual time=0.699..1.797 rows=1 loops=1)
  Index Cond: ((mmsi = 123456789) AND (ais_system = 1))
Total runtime: 1.850 ms

After a hundred repeats or so it got to over 2ms.

EXPLAIN ANALYZE for Select:

EXPLAIN ANALYZE SELECT * FROM ais_track WHERE mmsi = 123456789 AND ais_system = 1

1st run:

Index Scan using pk_track on ais_track  (cost=0.00..4.46 rows=1 width=38) (actual time=1.283..2.522 rows=1 loops=1)
  Index Cond: ((mmsi = 123456789) AND (ais_system = 1))
Total runtime: 2.560 ms

After a hundred or so runs:

Index Scan using pk_track on ais_track  (cost=0.00..4.46 rows=1 width=38) (actual time=0.027..1.357 rows=1 loops=1)
  Index Cond: ((mmsi = 123456789) AND (ais_system = 1))
Total runtime: 1.382 ms

The table used in query:

CREATE TABLE ais_track
(
  ais_system integer NOT NULL,
  mmsi integer NOT NULL,
  ext_id integer,
  latest_dynamic timestamp without time zone,
  latest_static timestamp without time zone,
  "name" character varying,
  CONSTRAINT pk_track PRIMARY KEY (mmsi, ais_system)
)

And two indexes:

CREATE INDEX ais_track_mmsi
  ON ais_track
  USING btree
  (mmsi);

CREATE INDEX ais_track_sys
  ON ais_track
  USING btree
  (ais_system);

NOTE: The table size is 11000 and it doesn't change.

Primary and unique keys in all(?) RDBMSes use indexes in order to quickly be able to determine whether a newly inserted value is indeed unique.

The side effect of this is that queries via primary and unique keys are usually "fast".

Now if you haven't defined primary or unique keys on your tables,

You don't have a relational table but you have junk (OK, this is a contentious opinion, but a relational model needs keys on all tables).
queries on this table (in the absence of any other indexes) will become slower as more data is inserted into the table.

So yes, the absence of primary keys will cause this!


Inner Join Table (Mapping table) this is first time i am try mapping table in mysql  i write this procedure  but not work ? whats wrong ?

        DELIMITER $$

       CREATE DEFINER=`root`@`localhost` PROCEDURE `spr_GetInformation`(
                    IN cos_ID INT
                    )
      BEGIN
      SELECT
                     students.ID as 'StudentID',
                     course.ID as 'CourseID',
                     students.name,
                     course.course_name,
                     course.cost_fee,
                     students_Course.net_cost
            FROM
                     students INNER JOIN  students_course on students.ID     = students_course.students_ID
                     inner join course on course_ID = students_course.course_ID
            where
                     course.ID = cos_ID;

END

SELECT
                 students.ID as 'StudentID',
                 course.ID as 'CourseID',
                 students.name,
                 course.course_name,
                 course.cost_fee,
                 students_Course.net_cost
        FROM
                 students_course INNER JOIN  students on students.ID     = students_course.students_ID
                 inner join course on course_ID = students_course.course_ID
        where
                 course.ID = cos_ID;


Defining system scope and boundary I have been given a homework task where I am to create an ER model of a database from a set of client requirements. Another part of this task is to create a diagram that shows the scope and boundary of the database system I create. I am having problems creating a scope/boundary diagram as I have been unable to find any examples in my textbooks or on-line. Is anyone able to suggest or provide an example of how this could be done?

A system scope / boundary diagram is sometimes also called a level zero data flow diagram or a context diagram See here for the Wiki on DFDs and here for System Context Diagrams.

Whereas an ERD is a model of data at rest, DFDs are a model of data "moving" through processes.  Before RDBMS was the predominant data storage mechanism, data was kept in files that were accessed sequentially and was processed a record at a time.  Each time the data was processed it was written into a new file.  In this way, you could think of data "flowing" from one place to another, transforming as it went.  Each data store was shown - often as a rectangle.  Processes were shown as circles ("bubbles") and flows were shown as lines or arrows.

The level zero DFD shows the whole system as a single bubble.  Outside of the bubble are external actors (e.g. users of various types) other systems, and any other data sources and sinks that you might be concerned with.

The idea of a DFD is to incrementally decompose your system into smaller and smaller processes, until the whole system is described.  The level 0/context diagram is the starting point and you drill into each bubble as deeply as necessary to develop a complete system description.

In modern systems, especially where data is stored in RDBMS, the data flow paradigm is not always an especially good fit.  However, the context diagram, or level 0 DFD is still useful for describing the scope or boundary of the system in terms of data sources and sinks.


TempDB Version Store cleanup From what I can find the version store will only clean up versions that are older than the oldest active transaction. Question: Is the oldest transaction database specific or will SQL Server keep all versions, regardless of the database, if there is an older transaction still active, period?

Backstory - SQL Server 2005 SP4 Enterprise hosting around 40 databases. TempDB is currently 102 GB, version store is around 98 GB. One of the applications hosted on the database instance has an open transaction that is 40 days old based on sys.dm...database_transactions. Two separate large databases had extremely heavy use over the last month and we saw consistent TempDB growth coinciding with these operations. We expected some growth. We did not expect it to keep growing.
Question: Are the versions stored in TempDB's version store from these two separate databases still there because a third independent database has a connection that is 40 days old and shows an open transaction_state?

Perfmon counters: Version store is continually growing in the few hours I have tracked it this morning. Version Generation Rate AVG is around 30 kb/s, Version Cleanup rate is 0 kb/s.

Plenty of space left for TempDB, there are around 300 GB of total data files for all user databases, TempDB has grown on average 350 MB per day for each of its 8 data files since the last restart. This behavior is abnormal and investigation revealed the large version store

Answers to comment questions so as not to have a long running comment section:

Q: Why auto-growth on tempdb?
A: TempDB is set to initialize at a size we have found to be appropriate for most of the time. We allow auto-growth in order to handle abnormal database activity. We monitor auto-growth as well.

Q: How do you know the transaction is active and not just an active connection?
A: transaction_state says active in sys.dm_tran_active_snapshot_database_transactions and other stuff. Activity Monitor says each connection has 1 open transaction.

Q: Why is your app so stupid?
A: Its third party. One of many on this instance. I do not know if the behavior is abnormal, or easily fixed.

RESOLUTION

The open transaction(s) where preventing any version store cleanup, so Jon was right, version store cleanup is done independent of databases. Closing the offending transactions allowed version store cleanup to commence. Current theory behind why is from Jon Seigel

  The version store can only clear versions based on the oldest active transaction within the entire instance, to support the use of transaction-level snapshot isolation across multiple databases simultaneously.

If anyone knows for certain or can prove this please do

Referenced question: find-transactions-that-are-filling-up-the-version-store

Referenced documents:
TempDB 2005 WP
Teratrax tuning tempDB
Idera Demystify Tempdb

(Note: this isn't a 100% certain answer, and I don't have references, so let me know if you can prove otherwise.)

The version store can only clear versions based on the oldest active transaction within the entire instance, to support the use of transaction-level snapshot isolation across multiple databases simultaneously. That simply wouldn't work if "old" versions within one database were cleared out in the middle of another database's snapshot transaction.

So, if there's a very old open transaction, the version store will not be able to clear until that transaction either commits or rolls back.


SSDT: Create User in a database not being part of the project I want the user of my SSDT Database Project to be created as a user in another database on the same server and give it permissions to connect and add it to the db_datareader role.

I failed to do so. The PostDeployment Script complains about:

USE command is not allowed
not knowing the Create User OtherDB.USERNAME Syntax

How can this be realized? Isn't there a script option where I can place any command I want?

The action described in the question should not be a problem. I was able to create a Post Deployment script and add the following code:

USE [tempdb];
GO
PRINT DB_NAME();

IF (USER_ID(N'g') IS NULL)
BEGIN
    PRINT 'Creating user [g]...';
    CREATE USER [g] WITHOUT LOGIN;
END;
ELSE
BEGIN
    PRINT 'Dropping user [g]...';
    DROP USER [g];
END;

It builds and publishes with no errors, and executes the commands as expected.

Make sure that your SQL script is truly a Post Deployment script. If it is set to Build Script then it probably will get those errors. Look at the properties of the script and make sure that Build Action is set to PostDeploy.

And please make sure that you are using the latest update of SSDT. The most recent version can be found here:

Download Latest SQL Server Data Tools


Maximum concurrent connections to SQL Server Express 2017 installed on Windows Server 2016 Essentials If I install SQL Server Express 2017 on Windows Server 2016 Essentials, what is the maximum number of users that will be able to connect concurrently across the network to the SQL Server Express instance?

Is is unlimited or does the operating system's limits of 25 users / 50 devices then kick-in?

Also, how does the o/s count concurrent users? e.g. if we have 2 users on different computers connecting to the SQL Server service using the same SQL Server credentials, will the o/s count them as 1 user on 2 devices (hence we can, in theory, get 50 users connecting to the database), or will they be counted as 2 users on 2 devices?

Note that I am not a DB administrator nor a network specialist, just a developer trying to install my first server and trying to understand whether the o/s connection limitations per user only apply to built-in operating system services (like NTFS file sharing) or whether they also affect connections to 3rd party application services like SQL Server Express.

e.g. if I install SQL Server Express on a Windows 2016 server that has 5 user CAL only, will the limit on concurrent connections be the 32K (approximately) set by SQL Server Express or will it be the 5 connections limit set by Windows Server?

Thanks.

@@MAX_CONNECTIONS

The @@MAX_CONNECTIONS function will according to docs:

  Returns the maximum number of simultaneous user connections allowed on an instance of SQL Server. The number returned is not necessarily the number currently configured.

So the above will give us some value the SQL Server is or was limiting the number of connection to.

On most servers this will likely return the default value of 32767.

Another two ways to access the value of the setting are as follows:

SELECT value FROM sys.configurations
WHERE name = 'user connections'

Or

USE master;
GO
EXEC sp_configure 'show advanced option', '1';
GO
RECONFIGURE
GO
EXEC sp_configure @configname = 'user connections';
GO
EXEC sp_configure 'show advanced option', '0';
GO
RECONFIGURE
GO

Why would I want to change this option

As I would suggest is the case with most advanced options, if you're asking the question it's unlikely you need to. The use cases are likely far between, but in general this setting can be used to limit the impact users are having on a server in a very high level sense.


How to restrict in SSMS to allow remote connections only specific IP / Host I cannot change sql server user. How can I restrict in SSMS to allow remote connections only specific IP / HostAddress.

SSMS is only a tool that connects to a SQL Server instance. SQL Server listens on a specific IP address for connections. It doesn't really care which tool is connecting.

SQL Server is not like PostgreSQL that has a pg_hba.conf where you can restrict connections, nor is it MySQL where you assign permissions to a user@source combination when creating the user login.

Answer

It's not currently possible by using simple means (PostgreSQL, MySQL), but if you are willing to start creating Logon Triggers (Microsoft Docs), then you might be able to build a restriction that meets your requirements.

The SQL Syntax for triggers can be found in the article CREATE TRIGGER (Transact-SQL) (Microsoft Docs). The article lists an example for a Logon Trigger.

Because your requirements are pretty dire, I am unable to provide a solid solution. Please edit your question and provide as much details as possible.

USE master
GO
CREATE LOGIN login_test WITH PASSWORD = 'password' MUST_CHANGE, CHECK_EXPIRATION = ON
GO
GRANT VIEW SERVER STATE TO login_test
GO
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'login_test') > 3
    ROLLBACK
END

Alternative

You could equally create a blocking firewall rule or hack the routing table on the server / network.


Why are duplicate permissions appearing on the securables GUI in SSMS? Why are duplicate permissions appearing on the Securables GUI?

There are multiple entries because you can be granted the same permission multiple times, from different "grantors". If that principal (i.e. the "grantee") were already granted that same permission from two grantors, then you would see three entries for that same permission on this screen.

For the "duplicate" entries that you are seeing, the one with the empty "Grantor" column could be used to assign that same permission from a different grantor than the one listed in the other entry for that same permission (in this case it is: dbo). Of course, I am not sure why there is that extra entry when you are connected as principal that already has a granted permission for what would be the same grantor since you can only have one row per grantor. If you change the "With Grant" option as "dbo", for example, it will simply update the entry for "Grantor" = "dbo". I guess the GUI does not check the connection to filter out the empty rows when there is a non-empty "Grantor" row for the same principal as the connection. Then again, it would make sense to have that empty row if it the "Grantor" cell was a drop-down of principals that would allow for selecting one to be used in the AS clause of the GRANT statement, but it isn't, so having the empty row seems more confusing than practical.

Try the following to see this in action:

USE [tempdb];

CREATE USER [GrantPark] WITHOUT LOGIN;
CREATE USER [GrantWard] WITHOUT LOGIN;

CREATE TABLE dbo.MultiplePermissionsTest (Col1 INT);

SELECT dp.[permission_name], dp.state_desc, USER_NAME(dp.grantee_principal_id)
       AS [Grantee], USER_NAME(dp.grantor_principal_id) AS [Grantor]
FROM   sys.database_permissions dp
WHERE  OBJECT_NAME(dp.major_id) = N'MultiplePermissionsTest';

GRANT SELECT ON dbo.MultiplePermissionsTest TO [GrantPark] WITH GRANT OPTION;

SELECT dp.[permission_name], dp.state_desc, USER_NAME(dp.grantee_principal_id)
       AS [Grantee], USER_NAME(dp.grantor_principal_id) AS [Grantor]
FROM   sys.database_permissions dp
WHERE  OBJECT_NAME(dp.major_id) = N'MultiplePermissionsTest';

GRANT SELECT ON dbo.MultiplePermissionsTest TO [GrantWard];

SELECT dp.[permission_name], dp.state_desc, USER_NAME(dp.grantee_principal_id)
       AS [Grantee], USER_NAME(dp.grantor_principal_id) AS [Grantor]
FROM   sys.database_permissions dp
WHERE  OBJECT_NAME(dp.major_id) = N'MultiplePermissionsTest';

GRANT SELECT ON dbo.MultiplePermissionsTest TO [GrantWard] AS [GrantPark];

SELECT dp.[permission_name], dp.state_desc, USER_NAME(dp.grantee_principal_id)
       AS [Grantee], USER_NAME(dp.grantor_principal_id) AS [Grantor]
FROM   sys.database_permissions dp
WHERE  OBJECT_NAME(dp.major_id) = N'MultiplePermissionsTest';

Returns:

permission_name    state_desc                 Grantee      Grantor
(no rows)

permission_name    state_desc                 Grantee      Grantor
SELECT             GRANT_WITH_GRANT_OPTION    GrantPark    dbo

permission_name    state_desc                 Grantee      Grantor
SELECT             GRANT_WITH_GRANT_OPTION    GrantPark    dbo
SELECT             GRANT                      GrantWard    dbo

permission_name    state_desc                 Grantee      Grantor
SELECT             GRANT_WITH_GRANT_OPTION    GrantPark    dbo
SELECT             GRANT                      GrantWard    dbo
SELECT             GRANT                      GrantWard    GrantPark

Now, go to SSMS and expand the "System Databases" folder, then go to "tempdb", and then to "Tables".  Right-click on "dbo.MultiplePermissionsTest" and go to "Properties", then to "Permissions", and select "GrantWard". Scroll down a little and you should see 3 rows for "Select", each with a different value for "Grantor": (empty), dbo, and GrantPark.


Does SQL Server ignore STATISTICS for varchar columns as this article suggests? (I don't believe this question is a duplicate of this question from 8 years ago, as I'm not asking about the advantages of oversized columns, I'm asking about the behaviour demonstrated in the linked article below.)
This recent (2017) article from SQLPerformance.com demonstrates how varying the maximum length n for a varchar(n) column affects query plan row-size estimates and sorting-buffer size estimates that can lead to subpar performance and memory allocation warnings.
In it, the author claims (emphasis mine):

From here we see that, the bigger the column definition, the higher the estimated row and data size. In this simple query, the I/O cost (0.0512731) is the same across all of the queries, regardless of definition, because the clustered index scan has to read all of the data anyway.

But there are other scenarios where this estimated row and total data size will have an impact: operations that require additional resources, such as sorts.

When I read that claim (in bold) I was surprised because I thought that SQL Server would get fairly accurate row-size estimates from its sampled STATISTICS objects maintained on those same tables. Especially given the SELECT AVG(LEN(email)) query in the article shows that no column has a value exceeding 77 characters.
The article also explicitly performs an ALTER INDEX ALL ON dbo.Table REBUILD - which this DB.SE posting says will will also automatically update STATISTICS.
(though I'm surprised that the word "statistics" doesn't appear anywhere in the SQLPerformance article at all - so maybe in the author's case the statistics weren't updated at all due to some machine-configuration and they didn't notice?)
Does SQL Server only use the varchar column length limit for row-size estimates? If not, then why does the SQLPerformance article describe the same?

Does SQL Server only use the varchar column length limit for row-size estimates? If not, then why does the SQLPerformance article describe the same?

Correct. SQL Server uses only the varchar (max specified) length when estimating row size. The SQLPerformance article accurately describes the estimated row size measurement.
The longer answer
In his example in the linked article, Aaron rebuilds all indexes to ensure all versions of the query have an equal playing field as far as both index size and statistics so that the execution plans for all cases are "ideal" and (as the experiment proved) almost equal, but not quite.
Statistics are used to estimate how many rows will be returned, not how much memory is granted for the execution of a query.
In the article, Aaron says (emphasis mine):

SQL Server has to take into account how much data might be in a column. It doesn't do this based on actually profiling the data, and it can't make any assumptions based on the <= 201 histogram step values. Instead, it has to estimate that every row holds a value half of the declared column size. So for a varchar(4000), it assumes every e-mail address is 2,000 characters long.

Aaron's reference to "the histogram step values" is a reference to the statistics histogram. The statistics histogram contains knowledge of at most 201 data values from the table. It knows the actual length of those (up to 201) explicit values, but it has no idea about the values in between those.
Additionally, Statistics are based on a sample of data, so there may be rows that were not analyzed as part of the sample, and relying on min/max/avg length of data from stats would be another opportunity for outdated or unrepresentative samples to adversely affect query execution.