how to clear only the query plans from the current session? I am working on tuning a query on a very busy server, where I don't want to clear the cache, using DBCC FREEPROCCACHE;

I know it will cause at least a CPU spike, and in this case I don't want it.

here in this article it shows how to clear the cache for an specific database only.

-- Example 2 (Ballpeen hammer)
-- Remove all elements from the plan cache for one database
-- Get DBID from one database name first
DECLARE @intDBID INT;
SET @intDBID = (SELECT [dbid]
                FROM master.dbo.sysdatabases
                WHERE name = 'AdventureWorks');

-- Flush the procedure cache for one database only
DBCC FLUSHPROCINDB (@intDBID);

I would like to do something like this:

-- Example 3 (Scalpel)
-- Remove one plan from the cache
-- Get the plan handle for a cached plan
SELECT cp.plan_handle, st.[text]
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N'%/* GetOnlineSearchResultsMonday %';

-- Remove the specific plan from the cache using the plan handle
DBCC FREEPROCCACHE (0x05000800F7BA926C40C15055070000000000000000000000);

  Is there any way I can  locate the query that I am working on, or at
  lease my own session in the cache?

I am tuning the query below:

checkpoint

set statistics io on
set statistics time on

    SET NOCOUNT ON;

    DECLARE @OrderStartDate DATETIME
    DECLARE @OrderEndDate DATETIME

    SELECT @OrderStartDate = '27-FEB-2016'
    SELECT @OrderEndDate = '28-FEB-2016'

    SELECT
           op.lngPaymentID
          ,op.strBxOrderNo
          ,op.sintPaymentTypeID
          ,op.strCurrencyCode
          ,op.strBCCurrencyCode
          ,op.decPaymentAmount
          ,op.decBCPaymentAmount
          ,ap.strAccountCode
          ,o.sintMarketID
          ,o.sintOrderChannelID
          ,o.sintOrderTypeID
          ,CASE WHEN opgv.lngpaymentID IS NULL THEN NULL
                     -- Not a Voucher = Null
                WHEN gvp.strIssuedBxOrderNo IS NULL THEN 0
                ELSE 1 END AS [IsPromoVoucher]
                     -- Is a Voucher - check type
          ,o.sdtmOrdCreated

    FROM    tablebackups.dbo.tblBOrderItemOrderComplete com

            INNER JOIN tablebackups.dbo.tblBOrderPayment op
                    on op.strBxOrderNo = com.strBxOrderNo

            INNER JOIN tablebackups.dbo.tblBOrder o
                    ON op.strBxOrderNo = o.strBxOrderNo

            INNER JOIN tablebackups.dbo.tblBOrderItem as oi
                    on com.strBxOrderNo = oi.strBxOrderNo
                   AND com.sintOrderSeqNo = oi.sintOrderSeqNo

            INNER JOIN tablebackups.dbo.tblBAccountParticipant ap
                    ON o.lngAccountParticipantID = ap.lngParticipantID

            LEFT OUTER JOIN tablebackups.dbo.tblBOrderPaymentGiftVoucher opgv
                         ON op.lngPaymentID = opgv.lngPaymentID

            LEFT OUTER JOIN tablebackups.dbo.tblBGiftVoucher gv
                         ON opgv.strVoucherNumber = gv.strVoucherNumber

            LEFT OUTER JOIN tablebackups.dbo.tblBGiftVoucherPromotion gvp
                         ON gvp.strIssuedBxOrderNo = gv.strIssuedBxOrderNo

    WHERE   com.LoadDate >= @OrderStartDate
    AND     com.LoadDate < @OrderEndDate
    AND     o.sdtmOrdCreated >= @OrderStartDate
    AND     oi.decCatItemPrice > 0

set statistics io off
set statistics time off

Marcello, it seems you have the answer in your question, albeit with one minor modification.

The query:

SELECT cp.plan_handle, st.[text]
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N'%/* GetOnlineSearchResultsMonday %';

contains the term '%/* GetOnlineSearchResultsMondy %', which is a wild-card search used to locate the specific plan to remove from the cache.  If you ran it with a search term contained within the query you are attempting to tune, it should return a plan handle that can then be used with DBCC FREEPROCCACHE:

-- Remove the specific plan from the cache using the plan handle
DBCC FREEPROCCACHE (0x05000800F7BA926C40C15055070000000000000000000000);

I would suggest perhaps using the following to identify the query:

SELECT cp.plan_handle, st.[text]
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N'%CASE WHEN opgv.lngpaymentID IS NULL THEN NULL%';

Assuming that string is not used in other queries, the results should only return a few rows.  You would then look at the st.[text] column to ensure you have the correct plan_handle, then use that plan_handle with DBCC FREEPROCCACHE to remove it from the plan cache.

As a side-note, I would be very careful running an SSMS session against a live production system - it can be a recipe for disaster, since it is very easy to forget your are working on the production server.


Is SQL Server on Mac equally well performing as it is on Windows? My friend decided to learn SQL Server and T-SQL. However, he has a Mac machine.

It is my understanding that SQL Server's primary use case was to be run on Windows.

What potential issues may he run into with SQL Server on Mac?

For example how well does SQL Operations Studio work, does it miss features? Is it reliable?

You can install SQL Server on Mac using docker images. The procedure for that installation can be found here

However, a lot of features still aren't supported when not running on windows (even though Microsoft is working on adding some of them). If you don't need things like Distributed Transaction Coordinator or some of the unsupported features you should be OK.

SQL Server Management Studio will not run on Mac natively, maybe you might have some success using Parallels Desktop.

SQL Server operations studio  will run natively on a Mac.

Bob Ward has a good step by step guide on how to set everything up: Take the SQL Server Mac challenge

SQL Server Operations Studio is still in prerelease status so it's likely to have a lot more bugs than Management Studio and it still lacks a lot of features SSMS has.
If you look at their release notes you will see that the team releases often (sometimes more than one release per month) and you will also see some releases fix regressions in previous releases (which means they broke stuff in one release they had to fix in the next one). You will also note that they add features at a high pace, so trying to list the missing features now isn't going to help you since the list will change again in a few weeks.

If your goal is to start learning SQL Server and T-SQL however I think you should be fine running it on a Mac.


How to Select Most Recently Updated Row Based on Sequence ID I have a primary ID column but also a sequence ID column for each edit of the row (the initial row is 1, each edit increments the sequence ID but the primary ID column remains the same). What is the best way to select only the most up-to-date (max sequence ID) row in queries? I currently do this programmatically but was wondering if there was a way to do this all in one query with SQL.

Sample Data:

Person_ID   Person_Seq_ID   Person_Name   Person_Favorite_Color
---------   -------------   -----------   ---------------------
1           1               John Doe      Green
1           2               John Doe      Turquoise
2           1               Jane Doe      Blue
3           1               Mike Smith    Red

Let's suppose I have this query:

SELECT * FROM Person

I would only want the most recent John Doe row to return (sequence ID #2 in this case), and Jane's and Mike's single rows should also be returned.

Desired Results:

Person_ID   Person_Seq_ID   Person_Name   Person_Favorite_Color
---------   -------------   -----------   ---------------------
1           2               John Doe      Turquoise
2           1               Jane Doe      Blue
3           1               Mike Smith    Red

What's the best way to do this with one SQL query so that it can be used with different types of queries such as if I only want to return certain columns?

;WITH x AS
(
  SELECT Person_ID, Person_Seq_ID, Person_Name, Person_Favorite_Color,
    rn = ROW_NUMBER() OVER (PARTITION BY Person_ID ORDER BY Person_Seq_ID DESC)
  FROM dbo.Person -- always use schema prefix
)
SELECT Person_ID, Person_Seq_ID, Person_Name, Person_Favorite_Color
  FROM x WHERE rn = 1
  ORDER BY Person_ID;


How to implement a unique key for a long VARCHAR column in MySQL I need to let MySQL to check the uniqueness of a VARCHAR column which is 5000 max length and is not the primary key. I can use an index of length 500 on the column to speed up query, but how to create an unique key that works?

An unique key of length 500 fails when trying to insert two records whose that column is the same for the first 500 characters and differs on somewhere after that.

Checking directly for uniqueness of a 5000-character string is going to be pretty expensive, no matter how you implement it.

I'd suggest computing a hash, storing that hash, and computing uniqueness on that hash. This is almost the same thing, but does introduce the opportunity for a hash collision, where two different values result in the same hash.

The likelihood of a collision will vary with the hashing algorithm: MD5 has a 1 in 2128 chance in a collision. SHA1, and SHA256 each provide better protection against collision, but are slower/more expensive.

For example: You could create a computed/virtual column with the MD5() function, then create a unique index on that column. This won't guarantee perfect uniqueness, but is likely unique enough for most purposes where you have a 5k chunk of text that you want to keep unique.

With hashing, if two different, unique strings have a hash collision, this would prevent that second unique value from being inserted. However, two identical strings will produce the same hash. So the problem with a weak hashing algorithm is that it would be too aggressive in preventing dupes when it shouldn't.


What does OPTION FAST in SELECT statement do? I have done some digging on what the OPTION (FAST XXX) query hint does inside a SELECT statement and I'm still confused about it. According to MSDN:

Specifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues execution and produces its full result set.

For me that does not make much sense but basically the query can get the first XXX rows really fast then the rest at normal speed?
The Microsoft Dynamics query that got me thinking on this is:
select pjproj.project,pjproj.project_desc,pjproj.customer,pjproj.cpnyid
from pjproj WITH (NOLOCK)
where project like  '%'
order by project OPTION(FAST 500)

Can anyone explain exactly what this query hint is doing and its advantage over not using it?

A FAST N will tell SQL Server to generate an execution plan with quickly returning the number of rows defined as N.

Note that the estimates will be as per the N since you are telling sql server to retrieve N rows as fast as possible.

e.g. running below query with fast 500:

-- total rows : 19972
 SELECT [BusinessEntityID]
      ,[TotalPurchaseYTD]
      ,[DateFirstPurchase]
      ,[BirthDate]
      ,[MaritalStatus]
      ,[YearlyIncome]
      ,[Gender]
      ,[TotalChildren]
      ,[NumberChildrenAtHome]
      ,[Education]
      ,[Occupation]
      ,[HomeOwnerFlag]
      ,[NumberCarsOwned]
  FROM [AdventureWorks2012].[Sales].[vPersonDemographics]
  order by BusinessEntityID
  option (fast 500)

Est vs Actual rows with option (fast 500)

Est vs Actual rows without option (fast 500)

A use case would be when an application is doing caching (load large amount of data in background) and wants to show the user a slice of data as quickly as possible.

Another interesting use case is in SSIS land that Rob Farley describes using FAST N as a catalyst speeding up the data retrieval.

  By adding this hint, it felt like a magic wand had been waved across the query, to make it run several times faster.

See Remus Rusanu's answer as well.


MySql - How to monitor the amount of reads/writes to the DB Is there a way to fetch the amount of reads / writes made to the entire DB (MySql) in a given period, e.g. the amount of read / writes in the last hour?

In the table GLOBAL_STATUS in information_schema.

Use the field Com_select for reads and sum the fields Com_delete, Com_insert, Com_update, Com_replace for writes.

These values are cumulativ counters, you should subtract values between an interval:

if t1=t0+60seconds, Com_select at t1 - Com_select at t0 = reads in 1 minute


Allocate space for object 'dbo.SORT temporary run storage DBCC CHECKDB returns:

  Could not allocate space for object 'dbo.SORT temporary run storage:
  140737951236096' in database 'tempdb' because the 'PRIMARY' filegroup
  is full.

  Create disk space by deleting unneeded files, dropping
  objects in the filegroup, adding additional files to the filegroup, or
  setting autogrowth on for existing files in the filegroup.

  Msg 9002,
  Level 17, State 4, Line 1

This is pretty self explanatory - your tempdb is full, and can't be grown to perform the operation it needs tempdb for.

As indicated, either the disk where it is located is full, or you haven't got suitable autogrowth settings (see this earlier post for information about that), if it's not autogrowth, you'll have to either delete other files from the disk that your tempdb is on, or locate your tempdb on a bigger disk.

This sqlservercentral post is similar to yours and might take be worth a look. This video by Brent Ozar might be worth a look too, covering tempdb performance and config.


ALL permission is deprecated and maintained only for compatibility? How can i resolve this issue below?

use eNtsaMemberRegistrationDB
go
grant all on tablename to Users;

Message

  The ALL permission is deprecated and maintained only for
  compatibility. It DOES NOT imply ALL permissions defined on the
  entity.

Always best to check the documentation.
GRANT

This option (ALL) is deprecated and maintained only for backward
compatibility. It does not grant all possible permissions. Granting
ALL is equivalent to granting the following permissions:
If the securable is a table, ALL means DELETE, INSERT, REFERENCES,
SELECT, and UPDATE.

So in theory you have executed these commands.
GRANT DELETE ON tablename to Users;
GRANT INSERT ON tablename to Users;
GRANT UPDATE ON tablename to Users;
GRANT SELECT ON tablename to Users;
GRANT REFERENCES ON tablename to Users;

Best to execute them separately in the future & avoid using deprecated statements.


Test password complexity without creating a user Is there a way to check if a particular password string passes sql server/windows password validation/complexity rules (that is be valid for creating a user) using SQL but without creating a user ?

Thx!

The enforce password policy setting for a SQL Server Login is basically a set of rules. These are as follows:

  Password Complexity

  Password complexity policies are designed to deter brute force attacks by increasing the number of possible passwords. When password complexity policy is enforced, new passwords must meet the following guidelines:

  The password does not contain the account name of the user.
  The password is at least eight characters long.
  The password contains characters from three of the following four categories:

  Latin uppercase letters (A through Z)
  Latin lowercase letters (a through z)
  Base 10 digits (0 through 9)
  Non-alphanumeric characters such as: exclamation point (!), dollar sign ($), number sign (#), or percent (%).

  Passwords can be up to 128 characters long. You should use passwords that are as long and complex as possible.

Reference: Password Policy (Microsoft Docs)

So basically you could program your web form to check for the complexity of a given password according to the above rules.

There are a couple of questions over on Stack Overflow that summaries possibilities of using REGEX or Java to check the password complexity:

Complex password Regular expression and accepted answer
Regex to validate password strength and accepted answer

Good luck.


What is better to use for this case, a CTE or a table variable? I sometimes need to write t-SQL code to send emails per row based on some conditional checks on some tables. So because the original table does not have an identity or some such column to track rows, I have to insert the data i want into a table variable, something like this:

DECLARE @i int
DECLARE @rowCount int
DECLARE @body nvarchar(max)
DECLARE @tableVar1 TABLE(rowid int identity(1,1), col1 nvarchar(10), col2 nvarchar(10), col3 nvarchar(30))

INSERT INTO @tableVar1(col1, col2, col3)
SELECT col1,col2,col3
FROM MyTable
WHERE (some condition(s))

SET @rowCount = @@ROWCOUNT

SET @i = 1

WHILE (i < @rowCount)
BEGIN

SET  @body = (SELECT col1 from @tableVar1 where rowid = @i) + ' and ' + (SELECT col2 from @tableVar1 where rowid = @i) + ' and ' + (SELECT col3 FROM @tableVar1 where rowid = @i) + ' are the columns.'
EXEC msdb.dbo.sp_send_dbmail @profile_name = @profileName, @recipients = 'recipientlist', @subject = 'someSubject', @body = @body

SET @i = @i + 1

END

Now, I have two questions, first is, when there is a lot of resultant data from the table, it becomes a mess of several dozens of separate SELECTS from the table variables just to retrieve data from say, one column, then more SELECTS from another column, etc. Is there another way for such a scenario to avoid a lot of SELECTs?

Second, would a CTE be better vs a Table Variable when querying hundreds of thousands of rows and performing calculations per row? I don't fully understand CTEs and have not really used them. For this sort of a thing, I only use the kind of solution I have written above.

I would probably do it this way, using set-based dynamic SQL instead of any looping constructs, CTEs, etc. We could probably eliminate the table variable as well, but not enough information in the question for that, so this code assumes the table variable is already populated.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
EXEC msdb.dbo.sp_send_dbmail
  @profile_name = @profileName,
  @recipients = N''recipientlist'',
  @subject = N''someSubject'', @body = N'''
    + col1 + N' and '
    + col2 + N' and '
    + col3 + N' are the columns'';'
FROM @tableVar1;

EXEC sys.sp_executesql @sql, N'@profileName SYSNAME', @profileName;


MySQL table not working after a few days of use I have the following tables on MySQL server, 5.5.46:

CREATE TABLE `locking` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `filename` varchar(255) NOT NULL,
  `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `origin` varchar(255) NOT NULL,
  `system` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `filename` (`filename`,`origin`)
) ENGINE=InnoDB AUTO_INCREMENT=74882007 DEFAULT CHARSET=utf8;

CREATE TABLE `locking_rules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `timeout` int(11) NOT NULL,
  `origin` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

And I have the following SQL which is executed periodically from different machines (simultaneously or not):

DELETE l FROM locking l,locking_rules r WHERE l.origin=r.origin AND r.name!='*' AND ABS(TIME_TO_SEC(TIMEDIFF(l.timestamp,NOW())))>r.timeout

In this table there are permanently inserted new rows. The table is in general at about 20000 rows size. I insert on DB using OpenJPA ORM, and remove using the provided "native query".

The problem:

Sometimes, once at few days, delete queries on locking table are just waiting and nothing happens anymore to this table. Can anybody give me some hints on which part should I look in order to see where the problem sits?

If I run show engine INNODB status, output doesn't have anything related to locking table locks or deadlocks.

Part of the problem is that you are scanning most of 20K rows and locking the rows to be deleted as you go.  The scan, itself, is time consuming.

If you are using MyISAM, that is a big issue -- switch to InnoDB.

Don't use TIMEDIFF, it will get you in trouble for large differences.  I will assume that l.timestamp was some time in the past and that r.timeout is how many seconds before it "times out".

DELETE  l
    FROM  locking l,
          locking_rules r
    WHERE  l.origin = r.origin
      AND  r.name!='*'
      AND  l.timestamp > NOW() - INTERVAL r.timeout SECOND
    LIMIT 100

And have

l: INDEX(origin, timestamp)

If the DELETE is running too slowly, either increase the frequency of running it, or increase the LIMIT.  But I would not use more than 1000.

OK, the LIMIT will probably spit at you.  So, I'll provide another possibility:

DELETE  l
    FROM  locking l,
    JOIN  ( SELECT l2.id
              FROM locking l2
              JOIN locking_rules r  ON r.origin = l.origin
                                   AND l.timestamp > NOW() - INTERVAL r.timeout SECOND
              WHERE r.name!='*'
              LIMIT 100
           ) s  ON s.id = l.id;

But this will also suffer from scanning too much of locking to find the few rows that need deleting.

So, let's start over and break the task into two steps:

Find 100 ids to delete.  This is just a SELECT, so it has much less impact.
Delete those 100 (or fewer) via the PRIMARY KEY -- This is much less invasive.

thus:

CREATE TEMPORARY TABLE t
    SELECT l2.id
      FROM locking l2
      JOIN locking_rules r  ON r.origin = l.origin
                           AND l.timestamp > NOW() - INTERVAL r.timeout SECOND
      WHERE r.name!='*'
      LIMIT 100;
DELETE  l, t
    WHERE l.id = t.id;

Let's prevent the various connections from doing the "same" code at the "same" time.  (In pseudo client code:)

if (GET_LOCK('messy delete) == 1)
{
    perform the above code
    RELEASE_LOCK('messy delete');
}

It may be that the sporadic hangup is due to two DELETEs stumbling over each other, perhaps even having a 'domino effect' cascading into other connections.


Why does my hard-drive fill up when executing a query? I have a very complex SQL query that I execute in SQL Server Management Studio. As the query is running, I notice that my harddisk is filling up, until the point it's completely full (I'm talking a 10+GB increase in data).

I tried finding where this data is located, but can't find any large files and trying to find it by modified data through Explorer does not gives me thousands of folders and files. I went through a lot of them, but have a hard time identifying what I'm looking for.

So where does SQL Server typically hold all this data when executing a query?

ps. I know I need to do something about the query itself, but for now I need to know where data is stored.

Three suspects

tempdb
A big query may use tempdb
It may use it for spool, hash match, and spool
Can cap the size of tempdb
Can find this in SSMS
log
If you are in full recovery mode then a select may use the transaction log.
You can cap the size the transaction log but then the query just does not run.
Can find this in SSMS
Can change the recovery model in SSMS
swap file
As SQL asks for memory and the server is out of physical memory then the OS will use the swap file.  Not optimal. Cap the amount of memory available to SQL at like physical memory minus 1-4 gb.
Can cap memory in SSMS
Can cap page file at the OS
I think it is in Control Panel


What is the best configuration for a MySQL instance with a lot of databases and lot of tables? I have a MySQL database instance with more than 3000 databases in it. Each database contains more than 200 tables. Total data of all theses database comes around 100 GB. I am using Windows Server 2012R2 operating system with 4 GB of RAM. The RAM memory utilization of the server system was always showing very high. So I tried to restart the system and restart is not working. It is showing restarting for long time and not restarting. When I checked the logs I understood that there was a memory issue. What is the best configuration for the MySQL with above architecture? What do I need to do to make this work with out failure in the future?

[Warning] InnoDB: Difficult to find free blocks in the buffer pool (1486 search iterations)! 1486 failed attempts to flush a page! Consider increasing the buffer pool size. It is also possible that in your Unix version fsync is very slow, or completely frozen inside the OS kernel. Then upgrading to a newer version of your operating system may help. Look at the number of fsyncs in diagnostic info below. Pending flushes (fsync) log: 0; buffer pool: 0. 26099 OS file reads, 1 OS file writes, 1 OS fsyncs. Starting InnoDB Monitor to print further diagnostics to the standard output.

There is a reason you will need more RAM ... the INFORMATION_SCHEMA database.
What has to get populated in it ??? Let pick just two tables as an example:

INFORMATION_SCHEMA.TABLES
INFORMATION_SCHEMA.COLUMNS

Imagine (all the columns in the tables + the number of tables) multiplied by 3000 ???
The metadata would be need lots of RAM to map them out. This is not to mention mapping indexes, constraints, views plus dozens of other database objects.
A client my employer had once had a Galera Cluster with 11000 databases with close to 2 million tables.

It took at least 24 hours to start mysqld and the Galera components.
Crash recovery made this nightmare scenario worse

See my old posts where I discussed memory consumption by the INFORFMATION_SCHEMA

How is INFORMATION_SCHEMA implemented in MySQL?
Adding new tables -- memory usage increases
Do dormant databases impact server performance?
#1041 - Out of memory issue in mysql

WHAT YOU COULD DO
Please do the following:

Create a new MySQL instance on another server.
While the MySQL instance is empty, record how much RAM the server has free.
Create all the databases and tables in that instance but do not load any data.
After creating the databases and tables, record how much RAM the server has free.
Shutdown mysql
Wait like 1-2 minutes
Startup mysql
Record how much RAM the server has free and how long it took to start back up.

This will given a general idea (or a ballpark figure) how much RAM you will need to add.
Whatever number you choose to increase by for RAM, DOUBLE THAT PLEASE !!!
Increasing RAM will not reduce crash recovery time. Also, do not forget that DB Connections need memory love too (How costly is opening and closing of a DB connection?)


Create Oracle DB LINK to a database with no DB_DOMAIN I'm trying to create a database link between this two DBs.

My local DB is 11.2.0.3.

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MYDB.DOMAINNAME

SQL> select name, value from v$parameter where name in ('db_name', 'db_domain', 'global_names');
NAME                 VALUE
-------------------- ----------------------------------------
db_domain
global_names         TRUE
db_name              mydb

The remote DB is 11.2.0.2 configured as follows:

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
myremotedb

SQL> select name, value from v$parameter where name in ('db_name', 'db_domain', 'global_names');

NAME                 VALUE
-------------------- ----------
db_domain
global_names         FALSE
db_name              myremotedb

I want to create a DBLINK in the local database to the remote database.

I have my tnsnames (mydb's machine) configured as follows:

REMOTE-DATABASE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(Host = 192.168.1.2)(Port = 1521))
    )
    (CONNECT_DATA =
      (SID = myremotedb)
    )
  )

Then, when I try to create a database link in my local database, since global_names parameter is set to true, oracle appends the local domain name to the database link name

SQL> create database link myremotedb@myuser connect to myuser identified by mypasswd using 'REMOTE-DATABASE';

Enlace con la base de datos creado.

SQL> select db_link from user_db_links;

DB_LINK
------------------------------
MYREMOTEDB.DOMAINNAME@MYUSER

and if I try to use it, it fails

SQL> select * from dual@MYREMOTEDB@MYUSER;
select * from dual@MYREMOTEDB@MYUSER
                   *
ERROR en línea 1:
ORA-02085: database link MYREMOTEDB.DOMAINNAME@MYUSER connects to MYREMOTEDB

SQL> select * from dual@MYREMOTEDB.DOMAINNAME@MYUSER;
select * from dual@MYREMOTEDB.DOMAINNAME@MYUSER
                   *
ERROR en línea 1:
ORA-02085: database link MYREMOTEDB.DOMAINNAME@MYUSER connects to MYREMOTEDB

How can I create this database link in this scenario?

The remote db doesn't have a db_domain. How can I force oracle to create a DBLINK with no domain name?

Regards!

Can you show us the content of your sqlnet.ora file ?
The domain name should be listed there :

NAMES.DEFAULT_DOMAIN = domain.com.xxx

In case you don't have one , create a new sqlnet.ora file using the netca tool.
- after you create that file edit it and add the entry with you domain name!


Cross Database Chaining vs. Trustworthy Option Recently, we had a stored proc request that required access across multiple databases. Of course this is not uncommon to have but in our case, earlier we used service accounts and this service account had read/write permissions to all the databases.

But now we changed all permissions to windows sign on AD group and the users in the AD group do not have all permissions to all the databases.

Example:

AD group UG1 has 10 users who are users on Database A.
Stored procedure PROCA resides in Database A but has selects on the tables that belongs to Database B and Database C.
Execute permissions have been granted to the stored proc PROCA and the AD group UG1 as users (with no other permissions) has been created on Database B and Database C.
The database owners of the DatabaseA, DatabaseB and DatabaseC are under login XXX.

As we all know, such permissions across databases can be accomplished by

Option 1 : Switching on Database Cross chains ON all the databases, or
Option 2 : Giving the AD group UG1  required individual object permissions on the databases (B and C)  or
Option 3 : Using Trustworthy db options on DatabaseA and changing the stored proc PROCA to include execute as “dbo” user so that the permissions can impersonate.
Option 4: Using certficates

My question is what would be the best solution with respect to security and keeping it simple and safe?

Please note that none of the developers are database owners of any of the databases and elevated permissions are only assigned to DBA’s and hence the chances of someone hacking or SQL injecting or impersonating as sysadmin is “none”.

But again, I would like to know what your take would be in such scenarios? I prefer option 3.

Option 1 is the worst of all.. would not use that..

Option 2 a lot of administrative effort..

Option 3 (see last sentece :) )

Option 4 recently I had to think about how to secure databases.. My solution was this one. there is great article on security here. Even thought it was a bit complicated to use Certificates at beginning (never done with them anything before) I am very happy of this choice. I grant permissions to Certificate Login/User. I sign procedures by that certificate and everything works. I use the same system to grant permissions for dynamic SQL (so if there is sp_executesql I grant minimum rights to necessary tables to Certificate User, so caller of proc can not see tables and i do not have to make Execute as Owner or smthng). And the same to grant rights to use Asymmetric Keys. And I always know WHO was the caller of procedure (no Execute As), even though its not very important in my case..

Its a bit cumbersome to sign procedures during development time (trying to find good way around that). Currently I am searching procedure text (DDL trigger) for use of other database and if I find it, I print out warning, that procedure possibly has to be signed..

Another thing I miss is that its not possible to sign functions or views (there must be a good reason) so you cant use them to access other DB..

Last thing I would like to say- I would prefer to solve as many problems I possibly can with minimum variety of patterns.. So if you extensively using "Execute As" and proxy users then maybe Trustworthy option is better choice.


SSMS 2012 CSV exports Is there a way to prevent SSMS 2012 from adding the word 'NULL' to CSV exports?

Steps to reproduce behavior:

Create a new query (Ctrl+N)
Select Query | Results To | Results to Grid (Ctrl+D)
Run query: SELECT getdate() NOW, NULL DUMMY; results in one row and two columns, the second column displays ‘NULL'
Right click the results pane and choose Save Results As…; save the file as CSV
Open the file with Notepad (view the ‘raw’ values)

Actual result:

NOW,DUMMY
2015-10-16 10:25:28.403,NULL

Desired result:

NOW,DUMMY
2015-10-16 10:25:28.403,

Is there a setting that will produce the desire result?  Do newer versions of SSMS work differently in this regard?

You could use COALESCE(NullableColumn,'') for any column you don't want to export with NULL values.

That COALESCE will output zero-length strings instead.

Most of the time, however, it is important to know the difference between a zero-length string and a NULL value in exported data.

You're sample query would become:

SELECT getdate() NOW, COALESCE(NULL,'') DUMMY;

Although, I'd really prefer to see it as:

SELECT [Now] = getdate()
    , Dummy = COALESCE(NULL,'');

Putting the column alias at the beginning of the line makes it easier to find columns referenced in the output when doing debugging work down the road.


How can I get SQL Server Management Studio 2012 to connect to multiple servers automatically on open? When I open SQL Server Management Studio, it only connects to a single Database Server, but I would like it to automatically connect to multiple Database Servers.

How can I accomplish this?

In Management Studio, go to the View menu and then Registered Servers. Register each of the servers that you want to keep connections for. Then when you open up Management Studio in the future, you just have to go to your register servers and click the ones you need. You can also use the Central Management Servers feature if there are multiple people on your team that would like to share the registered servers. You can group them in various ways, such as by version, by environment, by location, etc.


Getting repeated value in a group_concat only if ids are different I have two tables, first the table Product:

id|category_id
--+-----------
1 | 12345
2 | 12345
3 | 12465

And then a table activity:

id|prod_id|activity_type   |description
--+-------+----------------+-----------
1 | 1     | Initialization | blah
2 | 1     | Finalization   | foo
3 | 2     | Initialization | blah again
4 | 2     | Duplication    | bar
5 | 2     | Finalization   | foobar
6 | 3     | Initialization | blob
7 | 3     | Migration      | A to B
8 | 3     | Migration      | B to C
9 | 3     | Finalization   | fuh

Now I want to retrieve for each type of activity the number of product having at least one of this kind of activity, and also the list of product category. The categories will be repeated in the list for each product of this category. For now I'm using the following query:

SELECT a.activity_type as Activity, COUNT(DISTINCT p.id) as Products,
CONVERT(GROUP_CONCAT(p.category SEPARATOR ',  ') USING utf8) AS Categories
FROM mydb.product p, mydb.activity a
WHERE p.id = a.prod_id
AND a.activity_type <> '' // To not count activities which haven't been correctly initialized
GROUP BY Categories
ORDER BY Products

Now what I await for result is:

Activity       | Products | Categories
---------------+----------+--------------------
Initialization | 3        | 12345, 12345, 12465
Finalization   | 3        | 12345, 12345, 12465
Duplication    | 1        | 12345
Migration      | 1        | 12465

But with this query I get the value '12465, 12465' for Migration. I could I get that a category appears on the list, only for each different product ids, but not for each activity of one type?

First group by both activity_type and prod_id and then another group by activity_type:

SELECT
    a.activity_type AS Activity,
    COUNT(DISTINCT p.id) AS Products,
    CONVERT(GROUP_CONCAT(p.category_id SEPARATOR ',  ') USING utf8)
      AS Categories
FROM
    product AS p
  JOIN
    ( SELECT activity_type
           , prod_id
      FROM activity
      WHERE activity_type <> ''
      GROUP BY activity_type
             , prod_id
    ) AS a
    ON p.id = a.prod_id
GROUP BY
    activity_type
ORDER BY
    Products DESC;

Tested in SQL-Fiddle (thank you @Mr.Radical)

You could also safely replace COUNT(DISTINCT p.id) with COUNT(*) in the above, as for every activity type, there are only distinct product IDs (this is taken care in the internal group by).


MySQL statement in Project Voldemort site I've just entered  Project Voldemort (a NoSQL key-value datastore) site, and saw something interesting:

  "Unlike MySQL replication, both reads and writes scale horizontally"

Well, seems like this statement tries to compare Valdemort with a MySQL traditional RDBMS. Yet, MySQL Cluster is also part of the MySQL solution family. It can scale writes and reads horizontally.

Wasn't this statement a little bit unfair to MySQL?
Can MySQL Cluster scale well at all?

AFAIK MySQL Cluster has the ability to scale, but it is really a manual process to do so. In fact, it takes more planning and more hardware to do such scaling. Here is why:
MySQL Cluster
According to "Guide to Scaling Web Databases with MySQL Cluster" (October 2011 Whitepaper), page 5 paragraphs 4,5 under the subheading 'Auto-Sharding':

MySQL Cluster is implemented as an active/active, multi-master database ensuring updates made by any application or SQL node are
instantly available to all of the other nodes accessing the cluster.
Tables are automatically sharded across a pool of low cost commodity data nodes, enabling the database to scale horizontally to
serve read and write-intensive workloads, accessed both from SQL and
directly via NoSQL APIs. Up to 255 nodes are supported, of which 48
can be data nodes ... MySQL Cluster excels at both aggregate and
per-node performance, eliminating the requirement to provision and
manage racks of poorly utilized hardware.

Scaling can be automatic, but within the confines of the MySQL Cluster architecture.
MongoDB
MongoDB can autoshard by means of running a routing program called mongos in front of shards. The router knows where data is located, so applications can connect to it and issue requests normally. There is no apparent limitations on the amount of servers to store data and communicate requests.
Conclusion
Just comparing MySQL Cluster to MongoDB, you find auto-sharding available in both. The major difference is that you are limited to 48 data nodes, even though a maximum of 255 servers can be configured for MySQL Cluster infrastructure (The remaining 207 maximum nodes make up SQL nodes [providing mysqld process and other APIs] and management nodes [providing ndb_mgmd process]). MongoDB does not have such an architectural limitation. MySQL Cluster can, by design, run out of elbow room.
Saying that" read and writes scale horizontally" would quickly apply to products that internally perform this. Using MySQL Replication, this statement holds true. Why? MySQL Replication is asynchronous (even MySQL 5.5 is Semisynchronous, at best), whereas MySQL Cluster and MongoDB (as well as other NoSQL Databases) are synchronous with the backing of ACID-compliance.


Database appearing in one tool but not another Can anyone think of a reason why a mySQL database would be accessible from one tool (phpMyAdmin, logged in as db_user) but not from another (Sequel Pro, logged in as db_user).

The user is the same in each case and has full rights on the database in question.

In the case that I can't see it, I can access all other databases I would expect to and there are no obvious errors being returned.

The only thing that separates the tools is that phpMyAdmin is running on the same machine as MySQL so uses localhost whereas Sequel Pro uses the domain name.

You will need to grant access on a user@hostname basis, not just the user alone. So you will have to grant access to 'sequel_pro'@'your_domain_name' to database_name.*. Click here for a sample script.


Select rows from T such that sum(T.quantity) is greater than an argument TLDR: I need a way to limit my select such that only rows needed to fill my new order are returned.

Hey, I'm looking for a better way to do a query on the simplified toy table below. Right now I do a straightforward select then do some additional logic in Python that processes rows that are fully or partially fillable.

id |name   | category |  price  | quantity
---+-------+----------+---------+---------
1  |Robby  | buy      | 45      | 4
2  |Blane  | buy      | 48      | 5
3  |Shawn  | buy      | 43      | 10
4  |Valva  | sell     | 62      | 9
5  |Chris  | buy      | 42      | 15

If I'm inserting a new sell order at price $40 and quantity = 12

SELECT id, name, price, category
FROM my_table
WHERE price > 40 AND category = 'buy'
ORDER BY price DESC;

Then in Python my logic looks something like this:

running_total = 0
for order in orders:
    running_total += order.quantity
    fill_order(order, new_sell_order)
    if running_total >= new_sell_order.quantity:  # Values is 12 in my example.
        break

I'd like a way to limit my select such that all rows are returned in order until a row is returned such that the sum of all quantity columns is greater than some value. Additional columns need not be returned (and thus locked in a repeatable read state).

If you don't need the "optimal fill", you could use a window function and something like this:

select *
from (
  select id, name, price, category,
         sum(price) over (order by price, date_created) as so_far
  from my_table
  where price > 40
    and category = 'buy'
) t
where so_far <= 12;

If you are mixing "groups" of orders, you might need a partition by clause as well.
Change the order by inside the window function to meet your ordering criteria.


Best practices for storing multiple SQL tables per user (built from uploaded csv file?) I have a few users who will be uploading a few types of predefined CSV files. I need to store these as sql tables to be able to run queries on this data for a particular user, however that means there's potentially going to be loads of tables.

Right now I'm storing the csv's in an s3 bucket with the key username/filename/timestamp-filename.csv

What would be the best way to transform it into SQL? I have CSV parsing done using nodes fast-csv, it's just the schema that's baffling.

I was thinking table names like data-username-filename so each user will have as many tables as csv categories, so 10 tables each. Would it be best to store this in a seperate DB or the same DB just differntiating by table name/prefix?

This is a backoffice app with only a few users.

One option is to use different schemas per user, and then tables with the convention you want.

CREATE TABLE '<user_id>'.'<filename>'...


Maintenance Cleanup Job Task Fails but Backup Jop is successful I get this error:

  Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'\\SQLSvr..." failed with the following error: "xp_delete_file() returned error 2, 'The system cannot find the file specified.'". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The server is running SQL Server 2008 R2 doing backups on one server to save to a remote server using UNC paths; file extension is ".bak".

Much as the error message states, it can't find the file N'\\SQLSvr..."

If you know the file does exist, then that usually points to a permission problem with either the account SQL Agent is executing as or if you've created it, a credentials proxy account. Either way, it can't access the network share or it does not have permission to perform the delete operation.

I'd first verify the account can access the share, then look at folder permissions and ensure it has delete privilege.


Return all rows with a certain value in jsonb dictionary in Postgres Let's say I've got a table like this where name is just a string and dicts is jsonb.

id  | name  |                         dicts
----+------+-----------------------------------------------------------
  1 | "bob"| {"food": "pasta", "exercise": "walking"}
  2 | "joe"| {"vehicle": "car"}
  3 |"john"| {"hobbies": "walking"}

And I want to return all rows that contain "walking" as a value within the key:value pairs in the dicts column. So in this case, I'd want to return the "bob" and "john" rows since "walking" appears as values in both rows. The number of key:value pairs in each jsonb dict can vary and the keys can also vary.

There's this command

select jsonb_object_keys(column) from table;

that at least gets me all the possible keys represented in a particular column, but that's the only lead I've got so far.

You can use jsonb_each_text (or json_each_text) function which expands outer most json object into key - value record. Here is the example query;

SELECT * FROM
(
    SELECT
        t.id,
        (jsonb_each_text(t.dicts)).*
    FROM
        my_table t
) denormalized_values
WHERE
    value = 'walking'

And the result of the query above will be;

| id |      key |   value |
|----|----------|---------|
|  1 | exercise | walking |
|  3 |  hobbies | walking |

<<fiddle>>


MongoDB explain winning plan shows "EOF" I've been working on optimizing my queries on some 2 million documents in MongoDB and I tried to use explain on aggregate functions, but it would display

"winningPlan" : {
      "stage" : "EOF"
    },

Prior to this, the function would show the winning plan with the stages such as "Fetch", but after I tried a few different syntax in writing the aggregate command now it is showing "EOF". I tried to simplify my command to a find().explain() function but it is still the same. Anyone has any ideas?

On a second note, has anyone figured out how to explain("executionStats") on an aggregate query. I see that the function has been implemented here, but when I run it I get "EOF" as well as a basic explain() results. Is it because my MongoDB is not updated to 3.5.5? Is this functionality supported for versions below 3.5.5? Many thanks in advance.

Ok, I found out why. Apparently I was querying against the admin database. Always run use <db> guys


Comparison operators give wrong results with keyset pagination I implemented keyset pagination on these tables

CREATE TABLE profiles (
    id serial primary key,
    username varchar(255),
    followers bigint,
    tsv tsvector
);

CREATE TABLE profile_categories (
    username varchar(255),
    category varchar(255),
    primary key (username, category)
);

with the following query:

SELECT profiles.*, categories,
       ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers) AS rank
  FROM profiles
  JOIN plainto_tsquery($1) AS q ON tsv @@ q
  LEFT OUTER JOIN profile_categories ON profiles.username = profiles_categories.username
  WHERE followers IS NOT NULL AND
    followers > 0 AND
    (ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers), -id) < ($2, $3)
  ORDER BY (
    ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers),
    -id
  ) DESC
  LIMIT 50;

The idea is to use ts_rank_cd to rank documents with full-text search queries, and to scale the rank by the logarithm of the followers.

The problem is that when I query for the second page by supplying the last rank and id ($2 and $3) of the previous page, I still get the last result from the previous page as the first result (so I effectively get only 49 new results)! I have no idea how this is possible since I'm using strict inequality operators.

EDIT Even if I cast the ranks to decimal the same problem persists -- I get duplicates results at the end of one page and at the start of the next:

SELECT profiles.*, categories,
       (ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers))::decimal AS rank
  FROM profiles
  JOIN plainto_tsquery($1) AS q ON tsv @@ q
  WHERE followers IS NOT NULL AND
    followers > 0 AND
    ((ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers))::decimal, -id) < ($2, $3)
  ORDER BY (
    (ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers))::decimal,
    -id
  ) DESC
  LIMIT 50

Floating point numbers are subject to representation/rounding errors.  When translated to the client and back again, it is unlikely to be exactly equal to the original.


Allow SELECT on specific column only I have a User table with 3 column; name, email, password, etc.

I would like to create another user that can only SELECT the name column.

SELECT name     FROM User;    -- Ok!
SELECT email    FROM User;    -- Not Ok!

Can this be done on MySQL?

For this you need to grant select permission to user (MySQL User) on that particular column of table.

GRANT SELECT (name) ON MyDb.User TO 'MySQLUser'@'MySQLHost';

For explanation have a look at MySQL Documentation on column Privileges


Mirroring between two Ms-sql servers for Migrating server from one Datacenter to other Datacenter I have a query about microsoft sql server. I'm looking for migrating ms sql server from one vendor to another vendor(Datacenter). I'm looking to have a mirror configuration between servers since my database is close to 40GB and would take days for copying from old to new, So once the database is synchronized, i would be taking down the old server and connect my app with new server.

My concern is if i take backup and restore it in the new server and start mirroring, Would the data will be mirrored where transactions happened between taking backup of the server and starting mirroring?

Thanks,
Swaroop.

This is perfectly viable (and is how I moved several databases before). The outline of the process is this:

Make sure the source database is in full recovery mode
Configure the source to no NO_TRUNCATE transaction log backup (so the log is preserved)
Full Backup the source. Preferably use backup compression
Take your full backup (on a USB stick if it takes to long to copy over the network) to
the target
Restore full backup on target
Take a log backup of the source
Move (using your fastest method as in 4) the log backup to the target an apply it there
Configure mirroring in async mode (source and target can now mirror)
Start regular transaction log backups again on the primary
Wait for mirror to catch up (use the mirror admin tools to monitor)
Set mirror to sync mode
Move the app pointer and manually fail over the mirror

A thing you have to be careful about is that you have enough space to hold the transaction log while the move is happening. But a 40GB database is tiny, so I assume you wont have a lot of tlog traffic on it.

Another thing to watch out for is that you have the same security set up on the target as on the source. If not, you may end up with orphaned users when you swing over the server. This is less of an issue if you only use Windows logins.

Getting this right takes a bit of practise. I would advise to test it out on a development system first to make sure you have all the steps scripted nicely.


What is 'Log Memory' in Query Store 2017 In SQL 2017 there is a new execution metric, 'Log memory' other than that it was added in 2017 I am not finding anything about it.

Execution metric: (SQL 2017)

  CPU time, Duration, Execution Count, Logical Reads, Logical writes, Memory consumption, Physical Reads, CLR time, Degree of Parallelism (DOP), Row count, Log memory, TempDB memory, and Wait times

I believe I understand what all the other metrics are and why I might care.

I ran all the metrics for the top 5 resource consuming queries, during several specific periods.  I recorded and now I am examining the results.  I know the (very large) values for 'Log memory' are in KB's.

What exactly is the metric 'Log memory'?

Edit, having received two answers I checked

The answer by LowlyDBA suggests it is a combination of 5 related fields from sys.query_store_runtime_stats

Using the code to validate provided by jadarnel27 in their answer

I created the database '231682' and ran the test query for the 5 fields, I got results very similar

I summed (used =SUM() in Excel) my values and got 1,383,040 (bytes)

I looked at Query Store, for Log memory used (KB), it shows a value of 354,058,240 (KB), this number is orders of magnitude larger, it is also KB as compared to bytes, in bytes it would be 354,058,240,000 (bytes)

I summed the totals of all the fields and only got 1,655,236 (byte)

SELECT *
FROM sys.query_store_runtime_stats qsrs
WHERE qsrs.avg_log_bytes_used > 0;

I suspect the answer to my question, is that the 'Log memory' metric in SQL 2017, is not any real value. The value presented in this small experiment would be 354GB, unrealistically high.

If we look at the documentation for the underlying object, sys.query_store_runtime_stats, we'll see it has the following descriptions:

avg_log_bytes_used - Average number of bytes in the database log used by the query plan, within the aggregation interval.Note: Azure SQL Data Warehouse will always return zero (0).
last_log_bytes_used - Number of bytes in the database log used by the last execution of the query plan, within the aggregation interval. Note: Azure SQL Data Warehouse will always return zero (0).
min_log_bytes_used - Minimum number of bytes in the database log used by the query plan, within the aggregation interval.  Note: Azure SQL Data Warehouse will always return zero (0).
max_log_bytes_used - Maximum number of bytes in the database log used by the query plan, within the aggregation interval.Note: Azure SQL Data Warehouse will always return zero (0).
stdev_log_bytes_used - Standard deviation of the number of bytes in the database log used by a query plan, within the aggregation interval.Note: Azure SQL Data Warehouse will always return zero (0).


join and select multi values for each row i have 2 tables, users and skills
in skills i save skill ids and each user can have multiple skills

now i want to select all users with each user's all skill ids

but with join i cant, and with sub query i get

  Cardinality violation: 1242 Sub query returns more than 1 row

how should query be written ?

Should be as simple as:

SELECT  U.ID, S.ID
FROM    SKILL S
INNER JOIN USER U      -- in case you want more user details in the results.
ON      U.ID = S.USER_ID
ORDER BY U.ID, S.ID

Will retrieve all the users and each skill they have.

If Skills is not a child of User (i.e. each skill can also be associated with multiple users) then you need a USER_SKILL table to link the two together. Then the query would be:

SELECT  USER_ID, SKILL_ID
FROM    USER_SKILL
ORDER BY USER_ID, SKILL_ID

In future it is a good idea to include the query you are using (which caused the error) in your question.


Is it possible to give PDB name to a procedure and select from it? The following example is not working, I just trying to show what I'm trying to do.
Given table:
-- this table is located at the CDB
create table t1 (tab_name varchar2(35))
/
-- this proc is located at the CDB
Create or replace procedure FindTables(vPdb in varchar2, vOwner in varchar2)
BEGIN
  insert into t1(tab_name) select TABLE_NAME from ALL_TABLES@vPdb where OWNER=vOwner;
END;
/

Is there a way to create a temporary DB link or something like that? or any other way?
EDIT
I need also a solution for user tables.

Sorry, but this is just wrong. A dblink can not be provided as a parameter like that. And a dblink is not required at all for this.
You can access information about all tables in all PDBs using the CDB_TABLES view.
CDB_* Views

For every DBA_* view, a CDB_* view is defined. In the root of a
multitenant container database (CDB), CDB_* views can be used to
obtain information about tables, tablespaces, users, privileges,
parameters, and so on contained in the root and in pluggable databases
(PDBs).

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs

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

SQL> select table_name from cdb_tables t join v$containers c on t.con_id = c.con_id
     where c.name = 'PDB1' and t.owner = 'BP';

no rows selected

SQL> select table_name from cdb_tables t join v$containers c on t.con_id = c.con_id
     where c.name = 'PDB2' and t.owner = 'BP';

TABLE_NAME
--------------------------------------------------------------------------------
T1

SQL>

You can easily provide the PDB name and table owner as parameters to a query like above.


Does the mysql parameter "max_connections" apply for unix socket connections? The documentation does not specify if max_connections apply only to TCP connections.

The way it is said, it looks like it does apply for any kind of connection, but I'd like to be sure because otherwise, I could use it when the "Too many connection" problem arises, even if the (last) SUPER connection slot is already in use.

This limit applies to the total TCP socket connections and Unix socket connections, combined, so connecting via the Unix socket won't help you if your connection count reaches the configured max.

However, the slot reserved for a user with SUPER privilege is not a single slot in the way your question implies.  Any time the server's current client thread count is equal to the value of max_connections, one more user with the SUPER privilege will be allowed to connect, regardless of how many SUPER connections are currently already established.

So the max+1 allowance refers not to a single slot that can be occupied and whose occupancy is remembered, but rather to an exception in the code that bypasses the logic that denies new connections connections when current == max and new attempt comes from a user with super, each time that condition is encountered.

This is one good reason, among others, that your application should never use an account with SUPER.


Comparing Tool for Reporting Services Objects I have three Reporting Services Instances (Server), all in version of SQL-Server 2017.

All instances have the same tree structure with hundreds of objects (datasources, datasets and reports)

Now I'm looking for a tool which compares the same objects between two instances. I already use the redgate comparing tools but they do not cover reporting services.

Any suggestions?

So basically what @saihtam8 said in his comment.

This SO thread might be helpful to you: Where does a published RDL file sit?

Essentially just compare the report BLOB columns (Content? in SQL Server 2008 R2) in the Catalog tables across each server instance.
In SQL Server 2008 R2, it looks like there are DataSets and DataSource tables as well which you can compare.


Grant access for any logged in user for common database In SQL server 2008, I have one database that is a common database that is accessed thru stored procedures in all other databases. Each of the other databases have their own login.

I need a way that all logged in users can access that common database. I know I could create a user for the common database for each login but I would need to create over 100 users in the database then.

Is there a better way?

I believe you can create a user account in that database, using WITHOUT LOGIN. This will create it where you can grant execute as permissions to the other users for any particular stored procedure in that common database. They don't have to use a password for it so you control it by who can EXECUTE AS with it. Here is a pretty good write-up on how it could be used, near the end he talks on how it can be used outside the context of the database.


ORA-00933 With Match_recognize Am trying to figure out was wrong with this simple query that uses MATCH_RECOGNIZE. Am getting the error ORA-00933: SQL command not properly ended. What could I be missing?

    SELECT EMPLOYEE_ID,DEPARTMENT_ID,SALARY FROM EMPLOYEES
MATCH_RECOGNIZE(
PARTITION BY DEPARTMENT_ID
ORDER BY HIRE_DATE
MEASURES
 EMPLOYEE_ID,DEPARTMENT_ID,SALARY
ONE ROW PER MATCH
PATTERN( A B* )
DEFINE
      B AS ( B.SALARY < PREV(B.SALARY))
)

When I first ran your query on SQL*PLUS (Oracle 12.2)-

SQL>  SELECT EMPLOYEE_ID,DEPARTMENT_ID,SALARY FROM EMPLOYEES
MATCH_RECOGNIZE(
PARTITION BY DEPARTMENT_ID
ORDER BY HIRE_DATE
MEASURES
 EMPLOYEE_ID,DEPARTMENT_ID,SALARY
ONE ROW PER MATCH
PATTERN( A B* )
DEFINE
      B AS ( B.SALARY < PREV(B.SALARY))
)  2    3    4    5    6    7    8    9   10   11  ;
 EMPLOYEE_ID,DEPARTMENT_ID,SALARY
            *
ERROR at line 6:
ORA-62505: expression needs to be aliased

I didn't get ORA-0933 as you have mentioned. But when I ran your code on Oracle 11.2.0.4 then I got the same error as you have said. And the reason is, the MATCH_RECOGNIZE clause is added on analytic function syntax from Oracle 12c only.

Then on 12c, I corrected the error as-

SELECT emp_id,dept_id,SAL FROM EMPLOYEES
MATCH_RECOGNIZE(
PARTITION BY DEPARTMENT_ID
ORDER BY salary
MEASURES
 EMPLOYEE_ID as emp_id,
 DEPARTMENT_ID as dept_id,
 SALARY as sal
ONE ROW PER MATCH
PATTERN( A B* )
DEFINE
      B AS ( B.salary < PREV(B.salary))
);

which runs perfectly for me, at least without any syntax error.


How to have a "kinda" unique column How can I have a MySQL database number column that only allows one 1, but infinite 0's? Some type of constraint or something.

Elaboration

To clarify what I mean and why I want this:

Imagine you have a MySQL table (let's say "accounts"). An account can be "assigned" to multiple people, but only one person can "own" it. This is in a way similar to bank accounts or Netflex.

So, the schema might look like

Accounts
[id] [name]

Account_membership
[account_id] [user_id] [is_owner]

Here's the rub: You can only have one owner. But, You can have infinite non_owners who are members. How can I ensure that this is the case?

A unique constraint won't work for this, because (1, 1, 1), (1, 2, 0), (1, 3, 0) are valid rows.

So, is there some way I can accomplish this with mysql?

In MySQL multi-column unique constraints are implemented such that they allow multiple null values. You can make use of this by using nulls to represent the non-owners:

create table account_membership (
  account_id int not null references accounts (id),
  user_id int not null references users (id),
  is_owner boolean null check (is_owner in (1, null)),
  unique key (account_id, is_owner)
)

The above will allow multiple null values in is_owner for each account_id, but only one 1.

Note that this won't be portable as other databases treat nulls in unique constraints differently (i.e. only allow one null value).


Remote Connection to SQL-Server for data extract I have zero experience with MSSQL Server, but I'm trying to determine the most effective way to extract data from table into flat files from a remote location.
Here's the usage case scenario:

We are a data-integration company and we have some customers who run software ( ERP ) on top of a MS SQL Server. The ERP vendor previously supported a extract and delivery process directly to our ( company ) location. The ERP company has now become unresponsive to our ongoing extract issues ( bugs ) and we're preparing for the possibility of losing customers as a result (even though we have a contract with the software company).

I'm assuming that there's a way for us to connect directly to the MSSQL server and extract the data, bypassing the ERP vendor extract solution altogether, but I'm not familiar w/ MSSQL or Connections / Connectors at all.

Would anyone be willing to share with me if this sort of thing is possible and what the best route for me to continue my research might be?

As @FutbolFan said, best approach is to use SSIS (which takes a bit more time) or SQL Server Import/Export Wizard (the feasible option, in your case).

Right Click on the Database in question ans Select Tasks -> Export Data (the wizard will open up)
Select The Database in question as your source (might have to switch between SQL 10.0 or 11.0 depending on your version.
Select Flat File as your destination.  You'll be able to customize your delimiters and text qualifiers here
Select the Object you want to export.  You can do this by using the Object selector, or by using a custom query.  Up to you.
Run the package for each table you want to export, verify your results.


SQL Query to find Records companywise I want a query that will display rows of those COMPANIES who are giving theft cover and key loss Having status is yes
See the Sample Data below :

    addon       status  amt1    amt2    company
    theft cover yes      7       7      comp1
    key loss    yes      2       2      comp1
    theft cover no      NULL    NULL    comp2
    key loss    yes      2      33      comp2
    key loss    yes      1       1      comp3
    theft cover yes     12      22      comp3
    theft cover yes     11      22      comp4
    key loss    no      NULL    NULL    comp4
    theft cover yes     22      55      comp5
    key loss    yes     33      44      comp5

My query:

SELECT     addon, status, amt1, amt2, company
FROM         test
WHERE     (addon = 'theft cover' OR
                      addon = 'key loss') AND (status = 'yes')

Please help me. The above query is not working.

This query gives you company names:

SELECT company
FROM @Table
WHERE addon IN ('theft cover', 'key loss') AND status = 'yes'
GROUP BY company
HAVING COUNT(DISTINCT addon) = 2;

It gets all rows with requiered values (WHERE clause)
It GROUP BY company names in order to count how many of them are present
It only keeps companies with a COUNT of DISTINCT values of 2 (='theft cover'+'key loss')

Output:

company
comp1
comp3
comp5

You can then join it with you main table and get each associated rows:

SELECT t.addon, t.status, t.amt1, t.amt2, t.company
FROM @Table t
INNER JOIN (
    SELECT company
    FROM @Table
    WHERE addon IN ('theft cover', 'key loss') AND status = 'yes'
    GROUP BY company
    HAVING COUNT(distinct addon) = 2
) as c
    ON c.company = t.company
-- where t.compny = 'xxx'
-- where at.mt1 = 7 or t.amt2 = t.amt1
-- where ...
;

Output:

addon       status  amt1    amt2    company
theft cover yes     7       7       comp1
key loss    yes     2       2       comp1
key loss    yes     1       1       comp3
theft cover yes     12      22      comp3
theft cover yes     22      55      comp5
key loss    yes     33      44      comp5

Sample data:

Declare  @Table table([addon] varchar(20), [status] varchar(3), [amt1] varchar(4), [amt2] varchar(4), [company] varchar(5));

INSERT INTO @Table([addon], [status], [amt1], [amt2], [company])
VALUES
    ('theft cover', 'yes', '7', '7', 'comp1'),
    ('key loss', 'yes', '2', '2', 'comp1'),
    ('theft cover', 'no', NULL, NULL, 'comp2'),
    ('key loss', 'yes', '2', '33', 'comp2'),
    ('key loss', 'yes', '1', '1', 'comp3'),
    ('theft cover', 'yes', '12', '22', 'comp3'),
    ('theft cover', 'yes', '11', '22', 'comp4'),
    ('key loss', 'no', NULL, NULL, 'comp4'),
    ('theft cover', 'yes', '22', '55', 'comp5'),
    ('key loss', 'yes', '33', '44', 'comp5');


SQL Server 2008 mirroring migration to SQL Server 2012 always on Quick question, request your input.
I am migrating a SQL Server 2008 R2 instance with database mirroring to SQL Server 2012 with always on.
Should I :

Restore the SQL Server 2008 R2 backup to SQL Server 2012 primary and mirror (with no recovery), setup the mirroring and then do the post upgrade steps like dbcc checkdb, update stats etc only on primary
OR

Restore the SQL Server 2008 R2 backup to SQL Server 2012 primary. Complete the post upgrade steps like dbcc checkdb, update stats etc on primary. Then take fresh backup of this instance and setup the SQL Server 2012 always on on secondary.

Thank you in advance for your kind recommendations

It really depends on your downtime window.

My preferred steps would be :

Create a full backup of the SQL Server 2008 R2 database and restore it on the SQL Server 2012.
Perform all of the post-restore steps.
Add the database to an Availability Group.

With modifications as per your environment, you can probably automatically add databases using T-SQL script.


DB Locks - How would one handle long transactions and maintain high concurrency? We're maintaining a POS/WMS Application built in powerbuilder that runs on SQL Server 2008 R2 backend. Recently, we've been having a lot of locking issues and I believe it has to do with how we handle our transaction. We're considering of enabling read-committed snapshot on our DB, but according to this answer, it seems that won't really solve anything and is prone to introduce more issues later on.

The whole process of ordering products to shipping is very intricated (The application has been in use for 19 years and has been consistently maintained by numerous developers). Here's a pseudocode of how a batch ordering process uploaded from an excel file is locking up our DB:

Load all items from excel files
Begin Transaction
Loop per item
    Perform all validation checks, such as checking current stocks, authorization to order etc
    (There's a lot of complex checks going on here)
    Insert new item to order table
    If an error occurs during insert, rollback and return
End Loop
Commit Transaction

Since our order table is within a transaction, this means other users must wait until the whole job is done to perform any operation on it. This particularly is causing a lot of issues for warehouse employees, where they must wait for a long time until they can mark the items as shipped on our order table before actually shipping them.

One might say "The transactions should be kept as humanly short as possible", but the thing is, if any error occurs during this order process, the whole job should be rollbacked to initial state. This is part of the program requirement.

I'm not a DB expert, so I maybe missing something critical here but given our requirements, it seems like the only option is to enable row versioning on our transactions.

I'd really like to know how this type of issue could be resolved.

Firstly the batch order should be validated first and then committed.  The transaction that commits the inserts into the database should as you say be as short as possible.  In the perfect world this would mean that you do all the validation up front before beginning the transaction and committing.

From what you have said I doubt such a restructure is on the cards, so I would suggest investigating why the transaction is locking the whole table.  Ideally such an insert would only have row level locks.

Depending on the structure of the data it may be possible to open a transaction and commit after each row.

Lastly, it maybe be possible to batch up the data and send it all to the database for processing.

All of these options will require a non-trivial amount of changes.

Edit:

Given your comments it would seem that you need this batch process to happen in a single transaction.  This would indicate that the locking is correct.  The next step is to understand what is causing the batch process to take so long.  If the batch process is taking a considerable amount of time there is either an issue in the queries that read the data that is used for validation or how the data required for the validation is stored.

You will need to start looking at each query that is run as part of the validation to understand what is taking the longest and then optimise that query and so on until the whole process is much more streamlined.


SQL Server LicenseType is disbled I have installed SQL Server 2016 Enterprise and applied the license key but

SERVERPROPERTY('LicenseType') is disbaled and
SERVERPROPERTY('NumLicenses') is null

There is no key in the registery such as following for SQL Server 2016:

  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.SQL2016\Setup

Any idea how can I change LicenseType of SQL Server?

If you read official documentation of Serverproperty (T-SQL), it says

LicenseType:   Unused. License information is not preserved or maintained by the SQL Server product. Always returns DISABLED.
NumLicenses: Unused. License information is not preserved or maintained by the SQL Server product. Always returns NULL.

SQL Server does not stores license information anywhere inside its mostly on paper or in some kind of agreement between you and Microsoft or you and Vendor who is providing the software copy. Having said that you can get very limited information when you run select @@version

Any idea how can I change LicenseType of SQL Server?

You simply cannot unless you buy a totally different license.
PS:Dont worry your SQL Server installation is fine


Design: How to account for a custom product in a transaction item table This question is related to Database Design: inventory and sales system?

So for example a burger is a Product which is made up of Component bun, meat, cheese, pickles etc. When this burger is sold it can be customized by adding/removing/modifying components.

How is this customization accounted for as a line item? Would there be another table ProductCustomization with a reference to the Product and the Component and a column to indicate the type of customization? What about if you want to modify the Composition of a Component in the Product (ex. 3 pickles per burger).

An example of a customization would be ordering a burger with no pickles, or adding extra cheese.

Since first starting to type up an anser I have argued myself into, and out of, a set position. I think this question steps over the border from the science of normalisation into the craft of data modelling. The answer will be determined by how you (or, more importantly, your users) understand the semantics of the situation. Is "extra cheese" a new "thing" which is sold, just like "burger" is a thing which is sold, or do they see "extra cheese" as a simple modification of the component list and "burger" is still the product?

I'll take a different business area to show the difference more clearly. If two people each bought a Ford Focus but with different engine, radio, wheels, paint etc., would you think they had bought esentially the same "thing"? Chances are you would. Even though different specifications go into the factory, the item that emerges is thereafter treated as a single unit for sales, transport, handling, support and all other purposes. However, if one customer orders, say, a tow bar that would be a post-factory option and would be handled through different sales, distribution, stock keeping, and maintenance channels, so is likely to be seen by the business as a separate product.

So to the burger. If a burger with extra cheese is still a burger then model it as variations to the composition. If "extra cheese" and "no cheese" are seen by the users as products in their own right model them thus, with their own composition and price, which may be negative.

In term of line items, treating the variations as separate products is the easiest. Once set up in Product and Composition they will flow naturally through to the invoice and stock totals just like the standard products will. You may need to add a product dependency table to ensure the variations make sense ("no cheese" only applies to cheese burgers; can't order "cola with extra cheese").

For the scenario where there is a single product with variaitons I would model it thus:

Customer -< Order -< LineItem >- Product -< Composition >- Component
                        |                                   |
                        +-< ProductCustomization >----------+

ProductCustomiation will have an AddRemove column to say whether its "extra cheese" or "no cheese". It will also have a price variation column which is used with Product.Price when calculating the LineItem.Value.

By making ProductCustomization bridge LineItem and Component I'm allowing for a customer to specify any variation she chooses. If the business only want certain combinations to be available these have to be modelled separately. I specifically do not link variations to Product as her ordering extra cheese does not changes the basic definition of what a burger is, and you do not want to be defining new Products for every possible combination of Components.

The price of a customization can be hard to pin down. Is cheese on a burger the same Component as cheese on a pizza? Is extra cheese on a 10" pizza the same value as on a 20" pizza? Where you store the price of a Component-as-variation will depend on the rules your business uses to determine this price.

Similarly with product/ component dependencies. If you're allowing complete freedom of customization it is easy. Once you start limiting, the combinatorial explosion can get really large quickly.


How to estimate the disk space for 1 million rows if I have a table of fewer rows in InnoDB MySQL I want to estimate the disk space for 1 million rows.

Currently, I have 8527959 rows, Data Length of 744 MB and Index Length 989.4 MB.

Please let me know if my calculations are correct.

Total Data Length for 1 million rows

Data of 744MB consists of 46500 pages of 16KB blocks (InnoDB page size)

If 46500 pages consist of 8527959 rows, 1 page consists an average of 183 rows.

So, 1 million rows need (1,000,000/183) pages= 5465 pages of 16KB

So, 1 million rows of data need 87.4MB.

Total Index Length for 1 million rows

Indexes of of 989.4MB consists of 61837 pages of 16KB blocks (InnoDB page size)

If 61837 pages consist of 8527959 rows, 1 page consists an average of 138 rows.

So, 1 million rows need (1,000,000/138) pages= 7247 pages of 16KB

So, 1 million rows of data need 115.9MB.

Total disk space (approximate) for 1 million rows

Total data length+Total Index Length = 87.4MB + 115.9 MB = 203.3 MB

Is this correct?

Your basic assumptions are correct, it is easy to see that the allocation per row is just under 100 bytes per row, and a bit more for the indexes, giving you a 200 * 1M = 200M of total size.

However, several important things have to be taken into account:

The tables must have identical structure and equivalent content- e.g. the same exact table with similar sized variable content.
With SHOW TABLE STATUS you are getting an estimation of size (rows, total size) based on sampling- it may not be accurate, specially if large changes happened to the table and automatic sampling didn't kick-off yet. You can force statistics gathering with ANALYZE TABLE command, but it can never guarantee you 100% accuracy
Everything is related to the logical size, not to physical size, or size on disk. If you were to delete 7/8ths of that table, the disk used on disk wouldn't shrink in the case of InnoDB, because disk defragmentation wouldn't happen- space is freed for other rows to be inserted, but it wouldn't be released to the filesystem. To reduce the on-disk size, you would need to recreate the table with OPTIMIZE TABLE, a more time consuming and blocking operations.
If disk space is a concern, consider using compression


How should we handle rows which won't be queried once they are old in PostgreSQL? We have a table in a PostgreSQL database which is growing on the order of millions of rows per day.

Each row consists of:

ID
Foreign user ID
Date and time
Other data

The date and time aren't strictly monotonic with ID, however they are close.

When querying this table we are only interested in getting rows for a given foreign user ID, with a date and time in the last two weeks. Rows more than two weeks old will never be queried, however we'd like to keep them for archival purposes.

Given this special use case:

Should we have an index on the date and time column?
Is there any hint we can give that the date and time is (almost) monotonically increasing with ID.
Should we look at trying to remove rows older than two weeks from the table, is that likely to improve performance?

Partition and/or use partial indexes, so you only have an index for the part you care about
No, AFAIK there are no correlation hints in Pg yet. Pity.
Depends a lot on the type of queries, but it can be worth doing, especially if you can do it by partitioning.

Partition

This is a classic use for table partitioning. Partition the table into two (or more) parts, one with the hot data and one with the old archival data. Index anything you need to on the hot table, and use fewer indexes to save space and save insert time on the cold table.

With constraint exclusion enabled, Pg will automatically scan only new_table when your queries match the partitioning constraint.

If your queries don't all use the same constraint you can either add a where test that does use the constraint, or just query the new_table partition directly.

Partial indexes

Alternately, you can create partial indexes that only cover the ranges you're interested in. Again, these will only work if your queries use constraints Pg can recognise as matching the partial index. In your case you'd have to create and drop them over time to keep your time range relevant, since you can't index on a dynamic expression like WHERE (some_field > current_timestamp - INTERVAL '2' week).


RESTORE failing with OS error 32, file being used by another process I am writing a set of procedures for (partially) automating DB deployment for a customer, which mostly works fine, but sometimes a RESTORE command is failing with the OS error 32, about a file being in use by another process (details below).

I have googled this extensively but have found little that applies to my specific case.  I suspect that there's something that I am overlooking, but I just can't seem to find it.

Here's the command:

RESTORE DATABASE [NBBC_Logistics] FROM DISK = '\\wpdboardq01\Shares\DbCopy\DevBackups\NBBC_Logistics_140916112310.bak'
    WITH FILE=1, NOUNLOAD, STATS=10,
     MOVE 'NBBC_Logistics' TO 'D:\MSSQL2K12\MSSQL11.MSSQLSERVER\MSSQL\DATA\NBBC_Logistics.mdf',
     MOVE 'NBBC_Logistics_log' TO 'D:\MSSQL2K12\MSSQL11.MSSQLSERVER\MSSQL\DATA\NBBC_Logistics_log.ldf',
     REPLACE
    ;

This results in the following error message:

Msg 3634, Level 16, State 1, Line 11
The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'D:\MSSQL2K12\MSSQL11.DEV\MSSQL\DATA\NBBC_Logistics_log.ldf'.
Msg 3156, Level 16, State 8, Line 11
File 'NBCC_Logistics_Model2_log' cannot be restored to 'D:\MSSQL2K12\MSSQL11.DEV\MSSQL\DATA\NBBC_Logistics_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 11
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 11
RESTORE DATABASE is terminating abnormally.

Some things to note:

This only happens on some of the receiving SQL Instances most of them are executing very similar commands with no problem.
The instance that is failing is one where there are multiple SQL instances on the same box (DEV and QA) and it is trying to restore a DB backup from DEV into the QA version of the same database.
Other DBs in this same instance can execute their corresponding RESTORE command without any problems.
It may be relevant that the source logical file names (shown in the error) are different from the DBs existing ones (named in the command), however, I beleive that I have cases where this does work.
Also, note carefully, that the file path reported by the error is NOT what I am specifying in the MOVE, but rather the original file path (which does still have the original DB files in use by the DEV instance).

So it appears that it is trying to first RESTORE the DB files to their original path locations, and only then MOVE them to the path I tell it to.  That is in contrast to what the doc says it does, and obviously would be compeltely unworkable in general, as someone RESTOREing a DB copy has no control over where the original files were and could not gaurantee that such paths existed and were not already in use.

Any help would be greatly appreciated.

Just to head off some of the automatic responses that don't apply:

The target database is not currently in use by me or anyone else.
Nor does it show up in sp_lock
The command specifies REPLACE, it is supposed to overwrite the existing database
The files specified are not open by any other windows processes
however, the file mentioned in the error are open by the other Sql Instance on the box
the BAK file location on a share has nothing to do with it, copying it locally changes nothing, nor does renaming it.
the db files and paths specified are the correct ones for the target database

When using the backup files please make sure to use the logical filename. When using multiple backupsets within the same backup files, please make sure to check that the file being used is correct.

In this instance:

  File 'NBCC_Logistics_Model2_log' cannot be restored to 'D:\MSSQL2K12\MSSQL11.DEV\MSSQL\DATA\NBBC_Logistics_log.ldf'. Use WITH MOVE to identify a valid location for the file.

It looks as though you have the wrong logical file name or the wrong backup inside a backupset. I know you pointed this out, but it would make sense to interrogate the information in the backupset and double check.


Cassandra writing (too?) large partitions I have a Cassandra cluster running for quite some time now (months) and, as expected, it has been consuming large quantities of data every day.

In the past few days the cluster has been having problems because one of the nodes keeps crashing and I don't see much in the logs.

One thing that stands out is the following warning but I'm now sure if it is related:

insufficient space to compact all requested files. 846201.56MB required

which is quite more than it is available. Nevertheless, even if there was enough disk to support such compaction, it seems excessive, doesn't it?

Does anyone have any idea what my problem is?

thank you for your attention.

By default, Cassandra uses SizeTieredCompactionStrategy that compacts several files (4 by default) of similar size into bigger file. These files contain multiple partitions, so big file size isn't a necessary sign of wide partitions.

When you're using Cassandra (especially with SizeTieredCompactionStrategy) you need to have ~50% of disk space free so Cassandra will able to write data during compaction - after compaction happens, the old files will be removed.

You may also consider the use of other compaction strategies, such as, LeveledCompactionStrategy, but it's more suited for read-heavy workloads (like 90% of all operations are reads).

You can read more about compaction strategies in documentation, and about size tiered compaction in this blog post.


Memory utilization per database - SQL Server I am trying to find the memory utilization per database(using dmvs) in a sql server instance,
ie (Buffer Cache + Procedure Cache) over a period of time.
 I have the query to get the buffer cache utilization, what i require is procedure cache, plus any other parameter that a database will use in terms of memory.

-- Get total buffer usage by database for current instance

SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);

Also if this can be compared with some perfmon counter info, it would be great

SELECT db = DB_NAME(t.dbid), plan_cache_kb = SUM(size_in_bytes/1024)
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.dbid < 32767
GROUP BY t.dbid
ORDER BY plan_cache_kb DESC;

This tip I wrote on mssqltips.com might be useful as well if you want to dig deeper into buffer usage.


Is it possible to make one column readonly? I'm curious if it is possible to create a table with a column that can never be changed, but the other columns of the table can.

For instance I could imagine a CreatedByUser column that should never be changed.

Is there a built-in functionality in SQL Server for this, or is it only possible via triggers or something else?

There is no built in declarative support for non updatable columns (except for specific predefined cases such as IDENTITY)

This Connect item requested it but was rejected. Add DRI to enforce immutable column values

An UPDATE trigger would probably be the most robust way of achieving this. It could check IF UPDATE(CreatedByUser) and raise an error and rollback the transaction if true.


Completely disable all support for a certain CHAR SET in MySQL? Is it possible to completely disallow certain charsets in MySQL and force an error and/or force everything into the server's chosen default charset?

Sessions can negotiate its own character set (SET names). That was a problem because, at least for some time, the PHP connector selected its own character set disregarding the MySQL server preferred one (default_character_set).

There are several things that people have done to handle that. If possible, fix the client configuration or enforce it on connection. That is the preferred method. Other people have decided to let the client take control of the encoding -Wikipedia does that, storing everything into blobs, but they had very specific requirements, I would not recommend it in general.

If you really want to go in your direction, you must recompile MySQL with the chosen -DDEFAULT_CHARSET and -DWITH_EXTRA_CHARSETS=none.


Apply pivot on SQL Server table I have a table which gives the output when queried normally. I want to transform rows to columns and columns to rows to show it in a report.

I want the tranformation to be applied as shown in the below image.

Other than BRk, CLR, BIN and SDC, they need to be categorized under Others in the above desired table.

Below is the table's DDL and DML.

  SET ANSI_NULLS ON
  GO

  SET QUOTED_IDENTIFIER ON
  GO

  CREATE TABLE [dbo].[DtlChrg](
  [ID] [uniqueidentifier] NOT NULL,
  [ChrgCode] [nvarchar](10) NULL,
  [PromotionInd] [nvarchar](1) NULL,
  [ChrgAmt] [decimal](15, 6) NULL

  CONSTRAINT [PK_DtlChrg] PRIMARY KEY CLUSTERED
  (
  [ID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
  IGNORE_DUP_KEY =OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
  ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  GO

   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'BRK','Y','100')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'BRK','N','200')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'BRK','N','55')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'CLR','Y','140')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'CLR','N','250')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'CLR','N','5')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'SDC','Y','100')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'SDC','N','300')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'SDC','N','552')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'BIN','Y','500')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'BIN','N','20')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'BIN','N','25')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'SLC','Y','540')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'SLC','N','220')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'SLC','N','245')

Thanks..

If you don't know in advance the name of the columns you want to put in your 'Other' column, you have to use something else in addition to the PIVOT statement.

You can try something like :

select  t.PromotionInd  as [Promo Id],
        'BRK'           as [Charge ID],
        t.[BRK]         as [Amount],
        'CLR'           as [Charge ID],
        t.[CLR]         as [Amount],
        'SDC'           as [Charge ID],
        t.[SDC]         as [Amount],
        'BIN'           as [Charge ID],
        t.[BIN]         as [Amount],
        'Other'         as [Charge ID],
        o.Amount        as [Amount]
from (
    select PromotionInd, sum(chrgAmt) as Amount
    from DtlChrg
    where ChrgCode not in ('CLR', 'SDC', 'BRK', 'BIN')
    group by PromotionInd
    ) o
join (
   select p.PromotionInd, p.BIN, p.BRK, p.CLR, p.SDC
   from (
       select PromotionInd, ChrgCode, chrgAmt
       from DtlChrg
        ) s
   pivot
       (
       sum(chrgAmt)
       for ChrgCode in ([CLR], [SDC], [BRK], [BIN])
       ) p
   ) t on o.PromotionInd = t.PromotionInd

(but not very nice to have several columns with the same name or with spaces inside...)

(and you can't have a "double header" as in your example, this is something you should build in your reporting tool, not directly in SQL I think)


Default values for crosstab results I'm trying to write my first crosstab query in Postgres. Some of the results have no entries and so the rows that are returned have null entries. How do I set a default value for those entries?

Here is a row that is returned:

-[ RECORD 1 ]-------------------+-----
username                        | some name
some_column_name_1              | 2
some_column_name_2              |

Here is my crosstab query:

SELECT *
FROM crosstab( 'select username, stage, count from some_view order by 1,2')
     AS final_result(username TEXT,
         "some_column_name_1" BIGINT,
         "some_column_name_2" BIGINT,
);

I've tried little things like this with no luck:

"some_column_name_1" BIGINT 0
"some_column_name_1" BIGINT default 0
"some_column_name_1" BIGINT set default 0

More importantly, if ...

  some of the results have no entries

You need crosstab(text, text) - the variant with 2 input parameters instead of crosstab(text) you are using now to avoid incorrect results. Detailed explanation:

PostgreSQL Crosstab Query

So, assuming the respective values in the column stage are 'some_column_name_1' and 'some_column_name_2':

SELECT username
     , COALESCE(col1, 0) AS some_column_name_1  -- sets default for NULL
     , COALESCE(col2, 0) AS some_column_name_2
FROM   crosstab(
   'select username, stage, count from some_view order by 1,2'
  , $$VALUES ('some_column_name_1'), ('some_column_name_2')$$  -- !!!
   ) AS final_result (username text, col1 bigint, col2 bigint);

And COALESCE provides the default value for NULL that you asked for - like AMG commented.


Rebuild system databases to a different location Firstly, forgive my poor question title. I couldn't think of the best way to summarise this.

Essentially I'm putting together some scenario based DR documentation, with this specific scenario being a SAN issue where we have lost disks which hold either log or data files.

I'm assuming that MASTER has been affected (ie. the drive where it's files exist isn't accessible) and SQL services won't therefore start.

My specific questions are as follows:

If I run a rebuild of sys databases via command prompt using the install media, am I able to specify an alternative drive for the files? My understanding is on a rebuild, the pre-existing default instance location is used, which in this scenario wouldn't be accessible.
Assuming I can get a copy of the mdf and ldf files (by restoring a backup to another instance, for example) is there a way to start SQL Server by pointing it to a different location for the MASTER database? Like a service parameter?

Now I know that it would be possible to mount a new disk with the same drive letter, but I'm looking for a way where I could safely bring the instance up sooner than that, given in a critical situation such as this, the datacenter may have a list of priorities as long as their arm and it could be several hours or worse before the new volume could be ready.

Thanks all...

I believe all of your questions can be answered here :)

The link covers:

Planned relocation of system databases
Failure recovery of system databases
Moving Master
Moving Resource
Other considerations