Query Time is doubled because of a column in select I have a Stored Procedure which is calculating some data and inserting it into a Temp Table.
Then I am selecting the Data from the Temp Table.
Select
    AssetId = iAssetId,
    .....
    SpeedKM = fSpeed,
    [Address] = sState +', '+ sDistrict +', ' +sPoi +', ' + sRoad +', '+sPoi,
    MapUrl = @sUrlHeader+'/Report/ReportOnMap/?id='
        +CONVERT(VARCHAR(10), @iCompanyId)+'&ReportName=OverspeedReport'
        +'&AssetId='+ CONVERT(VARCHAR(10), iAssetId)
        +'&MaxSpeed='+ CONVERT(VARCHAR(10), fSpeed)
        +'&OverspeedingDate=' + FORMAT(dtutcDateTime, 'dd-MMM-yyyy HH:mm:ss')
        +'&VehicleMonitoringLogId='+ CONVERT(VARCHAR, ol.iVehicleMonitoringId),
    [Locate] = 'Locate'

FROM #overspeedLogs ol
    ORDER BY ol.iAssetId, ol.dtUtcDateTime

Now this Query takes around 1:45 Minutes to execute.
But when I remove the column
MapUrl = @sUrlHeader+'/Report/ReportOnMap/?id='
            +CONVERT(VARCHAR(10), @iCompanyId)+'&ReportName=OverspeedReport'
            +'&AssetId='+ CONVERT(VARCHAR(10), iAssetId)
            +'&MaxSpeed='+ CONVERT(VARCHAR(10), fSpeed)
            +'&OverspeedingDate=' + FORMAT(dtutcDateTime, 'dd-MMM-yyyy HH:mm:ss')
            +'&VehicleMonitoringLogId='+ CONVERT(VARCHAR, ol.iVehicleMonitoringId),

The stored procedure only takes 35 seconds,
There are 4 Converts and 1 Date Format function, which maybe causing this issue - but without changing the logic on the Front End (which is an RDLC report) is there a way to increase the performance of this?
Changing Format to Convert still has similar result - still takes double or more the time. The variable @sUrlHeader is VARCHAR(500).
Execution plans
Without MapUrl Column - 121919 Rows in 2 minutes https://gist.github.com/mdawood1991/580fadf9031824aa22df

With MapUrl Column - 121919 Rows in 7 Minutes (So more than double for more rows) https://gist.github.com/mdawood1991/33817f95aad40d42631b

The time difference is probably due to:

The larger amount of data that needs to be returned to the client; and
The FORMAT function is relatively slow

You may be able to avoid using FORMAT by using a T-SQL expression instead, for example:

REPLACE(CONVERT(char(11), dtutcDateTime, 106), SPACE(1), '-') +
SPACE(1) +
CONVERT(char(8), @dt, 108);

-- Returns 25-Mar-2016 23:45:19

See:

FORMAT() is nice and all, but… by Aaron Bertrand
CAST and CONVERT (Transact-SQL) in the product documentation

You should also avoid scalar T-SQL functions in general, for performance reasons. Both plans use a UDF called xPT_ConvertTimeToDDHHMMSS. Scalar T-SQL functions are executed per row, with an overhead similar to that of running a complete query (each time). With 121,861 function calls, that overhead will be adding up. Use an in-line function or T-SQL intrinsics instead.

Also check that the variable @sUrlHeader is not a LOB type (e.g. varchar(max)) if it does not need to be. Using varchar(8000) or below may be significantly faster.

You can check the raw performance of the query (discounting the effect of a client that is slow to accept the results) by running it in SSMS with the Discard Results option set:

...or by selecting the query into a temporary table:

SELECT
  OverspeedReportId = ol.iVehicleMonitoringId,
  AssetId = iAssetId,
  Registration = sReference,
  CategoryId = iCategoryId,
  CategoryName = sCategoryName,
  SiteId = iSiteId,
  SiteName = sSiteName,
  OverspeedDate = FORMAT(dtutcDateTime, 'dd-MMM-yyyy HH:mm:ss'),
  DistanceTraveledSinceLastOverSpeed = DistanceCoveredKM,
  TimeDifferenceDDDHHMMSS = SUBSTRING(dbo.xPT_ConvertTimeToDDHHMMSS(DiffSeconds,'s'),1,12),
  SpeedKM = fSpeed,
  [Address] = sState +', '+ sDistrict +', ' +sPoi +', ' + sRoad +', '+sPoi,
  MapUrl = @sUrlHeader+'/Report/ReportOnMap/?id='
      +CONVERT(VARCHAR(10), @iCompanyId)+'&ReportName=OverspeedReport'
      +'&AssetId='+ CONVERT(VARCHAR(10), iAssetId)
      +'&MaxSpeed='+ CONVERT(VARCHAR(10), fSpeed)
      +'&OverspeedingDate=' + FORMAT(dtutcDateTime, 'dd-MMM-yyyy HH:mm:ss')
      +'&VehicleMonitoringLogId='+ CONVERT(VARCHAR, ol.iVehicleMonitoringId),
  [Locate] = 'Locate'
INTO #Results -- NEW!
FROM #overspeedLogs ol
LEFT JOIN VehicleGISAddressLog gis
    ON gis.iVehicleMonitoringId = ol.iVehicleMonitoringId
ORDER BY
    ol.iAssetId,
    ol.dtUtcDateTime;

For the most comprehensive collection of performance data, run the queries directly from SQL Sentry Plan Explorer. Click the "Post to SQLPerformance.com" toolbar button to upload the complete session for expert analysis on that site.

If you are unable to do that, consider adding STATISTICS IO output to your question, at least.


INSERT INTO inserting multiple times I have a table that contains all information about user called 'DataSource'. I have columns called 'First Name', 'Surname', and 'Address' (there's more but as an example). I have two tables called 'Address' and 'User'. What I'd like to do is take the Address column for a user in DataSource and add it to the Address table. I would then like to take the First Name, Surname, and AddressID (created upon Address insertion) and add it to the User table. (Basically, split the data from DataSource into two tables and have the AddressID link between the two of them via the auto-generated AddressID from the Address table).

I've got the code below but it's not behaving as expected. The User table is being filled in a loop so every user is inserted the number of rows (e.g. if there are two people in the DataSource table, each user is inserted twice and given every address ID each time)

DECLARE @ids TABLE (address_id INT);

INSERT INTO dbo.Address(address)
OUTPUT INSERTED.address_id INTO @ids
SELECT [Address]
FROM dbo.[DataSource]

SET IDENTITY_INSERT dbo.[Users] OFF

INSERT INTO dbo.[Users]
        (
          first_name,
          surname ,
          address_id
        )

SELECT  first_name, surname,i.address_id,
FROM dbo.[DataSource], @ids i

How can I essentially do this sequentially so that the correct address is assigned once to the user? This is not a time sensitive operation so performance is not important but the integrity of the data is crucial.

Thanks

You are doing a cartesian product here:

...
FROM dbo.[DataSource], @ids i

you need to know which address_id corresponds to which user, and for that you must have a clause. Something like this will do it:

INSERT INTO dbo.[Users]
        (
          first_name,
          surname ,
          address_id
        )

SELECT  first_name, surname, Address.address_id,
FROM dbo.[DataSource]
INNER JOIN dbo.Address ON Address.address = DataSource.Address

A further consideration; this will work well as long as you don't have two users with the same address, or if it is desirable that two users with the same address have the same address_id.


According to the SQL92 spec, can I store the value 1 in a field where the precision = scale? Is it SQL92 compliant to store the number 1 in a field that is defined as NUMERIC 3,3?

What about DECIMAL 3,3?

Does this mean that the precision is a floating decimal place or static that must have 3 decimal places even if they are all zero?

The precision is the total number of decimal digits that can be stored on both sides of the decimal place. The scale is the total number of decimal digits to the right of the decimal point which can be less than or equal to the precision. Therefore the maximum value that can be stored in a DECIMAL(3,3) or a NUMERIC(3,3) is 0.999. The easiest way to find this out is to actually try it out.

Using mysql 5.5.20 the below returns 0.999:

SET @val = (SELECT CAST(1 AS DECIMAL(3,3)));
SELECT @val;

Whereas the following returns 1:

SET @val = (SELECT CAST(1 AS DECIMAL(3,0)));
SELECT @val;

I hope this helps.

EDIT: I forgot to mention that on most platforms attempting to perform the above will result in some type of Arithmetic Overflow Exception. For instance the below statement running on Microsoft SQL Server 2008 will throw an error:

DECLARE @val DECIMAL(3,3);
SET @val = 1;
SELECT @val;


Using FLOATs with RAISERROR I am using RAISERROR() to provide some basic Unit Testing functionality (as here), but I am frustrated by the inability to use FLOATs in the error message. I know I can cast the float to a string, but I am using RAISERROR in every single unit test, I don't want to add another line of code for every test. (My unit tests are already wordy enough!) Is there a way to perform an inline cast/convert within the RAISERROR parameter list? Or is there another way around this deficiency?

Update:
So ultimately what I wish I could do is this:

RAISERROR('Unit Test FAILED! %f', 11, 0, @floatParm)

Unfortunately, RAISERROR doesn't handle %f or floats in general. So I have to do this instead:

DECLARE @str VARCHAR(40) = CAST(@floatParm AS VARCHAR(40))
RAISERROR('Unit Test FAILED! %s', 11, 0, @str)

...which just looks like a mess when it's scattered through dozens of Unit Tests. So I'd like to boil it down to something like this:

RAISERROR('Unit Test FAILED! %s', 11, 0, CAST(@floatParm AS VARCHAR(40))

But that gets me a Incorrect syntax near 'CAST' message. I don't understand why that's illegal, but it is. Is there another "one liner" I could use here instead?

Unfortunately, for whatever reason, you can't do an inline conversion in that context, and RAISERROR doesn't directly support float, again, for whatever reason.

For completeness of this answer, here is the relevant snippet from MSDN, which I'm sure you've already seen (note: it's the same text in all versions of the documentation from 2005 to 2012):

  Each substitution parameter can be a local variable or any of these data types: tinyint, smallint, int, char, varchar, nchar, nvarchar, binary, or varbinary.

The only reasonable solution I can think of would be to write a stored procedure to wrap the RAISERROR call. Here's a starting point:

CREATE PROCEDURE [dbo].[MyRaiserror]
(
    @message nvarchar(2048),
    @severity tinyint,
    @state tinyint,
    @arg0 sql_variant = NULL
)
AS
BEGIN

    DECLARE @msg nvarchar(MAX) = REPLACE(@message, '%f', '%s');
    DECLARE @sql nvarchar(MAX) = N'RAISERROR(@msg, @severity, @state';

    DECLARE @int0 int, @char0 nvarchar(MAX), @bin0 varbinary(MAX);

    IF (@arg0 IS NOT NULL)
    BEGIN
        SET @sql += N', ';

        IF (SQL_VARIANT_PROPERTY(@arg0, 'BaseType') IN ('tinyint', 'smallint', 'int'))
        BEGIN
            SET @int0 = CONVERT(int, @arg0);
            SET @sql += N'@int0';
        END
        ELSE IF (SQL_VARIANT_PROPERTY(@arg0, 'BaseType') IN ('binary', 'varbinary'))
        BEGIN
            SET @bin0 = CONVERT(varbinary(MAX), @arg0);
            SET @sql += N'@bin0';
        END
        ELSE
        BEGIN
            SET @char0 = CONVERT(nvarchar(MAX), @arg0);
            SET @sql += N'@char0';
        END
    END

    SET @sql += N');';

    EXEC sp_executesql
        @sql,
        N'@msg nvarchar(2048), @severity tinyint, @state tinyint, @int0 int, @bin0 varbinary(MAX), @char0 nvarchar(MAX)',
        @msg, @severity, @state, @int0, @bin0, @char0;

END

Sadly, there's no easy way to scale this for an arbitrary number of parameters... It could probably be done using convoluted nested dynamic SQL, which would be fun to debug. I'll leave that as an exercise for the reader.

I used sql_variant on the assumption that for code uniformity reasons, the same procedure would be used everywhere, even for value types that are supported directly by RAISERROR. Also, this could be created as a temporary stored procedure if that's appropriate.

Here's what using this procedure would look like:

DECLARE @f float = 0.02345;
DECLARE @i int = 234;
DECLARE @s varchar(20) = 'asdfasdf';
DECLARE @b binary(4) = 0xA0B1C2D3;
DECLARE @d decimal(18, 9) = 152.2323;
DECLARE @n int = NULL;

EXEC [dbo].[MyRaiserror] N'Error message with no params.', 10, 1;
EXEC [dbo].[MyRaiserror] N'Float value = %f', 10, 1, @f;
EXEC [dbo].[MyRaiserror] N'Int value = %i', 10, 1, @i;
EXEC [dbo].[MyRaiserror] N'Character value = %s', 10, 1, @s;
EXEC [dbo].[MyRaiserror] N'Binary value = %#x', 10, 1, @b;
EXEC [dbo].[MyRaiserror] N'Decimal value = %f', 10, 1, @d;
EXEC [dbo].[MyRaiserror] N'Null value = %i', 10, 1, @n;

Output:

Error message with no params.
Float value = 0.02345
Int value = 234
Character value = asdfasdf
Binary value = 0xa0b1c2d3
Decimal value = 152.232300000
Null value = (null)

So the net result is that you don't get formatting ability for floats (roll your own), but you do gain the ability to output them (decimal/numeric, too!) while retaining formatting ability for the other types.


Postgres database not using indexes I have a SQL optimization question.

Table Name: charts

Total Records: +250 million

Columns: timestamp(string), imported(boolean), ....

Indexes:

CREATE INDEX index_charts_on_timestamp ON public.charts
USING btree ("timestamp") WHERE (imported = false)

CREATE UNIQUE INDEX charts_pkey ON public.charts USING btree (id)

QUERY EXPLAIN

SELECT  "charts"."id" FROM
"charts" WHERE
"charts"."imported" = FALSE AND
"charts"."timestamp" = '2018-01-03' ORDER BY
"charts"."id" ASC LIMIT 1000;

OUTPUT

Limit  (cost=1000.59..237865.48 rows=1000 width=8)
 ->  Gather Merge  (cost=1000.59..33262989.10 rows=140426 width=8)
     Workers Planned: 2
 ->  Parallel Index Scan using charts_pkey on charts_pkey  (cost=0.57..33245780.43 rows=58511 width=8)
    Filter: ((NOT imported) AND (("timestamp")::text = '2018-01-03'::text))
(5 rows)

How can i make postgres to use index_charts_on_timestamp instead?

I cannot present EXPLAIN ANALYZE results, the command simply freezes without any results.

I suggest to improve the index to:

(timestamp, id) WHERE (imported = false)

so it fits the query better, for both the WHERE and ORDER BY clauses.

The plan will only need an index seek and a partial index scan (until it finds the required 1000 rows).

The current index does not hold the id values, so the whole index has to be read and then the id values retrieved from the table and sorted to get the first 1000.

If the rows matching the WHERE conditions are not many (less than 1000 or less than 10k), then the sort operation is not very expensive but the values still have to be read as they are not in the index. If the matching rows are a lot more (say 100K or more), then both fetching and sorting them will be much more expensive.
It might be cheaper in these cases to do a full table scan. The optimizer decided in your case to do something similar: a parallel index scan of the PK index. This avoids the need of sorting but it still has to read the rows from the table until it finds 1000 rows that match the WHERE condition.

Both cases however would have no problem with the suggested index above, as the table would not have to be read at all and no sorting needed as the index order matches the ORDER BY.


Is it possible to make a SELECT wait until an ongoing UPDATE finishes? Suppose that I am implementing a bank account logic using PostgreSQL. At some point, I will want to check if the user has sufficient balance to perform an operation. However, the user's balance might be in the middle of an UPDATE statement and, in my perception, any SELECT statements performed during this period will return the old balance. My questions are:

Is my perception correct, i.e. is it possible for the SELECT statement to get old data in this trivial scenario?
If yes, how do I implement locks to protect myself from this? I've read about FOR UPDATE in the docs, but I'm not sure it is meant for this specific situation.

Please assume that I cannot serialize the requests in the clients.

"sufficient balance to perform an operation", this should be one transaction, the check and the operation, otherwise the check is a waste of time.  Plenty could happen between check and operation, if you separate them.


Best type to use in a composite key when one of the values varies in type I am building a data store for the content of multiple blog sites which have been scraped.  Each of these sites is going to have an entry in a Blog table

BlogId                  Url                         Version
int, PK, identity       varchar(2500), not null     int, not null
-----------------       -----------------------     -------------
                1       'http://site1.com/                      1
                2       'http://site2.com/                      1
                3       'http://site2.com/                      2

Then there will be a Post table:

BlogId                Url                        PostId
int, FK, not null     varchar(2500) not null     ?

For most blogs that I'm looking at, I can easily discern a PostId value that is unique for them.

For all blogs, the Url will be unique for a BlogId value.  However, the Url is frequently subject to change.

However, there are a few blogs that I can't discern a good PostId value when scraping the HTML.  In those cases, I can get the datetimeoffset that a post was published on.  The accuracy of the publishing frequency is on the minute level.

The question is, I need to build a composite natural key (I won't use it as a primary key because I'll need to relate to other tables and don't want to carry this along to those tables) using the BlogId and the PostId but I'm not sure what I should type the PostId as.  I've been thinking of two options:

sql_variant - I could use an int for most of my sites, and that would be fine and then use datetimeoffset for the ones where I can't easily discern a unique numeric PostId
bigint - The PostId would fit in here and I can encode the datetimeoffset as a bigint (2011-11-11 13:59 would become 201111111359)
Or some other approach I haven't thought about.

Some other key facts:

The Post table will only have a few thousand records in it initially (if there are more than five thousand, I'd be surprized)
There are related tables for tags, categories, etc, which will be joined with this table (maybe a few thousand records at most)
The growth of any of these tables won't be astronomical.  Maybe a another five thousand records a year (that's being conservative on my part but you never know)
This key will only be used to create an index/constraint, currently, I don't foresee the use of it in queries (there are other attributes that I am linking to which are of more concern).

I wouldn't even consider forcing data of different types into one field.

Another option:

synthetic post_id and
child tables for each 'type' of 'native_post_id'

There are various ways to go about enforcing the subset relationship between these child tables and the parent if necessary


Single User mode can't be turned off and no users are connected I have a database that is in Single User Mode.  When I run this:

exec sp_dboption 'MyDb', 'single user', 'FALSE'

It fails, telling me that there is still a user connected.

But when I run both of these:

exec sp_who2

select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
  from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
 where d.name = 'MyDb'

I get no connections for MyDb.

How can this be, and how can I get my database out of single user mode?

If a user - or process-  is using multiple databases, sp_who2 can only report on one of the databases per spid.

Have you tried something like the following - it will rollback all transaction not completed:

ALTER DATABASE [mydb] set multi_user with rollback immediate


Add a column contains a photo I installed PostgreSQL 9.1 under Ubuntu 12.10

And now I have a table called property

I want to add a field that can be used to store photo(link to the photo or import a photo into a column?)

But I do not know how to add this field

Can PostgreSQL do this?

If so, what keywords can I use to find this information?

Update 2013/10/30

I try to use oid to import my files, and it works fine!

But when I want to use a not superuser user to output files,

it's give me an error

ERROR:  must be superuser to use server-side lo_export()
HINT:  Anyone can use the client-side lo_export() provided by libpq.

How to use client-side lo_export()?

I try to google it, but there's almost all server-side tutorial

So I tried to use byteA

it's works fine with other user but cannot export data to a file?

I tried to use bytea_export but bytea_import is make by Jack's sql script not a built-in function

So there is two more question:

1) How to use clent_side lo_export()

2) how to use bytea_export()??

Thanks for answers!

There are two ways to store Large Objects LOBs(like photos and images) in your property table. You can use the BYTEA data type which is limited to 1GB. The other option is OID, which stores large objects in a special LOB structure. The OID column is limited to 2GB.

OID

Here is the relevant documentation from postgres regarding OIDS.

-- Add the column
ALTER TABLE property ADD COLUMN photo OID;

-- insert the image dilbert.jpg
INSERT INTO property VALUES('dilbert', lo_import('/var/lib/pgsql/dilbert.jpg'));

-- export the image to the tmp directory
SELECT lo_export(property.photo, '/tmp/dilbert.jpg') FROM photos;

BYTEA

Here is a great example from Jack Douglas on how to use a function to import data into a bytea column. Just remember that in order to use the function, you would have to run the following command to activate plpgsql.

psql mydatabase -c 'CREATE LANGUAGE plpgsql;'
psql mydatabase < bytea_import_function.sql

(where bytea_import_function.sql contains the code from Jack's example.)

Now connect to psql and your database

psql mydatabase

-- Add the column
ALTER TABLE property ADD COLUMN photo BYTEA;

-- Insert binary data
insert into photos (name,photo) values('dilbert_bytea',bytea_import('/var/lib/pgsql/dilbert.jpg'));

-- Select data
SELECT name, photo FROM property;

OID has several advantages over BYTEA. BYTEA will preload your images into memory before sending them to the user, whereas an OID column streams the data directly to the user.


Postgres restore unclear behaviour I have following: 2 nodes one of which is current production db and another is for fail recovery. I made base backup on first node with following command pg_basebackup -h 127.0.0.1 -U postgres -D bckp -Ft -z -P -R -x and got bacup file base.tar.gz. Now i'm trying to recover it on failover node. I cleared data directory and unzipped there contents of the backup. As I do not need wal logs from master node to stream to this server i should create recovery.conf with at least restore_command specifying command that will copy wal logs from archive directory to xp_log. But it was already there in xp_log. There was file 000000010000000000000017. As i'm new to postgres and linux I decided to move this file to archive directory and set recovery.conf contents to restore_command = 'cp /var/lib/postgresql/archive/%f /var/lib/postgresql/data/%p' then started postgres. It printed ot logs

2015-10-26 14:06:16 UTC [16-3] LOG:  starting archive recovery
2015-10-26 14:06:16 UTC [16-4] LOG:  restored log file "000000010000000000000017" from archive
2015-10-26 14:06:16 UTC [16-5] LOG:  redo starts at 0/17000060
2015-10-26 14:06:16 UTC [16-6] LOG:  consistent recovery state reached at 0/17000160
cp: cannot stat ‘/var/lib/postgresql/backup/000000010000000000000018’: No such file or directory
2015-10-26 14:06:16 UTC [16-7] LOG:  redo done at 0/17000160
2015-10-26 14:06:16 UTC [16-8] LOG:  restored log file "000000010000000000000017" from archive
cp: cannot stat ‘/var/lib/postgresql/backup/00000002.history’: No such file or directory
2015-10-26 14:06:16 UTC [16-9] LOG:  selected new timeline ID: 2
cp: cannot stat ‘/var/lib/postgresql/backup/00000001.history’: No such file or directory
2015-10-26 14:06:16 UTC [16-10] LOG:  archive recovery complete
2015-10-26 14:06:17 UTC [16-11] LOG:  MultiXact member wraparound protections are now enabled
2015-10-26 14:06:17 UTC [30-1] LOG:  autovacuum launcher started
2015-10-26 14:06:17 UTC [1-2] LOG:  database system is ready to accept connections

Finally it restores and works fine but there are unclear moments for me:
With restoring of 000000010000000000000017 i understand, but why it tries to recover 000000010000000000000018 as it's not there? And what are 00000002.history and 00000001.history files and why it tries to find them?

With regard to your questions, please read the documentation completely, as it addresses all of your concerns.

In terms of looking for 000000010000000000000018 when it doesn't exist .. the recovery process just parses all WAL files until it encounters "EOF" .. which usually happens when it tries to read from the next WAL file and it doesn't exist..  So it uses that case as a termination point for the recovery process..  The important bit here is that it reached a checkpoint in the recovery process as stated with consistent recovery state reached at 0/17000160.  This means that your recovery is reliable.

Continuous Archiving Documentation Section 24.3.5. - Timelines

Timelines are a way for Postgres to handle "alternate universes" so to speak...  For example, if you have a situation where your replication gets messed up in some way and suddenly you have slave promoted into master status (while your master never went down) ... some machines are writing to the master still while some are writing to the slave..  But it's ok, because the writes to the current master will all happen on timeline 1 and the writes to the slave will all happen on timeline 2 .. so you can, theoretically, still merge all the changes together (although I'm sure it wouldn't be easy)..  I imagine it's similar to a branch in a version control system.  Except there's no real "trunk".

For the postgres gurus out there - if I have anything incorrect, please correct me.  :)


SQL Server takes an hour to fail over to secondary node I am at a new job, we have a 2008 R2 (SP1) WSFC which hosts SQL Server 2008 FCI.

Each node has it's own storage, nodes are 32 core with 32 GB memory (don't ask me why it is configured this way).   The storage at each node is a read only copy when the node is secondary. Until today I was told that it was shared storage. I spoke with the sys admin about it because the quorum has a file share witness not a disk witness. He then explained that it does have it's own storage at each site but it is not active on the secondary node.  Blocks of data are replicated between the primary and secondary nodes.

What's happening is that the host is failing over (unclear as to why), the most likely culprit is heartbeat timeout.

When the nodes fail over SQL Logs that it was sent a shutdown command.

Sometimes the second (SQL) node comes up within a minute or so.

Other times it takes an hour for SQL Server to come online on the secondary node.  On the OS Side the failover is quick, it's just SQL Server that takes and hour.

My thought is that something was running.

I have not found any logs which indicate anything like this though.

In this reactionary state is there anything I can look at within SQL such as a system DB log or table (error logs have nothing after the shutdown command issued until the other instance comes online)?

I've not had this issue before and I have been scouring Google to no avail.

First, I want to address a few things.

  Each node has it's own storage, nodes are 32 core with 32 GB memory (don't ask me why it is configured this way). The storage at each node is a read only copy when the node is secondary. Until today I was told that it was shared storage. ... He then explained that it does have it's own storage at each site but it is not active on the secondary node. Blocks of data are replicated between the primary and secondary nodes.

This is block replicated storage and we have no idea how or what is doing it. It could be done through software, it could be done through hardware, it could be a combination of both.

  I spoke with the sys admin about it because the quorum has a file share witness not a disk witness.

There is nothing wrong with having a FSW. It's also the most common witness type.

Now to methodology:

  What's happening is that the host is failing over (unclear as to why), the most likely culprit is heartbeat timeout.

The cluster logs will tell you why. Generate and go through the cluster log to understand why the failover took place. If, for example, the root cause was failed heartbeats then it would require extra packet logging on each node to understand why the heartbeats were failing.

  When the nodes fail over SQL Logs that it was sent a shutdown command.

Yes, so you know the time this was given in the SQL Server errorlog. Thus, the issue had to happen before this in the Cluster Log. This should start your timeline of events which will ultimately lead to the overall time it took for SQL Server to come up.

  Sometimes the second (SQL) node comes up within a minute or so.

We need you to define, "comes up".

It's ambiguous whether you mean that the SQL Server service was attempted to be started by the cluster or the database you want to be online and accessible for user queries is online and accessible. I don't know to which you are referring.

In either case, if it's the cluster to bring the SQL Server resource online (service) then that is all in the cluster log of the node that now owns the resource group. It should be trivial to find an online call to the resource as you can quickly search for the name in the log. If it's for the database to be accessible, then you can look at the SQL Server errorlog and see how long it took for the startup messages and the recovery messages. Both should be trivial and can be added to your timeline of events and times.

  Other times it takes an hour for SQL Server to come online on the secondary node. On the OS Side the failover is quick, it's just SQL Server that takes and hour.

Again, I'm unsure what you mean by the "OS Side". The call to online the resource is successful and SQL Server starts up but the database isn't accessible or the resource group moves over but the resources aren't started in a timely manner?

  I have not found any logs which indicate anything like this though.

AFAIK there is no log that says why a database took a long time to start up. You need to create a timeline of events and extract the data from the multiple sources to build said timeline. Then you can piece together what happened and about how much time each step takes. You can focus in on the long steps and break them down into their individual pieces.

For example, when I do an RCA for these events I start with:

When did the originating issue occur and what was it?
When was SQL Server affected and how did it affect it?
Let's assume, there was a failover.
When did the failover occur on the previous owning node?
when did the resource group move over to the next owning node (if not that last one to own it)?
When was the call to online the SQL Server resource first successful and were there any unsuccessful attempts before that?
When did SQL Server log it started the service?
What order were the databases started and at what time?
When did each database finish the recovery phases and how long did that take?
When was the first successful user connection?
Were there any other notable errors, warnings, or informational messages in the logs?

Once you have that overall timeline you can find the pieces that took the longest and investigate further from there.


How to add all combinations of two pairs of key-value to a table? I have a database of items, as each item has a set of key-value pairs such

place => london
field => engineering

since I frequently need to find items having the same two key-value sets. I created supporting tables by storing designated ids for each key and value.

CREATE TABLE ItemKeyValues
(
ItemID int(11) unsigned,
KeyID mediumint(7) unsigned,
ValueID mediumint(7) unsigned,
PRIMARY KEY (ItemID,KeyID,ValueID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=1

CREATE TABLE Pairs
(
PairID int(11) unsigned NOT NULL AUTO_INCREMENT,
Key1 mediumint(7) unsigned,
Value1 mediumint(7) unsigned,
Key2 mediumint(7) unsigned,
Value2 mediumint(7) unsigned,
UNIQUE INDEX(Key1,Value1,Key2,Value2),
PRIMARY KEY (ItemID,KeyID,ValueID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=1

CREATE TABLE ItemPairs
(
PairID int(11) unsigned NOT NULL,
ItemID int(11) unsigned,
PRIMARY KEY (PairID,ItemID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=1

(I removed indices and FKs for simplicity)

For example, one row of the table Pairs is

PairID    Key1             Value1            Key2              Value2
1         3 (for place)    88 (for London)   10 (for field)    9 (for engineering)

I need to fill the last two tables.

How can I INSERT all combinations of Key1,Value1,Key2,Value2 from the table ItemKeyValues (KeyID,ValueID)?
How can I UPDATE table ItemPairs from the table ItemPairs?

The performance is of great importance since table ItemKeyValues is over 100M rows (though, this is one-time operation).

INSERT INTO Pairs
SELECT DISTINCT NULL, t1.KeyID, t1.ValueID, t2.KeyID, t2.ValueID
FROM ItemKeyValues t1
JOIN ItemKeyValues t2 USING (ItemID)
WHERE t1.KeyID < t2.KeyID;

INSERT INTO ItemPairs
SELECT t1.PairID, t2.ItemID
FROM Pairs t1
JOIN ItemKeyValues t2 ON t1.Key1 = t2.KeyID
                     AND t1.Value1 = t2.ValueID
JOIN ItemKeyValues t3 ON t1.Key2 = t3.KeyID
                     AND t1.Value2 = t3.ValueID
                     AND t2.ItemID = t3.ItemID;

fiddle

PS. I have simplified your structures by removing indices and options.

PPS. Your DDL scripts pack contains at least 1 error (copypaste misprint?).


What happens when the index size of my Mysql instance exceeds the innodb_buffer_pool_size? I am using Amazon's RDS db.m1.small.

innodb_buffer_pool_size is set to 1125.00 MB.

Current index size is 1110.00 MB and growing slowly but steadily.

I do not need more hard drive space.

I want to know if it is necessary to upgrade to a larger instance (db.m1.medium) in order to keep my app working smoothly. Is it important that the index fits into memory? What should be my next steps?

It isn't the index size... it's the data and indexes.  The InnoDB Buffer Pool:

...holds cached InnoDB data for both tables and indexes
— http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_buffer_pool
The larger the buffer pool, the more InnoDB acts like an in-memory database, reading data from disk once and then accessing the data from memory during subsequent reads. The buffer pool even caches data changed by insert and update operations, so that disk writes can be grouped together for better performance.
— http://dev.mysql.com/doc/refman/5.6/en/innodb-buffer-pool.html

Conversely, the smaller the buffer pool, the less InnoDB acts like an in-memory database.
In an ideal world, your buffer pool is larger than data size plus index size plus a little bit of overhead, because InnoDB does nothing to a page from a tablespace without storing that page in the buffer pool, it follows that when the pool is full, something less recently used gets evicted from the pool.  Practicality, of course, often necessitates something smaller than the ideal, and the key to appropriate sizing is the working data set.  The more infrequently-accessed data you have on your server, the fewer potential performance issues you should expect from an undersized pool.
There is, of course, no formula for this... familiarity with your unique working data set is the only "tool" you can use... but SHOW STATUS LIKE '%buffer%'; will give you the counters.
Note that Innodb_buffer_pool_pages_free is only an indication of completely unused space in the pool... it's not an emergency situation or even intensely meaningful if this value is very low or 0, because there is no process to spontaneously evict pages from the pool even when they are old and idle... but as long as they aren't "dirty" then replacing one page with something else is essentially no different in cost than loading a page into free space.  Zero free only means that at some point since the server was started, the total data and indexes read into the pool exceeded the size of the pool and there have not been enough deletions or tables dropped since the last peak to free up any pages.  Note also that running a backup will tend to fill an undersized pool, relatively harmlessly, depending on what's been loaded into the pool by the prior workload.


Does SQLBackup2Azure delete the blobs as well? I am not sure if this questions belongs here or in server fault, apologies if it belongs there.  I am using MS SQL 2012 on an azure VM, and am planning on utilizing the SQLbackup2Azure tool to store the backup files directly in blob storage.  Does anyone know if deleting the stub files locally on the server will also trigger the azure storage service to delete the storage blobs?  My hunch is that it will not and that I should utilize a powershell script (like  this one) to remove them after a time.

I have experimented with the tool and confirmed that deleting the stub files does not delete the Azure blob.  I had to set up a PowerShell script to cleanup the file blobs.


SQL Server: filegroup for system tables only? One of our corporate standards to is have a separate filegroup/file for user tables/indexes. This is set as the default so no need to qualify CREATE TABLE statements.

So it looks like this

fileid 1 = system tables, MDF
fileid 2 = t-log = LDF
fileid 3 = user stuff = NDF

Can anyone here help me understand the original justification why this was mandated?

I'll come clean and state I think it's voodoo. Am I wrong...?

Edit: I am aware of how to use filegroups for separation of indexes/partitions/archives, as well as how to restore piecemeal. This question is about the use of a separate filegroup on the same volume for system tables only.

The isn't a performance gain to this, there is a recoverabily gain to be made.  If file corruption happens in the system tables then the database is lost.  If you keep the user data in a separate file group (or groups) then you can restore just those files keeping the rest of the database online during the restore (assuming Enterprise Edition here).

If this is why they state this, I can't say, but this would be a benefit of having multiple file groups with just the system objects in the PRIMARY filegroup.

You should however kick then in the junk for saying that AutoShrink should be enabled.


I cannot get SQL Server 2012 to install the database engine SQL Server 2012 installation on a standard Windows 2008 R2 server. It refuses to install the database engine or sql replication.

During the installation I got no errors saying that it wasn't installing. So what I did was I ran a repair on the installation and it still stated it would not install. I'm afraid to uninstall and reinstall due to DTC being installed and I don't really want this to boil down to me doing a full 2008 reinstall.

Here's a screenshot of the error I'm getting with this after attempting a repair :

Any suggestions would be greatly appreciated I've been banging my head on this for the past few days.

Also any suggestions on doing an uninstall/reinstall if need be would be great. I'm under the impression that it will cause issues though.

SQL server 2012 installation is not supported through terminal services.  It causes a lot of strange problems, and I experienced this same issue.

Microsoft states this in their prerequisites:

Link is http://msdn.microsoft.com/en-us/library/ms143506.aspx

This document states:

The installation of SQL Server 2012 fails if you launch the setup through Terminal Services Client. Launching SQL Server Setup through Terminal Services Client is not supported.


which one is the fastest way of moving database form local to live server in sql server I am using SQL Server 2008, I have a big database of almost 500 MB. Now I wanted to upload or deploy this DB to the Live server. Currently I am generating scripts and then uploading them to live and by command line I am deploying the database.

This took almost 2 hours so that's very cumbersome to do and one more problem is also that I am not able to use import export database option cause it loses constraints like primary key and all and also gets an error while inserting record from the same process.

Can you tell me how can I do all this and with proper data transfer.

You can use backup/restore. Take a backup of your dev db and restore it on the live server. Should be a bit faster than 2 hours.


Modeling a database for easy counting / reporting I have an app where user is known (user_id) and he can do several actions (action_id).
Every time he makes an action I need to save the fact that he made it for reports/analytics. I guess it is similar to other analytic solutions and their db design.

Once I have the data, provided with a time window (minutes resolution) I need to count for each user (all or some) the number of times he did actions and which actions he did. (sum all data grouped by action_id).

Some assumptions:

The number of users are ~1000.
Action types are ~100.
Actions can happen 24/7.
The time windows can span from minutes to days and are random.
A time window can't go back more than 30 days.

I'm considering SQL, NoSQL and RRD to save the data.

I put RRD here because it's easy to implement the insert of the data into statds+graphite. I'm concerned if I take this approach, the querying (although provided by graphite) will not be indexed and will probably have to count all the data whenever I ask for a window/user (no indexing). Another problem is that when querying all the data, all users info will be needed, resulting in reading all the files concurrently which I'm not sure is a good thing.

SQL - Very easy implementation when inserting the data and querying. Easy to index, order and group by. However I'm not sure it's easy if I'm anticipating high traffic. Also, I'm not sure how effective is the count() of sql (haven't used SQL in the last few years) after group by. Can it offer parallel computation?

NoSQL - Is there a solution out there that is the right fit for this type of scenario (perhaps a Map/Reduce algorithm to fast generation of counts in a time window?)

Thanks for helping me model

I think the essential approach should be to keep it simple. It will save you frustration and unplanned maintenance. The table design is simple a parent (users) and a child (actions) with a one-to-many foreign key relationship. The data you need will be easy to query.

Lets assume active user actions are 500 users 20 actions 100 per day for 30 days. That's about 30 million rows, a regular day's work for a database.

It sounds like you are most comfortable with a standard SQL relational database. That approach is also widely used, with good community and tool support, and easily maintainable.

So you have a simple design, a feasible architecture, and good comfort level with a SQL relational database. Go with that.


Is partial dependency possible In Data Base for Foreign key relationship I have 3 tables Student, Teacher & User.

Student:

CREATE TABLE Student( id INT NOT NULL PRIMARY KEY,name VARCHAR(50) NOT NULL);
INSERT INTO [dbo].[Student]([id],[name]) VALUES(4,'Ram'),(5,'Raman');

Teacher:

CREATE TABLE Teacher( id INT NOT NULL PRIMARY KEY,name VARCHAR(50) NOT NULL);
INSERT INTO [dbo].[Student]([id],[name]) VALUES(1,'Raj'),(2,'Rahul');

User:

CREATE TABLE [dbo].[User](
    id INT NOT NULL PRIMARY KEY,
    user_id INT NOT NULL,
    user_type CHAR(1) NOT NULL,
    user_name VARCHAR(10) NOT NULL,
    user_password VARCHAR(255) NOT NULL,

    CONSTRAINT  FOREIGN KEY (user_id) REFERENCES Student (id),
    CONSTRAINT  FOREIGN KEY (user_id) REFERENCES Teacher (id) );

Now I try to INSERT in User table with below query

INSERT INTO [dbo].[User] ([id] ,[user_id] ,[user_type], [user_name], [user_password])
VALUES  (1 ,1,'S','Raj_001','********')

It gives me error for violation of foreign key due to value of user_id is available in  Teacher and not in Student

So my question is: How can I achieve that a value of user_id is  present in one of those tables and data should be inserted in User table.

Something I would be worried about with this approach is what will keep me from having a Student_id of 27 for Kevin, and a Teacher_ID of 27 for Mr. Jones? If you absolutely want to avoid this you could use a single base table for Person, then everyone get's their own number.

In keeping the current plan, I would pull the user_id field from the user table, and add a user_id field to the student and teacher tables. The student table would now have a foreign key to the Primary key of the user table, essentially going the opposite way you have it going here. Now a person's User_ID is part of their record.

CREATE TABLE Teacher( id INT NOT NULL PRIMARY KEY,name VARCHAR(50) NOT NULL, user_id INT NOT NULL);
INSERT INTO [dbo].[Student]([id],[name], [user_id]) VALUES(1,'Raj', 356),(2,'Rahul',222);

CREATE TABLE Student( id INT NOT NULL PRIMARY KEY,name VARCHAR(50) NOT NULL, user_id INT NOT NULL);
INSERT INTO [dbo].[Student]([id],[name], [user_id]) VALUES(4,'Ram',123),(5,'Raman',232);

CREATE TABLE User(User_ID INT NOT NULL PRIMARY KEY, User_Type(Char(1) NOT NULL, User_Name NVARCHAR(25) NOT NULL, User_Password NVARCHER(255) NOT NULL)

ALTER TABLE Student
ADD CONSTRAINT FK_Student FOREIGN KEY (User_ID) REFERENCES User (User_ID)
ALTER TABLE Teacher
ADD CONSTRAINT FK_Teacher FOREIGN KEY (User_ID) REFERENCES User (User_ID)


Query to get list of busiest schemas in Oracle 12c database I have an Oracle 12c database with many schemas, each schema belongs to a specific application.

I want to produce a query that shows me which schemas are the busiest in terms of read/write activity / IO etc.

I know that I can use the DBA_TAB_MODIFICATIONS table to get modifications since the last stats update, per table.

I've been told that the DBA_HIST objects will be able to give me more information but there's a lot of data there and I don't really know how to get the best out of it for this purpose.

Any help would be appreciated.

DBA_HIST_SEG_STAT stores segment level statistics about reads, writes and others.

Example output from one of my sandbox environments:

select
  o.owner,
  sum(physical_reads_delta) as physical_reads,
  sum(physical_reads_direct_delta) as physical_reads_direct,
  sum(physical_writes_delta) as physical_writes,
  sum(physical_writes_direct_delta) as physical_writes_direct,
  sum(db_block_changes_delta) as db_block_changes
from
  dba_hist_seg_stat ss
  join dba_objects o on (o.object_id = ss.obj#)
group by
  o.owner
order by
  o.owner
;

OWNER  PHYSICAL_READS PHYSICAL_READS_DIRECT PHYSICAL_WRITES PHYSICAL_WRITES_DIRECT DB_BLOCK_CHANGES
------ -------------- --------------------- --------------- ---------------------- ----------------
AUDSYS              0                     0              42                      0              112
BP            7917707               7917685          387803                 386932             4224
DBSNMP              0                     0               0                      0             1264
SYS            192271                 13873          290452                   5753          8182176


Where are SQL Server catalog views persisted? It seems that sys catalog views can be found under every database, either built in or user created. Are they all actual view definitions created under each of the database or just "links" to somewhere?
Thanks

Catalog views do not provide access to replication, SQL Server Agent, or backup metadata.  Those are available in MSDB only.

Reference:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/resource-database

https://technet.microsoft.com/en-us/library/ms179503(v=sql.105).aspx

  The Resource database is a read-only database that contains all the
  system objects that are included with SQL Server. SQL Server system
  objects, such as sys.objects, are physically persisted in the Resource
  database, but they logically appear in the sys schema of every
  database. The Resource database does not contain user data or user
  metadata.

  The Resource database makes upgrading to a new version of SQL Server
  an easier and faster procedure. In earlier versions of SQL Server,
  upgrading required dropping and creating system objects. Because the
  Resource database file contains all system objects, an upgrade is now
  accomplished simply by copying the single Resource database file to
  the local server.

  The physical file names of the Resource database are
  mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are
  located in :\Program Files\Microsoft SQL
  Server\MSSQL.\MSSQL\Binn\ and should not be
  moved. Each instance of SQL Server has one and only one associated
  mssqlsystemresource.mdf file, and instances do not share this file.

  System base tables are the underlying tables that actually store the
  metadata for a specific database. The master database is special in
  this respect because it contains some additional tables that are not
  found in any of the other databases. These tables contain persisted
  metadata that has server-wide scope. The Resource database contains
  the same base table schema as any other non-master database.


nohup import not process I tried to import job but my nohup command seems doesn't work.

devinim@koha-db57:~/kohaaktarim$ nohup mysql -u root -p kohadata < kohaaktarim.sql &
[1] 9619
devinim@koha-db57:~/kohaaktarim$ nohup: appending output to ‘nohup.out’

[1]+  Stopped                 nohup mysql -u root -p kohadata < kohaaktarim.sql
devinim@koha-db57:~/kohaaktarim$ jobs
[1]+  Stopped                 nohup mysql -u root -p kohadata < kohaaktarim.sql
devinim@koha-db57:~/kohaaktarim$ gf
gf: command not found
devinim@koha-db57:~/kohaaktarim$ fg
nohup mysql -u root -p kohadata < kohaaktarim.sql
Enter password:

I entered password but it's not responding. I open another terminal and connect there. I can't see any nohup when I search ps -ef|grep nohup . what should i do? thanks.

ROOT CAUSE

The password option is not correct. When expressed like this

nohup mysql -u root -p kohadata < kohaaktarim.sql &

The word kohadata is being understood as the database

SUGGESTION #1

Either remove the space after -p

nohup mysql -u root -pkohadata < kohaaktarim.sql &

or change the option to --password=

nohup mysql -u root --password=kohadata < kohaaktarim.sql &

SUGGESTION #2

If the word kohadata is the database, you need to add the password

MYSQL_DB=kohadata
MYSQL_PASS=rootpassword
nohup mysql -u root -p${MYSQL_PASS} -D${MYSQL_DB} < kohaaktarim.sql &

GIVE IT A TRY !!!


v$ tables not recognized in a sql statement of a shell script I have .sh script with this block:

sqlplus -S / as sysdba <<EOFILE

set feedback off
set lines 2000
set pages 20000
col db_size for a15
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';

select * from ( select "START_TIME", "END_TIME", INPUT_BYTES_DISPLAY db_size from v$RMAN_BACKUP_JOB_DETAILS order by "END_TIME" DESC) where ROWNUM <= 5;

EOFILE

EOF

But output is this error:

  select * from ( select "START_TIME", "END_TIME", INPUT_BYTES_DISPLAY
  db_size from v order by "END_TIME" DESC) where ROWNUM <= 5
                                                                                * ERROR at line 1: ORA-04044: procedure, function, package, or type is not allowed here

I know there is problem with accessing to v$RMAN_BACKUP_JOB_DETAILS but how should i solve this?

I tried:

v\$RMAN_BACKUP_JOB_DETAILS
'v$RMAN_BACKUP_JOB_DETAILS'
"v$RMAN_BACKUP_JOB_DETAILS"

Can someone help me? Thanks

Try: sqlplus -S / as sysdba <<'EOFILE' ...

Putting quotes tells the shell to not interpret the contents of the here document: it does not try to interpret $XXX in the here document as a shell variable.


In what case is a count(x or null) needed in Gaps and Islands? In this answer, Erwin Brandstetter says:

count(step OR NULL) OVER (ORDER BY date) is the shortest syntax that also works in Postgres 9.3 or older. count() only counts non-null values. In modern Postgres, the cleaner, equivalent syntax would be:
count(step) FILTER (WHERE step) OVER (ORDER BY date)

I'm unsure of why count(step OR NULL) is preferred though. In my query, I do the following. I renamed my variables to match his while maintaining the syntax.
CASE WHEN lag(id_type) OVER (ORDER BY date) <> id_type THEN 1 END AS step

We're counting the values returned by that. Note, that the case can only return 1, or null.

if the two ARE NOT equal, 1 is returned.
if they are equal, it returns null which isn't counted.

Erwin's answer has:

This assumes involved columns are NOT NULL. Else you need to do more.

So I'm even more confused. What is the point of adding count(step OR NULL) what is this protecting our query against?
Could anyone break this down and perhaps show two examples with data wherein only one of them - the one with - count(x OR NULL) works?

It's all about NULL handling. The expression lag(id_type) OVER (ORDER BY date) <> id_type evaluates to a boolean value, which can be TRUE, FALSE or NULL. If either the left or the right operand is NULL, we get NULL. There are various ways NULL can be introduced on either side:

No previous (or next or similar in related cases) row.
Column can be NULL in underlying table.
An OUTER JOIN introduces NULL (no matching row found).
An expression is involved that can evaluate to NULL.

There are various ways to fold three-valued logic to just two cases. A CASE statement, an OR expression etc. Related answer with details:

Select longest continuous sequence

Here is a comparison of the involved techniques, plus (updated) benchmark. Basically, they all perform the same, it's a cheap operation. The aggregate FILTER clause, introduced Postgres 9.4, seems a bit faster in the updated benchmark:

For absolute performance, is SUM faster or COUNT?
Return counts for multiple ranges in a single SELECT statement

There is no case wherein only one of them works, the techniques are interchangeable. What to count or sum exactly depends on details of setup and requirements. In this case we only count TRUE - as long as the underlying table or query yields all non-null values (the corner case of the first row is open for debate).

And this case only requires a simple comparison. Related cases need to look at the previous or next value more closely. Then it may come in handy to provide a default value as 3rd parameter to lag() or lead().


There could be several reasons for autovacuum to not vacuum the table:

a table storage parameter is configured different from the default: enable_autovacuum = off, autovacuum_vacuum_scale_factor is absurdly high or similar.
Find out by looking at the \d+ output for the table in psql.

the rate of change on the table is so high that autovacuum just cannot keep up.
This is the case if you find autovacuum workers running for the table that never finish. The remedy is to lower autovacuum_vacuum_cost_delay.

data corruption in the table or its indexes make autovacuum fail with an error, so that it never finishes processing the table. Look at the log file to diagnose that.


Who done it sp_blitz is there a way to add Session ID and timestamp to your first responders scripts? In particular the sp_BlitzLock. I am trying to find out when and by who some adhoc scripts are run that cause blocking on old SQL Server versions (2008R2/ 2012) .

First, sp_BlitzLock only helps you analyze deadlocks, not blocking.

Second, it won't run on 2008R2 -- it's 2012+ only. The deadlock XML definitions are sufficiently different to make supporting both unwieldy.

Third, it already includes the timestamp of the deadlock event, along with other identifying information. I don't think spid would be helpful.


SQL Server: Apply Custom Server Role afterwards for already existing databases I needed a custom SQL Server role which has readonly access to all databases, regardless of whether a database is existing or is newly created.

The role is not a  problem. The problem is that the role will not be applied to existing databases.
If I create a new database, the role is applied and does working as I expected.

I assume there will be some system stored procedure that will be run at the moment, a new database is created.

Is there any possibility to apply the custom server role afterwards to databases which are existed before i created the custom server role?

I am using SQL Server 2012.

Thanks in advance

I suspect that you are not actually creating a SERVER ROLE but in fact are creating a DATABASE ROLE in the model database.  I looked under server roles for a bit and could find nothing that would grant read access to a database (old or new). I can see ALTER ANY DATABASE, CREATE ANY DATABASE and VIEW ANY DATABASE but nothing about read/write of the tables.

If what I suspect is correct and you are creating a role in the model database then it would have exactly the effect you are discussing.  You would see the role show up in all new databases but not the old ones.  Unfortunately the only way around this is to create the role in each of the existing databases.  Fortunately this is fairly easy.

First thing would be to generate a script that creates the role either by using a scripting tool, creating it in the GUI and hitting the script button, or using the generate scripts option (right click on the database).

Once you have the script for the role you can either manually run it on each of your existing databases (probably ok if you only have <50 or so) or you can generate some dynamic SQL to run the script for you.


Performance of Query where time captured from SP statement completed differs by large from RPC completed I am unable to understand the performance of below query when collected data from XE events:

Query

DECLARE @Importdate DATETIME = ISNULL ((

SELECT ImportDatetime
FROM
Customertable
where CustomerID = 1
and OrderID =1
) , '01/01/2000')

This part of statement returns just 1 row

  SP statement completed shows 40 secs

  RPC Completed for above statement shows 64 secs

Need help to understand why RPC will show such a big difference and where to look for this problem ?
I do not see any NW issue reported as such but what does this indicate here and query as simple as above seems to high for that run time

Please advice

Generally speaking rpc_completed includes network time, while sp_statement_completed does not.  This is very likely the case for your query, since it only returns one row.

So one explanation is the extra time is spent sending data over the network to the client.

You could try measuring wait statistics for this query to confirm there are 24 seconds of ASYNC_NETWORK_IO waits that explain the difference.

If the server has SSMS installed, you could remote desktop into it and run the query there, which should have very little network wait time.  In which case you'd see very little difference in the time on those two events for this statement.


Executing sql linked server procedures through python I am getting an error when trying to execute SQL Server system procedures such as sp_addLinkedServer and sp_dropserver etc from python script
      USE [master]

      EXEC master.dbo.sp_dropserver @server=N'DS', @droplogins='bs'

Error :
          (15002, b"The procedure 'sys.sp_dropserver' cannot be executed within a transaction.DB-Lib
          error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL
          Server\n")

The error is quite clear - you can't run this SP in a transaction, Some libraries (ODBC etc) support implicit transactions (meaning every piece of SQL will be run in a transaction) so you need to dig into why these are enabled. Explained in the link below
or just add SET IMPLICIT_TRANSACTIONS OFF in the line before the SP call
https://stackoverflow.com/questions/60511929/set-set-implicit-transactions-attribute-on-off-for-a-pyodbc-connection


MySQL Workbench Tabs refuse to close I'm using MySQL Workbench 6.1.6, and I have opened a SQL Editor Window and have 14 Query tabs open. If I close the connection, and then open the connection again from the Home page it reopens all 14 tabs. That's ok.

However, if I close the tabs, then close the connection, when I re-open the connection the tabs re-appear. I have tried closing the tabs and exiting workbench. I have tried closing the tabs and opening new ones, but each time the original 14 tabs re-appear.

Does anybody know of what I can do to get rid of these 14 tabs once and for all?

That's actually a bug. MySQL Workbench saves the tabs on close of the connection. To get rid of the unwanted tabs you could temporarily switch off the snapshot feature:

If you like to get that bug fixed file a bug report at http://bugs.mysql.com.


When are the unnecessary row versions being removed from the version store Sorry for my bad English :)

I need a help with understanding version store`s cleaning. I have the following situation.

12:00 Start a select statement under the snapshot isolation level and not commit.
12:01 Start an update statement (update a single row) under the read committed isolation level and commit.
12:02 Start a select statement under the snapshot isolation level and not commit.
12:03 Start an update statement (update a single row) under the read committed isolation level and commit.

Now if we look at the version store such as

SELECT *
FROM sys.dm_tran_version_store

we will see there 2 rows (one row for each update statement).

Now if we commit the first transactions that starts at 12:00, no row is being removed from the version store (even pass a minute).

The question is WHY???.

In my opinion on of the rows have to be removed from the version store, because there are no any active transactions, that have to access to that version.

I tried also to update no 1 row, but 100 rows (Total more then 100 KB) but I have the same situation, the version store is not being removed.

Please help me to understand when does the version store is being clean.
Thanks a lot for help.

Now if we commit the first transactions that starts at 12:00, no row is being removed from the version store (even pass a minute).
The question is WHY ?

Minimal condition for a version row to be garbage collected is when SQL Server determines, based on the transactional states, that this version is no longer needed.
Also, referring to my SQL Server internals notes, for management of version store, SQL Server will perform a regular cleanup every minute with a background process to reclaim all the reusable space from the version store.
You can monitor Version generation rate and version cleanup rate in SQLServer:Transactions perfmon counter to find out rate at which space is acquired and released from version store in KB/Sec.
Check : Managing TempDB in SQL Server: TempDB Basics (Version Store: Simple Example)


Oracle error 12154 while importing DMP file My Setup
I'm running Windows 7 64-bit, using Oracle DB 11g R2 for Windows 64-bit. I have Oracle SQL Developer Release 3.0 (4.0.2.15.21) as the GUI for my database.

Question
I followed the instructions from this post on how to import an Oracle .DMP file. I issued this command into the command prompt: C:\>imp sys@dev/password12345 FROMUSER=FOOADMIN TOUSER=FOOADMIN file=c:\fooadmin.dmp full=yes
It then prompts me for a password in which I enter the correct one and then I receive this error:
IMP-00058: ORACLE error 12154 encountered
ORA-12154: TNS:could not resolve the connect identifier specified
IMP-00000: Import terminated unsuccessfully

Actual Question: How do I fix this error and be able to import the .dmp file successfully? Or what are some steps I can head towards to potentially get this working...?

My Files & What I've Done
I have looked through countless posts on this same issue. My tnsnames.ora, listener.ora, & sqlnet.ora are located in this directory: C:\oracle\product\11.2.0\dbhome\NETWORK\ADMIN
My connection in my tnsnames.ora file looks like this (I have also tried to put my computer name as the HOST, instead of 127.0.0.1)...
DEV =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = DEV)
  )
 )

PATH Variables
I added the following to my PATH (I realize there may be some unnecessary item in there, I just wanted to be on the safe side. ):
C:\oracle\product\11.2.0\dbhome\bin;C:\oracle\product\11.2.0\client_1;C:\oracle\product\11.2.0\client_1\bin;%ORACLE_HOME%\bin;
I also created two more variables in my System Variables...
TNS_ADMIN C:\oracle\product\11.2.0\dbhome\NETWORK\ADMIN
TNS_NAMES C:\oracle\product\11.2.0\dbhome\NETWORK\ADMIN

Use this on Windows (three nested double quotes):

exp system/password_for_system@dev file=c:\fooadmin.dmp full=yes

imp """sys/password12345@dev as sysdba""" FROMUSER=FOOADMIN TOUSER=FOOADMIN file=c:\fooadmin.dmp

In general imp would work as intended with sys account, it is just not recommended. But exp with sys account could possibly produce inconsistent dump file silently. So it's better to avoid it, for example use system.


How to set-up a distributed data storage system for The Real Junkfood Project Context

The Real Junkfood Project consists of a network of pay as you feel (PAYF) cafes. We need to log the data from each of ~100 cafes, generating perhaps 50 rows each par day. I estimate that this could (if we expand) generate around 1 million rows per year. How best to store this data in a system that allows computer noobs to log their data in a way that is as easy and user-friendly as possible? That's the essence of my question. Details below.

Introduction to data storage

I know that the appropriate way to store data is described by the term 'tidy data'
[@Wickham2014]. The rules of data stored in this way are:

Each variable forms a column.
Each observation forms a row.
Each type of observational unit forms a table.

TRJFP data

The data collected by the TRJFP operates at several levels, so several
tables are needed. These are

Food items intercepted
Daily totals (e.g. meals served, finances)
Interception points
Cafes

The first 2 tables are usually collected daily by each cafe. The 3rd table
on 'interception points' (where food is picked-up from)
is also collected by each cafe but is only updated
when a new interception point is logged. The 5th table provides a global overview
of all the cafes and is updated every time a new cafe is added.

By far the largest table in the database will be 1,, which could store perhaps 50
new items per day. Assuming (optimistically) that in the future cafes are dilligent in
logging the data, we could log data from 100 cafes. That would be 5,000
rows per day or around 1.8 million rows of data per year. This is not really
Big Data, but it is worth thinking carefully about how it is stored.

The variables associated with each table are described below.
Critical to the useability of the data is that the datasets can be
linked. Therefore linking variables connecting each table are described.

Options

There are 3 main options for storing these tables, from simple to complex.

On Google Fusion Tables
As regularly backed-up .csv files managed through an online server system like Shiny
On a fully fledged database system, like postgres

Generally speaking I would recommend a full fledged relational database.  This will give you everything you need, and allow you to grow gracefully.

As far as what exact engine to use that depends on personal preference/budgetary constraints/hosting requirements/compatability with the application layer/etc.  Personally I would probably use the Microsoft stack (SqlServer database, C# for the application layer, etc) because that is what I'm most familiar with, and would be most efficient in coding.  Others may choose other platforms based on X,Y,Z reasons.  The truth is any mainstream relational database engine will support your needs.

I wouldn't recommend csv files because that is highly likely to become unmanageable. I would overwhelmingly recommend Sql Lite over csv files given only these two options.  Both are lightweight, but Sql Lite at least offers some database functionally. Here is a good link for what SqlLite is good for. Basically good: local storage and/or replacing CSV/custom data storage files. Not designed for: Replacing client/server SQL database engines (SqlServer, Oracle, PostgreSQL, etc).

I also wouldn't recommend Google Fusion Tables because I believe it'll be harder to manage as you grow.  I could be wrong but that is my general experience with those types of solutions.  Also Fusion Tables is an experimental app, so I would be worried about what happens if the "experiment" fails...

I wouldn't recommend NoSql because I don't think you will gain much if anything, and relational type reporting will be complicated.


SQL agent jobs appear as job id only SQL Server 2005, I'm seeing about 100 'jobs' in the SQL Agent>jobs node in SSMS. They appear as hexadecimal strings not with their real names:

If I query msdb..sysjobs, I see a much smaller list of jobs and their corresponding JobName.

So what are these jobs and how can I make them appear as their real job name as opposed to the JobID?

Thanks,
Peter

SQL Server 2005, I'm seeing about 100 'jobs' in the SQL Agent>jobs node in SSMS. They appear as hexadecimal strings not with their real names:

They are from reporting service subscriptions.

Below SQL will give you list of the job names against the actual reports

select ScheduleID
    ,Path
    ,name
    ,s.Description
from ReportServer.dbo.catalog c
inner join ReportServer.dbo.Subscriptions s on c.ItemID = s.Report_OID
inner join ReportServer.dbo.ReportSchedule rs on rs.SubscriptionID = s.SubscriptionID

Even better (got it from this link):

  A SQL Server Agent job is created to control the scheduled execution of the report, and this is stored in the sysjobs and sysjobsteps of the MSDB database. The agent job name is a guid value that represents the ScheduleId (yes, in case you've not yet noticed, this makes your Agent Job list messy with a bunch of guids that make it hard to work with your non-subscription jobs, we are hearing you:).

select 'Report' = c.Path
    ,'Subscription' = s.Description
    ,'SubscriptionOwner' = uo.UserName
    ,'SubscriptionModBy' = um.UserName
    ,'SubscriptionModDate' = s.ModifiedDate
    ,'ProcessStart' = dateadd(hh, DATEDIFF(hh, Getutcdate(), Getdate()), n.ProcessStart)
    ,'NotificationEntered' = dateadd(hh, DATEDIFF(hh, Getutcdate(), Getdate()), n.NotificationEntered)
    ,'ProcessAfter' = dateadd(hh, DATEDIFF(hh, Getutcdate(), Getdate()), n.ProcessAfter)
    ,n.Attempt
    ,'SubscriptionLastRunTime' = dateadd(hh, DATEDIFF(hh, Getutcdate(), Getdate()), n.SubscriptionLastRunTime)
    ,n.IsDataDriven
    ,'ProcessHeartbeat' = dateadd(hh, DATEDIFF(hh, Getutcdate(), Getdate()), n.ProcessHeartbeat)
    ,n.Version
    ,n.SubscriptionID
from Notifications n
inner join Subscriptions s on n.SubscriptionID = s.SubscriptionID
inner join catalog c on c.ItemID = n.ReportID
inner join Users uo on uo.UserID = s.OwnerID
inner join Users um on um.UserID = s.ModifiedByID


Comparing Row Values I'm a SQL newbie and would like some help please.

This is my table:

ID  Name  Number  Src_Call_Leg  Dest_Call_Leg
--  ----  ------  ------------  -------------
1   John  5555    100           1000
2   John  5555    100           102

The table stores call records. When a call is made to 5555, the system first took source call leg 100 and destination call leg 1000 and a final destination call leg of 102 before it was answered by John. The source call leg does not change in the whole process.

What I'd like is to show only the data which has Name, Number, Source Call Leg and the final destination call leg for records where the interim call leg starts with 1000.

I tried doing this but it didn't work. This is my attempt:

select
  tb.name,
  tb.number,
  tb.Src_Call,
  tb.Dest_Call_leg
from
  table tb
where
  tb.Src_Call_Leg IN (
    select
      tb1.Src_Call_Leg
    from
      table tb1
    where
      tb1.Dest_Call_Leg like '1000%'
  );

I know I'm doing something really silly, would appreciate it if someone could point that out.

The source call leg does not change in the whole process.

You need to remember that Name and Number also stay the same. So, your IN predicate should include those column in the matching:

  (tb.name, tb.number, tb.Src_Call_Leg) IN (
    SELECT
      tb1.name, tb1.number, tb1.Src_Call_Leg
    FROM
      yourTable tb1
    WHERE
      tb1.Dest_Call_Leg LIKE '1000%'
  )

Not all SQL products support tuple comparison, though, and you have not specified what is yours, so the above syntax might not work for you.

Still, there is an alternative that will most likely work: an EXISTS predicate. It will go like this:

  EXISTS (
    SELECT
      *
    FROM
      yourTable tb1
    WHERE
      tb1.Dest_Call_Leg LIKE '1000%'
      AND tb1.name = tb.name
      AND tb1.number = tb.number
      AND tb1.Src_Call_Leg = tb.Src_Call_Leg
  )

And since you have also specified that you want only the final Dest_Call_Leg, you probably need this additional condition in the main WHERE:

  AND tb.Dest_Call_Leg NOT LIKE '1000%'

So, the entire query would look like this:

SELECT
  tb.name,
  tb.number,
  tb.Src_Call,
  tb.Dest_Call_leg
FROM
  yourTable tb
WHERE
  EXISTS (
    SELECT
      *
    FROM
      yourTable tb1
    WHERE
      tb1.Dest_Call_Leg LIKE '1000%'
      AND tb1.name = tb.name
      AND tb1.number = tb.number
      AND tb1.Src_Call_Leg = tb.Src_Call_Leg
  )
  AND tb.Dest_Call_Leg NOT LIKE '1000%'
;

Alternatively, if your SQL product supports analytic functions like LAST_VALUE(), you could implement the request like this:

SELECT
  Name,
  Number,
  Src_Call_Leg,
  Final_Dest_Call_Leg AS Dest_Call_Leg
FROM
  (
    SELECT
      Name,
      Number,
      Src_Call_Leg,
      Dest_Call_Leg,
      LAST_VALUE(Dest_Call_Leg) OVER (
        PARTITION BY Name, Number, Src_Call_Leg
        ORDER BY ID ASC
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
      ) AS Final_Dest_Call_Leg
    FROM
      yourTable tb1
  ) derived
GROUP BY
  Name,
  Number,
  Src_Call_Leg,
  Final_Dest_Call_Leg
HAVING
  COUNT(CASE WHEN Dest_Call_Leg LIKE '1000%' THEN 1 END) > 0
;

The derived table calculates the final Dest_Call_Leg for every call. Columns Name, Number, Src_Call_Leg and Final_Dest_Call_Leg will stay the same across every group of rows that belong to the same call – so the main SELECT groups the derived table by those columns and uses HAVING to filter out the groups that do not have an intermediate Dest_Call_Leg that matches the 1000% pattern.


date variable manipulation I'm working on DB2 stored procedure, which will be called by the Korn Shell (.ksh). My DB2 version is 11.5 and it is running on AIX.

I was stuck in TIMESTAMP to DATE conversion. Below is my requirement.

If s_day < day(s_date) then
start_date = YYYY-MM from s_date + 1 day from s_day
end if

Note here s_date is DATE variable, s_day is CHAR variable and start_date is DATE variable.

So I tried, the following code, but got an error.

   IF s_day < DAY(s_date)
    THEN
    SET start_date = DATE (DATE(s_date,'YYYY-MM') || s_day + 1);
    END IF

Sample values

s_day =03
s_date= 15/05/2020
expected output start_date is 2020-05-04

Can anyone help me to achieve expected output?

Try

IF int(s_day) < DAY(s_date)
THEN
    SET start_date = THIS_MONTH(s_date) + int(s_day) days;
END IF


should I shrink the filegroup? how? SQL Server I just have a question. We recently purged 1,5 millions of record from a table. The Current allocated space for the related Filegroup is 700GB, but the space Used is 300GB. Should I perform a shrink for this particular case in order to reduce the Allocated space /disk size? How can we do it? because it is an .ndf file, I have never shrink an .ndf file. Thanks in advance!

Why would you?

I mean, the disk is there for you to use, and as I guess the server in which the database runs is devoted to it, there is no real reason to give the space back to the OS just to have the OS allocate it again to the same datafiles in the future.

Shrinking is in general not a really good idea, when you do it, you risk facing fragmentation issues which will hurt your performance.

In case you have a good reason to do it DBCC SHRINKFILE(yourfile) is what you need.


How to create a database from an XML file's DTD I have a fairly large and complex self-describing XML file (It's an Apple Healthkit export; 183 MB).

Are there any utilities available to create a database in MySQL or PostgreSQL  with the tables and fields described in the XML file's DTD?

(If not, I suppose PHP's SimpleXML Parser would help but would require more thought.)

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE HealthData [
<!-- HealthKit Export Version: 3 -->
<!ELEMENT HealthData (ExportDate,Me,(Record|Correlation|Workout|ActivitySummary)*)>
<!ATTLIST HealthData
  locale CDATA #REQUIRED
>
<!ELEMENT ExportDate EMPTY>
<!ATTLIST ExportDate
  value CDATA #REQUIRED
>
<!ELEMENT Me EMPTY>
<!ATTLIST Me
  HKCharacteristicTypeIdentifierDateOfBirth         CDATA #REQUIRED
  HKCharacteristicTypeIdentifierBiologicalSex       CDATA #REQUIRED
  HKCharacteristicTypeIdentifierBloodType           CDATA #REQUIRED
  HKCharacteristicTypeIdentifierFitzpatrickSkinType CDATA #REQUIRED
>
<!ELEMENT Record (MetadataEntry*)>
<!ATTLIST Record
  type          CDATA #REQUIRED
  unit          CDATA #IMPLIED
  value         CDATA #IMPLIED
  sourceName    CDATA #REQUIRED
  sourceVersion CDATA #IMPLIED
  device        CDATA #IMPLIED
  creationDate  CDATA #IMPLIED
  startDate     CDATA #REQUIRED
  endDate       CDATA #REQUIRED
>
<!-- Note: Any Records that appear as children of a correlation also appear as top-level records in this document. -->
<!ELEMENT Correlation ((MetadataEntry|Record)*)>
<!ATTLIST Correlation
  type          CDATA #REQUIRED
  sourceName    CDATA #REQUIRED
  sourceVersion CDATA #IMPLIED
  device        CDATA #IMPLIED
  creationDate  CDATA #IMPLIED
  startDate     CDATA #REQUIRED
  endDate       CDATA #REQUIRED
>
<!ELEMENT Workout ((MetadataEntry|WorkoutEvent)*)>
<!ATTLIST Workout
  workoutActivityType   CDATA #REQUIRED
  duration              CDATA #IMPLIED
  durationUnit          CDATA #IMPLIED
  totalDistance         CDATA #IMPLIED
  totalDistanceUnit     CDATA #IMPLIED
  totalEnergyBurned     CDATA #IMPLIED
  totalEnergyBurnedUnit CDATA #IMPLIED
  sourceName            CDATA #REQUIRED
  sourceVersion         CDATA #IMPLIED
  device                CDATA #IMPLIED
  creationDate          CDATA #IMPLIED
  startDate             CDATA #REQUIRED
  endDate               CDATA #REQUIRED
>
<!ELEMENT WorkoutEvent EMPTY>
<!ATTLIST WorkoutEvent
  type CDATA #REQUIRED
  date CDATA #REQUIRED
>
<!ELEMENT ActivitySummary EMPTY>
<!ATTLIST ActivitySummary
  dateComponents           CDATA #IMPLIED
  activeEnergyBurned       CDATA #IMPLIED
  activeEnergyBurnedGoal   CDATA #IMPLIED
  activeEnergyBurnedUnit   CDATA #IMPLIED
  appleExerciseTime        CDATA #IMPLIED
  appleExerciseTimeGoal    CDATA #IMPLIED
  appleStandHours          CDATA #IMPLIED
  appleStandHoursGoal      CDATA #IMPLIED
>
<!ELEMENT MetadataEntry EMPTY>
<!ATTLIST MetadataEntry
  key   CDATA #REQUIRED
  value CDATA #REQUIRED
>
]>
<HealthData locale="en_GB"> ... etc. (data)

Use the following tools:

  1) create full sql schema from DTD.

DTDParse + XML::RDB

  2) convert XML doc with data into SQL.

https://stackoverflow.com/questions/13121456/transform-xml-using-xslt-to-sql

References

DTD Tools (the link no longer works)
XML Matters: Using Python modules xml2sql and dtd2sql


Find day values in bigint date? This query is working good.

select  DATENAME(DW,getdate()) AS [Weekday]

My Problem is suppose I give input values like

select  DATENAME(DW,InvoiceDate) AS [Weekday] from store;

is not working in SQL-Server 2008 R2.

Column datatype is BIGINT, column values below

InvoiceDate

20130925
20130926
20130927
20130928
20130929
20130930

Firstly, why on earth are you storing dates as BIGINT?!

Date fields should be stored as dates, otherwise you could end up inserting invalid dates and you'll have trouble doing basic date calculations, as per now.

To answer your question, you'll need to CAST the InvoiceDate field, firstly to a VARCHAR, then to a DATE type:

SELECT DATENAME(WEEKDAY, CAST(CAST(InvoiceDate AS VARCHAR(10)) AS DATE)) AS [WeekDay]
    FROM Store;


Merge Replication Large Table We have a two SQL Server instances that use merge replication to keep in sync.

There is a single table that potentially can add millions of rows in between syncronisations (which is a relative small interval).

This table has a compound primary key (which is also the clustered index).

Replication is currently unable to merge changes between the two database servers (the query times out).

Is there any potential pitfalls with merge replication in this scenario? Is there a performance problem related to the database schema?

For the timeout issue, you can increase the Query Timeout value in the merge agent.

See this Replication Merge Agent reference to specifics on the QueryTimeOut.


PostgreSQL Automatic Failover (Trigger File Creation)? Is there a way to detect if the master PostgreSQL (9.3) server becomes unresponsive, and if so, create the trigger file (that is specified in recovery.conf) that initiates the failover process?

Or more specifically, is there some kind of ping or keepalive command that you can use from the slave to see if the master is responding?  If so, this can probably be implemented with a shell script rather easily.  I have not yet found a command that does something similar to this.  Any help is appreciated, thank you.

repmgr 2.0 supports automatic failover.

However, I strongly advise you not to use automatic failover. It's generally a much better idea to use manually triggered automated failover, where the failover process is scripted/automated, but a human has to say "Yup, master's dead, do it".

If you do use automatic failover you need to make it a routine part of your operations, where you test it regularly. Otherwise it will bite you when you least expect it and can least afford it.

It is also vital that you consider fencing/STONITH as part of your failover solution, to prevent two diverging masters.


querying sys.database_principals accross a linked server I'm attempting to get a listing of the users and roles they are associated with on a remote server to be input into a database on the local server.  But when I use the linked server sys.database_principals only shows me information for the login that the linked server is using.  How do I get a listing of users and roles by database to a central db location?

sys.database_principals and sys.server_principals are protected views.

From the MSDN page on sys.database_principals:

  Any user can see their own user name, the system users, and the fixed database roles. To see other users, requires ALTER ANY USER, or a permission on the user. To see user-defined roles, requires ALTER ANY ROLE, or membership in the role.

You need to ensure the login used by the linked server has at least the ALTER ANY USER or ALTER ANY ROLE privilege in order to see the other principals.


Can a user with readonly access change SQL server settings Database administration is not a strong point of mine, and I was wondering if a user with read only access can change the server settings.

In their query I can see this at the beginning:

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF

I'm worried because we have been having SQL database deadlock issues and performance issue, and Microsoft say SET ARITHABORT OFF should be SET ARITHABORT ON

Thanks.

Both the settings that you have mentioned in the question requires membership in public role, which is true in almost all the cases. You may refer below screenshot from the login window:

Kindly refer microsoft site for both of these as below:

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-warnings-transact-sql?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-arithabort-transact-sql?view=sql-server-2017

You may also refer article from Pinal Dave on this setting. There is another wonderful article from Mr. Ahmad Yaseen,

I hope above helps.


How to get data from different table having same column? I have a database which stores historical data in different tables with same column names. Is there any way I can query all tables and get result

Example

2016_01_01----Fields-f1,f2,f3,f4----Values-1,2,3,4
2016_02_01----Fields-f1,f2,f3,f4----Values-5,6,7,8
2016_12_01----Fields-f1-f2-f3-f4----Values-9,10,11,12

Result should be

f1   f2    f4
1    2     4
5    6     8
9    10    12

UNION (Transact-SQL).

  Combines the results of two or more queries into a single result set
  that includes all the rows that belong to all queries in the union.

select T.f1, T.f2, T.f3
from [2016_01_01] as T
union all
select T.f1, T.f2, T.f3
from [2016_02_01] as T
union all
select T.f1, T.f2, T.f3
from [2016_12_01] as T;

One way to build the statement dynamically.

/*
-- Create tables
create table [2016-01-01](f1 int, f2 int, f3 int);
create table [2016-02-01](f1 int, f2 int, f3 int);
create table [2016-12-01](f1 int, f2 int, f3 int);
*/

declare @SQL nvarchar(max);

set @SQL = stuff((
  select
N'
union all
select T.f1, T.f2, T.f3
from ' + quotename(name) + N' as T '
  from sys.tables
  -- Get all tables where the name is a date
  where isdate(name) = 1
  for xml path(''), type
  ).value('text()[1]', 'nvarchar(max)'), 1, 13, '');

-- Print statement
print @SQL;

-- Execute statment
exec (@SQL);


DBCC Shrinkfile after full backup restoration but before differential restoration My database full backup file size is 760 GB and differential would be around 20 GB but the database size is around 2TB. As it is a production database, so to apply only a minimum downtime, i want to run DBCC SHRINKFILE with TRUNCATEONLY before restoring differential backup.

Please advise if i can run the command DBCC SHRINKFILE with TRUNCATEONLY after full backup restored but before differential backup restoration.

Please advise if i can run the command DBCC SHRINKFILE with
  TRUNCATEONLY between full backup restored and before differential
  backup restoration.

No, you cannot.

You'll get the error that your database is in the middle of restore, and this is logic, you cannot do anything with database that is restoring.

You should analyse your db free space using sp_spaceused to understand if you can and should do anything with this database BEFORE you take the last backup, may be your db is in the full recovery model and log backups are not made regularly?


What determines whether `pg_dump` includes a varchar length? I work on a project where we regularly dump our Postgres database schema and commit it in the project repository. However, depending on which developer dumps the schema (via pg_dump), varchar columns may be given without a length:

email character varying NOT NULL

or with a length:

email character varying(255) NOT NULL

Is there a configuration option that determines whether the length is included in the schema dump?

From the docco here, it says that

  The notations varchar(n) and char(n) are aliases for character
  varying(n) and character(n), respectively. character without length
  specifier is equivalent to character(1). If character varying is
  used without length specifier, the type accepts strings of any size.
  The latter is a PostgreSQL extension.

Note the bit in bold. Your email fields with CHARACTER VARYING without a length are like TEXT fields. This is not good practice - you really only should set fields to this if you really don't know the longest possible value. From the correct answer to this thread, it's 254 characters. This area is treated well in this thread (also check out the link within the correct answer).

[EDIT]

Of course, as @DanielVérité points out, these are not the same fields from the same table - my answer was predicated on the first being the email in table_x and the second being the email in table_y. If they're meant to be the same field in the same table, someone has made a mistake, perhaps not huge, but there's a problem.

It's better to have CHARACTER VARYING(254) or VARCHAR(254) for all email fields. That way, if you attempt to put more than 254 characters into the email field, PostgreSQL will throw an error:

test=# create table x(y varchar(4));
CREATE TABLE
test=# insert into x values('sadfsadfa');
ERROR:  value too long for type character varying(4)
test=# select * from x;
 y
---
(0 rows)

test=#


How to use query result as REPLACE parameters i'm trying to:

1- Find a string inside a lot of content

2- Replace that specific part of the string with a new one using REPLACE function like this:

REPLACE(column_name, query#1, query#2)

Is this possible?

I already have located the exact part of the content that i want to replace

SELECT `post_title`, SUBSTRING(`post_content`, LOCATE('[flv:',`post_content`), LOCATE(']',`post_content`) - LOCATE('[flv:',`post_content`) + 1) AS amazonlinks FROM wp_posts_duplicatedfortestonly WHERE `post_content` LIKE '%[flv:%'

and have the new string ready in another table column: Png_Link

Finally i'm trying to merge all this using the REPLACE function but it's giving me a lot of errors:

UPDATE wp_posts_duplicatedfortestonly
SET
    post_content = REPLACE(post_content,
                           (SELECT SUBSTRING(`post_content`, LOCATE('[flv:',`post_content`), LOCATE(']',`post_content`) - LOCATE('[flv:',`post_content`) + 1) FROM (SELECT post_content FROM wp_posts_duplicatedfortestonly) AS something WHERE `post_content` LIKE '%[flv:%'),
                           (SELECT `Png_Link`  FROM `VideoPlayerUdpateLinksReady` WHERE `Mp4_Link` != ''));

Something like this (only difference is i'm using 2 tables): https://www.db-fiddle.com/f/91EW2kJDc8aWJYYvw86vG9/0

Also tried with variables without any luck:

SET @tblname := "wp_posts_duplicatedfortestonly";
SET @colname := "post_content";
SELECT @lookfor := SUBSTRING(`post_content`, LOCATE('[flv:',`post_content`), LOCATE(']',`post_content`) - LOCATE('[flv:',`post_content`) + 1) FROM @tblname WHERE `post_content` LIKE '%[flv:%';
SELECT @replacewith := `Png_Link` FROM `VideoPlayerUdpateLinksReady` WHERE `Mp4_Link` != '';

SET @qry = CONCAT('UPDATE ',@tblname, ' SET ', @colname, ' = REPLACE(' ,@colname, ',', @lookfor, ',',@replacewith,')');
PREPARE QUERY FROM @qry;
EXECUTE QUERY;

What am i'm doing wrong? Is it possible to use SELECT expressions as REPLACE function parameters or what other workaround do i have?

Managed to solve it on my own rookie way:

1- Since i couldn't make the REPLACE function to accept a query as parameter i just inserted the query result in a new table column:

INSERT INTO VideoPlayerUdpate_FLV_links (`ID`,`PostTitle`,`AmazonLinks`) SELECT `ID`, `post_title`, SUBSTRING(`post_content`, LOCATE('[flv:',`post_content`), LOCATE(']',`post_content`) - LOCATE('[flv:',`post_content`) + 1) AS amazonlinks FROM wp_posts WHERE `post_content` LIKE '%[flv:%'

Remember I had another table VideoPlayerUdpateLinksReady with all my new string that i wanted to replace. I had to manually construct and validate all errors in this column VideoPlayerUdpateLinksReady.Png_Link so it would be ready to replace.

2- So final Step was only to use these 2 tables as parameters in the REPLACE function:

UPDATE `wp_posts_duplicatedfortestonly`  JOIN `VideoPlayerUdpate_FLV_links` ON wp_posts_duplicatedfortestonly.ID = VideoPlayerUdpate_FLV_links.ID  JOIN `VideoPlayerUdpateLinksReady` ON wp_posts_duplicatedfortestonly.ID = VideoPlayerUdpateLinksReady.ID SET `post_content` = REPLACE(`post_content`,VideoPlayerUdpate_FLV_links.AmazonLinks, VideoPlayerUdpateLinksReady.Png_Link)

And i got all my content replaced without any further issues.


Need help with null field values I am having a problem with syntax.  I have the following statement which updates a field in a table with a value from a field in a different table. I need the statement to update the field with the text in field 142 unless field 142 is null, then update with field 17. Here is the existing code:

--Defendant Address
update xrji_:Usernum
    set DefendantAddress=trim(MemoText)
from eqanswer
where eqanswer.entitynum=xrji_:Usernum.DefendantEntityNum and
                       entityrole='DEFENDANT' and fieldnum=142
;

Columns MemoText, entityrole and fieldnum belong to the eqanswer table.

Let me know if this needs further clarification.
Any suggestions would be appreciated.

DBMS is: Advantage.

The principal difficulty here is probably that the alternative value of MemoText you want to use in case the fieldnum=142 value is null is in a different row. It would be much easier if it was just a different column of the same row. That way you could use the pattern suggested by Manu – I mean specifically this part:

set <field_to_update> = ISNULL(<field142>, <field17>)

The ISNULL function will evaluate to <field142> if that value is not null, otherwise the function will evaluate to <field17>.

So is it possible to arrange two values from different rows on the same row? Yes, there are at least two ways to do so.

First, you could replace the eqanswer reference with a derived table that groups eqanswer rows by entitynum and uses conditional aggregation to present field 142 and field 17 values on the same row, like this:

SELECT
  entitynum,
  MAX(CASE fieldnum WHEN 142 THEN MemoText END) AS MemoText142,
  MAX(CASE fieldnum WHEN 17  THEN MemoText END) AS MemoText17
FROM
  eqanswer
WHERE
  entityrole = 'DEFENDANT'
  AND fieldnum IN (142, 17)
GROUP BY
  entitynum

Now you can use the above query in the UPDATE statement like this:

UPDATE
  xrji_:Usernum
SET
  DefendantAddress = TRIM(ISNULL(eqa.MemoText142, eqa.MemoText17))
FROM
  (
    SELECT
      entitynum,
      MAX(CASE fieldnum WHEN 142 THEN MemoText END) AS MemoText142,
      MAX(CASE fieldnum WHEN 17  THEN MemoText END) AS MemoText17
    FROM
      eqanswer
    WHERE
      entityrole = 'DEFENDANT'
      AND fieldnum IN (142, 17)
    GROUP BY
      entitynum
  ) AS eqa
WHERE
  eqa.entitynum = xrji_:Usernum.DefendantEntityNum
;

The other method would require joining the eqanswer table to xrji_:Usernum twice: once to get the field 142 MemoText and once again to get the field 17 MemoText. For this it would be more convenient to rewrite UPDATE to use an explicit join. The complete statement would look something like this:

UPDATE
  xun
SET
  DefendantAddress = TRIM(ISNULL(eqa142.MemoText, eqa17.MemoText))
FROM
  xrji_:Usernum AS xun
  LEFT JOIN eqanswer AS eqa142 ON eqa142.entitynum = xun.DefendantEntityNum
    AND eqa142.entityrole = 'DEFENDANT'
    AND eqa142.fieldnum = 142
  LEFT JOIN eqanswer AS eqa17 ON eqa17.entitynum = xun.DefendantEntityNum
    AND eqa17.entityrole = 'DEFENDANT'
    AND eqa17.fieldnum = 17
WHERE
  eqa142.entitynum IS NOT NULL
  OR eqa17.entitynum IS NOT NULL
;

You can see that this UPDATE statement uses the same ISNULL pattern as the previous UPDATE does. The difference is only in how the MemoText values from different rows manage to get on the same row. This time they simply come from different, independently joined subsets of eqanswer.

The query uses outer joins instead of inner joins because of the assumption that field 142 or field 17 may be absent for any given entitynum. The query still checks that at least one of the two rows is present – otherwise the corresponding xrji_:Usernum row is not updated at all. If you still would like to update such rows (with a null, apparently), just remove the WHERE clause.