texts: How to keep smaller transaction logs files while rebuilding index of very large table

texts:
I need all the help that I can get to get this index rebuild successful. Especially an expert advise on transaction log management.

Background

The target database is a DW database hosting 3 big tables in clustered columnstore index (CCIX). The biggest table called Analog. It holds ~37 billion rows and ~600 GB of high compressed data. Based on our initial estimate using smaller table, a 150 GB CCIX table could expand to 5 TB, so we provisioned additional 29 TB for this rebuild.

SQL Server Enterprise 2016
Recovery mode = Simple

Why do we have to do this?

In the first version of our ETL service, the data are not consolidated and sorted first before they are loaded to CCIX. This results to non-optimal use of CCIX as many row segments are not fully filled before they are compressed. An optimal segment must have compressed 1,0485,76 rows and must be sorted in Time so time-based queries can get the most row-group elimination in sql server and less segments to process. We’re learn more CCIX as we go and as more documentatons are available.

Excerpt of my alignment is here
https://drive.google.com/file/d/0BzGLNskaj70UQUtZYW9CZF9iUUk/view?usp=sharing

The new ETL guarantees that data are consolidated and sorted in time before they are loaded to CCIX. So future loads will be sorted, we are concern here with existing data loaded during First Load.

For full case description, please read here.
https://drive.google.com/open?id=0BzGLNskaj70URmZURlVDWVNYd2M

This is our second attempt and I can see the log file is still piling up despite the new script having partition-based index rebuild. We need to keep this in check.

My questions are:

I already executed the 2nd attempt (in progress), is it possible to check which partitions are already processed and sorted? I have OFFLINE = ON in the rebuild script.
How can we manage the size of transaction log while a partion-based index rebuild is in progress? We need to keep this in check and regularly truncated every partition if possible.
The index rebuild failed initially due to insufficient space in log file. We added a new log file and use a separate disk. But how we make make sure this wont happen again in this second run? Will the partition-based rebuild sufficient to guarantee we can rebuild successfully?

Appreciate your help.

Rebuild scripts by partition (2nd attempt) – IN PROGRESS

Elapsed: 17 hrs, 34 mins

--create a new clustered row index (CRI)
CREATE CLUSTERED INDEX [Analog_ColumnStoreIndex]
ON [dbo].[Analog]
(
    [LogTime] ASC,
    [CTDIID] ASC,
    [WindFarmId] ASC,
    [StationId] ASC
)
WITH (
    DROP_EXISTING = ON,
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON,
    ONLINE = OFF
)
ON [AnalogMonthlyPScheme]([LogTime])
GO


texts:
I had a recent DBA.SE question that involved dates that were used as the x-axis in a graph: Interpolate dates along a line.

It was centred around a road_condition table:

COND_ID ROAD_ID   COND_DATE  CONDITION
------- -------   --------   ---------
      1     100   84-11-01          18
      2     100   09-01-01           6
      3     100   12-06-19           4
      4     100   15-04-29           4

      5     200   92-04-29          20
      6     200   17-04-05           3

Note: I consider cond_date to be the X-axis, and condition to be the Y-axis.

In my an attempt to answer my own question, I tried to do things like use cond_date as X1 in a calculation:

y1 - ((y2-y1)/(x2-x1)) * x1 as y_intercept

However, obviously I can't just use a date in a calculation that involves multiplication or division; dates are not traditional numbers.

As a work around, I stripped the date of its month and day: extract(year from cond_date) and performed the calculations from there on the resulting integer (the year). However, by doing this, I of course lose valuable precision in the number. This compromises the output of the calculation.

How can I multiply and divide dates, without losing precision?


texts: Age range on age range in WHERE to heavy - POSTGRES

texts:
I have 2 columns in the database for events.

agelow (int)
agehigh (int)

How it's structured:

agehigh is being used for "and under" which is represented by 0 in the column
the high age is placed in the agelow column
The "and over" is represented by 99 and is not a problem since it has the agehigh and the agelow in the right columns.
all ages are represented with 99 in both columns.

I am trying to write a WHERE that can pull all event ages if no ages are passed in but also need to be able to pull events just for one age passed in or for an age range passed in. The age range is making this a big headache.

Example

If I pass in agelow 5 and agehigh 15, I would need to grab all events that are

available for anyone from 5 up to 15 which includes the "5 and under", "5 and up", "15 and under" and all ages.

Here is the WHERE part:

WHERE
((events.age_low >= agelow AND events.age_high <= agehigh) OR
 (events.age_low <= agelow AND events.age_high >= agehigh) OR
 (events.age_low <= agelow /* LOW (99 for all) */ AND events.age_high >= agelow /* LOW (99 for all) */) OR
 (events.age_low <= agelow /* LOW (99 for all) */ AND events.age_high = 99 /* DEFAULT 99 */) OR
 (events.age_low >= agelow /* LOW (99 for all) */ AND events.age_high = 0 /* DEFAULT 0 */) OR
 (events.age_high >= agehigh /* HIGH (0 for all) */ AND events.age_low <= agehigh /* HIGH (0 for all) */) OR
 (events.age_low >= agehigh /* HIGH (0 for all) */ AND events.age_high = 0 /* DEFAULT 0 */) OR
 (events.age_low <= agehigh /* HIGH (0 for all) */ AND events.age_high = 99 /* DEFAULT 99 */) OR
 (events.age_low = 99 AND events.age_high = 99));

Here is a small selection of the age columns:

CREATE TABLE events(age_low,age_high)
AS VALUES
  (  99 ,  99 ),
  (   3 ,  12 ),
  (   1 ,   6 ),
  (   5 ,   0 ),
  (  99 ,  99 ),
  (  99 ,  99 ),
  (  99 ,  99 ),
  (   5 ,  11 ),
  (   5 ,   0 ),   -- this is 5 and below
  (  16 ,  99 ),   -- this is 16 and up
  (   1 ,   4 ),
  (  13 ,  18 ),
  (  99 ,  99 ),   -- this is all ages
  (   5 ,   0 ),
  (  99 ,  99 );


texts:
Master A mysql 8
Master B mysql 5.7
Master C mysql 5.7
Slave mysql 8

i create 4 channels on my slave and i can not restore backup from master.

[root@CentOS-76-64-minimal hex]# gunzip < db.gz | mysql -u root -p
Enter password:
ERROR 3079 (HY000) at line 22: Multiple channels exist on the slave. Please provide channel name as an argument.


texts: Can a table belong to multiple tablespaces?


texts: What is the best option to easily fix fragmented heap indexes?

texts:
I have SQL Server 2008 R2 SP2 and also SQL Server 2012 SP2 on these servers.

I have a database in which there is a lot of heap indexes which are more than 90% fragmented.

What is the best option to easily fix these heap indexes?


texts: SQL Server 2005 - 2 sets of backups - 2 maintenance plans?

texts:
I did my best to search for an answer to this question and couldn't find a scenario quite like this.

I'm a fairly new DBA in the sense that I haven't had a ton of training, I basically took the job over when a co-worker left.

This question pertains to our server that's running SQL Server 2005.

Recently I've been trying to make sure that our data is as safe as possible and thus want to have two backup sets in two different places.  I currently have one set of plans doing a full backup, system DB backup and hourly transaction log backups to one server and then another set of plans doing the same thing but storing them on another server.  The transaction log portions of the plans are also staggered by 30 minutes so that in the event I need to restore I can choose the most recent.

Is this the proper way to go about doing this?  I've read that backup striping is usually done via a backup script and it sounds like the maintenance plan in 2005 isn't capable of doing this. Is that the case?  Can anyone say if my method is usable or could this create problems in the future?

Thanks for your help.


texts:
Total noob at database administration here, excuse my ignorance.

I have a server running a Rails application that's using PostgreSQL. The database is saved on drive /z/, which seems to have filled up. I want to put a higher capacity drive and shift the database to that. How should I go about doing this? Should I copy the data folder from drive /z/ to the new drive and then change the config files (could someone point me to what config changes I need to make?)

Or could I copy the data folder to the new hard disk, and then mount the disk as /z/ and then I'll need no config changes?

Or is there some helper commands in postgres that provide a functionality to migrate between disks?

I'm running Ubuntu 12.04.5 LTS. Output of version():

  PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc
  (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit


texts:
this is my query :

SELECT Count(last_upd)
FROM S_CONTACT
WHERE last_upd_by = '0-1' AND LAST_NAME <> 'Wait' AND last_upd  +  1/24 > SYSDATE - (1 / (24 * 60));

It takes 84 seconds to return me this result :

COUNT(LAST_UPD)
---------------
43

I don't understand why it's too much slow for only 43 returned row...

This is the execution plan :

Can anyone help me? this is the V$STATNAME

active txn count during cleanout    1
bytes received via SQL*Net from client  497
bytes sent via SQL*Net to client    14803
calls to get snapshot scn: kcmgss   23
calls to kcmgas 45
cleanout - number of ktugct calls   12
cleanouts and rollbacks - consistent read gets  2
cleanouts only - consistent read gets   10
commit txn count during cleanout    11
consistent changes  450
consistent gets 1458915
consistent gets - examination   458
consistent gets from cache  1458915
CPU used by this session    4485
CPU used when call started  4493
CR blocks created   44
current blocks converted for CR 1
cursor authentications  1
data blocks consistent reads - undo records applied 59
db block changes    12
DB time 8383
dirty buffers inspected 31
enqueue releases    1
enqueue requests    1
execute count   2
free buffer inspected   1442014
free buffer requested   1436612
heap block compress 12
hot buffers moved to head of LRU    25764
immediate (CR) block cleanout applications  12
no work - consistent read gets  1458380
opened cursors cumulative   2
OS Involuntary context switches 18069
OS Maximum resident set size    1956
OS Page faults  4
OS Page reclaims    410
OS System time used 1868
OS User time used   2625
OS Voluntary context switches   63594
parse count (hard)  1
parse count (total) 2
physical read bytes 11768365056
physical read IO requests   55724
physical read total bytes   11768365056
physical read total IO requests 55724
physical read total multi block requests    54675
physical reads  1436568
physical reads cache    1436568
physical reads cache prefetch   1380844
prefetched blocks aged out before use   8
recursive calls 1
redo entries    12
redo size   1272
redo subscn max counts  8
rollbacks only - consistent read gets   40
session logical reads   1458915
session pga memory  -393216
session pga memory max  262144
session uga memory max  299256
shared hash latch upgrades - no wait    4
sorts (memory)  2
sorts (rows)    760
SQL*Net roundtrips to/from client   9
table scan blocks gotten    1458432
table scan rows gotten  16656961
table scans (long tables)   1
transaction tables consistent read rollbacks    4
transaction tables consistent reads - undo records applied  391
user calls  11
user I/O wait time  6172
workarea executions - optimal   5

I'm using Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bits

Thank you


texts: Recreate existing database with a different charset

texts:
I want to change the character set of an existing database from none to utf8,
it seems that the safest way is to create a new one then pump data to it.

I have tried two methods, both gave me the same error:

Create a new database with utf8 charscter set, then create a simple stored procedure to pump data via "execute block on external datasource"
use fbclone with this command line

fbclone -l fbembed.dll -v -s source.gdb -t destination.gdb -u SYSDBA -p masterkey -tc UTF8 -wc UTF8

Both gave me a malformed string error, and most of the rows were not copied to the destination database.

Example verbose error

Incompatible column/host variable data type
GDS Code: 335544569 - SQL Code: -303 - Error Code: 249
fields values ---
VANUM = 244458
RUBNUM = 5054
VALEUR = Absence de germes pathogènes.
ATBANA =
DATEMODIF = 16/05/2018


texts: Find Multiple Finished Product code via 1 Raw Material Code

texts:
On the SQL server in work we have multiple part codes which start with either "M" or "P" codes, once these M or P codes are used throughout the manufacturing process these are then assigned an "A" code of which there can be multiple "A" codes which are finally sold as "R" codes.

Therefore I'm looking to enter a specific M-code for example such as M1655 which links directly to the R-code without having to manually search by each A-code.

For example, the initial search of M1655 results in A02748.  I then need to search for A02748 which then leads on to A02749 then on to R3700, R3856 and R3857

I'm just not sure how I'd go about sorting that, there's a small selection of data below

Resource    PM Name   PM Description
M1655       A02748    HONEY RST SALMON FROZEN
A02748      A02749    HONEY RST SALMON DEFROSTED
A02749      R3770     LOCHMUIR SALMON PASTA
A02749      R3856     CAFE HONEY RST SALMON NICOISE
A02749      R3857     BFY HOT SALMON & SUPERGREEN

Current SQL query details to get this info is:

SELECT pdResc.CalcValResource AS [Consumed Resource], pdPM.PM AS [PM Name], pdBalConsRescDt.Shrinkage,
        pdPM.Description AS [PM Description], fdBasResc.CatCodesCode2 AS [Resource BUSINESS], pdPM.PMSite AS [Site Name],
        fdBasResc_1.CatCodesCode2 AS [PM BUSINESS]
    FROM ERP_STD_CALE.dbo.fdBasResc AS fdBasResc,
        ERP_STD_CALE.dbo.fdBasResc AS fdBasResc_1,
        ERP_STD_CALE.dbo.pdBalConsRescDt AS pdBalConsRescDt,
        ERP_STD_CALE.dbo.pdPM AS pdPM,
        ERP_STD_CALE.dbo.pdResc AS pdResc,
        ERP_STD_CALE.dbo.pdStage AS pdStage
    WHERE pdPM.ObjectID = pdStage.ParentObjectID
        AND pdResc.ParentObjectID = pdStage.ObjectID
        AND pdResc.RescDtlObjectID = pdBalConsRescDt.ObjectID
        AND pdResc.CalcValRescOID = fdBasResc.ObjectID
        AND pdPM.PPRObjectID = fdBasResc_1.ObjectID
        AND (
              ( pdStage.ParentClassID = 10353 )
              AND ( pdStage.CollectionID = 1 )
              AND ( pdResc.ParentClassID = 10354 )
              AND ( pdResc.CollectionID = 1 )
              AND ( pdPM.PMInstType = 0 )
              AND ( pdPM.PMSite = '1000' )
              AND ( pdPM.AvailforSched = 1 )
            )
    ORDER BY fdBasResc.CatCodesCode2, pdResc.CalcValResource;


texts: How to create a unique column in SQL Azure Federation

texts:
I need to create two columns in a federated table where ID is a primary key and EMAIL_ADDRESS can never contain duplicate values. Think of it like a user being able to register only one email address at a time, and each user has a unique ID.

CREATE TABLE dbo.PERSON
(
    ID uniqueidentifier PRIMARY KEY NOT NULL
    , EMAIL_ADDRESS nvarchar(256) NOT NULL
    CONSTRAINT [PK_C1] UNIQUE CLUSTERED
    (
    ID ASC,
    EMAIL_ADDRESS ASC
    )
) FEDERATED ON ([dist] = ID);
GO

CREATE UNIQUE INDEX users_email_address
    ON PERSON(ID, EMAIL_ADDRESS);
GO

This will allow me to create a table, but I can still save duplicate values under EMAIL_ADDRESS.

How can I prevent duplicate email address entries?


texts:
I have a basic doubt about logical data independence. I read that in logical independence, we can remove attributes from relation without affecting external schema.

How can the removal of attribute not affect external schema? Suppose we have a student relation with marks attribute and if we remove marks attribute, external view will also be affected right?


texts: what is the best practice when making changes to the the application and database?

texts:
I need to make changes to an application which utilizes a database that is replicated across locations. I was wondering what the best practice is when working with databases and making changes to the database.

I will be backing up the database to dev environment and making changes to both the application and the database. Then when testing the application and the database I will likely make changes to the records. In that case, would it make sense run the t-sqls that I applied in the dev to the production database?


texts:
We are getting continuously logs in mysql error logs

2020-07-29T07:46:56.534666Z 111536 [Note] Aborted connection 111536 to db: '' user: '' host: '' (Got an error reading communication packets)

we are using the following
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| have_statement_timeout      | YES      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | ON       |
| interactive_timeout         | 1800     |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 1800     |
| net_write_timeout           | 1800     |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 60       |
| thread_pool_idle_timeout    | 60       |
| wait_timeout                | 1800     |
+-----------------------------+----------+

we are using percona MySQL server 5.7.30-33
let us know what we are missing or what we can do to resolve the issue.
I have referred some online topics with same errors but nothing resolved the issue
Thanks


texts: Table metadata auto_increment value for table modification

texts:
if table X is modified(DELETE, INSERT, UPDATE) then
    table X's metadata value should get incremented.

I can write trigger for the same. But inbuilt solution will be better.

This way I can stop issuing meaningless SELECT queries on the large table, which will give same result as previous.

Server side program can tell clients that nothing changed on server. No need to query.

In other words I want to get version number of table, which increments after every modification.
I know UPDATE_TIME but for some engines, this value is NULL.


texts: Distribute values equally in count and sum to a set of users

texts:
I have a table with 100 float values (say AMOUNT) that need to be equally divided among 5 users (say UID 1 to 5). The distribution must be such that each user gets approximately the same total count and value of AMOUNT.

Say, I have a sample table with 20 Amounts:

164548.65, 148410.72, 131395.33, 130219.97, 128593.28,
124539.92, 103958.45, 103671.87, 100210.36,  99645.42,
 98848.25,  97764.84,  97577.03,  90067.98,  87838.22,
 86730.85,  85508.00,  83481.78,  82886.95,  78588.79

This needs to be divided among 5 codes (numbered 1-5) in such a manner that each code gets 4 Amounts with a net worth of approx 424,000.

I am using SQL Server.

The Amounts are Linked to Invoice numbers. So the table has Invoice_Number,Amount. I need to add a column where the USER_ID (1-4) can be plugged in. So if there are 20 Invoices and 4 users, each user gets assigned with 5 Invoices and the sum of Invoice Amounts for each user must approximately be the same.


texts: phpmyadmin not working after mysql update

texts:
i followed this guide to update my mysql form 5.1 to 5.5. After lots and lots of command and headache, i was able to upgrade mysql. i checked the version via terminal.

But now there is another problem. phpmyadmin is not working. It says

Forbidden

You don't have permission to access /phpmyadmin/ on this server.

Apache/2.2.14 (Ubuntu) Server at localhost Port 80

i searched everywher and tried various solutions like including Include /etc/phpmyadmin/apache.conf in apache2.conf file and restared apache(it started showing 404 error). But it's still not working.

i remember the terminal was showing phpmyadmin is broken or not fully installed, while i was trying various solutions.

What should i do now? repair, reinstall, format??

Edit: Also now when i open terminal(Ctrl + Alt +t), it shows various errors:

Command 'uname' is available in '/bin/uname'
The command could not be located because '/bin' is not included in the PATH environment variable.
uname: command not found
bash: [: !=: unary operator expected
bash: [: too many arguments
bash: [: too many arguments
bash: [: too many arguments
bash: [: =: unary operator expected
bash: [: too many arguments
bash: [: too many arguments
bash: [: =: unary operator expected
bash: [: =: unary operator expected
bash: [: =: unary operator expected
bash: [: =: unary operator expected
bash: [: =: unary operator expected
Command 'sed' is available in '/bin/sed'
The command could not be located because '/bin' is not included in the PATH environment variable.
sed: command not found
bash: [: too many arguments
bash: [: =: unary operator expected
bash: [: too many arguments
bash: [: too many arguments
bash: [: =: unary operator expected
bash: [: =: unary operator expected
bash: [: =: unary operator expected
bash: [: =: unary operator expected
ubc-7@ubc-7-desktop:~$


texts: Is Logical Fragmentation Meaningful in the age of huge SANs?

texts:
So, every time I read that a data file shrink will cause fragmentation or a developer rave on that the 95% frag he sees is the cause of all life's misery, I wonder what the problem really is. We no longer dedicate a single disk for anything and instead carve LUNs out of auto-tiered pools of EFD, 15k, 10k spindles; however, I have never seen any test considering whether logical fragmentation means anything performance wise once we ignore the one disk test model.

Perhaps the bpool performance will suck more, but if I take as an axiom that my IO subsystem is still returning what I need in less than 20ms and further assume that every index I have is fragmented to hell, does it really matter? That is, in the world of terabyte indexes, is it cheaper to keep the fragmentation than to remedy it or is it even cheaper just to use a heap and build non-clustered indexes on it? I guess more study is needed; but I just wanted to throw the idea out here for discussion.


texts: Created Check Constraint WITH NOCHECK - still trusted

texts:
I needed to alter the conditions of existing constraint - so I dropped it and recreated with the new conditions.

This failed, because there were 800k+ rows, that didn't meet the condition. That strikes me as odd, because

SELECT * FROM sys.[check_constraints] cc

showed is_not_trusted as 0

Anyway, I recreated the constraint again, this time with hint WITH NOCHECK
This time it passed and I expected is_not_trusted to be 1, but it is still trusted.

Can someone explain it to me?

My code:

ALTER TABLE dbo.MyTable
DROP CONSTRAINT CK_MyTable;
GO

ALTER TABLE dbo.MyTable WITH NOCHECK
ADD CONSTRAINT CK_MyTable
CHECK
(
    -- 7 conditions, mainly checking null and not null

)


texts:
There a are huge number of in-active sessions persisting in our database.How can we clean these in-active sessions?

Database - Oracle 10g R2 with ASM

DB Size - 5TB
SGA - 78GB
PGA - 10GB
Maximum Sessions allowed - 1200
Average number of In-Active sessions - 150
Maximum In-Active Sessions - 900


texts:
Some times ago I have done backup my  MS SQL Server database and then I restore it (on other computer and server). Today I would make just like two weeks ago. But when I choose file to restore from devise I see error:

TITLE: Microsoft SQL Server Management Studio

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

ADDITIONAL INFORMATION:

RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3013)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500&EvtSrc=MSSQLServer&EvtID=3013&LinkId=20476

BUTTONS:

OK

I am really confused, because I done everything just like two weeks ago. Of course, I searched in Internet how to solve the problem but it didn't help me.


texts: Upgrading SQL Server 2012 from SP2 to SP3

texts:
We need to upgrade our SQL Server 2012 from Service Pack 2 to Service Pack 3. We are no database experts and would like to have some info regarding this.
We are going to be doing this upgrade on the lower environments first.
Is there anything we need to specially take care of while or before doing this upgrade. We have checked for the system requirements and we are fine there.
We plan to take a database backup and just run the installation for upgrade and for the latest patches for SP3. Is that all?
Please let me know if we are missing something in here.

Thanks


texts:
In our application we need to insert a lot of historical data that will be consumed later by some mobile devices.
These data is organized by a numeric incremental attribute that the device will use to download only the 'offset' it needs. So, if in the last connection a device downloaded records from 0 to 100, later it will download records starting 101 to last record available.
Let's call Producer the component that will process and populate (insert) data on these historical tables. For each historical table that the producer will populate, it will:

Open a transaction
Insert data
Update last available record for that table
Close the transaction

There is a lot of producers (about 15, each one with 8 threads) running on the database concurrently.
Now, let's say that the Consumer is the component responsible for reading the information saved by the producers. All it will do is check, table by table, if there is some record between a required offset a return it to the device.
When a device requests data, it will:

Create a transaction
Iterate each table querying it for new records based on the offset requested.
Close the transaction

Actually, the project runs a AWS db.r5.2xlarge (8 vCPU, 64 GB Ram) Oracle RDS Instance, without any problems.
Now, our team was requested to change from Oracle to PostgreSQL 12. So, we created an PostgreSQL RDS Instance with same size and start some tests.
The producers are working pretty well! The insertions are fast, memory and CPU consumptions under control, no issues.
BUT, when we start the Consumers, everything changes...
The CPU goes to 100%, Freeable memory drops to zero. Swap increases, the system freezes and "out of shared memory" with hint "YOU MIGHT NEED TO INCREASE MAX_LOCKS_PER_TRANSACTION" comes in place.
Apparently, the execution plan of the queries is OK! We are using prepared statements. Statistics are OK also. No sessions being locked by other sessions... Records are being returned as Cursors. Exactly as with Oracle.
Looking in pgAdmin, we a lot of sessions wait for "LWLock: lock_manager", but we can't understand why...
We tried the following approaches, with no success:

Increase MAX_LOCKS_PER_TRANSACTION (this eliminated the out_of_shared_memory error, but other behavior are the same)
Disable parallel queries
Increase work_mem
Reduce max_connections
Increase shared buffers
Increase wal_size

We can't figure out why this is happening. The PGSQL database load is smaller than the load Oracle supports on production. Does anybody have any hint that we could try?
Additional info:

PGSQL tables is currently partitioned, but partition prunning seems to be ok.
Multiple Producers and Consumers writes/reads the same table at the same time, but a consumer will never try to consume a record that before producer explicitly marks as available.
We read that out_of_shared_memory occurs when tables are being dropped and recreated within a long transaction... This is not our case.
PostgreSQL Version: PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

Attached there are some pictures that we see when the problem happens.

Thanks a lot!


texts:
Of the systems with which I am familiar, I have noticed that SQL Server's native database backup format is binary and proprietary. PostgreSQL and MySQL allow for a simpler approach, dumping the schemata and data. This leads to an interesting question.

  What are the primary architectural differences between the backup formats of some of the more common RDBMS' and what advantage do they serve?

It seems to me that the PostgreSQL and MySQL dump-style implementations would allow for one to open the backup in a text editor and get a sense for the data being restored.


texts: SQL SERVER 2016, Restore database get 15517

texts:
I got "15517" error When I tried to restore database when the process reached 100%

I googled several hours,the recommended solution is to change the database owner , I did so
ALTER AUTHORIZATION ON DATABASE::[joy] TO [sa]

I checked the database owner has been changed successfully.

Then I restored again, but I still got the same previous "15517" error.
Is there anyone can help me?


texts: All queries waiting for query cache lock

texts:
I'm managing a e-commerce site which uses a popular online shopping cart software running on MySQL 5.6.  Yesterday I noticed that SHOW PROCESSLIST reports that 990 of 1000 queries are waiting for a query cache lock:

mysql> show processlist;
+----------+------------+---------------------+-------------+---------+------+--------------------------------+------------------------------------------------------------------------------------------------------+
| Id       | User       | Host                | db          | Command | Time | State                          | Info                                                                                |
+----------+------------+---------------------+-------------+---------+------+--------------------------------+------------------------------------------------------------------------------------------------------+
| 12224065 | sqluser    | 10.13.13.13:21716   | mydatabase  | Query   |    0 | Waiting for query cache lock   | SELECT `data` FROM mytable WHERE `foo` = 'bar'                                                       |
(...)

However, the Time is always 0 and the process Ids change all the time. My understanding is that the query waits for a table lock but the lock is released after less than one second.

Is this a normal/acceptable behaviour or could it be worth to do some fine tuning on the query cache, perhaps removing it completely?


texts: Does .bak file also contain backup of ldf file?

texts:
I am running MS SQL Server, which is holding mdf and ldf files of sizes 110 GB and 245 GB respectively.

My question is, if I take .bak backup of said database, will it result in 355 GB (110+245) sized backup file, or does it only contain mdf backup?

I need to know this since I don't have this much of space on my hard disk.

I'm using SQL 2008 R2 standard edition


texts: Manual Failover with AlwaysOn availability group

texts:
We are planning to upgrade our SQL 2012 to SQL 2014. We currently use a mirror for our production databases but are planning to move to availability groups. We are going to be running 2 SQL Servers on our primary site and a single asynchronous replica at DR.

Our link to DR is sometimes quite patchy and so we don't want to rely on DR to have a quorum, which is also the advice from Microsoft: https://msdn.microsoft.com/en-us/library/hh270280.aspx. To provide an odd number of votes we are planning to have a node and file share majority (2 nodes + 1 file share at the primary site = 3 votes).

A couple of times a year we need to fail over to DR, either to test or because of maintenance (Electrical/Connectivity/etc) work at our primary site.

To failover to DR, we can set the DR replica to be synchronous, wait for it to catch up and then failover either using a script or the failover wizard. I've setup a test using 4 servers in Hyper-v (3x SQL + 1x AD). Once I do the failover to DR and then shutdown the SQL Servers the cluster goes offline.

I notice that the same msdn article I mentioned above says

  • Re-assess vote assignments post-failover. You do not want to fail
  over into a cluster configuration that does not support a healthy
  quorum.

What would be the best adjustments to make to maintain quorum after a planned failover to DR and the Primary site being completely offline (including the 2 primary SQL nodes and file share)?


texts: Convert string to number from xml at server side

texts:
I am having issues converting numbers stores as character (with . as decimal, sometimes are null) to number from xml on server side. Query is part of package. When I run the query below on my computer, it works. However when I call the procedure from UNIX, it fails and I am getting following error message:

Errors:
ORA-06512: at "DTWH.PKG_GPA", line 246

----- PL/SQL Call Stack ----- object line object handle number name 0x16967d328 390 package body DTWH.PKG_GPA 0x90bf2568 3 anonymous block

ORA-01722: invalid number

SELECT cast(nvl(trim(replace(xmltype(t.sce_msg).extract('//RoofInsulationCSV/Total/text()', 'xmlns="http://INT010.GPA.Schemas.External.GPA_AllFile')
                      .getstringval(),'.',',')),0) AS NUMBER(7,2)) AS amount
FROM        ta_main.serv_cons_event t
INNER JOIN  ta_main.event e
      ON    t.sce_evt_id_s = e.evt_id_s
WHERE       e.evt_typ_id_c IN ('INV', 'ACC')

I tried various approaches like to_number, replace with regex or whatever I found. Always work with me, never from UNIX.

Any idea, what I am missing?


texts:
I want to import one of my schema "vvs1" to r&d database with content=metdata_only. expdp works perfect but when i am going to import on r&d server it takes tables original size.

for example:-
I have 1 table vvs.xyz with size of 10 GB. i am taking structure backup only. now import on r&d server it creates a table structure with 10 GB size.
i want only table with default size while created.

expdp syntax:

expdp vvs/1 full=Y CONTENT=METADATA_ONLY directory=DATAPUMP_DIR dumpfile=full.dmp logfile=full.log KEEP_MASTER=Y

impdp syntax:

impdp vvs/1  CONTENT=METADATA_ONLY directory=DATAPUMP_DIR dumpfile=full.dmp logfile=full_impdp.log schemas=vvs

main database :-Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

r&d database:-
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


texts:
I have 2 AWS RDS databases (A & B) and need to create a datawarehouse with star scheme and slowly changing dimensions within AWS.

From a MS background it makes sense to create a SSIS package to run as a SQL job, which can collate the A & B databases and create dimensions and facts.

AWS RDS does not have SSIS support and I would therefore need to run an EC2 instance with SQL Server installed which then ran the SSIS package.

My questions is - is there any better way, assuming a heavy MS stack and AWS?


texts: Allocation Unit ID calculation is incorrect?

texts:
I am reading the article http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-how-are-allocation-unit-ids-calculated/ on how to calculate the allocation unit ID based on m_indexId and m_objID. Then I try that on one of my database, by executing the command below:

    DBCC TRACEON(3604);
    DBCC PAGE (UFDATA_008_2013, 1, 73057, 3);
    GO

And the output result is:

    PAGE HEADER:

    Page @0x10008000

    m_pageId = (1:73057)                 m_headerVersion = 1                  m_type = 1
    m_typeFlagBits = 0x0                 m_level = 0                          m_flagBits = 0x8000
    m_objId (AllocUnitId.idObj) = 1913890731                                  m_indexId (AllocUnitId.idInd) = 0
    Metadata: AllocUnitId = 406903719657472                                   Metadata: PartitionId = 406903719657472
    Metadata: IndexId = 1                Metadata: ObjectId = 1913890731      m_prevPage = (1:92774)
    m_nextPage = (0:0)                   pminlen = 11                         m_slotCnt = 61
    m_freeCnt = 3241                     m_freeData = 4829                    m_reservedCnt = 0
    m_lsn = (4028:6215:30)               m_xactReserved = 0                   m_xdesId = (0:0)
    m_ghostRecCnt = 0                    m_tornBits = 0

Then I use the formula in the above article to calculate the allocation Unit ID as (0 << 48) + (1913890731 << 16) = 125428742946816, which does not equal to the output Metadata: AllocUnitId(406903719657472). Why?

I can confirm the page is allocated, since I use the following command to list all the pages allocated to table ‘AADomDataColSet’:

       DBCC TRACEON(3604);
       Dbcc ind(UFDATA_008_2013, ‘AADomDataColSet’, -1);

And the result is below:

You can find the page (1, 73057) is allocated to this object.

Thanks


texts: SQL: How to ask for multiple counts from the same table within a join

texts:
Trying to find 3 separate counts within one column of a table, each with separate WHERE clauses. I've got 2 tables in the query, so one join. Thought this looked correct but it will not run:

SELECT stores.caption,

COUNT (
            WHEN tasks.created_at BETWEEN '2019-04-01' AND '2019-04-30'
            AND tasks.deleted_at IS NULL)
            AS TaskTotal ,

 COUNT (
            WHEN tasks.created_at BETWEEN '2019-04-01' AND '2019-04-30'
            AND tasks.deleted_at IS NULL
            AND tasks.completed_at IS NULL) AS TaskOutstanding,

COUNT (
            WHEN tasks.created_at BETWEEN '2019-04-01' AND '2019-04-30'
            AND tasks.deleted_at IS NULL
            AND tasks.completed_at IS NULL
            AND tasks.deadline < '2019-05-16') AS TaskOverdue

    FROM tasks
    FULL JOIN stores ON tasks.store_id=stores.id
    GROUP BY stores.caption;


texts: Joining two inline functions, slows considerably when filtering on second function

texts:
I have two inline functions that run quickly enough alone.

When I left join them like this:

select *
from
Function1(-1) F1
left join
Function2(-1) F2 on F1.key = F2.key

...I get my result set in approx 14 seconds. Perfectly fine for what it's doing.

But if I add a where condition:

Where
F2.Boolean = 0

...then it takes over 8 minutes to complete.

There are some results that do not have a F2 record so those are null.

I did a test with isnull(f2.boolean,0) = 0 and that runs in 10 seconds, also if a place a or F2.boolean is null it runs quickly.

I am using SQL Server 2008 R2, and tested on SP2 and SP3. Does anyone have ideas or suggestions for what to look at?

The functions are simple select statements joining multiple tables. Nothing special in them. Each individual function returns results of the entire tables in under 10 seconds.

When I add "where one of the date fields in F2 is between two dates", that runs fast. I also just tested on a varchar(2) field in the where clause, and this causes it to be extremely slow as well (but not the datetime field). ok, Why would a filter on a bit field or varchar from the second function cause such a speed decrease, vs a datetime between? Another observed behavior: If I add a filter on F1, to specify only ones that actually have a F2 record first then the original filter on F2 it runs quickly again.


texts:
not sure if this is the right forum. But just wondering is there a way one can make a map report out of an image or a map made on excel. I have the data and I've looked around and found that its possible to edit world maps, etc, to show information. Is it possible to make a simple map from scratch, to show something like a floor plan, something that isn't available on google maps.
Thanks guys in advance sorry if I'm not being clear on what I'm asking. If i can be clearer please ask.

Note: I'm currently using SSRS and QGIS to try do this. I have the data i need to show but don't have the co-ordinates for the thing i want to show. I thought that could be something i could do when I'm making the map. Only a small map to show one piece of information about 200 distinct data points.


texts: MySQL Cluster - Table is full [Error 1114] while still creating tables

texts:
I am trying to forward engineer a MySQL Workbench model with about 100 tables to a development single-node cluster and, while creating the 51th table, I end up getting a "1114 table is full" error, which is somewhat curious because there isn't any data inside those tables yet.

I suspected the problem had to be related to the memory allocation of hash indexes, so I have tried to create that table without any constraints or indexes and adding them later, not surprisingly finding that the error came out after trying to add any of the indexes, but getting this incoherent memory usage report:

~$ /usr/local/mysql/bin/ndb_mgm -e "ALL REPORT MEMORYUSAGE"
Connected to Management Server at: localhost:1186
Node 1: Data usage is 0%(13 32K pages of total 16000)
Node 1: Index usage is 0%(71 8K pages of total 32032)

I assume I must be missing something but I still can't figure out what it is. This is my config.ini file:

[NDB_MGMD DEFAULT]
Portnumber=1186

[NDB_MGMD]
NodeId=49
HostName=192.168.122.1
DataDir=/var/lib/MySQL_Cluster/49/
Portnumber=1186

[TCP DEFAULT]
SendBufferMemory=2M
ReceiveBufferMemory=2M

[NDBD DEFAULT]
BackupMaxWriteSize=1M
BackupDataBufferSize=16M
BackupLogBufferSize=4M
BackupMemory=20M
BackupReportFrequency=10
MemReportFrequency=30
LogLevelStartup=15
LogLevelShutdown=15
LogLevelCheckpoint=8
LogLevelNodeRestart=15
DataMemory=500M
IndexMemory=250M
MaxNoOfTables=4096
MaxNoOfTriggers=3500
NoOfReplicas=1
StringMemory=25
DiskPageBufferMemory=64M
SharedGlobalMemory=20M
LongMessageBuffer=32M
MaxNoOfConcurrentTransactions=16384
BatchSizePerLocalScan=512
FragmentLogFileSize=64M
NoOfFragmentLogFiles=16
RedoBuffer=32M
MaxNoOfExecutionThreads=2
StopOnError=false
LockPagesInMainMemory=1
TimeBetweenEpochsTimeout=32000
TimeBetweenWatchdogCheckInitial=60000
TransactionInactiveTimeout=60000
HeartbeatIntervalDbDb=15000
HeartbeatIntervalDbApi=15000

[NDBD]
NodeId=1
HostName=192.168.122.1
DataDir=/var/lib/MySQL_Cluster/1/

[MYSQLD DEFAULT]

[MYSQLD]
NodeId=53
HostName=192.168.122.1

[API]
NodeId=50
HostName=192.168.122.1

Any ideas?


texts:
I would like to know if there is any way to execute a task in SQL that executes the result of a query

these is my script:

SELECT serie,noDoctoFiscal,total,shipperid,fechaemision,
  'update xDoctoFiscalDigital set PDFpath =''C:\Webfact Files Pdfs\Facturas\'
  + RTRIM(noDoctoFiscal)
  + '_' + RTRIM(ShipperId) +'.pdf'''
  + ' '
  + 'where shipperid = ''' +rtrim([ShipperId])+ ''' 'as 'Path'
from xDoctoFiscalDigital
WHERE fechaemision > ='2018-12-01' and serie ='TEC'
and PDFpath is null
order by fechaemision desc

and this is the result of my first script:

update xDoctoFiscalDigital set PDFpath ='C:\Webfact Files Pdfs\Facturas\TEC73535_0EPV085629.pdf' where shipperid = '0EPV085629'

I need to make a job that execute the script result.
Any help would be great, thanks


texts: How to check the pre-allocated database file of MongoDB is Used or not?

texts:
We have been using MongoDB for a few weeks. One thing we could not understood is Pre-allocated file. Is there any way to check whether the pre-allocated file is currently used by MongoDB or not?

We have planned to implement "Quota File". So we need to ensure the pre-allocated file is currently used or not.

For Example, I have the database file as follows:

sgserver.0 ---> 16MB
sgserver.1 ---> 128MB
sgserver.2 ---> 256MB
sgserver.3 ---> 512MB

How do I check the pre-allocated file sgserver.3 is used by MongoDB (for example, this database is used by any BSON or its Un-used)?


texts: Business Intelligence Development Studio installation for Visual studio 2010 problem

texts:
I want to install Business Intelligence Development Studio for doing report development in Visual Studio 2010, however in my installation process, I cannot find BIDS in the feature selection.

What I have done is:

Install Visual Studio 2010
Upgrade Visual Studio 2010 to SP1
Installed SQL Server Data tools
Lastly, tried to install SQL server 2008 for BIDS feature. But it is not in the feature selection list

What is the matter for this problem? Is there anything that I need to install before I install SQL server 2008? If there is, can I have the link?


texts:
table name:- **t_ci**
s39   column_name
        176

i want to have my table like this

s39    column_name
176     176

But this query works

UPDATE t_ci SET s39=s345  WHERE blueprint_id=1234;

But I dont know the column_name for example s345. This column_name will be the output of a different query

SELECT column_name FROM `t_core_properties` WHERE property_name='Virtual IP Address'

I tried this query.

UPDATE t_ci SET s39=(SELECT column_name FROM `t_core_properties` WHERE property_name='Virtual IP Address')
WHERE blueprint_id=1234;

this comes as

s39    column_name
s360     176

But it adds only the column_name not the value inside that.

I tried using the Mysql User defined Variables

SET @column = (SELECT column_name FROM `t_core_properties` WHERE property_name='Virtual IP Address');
UPDATE t_ci SET s39=@column WHERE blueprint_id IN(SELECT id FROM `t_core_blueprints` WHERE NAME='VMWare VCenter');

I get the same result as before.


texts: Is it possible to have a script that double quotes a view or proc

texts:
I apparently cannot figure out the key words to google search for what I want, so I need help finding out how to do this. I feel like this may be a duplicate, but I just cannot find this anywhere else.

I would like to be able to easily save a bunch of create table/view/proc scripts to a file. The file has an easy-to-execute format that will drop and then re-create all of the objects, so it can be run on multiple databases.

This format works well for me as I have to make changes to some objects during development, and then make the same changes on multiple other databases. I can just change the database and click execute.

However, some of the scripts have A LOT of quotes in them, and it gets QUITE annoying to have to double/quad quote them every time I make a change or add a new object to the file. For instance...

IF NOT EXISTS (SELECT * FROM sys.views WHERE NAME = 'MF1' AND schema_id = (SELECT schema_id FROM sys.schemas WHERE NAME = 'CFConversion'))
BEGIN

EXEC('CREATE VIEW [CFConversion].[MF1]
AS
...
...
...
CROSS APPLY (
VALUES (''T'', ''FIT', FIT, '''')
      ,(''T'', ''SIT', SIT, '''')
      ,(''T'', ''FUTA', FUTA, '''')
      ,(''T'', ''Employee_SUI'', EMPLOYEE_SUI, '''')
      ,(''T'', ''Employee_SDI'', EMPLOYEE_SDI, '''')
...
...
...

With tons of values in the Cross Apply, it just gets annoying. How can I easily export the definition of an object to have double/quad quotes?


texts:
I recently got into SSIS and haven't been able to find what I am trying to achieve anywhere on the web.

I am trying to take some rows as input, and then replace one specific column's value with something else, and then return the rows with modified columns as output.

The problem is that I don't know which task to use.

I'm limited by SQL Server 2008.

How do I get started?


texts: Is it safe to add more than primary data file to Temp DB in SQL server 2008 R2 as we have database server with 4 Core processor?

texts:
Is it safe to add more than primary data file to Temp DB in SQL server 2008 R2 as we have database server with 4  Core processor and please tell me the steps to do it .And does it will tune performance?

Tip : Our Temp DB size is about 22 GB which is bigger than our Production database and does it normal?


texts: Oracle - Why create several service_name in service_names parameter?

texts:
SERVICE_NAMES specifies one or more names by which clients can connect
  to the instance. The instance registers its service names with the
  listener. When a client requests a service, the listener determines
  which instances offer the requested service and routes the client to
  the appropriate instance.

What is the reason for I modify the parameter service_names like this

Service_names = 'service_one','service_two'

Rather than

Service_names = 'service_one'


texts: Remove (XX.. rows affected) from SQLCMD output to file

texts:
Using SQLCMD with -o to file.

How to get rid of the (XX... rows affected) message at the end for a clean output?


texts: Compression in MongoDb 3.2 does not work

texts:
I installed MongoDb 3.2, and configured snappy compression in mongodb configuration file:

engine: wiredTiger
   wiredTiger:
       engineConfig:
           cacheSizeGB: 99
           journalCompressor: none
       collectionConfig:
           blockCompressor: snappy
       indexConfig:
           prefixCompression: true

However I do not think compression actually work as I could see average object size in the collection is about the same (I filled collection created from scratch by the same objects right after configuration change and service restart):

MMAPv1 no-compression avgObjSize: 5kb
WiredTiger snappy avgObjSize: 4kb
WiredTiger zlib avgObjSize: 4kb

when I run stats on collection here is what I see for "snappy":

"compression" : {
        "compressed pages read" : NumberInt(0),
        "compressed pages written" : NumberInt(5),
        "page written failed to compress" : NumberInt(0),
        "page written was too small to compress" : NumberInt(4),
        "raw compression call failed, additional data available" : NumberInt(0),
        "raw compression call failed, no additional data available" : NumberInt(0),
        "raw compression call succeeded" : NumberInt(0)
    },

for zlib:

{
    "compressed pages read" : NumberInt(0),
    "compressed pages written" : NumberInt(1),
    "page written failed to compress" : NumberInt(0),
    "page written was too small to compress" : NumberInt(3),
    "raw compression call failed, additional data available" : NumberInt(0),
    "raw compression call failed, no additional data available" : NumberInt(1),
    "raw compression call succeeded" : NumberInt(2)
}

Not sure how to read this info, and not sure how can I detect what's wrong with compression.

I should also say I had only few (like 10) objects in each scenario. Would it be enough to accurately measure average object compression ratio?

UPDATE: with 1188 documents mongodb ver3 shown around 50% better compression ratio comparing to mongodb ver2.6 (MMAPv1), however got no difference between snappy and zlib: dataSize and avgObjSize are the same for some reason. When I request statistics it shown WiredTiger:creationString:...block_compressor=zlib.. for the first database and block_compressor=snappy for the second one, so databases and collections seemed to be created with right compression algorithms


texts:
I'm new to mysql,and I'm designing a  database for my social network app, I got some questions about it.

user_table

comment_table

1.Should I use unique key as much as I could in the database when the value of the field is unique?

2.Should I add some backup field in case I need to add other field in the future? If not,how to add other field in the future?

3.In the comment_table,I use user_id for foreign key,but I heard using too much foreign key will make the database slow,especially in social network.So,should I use comment_author_id instead,and if I need someone's
comment,I have to use

"select * from comment_table,user_table where comment_author_id = user_id"

Here is an graph I found on the internet,it use a lot of foreign key.Is it correct or out of date? Thanks for your time.


texts: Is there any reason that metadata should not be a part of my database schema?

texts:
By metadata I mean things such as:

the owner of an entity
permissions associated with an entity
the date and time of data entry
the surety of entered data
the format of a text attribute
descriptive names for attributes

This is all just data as far as I can tell. I might implement entity ownership by giving an Entity table a foreign key to a User table; I might store all text fields as two fields: content and format. And so on...

Is there any difference between data and metadata as far as practical database design is concerned? Why might I want to store my metadata somewhere other than my database?


texts:
I have a table with a m:n (many to many) relationship. Basically, if I select an article_id, I want to return all the other articles that have the same tag_id as the selected article_id.

| article_id | tag_id |
|------------|--------|
| 1          | 2      |
| 1          | 11     |
| 2          | 5      |
| 4          | 6      |
| 4          | 11     |
| 12         | 9      |
| 14         | 9      |
| 14         | 15     |
| 14         | 17     |
| 18         | 1      |
| 20         | 8      |
| 41         | 13     |
| 49         | 9      |
| 50         | 12     |
| 60         | 18     |
| 75         | 9      |
| 75         | 10     |
| 75         | 11     |

So, if i select article_id 75 i need to output all of the articles that contains tag_id 9, 10 and 11

| article_id | tag_id |
|------------|--------|
| 1          | 11     |
| 4          | 11     |
| 12         | 9      |
| 14         | 9      |
| 49         | 9      |

I've created the following query and it looks like it works, but i want to make sure. Is there a better way?

SELECT aj2.article_id, aj2.tag_id
FROM article_tag_join aj
JOIN article_tag_join aj2 ON aj2.tag_id = aj.tag_id
WHERE aj.article_id = 75 AND aj2.article_id <> 75


texts: Group by unique items in an array column and count records that contain each item

texts:
I have a table that looks like this:

records
-------------------------------------
id    items[]
-------------------------------------
1     {toaster,piano}
2     {car}
3     {car, toaster}
4     {tv}
5     {toaster}

Items is an array column. I'd like to group by the different 'items' and count the number of records that contain that 'item'. So the result I'd like here would be:

item      count
toaster   3
piano     1
car       2
tv        1

Is this doable or do I really need an items table here?

Thanks!


texts: Why is Row Overflow not working on SQL Server 2005

texts:
I have a SQL Server 2005 database with a table that has around 860 columns. Aside from the ID column all other columns are varchar(N) where N is either 100 or 500. Upon inserting a new row (via an InfoPath form as the front end) with more than 8000 characters the SQL server returns the following error:

Cannot create a row of size 11024 which is greater than the allowable maximum of 8060.

From what I understand this should not be happening due to Row Overflow.

Can anyone please provide some insight as to why this is happening?


texts: Security implications disabling promotion of distributed transaction for linked server

texts:
I've a a linked server and I need to run the following statement:

INSERT INTO...EXEC linkedserver.sp @parameter

Both servers SQL Server 2008R2 SP1. Once I run it I get this error:

  Msg 7391, Level 16, State 2, Line 6 The operation could not be
  performed because OLE DB provider "SQLNCLI10" for linked server
  "MY.LINKED.SERVER" was unable to begin a distributed
  transaction.

After searching for the error I saw that lot recommend running:

EXEC master.dbo.sp_serveroption
     @server = N'[mylinkedserver]',
     @optname = N'remote proc transaction promotion',
     @optvalue = N'false'

Is there any security implications on this action I should be aware of?

Other option is to use advanced properties of the linked server object on main server:


texts: SELECT query in a SP in AlwaysOn Availability Group Read Only Routing

texts:
There is a stored procedure which has select statement as well as insert and other statements. What will be the behaviour of the stored procedure in a Read Only Routing configured in AlwaysOn Availability Group?
Will the select statement go to the secondary server for reading and rest will be done on the primary server or since it is an inline statement in a stored procedure, that's why everything will take place on the primary itself.
I could not find any documentation regarding this, how will the select statement behave?


texts:
I have  questions regarding the distributed database elements and environment :

What is the exact meaning of a transaction processor and where does it exists in a distributed environment ?

What is the exact meaning of a data processor and where does it exists in a distributed environment ?

where does a DDBMS exists in a distributed database environment ?


texts: How to make a query on multiple columns without writting all possible permutations?

texts:
I'm at my first question on stackexchange, as i have a few days since I struggle on this matter:

I want to make a complex query(PLSQL) on a table that has col1,col2,col3,col4,col5 having values like (names: which are split- one part per column)

Andrew Joan Bach Mike
Mark Andrew Livy
Joan Arch Donnie
Joan Andrew Lyx

Number of parts differ from 1 to 5.

I want to search in different combinations like this:

Bach Joan Mike - get #1, #3,#4 order
Andrew Bach - get the following order #1,#2,#4 order

I don't like the idea of using a looooong query in which I will write all possible permutations in order to mach each part of my search string

What I'd like to achieve is this:

first set: match all those n parts (both Bach and Joan and Bach match, in any order)
second set: match n-1 parts (at least N-1 of my search parts match the row, in any order)
third set: match n-2 parts

I use a ORACLE database and I was thinking on making it in a stored procedure: match_my_set(query_str,col1,col2,col3,col4,col5). I would write at least 5 loops (loop into loop) in order to achieve this, but I doubt it is a professional idea . .

Any help is appreciated. Thank you


texts:
I'm new in SQL Server's extended events and you can create your own session and select your own name in Event library.
From your experience, what name from event library have you selected in order to faciliate your work or similar?
Thank you!


texts: How to prevent transaction log getting full during index reorganize?

texts:
We have multiple machines where we have pre allocated the size of the transaction log to 50gb. The size of the table that I am trying to reorganize is 55 - 60 gb but is going to continuously increase. The main reason I want to reorganize is to reclaim space and any performance benefit because of that is an added bonus.

The fragmentation level of the table is 30 - 35%. On some of these machines I get "transaction log full" error and reorganize fails. The transaction log size reaches upto 48gb. What is a good way to counter this? We do not have auto increment switched on and I am reluctant to do so.

I can increase the log size to a larger value but as the table size increases in the future, the value may not be enough. Also it defeats the purpose of doing reorganize to reclaim space if I am going to increase the log size equally. Any ideas on how I can effectively counter this? Using bulked mode is not an option since data loss is not acceptable.


texts: On a 12c R2 RAC DB, Oracle instance information on crsctl is shown incorrectly

texts:
crsctl status output is showing incorrect information for ora.***.db.
Instance 1 and 2 are running on Node1 and Node2 respectively but crsctl status shows as below

As we can see below, its showing Instance 1 is running on Node2 and Instance 2 is running on Node1.

ora.stgcdb.db
      1        ONLINE  ONLINE       node02                 Open,HOME=/u01/app
                                                             /oracle/product/12.2
                                                             .0/dbhome_1,STABLE
      2        ONLINE  ONLINE       node01                 Open,HOME=/u01/app
                                                             /oracle/product/12.2
                                                             .0/dbhome_1,STABLE

When we see srvctl status for database, it shows as below :

[oracle@node01 ~]$ srvctl status database -d stgcdb
Instance stgcdb1 is running on node node01
Instance stgcdb2 is running on node node02
[oracle@node01 ~]$


texts: Transposing hierachical data from one VARCHAR to two INTs

texts:
THE PROBLEM

The table where the data is recorded is

CREATE TABLE [dbo].[Almoxarifado](
    [idAlmoxarifado] [varchar](20) NOT NULL,
    [tipoAlmoxarifadoId] [varchar](30) NOT NULL,
    [entidadeId] [bigint] NOT NULL,
    [dtInclusao] [smalldatetime] NOT NULL,
    [dtUltimaAlteracao] [smalldatetime] NULL,
    [descricao] [varchar](255) NOT NULL,
    [terceiro] [bit] NOT NULL
) ON [PRIMARY]

And this is a SELECT TOP 10 * example (the table has more than 1 million entries):

As you can see, the idAlmoxarifadofield is storing a hierarchical data that has no hierarchical relationship as most hierarchical table have.

Now I want to bring this data to a usual hierarchical table:

CREATE TABLE [dbo].[Almoxarifado2](
    [idMaster] [bigint] IDENTITY(1,1) NOT NULL,
    [idAlmoxarifado] [int] NOT NULL,
    [idAlmoxPai] [int] NOT NULL DEFAULT ((0)),
    [entidadeId] [bigint] NOT NULL,
    [tipoAlmoxarifadoId] [varchar](30) NOT NULL
    [dtInclusao] [datetime] NULL DEFAULT (getdate()),
    [dtUltimaAlteracao] [datetime] NULL,
    [descricao] [varchar](255) NOT NULL,
    [terceiro] [bit] NULL DEFAULT ((0)),
    PRIMARY KEY ([idMaster])
)

On this table I'll have a usual father-son relationship where the "father" of a given field will always be it's idMastercorrespondent (unless it is on the first degree of the hierarchical chain; then it's "father" would be 0).

So, if we follow the same order shown on the image above, we should have (after inserting the transposed data on the new table) :

THE QUESTION

How can I do this transposition is a safe and painless way since the original table has more than 1 mi entries?

THE RULES

There are no restrictions on how many top-levels could exist; neither children levels. Our idea is to work with a mask to make the data useful on our application (we workd with Motorola Handheld Computer; check MC45 and MC3190). Let's say that the customer that buys our solution has several Warehouses where he stocks his products. In this case he'll be able to define a mask (say 9.99.99.999 or 9.9.99.99.99.999) and our system will have to be able to use this mask to allow him to register his warehouses in those levels.

So you can basically say that our system is foccused on logistics in a fashion.

Where the 1st level could be "NY South Warehouse", the 2nd "Room #2", 3rd "Corridor A", 4th "Locker 2" and 5th "Shelf 3".


texts: pg_basebackup from docker postgres container and restoring to physical postgres

texts:
I am using pg_basebackup to backup data that is running in docker based postgres (alpine) to a physical postgresql cluster. Both postgres versions are 12.
docker exec -t postgresql pg_basebackup -U postgres -D /backup -Ft -z -Z5 -P
docker cp postgresql:/backup .
I am trying to restore the physical cluster with this backup data. I am getting a lot of weird errors. postgresql is not restoring correctly.
Is it possible to use backup from docker container to physical postgres ? will it work ?
Is it better to do with pg_dumpall ?
Thanks.


texts:
CREATE OR REPLACE TRIGGER AWH_TOOLS_TRG
AFTER LOGON ON HRMS.SCHEMA
DECLARE
v_prog sys.v_$session.program%TYPE;
v_pro  varchar2(250);
BEGIN
SELECT upper(program) INTO v_prog
FROM sys.v_$session
WHERE  audsid = USERENV('SESSIONID')
AND  audsid != 0
AND  ROWNUM = 1;
v_pro := SUBSTR(upper(v_prog),0,INSTR(upper(v_prog),'.')-1);
IF UPPER(USERENV('TERMINAL')) NOT IN ('DESKTOP-U186KO5','WLSRV') THEN
          IF UPPER(v_prog) LIKE '%TOAD%'      OR
             UPPER(v_prog) LIKE '%T.O.A.D%'   OR
             UPPER(v_prog) LIKE '%SQLNAV%'    OR
             UPPER(v_prog) LIKE '%PLSQLDEV%'  OR
             UPPER(v_prog) LIKE '%BUSOBJ%'    OR
             UPPER(v_prog) LIKE '%EXCEL%'     OR
             UPPER(v_prog) LIKE '%SQLPLUS%'   OR
             UPPER(v_prog) LIKE '%FRMBLD%'    OR
             UPPER(v_prog) LIKE '%RWBUILDER%'
          THEN
          RAISE_APPLICATION_ERROR(-20983, '" '||v_pro||' "'||' not allowed... Please contact DBA...!!!');
          END IF;
END IF;
END;

program column is not showing any data during query.
How can i resolve this issue???


texts: Prevent execution of heavy-loading and / or runaway queries in postgres

texts:
Is there a way to stop/kill queries being executed on a postgres database based on criteria such as:

maximum execution time
load (CPU and/or memory) imposed on the server?


texts:
I am currently developing a new database schema with an opportunity to do things right.  The purpose of the development effort is to collect data (events) from various sensors that will be recorded with a time/date stamp.

Currently there will be two disconnected databases at two different locations capturing data that may or may not occur on the same day.  On a monthly basis, the drives will be pulled and sent to the customer's main office to ingest the data and merge into one database.

Once the data is available, the customer will replay the data for analysis as if they were actually there when it was captured.

Since recalling this data needs to be performance driven, I know right off the bat I want a clustered index on DATETIME2.

This leads me to the problem of designing the primary key to account for merging.

Would a composite primary key of DATETIME2 and an IDENTITY column be unique enough to avoid any collisions when the data is merged? For example, some data may be captured at a 50hz to 100hz rate.

Or is that too much of a wild card and it would be best to use a GUID as the PK?   If that is the case, how should I handle a clustered index for performance (for example) where a child table would have a PK of two columns:  a GUID from the parent table and a 3 char column "product" identifier?

Would it be best to add a DATETIME2 column to become the clustered index or am I making a mountain out of a mole hill?


texts: Alternative query to avoid count(distinct)

texts:
i have a performance issue with my query and i need to rewrite it to have my result in better time. If you can help me with an idea?

My query is the following:

select
    dc.Country,
    COUNT(DISTINCT fvcd.IdAppInstance) AS Visitors
FROM
    dbo.Fact_VisitorsCountryDetails fvcd
    INNER JOIN dbo.Dim_Country dc ON dc.Id = fvcd.IdCountry
WHERE
    fvcd.IdDate BETWEEN @IdMinDate AND @IdMaxDate
    AND fvcd.IdPartnerid = @IdPartnerId
GROUP BY
    dc.Country
ORDER BY
    Visitors DESC;

My DDL is:

CREATE TABLE [dbo].[Dim_Country](
    [Id] [bigint] NOT NULL,
    [Country] [varchar](24) NULL,
 CONSTRAINT [PK_Dim_Country_Id] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Fact_VisitorsCountryDetails](
    [IdDate] [bigint] NULL,
    [IdPartnerId] [bigint] NULL,
    [IdCountry] [bigint] NULL,
    [IdAppinstance] [bigint] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Fact_VisitorsCountryDetails]  WITH NOCHECK ADD  CONSTRAINT [FK_Fact_VisitorsCountryDetails_Dim_Country_Id] FOREIGN KEY([IdCountry])
REFERENCES [dbo].[Dim_Country] ([Id])
GO

ALTER TABLE [dbo].[Fact_VisitorsCountryDetails] CHECK CONSTRAINT [FK_Fact_VisitorsCountryDetails_Dim_Country_Id]
GO

ALTER TABLE [dbo].[Fact_VisitorsCountryDetails]  WITH NOCHECK ADD  CONSTRAINT [FK_Fact_VisitorsCountryDetails_Dim_Date_Id] FOREIGN KEY([IdDate])
REFERENCES [dbo].[Dim_Date] ([Id])
GO

ALTER TABLE [dbo].[Fact_VisitorsCountryDetails] CHECK CONSTRAINT [FK_Fact_VisitorsCountryDetails_Dim_Date_Id]
GO

ALTER TABLE [dbo].[Fact_VisitorsCountryDetails]  WITH NOCHECK ADD  CONSTRAINT [FK_Fact_VisitorsCountryDetails_Dim_PartnerId_Id] FOREIGN KEY([IdPartnerId])
REFERENCES [dbo].[Dim_PartnerId] ([Id])
GO

ALTER TABLE [dbo].[Fact_VisitorsCountryDetails] CHECK CONSTRAINT [FK_Fact_VisitorsCountryDetails_Dim_PartnerId_Id]
GO


texts: Improve time for Select first 10 Rows of a Table with 10 Million Rows Postresql

texts:
I am working with a table of 10 million rows and I am selecting the first 10 or the last 10 rows with this query:

SELECT *
FROM category_member
ORDER BY (p::float8/q) DESC
LIMIT 10;

I am ordering by (p::float8/q) cause I am using a user specified order with fractions as index (I have the same table as in this link explained just with 10 million rows)

Limit  (cost=585557.40..585557.43 rows=10 width=24) (actual time=15865.330..15865.332 rows=10 loops=1)
  ->  Sort  (cost=585557.40..610557.40 rows=10000000 width=24) (actual time=15865.327..15865.327 rows=10 loops=1)
        Sort Key: (((p)::double precision / (q)::double precision))
        Sort Method: top-N heapsort  Memory: 25kB"
        ->  Seq Scan on category_member  (cost=0.00..369461.00 rows=10000000 width=24) (actual time=0.070..13528.741 rows=10000000 loops=1)
Planning time: 10.556 ms
Execution time: 15865.540 ms

This query takes to long in my eyes 16,135 s to get the first 10 rows of a table with 10 Million rows.

Anyone had the same issue and knows how to improve performance on my query?

CREATE UNIQUE INDEX ON category_member (category_id, (p::float8/q));


texts: Postgres - Vacuum doesn't complete on large/busy table

texts:
We have a decently active PG database hosted on AWS. We recently started getting notifications like the following:

 Transaction ID age reached 750 million. Autovacuum parameter values for [autovacuum_vacuum_cost_limit, autovacuum_vacuum_cost_delay, autovacuum_naptime] are updated to make autovacuum more aggressive.

I also noticed the disk usage for this particular table was increasing fast. Here's the used space:

[
  {
    "oid": "16413",
    "table_schema": "public",
    "table_name": "connections",
    "row_estimate": 1.01476e+07,
    "total_bytes": 518641270784,
    "index_bytes": 478458511360,
    "toast_bytes": 30646272,
    "table_bytes": 40152113152,
    "total": "483 GB",
    "index": "446 GB",
    "toast": "29 MB",
    "table": "37 GB"
  }
]

Then we were doing some analysis on something else and noticed a long-running (5 days ago) vacuum process:

[
  {
    "pid": 14747,
    "duration": "14:11:41.259451",
    "query": "autovacuum: VACUUM ANALYZE public.connections (to prevent wraparound)",
    "state": "active"
  }
]

(This was actually a new one, but looked identical to this as the last one never finished).

To confirm, i see that the connections table hasn't been autovacuumed since the 15th and has a lot of things to cleanup:

[
  {
    "relid": "16413",
    "schemaname": "public",
    "relname": "connections",
    "seq_scan": 19951154,
    "seq_tup_read": 226032655046,
    "idx_scan": 41705151351,
    "idx_tup_fetch": 375484186787,
    "n_tup_ins": 8029742,
    "n_tup_upd": 13217694302,
    "n_tup_del": 542670,
    "n_tup_hot_upd": 96750657,
    "n_live_tup": 10237553,
    "n_dead_tup": 887751401,
    "n_mod_since_analyze": 350036721,
    "last_vacuum": null,
    "last_autovacuum": "2019-06-15 17:05:51.526792+00",
    "last_analyze": null,
    "last_autoanalyze": "2019-06-15 17:06:27.310486+00",
    "vacuum_count": 0,
    "autovacuum_count": 4190,
    "analyze_count": 0,
    "autoanalyze_count": 4165
  }
]

I've read a bunch about configuring autovacuum_vacuum_scale_factor and autovacuum_analyze_scale_factor differently for very active tables. And that's all good, but it doesn't appear it's going to get through this one right now that's running.

I've also read about optimizations to autovacuum_vacuum_cost_limit and autovacuum_vacuum_cost_delay to let it be more aggressive in the work it needs to do.

I've tried to change a few of those for the table, but it just sits there when I try to write any values for that particular table.

What's the best way to get the table vacuumed?

Also, would effect would a reboot of the database have on all this?


texts: Enable SQL server agent service by T-SQL script or PowerShell

texts: Can we enable/disable SQL server agent service by T-SQL script or PowerShell/cmd?


texts: Why isn't prod level updated after applying SP - SQL 2012

texts:
My environment is SQL Exp 2012 | SELECT @@VERSION = 11.0.2100.6 (RTM)

I then installed SP4 + GDR with no errors.

Now, SELECT @@VERSION = 11.0.2100.6 (RTM).  I would have expected 11.0.7462.6.

Discovery report:

In registry:

HKEY_LOCAL_MACHINE>SOFTWARE>Microsoft>Microsoft SQL Server>
110>SQLServer2012>CurrentVersion>PatchLevel=11.4.7462.6 & Version=11.4.7462.6

HKEY_LOCAL_MACHINE>SOFTWARE>Microsoft>Microsoft SQL Server>
ADK>MSSQLServer>CurrentVersion=11.0.2100.6

If I attempt to patch with SP4 + GDR (based upon shown version = 11.0.2100.6) the Shared Features in unchecked and I get the error

  There are no SQL Server instances or shared features that can be
  updated on this computer.

Using the GUI to update:

It appears to already be patched, which it is.

On another express instance, I followed the above process and the version was 11.0.7462.6 after the SP + GDR update. Great?

My question:

Why isn't the version not getting updated to the patch level?  How can I fix this?

Thanks for any help.


texts: Create database link on Oracle Database with 2 Databases on different machines.

texts:
I have 2 oracle databases (db1 and db2) on different machines, lets suppose: db1 on 192.168.1.1 and db2 on 192.168.1.2

What I want to achieve is to create a link on db1 that will allow me to perform queries on db2.

This is the way I created the link on db1:

create public database link db2Link connect to db2username identified by db2password
using 'db2';

I think this should work if both that bases are on the same machine, but as I want to connect to a database that is on a different machine, how can I do that link?

Thanks
Best Regards


texts:
I am attempting to run this syntax

Select
[StoreName] = (Select *
              FROM OPENQUERY(192.168.5.812,'Select
                                    storename
                                    FROM stores') x
              WHERE x.storename = y.storename)
[StoreManager] = y.storemanager
FROM managerinfo y

But this gives me an error of

  Msg 116, Level 16, State 1, Line 28
  Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Is it possible to do this while using OPENQUERY?  How can I achieve my desired result?


texts: Oracle 11g installation stuck at installation

texts:
I'm trying to install oracle 11g on fedora by following this tutorial but it stuck after I click Next in step 8. The screen does not go away even after several hours. There's no log file in oraInventory folder.

What could be the possible problem? How can I debug it?


texts: Is there a good reason to store big text data outside database?

texts:
I was analyzing my MySQL database performance and found that tables containing text fields are just huge, larger than 1Gb.

This data is only used when loading single record (like article or blog post) and is updated very rarely.

Is there a reason to save text data to plain files and metadata to database, or let the db engine handle it all?


texts: How to get the last not-null value in an ordered column of a huge table?

texts:
I have the following input:

 id | value
----+-------
  1 |   136
  2 |  NULL
  3 |   650
  4 |  NULL
  5 |  NULL
  6 |  NULL
  7 |   954
  8 |  NULL
  9 |   104
 10 |  NULL

I expect the following result:

 id | value
----+-------
  1 |   136
  2 |   136
  3 |   650
  4 |   650
  5 |   650
  6 |   650
  7 |   954
  8 |   954
  9 |   104
 10 |   104

The trivial solution would be join the tables with a < relation, and then selecting the MAX value in a GROUP BY:

WITH tmp AS (
  SELECT t2.id, MAX(t1.id) AS lastKnownId
  FROM t t1, t t2
  WHERE
    t1.value IS NOT NULL
    AND
    t2.id >= t1.id
  GROUP BY t2.id
)
SELECT
  tmp.id, t.value
FROM t, tmp
WHERE t.id = tmp.lastKnownId;

However, the trivial execution of this code would create internally the square of the count of the rows of the input table ( O(n^2) ). I expected t-sql to optimize it out - on a block/record level, the task to do is very easy and linear, essentially a for loop ( O(n) ).

However, on my experiments, the latest MS SQL 2016 can't optimize this query correctly, making this query impossible to execute for a large input table.

Furthermore, the query has to run quickly, making a similarly easy (but very different) cursor-based solution infeasible.

Using some memory-backed temporary table could be a good compromise, but I am not sure if it can be run significantly quicker, considered that my example query using subqueries didn't work.

I am also thinking on to dig out some windowing function from the t-sql docs, what could be tricked to do what I want. For example, cumulative sum is doing some very similar, but I couldn't trick it to give the latest non-null element, and not the sum of the elements before.

The ideal solution would be a quick query without procedural code or temporary tables. Alternatively, also a solution with temporary tables is okay, but iterating the table procedurally is not.


texts: Getting MySQL User Variable in Sequence, While Using ORDER BY RAND()

texts:
I am running a SELECT statement, which ultimately will be in an INSERT..SELECT statement.

I want a result like this

ID        COUNTER
3           1
7           2
1           3
20          4
12          5

When I run this query, I get everything in sequence:

SELECT 16132,@counter:=@counter+1 as counter
  FROM table t
 INNER JOIN (SELECT @counter:=-1) b
 LIMIT 5;

But I want it to be randomized, so I add ORDER BY RAND() at the end of the query, but then I end up with "random" values in the counter column. I think the randomized counter values match to the larger, entire result set, but is there a way I can get a counter column that will go in sequence when I am using ORDER BY RAND() ?


texts: repmgr: Can I replicate databases from two different masters to a third slave?

texts:
If I have a master node 1 (with database A) and a master node 2 (with database B) can I use repmgr and have both A and B replicated to a hot standby node 3 ?

[ EDIT: They would all be running Postgres 9.3 ]


texts: Select rows from T such that sum(T.quantity) is greater than an argument

texts:
TLDR: I need a way to limit my select such that only rows needed to fill my new order are returned.

Hey, I'm looking for a better way to do a query on the simplified toy table below. Right now I do a straightforward select then do some additional logic in Python that processes rows that are fully or partially fillable.

id |name   | category |  price  | quantity
---+-------+----------+---------+---------
1  |Robby  | buy      | 45      | 4
2  |Blane  | buy      | 48      | 5
3  |Shawn  | buy      | 43      | 10
4  |Valva  | sell     | 62      | 9
5  |Chris  | buy      | 42      | 15

If I'm inserting a new sell order at price $40 and quantity = 12

SELECT id, name, price, category
FROM my_table
WHERE price > 40 AND category = 'buy'
ORDER BY price DESC;

Then in Python my logic looks something like this:

running_total = 0
for order in orders:
    running_total += order.quantity
    fill_order(order, new_sell_order)
    if running_total >= new_sell_order.quantity:  # Values is 12 in my example.
        break

I'd like a way to limit my select such that all rows are returned in order until a row is returned such that the sum of all quantity columns is greater than some value. Additional columns need not be returned (and thus locked in a repeatable read state).


texts: DAC silently swallows tokenization errors in sp_msforeachdb

texts:
This morning I hopped on the DAC to check something out. Oops, I made a typo!

1> exec sp_msforeachdb N'
2~ use [?]
3~ select N'?', count(*) from sys.sysrscols;
4~ '
5> go
1>

Can you spot my error? Pretty obvious when you see it, right? It's even more obvious if you type that command into SSMS.

Frustratingly, it took me a long time to figure it out. You will note that no error message is returned from the batch 1 (indicated by the 1> on the newline after go)

Maybe errors get handle differently in the DAC? No... doesn't look like it...

1> select 1/0 -- simple error
2> go
Msg 8134, Level 16, State 1, Server VANDIVIER, Line 1
Divide by zero error encountered.
1> select ( -- syntax error
2> go
Msg 102, Level 15, State 1, Server VANDIVIER, Line 1
Incorrect syntax near '('.
1> exec (N'select ('); -- syntax error in deferred command
2> go
Msg 102, Level 15, State 1, Server VANDIVIER, Line 1
Incorrect syntax near '('.
1> exec sp_executesql N'select ('; -- sp_executesql
2> go
Msg 102, Level 15, State 1, Server VANDIVIER, Line 1
Incorrect syntax near '('.
1>

So clearly the DAC returns most errors to the client in just the way we expect. What the heckin' heck is happening here?!

1 You will also note that even though rowcount is set to on, no rowcount was returned to the client - which should have been a clue in hindsight.


texts: Why would call to scalar function inside a Table Value Function be slower than outside the TVF?

texts:
I am writing a Table Value Function, calling the function takes 10x as long as directly running the code.  I traced this to a call to a multi-line scalar function inside the TVF.  The call to the scalar function is excessively slow WHEN called from within the TVF.  The scalar function takes 3 int parameters and returns a single int result.

What would cause it to be slower from within a TVF?

First, the TVF is basically a sort of pivot table, returning just one row, with 13 columns.

The scalar function is a multiline scalar, it looks for a matched set of keys (ie input is columnA, output is columnB, where  input = columnA and active) in one of two tables.  Searching table 1, then table 2 and finally table 1 again with a slightly changed where clause.

Usage was originally a cte query with a join clause:

  With cte as ( select count(*) over (partition by c.c1) recs,
                       c.setId, c.c1, c.c2, n.name
                From tbl c
                     Inner join tbl n on n.id = schma.scal(c.c1, c.c2, c.c3)
                Where c.setId=@setId and c.endDt is null
   )

This cte was originally called 11 times, returning from (about half the time) zero to maybe 3 rows (with a where clause like c1=42).  So, I thought, hey, it's getting called a lot, since there are really only 4-15 rows in total, and the base tables are ~1,000,000, I'll cut that down to just 15 calls by putting the values into a table variable, and then doing an update.  This wasn't really any faster but it did let me prove that it was the call to the scalar function that was slowing things down.

That looked like:

   Insert into @cte(recs, setId, c1, c2)
    select count(*) over (partition by c.c1) recs, c.setId, c.c1, c.c2
    From tbl c
    Where c.setId=@setId and c.endDt is null;

    Update c
     Set nId =schma.scal(c.c1, c.c2, c.c3)
    From @cte c

The @cte had, as I said, from 4 to 15 rows (11 in my most used setId).  Putting a return first after, and then before this update is how I concluded that it was the scalar function causing the problem.  Basically it was tens of milliseconds, more than a second, another few milliseconds and done.

The query plan from SSMS wasn't useful, and I was thinking about seeing if I could get more details using the plans in the DMVs.

Knowing that TVFs can be used to replace scalars, and that this can improve performance, I gave that a try, and it worked (times in ~100 ms range for the whole query when called inside the original TVF).

I'm still scratching my head as to why it would take longer to call from within the TVF than from outside.

From outside the TVF, I get comparable times using the scalar as I do using the new TFV inside the original TFV.


texts: Oracle related to count(*) syntax alternate options

texts:
i am using a Stored Procedure which are using multiple count(*) in oracle which makes slowness of the server the same i have used in db2 using if exists() which works fine in db2 but the same syntax is not supporting in oracle.
below is my Stored Procedure: i have to use 8 elsif conditions(just use here 2 only)

in the below SP i don't want to use count(*). can anyone help on this:

CREATE OR REPLACE FUNCTION GetStandadrdDefaultDataXml
(
    p_pf_wkstn_oid_sh number,
    p_pf_wkstn_oid_lng number
)
RETURN varchar2
IS
 StdDefaultValue varchar2(1500);
 rate_count1L NUMBER(5);
 rate_count1 NUMBER(5);
 v_count NUMBER(5);
BEGIN

    --RateTable1L
    select count(*) into rate_count1L FROM RateTable1L where wkstn_oid_sh = p_pf_wkstn_oid_sh
            AND wkstn_oid_lng = p_pf_wkstn_oid_lng and exact='STANDARD DEFAULT';
    --RateTable1
    select count(*) into rate_count1  FROM RateTable1 where wkstn_oid_sh = p_pf_wkstn_oid_sh
            AND wkstn_oid_lng = p_pf_wkstn_oid_lng and exact='STANDARD DEFAULT';

    --RateTable1 and RateTable1L
    IF( rate_count1L > 0 )
    THEN
        --RateTable1L
        SELECT rate into StdDefaultValue FROM RateTable1L where wkstn_oid_sh = p_pf_wkstn_oid_sh AND wkstn_oid_lng = p_pf_wkstn_oid_lng  and exact ='STANDARD DEFAULT';
    ELSIF ( rate_count1 > 0 )
    THEN
        --RateTable1
        SELECT rate into StdDefaultValue FROM RateTable1 where wkstn_oid_sh = p_pf_wkstn_oid_sh AND wkstn_oid_lng = p_pf_wkstn_oid_lng  and exact ='STANDARD DEFAULT';
    --RateTable2 and RateTable2L
    --- multiple elsif we have to use here.
        END IF;

    return StdDefaultValue;
END;
/

Please provide solution/hints to handle


texts: How to properly call identical functions in different schemas in PostgreSQL?

texts:
I have the same function in multiple schemas. When I first call the function in schema A, subsequent calls will return data from schema A, regardless of how I call the function.

First call:

SELECT * FROM schema_a.my_little_function();
-> data from schema_a

Next call:

SELECT * FROM schema_b.my_little_function();
-> data from schema_a

When I set search_path to another schema that schema will stick.

SET SEARCH_PATH = 'schema_b';
SELECT * FROM schema_a.my_little_function();
-> data from schema_b

This happens across sessions. Anyone know what is going on?

The simplified function looks like this:

CREATE FUNCTION get_bla (id_param integer) RETURNS TABLE(data text)
    LANGUAGE sql
AS $$
SELECT t.data FROM table t;
$$


texts:
Is there any way to declare identity partitioned by tenant id?

For example I want to have this structure:

tenant_id | record_id (auto increment)
---------------------
      1   |   1
      1   |   2
      1   |   3
      1   |   4
      2   |   1
      2   |   2


texts: If replication is 600 seconds behind master will INSERT or UPDATE queries including `NOW()` be 600s different?

texts:
If slave is behind master by 600 seconds, will queries including NOW() also be 600s different?

Example:

master|mysql> INSERT INTO my_table (hash,my_time) VALUES ('foo',NOW());
### 600s later, does slave get the same query?
 slave|mysql> INSERT INTO my_table (hash,my_time) VALUES ('foo',NOW());

Will the following produce the exact same result if run on master and slave?

master|mysql> SELECT my_time FROM my_table WHERE hash = 'foo'; #2016-04-21 09:49:42
 slave|mysql> SELECT my_time FROM my_table WHERE hash = 'foo'; #????-??-?? ??:??:??

Assume SHOW SLAVE STATUS\G shows Seconds_Behind_Master: 600, that master and slave are in the same datacenter (same timezone) and slave doesn't catch up to master until after the above is completed.

If the two are 600s different, what is the best practice to use instead of NOW()?


texts: SQL Server 2016, temporal tables and compressed indexes

texts:
We use temporal tables in our database, and I have just run Brent Ozar's sp_BlitzIndex on it with @mode=0 (urgent issues only).

It has come up with 33 issues, all of which are "Abnormal Psychology: Compressed indexes", and they are all on the temporal tables e.g.

  Schema.TableName.ix_IndexName (1). COMPRESSION:   Partition 1 uses PAGE
      [CX] [2 KEYS] SysEndTime {datetime2 8}, SysStartTime {datetime2 8}

I am pretty sure we didn't actively choose to turn compression on for these indexes, and I think it is the default behaviour for system generated history tables. But should I be worried? sp_BlitzIndex thinks I should be...

What are the recommended best practices? Thanks in advance.


texts:
A similar question has been asked here, but I'll give a brief intro. In SQL Server there are 3 tables:

Order (Id, Date);
Product (Id, Name);
OrderRefProduct (Id, OrderId, ProductId);

OrderRefProduct is a many-to-many table since one product can be in different orders and different products can be in one order.

All this is sticked into an OLAP Cube with Orders and Products as Dimensions and OrderRefProduct as Facts. We need to get results like the output of SQL query would provide:

SELECT ProductId, COUNT(*) as [Count]
FROM OrderRefProduct
GROUP BY ProductId

the correspondind MDX query is fairly simple, given the Cube is built correctly:

SELECT
NON EMPTY { [Measures].[Order Ref Product Count] } ON COLUMNS,
NON EMPTY { ([Product].[Product ID].[Product ID].ALLMEMBERS ) } ON ROWS
FROM [Sales]

And the result for both is:

ProductId   Count
1   7
2   7
3   5
4   6

Now here's the problem. I want to select count of orders that have BOTH products 3 AND 4. SQL:

select ProductId, Count(*) as [Count]
from OrderRefProduct
where [OrderId] in
(SELECT S.OrderID FROM
(select * from OrderRefProduct where ProductID=3) as S
INNER JOIN (select * from OrderRefProduct where ProductID=4) as T on T.OrderId = S.OrderID)
Group by ProductId

Result:

ProductId   Count
1   1
2   1
3   3
4   3

Can't reproduce this in MDX query. What I could achieve so far is FULL JOIN, while I need an INNER one. Any help would be appreciated. Test solution can be found here.


texts:
In the given export(Normal Export) log file i see following:

Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

Is this multi-byte or single byte database char-set.

Can you please explain me a bit

What is difference between multi-byte and single byte character set?
Can we import multibyte into Single byte and vice versa?
How can we identify weather a character set is multi-byte or single byte by looking into its name?

Please provide any other useful information on this topic?


texts: Performance issues since migrating from SQL Server 2012R2 to 2019

texts:
I've migrated my database from a SQL Server 2012R2 to a new SQL Server 2019. I've just backupped the database and restored it on new sql server. After that I've imported and updated some additional data (quotes) with IMPORT, INSERT, UPDATE or MERGE. And in between some testers have used the system for some testing. Now performance is on some actions slower than on old server.

First I've done some index tuning. Here are some questions regarding this topic:
Questions on updating statistics and index maintenance jobs
Index and statistics optimization scripts duration and log bloat problem. Looking for good strategy? [closed]

Second I've done the same import done on our test environemnt with SQL Server 2012R2. And there I don't have the performance issue.

Query
(deleted)

Execution Plans

[Execution plan on Server 2012R2]
(deleted)
[Execution plan on Server 2019]
(deleted)


texts: Is there a faster way of moving data between databases than CTEs?

texts:
I use the following query on SQL Server 2012 Express to move data that is older than 4 days old out of one [primary] database and into another [secondary database] for archiving purposes. The only issue is that this takes the database offline as it is usually moving about 500k rows of data, and that figure is climbing with more data points being added to the database.

The result is that my web based app is not able to access the database for (in most cases) about 2 hours, and this stalls a lot of other processes, as well as the app.

DECLARE @4daysago datetime
SELECT @4daysago = DATEADD(d, -4, GetDate());

SET IDENTITY_INSERT [activetrackarchivedb].dbo.[Data Import] ON;

--Transfer from current (production) DB to Archive DB
WITH CTE as (
    SELECT TOP 1000000 *
    FROM [activetrackdb].dbo.[Data Import]
    WHERE [activetrackdb].dbo.[Data Import].[Receive Date] < @7daysago
    ORDER BY [Receive Date] ASC)
DELETE CTE
  OUTPUT DELETED.id,
  DELETED.[Company id],
  DELETED.[Site id],
  DELETED.[Site name],
  DELETED.[Receive date],
  DELETED.[Detect date],
  INTO  [activetrackarchivedb].dbo.[Data Import]
  (id,
  [Company id],
  [Site id],
  [Site name],
  [Receive date],
  [Detect date]);

Is there a better method I could use to 'transfer' these rows? Even if the new method was slower, if it could at least still allow access to the database. This one took me a week to implement (I'm a novice) and involved the StackOverflow community's help. It's been great up until now, but as amount of data has increased, its become a very cumbersome process.

This is not for backup purposes either. The database the rows are transferred to simply have a higher capacity and the archived data is still used in the production environment (it just makes the primary database more maintainable and responsive when combined with other maintenance and indexing scripts)

Any help would be greatly appreciated.


texts: SQL Server MCSA Database Administration Certification retires on June 30, 2020 - is there going to be a replacement?

texts:
Looking at exams 70-764 and 70-765

  This exam retires on June 30, 2020. A replacement exam may be available. To learn more, visit our blog: https://www.microsoft.com/en-us/learning/community.aspx and review our Exam Retirement posts.

Updated competency exams and certifications for 2020 (pdf)
Microsoft Certified: Azure Data Engineer Associate

Is there going to be a SQL Server specific replacement for exams 70-764 and 70-765?

Am I getting it wrong, or this thing "Microsoft Certified: Azure Data Engineer Associate" going to replace "MCSA SQL Server 2016"?

If true, that is a shame, because it looks like passing its exams (DP-200 and DP-201) would require learning a lot of stuff that I actually do not need...


texts: Why would a procedure sometimes hang executing SELECT...INTO #Temp2 FROM #Temp1 JOIN LocalTable...?

texts:
We have several stored procedures, called by jobs, that follow a specific pattern (designed to minimize blocking). Some of these procs tend to hang on occasion, running up the CPU, tempdb allocations, reads, and writes indefinitely until killed. The pattern is:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
CREATE #RemoteTemp...;
INSERT INTO #RemoteTemp EXEC MyLinkedServer.DbName.SchemaName.ProcName;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SELECT...INTO #MixedTemp FROM #RemoteTemp JOIN LocalTable...;

We then make use of #MixedTemp. However, that last statement is the one that sometimes runs in less than one second and sometimes hangs even though the row counts are the same at every execution. What might cause that?

Additional info:

1117 and 1118 are enabled.
8 tempdb files for this 8 core instance.
No tempdb autogrowth is occurring.

During the hang:

Sch-S locks on the local tables.
On the temp table being read:

Lock resource_type="HOBT.BULK_OPERATION" request_mode="S"
request_status="GRANT" request_count="1" Lock resource_type="OBJECT"
request_mode="X" request_status="GRANT" request_count="1"

On the temp table being inserted into:

Lock resource_type="OBJECT"
 request_mode="X" request_status="GRANT" request_count="1"

From sp_lock (6 and 7 are databases being joined to the temp table, 2 is tempdb):

spid   dbid   ObjId       IndId  Type Resource                         Mode     Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
96     6      0           0      DB                                    S        GRANT
96     7      0           0      DB                                    S        GRANT
96     6      702625546   0      TAB                                   Sch-S    GRANT
96     2      -1219105587 0      HBT  [BULK_OPERATION]                 S        GRANT
96     7      98099390    0      TAB                                   Sch-S    GRANT
96     2      -1219105587 0      TAB                                   X        GRANT
96     2      -1203105530 0      TAB                                   X        GRANT
96     7      115775811   0      TAB                                   Sch-S    GRANT
96     7      194099732   0      TAB                                   Sch-S    GRANT


texts: Transforming multiple rows into a single row


texts: MSSQL Deadlocking preventing DDL statement in different DB from running

texts:
I had a user doing a massive SQL data compare from server A to server B involving database X.

On Server B, I was trying to run a CREATE SYNONYM statement in database Y, which was being entirely blocked -- as in the statement was running for approximately 20+ mins.  There were no open transactions in database Y on server B.

I was wondering what could be causing this, and if it is preventable via server configuration, so I can pass any information along to my sys admin and DBA.  Thanks in advance for any insight that might help performance.

According to sp_who2:

Create Synonym blocked by INSERT into master by me

INSERT into master by me blocked by INSERT into master by sa

INSERT into master by sa blocked by massive EXECUTE from other user (for SQL compare / sync)


texts:
I need to migrate a big database from Interbase to MySQL, I'd like to have suggestions about free and non-free tools to perform this task.

I've found this one: http://dbconvert.com/convert-firebird-to-mysql-pro.php

But I'd like to have advises by someone that has done this kind of task to have concrete advises.


texts: When installing mysql in linux, why do we create a mysql group and user?

texts:
When installing mysql in linux, why do we create a mysql group and user ?
The installation step tells to create a user and to change the ownership of mysql folder to mysql.


texts: Always Encrypted - certificate cannot be used as a column master key

texts:
I want to create a new column master key using certificate created in advanced. I use the following command to create a certificate in the Windows Certificate Store - Local Machine.

New-SelfSignedCertificate -CertStoreLocation cert:\LocalMachine\My -DnsName TEST_CERT_01

The issue is that when I try to select this certificate, I get the following error:

Can anyone tell what's wrong with the certificate?