texts:
Below is some VBA code to authorize users to login into an MS Access database. I am trying to fix this code and I don’t understand what is the issue in this code snippet.
Private Sub cmdLogin_Click()
Dim lngUserID As Long
    lngUserID = Nz(DLookup("UserID", _
    "tblUser", "username = '" _
    & Me!cbousername _
    & "' AND Password = '" _
    & Me!txtPassword & "'"), 0)
    If lngUserID = 0 Then
        MsgBox "Username and/or password are incorrect."
        Me!txtPassword.SetFocus
    Else
        MsgBox "Registration successful!"
        OptionSetting "CurrentUserID", CStr(lngUserID)
        DoCmd.Close acForm, Me.Name
        DoCmd.OpenForm "Homepage"
    End If
      SetWebControlAsIE9
    ChangeWebControlFeature
END SUB

The debugger in the VBE shows the following code highlighed in yellow. Can someone help me to understand what the issue is?
lngUserID = Nz(DLookup("UserID", _
    "tblUser", "username = '" _
    & Me!cbousername _
    & "' AND Password = '" _
    & Me!txtPassword & "'"), 0)


texts: How to choose suitable naming convention for Dimensional Table Column in a Data Mart?

texts:
I have done a lot of reading on naming columns for use in a data mart / data warehouse, as well as for general programming usage.

For example, if I had a business branch in a normal application I would have

Table BRANCH
------------
ID
Code
Name
RegionID

Now in the star schema data mart, I want to flatten the region information into this table.
Which would be more appropriate and user friendly as a naming choice?

Table Branch_Dim
------------
BranchID
BranchCode
BranchName
RegionName
RegionShortName

OR

Table Branch_Dim
------------
ID
Code
Name
RegionName
RegionShortName


texts: Performance drop after change tracking & isolation snapshot

texts:
Several days ago, I enabled change tracking on a db and some of its tables.
A few days ago, I also SET ALLOW_SNAPSHOT_ISOLATION ON.

(Probably important note: I never SET READ_COMMITTED_SNAPSHOT ON, and the only procedures/queries that actually used transaction isolation where a few manual runs by myself, which I saw them run to completion within a logical time frame).

A few days after that, we got reports of decreased performance. I am aware that both of these things impact performance in a minor(?) way, but i am not sure how to measure this yet. So, for the sake of being on the safe side, I SET ALLOW_SNAPSHOT_ISOLATION OFF, and also disabled change tracking on every table first and on the whole db in the end.

The performance problems remain. So my questions are:

Can change tracking and/or ALLOW_SNAPSHOT_ISOLATION cause performance drop in a server/database even AFTER they are disabled? If so, how, and how can I check and fix the problem?

I run a few queries from this site to check the state of tempdb but did not find any interestingly large table.


texts: Execute pgScript in pgAdmin 4 while skipping errors

texts:
In pgAdmin3, via the SQL Editor, there is an Execute pgScript button which (unlike Execute Query) would run through the entire script, skipping errors. The SQL editor in pgAdmin4 doesn't appear have this, is there an alternative that I haven't been able to find, or is there a parameter/command I can add to my script in order to skip errors at runtime?

In this instance the script is auto-generated and only renames tables/columns which may not exist so the error output is not required.

I can still run the script in pgAdmin 3 but connecting to a version 9 or above database with pgAdmin3 raises several warnings which don't seem to cause any problems but I'd rather find a solution than a workaround

Using: PostGreSQL 9.5, pgAdmin4 1.6

UPDATE: according to this:

    How do we execute pgScripts in pgAdmin4?

  You cannot I'm afraid. They've been more or less unsupported for years
  as they really became obsolete when Postgres introduced the DO command
  for executing anonymous blocks of code in v9.0.

...it looks like I might be able to use DO to force it to behave like pSQL?


texts: Enable TLS 1.2 and disable SSL 3 on SQL Server 2008

texts:
I know similar questions have been asked. What I'm asking for are concrete instructions on what to do, or not do, for my particular installation. I have some questions that I can't find the answer to, and I need some answers before I can install Microsoft's hotfix.

The goal is to disable SSL 3 on the machine by adding the registry key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\Schannel\Protocols\SSL 3.0\Server\ - add value Enabled = 0. However this causes SqlServerAgent to not start.

Running SQL Server 2008 R2 (SP3) build 10.50.6220.0 (X64).

Here's Microsoft's official download to enable TLS1.2 support: link to microsoft

The page reads First build that supports TLS 1.2 - 10.50.6542.0. This is higher than my build number. Does it mean the hotfix only is compatible with builds from 10.50.6542 and up? And so my build is not supported?

If my build is supported, then I supposed I should install it. Then what happens next?


texts: PostgreSQL hot standby promotion, multiple hot standbys

texts:
I'm trying to work out failover and disaster recovery procedures for a
cluster of three servers. Streaming replication is being used with a high
wal_keep_segments, no log shipping is happening. I need to avoid the
several hours it takes to rebuild a hot standby from scratch.

ServerA is the master.
ServerB is a streaming hot standby and prefered failover server.
ServerC is a streaming hot standby.

For a planned failover, maintenance on ServerA:

Shutdown ServerB & ServerC
Shutdown ServerA
Copy pg_xlog from ServerA to ServerB and ServerC
Reconfigure ServerB as master, start it up.
Reconfigure ServerC as streaming hot standby of ServerB. Start it.
After maintenance, reconfigure ServerA as streaming hot standby of
ServerB. Start it.

For an unplanned failover, ServerA has exploded:

Run 'SELECT pg_last_xlog_receive_location()' on ServerB and ServerC, determining which is most up to date.
Shutdown ServerB and ServerC
If ServerC is more up to date, copy pg_xlog from ServerC to ServerB.
Reconfigure ServerB as master, start it up.
Reconfigure ServerC as streaming hot standby of ServerB, start it up.

Does this look correct to people?

Am I going to end up in trouble copying files into pg_xlog like this on a
busy system? With further reading, I suspect I need a recovery.conf on the new master with a restore_command pointing to an archive of the old master's WAL files (if available).

Is it overengineered? eg. will a master ensure everything is streamed to
connected hot standbys before a graceful shutdown?


texts: SQL Replication, transactions Replication

texts:
I have configured a SQL transaction replication to create two subscribers and one publisher, the question is that after any deployment and changes that made on the SQL "alter procedures" on the publisher server, the replication log several errors and i solved the issue by re establish the snapshot.

is there any think that i should do during the deployment "run the updated script on the publisher" ? should i stop the application to not log any transaction during the deployment, or take the DB offline. any ideas please .


texts: Error when I run sp_BlitzCache specifying an output db, schema, and table

texts:
I am using sp_BlitzCache version 4.1 (20161210) and executing it on a 2008R2 instance (10.5.4042).

If I execute the sp without the @OutputDatabaseName, @OutputSchemaName, and @OutputTableName, it works fine. However, when I set those values, it runs through its process and at the very end, and error appears:

  "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns."

The specific statement I am running is:

EXEC [dbo].[sp_BlitzCache] @OutputDatabaseName = 'Maintenance_Stats'
,@OutputSchemaName = 'dbo'
,@OutputTableName = 'BlitzCacheResults'

The database, schema, and table already exist, but has no records in it. So...somewhere in the sp code, it created the table, but is trying to stuff too many values into the table.

I am using this to try to troubleshoot an application (SCCM) hang issue, and as recommended, have created a SQL Agent job that the helpdesk can execute when they encounter the issue (it is intermittent), but I tried executing it myself, and it failed with the above error.


texts:
I'm looking for a SQL design solution for the following problem. The idea is that I have a validation form that contains the client's data. Each client can log in to his account and edit his data. After that I have to save it to the database. After validation of the content of the data, I have to update the original fields.

Actually each client has a photovoltaic installation. Each installation contains one or more inverters and at each one there is N solar panels that are attached. All of the inverters are attached to an electrical meter.

I choose to save only the difference between the data stored in the db and the modified data. e.g: if the client changes only the brand of his inverter I will not save all the data, but only the modified inverter brand. I then have to validate the brand and to update it in the table Inverters.

My first idea to solve that issue is to work with 2 tables: validation_data and validation_fields. The table validation_fields contains all the fields that can be updated (vaf_field). The table validation_data is a temporary table. It contains the id of the field to update (vad_vaf_id, who is also the foreign key to the table validation_fields) and the value (vad_value). vad_met_id is the foreign key to link to the meter. Nothing special :) Once the data is verified, vad_is_treated will have the value 1. The default is 0.

+-----------------+   +-------------------+
| validation_data |   | validation_fields |
+-----------------+   +-------------------+
| vad_id          |   | vaf_id            |
| vad_met_id      |   | vaf_field         |
| vad_vaf_id      |   +-------------------+
| vad_value       |
| vad_is_treated  |
+-----------------+

So that logic is good, or at least it works fine for me, but only if the client possesses only one inverter. As I said I save only the difference between the existing and modified data. So if the client changes the brand of two of his inverters, in the validation_data table I will have:

+--------+------------+------------+--------------+
| vad_id | vad_met_id | vad_vaf_id |   vad_value  |
+--------+------------+----------- +--------------+
|      1 |     1234   |          2 |        Peter |
|      2 |     1234   |          3 |    SMA 1400S |
|      3 |     1234   |          3 |    SMA 1400S |
+--------+------------+------------+--------------+

Consider that if vad_vaf_id is 2, that is the firstname field, and 3 - the inverter's brand.

So okay, for the first line all is good, the client changed his firstname. But for the second and the third line there is a problem. The client changed the brand of his inverters but for which one? The first and the second inverter, or the first and the third, or the second and the third?

Do you have any idea how I can deal with that?


texts: Avoiding deadlocks in PostgreSQL with multiple clients

texts:
I am developing software which launches multiple, concurrent clients to connect to a PostgreSQL (12) database. When each client starts, the first thing it does upon connecting to PostgreSQL is to run the schema creation script.

This script has been written idempotently -- at least in principle -- such that the multiple clients shouldn't trip over themselves. By in large, this works fine. However, PostgreSQL sometimes detects deadlocks and affected client(s) crash. Looking through the logging, I believe these occur under such a sequence:

Client A: Begin schema creation transaction
Client A: Finish schema creation transaction
Client B: Begin schema creation transaction
Client A: New transaction that uses schema (select from view)

Client A and B now in deadlock

The logs aren't 100% clear and I can't reproduce this deterministically, but that seems to be what's going on: Client A is trying to SELECT from a view defined by the schema, but it's deadlocking because Client B is trying to recreate that view (CREATE OR REPLACE VIEW) in the schema script.

Is it possible to ensure that the schema creation script runs exclusively? Or, is there some other solution (e.g., rather than CREATE OR REPLACE VIEW, I only CREATE VIEW once I've determined it doesn't already exist)?


texts:
How do I combine these 2 queries?

select a, b, c from MY_TABLE where date < sysdate+1 AND c is NULL order by id

select a, b, c from MY_TABLE where date < sysdate AND b is NULL order by id


texts: This certain MySQL database organisation

texts:
Hypothetically, let's say that I have three tables:

- Products (id, product_name, added_timestamp)
- Individuals (id, name, last_name)
- Companies (id, company_name, company_number, company_address)

Now, let's say that both individuals and companies can add their products to my virtual shop.

Now, let's say I want to display all the added products sorted from descending order by the Products.added_timestamp column. However, I don't know how to connect Products with Individuals and Companies and display all the data.

How would I approach this problem?


texts: Relationship between two multi-valued child tables

texts:
Scenario

In my system, there are a number of different TradeBaskets. Each TradeBasket contains a number of different Securities. The Securities have a Type, Value, Sector etc... and new Securities are populated into the basket each day (and others can be removed).

Each TradeBasket has a unqiue list of EligibleEquity that can be accepted from the Securities within the TradeBasket. EligibleEquity can take the form of CommonStock, PfdStock, Reit, MutualFunds etc. My initial approach was to model the EligibleEquities as a multivalued attribute (instead of an entity). But I believe the best approach is to model EligibleEquity as an entity.

The TradeBasket also has a list of EligibleIndices that are accepted for the EligibleEquities in the TradeBasket. Thus, for each type of EligibleEquity in the TradeBasket, there is a list of associated EligibleIndices. It is true that each type of EligibleEquity(such as CommonStock, PfdStock etc...) can exist in a number of different EligibleIndices (such as S&P500, AS25X, CAC40 etc...) and that each EligibleIndices can be associated with a number of different EligibleEquities. Thus, I have assigned a Many:Many relationship between EligibleEntities and EligibleIndices.

ERD Design

In the ERD provided, I have colour coded the relationship between the TradeBasket and EligibleEquities and EligibleIndices.

In one case (red), I have mapped the EligibleEquity and EligibleIndices as a 1:Many relationship with the TradingBasket. In this case, I consider EligibleEquity and EligibleIndices as multi-valued attributes of the TradeBasket. Thus, in order to normalize the TradeBasket, the EligibleEquity and EligibleIndices are placed in  separate tables (using the primary key of TradeBasket as the foreign key in each EligibleEquity and EligibleIndices child table). However, is it possible to create a Many:Many relationship between ElibibleEquity and EligibleIndices if they are children to the TradeBasket?

In the other case (purple), I have mapped the EligibleEquity and EligibleIndices as a Many:Many relationship with the TradingBasket. In this case, I consider EligibleEquity and EligibleIndices as entities in their own rite. and the Many:Many relationship between EligibleEquities and EligibleIndices is straightforward.

Questions:

To facilitate a relationship between EligibleEquity and EligibleIndices, what the cardinalities should I adopt (red or purple - with respect to the ERD)?
If the EligibleEquity and EligibleIndices should be mapped as multivalued entities of the TradingBasket, what would the primary key of the "junction" table between EligibleEquity and EligibleIndices be?

This question has a similar discussion, but doesn't fully answer my question.

Thanks for any help!


texts: Can we shrink datafile of a database which is configured with log shipping

texts:
I am using MS SQL Server 2012 version RTM, below is our environment,

I have a primary database Server A and Secondary Database Server B Log shipping configured on A to B of a database,

1) Database size 500 GB, We would like to delete old data and make it to 250 GB, Can we do this without breaking of log shipping?

2) After this we need to shrink the data file not log file, Can we shrink data file without breaking of log shipping?

If we do shrinking do i need to stop log shipping jobs or will it break log shipping configuration?


texts: Does a composite primary key break the first normal form?

texts: Does having a composite/compound key (using multiple columns/attributes) to form the primary key break first normal form? (I am assuming that this is handled by the DBMS, and so the attribute containing composite key does not exist in the table/relation itself, and so this still complies with the first normal form, but is this correct?) thanks.


texts: Move Complete ORACLE from one system to another

texts:
I have 2 identical server with exact disk partitions (OS CENTOS 6.4 both ) [My college central oracle server]. [Oralce 11G Enterprise Edition]

Entire ORACLE HOME ( bin , control files, data files everything on oracle) is on 2nd Disk (which is not a OS disk)

Since OLD server had reached end of life (from HP) a new HP machine with upgraded RAM (OLD system had 16 GIG where as new system as 64 GIG).

Now Since All oracle Dependencies (RPM, libraries, environmental variables, JAVA, Users and groups) are already made on new server.

So Now Question is ..

Can I remove Disk [Oracle HDD] from OLD server and insert it on NEW server and just start Oracle (sqlplus "/ as sysdba" or dbstart and lnsctrl).

Can it will give any challenge.
(I have cold backups, export backup, & RMAN backup too).

I just want to give a try on above case.

Will this work ??


texts: Change the behavior of temporal tables to log actual value changes rather than dummy updates

texts:
Currently the articles I read on temporal tables push the responsibility of the clean-up to the application code. Is it possible to configure the temporal tables to record particular column value changes instead of when the update statement is called on the table?

Example for the expected behavior:

USE Master;
GO

-- Create test database
CREATE DATABASE [TemporalTables];
GO

USE TemporalTables;
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- Hidden period columns
CREATE TABLE [dbo].[PersonHistory](
    [PersonID] [int] NOT NULL,
    [EmailAddress] [varchar](75) NULL,
    [IsSubscribed] [bit] NULL,
    [SysStartTime] datetime2  NOT NULL,
    [SysEndTime] datetime2 NOT NULL
);

CREATE CLUSTERED COLUMNSTORE INDEX IX_PersonHistory
   ON PersonHistory;
CREATE NONCLUSTERED INDEX IX_PersonHistory_ID_PERIOD_COLUMNS
   ON PersonHistory (SysEndTime, SysStartTime, PersonID);
GO

CREATE TABLE [dbo].[Person](
    [PersonID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    [EmailAddress] [varchar](75) NULL,
    [IsSubscribed] [bit] NULL,
    [SysStartTime] datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    [SysEndTime] datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH
(
      SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.PersonHistory)
);

-- Current Time UTC
SELECT sysutcdatetime();
-- Value: 2017-03-14 16:11:56.6294004

--Insert Rows
INSERT [dbo].[Person] ([EmailAddress], [IsSubscribed]) VALUES ('SD.Burman@gmail.com', 1);
GO

INSERT [dbo].[Person] VALUES ('RD.Burman@gmail.com', 1);
GO

INSERT [dbo].[Person] ([EmailAddress], [IsSubscribed], [SysStartTime], [SysEndTime])
VALUES ('AR.Rehman@gmail.com', 1, default, default);
GO

-- Query the main table
SELECT *, SysStartTime, SysEndTime
FROM dbo.Person
-- Shows all changes for specific PK (for inserts to show up)
SELECT *, SysStartTime, SysEndTime
FROM dbo.Person
FOR SYSTEM_TIME ALL
where Personid IN (1,2,3);

-- Current Time UTC
SELECT sysutcdatetime();
-- Value: 2017-03-14 16:13:27.0374068

--Update Rows (Actual Updates)
UPDATE [dbo].[Person] SET [EmailAddress] = 'SD.Burman@hotmail.com' WHERE [EmailAddress] = 'SD.Burman@gmail.com';
GO
UPDATE [dbo].[Person] SET [EmailAddress] = 'RD.Burman@hotmail.com' WHERE [EmailAddress] = 'RD.Burman@gmail.com';
GO
--Update Rows (Dummy Update)
UPDATE [dbo].[Person] SET [EmailAddress] = 'AR.Rehman@gmail.com' WHERE [EmailAddress] = 'AR.Rehman@gmail.com';
GO

-- Query the history table
SELECT *
FROM dbo.PersonHistory

-- !!!
-- What I'd want is that the Id = 3 update to not get saved since the data did not change.
-- Also a way I can limit the number of columns I want this behavior on.
-- PersonID    EmailAddress                                                                IsSubscribed SysStartTime                SysEndTime
-- ----------- --------------------------------------------------------------------------- ------------ --------------------------- ---------------------------
-- 1           SD.Burman@gmail.com                                                         1            2017-03-14 16:12:29.5018193 2017-03-14 16:13:27.0374068
-- 2           RD.Burman@gmail.com                                                         1            2017-03-14 16:12:29.5118172 2017-03-14 16:13:27.1484051
-- !!!

Similar question highlighting this behavior:

Do temporal tables log changes when there are none?

I'm looking for a workaround for the mentioned behavior.

Change Data Capture would be able to handle this (or even custom triggers for that matter), but I'm exploring if I could get the benefits of temporal tables with selective history recording.

The challenge is, we have an ETL job that touches the syncdatatime columns often, and we want to exclude or include certain columns, as well only record when the actual data value changes, preferably with the temporal tables.


texts: Lock a row if it exists, create new record otherwise

texts:
I have a table that holds metadata for "documents", let's call it DOCS.  Other tables point to the metadata table's ID, to build the parts of the document.

I want to make sure that, in my application, I can have one person "check out" a document so that they are the only person who can edit it. (With an override mechanism built into the app, of course).

So my idea is to have a column in the DOCS table, LockedBy, which holds a user ID if the document is locked.

I want the application to only have to use one stored procedure for inserts and updates, so I'm currently taking the given @DOCID and doing an "upsert":

UPDATE DOCS
SET column=@param
WHERE ID=@DOCID;
IF @@ROWCOUNT=0 --@@ROWCOUNT will be 0 if the update fails
BEGIN
    INSERT INTO DOCS(columnnames)
    VALUES(@newvalues);
    SET @DOCID=SCOPE_IDENTITY();
END

But now that I want to add the locking mechanism, I need some way to check if the doc exists, and it is not locked by someone else, taking into account the possibility of concurrency.

If I do:

BEGIN TRANSACTION TR1
IF EXISTS(
    SELECT *
    FROM DOCS WITH(HOLDLOCK,ROWLOCK)
    WHERE ID=@DOCID)
BEGIN
    IF NOT EXISTS(SELECT * FROM DOCS WHERE ID=@DOCID AND LockedBy=@Editor)
    RETURN -1 --return "error" code to app
END
ELSE
--create row here

--finally do all the updates to secondary tables
COMMIT TRANSACTION TR1

Will the row be locked from the SELECT to the end of the transaction?  And if I create the row in the last ELSE clause, do I need to do something like INSERT INTO DOCS WITH(HOLDLOCK,ROWLOCK)?
Is there a better way to do this?


texts: sql join query through Adventure works database

texts:
I want to show a list of Product Colors. For each Color then show how many SalesDetails there are and the Total SalesAmount (UnitPrice * OrderQty). Only show Colors with a Total SalesAmount more than $50,000 and eliminate the products that do not have
a color.

Please see my query below:

select * from Production.Product
select * from Sales.SalesOrderDetail

Select pr.Color
from Production.Product as pr
left Join
--on pr.ProductID=sr.ProductID
(SELECT  count( sr.SalesOrderDetailID) as count1,sum(sr.UnitPrice * sr.OrderQty) as total
     FROM    Sales.SalesOrderDetail as sr
     GROUP BY sr.ProductID,
     HAVING  sum(sr.UnitPrice * sr.OrderQty) >50000
    ) as sa
 on sa.ProductID=pr.ProductID

I am not sure what to do after this point.please help


texts: How to create a conditional index in MySQL?

texts:
How to create an index to filter a specific range or subset of the table in MySQL? AFAIK it's impossible to create directly but I think it's possible to simulate this feature.

Example: I want to create an index for NAME column just for rows with STATUS = 'ACTIVE'

This functionality would be called a filtered index in SQL Server and a partial index in Postgres.


texts: Mysql: Count, Group By, and limiting to first instance of an ID


texts: SQL script to count rows for all views in a database with SELECT-only access

texts:
I need to know the row count for every view in a third-party database that I have been given SELECT-only access to. The following script works for tables but not views:

SELECT      SCHEMA_NAME(A.schema_id) + '.' +
        --A.Name, SUM(B.rows) AS 'RowCount'  Use AVG instead of SUM
          A.Name, AVG(B.rows) AS 'RowCount'
FROM        sys.objects A
INNER JOIN sys.partitions B ON A.object_id = B.object_id
WHERE       A.type = 'U'
GROUP BY    A.schema_id, A.Name
Order by AVG(B.rows) desc
GO

I have looked online but all the suggestions either work only for tables, require me to create tables or procedures (which I can't do with this level of permissions), or require me to count the rows for each view separately, which is infeasible for a database containing a large number of views such as this.

Does anyone have any ideas for a script that would count all of the rows in all of the views in a database without requiring CREATE or EXECUTE permissions?


texts:
So I've got an Excel template I send to clients and they fill in one cell with a type of resource they need, and I check an Access database for available resources to find what we have and fill out things accordingly. Here's an example of what I'm trying to do:
The client wants some rental cars from the ones that I have. They copy the rows and add SUV/Sedan/Van/Truck into column B. Say they have three requests for SUVs and five requests for Sedans. How could I import data like make, model, and year, based on these entries? I want to be able to query which vehicles have the attribute they want and fill in which ones are available, preferably sorted newest to oldest, with blank entries if I don't have anything available.


texts: Automatic slave reconfiguration after updating master

texts:
I have development mysql master and slave servers and a prod server. I need to set up regular (once a week) syncronization of dev and prod databases. It would be easy for me to use prod# mysqldump > dump.sql and dev-master# mysql < dump.sql as it won't break replication, but having a 170Gb base it is not very good idea.
So the question is if there's a proper way to re-enable replication after re-writing database on master. Will it work if I just re-write database on both master and slave and hope no bin-log errors occure?

Upd: percona-server-server-5.6, InnoDB/Barracuda are used.


texts:
I was doing some research on what the recommended maximum size for the transaction log, but I cannot find any substantiate or documented answer. I learned that a log size of about 25 - 50 percent of the DB is not uncommon. I also know that you want to set a recommended number of VLFs and must tune autogrowth to prevent VLF fragmentation. This can slow down your queries and log backups.

The only thing I cannot find is what would be a recommended maximum size in relation to your database size or possibly workload. Of course, you probably don't want your log file to grow beyond the database size, but what would be the exact impact? Of course, log backups will take longer if the log file is huge AND the log file is filled for example 80 percent.

But what if I have a db of 1 TB and a log file of (hypothetically :-) ) of 2 TB, which would be filled for 10 percent. I can imagine that log backups will take longer because the log backup has to scan the complete log maybe? If your VLFs are not fragmented, then there is not really a problem right?

Log backups are critical to SQL Server and let's say you would create a log disk that is 2 TB, make the log file 500 GB. Now, the backup fails for a few hours, the log fills for 1.5 TB. Your log backup will take longer, much longer, sure. We have a performance impact because of the autogrowth events, yes, but we made sure the DB does not stop. In such a scenario, when the backup is fixed and the first log backup has run, the log filing is low, but the log is unusually large. Now, I cannot think of any critical reason to get the log file back to the normal ( let's say that 500 GB). Of course you don't want to do that during production hours of course due to locking. However, I cannot imagine that there would be absolutely no impact with such a huge log file (which would be mainly empty).

Edit:

To clear op my a little bit;  my question is actually what is the impact of a scenario where a transaction log is huge (let's say 3x the size of the db) but log backups are taken regularly and it will normally be filled for only about 10%. Maybe slower log backups, possible really bad VLF fragmentation?

I don't want to do this. I only want to understand what the impact will be when I find a case where that is the issue (yes, there people who do this...). Of course I want to get it back to normal, but based on the impact, I can determine a priority. High; fix it now not matter what downtime, medium; fix it within a week because it is not critical, low; fix it during the next maintenance window.

Again; iam not interested in how to size it, but what would be the impact in a scenario where the file is already huge AND regular log backups are taken AND it is only filled for max 10% or so. Also, I want to be able to no only say that this is a bad, bad, bad, bad idea, but also why. Hope this helps to clear up my question. Btw, I tried to reproduce this scenario in my home lab, but it is difficult to see what the impact would be.


texts:
What does it mean to backup a database?

My impression is that you would simply dump everything that makes up the database into some format that can be stored externally and used to restore from when needed.

Are there different levels of backups? For example, maybe it is not necessary to backup absolutely everything about the database. Data is critical, so the table structure is important, but there's probably more to databases than just that.


texts: Why does this SQL work on one version of MySql-Server but not another?

texts:
I'm trying to run the following very simple SQL command on two different MySQL databases:

CREATE TABLE mytable (
    created DATETIME(6) NOT NULL,
    updated DATETIME(6) NOT NULL,
    my_id INTEGER NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (my_id),
    UNIQUE (name)
);

This mysql fails when I run it against mysql-server 5.5.
But it succeeds when I run it against mysql-server 5.6. Why?

Below you can see how actually ran it. Apparently 5.5 doesn't allow giving an integer-size with a DATETIME column.

Failing Case:

    # mysql -D my_db_instance -h 127.0.0.1
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 938
    Server version: 5.5.46 MySQL Community Server (GPL)

    Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql> CREATE TABLE mytable (
        ->     created DATETIME(6) NOT NULL,
        ->     updated DATETIME(6) NOT NULL,
        ->     my_id INTEGER NOT NULL AUTO_INCREMENT,
        ->     name VARCHAR(255) NOT NULL,
        ->     PRIMARY KEY (my_id),
        ->     UNIQUE (name)
        -> );

    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(6) NOT NULL,
        updated DATETIME(6) NOT NULL,
        my_id INTEGER NOT NULL A' at line 2

Succeeding Case:

    $ mysql -u myuser -p -D my_db_instance -h 127.0.0.1
    Enter password:
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 6
    Server version: 5.6.21 Homebrew

    Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql> CREATE TABLE mytable (
        ->     created DATETIME(6) NOT NULL,
        ->     updated DATETIME(6) NOT NULL,
        ->     my_id INTEGER NOT NULL AUTO_INCREMENT,
        ->     name VARCHAR(255) NOT NULL,
        ->     PRIMARY KEY (my_id),
        ->     UNIQUE (name)
        -> );
    Query OK, 0 rows affected (0.01 sec)


texts: What would be the better schema to represent this data?

texts:
I suspect this is one of these questions to which there is not going to be a single definitive answer.  But I'm hoping to gain some insight into what might generally be considered best practice, most of the time anyways.  So, thanks in advance to those who weigh in on this.

I have two types of folders for storing records.  Personal Folders and Team Folders.

There are two ways that I could represent this in the database...

Option 1: Two separate and unrelated folder tables.

personalFolders( id, userId, title )
teamFolders( id, teamId, title )
records( id, folderId, folderType, title )

Option 2: A common folder table with two join tables

folders( id, title )
personalFolders( folderId->folders.id, userId )
teamFolders( folderId->folders.id, teamId )
records( id, folderId->folders.id, title )

My question is:  Generally, what schema would be considered best practice?

(My gut is telling me to go with option 2, because then I can keep the foreign key constraint in the records table.  But, are there situations where it might be better to go with option 1?)


texts: Mysql query to get count result and group by

texts:
I've got three tables with different records for users,

User

username |        realname   |         date
evn-az-3ju           john      11/2012 03:09:40 p.m.
jwyvm_rdyt           steve      12/2012 03:09:40 p.m.
bsMIAtWkhi           mahesh      01/2013 03:09:40 p.m.
zrObzh4um0           santa      01/2013 03:09:40 p.m.
WyVm_rDYt           grolsch      11/2012 03:09:40 p.m.

offline

username |       messageID |    message
jwyvm_rdyt           54      <message to="jwyvm_rdyt" id="t4Wa4-291" type="chat" from="evn-az-3ju">test1<thread>1a327531-5a1c-4d6b-8b66-1209cdabb77d</thread></message>
jwyvm_rdyt           78      <message to="jwyvm_rdyt" id="t4Wa4-290" type="chat" from="evn-az-3ju">Happy birthday<thread>1a327531-5a1c-4d6b-8b66-1209cdabb77d</thread></message>
evn-az-3ju           89      <message to="evn-az-3ju" id="t4Wa4-290" type="chat" from="evn-az-3ju">Happy birthday<thread>1a327531-5a1c-4d6b-8b66-1209cdabb77d</thread></message>
zrObzh4um0           98      <message to="zrObzh4um0" id="t4Wa4-290" type="chat" from="evn-az-3ju">Happy birthday<thread>1a327531-5a1c-4d6b-8b66-1209cdabb77d</thread></message>
WyVm_rDYt           45      <message to="WyVm_rDYt" id="t4Wa4-290" type="chat" from="evn-az-3ju">Happy birthday<thread>1a327531-5a1c-4d6b-8b66-1209cdabb77d</thread></message>

roster

username |             jid       |     date
jwyvm_rdyt           evn-az-3ju      11/2012 03:09:40 p.m.
jwyvm_rdyt           zrObzh4um0      12/2012 03:09:40 p.m.
jwyvm_rdyt           bsMIAtWkhi      01/2013 03:09:40 p.m.
zrObzh4um0           WyVm_rDYt      01/2013 03:09:40 p.m.
WyVm_rDYt           zrObzh4um0      11/2012 03:09:40 p.m.

These are the tables from which i want result set like this,

 username |             realname       |     count
evn-az-3ju           john                     2
zrObzh4um0           santa                    0
bsMIAtWkhi           mahesh                   0

I want to know all the roster jid which username 'jwyvm_rdyt' has along with their realname and count of message if that message contains any roster jid in that. Like 'evn-az-3ju' is in message 54 and 78, so count 2 for that roster jid.

Any help would be greatly appreciated.


texts: Is a Named Instance more Secure than the Default Instance?

texts:
I realize that you can change the default instance from the default port of 1433, and equally you can make named instances use static ports as opposed to dynamic ports, but in general are named instances more secure than the default instance against external intruders?

Here's why I would think that's the case:

Named instances are (again, by default I realize) utilizing dynamic ports.  If you move the SQL Browser service to listen on a different port than 1434 then that would be even better, I imagine.
A default instance is pretty easy to figure out as the data source.  It's just the server's name.  But the named instance is an extra step to retrieving the data source with the serverName\instanceName schema.

What are some thoughts on this?  Am I way out in left field and it makes no difference?


texts: Sql Server - Multiple errors regarding to memory in error log

texts:
MSSQL-Server 2017 Express Edition runs on my Linux server.
The server has 4GB RAM.
The total size of DB is 300MB.

My website and the sqlserver are on the same server, it's a small website with max of 400-300 visitors a day.
Memory usage in linux looks fine, mssql takes only 20% memory:

Some additional sql memory information:

Maximum server memory is set to 2147483647 which is the default I guess.

The errors I get:

After restarting the mssql-service there are no problems for something like 2 days and then it repeats it self.

I would happily get an answer that will solve this for me, but I would also like to get a resource where I can learn about memory allocation/cache/pages/ related to sql server because I am not a sql expert, I am a programmer.


texts: How to shutdown mongodb sharded cluster temporary

texts:
I have setup mongodb sharded cluster with following topology

3x3 shards (each have 2 replica sets)
1x3 config server replica cluster
2 mongos routers

I want to shutdown whole cluster temporary keeping data integrity intact.
I may use this setup later so I do not want to scrap whole setup.

Any suggestions would be appreciated.


texts:
Im trying to shard my collection. when I execute the command:

  sh.enableSharding("testdb")

I get:

{
    "ok" : 1,
    "operationTime" : Timestamp(1552083125, 2),
    "$clusterTime" : {
        "clusterTime" : Timestamp(1552083125, 2),
        "signature" : {
            "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
            "keyId" : NumberLong(0)
        }
    }
}

Now when I try to shard my collection with the command: "sh.shardCollection("testdb.testcollection", {testkey:1})"

I get the error:

{
    "ok" : 0,
    "errmsg" : "Cannot accept sharding commands if not started with --shardsvr",
    "code" : 193,
    "codeName" : "NoShardingEnabled",
    "operationTime" : Timestamp(1552083136, 4),
    "$clusterTime" : {
        "clusterTime" : Timestamp(1552083136, 4),
        "signature" : {
            "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
            "keyId" : NumberLong(0)
        }
    }
}

I checked the status of the shard and it looks like it was successful:

sh.status()
--- Sharding Status ---
  sharding version: {
    "_id" : 1,
    "minCompatibleVersion" : 5,
    "currentVersion" : 6,
    "clusterId" : ObjectId("5c82df7b7a98360e9dd3c5da")
  }
  shards:
        {  "_id" : "shard0000",  "host" : "127.0.0.1:27023",  "state" : 1 }
        {  "_id" : "shard0001",  "host" : "127.0.0.1:27024",  "state" : 1 }
  active mongoses:
        "4.0.6" : 1
  autosplit:
        Currently enabled: yes
  balancer:
        Currently enabled:  yes
        Currently running:  no
        Failed balancer rounds in last 5 attempts:  0
        Migration Results for the last 24 hours:
                No recent migrations
  databases:
        {  "_id" : "config",  "primary" : "config",  "partitioned" : true }
        {  "_id" : "testdb",  "primary" : "shard0000",  "partitioned" : true,  "version" : {  "uuid" : UUID("ed327ab3-1de9-4a0a-89f5-8d0bf81015fc"),  "lastMod" : 1 } }

So why do I get that error when I try to shardCollection? I running all of this in the mongos instance


texts: How to add table to database inventory in sqlplus oracle 12c

texts:
Problems

How to create database Inventory database and inside it table Items in sqlplus
oracle 12c .
Details

Im beginner on Oracle 12c release
 i setup oracle and i create database Inventory

Now how to add table items to database Inventory

i can create table but how to create it inside database Inventorty
this actually what i need

Create table Items
(
itemno number(4) not null,
itemname varchar2(40) not null
);


texts: Postgres slow select query on another table after other table alter and update

texts:
I have postgresql 9.1.14 (upgraded a few weeks ago from 9.1.3, but no problems up till now).

I've recently altered several tables by adding a new UUID column and have been updating these UUIDs to match another unrelated system. This was a large (around 100'000) update for the system, which only usually has small changes.

One of the tables I updated had a trigger on it to update another table (which is used for querying) this table now runs SELECT queries upwards of 10+ times slower than it did before the update. The original tables I changed do not show any slowdown for select statements. The queries are not complicated, for instance:

SELECT iwork_id,
FROM cofk_union_queryable_work
WHERE date_of_work_std_year > 1800;

I've tried reindex, vacuum and analyse this table but no change. The table has only ever had a couple of indexes related to IDs, not to the columns which are queried on.

Running EXPLAIN (ANALYSE, BUFFERS, VERBOSE) on this database:

 Seq Scan on public.cofk_union_queryable_work  (cost=0.00..357332.10 rows=7198 width=4) (actual time=9451.804..32923.487 rows=7434 loops=1)
   Output: iwork_id
   Filter: (cofk_union_queryable_work.date_of_work_std_year > 1800)
   Buffers: shared hit=857 read=354888
 Total runtime: 32924.968 ms

This table contains about 100'000 entries.

Any thoughts on other lines of investigation?


texts:
I was running different postgresql versions. And accidentally they were removed. they contained several databases and I want to recover them. I'm wondering if there'a a command line in linux that allows me to recover lost data.

Your help would be so precious!

Thank you in advance


texts: How to write a query to identify common combinations of parameters used to call a stored proc?

texts:
recently I deployed a change into our production environment that ALTER'd an existing stored proc and essentially all the addition did was INSERT into a table I created the parameters passed in by the user calling that stored proc.

Example: User from finance passes in 15/20 possible parameters in his front-end application.. those exact parameters are first INSERTed into my table where each column is a parameter.  If the user enters in nothing for a given parameter (because they are optional) the cell will be empty (not NULL).

So again, I need to aggregate and figure out what is the most common combination of parameters that are actually used (I do not care about the actual values, just that the parameter was used or not).

To clarify my table.. Each row is one stored proc call and each column is a parameter.

My table:


texts: How to troubleshoot hung queries in PostgreSQL RDS?

texts:
I have strange behavior from an Amazon RDS instance running PostgreSQL 9.6.11. This is new behavior from a system that has otherwise been behaving properly without any changes to code in the last few months. Additionally, we have a staging environment that the latest database and code changes were replicated to and it is not having these problems.

Nightly, thousands of jobs are run against the database server that consist mainly of very large SELECT queries (In the order of 8000 lines or so) that calculate statistics. Queries that typically take up to 1 minute, hang up, and do not complete, even after hours. However, if the server is rebooted to clear the queries, then I can run the same queries manually and they complete normally. The jobs will continue to process normally, and then eventually hang up again on stuck SELECT statements (usually 5 or 6, and it is not the same statements. They all vary slightly.).

I have done a lot of research and troubleshooting, but after determining that the query wasn't the problem, I'm at a loss.

The hung statements are all in active state and have been processing for hours. The server is pegged at 99%+ load.

What things should I look for? What problems could cause this issue? Why would a combination of large SELECT statements hang up? What commands can I run to get more information?


texts: Are there better alternatives rather than using a "Full outer join" when it is not possible to use a "left or right join"?

texts:
I have two tables with below structure :

 Create table Modern_transaction
 (Eeffective_date   Date,
  Customer_Num      Number,
  Branch_Code       Number,
  MTrns_type_one    Number,
  MTrns_type_two    Number)

 Create table Card_transaction
  (Eeffective_date   Date,
   Customer_Num      Number,
   Branch_Code       Number,
   CTrns_type_one    Number,
   CTrns_type_two    Number)

I need to have the data of both table in one table with this structure:

Create table Customer_transaction
  (Eeffective_date   Date,
   Customer_Num      Number,
   Branch_Code       Number,
   MTrns_type_one    Number,
   MTrns_type_two    Number
   CTrns_type_one    Number,
   CTrns_type_two    Number)

some example data :

                                Modern_transaction
 ----------------------------------------------------------------------------------
   Eeffective_date | Customer_Num  | Branch_Code | MTrns_type_one  |  MTrns_type_two
      9/22/2013    |       x       |       12    |       10        |      20
      9/22/2013    |       y       |       13    |       20        |       0
      9/22/2013    |       z       |       18    |       12        |      12

                                Card_transaction
 ----------------------------------------------------------------------------------
   Eeffective_date | Customer_Num  | Branch_Code | CTrns_type_one  |  CTrns_type_two
      9/22/2013    |       x       |       22    |       10        |      20
      9/22/2013    |       y       |       13    |       20        |       0
      9/22/2013    |       y       |       12    |       20        |      20

The point is that I'm using a full join for this :

 select nvl(mt.Eeffective_date,ct.Eeffective_date),
        nvl(mt.Customer_Num ,ct.Customer_Num),
        nvl(mt.Branch_Code ,ct.Branch_Code),
        nvl(mt.CTrns_type_one,0),
        nvl(mt.CTrns_type_two,0),
        nvl(mt.MTrns_type_one,0),
        nvl(mt.MTrns_type_two,0)
 from Modern_transaction mt full join
      Card_transaction  ct
   on mt.Eeffective_date = ct.Eeffective_date and
      mt.Customer_Num    = ct.Customer_Num
      mt.Branch_Code     = ct.Branch_Code

according to many references , full join is harmful and it's not suggested. for example take this for example https://weblogs.sqlteam.com/jeffs/2007/04/19/full-outer-joins/. I wan to know is it always bad and harmful to use  full outer join? I mean do yo consider full join as your last solution ? Are there better alternatives for writing this query? In which circumstances will full join produce false or duplicate result?Using a left join is not possible here cause non of the tables above could be considered as the base table.

Update :
  The primary key of both tables is the combination of Eeffective_date ,Customer_Num,Branch_Code

thanks in advance


texts: SQL Server - Very high disk activity after running query?

texts:
I'm running SQL Server Express 12.0.4100 on an AWS instance with 16 cores and 64 GB of RAM with a 3TB and 9000 IOPS EBS attached. This has been running for 2 years perfectly with no issues until this week.

The server is running a web app that receives 5-10 requests per second and each request is translated to the same query to the database, only with different parameters (this is all handled by an ORM). These queries (let's call it the GetProduct query) are kind of large because they retrieve data from 13 tables to build a single JSON response that is sent back to the user. The query usually takes between 800 and 1500ms to run.

This week I noticed that when I executed a query, a simple select top 1000 with a single where condition in a table that is also scanned by the GetProduct query the Database I/O usage reported by SQL Management Studio goes up fluctuating between 30MB/s and 50MB/s even when my query already returned (the normal database I/O is between 0.1MB/s and 1MB/s). I detected that suddenly my GetProduct query is taking now more than 60 seconds to complete! Causing timeouts and all the queries executed to the database becomes really slow. This is fixed if I restart the entire server box, everything goes back to normal until I run a query again which is very weird.

I'm far from a DB expert, I'm a programmer that is also in charge of maintaining this database, none of the team knows much of databases. I have updated statistics and noticed that fragmentation is really high on most of my indexes (between 95% and 99% as reported by SSMS). I'm planning a maintenance window so I can take down the app and rebuild all the indexes.

Does fragmentation cause this kind of behavior? I executed sp_whoisactive and I'm sure there's nothing else running on my DB, just calls to the GetProduct query taking a lot of time to complete.

UPDATE

I got the query plans using the "Recent Expensive Queries" section in the Activity Monitor of SSMS because sp_whoisactive @get_plans = 1 was returning a NULL query_plan for this queries.

I can't use Pastebin because the files are too big so I uploaded them to my Google Drive account:

Query plan when everything is working fine, query doesn't take more than 1500ms to run: https://drive.google.com/open?id=1x3esroDgkdwz5XeRXbDjA5ygmQc_sZOF
Query plan after running sp_updatestats, for some reason now other queries that were causing the issue doesn't make it happen anymore. After running sp_updatestats however it causes the issue, query plan with slow query: https://drive.google.com/open?id=1lWrlljgtrGnYPGLjCZe_Hq_uCNqaRHko

Here is the query that is being executed, the only parameter that changes is @p__linq__0 that is replaced by different UUIDs: https://pastebin.com/YnrCJVLW

Finally, here you can see the output of sp_whoisactive @get_plans = 1 when the queries are taking a lot of time to complete:


texts: Advanced threat detection vs IP restriction

texts:
If we configured IP restriction to allow only internal network, then why we need threat detection?
IP restriction already blocks connections from other resources. Moreover, advanced threat detection is not free.


texts:
I've got a question about a query.

I've got two tables. The first table has to columns, a start-date and an end-date.
For example start: 00:00:00 and end: 00:15:00

The first table period:

|---start---|----end----|
| 00:00:00  | 00:15:00  |
| 00:15:01  | 00:30:00  |
| 00:30:01  | 00:45:00  |
| 00:45:01  | 01:00:00  |
|-----------|-----------|

The seconds table tt contains following information: name,zeit,points

|---name---|----zeit----|---points---|
|   AAAA   |  00:11:00  |     5      |
|   AAAA   |  00:12:00  |     5      |
|   BBBB   |  00:08:00  |     9      |
|   BBBB   |  00:10:00  |     9      |
|   CCCC   |  00:13:00  |     8      |
|   AAAA   |  00:31:00  |     1      |
|   BBBB   |  00:37:00  |     3      |
|   AAAA   |  00:47:00  |     7      |
|   BBBB   |  00:50:00  |     2      |
|   CCCC   |  00:55:00  |     4      |
|----------|------------|------------|

I've used the following query to summarize the points of each team grouped by the period:

SELECT
   period.end,
   IFNULL(sum(tt.punkte), 0) punkte,
   tt.name
FROM period
   LEFT JOIN tt ON (tt.zeit < period.end AND tt.zeit >= period.start)
GROUP BY period.end,tt.name

With this query I get this result:

|---end----|---punkte---|----name----|
| 00:15:00 |     10     |    AAAA    |
| 00:15:00 |     18     |    BBBB    |
| 00:15:00 |      8     |    CCCC    |
| 00:45:00 |      1     |    AAAA    |
| 00:45:00 |      3     |    BBBB    |
| 01:00:00 |      7     |    AAAA    |
| 01:00:00 |      2     |    BBBB    |
| 01:00:00 |      4     |    CCCC    |
|----------|------------|------------|

As you can see the results table is missing the rows with end 00:30:00 and there are only two rows with end 00:45:00.

So I've tried everything to add these rows to the result:

| 00:30:00 |      0     |    AAAA    |
| 00:30:00 |      0     |    BBBB    |
| 00:30:00 |      0     |    CCCC    |
| 00:45:00 |      0     |    CCCC    |

How can I change the query to get these missing rows to my result?


texts: Are there known parallelism issues with Sql Server 2016?

texts:
Looking for anything such as known bugs in Sql Server 2016 SP1, CU4 and CU5 (both have same issue) on windows 2012 R2 on vmware. Esp. if where parallel threads wait on each other.

Issue:  After going production we have one specific query in an SSIS dataflow that was very poorly performing in 2016 (was fine in 2012).  So I found some indexes needed to be added so added them which cut down on runtime by about 80%. Then it started randomly freezing (say once or twice a day) never returning any rows. Doesn't seem to correlate to how busy the server is, locks, or data volume. We have very high cxpacket waits but who doesn't?  I'm running maxdop 8 with 12 virtual cores.

Activity monitor very often shows session id is blocked by same session id on 2016.  I never looked on 2012 to see if this happens. It also shows this when a query hangs.  No deadlocks.  I'm running with option recompile and default server maxdop 8 (database is maxdop 0).

Any ideas greatly appreciated.


texts:
[enterprisedb@pgdvdb1dr bin]$ 2017-06-20 11:34:41: pid 103313: WARNING:  could not open configuration file: "pgpool.conf"

2017-06-20 11:34:41: pid 103313: DETAIL:  using default configuration parameter values
2017-06-20 11:34:41: pid 103313: FATAL:  initializing pool password, failed to open file:"/mnt/hgfs/edb-postgres.auto/pgpool/staging/linux-x64/pgpool/etc/pool_passwd"
2017-06-20 11:34:41: pid 103313: DETAIL:  file open failed with error:"No such file or directory"

[1]+  Exit 3                  ./pgpool -n


texts: Unrolling a self-referencing table referred to by another table...?

texts:
What's a proper way to "unroll" a self-referencing table with a variable depth and concatenate the levels with an associated value from another table?

table 1: FILENAME

filename      folder_id  size
-----------------------------
toplevel1.txt 1          1
onedeep1.txt  2          2
twodeep.txt   3          4
toplevel2.txt 1          4
onedeep2.txt  4          3

table 2: FOLDERS

folder_id  folder_name  parent_folder_id
----------------------------------------
0          ROOT         NULL
1          temp         0
2          subdir1      1
3          subsubdir    2
4          subdir2      1

Desired output:

path                                size
----------------------------------------
temp\toplevel1.txt                  1
temp\subdir1\onedeep1.txt           2
temp\subdir1\subsubdir\twodeep.txt  4
temp\toplevel2.txt                  4
temp\subdir2\onedeep2.txt           3

Have tried (and failed) using while loops, ctes, and cursors.  Seems relatively straight forward, but I can't get a handle on the variable depth of the paths...


texts: Tag system for multiple Item types and groups Schema

texts:
I have a groups table and users table, and a many to many relationship between them.

In my app, there are different modules, like question, product,suggestion , etc.

Users can create products/questions/suggestions for a group. Each of the mentioned items belong to a group.

I want to implement a tagging system for these modules. A tag is created by a user, and is specific to only that group. (if group A creates a tag called 'disco', group B should not be able to search for it)

All of the above modules can have the same tag (I would also like to show all the modules when you search for a given tag)

How can I implement this ?


texts:
I have a couchbase bucket, in it I have an Integer data with a key.

I am incrementing it, using the API "increment" so I cannot put a JSON there with the date of the last updated.

The question is, how can I select ALL element that where changed today/yesterday?


texts:
I'm experiencing a very weird problem with userstore_tokenperm clerk recently.

We have an environment with some databases in contained mode executing in version SQL Server 2017. Anyway i'm facing this problem since the SQL Server 2016.

At a certain moment this cache starts to grow at a rate of 6GB by day. When this ocurr we have to do a failover and restart the principal node of AG.

Run DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') does not clear the cache. We are considering to use the trace flags 4621 as suggested by microsoft to avoid this memory pressure, but i want to know if someone has a tip to help me  to understand this behavior. This seems like a memory leak, because even running DBCC FREESYSTEMCACHE the objects are not released from memory.

We have around to 70 users in contained mode in this server and when the cache starts to grow it grow until starve the server for memory.

I tried to find out what kind of class of token is present and the worse instance have only this entrys.

In sys.dm_os_memory_clerks we already faced more than 30GB of consumption

Someone please can help with some troubleshooting hint or even with an experience about this kind of problem.

Thks in advance


texts: It's a good practice always restore controlfile before restore the database?

texts:
I'm beginner in Oracle, but I'd like know if it's a good practice always restore the control-file of a backup-set before restore the database. I ask this because always when I restore the control-file I need open the database with reset-logs option and I don't know if it's a good practice to do this.

If the answer is no, so when I need restore the control-file?


texts: Sybase ASE 16.0 BCP in character problem

texts:
I am using SYBASE ASE 16.0 and in my tables i have some values like

  "KOCAELİ", "İZMİR", "TALATPAŞA"

. They are readable in csv file however when i put database with bcp in command, i have mistakes which are

  "KOCAEL0", "0ZM0R", "TALATPA^A

my command is :

bcp ME_DEV_TRUNK_DB..aktar2 in list.csv -b10000 -c -t',' -r\\n -Usa -SMYDB1 -Jiso88599 -F2

How can i fix and input data into database?


texts:
I have a table which has 300 columns in it.  Each column is up to 255 bytes (there is a business justification for this).

When I try it create it using VARCHAR(255), I go past the limit for the maximum number of bytes.  So I create is using 300 TEXT fields.  When I then try and insert data, I get the error:

  Row size too large (> 8126).
  Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help.
  In current row format, BLOB prefix of 768 bytes is stored inline.

After reading up on this, I tried to change the table to use Barracuda format by specifying ROW_FORMAT=COMPRESSED.  The issue now seems to be that when I try and create the table using that format, I get the same error.

CREATE TABLE T_ObjLarge__c (Id VARCHAR(18), Name VARCHAR(80),
ObjLarge_Field_1__c TEXT,
ObjLarge_Field_2__c TEXT,
...
ObjLarge_Field_300__c TEXT
) ROW_FORMAT=COMPRESSED ;

The error I get is:

  Row size too large (> 8126).
  Changing some columns to TEXT or BLOB may help.
  In current row format, BLOB prefix of 0 bytes is stored inline.

I am using MySQL 5.5.31 on Linux mint.  No indexes.  I have tried DYNAMIC format; it behaves the same way.

Output of SHOW GLOBAL VARIABLES LIKE 'innodb_file_per_table';:

+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+


texts:
I have a table in Postgres and trying to query a JSON type columns data_meta & source_data
request_id               | bbb1-bdfa3ae765a2
job_type                 | Incoming
data_meta                | {"data_type": "Insurance", "technology": "XYZ", "timestamp_column_name": "JOBTIME"}
source_data              | [{"vendor": "ABCD", "name": "MAPPING_FILE", "data_type": "Insurance"}]
status                   | OK

SELECT * FROM   job_request_table p, json_array_elements(p.data_meta) as element
where element->'data_type' ='Insurance';

and getting this:
ERROR:  operator does not exist: json = unknown
LINE 2: where element->'data_type' ='Insurance';
                                   ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

will there be a change in query if I have to search for vendor='ABCD' in another JSON type column "source_data"


texts:
I have questions.
What is best strategy for backup on streaming replication(hot standby) environment.
I’d like to do backup on slave server and restore(PITR).

First, I set up streaming replication(hot standby).

I tried backup using barman tool on slave.
But barman tool couldn’t execute on slave because pg_start_backup() can’t be executed on slave sever(read only).
I tried backup using pg_basebackup on slave.
But pg_basebackup is not included wal and wal archive.
Because I need a PITR(point-in-time-recovery), I tried to copy wal and wal archive from master to slave.
It was successful but was complicate and also inefficient.

Anyone have good strategy for backup on streaming replication(hot standby) environment?

Also, If  backup is executed on master(about 100GB DB size), How much it has effect on master?

Thanks,


texts: Displaying an Estimated Execution Plan generates CXPACKET, PAGELATCH_SH, and LATCH_EX [ACCESS_METHODS_DATASET_PARENT] waits

texts:
I'm running Microsoft SQL Server 2016 SP2-CU6 (13.0.5292.0) on a 4 vCPU VM with max degree of parallelism set to 2 and cost threshold for parallelism set to 50.

In the mornings, when trying to display an Estimated Execution Plan for a SELECT TOP 100 query, I run into massive waits and the operation to render the estimated plan takes minutes, often times in the 5 - 7 minute range.  Again, this is not the actual execution of the query, this is just the process to display an Estimated Execution Plan.

sp_WhoIsActive will show either PAGEIOLATCH_SH waits or LATCH_EX [ACCESS_METHODS_DATASET_PARENT] waits and when I run Paul Randal's WaitingTasks.sql script during the operation it shows CXPACKET waits with the worker threads showing PAGEIOLATCH_SH waits:

*resource description field = exchangeEvent id=Port5f6069e600 WaitType=e_waitPortOpen waiterType=Coordinator nodeId=1 tid=0 ownerActivity=notYetOpened waiterActivity=waitForAllOwnersToOpen

The worker threads look to be bringing the entire stats table into memory (as those page numbers as well as subsequent page numbers shown from Paul Randal's query point back to clustered key for the stats table).  Once the plan does come back, it's basically instantaneous for the remainder of the day, even after I see most of the stats table attrition from cache with only various records remaining (that I assume were pulled due to seek operations from similar queries).

I would expect this initial behavior if the query was actually executing with a plan that used SCAN operators, but why is it doing this when evaluating execution plans only to arrive at a SEEK operator as shown in the plan linked above?  What can I do (aside from running this statement before office hours so my data is appropriately cached) to help improve performance here?  I'm assuming a pair of covering indexes would be beneficial, but would they really guarantee any changes in behavior?  I have to work within some storage and maintenance window limitations here, and the query itself is generated from a vendor solution, so any other suggestions (besides better indexing) would be welcome at this point.


texts: Verifying connection pooling server side with CONTEXT_INFO on

texts:
I'm working with a 3 tiered application, Microsoft Dynamics AX, where the middle tier maintains connections to a SQL Server. Several clients connect to this middle tier server.

The middle tier server typically has several connections open to the SQL Server, so I'm pretty sure they are being pooled, however there is no documentation available as to how this is implemented.

Normally we cannot relate SPIDs to users or client applications, but there is an option where we can set a registry key (specific to Microsoft Dynamics AX) which makes this information available in the context_info field of sys.dm_exec_sessions.

Again, there is no documentation about how this is implemented. The only information we have on this is a vague blog entry on MSDN.

The post mentions

  Adding  this information has a small performance overhead.

So as we don't know any of the implementation details such as:

Is the information somehow included in the connection string or is this done by SET CONTEXT_INFO?
When are connections reused?
What exact impact can be expected

Is there any way to determine server side how the connection pooling is working and what the impact of the context_info is?

update:
Using this query from here

SELECT des.program_name,
       des.login_name,
       des.host_name,
--       der.database_id,
       COUNT(des.session_id) AS [Connections]
FROM sys.dm_exec_sessions des
INNER JOIN sys.dm_exec_connections DEC
        ON des.session_id = DEC.session_id
WHERE des.is_user_process = 1
--AND des.status <> 'running'
GROUP BY des.program_name,
         des.login_name,
         des.host_name
--         ,der.database_id
HAVING COUNT(des.session_id) > 2
ORDER BY COUNT(des.session_id) DESC

I can see connection pooling is used.


texts: Dynamic auditting trigger without temporary tables

texts:
We currently have a trigger set up on a table to audit any data changes similar to this answer, which uses dynamic SQL to generate the audit rows.  In order to use the INSERTED and DELETED tables in the dynamic SQL they have to be inserted into temporary tables.

We are currently trying to limit our tempdb usage as it has been causing issues and this table is udpated frequently, and is often causing tempdb contention as the trigger is creating and dropping so many temporary tables.

Is there a way to audit data changes to this table without using temporary tables?  Obviously I can hard code in the columns myself into the trigger and audit tables, but I'd rather have a solution which doesn't need updating every time there is a schema change on the table.

I have been looking around and I expect the answer to be 'No you can't do this' but I thought it was worth an ask in case I was missing some obscure method (or newer feature as we are on SQL Server 2016). As you can guess the solution needs to have good performance, otherwise it will cause issues somewhere else!


texts:
So I am having SQL error: "Cannot perform an aggregate function on an expression containing an aggregate or a subquery." I know you will ask me to just put it on a variable but this piece of code is inside a view. SQL does not allow us to put variable inside a view.
SELECT top 1000
              ProductID,
              (
                NULLIF(
                  AVG(
                    CASE WHEN QtyShip_RAW_adjusted >= 0 and QtyShip_RAW_adjusted < MAX(QtyShip_RAW_adjusted) THEN QtyShip_RAW_adjusted END
                  )/COUNT(*),
                  0
                ) + SUM(
                  CASE WHEN QtyShip_RAW_adjusted >= 0 THEN QtyShip_RAW_adjusted END
                ) - (
                  SELECT
                    MAX(QtyShip_RAW_adjusted)
                  FROM
                    QtyShip_RAW_adjusted_test
                ) + (
                  SELECT
                    TOP 1 QtyShip_RAW_adjusted
                  FROM
                    (
                      SELECT
                        TOP 3 QtyShip_RAW_adjusted
                      FROM
                        QtyShip_RAW_adjusted_test
                      ORDER BY
                        QtyShip_RAW_adjusted DESC
                    ) AS Comp
                  ORDER BY
                    QtyShip_RAW_adjusted ASC
                )
              ) / COUNT(
                CASE WHEN QtyShip_RAW_adjusted >= 0 THEN 1 END
              ) as AverageLessPeakWeek
            FROM
                QtyShip_RAW_adjusted_test
            GROUP BY
              ProductID

I already tried putting in a derived table.
FROM
(
                  SELECT
                    MAX(QtyShip_RAW_adjusted) as MaxQty
                  FROM
                    QtyShip_RAW_adjusted_test
) t

But putting it inside derived tables gave me more errors.
                    CASE WHEN QtyShip_RAW_adjusted >= 0 and QtyShip_RAW_adjusted < t.MaxQty THEN QtyShip_RAW_adjusted END

I already made some google search and nothing seems to be working. Thanks Guys!


texts: mysql show processlist hanging processes;

texts:
We needed to move services from one co-location patner to another so we had to copy some files from main server to back up server so these services can now be run on back up server at the new location.

The Systems Admin did a replication of the mysql database. However, since moving the servers, the users created do not work. I cannot login as of the database users created after moving the servers.

when i do a show processlist; i see some two processes that have been hanging for a very long time; I believe they have to do with the replication process.

even when i try to make a change to the password of any of the original users, they too are affected and cant now login.

mysql> show processlist;
+-------+-------------+-----------+-------+---------+---------+-----------------------------------------------------------------------+------------------+
| Id    | User        | Host      | db    | Command | Time    | State                                                                 | Info             |
+-------+-------------+-----------+-------+---------+---------+-----------------------------------------------------------------------+------------------+
|     1 | system user |           | NULL  | Connect | 2156939 | Connecting to master                                                  | NULL             |
|     2 | system user |           | NULL  | Connect | 2156939 | Has read all relay log; waiting for the slave I/O thread to update it | NULL             |

I don't know if these processes are the one's affecting my login. But I need to clean them just because they're annoying.

Someone please show me how to

get rid of these two processes;
troubleshoot possible reason for my new users not being able to login.

Thanks.


texts: Configuring multiple listeners on SQL Server 2012 through High Availability

texts: We have configured two SQL servers on Azure and they are in HA. There are about 10 databases and we wanted to have two critical ones on one dedicated HA group. The listener worked fine until we configured another HA on the same servers and tried to have another listener on the same box. This caused the first listener to fail and am wondering if its possible to have multiple listeners on different HA groups within the same SQL servers.


texts:
It may not be possible, but without doing a JOIN from the inserted/updated from the records coming in and adding them to an audit table column-by-column, is it possible to just capture the full DML query text, similar to using the below logic when a DML transaction is run against a table:

IF ((@icount > 0) AND (@dcount = 0))
BEGIN

    SELECT @querytext = text
    FROM sys.dm_exec_requests er
        CROSS APPLY sys.dm_exec_sql_text(sql_handle)
    WHERE session_id = @@SPID

END

IF ((@dcount > 0) AND (@icount = 0))
BEGIN

    SELECT @querytext = text
    FROM sys.dm_exec_requests er
        CROSS APPLY sys.dm_exec_sql_text(sql_handle)
    WHERE session_id = @@SPID

END

When I use the above logic and try to insert the @querytext variable to a table, it is NULL or passes in the trigger text - meaning that its context is not the actual insert or update, so it may not be possible to capture the full text and have to do a column-by-column DML save.


texts: Designing a user table, FName LName v.s Username

texts:
I did some research on different ways of getting user's name. Some database designers prefer to have a username that contains first name and last name with space separator, and some other designers have it in two separate fields such as fname and lname. When it comes to searching using mySql, which approach makes it easier to search for a user based on his full name, or only either his last name or first name ? Any advice on the design approach ? My application is probably going to have about 5,000,000 users registered, I'm a bit concerned about in what way to approach user full name.

This is how I'm doing it, assuming that the user enter "alex coner" in one input textbox and press enter, then I parse the string based on space separation and then run the query like this which doesn't know which one is fname and lname :

select * from User
where UserFName in ("alex", "coner")
   OR UserLName in ("alex", "coner")

is there a way to run this and then get the one that matches the closest ? Assuming there is these users as below:

alex buche
coner johnson
alex coner
david rudd

and the result appears as :

alex coner
alex buche
coner johnson


texts: Do Large Hash Joins Contribute to Stolen Pages?

texts:
On a SQL Server 2014 SP1 Enterprise Instance, I'm performing an outer join between two partitioned tables on a numeric(16,0) + char(1) composite key.

100 GB is the max server memory setting on a 128 GB host. Per the execution plan, the outer table is accessed with a non-clustered index of 47 GB and 2.9 billion rows, and the inner table is accessed with a non-clustered index of 36 GB and 3.9 billion rows.

I'm observing with Quest Spotlight a huge stolen page value of about 30 GB while proc cache is only about 3 GB. Is the nature of this join contributing to the stolen pages and will they age-out of the buffer cache following the same rules for least-recently-used pages or a different algorithm?


texts:
Every 15 seconds or so, the application I am trying to profile fires:

@@TRANCOUNT > 0 COMMIT TRAN
IF @@TRANCOUNT > 0 COMMIT TRAN
IF @@TRANCOUNT > 0 COMMIT TRAN SET IMPLICIT_TRANSACTIONS OFF
IF @@TRANCOUNT > 0 COMMIT TRAN SET IMPLICIT_TRANSACTIONS OFF
elect top 1 accountId from account with (nolock)

Is this a regular SqlServer pattern?

And how I can configure the profiler so I do not see it?

IT is coming from the same process  and user as the process,user  for which I want to configure SQL profiling for.

Many thanks


texts: How can I restore a single record from a MySQL backup file?

texts:
Occasionally we have an issue where a record's previous data might get wiped while editing.

We backup our database every night for issues like this, however sometimes I am not available to do a restore immediately.

I've been asked to create a tool our editors can use to restore a record from a previous backup file.

I imagine this would look a lot like the a Webmin panel, which leads me to believe I may be reinventing the wheel. However, our editors find Webmin difficult to work with, so this isn't a solution for us.

Is anyone familiar with something we can use for the purpose of restoring a single record?

If not, are there any best practices I should abide by?


texts: How to ensure all records of a header are under the same fiscal year?

texts:
Say I have the following schema:

BUDGET_YEAR_TARGET
    INTEGER ID

BUDGET_DATE_TARGET
    INTEGER ID
    INTEGER BUDGET_YEAR_TARGET_ID--Foreign key
    DATE DATE

How can I ensure that any records of BUDGET_DATE_TARGET that share a BUDGET_YEAR_TARGET_ID will have DATEs under the same Fiscal Year (with Oct 1 as new year day)?

For example, this would be valid:

ID BUDGET_YEAR_TARGET_ID DATE
1  1                     Jan 1, 2020
2  1                     Jul 1, 2020
3  2                     Jan 1, 2020
4  2                     Aug 1, 2020
5  3                     Nov 1, 2020

If I were to insert a new record with a DATE of Dec 1, 2020, I'd like it to succeed if the BUDGET_YEAR_TARGET_ID is 3 but fail if it is 1 or 2.

How can I achieve that?


texts: Best event to track the DML operations being performed on Server along with the hostname performing those operations?

texts:
Currently I'm using the XE event sql_statement_completed to track the DML statements that are being executed and the hostname that is performing the same. However this event gets fired for each and every SQL statement, and has to be short-circuited later on, by setting the predicate to filter out statements that are NOT LIKE 'INSERT%', NOT LIKE 'UPDATE%' and NOT LIKE 'DELETE%'.
The event degree_of_parallelism gets fired only for the DML statements, however it does not provide the statement, only provides the sql_text. Setting collect_statement = (1) for degree_of_parallelism returns an error stating the "collect_statement" attribute does not exist for degree_of_parallelism. Is there a way around such that the event gets fired only for DML statements i.e. INSERT,UPDATE and DELETE, and the statement that fired the event is provided too?

Below is the sql_statement_completed event script that I have to use that is not very optimal:

CREATE EVENT SESSION [XE_Demo] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
   ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
WHERE ([SQLSERVER].[IS_SYSTEM]=(0) AND (([STATEMENT] like 'INSERT%' OR [STATEMENT] like 'UPDATE%' OR [STATEMENT] like 'DELETE%'))
ADD TARGET package0.event_file(SET filename=N'XE_Demo.xel',max_file_size=(102400),max_rollover_files=(10000),metadatafile=N'C:\XE_Demo.xem')
WITH (MAX_MEMORY=1048576 KB,EVENT_RETENTION_MODE=NO_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)

PS. I do know that sql_text also provides the statement that fired the event, however this sql_text is nothing but the DBCC input buffer and in case your statement consists of the keyword 'password' anywhere, your buffer would be flushed and you won't get access to the statement anymore.


texts: In what situation the .NDF files are created?

texts:
I'm not a db administrator but from a quick search I found .NDF files are secondary data files.
I'm curious to know under what conditions the .NDF files are generated ?
If it is generated, how we can know which data base is behind it ?


texts: Apply settings to multiple output columns in SSIS flat file source

texts:
Is there an easier way to apply property settings to multiple output columns in a SSIS flat file source, rather than manually applying to each output column? (SQL Server 2014.)  I am attempting to export data from a large text file source to an Azure database and the process is very slow.  I have read some tuning guides that suggest that the "FastParse" and "Length" properties should be set.  The problem is, my data source is columnar (which is transposed to rows further down the track) and it is tedious to set properties individually for a large amount of columns.

In general it would also be beneficial to know if it is possible to bulk-apply other such settings.


texts: get number of events per day from profiler trace

texts:
I am trying to do something that I thought would be simple but turns out it is not (for me).
I have 5 days of profiler trace files that I have imported to a single table.
I want to query the table and get the number of events per day.

I just want a simple result that looks something like this

StartTime count
2016-02-03 35600
2016-02-02 44000
2016-02-01 55055 etc...

so I found some examples but they don't seem to work.

select cast(StartTime as DATE) as StartTime, count(*) as [count]
from trace_table
group by StartTime

This is not working as I expected.  It's bringing back over five million rows with a count of only small digits for a count number.  I think I should get back five rows with a count for each day.  If there is an easier way to accomplish this I am very interested.
Thanks,
Don


texts: running DDL and DML statements on multiple customer Mysql databases

texts:
We have a dozen customers who each have their own database (they are all on the same RDS instance). The database schema is exactly the same for all customers.
When a new code deployment feature requires a new database table, is there a 'best practice' for creating this table on all customer databases?
We currently use a custom script that iterates over all databases and runs the 'create table xxxx' statement.
Was wondering if this is a common technique that is used, or if there's a better/safer way.


texts: What are the best practices for triggers to maintain a revision number on records?

texts:
I have an audit trail implemented on an application database that captures old value, new value, modification time, and user inserting it into another table. I'm now wanting to add a revision number to each table to make idempondency checks more efficient.

As it stands, the trigger that captures all the data is AFTER UPDATE. My understanding is that  if I want to update the row I'm operating on, I will need to use a BEFORE trigger. ie:

CREATE TRIGGER `Table_A_Audit_Trigger` BEFORE UPDATE ON `Table_A`;
FOR EACH ROW BEGIN;
SET NEW.revision = OLD.revision + 1;

Would it be better to have two separate triggers for this, or to move the insert into the BEFORE trigger as well?

EDIT: The code for the AFTER UPDATE trigger (as well as the table DDL itself) is created by the application layer, then run on the database (generally when the model is created or updated in the software). It would not be difficult to add creation of this trigger at the same time.

As I understand it, the downside to combining into a BEFORE trigger would be inconsistency if the audit insert succeeds, but the actual update fails. Putting everything in an AFTER trigger would require the use of an additional UPDATE on the target table, which further compromises performance, and can cause cascading issues as well.


texts: Table size unaffected after deleting 400k rows

texts:
When I ran the below query

select bytes/1024/1024,segment_name,owner from dba_segments where tablespace_name='DATA_L1' and segment_name='Step_Status';

I got the below result for a segment which is a table named 'Step_Status'

BYTES/1024/1024         SEGMENT_NAME                OWNER
-----------------------------------------------------------------
 3192                    STEP_STATUS                FLSTD04

It had about 18 million entries

select count(*) from flstd04.step_status;

  COUNT(*)
----------
  18178090

After deleting nearly 4 million enteries

select count(*) from flstd04.step_status;

  COUNT(*)
----------
  14114186

When I again checked the size of the table using dba_segments
I got the same result

select bytes/1024/1024,segment_name,owner from dba_segments where tablespace_name='DATA_L1' and segment_name='Step_Status';

  BYTES/1024/1024         SEGMENT_NAME                OWNER
    -----------------------------------------------------------------
   3192                    STEP_STATUS                FLSTD04

Why is it that even after deleting 4 million rows the size of the table is same?


texts: "freeing items" taking forever on inserts, updates and deletes

texts:
Recently I migrated all my MySQL database tables from MyISAM engine to InnoDB. I'm using the DotCloud MySQL service as my server (http://docs.dotcloud.com/services/mysql/).

After the migration, everything went fine for the first two weeks, but for a couple days I've been experiencing some problems.

At first, all my queries would lock under an update or insert at certain tables. With a SHOW PROCESSLIST; I could see that the locking queries had the state of "freeing items". I started to research and found this:

Why do MySQL threads often show "freeing items" status when the query cache is disabled?

I've changed the innodb_thread_concurrency to 32 as suggested, and that really did help in order to keep the website running well, which means that I'm not experiencing application locks any more.

The problem is that inserts and updates are still slow, some of them taking more than 60 seconds to happen and that is terrible. I'll try to put my database under maintenance and optimize or repair it, but I don't know if that is going to work really well.

Does anyone knows what I could do about it? Thanks!


texts: Log sizes unexpectedly large: log_reuse_wait_desc == "log_backup"

texts:
I've been reading posts and manuals all day, I need help.

My problem: transaction log sizes after data and transaction log backups will not shrink, and grew to be unexpectedly large.  The system is quiet; no programs/utilities updating the database.

What I expect to see is that after database file and transaction log backup, is to be able to shrink the log, that transaction log sizes decrease, and the log_reuse_wait_desc in sys.database, changes to == '0'

My environment:

SQL Server 2012 (SP1) - 11.0.3321.0 (X64), build 9200.
Configured w/high-availability mode; recovery set to full.
Database file size, 16Gb, Compressed backup 13GB.
3x log files: 20MB, 10GB, 104GB  (they all grew to 104GB, but I was able to shrink the first two)
Backups complete, no update jobs are running now … DBCC SQLPERF(logspace) == 112GB log size, 64% used
…and, ? Select from sys.database log_reuse_wait_desc == "log_backup"

I update the database with Sql stored procs, some integrated-services truncate/load jobs, and a few C# linq-to-sql programs.  No transactions…

Any expertise and guidance would be appreciated.  Thank you.


texts:
I have database mirroring setup between two SQL 2012 servers in two different datacenters.

For testing purposes in the "mirrored" datacenter I would like to create a working copy of the database. Copying the 100GB database from the backups of the primary DB is not really an option. So I would like to use my mirror as a source.

I understand I will have to suspend the mirroring in able to perform a backup.

My question is after I suspend, how can I backup the database, and easily continue the mirroring where I left off?

(Footnote: The database is not very active, so even if it is backing up 15 minutes, there is no big difference in transaction log.)


texts: Right way to restart Oracle 12c Server with pluggable databases

texts:
I am new to oracle. I installed Oracle 12c on windows with a pluggable database on a test VM. Everything was working fine. After the installation I was able to connect via EM Express on port 5500, via SQLPlus and via SQL Developer.

Them I restarted the server (windows shutdown and restart) and nothing worked. After some serious reading and a lot of trial and error I was able to connect again by starting the listener, the container database and the pluggable database. Also I had to restart the Listener service via the windows services application.

Now I have a couple of questions. Given my environment what is the recommended procedure to restart my oracle server? Is shutting down windows OK or should I shut down the oracle databases and listener before shutting down windows? What is the right startup process after a restart? Startup the container and pluggable databases and them the listener or vice versa?

All of those questions translate into just one: What is the right way (procedure and order) to shutdown and restart a windows server with Oracle 12c that has a pluggable database?

I have been reading about all those aspects but given my inexperience I haven’t found a guide about what to do in what order and why.

Thanks in advance!


texts: How does performance of SQL Server CE and SQL Server Express compare?

texts:
We have an application that runs on netbooks. It currently uses a SQL Server CE 3.5 database, and we are thinking of upgrading to SQL Server Express.

Just wonder if there are any benchmarks to compare the performance of SQL Server CE and SQL Server Express? Would it be too intensive for an atom netbook?

We are using SQL Server CE 3.5 and we are thinking of moving to SQL Server Express 2012.

UPDATE:
Why are we wanting to upgrade? Because we will now be using merge replication with the SQL Server Express database.

Also we don't want to limit ourselves. We might use stored procedures and triggers on the database because with the replication solution we will be redesigning parts of our system.


texts:
What is the behavior of Slony waiting for an event after lock set command?

Basically I am trying to do a switchover and it takes a lot of time. We are following these steps:

Lock Set
Wait for Event (timeout = 300)
Move Set (timeout = 300)
Wait for Event (timeout = 300)

The alt perl script we have with Slony source has the following sequence:

Lock Set
Sync
Wait for Event
Move Set

What should be the correct sequence of events and how can I make the switchover faster?


texts:
Is there a better way of accessing historical dbcc checkdb result history (if output logging was not used at runtime) than xp_readErrorLog?

EXEC xp_readErrorLog {0-20}, 1, N'CHECKDB';

SQL Server 2008 or later.


texts:
My question targets Postgres, but answers might just be good enough coming from any database background.

Are my assumptions correct:

Disks have a fixed block size?
RAID controller can have a differnt block size? Does one RAID block get split onto multiple real disk blocks?
The filesystem also has an independant block size which again gets split onto the RAID block size?
Postgres works with fixed 8k blocks. How does the mapping to the filesystem block size happen here? Are Postgres 8k blocks batched together by the filesystem?

When setting up a system is it best to have all blocks at 8k? Or do the settings not real matter? I was also wondering if some "wrong" block size settings could endanger data integrity in case of a crash? Maybe if a Postgres 8k block has to be split onto multiple disk blocks?

Or does nothing get batched together, and therefore I loose disk space with every mismatch between defined block sizes?


texts: T-SQL Purpose of MAX in this Group By Query

texts:
I have run into some pre-existing SQL that I'm having a hard time uderstanding.

SELECT
    MAX(I.Symbol) Symbol
,   MAX(I.Ticker) CUSIP
,   MAX(I.Name) Name
,   SUM(H.Quantity) TotalQuantity
,   SUM(H.MarketValue) TotalMarketValue
,   MAX(H.PriceLC) Price
,   MAX(I.CategoryCode5) BUY_SELL
,   MAX(I.EquivFactor1) PriceTgt
,   MAX(P.LastPrice) CurrPrice
,   MAX(I.AssetClass) Target
,   MAX(I.Industry) Industry
,   MAX(I.CategoryCode1) Risk
FROM
    HOLDINGS_SECURE H
,   INVESTMENTS I
,   PRICE P
WHERE
    H.Symbol = I.Symbol
    AND I.Product = 'stock'
    AND H.Quantity > 0
    AND I.CategoryCode5 NOT IN ('X', '')
    AND H.Symbol = P.Symbol
GROUP BY
    I.Symbol

Symbol, Ticker, Name, CategoryCode5, Assest Class, Industry, and CategoryCode1 are all varchar fields. The remaining fields are decimals.

My best educated guess is that somehow max is being used to avoid multiple grouping columns but how can this return the correct results?


texts:
When trying to install a semantic search example, I'm running into a problem.

There are some steps in the example to check if the 'new' Semantic Search and Full Text Search are installed.

Executing the following T-SQL code returns the version '11.0.1153.1.1' of the semantic database.

/* Verify the registration is succeeded */
SELECT * FROM sys.fulltext_semantic_language_statistics_database;
GO

And...the following T-SQL code returns 1, which indicates that Full-Text Search and Semantic Search are installed.

/* Check if Full-Text Search and Semantic Search are installed*/
SELECT SERVERPROPERTY('IsFullTextInstalled');
GO

I also registered the 'language_statistics_db'.

/* Register Language Statistics Database */
EXEC sp_fulltext_semantic_register_language_statistics_db 'semanticsdb';
GO

I'm executing the following T-SQL code to verify if the languages are installed correctly (this is were something odd happens).

/* Check available languages for statistical semantic extraction */
SELECT * FROM sys.fulltext_semantic_languages;
GO

This statement returns no rows, indicating that there are no languages installed. I tried reinstalling the database(s) but with no effect.

Can someone point out to me were I made a mistake?

UPDATE: After unregistering the semantic_language database and registering it using 'master' it worked. No idea why this worked. Why? Someone?

The code that worked for me:

/ * Unregistar language db */
EXEC sp_fulltext_semantic_unregister_language_statistics_db
GO

And registering:

/ * register_language db */
Use master
EXEC sp_fulltext_semantic_register_language_statistics_db
GO


texts:
Does anyone have experience of moving a database from an Azure SQL Basic, or Standard tier, to Serverless?

I'm currently investigating whether to do this for our development, UAT and staging databases, to hopefully save a bit of money. They're currently averaging between £5-£40/month each.

The workload for them seems ideal for serverless, a few hours use, then nothing for days / weeks at a time, but despite staring at lots of Microsoft's docs about DTU vs VCore pricing, I wouldn't say I feel entirely confident that the billing would actually be reduced if we did switch over.

Would switching to Serverless, at as similar a performance level as possible definitely reduce the bills? Has anyone got any real world experience of this working out?


texts: How do I specify that a column should be auto-incremented in pgAdmin?

texts:
I have started to learn pgAdmin III to manage a PostgreSQL database. But it wasn't an easy to use application.

If I create or have created a table with pgAdmin III, how can I add "auto-increment"-functionality on a column id that has type integer?


texts: Case insensitive search of CLOBs in Oracle 12

texts:
Question: What is the most performant way to do a case-insensitive search  in a CLOB column?

I've simplified the query to easily reproduce the problem. The filedata table can have millions of rows. Many rows can have the same fileid. The fileid column is indexed. In cases where a particular fileid matches many rows, the proc is really slow.

select * from filedata where fileid=2148102100 AND REGEXP_LIKE (dataClobfield, 'Some Text Here', 'i');

This query completes in 130 seconds. 16 rows are returned by this query, but there 400k rows matching the fileid value (in other words, if I removed the regexp_like where clause, I would get 400k with a fileid of 2148102100).

If I change this to

select * from filedata where fileid=2148102100 AND dataClobfield like '%Some Text Here%';

It completes in 32 seconds but the search is case-sensitive, which does not meet the requirements.

I've also tried

select * from filedata where fileid=2148102100 AND LOWER(dataClobfield) like '%some text here%';

This returns in 83 seconds... better than the regexp_like, but still not great.

Other things I've read about haven't worked: setting the session NLS_SORT=BINARY_CI (doesn't work with CLOBs), using the DBMS_LOB.instr function (case sensitive). Ideally I'd like to get this down to 30 seconds. Any ideas?


texts: How can I create a readable/writable clone of a log shipped standby/read only database?

texts:
I need to take adhoc copies of a development database (on SQL Server 2005) to another SQL Server and be able to read/write to that cloned database. As the SQL Servers are in different data centres, copying the 20GB database I'm working with and then restoring is pretty slow. I have therefore opted to use log shipping to get an up to date copy of the data on the destination server. I now need a method to clone the log shipped read only database to a database I can read/write.

How can I copy a log shipped database to writeable database and not break the log shipping chain?


texts:
I am implementing a sharding with MongoDB and have recently added another node, but the rebalancing of data is very slow. Servers have 64 Mac OS with RAM.

I need help on how I can run the mongod process faster.


texts: Is total_elapsed_time in DMV sys.dm_exec_requests completely inaccurate?

texts:
I am running SQL Server 2012 and am trying to put some queries together for monitoring using the DMVs.  However, when looking at the total_elapsed_time field in the sys.dm_exec_requests DMV, the numbers look way off.  Here's an example:

SELECT
  session_id, RunTime = CURRENT_TIMESTAMP,
  start_time, total_elapsed_time
FROM sys.dm_exec_requests
WHERE session_id = 284;

session_id  RunTime                 start_time              total_elapsed_time
284         2016-04-07 16:14:03.690 2016-04-07 16:08:14.587 1419976

By my calculations*, the elapsed time should be around around 349,103 -- not 1,419,976.  That's off by over a factor of 4.

* From the difference, in milliseconds, between the current time and the start_time i.e.
    SELECT DATEDIFF(MILLISECOND, '2016-04-07T16:08:14.587', '2016-04-07T16:14:03.690');

Here's the server info:

SELECT @@VERSION;

Microsoft SQL Server 2012 - 11.0.5592.0 (X64)
    Apr 17 2015 15:18:46
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Any ideas what could be causing this discrepancy?


texts: SQLAnywhere: Visualize in real time CPU and RAM consumption of sql connections

texts:
I want to keep track of which connection use most of resources.

Within Sybase Central (SQL Anywhere 9, 12), for each server and database, we have pane to check in real time All Connected Users and Connections.

How to check for each connect it's cpu and ram consumption (in real time)?