Changing varchar(max) to varchar(255) requires rebuilding table Our developers (prior to me being DBA) set the data type for a field to VARCHAR(MAX) when in reality it only needs to be VARCHAR(255). This wasn't discovered until after our table had grown to over several million records and almost a terabyte of data. When I go to design mode on that table to test this change in our test environment I find that the script it creates makes a _tmp table copy of the table and then drops the original and renames _tmp to the original. I seem to remember learning the reason behind this had something to do with having to move the data from the "LOB" section of a record to the fixed length section of a record (Or so my memory recalls). However, I can't find any documentation on why exactly this occurs (instead of just changing the size in place). Can someone point me in the right direction so I can better explain to management why making this change now on a several million record table is going to take a long time.

The script that SSMS generates is not the best one.

You should be able to use simple ALTER TABLE:

ALTER TABLE TableName
ALTER COLUMN ColumnName VARCHAR(255) NOT NULL

Obviously, make sure in advance that the text that you store in this column would fit into 255 characters limit.

I'd strongly recommend to try it in test environment first to get an idea how long it would take. I'm not sure, and it may depend on the version and edition of your server, but it is likely that this kind of change would be performed not as a metadata change, which means that for a terabyte of data it would take a significant amount of time.

This question (Why does ALTER COLUMN to NOT NULL cause massive log file growth?) has a lot of details about ALTER COLUMN.


Count violated unique constraint caused by INSERT statement I would like to add rows from table2 into table1 and see how many conflicts of the type "duplicate key value violates unique constraint" result from this in PostgreSQL. pkey is a primary key, so there a unique constraint on it. Unfortunately and apparently, conflict_action does not permit incrementing a counter.

INSERT INTO table1 (pkey, col1, col2)
  SELECT pkey, col1, col2 FROM table2
  ON CONFLICT ...;

So is there another (elegant) way for tackling this either by calculating or retrieving that count?

Insert not needed for to count:

WITH
cte1 AS ( SELECT COUNT(*) cnt FROM table1 ),
cte2 AS ( SELECT COUNT(*) cnt FROM table2 ),
cte3 AS ( SELECT COUNT(*) cnt FROM table1 JOIN table2 USING (pkey) )
SELECT cte1.cnt "Records in table1",
       cte2.cnt "Records in table2",
       cte3.cnt "Conflicts count",
       cte2.cnt - cte3.cnt "Potential inserts count"
FROM cte1, cte2, cte3;

And insert only non-conflicted records - it will be less expensive.


bulk insert for loas data declare @DATADIR varchar(128)
set @DATADIR= 'C:\SQL2012\SH_DATA'

msg:Command(s) completed successfully.

EXECUTE('bulk insert CHANNELS from '''+@DATADIR+'\CHANNELS.txt''
        With (firstrow = 2,
 fieldterminator=''|'',
 KEEPNULLS,
rowterminator=''\n'',
 MAXERRORS = 10000)
 ')

msg:Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@DATADIR".

I am using SQL2012.

You shouldn't concatenate inside EXEC like that. Instead, try this (most importantly, it lets you debug your statement before executing it):

DECLARE @datadir VARCHAR(128) = 'C:\SQL2012\SH_DATA';

DECLARE @sql NVARCHAR(MAX) = N'BULK INSERT dbo.CHANNELS FROM '''
  + @DATADIR + '\CHANNELS.txt'' WITH ...;';

PRINT @sql;
-- EXEC sp_executesql @sql;

Also, you need to execute all three of these statements together. You can't execute the declares, then execute the remainder independently, which it seems like you did above.


Execute stored procedure in another server from sql agent job I am trying execute stored procedure in remote server in sql agent job using sqlserver 2008 r2

  select * from openquery([1.1.1.1],'exec [DB_Name].[dbo].[sp_ProcName]')

Or

EXEC [1.1.1.1].[DB_Name].[dbo].[sp_ProcName]

but I got the folowing error:

Could not find server '1.1.1.1' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

(I type the correct IP)
Thanks!

As the error message suggests, OPENQUERY looks for a linked server.  If you don't have that set up, and don't have the rights to get that set up, then perhaps using an OPENROWSET query might be the way to go.  This will allow you to connect to a datasource on a more ad-hoc basis, by writing a query which contains a full connection string.

In your case, you may get the results by using something like:

SELECT spresults.*
FROM OPENROWSET('SQLNCLI', 'Server=1.1.1.1;Trusted_Connection=yes;',
                'EXEC [db_name].dbo.[sp_ProcName]') AS spresults

CAVEAT:  This will fail at runtime if your SP returns multiple resultsets.

CAVEAT 2: This will also fail if Ad Hoc queries haven't been allowed.  To sort that out, you need to run the appropriate sp_configure statement(s):

exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'ad hoc distributed queries', 1
reconfigure

Of course, in order to run sp_configure, you'll need the appropriate rights in SQL Server.  According to the "Permissions" section of the sp_configure documentation, that's "ALTER SETTINGS", which is by default part of the sysadmin and serveradmin roles, but may be assigned to other roles too.


MS SQL running out of Disk Space - can FileGroups be used to free up some space? Problem

We have a Sql Server 2014 with a 1TB attached disk (on Azure) that's running out of disk space. We have about 20GB's left (maybe a few weeks of space). As such, we need to move some data off the CURRENT disk and onto a NEW disk.

Details

Server:

Microsoft SQL Server 2014 - 12.0.2548.0 (X64)
    Jun  8 2015 11:08:03
    Copyright (c) Microsoft Corporation
    Web Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

MS-SQL 2014 is installed onto a classic Azure VM. This VM is in a classic VNET. The storage DISKS are also classic. As such, we couldn't just expand the existing disk. MS-Support said that we need to update all of these, if we wish to leverage the modern Azure Storage to allow disk resizing, using the newer SSD disk, etc. TL;DR; we can't take this offline for hours including all the other subsystems that communicate with the VM via IP address. Now, don't turn this into a flame-fest .. this is what we've been given to work with and will need to fix all of this up later.

So right now, an idea what to try and leverage FILEGROUPS and to move one or more tables into a FILEGROUP and push this FILEGROUP onto another DISK which we have attached.

So the questions here are:

Is this a really really crazy and lame idea, first of all?
If it's crap but OK, then will using FILEGROUPS actually move the data from the CURRENT disk onto the NEW disk (which frees up some disk space on the nearly full CURRENT DISK)?
If this is still possible, does moving these tables mean the data is locked/unavailable ... which means were still back to our initial problem :(
What about logs? Moving this data means the logs just get a copy of this? (we are doing hourly and weekly backups, I believe).

It's ok if the data is small, but here's a quick look at some of our tables...

That first table is massive (with respect to the rest of the data). 750GB ish.

I was thinking of moving maybe the 2nd, 3rd of 4th line, in the result image. Remember how I said the infrastructure is all on OLD classic stuff? This means the HD's are old and slow so copying data could take some time also.

As an example, I just tried to copy the .mdf's (this DB has 1 main mdf and 2 other small ones) from OLD over to NEW disks. that had a quick ETA of 24 hours.

Having the site offline for a few hours is totally acceptable. We can take stuff offline when our customers are asleep. but ... 24 hours .. that hurts. The 24 hour idea was a simple test for:

Create new 2TB Disk (if possible)
turn off sql server.
copy mdf + log files to new disk. (24 hours or so)
point filegroups from old location to new location
start sql server again.

Now, we're open to ideas and I know that stack exchange is not a site for 'opinions' so I'm trying to keep this on target with a suggested answer and to get feedback on it ... but we're open to other solutions to reduce the offline time.

So - can anyone help please?

Update 1

Here's a screen shot of the current FILES for this DB.

While it will work, it's not necessary to move tables to a new filegroup.

If you simply add a file to the database's existing filegroup(s) SQL Server will start using the new file(s).

SQL Server uses a "proportional fill algorithm" when a filegroup has multiple files.  So if you add a new file (perhaps on a different volume) to a filegroup, SQL Server will write new data to that filegroup until it's the same percentage full as the current file.


How to check if a client can use windows authentication My ORACLE (10g) sqlnet.ora has SQLNET.AUTHENTICATION_SERVICES=(NTS). I need to validate that no client is using Windows authentication to connect. What can I check next to make sure they don't ?

You want to check AUTHENTICATION_TYPE in the DBA_USERS view. Users who are authenticated externally (EXTERNAL in the view), are authenticated by the operating system. A user cannot be both internally and externally authenticated, or be altered to switch authentication methods once created. If you haven't deliberately created externally authenticated users, then you won't have a problem.
See here: https://docs.oracle.com/en/database/oracle/oracle-database/18/ntqrf/overview-of-manually-administering-external-users-and-roles.html#GUID-8A209E30-4BA9-4993-80F5-AF0A82ADEB5B


How do I design a database for continents, countries, regions, cities and POIs? I am not a database designer so I'm having trouble designing database with GIS information. The goal is to create a system with continents, countries, regions (including states, sub-regions, provinces), cities and places in cities. Each of these elements will contain some text information and related stuff. As database we are going to use PostgreSQL with PostGIS.

My question is, how do I design the database for this system? I was thinking of 2 tables polygons and points, but I'm not sure if it's good way of thinking.

Actually what we need is some hierarchical base for relationships between countries, cities, regions, etc. The main goal of the application will be collecting tourist data from many sources about specific cities, regions, countries and so on, and store it in database. Let's say we have a city Rome; we collect some info about this city into the database from couple sources. And we need to know that Rome is in the province Rome, sub-region Lazio in region Lazio, country Italy. So the system should be flexible to allow us to create such relations from the real world.

That's why I would choose two tables:

polygons - which can store countries, regions, sub-regions, provinces etc.
points - which can store cities and POIs

i suggest 2 options

Option 1: if you want a RBDMs, i think 2 tables will be sufficient

Table 1 (Lookup)
-LocationID
-Continent
-country
-region
-City
-Landmark (granularity)

in this Table since its a lookup so i suggest keeping it Denormalized, it will save you alot in your coding specially when joining tables and searching and reporting. and maintenance is  easier, then 5 separate tables

the other table is the table to collect the data

Table 2 (Master)
-Tourist
-LocationID
-ExtraFields
-...

so in this table you collect info about tourists and all you have to do is store the locationid, which is your granularity, this way the Locationid, has the Landmark,city,region,country,continent
Since the Landmark is definetly in a city and the city is in a country and the country is in a region.. (no need for 5 tables to keep them separate, there will be a big chance of invalid inputs or data if not maintained properly)

Option 2:

i suggest graph database, check this post at SO , about using Neo4J for GIS and an implementation of GIS by Neo4J


filling a table by triggering multiple tables on insert I have a table m contains the following fields:

id int(11) not null auto_increment, //PK
name varchar(100) null,
something_else varchar(100) default 'not important'

I need to fill up table m by using this trigger

CREATE TRIGGER some_name AFTER INSERT ON `x` FOR EACH ROW INSERT INTO m (name) VALUES (NEW.name);

which I got from this dba question

but the problem is that each row on m should be filled using after insert on multiple different tables like x,y. So, how can this be done.

I hope that my question is clear enough as it is in my mind.

Update 1:
table m has the following row:

id - name - something_else
1  - null - not important

on after insert for table x, table m row should become like:

id - name     - something_else
1  - newthing - not important

then, on after insert for table y, table m row should become like:

id - name     - something_else
1  - newthing - new value

Hence, the row on table m had been filled by multiple inserts on different tables and updated accordingly.

The trigger code you just displayed in your question needs to be manually defined on each table you intend to copy from. For tables x, y and z the answer to your question is:
CREATE TRIGGER some_name AFTER INSERT ON `x` FOR EACH ROW INSERT INTO m (name) VALUES (NEW.name);
CREATE TRIGGER some_name AFTER INSERT ON `y` FOR EACH ROW INSERT INTO m (name) VALUES (NEW.name);
CREATE TRIGGER some_name AFTER INSERT ON `z` FOR EACH ROW INSERT INTO m (name) VALUES (NEW.name);

I suggest you make table m use MyISAM to prevent all potential deadlocks.
UPDATE 2013-10-15 19:39 EDT
First, define the table m  like this:
CREATE TABLE m
(
    id int(11) not null auto_increment, //PK
    name varchar(100) null,
    something_else varchar(100) default 'not important',
    primary key (id),
    unique key (name)
) ENGINE = MyISAM;

Then, make the trigger like this:
CREATE TRIGGER some_name AFTER INSERT ON `x`
FOR EACH ROW
    INSERT INTO m (name) VALUES (NEW.name)
    ON DUPLICATE KEY UPDATE
    SET something_else = 'new thing'
;

I would suggest that make something_else have the date and time as well
CREATE TRIGGER some_name AFTER INSERT ON `x`
FOR EACH ROW
    INSERT INTO m (name) VALUES (NEW.name)
    ON DUPLICATE KEY UPDATE
    SET something_else = CONCAT('new thing ',NOW());
;

and maybe the table where the insert came from
CREATE TRIGGER some_name AFTER INSERT ON `x`
FOR EACH ROW
    INSERT INTO m (name) VALUES (NEW.name)
    ON DUPLICATE KEY UPDATE
    SET something_else = CONCAT('new thing from x : ',NOW());
;

Give it a Try !!!


SSIS convert date in mmddyyyy format to date format using SSIS expression I'm receiving dates in a mmddyyyy format but want to convert it to the date format in sql/ssis yyyy-mm-dd, how can I do so using an SSIS expresion.
example of dates I'm reciving are 03051978 but want it to appear in 1978-03-05.
Thank you

You have to use substring to get the parts needed for year monh and so on
(DT_DATE)(SUBSTRING(yourcolumn,5,4) + "-" + SUBSTRING(yourcolumn,1,2) + "-" + SUBSTRING(yourcolumn,3,2))


assistance with mongodb setup on windows server I am currently setting up Mongo DB on a dedicated web server. I am not experienced with Mongo so I have some questions around its setup. I have done research in to Mongo etc but would like some assistance/direction on the following:

on the windows server this will be running on I will have an anti virus running on the server. Are there any files on mongo that I should avoid scanning?
in the area of clustering, what is the default/recommended setup for this? In what manner should it be setup in an enviornment it is storing data for a front wcf application.

You shouldn't scan the folder in which mongodb stores it's data files, as this will degrade performance. The location is set by using the --dbpath C:\Where\You\Want\The\Files flag at runtime.

With regards to best practices for clustering, I'd recommend taking a look at the Infrastructure Requirements for a Sharded Cluster page.

At a minimum, you will need three config servers, two or more shards and one or more MongoSes


Unable to deny remote access to Postgresql even if there is no entry in pg_hba.conf I have 5 Postgres Servers, with IP addresses let's say as A,B,C,D and E.
A is the master server and remaining are the child servers which need to access A. In the postgresql.conf file of A, the listen address has been set to '*'.

Now, let's say I want B to access A remotely. For that, the pg_hba.conf file of A should contain an entry with the IP address of B (along with other necessary data), like this for example:

host all    all     IP_Address_of_B 32    md5

But, the problem which I am facing is that even if I am entering the details of any one of the child servers in the pg_hba.conf file of A, the access to A is enabled to all of the child servers. And if I remove that entry, then the access is disabled for all.

As far as I know, if the entry of B is present in the pg_hba.conf file of A, the only B must be allowed to access A, not the remaining. But, in my case, either all have the access or none.

Is their a way to enable only B (or only a selected servers, not all) to access A ?

The IP address should be followed by either a slash and a mask length (like /32) with no whitespace, or by whitespace and a netmask (like 255.255.255.255).  What you seem to have here is whitespace and mask length.  So you get a mask length being interpreted as a netmask.  I am surprised that that doesn't throw an error upon parsing, but in any event it doesn't do what you want.


sqlite calculating percentage I would like to select items from a table where their frequency count is less than 5% of the total amount.

For example, if I had a table of names, I would like to select the names that appeared less than 5% of the total.

A simple GROUP BY would suffice I think:

SELECT name,
       1.0 * COUNT(*) / (SELECT COUNT(*) FROM names) AS percentage
FROM names
GROUP BY name
HAVING 1.0 * COUNT(*) / (SELECT COUNT(*) FROM names) < 0.05 ;


Hiding a SQL Server vs Turning Off SQL Browser Can anyone explain the difference between turning off the SQL Browser service and Hiding an instance through the SQL Server Configuration Manager?

I've always assumed they do the same thing and have have hidden the server as a backup in case the SQL Browser service ever gets turned on but cannot find any documentation confirming this.

Thanks in advance!

Can anyone explain the difference between turning off the SQL Browser service and Hiding an instance through the SQL Server Configuration Manager?

To hide an instance of SQL Server, you need to set HideInstance flag to YES.

When you hide an instance, it will indicate that sql server browser should NOT respond with information about this server instance. Note that hidden instances support only shared memory connections. TCP/IP is allowed (as long as the port is 1433 or you specify) [Thanks to @AaronBertrand for highlighting this].

SQL Server uses the SQL Server Browser service to enumerate instances of the Database Engine installed on the computer. This enables client applications to browse for a server, and helps clients distinguish between multiple instances of the Database Engine on the same computer.

Refer : Hide an Instance of SQL Server Database Engine and Why listing all of your SQL Servers does not always work ?


Extended Events: Specify Session Data Storage left blank What happens if the Data Storage is never configured?

If you specify no target, but choose to use an Extended Events wizard template for the the session, it is created with a single histogram target.

You can see this by clicking through to the last page of the wizard, then hitting the "script" button.

This is a sample I created using the "script" button.  Below this is one where I specified a ring buffer as a target:

CREATE EVENT SESSION [test] ON SERVER
ADD EVENT sqlserver.additional_memory_grant,
ADD EVENT sqlserver.lock_acquired(
    ACTION(sqlserver.query_hash)
    WHERE (([package0].[greater_than_uint64]([database_id],(4))) AND ([package0].[equal_boolean]([sqlserver].[is_system],(0)))))
ADD TARGET package0.histogram(SET filtering_event_name=N'sqlserver.lock_acquired',source=N'sqlserver.query_hash')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

This is the one with the ring-buffer:

CREATE EVENT SESSION [asdf] ON SERVER
ADD EVENT sqlserver.additional_memory_grant,
ADD EVENT sqlserver.lock_acquired(
    ACTION(sqlserver.query_hash)
    WHERE (([package0].[greater_than_uint64]([database_id],(4))) AND ([package0].[equal_boolean]([sqlserver].[is_system],(0)))))
ADD TARGET package0.histogram(SET filtering_event_name=N'sqlserver.lock_acquired',source=N'sqlserver.query_hash'),
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

As you can see above, the only difference is the lack of a ring-buffer target in the first result, even though they bot have a histogram target.

However, if you do not use a template when building the Extended Events session, the session is created like so (I just added a single event and action):

CREATE EVENT SESSION [test] ON SERVER
ADD EVENT sqlserver.additional_memory_grant(
    ACTION(sqlserver.client_pid))
WITH (STARTUP_STATE=OFF)
GO

This results in the session only providing live data, which is viewable via the "Watch Live Data" context-menu of the XE Session:

You can obtain a live stream of the rows returned by the Extended Event by looking at the very lightly documented system function, fn_MSxe_read_event_stream function, as in:

DECLARE @source nvarchar(256);
DECLARE @sourceopt int;
SET @source = N'test';
SET @sourceopt = 0;

SELECT xes.type
    , CONVERT(xml, CONVERT(varbinary(max), xes.data))
FROM sys.fn_MSxe_read_event_stream(@source, @sourceopt) xes

However, the format of the data returned is image, and apparently "not customer readable".


Help with designing a multi relational schema / domain model I am in the process of developing a web application with asp.net MVC. I am using Entity Framework to make a connection to my database.

I am having trouble with a specific concept in my program. I will explain below.

I have a Network class which resembles a network in real life which has a frequency.

I then have a Block class. A block is a certain section of devices on a networks.

I also have a Customers Class.

Here are my current Models of the above classes:

Network:

  public class Network
{
    public int networkID { get; set; }
    public String name { get; set; }
    public int startCode { get; set; }
    public decimal frequency { get; set; }
    public virtual List<Block> blocks { get; set; }
}

Customer:

 public class Customer
{
    public int customerID { get; set; }
    public String name { get; set; }
    public String shortName { get; set; }
}

Block:

 public class Block
{
    public int blockID { get; set; }
    public decimal start { get; set; }
    public int end { get; set; }
    public int blockSize { get; set; }
    public Customer customer { get; set; }
}

Ok now my problem comes with this.

A network has many blocks.
Each block belongs to a single client.
A client may have many blocks in one network, and can also have many blocks in many networks.

Is my above configuration correct / sufficient to store my certain scenario. But I also then need to be able To browse a client and query all his blocks which he has.

It might help if you also had some table descriptions in your question, but here goes...

  A network has many blocks.

You could do this very simply with a network_id in each block. That way there is no restrictions on how many blocks can reference a network, but you ensure that each block is only in one network.

  Each block belongs to a single client. A client may have many blocks in one network, and can also have many blocks in many networks.

Having a client_id in your block would ensure that each block can only reference one client, but a single client could be referenced by many blocks (and it won't matter which network they're in). So maybe your block table needs to look something like this:

block
-----
  ID
  client_id (FK to client.id)
  network_id (FK to network.id)
  [other fields...]

  I also then need to be able To browse a client and query all his blocks which he has

The SQL for that could look something like:

select client.name, client.id, block.id
from block
inner join client
on client.id = block.client_id
where client.id = $SOME_PARAMETER_FOR_CLIENT_ID


Mysql 5.7 innodb_buffer_pool_size - zero I/O? I have a question regarding Mysql server 5.7, put the correct value for innodb_buffer_pool_size or increase RAM. Maybe I'm wrong, I'm not an expert, but let me explain what is it about.
Server configuration:
Mysql 5.7.32 server is running on Ubuntu 20.04 server, specs are 125GB of RAM, 64 cores of CPU and it have also 3TB partition for the database. SSD is nvme and CPU is AMD EPYC 7502P, so the machine is dedicated.
Here is an mysqld.cnf file conf:
[mysqld]
#
# * Basic Settings
#
#innodb_monitor_enable      =   all
#performance_schema     =   ON
default-storage-engine      =   innodb
tmpdir              =   /data/mysql_tmp
skip-log-bin
innodb_support_xa       =   0

#
# * innodb settings
#
innodb_read_io_threads      =   64
innodb_write_io_threads     =   64
innodb_buffer_pool_instances    =   64
innodb_buffer_pool_size     =   85G
innodb_buffer_pool_chunk_size   =   256M
innodb_io_capacity      =   2500
innodb_io_capacity_max      =   5000
innodb_log_file_size        =   13G
innodb_log_buffer_size      =   32M
innodb_flush_log_at_trx_commit  =   0

query_cache_size        =   0
query_cache_type        =   0
sort_buffer_size        =   10M
join_buffer_size        =   1M
read_buffer_size        =   1M
key_buffer_size         =   16M
thread_cache_size       =   100
read_rnd_buffer_size        =   1M

max_allowed_packet      =   1073741824
net_buffer_length       =   1048576
#innodb_fill_factor     =   50
max_connections         =   150
table_open_cache        =   3000
table_open_cache_instances  =   55
back_log            =   65535
wait_timeout            =   31536000
connect_timeout         =   31536000
interactive_timeout     =   31536000
net_read_timeout        =   10000
net_write_timeout       =   10000

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            =   0.0.0.0

# Procedures tunning
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
event_scheduler         =   on

The next thing that I can share is that on this server we have 18 different databases, a lot of them are small.
The main problem is with 2 big databases. First database have around 700GB and around 100 tables. Second have 200GB of data and around 15 tables. And those two constantly expands (each day).
Let me now explain what I need. I got some ticket on my work to do make adjustments with the server regarding and adjusting innodb_buffer_pool_size , and I need to calculate how much more RAM we need to add to this server so Mysql can put data in innodb_buffer_pool_size.
Because there is another service that need to pull the data in some their weird specific way and we wanna to increase speed up for them somehow.
But I'm not sure how can I do that and is it possible.
Question is:
Can we just somehow put specific large tables from these two databases in innodb_buffer_pool_size, like to tell Mysql Server just keep them in RAM bassed? Until we have some zero I/O ? Or Mysql can do there something or some other service can do this?
Some lead guy at my work told me that this is possible, like to subtract unneeded tables, or exclude tables? Combined with this "zero I/O" stuff !? Really don't know what to google and what exactly can I do there.
I am aware of memcached, also to have the other SQL server installed, but that isn't an option right now.
I've tried to tune the server with a PERL script called mysqltuner.pl , also tried with some queries similar from this page: How large should be mysql innodb_buffer_pool_size? , and I got the info like install 2TB of ram and simillar. Which for me is insane.
Hope someone can help and suggest/guide for some solution to this or maybe I'm looking wrong on this with innodb_buffer_pool_size variable.
Thank you!

Your ulimit -a report indicates Open Files is limited by the OS to 1024.
From your OS Command Prompt, ulimit -n 32000 would increase this limit dynamically.
Shutdown then restart your instance would make the additional file handles available and reduce opened_tables, opened_table_definitions, opened_files.
For this change to persist over OS restarts, follow this url - https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/

but use 32000 rather than 500000 used in their example.

Suggestions to consider for your my.cnf [mysqld] section
Rate Per Second - RPS
innodb_lru_scan_depth=100  # from 1024 to conserve 90% of CPU cycles used every second for the function
innodb_buffer_pool_dump_pct=90  # from 25 (percent) to reduce warmup bp time required
innodb_io_capacity=2000  # from 2500 to extend life of your NVME device
read_buffer_size=192K  # from 1M to reduce handler_read_rnd_next RPS of 160,774

REMOVE innodb_io_capacity_max to allow MySQL to
auto calc the max as innodb_io_capacity*2
Many more opportunities exist to improve your configuration.  View profile, Network profile for contact and free downloadable Utility Scripts to assist with performance tuning, specifically our findfragtables.sql will be helpful.
Monitoring SHOW GLOBAL STATUS LIKE 'innodb_data_reads'; divided by uptime for read rate per second should be your primary consideration for when you need more RAM to support your innodb_buffer_pool.  The provided SHOW GLOBAL STATUS data indicates RPS of 16.  Many people are OK with up to 100 RPS.  Less RPS is desirable, when funds are available. 0 RPS is likely unnecessary due to the way innodb only ages out of RAM the least recently used data.


How to store a database table in multiple disks with assigned ratio? I am going to deploy database tables to multiple disks.

My database layout would be like following example:

There are 3 tables (Customer, Order, Product) and 4 disks (disk_A, B, C, D).

Customer: 5% on disk_A, 95% on disk_B
Order: 25% on disk_A, 50% in disk_C, 25% on disk_D
Product: 100% on disk_A

Q1: Dose any DBMS support such data layout?

I tried tablespace in following DBMS but they do not work.

1) PostgreSQL: It doesn't support  tablespaces across multiple disks. Is it?

2) MySQL and Oracle: It supports multiple disk tablespaces. But I
cannot control how much data store on each disks. (Eg. 25% in A, 0% in
B, 50% in C, 25% in D). Is it?

Q2: Which DBMS support that?

To use multiple disks for IO, your best bet is to use RAID, preferably hardware RAID, giving both increased reliability and performance both.

Trying to do it by hand is horrifyingly bad; you're unlikely to get a good performance balance, that performance balance probably, won't survive table growth, and a single disk failure destroys your data - you're effectively suggesting RAID 0 (striping) with all the disadvantages, but without most of the benefits.

If you insist on the mindbogglingly crazy idea you're looking at, then MS SQL Server Enterprise Edition (and likely other products) has partitioned tables, and that would allow you to have one table with partitions on different filegroups, and then you could put the different files of the filegroups on different partitions.  You'd control proportions by the choice/control of the data the partitioning is done on.

The budget crazy idea is multiple tables to store different percentages of the data (by hand partitioning), and then UNION them into a view.

Again, don't do this - use RAID*.  Heck, even use software RAID if you have too low a budget for hardware RAID!

* RAID 1, 10, 5, 50, 6, and 60 are approved variants for databases.  Do NOT use RAID 0.


Query list of logins that have logged in the last X days on SQL Server Is there any good query that included internal system objects like sys.dm_exec_sessions or sys.dm_exec_connections that would list logins that have connected to the SQL server over the last X days? 15, 30 days? I am also looking for any other query that may help or related to this.

As mentioned by @Tibor, unless you set up logging or trace you cannot retrieve that information natively. You do not need to use trigger.

Using SQL Server Management Studio

Configure Login Auditing (SQL Server Management Studio)
Once you set up above you can query the error log and retrieve the information. You can also save the date in a table. I did describe in this blog post how to read from the error log.

Using Extended Event Trace

Audit Login Event Class

The Audit Login event class indicates that a user has successfully logged in to Microsoft SQL Server. Events in this class are fired by new connections or by connections that are reused from a connection pool.

Steve Jones explained in detail in his blog post: Tracking Logins with Extended Events


Oracle Backup and Recovery My backed up archivelog file format is 'al_%U'. During recovery it shows the following error:

RMAN-06054: media recovery requesting unknown log: thread 1 seq 17 lowscn 671075

How to determine which archivelog is necessary? In RMAN format I can't indicate thread and seq  yes(for simply finding archivelog during this error)?

I should have that archivelog... If I really have that archive log how to indicate it to my database?

Thank You a lot...

I wil be very glad if you provide me with some book that clearly explains backup and recovery strategy and examples(In Oracle)...

GREAT THANKS!!...

Partial Answer of my question:

If I type

RMAN> list backup;

It shows me the thread, Seq and Low SCN of the logfiles in the backup set :D great!!

But there is one missing archivelog backup, what to do? How to register?(I am not using Catalog!)

You need to use the catalog command in RMAN to make the file known to the database (don't worry, this does not depend on a recovery catalog!)


Any Issues, concerns on having a Stored Procedure in Subscriber Database I have a database (ms-sql) that is replicated(transactional) to a subscriber.

I'd like to add a stored procedure to the subscriber only

Is this possible?
Does it affect replication?
Any potential Issues?
Under what circumstance could the sp be erased/cleared?

Subscriber can have different set of objects than publisher. Its perfectly fine to have a stored procedure on subscriber ONLY.

@Tara is correct, it wont drop the proc at the subscriber when you reinitialize the publication.

The only time that I think you would have to remember to recreate the SP is when you have to Initialize a Transactional Subscription from a Backup of publisher .


I need to select random groups of data I am working on a query that requires the retrieval of 20 random rows from a cash book table, which I am doing using 'Top 20 & order by newId()'.
Let me explain: The cash book table is a list of customers with Account Number, Name, Balance. Each customer, depending on the number of Accounts they have will return a different number of rows. ie: one account will return one row, 7 accounts will return 7 rows etc.
The number of Customers can be anything between 150 - 300 depending on which cash book i'm looking at. So if each Customer had 3 accounts there could potentially be 900 rows in that particular cash book table.
What i need to do is return 20 'groups of results'.
So if customer one has 6 accounts, it returns 6 rows but only classes that as a single return, meaning that it will return the account details for another 19 customers irrespective of the number of accounts they have.
That's probably not very clear so i apologise, but if anyone can help i would be very grateful. regards, rich

I'm not entirely clear on whether I understand you problem or not, but  assuming there is a customer_id attribute you can partition by that:

select ... from (
    select ..., row_number() over (partition by customer_id) as rn
    from ...
)
where rn = 1

This will give you one row per customer, in an undefined order. If you want a true random account per customer you can add order by some random function after partition by:

    select ..., row_number() over (partition by customer_id
                                   order by ...) as rn


Query for total downtime of site using datestamps and status columns what want to find the total downtime for a site. I'm thinking I can subtract the 'down' DateStamp from the following 'up' DateStamp. then add the totals together for all downtime.

I want to get a year to date for total downtime for any particular site (website). I'm using a sitemonitor to register downtime with a timestamp called datetime. It's the start of the downtime and the following up is when the downtime ended.

There can be multiple down times. The table is an example of what the data looks like.

The downtime may not be in 15 second increments. It could be 10 second to 45 seconds. It would be the difference between the downtime and the following uptime in the table. Is this even possible to be done?

Would this work.  I just had the query give the totals for each year.  You could easily filter by date if you wanted to restrict it for just this year, or a specific year you are looking at.

Also, this assumes the final record in the table is an up time.  If you end with the site being down, then that last downtime isn't included.  You should get your site back up before worrying about calculating downtime :).

WITH TAB
AS
(
    SELECT * ,
           Row_Number() OVER (Partition BY Site ORDER BY DateTime) AS RowNum
    FROM @Table
)
SELECT UT.Site , DATEPART(Year, UT.DateTime) as Year,
       Sum(DateDiff(second, DT.DateTime, UT.DateTime)) as TotalDownTimeSeconds
FROM       Tab AS DT
INNER JOIN Tab AS UT
    ON DT.SiteStatus = 'Down'
    AND UT.RowNum = DT.RowNum + 1
    AND UT.Site = DT.Site
WHERE DT.Site = 'ZooToys'
GROUP BY UT.Site, DATEPART(Year, UT.DateTime);

If you want to get the down time for the past 60 days, add to the where clause:

UT.DateTime > DateAdd(day, -60, getdate())


Multi Subnet Availability Group IP Requirement for Windows Failover Cluster Does the Windows Failover Cluster for a multi-subnet SQL Server Availability Group require a static IP entry for each subnet?

The instructions I am finding for a Failover Cluster Instance (FCI) in a multi-subnet environment require separate IPs but I am not creating any FCIs - only an availability group.

I am running SQL Server 2012 on Windows Server 2012 Hyper V VMs in 2 separate subnets in the same domain. I understand that I will need an IP from each subnet when I create the listener for my AAG. What I am unclear on is the configuration of IPs on the underlying Windows Failover Cluster.

Can I get away with using a single IP in the same subnet as the primary replica or do I need to add an IP from the secondary replica's subnet as well?

Also, if the server hosting the secondary replica does require its own IP, does it also require its own unique cluster name as stated in this blog post (and can you explain why this is necessary)?:

What you need for a Multi Subnet Configuration for AlwaysOn FCI in SQL Server 2012

Does the Windows Failover Cluster for a multi-subnet SQL Server
  Availability Group require a static IP entry for each subnet?

The CNO will require an IP address for every subnet it could reside in.

  I am running SQL Server 2012 on Windows Server 2012 Hyper V VMs in 2
  separate subnets in the same domain. I understand that I will need an
  IP from each subnet when I create the listener for my AAG. What I am
  unclear on is the configuration of IPs on the underlying Windows
  Failover Cluster.

For the underlying WSFC you'll need at a minimum:

Node1 - IP Address for each unique subnet for each network interface

Node2 - IP Address for each unique subnet for each network interface

CNO - IP Address for each unique subnet

EX: 2 nodes, 2 subnets, 1 interface per node, subnets 192.168.1.1/24 and 192.168.2.1/24

Node1: 192.168.1.10

Node2: 192.168.2.10

CNO: 192.168.1.20, 192.168.2.20

  Also, if the server hosting the secondary replica does require its own
  IP, does it also require its own unique cluster name (and can you
  explain why this is necessary)?

I'm not sure I understand this part of the question. All of the resources can only belong to a single cluster - there is no cluster inside of a cluster thing.

Edit - I looked at the link that you posted and I'm not sure why the author stated "•Cluster name for each node". My only guess is they meant each node needs a name and IP (for the node). Otherwise it's not a correct statement, the author should probably be contacted.


SQL Server System Versioned Table Execution Timing I am trying to learn more about system versioned tables in SQL Server so I am running a script to perform simple DML statements on a system versioned table and capturing different times stamps that are used for inspecting the tables between each statement.
Setup
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EXAMPLE_TABLE]') AND type in (N'U'))
ALTER TABLE [dbo].[EXAMPLE_TABLE] SET ( SYSTEM_VERSIONING = OFF  )
GO
DROP TABLE IF EXISTS [dbo].[EXAMPLE_TABLE]
GO
DROP TABLE IF EXISTS [dbo].[EXAMPLE_TABLE_HISTORY]
GO

CREATE TABLE dbo.EXAMPLE_TABLE_HISTORY (
    ID INT NOT NULL,
    -- 'data'
    ColA VARCHAR(255) NULL,
    ColB VARCHAR(255) NULL,
    ColC VARCHAR(255) NULL,
    SysStartTime DATETIME2 NOT NULL,
    SysEndTime DATETIME2 NOT NULL
);
CREATE CLUSTERED INDEX IX_EXAMPLE_TABLE_HISTORY__SysEndTime_SysStartTime
    ON dbo.EXAMPLE_TABLE_HISTORY (SysEndTime ASC, SysStartTime ASC);

CREATE TABLE dbo.EXAMPLE_TABLE (
    ID INT NOT NULL IDENTITY(104729, 1),
    CONSTRAINT PK_EXAMPLE_TABLE
        PRIMARY KEY CLUSTERED (ID),
    -- 'data'
    ColA VARCHAR(255) NULL,
    ColB VARCHAR(255) NULL,
    ColC VARCHAR(255) NULL,
    -- system versioning
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EXAMPLE_TABLE_HISTORY));
GO

Problem
I am running into a strange situation where the results don't appear to be correct unless I add delays into the code. For example, when running the below script I would expect the table to be empty at T0, however it returns data from the first insert.
-- perform simple dml statements
DECLARE @t0 DATETIME2 = SYSUTCDATETIME();
INSERT INTO dbo.EXAMPLE_TABLE (ColA, ColB, ColC) VALUES ('a', NULL, NULL);

DECLARE @t1 DATETIME2 = SYSUTCDATETIME();
UPDATE dbo.EXAMPLE_TABLE SET ColB = 'b' WHERE ColA = 'a';
INSERT INTO dbo.EXAMPLE_TABLE (ColA, ColB, ColC) VALUES ('d', NULL, 'f');

DECLARE @t2 DATETIME2 = SYSUTCDATETIME();
DELETE dbo.EXAMPLE_TABLE WHERE ColA = 'a';

DECLARE @t3 DATETIME2 = SYSUTCDATETIME();

-- inspect tables at specific points in time
SELECT * FROM dbo.EXAMPLE_TABLE FOR SYSTEM_TIME AS OF @t0;
SELECT * FROM dbo.EXAMPLE_TABLE FOR SYSTEM_TIME AS OF @t1;
SELECT * FROM dbo.EXAMPLE_TABLE FOR SYSTEM_TIME AS OF @t2;
SELECT * FROM dbo.EXAMPLE_TABLE FOR SYSTEM_TIME AS OF @t3;
SELECT * FROM dbo.EXAMPLE_TABLE;

'FIX?'
If I add in delays everything seems to work, but I am want to understand if the delays are needed or if they are 'working' accidentaly.
-- perform simple dml statements
DECLARE @t0 DATETIME2 = SYSUTCDATETIME();
WAITFOR DELAY '00:00:00.100';
INSERT INTO dbo.EXAMPLE_TABLE (ColA, ColB, ColC) VALUES ('a', NULL, NULL);

DECLARE @t1 DATETIME2 = SYSUTCDATETIME();
WAITFOR DELAY '00:00:00.100';
UPDATE dbo.EXAMPLE_TABLE SET ColB = 'b' WHERE ColA = 'a';
INSERT INTO dbo.EXAMPLE_TABLE (ColA, ColB, ColC) VALUES ('d', NULL, 'f');

DECLARE @t2 DATETIME2 = SYSUTCDATETIME();
WAITFOR DELAY '00:00:00.100';
DELETE dbo.EXAMPLE_TABLE WHERE ColA = 'a';

DECLARE @t3 DATETIME2 = SYSUTCDATETIME();
WAITFOR DELAY '00:00:00.100';

-- inspect tables at specific points in time
SELECT * FROM dbo.EXAMPLE_TABLE FOR SYSTEM_TIME AS OF @t0;
SELECT * FROM dbo.EXAMPLE_TABLE FOR SYSTEM_TIME AS OF @t1;
SELECT * FROM dbo.EXAMPLE_TABLE FOR SYSTEM_TIME AS OF @t2;
SELECT * FROM dbo.EXAMPLE_TABLE FOR SYSTEM_TIME AS OF @t3;
SELECT * FROM dbo.EXAMPLE_TABLE;

At this point my best guess is SQL Server captures the time at the beginning of the DML statment and there is simply not a long enough delay at the precision of a datetime2 to distinguish between the time captured and the next statement.
Is this assumption correct? Any explanation or documentation would be helpful.
All testing was performed with SQL Server 2017.

You're sort of correct (although not in a good way, as it turns out). From the docs:

  The times recorded in the system datetime2 columns are based on the begin time of the transaction itself. For example, all rows inserted within a single transaction will have the same UTC time recorded in the column corresponding to the start of the SYSTEM_TIME period.

Normally though, unless these were explicit transactions, a DATETIME2 variable should have more than enough precision to split the results, but that's not what we're seeing...

Reproducing

This dbfiddle of 2017 matches OP's situation, but if I run locally on my 2017 instance I do not get results for @t0.

I was unable to reproduce locally on an RTM of 2017 (Express) as well as the latest CU 17. I thus began to assume my laptop is either way too fast, or way too slow, to make this discrepancy obvious.

However, after playing around with multiple fiddles I have found the same code is returning different results, which leads me to believe there is undocumented lag or perhaps a bug in the time stamping feature for temporal tables.

Example

dbfiddle

Conclusion

It took 50+ executions of the same code to trigger all of these differing results (sorry for spamming the site, Jack) but eventually it became clear that the time stamping for temporal tables cannot be trusted on a sufficiently fast enough system. And not only that, but even when the time stamps matched up the results are still not guaranteed to be the same.

I am assuming this is most apparent on faster servers, since my laptop isn't actually all that great, and the STATISTICS TIME on my local tests were consistently 5-6x higher than the ones dbfiddle was returning.

This is probably worth opening a bug with Microsoft about as it seems to be a pretty problematic behavior.


MongoDB replica has no primary I have an existing MongoDB setup (part of a Graylog installation) where I'm attempting to configure a replica set with a primary, secondary and arbiter. I had the primary and arbiter configured successfully but then for some reason my primary has become the secondary and the application is unable to start now and also isn't allowing me to add in any other nodes in the replica set.

my_mongodb_0:SECONDARY> rs.status()
{
    "set" : "my_mongodb_0",
    "date" : ISODate("2019-01-02T19:50:19.291Z"),
    "myState" : 2,
    "term" : NumberLong(12),
    "heartbeatIntervalMillis" : NumberLong(2000),
    "members" : [
        {
            "_id" : 1,
            "name" : "node2:27017",
            "health" : 1,
            "state" : 2,
            "stateStr" : "SECONDARY",
            "uptime" : 9182,
            "optime" : {
                "ts" : Timestamp(1545938968, 1),
                "t" : NumberLong(12)
            },
            "optimeDate" : ISODate("2018-12-27T19:29:28Z"),
            "configVersion" : 166709,
            "self" : true
        },
        {
            "_id" : 2,
            "name" : "arbiter1:27017",
            "health" : 0,
            "state" : 8,
            "stateStr" : "(not reachable/healthy)",
            "uptime" : 0,
            "lastHeartbeat" : ISODate("2019-01-02T19:50:18.868Z"),
            "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
            "pingMs" : NumberLong(0),
            "lastHeartbeatMessage" : "Connection refused",
            "configVersion" : -1
        }
    ],
    "ok" : 1
}

...

my_mongodb_0:SECONDARY> rs.add( { host: "node1:27017", force: true } )
{
    "ok" : 0,
    "errmsg" : "replSetReconfig should only be run on PRIMARY, but my state is SECONDARY; use the \"force\" argument to override",
    "code" : 10107
}

Ideally I'd like to add the other node but with the state that it's in now not sure if I can still do this or if I have to revert it to a stand-alone setup first and configure it again. I know the primary election can't occur now and I haven't found a way to force the primary but just wanted to see if anyone else had any ideas.

I'm guessing now it matters what order you add the DB servers and the arbiter but I haven't been able to find a definitive answer to this...

I had the primary and arbiter configured successfully but then for some reason my primary has become the secondary

The issue is that electing or maintaining a primary requires a strict majority (n/2+1) from your configured voting replica set members. A replica set with only two voting members does not provide any fault tolerance for a primary since votes from both members are required to achieve a strict majority.

Since your arbiter is currently "not reachable/healthy" from node2's perspective, node2 no longer has enough votes to remain primary and will step down to become a secondary.

The best way to resolve this would be to fix the communication issue between node2 and your arbiter before adding a third member.

Forced reconfiguration

  rs.add( { host: "node1:27017", force: true } )

The force option is only available with rs.reconfig(), so would be ignored in this usage example. If you pass a document as the host parameter for rs.add(host), any options (such as priority or tags) only apply to that individual replica set member.

If you can't get your arbiter back online for some reason, you could follow the forced reconfiguration tutorial to remove the arbiter from your replica set config.

As noted in the tutorial, forced reconfiguration is intended to be used as a last resort rather than routine admin:

The `force` option forces a new configuration onto the member. Use this procedure only
to recover from catastrophic interruptions. Do not use `force` every time you reconfigure.
Also, do not use the `force` option in any automatic scripts and do not use `force`
when there is still a primary.

  I'm guessing now it matters what order you add the DB servers and the arbiter

The order isn't significant aside from adding an initial data-bearing node so the replica set has a primary. However, you do need to ensure you have a majority of voting members online and able to communicate with each other.


Replay trace file and filter on error>0? I have captured a trace file using the replay template. It includes all columns available, of course including ERROR. When I replay this trace file, the "trace configuration" window pops up, I declare the target server, save the result to a new trace file and run the trace.
When I then load the new trace file, most of the columns are gone, including the error column. I was hoping to filter on ERROR > 0 in the resulted trace file of the replay action to find queries having problems on the replayed-against server.
Why are the columns not included anymore - and why does the new trace file contain not all events anymore but instead includes these "Replay Result Row Events" (e.g. Name = blabla...) ?

Edit:
Meanwhile I followed a suggestion to look at the trace file using a sql query:

SELECT * INTO mytracetable FROM ::fn_trace_gettable('c:\my_trace.trc', default)

to see ALL columns. Unfortunately here the columns are displayed but contain all a NULL value where I had expected to see values e.g. for ERROR.

"I have captured a trace file using the replay template"

I believe this is your problem. Create a server-side trace using the stored procs rather than the replay template. Then execute:

SELECT * INTO mytracetable FROM ::fn_trace_gettable('c:\my_trace.trc', default)

To see your columns. I used the same method as you and had exactly the same issue. There are other tools available to replay your workload outside of profiler, and I would recommend you look into SQLNexus or Distributed Replay.


pgAdmin Query Tool throws error when I try to run multiple statements I'm very new to PostgreSql and still learning the ropes.

I have a really long script for setting up a new copy of my database. It creates functions, runs them, does some inserts and a bunch of other stuff. When I try to run this entire script in the pgAdminIII Query Tool, it throws an error that doesn't seem to make sense.

ERROR:  syntax error at or near ""
LINE 18: /*
         ^

This error gets thrown right after the first alter function owner statement which is after the first create function statement.

If I take each statement out and run them individually then everything runs OK. What gives?

Lines before error:

ALTER FUNCTION table_exists(text) OWNER TO postgres;

Lines at error:

/*
-- Function     : column_exists(text, text)
-- Function output  : true / false
*/

Lines after error:

CREATE FUNCTION column_exists(tablename text, columnname text)
RETURNS boolean AS $$

BEGIN
    RETURN EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = $1 AND column_name = $2);
END;

$$LANGUAGE plpgsql;

Per the comment by Fung:

  ...it was actually some non-visible character at the beginning of each
  comment. And the reason the statements still worked when run
  individually was because I was not running them with the comments


Developer Edition vs Standard Edition Is it dangerous to have dev environment with a Developer Edition and Live with Standard Edition, are the binaries different ?

Mostly no, it isn't dangerous, but it depends on which version of SQL Server you are talking about, what parts you are using (just the DB engine?, SSIS?, SSRS?, ...) and your level of aversion to the small risk.

Developer edition is essentially Enterprise edition under a different licence. The problems you could hit are that some features might not be enabled in your production, standard edition, installation that are present in your development environment.

If you are using SQL Server 2016sp1 or above then this is unlikely to be a problem at all: almost everything is available to all editions. If you are using an older version then there might be differences to watch out for. For instance table level data compression was not supported outside enterprise (and developer) editions before 2016sp1 (including the original release of SQL Server 2016). The risk here being that one of your devs may make use of a feature that is not enabled in production. To see which features this affects, check MS's editions documentation: 2012, 2014, 2016, 2017. The risk here is small, and can be mitigated with care.

You could also go the other way: if you don't specifically need greater feature set and your dev environments don't have the data size to need more RAM than it will make use of, make your devs use Express instances then you know they won't use anything that won't be present when you deploy to Standard!

The differences beyond features between the editions and licensing options are how much RAM and how many CPUs/cores are supported. Unlike feature differences this will not cause your code to fall over because things are missing in production that are present in development, but it might cause performance differences. So again, the risk here is small. The same editions comparison pages listed above include this information also.

In any case, even if you use Developer edition for general dev, I would recommend using Standard edition in your Test environment because you want Test to be as close to Production as possible. Furthermore, matching versions and service pack levels, even if you don't match editions, is strongly recommended where possible.


Interpreting Decision Time (DT) in temporal databases Could anyone give me an insight for the need to model decision time in temporal databases and specifically how and why decision time differs from valid time and transaction time? What would be the semantics of decision time?

For instance, what information would/could the dt_period convey in the  following example which illustrates an employee salary data where range types (PostgreSQL) are used, eg. vt_period instead of two distinct columns such as valid_from and valid_to:

  name   | salary |        vt_period        |       tt_period       | dt_period
---------+--------+-------------------------+-----------------------+-----------
 Okihara |   1000 | [2014-01-01,2014-12-31) | [2014-03-15,infinity) | (!?!??)
 Okihara |   1500 | [2014-12-31,infinity)   | [2014-11-05,infinity) | (what should this column mean?)

where

  vt_period = Valid time period
  tt_period = Transaction time period
  dt_period = Decision time period

from which it may be inferred that Okihara was hired for a salary of $1,000 over the period [2014-01-01, 2014-12-31) as last updated on 2014-03-15 and still current. On 2014-11-05 a new entry with a raise to $1,500 was made for the subsequent validity period [2014-12-31,infinity) (as stated by thevt_period column).

What range values could dt_period hold and what would their semantics be?

I don't know if there is necessarily a decision period, but there might be a decision date, separate from the valid period start date and the transaction date, where it makes sense to indicate when exactly a decision was made to make a new entry valid.

In your example the decision to hire Okihara probably had been made at some time prior to '2014-01-01', in which case it might have put certain obligations on the employer even before Okihara's first day on the job, so it made sense to record it separately.

One might presume that the decision date will never be after the transaction date, while the valid date might still be arbitrary.


SQL Server - Sudden log autogrowth after database backup I have to manage a SQL Server 2008 Database, using SQL Server Management Studio 2012. The database file is about 290Gb. The problem we have is that the log file used to grow a lot.

Doing some internet research (I'm totally new to DB management), I came to the following conclusions, and applied all of them:

Set (what I thought to be) a realistic initial log size; 220 Mb.
Set autogrowth feature to increase in MB (instead of %).
Schedule regular log backups (currently once every 15 minutes).
Schedule a full database backup once a day.
I've scheduled 4 UPDATE tasks every 10 seconds to keep the log growing. These tasks are performed on 21000 records.

This configuration seemed to worked ok. I've been monitoring the behaviour of the log size, and it never needed to autogrow; when the reserved space for the log file was about to get full, the scheduled backup would free it again. After 11 hours, the log size had only increased up to 260MB, which I considered to be Ok (4x10Mb autogrowths in 11 hours).

So I went home, trusting that this behaviour would persist at night. But the following day, I saw that several autogrowths had been performed. The log size increased from 220Mb to 2350 Mb (more than 200 autogrowths!).

I decided not to change anything, and keep monitoring it; the used space for the log file never grew more than the 18%. I increased the autogrowth size from 10 Mb to 50 Mb.

Again, I was pretty confident that this configuration will prevent autogrowths from happening. And again, I was wrong. This morning I've found that a new autogrowth process has been performed. Only once, but it still makes me feel confused.

I've noticed that this last autogrowth operation took place after the full database backup was performed.

Can please anyone explain me if this behaviour is normal? Why the log size keeps stable during all day, and after the database backup is performed, it suddenly grows? And why does it grow, when barely 20% of the reserved space for the log file was being used at peak times, right before the log backup? Any recommendation?

I wanted to clear this in a comment but there is no room to do this, so I post it as the answer.

While the the key point of what happens is right, i.e. the  full backup cause the log to grow, this explanation below given by Ste Bov is completely wrong:

  When you start a full backup that is the new point in time that the
  tLog backups will work from, but they cant work until that backup is
  complete, so during this time the transaction log grows, once the full
  backup is complete you now have a new point in time and the tLog
  backups will now be able to actually release the data from the
  transaction log and it will return to its normal usage levels

Fisrt of all full backup will NOT make "the new point in time that the
 tLog backups will work from", yes it creates a new recovery point, but the next log backup will start exaclty from the point where the previous log backup was stopped (this point in the log file, last_LSN, is written to the backup and can be seen using RESTORE HEADERONLY, or by querying msdb..backupset)

Second, starting with SQL Server 2005, concurrent log and full backups are possible, you can prove it yourself. What happens instead is that the log backup cannot clear the log when it finishes if there is a full/differential backup running concurrently. That is because diff/full backup need to write out the "active portion"* of the log in order to get a database in consistent stay when restored.

*to be precise, it's not an "active portion", but all the log records from
MIN(LSN of last checkpoint, LSN of oldest active transaction) to the LSN at which the data read portion of a backup ends

So the log should be large enough to be able to accomodate all the activity that is logged for the whole duration of the full backup.

More on this here:

Concurrent log and full backups by Randal

More on how much transaction log a full backup includes by Randal


How to perform a multiplication by a different select query Hope my question is accepted here and I haven't made a mess out of renaming the tables and columns so you can understand what I need! :)

Query A:

DECLARE @var VARCHAR(7)
SET @var = 'xtpo'
SELECT
    row_number() over(order by dbo.tbl_A.field_1) AS '1st column',
    dbo.tbl_A.field_2 AS '2nd column',
    dbo.tbl_B.field_1 AS '3rd column',
    dbo.tbl_A.field_3 AS '4th column',
    COUNT(temp0.reference) AS 'Qty'
FROM dbo.tbl_C tbl_c
JOIN dbo.tbl_D tbl_d ON tbl_d.field_4 = tbc_c.field_4
JOIN dbo.tbl_A tbl_a ON tbl_a.field_3 = tbl_d.field_3
JOIN dbo.tbl_B tbl_b ON tbl_a.field_4 = tbl_b.field_4
JOIN (SELECT
         tbl_e.field_1 f1,
         tbl_e.field_2,
         tbl_e.field_3 AS f3
      FROM dbo.tbl_E tbl_e
      GROUP BY
         tbl_e.field_1,
         tbl_e.field_2,
         tbl_e.field_3,
         tbl_e.field_1) temp0 ON dbo.tbl_A.field_2 = temp0.f3 AND temp0.f1 = tbl_c.field_2
WHERE tbl_c.field_1 LIKE @var
GROUP BY dbo.tbl_A.field_a, dbo.tbl_A.field_2. dbo.tbl_B.field_2

Query A is a listing containing 5 columns.

Query B:

DECLARE @var VARCHAR(7)
SET @var = 'xtpo'
SELECT COUNT(DISTINCT PcbID) FROM dbo.tbl_C WHERE dbo.tbl_C.field_5 LIKE @var

How can I integrate Query B on Query A so the 6th column of the listing is a multiplication of Query A's 5th column and the single result of Query B?

Thanks in advance,

gtludwig

Probably simplest would be:

DECLARE @var VARCHAR(7), @c INT;

SET @var = 'xtpo';

SELECT @c = COUNT(DISTINCT PcbID) FROM dbo.tbl_C WHERE dbo.tbl_C.field_5 LIKE @var;

SELECT
    row_number() over(order by dbo.tbl_A.field_1) AS [1st column],
    dbo.tbl_A.field_2 AS [2nd column],
    dbo.tbl_B.field_1 AS [3rd column],
    dbo.tbl_A.field_3 AS [4th column],
    COUNT(temp0.reference) AS Qty,
    COUNT(temp0.reference) * @c AS [6th column]
FROM dbo.tbl_C tbl_c
...

Note that I changed your 'single quote' aliases to use [square brackets] instead. Single quote aliases are deprecated in some forms, and they are not well-received because they make your aliases look like string literals. In most cases you are better off not using delimiters at all if you don't need them (which means not using column names that require delimiters in the first place).


SQL Server effectiveness of mirroring partial database Our database stores 200 TB of data. We've setup clustering options of two VM nodes with one storage but due to limited resources, we cannot replicate the entire database for disaster recovery (only about 100 GB can be spared at most).

So, I was wondering if it's possible to mirror partial portion of the database.

Distinguish active data, pull them into separate database (maybe at most 20 GB worth of active data)
Setup database mirroring for new database

Questions:

If partial database is mirrored, should I be worried about performance issue?
Are there other known solutions to prepare for DR situations?

It may be hard to provide best answer without knowing the detailed specs of servers, and configurations but I'd like to know how other servers are handled with limited resources.

Thank you!

I think you should consider transactional replication instead of pulling data into a separate database and then mirror that database.

Replication in my opinion is not a high availability solution because for example it does not provide automatic failover, but I think it fits to your needs because you just want to replicate some tables and you can also apply filters to the published tables so you can replicate just part of the data and not the entire table (in case you want to do something like that). If you want to redirect applications to your replica, you could create a DNS entry pointing to the primary sql instance and set the connection string to use that DNS instead the servername or the ip and you just change the DNS to make it point to the principal instance or the replica so you don't have to touch your applications to failover.

Of course replication look at the limitations of replication and evaluate if this fits your needs.

As you mentioned, it is hard to provide a recommendation without knowing all the specs of your scenario, but I hope this can be an alternative to you situation.


SQL Server - Windows Group Authentication I am trying to understand how to create Windows Group based access to a SQL Server (2012) database the right way. I was able to successfully add a Windows group to the Server Login and I have a defined Role to which I would like to have the group mapped. My question is, does SQL Server need a User created and mapped to the Windows Group to allow users with Windows Authentication access the database? If so should it be per user or can I just have a single User Name for the Windows Group Login.

Just to demonstrate how I have the current set up:

Windows Group Name  -> domain\AdminGroup
Database Name       -> XYZ
DB Login Name       -> domain\AdminGroup
DB Role             -> xyzAdmin
User Name           -> xyzAdminUser (mapped to Role: xyzAdmin and Login: domain\AdminGroup)

Currently I have created a generic User (not individual ones for each user in the group) and mapped it to the Windows Group Login. But I am not sure why that step is required since it is Windows Authentication. Is that something more of a requirement to have explicit mapping from Login to User Name which SQL Serve requires? Not sure if SQL Server could handle this implicitly behind the scenes if there is any option to map a Windows Group Login to a Database Role. The reason I am asking is, if a User name needs to be created explicitly for either Windows Group as a whole or for individual users, I am not really sure if I as a User would ever need to use it since it is Windows based authentication unlike other login where I use my User Name and password to authenticate.

You were correct in creating the Windows domain security group.  Add Windows domain accounts to that security group as required.  You were correct in creating a SQL login for the Windows domain group.  SQL will allow authentication by any member of that Windows domain group.  Nothing further is required as far as SQL login creation.  Set permissions as needed within SQL Server.

Have you tested your configuration?  What problems are you encountering?

  By using Windows Authentication, Windows groups can be created at the domain level, and a login can be created on SQL Server for the entire group. Managing access from at the domain level can simplify account administration.

Books Online


Default values in function, arguments dependency, PostgreSQL I would like to write a function with two arguments, which would have dependency. Something like this:

    CREATE OR REPLACE FUNCTION years_compare(
        IN year1 integer DEFAULT date_part('year'::text, ('now'::text)::date),
        IN year2 integer DEFAULT year1 - 1)

...

When I call a function years_compare(2019), the second argument would have a value 2018. But how can I write a default value for the second argument?

Maybe using Postgres' support of polymorphism might be of use here.

First create the base function with both parameters.

CREATE FUNCTION years_compare(IN year1 integer,
                              IN year2 integer)
                RETURNS integer
AS
$$
BEGIN
  RETURN 1; --<replace by some calculated value>
END;
$$
LANGUAGE PLpgSQL;

Then create one function with only one parameter calling the base function with the first parameter and the calculated second parameter.

CREATE FUNCTION years_compare(IN year1 integer)
                RETURNS integer
AS
$$
BEGIN
  RETURN years_compare(year1, year1 - 1);
END;
$$
LANGUAGE PLpgSQL;

Finally create a parameterless version of the function calling the second one with a calculated parameter.

CREATE FUNCTION years_compare()
                RETURNS integer
AS
$$
BEGIN
  RETURN years_compare(date_part('year'::text, ('now'::text)::date)::integer);
END;
$$
LANGUAGE PLpgSQL;

Then you have three versions of the function with zero to two parameters.


MS SQL 2008 R2 - How to use a production database through dev/test environments or how to copy a SQL schema and all its data to another We currently have 3 Windows Server 2008 R2 VM identically build for Sage x3 v6.5, one for production/formation/budget database, a second one for QA (qualification) and a third one for dev & tests. This makes a total of 6 databases, Microsoft SQL Server 2008 R2 with standard licences. We would like to improve our process in order to always have fresh production data in dev, test and qualification (and potentially review, at the same time, the budget and training databases locations, which are also copies of the production scheme).

As I'm not a database specialist (currently an infrastructure engineer apprentice), let alone Microsoft SQL Server... I come to ask you for advice and best practices. Our infrastructure being rather modern, robust and spacious, we don't have any storage capacity or performance problems but, in the interest of optimization, I would like to avoid, as much as possible, having to duplicate 6 times the production VM (about 800 Go) or the production database as it is more or less the case actually...

So is there a way to use the production database from another environment but to redirect the writings of those environments to another location, specific to each environment (another shema or somewhere else...) to keep production database clean as it should be, exactly as a VM snapshot does physically (redirect on write) ? If not, I think I will have to copy the database 6 times (don't hesitate to tell me if I'm wrong)... How could I proceed in SQL 2008 R2 because it's hard to find how to copy tables (and their data) from a specific schema to another database schema on another server and to automate it. And, afterwards, is it possible to do differential copy (each night for example) to reduce the process duration.

Thanks a lot for all your answers :)

Sincerely yours,
Tigerblue77

Consider also replication. You can replicate only your tables so that you don't override any stored procedures or functions that the developers are working on. You can set up a publisher on production and 2 subscribers: one on TST and one on DEV.

It's almost real time and very suitable for scenarios like yours. I always try to go for replication instead of anything else because in a stable environment you set it up and forget it's there while saving network bandwidth and unnecessary data copy. (with backup and restore you basically [copy prod], [delete tst/dev], [paste prod in dev/tst] - 90% the same data everyday)

Downsides:

it's a bit of trouble until you learn to work with it
it will take some network bandwidth during the day to send your
transactions from the publisher (prod in your case) to the subscriber
(or subscribers in your case: dev and tst).
it will eat up a lot of network bandwidth, SQL Server memory and disk I/O when you initially set it up and this will repeat if and every time you need to reinitialize the replication. You can also set up replication from backup and that goes faster when you initially set it up and if you ever reinitialize.

More documentation about replication here.


Improving the performance of procedures showing warnings for implicit conversions We have recently migrated from SQL Server 2008 R2 to SQL Server 2012 Standard Edition. We and witnessing poor performance related to quite a number of stored procedures running against a database that is not that big in size (8 GB).

While analyzing the plans we have seen lot of warnings pop ups for eg:

  CONVERT_IMPLICIT(int,[DBNAME].[dbo].[tblname].[col1] as
  [s].[col1],0) CONVERT_IMPLICIT(nvarchar(100),[DBANME].[dbo].[tblname2].[col1]
  as [s].col1],0)

I have a few questions related to above.

How can I find the mismatch for those datatypes in the warnings shown in the plan? Is there any script or do I have to manually check the data types for each column?
When we use index hints, the query performs better. Does that mean that when the above warnings are received, the optimizer won't use available nonclustered indexes? If yes, why so? Also, will using index hints be a benefit in the future or harm?

We have already conveyed these to the developers to change or look at data types for any mismatch. Is there be any better approach to solve this problem?

The server and database collation are same: SQL_Latin1_General_CP1_CI_AS
Statistics were updated using Ola's script a couple of hours back
The actual plan can be found here

sp_BlitzCache will break down implicit conversion information for you, for seek affecting converts.

For example, with this query (for brevity):

DECLARE @butthead SQL_VARIANT = N'your mom'
SELECT COUNT(*) FROM dbo.Comments AS c WHERE c.Text = @butthead

And this plan, it'll return results like this:

If you click on the implicit conversion info column, it'll give you a break down of what happened where:

So while it doesn't search your plan cache specifically for that issue, it will give you your most resource intensive queries and warn you when implicit conversion is a problem, and then break down the details.

Hope this helps!


get rank in marks database in mysql I have a Student database having one table EXAM.

id    1  2  3  4  5
marks 7 10  7 10  9

Question is how to give rank like

+------+
| rank |
+------+
|    3 |
|    1 |
|    3 |
|    1 |
|    2 |
+------+

I have used a Query:-

SELECT id, marks, FIND_IN_SET( marks, (
SELECT GROUP_CONCAT( marks
ORDER BY marks DESC )
FROM exam )
) AS rank
FROM exam;

but it gives rank

+------+
| rank |
+------+
|    4 |
|    1 |
|    4 |
|    1 |
|    3 |
+------+

What should I do??

Just add the word DISTINCT in front of the first marks inside the GROUP_CONCAT function

SELECT id, marks, FIND_IN_SET( marks, (
SELECT GROUP_CONCAT( DISTINCT marks
ORDER BY marks DESC )
FROM exam )
) AS rank
FROM exam;

First here is the sample data

use test
DROP TABLE IF EXISTS exam;
CREATE TABLE exam
(
    id int not null auto_increment,
    marks int not null,
    primary key (id)
);
INSERT INTO exam (marks)
VALUES (7),(10),(7),(10),(9);
SELECT * FROM exam;

Let's load the sample data

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS exam;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> CREATE TABLE exam
    -> (
    ->     id int not null auto_increment,
    ->     marks int not null,
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO exam (marks)
    -> VALUES (7),(10),(7),(10),(9);
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM exam;
+----+-------+
| id | marks |
+----+-------+
|  1 |     7 |
|  2 |    10 |
|  3 |     7 |
|  4 |    10 |
|  5 |     9 |
+----+-------+
5 rows in set (0.02 sec)

Now, watch the output of the proposed query:

mysql> SELECT id, marks, FIND_IN_SET( marks, (
    -> SELECT GROUP_CONCAT( DISTINCT marks
    -> ORDER BY marks DESC )
    -> FROM exam )
    -> ) AS rank
    -> FROM exam;
+----+-------+------+
| id | marks | rank |
+----+-------+------+
|  1 |     7 |    3 |
|  2 |    10 |    1 |
|  3 |     7 |    3 |
|  4 |    10 |    1 |
|  5 |     9 |    2 |
+----+-------+------+
5 rows in set (0.00 sec)

mysql>

Give it a Try !!!


Copy Specific Columns from DB 1 To DB 2 on MongoDB I have Two Instance of Node JS server which is handled By NGINX. First server is connect with MongoDB First Server i.e

  mongoose.connect('mongodb://127.0.0.1/db_primary', { useMongoClient: true });

While Second Node Js Server is Connect with MongoDB Second Serer i.e

  mongoose.connect('mongodb://127.0.0.1/db_secondary', { useMongoClient: true });

Actually, I have to Do some Heavy Calculations on DB and that the main reason I will divide my DB into Two Pieces.

Initially When I have One Server then if the backend is busy with calculations (Millions of Entries) at that time Server is completely freeze. So by the Separation There is now no freezing point of server.

My Question is How I can Copy the selected Columns on The entire Table form DB 1 And paste it to DB 2 with only Selected Columns.

Example:Database 1

{
    _id: ObjectID(54815dfsfsd1f564168ad51a5s),
    a: 123,
    b: 768,
    c: 89.67,
    d: 8976,
    e: 45
  }..........upto 10 million entries

Now I want to Copy Only Column c & e from Database 1 to Database 2.

Expected Result:
{
        _id: ObjectID(54815dfsfsd1f564168ad51a5s),
        c: 89.67,
        e: 45
}..........upto 10 million entries

And all these things are done by CronJob of Nodejs. I mean Not a manual task.

I am Newbie on MongoDB. Any help is appreciated.

You can do projection with query. So, db.collection.find({},{c:1,e:1}) and then insert that to second DB.

Inside the one DB you can do it with use of aggregation with project and out stages.

Have you consider to use replica set and secondary read?


sp_whoisactive @get_locks parameter xml error I often receive this error when running the following command

sp_whoisactive @get_locks = 1

  Msg 6841, Level 16, State 1, Procedure sp_WhoIsActive, Line 4287 FOR XML could not serialize the data for node 'Lock/@resource_description' because it contains a character (0x0001) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive. Warning: Null value is eliminated by an aggregate or other SET operation.'

I believe it is related to the grabbing the xml of the locks being held, however I notice it happens frequently when there is blocking occurring. Is there any way around this or is it a known issue? Running SQL Server 2012, and latest version v11.32 of sp_whoisactive.

Edit

Following the suggested option, I’m having trouble locating this data. I mostly encounter the message when there is blocking on the server, once I encounter the message I take the parameter off and try to see which object it may be coming from, but it’s difficult because there are many threads running. Is there any easier way of going about it? I initially didn’t think it was data but an object name, because when I see the locks xml it normally only contains object names, I didn’t realize actual data is being put in there that may be resulting in this message?

Edit 2

Following the advice from Erik, I was able to track down the object with the SOH character outputting the results of the #locks table. However ultimately what I found most useful is using the work-around of get_task_info = 2 and get_additional_info = 1, when running these parameters I do not receive the error message and still receive the information I need. I am still going to explore trying to add that character into the replace statements in sp_whoisactive so I can still use the get_locks parameter in the case I need to see the full XML.

I can't fully answer your question as to which object is causing the issue, but I can offer you some troubleshooting, and possible workarounds.

Note that I can't distribute a "fixed" script to you because of its licensing, but you're free to try the edits locally.

Troubleshooting

If you ctrl+f through sp_WhoIsActive for @get_locks = 1, you'll hit this code section:

A bit further down here, you'll see that a rather complicated select inserts into a table called #locks:

If you scroll down to line 1670 or so (assuming you have line numbers enabled in SSMS), you'll reach the end of the SELECT INTO that populates it. You could try adding a simple SELECT * FROM #locks; here to see what ends up in the table. This could at least potentially point you to the correct object.

Workarounds

One workaround, without changing any code might be to run the procedure like this:

EXEC sp_WhoIsActive
    @get_task_info = 2,
    @get_additional_info = 1;

The intent of these parameters is documented here.

If that doesn't run without error, you're stuck doing a ctrl+f for FETCH NEXT FROM locks_cursor. This will bring you to the section of code where your error pops up.

Within this block of cursor code, you'll find a dynamic query, a catch block, and an update with nested REPLACE blocks to clean out bad characters (except yours, it appears). You'd have to add an additional nested REPLACE across all of them with NCHAR(0x0001) here I believe, in order to fix it. I haven't tested this, so YMMV.

Hope this helps!


SQL Server 2014 DELETE with OUTPUT Why would I get an 'incorrect syntax' error with this (on the OUTPUT keyword):

DELETE tb
    FROM dbo.TransactionBatch tb
    OUTPUT Deleted.TransactionID INTO #TransactionIDs
    INNER JOIN @BatchTransactionIDs tid
        ON tb.TransactionID = tid.TransactionID

@BatchTransactionIDs is a User-Defined Table type

The OUTPUT has to go before the FROM.

DELETE tb
    OUTPUT Deleted.TransactionID INTO #TransactionIDs
    FROM dbo.TransactionBatch tb
    INNER JOIN @BatchTransactionIDs tid
        ON tb.TransactionID = tid.TransactionID


You can use the SUBSTRING_INDEX() function to get the part before the first dot and then group:

select SUBSTRING_INDEX(programVersion, '.', 1) AS programMajorVersion,
       count(programVersion) AS totalIncrements
     from records R
     inner join Programs P
         on P.programID=R.usageProgramID
     where programName="word"
     group by SUBSTRING_INDEX(programVersion, '.', 1)
     order by CAST(programMajorVersion AS unsigned) ;

The above may not be the most efficient on a large table. If you have indexes that your version uses and is efficient, you may use that as a derived table and then aggregate with the substring function:

select SUBSTRING_INDEX(programVersion, '.', 1) AS programMajorVersion,
       SUM(cnt) AS totalIncrements
     from
     ( select programVersion, count(programVersion) AS cnt
         from records R
         inner join Programs P
             on P.programID=R.usageProgramID
         where programName="word"
         group by programVersion
     ) AS t
     group by SUBSTRING_INDEX(programVersion, '.', 1)
     order by CAST(programMajorVersion AS unsigned) ;


Get count of near points based on 2 unrelated tables I have 2 tables, cities and listings and there is not FK relationship or any other field that I can create a relationship based on.
They both have a geography field.
I would like to get list of all cities that have at least 1 listing (using Location property of both tables)
When I run the following query, I get cities with count of 1, multiple times. I simply want to get cities with number of listings near it as the count.
DECLARE @distance FLOAT = 100000;
SELECT c.name, count(l.ListingID) FROM listings l, Cities c
WHERE l.Location.STDistance(c.Location) <= @distance
GROUP BY c.name, l.listingid

I also have a 16x16x16x16 and 256x256x256x256 indexes on both tables for Location field if that matters...
I am using SQL Server 2019
EDIT: indexes on both tables
CREATE SPATIAL INDEX [256_HHHH] ON [dbo].[Listings]
(
    [Location]
)USING  GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),
CELLS_PER_OBJECT = 256, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE SPATIAL INDEX [16_HHHH] ON [dbo].[Listings]
(
    [Location]
)USING  GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Actual Execution Plan
https://www.brentozar.com/pastetheplan/?id=rJ2HftM4v

simply want to get cities with number of listings near it as the count.

Then don't GROUP BY listingid eg
DECLARE @distance FLOAT = 100000;
SELECT c.name CityName, count(l.ListingID) Listings
FROM listings l, Cities c
WHERE l.Location.STDistance(c.Location) <= @distance
GROUP BY c.name

Or you might try something like:
DECLARE @distance FLOAT = 100000;
SELECT c.name CityName,
(
  select count(*)
  FROM listings l
  WHERE c.Location.STDistance(l.Location) <= @distance
) Listings
FROM Cities c


Query causing Database lock problem (php/Mysql) What is the issue with my query? When I execute, it lock the database for 5 to 10 minutes. Please help me to optimize this query.

SELECT
    u.username,
    u.email,
    u.created_at,
    p.firstname,
    p.lastname,
    p.address,
    p.address_2,
    p.city,
    p.country,
    p.state,
    p.zip,
    p.phone,
    p.phone_2,
    u.last_ip,
    u.last_login_at,
    u.auto_login,
    u.registration_page,
    s.product_name
    FROM
    users AS u
        Left Join subscriptions AS s ON u.id = s.user_id
        Left Join profiles AS p ON u.id = p.user_id
    where u.registration_page='Chris_Cleanse' and
        u.id not in (select user_id from goal_sheets) and
        u.id not in(select user_id from sheet_user_popup_not_adam) and
        s.expired=TRUE ORDER BY u.id DESC;

SELECT
    u.username,
    u.email,
    u.created_at,
    p.firstname,
    p.lastname,
    p.address,
    p.address_2,
    p.city,
    p.country,
    p.state,
    p.zip,
    p.phone,
    p.phone_2,
    u.last_ip,
    u.last_login_at,
    u.auto_login,
    u.registration_page,
    s.product_name
    FROM
    users AS u
        Left Join subscriptions AS s ON u.id = s.user_id
        Left Join profiles AS p ON u.id = p.user_id
        LEFT JOIN goal_sheets AS gs ON u.id = gs.user_id
        LEFT JOIN sheet_user_popup_not_adam AS supna ON u.id = supna.user_id
    where u.registration_page='Chris_Cleanse' and
        s.expired=TRUE and
        gs.user_id IS NULL and
        supna.user_id IS NULL
    ORDER BY u.id DESC

You can transform the subqueries into left joins and check for IS NULL in where conditions. MySQL performs better on joins than on subqueries.

Have you set appropriate indexes on your tables? Otherwise this should be done too. (Indexes on subscriptions.user_id, profiles.user_id, goal_sheets.user_id and sheet_user_popup_not_adam.user_id should exist as minimum.)


Mixing 2 and 3 node AG on a single WSFC? I have a 3 node availability group (Enterprise 2014), it is multi subnet, with two data centers and file share witness in a third data center. There are about 20 "production" database that exist on the Primary, Reporting and DR instances, via an AG with a listener. Currently configured, async with manual failover.  Production is about 1TB, active OLTP.

The application points to the listener, reporting points to Report Server.

We get HA by temporarily changing to sync-commit and failing over.  Reporting is fine with async as is DR (risks understood)

There is a "Reportsdb" that only needs to exist on the reporting database. I have used a 2 node AG with async to have it available on DR. Reportsdb is about 50GB, with a rebuild from scratch time of a few hours.

The DR goal is have reporting on a seperate server as long as any two are up.  With two down, there is no reporting.

Question: If we change to sync and automatic failover; what if any issues will having mixed 2 and 3 node AGs on one WSFC have?

None.  The settings and behavior of each availability group (AG) are completely independent of the other AGs.  When an AG is created, a separate role is created in the cluster to contain that AG's objects.  Each role acts independently and doesn't share, care about, or even know about the settings of other AGs that are on the cluster.


ORA-32000: write to SPFILE requested but SPFILE is not modifiable I'm not able to alter the SORT_AREA_SIZE in my database.

Use the command:

create pfile ='loc' from spfile;

To create a pfile named loc. The edit this file and change the parameter in this pfile. Now create a new spfile from it:

create spfile from pfile ='loc';


pgAdmin 4 including  in all copied strings I'm having trouble using copy/paste from pgAdmin 4. The symptom is that I get the character <U+FEFF> aka Zero Width No-break Space aka Byte Order Mark (BOM) prepended to any string I copy.

The workaround is to paste and then delete that first character. But in practice this is cumbersome. In some contexts the character does not get pasted (in vim on my machine). In some contexts it is pasted, but it's not visible. In other cases it is "visible" as a space (in TextWrangler on my machine).

Here's a bit more detail. I successfully type and execute a query:

select 'this is constant text value in my query'

I select and copy these 4 characters: text

I think my clipboard should contain just those 4 characters. But it doesn't. Instead, it contains this: <U+FEFF>text

This is perhaps related to this old thread between Josh Berkus and Dave Page. But that discussion focuses on the fact that pgAdmin puts the character at the beginning of any file. In my case it's really not the file that is the issue. It's the clipboard.

More practically, the solution in that case was to go to File -> Options and turn off Unicode file writing. In pgAdmin 4 (v2.1 on MacOS), that menu doesn't exist. The menu File -> Preference exists, but I can find no corresponding setting in the Preferences.

Question: can anyone help me to prevent the FEFF character from sneaking into the clipboard whenever I copy anything in pgAdmin?

It turns out this is a known bug:

https://redmine.postgresql.org/issues/2980

It has been fixed in source already. Expect the fix in pgAdmin 4 v3. Khushboo said in the pgadmin-support list that it will be out soon.


update/delete records concurrently without impact to other process for handling huge data? I tried to googling first, but i didn't get it . . .

So, What are best practices for update/delete records concurrently without impact to other process handle to huge data(>5 millions records)?

Please point to me.

Thanks you.

If you can apply partitioning to the table(s) in question you can swap out whole partitions then drop the swapped partitions.  This is extremely fast and resource-light.

It requires minimum versions and editions, however, and your question's tags don't specify this.  It also imposes some prerequesites on your tables' keys.


Postgresql 9.2.6 ignoring archive_command I'm attempting to set up a 3-server chain by leveraging WAL-E.  I've done this in the past with replication/wal segments and I'm attempting the exact same method, just with using WAL-E in between.

My replication from a -> b is working perfectly through WAL-E.  I specify the S3 bucket and access information and a uploads to S3, and b pulls down segments it may need if streaming falls behind.

I'm having an issue trying to get b to upload segments to S3 for use by server c.  I've updated my postgresql.conf on b to be:

wal_level = hot_standby
checkpoint_segments = 10
archive_mode = on
archive_command = '/etc/postgresql/9.2/main/local_backup_script.sh "%p" "%f"'
archive_timeout = 3600
max_wal_senders = 5
wal_keep_segments = 4096
hot_standby = on

My local_backup_script.sh is as follows:

#!/bin/bash

echoerr() { echo "$@" 1>&2; }

FAIL=0

`envdir /etc/wal-e.d/env-rep-a /usr/local/bin/wal-e wal-push $1` & pid_1=$!

echoerr "Spawned replication process $pid_1"

wait $pid_1 || let "FAIL+=1"

if [ "$FAIL" == "0" ];
then
echoerr "Replication success $1 $2"
else
echoerr "Replication failed $1 $2"
fi

exit "$FAIL"

I am using a different S3 bucket from b -> c so they don't step on each other.  I've also verified that wal-e upload works to this bucket by manually calling the script from the pg_xlog directory.

But postgresql NEVER executes archive_command, and I'm not at a loss as to why.  I even just tried to print something to STDERR (so it goes to the log), nothing.

Few other points:
1) /var/lib/postgresql/9.2/archive directory is empty (on b)
2) the script permissions have been set properly (owned by postgres and chmod +x)

Anyone have any suggestions on what may be wrong?

Streaming replicas don't run archive_command because they do not generate WAL. They just replay the master server's WAL.

You can cascade streaming replicas, where one replica acts as a master for the next. But for fallback WAL archiving they both use the master's WAL stream directly.

So just configure the second replica to use the master's WAL stream.


How to group records in mysql query I have a table (user_access) with the following columns in mysql database

id      int
user    int
access_time    datetime

I am trying to run a query that gives me the number of times users accesses the system (access_time) every 15 minutes between two specified timestamps. I am still developing the query, this is what I have now. Any suggestion will be highly welcomed.

select user, count(user) as users from user_access
where (access_time between '2013-05-28 02:00:00' and '2013-05-28 10:00:00')
group by user

Sample Result will be something like:

Time                User       No of Times
--------------------------------------------------
8:00am - 8:15am     user1          20
8:00am - 8:15am     user2          5
8:15am - 8:30am     user1          15
8:15am - 8:30am     user2          23

This query should do it for you

select
    tm,(tm + interval 15 minute) tm2,user,users
from
(
    select tm,user,COUNT(1) users from
    (
        select user,(access_time - interval
        MOD(unix_timestamp(access_time),900) second) tm
        from user_access where access_time between
        '2013-05-28 02:00:00' and '2013-05-28 10:00:00'
    ) AA
    group by tm,user
) A;

I'll leave the formatting to you

Give it a Try !!!


Best table name instead of "user", if the DB doesn't like that Yes, I know, it is possible to quote the name of the table, but it is sometimes improper.

If there is no alternative, I name this table as "users", although this semantically unneeded plural makes things a little bit inconsistent.

What alternative solutions do exist?

Put some context in the name. If the table is for a CMS, maybe name the table CMS_USER. If it's a reporting tool, REPORT_USER. You could include the application name in it, as ${applicationName}_USER. Failing those, would just APPLICATION_USER be OK? I did once see a table named UZER, along the tradition of naming a variable representing a class as clazz, but I'm not too fond of that one. You could do something like U5ER, since a "5" sort of looks like an "S". Or you could just go totally phonetic and name the table something like YOOZER.


Optimizing a compare query Suppose I have two tables, A and B, and I know that size(A) = size(B). I want to confirm that the data in both tables is the same in three given columns, suppose they are X, Y, and Z (there are no keys on the table).

For that, I would do:

 SELECT COUNT(*) FROM
     (
        Select
            X, Y, Z
        From
            A
     )
     MINUS
     (
        Select
            X, Y, Z
        From
            B
     )

Now, I really don't need to know count(*) value, as long as there is one mismatch between the data, i.e. values tuple exists in A but not in B, I know that the tables are not identical. Is there a way for me to say this in SQL? I.e. as soon as MINUS encounters one mismatched value, return a value from a query indicating that?

Thanks!

Not sure how such a query should be optimized for Oracle, when no indexes are available but here's another rewriting. It will not calculate the number of mismatching tuples but will stop searching when it finds one:

SELECT 1
FROM dual
WHERE EXISTS
     (
        Select
            X, Y, Z
        From
            A
     MINUS
        Select
            X, Y, Z
        From
            B
     ) ;

SQL-Fiddle shows a similar performance for all the provided answers, as the two tables have to be fully scanned in the worst case.

Rolando's and Narendra's queries show different execution time (sometimes better, sometimes worse), I think because of the HASH JOIN used.

There may be different results with bigger tables and with fewer or more matches between the two tables. (the test were done with only a few K rows).