Why does this simple select statement require quotes? Why does the following statement

select * from expertrule where rule_id = '1';

Return this error

ORA-00904: "RULE_ID": invalid identifier

While

select * from expertrule where "rule_id" = '1';

works!

The same goes for every column of the table (and only this table).  All the columns in this particular table require quotes surrounding their names.  Why?

  CREATE TABLE "TEST"."expertrule"
   (    "rule_id" NUMBER(*,0),
    "name" VARCHAR2(256 BYTE),
    "description" VARCHAR2(256 BYTE),
    "ruleaccess" VARCHAR2(4000 BYTE),
    "ruledisable" VARCHAR2(4000 BYTE),
    "userssn" VARCHAR2(256 BYTE),
    "departmentid" VARCHAR2(256 BYTE),
    "active" CHAR(1 BYTE)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TESTTABLESPACE" ;

You created your column with double quotes, so now Oracle requires you to always refer to this column using identifiers and respecting the case. If you remove the double quotes, Oracle always transforms the name to upper case, so without them it is case insensitive.

This will work if you created the column without identifiers:

where rulE_id
where RULE_ID

Also when creating a table, quotes are not required, unless you use reserved words.


Recover parameters for stored procedure from system logs I'm trying to debug an app, and I'd like to rule out database errors before tackling the code.

I know which stored procedure is responsible to handling the data I'm looking at, but there is no logging nor any traces set up on this DB. Is there a way to recover the parameters passed to that procedure, using existing system logs?

I've looked at:
dm_exec_procedure_stats and fn_dblog but they don't seem to have what I need.

I'm setting up logging moving forward.

There is no built-in logging mechanism that runs by default out-of-the-box to capture stored procedure parameter values.

Depending on the version of SQL Server you're using, the best way to capture that detail is via Extended Events (actual execution plans and looking at parameter runtime values. Pretty expensive though, and a bear to parse out via XML).

I suppose "best" is very subjective.  I don't think there is really a great, low-impact, way to get the actual parameter values for every execution of a stored proc through some SQL Server built-in mechanism.  It's probably easier to log that through the client, or via logging built into the stored proc.


Enabling InnoDB on an existing MySQL production server We have a MySQL 5.1.59 server that has 30-40 existing MyISAM databases. I need to deploy an application that requires InnoDB databases.

Can I just enable/configure InnoDB in /etc/my.cnf and restart MySQL, or is there more to this than meets the eye?

I should add that this is a MySQL environment built from source (running on CentOS 5.5 x64) and we plan to use the InnoDB plugin rather than the built-in InnoDB capability.

The plan is to follow steps as described on this page:

  http://dev.mysql.com/doc/refman/5.1/en/replacing-builtin-innodb.html

We also want to use innodb_file_per_table.

Apart from tuning parameters such as pool sizes and so on, is there anything else I should take into consideration?

InnoDB should already available. You can check with

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

or

mysql> show variables like 'have_innodb';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb   | YES   |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

Make sure skip-innodb is not in /etc/my.cnf

Since you are using the InnoDB Plugin, you need to activate new features to greatly enhance InnoDB Performance. For example, did you know there are new options for making InnoDB use multiple CPUs?  See my past posts on this:

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance

This is important because there are occasions when InnoDB in older versions of MySQL is faster than newer versions. I wrote about this as well:

Jun 19, 2011 : How do I properly perform a MySQL bake-off?
Oct 05, 2011 : Query runs a long time in some newer MySQL versions
Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)

Additional Reference from MySQL Performance Blog : http://www.mysqlperformanceblog.com/2011/10/10/mysql-versions-shootout/


How many partitions should I set for my MySQL table? I have a table which contains 3.5M rows of sales data (~4 years).

Currently some of my queries (done from php) take 6-15 seconds to complete.  (indexes are useful, as per explain plans). My next step is to partition the table to see if I can squeeze more out of it, without upgrading hardware during development phase of the application.

When the table is queried, at the very most a whole, single year's worth of data is retrieved, sometimes finer like QTR or month.

When the application is 'released', it is anticipated that the data in the table will grow, at a rate of about 900k rows per year.

This being the case. Should I partition only the amount of years I hold in the db currently? assuming that number can be changed down the line. Or should I create more partitions?

The thought which led me to my question is:  If I create more partitions than I have years, then queries will need to get data from multiple partitions, will the performance of this case be worse than if my query is only needing to select data from 1 much larger partition?

Do not PARTITION unless you have actions that would benefit from it.  Let's see your queries.  And, will you be purging 'old' data?

Rule of Thumb:  Don't even consider PARTITION unless there is more than a million rows.

Rule of Thumb:  Have 20-50 partitions; no more.

Do not create next month's partition until you are about to need it.  Keep an empty 'future' partition in case you forget to create the next one.

For real performance, use Summary tables.

More on partitioning.
More on Summary tables.

SHOW CREATE TABLE -- There may be some simple changes to make things more efficient.  Also provide a sample SELECT.


MySQL select values based on a sub query I have two tables , Table #1 => tbl_notification_flow has the  following information :

app_status
Booked
Notified
Missed
Defaulted

With the  app_status being the column name and Table #2 => tbl_appointment  has the following information :

id  app_status
1   Booked
3   Missed
4   Missed
5   Missed
6   Missed
7   Booked
8   Booked
9   Booked
10  Booked
11  Booked
12  Booked
13  Booked
14  Booked
15  Booked
17  Booked
18  Booked
19  Booked
20  Defaulted
21  Notified

With id and app_status being the  columns ,

I want to get a count of all the  app_status based on the  number of times they have appeared on Table #2 ,

I tried the  following query :

SELECT COUNT(tbl_appointment.id)AS total_count, tbl_appointment.`app_status`
  FROM tbl_appointment
 WHERE (tbl_appointment.`app_status`) IN
 (
   SELECT tbl_notification_flow.`notification_type` AS app_status
     FROM tbl_notification_flow
    WHERE tbl_notification_flow.notification_type != 'Other'
 )

It gets the following result :

total_count app_status
19  Booked

Please help with the  best query to get the  right results.

select
  t2.app_status,
  count(*) as status_count
from
  tbl_appointment t2
INNER JOIN
  tbl_notification_flow t1 ON t1.notification_type = t2.app_status
WHERE t1.notification_type != 'Other'

group by t2.app_status ;

with result:

Booked      13
Defaulted   1
Missed      4
Notified    1


VLDB is stuck in recovery state. It's a permission issue, now what should I do? SQL Server database which is very large (4TB) is stuck in recovery state. Reason: Data center had a planned downtime and we had to turn the physical machine off and when it turned on, as I read in error logs, SQL Server couldn't access the files, because files were on a SAN machine, and probably things didn't went smooth.

  FCB::Open failed: Could not open file E:\SomeFolder\SomeDatabase.mdf for file
  number 1.  OS error: 5(Access is denied.).

Now that I've made sure that SAN is up and running, and permission are OK, how can I tell SQL Server to continue recovering? Since this database is very large, I don't want to interrupt the course of recovering and start from the beginning. And also, any backup would take hours to complete.

SQL server service account does not have permission to access the file.

Navigate to the folder in which this file is residing
Grant Full control privilege on this folder to the SQL server service account.
Bring the DB Offline - Online or Restart SQL server services.


How to create identity field in MySQL without using auto_increment column? I am trying to create a dynamic system when a user can create different objects and relate them to another objects.

I need is to be able to generate an identity column similar to auto_increment but in an alpha numeric format.

The ids will be something like
001abcdeFGhJk123

Using a id like above will help me identify the object identity. For example I can say the first 4 characters are the object id and the last 14 charecaters are the record identifier.

How can I do this in MySQL. Or is it even possible in MySQL. I belive MS SQL server and Oracle database have field type of identifier that does the trick.

Thank you in advance

You have to use UUID() function that guarantee that returned string is unique not only within your table or database but all over the world.

http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_uuid

  A UUID is designed as a number that is globally unique in space and
  time. Two calls to UUID() are expected to generate two different
  values, even if these calls are performed on two separate computers
  that are not connected to each other.

Also there is UUID_SHORT() function that returns 64-bit INT instead of string, that is way faster for indexing and search.

http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_uuid-short


If no plans to uninstall, can Log and Update Cache folders in Setup Bootstrap folder be removed? I have several versions of SQL Server, used for testing, installed on my laptop (2012, 2014, 2016, and 2017). I noticed the other day that there was a folder containing prior versions of files across updates (SPs, CUs). Across all versions there is actually quite a bit of space being taken up:

(in C:\Program Files (x86)\Microsoft SQL Server\)

110\Setup Bootstrap\Log - 91.8 MB (818 files)
110\Setup Bootstrap\Update Cache - 608 MB (2,382 files)

(all folders below reside within: C:\Program Files\Microsoft SQL Server\ )

110\Setup Bootstrap\Log - 1.18 GB (3,715 files)
110\Setup Bootstrap\Update Cache - 9.58 GB (14,766 files)

120\Setup Bootstrap\Log - 569 MB (2,963 files)
120\Setup Bootstrap\Update Cache - 5.74 GB (12,797 files)

130\Setup Bootstrap\Log - 448 MB (2,808 files)
130\Setup Bootstrap\Update Cache - 3.84 GB (8,579 files)

140\Setup Bootstrap\Log - 84.3 MB (687 files)
140\Setup Bootstrap\Update Cache - 937 MB (1,571 Files)

Totals for those folders are (MB have been normalized to GB):

Folder          GB          Files
----------     -----        ------
Log             2.35        10,991
Update Cache   20.66        40,095

TOTAL          23.01        51,086

Initially I figured that it should be safe to remove the Log and Update Cache folders, but then I reckoned it best to first check to see if others have asked this and if so, what the answer(s) had been. I found this KB article:

Description of the Update Cache folder in SQL Server

which states:

  What happens if you remove this folder or delete its contents?

  If the Update Cache folder or some patches are removed from this folder, you can no longer uninstall an update to your SQL Server instance and then revert to an earlier update build. In that situation, Add/Remove Programs entries point to non-existing binaries, and therefore the uninstall process does not work. Therefore, Microsoft strongly encourages you to keep the folder and its contents intact.

Ok. I have no plans to ever downgrade or uninstall an SP or CU, so I should be ok, right? Well, there are other references, such as this one:

Is it safe to delete the "Update Cache" folder?

in which someone referenced the following KB article:

How to restore the missing Windows Installer cache files and resolve problems that occur during a SQL Server update

which states (emphasis added):

  When you install SQL Server, the Windows Installer stores critical files in the Windows Installer Cache (default is C:\Windows\Installer). These files are required for uninstalling and updating applications.

Now, this particular article seems specific to the C:\Windows\Installer folder, and I am not talking about deleting from that folder. Still, due to the mention of the possibility of not being able to apply updates, I wanted more info first.

Has anyone removed these folders before, or at least just their contents? If so, were there any negative effects? These folders only ever increase. I just applied the "Spectre / Meltdown" patches for all four versions and the totals increased by 3.65 GB and 6,900 files (the end result being the figures shown in the chart above).

To be clear, I am not looking for should I or shouldn't I remove one or both of these folders; I am looking for can I or can't I remove one or both (without breaking anything more than the ability to uninstall / downgrade, which I already accept as a consequence).

>> NOTE FROM O.P.: While I have accepted this answer, please be aware that it is not conclusive since extensive testing has not been done. It is possible that certain types of updates, or series of updates, might have slightly different requirements, etc. But for the moment, there is no evidence or information suggesting any different behavior. <<

This testing confirms that you can delete the contents of the Log and Update Cache within your Setup Bootstrap log folder and still be able to apply future CUs, GDRs, and SPs.

Phase 1:

As highlight the version of the SQL Server is 12.0.5000 (SQL Server 2014 SP2)
  1:

Next, I delete the contents of the Log and Update Cache. See screenshot below;

Phase 2:

Now, I run a CU (10) patch for SP2: https://www.microsoft.com/en-us/download/confirmation.aspx?id=53592 This should bring the version up to 12.00.5571 if successful.

The only thing i see here during the setup is that it does not pick up the current version for the instance. NOTE: I have seen this in multiple scenarios which is not related to this and setup did complete successfully. Now I am more interested to see if this completes successfully :P

Phase 3

Setup did complete successfully as I was expecting and SQL has been patched. Now lets do some few checks to verify the behavior.See screenshot for successful intall and noticed highlighted at the bottom the location of the new summary txt file for the patch.

First lets connect to SQL Server and observe the version. Per screenshot, Yes the SQL Server is now at 12.0.5571 as mentioned earlier in the test.

Now lets finally check what happens in the Log and Update Cache folders.

We notice that new folders and files are created for the patch that we run successfully. This is the expected behavior of a SQL setup.

It is valid to say that yes you can delete the contents of Log and Update Cache folders in Setup Bootstrap folder and be able to still perform future patching/upgrade on the SQL Server.

HTH someone :)


Why am I getting an error in execute immediate command in oracle 11g? I was just testing an anonymous code which I wrote for audit trail It's just a sample for test. However I am getting this error:

[Error] Execution (1: 1): ORA-00984: column not allowed here
ORA-06512: at line 29

Code:

DECLARE
    colName VARCHAR2(2000);
    colNewVal VARCHAR2(2000);
    sqlInsert VARCHAR2(2000);
BEGIN
   colName := 'BOOK_NAME,AUTHOR,PUBLISHER,PUBLISH_YEAR';
   colNewVal := 'BOOK1,AUTH1,PUB1,2001';
   sqlInsert := 'INSERT INTO TBL_TRN_AUDIT_TRAIL_MASTER(';

   FOR cur_var IN (SELECT REGEXP_SUBSTR (colName,'[^,]+',1,LEVEL) col_name FROM DUAL
        CONNECT BY REGEXP_SUBSTR (colName,'[^,]+',1,LEVEL)IS NOT NULL)
    LOOP
        sqlInsert := sqlInsert || cur_var.col_name || ',';

    END LOOP;
    sqlInsert := SUBSTR(sqlInsert,1,INSTR(sqlInsert, ',', -1)-1);
    sqlInsert := sqlInsert || ')VALUES(';
    dbms_output.put_line (sqlInsert);
    FOR cur_var2 IN (SELECT REGEXP_SUBSTR (colNewVal,'[^,]+',1,LEVEL) col_new_val FROM DUAL
        CONNECT BY REGEXP_SUBSTR (colNewVal,'[^,]+',1,LEVEL)IS NOT NULL)
    LOOP
        sqlInsert := sqlInsert || cur_var2.col_new_val || ',';

    END LOOP;
    sqlInsert := SUBSTR(sqlInsert,1,INSTR(sqlInsert, ',', -1)-1);
    sqlInsert := sqlInsert || ')';
    dbms_output.put_line (sqlInsert);
    EXECUTE IMMEDIATE sqlInsert;

END;

Line 29 is the execute immediate command.
Am I doing something wrong here??

Actually i sort of got the problem here. In the Insert into statement I was not giving proper quotes for the Values which are VARCHAR2. Got sorted now.


SQL Server 2012 installation silently fails I am trying to install SQL Server 2012 running setup.exe on 64-bit windows 8. After the "please wait" dialog, the installation center comes up. I chose "New SQL Server stand-alone installation", getting a new "please wait" dialog and then this window:

I press ok and comes to a dialog where the product key is input, I press next and accept the license terms, next again, leaves as it is, press next, the installation seems to start.

This should happen according to the text in the window: Scan for product updates, download setup files, extract setup files, install setup files.

After a while, the window dissapears and I get no message.

Nothing seems to be installed.

What really happened?

If this is RTM, I would recommend slipstreaming SP1 + the latest CU into setup before installing, and making sure you install from a local source rather than an ISO, network folder, external drive, etc.

To slipstream you can just download SP1 (likely SQLServer2012SP1-KB2674319-x64-ENU.exe) and/or a CU (CU3 is here; CU4 should be released any moment now) to a folder (say C:\SP1Updates\), extract the files (you should end up with .exe files like SQLServer2012-KBxxxxxxx-x64.exe and you can ignore or get rid of the source 4xxxxx_intl_x64_zip.exe files) and then run setup from a command line like so:

setup.exe /UpdateEnabled=1 /UpdateSource="C:\SP1Updates\"

Or you can download the slipstreamed ISO from here (they have slipstream in the name, just note they contain SP1 but no CU):

Microsoft® SQL Server® 2012 Service Pack 1 (SP1) (download link)

Some blog posts that go into greater detail about slipstreaming:

Installing SQL Server 2012 + Service Pack 1 + SP1 Cumulative update in one installation using SQL Server 2012 Product Update
How to create an SQL Server 2012 installer with integrated CU1 (Product Updates – Slipstream)
SQL Server 2012: Merged Product Updates (Slipstream)

You might also try the technique here, and you should be able to use slipstream here as well if you want (you'll just need to use a different PatchLevel, 11.00.3000.00 I believe):

SQL 2008/2008 R2/2012 setup disappears/fails when installing Setup Support files

In case that blog post disappears, I'm going to steal the content and paraphrase (since part of the instructions seem incorrect):

  Save the following in a .reg file and merge to populate the registry:

Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\Bootstrap]
"BootstrapDir"="C:\\Program Files\\Microsoft SQL Server\\110\\Setup Bootstrap\\"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\Bootstrap\Setup]
"PatchLevel"="11.00.2100.60"

  Next, copy the following files and folders from all of the media to:

 C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012

  Next, run setup from the new location, and it should proceed beyond the point of error this time.

(Note that I haven't tried this technique.)


How to store values of all columns in an index in PostgreSQL? As described here: https://devcenter.heroku.com/articles/postgresql-indexes#managing-and-maintaining-indexes  indexes do not store the entire row data themselves. Is it possible to store the whole row in the index for fast retrieve? I don't care about space.

Of course, you can create an index which would contain all columns from the rows.

From PostgreSQL 9.2 there is a possibility that this will speed up certain SELECT queries as from that version there are index-only scans.  The difference between the 'raw' table and this index will only show if the query picks only a part of all rows, based on the first column(s) of the index.  A WHERE clause containing conditions only about some later columns will most probably do a sequential scan, that is, it will go to the table itself as the rows to be retrieved will be spread across the whole index.

Also, maintaining such an index is quite expensive when it comes to DML statements - I would say that it doubles the number of writes for every such statement.


Error 297, Severity 19 trying to create new table as sysadmin I am completely scratching my head right now. I have two database instances that I am trying to add a table to, and both give me the same error:
The user does not have permission to perform this action.
and I have triple checked the permissions, I am on a sysadmin account. Running the following query to see what create permissions I have yields the following:
SELECT SYSTEM_USER

-- Now get the permissions assigned to you by the server administrator
use tempDB
GO

;with getPermissions as ( SELECT * FROM fn_my_permissions (NULL, 'DATABASE') )
select permission_name from getPermissions
where permission_name like 'create%'
GO

I have also tried creating another login with sysadmin privileges to no avail. Any ideas?
Error log picture below:

USE [db_test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table](
    [a] [int] IDENTITY(1,1) NOT NULL,
    [b] [nvarchar](50) NULL,
    [c] [decimal](18, 3) NULL,
    [d] [bit] NULL,
    [e] [nvarchar](50) NULL,
    [f] [nvarchar](10) NULL,
    [g] [nvarchar](500) NULL,
    [h] [int] NULL,
    [i] [int] NULL,
    [j] [int] NULL,
    [k] [int] NULL,
    [l] [int] NULL,
    [m] [int] NULL,
    [n] [int] NULL,
    [o] [int] NULL,
    [p] [int] NULL,
    [q] [int] NULL,
    [r] [int] NULL,
    [s] [int] NULL,
    [t] [int] NULL,
    [u] [int] NULL,
    [v] [int] NULL,
    [w] [int] NULL,
    [x] [int] NULL,
    [y] [int] NULL,
    [z] [int] NULL,
    [aa] [int] NULL,
    [ab] [int] NULL,
    [ac] [int] NULL,
    [ad] [int] NULL,
    [ae] [int] NULL,
    [af] [int] NULL,
    [ag] [int] NULL,
    [ah] [int] NULL,
    [ai] [int] NULL,
    [aj] [int] NULL,
    [ak] [int] NULL,
    [al] [int] NULL,
    [am] [int] NULL,
    [an] [int] NULL,
    [ao] [int] NULL,
    [ap] [int] NULL,
    [aq] [int] NULL,
    [ar] [int] NULL,
    [as] [int] NULL,
    [at] [int] NULL,
    [au] [int] NULL,
    [av] [int] NULL,
    [aw] [int] NULL,
    [ax] [int] NULL,
    [ay] [int] NULL,
    [az] [int] NULL,
    [ba] [int] NULL,
    [bb] [varchar](12) NULL,
    [bc] [varchar](12) NULL,
    [bd] [varchar](12) NULL,
    [be] [varchar](12) NULL,
 CONSTRAINT [PK_Table] PRIMARY KEY CLUSTERED
 (
    [A] ASC
 ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Table] ON
INSERT [dbo].[Table] ([a], [b], [c], [d], [e], [f], [g], [h], [i], [j], [k], [l],
                      [m], [n], [o], [p], [q], [r], [s], [t], [u], [v], [w], [x],
                      [y], [z], [aa], [ab], [ac], [ad], [ae], [af], [ag], [ah],
                      [ai], [aj], [ak], [al], [am], [an], [ao], [ap], [aq], [ar],
                      [as], [at], [au], [av], [aw], [ax], [ay], [trkCusT4SA], [az],
                      [ba], [bb], [bc], [bd])
              VALUES (36, N'City', CAST(2.500 AS Decimal(18, 3)), 1, N'SSS', N'CTY',
                      N'http://google.com', 200, -4, 6400, 6700, 150, 450, 6400,
                      6700, 1400, 1800, 3400, 3800, 8000, 8400, 10100, 10500, 850,
                      2430, 2880, 4460, 7480, 9060, 9540, 11070, 6400, 6700, 150,
                      450, 6400, 6700, 1400, 1800, 3400, 3800, 8000, 8400, 10100,
                      10500, 850, 2430, 2880, 4460, 7480, 9060, 9540, 11070, NULL,
                      NULL, NULL, NULL)

ALTER TABLE [dbo].[Tracks] ADD  CONSTRAINT [IX_Table] UNIQUE NONCLUSTERED
(
    [A] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
        IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Running SELECT * FROM sys.dm_exec_trigger_stats
Yields the following result:

This is not likely a permissions issue, at least not the type of permission you are thinking of. Typical permissions errors show up as follows:
Msg 262, Level 14, State 1, Line XXXXX
CREATE TABLE permission denied in database 'tempdb'.

Now that we have a more complete error message, we can see that indicates a "severity" of 19.
According to Microsoft SQL Server documentation:

Severity         Meaning

17-19          Indicate software errors that cannot be corrected by the user. Inform your system administrator of the problem.
19               Indicates that a nonconfigurable Database Engine limit has been exceeded and the current batch process has been terminated. Error messages with a severity level of 19 or higher stop the execution of the current batch. Severity level 19 errors are rare and must be corrected by the system administrator or your primary support provider. Contact your system administrator when a message with a severity level 19 is raised. Error messages with a severity level from 19 through 25 are written to the error log.

This sounds more like (hypothetically) using SQL Server Express and reaching the 10 GB DB limit (even a CREATE TABLE statement needs to add meta data) than it does a permissions problem.
With more info it will hopefully be possible to narrow down the specific source of this. Here is what testing has shown so far:

database is not read-only
user = dbo
able to run statements in question on several other identical instances of this exact database just fine. Just one local instance giving the error
unable to create a table using CREATE TABLE dbo.[_test] ([Col1] INT);. but able to edit tables using SSMS and also add rows
unable to create anything, not just Tables (View / Function / etc). get the same error.
The size of the database is 5008.00 MB and space available is 114.16 MB
able to create a temp table while in the problem DB: USE [problem_db]; CREATE TABLE #Bob (Col1 INT);
able to create a real table in tempdb: USE [tempdb]; CREATE TABLE dbo.Bob2 (Col1 INT);
DBCC TRACESTATUS(-1); shows no trace flags in use
Compatibility mode is SQL Server 2014 (120), but have also tried 2017 (140)
able to create a schema in the problem DB: CREATE SCHEMA [_DeleteMe];
UNable to create a table in the new schema: CREATE TABLE [_DeleteMe].[_test] ([Col1] INT);
Owner SID did not match between sys.databases and dbo user in the problem DB, but that has been fixed and still not able to create a table
TRUSTWORTHY is OFF, but also OFF on instance where this DB is just fine

Here are some places to check for things can cancel submitted operations:
SELECT * FROM sys.triggers;
SELECT DB_NAME(ts.[database_id]) AS [Database],
       OBJECT_NAME(ts.[object_id], ts.[database_id]) AS [Trigger],
       OBJECT_DEFINITION(ts.[object_id]) AS [Definition],
       *
FROM   sys.dm_exec_trigger_stats ts;

SELECT * FROM sys.database_audit_specifications;
SELECT * FROM sys.database_audit_specification_details;

SELECT * FROM sys.security_policies;


DB Normalization ends up with 2 similar tables I've been googling for a long time now, reading lots of Normalization Tutorials, but haven't found an answer to my question....

The problem (in this question) is when I have something like a trip, where it has a start location and end location, but those locations are stored on a table and assigned an ID.

If I follow the normalization tutorials I end up with:

UNF

TripID, VehicleId, EndLocId, StartLocId, StartLocAddress, EndLocAddress

So if I normalize, I end up with these tables

1NF = UNF (no repeating groups)

2NF = 1NF (no partial dependency)

3NF (Separate Transitional Dependency)

Trip(TripId, VehicleId, StartLocId, EndLocId)
TripStartLoc(StartLocId, StartAddress)
TripEndLoc(EndLocId, EndLocAddress)

In my 3NF the last 2 tables will be exactly the same, so I want to use only one, a Location(LocId, LocAddress)

Trip(TripId, VehicleId, StartLocId, EndLocId)
Location(LocId, LocAddress)

But I can't find any step that allows me to do this, I know I can do it because there's no point in maintaining 2 tables with repeated information... but how to justify? or is this step outside of Normalization? I just want 2 foreign keys in my trip table pointing to the location table, one for the starting point and one to the end point.

Thank you for your help

The purpose of normalization is to remove insert, update and delete anomalies from the data model.  For instance, if you have a spelling error for the name of a location or say the lat-long is incorrect, you would want a single place to go to change that information.  Your solution with two tables seems correct to me.  Each column is dependent on the key, the whole key, and nothing but the key of the table.


How to synonymize a database name? Within one database instance, I need to restore a set databases into new names. The new names will be suffixed based on the development phases, like: _DEV and _QA. Where:

DBApp_DEV shall access DBGen_DEV, and
DBApp_QA shall access DBGen_QA.

From DBApp_DEV, I would do this in an SP:

select Col1 from DBGen_DEV.dbo.TableName

However, from DBApp_QA I would have do this in the same SP:

select Col1 from DBGen_QA.dbo.TableName

Now, how can I synonymize the database name in that SP?

OK, I managed to solve this by creating a Synonym in DBApp_DEV:

CREATE SYNONYM GenTableName FOR DBGen_DEV.dbo.TableName

And in DBApp_QA, the Synonym would be:

CREATE SYNONYM GenTableName FOR DBGen_QA.dbo.TableName

This way, I can maintain the exact same query in my SP that exists in both DBApp_* databases:

select Col1 from GenTableName


How to create a clustered index on a schema bound view with the least amount of contention caused? I have a table with 100 billion rows in it, with an identity BIGINT column that is the primary key (and clustered index). I have a schema bound view on that table that is filtered down to the last 500 million rows or so. I want to create a clustered index on the view that incorporates the primary key and a few other fields from the table.

The table is highly transactional, and can't really be locked for more than a minute at a time. Will the creation of the index on the view cause contention directly on the underlying table during the creation? If so, is it possible for me to create the index on my schema bound view in either an incremental manner (i.e. somehow pause part of the way through creation, to allow the table to catch up to it's transaction backlog, then resume creation). Also I have to be careful with overall server contention as well, I've seen heavy operations against one table like this slow up my whole server before.

Essentially I'm looking for the most efficient way to create the index on my view to minimize contention.

EXAMPLE,

CREATE VIEW FilteredIndexedView WITH SCHEMABINDING AS

SELECT TextColumn1, TextColumn2, DateColumn1
FROM dbo.BoringTable
WHERE DateColumn1 < '1/1/2019';

CREATE CLUSTERED INDEX IX_FilteredIndexView_DateColumn1 ON FilteredIndexView (DateColumn1);

INSERT INTO dbo.BoringTable (TextColumn1, TextColumn2, DateColumn1)
SELECT 'SomeText', 'SomeOtherText', '12/11/2019'; -- Some date that falls outside the filter of the indexed view

Whenever data will be inserted WHERE DateColumn1 < '1/1/2019' then index IX_FilteredIndexView_DateColumn1 will be updated in same query plan.
That means Insert query cost will increase.
If data inserted is outside the bound of give view i.e. WHERE DateColumn1 >='1/1/2019' then in this case index IX_FilteredIndexView_DateColumn1 won't be maintain. That means there won't be extra cost of maintaining index.

As this table is highly transnational and if most of the INSERT or DML operation lies within  WHERE DateColumn1 < '1/1/2019' then INSERT COST will INCREASE significantly.

In this example it appear that Index View is NO NO.

If Insert operation finish in seconds and whatever the cost incur is acceptable then Index View is no problem.

In First place, why you will create view with Single table ?

Alternatively you can try Filtered Index

Create NonClustered index ix_Boring_Date on Boring(DateColumn1) include (TextColumn1,TextColumn2)
where DateColumn1 < '1/1/2019'

Keep Rebuilding index from time to time.

Suggested Reading by  @PaulWhite


Recommended SQL server service accounts In my SQL environments most of the SQL server are running under '.\Local User' with Power user groups rights and some sql servers are running under virtual accounts 'NT Service\MSSQLSERVER'.

I know its best to run SQL service  under Domain accounts, but I need to know best compared  to Local User '.\Local User' vs Vertual account 'NT Service\MSSQLSERVER'.

The MSDN Page Configure Windows Service Accounts and Permissions has some detailed recommendations for the SQL Server 2014 service accounts.

The subheading for configuring the services is at Service Permissions

This is quite a detailed outline of recommendations for the SQL Server Service Accounts.  These are good recommendations, however you can make your own decisions on configuration, based on your needs for security and for being able to manage the configuration.

Truth in reading: Although we use many of these settings, we also have some variations that suit us.


Query Transactional Replication Article Filters Through the UI, I can see that I have 2 tables with filters on them. I need to be able to query this information but cannot find the table that contains the data. I did a search for any column in the Distribution database with a name like either "Filter" or "Where". The results are below. Unfortunately, none of these tables has the data I'm looking for  in them.

 - SELECT * FROM IHarticles
 - SELECT * FROM IHextendedArticleView
 - SELECT * FROM MSarticles
 - SELECT * FROM MSmerge_articlehistory
 - SELECT * FROM MSmerge_articleresolver
 - SELECT * FROM sysarticlecolumns
 - SELECT * FROM sysarticles
 - SELECT * FROM sysextendedarticlesview
 - SELECT * FROM sysschemaarticles

Does anyone know where this data is held?

To research this, I configured transactional replication on my test server with 1 article having row filter. I did this for Adventureworks.Sales.Store table having [CustomerID] <10. Initially, I tried querying distribution..sysarticles to check the filter details. But, surprisingly it did not show any data in it.

Then I checked Adventureworks..sysarticles(i.e. in publication database) and that did the trick. See the below result:


How to make unique check without creating heavy index in postgres I need to implement unique check for INSERT and UPDATE operations, but I would prefer to avoid creating heavy unique index (which is approx 12Gb now) on my table. Now I have unique partial index and it work great, except one thing - it need a lot of SSD space. I don't need SELECT operations using this index, I need just check for the uniqueness of the data.

I read this and this discussion and I understood that it's no real difference between unique constraint and unique index, except partial condition, which is able only for index.

Is there way to do this?

UPDATE:

My example is the schema of many-to-many relation table with history option. This option is implemented by 2 additional fields time_from and time_to. They are storing the time of entering and leaving relation. For data consistency I created 4 additional partial unique indexes (see below).

Now this table contains 1 162 010 000 rows. And overall space of table with each indexes:

vkontakte_wall_post_likes_users - 57 GB
vkontakte_wall_post_like_users_post_id - 24 GB
vkontakte_wall_post_like_users_time_to_2col_uniq - 24 GB
vkontakte_wall_post_like_users_pkey - 24 GB
vkontakte_wall_post_like_users_user_id - 24 GB
vkontakte_wall_post_like_users_time_to_3col_uniq - 846 MB
vkontakte_wall_post_like_users_time_from_2col_uniq - 8192 bytes
vkontakte_wall_post_like_users_id_seq - 8192 bytes

The schema:

CREATE TABLE vkontakte_wall_post_likes_users
(
  id integer NOT NULL DEFAULT nextval('vkontakte_wall_post_like_users_id_seq'::regclass),
  post_id integer NOT NULL,
  user_id integer NOT NULL,
  time_from timestamp with time zone,
  time_to timestamp with time zone,
  CONSTRAINT vkontakte_wall_post_like_users_pkey PRIMARY KEY (id),
  CONSTRAINT post_id_refs_id_3979681bdf0b31a3 FOREIGN KEY (post_id)
      REFERENCES vkontakte_wall_post (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT user_id_refs_id_73bdbaad8e08aee5 FOREIGN KEY (user_id)
      REFERENCES vkontakte_users_user (remote_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
)
WITH (
  OIDS=FALSE
);
ALTER TABLE vkontakte_wall_post_likes_users
  OWNER TO manufacture;

CREATE INDEX vkontakte_wall_post_like_users_post_id
  ON vkontakte_wall_post_likes_users
  USING btree
  (post_id);

CREATE UNIQUE INDEX vkontakte_wall_post_like_users_time_from_2col_uniq
  ON vkontakte_wall_post_likes_users
  USING btree
  (post_id, user_id)
  WHERE time_from IS NULL;

CREATE UNIQUE INDEX vkontakte_wall_post_likes_users_time_from_3col_uniq
  ON vkontakte_wall_post_likes_users
  USING btree
  (post_id, user_id, time_from)
  WHERE time_from IS NOT NULL;

CREATE UNIQUE INDEX vkontakte_wall_post_like_users_time_to_2col_uniq
  ON vkontakte_wall_post_likes_users
  USING btree
  (post_id, user_id)
  WHERE time_to IS NULL;

CREATE UNIQUE INDEX vkontakte_wall_post_like_users_time_to_3col_uniq
  ON vkontakte_wall_post_likes_users
  USING btree
  (post_id, user_id, time_to)
  WHERE time_to IS NOT NULL;

CREATE INDEX vkontakte_wall_post_like_users_user_id
  ON vkontakte_wall_post_likes_users
  USING btree
  (user_id);

There's no way to avoid creating a unique index if you want to efficiently ensure uniqueness for the data. The unique index is necessary for PostgreSQL to enforce uniqueness in the face of concurrent inserts, updates, and deletes.

A unique index is what backs a unique constraint; when you create a unique constraint, a unique index is created for you automatically.

The only other way to enforce uniqueness is to LOCK TABLE mytable IN EXCLUSIVE MODE before any change to the table that might affect the column you wish to make unique. That way you don't have to create a unique index, but in exchange you can only have one transaction changing the table at a time. You could do this from a trigger only when it detects a change in the unique column, but then you'd suffer from lock upgrades that would lead to frequent deadlocks.

Really, a unique index is the way to go here.

You can mitigate the impact of the required lock by using CREATE UNIQUE INDEX ... CONCURRENTLY then using the version of ALTER TABLE ... ADD CONSTRAINT ... that specifies an already-created index to use for the UNIQUE constraint.


How to modify multiple databases structure simultaneously? I have multiple databases on the same server which have the same structure as each other. whenever I want to modify a table or a procedure or add, edit or delete something on one of them I should run the modifications manually on all the others too!

Is there a way to modify the structure of all of the databases simultaneously and preferably automatic?

I use MySQL workbench tool to connect to my databases.

It's the job of your app. Database Schema Migration frameworks help you with that.

For Java based Apps Flyway is an example tool. For PHP based Apps Laravel Database: Migrations

  Migrations are like version control for your database, allowing your
  team to easily modify and share the application's database schema.
  Migrations are typically paired with Laravel's schema builder to
  easily build your application's database schema. If you have ever had
  to tell a teammate to manually add a column to their local database
  schema, you've faced the problem that database migrations solve.

such Frameworks exists for every language and is the state of the art way to DDL statements in an automated and versioned fashion.

simple example with Flyway and Grails howto deploy a procedure:

path .../grails-app/conf/db/migration/V1_0_7__remove_service_ref_from_service_instance.sql

DELIMITER //

CREATE PROCEDURE normalize_service_instance()
  BEGIN

    SET @col_exists = 0;
    SELECT 1
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'service_instance'
          AND column_name = 'service_id'
          AND table_schema = database()
    INTO @col_exists;

    IF @col_exists = 1
    THEN
      ALTER TABLE service_instance
        DROP FOREIGN KEY FK_c2o824f4t90lp1v2df5dxp2r8;
      ALTER TABLE service_instance
        DROP COLUMN service_id;
    END IF;

  END//

Other popular tool for Java land is Liquibase

  Liquibase is an open source database-independent library for tracking,
  managing and applying database schema changes. It was started in 2006
  to allow easier tracking of database changes, especially in an agile
  software development environment.


Need SQL Server VLAN advice.. is it best practice to have all SQL Servers under 1 VLAN/network zone? Looking at the network placement of my SQL Servers. Looking for answers:

Is it a best practice to have all SQL Servers in 1 or 2 VLANs within your businesses network? however, if so, wont there potentially be performance issues on the NICs that manage that VLAN because there are so many SQL Servers in one VLAN?

Also on firewalls between the SQL Servers & application servers - this is not recommended right, As there will be a bottleneck from the firewall?

So going from the above comment, is it simply best practice to have the SQL servers in the same VLANs as the application servers that interact with the databases?

Is it a best practice to have all SQL Servers in 1 or 2 VLANs within your businesses network?

This is enterprise and personal preference. Some enterprises setup areas for specific things, some don't. You might do it to ease certain administration items and others harder, but overall there isn't anything you specifically gain or lose from doing so.

  Also on firewalls between the SQL Servers & application servers - this is not recommended right, As there will be a bottleneck from the firewall?

This comes down to security requirements. Most enterprises have this in place, but again it's entirely dependent upon your requirements. If you have no requirements you may or may not choose to do this based on the security measures you'd like to enforce. Not everyone specifically wants all servers to be able to talk to all other servers on a network.


Cannot connect to a mongos I am implementing a sharded cluster in a Windows environment.

I first create the config server as follows:

C:\MongoDB\bin>mongod –configsvr –bind_ip 192.168.1.4 –port 3000 –dbpath C:\Sharding\configServer

Then I try to connect to the mongos instance which is listening on port 3000:

The command I use to connect to the mongos instance is as follows:

C:\MongoDB\bin>mongos –configdb 192.168.1.4:3000

My problem is that I cannot connect with the mongos instance as I get the below error. I cannot figure out what is this problem and how to solve it.  I searched on the Internet however all suggestions that I found did not solve my problem and I'm literally stuck.

provide a port option for your mongos instance, for example 27018

The error messages says: Only one usage of socket address is normally permitted for 0.0.0.0:27017. It looks like mongos instance is trying to bind to a port that is already ocuppied by another process (propably mongod)


Getting the database from synonym base object Risking asking something idiotic, but not easily got by Google-fu, but how to query the database id (and other information) from the base object of an synonym?

The synonym in question is an remote one (pointing to a table on another DB on the same server). The base object itself is not fixed: is the same table on different DB each execution of procedure.

I would like an non-specific method (example: don't want to rely on parsing the base_object_name column on sys.synonyms), but I'll understand if I'm told that's the only choice.

And since it begs the question: concurrency is not an problem, only one client will call that procedure.

This will work for all three-part names, and all four-part names where the server is the local instance.

SELECT
  name, base_object_name,
  DB_ID(PARSENAME(base_object_name,3)),
  DB_NAME(DB_ID(PARSENAME(base_object_name,3)))
FROM sys.synonyms;

If you get NULL it's either because the synonym is only 2-part (e.g. it references an object in the current database), or the database listed in the synonym no longer exists (it may have been renamed, dropped, etc).

If the four-part name references a remote server, you won't be able to use DB_ID()/DB_NAME(), you'd have to join to the remote server's master.sys.databases - which would involve first parsing out the linked server being used, then constructing some dynamic SQL. Just mentioning that; it doesn't seem to be an issue for your use case.

If you want to limit to 3-part names only, you can add these predicates:

WHERE PARSENAME(base_object_name,3) IS NOT NULL
AND PARSENAME(base_object_name,4) IS NULL;


How to create a stored procedure with several delete in(select) statements? Our database needs to be periodically cleared of certain items that receive a specific flag (A4) on their id. Our application manages stores and all tables are myISAM. The items can be either in the bought, sold, exchanged, etc tables and they will also exist in the main table that contains all items (the only information the tables share is the id and I can't control that at the moment).

My initial idea was to create the individual delete statements and then place them together in a stored procedure. The stored procedure I created contains:

delimiter $$

create procedure clear_A4(in period char(10))

BEGIN

delete from mov_pedido where pednot in(select distinct titnum from mov_titulo where titnum like 'A4%' and titemi <='2012-01-01' and titvalpag >0);

delete from mov_orcame where orcnot in(select distinct titnum from mov_titulo where titnum like 'A4%' and titemi <='2012-01-01' and titvalpag >0);

delete from mov_nfsven where nfsnum in(select distinct titnum from mov_titulo where titnum like 'A4%' and titemi <='2012-01-01' and titvalpag >0);

delete from mov_movime where movdoc in (select distinct titnum from mov_titulo where titnum like 'A4%' and titemi <='2012-01-01' and titvalpag >0);

delete from mov_titulo where titnum like 'A4%' and titemi<='2011-12-01' and titvalpag >0;

end $$

delimiter ;

These lines refer to items sold. A sale starts with a order, then a quotation, then the actual sale, and finally it gets throw in the main record. The first table mov_pedido has the orders, mov_orcame has the quotations, mov_nfsven has the actual sales and mov_movime records all the transactions. These tables can only be cleared of items that have been paid for by customers but this information can only be found on mov_titulo so I decide to use the select statements with the IN operator. The problem is I also need to clear the mov_titulo table. If I delete the items from mov_titulo first then the information used in the select statements is lost.

Reading up on mySQL,multithreaded databases it seems that if the statements are inside the stored procedure together, they will be processed simultaneously and because the tables are related that way it will cause a problem.

My question is: how can I improve the logic of my delete statement to make them work together? Can I improve my stored procedure to deal with this?

Right now I am executing one statement at a time manually, and that works, but I can see problems down the line, such as entering the wrong date in one of the existing 20 some delete statements for example so I am looking for a better way to do this.

Thank you.

if the statements are inside the stored procedure together, they will be processed simultaneously and because the tables are related that way it will cause a problem.

No, the statements in a stored procedure are processed sequentially, not in parallel.  A stored procedure executes in a single thread, just as if you had issued the queries one after another... not simultaneously.

There are some optimizations you could make, however.

If the `titnum` table doesn't have a multi-column index on the three columns (titvalpag, titemi, titnum) together, you should probably add one.

The delete statements can be rewritten without the IN().

Use caution with this, because doing it incorrectly will remove the wrong data.

delete from mov_pedido
where pednot in(select distinct titnum
                  from mov_titulo
                 where titnum like 'A4%'
                   and titemi <='2012-01-01' and titvalpag >0);

...becomes...

DELETE p
  FROM mov_pedido p
  JOIN mov_titulo m ON m.titnum = p.pednot
 WHERE m.titnum like 'A4%' and m.titemi <='2012-01-01' and m.titvalpag >0;

This is the multi-table delete syntax.  "DELETE p" will only remove the rows from "p" (an alias for mov_pedido) that match rows in "m" (mov_titulo).  It won't remove the rows from "m" unless you say "DELETE p, m" -- so don't do that.

The mov_pedido table (and the others) should have an index on the column you're using to join (pednot, etc.) if they don't already.


SQL Alert for long running queries I want to receive a mail when a query is running for more than x seconds. I know I can set up a job running on the database but, is there any way to do the same using SQL Alerts? What kind of alert should I use?
   Thanks.

If you have sp_WhoIsActive (great tool, can be downloaded here: http://whoisactive.com/) you can log the results to a table and then email out if the threshold you set for the duration is tripped.

example:

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---- Load WhoIsActive To A Temp Table

IF OBJECT_ID('tempdb.dbo.#wia') IS NOT NULL DROP TABLE #wia

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

EXEC sp_WhoIsActive
    @format_output = 1,
    @destination_table = '#wia'

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---- Find Long Running Queries

IF OBJECT_ID('tempdb.dbo.#Temp') IS NOT NULL DROP TABLE #Temp

SELECT
[dd hh:mm:ss.mss],
session_id,
CAST(sql_text AS VARCHAR(MAX)) AS sql_text,
login_name,
wait_info,
host_name,
database_name,
program_name,
start_time
INTO #Temp
FROM
#wia

WHERE
start_time < DATEADD(HOUR,-8,GETDATE() ) -- CHANGE THIS TO WHATEVER YOU WANT

DECLARE @CNT INT = (SELECT COUNT(*) FROM #Temp )

IF @CNT > 0
BEGIN
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

SET @xml = CAST(( SELECT [dd hh:mm:ss.mss] AS 'td','',session_id AS 'td','', [sql_text] AS 'td','',
[login_name] AS 'td','',[wait_info] AS 'td','','',[host_name] AS 'td','',
[database_name] AS 'td','', [program_name] AS 'td','',start_time AS 'td'
FROM  #Temp
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='
Long Running Queries

 dd hh:mm:ss.mss 	  session_id 	  sql_text 	  login_name
 wait_info 	  host_name 	  database_name
 program_name 	  start_time

'

SET @body = @body + @xml +'
'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'XXXXX', -- replace with your profile
@body = @body,
@importance = 'HIGH',
@body_format ='HTML',
@recipients = 'XXXXX', -- replace with your email address
@subject = 'Longing Running Queries On <SERVER_NAME>' ;

DROP TABLE #Temp

END


If the maximum current value of an ID generated by a sequence is N, does that guarantee that all future rows will have index > N? I'm debugging some legacy code and have found what might be a flaw in how it interacts with the database. A simplified version of it is as follows:
TableA is acting as a queue. It has data inserted into it from multiple users/sessions at pretty high velocity. There are never any updates or deletes except the one I am about to describe. This table has an integer ID which is populated by a 'before insert' trigger on the table that draws values from a sequence.
A consumer application repeatedly selects the whole table, processes the data, and stores the maximum index value of the resultset each time. It then executes
DELETE FROM TableA WHERE id<=:maxSelectedID

My question is, is this a reliable way to delete all the data that was previously selected (and only that data)? I feel like with the volume of inserts on this table that it is quite possible that a higher ID might get committed (and thus be available to select) before a lower one. In this case the lower one could potentially be deleted without ever having been selected (or if we are lucky it would also miss the delete get caught in the next select).

If the database is running on a RAC cluster, each node in the cluster would have a separate sequence cache.  That would cause the values returned to be out of order unless the sequence was created with ORDER specified (i.e. CREATE SEQUENCE seq_foo START WITH 1 ORDER).
If the database is running on a standalone instance, sequence numbers will always be generated in order.  If you're depending on this behavior for functional reasons, I'd be pedantic and specify ORDER when creating the sequence even on a standalone database.  But it isn't necessary to do so.
From the documentation

ORDER is necessary only to guarantee ordered generation if you are
using Oracle Real Application Clusters. If you are using exclusive
mode, then sequence numbers are always generated in order.

The other potential issue would be if the sequence reached the MAXVALUE and was set to CYCLE by starting over at the minimum value.  NOMAXVALUE and NOCYCLE are the defaults, though, so it would be pretty unusual that someone using a sequence like this would create the sequence in that way.


What does TBSCAN GENROW mean in Explain Plan? In a DB2 Explain Plan, what does TBSCAN GENROW (line 5 below) mean?

SELECT STATEMENT ( Estimated Costs =  1.811E+03 [timerons] )
   1 RETURN
      2 NLJOIN
          3 [O] TBSCAN
              4 SORT
                  5 TBSCAN GENROW
          6 [I] FETCH EKKO
              7 IXSCAN EKKO~1 #key columns:  2

The IBM docs say GENROW is

  "A built-in function that generates a
  table of rows, using no input from
  tables, indexes, or operators"

What does that mean from a performance perspective?

Also, this is in an SAP system if that matters.

I think I've figured it out.  GENROW means it's creating a temporary table which is then joined to an actual table.  This is usually done when the query contains an IN (...) statement.  Specific to SAP, this is usually done for FOR ALL ENTRIES queries.


PostgreSQL: ... LIMIT 1 much slower than fetching all rows? If I fetch only one row, the query takes much longer: 1433 ms vs 23 ms

Is there a work around?

Slow:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM "modwork_ticket" WHERE
 "modwork_ticket"."email_sender_id" = 'foo@example.com'
 ORDER BY "modwork_ticket"."date_created" ASC limit 1;

--------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..542.14 rows=1 width=1146) (actual time=1433.128..1433.129 rows=1 loops=1)
   Buffers: shared hit=1466151 read=4661
   ->  Index Scan using modwork_ticket_date_created on modwork_ticket  (cost=0.43..606714.36 rows=1120 width=1146) (actual time=1433.125..1433
         Filter: ((email_sender_id)::text = 'foo@example.com'::text)
         Rows Removed by Filter: 1705251
         Buffers: shared hit=1466151 read=4661
 Planning time: 2.504 ms
 Execution time: 1433.218 ms

Fast:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM "modwork_ticket" WHERE
  "modwork_ticket"."email_sender_id" = 'foo@example.com'
  ORDER BY "modwork_ticket"."date_created" ASC;

--------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=4335.83..4338.63 rows=1120 width=1146) (actual time=23.637..23.794 rows=584 loops=1)
   Sort Key: date_created
   Sort Method: quicksort  Memory: 732kB
   Buffers: shared hit=544 read=4
   ->  Bitmap Heap Scan on modwork_ticket  (cost=25.11..4279.11 rows=1120 width=1146) (actual time=20.479..22.595 rows=584 loops=1)
         Recheck Cond: ((email_sender_id)::text = 'foo@example.com'::text)
         Heap Blocks: exact=538
         Buffers: shared hit=541 read=4
         ->  Bitmap Index Scan on modwork_ticket_email_sender_id_like  (cost=0.00..24.83 rows=1120 width=0) (actual time=20.388..20.388 rows=
               Index Cond: ((email_sender_id)::text = 'foo@example.com'::text)
               Buffers: shared hit=3 read=4
 Planning time: 0.173 ms
 Execution time: 23.974 ms

Version:

PostgreSQL 9.6.3 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 4.7.2 20130108 [gcc-4_7-branch revision 195012], 64-bit

I run "VACUUM ANALYZE" before running above queries.

The problem is that rows for 'foo@example.com' are deficient in values with an early date_created, but the planner doesn't know that.  It thinks they are evenly scattered, so it will find one early on when walking modwork_ticket_date_created and will be able to stop then.  But instead it has to walk through 1,705,251 of them.

You can create an index on (email_sender_id text_pattern_ops, date_created) and it will be able to jump directly to the tuple you want.  That should be much faster than either existing plan.   (I proposed the including text_pattern_ops purely because the name of the existing index has the word "like" in it, so I assume it was already defined that way--but maybe you actually have a column named "like".  It would be better if you show us the definitions so we don't have to guess about things such as this).  This index may be able to replace an existing one, so the overall number of indexes might stay the same.

If you don't want to create the index and just want it to the use the 2nd plan, you can force it by writing the query like this:

SELECT * FROM modwork_ticket WHERE
 modwork_ticket.email_sender_id = 'foo@example.com'
 ORDER BY modwork_ticket.date_created + '0 days' ASC limit 1;

By adding something to the date before you sort, it tricks PostgreSQL into thinking it can't use the date index.

Once v10 of PostgreSQL is released and you upgrade to it, you can create an advanced statistics definition for the functional dependency between the two columns, which might also be enough to get it to choose the better plan.


SQL Server table size increased with column data type change I have a SQL Server table with 39 million rows. I changed the data type of existing columns as follows:

Col A -> tinyint -> bit
Col B -> tinyint -> bit
Col C -> tinyint -> bit
Col D -> tinyint -> bit
Col E -> tinyint -> bit
Col F -> smallint -> tinyint
Col G -> smallint -> tinyint

The table size was supposed to have shrunk, however, the size increased from 11gb to 18gb.

I tried the following:

Rebuild all indexes
DBCC SHRINKDATABASE ('DBName')

however it did not work.

Any idea where to look for the problem?

We need more info. Stuff like:

Is the table clustered or a heap?

What indexes do you have?

How did you do the change? ALTER TABLE ALTER COLUMN, or use some GUI?

If you used ALTER TABLE, can you post those commands?

If you used some GUI, what SQL did it generate?

For instance, I did a test. I had a heap, and used ALTER TABLE ALTER COLUMN. Evidently, the old data was still there for the tinyint -> bit change. This means that the row lengh increased, and subsequently more space was needed. Also, since it was a heap, forwarding pointers were left at the old row locations, for the rows that were moved. Why was the old data still there? One would have to ask MS. Sometimes they are clever, and for instance delay a change until you actually modify data, so it can do it as a a meta-data only change.

The smallint -> tinyint didn't change number of bytes/pages. This was indeed done as a meta-data only change.

So, in order to examine your case, we need a repro from you, with your table structure including stuff like I mentioned above (heap or clustered table, other indexes etc).

For the record, since my table was a heap, I could reclaim the space by doing an ALTER TABLE REBUILD. Probably easier and faster than creating a new table.

Here's the script I used, with some comments. In case you want to dig deeper.

USE tempdb

SET STATISTICS IO ON

DROP TABLE IF EXISTS x1

SELECT TOP(100000)
 CAST(0 AS tinyint) AS a
,CAST(0 AS tinyint) AS b
,CAST(0 AS tinyint) AS c
,CAST(0 AS tinyint) AS d
,CAST(0 AS tinyint) AS e
,CAST(0 AS tinyint) AS f
,CAST(0 AS tinyint) AS g
,CAST(0 AS tinyint) AS h
,CAST(0 AS tinyint) AS i
,CAST(0 AS smallint) AS j
INTO x1
FROM sys.columns AS a, sys.columns AS b

--Verify data types
EXEC sp_help x1

--Below proc can be found at my web-site, https://karaszi.com/spindexinfo-enhanced-index-information-procedure
EXEC sp_indexinfo 'x1' --266 pages

ALTER TABLE x1 ALTER COLUMN a bit
EXEC sp_indexinfo 'x1' --306 pages, new pages (one byte per row) allocated for bit

ALTER TABLE x1 ALTER COLUMN b bit
EXEC sp_indexinfo 'x1' --306 pages, same byte used as above

ALTER TABLE x1 ALTER COLUMN c bit
ALTER TABLE x1 ALTER COLUMN d bit
ALTER TABLE x1 ALTER COLUMN e bit
ALTER TABLE x1 ALTER COLUMN f bit
EXEC sp_indexinfo 'x1' --306 pages, same byte used as above

ALTER TABLE x1 ALTER COLUMN g bit
EXEC sp_indexinfo 'x1' --346 pages, new pages (one byte per row) allocated for bit

ALTER TABLE x1 ALTER COLUMN h bit
EXEC sp_indexinfo 'x1' --346 pages

ALTER TABLE x1 ALTER COLUMN i bit
EXEC sp_indexinfo 'x1' --378 pages, new pages (one byte per row) allocated for bit

ALTER TABLE x1 ALTER COLUMN j tinyint
EXEC sp_indexinfo 'x1' --378 pages, above was meta-data only operation

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('x1'), NULL, NULL, 'DETAILED')
--Pages are full

--Lets look at a row
DBCC TRACEON(3604)

SELECT * FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('x1'), null, null, 'DETAILED') AS a
WHERE page_type_desc = 'DATA_PAGE'
--Grab some page number from above, the column allocated_page_page_id

--Check out the page
--Note all dropped columns, they are still there
DBCC PAGE('tempdb', 1, 268, 3)
--Also, we got forwarded records because of the row length increased

--Rebuild the data
ALTER TABLE x1 REBUILD
EXEC sp_indexinfo 'x1' --170 pages, data reclaimed
DBCC PAGE('tempdb', 1, 544, 3)


sql_safe_updates set to ON by default This is fresh mysql install from Debian apt.

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.5.47, for debian-linux-gnu (x86_64) using readline 6.3
..
Server version:         5.5.47-0+deb8u1-log (Debian)
Protocol version:       10
Connection:             Localhost via UNIX socket
..
Note that you are running in safe_update_mode:
UPDATEs and DELETEs that don't use a key in the WHERE clause are not allowed.

This is how it's set:

mysql> show global variables like 'sql_safe_updates';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_safe_updates | OFF   |
+------------------+-------+

mysql> show variables like 'sql_safe_updates';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_safe_updates | ON    |
+------------------+-------+

There's no sql_safe_updates setting on any config file (my.cnf or ~/.my.cnf etc.).

Why it's set to ON for my session (run mysql from root).

From here, you obtain the command to change the setting for sql_safe_updates (from the mysql client command line). It's fairly simple - SET SQL_SAFE_UPDATES=0; (or 1), though as you point out, it can't be modified in my.ini or my.cnf.

However, from this post on StackOverflow, you have a method to run SQL (and server) commands on server startup.

Briefly, you create a file called (arbitrary) <my_file_name>.sql and put the command above setting the sql_safe_updates in that file.

Then, in the [mysqld] section of the my.ini (or my.cnf), you put the line

init-file="C:/xxx/yyy/<my_file_name>.sql"

To answer your specific question, there is a reason that the default is ON.

This is because many software developers are not very proficient in SQL. To compound this problem, many of them use frameworks which produce SQL which is (ahem...) not optimal. If one ran a command

DELETE FROM TABLE <no where clause>

on  a production system, this could cause havoc (and has done, judging by the number of posts here pleading for help with a deleted table).

Note: I tested this on Windows (don't have Linux up and running at the moment).


Understanding Index w/ Cast Given:
postgres=# \d b
                Table "public.b"
 Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
 a      | uuid |           | not null |
Indexes:
    "b_a_idx" btree (a)

postgres=# \d c
                Table "public.c"
 Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
 a      | text |           | not null |
Indexes:
    "c_a_idx" btree (a)

postgres=# explain b;
ERROR:  syntax error at or near "b"
LINE 1: explain b;
                ^
postgres=# analyze b;
ANALYZE
postgres=# analyze c;
ANALYZE

Why does the following quering not use b.a's index?
postgres=# explain (select * from b join c on (b.a :: text) = c.a);
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Merge Join  (cost=129.05..455.25 rows=12580 width=48)
   Merge Cond: (c.a = ((b.a)::text))
   ->  Index Only Scan using c_a_idx on c  (cost=0.15..64.55 rows=1360 width=32)
   ->  Sort  (cost=128.89..133.52 rows=1850 width=16)
         Sort Key: ((b.a)::text)
         ->  Seq Scan on b  (cost=0.00..28.50 rows=1850 width=16)
(6 rows)

Why does the following query not use b.a's index?

Because the index only stores the UUID value of a, not the result of the expression a::text
To make the index eligible for use, you would need to cast the other column to the data type stored in the index, e.g.
select *
from b
   join c on b.a = c.a::uuid;

Note that this doesn't guarantee that the index is used, it just makes it possible.
But as the query retrieves all rows from both tables, it's highly unlikely that the index is used anyway.


PostgreSQL 7.4 data corruption We're dealing with the following situation (PG version is 7.4.30): somehow, a DB has gone missing from pg_database (psql \l listing doesn't show it anymore). More, while you can still \c to it, a \d table listing only shows a fraction of the tables. A \d on the existing tables shows missing columns as well.. However, all the tables (still visible or not) may be queried by selects via psql and the actual data and columns in the result sets seem just fine.

We backed up the data folder and for the last 24h tried to recover the situation to a point where pg_dump works. Unfortunately we haven't been fully successful yet although we tried tons of suggestions in previous similar posts. Reindex didn't help, but a "VACUUM FULL FREEZE [ANALYZE]" did free 70% (14GB to 4) of the used disk space. Of course, the database wasn't vacuumed regularly as it should have been so:

WARNING:  some databases have not been vacuumed in over 2 billion transactions
DETAIL:  You may have already suffered transaction-wraparound data loss.

A full vacuum without the freeze option does make all databases, tables and users visible, but duplicated, i.e. \l then shows the database twice and a \d on the database lists all tables twice. The postgres user appears twice in pg_user, etc. pg_dump won't work if the database is not listed and won't either if the postgres user is duplicated..

Does this sound familiar to anyone? It's a pity that all the data seems to be there, but we're not able to recover and restore it to a clean DB. We'd appreciate any suggestions.

Thank you!

PS: I've waiting for feedback from the pgsql-admin mailing list as well.

First, if you haven't done the first two that vyegorov suggests do it now.  If you have already vacuumed, it is not clear what sort of damage you may have already done in this case.  This error should not be possible on new versions of PostgreSQL due to a number of checks, and had you upgraded some time ago, this wouldn't have happened.  I highly recommend trying to stay on supported branches in the future.

I want to take a moment and describe what likely caused the problem and what it means.  The prognosis IMO is not good and recovering the data, if it is even possible, is likely to be expensive and time consuming.  I sincerely hope you have a good backup from before the transaction wraparound.  If not, ouch....

What Went Wrong

PostgreSQL uses something called MVCC, which means that old versions of rows are kept around until they are clearly no longer used.  MVCC as practiced by PostgreSQL stamps each row with a minimum transaction and a maximum transaction, and uses these for visibility management.  When a transaction rolls back those rows entered are no longer visible and those rows deleted remain visible.  Transaction ID's are 32-bit integers.

Periodically you are supposed to vacuum PostgreSQL instances.  This, among other things, manages MVCC so that fewer transaction id's need to be checked, manages free space in tables, and can reset the transaction id sequence.

When the transaction ID wraps around, bad things happen but they boil down to the fact that PostgreSQL can no longer be sure what rows are visible and what rows are not.  Note thais applies not only to rows in your own tables but in the system catalogs as well.  This is a very, very bad thing.  The best approach, if you can do it, is to restore from a backup before the wraparound occurred.  This is why databases and tables are not showing up and why vacuuming full duplicates them.

Why Upgrading Would have Fixed This

Modern versions of PostgreSQL come with autovacuum automatically enabled which runs vacuum processes in the background to help prevent this sort of problem.  More recent versions also will refuse to start new transactions for non-superusers once wraparound is approaching.  This gives you a chance to detect and correct the problem before you suffer possibly catastrophic data loss.

PostgreSQL 7.4 hasn't been supported in nearly three years.  I don't know when the last time anything was vacuumed but it must have been billions of transactions ago.  This is not good.


Migrating from MongoDB Atlas to self managed Mongo (EC2) server There is an abundance of info about migrating TO Atlas but not too much I could find specifically about going the other direction.

I am migrating a collection which is 250GB in size.
I have tried using mongodump from the remote EC2 server connecting to the Mongo Atlas instance however presumably due to the large collection size this keeps failing.
I have also read that mongodump is not great for DBs of this size but I could be wrong.

I was able to download a backup .tar.gz file from Atlas and move this to the the remove server. The file contents are below.

Is it possible to restore these in to a fresh mongo setup on the EC2, or will these only work for the environment they were taken from?

Here are the files available in the backup:

These appear to be database files, if you move them to the Mongodb data path, then start the mongod process/service, should work.

If the database is not inside sharded clusters, you can use mongodump/mongorestore.


Changing IP address of SQL Server 2008 R2 I would like to know if there are any dangers or relevant precautions before changing the IP address of a SQL Server 2008 R2 box.

We have built a virtual PC with Windows Server 2008 R2 and SQL Server 2008 R2. The purpose of this machine is run a job that restores a backup file (copied by a different server), manipulates it, backs it up again and then copies it out to other servers. We've run it a few times in our test/dev DMZ and are now considering deployment.

The simplest option would be to re-IP it. The server name would remain intact. My problem is a colleague has suggested this is unsafe (to re-ip a SQL Server). My question is

a) Is this true for the OS and SQL I'm using? (Win 2008 R2 & SQL 2008 R2?)

b) Is it true for any other combinations of OS and/or SQL?

c) Is there anything special we should do in preparation?

My research thus far indicates it will be fine but I trust the folks of DBA StackExchange more than those social MSDN people.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/9844e0c1-b57e-4e0c-88cf-7cb51cc55e07/change-sql-server-ip-address

http://www.sqlservercentral.com/Forums/Topic1164450-391-1.aspx

Changing the IP Address for a machine running SQL Server is not a problem at all.  You'll need to restart the SQL Server after changing the IP Address so that the SQL Server will start listening on the new IP address.  But that's it.

If no users actually connect to the machine then there's nothing else to do.

If users did connect to the machine they'd need to refresh their DNS cache on their workstations by running "ipconfig /flushdns" or by simply restarting their computers.

Unless you've done something very unusual with the TCP listeners within SQL there's nothing else that needs to be done with SQL Server other than the quick restart.


Cassandra in a Public Cloud - Which Snitch? I am planning on deploying Cassandra in a public cloud (like Amazon AWS).  The cluster will span multiple zones and regions.  The cluster will also require logical DC separation within a given region.  I know that this is achievable using the Ec2MultiRegionSnitch and the logical DC separation can be achieved using the "dc_suffix" parameter in the cassandra-rackdc.properties file.  I also know that this can be achieved using the GosspingPropertyFileSnitch by assigning DC and RACK values to each Node in the same aforementoined file.

Since both snitches can be used to achieve my needs, then what is the benefit of using one snitch over the other?  Are there any specific pros and cons to using the Cloud specific snitch over the Gossip based snitch in a public cloud?  Any restrictions to one over the other in the public cloud?

I have googled around myself and while there is plenty of information regarding each snitch, I can't seem to find anything related to why one snitch would be preferred over the other in the public cloud.

Any guidance is greatly appreciated.

The biggest advantage of the GosspingPropertyFileSnitch is that you can easily migrate your cluster to another cloud without downtime, just by creating a new DC in another cloud, replicate data to that DC and decomission old DC. With provider-specific snitches, such migration may not be possible at all.


MySQL data modeling help I've been struggling with this model for weeks, going in circles from one type of solution to another. I'd like some advice on the best approach.

One or more organizations can sponsor an event or project; projects are a series of events. Not all events are part of projects but projects always have events. Leaders of any of the sponsoring organizations can create and update an event or a project (and the events in that project). A project has leaders, who can also create and update events for that project. Events may have leaders who can also update that event. Does this make for too little accountability and too many people who can edit and add organizations, events, and projects?

For the output end, I need to display for the leader, each event, project, and organization they can update and then when they do update, make sure they have the right to update that particular event, project, or organization.

As I see it I have to have Event, Project, Organization, and Leader tables. I also need to store which organizations sponsor which events and which projects. Here is where I go round and round. Is it better to have two tables or one:

two tables:

project-sponsor table (ID, organization_id, project_id) AND
event-sponsor table (ID, organization_id, event_id)

one table with entity_type = 'project', 'event', or 'org':

sponsor (ID, organization_id, entity_type, entity_id) OR
sponsor (ID, organization_id, event_id, project_id)

I also need to store leaders and their roles in organizations, projects, and events. I face the same quandary. Is it better to have three tables or one:

three tables:

org_leader(ID, leader_id, organization_id, role, title, term...) AND
project_leader(ID, leader_id, organization_id, role, title, term...) AND
event_leader(ID, leader_id, organization_id, role, title, term...)

one table  with entity_type = 'project', 'event', or 'org':

role(ID, leader_id, entity_type, entity_id, role, title, term...) OR
role(ID, leader_id, event_id, project_id, organization_id, role, title, term...)

Which choices would make it easier to find out who can edit a particular event (from its project, if any, its sponsoring organization) and find out which events, projects, and organizations a particular leader can edit?

I've attached erd's for the various choices.

Any advice and strategies would be greatly appreciated!

I would use separate tables for the project-sponsor and event-sponsor relationships.  You are making two many-to-many relationships here, and that just seems like the right way to do it to me.

Can each project/event/org have more than one leader?  If not, why not simply put the leader id in the project/event/org table?  Otherwise, I would use three tables for the same reason as above: you are describing three many-to-many relationships.


Foreign Key Constraint Set Back to Trusted We have some production tables with numerous constraints that are not trusted at the moment.  We're having them checked/trusted so they can be used again.  We know that the optimizer has not been using them in its plans (because they weren't trusted).  I have two questions before setting them back to trusted.

Will the optimizer recognize the change and consider them before using cached query plans for re-run transactions? Put another way, do I need to consider removing any query plans?
Is it safe to say this is a situation where SQL Server has been rebuilding the index (we do have this turned on), and maintaining the index throughout inserts, updates, and deletes, but simply not using the foreign key?

Thanks

Will the optimizer recognize the change and consider them before using cached query plans for re-run transactions? Put another way, do I need to consider removing any query plans?

Yes, the optimizer should recompile the plans since changes to an underlying table are one of the triggers that result in a plan recompilation. Since checking a constraint involves an ALTER TABLE command,

ALTER TABLE [dbo].[table] CHECK CONSTRAINT [fk_table]

this should qualify it as a valid reason to update the plans, according to this TechNet article (from 2008 R2 but should stand true for current versions as well) which specifies that a ALTER TABLE or ALTER VIEW command will qualify.

  Is it safe to say this is a situation where SQL Server has been
  rebuilding the index (we do have this turned on), and maintaining the
  index throughout inserts, updates, and deletes, but simply not using
  the foreign key?

Correct. As you've observed the optimizer ignores the foreign key when creating query plans - no operation involving the table will make use of the foreign key while it is untrusted, whether that is a rebuild or an insert, update, etc.


Programatically copying data from table from offline database to online database I have a local database and an online database. When i make an entry into my local database, it should also automatically make an entry in the online database. Can anybody tell me how I can do that?

The key concept you need to study is called "Replication".


SQL Log shipping - Very Slow restore I have SQL Server 2012 SP4, ten databases and log shipping.

One database 2.7TB

Ten data files and two log files

Log shipping configuration:

Transaction log backup job : 3 Min
Copy job : 1 Min
Restore job : 1 Min
Standby Mode, clicked Disconnect users...

A 50MB trn file take at 2 hours restored. The database is being 100% restored and remains. How do i solve this?

I added sql service account to Perform volume maintenance tasks and disk slowness problem appeared on the server. The storage team replaced the disks with faster disks. Problem is solved.


Select in a PostgreSQL function I'm trying to do my first Function in PostgreSQL, but I can't to do it.
I was write this piece of code

CREATE OR REPLACE FUNCTION getCars ()
   RETURNS TABLE (
      car_name VARCHAR,
      id_car INT
)
AS $$
BEGIN
   RETURN QUERY SELECT
      brand,car_id
   FROM
      cars;
end;
$$

LANGUAGE plpgsql;

But... The error is when I'm trying to call taht function:

SELECT getcars();

Where is the issue?

the error message it's:

SQL Error [42804]: ERROR: structure of query does not match function result type
  Detail: Returned type text does not match expected type character varying in column 1.
  Where: PL/pgSQL function getcars() line 3 at RETURN QUERY

Thanks in advance!

You have two errors:

apparently brand is defined as text, not varchar
set returning functions have to be used like a table in the FROM clause.

So you need to change your function definition to:

CREATE OR REPLACE FUNCTION getcars()
   RETURNS TABLE (car_name text, id_car INT)
AS $$
BEGIN
   RETURN QUERY
   SELECT brand,car_id
   FROM cars;
end;
$$
LANGUAGE plpgsql;

And then use it like this:

select *
from getcars();

Simple functions that just return a query are better defined as language sql:

CREATE OR REPLACE FUNCTION getcars()
   RETURNS TABLE (car_name text, id_car INT)
AS $$
   SELECT brand,car_id
   FROM cars;
$$
LANGUAGE sql
stable;


Can I upgrade SQL Server 2008 R2 Standard to SQL Server 2014 Developer? Is there a migration path to upgrade SQL Server 2008 R2 Standard to SQL Server 2014 Developer?

If so, are there any pain points?

If not, how would you recommend I proceed?

The context of this question is a Developer machine that is running SQL Server 2008 R2 Standard and wants to now run SQL Server 2014 Developer.

Update 07/11/2014 - The accepted answer worked for me. I installed SQL Server 2014 Developer as a named instance then followed Zasz's StackOverflow answer to convert the named instance to the default instance.

That is not a supported upgrade path.

  how would you recommend I proceed?

My recommendation would be to install side-by-side and then migrate (backup and restore) your databases to the SQL Server 2014 instance.


Oracle XE TNS Listener fails to start I freshly installed Oracle XE 11.2.0.2.0 and found out that the TNS Listener service fails to start. At first I tried to start the Windows service by hand. After the failure this is the entry in the event log:

<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
 <System>
  <Provider Name="Application Error" />
  <EventID Qualifiers="0">1000</EventID>
  <Level>2</Level>
  <Task>100</Task>
  <Keywords>0x80000000000000</Keywords>
  <TimeCreated SystemTime="2012-12-18T07:11:23.000000000Z" />
  <EventRecordID>6195</EventRecordID>
  <Channel>Application</Channel>
  <Computer>[removed]</Computer>
  <Security />
 </System>
 <EventData>
  <Data>tnslsnr.exe</Data>
  <Data>11.2.0.2</Data>
  <Data>4e2e9556</Data>
  <Data>orahasgen11.dll</Data>
  <Data>11.2.0.1</Data>
  <Data>4ce506a3</Data>
  <Data>c0000005</Data>
  <Data>0002fbdd</Data>
  <Data>1084</Data>
  <Data>01cddceee2b90e19</Data>
  <Data>C:\oraclexe\app\oracle\product\11.2.0\server\BIN\tnslsnr.exe</Data>
  <Data>C:\oraclexe\app\oracle\product\11.2.0\server\BIN\orahasgen11.dll</Data>
  <Data>20df4569-48e2-11e2-87b0-1c6f650a0fc2</Data>
 </EventData>
</Event>

In a second go I tried STATUS and START in LSNRCTL.EXE:

LSNRCTL> status
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   32-bit Windows Error: 61: Unknown error

... and START:

LSNRCTL> START
Starting tnslsnr: please wait...

TNS-12537: TNS:connection closed
 TNS-12560: TNS:protocol adapter error
  TNS-00507: Connection closed
   32-bit Windows Error: 109: Unknown error

Google found a few forum entries with people having a similar error but all of them would either have an inner error different from 109 or not get any reasonable answer at all...

Any ideas? I know that before installing Oracle XE on my computer, an oracle 11.2.0 client was already on it (part of the image).

ORACLE_HOME and TNS_ADMIN point to that client. Would that have any impact? I hope the two Oracle instances are smart enough for each to run independently!

Update: It does! I changed the ORACLE_HOME and now the listener runs. But I can't go and edit the ORACLE_HOME every time I need to start the listener or run SQLPlus from my client installation. I really wonder what an architecture would install a product with its own listener but the listener is not able to run without a global environment variable being set to a certain directory, especially since we know there can be more than 1 oracle installations on one system...

Check this registry key to ensure ORACLE_HOME is configured for the service:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\KEY_XE\ORACLE_HOME

If that doesn't exist, check here:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_XE\ORACLE_HOME

Also make sure this other key isn't pointing to your old client registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\KEY_XE\ORACLE_HOME_KEY
or
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_XE\ORACLE_HOME_KEY


Filter and then sort products by availability I have table with products. Each product can be avaible in 1+ suppliers, so product can be present multiple times.

products
- id_productSupplier (primary key)
- id_product (foreign key)
- availability (int - how many days takes to deliver product)

I need:

list of 10 product_id
1 record per id_product with lowest availability
sort it by availability of lowest available availability per product_id

For example, when id_productSupplier has availability=0, and other id_productSupplier with same product_id has availability=5, I want to order this product_id with availability=0.

Data:

id_productSupplier | id_product | availability
1 | 1 | 2
2 | 1 | 5
3 | 2 | 7
4 | 2 | 4
5 | 3 | 1

Expected result:

id_productSupplier | id_product | availability
5 | 3 | 1
1 | 1 | 2
4 | 2 | 4

Here what I have so far:

SELECT id_product FROM products GROUP BY id_product ORDER BY availability LIMIT 10

This does not really work, as it first randomly groups id_product and then orders by availability...

So, is there way to first get rid of records with greater availability for particular product_id and then sort it by availability?

I use MariaDB 5.5 (almost same as MySQL 5.5).

This is a "top n per group" problem but within the limits of MySQL.
You need to GROUP first then join back

SELECT
   F.*
FROM
  (
  SELECT
    MIN(availability) AS MINavailability, id_product
  FROM
    foo
  GROUP BY
    id_product
  ) X
  JOIN
  foo F ON X.id_product = F.id_product AND X.MINavailability = F.availability
ORDER BY
    F.availability, F.id_product


Accumulated logs - Log shipping SQL Server If I have a log shipping scenario where a report runs for 30 minutes and the log shipping is set up for not killing the connection, and because of that, the restore will fail,  the question is if all the accumulated transaction logs will be restored the next time the db is available for that (no connections open) ?

The log-shipping process includes 3 steps.

T-Log backup of the primary database
Copy the backup file to the target destination (shared)
Restore to the target database with standby or no-recovery.

If we use stand-by, there are 2 option available disconnect user or go without disconnecting it. If we don't disconnect active sessions restore wouldn't begin until there's no active connection with the database.

In this case, steps 1 & 2 (backup & copy) will execute normally but restore will fail.

The log-shipping process wouldn't delete any backup file until it's older than the defined threshold.

Once there's no connection with the database it will restore all transaction log file greater then last LSN.

Thanks!


customize permission in Cassandra i want to grant user update permission in Table
and deny delete permission for this user.

Is there any way to accomplish this?

I don't think this is possible natively. The Cassandra role-based permissions as shown here show that granting a user MODIFY includes both UPDATE (which is effectively INSERT) and DELETE. You can't select which one you want.

Check out this blog entry I found, though and see if there's anything useful there. It looks like you could do read or write only permissions. Maybe if you can do write-only permissions you can limit the user from being able to delete a table or keyspace.

Hope this helps point you in the right direction.

-em


What is the "other miscellaneous information" in the Shared Global Area (oracle database)? I'm studying Oracle database ,and it says here that the SGA contains these data structures :

Database buffer cache
Redo log buffer
Shared pool
Java pool
Large pool (optional)
Data dictionary cache
Other miscellaneous information

What is that "    Other miscellaneous information" that is referred to?

thanks

That "other stuff" is everything else that the database needs to run.

That's a whole lot of things, most of which you'll never need to care about at all. If you're curious to see what the different memory areas are in your system, you can query the v$sgainfo view and take a peek. Warning: there's a lot of them (1058 entries on a 12c database I'm connected to right now).

For instance, you'll find a "resize operation history" bucket which should hold the information about dynamic resizing operations (which you can query via v$sga_resize_ops view).

Not all of these are documented (mostly because you don't actually care about the majority), but you'll find information about some of them scattered around the web (and occasional bugs related to some of them) if you're curious.

If you're interested about Oracle's memory architecture, the Concepts Guide's Memory Architecture sections is quite detailed already, you'll rarely need to mess with things that aren't mentioned there.


MySQL Service is not starting on linux I am having a problem with my database it does not want to start :(

the server is running on linux centos 6.2

it was working until I emptied the logs I don't know if this have anything to do with my problem !

the log says the following :

131212 15:52:05 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
131212 15:52:06  InnoDB: Initializing buffer pool, size = 8.0M
131212 15:52:06  InnoDB: Completed initialization of buffer pool
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
131212 15:52:06  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
131212 15:52:06  InnoDB: Started; log sequence number 2 2565027428
131212 15:52:06 [ERROR] Fatal error: Can't open and lock privilege tables: Can't find     file: 'db' (errno: 2)
131212 15:52:06 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Edit

here is my "my.cnf" :

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

user=mysql
max_connections=1500
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

also this is the output of ls -l /var/lib/mysql/ :

-rwxr-xr-x 1 mysql mysql 1512046592 Dec 12 17:02 ibdata1
-rwxr-xr-x 1 mysql mysql    5242880 Dec 12 17:19 ib_logfile0
-rwxr-xr-x 1 mysql mysql    5242880 Dec 11 09:54 ib_logfile1
drwxr-xr-x 2 mysql mysql       4096 Dec 12 09:02 mysql
drwxr-xr-x 2 mysql mysql      12288 Dec 12 17:15 nagios
drwxr-xr-x 2 mysql mysql      20480 Dec  9 09:05 nagiosql
drwxr-xr-x 2 mysql mysql       4096 Oct 27 10:45 test

any suggestions?

I was able to solve this problem by doing the following :

Move all the contents of my data directory (in my case /var/lib/mysql/ ) to a safe place.
Execute the command service mysqld start, this will:

recreate two DBs:

mysql
test

recreate ibdata1, ib_logfile0, and ib_logfile0

Copy the contents that you backed up (in step 1) from the safe place to the data directory again, of course, except both mysql and test directories.
Using the following command mysqladmin -u root -p'' password yournewpass reset the password of your root user.

An Advice:

Always make sure you make a backup of your databases structure and data to avoid looping in such loops, just delete every thing, re-install your engine, and restore your data :)


SELECT vs COPY (SELECT) TO STDOUT I need to read more rows than fit in memory, resonably fast. I see two options:

SELECT ... where my bindings use a cursor to stream the rows;
COPY (SELECT ...) TO STDOUT (FORMAT binary) where my bindings decode the binary format into rows.

What are the theoretical differences in performance and behaviour between the above approaches, or are they effectively identical?

As far as the server and the network protocol are concerned, the result of a query and the binary COPY protocol are pretty much identical.

So you would choose COPY only if your client's PG library handles it better in your situation.


which design better when use foreign key instead of a string to store a list of id Possible Duplicate:
  Is using multiple foreign keys seperated by commas wrong, and if so, why?

I'm building online examination system. I have designed to table, Question and GeneralExam. The table GeneralExam contains info about the exam like name, description, duration,... Now I would like to design table GeneralQuestion, it will contains the id of questions belongs to a general exam.

Currently, I have two idea to design GeneralQuestion table:

It will have two column: general_exam_id, question_id.
It will have two column: general_exam_id, list_question_ids(string/text).

So, I would like to know which designing is better, or Pros and Cons of each designing. I'm using Postgresql database.

As ypercube and marc_s pointed out, it's best to have a 2-column "mapping" table to store which questions are used by which general exams.

This approach provides the following benefits:

Foreign Keys can then be used to ensure data integrity
Queries are much easier to write (eg: find all general exams that use question 17)
Extending the database schema at a later date (to support new features) will be much easier
The schema will be easier to understand by new/other developers/DBAs since it confirms to best practice