texts: Getting the first 3 children(items) for each owner in a list of primary keys

texts:
I have two tables Item an Owner, an a Foreign Key from Item to Owner, owner_id. I'm using PostgreSQL 10.

For each owner I want to get the first 3 items if exist.

select  "items_item"."name"
FROM "items_item"
WHERE ("items_item"."owner_id" IN (64, 65, 130, 99, 43, 140, 108, 143, 115, 154))
ORDER BY LIMIT 3

Clearly is not ok, because is selecting just 3 items from all owners, not a maximum 3 items per owner .


texts: String or binary data would be truncated, with an nvarchar(max)

texts:
I'm facing a well known error, but this time I cannot figure out the problem.

I'm working with SQL 2014 and I need to replace some values inside an NVARCHAR(MAX) column. To do this I retrieve the column value into a variable @model nvarchar(max), then replace the required text elements, but when I try to update the source column the error is thrown.

As a resumed code:

CREATE TABLE SourceTable(
  Id INT
  TextColumn NVARCHAR(MAX)
  ....
)
Declare @model NVARCHAR(MAX)

SELECT @model = TextColumn
FROM SourceTable
WHERE Id = 1

--Do stuff, modify @model variable

SELECT DATALENTH(@model) ModelLenth, DATALENTH(TextColumn) as TextColumnLenth
FROM SourceTable
WHERE Id = 1

Update SourceTable
SET TextColumn = @model
WHERE Id = 1

The length of the data retrieved and later inserted are equal.
The changes themselves are minimal, just numbers.

Example:

some text 8... more text

To

some text 7... more text

Does anyone have an idea what the issue is?


texts: Simple Mode - Is using checkpoints to truncate the transaction log a bad practice?

texts:
I have a database with simple recovery mode. For that database I have a job running every morning. This job imports data and executes plenty stored procedures that inserts data, updates records, delete records, makes calculations... The job takes approximately 30 minutes and makes the transaction log be 10GB big. To avoid growth of the log file during the job I set the log size to 11GB. With that amount it is never autogrowing. Nevertheless it is blocking 11GB. I think it is useless knowing that I will never need to make a recovery (every job empties all the tables and populate them from scratch every morning).

I just read that putting checkpoints truncates the log. So my idea is to put checkpoints at several location in my stored procedures in order to be able to minimize the size of the log (from 11GB to lot less: 3GB for instance).

Question: is it dangerous to do that take into consideration once again that I will never have to make recoveries?


texts: postgres: how can I allow index creation but no table mutations or table drops by the same user?

texts:
I am setting up a postgres server with what is going to be a (mostly) read-only workload.

I would like to allow myself and colleagues to create/drop indices as we see fit, without allowing ourselves to drop tables or mutate the tables.

I spent time reading the grant/revoke documentation as well as role documentation pages, but it seems the table owner roles get both powers.

One thing I found was that, supposedly, grant create on tablespace allows index creation, but it didn't work ( note, I did not create any other tablespaces).

to be specific, I tried:

mydb=# grant create on tablespace pg_default to bob;
GRANT

... then switched over to bob:

mydb=> create index on foo(a);
ERROR:  must be owner of relation foo

I also tried making bob an owner, but then bob gets the right to drop tables, and there is seemingly no way to revoke that privilege.  So I'm stuck with both or none.

Any ideas?


texts: Postgres: how to tell if insert took place

texts:
I have a (working) stored procedure that incorporates logic disallowing dups. However, I cant determine a way to have the stored procedure tell me if a row was inserted or not. I can think of a terrible hack - the table has a sequence, and compare the sequence current value before and after the insert.  At least I think its a hideous hack...

CREATE OR REPLACE FUNCTION mysp() RETURNS integer AS
$BODY$
DECLARE
BEGIN
    -- only 1 row gets in
    INSERT INTO mytable (locationname) VALUES ('some text')
    WHERE NOT EXISTS (SELECT 1 FROM mytable
    WHERE locationname='some text');
    -- cant figure out how to do something like RETURN ROWSINSERTED();
END
$BODY$
LANGUAGE 'plpgsql' ;


texts:
I have an orders table. Imagine it having only 2 columns, id and created_at.

Imagine I have a data in my orders table that looks something like this:

id | created_at
01 | 2018-09-29
02 | 2018-09-29
03 | 2018-09-30

I want to select all orders within that day.

I can select the records you by using the following query.

SELECT created_at AS date, COUNT(id) AS result
FROM orders
GROUP BY created_at
ORDER BY date

This will return:

date | result
2018-09-29 | 2
2018-09-30 | 1

I want to expand the query above to generate the following result instead: (daterange)

date | result
2018-09-15 | 0
2018-09-16 | 0
...
2018-09-29 | 2
2018-09-30 | 1

Is this possible? To select a data that is non-existent to generate a date range report?


texts: How to display the query result separately for each item in an IN clause?

texts:
Maybe it's a novice question, but I am trying to get the sum of balances for each account present in the table.

I am trying the below

Select
    SUM(BALANCE)
from
    dbo.account_balances
where
    ACCT IN (
            1000, 1001, 1002...)

This is giving me the total balance and I can see why. I need help in figuring out how to see the balances for each account individually in query result.

I do not have the rights to modify the table. There are no NULLs.


texts: MongoDB collStats.size compared to collection.totalSize

texts:
I have a collection which has the following dimensions:

rs0:PRIMARY> db.myCollection.totalSize()
2188206080

rs0:PRIMARY> db.myCollection.stats()
{
    "size" : 5899660704,
    "avgObjSize" : 640710,
    "storageSize" : 2187829248,

According to the mongo docs:

  db.collection.totalSize()
  The total size in bytes of the data in the collection plus the size of every indexes on the collection

which to me, means the collection is 2.18Gb in size (including the indexes).

The docs also state:

  collStats.size()
  The total size in memory of all records in a collection

So what I dont understand is why size = 5899660704 (5.9Gb) i.e. significantly bigger than totalSize?


texts:
i need this query to run against 20 instances from main server that have linked server to those 20 instances.i want to change the [@server]  to the right instance via cursor and make the SELECT statement to work.

how can i write the query accordingly?

THX

declare @server varchar(50)

set @server = ‘servername\instnacename’

SELECT N'’ + ISNULL( + ‘
’ + (SELECT CONVERT(NVARCHAR(MAX),column_name) from [@server].db_name.dbo.table_name),'
Not Avail’) + N'
	’


texts: How to create multiple azure sql database in an elastic pool using powershell script?

texts: I want to create a sql server and deploy the elastic pool on that server. Then create multiple azure sql database in that elastic pool using powershell script.


texts: MySQL Replication Stops, But Still Says it is Running

texts:
I've got a strange replication problem. For reference I am using MySQL 5.5 with Statement Based Replication. We have a Master with Slaves on site A / network A, and slaves on site B / network B.

Everything on network A is fine. The problem occurs with the databases on site B / network B that are connecting to the master at Site A.

On a few occasions now I have seen replication stop on the databases at site B. If I look at the SLAVE_IO_RUNNING and SLAVE_SQL_RUNNING in SHOW SLAVE STATUS they both say yes. To all extents and purposes everything looks fine to me. However, my monitoring is reporting that the heartbeat event we run is falling behind. It's as if the slave was connected to the master, but not receiving any data.

The various pos values (read_master_log_pos, relay_log_pos, exec_master_log_pos) are all static and not moving. I also checked the relay log on one, and the incoming data just stops at that time.

If I look at both the Master and Slave Databases there are no long running queries that would cause this. Everything appears to run as expected, and as mentioned the slaves at site A are all fine and keep up to date.

The databases in question are all running different queries so it's not a specific query upsetting things.

There is nothing in the error logs for MySQL.

We have simulated a short network failure (although from our monitoring we can't see any network outages around these times) and the databases work as expected. As soon as the network is reconnected they resume replication.

This is fixed by running stop slave; start slave; at which point everything carries on as if nothing had happened.

Has anyone else had a similar problem? or could shed some light on what may be happening. My gut feeleing is there is a very brief network outage, too short for the monitoring to catch, but why this would upset MySQL I don't know.


texts: SQL Server high availability fn_hadr_backup_is_preferred_replica returns 0 zero on secondary

texts:
I'm running the Ola Hallengren Maintenance Solution and noticed the transaction logs weren't being backed up on the secondary, hence not being truncated, despite the AG backup preference being set to Prefer Secondary.  If I run:

SELECT availability_groups.name,
   dm_hadr_availability_replica_states.role_desc,
   UPPER(availability_groups.automated_backup_preference_desc) as 'preference'
FROM sys.databases databases
  INNER JOIN sys.availability_databases_cluster availability_databases_cluster ON databases.group_database_id = availability_databases_cluster.group_database_id
  INNER JOIN sys.availability_groups availability_groups ON availability_databases_cluster.group_id = availability_groups.group_id
  INNER JOIN sys.dm_hadr_availability_replica_states dm_hadr_availability_replica_states ON availability_groups.group_id = dm_hadr_availability_replica_states.group_id AND databases.replica_id = dm_hadr_availability_replica_states.replica_id
WHERE databases.name = 'EXT_CONFIG_DB'

on the secondary, I get:

name            role_desc   preference
SP-EXT-CONFIG   SECONDARY   SECONDARY

This is as expected.  However if I then run:

SELECT sys.fn_hadr_backup_is_preferred_replica('EXT_Config_DB') as 'is_preferred_replica'

I get:

is_preferred_replica
0

This is wrong and should be set to 1. As the fn_hadr_backup_is_preferred_replica bit is set to 0 the Ola job doesn't backup or hence truncate the log.  Searches seem to suggest lowercase instance names may be the issue, but all my instance names are upper case.

Can anyone suggest a resolution to fn_hadr_backup_is_preferred_replica returning 0 instead of 1?


texts: Want to get executed query users log in SQL-server

texts:
Yesterday one of my team members has truncated a few tables, but who have truncated those tables is still a mystery.

Can I have user based query log with the timing of execution? Or something where I can enable that functionality in future??

Currently, I am using SQL-Server 2017 on Linux.

Thank you in advance.


texts: Query to check if all replication articles are created at Subscriber SQL Server 2012

texts:
I am creating an automated build and test tool for our databases. In general there are two stages:
- install databases in version A, install transactional replication
- drop replication, update databases to version B, install replication

The problem is that replication is dropped before previous stage finish to create articles (tables) at Subscriber. In this case Subscriber is missing some tables and update script fails.

Can you give me a query or tables to check if all articles are created?

Subscriber is on different instance than Publisher.
Articles are created with sp_addarticle and after that we execute this: sp_startpublication_snapshot

UPDATE:
I managed to create a workaround but it is not 100% correct.
This query is supposed to check if tables created from replication on Subscriber are the same as articles on Publisher.
I found out that our actual situation is as follow:
- tables on Subscriber exists, because they were created from initial scripts
- replication is installed
- below query is executed
- in the meantime articles on Publisher are created
- my query compares them with original tables from initial scripts and finishes with success
- Replication is started and Subscriber tables are being dropped and created
- update to version B is started
- sql agent is stopped, replication is dropped
and we have missing tables on Subscriber.

The query is executed on Subsciber with linked connection to Publisher.

WHILE (
  SELECT COUNT(1)
  FROM (
   SELECT a.dest_table
   FROM [10.149.000.000].PubDB.dbo.sysarticles a (NOLOCK),
    [10.149.000.000].PubDB.dbo.syspublications p (NOLOCK)
   WHERE a.pubid = p.pubid
    AND p.NAME = 'my publication name'

   EXCEPT

   SELECT t.NAME
   FROM sys.tables t (NOLOCK)
   WHERE t.is_ms_shipped = 0
   ) AS Z
  ) > 0
BEGIN
 PRINT 'Waiting for replication to finish.'

 WAITFOR DELAY '00:00:02'
END


texts: How to Disable and Drop all Temporal Tables from a database

texts:
I'm working on a task where I need to backup and restore a database in another instance.

But, when I restore that database, I need to Drop all the History Tables associated with Temporal Tables.

Is there a quick way to do this? Any help is appreciated.


texts: How to find out which object is taking space?

texts:
I have database of size 537gb. How can I find out which object is taking space.
I executed sp_spaceused, which showed me unallocated space is 502GB and 88GB used space. How can I release unallocated space to get some free space. My HDD is getting full due to this. Please advise.

Edit: I have checked via below script as well. But total UsedSpaceMB of each table is 47GB. Still I am unable to figure out how DB size is 537 GB.

SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB, SUM(a.used_pages) * 8 AS UsedSpaceKB, CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB, CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM sys.tables t
INNER JOIN sys.indexes i
    ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p
    ON i.object_id = p.OBJECT_ID
        AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a
    ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s
    ON t.schema_id = s.schema_id
WHERE t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY t.Name, s.Name, p.Rows
ORDER BY 7 DESC


texts: How do I determine if a database restore file should be restored to the database based on backupset?

texts:
I have a production database that is fully backed up nightly and incrementally backed up hourly.  On our backup machine, we have the differential backups and full backups restored ion a regular basis in a job. However, I need to only restore a backup file if the backup is valid (meaning this backup is newer than the current database backup version).  Is there any way to accomplish this?

This is our current solution until we can get mirroring working.

All help is greatly appreciated!


texts: Backup/restore check data is the same SQL Server

texts:
I'm a trainee SQL DBA. I'm using SSMS 2008r2.
I've backed up a database and restored this as a new database on a new instance.
I want to check to see if the data is the same on both databases. Is there a way I can do this without running select commands on both databases and comparing?

Thanks.
Ryan.


texts: Different execution times running a query

texts:
I have a view on a table that has 35 million rows. When I'm selecting from the view for the first time, the execution time is about 35 seconds. When I run the query again repeatedly, it is much faster, down to about 3 seconds each time.

Is there a generic explanation of why I get different execution times in scenarios like this?


texts: Performance tuning a cascading delete with many foreign keys

texts:
I have a delete query that is taking a long time. Looking at the execution plan, I see that most of the estimated cost in the delete query is in a section of the data model that had a lot of data (say, 400k rows) which seemed fine, but I don't understand one thing.

Stripped down view of data model:

table ParentObject
      int parentObjectId (PK)

table Child
      int childId (PK)
      int parentId (FK)
      <stuff>

table GrandChild
      int grandChildId (PK)
      int childId (FK)
      <more stuff>

Where a parent object might have 200,000 Children, and a Child has 2 or so GrandChildren. I am interested in tuning the performance of:

DELETE FROM ParentObject WHERE parentObjectId = %d;

On Grandchild, there is an extra nonclustered index on (childId, + two other columns) as well as the primary key index. On child there is an extra nonclustered unique index (parentId, + two other columns).

The thing that I saw in the query plan is that while deleting the Grandchild objects, there were two expensive sorting operations mixed in with the deletions, and I don't understand why they are there.

What should I be looking at to help this delete operation go faster? Does it need to sort? Would it help if I denormalized the ids and added a parent Id to the grandchild table? Did I build my index stupidly?

The full execution plan is here.


texts: How to recover .ibd and .frm files in MySQL

texts: I am developing a Java application with MySQL 5.6.13. A few days earlier I purchased a new laptop. I forgot to create a dump of application from previous laptop and instead I copied folder of my database(.ibd and .frm files) from datadir, when I installed MySQL 5.6.13 in new machine, and I had pasted my database(.ibd and .frm files) into datadir. The server is running fine. But in MYYOG IDE, when I open any of tables I am getting this error:


texts: Clustered index update, how changing the index only at different column value?

texts:
I've table,
example as

Id int primary key(clustered index)
Name varchar(255)not null (non-clustered index)
..
..
Other columns

I execute stored procedur as

update table1 set table1.Name=isnull(@Name,table1.Name) ,( updating other columns) where Id=@Id;

many times Name equals @Name;
In execution plan i saw

if i change stored procedure as

update table1 set ( updating other columns) where Id=@Id;

i saw Cost:15%

Question:

I need to change this procuder for two situation when

table1.Name= @Name
table1.Name <> Name.

In first case table1.Name doesn't change value or index doesn't update and clustered index update cost 15%;

in second case table1.Name changes value and clustered index update cost 50%.

Why index is updating when update a equal value?

i can't select "Name" and compare befor updatings

I know that create varchar column  index isn't good idea

Update

update query with change Name column(execution CPU time 200ms)

update query without change Name column(execution CPU time 70ms)

Update

maybe anybody can help with trigger if it can help with this issue?

Update

Question is closed


texts:
Is there a way to get an array column while using GROUP BY command?
I am trying to get geometry from oracle database as array of coordinates, but get an error when try to use command GROUP BY

SELECT A.C_ID,
       A.ID,
       COUNT(T.Text),
       LISTAGG(T.TEXT, '/// ') WITHIN GROUP (ORDER BY NULL) text,
       P.GEOMETRY.sdo_ordinates p_geom
FROM G_P_THEME A
LEFT JOIN G_POLYGON P on P.C_ID=A.C_ID and P.ID=A.ID
LEFT JOIN  G_P_TEXT T on P.C_ID=T.C_ID and P.ID=T.ID
WHERE (A.THEME_ID=440 OR A.THEME_ID=47)
Group by A.C_ID, A.ID
Having (COUNT(T.Text) > 1);

it returns an error

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:
*Action:

I know that this is because there is no reference to P.GEOMETRY in GROUP BY command if I add P.GEOMETRY.sdo_ordinates in GROUP BY, result will be

ORA-00932: inconsistent datatypes: expected - got MDSYS.SDO_ORDINATE_ARRAY
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
Error at Line: 6 Column: 32

and if there is only P_GEOMETRY in GROUP BY

ORA-22901: cannot compare VARRAY or LOB attributes of an object type
22901. 00000 -  "cannot compare VARRAY or LOB attributes of an object type"
*Cause:    Comparison of VARRAY or LOB attributes of an object type
           was attempted in the absence of a MAP or ORDER method.
*Action:   Define a MAP or ORDER method for the object type.
Error at Line: 6 Column: 32

There is probably something that is needed to use in in SELECT to avoid using it in GROUP BY, but couldn't figure out what


texts:
I have a PC which runs a local desktop application that connects to a logic server with a database.
additionally i need a small cache database in this PC so when the client goes offline for some reason i can still have some of the data to pull and still use

I read this article about local database. I can't use Microsoft Access because of it's access limitation of one process can access the mdb file  (sound ironic :) ). On the other hand I want to avoid using SQL Server Express because it has quite an overhead per PC installation and maintenance.

I do want a SQL engine that will allow me triggers so I'll be able to cache or extract data from the PC to the server and vice versa automatically and not develop a service to cache the data

NoSQL like mongoDB can't be use in here because the data structure is mad of a lot of relationships

Any opinion or experience in this area will be helpful


texts: Why do the outdated statistics degrade a query performance?

texts:
Some facts:

1. The outdated statistics cause generating an inaccurate estimated
    plan. The estimated execution plan is created by Query Optimizer
    which uses both statistics and the query processor tree.

2. The actual execution plan is created after executing the query. When the stats are stale, the Storage Engine generates its own plan which is different than the estimated one.

The question: Why to bother about outdated stats if SQL Server can create accurate actual execution plan (by Storage Engine) when it finds out that data has been changed? How can the difference between the estimated and actual plan influences the query performance?


texts:
My problem is with Mongo eating up space on the disk.

Reading a few blogs no the web - I was certain - a repair would save me time as this would free up mongo's reserved space. But attempting a repair throws this error because I am at 90% disk usage :

db.repairDatabase()
{
    "ok" : 0,
    "errmsg" : "Cannot repair database my_d_b having size: 347673198592 (bytes) because free disk space is: 42874580992 (bytes)"
}

Then I tried the next step which is compacting every collection in the database using :

db.getCollectionNames().forEach(function (collectionName) {
  print('Compacting: ' + collectionName);
  db.runCommand({ compact: collectionName });
});

This reduced my disk usage by just 1%.

Now I can easily pause my apps from writing to this mongo database. I don't want to use any replset approach etc. I want to just move it to a bigger disk and move forward. I have already mounted an extra disk which 2.5times the size. I am not finding concrete steps to move the data and change mongo configuration and then start mongo process.

My repairs and compactions may be failing because they need space to operate as per my understading. If I can move this data to new disk and point mongo to the new disk, I will be good.

This is what I want to achieve - would appreciate a little direction.


texts: How to optimize a query so that it seeks on one index first, and then another index after that

texts:
I have two sets of earth measurements from satellite data, each with time fields (mjd for mean julian date) and geography positions (GeoPoint, spacial) and I'm looking for coincidences between the two sets such that their times match to a threshold of 3hrs (or .125 days) and their distances to within 200 km of each other.

I have made indexes for both the mjd fields on both tables and spatial tables.

When I just join on the time constraint, the database calculates 100,000 matches in 8 seconds and computes the distances for all 100,000 matches in that time. The query looks like this:

select top 100000 h.Time, m.Time, h.GeoPoint.STDistance(m.GeoPoint)/1000.0
from L2V5.dbo.header h join L2.dbo.MLS_Header m
on h.mjd between m.mjd-.125 and m.mjd+.125
option( table hint ( h, index(ix_MJD) ), table hint( m, index(ix_MJD) ) )

And the executed plan is:

When sorted, 9 of the distances were under 200km, so there are matches. The trouble is, when I add the distance constraint and run this instead,

select top 10 h.Time, m.Time, h.GeoPoint.STDistance(m.GeoPoint)/1000.0
from L2V5.dbo.header h join L2.dbo.MLS_Header m
on h.mjd between m.mjd-.125 and m.mjd+.125
and h.GeoPoint.STDistance(m.GeoPoint)<200000
option( table hint ( h, index(ix_MJD) ), table hint( m, index(ix_MJD) ) )

it goes away for a long time. Obviously, in 8 seconds, it could find 100,000 time matches, 9 of which were under 200km, so the optimizer must be trying something sub-optimal. The plan looks similar to above with a filter on the distances (I'm guessing).

I can force the use of the spatial index with this:

select top 5 h.Time, m.Time, h.GeoPoint.STDistance(m.GeoPoint)/1000.0
from L2V5.dbo.header h join L2.dbo.MLS_Header m
on h.GeoPoint.STDistance(m.GeoPoint)<200000
and h.mjd between m.mjd-.125 and m.mjd+.125
option( table hint ( h, index(ix_MJD), index(ix_GeoPoint) ), table hint( m, index(ix_MJD) ) )

which then takes 3 minutes to find 5 matches.

How do I tell the query optimizer to use the MJD index seek first, and then the spatial index second (or is that what it is doing already) and is there any way I can help it out by telling it how many matches to expect? If it can compute 100,000 matches with distances in 8 seconds that has 9 under 200km, shouldn't the addition of the spatial index make it faster not slower?

Thanks for any other tips or ideas.

EDIT: To answer the question what the plan looks like without the hints, this (and it takes forever):

It maybe also worth mentioning that there are almost 1M records in the one table and 8M in the other


texts: Shrinking and consolidating to only one log file

texts:
I've restored a database from production to QA, on SQL Server 2012. One of the databases has two log files:

logfile.ldf - 6 GB
logfile2.ldf - 200 GB

Due to space issues and data not being truncated through the years, there is data dating back from 10 years ago. However, I would like to only retain data from 2015 onwards.

I did the following:

Set Recovery Mode to Simple;
Issued DBCC SHRINKFILE multiple times in this order:

200 GB > 100 GB > 50 GB > 25 GB > 12 GB

DBCC SHRINKFILE(EMS_1_4_3_log, 12288); -- unit is set in MBs

Used a table for min and max dates and the dates remain the same.

Questions

Although the shrink was successful, I believe I can shrink it more but I'll stop here. How would I know that it shrunk efficiently?

I didn't use TRUNCATEONLY, should I have used this?

  TRUNCATEONLY

  Releases all free space at the end of the file to the operating system
  but does not perform any page movement inside the file. The data file
  is shrunk only to the last allocated extent. target_size is ignored if
  specified with TRUNCATEONLY. The TRUNCATEONLY option does not move
  information in the log, but does remove inactive VLFs from the end of
  the log file. This option is not supported for FILESTREAM filegroup
  containers.

What does it mean that "it does not move information in the log"?
To get the data dates back to retention period, do I truncate data in the tables themselves? What's a better way of doing this? I would lose the LSN, which we don’t care right now, but I still would like to recover from the retention dates.
How do I consolidate multiple logfiles?


texts:
We use Ola's maintenance solution and its great.

Regardless of method for re-indexing a major friction point with IT is the amount of log generated during the weekly re-indexing process.  For a 1TB DB upwards of 300 GB of log can be generated.  This causes mirroring backlogs/delays and also cause Data Protection Manager to take a long time to sync up with its off-site DPM partner server (sometimes several Days!).  As we approach a time where we will have a Second site on warm standby we know that this delay in having off-site backups available during the vulnerable period after Index maintenance could be the Achilles heal. We are considering a larger pipe between the sites for Avail Group but to generate less burst Log activity would be great.

To mitigate this we have done 2 things with only minimal impact.  First we spread out the weekly re-indexing by introducing Delays, purposely slowing a 3 hour process to about 8 hours or so.  Secondly "some" key tables are maintained by a process that runs hourly resulting in Just in Time re-indexing.

In a large and active OLTP DB with some LOB what are the rule of thumbs for re-indexing frequency, % of database affected, # indexes that should be rebuilt less frequently?  Is weekly rebuild overkill?


texts: Two dichotomy views of a table, and means to express this as a stored procudure?

texts:
There is a table events. Some relatively complex logic determines for each event whether it is archived. Then I need two more views: All archived events and all not archived events.

How to express this? Should I create a view, a procedure, or a function which tells for every view ID whether it is archive (in order to use it in two above mentioned views)?

MySQL.


texts: Can I use a single stored procedure to operate on different schemas based on the executing user

texts:
I have thousands of schemas with same set of tables, Each user has a default schema. But I dont want to create copies of a single stored procedure in every schema. Can a single stored procedure access the tables of user specific schema.

I have created the test projects to test this but it is throwing error as the table not found

create Database PermissionsTest

-- Create  user1 and UserSchema1  and assign permissions
CREATE LOGIN [User1] WITH PASSWORD=N'User1'
GO
CREATE USER [User1] FOR LOGIN [User1] WITH DEFAULT_SCHEMA=[UserSchema1]
GO
ALTER LOGIN [User1] Enable
go
CREATE SCHEMA [UserSchema1] AUTHORIZATION [User1]
GO

-- Create  user2 and UserSchema2  and assign permissions
CREATE LOGIN [User2] WITH PASSWORD=N'User2'
GO
CREATE USER [User2] FOR LOGIN [User2] WITH DEFAULT_SCHEMA=[UserSchema2]
GO
ALTER LOGIN [User2] Enable
go
 CREATE SCHEMA [UserSchema2] AUTHORIZATION [User2]
GO

-- Create StoredProcedure Schema and creating the role to execute on this     schema
CREATE ROLE [ExecuteSprocsOnStoredProcsSchema] AUTHORIZATION [dbo]
GO

ALTER AUTHORIZATION ON role::ExecuteSprocsOnStoredProcsSchema TO User1;
GO

Create Schema [StoredProcedures] AUTHORIZATION [ExecuteSprocsOnStoredProcsSchema]
Go

EXEC sp_addrolemember N'ExecuteSprocsOnStoredProcsSchema', N'User1'
GO
EXEC sp_addrolemember N'ExecuteSprocsOnStoredProcsSchema', N'User2'
Go

-- GRANT Execute ON SCHEMA :: StoredProcedures TO     ExecuteSprocsOnStoredProcsSchema

GRANT CONNECT TO [User1]
Grant Connect to [User2]

grant SELECT ON SCHEMA::[dbo] TO [User1]
GO
grant SELECT ON SCHEMA::[dbo] TO [User2]
GO
----------------- Database data side changes ---------------
USE PermissionsTest
GO
CREATE TABLE [dbo].[StaticTable](
   [pkcol] [int] IDENTITY(1,1) NOT NULL,
   [col1] [int] NULL,
PRIMARY KEY CLUSTERED ([pkcol])
)
GO

insert into StaticTable values (420);

-- drop table [UserSchema2].[Table1]

CREATE TABLE [UserSchema1].[Table1](
   [pkcol] [int] IDENTITY(1,1) NOT NULL,
   [col1] [varchar](max) NULL,
PRIMARY KEY CLUSTERED ([pkcol])
)
GO

CREATE TABLE [UserSchema2].[Table1](
   [pkcol] [int] IDENTITY(1,1) NOT NULL,
   [col1] [varchar](max) NULL,
PRIMARY KEY CLUSTERED ([pkcol])
)
GO

Create Procedure [StoredProcedures].[Insert_Table1]
as
begin
Insert into table1 values (newID());
Insert into table1 select col1 from StaticTable;
end

after setting up the system for two users and the stored propcedure Insert_Table1

When I try to execute the stored procedure by User1 I get this error

  Msg 208, Level 16, State 1, Procedure Insert_Table1, Line 6
  Invalid object name 'table1'.

but when I run the queries separately as user1 it gets executed without any problem.

I know while executing the stored procedure the stored procedure temperarily takes the permissions of the Schema owener of that Stored procedure

I have created the schema with authorization to the role assigned to user1 already.

Create Schema [StoredProcedures] AUTHORIZATION [ExecuteSprocsOnStoredProcsSchema]

Am I missing anything.. Is there any way to make it work? Or is there any way that a single stored procedure can be used over multiple schemas..


texts: Is there anyway to view historical performance of SQL 2008 R2 database?

texts:
So.. I was wondering, is there a way for me to generate any report for the historical performance of a MS SQL database? I'm originally an Oracle DBA, but now I'm requested to support a few SQL databases in my office as there's nobody supporting it now.
Being an Oracle DBA, I know where to look if I need any reports related to DB performance e.g. AWR & Statspack but now MS SQL DB.
Going around google doesn't yield any result as well unfortunately, since most requested me to turn on SQL profiler to run the trace, which I can't since it will cause even more load to the high loaded server.
Is there a way to configure the DB to take snapshots every half hour and combine it into a report like how AWR / Statspack behave in Oracle?


texts: Unable to apply order by clause on distinct select in Oracle

texts:
The query is:

select distinct( Name )
from Students
where marks>75
order by Substr(Name,-3,3),ID asc;

This shows an error message:

order by Substr(Name,-3,3),ID asc
*
ERROR at line 4:
ORA-01791: not a SELECTed expression


texts: SELECT blocks UPDATE in Snapshot Isolation

texts:
We have a database running on SQL Server 2016 SP1 Enterprise and on which Allow Snapshot Isolation is enabled. In this scenario readers do not block writers and writers do not block readers.

Today we saw that a SELECT-query was blocking an UPDATE-query.
These are the queries at issue:

First query:

IF @@TRANCOUNT = 0 SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SELECT TOP 10 Field1, Field2, Field3 FROM [dbo].[TABLE1]
WHERE ([Field1] = @SV1 AND (1=1))
ORDER BY A.[Field1] ASC,A.[Field2] ASC
OPTION (KEEP PLAN, KEEPFIXED PLAN, LOOP JOIN)

Second query:

UPDATE [dbo].[Table1] SET [Field4]= @V128, [Field5]= @V140, [Field6]= @V141 WHERE [Field3] = @ID0

If a understand versioning correctly, a SELECT-query do not use any locks because it works on a snapshot of the data (taken at the start of the transaction). How is it possible that the SELECT-query blocks the UPDATE?


texts: What does 'Vacuum' do in Menial's Base 2 application?

texts: I've had a look at the help documentation for Base 2, as well as Menial's product page, but haven't found an answer there. I've even clicked on the vacuum button and not noticed any change. What does that button do?


texts: Where has STATMAN gone in SQL Server 2014?

texts:
So we've recently upgraded our environment to SQL Server 2014 (Enterprise on CU5) and are having some problems diagnosing performance issues, compounded by the problem that we no longer seem to be able to tell when SQL Server is updating/creating statistics.

Previously, when compiling a plan, if SQL Server needed additional statistics (we have autostats on) we could see in sp_whoisactive that STATMAN was busily working away doing his thing. Now all we get is a blank sql_text column, no query plan and a huge number of reads and only by looking at the locks do we see that there are some statistics somewhere being generated (but not which columns on what table).

Does anyone have any insight into the changes here? Is there any way to tell what stats the optimizer has asked for? I know we can once the plan is compiled (thank you Paul White) but we have a fairly large data warehouse, so sometimes the stats collection operation takes a significant amount of time!

UPDATE
It looks like while sp_whoisactive no longer shows you the stats being created/updated, you can use a dmv to find out what's going on with async stats:

SELECT  time_queued,
        OBJECT_NAME(object_id1),
        ss.name,
        sc.name,
        auto_created,
        user_created,
        no_recompute,
        has_filter,
        is_temporary,
        is_incremental
FROM    sys.dm_exec_background_job_queue bq
JOIN    sys.stats ss ON ss.object_id = bq.object_id1
                        AND ss.stats_id = bq.object_id2
JOIN    sys.stats_columns stc ON stc.object_id = ss.object_id
                                 AND stc.stats_id = ss.stats_id
JOIN    sys.columns sc ON sc.column_id = stc.column_id
                          AND sc.object_id = stc.object_id
WHERE   in_progress = 1

The way to kill the background jobs is via the KILL STATS JOB command to terminate the stats process.


texts: FULLTEXT Search (InnoDB) - Am I doing it right?

texts:
Goal: I wish to do autocomplete using ~130K global city names.

Setup: I have created a 'cities' table as follows:

CREATE TABLE IF NOT EXISTS cities (
          id INT(11) NOT NULL AUTO_INCREMENT,
          name VARCHAR(199) NOT NULL,
          PRIMARY KEY  (id),
          FULLTEXT (name)
          )$charset_collate;

My autocomplete code would fetch the 'name' entries and suggest them to user as user types name.

I'm using following query to fetch results:

SELECT name
FROM cities
WHERE MATCH (name) AGAINST (\''.$searchQuery.'*\' IN BOOLEAN MODE);

Question: Is this the right approach to take full advantage of FULLTEXT? Am I missing out on anything? Can my query be improved to fetch better results?

Thanks!


texts: Open Transaction that won't go away SPID Ns

texts:
There is a transaction that has been open on this instance since it was restarted and is preventing us from clearing the ldf.

Here is the transaction information:

Transaction information for database 'MYDATABASE'.

Oldest active transaction:
    SPID (server process ID): 7s
    UID (user ID) : -1
    Name          : user_transaction
    LSN           : (54883:155655:1)
    Start time    : Jun 30 2014  5:45:35:820PM
    SID           : 0x01
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Does anyone know what 7s means, I have not yet seen a SPID with a letter in it.

Thanks,
Craig

Update 14:03

I ran the resolution suggested here and this is the result after restart:

2014-07-18 14:01:12.83 spid7s      Mise à niveau des paramètres d'abonnement et des objets système dans la base de données [MYDATABASE].
2014-07-18 14:01:13.03 spid7s      Nom d'objet 'MSreplication_subscriptions' non valide.
2014-07-18 14:01:13.03 spid7s      Error executing sp_vupgrade_replication.
2014-07-18 14:01:13.03 spid7s      Saving upgrade script status to 'SOFTWARE\Microsoft\MSSQLServer\Replication\Setup'.
2014-07-18 14:01:13.04 spid7s      Saved upgrade script status successfully.

I still have the same problem. Any ideas on how to resolve this?

Update 19/07
Problem solved by adding the  MSReplications_subscriptions table to the database where sp_vupgrade_replication was failing


texts: No network ACL, yet my user can successfully connect to external server

texts:
This one's had both me and our dba scratching our heads.

We have an Oracle 11.1.0.7.0 instance, DEV, with a user who can successfully connect to our ldap server, e.g.:

DECLARE
  l_session DBMS_LDAP.session;
BEGIN
  l_session := DBMS_LDAP.init('ldap.host.bla.bla',389);
END;
/

PL/SQL procedure successfully completed

We have another Oracle instance, 11.2.0.3.0 called UAT, with the same user, same set up, but when they run the above code, it raises an exception from DBMS_LDAP:

ORA-24247: network access denied by access control list (ACL)

In both instances, if I run the following queries, no rows are returned:

select * from dba_network_acls;

select * from dba_network_acl_privileges;

To fix this problem in UAT, we've created a suitable network ACL using DBMS_NETWORK_ACL_ADMIN.create_acl. Now, UAT works fine.

What's bothering us is that this worked fine in DEV all along, with apparently no network ACLs created. Our only theory is a bug in the version in DEV (11.1.0.7.0) but I hesitate to just leap on that as a conclusion.

We will soon be upgrading DEV to 11.2 to be consistent with the other environments, but before then, is there anything else I can check to see what difference there is between the two environments that might cause this behaviour?

(Background for those unfamiliar with ACLs: http://www.oracle-base.com/articles/11g/fine-grained-access-to-network-services-11gr1.php)


texts: SSMS causing Event ID 4674: SeTakeOwnershipPrivilege

texts:
Somewhat of a strange one.  Recently my user id's been showing up in a number of Audit Success records in the Security event log of one of our production database servers with the below content (or similar):

An operation was attempted on a privileged object.

Subject:
    Security ID:        myDomain\myUsername
    Account Name:        myUsername
    Account Domain:        myDomain
    Logon ID:        0x297c9c7af4

Object:
    Object Server:    SC Manager
    Object Type:    SC_MANAGER OBJECT
    Object Name:    ServicesActive
    Object Handle:    0xfffffa6012a44b18

Process Information:
    Process ID:    0x2f4
    Process Name:    C:\Windows\System32\services.exe

Requested Operation:
    Desired Access:    DELETE
            READ_CONTROL
            WRITE_DAC
            Connect to service controller
            Create a new service
            Enumerate services
            Lock service database for exclusive access
            Query service database lock state
            Set last-known-good state of service database

    Privileges:        SeTakeOwnershipPrivilege

This record appears about 40 times one second, then there's a few seconds without anything being logged before the next batch of ~40.

The events start appearing after I connect to a database hosted on the affected server from SSMS.  I don't open a query window or do anything more than connect under my user account.

Disconnecting in SSMS does not stop the events.  Exiting SSMS does.

Has anyone seen behavior like this before / any idea what may cause it?

Thanks in advance.

Version Info:

SSMS: 10.0.5512.0 (on OS 5.2.3790)
DB: Microsoft SQL Server 2008 (SP3) - 10.0.5794.0 (X64) Enterprise Edition (64-bit) on Windows NT 6.0  (Build 6002: Service Pack 2)


texts: Filter joined rows if column's value exists in another table

texts:
Today I have encountered a problem as follows (fiddle here):
create table ta02
(
  t_key      varchar(255) default null,
  ta_over_amount decimal(13,2) default null,
  name           varchar(255) default null
);

and:
create table voucher
(
  v_key       varchar(255) default null,
  over_amount decimal(13,2) default null,
  signature   varchar(255) default null
);

Populate:
INSERT INTO ta02 (t_key, ta_over_amount, name) VALUES ('gogo', 100.00, 'anh');
INSERT INTO ta02 (t_key, ta_over_amount, name) VALUES ('gogo', 50.00, 'duc');
INSERT INTO ta02 (t_key, ta_over_amount, name) VALUES ('logbasex', 25.00, 'ho');
INSERT INTO ta02 (t_key, ta_over_amount, name) VALUES ('logbasex', 15.00, 'phi');

and:
INSERT INTO voucher (v_key, over_amount, signature) VALUES ('gogo', 100.00, 'blue');
INSERT INTO voucher (v_key, over_amount, signature) VALUES ('gogo', 50.00, 'white');
INSERT INTO voucher (v_key, over_amount, signature) VALUES ('logbasex', 14.00, 'itsme');
INSERT INTO voucher (v_key, over_amount, signature) VALUES ('logbasex', 25.00, 'green');

This is my join query without any conditions:
select t.name, t.ta_over_amount, v.over_amount, v.signature
from ta02 t
left join voucher v on t.t_key = v.v_key;

Result:
name    ta_over_amount  over_amount     signature
anh     100.00           50.00          white
anh     100.00          100.00          blue
duc     50.00            50.00          white
duc     50.00           100.00          blue
ho      25.00            25.00          green
ho      25.00            15.00          itsme
phi     15.00            25.00          green
phi     15.00            14.00          itsme

And the required condition is:

On the same v_key, only join which row that has ta_over_amount value does not equal to corresponding over_amount value of matched row and the list ta_over_amount values (get by the joined key v_key in ta02 table) does not contains over_amount value.

For example, when joining two tables with v_key = gogo (first row) in ta02 table, that will match two corresponding rows in voucher table, which is include signature = blue and white respectively. With a row has signature = blue, the over_amount value = 100 is equals to the ta_over_amount value then I ignore that row (clearly doesn't satify my condition), with a row has signature = white, the over_amount value = 50 is not equals to ta_over_amount but exits in list of ta_over_amount values with v_key = gogo in ta02 table then I also need to ignore this row.

And this is my desired result:
    +----+--------------+-----------+---------+
    |name|ta_over_amount|over_amount|signature|
    +----+--------------+-----------+---------+
    |phi |15.00         |14.00      |itsme    |
    +----+--------------+-----------+---------+

Ex:

ta02 table
+--------+--------------+----+
|key     |ta_over_amount|name|
+--------+--------------+----+
|gogo    |100.00        |anh |
|gogo    |50.00         |duc |
|logbasex|25.00         |ho  |
|logbasex|15.00         |phi |
+--------+--------------+----+

voucher table
+--------+-----------+---------+
|key     |over_amount|signature|
+--------+-----------+---------+
|gogo    |110.00     |blue     |
|gogo    |50.00      |white    |
|logbasex|25.00      |itsme    |
|logbasex|15.00      |green    |
+--------+-----------+---------+

My expected result is:
+----+--------------+-----------+---------+
|name|ta_over_amount|over_amount|signature|
+----+--------------+-----------+---------+
|anh |100.00        |110.00     |blue     |
+----+--------------+-----------+---------+

- 100 doesn't match 110, I include this one in the desired result.
- 100 doesn't match 50, but 50 exits in ta_over_amount list, ignore
- 50  doesn't match 110, but 50 exits in over_amount list, ignore
- 50 match 50, ignore


texts:
I have a table with contact records, and clients wish to search this table to find records with a matching email address (filtered by account ID). The results are paginated by a RESTful API.

This results in queries like the below being run:

SELECT * FROM CONTACT
WHERE deleted is null AND email like '%Matt%' AND accountid=1
ORDER BY lastname LIMIT 0,50;

In this table, there are indexes on email, accountid, and lastname. Overall, this table has 3.6 million rows, and in this particular example this accountid has 170,000 rows. This particular query is taking around 2 minutes 30 seconds to complete.

If I change the query to remove the ORDER BY clause, the query finishes in about 0.6 seconds. If I change the query to remove the LIMIT clause, the query finishes in about 0.23 seconds.

I have read this article but I'm not sure what else I can do to improve this query. I know that the email index won't be used because of the full wildcard search, but I thought the lastname index would help things with the ORDER BY anyway.

mysql> explain SELECT * FROM CONTACT WHERE deleted is null AND email like '%Matt%' AND accountid=1 ORDER BY lastname LIMIT 0,50;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | a     | index | accountid     | lastname | 53      | NULL | 5804 | Using where |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)

How can I improve this query to finish quicker with the ORDER BY and LIMIT 0,50 clauses?


texts: Hitting a transactional table, or copying the data to a temporary table?

texts:
I have the following query (Pseudo SQL warning):

select count(*), groupcolumn from table
where date is < 6 days ago  and > 7 days ago
AND date is < 13 days ago
AND date > 14 days ago... (until 28 days ago)
group by groupcolumn

I was told to optimize this as:

select count(*), groupcolumn from (
select columns from table
 where date is < 6 days ago  and > 7 days ago
union all
select columns from table
where date is < 13 days ago  and > 14 days ago
union all
(repeat until 28 days ago)) group by groupcolumn

This hits the table several times but only brings the rows i need (60K of them for the 4 days specified).

The DBA then told me this query is no good because "it's hitting a transactional table multiple times and it's bad performance". The table in question has records being inserted continuously, a couple to a dozen of them every second.

His suggestion was to change the query to:

select "only the minimum columns i need, if possible only IDs"
into #temptable
from table where date is < 6 days ago  and > 28 days ago

And then do my original query against "this much smaller table that also doesn't interfere with the transactional table".

The DBA tells me this query is better because "it's not hitting the transactional table so much". It counter intuitive to me, though, since I'm bringing 15K records per day. So instead of copying 60K records to memory,  I'll now be copying 420K records to disk, and running a second query against those.

I'm using SET TRANSACTION LEVEL READ UNCOMMITTED and WITH(NOLOCK) for every table. I don't mind dirty reads as I'm just making approximate statistics, so I don't see why "hitting the table so many times" would be an issue here.


texts: Login MSSQL using Active Directory account from local Windows account

texts:
Due to Coronavirus, we on my company are working from home. I had setup a virtual machine and am using VPN to connect to company's intranet.

It happens that we use company's AD account to log in MSSQL. We log in Windows with our account, and use Windows Authentication to log in MSSQL.

Now, I'm unable to log in local Windows using AD. I'd need to configure it to connect to AD, but for that Windows/PC needs a machine digital certificate, and be approved by some domain admin. That won't happen.

Anyway, I'm looking for a way to run SSMS using AD account, without needing to use it on Windows. I tried Run as different user but it also didn't work.


texts: Indexing options for Postgres for a table with billion records when you want to index only recent records

texts:
We have a table which has close to 1 billion records taking up to ~315 Gigs. This is just the size of the raw table without the size taken by any indexes.
This table has a updated_at column which gets updated whenever an entry in the table is added/updated. The use case we have now is to query recently added/modified entries. We don't want to add an index on the updated_at column for the whole table.
We are considering the following options:

At the beginning of a month, create a partial index on the table for the duration of the month (Example: If we run this on May 1st, we will create an index for range May 1 - May31).
At the end of the month, drop the partial index for the previous month (Example: If we run this on May 31, we will drop the index created on April 1 for the range April 1 - April 30)

This way we will have not more than two indexes on the column at a given time.
Since the query loads might not look past more than two months right now, this will work well but not sure if there are any gotchas around it.
Specifically.

Is the constant create and drop indexes bad for the table?
How does the performance of the inserts/updates go if there are multiple indexes to update. Though all the updates should only affect one index at a time. The index for the previous month will not receive any updates once we have next month.

Are there better ways to handle this situation than outlined above ?
Cheers


texts: I dropped the sys schema while tinkering around mysql 8(current version). Is there a way to recover it. I am using ubuntu 20.04. I tried using the mysql documentation but I couldn't find anything relevant. Could someone with better experience help me with this? Thank you.


texts: Mariadb alongside MySQL Can't start the MariaDB

texts:
I'm running MySQL on ubuntu 14.04 and it works great.

For development and testing before switching between the two I want to install a separate Mariadb server.

I followed this (old) tutorial: Installing MariaDB Alongside MySQL

Everything was done carefully with a 10.0.21 compiled binaries until I try to start the mariadb:

# /etc/init.d/mariadb start

Instead of printing [ OK ] it prints .*. I went to the error log in the data folder and found two types of errors depending on the installed mysql server:

If mysql was running -> mysql_safe A mysqld process already exists.
I  mysql was not running -> File './mysql-bin.index' not found (Errorcode: 13 Permission denied)

I think the Mariadb is trying to use the wrong pid file and I have no clue how to fix that - Any Ideas?


texts: Update postgres json value by other attribute

texts:
I'm using PostgreSQL 9.6 and
I got the following table:
Cars
ID          Name        Data
 1           abc        {"color":"red","wheels":4,"my-value":false}
 2           def        {"color":"blue","wheels":8}
 3           xyz        {"color":"yellow","my-value":true}

I want to extend the Data JSON object by adding another boolean attribute called "new-attr" which should get the exact value of "my-value" of the JSON field.
for example, if "my-value" in ID 1 is false, then "new-attr" will be false.
And if not set, do not add it at all (as in number 2).
I tried the following but it didn't help:
UPDATE cars SET data=jsonb_set(data,'{new-attr}',data->'my-value')

I expect to get the following JSON :
 ID          Name        Data
1           abc        {"color":"red","wheels":4,"my-value":false,"new-attr":false} // same value
2           def        {"color":"blue","wheels":8} // no change
3           xyz        {"color":"yellow","my-value":true,"new-attr":true} // same value true

Any suggestions ?


texts: Unable to restore database because it exists, unable to drop because it does not exist

texts:
I've seen this mentioned a couple of times but none of the suggested solutions worked for me, so I'm posting a question together with a solution in hope that it helps someone else.

During restore of a development database I encountered:

SQL1005N  The database alias "XXXX" already exists in either the local
database directory or system database directory.

db2 drop db XXXX
SQL1031N  The database directory cannot be found on the indicated file system.
SQLSTATE=58031

The solution suggested elsewhere is to catalog the database and then drop it:

db2 catalog db XXXX
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.

db2 drop db XXXX
SQL1031N  The database directory cannot be found on the indicated file system.
SQLSTATE=58031

So it appears as there's a zoombie database lurking around, that we can't get rid of. What to do?


texts: Postgres 9.3 - Problem to initialize folder as a data folder

texts:
I have a problem when I want to initialize a folder as a data folder:

postgres@ccruzado-test01:~$ /usr/lib/postgresql/9.3/bin/initdb -D /datadrive/postgresql/
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /datadrive/postgresql ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
creating configuration files ... ok
creating template1 database in /datadrive/postgresql/base/1 ... LOG:  could not link file "pg_xlog/xlogtemp.1716" to "pg_xlog/000000010000000000000001" (initialization of log file): Operation not supported
FATAL:  could not open file "pg_xlog/000000010000000000000001": No such file or directory
child process exited with exit code 1
initdb: removing data directory "/datadrive/postgresql"

The directory /datadrive/postgresql/ is a drive mounted by CIFS in FSTAB

//prueba /datadrive cifs vers=2.1,credentials=/home/user/.smbcredentials,dir_mode=0777,file_mode=0777,nounix 0 0.

The OS is Ubuntu 14.04


texts: Azure IaaS VM - Use D:\ Drive for tempdb - NTFS Formatting 64k

texts:
This was a question I was battling with earlier today and eventually figured out an answer.  Wouldn't mind something better, but wanted to provide this to whomever also needed.

First, on an Azure VM you get a D:\ drive for free which is SSD.  The caveat is that it usually gets destroyed when a VM is restarted.  MS best practice for high write volume is to use this drive for tempdb.  What they don't go into is that it's not formatted 64kb.  The hidden pagefile.sys resides here, so you need to keep that into account if you try to reformat (it'll fail).

From this:
https://cloudblogs.microsoft.com/sqlserver/2014/09/25/using-ssds-in-azure-vms-to-store-sql-server-tempdb-and-buffer-pool-extensions/

You'll want to alter their startup powershell script to something more like below, where I'm first removing the pagefile entirely, then formatting to 64k, creating the file as the script usually would, then putting the pagefile.sys back before starting the startup services.

$SQLService=”SQL Server (MSSQLSERVER)”
$SQLAgentService=”SQL Server Agent (MSSQLSERVER)”
$tempfolder=”D:\SQLTEMP”
if (!(test-path -path $tempfolder)) {
    (Get-WmiObject -Class Win32_PageFileSetting).Delete()
    Format-Volume -DriveLetter D -FileSystem NTFS -AllocationUnitSize 65536 -NewFileSystemLabel "Temporary Storage"  -Confirm:$false
    New-Item -ItemType directory -Path $tempfolder
    Set-WMIInstance -Class Win32_PageFileSetting -Arguments @{ Name = 'D:\pagefile.sys';}
}
Start-Service $SQLService
Start-Service $SQLAgentService

Does anyone have anything better or see any holes in this process?

For a little more automation/help for others, I also script the above script out as well as the triggers to create it and run it 30s after startup using below.  This basically allows someone to automatically do the steps in the cloudblogs article I reference.

#1 - Set Services to manual startup so that windows scheduler will start after tempdb adjusted
Set-Service -Name MSSQLSERVER -StartupType Manual
Set-Service -Name SQLSERVERAGENT -StartupType Manual
IF (Get-Service MsDtsServer130 -ErrorAction SilentlyContinue)
{
Set-Service -Name MsDtsServer130 -StartupType Manual
}

<#2 - using below powershell script to create the startup script#>
$script=
'
$SQLService="SQL Server (MSSQLSERVER)"
$SQLAgentService="SQL Server Agent (MSSQLSERVER)"
$tempfolder="D:\SQLTEMP"
if (!(test-path -path $tempfolder)) {
    (Get-WmiObject -Class Win32_PageFileSetting).Delete()
    Format-Volume -DriveLetter D -FileSystem NTFS -AllocationUnitSize 65536 -NewFileSystemLabel "Temporary Storage"  -Confirm:$false
    New-Item -ItemType directory -Path $tempfolder
    Set-WMIInstance -Class Win32_PageFileSetting -Arguments @{ Name = "D:\pagefile.sys";}
}
Start-Service $SQLService
Start-Service $SQLAgentService
IF (Get-Service MsDtsServer130 -ErrorAction SilentlyContinue)
{
Start-Service -Name MsDtsServer130
}
'

Out-File -FilePath C:\SQL-startup.ps1 -InputObject $script -NoClobber

<#3 - using below script to create a scheduled task to call a powershell script that creates a folder on the d drive.#>
$trigger = New-JobTrigger -AtStartup -RandomDelay 00:00:30
Register-ScheduledJob -Trigger $trigger -FilePath C:\SQL-startup.ps1 -Name CreateSqlFolderOnDSsd

**Edit, above assumes SQL 2016 and at least powershell 5 (though I'm unaware of specific dependencies, should work on v4 at least).


texts: What does it mean that a mongodb query has a waitingForLatch value of ReplicationCoordinatorImpl::_mutex?

texts: Some queries that are normally fast sometimes take ages to complete. I checked currentOp() and the queries that are taking so long are waiting for the ReplicationCoordinatorImpl::_mutex latch. None of those queries are inserts, updates or deletes. What does it mean?


texts: What is the Difference between Data Transformation vs Transportation?

texts:
Is there a difference in meaning between "data transformation" and "data transportation" in ETL process and data warehousing? or both concepts can be used interchangeably in this context?

Thanks


texts: ALTER TABLE SWITCH statement failed. SWITCH is not allowed because source table '' contains primary key for constraint ''

texts:
I'm using SQL Server 2008.

I have two tables PARTITIONTESTTABLE and PARTITIONTESTTABLESUB. PARTITIONTESTTABLESUB has a foreign key to PARTITIONTESTTABLE. I created partition and both of these two tables are partitioned. I also created staging table from Managing partition wizard.

When I run....

ALTER TABLE  PARTITIONTESTTABLESUB SWITCH PARTITION 10 to [staging_PARTITIONTESTTABLESUB];

...It runs to success.

But...

ALTER TABLE  PARTITIONTESTTABLE SWITCH PARTITION 10 to [staging_PARTITIONTESTTABLE];

...Failed with error:

  ALTER TABLE SWITCH statement failed. SWITCH is not allowed because
  source table 'Enterprise.MSH2.PARTITIONTESTTABLE' contains primary key
  for constraint
  'staging_PARTITIONTESTTABLESUB_PARTITIONTESTTABLESUB_FK'.

DDL:

CREATE TABLE [PARTITIONTESTTABLE](
    [pkcol] [int] IDENTITY(1,1) NOT NULL,
    [datacol1] [int] NULL,
    [datacol2] [int] NULL,
    [datacol3] [varchar](50) NULL,
    [partitioncol] [datetime] NOT NULL,
 CONSTRAINT [PK_PARTITIONTESTTABLE] PRIMARY KEY CLUSTERED
(
    [pkcol] ASC,
    [partitioncol] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

CREATE TABLE [PARTITIONTESTTABLESUB](
    [subpkcol] [int] IDENTITY(1,1) NOT NULL,
    [fkcol] [int] NOT NULL,
    [datacol1] [int] NULL,
    [datacol2] [int] NULL,
    [datacol3] [varchar](50) NULL,
    [partitioncol] [datetime] NOT NULL,
 CONSTRAINT [PK_PARTITIONTESTTABLESUB] PRIMARY KEY CLUSTERED
(
    [subpkcol] ASC,
    [partitioncol] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [PARTITIONTESTTABLESUB]  WITH CHECK ADD  CONSTRAINT [PARTITIONTESTTABLESUB_FK] FOREIGN KEY([fkcol], [partitioncol])
REFERENCES [PARTITIONTESTTABLE] ([pkcol], [partitioncol])
GO

ALTER TABLE [MSH2].[PARTITIONTESTTABLESUB] CHECK CONSTRAINT [PARTITIONTESTTABLESUB_FK]
GO


texts: Validation of data with checksum vs Binary_checksum

texts:
Based on this post, I was able to solve the performance issue by adding indexes and thought I completed my project until I ran into an issue with the checksum... I then did research (should have done this before I started the project) and found that the checksum and binary_checksum are not reliable in a system critical environment.

I'm looking for a reliable way, to compute data for a table at a store and then compare it with the same table at the head office.

As explained in the post mentioned above, Data files are transferred from multiple stores (transactions) to the head office as they happen. For each store the table in question is pretty big in size. At the head office, that same table contains the same info from all stores which makes it huge. The data is transferred through files as there isn't any direct connections between the HO and the stores.  Ideally it would be great if I could transfer all the data from the store, line by line and compare it against the head office's table but it's unthinkable since it's way too big.

I've tried to use the HASHBYTES by casting columns to strings but just found out that it has a limitation of 8000 characters up until SQL Server 2014.  Our systems have to support any version of SQL Server from 2008 up until today.

I'm trying to find a reliable way to get some type of aggregated checksum value that can be compared between each store and their head_office table by table.

UPDATE: I've tried to use the Hashbyte but it doesn't work in my case since I'm not comparing row by row due to the amount of data between each store and the head office.  Example: if a store has 100 000 rows for a table, the head office that would have the info for 10 stores would contain 1 000 000 rows in that same table... I can't send all the rows from the store to the head office just to compare and make sure that all the data has been transferred successfully.  So my way of thinking is aggregate the info at the store per date and compare it with the same aggregation for the same store at the head office and if I find a mismatch, I have a process that is triggered to request the info from the store for a specific date to the head office.

I was so happy to find the Checksum_agg of a checksum, which did just that... but after reading and testing different scenarios such as checksum(100.00) giving the same result as checksum(10.00), it killed my whole logic.

Any other ideas? anyone?


texts:
I need to combine the results from two queries into one result set and order them altogether.

This is the working, raw SQL code I have:

SELECT * FROM (
    SELECT
      t1.id AS entity_id,
      t2.title,
      'query1' AS entity_type
    FROM
      `table1` t1,
      `table2` t2
    WHERE
      t1.id = t2.id
  UNION ALL
    SELECT
      t3.id AS entity_id,
      t4.title,
      'query2' AS entity_type
    FROM
      `table3` t3,
      `table4` t4
    WHERE
      t3.id = t4.id
) AS results ORDER BY results.entity_id

The query above works perfectly, but I need to convert it into a specific syntax for a CMS database API, which does not support such queries.

I am looking for something without the SELECT * FROM(subquery) as such queries can be converted easily.

Is there any command I can use to UNION ALL and order records from both sub-queries?

Thank you!


texts: User databases inaccessible after physical restart

texts:
We have an unusual error happening on the SQL Server.

After restarting the physical server where the SQL Server instance is installed, the user databases are not accessible. System databases are accessible.

Please see screenshot (After right clicking on DB and choosing properties) as example and error at bottom of the page.

I did find this note, however the owner field was already populated.
http://blogs.msdn.com/b/suhde/archive/2009/04/05/property-owner-is-not-available-for-database-dbname.aspx

However, the most interesting part is that when I restart the SQL Server instance, the user databases become accessible and no error is returned anymore. Everything functions as normal. This is done by logging into SSMS as 'sa' user.

I have tried to add additional permissions to the MDF, LDF and database location files, but haven't had much luck.
I will mention that the User Databases (D:\Data | D:\Logs) are in a different location to the System Databases (C:\ Default Location), although on the same server/network.

The SQL Server (MSSQLSERVER) service logs on as NT Service\MSSQLSERVER.

SQL Server version is:

  Microsoft SQL Server 2012 - 11.0.2100.60 (X64)  Feb 10 2012 19:39:15

  Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on

  Windows NT 6.2  (Build 9200: ) (Hypervisor)

Error:

  Property Size is not available for Database '[xxxxx]'. This property
  may not exist for this object, or may not be retrievable due to
  insufficient access rights.  (Microsoft.SqlServer.Smo)


texts:
As an analyst I love having the ability to turn up the work_mem on occasion when  a big sort or index build is in the offing.

As a DBA, the idea of random users being able to request an arbitrarily large amount of system resources at run-time is a little scary. I confirmed that a basically permission-less fresh-created user, could request as much work_mem as they wished. It would presumably take only a few greedy users at once to starve out other users, which is scary.

Is there any way to limit the work_mem a user can request?


texts: How do you show SQL executing on an Oracle database?

texts:
How can you show the SQL that is currently executing on an oracle db?

Extra information that would be useful would include user, session id etc.


texts: Large dataset of 400+ million rows to compare

texts:
I am trying to solve this logic problem but my current MySQL schema is giving me a very poor performance with a large dataset.
I have a table which contains almost half a billion telephone records. They are called the Do Not Call list or simply the DNC.
When I start my campaign to dial numbers, I have to check against this list to see if my phone numbers are there in the list or not. If yes, then scrub from the list and do not dial that number.
Here is my dnc table:
CREATE TABLE `dnc_contacts` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `group_id` INT(11) NOT NULL,
  `contact` VARCHAR(45) NOT NULL,
  `status` TINYINT(1) NOT NULL DEFAULT '1',
  `created_date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `admin` TINYINT(1) NOT NULL DEFAULT '0',
  `updated_date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`group_id`),
  KEY `fk_dnc_contact_dnc_group1_idx` (`group_id`),
  KEY `idx-group_id-contact` (`group_id`,`contact`),
  CONSTRAINT `dnc_contacts_copy_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `dnc_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The query I am using to compare the dnc table is:
UPDATE contacts c LEFT JOIN dnc_contacts d ON d.contact = c.contact AND d.group_id = '9' SET campaign_id = '525', call_status = 1, active_call = 0, dnc_contact = if(d.contact IS NULL, 0, 1) WHERE phonebook_id IN (920)

My contacts table looks like:
CREATE TABLE `contacts` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `phonebook_id` int(11) NOT NULL,
  `campaign_id` int(11) DEFAULT NULL,
  `contact` varchar(45) NOT NULL,
  `status` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `admin` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `call_status` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `active_call` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `test_call` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `dnc_contact` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `v_node_name` varchar(45) DEFAULT NULL,
  `updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_date` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`,`phonebook_id`),
  KEY `fk_contact_phonebook1_idx` (`phonebook_id`),
  KEY `idx-phonebook_id-contact` (`id`,`contact`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Running this query for small datasets with 100K to 200K contacts works fine, but with large dataset with more than 300 million records kills the performance. Can't think of a better way to achieve the goal.
Appreciate any advice to improve the logic/schema/query.

The contact is not unique. I can have the same contact but under a different group or a phonebook. It gets even messier when there are multiple phonebooks. active_call and call_status keep on changing as the numbers are dialled.


texts:
Background

I have a database with dozens of tables, some of which should have audit columns.

Problem

I'd like to avoid the drudgery of creating audit columns manually, and write the update and delete triggers once.

Code

There are several different pieces of code that must come together.

Audit Table

The following code creates an audit table and city table:

CREATE TABLE audit (
  created timestamp with time zone NOT NULL DEFAULT ('now'::text)::timestamp with time zone,
  deleted timestamp with time zone,
  updated timestamp with time zone
);

Example Table

A simple table to serve as an example of the problem:

CREATE TABLE city (
  name       text,
  population real,
  altitude   int
);

Parent Table Trigger

The following code creates a database trigger and a delete prevention rule on the audit table:

CREATE FUNCTION audit_delete() RETURNS trigger AS
$audit_delete$
BEGIN
  OLD.deleted := current_timestamp;
  RETURN NULL;
END;
$audit_delete$
LANGUAGE plpgsql;

CREATE TRIGGER audit_delete BEFORE DELETE ON audit
    FOR EACH ROW EXECUTE PROCEDURE audit_delete();

Adding Columns

Inheritance requires columns in the child table to match the parent table and must be created manually. Such as:

for $name in (SELECT list of tables); do
  ALTER TABLE $name ADD COLUMN
    created timestamp with time zone NOT NULL DEFAULT ('now'::text)::timestamp with time zone;
  ALTER TABLE $name ADD COLUMN
    deleted timestamp with time zone;
  ALTER TABLE $name ADD COLUMN
    updated timestamp with time zone;
done

There's probably a way to detect and auto-add the missing columns using the information schema.

Establish Inheritance Hierarchy

The following code applies the inheritance to the given table:

ALTER TABLE city INHERIT audit;

Insert Data

Add some data into the database:

INSERT INTO city ('Vancouver', 603502, 10 );
INSERT INTO city ('Seattle', 620887, 10 );
INSERT INTO city ('La Rinconada', 30000, 5100 );
INSERT INTO city ('Jericho', 20300, -260 );

Delete Data

Remove some data:

DELETE FROM city WHERE name = 'Jericho';

At this point, I was expecting the deleted column to contain the date for the value of "Jericho". Instead, the row as actually removed from the database.

The trigger for audit_delete() didn't fire.

Questions

What is the optimal (or usual) way to create a trigger that sets the deleted date on delete queries for all tables that inherit from the audit table (using PostgreSQL 9.2)?
(Optional) Is this a good use for triggers?

Thank you!

Related Links

http://projects.nocternity.net/index.py/en/psql-inheritance
http://www.postgresql.org/docs/current/static/tutorial-inheritance.html
https://stackoverflow.com/questions/8469116/how-can-i-make-a-trigger-to-use-inheritance-with-that-tables
http://tapoueh.org/blog/2010/11/24-dynamic-triggers-in-plpgsql.html
http://www.postgresql.org/docs/current/static/plpgsql-trigger.html
http://archives.postgresql.org/pgsql-general/2009-08/msg00026.php
http://andreas.scherbaum.la/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html


texts: PostgreSQL: Foreign key on string column

texts:
I'm currently in a process of redesigning a legacy database. One of the tables primary key is a varchar(254). I need to create a table relating to it.

So for example:

create table "Item"
(
    "Name" VARCHAR(254) PRIMARY KEY
);

CREATE TABLE "Uses" --new table
(
    id SERIAL PRIMARY KEY NOT NULL,
    "itemName" VARCHAR(254) NOT NULL,
    FOREIGN KEY ("itemName") REFERENCES "Item" ("Name")
);

I was wondering, if each row of Uses will have a reference(memory address) to the Item tables column - that way not copying the value around.

Or will it indeed copy that value, causing superfluous space loss.

Also would like a general opinion on using a VARCHAR as a primary and foreign key. Should maybe better alter the old database to give it a proper id?

Thank you very much.


texts: cross database way to force join strategies

texts:
[how to enforce the query to use certain join algorithm]

Knowing that there are several joining algorithms [Nested Loop; Sort Merge; Hash Join], when implementing a query , is there any technique to enforce the database to adapt certain Join algorithm ? Perhaps using some hints ? creating some indexes ?

Related Topic: Enforce Hash Join -- not that useful for me.

Adding more details:
  I am talking about DB2 V9.7, but I originally thought this would apply for other DB as well as they should share some common characteristic


texts: SSRS default URL not being referenced from Windows application

texts:
I have installed and configured SSRS and when I check the URL via HTTP it works fine (I haven't configured SSL).

When I try to run a report from the Windows application, I get the following error message:

  Unable to connect to the remote server

  No connection could be made because the target machine actively
  refused it xxx.xx.xxx.xx:443

But I am not trying to get to port 443, I am trying to get to the default port 80. I'm not sure why the windows application is trying to access port 443.

Additional Info:

The Windows application and SSRS is on the same server using the same network.
The server is part of a load balancer which has a SSL certificate.


texts: What is the expected performance impact when dropping a large table

texts:
I have a 100G+ innodb table that needs to be dropped in a production environment. I am trying to do this without interruption to service.

The table is no longer being written to, and needs to be dropped to free up space. My MySQL version is 5.1.52.

What is the expected impact to performance while this table is being dropped? I was thinking of slowly shrinking it by deleting rows and then optimizing. However, optimize makes a copy of the table so it will be just as disk IO intensive, if not more.


texts: Is there a "Full Containment" option for databases in SQL Server 2014?

texts:
In creating a new database on SQL Server 2014, the "Containment Type" dropdown in the create database dialog has only "None", and "Partial".  This kind of hints that there should be a "Full" option, but there isn't.

And, indeed, this article actually refers to both partially contained, and fully contained databases.

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

Is there some SQL Server configuration option that I didn't select when installing SQL Server?  Or is there really just no actual "Fully Contained" option?


texts:
Have 3 node cluster. RF=3. Table with gc_grace_seconds = 120 secs. max_hints duration as default at 3 hours.

inserted a row

nodetool flush on all the 3 nodes

bring down node 3

delete the inserted row

nodetool flush on node 1 and node 2.

hints directory on node 1 and node 2 show the hints files.

Now I bring up the node 3. the hints are sent to the node 3 and deleted entry is made on the node 3.

Why is this behaviour? Is'nt the hint supposed to be expired with the gc_grace_seconds over? hints should not be sent to node 3. Am I missing something here?

help appreciated


texts:
I am going through this page to try to understand how a geo replicated DB would fit into our RTO and RPO and I was surprised to see RTO of 1H.  I am even more confused as it says the RTO is equal to 1H.  How can that be equal, shouldn't it be less that an hours?

So does anyone know why it is so high?  Is it just because Microsoft
is being safe and in reality it is much better?
Secondly why is it equal ?


texts:
I have a T-SQL query with four inner joins, I executed the query in SSMS with Actual Execution Plan selected. When I review the generated plan I saw the warning icon on Sort operator.
What is the reason for this warning in Sort operator?

The Query Plan URL: https://www.brentozar.com/pastetheplan/?id=HJULL9H4N

I provided the screen shot of Execution Plan.

The Warning:


texts:
I had a strange issue today on a 3rd party vendor's SQL Server. This is a VM running SQL Server 2012 SP1. The min and max memory settings are set to 9GB. Task manager showed SQL Server was getting the 9GB. SQL Sentry shows over time the buffer pool was getting smaller until eventually we were not able to connect with SSMS. PLE was in the single digits.

The CPU was at 40% for a couple of days prior and I singled it down to Resource Monitor. This appears to be a symptom of the memory issue.

The vendor restarted the service and everything is back to normal.

I found this KB2845380 and think this might be related but I'm not sure. Has anyone run into something like this before? From what I've been told this has happened every month or so.


texts: Sudden PRIMARY KEY violation on IDENTITY column

texts:
I have a log table with an ID identity column. Everything works fine for years; then, yesterday, I see the following error in the logs:

  The statement has been terminated.
  Violation of PRIMARY KEY constraint 'PK__log__ID__3B40CD36'. Cannot insert duplicate key in object 'dbo.log'. The duplicate key value is (295992).

  INSERT INTO log (datum, zeit, benutzer, modul, prozedur, code, zeile, bez1, bez2, tech_info) VALUES ('20151126 00:00:00.000', '19000101 18:26:45.121', 'Customer', '', '', 'WShop-Trans', 0, '1 Datensätze für Tabelle adresse gesendet.', '', '')

I checked the IDENTITY seed, and it looks OK:

  Query: DBCC CHECKIDENT(log)

  Result: Checking identity information: current identity value '296021', current column value '296021'.
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  Query: SELECT MAX(ID) FROM log

  Result: 296021

There are no triggers on the table, and nobody is messing around with the seed values (I'm the one administering the database server, so I'm pretty sure about that).

So far, this is a one-time event, and I cannot reproduce it.

I looks like just an SQL Server glitch, but I'm curious: Is this a known bug, or is there any other plausible explanation for this? The SQL Server version is Microsoft SQL Server 2012 - 11.0.2100.60 (X64).

For completeness, here's the complete table script:

CREATE TABLE [log](
    [datum] [datetime] NULL,
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [zeit] [datetime] NULL,
    [benutzer] [varchar](255) NULL,
    [modul] [varchar](255) NULL,
    [prozedur] [varchar](255) NULL,
    [code] [varchar](255) NULL,
    [zeile] [int] NULL,
    [bez1] [text] NULL,
    [bez2] [text] NULL,
    [tech_info] [text] NULL,
    [pc_name] [varchar](255) NULL,
    [app_name] [varchar](255) NULL,
    [s_insert_user] [nvarchar](255) NULL,
    [s_insert_dat] [datetime] NULL,
    [s_update_user] [nvarchar](255) NULL,
    [s_update_dat] [datetime] NULL,
    [fs_mandant] [uniqueidentifier] NULL,
 CONSTRAINT [PK__log__ID__3B40CD36] PRIMARY KEY CLUSTERED ([ID] ASC)
)

CREATE NONCLUSTERED INDEX [code] ON [log] ([code] ASC)
CREATE NONCLUSTERED INDEX [datum_zeit] ON [log] ([datum] ASC, [zeit] ASC)
CREATE NONCLUSTERED INDEX [fs_mandant] ON [log] ([fs_mandant] ASC)
CREATE NONCLUSTERED INDEX [modul] ON [log] ([modul] ASC)


texts: The Database Session is automatically Getting Killed

texts:
I am running a java program which connects to the oracle db and retrieves some information. The program contains 12 threads, each thread interns execute sql select commands with multiple join conditions on the tables.

Some times the application runs fine and giving the exact result but sometimes it is not. Finally what we have found is the database session is getting killed.
But there are no scripts running at the db side to kill the idle sessions, no session time out parameter is set with the db profile for that particular user. No locks on the tables. And i have handled all the exceptions in front end application, but it is not throwing any exceptions. the program is keep on running even the session is killed.....
please suggest.............

Thanks
ABCReddy


texts: Distrubution1 Database Offline - I have no clue what this is

texts:
I have a SQL Server 2012 installation with a hosting provider that is an active/passive high-availability setup. (Exact version 11.0.5058)

We recently migrated to this server from a 2008 install in a different data center.

There is a database that now shows up in SSMS called "distribution1".  It appears to be offline.

I know there is a System Database called distribution, but I have no idea what this distribution1 database is.

My hosting provider said they couldn't provide any advice about it and to contact the developer and well... that's me.

This may be a very dumb question, but is it safe to assume that this database is not doing anything since it is offline anyway?


texts:
I created a database in oracle XE 11.2 that uses charset AL32UTF8 that I now want to export.

When I perform the export, the resulting data appears in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set.

The server uses AL32UTF8 character set (possible charset conversion).

Why is the export from the database in a different charset than the database itself is using?


texts: MySQL TIMESTAMP isn't behaving as advertised

texts:
I'm using MySQL 5.5.34. The 5.5 Documentation says I can create a table like this:

CREATE TABLE t1 (
  ts1 TIMESTAMP DEFAULT 0,
  ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP);

From the documentation:

  For t1, ts1 is NOT NULL and assigning it a value of NULL sets it to the current timestamp.

The documentation also says I can only assign CURRENT_TIMESTAMP as the default to a single TIMESTAMP field only. No multiple fields auto updating.

So, I tried this exact trick:

CREATE TABLE `users` (
  blah blah blah,
  `record_created` TIMESTAMP DEFAULT 0,
  `last_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP);

When I execute the CREATE command, I get this back:

ERROR 1067 (42000): Invalid default value for 'record_created'

and the table doesn't actually get created.

What am I missing?


texts: How to check if sysadmin logins have a master database as a default database in T-SQL?

texts:
I want to check whether all the sysadmin users in my sql server instance have a master database as their default database AND if not change their default database to master database. I tried the following, but it was not successful. Any help would be greatly appreciated.

DECLARE @cnt INT;
SELECT @cnt= COUNT(p.name)
FROM sys.server_principals p
JOIN sys.syslogins s ON p.sid = s.sid
WHERE p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
AND p.name NOT LIKE '##%'
AND p.default_database_name not in ('master')
AND s.sysadmin = 1

IF (@cnt = 0)
BEGIN
   exec master..xp_cmdshell 'echo '''all sysadmins have master database as their
   default database> C:\test\result.txt'''
END
ELSE
BEGIN
while @cnt > 0
BEGIN
    alter login p.name with default_database = 'master'
END
END
END

The error message I get is

  Msg 102, Level 15, State 1, Line 21 Incorrect syntax near '.'. Msg
  102, Level 15, State 1, Line 24 Incorrect syntax near 'END'


texts: How restore a Postgres database on AMAZON RDS when the file is in another server?

texts:
I'm around 2 days to simply restore my database to an AMAZON RDS server. The tutorial page from AMAZON is so bad that I can't do a simple task like this.
I have db_example.bak file on my local machine and in my other server and I want to simply restore this database on RDS from the amazon rds server.
Because of my database, I try to make this on the client-side but it will take the whole day to restore. I can connect with psql on AMAZON RDS but I don't know how I can import a file that doesn't exist there.
I don't know if this a noob question but I will try everything to make this simple thing done and I think my doubt can be the doubt of someone.
observation: I only find tutorial in SQL SERVER but not in POSTGRES


texts: Backup plan for MySQL NDB cluster databse not innoDB

texts:
I have a Database which will grow more than 250GB all data is in NDB engine(2 datanodes) and no other mysql engine used for data store.

What are the best approaches or plans for MySQL Cluster NDB database backup.
Is MySQL Enterprise Backup( part of MySQL Cluster CGE) or any other tool does backup for NDB?
Can a beginner like me do these backup of NDB easily or needs specialised skills?

Kind regards,


texts: How can I implement a sequence for each foreign key value?

texts:
In a PostgreSQL (9.3) database, I have a one-to-many relationship between Accounts and Customers, so within the Customers table, I have a globally-unique id for the primary key of each Customer, plus the corresponding Account id.  But I would also like to assign each one an Account-specific id - just a number starting at 1 for each Account's first Customer and incrementing for each additional Customer - simply as an easy way for users in an Account to reference a Customer in the UI.

I can think of two possible solutions:

Use the max() aggregate function to obtain the last Customer id used for the Account and increment it

Pros

Relatively simple (conceptually, at least)

Cons

Can result in an id being reused if the newest Customer is deleted
Need to handle concurrent inserts calculating the same id for the new records

Use a sequence

Pros

Specifically designed to handle incrementing the value
Remembers the last-used value, even if the corresponding record is deleted

Cons

Requires a separate sequence to be created for each new Account somehow (and then be deleted if/when the Account is deleted), otherwise each Account would end up with non-contiguous values in its Customer id sequence

UPDATE

To clarify the last point, some gaps in the sequence of ids are always going to occur; as soon as a Customer is deleted, for example, but those are to be expected.  Aside from that though, a user adding a couple of new Customers would generally expect them to have consecutive ids even if they were added days apart.

OTOH, using a single sequence for all Customers could almost certainly see regular, unexplained gaps introduced by the ids assigned to the Customers created for other Accounts in the meantime.

I'm not a Postgres expert, so I'm concerned about how robustly I could implement either of these, especially as I've struggled to find any examples to follow.

Is there a generally accepted way of doing this?


texts: Why does my SELECT query not return null values?

texts:
I have the following SQL script:
CREATE temporary table if not EXISTS the_values (
 key SERIAL,
 value INTEGER NULL
);

insert into the_values(value) values (null),(1),(null),(2),(3),(4),(5),(6),(10),(null),(null);

select *
from the_values
where value not in (1,2,3,4,5,6,10);

And I noticed that the query:
select *
from the_values
where value not in (1,2,3,4,5,6,10);

Does not return the rows having value NULL, and that caught my attention. Therefore, I want to know why that happens. I am interested more about the technical aspect of this phenomenon rather that the obvious solution:
select *
from the_values
where value not in (1,2,3,4,5,6,10)
   or value IS NULL;


texts: How can I determine the last synchronization time for a SQL Server replication subscription?

texts:
I'm using SQL Server replication to mirror a few tables from an on-premises SQL Server instance to an Azure SQL database. We had an issue where the replication encountered an error, and we incorrectly assumed that the data in the read-only replica was current. I'm trying to setup an alert system that will notify me if the replica database is more than 30 minutes out of sync with the distributor.

In Replication Monitor, I can see the "Last Synchronization", but that timestamp doesn't seem to be in any of the dbo.MS* system tables on Azure SQL. I'm looking to get this information from the subscriber instead of the distributor.


texts: How to query which users are using which certificates in SQL Server?

texts:
I would like to know which users a certificate is used by.  I tried to do this query but got no results:
SELECT
    master.sys.sql_logins.name,
    sys.certificates.name
FROM
    master.sys.sql_logins
INNER JOIN
    sys.certificates
    ON master.sys.sql_logins.sid = sys.certificates.sid

Does anyone know how to do this?
Any help would be greatly appreciated.


texts:
I am writing a web site that uses SQL Azure heavily. However, it is painfully slow.

Is there an easy way to profile the live SQL Azure instance?


texts: Slow remote SELECT statement due to long "client processing time", but fast locally

texts:
While connected to our production server (SQL Server 2008, very powerful machine), this SELECT statement takes 2 seconds, spitting back all fields (4 MB of data in total).

SELECT TOP (30000) *
FROM person
WITH(NOLOCK);

From any other box on the same network (connecting using SQL authentication or Windows Authentication), the same query takes 1 minute, 8 seconds.

I am testing with this very simple statement to illustrate that it's not an indexing problem or query-related problem.  (We have performance issues with all queries at the moment...)

The rows come in chunks, and not all at once.  I get my first rows instantly, and then wait for over 1 minute for the batches of rows to come in.

Here are the Client Statistics of the query, when it is ran from the remote box:

Query Profile Statistics
  Number of INSERT, DELETE and UPDATE statements 0
  Rows affected by INSERT, DELETE, or UPDATE statements 0
  Number of SELECT statements  2
  Rows returned by SELECT statements 30001
  Number of transactions 0

Network Statistics
  Number of server roundtrips 3
  TDS packets sent from client        3
  TDS packets received from server 1216
  Bytes sent from client         266
  Bytes received from server 4019800

Time Statistics
  Client processing time 72441 ms (72 seconds)
  Total execution time   72441 ms
  Wait time on server replies 0

We can see that the "Client Processing Time" is equal to the total execution time.

Does anyone know what steps I can take to diagnose why the transfer of the actual data is taking a long time?

Is there an SQL configuration parameter that restricts or limits data transfer speed between machines?


texts: I need to run a procedure that goes through all schemas tables in MySQL server

texts:
I am new in creating procedures ,Actually I need to run a procedure that will run on all schemas tables in my server when am calling the procedure it gives me this error

  ERROR 1046 (3D000): No database selected

DELIMITER $$
CREATE PROCEDURE SET_UTF_ALL_TABLES()
BEGIN

DECLARE done INT DEFAULT FALSE;
DECLARE a CHAR(150);
DECLARE _counter INT DEFAULT 0;
DECLARE row_number INT;

DECLARE cur1 CURSOR FOR SELECT TABLE_NAME from information_schema.tables
where TABLE_SCHEMA IN ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4') ;

SELECT COUNT(*) from information_schema.tables where TABLE_SCHEMA IN
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4') INTO row_number;

OPEN cur1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

read_loop: LOOP
   FETCH cur1 INTO a;
IF done THEN
    LEAVE read_loop;
END IF;
IF _counter < row_number THEN
    ALTER TABLE a CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    SET _counter = _counter + 1;
END IF;
END LOOP;

CLOSE cur1;
END;
DELIMITER;


texts: Does barman require continuous execution of receive-wal on a slot for streaming replication to work?

texts:
I am new to barman and am trying to implement barman on my server. I have followed each and every step as mentioned in the barman docs. Everything works fine till the receive-wal command in running in foreground. Once it stops the wal files from my main db get archived in done state in /var/lib/postgresql/9.6/main/pg_xlog/archive_status but do not get deleted. This also happens when I take a complete backup onto barman and stop barman server. The replication slot used for streaming replication is still active in the main db.

Is it necessary to continuously run receive-wal command on the barman server?
Should I always remove the replication slot from the main db once the backup is complete?
What happens when a pg_restore command is run on the main db being replicated? ( In my case the pg_xlog files are being created at an insane rate thus creating large number of checkpoints. Eventually wal files only get created but are not deleted. What exactly should be done in this scenario? )


texts:
I am building a booking app and part of this involves reserving a courtesy car.

Courtesy cars can be booked out for a period, e.g.

2nd April - 11 April
12 April - 19 April
etc...

These dates are stored in a courtesycarbooking table.

What I want to write is a query which can query the courtesycar and courtesycarbooking tables (they are related) for free dates. Free dates are those which are NOT in the booking table.

The user will specify, in a javascript widget, a start and end date for the courtesy car, which will be sent to the sql server.

How could/should a query like this be written?