Unable to mirror a database SQL Server 2012

Its easy to repro the error that you got

Create database in full recovery mode on Primary.
Create database in full recovery mode in Secondary.
Launch GUI and try to configure mirroring between Primary and Secondary.

Below is the error you will get :

  Database "test_mirroring_kin" might contain bulk logged changes that have not been backed up. Take a log backup on the principal database or primary database. Then restore this backup either on the mirror database to enable database mirroring or on every secondary database to enable you to join it to the availability group. (Microsoft SQL Server, Error: 1475)

Lets understand what that error is :

You configured your database in FULL recovery mode and think that the database is indeed in FULL recovery mode.

The above is not true. After creating the database, if you don't do a FULL backup, even though the database is in FULL recovery mode, it is in pseudo-SIMPLE recovery

You can easily verify it using dbcc dbinfo --> dbi_dbbackupLSN having value of 0:0:0(0x00000000:00000000:0000) or using Paul Randal's script

dbcc traceon (3604)
go
dbcc dbinfo('test_mirroring_kin') with tableresults
go
dbcc traceoff (3604)

Edit: Even taking a first full backup with COPY_ONLY option does not establish a backup chain as well

backup database test_mirroring_kin
to disk = 'D:\test_mirroring_kin_FULL.bak'
with init, stats=10, COPY_ONLY

dbcc dbinfo --> dbi_dbbackupLSN is still having value of 0:0:0(0x00000000:00000000:0000). This means the database is still in pseudo-simple recovery mode.

What you need to do to resolve the above error ?

You need to take a full backup + one transaction log backup on primary and then restore it on secondary database with norecovery and then join the database in AG group or Mirroring.

As a side note and for completeness, for your script telling backup to NUL, read this blog post by Gail Shaw.


identifier 'DBMS_CDC_PUBLISH.CREATE_CHANGE_SET' must be declared

Verifying Packages
To verify that you indeed do have the required packages in Oracle run the following statement:
set pages 50
set lines 230
column owner format a20
column object_name format a30

SELECT DISTINCT Owner, Object_Type, Object_Name FROM DBA_Objects_AE
     WHERE Owner IN (
       'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
       'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
       'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
       'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
       'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
       'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
       'SI_INFORMTN_SCHEMA', 'XDB', 'ODM')

     AND Object_Type IN ('PACKAGE')
     AND Object_Name like 'DBMS_CDC%'
     ORDER BY Owner, Object_Type, Object_Name

This should return something like this:

OWNER                OBJECT_TYPE             OBJECT_NAME
-------------------- ----------------------- ------------------------------
SYS                  PACKAGE                 DBMS_CDC_DPUTIL
SYS                  PACKAGE                 DBMS_CDC_EXPDP
SYS                  PACKAGE                 DBMS_CDC_EXPVDP
SYS                  PACKAGE                 DBMS_CDC_IMPDP
SYS                  PACKAGE                 DBMS_CDC_IMPDPV
SYS                  PACKAGE                 DBMS_CDC_IPUBLISH
SYS                  PACKAGE                 DBMS_CDC_ISUBSCRIBE
SYS                  PACKAGE                 DBMS_CDC_PUBLISH
SYS                  PACKAGE                 DBMS_CDC_SUBSCRIBE
SYS                  PACKAGE                 DBMS_CDC_SYS_IPUBLISH
SYS                  PACKAGE                 DBMS_CDC_UTILITY

11 rows selected.

Package Parameters
If all is well, then you can run the following statement to determine the required inputs for DBMS_CDC_PUBLISH.CREATE_CHANGE_SET:
set pages 50
set lines 250
column object_info format a200

select ao.object_type
  ||' '|| ao.owner ||'.'|| ao.object_name
  || case when ap.procedure_name is not null then
    '.'|| ap.procedure_name
    || case when count(aa.argument_name) > 0 then
      '('|| listagg(aa.argument_name, ',')
        within group (order by position) ||')'
    end
  end as object_info
from all_objects ao
left join all_procedures ap
on ap.owner = ao.owner
and ap.object_name = ao.object_name
and ap.object_id = ao.object_id
left join all_arguments aa
on aa.owner = ap.owner
and aa.object_name = ap.procedure_name
and aa.object_id = aa.object_id
and aa.subprogram_id = ap.subprogram_id
where ao.owner in (
  'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
  'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
  'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
  'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
  'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
  'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
  'SI_INFORMTN_SCHEMA', 'XDB', 'ODM')
and ao.object_type in ('PACKAGE', 'TYPE')
and ao.object_name like 'DBMS_CDC_PUBLISH%'
and ap.procedure_name like 'CREATE_CHANGE_SET%'

group by ao.object_type, ao.owner, ao.object_name, ap.procedure_name,
  ao.object_id, ap.subprogram_id
order by ao.owner, ao.object_type, ao.object_name, ap.procedure_name;

This should return:

OBJECT_INFO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PACKAGE SYS.DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(CHANGE_SET_NAME,DESCRIPTION,CHANGE_SOURCE_NAME,STOP_ON_DDL,BEGIN_DATE,END_DATE)
1 row selected.

Isues
If you don't get a result, then you are possibly running a version of Oracle that doesn't support the package or you don't have sufficient privileges.
CDC Edition Support
...in synchronous mode is available in Oracle Standard Edition:

Change Data Capture comes packaged with the appropriate Oracle drivers already installed with which you can implement either asynchronous or synchronous data capture. The synchronous mode of Change Data Capture is included with the Standard Edition, but the asynchronous mode requires you have the Enterprise Edition.

Reference Material

How to get a list with description of all dba packages (Stack Overflow)
Introduction to Oracle Supplied PL/SQL Packages & Types  (Oracle | Docs)
DBMS_CDC_PUBLISH (Oracle | Docs)
Change Data Capture (Oracle | Docs)


It's not a modified version of your query, but I wrote this a while back (backupset is one of my favorite tables) that serves your purpose.

--When was the last full and where is it?
select
    bs.database_name
    ,bs.backup_finish_date
    ,bs.backup_size/1024.0/1024.0 [backup_size_mb]
    ,bs.compressed_backup_size/1024.0/1024.0 [compressed_size_mb]
    ,datediff(ss,backup_start_date,backup_finish_date) [backup_time]
    ,((bs.backup_size/1024.0/1024.0)/(datediff(ss,backup_start_date,backup_finish_date)+1)) [mb_per_second]
    ,1.0-(bs.compressed_backup_size*1.0/bs.backup_size) [compression_ratio]
    ,bm.physical_device_name
from
    msdb.dbo.backupset bs
    join msdb.dbo.backupmediafamily bm on (bs.media_set_id = bm.media_set_id)
    join (select database_name
            ,max(backup_finish_date) last_backup
          from msdb.dbo.backupset
          where type = 'D'
          group by database_name) lb on (bs.database_name = lb.database_name and bs.backup_finish_date = lb.last_backup)
where type = 'D'


SQL Server 2008 Containstable generate negative rank with weighted_term

Qualifier: Since I can't recreate this, I'm unable to know for sure if this will fix the problem.  However, these are some things that I'm seeing.

First off, the ampersand, pound sign, and semicolon are word-break characters.  That means, that instead of searching for the string "p&#233;toncle", what you're actually searching for is "p", "233", and "toncle".  Clearly, that's not your intent.

I have to presume that you have the text "p&#233;toncle" somewhere in your dataset.  That means you need that entire string to be complete.

There are a few things you can do.

1)  Turn off Stopwords all together.  You can do that by altering the full text index to turn it off.

Note that you have to have your database set to SQL Server 2008 compatability for this to not generate a syntax error:

ALTER FULLTEXT INDEX ON Table1 SET STOPLIST OFF;

2) Create a new stoplist

If you create an empty StopList, you might be able to add the stopwords that you want or copy the system stoplist and remove the stopwords that you don't want.  (I would advise the second approach).

Having said that, I wasn't able to find the & or # in the system stoplist, so they may be hard coded.  You may have to simply turn the stoplist off.

3) Change your search to ignore the "p&#233;toncle" case.

If you drop the "p&#233;toncle" from the ISABOUT and change them to "p toncle", it might work:

'ISABOUT("pétoncle" weight (.8), "p toncle" weight (.8))'

Those are just some ideas.  Like I said, without being able to access the system or recreate the scenario, we won't be able to help much.

Some more information for your researching pleasure:

Stopwords and Stoplists
Alter Fulltext Index syntax
FullText search using Thesaurus file and special characters


This does not seem wrong. Though because most people define private keys as their clustered indexes by default unless they have specific reason to chose something else as the clustering key, it might be unusual. It very much depends on the other indexes that are in place, and how the table is used by your application(s).

If a query can be entirely satisfied using a non-clustered index, then there is not need to query the clustered index (or heap, if there is no clustered index defined) for extra information. If I have a table T with columns a, b, c, d, e, f, g, h, i, with a being defined as the primary key and there being a non-clustered index on b,c,d, the query SELECT b,c,d FROM T WHERE b='some-value' ORDER BY b,c,d can be satisfied by the index and nothing else needs to be referred to. On the other hand SELECT * FROM T WHERE b='some-value' ORDER BY b,c,d will need to refer to the data pages (and therefore the primary key if it is the clustered index for this table) in order to read the columns other than b, c, d, and a.

Also the primary key might not be the clustering key. In this case even if another index is used that is not entirely sufficient so that other information from the main data pages is needed, the clustered index (or heap if there is no clustered index) will be used not the primary key's index.

So if you have a sufficient selection of covering indexes defined, it might be the case that is it rare for your queries to need to touch the clustered index, and if your PK isn't the clustered index it might not be touched even if there are no other useful indexes for a statement to use.

In the example that you give, the index does see some action. It could also be that this table is usually write-only and only occasionally read from. This is not uncommon for logging and auditing tables where data is constantly written as users take actions but is only read once in a while when someone runs off an audit report.


Create column for values between a range sql

I figured out the answer to my question. The below works fine

select
case when num_of_extents between 0 and 9 then a.tabname end as one,
case when num_of_extents between 10 and 19 then a.tabname end two,
case when num_of_extents between 20 and 29 then a.tabname end three,
case when num_of_extents between 30 and 39 then a.tabname end four,
case when num_of_extents between 40 and 49 then a.tabname end five,
case when num_of_extents >= 50 then a.tabname end six
from (
select
a.tabname,
count(*) num_of_extents,
sum( pe_size ) total_size_pg,
round(sum( pe_size*4),2) total_size_kb,
round(sum( pe_size*4/1024 ),2) total_size_mb,
round(sum( pe_size *4/1024/1024),2) total_size_gb
from systabnames a, sysptnext b, sysdatabases c
where a.partnum = b.pe_partnum
and a.dbsname = c.name
and a.dbsname = 'system'
-- a.tabname not like ' %'
group by 1
order by 3 desc, 4 desc
) a group by 1,2,3,4,5,6

Output shows like below within dbaccess:

one
two    bc_storage_idx2
three
four
five
six

one
two
three
four   tock_mvmt
five
six

one
two
three  rd_stats
four
five
six

one
two    tock_mvmt_idx1
three
four
five
six
...

Please note that I did not use the same table data as above. The above is just an example of how the information will be presented within dbaccess. Due to the number of columns formatting it will not be able to display as needed, but can be filtered when outputting to spreadsheet when extracting.

The below formatting would work if I mess around with my environment variables within AIX:

       select
       (case when num_of_extents between 0 and 9 then a.tabname end)[2,20] as one,
       case when num_of_extents between 10 and 19 then a.tabname[2,20] end two,
       case when num_of_extents between 20 and 29 then a.tabname[2,20] end three,
       case when num_of_extents between 30 and 39 then a.tabname[2,20] end four,
       case when num_of_extents between 40 and 49 then a.tabname[2,20] end five,
       case when num_of_extents >= 50 then a.tabname[2,20] end six
from (
select
a.tabname,
count(*) num_of_extents,
sum( pe_size ) total_size_pg,
round(sum( pe_size*4),2) total_size_kb,
round(sum( pe_size*4/1024 ),2) total_size_mb,
round(sum( pe_size *4/1024/1024),2) total_size_gb
from systabnames a, sysptnext b, sysdatabases c
where a.partnum = b.pe_partnum
and a.dbsname = c.name
and a.dbsname = 'system'
-- a.tabname not like ' %'
group by 1
order by 3 desc, 4 desc
) a group by 1,2,3,4,5,6

I don't know how to make the section visible in bold on stackexchange, but see [2,20], that will be used for format.


How many bytes does character takes if I created database with utf8 in mysql

Depends on a character. Some need one byte, some - four. See http://www.fileformat.info/info/charset/UTF-8/list.htm .
MySQL has two UFT8 character sets:

mysql> SHOW CHARACTER SET like 'UTF8%';
+---------+---------------+--------------------+--------+
| Charset | Description   | Default collation  | Maxlen |
+---------+---------------+--------------------+--------+
| utf8    | UTF-8 Unicode | utf8_general_ci    |      3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |
+---------+---------------+--------------------+--------+
2 rows in set (0.01 sec)

If a character is a one byte, it will use one byte in memory and on a disk. If four - then four. Plus the string will use one or two bytes to encode length of the string.
Note, that utf8 character set cannot encode all UTF8 characters.


Can I speed up a large MySQL/MariaDB join through partitioning or merging?

From the looks of the EXPLAIN plan, you are doing a full table scan on project_commits.

From the looks of the query, I surmise there is a one-to-many relationship from commits to project_commits. The problem I see is that your query is gathering data as many-on-one.

You are also using LEFT JOIN.

Your query is saying:

  Show me all project_commits that are connected to or orphaned from commits

Instead, you might want the query to say:

  Show me all project_commits that are connected to commits

SUGGESTION #1 : Flip Table Order

EXPLAIN select distinct
    project_commits.project_id,
    date_format(created_at, '%x%v1') as week_commit
    from commits
    left join project_commits
    on commits.id = project_commits.commit_id;

SUGGESTION #2 : Use INNER JOIN

EXPLAIN select distinct
    project_commits.project_id,
    date_format(created_at, '%x%v1') as week_commit
    from project_commits
    inner join commits
    on project_commits.commit_id = commits.id;

SUGGESTION #3 : Add created_at index

If you are already have an index on created_at or if you already have a compound index whose first column is created_at, skip this suggestion.

ALTER TABLE `project_commits` ADD INDEX created_at_ndx (`created_at`);

Adding an index will change EXPLAIN plan to do an index scan instead of a table scan

SUGGESTION #4 : Alter the JOIN behavior

You could manipulate the style of the join operation by tweeking the optimizer

According to MySQL Documentation on Block Nested-Loop and Batched Key Access Joins

  When BKA is used, the value of join_buffer_size defines how large the batch of keys is in each request to the storage engine. The larger the buffer, the more sequential access will be to the right hand table of a join operation, which can significantly improve performance.

  For BKA to be used, the batched_key_access flag of the optimizer_switch system variable must be set to on. BKA uses MRR, so the mrr flag must also be on. Currently, the cost estimation for MRR is too pessimistic. Hence, it is also necessary for mrr_cost_based to be off for BKA to be used.

This same page recommends doing this:

mysql> SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

GIVE IT A TRY !!!

Note : I have no idea what to expect from my suggestions. After all, your LEFT JOIN is like an iterative Cartesian Join with the potential of make a temp table that is the following

  4.573 Quintillion rows (5.4 Billion X 0.847 Billion) being looked up

Have fun and let us know what you find


MariaDB instance in cloud hit 95% CPU utilisation for 30 mins

(Not yet an "Answer", but debugging help:)

High CPU almost always means a SELECT needs optimizing.  To find out which query:

Run SHOW FULL PROCESSLIST; when the naughty query is running (10:00?).  Run it a few times; hopefully, you can spot some query with a large "Time" or perhaps it is being run repeatedly.
Or us the SlowLog to find it.  Here are tips on how to get the info and present it to us here for help.

You show heavy I/O also.  That could be a query that is doing a table scan.  Again, find the query and ask for help.

Meanwhile, refresh graphs, and add one for "slow queries", if it exists.


Enabling mySQL replication using GTID after purging binlog

Whether you use GTID or not, any replica needs a continuous stream of binlogs to keep in sync.
If any of the binlogs needed for the replica to catch up are gone, then it's trash. It can never catch up.
You need to reinitialize the replica. As if you were creating a new replica: make a new backup from the master, and use it to overwrite the replica. Then the replica can catch up from the point you created the backup.


Who created a procedure? (SQL Server 2008 R2 )

Yes there is a way

The table to store the results of the trigger

USE [SOME_DATABASE]
GO

CREATE TABLE [dbo].[ddl_objects_log](
    [date] [datetime] NULL DEFAULT (getdate()),
    [login_name] [nvarchar](128) NULL,
    [nt_user_name] [nvarchar](128) NULL,
    [program_name] [nvarchar](128) NULL,
    [host_name] [nvarchar](128) NULL,
    [text] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

The trigger

USE [SOME_DATABASE]
GO

/****** Object:  DdlTrigger [ddl_db_trigger]    Script Date: 22/01/2015 13:41:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [ddl_db_trigger]
ON DATABASE
FOR CREATE_FUNCTION,
    CREATE_PROCEDURE,
    CREATE_TABLE,
    CREATE_TRIGGER,
    CREATE_VIEW,
    ALTER_FUNCTION,
    ALTER_PROCEDURE,
    ALTER_TABLE,
    ALTER_TRIGGER,
    ALTER_VIEW,
    DROP_FUNCTION,
    DROP_PROCEDURE,
    DROP_TABLE,
    DROP_TRIGGER,
    DROP_VIEW,
    CREATE_INDEX,
    ALTER_INDEX,
    DROP_INDEX
AS
BEGIN
set nocount ON
    insert into ddl_objects_log(login_name, nt_user_name, program_name, host_name, text)
    select login_name, nt_user_name, program_name, host_name, EVENTDATA() from sys.dm_exec_sessions WITH(NOLOCK) where session_id=@@SPID
set nocount OFF
END

GO

ENABLE TRIGGER [ddl_db_trigger] ON DATABASE
GO

The Query to Audit the results of the trigger

USE [SOME_DATABASE]
GO

SELECT top 10
REPLACE(CONVERT(VARCHAR(250), text.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' ') as modify_datetime,
CONVERT(VARCHAR(215), text.query('data(/EVENT_INSTANCE/EventType)')) as event_type,
CONVERT(VARCHAR(225), text.query('data(/EVENT_INSTANCE/ServerName)')) as server_name,
CONVERT(VARCHAR(225), text.query('data(/EVENT_INSTANCE/DatabaseName)')) as database_name,
CONVERT(VARCHAR(225), text.query('data(/EVENT_INSTANCE/ObjectType)')) as object_type,
CONVERT(VARCHAR(225), text.query('data(/EVENT_INSTANCE/ObjectName)')) as object_name,
CONVERT(VARCHAR(215), text.query('data(/EVENT_INSTANCE/UserName)')) as user_name,
Login_name,
CONVERT(VARCHAR(MAX), text.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')) as command_text
FROM [SOME_DATABASE].[dbo].[ddl_objects_log]
where CONVERT(VARCHAR(225), text.query('data(/EVENT_INSTANCE/ObjectName)')) like '%SOME_STORED_PROCEDURE%'
order by date desc
GO

Another DBA might have a different opinion, but I consider having this information readily available as important for a DBA.


SQL Server 2014 - Compute scalar over computed indexed column

I believe the "computer scalar" operator in the plan where it is using the index is actually not being executed.  On my test rig with 1,000,000 sample rows, which is shown below, the query without the non-clustered index is a lot slower than the query that uses the clustered index on the myhash column.

USE tempdb;
SET NOCOUNT ON;

IF EXISTS (SELECT 1
    FROM sys.objects o
    WHERE o.name = 'HTest'
        AND o.type = 'U')
DROP TABLE dbo.HTest;

CREATE TABLE dbo.HTest
(
    HTest_ID INT NOT NULL
        CONSTRAINT PK_HTest_ID
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , V1 VARCHAR(255) NOT NULL
    , V2 VARCHAR(255) NOT NULL
    , V3 VARCHAR(255) NOT NULL
    , V4 VARCHAR(255) NOT NULL
);

INSERT INTO dbo.HTest (V1, V2, V3, V4)
SELECT TOP(1000000)
    o1.name
    , o2.name
    , o3.name
    , o4.name
FROM sys.objects o1
    , sys.objects o2
    , sys.objects o3
    , sys.objects o4;

ALTER TABLE dbo.HTest
ADD MyHash AS(CAST(HASHBYTES('SHA1', V1 + V2 + V3 + V4) AS VARBINARY(20)));

Below we have two test runs, one without the index, and the 2nd one with an index on the MyHash column.

IF EXISTS (SELECT 1
    FROM sys.indexes i
    WHERE i.name = 'IX_Htest_MyHash'
)
DROP INDEX IX_Htest_MyHash
ON dbo.HTest;

PRINT (N'');
PRINT (N'-----set stats io on---------------------------------------------------');
PRINT (N'');

SET STATISTICS IO, TIME ON;

PRINT (N'');
PRINT (N'-----run 1 (no index)--------------------------------------------------');
PRINT (N'');

SELECT MyHash
FROM dbo.HTest;

PRINT (N'');
PRINT (N'-----end of run 1------------------------------------------------------');
PRINT (N'');

CREATE INDEX IX_Htest_MyHash
ON dbo.HTest(MyHash);

PRINT (N'');
PRINT (N'-----run 2 (with index)------------------------------------------------');
PRINT (N'');

SELECT MyHash
FROM dbo.HTest;

PRINT (N'');
PRINT (N'-----end of run 2------------------------------------------------------');
PRINT (N'');

SET STATISTICS IO, TIME OFF

The salient bits from the output of this is:

-----run 1 (no index)--------------------------------------------------

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.
Table 'HTest'. Scan count 1, logical reads 9409, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1560 ms,  elapsed time = 7516 ms.

-----end of run 1------------------------------------------------------

-----run 2 (with index)------------------------------------------------

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
Table 'HTest'. Scan count 1, logical reads 4227, physical reads 0, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 188 ms,  elapsed time = 4912 ms.

-----end of run 2------------------------------------------------------

As you can see, run 2 is clocking in with far less CPU time, and around half the reads of run 1.

The plan for run 1:

The plan for run 2:

Looking at the XML for the 2nd plan, you can see the <ComputeScalar> operator is actually just a lookup.  Look at the 5th line <ScalarOperator ScalarString="[tempdb].[dbo].[HTest].[MyHash]">:

  <ComputeScalar>
    <DefinedValues>
      <DefinedValue>
        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[HTest]" Column="MyHash" ComputedColumn="true" />
        <ScalarOperator ScalarString="[tempdb].[dbo].[HTest].[MyHash]">
          <Identifier>
            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[HTest]" Column="MyHash" ComputedColumn="true" />
          </Identifier>
        </ScalarOperator>
      </DefinedValue>
    </DefinedValues>
    <RelOp AvgRowSize="21" EstimateCPU="1.10016" EstimateIO="3.11572" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1000000" LogicalOp="Index Scan" NodeId="1" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="4.21587" TableCardinality="1000000">
      <OutputList>
        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[HTest]" Column="MyHash" ComputedColumn="true" />
      </OutputList>
      <RunTimeInformation>
        <RunTimeCountersPerThread Thread="0" ActualRows="1000000" ActualRowsRead="1000000" ActualEndOfScans="1" ActualExecutions="1" />
      </RunTimeInformation>
      <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
        <DefinedValues>
          <DefinedValue>
            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[HTest]" Column="MyHash" ComputedColumn="true" />
          </DefinedValue>
        </DefinedValues>
        <Object Database="[tempdb]" Schema="[dbo]" Table="[HTest]" Index="[IX_Htest_MyHash]" IndexKind="NonClustered" />
      </IndexScan>
    </RelOp>
  </ComputeScalar>

Presumably, the plan for the 2nd query includes the base calculation for the computed column, even though the results actually come from the index without any calculations actually taking place.


Inconsistency between maxbytes & bytes. Oracle

MAXBYTES is 0 for datafiles that are not autoextensible, but BYTES is greater than 0. Use this:

Select ROUND (SUM (d.bytes) / (1024 * 1024 * 1024)) AS size1,
ROUND (SUM (decode(d.maxbytes, 0, d.bytes, d.maxbytes)) / (1024 * 1024 * 1024)) AS size2
From dba_data_files d where tablespace_name = 'TABLESPACE_NAME';

But it is possible to manually resize a datafile above maxbytes.

SQL> select file_id, bytes, maxbytes from dba_data_files;

   FILE_ID      BYTES   MAXBYTES
---------- ---------- ----------
         1  954204160 3.4360E+10
         3 1142947840 3.4360E+10
         5  608174080 3.4360E+10
         7    5242880   10485760

SQL> alter database datafile 7 resize 20M;

Database altered.

SQL> select file_id, bytes, maxbytes from dba_data_files;

   FILE_ID      BYTES   MAXBYTES
---------- ---------- ----------
         1  954204160 3.4360E+10
         3 1142947840 3.4360E+10
         5  608174080 3.4360E+10
         7   20971520   10485760

I updated the query:

Select ROUND (SUM (d.bytes) / (1024 * 1024 * 1024)) AS size1,
ROUND (SUM (greates(d.bytes, d.maxbytes)) / (1024 * 1024 * 1024)) AS size2
From dba_data_files d where tablespace_name = 'TABLESPACE_NAME';


DB Locks - How would one handle long transactions and maintain high concurrency?

Firstly the batch order should be validated first and then committed.  The transaction that commits the inserts into the database should as you say be as short as possible.  In the perfect world this would mean that you do all the validation up front before beginning the transaction and committing.

From what you have said I doubt such a restructure is on the cards, so I would suggest investigating why the transaction is locking the whole table.  Ideally such an insert would only have row level locks.

Depending on the structure of the data it may be possible to open a transaction and commit after each row.

Lastly, it maybe be possible to batch up the data and send it all to the database for processing.

All of these options will require a non-trivial amount of changes.

Edit:

Given your comments it would seem that you need this batch process to happen in a single transaction.  This would indicate that the locking is correct.  The next step is to understand what is causing the batch process to take so long.  If the batch process is taking a considerable amount of time there is either an issue in the queries that read the data that is used for validation or how the data required for the validation is stored.

You will need to start looking at each query that is run as part of the validation to understand what is taking the longest and then optimise that query and so on until the whole process is much more streamlined.


Uninstalling SQL Server Cumulative Update Issue

Why are you uninstalling a Cumulative Update?
If you no longer have the CU, download it again, then (you'll have to stop the current setup process first) run:
> C:\whatever\cu-filename.exe /x c:\temp\cu-files\

Then start the uninstall over again, and when you get that dialog, hit Browse... and point it at c:\temp\cu-files.
It may find what it needs in the extracted CU files, but you may have to wait to perform this change until you have access to the 2017 image. (Any edition will do, I expect, so you could download the evaluation ISO for example.)
But let me ask again: Why are you uninstalling a Cumulative Update?


Direct the OUTPUT of an UPDATE statement to a local variable

No, because you are potentially OUTPUTting multiple rows, which wouldn't fit into a scalar variable.

You need to output into a @Table variable or declared table to handle multiple rows of output.


Database Wrapper Design issue: No operator matches the given name and argument type(s)

You'll need to explicitly convert your datetime strings to appropriate data types. Since you chose not to include the SQL statement in question, I can only guess what it's like, but if it looks anything like

...WHERE timestamp_column > ?...

you'll need to replace it with

...WHERE timestamp_column > TO_TIMESTAMP(?, 'YYYY-MM-DD HH24:MI:SS')...

using, of course, an appropriate format string.


You seem to be referring to the tables too many times. The lines

 FROM `tbl_users` , `tbl_applicant`
    INNER JOIN `tbl_users`

causes the table tbl_users to be joined with itself, which is probably not what you intend.

Instead, start with tbl_users and then 'join' the other tables one at a time, each time stating which fields you are joining on, like this...

SELECT  tbl_users.userid , tbl_applicant.applicant_id, tbl_payment.userid
FROM tbl_users
INNER JOIN  tbl_applicant
       ON tbl_users.userid = tbl_applicant.applicant_id
INNER JOIN tbl_payment
      ON tbl_applicant.applicant_id = tbl_payment.userid
WHERE tbl_users.userid = '".$_SESSION['userid']."'"

Other points are these:

You have

WHERE `u_id.userid` =

but you do not include the table u_id in any of your joins. Did you mean tbl_users.userid ?

If not and u_id is really another table, then you need another JOIN to join it to one of the other three tables on the appropriate column(s).
You do not really need to have all the back ticks in your table/column names. They are only really needed if you have a table with the same name as a keyword or if you have spaces in your column names. Leaving them out makes it easier to read I think.
Although it's good practice to include the table name in your SELECT line you do not need to do so unless the column name is repeated in the other tables, as with tbl_users.userid and tbl_payment.userid. Then using the table name removes ambiguity.
By default, just saying JOIN will do an INNER JOIN and return only those rows whose key appears in both the joined tables.

Incidentally, to answer why you get the Non unique table/alias message, it's because you select tbl_users.userid from tbl_users which you later join to tbl_users again, so it it ambiguous as to which of the two versions of  tbl_users you are referring to the SELECT line.

Sometimes it is useful to join a table to itself, as you seem to be inadvertently doing (it's called a self join). However in that case you should include a new table name as an alias to one of the real table names to make them unambiguous. eg

FROM tbl_users
INNER JOIN tbl_users AS copy_tbl_users --  here I am using copy_tbl_users to refer to the tbl_users that I'm joining with.
ON tbl_users.userid = copy_tbl_users.some_id_in_the_table --  Now I use the alias so the table names are unambiguous.

The above code, using AS to make the alias, is for MySQL. Although even in MySQL you don't strictly need the AS keyword but I feel leaving it out for the sake of saving two characters makes the code harder to understand so I always use it.


Set up a SQL Server database based on an Excel file which refreshes automatically

You could use a FileSystemWatcher which triggers when the Excel file changes and runs simple code to push the revised file through to SQL Server.


What you're asking here is essentially a crash-course in being a DBA. I'll try to explain this by sequentially explaining how the Oracle Database software comes to life at startup.

An "instance" is the Oracle software running in memory, before it touches any files. It goes away when the power is switched off.

init<DB>.ora is the instance parameter file. (This is a human-readable PFILE, which is one of two kinds of parameter file. The other is a binary SPFILE.) This is what defines the parameters to start the Oracle Database software in memory, such as how much memory to use. Yours may not look like theirs, and it doesn't have to. The most important parameters here are the database name and the location(s) of the controlfile(s).

After the instance starts and allocates memory, it then "mounts" a controlfile. This defines the other files that are available, the state/version level of transactions in the system, and other extremely high-level metadata. Note that we're still not up to the part that considers the user data in the database. Like an SPFILE, a controlfile is stored in a binary format, but also like a PFILE, the controlfile can be backed up "to trace" to generate a human-readable means of recreating it. Note that only the binary version can actually be used.

The parameter file defines the instance, and the controlfile defines the database.

A "database" is the collection of files on the disk. This is where the business data and the logical metadata is stored. After the instance mounts the controlfile, it reads the controlfile and "opens" the database. If it's missing any data files--or they fail consistency checks--it won't open.

The challenging part of answering this properly is that both the instance parameter file and the controlfile(s) will likely need to be recreated on your end if you're going to have any luck accomplishing what you'd like to do.

This is not unlike duplicating a production database to a test environment, detailed here: http://www.dba-oracle.com/oracle_tips_db_copy.htm


MySQL BETWEEN command - performance (order)

The answer is kind of "no" but more "your question does not have an answer because it's based upon incorrect assumptions".

1) Tables are not really stored in a particular order.

2) Even if they were, how would the DB know that the question had been answered until it had finished reading the entire table.  Putting things "up front" would not help.

3) What you ask (range of values) is in the realm of an index.  An index has more structure and roughly the concept of "beginning and end" so would be the way to go.  Build an index on the values you intend to query.

4) The way indexes are structured means that individual rows can be selected from billions with just a handful of I/O reads.  However you need to have the right values indexed to match the queries.  Look at the plan to see if it's working for you.


As Erik mentioned, 32767 is the resource database, and it is hidden from the sys.databases view, but that doesn't mean the object you're after lives only there - in fact most system objects are in all databases.

To determine the object, we just need to pass your object_id to a query against sys.all_objects (rather than sys.objects):

SELECT name FROM sys.all_objects
  WHERE object_id = -710746692;

And the result:

sp_MSreplupdateschema

So, sounds to me like something is messed up with your replication configuration, or you're just throwing too much concurrent changes at it. If this is only happening during deployments or specific activities I would try to eliminate those as causes.

Otherwise, like Erik said, there's not much you can do about it - it's not like you can edit that stored procedure, even if you do understand why it's deadlocking, so you'll have to troubleshoot it from the feature side. You could upload the .xdl graph and someone could take a look and maybe figure out if it's deadlocking because of something else you can control.


For MySQL in addition to the GRANT command, there is also a REVOKE command.  However, REVOKE has limitations.

If you GRANT ALL ON mydb.* TO 'someuser'@'somehost' you cannot remove one table by running REVOKE ALL ON mydb.mytbl3 TO 'someuser'@'somehost'.

This is because REVOKE will only remove rights as they are granted. That is, you could:

REVOKE ALL ON mydb.* TO 'someuser'@'somehost'

Of course you could create scripts that would automatically GRANT access to each table individually.  Then you could have a special script that would REVOKE the table access that you need to remove from 'troublesomeuser'@'somehost'.

Note that the most recently run command, whether GRANT or REVOKE is the command that is in effect.

There is no DENY command in the MySQL syntax. This is a feature defined in some other SQL implementations. However, denying rights has its own pitfalls that many have stumbled over.


SQL Server backup fails on disk space error

The error not enough server storage available is misleading.  This is not disk space, but Windows SMB management memory.  Try restarting the target machine (where the backup is being saved), or at least restart the Server service.

See the following Microsoft support link for instructions on potential registry changes that may be necessary at the machine where the backup is being stored.

http://support.microsoft.com/kb/304101

Also, you might try a search for "Windows Server error 1130" for more info.


SQL Server CLR procedures and SQLCLR_QUANTUM_PUNISHMENT

Unfortunately there is not a lot of information out there on this topic. I can say that yes, you certainly should add the Thread.Sleep(0); to various places in your code. Doing so gives SQL Server an opportunity to control the thread. This is not necessary in places that are doing data access (i.e. SqlClient stuff) as SQL Server already has the ability to manage that.

Also, @Remus Rusanu, in another question (perhaps on Stack Overflow) had recommended using Thread.BeginThreadAffinity / Thread.EndThreadAffinity to manage long-running processes, but using those requires that the Assembly be set to UNSAFE.


How to separate out individual sql statements from Oracle SQL script for execution from java code

What creates your sql script file? Is the sql script file creation modifiable?

If you can modify the sql script file creation, the easiest way is to append your own separator characters to your script between the statements. (Comments should be a good idea)

If not, if you're just receiving a bunch of sql or pl/sql statements in a unmodifiable-source plain text file, then you should code your own parsing algorithm.
You must keep in mind that EVERY sql statement end with semicolon (;), but since PL/SQL blocks can contains several sql statements they must end with a forwarded slash (/) by itself in a new line.

The confusing part is that some SQL statements are, in fact, stored PL/SQL blocks, like CREATE PROCEDURE or CREATE TRIGGER (complete list in documentation book "Oracle® Database PL/SQL Language Reference" : http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/sqlstatements.htm#LNPLS99979 )

(e.g.

ALTER, SELECT, DROP, GRANT etc.. statements end with semicolon ;
CREATE TABLESPACE, CREATE USER, CREATE TABLE, etc statements end with semicolon ;
CREATE PROCEDURE, CREATE PACKAGE, CREATE TRIGGER, etc statements end with slash /
DECLARE, BEGIN (annonymous pl/sql blocks) statements end with slash /

)

Regards


That is not possible I am afraid of.  The selection is based on the following criteria:

Type of connection (local or network)
Username requested
Database requested

Nowhere in there is a process given.  If you want to trust a given process the way to do that is to have a service account, a specific database user, and map these in, using peer authentication on *nix (peer on local connections used to be called ident but this was changed since ident is very different on network connections and has far more issues).


No, foreign keys are not implicitly unique. And they can't be otherwise it wouldn't be possible to implement a one-to-many relationship, e.g. this:

create table customer
(
   id integer primary key
);
create table orders
(
   id integer primary key,
   customer_id integer not null references customer
);

insert into customer values (1);
insert into orders values (1,1), (2,1), (3,1);

You can however make them unique if you want. This is typically used when modelling a a one-to-one relationship.


SQL Server compact- Query that return 2 constant columns even if query didn't return data

Derived tables and Case statements are supported in SQL CE, so you can use this script

Select
case
when s.custs = 0 then 'Didnt found value in DB'
else 'found value in DB'
end
,'customer id'
from
(select COUNT(*) as 'custs' from Customers c Where  c.CustomerId = 1234) s

for reference check what can and what cannot be used in SQL CE


Is it possible to set up Azure as a publisher for Pull Replication to an on-premise database?

At this time, replication is only supported from on-prem to SQL Database, and it has to be push replication.  See here:

Replication to SQL Database

  Supported Configurations:

  The SQL Server can be an instance of SQL Server running on-premises or an instance of SQL Server running in an Azure virtual machine in
  the cloud
  SQL Database must be a push subscriber of a SQL Server publisher.
  The distribution database and the replication agents cannot be placed on SQL Database.

You may want to consider Managed Instances to get around this.  See Replication with SQL Database Managed Instance for details.


Avoid duplication when using other table in WHERE and JOIN clause in Postgres

Use DISTINCT ON, it's considerably shorter, simpler and probably also faster:

SELECT DISTINCT ON (device_id) *
FROM   devices_shop_versions
WHERE  shop_id = 1
ORDER  BY device_id, updated_at DESC NULLS LAST;

Detailed explanation:

Select first row in each GROUP BY group?

If your table is big, you have many rows per device_id and you need to optimize performance, a recursive CTE might be faster, yet. Detailed instructions:

Optimize GROUP BY query to retrieve latest record per user

Make it a subquery to integrate in a bigger query:

SELECT mv.id, d.device_name, u.user_name, mv.version, mv.device_id, mv.shop_id, mv.updated_at
FROM  (
   -- query from above
   ) mv
JOIN   devices d on d.id = mv.device_id
JOIN   users   u on u.id = d.user_id
-- ORDER BY ???;  -- optional

Demo on rextester.

It's typically cheaper to join to more tables after excluding irrelevant rows.


can I set a MAX Limit to a BIGINT cassandra's data type?

No. A Bigint is just a 64-bit signed long. No size or space limits can be specified.
https://cassandra.apache.org/doc/latest/cql/types.html


How do I find the error at a line number in a Oracle Live SQL script?

The way to find errors within a Trigger within Oracle Live SQL is as follows:

Select *
from
     user_errors

This is how Ricardo Araujo debugged his Compound trigger error in statement 22 of his Oracle Live Sql script.


Two SQL Server databases setup with same .mdf and .ldf files

For a database in SQL Server, there's a setting called Auto-Close, which is meant to minimize the amount of resources used while SQL Server is running, but no users are actively doing things.

This setting is enabled by default in Express Edition instances, as those are usually running on developer workstations where performance isn't critical, and resources are needed for other applications.

When resources are released by this feature, the implementation includes closing the file handles associated with the database files (*.mdf, *.ldf, etc) on disk. This means that any other application can potentially open them, including the same or another instance of SQL Server.

In testing out this scenario, I found out there's no constraint inside SQL Server (at least in this particular version) that prevents attaching a database to the same set of files on disk, as long as they're readable. While this isn't a good/safe thing, of course, it's a relief to know that only one of the databases can be written to at a time, because the files will be exclusively locked by SQL Server when one of the databases is opened. The other database(s) will not be accessible.

To undo the situation:

Detach the database(s) you don't want. This removes the database from the instance, but leaves the files on disk alone (whereas DROP DATABASE does remove the files on disk). It may be necessary to restart the instance to get it to recognize that the database you want to keep is accessible.
Disable auto-close for the main database, to better prevent this from happening again. You may also want to disable it in the Model system database (if applicable) so new databases don't inherit this setting. For a production system, I recommend making sure auto-close is disabled on every database; I like to use Policy-Based Management to do the checking periodically, but there are lots of ways to go about it.


SSAS / MDX Query - Add an extra dimension when using Order

I'm not quite sure what you're asking, but I think you want to do the following (correct me if I'm wrong):

You want to see a breakdown of sales by month, one row for each month, with a total for all months at the bottom.
You want to see the items across the columns, ordered by the item with the largest sales volume to the smallest.

select non empty
       order ([Item].[Item].Children
             ,([Measures].[Total Line Value]
              ,[Date].[Month].[All])
             ,BDESC)
    on Columns
       non empty
      ,[Date].[Month].Children
    on Rows
  from [Sales Analysis]
 where [Measures].[Total Line Value]

The columns are each individual item, sorted by the total Line Value over all months, i.e. from all-time best seller to all-time lowest seller, with items having no sales at all excluded.

The rows are the months for all time where data exists.  You can use a set definition to specify any date range you want.  Note that this assumes the existence of a dimension called [Date].

This query will show the items in order of total sales across the columns and the sales for each month by item down the rows.  I'm not 100% sure if this is what you want; feel free to clairfy your question if I'm not on the right track.


Relationship Between Students, Parents and Guardians

Your design with 5 tables is more normalized than your initial design, and makes more sense.
When you say "pivot table" it sounds like you mean a linking table. Since the relationship between students and parents (and also between students and guardians) is a many-to-many relationship being that a student can have up to two parents, and a parent can have multiple children (students) then this is the proper way to relate the two tables in a normalized form, and good design.
The only thing I think you could do is consolidate the parents and guardians into one Table (assuming they have basically the same attributes) and have a type field to determine if it's a parent or guardian. If there's only two types, you can use a boolean datatype field called IsParent, or with more types you can create an enum table called RelationshipTypes (or something similar) and store the ID in your consolidated Table, for normalization.
The IsParent field would be best to put on the linking table, that way a Guardian can be a parent to one Student but only a Guardian to another Student (who isn't related by blood) in cases of like adoption, etc.
Here's a sqlfiddle to illustrate the above, with some tweaks.


The way I understand your question is that you have an existing table with a column that has up until now been populated with manual values, and now you want to (1) make this column an IDENTITY column, and (2) make sure that the IDENTITY starts from the most recent value in the existing rows.

First off, some test data to play with:

CREATE TABLE dbo.ident_test (
    id    int NOT NULL,
    xyz   varchar(10) NOT NULL,
    CONSTRAINT PK_ident_test PRIMARY KEY CLUSTERED (id)
);

INSERT INTO dbo.ident_test (id, xyz)
VALUES (1, 'test'),
       (2, 'test'),
       (5, 'test'),
       (6, 'test'),
       (10, 'test'),
       (18, 'test'),
       (19, 'test'),
       (20, 'test');

The goal is to make the table's primary key column, id, an IDENTITY column that will start at 21 for the next record that gets inserted. For this example, the column xyz represents all of the table's other columns.

Before you do anything, please read the warnings at the bottom of this post.

First off, in case something goes wrong:

BEGIN TRANSACTION;

Now, let's add a temporary work column, id_temp and set that column to the existing id column's values:

ALTER TABLE dbo.ident_test ADD id_temp int NULL;
UPDATE dbo.ident_test SET id_temp=id;

Next, we need to drop the existing id column (you can't just "add" an IDENTITY to an existing column, you have to create the column as an IDENTITY). The primary key also has to go, because the column depends on it.

ALTER TABLE dbo.ident_test DROP CONSTRAINT PK_ident_test;
ALTER TABLE dbo.ident_test DROP COLUMN id;

... and add the column again, this time as an IDENTITY, along with the primary key:

ALTER TABLE dbo.ident_test ADD id int IDENTITY(1, 1) NOT NULL;
ALTER TABLE dbo.ident_test ADD CONSTRAINT PK_ident_test PRIMARY KEY CLUSTERED (id);

Here's where it gets interesting. You can enable IDENTITY_INSERT on the table, which means that you can manually define the values of an IDENTITY column when you're inserting new rows (not updating existing rows, though).

SET IDENTITY_INSERT dbo.ident_test ON;

With that set, DELETE all the rows in the table, but the rows that you're deleting are OUTPUT right into the very same table - but with specific values for the id column (from the backup column).

DELETE FROM dbo.ident_test
OUTPUT deleted.id_temp AS id, deleted.xyz
INTO dbo.ident_test (id, xyz);

Once, done, turn IDENTITY_INSERT back off again.

SET IDENTITY_INSERT dbo.ident_test OFF;

Drop the temporary column that we added:

ALTER TABLE dbo.ident_test DROP COLUMN id_temp;

And finally, reseed the IDENTITY column, so the next record's id will resume after the highest existing number in the id column:

DECLARE @maxid int;
SELECT @maxid=MAX(id) FROM dbo.ident_test;
DBCC CHECKIDENT ("dbo.ident_test", RESEED, @maxid)

Checking the example table, the highest id number is 20.

SELECT * FROM dbo.ident_test;

Add another row and check its new IDENTITY:

INSERT INTO dbo.ident_test (xyz) VALUES ('New row');
SELECT * FROM dbo.ident_test;

In the example, the new row will have id=21. Finally, if you're happy, commit the transaction:

COMMIT TRANSACTION;

Important

This is not a trivial operation, and it carries quite a few risks that you should be aware of.

Do this in a dedicated test environment. Have backups. :)
I like to use BEGIN/COMMIT TRANSACTION because it prevents other processes from messing with the table while you're in the middle of changing it, and it gives you the possibility to roll everything back if something goes wrong. However, any other process that tries to access your table before you've committed your transaction will end up waiting. This may be pretty bad if you have a large table and/or you are on a production environment.
OUTPUT .. INTO won't work if your target table has foreign key constraints or any of a number of other features that I can't remember off the top of my head. You could instead off-load the data into a temporary table instead, and then insert it back into the original table. You might be able to use partition switching (even if you don't use partitions).
Run these statements one by one, not as a batch or in a stored procedure.
Try to think of other things that may depend on the id column that you're dropping and re-creating. Any indexes will have to be dropped and re-created (like we did with the primary key). Remember to script every index and constraint that you will need to recreate beforehand.
Disable any INSERT and DELETE triggers on the table.

If re-creating the table is an option:

If re-creating the table is an option for you, everything is a lot simpler:

Create the empty table, with the id column as an IDENTITY,
Set IDENTITY_INSERT ON for the table,
Populate the table,
Set IDENTITY_INSERT OFF, and
Reseed the identity.


Exclude elements are already in file - Foreach loop powershell

As presented here, your code has many problems.

First off, avoid non-descriptive variable names.  Your code is very difficult to read. Until you read the entire script, you have no idea what any variable is supposed to represent.  Every time you find a new variable, you have to hunt down where it comes from and then keep it all in your head.  It's difficult to follow the logic of your script because there's no contextual information.  If you were trying to remove sensitive information so you could post it on the web, you've removed too much.

Next:

$l = $ab | Select-Object file | Out-File C:\temp\fname.txt

This line makes no sense.  Out-File has no standard output to assign to $l, so $l is always going to be assigned the value of $null.  You also never appear to use $l again.  I don't know what you intend this line to do.

However, if I assume you only want this line to write the file column to a file and that you don't need $l at all, then as written this is going to output a header line.  Your file contents will be:

File
----
MyFile1
MyFile2

You probably don't want that because when you read it back the system will assume File and ---- are files.  The pattern you want is more like this:

$ab | Select-Object -ExpandProperty File | Set-Content C:\temp\fname.txt

$SentUsers = Get-Content -Path C:\temp\fname.txt

I tend to avoid pairing Out-File with Get-Content because they have different encoding defaults, while Get-Content and Set-Content are the same.

Next:

foreach ($i in $cr) {

The variable $cr has no value assigned to it.  If you're not assigning it elsewhere, this entire loop will never run.

Next:

If ( $i.Uid -eq 'No' -and $i.UIntheTeam -eq 'No' -and $i.file -ne $SentUsers ) {

As @ScottHodgin mentions, $SentUsers is likely going to be an array of values.  -ne treats both operands as a scalar.  You probably want one of the containment operators:

$Value -in $Array
$Value -notin $Array
$Array -contains $Value
$Array -notcontains $Value

So, I'd expect this to do what you want:

If ( $i.Uid -eq 'No' -and $i.UIntheTeam -eq 'No' -and $i.file -notin $SentUsers ) {

See Get-Help about_Comparison_Operators for more information on the above operators.

Next:

Else {
    'Exit'
}

Huh?  "Else, output the string 'Exit'."  What are you intending this to do?  Is it supposed to stop the script?

Next:

# Copy new current file name to file by overwriting existing file

$ab | select file | out-file -FilePath C:\temp\fname.txt

Why do you need to do this?  You did the same thing earlier in the script, and $ab hasn't modified since then.  I mean, the file path is the same and everything.  Is that an error?  Did you decide to use the same file path throughout your posted code without leaving in the fact that you're actually changing different files?


B, D incorrect:

The join is performed in parallel, there is only 1 serial operation on the picture, only one parallel->serial (P->S) transition, and that is when the query coordinator collects the results.

A (, D) incorrect:

Parallel SQL uses the producer-consumer model. One set reads the table, the other set builds/probes the hash table, they do not read seperate tables simultaneously.

C incorrect:

There is only 1 DFO (:TQ1), the order of execution can be easily recognized by the order of virtual tables or the table queues (:TQ1000x). This is not necessarily true for multiple DFOs, but it is inside 1 DFO. :TQ10000 comes before :TQ10001, but :TQ10000 does not necessarily come before :TQ20000. Read more about this:

https://jonathanlewis.wordpress.com/2015/12/21/parallel-plans-2/

So the first table to be read is CUSTOMER, not SALES.

E correct:

Well, this is how it works, but that is not a real explanation. Easier to prove the other options wrong.


How to create a trigger in a different database?

DECLARE @statement NVARCHAR(MAX) = N'CREATE TRIGGER [dbo].[TestTrigger]
  ON [dbo].[TestTable]
  AFTER INSERT, UPDATE, DELETE
  AS
  BEGIN
    PRINT ''test'';
  END';

DECLARE @sql NVARCHAR(MAX) = QUOTENAME(@db) + '.sys.sp_executesql';

EXEC @sql @statement;

This is not entirely intuitive, but it should work. Here is a simpler example that you can test independently:

USE [master];
GO
DECLARE @db SYSNAME = N'tempdb';

DECLARE @statement NVARCHAR(MAX) = N'SELECT DB_NAME(), * FROM sys.database_files';

DECLARE @sql NVARCHAR(MAX) = QUOTENAME(@db) + 'sys.sp_executesql';

EXEC @sql @statement;

Credit to Erland Sommarskog - you should read this article if you want to understand more deeply how it works.


Keeping service up while taking drive offline in clustered instance

I've never tried this before but thinking this out logically, these are the things I would do (in no specific order):

To answer your question yes, exclude drive K as a dependency as a cluster resource (this should prevent it from causing the cluster to go down when you need to remove it)
Manually take those databases offline that are stored on drive K
(I usually set the database to single user mode to make that happen)
Triple-check that nothing else is stored on drive K (like any text log files, etc) that may be used by SQL Server (make sure you dont have any hidden files too)
Take drive offline, rescind the space, then bring it back online


Books on optimizing and refactoring SQL stored procedures

The best two books here are both from Itzik Ben Gan - Inside SQL Server T-SQL Querying and Inside SQL Server T-SQL Programming.

Read the Querying one first. Covers all the important information on how queries are processed, thinking in terms of sets, etc. Covers all aspects of querying. The second book goes into programming constructs when working with T-SQL. Amazing books.


Open encrypted trigger of table in database (MS SQL Server Management Studio)

Is there a possibility to view Code behind a encrypted trigger of a database table?

Yes, but since it's most likely against the TOS, I'll leave it as that and tell you if you really really want to get at the code to search the internet.

I hate to leave the answer that short, but that's the whole point of encrypting it. If this is your code and you did this, note that you'll want to save the original text in a managed repository or other source code system. If it's not yours, you may be violating a TOS, EULA, or other laws depending on the situation and opening yourself up to liabilities.


Postgresql after 'for each row' triggers don't actually execute after each row?

Since the your INSERT and UPDATE are atomic (either all rows inserted/updated or none), you cannot catch a state when the count will not be 5 or 0.  First the INSERT happens, then the trigger is fired for all the rows.


I started with an example of good must read book.

An Introduction to Database Systems by C.J. Date.

This is one of the most influential book to development of all concepts and technological available today. The book follows an academic style and has lots of real cases.

The primary target is not for DBAs but Database systems developers, but a real good DBA knows how your database works and how to handle with its limitations and hidden capabilities.


A backup can't prevent corruption. Nothing can absolutely prevent corruption.

What a backup does is to enable you to recover data to an earlier point in time in the event of corruption or other disaster.

Whether your backup schedule protects you adequately is not something a community of peers can answer - your business requirements will dictate that. You need to consider your recovery point objective (RPO) - how much data can you afford to lose? If you cap at 5 minutes and you take a full backup once a day and log backups every hour, you're not covered. To truly protect yourself you should be in full recovery mode and your log backups should occur every <= n minutes, where n is the maximum amount of data loss you can sustain.

There are other considerations as well, such as recovery time objective (RTO) - in the event of a disaster, how long can you afford to spend getting back online? This will determine whether you just keep your backups on a drive ready for access, or actively keep a warm standby using log shipping, or higher "cost" DR techniques such as mirroring, FCIs, and Availability Groups. Note that some of these technologies can help save you from data corruption but other types of failures aren't covered (e.g. an accidental delete without a where clause will happily run on both the primary and any secondaries).


MySQL fails to start on arch Linux. Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

The arch wiki helped me solve this pretty quickly.

Install mariadb, afterwards run the following command before starting the mariadb.service:

mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql
mysql_secure_installation

Arch does not hold your hand, with this type of thing.
I guess if you install mysql / mariadb from source this is expected.

This answer also helped me to figure out what I was looking for.
https://stackoverflow.com/a/22179751/619760


Q:  I have a production data base that is experiencing wildly fluctuating Page   Life Expectancy (PLE) issues. (It crashes to zero at random times.)

Let me ask you what is output of Select @@Version. What is SP and CU level to which your SQL Server is patched. The reason I am asking this is because there was bug in SQl Server 2012 which forced PLE to plummet like what you are observing. Ths bug was fixed in SQL Server 2012 SP1 CU4. Or to be on safer said I would recommend you apply SQL Server 2012 SP2 instead of going for CU4
Its sometime normal for PLE to fluctuate on system having high activity. Actually this is by very virtue how PLE code works in SQL Server. But the fact that its plummetting to zero quite frequently make me believe you might be hitting the bug I have mentioned above.
As per Microsoft Bug fix detail

You may experience slow performance in SQL Server 2012. When you check SQL Server Performance Monitor tools, you see the following:
•A rapid decline in the SQLServer:Buffer Manager\Page life expectancy performance counter values. When this issue occurs, the counter is near 0.

PLE on system is measure of how volatile your buffer pool is, its also measure of amount of I/O activity going in your SQL Server. MSDN says that

Page life expectancy - Indicates the number of seconds a page will stay in the buffer pool without references

Believe me this definition is incomplete. It describes it in form of time which is not a complete definition. I have always noticed that it is measure of I/O activity on server. The greater the I/O activity the more volatile would be BPool, thus fluctuating PLE.

Q:  My guess is that the hardware for all the virtual machines is under stress, so it will randomly swap out some memory from the server for a while.

If you believe this is the case and you want SQL Server not be be victim of such issues you must make sure SQl Server service account has Locked Pages in Memory Privielge (LPIM). This will not let OS to force SQL Server page out its memory. If account running SQL Service is local system by default SQL Server will have this privilege in SQL Server 2012.
Note:

This is a workaround. The solution here would be to find out what is causing stress to VM machine. You should fix that. If you feel Wmware Balooning is the issue. You can use RAMMAP tool to track memory which is consumed by Locked Driver. In RAMMAP tool if you see Locked driver taking huge memory its sign of VMware balooning. Take help from the team to configured/disable ballooning for the virtual machine on which SQL Server is running

Befor giving LPIM you must make sure you have set optimum value for max server memory and have left ENOUGH memory for OS to perform efficiently.

If you do not follow above two points and if OS comes under severe memory pressure due to LPIM OS processes would be paged out because it cannot force SQL Server to release memory(its locked/non pageable due to LPIM) and thus leading to tremendous slowness of OS processes.

Q: So, am I using the sys.dm_os_buffer_descriptors view correctly? From what I read it always shows used buffer/cached pages. So if it is empty (or significantly reduced), I either don't have the memory anymore, or it is empty. (I would love a way to confirm this conclusion.)

Buffer descriptors as already mentioned returns information about all the data pages that are currently in the SQL Server buffer pool. IMHO buffer pages are affected by I/O activity on server and thus indirectly related to PLE. If there is request to fetch large amount of pages from disk to memory its quite possible that SQL Server will flush datapages to disk if it finds it needs to create space in buffer pool to bring in the new pages in memory and thus decreasing the amount of data page present in memory for particular database.
So what you are seeing via sys.dm_os_buffer_descriptors is not incorrect but I would not suggest you to use Buffer descriptor DMV to gauge PLE on server. This would not be a correct approach.


PostgreSQL ORDER BY clause effect different number of rows to return in pivot table

Omitting the ORDER BY goes against what the documentation recommends:

  In practice the source_sql query should always specify ORDER BY 1 to
  ensure that values with the same row_name are brought together

This ORDER BY is a requirement of the algorithm inside crosstab() because it's counting on identical values being contiguous. What happens inside crosstab() is a black blox as far as the executor of the main query is concerned. It doesn't know and doesn't care that this algorithm is sensitive to the order of the rows inside crosstab. To start with, it doesn't know that the arguments passed to crosstab() are queries. These queries are executed by a different instance of the executor, as dynamic SQL. It seems that your reasoning in the question is ignoring that, which is why you see properties in this query that are unusual in normal SQL queries, which don't involve embedding dynamic SQL.


Who changed the job schedule on a SQL Server Agent Job (SQL 2000)

Without reading the transaction log and/or log backups (which may be impossible, if msdb is set to the default simple recovery), I can't think of any good way.

Going forward, if you want to log the changes, you could create some log tables, and add logging triggers to these tables in msdb.

sysjobs
sysjobsteps
sysjobschedules

I checked those names on 2008, but I seem to recall they have the same names on 2000 (though the structure may differ).


Does SQL Server handle databases on SMB shares different than on Hard Drives?

As far as I know SQL server does nothing extra when using databases on a network share. The network link between it and the machine hosting the share going down would most likely be treated the same way as a disk error: the affected databases would be dropped into a "suspect" state until someone takes restorative action.

Recent improvements in the OS fileshare layer (i.e. in Windows, not SQL Server directly) with the introduction of SMB3.0 and related updates make problems less frequent and less severe though, assuming you are using Windows 2012 on both the SQL box(es) and the one(s) hosting the file share(s). See here and here for some more detailed relevant discussion.


What is the Best Way to detect if server is a Master inside a Stored Procedure?

This will work if the Master has a Slave Connected

SELECT COUNT(1) FROM
(SELECT user FROM information_schema.processlist) A
INNER JOIN
(SELECT user FROM mysql.user B WHERE repl_slave_priv = 'Y')
USING (user);

If the answer is...

> 0, Replication is running (This indicates this is a Master)
= 0, Replication is not running

You can also check the global status variable 'Slave_running'

SELECT COUNT(1) FROM information_schema.global_status
WHERE variable_name = 'Slave_running'
AND variable_value = 'ON';

If the answer is...

> 0, Replication is running (This indicates this is a Slave)
= 0, Replication is not running

You can also check the processlist for 'system user'

SELECT COUNT(1) FROM information_schema.processlist
WHERE user = 'system user';

If the answer is...

= 2, Replication is running (This indicates this is a Slave)
= 1, Replication is broken (This indicates this is a Slave)
= 0, Replication is not running

I hope these queries give you some direction.

CAVEAT

SHOW MASTER STATUS; just tells you what the current binary log is. If your Slaves  have binary logs disabled, then SHOW MASTER STATUS; return nothing. There is no way to capture this in the information_schema database.

The only obvious way is to either

SHOW SLAVE STATUS\G
Look for the master.info file in the OS

UPDATE 2011-12-30 12:50 EDT

Here is simply way to identify your master from your slaves: Create a Table whose sole purpose to is to hold the server names that make up your Master. Run these commands on all your DB Servers, (Masters and Slaves)

CREATE TABLE mysql.MasterList
(
    hostname VARCHAR(64),
    PRIMARY KEY (hostname)
) ENGINE=MyISAM;
INSERT INTO mysql.MasterList VALUES ('dbserver1'),('dbserver2'),('dbserver3');

Now just run this query to determine if it is a Master:

SELECT COUNT(1) INTO @IsThisMaster FROM
(SELECT variable_value hostname FROM information_schema.global_variables
WHERE variable_name='hostname') A
INNER JOIN mysql.MasterList B USING (hostname);

Here is a sample run on my PC using MySQL 5.5.12

mysql> show variables like 'hostname';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| hostname      | LW-REDWARDS2 |
+---------------+--------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE mysql.MasterList
    -> (
    ->     hostname VARCHAR(64),
    ->     PRIMARY KEY (hostname)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO mysql.MasterList VALUES ('dbserver1'),('dbserver2'),('dbserver3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(1) INTO @IsThisMaster FROM
    -> (SELECT variable_value hostname FROM information_schema.global_variables
    -> WHERE variable_name='hostname') A
    -> INNER JOIN mysql.MasterList B USING (hostname);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT @IsThisMaster;
+---------------+
| @IsThisMaster |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

mysql> INSERT INTO mysql.MasterList VALUES ('LW-REDWARDS2');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT COUNT(1) INTO @IsThisMaster FROM
    -> (SELECT variable_value hostname FROM information_schema.global_variables
    -> WHERE variable_name='hostname') A
    -> INNER JOIN mysql.MasterList B USING (hostname);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @IsThisMaster;
+---------------+
| @IsThisMaster |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

mysql>

Give it a Try !!!

UPDATE 2011-12-30 13:06 EDT

Here is the Stored Function you will need:

DELIMITER $$

DROP FUNCTION IF EXISTS `mysql`.`Is_This_A_Master` $$
CREATE FUNCTION `mysql`.`Is_This_A_Master` () RETURNS INT
DETERMINISTIC
BEGIN

    SELECT COUNT(1) INTO @IsThisReplicationMaster FROM
    (SELECT variable_value hostname FROM information_schema.global_variables
    WHERE variable_name='hostname') A
    INNER JOIN mysql.MasterList B USING (hostname);

    RETURN @IsThisReplicationMaster;

END $$

DELIMITER ;

Here is a Sample Call:

mysql> select mysql.Is_This_A_Master();
+--------------------------+
| mysql.Is_This_A_Master() |
+--------------------------+
|                        1 |
+--------------------------+

UPDATE 2011-12-30 13:12 EDT

Just remember

Once a Server Becomes a Slave, delete that hostname from mysql.MasterList
Once a Server Becomes a Master, insert that hostname into mysql.MasterList


How can I view how long a query has spent waiting for locks, without a profiler?

(If you have access to DMVs then look into sp_whoisactive with @find_block_leaders = 1. Just tell you DBA (if you are not) to deploy it and grant you execute permission.)

SQL server dynamic management views are your best friend :

Below are several ways to find out blocking :

--Ref: https://sqlserverperformance.wordpress.com/category/diagnostic-queries/
select t1.resource_type as 'lock type'
    ,db_name(resource_database_id) as 'database'
    ,t1.resource_associated_entity_id as 'blk object'
    ,t1.request_mode as 'lock req'
    ,--- lock requested
    t1.request_session_id as 'waiter sid'
    ,t2.wait_duration_ms as 'wait time'
    ,-- spid of waiter
    (
        select [text]
        from sys.dm_exec_requests as r -- get sql for waiter
        cross apply sys.dm_exec_sql_text(r.sql_handle)
        where r.session_id = t1.request_session_id
        ) as 'waiter_batch'
    ,(
        select substring(qt.text, r.statement_start_offset / 2, (
                    case
                        when r.statement_end_offset = - 1
                            then LEN(CONVERT(nvarchar(max), qt.text)) * 2
                        else r.statement_end_offset
                        end - r.statement_start_offset
                    ) / 2)
        from sys.dm_exec_requests as r
        cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
        where r.session_id = t1.request_session_id
        ) as 'waiter_stmt'
    ,-- statement blocked
    t2.blocking_session_id as 'blocker sid'
    ,-- spid of blocker
    (
        select [text]
        from sys.sysprocesses as p -- get sql for blocker
        cross apply sys.dm_exec_sql_text(p.sql_handle)
        where p.spid = t2.blocking_session_id
        ) as 'blocker_stmt'
from sys.dm_tran_locks as t1
inner join sys.dm_os_waiting_tasks as t2 on t1.lock_owner_address = t2.resource_address;

A more deeper look into blocking :

-- Pedro Lopes (Microsoft) pedro.lopes@microsoft.com (http://blogs.msdn.com/b/blogdoezequiel/)
-- Waiter and Blocking Report
SELECT -- blocked
    er.session_id AS blocked_spid
    ,ot.task_state AS [status]
    ,owt.wait_type AS blocked_spid_wait_type
    ,owt.wait_duration_ms AS blocked_spid_wait_time_ms
    ,
    -- Check sys.dm_os_waiting_tasks for Exchange wait types in http://technet.microsoft.com/en-us/library/ms188743.aspx.
    -- Wait Resource e_waitPipeNewRow in CXPACKET waits – Producer waiting on consumer for a packet to fill.
    -- Wait Resource e_waitPipeGetRow in CXPACKET waits – Consumer waiting on producer to fill a packet.
    owt.resource_description AS blocked_spid_res_desc
    ,CASE
        WHEN owt.pageid = 1
            OR owt.pageid % 8088 = 0
            THEN 'Is_PFS_Page'
        WHEN owt.pageid = 2
            OR owt.pageid % 511232 = 0
            THEN 'Is_GAM_Page'
        WHEN owt.pageid = 3
            OR (owt.pageid - 1) % 511232 = 0
            THEN 'Is_SGAM_Page'
        WHEN owt.pageid IS NULL
            THEN NULL
        ELSE 'Is_not_PFS_GAM_SGAM_page'
        END AS blocked_spid_res_type
    ,(
        SELECT qt.TEXT AS [text()]
        FROM sys.dm_exec_sql_text(er.sql_handle) AS qt
        FOR XML PATH('')
            ,TYPE
        ) AS [blocked_batch]
    ,es.last_request_start_time AS blocked_last_start
    ,LEFT(CASE COALESCE(er.transaction_isolation_level, es.transaction_isolation_level)
            WHEN 0
                THEN '0-Unspecified'
            WHEN 1
                THEN '1-ReadUncommitted(NOLOCK)'
            WHEN 2
                THEN '2-ReadCommitted'
            WHEN 3
                THEN '3-RepeatableRead'
            WHEN 4
                THEN '4-Serializable'
            WHEN 5
                THEN '5-Snapshot'
            ELSE CONVERT(VARCHAR(30), er.transaction_isolation_level) + '-UNKNOWN'
            END, 30) AS blocked_tran_isolation_level
    ,er.total_elapsed_time / 1000 AS total_elapsed_time_sec
    ,
    -- blocker
    er2.session_id AS blocker_spid
    ,CASE
        -- blocking session is either not blocked or has open trans
        WHEN owt.waiting_task_address IN (
                SELECT owt2.blocking_task_address
                FROM sys.dm_os_waiting_tasks owt2
                )
            AND (
                er2.session_id IS NULL
                OR owt.blocking_session_id IS NULL
                OR owt.[blocking_task_address] IS NULL
                )
            THEN 1
        ELSE 0
        END AS is_head_blocker
    ,(
        SELECT qt2.TEXT AS [text()]
        FROM sys.dm_exec_sql_text(er2.sql_handle) AS qt2
        FOR XML PATH('')
            ,TYPE
        ) AS [blocker_batch]
    ,es2.last_request_start_time AS blocker_last_start
    ,LEFT(CASE COALESCE(er2.transaction_isolation_level, es2.transaction_isolation_level)
            WHEN 0
                THEN '0-Unspecified'
            WHEN 1
                THEN '1-ReadUncommitted(NOLOCK)'
            WHEN 2
                THEN '2-ReadCommitted'
            WHEN 3
                THEN '3-RepeatableRead'
            WHEN 4
                THEN '4-Serializable'
            WHEN 5
                THEN '5-Snapshot'
            ELSE CONVERT(VARCHAR(30), er2.transaction_isolation_level) + '-UNKNOWN'
            END, 30) AS blocker_tran_isolation_level
    ,
    -- other data
    DB_NAME(er.database_id) AS DBName
    ,es.host_name AS blocked_host
    ,es.program_name AS blocked_program
    ,es.login_name AS blocked_login
    ,CASE
        WHEN es.session_id = - 2
            THEN 'Orphaned_distributed_tran'
        WHEN es.session_id = - 3
            THEN 'Deffered_recovery_tran'
        WHEN es.session_id = - 4
            THEN 'Unknown_tran'
        ELSE NULL
        END AS blocked_session_comment
    ,es.is_user_process AS [blocked_is_user_process]
    ,es2.host_name AS blocker_host
    ,es2.program_name AS blocker_program
    ,es2.login_name AS blocker_login
    ,CASE
        WHEN es2.session_id = - 2
            THEN 'Orphaned_distributed_tran'
        WHEN es2.session_id = - 3
            THEN 'Deffered_recovery_tran'
        WHEN es2.session_id = - 4
            THEN 'Unknown_tran'
        ELSE NULL
        END AS blocker_session_comment
    ,es2.is_user_process AS [blocker_is_user_process]
FROM (
    --In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
    --waiting for several different threads.  This will cause that thread to show up in multiple rows
    --which is irrelevant.  Use ROW_NUMBER to select the longest wait for each thread
    SELECT [waiting_task_address]
        ,[session_id]
        ,[wait_duration_ms]
        ,[wait_type]
        ,[blocking_task_address]
        ,[blocking_session_id]
        ,[resource_description]
        ,CASE
            WHEN [wait_type] LIKE 'PAGE%'
                AND [resource_description] LIKE '%:%'
                THEN CAST(RIGHT([resource_description], LEN([resource_description]) - CHARINDEX(':', [resource_description], LEN([resource_description]) - CHARINDEX(':', REVERSE([resource_description])))) AS INT)
            ELSE NULL
            END AS pageid
        ,ROW_NUMBER() OVER (
            PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC
            ) AS row_num
    FROM sys.dm_os_waiting_tasks --ORDER BY session_id
    ) owt
INNER JOIN sys.dm_os_tasks ot ON ot.task_address = owt.waiting_task_address
LEFT OUTER JOIN sys.dm_exec_requests er ON er.session_id = ot.session_id
    AND er.request_id = ot.request_id
LEFT OUTER JOIN sys.dm_exec_sessions es ON es.session_id = er.session_id
LEFT OUTER JOIN sys.dm_exec_sessions es2 ON es2.session_id = owt.blocking_session_id
LEFT OUTER JOIN sys.dm_exec_requests er2 ON er2.session_id = owt.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) est
OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) eqp
WHERE owt.row_num = 1
    AND es.session_id <> @@SPID
    AND es.is_user_process = 1
ORDER BY blocked_spid
    ,is_head_blocker DESC
    ,blocked_spid_wait_time_ms DESC
    ,blocker_spid
GO

Using Extended Event or BLOCKED_PROCESS_REPORT, you can find and get alerted when blocking occurs.


PageIOLatch_Sh waits noticed after tuning an index

From the background information you have provided it sounds like you have moved the contention point on your server from CPU to IO, as a result of implementing an indexing strategy targeted at specific troublesome queries.

For the most part (without getting into the specifics of what exactly your indexing strategy is) this is expected behaviour because with CPU no longer being a contention point, WAITS are naturally occurring elsewhere.

The performance issues you are seeing now are not necessarily caused by the index modifications you have applied. Further investigation into identifying the specific queries that are responsible for the IO waits/as well as the timeouts that you are seeing is required. You want to review the execution plans for each of these scenarios to identify possible areas for optimization (look for typically undesirable operations first such as implicit conversions, table scans etc.).

Not wanting to be presumptuous however I often encounter query timeouts on customer environments due to poor query plan choices as a result of parameter sniffing. This may also be something to look into.

For some handy queries to use during you investigations take a look at Glenn Berry's SQL Server Diagnostic Queries. There are a number of fantastic queries in there for looking at IO, for example which databases are responsible for the most IO, which database data files are seeing the most activity, which queries cause most reads/writes etc.


--Declare both of your CTE first
WITH Numbered AS
(
SELECT
    B.id,
    B.masahat,
    B.tozihat,
    mahsol_name = G.mahsolname,
    ghate_code = G.ghate_code,
    M.cheshme,
    rn = ROW_NUMBER() OVER (
        PARTITION BY B.id
        ORDER BY G.id)
FROM bahrebardarighate AS B
JOIN ghatemanabeabi AS G
    ON B.id = G.bahrebardarighate_id
JOIN manabeabi AS M
    ON M.id = G.manabeabi_id
),--Separating the two CTE declarations
Numbered2 AS
(
SELECT
    A.id,
    A.bahrebardarighate_id,
    fullname = S.fullname,
    nationalcode = S.nationalcode,
    rn = ROW_NUMBER() OVER (
        PARTITION BY A.id
        ORDER BY S.id)
FROM ghate AS A
JOIN shoraka AS S
    ON A.id = s.ghate_id
)

--Then your select/UNION statement goes here, ensuring the two selects have the same number of columns and datatypes.


Transform a Lease relation into Third Normal Form (3NF)

In the real world a person can obviously hold more than one lease at the same time - a primary residence, holiday home and business premises, for example.  Then the primary key of lease would be both PersonID and BuildingID together.  If in your case you artificially constrain this to only one lease per person at a point in time then you are mostly there.  However

You need some sub-division of Building.  Otherwise a person must
lease the entire building.  This may be true for houses; it is
unlikely for office blocks.
You state "a building can charge only one rental rate." Remove rent from LEASE. It can be found via the relationship through BUILDING.
You will likely want a PERSON relation
to hold things like name, date of birth etc.
RENT is only useful if there is some external agency defining a small set of rent values which it is permissible to charge.  If each building's owner can charge what he likes, remove RENT and add an attribute to BUILDING.  Just because Building A's owner charges the same rate as Building B's owner does not make them the same thing.  If Building A's rent changes there is no rule which says Building B's must also change at the same time.
LEASE will require a unique constraint on PersonID to ensure a person does indeed rent only once.


How to make conditional join when containing Nulls in some columns use for join

First of all, your description does not match the ON condition. Your description would fit an ON like this:

Select ...
from ...
     meas_data  m
     left outer join  tlea  t
       on  m.dset_cob_dt = t.tlea_cob_dt
       and m.tlsn_trd_id = t.tlsn_trd_id
       and (
                 m.tlsn_vrsn_num = t.tlsn_vrsn_num
             and m.tlsn_leg_id   = t.tlsn_leg_id
           or
                 m.tlsn_vrsn_num = t.tlsn_vrsn_num
             and m.tlsn_leg_id is null
           or
                 m.tlsn_vrsn_num is null
           )

As for efficiency, I'd suggest a composite index on the 4 columns, on each table (but I'm nowhere near a Oracle expert, so take this suggestion with caution).


Mysql backup from ibdata1, ib_logfile1, ib_logfile0

i re-ran the sql statements in the binary log files on the same DB instance (which gave me a older version of my wordpress tables)

Ouch.  How much hope there was for recovery depended on whether your tables were using the MyISAM or the InnoDB storage engine.  If MyISAM, then... "none," and if InnoDB, then "some."

MyISAM, the older engine, stores its data in files named after the table, with an MYD extension, while InnoDB stores its data either in files named after the table, with an ibd extension, or in the ibdata1 file, which houses the system tablespace, depending on the setting of innodb_file_per_table in configuration.  The definition of each the table (column names, data types, index definitions), for either engine, is stored in files named after the tables, with an frm extension.

The large size of your ibdata1 file suggested that maybe you didn't have innodb_file_per_table enabled, and that some variation of the following steps might have worked, if you were indeed using InnoDB without innodb_file_per_table:

Make a backup copy of everything.
Set up a separate, new MySQL server.
Apply the binlogs you have to the new server, in the interest of recreating the .frm files for the table definitions, and then manually altering any tables to make any changes you recall that aren't accounted for in the older binlogs.
Stop the new server.
Copy directories inside your data directory and their .frm files to the appropriate places on the old server
Start the old server, using innodb_force_recovery if needed, to see how well InnoDB would manage to pair up the .frm files with the table data in ibdata1, which it should, if the table definitions were the same.

Since you have already created new tables, presumably with the same names, on the existing server, any hope for that plan to have succeeded are likely to be eliminated, or significantly diminished to the point that it is unlikely to be worth trying, since your best hope if you tried it would be to see what you are seeing now -- old data that you inserted into tables you created from the binary logs.

So, what's plan B?  To borrow a fitting phrase from the source I'll cite:

  "It is a tedious process that requires an understanding of InnoDB's internal data storage format, C programming, and quite a bit of intuition and experience."

http://www.percona.com/docs/wiki/innodb-data-recovery-tool:start

The Percona InnoDB Data Recovery Tool is the only meaningful approach I can think of that might get you where you need to be... or any comparable tools, assuming there are comparable tools.  I don't see any simple path... you're going to have to go "low level" if you want to rescue your data.

You have deleted important files, and in an effort to recover from the first problem, you have altered the one remaining file that might have been salvageable, and I'm not entirely sure how InnoDB will have handled those abandoned tables in its internal structures.  The data may still be hiding.

There is one reason for optimism, though, and that is in the strings utility.  The link I've provided is not to the genuine GNU "strings" utility, it's Microsoft's analogous semi-equivalent, but the point of this tool is to display strings embedded in files that the internal algorithm thinks might be human-meaningful strings.

There are two uses for this:  one, you can try using it on your ibdata1 file (it only reads, shouldn't modify) to see what your eyeballs tell you about what you find in there.  It offers essentially a brute-force window into the raw content living in the file, and if you find text in there that is newer than anything you put in using the binlog files, that's a good sign that time spent with the Percona tools could be worthwhile because the old data is actually in the file.  If you see nothing at all that you recognize, there may be a problem with the method (though I've verified it works on Linux with genuine GNU "strings"), but if you see old content and not new, then the new content is likely gone.

Of course, the other potential use of "strings" if you do see the content and if it's primarily content that is the most valuable thing you've lost -- not configuration and customization -- then you could just use "strings" to extract the raw content, redirect it into another file, and proceed with a new wordpress installation where you could use that raw text to recreate things.

If the data is "that" important, Percona and SkySQL (I'm not affiliated with either company, but those seem to be the two big players in MySQL space) and other companies offer paid recovery services.


Is ELSE 0 implied in my COUNT CASE WHEN statement?

Simply enough in the first case you are counting 1s & NULLs.  (NULL is returned if none of the conditions in the CASE statement match and there is no ELSE clause.) NULLs don't get counted.  In the second case 1's and 0's.  0's can be counted.

Quick example:

CREATE TABLE #CountMe (Col1 char(1));

INSERT INTO #CountMe VALUES ('A');
INSERT INTO #CountMe VALUES ('B');
INSERT INTO #CountMe VALUES ('A');
INSERT INTO #CountMe VALUES ('B');

SELECT
    COUNT(CASE WHEN Col1 = 'A' THEN 1 END) AS CountWithoutElse,
    COUNT(CASE WHEN Col1 = 'A' THEN 1 ELSE NULL END) AS CountWithElseNull,
    COUNT(CASE WHEN Col1 = 'A' THEN 1 ELSE 0 END) AS CountWithElseZero
FROM #CountMe;

Output:


What to use instead of WITH ORDINALITY in PostgreSQL 9.3

Instead of

SELECT unnest, ordinality
FROM unnest(ARRAY[6,5,4,3,2,1]) WITH ORDINALITY;

you can also write

SELECT unnest, row_number() OVER () AS ordinality
FROM unnest(ARRAY[6,5,4,3,2,1]);

Something like:

...
from s_product
  join (
    select unnest, row_number() over ()
    from unnest(array[....])
  ) as x(shopid, idx)
    on s_product.product_shop_id = x.shopid
....


You need to use excluded as the table alias in the do update section, as follows:

INSERT INTO app.CUSTOMERS(NAME, ADDRESS)
SELECT NAME, ADDRESS
FROM STAGE.STG_CUSTOMERS S
ON CONFLICT (NAME) do update
SET NAME = EXCLUDED.NAME
AND ADDRESS = EXCLUDED.ADDRESS;


Unknown column in 'where clause' when using an alias for a subquery in MySQL

Because the order of operations with the WHERE clause is applied first before the SELECT operator. So n_dataset doesn't exist yet when the WHERE clause is being applied.
You would need to use COUNT() as a window function (version of MySQL depending, you should update your tags to include the version) to get the results you're looking for.
Or you can create your COUNT() query in a CTE first, then re-join to your table later on.
But interpreting your logic further, it sounds like your actual end goal is to get all exams that don't have any correlating datasets. You can accomplish that a lot simpler with a LEFT JOIN this way:
select distinct exam.id as exam_id, 0 as n_dataset
from exam
left join dataset
    on exam.id = dataset.id
where dataset.id is null


You should be able to find the full text log location with the following code:

xp_readerrorlog 0, 1, N'Logging SQL Server messages in file'

I don't think it's possible to split them away from the normal SQL Error log as this connect suggests.


database creation using codeigniter database dbforge class gives "Access denied for user 'username'@'localhost' to database 'database_name' "

Using C-Panel / WHM, you create databases on a per application basis. Let's say that your c-panel username is john123 and you were installing a blog. You'd do the following:

Go to c-panel -> databases -> and create the database john123_blog
Go to databases -> users and create a user to access it john123_blogu
Add the user to the database, and give them the appropriate permissions

At that point, you can use the dbforge class to create tables within the database. In a shared hosting setup, users are often limited to how many databases they can have, so database creation itself has to go through the automation software (in this case, c-panel).

There's nothing wrong with your code, it's just assuming a non-shared environment.


Count the number of days in each month between two specified dates

This looks like a lot of code but I think it steps through and explains how it meets the requirements of counting days in the range, including leaving out days from the next year (in case someone forgets to filter for it first, which the sample usage also handles):

CREATE FUNCTION dbo.ReturnDateRangeByMonth
(
    @start date, @end date
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(
  WITH d(d) AS -- recursive to get all the days in the range
  (
    SELECT @start UNION ALL SELECT DATEADD(DAY, 1, d) FROM d
    -- except the last day
    WHERE d < DATEADD(DAY,-1,@end)
    -- and except any days from the next year
      AND d < DATEFROMPARTS(YEAR(@start)+1, 1, 1)
      -- on < 2012 use AND YEAR(d) = YEAR(@start)
  ),
  m(m) AS -- grab all 12 months to pivot on
  (
    SELECT 1 UNION ALL SELECT m + 1 FROM m WHERE m < 12
  )
  SELECT m.m, c = COALESCE(COUNT(d.d),0) FROM m
  LEFT OUTER JOIN d ON m.m = MONTH(d.d) GROUP BY m.m
);

Sample usage:

DECLARE @vaca TABLE (OOW date, RTW date);

INSERT @vaca(OOW,RTW) VALUES('20191222','20200107'),('20190326','20190528'),
('20190328','20190522'),('20190612','20190617'),('20190404','20190418');

;WITH v AS
(
  SELECT OOW, RTW FROM @vaca WHERE OOW >= '20190101' AND OOW < '20200101'
)
SELECT * FROM v CROSS APPLY dbo.ReturnDateRangeByMonth(v.OOW, v.RTW) AS f
  PIVOT (MAX(c) FOR m IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS p
  OPTION (MAXRECURSION 366);

Results:


innodb , mysql Indexes difference when adding index

Try a no-op ALTER:

ALTER TABLE tablename ENGINE=InnoDB;

(Suggested in this forum.)

(Edits)

I do not know why the problem happened.

Here is another 'fix' that might work:

CREATE TABLE b_new LIKE b;
INSERT INTO b_new
    SELECT * FROM b;
RENAME TABLE b TO b_old, b_new TO b;
DROP TABLE b_old;


SQL Server Management Studio 2012 separated toolbars

The only option close to what you need is to mark the control as "Begin a Group", to create a separator to the left of the control.

Open SSMS > Tools > Customize > Commands tab > under Toolbar select "Standard" > highlight "Activity Monitor" > click Modify Selection and you can (un)tick "Begin a group" and see how the toolbar changes. You could edit the Name to start with something like "__" for visual comfort (doesn't seem to take extra spaces).


SQL Server Log: The database 'master4IDR' is marked RESTORING

if you are using Symantec BackupExec check out this article

How to disable the creation of the $IDR file when backing up a Microsoft SQL server


SQL Server 2012 Publisher SQL Server 2008 Subscriber version issue

It seems That this Problem is due to the SSMS versions. What you could do is create the subscription by right clicking on the Publication.

I assume you are using SQL 2012 as Distributor/Publisher & 2008 as Subscriber.


It may be that tablename isn't actually a table but is another database object that acts like a table.

To find out, run this bit of T-SQL in the database containing tablename:

select schema_name(schema_id),name, type from sys.objects where name='tablename'

This will return rows if it finds for any object called tablename in the database. If it returns 1 single row, and type is U then it is a table and you'll need to see why it's not showing in SSMS. This could be just that it needs refreshing, or your view may be filtered by schema or something else.

If the second column isn't a U then the object isn't a table (ie; it could be a view, or a synonym for another table, etc). There is a lookup list of values here - http://msdn.microsoft.com/en-gb/library/ms190324.aspx - which you can use to identify the object type.


Computed Column Operand type clash on Date Column When Checking For Null

According to the ISNULL documentation the returned data type is the same type as the check expression, which is data type date in this case. The ISNULL expression fails because the integer literal 1 cannot be implicitly converted to date.

To return a bit indicating whether or not the date column is NULL, you could instead use a CASE expression:

CREATE TABLE [history].[PhoneBook](
    [PhoneBookId] [int] IDENTITY(1,1) NOT NULL,
    [PersonId] [int] NOT NULL,
    [PhoneId] [int] NOT NULL,
    [DateActive][Date] NOT NULL,
    [DateInActive][Date] NULL,
    [IsValid] AS CASE WHEN DateInActive IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS bit) END
 CONSTRAINT [PK_PhoneBook_1] PRIMARY KEY CLUSTERED
(
    [PersonId] ASC,
    [PhoneId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


How to change the path of data during database restore + SQL Server 2008

You can redirect the data files using the move command

USE [master]
RESTORE DATABASE [{DBName}]
FROM  DISK = N'{PathToBakFile}'
WITH  FILE = 1,
MOVE N'{DataFileLogicalName}'
    TO N'{NewPath}',
MOVE N'{LogFileLogicalName}'
    TO N'{NewPath}'
GO