Altering partition functions dynamically by utilizing triggers I am wanting to utilize partitioning based on a [TenantId] (and later in conjunction with date ranges). Instead of needing to manually insert the latest value within the PARTITION FUNCTION, I thought of creating a TRIGGER AFTER INSERT to pull the [TenantId] value and ALTER PARTITION FUNCTION to add it to the SPLIT RANGE. However, I am running into an unexpected error:

  Cannot execute ALTER PARTITION FUNCTION on/using table 'Tenant' since the table is the target table or part of cascading actions of a currently executing trigger.

First, I'm creating the PARTITION FUNCTION [PF_Tenant_Isolation] and PARTITION SCHEME [PS_Tenant_Isolation] for partitioning on the [TenantId].

CREATE PARTITION FUNCTION [PF_Tenant_Isolation] ([int])
    AS RANGE LEFT FOR VALUES (1);
GO

CREATE PARTITION SCHEME [PS_Tenant_Isolation]
    AS PARTITION [PF_Tenant_Isolation]
    ALL TO ([Auth]);
GO

Following this, I am creating the [Tenant] table against the newly created partition scheme.

IF OBJECT_ID('[Auth].[Tenant]', 'U') IS NULL
BEGIN
    CREATE TABLE [Auth].[Tenant] (
        [TenantId] [int] IDENTITY(1,1)
        ,[TenantActive] [bit] NOT NULL CONSTRAINT [DF_Tenant_TenantActive] DEFAULT 1
        ,[TenantName] [varchar](256) NOT NULL
        ,CONSTRAINT [PK_Tenant_TenantId] PRIMARY KEY CLUSTERED ([TenantId] ASC)
    ) ON [PS_Tenant_Isolation]([TenantId]);
END

I seed the first value before creating the trigger.

INSERT INTO [Auth].[Tenant]
VALUES (1,'Partition Trigger Test A');

I create the trigger against the [Tenant] table.

CREATE TRIGGER [TR_Tenant_Isolation] ON [Auth].[Tenant]
AFTER INSERT
AS
BEGIN
    DECLARE @MaxInsertedId int
    SET @MaxInsertedId = (SELECT MAX([TenantId]) FROM inserted)

    ALTER PARTITION SCHEME [PS_Tenant_Isolation]
        NEXT USED [Auth];

    ALTER PARTITION FUNCTION [PF_Tenant_Isolation]()
        SPLIT RANGE (@MaxInsertedId);
END

I follow this up with attempting to insert the second [Tenant] value.

INSERT INTO [Auth].[Tenant]
VALUES (1,'Partition Trigger Test B');

This is when the error described above pops up. Based the error itself as well reading the Technet arguments, I understand the issue to be in utilizing AFTER INSERT. Since the partition action of the transaction relies on utilizing the range value within the partition function, the ALTER PARTITION SCHEME fails, and thus the entire transaction as well.

  AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires.

I have looked into INSTEAD OF INSERT but have not had any success. The trigger fires once and updates the SPLIT RANGE with a value of 0 (implicitly converted from NULL). I believe this is due to the IDENTITY not being properly captured in the scope of the transaction.

CREATE TRIGGER [TR_Tenant_Isolation] ON [Auth].[Tenant]
INSTEAD OF INSERT
AS
BEGIN
    DECLARE @MaxInsertedId int
    SET @MaxInsertedId =  (SELECT [TenantId] FROM inserted)

    ALTER PARTITION SCHEME [PS_Tenant_Isolation]
        NEXT USED [Auth];

    ALTER PARTITION FUNCTION [PF_Tenant_Isolation]()
        SPLIT RANGE (@MaxInsertedId);

    INSERT INTO [Auth].[Tenant] ([TenantActive], [TenantName])
    SELECT [TenantActive], [TenantName]
    FROM inserted;
END

Subsequent row inserts into [Tenant] produce an additional error due to it trying to enter 0 (NULL).

  Duplicate range boundary values are not allowed in partition function boundary values list. The boundary value being added is already present at ordinal 1 of the boundary value list.

How can I work around this? Do I need to explicitly set the IDENTITY value of [TenantId] in conjunction with INSTEAD OF INSERT? New inserts into [Tenant] will be rather sporadic and minimal, but [TenantId] will be a constraining key across other tables. This is why I decided to investigate this implementation method so to dynamically alter the partition function.

The cryptic error in the AFTER trigger is due to performing a DDL against the trigger target table. With the INSTEAD OF trigger, you would need to perform the INSERT to get the assigned IDENTITY value and then split the partition function. However, you probably don't want to use IDENTITY here anyway since those may have gaps that are sometimes large and result in an untidy partition boundary list.

Below is an example that ditches the IDENTITY and uses a RANGE RIGHT function, which I believe is more natural for incremental partition boundaries. This version validates exactly one row is inserted but could be extended to handle multi-row inserts if needed. Your use case as I understand it suggests only rare singleton inserts.

--start with no partition boundaries
CREATE PARTITION FUNCTION [PF_Tenant_Isolation] ([int])
    AS RANGE RIGHT FOR VALUES ();
GO

CREATE PARTITION SCHEME [PS_Tenant_Isolation]
    AS PARTITION [PF_Tenant_Isolation]
    ALL TO ([Auth]);
GO

CREATE TABLE [Auth].[Tenant] (
     [TenantId] [int] NOT NULL
    ,[TenantActive] [bit] NOT NULL CONSTRAINT [DF_Tenant_TenantActive] DEFAULT 1
    ,[TenantName] [varchar](256) NOT NULL
    ,CONSTRAINT [PK_Tenant_TenantId] PRIMARY KEY CLUSTERED ([TenantId] ASC)
) ON [PS_Tenant_Isolation]([TenantId]);
GO

CREATE TRIGGER [TR_Tenant_Isolation] ON [Auth].[Tenant]
INSTEAD OF INSERT
AS
DECLARE @TenantId int;
BEGIN TRY

    --Get next TenantId and exclusively lock table to prevent deadlocking during DDL.
    --If other tables are partitoned via this function, add code to get exclusive locks on those too.
    SELECT TOP(1) @TenantId = COALESCE(MAX(TenantId),0) + 1 FROM [Auth].[Tenant] WITH(TABLOCKX);

    INSERT INTO [Auth].[Tenant] ([TenantId], [TenantActive], [TenantName])
        SELECT @TenantId, [TenantActive], [TenantName]
        FROM inserted;

    IF @@ROWCOUNT <> 1
    BEGIN
        RAISERROR('Exactly one row must be inserted into Auth.Tenant at a time',16,1);
    END;

    ALTER PARTITION SCHEME [PS_Tenant_Isolation]
        NEXT USED [Auth];

    ALTER PARTITION FUNCTION [PF_Tenant_Isolation]()
        SPLIT RANGE (@TenantId);

END TRY
BEGIN CATCH;
    THROW;
END CATCH;
GO

INSERT INTO [Auth].[Tenant]([TenantActive], [TenantName])
VALUES (1,'Partition Trigger Test A');
GO

EDIT:

  I see your notation, but given that queries will read from [Tenant],
  wouldn't the opposite happen where this would actually cause
  deadlocks?

The course-grained X lock on the Tenant table will wait for (be blocked by) other concurrent activity against the table to complete and, once granted, block other activity against the table. This blocking will prevent deadlocks on the Tenant table during the DDL operation within the trigger transaction. The duration of the SPLIT itself will be fast since rows are not moved between partitions. The duration of blocking before the initial block X lock is granted will depend on how long the other queries run.

In the case of multiple tables (i.e. related tables partitioned by schemes based on the same function), deadlocks can still occur if the locking order in the trigger is different than that of other activity. An exclusive lock on those tables too in the trigger can only mitigate the likelihood of deadlocks in that case. For example, if you have a SELECT query that joins Tenant and TenantDetails, both partitioned similarly, a deadlock may occur if the query acquires locks those tables in the reverse order as the trigger.

  Also, I understand with partition schemes you typically want to leave
  partitions on the left and right boundaries that are "empty" for
  proper switching.

Empty partitions are a consideration for SPLIT and MERGE but not SWITCH. With the SPLIT in the trigger, the split partition is always empty so no expensive data movement is needed to conform to the new boundary specification.

The general best practice is to MERGE boundaries when both adjacent partitions are empty. That said, you can sill MERGE without row movement as long as the partition containing the boundary (one on the right with a RANGE RIGHT function) is empty.


How to limit MongoDB database size Are there any mechanisms to limit the size of a single database on MongoDB 2.6? I found some options like quota, quotaFiles and nssize but am unsure how to use them.

Ok, it was simple :)

I wanted to limit each database to 256MB of space per student. In order to achieve that, I enabled smallfiles in /etc/mongod.conf and ran the mongod process with mongod -f /etc/mongod.conf --quota --quotaFiles 4 which limited the size of the database to 256MB.

smallfiles starts preallocating space with 16MB, then 32MB, 64MB and finally 128MB. Add this up and you get 240MB plus 16MB file for the namespace = 256MB

This however doesn't stop them from creating another database with the same user. Any help on this?


Insert PBKDF2 hash into PostgreSQL container I am using the PostgreSQL docker image with the following Dockerfile:

FROM postgres:10.1

COPY init-db.sh /docker-entrypoint-initdb.d/init-db.sh

and an init-db.sh script with the following content:

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL
    CREATE DATABASE foodb;

    \c foodb;
    DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id SERIAL,
    username VARCHAR(25) NOT NULL,
    pw VARCHAR(128) NOT NULL,
    super INT NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
  );

CREATE UNIQUE INDEX users_username ON users (username);

INSERT INTO users (username, pw)
VALUES ('jjolie',
        'PBKDF2$sha256$901$x8mf3JIFTUFU9C23$ic9J6mFDSz0rETKux/Co9TzCPOX50j+V8Vky5GkY5v0=');

After building the container (with the init-db script included) with docker build . --tag postgres, when the container is started with docker run postgres, and the users table is viewed:

docker exec -it postgres bash
psql -U postgres
\c foodb
select * from users;

We see the following output:

 id | username |                   pw                    | super
----+----------+-----------------------------------------+-------
  1 | jjolie   | PBKDF201/Co9TzCPOX50j+V8Vky5GkY5v0=     |     0

PBKDF201/Co9TzCPOX50j+V8Vky5GkY5v0= differs from PBKDF2$sha256$901$x8mf3JIFTUFU9C23$ic9J6mFDSz0rETKux/Co9TzCPOX50j+V8Vky5GkY5v0='.

Why did the password change from PBKDF2$... to PBKDF201..? How can it be prevented?

Because the variables are replaced (with nothing) in bash, the dollars signs need to be escaped:

'PBKDF2\$sha256\$901\$x8mf3JIFTUFU9C23\$ic9J6mFDSz0rETKux/Co9TzCPOX50j+V8Vky5GkY5v0='


how to make shapefile in geometry column of ms sql? I was tried ogr2ogr command like this.

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport.shp" "MSSQL:server=[RemoteServerIp];database=[dbname];User Id=[user];Password=[password];trust_connection=yes;" -sql "SELECT [geometrycolumn].STAsBinary() FROM [tableName] WHERE [indexColumn] = 1;" -overwrite

but I saw error message.
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

I don't know what is wrong.

The issue is that you are running your GIS programme from a domain that is not trusted by the domain in which SQL Server resides.

That are numerous methods you can use to correct this. You should discuss with your network admin/sql server admin as to the best approach.

Create a SQL Server Account and use those credentials to connect to the SQL Server <-- this might be the easiest
Trust the domain in which your GIS Programme is running <--I would be loath to do this


SQL2043N Unable to start a child process or thread I've seen the message SQL2043N Unable to start a child process or thread occur every once in a while since upgrading to rhel7 and db2 10.5. db2diag.log does not reveal much out of interest, beside:

FUNCTION: DB2 UDB, oper system services,
sqlo_waitlist::initialize, probe:10
MESSAGE : SysV semget failed with errno:
DATA #1 : Hexdump, 4 bytes

The target machines are used for unit-testing and development and relevant kernel parameters seems appropriate:

]$ cat /proc/meminfo
MemTotal:       12140640 kB

]$ ipcs -l
[...]
------ Semaphore Limits --------
max number of arrays = 3072
max semaphores per array = 250
max semaphores system wide = 256000
max ops per semop call = 32
semaphore max value = 32767

At the moment there are no databases on the machine, yet - what I believe - a huge number of semaphores is associated with db2fenc1:

]$ ipcs -s | grep db2fenc1 | wc -l
2800 [1]

[1] The number 2800 is not the exact number, but it was close to this

]$ db2stop force

has no effect on the number of semaphores in ipcs -s. To release the semaphores I had to do ipclean

The only thing I can think of that might be causing db2fenc1 to grab a lot of semaphores is a fenced java function:

CREATE FUNCTION NYA.REMOVE_DIACRITICS( S VARCHAR(50))
    RETURNS varchar(50)
    FENCED THREADSAFE
    DETERMINISTIC
    NO SQL
    LANGUAGE JAVA
    PARAMETER STYLE JAVA
    RETURNS NULL ON NULL INPUT
    EXTERNAL NAME
    'StringUtil:se.vhs.nya.framework.common.util.db.DiacriticUtil!removeDiacritics'
NO EXTERNAL ACTION @

The functions is used in an generated column:

CREATE TABLE ...
( [...]
, <COLNAME2> VARCHAR(25) NOT NULL
    GENERATED ALWAYS AS ( NYA.REMOVE_DIACRITICS( <COLNAME1> ) )
, [...] ) @

but there is no massive insert/updates against this table during testing.

Has anyone experienced something similar where semaphores are not released, and in that case, what was the solution?

PS. Not sure if it is relevant, but the JAVA_HEAP_SZ is:

(JAVA_HEAP_SZ) = 2048

Before reporting this as error, the max number of processes (ulimit -u) should be checked and increased for the instance owner.
I'm sure they would ask it anyway.


Filtering the results of a SELECT query to avoid certain words in the value of a column? I have a table called videos with a column named title and there is a set of words (around 50 words as of now but the number of words may increase) which is the knowledge base for filtering the content.

For example

  porn, pornography, Adult, Boobs, Booty, Tits

pseudo query may be like

SELECT * FROM videos WHERE title NOT LIKE (porn, pornography, Adult, Boobs, Booty, Tits)

I checked NOT LIKE but I have to do it like

SELECT * FROM videos WHERE title NOT LIKE ‘%porn%’ AND title NOT LIKE ‘%pornography%’ AND title NOT LIKE ‘%Adult%’ AND title NOT LIKE ‘%Booty%’ AND title NOT LIKE ‘%Boobs%’ AND title NOT LIKE ‘%Tits%’

which is very inconvenient and inefficient. So is there any other better way to do this ?

I've written this in SQL Server so you may have to translate to MySQL but this should work for you.

CREATE TABLE #ExcludeList (ExcludeVal varchar(50))

CREATE TABLE #VideoList (VideoName varchar(500))

INSERT INTO #ExcludeList VALUES ('Prob1'),('Prob2'),('DontUse1')

INSERT INTO #VideoList VALUES ('This is a prob1'),
            ('This is clean'),('This is also clean'),
            ('DontUse1 this one is bad')

SELECT *
FROM #VideoList VL
WHERE NOT EXISTS (SELECT 1 FROM #ExcludeList EL
                    WHERE VL.VideoName LIKE '%' + EL.ExcludeVal + '%')


How to order by alphanumeric codes in SQL Server I have a table with some project codes. These codes are

e.g.: B001, B002, Z15001, Z14001, P003,...

I need this result table:

Z15001
Z15002
...
B001
B002
...
C001
C002
...
Z14099
Z14098
Z14097
...
Z13099
Z13098
...
Z13001

I tried this but the order is wrong.

select *
from table
order by
    case
        when Kod like 'Z15%' then 1
        when Kod like 'B%' then 2
        when Kod like 'C%' then 3
        when Kod like 'D%' then 4
        when Kod like 'E%' then 5 else 6 end asc, Kod asc
    , case when Kod NOT LIKE 'B%'
        AND Kod NOT LIKE 'C%'
        AND Kod NOT LIKE 'D%'
        AND Kod NOT LIKE 'E%'
        AND Kod NOT LIKE 'Z15%' then 6
      end desc

How I can do this in SQL Server?

In case you are interested on running this kind of sort repeatedly, or on a larger dataset, you may want to consider adding a key table to control the sort order, and JOINing to that table.

First, we setup the test environment:

USE tempdb;
CREATE TABLE dbo.K
(   KOD VARCHAR(255) NOT NULL CONSTRAINT PK_K
        PRIMARY KEY CLUSTERED
);

CREATE TABLE dbo.KSort
(
    KODSort INT NOT NULL CONSTRAINT PK_KSort
        PRIMARY KEY CLUSTERED
    , KODPrefix VARCHAR(255) NOT NULL
);

INSERT INTO dbo.K (KOD)
VALUES ('Z15001')
    , ('Z15002')
    , ('B001'  )
    , ('B002'  )
    , ('C001'  )
    , ('C002'  )
    , ('Z14099')
    , ('Z14098')
    , ('Z14097')
    , ('Z13099')
    , ('Z13098')
    , ('Z13001');

INSERT INTO dbo.KSort(KODSort, KODPrefix)
VALUES (10, 'Z15%')
    , (20, 'B%')
    , (30, 'C%')
    , (40, 'Z14%')
    , (50, 'Z13%');

SELECT K.*
FROM dbo.K;

This shows the expected result, which just happens to be sorted in order of the clustering key:

Now, two queries, the first with the standard ORDER BY clause to sort the results "by hand".

SELECT *
FROM dbo.K
ORDER BY
    CASE
        WHEN Kod LIKE 'Z15%' THEN 1
        WHEN Kod LIKE 'B%' THEN 2
        WHEN Kod LIKE 'C%' THEN 3
        WHEN Kod LIKE 'Z14%' THEN 4
        WHEN Kod LIKE 'Z13%' THEN 5
        ELSE 6
    END ASC;

The plan for this is:

Nice and simple.  The "Sort" operator is taking 77.6% of the plan cost.

If we use a JOIN to accomplish our sort instead, like so:

SELECT K.*
FROM dbo.K
    INNER JOIN dbo.KSort ON K.KOD LIKE KSort.KODPrefix
ORDER BY KSort.KODSort, K.KOD;

We start with a much simpler piece of code, which appeals to me on a deep level; however we also see an interesting execution plan:

Instead of a sort operator, we now have several nested loop joins.

When dealing with a small amount of data, the difference is inconsequential once we have the data in SQL Server's buffer.

However, lets see what happens when we add more rows to the KOD table.

TRUNCATE TABLE dbo.K;

INSERT INTO dbo.K(KOD)
SELECT TOP(1000000) CASE ROW_NUMBER() OVER (ORDER BY o.object_id) % 5
   WHEN 0 THEN 'Z15'
   WHEN 1 THEN 'B'
   WHEN 2 THEN 'C'
   WHEN 3 THEN 'Z14'
   WHEN 4 THEN 'Z13' END
    + CONVERT(VARCHAR(255), ROW_NUMBER() OVER (ORDER BY o.object_id))
FROM sys.objects o, sys.objects o1, sys.columns c;

(The above adds 1,000,000 rows to the table)

On my machine, which is a 4-core Intel I7 with 16GB RAM, I see the following with SET STATISTICS IO,TIME ON; configured:

The "standard" sort:

(1000000 row(s) affected)
Table 'K'. Scan count 9, logical reads 2679, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1094 ms,  elapsed time = 6463 ms.

The "JOIN":

(1000000 row(s) affected)
Table 'K'. Scan count 5, logical reads 2641, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'KSort'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 437 ms,  elapsed time = 5475 ms.

I used DBCC FREEPROCCACHE before starting each query, the results are very similar across multiple runs.  Typically the standard sort is 2 to 3 times slower than the JOIN version.

Above-and-beyond performance implications, using the key-table I propose (dbo.KSort) allows you to change the sort order of the output at will without having to ever modify code, which is a pretty nice bonus, in my opinion.  You simply modify the values of the KODSort column so the results are returned in whatever order you like.  For instance, the following UPDATE would make the Z14* rows display prior to the B* rows:

UPDATE KSort SET KODSort = 15 WHERE KODSort = 40;


Shrinking during backup All,

I'm familiar with the good reasons not to shrink a database and I am in the process of shrinking a 2TB one. We have a large database that gets backed up each day (by policy) and because of the size, the backup runs most of a day. We have been running for some time with over 60% free and that shouldn't change significantly. I'm reducing the table size to mitigate what the backup is doing to our performance.

The table is large and shrinking is quite slow so I am running a DBCC SHRINKFILE in a looping script where I shrink 250MB at a time. I'm doing this because when the backup starts SHRINKFILE errors out saying it can't change the file system. I'm assuming this is because the backup has the file locked so the file isn't changing in the middle of the backup. I am doing the small shrinks as I been in the middle of one when the backup starts. So I loop it so I can see the shrinkfile errors and kill my script - the backup can start at different times.

My question is that although the DBCC SHRINKFILE can't change the file after the backup starts and I get the error message, the first iteration of the script when the backup is done goes quite quickly and then settles into a normal cadence. As folks have said if you kill a SHRINKFILE, you "don't lose your work". Is the results of the shrink sitting out in tempdb somewhere ready to go? To get this shrink done a little quicker, does anyone think running a larger shrinkfile during the backup that I know won't complete is a good idea? It would give me a jump on the next day.

Thanks,

Pat

Are you shrinking before backup in the hope that it will reduce your backup time?  If so that will not make any difference. But if you are doing it so it allocates less space on your target (when you restore) then it will help.

I did not understand if you are doing this every time before backup. If yes you want to resolve this permanently and not do this.

Now to answering your question.

Is the results of the shrink sitting out in tempdb somewhere ready to go?

It does not have to keep the result. It will check everything again once you restart.

To get this shrink done a little quicker, does anyone think running a larger shrinkfile during the backup that I know won't complete is a good idea?

No, because it does 32 pages at one time and commit. See below.

Reference: https://blogs.msdn.microsoft.com/psssql/2008/03/28/how-it-works-sql-server-2005-dbcc-shrink-may-take-longer-than-sql-server-2000/

  Shrink works in transaction batches of ~32 pages.   Once 32 pages are
  moved below the truncation point the transaction is committed and a
  new transaction started.   This avoids an older issue with shrink*
  that kept a transaction open for a long time and caused the log files
  to grow because you can’t truncate past an active transaction.   It
  also makes shrink* restart capable.    If you cancel/kill a shrink*
  operation only the current active transaction is rolled back.   This
  means you could schedule a shrink* operation during a maintenance
  window and limit its runtime to that maintenance window.    You can
  restart it during the next maintenance window and it will pick up from
  where it left off.   The only disadvantage to this is that allocations
  could occur after the target once the shrink is terminated.   This may
  require shrink to do additional work for newly allocated space but
  this should be reasonably limited.

On a side note backing up 2TB should not take a full day. There are switches in native backup, can speed up/optimize your backup duration.


Order implicit groups Given this set...

Site, Date
4   10/10/18
3   10/10/18
4   07/10/18
5   12/10/18
3   11/10/18
5   13/11/18

I want to order it such that the rows are ordered by site and the site groups are ordered by MIN date.

i.e.

Site, Date
4   07/10/18
4   10/10/18
3   10/10/18
3   11/10/18
5   12/10/18
5   13/11/18

I have tried using...

SELECT

    RANK() OVER (PARTITION BY Site ORDER BY (Date)) AS GroupRank,
    a.*
FROM

    (select * from
    (select 4 AS Site,   '10/10/18' as date union
    select 3,   '10/10/18' union
    select 4,   '07/10/18'  union
    select 5,   '12/10/18'  union
    select 3,   '11/10/18'  union
    select 5,   '13/11/18'  as date ) b) a
    order by GroupRank

The results don't make sense.

1   3   10/10/18
1   4   07/10/18
1   5   12/10/18
2   5   13/11/18
2   4   10/10/18
2   3   11/10/18

It's supposed to be partitioning by site but it's given the same rank to rows with different sites.

Something like this:

SELECT

    min(date) OVER (PARTITION BY Site) AS MinDate,
    a.*
FROM

    (select * from
    (select 4 AS Site,   '10/10/18' as date union
    select 3,   '10/10/18' union
    select 4,   '07/10/18'  union
    select 5,   '12/10/18'  union
    select 3,   '11/10/18'  union
    select 5,   '13/11/18'  as date ) b) a
    order by MinDate, Site


How to remove spaces between words and replace with single underscore using 'REPLACE','INSTR' OR 'SUBSTR' oracle function I want to replace spaces between words with a single underscore. I can't use the replace_regex Oracle function to do this, because it is not supported by the converter which we are using to convert sql statements so that they will be independent of platform such as SQL Server, PostgreSQL etc. .

Currently converter is providing support for 'substr' , 'replace', and 'instr' oracle functions. how can i use these three functions to get below output.
Example:

"my name is            xyz" =>  "my_name_is_xyz"
"test    sdf"               =>  "test_sdf"

Something like this might work for you, at least if replace does the same on all your platforms as in SQL Server.

Replace all spaces with space+underscore
Replace underscore+space with an empty string
Replace space with an empty string

replace(replace(replace('my name is            xyz', ' ', '_ '), ' _', ''), ' ', '');


When executing a command via xp_cmdshell what user issues the command to the system? I'm executing the following command to stop a service on a remote server from within a stored procedure in SQL Server.

----------------
-- Stop Service
----------------
SET @Command = 'sc \\' + @Server + ' stop ' + @ServiceName;
EXEC master.dbo.xp_cmdshell @Command;

Depending on the service sometimes this executes successfully but other times I receive the following error:

[SC] OpenService FAILED 5:
NULL
Access is denied.
NULL
NULL

Im curious what user executes the command.  Is it the user I login/connect with to the database?  Or is it the user Im logged onto the physical server with?  Can I pass in different authentication with the xp_cmdshell?

From xp_cmdshell (Transact-SQL) in the product documentation:

The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account.
When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.

See the full entry for the steps you need to take to set up the xp_cmdshell proxy account.


How can I use COUNT in the same query with ROUND() and AVG()? I need to write a query where I need to first count the tickets sold for spectacles in a theater, then calculate the average number of tickets sold in each theater and to be round it to two decimal place and these values must to be sort by the number of tickets sold. In principle, I want to find the average number of tickets sold for each theater. For this query I must use AVG() and ROUND(). I tried so many different options, but I have different errors. When I tried to run this, this error appeared :
SQL Error: ORA-00937: not a single-group group function
SELECT
    t.id_theater
    , s.id_spectacle
    , ROUND((AVG(COUNT(tickets.id_ticket))), 2) average_tickets_theatre
FROM
    tickets tick
    , theater t
    , spectacles s
WHERE
    tick.id_spectacle = s.id_spectacle
    AND t.id_theater = s.id_theater
GROUP BY
    t.id_theater
    , s.id_spectacle
ORDER BY
    average_tickets_theatre

I tried to add the HAVING clause, but another error appeared:
SQL Error: ORA-00933: SQL command not properly ended

I'm sure that the problem consists in the fact that I'm trying to use COUNT with ROUND() and AVG(), but I really don't know what do to at this point and would appreciate any help.

The current version gives you an error because you're trying to get one aggregate function (average) over another one (count) ; it's not related to round.  As far as I understand, you want to output id_theater, id_spectacle, count of tickets per theater and spectacle, and also average number of tickets per theater.  One way (most likely not the fastest from performance perspective) would be
with t1 as
(SELECT
    t.id_theater
    , s.id_spectacle
   , COUNT(tick.id_ticket) as num_tickets

FROM
    tickets tick
    , theater t
    , spectacles s
WHERE
    tick.id_spectacle = s.id_spectacle
    AND t.id_theater = s.id_theater
GROUP BY
    t.id_theater
    , s.id_spectacle
)
select  id_theater,id_spectacle , num_tickets, ROUND(avg(num_tickets) over (partition by id_theater),2) as avg_per_theater
from t1

Also, I'd suggest using ANSI-92 join instead of ANSI-89 syntax, it's way more readable


Rebuilding, truncating etc SQL Server 2008 maintenance About a month or so ago I inherited this somewhat-new database. I was lead to believe that everything was set up and all maintenance tasks were running without a hitch. SQL Server 2008 R2

I was looking at it today while trying to troubleshoot some slowness and discovered that we are almost out of space on our ~500gb array. We have multiple tables whose sizes are upwards of 6gb, which I'm not too concerned about. BUT they have corresponding log files that are around 5x the size! One of them is 31gb. Their modification dates are 3 days ago. The little I have read leads me to believe this is incorrect.

My questions-

Can I safely truncate these log files?
How often should I do this, and when?  (The nightly backup is at 12am.)
I see no re-indexing, statistic updating, etc.  Is it safe to go ahead and set these tasks up?  Should I wait until after the aformentioned truncation?
Can the software writing/reading to the database be used while these tasks are being performed?  We are 24 hours and stopping production is very difficult.
We also have a LOT of backup files.  Like, one for every table for every day since February.  Can I delete some of them?  Is there a way to set the backup to do this automatically?

Thanks so much guys.  We have an IT department but I'd rather not have to wait on them, especially if these things are easy to do.  I'd rather set them up myself (if possible) and let a DBA check in on them occasionally.

Your questions cover a lot of scope for SQL Server database maintenance.  We need to address two areas here:  Backups and Index Maintenance.

Backups

The first thing you must understand before you can properly manage your backups is your recovery model.  This will determine how your transaction log is managed by the engine.  The simple description (the "real answer" is far more involved) is that your log file contains the records of each transaction that has occurred in your database.  In relation to that, your recovery model tells the engine whether or not you want point in time recovery (Full/Bulk Logged if you do, Simple if you don't).  Based on this information, the engine will remove records from the log file at checkpoints if it considers them safe to remove:

Full/Bulk Logged:  Since point in time recovery is desired, log records will not be removed until they have been safely backed up.  The engine needs to ensure that these log records can be made available for recovery.  Note, this is different than a full database backup.  If you need to restore your database, you will require both a full database backup and attendant log backups.  More detail on this restore process can be found here.
Simple Recovery: Log records are removed if transactions are completed.  Since point in time recovery is not desired, the engine will not retain the log records.

In regards to your other concerns, the backup operations are done online, so they won't directly impact your database.  Full backups capture a point in time, so any previous files will represent a previous point in time and, if you don't need those previous points in time, you won't need to retain those older backup files.

As for a backup schedule(frequency and retention of files), this is very much of an "it depends" answer.  It is discussed in more detail here.

Index Maintenance

This is a MUCH deeper topic, but essentially what you're looking to do is manage your index fragmentation so that it does not impact performance.  There's a lot written out there on what it is and how to manage it.  ToadWorld is a good place to start on how to manage your indexes and what index maintenance means.  This can impact use of your database (though the database will remain online for the operation), but there's lots of different factors around this impact and how to manage it.

Maintenance Plan

If you have DBA resources in your company, these tasks are within their bailiwick and they will likely have tools and a roadmap for managing these already.  If you don't have these resources, there are several tools that can be leveraged.  For maintenance as a whole, Ola Hallengren provides some free tools for this that are highly regarded and recommended in the SQL community.


Can't start Postgresql I am unable to start postgresql on Ubuntu

postgres@DESKTOP-1TPO12P:~$ /etc/init.d/postgresql start
     * Starting PostgreSQL 10 database server                                                                                * Error: /usr/lib/postgresql/10/bin/pg_ctl /usr/lib/postgresql/10/bin/pg_ctl start -D /var/lib/postgresql/10/main -l /var/log/postgresql/postgresql-10-main.log -s -o  -c config_file="/etc/postgresql/10/main/postgresql.conf"  exited with status 1:
    2019-04-30 14:58:57.825 DST [123] LOG:  listening on IPv4 address "127.0.0.1", port 5432
    2019-04-30 14:58:57.837 DST [123] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
    2019-04-30 14:58:57.891 DST [124] LOG:  database system was interrupted; last known up at 2019-04-30 14:44:15 DST
    2019-04-30 14:58:57.891 DST [124] PANIC:  could not flush dirty data: Function not implemented
    2019-04-30 14:58:57.891 DST [123] LOG:  startup process (PID 124) was terminated by signal 6: Aborted
    2019-04-30 14:58:57.891 DST [123] LOG:  aborting startup due to startup process failure
    2019-04-30 14:58:57.901 DST [123] LOG:  database system is shut down
    pg_ctl: could not start server
    Examine the log output.

I installed it using apt-get install postgresql-10

Given the error message, this is most probably happening under WSL (Windows Subsystem for Linux).

There have been recent changes in PostgreSQL related to fsync that turn out to be problematic with WSL. The specific PANIC error mentioned and a candidate solution is discussed in detail here:

WSL (windows subsystem on linux) users will need to turn fsync off as of 11.2

Except it's not just 11.2 because the changes were made in other supported versions like the 10.7 you're probably using if you're up to date in the 10.x branch.

Note that the workaround suggested in the subject, turning fsync to off, is not for production use. It's only okay if you're playing around with Postgres on WSL, and don't really care about the durability of your data. Some say that WSL itself is not for production use anyway.

As you're using PostgreSQL 10, it's possible that this problem will be mitigated in the next bugfix release, version 10.8. Two fixes have been committed in the 10.x branch last February that look like they might address the issue.


Why does SqlSentry Plan Explorer not give the duration of my queries? I have a large nasty stored procedure in a 13gig database that takes 45 minutes to run when I include the actual execution plan. I save the query plan from SSMS and open in up in SQL Sentry Plan Explorer and I don't see a duration for any of the queries:

I've searched through the entire list of queries and resorted it, the entire column is empty. I know some queries in this particular stored procedure take a long time to run. While the stored procedure is large, it not a stored proc with millions of microsecond queries, and I know for a fact that certain queries take minutes to run.

Martin is correct, in order to see duration and other metrics from an actual plan with Plan Explorer, "Get Actual Plan" must be run from within Plan Explorer. Actual plan XML does not contain this data, regardless of source.

Plan Explorer FREE uses STATISTICS TIME & IO behind the scenes and auto-correlates the data with the plan tree, and Plan Explorer PRO uses a dynamic rowset trace for this purpose. An advantage of the latter method is the ability to provide the full call stack including plans and metrics for embedded dynamic SQL calls. Neither method is affected by statement duration.

This is why we use our own .queryanalysis file format when saving plans from within Plan Explorer, as it contains this extra metadata along with the plan XML.


Is it possible to concatenate query results to form a new query? I have now:

SELECT TABLE_SCHEMA ,
   TABLE_NAME ,
   COLUMN_NAME
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'NNN';

This gives me several hundreds of rows, because several tables contain field with that name. I want to search some value from these tables and columns by using the previous data in new query like:

SELECT <COLUMN_NAME> FROM <TABLE_SCHEMA>.<TABLE_NAME>

Could I include the results of the first query to the tags in second one in one query and how? I would like to avoid calling the every schema, table and column by hand to check only minor things in every table.

Source:

I took the first query from:
https://stackoverflow.com/questions/420741/getting-list-of-tables-and-fields-in-each-in-a-database

Unless you are explicitly trying to write queries that work in multiple engines, I recommend sticking to the catalog views in the sys schema rather than INFORMATION_SCHEMA (see this and this). You'll need dynamic SQL for this anyway, and in other engines the syntax for this will vary greatly:

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'SELECT ' + QUOTENAME(c.name) + N' FROM '
  + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.columns AS c
ON t.[object_id] = c.[object_id]
WHERE c.name = N'NNN';

EXEC sys.sp_executesql @sql;

If you want the column to be listed as the name of the table instead of the name of the column:

...
SELECT @sql += N'SELECT ' + QUOTENAME(t.name) + N' = '
  + QUOTENAME(s.name + N'.' + t.name) + N' FROM '
  + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'
FROM
...

And if you want them all in a single result set, you could add the table name as a column, and use a UNION (and coerce all the columns into a compatible type, just in case):

...
SELECT @sql += N'UNION ALL SELECT [table] = '''
  + QUOTENAME(s.name + N'.' + t.name) + N''', '
  + N' value = CONVERT(nvarchar(4000),'
  + QUOTENAME(c.name) + N') FROM '
  + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'
FROM
...

SET @sql = STUFF(@sql, 1, 10, N'');
...


Automatically restore SQL Server database from file from another server We have a daily task to overwrite a number of development databases using backups of the associated production databases. The backups are produced by maintenance plans on the production server then transferred to the dev server by FTP. Each day we run a SQL statement similar to this to overwrite each database:

RESTORE DATABASE [Database1]
FROM DISK = N'D:\path\to\Database1_backup_2015_02_05_190004_7401803.bak'
WITH FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

Each time we run this we have to replace the file name with the correct most recent file. I would like to automate this somehow to minimise the chance of operator error. The problem is that we can't control the name of the .bak file (although the format is consistent - database name, date, time and whatever that seven digit number is), and the folder will usually contain several days worth of backups.

Since all the details on backups are maintained in the msdb database, you should just extract the backup file name from the source server.

You could create a linked server from your Dev Server to access the Production Server's msdb database. Or you can use OPENQUERY to query the same data.  (OPENQUERY may be faster since the query is actually being run on the Production Server.)

For example:

SELECT * from OPENQUERY([LinkToPRD],
     'EXEC database.dbo.ExecDailyRestore');

This shows running a stored procedure with no parameters, which might suit your daily restore plan.

If you search for "auto generate sql server database restore scripts" you will find many scripts.  An example from Paul Brewer is sp_RestoreGene that you might use as is or as a basis to create your own ExecDailyRestore stored procedure.

https://paulbrewer.wordpress.com/sp_restoregene/

Here are the parameters supported by sp_restoregene:

    @Database SYSNAME = NULL,
    @TargetDatabase SYSNAME = NULL,
    @WithMoveDataFiles VARCHAR(2000) = NULL,
    @WithMoveLogFile  VARCHAR(2000) = NULL,
    @FromFileFullUNC VARCHAR(2000) = NULL,
    @FromFileDiffUNC VARCHAR(2000) = NULL,
    @FromFileLogUNC VARCHAR(2000) = NULL,
    @StopAt DATETIME = NULL,
    @StandbyMode BIT = 0,
    @IncludeSystemDBs BIT = 0,
    @WithRecovery BIT = 0,
    @WithCHECKDB BIT = 0,
    @WithReplace BIT = 0,
    @UseDefaultDatabaseBackupPath BIT = 0,
    @Log_Reference VARCHAR (250) = NULL,
    @LogShippingVariableDeclare BIT = 1,
    @LogShippingStartTime DATETIME = NULL,
    @LogShippingLastLSN VARCHAR(25) = NULL

And here is a sample script:

RESTORE DATABASE db_workspace
FROM DISK = 'X:\Backups\Temp\db_workspace.bak' WITH REPLACE,
FILE = 1,CHECKSUM,NORECOVERY, STATS=10
, MOVE 'db_workspace' TO 'x:\data\db_workspace.mdf'
, MOVE 'db_workspace_log' TO 'x:\data\db_workspace_log.ldf'
, MOVE 'db_workspace_FG2' TO 'x:\data\db_workspace_FG2.ndf'
, MOVE 'db_workspace_FG1' TO 'x:\data\db_workspace_FG1.ndf'


Why index is not used with order by? I trying to get info about why MySQL does not using my index when I create a inner join and trying to ORDER BY on the end.

I have my SQL query here:

SELECT
    *
FROM
    product p INNER JOIN productStore ps ON p.productUUID = ps.productUUID
ORDER BY
    ps.storeTitle
LIMIT 50;

When I'm using order by this select take over 3,5 sec, when i remove order by its taking like 1,6ms to run the same SQL, my explain SQL is follow

With ORDER BY:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  ps  ALL PRIMARY NULL    NULL    NULL    942187  Using filesort
1   SIMPLE  p   eq_ref  PRIMARY PRIMARY 16  foeniks_core.ps.productUUID 1   NULL

Without ORDER BY:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  ps  ALL PRIMARY NULL    NULL    NULL    942187  NULL
1   SIMPLE  p   eq_ref  PRIMARY PRIMARY 16  foeniks_core.ps.productUUID 1   NULL

the field there not are indexing right is a varchar on 282 length.

My table design is here:

CREATE TABLE `productStore` (
  `productUUID` binary(16) NOT NULL,
  `storeUUID` binary(16) NOT NULL,
  `distributorLastUsed` binary(16) DEFAULT NULL,
  `storeTitle` varchar(282) DEFAULT NULL,
  `storeUrl` varchar(282) DEFAULT NULL,
  `storeDescription` text,
  `storeDescriptionDemo` text,
  `storePrice` int(11) NOT NULL DEFAULT '0',
  `storePriceNext` int(11) NOT NULL DEFAULT '0',
  `storePriceCost` int(11) NOT NULL DEFAULT '0',
  `overwrites` int(11) NOT NULL DEFAULT '0',
  `updated` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `added` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `allowDisplay` tinyint(1) NOT NULL DEFAULT '0',
  `activated` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`productUUID`,`storeUUID`),
  KEY `productStoreLanguageToStore_idx` (`storeUUID`),
  KEY `productStoreToDistributor_idx` (`distributorLastUsed`),
  KEY `storeUrl` (`storeUrl`(180)) USING BTREE,
  KEY `testStoreTitle` (`storeTitle`(182)),
  CONSTRAINT `productStoreToDistributor` FOREIGN KEY (`distributorLastUsed`) REFERENCES `distributor` (`distributorUUID`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `productStoreToProduct` FOREIGN KEY (`productUUID`) REFERENCES `product` (`productUUID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `productStoreToStore` FOREIGN KEY (`storeUUID`) REFERENCES `store` (`storeUUID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Product table:

CREATE TABLE `product` (
  `productUUID` binary(16) NOT NULL,
  `productManufacturerUUID` binary(16) NOT NULL,
  `productManufacturerSKU` varchar(40) DEFAULT NULL,
  `productEan` varchar(40) DEFAULT NULL,
  `cnetID` varchar(10) DEFAULT NULL,
  `edbID` int(10) DEFAULT NULL,
  `overwrites` int(10) NOT NULL DEFAULT '0',
  `updated` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `added` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `activated` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`productUUID`),
  KEY `manufacturerSKU` (`productManufacturerSKU`(16)),
  KEY `productToManufacturer_idx` (`productManufacturerUUID`),
  KEY `cnetID` (`cnetID`),
  KEY `productEAN` (`productEan`),
  CONSTRAINT `productToManufacturer` FOREIGN KEY (`productManufacturerUUID`) REFERENCES `manufacturer` (`manufacturerUUID`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

David Spillett's answer is correct in all points, except for the "encouraging" suggestion.

Here's a way to not only encourage but (in almost all versions) force the optimizer to choose a plan that uses the wanted index to find the 50 rows - and only after that perform the join. It can't always be used but the FOREIGN KEY constraint assures that in this case the two queries will produce identical results.
I call this technique "first LIMIT, then JOIN":

SELECT     p.*, ps.*
FROM       ( SELECT     *
             FROM       productStore
             ORDER BY   storeTitle
             LIMIT 50
           ) ps
    INNER JOIN product p
        ON p.productUUID = ps.productUUID
ORDER BY   ps.storeTitle ;


How to view contents of table in mysql-workbench Hi everyone am fairly new to mysql, I made a java program that stores data in mysql database. I am using this tool called mysql-workbench that can create databases, tables and much more. What I wanted to do is to view all the contents of the table I just populated with data in my java program. I know that one can view all the contents of a table using some SQL scripts, however am wondering if mysql-workbench can display all the contents in the table without entering SELECT mysql script in a sort of tabular form or similar to how they display contents of table in phpMyAdmin.

The reason why I wanted to do this is that I have 2-3k entries in that table and using a console is kind of messy. Any help or suggestions on how to do this? Thanks in advance.

After you create a connection to your database, execute the following two commands:
  USE <DATABASE NAME>;
  SELECT * FROM <TABLE NAME>;

Then MySQL Workbench will show another pane with the results.  This will be the entire contents of the table.


Why would  running ALTER TABLE  on a  column cause it to go from NOT NULL to NULL? I am mass updating a SQL Server database. I am changing all our numeric(38,0) columns to int (yes, SQL Server scripts were created from Oracle scripts).
Using SMO and C# (I am a sw engineer), I managed to generate really nice scripts like SQL Server Management studio would. It all works very nicely except for one particular issue:

for a a handful of tables, when I call ALTER TABLE [myTable] ALTER COLUMN [columnA] INT it decides to also change the column from NOT NULL to NULL. That of course is a huge issue since I need to regenerate primary keys for most of those tables on those particular columns.

Obviously, I have plenty of options using SMO to find out which columns are primary keys and force them to be NOT NULL after or while I am updating the data type, but I am really curious as to what can be causing this.

It depends on your setting of SET ANSI_NULL_DFLT_OFF or SET ANSI_NULL_DFLT_ON (read these for other effects too from SET ANSI_DEFAULTS).

Personally, I'd have specified this constraint explicitly if I wanted null or non null columns rather than relying on environment settings.

ALTER TABLE [myTable] ALTER COLUMN [columnA] INT NOT NULL

You can issue this command again and if a column is already at the desired nullability the command is ignored.

However, you also said that you only need to change it where it's part of a primary key. That should have no bearing on whether a column can be nullable or not from the modelling/implementation phase


How can I perform this database backup/sanitization task? I have a 3-node MariaDB/Galera cluster.  I need to do two things:

Back up the database regularly (multiple gigs in size) for a normal, in-case-everything-crashes-I-can-restore-from-this backup.
Have a sanitized version in which private data is obfuscated and key values are changed, for the purpose of distributing to developers.  (The idea is that every day, they can grab a fresh "devel snapshot" that is a content- and structural- facsimile of the real thing, but that in which any potentially sensitive data is sanitized, and devel-centric settings are changed.

I know how to do a mysqldump, but I'm afraid this will lock the db.

I know how to write the queries to obfuscate the data, but obviously I can't do this on the live db.  (I'm assuming that I need to import this into a different database... perhaps on a separate machine not in the cluster?)

I need this to be scripted (which I'm not scared of doing, I'm just not sure where to start.)

Can someone point me in the right direction?

Setup a slave.

Once everything is sync'd up and good.

Stop the slave (sql thread).
mysqldump slave data to orig.sql
run your queries to modify the data
mysqldump again to dev.sql
Restore the data using orig.sql
start slave to resume collection from the master.

This will take some time with a large db of course but it seems pretty straight forward to me.  It won't lock your master cluster at all (at least until you create the slave).  If you are modifying only a few tables you can tweak the mysqldump.

You can cron the operation and upload it to s3 or something for the devs.  Use some fast SSDs.


Does logical reads in RAM appears in wait stats or where? Concerning execution model of SQLOS (RUNNING state, RUNNABLE queue, WAITER list), what will be the state of a task when there is a logical read of pages in RAM currently going on?

If it's a WAITER list, what would be the most prevalent wait type?

Can I somehow measure the time that such operation takes?

I know lots of logical reads slow down your queries, lots of table / index scans (resides already in buffer pool) slow down your queries - I just want to know how do they appear in the statistics/dmv's or how to distinguish it from other "classic" wait types.

A worker goes on a waiter list when it needs to wait for some kind of resource. There is no resource to wait on for logical reads. Assuming that your buffer pool hasn't paged out the data is already in memory managed by SQL Server. Queries that do lots of logical reads are slow because they spend a lot of CPU doing those logical reads.

If you want to get an idea of what internal operations SQL Server is doing when performing logical reads then you could do ETW tracing using perfview or some other tool while running a query which is bottlenecked by logical reads. ETW tracing might be a good step if you believe that your workload has issues with memory throughput. Please note that this is probably not the case and the following demo is almost certainly the wrong way to approach a query performance issue.

First I'll hack together a query which I expect to spend most of its time doing logical reads:

DROP TABLE IF EXISTS #215016;

SELECT 1 ID, REPLICATE('Z', 1000) FILLER INTO #215016
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

SELECT t1.ID, t2.ID
FROM #215016 t1
INNER JOIN #215016 t2 ON t1.ID < t2.ID
OPTION (MAXDOP 1, QueryRuleOff BuildSpool);

SQL Server used a full core of CPU throughout the query's execution. Here are perfview results taken from a ten second sample:

Perhaps the circled method name is what you're interested in. For this query SQL Server spends about 5% of its CPU time on sqlmin!BPool::Get, which I assume has to do with actually performing the logical read. In defense of that idea, that method is what calls sqlmin!BUF::AcquireLatch most of the time:

and the number of shared latches taken during the query is about the same as the number of logical reads. But this is just a guess because method names aren't documented by Microsoft and I don't know the full scope of a "logical read".


Why the table gets deleted by the server in mysql? I have a table in mysql. I have one java application which will keep inserting the data into this table. So the size of the table grows drastically and at one point it gets deleted automatically by the mysql server. I am not sure about the reason yet. So please if anybody has come across this issue then please share the way to handle this, so that I can have my data preserved.

UPDATE

This is what I found in the sql log of the server.

> 2013-11-27T15:07:39.285156Z    9 Query    SELECT s.Sql_Id
>       ,s.Child_Number
>       ,s.Executions
>       ,s.Last_Active_Time
>       ,s.User_Io_Wait_Time
>       ,s.Object_Status
>       ,s.Plan_Hash_Value FROM   V$sql s WHERE  s.Sql_Text LIKE 'SELECT /*+ FIRST_ROWS XX */ T1.SYNC_TIMESTMP, T1.CUSTNO, T1.XAFID_ID_IFX,
> T1.XAFID_CLASSIFIED%' 2013-11-27T15:07:39.286132Z    9 Query  SHOW
> WARNINGS 2013-11-27T15:08:08.791015Z    9 Query   select
> @@session.tx_read_only
> 2013-11-27T15:08:08.791992Z    9 Query    drop table devicedata 2013-11-27T15:08:08.926757Z    9 Query    SHOW WARNINGS
> 2013-11-27T15:08:08.936523Z    9 Query    select @@session.tx_read_only

Thanks

Reading the log seems to make the problem fairly apparent:

2013-11-27T15:08:08.791992Z 9 Query drop table devicedata

That's not mysql dropping the table.  That's your application dropping the table.

There's another line in the log that is very suspicious:

 ,s.Plan_Hash_Value FROM V$sql s WHERE s.Sql_Text LIKE 'SELECT /*+ FIRST_ROWS XX */

V$sql is not a valid table name, at least not without backticks.  It sounds like the application may be trying to build the table name from a string and building the string incorrectly... when that query fails, it drops the table in some kind of misguided attempt at "recovery."

Search the code for the word "drop."  MySQL has no concept of dropping a table because it's too full.


delete tables on import of dump created by mysqldump There are 2 copies of the same database on 2 different storages which need to be in sync.

For example, database named test_db contains tables T1,T2,T3 tables.
T2,T3 are deleted.
Dump of the 1st copy is created using:

mysqldump --login-path=lp --add-drop-table test_db > dump.sql

and imported on 2nd storage using:

mysql --login-path=kp test_db < dump.sql

But deletion of tables T2,T3 on the 1st storage is not propagated this way to the 2nd storage.

How to propagate deletion of tables from the 1st storage to 2nd storage using mysqldump on the 1st and import of the dump on the 2nd?

You need to drop and re-create the database. So you need to use some extra / different options when creating the dump file:

mysqldump --login-path=lp --add-drop-database --databases test_db > dump.sql

And import with:

mysql --login-path=kp < dump.sql


How to allow a user to create databases only with a certain prefix? We're having a multi-user Linux server. I'd like to give each user the ability to create and manage databases at will, but to keep things sane, force them that each database name they create must start with their user name as prefix. For example, user joe would be only allowed to create databases like joe_app1 and joe_app2 but not app1 or jack_app1. Is this possible in PostgreSQL? Or is there another way how to separate name-spaces for user-created databases?

PostgreSQL does not allow you to do this inside PostgreSQL.  You cannot create a database inside a transaction so procedural language functions cannot create databases or drop databases.  I hope some day this is corrected, but it is the one piece of DDL that does not work inside a transaction.

What this means is you have to do it from outside the database with a separate application that runs whatever checks you want to run.  Some key things to keep in mind are:

You probably want to have a dedicated user (role with login, noinherit) for the database creation process.
You do not want to give the permission to create dbs to the general users.
The script should create the database and then ALTER DATABASE ... OWNER TO ... in order to ensure that the user has full permissions to it.


Can the view "v$database_block_corruption" get refresh after the corrupted block get fixed? I have a table that contain several corrupted data blocks and I use the stored procedure reside in the “DBMS_REPAIR” to mark the data blocks as corrupted data blocks so that I can keep query to the table. After the stored procedure is executed, everything goes fine, I use rman to check the corruption by command:

validate datafile number

and the output still keep reporting the corruption, nothing change in the v$database_block_corruption ,so howv$database_block_corruption can be refresh, did I have to rebuild the table by such statement ：

create table TABLE_BAK as select * from TABLE_CORRUPTED;
drop table TABLE_CORRUPTED;
RENAME table TABLE_BAK to TABLE_CORRUPTED;

By the way: I don`t have any backup to restore the data block.

It is not neccessary. You can leave the corruption in place if you have correctly marked the affected blocks as corrupt. But you must accept that tools such as RMAN will alert you about these blocks every time you perform an action on them. e.g. whenever you take a backup of this datafile.


Entity relationship many to many Is an association class in UML the same as an Link entity in ERD?

It's not strictly necessary to have an association class to model a M:M relationship.  You can just have classes with references to the two parents that would map directly to a link entity.  Semantically, an association class would limit you to one relationship between two entities.

A short blurb on association classes can be found here.


Join and Count based on Specific User ID in SQL I have the following tables, I inserted some dummy data in it for demonstration purposes. I would like to join these two tables in a way that based on the user ID, I can get the count of similar questions other users answered. For Example : Let's say I want to see how many people in my Answer table answered age as 30 like Alex. So how can I join these two tables based on User.USERID="1" ? My expected result should be 1 since There is only one user "Tom" who is 30 years old and is "M" Male.

CREATE TABLE Question (
  QID INT,
  QTxt VARCHAR(150),
  QOrder INT);

CREATE TABLE User (
  UID INT,
  UserName VARCHAR(150));

CREATE TABLE ANSWER (
  AID INT,
  ATxt VARCHAR(150),
  QID INT,
  USERID INT);

INSERT INTO User VALUES
 (1, 'Alex'),
 (2, 'Sarah'),
 (3, 'Tom');

INSERT INTO Question VALUES
 (1, 'Age', 1),
 (2, 'Gender', 2),
 (3, 'Weight',3),
 (4, 'Height',4);

INSERT INTO Answer VALUES
 (1, '30', 1 , 1),
 (2, '27', 1,2),
 (3, '30', 1,3),
 (4, 'M', 2,1),
 (5, 'F', 2,2),
 (6, 'M', 2,3),
 (7, '61', 4,1),
 (8, '165', 3,1);

This is how I did it and it doesn't work :(

SELECT *
FROM Answer
JOIN Question
ON Answer.QID IN (SELECT Answer.QID FROM Answer WHERE Answer.USERID=3)
   AND Answer.ATxt IN (SELECT Answer.ATxt FROM Answer WHERE Answer.USERID=3)

When you join to tables together you need to think of it comparing each row from the first table to each row of the second table and then returning the ones that match based on the join condition. The join condition is specified in the ON clause.

In almost all cases you want to reference both tables in the ON clause. Let's look at your example in SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE Question (
  QID INT,
  QTxt VARCHAR(150),
  QOrder INT);

CREATE TABLE User (
  UID INT,
  UserName VARCHAR(150));

CREATE TABLE ANSWER (
  AID INT,
  ATxt VARCHAR(150),
  QID INT,
  USERID INT);

INSERT INTO User VALUES
 (1, 'Alex'),
 (2, 'Sarah'),
 (3, 'Tom');

INSERT INTO Question VALUES
 (1, 'Age', 1),
 (2, 'Gender', 2),
 (3, 'Weight',3),
 (4, 'Height',4);

INSERT INTO Answer VALUES
 (1, '30', 1 , 1),
 (2, '27', 1,2),
 (3, '30', 1,3),
 (4, 'M', 2,1),
 (5, 'F', 2,2),
 (6, 'M', 2,3),
 (7, '61', 4,1),
 (8, '165', 3,1);

The ON condition in your case is Answer.QID = Question.QID:

Query 1:

SELECT *
  FROM Answer
  JOIN Question
    ON Answer.QID = Question.QID
 WHERE Answer.UserId = 3

Results:

| AID | ATXT | QID | USERID |   QTXT | QORDER |
|-----|------|-----|--------|--------|--------|
|   3 |   30 |   1 |      3 |    Age |      1 |
|   6 |    M |   2 |      3 | Gender |      2 |

You can use aliases to shorten that query a little:

Query 2:

SELECT *
  FROM Answer AS A
  JOIN Question AS Q
    ON A.QID = Q.QID
 WHERE A.UserId = 3

Results:

| AID | ATXT | QID | USERID |   QTXT | QORDER |
|-----|------|-----|--------|--------|--------|
|   3 |   30 |   1 |      3 |    Age |      1 |
|   6 |    M |   2 |      3 | Gender |      2 |

The same way you can now also join in the User table:

Query 3:

SELECT *
  FROM Answer AS A
  JOIN Question AS Q
    ON A.QID = Q.QID
  JOIN User AS U
    ON A.UserId = U.UID
 WHERE U.UserName = 'Tom'

Results:

| AID | ATXT | QID | USERID |   QTXT | QORDER | UID | USERNAME |
|-----|------|-----|--------|--------|--------|-----|----------|
|   3 |   30 |   1 |      3 |    Age |      1 |   3 |      Tom |
|   6 |    M |   2 |      3 | Gender |      2 |   3 |      Tom |

For more information on JOINs check out my JOIN series here: A Join a Day

It is written about Microsoft SQL Server, but most of it works in other database management system too.


How to find physical_device_name on backup by extending great query from Ozar? Found this on an older blog post at BrentOzar.com:

SELECT  d.name, MAX(b.backup_finish_date) AS last_backup_finish_date
FROM    master.sys.databases d WITH (NOLOCK)
LEFT OUTER JOIN msdb.dbo.backupset b WITH (NOLOCK) ON d.name = b.database_name AND b.type = 'D'
WHERE d.name <> 'tempdb'
GROUP BY d.name
ORDER BY 2

Pretty great query!

Question: is there is an elegant way to join on msdb.dbo.backupmediafamily and return the physical_device_name for those databases that DO have recent backups? Result set should still return the ones without backups.

My attempts thus far stink :-)

Link to blog post:
https://www.brentozar.com/archive/2012/03/how-talk-your-boss-about-world-backup-day/

Thanks!

WITH backups (DatabaseName, LastBackupFinishDate)
AS
(
    SELECT d.name AS DatabaseName, MAX(b.backup_finish_date) AS LastBackupFinishDate
    FROM master.sys.databases d WITH (NOLOCK)
    LEFT OUTER JOIN msdb.dbo.backupset b WITH (NOLOCK)
        ON d.name = b.database_name AND b.type = 'D'
    WHERE d.name <> 'tempdb'
    GROUP BY d.name
)
SELECT
    b.DatabaseName,
    b.LastBackupFinishDate,
    bmf.physical_device_name
FROM backups b
LEFT JOIN msdb.dbo.backupset bs ON b.DatabaseName = bs.database_name
LEFT JOIN msdb.dbo.backupmediaset bms ON bs.media_set_id = bms.media_set_id
LEFT JOIN msdb.dbo.backupmediafamily bmf ON bms.media_set_id = bmf.media_set_id


Mysterious postgres process pegging CPU at 100%; no running queries I have a recurring issue where a postgres process is pegging my cpu at > 100%. It's a strange one, you can see below the command is ./Ac2p20853 (surrounded by asterisks, added by me).

Running SELECT pid, datname, usename, query FROM pg_stat_activity shows nothing too interesting (to me at least). Simply a few inserts into pg_proc.

How would you go about debugging what is the issue here?

See below for output from ps, grepping postgres.

Thank you.

deploy@localhost:~$ ps auxww | grep ^postgres
postgres   590  0.0  0.3 309312 13768 ?        Ss   20:29   0:00 postgres: deploy template1 5.5.5.5(53394) idle
postgres  2264  0.0  0.3 309312 13748 ?        Ss   20:20   0:00 postgres: deploy template1 5.5.5.5(37818) idle
postgres  3079  0.0  0.3 309312 13776 ?        Ss   20:12   0:00 postgres: deploy template1 5.5.5.5(60006) idle
postgres  3809  0.8  0.0   4504  1656 ?        S    18:11   1:19 /bin/sh ./systemd
**postgres  3862  167  0.1 252512  5464 ?        Ssl  18:11 265:06 ./Ac2p20853**
postgres  5287  0.0  0.6 308388 24560 ?        S    Aug09   1:22 /usr/lib/postgresql/9.6/bin/postgres -D /var/lib/postgresql/9.6/main -c config_file=/etc/postgresql/9.6/main/postgresql.conf
postgres  5289  0.0  2.1 308516 87096 ?        Ss   Aug09   0:01 postgres: checkpointer process
postgres  5290  0.0  0.5 308388 22832 ?        Ss   Aug09   0:04 postgres: writer process
postgres  5291  0.0  0.2 308388  8676 ?        Ss   Aug09   0:03 postgres: wal writer process
postgres  5292  0.0  0.2 309196 11972 ?        Ss   Aug09   1:03 postgres: autovacuum launcher process
postgres  5293  0.0  0.1 163724  5004 ?        Ss   Aug09   2:34 postgres: stats collector process
postgres  6065  0.0  0.3 309312 13824 ?        Ss   20:04   0:00 postgres: deploy template1 5.5.5.5(51616) idle
postgres  7384  0.0  0.3 309312 13832 ?        Ss   20:40   0:00 postgres: deploy template1 5.5.5.5(55916) idle
postgres 11467  0.1  4.0 328788 161612 ?       Ss   Aug09  10:50 postgres: deploy db_production [local] idle
postgres 11638  0.0  4.3 339336 174368 ?       Ss   Aug09   4:52 postgres: deploy db_production [local] idle
postgres 11812  0.0  0.5 316092 21496 ?        Ss   Aug09   0:00 postgres: deploy postgres [local] idle
postgres 12707  0.0  0.4 315660 16820 ?        Ss   Aug09   0:00 postgres: deploy db_production [local] idle
postgres 17511  0.0  0.3 309312 13716 ?        Ss   20:33   0:00 postgres: deploy template1 5.5.5.5(33460) idle
postgres 17968  0.0  0.3 309312 13804 ?        Ss   20:44   0:00 postgres: deploy template1 5.5.5.5(34548) idle
postgres 18974  0.0  0.3 309312 13768 ?        Ss   20:16   0:00 postgres: deploy template1 5.5.5.5(52290) idle
postgres 19017  0.0  0.3 309312 13752 ?        Ss   20:24   0:00 postgres: deploy template1 5.5.5.5(37348) idle
postgres 21120  0.0  0.3 309312 13788 ?        Ss   20:08   0:00 postgres: deploy template1 5.5.5.5(33084) idle
postgres 24189  0.0  0.3 309312 13744 ?        Ss   20:36   0:00 postgres: deploy template1 5.5.5.5(35660) idle
postgres 31498  0.0  0.3 309312 13804 ?        Ss   20:48   0:00 postgres: deploy template1 5.5.5.5(39618) idle

Most likely you've been hacked and are now mining cryptocurrency for the hacker.

Chances are, you have a weak password for your postgres superuser account (probably named "postgres") and you let anyone in the world connect and attempt to log in, which they do until they guess your weak password.  Or maybe you have no password at all.


MySQL joining two tables and counting based on conditions table 1 > cats

cat1
cat2

table 2 > sites

site1 > lang1 > cat2
site2 > lang2 > cat1
site3 > lang3 > cat1
site4 > lang1 > cat1
site5 > lang2 > cat1
site6 > lang3 > cat2

the query should do this :

get cats and for every cat go to sites and count sites that belongs to that cat with lang1 , lang2 , lang3

I want output to be like :

cat1 > lang1 : 41 , lang2 : 11 , lang3 : 24

cat2 > lang1 : 17 , lang2 : 06 , lang3 : 74

thanks in advance

I just found the answer and wanted to share it:

SELECT
  dir_cats.*,
  SUM(CASE WHEN dir_sites.site_lang = "ar" THEN 1 ELSE 0 END) AS ar_count,
  SUM(CASE WHEN dir_sites.site_lang = "en" THEN 1 ELSE 0 END) AS en_count,
  SUM(CASE WHEN dir_sites.site_lang = "fr" THEN 1 ELSE 0 END) AS fr_count
FROM (dir_cats)
  LEFT JOIN dir_sites
    ON site_cat_id = cat_id
GROUP BY dir_sites.site_cat_id


Move data from one table to other in batch's I have a table 18 Million records I need to add append it to another table (with the same structure) I tried the import-export wizard, the problem is that the log gets very very big and eventually it took down the full server, I had to kill the process in middle.

I think if I'll do it in batches it'll be faster, but I don't know how to do it.

(I don't need the old table, a record that has been copied could be deleted)

EDIT: bulk insert block all operations, is there a way to prevent him from blocking everyone here?

while (1=1)
begin
    delete top(10000) from oldtable
       output deleted.columnA, deleted.columnB, deleted.columnC
       into newtable (columnA, columnB, columnC)
    if @@ROW_COUNT = 0
       break;
end


ALTER TYPE Not Available So How To Alter a Type? I know there are other questions out there regarding this issue, but this one is really simple so I'm not looking for a complicated answer!

So I have this script:

IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'IDListTableType' AND ss.name = N'dbo')
DROP TYPE [dbo].[IDListTableType]
GO

IF NOT EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'IDListTableType' AND ss.name = N'dbo')
CREATE TYPE [dbo].[IDListTableType] AS TABLE(
    [ID] [int] NOT NULL,
    UNIQUE NONCLUSTERED
(
    [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

I get an error when I run it because IDListTableType is referenced elsewhere in the database. So I need a way to either alter the table type in place, or to find and drop and recreate all other database objects that are referencing IDListTableType. Is there a simple way to do this?

Simplest would be to create a new type with the updated definition, then you can change the affected objects one at a time (or all at once) to reference the new name. You can’t pull the tablecloth.

I wrote an article about this here:

How to Alter User Defined Table Type in SQL Server

The fundamental process is:

create a new type
determine which objects reference the old type
change those objects manually to point at the new type (or at least those objects that need to use the new type)
optionally, drop the old type

The tricky part is #2, people will hunt and peck manually, or try to parse OBJECT_DEFINITION or sys.sql_modules, but the following is a bit more reliable:

SELECT s.name, o.name, def = OBJECT_DEFINITION(d.referencing_id)
  FROM sys.sql_expression_dependencies AS d
  INNER JOIN sys.objects AS o
     ON d.referencing_id = o.[object_id]
  INNER JOIN sys.schemas AS s
     ON o.[schema_id] = s.[schema_id]
  WHERE d.referenced_database_name IS NULL
    AND d.referenced_schema_name = N'dbo'
    AND d.referenced_entity_name = N'MyType';


Latest version to support MySQL 5.1.69 ib* files Would like to recover InnoDB data from a crashed server. The last backup indicated it was running MySQL server 5.1.69. When doing a recovery, the log files show that the current MySQL won't support ib* files from this far back.
I tried installing 5.1.69 from archives and compiling from sources, but no-go, rabbit hole of problems.
Does anyone know the latest version of MySQL to support 5.1.69 ib* files? Will try that as a last resort. If it doesn't work, will go with the latest backup and some auxiliary data to try to reconstruct as much as possible.

You should be able to load it with 5.5. As a general rule, next version up should always work. You should be able to run mysqldump from there, or run mysql_upgrade to keep it as is for 5.5. Then if you want to upgrade from there, you have to go up to 5.6 and run mysql_upgrade again. Repeat for 5.7 and 8.0 depending on how far you need to upgrade.


sp_Blitz - undocumented DBCC events related to the replication marked as a finding Recently I found in the sp_Blitz output new finding 'Overall Events' in 'DBCC Events' findings group:

15278 DBCC events have taken place between Aug  8 2017 12:15AM and Aug  8 2017  8:17AM. This does not include CHECKDB and other usually benign DBCC events.

When I digged into this and checked the default trace, I found that every minute it records entries like this:

dbcc incrementinstance ("SQL Replication Distribution", "Dist:Delivered Cmds/sec", @agent_name, @new_delivered_commands)
dbcc setinstance ("SQL Replication Distribution", "Dist:Delivery Latency", @agent_name, @delivery_latency)
dbcc addinstance ("SQL Replication Logreader", @agent_name)

It's plainly visible this is replication related, but since these commands are undocumented I am not sure whether they should come up in the trace. However, basing purely on the output it just measures the amount of commands per seconds plus the latency. I think this is nothing harmful.

So if you could possibly check your servers where you run the replication and confirm if you have these entries and the frequency is more-less the same?

The separate question is if it's normal to see such commands (and I guess it is) in the default trace, perhaps it shouldn't be considered as a finding by sp_Blitz and it should be grouped with other harmless DBCC commands already excluded in sp_Blitz.

We use Sentry One to monitor SQL Server, but I don't think it's monitoring replication. I think it might be something internal. There are some default alerts defined when you deploy replication (SQL Agent -> Alerts, for example: Replication Warning: Transactional replication latency (Threshold: latency)) It is basing on SQL Server error log, but the entry in the error log has to be initially triggered by something.

I have opened an issue on Github to cover this.

Brent has just replied on Github it is going to be excluded from the alerts.


How to select from table by comparing multiple rows with same key I have below table KEY_STATUS.

|| KEY || STATUS   ||
| 0001 | FAILED    |
| 0001 | COMPLETED |
| 0002 | FAILED    |
| 0003 | COMPLETED |
| 0002 | FAILED    |
| 0004 | STARTED    |

I would like to select keys with only FAILED state. That means I will have to exclude 0001 as it has both FAILED and COMPLETED, also exclude 0003 as it has COMPLETED state.
Output should be

|| KEY || STATUS ||
| 0002 | FAILED  |

What approach should I take in Oracle to achieve above?

Assuming only two possible values for status , eliminate all keys that has more than one row and filter for failed status

SELECT *
FROM key_status
WHERE status='FAILED'
  AND key <>
    (SELECT key
     FROM key_status
     --WHERE status IN('COMPLETED','FAILED')
     GROUP BY key
     HAVING COUNT(key) >1)


How to start Sybase server I have installed Sybase(Developer's Edition for ASE 16.0) on a linux machine. I have executed the setup.bin file, and installed it using GUI. After this, I tried connecting to the localhost server, by running the dbisql executable in the DBISQL-16_0/bin folder of the base-installation-folder but couldn't connect to the 'localhost:5000' server, using the username/password of the server. I am not sure if the server is tarted. Do I need to run anything to start the server, or perform any other actions before trying to connect using this client?

Any help regarding this is highly appreciated.

Following information was found on sybasewiki Hope this helps !

How to start the sybase server?

After log in your Linux/Unix machine.

Step 1:  Find out current location or present working directory.
Step 2:  Locate environmental file SYBASE.sh or SYBASE.csh profile which is ,by default, located in $SYBASE.
Step 3:  Run environmental file.
Step 4:  Verify SYBASE.sh or SYBASE.csh run properly.
Step 5:  Find out RUN_Serverfile
Step 6:  Start server with startserver.
Step 7:  Start backup server with startserver.

Step 1: pwd   -- know your present working directory.
Step 2: cd  <directory_name_where_SYBASE.sh_present> --find out SYBASE.sh profile which can you find SYBASE ASE directory.
Step 3: . SYBASE.sh   -- run it and include it.
Step 4: echo $SYBABE  -- shows path where $SYBASE created which verify that SYBASE.sh run prperly.
Step 5: cd $SYBASE/SYBASE_ASE/install
        ls -ltr  -- find out RUN_server_file name.
Step 6: startserver -f RUN_servername

Starting Back Up Server :
 Step 7:startserver -f RUN_SYB_BACKUP

Note: depends upon installation where SYBASE ASE Server installed, so relative path of Run_serverfile can be differ.


How to rewrite a query with MAX in WHERE clause I have a query with a max in the where clause, which is slow.

select count(*)
from   TableName tbl1
where  tbl1.id = (
                select max(tbl2.id)
                from TableName tbl2
                where tbl2.companyId = tbl1.companyId)
and    tbl1.active = 2

Please note that both tables are the same.

I rewrote it as:

select count(*)
from   TableName tbl1
inner join (
                select max(id) as id, companyId
                from TableName tbl2
                where tbl2.active= 2
                group by companyId
        ) tbl2
        on  tbl2.companyId = tbl1.companyId
        and tbl2.id=tbl1.id

but number of records is different.

Can anyone say how can I rewrite it for better performance?

Summary
Add the index:
CREATE INDEX index_name
ON TableName
    (companyid DESC, id DESC)
INCLUDE
    (active);

Try this rewrite:
SELECT COUNT_BIG(*)
FROM
(
    SELECT TOP (9223372036854775807)
        TN1.active
    FROM TableName AS TN1
    WHERE
        TN1.id =
        (
            SELECT MAX(TN2.id)
            FROM TableName AS TN2
            WHERE
                TN2.companyid = TN1.companyid
        )
) AS SQ1
WHERE
    SQ1.active = 2;

Detail
The original query specification appears to be:

How many rows are there in the table where active = 2; and the id column equals the highest id value across all rows within the same companyid (without the active = 2 restriction, for clarity).

There is a way to get these results with a single scan of a suitable index.
For example, given the table:
CREATE TABLE #TableName
(
    companyid integer NOT NULL,
    id integer NOT NULL,
    active integer NOT NULL
);

A few rows of sample data:
INSERT #TableName
    (companyid, id, active)
VALUES
    (1, 1, 2),
    (1, 2, 2),
    (1, 3, 0),
    (2, 2, 2),
    (2, 3, 2),
    (2, 3, 0);

With the index:
CREATE INDEX index_name
ON #TableName
    (companyid DESC, id DESC)
INCLUDE
    (active);

A query (based on the original) to find rows without the predicate (active = 2) is:
SELECT
    TN1.active
FROM #TableName AS TN1
WHERE
    TN1.id =
    (
        SELECT MAX(TN2.id)
        FROM #TableName AS TN2
        WHERE
            TN2.companyid = TN1.companyid
    );

The execution plan for that query features a single scan of the index, despite the query referencing the same table twice:

The basic idea is to scan the index in key order, returning the row(s) with the highest id within each group of companyid values.
Unfortunately, we cannot simply add the predicate active = 2 to that query without breaking the optimizer transformation that gives us the single scan. The problem (if you're interested) is that the new predicate gets pushed down the plan tree, resulting in a shape that no longer matches the 'group by top' transform.
One workaround adds a meaningless TOP specification around the part of the query we want transformed, preventing the new predicate being pushed past it:
SELECT COUNT_BIG(*)
FROM
(
    SELECT TOP (9223372036854775807)
        TN1.active
    FROM #TableName AS TN1
    WHERE
        TN1.id =
        (
            SELECT MAX(TN2.id)
            FROM #TableName AS TN2
            WHERE
                TN2.companyid = TN1.companyid
        )
) AS SQ1
WHERE
    SQ1.active = 2;

The execution plan is similar to before, with an extra Top (the meaningless, bigint.max one we added), a Filter for the active = 2 predicate, and a final Stream Aggregate to count the rows:

I cannot promise you will get this plan shape with the real data, or that it will be faster. It all rather depends on the number and distribution of the rows.
The new Top also has the effect of disfavouring a parallel plan (the group by top is parallel-compatible, but a global top is not; parallelism would have to stop and restart around it).
Nevertheless, until a better option comes along, or more detail is provided, it is something you could try.


Multiple rows into json Looks my question is unusual because I didn't find the answer at all.
Let's imagine, I have table A with columns: language and uri.

language | uri
---------|-----------------
ru       | some-uri
en       | some-another-uri
...

My question is: How can I return JSON object instead of many rows.
E.g.:

{
  "ru": "some-uri",
  "en": "some-another-uri",
  ...
}

So, I found out the answer far far away in the documentation.

SELECT json_object(array_agg(language), array_agg(uri)) FROM A will give you the expected result.


How to migrate postgreSQL on EC2 instance to AWS RDS? I have a large PostgreSQL DB (around 100GB) which is on EC2. I want to migrate it to RDS.

How can I achieve this with minimal downtime?

You can't do it with minimal downtime, or not easily anyway.

RDS doesn't support importing base backups and doing streaming replication to/from outside servers.

See Getting WAL from AWS RDS and Moving a standalone DB to Amazon RDS.

You'll have to dump and reload.

If you're keen, you could instead set up Londiste or a similar tool to stream changes from the standalone server into RDS, with the Londiste pgq daemon and the subscriber daemon both running on AWS EC2. The subscriber would connect remotely to RDS over the PostgreSQL protocol. This lets you use trigger based replication to keep the DBs in sync, giving you a shorter outage window. You still have to disable writes on the old DB and make sure Londiste is fully caught up before switching over, though.


May I Convert Non RAC db to an RAC DB and add on a Node? Currently my work env as 1 node none RAC database 11gR2, we are getting another machine (Linux), so I need to add on this node to our currently env and becoming a RAC db. What is the best way/route to do it? Advanced thanks!

See the Oracle Real Application Clusters Installation Guide 11g Release 2 for Linux and UNIX.  It has a section on "Converting to Oracle RAC from Single-Instance Oracle Databases" describing several different ways this can be done.


Slow SSRS Report in production I have an SSRS report which gets its data by firing a series of stored procedures.

Now the report is timing out big time when run in production, yet when I pull down the prod database and restore to development the report runs fine.

I was thinking to set up a sql server profiler trace in production and hopefully that will tell me something... eg high Disk I/O at the time it's being run.

What else should I be doing? Something with perfmon?

You've either got a slow running query or blocking/deadlocking going on.  To see blocking, you could run this query:

CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),
      Login  VARCHAR(255),HostName  VARCHAR(255),
      BlkBy  VARCHAR(255),DBName  VARCHAR(255),
      Command VARCHAR(255),CPUTime INT,
      DiskIO INT,LastBatch VARCHAR(255),
      ProgramName VARCHAR(255),SPID2 INT,
      REQUESTID INT)
INSERT INTO #sp_who2 EXEC sp_who2
SELECT      *
FROM        #sp_who2
-- Add any filtering of the results here :
WHERE       DBName <> 'master'
-- Add any sorting of the results here :
ORDER BY    DBName ASC

DROP TABLE #sp_who2

Look for anything in the BlkBy column and you can see what session is blocking your queries.

You didn't mention a deadlock error, which you should see in SSRS logs.  I'd take a close look at those to see the exact error and proceed with that knowledge.  It's down in the log dir of the SSRS install.

SSRS will have timeouts set for it, and you may be hitting those times, but your SSRS log should tell you if it's a application timeout or a deadlock.


SQL Get Users By One Group But Exclude Others I'm trying to write a query that get's all users that belong to Group A, but excludes users that belong Groups B, C & D.

Users can belong to multiple groups.

I have the following query but it's not working:

SELECT id
FROM users
LEFT JOIN groups USING (user_id)
WHERE groups.name = 'A'
AND groups.name NOT IN ('B', 'C', 'D')

This just selects all users from group A, how do I exclude anyone who also belongs to groups B, C & D?

SELECT
    id
FROM
    users as u
    LEFT JOIN groups
        ON groups.user_id = u.id
WHERE
    groups.name = 'A'
    AND NOT EXISTS (
        SELECT
              1
        FROM
            groups
        WHERE
            user_id = u.id
            and name IN ('B', 'C', 'D')
    )


MySQL Indexing and performance when size of index is greater than size of data in table I wrote this query for seeing if the size of the indexes in certain tables were larger than the actual amount of data in the table, using MySQL 5.6 and InnoDB as the engine.

SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2)
'Size in MB',
IF(index_length > data_length, 1, 0) as poorIndexing
FROM information_schema.TABLES

The results are that some of the tables have much larger index_lengths than their actual data table.  I was under the impression that this meant that MySQL would skip over the indexes and simply do a full table scan because there is less data to parse in the entire table than the index.

However, is this true if an individual index is smaller than the aggregate?  I.e. if I'm doing something like "SELECT * FROM user WHERE id = 244", and the id is indexed, but so is the address, phone, username, email, etc. does InnoDB just use the id or does MySQL see the size of the entire operation and simply skip over it.  Or is it only a problem if the query is :

SELECT username FROM users WHERE username = 'johnrambothe14th';

And the total amount of indexes for username is greater than the total amount of data?

As you mentioned of 5.6 i guess tables are Innodb... Where we have PK stored as clustered index. The rows are stored in an index or say leaf node of the PK  index is data itself.

Secondary further contains the PK key columns as well. So a long PK key => longer Secondary index (WRT size).

About large indexes, you might need to look out for duplicate indexes.
Also one thing to make sure is having large enough innodb-buffer-pool to make sure indexes are in memory for quicker access.

Refer:

https://dev.mysql.com/doc/refman/5.6/en/how-to-avoid-table-scan.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-index-types.html
https://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html


What's the best way to store and search through many product specifications Let's say I have a lot of products and all of those products have a lot of specifications. These specifications can change at any time (e.g. "colour" could suddenly be available for new products) and some products do not have certain specifications.

What would be the best way to store this?
What would be the best way to search through this?

In the end, there will be tens of thousands of products each with dozens of specifications. Adding a column for each specifications feels wrong, so I'm assuming having a separate table for the specifications and then mapping them through a third table is the best way to go.

How would I be able to search through these specifications easily while maintaining performance when the products_specs table grows to millions of records?

Here's the schema and query I came up with: http://sqlfiddle.com/#!9/53f49/3

One way is to design the specifications in a supertable/subtable form. Here is one such design: Fiddle. I'm sure it can be improved on, but the key point is that specification ID values are unique among all specifications, not just within each type of specification. So there are no collisions between Colour and Weight, for example. However, a spec defined as colour cannot be entered into any other spec subtable except Colors (or Colours -- try as I might, I'm afraid I mixed the spelling in at least one location). The super table (Specs) makes it easy to query individual specs.


MYSQL - Unable to change 'old_passwords' variable I have full access to a 'MYSQL 5.0.27-community-nt-log' database one of our new updates for a core app requires PHP 5.4, after installing and configuring I'm stuck at the error regarding old vs. new passwords.

I've followed every thread I can find and I'm pretty sure I understand the process:

Disable 'old_passwords'
Reset the password to the new 41 encryption

But I cannot seem to get 'old_passwords' to be set to 'OFF'. Logging in as root I've tried the following:

I've removed it from the my.cnf file (I've also tried setting it to '0', 'False' etc)
Manually setting it in the query browser both globally and session
Turning the option on/off in MYSQL administrator

and restarted the service but 'show variables like 'old_passwords' always reports as 'ON'.

Changing the password still remains at a 16 length.

I'm sure once I'm able to turn this variable off I should be fine, Any advice?

I have 3 additional suggestions (choose one):

Downgrade the PHP drivers (which understand the 16 character encrypted password)
Upgrade to the latest MySQL (where you can set old_password=1 from inception)
Change all the passwords in mysql.user to 41 character encrypted password format

Whatever you do, do not use the MD5 function to make new passwords. Use the PASSWORD function. It is very different from MD5:

The password function PASSWORD function is the equivalent of

SET @my_new_password = 'WhateverIWantAsThePassword';
SELECT CONCAT('*',UPPER(SHA1(UNHEX(SHA1(@my_new_password))))) MySQLPWDHASH;

I learned that like 2 years ago from this PalominoDB Blog Post.

You can QA the PASSWORD function with OLD_PASSWORD function. Compare the output of SELECT PASSWORD(@my_new_password); WITH SELECT OLD_PASSWORD(@my_new_password);.


What is the MAXDOP value for the server which doesn't have NUMA Im using a SQL Server with the below configurations.

4 Core.
8 Logical processors.
No NUMA.
NO hyperthreading.

I read this Doc:

Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server

Since I don't have NUMA and HyperThread.

So what could be my best MAXDOP setting?

For SQL Server 2005 and later versions

Server with single NUMA node    Less than 8 logical processors  Keep MAXDOP at or below # of logical processors

Server with single NUMA node    Greater than 8 logical processors   Keep MAXDOP at 8

Server with multiple NUMA nodes Less than 8 logical processors per NUMA node Keep MAXDOP at or below # of logical processors per NUMA node

Server with multiple NUMA nodes Greater than 8 logical processors per NUMA node Keep MAXDOP at 8

See : Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server


Database Schema for an address book with different mailing lists I'm helping a friend out with taking an old excel, single-page database which contains around 12 different columns that store information about:

person first+last name
email(s)
current address
phone(s)
sorting code
receives mailing list
other information relating to their church and first cursillo attended

I started to normalize this into different tables.

person | address | email | phone | mailing | church | cursillo

I'm sure there's probably a few more tables I can get, the current database has a lot of missing data and some fields contain multiple email accounts or multiple phone numbers.

My friend doesn't have the time to learn about different databases or to learn programming so I want to make everything as simple as possible for him. For the new website, we're looking at setting it up in WordPress; the previous one was built by him in iWeb.

Ideally we want to be able to easily extract different mailing lists like:

Send e-mail to everyone who attended cursillo 50
  OR
Send e-mail to everyone who wants the monthly mail (mailing is just yes/no field option linked to person id)
  OR
Send e-mail to the new cursillo team for the upcoming weekend
  OR
Send e-mail to people who attend a specific church

etc.

However after splitting the tables up, I find that I'm not sure how easy it will be to maintain. What if my friend wants to change John Smiths' phone number, he'll have to go into person table and find the id for John Smith then go into the phone table to change the number.

What should I do to minimize the effort required to maintain such a database? as in, should I really normalize the database to 3NF/BCNF?

I think you should definitely keep the data normalized as you have it now.  I would go a step further and instead of updating John Smith, add a new record for his phone information and mark the old one as inactive.  Then  you have history as well.

It seems complicated but it's really not, and the DB is optimized for this sort of query.

For your update example, it would be as simple as:

UPDATE dbo.phone
Set PhoneNum = '111-555-1234'
WHERE UserId IN (SELECT UserID FROM dbo.Users WHERE Name='John Smith')


Cannot Attach Database to SQL Server Management Studio 2008 R2 I installed SQL Server 2008 R2 just now and wanted to attach my previous databases. Attaching a Database gives an error.

  Attached is the image of the error.

Any Suggestions and help will be appreciated. :)
Regards.

Something seems not quite right with your question.

SQL 2008 is version 655
SQL 2008R2 is version 661

I would expect that you would only see that error if you are attempting to attach a 2008R2 DB to 2008, not vice versa.

As soon as you attach a DB to 2008 R2, the version is changed to 661 and can no longer be attached to a 2008 server.


Database backup size I created SQL server scheduled backup as follow :

Full: 2 times a week
Differential: 6 hours a day
Log: 3 hours

The problem I am having is that after one month I end up with a backup file of size  30GB and that is too heavy to restore, knowing that one full backup is only 1 GB max.

Can I, for example, each week save the .bak file (created as described above) somewhere and delete it from the SQLSERVER backup folder (delete it just after the last log backup that is before the next full backup occurs), so that the next week a new Full backup will be created then Differential and Log.

Or you may have a better strategy, then please elaborate.

Sql Server doesn't care 'where' your backups are ultimately stored.  Having said that, I can assure you that Sql Server 'knows' where the backups were 'created' (physical location) and stores that information in the MSDB database. Sql Server would try to use that information to generate a restore via SSMS GUI and the files wouldn't be there. Moving backup files around will also complicate an attempt to automate a restore plan by utilizing the information in the MSDB database tables because the files are in a different place than where they were originally created.  And, I think you definitely want an automated restore plan, especially with trying to restore a bunch of logs.

In my opinion, the 'better' strategy is to allocate a separate disk drive or share with enough space to hold your intended backups.  I personally don't like maintenance plans for backups because they don't give you enough options.  A better choice is to use Ola Hallengren's maintenance scripts (backup, being one) - https://ola.hallengren.com/sql-server-backup.html