how to perform regular backups of SQL Express 2008 R2 databases Which instruments to choose to implement backup strategy on 150 servers with SQL Servers Express Edition 2008 R2? The strategy should also include checkdb, index and statistics maintenance. In case of errors there should be some kind of notifications. Each of the machines is turned off every night.

Allowed data loss - 15 minutes.

I've found several ways to get the job done:

script + Windows Scheduler on every machine;
script + linked servers;
third-party software;
some other option

As my company is very intolerant to anything that implies extra bills I had 3 options left. Some kind of script plus Windows Scheduler seemed the most natural solution so I opted for it.

For scripting I chose Powershell as it is able to get every piece of the task done including sending e-mail notifications. I implemented this strategy.

This is where problems began. In fact, the main source of evil was the Scheduler On one machine some files were missing so the Scheduler did not work, on another machine  problems with permissions, on third machine the OS is too old and so on... So 10-20 machines out of 150 were not being backed up.

Running task every 15 minutes via Linked Servers was not a very good idea to me. At this moment I was solving other issue and realized that SQL Server   sp_procoption might be of help in my situation. A procedure that starts at SQL Server startup is a really good candidate for doing statistics and index maintenance, checking for corruption and backing up every morning and every 15 minutes subsequently.

I used Ola Hallengren's maintenance script to reindex, update stats and checkdb. Of course modified a little to meet my company's needs. It also writes data on backups and checks to a specific table which can be queried via Central Management Server for analysis.

Now the amount of DB's not backed up is no more than 2-3. And the only reason is that machines were not turned off.


Backup database directly to FTP server Is it possible from SQL Management Studio to create a backup directly to an FTP server?
Basically, instead of choosing the destination to a disk, I sould like to be able to specify an FTP location (server, user, pass) to send the backup file to.

This prevents the backup data from consuming space on the server itself.
Network transfer through a shared folder is not an option for security reasons.

If you just want to backup to a non-network path location, then there is now a [tool for backing up to Windows Azure.  Also, SQL Server 2014 will allow to backup directly to Windows Azure without a separate tool. This is a viable alternative to trying to backup to a FTP site while eliminating the need to keep a local copy of the backup. Please note the backups can be encrypted which is an obvious must if you are storing the backups off premises.

Otherwise, you may want to find a way to map a FTP site as either a network path or drive.  Although I have never used either of these products, FTPUse and NetDrive should allow you to accomplish this goal.  There's a blog post on how to map FTP sites as network drives in Windows 7 that might also work for you, but it appears to be an OS-specific solution that might not work for you.

Although these solutions may work, I would strongly recommend that you backup the file locally and then copy it to a FTP site.  Drive space is cheap.  If you store the backup locally, then you can easily restore from backup without having to download the backups.  You should also periodically test restoring your backups to validate that you have good backups.  You should also periodically test restoring your backups from wherever you are sending the backups to make sure these are good as well.

Backing up a database is an IO intensive process and backing up to a FTP location even if it is represented as a local drive will probably be very slow.  Even if the performance is adequate for your purposes, FTP connections can be unstable and can be interrupted for a variety of reasons.  You don't want to have to run another backup just to get the file over the to FTP site.  It would be safer to backup to a high speed local drive and upload the file separately.  I would strongly recommend finding a way to solve your storage problem rather than going down this road.


Select all data group by 1 column and functioned by another I have the following table in SQL Server 2012 :

ID int
priority int
type int
other data columns

I want to query the table for the following result :

grouped by 'type'
get the id of top 1 max priority from all the grouped rows
select all rows from the id's selected in the last step

Example :

ID      Prioroty      Type           Name
1         5            3               foo1
2         4            3               foo2
3         0            4               foo3
4         0            4               foo4
5         2            64              foo5

will give the following result :

ID      Prioroty      Type           Name
1         5            3               foo1
3         0            4               foo3
5         2            64              foo5

;with cte as
(
    select
        ID,
        Priority,
        Type,
        Name,
        row_num =
            row_number() over (partition by Type order by Priority desc)
    from dbo.your_table
)
select ID, Priority, Type, Name
from cte
where row_num = 1;


Options for creating a read-only replica of a DB2 database We are a dev company that develops and supports an application based on DB2 LUW 10.5 for a customer.  We have a VPN that gives us access to our customer's production environment, and we often connect to the production DB2 server directly to investigate various issues.  We want to avoid accessing the production database in this way, so we'd like to create a copy of the production database on our network.

The production server is currently replicated to a DR site using HADR.  HADR with the read only option would give us what we wanted but we want to avoid the need to purchase another license for our server - we'd like to just use the Express-C edition of DB2.  Log shipping may be possible but from what I read the database is not accessible while in the state to accept logs.

For the solution to work the target database would need to be fairly up to date.  It can be read-only but that is not a requirement.  If it is not read-only we would not want to push changes back to the master.

What options are available for this scenario?  Thanks in advance for any ideas.

If you wish to use Express-C, your best options are probably either mirroring the disk and logs using an OS utility (I believe best done on Unix) or through taking backups and copying them to the standby server and then restoring them.

HADR won't work with Express-C. And I'm not sure that pureScale will either (although I can't say with 100% certainty.)

Do note that not every feature from higher editions of DB2 (such as Enterprise) won't work on Express-C. Multidimensional Clustering Tables (MDCs) or BLU for example. So if your client uses those features someone is going to have to pay for a license for another server.

I guess I would also question what data may be in that production server that a client wishes to allow you to have? Or that you wish to have? That is pretty dangerous. Developers should have limited access to production data. It should be specific and timed. Otherwise a copy with scrambled data should be restored to a test or dev environment (still preferably on their equipment). A good DBA shouldn't just let someone have access to production unless they have signed in blood so to speak. I'd go so far as to say a DBA shouldn't really even be able to see the data in production. I digress.


Constraint to prevent overlapping ranges in an array field in PostgreSQL If I have a tztrange field in a table, I can make sure that no two rows have values that overlap using an exclusion constraint. To make sure that no two appointments can be scheduled at the same time, for example, I can use:

CREATE TABLE appointment (
    schedule tstzrange,
    EXCLUDE USING gist (schedule WITH &&)
);

Is there a way to get this same behaviour, but for the values in an array field of type tstzrange[] in a single row?

As an explicit example, I want to be able to define a table test with a constraint that lets these successive inserts both succeed, because in each single array the two ranges don't overlap:

INSERT INTO test (distinct_ranges)
    VALUES ('{"[2016-01-01, 2016-02-01)", "[2016-03-01, 2016-04-01)"}');
INSERT INTO test (distinct_ranges)
    VALUES ('{"[2016-01-15, 2016-02-07)", "[2016-02-29, 2016-03-15)"}');

but such that this insert fails regardless of what other rows exist, because in this array the second range overlaps the first:

INSERT INTO test (distinct_ranges)
    VALUES ('{"[2016-01-01, 2016-02-01)", "[2016-01-15, 2016-02-07)"}');

What I've done so far:

First I thought I'd just look up the function that returned whether or not all ranges in an array were pairwise non-overlapping, and then use a check constraint. Unfortunately, that function doesn't seem to be built-in. I also considered using unnest and the * intersection operator, which would probably mean defining a custom aggregate function, although that's not quite right either unless I can get tricky with the aggregator, because the intersection of three ranges might be empty even though two of the ranges overlap.

So now I'm looking at unnesting the array, sorting it by the start of each range, and returning the logical OR of range1 && range2, range2 && range3, and so on up to range(n-1) && range(n), and checking that that result is false. It's far from obvious to me though what the most efficient way to do this is, and I'm still hoping that I've missed some built-in functionality that will help me out at least partially.

I had a similar problem with an array of int4ranges, and solved it (so far to my satisfaction) by writing this for WiTopia who has kindly agreed to let me share. Please satisfy yourself it works before using it in production -- I am still doing that!

CREATE OR REPLACE  FUNCTION overlapping(therange int4range[])
RETURNS BOOLEAN as
$$
BEGIN

        RETURN (
                with
                      t1 as (select unnest(therange) as lefthand),
                      t2 as (select unnest(therange) as righthand)
                select (select array_length(therange, 1)) !=
                       (select count(*) from t1, t2 where t1.lefthand && t2.righthand)

       );

END;
$$ LANGUAGE plpgsql

I do a && (overlap) based join with the array and itself.   Each item will join with itself, and should join with any other item it overlaps as well, so the count of the result of the &&-join would be greater than the count of the original array if there are overlapping items.   If there are only non-overlapping items, then each item will join with itself only, and the result of the join will have one row for each element in the original array.

I'd be careful about using this once the arrays got big -- it's N^2 in concept!

After defining this function, I put this constraint in:

check (portsegments is null or not overlapping(portsegments))


noSQL ACID and consistency for banking We are building a real time banking platform and app.

Our prime concern is safety and consistency of our data across multiple db servers. We need transactions and we can't afford to risk power outages and lose our data (I'm talking about MongoDB).
Our secondary concern is schema - like functionality for our app tenant users.

We also would like to escape schema updating hell on large databases. Custom schemas for our tenants is optional but very desirable.

We are very heavy AJAX frond end and our backend is full nodejs. We do a lot of JSON data processing.

Should we go with MySQL? Is there any noSQL solution (maybe CouchDB?) for our case?

Thank you for taking your time with this question!

As far as I know there are no "nosql" databases that promise ACID transactions, so for banking purposes they are a non starter. Referential consistency support is not usually in their key feature sets either.

mySQL claims ACID transactions when using innodb tables, but I believe there are some caveats around that which may be show stoppers (any mix of other table types, including in-memory tables which are sometimes used for intermediate results in complex logic, will break ACID compliance for instance).

If you are looking at these options because they are free, then consider postgres which does offer ACID transactions, even including transactions that make schema changes. Please consider your support SLAs though: if this is a system that needs high availability (and the words "realtime" and "banking" suggest it is) then I recommend having a support contract in case something beyond your understanding or access to fix goes wrong. This will be rather expensive and once you are talking about that sort of money you might as well at least consider commercial database servers too.

One final point not relating to any particular database: make sure you have the right options selected for full ACID behaviour. A lot of systems default to settings that bend the isolation part, because guaranteeing complete isolation often requires serialisation which can significantly impact the performance of concurrent operations. Whatever DBMS you chose make sure that you understand its options for isolation/serialisation (like "SET TRANSACTION ISOLATION LEVEL" and related directives in MSSQL) and how they interact with each other.


How to get MariaDB (MySql) query time? I'm using MariaDB 10.1. How to get actual query time for all of executed query? It seems that SHOW PROFILES; was deprecated. Is there a better solution for database tuning?

Edit: It's deprecated only in MySQL 5.6.7.

Set long_query_time=0 and turn on the slowlog to a FILE (in my.cnf, and restart).  Then the slowlog will contain a variety of info about every command executed, including the query time.

You might also want to run pt-query-digest to summarize the slow log.  This will include min/max/avg/etc for each 'different' query.


Don't preallocate many gigs of ram in Oracle Database 12c? I've just installed Oracle Database 12c Enterprise for Windows. The first thing I've noticed is it preallocates almost 10GB ram. How can I configure Oracle to only allocate and deallocate ram as needed to hold indexes and to perform normal database operations?

To be clear, I am happy for Oracle to use 10GB ram when it's actually needed, so I don't just want to set a lower value on SGA_TARGET and have Oracle struggle to work in the confines of a much smaller limit when a big index is created.

Edit: (answers to comments)

@a_horse_with_no_name I tried disabling pre_page_sga and restarting Oracle but I'm still seeing "Oracle RDBMS Kernel Executable" using up 10GB of RAM after the restart.

The RAM is used for offline scientific scripts whose performance scale according to the amount of free RAM. These scripts are run manually and they must be run on this server as it's the only high performance server we can afford right now.

This wasn't a problem on our old server, it just seems that Oracle 12c allocates 10GB RAM off the bat and never releases it.

You probably have pre_page_sga set to true. To disable this, change the value of pre_page_sga to false:

alter system set pre_page_sga=false scope=spfile;

then restart Oracle.


update table using condition I have City and Cars tables and I have this query:

UPDATE City
           Set status='good',
           marke1=(Select marke2 From Cars
                   where car_id=123
                     and city_id is null
                     and country is null )
           Where person_id=789

Problem is when country or city_id is not null I am still getting status='good'
           so I want to use the same (city_id is null and country is null) conditions for status but I'm not good enough in conditional statement. Any help please.

You have to join tables like that: (not an actual query)

UPDATE City AS w
  JOIN Cars AS z ON z.id = w.car_id -- some real condition needed
   SET w.status='good'
 WHERE z.car_id=123
   AND w.city_id IS NULL
   AND w.country IS NULL
   AND w.person_id=789
;

Show your tables for more specific code.


Full Back up over to Network failed due to unknown network error 59 I am recently seeing the Unknown Netowrk Error 59 which saves the full  back ups to a file share on some of the servers . The back ups happens at different times.

Ola Halengren scripts are being used currently and the sql server edition used are 2012, 2014 enterprise edition.

I see there are suggestions for to create a DWORD with name ‘Sesstimeout’ and value 360 in  HKLM\SYSTEM\CurrentControlSet\Services\lanmanworkstation\parameters.

My question is should we create this on the target server (file share) or the server from where the sql server resides

If you are having network reliability issues:

speak to your network/SAN team (and/or use a different file share that doesn't exhibit the problem)
Back up the file, temporarily, to the local SQL Server system, assuming there is adequate space. Then copy the backup to the network share, typically using a tool that can resume interrupted transfers, and delete the original when the copy has been confirmed (which usually includes validating that the backup can be restored). If a copy fails, no big deal; you just start the copy again, and you haven't lost anything.

And I agree with Max, this doesn't seem to be something that altering a session timeout will fix; that's kind of like changing a tire when your headlight is out. You might be better prepared for the next pothole, but you're still only driving with one light.


Is this acceptable practice for a many to many? As a newbie in database design, I have a question about many-to-many relationship practice.

This question is actually a follow up question of this question, but I found it too big to explain in a comment.

The design should be like this(picture below):

A person can have multiple phone numbers
A club can have multiple phone numbers
A phone number can only belong to either 1 club or either 1 person

As I understand it, this isn't exactly a many to many relationship but a many to one.
However, phone number can belong to 2 other tables.
So, should I add 2 columns to phone numbers then?
1 with a FK to person and 1 with a FK to club? Leaving one of them null when a phone number is entered?

Personally, I thought it was better to work with an extra table contact and have person and club link to this table and then have phone numbers link to this table also.

But,...
I find myself in the same situation when it comes to e-mail addresses.
So, my idea was to also link them to that same contact table.
Was this a good step, or should I have added a separate table for the relationship between club - email and person - email?

I hope my question is a bit clear considering its actually a follow up.

-edit-
My schema seems to be incorrect for what my intentions are.

a person/club can only have one contact
a contact can only belong to one person/club
a phone number can only belong to one contact
an email address can only belong to one contact
a contact can have multiple phone numbers
a contact can have multiple email addresses

I notice that your specifications have turned a bit intricate (intentionally, right?), and I consider that this is a beneficial fact since this kind of scenarios will help you broaden your perspective on relational design.

Regarding such situation, I am going to suggest you two similar methods to deal with these new conditions. In the first one I propose the use of a (super)type-subtype cluster, and in the second one I recommend two one-to-many relationships. This way you would be addressing the relationships that you specify between the entities Person (or Club) and TelephoneNumber, and between Person (or Club) and EmailAddress.

My understanding of your business rules

I will describe my suggestions within the context of your individual questions but, before I do that, you first need to know the way I understand your scenario, and I understand it as follows:

Telephone and Email are both types of ContactMeans.
A particular type of ContactMeans must be fixed for a Person (or a Club).
Once a specific type of ContactMeans has been fixed for a Person (or a Club), this Person (or Club) can be reached through one-to-many occurrences of such type of ContactMeans, and you want to make sure that each one of these occurrences belong to the same type, either Telephone or Email.

Proposed methods and answers to your individual questions

First method

  Personally i thought it was better to work with an extra table contact and have person and club link to this table and then have phone numbers link to this table also.

  But,...

  I find myself in the same situation when it comes to e-mail addresses. So, my idea was to also link them to that same contact table. Was this a good step, or should i have added a separate table for the relationship between club - email and person - e mail?

Yes, adding a separate table is a good step and, to me, the best method, but it needs some refinements. Here is where my first suggestion comes into play, you should make use of two supertype-subtype clusters.

In the first case, I would include a table called Party (which has been mentioned in my other answer). This table will serve the purpose of relating a particular Club or Person (the Party subtypes) with a given ContactMeans, Telephone or Email.

You need to store the fact that a specific Party is fixed with only one type of means of contact, so I recommend you including a table called ContactMeansType, with a PRIMARY KEY named ContactMeansTypeCode. Then, in the Party table, you should add a FOREIGN KEY pointing to ContactMeansType.ContactMeansTypeCode.

And then comes the second supertype-subtype cluster. In this case I would call your Contact table “ContactMeans” (the supertype of Telephone and Email) holding a PRIMARY KEY composed of two columns, PartyId (FOREIGN KEY referencing Party.PartyId) and ContactMeansNumber. Every ContactMeans must hold a type, so I would add a FOREIGN KEY named ContactMeansTypeCode pointing to ContactMeansType.ContactMeansTypeCode.

Then, you should establish a validation method in order to guarantee that any given occurrence of a Party (Club or Person) can only be reached through the ContactMeansType (Telephone or Email) that has being fixed for such occurrence. Involve the columns Party.ContactMeansTypeCode and ContactMeans.ContactMeansTypeCode in this effort. For instance:

Suppose that a particular Party has been fixed to be reached only by means of Telephone, then when there is an attempt to INSERT a new ContactMeans, you must make sure that the value contained in this ContactMeans.ContactMeansTypeCode is the same as the value contained in Party.ContactMeansTypeCode, if it is so, then let the INSERT go on, otherwise deny said INSERT attempt. You are using MySQL, so this content about TRIGGERS from the MySQL Reference Manual may be relevant in this purpose.

See Figure 1, which shows an IDEF1X data model depicting a logical structure for this method.

* Do not overlooke the vast power that relational keys have to offer, since they are being used to shape most of your business rules.

In this sense, e.g., it is worth noting how the PRIMARY KEY called PartyId has migrated from Party to ContactMeans, and then from ContactMeans to Telephone or Email. Said PRIMARY KEY has also migrated from Party to Club or Person (receiving the rolenames ClubId and PersonId, respectively), and then to ClubMember (PersonId being rolenamed as MemberId). Thereby, all this “chain” of tables is provided with referential integrity.

In this external document in .PDF format I give a more detailed treatment to this method.

Second method

  As i understand it, this isn't exactly a many to many relation but a many to one.

  However, phone number can belong to 2 other tables. So, should i add 2 columns to phone numbers then? 1 with a FK to person and 1 with a FK to club? Leaving one of them null when a phone number is entered?

Yes, as per the business rules that you have presented, this situation could be handled through a many-to-one (or one-to-many) relationship.

And yes, one given Telephone can belong to a Person or a Club. But no, I do not recommend the addition to the Telephone table of the two FOREIGN KEYS that make reference to Person and Club, nor I advise you leaving them NULL because, according to my personal experience, this may be a sign that the modeling stage needs to be extended.

Instead of the above, I propose a second method in which, again, you would be using the supertype table called Party. This table will “link” Club or Person with Telephone or Email.

It is necessary to add a column that represents the fact that a given Party has been fixed with a specific type of means of contact, so I would add the column Party.ContactMeansCode that makes to the PRIMARY KEY (ContactMeans.ContactMeansCode) of a table named ContactMeans. Or you could deal with this value by way of a BOOLEAN column called Party.IsFixedWithEmail or Party.IsFixedWithTelephone, also.

As you can see in Figure 2, in this option Telephone and Email are structured with a many-to-one relationship with Party, and then, through the latter, to Club or Person.

I included the column denominated IsPrimary to Telephone and Email, which should be of use to store the fact that an individual Telephone (or Email) has been established as the main instance (of its corresponding type) for contacting a particular Party. Regarding this aspect you must create a method to ensure that there could be only one instance set as primary.

Then, you need to define a procedure to validate that any particular instance of a Party (Club or Person) can only be reached through (or contacted via) the ContactMeans (Telephone or Email) that has being fixed for such instance. The column Party.ContactMeansCode is useful in achieving this goal. For example:

Say an individual Party has been fixed to be contacted only via Email (
by virtue of a specific value in the Party.ContactMeansCode column), so you have to accept only INSERTS of new Emails for such Party and, of course, reject every INSERT attempt in the Telephone table for this Party. Again, since you are using MySQL, you may have to do it via TRIGGERS.

See an IDEF1X data model illustrating a logical structure for this procedure in Figure 2.

In the same external document in .PDF format I give a more detailed treatment to this second method, as well.


Getting root node for a given leafs IDs I got the following tables:

  +-----------------+
  | PRODUCTS        |
  +-----------------+
  | #product_id     |
  | #company_id     |
  +-----------------+
        |     |
+---------------------+
| PRODUCT_BUNDLES     |
+---------------------+
| #parent_product_id  |
| #child_product_id   |
| #company_id         |
+---------------------+

And here is a result in a tree like of the relationships among ids in the PRODUCT_BUNDLES table for some product:

        p1
       /   \
      p2   p3
          / | \
        p4  p5 p6

A user give me the following ids p2, p4, p5 and p6 (the leafs of the tree) and I want return the p1 id (the root of the tree).

How I build select query in mysql to do that?

For now I only have the inverse query. From a root ID give the descendent's IDs.

SELECT bundles.product_id FROM products_bundles bom INNER JOIN products products ON products.product_id = bom.parent_product_id INNER JOIN products bundles ON bundles.product_id = bom.child_product_id WHERE products.product_id = SOMEID;

To clarify what I search is a query that return the parent who have all product_id given as descendant not the parent who have some product_id given as descendant. Thanks

With the naive tree pattern (where you just list the immediate parent/child relationships, sometimes called the adjacency list pattern) the this would be done with a recursive CTE in DBs that support them, but mySQL doesn't. If you know that each tree will never be more than a certain depth you can construct a query that looks at all possible depths and picks the last not found like so:

SELECT COALESCE(t5.parent, t4.parent, t3.parent, t2.parent, t1.parent)
FROM   tree_table AS t1
LEFT OUTER JOIN tree_table AS t2 ON t2.child=t1.parent
LEFT OUTER JOIN tree_table AS t3 ON t3.child=t2.parent
LEFT OUTER JOIN tree_table AS t4 ON t4.child=t3.parent
LEFT OUTER JOIN tree_table AS t5 ON t5.child=t4.parent
WHERE  t1.child = @given_node_id

This fails if a tree becomes deeper than expected though, and is not terribly efficient. You could also consider using a stored procedure to loop up the tree until you get to the root, which would work more generally but is more limited in its use (the above could be turned into a view which could be used as part of other select statements, a stored procedure could not).

To help with queries like this you could consider altering your data structure to contain some unrolled data. This sometimes feels wrong (breaking normal form and requiring extra work on inserts, updates, and deletes) but can be very helpful. Three options are:

1: Maintain the path to a node in an extra column:

So the record (c="p4", p="p3") becomes (c="p4", p="p3", pa="/p1/p3/p4"). You have to maintain the paths when nodes are added or moved, and to work out the root you need to use a string function to extract the first item in the path.

2: Maintain a closure table containing every relationship between nodes and their ancestors:

This is like your existing table but contains all ancestors for each node with a depth marker so for p4 you would record:

node   ancestor   distance   depth
----------------------------------
p4     p1         2          1
p4     p3         1          2

You may not need both the depth and distance columns depending on the queries you need to support and wither you keep this as well as or instead of the existing structure.

To find the root from any given point just look for the ancestor with a depth of 1. Again you have extra work to do maintaining the structure on insert/update/delete.

Of course this all assumes you have a forest of trees, not just a single tree. If you have a single tree you only have one root it is will be the same for every node.

A reading recommendation that I often give is https://pragprog.com/book/bksqla/sql-antipatterns - that covers dealing with some common tree/forest related problems, including this one, in chapter 2.


Determining the Actual Server Create Date I am interested in determining the ages of all 70+ my servers. I would like to create a growth graph showing when servers and databases were added to the environment either/both monthly or annually. I executed the following query using the SSMS multi-query feature with only partial success.

SELECT [name], create_date FROM sys.databases

It's partial success because the master and model databases return with a date of 'April 8, 2003' when I know that I created some of these servers during the past couple years.

I refresh databases from one server to another several times a week, which appears to be causing issues with the database create date.

Is there a simple method to determine the actual create date for both servers and databases?

determine the actual create date for SQL Server

This can be found using

SELECT  createdate as SQL_SERVER_INSTALLATION_DATE
FROM    sys.syslogins
where   sid = 0x010100000000000512000000

Note : sys.syslogins is deprecated, so using sys.server_principals will give you the same results.

So you can use :

--Using  sys.server_principals
SELECT SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
   SERVERPROPERTY ('ServerName') AS SQLServerName,
   SERVERPROPERTY ('Edition') AS ServerEdition,
   CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) +  ' + '
       + CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build'
       + CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion,
    RIGHT(@@version, (Len(@@Version)-charindex('Windows',@@version, 1))+1) AS [O.S.],
 SERVERPROPERTY ('Collation') AS Collation
      ,create_date as 'SQL Server Installation Date'
FROM sys.server_principals
WHERE name='NT AUTHORITY\SYSTEM'

--Using sys.server_principals
SELECT create_date as 'SQL Server Installation Date'
FROM sys.server_principals
WHERE name='NT AUTHORITY\SYSTEM'

  Actual creation of database

This can be found by reading the boot page of the database.

Note: DBCC PAGE is undocumented.  I have not seen any side effects of using it, but since it is undocumented, use it with caution.

DBCC TRACEON(3604)
GO
 -- Actual creation time of database is stored in the boot page of the database
 -- This is even retained after restore or detach/attach of the database.
 --- Look for dbi_crdate.
DBCC PAGE('database_name_Goes_here', 1, 9, 3)
GO

DBCC TRACEOFF(3604)
GO

Even better using DBCC PAGE  WITH TABLERESULTS

CREATE TABLE PageData
(
      ParentObject VARCHAR(1000)NULL,
      Object VARCHAR(4000)NULL,
      Field VARCHAR(1000)NULL,
      ObjectValue VARCHAR(MAX)NULL,
)
GO

INSERT INTO PageData (ParentObject, Object, Field, ObjectValue)
EXEC ('DBCC PAGE (''AdventureWorks2008R2_copy_August_2014'',1, 9, 3) WITH TABLERESULTS')
GO

select DatabaseName = (select ObjectValue from PageData where field ='dbi_dbname')
, DatabaseCreateDate = ( select ObjectValue from PageData where field = 'dbi_crdate')


Can we use the multiple databases system concept in a single web application? Am building a simple web based expert application.I have created two databases in the back-end,One database holds user data and the other holds user activity data.Hence it is accessed by a single web app.[e.g Distributed and Parallel both use in single website for back-end]

My question: Can a single web app have multiple databases in the back-end?

I don't see why not.  You would just create two data sources and potentially two data access layers if you want to take it that far.  At least for .Net/C#/VB.Net this wouldn't be a problem.
I'm not sure I understand the benefit in this case but if you feel you have a good reason to separate the database, there's no reason it can't be done.


Are shards distributed randomly across machines in MongoDB or are they in order? Are shards distributed randomly across machines in MongoDB or are they in order?

For example, if I had a MongoDB cluster that was sharded across two machines and my shard key was a person's name, would names starting with 'a-m' be on the first machine (or replica set) and names starting with 'n-z' be on the second machine?

Thanks so much!

In general, no they are not distributed in order.  However, there is a way that this (or at least approximately this) distribution can occur during normal operations.  To explain:

If you have all of your data on a single shard (a-z) and then add a second shard, the balancer will redistribute the data to the new shard by migrating chunks.

The way the balancer picks a chunk to migrate in general is pretty simple, it will pick the lowest available range on the shard with the most chunks (the "a" chunk for example), and move it to the shard with the lowest number of chunks.  Rinse and repeat until the chunks are balanced.

Hence, if you have all your data on one shard and add a second shard, the low range chunks (a-m) will all be moved from the original shard to the new shard, and you can end up with this almost-in-order distribution.

In your example, you just end up with an oddly uniform distribution of data, no real harm done.  However, it might be problematic from a performance perspective for range based queries across a large portion of the data.  Think about a query that would walk the index in order - it would only ever be hitting one shard (new shard, then old shard) at a time rather than using the resources of both with better distribution of data.

In other scenarios this behavior can lead to a particularly poor data distribution.  Let's say you had a time based shard key (bad for various reasons, but still quite common) and you deleted old data on a regular basis.  Combine that with the scenario above and the new shard would eventually have no data on it (the data in the low range, old, chunks would all be deleted).  Remember that an empty chunk (no data) counts just as much as a 60MB chunk in terms of balancing (the balancer is purely looking at the number of chunks on each shard, not the amount of data in them).

Also, none of this would happen if you had 2 shards to start with and then inserted your data, because the splits and migrations would be more random and more distributed in general.  MongoDB will also attempt to move the maximum chunk as well to help this distribution happen.

To summarize - if you have spotted this type of distribution problem, you can take steps to fix it by moving chunks around yourself.  Take a look at the moveChunk command (it's generally a good idea, but not required, to have the balancer off when you do this or you can have trouble getting the required lock).

The easiest way to view all this is to run the sharding status command and pass and argument of true, i.e. sh.status(true).  This will print details of all sharded collections, where the chunks live etc.  It pulls its information from the config database and uses a simple Map Reduce to aggregate the information.

Armed with that information you can also inspect the changelog collection to determine what has been migrated and when it was done (this will also be recorded in your primary mongod logs but can be tough to parse out on busy systems).

Finally, I realize this is somewhat beyond the scope of this answer, but I have written a couple of Javascript functions (that you can use from the MongoDB shell) that will let you determine the object and data distribution on your shards for a given collection, something not easily available elsewhere.  Hopefully you will find them useful :)

Please Note: these functions can be intensive to run, so use with caution in a production environment.

This first function prints out the chunk information in CSV format for a given namespace.  It uses the estimate option in the datasize command to use counts (rather than a scan of all the objects) to figure out the size of each chunk.  Removing the estimate option would give a more accurate result (particularly if your document size varies) but if the data set is not in RAM it can be very resource intensive.

AllChunkInfo = function(ns){
    var chunks = db.getSiblingDB("config").chunks.find({"ns" : ns}).sort({min:1}); //this will return all chunks for the ns ordered by min
    //some counters for overall stats at the end
    var totalChunks = 0;
    var totalSize = 0;
    var totalEmpty = 0;
    print("ChunkID,Shard,ChunkSize,ObjectsInChunk"); // header row
    // iterate over all the chunks, print out info for each
    chunks.forEach(
        function printChunkInfo(chunk) {

        var db1 = db.getSiblingDB(chunk.ns.split(".")[0]); // get the database we will be running the command against later
        var key = db.getSiblingDB("config").collections.findOne({_id:chunk.ns}).key; // will need this for the dataSize call
        // dataSize returns the info we need on the data, but using the estimate option to use counts is less intensive
        var dataSizeResult = db1.runCommand({datasize:chunk.ns, keyPattern:key, min:chunk.min, max:chunk.max, estimate:true});
        // printjson(dataSizeResult); // uncomment to see how long it takes to run and status           print(chunk._id+","+chunk.shard+","+dataSizeResult.size+","+dataSizeResult.numObjects);
        totalSize += dataSizeResult.size;
        totalChunks++;
        if (dataSizeResult.size == 0) { totalEmpty++ }; //count empty chunks for summary
        }
    )
    print("***********Summary Chunk Information***********");
    print("Total Chunks: "+totalChunks);
    print("Average Chunk Size (bytes): "+(totalSize/totalChunks));
    print("Empty Chunks: "+totalEmpty);
    print("Average Chunk Size (non-empty): "+(totalSize/(totalChunks-totalEmpty)));
}

You can call this on any sharded namespace from a mongos as follows:

mongos> AllChunkInfo("test.users");
ChunkID,Shard,ChunkSize,ObjectsInChunk
test.users-_id_MinKey,shard0000,0,0
test.users-_id_10000.0,shard0000,525420,26271
test.users-_id_36271.0,shard0001,1120640,56032
test.users-_id_92303.0,shard0001,153940,7697
***********Summary Chunk Information***********
Total Chunks: 4
Average Chunk Size (bytes): 450000
Empty Chunks: 1
Average Chunk Size (non-empty): 600000

The next lets you find the information for a particular chunk, again using an estimate.  This is somewhat safer to use without the estimate, but should still be used with caution:

ChunkInfo = function(ns, id){
    var configDB = db.getSiblingDB("config");
    var db1 = db.getSiblingDB(ns.split(".")[0]);
    var key = configDB.collections.findOne({_id:ns}).key;
    var chunk = configDB.chunks.find({"_id" : id, }).limit(1).next();
    var dataSizeResult = db1.runCommand({datasize:chunk.ns, keyPattern:key, min:chunk.min, max:chunk.max, estimate:true});
    print("***********Chunk Information***********");
    printjson(chunk);
    print("Chunk Size: "+dataSizeResult.size)
    print("Objects in chunk: "+dataSizeResult.numObjects)
}

Sample Output:

mongos> ChunkInfo("test.users", "test.users-_id_10000.0")
***********Chunk Information***********
{
        "_id" : "test.users-_id_10000.0",
        "lastmod" : Timestamp(3000, 0),
        "lastmodEpoch" : ObjectId("518373f0a962406e4467b054"),
        "ns" : "test.users",
        "min" : {
                "_id" : 10000
        },
        "max" : {
                "_id" : 36271
        },
        "shard" : "shard0000"
}
Chunk Size: 525420
Objects in chunk: 26271


Can SQL Server 2014 server reported drive space properties change overnight when database growth is set to Unlimited? Our SQL Server 2014 server is running on Windows Server 2012 R2. The drive space properties reported by the OS shows that we had 17 GB free one day, and the next day it shows we have only 718 MB free. No particular queries nor SQL Jobs were ran during this time. The database maxsize is set to Unlimited (growth is set to 10%), and executing this query:

SELECT (
           SELECT SUM(CAST(df.size AS FLOAT))
           FROM   sys.database_files AS df
           WHERE  df.type IN (0, 2, 4)
       )                   AS [DbSize],
       SUM(a.total_pages)  AS [SpaceUsed],
       (
           SELECT SUM(CAST(df.size AS FLOAT))
           FROM   sys.database_files AS df
           WHERE  df.type IN (1, 3)
       )                   AS [LogSize]
FROM   sys.partitions p
       JOIN sys.allocation_units a
            ON  p.partition_id = a.container_id
       LEFT JOIN sys.internal_tables it
            ON  p.object_id = it.object_id

...to get the DB size vs space used, shows we are actually 12 GB below the current database size.

So is it possible that Windows suddenly updated the reported space available, taking into account the unused portion of space?

I ran the following query accessing the Default Trace log to find when the database did an Autogrow:

DECLARE @path NVARCHAR(260);
SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1
SELECT
   DatabaseName,
   HostName,
   ApplicationName,
   [FileName],
   SPID,
   Duration,
   StartTime,
   EndTime,
   FileType = CASE EventClass WHEN 92 THEN 'Data' ELSE 'Log' END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (92,93)
ORDER BY StartTime DESC

This showed the database autogrew by 10% during the time the change in drive space was noticed.  So the database simply did what it was supposed to do: grow by 10% when it was needed.


Find all instances where a user has been explicitly granted permissions on an object Is it possible to generate the list of objects a user has been given explicit access to, for a certain user?

I'd like to see a list of all the items a user has explicit permissions for, excluding any permissions inherited through roles etc.

Check the sys.database_permissions DMV, which contains all granted and denied permissions.

The major_id will point to the object_id in sys.objects, the minor_id points to the column_id in sys.columns (if applicable).

The grantee_principal_id and grantor_principal_id columns point to the granted and granting user ID respectively, which you can find in sys.database_principals.

Remember that permissions can be assigned not just to users, but also to server or database roles, which the user can inherit. Also, granted and denied permissions can conflict, in which case the more restrictive of the two rules applies.

I wrote a blog series a while back, which goes into more details on permissions.


SHOW SLAVE HOSTS shows incorrect output I had used --report-host option in one of my slaves, so that I could see that under SHOW SLAVE HOSTS. Now that server is no longer in use. I have uninstalled that server as well. Unfortunately I still see the details of that server under SHOW SLAVE HOSTS(When this is executed on the uninstalled server's master) which is incorrect.

What changes should I make such that SHOW SLAVE HOSTS gives me the correct output?

You will have to check the following items

If the decommissioned Slave server still has /etc/my.cnf, do one of the following

rm -f /etc/my.cnf
mv /etc/my.cnf /etc/my.cnf.old

If the Master Server still has any --report-host in its /etc/my.cnf

remove report-host from /etc/my.cnf
service mysql restart

You may need service mysql restart on the Master anyway
Make sure you delete master.info in the datadir of the old Slave

Give it a Try !!!


Why can a partition bound expression not include a casting operation? In the Postgres CREATE TABLE docs, creating a partition of a table is described like this:

PARTITION OF parent_table { FOR VALUES partition_bound_spec | DEFAULT }
...
partition_bound_expr is any variable-free expression (subqueries, window functions, aggregate functions, and set-returning functions are not allowed). Its data type must match the data type of the corresponding partition key column. The expression is evaluated once at table creation time...

So why is this a syntax error?
CREATE TEMPORARY TABLE foo
(ts timestamptz)
PARTITION BY RANGE(ts)
;

CREATE TEMPORARY TABLE foo_1970_01_01
PARTITION OF foo
FOR VALUES
  FROM ('1970-01-01 00:00+00:00'::timestamptz)
  TO ('1970-02-01 00:00+00:00'::timestamptz)
;

The error is:
ERROR:  syntax error at or near "::"
LINE n: FROM ('1970-01-01 00:00+00:00'::timestamptz)
                                      ^

You must be using PostgreSQL v11 or older.
Back then, it could only be a literal:

FROM ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] )

See the documentation.


column auto updated after 24 hours in mysql i have a table T1 with column :

Name      State             time
mon         1          2014-01-09 11:23:00
tue         0          2014-01-07 14:40:00
wed         2          2014-01-08 09:23:00
thu         0          2014-01-09 12:23:00

Now i want to schedule an event or develop a trigger (if possible) , if any state is 0/2 and its time is more than 24 hours from its current time then it should be updated to 1.

For example in above table, state of Tue is 0 and time is more than 24 hours , so state should be updated to 1 and same applies to wed , but not for thu.

I went through many questions like :

https://stackoverflow.com/questions/17387802/how-to-auto-update-mysql-after-timestamp-field-expierd

https://stackoverflow.com/questions/12421756/how-to-update-table-after-a-certain-time-interval

but i haven't understand yet , how to solve my problem

CREATE EVENT reset
    ON SCHEDULE
      EVERY 1 HOUR
        DO
update T1
set state=1
where time < date_sub(now(),interval 24 hour)
  and (state=0 or state=2) ;

it will run in every hour


Fail plan on a release to production with differential backup I am currently looking into a fail plan when we do a release to our production server.

The context
Currently we have one single production database (also production and testing but they are not important to my question) with MS SQL SERVER 2012. No mirroring and no fail over clusters.
We make a full back up every night around 1 am.

The issue
When we release a new version of our web application we need to update the production database with SQL update scripts. (changes to the scheme, tables, constraints etc...). We want to keep the down time as short as possible.

When we update our production database with our scripts, and something goes wrong there, we want to revert the failed changes.

My plan

DB becomes inaccessible to the web application.
I first make a differential backup from the production database.
We run our cleanup and update scripts
Something goes wrong here
Restore the production database by restoring the differential backup

But all documentation states that I should first restore the full backup and then the differential backup.

My Question
Is it not sufficient enough to use my differential back up when something went wrong with the cleanup and update scripts ? Or do I really have to restore first the full back up and then the differential backup ? Because in this case, I won't win much time if something goes wrong.

Extra
We want change the database setup in the future to avoid this manual approach but for now I am hooked to this context and approach.

You can't use differentials as stated in the other answer, but you CAN use database snapshots. They require Enterprise Edition, but they function exactly like what you're looking for. You take the snapshot, start your deployment, do user acceptance testing, and then either delete the snapshot or roll back to it.

Be aware that "snapshots" is an overloaded term - there's also snapshot replication (which wouldn't help) and SAN snapshots (which actually would help, but it requires a SAN, which I'm guessing you don't have.)


Why is MySQL Workbench is reporting different number of writes per second at the same time on the same database? I am working on a project with MySQL Workbench on a server that I remote into with Windows remote desktop. Sometimes I will also connect to the server with MySQL Workbench on my local machine.

When I go to server status, the number of InnoDb Writes per Second is different depending on whether I am using MySQL Workbench on the remote server or on my local machine.

Here is a screen shot of what it looks like, the left status is on my local machine, the right is on the remote desktop.

It would be nice to be able see accurate live numbers of writes per second.

Perhaps that figure is a snapshot with a tight granularity, reading the number of writes once then again after some portion of a second, and multiplying up to get a "per second" value. In that case I would expect the figures to differ every time unless you somehow request an update of both at exactly the same fraction of a second.

If the figure is an average taken over a larger timeframe (multiple seconds instead of one or a fraction there-of) then the figures would be closer to each other.

You can see a similar effect looking at CPU use on any unix-a-like system by running two copies of a top variant in separate terminals. The same for disk and network IO with iotop and iftop or equivalents. They will almost always show slightly different figures as they poll for updated data at slightly different times, especially if you turn the update frequency as fast as you can read.


How to extract dynamically data from another database in function with DBName as parameter? I have one main database (MAIN) and several client DBs (CLIENTDB). I need to start query in MAIN and to get data from CLIENTDB. The client DB can be found also on linked server. My problem is that I need to do this in a function with EXEC, so I can get SELECT * FROM SRV.CLIENTDB, but this in not allowed by SQL Server. Is there another way to do that?

I need to have code like inside the function:

DECLARE @sSRV AS VARCHAR(128) = 'SRV';
DECLARE @sDB AS VARCHAR(128) = 'CLIENTDB1';

EXEC('SELECT * FROM ' + sSRV + '.' + @sDB + '.MyTable');

I need a function, because I need to make joins with it inside my engine.

It is not possible to do this dynamically via EXEC, as IIRC you can't do that in a function. You can't use an ad-hoc distributed query via OPENROWSET (https://msdn.microsoft.com/en-us/library/ms187569.aspx) either as OPENROWSET explicitly requires its inputs including the query text to be string literals not more general string values (so you can't use an expression or variable).

The only ways I can think of involve either having one function per remote database or the CASE/IF suggestion from Marian's comment. You could pragmatically create such a function by cursoring through a view based in sys.servers and other DMVs to put the function together through string concatenation, then replace the old version with the new using a call to EXEC. You still need to run the generation procedure every time the list of linked servers changes, but at least it would just be one procedure call (or script/statement run) and not a more manual code change.

This all sounds very hacky though. Such a "code smell" would suggest a redesign is in order if you have such power!


Append elements to array only if the element does't exist WITH upd AS (
  UPDATE univ.products
  SET source = source::int[] || _source
  WHERE project_id = _project_id AND product_id = _products AND NOT(_source = ANY(source))
  RETURNING id
)

, ins AS (
  INSERT INTO univ.products(project_id, product_id, source, tracked)
  SELECT _project_id, _product_id, ARRAY[_source], _tracked
  WHERE NOT EXISTS (SELECT 1 FROM upd)
  RETURNING id
 )

SELECT id
FROM   upd NATURAL FULL OUTER JOIN ins
INTO   _project_product_id;

My situation, I have this function which contains all the above and more, what I am trying to avoid is duplicating the project/products pair, but in the same time update the sources by appending new sources. Now the sources for each product/project pair need to be unique, thats why I've added NOT(_source = ANY(source)), but if this is true the select 1 from up will not return anything so will try to insert the records into the db to avoid this I've added the below exception.

EXCEPTION WHEN UNIQUE_VIOLATION THEN
    EXIT;
END;

This is almost doing what I want, but is not returning any id is there a better way of doing this?

Also I have noticed that if I insert a record, then I insert the same record with a different source(the row id will be 1), and then I insert a different record the row id will be 3 instead of 2;

[EDIT] I've solved my problem by doing:

SET source = CASE WHEN NOT(source::int[] @> ARRAY[_source]) THEN source::int[] || _source ELSE source END

Is there a better solution? This one seems a bit slow.

It's not super efficient, but you can define something like your solution as a function:

create or replace function set_append(anyarray, anyelement)
  returns anyarray
  immutable
  PARALLEL safe
  language sql as
$$
  select case when $2=any($1) then $1 else $1 || $2 end;
$$;

You can also use that to define an aggregate:

CREATE AGGREGATE set_agg( ANYELEMENT ) (
  SFUNC = set_append,
  STYPE = ANYARRAY,
  INITCOND = '{}');

This will let you select set_agg(x order by y) to control the order of the distinct elements where you can't do select array_agg(distinct x order by y).


Creating new TempDB files I would like advice on what would be the best setup for our SQL Server 2005 TempDB.

At the moment our Temp DB consists of a single MDF file of 3 GB and a single log file of 120 MB on the D: drive of a 3 spindle RAID-5 array which is partitioned in to C: and D: drives. The D: drive also holds our application core programs and our reports database.

I would like to move the temp DB to its own drive which is a 2 spindle RAID-1 array with a single partition, drive F:.

I would like to create 4 x 1 GB temp db MDF files when I move it to this drive. I do not have another spare dedicated drive to split up the TempDB MDF and LDF files, so both would have to go on the same drive.

Should I leave the LDF file behind on the current drive or should I move all the files to the new one?

Also striping tempdb on the same underlying spindle is just as likely to increase latency as it is to lower it, since this may actually make tempdb access slower - a single drive still has the same moving parts, and the more files you are trying to read/write simultaneously, the worse off you'll be.

I would start with 2 files at most and see if it improves the situation or not. You may even consider leaving one file where it is and adding a second on the new drive (depending on the other activity on the original drive).

Also make sure they are sized exactly the same and they have the same autogrowth setting. As soon as one file is larger than the other, the proportional fill algorithm will only use one file. Consider using trace flag 1117 to make sure that all data files autogrow at the same time, keeping the proportional fill algorithm in check. Just note that this trace flag affects all databases - so if you have other databases with multiple data files, you'll want to make sure their autogrow settings won't cause an issue.


SQL Server 2012 CPU spike due to LinkedServer - how to find the offending query? As part of another question (SQL Server 2012 performance issues (maybe due to linked server)) I've identified a CPU spike to be directly related to a spike in Bytes Received, which leads me to believe it is likely due to the link server.

THE ENVIRONMENT

2 servers, one server accesses a database on the first via a linked server, for nice diagram, please see SQL Server 2012 performance issues (maybe due to linked server)

CPU spike directly related to Bytes Received Spike

QUESTION
How to find which queries could be causing it??

TYPES OF QUERIES WE RUN

I've went through all our stored procedures, and they are optimized. Here are the types of linked server access that we do:

select one value from remote table

select @userid = userid from FBGC.FBGCommon.dbo.players where playerid = @playerid

execute stored procedure that returns result set of max few hundred rows

exec FBGC.Fbgcommon.dbo.qPlayersFriends @PlayerID

execute stored procedure that returns one value

exec @HasEnoughCredits = FBGC.FBGCommon.dbo.qDoesPlayerHaveEnoughCredits @PlayerID, @Cost

delete a few rows from remove table

delete from FBGC.FBGCommon.dbo.InactivePlayersToBeWarned where PlayerID = @AccountOwnerPlayerID

Update one row in remote table, or add one row to remote table

update FBGC.FBGCommon.dbo.users set XP = XP + @XPForTitle where userid = @userid
insert into FBGC.FBGCommon.dbo.UserXPHistory (userid, time, XPReceived, XPFromTypeID, XPFromRealmID) values (@UserID, GETDATE(), @XPForTitle, 1, @ThisRealmID)

I believe all of these are optimized, yet, it appears we are joining tables over the linked server!

Some interesting wait stats that I do not know how to interpret.
Ran a query from http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

I get this DURING the CPU spike:

I get this when there is no CPU spike:

These wait times are on the server with DB2, the SQL 2012 server which accesses a remote database on another server running sql server 2008 (for diagram, see SQL Server 2012 performance issues (maybe due to linked server)

Restarting sql server help - no CPU spike for significant amount of time.

We've also found that, restarting SQL Server helps - we could be getting intermittent spikes for hours, many times an hour, then after restart, no spikes for an hour or more..

Process explorer

As suggested by @EdwardDortland, I used Process explorer to see what thread is consuming most CPU during the slow down

unfortunately, no single thread shows up as the main culprit - during CPU spike, simply all threads consume more CPU. although I am seeing a lot more threads highlighted in red (meaning deleted) during the CPU spike - not sure how to interpret this.

Threads before the CPU spike

Threads during the CPU spike

To answer the question: How to find the offending query:

Since the spikes in the graph you posted last for several minutes you have plenty of time to use the following method:

Download sysinternals process explorer

start process explorer and find the SQL Server process.
right click and select properties
look at the thread tab.
Sort on the CPU column and note the thread id (TID) that is consuming the most CPU.

Use this query and lookup the query that is currently being executed by that thread:

SELECT r.session_id, st.text, qp.query_plan
FROM sys.dm_os_threads AS ot
JOIN sys.dm_os_tasks AS t
ON t.worker_address = ot.worker_address
JOIN sys.dm_exec_requests AS r
ON t.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE os_thread_id = <thread id>


Is there a way to avoid the password prompt in pg_basebackup? I'm attempting to get a server provisioned using ansible, meaning that I can't type in a password. I'm doing a master-slave topology and I need to run pg_basebackup on the slave machine to accomplish this. I've looked into using .pgpass, but that appears to be for my shell user, not the postgres account.

Is this possible? Or will I have to make a manual stage in my deployment?

Create a .pgpass in the home directory for the postgres user, or whatever user you run pg_basebackup as.

Link to the manual: http://www.postgresql.org/docs/current/static/libpq-pgpass.html


How to disable mongodb connection logs? Our mongodb relication has many logs like this:

>     2017-11-24T02:15:50.679+0000 I ACCESS   [conn6537] Successfully authenticated as principal stats on stats
>     2017-11-24T02:15:50.679+0000 I ACCESS   [conn6535] Successfully authenticated as principal stats on stats
>
>     2017-11-24T02:15:51.150+0000 I -        [conn6535] end connection :49982 (13 connections now open)
>     2017-11-24T02:15:51.150+0000 I -        [conn6536] end connection
>
> 2017-11-24T02:33:09.187+0000 I NETWORK  [thread1] connection accepted
> from 2017-11-24T02:33:09.187+0000 I NETWORK  [thread1] connection
> accepted from 2017-11-24T02:33:09.187+0000 I NETWORK  [thread1]
> connection accepted from

Our config:

db.getLogComponents();

{
        "verbosity" : 0,
        "accessControl" : {
                "verbosity" : -1
        },
        "command" : {
                "verbosity" : -1
        },
        "control" : {
                "verbosity" : -1
        },
        "executor" : {
                "verbosity" : -1
        },
        "geo" : {
                "verbosity" : -1
        },
        "index" : {
                "verbosity" : -1
        },
        "network" : {
                "verbosity" : -1,
                "asio" : {
                        "verbosity" : -1
                },
                "bridge" : {
                        "verbosity" : -1
                }
        },
        "query" : {
                "verbosity" : -1
        },
        "replication" : {
                "verbosity" : -1
        },
        "sharding" : {
                "verbosity" : -1
        },
        "storage" : {
                "verbosity" : -1,
                "journal" : {
                        "verbosity" : -1
                }
        },
        "write" : {
                "verbosity" : -1
        },
        "ftdc" : {
                "verbosity" : -1
        },
        "tracking" : {
                "verbosity" : -1
        }
}

How can I disable these logs? Thank you!

With "quiet" configuration parameter (not recommended) you get rid of:

"Successfully authenticated as..."
"connection accepted from ..."

but "end connection ..." still stays there and there is no way to remove it, without changing source code. You can always ask developers to change code to

if (!quiet) {
  const auto word = (connectionCount == 1 ? " connection"_sd : "connections"_sd);
  log() << "end connection " << remote << " (" << connectionCount << word << " now open)";
}


Using SELECT to call a function in a jdbc migration I'm looking to set up some Postgres/PostGIS migrations with clojure/jdbc.

running side-effect functions with SELECT is proving to be an issue, with most migration libs eventually throwing the A result was returned when none was expected error, because at some point they use clojure.java.jdbc/execute! or clojure.java.jdbc/db-do-commands, which seems understandable, but frustrating when you need to call a function that's critical to the migration.

The PostGIS's docs encourage using SELECT statement to create a spatial column:

SELECT AddGeometryColumn('parks', 'park_geom', 128, 'MULTIPOLYGON', 2 );

Has anyone run into this or found an appropriate workaround for using functions in a clojure/jdbc and Postgres migration?

related tidbits:

this description for manually registering a spatial column looks promising but seems remarkably heavy-handed for something that already has a supporting function
there's also PL/PgSQL's PERFORM statement that I stumbled across but it seems like I'm grasping at straws at that point, despite it looking promising
clojure.java.jdbc/execute! docs give a specific heads-up about only using "general (non-select) SQL operation[s]"

You can avoid this by using the DO command like so:

DO $$
BEGIN
    PERFORM AddGeometryColumn('parks', 'park_geom', 128, 'MULTIPOLYGON', 2 );
END;
$$

Note the BEGIN and END are necessary as it has to be a valid plpgsql block.


Specific user can't view database contents I have a particular user, let's call it TheUser.  This user is able to connect to my SQL Server instance and view a list of the databases.  However, upon expanding a database to view tables, stored procedures etc. I am greeted with this lovely, vague message:

This isn't being written to the error log, so I can't actually get a more detailed error (unless I'm looking in the wrong place?)  TheUser's default database is currently set to master.  If I set it to anything else, I'm unable to login.  It has also been granted connect permissions under the database's permissions.  Does anyone know how to resolve this?

To clear things up, language-wise: In SQL Server, the login is global for the SQL Server instance (a "server principal") and allows a client to log on to the server with very limited permissions.

Once you've set up a login, you can assign users to this login. A user is the login's security principal in a specific database ("database principal"), which means that a login can have multiple users, one for each database where he holds permissions or role memberships.

You might have added a login, but not the user, or you may not have assigned the user enough permissions in the database. To view the definition of database objects, your user will probably need VIEW DEFINITION, either on the database, the schema or on the individual object. These permissions are inherited. If you want to be able to read the data of tables, add SELECT permissions. Membership in the database role db_datareader gives the user complete read-only access to all tables and views in the database.

I've recently written a series of blog posts on SQL Server security that might give you a little more details.


Creating unique constraint to be validated from input I have this table name tbl with this table definition:

create table tbl (
    id serial primary key not null,
    counter integer not null default 1,
    ref_id integer not null,
    date_booked date not null
);

With these insert statement(s):

insert into tbl(counter, ref_id, date_booked) values (1, 1, '2014-07-05');
insert into tbl(counter, ref_id, date_booked) values (2, 2, '2014-07-10');
insert into tbl(counter, ref_id, date_booked) values (3, 3, '2014-07-15');
-- this should fail
insert into tbl(counter, ref_id, date_booked) values (3, 4, '2014-07-20');

I am having a problem of creating a unique constraint/index in such way tbl.counter should be unique within the month of the inputted tbl.date_booked. From the insert statements above, the 4th insert statement should fail since if violates the unique validation. The problem is I don't know how to create such validation. Though I have a bit of an idea about constraints, index, etc. it's just am confused which to choose to make this work.

This can be achieved by using a unique index on the combination of counter and the month of date_booked. Something like this:

create unique index idx_tbl on tbl (counter, to_char(date_booked, 'yyyymm'));

However the above will fail because to_char() is not an "immutable" function and thus cannot be used in an index (even if we know that the above is fine). The workaround for this is to create a wrapper function that is marked as immutable:

create function my_month(some_date date)
  returns varchar
  immutable
  language sql
as
$$
  select to_char(some_date, 'yyyymm')
$$
;

Then you can create an index using that function:

create unique index idx_tbl on tbl (counter, my_month(date_booked));

If you now insert the fourth row, you get the error:

duplicate key value violates unique constraint "idx_tbl"

If the table is big, you might want to use a number instead of a varchar in order to reduce the size of the index as well e.g. by using to_number(to_char(some_date, 'yyyymm'))


What's the advantage of the inet datatype in Cassandra vs a plain text string? What is the inet datatype really for? The documentation refers to its usage having something to do with Python but I'm not much of a Python guy so that aspect is beyond me. Is there any advantage to using inet over just a plain text representation of an IP and what is it?

If its pertinent, I'm using PHP 5.6 and Apache Cassandra.

It's a special datatype for storing ip addresses. It would be more compact that the string representation of the same information as well as type safe.

inet    An IP address. It can be either 4 bytes long (IPv4) or
16 bytes long (IPv6). There is no inet constant,
IP address should be inputed as strings

https://cassandra.apache.org/doc/cql3/CQL.html


SQL log shipping from different hosts on different versions We have a current setup where we have two SQL 2008R2 database servers in production.  Our reporting team requested a single place they could query data from both servers (to avoid both hitting production directly and the performance issues with linked servers).  So we set up log shipping where we ship several databases from both sources to a target server.  It works great, the databases on the target server are read only during the day and only unavailable when the log shipping restores are scheduled at night.  However, the vendor applications for both source servers are going through an upgrade project, and they will unfortunately have to be be different versions (SQL 2014 and 2016 respectively).
Most of what I have researched seems to say that you cannot log ship from a different source version, but I've also found information suggesting that you can ship from a lower to a higher version, but am unable to find anything official.
So, my question is, is it possible to log ship from both 2016 and 2014 sources into a 2016 destination, AND leave the databases read only when they are not restoring?  And is there official Microsoft confirmation of this?

As per the link in the comments, it looks like you can do a log shipping restore from a lower to higher version, but cannot make it standby (read only):
log ship from older version to newer one
I'll probably write up a separate question to see what alternatives there are (other than transactional replication... ideally looking for something uncomplicated).


Profiling and debugging "Incorrect syntax was encountered while parsing :setvar." I have the following script using SQLCMD setvar, but I receive an error when I parse the script:

:setvar dbname "PROD"
:setvar backuppath = "e:\sql\restore\PROD.bak"

USE [master];
GO

-- backup the existing PRODUCTION database
BACKUP DATABASE [$(dbname)]
TO DISK = '$(backuppath)'
WITH COPY_ONLY, COMPRESSION

-- we need to clear the existing PRODCOPY users
-- before we restore and replace
IF DB_ID('PRODCOPY') IS NOT NULL
BEGIN
    -- Remove any connected users
    ALTER DATABASE [PRODCOPY]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    WAITFOR DELAY '00:00:05'
    ALTER DATABASE [PRODCOPY]
    SET MULTI_USER
END

-- restore PROD as PRODCOPY
-- ...

The error is:

A fatal scripting error occurred.
Incorrect syntax was encountered while parsing :setvar.

If I comment out the section:

IF DB_ID('PRODCOPY') IS NOT NULL
BEGIN
...
END

Then the error goes away. It seems like the use of SQLCMD is actually testing whether the PRODCOPY database exists, which of in this case it doesn't.

I've tried to use profiler, but although I can see the SQL commands:

SET PARSEONLY ON
SET PARSEONLY OFF

However, profiler is not logging anything in between the two instructions that should be parsed, but I still see the error! Any ideas what I need to do to see the content that is supposed to be parsed by the server? Which columns, events to trace?

Is this an oddity with SQLCMDs?

:setvar backuppath = "e:\sql\restore\PROD.bak"

should be

:setvar backuppath "e:\sql\restore\PROD.bak"


Sql Server 2008 R2: Simple recovery model with transaction log backups I'm busy reviewing my SQL Maintenance plans and I'm a bit confused with the recovery model option on the databases, specifically related to point in time recovery.

Here is my scenario:

I have two maintenance plans

One that does hourly transaction log backups
another that does a daily full backups (but first a transaction log backup before the full backup)

All databases are set to use the simple recovery model.

Here are my Questions

Seeing that the recovery model is set to simple and msdn states that the simple recovery model does not support point-in-time recovery - Does this mean that I won't be able to use my transaction log backups to restore the database in a disaster to an hour before it happened?
Which backup should be done first, the database backup or the transaction log backups?

Articles that I'm busy reading say I should do the database backup first and then the transaction log backup else I will get maintenance plan errors, but I'm currently first backing up my transaction logs and then data databases and I'm not getting any errors.

My final solution (Maintenance Plan)
Set all databases to use the FULL recovery model. Then:
Hourly Maintenance Plan:
- Check sufficient hard drive space for backups
- Make Transaction Log Backups
- Archive transaction log backups (zip the .trn files)
- FTP the archived transaction log backups offsite

Daily Maintenance Plan:
- Check sufficient hard drive space for backups
- Do a full database backups followed by transaction log backups
- Archive backups (zip the .bak and .trn files)
- FTP the backups offsite

Weekly Maintenance Plan:
- Check database integrity
- Rebuild indexes
- Cleanup history older than 4 weeks
- Delete .bak and .trn files older than 4 weeks
- Delete archived backups (zip file) older than 4 weeks

If any task fails a notification email is sent. A success email is also sent when the daily and weekly task finish successfully.

Note of testing your maintenance plans. My first backups got corrupted by the ms windows ftp.exe not configured correctly to use passive mode. SO CHECK YOUR BACKUPS :)

Seeing that the recovery model is set to simple and msdn states that the simple recovery model does not support point-in-time recovery - Does this mean that I won't be able to use my transaction log backups to restore the database in a disaster to an hour before it happened?

Even taking a transaction log backup is not supported for databases using the SIMPLE recovery model. This is a restriction of the database engine based on how this recovery model works, and the recovery features it doesn't support, as you mentioned.

A transaction log backup maintenance plan task automatically skips databases in SIMPLE recovery to avoid causing errors.

  Which backup should be done first, the database backup or the transaction log backups? Articles that I'm busy reading say I should do the database backup first and then the transaction log backup else I will get maintenance plan errors, but I'm currently first backing up my transaction logs and then data databases and I'm not getting any errors.

For the reasons I mentioned above, it won't matter for databases using SIMPLE recovery, as they will be skipped by the transaction log backup task.

For databases in the other two recovery models, a full backup must exist before you start taking transaction log backups (just the first time), or you will get an error -- this is probably what the articles refer to.

Point-in-time recovery ability is normally driven by business need -- in other words, you determine how critical the data is and how much you can afford to lose, then set the appropriate recovery model to meet those needs, and finally create a backup solution.

Even though SIMPLE recovery does not support point-in-time recovery, if an hour of data loss is okay, perhaps a differential backup solution could work for you. (There are far too many variables that go into developing this kind of solution to give you a complete picture with what was provided in the question.)


Automatic migration failed on Azure Database I am using Entity Framework 6.1.1. The database has three million records; migration has to change the precision of a column of type decimal. The migration timeout window is set to the maximum value, CommandTimeout = Int32.MaxValue;

I was testing this scenario on Azure SQL Database. After trying for around 90 minutes, it ends up with an exception, but no migration done.

Exception details:

  The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.

My questions are:

Is it the expected behavior to take more time for migration of this type on a huge DB? (Because after changing the precision it has to convert the existing value and save it back, and it has to be repeated on 3 million records)
How to  resolve this? I don't think we can have migration in chunks of transactions.

The simple answer is don't use Entity Framework for this sort of thing. You need to write a SQL migration script, test it, and then deploy it onto your production system.  And yes - changing a table with 3M rows will take more time and resources than migrating a table with 3 rows.

There are a few techniques for achieving this. First, judging by the error message, you're trying to do this in a single transaction. Better to add a new column of the correct precision, copy values to it in batches of, say, 50,000 at a time, then drop the old column and rename the new one.

If the system has to remain operational during the migration you can get creative with views and triggers.


How to resynchronize replication for a single database I have two MySQL 5.5 servers in a master/slave replication setup. The server hosts about 10,000 databases, but the replication is messed up for only a couple of databases.

To fix the replication I could do a complete resynchronization by copying all the databases over to the slave server, but there are many databases and so much data that this would entail a long downtime for all the databases.

A better solution would be to re-synchronize just the two or three databases whose replication is broken. Can I do this in such a way that the only downtime would be for those two or three databases that have replication problems?

It depends a bit on your setup, there are different ways to archive what you want. I will describe a very basic one which only uses MySQL tools (and some standart Linux tools).

First of all you have to make sure a few things:

It can be guaranteed that the databases which you want ro resync will have no writes or other DML or DDL statements issued against while you perform the resync. E.g. you are able to revoke privileges for these database users.
You are familiar with backup and restore using mysql tools :)

Now the procedure:

Ensure that there will be no access on the databases which you want to resync, easiest way to do this is to revoke all priviliges for users which have access to this database. A possible way to find those users would be querying mysql.db table.
Now you have your three schemas and there will be no changes to it, it means it will be on a consistent state until you have finished your operations on it. You can now safely dump only there required databases using mysqldump. Please refer here to the mysqldump docs. If you are unsure just ask.
Copy your dumps to your slave machine and restore your three databases there using these dumps. Beforehand make sure that all binary logs are applied to the slave which might contain changes to these databases (make sure replication has catched up and there is no lag or something).
After the restore your databases will be identical on the master and the slave. It is now safe to grant back the permissions to the users on the master, as soon as there are changes on the master they will be replicated to the slave.

Keep in mind that there are different approaches for restoring failed slave databases, above is one of them, so maybe some additional research would be a good idea. If you have questions to particular points of my description don´t hesitate to ask, I will then edit my answer.


Password hashes: Fixed-length binary fields or single string field? I'm currently having am amusing debate with a friend, and we simply can't agree on the best method to store salted passwords in a database. The two options on the table are:

Storing the hash and the salt together as a string, delimited by some agreed character (for example: "E69B4A103...598D$59FBA6")
Storing the hash and the salt in separate fixed-length binary fields

Obvious advantage to the first is only having to retrieve one field, but at the cost of the field being significantly larger (especially when using SHA512 or a large salt), and performance issued with string manipulation later down the road in the application.

Option two has the advantage of being much smaller, but at the cost of a slightly more complex query to retrieve two fields, not one.

Our question is: which option would make the most sense for a large database where performance is a key factor?

There are 2 bits of information. This means 2 fields. It is that simple.

In practical terms, selecting 2 columns is zero extra complexity. Having to read a large field to parse one bit out is unnecessary complexity and performance overhead.

A couple of SO questions on storing salts with hashes

https://stackoverflow.com/q/1219899/27535
https://stackoverflow.com/q/1191112/27535


How to alter/migrate the schema of a table with tons of records I have a table which has about 1 million records, and recently i'm going to ship a new feature which requires 2 new fields. But I found it takes about minutes to get it finish, and during the migration, i have to shutdown the service, as the table was locked.

Is there any ideal way to do such kind of jobs without making the online service offline?

Thanks.

What I do in this case is:

create a shadow table with the new schema
move over all the data
xlock the original table
apply the changes since moving the data
rename the original table to bak
rename the "new" table to the correct name

This makes your downtime minimal at the cost of disk space.


where is oracle session timezone set? I have a logging table

CREATE TABLE EMAIL_LOGGING
(
  ID               NUMBER(9)                    NOT NULL,
  SEND_DATE        TIMESTAMP(6)                 DEFAULT LOCALTIMESTAMP        NOT NULL,
  SEND_TO_USER_ID  NUMBER(9)                    NOT NULL);

and a package that inserts into it with this fragment:

  INSERT INTO EMAIL_LOGGING ("ID",SEND_DATE, SEND_TO_USER_ID)
       VALUES (NULL, LOCALTIMESTAMP,send_to_user_in);

When this is called from a DBMS_JOB from one package it inserts Greenwich mean time.  When it is called from a DBMS_JOB from another package that uses a db_link to another database it correctly inserts the local time.

I think this is caused by the client's timezone being used but adding this to the job did not resolve it:

execute immediate 'alter session set time_zone=local';

Of course the best solution is to move to DBMS_Scheduler but until that is done how can I ensure that the timestamp that is inserted is the local time?

select * from v$version where banner like 'Oracle%';
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
SELECT DBTIMEZONE FROM DUAL;
+00:00
SELECT SESSIONTIMEZONE from dual;
-04:00

I resolved this issue by setting this attribute

BEGIN
  DBMS_SCHEDULER.set_scheduler_attribute (
    attribute => 'default_timezone',
    value     => 'EST');
END;

and moving the job to DBMS_SCHEDULER.  Now, when it runs the correct (local) time is entered in the logging table.


mysqldump error on bash script I'm trying to run a bash script to backup my database with Cronjob.

# Backup and compress each database
for database in $mysql_databases
do
  if [ "${database}" == "information_schema" ] || [ "${database}" == "performance_schema" ]; then
        additional_mysqldump_params1="--single-transaction"
  else
        additional_mysqldump_params=""
  fi
  echo "Creating backup of \"${database}\" database"
  mysqldump ${additional_mysqldump_params} --user=${mysql_user} --password=${mysql_password} ${database} | gzip > "${backup_dir}/${database}.gz"
  chmod 600 "${backup_dir}/${database}.gz" ${additional_mysqldump_params1}
done

but the --single-transaction option is not recognized

OBSERVATION #1
It is not recognized because of a typo
You have
  if [ "${database}" == "information_schema" ] || [ "${database}" == "performance_schema" ]; then
        additional_mysqldump_params1="--single-transaction"
  else
        additional_mysqldump_params=""
  fi

It should be
  if [ "${database}" == "information_schema" ] || [ "${database}" == "performance_schema" ]; then
        additional_mysqldump_params="--single-transaction"
  else
        additional_mysqldump_params=""
  fi

Please change the additional_mysqldump_params1 to additional_mysqldump_params
OBSERVATION #2
You have
chmod 600 "${backup_dir}/${database}.gz" ${additional_mysqldump_params1}

It should read
chmod 600 "${backup_dir}/${database}.gz"

Please remove additional_mysqldump_params1 from that line of code
OBSERVATION #3
There is no need to mysqldump information_schema or performance_schema databases

The INFORMATION_SCHEMA database is made of up in-memory temporary tables. They are dynamically generated and populated on mysqld startyo.
The PERFORMANCE_SCHEMA database is for the instrumentation of measuring performance. Each individual MySQL instance is responsible for populating it with instrumentation data when it is in use.


SQL query, group by and number of occurrences I have the following table (only one column):

X
----
1
1
1
2
2
3
3
3
4
4
5
6
6
7
7
7

I need a SQL query which will output the following:

Occurrences | Number
1  1  * there are only 1 entry with one occurrence (number 5)
2  3  * there are three entries with two occurrences (numbers 2,4,6)
3  2  * there are two entries with three occurrences (numbers 1 and 7)

If I do something like this:

select count(*) FROM (SELECT count(*) as N FROM table 1 GROUP BY x) a GROUP BY a.N;

I get the correct results, but only the second column t with the number of occurrences.
Does anyone know how to get the complete output as in the example above?

This should work:

select count(*), a.N FROM (SELECT count(*) as N FROM table1 GROUP BY x) a GROUP BY a.N;

SQLFiddle

Note that it gives you 3 for N = 3, because there are actually three number repeated thrice: 1, 3 and 7.


Is there any trick that allows to use Management Studio's (ver. 2008) IntelliSense feature with earlier versions of SQL Server? Latest version of Management Studio (i.e. the one that ships with SQL Server 2008) finally has a Transact-SQL IntelliSense feature. However, out-of-the-box it only works with SQL Server 2008 instances.

Is there some workaround for this?

[I asked this question a while ago at StackOverflow and unfortunately nobody knew such a trick. Maybe I will have more luck with this problem here.]

Aside from using a third party tool, no. I run into this problem a lot too. The deal is the SqlServer2005 doesn't support the meta data to do that.

However, I can suggest (cos I used it for a bit and loved it) RedGates SqlPrompt product to do this. It's incredibly handy. However, it does cost money, so there's a tradeoff.

If someone DOES have a workaround for this, I would love to hear of it.


SQL Server : CRUD Editor that will track data changes I'm looking for a tool that allows CRUD changes to SQL Server data. Our company currently utilizes a product from Pro Data called DBU (database utility) to make IBM DB2 / AS400 changes. This tool tracks CRUD activity, including before and after data changes. Then it produces a log that can be reviewed for audit purposes.

I tried several tools that track object level changes on the server, including SQL Server built-in tracking, however this is not the desired affect. We would like to expose a utility to a group of users and track only that particular activity.

Any suggestions or recommendations?

I won't use a own product for this.

You have two simple ways to do it on your own.

You can use CDC (Change-Data-Capture) which will do this automatically. You can retrieve the changes afterwards for some time. (If you don't need that every value is hold back for ever)
You can create an Before-Update trigger, which will generate a own log for a record before and after the insert. You can just query those log table and everything is fine. The end-user won't see anything of this, he can use Access, SSMS or whatever to achieve the changes.


sp_BlitzFirst - Wait Stats Interpretation I have some BlitzFirst Wait Stats that look as follows:

Is this the proper way to interpret this? During the 30 second sample, there were a total of 627.1 seconds of IO_Completion waiting, an average of .5 waits per core per second and a total of 20,723 of these waits, each averaging 30.3 MS?

Also, I am not using the Since Startup option.

So let's do a quick rundown of your top two waits:

IO_COMPLETION - waiting to write to storage
PAGEIOLATCH* - waiting to read from storage

In 30 seconds, your queries spent 1,655 seconds waiting on storage.

Your storage is probably slow - if you skip down to the next section in sp_BlitzFirst's output, it will show which data and log files SQL Server was waiting on, and for how long. However, before you leap to playing Pin The Blame On The SAN Administrator, consider:

Tuning indexes & queries to reduce the amount of data read from disk (I'd suggest using sp_BlitzIndex and sp_BlitzCache for that, although I'm biased (disclaimer: I'm one of the coauthors))
Adding memory to cache data, which will reduce PAGEIOLATCH, thereby easing pressure on the storage, and will likely reduce the IO_COMPLETION waits too
Spending a whole lot of money on storage (you might guess that the above two fixes are above this one for a reason)


DBMS_RESOURCE_MANAGER limit IOPS or MBPS Does DBMS_RESOURCE_MANAGER have limit io bandwidth?

I want reporting group to be limited to some mbps. During peak hours our database disks are 100% loaded, because reporting groups are selecting many rows.

I want  somehow to limit io resource without canceling their selects.

By making those processes slower you als stretch the period with pain for both the regular users as well as the etl processes. We normally solve this by assigning a dedicated replica to the etl/reporting processes. That replicate can be tuned to better support the etl processing and it removes the need to put the extra pressure on the production database.
Resource manager can be used to prevent long running queries from starting at all. Problem here is that the estimates it uses are not always correct which might cause regular queries to be rejected. So be careful.


User has been denied select to table, but has access to table via synonym User has been denied select to table T1 in DB1, but user can create synonym in DB2 and can select from DB1.TB1.
I can not deny create synonyms to user. Is it possible to prevent user to select after create synonyms for DB1.TB1?
use d2
create synonym dbo.cl for d1.dbo.cl
select top 100 * from dbo.cl
(100 rows affected)

select top 100 * from d1.dbo.cl
The SELECT permission was denied on the object 'cl', database 'd1', schema 'dbo'.

User has db_owner through AD group in d2

This is because of the "ownership chaining" feature in SQL Server. It means that if one object (in your case a synonym) is calling another object (in your case a table), then it checks if the owner of the objects is the same. If the owner of both objects is the same then the access on the called objects (so your table) is not checked.
This feature is why for example users can have access to a view without having access to the underlying tables.
Normally this ownership chaining is not enabled cross-database by default. So check if it is in your case. It can be granted on database level:
SELECT is_db_chaining_on, name FROM sys.databases;

Or it can be granted on server level for all databases:
EXECUTE sp_configure 'show advanced', 1;
RECONFIGURE;
EXECUTE sp_configure 'cross db ownership chaining';


How to determine if an ALTER statement will be a metadata-only operation O(1) or a size of data operation O(n)? We are dealing with very large tables in Sql Azure.

Size-of-data operations can lock tables for a very long time so we must be careful to run these only on week ends.

Metadata operations are quick and we can run them in production at any time.

Looking at a given ALTER TABLE statement, how can I determine if this will be a metadata-only operation or a size-of-data operation?

As far as I know it's not possible to view an ALTER's execution plan?

Generally there are three different ways SQL Server can proceed when it comes to table alteration.
1. Metadata only changes
examples - dropping a column, changing a not nullable column to nullable one or adding a nullable column to the table.
2. Alteration requires changing the metadata only, but SQL Server needs to scan the table to make sure it confirms to the new definition.
example - changing a nullable column to not nullable.
In this case SQL Server will need to scan all the data rows in the table to make sure that there are no null values stored in that particular column.
3. Alteration requires changing all the rows in the table in addition to the metadata.
example - change a fixed-length char column to varchar. In this case SQL Server will need to move the data from fixed to variable length section of the row.

I read about table alteration in the SQL Server Internals book by Dmitri Korotkevitch sometime back and found it very helpful to understand this topic.

Though as suggested by Kin in the last answer, there is nothing better than being able to test it out and assess the impact before going into production with the change. Hope this helps.


Can I access the file stream shared folder using Listeners in case of AlwaysOn cluster environment? One of the most effective ways to extend your SQL Server Integration Services (SSIS) control flow is to use a Script task to write custom code that perform tasks you cannot perform with the built-in components.But it's not straight forward in case of AlwaysOn configured nodes

Below is my current environment setup which helps you to understand the problem.

My environment

I am having NODE 1 and NODE 2 as cluster configured
SQL Server always on and availability group configured
SQL Server file stream is enabled on both node 1 and node 2 entitled as MYSHAREDNAME
Internal load balancing Listener configured and entitled as MYACTIVENODE

Problem Statement
One of the node among cluster (node 1 or node2) can be down any time. We will not sure which one will become PRIMARY. I am trying to access the shared folder as follow

\NODE1\MYSHAREDNAME - Works if NODE 1 is primary node
\NODE2\MYSHAREDNAME - Works if NODE 2 is primary node

But it's difficult to hard code above path as any node can go down. So I used LISTENER name so that it can automatically detect the PRIMARY node to do the desired job as shown below

So I used LISTENER name so that it can automatically detect the PRIMARY node to do the desired job as shown below

  \\MYLISTENERNAME\MYSHAREDNAME

But I am not able to access it

How can I fix it? Ports 1433, 5022, 59999 are enabled.

Extract from https://msdn.microsoft.com/en-in/library/dn385720.aspx

  AlwaysOn Availability Groups are supported as long as you do not add new database files to the primary database. If a database operation requires a new file to be created in the primary database, first disable AlwaysOn Availability Groups in the secondary node. Then, perform the database operation on the primary database and backup the database in the primary node. Next, restore the database to the secondary node, and enable AlwaysOn Availability Groups in the secondary node. Note that AlwaysOn Failover Cluster Instances is not supported when using the SQL Server Data Files in Windows Azure feature

But I did not understand what does above statement means?

I do not believe your extracted quote is relevant to your problem.

Your issue could be that your listener has multiple IP addresses. Under the default configuration, the listener will have multiple A records in DNS and your client will cache only one of them. Each time the cache expires it will randomly grab one of the IP addresses and sometimes it will get the active IP and other times it will get the one that is offline.

This is the purpose behind the MultiSubnetFailover parameter that the listener acknowledges.


Is it possible to tell the source of a backup? I was wondering if it is possible to tell the difference between backups created manually and backups created through a maintenance plan.

There does not seem to be any way to discriminate them in the msdb backup related tables apart from the account that the Sql Agent runs under is placed in the user_name column in the msdb..backupset table

I would like to verify that backups done through a maintenance plan exist on disk but don't really care about any manually created ones. Part of the plan compresses and copies the archives to another server. These will be compared to make sure everything is ok with the plan.

When you set up a backup using the Maintenance plan, it provides a rather complex name for the database backup (example: MovieManagement_backup_2012_02_14_064551_7520824). You can view this by using RESTORE HEADERONLY. Most people don't supply any name at all when running a backup manually. This can be the difference. Other than that, no, I'm not so sure there's anything you can do there.