Please use yyyy-mm-dd to avoid confusion (mm-dd-yyyy vs dd-mm-yyyy) and be consistent with MySQL.

Does this give you the first item you want?

SELECT *
    FROM tbl
    WHERE ts < CURRENT_DATE()
    ORDER BY ts DESC
    LIMIT 1;

If so, does this give you the other two:

SELECT *
    FROM tbl
    WHERE ts > $foo
    ORDER BY ts
    LIMIT 2;

given that $foo is the timestamp you got from the first query.

I assume your ts is of datatype TIMESTAMP, not INT.  DATETIME would also do.

If you need to display dd-mm-yyyy, use DATE_FORMAT in the SELECT, but not for internal calculations.


Clustered columnstore indexes and foreign keys

You've got lots of questions in here:

Q: (The lack of foreign keys) confuses me a lot! It is a good practice (not mandatory) to have Fk's in the DWH for a variety of reasons (data integrity, relations visible for semantic layer, ....)

A: Correct, it's normally a good practice to have foreign keys in a data warehouse. However, clustered columnstore indexes don't support that yet.

Q: So MS advocates Clustered Column store indexes for DWH scenarios, However it can not handle FK relationships?!

A: Microsoft gives you tools. It's up to you how you use those tools.

If your biggest challenge is a lack of data integrity in your data warehouse, then the tool you want is conventional tables with foreign keys.

If your biggest challenge is query performance, and you're willing to check your own data integrity as part of the loading process, then the tool you want is clustered columnstore indexes.

Q: However SQL 2014 than adds no real new value for DWH??

A: Thankfully, clustered columnstore wasn't the only new feature in SQL Server 2014. For example, check out the new cardinality estimator.

Q: Why am I so angry and bitter about the way my favorite feature was implemented?

A: You caught me - you didn't really ask that question - but I'll answer it anyway. Welcome to the world of third party software where not everything is built according to your exact specifications. If you feel passionately about a change you'd like to see in a Microsoft product, check out Connect.Microsoft.com. It's their feedback process where you can submit a change, other people can vote it up, and then the product team reads it and tells you why they won't implement it. Sometimes. Most of the time they just mark it as "won't fix, works on my machine" but hey, sometimes you do get some answers.


SQL Server table appears locked for no reason

Based upon my investigation with sp_whoisactive (thanks @billinkc), it was a blocking issue with the Windows service accessing the table.  I found the issue in code and corrected it.

What is still unclear to me is why this was not an issue prior to the server reboot, why it suddenly started coming up.


How to get the ignored row while IGNORE_DUP_KEY is ON on SQL Server?

There would be additional overhead, but one option might be to create an instead of insert trigger which would check for duplicates first and route those to another table.

--demo setup
set nocount on
DROP TABLE IF EXISTS [dbo].[TestTable]
CREATE TABLE [dbo].[TestTable](
    [ID] [int] NOT NULL,
    [ExtraInformation] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Employee_ID] PRIMARY KEY CLUSTERED
(
    [ID] ASC
) with (IGNORE_DUP_KEY = ON)
) ON [PRIMARY]
GO

DROP TABLE IF EXISTS [dbo].[TestTableIgnoredDups]
CREATE TABLE [dbo].[TestTableIgnoredDups](
    [ID] [int] NOT NULL,
    [ExtraInformation] [varchar](50) NOT NULL
)
 ON [PRIMARY]
GO

--create INSTEAD OF trigger
CREATE TRIGGER TestTable_InsteadOfInsert ON dbo.TestTable
INSTEAD OF INSERT
AS
BEGIN
    --select rows to be inserted into #temp
    SELECT *
    INTO #temp
    FROM inserted

    --insert rows to TestTableIgnoredDups where primary key already exists
    INSERT INTO TestTableIgnoredDups
    SELECT t.*
    FROM #temp t
    JOIN TestTable tt
        ON tt.id = t.id

    --delete the duplicate rows from #temp
    DELETE t
    FROM #temp t
    JOIN TestTable tt
        ON tt.id = t.id

    --insert rows to TestTableIgnoredDups where duplicates
    --exist on the inserted virtual table, but not necessarily on TestTable
    ;WITH DupsOnInserted
    AS (
        SELECT id
            ,count(*) AS cnt
        FROM #temp
        GROUP BY id
        HAVING count(*) > 1
        )
    INSERT INTO TestTableIgnoredDups
    SELECT t.*
    FROM #temp t
    JOIN DupsOnInserted doi
        ON doi.id = t.id;

    ;WITH DupsOnInserted
    AS (
        SELECT id
            ,count(*) AS cnt
        FROM #temp
        GROUP BY id
        HAVING count(*) > 1
        )
    DELETE t
    FROM #temp t
    JOIN DupsOnInserted doi
        ON doi.id = t.ID

    --insert the remaining rows to TestTable
    INSERT INTO TestTable
    SELECT *
    FROM #temp
END
GO

--verify by trying to insert a duplicate row
insert into testtable(id,ExtraInformation) values(1,'RowOne')
insert into testtable(id,ExtraInformation) values(1,'RowOneDup')

select * from TestTable
select * from TestTableIgnoredDups

Data from TestTable

| ID | ExtraInformation |
|----|------------------|
| 1  | RowOne           |

Data from TestTableIgnoreDups

| ID | ExtraInformation |
|----|------------------|
| 1  | RowOneDup        |


Performing aggregations and calculations: SQL (CTE) vs MDX

Row-level YTD calculations on a database table are not very useful unless you have a unit of analysis for your YTD figures that can be processed additively (i.e. you're not going to find your reports trying to aggregate two YTD figures).  This essentially limits their usefulness to summary tables.

This type of aggregate table is of limited use with a cube, as the cube gives you the aggregation for free.  If you have a time dimension on the cube you can make calculated YTD measures that encapsulate the calculations. with an expression such as Aggregate (YTD([Date].[Date].[CurrentMember]), [Measures].[SomeMetric]).  You don't even have to specify the date.

The calculations you describe can all be done relatively easily in MDX with hierarchy functions such as PrevMember and ParallelPeriod, and this is more flexible and much less effort to implement than working off an aggregate table generated in the underlying database.  You're better off doing al the YTD and period-period comparisons in the cube.


I didn't get listener file get installed

SQL Developer can connect to Oracle databases without Oracle client and tnsnames.ora.

Creating a Database Connection Using SQL Developer

More details you can read at the above url.


Can we do the domain migration for 4 node SQL cluster?

Microsoft does not supports migrating cluster from one domain to other. Their is support article describing this.

This support article  clearly mentiones that

  Because of an increased dependence on Active Directory Domain Services, Microsoft does not support moving an already installed and configured Windows Server 2008, Windows Server 2008 R2 and Windows Server 2012 failover cluster from one domain to another. You must create a new cluster. Additionally, you must re-cluster highly available applications.

It would be better to rebuild the cluster from scratch in other domain.

In windows server 2016 you can have cluster nodes in 2 different domain


Is it possible to restore a database in a mirroring configuration?

Database Mirroring must be removed to perform the restore. Break the mirror, do the restore and then setup mirroring again.


Problem with aborted PT-online-schema change command

When Percona Online Schema Change runs, it creates triggers which synchronize the new table with updates that happen to the old table (with the previous table definition).

Before you run pt-online-schema change again, you have to delete these triggers.

In MySQL you can find the triggers by writing show triggers like '<table name>'\G.

And you can delete those triggers by typing

drop trigger <trigger name>;

You should only follow this recipe if your table definition does not have other triggers than those created by pt-online-schema change. Their names will start with "pt_osc_".


Error installing Oracle Database Client 12c: "OracleMTSRecoveryService Control Utility has stopped working"

The issue in my case was that a DLL file was missing that prevented "oramtsctl.exe" from running. I found when I attempted to run the oramsctl.exe manually from its installation folder. The missing DLL file was in the Microsoft Visual C++ Redistributable Packages, which I installed to solve the issue.


Step through multiple arrays in parallel with FOREACH

While FOREACH is very convenient to loop through a single array, it's not particularly useful to step through multiple arrays in parallel. Use a plain FOR loop with array_lower() / array_upper() instead:

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

Since you created the array in the function, we know the index starts with 1 (default). Else, use:

FOR i IN array_lower(UserResponseList, 1) .. array_upper(UserResponseList, 1)

The index i is defined automatically, the documentation:

  The variable name is automatically defined as type integer and exists
  only inside the loop.

Related cases with more information:

Postgres - array for loop
To ignore result in BEFORE TRIGGER of PostgreSQL?


This answer is intentionally Community Wiki to encourage others to edit in other useful links and information for troubleshooting connection problems.

Please try:

Troubleshooting: Timeout Expired
How to Troubleshoot Connecting to the SQL Server Database Engine

Both are Microsoft TechNet guides targeted at your type of problem.

Aaron Bertrand wrote a blog post listing many different error codes for login related connection errors

The Stack Overflow Q & A Help troubleshooting SqlException: Timeout expired on connection, in a non-load situation provides lot of good pointers to check on a box facing this type of issue.


From the Oracle documentation

  When DML changes are made to master table data, Oracle Database
  stores rows describing those changes in the materialized view log and
  then uses the materialized view log to refresh materialized views
  based on the master table.1

So you should be able to see what changes are invalidating the MV.

Next you should examine the alert log to see if anything has been logged.

I wonder if you have a complex chain of materialized views?  Does one depend on another? Is only one becoming invalid and others are not?  Try this query to see what depends on what

dbms_snapshot.get_mv_dependencies(
list    IN  VARCHAR2,
deplist OUT VARCHAR2);

I would not recommend your suggested workaround until you understand what changes are invalidating the view.

Edit: Metalink note 264036.1 says:

"This is expected behavior. When there is a DML on the master table, all the MVs based on this table are marked as INVALID. Though the status is INVALID, you will be able to query the mview.
However, the query on MV will not return latest update done in master table unless MV is refreshed."

From the way you are using the Materialized Views I am not sure why you cannot issue a refresh of each view that is marked invalid.


What is disadvantage of disabling lock escalation in SQL Server 2016

As Erik mentioned and most experts including Microsoft books online online suggest reducing batch size/change isolation level/rewrite query but not disabling lock escalation.

Ref:

How to resolve blocking problems that are caused by lock escalation
in SQL Server
Lock Escalation (Database Engine)

Lock Escalations are an optimization technique used by SQL Server to control the amount of locks that are held within the Lock Manager of SQL Server.

Lock escalation serves a very useful purpose in SQL Server by maximizing the efficiency of queries that are otherwise slowed down by the overhead of acquiring and releasing several thousands of locks. Lock escalation also helps to minimize the required memory to keep track of locks. The memory that SQL Server can dynamically allocate for lock structures is finite, so if you disable lock escalation and the lock memory grows large enough, attempts to allocate additional locks for any query may fail and the following error occurs:

  Error: 1204, Severity: 19, State: 1 The SQL Server cannot obtain a
  LOCK resource at this time. Rerun your statement when there are fewer
  active users or ask the system administrator to check the SQL Server
  lock and memory configuration.

Escalation Threshold for an Instance of the Database Engine

Whenever the number of locks is greater than the memory threshold for lock escalation, the Database Engine triggers lock escalation. The memory threshold depends on the setting of the locks configuration option:

If the locks option is set to its default setting of 0, then the lock
escalation threshold is reached when the memory used by lock objects
is 24 percent of the memory used by the Database Engine, excluding
AWE memory. The data structure used to represent a lock is
approximately 100 bytes long. This threshold is dynamic because the
Database Engine dynamically acquires and frees memory to adjust for
varying workloads.
If the locks option is a value other than 0, then the lock escalation
threshold is 40 percent (or less if there is a memory pressure) of
the value of the locks option.

Similar question was asked before, answer was not accepted but has some useful information.

drawbacks of disabling lock escalation


This is absolutely normal when you try to perform a complete recovery from a hot backup, because that is how hot backups work, you will never have the latest changes in your backups.

Your backup includes archivelogs up to sequence 29. The next changes were in log sequence 30, but log sequence 30 became the current redo log at the time your backup was finished, so you do not have any backup of that. This is not a problem, this is how a hot backups work, you will never have backups of the latest log sequence. Obviously, the next hot backup will back up that, but by the end of that backup, another sequence becomes the latest sequence.

If you simply try recover database;, RMAN tries to recover the database to the most current state, and it does not know where to stop (except when you have not only the archivelogs, but the redo logfiles also). Your last log sequence in the backup is 29, RMAN will complain about the next one, because it does not know where to stop.

If you want to avoid the above error, you can explicitly specify RMAN where to stop (perform a point-in-time recovery). For example:

recover database until sequence 30;

This would have had the same result as your recover command, without throwing an error. You can also specify UNTIL SCN and UNTIL TIME.

30 because:

  When specifying log sequences, if the last created archived redo log
  has sequence n, then specify UNTIL SEQUENCE n+1 so that RMAN will
  apply n and then stop.

Or, you can just ignore this error and continue with alter database open resetlogs.


Sort by ID when ID < 1000, otherwise sort by name

@ypercubeᵀᴹ has the correct answer in a comment.
To have this always be correct -- including cases when the names start with digits -- you need to have a two column order by clause as follows:
SELECT *
FROM table1
ORDER BY
  CASE WHEN Id < 1000 THEN Id ELSE 1000 END,
  Name;

Observe that all Id's above 1000 are assigned the same value so that then the second column comes into play.


You've answered with your options. Either click and edit each individual annotation or open with your favorite text editor and be prepared to write XML safe comments i.e. runs when sales_amount &gt; 30
Now that I have cracked open a 2017 version SSIS Package, the annotation XML is far friendlier for text editing than it was in the 2005/2008 era.
Given a package that looks like

That Inner Annotation > 30 is out in the "clear" down in the DTS:DesignTimeProperties in a CDATA field. Update all the Text attributes, save and reopen and you should be good.
    <AnnotationLayout
      Text="Inner Annotation &gt; 30"
      ParentId="Package\SEQC Placeholder"
      Size="138,60"
      Id="4b28de53-9db6-47c2-957c-21118893f6dc"
      TopLeft="34,25">
      <AnnotationLayout.FontInfo>
        <mssge:FontInfo>
          <mssge:FontInfo.TextDecorations>
            <av:TextDecorationCollection />
          </mssge:FontInfo.TextDecorations>
        </mssge:FontInfo>
      </AnnotationLayout.FontInfo>
    </AnnotationLayout>

Normally, I'd put a plug in for Biml as you can reverse engineer a package to a simplified XML markup, edit there and re-generate your SSIS package.
This is what the above package would look like (assuming I imported the SSIS Annotations which is not a default)
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="dba_288939" Language="None" ConstraintMode="LinearOnCompletion">
            <Annotations>
                <Annotation Tag="Varigence.Biml.SsisImporter.ImportedFromPackage">dba_288939</Annotation>
                <Annotation Tag="Varigence.Biml.SsisImporter.OriginalName">dba_288939</Annotation>
            </Annotations>
            <Tasks>
                <Container Name="SEQC Placeholder" ConstraintMode="LinearOnCompletion">
                    <Annotations>
                        <Annotation AnnotationType="Description">Sequence Container</Annotation>
                    </Annotations>
                    <Tasks>
                        <Dataflow Name="Data Flow Task">
                            <Annotations>
                                <Annotation AnnotationType="Description">Data Flow Task</Annotation>
                            </Annotations>
                        </Dataflow>
                    </Tasks>
                </Container>
            </Tasks>
        </Package>
    </Packages>
</Biml>

However, the emitted SSIS package doesn't have annotations in it. I'll reach out to the fine folks as Varigence and see what I'm doing wrong in the package generation phase.
Visual Studio 2019 annotation edit experience


Querying an enormous table (~150 million rows) with multiple joins - SQL Server

Try running the cases seperately.  If that's faster you can work on combining them into one query.  eg something like the not strictly-equivilent:

SELECT rt.VehicleID,rt.PersonalAutoCovID,SUM(CAST(rd.OutputValue AS INT)) VehicleScore
FROM #RatingDataTemp1 rt
INNER JOIN modelingdata md
    ON md.BranchID = rt.BranchID
    AND (rt.EditEffectiveDate >= md.EffectiveDate )
    AND (rt.EditEffectiveDate < md.ExpirationDate )
GROUP BY rt.VehicleID,rt.PersonalAutoCovID

and

SELECT rt.VehicleID,rt.PersonalAutoCovID,SUM(CAST(rd.OutputValue AS INT)) VehicleScore
FROM #RatingDataTemp1 rt
INNER JOIN modelingdata md
    ON md.BranchID = rt.BranchID
    AND md.EffectiveDate is null
    AND md.ExpirationDate IS NULL
GROUP BY rt.VehicleID,rt.PersonalAutoCovID

And if the target date range is small, consider changing the #RatingDataTemp1 to have one row per day per BranchID and doing a straight inner join on both columns.


NDB Cluster seems to ignore foreign_key_checks=OFF

Mock tables were how mysqldump were loaded in older versions of MySQL a long time ago

Issue like this existed

load of mysqldump script may fail with error 1215 if dest already has tables
Foreign keys: mysqldump should respect foreign keys

See that last line

2014-11-03 18:01:29 22485 [Note] NDB FK: Created mock table 'NDB$FKM_20_0_aclRole' referenced by 'aclGroup'

Since mysqldumps were in alphabetical order by default, there might be some cases where the parent table of a foreign key relationship would appear after the child table rather than before. As a consequence, the mock table was created to have a parent to adopt the aclGroup table.

Since a child cannot have more than one foreign key reference to the same parent on the same columns, then NDB is ignoring it and still trying to enforce foreign_key_checks=OFF

Under the hood, this is what the NDB storage engine should be doing afterCREATE TABLE aclGroup

ALTER TABLE aclGroup DROP CONSTRAINT aclGroup_fk_aclRole_id;
DROP TABLE `NDB$FKM_20_0_aclRole`;
DROP TABLE IF NOT EXISTS aclRole;
CREATE TABLE aclRole (...) ENGINE=NDBCLUSTER;
ALTER TABLE aclGroup ADD CONSTRAINT aclGroup_fk_aclRole_id
FOREIGN KEY (`aclRole_id`) REFERENCES `aclRole` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

If you look at Dump of foreign key constraints with mysqldump not sorted, you will note that this was a feature request for InnoDB long ago. This same feature is probably not properly implemented in NDB. I am sure if you change the NDBCLUSTER to InnoDB in your script and run it, it will work without incident.

So, YES, foreign_key_checks=OFF is being ignored.


Table constraint, number of row taken by a fk key

One way would be to add a column (say project_title_no) that allows only 3 possible values and a unique constraint on (project_id, project_title_no):

ALTER TABLE ProjectTitles
  ADD COLUMN project_title_no smallint not null ;

ALTER TABLE ProjectTitles
  ADD CHECK (project_title_no IN (1,2,3)) ;

ALTER TABLE ProjectTitles
  ADD UNIQUE (project_id, project_title_no) ;

One drawback is that this column is not automatically filled and you have to supply the value, either in your application or by creating suitable insert/update procedures (that check first if for example, for some project there are rows with title_no 1 and 3, and fill the missing 2).

It requires 2 extra bytes* which seems a lot for only 3 values but it's easily modifiable (the requirements change to 10 titles per project? Easy, just change the CHECK constraint.)

There are no table locking required though or race conditions that come with a trigger solution.


SSIS for/each loop to ETL tables across databases

If I was dealing with your particular scenario, I would probably end up building a "mapping" table that had the [SourceTable] and [DestinationTable] tables.

Then I would use that table as parameters that feed into a cursor which Selects from [SourceTable] and pushes the records to [DestinationTable].

Of course, this would only work if all of the columns are the same accross all tables (or if you do "INSERT INTO [DestinationTable] SELECT * FROM [SourceTable]" which I wouldn't advise).

Pros to 100 components: Modular, allows for less changes if only one or a few needs to be changed.

Cons to 100 Components: 100 components to maintain and change, especially if it's a small change that applies to all of them.


What steps can I take to determine if my server is under-provisioned for memory?

Here are several perfmon counters you can use as a starter.
Memory Grants Pending - This is a counter that tells you if there any queries waiting for a memory grant (memory allocation). This should actually be 0. If it is consistently more than that, you have an issue.
Page Life Expectancy - This is the estimated amount of time in seconds that a page stays in memory. The higher the better, but there is a formula to calculate what the minimum value for your serer should actually be. It used to be 300 seconds minimum, but this is an old calculation and should nowadays be 100 seconds per GB. I got this from Richard Douglas during a SQL Saturday session, so credits to him. He works for SentryOne. Less than that value tells you there is too little memory assigned. Also use this counter in conjunction with Checkpoint pages/sec. Be aware that every NUMA node has it's own PLE value (if you have multiple NUMA nodes on your SQL Server). SQL Server starts deviding resources into (soft) NUMA nodes above 8 cores assigned.
Lazy writes/ sec - The lazy writer process clears out old pages from the cache when SQL Server experiencs memory pressure. Constantly above a value of 20 is a problem (got this from Richard Douglas as well). However, use it in conjunction with Page Life Expectancy. If you see a high PLE together with a spike in lazy writes/sec, then something is causing SQL to remove pages from it's cache and insert new pages. See the screenshot below for an example from my home lab.

Iam sure that some experts here know a little more about the memory clerks, still on my list to dive in deeper, so maybe someone has some additional info for you (in which I am very much interested as well :-) ).
Edit: you can also use sys.dm_os_performance_counters to get them on the fly with an query if you like.
Edit 24 June 2020:
@J.D. regarding your comment of June 23; i digged in a little deeper into memory pressure as well, because of the comments of @Dominique Boucher and this article: https://www.brentozar.com/archive/2020/06/page-life-expectancy-doesnt-mean-jack-and-you-should-stop-looking-at-it/. I was laughing when I got this in my mailbox; maybe he saw this post. :-) The article is telling us to stop looking at it. Well, although Brent is surely a lot more experienced then me, I don't think I can totally agree with his statement to never look at it. I get his point within the context of his sp_BlitzFirst, a single query using max 25% of the buffer cache, it is a lagging indicator, etc. but for trend analysis and history I would still look at PLE vs Lazy Writer. If I want to determine if a server has memory pressure over time, this is what i would use in combination with the waiting memory grants. Also, monitoring tools from RedGate and Quest still use this. Now @Dominique Boucher says to look at the RESOURCE_SEMAPHORE waits, which I agree on, but this will most likely be in line with the number of pending memory grants (which you can easily register with perfmon). If you have a constant queue of memory grants (it works with a FIFO queue), then you do have memory pressure.
As a reference, this is Lazy Writer for a certain system in this universe (32 GB mem, 1 TB database, although the type of workload is also very important). Yellow is batch requests p/s, 10 indicates a 1000, so you can see it is definitely not idle.

here is also a snapshot from my home lab regarding memory grants vs RESOUCE_SEMPAPHORE waits (and I also see I highlighted the tempdb writes, that was what david was talking about, too little mem, so spill to tempdb):

Now, looking at your perfmon counters, I think that you could definitely have a memory issue. I mean; something is constantly forcing SQL Server to remove pages from the buffer cache. If this would be a single time, ok, but it seems it is constantly busy doing that... However, I would like to see them compared to PLE. This can be a clear indication there is memory pressure ( over time I still think so). Secondly, you also want to look at pending memory grants. Now, I didn't say this before, but looking back I do think you want to look at the wait stats as Brent and Dominque are saying. However, that is a little more difficult over time. Wait stats are gathered cummulative, so you need to clear them first (which I don't prefer) and then look at them if the RESOURCE_SEMAPHORE are adding up.
To clear the wait stats: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-sqlperf-transact-sql?view=sql-server-ver15
You can also use sp_BlitzFirst to monitor it, but that is only a snapshot from the point of time your are running it. sp_BlitzFirst (or sp_Blitz i cannot recall) has an option to record this periodically in a table, so you could look at that as well. Or just querying dm_os_wait_stats yourself in some other way also works. In general, my personal preference is to collect data over time to analyze this. I do this using Database Health Monitor from Steve Stedman: databasehealth.com. Screenshot from my home lab:

This way you can monitor wait stats a lot better, although it will cost you a tiny bit of resources.
If you have SQL Server 2017 or above (we don't have that yet in our env.), then you can also use Query Store. As of SQL Server 2017 Query Store also records wait stats (it is a configurable option). Be carefull though, I have read stories of Query Store bringing very busy servers to their knees (you can monitor that with wait stats :-P ). You should always test before implementing functionality in prod of course. We do use it, and works great, but we have 2016, so we do miss the wait stats option :-(.
By the way; my tactic would be to gather information (perfomon, wait stats if possible), if you think there is memory pressure upgrade the RAM (if a VM that would be quite easy), gather performance indicaters afterwards and check if they have improved. Bit of a no brainer, but the latter is often forgotten or not done very well.


What Other Information Is Stored in The Page Header

Paul Randal actually answers this exact question in the comments on the blog post you linked to:

The 8060 bytes is the maximum size of one record, not the amount of data space on the page – 8096 bytes.
For a maximum-sized record of 8060 bytes, add two bytes for the slot array entry, 10 bytes for a possible heap forwarded-record backpointer, 14-bytes for a possible versioning tag, and that’s 26 bytes used. The other 10 bytes are for possible future use.
If there are more than one record on the page, all 8096 bytes of data space can be used.

So, in answer to the question in the body of your post:

What do these 36 bytes contain?

The "extra" 36 bytes in a page are used as follows:

10 bytes reserved for heap forward-record backpointer
14 bytes reserved for versioning tag pointing to the version store in tempdb
12 bytes available for the slot array

in the case you outlined of one large record, there are 10 bytes of "wasted" space here.  There is room for 5 more 2-byte slot array entries

Just to confirm that the table defined in the question is, in fact, 8060 bytes wide, let's have a full repro.
First we'll set up the database and the table, and insert one row into it.  I'm adding a clustered index because heaps are the worst.
USE master;
GO

CREATE DATABASE PageJunk;
GO

USE PageJunk;
GO

CREATE TABLE [dbo].[Generated_Data_GUID](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [GUID] [uniqueidentifier] NOT NULL,
    [SEQGUID] [uniqueidentifier] NOT NULL,
    [Data1] [char](4000) NULL,
    [Data2] [char](4000) NULL,
    [Data3] [char](9) NULL,
    [EntryDate] [datetime2](7) NULL
) ON [PRIMARY];
GO

CREATE CLUSTERED INDEX PK_Generated_Data_GUID
    ON Generated_Data_GUID (ID);
GO

INSERT INTO [dbo].[Generated_Data_GUID]
    ([GUID], SEQGUID, Data1, Data2, Data3, EntryDate)
VALUES
    (NEWID(), NEWID(), REPLICATE('1', 4000), REPLICATE('2', 4000), REPLICATE('3', 9), '2018-01-01');
GO

We can see all the pages allocated the index by running this DBCC command:
DBCC IND ('PageJunk', 'Generated_Data_GUID', 1);
GO

The one with a page type of 1 is the index page (page ID 336).  We can dump all kinds of information about that page with this other DBCC command:
DBCC TRACEON (3604); -- needed for the next one to work
GO

DBCC PAGE (PageJunk, 1, 336, 3);
GO

Here are some important snippets from the output of that command.  From the HEADER section:
m_freeCnt = 34

This means there are 34 bytes of free space on the page.  That's the 36 you outlined in your original post, minus the 2 bytes for the slot array entry.  Speaking of which:
m_slotCnt = 1

This means there is only one record on this page.
Now, down in the record section:
Slot 0 Offset 0x60 Length 8060

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP    Record Size = 8060

This indicates that the single record stored on this page is 8060 bytes (which is the sum of all the data type storage sizes plus the 7 bytes per-record overhead).
So we do have a fully sized, 8060 byte record on this page.  However, we can still squeeze an additional 34 bytes onto this page if we try harder.
For instance, I could create a table that's 2015 bytes wide.  Each row would then take up 2015 + 7 (internal overhead) + 2 (slot array) = 2024 bytes in the page.  So four rows should add up to 8096 bytes, exactly filling the space left over after the 96 byte header.  Let's try it in the same database:
CREATE TABLE [dbo].[QuarterPage](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [GUID] [uniqueidentifier] NOT NULL,
    [SEQGUID] [uniqueidentifier] NOT NULL,
    [Data1] [char](981) NULL,
    [Data2] [char](981) NULL,
    [Data3] [char](9) NULL,
    [EntryDate] [datetime2](7) NULL
) ON [PRIMARY];
GO

CREATE CLUSTERED INDEX PK_QuarterPage
    ON QuarterPage (ID);
GO

INSERT INTO [dbo].[QuarterPage]
    ([GUID], SEQGUID, Data1, Data2, Data3, EntryDate)
VALUES
    (NEWID(), NEWID(), REPLICATE('1', 981), REPLICATE('2', 981), REPLICATE('3', 9), '2018-01-01');
GO 4

Now we find our page, and there is only one as expected:
DBCC IND ('PageJunk', 'QuarterPage', 1);
GO

So now we want to get info on page 352:
DBCC PAGE (PageJunk, 1, 352, 3);
GO

And here's the good stuff:
m_slotCnt = 4
m_freeCnt = 0

No free space!  This page is slap full with our 4 rows.


How to force SQL Serve *not* to maintain a log file

There is no way to prevent SQL Server from writing to the transaction log.

In the simple recovery mode, as soon as a transaction finishes its log records are marked so that they can be overwritten but the transaction is still written to the log.

This means that a large transaction can still expand the log, if the current size of the log is too small.

You need to make sure that the drive your log is on has enough adequate free space for the log to expand, or (and this is what I recommend) expand your log file manually to a size that will avoid auto-growth. Do not regularly shrink your transaction log.

The best way to get the correct size for your transaction log is to simulate production traffic on a development server. Run queries (that would be executed against your production database) against your development database for a set period of time, and monitor the log.


"ORA-01950: no privileges on tablespace 'SYSTEM' " : What error is it?

This error says that the user doesn't have quota on tablespace SYSTEM which is set as the default persistent tablespace. You can assign a user the quota like this:

sql> alter user scott quota 50m on system;

Here, 50m means that the user quota on the SYSTEM tablespace is 50 mebibytes. You can also set the quota to unlimited.

However it is a bad practice to store user and application data in SYSTEM tablespace as it causes data fragmentation and thus degrades performance. So I would recommend you to create a new permanent tablespace:

sql> create smallfile tablespace users datafile '/u01/app/oracle/oradata/ORCL/users.dbf' size 10g;

And then set it as the default database permanent tablespace so that objects created by the users go into that default tablespace:

sql> alter database default tablespace users;


Cumulative Sum over 12 months by Postgreql 10


Does a killed query continue rolling back after closing and exiting out of SSMS?

Closing the connection to SQL Server, whether that is a connection utilizing SSMS or some other application causes the rollback of any currently active transaction(s) created by that connection.  This includes any implicit transactions, and operations such as RESTORE DATABASE, etc.  See this answer for more details about what can cause a transaction to be rolled back.  Be aware that rollback may take an extended amount of time to complete, and will continue whether or not the application that caused the rollback is connected or not.  Rollback will continue even if you restart SQL Server - in fact, restarting SQL Server will likely result in the database being marked as "in recovery" while rollback continues.

If SSMS is actually still running (perhaps the user interface is not visible, but the process is visible in Task Manager), the query might be still running, but I highly doubt that is the case.

Most likely you have some other connection holding a lock on some objects in the database in question, or a rollback is continuuing.

You can check the progress of transactions that are being rolled back by looking at system DMVs, such as:

SELECT des.session_id
    , des.host_name
    , der.command
    , der.percent_complete
FROM sys.dm_exec_requests der
    INNER JOIN sys.dm_exec_sessions des ON der.session_id = des.session_id
WHERE der.command = 'ROLLBACK';


Making one large table would be a mistake: you'll have a table with lots of NULL values and it would soon evolve into a maintenance nightmare. And about saving the data as XML or JSON -- (slaps your wrist) that's for even considering it.

Every Resulttype should get its own table, each of these tables has a primary key over an ID column and (this is the trick!) all of these IDs are taken from the same sequence!

Then you only need a table with the columns projectid, resultid (and optionally resulttype). Since you will look for the primary key, even searching over all tables can be done fast.


How to have a fail-over resilient SQL Server view over linked mirrored database table

Well, I haven't tested but how about openrowset? Native client is deprecated so there might be a bit of work needed to get it running, depending on what SQL server version youre're running, but then again so is mirroring I suppose. How about something like this:

sp_configure 'show advanced options', 1;
RECONFIGURE;

GO

sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Create view MyView

AS

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Server1;FailoverPartner=Server2;Trusted_Connection=yes;',
                 AdventureWorks2012.HumanResources.Department) AS a;

GO


How to store daily records in SQL Server 2017 database?

Your table is going to be very narrow with two INTs and a DATE, so even with hundreds of millions of rows, storage will be relatively light.

You can also create summary tables off of this main table, like if you need monthly or yearly logins, you can create these as separate tables.


How to pass output of one stored procedure to the input of another

You must pass the value from the first SP to the second via a variable local to the master SP.  The bare bones of the code will look something like this:

create master_sp
..
declare @LocalVariable int;

exec first_sp
    @sp_parameter = @LocalVariable OUTPUT;

exec second_sp
    @sp_input = @LocalVariable;


With Postgres, can I make a rule that will not recurse?

If fullname column is never altered manually (i.e. it must match firstname and lastname values always) then simply use generated column. It will be (re)calculated automatically during insert/update:
ALTER TABLE people
    DROP COLUMN fullName,
    ADD COLUMN fullName VARCHAR(200) GENERATED ALWAYS AS (firstName || ' ' || lastName) STORED;

DEMO


Where are filtered-index dependencies listed?

I think this information is stored in sys.sql_expression_dependencies. See the table in MSDN, sys.sql_expression_dependencies:

THIS TOPIC APPLIES TO:
yes: SQL Server (starting with 2008)
no: Azure SQL Database
yes: Azure SQL Data Warehouse
yes: Parallel Data Warehouse
Remarks
The following table lists the types of entities for which dependency information is created and maintained. Dependency information is not created or maintained for rules, defaults, temporary tables, temporary stored procedures, or system objects.

Entity type          Referencing entity    Referenced entity
Table                Yes*                  Yes
View                 Yes                   Yes
Filtered index       Yes**                 No
Filtered statistics  Yes**                 No
...

** Each column used in the filter predicate is tracked as a referencing entity.

Also note that filtered indexes were added as a feature in version 2008 and this system view does not exist in 2005 (thnx to @Michael Eriksson for noting this).


In a data warehouse with small amount of writes, would RAID 1 be better than RAID 1+0?

This is a pretty open ended question and you might want to just test it yourself and see.  It depends on how you query and write the data.  This will determine how many IOPS you need at peak use, which if you want to ensure you'd figure out by testing then deciding.

However if that is not an option, do you have hundreds of gigs of RAM?  Some systems do.  If so, perhaps your read/write patterns will hit RAM and Disk IO won't matter.  Are you using SSDs or magnetic disks?  SSDs have a much higher disk IO and IOPS than traditional spinning media so the write penalty might not even be noticeable.

Really not sure how much more to add here, it'll come down to your testing results and IOPS needs.


Is there a database tool that bashes data with HUGE excel files?

What you're describing is known as "Excel spreadsheet hell". Your scenario sounds like a disaster - constant firefighting, messy data... the list goes on.

I'm not sure what sort of data volumes you're dealing with. Oracle's solution to this is Apex. It's potentially a lot more than that, but take a look here.

If your volumes are not too large, you could use Oracle XE as your backing database for your systems. Apex only works with Oracle - hardly surprising for a tool which comes free with their database system.

You might ultimately want to upgrade to one of the other Oracle editions - but ultimately, managing data is not a trivial exercise and in order to improve your systems, some financial outlay may be required.

You could take one of the simpler systems, convert it to Apex and show management the potential of using a proper RDBMS to manage their data. It might require some midnight oil on your part, but if it works, it could be of benefit to your company and you.

All I would say, is that your system is untenable and will collapse under its own weight at some point in time. Either try and make it better or resign.

p.s. I hope you don't work for my telco! :-)


Insert if something changed other than specific column

ALTER TRIGGER [dbo].[t_upd_insert]
ON [dbo].[Player]
FOR INSERT, UPDATE
AS
SET XACT_ABORT, NOCOUNT ON;

BEGIN
IF (update(name))
BEGIN
  INSERT INTO dbo.Changes (
            [name]
            [created_at]
       )
  SELECT
            ins.[name],
            ins.[created_at]
  FROM INSERTED ins
END


Network connection from old application to SQL Server 2008 R2 lost once in a while

Axapta 2.5 was not supported on SQL Server 2005 or later versions so you are out of your depth there. To support later version of the database engine you will need to update the axapta application itself. 2.5 predates SQL Server 2005 by years: http://everything.explained.today/Microsoft_Dynamics_AX/

Any problems you have with incorrect statements are most probably due to the axapta server getting confused over the database version, you could try to change compatability level to 8.0 but I would recommend that you contact an Axapta certified partner and migrate to newer version of the application


There is never enough information with these crash logs. Often, all you can do is drop the database and start over -- access to the tools that would otherwise assist in trouble shooting this are disabled by Microsoft, such as strace and ltrace. You can roll your own kernel if you're in need of more information.

You may also try running this to reset the permissions on the system if they're misconfigured.
Remember that the right user for the server is always mssql. You must use that mssql user.
You have to have the server licensed properly (even if you just use the configuration utility to tell it you're a developer). Without proper licensing, you may get weird and unexplainable errors including segfaults.


Relational vs Non-Relational Database for Events Database

This seems a little outside the scope of a StackExchange question.  However.....

NoSQL databases are, typically, build to resolve specific issues with the relational model.  The most common issue addressed is scalability.  However, because they're all designed to address different aspects of certain problems that some applications have with the relational model, there really isn't something you can say about all of them as a whole.  Maybe you need to handle terabytes of data?  Or maybe you need to handle a data schema that is very dynamic across the board and an EAV type schema would just kill performance?  Maybe you want a data store that cares about availability more than consistency (see CAP theorem)?  Each one is good at something that could be wildly different from what another is good at, while an RDBMS is a much more generalized database.

The answer I hear from people I trust on this topic is that if you don't already know why you need to abandon a traditional RDBMS for NoSQL, then you almost certainly should stick to an RDBMS.  There's a reason it took 30 years before people began to seriously consider data store models beyond the RDBMS.  If your queries are requiring features not available in MySQL/MariaDB, you may wish to consider another RDBMS that has a more robust feature set.  That could be PostgreSQL, or Oracle, or MS SQL Server.

Your proposed schema here already suits the relational model fairly well.  You may need a few junction tables for many-to-many relationships, but I don't think you necessarily will see many of those.  If you properly size the DB, properly index your tables, and analyze your common queries -- things you have to do with NoSQL, too -- I don't see that you'd have a huge problem.  You may even be able to get away with views for the most common queries.


Set up new publication on SQL Server 2014 database which is part of an availability group

I found a solution for my problem and like to share it.
Solution
By using the fourth parameter ignore_distributor I could get around the problem and was able to set option publish for my db.
exec sp_replicationdboption @dbname = N'mydatabase',
                            @optname = N'publish',
                            @value = N'true',
                            @ignore_distributor=1

But unfortunately it wasn't solving my overall issue on creating a new publication on this existing publisher.
New (second) Problem
The next step was to create the log reader agent job by executing the following command.
EXEC sys.sp_addlogreader_agent @job_login = N'<domain user>',
                               @job_password = N'<password>',
                               @publisher_security_mode = 0,
                               @publisher_login = N'<username>',
                               @publisher_password = N'<password>';

It resulted into the following error

The Publisher 'ServerB' does not exist. To view a list of Publishers, use the stored procedure sp_helpdistpublisher.

Ok, strange as I had a running publication from this server. So, I was checking it by executing the mentioned SP on my distributor. The result was showing me my publisher correctly. So far so good. Where is now the problem? Even after checking a couple of other thinks on my publisher I wasn't able to find any think wrong.
Let's check the distributor and run a trace when I executed my above command.
At the end the issue was created by the system procedure sp_MSreplagentjobexists. It was checking the publisher configured in the table dbo.MSreplservers within the distribution db. Bingo! My publisher was missing in there. I only found my original publisher in there. So, getting the server id from sys.server and insert the current primary node of my availability into this table worked like a charm.
The last think I had to do was to redirect my publisher which are running on an availability group to my AG listener.
-- redirect ServerA
exec sys.sp_redirect_publisher
       @Original_publisher = 'ServerA_primary_node',
       @Publisher_db = 'mydatabase_to_publish',
       @Redirected_Publisher = 'AGListener';
GO

-- redirect ServerB
exec sys.sp_redirect_publisher
       @Original_publisher = 'ServerB_seconary_replica',
       @Publisher_db = 'mydatabase_to_publish',
       @Redirected_Publisher = 'AGListener';
GO

Finally my new publication was working :)
So, I am not sure if this a bug and configuration issue made in the past. As I have no idea how and when the entries are made in the table MSreplservers of the distribution.
Any thoughts?


Postgres: check disk space taken by materialized view?

This is assuming that materialized views have relpages >= 8 in pg_class, which doesn't have to be the case. It can actually be empty - not populated yet, indicated by pg_class.relispopulated = FALSE. The corresponding disk file has a zero size in this case.

Try instead:

SELECT relname   AS objectname
     , relkind   AS objecttype
     , reltuples AS entries
     , pg_size_pretty(pg_table_size(oid)) AS size  -- depending - see below
FROM   pg_class
WHERE  relkind IN ('r', 'i', 'm')
ORDER  BY pg_table_size(oid) DESC;

Where the available types are:

  r = ordinary table,
  i = index,
  S = sequence,
  v = view,
  m = materialized view,
  c = composite type,
  t = TOAST table,
  f = foreign table

Use one of the database object size functions rather than building your own. Be aware that the "size of a table" can be defined in different ways. Details:

Measure the size of a PostgreSQL table row


Can we expdp using JOINS in QUERY clause

The QUERY parameter is used to restrict data in the export job. If you want to export number of rows that are exist in another table then you can use subquery in QUERY parameter.

If you want to join two tables and export you can create table by joining two tables. As shown below.

SQL> create table export_tbl as select e.first_name FN, d.department_name DN from employees e join departments d on(e.department_id=d.department_id);

Table created.

Then can export it.

[oracle@testsrv Desktop]$ expdp hr/hr directory=user_dir dumpfile=export.dmp tables=export_tbl logfile=export.log

Alternatively,  you can create a view by joining tables and export views(INCLUDE=VIEWS). You have to filter views during import.


how to compare two date column from two different tables by extracting specific parts in both columns using trigger/function in postgresql 10

If you only want to remove milisecinds
CREATE OR REPLACE FUNCTION hist_base_host_table_trig_func() RETURNS TRIGGER AS $hist_base_host_table_trig$
declare

username VARCHAR(40);

BEGIN
    --
    -- Create a row in emp_audit to reflect the operation performed on emp,
    -- make use of the special variable TG_OP to work out the operation.
    --
       IF (TG_OP = 'DELETE') THEN
    select userid into username
    from gui_audit_trail
    WHERE messageid='LS_REF_DATA_ROW_DELETED'
        and description like '%BASE_HOST%'
        and date_trunc('second', recorded::timestamp)
        =date_trunc('second', OLD.last_update::timestamp);
        RAISE NOTICE 'username is (%)',username;
        INSERT INTO HIST_BASE_HOST SELECT OLD.zone_id, OLD.site_id, OLD.org_id, OLD.start_date, OLD.end_date, OLD.reserved_capacity, now(),'D', username;
        RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO HIST_BASE_HOST
           SELECT NEW.zone_id, NEW.site_id, NEW.org_id, NEW.start_date, NEW.end_date, NEW.reserved_capacity, now(), 'U',user;
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO HIST_BASE_HOST SELECT NEW.zone_id, NEW.site_id, NEW.org_id, NEW.start_date, NEW.end_date, NEW.reserved_capacity, now(), 'I',user;
        RETURN NEW;
    END IF;
    RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$hist_base_host_table_trig$ LANGUAGE plpgsql;

CREATE TRIGGER hist_base_host_table_trig
AFTER INSERT OR UPDATE OR DELETE ON base_host
    FOR EACH ROW EXECUTE PROCEDURE hist_base_host_table_trig_func();


What do we call the equivalent of CSS "Specificity" in SQL?

In CSS, the more HTML elements you specify, the more precedence the selector gets. This is known as "specificity." For example,

That's not true. CSS specificity is very complex because it's trying to apply conflicting rules rather simply disambiguate. While I understand what you're talking about SQL suffers no such problem with identifiers as queries are either executed with certainty or an error is thrown.

Is there a proper term in SQL for being more specific in queries, or is it just called "specificity" like in CSS?

The intent here is different, "specifity" doesn't exist.

In CSS, the goal is to establish a ruleset to apply conflicting rules.
In SQL, the goal with the syntax you've mentioned is to merely disambiguate.

In the context given we would say <catalog>.<schema>.
.<column> is "fully qualified", as in it leaves no further room for ambiguity. This definition comes from ISO9075-1.,

fully qualified (of a name of some SQL object) With all optional components specified explicitly
NOTE 3 — A fully qualified name does not necessarily identify an object uniquely. For example, although a fully qualified specific name, consisting of a catalog name, a schema name and a specific name, uniquely identifies a routine, a fully qualified routine name doesn't necessarily do so.

NOTE 3 is referring to routines being typed, catalog.schema.myRoutine(text,text) can be different from catalog.schema.myRoutine(int,int).
This is massively different from "specificity" which implies cascading mutability -- if we say "show all animals" and "hide all dogs" and "show collies" what should happen?

.animals { display: block }
.dogs { display: none }
.collies { display: block }

In CSS, you have to know if multiple rules can apply to the same identifier, which one has precedence in the event they conflict. In SQL, the rule-set is much simpler. If there is any ambiguity, things will throw a fatal error. All databases that I can think of forgo any attempt at guesswork. Preventing that fatal error from being thrown requires qualification of your "identifiers".
For instance, the following creates a result set that returns two x (some databases would likely throw an error here),
SELECT *
FROM ( VALUES (1) ) AS t(x)
CROSS JOIN ( VALUES (2) ) AS g(x);
 x | x
---+---
 1 | 2
(1 row)

but the second you say you want x with SELECT x rather than SELECT * all databases that I know of throw an error,
ERROR:  column reference "x" is ambiguous
LINE 1: SELECT x FROM ( VALUES (1) ) AS t(x) CROSS JOIN ( VALUES (2)...

Logically you may ask what the equivalent of SQL "fully qualified" identifiers in CSS. That would be a simple identifier (id attribute selectors). The spec requires identifiers be absolute, thus there is no ambiguity when you specify
#Lassie { color: blue }

It's infinitely (fully-) (globally-) "specific" (qualified), and far more specific than
.animals.dogs.collies { color: white }

Actually there is a small rabbit hole I'm excluding and that's XHTML namespaces and Foreign Elements. which do in fact provide for multiple #Lassies (that is to say identifiers aren't always globally unique)


Yes, you can, but it is not a good idea unless you are 100% certain the database won't grow again. If it will, then shrinking the file to free up space is like vacuuming while confetti is still falling on your carpet, and you should really read one of the many posts out there on how futile it is to shrink files that are just going to grow again. Nothing is gained except your free space % looks a little better for a little while. Big whoop! Do you get a bonus for how much free space is on your disk, even if you can't sustain it?

http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

Why Does the Transaction Log Keep Growing or Run Out of Space?

Now, if you really, really, really want to shrink your files, even after reading all of the above material and refusing to heed our warnings, keep reading.

If you want to shrink the data file by 10GB, look at the size it is now, and then use DBCC SHRINKFILE (not DBCC SHRINKDATABASE) and set a size 10GB smaller than that. Look at the size column from sp_helpfile - let's say the file is now 41 GB, and you want it to shrink to 31 GB, then you would say:

DBCC SHRINKFILE(filename, 31000); -- 31,000 MB

You can probably do better math than that (I know 10,000 MB is not exactly 10 GB but you get the idea).


You are not sacrificing performance as in database performance using varbinary(MAX). You are not searching on the varbinary(MAX). You are not indexing the column.

What is nice about in the table is single consistent backup. The down side is a bigger backup.

Delivering the file to the client varbinary(MAX) is going to be less efficient than FILESTEAM or file system.

1 - 2 mb is relatively small.  If it was 200+ mb then you typically would want to avoid storing in varbinary(MAX).

If table size alone is problem then FILESTREAM is probably your best solution. This will not hurt database performance and files are not stored in a table.

  FILESTREAM enables SQL Server-based applications to store unstructured
  data, such as documents and images, on the file system. Applications
  can leverage the rich streaming APIs and performance of the file
  system and at the same time maintain transactional consistency between
  the unstructured data and corresponding structured data.

From a licensing perspective I am pretty sure FILESTREAM does not count as database size.  Like for EXPRESS with a 10GB database limit the FILESTREAM does not count.

Another benefit is if you have OCR on the PDF with an Adobe iFilter you can Full Text search the document.  But you may be able to do that with FILESTREAM also - from what I see no.

Or you can just manage the file totally separate and only store a path in SQL.


You startt the server with following restrictions

  (Then) ithout a password and with all privileges, and disables account-management statements such as ALTER USER and SET PASSWORD. Because this is insecure, if the server is started with the --skip-grant-tables option, it also disables remote connections by enabling skip_networking.

This comes from how to reset root passwored

The safe mode is only practical, when doing ridumietery things like resetting roor password.

So explain better what you want to do, maybe there is a better or otheer  solution


How to export the output of sp_AskBrent?

Make the alert run a job that runs the EXEC msdb.dbo.sp_send_dbmail with the @query parameter:

EXEC dbo.sp_send_dbmail @profile_name = 'mail_profile'
    , @recipients = 'some@mail.com'
    , @subject = 'some subject'
    , @body = 'the body text goes here'
    , @query = 'EXEC sp_AskBrent;'
    , @attach_query_result_as_file = 1;

The @attach_query_result_as_file = 1 parameter will send the results as an attachment in text format, which should certainly be readable.  If you want, you can export the results in comma-delimited format using the @query_result_separator parameter.

From the MSDN page for sp_send_dbmail:

  This example sends an e-mail message to Dan Wilson using the e-mail address danw@Adventure-Works.com. The message has the subject Work Order Count, and executes a query that shows the number of work orders with a DueDate less than two days after April 30, 2004. Database Mail attaches the result as a text file.

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

There are many options for that stored proc; check the MSDN page and likely you'll get the output exactly how you want it.


Is this right decision to define 5-6 foreign key in a single table...?

Foreign keys exist to enforce referential integrity i.e. to keep the data clean. This is a good thing.

However, there are no free lunches. The constraint will be checked on writes, adding latency. A FK may have an associated index. As such there will be an overhead during writes. The more FKs, the more overhead. Plus the extra disk and maintenance time.

If you have done design diligently and arrived at this table then I see no prima face problem. If testing at scale shows problems that's the time to consider alternate implementations.


Oracle DB occasionally slows down when "Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN"

Seems like there are some Database Resource Manager (DRM) constraints put on user sessions when the database switches to maintenance plan via Scheduler window each night (as evident by your log portion).

There are at least two predefined DRM plans when a database is created.

DEFAULT_PLAN
DEFAULT_MAINTENANCE_PLAN

(See the complete list.)

When a window activates, it can switch the current resource plan to one specified in the window. You can query ALL_SCHEDULER_WINDOWS view to see which plan each window is assigned.

You can also switch between plans in Enterprise Manager or manually changing RESOURCE_MANAGER_PLAN initialization parameter value.

You can use Enterprise Manager or DBA_RSRC_* views to see

which plans are active in database – DBA_RSRC_PLANS
which resource consumer groups are defined – DBA_RSRC_CONSUMER_GROUPS
which group mappings are defined – DBA_RSRC_GROUP_MAPPINGS
which resource plan directives are defined – DBA_RSRC_PLAN_DIRECTIVES
and so on

Resource plan directives is what defines resource usage by the database sessions. The sessions map to consumer groups via group mappings.

When your database switches to another plan (using window or manually), the different set of directives is enabled which controls the resource usage by sessions. Thus when your database switches from DEFAULT_PLAN to DEFAULT_MAINTENANCE_PLAN and vice versa, your user sessions notice the difference – their performance dynamically changes according to the resource directives defined in the enabled plan.

For more details on implementing and using resource plans in DRM, consult the Managing Resources with Oracle Database Resource Manager chapter in Database Administrator's Guide.

UPDATE

You can check which directives are defined for plans in your database.

select plan, group_or_subplan, mgmt_p1, mgmt_p2, mgmt_p3
from DBA_RSRC_PLAN_DIRECTIVES
where plan like ('DEFAULT_%')
order by plan, mgmt_p1 desc, mgmt_p2 desc, mgmt_p3 desc;

PLAN                        GROUP_OR_SUBPLAN    MGMT_P1 MGMT_P2 MGMT_P3
------------------------    ----------------    ------- ------- -------
DEFAULT_MAINTENANCE_PLAN    SYS_GROUP                75       0       0
DEFAULT_MAINTENANCE_PLAN    OTHER_GROUPS              0      70       0
DEFAULT_MAINTENANCE_PLAN    ORA$AUTOTASK_SUB_PLAN     0      25       0
DEFAULT_MAINTENANCE_PLAN    ORA$DIAGNOSTICS           0       5       0
DEFAULT_PLAN                SYS_GROUP                75       0       0
DEFAULT_PLAN                OTHER_GROUPS              0      90       0
DEFAULT_PLAN                ORA$AUTOTASK_SUB_PLAN     0       5       0
DEFAULT_PLAN                ORA$DIAGNOSTICS           0       5       0

Notice how resources are allocated between different consumer groups. Here OTHER_GROUPS and ORA$AUTOTASK_SUB_PLAN are allowed to use 70% and 25% of CPU respectively in DEFAULT_MAINTENANCE_PLAN plan, while in DEFAULT_PLAN they are allowed 90% and 5% respectively.

It means that these directives might be one of the reasons your users' sessions run slower when maintenance windows are open, because users are allocated less resources than what they are during work hours.


What is the definition of bad circular dependency?

There is nothing circular in any of these cases. In "circular reference" scenario you have "a chicken and an egg problem" -- can not insert a row into a table because it is always missing a reference to some other table, like in:

user {user_id, address_id, ...}
  PK {user_id}

FOREIGN KEY {address_id} REFERENCES address

--
--
address {address_id, user_id, ...}
     PK {address_id}

FOREIGN KEY {user_id} REFERENCES user

The only problem that the author of that article has is not understanding
database design. The problem in his examples is simply lousy design stemming from insistence on single column PKs (IDs), and not understanding how business logic relates to DB constraints.

In the second example, his design assigns re-sellers commission based on the user and the product, instead on an actual purchase, which  makes no sense business-wise.
In the first example it is possible to assign a user to a task outside his project scope; again nothing to do with "circular", but not knowing how to implement constraints.

For the second example you may consider something like:

Product {ProductID}
     PK {ProductID}

Customer {CustomerID}
     PK {CustomerID}

Reseller {ResellerID}
     PK {ResellerID}

--
--
Purchase {PurchaseID, CustomerID, ProductID, ResellerID, ...}
      PK {PurchaseID}

FOREIGN KEY {CustomerID} REFERENCES Customer
FOREIGN KEY {ProductID}  REFERENCES Product
FOREIGN KEY {ResellerID} REFERENCES Reseller

And if you insist on the Commission table,  say if not every purchase involves re-seller.

Product {ProductID}
     PK {ProductID}

Customer {CustomerID}
      PK {CustomerID}

Reseller {ResellerID}
      PK {ResellerID}

--
--
Purchase {PurchaseID, CustomerID, Product_ID, ...}
      PK {PurchaseID}

FOREIGN KEY {CustomerID} REFERENCES Customer
FOREIGN KEY {ProductID}  REFERENCES Product

--
--
Commission {PurchaseID, ResellerID, ...}
        PK {PurchaseID}

FOREIGN KEY {PurchaseID} REFERENCES Purchase
FOREIGN KEY {ResellerID} REFERENCES Reseller

And for the first example:

Project {ProjectID, ...}
     PK {ProjectID}

User {UserID, ...}
  PK {UserID}

TaskType {TypeID, ...}
      PK {TypeID}

ProjectRole {RoleID, ...}
         PK {RoleID}

--
--
Task {TaskID, ProjectID, TypeID, ...}
  PK {TaskID}
  AK {TaskID, ProjectID}

FOREIGN KEY {ProjectID} REFERENCES Project
FOREIGN KEY {TypeID}    REFERENCES TaskType

--
--
ProjectTeam {UserID, ProjectID, RoleID, ...}
         PK {UserID, ProjectID}

FOREIGN KEY {UserID}    REFERENCES User
FOREIGN KEY {ProjectID} REFERENCES Project
FOREIGN KEY {RoleID}    REFERENCES ProjectRole

--
--
ProjectAssignment {UserID, ProjectID, TaskID, ...}
               PK {UserID, ProjectID, TaskID}

FOREIGN KEY {UserID, ProjectID} REFERENCES ProjectTeam
FOREIGN KEY {TaskID, ProjectID} REFERENCES Task

If you prefer single-column PKs then you can ADD them to ProjectAssignment and ProjectTeam tables, but you must keep the existing ones as AKs (unique) and reference them in foreign keys where applicable.

Note PK = primary key
     AK = alternate key (unique)
     All attributes (columns) NOT NULL


Log full of [Note] InnoDB: ft_end() after upgrade to MariaDB 10.3.10

Seems to be a rather meaningless info message. It certainly isn't an error or something to worry about.

It is a bit noisy and goes against a principle of the error log being quite clean (and meaningful). I recommend submitting a bug/pr to remove this line of code.


I do not believe that there is a specific problem with referencing tables, in C#, having names starting with @. I was able to create a table with a name of @TableName and was able to execute SELECT SUM(Col1) FROM [@TableName]; successfully. I used SqlConnection and SqlCommand, not EF or DataSet / DataTable. Are you sure that you are connecting to the database that contains this table?

Most likely the error message is very accurate and you are not in the correct database. Either specify the desired database in the Connection String, or use a fully qualified name: [DatabaseName].[SchemaName].[@TableName].

Of course, if you have a choice, then you should not choose to name any object starting with @ as that is the symbol used to denote local variables, system variables, and input/output parameters.


can anyone help me with this awful query plan?

The execution plan accesses Object7 first using a non covering index in Column7 order. It then does some key lookups on that table and nested loops joins against the other tables with the final joined resulting arriving at the TOP operator still ordered by Column7.

Once this has received enough rows to satisfy the OFFSET ... FETCH it can stop requesting any more rows from downstream operators. SQL Server estimates that it will only need to read 2419 rows from the initial index on Object7.Column7 before this point is arrived at.

This estimate is not at all correct. In fact it ends up reading the entirety of Object7 and likely runs out of rows before the OFFSET ... FETCH is satisfied.

The semi join on Object11 reduces the rowcount by almost half but the killer is the join on Object6 and predicate on the same table. Together these reduce the 9,753,116 rows coming out of the semijoin to 2.

You could try spending some time looking at statistics on the tables involved to try and get the cardinality estimates from these joins to be more accurate or alternatively you could add OPTION (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL') ) so the plan is costed without the assumption that it can stop early due to the OFFSET ... FETCH - this will certainly give you a different plan.


Increase logging/tracing on Oracle 12c database

You need to enable listener logging.

In your listener.ora, set:

LOG_DIRECTORY_listener=/path/to/your/oracle/home/network/admin/log
LOG_FILE_listener=listener.log
TRACE_DIRECTORY_listener=/path/to/your/oracle/home/network/admin/trace
TRACE_FILE_listener=listener.trc
TRACE_LEVEL_listener=10

... make sure you substitute your listener name for listener above.

You can also use ADR parameters instead - see the documentation.


Please provide SHOW CREATE TABLE; the explain is useless without it.

OR is a performance killer in many contexts.

  ( p.pricelist = "name_abc" AND p.iln = "sellerID_123" ) OR
  ( p.pricelist = "name_def" AND p.iln = "sellerID_456" ) OR ...

Turn that into

JOIN ( SELECT id FROM p WHERE
( p.pricelist = "name_abc" AND p.iln = "sellerID_123" ) OR
( p.pricelist = "name_def" AND p.iln = "sellerID_456" ) OR ... ) x ON x.id = foo.id

Also needed (on p):

INDEX(pricelist, iln, id)

(With the CREATEs, I could be more specific.)

The idea behind this "trick" is to move the costly work of the OR into a subquery that returns the necessary ids.  Plus the INDEX makes it so that it can do all that work in the INDEX.


Is an SQL Server trace 'considered harmful' in production?

The latest version of the RML Utilities (09.04.0051 as at today) support Extended Events tracing, so maybe you could work with your DBAs to do some controlled tracing.  A screenshot from the help file:

They do however have a point because the default trace template provided with RML includes statement-level events (SQL:StmtStarting, SQL:StmtCompleted, SP:StmtStarting, SP:StmtCompleted), ie every statement in every proc.  This could easily bring a server to its knees under the wrong conditions, eg scalar function in a SELECT statement running across a big table.

Extended Events (XE) were a bit limited in versions prior to 2012, but it is more lightweight.  You still need to be careful as SQL statement-level events will still incur overhead for very intensive workloads (sqlserver.sp_statement_starting, sqlserver.sp_statement_completed, sqlserver.sql_statement_starting, sqlserver.sql_statement_completed).  Execution plan capture (sqlserver.query_post_execution_showplan) can also be intensive.  Consider using the XETraceCaptureDefLightWeight.sql template which is provided and includes less events.

In summary I suggest moving to XE and using the lightweight XE definition provided with the latest versions of RML.


Run a count query for every row ID in results

Im not sure if i understood correctly are you referring to a group by?

SELECT lot.id,lot.name,manager.id,manager.name,count(car.id)
FROM lot
LEFT JOIN manager ON lot.manager_id = manager.id
LEFT JOIN car ON car.lot_id = lot.id
GROUP BY lot.id,lot.name,manager.id,manager.name;


Deleting old files in /var/lib/postgresql/9.x/main after changing postgresql data_directory

There shouldn't be any references as all the scripts/utils/etc use the data directory environment variable.

the only place I would look is in the new configuration file - take a look you don't have any includes that point to the old folder (not sure at what version they added this option.)

BTW, it's always a good practice to rename (mv dir dir_bck) the folder for a week to see you really don't have anything trying to access there.
(you shouldn't anyway as it's stale data...)

Regards,
 - Jony


Sql Server Replication requires the actual server name to make a connection to the server

You need to restart your instance after doing sp_addserver in order to get a proper @@SERVERNAME response. This is from BOL:

  The local definition takes effect only after the Database Engine is restarted. Only one local server can be defined in each instance of the Database Engine.


Unable to store query result inside a variable

The console.log is executed before the promise is fulfilled. This should work:
const checkEmailUsed = (email) => {
  let sql = "SELECT * FROM users_signup WHERE user_email = ? LIMIT 1";
  return new Promise((resolve, reject) => {
    conn.query(sql, email, (err, res) => {
      if (err) {
        reject(err);
        return;
      }
      resolve(res[0]);
    });
    conn.end();
  });
};

var user = [];
checkEmailUsed("mikias@email.com")
  .then((res) => user.push(res))
  .then(() => console.log(user))
  .catch((err) => console.log(err));


How to execute AFTER INSERT TRIGGER with an auto-incremented (seq.NEXTVAL) column value in Oracle PL/SQL?

The trigger you have written will try and insert a second row, and is causing the triggers view of the table to mutate (as it will end up in an infinite loop, essentially).

You actually only need a BEFORE INSERT trigger that sets :NEW.Airline_No :

create trigger new_airline_btrig
  before insert on airline
  for each row
begin
  :new.airline_id := airline_seq.NEXTVAL;
end;
/

You can then insert using just:

INSERT INTO Airline (Airline_Name, Country)
   VALUES ('Malaysia Airlines', 'Malaysia')

:new.columnname alters the given inserted value (in this case, from NULL to the sequence value, as airline_id was not provided).


In Memory OLTP causing OOM Issue in SQL Server even though no data in In Memory OLTP Table

It's a best practice to use a Resource Pool if running EE, that might help in this case. Also, garbage collection does not occur unless there is memory pressure/activity.


What is the difference between Interbase and Firebird?

InterBase version 6 was released under an open-source license in mid-2000, and was immediately forked to become the Firebird project.

Later versions of InterBase reverted to closed-source and the projects have diverged. Both are still actively developed. Developer’s tools and libraries traditionally support both servers but they are growing apart.


Return only records with a date after the most recent September 1st

Answered my own question, leaving it here for refence for others as I couldn't find the solution anywhere else. By adding 4 months to the date from "dtAssessmentDate" I was able to make the range of values I needed to return as any date from a single year. I extracted the year I am looking for by doing the same 4 month addition to the current date, then using DatePart to exctract the year and only return records who's year matches the extracted year.
Expression in criteria box in access is:
DatePart("yyyy",DateAdd("m",4,[dtAssessmentDate]))=DatePart("yyyy",DateAdd("m",4,Date()))


How to get job start time inside of a TSQL agent job step?

There is a lot of information about the jobs and SQL Server Agent in msdb.
You can check it out below, I added a few columns that help you in your thoughts. Maybe you want to check If this job success or not (in the message column).
SELECT  JobName = sj.name,
        StepName = jh.step_name,
        RunDateTime = msdb.dbo.agent_datetime(run_date, run_time),
        EndDateTime = DATEADD(SECOND, run_duration / 10000 * 3600 + run_duration % 10000 / 100 * 60 + run_duration % 100, msdb.dbo.agent_datetime(jh.run_date, jh.run_time)),
        jh.message
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobhistory jh ON sj.job_id = jh.job_id
where step_id=0

For the comment: There is no clearly answered yet about that. So, you should try it.
Microsoft says;

In most cases the data is updated only after the job step completes
and the table typically contains no records for job steps that are
currently in progress, but in some cases underlying processes do
provide information about in progress job steps.


MySQL is a big pain in the neck in Windows because of the way open files behave.
In Linux, whenever I see a log file filling up a disk (i.e., /var/log/mysqld.log), I attempt to truncate the file
echo -n > /var/log/mysqld.log

Linux and mysqld has no problem with it. Windows, on the other hand, does.
I tried to do this in MySQL 5.5.12 on my desktop, that is, zapping the error file and I got this message:
C:\>copy con C:\MySQL_5.5.12\data\LW-REDWARDS2.err
^Z
The process cannot access the file because it is being used by another process.
        0 file(s) copied.

C:\>

You must go to the Task Manager and kill the mysqld process.
Then, with the lock of the file handle released, you could attempt a zap of the log file.
Then, restart mysql with
C:\> net start mysql

You will have to allow time for mysqld to perform InnoDB Crash Recovery
UPDATE 2012-04-25 17:37 EDT
Diskspace is needed to write shutdown information into the mysql error log. During any mysql operation that produces temp tables (which are always MyISAM), diskspace is always needed. What happens if there is no diskspace for temp tables?
According to MySQL 5.0 Certification Study Guide Page 408,409 Section 29.2 bulletpoint 11 says:

If you run out of disk space while adding rows to a MyISAM table, no
error occurs. The server suspends the operation until space becomes
available, and then completes the operation.

Trying a standard shutdown of mysqld with no available diskspace complicates the problem. Just kill the process, delete the 18G log file, start mysql back up again, and live with InnoDB crash recovery. Trust me, it is better than looking for alternatives in a Windows environment which will not work because of the same disk issue.


Site running with mysql replication, shall we shut down the site when replication broken?

Replication may fail because of different factors. You don't want to shot down the site each time replication fails. Instead, you should think of a solution for this situation: when replication fails, how to keep the site up.

It seems that you have master-master setup for the replication. I suggest to let users (application) connect to the closer server when they are reading, and perform all writing operations on one master, call it "active master". This way, you gain the speed of reading, and you know that the replication is going in one direction. If the replication fails, you fix it while the site is still up.

On the other hand, if the master fails, you switch masters: pending master becomes the active master, and you fix the failed one, then resume the replication.

Having said that, there are some situations where you may want to shut down the site.

HTH


Migrate from innodb_file_per_table to off in MySQL

STEP #1

Set innodb_file_per_table to 0 in /etc/my.cnf

[mysqld]
innodb_file_per_table = 0

STEP #2

service mysql restart

STEP #3

Convert every InnoDB table to InnoDB again

echo "SET SQL_LOG_BIN = 0;" > /root/ConvertInnoDBToInnoDB.sql
MYSQL_CONN="-u... -p..."
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
mysql ${MYSQL_CONN} -AN -e"${SQL}" >> /root/ConvertInnoDBToInnoDB.sql
less /root/ConvertInnoDBToInnoDB.sql

Once you view he script and are satisfied, login to mysql and run this

mysql> source /root/ConvertInnoDBToInnoDB.sql

EPILOGUE

All .ibd files will vanish and all InnoDB tables and indexes will exist inside ibdata1

Give it a Try !!!


MSSQL - convert list of datetimes in a date range

Your problem is known as an "Islands and Gaps" problem. As far as I can see your solutions is correct (given that I understood what you try to achieve) up until:

,LEAD(StartDate,1,StartDate) OVER(ORDER BY StartDate) AS EndDate

Why are you replacing the EndDate you calculated in MinMax? Try:

SELECT
    [UserId], [RoomId], [TypeId]
    ,StartDate
    ,EndDate
FROM
    MinMax
ORDER BY
    StartDate
   ,EndDate

Regarding how you calculate the group, I assume that a reservation is for the same room, and type during the whole period. You may want to consider something like:

,ROW_NUMBER() OVER (PARTITION BY [RoomId], [TypeId]
                    ORDER BY [Date] ASC)
-ROW_NUMBER() OVER (PARTITION BY [UserId], [RoomId], [TypeId]
                    ORDER BY [Date] ASC) AS Grp

In your query, GRP changes regardless of which room or type that interferes with the enumeration of dates.

PS. Welcome to the forum. A Very good first question. If GRP puzzles you, I suggest that you look at the row_number functions individually, before looking at their difference DS.

EDIT:

The idea you used uses the fact that some other user interrupts the current ordering, and thereby creates a new grp. Since several users can rent the room at the same time, I don't think that will work. Here is one idea (date is a reserved word so I changed it to dt to be able to get rid of the annoying quotes []):

with start_period as (
    select a.userid, a.roomid, a.typeid, a.dt as start_date
    from Reservations a
    where not exists (
        select 1 from Reservations b
        where a.userid = b.userid
          and a.roomid = b.roomid
          and a.typeid = b.typeid
          and a.dt = dateadd(day, 1, b.dt)
    )
), end_period as (
    select a.userid, a.roomid, a.typeid, a.dt as end_date
    from Reservations a
    where not exists (
        select 1 from Reservations b
        where a.userid = b.userid
          and a.roomid = b.roomid
          and a.typeid = b.typeid
          and a.dt = dateadd(day, -1, b.dt)
    )
)
select x.userid, x.roomid, x.typeid, x.start_date
     , min(y.end_date) as end_date
from start_period x
join end_period y
    on x.userid = y.userid
    and x.roomid = y.roomid
    and x.typeid = y.typeid
    and x.start_date <= y.end_date
group by x.userid, x.roomid, x.typeid, x.start_date;

1   1   2   2020-04-01T00:00:00Z    2020-04-03T00:00:00Z
1   1   2   2020-04-05T00:00:00Z    2020-04-07T00:00:00Z
1   1   2   2020-04-10T00:00:00Z    2020-04-10T00:00:00Z
2   1   2   2020-04-01T00:00:00Z    2020-04-03T00:00:00Z
2   1   2   2020-04-05T00:00:00Z    2020-04-05T00:00:00Z
2   1   3   2020-04-06T00:00:00Z    2020-04-07T00:00:00Z

Fiddle

The idea is pretty simple, get all dates (for a given user, room, type) where there is no predecessor. Then get all dates (for a given user, room, type) where there is no successor. For all start_dates, find the smallest end_date that is bigger than the start_date


connection issue / Oracle Express 11g in windows7 32bit

You most likely need to set the ORACLE_SID environment variable. This error usually means you are trying to connect to a database that doesn't exist. Also check that ORACLE_HOME is set correctly. If that doesn't fix it, check if the database service is started. If it is, check the alert log for error messages.


Cannot Access New Installation of MySQL on an Amazon EC2 Instance

While others are suggesting a full reset of the MySQL root password via launching into single user mode and then reseting the password manually through a query, that should not be the case on a clean install.

And though this question is from 2015, it’s unclear what version of MySQL is being used. So that said, using MySQL 5.7, the process of getting a password on a clean install has changed. A temporary password is now set in the MySQL daemon log file which should be in this location:

/var/log/mysqld.log

But could also be in this path:

/var/log/mysql/mysqld.log

As explained here in the official MySQL 5.7 docs the password is in that error log and can be found using Grep like this:

grep 'temporary password' /var/log/mysqld.log

You can now login to MySQL using that password by running this command and entering that password when prompted:

mysql -uroot p

Or you can run mysql_secure_installation—which is good practice regardless—and enter and change the password there with that.


Use an UPDATE JOIN

UPDATE database_a.clients A INNER JOIN database_b.clients B
USING (id) SET B.email = A.email;

or

UPDATE database_a.clients A INNER JOIN database_b.clients B
ON A.id = B.id SET B.email = A.email;

Give it a Try !!!


Can this method for unit testing a stored procedure be improved?

I have written a series of blogs on unit testing select stored procedures using DBTestUnit that might be of use.


MAX(id) function in query with indexed condition

DATE(), not MAX() is the villain...

DATE(createdAt) <= '2020-02-07' is not "sargable".

This has the equivalent effect, and can use INDEX(created_at):  created_at <= '2020-02-07'.  Yes, that works for datatypesDATE,DATETIME, andTIMESTAMP`.


Full outer join is not supported. If you look at:

https://dev.mysql.com/doc/refman/5.7/en/join.html

there is only LEFT and RIGHT outer join. You can however mimic FULL OUTER with a UNION between LEFT and RIGHT:

SELECT user_record.id AS user_id,
       user_record.password, user_addresses.street
FROM `user_record`
LEFT JOIN `user_addresses`
    ON user_record.id = user_addresses.user_id

UNION

SELECT user_addresses.user_id,
       user_record.password, user_addresses.street
FROM `user_record`
RIGHT JOIN `user_addresses`
    ON user_record.id = user_addresses.user_id

ORDER BY user_id ;

You have to include user_record.id / user_addresses.user_id in your select clause to be able to order by it.


datetime in Adaptive Server Enterprise (and SQL Server, since they share a common-code-base that included the datetime type) is stored using 8 bytes.  4 bytes for the date, and 4 bytes for the time.  You can see this by looking at the binary version of a datetime:

SELECT CONVERT(varbinary(8), CONVERT(datetime, '1753-01-01T00:00:00.000'), 0)
SELECT CONVERT(varbinary(8), CONVERT(datetime, '1900-01-01T00:00:00.000'), 0)
SELECT CONVERT(varbinary(8), CONVERT(datetime, '1900-01-02T00:00:00.000'), 0)
SELECT CONVERT(varbinary(8), CONVERT(datetime, '3000-12-31T23:59:59.997'), 0)

The four binary values are:

0xFFFF2E4600000000
0x0000000000000000
0x0000000100000000
0x000622D3018B81FF

Take the following, which shows where the 1/300 of a second comes into play:

SELECT CONVERT(varbinary(8), CONVERT(datetime, '1900-01-01T00:00:00.000'), 0)
SELECT CONVERT(varbinary(8), CONVERT(datetime, '1900-01-02T00:00:00.000'), 0)
SELECT CONVERT(varbinary(8), CONVERT(datetime, '1900-01-02T00:00:00.003'), 0)

The difference between the 2nd and 3rd values, is one:

0x0000000000000000
0x0000000100000000
0x0000000100000001

So dates are stored in the most significant 4 bytes; and times are stored in the least significant 4 bytes.  Although moving to a precision greater than 3 milliseconds (1/300 of a second) would be possible in 4 bytes of storage; that is all the precision that is actually used.

In SQL Server, you can use a datetime2(7) data type to get precision down to 7 digits, with an accuracy of 100ns:

SELECT CONVERT(varbinary(16), CONVERT(datetime2(7), '1900-01-01T00:00:00.0000000'), 0)
SELECT CONVERT(varbinary(16), CONVERT(datetime2(7), '1900-01-01T00:00:00.0000001'), 0)
SELECT CONVERT(varbinary(16), CONVERT(datetime2(7), '1900-01-01T00:00:00.0000002'), 0)

The storage of these values are slightly different, however you can still see the binary value incrementing:

0x0700000000005B950A
0x0701000000005B950A
0x0702000000005B950A

I'm using the Sybase ISQL client; Sybase CTISQL Utility/15.7/P-EBF20996 SP100/DRV.15.7.0.10

As an aside, a small difference exists between how Adaptive Server Enterprise (ASE) and SQL Server round datetime values.  In SQL Server, milliseconds are rounded to 0.000, 0.003, and 0.007, whereas in ASE they are rounded to 0.000, 0.003, and 0.006 - why there is a difference is not documented as far as I can tell.  You can see this on ASE by running this query:

SELECT CONVERT(varchar(50), CONVERT(datetime, N'2017-01-01T23:59:59.997'), 139);

Which returns:

Jan  1 2017 23:59:59.996000

Whereas on SQL Server, the equivalent code, SELECT CONVERT(varchar(50), CONVERT(datetime, N'2017-01-01T23:59:59.997'), 109); , returns:

Jan  1 2017 11:59:59:997PM


Logins mapped to dropped database sql server

Try the following query:

SELECT * FROM syslogins WHERE dbname = 'ABC'

If there are logins still using that as their default database, this should help you find them.  However, if the users had a default database of master and the application performed a USE ABC command, then you'll have no way of knowing they were tied to the application.  It's only if the application was kind enough to create your SQL logins with a default database of 'ABC' will you be able to track things down.


backup maintenance plan fails with error 0x80131534

Grant the access to the computer account since the service is running under NT Service\InstanceName: domain_name>\$

Example: Domain1\MyServerName$

MyServerName must be the name of the server, not the name of the SQL instance. Get the server name via hostname in a cmd window. By adding the dollar sign to the end, it's the computer account.


Oracle data Guard status (active/passive)- version 11g

No. The redo (archivelogs) can be transported without applying it on the standby.
sequence# alone is not enough. You need to consider resetlogs_change# too.
An expired archivelog entry is an archivelog entry that exists in the controlfile, but it does not exist on the disk or the database is unable to access it. Such a command does not delete archivelogs from the filesystem.

The standby database was disabled in the broker configuration. Enable it in dgmgrl:

enable database ABCUAT1;

If you are in luck and the standby has all the necessary logs, it will start processing them and it will eventually catch up to the primary. You should see a decreasing transport/apply lag value instead of unknown. If it is unable to do so, newer errors will be shown.

At this point you may have everything on the standby site which just needs processing. Or you could also have an archive gap when the standby is unable to continue due to missing logs, which you can resolve by restoring the missing logs or rolling forward the standby with an incremental backup or rebuilding the standby. Or the standby could be in a totally different state, like a snapshot standby, without the broker being aware of it.


SQL Server 2008 (r1) introduced Extended Events and with that came SQL Server Audit and works similar to XEvents to audit events. So it is less intrusive than C2 Auditing and server side traces.

It is very granular and fairly easy to setup. This feature is available in all editions of SQL Server 2008 R2. A good write-up intro/how-to can be found here by Brad McGehee. Thomas LaRock also did a great article on Simple-Talk walking through creating/setting up the audit.

Reading through the articles mentioned above you will note that you can have the events logged to different places (windows app log, file, etc.). From that you can then write PowerShell scripts or T-SQL scripts to alert you of whatever you want. Much more easy than screwing with C2 Audit trace files.


Postgres 8.3 correct deletion of files in pgsql_tmp

Rather than seeking better ways to suture up your foot, you should just stop shooting yourself in the foot.

All that pg_terminate_backend does is check for permission, then deliver a SIGTERM. You can do this directly by using kill -15 PID rather than kill -9 PID.  This will give that one process a chance to clean up after itself before exiting.  More importantly, it will not needlessly kill every other process and trigger a crash recovery.

If that process is so wedged that it won't respond to kill -15, then pg_terminate_backend wouldn't help anyway.  And in that case, it is probably a bug, and using software that is not 5 years past its EOL would likely help there.  If it is wedged like that, then rather than killing it with -9, you could probably just restart the server with pg_ctl restart -mi.  It would accomplish the same thing, but would clean up the files for itself.

If you are really only interested in better ways to suture up your foot, then your option 1 should work fine.  Option 2 seems pointless, since 1 works.

Your option 3 should also work as long as one day is long enough to avoid any still-running processes.  If you remove a file that some process still needed, the worst that happens is that that one process throws an error (which you must already be used to, as a user of kill -9). I would say it is not a "best practice", but since you are running long out-of-support software and routinely using kill -9, the best practice ship has already sailed.


Storing the data in a single column is the preferred way, since they are inextricably linked.  A point in time is a single piece of information, not two.

A common way of storing date/time data, employed "behind the scenes" by many products, is by converting it into a decimal value where the "date" is the integer portion of the decimal value, and the "time" is the fractional value.  So, 1900-01-01 00:00:00 is stored as 0.0 and September 20th, 2016 9:34:00 is stored as 42631.39861.  42631 is the number of days since 1900-01-01.  .39861 is the portion of time elapsed since midnight.  Don't use a decimal type directly to do this, use an explicit date/time type; my point here is just an illustration.

Storing the data in two separate columns means you'll need to combine both column values any time you want to see if a given point in time is earlier or later than the stored value.

If you store the values separately, you'll invariably run into "bugs" that are difficult to detect.  Take for instance the following:

IF OBJECT_ID('tempdb..#DT') IS NOT NULL
DROP TABLE #DT;
CREATE TABLE #DT
(
    dt_value DATETIME NOT NULL
    , d_value DATE NOT NULL
    , t_value TIME(0) NOT NULL
);

DECLARE @d DATETIME = '2016-09-20 09:34:00';

INSERT INTO #DT (dt_value, d_value, t_value)
SELECT @d, CONVERT(DATE, @d), CONVERT(TIME(0), @d);

SET @d = '2016-09-20 11:34:00';

INSERT INTO #DT (dt_value, d_value, t_value)
SELECT @d, CONVERT(DATE, @d), CONVERT(TIME(0), @d);

/* show all rows with a date after 2016-07-01 11:00 am */
SELECT *
FROM #DT dt
WHERE dt.dt_value >= '2016-07-01 11:00:00';

/* show all rows with a date after 2016-07-01 11:00 am */
SELECT *
FROM #DT dt
WHERE dt.d_value >= CONVERT(DATE, '2016-07-01')
    AND dt.t_value >= CONVERT(TIME(0), '11:00:00');

In the above code, we're creating a test table, populating it with two values, then performing a simple query against that data.  The first SELECT returns both rows, however the second SELECT only returns a single row, which may not be the desired outcome:

The correct way to filter a date/time range where the values are in discrete columns, as pointed out by @ypercube in comments, is:

WHERE dt.d_value > CONVERT(DATE, '2016-07-01') /* note there is no time component here */
    OR (
        dt.d_value = CONVERT(DATE, '2016-07-01')
        AND dt.t_value >= CONVERT(TIME(0), '11:00:00')
    )

If you need the time component separated for analysis purposes, you could consider adding a calculated, persisted, column for the time portion of the value:

ALTER TABLE #DT
ADD dt_value_time AS CONVERT(TIME(0), dt_value) PERSISTED;

SELECT *
FROM #dt;

The persisted column could then be indexed allowing for fast sorts, etc, by time-of-day.

If you are considering splitting the date and time into two fields for display purposes, you should realize that formatting should be done at the client, not the server.


How to create column in DB with default value random string

The solution is (for PGSQL):

alter TABLE users ADD column register_key text NOT NULL default md5(random()::text);


Counting on Two Different Columns in the Same Table

John,

If you have a table of users, and a table of matches, you should probably have a reference table between the two of them, that has a many-to-many relationship.

users:
--------------------
id (PK)       Int
display_name  VarChar(250)

matches:
---------------------
matchId (PK)   Int
...
...
  -- Other relevant attributes about the match

results:
---------------------
ResultId (PK)      Int
Name           Varchar(100)
  -- Win, loss, whatever else

match_results:
---------------------
matchId (PK, FK)  Int
UserId (PK, FK)   Int
ResultId (PK, FK) Int

For every match, you should have a record for every participant in that match, and their relevant result. Unless you plan on having more than 2 billion matches or users, I would stick to using Int.

Now you can join across these tables and SUM() or MAX() or whatever aggregate you would prefer.

SELECT u.Name ,
   COUNT( CASE
          WHEN r.Name = 'Win' THEN 1
          END )AS Wins ,
   COUNT( CASE
          WHEN r.Name = 'Loss' THEN 1
          END )AS Losses
FROM match_results AS mr INNER JOIN users AS u ON mr.userId = u.id
                       INNER JOIN matches AS m ON mr.matchId = m.id
                       INNER JOIN results AS r ON mr.resultId = r.id;

Edit:

Using your existing schema, you can use sub queries to aggregate your statistics

  SELECT u.display_name AS NAME,
    (SELECT COUNT(1) FROM match_results mr WHERE mr.winner_id = u.id) AS Wins,
    (SELECT COUNT(1) FROM match_results mr WHERE mr.loser_id = u.id) AS Losses
  FROM users u


Oracle 11GR2 - When will datafile autoextend?

Extents will spread evenly:

SQL> select * from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SQL> create tablespace test datafile size 10M autoextend on next 10M maxsize 100M;

Tablespace created.

SQL> create table t1 (id number) tablespace test;

Table created.

SQL> alter table t1 allocate extent (size 50M);

Table altered.

SQL> select * from dba_tablespace_usage_metrics where tablespace_name = 'TEST';

TABLESPACE_NAME                USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
TEST                                 6536           12800      51.0625

SQL> alter tablespace test add datafile size 10M autoextend on next 10M maxsize 100M;

Tablespace altered.

SQL> select file_id, sum(blocks) from dba_extents where tablespace_name = 'TEST' group by file_id;

   FILE_ID SUM(BLOCKS)
---------- -----------
         7        6408

SQL> alter table t1 allocate extent (size 30M);

Table altered.

SQL> select file_id, sum(blocks) from dba_extents where tablespace_name = 'TEST' group by file_id;

   FILE_ID SUM(BLOCKS)
---------- -----------
         8        1536
         7        8712


Mysql: Create a view with multiple self joins without duplicates in result

Inner Join:

An inner join produces a result set that is limited to the rows where there is a match in both tables for what we're looking for.  If you don't know which kind of join you need, this will usually be your best bet.

Left Outer Join:

Add to Where

... and establishment_id is not null and department_id is not null

And sometimes just to make it complete i do also

... and establishment_id<>'' and department_id<>''