SQL Server service broker related rights issue CREATE PROCEDURE [dbo].[stproc_ssb_send]
  @iname varchar(20)
WITH EXECUTE AS 'xyz'
AS
BEGIN

DECLARE @DIALOG_HANDLE UNIQUEIDENTIFIER
DECLARE @MSG varchar(max)

SET @MSG='<MESSAGE>
        <test>' + @iname + '</test>
        </MESSAGE>'

    BEGIN DIALOG CONVERSATION @DIALOG_HANDLE
    FROM SERVICE START_PROC_PROCESS_SERVICE
    TO SERVICE 'EXECUTE_PROC_PROCESS_SERVICE'
    ON CONTRACT [PROC_PROCESS_CONTRACT]
    WITH ENCRYPTION= OFF;

    SEND ON CONVERSATION @DIALOG_HANDLE
    MESSAGE TYPE PROC_PROCESS_MT(@MSG);

    END CONVERSATION @DIALOG_HANDLE
END

error:

  Msg 15517, Level 16, State 1, Procedure [stproc_ssb_send], Line 0
  Cannot execute as the database principal because the principal "xyz" does not exist, this type of principal cannot be impersonated, or you do not have permission.

XYZ is db_owner / db_ddladmin for this database.

Procedure is created using sa; error occurs while executing.

ALTER DATABASE  SET trustworthy ON

This query solved my prob. Thanks for efforts.


What do the HT* wait types mean? (HTREPARTITION, HTDELETE, ...) A big query is running right now. The result of a batch mode hash join that spills to disk is streamed into a temp table using select into. The query is showing wait types HTDELETE and HTREPARTITION occasionally. The query is not using columnstore indexes.

I'm quite sure that these wait types are normal for batch mode hash joins that spill to disk. I'm trying to understand what these wait types mean out of curiosity. Maybe this insight can help optimize the query or find problems. So what do these wait types mean and under what circumstances are they expected?

No columnstore indexes. I'm using the left join ZeroRowCsTable on 0=1 trick to enable batch mode for row mode tables.

Community Wiki answer generated from a comment on the question by ypercube

From an MSDN Forums thread:

  HTDELETE

  SQL Server 2014 now uses one shared hash table instead of per-thread copy.
  This provides the benefit of significantly lowering the amount of memory
  required to persist the hash table but, as you can imagine, the multiple
  threads depending on that single copy of the hash table must synchronize with
  each other before, for example, deallocating the hash table. To do so, those
  threads wait on the HTDELETE (Hash Table DELETE) wait type.

(This only applies to hash operators that execute in batch mode)

Could you just try updating your statistics and running this again? Perhaps it will work? Pls let us know if it does.

ref : a Connect item on Microsoft and it's comment:


Optimise MySQL SELECT with LEFT JOIN subquery I have a query which combines a LEFT JOIN and subquery. The dataset is quite big and the time to execute the statement is over 70 seconds.

SELECT
    s.siblings,
    l.id
FROM
    `list` l
        INNER JOIN
    child c ON c.id = l.child_id
        INNER JOIN
    parent p ON p.id = c.parent_id
    LEFT JOIN (
      SELECT COUNT(c.id) AS siblings, c.id, c.parent_id
      FROM child c
      GROUP BY c.id
    ) AS s ON s.parent_id = c.parent_id AND s.id != c.id
WHERE
    l.country = 1
GROUP BY l.id, s.siblings
ORDER BY l.dateadded

This query should return all lists for a country. Each list is specific to a unique baby. For each list I would like to return a count of the number of children that have the same parent.

If I remove the LEFT JOIN subquery the fetch time is 0.1 seconds. Is there a way to make the query more efficient?

The main reason for the slow query is the join on a subquery. This will not use indexes. Then, you not only join with a derived table (subquery), but as well group total result based on subquery column - GROUP BY l.id, s.Siblings

In this case it could help to:

create temporary table from subquery, it also could include subquery
for return correct parent_id
create index on this table
use temporary table in join
drop temporary table

This could have variants, but it is often faster and less server-loading than a complicated set of subqueries with joins.

s.siblings,
...
JOIN ( SELECT ... )
  -->
( SELECT COUNT(*) FROM ... ) AS siblings

That is, get rid of the LEFT JOIN and replace it with a subquery in the SELECT.


How best to maintain SQL log file sizes I'm somewhat of a new DBA and I'm managing a SQL Server 2012 instance that has a fair amount of activity. I'm running in Full Recovery mode because we need point in time recovery.

Right now, I'm taking a full backup of the databases and logs every day at 5am. Some of the log files have ballooned up to 300gb and even after taking a backup they don't reduce in size. I can get them to reduce in size by running something similar to:

BACKUP LOG db1 TO DISK = '\\server\share\db1_log1.trn';
DBCC ShrinkFile([db1_log], 0);

BACKUP LOG db1 TO DISK = '\\server\share\db1_log2.trn';
DBCC ShrinkFile([db1_log], 0);

BACKUP LOG db1 TO DISK = '\\server\share\db1_log3.trn';
DBCC ShrinkFile([db1_log], 0);

When I check the LSNs of the backup files I see something like:

RESTORE headeronly FROM DISK = N'\\server\share\db1_log1.trn'
FirstLSN:  15781000014686200001
SecondLSN: 15802000000665000001

RESTORE headeronly FROM DISK = N'\\server\share\db1_log2.trn'
FirstLSN:  15802000000665000001
SecondLSN: 15805000000004100001

RESTORE headeronly FROM DISK = N'\\server\share\db1_log3.trn'
FirstLSN:  15805000000004100001
SecondLSN: 15808000000004200001

I don't believe I'm breaking my log chain by shrinking the log files. Reading up on this, I do believe I'm hurting my performance because those shrunk log files have to re-grow themselves.

Questions:

Why doesn't the log file shrink after my backups? Is it because there are uncommitted transactions?
At first I was thinking I should shrink the log files after every 5:00 AM backup. After reading up on how that's bad for performance I now believe that I need to take regular log backups every couple of hours during the day. Is that correct?
My normal full backup of the database/logs happens every day at 5:00 AM and sometimes takes 3 hours. If I schedule the log backups to happen every hour, what will happen when the log backup collides with the 5:00 AM backup?

Why doesn't the log file shrink after my backups? Is it because there are uncommitted transactions?

The actual NTFS log file doesn't "shrink" from a transaction log backup, but VLFs (Virtual Log Files) within the transaction log are marked for reuse (because they are now backed up and persisted on media) allowing the wrap-around of transaction log use to occur.  If you aren't backing up the transaction log, or not frequently enough then there will be not available VLFs and that will cause the transaction log to grow (provided that autogrowth is set) to accommodate additional transaction log entries.

  2.At first I was thinking I should shrink the log files after every 5:00 AM backup. After reading up on how that's bad for performance I now believe that I need to take regular log backups every couple of hours during the day. Is that correct?

Routine and scheduled file shrinkage is not a good idea.  Only when you need to reclaim much needed space should you consider a DBCC SHINKFILE.  Also, when you are continuously growing your transaction log, you could be hindering other things such as the recovery of the database.  With too many VLFs in the transaction log (a common problem when the transaction log is only grown by a small storage increment) the amount of time to recovery the database could be longer than desired.

  3.My normal full backup of the database/logs happens every day at 5:00 AM and sometimes takes 3 hours. If I schedule the log backups to happen every hour, what will happen when the log backup collides with the 5:00 AM backup?

Nothing will happen, that is a completely legal operation.  See this below graph from MSDN.  Where there is a black dot, those two operations can not occur at the same time.  As you can see, a database backup and a transaction log are allowed concurrently.

The takeaway here is you should be backing up your transaction log more frequently.  NTFS file growth isn't the only problem you could run into by not backing up your transaction log more frequently.  If you were to have storage failure and your transaction log is lost, then you can only restore to the point in time of your last transaction log backup.  If the transaction log is lost, you won't be able to backup the tail of the log and restore to point-in-time of the failure.  In your case, you could potentially lose 24 hours worth of data.  But if you backup your transaction logs every, say, 30 minutes then your maximum data loss would be 30 minutes.  In that case, if your transaction log is gone, and you have your full backup and your intact log chain you could restore to that last log backup.

TechNet documentation on Transaction Log Truncation

I got the same issue like you before.
My log file always increase instead I use full Database backup every night.
So here is my solution:

backup your current log file.
Set your Database to Simple Recovery

Becasue in Full Recovery -> Log file don't delete committed transaction, it's only re-arrange your data -> Shink file not affect much -> and vice versa for Simple Recovery.

Shink your log file to 1 MB or less (it's up to you)
Set your Database back to Full Recovery.

Hope it's help

Phong Tran


DBO on System Databases I am trying to implement some better security practices for our database systems. Right now, dbo is owned by SA. I plan to create new accounts on each server, with differing passwords, and assign SysAdmin to that local user. For many databases, dbo user is linked to the SA login. I can change these using ALTER AUTHORIZATION... no biggie.

My question is, I see MASTER and some other DB's have this mapping of DBO to SA. I don't believe that the DB Owner can be set to anything but SA on these db's.

Will disabling the SA account have impact on the opperation of the system databases when DBO is mapped to SA?

Thank you!

Wes

No, DBO is essentially just a default schema and you can make other accounts with different privileges that have separate schemas. Even though, not quite sure how disabling the SA would help in elevating security


Can I restrict user's access on a certain IP? Like in a certain way in MySQL.
CREATE 'dbuser'@'10.1.0.1' identitifed by 'password';
GRANT USAGE on *.* to 'dbuser'@'10.1.0.1';
We are not using any mongodb tools except for Robo

When you create a user then you can specify Authentication Restrictions
Would be like this:
use admin
db.createUser(
   {
     user: "dbuser",
     pwd: "password"
     roles: [ { role: "readWrite", db: "reporting" } ],
     authenticationRestrictions: [ {
        clientSource: ["192.0.2.0"],
        serverAddress: ["198.51.100.0"]
     } ]
   }
)

But for me it is not clear, if you are looking for this.

In mongodb config file, you should specify bind_address value to your desired ip.


ORDER BY clause with Alias I am trying to get the sum of the market values for each given CPTY. Each CPTY can have several market values. In my output list, i want to see the top 5 CPTYs, ordered by the sum of their market values.

Works:

SELECT TOP 5 CPTY, SUM( MKT_VAL ) AS MarketVal
FROM Eod
GROUP BY CPTY
ORDER BY SUM( MKT_VAL ) DESC;

Fails:

SELECT TOP 5 CPTY, SUM( MKT_VAL ) AS MarketVal
FROM Eod
GROUP BY CPTY
ORDER BY MarketVal DESC;

It appears when I try to ORDER BY the Alias (MarketVal), Access asks for MarketVal parameter value. However, when I ORDER BY SUM( MKT_VAL ), the code executes fine. I can't understand why this is, can anyone tell me why?

There is no issue in both the query they will run properly.

you must inspect any expressions Or find the identifier that is causing this.


Where do I use T-sql for SQL Server? I have used SQL Server a fair amount but always with SSMS.  I would like to be able to use all the capabilities and understand the environment better but I do not know where I use T-sql.  I have tried a few tutorials but they seem to gloss over this simple but important part; where do I input these commands?  Is it command line?  Is it some command line type GUI that should have installed with the SQL Server installation?  In this post (https://www.tutorialgateway.org/sql-attach-database/), there is a good example of just jumping into the commands but not specifying where these commands are input.  Thank you for any help you can give me.

where do I input these commands? Is it command line?

You can use these commands in a variety of tools that connect to SQL Server these include :

SQL Server Management Studio.
SQLCMD (Command line tool)
SQL Operations Studio, (Now SQL Azure Data Studio)
Power Query tool in Excel.
Powershell
SQL Developer
Toad for SQL Server

Pretty much any tool that allows you to connect and issue SQL commands.

T-SQL is the dialect of SQL used by SQL Server.  SQL is just a language standard.  Each vendor implement their own SQL.  SQL Server named its SQL as T-SQL.

The syntax is a bit different for different dialects.  Here's some info.  https://stackoverflow.com/questions/1301038/what-are-the-differences-between-t-sql-sql-server-and-sql


ALTER DATABASE statement not allowed within multi-statement transaction I've downloaded the AdventureWorks based In-memory sample from here, and followed all the steps described in the accompanying doc. However, when I try to run the script in SQL Server Management Studio, I get the error message:

  ALTER DATABASE statement not allowed within multi-statement transaction

The error points to line 9, which is:

IF NOT EXISTS (SELECT * FROM sys.data_spaces WHERE type='FX')
    ALTER DATABASE CURRENT ADD FILEGROUP [AdventureWorks2012_mod]
    CONTAINS MEMORY_OPTIMIZED_DATA
GO

Since this is (more or less) official Microsoft documentation, I'm assuming it's something I'm doing wrong, but I can't figure out what it is.

No, you're not doing anything wrong. I got the same thing. I solved it by breaking the sample up into multiple scripts and running each section of the script sequentially, in its own query window, instead of as one big script. This worked in my case because I'm always running these samples in an isolated VM (not on a production server!) and transaction handling is unnecessary since I'm the only one here.

Looking at the script again today more closely, there is no transaction handling defined explicitly, but perhaps you pasted the script into a query window that already had an active transaction, or created a new query window that automatically added BEGIN TRANSACTION; / COMMIT TRANSACTION; statements.

I also pointed out a couple of other potential gotchas in this blog post.

Use "Go" to separate the transactions. That will solve the issue. ( Its easy than run one by one.)
Also can change the Isolation level ( Not tested )

SET TRANSACTION ISOLATION LEVEL SERIALISABLE

Begin tran

---Statements goes here

commit tran

SET TRANSACTION ISOLATION LEVEL READ COMMITTED


Reset IDENTITY value I have a table with an IDENTITY column. While developing I delete the rows from time to time and add them again. But the IDENTITY values always kept increasing and didn't start from 1 when I added them again. Now my id's go from 68 -> 92 and this crashes my code.

How do I reset the IDENTITY value?

You can reset the identity value by

DBCC CHECKIDENT('tableName', RESEED, 0)

So next time you insert into TableName, the identity value inserted will be 1.

When you delete rows from the table, it will not reset the Identity value, but it will keep increasing it. Just like what happened in your case.

Now when you truncate the table, it will reset the Identity value to its original Seed value of the table.

Refer to : SQL SERVER – DELETE, TRUNCATE and RESEED Identity for a detailed example and some good explanation of Difference between Truncate and Delete

If you only need to eliminate the last rows which have not followed the incremental value of an identity field, there is an easy and safe way:

first delete the last record(s) which have 'jumped'
change the data type of your Identity Field (from int to bigint or vice versa)
save the table
add a new record, and check it assigns the number of the highest value + 1
Replace the data type of your Identity Field, as convenient for your needs

and you are done.


is there a DDL generator out there where you can start with CSV/TSV to create DDL for Cassandra Is there a DDL generator out there where you can start with CSV/TSV (or maybe even JSON) to create DDL for Cassandra?

DDL generator for a Cassandra keyspace:
DESC KEYSPACE the-Keyspace-name;

CSV generator for a Cassandra table:

COPY TO
DSBulk.


Truncate with where clause Can I use truncate command with a where clause?  I need to remove specific rows from several tables.

How can I delete specific data from the entire database?

SELECT DimEmployee.[FirstName], DimEmployee.[LastName], [SalesOrderNumber], [ShipDateKey]
    FROM DimEmployee
    JOIN [FactResellerSales]
        ON DimEmployee.[EmployeeKey] = [FactResellerSales].[ProductKey]
    WHERE DimEmployee.[FirstName] like 'kevin%' <--have to truncate this specific name from entire DB

Is there any other method to remove a specific data from entire DB?

In my database there are 172 tables. I wanted to delete a specific name and its corresponding columns from the entire database. The name is spread across entire database, hence I want to remove it in a single shot instead of going to each table and deleting it individually.

No, Truncate can't be used with a WHERE clause. Truncate simply deallocates all the pages belonging to a table (or a partition) and its indexes.

From BOL:

-- Syntax for SQL Server and Azure SQL Database

TRUNCATE TABLE
    [ { database_name .[ schema_name ] . | schema_name . } ]
    table_name
    [ WITH ( PARTITIONS ( { <partition_number_expression> | <range> }
    [ , ...n ] ) ) ]
[ ; ]

If you're looking for a more efficient way to delete data, I'd start here.

Can a where Clause be used along with a Truncate command
Every SQL Developer wants full control over the Database by query. Due to which they start thinking beyond the SQL syntax. One such misconception is whether a where Clause be used along with a Truncate command to perform some conditional operations.
For example, if someone wants to delete all records of last week (7 days) using Truncate command. Although this can be done using "Delete from table_name where conditions" but truncate is used because it is faster than Delete query.
The Answer for the question "Can a where Clause be used along with a Truncate command" is YES. Truncate statement means All or Nothing. This means either all the records in the Table will be deleted or none of them will be deleted.
TRUNCATE TABLE
TRUNCATE TABLE is a statement in SQL that deletes all records in a table very fast by deallocating the data pages used by the table. Deallocating the data pages reduces the resource overhead of logging the deletions done, as well as the number of locks acquired. Records removed by the TRUNCATE TABLE query statement cannot be restored, so you need to be very careful before executing TRUNCATE TABLE query.


Migrate logins tagged to specific databases users We are doing migration from SQL2008R2 to SQL 2014 for some of the databases hosted on 2008R2 to 2014 and remaining DB's to other servers.

I am aware of migrating the login using dbatoo or using sp_helprevlogin..

But using that it would move all the logins from 2008R2 instance to 2014.

Since we are only moving some databases we only need to move their respective logins or i should say only those logins that are present under users in the database geting migrated to 2014 instance.

How can we achieve this intelligently via script, because doing manually is taking lot of time as we have hundred of logins and about 40 databases

I have a procedure called sp_SrvPermissions that has a @DBName parameter specifically for that.  You can do something like this:

EXEC sp_SrvPermissions @DBName = '<DBName>', @Output = 'CreateOnly'

This will give you not just the script to create the LOGIN associated with just that database but any server level roles they are a member of and any server specific permissions they might have as well.

While this may not be the answer you want, I use sp_helprevlogin because it gives me the ability to build that script and then clean up logins.

Migrations are a perfect time to run through that list of logins and users and get rid of the "badness" Are there logins for people who no longer work there? Are there logins for items that no longer need access to the database (think role change or application retirement) Any other old logins and users that are not used any more? Using sp_helprevlogin creates that script and allows you to do that audit. Then you can comment out the logins/users you determine are not needed and only bring over what is good. This makes your and your team's live better. This also makes your auditors happy...which definitely makes your life better. :-)


Database design that requires an index over two tables Using PostgreSQL (currently 9.6, but upgrades are possible), I currently have the following database layout where customers can order products, which are themselves sorted into categories (products may be in multiple categories):
Orders
id -- PRIMARY KEY
customer_id -- FOREIGN KEY (Customer - id)
product_id -> FOREIGN KEY (Product - id)

Products
id -- PRIMARY KEY

Categories
id -- PRIMARY KEY

Product_Categories
product_id -- FOREIGN KEY (Product - id)
category_id -- FOREIGN KEY (Category - id)

Data volume
Now, I have a fairly large amount of orders (~30M) and a reasonable number of categories (~1K) and customers (~10K). There is around 30K Products, with an average of 3 products by category. Products may be moved from a category to another occasionally (let's say a once per month shuffle)
Query tendencies
My problem is that I want to have the following type of query run fast: "Get all Orders for customer whose product is in Category C". That would look like:
SELECT * FROM Orders
JOIN Product_Categories ON Orders.product_id = Product_Categories.product_id
WHERE Orders.customer_id = X AND Product_Categories.category_id = Y

Indexing considerations
The best index I can think of is an index on customer_id in Orders, supported by a secondary index on Product_Categories.product_id. This leads to the following plan (not a real plan since the design I showed above is a very large simplification of the actual case):
 - Index Scan on Orders using index on customer_id ---> Returns ~10K Rows
 - 10K Joins done by Index Lookup on the product_id index of Product_Categories (MAIN TIME CONSUMER)
 - 9990 Rows Filtered Out.
 - 10 Rows Returned

I would like to have an index over (customer_id, category_id), but I haven't been able to find a way to do this. The best solutions I can think of is to add a column categories_id INTEGER[] and then either:

Add a GIN index using categories_id and customer_id with the inclusion in list operator.
Create 1000 Partial indexes on order_id.

In both cases, I would have to synchronize categories_id with the updates in the category ↔ product association tables, which is unfortunate.
Questions
My questions are:

Am I overthinking? Is the "filtering out 10k" rows not that bad of a
problem  and any solution I can think of will make the problem worse?
Am I missing something? Can I be efficient without changing my database schema?
Assuming I should change my database schema, what is the best way to do so?

If you have an index on product_categories (category_id), as well as the one you already have on orders (customer_id) then this type of query should be very fast.  You can do a highly specific index scan on each table separately, then hash join the results.

https://explain.depesz.com/s/JEpZ

If that isn't fast enough for you, or you can't get it to use such a plan even when you have indexes in place, then I'm afraid you will have to give us a lot more info, like the actual query plan including timing, and what time you hope to achieve.

I would consider a couple of things.  An order_products table which includes op_order_id and op_product_category_id fields.

In other words a record of each order "line items". Since each record is effectively a mini representation of the product,  it has the product's category at the time the order is created.

So the query could be something like:

SELECT * FROM ORDERS O,  O_PRODUCTS OP
WHERE OP_CATEGORY_ID=123 AND O.O_ID=OP.OP_ORDER_ID AND
O.O_CUSTOMER_ID=456;

Don't scan a table in your query unless you definitely need fields from that table in the resulting recordset.

Indexes on the common identifier fields and any important for sorting.

Always ensure that the order of your query where clauses reduces the recordset as early as possible.

Do you need so many records in your tables? Can old records be moved to an OLAP / datawarehousing db to keep help improve performance. Also I sometimes use in memory copies of tables to improve query performance however you might want to be selective on what it contains to reduce demands on RAM.


Create a trigger to update table data on another Server's database I am creating a trigger in MySQL and I need a little help.

I have 2 websites, 2 databases (same name) on 2 different web servers, S1 & S2.

These databases have the same tables names.

I want both the user data on both the websites to be the same.

So if one user registers on S1, then that user registration information should be passed to S2.

If a user registration information is updated on S1, the same information should be updated on S2.

And the same applies for S2.

How can I create a trigger so that every time there is an insert / update / delete in database on S1, then the user table on S2 also gets automatically updated.

And every time there is an insert / update / delete in database on S2, then the user table on S1 also get automatically updated.

Is this possible? Could you provide some examples?

To accomplish what you are wanting to do, it is possible to use the FEDERATED storage engine on both servers, in conjunction with triggers, to allow each server to update the other server's database.

This is not exactly a simple out-of-the-box solution, because it requires additional precautions and requires you to decide whether consistency or isolation tolerance is more important and allow the queries to fail when the other server isn't available (more consistency) or use a CONTINUE HANDLER to suppress errors (isolation tolerance).

But here is an extremely simplified example.

Each server would have the identical configuration.

The local user table:

CREATE TABLE user (
  username varchar(64) NOT NULL,
  password varbinary(48) NOT NULL, /* encrypted of course */
  PRIMARY KEY(username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

A local table that is federated to the user table on the other server.

CREATE TABLE remote_user (
  username varchar(64) NOT NULL,
  password varbinary(48) NOT NULL, /* encrypted of course */
  PRIMARY KEY(username)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://username:pass@the_other_host:port/schema/user';

Selecting from remote_user on one server will retrieve the records from the other server, and insert/update/delete on that table will change data on the other server.

So, we create triggers do accomplish the purpose of updating the distance server.  They are written as BEFORE triggers, with the idea being that we don't want to do something to ourselves that we can't do to the other server -- for example, if a username already exists on the other server, but not here, we want the insert on the other server to throw an error that prevents us from creating the user here... as opposed to creating a user here with what would be a conflicting username.  This is, of course, one of the tradeoff decisions you'll need to make.

DELIMITER $$

CREATE TRIGGER user_bi BEFORE INSERT ON user FOR EACH ROW
BEGIN
  INSERT INTO remote_user (username,password) VALUES (NEW.username,NEW.password);
END $$

CREATE TRIGGER user_bu BEFORE UPDATE ON user FOR EACH ROW
BEGIN
  UPDATE remote_user
     SET username = NEW.username,
         password = NEW.password
   WHERE username = OLD.username;
END $$

CREATE TRIGGER user_bd BEFORE DELETE ON user FOR EACH ROW
BEGIN
  DELETE FROM remote_user
   WHERE username = OLD.username;
END $$

DELIMITER ;

This is not a perfect solution and is not a high-availability solution, because it relies on solid connectivity between the two systems and even if you are using InnoDB and transactions, the actions you take against the target table are not part of your local transaction and cannot be rolled back.

I use the FEDERATED engine quite a bit; it comes in handy for a number of creative purposes in my environment, including one situation where I used a federated query launched by a trigger to impose foreign key constraints against a foreign data source; however, I restrict its use to back-end processes where unexpected issues such as timeouts, coding errors, or server-to-server network/outage/isolation events cannot result in the end user on one of our web sites experiencing any kind of problem.  Your ability to tolerate such a situation would be a major determining factor into whether this is an appropriate solution.

An alternative would be to configure your two servers in master/master replication.  For this, you would need to use different database names on each server, so that for most events that replicate, the two servers could not possibly conflict with each other.  In the worst-case scenario, if you lose connectivity or encounter a replication error, the two sites would still be running independently and you could resynchronize and recover.  Configuration would look something like this:

database_a database for site A
database_b database for site B
database_c database for only the shared table(s)

Then, in database_a and database_b:

CREATE ALGORITHM=MERGE SQL SECURITY INVOKER VIEW user AS SELECT * FROM c.user;

MySQL will treat database_a.user and database_b.user as aliases for the "real" user table, database_c.user, so you would not have to change your application other than to use its designated database (i.e, you wouldn't have to configure it to understand that the user table was actually in a different schema, because the view will function pretty much transparently with this configuration).  If the schemas have foreign keys against the user table, you would declare those against the true base table database_c.user.

Configure the two servers to replicate everything, but set auto_increment_increment and auto_increment_offset appropriately so you do not have conflicting auto-increment values on the shared table(s), if your tables use auto-increment.  (Note, the documentation says that these variables are for NDB tables only, but that's not accurate).

An extra advantage of this setup is that your two servers would then have a complete duplicate of the other site's data that you could potentially use to your advantage for recovery from hardware failure in one of the servers.

Create a linked server between your servers and create a stored procedure on both servers.

Example:

CREATE proc p_insert(your parameters)

...your logic

INSERT INTO table values ()
INSERT INTO [AnotherSERVERNAME].[DATABASE NAME].dbo.[Table] values()

It may solve your problem.


Is there a way to tell your hostname within a stored procedure? Is there a way to determine the hostname of the server you're running in w/ in a stored procedure?

You can run system hostname from a client but you can't make system calls from SP.  I don't see it any of the global variables or statuses.

I can see my hostname on my PC

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

mysql>

This works in MySQL 5.x

SELECT variable_value INTO @local_hostname
FROM information_schema.global_variables
WHERE variable_name = 'hostname';

The query works directly in the mysql client:

mysql> SELECT variable_value INTO @local_hostname
    -> FROM information_schema.global_variables
    -> WHERE variable_name = 'hostname';
Query OK, 1 row affected (0.00 sec)

mysql> select @local_hostname;
+-----------------+
| @local_hostname |
+-----------------+
| LW-REDWARDS2    |
+-----------------+
1 row in set (0.00 sec)

mysql>

Here is quick SP I wrote in MySQL 5.5.12 on my PC

DELIMITER $$

DROP PROCEDURE IF EXISTS `junk`.`ShowHostName` $$
CREATE PROCEDURE `junk`.`ShowHostName` ()
BEGIN

    SELECT variable_value INTO @local_hostname
    FROM information_schema.global_variables
    WHERE variable_name = 'hostname';
    SELECT @local_hostname;

END $$

DELIMITER ;

Then run CALL junk.ShowHostName();

mysql> CALL junk.ShowHostName();
+-----------------+
| @local_hostname |
+-----------------+
| LW-REDWARDS2    |
+-----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>

Here it is as a function

DELIMITER $$

DROP FUNCTION IF EXISTS `junk`.`GetHostName` $$
CREATE FUNCTION `junk`.`GetHostName` () RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN

    DECLARE local_hostname VARCHAR(64);

    SELECT variable_value INTO local_hostname
    FROM information_schema.global_variables
    WHERE variable_name = 'hostname';

    RETURN local_hostname;

END $$

DELIMITER ;

Now, just SELECT it:

mysql> SELECT junk.GetHostName();
+--------------------+
| junk.GetHostName() |
+--------------------+
| LW-REDWARDS2       |
+--------------------+
1 row in set (0.00 sec)

mysql>

Give it a Try !!!

How about:

select @@hostname into your_variable;


MySQL LDAP authentication Is LDAP authentication and authorization in MySQL possible? What are the requirements (MySQL version, third-party software etc.)? How it's organized?

Thanks.

use OpenDS with MySQL cluster to implement LDAP

I think this will help a bit...

You can use the auth_ldap plugin provided by Infoscope Hellas L.P. under GPL.

It can be downloaded from sourceforge at:
http://sourceforge.net/projects/mysqlauthldap/

Homepage:
http://infoscope.gr/mysqlauthldap

The plugin is still a Beta and works only for UNIX installations.


Can't read config files from pgAdminIII on local Kubuntu machine My database starts fine, I can connect to it from pgAdmin and run queries.  However when I go to Tools->Server Configuration->postgresql.conf I get an error.  The error states

could not stat file "postgresql.conf": No such file or directory
CONTEXT: SQL function "pg_file_length" statement 1.
STATEMENT: SELECT pg_file_read('postgresql.conf', 0, pg_file_length('postgresql.conf'))

I tried to run that statement as its own query and came up with no such function.  I googled around and found that I need adminpack so I installed that.  Now when I run the same query I get `could not open file "postgresql.conf" for reading: No such file or directory.

The file does indeed exist at /etc/postgresql/9.4/main/ so I put the full path in the command and it tells me that absolute paths aren't allowed.  I made a symlink in my data directory /var/lib/postgresql/9.4/main of the postgresql.conf file.  Doing that allows me to open postgresql.conf from pgamin.  However, when I save the file I notice that I get a new postgresql.conf in my data directory and that it renames the symlink to postgresql.conf.bak which means when I have conflicting postgresql.conf files between the /etc/.../main and the /var/.../main directories.

One thought is to move all the .conf files from the /etc/ directory to the /var/ and then put symlinks in the /etc/ but I figure I should just ask rather than screw around.

pg_hba and postgresql.conf should stay under

/etc/postgresql/[pg_version]/main/

then you should link them to /var/lib.. with:

#:ln -s /etc/postgresql/[pg_version]/main/pg_hba.conf
/var/lib/postgresql/[pg_version]/main/pg_hba.conf


Mysql Command required for getting values from my.cnf I am new to MySQL DBA, i have a doubt, Is there any command for finding my.cnf configuration file variables and its respective value which is mention in my.cnf file in mysql prompt itself. like we will see the global variable which are there in mysql in below format
mysql> show variable like'xxxx';

Similar kind of command is there for seeing all parameter present in my.cnf.

You could use this reference: MySQL Documentation.

For example:

mysql> SHOW VARIABLES;
+---------------------------------+-------------------------------------+
| Variable_name                   | Value                               |
+---------------------------------+-------------------------------------+
| auto_increment_increment        | 1                                   |
| auto_increment_offset           | 1                                   |
| automatic_sp_privileges         | ON                                  |
| back_log                        | 50                                  |
…

  To obtain the row for a specific variable, use a LIKE clause as shown:

  SHOW VARIABLES LIKE '%size%';
  SHOW GLOBAL VARIABLES LIKE '%size%';

By using below command we can read the my.cnf or any file content through prompt.

mysql>/! cat /etc/my.cnf


What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service? I have created a user and granted many permissions and roles to it including SQLAgentUserRole and SQLAgentReaderRole.

My application checks the status of the SQL Server Agent to see if it is running or not, and whenever I am trying to run my application using the user I have created it is not able to identify a Running SQL Server Agent.

When I do the same thing using a user in the sysadmin role it works fine.

We are using master..xp_servicecontrol to check status of SQL Agent Service and I already provided user with

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO User;

Still it is not working. Any help?

What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

NONE :-)

Starting in SQL Server 2005, Microsoft provided a mechanism to infer permissions via a "proxy". The proxy for the permissions is either a Login (for Server-level permissions) or a User (for Database-level permissions). In either case, the principal (server or database) is created from either an Asymmetric Key or Certificate, is granted the appropriate permissions, and then the permissions get inferred by signing one or more pieces of code using ADD SIGNATURE. By doing this, you are giving the code the permissions that you had granted to that server or database principal. So now you can create a stored procedure or function to do a very specific thing, and effectively grant only that code the appropriate permissions to run properly. Then you just handle permissions naturally, by granting EXECUTE / SELECT permission to only those users or roles who should be allowed to perform that action.

In terms of executing xp_servicecontrol, if you grant EXECUTE on it to a User, then that User can run it to get info on ANY service, which is not nearly as fine-tuned as allowing for only running it to get info on one particular service.

With the inferred permissions method in mind, we could create a Login and place it in the sysadmin role, but we don't even need to go that far. Starting in SP1 of SQL Server 2008 R2, a new DMV was introduced that shows status information for SQL Server related Windows / NT Services: sys.dm_server_services. The only permission this DMV needs is VIEW SERVER STATE. But again, you don't even need to grant this permission to the User in question. You can:

Create a Function (UDF or Multistatement TVF) or Stored Procedure that encapsulates the very specific usage of this DMV
Create a Certificate
Create a Login based on that Certificate
Grant VIEW SERVER STATE to that Login
Create that same Certificate in the database where the User in question exists
Sign the Function and/or Stored Procedure with that Certificate
Grant the User in question EXECUTE on that Function and/or Stored Procedure

In this setup, the User isn't given any permissions outside of running a Stored Procedure or Function. And while the VIEW SERVER STATE permission allows for A LOT of info to be seen, that permission is only used to run whatever code is in the Stored Procedure or Function. And as long as that code isn't Dynamic SQL, then that permission can't be applied outside of that narrowly-defined usage (or outside of any other code that has been signed by that same certificate).

Worried that you might slip up and accidentally grant ALTER PROCEDURE to that User and then they can put in any code that requires that permission? WRONG! Any change at all to an object that is signed will drop the signature from that object. So even if someone does manage to change that Function or Stored Procedure, it will no longer be signed by the Certificate and hence will no longer be able to infer permissions from the Certificate-based Login. And even if that User somehow has permission to add a signature to an object, that won't help without the password used to create the Certificate. And if the User has access to your passwords (or deployment scripts that might have them), then you have serious flaws in your process and standards, in which case it isn't worth the time and energy to do all of this, and just put that person in the sysadmin role ;-).

Try the following:

Initial setup:

USE [Test];
GO -------------------------------------------------------------
-- DROP PROCEDURE dbo.CheckSqlAgent
CREATE PROCEDURE dbo.CheckSqlAgent
AS
SET NOCOUNT ON;

SELECT dss.[status], dss.[status_desc]
FROM   sys.dm_server_services dss
WHERE  dss.[servicename] LIKE N'SQL Server Agent (%';

GO -------------------------------------------------------------
-- DROP FUNCTION dbo.IsSqlAgentRunning
CREATE FUNCTION dbo.IsSqlAgentRunning()
RETURNS BIT
AS
BEGIN
  DECLARE @IsRunning BIT = 0;

  IF (EXISTS(SELECT dss.*
             FROM   sys.dm_server_services dss
             WHERE  dss.[servicename] LIKE N'SQL Server Agent (%'
             AND    dss.[status] = 4 -- Running
            )
     )
  BEGIN
    SET @IsRunning = 1;
  END;

  RETURN @IsRunning;
END;

GO -------------------------------------------------------------

CREATE USER [MrNoLogin] WITHOUT LOGIN;
GRANT EXECUTE ON dbo.CheckSqlAgent TO [MrNoLogin];
GRANT EXECUTE ON dbo.IsSqlAgentRunning TO [MrNoLogin];

Test #1:

SELECT SESSION_USER AS [SessionUser];

EXEC dbo.CheckSqlAgent;
SELECT dbo.IsSqlAgentRunning() AS [IsSqlAgentRunning];

Returns:

SessionUser
-----------
dbo

status  status_desc
------  -----------
4       Running

IsSqlAgentRunning
-----------------
1

Test #2:

EXECUTE AS USER='MrNoLogin';
SELECT SESSION_USER AS [SessionUser];

EXEC dbo.CheckSqlAgent;
SELECT dbo.IsSqlAgentRunning() AS [IsSqlAgentRunning];

Returns:

SessionUser
-----------
MrNoLogin

Msg 297, Level 16, State 1, Procedure CheckSqlAgent, Line 7
The user does not have permission to perform this action.

Msg 297, Level 16, State 1, Line 6
The user does not have permission to perform this action.

The Fix:

REVERT;
SELECT SESSION_USER AS [SessionUser];
-------------------------------------------------------------
USE [master];

-- DROP CERTIFICATE [DoStuffCert];
CREATE CERTIFICATE [DoStuffCert]
    ENCRYPTION BY PASSWORD = N'W0rdUp,Yo!'
    WITH SUBJECT = N'Certificate for Managing Special Permissions';

CREATE LOGIN [MrDoStuff] FROM CERTIFICATE [DoStuffCert];
GRANT VIEW SERVER STATE TO [MrDoStuff];

BACKUP CERTIFICATE [DoStuffCert]
    TO FILE = 'C:\TEMP\ViewSqlAgentStatus.CER'
    WITH PRIVATE KEY
    (
        FILE = 'C:\TEMP\ViewSqlAgentStatus.PVK',
        DECRYPTION BY PASSWORD = 'W0rdUp,Yo!',
        ENCRYPTION BY PASSWORD = 'DontStartNoneWontBeNone'
    );
-------------------------------------------------------------
USE [Test];

CREATE CERTIFICATE [DoStuffCert]
    FROM FILE = 'C:\temp\ViewSqlAgentStatus.CER'
    WITH PRIVATE KEY (
        FILE = 'C:\temp\ViewSqlAgentStatus.PVK',
        DECRYPTION BY PASSWORD = 'DontStartNoneWontBeNone',
        ENCRYPTION BY PASSWORD = 'W0rdUp,Yo!'
    );

ADD SIGNATURE TO dbo.CheckSqlAgent
    BY CERTIFICATE [DoStuffCert] WITH PASSWORD = 'W0rdUp,Yo!';

ADD SIGNATURE TO dbo.IsSqlAgentRunning
    BY CERTIFICATE [DoStuffCert] WITH PASSWORD = 'W0rdUp,Yo!';

Test #3:

EXECUTE AS USER='MrNoLogin';
SELECT SESSION_USER AS [SessionUser];

EXEC dbo.CheckSqlAgent;
SELECT dbo.IsSqlAgentRunning() AS [IsSqlAgentRunning];

REVERT;

And now both work for MrNoLogin :-).

To set the Service Startup Account for SQL Server Agent
In Registered Servers, click the plus sign to expand Database Engine.

Click the plus sign to expand the Local Server Groups folder

.

Right-click the server instance where you want set up the Service
Startup Account, and select SQL Server Configuration Manager….

In the User Account Control dialog box, click Yes.

In SQL Server Configuration Manager, in the console pane, select SQL
Server Services. In the details pane, right-click SQL Server
Agent (server_name), where server_name is the name of the SQL Server
Agent instance for which you want to change the service startup
account, and select Properties.

In the SQL Server Agent (server_name) Properties dialog box, in the
Log On tab, select one of the following options under Log on as:

Built-in account: select this option if your jobs require resources
from the local server only. For information about how to choose a
Windows built-in account type, see Selecting an Account for SQL
Server Agent Service.

Important note Important

The SQL Server Agent service does not support the Local Service
account in SQL Server Management Studio

.

This account: select this option if your jobs require resources
across the network, including application resources; if you want to
forward events to other Windows application logs; or if you want to
notify operators through e-mail or pagers. If you select this option:

In the Account Name box, enter the account that will be used to run
SQL Server Agent. Alternately, click Browse to open the Select User
or Group dialog box and select the account to use.

In the Password box, enter the password for the account. Re-enter the
password in the Confirm password box.

Click OK.
In SQL Server Configuration Manager, click the Close button.


How to insert records using select statement? Hi I am working on one project, here what I am trying is, I want to insert records what I get using select statements into temp table. but it is giving error to me, can any one help me to solve this ?
ERROR:  relation "temp_mytbl" does not exist
SQL state: 42P01
following is my query
truncate table temp_mytbl;
create temporary table temp_mytbl as
select iex_id,dte,agent_name,schd_total
FROM source_xrx_iex6_sandy_1.iex6_agent_adh_sum  limit 0;

 insert into temp_mytbl
select iex_id,dte,agent_name,schd_total
FROM source_xrx_iex6_sandy_1.iex6_agent_adh_sum
group by iex_id,dte,agent_name,schd_total;

SELECT i.dte,
    idm.empl_id,
    1 AS entity_id,
    sum(i.schd_total) AS s_schdhours
   FROM temp_mytbl i
     JOIN source_odw_usac.t_incontact_agentstatelog a ON a.winid = i.iex_id::text
     JOIN reporting_prod.idm_dte idm ON idm.src_id = a.agent_no AND idm.src_type_id = 618 AND soar_date(a.calendar_date_key) >= idm.start_dte AND soar_date(a.calendar_date_key) <= COALESCE(idm.end_dte, now()::date)
  GROUP BY i.dte, idm.empl_id limit 100;

WITH temp_mytbl AS ( -- without aggregation in output expression / HAVING clause
                     -- GROUP BY must be replaced with DISTINCT
                     -- which is less expensive
                     SELECT DISTINCT iex_id, dte, agent_name, schd_total
                     FROM source_xrx_iex6_sandy_1.iex6_agent_adh_sum
                   )
SELECT i.dte, idm.empl_id, 1 AS entity_id, SUM(i.schd_total) AS s_schdhours
FROM temp_mytbl i
JOIN source_odw_usac.t_incontact_agentstatelog a ON a.winid = i.iex_id::text
JOIN reporting_prod.idm_dte idm ON idm.src_id = a.agent_no
                               AND idm.src_type_id = 618
                               AND soar_date(a.calendar_date_key) >= idm.start_dte
                               AND soar_date(a.calendar_date_key) <= COALESCE(idm.end_dte, now()::date)
GROUP BY i.dte, idm.empl_id /* , entity_id */
-- LIMIT without ORDER BY makes no sense
ORDER BY i.dte /* or another sorting expression */
LIMIT 100;

or the same in subquery form
SELECT i.dte, idm.empl_id, 1 AS entity_id, SUM(i.schd_total) AS s_schdhours
FROM ( SELECT DISTINCT iex_id, dte, agent_name, schd_total
       FROM source_xrx_iex6_sandy_1.iex6_agent_adh_sum
     ) i
JOIN source_odw_usac.t_incontact_agentstatelog a ON a.winid = i.iex_id::text
JOIN reporting_prod.idm_dte idm ON idm.src_id = a.agent_no
                               AND idm.src_type_id = 618
                               AND soar_date(a.calendar_date_key) >= idm.start_dte
                               AND soar_date(a.calendar_date_key) <= COALESCE(idm.end_dte, now()::date)
GROUP BY i.dte, idm.empl_id
ORDER BY i.dte
LIMIT 100;

create table #temp_mytbl (
 iex_id int,
 dte int,
 agent_name varchar(10),
 schd_total float
 )

 insert into #temp_mytbl
select iex_id,dte,agent_name,schd_total
FROM source_xrx_iex6_sandy_1.iex6_agent_adh_sum
group by iex_id,dte,agent_name,schd_total;

SELECT i.dte,
    idm.empl_id,
    1 AS entity_id,
    sum(i.schd_total) AS s_schdhours
   FROM #temp_mytbl i
     JOIN source_odw_usac.t_incontact_agentstatelog a ON a.winid = i.iex_id::text
     JOIN reporting_prod.idm_dte idm ON idm.src_id = a.agent_no AND idm.src_type_id = 618 AND soar_date(a.calendar_date_key) >= idm.start_dte AND soar_date(a.calendar_date_key) <= COALESCE(idm.end_dte, now()::date)
  GROUP BY i.dte, idm.empl_id limit 100;

  DROP TABLE #temp_mytbl


Is this surprising deadlock reasonable or a MariaDB bug? I recently had to diagnose a deadlock in an application that seemed impossible to me. Even after I found out how to reproduce it, I don't really undestand why it happens. I'm considering filing a MariaDB bug, but since I'm not a database expert, I thought I'd ask first if someone can tell me there is a reasonable explanation.

Edit: Filed as a bug now at https://jira.mariadb.org/browse/MDEV-17512

I used a recent version of mariadb (will have to look up the exact version number, but I think that's more interesting for a bug report than for a general "why does this happen") with its default transaction isolation level of REPEATABLE_READ. Here is how I can reproduce the problem:

First, let's set up the test:

create database deadlock;
use deadlock;
create table foo(id int primary key) engine=InnoDB;
insert into foo values (10);

Now we can start. Let's open two connections A and B to the database.

-- Connection A
begin;
delete from foo;

-- Connection B
begin;
delete from foo;

At this point, the delete issued by B is waiting. This is as expected because A is holding the relevant locks.

-- Connection A
insert into foo values (9);

And at this point, mariadb kills the waiting query of B due to a deadlock. This is very surprising to me, because it would mean that B has already managed to acquire some locks.

As an additional point of information, if you use the id 11 instead of 9 in the last statement, there is no deadlock.

So can someone explain what is happening here? Is this normal behaviour, or should I file a bug?

Edit:
Here is the output of SHOW ENGINE INNODB STATUS:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-10-15 21:33:09 0x7f9f9da02700
*** (1) TRANSACTION:
TRANSACTION 481736, ACTIVE 9 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 3049, OS thread handle 140323520444160, query id 12527706 localhost root updating
delete from foo
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 32882 page no 3 n bits 72 index PRIMARY of table `deadlock`.`foo` trx id 481736 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 0000000759c3; asc     Y ;;
 2: len 7; hex 2d000002182fc2; asc -    / ;;

*** (2) TRANSACTION:
TRANSACTION 481731, ACTIVE 16 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 3050, OS thread handle 140323521046272, query id 12527773 localhost root update
insert into foo values(9)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 32882 page no 3 n bits 72 index PRIMARY of table `deadlock`.`foo` trx id 481731 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 0000000759c3; asc     Y ;;
 2: len 7; hex 2d000002182fc2; asc -    / ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 32882 page no 3 n bits 72 index PRIMARY of table `deadlock`.`foo` trx id 481731 lock_mode X locks gap before rec i
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 0000000759c3; asc     Y ;;
 2: len 7; hex 2d000002182fc2; asc -    / ;;

*** WE ROLL BACK TRANSACTION (1)

A guess...

DELETE without WHERE needs to lock the entire table.  Perhaps that is done via "start through current end", where the 'end' is at 10.  Hence messing with '9' would be problematic.

On the other hand, '11' is off the end, so less ambiguity.

Are you arguing that neither should deadlock?  Or that both should deadlock?

Here's another aspect...  To do everything "correct" in every case would possibly be very complex.  So some shortcuts have been taken.  And your test case pulls out a case where the shortcut led to an inconsistency.

Or is it "right" to have the inconsistency?  One thread is deleting "old" stuff; the other thread is adding "new" stuff.  Think of a queue -- this is exactly what you need.


Oracle returns different results when executing a sql with bind variable or with a fix value The following two statements (strongly simplyfied) returning a different result:

with a fix value for rownmun:

select * from table
 where rownum <= 12;

with bind variable for rownum:

select * from table
 where rownum <= :maxsize;

I know, Orcale is using a different execution plan due to internal statistics, but imho even if the choosen plans are different , the result should be the same.
I'm using a Oracle 10g.

My dba skills are limited, therefore I would appreciate any hint or advice, that helps me to understand this strange behaviour.

The results would not be guarenteed to be the same if the execution plans are different. Without an explicit sub-query ordering the rownum will be based on the order in which Oracle selected the data from the tables / joined tables. If the plans differ on how the tables are joined and in which order, the first 12 values read will be different.

To make this more deterministic, use some kind of explicit ordering on a subquery and then rownum that on the outer query. It would be normal to want the first X records based on some explicit ordering, instead of 12 random ones.

If you are using the literal word maxsize, this is reserved in Oracle and has specific meaning.

Consult the list of reserved words for more information.


db2_install do not have write permission on the directory or file This is the error I encountered (I ran the db2_install and everything else as root,):

DBI1288E  The execution of the program /home/DB_SERVER/ibm/db2/v10.5 failed. This
      program failed because you do not have write permission on the
      directory or file .

I tried to change the access permission of the path:

chmod -R a+rw /home/DB_SERVER/ibm/db2/v10.5

And then I could create a new file in that folder:

vi test

Result of ll in that directory:

total 1
-rw-rw-rw- 1 root root 12 Nov 23 00:24 test

But when I ran the db2_install again, it failed for the same reason.

PS: I tried to change permission of the setup file too

chmod -R a+rwx db2Setup/

Result of ll

total 72
drwxrwxrwx  6 root root 4096 Nov 23 00:10 db2
-rwxrwxrwx  1 root root 5349 Nov 23 00:10 db2ckupgrade
-rwxrwxrwx  1 root root 5302 Nov 23 00:10 db2_deinstall
-rwxrwxrwx  1 root root 5172 Nov 23 00:10 db2_install
-rwxrwxrwx  1 root root 5136 Nov 23 00:10 db2ls
-rwxrwxrwx  1 root root 5154 Nov 23 00:10 db2prereqcheck
-rwxrwxrwx  1 root root 5154 Nov 23 00:10 db2setup
drwxrwxrwx 10 root root 4096 Nov 23 00:10 ibm_im
-rwxrwxrwx  1 root root 5190 Nov 23 00:10 installFixPack
drwxrwxrwx  4 root root 4096 Nov 23 00:10 nlpack
-rw-r--r--  1 root root    8 Nov 23 01:00 test

So I have no idea what's wrong? How to fix this?

if you install on mounted fs ensure it's not mounted with the nosuid and noexec parameters.
db2 binaries require suid file permissions.
remove v10.5 directory if you created it manually
and set chmod 755 on parent directory

We had similar kind of issue and this was fixed after we ran fsck.
Note:- We ran FSck after we reboot the server. You might try it.


Difference between Sharding And Replication on MongoDB I am just confused about how Sharding and Replication work.
According to the definitions I found in the documentation:

Replication: A replica set in MongoDB is a group of mongod processes that maintain the same data set.
Sharding: Sharding is a method for storing data across multiple machines.

As per my understanding if I have 75 GB of data then by using replication (3 servers), it will store 75GB data on each server means 75GB on Server-1, 75GB on server-2 and 75GB on server-3. (correct me if I am wrong).
And by using sharding, it will be stored as 25GB data on server-1, 25Gb data on server-2 and 25GB data on server-3. (Right?).
But then I encountered this line in the tutorial:

Shards store the data. To provide high availability and data
consistency, in a production sharded cluster, each shard is a replica
set

As a replica set is 75GB in size, but shard is 25GB in size, then how can they be equivalent?
This makes me quite confused. I think I am missing something obvious. Please help me with this.

A Replica-Set means that you have multiple instances of MongoDB which each mirror all the data of each other. A replica-set consists of one Master (also called "Primary") and one or more Slaves (aka Secondary). Read-operations can be served by any slave, so you can increase read-performance by adding more slaves to the replica-set (provided that your client application is capable to actually use different set-members). But write-operations always take place on the master of the replica-set and are then propagated to the slaves, so writes won't get faster when you add more slaves.

Replica-sets also offer fault-tolerance. When one of the members of the replica-set goes down, the others take over. When the master goes down, the slaves will elect a new master. For that reason it is suggested for productive deployment to always use MongoDB as a replica-set of at least three servers, two of them holding data (the third one is a data-less "arbiter" which is required for determining a new master when one of the slaves goes down).

A Sharded Cluster means that each shard of the cluster (which can also be a replica-set) takes care of a part of the data. Each request, both reads and writes, is served by the cluster where the data resides. This means that both read- and write performance can be increased by adding more shards to a cluster. Which document resides on which shard is determined by the shard key of each collection. It should be chosen in a way that the data can be evenly distributed on all clusters and so that it is clear for the most common queries where the shard-key resides (example: when you frequently query by user_name, your shard-key should include the field user_name so each query can be delegated to only the one shard which has that document).

The drawback is that the fault-tolerance suffers. When one shard of the cluster goes down, any data on it is inaccessible. For that reason each member of the cluster should also be a replica-set. This is not required. When you don't care about high-availability, a shard can also be a single mongod instance without replication. But for production-use you should always use replication.

So what does that mean for your example?

                            Sharded Cluster
             /                    |                    \
      Shard A                  Shard B                  Shard C
        / \                      / \                      / \
+-------+ +---------+    +-------+ +---------+    +-------+ +---------+
|Primary| |Secondary|    |Primary| |Secondary|    |Primary| |Secondary|
|  25GB |=| 25GB    |    | 25 GB |=| 25 GB   |    | 25GB  |=| 25GB    |
+-------+ +---------+    +-------+ +---------+    +-------+ +---------+

When you want to split your data of 75GB into 3 shards of 25GB each, you need at least 6 database servers organized in three replica-sets. Each replica-set consists of two servers who have the same 25GB of data.

You also need servers for the arbiters of the three replica-sets as well as the mongos router and the config server for the cluster. The arbiters are very lightweight and are only needed when a replica-set member goes down, so they can usually share the same hardware with something else. But Mongos router and config-server should be redundant and on their own servers.

In terms of functionality delivered.
Sharding provides scalability and parallelism.
Replication provides availability


Cleanup process after bulk operations Not too long ago I did a bulk operation that I ended up needing to rollback. My transaction log file for that hour was 7 GB. I found that my log file is now over half the size of my data file and I'm assuming that transaction caused that growth since nothing changed with the backup schedule (the log backup has always succeeded and the full has intermittently failed recently due to space).

Both this time and in the future, what operations should I follow up with after doing large maintenance (like index rebuilds, full table updates, etc)?

I'm realizing I may need to shrink my log file back to a more typical size (since my maintenance activity was not a normal operation), but I also hesitate about this - if I have the space (I'm about to make space), should I just leave the transaction file at that size since it did need that space at one point, and will probably again (even if not during normal transactions)?

Are there any other operations I should consider during maintenance/release windows (reduced or full shutdowns)?

For reference, I have in mind our production SQL Server 2016 server with a 2008 compatibility database; currently 30 GB in size in Full recovery mode; full backups are nightly and transaction logs are hourly.

I always truncate the log file after a bulk operation. Also, typically, production log backups are set to at least once every 30 minutes. I prefer 15 minutes, but an hour is a bit long. That helps in keeping log file in check too.


Audit table insert restriction So I'm trying to create my own audit table for some user management analysis and stumbled across a little problem. I use this trigger

CREATE OR REPLACE
TRIGGER usr_t_audit_user_alnd
    AFTER LOGON ON DATABASE
    BEGIN
        INSERT INTO usr_t_audit_user (username, session_id, logon_day, logon_time)
        VALUES(UPPER(USER), SYS_CONTEXT('USERENV','SID'), SYSDATE, TO_CHAR(SYSDATE, 'HH24:MI:SS'));
    END;

to insert some standard informations into my table. It works fine, except the logoff time inside my other trigger isn't always that correct. But my actual problem is, that I don't want to insert all user logons. For example I only want to audit some chosen users. How can I achieve such a restriction? I can't put a WHERE-clause in my trigger. Anybody got some ideas?

You probably want to use something like,

CREATE OR REPLACE TRIGGER usr_t_audit_user_alnd AFTER LOGON ON DATABASE
BEGIN
  -- insert audit record only if user is one of specified, else ignore
  if ora_login_user in ('USER_A','USER_C','USER_D') then
    INSERT INTO usr_t_audit_user (username, session_id, logon_day, logon_time)
    VALUES(UPPER(USER), SYS_CONTEXT('USERENV','SID'), SYSDATE, TO_CHAR(SYSDATE, 'HH24:MI:SS'));
  end if;
END;

you can add IF condition around the INSERT stmt in your trigger.


SQL update next row in select I have ran into a problem with SQL. I have a list with car and mileage data. What I need to do is for the empty fields to calculate data.

Each row has a field for KM (mileage in KM metric unit), prevMileage (mileage of previous row) and nextMileage (mileage of the next row). The prev and next fields are calculated in a list before this one. The problem with this is that if the KM for a row is NULL the prevmileage of the next row and the nextmileage of the previous row will be NULL as well.

To make my calculation correct I need to add the previous mileage to the calculated value I make for each row. But if there are multiple NULL rows after each other, this is impossible.

What I need now is a way to update the 'previousmileage' field of the next row.

This is the code I have so far and a preview of the table:

Date      |     KM     |   prevmileage    | nextmpileage
--------------------------------------------------------
01/01/2008|     5000   |       4500       |     55000

Code

    , CalculatedList as (
SELECT
    PNL.MonthDate,
    PNL.FK_CarID,
    PNL.AllocationID,
    PNL.CO2,
    PNL.KM,
    PNL.beginMonth ,
    (CASE   WHEN PNL.KM IS NULL
            THEN (CASE  WHEN PNL.PrevMileage IS NULL
                        THEN (CASE  WHEN PNLnext.PrevMileage IS NULL --Here I want to update the prevmilage of the next row
                                    THEN (CASE  WHEN PNL.NextMileage IS NULL
                                            THEN 0
                                            ELSE ((PNL.NextMileage / DATEDIFF(DAY, PNL.PrevMonthDate, PNL.NextMonthDate)) * DATEDIFF(DAY, PNL.PrevMonthDate, PNL.MonthDate))
                                            END)
                                    ELSE 0
                                    END)
                        ELSE (CASE  WHEN PNL.NextMileage IS NULL
                                    THEN PNL.PrevMileage
                                    ELSE (CASE  WHEN PNL.PrevMileage = 0 OR PNL.NextMileage = 0
                                                THEN (CASE  WHEN PNL.NextMileage = 0
                                                            THEN 0
                                                            ELSE (PNL.NextMileage / 2)
                                                            END)
                                                ELSE ((PNL.NextMileage - PNL.PrevMileage) / DATEDIFF(DAY, PNL.NextMonthDate, PNL.PrevMonthDate)) * DATEDIFF(DAY, PNL.MonthDate, PNL.PrevMonthDate)
                                                END)
                                    END)
                        END)
            ELSE PNL.KM
            END ) as Calculated,
    PNL.NextMileage,
    PNL.PrevMileage,
    PNL.PrevMonthDate,
    PNL.NextMonthDate,
    (CASE   WHEN PNL.KM IS NULL
            THEN 'N'
            ELSE 'Y'
            END) AS RealData,
    PNLnext.PrevMileage as test
FROM
    PrevNextFullList as PNL outer apply
     (select top 1 ml2.*
      from PrevNextFullList ml2
      where ml2.FK_CarID = PNL.FK_CarID and
            ml2.MonthDate > PNL.MonthDate
      order by ml2.MonthDate desc
     ) PNLnext
)

Do you actually need to persist the prev and next columns in the data? Why not use Window functions on retrieval of data to get what you need from your base table? I've made a bit of a guess on what your schema looks like in it's most basic form but this should help you. If not post CREATE statements for all tables involved and I'll edit the answer.

SELECT  MonthDate
        , CarId
        , KM
        , LAG(KM) OVER (PARTITION BY CarId ORDER BY MonthDate) Prev
        , LEAD(KM) OVER (PARTITION BY CarId ORDER BY MonthDate) [next]

FROM    PrevNextFullList

If you do need to persist the columns then use this statement to create a VIEW

UPDATE You would need to upgrade to SQL 2012 to do this though.


Is the SQL Server Express memory and CPU limit per instance? If I have 8GB of RAM in a server and I run 4 instances of SQL Server Express, will the total memory limit used by SQL Server be 1GB or 4GB?

Would it be advisable to run multiple instances like this to enable each database to make better use of resources (assuming that the server has plenty of resources)?

If I have 8GB of RAM in a server and I run 4 instances of SQL Express,
  will the total memory limit used by SQL Server be 1GB or 4GB?

Each instance can use up to 1GB of memory for the buffer pool.  Each instance can use a bit more than 1GB in total because not all memory allocations go via the buffer pool.  In your case, the maximum memory used by the four instances for buffer pool would be 4GB.

To confirm, I started two instances of the SQL Server 2008 Express Database Engine, performed some activity to load up the (separate) buffer pools, and then looked at per-instance memory utilization in a number of ways, for example using DBCC MEMORYSTATUS or by counting the number of buffers using the sys.dm_os_buffer_descriptors DMV.

The physical memory usage numbers below were obtained using simultaneous queries against the system DMV sys.dm_os_process_memory on each instance of the database engine:

SELECT
    dopm.physical_memory_in_use_kb
FROM sys.dm_os_process_memory AS dopm;

Output:

╔═══════════╦═══════════╗
║ Instance1 ║ Instance2 ║
╠═══════════╬═══════════╣
║   1102872 ║   1059812 ║
╚═══════════╩═══════════╝

Each of these is slightly in excess of 1GB as total physical memory usage includes more than just buffer pool usage, as mentioned previously.

  Would it be advisable to run multiple instances like this to enable
  each database to make better use of resources (assuming that the
  server has plenty of resources)?

If the databases on each instance are functionally independent then it is at least workable to run multiple instances of Express in this way, though you would need to pay careful attention to configuration and maintenance may be made more complex.

You might be be better served using another edition, such as the fully-featured (and very cheap) Developer Edition, if the intended usage matches the licence).  You would need to say much more about the circumstances to get a clear answer on this point.

In SQL Express, the database engine can only consume 1 GB RAM, and any DB cannot be larger than 10 GB.

So, 4 instances would still have to share that 1 GB RAM.

You can find out much more at http://msdn.microsoft.com/en-us/library/cc645993(v=SQL.110).aspx.

A similar discussion here on Stack Overflow says this is the right answer BTW, so no need to downvote: https://stackoverflow.com/questions/1169634/limitations-of-sql-server-express

This page says the same, even though it is about SQL Server Express 2005: http://www.edugeek.net/forums/windows-server-2000-2003/55183-sql-server-express-2005-memory-limit-per-instance.html


Merge multiple column values into one column in different table? I've done this before, but I don't remember how. I need to select all rows with unique values from several columns in one table, and insert them into one column in a different table. For the destination table they need to be on separate rows in the destination column.

Here's an example:

                        Source Table
+-----+------------+------------+------------+------------+
|  id |  column1   |  column2   |  column3   |  column4   |
+-----+------------+------------+------------+------------+
|  1  |   value1   |   value1   |   value1   |   value1   |
+-----+------------+------------+------------+------------+
|  2  |   value2   |   value3   |   value4   |   value5   |
+-----+------------+------------+------------+------------+

 Destination Table
+-----+-----------+
|  id |  column1  |
+-----+-----------+
|  1  |   value1  |
+-----+-----------+
|  2  |   value2  |
+-----+-----------+
|  3  |   value3  |
+-----+-----------+
|  4  |   value4  |
+-----+-----------+
|  5  |   value5  |
+-----+-----------+

I tried this query but of course it didn't work because the columns aren't equal.

INSERT INTO table2 (column1)
SELECT DISTINCT column1, column2, column3, column4
FROM table1

Try with union

insert into table2(column1)
select column1 from table1
union
select column2 from table1
union
select column3 from table1

Try this...

SELECT col1,
  col2
FROM
  (SELECT rownum X,col_table1 FROM table1
  ) T1
INNER JOIN
  (SELECT rownum Y, col_table2 FROM table2
  ) T2
ON T1.X=T2.Y;


Databases outside of AlwaysOn availability group stuck in RESTORING status I am testing the AlwaysOn feature of SQL Server 2012. I have 2 servers set up and can successfully create availability groups and fail them over between servers. However, any databases that DO NOT belong to an availability group are stuck in a status of "Restoring" on the secondary server. I have started from scratch several times, and the same thing happens to these databases.

Is this normal behavior?

I was assuming that databases that don't belong to an AG would simply not show up on the secondary server.

How did you setup your Availability Group?  It sounds like what happened is you blanketed the backups and restores from the primary replica server to the secondary replica server.

In other words, you did a:

backup database <DatabaseName>
to disk .... <so on and so forth>

to all databases on the primary and a

restore database <DatabaseName>
from disk ....
with norecovery

to all the backups aimed at the secondary replica server (proper operations for future AG databases).  If you don't join one of those databases to the availability group then it'll stay in that restoring state.

The main question here is, if Database1 (for example) on your primary replica server as not meant to be in the availability group, then why would it be restored/existing on the secondary replica server to begin with?

I have a half-answer at least. My first attempt at creating an Availability Group consisted of every database. I later decided to only try a few databases in a group. So I deleted the databases on the secondary after removing them from the AG. Subsequent attempts to create availability groups with a limited set of databases triggered the stuck "Restoring" state. I am not sure why. I stopped SQL Server on the secondary, deleted the mdf and ldf files, then restarted SQL Server. All databases still showed "Restoring", but I was able to delete them in SSMS. I now have just the databases that I intended to have in the availability group showing on the secondary as "Synchronized"


how to check which background process are running in my oracle database? To check all background process in oracle we can see using v$bgprocess view. I want to check which background process are active. Can anybody please help ?

Thanks

17:31:15 SYSTEM@sandbox> ed
Wrote file S:\spool\sandbox\BUFFER_SYSTEM_34.sql

  1  select sid,  process, program
  2    from v$session s join v$bgprocess using (paddr)
  3   where s.status = 'ACTIVE'
  4*    and rownum < 5
17:31:21   5  /

       SID PROCESS                  PROGRAM
---------- ------------------------ ----------------------------------------------------------------
         2 1332                     ORACLE.EXE (PMON)
         3 480                      ORACLE.EXE (PSP0)
         4 976                      ORACLE.EXE (VKTM)
         5 992                      ORACLE.EXE (GEN0)

Elapsed: 00:00:00.05

However, this doesn't make much sense as they're always stated as active, so I guess you should define what you mean saying active.

select *
from v$session ses
join v$sql sql on
     sql.ADDRESS = ses.SQL_ADDRESS
where 1=1
and upper(ses.OSUSER) like '%USERNAME%'
;

STATUS = ACTIVE if the session is running.

If the client logged off, the entry will dissappear after some time.


Oracle: terrible execution times from virtual application server I've been provided with an Oracle VM linux box playing host to an Oracle 11g installation. I've also been provided with an Oracle VM Windows Server 2008 64-bit box acting as an application server. Tasked with deploying my application to this environment, I've created / populated the DB, installed instantclient drivers and SQL*Plus on the application server and ran a few simple queries...

...and performance is terrible. A selection on 9000 rows takes 30 seconds.

N.B. the same SELECT * FROM ... ; query is being used as a baseline, executed from SQL*Plus with timing on, autotrace traceonly statistics and arraysize 5000.

Things I've tried:

HRPING the DB server from the application server - response time is around 0.5ms (slow, but not this slow)
Execute the query using SQL*Plus directly on the DB server - execution time is 0.2 seconds
Execute the query using the exact same instantclient drivers / SQL*Plus client from a different (physical) machine on the network - execution time is 0.2 seconds
Checked that tablespaces are not full
Checked that tmpfs (/dev/shm) space is not full
Check that no AV / firewalls are running

I'm convinced this is an infrastructure / networking issue, but the infrastructure admin insists it is a configuration / software issue.

Other factors:

The application is using manual IPv4 settings, and the DNS server was not specified... The local machine I used for testing had Automatic Settings. I've now requested for the correct details to be applied. Could this cause the issue?
There's an undiagnosed (unsolved) issue whereby if either the application server or the DB is restarted, it drops off the network and requires a shutdown/startup command to be issued via the Oracle VM client to bring it back
The application server came with Oracle PV drivers installed... I uninstalled to see if this would have any impact, but no difference has been observed

I'm all out of ideas. Any suggestions? Thanks in advance.

EDIT:

I'm currently looking into DNS settings. They were missing on the application server, and I'm not sure that they're correct on the DB server in /etc/resolv.conf either. I found a couple of resources suggesting that incorrect DNS settings could cause very poor performance.

This is almost certainly a networking issue (as the execution on a different machine is way faster) so I doubt you'll be able to go much further with any Oracle tools. I've never had any needs to tweak out-of-the-box network parameters to get good performance.

What I suggest is you use a standard network benchmarking tool, e.g. iPerf. It needs to be cross-platform since you have a mixed Linux/Windows environment. Keep in mind that the issue could be due to either low bandwidth or high latency. Run the same test from the other client from which you're getting good SQL Plus results, compare and if it's massively different send the evidence to your networking admin guys.

By the way, if you still need more advice I suggest you post to https://serverfault.com/ as you'll likely to reach more experienced network admins there.

Oracle query is compiled and processed is some "environment". In rare cases some differences in an "environment" can lead to situations where the same query has different execution plan when executed from sqlplus, PL/SQL versus JDBC. Just to be 100% sure, try to check v$sql_plan and compare the real execution plan of the query when beeing executed from various clients.


SQL Server database design for "archived but available" data We have this large database (>1TB) that we intend to "shrink".  The database revolves around one main entity, let's call it "Visit".  For discussion, let's say it is a database for a medical practice.

There are a total of 30 visit "types", such as procedure, annual, follow-up, immunisation etc, each of which is a subsidary table to "Visit", e.g. "visit_immuno".

The database has accummulated some 12 years of data since 2000.  Someone has proposed that we keep about 3 years of data in the "live" version and have the rest live in an "old_data" database.  The date is ONLY stored in the "Visit" table since it is normalised.  The Visit table also contains a ROWVERSION column and a BIGINT pseudo-identity (clustered) column.  For all intents and purposes, let's say the clustering key is populated by a SEQUENCE (SQL Server 2012 Enterprise) - we shall name it cid.

The visit.date is not always in the same order as the clustering key, for example when a doctor goes on extended visitations and returns with his "briefcase" of data, it gets merged into the main table.  There are also some updates to the "visit" table that will cause the ROWVERSION column to be out of sync with both the cid and date columns - to put it simply, neither ROWVERSION nor cid would make suitable partition keys for this reason.

The business rule for removing data from the "live" is that the visit.date must be greater than 36 months and a child visit_payment record must exist.  Also, the "old_data" database does not contain any of the base tables except visit%.

So we end up with:

Live DB (daily use) - All tables
Old-Data DB - older data for the visit% tables

The proposal calls for a Combined DB that is a shell containing Synonyms to ALL the base tables in the Live DB (except visit%) plus Views that UNION ALL across the visit% tables in the two databases.

Assuming the same indexes are created in the Old-Data DB, will the queries perform well on the UNION-ALL Views?  What type of query patterns might trip up the execution plan for the UNION-ALL Views?

For convenience, assume that the live database is called LiveDb and the achive database is called ArchiveDb

Add a UNION ALL view in LiveDb pointing to the tables in ArchiveDb database via a synonym (There is no need to do a combined db with synonyms)
"Partition" on visit.date and denormalise this column to visit_payments too if it isn't there already (this improves co-located join performance)
Only archive the two large tables if possible (reduces chance of tripping up the optimiser). Keep the UNION ALL view and the other tables in LiveDb so all joins to the smaller tables are kept local
Add a CHECK constraint on the tables in both LiveDb and ArchiveDb  that describes the range of visit.date contained in the table. This helps the optimiser eliminate the archive table from both seeks and scans that contain the column visit.data. You will have to periodically update this constraint.
In the UNION ALL view, add a WHERE criteria which filters on visit.data. This is in addition to the hint you already provided in the check constraint. This maximises the chance of filters being pushed down
If you have EE, partition the table in the archive database (But NOT in the live database). If you want to get really fancy, use filegroup level backup/restore of the archive databases to save on backup times.
Consider putting AchiveDb in SIMPLE recovery mode if it isn't already. You are not likely to need transaction log backups of ArchiveDb
Use INSERT... WITH (TABLOCK) SELECT ... WITH (ROWLOCK) to force minimal logging on the destination when moving data between  LiveDb and ArchiveDb

All of the above does not guarantee that the optimiser will eliminate the archive tables from seeks and scans, but it makes it more likely.

When the elimination doesn't happen. These are the effect you may see (this list may be incomplete). For seeks, you will get an additional seek on every query (this drives up IOPS). For scans, the results could be disastrous for performance as you may end up scanning both the archive and live tables. Here are the typical ways you can trip up the optimiser:

If you join the visit% tables together and don't include the visit.data in the join criteria (this is why you want to denormalise). Because of this, you may wish to modify some of your queries
If you get a hash join between visit.data and another table (for example a date dimension), you may not get the right elimination of tables
If you try to aggregate data over the archived tables
If you filter on anything BUT the visit.data, for example a seek directly on the key of the view.

For the last scenario, you can guard yourself against the worst effects by adding another check constraint on the cid - if this is possible. You did mention that the sequence of cid not "clean" with respect to the dates and progression of rows in the table. However, could you maintain a table that contains the information: "There are no cid above this number since this visit.data" or similar? This could then drive an additional constraint.

Another thing to be careful about is that parallel queries may spawn a LOT more threads once you query the partitioned view (as both "sub-tables" will be exposed to the same parallel optimisations). For that reasons, you may want to limit MAXDOP on the server or the queries that are parallel.

By the way, if you know the queries well - you may not even need the same indexes in the two databases (this assumes you are 100% sure you will get the right elimination of tables). You could even consider using column stores for ArchiveDb.

The way we have done is to write the old data in batches to a newly created database and delete the old data from live db. That way both db are accessible. You can also back up the newly created database and restore it elsewhere to remove the large footprint from production servers. Hope that is an acceptable solution to your needs.


Foreign Key with a Constant Let's say I have a table A, that has two columns: one is an ID for ThingA, and one is an ID for ThingB. The primary key is (ThingA, ThingB).

Next, I have a second table, but this time it's restricted to entries in table A that have ThingB = 3. The primary key is ThingA, because ThingB is a constant of 3.

Initially, I had thought I could simply:

FOREIGN KEY (ThingA, 3) REFERENCES A(ThingA, ThingB)

But I've learned that's not the case, and I have to create a column for the ThingB:

ThingB INT NOT NULL DEFAULT(3) CHECK(ThingB = 3)

Then,

FOREIGN KEY (ThingA, ThingB) REFERENCES A (ThingA, ThingB)

Is there an alternative to this that doesn't require an extra column, or the DEFAULT + CHECK? One alternative is a persisted, computed column, but I hate that idea too as it's basically a cheat and still adds a new column with physical storage. While on it's own, the INT won't be big, there are several million rows that need it across several tables, and I'd rather not maintain the extra columns.

Here's sample DDL to illustrate the situation:

CREATE TABLE Test1
(
    ThingA INT NOT NULL,
    ThingB INT NOT NULL,
    PRIMARY KEY (ThingA, ThingB)
);

CREATE TABLE Test2
(
    ThingAVal INT NOT NULL,
    ThingBVal INT NOT NULL DEFAULT(3) CHECK(ThingBVal = 3),
    Val INT NOT NULL,
    FOREIGN KEY (ThingAVal, ThingBVal) REFERENCES Test1 (ThingA, ThingB)
);

And I've created a db<>fiddle that demonstrates my (current) solution:

https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=edba40478f104ac017c3444138a07762

If the answer is "No", I'll accept it, but I'm curious if there are any other alternatives.

You said you didn't want to add an extra column to a number of tables that need to link back to Test1 in this way (ie on ThingA, 3).

How about adding one persisted computed column to TestA that shows the value ThingA if ThingB is 3, and null otherwise?

Then your foreign key references the new column only, based on ThingA in the referencing table.

alter table Test1 add SpecialThingA as
    (case ThingB when 3 then ThingA else null end) persisted;

and

FOREIGN KEY (ThingA) REFERENCES Test1 (SpecialThingA)

In other words - one new column on Test1, rather than a new column on Test2 (to hold '3') and Test3, and ....

If you know the constant value of ThingB, then I suggest to leave the column "ThingB" away. Instead let the business logic add the constant value. What the value exactly is can be stored in another table or in some setting.


Best name for link table I was wondering what the best name for a linked table in this situation (or any) would be. I've searched for this, but could not find any concrete examples.

For example, I have the following tables (where 'table in the name refers to tables in a restaurant):

restaurant_table
restaurant_table_feature

A table can have all kind of features and also multiple. Think of rounded tables, tables near the window, ground-floor tables, tables near entrance. It really depends on the type of restaurant, so it needs to be customizable. Arrangements are not permanent, since tables can be moved around for various reasons. Usually, the smallest possible combination of a table has a table number, if tables are combined for a group, multiple table numbers are used.

I want to link multiple features to a table row, with a linked table. Should I call the linking table restaurant_table_feature_table or restaurant_table_to_feature.

I've seen both possibilities in multiple systems, but which one would be the preferred one?

I also could simply rename restaurant_table_feature to restaurant_feature, and create a restaurant_table_feature table as link table. However if I also have a restaurant_location table for which I would have features (which differ from the table features) I would run into problems.

To be clear, my question is what the best name would be (for the many-to-many tables) in any situation with multiple many-to-many tables.

This is generally developer's choice but in my opinion restaurant_tables implies one-to-many relationship. When a table resolves many-to-many relationship I prefer names like restaurants_X_dishes. Again, this is up to my taste.

I would suggest restaurant_table_continued


Insert performance increases under load: Why? I have a piece of code that performs inserts into highly denormalized tables. The tables have numbers of columns ranging from ~100 to 300+. This is SQL Server 2008 R2, running on Windows Server 2008.

Each insert consists of inserting to a number of tables under the same transaction. Some inserts are batched by NHibernate, but some cannot be, but they are all under the same transaction nonetheless.

When I perform inserts for say 500 times by repeatedly calling a piece of code that performs the insert, I get an average of ~360 ms.

The weird bit is, when I run the test code simultaneously using 4 processes (the same exe run from 4 different command prompts under windows server 2008), the insertion performance per call gets much better. I see bursts that go as fast as 90 ms (almost X4 faster). I'm measuring the insertion time from the code.

Since the 4 processes know nothing about each other, I'm assuming that this has something to do with SQL Server, but I have absolutely no clue why. I'd like to know why this is happening and if there is any configuration that would allow me to get the same performance when the inserts are not that frequent.

Suggestions regarding SQL Server monitoring methods to understand what is going on at the db level are equally welcome.

One possible reason is that four concurrent processes generate a more favourable pattern of log flushes - typically meaning that each log flush writes more data than is the case with a single executing process.

To determine if transaction log throughput/flush size is a factor, monitor:

sys.dm_os_wait_stats for WRITELOG and LOGBUFFER waits
sys.dm_io_pending_io_requests for IO performance
Performance Monitor counters (or sys.dm_os_performance_counters) for:

Log Bytes Flushed/sec
Log Flushes/sec
Log Flush Wait Time

Look for internal limits being reached. In SQL Server 2008 R2, there can be a maximum of 32 outstanding (asynchronous) log flush I/Os per database on 64-bit versions (only 8 on 32-bit). There is also a total size limit on the outstanding IOs of 3840KB.

More information and further reading:

Transaction Log Monitoring
Trimming Transaction Log Fat
Diagnosing Transaction Log Performance Issues and Limits of the Log Manager
Optimizing Transaction Log Throughout

some servers / cpus / os's remember the patterns.  like cache.

Since you doing the same thing 4 times, I'm sure there are ways it can cut corners,  What I am guessing is that the first way you do it, it thinks of it as one long process (example1) but in the second way is sees the reused code and runs it like cache (example2) or it could be the first process is to big to fit it all in the (ram example3).

example1: 0111110000110111110000111011111000011110111110000

example2: 0111110000|11|0111110000|111|0111110000|1111|0111110000

example3: 0111110000011111000001111100000111110000
example3: loop: 0111110000

I know ubuntu server does this with repeated mysql queries. I can save them in cache, although really the only difference in time is 10-40mms but it adds up. When I was in school there was classes that showed you have to make programs (perl / php) use that cache to be faster.

But, it might depend on the program, what language is it, what is it compiled in or how it was programmed.


InnoDB row level locks without SELECTs? I have several (>30) MySQL clients inserting into InnoDB tables concurrently. I keep encountering lock wait timeouts and can't figure out why. Here is the situation:

Each client runs the following transactions repeatedly:

Start transaction
Insert one row into table A
for i = 1 to 22
  Do some time-consuming calculation (30 sec)
  Insert results into table B
end
Commit transaction

The lock wait timeouts happen when inserting into table A. Table B's primary key is the same as table A's augmented by another field. Table B has a foreign key into table A.

There are no SELECTs anywhere. No row is inserted twice. Transaction isolation level is REPEATABLE_READ.

SHOW ENGINE INNODB STATUS shows up to 36 row-level locks for some active transactions.

I do not understand why there are any locks. Can anyone give me some hints as to what's going on?

MySQL is taking too much of the innodb buffer pool size for your table locking.

Insert innodb_buffer_pool_size=2g in my.cnf file, which is most probably in /etc/mysql/


Server Logon Trigger fails after deleting many records in "Logon-Table" -SQL Error 2801 We have a server logon trigger defined that logs every login with information like windows/sql user name, client and time in a table.

The table doesn't have clustered index, but it does have a "PK_ID" column, which is bigint and autoincrement.

Last week, we deleted roughly 80 million from a total of 90 million records in that "login table" (with a delete from..., we didn't truncate the table).

But since then we see multiple cases where the logon trigger fails with

Logon failed for login 'SQL\xy' due to trigger execution.
Changed database context to ‘master’.
(Microsoft SQL Server, Error: 17892)

We did restart the whole windows server a couple of times and afterwards the error went away for a few hours, but seems to come back every now and then. Note that this only started to appear after we deleted so many records.

We found out that prior to users not being able to login, we get the following error:

Msg 2801, Level 16, State 1,:
The definition of object 'my_proc' has changed since it was compiled.

Afterwards we get the error 17892 as described above.

Does anybody know what the problem could be?

Trigger definition

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TRIGGER [Tr_My_Logon]
     ON ALL SERVER  WITH EXECUTE AS 'xy'
     FOR LOGON
     AS
     BEGIN
        BEGIN TRY
            INSERT INTO mydatabase.myschema.myLogonHistoryTable
                SELECT GETDATE(),
                      ORIGINAL_LOGIN(),
                      EVENTDATA().value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(500)'),
                      @@SPID,
                      program_name(),
                      HOST_NAME(),
                      EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(500)')
       END TRY

       BEGIN CATCH
           PRINT ERROR_MESSAGE()
       END CATCH

     END
    GO

    SET ANSI_NULLS OFF

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

From a logging perspective, if you intend to delete more than 50% of the rows from a table, you should use the following methodology:

Set the transaction isolation level to serializable
Begin a transaction
Create a new table, named the same as the original table, with a _x
suffix
Copy the results you want to keep from the original table to the _x
one
Drop the original table
Rename the _x table back to the original table
Commit the transaction

Also, the PK_ID column that you described should be the PK and clustred index for the table, otherwise its just a heap.

Also, in terms of logging server logins, perhaps you'd have better luck using an Audit or extended event session?

The "The definition of object ‘my_proc’ has changed since it was compiled" error that you described should be resolvable by calling sp_recompile.