PostgreSQL CPU 100% after configure Wal log shipping hot standby

By exposing a database with a weak superuser password to the internet you invited somebody to break in and use your CPU for their own purposes, probably mining crypto-currencies.
Take the machine from the internet, wipe it clean and re-install the operating system. In the case of a hosted database, decommission the database and get a new one.
To protect yourself from this problem, choose good passwords.


Mysql to Mongodb data migration application for use in production environment

depends from what Your need, it could be replication, such as:

https://pubs.vmware.com/continuent/tungsten-replicator-3.0/deployment-mongodb.html
http://www.symmetricds.org  -

or ETL Tools, like:

Telend
Pentaho
Informatica


what are the best ways to sync two databases in sql server or azure sql db?

You seem to want to do data replication in almost real-time. Why haven’t you considered SQL Server data replication or even better, an availability group?
Our data warehouse is setup as a cluster-less availability group and the TB sized DBs are kept in sync.


Is it possible to set the prompt to a variable - MySql - MariaDB

AFAIK MySQL prompt cannot be set to MySQL variables (Global or Session). The only options that can be used are documented at https://dev.mysql.com/doc/refman/5.7/en/mysql-commands.html (look towards the end of the documentation page for a full list).


select in (select) allow to get duplicate values

The query that returns 609 rows apparently has only 12 distinct parentID values. If you use it as an IN subquery matched against a unique column (which I am assuming ffPackage.ID is), you can only get 12 rows as the result.

If you want to get as many rows as the subquery returns, one way is to use the subquery as a derived table and join it to ffPackage:

SELECT
  serialnumber
FROM
  dbo.ffPackage AS p
  INNER JOIN
  (
    -- the 609 row subquery
    select parentID from ffPackage where SerialNumber in (
    select ffPackage.SerialNumber from ffEmployee, ffHistory, ffSerialNumber, ffPackage, ffUnitDetail
    where ffHistory.UnitID in (
    select UnitID from ffUnitDetail where OutmostPackageID in (
    select ID from ffPackage where SerialNumber = 'xxxxxxxx'))
    and ffHistory.EmployeeID = ffEmployee.ID
    and ffHistory.UnitID = ffSerialNumber.UnitID
    and ffHistory.UnitStateID = 'xxxx'
    and ffUnitDetail.UnitID = ffHistory.UnitID
    and ffUnitDetail.InmostPackageID = ffPackage.ID
    ))
  ) AS sub ON p.ID = sub.ParentID
;

Incidentally, I would like to suggest that you rewrite the comma joins in the subquery as explicit joins – for consistency and maintainability's sake:

SELECT
  SerialNumber
FROM
  dbo.ffPackage AS p
  INNER JOIN
  (
    -- the 609 row subquery
    SELECT
      ParentID
    FROM
      dbo.ffPackage
    WHERE
      SerialNumber IN
      (
        SELECT
          p.SerialNumber
        FROM
          dbo.ffEmployee AS e
          INNER JOIN dbo.ffHistory AS h ON h.EmployeeID = e.ID
          INNER JOIN dbo.ffSerialNumber AS sn ON h.UnitID = sn.UnitID
          INNER JOIN dbo.ffUnitDetail AS ud ON ud.UnitID = h.UnitID
          INNER JOIN dbo.ffPackage AS p ON ud.InmostPackageID = p.ID
        WHERE
          h.UnitID IN
          (
            SELECT
              UnitID
            FROM
              ffUnitDetail
            WHERE
              OutmostPackageID IN
              (
                SELECT ID FROM ffPackage WHERE SerialNumber = 'xxxxxxxx'
              )
          )
          AND h.UnitStateID = 'xxxx'
      )
  ) AS sub ON p.ID = sub.ParentID
;

I would also argue that using better formatting and short (but meaningful) table aliases contributes to maintainability of your queries too. And you may want to consider getting into the habit of always qualifying your tables with the schema name as well as that of using statement terminators.


SQL 2012 Upgrade Advisor cannot detect SQL Server

I was going to delete my question but it got voted +1..anyhow
Needed to enable access to remote registry as the error says...

How to: http://www.windowsreference.com/security/how-to-disablerestrict-remote-registry-access-in-windows/


Why don't SQL Server queries use more than 7MB/sec of disk I/O

From the comments chain, it looks like you're interpreting ASYNC_NETWORK_IO waits to mean that the problem is related to the network. It (typically) isn't.

As @MartinSmith hinted at (twice) the most likely explanation for that is SSMS or the application you're using not consuming the results as fast as SQL Server is serving them.  Follow either of the suggested methods to remove the consumption of the rows from your measurement and you'll get a true(r) picture of the maximum IO throughput:

Discard results after execution
Assign the result to scalar variables

Just in case you haven't already, you'll obviously need to DBCC DROPCLEANBUFFERS to ensure the data is actually read from disk rather than buffer cache. Usual caveats of "on test only, don't do this in an active live environment" etc apply.

Honing in on a couple of your other comments:

  While executing a query that returns 9 million rows CPU usage will stay around 13% with 9% attributable to sqlserver... wouldn't it return the results quicker than 3 min if all data was in RAM?

What exactly are we testing here, how and why? If your 9 million row query is anything other than a SELECT * FROM dbo.SomeTable then there are a 1001 factors that come in to play, other than just raw IO throughput.

Your Intel I7-3820 is a 4-core processor. If your test query doesn't generate a parallel plan, I'd be surprised if you could thrash more than 20% CPU utilisation from the system.

The 3 minutes to return 9 million rows is very suspicious and suggests we're not getting a full picture of what your testing. My guess would be this is a case of sub-optimal (non-parallel) query plan, stuffed full of nested-loop operators pulling millions of rows i.e. not just a single table SELECT to verify the IO consumption.

I suggest:

SELECT * to test just IO through SQL Server.
New question with the execution plan of your query if you want to dig into why it doesn't saturate the IO.


Ubuntu MS SQL Server RESTORE fails beacuse 'LOG_BACKUP' is full

It sounds like the transaction log needs to grow during the upgrade portion of the restore from 2008 to vNext, but is unable to. Here are a few things to try:

Back up the log on the source database
Enable autogrowth on the source database log, if not already enabled
Make sure the autogrowth amount is set to a size that is smaller than the amount of free space on the target volume
Size the log on the source database so that it has some free space and won't have to autogrow during the restore
Take a fresh full backup and retry the restore to the target linux instance


Should I add a clustered Index on a foreign key that is not unique?

It would be a great design approach to put the  clustered index on your FK if you will be fetching all the lines (I mean all the data of the line) for a given FK, or making joins based on that key : as they're stored physically in this order, they will come up much more quickly. Otherwise you could use a non-clustered index (slower fetching of the actual rows' data).

You may encounter a slower insert performance though.

Values can be multiple in a CI, they are also very efficient to quickly fetch a single, uniquely identified, row's data (Is it the reason why MS puts the clustered index on the PK by default ?).

I would recommend to make some tests using both approaches with a subset of your data, and check the estimated execution plans using CTRL+L, then include the EP via CTRL+M before you execute the test statement. This way you will see wich indexes are being used and how the engine modifies its strategy depending on what you are querying.

I generally avoid includig dates for unicity beacause on multiple insert statements the date for all inserted records is the same... I prefer using the default value of the uniqueidentifier type : myCol uniqueidentifier NOT NULL DEFAULT(NEWID()) which ensures uniqueness among all.

If you need all the columns' data of several rows with the same foreign key I would use the clustered index on non unique values. You may however check the execution plans.


Allowing users to compile / run PLSQL and SQL

I think as a DBA you will inevitably lose the fight to keep hands out of your database. Having said that I think we owe it to our customers to try and provide a product that they can use. There are dangers and pitfalls of even read-only access that any DBA should be aware of:

You admitted that you are working with large record counts in your tables. What is your game plan when a user inadvertently issues a Cartesian Join?
If you are talking about read/write access how are you going to hold those users accountable for their actions? If financial data is involved (SOX) then it is not enough to just prove you are auditing the modifications to sensitive data you have to show you have removed their ability entirely.
If this is a production environment have you reconfigured your DR/backup solution? If your backup solution involves taking the DB off-line then what is your solution if a user puts a procedure in DBMS_JOB to run during it?
Lastly, and probably most importantly, who is going to support their code? If your org has a dev team and they are not on board for support I can guarantee that as the DBA you are going to get a finger pointed at you as soon as something goes wrong. Make that expectation up front.

I think thats enough devils advocate but I guess in summary my answer would be:
 "Never in production and never in QA. If you want to run code you write it you support it and then you package it in a deployable format so I can push it out. If you want to act like a developer I'm going to treat you like one."


Mysql: Order by boolean value mixed into the results

To do so, the actual search results and the sponsored ads are sorted respectively.
Then, room for the sponsored ads needs to be made in the sequence of the actual search results.
Finally, they are put together, and displayed in defined order.

You asked for every third job offer to be a sponsored one:

WITH
SearchTerm AS (
  SELECT
    ROW_NUMBER() OVER (ORDER BY created_at) AS search_term_order
    , jobs.*
  FROM jobs
  WHERE description LIKE '%programmer%'
),
PremiumAdd AS (
  SELECT
    ROW_NUMBER() OVER (ORDER BY created_at DESC) AS premium_order
    , jobs.*
  FROM jobs
  WHERE is_premium = 1
),
FinalOrder AS (
  SELECT
    id
    , description
    , created_at
    , is_premium
    , MOD(search_term_order + 1, 2) + FLOOR((search_term_order + 1) / 2) * 3 - 2 AS position
  --  , MOD(search_term_order + 2, 3) + FLOOR((search_term_order + 2) / 3) * 4 - 3 AS position
  FROM SearchTerm
  UNION ALL
  SELECT
    id
    , description
    , created_at
    , is_premium
    , premium_order * 3
  --  , premium_order * 4
  FROM PremiumAdd
  ORDER BY position
)
SELECT
  id, description, created_at, is_premium FROM FinalOrder
;

The comments show the adjustments for having an ad in every fourth row…

N.B.:

Ads holding the search term would be listed twice. This could be handled by adjusting the WHERE clauses.
This requires MySQL 8.

Please comment, if and as this requires adjustment / further detail.


I'll hazard the guess of the extra comma at the end of PROFESORES may be causing the error to be thrown.  It's the only thing that looks out of place.


SQL Server log shipping: How to give SQL Agent the needed rights to remote share?

Create a service account in your Active Directory or as a local account on the primary server. Add that account to the folder's security permissions with change and read permissions.

http://www.mssqltips.com/sqlservertip/2562/sql-server-log-shipping-to-a-different-domain-or-workgroup/

The service startup account defines the Microsoft Windows account in which SQL Server Agent runs and its network permissions. SQL Server Agent runs as a specified user account. You select an account for the SQL Server Agent service by using SQL Server Configuration Manager.

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

To set the Service Startup Account for SQL Server Agent:

In Registered Servers, click the plus sign to expand Database
Engine.
Click the plus sign to expand the Local Server Groups folder.
Right-click the server instance where you want set up the Service
Startup Account, and select SQL Server Configuration Manager….
In the User Account Control dialog box, click Yes.
In SQL Server Configuration Manager, in the console pane, select SQL
Server Services.
In the details pane, right-click SQL Server Agent (server_name),
where server_name is the name of the SQL Server Agent instance for
which you want to change the service startup account, and
select Properties.
In the SQL Server Agent (server_name)Properties dialog box, in
the Log On tab, select one of the following options underLog on as.

https://msdn.microsoft.com/en-us/library/ms186264.aspx#SSMSProcedure

You must grant read permissions on this network share to the proxy account under which the copy job will run at the secondary server instance.

By default, this is the SQLServerAgent service account of the secondary server instance, but an administrator can choose another proxy account for the job.

Network path to the download folder


Replay (re-execute) MySQL SELECT queries from a log file

I found a solution for my problem. It is a combination of Percona pt-query-digest and Percona playback.

First, I filter the slow log by database name and exclude all statements that do not start with select.

cat mysql-slow.log |  pt-query-digest --filter '(($event->{db}) =~ /my_database/) && $event->{arg} =~ m/^select/i' --output slowlog > my_database_selects.log

As output, I get a new slow log file that only contains the desired queries.

percona-playback --mysql-max-retries 1 --mysql-host example.org --mysql-port 3306 --mysql-username warmup --mysql-password S-E-C-R-E-T --ignore-row-result-diffs --query-log-file ~/my_database_selects.log

As result, I get an overview of the query execution time and a warmed up database.

Detailed Report
----------------
SELECTs  : 41666 queries (33379 faster, 8287 slower)
INSERTs  : 0 queries (0 faster, 0 slower)
UPDATEs  : 0 queries (0 faster, 0 slower)
DELETEs  : 0 queries (0 faster, 0 slower)
REPLACEs : 0 queries (0 faster, 0 slower)
DROPs    : 0 queries (0 faster, 0 slower)

Report
------
Executed 41666 queries
Spent 00:00:45.795945 executing queries versus an expected 00:02:07.966942 time.
33379 queries were quicker than expected, 8287 were slower
A total of 0 queries had errors.
Expected 79870 rows, got 79868 (a difference of 2)
Number of queries where number of rows differed: 0.

Average of 265.39 queries per connection (157 connections).

I hope this helps someone with a similar problem.

Installing Percona Playback

There is a bug in the current version that requires to change a library path.

sudo apt-get install libtbb-dev libmysqlclient-dev libboost-program-options-dev libboost-thread-dev libboost-regex-dev libboost-system-dev libboost-chrono-dev pkg-config cmake  libssl-dev
git clone https://github.com/Percona-Lab/query-playback.git
cd query-playback/

Edit the the file CMakeLists.txt  (in the directory   ~/git/query-playback/percona_playback/mysql_client/CMakeLists.txt ) and replace  find_library(MYSQL_LIB"mysqlclient_r" PATH_SUFFIXES "mysql") with find_library(MYSQL_LIB "mysqlclient" PATH_SUFFIXES "mysql") (remove the _r suffix).

mkdir build_dir
cd build_dir
cmake -DCMAKE_BUILD_TYPE=RelWithDebInfo ..
make
sudo make install

Here is a link with some background


MariaDB 10.2 - SQL Dump of specific columns

Create a view v_ip that contains only the columns you want, and then dump that view into a csv file.

select * from v_ip into outfile '/tmp/test.sql'
   fields terminated by ','
   enclosed by '"'
   lines terminated by '\n';

Importing should be done with "load data".


DateTime Conversion works for string value but for calculated value throws "out-of-range" error

Are you able to use date based functions to build your datetime.

Select
Dateadd(hh,11,
Dateadd(mi,50,
Dateadd(ss,30,
Convert(datetime,eatdate))))

Sorry if I've made any mistakes, I'm typing on my phone.


Determine minimal permissions required for a given SQL script or session

I am with Stacy on this one, abstracting the user to make sure they cannot do anything directly and controlling the view/SP code, would work in pretty much all cases. If you run into more complex issues because of a dependancy on elevated permissions (e.g. view serverstate) you may want to look into custom server/application/database roles depending on your need. For checking the permissions you will need for a given task, you could have a look at the permission poster for SQL Server 2016 (https://github.com/microsoft/sql-server-samples/tree/master/samples/features/security/permissions-posters). Azure uses the same permissions, so if it works on-prem it will also work in Azure (according to the applies to statement), but check it to be sure. Azure does have a few less then well documented features here and there. :-) Good luck!

Feel free to mark the awnser or upvote. :-) If you need more info, just let me know.


How to query the top TWO and the bottom TWO values at the same time in MySql

You can achieve this by simply following this query as well.

    SELECT * FROM (
            SELECT playerName,teamName, scoreChange,category
            FROM players ORDER BY scoreChange LIMIT 2
        ) AS t1
    UNION
    SELECT * FROM (
            SELECT playerName,teamName, scoreChange,category
            FROM players ORDER BY scoreChange DESC LIMIT 2
        ) AS t2


Replication members count limitation in MongoDB

Yes! Of course!
But in the situation where you lose one of the nodes (primary or secondary), remaining node state will be secondary and you cannot insert/update/delete data.
If you can add (one) arbiter to that replica set, your DB will keep working even you lose one node.
Basically arbiter don't need many resources, smallest VPS node what you can find is enough. (512MB memory, 1 CPU, 10GB diskspace) OR you can even add that arbiter to that application machine what is connecting to that mongoDB.


MySQL Update Trigger that updates multiple columns from values in another table

If you want to get right down to it, that's not the best approach from a theoretical perspective, because you're copying data around in your database that you really should be deriving.

CREATE ALGORITHM=MERGE VIEW products_with_packaging_info AS
SELECT p.*,
       pt.width as packaging_width,
       pt.height as packaging_height,
       pt.weight as packaging_weight,
       pt.case_count AS packaging_case_count
  FROM PRODUCTS p
  JOIN PACK_TYPES pt ON pt.id = p.packaging_type;

Done.  SELECT queries against this view work exactly the same as queries against either table individually, as long as every product has a pack type.  Queries against this view can still take advantage of the indexes on the base tables, and there's no overhead involved with copying the attributes from one table to another, which always has the potential for update anomalies.

You might even be surprised to find that the columns in the view can actually be updated as if it were a table, with updates propagating down into the base tables.

I offer this suggestion because a well-designed database should be such that it is impossible to get two different answers to the same question.  For example, if a PACK_TYPES row is changed because an error is found, how do its new values propagate backwards into products?

But if you really want to take the trigger approach, that looks something like this:

DELIMITER $$

DROP TRIGGER IF EXISTS PRODUCTS_bu $$
CREATE TRIGGER PRODUCTS_bu BEFORE UPDATE ON PRODUCTS FOR EACH ROW
BEGIN

  IF NOT (NEW.packaging_type <=> OLD.packaging_type) THEN
    BEGIN
      DECLARE my_width INT DEFAULT NULL;      -- using
      DECLARE my_height INT DEFAULT NULL;     -- the
      DECLARE my_weight INT DEFAULT NULL;     -- appropriate
      DECLARE my_case_count INT DEFAULT NULL; -- data types here
      SELECT width, height, weight, case_count
        FROM PACK_TYPES
       WHERE id = NEW.packaging_type
        INTO my_width, my_height, my_weight, my_case_count;
      SET NEW.width = my_width, NEW.height = my_height, NEW.weight = my_weight, NEW.case_count = my_case_count;
    END;
  END IF;

END $$
DELIMITER ;

The <=> "spaceship" is the "null-safe equality operator" which constrains "NOT [possibly null] = [possibly null]" to always be either TRUE or FALSE; this is needed because [possibly null] != [possibly null] will never be true if either expression is NULL. This is the case because, logically, "NOT (FALSE)" is "TRUE" while "NOT (NULL)" is "NULL."

I could have declared the variables at the beginning and avoided the inner BEGIN/END  but it seems optimal to avoid that work until we know we actually need to execute the inner logic in the first place, which is avoided whenever 'packaging_type' hasn't actually changed on a row for a given update query.  Within a block, declarations have to precede other statements, so delaying the declarations requires the addition of the inner BEGIN/END.

You would also want a similar trigger for BEFORE INSERT which would be identical except you'd remove the 4 lines starting with IF ... BEGIN ... END ... END IF from the body of the procedure, use a new trigger name, and change BEFORE UPDATE to BEFORE INSERT.

It's BEFORE -- not AFTER -- in both cases, because the trigger fires BEFORE the newly-inserted or newly-updated row is written to the database.


How to link other tables (entities) to parent and child categories?

I am going to suggest an alternate solution.

Rather than trying to mock-up a database design in Visio, why don't you create the database design in the relational database? Once that is complete you can use software to create the database diagram from your actual database. Most relational databases have database diagrams built in, and if the built in diagrams don't fit your needs there are a variety of third-party alternatives, many of them free, or with trial periods.

The advantages to this approach is that doing the actual design will help you think through your choices, and even if it doesn't end up being the final design, you can use the work as a starting point.


Insert results of spBlitzIndex stored procedure into table

I think there is a open request to have this fixed.

It does appear that they have made some changes in March 2017 as referenced here:

Added Output parameters to sp_BlitzIndex to send Mode 2 results to
permanent repository

I also found a workaround that uses Powershell.

Using PowerShell To Store sp_BlitzIndex Results In A Table.

I'm copying the important information from that post.

  First we need to create a table to store our results in:

USE Master --change this to wherever you want this table to reside
GO
CREATE TABLE [dbo].[BlitzIndexStats](
    [Priority] [tinyint] NULL,
    [Finding] [varchar](255) NULL,
    [DatabaseName] [nvarchar](128) NULL,
    [Details] [varchar](2000) NULL,
    [Definition] [varchar](4000) NULL,
    [SecretColumns] [varchar](512) NULL,
    [Usage] [varchar](255) NULL,
    [Size] [varchar](255) NULL,
    [CaptureDate] [datetime] NOT NULL DEFAULT (sysdatetime())
) ON [PRIMARY]

  I decided to leave off things like the URL and CREATE T-SQL statements
  that are returned, and only store the data I was after.

  Next we use the following PowerShell script to run sp_BlitzIndex and
  store the results in our newly created BlitzIndexStats table.

#call the sp_BlitzIndex procedure and specify the names and data types of the results;
#save the result set in the $BlitzIndex variable
$BlitzIndex = Invoke-Sqlcmd -ServerInstance $SQLInstance -Database master -Query "EXEC ('exec dbo.sp_BlitzIndex @DatabaseName=''--YourDBNameHere--'',@Mode=4')
WITH RESULT SETS
(
    (
    [Priority] smallint
    ,[Finding] varchar(255)
    ,[DatabaseName] nvarchar(128)
    ,[Details] varchar(2000)
    ,[Definition] varchar(4000)
    ,[SecretColumns] varchar(512)
    ,[Usage] varchar(255)
    ,[Size] varchar(255)
    ,[Info] varchar(255)
    ,[URL] varchar(255)
    ,[CreateStatement] varchar(4000)
    )
)"

#Query the data that we want out of our $BlitzIndex result set, and set the $BlitzIndex result set to just contain that data
$BlitzIndex = $BlitzIndex | SELECT Priority,Finding,DatabaseName,Details,Definition,SecretColumns,Usage,Size | Where-Object {$_.Priority -gt 0} | Sort-Object Priority,Finding

#Loop through our desired results
foreach ($i in $BlitzIndex) {
    #we have to set these variables to keep the PowerShell from erroring when encountering sub-expression statements
    $Priority = $i.Priority
    $Finding = $i.Finding
    $DatabaseName = $i.DatabaseName
    $Details = $i.Details
    $Definition = $i.Definition
    $SecretColumns = $i.SecretColumns
    $Usage = $i.Usage
    $Size = $i.Size
    #Insert our results into our table
    Invoke-Sqlcmd -ServerInstance $SQLInstance -Database master -Query "INSERT INTO dbo.BlitzIndexStats (Priority,Finding,DatabaseName,Details,Definition,SecretColumns,Usage,Size) VALUES ($Priority,'$Finding','$DatabaseName','$Details','$Definition','$SecretColumns','$Usage','$Size')"
}

  There are two things I want to point out before running that script:

  Be sure to replace “–YourDBNameHere–“ with your database name
  If you created the BlitzIndexStats table in a different database, modify “-Database master” in the last line of the script


I don't think there is a way to get this query to perform well, but the code will be easier to read if you use the IN predicate:

select *
from tbl1 t1
join tbl2 t2
    on t1.col1 in (t2.col1, t2.col2, ...)
    or ...
    or t1.col5 in (t2.col1, ... )


Postgres 11 - Range Partitioning on a Date Column with NULL values

PostgreSQL 11 allows you create a default partition for range-partitioned tables, which will receive the NULL values.
You can add a check constraint demanding the column be NULL, if you want to prevent other rows not covered by existing partitions from going into that default table.
create table for_nulls PARTITION OF parent_table (check (date_requested is null)) DEFAULT;


how to convert from raiserror to throw in the current context?

I think this is what you mean? This is the closest you can get AFAIK.

--Catch any errors, include stored proc name for stack trace & cheeky link to SE
BEGIN CATCH;
       IF (XACT_STATE()) = -1 OR ((XACT_STATE()) = 1 AND (@@TRANCOUNT = 0 OR @@TRANCOUNT > 0))
          BEGIN
             ROLLBACK TRAN;
             DECLARE @errMsg NVARCHAR(MAX)= FORMATMESSAGE('%s: %s Try troubleshooting: http://dba.stackexchange.com/search?q=msg+%i', OBJECT_NAME(@@PROCID),ERROR_MESSAGE(),ERROR_NUMBER());
             THROW 51000, @errMsg, 1; --Throw error
          END
END CATCH


Does ALTER INDEX ALL REBUILD use more transaction log space with simple recovery model than rebuilding each index individually?

I assumed that following each index operation performed by the "ALL" form of the command, the transaction log data would be flushed prior to the next index rebuild. Is that how it actually works, or are the index rebuilds logged as if they are part of a single transaction?

1) Log flushing: the SIMPLE recovery model does not clear the log after every transaction, but at checkpoints. (link for more info)

2a) REBUILD ALL: yes, REBUILD ALL works as a single transaction. The index rebuilds within have their own transactions, but the overall operation isn't fully committed until the end. So yes, you might limit log file growth by rebuilding individual indexes (and possibly issuing CHECKPOINT commands).

2b) Proof! Here, have a demo script. (Built in 2016 dev)
First, set up a test db, with table and indexes:

USE master
GO

CREATE DATABASE Test_RebuildLog
GO

ALTER DATABASE Test_RebuildLog
SET RECOVERY SIMPLE
GO

USE Test_RebuildLog
GO

CREATE TABLE IndexTest
(ID int identity(1,1),
a char(1),
b char(1))

CREATE CLUSTERED INDEX CIX_IndexTest_ID ON IndexTest(ID)
CREATE INDEX IX_IndexTest_a ON IndexTest(a)
CREATE INDEX IX_IndexTest_b ON IndexTest(b)

INSERT IndexTest
(a,b)
VALUES ('a','b'),('z','y'),('s','r')

Now you can compare log activity between REBUILD ALL and rebuilding individually

CHECKPOINT
GO
ALTER INDEX ALL ON IndexTest REBUILD

SELECT *
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation = 'LOP_COMMIT_XACT'
OR Operation = 'LOP_BEGIN_XACT'
GO

CHECKPOINT
GO
ALTER INDEX CIX_IndexTest_ID ON IndexTest REBUILD
ALTER INDEX IX_IndexTest_a ON IndexTest REBUILD
ALTER INDEX IX_IndexTest_b ON IndexTest REBUILD

SELECT *
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation = 'LOP_COMMIT_XACT'
OR Operation = 'LOP_BEGIN_XACT'
GO

Note how the first open transaction (Transaction ID 0000:000002fa for me) isn't committed until the end of the REBUILD ALL, but for the index-by-index rebuilds, they are successively committed.


SQL Server system views VERY slow at one computer

This sounds more like a name resolution / reverse lookup issue. Have you tried adding an entry in the hosts file on the "Computer outside of domain"?

Windows keeps it in C:\Windows\System32\drivers\etc

Edit - https://stackoverflow.com/questions/10872557/how-slow-are-tcp-sockets-compared-to-named-pipes-on-windows-for-localhost-ipc


Does SKIP LOCKED FOR UPDATE work with views?

It does work as expected.

To test it, start two sessions and use explicit transactions, e.g.

Session 1:

BEGIN;
SELECT * FROM outstanding_task WHERE id=1 FOR UPDATE SKIP LOCKED

Session 2:

-- Here SKIP LOCKED is optional.
BEGIN;
SELECT * FROM outstanding_task WHERE id=1 FOR UPDATE SKIP LOCKED

Assuming there is an outstanding task with id=1, then the first transaction should return a row and the second one should not.


SQL Server, how to log client PID of failed login attempts?

Default Server Trace
Try to get more info from the default server trace, as Eitan Blumin's article Finding the Details Missing from the SQL Server Failed Logins Audit shows using this query:
SELECT  trc.*
FROM fn_trace_getinfo(default) AS inf
CROSS APPLY fn_trace_gettable (convert(nvarchar(255), inf.value),default ) AS trc
WHERE inf.property = 2 AND inf.value IS NOT NULL
AND trc.EventClass= 20
ORDER BY trc.StartTime DESC

XE Session
Alternatively, Dave Bland's article Find Failed Logins Using Extended Events has an example of how you can achieve it using an XE Session:
CREATE EVENT SESSION [FailedLogins] ON SERVER
ADD EVENT sqlserver.error_reported(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.nt_username)
WHERE ([severity]=(14) AND [error_number]=(18456) AND [state]>(1)))
ADD TARGET package0.event_file(SET filename=N'C:\temp\FailedLogins.xel')
GO

Don't forget to create the folder listed on the path (or change it). With that XE Session you'll be able to capture the client_app_name which is what you're actually looking for instead of just the PID.
SQL Server Audit
You could also configure a SQL Server Audit, but the doc says:

SQL Server audit uses Extended Events to help create an audit.

Therefore, using an Audit would cost about the same as creating the XE Session.


You can restrict user per database.  I will explain here using sql authentication and you can do the same if you have AD set up.

I have 3 database and 'taiobmdjamshed' is the server admin and have access to all the databases.

I create security1 login with db_owner privilege to security1 database and a user at master database.

CREATE LOGIN security1
    WITH PASSWORD = 'S<<MM12345!@@!'
GO

Create a user in master database.

CREATE USER security1
    FOR LOGIN security1
    WITH DEFAULT_SCHEMA = dbo
GO

Make a user in security1 database and make it a member of db_owner role.

CREATE USER security1
    FOR LOGIN security1
    WITH DEFAULT_SCHEMA = dbo
GO

-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N'security1'
GO

If connecting to the server using security1 login and try to access security2 database this is what will happen because security1 login does not have a corresponding user or privilege in security2 database.

This link explains more in details.


ORA-04091: table ExpenseTable is mutating, trigger/function may not see it

As the trigger says, you cannot read data from the same table as the trigger is working on as it would throw consistency out of the window.
Have you tried to put ExpenseTotal column in a view like below?
CREATE VIEW ProjectsTable_V AS
SELECT
  P.ProjectID,
  P.ProjectName,
  P.Cost,
  (
     SELECT SUM(ExpenseAmount)
       FROM ExpenseTable
      WHERE ExpenseTable.ProjectID = P.ProjectID
  ) ExpenseTotal,
  P.CostRemaining
FROM
  ProjectsTable P

If CostRemaining is Cost - ExpenseTotal you can take it out of your ProjectsTable trigger, too :)
If this is sufficient with regard to the performance, I'd prefer it over the trigger based approach any time...


What is happening under the hood in MySQL 8 when I apply an auto-increment primary key to a raw table of data?

Do you care whether you keep the old auto_increment values?  Your process seems to loose them.
Did you have innodb_file_per_table turned on when the original table was created?  If yes, there are easier ways; if no, you need to dump all tables and rebuild.
Be sure to have innodb_file_per_table=ON
What percentage of the table is to be kept?  If it is a small percentage, don't bother with the CSV steps, simply rebuild the new table as InnoDB.  Also you won't need to rebuild the AI.
While you are at it, consider shrinking the datatypes.  INT takes 4 bytes and BIGINT takes 8.  Look into smaller datatypes.  And UNSIGNED.  (Eg, SMALLINT UNSIGNED takes 2 bytes had has a range of 0..64K.)
Adding and dropping secondary indexes (but not the PK) is efficient in 8.0.
Do you have any other indexes, perhaps even composite indexes?  Is any of them 'unique'?  If so, could you get rid of id and use that index as the PRIMARY KEY?
Can each of the columns really be NULL?  Consider fixing that if necessary.
Do the rename in these steps.  This minimizes downtime:
RENAME TABLE real TO old,
             temp TO real;
DROP TABLE old;

More tips on big deletes and related things:  http://mysql.rjweb.org/doc.php/deletebig


Where in the database file is an index rebuild started? Alternative to SHRINKFILE?

I don't believe those details are documented anywhere, and even if you were to figure it out (by asking Paul Randal, or perhaps by mapping the exact pages before and after an index rebuild), you may not be able to rely on those going forward. You also might end up with some random single pages at the end of the file anyway, and still need to do a shrink.

The best idea, in my opinion, is to do an index rebuild into a different file group:

  Create a new filegroup
  Move all affected tables and indexes into the new filegroup using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the tables and remove fragmentation from them at the same time
  Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)

If that's not an option, then you can instead do a shrink-then-reindex, or maybe a reindex-shrink-reindex:

Initial reindex: this releases free space within each page, and might be necessary if you've made major changes in the table structure like dropping large columns or changing column datatypes
ShrinkFile (never use ShrinkDatabase): Pick a reasonable target size, shrink each file with some remaining free space (I usually pick 15-20% free)
Final reindex: Resolves any fragmentation we created in step 2.

This multi-pass approach is a ton of IO, and might take a while to do. That's why the single-pass approach into a different file group is preferred, if you can pre-allocate the space.

Also, the final reindex may reclaim some of the space recovered in step 2. This is common where the majority of space is taken up by a single huge table or single huge index.


the query does not return 43 rows but 1 row that contains the number 43
the execution time of a query does not depend on the number of rows it return but on the number of rows it inspects.
from your query plan you can see your query makes a full table scan. This means it reads the table from begin to the end and reads all 16 millions of rows of the table (table scan rows gotten 16656961).
for this it has to read about 11G data (physical read total bytes 11768365056) and executes 55,000 read operations (physical read total IO requests 55724). 55,000 reads in 60 seconds (user I/O wait time 6172) means 1.1 ms per read. This is a good value.

You have to decrease the number of row your query has to process. An index may help Oracle to count efficiently the number of rowa you are interested in.

The most selective subclause of your where-clause will be the last_upd subclause. So make an index on this column.
You should add the columns from the other where-clauses (and the select-clause) to the index, too. So Oracle does not have to lookup each fitting row in that is found by the index in the table to get the last_upd_by and last_name values.
The last_upd_by subclause is the next selective clause so it schould be the next column in the index. So you should make an inex on the columns (last_upd, last_upd_by, last_name) in this order.


Why is MySQL running as a single process?

innodb_buffer_pool_size should not be set to 0.  It should be at least 100M, up to about 1000M for a 4GB machine that is running only MySQL.

This may be indirectly causing your problem.


How to reclaim the unused Space in a database

Is there a particular reason you are specifying TRUNCATEONLY?  As per the documentation on DBCC SHRINKFILE:

  TRUNCATEONLY

  Releases all free space at the end of the file to the operating system
  but does not perform any page movement inside the file. The data file
  is shrunk only to the last allocated extent.

  target_size is ignored if specified with TRUNCATEONLY.

If that is not the intended behavior you were looking for (which it doesn't sound like it is), then you could try without that option set.

Also, there are other factors that hinder database file shrinking as well, such as text`ntext\image` BLOB data.  So it is worth looking into the possibility of the existence of data with these data types.


This is a typical situation in which you have to create three tables: Helpers, Contacts, and Help_Contact, using the third table to represent associations between the elements of the first two, by storing the foreign keys for the first two, without any multivalued field in those.

That is, you should have something like:

Helpers(h_primary_key, other_fields_only_relevant_to_helpers)
Contacts(c_primary_key, other_fields_only_relevant_to_contacts)
Help_contact(h_primary_key,c_primary_key)

So, to display information for contacts or helpers associated to another element, you could use a join:

SELECT relevant_info
FROM Helpers h, Contacts c, Help_contact hc
WHERE hc.h_primary_key = h.h_primary_key AND hc.c_primary_key = c.c_primary_key AND other_conditions_on_helper_or_contact_or_both


Why does pg_hba.conf sometimes have random rules added to it? (postgresql)

As you seem to have surmised, "pgdbadm" is an account created by hackers. It is the known account created a recent crypto-mining attacker who exploits unsecured postgresql superuser accounts.  Changing the pg_hba.conf is also part of his MO.


Why would a SQL Server 2008 R2 database disable a table index when upgraded to SQL Server 2012

This has to do with any table which contains a geospatial field. I'm not sure why yet (is it a bug or a feature lol), but that's the cause.

Update: the problem only appears when there is a geospatial field and a PERSISTED column that uses it.


Stored procedure triggered on insert causes deadlock in transaction

Is there a better way to keep the constraint I'm trying to get (uniqueness on b and the YEAR of date d)?

Create virtual generated column which calculates value You'd like to be unique, and create unique secondary index over it.

For more information read MySQL 5.7 Reference Manual / Secondary Indexes and Generated Columns


Can PostgresSQL cluster have synchronus and asyncronus standbys at the same time for the same dbs?

Yes,

There is a setting for this which is called synchronous_standby_names.

You can give expression like ANY 3 (r1, r2, r3, r4) which says "proceed commit as soon as at least any three standbys reply"

If you want to specify exact replication name just name them like r1,r4.

Here is sample usage;

ALTER SYSTEM SET synchronous_standby_names TO 'ANY 3 (r1, r2, r3, r4)';
SELECT pg_reload_conf();

Note: The standby names (which are r1,r2..) are defined by application_name option of standby's primary_conninfo setting value.


If you compute the record using the simple size addition you indeed get 8: 4+1+2+1 (header+fixed size+null bitmap count+ null bitmap itself). But a heap record cannot be smaller than the forwarding stub size, which is 9 bytes, since the record must guarantee that it can be replaced with a forwarding stub. Hence, the record will by actually 9 bytes. A smallint will be 9 bytes both by means of compute and min size. Anything bigger is already bigger than the forwarding stub, so your compute size matches the record size.


Add database-level collation to PostgreSQL

pg_collation is a system table inside pg_catalog system catalog schema. It lists all available collations: https://www.postgresql.org/docs/9.5/static/catalog-pg-collation.html

SELECT * FROM pg_collation;
SELECT * FROM pg_collation WHERE collname='en_US';

pg_collation table belongs to pg_catalog schema. To list all tables in the pg_catalog schema, use

\dS+

One of the columns of pg_collation table is 'collnamespace'. The information of collnamespace can be obtained from pg_namespace table. https://www.postgresql.org/docs/9.5/static/catalog-pg-namespace.html

pg_namespace table contains a hidden column 'oid'. oid is called object identifier used internally to identify postgres objects. Namespace is also known as Schema in postgres. Essentially, the oid number seen in pg_namespace table will be the same oid seen in collnamespace column of pg_collation table for any particular row (Eg: 'en_US).

postgres=# select * from pg_namespace;
      nspname       | nspowner |               nspacl
--------------------+----------+-------------------------------------
 pg_toast           |       10 |
 pg_temp_1          |       10 |
 pg_toast_temp_1    |       10 |
 pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
 public             |       10 | {postgres=UC/postgres,=UC/postgres}
 information_schema |       10 | {postgres=UC/postgres,=U/postgres}
(6 rows)

postgres=# select oid,nspname,nspowner,nspacl from pg_namespace ;
  oid  |      nspname       | nspowner |               nspacl
-------+--------------------+----------+-------------------------------------
    99 | pg_toast           |       10 |
 11320 | pg_temp_1          |       10 |
 11321 | pg_toast_temp_1    |       10 |
    11 | pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
  2200 | public             |       10 | {postgres=UC/postgres,=UC/postgres}
 12453 | information_schema |       10 | {postgres=UC/postgres,=U/postgres}
(6 rows)

postgres=# select * from pg_collation where collname='en_US';
 collname | collnamespace | collowner | collencoding |   collcollate   |    collctype
----------+---------------+-----------+--------------+-----------------+-----------------
 en_US    |            11 |        10 |            6 | en_US.utf8      | en_US.utf8
 en_US    |            11 |        10 |            8 | en_US           | en_US
 en_US    |            11 |        10 |           16 | en_US.iso885915 | en_US.iso885915
(3 rows)

You see the number 11 in oid column of pg_namespace table matches with collnamespace column of pg_collation. This is because, pg_collation table belongs to pg_catalog namespace or schema.

Now, in your case, pg_collation table belongs to public schema (2200) instead of pg_catalog schema(11). I do not know how this happened to you. But, it is safe to say that Schema is below database level in the postgres architecture hierarchy, so you should not have any issue with creating a new database with required collation.

When you initialize a database cluster using initdb command, the default encoding is set during this time and it is applied to template1 default database. Template1 database is used to create new database by default and it will not allow changes to encoding, because it has been already set by initdb during cluster initialization. To create a database with a new encoding, you would have to use template0 database, which will allow you to specify the different collation and the relevant encoding while creating a new database as follows.

postgres=# CREATE DATABASE mydb ENCODING 'LATIN1' LC_COLLATE 'en_US' LC_CTYPE 'en_US' TEMPLATE template0;
CREATE DATABASE
postgres=# \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 mydb      | postgres | LATIN1   | en_US       | en_US       |                       | 6681 kB | pg_default |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7100 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 6681 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 6804 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            |
(4 rows)

Notice the size of mydb is same as template0, because template0 is used to create mydb with LATIN1 encoding and 'en_US' collation.

To see the character encoding mapping to internal number encoding (ranging from 0 to 41), use

mydb=# select pg_encoding_to_char(8);
 pg_encoding_to_char
---------------------
 LATIN1
(1 row)

Hope this helps.


SQL Server - drop user triggered silent revoke of privileges

The tl;dr answer is that nothing went wrong here and the behaviour is as designed:

A DROP USER statement will remove the user (and by extension their permissions).

More full answer:

SQL Server and T-SQL are powerful tools - there are little to no "are you sure?" checks, such as are common in end user applications, when executing queries - the only things that prevent queries executing are only technological errors (not potential user errors) or any database constraints.

SQL SERVER by default is in autocommit - that is, IMPLICIT_TRANSACTIONS are off, meaning that the query is committed as soon as it is finished executing - no second chances.

Although there are no options as such, this behaviour can be controlled. As mentioned in Akina's comments, one could add this custom behaviour (for example through triggers). The way that this is controlled out-of-the box is by permissions - only user's with the correct permissions can drop other users - only some of the highest privileges can execute such statements. As per BOL article on DROP USER, the statement requires the ALTER ANY USER permission on the database (and of course the db_owner and sysadmins can do this)
The DROP USER documentation does state the required permissions, although you need to go into more detail to find out why this happens. In SQL Server permissions are granted TO a USER (or LOGIN at the Instance level) or the USER added to a ROLE. The USER has to exist for it to have these privileges.

You can find more about where permissions are stored in SQL Server as per This question by exploring the tables sys.database_permissions and sys.database_principals - joined on Princpal_id (and you can see for yourself that without the principal i.e. the USER, their can't be a permissions for it). Nb. Although probably not that risky in most cases, exploring system tables at whim on a production DBs is not advised!

This article explains about permissions in SQL SERVER in detail, and from an authorization point of view This BOL article has more information and is a good start for learning the concepts.

What can you do?

To ensure this doesn't happen again, ensure that only appropriate permissions are given to everyone (Principle of Least Privilege), and those with elevated privileges take care and know what they're doing. Importantly, don't make changes in production with first testing (and testing again)! Also - follow change control procedures appropriate in your company.

To recover, you can either restore from a backup, or you'll have to recreate permissions (ideally you'll have documentation about your system to do this from, if you don't this is the time to make it!).

Finally

SQL Server can take no prisoners - and by default SQL Server COMMITs transactions automatically (and keeping that way is a good thing), be sure of your query before executing and test, test and test again.


highlight an instance where a volume reported is unchanged from the previous month

Highlighting would most likely be done in a client tool like Reporting Services. From a TSQL point, you could add an additional column to your result to signify 'No change'.  If you're on at least SQL Server 2012, you can use LAG.

--Setup demo data
declare @t table (Fields varchar(30), Name varchar(30), [Month] varchar(10), Amount int)
insert into @t(Fields,name, [Month],Amount) values
('Instance1','Alan', 'Jan2018', 120),
('Instance2','Alan', 'Feb2018', 140),
('Instance3','Alan', 'Mar2018', 140),
('Instance4','John', 'Jan2018', 50),
('Instance5','John', 'Feb2018', 50),
('Instance6','John', 'Mar2018', 60)

--The actual query
--Using a Common Table Expression, create a SortOrder column
;
With _cte as
(
SELECT *
    ,SUBSTRING([month], 4, 4) + CASE
        WHEN substring([month], 1, 3) = 'Jan'
            THEN '01'
        WHEN substring([month], 1, 3) = 'Feb'
            THEN '02'
        WHEN substring([month], 1, 3) = 'Mar'
            THEN '03'
        WHEN substring([month], 1, 3) = 'Apr'
            THEN '04'
        WHEN substring([month], 1, 3) = 'May'
            THEN '05'
        WHEN substring([month], 1, 3) = 'Jun'
            THEN '06'
        WHEN substring([month], 1, 3) = 'Jul'
            THEN '07'
        WHEN substring([month], 1, 3) = 'Aug'
            THEN '08'
        WHEN substring([month], 1, 3) = 'Sep'
            THEN '09'
        WHEN substring([month], 1, 3) = 'Oct'
            THEN '10'
        WHEN substring([month], 1, 3) = 'Nov'
            THEN '11'
        WHEN substring([month], 1, 3) = 'Dec'
            THEN '12'
        END AS SortOrder
FROM @t)

SELECT *
    ,CASE
        WHEN LAG(amount) OVER (
                ORDER BY NAME
                    ,SortOrder
                ) = amount
            THEN '**'
        ELSE ' '
        END AS NoChangeFromPreviousMonth
FROM _cte
order by name,SortOrder

| Fields    | Name | Month   | Amount | SortOrder | NoChangeFromPreviousMonth |
|-----------|------|---------|--------|-----------|---------------------------|
| Instance1 | Alan | Jan2018 | 120    | 201801    |                           |
| Instance2 | Alan | Feb2018 | 140    | 201802    |                           |
| Instance3 | Alan | Mar2018 | 140    | 201803    | **                        |
| Instance4 | John | Jan2018 | 50     | 201801    |                           |
| Instance5 | John | Feb2018 | 50     | 201802    | **                        |
| Instance6 | John | Mar2018 | 60     | 201803    |                           |


Here you go:

SELECT COUNT(*) FROM (
    SELECT tbl1.id
    FROM tbl1 EXCEPT
    (
    SELECT tbl2.id
    FROM tbl2

    UNION

    SELECT tbl3.id
    FROM tbl3
    )
) AS t;


TNS-12543: TNS:destination host unreachable

Have you looked at the alert log to verify that the database is running. Can you show your environment settings for ORACLE_SID, ORACLE_HOME, LD_LIBRARY_PATH, TNS_ADMIN and PATH? Do you have a listener running? Can you also show the contents of your listener.ora, sqlnet.ora and tnsnames.ora files? It could be that sqlplus can't find your database. Either using the listener or having ORACLE_SID set should get you to your database. You want to verify that the database and version of Sqlplus are both using the same Oracle Home.


SQL Server 2014 Sudden Slow Down / No inserts/ Not related to hardware or index

You have a non-SARGable query - even if you have good indexes, you're not using them with that query.

First, a knee-jerk reaction to pageiolatch_sh is reading pages from disk into the buffer; you don't have enough RAM for the data it's trying to pull.

Second, you need to look at the execution plan and its use of indexes - or the lack thereof.

Quit using functions in your joins and your WHERE, and only get the data you absolutely need to start with.

"BETWEEN ACALLS.StartTime and DATEADD(ss,-5,ACALLS.StartTime)" - get rid of that DATEADD in the BETWEEN.

If this is a read only, reporting database, then create a reporting table with just the data you need, and then put compound indexes on it as required.  Use that one to get the primary keys/other unique keys of the ACALLS rows you need, then get the rest of the ACALLS data later.

WHERE DATEPART(hour,MainView.TimeStamp) BETWEEN 10 and 13 and
CAST(MainView.TimeStamp as date) = '2015-12-09'

same thing - get rid of the CAST - change '2015-12-09' to one or two @parameters of the right data type for MainView.TimeStamp >= @StartTimestamp AND MainView.TimeStamp < @EndTimestamp
and get rid of that DATEPART by restricting the @StartTimestamp and @EndTimestamp to include your hours criteria as well.

Perhaps load up a #temp table with just the primary/unique keys of rows that meet those MainView criteria before the join.

Hmm... also, if Mainview is a complex view, go straight to the base tables to load up that #temp table

Don't forget to use Profiler to check and see if adding (compound if need be) indexes on the #temp or other staging table is a net gain or a net loss :).


For a complex query how do i increase the time SQL Server searches for the best execution plan

There are some undocumented trace flags that allow one to increase or remove some of the limits that the optimizer uses when determining whether a "good enough" plan has been found.

However, all of the results I've ever seen show that plan quality is only minimally improved, if at all, when using these flags.  There is a good reason the timeouts and limits exist.  You're almost certainly better off breaking these queries up into smaller chunks somehow (great YouTube video from Erik Darling on the subject), to make them more manageable for the optimizer to make choices about.

Additionally, these trace flags are undocumented.  So if you do find a better plan using this method (on a test system!), you should try to get that plan using conventional means, like:

rewriting the query,
adding documented query or index hints,
adding / tweaking indexes,
using a plan guide if necessary

The trace flags I'm aware can be enabled at the query level like this:

/* your query here */
OPTION
(
    QUERYTRACEON 8780,
    QUERYTRACEON 8671
)

References:

8780: Optimizer Without Borders (Part 1) by Dmitry Pilugin
8671: Optimizer Without Borders (Part 2) also by Dmitry Pilugin


After adding a slave to Master - Master configuration, slave syncs from only one master

You need to ensure you have log_slave_updates enabled on M1, so that any changes that M1 makes via replication from M2 gets written to M1's master log (and thus replicated to S1).

Don't worry, the entries in M1's binary log will not get re-replicated to M2. M2 will ignore any updates which originated from its server-id.


Is it acceptable to have circular foreign key references\How to avoid them?

Since you are using nullable fields for the foreign keys, you can in fact construct a system that works correctly the way you envision it.  In order to insert rows into the Accounts table you need to have a row present in the Contacts table unless you allow inserts into Accounts with a null PrimaryContactID.  In order to create a contact row without already having an Account row present, you must allow the AccountID column in the Contacts table to be nullable.  This allows Accounts to have no contacts, and allows Contacts to have no account.  Perhaps this is desirable, perhaps not.

Having said that, my personal preference would be to have the following setup:

CREATE TABLE dbo.Accounts
(
    AccountID INT NOT NULL
        CONSTRAINT PK_Accounts
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , AccountName VARCHAR(255)
);

CREATE TABLE dbo.Contacts
(
    ContactID INT NOT NULL
        CONSTRAINT PK_Contacts
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ContactName VARCHAR(255)
);

CREATE TABLE dbo.AccountsContactsXRef
(
    AccountsContactsXRefID INT NOT NULL
        CONSTRAINT PK_AccountsContactsXRef
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , AccountID INT NOT NULL
        CONSTRAINT FK_AccountsContactsXRef_AccountID
        FOREIGN KEY REFERENCES dbo.Accounts(AccountID)
    , ContactID INT NOT NULL
        CONSTRAINT FK_AccountsContactsXRef_ContactID
        FOREIGN KEY REFERENCES dbo.Contacts(ContactID)
    , IsPrimary BIT NOT NULL
        CONSTRAINT DF_AccountsContactsXRef
        DEFAULT ((0))
    , CONSTRAINT UQ_AccountsContactsXRef_AccountIDContactID
        UNIQUE (AccountID, ContactID)
);

CREATE UNIQUE INDEX IX_AccountsContactsXRef_Primary
ON dbo.AccountsContactsXRef(AccountID, IsPrimary)
WHERE IsPrimary = 1;

This provides the ability to:

Clearly delineate the relationships between contacts and accounts through a cross-reference table the way Pieter recommends in his answer
Maintain referential integrity in a sound, non-circular manner.
Provide for a highly maintainable list of primary contacts through the IX_AccountsContactsXRef_Primary index.  This index contains a filter, so it will only work on platforms that support them.  Since this index is specified with the UNIQUE option, there can only ever be a single primary contact for each account.

For instance, if you want to display a list of all contacts, with a column denoting the "primary" status, showing primary contacts at the top of the list for each Account, you could do:

SELECT A.AccountName
    , C.ContactName
    , XR.IsPrimary
FROM dbo.Accounts A
    INNER JOIN dbo.AccountsContactsXRef XR ON A.AccountID = XR.AccountID
    INNER JOIN dbo.Contacts C ON XR.ContactID = C.ContactID
ORDER BY A.AccountName
    , XR.IsPrimary DESC
    , C.ContactName;

The filtered index prevents insertion of more than a single primary contact per account, whilst simultaneously providing a quick method of return a list of primary contacts.  One could easily imagine another column, IsActive with a non-unique filtered index to maintain a history of contacts per account, even after that contact is no longer associated with the account:

ALTER TABLE dbo.AccountsContactsXRef
ADD IsActive BIT NOT NULL
CONSTRAINT DF_AccountsContactsXRef_IsActive
DEFAULT ((1));

CREATE INDEX IX_AccountsContactsXRef_IsActive
ON dbo.AccountsContactsXRef(IsActive)
WHERE IsActive = 1;


Login failure error log when querying Central Management Server group

I have seen this issue when the default database for your login is set to the wrong database.


MySQL replication - database size is different

The setup can differ slightly in size and be consistent at the same time. Could be a range of reasons for the size difference. E.g. Different file system, fragmentation, method of determining size, etc...

If you want to test the consistency of your replicated tables, run a pt-table-checksum.


I'd have role table that contains "student", "instructor", etc. Then join table would look like user_role(user_id,role_id, fos_id, date_from, date_thru) . Date_from and date_thru may or may not make sense for your task....


Database structure for product attributes on orders

Assuming that the attributes are for records in the tbl_salelines.

If the relationship is such that one salesline can have many attributes, then add a column for salesline_id in the tbl_attributes table.

If the relationship is such that one salesline can have many attributes AND one attribute can have many saleslines, then add an intersect table with attribute_id and salesline_id, where both of these id's are primary keys in their own table and are foreign keys in the intersect table. It may look like this...

select * from tbl_salesline_i_attribute;

SALESLINE_ID  ATTRIBUTE_ID
------------  ------------
800           22
800           89
555           45
764           45

select * from tbl_salesline;

SALESLINE_ID  COLUMN
------------  ------------
800           foo
555           bar
764           foobar

select * from tbl_attribute;

ATTRIBUTE_ID  NAME               EXTRA_PRICE
------------  ------------       -------------
22            blue               3
89            glow-in-the-dark   3
45            extra large        10

In the example above, salesline 800 has two attributes on it (blue and glow-in-the-dark). Likewise, attribute 45 is on two saleslines.


Assign a value to a field of a composite type (a row in an array of rows)?

Yes, this feature is missing in the arsenal of array functions. It's rather exotic, and one would typically operate with a (temporary) table instead of an array of rows.

That said, there is a way with a single assignment, using the hstore #= operator:

CREATE OR REPLACE FUNCTION f_test()
  RETURNS int LANGUAGE plpgsql AS
$func$
DECLARE
   x test_type[] := '{(1)}';
BEGIN
   x[1] := x[1] #= hstore '"some_int"=>"2"'; -- !!!
   RETURN x[1].some_int;
END
$func$;

SELECT f_test();

| f_test |
| -----: |
|      2 |

db<>fiddle here

The additional module hstore must be installed. In-depth explanation:

How to set value of composite variable field using dynamic SQL


I spoke to people much smarter than I and we will be documenting this soon™.

The actual definition of this, in the interim is:

  PartialMatchingIndexCount reflects the number of references that could
  be checked using index seeks, but the index key does not cover all the
  columns being checked. For example, the corresponding
  ForeignKeyReferenceCheck elements contain both a Seek Predicates and a
  Predicate element.

In addition:

  If this number is greater than 0, there is a potential performance issue, in case the partial matches result in large numbers of rows.


Is there a tuning parameter for MySQL that allows you to set an on-disk gap between non-sequential primary keys?

Your understanding is completely wrong, so the question does not really make sense. Rows beyond the value of the point of insertion are not rewritten with insertion. Rows aren't even re-ordered within the page level for insertion; insertion happens into empty space within the page, and the rows are linked together in ascending order through a singly-linked list.

It may be informative to check out this series of blog posts I wrote: http://blog.jcole.us/innodb/


Copying all columns of a row from table into another?

You can - you need to drop the column aliases from the INSERT list as below

CREATE TABLE SourceTest (id int, characters nvarchar(100))

INSERT INTO SourceTest (id, characters)
VALUES
(1,'hello'),(2,'goodbye'),(3,'test')

CREATE TABLE TargetTest (id int, characters nvarchar(100));

INSERT INTO TargetTest
SELECT * FROM SourceTest

SELECT * FROM TargetTest

DBFiddle

NOTE: The columns of both tables must be in the same order, and if the schema of the source table is modified the schema of the target table must be amended also.

DISCLAIMER: Using SELECT * in production code is seen as an anti-pattern - changes to column order or table structure can cause errors. I would recommend NOT using this method.


How to create trigger on database that 'triggers' when mirroring role changes from mirror to principal

Create an event notification for the Database Mirroring State Change. See Implementing Event Notifications for how to design a service that processes the notification and does whatever is appropriate. Keep in mind that event notifications are asynchronous.


Get Insert/Delete/Update operations with data from the transaction log

Can anyone please explain the script to find Insert/Delete/Update operations with data (like cdc) that are made to the sql server from transaction log.

Free ones of this would be a program and they are generally paid for. There are no free ones that I know of. Additionally the SQL Server Transaction Log is not documented as it is for internal use only.

  I cant enable cdc since I am using SQL server 2012 standard edition.

Then either change your edition or go without. You can write your own tracking using triggers, but it's going to make your application run extremely slow.

You really need to look into items that do this, Golden Gate, CDC, CT, etc., and go from there. If you can't spend any money then you need to make a business justification case for it and if still nothing, you can write it using triggers. Note that using triggers is still spending money on development... so either way money is being used.


Can't create a view for other user's table despite having select privilege

SCOTT will have the ability to expose the new view to other users, thus indirectly granting data access on HR to other users. SCOTT therefore needs WITH GRANT OPTION on the grant.

GRANT SELECT ON employees TO scott WITH GRANT OPTION;


UNIQUE constraint on large VARCHARs - PostgreSQL

As suggested by @Josh Kupershmidt and @JoeNahmias the solution is to use UNIQUE on md5 hash of the long value. However PostgreSQL 9.3 doesn't support expressions in UNIQUE constraints so an index, that supports expressions, has to be used:

create unique index unique_data_url_index on mytable (md5(data_url));


why do I have three copies of data/log files per database on same server instance

It is possible that your databases were set up this way. To find out you can run this query:

SELECT DATABASE_NAME(database_id) DbName,* FROM sys.master_files ORDER BY 1;

It lists all files that belong to each of your databases.

If the databases in question list only one file each, then something outside of SQL Server is creating these copies.

Otherwise each database is just split into multiple files and actually taking up that space. In that case you can really only either delete databases or increase the disk size.

It can have performance benefits to split a database into multiple data files. However, splitting the log into multiple files is usually unnecessary as SQL Server is writing always only to one of them at a time.


You want query looking like this one:

SELECT foo.table1, bar.table2, table3.abc, table4.xyz
  FROM table1
 LEFT OUTER JOIN table2
  ON table1.id2 = table2.id
 LEFT OUTER JOIN table3
  ON table1.id3 = table3.id
 JOIN table4
  ON table1.id4 = table4.id
 WHERE table1.foo BETWEEN 10 AND 30

It's in SQL JOIN syntax, so You might want to check some tutorial or course, like one from CodeAcademy. Another great source helpful with learning about joins is Jeff Atwood's visual explanation of sql joins.

Just like @a_horse_with_no_name and @Vladimir_Oselsky commented, You should switch from old ANSI-89 SQL syntax to current ANSI-92, which allows for easier to write (and read) queries with more flexible JOIN syntax.


Convert Text Column Web Service Data to Columns

dbfiddle

There surely are, a lot of, Oracle experts who can write more optimized code (in Oracle) than this fiddle....


How to revive SQL Server 2008 R2 performance counters?

Have a try with lodctr /R, it will rebuild all performance counters from informations stored in registry.


incorrect result when filtering by association

You need one more join (a LEFT join with IS NULL check) or a NOT EXISTS subquery and to move the condition (reversed!) there:

SELECT * FROM dockets
INNER JOIN entries
ON entries.docket_id = docket.id
WHERE NOT EXISTS
      ( SELECT 1
        FROM entries AS e
        WHERE e.docket_id = docket.id
          AND e.docket_type LIKE '%PENDING%'
      ) ;


How to send secure emails using sp_send_dbmail

I don't think SQL Server has any built-in functionality to achieve what you're after.

One possible solution is to use a local SMTP server as a relay, so that the email is not encrypted on the initial hop (between SQL Server and the local SMTP server), but it gets encrypted leveraging the SMTP server's functionalities on the second hop (to the outbound SMTP server).


You could use WHERE NOT EXISTS to check new values before insert a new record.

INSERT INTO

(
    field1, field2, field3
)
SELECT value1, value2, value3
FROM dual
WHERE NOT EXISTS (SELECT 1
                  FROM

                  WHERE <pk fields> = <new values>);

CREATE TABLE foo(id int, v1 int, v2 int);
INSERT INTO foo VALUES (1, 100,100);
INSERT INTO foo VALUES (2, 200, 200);

This record exists and should not be inserted:

INSERT INTO foo (id, v1, v2)
SELECT 1, 101, 101
FROM   dual
WHERE NOT EXISTS (SELECT 1 FROM foo WHERE id=1);

This is a new record:

INSERT INTO foo (id, v1, v2)
SELECT 3, 300, 300
FROM   dual
WHERE NOT EXISTS (SELECT 1 FROM foo WHERE id=3);

The final result:

SELECT * FROM foo;

id |  v1 |  v2
-: | --: | --:
 1 | 100 | 100
 2 | 200 | 200
 3 | 300 | 300

dbfiddle here


Reading from subscriber during Transactional Replication

are there any restrictions around selecting data from the subscriber whilst transactions are being applied to it?

There are no restrictions for selecting data on subscriber side.

  Will performance of these selects suffer whilst transactions are being applied?

Yes, depending on the frequency of the log-reader agent there might be blocking when the replicated transactions are being applied to subscriber.

If your publication database is very active - generating lots of changes that needs to be applied to subscriber, I would suggest to change the database option to SET READ_COMMITTED_SNAPSHOT ON for publication and subscription databases. TEST IT before implementing in PROD !!

As a side note:

publish only subset of tables that are required rather than the entire database.
Use PULL subscription when there are large number of articles as the agent will run on subscriber side and this will alleviate load on distribution.

Refer to : Enhance General Replication Performance


Function returning table : access individual fields

You are right, the function will be called for each column if it used in the select clause even if it declared as immutable. The simple example:

drop function if exists foo(int);
create function foo(x int, out a int, out b text) returns record immutable language plpgsql as $$
begin
  raise info 'foo() called';
  a := x*2;
  b := 'x';
  return;
end $$;

explain (analyse, verbose)
  select *, (foo(t.x)).*
  from (select 2 as x) as t;

INFO:  foo() called
INFO:  foo() called
┌────────────────────────────────────────────────────────────────────────────────────┐
│                                     QUERY PLAN                                     │
╞════════════════════════════════════════════════════════════════════════════════════╡
│ Result  (cost=0.00..0.51 rows=1 width=0) (actual time=5.189..5.190 rows=1 loops=1) │
│   Output: 2, (foo(2)).a, (foo(2)).b                                                │
│ Planning time: 0.087 ms                                                            │
│ Execution time: 5.239 ms                                                           │
└────────────────────────────────────────────────────────────────────────────────────┘

There are several notes:

If your function is not SRF, you could to declare it as returns record and use out parameters to give the names to the returning fields;

I used shorthand to retrieve all fields from the function.

The right way is to use cross join:

explain (analyse, verbose)
  select *
  from (select 2 as x) as t cross join lateral foo(t.x);

INFO:  foo() called
┌──────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                              QUERY PLAN                                              │
╞══════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Function Scan on nd.foo  (cost=0.25..0.26 rows=1 width=36) (actual time=0.534..0.535 rows=1 loops=1) │
│   Output: 2, foo.a, foo.b                                                                            │
│   Function Call: foo(2)                                                                              │
│ Planning time: 0.114 ms                                                                              │
│ Execution time: 0.611 ms                                                                             │
└──────────────────────────────────────────────────────────────────────────────────────────────────────┘

Or for your case:

WITH dc AS (
  SELECT iddyncondition, id, weight AS dcrec
  FROM dynconditions CROSS JOIN LATERAL dynamiccondition_byid(iddyncondition,'')
)

Update: following to the comment instead of cross join lateral you also can use: t cross join foo(t.x), t, lateral foo(t.x) or just t, foo(t.x) - all will works in same way. But I recommend to use at least cross join form for that case if some other tables will be involved in the query which makes it more clear.


For Microsoft SQL Server, the lowest-intrusive tool is Performance Monitor, aka Perfmon.  Here's my tutorial on grabbing Perfmon counters for SQL Server and analyzing them:

http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/


Repeat row in the result based on the value of a column

If you are using Oracle 12c or higher, you can CROSS APPLY with a CONNECT BY LEVEL query generating the numbers from 1 to VAL_COL. Something like this (I don't have an instance ready and SQLFiddle uses 11g, which doesn't support this):

SELECT FIRST_COL, VAL_COL
FROM table1
CROSS APPLY (
  SELECT LEVEL
    FROM DUAL
    CONNECT BY LEVEL <= table1.VAL_COL
);

If you are on 11g or higher, you can use Recursive Refactored Subquery.

with data as (
  -- example data
  select 'A' FIRST_COL, 2 VAL_COL from dual union all
  select 'B' FIRST_COL, 3 VAL_COL from dual union all
  select 'C' FIRST_COL, 4 VAL_COL from dual
), counter(first_col,val_col, iterator) as (
  select first_col, val_col, val_col
  from data
  union all
  select first_col, val_col, iterator - 1
  from counter
  where iterator - 1 > 0
)
select first_col, val_col
from counter
order by first_col;

Prior to 11g, you would need to use a pipeline function


Should future time be stored in UTC or local time?

In general I agree with your appraisal. When a time is going to be used under any of the following circumstances using local time only is appropriate:

There is some other bit of context that makes the time zone obvious or irrelevant.

The time is going to only be used in reference to a local event like a concert. Since I know the concert is taking place in venue X it would be weird if the time wasn't in the time zone for venue X. Similarly it would be weird if my doctor's appointment was made based on a different time zone than the location of the appointment.
I shouldn't have to set a time zone to get the alarm on my phone to go off at 6 o'clock in the morning. I should be able to say 6 am, and the phone should wake me up everyday at 6 am local time. Note I'm just talking about a basic alarm like you would have with a clock radio. I'm not talking about scheduling a meeting/appointment with people in different time zones.

For some reason the fact that X occurs on a regular interval is more important that X occurs at a specific time.

Something needs to happen once a day and it doesn't really matter when it happens. Usually you would like to do some scheduled task during non-peak hours, but it is conceivable that this isn't always important.

Assuming the above is true then I'd fully support using local time. You didn't mention the database platform, but if your database platform supports it I think an event that isn't tied to a date should be stored in a pure time data type, assuming your database platform supports/offers this data type. For instance in SQL Server, I'd use the Time data type to store the hours of operation for a store. Using SQL Server once again, I'd use the DateTime2 data type to store the date and start time for a concert.

Since we're on the subject I personally would use UTC time on the server for everything that doesn't fit in the "local time only" parameters I outlined above, like you stated in your question. Many database platforms offer an analog to SQL Server's DateTimeOffset but this isn't really a safe solution because you end up losing the actual time zone information in lieu of the offset, and offsets change. For a more detailed overview on this and other time related issues, from a .NET point of view, check out this article written by Jon Skeet.


ERROR: relation "codepool_codepoolseq_seq" does not exist SQL state: 42P01

In your Scheme you must have Sequences called codepool_codepoolseq_seq

Example create:

-- SEQUENCE: public.codepool_codepoolseq_seq

-- DROP SEQUENCE public.codepool_codepoolseq_seq;

CREATE SEQUENCE public.codepool_codepoolseq_seq
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE 1;

ALTER SEQUENCE public.codepool_codepoolseq_seq
    OWNER TO postgres;


Recovering data from SQLite .db File Data

Community wiki answer:

Is it possible that the text appears in deleted rows whose disk space has not yet been "recycled" or "garbage collected".  If you vacuum the database and check again with grep, the text should be gone.

  I now need to find the best way to attempt to recover instead of vacuuming.

To recover the data, instead of vacuuming, make a copy of the database as it is now, look for a hex editor, and see what you can salvage by hand.

See also Undelete accidentally deleted records in Sqlite3 on Stack Overflow.


Data Sync between servers with different schema in SQL Server

It is not quite clear what you are asking. It seems you are looking for a solution that would replicate data changes as well as schema changes from the 2005 server to the 2008R2 server.

This can be achieved with SQL Server Replication. Transactional Replication is able to move all data changes as well as all schema changes from a single source system to one or more target systems. The systems do not need to be at the same version of sql server for this to work (http://technet.microsoft.com/en-us/library/ms143241(v=sql.105).aspx)

For an introduction to transactional replication check out my Replication Stairways article series.

If this does not answer your question, please try to clarify what you are looking for.


Are database backups specific to the OS on which they are created?

In Oracle, you can restore onto an OS/CPU with the same endianness in most cases. This can be done with RMAN, the standard Oracle backup and restore tool, but it might be worth it to  investigate options such as DataPump or a Linux VM on AIX (but I don't know how that would deal with endianness). x86 is little-endian and POWER is big-endian, so this would not necessarily be straightforward.


For SQl Server 2008 you can use convert function like below.

--For output format mm-dd-yyyy
declare @d datetime = getdate()
select convert(varchar(10), @d, 110)

--For output format yyyy-mm-dd
declare @d datetime = getdate()
select convert(varchar(10), @d, 120)

If you are using SQL Server 2012 and later, the new format function works fine too. Example below.

--For output format mm-dd-yyyy
declare @d datetime = getdate()
select format(@d,'MM-dd-yyyy', 'en-US')

--For output format yyyy-mm-dd
declare @d datetime = getdate()
select format(@d,'yyyy-MM-dd', 'en-US')


Can SQL Server's Page Life Expectancy be too high?

No, I can't think of any situation or theory in my head that could illustrate a negative side effect for an astronomically high PLE (unless you enjoy the humming sound of the platters in a hard disk drive?).

With memory getting cheaper and cheaper, and buyers-of-hardware getting a little more generous, these bigger memory boxes we see it is quite common to have PLE in the thousands upon thousands range.  It's simply one indication that your instance may not be under memory pressure.  Take it for what it is, and only that.

Page Life Expectancy is just an estimation SQL Server makes on how long it thinks a page will last in a buffer.  This is SQL Server's way of saying it thinks it'll be "a while".

Another thing of note, take Paul Randal's advice and don't concentrate on the PLE reported by the buffer manager.  Like all averages, the buffer manager can hide differing PLEs.  You want to look at all of the individual NUMA nodes' PLEs with the Buffer Node counter:

Get-Counter -ListSet "*" |
    Select-Object -ExpandProperty Counter |
    Where-Object {$_ -like "*buffer node*page life expectancy*"} |
    Get-Counter

Page Life Expectancy is simply one color in the beautiful painting that is SQL Server memory.  Look at it along with the other "colors" (target/total server memory, available mbytes, etc.).  Too often people look at one simple metric and fear either their server is about to explode, or conversely that it'll live for centuries unmanaged.


Check if there are users who are using a common password

SELECT a.userid AS TheAdmin,
       GROUP_CONCAT(b.userid) AS NonAdminsWithSamePwd
    FROM tbl AS a
    JOIN tbl AS b  ON b.password = a.password
    WHERE a.adminlevel > 0
      AND b.adminlevel = 0


I didn't try this but just giving an approach I would follow.

The log reader scans all the log records and keeps it in distributor DB. The subscriber reads the records from the distributor DB, then inserts it in subscribers and marks the last record it read.

Considering the above topology, I don't see any issues with moving the distributor DB. Assuming you are moving the publisher(push) or subscriber(pull) involved and the server name remains the same.

In a Push model, the distributor DB resides in publisher and in pull model, the distributor resides in the subscriber DB.

Replication stores all the server information for all publishers, distributors, and subscribers in the database. So if you go with this approach I would suggest the below steps:

Stop all jobs (log reader, distributor)
Move all databases(attach, detach) to new server with same server name

The only issue I see here is the instance name being the same as the old one, which can be tricky. You also can try testing this yourself with a single table replication and with the same setup.


Why there is no much change in the incremental backup size of level 0 and level 1

But in my db after level 0 full backup, level 1 backup is also taking
  the almost same backup size of level 0.

Verify if the backup generated in level 0 exists there in the location, while level 1 backup occurs.

The level 0 backup might have got deleted or removed from the location. This might be the reason for this issue.


MySQL query to search string in Master-Child tables and return all rows with Master-Child relation

SELECT  t1.*,
        t2.*

FROM    Table1 AS t1
    LEFT JOIN
        Table2 AS t2
            ON WHERE t2s.FK_id = t1.PK_id

WHERE   t1.Table1_Title = 'search text'
   OR   EXISTS
        ( SELECT  *
          FROM    Table2 AS t2s
          WHERE   t2s.FK_id = t1.PK_id
            AND   t2s.Table2_Title = 'search text'
        ) ;


Convert yymmdd to datetime using ssis derived column

Data Flow

Here's the general approach I'd take to solving your problem.

I started with your source data and added some other conditions - a NULL as well as a 14 and 15 year to ensure my logic later is correct.

SELECT
    D.DrvDOB
FROM
(
    VALUES
    ('470324')
,   ('470324')
,   ('470209')
,   ('140209')
,   ('150209')
,   ('101')
,   ('0')
,   (NULL)
) D(DrvDOB);

This is going to seem like a lot of work, but for me, this is the most maintainable approach. If something's wrong, I can simply inspect between the derived columns and find the failing bit of logic. Many people mistakenly believe the amount of components on a data flow decrease the processing time. The reverse case can actually be true, see the sqlblog post at the end for more details.

Create a column called IsValidLength This is going to provide us a boolean to tell us whether we even need to try and parse the year, month and day from the string. I'm ensuring we don't have a NULL value and that the length is 6. Otherwise, we're going to flag as invalid

!IsNull([DrvDOB]) && LEN([DrvDOB]) == 6

I then am going to generate 3 more string columns (der_Year, der_Month, der_Day) with the parsed year, month and day. Otherwise, I'm going to use 00 as my value. Again, I only attempt to parse the string if it's valid.

(IsValidLength) ? SUBSTRING([DrvDOB], 1, 2) : "00"
(IsValidLength) ? SUBSTRING([DrvDOB], 3, 2) : "00"
(IsValidLength) ? SUBSTRING([DrvDOB], 5, 2) : "00"

Now comes the actual fun part, determining the actual year. Since your field is called date of birth, I'm assuming there are no future dated records - my apologies if you have psychic data. I took the route SQL Server, .NET library and COM use and have a prescribed year to use as the marker between what gets tagged as 19XX vs 20XX. I create an SSIS Variable called TwoDigitYearCutoff and assign it a value of 2014. In short, any years that are parsed from 00 to 14 will be tagged with the 21st century, while the rest are left in the 20th century. Adjust this value as needed.

Back in my data flow, I create yet another column, this one der_ActualYear which will be the 4 digit year.

(@[User::TwoDigitYearCutoff] / 100
- ((DT_I4)der_Year > @[User::TwoDigitYearCutoff] % 100 ? 1 : 0))
* 100 + (DT_I4)der_Year

At this point, we know the full year, the month and day along with whether this data is any good. The final piece then is to put it all together and make it an actual Date. I build out a YYYY-MM-DD string and cast that as a date. Otherwise, I cast NULL as a date (to get my metadata consistent)

(IsValidLength) ? (DT_DATE) ((DT_WSTR, 4) [der_ActualYear]
+ "-" + [der_Month] + "-" + [der_Day]) : NULL(DT_DATE)

You can now wire der_DrvDOB into your target destination knowing that you have a valid date or a NULL.

Results

As expected, valid dates or null

Biml

Biml, the Business Intelligence Markup Language, describes the platform for business intelligence. Here, we're going to use it to describe the ETL. BIDS Helper, is a free add on for Visual Studio/BIDS/SSDT that addresses a host of shortcomings with it. Specifically, we're going to use the ability to transform a Biml file describing ETL into an SSIS package. This has the added benefit of provding you a mechanism for being able to generate exactly the solution I'm describing versus clicking through many tedious dialogue boxes.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <!-- 74383 -->
    <Connections>
        <OleDbConnection ConnectionString="Provider=SQLNCLI11;Data Source=localhost\dev2014;Integrated Security=SSPI;Initial Catalog=tempdb" Name="CM_OLE" />
    </Connections>
    <Packages>
        <Package Name="dba_74383" ConstraintMode="Linear">
            <Variables>

                <Variable DataType="Int32" Name="TwoDigitYearCutoff">2014<Annotations>
                    <Annotation AnnotationType="Description">Use the two digit year cutoff to specify an integer from 1753 to 9999 that represents the cutoff year for interpreting two-digit years as four-digit years</Annotation>
                </Annotations>
            </Variable>
            </Variables>
            <Tasks>
                <Dataflow Name="DFT Make Dates">
                    <Transformations>
                        <OleDbSource ConnectionName="CM_OLE" Name="OLE_SRC Gen data">
                            <DirectInput>SELECT
    D.DrvDOB
FROM
(
    VALUES
    ('470324')
,   ('470324')
,   ('470209')
,   ('140209')
,   ('150209')
,   ('101')
,   ('0')
,   (NULL)
) D(DrvDOB);</DirectInput>
                        </OleDbSource>
                        <!--
                            Test whether we have minimally valid data
                        -->
                        <DerivedColumns Name="DER IsValidLength">
                            <Columns>
                                <Column Name="IsValidLength" DataType="Boolean"><![CDATA[!IsNull([DrvDOB]) && LEN([DrvDOB]) == 6 ]]></Column>
                            </Columns>
                        </DerivedColumns>
                        <!--
                            Extract the year
                        -->
                        <DerivedColumns Name="DER ExtractYear">
                            <Columns>
                                <Column Name="der_Year" DataType="String" Length="2">(IsValidLength) ? SUBSTRING([DrvDOB], 1, 2) : "00"</Column>
                            </Columns>
                        </DerivedColumns>
                        <!--
                            Extract the Month
                        -->
                        <DerivedColumns Name="DER ExtractMonth">
                            <Columns>
                                <Column Name="der_Month" DataType="String" Length="2">(IsValidLength) ? SUBSTRING([DrvDOB], 3, 2) : "00"</Column>
                            </Columns>
                        </DerivedColumns>

                        <!--
                            Extract the day
                        -->
                        <DerivedColumns Name="DER ExtractDay">
                            <Columns>
                                <Column Name="der_Day" DataType="String" Length="2">(IsValidLength) ? SUBSTRING([DrvDOB], 5, 2) : "00"</Column>
                            </Columns>
                        </DerivedColumns>

                        <!--
                            Guess at year based on cutoff date. Assume historical data and not future dated
                            since this is DOB. For supplied value of 2014,
                            if 14 is seen, it will be 2014
                            if 15 is seen, it will be 1915

                            http://technet.microsoft.com/en-us/library/ms189577(v=sql.105).aspx
                            A two-digit year that is less than or equal to the last two digits of the cutoff year is
                            in the same century as the cutoff year. A two-digit year that is greater than the last
                            two digits of the cutoff year is in the century that precedes the cutoff year. For example,
                            if two digit year cutoff is 2049 (the default), the two-digit year 49 is interpreted as 2049
                            and the two-digit year 50 is interpreted as 1950.

                            https://stackoverflow.com/questions/2024273/convert-a-two-digit-year-to-a-four-digit-year
                            this.TwoDigitYearMax / 100 - (year > this.TwoDigitYearMax % 100 ? 1 : 0)) * 100 + year

                            @[User::TwoDigitYearCutoff]) / 100 - ((Int32) [der_Year] > @[User::TwoDigitYearCutoff]) % 100 ? 1 : 0)) * 100 + (Int32) [der_Year]
                        -->
                        <DerivedColumns Name="DER Guess at year">
                            <Columns>
                                <Column DataType="Int32" Name="der_ActualYear"><![CDATA[(@[User::TwoDigitYearCutoff] / 100 - ((DT_I4)der_Year > @[User::TwoDigitYearCutoff] % 100 ? 1 : 0)) * 100 + (DT_I4)der_Year]]></Column>
                            </Columns>
                            <Annotations>
                                <Annotation AnnotationType="Description">This might be overkill, but it sure was fun</Annotation>
                            </Annotations>
                        </DerivedColumns>

                        <!--
                            Put it all together
                        -->
                        <DerivedColumns Name="DER Make valid date">
                            <Columns>
                                <Column Name="der_DrvDOB" DataType="Date">(IsValidLength) ? (DT_DATE) ((DT_WSTR, 4) [der_ActualYear] + "-" + [der_Month] + "-" + [der_Day]) : NULL(DT_DATE) </Column>
                            </Columns>
                        </DerivedColumns>

                        <DerivedColumns Name="DER bitbucket">

                        </DerivedColumns>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

References

http://technet.microsoft.com/en-us/library/ms189577(v=sql.105).aspx
https://stackoverflow.com/questions/2024273/convert-a-two-digit-year-to-a-four-digit-year
http://web.archive.org/web/20111227015148/http://sqlblog.com/blogs/jamie_thomson/archive/2011/01/12/investigation-can-different-combinations-of-components-effect-dataflow-performance.aspx


How get the sum of two tables value using inner join

This may not be the best way to do this but it will work.

SELECT c.ClientNo, c.ClientId, c.FullName, i.InvoiceTotalDue, i.InvoiceTotalBalance, r.ReturnChequeTotalDue, r.ReturnChequeTotalBalance, (i.InvoiceTotalBalance + r.ReturnChequeTotalBalance) AS TotalBalance
FROM Client_TBL c
LEFT JOIN (
SELECT ClientNo, SUM(TotalDue) AS InvoiceTotalDue, SUM(TotalBalance) AS InvoiceTotalBalance
FROM Invoice_TBL
GROUP BY ClientNo
) AS i ON c.ClientNo = i.ClientNo
LEFT JOIN (
SELECT ClientNo, SUM(TotalDue) AS ReturnChequeTotalDue, SUM(TotalBalance) AS ReturnChequeTotalBalance
FROM ReturnCheque_TBL
GROUP BY ClientNo
) AS r ON c.ClientNo = r.ClientNo

And of course you could change the subqueries into CTEs.  But basically you are going to collect the data pre join since otherwise you are probably going to get bad data (Client 2 for example will have Invoice info that is twice as large and ReturnCheque that is 3 times as large.)


SQL - find records that are prefixes of my string

First, your way of using [I]LIKE is wrong:

string LIKE pattern

So, string first, pattern second.

Furthermore, as you are after prefixes only, the starting wildcard is not needed.  Something like this should work:

SELECT * FROM my_table WHERE 'aaaabbbbccccdddd' ILIKE prefix || '%';

A small proof of this:

WITH my_table (prefix) AS (
VALUES ('abcde'),
       ('abcd'),
       ('abcE'),
       ('bcde')
)
SELECT * FROM my_table WHERE 'abcdefghijkl' ILIKE prefix || '%';

 prefix
────────
 abcde
 abcd


Which SQL Server Express edition is right for me?

Backup are automated via SQL Agent jobs in SQL Server.  Since no express edition includes the SQL Server Agent, then you would have to find a way to have your application initiate the backups. Otherwise, you need standard edition.

What other features are you using?  Some of them may require enterprise or standard.  Personally when I was doing a lot of development, I would try to use an edition that matched what an average target user would have.  So If I want them to have SQL Express, then that is what I used for my development.


Speed up data transfer from db to application

During work hours, take a look at sys.dm_exec_requests and see what the wait_type column says.  This will tell you what the requests are waiting for.

Right before working hours, you could run DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR); and then look at sys.dm_os_wait_stats to see the accumulated stats during the day.

Do you have indexes defined on any of these tables?  If not, then table scans might be clogging your I/O system, and locks might be causing some blocking.

If people are running SQL Profiler traces during work hours, those could be slowing down the entire system.  You could detect those by quering sys.traces.

Running a careful profiler trace yourself might reveal some interesting facts about this query.  Try capturing a Showplan XML Statistics Profile event when it runs.  Maybe the query processor is choosing a very bad query plan.  Maybe the plan is generating intermediate tables that have a ridiculous number of records, or maybe there is a bad nested loop that would work better with an index or with a merge join.

Since this is an external server, another possibility is that you are simply overwhelming network bandwidth during working hours.

You mentioned in a comment that there was a lot of data coming back.  Adding some filters in the query might help-- if that is an acceptable solution for your application.


Select rows with most recent date for the given date

It's a bit hard to understand the logic of the query from the explanation, but I think you mean you get a date as input and you need the closest by date for each product?

If so, I have a solution, pretty sure not the optimal one, but maybe the logic can lead you in the right direction:

SELECT t.id, t.product
FROM table t
JOIN
(
    SELECT
        t.id,
        t.product,
        t.date, MIN(ABS(UNIX_TIMESTAMP('yourdate') - UNIX_TIMESTAMP(t.date))) AS diff
    FROM table t
    GROUP BY t.product
) x ON x.diff = ABS(UNIX_TIMESTAMP('yourdate') - UNIX_TIMESTAMP(t.date))
AND x.product = t.product