Max Price Using Multiple Tables and Columns The below code is working for me to pull out the max Avg Price, but I need to add another couple of fields from the dbo.TreatmentEvent table, when I add another column it stops giving me the max and instead just starts listing out all values.
SELECT Max_AvgPrice = MAX(pav.AvgPrice),
       te.a_ID_Treatment
  FROM dbo.TreatmentEvent           te
  LEFT JOIN
       dbo.dim_Procedure_Codes      pco ON te.Code_Insurance_1 = pco.a_IDPK_ProcedureCode
  LEFT JOIN
       dbo.dim_Procedure_Category   pca ON pco.a_ID_ProcedureCategory = pca.a_IDPK_ProcedureCategory
  LEFT JOIN
       dbo.dim_Procedure_AvgPrice   pav  ON pco.a_ID_ProcedureGrouping = pav.a_ID_ProcedureGrouping
 GROUP BY
       te.a_ID_Treatment,
       YEAR(te.TreatmentEventStartDate),
       pca.a_IDPK_ProcedureCategory,
       te.a_ID_TreatmentEventStatus
HAVING (YEAR(te.TreatmentEventStartDate) = YEAR(GETDATE())
        OR YEAR(te.TreatmentEventStartDate) = YEAR(GETDATE()) - 1)
       AND (pca.a_IDPK_ProcedureCategory = 1)
       AND (te.a_ID_TreatmentEventStatus = 3);

When I add in other columns this is an example the result;
a_ID_Treatment     a__IDPK_TreatmentEvent     Code_Insurance_1     Max_AvgPrice
454646             432023                     455                  1030
454646             431168                     194                  719

What I need to get is just the first line with the AvgPrice of 1030, but I need to see the other columns too that apply to that line.
Anyone have any suggestions?
Thanks

I'm guessing when you're adding the other columns, you are changing the GROUP BY as the columns in the SELECT must be included. That's going to change your results.
Instead, I do the joining and grouping necessary to get the results I wanted, in your case the average, in a sub query and join to that.
Try this:
SELECT
    Max_AvgPrice,
    te.a__IDPK_TreatmentEvent,
    te.Code_Insurance_1
FROM
    (
        SELECT
            Max_AvgPrice = MAX(pav.AvgPrice),
            te.a_ID_Treatment
        FROM
            dbo.TreatmentEvent           te
        LEFT JOIN
            dbo.dim_Procedure_Codes      pco ON te.Code_Insurance_1 = pco.a_IDPK_ProcedureCode
        LEFT JOIN
            dbo.dim_Procedure_Category   pca ON pco.a_ID_ProcedureCategory = pca.a_IDPK_ProcedureCategory
        LEFT JOIN
            dbo.dim_Procedure_AvgPrice   pav  ON pco.a_ID_ProcedureGrouping = pav.a_ID_ProcedureGrouping
        GROUP BY
            te.a_ID_Treatment,
            YEAR(te.TreatmentEventStartDate),
            pca.a_IDPK_ProcedureCategory,
            te.a_ID_TreatmentEventStatus
        HAVING
            (
                YEAR(te.TreatmentEventStartDate) = YEAR(GETDATE())
                OR YEAR(te.TreatmentEventStartDate) = YEAR(GETDATE()) - 1
            )
            AND (pca.a_IDPK_ProcedureCategory = 1)
            AND (te.a_ID_TreatmentEventStatus = 3)
    ) AS subquery
    INNER JOIN dbo.TreatmentEvent AS te ON subquery.a_ID_Treatment = te.a_ID_Treatment


Effective way of accumulating index fragmentation statisitcs Please help me figure out a way to gather fragmentation statistics of all tables in my DB on a daily basis.

The problem is that scripts like this one:

SELECT OBJECT_NAME( ind.OBJECT_ID )
AS TableName,
       ind.name
AS IndexName,
       indexstats.index_type_desc
AS IndexType,
       indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID( ), NULL, NULL, NULL, NULL) indexstats
INNER JOIN
sys.indexes ind
ON ind.object_id=indexstats.object_id
   AND ind.index_id=indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent>30
ORDER BY indexstats.avg_fragmentation_in_percent DESC;

will work forever to complete as tables in our DB are <=300GB.
My goal is to regularly gather statistics on fragmentation to implement proper fillfactor for indexes based on how heavily they get fragmented over time according to this article:

5 Things About Fillfactor

I fully expect down-votes for this because my opinion doesn't fit convention in the SQL community, but I don't think you should be focusing on fragmentation levels nor should you be adjusting the fillfactor unless absolutely necessary.

The definition of Index Fragmentation is as follows:

  The logical ordering [of pages within an index], based on the key value, does not match the physical ordering inside the data file.

Ok, so what does that really mean?  It just means that you have an ordering mismatch between how the values are stored in the Index Tree and how they are stored on disk.  Low Index fragmentation does NOT mean data is stored contiguously or even on the same drive within a drive array.

How does Index Fragmentation hurt you?  Really, through my investigation, the only downside to Index Fragmentation that I've been able to identify is that read-ahead operations are less efficient (e.g. more are performed) against an index that is fragmented (in regards to Seeks and Range Scans operations) as opposed to said index being absent of fragmentation.  Read-Ahead operations only happen when you pull an index/table off of disk.  If you are able to maximize the time that the index remains cached, you will see less and less read-aheads negatively affect performance because you won't constantly be going back to the disk.  Additionally, Index Scan operations (as opposed to Index Seeks or Range Scans) are less troublesome because the entire index/table is coming off of disk any way.

Why does most documentation attribute High Index Fragmentation with Poor Performance?  The answer is that often times, High Index Fragmentation creates unnecessary white-space within an index due to page splits and other operations.  This white-space is the real detriment to performance because it requires more pages be read into memory to get less data.  More white space = More I/O requests = less performance.

As Kendra's article alludes to, even the BOL article makes mention that setting FILLFACTOR too low will cause performance issues.  This is because of the exact same reason.  More reads for less data is less performant.  Setting FILLFACTOR to anything other than 0 when necessary is forcefully adding whitespace to your indexes/tables.  Let's do some basic math here and see why this isn't a good blanket-recommendation against your database.

Let's say you have an index that is 100GB in a completely unfragmented state with the default FILLFACTOR set.  You set FILLFACTOR to 90 and rebuild the index.  The index will now become 111GB (100/90 * 100), which is an increase by 11%.  This also means that you'll have to read 11% more pages to get the same amount of data off of disk.  Apply this system-wide, and you've just increase read operations by 11% across the system.  The other detriment to white space is that it is not only stored on disk, but it's also stored when the page resides in memory.  That means you're reducing your memory footprint by 11% just to help "prevent" bad page splits.  Really, are page-splits so bad that you need to reduce your memory footprint and increase the amount of read operations off of disk?  This setting is a negative double-whammy.

There are definitely scenarios where adjusting FILLFACTOR is needed and in the best interest of your database, but to set it across the board in a blanket fashion is not a good idea in my opinion.  I'm not saying you shouldn't adjust FILLFACTOR, rather  you should only adjust it when other options are less desirable.

So, after my rambling, what I suggest you should be reviewing instead of index fragmentation is the avg_page_space_used_in_percent field in the sys.dm_db_index_physical_stats DMV.  If you see that creep up past 10% or higher, a reorg/rebuild would be worthwhile because this is the only way to compress white space out of an index.  Often times, I see that this percentage of white space hits when the index is significantly fragmented, generally at 60% or more.  Also, because this value doesn't creep nearly as quickly as index fragmentation, this may allow you to reduce the frequency you perform index maintenance operations.


localtimestamp or curent_timestamp for default value In Oracle-12c I have some columns that I am converting from timestamp with time zone to timestamp with local time zone. Some of those same columns also currently have a default value of CURRENT_TIMESTAMP. Should I change the default values to use the LOCALTIMESTAMP function instead? The columns seem to populate fine if I just leave the CURRENT_TIMESTAMP default values. Is there any practical reasons or gotchas for using one over the other in this case. I have been combing the docs but can't find anything.

You write "I don't need the time zone information", however datatype TIMESTAMP WITH LOCAL TIME ZONE has time zone information stored.

For a TIMESTAMP WITH LOCAL TIME ZONE column I don't see any difference between CURRENT_TIMESTAMP and LOCALTIMESTAMP either of them should work in the same way.

CURRENT_TIMESTAMP returns current data and time as of datatype TIMESTAMP WITH TIME ZONE, whereas LOCALTIMESTAMP returns a LOCALTIMESTAMP value. Both of them have to be implicitly converted to a TIMESTAMP WITH LOCAL TIME ZONE datatype.


Profiling feature for SQL Server 2012 I'm using SQL Server 2012 to develop php/web applications.

On my development machine, I have SQL Server also.
The php codebase is version controlled with git.

The various applications we have (and there's more than 30) are in the process of being updated. As this happens, the database structure sometimes changes, and the data (even on my local content) changes a lot too.

Even as we develop new code, we need to work fixes and amends into the old code as well.
We use branches to develop this within git and that works well.

However: maintaining a database set to match these is a serious hassle.
If a project connects to three databases, and for example, there are three branches, the development becomes confusing, as well as a hassle to set up.

So - on to my questions:

Is there a database version of "profiling"?
Is it possible to have parallel versions of the databases that can be switched to active/inactive - just as switching a branch separates what amounts to the same code?
Switching a branch in git is a matter of seconds. Is there a tool to do the same within parallel versions of the current db structure?

This would save me some serious headaches (and the associated dangers of leaving a db-config pointing to the wrong database).

Any advice or tips to pointers to approach this problem would be appreciated.

If you are using Developer or Enterprise Edition, you can use Database Snapshots to get you most of the way.  Basically, this allows you to make virtual "copies" of your database and apply your object changes.  You will still need to change your connection string, but it could give you an easy to maintain alternative for standing up your alternate database branches.

Regardless of how you approach this, you will need to have some different database name and/or instance name for your database.  Data and databases are stateful in nature, so simply swapping structures in and out isn't an option.  After all, you can't just turn off columns and the data they contain.  You have some options for disabling objects, like indexes and triggers.  But beyond that, you can't just switch data "off".


SQL Server Merge vs MySQL Insert On Duplicate Key performance comparison I've been searching a lot for this kind of comparison or some sort of performance balance.

What I do know so far is this:

MERGE is T-SQL command that is already implemented on SQL Server. As far as I know, it does perform very well, since it uses "kind of like" INNER JOIN hash mapping for inserting and updating, but I've been having some issues when deleting on the same MERGE statement as well when using CLUSTERED INDEX. Besides that, it's very fast and make let me do some comparison clauses before updating or inserting something, so it's very flexible for me.
In SQL Server some specific scenarios would rather use UPDATE and INSERT as separate statements and I wouldn't argue that. In my short experience, I would use MERGE by default as a standard for my code.
Now as a DBA, I'm facing a new challenge, I have to manage MySQL servers as well, so I ended up looking for similar MERGE behavior in MySQL to improve performance of the queries. So far, I found nothing alike but INSERT ... ON DUPLICATE KEY ... UPDATE. Still, I have some performance questions, since I'm not sure how it behaves with the server, how does it works and if optional pair of statements would work better or faster than that.
Looking around in MySQL, I found UPDATE + INSERT IGNORE, UPDATE + INSERT, INSERT ... REPLACE, and so on..
MySQL documentation is a bit confusing when trying to determinate if I could use some other clauses ex. in MERGE statement I could use AND (TARGET.COLUMN_X > 'VALUE'):

MERGE _TABLE A_ AS TARGET
WITH _TABLE B_ AS SOURCE
ON (TARGET.KEY = SOURCE.KEY)
WHEN MATCHED AND (TARGET.COLUMN_X > 'VALUE')
  UPDATE
    TARGET.COLUMN_A = SOURCE.COLUMN_A
...

I don't find how to do this on MySQL.

*CONSIDERATIONS

I have to manage this to achieve better time result and performance friendly.

What I have as setting:

PERCONA MySQL

txt file that need to be uploaded to a table every moth with new data and some changes (this is why I'm looking for a MERGE like statement)
InnoDB MySQL Engine
Relational Database tables so I can't delete or truncate the target table because all of them are related.

IODKU is the best of the options in MySQL.  It works something like this:

Use some UNIQUE key (possibly the clustered, unique, PRIMARY KEY) to locate the row to modify.
If no such row, perform an INSERT.
If such a row, perform an UPDATE.

You can't get faster than that.

Note that step 1 will cache everything that is needed for #2 or #3 in the buffer_pool.  (Well, OK, secondary indexes are handled in a 'delayed' way via the "Change Buffer".)

Further note that the statement is atomic, whereas your 2-statement alternatives need to be in a transaction.

Keep in mind that REPLACE is DELETE (0, 1, or possibly multiple rows, if you have multiple unique keys), then INSERT.  Note that the AUTO_INCREMENT (if used) value is thrown away and a new one is created.

As for that messy query, it can probably be done with GREATER() and/or IF() and/or VALUES().  Assuming that you are trying to merge multiple rows in, you need the IODKU+SELECT syntax:

INSERT INTO Target (key, a)
    ON DUPLICATE KEY UPDATE
        SET a = VALUES(a)
    SELECT Target.key, Target.a
        FROM Target
        JOIN Source ON Source.key = Target.key
        WHERE Target.x > 'value';

Since I don't know what MERGE does, I can't give you all the details.


.NET SQL Server Authentication Schema issue I am currently working on a project for a client. The application is written in ASP.NET C#. The Database used is SQL Server 2008. The project has been to migrate the old Oracle database to a new SQL Server database so it connects to the .NET code and works correctly. We used Microsoft Microsoft SQL Server Migration Assistant (SSMA) to convert the Oracle database across to SQL server. This worked for the most part, however we did have to make some changed to the stored procedures and some functions.

The problem is that now when i try and run the .NET code it will not pick up any of the stored procedures or functions as they now have a schema prefix and the .NET code does not pick this up.

The .NET code connects to the SQL Server database using Windowws Authentication and therefore i am unable to add a default schema to the user or login within SQL Server as it is a group.

Is there any way i can get around this?

If you would like more information i will be happy to provide it.

Thanks

Connection string

<add key="DatabaseConnectionString" value="Data Source=DATASOURCE;Initial Catalog=DATABASE;Integrated Security=true;"/>

Stored Procedure Call

// Open a connection to the database
            SqlConnection databaseConnection = GetDatabaseConnection();

            // Initialise the SQL command object
            SqlCommand cmd = new SqlCommand("PKG_PACKAGE_NAME$PR_PROCEDURE_NAME",
               databaseConnection);
            cmd.CommandType = CommandType.StoredProcedure;

Boldonglen

I'm not sure what is preventing you assigning a default schema to the user. You can simply use:

ALTER USER [NT AUTHORITY\Authenticated Users]
WITH DEFAULT_SCHEMA = [Production]

Obviously replacing the user/schema names as appropriate.

However, it would be best if you could update the code to use a schema prefix - some more reading on that is here (functional reasons) and here (performance reasons).


Unable to connect to pluggable database in Oracle 12c I have oracle database Oracle Database 12c Enterprise Edition 12.2.0.1.0, and I created a pluggable database named DEVDB to use it in web application development, I created it using the Database Configuration Assistant, during the creation process I created a user/password to connect to that DB, then I modified the file tnsnames.ora adding this:

 DEVDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = devdb)
    )
  )

Using DBeaver or SQL developer I can connect to the PDB successfully as you can see in the image blow:

but when I try to connect to the DB trying this from sqlplus command line:

sql> conn user/password@DEVDB

I get the following error message:

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Warning: You are no longer connected to ORACLE.

This is show pdbs command result:

   CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        READ WRITE NO
         4 DEVDB                          READ WRITE NO

I reloaded lsnrctl many times and tried again but I get the same error.

I would like to be able to connect the PDB from sqlplus command line and understand the cause of that error

Any help please ??

Is the pluggable database (PDB) online?

Connect to the container database where you attached the PDB. Issue this SQL command: show pdbs; This should list all of your Pluggable Databases (including the seed PDB) and what state they are in.

You can start the DevDB database via this command: alter pluggable database DEVDB open;

If that works and the database opens and comes online, you can tell Oracle to "remember" this state via:  alter pluggable database DEVDB save state;

If, however, the database does not come online via the alter...open command, check select * from PDB_PLUG_IN_VIOLATIONS; for possible problems.

From your edits, I see that the PDB is up and available. Try connecting to the DEVDB from another computer?

When you connect to the container, you can then switch your context to the PDB via alter session set container = DEVDB;

In my Oracle 12 environments, I do not have the PDBs listed explicitly in the server's TNSNames file (just the container DB). On client PCs, I then list the PDB in the TNSNAMES file.

On the server, I use the ORACLE_SID environment variable and do not use the @service, so: Set ORACLE_SID=DEVDB and then sqlplus user/pwd
(But that is in a Windows environment.)


Update postgres row where condition is from another table I want to update a password field from a given value that exists from another table.

user_info:

--------------------------------------------
| user_login | password                    |
|------------|-----------------------------|
| ad         | $2a$12$oGBdoD....ia9mk25OHu |
--------------------------------------------

user_contacts

-------------------------
| user_login | email    |
|------------|----------|
| ad         | ad@...com|
-------------------------

I would like to change the password by identifying the user_login using the associated email address. So, something like:

UPDATE user_info SET password = '$2a$11$fwea...IEI' WHERE user_contacts.email = 'ad@...com';

Would anyone have an idea how to go about this? I've been trying to search for an answer online, but I've either not been able to find anything relating to this.

Cheers!

I believe this can be solved relatively quickly, by adding a FROM clause to your UPDATE, and adding an additional equality condition, namely, adding user_info.user_login = user_contacts.user_login.

UPDATE user_info SET password = '$2a$11$fwea...IEI'
FROM user_contacts
WHERE user_info.user_login = user_contacts.user_login
AND user_contacts.email = 'ad@...com';

This will almost certainly do what you are asking.

Refer to the PostgreSQL Documentation on UPDATE for more info.


SQL Server -- see when user/ login was last modified? And other meta-data I know you can see when tables or views were last modifed in sys.tables or sys.views in SQL Server.

It does not appear there is a table like this for users or logins --- or is there?

Basically, a user is having permissions issues and --- I can see their current permissions --- but I would like to know when it was last "changed" and possibly by "whom" --- is that possible retroactively?

You can have a look at

Server audit logging
C2 Audits

When working at banks or some Government agencies one Normally needs some way to proof who has accessed the database as well as the reports that was generated.

What happens is one always has to log the machine and user name the user used to accessed the data. one can record this by caling SUSER_SNAME() and Host_Name() in your procedures and logging this data along with the parameters used so that one can verse engineer who has accessed/ seen what data.


Unable to restore TDE enabled database when MAXTRANSFERSIZE and CHECKSUM is used Update: @AmitBanerjee - Senior Program Manager for the Microsoft SQL Server Product Group confirmed that MS will look into the issue as it is a defect.

Has anyone encountered issue restoring backups taken on SQL Server 2016 with TDE enabled and using MAXTRANSFERSIZE > 65536 (in my case, I have chosen 65537 so that I can compress TDE database) and CHECKSUM ?

Below is a repro:

--- create database
create database test_restore
go
-- create table
create table test_kin (fname char(10))
go
-- Enable TDE

use master
GO
CREATE CERTIFICATE test_restore WITH SUBJECT = 'test_restore_cert'
GO
SELECT name, pvt_key_encryption_type_desc, * FROM sys.certificates WHERE name = 'test_restore'
GO
use test_restore
go
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE test_restore
GO
alter database test_restore set encryption ON

Take full copy only backup .. do it twice ..

backup database test_restore
to disk = 'D:\temporary-short-term\test_restore_KIN_test_restore_1.bak' -- change as per your location !!
with init, stats =10  -- overwrite ..using INIT !!
, maxtransfersize = 65537
, compression
,CHECKSUM

Now do a verifyonly ...

restore verifyonly from disk = 'D:\temporary-short-term\test_restore_KIN_test_restore_1.bak'

Error Message :

  Msg 3241, Level 16, State 40, Line 11
  The media family on device 'D:\temporary-short-term\test_restore_KIN_test_restore_1.bak' is incorrectly formed. SQL Server cannot process this media family.
  Msg 3013, Level 16, State 1, Line 11
  VERIFY DATABASE is terminating abnormally.

Results (1 = ON, 0 = OFF) with different combinations :

+-------------------------+-------------+----------+--------+
| MAXTRANSFERSIZE (65537) | COMPRESSION | CHECKSUM | RESULT |
+-------------------------+-------------+----------+--------+
|                       1 |           1 |        1 | FAIL   |
|                       1 |           1 |        0 | PASS   |
|                       1 |           0 |        1 | FAIL   |
|                       0 |           0 |        0 | PASS   |
|                       0 |           1 |        1 | PASS   |
|                       0 |           1 |        0 | PASS   |
+-------------------------+-------------+----------+--------+

The issue happens on:

  Microsoft SQL Server 2016 (RTM-CU1) (KB3164674) - 13.0.2149.0 (X64)   Jul 11 2016 22:05:22   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: )

I was able to reproduce your problem.

Adding FORMAT to the BACKUP command solved it for me.

While I can't seem to find concrete documentation, it's my opinion that this is related to the fact that INIT retains the existing media header on the backup set while FORMAT creates a new media header.

I'm still researching this issue and if I find additional information, I will update this answer.


Junction table Trigger after update in another table I'm just trying to create a trigger that performs an update in a junction table.
In principle, I have three tables with Many-to-Many relationship.
Table a:
a_id, a_is_active
Table b:
b_id, b_is_active
Table ab:
ab_id, a_id, b_id, ab_is_active

What I want is that the ab_is_active needs to be actualized when a_is_active or b_is_active is updated, so ab_is_active should be like this (pseudo code):
ab_is_active = a_is_acvite && b_is_acvite where a_id = a.a_id and b_id = b.b_id
and that must be carried out after each is_active update in Table a or in Table b:
I have tried the following, but it does not work (nothing is updated in ab):
CREATE OR REPLACE FUNCTION is_active_changes()
    returns trigger
    language plpgsql AS
$BODY$
DECLARE
    b_is_active boolean;
    r            b%ROWTYPE;
BEGIN
    for r in select *
             into b_is_active
             from ab
                      join b _b on ab.b_id= _b.b_id
                      join a _a on ab.a_id= _a.a_id
        LOOP
                update ab set ab_is_active = r.a_is_active and r.b_is_active where b_id = r.b_id and a_id = r.a_id;
        END LOOP;
END;
$BODY$;

create trigger is_active_trigger
    after update
    on a
    for each row
execute procedure is_active_changes();

Can someone tell me what I do wrong or suggest a better solution?

Your problem is , that you made  some small errors
in the for loop you need to select those columns that fit the rowtype
I added as last a Version that is better as it only update one row and not all where b.id and a.id are present

CREATE Table a
("a_id" INT, "a_is_active" Boolean)

CREATE Table b
("b_id" INT, "b_is_active" Boolean)

CREATE Table ab
("ab_id" INT , "a_id" int, "b_id" int, "ab_is_active" Boolean)

INSERT INTO a VALUES (1,TRUE)

INSERT INTO B VALUES (1,TRUE)

INSERT INTO ab VALUES (1,1,1,FALSE)

DO
$BODY$
DECLARE
    b_is_active boolean;
    r            ab%ROWTYPE;
BEGIN
    for r in select ab.ab_id, ab.b_id,ab.a_id,_a.a_is_active AND _b.b_is_active AS ab_is_active
             from ab
                      join b _b on ab.b_id= _b.b_id
                      join a _a on ab.a_id= _a.a_id
        LOOP
                update ab set ab_is_active = r.ab_is_active
                where b_id = r.b_id AND a_id = r.a_id;
        END LOOP;
END;
$BODY$;

✓

SELECT * FROM ab

ab_id | a_id | b_id | ab_is_active
----: | ---: | ---: | :-----------
    1 |    1 |    1 | t

DO
$BODY$
DECLARE
    b_is_active boolean;
    r            ab%ROWTYPE;
BEGIN
    for r in select ab.ab_id, ab.b_id,ab.a_id,_a.a_is_active AND _b.b_is_active AS ab_is_active
             from ab
                      join b _b on ab.b_id= _b.b_id
                      join a _a on ab.a_id= _a.a_id
        LOOP
                update ab set ab_is_active = r.ab_is_active
                where ab_id = r.ab_id;
        END LOOP;
END;
$BODY$;

✓

db<>fiddle here


SSMS gives error if column name is "MERGE" where as other keywords works IN SSMS create table if we include sql keywords in column name then it will be wrapped around square brackets "[]" automatically and the table gets created without any error generally.

But if we include some keywords such as merge then SSMS will not insert square brackets around it and while saving table error is shown as

'Table_2' table
- Unable to create table.
Incorrect syntax near the keyword 'MERGE'.
Incorrect syntax near the keyword 'NULL'.

This is yet another flaky bug in yet another inconsistent and unreliable visual designer in SSMS. My suggestions, in this order:

Stop using the visual designers. Learn CREATE TABLE and other DDL syntax - not only does this break your dependence on terrible tools, it is also much easier to place these in source control, send them to colleagues, etc. When you are relying on pointing and clicking in a UI, your documentation of what you did will have to resort to Camtasia or other screen recording tools.
Stop using reserved words for column names.
If you must use keywords, put the brackets around them yourself.

I filed a bug on Connect, but don't hold your breath. Many of these buggy components of SSMS have been barely touched in years, and many usability bugs continue to get ignored unless they cause real problems (like data loss).


Oracle 9i to 12c Replication I have an old Oracle Forms solution that accesses a 9i database and I have been asked if it's possible to replicate to an Oracle 12c database server, with the intention of using Fusion until a new application is built.

Is it possible to replicate between these two versions? Or is there another way to achieve an active-active replica between the two servers? i.e. log shipping etc.

Please consult the Oracle Update and Migration Guide

There is no direct upgrade path, for your data a export/import might work.
Your old Forms application will not work, Oracle 10.1 was the last version that could run old 6i Client/Server Forms,IIRC


I might derive the progress table into two pieces... one with the zeros (complete) and one with the ones (not yet done), and join them together by the case number.

Then I'd join those with the predecessor table to find rows where the task is in the ones table and it's predecessor is in the zeros table.

Once I've identified those tasks, I'd join with the main table to get the task's proper name.

That might look something like this:

SELECT
    t.name,
    p1.id_progress
FROM
    tasks_predecessor tp
INNER JOIN
    (SELECT task_id,case,id FROM progress where current = 0) p0
    ON p0.task_id = tp.predecessor
INNER JOIN
    (SELECT task_id,case,id FROM progress where current = 1) p1
    ON p1.task_id = tp.task
    AND p0.case = p1.case
INNER JOIN
    tasks t
    ON t.id = tp.task

Then, if you wanted a particular case, you could isolate it with:

WHERE p0.case = 199

Give this a try, but don't hold me to it... I'm not in front of a console where I can test this, and I may have missed something obvious or simple.


Is this query supposed to be slow? Select * from (select tmp.userid userid, tmp.username ,tmp.likescore+tmp.commentscore+tmp.sharescore+tmp.tagscore+tmp.userscore+tmp.lsharescore+tmp.friendscore as score from
    (
    SELECT u.userid, u.username,
    (select 10*count(1) from b_likes l where l.userid = u.userid) likescore,
    (select 15*count(1) from b_comments c where c.userid = u.userid) commentscore,
    (select 25*count(1) from b_page_shares s where s.userid = u.userid  ) sharescore ,
    (select 20*count(1) from b_tags t where t.userid = u.userid ) tagscore,
    (select count(1)*10 score from (
                            Select Distinct friendid,userid
            from b_friends f ) a
            where a.userid=u.userid ) friendscore,
    (select 25*count(1) from b_shares ls where ls.userid = u.userid ) lsharescore,
    (SELECT (100 * count(rightflag))
            FROM `b_scores` s
            WHERE  rightflag='1'
            and level_no!=''
            and u.userid=s.userid) userscore
    FROM `b_users_data` u) tmp) tmp2 where tmp2.score != 0
    order by tmp2.score desc, tmp2.username asc  limit 10;

The slow query log says it took 72 seconds and 12.7k rows were examined.
Apart from id, no other columns are indexed in any table.

Yes, it is supposed to be slow. Why?

There are multiple subqueries:

3 levels of nested derived tables
7 inline correlated subqueries that each join to a different table, using the userid which as you say is not indexed. One of the subqueries has a further nested derived table in its FROM clause.

There are WHERE conditios on un-indexed columns
There is an ORDER BY clause - it always adds one more operation
There is a DISTINCT quantifier - filtering out duplicates is a costly operation
There are some calculations being made

It really looks very bizarre. I guess it can be rewritten to a neat form using JOINs and it would run much faster. You would have to post the database structure and the result you want to achieve.


Should a table be partitioned before or after an INSERT? I have a fairly simple question that is proving hard to search for.

I need to create a table that will be partitioned and into which (lots of) data will be loaded.

Is it more efficient to create the table with the partitions or add the partitions after I have INSERTed the data?

ADDITION: To make sure I understand the complete picture, if I was to truncate this table to then load (lots of) data again, should I drop the partitions and then add after the load?

ADDTION #2: If I can INSERT the data in the order of the partitions, I assume that would be faster than not?

You can't partition an existing non-partitioned table.  So if you want a partitioned table, you would need to create a partitioned table before inserting any data.

Assuming that you are not using interval partitioning (in which case you're telling Oracle how to automatically add new partitions based on the data), you would almost certainly want to create the appropriate partitions in advance.  If you are range partitioning the table, you could, in theory, create a MAXVALUE partition for the table, load all the data, and then split the MAXVALUE partition into the individual partitions that you want.  But that would involve moving all the data around multiple times which would be much less efficient than simply loading the data into the appropriate partition from the outset.

Inserting the data "in the order of the partitions" probably won't make a difference from a performance perspective.  Oracle still has to determine for each row which partition to insert it into.  Assuming your indexes are local, it may well be more efficient to load a staging table whose structure matches the structure of a partition, create the appropriate indexes on the staging table, and then doing a partition exchange to exchange an empty partition with the staging table.


Why does OBJECT_ID return NULL in a computed column? I'm trying to produce a usage report for some stored procedures in my database. The view view sys.dm_exec_procedure_stats contains some of what I need, so I decided to start here.

SQL Server keys procedure metadata by object ID. I know only the procedure names, so first I have to map the names to IDs using the OBJECT_ID function.

There are several procedures to check, so I want to put their names in a temp table before processing them.

I thought it would be cute to get the object id from a computed column in the temp table, so I created a table like this:

USE msdb;
GO

CREATE TABLE #procs (
  name SYSNAME,
  [object_id] AS OBJECT_ID(name)
);

INSERT INTO #procs (name) VALUES ('dbo.sp_send_dbmail');
INSERT INTO #procs (name) VALUES ('dbo.sp_add_job');
INSERT INTO #procs (name) VALUES ('dbo.sp_start_job');

With that table in place, the obvious way to get the execution counts looked like this:

SELECT #procs.name, execution_count
FROM sys.dm_exec_procedure_stats AS procstats
INNER JOIN #procs ON #procs.[object_id] = procstats.[object_id]
ORDER BY execution_count DESC;

But it returns an empty result set!

This query shows why the result set is empty:

SELECT name, [object_id]
FROM #procs;

The object_id column is NULL, so an inner join will produce no rows:

name                 object_id
-------------------- -----------
dbo.sp_send_dbmail   NULL
dbo.sp_add_job       NULL
dbo.sp_start_job     NULL

It's not that computed columns are broken for temp tables. If I add a computed column to reverse the name, it produces the correct value when I select from it:

ALTER TABLE #procs ADD reverse_name AS REVERSE(name);

SELECT name, [object_id], reverse_name
FROM #procs;

Result:

name                 object_id   reverse_name
-------------------- ----------- ---------------------
dbo.sp_send_dbmail   NULL        liambd_dnes_ps.obd
dbo.sp_add_job       NULL        boj_dda_ps.obd
dbo.sp_start_job     NULL        boj_trats_ps.obd

Why does OBJECT_ID return NULL here?

A metadata function mentioned in a computed column in a #temp table is going to reference tempdb.sys.objects, not yourdatabase.sys.objects, since the table is created in tempdb. Try adding the database prefix to the insert, and that will make OBJECT_ID go find it in the right place:

CREATE TABLE #procs (
  name SYSNAME,
  [object_id] AS OBJECT_ID(name)
);

INSERT INTO #procs (name) VALUES ('dbo.sp_send_dbmail');
INSERT INTO #procs (name) VALUES ('msdb.dbo.sp_send_dbmail');

SELECT name, [object_id] FROM #procs;

Results:

dbo.sp_send_dbmail          NULL
msdb.dbo.sp_send_dbmail     283148054


Transaction log is growing very fast One of our SQL Server 2014 database transaction logs is growing very fast and I cannot find the root cause for this.

Database in Simple recovery mode. During full backup the log file grows to its limits (800GB) and backup fails.

I checked the transactions during the backup and did not find anything strange.

All transactions are our regular loading and no maintenance jobs (index rebuilds etc.)

I am aware of that backup keep the log from reuse but this mean 800GB changes during the backup period and this does not make any sense.

I had compared this DB to similar DB size, loading and backup duration.
The second DB Log is not growing more than 400GB.

DB size is 7TB. Backup time is ~20H. The only time log is growing is during backup. Log_reuse shows correct Backup_Restore. No long running transactions.

The problem is the "speed" of log growth - compared to similar sites with same load and same backup duration, the log is 4 time less.

While taking a full backup of the database, SQL Server is unable to reuse the transaction log for the duration of the backup, even if the database is in SIMPLE recovery. If your database is very busy, or backups take a long period of time, this can require a large transaction log,  even if the database is in SIMPLE recovery.

 Why does SQL Server need to do this?

Think of the database as a bookshelf containing a set of encyclopedias, and each table is a book in that set. SQL Server is the bibrarian. The librarian needs to make a copy of every book in the set to get a snapshot of the set.

The librarian (SQL Server) starts copying books (tables) at one end of the shelf (database), and makes it's way to the far end. This takes time. If the data within the books is changing (ie, database transactions), getting a consistent (that's the "C" in ACID) snapshot becomes a challenge.

Consider this timeline:

The librarian copies book A.
The librarian copies book B.
Books A & Z are updated.
The librarian copies book C.
The librarian copies book D.
Books C, X, Y, Z are updated.
The librarian copies books E-Z. The backup is complete.

The librarian's backup is not consistent. It has some of the old data (Books A & C, and some of the updated data (Books X,Y,Z). In order for the data to be consistent, the librarian could keep track of what changed in books A & C, then roll forward those changes on her backup copy.

It might be OK for the librarian to have an inconsistent backup, but it's not ok for SQL Server to have an inconsistent backup. SQL Server's backups must be consistent to a single point in time.

How does SQL Server do this?

When SQL Server begins backing up data pages, it also starts keeping track of transactions, via the transaction log. After it has backed up the last data page, it then also backs up all of the transactions that occurred during the data backup. Upon restore, it will then roll those transactions forward or backward, as necessary, to ensure a consistent image is restored.

Backing up busy databases in Simple Recovery

Based on OP's comments, the database in question is 7TB, and takes 20 hours to back up. That means that the transaction log for this database must be large enough to accomodate 20 hours worth of transactions. Depending on the amount and type of activity, the amount of transaction log needed could very quite a bit.

To reduce the amount of transaction log needed, you can either try to make your backup go faster, or take your backups during a time of reduced write activity, or both.


Tempdb files Solid state on Same drive? We currently have tempdb (8 files) on solid state drive.

We know that primary mdf and transaction log ndf are recommended to be on different drives, to reduce contention. Should tempdb files be on different drives to have consistent principle, or can they all be on the same drive?

The following article stated they are all on same drive, however looking for a background technical explanation.
Recommendations for more than one tempdb file

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'G:\tempdb2.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'G:\tempdb3.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
....

There are several factors that comes to play here:

How much I/O in general can a device (your SSD in this case) handle? Say that out of 100% in total for tempdb, the log (ldf) caters for 30%. Buying a new SSD and put the ldf on there will give you more IOPS and those 30% for the ldf won't be taken from the drive where the mdf/ndf is.

The "separate the log" made a lot of sense when we had single disks and there was a huge performance difference between sequential and random I/O. A transaction has to wait for the ldf records to be written to the disks (hardened). The ldf is (almost) only sequential I/O. Isolating ldf to a separate disk means the disk is in position for the next write operations meaning sequential I/O (no moving of the disk arm). How big difference is there between sequential and random I/O for an SSD disk? I don't know, but probably minuscule. So, this aspect is probably not relevant for modern disk subsystems.

So, IMO, it boils down to capacity. Can your SSD handle the I/O load with reasonable response time? If not, then you want more capacity - more SSD drives.


SQL Server Builtin Logins Migration I am in the process of scripting existing SQL Server 2008R2 logins for migration to a new server instance.

I see that the existing logins contain the below...

Windows Groups:

[BUILTIN\Administrators]

[BUILTIN\Users]

[BUILTIN\Backup Operators]

Windows Users:

[NT SERVICE\MSSQLSERVER]

[NT SERVICE\SQLSERVERAGENT]

Should these logins be used at all? And do they get created by default on installation?

The following logins will be created on installation. They are containers for the accounts that the SQL Server Database Engine & Agent run under.

 [NT SERVICE\MSSQLSERVER]

 [NT SERVICE\SQLSERVERAGENT]

The other accounts are groups that have been manually added into your SQL instance.

BUILTIN\Administrators was installed by default in SQL Server 2005 but not in SQL Server 2008 R2.

I'd look at the permissions on the existing server to see what accounts are using these groups and then determine if they are needed.


Database explorer disabled in SQL Assistant I am a new Teradata SQL assistant user. I have used SQL Server Management Studio before.

In the View option, I am unable to toggle the Database Explorer. Please let me know how I can access the databases I add on my SQL assistant.

once you are connected to the DB on the left side of the window you will see the DB tree hierarchy.

Let me know if this helps.


SQL Server: Slow restore from SQL 2014 DB, but Fast from SQL 2016 DB via SSMS We are chasing an odd problem.

We see a significant perf delta when restoring via SSMS, but do NOT see the delta when restoring via TSQL.

When we restore, using SSMS, on SQL 2016 from a DB created on a SQL 2014 instance, the restore is really really slow.

But, on the same SQL 2016 DB server, when we restore from a SQL 2016 instance, again in SSMS, the restore is super fast.

The SQL 2016 system is a super fast system, all-SSD, etc. It should always be fast.

To address, we applied all OS and SQL patches and updates to the target system. Problem was not changed.

Why would restoring a SQL 2014 DB be so slow?

The restore from 2014 is an upgrade, not just a restore.  You should actually see this in the messages window, assuming you are using T-SQL to do the restore.   You don't mention what Fast and Slow are, or the differences, such as 5 mins vs. 5 hours, so we can't add much to this.


Better parallelization than a sequence container in SSIS 2008? We currently have a process that has to process hundreds of thousands of rows an hour.  For valid reasons, it has to call a stored procedure for each row. We're using a SSIS package (SQL Server 2008) to run this in parallel.  If there's 400k rows to do, we divide that by 10, then tell each SQL Task within the container (we have 10 tasks, so that each one basically monopolizes a CPU) to do rows 1-40000, 40001-80000, etc.  Naturally, not all rows are the same, so we frequently have 8 of the 10 SPIDs idle, waiting for the last 2 to finish, so that the job can start again and process the rows that have come in since it started processing.

There has GOT to be a better way to do this.  I can't move this to SSIS 2012/14 yet, and it's CPU bound so I can't run the SSIS package on a different box (plus, overhead would probably be nightmarish).  Also assume I'm not doing it in Service Broker.

One idea we'd had is to save each block of 1000 as a row to a table, then have multiple jobs running.  Each would process a block of 1000, then claim a new row (and thus the next 1000) when they finish.  No need for SSIS, either.  But I'm unsure how the overhead/development cost of doing that would compare to a simple design change in SSIS.

Thanks in advance!

Assuming I understand the situation, you have lots of source data to process but it all needs to go through a slow destination (stored procedure) then there's a native component, Balanced Data Distributor, that might be of interest.

As a row flows into a BDD, it can detect whether the downstream consumers of data are busy (whether there is backpressure). It's somewhat similar to a Multicast in that you can have multiple output paths but you won't have duplicate data flowing into them. Instead, it'll be using backpressure detection to know whether a worker is available. That should address the issue you're encountering with your manual segmentation of the problem.

It doesn't come preinstalled with SSIS so you do have to work through that but it is a MS supported component.


How do you select*from with multiple conditions? I'm trying to return the records of a table where three columns match up, but i'm getting no results in return. Any suggestions?

SELECT *
FROM Flights
WHERE Departure_IATA_Code = 'STN'
  AND Arrival_IATA_Code = 'DBN'
  AND Departure_Date = 2018-04-16

Create table statements

CREATE TABLE `Flights` (
 `Flight_Number` varchar(50) NOT NULL DEFAULT '',
 `Aircraft_ID` int(7) NOT NULL DEFAULT '0',
 `Departure_IATA_Code` varchar(3) NOT NULL DEFAULT '',
 `Departure_Date` date NOT NULL,
 `Arrival_IATA_Code` varchar(3) NOT NULL DEFAULT '',
 `Cost` int(7) NOT NULL,
 `Departure_Time` time NOT NULL,
 `Flight_Duration` time NOT NULL,
 PRIMARY KEY (`Flight_Number`,`Aircraft_ID`,`Departure_IATA_Code`,`Departure_Date`,`Arrival_IATA_Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Try it:

SELECT *
FROM Flights
WHERE Departure_IATA_Code = 'STN'
AND Arrival_IATA_Code = 'DBN'
AND Departure_Date = '2018-04-16';


Agent Job: Execute T-SQL on different server? While migrating SQL Server databases on updated hardware/software we separated the ETL server from the database server (referred this link).

We were moving our SSIS packages and agent jobs on ETL server. But the T-SQL steps in agent jobs need databases to be present on same server as well.

How do we proceed from here, do we create databases on the ETL server OR move SSIS packages only on ETL server and keep agent jobs on database server?

You can trigger a SSIS job from another server, no need to move your jobs.

So you can leave your SQL Agent jobs on the database server, deploy the SSIS packages to the new ETL server and then modify your job steps to start the SSIS packages running on the ETL server from your database server. You might need to modify the 'Run As' part depending on how you set up your permissions in between your 2 servers.


SQL Server blocking report with waitresource Database We recently had a number of unexpected blocks occur on one of our servers. We captured the blocked process report and they are showing the waitresource as waitresource="DATABASE: 12:0". Below is an example of one of the reports.

<blocked-process-report monitorLoop="45476">
 <blocked-process>
  <process id="process7db4d70c8" taskpriority="0" logused="10000" waitresource="DATABASE: 12:0 " waittime="24750" schedulerid="4" kpid="4556" status="suspended" spid="96" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="1900-01-01T00:00:00" lastbatchcompleted="1900-01-01T00:00:00" lastattention="1900-01-01T00:00:00" clientapp="App1" hostname="ServerMainFrame.domain.COM" hostpid="0" loginname="LoginA" isolationlevel="read committed (2)" xactid="0" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack />
   <inputbuf>
   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="suspended" waitresource="DATABASE: 12:0 " waittime="24998" spid="150" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="1900-01-01T00:00:00" lastbatchcompleted="1900-01-01T00:00:00" lastattention="1900-01-01T00:00:00" clientapp="Microsoft JDBC Driver for SQL Server" hostname="Server02.domain.com" hostpid="0" loginname="loginA" isolationlevel="read committed (2)" xactid="0" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack />
   <inputbuf>
   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

I was not even sure this could be a waitresource or sure what it means. Generally, the waitresource I see is a table, page, row, etc. The obvious would be that there is a lock on the database and nothing can use it, but not sure how that would happen. Lock escalation maybe? So my question is if someone could help shine some light on what this waitresource is and what might cause it.

The issue was fixed before I could look at the activity while it was happening so just have the block report to go on. I tried googling this but could not find any info on this. Any help or pointers to a resource that will help is appreciated

The MSDN sys.dm_tran_locks documentation shows a list of resource and resource subtypes, including those affecting database locking:

BULKOP_BACKUP_DB         Database backups with bulk operations.
BULKOP_BACKUP_LOG        Database log backups with bulk operations.
CHANGE_TRACKING_CLEANUP  Change tracking cleanup tasks.
CT_DDL                   Database and table-level change tracking DDL operations.
CONVERSATION_PRIORITY    Service Broker conversation priority operations such
                             as CREATE BROKER PRIORITY.
DDL                      Data definition language (DDL) operations with
                             filegroup operations, such as drop.
ENCRYPTION_SCAN          TDE encryption synchronization.
PLANGUIDE                Plan guide synchronization.
RESOURCE_GOVERNOR_DDL    DDL operations for resource governor operations such
                             as ALTER RESOURCE POOL.
SHRINK                   Database shrink operations.
STARTUP                  Used for database startup synchronization.


Allow a SQL Server job to run for specific time before killing it SQL Server 2016 Standard Ed
We have a clean up job that runs some pretty simple sql:
use productionDB
go

delete from transactionaltable where
createdat < DATEADD(day, -21, GETDATE())
go

This runs every weeknight, and has been great.... until last night, when an (apparent) lock escalation or conflicting jobs caused it to hang, locking the table and causing all kinds of mess.
I thought to myself: "Hey, there must be some some magic I can put on the job or on the step, a property like 'kill if running for more than ten minutes'"  But I can find no such.
Is the only technique to:

Fire the real job at 0100
Fire a kill job at 0110  // a safety that rarely gets a hit

The kill job to be built with this technique:
USE msdb ;
GO
EXEC dbo.sp_stop_job N'Your Job Name' ;
GO

Or is there a better/cleaner way to do this?
SOLUTION
Stop using that crude WHERE statement and start deleting rows in small batches, using a method like that described here.

I have been using the following stored proc on a SQL agent job:

    -- =============================================
    -- Author:        Devin Knight and Jorge Segarra
    -- Create date: 7/6/2012
    -- Description:    Monitors currently running SQL Agent jobs and
    -- alerts admins if runtime passes set threshold
    --
-- =============================================

/*
Change log:
 =============================================
7/11/2012 (v 1.01)
        Changed Method for capturing currently running jobs to use master.dbo.xp_sqlagent_enum_jobs 1, ''

7/12/2012 (v 1.03)
        Updated code to deal with “phantom” jobs that weren’t really running.
        Improved logic to handle this. Beware, uses undocumented stored procedure xp_sqlagent_enum_jobs

7/24/2012 (v. 1.16)
        Removed need to specify mail profile
        Fix for error sending notify email
        Added commented line for testing purposes (avg+1 minute for short tests)
 =============================================
 exec usp_LongRunningJobs
*/
ALTER PROCEDURE [dbo].[usp_LongRunningJobs]
                @jobname nvarchar(100)
AS

--Set limit in minutes (applies to all jobs)
--NOTE: Percentage limit is applied to all jobs where average runtime greater than 5 minutes
--else the time limit is simply average + 10 minutes
    DECLARE @JobLimitPercentage FLOAT

    SET @JobLimitPercentage = 150 --Use whole percentages greater than 100

    -- Create intermediate work tables for currently running jobs
    DECLARE @currently_running_jobs TABLE
        (
          job_id UNIQUEIDENTIFIER NOT NULL ,
          last_run_date INT NOT NULL ,
          last_run_time INT NOT NULL ,
          next_run_date INT NOT NULL ,
          next_run_time INT NOT NULL ,
          next_run_schedule_id INT NOT NULL ,
          requested_to_run INT NOT NULL ,-- BOOL
          request_source INT NOT NULL ,
          request_source_id SYSNAME COLLATE database_default
                                    NULL ,
          running INT NOT NULL ,-- BOOL
          current_step INT NOT NULL ,
          current_retry_attempt INT NOT NULL ,
          job_state INT NOT NULL
        ) -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions

--Capture Jobs currently working
    INSERT  INTO @currently_running_jobs
            EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, ''

--Temp table exists check

    CREATE TABLE ##LRJobsStage
        (
          [JobID] [UNIQUEIDENTIFIER] NOT NULL ,
          [JobName] [sysname] NOT NULL ,
          [StartExecutionDate] [DATETIME] NOT NULL ,
          [AvgDurationMin] [INT] NULL ,
          [DurationLimit] [INT] NULL ,
          [CurrentDuration] [INT] NULL
        )

    INSERT  INTO ##LRJobsStage
            ( JobID ,
              JobName ,
              StartExecutionDate ,
              AvgDurationMin ,
              DurationLimit ,
              CurrentDuration
            )
            SELECT  jobs.Job_ID AS JobID ,
                    jobs.NAME AS JobName ,
                    act.start_execution_date AS StartExecutionDate ,
                    AVG(FLOOR(run_duration / 100)) AS AvgDurationMin ,
                    CASE
        --If job average less than 5 minutes then limit is avg+10 minutes
                         WHEN AVG(FLOOR(run_duration / 100)) <= 5
                         THEN ( AVG(FLOOR(run_duration / 100)) ) + 10
        --If job average greater than 5 minutes then limit is avg*limit percentage
                         ELSE ( AVG(FLOOR(run_duration / 100))
                                * ( @JobLimitPercentage / 100 ) )
                    END AS DurationLimit ,
                    DATEDIFF(MI, act.start_execution_date, GETDATE()) AS [CurrentDuration]
            FROM    @currently_running_jobs crj
                    INNER JOIN msdb..sysjobs AS jobs ON crj.job_id = jobs.job_id
                    INNER JOIN msdb..sysjobactivity AS act ON act.job_id = crj.job_id
                                                              AND act.stop_execution_date IS NULL
                                                              AND act.start_execution_date IS NOT NULL
                    INNER JOIN msdb..sysjobhistory AS hist ON hist.job_id = crj.job_id
                                                              AND hist.step_id = 0
            WHERE   crj.job_state = 1
            and jobs.NAME=@jobname
            GROUP BY jobs.job_ID ,
                    jobs.NAME ,
                    act.start_execution_date ,
                    DATEDIFF(MI, act.start_execution_date, GETDATE())
            HAVING  CASE WHEN AVG(FLOOR(run_duration / 100)) <= 5
                              THEN (AVG(FLOOR(run_duration / 100))) + 10
                            --THEN ( AVG(FLOOR(run_duration / 100)) ) + 1  --Uncomment/Use for testing purposes only
                         ELSE ( AVG(FLOOR(run_duration / 100))
                                * ( @JobLimitPercentage / 100 ) )
                    END < DATEDIFF(MI, act.start_execution_date, GETDATE())

--Checks to see if a long running job has already been identified so you are not alerted multiple times
    IF EXISTS ( SELECT  RJ.*
                FROM    ##LRJobsStage RJ
                WHERE   CHECKSUM(RJ.JobID, RJ.StartExecutionDate) NOT IN (
                        SELECT  CHECKSUM(JobID, StartExecutionDate)
                        FROM    dbo.LongRunningJobs ) )
     BEGIN
--Send email with results of long-running jobs

    --Set Email Recipients
        DECLARE @MailRecipients VARCHAR(200)

    SET @MailRecipients = 'developer@adventureworks.com' --Uncomment/Use for testing purposes only

    EXEC msdb.dbo.sp_send_dbmail --@profile_name = @MailProfile
        @recipients = @MailRecipients,
        @query = 'USE DB_Maintenance; Select RJ.*
                From ##LRJobsStage RJ
                WHERE CHECKSUM(RJ.JobID,RJ.StartExecutionDate)
                NOT IN (Select CHECKSUM(JobID,StartExecutionDate) From dbo.LongRunningJobs) ',
        @body = 'View attachment to view long running jobs',
        @subject = 'Long Running SQL Agent Job Alert',
        @attach_query_result_as_file = 1;

--Populate LongRunningJobs table with jobs exceeding established limits
    INSERT  INTO DB_Maintenance.[dbo].[LongRunningJobs]
            ( [JobID] ,
              [JobName] ,
              [StartExecutionDate] ,
              [AvgDurationMin] ,
              [DurationLimit] ,
              [CurrentDuration]
            )
            ( SELECT    RJ.*
              FROM      ##LRJobsStage RJ
              WHERE     CHECKSUM(RJ.JobID, RJ.StartExecutionDate) NOT IN (
                        SELECT  CHECKSUM(JobID, StartExecutionDate)
                        FROM    dbo.LongRunningJobs )
            )
    END

Edited to include table creation:

CREATE TABLE [dbo].[LongRunningJobs](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [JobName] [sysname] NOT NULL,
    [JobID] [uniqueidentifier] NOT NULL,
    [StartExecutionDate] [datetime] NULL,
    [AvgDurationMin] [int] NULL,
    [DurationLimit] [int] NULL,
    [CurrentDuration] [int] NULL,
    [RowInsertDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LongRunningJobs] ADD  CONSTRAINT [DF_LongRunningJobs_Date]  DEFAULT (getdate()) FOR [RowInsertDate]
GO


Why is this key lookup required? I have an admittedly-complex query that I'm optimizing and I'm stuck on the next step. I have pinpointed the bulk of my problem to one part of my query that's doing the following Key Lookup but I'm confused both as to why it's doing this and what it's so slow. I'm not even referencing the PK in that table. I'm doing:

WHERE EXISTS
  (SELECT 1 FROM tblPriceTierQuantities [Extent8]
   INNER JOIN xxxx ON xxx=Extent8.AccountRateScheduleID
   WHERE Extent8.PriceTierID=tblPriceTiers.oid)

The details on the execution plan show as this:

That shows up as the only thing taking significant time to execute. Indeed, if I simply comment out that WHERE Extent8.PriceTierID=tblPriceTiers.oid clause, then the query is as speedy as I want it to be. The thing is, this lookup seems to be pointing to Extent8's oid and not tblPriceTier's oid field.

I'm confused why it's hitting the clustered index on that table because of this query. Nothing else in this query hits that table at all. Can you explain why this clustered lookup exists and also why it might be so slow? The ONLY columns ever referenced on this tblPriceTierQuantities table are AccountRateScheduleID and PriceTierID, NEVER oid which is the only column in this clustered index.

Have you updated statistics lately? Estimated Number Of Rows is 1 while Actual Number Of Rows is 10,259,536... Dated stats can lead to bad query plans (insufficient memory allocated for joins and sorts, bad join algorithm choices...).

When Auto Update Statistics is set to true on the database, SQL Server will automatically update statistics based on a percentage (around 20%) of changed rows in the statistic. This works well when tables are small but stats can get dated on bigger tables. A statistic in a table with 10000 records will automatically be updated when around 2000 of it's records get changed (and life is good). For a table with 10000000 records however, the auto update will still occur at 20% or 2000000 changed records (resulting in some very bad plans and long gaps between stat updates).

With the release of SQL Server 2008 R2 SP1 (and all future versions) the threshold can be set to a dynamic value by using trace flag 2371. This addresses the big table problem. You can read more about it here: http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx


Why is SQL Server's online indexing causing blocking? My understanding is that online indexing acquires locks at the beginning (Preparation) and end (Final) phases. However, we see a lot of DML queries blocked during the Build phase.

A sampling of the queries being blocked show they have a few things in common - the Wait Type is PAGELATCH_UP, the Wait Resource references a PFS page, and the query uses locking hints (NOLOCK, ROWLOCK, etc). Could locking hints be interfering?

Can anyone shed some light here or point me in the right direction?

Let me add, the actual command was:

ALTER TABLE dbo.Whatever DROP CONSTRAINT [PK_Whatever] WITH (ONLINE=ON);

This was the clustered index and there were no nonclustered indexes.

Despite what the official documentation says, dropping a clustered index using ALTER TABLE DROP CONSTRAINT can be performed online (tested on SQL Server 2005-2014):

USE Sandpit;

ALTER DATABASE Sandpit
SET RECOVERY SIMPLE;

CREATE TABLE dbo.Test
(
    col1 integer NOT NULL,
    CONSTRAINT PK PRIMARY KEY CLUSTERED (col1)
);

INSERT dbo.Test
VALUES (1);

CHECKPOINT;

BEGIN TRANSACTION;

ALTER TABLE dbo.Test
DROP CONSTRAINT PK WITH (ONLINE = ON);

SELECT
    [Current LSN],
    [Operation],
    [Transaction Name],
    [Xact ID],
    [Description]
FROM sys.fn_dblog(NULL,NULL) AS FD
WHERE
    FD.[Transaction Name] IS NOT NULL;

ROLLBACK TRANSACTION;

DROP TABLE Test;

Nevertheless, performing an operation online does not mean that other concurrent queries will not be impacted at all. The online operation does not take long term locks, but it may still consume considerable system resources (e.g. memory, processor, I/O). If these resources become a bottleneck, concurrent queries may experience resource-related waits.

In your specific scenario, the PAGELATCH_UP waits on PFS pages indicate latch contention on allocation resources. Any possible corrective action depends on the resource description associated with the latch waits.


Enabling both local and remote connections to a mysql server on linux (ubuntu) Is there any way to enable both remote connections and connections via "localhost" to a mysql server. I tried adding both

bind-address = 127.0.0.1

and

bind-address = externalIP

but it just used the one later in the file

Is this something that is possible or will I have to just use one?

You can set it to:

bind-address = 0.0.0.0

Which will allow both. "If the address is 0.0.0.0, the server accepts TCP/IP connections on all server host IPv4 interfaces. "

More info: https://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_bind-address


How to replicate MongoDB to a remote location? I'm working on a migration of MongoDB Replica Set from on premises to AWS. It's 300 GB of data. I've evaluated the following options:

MongoDB replica sets. It's really slow, initial sync takes 4/5 days.
Rsync. Rsync while MongoDB is running, then stop it, and run Rsync again. It doesn't work as expected, Rsync for some reason always takes the same amount of time.
Rsync + Replica Set. Stop a secondary member, rsync, start mongo, and then wait MongoDB replication to sync the latest updates.

I have questions for the first two options.

I do not understand why initial sync is so slow.
I do not understand why rsync is copying all the data always. I'm using rsync -a.

Option 3 should work, testing it. But if you have better options, it would be great.

Normally slow initial sync is because of slow network connection or VERY slow data disks. Have you measured what is transfer speed during initial sync.

Reason, why all data is copied with rsync, is that, that rsync thinks that too much has changed for delta copy.

And, yes option 3 is best solution, but only if you do that copy from snapshot or some other freezed data. Don't do it from live data files what are changing during copy process.


How to create tables with geometry in Postgres? I need to create 2 tables with distinct geometry.

One needs to be a table that has point information. The other needs to be a table that has line information.

They do not need to be populated as this is just a test. They just need to have the geometry column.

The code so far is:

CREATE TABLE point

CREATE TABLE line

How to add the geometry into the code?

The simple answer is:

create table points ( p POINT not null);
create table lines ( l LINE not null);

Then inserts look like this:

insert into points (p) values (POINT(1.2, 123.1));
insert into lines (l) values (LINE(POINT(1.2, 123.1), POINT(-5, -123)));

You probably want to build indexes if you want to have some lookups on them. Also a primary key may be beneficial.


Forced plans on readable secondaries If a plan is forced on the primary in an Availability Group, is it applied to queries run on a secondary?

I'm looking for answers that cover both possibilities for plan forcing:

Plan Guides
Query Store Forced Plan

I have read the following that suggest QS forced plans do not carry over, but cannot find anything authoritative in the documentation, or anything about plan guides.

Query Store and Availability Groups by Erin Stellato
Query Data Store Forced Plan behavior on AlwaysOn Readable Secondary by Vikas Rana

Conclusive evidence of forcing would be the presence of Use Plan or PlanGuideName and PlanGuideDB properties in the secondary's execution plan.

Query Store plan forcing does NOT affect queries on the secondary

Using Query Store to force a plan on the primary certainly looks like it forces the plan on the secondary.

I tried running a query on a non-prod server, and then flushing the query store with sp_query_store_flush_db (which was required to get the data to sync across to the secondary).  Here's the secondary on the left (note the circled warning about being "read only"), and the primary on the right:

Now I'll click "Force Plan" on the right, and then refresh both views:

So the "forcing" at least carried over in the underlying Query Store tables.  This makes sense, given that the articles quoted in the OP make the point that query forcing should remain in-place after a failover:

  Question: Will QDS retain FORCED Plan information when Database failover from Primary replica to secondary Replica?

  Answer: Yes, QDS store Forced Plan information in sys.query_store_plan table, so in case of failover you will continue to see same behavior on new Primary.

But does the forcing behavior actually take place?  I'll now run the same query on both servers.  On the primary, as expected, the "UsePlan" attribute is there in the plan XML:

<QueryPlan DegreeOfParallelism="1" MemoryGrant="11096" CachedPlanSize="288" CompileTime="82"
           CompileCPU="78" CompileMemory="2104" UsePlan="true">

And in the UI:

On the secondary (note the different server name), the plan was not forced.  Here's the same plan XML snippet:

<QueryPlan DegreeOfParallelism="1" MemoryGrant="11096" CachedPlanSize="288" CompileTime="32"
           CompileCPU="28" CompileMemory="1656">

Plan Guides do NOT affect queries on the secondary

I created a plan guide on the primary using this code (table names changed to protect the innocent):

EXEC sp_create_plan_guide
    @name = 'plan-guide-test',
    @stmt = N'SELECT TOP (1000) *
FROM dbo.TableName t
WHERE
    NOT EXISTS
    (
        SELECT NULL
        FROM dbo.OtherTable o
        WHERE t.Id = o.TableName
    );',
    @type = N'SQL',
    @module_or_batch = NULL,
    @hints = N'OPTION (MAXDOP 1)';

The plan guide was, of course, effective on the primary, as evidenced by the execution plan:

<StmtSimple StatementCompId="1" StatementEstRows="1000" ... StatementType="SELECT"
            PlanGuideDB="..._UAT" PlanGuideName="plan-guide-test" ...>

I did confirm at this point that the plan guide was replicated to the secondary.

Running the same query on the secondary, the execution plan is missing all the signs of being forced by a plan guide:

<StmtSimple StatementCompId="1" StatementEstRows="1000" ... StatementType="SELECT"
            QueryHash="0xECF8A24F126EE77A" QueryPlanHash="0x0E93CF7FEAC1B6EA"
            RetrievedFromCache="true" SecurityPolicyApplied="false">


why do I need \connect hostdb In the answer to How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

I see the line \connect hostdb why and when do I need this line?

I'm executing role creations SQL line from a .sql file

That's needed because the target database for the rest of that script is created just in the line above that statement. However creating a new database doesn't automatically switch to that database. In order for all following statements to be executed against that new database, the connection needs to be switched to that database.


MySql - How can I improve this query? I have the following table: http://pastebin.com/XH6FvhZ8

and the following query:

SELECT *
FROM (SELECT *
      FROM `job_logs`
      ORDER BY id DESC) AS job_logs_tmp
WHERE (job_logs_tmp.user_id = ? AND job_logs_tmp.workflow_state = ?)
GROUP BY chain, subchain, job_type

here is the explain of this query: http://pastebin.com/AjXgzRBA

this query usually takes over 4K ms to execute, any ideas how I can speed the query execution? I have no problem adding indexes to table if needed

thanks!

What are you trying to achieve here? I am not a MySQL programmer; however, as I understand SQL in general, every column that is not listed in the GROUP BY clause must be part of an aggregate like

SELECT
    MAX(id) AS max_id,
    chain, subchain, job_type
FROM
    `job_logs`
WHERE
    user_id = ? AND
    workflow_state = ?
GROUP BY
    chain, subchain, job_type

SELECT * in a grouping query will not work in most (if not all) SQL implementations.

UPDATE

In order to get the row with the highest id for each group you would have to embed the query above in an "outer" query.

SELECT *
FROM `job_logs`
WHERE id IN (
    SELECT
        MAX(id) AS max_id
    FROM
        `job_logs`
    WHERE
        user_id = ? AND
        workflow_state = ?
    GROUP BY
        chain, subchain, job_type
)

This query is deterministic and should work with most SQL dialects.

Some query engines perform better with joins than with "IN subquery". You can give this a try

SELECT A.*
FROM
    `job_logs` A
    INNER JOIN (
        SELECT
            MAX(id) AS max_id
        FROM
            `job_logs`
        WHERE
            user_id = ? AND
            workflow_state = ?
        GROUP BY
            chain, subchain, job_type) B
    ON A.id = B.max_id;


Mongodb _id Value and Indexes Are Lost During Mongorestore Steps I used:

mongodump
copy dump folder to another computer (for each collection I get .bson and metadata files)
mongorestore myDumpFolder

I noticed the indexes are lost and _id has new values after mongorestore.

Can anyone share their restore experiences that restored both indexes and mongodb_id?

Can anyone share their restore experiences that restored both indexes and mongodb_id?

As per MongoDB documentation here By default, mongorestore looks for a database backup in the dump/ directory.

New in version 3.6:

All MongoDB collections have UUIDs by default. When MongoDB restores collections, the restored collections retain their original UUIDs. When restoring a collection where no UUID was present, MongoDB generates a UUID for the restored collection.

UUID() has the following syntax:

UUID(<string>)

Parameter   Type    Description
hex        string
                    Optional. Specify a 36 character string to convert to a UUID BSON object. If not provided, MongoDB generates a random UUID in RFC 4122 v4 format.

Changed in version 3.6: In earlier versions of the mongo shell, UUID required a hexadecimal string argument. See the 3.4 manual.

It's returns A BSON UUID object.

  I noticed the indexes are lost and _id has new values after
  mongorestore.

@Will, As JJussi has already said that mongorestore should recreate all indexes what mongodump has recorded.

For Example

I have mongodump in my C:\data\dump folder. Which contains the database  folder 100YWeatherSmall,citibike,city,ships,video with their (.bson & .json) file. These database I had already restored in MongoDB 3.6.

Here again i am going to mongorestore to these databases dump file in MongoDB 3.6.

As you can see that mention all databases has already in MongoDB.

Note: Here I have highlighted the some database name due to security reason.

Here I am going to mongorestore through the below command.

C:\Program Files\MongoDB\Server\3.6\bin>mongorestore --verbose C:\data\dump

  Note:  Run mongorestore from the system command line, not the mongo shell.

video.movies database dump restore report status

- E11000 duplicate key error collection: video.movies index: _id_ dup key: { : ObjectId('58c59c6b99d4ee0af9e0eef0') }
  - E11000 duplicate key error collection: video.movies index: _id_ dup key: { : ObjectId('58c59c6b99d4ee0af9e0eef1') }
  - E11000 duplicate key error collection: video.movies index: _id_ dup key: { : ObjectId('58c59c6b99d4ee0af9e0eef2') }
  - E11000 duplicate key error collection: video.movies index: _id_ dup key: { : ObjectId('58c59c6b99d4ee0af9e0eef3') }
  - E11000 duplicate key error collection: video.movies index: _id_ dup key: { : ObjectId('58c59c6b99d4ee0af9e0eef4') }

100YWeatherSmall.data database dump restore report status

- E11000 duplicate key error collection: 100YWeatherSmall.data index: _id_ dup key: { : ObjectId('5553a98ee4b02cf7150e1cc8') }
  - E11000 duplicate key error collection: 100YWeatherSmall.data index: _id_ dup key: { : ObjectId('5553a98ee4b02cf7150e1cca') }
  - E11000 duplicate key error collection: 100YWeatherSmall.data index: _id_ dup key: { : ObjectId('5553a98ee4b02cf7150e1ccc') }
  - E11000 duplicate key error collection: 100YWeatherSmall.data index: _id_ dup key: { : ObjectId('5553a98ee4b02cf7150e1ccd') }
  - E11000 duplicate key error collection: 100YWeatherSmall.data index: _id_ dup key: { : ObjectId('5553a98ee4b02cf7150e1cd0') }

citibike.trips database dump restore report status

 - E11000 duplicate key error collection: citibike.trips index: _id_ dup key: { : ObjectId('572bb8232b288919b68b258e') }
  - E11000 duplicate key error collection: citibike.trips index: _id_ dup key: { : ObjectId('572bb8232b288919b68b25ad') }
  - E11000 duplicate key error collection: citibike.trips index: _id_ dup key: { : ObjectId('572bb8232b288919b68b2608') }
  - E11000 duplicate key error collection: citibike.trips index: _id_ dup key: { : ObjectId('572bb8232b288919b68b26c5') }
  - E11000 duplicate key error collection: citibike.trips index: _id_ dup key: { : ObjectId('572bb8232b288919b68b26da') }

Here you can noticed that all during mongorestore it's showing  index: _id_ dup key: because that index: id is already exist in that database & collection .

That means whatever mongodump has created the index:id & ObjectId that is same index:id & ObjectId is same mongorestore in MongoDB.

Here the below mention reports is the citibike.trips mongorestore complete mongodump restore reports.

2018-05-30T10:56:31.962+0300    [########################]  citibike.trips  835MB/835MB  (100.0%)
2018-05-30T10:56:31.962+0300    restoring indexes for collection citibike.trips from metadata
2018-05-30T10:56:32.106+0300    finished restoring citibike.trips (1990273 documents)
2018-05-30T10:56:32.126+0300    done

For example here I have only mention the citibite.trips restore complete reports. The message is showing restoring indexes for collection citibike.trips from metadata.

So, as per above conclusion we can say that mongorestore, store the same index:_id_ & ObjectId,  which mongodump has created.

For your further ref here , here and here


How to select the first 10 records for each table in the same database using sql I have 50+ tables in a database.

I don't want to use the following SQL on each table in a database.

select * from {table_name}

So, is it possible to display the first 10 records for each table inside the same database using sql?

I'd be careful about using sp_MSforeachtable, as it uses the same code pattern as sp_MSforeachdb. I've pointed out the problems with sp_MSforeachdb here (work backward) and was told it wouldn't be fixed on Connect (before they killed it, and so this link no longer works) here.

Instead I would do something like this (which also includes the table name in the output, probably useful):

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

SELECT @sql += N'

SELECT TOP (10) [table] = N''' + REPLACE(name, '''','') + ''', *
    FROM ' + QUOTENAME(SCHEMA_NAME([schema_id]))
  + '.' + QUOTENAME(name) + ';'
FROM sys.tables AS t;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

When you're satisfied with the PRINT output (note that it will truncate at 8K), uncomment the EXEC.


Do I add read committed after SET TRANSACTION ISOLATION LEVEL SERIALIZABLE? Inside a stored procedure, I have the following : ( sql server 2008 )

 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
 BEGIN TRANSACTION getStuff
 BEGIN TRY
    /*  some selects, updates, etc, etc. */
    ....
    COMMIT TRANSACTION getStuff
 END TRY
 BEGIN CATCH
   ...
 END CATCH

Since this is transaction based, my thought was the rest of the database connections will not be affected by the SERIALIZABLE.

Do I need to implicitly set isolation level to read committed after my commit? Will this adversely effect other connections between my application server and database server?

The command is TRANSACTION ISOLATION LEVEL, not SERVER ISOLATION LEVEL. It only changes the isolation level for the transaction scope.


MySql trigger having null variables during bulk inserts I have an SQL trigger. (mysql).

when am doing bulk inserts the summed values become null,

PS. single inserts work.bulk inserts don't.

DROP TRIGGER IF EXISTS  `calculate_balances`;

CREATE TABLE  IF NOT EXISTS temp(account_no VARCHAR(60),amount DECIMAL  (10,2));

CREATE TRIGGER `calculate_balances` AFTER INSERT ON `transactions` FOR EACH ROW
  BEGIN

SET @credit = (SELECT SUM(amount) FROM `transactions` WHERE  transactions.account_to = New.account_to);
SET @debit  = (SELECT SUM(amount) FROM `transactions` WHERE  transactions.account_from = New.account_to);
SET @credit_balance = @credit - @debit;

SET @credit2 = (SELECT SUM(amount) FROM `transactions` WHERE  transactions.account_to = New.account_from);
SET @debit2  = (SELECT SUM(amount) FROM `transactions` WHERE  transactions.account_from = New.account_from);
SET @debit_balance = @credit2 - @debit2;

#     UPDATE `accounts` SET account_balance = @debit_balance WHERE account_no =  New.account_from;
#     UPDATE `accounts` SET account_balance = @credit_balance WHERE account_no =  New.account_to;

INSERT INTO temp VALUES(New.account_from,@debit_balance);
INSERT INTO temp VALUES(New.account_to,@credit_balance);

END

So i need to figure out why that happens and fix it.

Looks like i needed to take care of null,

Here is what i did, though the columns might have changed

CREATE TRIGGER calculate_balances AFTER INSERT ON transactions FOR EACH ROW
             BEGIN

                SET @credit = (SELECT SUM(amount) FROM transactions WHERE  transactions.credit = New.credit);
                SET @debit  = (SELECT SUM(amount) FROM transactions WHERE  transactions.debit = New.credit);

                IF @debit IS NULL
                THEN
                    SET @debit = 0;
                END IF;

                IF @credit IS NULL
                THEN
                    SET @credit = 0;
                END IF;

                SET @credit_balance = @credit - @debit;

                SET @credit = (SELECT SUM(amount) FROM transactions WHERE  transactions.credit = New.debit);
                SET @debit  = (SELECT SUM(amount) FROM transactions WHERE  transactions.debit = New.debit);

                IF @credit IS NULL
                THEN
                    SET @credit = 0;
                END IF;

                IF @debit IS NULL
                THEN
                    SET @debit = 0;
                END IF;

                SET @debit_balance = @credit - @debit;

                UPDATE accounts SET account_balance = @debit_balance WHERE account_no =  New.debit;
                UPDATE accounts SET account_balance = @credit_balance WHERE account_no =  New.credit;

             END


Changing data type from DATE to CHAR(10) with a query I want to alter the date type in a database that has 40 tables from DATE type to CHAR(10) type, without opening the tables and manipulating them in Management Studio. i.e. only with a script/query.

I want to alter all columns of that type in any table in this database.

How can I do this?

A piece of code that does what you want is:

declare @sql nvarchar(max), @crlf char(2) = CHAR(13)+CHAR(10);

select @sql = isnull(@sql, '') + 'alter table ' + c.TABLE_SCHEMA + '.' + c.TABLE_NAME + ' alter column ' + c.COLUMN_NAME + ' CHAR(10)' +
    + case when c.IS_NULLABLE = 'YES' then ' NULL;' else ';' end + @crlf
from INFORMATION_SCHEMA.COLUMNS c
where DATA_TYPE = 'date'

print @sql;
-- exec sp_executesql @sql --execute only after inspection!

But I would like to stress that you shouldn't normally do it on a system (except for dev/test purposes), because if it is Date information that you're using, you should be using the proper data type and avoid any other unnecessary cast.


How to use mySQL to generate auto-increment ID for me This may be a bad structure. Let me know if it is, it's been a long time since I did any database design.

I have a tblGame in a mySQL database, which has two fields:

ID_Table
ID_Player

In the actual game, multiple players will sit on the one table.  So I have both fields set as a combined primary key. And ID_Table is set to auto-increment. ID_Player is an auto-increment in another table (tblPlayers).  What I'd like to do is to get the ID_Table value before any players are added to the table.  The actual game analogy is to an open table (with ID_Table already assigned) sitting there waiting for players to join. As they join, they get added to tblGame.

Is it possible to do this?

You could implement the following solution.

Game "Table"

This table stores the auto-generated values of the (playing) Tables for the game:

CREATE TABLE tblTable (
    ID int NOT NULL AUTO_INCREMENT);

Game "Players"

This table contains all the relevant Player data:

CREATE TABLE tblPlayer (
    ID         int          NOT NULL AUTO_INCREMENT,
    LastName   varchar(244) NOT NULL,
    FirstName  varchar(244) NOT NULL,
    Age        int          NOT NULL,
    PRIMARY KEY (ID)
);

Game "Games" (Matches)

This table links the tblPlayer table with the tblTable data.

CREATE TABLE tblGame (
    ID_Table   int,
    ID_Player  int
);

Creating a new table

When you create a new table in your game you just initialize a new ID by setting off the following statement:

INSERT INTO tblTable (ID) VALUE DEFAULT;

This will create a new value (ID). You would have to store this value in your program code to use when assigning players to the table.


Reindexing and its effects on SQL cache Currently looking into reindexing our sql database, and cannot find any information on whether or not the procedure cache would be effected. Any help or information would be great. We are using SQL Server 2005 as our DBMS.

Execution Plan Caching and Reuse lists some of the factors that trigger recompilation:

  Recompiling Execution Plans

  Certain changes in a database can cause an
  execution plan to be either inefficient or invalid, based on the new
  state of the database. SQL Server detects the changes that invalidate
  an execution plan and marks the plan as not valid. A new plan must
  then be recompiled for the next connection that executes the query.
  The conditions that invalidate a plan include the following:

  Changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW).
  Changes made to a single procedure, which would drop all plans for that procedure from the cache (ALTER PROCEDURE).
  Changes to any indexes used by the execution plan.
  Updates on statistics used by the execution plan, generated either explicitly from a statement, such as UPDATE STATISTICS, or generated automatically.
  Dropping an index used by the execution plan.
  An explicit call to sp_recompile.
  Large numbers of changes to keys (generated by INSERT or DELETE statements from other users that modify a table referenced by the
  query).
  For tables with triggers, if the number of rows in the inserted or deleted tables grows significantly.
  Executing a stored procedure using the WITH RECOMPILE option.

Since Index Rebuild operations also update the statistics as a side effect, it follows that an index rebuild will trigger a plan invalidation (recompile).


How do I find the transactions making my log grow? I have a database with a log that is constantly growing. It is in full recovery mode, and has a log backup every 15 minutes. It is also part of an availability group, and has a readable secondary. It is currently over 22GB in size, where the data is only 15GB. I have a certain amount of time to look into this, because the drive for logs is 60GB. sys.databases.log_reuse_wait_desc is AVAILABILITY_REPLICA for this db.

I started by trying to find out why I couldn't shrink the log, and the answer seems to be because of the readable secondary in the availability group. However, "don't shrink the log - stop it growing" seemed to be a theme in the answers I looked at. So I looked for some information on that, and found this and this.

I plan to talk to our development team, and discuss their code causing the issues, but I don't want to approach them without some additional information. What can I do to find the queries that are likely to be the culprits for growing the logs?

I found a number of troubleshooting tips for this situation here.

I've summarized them below:

"AVAILABILITY_REPLICA" indicates that the logged changes in the availability database at the primary replica that have not arrived and that these changes were applied to the availability database at one of the secondary replicas. Until logged changes arrive and are applied, the changes cannot be truncated from the availability database log at the primary replica.

Troubleshooting

"Log Send Queue" and "Redo Queue" are measurable data points during availability database synchronization. You can monitor these data points in order to determine whether an availability database log cannot be truncated because of the log uses type AVAILABILITY_REPLICA.

Log Send Queue -
When a transaction is performed at the primary, the logged blocks must be delivered and hardened to the database log file at the secondary. Any delay will prevent truncation of those logged changes in the database at the primary replica. A common reasons for a sustained log send queue is latency in the network or during hardening (write to disk) of the log blocks on the secondary.
Redo Queue -
As soon as it is hardened to the secondary database log file, a dedicated redo thread applies the log records. If the redo operation cannot keep up with the transaction log that is generated, log growth may occur. If the secondary replica redo operation is behind in applying those changes to a corresponding secondary database, the primary will truncate the transaction log.

Identify the secondary database that is delaying log truncation

If there is more than one secondary, there may be a particular secondary that is responsible for the log truncation issue. To identify the secondary database that is delaying log truncation, log on to the primary replica, query the truncation_lsn column of the sys.dm_hadr_database_replica_states dynamic management view (DMV), and then review the log_send_queue and redo_queue data points to help diagnose the issue.

The AlwaysOn dashboard and the sys.dm_hadr_database_replica_states DMV help monitor the redo progress. The following table lists some key fields:

For example, execute the following query against the primary replica in order to report the log_send_queue and redo_queue values for each secondary availability database.

SELECT ar.replica_server_name
    ,drs.truncation_lsn
    ,drs.log_send_queue_size
    ,drs.redo_queue_size
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
WHERE drs.is_local = 0

Corrective measures may include but are not limited to the following:

Make sure that there is no resource or performance bottleneck of the secondary.
Make sure that the Redo thread is not blocked in the secondary.
Make sure that the Redo thread does not fall behind because resource contention created a reporting workload.


How to estimate reindexing time? How do you estimate the total time reindexing an entire table will take in PostgreSQL?

I have a large PostgreSQL database, the primary table of which has 15 million rows with 100 columns and 200 indexes.

I'm seeing a problem where, after a night of processes loading data into the table, queries to the table are terribly slow for about 3-6 hours after the loading ends. However, these queries miraculously become fast again after 6 hours post-load.

Is this temporary slowness caused by Postgres updating indexes in the background? How do I speed up this processing? I can't find any parameters for controlling this.

I was thinking about running a REINDEX on the table immediately after loading ends, but I'm not sure how long this will take, and I'm worried it might take longer than the original load and will only worsen performance.

My table stores dozens of descriptors about a customer, and the indexes (many of them partial indexes) are for speeding up very routine queries run by users.

The database is nearly a terabyte, so I rarely load the entire thing to a dev environment. Instead, I have a script that copies a few thousand records.

As you have 200 index, it would take longer than a vacuum. A reindex always depends on your OI. It seemed that it would take more than an hour.

What I would suggest is:

get rid of some indexes. 200 for 100 columns makes no sense. See how many of the l those you use 90% of the time.

1.1. The other queries would find their path along other indexes

1.2. Your record takes more time recording indexes than rows, I that should be slowing down your night update
You could reindex the indexes the you use the most

2.1. Use REINDEX INDEX index_name;

2.2. If you can run some reindex in parallel, it would make better use of your server cores


How many database users/admins should be created for a website? I have created a CMS website that only had one database connection for both the admin area and the user area and it only had one user("root") with no password, and now I want to upload it, but I am not sure whether I should create 2 different users(and two distinct connections) one for the admin and the other just for displaying purposes for the site visitor(making it more restrict), In order to keep it more secure. Or does it not matter how many users one creates when looking at a security point of view?

You should at least not be using root for your application. The rest completely depends on how the application was developed. It sounds like you wrote it yourself, so my opinion is based on that.

My suggestion is to use two accounts, as you said one for write access (to the CMS database only) and one with read-only access (again, only for your CMS database). That way, if you have any kind of vulnerability with, for instance, the display portion of your application, you can somewhat potentially limit the damage an attacker can do.

You could do it with one user for the whole CMS; most applications you could download like WordPress and so on do it this way to make installation easier and because some hosts don't allow more than one user account. If you have the means to do so, I prefer to isolate roles as much as possible.

The reasons to not use root are many, but basically you want to give the user the minimal set of permissions needed; if an attacker gains access through your root account they can create new users, delete data, change configuration variables, and much, much more. If they gain access through a limited account that only has access to read and write to your CMS database, they can't create accounts, they can't delete other databases, and they can't insert malicious data in to other databases. They might mess with your CMS database, but it's the principle of limiting the damage at that point.


Can I generate binary logs if they weren't turned on? Ok, so here's the scenario:

Our master went down, boom! gone (Call this one DB1)
We switched our backup replica server. Ok, life is good, sort of (Call this DB2)
Binary logs were not turned on for DB2, therefore we are missing a few hours of logs
We want to bring another server up and sync with DB 2

Question: Can we generate the logs for the few hour gap? Or are they gone? I have the logs for everything up to that position and after but not during.

Followups:
Data is both InnoDb and MyIsam. The binary logs have been turned on for DB2. The issue is that they were turned on 2 hours after the server was in production. So there is two hours worth of binary logs that are missing

If i'm down voted, please tell me what I can do to improve my question.

Can we generate the logs for the few hour gap? Or are they gone?

They are gone. You cannot do a sync with that gap. However, it should not matter.

You need to setup replication from scratch on DB1.

During off-hours, run this on DB2

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQLDUMP_OPTIONS=""
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --master-data=1"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --all-databases"
echo "SET SQL_LOG_BIN = 0;" > MySQLData.sql
echo "STOP SLAVE;" >> MySQLData.sql
mysql ${MYSQL_CONN} -ANe"RESET MASTER;"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} >> MySQLData.sql
echo "SET SQL_LOG_BIN = 1;" >> MySQLData.sql

You just setup replication on DB1 and load MySQLData.sql into it.

BTW Make sure log-slave-updates is configured on DB1 and DB2