SQL agent job step's connection managers Is there any way with T-SQL to list the connection managers that are used in an SQL Agent Job step?

Given that SSIS tasks are XML files, I would have thought that it would be possible to write a T-SQL XML query to find the connection objects.

You would need to search the XML for instances of

<DTS:ConnectionManager>

Here's a sample from a project I have:

<DTS:ConnectionManager>
  <DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
  <DTS:Property DTS:Name="ObjectName">SQLOverview</DTS:Property>
  <DTS:Property DTS:Name="DTSID">{1e0a262f-476a-49d8-873b-acf7e3d7d46e}</DTS:Property>
  <DTS:Property DTS:Name="Description">
</DTS:Property>
  <DTS:Property DTS:Name="CreationName">OLEDB</DTS:Property>
  <DTS:ObjectData>
    <DTS:ConnectionManager>
      <DTS:Property DTS:Name="Retain">0</DTS:Property>
      <DTS:Property DTS:Name="ConnectionString">Data Source=MyPC\SQL2008R2Dev;Initial Catalog=SQLOverview;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SQLOverview;</DTS:Property>
    </DTS:ConnectionManager>
  </DTS:ObjectData>
</DTS:ConnectionManager>

A couple of things to note:

The top node is <DTS:Executable>.  These <DTS:ConnectionManager> items are the next level down.
The above snip shows a static / fixed connection.  If you use an expression / dynamic connection, the XML code is a bit different:

  0
  MultiServer
  {02150dee-336e-4750-9159-3c4d3f0c62c3}

  OLEDB
  @[User::DynamicServerName]

      0
      Data Source=MyPC\SQL2008R2;Initial Catalog=master;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SQLOverview;

Please also note that all the code here has been formatted using an on-line XML formatter to aid readability.  In reality, each of these ConnectionManager XML snippets is on a single line of the .DTSX file.

There is no way to do this with T-SQL; this is specific to SSIS.


Static archive database: SQL Server can't shrink it and leaves ~400G of unallocated space We are having an issue with our databases. Here's what happens:

All databases have around 50% of unallocated space and DBCC SHRINKDATABASE/SHRINKFILE won't reclaim the space back. These are archive databases which have been recently cleaned up and are supposed to be read-only, so performance is not an issue (yes, I'm fully aware of shrink and clustered indexes fragmentation and I couldn't care less). The main objective here is to have all datafiles reduced to save $$ on storage space. Again, I don't care about performance, these data files must be reduced at all costs! I'm talking about databases at 800GB, 1,5TB and 2,1TB. So nothing ordinary or small.

So:

shrink database shows ~50% of wasted space
a post-shrink database with mandatory page reorganization (NOTRUNCATE) and mandatory 1% free space keeps showing with 50%
unallocated space
so an 800 GB database, appears in sp_spaceused as having ~410 GB of unallocated space, even after the shrink
a shrink file acknowledged the database has only 1% of free space
when we started using SELECT INTO to migrate tables to another database, we started noticing that the original tables were occupying hundreds of thousands of pages, but after migration they were taking no more than a few hundreds of pages
DBCC also shows that the original database (before SELECT INTO) uses an huge amount of extents

My analysis of this is the following:

shrink is not freeing unallocated space
SELECT INTO yes frees up a lot of space

What I would like some help with:

why shrink isn't working
why SELECT INTO works
why the original database has so much wasted space (yes, it had auto-growth enabled for a long time before becoming an archive)

We're already looking at things like fill-factor, page and extents usage, but what we would like to have is at least an explanation for so many lost space and how to reclaim it without SELECT-INTO all terabytes of data. Any help would be appreciated.

There's 2 potential things at play here.  Shrink's algo takes the last page and moves it to the first empty spot, this creates massive fragmentation and could also create forwarding records.  The better idea is to rebuild your database on a different filegroup using a clustered index that's already defined pre data population.

The other item could be a ghost clean up bug that was around in 2008.  I don't know what version you're running, but if it is 2008, ensure you are fully up to date on patches.

https://codingsight.com/moving-existing-table-from-primary-filegroup-to-different-filegroup/

Here you have 2 ways...
1) Check the database file and see if you have empty space to release.. If you have it you can run the shrinkfile to release space (I'll recommend you to shrink in chunks of 1MB)
2) You can run a report that shows you the disk usage by tables on the DB.. If you see that there is a big unused space then you need to see the fill factor per index or if they are heaps.. If that is the case then you need first to rebuild those indexes / heaps and then release the space.

hope this helps!


Maybe try

SELECT * FROM tablename
 WHERE date_ BETWEEN '2017-07-10 8:19:00' AND '2017-07-10 8:19:59'


Table Lock on INSERT - How to optimize we have a few long running procedures in the database, that cause an instant Table Lock on our main text storage table, which causes all other requests of the web app to wait (because the table is used in nearly every request).

Setup

Web Application with ASP.NET, Entity Framework, SQL Server 2008R2
A lot of Tables (Table per Class Strategie) with data attributes
ONE Table ("dbo.Translations") for dynamic defined texts (to enable translations).
No foreign keys to this table

To get the text of the object in the right language, the Object ID is saved to the translation table with the text value

Table Definition

So the Translation Table looks like:

  dbo.Translations (ID varchar(36), type varchar(10), lang varchar(5), value varchar(max))

whereas Type is defined for each object and lang is like 'en-GB' (not relevant).

Index

The table has two indexes:
PK_Translations on ID (Clustered, not unique)
UNQ_Translations on all columns (nonclustered, unique)

Problem

The table has to handle two operations:
SELECTS and INSERTS.

Update and delete is not a common use-case.

The table holds nearly 1.000.000 rows and an index over ID,type,lang.

When the web application wants to insert (multiple) records, the operation takes a lot of time and creates an instant table lock. This stops every other request to this table (maybe even to other tables, so sometimes it seems to be a database lock?!) and causes them to wait.

Inserts

The INSERTS of large data is done in the following way:

INSERT INTO Translations
   (Id, Typ, lang, value)
SELECT
   td.nId,
   td.Typ,
   td.lang,
   td.value
   FROM #tempDictionary td

Note: #tempDictionairy is a temporary table

Steps

The following steps where tested and did not improve the situation:

Start the SELECT transaction with read uncommitted -> no change
Extend the index sparsity to 50% to ensure that the inserted records can be saved easily -> no change

How can I find out why the SQL Server creates an table / db lock?

Are you 100% sure that this process is escalating to a tablock? You can verify this with Adam Machanic's sp_whoisactive. Take a snapshot (execute the proc) while the process is running (and presumably blocking). Collect the locks and the query plan - this will give you information needed to fix this issue. Please note what the spids waiting in the insert are waiting on...If they are waiting on latches -we have a page split situation locks its a blocking/locking issue
Remember lock escalation happens when a single statement graps 5K locks on an table or index. (http://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx) I have found that using the paglock hint (so you take fewer coarser locks) can be used to avoid tablock escalation.
Take a look at the query plan collected in step 1. What is the cardinality estimate of the row being insert (i.e. the select statement)... Is it much greater than 5K? If it is you might want to code a loop - so that you are only inserting 2500 rows at a time...

It look like you are also inserting you primary keys (i.e. clustered index):

  INSERT INTO Translations
    (Id, Typ, lang, value)

it means the whole table is getting restructured - SQL Server has to physically rearrange data according to the new Ids you are inserting. It can possible lock a couple of pages.

However, it would be nice to see your select statement. I am sure you are selecting by Transaction Id.

Solution:
Add another Identity column - integers - increasing and make it primary key (clustered index). Make TransactionID as a non-clustered index. As a result inserting new TransactionIDs will not block reading old TransactionIDs (or will block with a less effect which you can negate using snapshot isolation level)

Do not use Read Uncommitted as it can lead to a dirty reads/phantom reads and non repeatable reads problems.


64bit SQL Server with total virtual address space of 4GB. How could this be? My SQL Server 2012 Standard edition, 64bit has a total virtual address space of 4GB. Why is that?

it is a 64bit installation: print @@VERSION :

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (Intel X86)
    Dec 28 2012 19:06:41
    Copyright (c) Microsoft Corporation
    Standard Edition on Windows NT 6.2 <X64> (Build 9200: ) (WOW64)

virtual space of just 4GB

select total_virtual_address_space_kb from sys.dm_os_process_memory
4194176

(entire result of sys.dm_os_process_memory
http://screencast.com/t/2PN2PDQYpt )

The reason is because it isn't a 64-bit installation.

  Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (Intel X86)

Intel X86 is 32-bit. X64 is 64-bit.

Simple mistake of installing the 32-bit version instead of the 64-bit version.

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (Intel X86)
Dec 28 2012 19:06:41
Copyright (c) Microsoft Corporation
Standard Edition on Windows NT 6.2 <X64> (Build 9200: ) (WOW64)

This means 32-bit version, which has the stated memory limit. You should perform backups and reinstall SQL Server using the correct 64-bit version.


What are the risks of the parameter PRESERVE_COMMIT_ORDER of Oracle's DBMS_LOGSTDBY? We have the following setting, with Oracle 10g:

One primary database
One logical standby database with setup with data guard. The logical standby is only used for generating reports and alike.

Currently the parameter PRESERVE_COMMIT_ORDER of DBMS_LOGSTDBY is set to true. In order to enhance the speed of data replication, we are thinking of setting it to false. But what are the risks, if any, of doing so? (Oracle says: "See the Usage Notes for details and recommendations." ... but I couldn't find these recommendations.)

I found the following white paper: SQL Apply Best Practices: Oracle Data Guard 11g Release 1 that brings a lot of enlightement.
On page 12 is says

If you are using the SQL Apply database only for disaster-recovery purposes or if the reporting application using the standby database can accommodate transactions being applied out of sequence, then set the PRESERVE_COMMIT_ORDER parameter to FALSE. When SQL Apply is running in this model, transactions that are unrelated to each other may be applied out of sequence.

there are some examples that illustrates the effects (logical and performance related) of setting this parameter to false.

Most reporting application are reporting about data that was processed some time ago (yesterday, the last month) it seems to be no problem to et this parameter to true

The most performant way to run a standby is to run it as a physical standby with the limitation that the database is a read only 1:1 copy of the primary database.

The risk is severe. The risk is that the standby database would hold different data than primary database. Both would be self-consistent, but may be different.

This parameter means to it is not necessarily for the multiple SQL Apply sessions to commit in the same order. Consider this example:

TransactionA: UPDATE employee SET salary=1000;
TransactionA: COMMIT;
TransactionB: UPDATE employee SET salary=1001;
TransactionB: COMMIT;
SELECT salary FROM employee;

I think the order of commits is relevant.


How do I rule out SQL Server as the reason for a slow website A website we host has recently been experiencing slow load times. It's been ruled out that the problem lies with the web server and it maybe database related.

What steps or procedures do I take to confirm or rule out that SQL Server is the reason for the slow load times? Also, without a baseline, what may be behaviours/characteristics of SQL Server to look out for that could indicate that there is a problem?

I'd suggest you need a more definitive problem statement - is it all web pages? If it's just some, what are those web pages doing? What is the acceptable load time, and what is the current load time? Is it all users, or just some?

I used to be a web admin, and my first check would have been the webserver - is memory and CPU ok? Do the OS and web logs show anything? Can the scripts output a log to show what's going on? eg I used to put the database stuff in one section, and write times/durations for each database activity to a log, when activated.

If you need to check the SQL Server, check the SQL & NT logs, and again check the memory, disc IO, and CPU - task manager will give you an ideal of two of these (not disc IO).  Ideally, SQL Server should be on a dedicated box rather than shared with anything.

Whilst someone is using the website, look at either Activity Monitor or sp_who2 - are you able to see the query dawdling? Is there any blocking? Keep refreshing... if the query is runnable/suspended/running for a while, then you need to find why it is slow. If you can't see a query in those states, it may not be SQL Server.

All server configurations and web services are different. The best way to go about this is not so much seeing if your SQL Server is slowing down your web site, but recognise that it is always best practice to make sure your SQL is written in a well optimised way, use stored procedures for everything and avoid pass-through queries. Pass-through queries have been legacy code since the early 90s and are no where near as efficient as stored procedures.

Additionally if you've written cross tab queries especially against large tables consider repeating data in order to avoid using cross tab queries (queries nested in joins or double joins). If you have a lot of cross tab queries and data repetition in extra fields is not an option, check that your cross tab queries are returning the minimum amount of rows. Don’t be tempted to filter cross tabs through the DISTINCT/DISTINCTROW filter. Also avoid using triggers as this opens up a DML layer. The extra DML layer in most database engines can easily cut the DB engine efficiency in half. The best way to optimize cross tabs are to play around with the join fields until you get the minimum amount of rows that reflect the amount of rows in the left most table (for a left join) or the right most table (in a right join).

Also do you best to avoid many to many queries. Many to many queries are only really used for reports or integrating with third party data sources that the system was not originally designed to integrate with.

Apart from these exceptions If a database is normalised properly you should never see many to many joins.

Though I respect this doesn’t directly answer your question if you have taken this advice in this post then, the problem is really that you need a more powerful web service, perhaps a cloud server.

Hope this advice helps.


How to select datas for a particular user between a date range I am querying a table with where condition in which i want to choose between a particular date range at present my query is like this with a where condition.

WHERE entered_by = '1' AND date_created = '2018-08-23'

I want to use a particular date range from to to how can i do this?

Don't use functions on indexed columns.

Don't put dates in VARCHAR; use DATE, DATETIME, TIMESTAMP, etc.  MySQL does not understand tz info, as in 2018-07-16T09:48:30+00:00; deal with it before storing into the date column.

Do test ranges this way:

WHERE date_created >= '2018-08-23'
  AND date_created  < '2018-08-23' + INTERVAL 7 DAY

to test for a week.  Note that you don't need to worry about leap years, etc.  For a single day, do use

WHERE date_created >= '2018-08-23'
  AND date_created  < '2018-08-23' + INTERVAL 1 DAY

Do not use this (or CAST):

WHERE DATE(date_created) = '2018-08-23'    -- inefficient

because no index can be used.

So, if your query is

WHERE entered_by = '1' AND date_created = '2018-08-23'  -- original

change it to

WHERE entered_by = '1'
  AND date_created >= '2018-08-23'
  AND date_created  < '2018-08-23' + INTERVAL 1 DAY

Note further:  This will work regardless of whether date_created is DATE, DATATIME, or even DATETIME(6).  And it avoids the clumsy 23:59:59 (which won't be adequate for DATETIME(6)).

Also, have this "composite" index:

INDEX(entered_by, date_created)   -- in this order

A further note:  BETWEEN leads to the "midnight bug", wherein you accidentally include an extra midnight.  Note that my code has >= and <, not <=.

The fiddle shows that candidate.date_created data type is VARCHAR:

`date_created` varchar(250) COLLATE utf8_unicode_ci DEFAULT ''

If so it must be explicity converted to a DATE data type when we use date literals in a compare operator and want to have a date type compare. Like:

WHERE CAST(date_created AS DATE) BETWEEN '$from_date' AND '$to_date'

Using CAST in the first operand forces the server to use DATE data type context. So all other operands will be converted to this datatype before compare. And the result will be one we need.


Should DBAs also be acting as Security Administrators? One standard I have been tasked to bring my databases up to asks me to separate security functionality and non-security functionality. Based on supplemental reading, this seems to be talking about hardware, software, and firmware dealing with encryption, authentication, authorizations and auditing.

Pursuant to this goal, and just as a general question to you experienced people, is it acceptable for a DBA to also be acting as a Security Administrator? Separation of duties would seem to say no, as DBA work, while administrative and highly privileged in nature, is mostly not security-related in nature. But if it would be prohibitive to hire someone as a DBA and another as a security administrator, what would be the largest areas of concern, if any, if one person performed both of these sets of duties?

ETA: I can't give many details to the best of my understanding of my restrictions, but security concerns are high, budget is tight, and the databases range from small, non-application databases with few users to users in the thousands with an expansive number of tables, each with an expansive number of rows of information. DBAs are utilizing the DBA role, so the most expansive set of privileges. And it looks like I accidentally removed the oracle tag, so I'll put that back on and mention that this is an Oracle database.

For the sake of discussion, let's focus on the largest subset of databases. These have personal and financial information in them. I think I can say that we are shooting for a standard that heavily leans on the NIST database standard, but isn't NIST for whatever reason. Not sure if that helps at all.

I have functioned as all the roles in the past. Small place, one server, 3 databases and I was the only IT. So I did it all; DBA, cabling, hardware, software, programming. It should be separated out, but if you don't have the manpower or the money, then combined roles is the only way to cover them all.

Warning tremendously unpopular red-pill opinion. The truth hurts.

If someone comes up to me and gives me such a title, I am going to grill them uncomfortably. My first kind of question is what kind of threat are you securing your users against, and on what skill sets and abilities do you think you're qualified to do that task?  I agree with @Wes, that everyone should "think as security", but for the term to have any meaning it should not apply to everyone. It sounds kind of pretentious to start with but when I think of people using those terms, you better be at Defcon and employed with Mitnic. You should not be substantiating that claim with the CompTIA de jour. What was the last book, tailored to the topic of security, that you read?

I think most IT roles are highly oriented to the merits of the target user. If you can't build it, you can't secure it. That leads me to dismiss Microsoft DBA's in this capacity and end-users of proprietary software. How are you going to claim any level of security expertise outside of the domain of creation? Do you have skills reverse engineering proprietary code? To state it another way, Microsoft may have guidelines that they don't currently follow in their point-and-click install of SQL Server. In so far as the end-user can follow those suggestions, that's some degree of security. But let's be honest here: it's not much.


Selecting records where date range is current year minus #years I want to select and eventually delete records from a point in time earlier than current year minus "x" years.

I'm unsure of the most efficient way to do this.

This will delete anything three years older than January 1st of this year.

DELETE table_name
WHERE change_date < ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), -36);

You'll want to review the DATEADD() function. So, a simple version is like this:

SELECT *
FROM YourData
WHERE DateField < DATEADD(yy, -1, GETDATE());

or

SELECT *
FROM YourData
WHERE DateField BETWEEN DATEADD(yy,-5, GETDATE()) and DATEADD(yy, -1, GETDATE());

So, this would give you data between 2010 and 2014. (yes you can add negative years)


How to remove earliest/latest row by two columns (userId, StatusCheck)? I have crosschex database that is used to store logged in/out data. Sometimes it happens that user clock in/out few times. I would like to remove that duplicates leaving the earliest when log in and latest when log out.
Here is what I have:

USE crosschex;
SELECT UI.Name, INOUT.UserId, INOUT.CheckTime, S.StatusText
FROM dbo.Checkinout AS INOUT
INNER JOIN dbo.Userinfo AS UI ON
INOUT.Userid = UI.Userid
INNER JOIN dbo.Status AS S ON INOUT.CheckType = S.Statusid
WHERE
    UI.Groupid = 1 AND INOUT.CheckTime >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE())-3, 0) AND INOUT.CheckTime < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE())-3, 1)

Here is the result:

"Zeab Simon"    "6" "2018-05-04 07:58:06.000"   "In"
"Zeab Simon"    "6" "2018-05-04 07:58:15.000"   "In"
"Zeab Simon"    "6" "2018-05-04 07:58:38.000"   "In"
"Kate B"    "4" "2018-05-04 14:15:45.000"   "In"
"Kate B"    "4" "2018-05-04 14:15:46.000"   "In"
"Zeab Simon"    "6" "2018-05-04 16:34:46.000"   "Out"
"Zeab Simon"    "6" "2018-05-04 16:34:48.000"   "Out"
"Zeab Simon"    "6" "2018-05-04 16:34:49.000"   "Out"
"Zeab Simon"    "6" "2018-05-04 16:34:50.000"   "Out"
"Zeab Simon"    "6" "2018-05-04 16:34:51.000"   "Out"
"Patty" "5" "2018-05-04 20:31:36.000"   "Out"

What I would like is to remove the earliest and lattest for each userId and StatusText In/Oou to get that:

"Zeab Simon"    "6" "2018-05-04 07:58:06.000"   "In"
"Kate B"    "4" "2018-05-04 14:15:45.000"   "In"
"Zeab Simon"    "6" "2018-05-04 16:34:51.000"   "Out"
"Patty" "5" "2018-05-04 20:31:36.000"   "Out"

How can I do it?

To answer this question, I did the following.

CREATE TABLE shift (worker_name VARCHAR(25), worker_id INTEGER, shift_time TIMESTAMP, in_out VARCHAR(3));

INSERT INTO shift VALUES ('Zeab Simon', 6, '2018-05-04 07:58:06.000', 'In');
INSERT INTO shift VALUES ('Zeab Simon', 6, '2018-05-04 07:58:15.000', 'In');
INSERT INTO shift VALUES ('Zeab Simon', 6, '2018-05-04 07:58:38.000', 'In');
INSERT INTO shift VALUES ('Kate B',     4, '2018-05-04 14:15:45.000', 'In');
INSERT INTO shift VALUES ('Kate B',     4, '2018-05-04 14:15:46.000', 'In');
INSERT INTO shift VALUES ('Zeab Simon', 6, '2018-05-04 16:34:46.000', 'Out');
INSERT INTO shift VALUES ('Zeab Simon', 6, '2018-05-04 16:34:48.000', 'Out');
INSERT INTO shift VALUES ('Zeab Simon', 6, '2018-05-04 16:34:49.000', 'Out');
INSERT INTO shift VALUES ('Zeab Simon', 6, '2018-05-04 16:34:50.000', 'Out');
INSERT INTO shift VALUES ('Zeab Simon', 6, '2018-05-04 16:34:51.000', 'Out');
INSERT INTO shift VALUES ('Patty',      5, '2018-05-04 20:31:36.000', 'Out');

I then ran the following query:

SELECT worker_name, MIN(shift_time), in_out
FROM shift
WHERE in_out = 'In'
GROUP BY worker_name, in_out
UNION
SELECT worker_name, MAX(shift_time), in_out
FROM shift
WHERE in_out = 'Out'
GROUP BY worker_name, in_out
ORDER BY worker_name

Which gives:

worker_name,         shift_time,   in_out
Kate B       2018-05-04 14:15:45       In
Patty        2018-05-04 20:31:36      Out
Zeab Simon   2018-05-04 07:58:06       In
Zeab Simon   2018-05-04 16:34:51      Out

A cursory inspection reveals that these are the correct times - earliest clock_in and latest clock_out. A db-fiddle is available here. To get the best answers to your questions, you might consider giving a fiddle in the question itself - help  us to help you!

Following the OP's clarifications in comments the need to delete extraneous records from the same table, I used the result above in a CTE (Common Table Expression - AKA the WITH clause) to delete the other records from the main shift table as follows.

WITH cte AS
(
  SELECT worker_name, worker_id,
  MIN(shift_time) as shift_time, in_out
  FROM shift
  WHERE in_out = 'In'
  GROUP BY worker_name, worker_id, in_out
  UNION
  SELECT worker_name, worker_id,
  MAX(shift_time) as shift_time, in_out
  FROM shift WHERE in_out = 'Out' GROUP
  BY worker_name, worker_id, in_out
)
-- select * from cte  -- testing
DELETE FROM shift
WHERE shift_time NOT IN
(
  SELECT shift_time FROM cte
);

And then a simple SELECT in the fiddle shows that the correct result has been achieved.

Another solution was kindly provided by @ypercube here - worth looking at for learning purposes. It is actually (no surprise here) more correct. My SQL will not work properly if there are exactly matching times for a MAX(or MIN) and a non significant value!

You could number the rows using an specific order and delete those rows where row number > 1.

Let me suggest to add a unique or primary key to the query just avoid using:

ON   X.worker_id = s.worker_id
AND  X.shift_time = s.shift_time
AND  X.in_out = s.in_out

WITH X AS
(
    SELECT   *,
             ROW_NUMBER() OVER (PARTITION BY worker_id, in_out ORDER BY worker_id, shift_time) rn
    FROM     shift
)
DELETE s
FROM shift s
JOIN X
ON   X.worker_id = s.worker_id
AND  X.shift_time = s.shift_time
AND  X.in_out = s.in_out
AND  x.rn > 1;

This is the final result:

SELECT * FROM shift;
GO

worker_name | worker_id | shift_time          | in_out
:---------- | --------: | :------------------ | :-----
Zeab Simon  |         6 | 04/05/2018 07:58:06 | In
Kate B      |         4 | 04/05/2018 14:15:45 | In
Zeab Simon  |         6 | 04/05/2018 16:34:46 | Out
Patty       |         5 | 04/05/2018 20:31:36 | Out

db<>fiddle here


remove leading and trailing quotes I have a field that may start and end with quotes. I only want to remove the quotes if the string starts and ends with quotes. If quotes exists inside the string then I wish to retain them.

I have used REVERSE and stuff which works if the field does start and end with quotes but if it does not it just returns null.

How can I ensure I still get the field result if it did not start with quotes?

What I have:

REVERSE(STUFF(REVERSE(STUFF(fieldname, CHARINDEX('"', fieldname), LEN('"'), '')), CHARINDEX('"', REVERSE(STUFF(fieldname, CHARINDEX('"', fieldname), LEN('"'), '')), 1), 1, ''))

You could try something like this

SELECT CASE WHEN RIGHT(fieldname,1) = '"' AND LEFT(fieldname,1) = '"' THEN REVERSE(STUFF(REVERSE(STUFF(fieldname, 1, 1, '')), 1, 1, ''))
       WHEN RIGHT(fieldname,1) = '"' THEN REVERSE(STUFF(REVERSE(fieldname), 1, 1, ''))
       WHEN LEFT(fieldname,1) = '"' THEN STUFF(fieldname, 1, 1, '')
       ELSE fieldname END as fieldname
FROM #temp;

DB<>Fiddle

declare @str varchar(50)='"ajhd"lajhi"'

select case when (@str like '"%"') then substring(@str,2,len(@str)-2) else @str end as string


Maximize production of log records I need to fill in transactional log file in possibly shortest amount of time.
I have tried a few variants of a loop of inserts and deletes on the same record. I fired it on separate tables in several sessions at the same time. The result was not bad but it is still too slow - about 1 GB per minute. And my log file is 500 GB (Despite regular backups. It is a very busy system, and put in Availability Group).
Could you suggest a clever and effective way to fill in the file?
And in case you are wondering why I am doing such a strange thing. I need to temporarily create a secondary log file and switch the log writer header to it. Then I will be able to truncate and recreate the primary log file. Because of too small autogrowth, it  consists now of some 15,000 VLFs which I want to fix.

This is an XY problem. You want to fix the VLFs in your log file, not fill a transaction log so it overflows into a second log file, freeing up the first log file.
There are definitely ways to stabilize VLFs, even in an AG, even in full recovery, and without disrupting your log chain, creating a second log file, or sending a bunch of bogus transactions to your secondaries. Here is a long-winded but easily reproducible example.

First, let's make a database with less than optimal filegrowth for the log. This will generate some pretty bad VLF layout. Let's also take a backup so it isn't operating in pseudo-simple mode:
CREATE DATABASE abc
      ON (name = N'abc_data', filename   = N'c:\temp\abc.mdf')
  LOG ON (name = N'abc_log',  filename   = N'c:\temp\abc.ldf',
          size = 1024KB,      filegrowth = 1024KB);
GO

ALTER DATABASE abc SET RECOVERY FULL;
GO

BACKUP DATABASE abc TO DISK = 'c:\temp\abc.bak' WITH INIT, COMPRESSION;
GO

Now, let's look at the VLF and file info (note that your metrics may differ slightly). Before adding any data:
SELECT current_vlf_size_mb, total_vlf_count, active_vlf_count,
  avg_size_mb = total_log_size_mb/total_vlf_count , [used_log_%]
    = (SELECT used_log_space_in_percent FROM abc.sys.dm_db_log_space_usage)
FROM sys.dm_db_log_stats(DB_ID(N'abc'));

-------------------  ---------------  ----------------  -----------  ----------
current_vlf_size_mb  total_vlf_count  active_vlf_count  avg_size_mb  used_log_%
-------------------  ---------------  ----------------  -----------  ----------
0.242187             4                1                 0.24804675   49.6063

Let's create a table and generate a bunch of log activity to encourage some sub-optimal log growth (this is to simulate the problem in a new database, not to solve the problem you already have):
SET NOCOUNT ON;
GO

CREATE TABLE dbo.flarb(a int, x char(4020) not null default 'x');
GO

BEGIN TRANSACTION;
INSERT dbo.flarb(a) SELECT object_id FROM sys.all_objects;
COMMIT TRANSACTION;

BEGIN TRANSACTION;
INSERT dbo.flarb(a) SELECT object_id FROM sys.all_objects;
COMMIT TRANSACTION;
GO 500

After we add that data, let's run query 1 again (and also EXEC abc.sys.sp_helpfile;):
-------------------  ---------------  ----------------  -----------  ----------
current_vlf_size_mb  total_vlf_count  active_vlf_count  avg_size_mb  used_log_%
-------------------  ---------------  ----------------  -----------  ----------
1                    11035            11032             0.997552532  99.97239

name     size
-------  -----------
abc_log  11272192 KB

Your data may vary depending on SQL Server version and how many objects are in your database, but order of magnitude should be similar. Let's see what happens when we try to shrink our log file:
DBCC SHRINKFILE(abc_log, 4000);

Running query 1 + sp_helpfile again:
-------------------  ---------------  ----------------  -----------  ----------
current_vlf_size_mb  total_vlf_count  active_vlf_count  avg_size_mb  used_log_%
-------------------  ---------------  ----------------  -----------  ----------
1                    11033            11032              0.99755208  99.9913

name     size
-------  -----------
abc_log  11270144 KB

I mean, it shrank a little wee bit, but didn't have a meaningful impact on VLFs. Think about it - it can't shrink the log because the log hasn't been backed up, so all that data is still active and can't be removed. So let's try backing up the log, then running another shrink, and run the query again:
BACKUP LOG abc TO DISK = 'C:\temp\abc.bak.1.trn' WITH INIT, COMPRESSION;
DBCC SHRINKFILE(abc_log, 4000);

-------------------  ---------------  ----------------  -----------  ----------
current_vlf_size_mb  total_vlf_count  active_vlf_count  avg_size_mb  used_log_%
-------------------  ---------------  ----------------  -----------  ----------
0.242187             11033            6                 0.997552088  3.1719937

name     size
-------  -----------
abc_log  11270144 KB

No good. While we got the log usage down to 3%, we still couldn't reduce the size of the log. Let's try again:
BACKUP LOG abc TO DISK = 'C:\temp\abc.bak.2.trn' WITH INIT, COMPRESSION;
DBCC SHRINKFILE(abc_log, 4000);

-------------------  ---------------  ----------------  -----------  ----------
current_vlf_size_mb  total_vlf_count  active_vlf_count  avg_size_mb  used_log_%
-------------------  ---------------  ----------------  -----------  ----------
0.265625             11033            5                 0.997552088  3.1494207

name     size
-------  -----------
abc_log  4096000 KB

Getting closer! This time, the log file agreed to shrink, but the VLF count is still out of proportion. So, we'll try one more time:
BACKUP LOG abc TO DISK = 'C:\temp\abc.bak.3.trn' WITH INIT, COMPRESSION;
DBCC SHRINKFILE(abc_log, 4000);

-------------------  ---------------  ----------------  -----------  ----------
current_vlf_size_mb  total_vlf_count  active_vlf_count  avg_size_mb  used_log_%
-------------------  ---------------  ----------------  -----------  ----------
0.265625             4027             1                 0.993293317  3.153424

name     size
-------  -----------
abc_log  4096000 KB

This time, it worked a little better. We got the size down, and the log % down, and VLFs are reduced, but still too high (and average size is too small).
So let's try halving the log file, then growing it in even, 8GB chunks. Using 64GB as an example; you may need more, or that may be ridiculous. But the point is to pick a size it's never exceeded before, because if you want to control VLFs, you're going to want to prevent it from auto-growing, and if it grew to 120GB before, it will grow to 120GB again (Lots of background here):
DBCC SHRINKFILE(abc_log, 1);
GO
ALTER DATABASE abc MODIFY FILE (name = 'abc_log', size =  8000MB);
ALTER DATABASE abc MODIFY FILE (name = 'abc_log', size = 16000MB);
ALTER DATABASE abc MODIFY FILE (name = 'abc_log', size = 24000MB);
ALTER DATABASE abc MODIFY FILE (name = 'abc_log', size = 32000MB);
ALTER DATABASE abc MODIFY FILE (name = 'abc_log', size = 40000MB);
ALTER DATABASE abc MODIFY FILE (name = 'abc_log', size = 48000MB);
ALTER DATABASE abc MODIFY FILE (name = 'abc_log', size = 56000MB);
ALTER DATABASE abc MODIFY FILE (name = 'abc_log', size = 64000MB);
-- should probably fix auto-growth here, too

Now, query 1 + sp_helpfile returns:
-------------------  ---------------  ----------------  -----------  ----------
current_vlf_size_mb  total_vlf_count  active_vlf_count  avg_size_mb  used_log_%
-------------------  ---------------  ----------------  -----------  ----------
0.265625             132              1                 484.8484257  0.00710449

name     size
-------  -----------
abc_log  65536000 KB

Your results may vary - you may have 1000 VLFs, you may have 700, it can vary depending on how the shrink and growth operations work through the log. But you should be in a better place. You can keep experimenting with sizes that get you toward an optimal VLF count/size (say, 0.5 GB per VLF).
Remember these log backups were introduced into your log chain. So you should put them with your other log backups, just in case you need to recover, or do this right before your next full backup.
Also, you may wonder, why wasn't one log backup enough? Some background here.

I think you are talking about oracle redo log files. In order to quickly fill in redo log files, you need to pick a very large table, for example, 100 million of rows, and if the table has Date type column, below update will create very large redo:
update big_table set date_column = sysdate where rownum < 500000;


How to drop multiple tables with common prefix in one query? I'm using  Microsoft SQL Server 2008.My question is:How to drop multiple tables with common prefix in one query?

something like that table names:

LG_001_01_STLINE,
LG_001_02_STFICHE

You can build up a string using the catalog views, e.g.:

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

SELECT @sql += '
DROP TABLE '
    + QUOTENAME(s.name)
    + '.' + QUOTENAME(t.name) + ';'
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s
    ON t.[schema_id] = s.[schema_id]
    WHERE t.name LIKE 'LG_001%';

PRINT @sql;
-- EXEC sp_executesql @sql;

Of course there are potential gotchas, for example if these tables have foreign key relationships, you'll either need to drop them first, or arrange the output to drop the tables in a certain order.

To just get the list of tables, use:

SELECT s.name, t.name
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE t.name LIKE 'LG_001%';

SELECT s.name, t.name
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE t.name LIKE 'LG_001%';

Run above query and save results into a csv. Then open that CSV in a notepad. Then do Ctrl+H to replace schema with DROP TABLE SCHEMA that will give you all the drop queries, copy and paste this big sql into your sql tool and execute

if your results are

myschema.table1
myschema.table2

after replace, it'll look like this

DROP TABLE MYSCHEMA.TABLE1
DROP TABLE MYSCHEMA.TABLE2


SELECT (CASE WHEN N.wbs_item_id IS NULL  THEN 'Grand Total' ELSE N.name END) AS '',
(CASE WHEN N.Materials IS NULL  THEN 'Total(Materials)', ELSE N.name END) AS '',
(CASE WHEN N.Labour IS NULL  THEN 'Total(Labour)' ELSE N.name END) AS '',

Total AS ''
FROM(
SELECT (CASE WHEN F.name='Materials' THEN F.name END ) AS 'Materials',
(CASE WHEN F.name='Labour' THEN F.name END ) AS 'Labour',wbs_item_id,SUM(Total) AS 'Total'
FROM(
SELECT b.wbs_item_id,w.name,w.parent_id,b.Total AS 'Total'
FROM wbs_items w,budgets t
LEFT JOIN  budget_items b  ON  b.wbs_item_id = w.id  AND b.id=t.id
)E,(SELECT name,id FROM wbs_item  WHERE parent_id IS NULL )F

WHERE E.wbs_item_id = F.id)N
    GROUP BY Materials,Labour,wbs_item_id WITH ROLLUP;


Bulk Insert Through Network Can someone help me with these?

BULK INSERT DATABESE01.dbo.TABLE01
FROM '\\COMPUTER01\FOLDER01\TextFile.txt'
WITH
(
    FIELDTERMINATOR = ' ',
    rowterminator = '\n',
    tablock
)

The error shows, couldn't open:

  Could not bulk insert because file '\SERVERNAME\FOLDERNAME\textFile.txt' could not be opened. Operating system error code 5(Access is denied.)

The path is on another computer on the network.

I'm going to make a leap of faith and assume you connect to SQL Server using Windows Authentication. In such a case you have a Kerberos  double hop: one hop is your client application connecting to the SQL Server, a second hop is the SQL Server connecting to the remote \\COMPUTER01. Such a double hop falls under the restrictions of Constrained Delegation and you end up accessing the share as Anonymous Login and hence the Access Denied.

To fix the problem you need to enable constrained delegation for the SQL Server service account. This blog How To: SQL Server Bulk Insert with Constrained Delegation (Access is Denied) has an example of how to do it, and I really do hope that the step on how to 'enable unconstrained delegation' is just a typo as unconstrained delegation is just plain evil.

In the case I jumped the gun and you are logging into SQL Server using SQL Authentication you will need to create a credential for your SQL login and use that to access network resources. See CREATE CREDENTIAL.

I'll tell you what works for me every time i get this message.
i open the csv file as a text in a notepad and delete all the space lines in the file,i cant see those blank lines in the excel file.
Usually its just the last line that seems to appear even though its empty


Best database and table design for billions of rows of data I am writing an application that needs to store and analyze  large amounts of electrical and temperature data.

Basically I need to store large amounts of hourly electricity usage measurements for the past several years and for many years to come for tens of thousands of locations and then analyze the data in a not very complex manner.

The information that I need to store (for now) is Location ID, Timestamp (Date and Time), Temperature and Electricity Usage.

About the amount of the data that needs to be stored, this is an approximation, but something along those lines:
20 000+ locations, 720 records per month (hourly measurements, approximately 720 hours per month), 120 months (for 10 years back) and many years into the future. Simple calculations yield the following results:

  20 000 locations x 720 records x 120 months (10 years back) = 1 728 000 000 records.

These are the past records, new records will be imported monthly, so that's approximately 20 000 x 720 = 14 400 000 new records per month.

The total locations will steadily grow as well.

On all of that data, the following operations will need to be executed:

Retrieve the data for a certain date AND time period: all records for a certain Location ID between the dates 01.01.2013 and 01.01.2017 and between 07:00 and 13:00.
Simple mathematical operations for a certain date AND time range, e.g. MIN, MAX and AVG temperature and electricity usage for a certain Location ID for 5 years between 07:00 and 13:00.

The data will be written monthly, but will be read by hundreds of users (at least) constantly, so the read speed is of significantly more importance.

I have no experience with NoSQL databases but from what I've gathered, they are the best solution to use here.
I've read on the most popular NoSQL databases, but since they are quite different and also allow for very different table architecture, I have not been able to decide what is the best database to use.

My main choices were Cassandra and MongoDB, but I since I have very limited knowledge and no real experience when it comes to large data and NoSQL I am not very certain. I also read that PostreSQL also handles such amounts of data well.

My questions are the following:

Should I use a NoSQL database for such large amounts of data. If not can I stick to MySQL?
What database should I use?
Should I keep the date and time in separate, indexed (if possible) columns to retrieve and process the data quickly for certain time and date periods, or can this be done by keeping the timestamp in a single column?
Is a time series data modeling approach appropriate here, and if not could you give me pointers for a good table design?

Thank you.

This is exactly what I do every day, except instead of using the hourly data, I use the 5 minute data. I download about 200 million records everyday, so the amount you talk about here is not a problem. The 5 minute data is about 2 TB in size and I have weather data going back 50 years at an hourly level by location. So let me answer you questions based on my experience:

Don't use NoSQL for this. The data is highly structured and fits a
relational database perfectly.
I personally use SQL Server 2016 and I have no problems applying computations across that volume of data. It was originally on a
PostgreSQL instance when I started my job and it couldn't handle the
volume of data as it was on a small AWS instance.
I would highly recommend extracting the hour portion of the date and storing it separate from the date itself. Believe me, learn from my mistakes!
I store the majority of data list-wise (DATE,TIME,DATAPOINT_ID,VALUE) but that is not how people will want to interpret the data. Be prepared for some horrendous queries against the data and vast amounts of pivoting. Don't be afraid to create a de-normalized table for result sets that are just too large to compute on the fly.

General tip: I store most of the data between two databases, the first is straight-up time series data and is normalized. My second database is very de-normalized and contains pre-aggregated data. As fast as my system is, I am not blind to the fact that users don't even want to wait 30 seconds for a report to load – even if I personally think 30 seconds to crunch 2 TB of data is extremely fast.

To elaborate on why I recommend storing the hour separate from the date, here are a few reasons why I do it that way:

The way that the electrical data is presented is by Hour Ending – therefore, 01:00 is actually the average of the electrical power for the previous hour and 00:00 is Hour Ending 24. (This is important because you actually have to search for two dates to include the 24 hour value – the day you are looking for plus the first mark of the following day.) However, the weather data is actually presented in a forward manner (actual and forecast for the next hour). In my experience with this data, consumers wish to analyze the effect that the weather has on the power price/demand. If you were to use a straight-up date comparison, you would actually be comparing the average price for the previous hour versus the average temperature for the following hour, even though the time stamps are the same. Storing the hour separate from the date allows you to apply transformations to the time with less performance impact than you would see applying a calculation to a DATETIME column.
Performance. I would say at least 90% of the reports that I generate are graphs, normally plotting the price against the hour either for a single date or for a range of dates. Having to split out the time from the date can bog down the speed of the query used to generate the report depending on the date range that you want to see. It is not uncommon for consumers to want to see a single date, Year-on-Year for the past 30 years (in fact for weather this is required to generate the 30 year normals) – this can be slow. Of course you can optimize your query and add indexes, and trust me I have some insane indexes that I would rather not have but it makes the system run fast.
Productivity. I hate having to write the same piece of code more than once. I used to store the date and time in the same column, until I had to write the same query over and over again to extract the time portion. After a while I just got sick of having to do this and extracted it to its own column. The less code you have to write the less chance there is of an error in it. Also, having to write less code means that you can get your reports out faster, nobody wants to be waiting all day for reports.
End users. Not all end users are power users (i.e. know how to write SQL). Having the data already stored in a format that they can bring into Excel (or other similar tool) with minimal effort will make you a hero in the office. If the users cannot access or manipulate the data easily, they will not use your system. Believe me, I designed the perfect system a couple of years ago and nobody used it because of this reason. Database design is not just about adhering to a predefined set of rules/guidelines, it is about making the system usable.

As I said above, this is all based on my personal experience, and let me tell you, it has been a hard few years and a lot of redesigns to get to where I am now. Don't do what I did, learn from my mistakes and make sure you involve the end users of your system (or developers, report authors etc...) when making decisions about your database.

Clearly this is not a NoSQL problem, but I would suggest that while an RDBMS solution would work, I think an OLAP approach will fit much better and given the very limited data ranges involved, I would strongly suggest investigating the use of a column based DB rather then row based one. Think about it this way, you may have 1.7 billion pieces of data, but you still only need 5 bits to index every possible value of hour or day of month.

I have experience with a similar problem domain where Sybase IQ (now SAP IQ) is used to store up to 300 million counters an hour of telecoms equipment performance management data, but I doubt if you have the budget for that sort of solution. In the open source arena, MariaDB ColumnStore is a very promising candidate, but I would recommend also investigating MonetDB.

Since query performance is a major driver for you, give consideration to how queries will be phrased. This is where OLAP and RDBMS show their greatest differences:- with OLAP you normalize for query performance, not to reduce repetition, reduce storage or even to enforce consistency. So in addition to the original timestamp (you did remember to capture its timezone I hope?) have a separate field for the UTC timestamp, other ones for the date and time, and yet more for the year, month, day, hour, minute and UTC offset. If you have additional information about locations, feel free to keep that in a separate location table that can be looked up on demand and feel free to keep the key to that table in your main record but keep the full location name in your main table as well, after all, all possible locations still only take 10 bits to index and every reference you do not have to follow to get the data to be reported is time saved on your query.

As a final suggestion, use separate tables for popular aggregated data and use batch jobs to populate them, that way you don't have to repeat the exercise for each and every report that uses an aggregated value and makes queries that compare current to historical or historical to historical much easier and much, much faster.


How to switch between Oracle 11g and 12c on same linux I have oracle11g and 12c installed on same machine and few test cases that need to be run on both versions.
Can power up both database simultaneously and run test paralally.
Do you know any document that describe this scenario of multiple oracle on same machine.

Note: I am not from DB background and just using it for something so please suggest accordingly

Check the /etc/oratab file. In there the ORACLE_HOME is defined in the second field. Make sure that both instances point to the right ORACLE_HOME. If you do an  . oraenv to change from one instance to another this scripts sets the correct ORACLE_HOME so you connect with the right binaries to start, stop, and access the instance. The file is normally also used to stat-up the instances at boot time.

Example of a line in the /etc/oratab file:

DEVA:/u01/app/oracle/product/11.2.0/db_2:Y

DEVA is the name (SID) of the instance, /u01/app/oracle/product/11.2.0/db_2 is the ORACLE_HOME, and Y indicates that the instance must be started at boot time.

You say that you have no DB background so be aware that you cannot run the same instance in both versions of Oracle.

Check AskTom webpage - Installed multiple home(11g and 12c on same windows server) unable to connect to 11G DB- Error - ORA-12514
I think, will be hard without DB background, but it is possible.


How to stop the SQL Server Agent Job Scheduler with T-SQL? I have a job that I manually start in coordinated time periods that does a bunch of heavy lifting (mostly restoring backups and configuring for a different server)

There are several jobs scheduled to run with increments of minutes to days.  I want to "turn off" the scheduler so nothing will run on schedule until my manual job is done.  I realize that missed jobs will not run again until their next scheduled time, after the scheduler is restarted.

First Step: stop scheduler
Several Heavy lifting steps: Do the hard work
Last Step: start scheduler

I found An Oracle command but I am 100% Microsoft SQL Server

I know that if I Turn off the SQL agent, jobs will not run but that is not what I am looking for.

The jobs that exist for today, are not expected to be the same jobs next time.  Disabling jobs with EXEC dbo.sp_update_job is not a viable solution.

Additional information: There may also be jobs in place that are intentionally disabled for some reason, that do want to be re-enabled. Stopping the scheduler seems like the best choice.

I would recommend to start by creating a staging table that will hold some information for you. The table should be constructed to hold the data the below code outputs. I recommend to store the Schedule_ID, name, and job_id at the minimum. (If you want to cheat, you can expand the asterisks and select the columns you want, and then add the word INTO along with the database.schema.table_name_you_want_to_create and it will create the table for the first time for you. After that you can change it into an insert.)

SELECT * FROM
MSDB.dbo.sysschedules ss
INNER JOIN msdb.dbo.sysjobschedules jss
   ON jss.schedule_id = ss.schedule_id
WHERE ss.enabled = 1

That code returns the schedules for all schedules and it also performs an inner join to limit the data returned to only schedules that are paired to jobs currently that are enabled.

You can then create a loop or cursor or something similar to loop through the staging table and execute the sp_update_schedule procedure. This will disable all schedules that are enabled and paired to a job. Once your maintenance is complete, you can run the loop once more, but this time enabling the schedules you had disabled earlier.

If you would like an example of a loop, you can see an example I have created in the past on stack overflow.

I have gone back and used the suggestions to create a complete code solution. Please see below, there are two scripts in total. The first will disable your jobs and the second will re-enable them. TEST THIS IN A TEST ENVIRONMENT BEFORE PUTTING IT IN PRODUCTION. Source for completeness.

Script one:

USE MSDB;

/*************************************************************
 Checking for history table. Creating it if it doesn't exist.
*************************************************************/

IF OBJECT_ID('dbo.JobsEnabledTracker', 'U') IS NULL
BEGIN
CREATE TABLE [dbo].[JobsEnabledTracker](
[Id] [INT] IDENTITY(1, 1) NOT NULL,
[job_id]      [UNIQUEIDENTIFIER] NULL,
[schedule_id] [BIGINT] NULL,
[enabled]     [BIT] NULL);
END;
IF EXISTS
(
    SELECT
           1
    FROM [dbo].[JobsEnabledTracker]
    WHERE [enabled] = 1
)
   OR
(
    SELECT
           COUNT(*)
    FROM [dbo].[JobsEnabledTracker]
) = 0
    BEGIN
        PRINT 'There are jobs enabled or there are no jobs yet populated in the history table.';

/***********************
 Clear out history table
***********************/

        PRINT 'Truncating history table: dbo.JobsEnabledTracker';
        TRUNCATE TABLE [dbo].[JobsEnabledTracker];

        PRINT 'Inserting records into history table: dbo.JobsEnabledTracker';

/******************************
 Add in values to history table
******************************/

        INSERT INTO [dbo].[JobsEnabledTracker]
        (
               [job_id],
               [schedule_id],
               [enabled]
        )
        SELECT
               [jss].[job_id],
               [jss].[schedule_id],
               1 AS 'enabled'
        FROM [msdb].[dbo].[sysschedules] AS [ss]
             INNER JOIN [msdb].[dbo].[sysjobschedules] AS [jss] ON [jss].[schedule_id] = [ss].[schedule_id]
        WHERE [ss].[enabled] = 1;

/**********************************************************************************
 Table variable to hold schedules and jobs enabled. This is important for the loop.
**********************************************************************************/

        DECLARE @JobsEnabled TABLE
        ([Id]          INT
         PRIMARY KEY IDENTITY(1, 1),
         [job_id]      UNIQUEIDENTIFIER,
         [schedule_id] BIGINT,
         [enabled]     BIT
        );

/*****************************************
 Insert schedules that we need to disable.
*****************************************/

        INSERT INTO @JobsEnabled
        (
               [job_id],
               [schedule_id],
               [enabled]
        )
        SELECT
               [job_id],
               [schedule_id],
               [enabled]
        FROM [dbo].[JobsEnabledTracker];

/********************************
 Holds the job id and schedule id
********************************/

        DECLARE @jobid UNIQUEIDENTIFIER;
        DECLARE @scheduleid BIGINT;

/***********************************
 Holds the ID of the row in the loop
***********************************/

        DECLARE @ID INT= 0;

/**********************
 Check if records exist
**********************/

        IF EXISTS
        (
            SELECT
                   [Id]
            FROM @JobsEnabled
        )
            BEGIN
                PRINT 'Loop mode, jobs found enabled.';

/**********
 Begin loop
**********/

                WHILE(1 = 1)
                    BEGIN

/***************************************
 Grab jobid, scheduleid, and id of rows.
***************************************/

                        SELECT
                               @jobid =
                        (
                            SELECT TOP 1
                                   [job_id]
                            FROM @JobsEnabled
                            ORDER BY
                                     [job_id]
                        );
                        SELECT
                               @scheduleid =
                        (
                            SELECT TOP 1
                                   [schedule_id]
                            FROM @JobsEnabled
                            ORDER BY
                                     [job_id]
                        );
                        SELECT
                               @ID =
                        (
                            SELECT TOP 1
                                   [Id]
                            FROM @JobsEnabled
                            ORDER BY
                                     [job_id]
                        );

/************************************
 Re-enable schedule associated to job
************************************/

                        PRINT 'Disabling schedule_id: '+CAST(@scheduleid AS VARCHAR(255))+' paired to job_id: '+CAST(@jobid AS VARCHAR(255));
                        EXEC [sp_update_schedule]
                             @schedule_id = @scheduleid,
                             @enabled = 0;

/*********************
 Removes row from loop
*********************/

                        DELETE FROM @JobsEnabled
                        WHERE
                              [Id] = @ID;

                        UPDATE [dbo].[JobsEnabledTracker]
                          SET
                              [enabled] = 0
                        WHERE
                              [job_id] = @jobid
                              AND [schedule_id] = @scheduleid;

/****************************
 No more rows, stops deleting
****************************/

                        IF
                        (
                            SELECT
                                   COUNT(*)
                            FROM @JobsEnabled
                        ) <= 0
                            BEGIN
                                BREAK
                            END;

/********
 End Loop
********/
                    END;
                PRINT 'Exiting loop, disabling schedules paired to jobs complete.';

/**********
 End elseif
**********/
            END;
            ELSE
            BEGIN
                PRINT 'All done';
            END;
    END;
    ELSE
    BEGIN
        PRINT 'YOU HAVE JOBS STILL DISABLED, EXITING SCRIPT. PLEASE RUN SCRIPT TWO FIRST.';
    END;

Script two

USE MSDB;

/*******************************************************************************
 Check for history table. This physical table tells us what jobs we are going to
 enable the scheduler for.
*******************************************************************************/

IF OBJECT_ID('dbo.JobsEnabledTracker', 'U') IS NOT NULL
BEGIN
    IF EXISTS
    (
        SELECT 1
        FROM [dbo].[JobsEnabledTracker]
        WHERE [enabled] = 0
    )
    BEGIN
        PRINT 'Jobs disabled in history table: dbo.JobsEnabledTracker found.';

/**********************************************************************************
 Table variable to hold schedules and jobs enabled. This is important for the loop.
**********************************************************************************/

        DECLARE @JobsEnabled TABLE
        (
        [Id] int PRIMARY KEY IDENTITY(1, 1)
        , [job_id] uniqueidentifier
        , [schedule_id] bigint
        , [enabled] bit
        );

/*******************************************************************************
 Insert schedules that we had disabled that we need to go back in and re-enable.
*******************************************************************************/

        INSERT INTO @JobsEnabled( [job_id], [schedule_id], [enabled] )
               SELECT [job_id], [schedule_id], [enabled]
               FROM [dbo].[JobsEnabledTracker];

/********************************
 Holds the job id and schedule id
********************************/

        DECLARE @jobid uniqueidentifier;
        DECLARE @scheduleid bigint;

/***********************************
 Holds the ID of the row in the loop
***********************************/

        DECLARE @ID int= 0;

/**********************
 Check if records exist
**********************/

        IF EXISTS
        (
            SELECT [Id]
            FROM @JobsEnabled
        )
        BEGIN
            PRINT 'Loop mode, jobs found disabled.';

/**********
 Begin loop
**********/

            WHILE 1 = 1
            BEGIN

/***************************************
 Grab jobid, scheduleid, and id of rows.
***************************************/

                SELECT @jobid =
                (
                    SELECT TOP 1 [job_id]
                    FROM @JobsEnabled
                    ORDER BY [job_id]
                );
                SELECT @scheduleid =
                (
                    SELECT TOP 1 [schedule_id]
                    FROM @JobsEnabled
                    ORDER BY [job_id]
                );
                SELECT @ID =
                (
                    SELECT TOP 1 [Id]
                    FROM @JobsEnabled
                    ORDER BY [job_id]
                );

/***************************************
 Re-enable schedule associated to job
***************************************/

                PRINT 'Enabling schedule_id: '+CAST(@scheduleid AS varchar(255))+' paired to job_id: '+CAST(@jobid AS varchar(255));
                EXEC [sp_update_schedule] @schedule_id = @scheduleid, @enabled = 1;

/*********************
 Removes row from loop
*********************/

                DELETE FROM @JobsEnabled
                WHERE [Id] = @ID;

/***********************
 Set job back to enabled
***********************/

                UPDATE [dbo].[JobsEnabledTracker]
                  SET [enabled] = 1
                WHERE [job_id] = @jobid AND
                      [schedule_id] = @scheduleid;

/****************************
 No more rows, stops deleting
****************************/

                IF
                (
                    SELECT COUNT(*)
                    FROM @JobsEnabled
                ) <= 0
                BEGIN
                    BREAK;
                END;

/********
 End Loop
********/
            END;
            PRINT 'Exiting loop, enabling schedules paired to jobs complete.';

/**********
 End elseif
**********/
        END;
        ELSE
        BEGIN
            PRINT 'All done';
        END;
    END;
    ELSE
    BEGIN
        PRINT 'dbo.JobsEnabledTracker has no disabled jobs currently.';
    END;
END;
ELSE
BEGIN
    PRINT 'dbo.JobsEnabledTracker is NULL, you may need to run the first script to create and populate this table.';
END;

sp_stop_job ?

Link to documentation: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-stop-job-transact-sql?view=sql-server-2017


Merging multiple databases as separate schemas in PostgreSQL I have to combine data from 4 different databases. Each having its own design.

What i want to do is to Create a new database with my desired schema. and one schema per (to be merged) database

Then export tables from all other databases and import this data into a unique corresponding schemas schema inside my final database.

How do i export table data from Postgresql Databases.

After having one database with multiple schemas i will use cross schema queries to read and write data to target schema.

Thanks in anticipation.

I would assume that the other databases are Postgres as well? And all of them have only one schema? If you only have to do it once, then the easiest way is to use pg_dump -Fc to export the database and then edit the resulting SQL file by some regexp script.

In detail, you will have a

SET search_path = public;

row near the top of the file.  Rewrite 'public' to the desired schema name.  Other places to check for are where a stored procedure returns a user-defined type, view or table rowtype, here you will see something similar to

CREATE OR REPLACE FUNCTION f()
RETURNS public.my_view AS
...

Rewrite 'public' in such places as well.

To export/import data you can use COPY FROM / COPY TO. Details here.

Or you can use dblink to query data in other database. Details here.


Possible to make MySQL use more than one core? I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer than DBA for MySQL so need some help

Setup

The servers are quite hefty with an OLAP/DataWarehouse (DW) type load:

Primary: 96GB RAM, 8 cores + single RAID 10 array
Test: 32GB RAM with 4 cores
The biggest DB is 540 GB, the total is around 1.1TB and mostly InnoDB tables
Solaris 10 Intel-64
MySQL 5.5.x

Note: The biggest DB is the replicated one from the OLTP DR server and the DW is loaded from this. It isn't a full DW: just last 6 months to 6 weeks so it is smaller than the OLTP DB.

Observations on a test server

3 separate connections
each has a concurrent (and different) ALTER TABLE...DROP KEY...ADD INDEX
the 3 tables have a 2.5, 3.8 and 4.5 million rows
CPU usage goes up to 25% (one core is maxed out) and no higher
the 3 ALTERs take 12-25 minutes (a single on the smallest takes 4.5)

Questions

What setting or patch is required to allow more than one core to be used?
That is, why doesn't MySQL use all cores available? (like other RDBMS)
Is it a consequence of replication?

Other notes

I understand the difference between an RDBMS "thread" and an OS "thread"
I'm not asking about any form of parallelism
Some of the system variables for InnoDB and threads are sub-optimal(looking for a quick win)
Short term, I'm unable to change the disk layout
OS can be tweaked if needed
A single ALTER TABLE on the smallest table takes 4.5 minutes (shocking IMO)

Edit 1

innodb_thread_concurrency is set to 8 on both. Yes, it's wrong but won't make MySQL use multiple cores
innodb_buffer_pool_size is 80GB on primary, 10GB on a test (another instance is shut down). This is OK for now.
innodb_file_per_table = ON

Edit 2

innodb_flush_log_at_trx_commit = 2
innodb_use_sys_malloc = ON
innodb_flush_method should be O_DIRECT (but SHOW VARIABLES doesn't show this)
innodb_doublewrite = OFF
File system = ZFS (And my sysadmin found this: http://blogs.oracle.com/realneel/entry/mysql_innodb_zfs_best_practices)

To test

innodb_flush_method isn't showing as O_DIRECT when it should be
will follow RolandoMySQLDBA's settings

Let me know if I've missed anything important

Cheers

Update

Changed innodb_flush_method + 3 x thread settings in RolandoMySQLDBA's answer
Result: > 1 core used for the tests = positive result

I actually discussed innodb_thread_concurrency with a MySQL Expert at the Percona Live NYC conference back in May 2011.

I learned something surprising: In spite of the documentation, it is best to leave innodb_thread_concurrency at 0 (infinite concurrency). That way, InnoDB decides the best number of innodb_concurrency_tickets to open for a given MySQL instance setup.

Once you set innodb_thread_concurrency to 0, you can set innodb_read_io_threads and innodb_write_io_threads (both since MySQL 5.1.38) to the maximum value of 64. This should engage more cores.

Consider that your bottleneck could be your filesystem's IO performance.

In addition to the settings suggested by @RolandoMySQLDBA, I also set the noatime mount settings in /etc/fstab for the partition holding my mysql data directory (/data01/mysql in my case, with /dev/sdb1 mounted to /data01).

By default linux records the access time for EVERY disk read or write which negatively affects IO performance, especially for high IO applications like databases. This means that even reading data from a file triggers a write to disk... WAT!

To disable this, add the noatime mount option in /etc/fstab for the desired mount point as follows (example in my case):

/dev/sdb1  /data01  ext4  defaults,noatime  0  2

Then remount the partition:

mount -o,remount /data01

This should boost the read/write performance of applications using that partition.  BUT... nothing beats holding all your data in memory.


Implicitly (or proxy) convert to Nvarchar on incoming queries from legacy or 3rd party applications We have a 3rd party application of which we don't have the source code. This application executes queries on our database without using the "N" prefix before literal strings. It does this in inserts and where clauses.

So what we need is a way to translate a query like this:

SELECT Whatever FROM Person WHERE Name = 'John';

to this:

SELECT Whatever FROM Person WHERE Name = N'John';

Is there a way to implicitly force this on SQL server (2005 or higher, I guess if an upgrade is needed it would be acceptable).

Otherwise, does anyone know of a 3rd party proxy that would be able to translate the queries? Building one would be costly, but I think it is feasible. Or could we use some other trick like a trigger or something?

I have already checked if the 3rd party application can handle multi-byte characters in the strings that are sent and returned, that is not a problem at all. The only problem is that it's formatting the queries without using N's. (It should have been using parameters but we can't change it anyway).

I guess the implicit conversion causes index scan, if that is the case, You can use plan guides and force index seek without changing the source code.


Updating multiple values at a time I have been given the task to update a few rows in a database. There are two columns in which one is ID and the other is CURRENCY. I have updated these rows using the update statements that follow:
update account set currency = 'INR' where id =15;
update account set currency = 'EURO' where id =12;
update account set currency = 'DOLLAR' where id =18;
update account set currency = 'Pound' where id =13;
-- and so on.

Actually, in this case I was able to do it easily using these update statements because of a relatively low number of rows, but what if there are thousands or hundreds of thousands of rows? Is this the only way to update them, or is there any alternate way or script?

You can use a table constructor with VALUES:

update account
set currency = nv.currency
from
    ( values
        (12, 'EURO'),
        (18, 'DOLLAR'),
        (13, 'Pound')
    ) as nv (id, currency)
where account.id = nv.id ;

You could use the "in" clause to identify multiple rows where the currency can be set to the same value:

update account
set currency = 'EURO'
where id in ( 12, 24, 38, 217 );

However, this will get slower and slower (and slower) as the list of values grows.

If there is some other way to identify these rows, then use that instead:

update account
set currency = 'DOLLAR'
where country = 'USA' ;


Load big file in MySQL table I've read some posts related to issues similar to this, but without success, so I'm trying to post my specific problem.

I have to load a big CSV file (25Gb) into a MySQL table. I'm doing this with this environment:

MacOS Mojave 10.14.3.
16 GB DDR3 RAM Memory.
75 GB free space in my hard disk.
MySQL 5.7 for MacOS 10.14 with default configuration.

At first I tried with this:

CREATE DATABASE mydatabase CHARACTER SET utf8;
CREATE TABLE mytable (field1 TEXT, field2 INTEGER, field3 VARCHAR(50));
LOAD DATA INFILE 'hugefile.csv' IGNORE INTO TABLE mytable CHARACTER SET utf8;
CREATE INDEX index_3 ON mytable (field3);

But my computer run out of memory and free hard disk space and the process crashed after more than 30 hours!!

I tried this with the same results:

CREATE DATABASE mydatabase CHARACTER SET utf8;
CREATE TABLE mytable (field1 TEXT, field2 INTEGER, field3 VARCHAR(50));
LOAD DATA INFILE 'hugefile.csv' IGNORE INTO TABLE mytable CHARACTER SET utf8;
CREATE INDEX index_3 ON mytable (field3);

Finally, after some investigation I tried with this MySQL configuration file:

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
secure_file_priv="/usr/local/mysql/data/"
innodb_buffer_pool_size = 6G
innodb_log_file_size = 4G
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity_max = 2000
innodb_io_capacity = 1000
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

And this code (using MyISAM engine):

CREATE DATABASE mydatabase CHARACTER SET utf8;
CREATE TABLE mytable (field1 TEXT, field2 INTEGER, field3 VARCHAR(50)) ENGINE = MYISAM;
CREATE INDEX index_3 ON mytable (field3);
LOAD DATA INFILE 'hugefile.csv' IGNORE INTO TABLE mytable CHARACTER SET utf8;

The process has a much better performance, it doesn't crash but it takes 4 hours to complete.

The issue is that I've asked one of my coworkers to try this with the exact same process, the same environment and the same configuration (the only difference is that he has 150GB of free hard disk space) and the process takes only 1 hour!

So, my doubt is:

What could be the difference between his Mac and mine to have such a different performance? What could I check?
Is there anything I could change in MySQL configuration to improve the performance of this process?
Is it normal that the process requires all of my free hard disk space? During the process I monitor the free space and it keeps decreasing from 75GB to only 500MB (aprox), when I start getting memory warnings.

EDIT to add answers to Rick's questions:

Both of us have SSDs.
I haven't set key_buffer_size, so I guess it has its default value (8388608 according to the documentation of MySQL 5.7).
That TEXT column has all of its values between 5 and 12 characters
Things go sour during create index: with load+create index the load command takes 10 minutes approximately and the create index takes hours; with create index+load, the create index is automatic and load takes hours (normal).

The data in a CSV file is encoded one way; the table in MySQL is encoded another way.  The difference could be 2x either way.  That is, the 25GB csv file might, for example, expand to 50GB, then have trouble adding on the indexes.

When MySQL runs out of disk space, it waits for you to free some space.  This might explain 40 hours.  Or it dies.

Does one of you have SSDs, the other have HDDs?

What was the value of key_buffer_size when using MyISAM?  Keep in mind that innodb_buffer_pool_size and key_buffer_size are competing for RAM, so you should be careful when switching between engines.

InnoDB wants a PRIMARY KEY; you don't have one.

How big is the TEXT column typically?  InnoDB stores them off-record in some situations.

When did things go sour?  During the LOAD?  Or during the CREATE INDEX?

When adding an index to a MyISAM table, it will either "repair via key buffer" or "repair by copying" (or something like that).  The clue is visible in SHOW PROCESSLIST during the CREATE INDEX.  If it is by "key_buffer", then 8M is much too small.  1G may be suitable for your machine.  If it is the other one, then I am surprised that it took so long.

Another trick (depending on the indexes you have or will have) is to first sort the data by the main key (field3? in your case) before doing the LOAD DATA.

If field1 is always 12 characters or fewer, then declare it VARCHAR(12).  (If it might get a bit bigger, pick some other limit.)  TEXT has some drawbacks compared to VARCHAR.  I am not sure whether it is relevant for this case, but it should not hurt.

You already have an improvement from more than 40 hours to 4 hours, and still complaining ? …

This increase of speed seems to be related to your changes in the configuration of MySQL, but it's unclear what you changed. Also is this configuration of MySQL the same as the configuration your coworker has?

If both configurations are the same you will have to find other differences, which can be the cause of this difference in needed time.


Is there a value that I can use in a SELECT TOP that will return all rows? I'm allowing the end user to define how many rows are returned by a query (SELECT TOP (@x)). Is there a value that can be entered where all rows are returned? Or do I have to dynamically create the query without the TOP (@x) if they want all rows returned?

I'm using SQL Server 2012.

Well, it looks like TOP is a BIGINT if you aren't using a PERCENT.  That means you could pass in the max value of BIGINT,

SELECT TOP (9223372036854775807) * FROM table1

I seriously doubt you will ever see a table that large.  I'm not sure what kind of effect that would have on the query plan though.

select top(SELECT COUNT(*) FROM YourTable) * From YourTable


mdf,ldf file attach problem? I've created one database on my local system. consider database name is test. This database primary files are test.mdf, test_log.ldf. and contains ndf files too.

Then i've stopped sql server service. then copied these primary files to another server machine*(not ndf files).*

Now i've to create one database 'test1' on that server machine, and have to attach these test.mdf and test_log.ldf files.

create database test1 on primary ( file name='C:\test.mdf'), ( file name='C:\test_log.ldf') for attach go

this query executed in server machine. it says error as """ Msg 5120, Level 16, State 5, Line 1 Unable to open the physical file "D:\data\ExistTable\FG1_lt_4000.ndf". Operating system error 2: "2(The system cannot find the file specified.)".

Msg 5120, Level 16, State 5, Line 1 Unable to open the physical file "D:\data\ExistTable\FG2_4000_8000.ndf". Operating system error 2: "2(The system cannot find the file specified.)".

Msg 5120, Level 16, State 5, Line 1 Unable to open the physical file "D:\data\ExistTable\FG3_8000_inf.ndf". Operating system error 2: "2(The system cannot find the file specified.)".

Msg 1813, Level 16, State 2, Line 1 Could not open new database 'test'. CREATE DATABASE is aborted. """

now what to do???

Well, did you try:

create database test1 on primary
  ( filename='C:\test.mdf'),
  ( filename='C:\FG1_lt_4000.ndf'),
  ( filename='C:\FG2_4000_8000.ndf'),
  ( filename='C:\FG3_8000_inf.ndf'),
  ( filename='C:\test_log.ldf')
for attach;

And yes, you do need to copy the ndf files as well. These are critical parts of your database, not garbage.

Otherwise, go back to your original server, take a backup, and then do a restore with move on the new server.

Your problem is with the permissions in your folder or files (mdf/ldf)!


Help with a table having fixed number of self-referencing columns I'm trying to design an electronic toll collection system, where I have stumbled on decision how to design road section. Each road section is designed in a way that it has references to other road sections that lie ahead(RSA) , behind(RSB) and across(ORS). I tried to visually demonstrate them in figure below.

Visual presentation of Road Section with their primary keys is given below.

   |     |    || Road     |
   |          || Section  |
   |     |    || Ahead    |
   |    (1)   ||   (6)    |
------------------------------
   |     |    ||    |     |
   |          ||          |
   | Opposite || Road     |
   | Road     || Section  |
   | Section  ||          |
   |     |    ||    |     |
   |    (2)   ||   (5)    |
---------|----------|---------
   |          ||          |
   |     |    || Road     |
   |    (3)   || Section  |
   |     |    || Behind   |
   |     |    ||   (4)    |

I have two approaches in mind in designing such entity. The first one is using same table and have 3 columns referencing other rows in the same table.

Road Section Table
---------------------------------------------------
| PK_RS | FK_ORS | FK_RSA | FK_RSB | Other data
---------------------------------------------------
|   5   |   2    |   6    |   4    |  ...
|   4   |   3    |   5    |  null  |  ...

The second one is to create separate table for road section relationships, called Road Section Mapping

Road Section Table
-----------------------------
| PK_RS | Other data
-----------------------------
|   4   |  ...
|   5   |  ...
Road Section Map Table
------------------------------------
| PK_RS | FK_ORS | FK_RSA | FK_RSB |
------------------------------------
|   5   |   2    |   6    |   4    |
|   4   |   3    |   5    |  null  |

In the first case I will need to create rows with null references at first and update foreign keys later. In second a can create relations after creating road sections first.

What is the best practice in such kind of models?

First approach seems to be more logical.
Because ManyToOne usually stored in same table.


When do I need to backup the Service Master Key? I'm reading some documentation and whitepaper about Transparent Data Encryption. Some documentation mention to backup the Service Master Key as well (For clarification I'm not talking about the Database Master Key). I just don't exactly understand why this is necessary, because I was able to backup/restore a Database with TDE Encryption from Server A (backup) to Server B (restore) without using any Service Master Key.

In what scenario do I need to restore the Service Master Key?

If you are talking about the SQL service master key, then there is a rare occurrence where you really need to restore it.

I'm thinking of a couple of scenarios where you need to restore the SMK...

Somehow it got corrupted.
You are rebuilding your SQL server and planning to restore every database including system databases from backup. Usually in this case also you might not need to restore the SMK if you are using the same SQL service account and password.

In TDE you don't need to restore the SMK. As everyone said, you just need the certificate and the private key. You don't need to have same database master key, also as when you create the certificate from backup it gets encrypted by the DMK of destination machine.

One case where you need to backup and restore the SMK is when you are upgrading a replication topology.


Query to find all dates between two specified dates using plsql I have a table with below structure
create table test_table
(
  customer_num      NUMBER,
  card_number       char(2),
  card_issue_date   DATE,
  card_expire_date  DATE
)

Example data is:
customer_num    |  card_number   |  card_issue_date | card_expire_date
--------------------------------------------------------------------------
  1             |  x             |  1/1/2019        | 1/4/2020
  1             |  y             |  1/1/2020        | 1/4/2020
  1             |  z             |  1/11/2020       | 1/15/2020
  1             |  b             |  1/11/2020       | 1/20/2020
  1             |  w             |  1/12/2020       | 1/17/2020
  1             |  a             |  1/18/2020       | 2/12/2020

For each customer's card, I need to have all dates between card_issue_date and card_expire_date.
So the output for the first record would be like this :
customer_num    |  card_number   |  card_issue_date
----------------------------------------------------
  1             |  x             |  1/1/2019
  1             |  x             |  1/2/2020
  1             |  x             |  1/3/2020
  1             |  x             |  1/4/2020

IS there any function or something else that can help me out with this?
Thanks in advance

This should get you started:
declare
  s date := date'2020-01-01';
  e date := date'2020-01-04';
  ndays number;
begin
  ndays := e-s+1;
  for i in 1..ndays loop
    dbms_output.put_line (s);
    s := s+1;
  end loop;
end;
/

01-JAN-2020
02-JAN-2020
03-JAN-2020
04-JAN-2020

PL/SQL procedure successfully completed.

Just use simple date arithmetic: the difference between two dates is the number of days between the two:
date'2020-01-04' - date'2020-01-01' + 1

The above returns 4 (the +1 is so as to also include the last day, since this is what you describe in your example).
Then the rest is just a loop over the number of days, incrementing the start date by one day on each loop:
s := s + 1;

The next stage is to turn that into a usable function. First we need a type:
create or replace type date_array as table of date;
/

This defines an array of dates as is used as the output of the pipelined function:
create or replace function dates_between (s date, e date)
return date_array
pipelined
is
  d date := s;
begin
  for i in 1..e-s+1 loop
    pipe row(d);
    d := d + 1;
  end loop;
end;
/
show errors

This function returns the list of dates between two dates (including the end date). Like this:
SQL> select * from table (dates_between (date'2020-02-23',date'2020-03-05'));

COLUMN_VALUE
-----------------
23-FEB-2020
24-FEB-2020
25-FEB-2020
26-FEB-2020
27-FEB-2020
28-FEB-2020
29-FEB-2020
01-MAR-2020
02-MAR-2020
03-MAR-2020
04-MAR-2020
05-MAR-2020

12 rows selected.

Let's try that with your example. First let's create and populate the test table:
drop table test_table purge;
create table test_table (
  customer_num      NUMBER,
  card_number       char(2),
  card_issue_date   DATE,
  card_expire_date  DATE
);
insert into test_table values (1,'x',to_date('1/1/2020 ','MM-DD-YYYY')  ,to_date('1/4/2020','MM-DD-YYYY'));
insert into test_table values (1,'y',to_date('1/1/2020 ','MM-DD-YYYY')  ,to_date('1/4/2020','MM-DD-YYYY'));
insert into test_table values (1,'z',to_date('1/11/2020','MM-DD-YYYY')  ,to_date('1/15/2020','MM-DD-YYYY'));
insert into test_table values (1,'b',to_date('1/11/2020','MM-DD-YYYY')  ,to_date('1/20/2020','MM-DD-YYYY'));
insert into test_table values (1,'w',to_date('1/12/2020','MM-DD-YYYY')  ,to_date('1/17/2020','MM-DD-YYYY'));
insert into test_table values (1,'a',to_date('1/18/2020','MM-DD-YYYY')  ,to_date('2/12/2020','MM-DD-YYYY'));
commit;

(Noticed I corrected the first date to match your description).
Here is the query that gives the result you describe:
select customer_num, card_number, column_value as card_date
from test_table, table (dates_between (card_issue_date,card_expire_date));

which returns:
CUSTOMER_NUM CARD_N CARD_DATE
------------ ------ -----------------
           1 x      01-JAN-2020
           1 x      02-JAN-2020
           1 x      03-JAN-2020
           1 x      04-JAN-2020
           1 y      01-JAN-2020
           1 y      02-JAN-2020
           1 y      03-JAN-2020
           1 y      04-JAN-2020
           1 z      11-JAN-2020
           1 z      12-JAN-2020
           1 z      13-JAN-2020
           1 z      14-JAN-2020
           1 z      15-JAN-2020
           1 b      11-JAN-2020
           1 b      12-JAN-2020
           1 b      13-JAN-2020
           1 b      14-JAN-2020
           1 b      15-JAN-2020
           1 b      16-JAN-2020
           1 b      17-JAN-2020
           1 b      18-JAN-2020
           1 b      19-JAN-2020
           1 b      20-JAN-2020
           1 w      12-JAN-2020
           1 w      13-JAN-2020
           1 w      14-JAN-2020
           1 w      15-JAN-2020
           1 w      16-JAN-2020
           1 w      17-JAN-2020
           1 a      18-JAN-2020
           1 a      19-JAN-2020
           1 a      20-JAN-2020
           1 a      21-JAN-2020
           1 a      22-JAN-2020
           1 a      23-JAN-2020
           1 a      24-JAN-2020
           1 a      25-JAN-2020
           1 a      26-JAN-2020
           1 a      27-JAN-2020
           1 a      28-JAN-2020
           1 a      29-JAN-2020
           1 a      30-JAN-2020
           1 a      31-JAN-2020
           1 a      01-FEB-2020
           1 a      02-FEB-2020
           1 a      03-FEB-2020
           1 a      04-FEB-2020
           1 a      05-FEB-2020
           1 a      06-FEB-2020
           1 a      07-FEB-2020
           1 a      08-FEB-2020
           1 a      09-FEB-2020
           1 a      10-FEB-2020
           1 a      11-FEB-2020
           1 a      12-FEB-2020

55 rows selected.

Using just SQL and using Tom's as_many_rows trick(adjust as required):
with as_many_rows_as_you_need
as
( select level l
  from dual
  connect by level <= 100
)
select customer_num cust, card_number card, (a.card_issue_date + l) dat
from   as_many_rows_as_you_need
     , test_table a
where  a.card_issue_date + l <= a.card_expire_date
and    a.customer_num = 1
--and    a.card_number  = 'x'
order by card_number, (a.card_issue_date + l)
;

Using above table definition and data, results in(cut-off...):
CUST    CARD    DAT
1   a   19-jan-20
1   a   20-jan-20
1   a   21-jan-20
1   b   12-jan-20
1   b   13-jan-20
1   b   14-jan-20
1   b   15-jan-20
1   b   16-jan-20
1   b   17-jan-20
1   b   18-jan-20
1   b   19-jan-20
1   b   20-jan-20
1   w   13-jan-20
1   w   14-jan-20
1   w   15-jan-20
1   w   16-jan-20
1   w   17-jan-20
...


Oracle database Architecture I need to know to correct order between these following steps, it's hard to find information on the internet on what happens first, i'd be really grateful if someone could give me the answer

A. Oracle searches for a init.ora to determine the configuration
       parameters

B. The proces SMON sometimes executes an instance-recovery

C. memory for the SGA gets allocated and the  background processes that are necessairy get started

D. Oracle searches the database files and redo log files

E. oracle starts an instance

F. oracle searches for the control files with the parameter CONTROL_FILES

A. Oracle searches for a init.ora to determine the configuration
parameters

without a parameterfile, you will get an LRM-00109, ORA-01078 error, and nothing gets started

E. oracle starts an instance

we could argue what exactly starting an instance means, I have just simply put it here because this is the "exact" message that appears first when starting an instance

C. memory for the SGA gets allocated and the background processes that are necessairy get started

background processes like LGWR, DBWR, CKPT, SMON, PMON start in NOMOUNT, way before doing anything with control files...

F. oracle searches for the control files with the parameter CONTROL_FILES

thats the next one when going to MOUNT

D. Oracle searches the database files and redo log files

now tell me, how could the database perform an instance recovery without knowing where the datafiles and redo logs are

B. The proces SMON sometimes executes an instance-recovery

finally an instance recovery is performed, and database gets opened

oracle starts an instance

Oracle searches for a init.ora to determine the configuration
 parameters

oracle searches for the control files with the parameter CONTROL_FILES

memory for the SGA gets allocated and the background processes that are necessairy get started

The proces SMON sometimes executes an instance-recovery

Oracle searches the database files and redo log files


MySQL create index with LOCK=NONE still locks a table I have the following MySQL RoR Migrations:

class ReindexRpushNotification < ActiveRecord::Migration
  def up
    execute("DROP INDEX `index_rpush_notifications_multi` ON rpush_notifications;")
    execute("ALTER TABLE rpush_notifications ADD INDEX index_rpush_notifications_multi (delivered, failed, processing, deliver_after), ALGORITHM=INPLACE, LOCK=NONE;")
  end

  def down
    execute("DROP INDEX `index_rpush_notifications_multi` ON rpush_notifications;")
    execute("ALTER TABLE rpush_notifications ADD INDEX index_rpush_notifications_multi (delivered, failed), ALGORITHM=INPLACE, LOCK=NONE;")
  end
end

during this migration I'm trying to perform some requests (GET, COUNT, DELETE, UPDATE) but nothing work, all these requests just wait

I found an info about index creation in background here

https://stackoverflow.com/a/36064200

http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

but it doesn't work for us

Did anybody try LOCK=NONE?

We use MySQL 5.6.23 on AWS RDS

Looks like I have a clue why it doesn't work

Our table have the following constraint:

CONSTRAINT `rpush_notifications_event_id_fk`
  FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE

Next, I found here one interesting thing:
https://blogs.oracle.com/mysqlinnodb/entry/online_alter_table_in_mysql

  Online operation (LOCK=NONE) is not allowed in the following cases:

  when adding an AUTO_INCREMENT column,
  when the table contains FULLTEXT indexes or a hidden FTS_DOC_ID column, or
  when there are FOREIGN KEY constraints referring to the table, with ON…CASCADE or ON…SET NULL option.

so looks like we have a 3rd case

Consider adding a new index first, then DROP the old index.  DROP should be a lot faster than ADD.

Or, if that fails, don't drop the old index.


Query to know who paid more than 10k I have a table with payment informations, like this:

ID COSTUMER_ID DATEPAID     AMOUNT
1    1        2012-01-01      50
2    1        2012-02-01      100
3    1        2012-03-01      70
4    2        2012-03-01     6000
4    2        2012-09-01     3000
4    2        2014-04-01     8000
5    3        2013-03-01     9000
6    3        2013-08-01     3000

etc.
I need to know wich costumers paid more than 10k in a 12 months period. (in the exemple, only costumer 3 fits). Don't mind when, the only criteria is SUM(AMOUNT) > 10000 in any 12 consecutive months combination.

But i'm stuck on it. Any ideas?
I'm using SQL Server 2012.

One way to do this is do a select back to the table and sum the rows before the row, and then use that as an inner query to be able to use the sum in the where clause, something like this:

select *
from (
  select
    P1.ID,
    P1.CUSTOMER_ID,
    P1.DATEPAID,
    (select sum(P2.AMOUNT)
      from PAYMENT P2
      where P1.CUSTOMER_ID = P2.CUSTOMER_ID and
      P2.DATEPAID <= P1.DATEPAID and
      P2.DATEPAID >= dateadd(month, -12, P1.DATEPAID)) as PAID12M
  from
    PAYMENT P1
) X
where PAID12M > 10000

Example in SQL Fiddle.

This will return all the rows where the criteria matches, if you need only customer_id or first row or something like that, you'll have to make the outer query a little bit more complex.

SELECT COSTUMER_ID, SUM(AMOUNT) AS Total
FROM TableName
WHERE DATEPAID BETWEEN '20120101' AND '20121231'
GROUP BY COSTUMER_ID
HAVING SUM(AMOUNT) >10000


Postgres DB user permission I'm logged in as a user in postgres with PgAdmin tool in Linux connecting remotely but I don't think the user has enough rights. I'm a MySQL experienced user but not so much in Postgres. When I select a table when logged in as the user I get

An error has occurred:

  ERROR:  permission denied for relation acs_activities

how do I add my user "gainpm" to have access to all tables in "projop" database?

You should log in as the table owner (the user you used to create the tables) and then grant the necessary privileges to your user:

grant select on acs_activities to your_user;

If you want to do that for all tables, you can use:

grant select on all tables in schema public to you_user;

Or maybe you created them as the superuser, but your regular user should be the real owner, then you should do:

alter table acs_activities owner to your_user;

I figured it out. First I had to login as superuser

sudo -u postgres psql

then issue

ALTER USER myuser WITH SUPERUSER;