Multiple database servers for performance vs failover If I have two database servers, and I am looking for maximum performance vs high-availability, what configuration would be best?

Assuming the architecture is two load-balanced web/app servers in front of two db servers, will I be able to have both db servers active with synced data, with web1 to db1, web2 to db2 setup? Is this active/active?

I'm also aware that the two db servers can have their own schema to manually 'split' the db needs of the app. In this case daily backups would be fine. We don't have 'mission critical data.'

If it matters, we have traffic around 3,000-7,000 simultaneous users.

If you are using mySQL, and you are not concerned about possible differences between your two databases, albeit for a short while, you could just set up mySQL Replication.

This is different to the active-active clustering you have asked about.  Active-active clustering implies that 2 or more nodes are actively hosting services, but in a mySQL / SQL Server context, you will not have explicit "synched" data in they way you mean. Instead this could be better achieved by replication or SQL2012 AlwaysOn (if you considered using it).


How to use variable and drop in test script? I have to modify certain Oracle Packages. Basically I will be extracting functionality and moving it to C.

They provided me with some "Test" scripts that I shoud be able to run to test that my changes don't break the underlying logic of the package. But they are not working.

Also I noticed they have hardcoded some values in their original scripts and I am trying to use variables instead. I have never worked with Oracle so I am confused.

In Toad I have a Script along these lines:

declare
myTestId VARCHAR(32);

begin

select  '23423DSSF34SD234' into myTestId from dual;

--Here my drop for my_test_table1 and my_test_table2
drop table my_test_table1;
drop table my_test_table2;

--Here create table my_test_table1 and my_test_table2

--Here I have a bunch of inserts

--Here are some selects statements

end;

However when I run the script it fails when it reaches the "Drop" statements it doesn't like them. How can I perform drop statements in my script?

I have been reading around and it seems that in Oracle it is not a good idea to create temporary tables like this? What would be the best approach?

drop, along with create, alter, truncate etc is a DDL (Data definition Language) statement. DDL is not allowed in PL/SQL blocks. Your script is a PL/SQL block (it has begin and end, along with variable declarations).

The way to work around it is to use execute immediate:

declare
myTestId VARCHAR(32);

begin

select  '23423DSSF34SD234' into myTestId from dual;

--Here my drop for my_test_table1 and my_test_table2
execute immediate 'drop table my_test_table1';
execute immediate 'drop table my_test_table2';

--Here create table my_test_table1 and my_test_table2

--Here I have a bunch of inserts

--Here are some selects statements

end;

Be aware that executing DDL in a PL/SQL block in this fashion will implicitly perform a commit. Any errors (such as ORA-00942: table or view does not exist) will also abort the script execution, though there are ways around this. You could first query the ALL_TABLES data dictionary few to check if the table exists before attempting to drop it, for example.

Variable use is massive subject... If you have a specfic question about it, you'd be better off asking a new question on the site.

As far as creating temporary tables are concerned, this is different than creating a true temporary table, as frequently used in Sybase and SQL Server - don't worry about it, your approach is fine for test scripts.


Insert if something changed other than specific column I have a table with many columns but my snippet is simplified. I have the trigger shown below but how can I have it so that nothing is inserted if only the created_at column has changed?

ALTER TRIGGER [dbo].[t_upd_insert]
ON [dbo].[Player]
FOR INSERT, UPDATE
AS
SET XACT_ABORT, NOCOUNT ON;

BEGIN
INSERT INTO dbo.Changes (
            [name]
            [created_at]
       )
SELECT
            ins.[name],
            ins.[created_at]
FROM INSERTED ins

I could do a WHERE name != ins.[name] for each column but there are a lot of columns and tables that need this; is there a shorter syntax?

EDIT
 I've tried adding

WHERE
      [name] != ins.[name] OR
      [age] != ins.[age]
      ....

but that doesn't work either. I thought that would be the naive method but that doesn't work.

ALTER TRIGGER [dbo].[t_upd_insert]
ON [dbo].[Player]
FOR INSERT, UPDATE
AS
SET XACT_ABORT, NOCOUNT ON;

BEGIN
IF (update(name))
BEGIN
  INSERT INTO dbo.Changes (
            [name]
            [created_at]
       )
  SELECT
            ins.[name],
            ins.[created_at]
  FROM INSERTED ins
END


Foreign key part of a composite primary key I have a table Room with primkey on column room_id and another table Items with primkey item_id. Each Item has a foreign integer key to room_id. When my program runs, it only looks at items in a single room so it would help if my primary key was on (room_id, item_id) in this order so that data is sorted on disk in this order.
Is this possible? Or do I need to duplicate room_id in Item and not have it as foreign key?

Your clustered index almost certainly should include RoomId for the reasons you correctly identify: it provides an efficient access path to all records for a given Room.
But there are other motivations are well.  If Item is a child of Room, then you probably have an identifying relationship like Room contains zero to many items.
This would naturally lead your primary key to be something like (RoomId,<columns that determine a unique item>).  So that could be (RoomId,ItemId)assuming that ItemId has some value beyond being a row pointer.
Otherwise it could be (RoomId,SerialNumber) or (RoomId,ItemType) - the exact answer depends on the data you're trying to store.


join the two tables which possess one to many relationship In drupal,the content type table and my own table is used to do some operations. Im having two tables namely Table1:'content_field_mem_id' &
                             Table2:'Ex'
description of my content_field_mem_id is

+--------------------+------------------+------+-----+---------+-------+
| Field              | Type             | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+-------+
| vid                | int(10) unsigned | NO   | PRI | 0       |       |
| nid                | int(10) unsigned | NO   | MUL | 0       |       |
| field_mem_id_value | int(11)          | YES  |     | NULL    |       |
| sid                | int(30)          | YES  | MUL | NULL    |       |
+--------------------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

The member Id values are inside the field_mem_id_value field.
Another Table Ex description is shown below :

mysql> desc Ex;
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| sid           | int(30)     | NO   | PRI | NULL    | auto_increment |
| mem_id        | varchar(30) | YES  |     | NULL    |                |
| nature_of_due | tinytext    | YES  |     | NULL    |                |
| amount        | int(30)     | YES  |     | NULL    |                |
| date          | date        | YES  |     | NULL    |                |
| paidby        | tinytext    | YES  |     | NULL    |                |
+---------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

The above table Ex doesnt contain any values.i.e)the Ex table has 0 rows. After posting operation by the admin Ex table will gets affected.

My aim is to show the view to the user using the Ex table.

The content_field_mem_id table consists 1000 numbers of member id which was imported by me and inserted into that content_field_mem_id table.

The relationship between this two table is one to many relationship i.e) Each member can pay any of the fees . But member fees is must so one member can have many transactions on the second table.

How to join the both tables and how to set the foreign key constraints?. I have set the foreign constraint for the mem_id ,it throws me an error not an syntax error.

I want to join the tables and the second table has to be taken for the view purpose and second table member id can be duplicated cos of many transactions.

The relationship appears to be between content_field_mem_id.field_mem_id_value and ex.mem_id
However, since content_field_mem_id.field_mem_id_value is an int and ex.mem_id is a varchar, you will need to cast/convert one of the columns as part of your join.

It would be more reliable (although slower) to convert the int column to a varchar.

You didn't say which DB technology you are using, so I can't show the exact syntax for the convert (it is very different between MS SQL/Oracle/DB2/etc).


Ship Standby Logs Without Datafiles When building a new standby database, is it possible to have it start shipping archive logs from the primary without having the datafiles on the standby yet?

Yes! we can do that.

Which can be observed from the following demo.

I have two hosts Server1(192.168.30.132) and Server2(192.168.30.142) and ORCl database is on Server1.

I skipped some of the steps, creating standby redo logs for example, and configuration to make the answer short.

On Server1

Enable archivelog

SQL> alter database archivelog;

Database altered.

Enable force logging.

SQL> alter database force logging;

Database altered.

Create standby controlfile.

SQL> alter database create standby controlfile as '/home/oracle/Desktop/orcl_stby.ctl';

Database altered.

SCP standby control file to Server2.

 [oracle@server1 Desktop]$ scp orcl_stby.ctl oracle@192.168.30.142:/u01/app/oracle/fast_recovery_area/ORCL2/controlfile/

SCP password file to Server2.

[oracle@server1 dbs]$ scp orapworcl oracle@192.168.30.142:/u01/app/oracle/product/11.2.0/db_1/dbs/

Set log archive config and log archive dest parameters.

SQL>ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcl2)';
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl2 NOAFFIRM lgwr ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl2';

On Server2

Make necessary directories.

$ORACLE_BASE/admin/db_unique_name/adump
fast_recovery_area/db_unique_name/controlfile
fast_recovery_area/db_unique_name/archivelog

Create initSID.ora filewith the following parameters OR you can create pfile on Server1 and SCP to Server2.

[oracle@server2 dbs]$ vi initorcl2.ora

db_name= orcl
db_unique_name=orcl2
compatible=11.2.0.4
control_files='/u01/app/oracle/fast_recovery_area/ORCL2/controlfile/orcl_stby.ctl

[oracle@server2 dbs]$ sqlplus / as sysdba
SQL> startup nomount

SQL> create spfile from memory;

File created.
SQL> shut immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size          2251816 bytes
Variable Size         159384536 bytes
Database Buffers       50331648 bytes
Redo Buffers            5189632 bytes
SQL> alter database mount;

Database altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcl2)';

System altered.

SQL> alter system set db_recovery_file_dest_size=500M;

System altered.

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/';

System altered.

On Server1

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

On Server2

[oracle@server2 archivelog]$ pwd
/u01/app/oracle/fast_recovery_area/ORCL2/archivelog
[oracle@server2 archivelog]$ ls
2017_02_22
[oracle@server2 archivelog]$ cd 2017_02_22/
[oracle@server2 2017_02_22]$ ls
o1_mf_1_36_dbtc52mg_.arc
[oracle@server2 2017_02_22]$

As long as LGWR/ARCn process on Primary and RFS process on Standby are running we can transfer archived logs without having datafiles in place on standby database.

On Server2

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS   STATUS      THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ----------- -------- ----------    ---------- ----------
RFS       IDLE          0      0             0           0


Trying to add another field for SSRS I have a union all that combines two statements for an SSRS report. I would like to create a third column that is based on a where clause from the table as the third field (col1, and col2 being the first two fields). Essentially I'm wanting this:

DECLARE @Parameter int = 0

SELECT col1,col2, 'here I would like to use the result of another select col3 WHERE some_condition FROM table 1'
FROM table 1
WHERE @Parameter int = 0

UNION ALL

SELECT col1,col2, 'same as above'
FROM table 1
WHERE @Parameter int = 1

You could try subqueries

However, a subquery that returns more than one value will fail

This works but you have to consider that it can only return one value, and it could break easily

DECLARE @table TABLE(col1 int,col2 int, col3 int, some_condition int)

INSERT INTO @table(col1,col2,col3,some_condition)
values(1,2,3,0),(3,4,5,1)

DECLARE @Parameter int = 0

SELECT col1,col2, (select col3 FROM @table WHERE some_condition = 1 ) as col3
FROM @table
WHERE @Parameter  = 0

UNION ALL

SELECT col1,col2, (select col3  FROM @table WHERE some_condition = 0 ) as col3
FROM @table
WHERE @Parameter  = 1

Result:

col1    col2    col3
1       2   5
3       4   5

5 will be returned for each col1 and col2 value, because parameter = 0.

This fails

DECLARE @table TABLE(col1 int,col2 int, col3 int, some_condition int)

INSERT INTO @table(col1,col2,col3,some_condition)
values(1,2,3,0),(2,3,4,0),(3,4,5,1),(6,7,8,1)

DECLARE @Parameter int = 0

SELECT col1,col2, (select col3 FROM @table WHERE some_condition = 1 ) as col3
FROM @table
WHERE @Parameter  = 0

UNION ALL

SELECT col1,col2, (select col3  FROM @table WHERE some_condition = 0 ) as col3
FROM @table
WHERE @Parameter  = 1

  Subquery returned more than 1 value. This is not permitted when the
  subquery follows =, !=, <, <= , >, >= or when the subquery is used as
  an expression.


Why is Include Actual Execution Plan not included in @@Rowcount when it produces a row count? While updating a column in a table the last day I was shown a new technique by our Senior DBA to reduce reads and contention.

The pseudo-code is

WHILE
  [always true condition]
BEGIN
  UPDATE [...]
IF @@ROWCOUNT = 0 BREAK;
END

However while running this, I had accidently left Include Actual Execution Plan on so in the Messages tab I was seeing repeating occurrences of this:

(1000 row(s) affected)

(1 row(s) affected)

However the code worked and the WHILE loop worked!

My question is, if including the Actual Execution Plan returns 1 row affected, shouldn't this create an infinite loop?

Why is it not getting counted?

Examples tables below:

USE [tempdb];
GO

CREATE TABLE [dbo].[UpdateMePlease]
([ID] INT IDENTITY(1, 1), [UselessNote] VARCHAR(10) DEFAULT 'Delete me!', [UpdateValue] VARCHAR(10) NULL)
GO

INSERT INTO [dbo].[UpdateMePlease] ([UselessNote]) DEFAULT VALUES;
GO 10000

SELECT * FROM [dbo].[UpdateMePlease];

/* turn Actual Execution Plan on here */

WHILE 1 = 1 BEGIN

UPDATE TOP (1000) [dbo].[UpdateMePlease]
SET [UpdateValue] = [UselessNote]
WHERE [UpdateValue] IS NULL;

IF @@ROWCOUNT = 0 BREAK

END

-- Clean up
DROP TABLE [dbo].[UpdateMePlease];

That DONE_IN_PROC message from the execution plan is completely independent of your WHILE loop, and isn't what's being checked in your conditional BREAK. Martin deleted his answer but this is very much by design. Your query shouldn't behave differently depending on whether or not your client tool is retrieving execution plans - that would be bad. Execution plans are for monitoring and reviewing, not for influencing actual behavior.

Note that the inverse is also not necessarily a given - not all things that will populate @@ROWCOUNT will lead to the DONE_IN_PROC message, e.g.

DECLARE @foo int, @x varchar(32) = 'hello';
SET @foo = @@ROWCOUNT;
PRINT @foo;

This will yield 1 in the messages pane, but you will not see 1 row(s) affected.

Now, I've always been a big fan of using SET NOCOUNT ON;, which renders this question rather moot. But it seems the impact of DONE_IN_PROC in modern versions is not what it used to be when I formed this opinion:

Performance Surprises and Assumptions : SET NOCOUNT ON


Converting Heap to MyISAM issue At work we have a problem that has now occurred more then once.

Problem is that query is in state of converting HEAP to MyISAM and it paralyzes our server( query in this state is not able to kill itself and kill flag is not evaluated until only after the operation which seem to take FOREVER in our case)

The following global MYSQL variables appear to us:

| tmp_table_size      | 17179869184 |
| max_heap_table_size |  8589934592 |

Any ideas what we should do?

Should we perhaps adjust the sizes of tmp_table_size and max_heap_table_size to have the same value?

Would that help?

You have tmp_table_size set to 16G and max_heap_table_size set to 8G. Way Too Big !!!
If you have temp tables that exceed 8G, you can do one of three(3) things
SUGGESTION #1
Use smaller values. Set tmp_table_size and max_heap_table_size to 16K. That way, the temp table will go to disk faster. BTW Yes, they should be the same value. If you don't set these small, this is what happens:

Temp Table Grows to 8G in memory
mysqld suspends the query processing
mysqld offloads temp table to disk (all 8G
mysqld resumes filling the temp table
mysqld continue query processing

CAVEAT: You may need to map disk-based temp tables to a separate disk. You can use tmpdir to point all such temp tables to that designated volume. The reason I am giving you a head-up on this ?
According to MySQL 5.0 Certification Study Guide

Page 408,409 Section 29.2 Bulletpoint 11 says:

If you run out of disk space while adding rows to a MyISAM table, no error occurs. The server suspends the operation until space becomes available, and then completes the operation.

Thus, you need tons of room for temp tables on disk. I have mentioned this before (See my old post "Site Offline" MySQL server failing to start and stop)
SUGGESTION #2
Tune other per-connection settings. If you setting join_buffer_size and sort_buffer_size, this can change the execution plan of the query for the better.
SUGGESTION #3
Tune your query. If you can change the query to take advantage of indexes or create indexes that will support the query you have, you can generate temp tables faster and, if possible, smaller.
UPDATE 2014-12-27 11:05 EST
You originally asked

Should we perhaps adjust the sizes of tmp_table_size and max_heap_table_size to have the same value?

Not necessarily. When used together, max_heap_table_size puts a cap on how large and in-memory temp table can be before offloading to disk. The only exception is when a table is create using CREATE TABLE ... ENGINE=MEMORY; For more information on this, please read MySQL Documentation on How MySQL Uses Internal Temporary Tables


Queries without Good Enough Plan Found I have a SQL Server 2012 database. I noticed value of Reason for early termination of statement optimization for some queries and all gave Good Enough Plan Found. Now my questions are:

What are all the possible types of  “Reason for early termination of
statement optimization”. I did searching for this in msdn but didn’t
get a complete list of values.
Is there a DMV or extended event to list all queries for which optimization was terminated due to reasons other than Good Enough Plan Found? I referred following two articles which does not list the complete list of possibilities. [Also, they give me different result in my database].

Finding: Query Compilation
Timeout
Identifying Query Plans that are not good
enough

Memory Limit Exceeded

The optimizer was forced to stop looking for better plan alternatives due to memory pressure. The reason for that should be investigated and corrected, then query compilation attempted again. The plan returned may very well not be the one the optimizer would have selected had the low memory condition not existed.
Time Out

This reason is much misunderstood.

The query optimizer aims to find a reasonable plan quickly. It does not perform an exhaustive search to find the best possible plan. By design, it avoids spending more time on optimization than necessary. One of these features that works to ensure this is the 'time out' (not a measure of time).

The optimizer sets itself an 'exploration budget' based on the complexity of the logical query, cardinality estimates, and the estimated cost of the cheapest plan found so far (if any). More complex queries with higher cardinality are given a higher budget.

If this budget is exceeded during one of its search phases, the phase ends. This is part of the design and normal operation of the optimizer. Queries that need more optimizer effort get it; ones that don't, don't.

Think of 'Time Out' as 'Good Enough Plan Found'.
Good Enough Plan Found

This means exactly the same as a blank reason. It is simply a historical quirk that plans with a cost below 0.909090... (1/1.1) are labelled this way. Nothing stops early or is otherwise specially handled or different within the optimizer code when this reason appears.

Aside from Memory Limit Exceeded, none of the 'early termination reasons' mean much (if anything at all) for query tuning or performance analysis. I generally ignore them.

Advice

Target query tuning efforts based on real performance metrics (elapsed time, CPU/memory usage, ... whatever is important in context). If a query is too slow for its intended purpose, spend time making it faster. Measure actual performance, compare it with the baseline and history, and target tuning effort at the important variances.

Store guaranteed-clean data in a proper relational schema, with useful statistics and indexes, and well-written, optimizer-friendly queries.


Statistics histogram AVG_RANGE_ROWS discrepancy According to MS docs, the description for AVG_RANGE_ROWS is:

Average number of rows with duplicate column values within a histogram step, excluding the upper bound. When DISTINCT_RANGE_ROWS is greater than 0, AVG_RANGE_ROWS is calculated by dividing RANGE_ROWS by DISTINCT_RANGE_ROWS. When DISTINCT_RANGE_ROWS is 0, AVG_RANGE_ROWS returns 1 for the histogram step.

I'm looking at the last line and if that is indeed the case, I'm curious to know why I'm seeing a value for AVG_RANGE_ROWS that is not equals 1 when DISTINCT_RANGE_ROWS is 0 in the histogram steps.

The statistic in question is a column stat created by SQL Server when the auto create statistics option is turned on. I'm on an older version of the database, but on the latest patch - SQL Server 2014 SP3, CU4+GDR (12.0.6372.1).
It is a bit unfortunate that we almost had a meltdown last week because of a sub-optimal query plan. The end result result was large scans and bloated memory grants. Resampling the statistic with a higher percent value sort of resolved the issue for us for the time being but I'm mostly curious to know if there are exceptions around the initial statement or a known issue (perhaps addressed using a trace flag?) and how do I prevent this from happening again for auto created stats where we have no control over the sampling size?

As described in answer to Badly-formed histogram causes bad estimates on Nested Loop there has been a change to the way sampled statistics are calculated and stored, more particularly when scaling is applied.
As a side-effect, the value for DISTINCT_RANGE_ROWS in your case is a fraction between 0 and 1 (980.235 / 386212.6 = 0.002538071). The column has an exposed type of bigint, so it is rounded down to zero.
Clearly there can't really be zero distinct values when the range contains a non-zero numbers of rows.
One can only hope these discrepancies are cleared up at some stage; though it's hard to imagine exactly what that might look like without a potentially breaking change of data type, extending also to sys.dm_db_stats_histogram (available on SQL Server 2016 and later).
As for what you do about it, if you are convinced this is not just a display issue and is actually causing poor estimations, you should report it as a regression.


How to select Data from ODBC Source in MySQL I try to select data from MSSQL in MySQL over straight way. I want to store the result in a view.
What did I do now:
Add the MSSQL Server as a ODBC Datasource.

MySQL won't do this. It doesn't have that feature. Sql Server, Oracle, Postgresql, and even MariaDB and MS Access all have the ability to create views which rely on remote databases of other types (including MySQL), but MySQL does not.
So, you could go the other direction, and let Sql Server create a view that uses MySQL data, but you cannot have MySQL create a view using data from Sql Server.
The only workaround is an ETL (Extract/Transform/Load) process in a cron job, the queries Sql Server and copies the data into MySQL table on some kind of schedule.


System SPID running for hours filling up the TempDB Log I've been trying to track down the source of my tempdb log filling up every few weeks where the tempdb log space used will continue to grow until it reaches 99% and stays there for hours while the log file grows.  After adding in some additional logging and email alerts, I think I identified the source of the problem, at least for this incident, not sure if the cause is the same in past incidents.
dbcc opentran in the tempdb reported this:
Oldest active transaction:
    SPID (server process ID): 47s
    UID (user ID) : -1
    Name          : sort_init
    LSN           : (41:3017:42)
    Start time    : Dec 19 2020 10:03:20:410AM
    SID           : 0x01
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The spid is 47s which I discovered is a system spid.  I ran sp_WhoIsActive with @show_system_spids = 1 which brought back data on the process.  After running sp_WhoIsActive over the course of an hour, tempdb_allocations ended up at 4,221,088, tempdb_current was 4,128,656, open_tran_count was 1 (throughout the entire incident) and sql_text was always null.  After running out of ideas, I ended up restarting SQL Server.  Several times prior to restarting SQL server, I issued a checkpoint in tempdb but that didn't help, likely since the transaction from the system process was still active.
The Start time from the dbcc opentran output is 10:03am and it was 1:00pm when I restarted SQL Server.  I ran dbcc opentran a few times over the course of an hour and the same output was returned each time.
In this particular case, the server was rebooted and it restarted at 10:02:54 -- 26 seconds before this long running system process started at 10:03:20.  Past incidents don't appear to have happened immediately after a system reboot. I don't have many details on past incidents, but at least the last time this occurred 9 days ago, in the sp_WhoIsActive output I collected (without @show_system_spids set to 1) there were no long running processes captured which likely means it was a similar system process running / filling up the tempdb log.  This 99% full tempdb log incident 9 days ago was ongoing for 7 hours.
Nothing stands out in the SQL Server log and Windows logs.  I'm running Microsoft SQL Server 2017 (RTM-CU15-GDR) (KB4505225) - 14.0.3192.2 (X64).
Is there a way to determine what a system SPID is doing?  Or any tips on how I can better troubleshoot this problem when it happens next time? The only clue from this incident is the Name value of sort_init in the dbcc opentran output which could be helpful but seems generic at the same time.

There are some newer DMVs designed specifically for troubleshooting what is happening in TEMPDB. Take a look at sys.dm_db_session_space_usage and sys.dm_db_task_space_usage. See this link for a discussion of those DMSVs


"sudo -u postgres psql postgres" does not ask for password I've taken over a system using postgres. When logging in, if I use ...

sudo -u postgres psql postgres

...it does NOT ask for a password. However, if I do ...

psql -U postgres -h localhost postgres

... it asks for the password.

Why?
How is this difference configured?
Can it be configured for a different user to login this way?

(NOTE: user postgres is NOT in the sudoers file, or in the sudo group.

Postgres has a type of authentication called peer. If you look at pg_hba.conf you may see something like

local   all             postgres                       peer

or like

local   all             all                            peer

The fact you did not get asked for the password has nothing to do with using sudo and everything to do with the fact that you were connecting to a DB that matched the user name and peer authentication was enabled in pg_hba.conf.

If this is a test machine and security is not needed you can use trust instead of peer and then passwords will not be needed. Can not stress enough, trust is only a good option for development and test machines. One should never, ever, use trust on a production  machine.


Insert statement sometimes runs slow and sometimes fast SQL Server 2014 we have an SSIS package that starts around mid night and does bunch of things. one of the insert statement in that package is causing slowness sometimes, runs for 90 minutes and sometimes runs in 42 or 20 minutes and during weekends it runs in 7 minutes.

I have checked for any resource contention, there is nothing going on except the database maintenance that runs in parallel and with the maintenance running  in parallel the insert statement sometimes runs fast.

Environment :
virtual
Windows 2012 R2
SQL 2014 Enterprise
Memory : 120Gb, max sql memory : 112GB
vCPU's : 10

Initially i thought the issue might be with the memory because of the PageIOLATCH_SH and PLE going down at the same time and after increasing the memory the performance is still bad.

I'm thinking the amount the records they insert might be different for each days but app team specified that they are almost same ?

Do you think is there any issue with the Vmware environment, i'm thinking may be something with VM NIC card limitation ? our infratructre team already checked the disk performance and it looks good.

Execution plan for the insert statement looks totally good.

Can anyone shed some light on this ?

You may have a disk bottleneck.  Run a Perfmon.exe recording specifically you want the individual "average disk read wait queue" and "average disk write wait queue" of all drives.  Perfmon is part of windows functionality and is on all Windows PC / servers / etc.  You will want a recording from a weekday and a recording from the weekend.
Are you running DBCC database integrity check on weekday nights?  While I want this running every night, it causes to much performance problems so to live in reality, mine only runs on weekends.


PostgreSQL - Removing Unused Index on Check Constraint column I used the script from this blog to identify the unused index on my database. It returns an index which is on a column called result where I have a check constraint on that column.
So removing this index will impact the check constraint performance?

will removing this index impact the check constraint performance?

No, it will not.
Check constraints only operate on the columns of a single row, not on multiple rows. So it is completely unaffected by the existence (or absence) of an index.


How to manage users and collections in MongoDB? Im triying to create users in a database in mongo, I want to lock the usage of te collections for some users, I'll give an example:

Lets say that I actually have a db with 3 collections:

Books, Students, Proffesors

And 3 users:

Librarian, SecretaryManager, Director

And I want the Librarian have r/w permissions just in Books, SecretaryManager r/w permissions in Students and Professors, and finally, Director that would have the same permissions as Librarian + SecretaryManager.

I've readed the documentation of mongo main website, im aware of using db.adduser, but I haven't find the way of doing this or I've miss something.

Need help, thanks in advance.

At the time of writing this (2.4 is the stable release) you would have to split up these collections into three databases instead.  The ability to do collection level permissions is not available, so you need to do it at the database level.  If you alter your model to have 3 databases (Books, Students, Professors), then you can add the relevant users to each database using db.addUser as you require.

However, if that is not possible for some reason, it is worth noting that collection level access control is slated to be a feature in 2.6 (the next major release) which is due soon (tm).  The usual caveats apply, nothing is truly in a release until you see the actual binary and confirm it, but this feature (as part of SERVER-1105) is already marked as committed and closed.  It is available for testing in the development releases right now.  The documentation (once complete) will be available at:

http://docs.mongodb.org/master/release-notes/2.6/#collection-level-access-control


MySQL - How do I Calculate an Average Value Over a Period of Time When Only Value Changes are Recorded I am tracking the sales price of various items, and I would like to calculate the average price of each item over a period of time.

My simplified tables look like this:

Items
------------------
|key  | title    |
------------------
|1    | Sproket 1|
|2    | Sproket 2|
------------------

And

Prices
----------------------------------------
|item_key | price | datetime           |
----------------------------------------
|1        | 2.99  | 2014-11-25 02:05:56|
|1        | 1.99  | 2014-12-13 02:05:56|
|2        | 9.99  | 2014-10-25 02:05:56|
|2        | 8.99  | 2014-11-13 02:05:56|
----------------------------------------

The Items table contains a single row for each item.  The Prices table contains a single row for each price change.  The script runs daily and checks for the current prices, if the current price is different than the most recent item price recorded in Prices than a new entry is made in Prices.

My question is, How do I calculate the average price of an item over 180 days?

I can't use AVG because there is not a price entry each day for each item.

KISS

Assuming daily means every day including weekends and holidays.

Insert price for all items everyday
Use AVG() for value
Delete prices over 180 days.


How to design a table for storing news items? I have a script that can get result from news websites and it will run every hour.

The php script will get the title and content.  The things I want to store in MySQL are:

  title
  content
  createTime (which is the time when I inserted the record)

As I will run the php script every hour I know there will be many cases where the same news will be found by the script.

How do I design a table that allows me to INSERT the new records, while previously existing news items are skipped?

A few things you will have to think about before starting:

How do you decide if a title is the same as a previous one?  Say there is an article 'A god table for storing news items'.  Your script discovers and stores it in your DB.  Later the editors of that news site notice the typo and correct the title as 'A good table for storing news items'.  What do you do in such a case?  (The same applies to the content.)
If you want to do something with your data after storing, you will need a unique identifier in the table.  Using this, you will be able to point to an item from other tables.
You may find useful to store the URL of the articles.  However, from time to time these can change just like titles do.
Short news are typically found in many places on the internet.  Both their title and contents are the same (like in the case of short news from news agencies) - are these the same or not?  If you store the URL, then these are different.  If not, these are the same.  What to do with these?
If you decide that you will store these from each site you process, than you will find useful to identify your source sites.  (Be careful, the URL will look like 'http://dba.stackexchange.com/questions/23568/how-to-design-a-good-table-for-real-time-news-extraction' and the site will be 'dba.stackexchange.com'.  Here the latter forms a part of the former, but this is not necessarily true for other sites.)  In this case, you will have to create another table where you list all the source sites.  This table will be referenced from your other table.

So far we have the following database structure:

Table "news_item"

+--------------+------------+--------------+---------------------+-----+-----------+
| news_item_id |   title    |   content    |     time_created    | URL | source_id |
+--------------+------------+--------------+---------------------+-----+-----------+
| 1            | First item | some content | 2012-09-03 22:53:14 | ... | 1         |
.                                                                                .
.                                                                                .
.                                                                                .
+--------------+------------+--------------+---------------------+-----+-----------+

Table "source"

+-----------+-----------------------+
| source_id | name                  |
+-----------+-----------------------+
| 1         | dba.stackexchange.com |
.                                   .
.                                   .
.                                   .
+-----------+-----------------------+

You can write your SQL script only after answering at least the above questions.


How to clean Archive logs Oralce 12c  ASM
How to clean Archive logs. Oracle Instance is NOT starting since Archive disk group is full, This is my TEST DB I don't want to extend the diskgroup so instead I would like to DELETE ALL archive logs.

SQL> startup
ORACLE instance started.

Total System Global Area  738197504 bytes
Fixed Size                  2928776 bytes
Variable Size             314576760 bytes
Database Buffers          415236096 bytes
Redo Buffers                5455872 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 31849
Session ID: 124 Serial number: 41214

Alert logs

> NOTE: Assigning number (1,0) to disk (/dev/DG-DISK3) SUCCESS: mounted
> group 1 (ARCH) NOTE: grp 1 disk 0: ARCH_0000 path:/dev/DG-DISK3 Wed
> Aug 24 03:50:11 2016 Unable to create archive log file '+ARCH' Wed Aug
> 24 03:50:11 2016 Errors in file
> /u01/app/oracle/diag/rdbms/asmdb1/ASMDB1/trace/ASMDB1_ora_338.trc:
> ORA-19504: failed to create file "+ARCH" ORA-17502: ksfdcre:4 Failed
> to create file +ARCH ORA-15041: diskgroup "ARCH" space exhausted ARCH:
> Error 19504 Creating archive log file to '+ARCH' Wed Aug 24 03:50:11
> 2016 Errors in file
> /u01/app/oracle/diag/rdbms/asmdb1/ASMDB1/trace/ASMDB1_ora_338.trc:
> ORA-16038: log 2 sequence# 143 cannot be archived ORA-19504: failed to
> create file "" ORA-00312: online log 2 thread 1:
> '+DATA/ASMDB1/ONLINELOG/group_2.262.917061333' USER (ospid: 338):
> terminating the instance due to error 16038 Wed Aug 24 03:50:11 2016
> System state dump requested by (instance=1, osid=338),
> summary=[abnormal instance termination]. System State dumped to trace
> file
> /u01/app/oracle/diag/rdbms/asmdb1/ASMDB1/trace/ASMDB1_diag_32746_20160824035011.trc
> Wed Aug 24 03:50:11 2016 Dumping diagnostic data in
> directory=[cdmp_20160824035011], requested by (instance=1, osid=338),
> summary=[abnormal instance termination]. Wed Aug 24 03:50:12 2016
> Instance terminated by USER, pid = 338

Start the instance in MOUNT mode, delete the logs, then open the database.

$ rman target /

RMAN> startup mount
RMAN> delete force noprompt archivelog all;
RMAN> alter database open;


Is it good to save Keywords stored by users in a new DB schema? My question is more from the schema design and of further performance of the DB.

I'm designing a DB schema which will store strings primarily. Actually, users work on these strings, as normal mortals, in macro-enabled Excel workbooks and there, they have one column specifically designed to store keywords so they can find words within a second column quickly.

Some of these words are obviously deduced. For example, if column 1 is the keywords column and column 2 is the text column, column 2 could say "information required for feature description" and, its keywords in column 1 would be "information", "feature" and "description".

So far so good, common sense, but there are texts that have, for example, "N", "S", "W", "E", as the four points of a compass but in the keyword column, instead of having "North", "South" and so on, the keyword for all is just "compass".

We could ask for a keyword change but it would be a little troublesome, so my question is, is it worth saving these keywords in the schema, whether there's a table for them or not, or is it better to get the keyword as the result of a query from the texts stored?

We know working with strings is difficult, would these affect performance in the future?

BTW, if it helps, I'm thinking of using PostgreSQL.

I would have 2 columns: one for the description and one for the keyword.
I would probably use MyIsam (depending on which Mysql version you're using) and use Mysql FullText search features. Innodb supports it now. I would test with both and see which ones is most performant.


Relation between Faculty, Student and Course tables What should be the relation between following tables. Confuse whether i create an enroll table with student and course or courses_assigned table using faculty and course and then create an enroll table with a foreign key of courses_assigned table.

This model is for an educational institute system, where users are students and faculty members. Courses are assigned to faculty members and students are enrolled in courses.

System will show courses and their instructors(faculty members) to each student which he or she has to study and faculty members will see record of all students of a particular section which he or she has to teach.

One Course can have many Sections.  "One-to-many".  That is handled via a course_id column in the Sections table.

Many Students take many Courses.  "Many-to-many".  That is handled by a many:many table with two columns: course_id and section_id.  Note that you can find the course_id from the Sections table.

Facilty:Sections is also many:many.

I don't know where you are going with "OneToOneField".  Generally it is not good to have two tables in a one-to-one relationship.

Students and Facilty, though both humans (I guess), do not seem to be related at all.  Well, maybe you have "student teachers"?


SQL Server - Is there a limit to the number of Distributed Availability Groups that a primary replica can participate in? The documentation has diagrams with availability groups taking part in multiple Distributed Availability Groups (AG 1, AG 2 and AG 3) take part in Distibuted AG1 ([AG 1, AG 2]) and Distributed AG2 ([AG 2, AG 3]).

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/distributed-availability-groups?view=sql-server-2017

Is there a limit to this?

The documentation says "As long as two availability groups can communicate, you can configure a distributed availability group with them".

The documentation also says "In other words, a primary replica can participate in two different distributed availability groups"

Is 'two' the max? Or just used for the purpose of illustrating that there can be more than one. Can a primary replica participate in three different DAGs?

The book "Pro SQL Server Always On Availability Groups" says that an availability group can be a member of more than one distributed availability group, it does not explicitly say two. https://link.springer.com/book/10.1007%2F978-1-4842-2071-9

You are misreading that document. In the document and image you are referring to - there are actually two distributed Availability Groups in the image you refer to. each one containing two Availability Groups.

A distributed Availability Group is distributed between two (and as far as I now - only two) Availability Groups.

You can have multiple secondaries in an Availability Group and they can span data centers, so depending on what you are trying to do, you may not need a Distributed Availability Group to do it.


Use pg_dump with postgis extensions? I'm using Postgres 9.4. I'm trying to dump a database (both the schema and the actual data) with the PostGIS extensions (set up using CREATE EXTENSION):

$ pg_dump prescribing -U prescribing -h localhost -Fc > prescribing.dump
Password:

But when I type in the db password, I see this:

pg_dump: [archiver (db)] query failed: ERROR:  permission denied for schema topology
pg_dump: [archiver (db)] query was: LOCK TABLE topology.topology IN ACCESS SHARE MODE

How can I dump this database?

UPDATE: it says these tables are owned by postgres:

 public | frontend_sha                               | table             | prescribing
 public | geography_columns                          | view              | postgres
 public | geometry_columns                           | view              | postgres
 public | pg_stat_statements                         | view              | postgres
 public | raster_columns                             | view              | postgres
 public | raster_overviews                           | view              | postgres
 public | spatial_ref_sys                            | table             | postgres
 public | vw_chemical_summary_by_ccg                 | materialized view | prescribing

Maybe I could just dump the other tables, and import them into another database set up in the same way? Or maybe I could run pg_dump as a superuser? But if I try pg_dump prescribing then it still asks me for a password, and I don't know what that should be.

What about using -T modifier for pg_dump to skip those tables?

For example -T topology.*


Find rows where column matches one of n strings I am trying to find the best way to see if multiple email addresses already exist in my database. I have a table with member id's, emails, and other info, and I need to import members but to check if members already exist. The amount of member that I will import will vary because it will be CSV files from users.

I dont want to make a really long "email LIKE  OR " ... etc.

I was thinking of making a temporary table and then union that table to my members table, but worried about performance. Any other ideas?

Additional Information:

I read in a lot of names and email addresses, and I need to check if those email addresses are already in the database. But instead of making numerous calls to the DB, or a really long where clause similar to "WHERE email LIKE '' OR email LIKE '' OR ...", I wanted to find a better way or getting emails that are either already in the database.

In the end, I did something like this for the where clause:

"email IN ('" . implode( "','", $inviteEmails ) . "')"

where $inviteEmails is an array of emails I am going to insert as users for the site.

You can use IN,

SELECT *
FROM t
WHERE email IN ('foo', 'bar', 'baz');


ALTER TABLE SWITCH statement failed. SWITCH is not allowed because source table '' contains primary key for constraint '' I'm using SQL Server 2008.

I have two tables PARTITIONTESTTABLE and PARTITIONTESTTABLESUB. PARTITIONTESTTABLESUB has a foreign key to PARTITIONTESTTABLE. I created partition and both of these two tables are partitioned. I also created staging table from Managing partition wizard.

When I run....

ALTER TABLE  PARTITIONTESTTABLESUB SWITCH PARTITION 10 to [staging_PARTITIONTESTTABLESUB];

...It runs to success.

But...

ALTER TABLE  PARTITIONTESTTABLE SWITCH PARTITION 10 to [staging_PARTITIONTESTTABLE];

...Failed with error:

  ALTER TABLE SWITCH statement failed. SWITCH is not allowed because
  source table 'Enterprise.MSH2.PARTITIONTESTTABLE' contains primary key
  for constraint
  'staging_PARTITIONTESTTABLESUB_PARTITIONTESTTABLESUB_FK'.

DDL:

CREATE TABLE [PARTITIONTESTTABLE](
    [pkcol] [int] IDENTITY(1,1) NOT NULL,
    [datacol1] [int] NULL,
    [datacol2] [int] NULL,
    [datacol3] [varchar](50) NULL,
    [partitioncol] [datetime] NOT NULL,
 CONSTRAINT [PK_PARTITIONTESTTABLE] PRIMARY KEY CLUSTERED
(
    [pkcol] ASC,
    [partitioncol] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

CREATE TABLE [PARTITIONTESTTABLESUB](
    [subpkcol] [int] IDENTITY(1,1) NOT NULL,
    [fkcol] [int] NOT NULL,
    [datacol1] [int] NULL,
    [datacol2] [int] NULL,
    [datacol3] [varchar](50) NULL,
    [partitioncol] [datetime] NOT NULL,
 CONSTRAINT [PK_PARTITIONTESTTABLESUB] PRIMARY KEY CLUSTERED
(
    [subpkcol] ASC,
    [partitioncol] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [PARTITIONTESTTABLESUB]  WITH CHECK ADD  CONSTRAINT [PARTITIONTESTTABLESUB_FK] FOREIGN KEY([fkcol], [partitioncol])
REFERENCES [PARTITIONTESTTABLE] ([pkcol], [partitioncol])
GO

ALTER TABLE [MSH2].[PARTITIONTESTTABLESUB] CHECK CONSTRAINT [PARTITIONTESTTABLESUB_FK]
GO

Correct. You can't do a switch out if there's another table which is dependent on the switching table with a FK.

Imagine that you have PK values 1,2,3, and a foreign key into it. Now you want to switch out value 3, so it'll have to check that 3 isn't mentioned in the FK table - that's a large amount of work, and a lot more than the metadata operation that partition switching is meant to be.

...so it's not allowed. Do it without FKs.


Is there a way to see completed process list in mysql (SHOW FULL PROCESSLIST) I am new to Mysql server, I use the below query to see what are all processes or queries that users are currently executing.
SHOW FULL PROCESSLIST;

I needed to check those processes, which users executed in past. like those processes which were executed and they got completed or aborted.
Is there a way to check the already executed processes via any query in MysqL server ?

In MySQL, queries executes in the past can be seen in the general log.
See the reference manual:
https://dev.mysql.com/doc/refman/8.0/en/query-log.html


How to interpret Azure SQL QueryStore Query Performance Insight The Azure SQL QueryStore is supposed to give you insight into what is causing high DTU usage, but I can not figure out what is going on.
In some cases, I get good data out of it, and I can pinpoint to the offending query, but often I come across a situation like below:

So here we have a peak of 45% DTU usage, all caused by CPU, yet the total of the top 20 queries is < 10%. So what is causing the other 30% of DTU usage?

It’s possible that CPU spike was short, let’s say for 5 minutes reached 90%. Each bar represents a query that runs for 1 hour.
So, in this case, you will see, one bar taking 5/60 * 90% ~ 7.5%

QPI will show you most significant queries in the observed interval, and time grain is one hour.  If query spiked and its significance is not big enough to get into TOP 20, it won’t be shown.

You can change the granularity of monitoring chart (DB Blade, Monitoring Chart, "Edit chart") to be 1 minutes and check if the spike was short.

Hope it helps,
Andrejs


Query with order by, group by and limit I have two tables: chains and documents.

CREATE TABLE `chains` (
  `chain_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
...
  PRIMARY KEY (`chain_id`),
...
) ENGINE=InnoDB

CREATE TABLE `docs` (
  `doc_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `chain_id` int(10) unsigned NOT NULL,
  `id_status` tinyint(3) unsigned NOT NULL COMMENT 'open,sent,inbox,read,etc',
  `date_created` int(10) unsigned NOT NULL DEFAULT '0',
...
  PRIMARY KEY (`doc_id`),
  KEY `id_status` (`id_status`),
  KEY `family` (`family`),
  KEY `chain_id` (`chain_id`),
  KEY `date_created` (`date_created`),
) ENGINE=InnoDB

One chain contains one or many documents.

I need to find all documents ordered by date_created and grouped by chain_id with limit.

SELECT `chains`.`chain_id`
FROM `chains`
JOIN `docs` USING (`chain_id`)
WHERE `docs`.`family`=1
AND `docs`.`id_status` IN (4,5)
AND `chains`.`is_archive`=0
GROUP BY `chain_id`
ORDER BY `date_created` DESC
LIMIT 0,200

Query time: 36.01 sec

EXPLAIN result:

+----+-------------+--------+------+---------------------------------+------------+---------+--------------------------+---------+----------------------------------------------+
| id | select_type | table  | type | possible_keys                   | key        | key_len | ref                      | rows    | Extra                                        |
+----+-------------+--------+------+---------------------------------+------------+---------+--------------------------+---------+----------------------------------------------+
|  1 | SIMPLE      | chains | ref  | PRIMARY,is_archive              | is_archive | 1       | const                    | 2789080 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | docs   | ref  | id_status,family,chain_id,test2 | chain_id   | 4       | for_test.chains.chain_id |       1 | Using where                                  |
+----+-------------+--------+------+---------------------------------+------------+---------+--------------------------+---------+----------------------------------------------+

How to optimize this query, or may be I need to redesign my tables?
May be some index can help me?

There are various ways to rewrite your query, so it uses indexes that cover it. For example:

SELECT d.chain_id
FROM docs AS d
WHERE d.family = 1
  AND d.id_status IN (4,5)
  AND EXISTS
      ( SELECT *
        FROM chains AS c
        WHERE c.is_archive = 0
          AND c.chain_id = d.chain_id
      )
GROUP BY d.chain_id
ORDER BY MAX(d.date_created) DESC
LIMIT 200 OFFSET 0 ;

or this:

SELECT d.chain_id
FROM docs AS d
  JOIN ( SELECT c.chain_id
         FROM chains AS c
         WHERE c.is_archive = 0
       ) AS c
    ON c.chain_id = d.chain_id
WHERE d.family = 1
  AND d.id_status IN (4,5)
GROUP BY d.chain_id
ORDER BY MAX(d.date_created) DESC
LIMIT 200 OFFSET 0 ;

In combination with proper indexes that will "cover" the query. I'd start by adding these and testing for efficiency:

docs
(id_status, family, chain_id, date_created)

chains
(id_archive, chain_id)


SQL Server mirrored backup using cluster shared volumes I have SQL Server 2014 cluster with 2 nodes and a third server with iSCSI target server configured on it, all of them are virtual Servers with Windows Server 2012 R2.

Both nodes of cluster are iSCSI initiators and connected to the iSCSI target which was converted to Cluster Shared Volume(CSV).

I'm using Ola Hallengren scripts to make backups, the idea here is backup to local Z:\ drive and mirror to C:\ClusterStorage\BackupSQL (this folder is the cluster shared volume), backup run successfully on each location by separate, but if I configure the script to use Mirror Device, it gives me an

  Error 3212 The mirror device "%ls" and the mirror device "%ls" have different device specifications.

The block size in both drives C: and Z: is the same, tested with the command:

fsutil fsinfo ntfsinfo <drive:>

After the job runs unsuccessfully the folder structure was created, but no backups are inside, what am I missing?

This works:

BACKUP DATABASE [msdb]
TO DISK = N'Z:\MSSQL\Backup\some_path_and_name.bak'
--MIRROR
--TO DISK = N'C:\ClusterStorage\BackupSQL\some_path_and_name.bak'
WITH CHECKSUM, COMPRESSION, FORMAT

This also works:

BACKUP DATABASE [msdb]
--TO DISK = N'Z:\MSSQL\Backup\some_path_and_name.bak'
--MIRROR
TO DISK = N'C:\ClusterStorage\BackupSQL\some_path_and_name.bak'
WITH CHECKSUM, COMPRESSION, FORMAT

This NOT work:

BACKUP DATABASE [msdb]
TO DISK = N'Z:\MSSQL\Backup\some_path_and_name.bak'
MIRROR
TO DISK = N'C:\ClusterStorage\BackupSQL\some_path_and_name.bak'
WITH CHECKSUM, COMPRESSION, FORMAT

the last one gives me the Error 3212

I hope this is Partial answer and this might helpful to you.

Clustered Storage: clustered file system is a file system which is shared by being simultaneously mounted on multiple servers. There are several approaches to clustering, most of which do not employ a clustered file system (only direct attached storage for each node).

Local Drive Z: Microsoft says "drive", what they actually mean is a partition - a formatted block of space on an existing physical drive. In your case, the "Z" partition is most likely the EFI System Partition (ESP) which holds the boot information for the OS.

Cluster Storage and Local Drives are different from each other.

Hardware Requirements for Backup Mirrors:

Multiple-storage servers working together to respond to multiple read and write requests. clustered file system is a file system which is shared by being simultaneously mounted on multiple servers.

Mirroring applies both to disk and Storage file system (disks do not support continuation file system). As in earlier versions of SQL Server, all backup devices for a single backup or restore operation must be of the same type, disk or tape.

Within these broader classes, you must use similar devices that have the same properties. Insufficiently similar devices generate an error message (3212). To avoid the risk of a device mismatch, use devices that are equivalent,such as only drives with the same model number from the same manufacturer.
or use same Cluster storage file system for Backup and Mirror paths with different folders.

This might work:

BACKUP DATABASE [msdb]
TO DISK = N'C:\ClusterStorage\BackupSQL\\some_path_and_name.bak'
MIRROR
TO DISK = N'C:\ClusterStorage\MirrorBackupSQL\some_path_and_name.bak'
WITH CHECKSUM, COMPRESSION, FORMAT


Ge the value of id in master table and supply it to other tables I have 5 tables, of which one is master table and other 4 are the tables with have the foreign key of the id of the master table

I have to create a procedure which INSERT's data in all the tables including the Master table, I have to keep the id of Master table incremental.
So how should I get the value of the Master table row and supply the id of it in other tables in that procedure, WITHOUT using SELECT query(to get the id of Master table row) or WITHOUT supplying manually the id of master table in my program.

I want all of it to be handled it on database side.

I am using PostgreSQL

Please help me in it.

Assuming the ID in the master table is generated (e.g. because it's defined as generated always as identity or serial) you can do those inserts in a single statement:

with new_master as (
  insert into master_table (column_one, column_two, column_tree
  values (1,2,3)
  returning id --<< makes the generated ID available
), new_t1 as (
  insert into table_01 (master_id, some_column, other_column)
  select id, 'some_value', 'other_value'
  from new_master
), new_t2 as (
  ... as as above
)
insert into table_04 (master_id, some_column)
select id, 'another_value'
from new_master;

You don't really need a stored procedure for that, but you can put the above statement into one if you really want.


Why is my @@Version unchanged after upgrading? I have SQL Server 2008 R2. I installed SQL Server 2014 with the option:

  Upgrade from SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 or SQL Server 2012

and the process ended correctly.

But when running on localhost:

SELECT @@Version

I still have:

  Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64)

Sorry if the question is basic but I am very lost at the moment.

I found the problem: The installer I had was just for SQL Server Management Studio, as @MarkSinkinson pointed out.

If you have this same problem, verify you have the correct installation file (not SSMS only, or an incompatible Edition).

Other helpful things to check, from comments originally left on the question:

You may have multiple instances installed, upgraded one of them, and connected to another. Or the upgrade didn't take (this can be easy to dismiss without noticing in the final step of the upgrade wizard). Please note that 2008 and 2008 R2 are completely separate, independent releases.
Make sure that you are connected to right instance in any case.
Check you didn't uncheck the database engine installation option


SQL Server BULK INSERT I have a file saved using BCP and I want to know the specification of the table of the database that it needs to be materialized on the database. Is that possible?

Thanks

You can do this using the BCP format parameter. This will create a format file of the table for you.

Based on your requirement, you can create XML/native/character formatted files.

eg: bcp db_name.schema_name.table_name format nul -T -n -f format-file.fmt


Failed to notify 'DBA Team' via email I setup a test job to test out my failure notifications and now I'm getting the following error, stating it did not notify my operator via email. The strange thing is I ran the same exact setup script on several other instances and it worked just fine, but is failing on this instance. I checked the properties for SQL Server Agent and the operator itself. I also sent myself a test email from the Management branch's Database Mail and it worked successfully.

  Failed to notify 'DBA Team' via email.

Ensure you've enabled Mail Session in SQL Server Agent.

SQL Server Agent > Properties > Alert System: Check Enable mail profile.

This link should help:
Avoiding failed notified operator email errors


MS Access: How to automatically fill in ID field based on value of another field I have an MS Access inventory database where I imported some data about our food purchases from Excel. In Excel, the Vendors were simply listed in a "Vendor Name" column, which looks like this:

Vendor name
------
Bearded Lady
Blue Heron Bakery
Calliope Farm
Cash and Carry
...

In my Access database, I have a table called "Vendors" that looks like this:

ID  Vendor Name
1   Bearded Lady
2   Blue Heron Bakery
3   Calliope Farm
4   Cash and Carry

The "Food purchases" table in Access looks like this:

ID  Item    Vendor ID   Vendor  Orderer ID  Orderer # of units purchased    Purchase unit   Price   Price per unit
2   Agave       Earthly Gourmet     bulk (wet)  5   lbs $25.00  $5.00
3   Allspice        OGC     bulk (spices)       3
4   Anise seed      OGC     bulk (spices)       3

Right now the "Vendor ID" field is blank, and I want to use the values in the "Vendor name" field (Earthly Gourmet, OGC, etc) to automatically fill in the correct values, based on matching the name to the corresponding key in the "Vendors" table, instead of having to manually type in the correct ID number for each vendor ... How can I do this?

Assuming that you know how to edit and run a SQL query in Access, I think you need to execute a SQL Query similar to the following:

    UPDATE FoodPurchases
    INNER JOIN Vendors ON FoodPurchases.VendorName = Vendors.VendorName
    SET FoodPurchases.VendorId  = Vendors.VendorId

I have not verified the above, so let me know if it does not work.


Modeling a Recursive Association for Linking an Entity Type to Itself I am designing a simple database as follows:

The database should contains an evolving set of standards;
each standard has a library of controls;
A single control in each standard may map to one or more controls in another standard;
A control may only belong to one standard. However, a standard may have many controls;
A control must belong to a standard.

A rough sketch of the entity-relationship diagram (ERD) I have come up with is as follows:

Is this a correct conceptual schema for such a data model?

Using the "crow's foot" notation that @MDCCL has mentioned, the OPTIONALITY can be expressed by using solid and broken lines, respectively (solid -> MUST/mandatory, broken -> MAY/optional).  E.g. one of your business rules is: "A control must belong to a standard." (5th bullet point of your question), then the line (relationship) going from CONTROL to STANDARD starts solid (notice that the - same - relationship STANDARD -> CONTROL starts with a broken line).

Also, a relationship should "work both ways (!)".  The crow's foot notation allows us to label both ends of a relationship, e.g. we if it is correct to say that a STANDARD is constrained by one or more CONTROLs and each CONTROL constrains a STANDARD, then the first version of our ERD could look like the one below (the label text "has" is a bit vague ... try to be more specific/descriptive).

The "crow's foot" notation also shows the CARDINALITY very clearly e.g. you have modelled the second business rule "A single control in each standard may map to one or more controls in another standard", as 2 single solid lines, labelled "mapsTo" (one to one?, mandatory?).  Using crow's foot notation, the fact that there is an optional one-to-many relationship between CONTROLs can be drawn like in the ERD below.
(NOTE: I'm not claiming that the relationship labels I have used are correct for your scenario.  It's just to show you that e.g. "has" is too vague.  We know that the relationship "has" or "is", otherwise it would not exist in the model.)

Some people recommend using singular nouns (STANDARD, CONTROL instead of STANDARDS and CONTROLS) in ERDs.  Maybe this makes finding the cardinalities easier.


MySQL/Percona Server is taking long and I'd prefer to skip all this waiting even if it means data loss Originally posted to https://www.facebook.com/groups/GNUAndLinux/permalink/10155020539805019/ but no replies received.

I run Percona Server 5.6.21-70.0-688 x64 on Ubuntu Server 12.04 LTS x64, 64GB RAM, 2x2TB SATA* in hardware RAID0 and Intel Xeon E5-1620 v2 3.70GHz. InnoDB/XtraDB is the primary engine (with Barracuda format), with 20GB (21 after recent change) buffer pool size divided to 20 (21 after recent change) pools. The zlib compression is maximized to 9.

The Database Servers and its threads are dropped to nice -20 and ionice -c 1 -n 1.

Almost every Server on rautamiekka.org depends on MySQL databases, especially Minecraft.

I found out how to make a perfect list of IPv4 addresses into a text file. I created a table with auto-increment column and 1 column for each address section, to maximum of 5 columns in total. I made a Python script that started a transaction and explicitly disabled auto-commit and uploaded those addresses. The script had uploaded so many addresses from the 115GB file it had about 51GB of data in, likely already compressed. Then Percona Server 5.6.21-70.1 came out. I thought I'd run the routine update process cuz I thought there'd be no problems so I shutted down Minecraft Servers and any other Server depending on MySQL and forgot about the Python script. However, after fighting with aptitude and apt-get for few minutes I realized Database Server was still running and not accepting any connections since it was the in the shutdown process. I noticed the Python script had quitted by way or another.

That was Monday 2014-11-24, and the many processes/threads have been running since then, at 20-50KB/s (read) or even 200, and 120-500KB/s (write). It's mainly 1 that runs read and 2 that run write, with 1 process running at least twice as fast as the second, and only 2 processes, mainly, use CPU, but they're not the only ones. I presume it's committing the data into the table and reading the compressed data to verify it.

My estimations say the shutdown will take 9 days if we assume it writes 80GB at 120KB/s at minimum, and I warned ppl it might not be ready until Monday 2014-12-08 although it's been running for days now.

I made changes to the config just recently:

[mysqld]
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1

I've had

[mysqld]
innodb_fast_shutdown = 0
innodb_flush_method=O_DIRECT

for ages to ensure InnoDB will write everything before going down so we'll have smaller chances of data loss, but due to that we've had to wait for ages, and probly have to for at least a week, which I hate.

For reference, I posted a change request to MySQL bugs asking for changing the way MySQL handles order to shut down to prevent this kind of downtime: http://bugs.mysql.com/bug.php?id=75110

What can I do here ? I can consider every method to bring the process down, even loss of data if the loss ain't big, particularly if I lose just a portion of the IPv4 cuz I'll use MySQL script to continue filling up the table. However, I'm afraid I could lose something else, which is almost not an option, but I'll see.

Unfortunately, you have to let InnoDB purge all its moving parts (Redo Logs, Undo Logs, Dirty Pages, Index Changes, Log Buffer Contents, etc)
Going forward, I recommend the following : Set innodb_max_dirty_pages_pct = 0
By default, innodb_max_dirty_pages_pct is 75. If you set it to 0, it forces the InnoDB Storage Engine to flush every dirty page and its grandmother out of the InnoDB Buffer Pool without any pausing. It will either flush every page or flush it low enough so that new dirty pages enter the Buffer Pool as fast as old dirty pages are flushed out.
Flushing dirty pages and cleaning up transaction is what consumes the most time in a shutdown. Minimizing the number of pages shortens shutdown time.
You can monitor the following status variables

Innodb_buffer_pool_bytes_dirty
Innodb_buffer_pool_pages_dirty

Watch them decrease until it hits zero or levels off. Then, you can issue a shutdown.
Here are additional settings you could use

Set innodb_flush_log_at_trx_commit = 2
Set innodb_write_io_threads = 64

Give it a Try !!!
Afterthoughts
Don't worry about dumping the buffer pool map and reloading. The output file is very small.
SATA/RAID0 sound scary to me. You should be using SAS/RAID10 for InnoDB.
At the very least you think about splitting up InnoDB data, logs, and system tablespaces into separate devices. See my earlier post MySQL on SSD - what are the disadvantages?
You should also think about getting away from Barracuda. See my old post innodb_file_format Barracuda
UPDATE 2014-12-07 18:14 EST
If you want to interrupt an InnoDB purge of everything and you don't want to preserve the data being fixed up, your only option is to set InnoDB Recovery Options. See my post The InnoDB log sequence number is in the future. If you use the innodb_force_recovery options, it may not let you do any more writes of InnoDB. If you a moderate amount of data, you could mysqldump whatever data can come out, shutdown mysqld, delete all data folders (except mysql schema), ib_logfile0, ib_logfile1, and ibdata1, start up mysql, reload data.
I cannot really help you if you have 1TB in a forced recovered state.


Database Normalization If someone could guide me on helping me understand if my results are correct I would appreciate that. Let's say I have the following table:

BOOK (BookTitle, AuthorName, BookType, ListPrice, AuthorAffil, Publisher)

AuthorAffil refers to the affiliation of author. Suppose the following functional dependencies exist:

{BookTitle} -> {Publisher, BookType}
{BookType} -> {ListPrice}
{AuthorName} -> {AuthorAffil}

I am asked if this table is in 1NF, 2NF, or 3NF.

I believe this table is in 1NF because no two rows of data contain repeating information.

If this was to be a 2NF, it would have to look like:

R1 = {BookTitle} -> {Publisher, BookType, ListPrice}
R2 = {AuthorName} -> {AuthorAffil}
R3 = {AuthorName, BookTitle} -> {extras}

I would like some help to know if I am understanding this concept or if there are any mistakes. Any type of help is welcomed.

Let us address first normal form and second normal form distinctly.
First Normal Form (1NF)
You cannot say a table is or is not in 1NF because no two rows contain repeating information.  By repeating information you may be thinking that BOOK doesn't look like this:
BOOK { BookTitle, AuthorName1, AuthorName2, AuthorName3, BookType, ListPrice, AuthorAffil, BookPublisher  }

This really isn't the definition of 1NF.  Instead, BOOK is in 1NF only if
each column in every row will have a single value of whatever type, no matter how arbitrarily complex, that is defined for that column.  While adding multiple AuthorNames columns may be bad design, it does not violate 1NF as each contains the name of a single author.  Now what would violate 1NF is if we had an AuthorNames column and defined it to contain a comma separated list of author names.  In that case we know absolutely BOOK is not in 1NF.
Its also possible we have designed the table variable to be in 1NF, assuming only a single author name would ever be placed into the AuthorName column, but our users start putting in comma separated lists of authors.  Now the resulting table is not in 1NF even though we meant it to be.  In both these cases the table isn't even a relational table anymore because it no longer follows the discipline necessary to gain the properties of a relation.
Secondly, you cannot say BOOK is or is not in 1NF without the presence of at least one candidate key!  As stated right now, BOOK has no candidate keys and thus the same value is allowed to be entered for every column in two or more rows.  This would result in duplicate rows and a table with duplicate rows is not a relational table!
Second Normal Form (2NF)
It appears from looking at the attempt to decompose BOOK a candidate key of BookTitle,AuthorName was assumed.  Even if this assumption were correct, it is not correct to say the original table is now in 2NF.  Instead, of the 3 tables resulting from the decomposition, each must now be evaluated with respect to the functional dependencies.  R2 is in 5NF as its single non key column is fully functional dependent on the key and that key is the only candidate key and it is a single column.  R3 is in 5NF as its only non key column (the assumed "extras") are also fully functionally dependent on the single candidate key and there are no additional candidate keys.  It is only R1 that is now in 2NF but not 3NF as it has a non key column, ListPrice, that is functionally dependent on another non key column, BookType, and thus forms a transitive dependency.
The assumption of a single candidate key of BookTitle,AuthorName may also not be correct!  Perhaps in this particular book world every book of interest has only one author.  If that were the case then the creation of R3 is incorrect with respect to the actual functional dependencies.  Thus it is vital to clarify all the candidate keys and all the functional dependencies, join dependencies, and multi-value dependencies, with the subject matter experts and to never assume them.  Otherwise the wrong design results.  Assuming many authors per book when really there can be only one per book results in an R3 that allows the FD of BookTitle --> AuthorName to be violated.  Assumming one author per book when really there can be many per book results in a table similar to the original BOOK where users find they only have room for one author name, not the many they need, and they find their only choice is to enter sets of author names, delimited by something like commas or pipes.
References
Fabian Pascal's Practical Database Foundation Series and CJ Date's Relational Theory for Computer Professionals are excellent references for understanding what makes a table a relational table and the fundamentals of normalization.  It is from these sources that all the information in this answer was derived.


SQL Server Database Diagram in SQL Management Studio creating multiple schemas I'm using a multiple schema approach (Multi-Tenant arhitecture) in the same DB and it's working great. I can use EF bounded contexts and split domains appropriately.

  According to MSDN, multi-tenant architecture can be a shared server
  (separate dbs), shared database (separate schema), or shared schema
  architecture.

Currently i'm writing TSQL to get the DB design i want but I would like to know if there is a way to choose the schema before creating a new table when using Database Diagram tool in SQL Management Studio?

Database Diagrams is one of three Visual database tools.

After reading trough Visual Database Tools F1 Help, where there is no mention of choosing schema before creating a new table, which confirms that it automatically creates the table in the dbo schema.

To overcome this and still use Database Diagrams open your Table Properties window.

Create new schema:

Name your schema, define permissions etc.

Change schema for your table while in design view using Properties window.

For me this approach is still faster that writing scripts, i didn't change to different designer tool and it works.

Hope it helps somebody.


Run "USE" with linked server alternatives I am using SQL server linked servers and find some difficulties while running queries.

Below is the actual query.

USe testdb
SELECT 'Identitiy property OFF' where COLUMNPROPERTY( OBJECT_ID('Table_1'),'column1','IsIdentity')=0

To run it to a linked server I used

Use mylinkedserver.testdb
SELECT 'Identitiy property OFF' where COLUMNPROPERTY( OBJECT_ID('Table_1'),'column1','IsIdentity')=0

But when I run this I get error as shown below
Database 'mylinkedserver' does not exist. Make sure that the name is entered correctly.

Where as I can query the table by using select * from pc91sql.testdb.dbo.Table_1 successfully.

So how to run the above query?(I think i cannot make use of "USE" with linked server.So what is the alternative

EDIT:

These things i have tried after getting nice replies.
Then i used OPENQUERY option

SELECT * FROM OPENQUERY(pc91sql,'SELECT COLUMNPROPERTY( OBJECT_ID(''testdb.dbo.Table_1''),''column1'',''IsIdentity'')');

It returned NULL. But it should have returned 1 as column1 is a identity column.

Then I checked again running my original query directly in the linked server without use command and NULL is returned . If I run same query after I mention use testdb or in SSMS after choosing database from list then it returns 1 .So it means OBJECT_ID is not able to use database name along with table name(i.e like ''testdb.dbo.Table_1'').

You can use EXEC to run dynamic SQL in a different database or different server. Also please try to use the catalog views directly instead of all of these messy metadata functions.

DECLARE @sql NVARCHAR(MAX) = N'SELECT ''Identity property OFF''
      FROM sys.columns AS c
      INNER JOIN sys.tables AS t
      ON c.[object_id] = t.[object_id]
      WHERE c.is_identity = 0
      AND t.name = N''Table_1''
      AND c.name = N''column1'';';

EXEC mylinkedserver.testdb.sys.sp_executesql @sql;

Even better, parameterize it:

DECLARE @t SYSNAME = N'Table_1', @c SYSNAME = N'column1';

DECLARE @sql NVARCHAR(MAX) = N'SELECT ''Identity property OFF''
      FROM sys.columns AS c
      INNER JOIN sys.tables AS t
      ON c.[object_id] = t.[object_id]
      WHERE c.is_identity = 0
      AND t.name = @t
      AND c.name = @c;';

EXEC mylinkedserver.testdb.sys.sp_executesql
  @stmt = @sql,
  @params = N'@t SYSNAME, @c SYSNAME',
  @t = @t,
  @c = @c;


Table structure recommendation Looking for a advice on table(s) structure for MySQL schema.

Background:
I have several tables that can hold information about my product, customers and their purchases using CC/DC/direct banking etc. It's all working fine so far. Now, I'm planning to offer my customers an option to purchase store credit(using existing payment methods) and accumulate promotional credit by referring friends or using coupons while purchasing credit. Characteristics of credit include:

every credit has an accumulation date
every credit can be used to purchase products (unless expired)
purchased credit will not expire
promotional credit will have an expiry date

I already have a table for offers/coupons, their validity, benefits etc. to be able to offer any promotional credit.

Goals:

A table structure that can hold both type of credits(purchased as
well as promotional). I thought of this - see image.

A way to automatically deduct credits with closest expiry date first and then deduct from next accumulation if short of credits from one accumulation and so on...

Example: Customer has 50 purchased credits(no expiry) and has 15 (promo) credits expiring on 25th Sept. and another 20 (promo) credits expiring on 29th Sept. Now, let's say customer wants to buy a product worth 20 credits from the store. So, instead of deducting all 20 from his second promo accumulation, I'd want to deduct 15 credits that expire earliest from today and then remaining 5 from the ones expiring later (and of course not touching the purchased 50 credit yet).

Another table to hold all transactions from credits (kind of a
ledger)

Any ideas on this plan? I'd also appreciate any complex SQL statements involved for insert/fetch/update operations on the advised plan. I can myself take care of basic to moderate SQL statements (though I'm not very good at GROUP BY and UNION).

I'll give more information, if required.

Your structure looks fine, you would want to query in ascending order by expiry date and value. So if the customer wants to cash in 60 credits and the nearest date has values 10, 40 and 75, you want to "consume" the 10 and 40 and take the remaining from the 75. This makes it easier to use a brute force algorithm to use the soonest and smallest credits first.

This calculation would likely be better placed in the app rather than using SQL. The code would be fed a sorted list of records and the code would issue a series of 0 or more deletes followed by possibly one update.


Are some these indexes redundant? Let's say I have a table of 4 columns: A, B, C, D. A primary key is on A, B, C.

Index1: is A, D, B, C in that order
Index2: A, B, C, D in that order
and the primary key index: A, B, C

There are queries selecting different columns in different numbers and order. I don't all the queries that hit the table.

Are some indexes here redundant?

I collected some scripts from the web for finding redundant indexes and they don't return the same results for the database. I guess every author has their own interpretation of what constitutes a duplicate or redundant index.

Not quite.

If the PK is A,B,C, you already have that unique index.

A,B,C,D is a covering index for queries that need exactly those four columns, but would be better as A,B,C INCLUDE D

A,D,B,C is definitely not redundant; it's the only one that can work with both A and D on queries that use those two but don't use B or C in the index.  Depending on the cardinality of A and D, it might be critical, or worthless... but it's not redudant.

Remember, SQL Server starts at the left and goes to the right; when it hits a column that's not used, it stops.


How to model a database structure for exam dates and date ranges? I have one table Exam, and I want to store exam Date. Each Exam belongs to one of the Categories described below:

Category 1. For Exam A, there can be a Date once in a year, e.g.,  01-01-2017.

Category 2. For Exam B, there can be more than one Date during the same year, e.g., 01-01-2017 and 03-01-2017.

Category 3. For Exam C, there can be a Date range, e.g., from 01-01-2017 to 10-01-2017

Category 4. For Exam D, the Date can be chosen by a Student, e.g., the whole year, i.e., 365 days

My approach

I am saving the Exam.Date(s) in another table. I believe the relationship between Exam and Date is one-to-many. Said table would display data as follows:

Question

The problem with this approach is that, for Category 4, each Exam will have 365 rows, so, is this approach correct?

I think I would approach this as the exam-dates table wouldn't have an entry for category 4, since it is an open-ended exam.

Perhaps, if you need to know when student A took an exam under category 4, then add a column for the date taken along with the student and student's grade?

So you could have

an Exam table that lists out the exams and other data like course id, etc.
an ExamDates table that lists when exams are scheduled (and possibly other  values like building, room number, etc.)
a Students table with all of your student data.
a StudentExams table that links exams to students, (and through exams back to courses). This would have a student ID, an Exam ID, a date the test was completed, and a grade.

and probably some other tables like

a Courses table with data about the course
a StudentCourses table linking student to course
and so on...


Database table archiving I am trying to create a stored procedure (SP) in order to archive a table within my production database which is about 1TB.

The production DB runs on Linux SUSE OS and the destination location for the archive is Windows Server 2012.

The SP will recurrently archive 42 days of data from the production table to the  archiving destination table and clean up the production table from the archived data to free up space.

I really don't have the skills to write this stored procedure and also put in place a cron job or a script that will perform this task automatically.

Please anyone to help me out with the above?

You will need to create a foreign table in the Linux server that points to the Postgres running on the Windows server.

Then you can move the rows with an INSERT and a DELETE statement

insert into archive_table (col1, col2, col3)
select c1, c2, c3
from local_table
where created_at_column <= current_date - 42;: -- rows older than 42 days

delete from local_table
where created_at_column <= current_date - 42;

Where archive_table is the foreign table that "points"  to the Windows server.

The DELETE will probably be quite slow (the more rows you archive, the slower it will be), a faster way would be to partition the table e.g. by month and then detach old partitions from the table.


Missing Transaction log in chain, Possible to skip We have had one of our clients SQL Servers crash and need to restore it to the most recent possible time. We have a full backup from Monday and transaction logs that run every hour up until this morning at 02:01, however we are missing one transaction log from a database so we are only able to restore this database to Wednesday.

Is it possible to skip a transaction log and continue recovering the database to the most recent date with the remaining logs?

EDIT: there were no changes to the database done at the time we are missing, so the transaction log would have nothing in it

Is it possible to skip a transaction log and continue recovering the database to the most recent date with the remaining logs?
  EDIT: there were no changes to the database done at the time we are missing, so the transaction log would have nothing in it

Yes, only if there is NO gap in the Log Sequence Numbers of the log backups.

Below will explain you in more detail.

create database logbackup_test
go

-- take a full backup, else the database will be in pseudo-full recovery
backup database logbackup_test to disk = 'C:\sandbox\logbackup_test_full.bak'
with compression, stats =10

use logbackup_test
go
CREATE TABLE dbo.Table_1
       (
       Name varchar(50) NULL
       )  ON [PRIMARY]
GO

--Insert a 1st Row in TEST table
Insert into dbo.Table_1 values ('Kin-1')
GO
--Take 1st T-LOG backup.
BACKUP LOG logbackup_test TO  DISK = N'C:\sandbox\logbackup_test-TLog1.trn' with stats =10
go

--Insert a 2nd Row in TEST table
Insert into dbo.Table_1 values ('Kin-2')
GO

--Take 2nd T-LOG backup.
BACKUP LOG logbackup_test TO  DISK = N'C:\sandbox\logbackup_test-TLog2.trn' with stats =10
go

--Insert a 3rd Row in TEST table
Insert into dbo.Table_1 values ('Kin-3')
GO

--Take 3rd T-LOG backup
BACKUP LOG logbackup_test TO  DISK = N'C:\sandbox\logbackup_test-TLog3.trn' with stats =10
go

--

--Take 4th T-LOG backup -- NOTE That there is no activity .. no transactions (NO INSERTS DONE) !!!
BACKUP LOG logbackup_test TO  DISK = N'C:\sandbox\logbackup_test-TLog4.trn' with stats =10
go
/*Processed 0 pages for database 'logbackup_test', file 'logbackup_test_log' on file 1.
100 percent processed.
BACKUP LOG successfully processed 0 pages in 0.072 seconds (0.000 MB/sec).
*/

--

--Insert a 4th Row in TEST table
Insert into dbo.Table_1 values ('Kin-4')
GO
--Take 5th T-LOG backup
BACKUP LOG logbackup_test TO  DISK = N'C:\sandbox\logbackup_test-TLog5.trn' with stats =10
go

Check the data now :

Below will explain you why you can skip the log backup. It relies on LSN (Log Sequence Number). It is beyond the scope of this answer to go into detail about the LSN, but the link will give you a good idea of what it is.

RESTORE HEADERONLY will tell you the first LSN and Last LSN as below :

--- now start the restore

-- restore a full backup
restore database logbackup_test
from disk = N'C:\sandbox\logbackup_test_full.bak'
with norecovery, replace, stats =10
go

-- restore consequent log backups !! - 1,2,3 and 5. WE ARE SKIPPING TLog4 !!
restore log logbackup_test
from disk = N'C:\sandbox\logbackup_test-TLog1.trn'
with norecovery, stats =10
go
restore log logbackup_test
from disk = N'C:\sandbox\logbackup_test-TLog2.trn'
with norecovery, stats =10
go
restore log logbackup_test
from disk = N'C:\sandbox\logbackup_test-TLog3.trn'
with norecovery, stats =10
go
-- NOTE -- I am skipping Log4 as it did not have any transaction .....
restore log logbackup_test
from disk = N'C:\sandbox\logbackup_test-TLog5.trn'
with recovery, stats =10

Below is the result .. we are able to restore the log backup 1,2,3 and 5. We skipped Tlog4 as the LastLSN of Tlog3 was the firstLSN of Tlog5.


What should be the rule for excluding an FD from consideration while doing BCNF or any other Normalization? Let us consider this example:

http://www.mathcs.emory.edu/~cheung/Courses/377/Syllabus/9-NormalForms/examples.html

At the first decomposition step, it is using A -> B C D E.

On the next step, the same FD is not considered any more.

Is it because,

A -> B C D E has already been used up in a decomposition.

Or,

A -> B C D E is irrelevant to the new calculation.

Note: So, the answer is,  " A -> B C D E has already been used up in a decomposition."

The algorithm that decomposes a relation schema R to produce the BCNF operates in the following way: at each step, a dependency is searched that violates the Normal Form. If one if found (as A -> B C D E in the example), the schema is decomposed in two subschemas, one with the attributes of A+, and the other one with the attributes of the relation R minus all the attributes determined by A. Then the same steps are applied to all the schemas obtained, while there are other dependencies that violates the BCNF.

So, in the example, the original schema R is decomposed in the following two schemas:

R1 = (A B C D E F G H)  (with key A)

and

R2 = (A I J K L M)  (with key AIL)

In the next step, all the functional dependencies (projected over the subschemas) should be tested to see if they violate the BCNF, first for R1, then for R2.

But obvioulsy in relation R1, (which has key A, for the way in which it has been build), the dependency A -> B C D E does not violate the BCNF (that says that each dependency should have a superkey as determinant), so it is not used anymore to decompose the relation.

So, from this example you can see that the dependency used for the decomposition is not used in the later steps of the algorithm: its determinant is the key of the first of the two new relations produced, and for this reason that particular dependency will not used anymore, since it does violate the BCNF of the schema produced (while other dependencies in the schema could still be used to decompose, like in the example linked).


Continuing failovers on production server At least a few times a day, my main production server has recently begun failing over.  Only, the quorum doesn't move to the other node.

I ran the validation report, and got a lot of info I don't understand.  It is an HP ProLiant BL680c G5.  Some of the info in the report that I'm wondering about:

Degraded:
HpCISSs
HP MPIO DSM for EVA4x00/6x00/8x00 family of Disk Arrays
Link-Layer Topology Discovery Mapper I/O Driver
Mount Point Manager

Those are just a few.  I'm not so worried about "errors", but "degraded" seems to imply that it should be running but isn't doing so well.

System details:
sql - 10.50.4000.0
Windows - Windows NT - 64 Bit

It really is going to be awful if it IS an MPIO issue.  That has happened many times, and the DBA team has been accused of modifying the settings!  I'm the team lead, and even I have no idea what half of this means (I know what MPIO is, and I recognize all the SAN stuff, but troubleshooting? Nah.)

Interesting new information - right before this began happening, we updated the Firewall Service Module, and rebooted the core switches.

I'm thinking there are settings which don't have the correct defaults?

Logged during the failover:

  Cluster Agent: The cluster resource FileServer-(server)(Cluster Disk 1) has failed. [SNMP TRAP: 15006 in CPQCLUS.MIB]" "Cluster Agent: The cluster resource SQL Server Agent has become degraded. [SNMP TRAP: 15005 in CPQCLUS.MIB]" "Cluster Agent: The cluster resource SQL Server has become degraded. [SNMP TRAP: 15005 in CPQCLUS.MIB]" "Cluster Agent: The cluster resource Analysis Services has become degraded. [SNMP TRAP: 15005 in CPQCLUS.MIB]" "Cluster Agent: The cluster resource FileServer-(servername)(Cluster Disk 4) has failed. [SNMP TRAP: 15006 in CPQCLUS.MIB]"

it is really odd, because there aren't a lot of error messages. the only real info I have is from the validation report. Disks 1-4 always fail, but not logged in the same order, and then the quorum just stays on the node it is on.

After speaking with the network guy, he thinks that perhaps when the core switch was rebooted, it affected the preferred routes for the NICs.  He's going to clean things up a bit this weekend (removing the file shares and recreating them) and we'll see where we are. The adding/removing didn't work.  It failed over yesterday evening.

We are using fiber channel to connect between the servers and the storage array.  We just patched Windows (using Shavlik) and now we have the same issue.  I'm starting to wonder if it is some default setting in MPIO that keeps returning.

I'm not familiar with this particular error but I've encountered situations when a two-node cluster had multiple failovers due to MPIO issues with the SAN LUNs. More often than not, it was resolved by updating HBA drivers.

One other thing to look for is to ensure that the disk dependencies are properly set. The SQL Server service should be dependent on all that disks that are hosting the DB files and the backups as well as the disk with the drive letter acting as the mountpoint host. I've run into a few hosts where a missing disk dependency caused a disk to go offline before SQL could close out the DB files.


Execute new query when current query finalises I am in the process of inserting some data into a SQL Server (2016). Since this is a manual one-time load, I have not bothered spending a lot of time writing it the most efficient way; however some of the loads takes much longer than initially anticipated - so here is my question.

Can I somehow tell the server to execute a nother query immediately after the current one finalises? I would have done this manually by hitting F5 on the next part of the code - but since the load takes longer than expected, I would like the server to somehow execute the next part of the code automatically, when the first part finalises.

Is this at all possible without either 1) waiting for the current query to finalise or 2) cancelling the current query and re-running the whole lot at once including my next steps of my code?

Hope this makes sense!

Thanks :-)

Assuming it is just a single statement that you are waiting to complete, you can first run the following to get the start time of the current request

SELECT start_time FROM sys.dm_exec_requests WHERE session_id = <<spid_of_other_connection>>

And then having got the start_time do some simple polling in another SSMS window

SET NOCOUNT ON;

WHILE EXISTS
(SELECT *
        FROM    sys.dm_exec_requests
        WHERE   session_id = <<spid_of_other_connection>>
                AND start_time = <<start_time_from_above>>)
BEGIN
  WAITFOR DELAY '00:00:10'
END

PRINT 'DO SOMETHING HERE';

The filter on start_time rather than just session_id is because the session_id can be recycled if the original connection is closed.