What can I do to speed up this query which aggregates over an interval? I have a star schema database in Redshift. I'm running an aggregation on a fact table order_facts with the following relevant columns:

total - FLOAT - total order cost

payment_date - INTEGER - payment date in the format YYYYMMDD

shop_id - INTEGER foreign key to the shops dimension

I'm using the following dimension tables:

DATES

id - INTEGER - date in format YYYYMMDD

date - DATE - SQL date formatted field

SHOPS

id - INTEGER - primary key for the table

created - DATETIME - timestamp for when the shop was created

My goal is to obtain a table of the total amount of orders for each shop in its first 30 days.

My query is this, and should give an accurate answer. However, it's taking over a half-hour to run:

SELECT of.shop_id, SUM(of.total) FROM order_facts of
INNER JOIN shops s
ON of.shop_id = s.id
INNER JOIN dates d
ON of.payment_date = d.id
WHERE d.date <= (s.created + INTERVAL '30 days')
GROUP BY of.shop_id

I tried rewriting it like this, but the query still does not finish after over 20 minutes of runtime

SELECT SUM(r.total), r.shop_id
FROM (
    SELECT s.created, of.shop_id, of.total, of.payment_date
    FROM order_facts of
    INNER JOIN shops s
    ON of.shop_id = s.id
) r
INNER JOIN dates d
ON r.payment_date = d.id
WHERE d.date <= (r.created + INTERVAL '30 days')
GROUP BY r.shop_id

I'm not currently understanding why it's taking so long to run. Understanding that would help me know how to correct the query. Likewise, seeing a better version of the above query would help me understand where the inefficiency exists. Either way is extremely helpful for me.

Just getting the total order amount per shop for the past X days, or all-time, is a very fast query. Thus it seems I'm doing something suboptimal with the join to the dates table, but it's not clear what.

EDIT: output of EXPLAIN

1
XN HashAggregate (cost=868301988.49..868301991.01 rows=1009 width=12)
2
-> XN Merge Join DS_DIST_NONE (cost=0.00..868286973.07 rows=3003084 width=12)
3
Merge Cond: ("outer".id = "inner".payment_date)
4
Join Filter: ("inner".shop_id = "outer".id)
5
-> XN Nested Loop DS_BCAST_INNER (cost=0.00..1773635510.00 rows=32472000 width=8)
6
Join Filter: (("outer".date)::timestamp without time zone > ("inner".created + '30 days'::interval))
7
-> XN Seq Scan on dates d (cost=0.00..110.00 rows=11000 width=8)
8
-> XN Seq Scan on shops s (cost=0.00..88.56 rows=8856 width=12)
9
-> XN Seq Scan on order_facts "of" (cost=0.00..90092.52 rows=9009252 width=16)
10
----- Nested Loop Join in the query plan - review the join predicates to avoid Cartesian products -----

I made a subquery that built daily order totals by shop, then ran aggregations over that table. Execution time was ~6 seconds. Final query looked like this:

SELECT s.shop_name, rrr.first_thirty_day_total FROM (
    SELECT SUM(rr.daily_total) AS first_thirty_day_total, rr.shop_id FROM (
        SELECT d.date, r.daily_total, r.shop_id FROM (
            SELECT SUM(of.total) AS daily_total, of.shop_id, of.payment_date
            FROM order_facts of
            GROUP BY shop_id, payment_date
        ) r
        INNER JOIN dates d
        ON r.payment_date = d.id
    ) rr
    INNER JOIN shops s
    ON s.id = rr.shop_id
    WHERE rr.date <= s.created + INTERVAL '30 days'
    GROUP BY rr.shop_id
) rrr
INNER JOIN shops s
ON rrr.shop_id = s.id


Is there a way to examine SQL Jobs for references to linked servers? In preparation for migrating a server farm to the cloud, I am attempting to enumerate all the linked servers that have been defined over the years.  I also need to find (and correct) the code that references these linked servers.

Here is something I threw togetether to help identify names of objects that refer to the linked server, but this only works against stored procs, views, functions and triggers:

declare @sql varchar(2000)
select  @SQL = 'use ?;
print ''?'';
print replicate(''-'',len(''?''));
print '' '';

select object_name(sc.id)
  from syscomments sc
  where text like ''%TheLinkedServer%''
'

exec sp_msforeachdb @sql

I know I will have to crack open the DTS/SSIS packages...I am just trying to get as many finished as quickly as possible.

To query for jobs, you just need to look in msdb's job tables:

SELECT j.name
  FROM msdb.dbo.sysjobs AS j
  INNER JOIN msdb.dbo.sysjobsteps AS s
  ON j.job_id = s.job_id
  WHERE s.command LIKE '%TheLinkedServer%';

Also syscomments is a backward compatibility view - it is not the best way to search for references in objects, particularly because any object > 4000 characters will be stored in multiple rows and could, potentially, have the linked server name straddle two rows. Much safer would be:

SELECT o.name FROM sys.objects AS o
  INNER JOIN sys.sql_modules AS m
  ON o.[object_id] = m.[object_id]
  WHERE m.definition LIKE '%TheLinkedServer%';

And finally, sp_msforeachdb is completely unreliable (see here and here for a better replacement).

Here is a handy search procedure I wrote last year that may be useful:

CREATE PROCEDURE dbo.FindString
   @search_string            NVARCHAR(4000),
   @database_list            NVARCHAR(MAX) = NULL,
   @case_sensitive           BIT = 0,
   @include_jobs             BIT = 0,
   @include_columns          BIT = 0,
   @include_parameters       BIT = 0,
   @include_system_objects   BIT = 0,
   @include_system_databases BIT = 0
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE
       @init_sql  NVARCHAR(MAX),
       @run_sql   NVARCHAR(MAX),
       @dbname    NVARCHAR(128),
       @all_text  NVARCHAR(10),
       @coll_text NVARCHAR(50);

   CREATE TABLE #t
   (
       [database]      SYSNAME,
       [schema]        SYSNAME,
       [object]        SYSNAME,
       [type]          SYSNAME,
       [create_date]   DATETIME,
       [modify_date]   DATETIME,
       [definition]    NVARCHAR(MAX)
   );

   CREATE TABLE #j
   (
       [job_name]      SYSNAME,
       [step_id]       INT,
       [step_name]     SYSNAME,
       [create_date]   DATETIME,
       [modify_date]   DATETIME,
       [definition]    NVARCHAR(MAX)
   );

   CREATE TABLE #cp
   (
       [database]      SYSNAME,
       [schema]        SYSNAME,
       [object]        SYSNAME,
       [type]          SYSNAME,
       [create_date]   DATETIME,
       [modify_date]   DATETIME,
       [param]         NVARCHAR(128),
       [column]        NVARCHAR(128)
   );

   SELECT
       @all_text = CASE @include_system_objects
           WHEN 1 THEN N'all_' ELSE N'' END,
       @coll_text = CASE @case_sensitive
       WHEN 1 THEN N'COLLATE Latin1_General_BIN' ELSE N'' END;

   SET @init_sql = N'SELECT
           [database] = ''$db$'',
           [schema]   = QUOTENAME(s.name),
           [object]   = QUOTENAME(o.name),
           [type]     = o.type_desc,
           o.create_date,
           o.modify_date,
           m.[definition]
       FROM
           $db$.sys.$all$sql_modules AS m
       INNER JOIN
           $db$.sys.$all$objects AS o
           ON m.[object_id] = o.[object_id]
       INNER JOIN
           $db$.sys.schemas AS s
           ON o.[schema_id] = s.[schema_id]
       WHERE
           m.definition $coll$
            LIKE N''%'' + @search_string + ''%'' $coll$;';

   SET @init_sql = REPLACE(REPLACE(@init_sql,
       '$all$', @all_text), '$coll$', @coll_text);

   SET @search_string = REPLACE(@search_string, '''', '''''');

   DECLARE c CURSOR
       LOCAL STATIC FORWARD_ONLY READ_ONLY
       FOR
           SELECT QUOTENAME(d.name)
               FROM
                   sys.databases AS d
               LEFT OUTER JOIN
                   dbo.SplitStrings_XML(@database_list, N',') AS s
                   ON 1 = 1
               WHERE
                   (
                     LOWER(d.name) = LOWER(s.Item)
                     OR NULLIF(RTRIM(@database_list), N'') IS NULL
                   )
                   AND d.database_id BETWEEN CASE @include_system_databases
                   WHEN 1 THEN 1 ELSE 5 END AND 32766
               ORDER BY d.name;
   OPEN c;

   FETCH NEXT FROM c INTO @dbname;

   WHILE @@FETCH_STATUS = 0
   BEGIN
       SET @run_sql = REPLACE(@init_sql, N'$db$', @dbname);

       INSERT #t
       EXEC sp_executesql
           @run_sql,
           N'@search_string NVARCHAR(4000)',
           @search_string;

       IF @include_columns = 1
       BEGIN
           SET @run_sql = N'SELECT
                [database] = ''$db$'',
                [schema]   = QUOTENAME(s.name),
                [object]   = QUOTENAME(o.name),
                [type]     = o.type_desc,
                o.create_date,
                o.modify_date,
                NULL,
                c.name
            FROM
                $db$.sys.$all$columns AS c
            INNER JOIN
                $db$.sys.$all$objects AS o
                ON c.[object_id] = o.[object_id]
            INNER JOIN
                $db$.sys.schemas AS s
                ON o.[schema_id] = s.[schema_id]
            WHERE
                c.name $coll$
                  LIKE N''%'' + @search_string + ''%'' $coll$;';

           SET @run_sql = REPLACE(REPLACE(REPLACE(@run_sql,
               '$all$', @all_text), '$coll$', @coll_text), '$db$', @dbname);

           INSERT #cp
           EXEC sp_executesql
               @run_sql,
               N'@search_string NVARCHAR(4000)',
               @search_string;
       END

       IF @include_parameters = 1
       BEGIN
           SET @run_sql = N'SELECT
                [database] = ''$db$'',
                [schema]   = QUOTENAME(s.name),
                [object]   = QUOTENAME(o.name),
                [type]     = o.type_desc,
                o.create_date,
                o.modify_date,
                p.name,
                NULL
            FROM
                $db$.sys.$all$parameters AS p
            INNER JOIN
                $db$.sys.$all$objects AS o
                ON p.[object_id] = o.[object_id]
            INNER JOIN
                $db$.sys.schemas AS s
                ON o.[schema_id] = s.[schema_id]
            WHERE
                p.name $coll$
                  LIKE N''%'' + @search_string + ''%'' $coll$;';

           SET @run_sql = REPLACE(REPLACE(REPLACE(@run_sql,
               '$all$', @all_text), '$coll$', @coll_text), '$db$', @dbname);

           INSERT #cp
           EXEC sp_executesql
               @run_sql,
               N'@search_string NVARCHAR(4000)',
               @search_string;
       END

       FETCH NEXT FROM c INTO @dbname;
   END

   CLOSE c;
   DEALLOCATE c;

   SELECT 'Objects:';

   SELECT
       [database],
       [schema],
       [object],
       [type],
       [definition] = CONVERT(XML, '<?query --
           USE ' + [database] + ';'
           + CHAR(13) + CHAR(10) + 'GO'
           + CHAR(13) + CHAR(10) + [definition] + ' --?>'),
       first_line = (DATALENGTH(abbrev_def)
           -DATALENGTH(REPLACE(abbrev_def, CHAR(13), '')))/2 + 1,
       create_date,
       modify_date
   FROM
   (
       SELECT
           *,
           [count] = (DATALENGTH([definition])
               - DATALENGTH(REPLACE([definition], @search_string, '')))
               /DATALENGTH(@search_string),
           abbrev_def = SUBSTRING([definition], 1,
               CHARINDEX(@search_string, [definition]))
       FROM #t
   ) AS x
   ORDER BY [database], [schema], [object];

   IF @include_jobs = 1
   BEGIN
       SELECT 'Jobs:';

       SET @run_sql = N'SELECT
                job_name = j.name,
                s.step_id,
                s.step_name,
                j.date_created,
                j.date_modified,
                [definition] = s.command
            FROM msdb.dbo.sysjobs AS j
            INNER JOIN msdb.dbo.sysjobsteps AS s
            ON j.job_id = s.job_id
            WHERE s.command $coll$
              LIKE ''%'' + @search_string + ''%'' $coll$
            ORDER BY j.name, s.step_id;';

       SET @run_sql = REPLACE(@run_sql, '$coll$', @coll_text);

       INSERT #j EXEC sp_executesql
           @run_sql,
           N'@search_string NVARCHAR(4000)',
           @search_string;

       SELECT
           job_name,
           step_id,
           step_name,
           [command] = CONVERT(XML, '<?query --
               ' + [definition] + ' --?>'),
           create_date,
           modify_date
       FROM #j;
   END

   IF @include_columns = 1 OR @include_parameters = 1
   BEGIN
       SELECT 'Columns/parameters';

       SELECT
           [database],
           [schema],
           [object],
           [type],
           [param],
           [column],
           create_date,
           modify_date
       FROM #cp
       ORDER BY [database], [schema], [object], [param], [column];
   END

   DROP TABLE #t, #j, #cp;
END
GO

It relies on a split function like this one (or your own, if you already have one or, better yet, STRING_SPLIT() if you are on SQL Server 2016 or better and can deal with its limitations):

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(3)
)
RETURNS TABLE
AS
   RETURN
   (
       SELECT Item = CONVERT(NVARCHAR(128), Item)
       FROM
       (
           SELECT Item = x.i.value('(./text())[1]', 'NVARCHAR(128)')
           FROM
           (
               SELECT [XML] = CONVERT(XML, ''
                    + REPLACE(@List, @Delimiter, '')
                    + '').query('.')
           ) AS a
           CROSS APPLY
           [XML].nodes('i') AS x(i)
       ) AS y
       WHERE Item IS NOT NULL
   );


SQL Server instance level action tracking How can we track actions happening at instance level in SQL Server 2014 .e.g. would like to know who created database, who changed recovery model, who setup backup strategy, who gave permissions to particular user etc..

Is there any native way or external tool effective for this scenario. Please let me know.

Actions happening at the instance level can be monitored by one of the following:

SQL Server Audit feature

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-ver15

DATABASE_CHANGE_GROUP - who created database
SERVER_PERMISSION_CHANGE_GROUP - who gave server-level permissions to particular login
DATABASE_PERMISSION_CHANGE_GROUP - who gave database-level permissions to particular user
... etc

Extended Events feature

Provides some additional benefits and is highly configurable, allows to track 1000+ different kind of events and will cover most your needs

DDL and Logon triggers


SSIS Transfer Login Task Error I have created an SSIS Transfer Logins Task. I have selected the SourceConnection and DestinationConnection and both valid and active.

I have selected certain Windows group accounts to transfer from source to destination databases. IfObjectExists is set to 'Overwrite' and CopySids is 'True'.

When I run the task I get the following error:

  SSIS package "F:\SQL Server\SSDT\acQuire Migration\acQuire Migration SSIS\acQuire Migration SSIS\Package.dtsx" starting.
   Error: 0xC002F325 at Transfer Logins Task, Transfer Logins Task: Execution failed with the following error: "An exception occurred in SMO.".
   Error: 0xC002F325 at Transfer Logins Task, Transfer Logins Task: Execution failed with the following error: "Value cannot be null.
   Parameter name: server".
   Task failed: Transfer Logins Task
   Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
  SSIS package "F:\SQL Server\SSDT\acQuire Migration\acQuire Migration SSIS\acQuire Migration SSIS\Package.dtsx" finished: Failure.
  The program '[1868] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).

Instead of going through the rabbit hole of finding a fix for the error, I would highly suggest you use dbatools
Copy-DbaLogin

This command migrates logins from source to destination SQL Servers. Supports SQL Server versions 2000 and above. Migrates logins with
SIDs
passwords
defaultdb
server roles & securables
database permissions & securables
login attributes (enforce password policy, expiration, language, etc).

e.g.
Copy-DbaLogin -Source sqlserver -Destination sqlcluster

and there are many more commands that will make your life easier


Postgres SELECT rows where JSON array contains "most" of values I have the following table structure:

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    labels json
);

INSERT INTO items (labels) VALUES
('{"labels":[{"value":"apple","score":0.95},{"value":"fruit","score":0.94},{"value":"red","score":0.93}]}'),
('{"labels":[{"value":"apple","score":0.92},{"value":"fruit","score":0.92},{"value":"green","score":0.93}]}'),
('{"labels":[{"value":"orange","score":0.92},{"value":"fruit","score":0.92},{"value":"orange","score":0.90}]}'),
('{"labels":[{"value":"tomato","score":0.98},{"value":"vegetable","score":0.96},{"value":"red","score":0.95}]}'),
('{"labels":[{"value":"carrot","score":0.94},{"value":"vegetable","score":0.93},{"value":"orange","score":0.92}]}')
('{"labels":[{"value":"peach","score":0.92},{"value":"fruit","score":0.92},{"value":"yellow","score":0.91}]}')

I'm trying to find a way to query this table by the labels column, using the "intersection" criteria with a factor of similarity >= 2.

e.g. if I set ["peach", "fruit", "orange"] array as the WHERE clause input, then the result should be:

 id|
---+
 3 |
---+
 6 |
---+

You can use exists combined with json_array_elements

select id from items where exists
    (select 1 from json_array_elements(labels->'labels') f(x)
        where x->>'value' in ('peach', 'fruit', 'orange')
        having count(*)>=2
    );

It gives the answer you want, but good luck making it fast if the table is large.  (Changing from JSON to JSONB might speed it up slightly)


Data from two table in SQL server 2008 R2 i have 2 tables:

Codes table with following column:

                         Name, Code

History table with following column:

                         Name, BU, Plan.

Now, I want code column of Codes table in place of Name column of History table.

I.e History table should contain Code, BU, Plan.

Here how can i achieve this?

Assuming Code is a VARCHAR(32) (just change the first line if my assumption is incorrect):

ALTER TABLE dbo.History ADD Code VARCHAR(32);

UPDATE h
  SET Code = c.Code
  FROM dbo.History AS h
  INNER JOIN dbo.Codes AS c
  ON h.Name = c.Name;

-- verify first, then:

ALTER TABLE dbo.History DROP COLUMN Name;

If you absolutely need the columns to be in the same order (you shouldn't), you can rebuild the table as follows:

-- first, drop all constraints that point to dbo.History. Then:

SELECT Code, BU, Plan
  INTO dbo.HistoryCopy
  FROM dbo.History;

EXEC sp_rename 'dbo.History', 'dbo.HistoryOld', 'OBJECT';
EXEC sp_rename 'dbo.HistoryCopy', 'dbo.History', 'OBJECT';

-- now re-create the constraints / indexes etc. on the new copy

In order to prevent user activity from affecting or being affected, you'll want to do this during a maintenance window, or surround the above with a serializable transaction.

When you're happy that the change is successful, you can drop the old copy:

DROP TABLE dbo.HistoryOld;


Shrink configuration & content database in Sharepoint DBs There was some argument between me and a college about for how we should shrink DBs. We knew that shrinking should be manual and only if required.

We run in a case that configuration DBs reached to 10 GB. My debate question here is shrinking to 10 mb is good choice , or leave it to 1 GB.

Thank you.

Just leave it at 10GB, it will grow on you. If you absolutely need to shrink the datafile for the configuration database then don't go to the smallest possible option but leave some headroom.


DB Integrity Check Lock Database Hi all i'm using The Integrity Check script from http://ola.hallengren.com to check through my database of roughly 65 million records. I was just wondering if this will lock my database? In terms of preventing any inserts, deletes, updates, etc. Just thinking in terms of scheduling my maintenance plans, etc.

Essentially no.

Briefly looking at the script you have referenced shows that it is making use of DBCC CHECKDB in order to perform the database integrity checks.

This will not lock the database to modifications. CHECKDB works on a consistent view of your database by creating a Database Snapshot.

Locks are of course acquired throughout the integrity checking process but these will not be long term blocking unless you explicity request them to be, for example by using the TABLOCK option for CHECKDB (which can be requested via a parameter in Ola's script).

The best practice advice would be for you to perform the desired maintenance operation in a controlled test environment first. This will give you an idea also of at least how long you can expect CHECKDB to take in Production.

Additional Thoughts: Dependant on your requirements, say for example you only manage a few databases, deploying an automated solution such as Ola's may in fact be overkill when a simple SQL Server Agent Job that calls the CHECKDB command may suffice. If you just need to check a single database then see my blog post Simplify Your DBCC CHECKDB Output for guidance.


Add DEFAULT to existing column In Microsoft SQL server, how can I add/change the default on an existing table column using T-SQL?

ALTER TABLE yourTable ADD  CONSTRAINT constraintName
   DEFAULT ('XYZ') FOR [YourColumn]

To change the default, drop the constraint and re-add it with the new value:

ALTER TABLE yourTable
DROP CONSTRAINT constraintName

ALTER TABLE yourTable ADD  CONSTRAINT constraintName
   DEFAULT ('ABC') FOR [YourColumn]


Performance of count() vs count() in a window function I'm having a hard time trying to figure the performance gap between a raw count() query vs a count() OVER() window function.

Here is the test setup. Let's create a dummy table:

CREATE TABLE main (id int);
INSERT INTO main VALUES (generate_series(1,1000000));
VACUUM ANALYZE main;

So far so good, counting the whole set takes around 60ms using a parallel sequential scan:

SELECT count(*) FROM main;

Finalize Aggregate  (cost=10633.55..10633.56 rows=1 width=8) (actual time=65.260..65.260 rows=1 loops=1)
  ->  Gather  (cost=10633.33..10633.54 rows=2 width=8) (actual time=65.229..65.256 rows=3 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=9633.33..9633.34 rows=1 width=8) (actual time=45.728..45.729 rows=1 loops=3)
              ->  Parallel Seq Scan on main  (cost=0.00..8591.67 rows=416667 width=0) (actual time=0.008..24.864 rows=333333 loops=3)
Planning time: 0.783 ms
Execution time: 67.769 ms

Now let's try to return a few rows along with the count :

explain analyze SELECT id, count(*) OVER()
FROM main
LIMIT 10;

Limit  (cost=0.00..0.27 rows=10 width=12) (actual time=1077.092..1077.100 rows=10 loops=1)
  ->  WindowAgg  (cost=0.00..26925.00 rows=1000000 width=12) (actual time=1077.091..1077.097 rows=10 loops=1)
        ->  Seq Scan on main  (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.520..60.036 rows=1000000 loops=1)
Planning time: 0.671 ms
Execution time: 1094.541 ms

This query takes 1050ms to complete. What surprises me is that the planner chooses to do a sequential scan, then a window aggregation, and finally a limit over the whole set.

Creating an btree index didn't help, while raising the work_mem to 128MB did (dropped to 300ms). But I'm not a big fan of "if it too slow, order more servers". I'd rather understand what causes this behaviour and optimize the code.

I am aware that you can get estimates by querying the reltuples, but my question is more about understanding why there is such a performance difference between executing a SELECT + a count() and selecting and counting in the same query?

why there is such a performance difference between executing a SELECT + a count() and selecting and counting in the same query?

Because PostgreSQL isn't optimized to do what you want, clearly. The count(*) OVER () is subject to WindowAgg which presumably does more (perhaps even with an exposed  accumulator) than the PartialAggregate. And, moreover, as you pointed out one of them is subject to Parallel Seq Scan, presumably Window Functions are not subject to Parallel queries yet.

I'm sure patches are accepted.

  What surprises me is that the planner chooses to do a sequential scan, then a window aggregation, and finally a limit over the whole set.

That's to be expected, what's surprising? The LIMIT always runs last.

  creating an btree index didn't help,


SQL 2017 Standard Availability Group I am working on creating an AG on SQL Server 2017 Standard and Windows Server 2016.  I have created the failover cluster using a file share witness and everything checks out OK with the cluster.  The only issue or warnings I get are related to only using 1 NIC for each server (These are VM's with multiple NIC's).  I can connect to the SQL instance using the windows failover cluster DNS entry.  When I down one server or manually fail over the cluster, the cluster DNS entry does not update to point to the new "failed over IP"  These 2 different IP's are on 2 separate subnets.  After some time (20 minutes) the DNS entry will update to the new (failed over) IP.  I have tried to adjust the TTL setting in the cluster name DNS entry however, it changes itself back to 20 minutes.  What am I missing here?

As you don't mention it, I suspect your application is not connecting with

MultiSubnetFailover=True

SqlClient Support for High Availability, Disaster Recovery


SQL Server is running slow after a certain amount of time We've been experiencing some issues with our SQL server recently.  I have noticed that some of our SQL jobs are running longer than normal.  One job normally runs in under 3 minutes will take over a day.  A job that runs in about 15 seconds will take over 2 minutes.  These jobs calls a simple stored procedure.

I have looked at the CPU usage and memory and everything looks fine.  I am not sure what else I can look at.  It seems odd that everything will run fine for a bit but then after a week or so, the jobs start taking long to run again.  The only thing I could do it make it better again is to restart the SQL service.

We are running SQL Server 2012 SP2 Enterprise Edition on Windows 2012 R2.  We are using AAG on this particular server as well.

Anyone have any ideas what to look at to see why the performance tanks after a few days?  I had some performance counters captured but I didn't see anything jump out.  I don't see any memory errors either.

I'm not sure how this recommendation will be viewed, but based on the vague nature of your problem description and report of steps you've taken, I'd recommend going with something that attempts to answer some of the 'what do I look for / at?' for you like the stored procedures Sp_blitz and sp_askbrent over at brentozar.com.  You may need to start with some basic health checks before you dig into the causes of for your performance issues on particular jobs. While this doesn't specifically answer your question, it's somewhere you can start. Brent's work is awesome and you'll find actionable output  that you will point you to potential solutions. Otherwise start with using activity monitor to see what else may be hitting the same tables while your jobs are running. At the same time check your growth patterns and see if you've had some growth recently within the tables your hitting. You may be limited in what you can retrieve depending on whether or not you're warehousing DMVs (or restarting the services for that matter) so setting of the built-in warehouse to store growth (etc) might be something you need to look into. In general if you have a solution in place to retrieve and retain DMVs you'll be able to act when problems like this come up. Sorry if I'm all over the place with this response.


How to UPDATE/DELETE/INSERT with Microsoft.ACE.OLEDB.12.0 as Linked Server in SQL Server 2012? I need to update an Access 2003 database from SQL Server 2012 with the Microsoft.ACE.OLEDB.12.0 provider. As an administrator, everything works great. I can do Select, Insert, Update and Delete statements on the Access database without any problem but when i use a non-admin user, i can only do Select queries. The Insert, Update and Delete queries doesn't work and i get the following error message:

Error message when i use a SQL Server login:

  Cannot execute the query "SELECT * FROM inv_lign" against OLE DB
  provider "Microsoft.ACE.OLEDB.12.0" for linked server
  "AccessFileTest". The provider could not support an interface required
  for the UPDATE/DELETE/INSERT statements.The provider indicates that
  conflicts occurred with other properties or requirements.

Here's the query:

DELETE FROM
OPENQUERY(AccessFileTest,
    'SELECT * FROM inv_lign')

I have also tried the 4 parts query without success:

DELETE AccessFileTest...inv_lign;

Here's how the Microsoft.ACE.OLEDB.12.0 provider is configured:

Dynamic parameter = true
Allow inprocess = true
Added the key DisallowAdHocAccess = 0 in the registry

Here's how the Linked Server AccessFileTest is configured:

In the Security tab, i have selected "Be made using this security context"
The remote login is "admin" without any password

So how can i do Insert/Update/Delete statements on an Access 2003 database using the Microsoft.ACE.OLEDB.12.0 provider?

What i have tried so far:

I've been able to make it work ONLY if i use an AD user that is not sysadmin of SQL Server but who is Administrator of my server. If the user is not admin of the server, i get the error message Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".. I then tried to just add this user to have permission on the TEMP folder and on the Access file without success. So i don't know what specials rights i have to give to get this working with an AD User.
If i use a SQL Server login, i'm stock on the error message as stated before.

The problem came from an insufficient privileges on the server where the Access file is stored. Here's how you should configure your server depending of the login type that you are using:

If you are using SQL Server login:

Give to the SQL Server Database Engine service account permissions to read and write (depending if you want to make CRUD on that file) on the folder where the Access file reside.

If you are using Windows authentication login:

Give to this user login permissions to read and write :

on the folder where the Access file reside.
on the temp folder of the SQL Server Database Engine user account. For exemple, if the SQL Server Database Engine service account is domain\sqlEngSvc, the read/write permissions should be set to the C:\Users\sqlEngSvc\AppData\Local\Temp folder.


mongodb: maxTimeMS ignored After trying with different drivers and clients, we suffer with mongodb timeout on find query.

Even Profiling logs the settings and issue well.

       2014-08-13T23:46:43.218+0200 [conn80] query mdb.fcol query: { $query: {
       unid: "39068119d2924babffedde92ef937bd0" }, $maxTimeMS: 100 } planSummary:
       IXSCAN { unid: 1 } ntoskip:0 nscanned:1 nscannedObjects:1 keyUpdates:0
       numYields:1 locks(micros) r:334 nreturned:1 reslen:341 2181ms

Though $maxTimeMS: 100, the query takes 2181ms.

How to force mongodb to respect the timeout and stop processing if time has exceeded?

MongoDB v2.6.4, 64Bit

Some notes on MaxTimeMS behaviour as at MongoDB 2.6:

MaxTimeMS sets a cumulative time limit in milliseconds for processing operations on a cursor.
Network latency and idle time for a cursor do not count toward MaxTimeMS.
After the MaxTimeMS is reached, the operation will be killed at the next safe interrupt point (for example, when that operation yields). This means that an operation can exceed MaxTimeMS if it is not currently interruptible.

From your profile output the query held a read lock for 334 microseconds and yielded once, so it would appear to be well below the MaxTimeMS threshold. Effectively, this isn't an inherently "slow query" to execute but it is likely being affected by other activity on your system.

A properly indexed query returning a single result is unlikely to be slow; the MaxTimeMS option is much more useful for a poorly indexed query or a long running query that returns many results.

For a forced example of a slow executing query to test MaxTimeMS you could use a JavaScript function, eg:

// Add some test documents in the collection
db.mycoll.insert([{sleepy:1}, {tired:1}, {zzz:1}, {xyzzy:1}])

// A query that sleeps for 1s on each document found
db.mycoll.find({$where: "sleep(1000) || true"}).maxTimeMS(50)

I would suggest looking at the query performance in aggregate as there are should be other performance metrics that correlate. You should ideally track your MongoDB metrics over time using a monitoring system. For monitoring suggestions, see Monitoring for MongoDB in the manual.

Asya's presentation on Diagnostics & Debugging also provides a good overview of performance troubleshooting tools and how they were used to solve some specific performance scenarios.


Retrieve multiple rows from table2 based on single row from table1 in SQL Server I have two tables and I am using SQL Server.

table1 structure is

Campiagn_Id  Campiagn_Name
1            test1
2            test2
3            test3

and table2 structure is

Campign_Id  EmalList
1           email_list_1
1           email_list_2
3           email_list_3
1           email_list_4
2           email_list_5
2           email_list_6
3           email_list_7
1           email_list_8

Now I want to fetch all the Campiagn_Id and Campiagn_Name from table1 and also all the respective EmalList from table2 in one query.

My Result will look like this

Campign_Id   Campiagn_Name    EmailList
1            test1            email_list_1,email_list_2,email_list_4,email_list_8
2            test2            email_list_5,email_list_6
3            test3            email_list_3,email_list_7

Or any other suggested output will be fine

I think my question is pretty much clear to all of you.

The query you need is:

SELECT
    camp.Campiagn_Id,
    camp.Campiagn_Name,
    camp_list.Campign_Id,
    camp_list.EmalList
FROM
    [table1] AS camp
    LEFT JOIN [table2] AS camp_list ON (camp.Campiagn_Id = camp_list.Campiagn_Id);

This will show all campaigns with all emails lists for each campaign. It will show also campaign with no emails list.

You can add or remove fields in the select as you need.


How to choose a collation for international database? I'm designing a database which will store data in different languages (using UTF-8), so I think the best way to display the query's results is ordering it according to the user's language during the query itself (because there are more than one correct ways to do that), as follows:

SELECT a < b COLLATE "de_DE" FROM test1;

Assuming this is the correct way to work with international data, which is the best collation for the database itself? PostgreSQL documentation says:

  The C and POSIX collations both specify "traditional C" behavior, in which only the ASCII letters "A" through "Z" are treated as letters, and sorting is done strictly by character code byte values.

I think this is the best choice in this case, or am I wrong?

(Bonus question: is it too slow to select the collation in the query itself?).

The C collation is the right choice.

Everything is a bit faster without locale. And since no collation is right anyway, create the database without collation, meaning with C.

It may be a pain to have to provide a collation for many operations. There shouldn't be a noticeable difference in speed between the default collation and an ad-hoc collation, though. After all it's just unsorted data, and collation rules are applied when sorting.

Be aware that Postgres builds on the locale settings provided by the underlying OS, so you need to have locales generated for each locale to be used. More in related answer on SO here and here.

However, as @Craig already mentioned, indexes are the bottleneck in this scenario. The collation of the index has to match the collation of the applied operator in many cases that involve character data.

You can use the COLLATE specifier in indexes to produce matching indexes. Partial indexes may be the perfect choice if you are mixing data in the same table.

For example, a table with international strings:

CREATE TABLE string (
   string_id serial
  ,lang_id   int NOT NULL
  ,string    text NOT NULL
);

And you are mostly interested in one language at a time:

SELECT *
FROM   string
WHERE  lang_id = 5  -- 5 being German / Germany here
AND    string > 'foo' COLLATE "de_DE"
ORDER  BY string COLLATE "de_DE";

Then create partial indexes like:

CREATE INDEX string_string_lang_id_idx ON string (string COLLATE "de_DE")
WHERE lang_id = 5;

One for each language you need.

Actually, inheritance might be a superior approach for a table like this. Then you can have a plain index on each inherited table containing only strings for a single locale. You need to be comfortable with the special rules for inherited tables, of course.


SQL Server Login to access tables of three different database from single database I have created a login in SQL Server 2008 R2 and given access to run stored procedures in that database only. But this stored procedure is actually accessing data from other two databases to which this user doesn't have access and I cannot give access to those databases.

How can I create such a user with single database access but can run query on other two database through this stored procedure only?

So far what I have done:

Created login and mapped to Database1
Right click on the stored procedure in Database1 and given permission to this user to execute this stored procedure
This stored procedure is running a query to get data from two other databases (Database2 and Database3)

e.g.

Select value1, value2, value3
from Database1.[dbo].table1
inner join Database2.[dbo].table2 on ....
inner join Database3.[dbo].table3 on ....

Database ownership chaining will achieve what you are looking for as long as your SP and the tables in the destination DB are owned by users of the same name.

Just alter all participating databases so they are allowed to do ownership chaining:

ALTER DATABASE database1 SET DB_CHAINING ON
ALTER DATABASE database2 SET DB_CHAINING ON
ALTER DATABASE database3 SET DB_CHAINING ON

Additionally, you have to grant the logins executing the SP the right to access the destination database:

USE database2;
EXEC sp_grantdbaccess 'loginname'
USE database3;
EXEC sp_grantdbaccess 'loginname'

Please read about the security implications this has:
http://technet.microsoft.com/en-us/library/ms188676(v=sql.105).aspx

In short, don't use ownership chaining when you can't trust the db_owner of the source database.

For a good in-detail explanation including examples see http://www.mssqltips.com/sqlservertip/1782/understanding-cross-database-ownership-chaining-in-sql-server/


Can a member of the SQL server 'sysadmin' role login to any database? I've been researching and can't find a definitive answer. So I apologize if this question is more obvious than I suspect. All documentation I can find definitively says 'sysadmin' roles can do absolutely anything on the server including accessing all databases.
Yet, if I try to connect using an explicitly defined database in the connection string (Using windows authentication), the sysadmin user fails to login if they have not been mapped to that database as well.
What is the definitive answer here? Is there a resource you can provide that specifies this?

A sysadmin can use any database and also specify any database in the connection string. The special dbo user is used whenever a sysadmin does anything inside a database.
If you failed to connect to a specific database, then I'd say that the database doesn't exist (or is inaccessible) or you aren't a sysadmin. :-)
(Sorry I don't have something to point to the specifics about having a database in the connection string.)


ORA 01722: Invalid Number caused by different comparison The following causes the above error:

SELECT * FROM (
SELECT (nameMatch + add1Match + add2Match + phoneMatch + postalCodeMatch + provinceMatch + cityMatch + FSAMatch) as RATING, UNIQUE_ID, NAME, ADDRESS_1, ADDRESS_2, PHONE, FAX, POSTAL_CODE, PROVINCE_ID, CITY FROM
(SELECT case when GREATEST(UTL_MATCH.JARO_WINKLER('NAME', NAME), UTL_MATCH.JARO_WINKLER('NAME', ADDRESS_1), UTL_MATCH.JARO_WINKLER('NAME', ADDRESS_2)) > .85 then 7.97 else -1 end as nameMatch,
case when GREATEST(UTL_MATCH.JARO_WINKLER('ST123', NAME), UTL_MATCH.JARO_WINKLER('ST234', ADDRESS_1), UTL_MATCH.JARO_WINKLER('ST567', ADDRESS_2)) > .85 then 9.23 else -1.32 end as add1Match,
case when GREATEST(UTL_MATCH.JARO_WINKLER('SUITE 1', NAME), UTL_MATCH.JARO_WINKLER('SUITE 1', ADDRESS_1), UTL_MATCH.JARO_WINKLER('SUITE 1', ADDRESS_2)) > .85 then 8.46 else -.74 end as add2Match,
case when (6045691111 = PHONE OR 6045691111 = FAX OR 6045691111 = PHONE OR 6045691111 = FAX) then 18 else -1 end as phoneMatch,
case when 'ABC123' = POSTAL_CODE then 7.13 else -1.73 end as postalCodeMatch,
case when 2 = PROVINCE_ID THEN 3.45 ELSE -6.51 END as provinceMatch,
CASE WHEN UTL_MATCH.JARO_WINKLER('VANCOUVER', CITY) > .8 THEN 6.57 ELSE -4.31 END as cityMatch,
CASE WHEN 'V6C' = SUBSTR(POSTAL_CODE, 1, 3) THEN 3 ELSE  -2.16 END as FSAMatch,
UNIQUE_ID, NAME, ADDRESS_1, ADDRESS_2, PHONE, FAX, POSTAL_CODE, PROVINCE_ID, CITY
FROM LOCATION))
WHERE RATING > 1;

Here's the weird thing, the following query DOES work, the only difference being whether rating needs to be larger than or smaller than 1.

Suffice it to say that I'm utterly perplexed. Is this a bug? I'm on 11.2.0.4.0.

Could it be something to do with JARO_WINKLER?

SELECT * FROM (
SELECT (nameMatch + add1Match + add2Match + phoneMatch + postalCodeMatch + provinceMatch + cityMatch + FSAMatch) as RATING, UNIQUE_ID, NAME, ADDRESS_1, ADDRESS_2, PHONE, FAX, POSTAL_CODE, PROVINCE_ID, CITY FROM
(SELECT case when GREATEST(UTL_MATCH.JARO_WINKLER('NAME', NAME), UTL_MATCH.JARO_WINKLER('NAME', ADDRESS_1), UTL_MATCH.JARO_WINKLER('NAME', ADDRESS_2)) > .85 then 7.97 else -1 end as nameMatch,
case when GREATEST(UTL_MATCH.JARO_WINKLER('ST123', NAME), UTL_MATCH.JARO_WINKLER('ST234', ADDRESS_1), UTL_MATCH.JARO_WINKLER('ST567', ADDRESS_2)) > .85 then 9.23 else -1.32 end as add1Match,
case when GREATEST(UTL_MATCH.JARO_WINKLER('SUITE 1', NAME), UTL_MATCH.JARO_WINKLER('SUITE 1', ADDRESS_1), UTL_MATCH.JARO_WINKLER('SUITE 1', ADDRESS_2)) > .85 then 8.46 else -.74 end as add2Match,
case when (6045691111 = PHONE OR 6045691111 = FAX OR 6045691111 = PHONE OR 6045691111 = FAX) then 18 else -1 end as phoneMatch,
case when 'ABC123' = POSTAL_CODE then 7.13 else -1.73 end as postalCodeMatch,
case when 2 = PROVINCE_ID THEN 3.45 ELSE -6.51 END as provinceMatch,
CASE WHEN UTL_MATCH.JARO_WINKLER('VANCOUVER', CITY) > .8 THEN 6.57 ELSE -4.31 END as cityMatch,
CASE WHEN 'V6C' = SUBSTR(POSTAL_CODE, 1, 3) THEN 3 ELSE  -2.16 END as FSAMatch,
UNIQUE_ID, NAME, ADDRESS_1, ADDRESS_2, PHONE, FAX, POSTAL_CODE, PROVINCE_ID, CITY
FROM LOCATION))
WHERE RATING < 1;

I partially solved it: PHONE is actually a VARCHAR2 column. However, this doesn't at all explain why comparing in one direction versus another works or doesn't work, when I make that mistake. So I'm still very curious.

Expressions in SELECT evaluated after filter[s] in WHERE applied. So if phones for records with RATING <1 can be converted to number, you don't have errors. Simplified example :

SELECT
CASE WHEN n1=1 THEN 'a' ELSE 'b' END AS test_str
FROM
(
SELECT '1a' AS n1 , 0 AS n2 FROM dual
UNION ALL
SELECT '1' AS n1 , 1 AS n2 FROM dual
)
x
WHERE n2 =0 -- gives an error
-- WHERE n2 =1 -  runs fine


How can I make only *one* user controlled database How can I system admin let it be me, but another users and admins must be cannot access my database.
My database located on customer server. I deleted all sql users, there's only sa user. This persons don't know my sa password. But this persons created sa similar users, and can access my database. I want block this users to my db.

You can only really hope to log this and raise alerts.  Users who can administer the hardware can always stop the service and bring a database up in single user mode etc.  They can give themselves elevated permissions etc.

It's a common requirement to segregate administrative duties so that server admins cannot see confidential files or confidential data.

However, because every administrative role can typically elevate their rights or create other users, requiring users to use least privilege accounts for their daily work and auditing what is done with elevated accounts is really the only way to deal with it.


Measuring latency between availability group nodes in real-time We have a monitor in which we would like to display the replication latency between primary and secondary node in milliseconds in real-time.

We're using this query:

;WITH
    AG_Stats AS
            (
            SELECT AR.replica_server_name,
                   HARS.role_desc,
                   Db_name(DRS.database_id) [DBName],
                   DRS.last_commit_time
            FROM   sys.dm_hadr_database_replica_states DRS
            INNER JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id
            INNER JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id
                AND AR.replica_id = HARS.replica_id
            ),
    Pri_CommitTime AS
            (
            SELECT  replica_server_name
                    , DBName
                    , last_commit_time
            FROM    AG_Stats
            WHERE   role_desc = 'PRIMARY'
            ),
    Sec_CommitTime AS
            (
            SELECT  replica_server_name
                    , DBName
                    , last_commit_time
            FROM    AG_Stats
            WHERE   role_desc = 'SECONDARY'
            )
SELECT p.replica_server_name [primary_replica]
    , p.[DBName] AS [DatabaseName]
    , s.replica_server_name [secondary_replica]
    , s.last_commit_time
    , p.last_commit_time
    , DATEDIFF(ms,s.last_commit_time,p.last_commit_time) AS [Sync_Lag_MS]
FROM Pri_CommitTime p
LEFT JOIN Sec_CommitTime s ON [s].[DBName] = [p].[DBName]

Result:

primary_replica DatabaseName secondary_replica last_commit_time        last_commit_time        Sync_Lag_MS
--------------- ------------ ----------------- ----------------------- ----------------------- -----------
XXXXX       NextGenAG    ANGSQLD01         2018-06-08 13:18:08.853 2018-06-08 13:18:08.853           0

However the last_commit_time columns are not updated in real-time. They are updated every 5-10 minutes, which is not the case based on the OLTP workload which performs write operations every few milliseconds.

You can technically do it using XEvents but you have to build your own tool to stream the data in near to realtime to your dashboard .. this involves data extraction and streaming.

Workflow will be as below :

Collect AG info:

 USE tempdb
                  IF OBJECT_ID('AGInfo') IS NOT NULL
                      BEGIN
                        DROP TABLE AGInfo
                   END
                  IF OBJECT_ID('LatencyCollectionStatus') IS NOT NULL
                      BEGIN
                        DROP TABLE LatencyCollectionStatus
                      END
                   CREATE TABLE LatencyCollectionStatus(
                        [collection_status] [NVARCHAR](60)  NULL,
                        [start_timestamp] [DATETIMEOFFSET] NULL,
                        [startutc_timestamp] [DATETIMEOFFSET] NULL
                    )
                  INSERT INTO LatencyCollectionStatus(collection_status, start_timestamp, startutc_timestamp) values ('Started', GETDATE(), GETUTCDATE())
                  SELECT
                  AGC.name as agname
                  , RCS.replica_server_name as replica_name
                  , ARS.role_desc as agrole
                  INTO AGInfo
                  FROM
                      sys.availability_groups_cluster AS AGC
                      INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
                      ON
                      RCS.group_id = AGC.group_id
                      INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
                      ON
                      ARS.replica_id = RCS.replica_id
                      where AGC.name =  N'YOUR AG NAME '-- change here !!

Create XE Session as below :

IF EXISTS (select * from sys.server_event_sessions
                WHERE name = N'AlwaysOn_Data_Movement_Tracing')
                    BEGIN
                    DROP EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER
                    END
                CREATE EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER ADD EVENT sqlserver.hadr_apply_log_block,
ADD EVENT sqlserver.hadr_capture_log_block,
ADD EVENT sqlserver.hadr_database_flow_control_action,
ADD EVENT sqlserver.hadr_db_commit_mgr_harden,
ADD EVENT sqlserver.hadr_log_block_send_complete,
ADD EVENT sqlserver.hadr_send_harden_lsn_message,
ADD EVENT sqlserver.hadr_transport_flow_control_action,
ADD EVENT sqlserver.log_flush_complete,
ADD EVENT sqlserver.log_flush_start,
ADD EVENT sqlserver.recovery_unit_harden_log_timestamps,
ADD EVENT sqlserver.log_block_pushed_to_logpool,
ADD EVENT sqlserver.hadr_transport_receive_log_block_message,
ADD EVENT sqlserver.hadr_receive_harden_lsn_message,
ADD EVENT sqlserver.hadr_log_block_group_commit,
ADD EVENT sqlserver.hadr_log_block_compression,
ADD EVENT sqlserver.hadr_log_block_decompression,
ADD EVENT sqlserver.hadr_lsn_send_complete,
ADD EVENT sqlserver.hadr_capture_filestream_wait,
ADD EVENT sqlserver.hadr_capture_vlfheader ADD TARGET package0.event_file(SET filename=N'AlwaysOn_Data_Movement_Tracing.xel',max_file_size=(25),max_rollover_files=(4))
                WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

                ALTER EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER STATE = START

Extract XEvent Info:

                BEGIN TRANSACTION
                USE tempdb
                IF OBJECT_ID('#EventXml') IS NOT NULL
                BEGIN
                    DROP TABLE #EventXml
                END

                SELECT
                    xe.event_name,
                    CAST(xe.event_data AS XML) AS event_data
                INTO #EventXml
                FROM
                (
                SELECT
                        object_name AS event_name,
                        CAST(event_data AS XML) AS event_data
                    FROM sys.fn_xe_file_target_read_file(
                                'AlwaysOn_Data_Movement_Tracing*.xel',
                                NULL, NULL, NULL)
                    WHERE object_name IN ('hadr_log_block_group_commit',
                                'log_block_pushed_to_logpool',
                                'log_flush_start',
                                'log_flush_complete',
                                'hadr_log_block_compression',
                                'hadr_capture_log_block',
                                'hadr_capture_filestream_wait',
                                'hadr_log_block_send_complete',
                                'hadr_receive_harden_lsn_message',
                                'hadr_db_commit_mgr_harden',
                                'recovery_unit_harden_log_timestamps',
                                'hadr_capture_vlfheader',
                                'hadr_log_block_decompression',
                                'hadr_apply_log_block',
                                'hadr_send_harden_lsn_message',
                                'hadr_log_block_decompression',
                                'hadr_lsn_send_complete',
                                'hadr_transport_receive_log_block_message')

                ) xe

                IF OBJECT_ID('DMReplicaEvents') IS NOT NULL
                BEGIN
                    DROP TABLE DMReplicaEvents
                END

                SET ANSI_NULLS ON

                SET QUOTED_IDENTIFIER ON

                CREATE TABLE DMReplicaEvents(
                    [server_name] [NVARCHAR](128) NULL,
                    [event_name] [NVARCHAR](60) NOT NULL,
                    [log_block_id] [BIGINT] NULL,
                    [database_id] [INT] NULL,
                    [processing_time] [BIGINT] NULL,
                    [start_timestamp] [BIGINT] NULL,
                    [publish_timestamp] [DATETIMEOFFSET] NULL,
                    [log_block_size] [BIGINT] NULL,
                    [target_availability_replica_id] [UNIQUEIDENTIFIER] NULL,
                    [local_availability_replica_id] [UNIQUEIDENTIFIER] NULL,
                    [database_replica_id] [UNIQUEIDENTIFIER] NULL,
                    [mode] [BIGINT] NULL,
                    [availability_group_id] [UNIQUEIDENTIFIER] NULL,
                    [pending_writes]  [BIGINT] NULL
                )

                IF OBJECT_ID('LatencyResults') IS NOT NULL
                BEGIN
                    DROP TABLE LatencyResults
                END
                CREATE TABLE LatencyResults(
                   [event_name] [NVARCHAR](60) NOT NULL,
                   [processing_time] [BIGINT] NULL,
                   [publish_timestamp] [DATETIMEOFFSET] NULL,
                   [server_commit_mode] [NVARCHAR](60) NULL
                )

                INSERT INTO DMReplicaEvents
                SELECT
                    @@SERVERNAME AS server_name,
                    xe.event_name,
                    AoData.value('(data[@name="log_block_id"]/value)[1]', 'BIGINT') AS log_block_id,
                    NULL AS database_id,
                    AoData.value('(data[@name="total_processing_time"]/value)[1]', 'BIGINT') AS processing_time,
                    AoData.value('(data[@name="start_timestamp"]/value)[1]', 'BIGINT') AS start_timestamp,
                    CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 75, 24) AS DATETIMEOFFSET) AS publish_timestamp,
                    AoData.value('(data[@name="log_block_size"]/value)[1]', 'BIGINT') AS log_block_size,
                    NULL AS target_availability_replica_id,
                    NULL AS local_availability_replica_id,
                    NULL AS database_replica_id,
                    NULL AS mode,
                    NULL AS availability_group_id,
                    NULL AS pending_writes
                FROM #EventXml AS xe
                CROSS APPLY xe.event_data.nodes('/event')  AS T(AoData)
                WHERE xe.event_name = 'hadr_log_block_send_complete'

                GO

                INSERT INTO DMReplicaEvents
                SELECT
                    @@SERVERNAME AS server_name,
                    xe.event_name,
                    AoData.value('(data[@name="log_block_id"]/value)[1]', 'BIGINT') AS log_block_id,
                    AoData.value('(data[@name="database_id"]/value)[1]', 'INT') AS database_id,
                    AoData.value('(data[@name="duration"]/value)[1]', 'BIGINT') AS processing_time,
                    AoData.value('(data[@name="start_timestamp"]/value)[1]', 'BIGINT') AS start_timestamp,
                    CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 65, 24) AS DATETIMEOFFSET) AS publish_timestamp,
                    NULL AS log_block_size,
                    NULL AS target_availability_replica_id,
                    NULL AS local_availability_replica_id,
                    NULL AS database_replica_id,
                    NULL AS mode,
                    NULL AS availability_group_id,
                    AoData.value('(data[@name="pending_writes"]/value)[1]','BIGINT') AS pending_writes
                FROM #EventXml AS xe
                CROSS APPLY xe.event_data.nodes('/event')  AS T(AoData)
                WHERE xe.event_name = 'log_flush_complete'

                GO

                INSERT INTO DMReplicaEvents
                SELECT
                    @@SERVERNAME AS server_name,
                    xe.event_name,
                    NULL AS log_block_id,
                    AoData.value('(data[@name="database_id"]/value)[1]', 'BIGINT') AS database_id,
                    AoData.value('(data[@name="time_to_commit"]/value)[1]', 'BIGINT') AS processing_time,
                    NULL AS start_timestamp,
                    CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 72, 24) AS DATETIMEOFFSET) AS publish_timestamp,
                    NULL AS log_block_size,
                    AoData.value('(data[@name="replica_id"]/value)[1]', 'UNIQUEIDENTIFIER') AS target_availability_replica_id,
                    NULL AS local_availability_replica_id,
                    AoData.value('(data[@name="ag_database_id"]/value)[1]', 'UNIQUEIDENTIFIER') AS database_replica_id,
                    NULL AS mode,
                    AoData.value('(data[@name="group_id"]/value)[1]','UNIQUEIDENTIFIER') AS availability_group_id,
                    NULL AS pending_writes
                FROM #EventXml AS xe
                CROSS APPLY xe.event_data.nodes('/event')  AS T(AoData)
                WHERE xe.event_name = 'hadr_db_commit_mgr_harden'

                GO

                INSERT INTO DMReplicaEvents
                SELECT
                    @@SERVERNAME AS server_name,
                    xe.event_name,
                    AoData.value('(data[@name="log_block_id"]/value)[1]', 'BIGINT') AS log_block_id,
                    AoData.value('(data[@name="database_id"]/value)[1]', 'BIGINT') AS database_id,
                    AoData.value('(data[@name="processing_time"]/value)[1]', 'BIGINT') AS processing_time,
                    AoData.value('(data[@name="start_timestamp"]/value)[1]', 'BIGINT') AS start_timestamp,
                    CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 82, 24) AS DATETIMEOFFSET) AS publish_timestamp,
                    NULL AS log_block_size,
                    NULL AS target_availability_replica_id,
                    NULL AS local_availability_replica_id,
                    NULL AS database_replica_id,
                    NULL AS mode,
                    NULL AS availability_group_id,
                    NULL AS pending_writes
                FROM #EventXml AS xe
                CROSS APPLY xe.event_data.nodes('/event')  AS T(AoData)
                WHERE xe.event_name = 'recovery_unit_harden_log_timestamps'

                GO

                INSERT INTO DMReplicaEvents
                SELECT
                    @@SERVERNAME AS server_name,
                    xe.event_name,
                    AoData.value('(data[@name="log_block_id"]/value)[1]', 'BIGINT') AS log_block_id,
                    AoData.value('(data[@name="database_id"]/value)[1]', 'BIGINT') AS database_id,
                    AoData.value('(data[@name="processing_time"]/value)[1]', 'BIGINT') AS processing_time,
                    AoData.value('(data[@name="start_timestamp"]/value)[1]', 'BIGINT') AS start_timestamp,
                    CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 73, 24) AS DATETIMEOFFSET) AS publish_timestamp,
                    AoData.value('(data[@name="uncompressed_size"]/value)[1]', 'INT') AS log_block_size,
                    AoData.value('(data[@name="availability_replica_id"]/value)[1]', 'UNIQUEIDENTIFIER') AS target_availability_replica_id,
                    NULL AS local_availability_replica_id,
                    NULL AS database_replica_id,
                    NULL AS mode,
                    NULL AS availability_group_id,
                    NULL AS pending_writes
                FROM #EventXml AS xe
                CROSS APPLY xe.event_data.nodes('/event')  AS T(AoData)
                WHERE xe.event_name = 'hadr_log_block_compression'

                GO

                INSERT INTO DMReplicaEvents
                SELECT
                    @@SERVERNAME AS server_name,
                    xe.event_name,
                    AoData.value('(data[@name="log_block_id"]/value)[1]', 'BIGINT') AS log_block_id,
                    AoData.value('(data[@name="database_id"]/value)[1]', 'BIGINT') AS database_id,
                    AoData.value('(data[@name="processing_time"]/value)[1]', 'BIGINT') AS processing_time,
                    AoData.value('(data[@name="start_timestamp"]/value)[1]', 'BIGINT') AS start_timestamp,
                    CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 75, 24) AS DATETIMEOFFSET) AS publish_timestamp,
                    AoData.value('(data[@name="uncompressed_size"]/value)[1]', 'BIGINT') AS log_block_size,
                    AoData.value('(data[@name="availability_replica_id"]/value)[1]', 'UNIQUEIDENTIFIER') AS target_availability_replica_id,
                    NULL AS local_availability_replica_id,
                    NULL AS database_replica_id,
                    NULL AS mode,
                    NULL AS availability_group_id,
                    NULL AS pending_writes
                FROM #EventXml AS xe
                CROSS APPLY xe.event_data.nodes('/event')  AS T(AoData)
                WHERE xe.event_name = 'hadr_log_block_decompression'

                INSERT INTO DMReplicaEvents
                SELECT
                    @@SERVERNAME AS server_name,
                    xe.event_name,
                    AoData.value('(data[@name="log_block_id"]/value)[1]', 'BIGINT') AS log_block_id,
                    NULL AS database_id,
                    AoData.value('(data[@name="total_sending_time"]/value)[1]', 'BIGINT') AS processing_time,
                    AoData.value('(data[@name="start_timestamp"]/value)[1]', 'BIGINT') AS start_timestamp,
                    CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 69, 24) AS DATETIMEOFFSET) AS publish_timestamp,
                    NULL AS log_block_size,
                    NULL AS target_availability_replica_id,
                    NULL AS local_availability_replica_id,
                    NULL AS database_replica_id,
                    NULL AS mode,
                    NULL AS availability_group_id,
                    NULL AS pending_writes
                FROM #EventXml AS xe
                CROSS APPLY xe.event_data.nodes('/event')  AS T(AoData)
                WHERE xe.event_name = 'hadr_lsn_send_complete'

                INSERT INTO DMReplicaEvents
                SELECT
                    @@SERVERNAME AS server_name,
                    xe.event_name,
                    AoData.value('(data[@name="log_block_id"]/value)[1]', 'BIGINT') AS log_block_id,
                    NULL AS database_id,
                    AoData.value('(data[@name="processing_time"]/value)[1]', 'BIGINT') AS processing_time,
                    AoData.value('(data[@name="start_timestamp"]/value)[1]', 'BIGINT') AS start_timestamp,
                    CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 87, 24) AS DATETIMEOFFSET) AS publish_timestamp,
                    NULL AS log_block_size,
                    AoData.value('(data[@name="target_availability_replica_id"]/value)[1]', 'UNIQUEIDENTIFIER') AS target_availability_replica_id,
                    AoData.value('(data[@name="local_availability_replica_id"]/value)[1]', 'UNIQUEIDENTIFIER') AS local_availability_replica_id,
                    AoData.value('(data[@name="target_availability_replica_id"]/value)[1]', 'UNIQUEIDENTIFIER') AS database_replica_id,
                    AoData.value('(data[@name="mode"]/value)[1]', 'BIGINT') AS mode,
                    AoData.value('(data[@name="availability_group_id"]/value)[1]','UNIQUEIDENTIFIER') AS availability_group_id,
                    NULL AS pending_writes
                FROM #EventXml AS xe
                CROSS APPLY xe.event_data.nodes('/event')  AS T(AoData)
                WHERE xe.event_name = 'hadr_transport_receive_log_block_message'

                DELETE
                FROM DMReplicaEvents
                WHERE CAST(publish_timestamp AS DATETIME) < DATEADD(minute, -2, CAST((SELECT MAX(publish_timestamp) from DMReplicaEvents) as DATETIME))
                COMMIT
                GO


Force NOW to return new timestamps without committing the transaction I'm aware that, in PostgreSQL, the return value of NOW() is the transaction begin timestamp. So if you use NOW() multiple times in the same transaction it will always return the same value.
And that's good in my book.
But I have a small issue with unit-tests in a client application with this and would love to be able to tell PostgreSQL to (temporarily) disable this if at all possible.
The reason I don't want to (can't) use another timestamp function like clock_timestamp() is because the function call to NOW() sits inside of a trigger, and in production code I want the "transaction-start" behaviour.
But in my unit-tests I'm patching the API-level "commit" function so I don't accidentally commit real data to the database during testing (don't worry, I don't use the production DB during testing). So during unit-tests the commit never hits the DB, so I don't get new transaction timestamps.
The database uses temporal tables, and new entries are only appended to the history tables if the timestamp changes to ensure we only consolidate one entry in the history table per transaction.
But when testing the temporal-table behaviour this now causes no entry to ever show up in the history tables. The key fragment of the temporal-table trigger is this:
new_validity_period = tstzrange(
    lower(OLD.validity_period),
    NOW(),
    '[)'
);
IF isempty(new_validity_period) THEN
    RAISE DEBUG 'New entry % will not introduce a new history item', OLD;
    RETURN OLD;
END IF;

So when I do an "insert" operation in my unit-test, and the transaction-time is '2020-01-01 01:02:03', then the validity-period for that entry will be [2020-01-01 01:02:02,). If, still in the same unit-test, I delete the entry (and to test whether it appears in the history table), the operation happens in the same TX, and the code above will read like this:
new_validity_perion = tstzrange(
    '2020-01-01 01:02:03', -- the lower-bound of the 'OLD' row
    '2020-01-01 01:02:03', -- the result of 'NOW()'
    '[)'
)
-- resulting in an empty range because the two timestamps are identical
IF isempty(new_validity_periond) THEN  -- <- Resulting to TRUE
    ...
    RETURN OLD;  -- returning here, not continuing to store the history entry
END IF;
-- code below here is skipped

Is there a way to configure PG to always return the wall-time when calling NOW()? The tests are run against a docker-container so I have full control over the server-config. But it would be even better if I could change the behaviour by setting the config via an SQL command, so I could only change the behaviour for the temporal-table tests.
If that is not possible I would need to use a different database-session/transaction setup for those tests. This is also fine, but I was wondering if I could control this PG behaviour.
Appendix: Unit-Test transaction isolation
This is the code I use to isolate the commit calls in user-code:
@fixture
def rb_session():
    """
    Returns a session which will always be rolled back.
    """

    engine = create_engine(Configurations.getenv("IPBASE_DATABASE_DSN"))

    # Get a *specific* connection from the pool
    connection = engine.connect()
    # Explicitly start a new connection on the connection we got
    # This makes the normal "session.begin()" and "session.commit()"
    # calls in SQLAlchemy no-ops as there is already a transaction
    # in progress.
    transaction = connection.begin()
    # Ensure we use the "primed" connection for all our SQLAlchemy
    # session needs in our unit-tests.
    session = Session(bind=connection)
    try:
        yield session
    finally:
        transaction.rollback()
        session.close()
        connection.close()

I think that that is questionable practice. Any way in which your tests differ from the productive environment increases the danger of testing the wrong thing.
Anyway, you could not use now(), but a different function, say mytimestamp(). On your test system, the function is defined as
CREATE FUNCTION mytimestamp() RETURNS timestamp with time zone
   LANGUAGE sql VOLATILE AS
'SELECT clock_timestamp()';

In the production database, you use
CREATE FUNCTION mytimestamp() RETURNS timestamp with time zone
   LANGUAGE sql VOLATILE AS
'SELECT current_timestamp';

Then you get what you want.  The only difference is that current_timestamp (or now(), which is the same thing) is STABLE, not VOLATILE, which may make queries behave differently (or not, if the function is inlined). But that's exactly the kind of thing that I warned you about in the beginning.


Request suggestion for a database between MySQL and Postgresql We maintain a list of all the SAN Switch Ports that are either free or allocated to hosts. Currently, we enter the information on the company portal. But we need to move if off to a database because as of now we have about 2200 rows of data in it and each row has 10 columns. I got the number after extracting the data into an excel sheet. I am expecting that atleast 1000 more rows will be added in a few months.

All of our SAN Mgmt servers are windows 2003, but none of them have MS Office installed, so cannot use MS Excel. Hence the need for an open source database.

I have tried MySQL and PostgreSQL. I find MySQL easier to deal with, but I have read that data reliablity wise PostgreSQL is better. I tried both databases for a few days, imported CSV files to both as that was easier compared to manually entering 2200 entries in it. Both MySQL and PostgreSQL seem to be doing good. Though, I had to edit the CSV file because the field seperator "," was used in some places in of the cells and that made it break and skew the entries in MySQL. PostgreSQL didnt have any trouble with that.

A request to experienced Database Admins - kindly let me know that for my need will MySQL suffice? Is there a chance of Data Corruption with MySQL? Or would it be better to move to PostgreSQL right now itself?

My apologies for not using database specific terminologies here because I do not hail from a DB Background.

Please let me know.

PostgreSQL has a lot more modern SQL features compared to MySQL.

Some of those featurese you might not find necessary right now but you'll deeply miss them once your application grows and your needs get more complex. Here are just some:

No check constraints
No deferrable constraints
No windowing functions
No common table expressions
No recursive queries (think hierarchical data)
Non transactional FK evaluation
No table functions
No function based index
No partial index
No full text search on transactional tables
No GIS features on transactional tables
Incomplete trigger implementation

Performance wise both will be pretty much the same (especially with such a tiny dataset). I wouldn't expect to see any difference between the two with your data.

If the database size grows, PostgreSQL seems to cope better with a lot of concurrent read and write access, while MySQL has some read optimizations that are not (yet) available in PostgreSQL (like index-only scans) which tends to make MySQL perform better in heavy read-only situations. But then every workload is different and it's basically impossible to generally say "one is faster than the other".

If you go for MySQL make sure you run it in ANSI and strict mode right from the beginning to get rid of some of gotchas that the default installation keeps for surprises (e.g. silently truncating data or accepting February 31st as a valid date).

(You might also want to look at the comment of ypercube here: https://stackoverflow.com/questions/9963012/what-does-the-sql-standard-say-about-dependent-conditionals-in-update/9963473#9963473 to get a feeling on what I mean with "gotchas")

This blog post also lists some of the really nice things about PostgreSQL (compared to SQL Server):

http://facility9.com/2011/12/ten-reasons-postgresql-is-better-than-sql-server/


Should a SQL Server TempDB space be allowed to be reclaimed? If I don't have space concerns at the moment, should I size my TempDB as a large space initially and not let it's space be reclaimed?

What's the best way determine what the optimal initial size for my TempDB should be?

My TempDB is currently 12gb and it's set to 10% autogrowth

TempDB (as with any other files) should be sized to a stable amount appropriate to your instance so that it shouldn't have to grow.  The idea is to minimize autogrowth events, because any time your files have to grow, your queries will be forced to wait until the file growth has been completed.  My practice(and this is my practice, your miles may vary) for sizing TempDB is as follows:

Total TempDB data file size should be approximately 1/3rd my total data file size for all databases
Number of data files is half the number of logical cores up to 8 total data files.
Log file is sized to 1/3rd total size of TempDB data files
I place TempDB on a LUN where it will have some space to grow, so that TempDB will have some ability to grow if a process requires more TempDB than I allocated.
I set explicit values for TempDB file growth, typically 1GB (depends on file size).  I do not use percentages for growth on any files, as percentage growth is harder to predict and accommodate.

As for giving space back to the OS, I typically don't do this.  The only time I will give space back is if there was an obvious run away process that blew TempDB out to an abnormal size.  To give the space back, I restart SQL in order to recreate TempDB fresh (as TempDB is created fresh on every SQL Service start) since shrinking TempDB can cause corruption.

There is a lot of discussion and opinion on exactly how TempDB should be configured.  Again, this is my approach.  For more on TempDB best practices and approaches, I recommend this whitepaper from Robert Davis(it's a vendor site, so you'll have to register).


Is it possible to join derived tables to actual in Access 2007 Is it possible to join derived tables to actual tables in Access 2007 like in SQL Server 2008? I have an example below:

  SELECT *
    FROM
    --MyOtherTable is a "real" table
    MyOtherTable INNER JOIN
    --MyTable is a "real" table. MyDerivedTable is a "pseudo" table derived from MyTable
    (SELECT * FROM MyTable WHERE MyID >10) AS MyDerivedTable ON
    MyOtherTable.ID = MyDerivedTable.ID

is this possible in Access? The reason I am asking is because I would like to migrate some of my SQL Server 2008 queries into Access but most of my queries contain a lot of derived tables and I haven't been able to convert my queries if they require the use of a derived table.

I found this link which seems to suggest that it is not possible....

http://www.access-programmers.co.uk/forums/showthread.php?t=78833

Can someone please confirm? Thanks in advance.

i have 2 tables in my access DB, customer and contracts and they are related through the customerid,

this is the script i use normally

SELECT Customer.CustomerName
FROM Customer INNER JOIN Contracts ON Customer.CustID = Contracts.CustomerID;

and it works fine and this is how its represented in the design view

i changed my query to

SELECT Customer.CustomerName
FROM Customer INNER JOIN (select * from Contracts) derivedtable ON Customer.CustID = derivedtable.CustomerID;

similar to yours and it gave me same results and this is how its represented in the design view

i hope it clarifies


MSAccess: creating a new row in a related table and linking it back to the "master" table In a hypothetical 'cars' database, assume there's a model table and a make table:

models
  modelID int
  model varchar
  make int

makes
  makeID int
  make varchar

Model's make column has a foreign key relation to the make table's primary key. You can use a query against make to populate the 'make' combo box on the model form. If you enter a model for which there isn't a corresponding make row yet, you'd want it to insert the new make (since the input doesn't match any existing makes.make value), then insert the model row with the new make row's primary key in the models.make column. Pretty basic stuff.

I can't seem to figure out how to do it easily in Access 2016, though. If I use the form wizard to create the model management form and feed it either the makes table or a query as a source for the makes combo box element, I can get the list of makes to populate the fill list, but it chokes if I try to enter a make that isn't in the list. It won't let me disable the 'limit to list items' property of that interface element unless I include the makes.makeid column not just in the form's data sources but as another visible item in the box itself. This doesn't fix the insert problem and also breaks the new entry behavior when the make does exist.

Is there an obvious way to do this out of the box in Access 2016? The only solutions I see are

write a macro to insert the new make if it doesn't exist, catch the new primary key, and feed it back into the model insert
change models.make to varchar and link it directly to makes.make
turn the combo box into a restricted list box, use a different form to populate makes

None of these are particularly elegant. I've watched some tutorials and looked at their sample databases and haven't seen this scenario addressed in any useful way. Is there a simple way to use another table as a data source and create new rows in it on-the-fly and I'm just overlooking it, or is this really not something that Access does well natively?

Your question is legitimate and there are some reasonable patterns that you can follow to get it working.  However, I also feel like your question demands some comments regarding Access forms and controls. so sorry if it's a bit chatty.

It seems completely reasonable to be able to add a new make when entering a new model for a make not yet in the database.  That can be done, so my next comments are not to discourage or dissuade.  However, the built-in controls and data binding in Access do not facilitate this behavior and there are good reasons for it.  Custom macro and/or VBA event programming can overcome these limitations.

This also requires an understanding of what a ComboBox is really doing and not expecting it to do more.  The value list of a ComboBox is largely read-only.  The primary reason for this is that it accepts almost any complex query--including non-updatable queries--as the RowSource for the list values.  Just because the [Makes] table only has one Text column (beside the ID column) does not mean that the ComboBox has any built-in way to automatically insert a new value into that table.  I'm not familiar with any ComboBox even in other environments that automatically facilitate such behavior without custom event programming.  This is not just a question of elegance, rather the impractical ability of updating any arbitrary source.  From my experience, most of my ComboBoxes have lists from complex data sources that would not accept a single value for adding to the list.

Elegance is a very subjective thing.  Honestly, one could argue that normalized relational database tables are completely inelegant compared to a well-designed object model with modern object databases, but that's a longstanding debate and Access is certainly not state-of-the-art.  Access often demands that you deal with separate inserts, retrieving ID values, explicitly refreshing form and control query sources, etc.  Pessimistically, Access is not elegant.  But it can be very convenient and has its plus sides when you don't/can't worry about installing and/or maintaining other RDMS systems and marrying it to a separate GUI environment.

As for the ComboBox properties, it is possible to hide extra column values using the ColumnWidth property, like ComboBox.ColumnWidth = "0;1.5".  This allows you to bind to the correct numeric value while displaying the corresponding text values ([makes].[make] in this case). How would you even properly bind to the [model].[make] field without the numeric ID?

All that said, your own #1 option is probably the best approach:

  Write a macro to insert the new make if it doesn't exist, catch the new primary key, and feed it back into the model insert

I personally prefer VBA instead of a macros.  Consider handling the "Not In List" event of the ComboBox.

An alternative is to create a separate form for managing the [makes] separately.  Provide a button or link (or some other various way) to open the form for adding new [makes].  In your case this probably seems overkill since you only have one data field, but if you start adding more fields to the [makes] table (e.g. website URL for example), you will definitely need something beyond an automatic ComboBox.


If count >= row then insert row (substitute for variables and else/if) I do 99% php/javascript work so getting my head around SQL is always a struggle, sorry if I seem dense.

So I've got a table that looks something like this:

post_id   |  meta_key    | meta_value
--------------------------------------
1         | requirement  | 3
2         | requirement  | 1
3         | requirement  | 1
1         | adder_id     | 3
1         | adder_id     | 2
1         | adder_id     | 69
2         | adder_id     | 42
2         | complete_date| 20190309040445

On an insert trigger I want to run a query that will for each post_id that dose not have a "complete date":

INSERT INTO this_table ( post_id, meta_key, meta_value )
VALUES
( var_post_id, 'complete_date',NOW());

But only if

SELECT COUNT(*)
FROM this_table
WHERE post_id = var_post_id
AND meta_key = 'adder_id'

is >=

SELECT meta_value
FROM this_table
WHERE post_id = var_post_id
AND meta_key = 'requirement'

So post_id two is the intended effect (requires 1, has 1, got a complete date) while post_id one is an example of something that should trigger an update (requires 3, has 3, waiting on a complete date) and post_id three should be ignored (requires 1, has 0, just ignore).

Now this would be easy enough with an if/else and 2 or three variables but is this even possible with a mySQL statement? Is there an easier way of going about it within mySQL or should I try and find a PHP solution instead?

Thanks for any assistance, I don't usually ask for help here unless I'm properly stumped and this one really has me going.

Start with getting all the required counts:

SELECT
  post_id,
  meta_value AS required_count
FROM
  this_table
WHERE
  meta_key = 'requirement'

Then derive another row set, which is the actual counts per post_id:

SELECT
  post_id,
  COUNT(*) AS actual_count
FROM
  this_table
WHERE
  meta_key = 'adder_id'
GROUP BY
  post_id

Now join the two sets as derived tables and filter the results on actual_count >= required_count to get the post_ids fulfilling the requirement:

SELECT
  req.post_id
FROM
  (
    SELECT
      post_id,
      meta_value AS required_count
    FROM
      this_table
    WHERE
      meta_key = 'requirement'
  ) AS req

  INNER JOIN
  (
    SELECT
      post_id,
      COUNT(*) AS actual_count
    FROM
      this_table
    WHERE
      meta_key = 'adder_id'
    GROUP BY
      post_id
  ) AS act

  ON req.post_id = act.post_id

WHERE
  act.actual_count >= req.required_count

You can simplify the last query by joining the act set directly to this_table and slightly modifying the WHERE condition by referencing meta_value instead of required_count:

SELECT
  req.post_id
FROM
  this_table AS req
  INNER JOIN
  (
    SELECT
      post_id,
      COUNT(*) AS actual_count
    FROM
      this_table
    WHERE
      meta_key = 'adder_id'
    GROUP BY
      post_id
  ) AS act  ON req.post_id = act.post_id
WHERE
  -- the filter from the first derived table is moved here
  req.meta_key = 'requirement'

  -- the 'req.required_count' reference is no longer valid or needed;
  -- use 'req.meta_value' instead
  AND act.actual_count >= req.meta_value

We are almost there. Now you need to filter out the post_ids that already have a completed_date. This is called an anti-join (a self-anti-join in this specific case, as you excluding rows from a table based on a condition involving the same table). There are different methods of implementing an anti-join. Last I checked, the method that worked in MySQL better than other methods was the one involving a left join and an IS NULL check. This is how it would be implemented in your case:

SELECT
  req.post_id
FROM
  this_table AS req
  INNER JOIN
  (
    SELECT
      post_id,
      COUNT(*) AS actual_count
    FROM
      this_table
    WHERE
      meta_key = 'adder_id'
    GROUP BY
      post_id
  ) AS act  ON req.post_id = act.post_id

  -- part of anti-join implementation
  LEFT JOIN this_table AS exc ON req.post_id = exc.post_id
                             AND exc.meta_key = 'completed_date'

WHERE
  req.meta_key = 'requirement'
  AND act.actual_count >= req.meta_value

  -- part of anti-join implementation
  AND exc.post_id IS NULL

An outer join could be basically described as an attempt to join. So by using a LEFT JOIN you are attempting to join each req row (which is all the requirement rows) to each exc row (all the completed_date rows) on post_id. The IS NULL check in the WHERE clause is saying that actually you want the rows where there was no match.

Now that you have all the right post_id values, it only remains to add 'complete_date', NOW() to the SELECT list, and you are ready to insert new rows into the table:

INSERT INTO
  this_table (post_id, meta_key, meta_value)
SELECT
  req.post_id, 'complete_date', NOW()
FROM
  this_table AS req
  INNER JOIN
  (
    SELECT
      post_id,
      COUNT(*) AS actual_count
    FROM
      this_table
    WHERE
      meta_key = 'adder_id'
    GROUP BY
      post_id
  ) AS act  ON req.post_id = act.post_id
  LEFT JOIN this_table AS exc ON req.post_id = exc.post_id
                             AND exc.meta_key = 'completed_date'
WHERE
  req.meta_key = 'requirement'
  AND act.actual_count >= req.meta_value
  AND exc.post_id IS NULL
;


Pass 'interval' value to date_trunc function in PostgreSQL procedure I am fetching the "age" of two timestamp columns (End and Start) and further choosing it as to_char 'MM' format. So I have the difference of the months from two timestamp columns.

The syntax in the above statement goes like :

i = (select to_char(age(End,Start),'MM'));

interval_value = (select i || ' month');

Also tried: interval_value = i || '   Month'

Now,

Passing the value of 'i' in another function date_trunc of PostgreSQL.

xyz = (select date_trunc('month',Start::date) + interval_value::text::interval);

The data types for the above variables are:

i numeric :=0

xyz date;

interval_value varchar;

But it doesn't seem to work.

Is there any alternate approach I could use here. The main idea is to get the difference from two timestamps in months and then further passing the difference into the date_trunc function.

I am not entirely sure about your objective, but what you show works with legal identifiers. "end" is a reserved word and I wouldn't use "start" either. Reserved words require double-quoting, best avoided to begin with:

DO
$$
DECLARE
   ts_start       timestamp := '2019-03-20 00:00:00';  -- legal name
   ts_end         timestamp := '2019-07-02 01:12:13';
   i              numeric   := (select to_char(age(ts_end, ts_start),'MM'));
   interval_value varchar   := i || ' month';
   xyz            date      := (select date_trunc('month',ts_start::date)
                                     + interval_value::text::interval);
BEGIN
   RAISE NOTICE '%', xyz;
END
$$;

Can be much simpler, though:

DO
$$
DECLARE
   ts_start       timestamp := '2019-03-20 00:00:00';
   ts_end         timestamp := '2019-06-29 01:12:13';
   xyz            date      := date_trunc('month', ts_start)
                             + date_trunc('month', age(ts_end, ts_start));
BEGIN
   RAISE NOTICE '%', xyz;
END
$$;

db<>fiddle here (raising exceptions to display the result)


JOIN by part of two column names Consider a table with the structure of

id            col1
WqDs.first
WqDs.second
eeRT.first
eeRT.second

How can I JOIN the table to itself using the first part of the id

SELECT idxx, a.col1 AS First, b.col1 AS Second
FROM table1 a JOIN table1 b ON ....

idxx(temporary)    First     Second
WqDs
eeRT

I tried LIKE but it works when an id exists in the id of another row. I thought to trim the first id of the first row and then find it in the second row, but there should be an easier way.

Use locate() to get the position of . and then left() to get the text left of it.

SELECT *
       FROM table1 t11
            INNER JOIN table1 t12
                       ON left(t12.id, locate('.', t12.id) - 1) = left(t11.id, locate('.', t11.id) - 1);


Cannot use GRANT ALL anymore on RDS MariaDB 10.5 but can on 10.2 Using the default admin user I created on a new RDS instance with MariaDB engine 10.5 I cannot use the GRANT ALL anymore and instead get "access denied". This works fine on the 10.2 instance I am upgrading from.
mysql> grant all on `database_name`.* to `database_user`@`%`;
ERROR 1044 (42000): Access denied for user 'master'@'%' to database 'database_name'

The privileges for the default RDS master user are:

I see a report here asking the same question https://forums.aws.amazon.com/thread.jspa?messageID=950604 (Unable to grant table level privileges - Error 1044).

It appears that in 10.3 there was a new privilege introduced, DELETE HISTORY, which means ALL will not work anymore if the user doesn't have it.

It still works. However, in 10.3 there is a new privilege DELETE HISTORY which your sys_dbcreator is missing, so it doesn't have ALL on the database level anymore, and thus cannot grant it.

The only way to do this is to manually specify all the privileges now. I hope I am wrong but this appears to be the case. I suspect this may be a bug in the RDS provisioning and they should add this privilege to the new admin user that is created.
Update: Just chatted with AWS support and they are aware of the issue and agree that the DELETE HISTORY privilege should get GRANT ALL working again. I also asked them to update their provisioning scripts to get this added. They recommended using 10.2 in the meantime.
sources:

https://jira.mariadb.org/browse/MDEV-18884 (GRANT ALL returns Access denied for user with GRANT OPTION)
https://mariadb.com/kb/en/grant/#table-privileges


Help with maintenance strategy I have a rather atypical database situation, so am finding researching it a bit difficult.

The question first: what kind of maintenance strategy do I need to put in place?

The setup:

The whole system is mounted on a vehicle, so at the end of the shift, everything gets turned off (so no scheduling jobs 'during the night').
However, we can ask the user to regularly click on a button somewhere to invoke a maintenance 'job' if necessary.
It is used for recording data (lots of) while the vehicle is moving (currently pumping IObservable data through a queued implementation of IDataReader into an SqlBulkCopy - one set for each table).
Having thought about this quite a lot, I think that we don't need Full Recovery: if anything goes pear-shaped, it's going to take more time to restore a backup than it would to just go and record again.
It is installed on the other side of the world, in a different time zone, and the users aren't DBAs.

My main concern is the log file: will it grow uncontrollably, or become 'fragmented' (or whatever the right term is).

What kind of maintenance strategy do I need to put in place?

Frankly this is something I have not worked on but if I were in your place I would take some time before closing the system and devote that time to maintenance activity. Depending on fragmentation suppose you plan is to perform the maintenance task weekly. So on Friday or weekends(as you like it) before closing the system why not give couple of hour  to the SQL Server and within that hour perform maintenance task.

  My main concern is the log file: will it grow uncontrollably, or become 'fragmented' (or whatever the right term is).

Yes if database is in full recovery mode and NO proper transaction log backup is in place or no transaction log backup is taken at all the log file can grow huge and can grow greater than data file. In full recovery mode ONLY transaction log backup can truncate the logs and make them reusable. This is how circular nature of transaction Log works. The working of transaction log is quite nicely explained in Logging And Recovery In SQL Server.

Full recovery model is ONLY suggested when point in time recovery is required. If you don't need point in time recovery and you are OK with some data loss change the recovery model to simple. In simple recovery model log truncation happen automatically after checkpoint hapens or when log file grows 70% of its size, only exception is if long running transaction requires a portion of log file then it wont be truncated. So you can see in simple recovery log truncation is managed by database engine. You cannot take transaction log backup in simple recovery mode.


MongoDB --- Failed global initialization: Failed to open "/var/log/mongodb/mongod-config.log" I'm trying to setup the config servers for mongodb sharding. I created a specific config file that is set to log to /var/log/mongodb/mongod-config.log.

When I run mongod --config <path-to-config-file>, I get this error:

`F CONTROL  [main] Failed global initialization: FileNotOpen: Failed to open
"/var/log/mongodb/mongod-config.log"`

When I run the same command but with sudo, it runs but there's no output.

But then when I try starting the mongo shell at the port (27019) specified by my config file, I get this:

MongoDB shell version v3.6.4
connecting to: mongodb://127.0.0.1:27019/test
2018-05-01T03:13:13.399+0000 W NETWORK  [thread1] Failed to connect to
127.0.0.1:27019, in(checking socket for error after poll), reason: Connection
refused
2018-05-01T03:13:13.400+0000 E QUERY    [thread1] Error: couldn't connect to
server 127.0.0.1:27019, connection attempt failed :
connect@src/mongo/shell/mongo.js:251:13
@(connect):1:6
exception: connect failed

I'm just following the MongoDB sharding documentation

I've seen some of the previous posts on here and one was the same problem. No solution there, but I want to try again and see if anybody has a solution to this.

Not sure if I missed something or what the issue is...

So, I have figured out my own question.

Firstly, the issue was that since I was using a different port 27019 for the config servers, I needed to open that port from the firewall.

Second, I changed the dbPath in my config file from /var/lib/mongodb to /data/db since that is the default path for mongodb to store data.

Make sure to run sudo service mongod restart after.

With that done, I was able to start mongod with my specific config file:

sudo mongod --config <path-to-config-file>

The key is to include sudo for me. That solved the issue of:

F CONTROL  [main] Failed global initialization: FileNotOpen: Failed to open
"/var/log/mongodb/mongod-config.log"

After running the command, I see that mongod has started on my virtual machine (vm) and now I am able to connect to the mongo shell of said vm from another machine with:

mongo <ip_address_of_said_vm>:27019


Extremely bloated transaction log during delete Now, I know that the log can get large during big deletes and one should try to chop it into batches. But I find this scenario strange and if someone could explain it to me that would be GREAT!.
I have a 27GB database with around 30GB log. The table that is beeing deleted from is around 15GB in size, with 20 million records in it. The table has 20 columns only with bigint and int data types.
The delete operation is deleting 18 million duplicate records from the table in question. I have switched selected for the delete to make sure the record count matches and it does.
The log grows to about 110GB before I run out of disk space and it´s forced to rollback.
There are no triggers on the table but 16 Non-clustered indexes, no clustered index. If I disable all indexes before running the operation it finishes without ever growing the log from the original size.
So my question is, am I logging every delete from every index when deleting from the table? If so, is that normal behaviour or could the reason be because of missing clustered index?

So my question is, am I logging every delete from every index when deleting from the table?

Of course you are.  The log contains a record for every change to every page with sufficient detail to either redo or undo the change.

If so, is that normal behaviour or could the reason be because of missing clustered index?

Unrelated.  You're deleting 18 million rows from each of 17 seperate "indexes", 16 nonclustred indexes and one heap.
And it's safer to disable/rebuild the non-clustered indexes than drop/create them because the index definitions are retained on the server.


How to to select data from a large MySQL table? Is there any best solution? Let say we have a table with a large number of records. Now to show up all the data using pagination in a basic framework using MySQL query, we can use limits to get a piece of record.

SELECT * FROM TABLE_NAME WHERE CONDITION ORDER BY COLUMN1 LIMIT 0,100;
... and so on ...

Though to my understanding LIMIT will work once the temporary result table is formulated or in other words, the search will go through all the rows once and then the final result will be populated. Firstly Am I right?

The query result takes much time to get data with limit and sometimes the system just reaches execution time limits. Is there any other better solution for this?

The best solution for pagination is not to use OFFSET.  Instead "remember where you left off".  See http://mysql.rjweb.org/doc.php/pagination

However, there are cases where that is difficult.  In your example of

SELECT * FROM TABLE_NAME
    WHERE CONDITION                -- This
    ORDER BY COLUMN1 LIMIT 0,100;

This works nicely.  The 1st page will take only as long as fetching the 100 rows for it.  And the 1000th page will be delivered as fast as the 1st.

SELECT * FROM TABLE_NAME
    WHERE id > $left_off            -- where you left off
    ORDER BY id LIMIT 100;

But if the 'condition' is too messy, it may have to gather all the rows, and only then figure out where you left off.  JOINs often add to the messiness.

If you would like to give the specific query, plus SHOW CREATE TABLE, we can discuss your situation.

  LIMIT will work once the temporary result table is formulated

It depends on whether a single (possibly 'composite') index can short circuit things.

What you really need to worry about is "OFFSET must step over the skipped rows."  That's why "left off" may work much better.