Find Queries that Are Causing Waits Given a certain kind of wait, how do you find which queries are causing those waits safely on a production SQL 2008 R2 Server?

In this particular case I am wondering about async_network_io.

My favorite way to check these is to use Adam Machanic's excellent sp_WhoIsActive stored proc.  Here's a video on how to use it and a link to download the code:

http://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/

The catch is that you have to run it periodically to check for results.  If you want this data to be gathered for you periodically, check out Kendra Little's tutorial on capturing sp_WhoIsActive results to a table:

http://www.littlekendra.com/2011/02/01/whoisactive/

Finally, if you want something to fire whenever a query waits for async_network_io, you can use a new tool called Extended Events.  It's like debug points inside the SQL Server engine where you can make magic happen.  Frankly, it's a little painful to use right now in 2008.


How to restore database from /var/lib/mysql I have a database folder from a crashed server called gain_wordpress. I'm trying to put that database on a new server but I'm not able to do it successfully. I don't have a .sql dump, all I have is the database folder from a past crashed server with ibdata and the 2 log files.

I copied the gain_wordpress folder to /var/lib/mysql on the new server but when I do a > show tables on the database I get the following error:

ERROR 1018 (HY000): Can't read dir of './gain_wordpress/' (errno: 13)

I also changed the permission of the folder and did a chown mysql:mysql gain_wordpress -R and it still didn't take care of the problem.

What is the best way to recover gain_wordpress. Do I need to replace any of the ibdata or the logfiles? What is the best way? I have the corresponding .frm, .MYD and .MYI files for each table. Please advise.

you need to make sure the ownership and privileges are correct for the directory, you can use the following commands to set it right:

chown -R mysql:mysql /var/lib/mysql/ --your mysql user may have different name, if its different you need to change it [mysql:mysql] as needed
chmod -R 755 /var/lib/mysql/

note: -R: recursive fir the directory and all its contents.


How to get the AD account associated to a Proxy? on the picture below, you can see, I have a sql server job called "Backup SSAS DBS_" that Run as:
PowerShell_Proxy

How can I find out the AD (active directory) account associated with that Proxy via T-SQL?

SO far I have this script below, but something is missing. Also, the AD account might not be a login or user in the current server.

USE [msdb]
GO

select
s.job_id
,s.name as [job_name]
,js.step_id
,js.step_name
,js.subsystem
,js.proxy_id
,js.command
from sysjobs S
INNER JOIN sysjobsteps JS ON S.job_id = js.job_id
LEFT OUTER JOIN sysproxylogin p ON js.proxy_id = p.proxy_id

WHERE S.NAME LIKE '%' + 'SSAS' + '%'

Using the query below I can get the SID used by the proxy.

select * from sysproxylogin

I think this is what you need.

select
    s.job_id
    ,s.name as [job_name]
    ,js.step_id
    ,js.step_name
    ,js.subsystem
    ,js.proxy_id
    ,js.command
    , c.credential_identity
from sysjobs S
INNER JOIN sysjobsteps JS ON S.job_id = js.job_id
INNER JOIN sysproxies p ON js.proxy_id = p.proxy_id
INNER JOIN sys.credentials as c on c.credential_id = p.credential_id

The proxy_id in sysjobsteps comes from sysproxies. You then get the account from credential_id in sys.credentials which is also available in sysproxies.


SQL Server AlwaysOn Error:35250 I am configuring AlwaysOn in my test environment but getting the following error.

Msg 35250, Level 16, State 7, Line 1
The connection to the primary replica is not active.  The command cannot be processed.

I followed the below link to solve the issue but no luck

https://blogs.msdn.microsoft.com/alwaysonpro/2013/12/09/create-availability-group-fails-with-error-35250-failed-to-join-the-database/

I checked the SQL LOG for error in node1 appears as

  The Service Broker endpoint is in disabled or stopped state.

checked the same at node2 for error appears as below:

  Database Mirroring login attempt failed with error: 'Connection
  handshake failed. An OS call failed: (80090350) 0x80090350(The system
  cannot contact a domain controller to service the authentication
  request. Please try again later.). State 66.'.  [CLIENT: my server IP
  address(node1)].

Kindly check and suggest to move forward.

This is login and permission related error. What kind of account are you using to connect when you build the AOA features? If you are using a domain account you can fix this error it in two ways.

Use the same domain account on all of the participating replicas. If you can't (allowed) change the account you could use the second option
Add the service accounts that you are using on participating servers into the primary replica.

N.B. make sure those accounts have at least a connect permission to the instances


SQL Server stored procedure validity checking How can I check the validity of a stored procedure programmatically? Meaning that if no errors are found it will execute. How can I detect things like invalid tables names in "from" or "join" clauses?

It depends on how strictly you are defining "programmatically". If you mean a process that knows nothing of the proc it is evaluating, then you aren't going to get very far. In addition to the obstacles mentioned by @Aaron in his answer (i.e. Linked Servers, Dynamic SQL, OPENQUERY / OPENROWSET, deferred name resolution, etc), you also have the issue of permissions. The table names being correct doesn't help if the user cannot access them :).

An even bigger issue is the conditional execution of blocks of code. Consider the following test:

First we run it such that the conditional block is skipped.

DECLARE @RandomCondition INT,
        @Test INT;

SET @RandomCondition = 1;

IF (@RandomCondition = 2)
BEGIN
    SET @Test = 'a';
END;
SELECT @RandomCondition AS [RandomCondition];
GO

This encounters no errors and returns a result set of 1 row.

Second time around we run it such that the conditional block is entered.

DECLARE @RandomCondition INT,
        @Test INT;

SET @RandomCondition = 2;

IF (@RandomCondition = 2)
BEGIN
    SET @Test = 'a';
END;
SELECT @RandomCondition AS [RandomCondition];
GO

This encounters the following error (as expected):

  Msg 245, Level 16, State 1, Line 9
  Conversion failed when converting the varchar value 'a' to data type int.

So you really can't even loop through a list of procs and run them by passing NULL in for all input params as different values for a particular parameter might cause it to take several different, and non-overlapping, paths.

On the other hand, if you are defining "programmatically" to mean "an automated process", then there is hope.

You really need to set up integration testing where you can set up the environment (if need be), execute it with specific parameter values, and test the output and/or any expected changes to the environment. AND you can set up as many tests for the same stored procedure as is required by the nature of each stored procedure.

The tool I prefer to use for such testing is DbFit. It has been a few years since I have used it, but it was soooo easy to use. Unlike some of the other SQL testing frameworks, it is completely unobtrusive, requiring no changes to your database(s). The tests are defined in a wiki so it is easy to create and update them. You can group tests together and even use variables across several pages (such as connection string info, etc). You can also automate it via command-line.

Even if you don't use DbFit, you should still have some testing framework as anything you can test for programmatically (in a strict sense) isn't worth the time it will take to set it up; it will catch such a small percentage of problems that you will still need something else to catch the other 80% - 90%.


Clean up all but current backupset details When backing up a Microsoft SQL Server database, even if I specify to backup to a new backup set and "Overwrite all existing backup sets", or even to a new media set, the msdb backupset/mediaset tables still retain the history of all previous backupsets. My questions are as follow:

What does it mean to "Overwrite all existing backup sets" if the history is preserved? Does it simply mean that it will delete other backupsets from the media?
How can I cleanup backup history for the database for all but current mediaset/backupset?
Related to 2) how do I select only "current" records from backupset/mediaset tables? I don't see any column indicating this. Does it mean I have to sort by date and somehow decide that this is the "current" one?

MSDB retains history of backups purely for informational purposes.  This history does not affect the ability to perform restores in any way.

To see backup history, I use a query like this:

DECLARE @DBName SYSNAME;
DECLARE @HistoryDays INT;
SET @HistoryDays = 7; --limit the display to backups taken in the past 7 days.
SET @DBName = DB_NAME(); -- modify these as you desire.
SET @DBName = NULL; -- comment this line if you want to limit the displayed history
--SET @DBName = 'some_db_name';
;WITH src AS
(
SELECT DatabaseName = bs.database_name
    , BackupStartDate = bs.backup_start_date
    , CompressedBackupSizeMB = bs.compressed_backup_size / 1048576
    , ExpirationDate = bs.expiration_date
    , BackupSetName = bs.name
    , RecoveryModel = bs.recovery_model
    , ServerName = bs.server_name
    , BackupType = CASE bs.type
            WHEN 'D' THEN 'Database'
            WHEN 'L' THEN 'Log'
            ELSE '[unknown]' END
    , LogicalDeviceName = bmf.logical_device_name
    , PhysicalDeviceName = bmf.physical_device_name
    , rn = ROW_NUMBER() OVER (PARTITION BY bs.database_name
                               ORDER BY bs.backup_start_date DESC)
FROM msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
WHERE (bs.database_name = @DBName
    OR @DBName IS NULL)
    AND bs.type = 'D'
    AND bs.backup_finish_date >= DATEADD(DAY, 1 - @HistoryDays, GETDATE())
)
SELECT src.DatabaseName
    , src.BackupStartDate
    , CompressedBackupSizeMB = CONVERT(INT, src.CompressedBackupSizeMB)
    , src.ExpirationDate
    , src.BackupSetName
    , src.RecoveryModel
    , src.ServerName
    , src.BackupType
    , src.LogicalDeviceName
    , src.PhysicalDeviceName
FROM src
WHERE src.rn = 1
     AND src.DatabaseName NOT IN (
          'master'
        , 'model'
        , 'msdb'
        , 'tempdb'
     )
ORDER BY src.BackupStartDate;

As you can see, I'm ordering the results by msdb.dbo.backup_set.backup_start_date -> the last row will be the most recently taken backup, which might or might not be the one I will restore in a disaster recovery effort.  As the old saying goes, "you can never have too many backups".  I may very well need to restore a backup from prior to the last backup for any number of reasons, including perhaps database corruption, user error, malicious intent, etc.  The concept of "current" backups does not really exist since the backup file may or may not exist at the time of restore.  If I take a backup now, to a disk that fails two minutes after the backup completes, what is the "current" backup?

You can use sp_delete_backuphistory to remove backup history from msdb:

DECLARE @oldestDate datetime;
SET @oldestDate = CONVERT(datetime, '2016-12-01T00:00:00');
EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @oldestDate;

Modify @oldestDate to whatever time period you like.


Maintenance plan using cursor statement I have a cursor which gives parameters to columns to executes the stored procedure in order to update my table. Cursor statement is following:

declare @ColumnName nvarchar(30)
declare @ColumnID int

declare UpdateCurs cursor
for
select ColumnName , ColumnID from MyTable

open UpdateCurs

fetch next from UpdateCurs into @ColumnName , @ColumnID

while @@FETCH_STATUS = 0
BEGIN

  execute dbo.UpdateMyTable @ColumnName , @ColumnID

fetch next from UpdateCurs into @ColumnName , @ColumnID

END

close UpdateCurs
deallocate UpdateCurs

When I execute the statement directly, it works fine, but when I create maintenance plan via "Execute T-SQL statement" and schedule job, the job results with  following error:

  failed with the following error: "Invalid column name 'ColumnID'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

One thing I can`t understand that, if there is something wrong with my statement, why it is executed without problems directly and gives error on Maintenance plan ?
I will appreciate your thoughts on this

Make sure you are either connecting to the correct database (USE <database>) or using fully qualified names in your maintenance plan.


Sudden PRIMARY KEY violation on IDENTITY column I have a log table with an ID identity column. Everything works fine for years; then, yesterday, I see the following error in the logs:

  The statement has been terminated.
  Violation of PRIMARY KEY constraint 'PK__log__ID__3B40CD36'. Cannot insert duplicate key in object 'dbo.log'. The duplicate key value is (295992).

  INSERT INTO log (datum, zeit, benutzer, modul, prozedur, code, zeile, bez1, bez2, tech_info) VALUES ('20151126 00:00:00.000', '19000101 18:26:45.121', 'Customer', '', '', 'WShop-Trans', 0, '1 Datensätze für Tabelle adresse gesendet.', '', '')

I checked the IDENTITY seed, and it looks OK:

  Query: DBCC CHECKIDENT(log)

  Result: Checking identity information: current identity value '296021', current column value '296021'.
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  Query: SELECT MAX(ID) FROM log

  Result: 296021

There are no triggers on the table, and nobody is messing around with the seed values (I'm the one administering the database server, so I'm pretty sure about that).

So far, this is a one-time event, and I cannot reproduce it.

I looks like just an SQL Server glitch, but I'm curious: Is this a known bug, or is there any other plausible explanation for this? The SQL Server version is Microsoft SQL Server 2012 - 11.0.2100.60 (X64).

For completeness, here's the complete table script:

CREATE TABLE [log](
    [datum] [datetime] NULL,
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [zeit] [datetime] NULL,
    [benutzer] [varchar](255) NULL,
    [modul] [varchar](255) NULL,
    [prozedur] [varchar](255) NULL,
    [code] [varchar](255) NULL,
    [zeile] [int] NULL,
    [bez1] [text] NULL,
    [bez2] [text] NULL,
    [tech_info] [text] NULL,
    [pc_name] [varchar](255) NULL,
    [app_name] [varchar](255) NULL,
    [s_insert_user] [nvarchar](255) NULL,
    [s_insert_dat] [datetime] NULL,
    [s_update_user] [nvarchar](255) NULL,
    [s_update_dat] [datetime] NULL,
    [fs_mandant] [uniqueidentifier] NULL,
 CONSTRAINT [PK__log__ID__3B40CD36] PRIMARY KEY CLUSTERED ([ID] ASC)
)

CREATE NONCLUSTERED INDEX [code] ON [log] ([code] ASC)
CREATE NONCLUSTERED INDEX [datum_zeit] ON [log] ([datum] ASC, [zeit] ASC)
CREATE NONCLUSTERED INDEX [fs_mandant] ON [log] ([fs_mandant] ASC)
CREATE NONCLUSTERED INDEX [modul] ON [log] ([modul] ASC)

Since the question states SQL Server 2012 RTM (build 2100) is in use, it is likely this bug:

FIX: Sequence object generates duplicate sequence values when SQL Server 2012 or SQL Server 2014 is under memory pressure

which says:

  Assume that you create a sequence object that has the CACHE option enabled in Microsoft SQL Server 2012 or SQL Server 2014. When the instance is under memory pressure, and multiple concurrent connections request sequence values from the same sequence object, duplicate sequence values may be generated. In addition, a unique or primary key (PK) violation error occurs when the duplicate sequence value is inserted into a table.

Note that IDENTITY uses the sequence object mechanism in SQL Server 2012 and later.

The issue was first fixed in:

Cumulative Update 6 for SQL Server 2014
Cumulative Update 4 for SQL Server 2012 SP2


Historical SQL Monitoring Is there a built-in Oracle feature that extends Real-Time SQL Monitoring to
 historical activity?  I am specifically interested in getting wait events aggregated at the SQL operation level, not just per-statement.

Operation level information is available in DBA_HIST_ACTIVE_SESS_HISTORY.  Many AWR reports use that table, but do any of them display information at the operation level?  Currently I am getting this data by joining DBA_HIST_ACTIVE_SESS_HISTORY and DBMS_XPLAN.DISPLAY_AWR, but it seems like there should be a built-in feature to do this for me.  Before I build my own tool I want to ensure someone else hasn't already built it.

This missing feature may sound like a minor inconvenience but using the actual execution statistics, or even a sample of them, makes tuning so much easier.  For example, below is a real SQL Monitoring Report for a recent problem, modified to fit here.  With operation-level information there was no need to guess which step was slow, or even examine the waits.  The problem was quickly found - the indexes were not disabled before a large INSERT.

SQL Monitoring Report

SQL Text
------------------------------
<SQL statement...>

Global Information
------------------------------
<metadata like run status, session, sql_id, duration, etc...>

Parallel Execution Details (DOP=64 , Servers Allocated=128)
...

SQL Plan Monitoring Details (Plan Hash Value=2062479242)

=================================================================================
| Id |     Operation          | Activity |          Activity Detail             |
|    |                        |   (%)    |            (# samples)               |
=================================================================================
...
|  1 |   PX COORDINATOR       |     0.19 | enq: KO - fast object checkpoint (21)|
|  2 |    PX SEND QC (RANDOM) |          |                                      |
|  3 |     INDEX MAINTENANCE  |    84.70 | buffer busy waits (2801)             |
|    |                        |          | enq: HW - contention (29)            |
|    |                        |          | enq: TX - allocate ITL entry (25)    |
|    |                        |          | free buffer waits (61734)            |
|    |                        |          | write complete waits (10365)         |
|    |                        |          | Cpu (341)                            |
|    |                        |          | enq: FB - contention (1632)          |
|    |                        |          | enq: TX - contention (53)            |
|    |                        |          | db file sequential read (3717)       |
|    |                        |          | read by other session (641)          |
|  4 |      PX RECEIVE        |     0.02 | Cpu (18)                             |
|    |                        |          | resmgr:cpu quantum (1)               |
...
=================================================================================

There are some other methods that produce similar information, such as tracing, /*+ gather_plan_statistics */, or alter session set statistics_level=all;.  But those methods are only useful in a development environment.  I don't have the luxury of being able to re-run statements with different settings.

Oracle 12c has a Performance Hub which includes Monitored SQL in historical mode.  Although I haven't tested it, and based on some open bugs I doubt it works 100% of the time.

For 11g, or when the Performance Hub is not available or not working, I have created the open source program Historical SQL Monitoring (hist_sql_mon):

Here's an example of how to use it:

select hist_sql_mon.hist_sql_mon(
    p_sql_id            => '2ssrz4j1m39wx',
    p_start_time_filter => date '2014-09-25',
    p_end_time_filter   => sysdate - interval '1' day)
from dual;

The primary output is a CLOB containing an execution plan with a count and distinct count of events.

-------------------------------------------------------------
|Id  | Operation              | Name   | Rows  | Cost (%CPU)| Event (count|distinct count)
-------------------------------------------------------------
|  0 | SELECT STATEMENT       |        |       | 83031 (100)|
|  1 |  SORT AGGREGATE        |        |     1 |            |
|  2 |   HASH JOIN RIGHT OUTER|        |  8116K| 83031   (4)| Cpu (25|25)
|  3 |    INDEX FULL SCAN     | I_USER2|   155 |     1   (0)|
|  4 |    NESTED LOOPS OUTER  |        |  8116K| 82993   (4)| Cpu (1|1)
|  5 |     NESTED LOOPS OUTER |        |  8116K| 10702  (24)| Cpu (2|2)
...

The functions also print the SQL statement to DBMS_OUTPUT. The bind variables are replaced with hard-coded values so the query can run anywhere. This can help with debugging or creating your own queries.

----------------------------------
--Historical SQL Monitoring Report
----------------------------------
--Execution plans and ASH data, where there are some samples for a plan_hash_value.
select
    --Add execution metadata.
    case
        when plan_table_output like 'Plan hash value: %' then
...


Why is optimistic locking faster than pessimistic locking? Both forms of locking cause a process to wait for a correct copy of the record if its currently in use by another process. With pessimistic locking, the lock mechanism comes from the DB itself (a native lock object), whereas with optimistic locking, the lock mechanism is some form of row versioning like a timestamp to check whether a record is "stale" or not.

But both cause a 2nd process to hang. So I ask: why is optimistic locking generally considered faster/superior than pessimistic locking? And, are there are use cases where pessimistic is preferred over optimistic? Thanks in advance!

You misunderstand optimistic locking.

Optimistic locking does not cause transactions to wait for each other.

Optimistic locking possibly causes a transaction to fail, but it does so without any "lock" ever having been taken.  And if a transaction fails because of optimistic locking, the user is required to start all over again.  The word "optimistic" derives from exactly the expectation that the condition that causes transactions to fail for this very reason, will occur only very exceptionally.  "Optimistic" locking is the approach that says "I will not be taking actual locks because I hope they won't be needed anyway.  If it turns out I was wrong about that, I will accept the inevitable failure.".


slow query on a partitioned table I have a huge table with 3B of rows containing around 6 months of data running on Microsoft SQL server 2017 (RTM), which is partitioned day by day on a date column (each day on a separate filegroup and each filegroup has 1 data file).
this table has an identity field which is bigint.
I have two indexes :
a clustered index on date and id
a non clustered index on date
I'm trying to run the following query :
select top 500000 * from table with(nolock) where id>@certain_id order by id

but the query is taking a lot of time. I tried to create an non clustered index on id field , but nothing changes!
the weird part I was able to run the same query with no issues and with fast response in the past. But due to some circumstances  I had to format the server and re-attach the database containing the partitioned table, and I'm now having this issue.
any hint is much appreciated.

Let's try this query:
SELECT TOP 1000 A.id
FROM  sys.partitions AS P
   CROSS APPLY ( SELECT TOP 1000 T1.id
          FROM dbo.table AS T1
          WHERE $PARTITION.PF1(T1.date) = P.partition_number
           AND T1.id > @certain_id
           ORDER BY T1.id ) AS A
  WHERE P.object_id = OBJECT_ID('dbo.table')
  AND P.index_id = INDEXPROPERTY( OBJECTID('dbo.table'), 'idx_c1', 'INDEXID')
    ORDER BY A.id;

from here: https://support.microsoft.com/en-in/help/2965553/decreased-performance-for-sql-server-when-you-run-a-top-max-or-min-agg


Update Query With Count I am attempting to run the below update query

UPDATE x
SET totalval = COUNT(projs)
FROM Prs x
LEFT JOIN es y
ON x.CS = y.CS

However, I am getting the error:

  Msg 157, Level 15, State 1, Line 2
  An aggregate may not appear in the set list of an UPDATE statement.

How should this statement be altered in order for it to be valid update statement?

You need to do your aggregation separately in a CTE or derived table. Here's a CTE version that should work for you.

WITH    t1
          AS ( SELECT y.CS, COUNT(y.projs) AS records
                FROM es AS y
                GROUP BY y.CS)
     UPDATE x
        SET x.totalval = t1.records
        FROM Prs AS x
        LEFT JOIN t1
        ON  t1.CS = x.CS


Cannot connect to a SQL Server 2014 Express database intermittently I have problems connecting computers running Windows 7 to a database SQL Server 2014 Express intermittently.

It happens that, intermittently, I can not connect to the database that is on a Windows 2008 R2 server.

If I restart the computer, works again, but only for a few minutes, sometimes hours.

The Firewall of my computer is turned off. Windows Firewall is turned off.

CONFIGURATION:

The server is Windows Server 2008 R2 Foundation
Firewall Ports TCP / IP and UDP 1434 1433 allowed

SQL Server Express:

Unlimited Connections
Allows remote connections
Timeout remotely query: 600 seconds

Machines with Windows XP have no problem with this issue.

Finally, it seems to have turned the problem: an IP in the secondary DNS configuration caused the problem.
Removing the IP, everything works correctly again.


Can SQL use an index while creating another? I just had a situation where I had to include a column in an index. This required dropping the index, and recreating it. This got me thinking, surely that's a lot of unnecessary work.

Say I had instead created the new index first, then dropped the old one. Let's also assume that I have some way of indicating that the old index will not be dropped until after the new one has been created.

Would the server get any performance benefit by using the old index while creating the new one?

You can do

CREATE TABLE T(A INT, B INT, C INT)

INSERT INTO T
SELECT 1,1,1
FROM master..spt_values

GO

CREATE INDEX ix ON T(A);

GO

--This won't benefit from existing index as noncovering
CREATE INDEX ix ON T(A) INCLUDE (B) WITH(DROP_EXISTING = ON);

GO

--This can benefit from existing index
CREATE INDEX ix ON T(A)  WITH(DROP_EXISTING = ON);

GO

DROP TABLE T;

There is no particular advantage of doing so in the first case.

Whilst the existing index provides the desired sort order it will not be used for this as it does not contain the newly added column B and so it would require a lookup back to the base table for each row. The plan shows a table scan and sort.

The use of DROP_EXISTING here could provide benefit if you were removing an included column though - as in the second DROP_EXISTING example (as the original index would both have the desired order and cover all columns).

The plan for this shows that the original index was read to create the replacement.


Stored procedure not working I am creating a procedure in a new database. There is no trigger on any table and no procedures exist. I am getting this error:

  ERROR 1303 (2F003): Can't create a PROCEDURE from within another stored routine

What does this mean? How do I solve it?

Just what it means. Some commands cannot be invoked from within stored routines. Creation of a stored routine is one of them. As another example, you cannot LOAD DATA INFILE from a stored routine.

You may try and do it with a prepared-statement, only to find that CREATE PROCEDURE cannot be invoked via prepared statement.

Sorry - no way around this that I know, and I've done my share of search on this.


What corner cases exist when relying on undocumented behaviour to determine values selected by MySQL for hidden columns in GROUP BY operations? Under GROUP BY and HAVING with Hidden Columns, the MySQL manual documents (added emphasis):

  MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

Despite this explicit warning from the developers, some people continue to rely on undocumented behaviour to determine the value that will be selected from a hidden column.

In particular, MySQL often appears to select the "first" record from each group (where the notion of "first" is itself undocumented, such as the oldest record on some storage engines or according to some sort order applied to a materialised table from a subquery).  I've seen this exploited to retrieve, for example, a groupwise maximum:

SELECT * FROM (
  SELECT * FROM my_table ORDER BY sort_col DESC
) t GROUP BY group_col

For completeness, the same can be accomplished in a standard and documented fashion with a simple join:

SELECT * FROM my_table NATURAL JOIN (
  SELECT   group_col, MAX(sort_col) sort_col
  FROM     my_table
  GROUP BY group_col
) t

I believe that one should never rely on undocumented behaviour because there may be unforeseen corner cases that cause that behaviour to break.  For example, in satisfying a GROUP BY operation with an index, MySQL sorts the results and may thereby choose an unexpected value.

What other corner cases can break this behaviour?  Or is it sufficiently reliable for production systems?

I was thinking the NATURAL JOIN example you just used

SELECT * FROM my_table NATURAL JOIN (
  SELECT   group_col, MAX(sort_col) sort_col
  FROM     my_table
  GROUP BY group_col
) t

If you shift to another type of JOIN and impose WHERE, ordering can come and go without warning in spite of the ill-advised reliance on undocumented behavior of the GROUP BY.

For this example, I will

use Windows 7
use MySQL 5.5.12-log for Windows
create some sample data
impose a LEFT JOIN without a WHERE clause
impose a LEFT JOIN with a WHERE clause

For the DB Environment

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.12-log |
+------------+
1 row in set (0.00 sec)

mysql> show variables like '%version_co%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86                          |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
3 rows in set (0.00 sec)

mysql>

Using this script to generate sample data

DROP DATABASE IF EXISTS eggyal;
CREATE DATABASE eggyal;
USE eggyal
CREATE TABLE groupby
(
    id int not null auto_increment,
    num int,
    primary key (id)
);
INSERT INTO groupby (num) VALUES
(floor(rand() * unix_timestamp())),(floor(rand() * unix_timestamp())),
(floor(rand() * unix_timestamp())),(floor(rand() * unix_timestamp())),
(floor(rand() * unix_timestamp())),(floor(rand() * unix_timestamp())),
(floor(rand() * unix_timestamp())),(floor(rand() * unix_timestamp()));
INSERT INTO groupby (num) SELECT num FROM groupby;
SELECT * FROM groupby;

and these two queries for testing the GROUP BY subsequent use;

SELECT * FROM groupby A LEFT JOIN
(
    SELECT   num, MAX(id) id
    FROM     groupby
    GROUP BY num
) B USING (id);
SELECT * FROM groupby A LEFT JOIN
(
    SELECT   num, MAX(id) id
    FROM     groupby
    GROUP BY num
) B USING (id) WHERE B.num IS NOT NULL;

Let's test the durability of the GROUP BY's results;

STEP 01 : Create the Sample Data

mysql> DROP DATABASE IF EXISTS eggyal;
Query OK, 1 row affected (0.09 sec)

mysql> CREATE DATABASE eggyal;
Query OK, 1 row affected (0.00 sec)

mysql> USE eggyal
Database changed
mysql> CREATE TABLE groupby
    -> (
    ->     id int not null auto_increment,
    ->     num int,
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO groupby (num) VALUES
    -> (floor(rand() * unix_timestamp())),(floor(rand() * unix_timestamp())),
    -> (floor(rand() * unix_timestamp())),(floor(rand() * unix_timestamp())),
    -> (floor(rand() * unix_timestamp())),(floor(rand() * unix_timestamp())),
    -> (floor(rand() * unix_timestamp())),(floor(rand() * unix_timestamp()));
Query OK, 8 rows affected (0.06 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> INSERT INTO groupby (num) SELECT num FROM groupby;
Query OK, 8 rows affected (0.05 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM groupby;
+----+------------+
| id | num        |
+----+------------+
|  1 |  269529129 |
|  2 |  387090406 |
|  3 | 1126864683 |
|  4 |  411160755 |
|  5 |   29173595 |
|  6 |  266349579 |
|  7 | 1244227156 |
|  8 |    6231766 |
|  9 |  269529129 |
| 10 |  387090406 |
| 11 | 1126864683 |
| 12 |  411160755 |
| 13 |   29173595 |
| 14 |  266349579 |
| 15 | 1244227156 |
| 16 |    6231766 |
+----+------------+
16 rows in set (0.00 sec)

STEP 02 : Use LEFT JOIN without a WHERE clause

mysql> SELECT * FROM groupby A LEFT JOIN
    -> (
    ->     SELECT   num, MAX(id) id
    ->     FROM     groupby
    ->     GROUP BY num
    -> ) B USING (id);
+----+------------+------------+
| id | num        | num        |
+----+------------+------------+
|  1 |  269529129 |       NULL |
|  2 |  387090406 |       NULL |
|  3 | 1126864683 |       NULL |
|  4 |  411160755 |       NULL |
|  5 |   29173595 |       NULL |
|  6 |  266349579 |       NULL |
|  7 | 1244227156 |       NULL |
|  8 |    6231766 |       NULL |
|  9 |  269529129 |  269529129 |
| 10 |  387090406 |  387090406 |
| 11 | 1126864683 | 1126864683 |
| 12 |  411160755 |  411160755 |
| 13 |   29173595 |   29173595 |
| 14 |  266349579 |  266349579 |
| 15 | 1244227156 | 1244227156 |
| 16 |    6231766 |    6231766 |
+----+------------+------------+
16 rows in set (0.00 sec)

mysql>

STEP 03 : Use LEFT JOIN with a WHERE clause

mysql> SELECT * FROM groupby A LEFT JOIN
    -> (
    ->     SELECT   num, MAX(id) id
    ->     FROM     groupby
    ->     GROUP BY num
    -> ) B USING (id) WHERE B.num IS NOT NULL;
+----+------------+------------+
| id | num        | num        |
+----+------------+------------+
| 16 |    6231766 |    6231766 |
| 13 |   29173595 |   29173595 |
| 14 |  266349579 |  266349579 |
|  9 |  269529129 |  269529129 |
| 10 |  387090406 |  387090406 |
| 12 |  411160755 |  411160755 |
| 11 | 1126864683 | 1126864683 |
| 15 | 1244227156 | 1244227156 |
+----+------------+------------+
8 rows in set (0.00 sec)

mysql>

ANALYSIS

Looking at the aforementioned results, here are two questions:

Why does a LEFT JOIN keep an ordering by id ?
Why in the world did using a WHERE impose a reordering ?

Was it during the JOIN phase ?
Did the Query Optimizer look ahead at the ordering of the subquery or ignore it ?

No one foresaw any of these effects because the behavior of explicit clauses was relied upon by the implicit behavior of the Query Optimizer.

CONCLUSION

From my perspective, corner cases can only be of an external nature. In light of this, developers must be willing to fully evaluate the results of a GROUP BY in conjunction with the following twelve(12) aspects:

aggregate functions
subquery usage
JOINs clauses
WHERE clauses
sort order of results with no explicit ORDER BY clause
query results using older GA releases of MySQL
query results using newer beta releases of MySQL
the current SQL_MODE setting in my.cnf
the operating system the code was compiled for
possibly the size of join_buffer_size with respect to its effect on the Query Optimizer
possibly the size of sort_buffer_size with respect to its effect on the Query Optimizer
possibly the storage engine being used (MyISAM vs InnoDB)

Here is the key thing to remember : Any instance of MySQL that works for your query in a specific environment is itself a corner case. Once you change one or more of the twelve(12) evaluation aspects, the corner case is due to break, especially given the first nine(9) aspects.


DELETE inside INSERT Trigger I wanted to make rotation inside my logs table to store last 100 rows only, and decided to use triggers, as in

create trigger rotate_log
after insert on logs
for each row begin
declare
  cnt int;
  set cnt = (select count(*) from logs);
  if cnt >100  then
    delete from logs limit 1;
  end if;
end

But it doesn't works because of deadlock caused by insert statement. So how to rewrite this with less work?

I am using mysql Ver 15.1 Distrib 10.1.35-MariaDB, for FreeBSD11.2

You cannot alter the same table in trigger.

Use rotate (update the oldest record) instead of add and delete. Insert 100 records into your table (if there is no enought records in it) with their timestamp in far past and use

UPDATE logs
SET field1=value1, field2=value2 -- , ...
ORDER BY record_timestamp ASC
LIMIT 1;

The records count will be always unchanged. To increase it simply add the amount of records you need with timestamp in far past, to decrease - delete the amount of oldest records you do not need.

And you do not need in trigger at all...


Not able to select the disk during Oracle 11g Installation Hi All, During the 11g installation on Win 2012, I am not able to select the available disks which are already stamped using ASM. Any idea what could be the reason? I have tried to select using keyboard space as well but doesn't work out. Appreciate your help. Thanks.

You should check if it is not a ownership issue. The disks should be owned by Oracle.

A common workaround to this issue is to delete and recreate the disks from scratch.


Slow query - big difference between estimated and actual rows At my work, we have a problem with a search query.

If I don't include the lookup into Table_39 (see below query), it takes 0 seconds to execute the query.
If I include the lookup (..AND (Not Exists(Select 1 From Table_39 ll ..), it takes 4 minutes and 57 seconds.

From the execution plan I can see that there is a huge discrepancy between number of expected rows and actual rows, especially in the index for Table_39 (62.6 estimated vs. 10,956,165), so I tried running UPDATE STATISTICS WITH FULLSCAN, but it didn't make any difference.

I would appreciate any input I can get on how to improve the performance of the query that includes the lookup!

Query:

select [Table_2].Gid
  into #Table_1
  from [Table_2]
where [Table_2].Gid_Line in ('{31445a8f-900b-4d64-b72f-4e2ac10fd6a7}')
union
select '{31445a8f-900b-4d64-b72f-4e2ac10fd6a7}' as Gid

create unique nonclustered index temp_Table1 on #Table_1 (Gid)
go

select top 500 Table_9.Id
 , Table_9.Gid
 , Table_9.name
 , Table_9.TimeEnter
 , Table_9.Prio
 , Table_9.Direction
 , Table_9.status
 , Table_9.CreaterId
 , Table_9.Subject
 , Table_9.BodyType
 , Table_9.Attach
 , Table_9.FromAddr
 , Table_9.ToAddr
 , Table_9.Gid_LineSet
 , Table_9.xml
from Table_9
where Table_9.Gid_LineSet in (
    select Gid
      from #Table_1
      )
  and not exists (
   select 1
   from Table_39 ll
   where ll.Id_Obj_To      = Table_9.Id
    and ll.Id_ObjType_To   = 5
    and ll.Id_ObjType_From = 3
    and ll.Id_Obj_From in (
        9602, 10661, 10857, 10858, 10859, 68823
      , 68824, 68825, 68826, 68827, 68828, 68829
      , 68830, 68831, 68832, 68833, 68834, 372513
      )
    )
order by Table_9.Id desc

drop table #Table_1

Execution plan for slow query (with lookup): https://www.dropbox.com/s/rc1i6vvdwwc5hzh/long%20query_Cleaned.sqlplan?dl=0

Execution plan for fast query (no lookup):
https://www.dropbox.com/s/bjafn5tiy3uepul/short%20query_Cleaned.sqlplan?dl=0

Row goal
The issue with the estimates is most likely due to the row goal introduced by the TOP (500). See:
How (and why) does TOP impact an execution plan?
If you find the estimates are much better without the TOP, or by adding OPTION (QUERYTRACEON 4138) to the query, that would confirm the hypothesis.
Trace flag 4138 is documented in KB 2667211:
A query may take a long time to run if the query optimizer uses the Top operator in SQL Server 2008 R2 or in SQL Server 2012.
You should also enable optimizer fixes with trace flag 4199.
Indexing
Consider creating an index on table 39, keyed on:
Id_Obj_To, Id_ObjType_To, Id_ObjType_From, Id_Obj_From

Ensure Id_Obj_From has a suitable data type (not e.g. a max type).
Predicte ordering
The optimizer does not reorder NOT EXISTS clauses well in your version of SQL Server. If you expect the NOT EXISTS clause to be more selective than the Gid_LineSet predicate, reorder those two clauses in the WHERE part of the query.


PostgreSQL asynchronous replication (and possible data loss) I'm trying to wrap my head around Postgres' asynchronous replication. The way I'm reading the documentation, if the master goes down after data was committed, but before it's shipped to slaves, the slaves will never get that data (even after the master goes back up).

This, as I understand it, is different than MySQL's async replication, where the master resumes syncing it's binary log once it's brought back up (without any data loss).

Seems a bit crazy to me, so I wanted to make sure I was reading the docs properly.

To be clear, I'm not talking about data loss resulting in electing one of the slaves as the new master. Simply data loss with respect to the master going down before the WAL is shipped and it not being shipped once the master comes back up.

When the master comes back up, any left-over WAL must be synced to the slave, otherwise the slave won't be able to carry on as a replica. At that point the slave will replay the WAL and be caught up with the master. No data is lost so long as the master comes back up. You only lose not-yet-replicated data if you lose the master.

This is guaranteed by the timeline; the slaves simply refuse to replay WAL archives except in the correct and unbroken timeline order. So long as you're correctly monitoring your slave nodes you'll see them stop advancing and know things are broken. This shouldn't happen if WAL archiving is set up properly, though.

Consider using async streaming replication as well as WAL shipping, that helps reduce the potential loss window if you do lose the master.


What is the difference between CHECK and CONSTRAINT CHECK? is there any difference between the follow exemples?

ProjNum int
    CONSTRAINT nn_ProjNum NOT NULL
        CONSTRAINT C_ProjNum CHECK (ProjNum >= 10)

and

ProjNum int NOT NULL
    CHECK (ProjNum >= 10)

Thanks.

There is no difference in SQL Server between those two statements. Each results in NOT NULL column (not a constraint) with a single check constraint. The only difference is that second one creates a system-named check constraint, something like 'CK__t__ProjNum__4AB81AF0'.


What does SOS in SQL Server stand for? I am reading SQL Server 2012 Internals book now. There are several phrases with SOS acronym, such as "SOS Scheduler" (in chapter about the scheduler), "SOS Memory node" (in chapter about the free buffer list and the lazywriter). But I can't find the meaning anywhere.

It's actually usually referred to as SQLOS and it means SQL Server Operating System.

Details in this series

Also it can be found referenced in documentation in some places, e.g. here:

  [...] associated with the SQL Server Operating System (SQLOS). The SQLOS is responsible for managing operating system resources that are specific to SQL Server.


Why is mysqldump file so large? We have a 2GB MySQL DB.  We started a mysql dump of this DB this morning and the resulting dump file is 27GB! Can anyone shed some light on what's going on here?

Questions

Why is this file so large?
What is in this file that causes it to be so much larger than the in memory footprint?

Something tells me you are either using a version of MySQL prior to 5.0, you called an older version of mysqldump, or you messed with the settings of the dump.

What usually blows up a mysqldump past the size of its dataset is the option --skip-extended-insert.

In older versions of MySQL, there was no extended insert. That means each and every row in a table had an INSERT command to itself. If a table had 2,000 rows, the mysqldump output will have 2,000 INSERT commands. That's a whole lot of commas, parentheses, single quotes, and "INSERT INTO" tags to place in a dump file.

In newer versions of MySQL, --extended-insert was added to group together dozens (or even hundreds) of rows in a single INSERT. SO, instead of...

INSERT INTO tbname VALUES (1);
INSERT INTO tbname VALUES (2);
INSERT INTO tbname VALUES (3);
INSERT INTO tbname VALUES (4);
INSERT INTO tbname VALUES (5);

You could have this:

INSERT INTO tbname VALUES (1),(2),(3),(4),(5);

For mysqldump, --opt includes --extended-insert. If you use --skip-opt when doing mysqldump, it disables --skip-extended-insert. Here are the options of mysqldump that affect extended insert:

  -e, --extended-insert
                      Use multiple-row INSERT syntax that include several
                      VALUES lists.
                      (Defaults to on; use --skip-extended-insert to disable.)
      --opt               Same as --add-drop-table, --add-locks, --create-options,
                          --quick, --extended-insert, --lock-tables, --set-charset,
                          and --disable-keys. Enabled by default, disable with
                          --skip-opt.

CAVEAT

Run these at the Linux command line

mysql --version
mysqldump --version
which mysqldump

If these do not match the version of mysql you are running on the server, or multiple versions of MySQL exist on the same machine, get that straightened out. Until then, make sure you call the correct version of mysqldump and don't use --skip-opt.


How can I allow a user to query a view but not the underlying table in MySQL? I have searched for this question but cannot find an answer.  Or, the answers I have found have been related to SQL Server.  This is for MySQL 5.x.

Anyway, say I have a table called Employees and in that table, I have various columns like:

id, lastname, firstname, salary, ssn, hiredate

Now, I have some users that need to be able to select from the VIEW but not the TABLE.

For example:

--user a
select * from employee_view;

--shows:
id, lastname, firstname, hiredate

--user a
select * from employee;   // access denied  (didn't use view)

Then I have an HR User:

--HR user
select * from employee_view;

-- shows:
id, lastname, firstname, ssn, salary, hiredate

-- HR user
select * from employee;  // access denied (didn't use view)

Is there any way I can do this?

Thanks.

I figured it out myself.

First, I created an account that ONLY had permissions to certain columns (not all).  I then created a view for those columns and used the user as the definer.

Then, I created another test account and ONLY gave them permissions to that view.

So the test user could not see the underlying table but only the view and columns.

Works great.


Are deadlocks a serious problem? I have a few production SQL Server instances and I get around 100 deadlock alert emails a day (up to 100 for one server, 150 in total).

Is this a problem I should investigate? How many deadlocks are problematic and what can be the possible consequence of ignoring this issue?

It's typically not the count of deadlocks that can cause a serious problem, but the reason to investigate would be to find out what workloads are deadlocking on each other.  After all, in a deadlock there is a victim.  So now you have a process that didn't complete the work that it intended to complete.  And, in good theory applications should have some sort of retry logic in them for situations like this... but in practice this almost never happens.

With that being said, investigate what is deadlocking.  Typically it is the same workload or type of workload that generates the majority of deadlocks.  A few quick fixes (after reading the deadlock XML) could be an easy win to get rid of most of them.

(yes, 100+ deadlocks a day is a lot.  But any recurring deadlocking pattern, whether it is 1 or 100, should be investigated)


Why does WAITFOR (RECEIVE FROM QUEUE) show up in the monitor incorrectly? I kick off the following command in my app:

WAITFOR (RECEIVE CONVERT(int, message_body) AS Message FROM MyQueue)

When I run `sp_who2 active', I get Status=SUSPENDED and Command=DELETE

Note: moved from StackOverflow.

Because is a DELETE. RECEIVe is just syntactic sugar for something like

DELETE from queue
OUTPUT DELETED.*
WHERE status = <receivable>;

If you enable retention then is an UPDATE, and will show as an UPDATE. There are some details, like how conversation group locking is done, how the proper message ExactlyOnceInOrder semantics are ensured and the syntax tree of RECEIVE is not truly possible to be built in T-SQL. As I said, details...

Is no coincidence I recommend in Using Tables as Queues to use the OUTPUT clause of DELETE to 'dequeue' from a table...


Will SSRS be extinct soon, and PowerBI the new model? I read SQL Server 2017 will now include PowerBI Server. They also moved SSRS to a different installer, so it will not come packaged in the original SQL Server installation. Does this mean Microsoft will eventually try to deprecate SSRS? Should our team try to build new reports in PowerBI and transition the previous SSRS reports?

Does this mean Microsoft will eventually try to deprecate SSRS?

Ref:
https://blogs.msdn.microsoft.com/sqlrsteamblog/2017/07/19/sql-server-2017-reporting-services-release-candidate-now-available/

No.

  We moved Reporting Services installation from the SQL Server installer
  to a separate installer. This is a packaging change, not a product
  change; access to SQL Server Reporting Services is still included with
  your SQL Server license. The new installation process keeps our
  packages lean and enables customers to deploy and update Reporting
  Services with zero impact on your SQL Server deployments and
  databases.

Should our team try to build new reports in PowerBI and transition the previous SSRS reports?

If you need PowerBI you should start using it but not thinking SSRS being deprecated.


Why do large tables slow down queries in databases? Kind of a generic question; but I have noticed that large tables in certain CMS's can slow them down to a crawl. The solutions for this I've seen are to just truncate those tables.

Why do large tables slow down SQL queries so much?

How do Indexes help large tables?

How does disk speed factor into this?

Most of the time, sql performance issues comes from a poor miss job on the indexing. Selecting the wrong field to index or even the lack of clustered index can create huge bottle necks. I have seen many, many times that most dba forgets to create indexes for deletes. Yeah. I know, a delete... How come? Deletes, at least in sql server, performs several tasks that involve checking if the element can be deleted, check related tables, etc and those tasks can be slow as molasses without the proper indexing.

On the other hand, performance issues can very well come from lazy programmers who don't really care about performance or creating heaps on the server. These type of guys just creates select queries or select * without caring if these queries are performing full table scans, and believe me, in those situations, it doesn't matter if the db is 3Mb or 1Gb.

Finally, if you are really planning to have very large tables, you should consider a good partition policy and create your db with that in mind. This way, you can create the proper partitioned indexes.

As in most cases, in databases, the problem is between the keyboard and the chair ;)


Structure for storing table data, lists, text, pictures within PostgreSQL JSON field We are extracting content sections from word documents and have been going back-and-forth on how to store this arbitrary data. We are using PostgreSQL and our plan was to use JSON fields to save arbitrary sections in JSON fields.

Ideally we would like to just map out all of the relations into relations, however there is a lot of diversity in the types of data (tables, text, images, lists, etc.). None of the columns in each of the documents are the same and the same section in DocumentA can have a table and the same section in DocumentB can be made up of text or picture, hence the reason to store it in JSON.

This is an example:

DocumentA: has a table with fields named

ColA|ColB|ColC
val1|val2|val3
val4|val5|val6

DocumentB: has a table document with fields named

ColB|ColC|ColD
val1|val2|val3
val4|val5|val6

DocumentC: has a table document with with just a paragraph of text and a table

Note: more than one type of data can come out of each document (e.g. DocumentA can have 2x tables and a text data).

Store it in a single field in postgreSQL as a JSON array that is flat is our first thought.

This would be a single row in PostgreSQL for (e.g. INSERT INTO documentData(id,documentName,JSONField) VALUES (1, 'DocumentA', '<JSONDATA>') documentA.

[
 {
  "Content Type":"Table"
  "ColA": "val1",
  "ColB": "val2",
  "ColC": "val3"
 },
 {
  "Content Type":"Table"
  "ColA": "val4",
  "ColB": "val5",
  "ColC": "val6"
 }
]

Another record INSERT INTO documentData(id,documentName,JSONField) VALUES (2, 'DocumentB', '<JSONDATA>'

[
 {
  "Content Type":"Table"
  "ColB": "val1",
  "ColC": "val2",
  "ColD": "val3"
 },
 {
  "Content Type":"Table"
  "ColB": "val4",
  "ColC": "val5",
  "ColD": "val6"
 }
]

Another record INSERT INTO documentData(id,documentName,JSONField) VALUES (3, 'DocumentC', '<JSONDATA>'

[
 {
  "Content Type":"Text"
  "Data": "Random paragraph of text",
 },
 {
  "Content Type": Table",
  "ColA": "val1",
  "ColE": "val2",
  "ColF": "val3"
 }
]

The main issue I see doing it this way could potentially result in difficulty of querying the data. Are there any other suggestions?

Ultimate goal is to extract contents within certain control documents. The control documents have tables that have columns and cells that have names of parts of other documents we have to reference. Storing it in RTF would be a good thought, but not sure how to deal with searching and referencing the material reliably.

If you're looking for alternatives other than JSON, and you just need arrays of text name-value pairs collections, you could simply use PostgreSQL's arrays:

CREATE TYPE mydoc AS (content_type text, names text[], values text[]);
CREATE TABLE documentData(id int primary key, documentName text, docs mydoc[]);
INSERT INTO documentData(id,documentName,docs) VALUES (1, 'DocumentA', '{"(Table,\"{ColA,ColB,ColC}\",\"{val1,val2,val3}\")","(Table,\"{ColA,ColB,ColC}\",\"{val4,val5,val6}\")"}'::mydoc[])

SELECT d.id, d.documentName, dd.*
FROM documentData d
LEFT JOIN LATERAL unnest(d.docs) dd ON (true);

This won't get a significant advantage over JSON, other than portability to older PostgreSQL versions, but unnest and =ANY type of operations are often much simpler than the JSON functions, so it may be easier to query and manipulate, e.g.:

SELECT d.id, d.documentName, dd.content_type, pos, name, dd.values[pos] AS value
FROM documentData d
LEFT JOIN LATERAL unnest(d.docs) dd ON (true)
LEFT JOIN LATERAL unnest(dd.names) WITH ORDINALITY AS y (name, pos) ON (true)
WHERE dd.content_type = 'Table' AND name ~ '^Col[AB]';


Specific issue on data pump API in oracle I have a client/server architecture. Using an Oracle dbms on the database server side.
I need to perform a user-triggered (from client side) backup of the database, where the best way to perform that is using a stored procedure on the server side which the client may call, as the client has no oracle tools to perform the backup. I've searched thorough inside available solutions and have found that using a stored procedure is the best way.
Well, then I found that using oracle data pump API is the best way to use inside a PL/SQl stored procedure.

My specific questions about the API are...

I would like to ask about two issues ...

---- The first -----

the detach function to detach the handler, is it necessary to be used at the end of the procedure? and what if I don't use it? I read the Oracle documentation but I didn't get their point, they say it doesn't terminate the job but indicates that the user is not interested in it, an when I use detach at the end of my procedure the exported .dmp file disappears.

---- The second -----

to perform a user (client side) triggered back up as the modification are only to the data, I used TABLE parameter for the export operation. But the version parameter... what should it be? I also read the documentation but couldn't determine what I need (LATEST or COMPATIBLE) ?

Thanks

DETACH is not required, once you submit the job using dp api, it will run in the background. Once job starts, the dumpfile will remain unless job is killed. Once you run DBMS_DATAPUMP.START_JOB (handle => nhandle); the job will start executing. However, once you start and before DP begins (there is short delay when it prepares), if you detach during this time, the job is effectively cancelled and dumpfile will disappear.
Unless you need the dumpfile be backword compatible with certain prior versions of Oracle, you should leave this parameter alone, the default value is fine.


Restore master database from SQL Server 2008 R2 on SQL Server 2012 I have a process that restores all my backups on a centralized server for testing. I have recently upgraded this server to SQL server 2012, and now I am getting an error when restoring master from SQL Server 2008 R2. Restores from SQL Server 2012 and SQL Server 2005 work fine. I get the same results when trying to restore to SQL 2014 as well.

This is my restore statement:

RESTORE DATABASE masttest
FROM DISK = 'c:\temp\master.bak'
WITH MOVE 'master' TO 'c:\temp\masttest.mdf',
MOVE 'mastlog' TO 'c:\temp\masttest.ldf';

And this is the results. The database ends up in Recovery pending.

Processed 400 pages for database 'masttest', file 'master' on file 1.
Processed 3 pages for database 'masttest', file 'mastlog' on file 1.
Converting database 'masttest' from version 661 to the current version 706.
Database 'masttest' running the upgrade step from version 661 to version 668.
Database 'masttest' running the upgrade step from version 668 to version 669.
Database 'masttest' running the upgrade step from version 669 to version 670.
Database 'masttest' running the upgrade step from version 670 to version 671.
Database 'masttest' running the upgrade step from version 671 to version 672.
Database 'masttest' running the upgrade step from version 672 to version 673.
Database 'masttest' running the upgrade step from version 673 to version 674.
Database 'masttest' running the upgrade step from version 674 to version 675.
Database 'masttest' running the upgrade step from version 675 to version 676.
Database 'masttest' running the upgrade step from version 676 to version 677.
Database 'masttest' running the upgrade step from version 677 to version 679.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 5570, Level 23, State 3, Line 1
FILESTREAM Failed to find the garbage collection table.

Filestream is not enabled on the source server or the server I am restoring to. Is there anything I can do to restore this successfully or does this look like a bug?

You should never restore a system database from from one version of SQL Server to another.  The master database contains tables and other objects that support the inner workings of the server itself.  I wouldn't even restore system databases to another server of the same version.  I would instead determine what the information was I was trying to capture (user accounts, scheduled jobs, SSIS packages etc.) and have those extracted and imported to the new server.


Missing date handling between subquery and OUTER APPLY I have two sub-queries to get visit date and visit type that I combined into one outer apply.
The sub-queries for visit date and visit type give me blanks when there is no visit, which is what I want.
The outer apply gives me blanks for visit type but for date give me 01/01/1900.

I can solve the issue with a where  fldvisitdate <> '19000101'
but I'm wondering why the outer apply is working this way.
here is the code of my two sub-queries.

  (SELECT TOP 1 fldVisitDate FROM tblVisit
inner join tblschedule on tblSchedule.fldScheduleVisitID =       tblvisit.fldvisitid
     WHERE fldVisitPrerepID = tblRunnerPrerepIDs.fldPrerepID
   and tblSchedule.fldScheduleVisitID = tblvisit.fldvisitid
    ORDER BY fldVisitDate)
      AS 'First Visit',

      (SELECT TOP 1
      tblVisitTypes.fldVisitTypeName
     FROM tblVisitTypes
     inner join tblVisit  ON tblVisit.fldVisitTypeID =    tblVisitTypes.fldVisitTypeID
     inner join tblschedule on tblSchedule.fldScheduleVisitID =    tblvisit.fldvisitid
     WHERE fldVisitPrerepIDID = tblRunnerPrerepIDs.fldPrerepID
    and tblSchedule.fldScheduleVisitID = tblpatientvisit.fldvisitid
  ORDER BY fldVisitDate)
   as 'First Visit Type',

        OUTER APPLY (SELECT TOP 1
       tblVisit.fldVisitDate, tblVisit.fldVisitDate,     tblVisitTypes.fldVisitTypeName
      FROM tblVisit
      inner join tblVisitTypes  ON tblVisit.fldVisitTypeID = tblVisitTypes.fldVisitTypeID
      inner join tblSchedule on tblSchedule.fldScheduleVisitID = tblVisit.fldvisitid
     WHERE fldVisitPrerepID = tblRunnerPrereps.fldPrerepID
     and tblSchedule.fldScheduleVisitID = tblVisit.fldvisitid
    ORDER BY fldVisitDate) as FirstVisitData

Of course earlier in the query I request a display of

      FirstVisitData.fldvisitdate as ‘First Visit’
      FirstVisitData.fldVisitTypeName as ‘First Visit Type’

First, the 2 original subqueries have different correlation conditions. The first one has and tblSchedule.fldScheduleVisitID = tblvisit.fldvisitid (which is a redundant repeat of the ON condition) but the 2nd has and tblSchedule.fldScheduleVisitID = tblpatientvisit.fldvisitid which is a correlated condition to the main query.
So, the two subqueries may have been giving you NULL in some results but they are incorrect (one of them!) if they are supposed to give you related results.

Second and based on the (broken) results from the OUTER APPLY where the conditions from the first subquery is used, we can deduct that the 2nd subquery has the correct/wanted conditions.

Other points of trouble is the random formatting of the code, the mixing of upper and lower case SQL keywords and the lack of aliases which makes the code rather unreadable. The more the code in nicely formatted, the easier would be to catch similar errors.

Columns and table aliases should be quoted (if needed) with double quotes ("some alias") or square brackets ([some alias]) and not with single quotes. While this is allowed for column aliases, it is very confusing and error-prone, as single quotes are used for string literals.

Columns should always (unless in ORDER BY) be prefixed with the table name or alias. This is mostly fine in the code, except for one miss.

Based on the above, I'd rewrite the OUTER APPLY like this:
OUTER APPLY
  ( SELECT TOP 1
        v  . fldVisitDate,
        vt . fldVisitTypeName
    FROM tblVisit AS v
        LEFT JOIN tblVisitTypes AS vt
            ON  v.fldVisitTypeID = vt.fldVisitTypeID
        INNER JOIN tblSchedule AS s
            ON  s.fldScheduleVisitID = v.fldvisitid
    WHERE
        -- replace "xxx" with table name or alias
        xxx.fldVisitPrerepID = tblRunnerPrereps.fldPrerepID
      AND
        s.fldScheduleVisitID = tblPatientVisit.fldvisitid
    ORDER BY
        fldVisitDate
  )
    AS FirstVisitData

and then double and triple check that the joining conditions are correct and matching business logic and the intention of the query.
All the above may still not resolve the weird results (1900-01-01) you get. It may be that they are caused due to the changed conditions (in which case, the rewrite will resolve it) or as @Duffy has commented they may due to some implicit conversion. The empty string '' when converted to DATE becomes by default 1900-Jan-01. A UNION can have this effect. Try this for example and you'll get the 2nd row as 1900-Jan-01:
SELECT getdate() AS SomeDate
UNION
SELECT '' ;

Without knowing what transformations are happening in the external query, we are not sure about that either nor why this happens only with the OUTER APPLY but not with the subqueries method.


SQLite Internals - Records Hey I'm trying to wrap my head around SQLite data storage, specifically how it is storing Records. I've found a book The Definitive guide to SQLlite, where the author explains internal record format (Figure 9-5, page 351):

Given table:

sqlite> SELECT * FROM episodes ORDER BY id LIMIT 1;
id   season  name
---  ------  --------------------
0    1       Good News Bad News

Its internal record format is:

| 04 | 01 | 01 | 49 |   | 00 | 01 | Good News Bad News |

  "The header is 4 bytes long. The header size reflects this and itself is encoded as a single byte. The first type, corresponding to the id field, is a 1-byte signed integer. The second type, corresponding to the season field, is as well. The name type entry is an odd number, meaning it is a text value. Its size is therefore given by (49-13)/2=18 bytes."

Specifically I'm curious about TEXT attribute, in the example above we have a string of length 18 characters. And the rule for TEXT in SQLite is as follows:

Type Value     Meaning   Length of Data
----------     -------   --------------------
N>13 and odd   TEXT      (N-13)/2

What hapenns though when the string is longer ? It'll get out of range of that one byte.

Either the book authors describe some really ancient version of SQLite, or they make a mistake when they say "[t]he header size [...] is encoded as a single byte."
Documentation that "describes and defines the on-disk database file format used by all releases of SQLite since version 3.0.0 (2004-06-18)" states that

The record format makes extensive use of the variable-length integer or varint representation of 64-bit signed integers[...]
A record contains a header and a body, in that order. The header begins with a single varint which determines the total number of bytes in the header. [...] Following the size varint are one or more additional varints, one per column.

while

A variable-length integer or "varint" is a static Huffman encoding of 64-bit twos-complement integers that uses less space for small positive values. A varint is between 1 and 9 bytes in length.

so

The serial type varints for large strings and BLOBs might extend to two or three byte varints

which certainly allows them to represent pretty long values.


SSDT: Requires Visual Studio? Requires connection to SQL Server? (SQL Server 2016) I need to make some SSIS workflow packages. I see I can do this in SSDT.

I have not used SSDT before.

It appears that SSDT installs into Visual Studio. OK, I can do that. (I have VS 2015 installed.)

But my workstation has no visibility to the SQL Server. The SQL Server is firewalled, etc.

Will I be able to author in a disconnected way in SSDT and then package a file that I can transfer to SQL Server?

If not, what is the workflow to author in the SSDT SSIS Designer and get the package to an isolated SQL Server?

Yes you can work offline but it will be difficult to develop and test without the input/output connections files/tables your package will use.

One option is to install SQL Server on your pc, create a database and copy the source and target tables and some test data to your pc. Then you can develop and test locally.

Deployment will be difficult unless you have ssdt on the server. Otherwise you will need access to deploy to the server from another machine.


Executing SPs in parallel (maybe with a cursor) We have an 3rd party accounting system that runs on SQL Server 2008 R2. To insert invoices into this accounting system, we have to use the API that comes with it (which is just a set of encrypted SPs).

Some of our batch processes need to insert a large number (1,000s) of invoices. So, ordinarily in SQL you'd write an INSERT statement and they'd get inserted very quickly.

However, because we have to use the SP to insert the rows we currently have a C# application that runs a PARALLEL FOR loop and makes a call to the SP once per iteration.

The performance isn't great and, while much of this is down to what happens inside the API SP, I'm wondering if there's anything SQL Server can do for me to improve performance. I know I could get rid of the C# app and use a CURSOR in T-SQL, but as far as I know, that wouldn't run in parallel - is that correct?

Similarly, I could dynamically generate thousands of SQL statements to call the SP with different parameters and I'd like to think that the optimizer can see what the encrypted SP is doing (certainly better than a C# app) and come up with a plan that will run them all in with as much parallelism as possible.

You should consider Table-Valued Parameters and a new stored procedure that can take 1000s of invoices and deal with them as a set rather than the singleton insert you have now. Knowing how to write the procedure to deal with all invoices as a set would require first-hand knowledge of what the procedure does now with a single invoice.

I wrote up a quick article on TVPs here:

http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql

And Erland's article is useful as well:

http://sommarskog.se/arrays-in-sql-2008.html


pt-table-sync error: Called not_in_left in state 0 I have setup a Mysql replication between 2 servers, using Percona Xtrabackup:

Master is a MySQL 5.0.91 Community Edition (CentOS 4.8)

Slave is a MySQL 5.1.68 Community Edition (CentOS 6.4)

When starting the slave, some replication queries where blocked because of some unknown "temp" tables.
I used a few SQL_SLAVE_SKIP_COUNTER commands to hide the problem. And now that the replication is up to date, I try to resync the tables.
=> 2 tables are out of sync. I use pt-table-sync to resync.

The first table has been resynced without any problem (a few UPDATE to replay)

But the second table, a huge table (57GB), give me this error after some time (varying from a few minutes to a few hours):

pt-table-sync --verbose --execute -uroot -p h=10.2.0.1,D=MYDB,t=MyTable h=10.2.0.2

# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
Called not_in_left in state 0 at /usr/bin/pt-table-sync line 5500.  while doing MYDB.MyTable on 10.2.0.2
#      0       0      0      0 0         16:38:24 19:08:17 1    MYDB.MyTable

Note that I launch pt-table-sync from a third server on the local network.

I don't find much information about this error.
What would you recommand to help me solve this problem?

You could try:

Checking the MySQL error log after you attempt the sync. It may reveal some consistency issues with the table that you weren't aware of
Working around this table for now (using --exclude-table), and then coming back to it later
Trying different combinations of checksum options (--algorithms, --chunk-size) for the problem table - this might fix the problem, or shed more light on the underlying issue

I received this error recently, and the underlying issue turned out to be a corrupt table in need of repair on the master server.

I discovered the problem by changing the behaviour of pt-table-sync to using the "nibble" algorithm for my problem table (--algorithms nibble). Instead of the cryptic error, I got a specific error along with the SQL Statement that caused it, when I then attempted to execute directly on the server. This led me straight to my problem.

I could have also checked the error log and discovered the same thing.

Alternatively, you could try changing algorithms or reducing the chunk size from 1,000 (e.g. --chunk-size 100). This may require a bit of trial and error, and it's likely to slow down the checksums significantly, which is why I suggest skipping your largest tables to start with.

For a sufficiently large table (and 57GB certainly qualifies) with a poorly distributed primary key, you might also experience problems with query or wait timeouts depending on your MySQL configuration - I'm not sure if this would also result in the same error. The pt-table-sync documentation offers a little bit more information on this.


Backups in Availability Groups In case of async commit setup and the backup preference set to secondary replica.
I have observed, in case of high unsent log size, log backup on secondary replica starts failing for that database with error:

  Connection With Primary Replica Not Active

The unsent log size was ~4GB. There was no connectivity issues.

What factors impact log backups in multiple secondaries and async commit mode?

You can backup on any secondary replica if they are connected to the primary replica AND the state is either SYNCHRONIZED or SYNCHRONIZING.

It seems that you should check the connection to the primary replica. Please take a look in the sql server's error log.


Where does each MySQL data file type belong? I'm in the process of transferring a database from one server to another.
I'm moving the data files directly, instead of using a SQL dump. To do this, I:

ensured that both servers use the same MySQL version
set innodb_fast_shutdown = 0
stopped the MySQL server
rsynced the data to the new server

I'm almost ready to start the new server, but I have a doubt. The old server uses the same data dir /var/lib/mysql for data, binlogs, etc., whereas the new server uses 3 different dirs for data, binlogs and tmp:
datadir=/mysql/data
tmpdir=/mysql/tmp
log-bin=/mysql/binlog/mysql-bin

The question is: where should each of the files go?
My reasoning is:

/mysql/tmp: there should not be any temporary files left after a slow (or even a normal?) shutdown, so nothing should go there right now
/mysql/binlog: I should only move the mysql-bin.* files
/mysql/data: everything else should go there

Is that correct?

Here is the file list, for information (nxxxxxxx is my main database):
# ls -al /var/lib/mysql
total 38518860
drwxr-x--x.  9 mysql mysql       4096 20 nov.  13:50 .
drwxr-xr-x. 37 root  root        4096 29 déc.   2019 ..
-rw-rw----.  1 mysql mysql         56 21 déc.   2014 auto.cnf
-rw-------   1 mysql mysql       1680 27 déc.   2018 ca-key.pem
-rw-r--r--   1 mysql mysql       1112 27 déc.   2018 ca.pem
-rw-r--r--   1 mysql mysql       1112 27 déc.   2018 client-cert.pem
-rw-------   1 mysql mysql       1676 27 déc.   2018 client-key.pem
-rw-r-----   1 mysql mysql     589824 20 nov.  13:50 #ib_16384_0.dblwr
-rw-r-----   1 mysql mysql    8978432 19 nov.  18:28 #ib_16384_1.dblwr
-rw-r-----   1 mysql mysql      91522 20 nov.  13:50 ib_buffer_pool
-rw-r-----.  1 mysql mysql 9143582720 20 nov.  13:50 ibdata1
-rw-r-----   1 mysql mysql 1073741824 20 nov.  13:50 ib_logfile0
-rw-r-----   1 mysql mysql 1073741824 20 nov.  13:50 ib_logfile1
drwxr-x---   2 mysql mysql       4096 20 nov.  13:50 #innodb_temp
drwxr-sr-x.  2 mysql mysql       4096 19 nov.  18:28 mysql
-rw-r-----   1 mysql mysql 1073742002  5 nov.  21:45 mysql-bin.002418
-rw-r-----   1 mysql mysql 1073742033  6 nov.  12:00 mysql-bin.002419
                                    (...)
-rw-r-----   1 mysql mysql     341943 20 nov.  13:50 mysql-bin.002446
-rw-r-----   1 mysql mysql        179 20 nov.  13:50 mysql-bin.002447
-rw-r-----   1 mysql mysql        570 20 nov.  13:50 mysql-bin.index
-rw-r-----   1 mysql mysql   62914560 20 nov.  13:50 mysql.ibd
-rw-r--r--.  1 mysql mysql          6 19 nov.  18:28 mysql_upgrade_info
drwxr-sr-x.  2 mysql mysql      69632 17 nov.  17:15 nxxxxxxx
drwxr-x---   2 mysql mysql       4096 19 nov.  18:28 performance_schema
-rw-------   1 mysql mysql       1676 27 déc.   2018 private_key.pem
-rw-r--r--   1 mysql mysql        452 27 déc.   2018 public_key.pem
-rw-r--r--   1 mysql mysql       1112 27 déc.   2018 server-cert.pem
-rw-------   1 mysql mysql       1680 27 déc.   2018 server-key.pem
drwxr-x---.  2 mysql mysql      12288 27 déc.   2018 sys
drwxr-x---   2 mysql mysql       4096  1 mars   2019 test
-rw-r-----   1 mysql mysql  109051904 20 nov.  13:50 undo_001
-rw-r-----   1 mysql mysql  109051904 20 nov.  13:50 undo_002

That's fine, just you'll likely need to reconfigure you bin and tmp locations in your .cnf file after you move the files.
Here's an article specifically about reconfiguring the tmp location (I would imagine the bin directory is similar): How to change your MYSQL tmpdir


Backup SSRS Encryption Keys Using Powershell I have made the following script from google for backing up the SSRS Encryption keys:

cls
$pwd = "sa@123@123"
$SSRSClass = Get-Wmiobject -namespace "root\microsoft\sqlserver\reportserver\rs_BPSSRS\v10\admin" -class "MSReportServer_ConfigurationSetting"

$key = $SSRSClass.BackupEncryptionKey($pwd)
$stream = [System.IO.File]::Create("c:\\SSRS.snk", $key.KeyFile.Length)
$stream.Write($key.KeyFile, 0, $key.KeyFile.Length)
$stream.Close()

But I'm getting the following errors:

Method invocation failed because [System.Object[]] doesn't contain a method named 'BackupEn
cryptionKey'.
At line:5 char:38
+ $key = $SSRSClass.BackupEncryptionKey <<<< ($results)
    + CategoryInfo          : InvalidOperation: (BackupEncryptionKey:String) [], RuntimeEx
   ception
    + FullyQualifiedErrorId : MethodNotFound

Exception calling "Create" with "2" argument(s): "Positive number required.
Parameter name: bufferSize"
At line:6 char:35
+ $stream = [System.IO.File]::Create <<<< ("c:\\SSRS.snk", $key.KeyFile.Length)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

You cannot call a method on a null-valued expression.
At line:7 char:14
+ $stream.Write <<<< ($key.KeyFile, 0, $key.KeyFile.Length)
    + CategoryInfo          : InvalidOperation: (Write:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At line:8 char:14
+ $stream.Close <<<< ()
    + CategoryInfo          : InvalidOperation: (Close:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

I'm using powershell v2. I tried finding about this but no luck. There are around 50+ SSRS servers in our environment and doing backup manually is tiresome. Hence, we came up with this automation. Kindly provide your comments.

Thanks

Using WMI to backup RS key might not be so efficient. You may try the below script that I found at http://powershell-tips.blogspot.com/2015/05/backup-report-server-encryption-key.html

#------------------------------------------------------------------------------------------
#     Script : Backup_RSKey.ps1
#     Author : Som DT.
#    Purpose : Backup Report Server Encryption Key with Powershell
#------------------------------------------------------------------------------------------
#     Syntax : Powershell .\Backup_RSKey.ps1 -InstanceName <InstanceName> -PassWord <Password> -BackupLocation <BackupLocation>
#------------------------------------------------------------------------------------------
#    Example : Powershell .\Backup_RSKey.ps1 -InstanceName "MSSQLSERVER" -PassWord "rsdal@2015" -BackupLocation "C:\temp"
#------------------------------------------------------------------------------------------

param (
    [string]$InstanceName="MSSQLSERVER",
    [string]$PassWord="rsdal@2015",
    [string]$BackupLocation="C:\temps"
)

cls
$FileTimestamp=$(get-date).tostring("yyyyMMddHHmmss")

function Backup-RSKey([string]$InstanceName , [string]$PassWord, [string]$BackupLocation)
{
    Trap {
        echo  "Failure : $($Error[0].Exception.Message)"
        return
    }

    $KeyFile="${BackupLocation}\KEY_$InstanceName_$FileTimestamp.snk"

    echo "Backing up to file : $KeyFile"

    $Error.Clear()

    Out-String -InputObject( echo "Y" | RSKeyMgmt.exe -e -f"$KeyFile" -p"$PasWord" -i"$InstanceName")

    if (!(Test-Path "$KeyFile") )
    {
        return "Failure : Error occured while backing up [$KeyFile]."
    }
    else
    {
        return "Success"
    }

}

$Output=Backup-RSKey "$InstanceName"  "$PassWord"   "$BackupLocation"

echo $Output

#--------------------------------------------------------------------------------------------------------------
#-- End of Program
#--------------------------------------------------------------------------------------------------------------


Split Field Every 1K Characters I have a field that can hold up to 3000 characters.  I need to split this field into 3 individual fields split1, split2, split3, I have successfully been able to figure out how to get the beginning, but grabbing the middle and end I am stuck on.  How would I capture the characters from 1001 to 2000 and from 2001 to 3000?

This is what I use to capture from 0 to 1000 how would I capture the data for split2, and split3

split1 = COALESCE(CASE WHEN DATALENGTH(fieldtosplit) > 1000
         THEN LEFT(CAST(fieldtosplit AS VARCHAR(MAX)), 1000)
         ELSE fieldtosplit END,'')

This should work:

SELECT
    LEFT(fieldtosplit
        , CASE WHEN LEN(fieldtosplit) > 1000 THEN 1000 ELSE LEN(fieldtosplit) END
    )
    , CASE WHEN LEN(fieldtosplit) <= 1000 THEN '' ELSE
        SUBSTRING(fieldtosplit, 1001
            , CASE WHEN LEN(fieldtosplit) > 2000 THEN 1000
                ELSE LEN(fieldtosplit)-1000 END
        )
    END
    , CASE WHEN LEN(fieldtosplit) <= 2000 THEN '' ELSE
        SUBSTRING(fieldtosplit, 2001, LEN(fieldtosplit)-2000)
    END
FROM (SELECT fieldtosplit = CAST(fieldtosplit as varchar(3000) FROM data)

I am not sure why you need to do that and it may be a bad idea. Perhaps something should be changed in your DB design in order to avoid this logic and this type of string manipulation. But I don't have enough information to be sure.

You are talking about 3000 characters and 1000 characters but you are using DATALENGTH ans 1 CAST AS VARCHAR:

LEN seems to be better than DATALENGTH since you want a number of characters, not a number of bytes
CAST does not seem useful here or perhaps you have NVARCHAR data or something wrong in the design


Is it beneficial to include ORDER BY fields in the list of SELECT fields? I'm just wondering when you do a query and you use ORDER BY should the fields you use in the ORDER BY clause always be included in the SELECT list even if you don't need them? Is this beneficial in some way?

There is no benefit to including the ORDER BY columns in the SELECT list. On the contrary, having unrequired columns in the SELECT list incurs a fractional overhead in run time and a larger one in maintenance.


Creating a database snapshot for every previous month I am new to SQL Server,
I got a task to create a snapshot of existing database for every previous month until current month and do a snapshot once a month from now on.

I created a job to make snapshots once a month, but I am having problem how can i do this for past months.

How precise in time does this snapshot have to be? To the hour? Second? You still didn't answer that in your first comment.

So you are using the "Database Snapshot" technology. IMO, this will lead you into trouble in the long run. You add more and more overhead to your database for each snapshot you have. Having database snapshot existing for a longer time period isn't something that I would recommend.  For more information, I suggest you read up on how database snapshot are implemented, with the copy-on-write technology. https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-snapshots-sql-server?view=sql-server-ver15

I recommend you re-work your approach and use backups. Whether or not you will have to complement your full backups with log backups depend on the answer to my above questions.

As for past time, you have to go find old backups. There is nothing inside SQL Server that can produce a snapshot of a database from a previous point in time. (Except if you have some date columns and/or temporal turned on for each table, but puzzling that together is likely a huge undertaking.)

Having some date column for only some tables won't help you create snapshots for older data. Hunt down old backups and use them instead.


Lock out of Oracle based on Windows username I have this logon trigger to only allow certain users to log in to an Oracle database (even if they have the correct password to enter the database):

CREATE OR REPLACE TRIGGER SYS.LOGON_TRIGGER
  AFTER LOGON ON DATABASE
DECLARE
  THIS_USER VARCHAR2(50);
BEGIN
  SELECT OSUSER INTO THIS_USER FROM V$SESSION WHERE SID = SYS_CONTEXT('USERENV','SID');
  IF THIS_USER NOT IN (<List of Users>)
    THEN RAISE LOGIN_DENIED;
  ENDIF;
END;
/

It works for preventing users from entering most schemas but not all (e.g. the SYS or SYSTEM schemas can still be entered regardless of the user - this logon trigger is seemingly completely bypassed).

Is there a way to lock out these users even for these SYS type schemas?

A bit of context:

Due to decisions made way before I got involved with this, all of the logins for this database have the same password. Additionally, most users use the same login as many of our processes that read/write to this database automatically.

We don't want to simply change the passwords because it would be a very large effort to see what impact changing these passwords actually does to the system. (We would have to modify the code that the processes use to access the database, and there are many of these.) An easier solution for us is to just lock out based on usernames, if possible.

The reason why your trigger does not work for users like SYS or SYSTEM is because they have the ADMINISTER DATABASE TRIGGER privilege.

The ADMINISTER DATABASE TRIGGER privilege allows you to create database-level triggers (server error, login, and logout triggers). It also allows you to log in regardless of errors thrown by a login trigger as a failsafe.

So, the answer is: no, you cannot prevent login for such users - at least not with a login trigger.


Restoring with replace going incredibly quickly So I have a database (on a SQL Server 2008 R2 SP2 instance) that is 2TB uncompressed, with a .bak file that is 500GB compressed. As I have been toying with the fastest way to restore this database after a series of tests, I've tried a couple of things:

Restoring after dropping the database
Restoring with Replace

When I restore after dropping the database, the fastest I can get a restore going is 4 hours. This is when I put the .bak on a RAID 10 logical drive, with the data files being written to a separate RAID 10 logical drive on the same server. The log files go to yet another RAID 0 on the same server.

However, if I restore with Replace, the restore process only takes 56 minutes (similar setup). Did I find some sort of turbo button? This is so fast that I am worried.

The RESTORE DATABASE ... WITH (REPLACE) does not have to create the 2 TB .mdf file.  This is why it is so much quicker.   You may want to look into instant file initialization.


Create fast-refresh MV over dblink on table without PK? I want to create a materialized view between a primary system (Oracle 18c) and an external system (Oracle 19c) via a dblink.

The MV would refresh on a 30-second schedule (since on-commit is not possible over a dblink).
The table in the external system does not have a primary key.

I'm a novice. Here's what I've tried:
--in the external system:
create materialized view log on external_system.workorder with rowid;

--in the primary system:
create materialized view primary_system.workorder_mv
build immediate
refresh fast
start with sysdate next sysdate + (30/(60*60*24))
as
select
    cast(workorderid as number(38,0)) as objectid,
    wonum,
    status,
    --other fields
    longitudex,
    latitudey
from
    external_system.workorder@gistomax

When I try the above, I get a litany of errors, starting with one that says I need a primary key.
I found a page that suggests that it's possible to do a fast refresh over a dblink. But the example uses a primary key, not a rowid.
And the docs say:

Rowid materialized views are not eligible for fast refresh after a
master table reorganization until a complete refresh has been
performed.

But I don't really know what that means. And to be honest, I'm so new at this, that I know I'm barking up the wrong tree, so I'll stop there.

Question:
Is it possible to create a fast-refresh MV over a dblink on table that doesn't have a PK?

As already demonstrated in your other question, a unique constraint on a NOT NULL column is good enough to create an mview log with "PK" values. That can be used for fast refresh.
SQL> conn bp/bp
Connected.
SQL> create database link self connect to bp identified by bp
     using 'localhost:1521/RYMIN19';

Database link created.

SQL> create table t1 (c1 number not null unique, c2 number);

Table created.

SQL> create materialized view log on t1 with primary key;

Materialized view log created.

SQL> create materialized view mv1 refresh fast on demand as select * from t1@self;

Materialized view created.

SQL> insert into t1 values (1, 1);

1 row created.

SQL> insert into t1 values (2, 2);

1 row created.

SQL> commit;

Commit complete.

Then:
SQL> exec dbms_mview.refresh('MV1', 'f');

PL/SQL procedure successfully completed.

SQL> select last_refresh_type from user_mviews where mview_name = 'MV1';

LAST_REF
--------
FAST


How can I setup some kind of buffering database with mariadb I have a primary database on a linux box run mariadb, but it uses >100 watts of power which reduces the UPS runtime.  Also I need to periodically reboot the server for updates and maintenance.
I want to have an intermediate database on the raspberry Pi 4 also mariadb.
So if the main server is off, it logs it locally(raspberry pi) on disk so if the power is lost the data is not.
I would prefer to have it attempt to transfer the data as soon as it comes in, but 15 minutes intervals is ok. After successful transfer the records should be deleted from the Pi. It needs to verify the records transferred correctly and not hope for the best. If the primary database fails, oh well I guess I will try again in 15 minutes and hope for the best. It will keep trying every 15 minutes until it succeeds. Even if it takes a week.
So I can setup a local mariadb easy enough, but how can I have it automatically transfer the data to the primary database and delete the records from itself.
Also the table has an auto incrementing field that needs to be ignored on the raspberry Pi as the primary server has it own auto incrementing field and that is the master.
I thought of some kind of data replication, but I don't want 2 copies of the data.

Primary - Primary  (aka multi-Master) replication.
When either is down, the other continues to log changes to the "binlog", which will be promptly sent to the other machine when it comes back up.
Read the manual for details.  It is essentially setting up Replication to go both ways.  There are several steps.  A common mistake is forgetting to turn on log_slave_updates.


Bad use of Oracle Tablespaces I have a doubt about the use of oracle tablespaces. I've decided to define for each schema of the datababase two different tablespaces (one for data, and another one for indexes).

The problem is that some time has passed, and my developers had mixed the tablespaces creating indexes in the data tablespace and creating tables and partitions in the index tablespace.

I would like to know how this could impact on performance.

Thanks in advance

In reality, on the modern devices the impact on performance is likely to be minimal.

The old "tablespace for index, tablespace for data" mantra comes from a time when storage tended to be made up of sets of single devices.  I.E. if you said that you wanted to put a tablespace on a certain disk, it went onto a particular physical disk.

Having both indexes and data on the same disk increases disk contention as Oracle can't read both index and data at the same time.

However, in most reasonably specc'd servers these days a 'disk' as far as Oracle is concerned is not necessarily a single disk.  Often they are striped disk arrays.  In this case there often isn't an increase in disk contention as there are multiple physical disks and IO cards in the server and the indexes and data don't always clash anyway.

So, in short - it depends on your hardware set-up.

Though, to be honest, unless you're really on top of monitoring, responding to your "top SQL" reports, have good data model designers and are building the right indexes you are much more likely to find performance improvements by learning about those things first.  I'm not saying you don't do those things, but if you don't you should look to improve there first.

If you think the tablespaces issue is a problem, then (as others have said) you can just move the tables and indexes around to put them back in the right place.

Once done, a good plan would be to implement some kind of standard for how you implement table and index creates, probably using substitution variables for the names of tablespaces, most certainly supported by a scripted test that runs as part of your commit / build process to check that people aren't putting things in the wrong place.

You can then follow that up with a regular process on your demo / test server that checks for things in the wrong place and sends an e-mail to the team when it discovers something.  That way you stand a chance of discovering problems before they hit the live environment.

I'd use this approach for any standard that is broken more than once or twice.

As for non-performance related implications, it can have an impact on the maintainability of your tablespaces and ensuring that the datafiles are properly configured.  Even that is probably negligible on modern servers.

Is should say though - I'm not a DBA, I've just worked as a developer with Oracle a LONG time - so when it comes to maintainability, I'm no expert...


pg_dump freezes on windows service application I have a system running over Tomcat which executes a backup batch job every day, it calls  pg_dump.exe from Java and stores its output to an SQL file.

When I'm executing Tomcat from the shell, it works perfectly, but when I start Tomcat as a Windows service the pg_dump.exe process stays there doing nothing, the SQL file is never written and the dump is never performed.

Is there any additional parameter or configuration required for Windows services? because it works without problems on a Unix-box.

Update: I'm using a Java class to call the operation.

Process p = new ProcessBuilder(
          "C:/postgres9/bin/pg_dump.exe",
          "-U", "myuser",
          "-h" , "localhost",
          "mydb").start();
storeToFile( p.getInputStream() );

Specs:

Tomcat 7.0.23
Postgresql 9.1
Windows 2008

dezso was right, the PGPASSWORD environment variable is present when executing Tomcat from a single shell, but not when it's started as a service. The solution is to put the value in the process environment:

 ProcessBuilder builder = new ProcessBuilder(
      "C:/postgres9/bin/pg_dump.exe",
      "-U", "myuser",
      "-h" , "localhost",
      "mydb");

 Map<String, String> env = builder.environment();
 env.put("PGPASSWORD", "supersecret");

 Process p = builder.start();
 storeToFile( p.getInputStream() );

Solution source is here: How to pg_dump from Java


Will I benefit from SQL Server 2014 In-Memory tables if my tables are already fully cached in-memory? Our most frequently used database is relatively small (5GB), and as a consequence, our 16GB memory dedicated SQL Server 2012 database server chooses to cache all used tables in memory (Buffer Cache Hit Ratio is 100% constantly). Some people in our team want to move to SQL Server 2014 to increase performance with In-Memory Tables, however I'm not convinced that doing such will increase performance since our tables are already in memory.

Is there more to the new In-Memory Tables in SQL Server 2014, or in my scenario there is indeed little to gain?

Some people in our team want to move to SQL Server 2014 to increase performance with In-Memory Tables ...

That entirely depends on:

If more performance is needed
What the current bottleneck is

  however I'm not convinced that doing such will increase performance since our tables are already in memory.

You should be suspect that it may or may not improve performance, however the reason isn't because the data is already in memory. It is mainly due to being lock and latch free and less code execution with native compilation.

Thus, if your bottleneck isn't around locks or latches... the actual gain you receive will be minimal to none. This can be exacerbated if the actual bottleneck is the client.

There is a great article about use cases for IMOLTP, and while we are at it you may want to look at 2016 and the uses of IMOLTP for advanced real time analytics.

  Is there more to the new In-Memory Tables in SQL Server 2014, or in my scenario there is indeed little to gain?

In-Memory objects (in this case, tables) are not just pinning data into memory. They have entirely different memory structures and are lock and latch free.

There are additional differences in how the data is stored, how it affects database startup, etc.


where does pg_dumpall store *.bak file? I want to dump a database. and I use:

pg_dumpall

by using this code, data which has been stored on data base is scrolling in the terminal and it says:

PostgreSQL database dump complete

PostgreSQL database cluster dump complete

but I can not find any new .bak file any where. I also used:

pg_dumpall -f test

it goes to next line without showing any message.

finally, where is stored .bak file?!

There is no .bak file until you name it so.

With the last command in your question, the dump is written to the file test — this, then, is your “.bak file”.

In fact, it is an SQL script, because pg_dumpall only supports the plain text format.

You restore such a dump by feeding it to psql:

psql -h hostname -p 1234 -f test -d postgres -U postgres

Where “1234” is the port number of your new database cluster.