texts: what would be the right permission to allow everything else but overwriting or creating a database?

texts:
I have a test environment where the AD group mycompany\developers is currently sysadmin.

Recently I have had some issues, specially regarding to people restoring databases. My concerns are:

sometimes the database is involved in replication
the permissions are overwritten - the correct way is to save the
current permissions before the restore and re-apply them after the restore.
Have you checked the current available disk space in all drivers,
specially those used in this restore?
there might be other people using or working on that specific
    database, and restoring over it without communication can cause
    someone's else work being lost
in the dev environment the databases can be in simple recovery mode.
Have you shrunk the log and changed the database to simple recovery
mode.
in this particular case, the full backup for every user database is
going to run automatically every night. but anyways, have you
checked for scheduled backups?
have you deleted from the server the backup that you used to do the
restore?
the folders and drives or data and logs are different in live and
test. are you sure you moved the files to the right places?
Have you had a look at orphaned users and logins?

I particularly have concerns when developers create new databases, even in the dev environment. why?

because they start by creating the database in dev and then just ask me to copy it to live.
have you done any capacity planning for this database? how big it should be and how much it would grow in a month?

have you isolated or considered different filegroups for demainding
objects\indexes?
Have you checked the current available disk space in all drivers,
    specially those used in this DB creation?
how big is the transaction log and why? Initially I like to set it
1.5 times the biggest clustered index.
what are the autogrowth settings?
what are the current permissions?

I have realised some people are not so easy to deal with, I rather reduce their power over that server.

I don't want to do their jobs, but I would like to restrict them from creating new databases, or overwriting existing ones, either by restoring or by attaching files, etc.

They still should be able to create and run jobs, create\alter any object withing user databases, create logins, grant permissions etc...

Basically the question is:

How to limit the power of a current sysadmin?
I thought about not granting sysadmin but a set of server permissions (excluding those to create/alter databases) that would allow them to do everything else.

what would that set of server permissions be?


texts: Locate the file that a given non-clustered index is stored on?

texts: Is there a query to view which file a given non-clustered index is stored on?


texts: How to retrieve a column which is not included in group by

texts:
I have tried the CTE and subquery approaches in my query below but it didn't work.

I have two tables with two columns; I would like to use only one column  for the join condition, union all, and group by as per the requirement. I would like to fetch the other column without mentioning it among the group by columns. How can achieve this?

SELECT BusnPartClientSuppContractId
    ,DivBranchId
FROM (
    SELECT A.BusnPartClientSuppContractId
        ,A.DivBranchId
    FROM tblBusnPartClientSuppContractDivBranch AS A
    WHERE A.BusnPartClientSuppContractId IN (
            SELECT BusnPartClientSuppContractId
            FROM @BusnpartIdsOfOtherContracts
            )
        AND A.BusnPartClientSuppContractId NOT IN (
            SELECT TOP 1 BusnPartClientSuppContractId
            FROM @SuppClientDivBranch2
            )

    UNION ALL

    SELECT B.BusnPartClientSuppContractId
        ,B.DivBranchId
    FROM @SuppClientDivBranch2 AS B
    ) tmp
GROUP BY BusnPartClientSuppContractId
    ,DivBranchId
HAVING COUNT(*) = 1
ORDER BY DivBranchId

I need BusnPartClientSuppContractId but I dont want to include it in the group by clause.


texts: Show clients with staff assigned and no staff assigned

texts:
I am trying to write a query to show a client list including whether or not there is a staff member assigned. If I use this query:

SELECT g.name AS group,  cl.name_lastfirst_cs AS client
FROM clients cl, groups g, link_group lg
WHERE lg.zrud_group = ?
AND lg.zrud_group = g.zzud_group
AND cl.zzud_client = lg.zrud_client
ORDER BY cl.name_lastfirst_cs

It shows all clients in a group although the staff assignment is left out. If I add the staff in:

SELECT g.name AS group,  cl.name_lastfirst_cs AS client, s.staff_name_cs AS Staff
FROM clients cl, groups g, link_group lg, staff s
WHERE lg.zrud_group = 'NEWHAN'
AND lg.zrud_group = g.zzud_group
AND cl.zzud_client = lg.zrud_client
ORDER BY cl.name_lastfirst_cs

It results in a blank result. Can anyone steer me to the correct way to build this?

UPDATE

I believe I have the answer resulting from your help:

List of clients by group No Staff

SELECT g.name AS group,
cl.name_lastfirst_cs AS client
FROM clients cl
JOIN link_group lg
ON cl.zzud_client = lg.zrud_client
JOIN groups g
ON lg.zrud_group = g.zzud_group
WHERE g.name = 'BEAUFT'
ORDER BY cl.name_lastfirst_cs

With Staff

SELECT g.name AS group,
cl.name_lastfirst_cs AS client,
s.staff_name_cs AS Staff
FROM  clients cl
LEFT OUTER JOIN link_group lg
ON cl.zzud_client = lg.zrud_client
LEFT OUTER JOIN staff s
ON cl.zrud_staff = s.zzud_staff
LEFT OUTER JOIN groups g
ON lg.zrud_group = g.zzud_group
WHERE g.name = 'MARTIN'
ORDER BY g.name, cl.name_lastfirst_cs


texts:
I installed and configured pgbouncer 1.5.4 on Ubuntu 14.04 LTS and I have a hard time interpreting log messages.

Basically, I have seen many errors:

2015-12-10 18:09:16.835 30488 ERROR varcache_apply failed: ERROR: invalid value for parameter "TimeZone": "localtime"

I have googled and some articles told me the root cause is coming from timezone or encoding and date style.

I tried to implement this on pgbouncer.ini:

db-order= host=xxx.xxx.xxx.xxx port=4326 user=xxxx password=xxxx timezone='Asia/Jakarta' datestyle=ISO client_encoding=UNICODE

I then tried to reload the config but still no luck and I am still seeing the same error.

Is the config need to be restarted rather than reloading?


texts: Migrating everything to do with a schema from one Oracle DB server to another

texts:
We have an old DB server, running Oracle 11g on Solaris.

We also have a new DB server, also running Oracle 11g, but on RHEL.

We want to move a schema to the new db - but we need everything it refers to moved too.  The data /tables can be moved easily enough, but then there's sequences, stored procedures, db types, views and maybe other things I'm not aware of yet.

The original schema contains different objects assigned to specific tablespaces and we need the same assignments in the destination server too.

What's the best way to accomplish this?

It looks like expdp/impdp can export and import other object types, but I can't find a list of the types to include, or how to say "everything".

Also DBMS_METADATA.GET_DDL looks useful, but again I can only find examples for specific object types and I want "everything in the schema", ideally.

Paul


texts: Query other database's INFORMATION_SCHEMA tables

texts:
I have a shared database that stores procedures for all the other databases on the same server. This helps maintain one instance of a procedure rather than copying it across all databases. As long as I prepend DBName to all my tables, i.e. SELECT * FROM DBName.Table, I've been able to successfully have a shared repository for procedures.

However, I've run into an instance where I need to reference INFORMATION_SCHEMA tables, but the shared database only has tables for that database. Is there a concept of querying INFORMATION_SCHEMA on another database?

I'd like to do something like this, but it doesn't work:

SELECT * FROM [DBName].INFORMATION_SCHEMA.TABLES


texts: Which is more efficient for searches on JSON data in Postgres: GIN or multiple indexed columns?

texts:
For example, say I have a table with a medium number of rows (~100,000 or so) that has a jsonb column with the following example data in one of the rows:
{"name":"Bob", "favoriteColor":"red", "someOtherObject": {"somethingElse": true}}

Is it more efficient to:

Use a GIN index on the jsonb column, then use Postgres' built-in JSON query functions to pull out data

Create several columns to represent things that can be searched for i.e. one column for name, one column for favoriteColor, etc; build b-tree indices on those columns and then run a select document from table where name = 'Bob'

Some other solution?
Keep in mind the solution needs to efficiently support like queries to be able to search for values that start with a given input string.


texts: How to convert datetime string of different formats in a valid datetime in SQL 2008

texts:
I have a table in SQL database in which there is an NVARCHAR(MAX) column. In it, the data is stored as 'Datetime1&Datetime2&Datetime3&Datetime4...'(& separated dates. I know this is not correct way to store data but I have to support an older system).

Now the issue is that this table is used in multiple sites and hence the data coming in this column could come in different formats. Means, for one site, the datetime could be in the format '22-06-2015 13:00:00' and for some other site, the datetime could be in the format '2015-06-22 13:00:00' or '2015-22-06 13:00:00'.

I have to break the data from this column and store individually each value as a datetime in a new row in another table. This has to be datetime only as I need to do some date operations like comparison, Datediff, Dateadd etc.

So, in my Stored Procedure, if I select value as convert(datetime,[date],121), then it gives error if the string is in any other format than 121(yyyy-mm-dd hh:mm:ss.mmm).

If I check whether the string is starting from a year, then I am sure it would be in the format YYYY-MM-DD, but if its starting from a month or day, then I can differentiate between the two in case of dates less than or equal to 12.

So how can I address this problem that this datetime string could be converted to actual datetime without any issue of a particular format or system format?


texts: How to check for existence of an INDEX on a column independent of used SQL database

texts:
Is there are unified way of how to check for the existance of an INDEX for a given column irregardless of the actual SQL database system used?

For MySQL one could for instance check for the existance using SHOW CREATE TABLE mytable. In the result there would be something like this if column mycolumn has an index: KEY 'Index_1' ('mycolumn').

Is this indicator KEY unified among all SQL database systems?

Are there better ways to check for an index?


texts: Pointers to multiple index allocation mappings

texts: In sql server 2008, the sysindexes table contains a pointer to the first page of the index allocation mapping. If a table consists of multiple IAM pages, will the sysindexes table contain a pointer to each one of those as well, or will the first IAM page point to the second and the second point to the third and so on... ...I checked the books online but wasn't able to find any information on this.


texts: Best method to implement HA and DR, replication via 1 PUB and 2 SUB or replication and LS both

texts:
I need a advise on one of our projects where:

We have 3 SQL database servers A, B and C:

A is the live production server with around 200 users with a database of size 1.5 TB hosted.

B is going to be used for reporting purpose only so we would implement transactional replication on this server B.

C is just going to be used for DR purpose, in case A goes OUT we can use c to recover. Clustering is not proposed by client so that is out of question.

All above servers are SQL sever 2014 enterprise edition:

So my question is:

1) Should i go with option of setting replication on this server C as well, that is one PUBLISHER A and two subscribers B and C. If yes, what points should be taken in consideration or i need to take care of before moving ahead with this?

2) We have to anyhow set up replication on server B, but for server C if point 1 cannot be achieved can we implement log shipping , that means a same database on server A can be used both for replication and Log shipping both. is that possible, then we are looking to implement log shipping from server A to C for every 15 minutes.

Note: The DB on server A is 24*6 usage. (Sunday off)

Please advise, thanks!


texts: Postgresql - Minor version upgrade 9.3.5 to 9.3.6 on Ubuntu

texts:
I am two servers: A & B. My master database is running on A and the version is 9.3.5. It's replica is running on B and the version is 9.3.6. I am trying to use B as UPSTREAM slave, and trying to start another database (on different port) which would be replica of B.
So the desired setup is like this:
A:5432(master) -> B:5432(slave-upstream) -> A:5433(slave-downstream)

When I try to start A:5433(slave-downstream), it says:

FATAL:  could not connect to the primary server: FATAL:  no pg_hba.conf entry for replication connection from host "5.6.7.8", user "replicator", SSL on
        FATAL:  no pg_hba.conf entry for replication connection from host "5.6.7.8", user "replicator", SSL off

I've following entries on B:

host    all             all             5.6.7.8/32       md5
host    replication     all             5.6.7.8/32       md5

I am suspecing the problem might be due to the difference of versions? If it's the case, how can I upgrade postgresql on A to 9.3.6?

Or anyother help?

Thanks


texts: How to send Email from a SQL Job with results of multiple select statements

texts:
I want to create a SQL job and send an emails with multiple select statements including in query results. Is it possible with SQL Job or should I use SSIS?

Edit 1:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Adventure Works Administrator',
    @recipients = '****@abc.com',
    @Execute_query_database = 'AdventureWorks',
    @query = 'SELECT COUNT(*) FROM AdventureWorks.Production.WorkOrder
                  WHERE DueDate > '2018-03-30'
                  AND  DATEDIFF(dd, '2004-04-30', DueDate) < 2
              SELECT * FROM NorthWind.Production.Work
                  WHERE DueDate > '2018-04-30'
                  ' ,
    @subject = 'Work Order Count',
    @attach_query_result_as_file = 1 ;

  Error message: File attachment or query results size exceeds allowable
  value of 1000000 bytes.

Edit 2: I feel that SQL agent job not pointing to desired DB and sp_send_email has no database name. so I added @Execute_query_database = 'AdventureWorks' but still there is no improvement because in SQL SELECTS using two different databases.

Any thoughts?


texts: How SQL Server distributes queries between Database Engine and FTS Engine

texts:
When a client submits a query to the database server, then SQL Server and its Database Engine parses, compiles, and execute those queries.

But, if the client's application submits a query which consists some of Full-Text-Search clause, than SQL Server uses FTS Engine to compiles and execute query.

What is tricky for me is whether the entire FTS query is executed by FTS Engine or divided somehow in two parts that are sent to Database Engine and FTS Engine, respectively.

Can someone explain to me what is really done at heart of the SQL Server in case of a SQL query that includes FTS clauses?


texts: Select row if column data was not selected

texts:
How can I selected rows from a table when a column data is different.

Table: id(pk), name, title, book_id, date

I want to get all the rows where book_id is uniq.

Data example:

test1, test2, 1, 2015-01-01
test2, test4, 1, 2015-01-02
test2, test3, 1, 2015-01-02
test2, test2, 4, 2015-01-03
test4, test2, 5, 2015-01-04
test5, test2, 5, 2015-01-05

Expected output:

test1, test2, 1, 2015-01-01
test2, test3, 1, 2015-01-02
test2, test2, 4, 2015-01-03
test4, test2, 5, 2015-01-04


texts: Total Fragments 99% and I cant get it to stay down

texts:
I have a SQL Server 2008 R2 database it is of size 13000.31 MB and my largest table has around half a million records. It is a database that I have bought. It already has indexes but when I run the database it is slow. It is slower than the database it replaced (from the same company) and should be an up grade. I have tried to rebuild the indexes as most of them are sitting at 99%. after the re-build they go to 0. but with in a few minutes they are back at 99%.

Why is this happening?


texts: MySQL shows CPU usage over 40% though total CPU usage is less than 30% as per TOP - How to debug this issue?

texts:
Not sure if I am reading this incorrectly. In the below screenshot of TOP command, it shows that mysql process CPU usage is 41.2% but at the 4 core CPU shows a total of 26% usage.

is MYSQL not configured correctly? Or is MySQL not using all cores? How do we ensure that MySQL is configured properly and uses the resources optimally?


texts:
I am trying to extract all the variable from the following string;
string = 'factor1|factor2|factor3'
This is just a test string. In real string, I do not know the number of factors. What I want is following;

ID  |  Var1 |  Var2 |  Var3 |  VarN  1  Factor1  Factor2 factor3 FactorN

What I have tried is as following but I have to provide everything manually in order to get all the N factors. I want something generic. I have googled a lot and I have searched a lot but couldn't find anything useful.

 select SUBSTRING('factor1|factor2|factor3'
            ,CHARINDEX('|','factor1|factor2|factor3') + 1
                    ,CHARINDEX('|','factor1|factor2|factor3',CHARINDEX('|','factor1|factor2|factor3')

                                        )
                            -1)


texts: Temporal tables: history table population

texts:
According to the prominent diagram here, the main table in a temporal table set-up is populated during the insert, and the history table is populated during update or delete.

However when I look at a query plan inserting or merging a temporal table tblWhatever, considerable cost is associated with the operator Table Insert [tblWhateverHistory] and when I re-create the clustered index on the history table, dropped before populating the temporal table, the allocated storage jumps considerably.

It all leads me to believe, that the history table is populated with a copy of every record in the temporal table initially, but those history copies are not accessible until and unless the temporal records are touched by updates or deletes. Can anyone confirm or prove me wrong?


texts: Locking a table and disabling the indexes hangs mysql - 100% CPU

texts:
CREATE TABLE `runs` (
  `RunID` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`RunID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `runs`
--
-- WHERE:  runid=430864838

LOCK TABLES `runs` WRITE;
ALTER TABLE `runs` DISABLE KEYS;
INSERT INTO `runs` VALUES (430864838);

As soon as ALTER TABLE `runs` DISABLE KEYS; runs it seems to hang the database - a core is 100% being used and it will not respond.

What is the cause of this behavior?


texts: Could I use a copy of the database directory instead of using initdb?

texts:
I have an offline Windows software that is installed locally on each user's computer. Each installation uses its own standalone PostgreSQL database. To create the database on each user's computer, the software uses the initdb command and then creates the tables and keys using normal SQL.

I want to avoid using the initdb command on each user's computer because sometimes this command fails (especially when the user uninstalls the Microsoft Visual C ++ 2010 x86 Redistributable).

I was thinking of embedding a copy of the initial database directory in the installer, so I did not have to use initdb. Would this work? Is there a better alternative?

Note: All users use the exact same version of PostgreSQL and the same database structure. However, they use different versions of Windows.


texts: How to count repeated rows taking into account other rows in the middle

texts:
For SQL server 2008 R2
It seems challenging to come up with a single query to the following:
Example given columns a & b:

a |   b
-------------
0 |  2000
1 |  2001
1 |  2002
1 |  2003
2 |  2004
3 |  2005
1 |  2006
1 |  2007
4 |  2008
1 |  2009

Goal: Mark rows with repeated column a and give them unique number taking into account other values in between. Result should be in column c. Note the most difficult part here is to populate column c with 2 & 5 & 7.

a |  b   |  c
-------------
0 |  2000 | 1
1 |  2001 | 2
1 |  2002 | 2
1 |  2003 | 2
2 |  2004 | 3
3 |  2005 | 4
1 |  2006 | 5
1 |  2007 | 5
4 |  2008 | 6
1 |  2009 | 7


texts: The datediff function resulted in an overflow. But should not

texts:
I have this stored procedure in SQL-Server 2014

USE [provbank]
GO
/****** Object:  StoredProcedure [dbo].[GetHBSeconds]    Script Date: 2018-10-05 08:40:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetHBSeconds]
AS
BEGIN

DECLARE @hbdatetime datetime
DECLARE @diffSec int

SET @hbdatetime = (SELECT HBDateTime from HBData WHERE ID=1)

SET @diffSec = datediff(second,@hbdatetime, convert(datetime,convert(char(19), getdate(),126)))

SELECT @diffSec AS DiffInSeconds , @hbdatetime AS 'LastHB', convert(datetime,convert(char(19), getdate(),126)) AS 'CurrentTime'

END

And every now and then I get this error:

  The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

What I have in the database is one row with a datetime column and an id.

I have a separate program that writes in this row the current timedate like '2018-10-05 09:58:30.000'. And then I have the SP to calculate between current time and the timestamp to get # of seconds in difference between now and the timestamp. I don't think having the diff in seconds would be too narrow/too precise.

Is there something I can change in my SP to avoid this error?

Because the difference in the table and getdate() on the same machine/server shouldn't be more then a few seconds apart if all works, which it is, when I get this error.


texts: Cumulative array aggregation in Postgres

texts:
Does anybody know if it is possible perform cumulative array aggregation in Postgres? For example, to transform this:

create table records
(
  data text,
  path text[]
);

insert into records values
('John', '{"AAA", "BBB", "CCC"}'),
('Paul', '{"AAA", "BBB"}'),
('George', '{"AAA", "BBB", "CCC", "DDD"}'),
('Ringo', '{"EEE"}');

into this:

John    {AAA}
John    {AAA,BBB}
John    {AAA,BBB,CCC}
Paul    {AAA}
Paul    {AAA,BBB}
George  {AAA}
George  {AAA,BBB}
George  {AAA,BBB,CCC}
George  {AAA,BBB,CCC,DDD}
Ringo   {EEE}
Ringo   {EEE,FFF}


texts: Executing Powershell SMO against a cluster with elevated privileges to update service accounts

texts:
I'm working on a Powershell script to update service accounts on named SQL instances in a failover cluster.  I'm trying to use the SMO and WMI to update this so that I can update the service account on the active node and have it populated to the other passive nodes (as would happen if I updated manually in the configuration tools).  I'm getting hung up on trying to execute the script on my local machine but updating a remote server. The error I see appears to be security related based on my Google searches and is successful if I run the script locally on the remote machine with elevated permissions (as administrator).

The code:

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
$mc=New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer "SQLHOST01"

$srvcs= $mc.Services | Where {$_.name -like "*INSTANCE01*"} | Where {$_.Type -like "Sql*"}

$srvcs | ft name,servicestate,serviceaccount

foreach ($s in $srvcs){
    $s.SetServiceAccount("FOO\Bar","strongpass")
}

Error:

Exception calling "SetServiceAccount" with "2" argument(s): "Set service account failed. "
At line:11 char:5
+     $s.SetServiceAccount("FOO\Bar","strongpass")
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : FailedOperationException

Using:
Windows 2008 R2 (remote)
SQL 2008 R2 (remote)
Windows 7 with SQL 2012 sqlps module (local)

Update:  The account I am executing the script under has administrator rights on the remote machine.

The primary question here is how can I make my call from the client machine act as if it were running under elevated permissions on the remote machine?


texts:
I am taking backup of 2 tables from different schema.
I need to create the "use dbname" statement at the beginning of each table.

mysqldump --databases company --tables loadme --databases new_company --tables new_load

use company
...
usual loadme dump
...

use new_company
...
usual new_load dump
...

This does not seem to be possible with mysqldump command.


texts: Can oracle use more memory than memory_max_target?

texts:
My system has 34GB RAM, however, I have set

memory_max_target = 3G
memory_target = 3G

Oracle memory parameters are specified using G (GB), M (MB) or K (KB)
...and I use AMM (Automatic Memory Management).

Even though that's the case when I check the current usage of my memory with

**free -m**

...it shows that around 33 GB of the total has been used. So I guess, Oracle also uses some of it (yes, I mean more than memory_max_target size, which is 3 GB).

So, my questions are:

Can Oracle use more memory than what we have set for memory_max_target?
Or is that a "hard" limit that Oracle never changes, even though the system has plenty of free memory?


texts: How do I recover 350 Gigabytes of unallocated space

texts:
I was left with a scenario where my Database Initial size is 370GB.

When I checked the EXEC sp_spaceused, I can see almost 360GB of unallocated space .

Does unallocated space means unused space? If it is unused space, why this is causing the issue for storage?

What options do I have apart from shrinking the .mdf file with 360GB of unallocated space?

Also, as this is a huge database, what level of increments I can mention in the place of 'x' below - can I start from 10,000 MB every time I shrink?

USE Demo
GO
DBCC SHRINKFILE (N ‘Demo’, x)
GO

Is there anything else I should consider in this type of situations?

Would adding a new data file help?


texts: How do I enable md5 authentication for only one user?

texts:
I just installed Postgres 9.2 on CentOS 7. I want to keep peer authentication for all my users except one, which I would like to be able to access via "psql" through any user's account. I tried editing my /var/lib/pgsql/data/pg_hba.conf to be this

local   all             all                                     ident
local   all             scale                                   md5

but I'm still unable to login with the user "scale" unless I'm in that user's account. What do I need to do to set up md5 authentication for this one user only?


texts:
We have a long running job for report data generation and we are not worried about changes to the source data after the job has started. For this case the SNAPSHOT isolation mode seems to be a good fit. But we are looking at a possible way to use Snapshot isolation from a scheduled SQL job.

When connecting through a client using ADO.NET we are able to mention the transaction to use Snapshot Isolation (SqlTransaction sqlTran =  connection.BeginTransaction(IsolationLevel.Snapshot)). But there seems to be no way of running a scheduled job with the same isolation.

Is there a way we can run an SQL job under snapshot isolation level?


texts: Postgres on Engineyard space at 100%, pg_xlog filled up with files

texts:
I have a database that is growing rapidly. VERY rapidly. My /db directory is at 100% now, and there are a bunch of new files in pg_xlog.

My background is in Oracle and MySQL. I do not know what is going on with this database. I know that I need to hire a Postgres DBA quickly.

Is there anything in the immediate term that I can do to fix my issue? I notice that deleting data does nothing to mitigate my space issue.

Output from pg_settings is:

archive_command             /bin/true                       configuration file
archive_mode                on                              configuration file
archive_timeout             1min                            configuration file
checkpoint_segments         100                             configuration file
checkpoint_timeout          5min                            configuration file
client_encoding             UTF8                            session
DateStyle                   ISO, MDY                        configuration file
default_statistics_target   100                             configuration file
default_text_search_config  pg_catalog.english              configuration file
effective_cache_size        1358MB                          configuration file
hot_standby                 on                              configuration file
hot_standby_feedback        on                              configuration file
lc_messages                 C                               configuration file
lc_monetary                 C                               configuration file
lc_numeric                  C                               configuration file
lc_time                     C                               configuration file
listen_addresses            *                               configuration file
log_destination             csvlog                          configuration file
log_line_prefix             %m:                             proc=%p,user=%u,db=%d,host=%r   configuration file
log_min_duration_statement  2s                              configuration file
log_rotation_age            1d                              configuration file
log_rotation_size           100MB                           configuration file
logging_collector           on                              configuration file
maintenance_work_mem        128MB                           configuration file
max_connections             512                             configuration file
max_files_per_process       65535                           configuration file
max_stack_depth             6MB                             configuration file
max_standby_streaming_delay -1                              configuration file
max_wal_senders             5                               configuration file
port                        5432                            configuration file
search_path                 public, "$user", public         session
shared_buffers              424MB                           configuration file
temp_tablespaces                                            configuration file
wal_buffers                 8MB                             configuration file
wal_keep_segments           128                             configuration file
wal_level                   hot_standby                     configuration file
wal_writer_delay            200ms                           configuration file
work_mem                    32MB                            configuration file


texts: Is there a way to hide mysql innodb cluster queries from general_log?

texts:
When I start my innodb cluster I get many lines of cluster related logs in the mysql general_log file like below, how can I hide these from the log?
2020-06-26T14:40:43.719029Z     31774 Connect   mysql_router1_6dv772yz7qu1@dbsg1 on  using SSL/TLS
2020-06-26T14:40:43.719494Z     31774 Query     SET @@SESSION.autocommit=1, @@SESSION.character_set_client=utf8, @@SESSION.character_set_re
sults=utf8, @@SESSION.character_set_connection=utf8, @@SESSION.sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
2020-06-26T14:40:43.719659Z     31773 Quit
2020-06-26T14:40:43.720231Z     31774 Query     SET @@SESSION.group_replication_consistency='EVENTUAL'
2020-06-26T14:40:43.720509Z     31774 Query     START TRANSACTION
2020-06-26T14:40:43.720740Z     31774 Query     SELECT * FROM mysql_innodb_cluster_metadata.schema_version
2020-06-26T14:40:43.721185Z     31774 Query     select cluster_type from mysql_innodb_cluster_metadata.v2_this_instance
2020-06-26T14:40:43.723301Z     31774 Query     select I.mysql_server_uuid, I.endpoint, I.xendpoint from mysql_innodb_cluster_metadata.v2_instances I join mysql_innodb_cluster_metadata.v2_gr_clusters C on I.cluster_id = C.cluster_id where C.cluster_name = 'prodcluster' AND C.group_name = '8a70e310-a997-4d59-8e81-92dab9daa5be'
2020-06-26T14:40:43.723985Z     31774 Query     COMMIT
2020-06-26T14:40:43.724317Z     31774 Query     show status like 'group_replication_primary_member'
2020-06-26T14:40:43.725621Z     31774 Query     SELECT member_id, member_host, member_port, member_state, @@group_replication_single_primary_mode FROM performance_schema.replication_group_members WHERE channel_name = 'group_replication_applier'
2020-06-26T14:40:43.793743Z     31771 Query     START TRANSACTION
2020-06-26T14:40:43.933131Z     31770 Query     SELECT * FROM mysql_innodb_cluster_metadata.schema_version
2020-06-26T14:40:44.032471Z     31771 Query     SELECT * FROM mysql_innodb_cluster_metadata.schema_version
2020-06-26T14:40:44.171571Z     31770 Query     select cluster_type from mysql_innodb_cluster_metadata.v2_this_instance
2020-06-26T14:40:44.226921Z     31774 Quit


texts: How to update table1 column from table3, with matching id from table2

texts:
I have 3 tables - customer, garages, the_cars. With columns like

customer           garages                  the_cars
id | car       owner_id | the_car_id       car_id | car

and need to update the_cars.car with customer.car if customer.car is null, and based by ids. Where customer.id matches with garages.owner_id, find by its garages.the_car_id the the_cars.car_id, and by it finally get the_cars.car and put into customer.car

the code I tried is, don't judge strictly, I'm new to this kind of sql queries

UPDATE `customer`
SET `customer`.`car` = `the_cars`.`car`
WHERE (`customer`.`car` IS NULL)
AND (`customer`.`id` = `garages`.`owner_id`)
AND (`garages`.`the_car_id` = `the_cars`.`car_id`)

I think that will help others too.


texts: is it possible to perform differential backup from primary and full and t-log from secondary in Always-On? Is there any data inconsistency issue while restoring these backups file?


texts: Create a stored-procedure to insert multiple rows on a junction table and other tables

texts:
I am new to Databases and Design and needs some help on a INSERT issue.
I want to create a procedure that will insert multiple values to different tables, I need to make sure it stays atomic so I need to use transact in the procedure .

Invoice_Table

--Invoice_ID (PK)

--Purchased_Date

Fruit_Table

--Fruit_ID (PK)

--Fruit_Name

Driver_Table

--Driver_ID (PK)

--Driver_Name

--Driver_LastName

Invoice_details_Table

--Invoice_ID FK

--Driver_ID FK

--Fruit_ID FK

--Quantity

--Amount

The reason why I created a junction table( Invoice_Details_Table) is for the reason that some invoices will have more than one driver with one or more fruits.

For example a trip was done so we need to create an invoice(1). Two drivers went on this trip and delivered Strawberries and Apples.

Trip number two so invoice(2) needs to be created. This delivery was with one driver and only apples were delivered.

The problem that I am having is how to create a procedure that will update the invoice table and the Invoice_Detail_Table and rollback if something happens in the insert process. Also how can I pass the driver/fruit parameters since sometimes it will be more than one.

I am using SQL Server 2012.
Any help is much appreciated


texts: Same tables with same data, different SQL Server stored procedure performance

texts:
I have 2 SQL Server 2008 instances installed on the same server. They have same table structures with same data. I have a stored procedure deployed on both of them. The stored procedure performs differently on them. It takes 5 seconds on one instance and 50 seconds on the other to execute the procedure. I think the possible reasons for this are: index fragmentation, outdated statistics. I defragmented the indexes and updated the statistics but still no luck. Any ideas on this issue?

Thanks


texts: Sql Server And Very Hot CPU % of Total Wait Time

texts:
I have recently started monitoring a production database with transactional replication. The CPU % of Total Wait time = %93.00.

This is horrible. I confirmed all 8 processors are being utilized. This database has been in production for six years, so I am wondering is the 93 percent reporting 93% of all processors or is there something I am missing?


texts: Can a Couchbase application work with no network connection (like CouchDB)?

texts:
I'm building an application where users running android tablets will have to be able to write to a database (running on their local device) with NO network connection and sync using peer-to-peer replication (through a USB connection or possibly bluetooth). I'm working in West Africa, where internet is basically nonexistent.

I know that CouchDB will work for this use case, but it seems like it's difficult to get CouchDB running on Android (compared to Couchbase). Will Couchbase work for this project?

Thanks!
- avi


texts: Merge Replication - Merge initial data contained on subscriber

texts:
I have two identically structured databases on two separate servers.  Both have different data within them.

I have set one of them up with merge replication as a publisher and I want the other to be a subscriber.

The problem is that when I create the subscription, the publisher sends its snapshot to the subscriber and it clears all of the data from the subscriber database before it begins the sync process.

How can I preserve the data in both databases and set them up so that one is the publisher and the other is the subscriber?


texts: Disable a bad SQL Server Query Plan vs. Force a good plan

texts:
I like to leave query planning up to the database as much as possible, and only force when absolutely necessary. I'll usually do this in SSMS with the 'Force plan' option.

So below, my purple query went rogue today and timed out 10 times in a row, but the black one seems to be working great. So I can Force the black query.

But what about the turquoise plan vs. the black? They're about the same.

I've been wondering for a while why there doesn't seem to be any option to 'Disable' a query plan from running. I know I can delete a plan from the cache, but there's nothing stopping it recreating it right?

Especially times when I don't have the resources for a full investigation I'd much rather say 'skip this plan' - but not have to commit to forcing a specific plan. I can find no such feature.

I can't even find a discussion of why this doesn't seem to exist. I was expecting 'Yes a lot of people ask this, and generally it's better to...' - but nothing.

I don't understand also why it picked a timeout plan 10 times in a row (the last purple square) and didn't even attempt the other plans once. But that's probably a different question. Also if you're curious - the above query is to get order history for a specific SKU - so the corresponding number of source rows may vary greatly for each SKU.


texts: Postgresql table lock to update constraint

texts:
I have a table in PostgreSQL 12.
Some data are not formed properly so I need to introduce a check constraint so it doesn't happen again. I have a running system that updates this data continuously and will try to insert the malformed data as soon as I fix it. So I want exclusive access to the table for the time I need to do change the constraint. It doesn't matter if the subsequent inserts fail.
I want to update the data and alter the constraint in a transaction.
BEGIN;
LOCK TABLE vehicle;
UPDATE vehicle SET vin = <fixed-vin> WHERE vin !~ 'some-magic-regex'; -- this is actually done inside a function, not sure that changes anything to my problem.
ALTER TABLE vehicle ADD CONSTRAINT clean CHECK (vin ~ 'some-magic-regex');
-- Fails here because in meantime, my fixed-vin where reinserted again

I don't understand how the lock is working, because it says, according to the documentation, taht ACCESS EXCLUSIVE mode guarantees that the holder is the only transaction accessing the table in any way.
So how come data is inserted in between my update and my alter constraint.
Thanks for the help


texts: Error 2013 in MySQL Workbench when I perform any operation on a certain table

texts:
when using mysql workbench I perform any operation on the table "my_table" I get an error:

Error Code: 2013 Lost connection to MySQL server during query

Previously I stopped the execution of a restore dump and the log showed the message:

"the table may be in an inconsistent state"

How can I solve this problem?


texts:
I am developing a project using oracle. I am writing functions and stored procedures to handle CRUD statements.

My question is in addition to oracle check constraints do I have to check error situations myself or let oracle do the job?

For example if I have a column in a table which is unique and I want to insert into this table I have to check the value to be unique or let the oracle unique constraint do the job and produce an error if it is a repeated value?


texts: How to configure Oracle11g to send out Password Expiry Notifications vide SMS or e-mail?

texts: Please guide me in configuring Oracle11g to send out Password Expiry Notifications vide SMS or e-mail before password expiry of one week or upon user's profile entering into grace period or any other given condition.


texts: MySQL Syntax Error When Creating a Trigger

texts:
I am trying to create a trigger but I am getting a syntax error and I am not really sure why I am.

CREATE TRIGGER Section_Insert AFTER INSERT ON Section
    -> FOR EACH ROW BEGIN
    -> INSERT INTO Audit(changeTime, tableName, Action) VALUES (NOW(), 'Section', 'INSERT');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 3


texts: How to properly design outgoing/incoming mailserver configuration per user?

texts:
I'd like to store incoming and outcoming mailserver configuration per user, it's not a real live project, but to learn database-design (never done before).

Let's say I have a mail_server_configuration table.

| MailServerConfigurationId | Host | Port | Username | Password

and my user table:

| UserId | IncomingMailConfigurationId (+FK) | OutgoingMailConfigurationId (+FK)

That is my current approach.

Q1: Is that the way to go or am I missing something?

Q2: What about data redundancy about hosts and ports? What if two users are using the same host&port? Should there be a "host" table?


texts: SQL Azure large multi-tenant table / lock escalation issue / partitioning / full text index

texts:
Our app organizes its data in "projects" that are saved in an SQL Azure database. A project can contain 100s or 100'000s of rows (spread over 35 tables). Since we have 1000s of projects, using a database or schema per project is not an option (we would end up with 10'000s of tables). We now save every entity in one single table that has a ProjectKey and use row level security.

Occasionally, some users import a large amount of data in one batch / transaction (100'000s of rows). This is a legitimate scenario in our app. For such large inserts, SQL Server performs a lock escalation and locks the complete table. This means that all other users are blocked from saving their data.

We could disable lock escalation, but this does not seem to be a good idea for 100'000s of rows. Another option is to partition the table so that (nearly) every project has its own partition and use (lock_escalation = auto). This way, SQL Server locks the respective partition only. I have made some experiments with 13'500 partitions and things seem to work well.

We also use SQL Server's full text search to index some of our tables / columns. FTS requires a single column unique index. By default, partitioned tables require partitioned indexes, resulting in more than one column. I found out that I can create a single column unique index over all partitions with a "non-aligned" index.

However, this destroys my locking-per-partition strategy. Since the non-aligned index sits on partition 1 and is updated by every insert, a large insert will lock it completely and again lead to the locking of all other users...

Is there a way to use FTS with a partitioned table and have a single-column index for a full text index?


texts: Is it possible to take incremental backup in MongoDB? if yes then how please guide me?


texts: Tuning performance of finding entities with fully matching attribute values

texts:
How to tackle performance tuning of finding matches of entities with the same attributes and values, in an OLTP entity-attribute-value table?

The query needs to run hundreds of times per minute from several application threads in parallel, and the performance needs to be quick and without bringing down the whole instance (ideally under 50ms duration, with accordingly low CPU and IO) -- but calculating all the matches is aggregating across at least hundreds of thousands of rows.

What approaches exist to tackle this type of problem?

Repro to test:

Simplified repro


texts: According to the SQL92 spec, can I store the value 1 in a field where the precision = scale?

texts:
Is it SQL92 compliant to store the number 1 in a field that is defined as NUMERIC 3,3?

What about DECIMAL 3,3?

Does this mean that the precision is a floating decimal place or static that must have 3 decimal places even if they are all zero?


texts: Why am I getting "Snapshot isolation transaction aborted due to update conflict"?

texts:
We have two tables

Parent (Id int identity, Date datetime, Name nvarchar)
Child  (Id int identity, ParentId int, Date datetime, Name nvarchar)

The Child having a foreign key relationship to the Parent.

We have enabled database level read committed snapshot isolation.

We only ever insert and delete rows for Parent and Child (no updates)

We have one process (transaction) which deletes old data from Child (and then Parent)

We have multiple other processes (transactions) which insert new data into Parent (and then Child)

The delete process regularly (but not all of the time) gets rolled back, even though the insert process does not insert new Child rows which refer to the Parent rows which the delete wants to delete - it simply creates new Parent rows and one ore more new Child rows which refer to the new Parent

The error when deleting the Parent rows is:

  Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.Child' directly or
  indirectly in database 'Test' to update, delete, or insert the row
  that has been modified or deleted by another transaction. Retry the
  transaction or change the isolation level for the update/delete
  statement.

I am aware the people suggest having an index on the foreign key column - we'd prefer not to have to do this ideally (for space/performance reasons) - unless this is the only reliably way to get this to work.

Noted this: https://stackoverflow.com/questions/10718668/snapshot-isolation-transaction-aborted-due-to-update-conflict

And pretty good article: https://sqlperformance.com/2014/06/sql-performance/the-snapshot-isolation-level

But neither of these gives me the understanding I would like to have :)


texts: Why is InnoDB waiting for table metadata lock in simple INSERT/DELETE + SELECT?

texts: I've started using a new MySQL server and moved an InnoDB table there. I'm seeing a weird behavior that when I have a long running INSERT / DELETE, any SELECT from the same table is locked which is not something I expect from InnoDB. Is this a configuration problem on my end?


texts:
Follow up from my previous question SQL Server Identity Column in the Cloud:

Are there any general guidelines? Should a person use Clustered Index Identity or GUID in the cloud for SQL Server 2016? Our company many deploy into Cloud (Amazon AWS, Google Cloudcloud), in any type (PaaS, DBaas, VM), not sure which. 'Company Strategy is be able to deploy anywhere.'
We only utilize 5million (70GB) rows per year.

Note: If were ever horizontally elastically scale databases, we can manage identity columns (DB1 gets 1Trillion-2T, DB2 gets 2Trillion-3T) using range identities or location/storage ids.
Guids have higher write slowness and fragmentation than Identity, but are mergable in horizontal scale databases. With Identity, 8 bit, Incremental so faster insertions. If using Guid, our API will generate Newid(), not NewSequentialId.


texts: Added New Subscriber but Replication Not working

texts:
I have one Merge Replication setup with one Publisher and 5 to 6 Subscribers. We have added one additional subscriber to this replication and when we try to sync it's giving the following error.
Error Messages

The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.MSmerge_genhistory_75711cc468114cd2ad98d5573a252083' and the index name 'c2MSmerge_genhistory_75711cc468114cd2ad98d5573a252083'. The duplicate key value is (1334936). (Source: MSSQLServer, Error number: 1505)

Actions Performed to Resolve Issue
I tried the following actions, but it did not help resolve the issue.

Created New Snapshot, but didn't work.
Changed agent profile with verbose history/big query timeout, but still didn't work.
I found one duplicate entry in the dbo.MSmerge_genhistory table with generation=1334936. I removed one entry and tried to sync, but it still didn't work. Then I removed both entries and kept them in a backup table and tried to sync, but again same error.

Kindly suggest what to do.


texts: Improve performance of converting a large xml file (~300 MB) to relational table in SQL Server

texts:
So this is what I have so far:

--Read xml content into a XML data type variable
DECLARE @FileData XML
SELECT @FileData = CONVERT(XML, BulkColumn)
FROM OPENROWSET(BULK '\\file_path\test.xml', SINGLE_BLOB) AS x
--Read from the XML variable to create Entity-Attribute-Value table
SELECT N1.Id.value('@Id', 'varchar(50)') as Id
    , N1.Id.value('@Name', 'varchar(100)') as Name
    , N2.AttributeLongName.value('@AttributeName', 'varchar(100)') as AttributeName
    , N3.AttributeValue.value('.', 'varchar(MAX)') as AttributeValue
FROM @FileData.nodes('/Data/Entities/Entity') as N1(Id) ---1st lvl Node contains the Entity
cross apply Id.nodes('Attributes/Attribute') as N2(AttributeName) --2nd lvl Node contains AttributeName
cross apply AttributeName.nodes('Values/Value') as N3(AttributeValue) --3rd lvl Node contains AttributeValue

This code performs decently when run against an 8 MB XML file (~15s). However, when I try to run it against a 300 MB XML file of the same structure, it takes hours.

Does this mean that the XQuery nodes() method does not scale linearly or scales worst?

Is there any other way I can improve the performance on this?


texts: Migrating SQL Server 2000 to SQL Server 2008

texts:
I'm migrating SQL Server 2000 to SQL Server 2008. I have many views. How do I check each views that if they have an order by clause?

Thanks.


texts: Is it possible to write (A OR B) IN(X, Y, ..., W)?

texts:
Is it possible to write (A OR B) IN(X, Y, ..., W) ?

I need to fill two IN() expr. with same values by hand. Like:

A IN(X, Y, ..., W) OR B IN(X, Y, ..., W)

What is the best option to have only one IN() to fill?


texts: SSDT Schema compare to compare only specific objects

texts: Is it possible to use schema compare in SSDT to compare only specific objects? Not object types, specific objects like 3 tables and 2 stored procedures instead of all tables and all stored procedures?


texts: Does PostgreSQL have a feature to update the "internal" constraint names and whatnot?

texts:
This always happens:

I design (create) a series of tables with PKs, FKs, UNIQUEs and sometimes indexes. pgAdmin 4 seems to add/encourage indexes added when making FKs. I never pick a name myself, but allow it to pick whatever name it wants, which is based on the table name and column names.
I make changes, such as renaming tables and/or columns.
Those constraints now have annoyingly outdated names. Renaming them is anything but straight-forward and apparently requires me to delete and then recreate them, which often is impossible due to data loss or errors preventing them from being deleted.

Is there some way to automatically rename them all so that they match the current table/column/etc. names?

I don't think I have ever directly referred to those constraints by name, so it would not break anything in my application code.

I frankly don't even understand why they need names to begin with, but it's very annoying when they are called things which are no longer relevant.


texts: Rename table in Postgres and update its usages in all functions

texts:
I need to safely rename a table and be sure, that all usages of this table in functions are also renamed.

Example:

CREATE TABLE test (
    id serial,
    name varchar(255)
);

CREATE FUNCTION public.last_test() RETURNS SETOF test
    LANGUAGE sql STABLE
AS $$
    SELECT
      *
    FROM
      test
    LIMIT 10;
$$;

ALTER TABLE test RENAME TO tests;

Now, when I look into function last_test, I see, that it still references old test table name.

Is it even possible to rename a table and all it's usages in functions automatically?


texts:
In SQL Server Management Studio 2014, How to apply a WHERE condition during Generating Script (Data only) for a particular Table?

I'm using Generate Scripts method as I dont have Remote Server access to download Database Backup. However, as the table rows are around 15k and each row is heavy, the script is not generating for the particular table.


texts: Cancelling the rebuilding of a partition with compression

texts:
Trying to enable compression on a large table in production but we are now out of time (been running for 6.5 hours already).

What is the impact of cancelling the operation and letting it roll back?

Trying to determine if we should power through and let it finish or kill it and let it roll back.  However if rolling back a 6.5 hr operation will take just as long, then we may want to just let it finish.

We tested it in QA, which has slower disks, it completed in 4 hours, so we are not entirely sure why this is taking so long.

Things to know:

Table size is 271 GB with approx. 30 million rows
At the 3 hr mark, the drive got full, we expanded it about 40 min later
Prevailing wait type is IO_Completion
sp_whoisactive shows CPU and physical_reads increasing, while reads/writes have not moved for quite some time
This is on SQL Server 2017 Standard Edition, Windows Server 2016

Here is our statement:

ALTER INDEX [PK_t1] ON [dbo].[t1] REBUILD PARTITION = ALL
WITH ( STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, SORT_IN_TEMPDB = OFF
    , MAXDOP = 3, DATA_COMPRESSION = PAGE );


texts: Looping with array of values in PL/pgSQL

texts:
Can we use RETURN QUERY within this loop to display all values from UserResponseList instead of RAISE NOTICE?

FOR i IN 1 .. array_upper(UserResponseList, 1)
LOOP
   RAISE NOTICE '%, %, %', QuestionList[i], UserResponseID_List[i], UserResponseList[i];
END LOOP;


texts: How to show rows with 0 without any records in database

texts:
I have a lot of record in my database and for data analysis, I need to show count of records for each day including days when I do not have any record, so result is 0.

For example I have these records (SQL Fiddle):

CREATE TABLE Tabs
    (`timestamp` varchar(10), `id` int)
;

INSERT INTO Tabs
    (`timestamp`, `id`)
VALUES
    ('2015-01-01', 1),
    ('2015-01-01', 2),
    ('2015-01-02', 3),
    ('2015-01-02', 4),
    ('2015-01-02', 5),
    ('2015-01-04', 6),
    ('2015-01-05', 7),
    ('2015-01-05', 8)
;

When I use this query:

SELECT `timestamp`, COUNT(*)
FROM Tabs
GROUP BY `timestamp`

I get ths result:

timestamp   COUNT(*)
2015-01-01  2
2015-01-02  3
2015-01-04  1
2015-01-05  2

But I need 2015-01-03 too with 0, so correct output for me is:

timestamp   COUNT(*)
2015-01-01  2
2015-01-02  3
2015-01-02  0
2015-01-04  1
2015-01-05  2

How can I realize it please?


texts:
I executed this manually:

GRANT SELECT ON TABLE my_table TO some_user;

How can I dump this statement again?

I looked at pg_dumpd but only found a way to do a data-only dump or a schema-only dump.

If I do the following, then I get both (CREATE TABLE and GRANT statements)

pg_dump --schema-only -t my_table my_db

Is there a way to exclude the CREATE statements, and to show the GRANT statements only?


texts: Grant in advance privileges on Schema A for dynamically created tables on Schema B in oracle

texts:
I have the following scenario in my Oracle Database (11g) and I am trying to work around grants and access privileges.

Note that the following is just a Minimum Viable Example and it does not cover the complete logic of what my program will do.

What I have

MyMainSchema: this schema has several tables in it
MyProcs: this schema has only several procedures in it. Every time I create a table in MyMainSchema I (manually) give S I D U in MyProcs for that table.
MyPlaceHolder: this schema is empty

I have a package and a procedure in MyProcs, which all it does is to

read data from MyMainSchema
create a new table in MyPlaceHolder
insert data in this newly created table
drops this newly created table

To achieve what I want, my solution until now is

run as MyProcs user
read data from MyMainSchema
alter session and switch to MyPlaceHolder user
create a new table in MyPlaceHolder
issue a grant to give S I D U permissions to MyProcs on the newly created table
alter session and switch to MyPlaceHolder user
insert data in this newly created table
do some processing
(at some point) drops this newly created table

I am trying to avoid the user / schema switch and the grant commands.

My question is: Is there a command (or more) that it will run only once and it will result the following:

MyProcs will have the ability to create and drop any table new or old in MyPlaceHolder.
MyProcs will 'automatically' have S I D U permissions on any table that is created in MyPlaceHolder


texts: PowerShell Subsystem max_worker_threads reverts back to default after applying cumulative update for SQL Server 2016

texts:
We change the PowerShell subsystem max_worker threads to 10, but after we install the latest cumulative update, it reverts back to 2.  Is there a way to keep that from happening?

SELECT  s.subsystem_id,
        s.subsystem,
        m.[text] AS [description],
        s.max_worker_threads
FROM msdb.dbo.syssubsystems s
INNER JOIN msdb.sys.messages m ON s.description_id = m.message_id
AND m.language_id = 1033


texts: MySql - partition by range and unique key

texts:
i'm trying to create the following table:

CREATE TABLE `s_relations_with_partition` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `source_persona_id` int(11) NOT NULL,
  `relation_type` int(11) NOT NULL,
  `message_id` int(11) DEFAULT NULL,
  `reply_to_message_id` int(11) DEFAULT NULL,
  `reshare_of_message_id` int(11) DEFAULT NULL,
  `target_object_id` int(11) DEFAULT NULL,
  `target_persona_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `sent_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`,`sent_at`),
  UNIQUE KEY `unique_target_persona` (`source_persona_id`,`relation_type`,`message_id`,`target_persona_id`),
  UNIQUE KEY `unique_target_object` (`source_persona_id`,`relation_type`,`message_id`,`target_object_id`),
  KEY `message_id_index` (`message_id`),
  KEY `reshare_of_message_id_index` (`reshare_of_message_id`),
  KEY `reply_to_message_id_index` (`reply_to_message_id`),
  KEY `source_and_target_object_index` (`source_persona_id`,`target_object_id`),
  KEY `source_target_persona_index` (`source_persona_id`,`target_persona_id`),
  KEY `target_persona_relation_type_message_id_index` (`target_persona_id`,`relation_type`,`message_id`),
  KEY `sent_at_index` (`sent_at`),
  KEY `source_persona_sent_at_index` (`source_persona_id`,`sent_at`),
  KEY `target_persona_sent_at_index` (`target_persona_id`,`sent_at`),
  KEY `target_object_sent_at_index` (`target_object_id`,`sent_at`)
) ENGINE=InnoDB
PARTITION BY RANGE (sent_at) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-02-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-03-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-04-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-05-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-06-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-08-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-09-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-10-01 00:00:00') ),
    PARTITION p10 VALUES LESS THAN (MAXVALUE)
);

and i get the following error:

A UNIQUE INDEX must include all columns in the table's partitioning function

adding sent_at (unix timestamp) to the unique index is not possible.

any ideas on how i can implement a date range partition on that table?


texts: Tips on using SQL access advisor in Oracle

texts: Please share how to use oracle SQL access adviser for single or multiple queries. you can assume that we know the sql ids for the queries we are interested in.


texts:
If we have implemented Index on Primary key column in Table1 and on Referenced column in Table2. But we don't have Foreign key on Referenced Column.

Can you please explain is this good to implement foreign key to utilize Index more efficiently


texts: I am confused on what the difference is between webscalesql and mysql. From my understanding webscalesql is a branch of mysql. Does this mean that webscalesql is a subset of mysql, so it is better to download MySQL rather than webscalesql as the changes made in webscalesql will be merged? If so, why would anyone download webscalesql?


texts: Index rebuild suspended due to IO_COMPLETION wait type

texts:
I am executing the rebuild index script in SSMS to rebuild a specific primary key index of a major table in our database. It has been running for 13 hours now. sp_who2 shows me that it was suspended due to IO_COMPLETION wait type.

Now I don't have any idea to how to deal with the situation.

What is IO_COMPLETION?
How can I make this job runnable from suspended?
Can I kill the reindex job SPID and plan it for some other time? I heard that it's not a good idea as it will take a lot of time to rollback.

When I check to see the percentage of completion of rebuilt index with some query using exec_sql_text and exec_sql_request, it is showing ZERO. Now the physical reads = 310,777,191. A while later the physical reads = 319,741,497.
How long should we wait and let it run? Is there anyway to get/see an estimate?

The table contains 8 billion records. It was fragmented 80 percent.


texts: There is a limit of 10,000 rows that can be returned for a drillthrough rowset in recent version of SSAS. I'm concerned about the risk of of returning partial results without warning if the selection set contains more data than the 10K limit. Is there some mechanism to either display a warning or return no rows at all in that case?


texts:
I have these queries:

(This one was deadlocked)

And this one:

update tbldealerativacao
    set flagusado = 0 from tbldealerativacao WITH (NOLOCK)
    inner join tbldealerusuario WITH (NOLOCK) on tbldealerativacao.coddealer=tbldealerusuario.coddealer
where codusuario = 34527

But , Isn't this an update? Why is this a deadlock?

The update 2 shoudnt wait for the first one?

this is the graph:

I created this test index to see if I can't solve the problem but with no success ( is was using the same index before the Image below, only with 2 more fields in the include ):

create index IX_TESTE
on tbldealerativacao ( flagusado,coddealer,iddealerativacao)

Obs1: I know WITH(NOLOCK) is a bad habit from programmers. I just got this query and I will try to not use it.

EDIT 1:

In the execution plan, I have this index scan for this query:

Update tbldealerativacao
Set flagusado = 0 from tbldealerativacao WITH (NOLOCK)
inner join tbldealerusuario WITH (NOLOCK)
  on tbldealerativacao.coddealer = tbldealerusuario.coddealer
    Where codusuario = 33826

Could this be the problem? 2 sessions waiting for the lock_x?

this is the index:

create  index IX_codusuario
    on tbldealerativacao ( coddealer,flagusado )

EDIT 2:

this is with whoisactive:

EDIT 3:

This is now with no with(nolock)

EDIT4:

Here is the create table queries:

  Tbldealerativacao

CREATE TABLE [dbo].[tblDealerAtivacao](
    [CODDEALER] [int] NOT NULL,
    [CODJOB] [smallint] NULL,
    [FLAGUSADO] [bit] NOT NULL,
    [CHAVEALEAT] [float] NULL,
    [idDealerAtivacao] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_tblDealerAtivacao] PRIMARY KEY CLUSTERED
(
    [idDealerAtivacao] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblDealerAtivacao]  WITH NOCHECK ADD  CONSTRAINT [FK_tblDealerAtivacao_tblJob] FOREIGN KEY([CODJOB])
REFERENCES [dbo].[tblJob] ([CodJob])
GO

ALTER TABLE [dbo].[tblDealerAtivacao] CHECK CONSTRAINT [FK_tblDealerAtivacao_tblJob]
GO

ALTER TABLE [dbo].[tblDealerAtivacao] ADD  CONSTRAINT [DF_tblJobAtivacao_FLAGUSADO]  DEFAULT ((0)) FOR [FLAGUSADO]
GO

  Indexes ( Some names are dumb names because I was trying to create them for test purposes:

CREATE NONCLUSTERED INDEX [IX_ALL] ON [dbo].[tblDealerAtivacao]
(
    [CODDEALER] ASC,
    [CODJOB] ASC,
    [FLAGUSADO] ASC,
    [CHAVEALEAT] ASC,
    [idDealerAtivacao] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_coddealer] ON [dbo].[tblDealerAtivacao]
(
    [FLAGUSADO] ASC,
    [CODDEALER] ASC
)

  tbldealerusuario

CREATE TABLE [dbo].[tblDealerUsuario](
    [CODDEALER] [int] NOT NULL,
    [CODJOB] [smallint] NOT NULL,
    [CODUSUARIO] [int] NOT NULL,
 CONSTRAINT [PK_tblDealerUsuario] PRIMARY KEY CLUSTERED
(
    [CODDEALER] ASC,
    [CODJOB] ASC,
    [CODUSUARIO] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblDealerUsuario]  WITH NOCHECK ADD  CONSTRAINT [FK_tblDealerUsuario_tblJob] FOREIGN KEY([CODJOB])
REFERENCES [dbo].[tblJob] ([CodJob])
GO

ALTER TABLE [dbo].[tblDealerUsuario] CHECK CONSTRAINT [FK_tblDealerUsuario_tblJob]
GO

ALTER TABLE [dbo].[tblDealerUsuario]  WITH NOCHECK ADD  CONSTRAINT [FK_tblDealerUsuario_tblUsuario] FOREIGN KEY([CODUSUARIO])
REFERENCES [dbo].[tblUsuario] ([CODUSUARIO])
GO

ALTER TABLE [dbo].[tblDealerUsuario] CHECK CONSTRAINT [FK_tblDealerUsuario_tblUsuario]
GO

  Index:

CREATE NONCLUSTERED INDEX [ix_codusuario] ON [dbo].[tblDealerUsuario]
(
    [CODUSUARIO] ASC
)


texts: Query where condition take from the list of value

texts:
/****** Script for SelectTopNRows command from SSMS  ******/
SELECT DISTINCT
       (
         SELECT ForeignCurrencyBal
         FROM [InsightWarehouse].[dbo].[v_Account] AS subA
         WHERE BusinessDate=['2019-02-01','2019-01-31',etc..]
               AND
               subA.AccountNum = STMT.ACCOUNT_NUMBER
       ) AS Balance,
       ACCOUNT_NUMBER,
       CASE WHEN STMT.CRF_TYPE='CREDIT'
            THEN STMT.AMOUNT_LCY
            ELSE NULL
       END as 'CreditAmount',
       CASE WHEN STMT.CRF_TYPE='DEBIT'
            THEN STMT.AMOUNT_LCY
            ELSE  NULL
       END as 'DebitAmount',
       STMT.BOOKING_DATE,
       STMT.VALUE_DATE,
       CRF_TYPE
FROM [InsightLanding].[dbo].[v_STMT_ENTRY] AS STMT
     LEFT JOIN [InsightWarehouse].[dbo].[v_Account] AS A
     ON a.AccountNum = STMT.ACCOUNT_NUMBER
WHERE STMT.MIS_DATE='2019-02-01'
      AND
      STMT.ACCOUNT_NUMBER='14498478'

At this line
WHERE BusinessDate=['2019-02-01','2019-01-31',etc..] this list can get by sub-query.

I want to put the condition where the previous day, it mean the second item from this list. something like this

BusinessDate='2019-01-31'


texts:
helping out on a project where the data loading is done using C# & SQLBulkCopy to normal rowstore tables - does anyone know if there are any issues or gotchas with continuing with SQLBulkCopy but swapping the target table to be an InMemory table ? Is this even possible ?
Have tried searching but going around in circles


texts:
Having the foresight to begin collecting data from our production environment several years ago, I have a table with a following structure-

Part
Status
Startdate
Enddate

I can write a relational join, but how do I then go about including this in my project? What should I do in the DSV and in the Dimension relationships?


texts: sp_AllNightLog - running on an existing SQL server

texts:
I am trying to get sp_AllNightLog running on an existing server that had regular SQL log shipping (now removed) and Ola Hallengren's backup utility for nightly backups.

I have the procs and jobs configured, but they only take log backups, so it won't seed the secondary server.

I assume this is due to Ola's scripts having been run on the DBs before so they are not reverting to a full back up.  How can I force the full backup on first run?


texts: sqllogship.exe - how to get it to encrypt log file when backing up log on source server?

texts:
SQL Server 2014 and 2016, standard edition:

How do I get sqllogship.exe to use built in sql server encryption when backing up and restoring log? (as part of minute to minute log shipping)


texts: Is there a way to start a script on the publisher when synchronization starts?

texts:
I am using merge replication with pull subscriptions and Web Sync.

What is the easiest way to start a SQL script on the publication database when a subscriber starts the synchronization process?

There is this, sp_addscriptexec, but that specifies a script to run at the subscription database which isn't what I am after.


texts: exporting some table table from oracle DB to Mysql DB

texts: I need to export some of the tables from Oracle DB to Mysql DB..i dont have any previous experience of export and Import. Could some please tell me how to do that ?


texts:
I have a comments table, which can be simplified down to this:

comments
=======
id
user_id
text
parent_id

where parent_id is nullable, but might be a key for its parent comment.

Now, how can I select all descendants of a specific comment?
The comments might be several levels down...


texts: Filtering a result set on a very large table (using INCLUDE index in SQL Server)

texts:
Consider a very large table having hundreds of millions of rows defined as

-- Here ObjectIdName and ObjectTypeName together are unique.
-- Due to their size they aren't indexed, but rather they are
-- hashed to ObjectIdName -> ObjectId and ObjectTypeName -> ObjectType
-- which are used in the index instead.
CREATE TABLE VeryLarge
(
    ObjectId INT NOT NULL,
    ObjectIdName NVARCHAR(512) NOT NULL,
    ObjectType INT NOT NULL,
    ObjectTypeName NVARCHAR(512) NOT NULL,
    PayLoad VARBINARY(MAX) NULL,
    IsDeleted BIT NOT NULL
);

-- This was originally in the question. By mistake includes the Payload column.
CREATE NONCLUSTERED INDEX IX_VeryLarge1 ON VeryLarge(ObjectId, ObjectType) INCLUDE(IsDeleted, PayLoad, ObjectIdName, ObjectTypeName);

-- This was the index I intended to have, without the Payload column.
CREATE NONCLUSTERED INDEX IX_VeryLarge2 ON VeryLarge(ObjectId, ObjectType) INCLUDE(IsDeleted, ObjectIdName, ObjectTypeName);

If one assumes the ObjectId and ObjectType parameters locate a single row in 99.99% of the cases, it looks to me there needs to be an interplay between the query and the indexing to be so effective the query will always return predictably in sensible time (considering hardware and the actual amount of rows). However, I'm not sure how should the query be constructed. Basically what I would like to do is query first with ObjectId and ObjectType and filter this small result set with ObjectIdName and ObjectTypeName (if there are more than one row).

Considering this, with the defined indexing, is the following query sensible on SQL Server?

SELECT
    Payload
FROM
    VeryLarge
WHERE
    ObjectId = @objectId
    AND ObjectType = @objectType
    AND IsDeleted = 0
    AND ObjectIdName = @objectIdName
    AND ObjectTypeName = @objectTypeName;

-- Or alternatively, considering the question:
SELECT
    PayLoad
FROM
(
    SELECT
        PayLoad,
        ObjectIdName,
        ObjectTypeName
    FROM
        VeryLarge
    WHERE
        ObjectId = @objectId
        AND ObjectType = @objectType
        AND IsDeleted = 0
) AS x
WHERE x.ObjectIdName = @objectIdName AND x.ObjectTypeName = @objectTypeName;

I don't know how to construct such a query that would prune the results of the first query that will is guaranteed to hit an index and return only one or a few rows. It would look that using INCLUDE has the same effect, forcing and index seek but I don't know if there will be costs associated with this. Also, I know MySQL doesn't have this concept of included index, but it works somehow similarily and I assume filtering the result set of at most a few rows would work on any database that supports indexing regardless of other structures.

This is related to my other question at Hashed and heap indexed object storage table insert and query performance, which has the table and query defined.

<edit: Additional question: Having those repeating ObjectType columns in the same table looks like wasteful. Might be subject for another post, but I believe they ought to be separated to a different table. Then the question is that should the INNER JOIN be avoid with a query that does the join only if there are more rows than one after the first filtering. And how to do that.


texts: Optimize MySQL average query for millions of rows

texts:
I have 4 tables (I've specified main columns for each):

monitors (id)
monitor_node (monitor_id, node_id, average_response_time)
monitor_checks (id, monitor_id, node_id, response_time)
nodes (id)

Their relations:

1 monitor - N checks

N monitor - N nodes

1 check - 1 monitor & 1 node

So, I'm logging a lot of checks for each monitor. Monitor_checks will have millions of rows (approximately up to 500 millions).

When I insert another bunch of checks (~1k checks), I need to calculate average response time per  node (table monitor_node, column average_response_time).

I'm pretty sure I'm doing it the wrong way and I need a faster solution. What I do now: after inserting 1k rows in monitor_checks I calculate average response time for each monitor (grouping by monitor_id). Then, based on this info I make an array and use insert ... on duplicate key update for monitor_node table, in order to do bulk update. Apart from average response time I calculate some other attributes, which goes along with response time in this bulk update.

Making the array with info and executing insert ... on duplicate key update is fast enough.

The slow query is:

select monitor_id, avg(response_time) as avg_response_time
from `monitor_checks`
where `node_id` = 2
group by `monitor_id`

which takes like ~10-20 seconds for ~4m rows I guess.

I also realised that it's not necessary to get average time based on all checks, I can as well use last 50-100 rows. But I couldn't figure out how to do this. I got a fancy solution for grouping checks for each monitor and limit them for some number of rows: How to get latest 2 records of each group but it took way too long as well.

So the question is: how to quickly recalculate average response times in monitor_node when inserting ~1k rows in monitor_checks table?

DB Fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=bd95afc030361bf1d87f8bc5c3935c2f

Final desired result:

monitor_id  node_id average_response_time
1   1   0.30
1   2   0.25
2   1   0.55
2   2   0.65


texts: Need a SQL Query for finding OS Version, RAM, etc

texts:
DBAs, I need some help. Is it possible to find below mentioned parameters through SQL query or else GUI (cluadmin, msinfo32, systeminfo, etc.) is the only option. I'm aware of finding below mentioned parameters through GUI, but based on the fact that working with query will save lot of time, I'm looking for a SQL query to get below details in SSMS.

OS Version - (eg., Windows 2012 Server Standard SP1)

RAM (GB) - (eg., 128GB)

Machine Type - (eg., Physical or Virtual)

Installed On - (When SQL instance is installed or built eg., 08 August, 2016)

If SQL instance is in cluster, I'm looking to get below details as well

SQL Virtual Name -

SQL Virtual IP -

Cluster Name -

Cluster IP -

Node1 -

Node1 IP -

Node2 -

Node2 IP -


texts: Error when using OPENROWSET from a View as a nonsysadmin

texts:
[SQL Server 2012] I am running into an issue regarding the usage of a non-sysadmin account calling a view which contains a OPENROWSET call that pulls from a stored procedure. I was wondering whether anyone has a possible fix to what I am currently trying or a suggestion that can get me out of the current workaround of using OPENROWSET in a view (possibly a linked server setup?).
I currently make a call to a procedure that looks like this:
SELECT *
FROM
OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=NO;UID=accountName;PWD=accountPassword;',
           'SET FMTONLY OFF;
            SET NOCOUNT ON;
            EXEC [My Stored Procedure] @params = ''field1,field2''
            WITH RESULT SETS
            (
                (
                    field1 NVARCHAR(1000),
                    field2 NVARCHAR(1000)
                )
            )
        ')

and the results are returned into a view. This all works just fine and executes when running this as a sysadmin. The problem is we now wish to give a non-sysadmin user access to this view, however, after granting SELECT permissions to the user for that view and trying to access the view as that user, this error comes up:
'Access to the remote server is denied because no login-mapping exists.'

I may have narrowed this down to the fact that the view itself calls OPENROWSET. OPENROWSET is used because I call on a stored procedure that pulls data from an API and produces a rowset. The stored procedure also calls extended stored procedures so this is not something easily converted to a function.
My only current way of getting this to work, in the newly created account, is by granting it sysadmin permissions. This is obviously not the desired route to take for security reasons but it helps confirm that this should work.
There are a vast number of questions regarding SQL Server + OPENROWSET but based on a few good hours of research I cannot seem to find a solution specific to this case. Any help would be very appreciated.
I arrived at my current setup through the referencing of answers like these:

https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table
https://stackoverflow.com/questions/2896445/why-can-we-not-execute-a-stored-procedure-inside-a-function-in-sql-server
https://stackoverflow.com/questions/916784/how-to-call-stored-procedure-in-a-view?noredirect=1&lq=1

and many more.
End note, I have already considered having the stored procedure(s) update/create local tables, possibly running on a nightly schedule to make sure the data is up to date, and creating views based off of the values in those updated tables. However, it reduces much of the duplicate code and re-configuring of stored procedures if I create the view to call the stored procedure on-the-fly with the parameters I wish to send to the procedure.


texts: MS SQL Server - Increase allocated memory

texts:
My problem is that SQL Server takes a long time to ramp up it's memory usage for instances with TB worth of RAM, all the while we get intermittent waits of MEMORY_ALLOCATION_EXT that slow down our processing until SQL Server reaches its max memory.
We have Failover Clustered Instances (FCI) of SQL Server 2019 Enterprise Edition with terabytes worth of memory on the nodes. In usual use cases we only allow 1 instance of SQL Server per node, and so we set max server memory close to ~85% of the memory on the node, but we also set min server memory relatively low just in case SQL Server fails over to another node and needs to be brought online with a reduced memory footprint to limp along.

I am quite aware that if I set min memory up higher that SQL Server will consume the memory all at once. Turns out SQL Server does not allocate the min memory at startup.
I am aware that SQL Server will dynamically consume more memory from the OS as it needs it and that eventually it will reach the max server memory.
I am aware that running a big query or DBCC checkdb that pulls in a lot of data will force SQL Server to consume more memory from the OS.

Are there any other ways to force SQL Server to increase its memory usage quickly?


texts:
I have a table named backupfile, this is a copy of sys.backupfile that I am inserting into from all of the servers on in my network.

I was trying to see how big the table has gotten, and when I go to Properties - Storage it takes a while, and then I get this:

It has done this every time I have tried the last few days.

Could my data be corrupt?  We run dbcc checkdb weekly.

What could be causing this?


texts: Count missing values across columns and join back to original table

texts:
Here is the table:

I want to count the missing values across each row for t1, t2, t3,..., and create another column in the same table with the results as shown in the picture.

I can easily do this in something like python pandas. But on SQL (specifically BigQuery and/or postgreSQL, MySQL), I can't seem to figure out the syntax. Here is my attempt:

select
  array_agg(id),
  array_agg(date),
  count(t)
from
(
  select id, date, t1 as t from testdata
  union all
  select id, date, t2 as t from testdata
  union all
  select id, date, t3 as t from testdata
) as data
group by id, date, t

Any ideas where I am going wrong? I think union all unpivots the wide to long table, but how do I count the missing values within a specific range and join all the results back to the original table?


texts: How to limit the no of rows to copy in MySQL Workbench Data Migration

texts:
I am trying to migrate a huge MS SQL database to a MySQL database using MySQL Workbench.

The source database is too big and i am only interested in 1000 records per table for migration.

Unfortunately i couldn't find a option in MySQL Workbench to limit the no. of Rows to copy from source DB.


texts:
I have asked a question here:
https://stackoverflow.com/questions/43807566/how-to-divide-two-values-from-the-same-column-but-at-different-rows

about dividing values from the same table, at the same column but on different rows. Now I have the problem where I have more numerators and denominators (with different uns). Is still the self join a good way to solve this problem with Postgres or there are better solutions?

Example:

| postcode | value | uns |
|----------|-------|-----|
|       AA |    40 |  53 |
|       BB |    20 |  53 |
|       AA |    10 |  54 |
|       AA |    20 |  55 |
|       AA |    10 |  56 |
|       AA |    30 |  57 |
|       AA |    50 |  58 |
|       BB |    10 |  54 |
|       BB |    10 |  55 |
|       BB |    70 |  56 |
|       BB |    80 |  57 |
|       BB |    10 |  58 |

Result should be:

| postcode | formula    |
|----------|------------|
|       AA | 18.888...  |
|       BB | 14.375     |

Where the value are grouped by postcode and the formula is (value with uns):

(V53 * V56 + V54 * V57 + V55 * V58) / (V56 + V57 + V58)

Paying attention to avoid eventual division by zero.
Formula can be even more complex but that is a good example.


texts: How can I find the longest record (in characters) for each column in a table in PostgreSQL

texts:
I couldn't find the answer to this on here so sorry if repeated. I'm looking to query a table to find the longest record in characters for each column in a specific table.

I have a table which is built of fields which are type: varchar and length:254

In certain cases, I know for a fact that the longest record is a single character so the problem is when exporting to shapefiles the .dbf file is over 1gb when not needed.

If someone can also help with a script to reduce the lengths of each column as well that would be great!


texts: Determining which database instance makes biggest IO

texts:
Assuming that I have a dedicated server on which I am running multiple instances of mysql and postresql servers. How without iotop determine which instance in particular time (proc/pid/io shows data collected in some peroid of time) makes the biggest IO (so it increases IOWAIT)?

When lots of ppl do something on DB then I clearly see which instance is making the load because of high cpu usage, but I had a situation when the cpu usage was just normal, but very high iowait made a huge load on server and i had problem finding process that was making some outstanding IO


texts: How to validate source and target tables are sync or not in AWS DMS task ongoing replication?

texts:
I would like to know how to validate source and target database tables are in sync at some point of time with AWS DMS task type as "Full load, ongoing replication"?
Source database Tables are:

catalog_table
Job_table (job_id, count)

One ETL job (outside of DMS task) inserts data into "catalog_table" with 1000 records (example) every time, then at the end it inserts record into "job_table" with job_id and count as "number of records" inserted into catalog_table.
And my DMS task running and continuously copying data into target DB (which is working fine).
Here, I want to validate source and target databases "catalog_table" data for each job_id which inserted into job_table. What kind of events or mechanism I can use in this scenario? Appreciate your time.
Note: Source and Target are RDS instances.


texts: Calculating the number of times a group occures


texts: ORA-02063 Encoding problem with Oracle querying SQL Server through ODBC link

texts:
We have a problem querying some data from the SQL Server from the Oracle database with the ORA-LNX connecting to SQL-SRV using ODBC and Heterogeneous Service (Oracle HS).

When we send the query through the ORA-WND Oracle, the results are exactly as expected; everything works fine.

The problem occurs when we send the query through our production Oracle server, which is ORA-LNX. oth Oracle are configured with the same encoding (NLS) parameters, both have the same ODBC configuration as well.

Here is the error:
ORA-02063 / ORA-28500

This happens when it encounters a french accent: É which gets translated into  Ã‰ on the ORA-LNX while it stays as an É when queried through ORA-WND...

Configuration:

SQL-SRV : Microsoft SQL Server
ORA-LNX : Oracle 12.1 : windows-1252 / AL16UTF16 on Linux
ORA-WND : Oracle 12.1 : windows-1252 / AL16UTF16 on Windows

Different tools used to reproduce the problem:

PL/SQL Developer (AllAroundAutomations)
.NET application
VB6 application


texts: Advantages of "middle" tables in one-to-many relationships

texts:
Entities:

Lists
Leads

What I am trying to achieve is a scenerio where in the relationship between lists and leads the following constraint is applied: A list can have many leads, but a lead can only belong to one list.

Standard way of doing this is a simple non-identifying one-to-many relationship.

Another way I'm doing this is to create a hybrid one-to-one and a one-to-many relationship.

In both cases a list can contain many leads, but a lead can only exist in one list. Does anyone see any advantages or disadvantages to the second method? Any differences between the two methods? Any alternatives?


texts: Trying to read a log file encrypted by TDE

texts:
I have a full chain of full backups and log backups.  I am looking to investigate exactly what transaction was done by a certain user in a given period of time.

The database (thus all backups) are encrypted by TDE.  Tools such as ApexSQL Log does not work on TDE-encrypted data source.  Is there any way I can analyze the log file to find out the information I want?

I can run fn_dump_dblog which gives me some information, however I cannot retrieve the exact information on what was changed, such as what column was changed to what value.


texts: best approach to keys, if data from separate DBs to be presented in same UI

texts:
Suppose we are creating databases containing information about monkeys, and a user interface to present monkey information to users.

We have two separate databases. The schemas are identical (and are at least intended to remain so) but they are on different machines and have information on different monkeys. This is inconvenient but unavoidable.

The user interface we're building queries both databases and presents all the monkeys in the same filterable HTML table to the user.

What's the best approach to generating IDs for monkeys? Is it acceptable to prefix the ID according to which database the monkeys are in?


texts: How to model medium.com paragraph comment

texts:
If you have used medium.com, I am interested to know more about how to model readers' paragraph comments in database.

Specifically, how connections between these comments and paragraphs are modeled?

My initial thought is (1) using the order of paragraphs in article, e.g. paragraph 1, 2... It won't work if the author deletes/adds/moves paragraphs, which messes up the order.

Then I think about (2) assigning id to paragraphs, e.g. SHA hash the paragraph as its unique id. But it won't work if the author edits the paragraph.

Could anyone help me?

Btw, could the solution extends to sentences/phrases comments as well?


texts:
I have a simple query like this:
select d.file_name, d.tablespace_name, s.segment_type, s.segment_name,
round(s.bytes/1024/1024,2) MB
from dba_data_files d
inner join user_segments s on d.tablespace_name = s.tablespace_name;

It works fine. But what i really want is to create a view with this query.
So i put:
create view espaco as
select d.file_name, d.tablespace_name, s.segment_type, s.segment_name,
round(s.bytes/1024/1024,2) MB
from dba_data_files d
inner join user_segments s on d.tablespace_name = s.tablespace_name;

But it doesnt work, it says:

ERROR at line 4:
ORA-01031: insufficient privileges

So, how come I can do this query normally, but if I create view it doesnt work?
I can create other views, but when I create a view to search from dba_data_files it doesn`t work.
The user has create view privileges, dba privileges, any privileges, and still doesnt work.
Does anyone have any idea why?


texts:
We use postgres to manage our data in windows. Most of our installations run 9.x branch of postgres, although all versions of the database software is used (however pretty much all installations run under windows).
We would like to add pg_repack extensions to the installations. I tried to compile the package using msvc 2017 community edition. I adjusted the include and lib paths and eventually got to these linking errors:
Severity    Code    Description Project File    Line    Suppression State
Error   LNK2001 unresolved external symbol set_pglocale_pgservice   bin.2010    \pg_repack\msvc\pgut.obj    1
Error   LNK2001 unresolved external symbol pgwin32_select   bin.2010     \pg_repack\msvc\pg_repack.obj  1
Error   LNK2001 unresolved external symbol __imp__dclass    bin.2010     \pg_repack\msvc\libpgport.lib(snprintf.obj)    1

Questions:
(1) what is missing to get this linked?
(2) are there pre-compiled pg_repack binaries for different versions of postgres in windows?
UPDATE:
Getting the code compiled in msvc 2017 community edition, one needs these adjustments:
(-1) take visual studio 2010 solution/project, not the pg_repack.sln
(0) install visual studio 2017 community edition. 2019 is harder, as they do not support 2010 projects anymore, one needs to upgrade those, and upgrading is buggy (creates additional work that needs fixes)
(1) set x64 target. 32 bit is a mess
(2) set include dirs:
C:\Program Files\PostgreSQL\9.6\include\internal;C:\Program Files\PostgreSQL\9.6\include\server\port\win32;C:\Program Files\PostgreSQL\9.6\include\;C:\Program Files\PostgreSQL\9.6\include\server;C:\Program Files\PostgreSQL\9.6\include\server\port\server;C:\Program Files\PostgreSQL\9.6\include\server\port\win32_msvc;

(3) set library dirs:
C:\Program Files\PostgreSQL\9.6\lib;

(4) remove _USE_32BIT_TIME_T from C++/preprocessor
(5) change libintl_8.lib to libintl.lib in linking libraries
(6) add postgres.lib to adress undefined symbol set_pg_locale_pgservice. In visual studio console this gives exported symbols:
dumpbin -exports postgres.lib

(7) add postgres.lib to the linking libraries. Move this in front, so the _imp_sprintf undefined reference is also addressed
(8) imp_job_func underfined because of https://stackoverflow.com/questions/30412951/unresolved-external-symbol-imp-fprintf-and-imp-iob-func-sdl2 recompile with visual studio 2010 platform toolset
(9) remove initialization errors
worker_conns workers   = {
    .num_workers     = 0,
    .conns           = NULL
};

(10) remove file from lib project:
lib\pgut\pgut-be.c

(11) compile the project and the library
(12) copy the dll to and exec to C:\Program Files\PostgreSQL\9.6\lib
copy /b/v/y "F:\git\pg_repack\msvc\bin\x64\8.3\bin\pg_repack.exe" "C:\Program Files\PostgreSQL\9.6\lib"

copy /b/v/y "F:\git\pg_repack\msvc\bin\x64\8.3\lib\pg_repack.dll" "C:\Program Files\PostgreSQL\9.6\lib"
copy /b/v/y "F:\git\pg_repack\msvc\bin\x64\8.3\lib\pg_repack.lib" "C:\Program Files\PostgreSQL\9.6\lib"

(11) load dll
load 'C:\Program Files\PostgreSQL\9.6\lib\pg_repack.dll'

(12) open ./lib/pg_repack.sql.in in postgres in database of choice
(13) replace all MODULE_PATHNAME with 'C:\Program Files\PostgreSQL\9.6\lib\pg_repack.dll'
CREATE FUNCTION repack.version() RETURNS text AS
'C:\Program Files\PostgreSQL\9.6\bin\pg_repack', 'repack_version'
LANGUAGE C IMMUTABLE STRICT;

(14) Add pars to debugging
-h localhost -U postgres -p 5432 -d play --table public.t

(15) get an debugging error
Exception thrown at 0x000000000059E908 in pg_repack.exe: 0xC0000005: Access violation executing location 0x000000000059E908.

(16) add debuging infos

(17) die somewhere


texts: Connecting a standalone Microsoft R Server to a SQL Server with R Services and a SQL Server without R Services

texts:
Let's assume I have 3 machines with me.
My first machine has SQL Server 2016 with R Services installed on it.
My second machine has SQL Server 2016 without R services enabled on it.
My third machine has a standalone Microsoft R Server installed on it.

I understand that since my first machine  has SQL Server with R Services, I can store R script as stored procedures on SQL Server and call it later for analysis.
My question lies with how my standalone Microsoft R Server connects to either SQL Servers (with and without R Services enabled).
From what I understand, it uses ODBC to connect to SQL Server and uses the RevoscaleR functions to query or analyze the SQL data - in both cases.

If that's the case,

What benefit or limitation does my machine 3 (standalone MRS) have while connecting to SQL Server with R Services vs. SQL Server without R Services?
In either cases, can I invoke any stored procedures from my R IDE remotely?
Can I push the compute context to SQL server in both cases?
Is there anything special that SQL Server with R Services offers for a standalone Microsoft R Server when compared to a SQL Server without R Services.
Is there any difference in the database engine's ability to perform other tasks either way?

Imagine I'm reluctant to enable R Services on my SQL Server because it may take up the memory and slow down the performance of the database engine itself. All I want to do is get a standalone Microsoft R Server and connect it to SQL Server without R Services and get all the scalability and performance anyway.
What is it that I'm missing by doing this?