Table Partition Switch out problem Please help! I am having error switching out from the partition i set up. I have the scripts and error information below :

I created separate file groups for each range

--Create Partition Function

USE [ApplicationLogs]
GO
CREATE PARTITION FUNCTION [FN_SchedulerLog](datetime) AS RANGE LEFT FOR VALUES (N'2016-06-30T23:59:59.998', N'2016-07-31T23:59:59.998', N'2016-08-31T23:59:59.998', N'2016-09-30T23:59:59.998', N'2016-10-31T23:59:59.998', N'2016-11-30T23:59:59.998', N'2016-12-31T23:59:59.998')

--Create Parttion SCHEME
CREATE PARTITION SCHEME [sch_SchedulerLog] AS PARTITION [FN_SchedulerLog] TO ([FG_SchedulerLog_06_16], [FG_SchedulerLog_07_16], [FG_SchedulerLog_08_16], [FG_SchedulerLog_09_16], [FG_SchedulerLog_10_16], [FG_SchedulerLog_11_16], [FG_SchedulerLog_12_16], [PRIMARY])

DROP INDEX [pkSchedulerLogId] ON [dbo].[SchedulerLog] WITH ( ONLINE = OFF )

CREATE UNIQUE NONCLUSTERED INDEX [pkSchedulerLogId] ON [dbo].[SchedulerLog]
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE CLUSTERED INDEX [ClusteredIndex_on_sch_SchedulerLog_636102140668795637] ON [dbo].[SchedulerLog]
(
    [Date]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [sch_SchedulerLog]([Date])

DROP INDEX [ClusteredIndex_on_sch_SchedulerLog_636102140668795637] ON [dbo].[SchedulerLog] WITH ( ONLINE = OFF )

--- switching out data from any FGs to a switchout table for archiving purposes
--******************
--Create a switch out table:
USE [ApplicationLogs]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[staging_SchedulerLog_20160923-120700](
    [ID] [int] NOT NULL,
    [Date] [datetime] NOT NULL,
    [Thread] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [HostName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HostIP] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ModuleName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Level] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Logger] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Message] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [FG_SchedulerLog_06_16]

USE [ApplicationLogs]
CREATE UNIQUE NONCLUSTERED INDEX [staging_SchedulerLog_20160923-120700_pkSchedulerLogId] ON [dbo].[staging_SchedulerLog_20160923-120700]
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [FG_SchedulerLog_06_16]
USE [ApplicationLogs]
ALTER TABLE [dbo].[staging_SchedulerLog_20160923-120700]  WITH CHECK ADD  CONSTRAINT [chk_staging_SchedulerLog_20160923-120700_partition_1] CHECK  ([Date]<=N'2016-06-30T23:59:59')
ALTER TABLE [dbo].[staging_SchedulerLog_20160923-120700] CHECK CONSTRAINT [chk_staging_SchedulerLog_20160923-120700_partition_1]

---Switch out partition
ALTER TABLE [dbo].[SchedulerLog] SWITCH PARTITION 1 TO [dbo].[staging_SchedulerLog_20160923-120700];
GO

  Error: ALTER TABLE SWITCH' statement failed. The table 'ApplicationLogs.dbo.SchedulerLog' is partitioned while index 'staging_SchedulerLog_20160923-120700_pkSchedulerLogId' is not partitioned.

Because switching partition change address of all pages to target table, it also moves indexes (created on the source table) to target table. If available index on the table is not partitioned index will not move to target.

When we SWITCH partition, make sure both (source and target) tables has same schema (structure), indexes (should be partitioned) and CHECK constraint.

According to the Error

  Error: ALTER TABLE SWITCH' statement failed. The table
  'ApplicationLogs.dbo.SchedulerLog' is partitioned while index
  'staging_SchedulerLog_20160923-120700_pkSchedulerLogId' is not
  partitioned.

The index [pkSchedulerLogId] is not partitioned.

After you partition the index

DROP INDEX [pkSchedulerLogId] ON [dbo].[SchedulerLog] WITH ( ONLINE = OFF )

CREATE UNIQUE NONCLUSTERED INDEX [pkSchedulerLogId] ON [dbo].[SchedulerLog]
(
    [ID] ASC
    ,[Date]
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
ON [sch_SchedulerLog]([Date])

AND

either add check constraint to both tables

ALTER TABLE [dbo].[staging_SchedulerLog_20160923-120700]  WITH CHECK ADD  CONSTRAINT [chk_staging_SchedulerLog_20160923-120700_partition_1] CHECK  ([Date]<=N'2016-06-30T23:59:59')
ALTER TABLE [dbo].[SchedulerLog]  WITH CHECK ADD  CONSTRAINT [chk_staging_SchedulerLog_20160923-120700_partition_2] CHECK  ([Date]<=N'2016-06-30T23:59:59')

OR

remove constraint from staging table.

ALTER TABLE [staging_SchedulerLog_20160923-120700] DROP CONSTRAINT [chk_staging_SchedulerLog_20160923-120700_partition_1];

Now you will be able to SWITCH partition

ALTER TABLE [dbo].[SchedulerLog] SWITCH PARTITION 1 TO [dbo].[staging_SchedulerLog_20160923-120700];

Thanks


Behavior of STUFF function for empty strings When working on answering another question (on SO), I came across some unexpected (to me) behavior with the STUFF function.
SELECT STUFF
(
    'a', -- character expression
    1,  -- start
    1,  -- length
    'z' -- replaceWith_expression
)

returns NULL, while I would expect it to return 'z'.

Stuff
The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
Syntax
STUFF ( character_expression , start , length , replaceWith_expression )
Remarks
If the start position or the length is negative, or if the starting position is larger than length of the first string, a null string is returned. If the start position is 0, a null value is returned. If the length to delete is longer than the first string, it is deleted to the first character in the first string.
An error is raised if the resulting value is larger than the maximum supported by the return type.

Per the documentation, there are a number of reasons why a NULL string would be returned:

If start is longer than the first character_expression
If start is negative or 0
If length is negative

After closely reading the documentation while posting this question, I realized it told me the answer. I'm still posting it, since I hope it might be helpful to others in a similar predicament.

Per the documentation:

  If start is negative or zero, a null string is returned. If start is longer than the first character_expression, a null string is returned. start can be of type bigint.

This means that, effectively, you can't use STUFF with an empty string (since any argument to start will result in a NULL string).

One workaround is to use a non-empty string, and delete the content. For the above example, you could use STUFF('a', 1, 1, 'z') to delete the initial character.


Is SARGable query the first thing to put in developers' minds? Is SARGable query the first thing to put in developers' minds from a performance tuning perspective?
It's been a while that I work through this way, but I was just wondering if you guys have the same approach, considering Microsoft will not Make more functions SARGable.

This is a bit tough to answer with the lack of details so I will share my experience. I help teach a short class to new developers about SQL optimization at the company that I work for. We do OLAP on SQL Server.

The concept of SARGable arguments is important and definitely mentioned in the class. It can be used to avoid basic mistakes, but it won't be much help for developers if their query is performing badly and they aren't making any of those mistakes. We try to focus on the fundamentals of queries and to teach developers how to interpret query plans. In my opinion, the three most important things to understand well are table access methods, join types, and join order. If developers understand those three things well then they'll be in a good spot to troubleshoot most performance issues, at least with our workload.

By table access methods, I mean how to identify if a table is being accessed through an index or not, when to expect index use, and the consequences of not using an index when you should. Same ideas for scans versus seeks. By join types, I mean the usual nested loop, merge, and hash varieties. When to expect each and what the consequences are when SQL Server gets it wrong from our perspective. By join order, I mean the order of tables in the query plan. If there is a table that greatly restricts the number of rows returned by the query is it processed relatively early in the plan? If not, why not?

If developers have a decent grasp on the basics then they should be well armed to identify lots of different performance issues, including those covered by the SARGable concept.


What is the difference between a foreign key and a primary foreign key? I understand that you use foreign keys to relate rows from one table to the other and if this foreign key also becomes a primary key of that table then it is a primary foreign key.

But, for example, if a hotel records the date and time of check-ins for each customer, and each customer has a customer ID, should I include the customer ID as a primary foreign key or just a foreign key? Because at one time there can be multiple check-ins so date and time is not enough as a primary key?

There is already an accepted answer.  This answer may shed new light on the topic.

Look up "shared primary key".  This is a design technique that is often used when there is a relationship that is one-to-one, and mandatory in one direction but optional in the other.  IS-A type relationships fit this pattern.  For example, every car is a vehicle, but not very vehicle is a car.  (Some are trucks or motorcycles, etc.).

This pattern is called superclass/subclass in object modeling,  and generalization/specialization in ER modeling.

In Shared Primary Key,  one column in the table is declared as both the primary key in this table,  and a foreign key that references the primary key of some other table.  continuing our example,  the ID field in the CAR table would be the primary key,  and also a foreign key that references the PK of the VEHICLE table.

This allows a each car to have an assigned identity, without introducing yet another identifier,  and it allows for rapid joins between VEHICLE and CAR.

You'll get more details when you do the look up.

Note that I've avoided the term "primary foreign key" which is confusing at best.


how to change the driver of a ole_dbconnection from oracle_32bit to oracle_64bit i have the connection string of:

connection= new OleDbConnection("Provider=OraOLEDB.Oracle;UserId=userid;Password=password;
Data Source=database;OLEDB.NET=true");

However after Oracle_64bit driver was installed on my computer in another process this doesn't work anymore. I read something about making a connection string like:

Driver={Oracle in OraClient12Home1_32bit};Dbq=database;Uid=userid;Pwd=password;"

I can't make that work however, and would like to hear if there's some simple fix for 32-bit to 64-bit of oledbconnection?

Even in this day and age, Windows is still reliant on the good, old-fashioned DOS PATH environment variable for finding dynamically linked libraries (DLLs).

It is possible that the 64-bit installation has altered the PATH variable (usually to insert its own location at the start of the "list").  This means that the 32-bit application, while searching through the DOS PATH, will find correctly-named 64-bit libraries before the 32-bit ones that it requires.


Why is this Full Outer Join not working? I've used Full Outer Joins before to get my desired results, but maybe I don't fully understand the concept because I am not able to accomplish what should be a simple join.

I have 2 tables (which I'l call t1 and t2) with 2 fields each:

t1

Policy_Number Premium
101             15
102              7
103             10
108             25
111              3

t2

Policy_Number   Loss
101              5
103              9
107              20

What I am trying to do is to get the sum of Premium and Sum of Losses from both tables and also the Policy_Number. The code I am using is:

select sum(premium) Prem_Sum, sum(Loss) Loss_Sum, t1.policynumber
from t1 full outer join t2 on t1.policynumber = t2.policynumber
group by t1.policynumber

The above code will return the correct sum totals but it will group all records where there isn't a policy_number match under "NULL" policy_number.

I would like my result to look like this

Policy_Number    Prem_Sum    Loss_Sum
    107            NULL        20
    111              3        NULL
    101             15          5

etc.....

I do not want a result that shows a NULL policy_number as shown below (since there is no such thing as a NULL policy_number. This is just the total for when the policy_number from both tables don't match):

Policy_Number    Prem_Sum   Loss_Sum
   NULL            35         NULL

If I Select and group by t2.policy_number instead of t1.policy_number then I get something like below as a record.

Policy_Number    Prem_Sum   Loss_Sum
   NULL            NULL         20

Again, I don't mind seeing NULL under Prem_Sum or under Loss_sum but I don't want a NULL under Policy_Number. I would like my results to be something like

Policy_Number    Prem_Sum    Loss_Sum
    107            NULL        20
    111              3        NULL
    101             15          5

ect.....

I thought the full outer join would accomplish this but I guess I am missing something. I was thinking maybe I could select and group by both t1.policy_number and t2.policy_number as a sub query and then maybe do a CASE in the outer query or something??? I don't think it should be this complicated.

Any ideas or advice?

You should do an isnull on both policynumbers so that you can group properly.

Since it's an outer-join, there's the possibility of one side of the join being NULL while still having data.

select sum(premium) Prem_Sum, sum(Loss) Loss_Sum, isnull(t1.policynumber, t2.policynumber)
from t1 full outer join t2 on t1.policynumber = t2.policynumber
group by isnull(t1.policynumber, t2.policynumber)


Alternative to SQL Server Management Studio and Azure Data Studio to initially change my password Background

I'm having to connect to a customer's Microsoft SQL Server. I generally don't need to do much in databases besides your average SQL, so using the database features in IntelliJ IDEA have so far been more than sufficient for various PostgreSQL and Orcacle databases. In this case though I was given a heads-up that I will have to change my password first, and when I use IDEA to connect with the credentials I received it does indeed reject them and gives me an error message saying I need to change my password. I was also told to use SQL Server Management Studio (SSMS) to do so.

Problem

SSMS is only for Windows, I'm running a Mac. The SSMS download page refers to Azure Data Studio for use on a different OS. An initial try of the latter did not let me change the password though. Even if I eventually find a way to do it, is there really no other easier way to do this that doesn't involve installing another full-blown db tool that I have no intention of using in the long run? Or worst case: buy a windows computer for the sole purpose of installing SSMS so I can change a password? Surely there must be a bunch of Mac- or Linux-using DBA's that know of a simple command-line tool or alike that allows them to change their password initially on a Microsoft SQL Server, after which they just use their favorite db management tool to access it?

Edit: Note on "duplicate"

I disagree that this is a duplicate of Handle expired SQL login's passwords in SQL Server because:

That question involves an expired password, while I was never able to log in in the first place, nor able to run any sort of SQL prior to logging in
That question involves someone already using SSMS, whereas I've specifically pointed out I cannot use it and I'm asking for an alternative

Additional clarification

When I said customer, I meant a client that has data that I need to pull. I do not have any sort of admin access to the VM or SQL Server beyond the credentials I was provided
The error I'm getting is not a notification while I'm logged in. It's an error preventing me from even connecting, so I cannot run any other SQL or commands whatsoever

mssql-cli is a command-line tool, available for Mac, Linux and Windows. See https://github.com/dbcli/mssql-cli/tree/master/doc/installation.

It isn't clear, however, if you can't login in the first place. If you can, then just change the password using ALTER LOGIN. If you can't, then you rely on the tool in question to (using any of the SQL Server APIs) to allow you to set a new password. SQLCMD (another command line tool, Win lony) for instance has command-line options for this. mssql-cli doesn't seem to have such options, at this point in time.


How do I get useful SQL Server database performance statistics? We currently have several shared SQL Server servers and instances in our organization. They are going with a different architecture where business units will get their own SQL Server instances. We have SQL Server 2008R2.

In the past, databases were put on servers in a more or less random fashion -- business use and disk space were the main drivers.

I've been tasked with coming up with a plan for allocating our 50+ databases across 3 servers (one instance per server). My criteria for divvying them up is as follows:

Business critical db's should be spread across the servers. i.e. avoid lumping a bunch of business critical db's all one server.
For the remainder of the non-mission critical db's, consider:
a. Disk space usage
b. I/O usage -- memory, CPU, etc.

1 and 2a are easy for me to figure out. I know how to get data file sizes.

My question is with that last bit - 2b. I do not know how relevant this is, what specific stats to look at, or how to get them.

Frankly, I'm not 100% sure how to phrase this question! I think I basically want to know is, "How 'busy' is this db -- however you define 'busy'?" Or maybe, "How does this db impact the performance of the server?"

I mean, disk space is important -- but it could (in theory) have a ton of rows or binary objects in there that are not written very often, so it has little impact on the performance of the server. I think I would want to know how much impact a db within a date range.

Is there a way to get these kind of general stats? I'm not looking for expensive queries, detailed user stats, or query optimization here. I'm aware of the SSMS reports, but they seem to be more real-time. The end goal is to divide up our current databases into evenly balanced buckets, and I would think performance is one way to do that.

Thoughts? Wisdom? Any other ideas on how to divvy up the db's?

Thanks! Tom

You can gather data you need with Data Collector, which is a part of the SQL Server Management Studio (not in all SQL Server editions though), but it’s features are limited and it won’t show you any real-time data.

It seems you need an ad-hoc solution and, if that is the case, it’s better to use some 3rd party monitoring solutions to gather data and get the job done during free trial. All these tools are great and are offering fully functional free trials:

ApexSQL Monitor

Idera SQL Diagnostic Manager

Redgate SQL Monitor

SQL Sentry


what is the difference between data file and data block in oracle I'd like to know if there is any difference between the data block which is the  smallest logical unit of data storage in Oracle Database and the data file which is a physical file on disk that was created by Oracle Database and contains the data for a database?

Can anybody please tell me that what exactly difference between the data file and the data block?

The Logical Storage Structures section in the Oracle Concepts Guide has a good explanation of both data blocks and data files (pages 259 - 296).  There is quite a bit of good information including illustrations, but here are some excerpts:

  data block
  Smallest logical unit of data storage in Oracle Database.
  Other names for data blocks include Oracle blocks or pages. One data
  block corresponds to a specific number of bytes of physical space on
  disk.

  data file
  A physical file on disk that was created by Oracle Database
  and contains the data for a database. The data files can be located
  either in an operating system file system or Oracle ASM disk group.


Partitioning visitor logs database I have set up a table that logs every page view a visitor makes. This is going to result in a table with about 50,000 entries per week.

To keep things manageable and performing well I am intending on moving the content of the table to an archive each week or month.

I have a couple of questions, the first is how often would be sensible to move the contents?

Secondly what is the best way to move the contents?

This is a mysql database and the table has 11 columns mostly int or varchar averaging 80 characters.

When it comes to the archive should that be a series of tables or again split but maybe to a lesser degree?

As for how often, I would say that depends entirely on your situation. e.g. How much data do you need to access, how much disk space do you have, how far back will you need to go.

I have a similar process here for similar data, which I partition by month, and then at the start of each month I simply have a scheduled Event that runs and drops the oldest Partition, and creates a new one. This is quick and simple, and keeps the disk space relatively constant. Depending on your current table structure this may or may not be so easy to do.

If you wanted to keep the data, you could simply add a new partititon to the top at the start of the month, creating one huge table that remains searchable, but is held on the system as individual files. If you are using MySQL 5.6 you can even select by partition (a good index also helps).

see How to split a MySQL table file? for a similar discussion.

The other option, if you want to keep things accessible, (which I also do with another system) is to simple run a scheduled event at the start of the month to create a new empty table (CREATE TABLE tablename LIKE..., and rename the old one e.g. tablename_201505. I also mainstain another table to keep track of which months data is in each table for easy refenrence by our applications.


Benefits of not having a clustered index on tables (heaps) What are the benefits of not having a clustered index on a table in SQL Server?
Will:
SELECT * INTO TABLE_A FROM TABLE_B

Be faster if TABLE_A is a heap?
Which operation(s) will benefit if the table is a heap?
I am quite sure UPDATEs and DELETEs will benefit from a clustered index. What about INSERTs? My understanding is that INSERT "might" benefit from the table being a heap, both in term of speed but also other resources and hardware (I/O, CPU, memory and storage...).
What is the most scarce resource in terms of hardware? In terms of storage is a heap going to occupy less space? Is disk storage not the least expensive resource? If so is it rational to keep table as heap in order to save disk space? How will a heap affect CPU and I/O with SELECT, INSERT, UPDATE and DELETE? What cost goes up when table is a heap and we SELECT, UPDATE and DELETE from it?

I'm only addressing the question in the title. The question body has too many different questions to be answered in a stack exchange answer. Heaps have the following advantages over tables with clustered indexes:

Tables with clustered indexes physically organize the data in order of those indexes. The SQL Server query optimizer may add a sort to a query plan for large enough inserts if the data to be inserted isn't already sorted in clustered key order. The additional work performed by the sort is not free and the query can run slower as a result. Inserts into non-partitioned heaps do not need sorted source data.

The rules for minimal logging work differently between heaps and clustered indexes. In some scenarios, an insert into a heap will write fewer bytes to the transaction log than a comparable insert into a table with a clustered index. Reference the Data Loading Performance Guide for some examples.

A clustered index prevents parallel insert into a table. The insert part of the query plan will always be located in a serial zone (DOP = 1). Starting with SQL Server 2016, an insert into a heap with no nonclustered indexes can qualify for parallel insert.

A clustered index will add lock and latch contention when many separate processes are inserting into the same table. Heaps with no nonclustered indexes can be loaded by many processes concurrently with less contention, especially when those inserts are sourced outside of SQL Server with Bulk Update locks.


Postgresql, commas in string_agg function Need

FIELD like this, with ' and without , at the end of whole field.

{name:'color',value:'red'},{name:'width',value:'300 ml'}

Tried

string_agg(distinct '{name:\'' || param_name || '\',value:\'' || value || '\'},','') as "FIELD"

but error behind 2nd \

SQL Error [42601]: ERROR: syntax error at or near "\"

With , at the end of whole field, I don't know how to do that.

Thanks.

If you want to generate JSON, it is easier to use JSON functions for that, e.g.:

string_agg(distinct jsonb_build_object('name', param_name, 'value', value)::text, ',')

Or create a proper JSON array of the result:

jsonb_agg(distinct jsonb_build_object('name', param_name, 'value', value))

Online example: https://rextester.com/EPOX97933


How shared nothing and shared everything architectures look like in Oracle? Oracle has partitioning and clustering features. Partitioning enables to split table or indexes into multiple tablespaces and store on various servers. So it's done manually. Clustering enables several servers to make operate as one. This IMHO is handled transparently. However, it's possible to have several tables (in different server databases) and group them as one, and use it. From the perspective on table partitioning and table clustering, IMHO both ways implement shared everything because the data is split into the different locations.

The theory about shared nothing architecture is that each database node is independent. But how this looks in practice? Both ways (partitioning and clustering) splits data into different sources and could be also described as horizontal partitioning. However, if we had for example, two different database servers we would need to synchronize the data between them... Anyway, any thoughts on that would be appreciated :)

Shared nothing typically refers to hardware, though it could also be used when describing an SOA architecture.

From a hardware perspective, a "shared nothing" Oracle database simply means a machine using local disks, memory, etc.

Oracle RAC (real application clusters) is a shared disk architecture, but not shared memory. Horizontal scaling is then achieved by adding more machines to the cluster.

When talking in SOA terms, "shared nothing" means that each service has a corresponding database which is only accessed by that service. So the ACCOUNTS service accesses the ACCOUNTS_DB, ORDERS service the ORDERS_DB and so on. These databases could be shared nothing from a hardware perspective as well, or use RAC.

Ensuring consistency of data and references which would normally be handled using foreign keys becomes a challenge in SOA shared nothing databases.

Sharding typically refers to partitioning managed at the application level, rather than within the database. For example, you could partition accounts by email address and direct customers with address starting A-C to ACCOUNTS_DB01, D-F ACCOUNT_DB02 and so on. The shard mapping could be a simple range like this, a function on the input or a lookup database stating which database is stored in. The databases would be "hardware shared nothing" in this case as the idea is you use relatively cheap machines which are easily added and replaced.

You could shard your databases at the application level and still have Oracle partitioning at the table level within the database itself. So you could shard your ORDERS database by customer, then partition the orders table by order date as well inside the database.

The downside to both meanings of shared nothing comes if you frequently run queries that have to access several databases. In these cases your joins will be pushed into the application layer rather than the DB layer so are likely to be slower. Good governance is necessary to ensure this doesn't happen.


Can SQL Agent job notifications be sent to one operator for success and another for failure? We have a requirement that some people are sent notification on success, and other on failure.

But the UI seems to only allow a combination of 1 operator + 1 condition.

So I was wondering if we could do perhaps by transact sql in the MSDB?

Quite frankly, I would just not use the limited operators facility through the UI and instead:

Create a step that only gets reached when the job succeeds, which e-mails person 1
Create another step that only gets reached when any step fails, which e-mails person 2

(You may need to modify multiple steps that could potentially fail, such that when they do fail, processing moves immediately to the second step above.)

You can still use operators if you like, just have the steps manually call sp_notify_operator.


Backup all privileges related to specific user of MySQL I want to backup all privileges related to specific user (for example u_1) from a MySQL database and restore that in another server. As described here, The 'mysql' database contains users/privileges/passwords. So I have to backup all related data from all tables of mysql database (mysql.user,mysql.db, ...). I can run this command:

$ mysqldump -u root --flush-privileges -r mysql.sql -p mysql

But of course the mysql.sql contains all users and all privileges.

Also I tried this command:

$ mysqldump -u root -p --where="user.user='u_1'" mysql user> mysql.sql

But as expected, it only contains a row of mysql.user table.

Is there a way to strip out other users except of u_1?

Capture the output from

SHOW GRANTS FOR 'u_1'@localhost;
SHOW GRANTS FOR 'u_1'@'%';

(and any other 'hosts' involved.)

Then replay GRANTs to establish hist credentials elsewhere.


Restored DB has data, DBCC CHECKDB is clean, but SELECT TOP 1 returns zero rows Having restored a client's SQL 2014 EE DB onto our Developer Edition instance, without issue, then running a DBCC CHECKDB that found zero errors, and a clustered columnstore index alleging over 1Bn rows, a simple SELECT TOP 1 FROM TheBaseTable returns no data - having taken 6m 38s to run, using 3845 reads, 155 physical reads, and over 400K ms CPU, so it gives the appearance of doing something.

Oddly, compatibility mode is at 2008 upon restore.  Changed it to 120 and page verification to CHECKSUM.

Fixed the SQL Login SID (only 1 orphan).  The login has db_owner, db_accessadmin, and db_securityadmin role membership, so it doesn't appear to lack privileges, but belts-and-braces-style it now also has db_datareader and db_datawriter memberships.

Ran sp_updatestats because the DMVs show rows but SELECT statements return zero.

Is it possible that the client's having set the compatibility down to 2008 with there being a CCSI in place has messed up the metadata?

Has anyone heard of anything like this, please?

Any ideas on how to see the data?

Here's the table - I see nothing fishy...

CREATE TABLE dbo.tbl_HistoryByType(
    InventoryID int NOT NULL,
    SalesTypeID int NOT NULL,
    ReturnFlag bit NOT NULL,
    SalesDate datetime NOT NULL,
    SeasonID int NULL,
    SalesQuantity float NOT NULL,
    TotalRevenue float NOT NULL,
    TotalCost float NOT NULL,
    TotalOriginalRetail int NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED COLUMNSTORE INDEX CCSIX_tbl_HistoryByType ON dbo.tbl_HistoryByType WITH (DROP_EXISTING = OFF, DATA_COMPRESSION = COLUMNSTORE) ON [PRIMARY]
GO

Hope someone can lend a hand as I've never seen anything like this in over 10 years with SQL Server...!

I have absolutely no idea why compatibility would be set to 2008, especially when we are to evaluate the DB code and performance.

  Microsoft SQL Server 2014 - 12.0.4416.0 (X64)
  Jun 11 2015 19:18:41
  Copyright (c) Microsoft Corporation
  Developer Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

Sadly, the original system is behind iron bars, so to speak. I'll inquire about any kind of select-only access. Strangely enough, I ran an ALTER TABLE...REBUILD and after DBCC CHECKTABLE scored zero rows.  1Bn+ "disappeared"  Will look at applying the latest CU, too - this server is heavily shared, so will have to schedule it.

As there's no information available about the cause and cure, sadly this will be a closed thread with no resolution.

Sadness all around.


Performance degradation after rebuilding indexes I am optimizing some server application task that uses database (querying, complex calculations, data insertions...). Execution of this task spends about 16 minutes (I have tested it 3 more times) and it predictable time to do it.
Then I executed script:

ALTER INDEX ALL ON dbo.'+ @TableName +' REBUILD

for every table in a database.
And what I see now. The time of execution my task is increased to 24 minutes. What's going on if these is not any external influences on this task?  I was waiting for increasing of performance (due rebuilding fragmented indexes) but got degradation.

It's because when you rebuild indexes, you also necessarily rebuild the statistics on each index as it happens.  That means that SQL Server rebuilds internal "tables" of data about the distribution of data in your indexes themselves.  The distribution of data indicated by your statistics govern how the cost-based query optimizer chooses to create plans for your queries.

In essence, it means that SQL Server is probably executing your queries using a different plan than it did before because the distribution of your data changed.

There's no simple fix to this.  It involves real detective work.  Start with SQL Server Profiler and get an idea of what queries are actually running in this job.  Analyze each query plan in detail and find out what parts of the query are taking the most time, and adjust your query, indexing scheme, or even your schema accordingly.


Sum of different table counts I have 2 different query and i want to summarize their count in other query like this:

    select a,((select count(id) from foo) + (select count(id) from bar))
    as 'alpha' from beta;

Is there a way to do it on mysql?

It worked as,

select a,(select (select count(id) from foo) + (select count(id) from bar))
as 'alpha' from beta;


Tablock hint triggers deadlocks I was inserting two data sets, using minimal logging, into an empty heap table using via two Execute SQL Tasks running in parallel and with SQL of the following form.

INSERT INTO Table (TABLOCK) SELECT FROM ...

After the job hangs a bit, one of the SQL tasks became a deadlock victim. Below is XML output of the deadlock graph.

Can someone explain what was happening under the hood?

  <resource-list>
   <objectlock lockPartition="0" objid="1586156746" subresource="FULL" dbid="7" objectname="dbo.TargetTable" id="lock7374a00" mode="IX" associatedObjectId="1586156746">
    <owner-list>
     <owner id="process9609dc8" mode="Sch-S"/>
     <owner id="process9609dc8" mode="IX"/>
    </owner-list>
    <waiter-list>
     <waiter id="process5e13048" mode="X" requestType="convert"/>
    </waiter-list>
   </objectlock>
   <objectlock lockPartition="0" objid="1586156746" subresource="FULL" dbid="7" objectname="dbo.TargetTable" id="lock7374a00" mode="IX" associatedObjectId="1586156746">
    <owner-list>
     <owner id="process5e13048" mode="Sch-S"/>
     <owner id="process5e13048" mode="IX"/>
    </owner-list>
    <waiter-list>
     <waiter id="process9609dc8" mode="X" requestType="convert"/>
    </waiter-list>
   </objectlock>
  </resource-list>

Things get a lot trickier because I found that for most cases the two Execute SQL Tasks can run in parallel successfully. Try below:

Create table dbo.TablockInsert (c1 int, c2 int, c3 int)

--then issue the script in two Execute Sql Task in parallel you won't fail:
insert into dbo.TablockInsert(TABLOCK) SELECT 1, 1, 1

Since the only difference is the SELECT... FROM... statement, looks like the SELECT... FROM... statement can have an impact on the lock mode here?

The Data Loading Performance Guide was written for SQL Server 2008 but as far as I can tell Microsoft hasn't made any improvements in this area for heaps. Here's a quote for your loading scenario:

  Bulk Loading an Empty, Nonpartitioned Table

  Loading data into a nonpartitioned table, while a simple operation,
  can be optimized in several ways.

  ...

  Multiple, concurrent insert operations for heaps are possible only
  when the chosen bulk method issues bulk update (BU) locks on the
  table. Two bulk update (BU) locks are compatible, and hence two bulk
  operations can run at the same time.

  In this scenario, both INSERT … SELECT and SELECT INTO have a
  drawback. Both of these operations take an exclusive (X), table level
  lock on the destination. This means that only one bulk load operation
  can run at a given time, limiting scalability. However, BCP, BULK
  INSERT, and Integration Services are all capable of taking bulk update
  (BU) locks – if you specify the TABLOCK hint.

The important part is that you don't get a BU lock with INSERT ... SELECT. You'll always get an exclusive lock on the table, so only one INSERT can run at a time.

In the comments you said that you'll insert 100k rows or less and that other processes won't be running on the tables during the inserts. When sending two INSERT queries to the database I would expect one of three things to happen:

One insert runs first and blocks the other insert. The second insert waits until the first insert is done.
One insert finishes before the second insert starts. There is no explicit blocking but they aren't run concurrently.
You get a deadlock and only one insert completes successfully.

In all cases you either benefit or aren't hurt by adding a TABLOCKX hint to the query, so that is my recommendation of working around the deadlock. If you want to know why the deadlock sometimes happens you'll need to look to another answer for that.

For in a different scenario in which you really need parallel insert, two ways of working around the BU issue are to partition your heap and to have each session insert into a separate partition or to load your data through BCP, BULK INSERT, or Integration Services.


ORA : 12519 No appropriate Service Handler Sorry if my question has already been asked many times but I spent two days trying to solve it.

I have a machine in which I had an Oracle database. Everything was working propertly until I added two other databases.

Now, I can connect only to the last database created.

The three databases have the same connection settings (except the SID) :

Host : localhost
Port : 1521
SID : Database1, Database2, Database3 (the last one created)

My tnsnames.ora file is as follows :

DATABASE3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = DATABASE3)
    )
  )

DATABASE2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = DATABASE2)
    )
  )

DATABASE1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = DATABASE1)
    )
  )

LISTENER =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

My Listener.ora file is as follows :

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = DATABASE1)
      (ORACLE_HOME = E:\app\user\product\11.2.0\dbhome_1)
      (ENVS = "EXTPROC_DLLS=ONLY:E:\app\user\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (SID_NAME = DATABASE2)
      (ORACLE_HOME = E:\app\user\product\11.2.0\dbhome_1)
      (ENVS = "EXTPROC_DLLS=ONLY:E:\app\user\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (SID_NAME = DATABASE3)
      (ORACLE_HOME = E:\app\user\product\11.2.0\dbhome_1)
      (ENVS = "EXTPROC_DLLS=ONLY:E:\app\user\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = E:\app\user

When connecting to the databases :

Database3 : Works fine !
Database2 : ORA 12505, TNS:listener does not currently know of SID given in connect descriptor.
Database1 : ORA 12519, TNS:no appropriate service handler found

I restarted the listener many times.

The command lsnrctl status returns (sorry it's in french)

Connection to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS du PROCESSUS D'ECOUTE
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date                07-JUIL.-2014 09:24:42
Activity                  0 jours 12 heures 45 min. 0 sec
Niveau de trace           off
Sécurité                  ON: Local OS Authentication
SNMP                      OFF
Fichier de paramétres du processus d'écoute      E:\app\user\product\11.2.0\dbhome_1\network\admin\listener.ora
Fichier journal du processus d'écoute           e:\app\user\diag\tnslsnr\MyComputer\listener\alert\log.xml
RÚcapitulatif d'Úcoute des points d'extrÚmitÚ...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MyComputer)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
RÚcapitulatif services...
Le service "CLRExtProc" comporte 1 instance(s).
  L'instance "CLRExtProc", statut UNKNOWN, comporte 2 gestionnaire(s) pour ce service...
Le service "Database1" comporte 1 instance(s).
  L'instance "Database1", statut UNKNOWN, comporte 1 gestionnaire(s) pour ce service...
Le service "Database3XDB" comporte 1 instance(s).
  L'instance "Database3", statut READY, comporte 1 gestionnaire(s) pour ce service...
Le service "Database3" comporte 1 instance(s).
  L'instance "Database3", statut READY, comporte 1 gestionnaire(s) pour ce service...
La commande a réussi

I read in a forum that this might be a matter of connections maximum reached, I'm sure it's not the problem because no one is connected to my machine.

If any one can provide some help, I will be grateful.

Thanks.

http://docs.oracle.com/cd/E18283_01/network.112/e10836/concepts.htm

sales=
 (DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
  (CONNECT_DATA=
     (SID=sales)
     (SERVICE_NAME=sales.us.example.com)
     (INSTANCE_NAME=sales)))

It looks like you haven't put the SID in your TNSNAMES.ORA.


sort a large table by its primary key and save it back So I have a large table (>50 million rows) with an auto incremented primary key column, which is filled by a script running on multiple cores. Now, when I do a SELECT, entries are returned 'out of order', which, I guess, is the result of the table being filled by several processes in parallel.

I could do a SELECT ... ORDER BY <primary key>, but this select takes much more time than the simple SELECT.

Is there a way to sort the table by its primary key and save it back, without loading all rows into memory? I found a similar question answered here, but I cannot apply it to my case without adding another column, which, given the size of the table, I'm reluctant to.

I'm Running Firebird Superserver

Any advice appreciated.

Unless you specify an ordering using ORDER BY you can never guarantee the order of the output, even if you are very careful what order you put things in.

I'm not familiar with Firebird, but in many databases the difference could be explained by your primary key not being clustered, so when with the ORDER BY present it scans down the index in order then for each row looks up the extra data from the heap, where the version without explicit sorting will just scan the heap. With a clustered index, the index and the heap are one in the same so this extra work is not needed. Another way to avoid extra lookups is to include all the other columns that you need in an index that the DB can use. This is not space efficient though, especially for wide tables. To see if post-scan lookups are your performance problem see how SELECT <pk> FROM <table> ORDER BY <pk> compares to your other timing tests.

The above assumes you have no other filtering clauses, or joins and the large table is not seen as the primary one by the query planner, that complicate matters. If you do have those you should present fuller details of he query and table structure for better help. It may be that adding the ORDER BY is forcing a more complex query to spool the data out to a temporary table (potentially on disk) before rereading in order. Index use hints may help with this sort of problem, but without further details of the query and structures we cant' be more specific on that.


Get Job id OR Job name from within executing job I have a central DB that I write job outcomes on all my servers back to. I pass 3 parameters via powershell in a SQL job back to a sp on the central server that verifies the job should be running at that time, etc. The info is then exposed via SSRS so we can see an job failures / long running jobs / & jobs that haven't run but should have(or if someone messed with a schedule).

To do this, I have 2 job steps added into each job on every server and I would like to reduce the script down to just 1 step added to each job..possibly even call it from a network share..

But my issue is one of the 3 parameters I pass. I need to get the executing jobid or job name from within the executing job so I don't have to hardcode the name parameter.
The 3 parameters I pass are jobid, status(success/fail), errormsg. The powershell script I wrote is pretty straightforward.

Invoke-sqlcmd -ServerInstance "MYRemoteSYSTEM" -Database remoteDB -Query "exec dbo.JOB_LOG  'JOBNAME/ID','Success/FAIL','BAD MESSAGE HERE'"

This writes what I need to the table. I've looked at
msdb.dbo.sp_help_job /
msdb.dbo.sp_get_composite_job_info /
dbo.xp_sqlagent_enum_jobs /
but none of these will guarantee I get the ID or name of the correct executing job in the event that there are more than 1 jobs executing at the same time.

I've even tried looked at sys.sysprocesses but I think since the agent job is a powershell script, it shows as ".Net SqlClient Data Provider" so I can't trim the binary JOBID off the jobs that show as "SQLAgent - TSQL JobStep (Job 0xF1800243164745429C30474FFD5C990C : Step 1)"
---this I learned from Denny cherry's post - thanks denny-

Any ideas on how to grab the executing jobid would be greatly appreciated.

Thanks,

Chris

You will have to use tokens in your job steps to get your own job id. Details here: Using Tokens in Job Steps.

At the end of the article there's one example with jobid:

SELECT * FROM msdb.dbo.sysjobs
WHERE @JobID = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID))) ;


Is it possible to configure backup filename conventions for Maintenance Plan Wizard in SSMS Is there a way to configure SSMS so that the backup file names for differential backups (created using the Maintenance Plan Wizard) would have the string  "DIFF" in the name?

I would recommend using Ola's scripts.

https://ola.hallengren.com/sql-server-backup.html

Not only are they awesome but they will give you the naming convention that you are looking for.


Resource Governor on 2014 - CPU Not Being Throttled We have a specific query ran from our platform that consumes a lot of CPU on a box that already has Enterprise, so Resource Governor seemed like a good solution. I'm working on configuring it on our Dev server, but I'm not seeing the behavior I expected. The server has 4 cores and 16GB of memory with SQL Server 2014 on Server 2012 R2. I modified my resource pool and workload group like so:

USE [master]
GO
ALTER RESOURCE POOL [CRTestRP] WITH(min_cpu_percent=5, max_cpu_percent=5,        cap_cpu_percent=5,
min_memory_percent=0, max_memory_percent=100, AFFINITY SCHEDULER = AUTO, min_iops_per_volume=0,
max_iops_per_volume=0)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
ALTER WORKLOAD GROUP [CRTestWG] WITH(group_max_requests=0, importance=Low,     request_max_cpu_time_sec=0,
request_max_memory_grant_percent=25, request_memory_grant_timeout_sec=0, max_dop=1) USING [CRTestRP]
GO
ALTER RESOURCE GOVERNOR RECONFIGURE

I realize that Resource Governor won't kick in unless there's resource contention. To test the effects, I created a simulated workload on the server (it's a node that holds secondary replicas for Availability Groups, so mine are the only queries against) that raised CPU to an average of 88% using HammerDB. I've verified my test queries are getting correctly put under my workload group by the classifier function, and I have a few test queries ready to go.

After kicking off HammerDB and letting it run for a few minutes, I kicked off one of the CPU intensive queries. It does throttle that one effectively, holding it to ~5% CPU, however, when adding additional connections running the same query, CPU Usage for that workload group shoot up and negatively affect the default pool:

In the graph above, CPU is at 100% for the entire time frame. The query in question doesn't seem to be relevent, other than the fact that it would go parallel if not for the MAXDOP 1 on the workload group.

Has anyone else seen this behavior? Has anyone had success with Resource Governor at all? What I really wanted out of it was that these queries wouldn't EVER be able to use above a certain amount of CPU for all of them combined at any one time, but it doesn't look like that's the case. It seems like there should be a configurable value for what CPU % constitutes "contention".

In any case, thank you all for your help!

I actually ended up engaging Microsoft on this. The explanation that the engineer gave is that RG doesn't do a good job at throttling an OLTP vs. OLAP type workloads on the same instance. Our use case was a server with 1000's of tiny queries taking 1-2 ms, with occasional queries taking 6-7 seconds, with the longer queries being the ones we wanted to throttle. He acknowledged this was a bug and they would fix in a future release. Apparently, RG is getting rewritten from scratch in SQL 2016 to resolve these particular issues.


Running SQL job: The login is from an untrusted domain and cannot be used with Windows authentication I have two databases in different servers (server1 , server2)

server1:
Login : login1 /  password :123 /member of sysadmin (user user1,password:pass)
server2 :
Login :login1 /   password :123 /member of sysadmin (user user1,password:pass)

when I try to connect to server1's linked server from server2 , I can do it without issue in stored procedures.but when I try to run sp in sql jobs in server2, this issue happens:

  The job failed.The Job was invoked by User user1.
  Executed as user: Server2\Administrator. Login failed. The login is
  from an untrusted domain and cannot be used with Windows
  authentication. [SQLSTATE 28000] (Error 18452).  The step failed.

(the owner of sql job is Login1)

Whats the point?Thank you for any help.

The job is executed as the SQL Agent account, unless you have configured a proxy.  The owner of the job is irrelevant as long as the SQL Agent account can verify that the owner is valid (still exists in AD/SQL).

Verify which account is running the job, either the SQL Agent account or the Proxy account. Then verify that account has permissions into the linked server.  Or create a proxy account for this job.


Verifying connection pooling server side with CONTEXT_INFO on I'm working with a 3 tiered application, Microsoft Dynamics AX, where the middle tier maintains connections to a SQL Server. Several clients connect to this middle tier server.

The middle tier server typically has several connections open to the SQL Server, so I'm pretty sure they are being pooled, however there is no documentation available as to how this is implemented.

Normally we cannot relate SPIDs to users or client applications, but there is an option where we can set a registry key (specific to Microsoft Dynamics AX) which makes this information available in the context_info field of sys.dm_exec_sessions.

Again, there is no documentation about how this is implemented. The only information we have on this is a vague blog entry on MSDN.

The post mentions

  Adding  this information has a small performance overhead.

So as we don't know any of the implementation details such as:

Is the information somehow included in the connection string or is this done by SET CONTEXT_INFO?
When are connections reused?
What exact impact can be expected

Is there any way to determine server side how the connection pooling is working and what the impact of the context_info is?

update:
Using this query from here

SELECT des.program_name,
       des.login_name,
       des.host_name,
--       der.database_id,
       COUNT(des.session_id) AS [Connections]
FROM sys.dm_exec_sessions des
INNER JOIN sys.dm_exec_connections DEC
        ON des.session_id = DEC.session_id
WHERE des.is_user_process = 1
--AND des.status <> 'running'
GROUP BY des.program_name,
         des.login_name,
         des.host_name
--         ,der.database_id
HAVING COUNT(des.session_id) > 2
ORDER BY COUNT(des.session_id) DESC

I can see connection pooling is used.

First, CONTEXT_INFO is a property of the session, not the connection. It gets reset by sp_reset_connection when the same session is reused and the first batch is executed. It seems that CONTEXT_INFO was not reset in SQL Server 2000 and possibly earlier versions, but starting with SQL Server 2005 it is definitely reset to NULL.

Part of the confusion here is that the question is specific to "Microsoft Dynamics AX" since general .NET programming wouldn't have the registry key noted in that blog article. Also, the information that Microsoft Dynamics is then storing in CONTEXT_INFO is its application session details, which have nothing to do with SQL Server SPIDs and cannot be used to infer that connection pooling is occurring since the application session will naturally span multiple connections as well as SPIDs.

The mechanism being used to set CONTEXT_INFO pretty much has to be a separate, additional query executed prior to any other queries for that session. Something along the lines of:

SqlConnection _Connection = new SqlConnection("{connection-string}");
_Connection.Open();

if(_IsConnectionContextRegistryKeySet)
{
  SqlCommand _Command = _Connection.CreateCommand();
  _Command.CommandType = CommandType.Text;

  _Command.CommandText = @"DECLARE @BinaryInfo VARBINARY(128);
                           SET @BinaryInfo = CONVERT(VARBINARY(128), @StringInfo);
                           SET CONTEXT_INFO @BinaryInfo;";

  SqlParameter _ParamInfo = new SqlParameter("@StringInfo", SqlDbType.VarChar, 100);
  _ParamInfo.Value = String.Format("{0} {1} {2}...", AXuserID, AXsessionID, ...);
  _Command.Parameters.Add(_ParamInfo);

  _Command.ExecuteNonQuery();
  _Command.Dispose();
}

Hence, the small amount of additional overhead incurred for enabling this info to be set comes from the execution of this additional query.

Second, you can test for connection pooling by using SQL Server Profiler. Select the "RPC:Completed" event in the "Stored Procedures" category, make sure that "TextData", "ClientProcessID", and "SPID" are checked for that event (at the very least, you can select other columns if you like). Then, go to "Column Filters", select "TextData", and in the "Like" condition add the following condition: exec sp[_]reset[_]connection. Now run that trace. If you see instances of exec sp_reset_connection coming through, then that is due to connection pooling being used.

Additionally, there are two side-effects of connection pooling that may or may not show up in the DMVs depending on how many connections are being requested. The following query should capture many / most of the connections that are pooled (please note that it has nothing to do with CONTEXT_INFO):

SELECT sssn.login_time,
       DATEDIFF(MILLISECOND, conn.connect_time, sssn.login_time)
                  AS [MillisecondsBetweenConnectionAndSessionStart],
       conn.*
FROM sys.dm_exec_connections conn
INNER JOIN sys.dm_exec_sessions sssn
        ON sssn.session_id = conn.session_id
WHERE conn.session_id <> conn.most_recent_session_id
OR    DATEDIFF(MILLISECOND, conn.connect_time, sssn.login_time) > 50
ORDER BY conn.connect_time;

This query looks for the following indications of connection pooling being used:

Current session_id is not the same as the prior session_id. If these two IDs are the same then it may or may not be a connection that is using pooling, since the same SPID can be reused. But, if they are different, then that can only be the result of connection pooling.
Time between the connection being made and time that the session starts is over 50 milliseconds (though that threshold might vary by system). Usually the first session to get created upon a connection is less than 30 milliseconds after the connection, but it "generally" shouldn't be above 50, even if executing multiple SqlCommands.

Along similar lines, it should also be possible to test for connection pooling by creating a temp table, and every few seconds, capturing both [session_id] (INT) and [connection_id] (UNIQUEIDENTIFIER) from sys.dm_exec_connections. Then, just look for rows that have the same connection_id but different session_id.

Finally, the query posted in the question is not valid for indicating connection pooling with respect to most web applications. The issue here is that typically, the properties of "program_name", "login_name", and "host_name" would be the same for all connections made by web/app server (hence the need for the per-processor / per-core licensing models instead of just having the CAL model).


Cannot release unused space in SQL Server mdf file I have a huge mdf file (61 GB) and it is 95% empty
I want to release unused space.
I tried to Shrink the file
but when I click OK the Shrink screen closes and nothing happens
the file still 61 GB
How to resolve this?

Relase unused space frees only the space after the last allocated extend in the data file. Since 95% of the space in the data file is not in use you need to perform file reorganization. You can do it from SSMS by choosing Reorganize pages before releasing unused space and setting the suggested Shrink file size to 2490 MB or higher value.
As an alternative you can execute command (I recommend to do it this way):
DBCC SHIRINKFILE ( Res )


Extrapolating gaps in hourly data I have data in the form of

 DateTime -- Status  --   Duration Seconds
 12:00:00,   Available,   10800
 15:00:00,   Unavailable, 460

I need to translate that into

 DateTime -- Status
 12:00:00,   Available
 13:00:00,   Available
 14:00:00,   Available
 15:00:00,   Unavailable

The logic to extrapolate the data this way is simple, but translating it into SQL is proving to be difficult. Any assistance would be greaty appreciated!

Since it looks like you only want a data point every hour, one approach could be to use the durationSeconds column to figure out how many subsequent hour boundaries the data point should cover.  The following query performs this logic and yields your desired results on the test data.

-- Create demo data
CREATE TABLE #status (
    DateTime DATETIME NOT NULL,
    Status VARCHAR(20) NOT NULL,
    DurationSeconds INT NOT NULL
)
GO
INSERT INTO #status (DateTime, Status, DurationSeconds)
VALUES ('2015-07-08 12:00:00', 'Available', 10800),
        ('2015-07-08 15:00:00', 'Unavailable', 460)
GO

-- Extrapolate the gaps!
-- Tally table taken from http://www.sqlservercentral.com/blogs/never_say_never/2010/03/19/tally_2D00_table_2D00_cte/
; WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
        t2 AS (SELECT 1 N FROM t1 x, t1 y),
        t3 AS (SELECT 1 N FROM t2 x, t2 y),
        t4 AS (SELECT 1 N FROM t3 x, t3 y),
        TallyHours AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N FROM t4 x, t4 y)
SELECT sExtrapolated.DateTime, sExtrapolated.Status
FROM #status s
CROSS APPLY (
    -- The original status
    SELECT s.DateTime, s.Status
    UNION ALL
    -- Any extrapolated gaps; if the DurationSeconds pushes past the next hour boundary,
    -- we infer that we should fill in the gap.  If you need slightly different business logic,
    -- you can adjust this part accordingly
    SELECT DATEADD(hh, h.N, s.DateTime), s.Status
    FROM TallyHours h
    WHERE h.N < CEILING(s.DurationSeconds / 3600.)
) sExtrapolated
GO


How do I write this MySQL query? I have a table with a column of numbers that are consecutive but not concurrent. So 91 may be followed by 92 or it may be followed by 96, for example) with intervening numbers not in the column.

I want to search on this column for a particular number and then return the entire row if it is found. If the number is not found I want to return the row with the lower number.

So using the example above if I search for 95, which won't be found, I want to return the row with the number 91 in it.

I presume this must be possible but I just can't work out how to do it.

You can use ORDER BY with LIMIT 1 for this:

SELECT t.*                           -- the columns you want
FROM table_name AS t
WHERE t.column_name <= 95            -- your parameter
ORDER BY column_name DESC
LIMIT 1 ;


Manipulating duplicate records in access I have a table with data as follows:

Field1  Field2 Field3
10      10     20
10      20
10      30
1       0
20      1      1
20      1
20      1
30      2      2
30      2
30      2

My goal is to find duplicate values of field1, and if said field is a duplicate, to average field2's data into field3. For instance Field1's duplicates are 10, thus Field 3's value should return 20.

How would I go about doing this?

To the best of my knowledge, something similar of Java would be written as follows (bear with me my Java is rusty)

{ int field1 = field1;
  scanner.in();
  int field2 = field2;
  scanner.in();

  int sum = sum;

  int count = count;

  int field3 = field3;

  while (field1.hasnext());
  {
  if(field1 = field1)
    {
        sum = sum+field2;
        count++;
    }
else
    {
        out.print("null");
    }
    }

    field3 = sum/count;

    System.out.println(field3);
    }

If you're just trying to query the table to get the average;

 Select AVG(field2) as Field3 from Table where EXISTS (Select Count(field1) from
 table Having Count(field1) >1)

If you want to view all of the other columns in your query results you'll need to use a self join to calculate the average, or a group by clause. You didn't give an example of what you wanted in your results so I can't help much further.


MariaDB ALTER TABLE produced a separate .ibd file, how can I re-integrate the data into the shared table space? We have a multi-GB InnoDB MariaDB (recently switched from MySQL). For some reason, the value of innodb_file_per_table changed to ON (we wanted only one innodb data file explicitely). So when I ran an ALTER TABLE statement on one of the big tables, it created a separate .ibd file which now occupies 35GB even though I had takes measures to make ~100GB space free in the shared tablespace (ibdata1).

How can I re-integrate the data from this file back into the shared tablespace? I thought maybe DISCARD TABLESPACE would do that, but this just deletes the file and the data in it (I tested it with a sample table of course).

While there are many reasons why you may want separate tablespaces, I will assume you are aware of all the disadvantages of having a single shared tablespace, specially in terms of administration (for example, how you can waste filesystem space when creating a temporary table on ALTER).

You probably got innodb_file_per_table changed automatically if you upgraded recently, as on MySQL 5.6 and some versions of MariaDB it is set by default to 1.

To reintegrate the table inside the main tablespace, execute the following on the running server:

SET GLOBAL innodb_file_per_table = 0;

Then recreate the table by doing either:

ALTER TABLE your_table ENGINE=InnoDB;

or

ALTER TABLE your_table ENGINE=InnoDB, ALGORITHM=COPY;

if you are using MariaDB 10/MySQL 5.6.

That will put your table back to the tablespace 0. Remember to change the innodb-file-per-table option on your my.cnf for it to survive a reboot!

Yes, DISCARD TABLESPACE is used for other reasons, mainly importing data with a transportable tablespace.


How does patching work in SQL Server? I need a clarification on how below SQL Server service pack patching works.

Few of my servers are at below patch for SQL Server 2014 which has latest meltdown patch

  12.00.5214    2014.120.5214.6   4057120 Security update for SQL Server 2014 Service Pack 2 GDR: January 16, 2018 – Security Advisory
  ADV180002  CVE-2017-5715 CVE-2017-5753 CVE-2017-5754

and some with below

  12.00.5532    2014.120.5532.0   3194718 MS16-136: Description of the security update for SQL Server 2014 Service Pack 2 CU: November 8,
  2016

2014.120.5532.0 version seems to be on higher build than one 2014.120.5214.6 but that patch seems old when checked as published in OCT 2016.

Do I still need to apply meltdown patch for those servers with build 2014.120.5532.0 or are they covered for meltdown.

Need some better understanding on this.

Ref:

Description of the security update for SQL Server 2014 SP2 GDR: January 16, 2018
Description of the security update for SQL Server 2014 SP2 CU10: January 16, 2018
ADV180002 | Guidance to mitigate speculative execution side-channel
vulnerabilities

On January 16, 2018 ADV180002 | Guidance to mitigate speculative execution side-channel vulnerabilities was released as a standalone patch. Build number 12.00.5214 and file version 2014.120.5214.6. This patch was applicable to servers with service pack 2 and no CU installed. Because there are shops who do not want install cumulative updates but still wants to install the security hotfix.

You can see it here.

One the same day the same patch was released as CU10 for those who wanted all cumulative update since service pack 2. Build number 12.00.5571 and file version    2014.120.5571.0.

You can see it here.

  Do I still need to apply meltdown patch for those servers with build
  2014.120.5532.0 or are they covered for meltdown.

Yes if you want to mitigate mitigate speculative execution side-channel vulnerabilities also know as Meltdown and Spectre.  In that case you will be applying CU10 which will include all updates you from 12.00.5532to 12.00.5571. Latest available update is CU14, build versin 12.00.5600.

As a side not MS-136 was also release with a non-CU and CU version.


OFFSET using a GUID kind of ID I would like use the OFFSET in a SELECT query so I can use pagination in my webapplication. The only problem is I only know the ID of the row from which I would like to start, not the actually OFFSET. An ID looks like this: asdfasfdf894t54yrqewrqew.

So what I do, I SELECT all results from a table and ORDER it by the created_at column and set the LIMIT to a certain value. Now I only know the last ID I get returned, so I would like to start the next page from that ID.

Anyone who knows an easy way to accomplish this using a MySQL query?

Don't use offset - it has to read all the rows to skip them.

If you order rows for pagination by created_at, then thats the column you need to use.

First page:

SELECT ... FROM table
ORDER BY created_at, id
LIMIT N + 1;

The +1 gives you a peek to the next page - you can quickly decide if next page exists and where does it start. If created_at is not guaranteed to be unique, adding the ID to the ordering helps keep it stable.

Next page:

SELECT ... FROM table
WHERE created_at >= <(x+1)th created_at from previous page>
    AND id >= <(x+1)th id from previous page>
ORDER BY created_at, id
LIMIT N + 1;

You use the extra item from fetching the previous page to quickly find the start of current page using the ordering index to get new page.


how can I install Microsoft SQL Server 2005 Express Edition on Windows 8.1 I can't install Microsoft SQL Server 2005 Express Edition on Windows 8.1.

As per Microsoft Official document

SQL Server 2005 (the release version and service packs) and earlier versions of SQL Server are not supported on Windows Server 2012 R2, Windows Server 2012, Windows 8.1, or Windows 8. You will receive a warning in the Action Center if Windows 8.1 or Windows 8 detects an instance of SQL Server 2005.

To resolve this issue, upgrade or remove the existing instance of SQL Server 2005.

Please read below link

http://support.microsoft.com/kb/2681562/en-gb

EDIT:

Click on run the program without getting help. It will proceed. Although installation is not supported it would not block you from installing but you are doing it at your own risk. If you want a supported scenario. You can create a VM on windows 8 machine and can install windows server 2003 SP2 on it and then install SQL Server 2005 with SP4 to run supported scenario.


Calculate sum of N preceding values I am write a SQL query to replicate below Excel result.

In Column A I have 252 values in ASC order from 10/30/2018 to 10/29/2019
In Column BV I have respective PL Number
I want to calculate TestPL column having value as Ignore first 3 PL Values and 4th Cell would be addition of B2:B5, 5th Cell would be B3:B6 and so on for all 251 values

TestPL should start from row 4 and should have sum of cells B2:B5, and row 5 would be  B3:B6 and so on in a stream till row 252 which would B249:B252

Use a window function

...sum(PL) over(order by [Date] range between 3 preceding and current row)


Redo log member and control files at Oracle DB Standard Edition I am planing to dive into the oracle database world. Since the oracle database is such a complex machine, I want to inform myself as good as possible, before I start. I have some questions.

How many redo log members for each group can I define and how many control files can I use by using the standard edition (one)?

I've read, that I can have only two archive log destinations defined by LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST in the standard edition (one). Now I am wondering, if there is any limitation by multiplexing redo log members and control files by using the standard edition (one) ?

I couldn't find this information except, that there can be 8 control files at all.

How many redo log members for each group can I define?

Database Limits

MAXLOGMEMBERS

Default: 2
Maximum Value : 5

  How many control files can I use by using the standard edition (one)?
  I couldn't find this information except, that there can be 8 control
  files at all.

That is correct. CONTROL_FILES

Range of values:1 to 8 filenames

  I've read, that I can have only two archive log destinations defined
  by LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST in the standard
  edition (one).

LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST are deprecated long ago. LOG_ARCHIVE_DEST_N is available since 8.1 in EE, and 10.1 in SE. You can define up to 31 destinations, but only the first 10 can be local. Remote destinations are not supported in SE.


User Rights - Can alter Sp's but not tables How can i make user permissions so that user can CREATE\ALTER\DELETE Store Procedures, but cannot CREATE\ALTER\DELETE tables in database.

I presume your users are creating reports or data extracts.  You can grant permissions to a particular schema, so you could create a separate schema for your users and have the stored procedures reside in that schema.  Configure the security on the rest of the objects so the users only have read permission.

You can also do something similar by creating a separate database and either require explicit reference to the tables or create synonyms within the reporting DB.


Is it "legal" to CREATE and DROP #SomeTable more than once? I've got my code sort of segregated as "coherent blocks" that I can insert into a longer "configuration script" over and over, and one of the patterns I'm using is this:

CREATE TABLE #WidgetSetting
(
    WidgetID bigint not null,
    Name nvarchar(100) not null,
    Value nvarchar(max) not null,
    CreateDate datetime not null
)

INSERT VALUES

MERGE TABLES

DROP TABLE #WidgetSetting

But now SSMS is complaining that the object already exists by the next time the CREATE TABLE fires. What gives?

I think it's obvious I'm going to have to declare the table once at the beginning of the script, truncate instead of drop, but it's frustrating, naturally, to not be able to just drop the table and use the same name again.

No, the parser won't let you create the same #temp table twice in the same batch (and this has nothing to do with SSMS). It doesn't even matter if only one copy of the #temp table could ever be created; for example, in the following conditional logic, which to humans could obviously only ever execute one branch, SQL Server can't see that:

IF 1 = 1
BEGIN
  CREATE TABLE #x(i INT);
  DROP TABLE #x;
END
ELSE
BEGIN
  CREATE TABLE #x(j INT);
  DROP TABLE #x;
END

  Msg 2714, Level 16, State 1, Line 8
  There is already an object named '#x' in the database.

And to prove it's not SSMS complaining at compile time (a common misconception):

DECLARE @sql NVARCHAR(MAX) = N'IF 1 = 1
BEGIN
  CREATE TABLE #x(i INT);
  DROP TABLE #x;
END
ELSE
BEGIN
  CREATE TABLE #x(j INT);
  DROP TABLE #x;
END';

EXEC sp_executesql @sql;

Yields the exact same error, even though SSMS does not try to parse or validate dynamic SQL before sending it to the server via sp_executesql.

The fix, of course, is to re-use the same #temp table instead of dropping, use a different #temp table each time, or don't use #temp tables in the first place.

This is not something you should ever expect SQL Server to handle better. In other words, get used to whatever workaround you decide on.

See also this related answer on Stack Overflow that gives an alternate explanation:

Can I recreate a temp table after dropping it?


SQL Server strategy for high availability with batch drop/create updates Have a production SQL Server 2017 db that is populated nightly using sqoop from another source beyond our control. Would like to fully sync certain tables without bringing down the app (right now we upsert but this is insufficient as there are deletes in the original data source). The quantity of data is fairly small (a dozen tables totaling records in the single digit million rows)

Is there a replication strategy that will allow us achieve this? Maybe something along the lines of having a pair of rotating DBs, where at any point in time one of which is pointed to production and the other is getting batch updated.

First, don't drop the table to do this. You have two better options:

Use the MERGE command
TRUNCATE the table before inserting, don't DROP and CREATE.

The main limitation on the TRUNCATE...INSERT pattern is permissions, since a simple read or update permission grant will not allow for TRUNCATE. ALTER permission on the table is needed to use this command. It also has some other specific limitations, since you can't use TRUNCATE IF:

The table is referenced by a FOREIGN KEY constraint (unless that FK reference is from the same table)
The table that is a participant in an indexed view.
The table is a source for transactional or merge replication.

If none of these conditions are true for the table, and the login performing the task has sufficient permissions, then you are good to go with TRUNCATE...INSERT processing.

MERGE only needs insert, update, and delete permissions. The syntax can be a bit (understatement) confusing until you understand what is happening. However, a quick search on the net will reveal a number of good tutorials for learning the command.

You can also technically write separate DELETE, UPDATE, and INSERT commands for the DUI update. However, this needs to be within an explicit transaction, and is more difficult to maintain than the MERGE, which does not need to be explicitly within a transaction.


What useful information can I get from ASH report which I cannot get from AWR report? What useful information can I get from ASH report which I cannot get from AWR report?
What are the common scenarios in which its better to get information from ASH than from AWR?

AWR(Automatic Workload Repository Reports) takes snapshots of the database in specified intervals(one hour is default) and stores that data for later analysis. For example continuous improvement of the database work.

ASH(Active Session History) has information about current active sessions, and it gather data every second. If you are experiencing sudden database issues, you should use ASH.

More on this subject
http://docs.oracle.com/cd/E11882_01/server.112/e16638/autostat.htm#PFGRF94186


Why does FREEPROCCACHE let you shrink tempdb? Somebody did something stupid and tempdb exploded in size.  Alerts are firing because the drive is full. I tried shrinking it, and it would not shrink (even though shrink queries said they worked).

There is lots of free space in tempdb (SQL2014 SP1); tempdb > Tasks > Shrink > Files > (shows how much available space I can release)

I shrink (SSMS 2016) to leave more space then the 'minimum shrink to size'.  But the files do not actually shrink.

I am working with the business owner, I am watching the server (sp_whoisactive, etc) the server is at rest.

Searching for a solution leads me to When Shrinking Tempdb Just Won’t Shrink(brentozar.com)

I run the following, and it works tempdb, is back to original size.

 DBCC FREEPROCCACHE

 USE [tempdb]
 GO
 DBCC SHRINKFILE (N'tempdev' , 2048)
 GO

I understand that DBCC FREEPROCCACHE releases all the cache data, and impacts plans.  But if it was taking space in tempdb, it should have showed as used space, and not been available for normal shrinking.

Why did this work?

Based on the comments pointing to What is important is that the cached objects are tied to a query plan and that by freeing the procedure cache you can make those objects go away, allowing you to shrink your files. Let me phrase this question from the other way.

Tempdb thinks it has space to shed as evidenced by 'minimum shrink to size', the space that won't go away got created in a temp processes that are no longer running. There is a big chunk of allocated and unused space, why can't I release it without destroying the query plan(s)?

Are the query plans or cache not living on allocated disc space, but still being protected from shrinking?

Analogy: 'minimum shrink to size' shows me the glass is half full/empty but it is not possible to transfer to a smaller glass without breaking the glass and loosing almost all of the contents.

A couple years later, same issue on a SQL 2014 instance, less emergent allows time to try experiment.

If you could somehow identify the query plan, you might be able to delete just that plan releasing the hold on the pages, and then be able to shrink tempdb.  I am looking for ways to accomplish this but have not yet been successful.

Maybe you can use DBCC PAGE to see what is on that page so you can address it?

DBCC TRACEON (3604)  -- By default, the output is sent to the errorlog. If you want the output to come back to your current connection, turn on trace flag 3604.
GO
DBCC PAGE ([tempdb], 1, 1, 3); -- detailed dump of the PFS page
GO

I see the last page is 937, so I go look at it.

DBCC PAGE ([tempdb], 1, 937, 3);
GO

Nothing clearly an issue there, The last few pages look empty.

I have tried the suggestion by Mike Fields (February 4, 2016 12:01 pm) Multiple attempts did not even free 1MB.

  I ran into this problem yesterday, using the SSMS task to shrink files never accomplished anything, then I re-read the documentation on Shrink File and realized I needed to try this:

DBCC SHRINKFILE (N'tempdev' , NOTRUNCATE) -- Move allocated pages from end of file to top of file
DBCC SHRINKFILE (N'tempdev' , 0, TRUNCATEONLY) -- Drop unallocated pages from end of file

  I did this for all 8 of the tempdev files and slowly, over time, it finally recovered about 80% of the space. I was truly surprised by this as nothing else had worked. I had to run a job every 15 minutes through the day but it eventually recovered the space

On a SQL 2014 instance I have 'naturally' cleared the cache when a large database was rebuilt with a clustered column store indexes (nightly process). The same SHRINKFILE that would not do anything the previous day works as expected in the morning.

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 2000)
GO

Maybe if I could recreate the problem on demand on a 2016+ instance with Query Store, it would be easier to track down exactly what is happening?

This answer is based on comments under the question by @MrTCS pointing to this link and by @KrisGruttemeyer with this summary

  TL;DR - "What is important is that the cached objects are tied to a query plan and that by freeing the procedure cache you can make those objects go away, allowing you to shrink your files."

As I understand it:

What is happening when you can’t shrink tempdb, as there is a pointer (or something) physically located on the last page(s) of the datafile.  It points to the query plan that caused tempdb to fill.  That page will not be released as long as that query plan exists.  The nuclear option is DBCC FREEPROCCACHE, which clears all the query plans, and the page is released.


SQL Server: Has anyone used the large-page allocations tuning option? Has anyone used the tuning option of using TF834 large page allocations. I was just reading an MS article on it and was wondering if anybody had used it and seen performance gains. Are there any things to watch out for, tips, pitfalls?

The server is a Windows 2008 64 bit, 128 GB RAM, 4 CPU 8 core hyperthreded (total 64 cores)  SQL2005 server. I am looking to tune the server to better use its specs than just using the default SQL installation that is currently done on it. Any extra tips will be welcomed.

Thomas Kejser from Microsoft (blog) regularly presents that the 834 trace flag is the only go-faster trace flag for SQL Server. He would caveat that, I'm sure, with plenty of testing!


Enable user access to all DBs and tables in MS SQL server managment I create a new user in MS SQL via MS SQL server managment in / SECURITY / LOGINS part.

User is set and can connect , however dispite the fact he see all databases he can get data just from one of them and from rest he can get just structure.

Is there some special settings to enable full access to specifc user to all DBs?
Thanks

Unless the user is given the system adminstrator (sa) role, you will need to add a user to each database which is mapped to the login and which gives the correct permissions just for that database.

Be very careful creating users with global access to databases, it is the start of a slippery slope to a major hack!


Index seek much slower with OR condition compared with separate SELECTs Based on these questions and the answers given:

SQL 2008 Server - performance loss possibly connected with a very large table

Large table with historical data allocates too much of SQL Server 2008 Std. memory - performance loss for other databases

I have a table in a database SupervisionP defined like this:

CREATE TABLE [dbo].[PenData](
    [IDUkazatel] [smallint] NOT NULL,
    [Cas] [datetime2](0) NOT NULL,
    [Hodnota] [real] NULL,
    [HodnotaMax] [real] NULL,
    [HodnotaMin] [real] NULL,
 CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED
(
    [IDUkazatel] ASC,
    [Cas] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[PenData]  WITH NOCHECK ADD  CONSTRAINT [FK_Data_Ukazatel] FOREIGN KEY([IDUkazatel])
REFERENCES [dbo].[Ukazatel] ([IDUkazatel])

ALTER TABLE [dbo].[PenData] CHECK CONSTRAINT [FK_Data_Ukazatel]

It contains cca 211 milion rows.

I run following statement:

DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;

SET @t1 = GETDATE();
SELECT min(cas) from PenData p WHERE IDUkazatel=24
SELECT min(cas) from PenData p WHERE IDUkazatel=25
SET @t2 = GETDATE();
SELECT DATEDIFF(millisecond,@t1,@t2) AS elapsed_ms;

SET @t1 = GETDATE();
SELECT min(cas) from PenData p WHERE IDUkazatel=24 OR IDUkazatel=25
SET @t2 = GETDATE();
SELECT DATEDIFF(millisecond,@t1,@t2) AS elapsed_ms;

The result is shown here:

The third SELECT also loads much more data into SQL Server memory cache.

Why is the third SELECT so much slower (8.5 s) then the first two SELECTs(16 ms)?
How can I improve performance of the third select with OR? I want to run following SQL command but it seems to me that creating cursor and running separate queries is much faster than a single select in this case.

 SELECT MIN(cas) from PenData p WHERE IDUkazatel IN (SELECT IDUkazatel FROM  ...)

EDIT

As David suggested I have hovered over the fat arrow:

For the first two queries all it has to do is scan in the clustered index to the first entry for that value of IDUkazatel - because of the order of the index that row will be the lowest value for cas for that value of IDUkazatel.

In the second query this optimisation is not value and it is probably seeking to the first row for IDUkazatel=24 then scanning down the index until the last row with IDUkazatel=25 to find the minimum value of cas over all those rows.

If you hover over that fat arrow you'll see it is reading many rows (certainly all those for 24, probably all those for 25 too), whereas the thin arrows in the plan output for the other two show the top action causing it to only consider one row.

You could try run each query and then get the minimum for the minimums found:

SELECT MIN(cas)
FROM   (
        SELECT cas=MIN(cas) FROM PenData p WHERE p.IDUkazatel = 24
        UNION ALL
        SELECT cas=MIN(cas) FROM PenData p WHERE p.IDUkazatel = 25
    ) AS minimums

That said, it seems you have a table with IDUkazatel values rather than an explicit OR clause. The code below will work with that arrangement, simply replace the table name @T with the name of the table containing IDUkazatel values:

SELECT
    MinCas = MIN(CA.PartialMinimum)
FROM @T AS T
CROSS APPLY
(
    SELECT
        PartialMinimum = MIN(PD.Cas)
    FROM dbo.PenData AS PD
    WHERE
        PD.IDUkazatel = T.IDUkazatel
) AS CA;

In an ideal world, the SQL Server query optimizer would perform this rewrite for you, but it does not always consider this option today.


Tool to match mysql password encryption I've created a user ... but forgotten the password

mysql> create user 'blayo'@'%' identified by 'right';

Which Linux command line tool can encrypt the password the same way mysql does ?

mysql> select Password,User from mysql.user
------------------------------------------+-------+
*920018161824B14A1067A69626595E68CB8284CB | blayo |

...to be sure I use the right one

$ tool right
*920018161824B14A1067A69626595E68CB8284CB

Just use MySQL:

select password('foo');

PASSWORD('FOO')

*F3A2A51A9B0F2BE2468926B4132313728C250DBF


DB alert on values change How to make notification when some conditions written by user are true.

I have one table DATA which has 3 rows.

name  value
----  -----
a1    10
a2    20
a3    30

This table changes some values every 1-10 minutes.

Example: a1=12 , a2=21

ALERT:
if a1>11 and a2>20 send me some message.

Question: How to check when a value in DATA fulfils the conditions in alert, and then my php/python environment takes any action on this alarm?

My idea:

After update on DATA table run trigger that will check if ALERT exist and set active on it (how to check when DATA id a1=12 and then check if there is some alert like a1>11)
Run every 1 minute process that checks if ALERT is active and execute it.

I don't now where to start...

I will use: PostgreSQL, php, cron

You have multiple choices here.

writing a crontab that will check every minutes your data (that's what you propose).
using NOTIFY/LISTEN with trigger like this :

CREATE OR REPLACE FUNCTION f_check_data_value()
  RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  SELECT pg_notify('data', NEW.value);
END;
$$;

CREATE TRIGGER check_data_values_trigger
AFTER INSERT OR UPDATE
  ON mytable
FOR EACH ROW EXECUTE PROCEDURE f_check_data_value();

You'll then have to write a listener (in PHP if it's what you are using). I haven't used php inn a while but the php.net documentation is a good place to start I think : http://php.net/manual/fr/function.pg-get-notify.php


Converting an SVN repository to SQL database I have downloaded the svn repository of Atunes using TortoiseSVN. I would like to transfer the logs of activity of the developers to a SQL database, preferably an Oracle one. Is there any software that can help me do this?

The command line SVN client can export the log to an XML file with the --xml option.

http://svnbook.red-bean.com/en/1.5/svn.ref.svn.c.log.html


Why does a count obtain an ExclusiveLock in Postgresql? And what does this mean for other CRUD operations going against that table?

I have this query (actually I'm trying to write it 9 ways from Sunday to get it to return in less than an hour):

SELECT COUNT(*)
FROM (SELECT DISTINCT listing_id
      FROM apps where subscription_id = 1298) AS temp

It churns and churns. When I look at a process list for the server, I find eight records. One for the table and six for the indexes. And apparently one for the statement itself.

It says Locked Object and the object name, and AccessShareLock next to the table and indexes. Then there is something that says LockedTransaction and next to it it says ExclusiveLock.

What does this ExclusiveLock mean, and will it keep other CRUD operations from running against that table?

Every transaction is born owning an ExclusiveLock on itself.

For a read-only transaction, this will only block things like CREATE INDEX CONCURRENTLY.  For DML transactions, this lock will be used to block other transactions trying to update or delete the same row, or insert conflicting rows.


:!!echo command not working from sqlcmd I have a sql script file which is executed from a batch file and is supposed to execute some sql scripts and write errors and output to files. Here is an example of how the script file looks like:

:error ".\DataAndTableScripts\9.0.0\_RunOrderedScripts.err"
:out ".\DataAndTableScripts\9.0.0\_RunOrderedScripts.out"
:on error ignore
go
set nocount on;
print 'Running .\DataAndTableScripts\9.0.0\OrderedFileList.txt'
print 'Start time: ' + convert( varchar, getdate(), 121 );
GO
:!!echo   Table_auth.IdentityApplication.sql
:error ".\DataAndTableScripts\9.0.0\Table_auth.IdentityApplication.err"
:out ".\DataAndTableScripts\9.0.0\Table_auth.IdentityApplication.out"
:r ".\DataAndTableScripts\9.0.0\Table_auth.IdentityApplication.sql"
go

The :!!echo  Table_auth.IdentityApplication.sql piece is suppose to print this text in the command prompt window. I initially had this code on SQL 2014, but when I upgraded to SQL 2016 and to command line utilities 13 this echo piece stopped working. It seems like, even when I have :on error ignore, when it gets to :!!echo, if fails and the rest of the script is not executed.

Anyone has an idea of what the issue may be?

SQLCMD

Next versions do NOT work:

Version 14.0.2027.2 NT (from: COMMAND LINE UTILITIES FOR MICROSOFT SQL SERVER 2017)
Version 15.0.1300.359 NT (from: SQL Server 2019 (15.x))

This version works:

Version 11.0.2100.60 NT x64 (from: Microsoft SQL Server 2012 Command Line Utilities)

(on my computer found in 'C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE')

This was tested by creating a file sqlcmd.txt which contains 1 line (:!! dir), and checking if this command was executed when performing sqlcmd -i sqlcmd.txt.

I think the version 11 that works can be downloaded here, but do NOT ask why this is named 'Command Line Utilities 13 for SQL Server'), and from a logfile i created during install is installen ina folder named 110....


Combine SQL query with variable Redshift support export data to CSV by executing this SQL query.

unload ('select * from bhuvi')
to 's3://bhuvi-bucket/folder//user-data.csv'
iam_role 'arn:aws:iam::1111111:role/myredshiftrole'
delimiter '|'  GZIP";

I have different tables and need to export them to different locations. So
I have a stored procedure like below. so I can pass the locations in a variable in the future.

CREATE OR REPLACE PROCEDURE test_variable()
 LANGUAGE plpgsql AS
$$
DECLARE
   sql text;
   s3_loc text;
   role text;
   deli text;
   query text;
BEGIN
   sql:='select * from bhuvi';
   s3_loc:='s3://bhuvi-bucket/folder/';
   role:='arn:aws:iam::1111111:role/myredshiftrole';
   deli:='|';
   query := "unload (sql) to 's3_loc/user-data.csv' iam_role 'role';
   execute query;
END
$$;

but somewhere the syntax is wrong. Im not able to find that. When I call this procedure, im getting this below error.

test=# call test_variable();
ERROR:  column "unload (sql) to 's3_loc/user-data.csv' iam_role 'role' " does not exist
CONTEXT:  SQL statement "SELECT  "unload (sql) to 's3_loc/user-data.csv' iam_role 'role' ""
PL/pgSQL function "test_variable" line 12 at assignment
test=#

You can't use double quotes for string literals. Do include a single quote in a string literal, it has to be escaped by doubling them:

query := 'unload (sql) to ''s3_loc/user-data.csv'' iam_role ''role''';

Additionally can't use names of variables from PL/pgSQL in a SQL string. You need to include the content of that variable in the query string you build:

query := 'unload ('||sql||') to '''||s3_loc||'/user-data.csv'' iam_role '''||role||'''';


How to fix error recording sp_whoisactive results to table? Have a sql agent job modeled from here that uses sp_whoisactive to capture results to a table. 99% it works fine but every now and then the sql agent job will fail with this error:
Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153)  Violation of PRIMARY KEY constraint 'PK__#ADDF8B9__69B13FDC8C10EA7F'. Cannot insert duplicate key in object 'dbo.@blockers'. The duplicate key value is (623). [SQLSTATE 23000] (Error 2627)  The statement has been terminated. [SQLSTATE 01000] (Error 3621)  Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153).  The step failed.
Here is table def
USE [Maint]
GO

/****** Object:  Table [dbo].[WhoIsActive]    ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[WhoIsActive](
    [dd hh:mm:ss.mss] [varchar](8000) NULL,
    [session_id] [smallint] NOT NULL,
    [sql_text] [xml] NULL,
    [sql_command] [xml] NULL,
    [login_name] [nvarchar](128) NOT NULL,
    [wait_info] [nvarchar](4000) NULL,
    [tran_log_writes] [nvarchar](4000) NULL,
    [CPU] [varchar](30) NULL,
    [tempdb_allocations] [varchar](30) NULL,
    [tempdb_current] [varchar](30) NULL,
    [blocking_session_id] [smallint] NULL,
    [reads] [varchar](30) NULL,
    [writes] [varchar](30) NULL,
    [physical_reads] [varchar](30) NULL,
    [query_plan] [xml] NULL,
    [used_memory] [varchar](30) NULL,
    [status] [varchar](30) NOT NULL,
    [tran_start_time] [datetime] NULL,
    [open_tran_count] [varchar](30) NULL,
    [percent_complete] [varchar](30) NULL,
    [host_name] [nvarchar](128) NULL,
    [database_name] [nvarchar](128) NULL,
    [program_name] [nvarchar](128) NULL,
    [start_time] [datetime] NOT NULL,
    [login_time] [datetime] NULL,
    [request_id] [int] NULL,
    [collection_time] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

What is causing this error?
sp_whoisactive version = v11.11.

You will have to download a newer version of Adam Machanic's sp_whoisactive stored procedure.
Preferably 11.16 or newer:

Version 11.16 - October 18, 2016 (Box versions 2005-2017 only. NOT for Azure PAAS.)

Fixed algorithm for identifying "special" database pages (e.g. GAM, SGAM). Thanks, Robert Davis!
Fixed @blockers duplicate key bug

In the code the definition of the @blockers table variable was changed to:
...
DECLARE @blockers TABLE
(
session_id INT NOT NULL PRIMARY KEY WITH (IGNORE_DUP_KEY = ON) /*<<== HERE!*/
);
...

And then again, you could modify your existing code.


ORDER BY clause is allowed over column that is not in SELECT list? In theory, every statement in SQL Server create virtual table passed to next statement. In other words, when FROM and Where clause are finished, next step is SELECt columns from virtual group created step before.

But, it is possible to order result set by column that is not listed in select list.

How is it possible, when SELECT clause are executed before ORDER BY and which primary task is to select columns for rendering?

It is a little confusing or I don't get something.

It's not always possible:

  If DISTINCT is specified, the expression in the ORDER BY clause have access only to the virtual table returned by the previous step (VT5)

(VT5 is virtual table returned generated by SELECT)

  If DISTINCT is not specified expressions in the ORDER BY clause can access both the input and the output virtual tables of the SELECT phase.

Explanation is taken from "Inside SQLServer 2008 T-SQL Querying" - I'm not sure if this particular chapter is available online (Step 6: The Presentation ORDER BY Phase, p.16.)

Thus,

SELECT distinct field2 FROM table1 order by field1

gives an error:

  Msg 145, Level 15, State 1, Line 1
  ORDER BY items must appear in the select list if SELECT DISTINCT is specified.