Postgres streaming replication between hosts with different timezone I am running Postgres 9.1 on Windows 2012 servers. I am using postgres's streaming replication to replicate data between 2 servers.

It has so happened that the time zone of the slave has not been set correctly. The master time zone is UTC+4:00 where as the slave is on UTC-5:00.
The master time zone is correct whereas slave timezone is incorrect.

Would like to know -

1) what will be the effect of this on fields of type "timestamp with timezone"?

2) what will be the effect of this on fields of type "timestamp without timezone"?

3) what will happen to replication if we change the slave timezone?

Thanks for your insights.

what will be the effect of this on fields of type "timestamp with timezone"?

They're converted from their internal UTC representation to UTC-5 at the moment. They'll be converted using UTC+4 after. So applications will see a 9-hour time shift if they're ignoring the time zone. If they respect the time zone reported in the timestamp then there will be no effect.

One notable exception to this is where the timestamp is truncated to a date. The timestamp may fall on a different day in one time zone to in another time zone. Truncation of a timestamp with time zone to a date discards time zone information. So be cautious with date_trunc, casts from timestamp with time zone to date, calls to extract or date_part, etc.

  what will be the effect of this on fields of type "timestamp without timezone"?

Nothing.

  what will happen to replication if we change the slave timezone?

Nothing. Replication is block level. It doesn't care about the timezone setting in the slightest.

This is just the same as changing TimeZone in the master with a SET TimeZone at the SQL level, or in the config file. So you can just try it with a standalone DB.

e.g.

test=> SHOW TimeZone;
    TimeZone
----------------
 Australia/West
(1 row)

test=> CREATE TABLE tztz(tstz timestamptz, ts timestamp);
CREATE TABLE
test=> INSERT INTO tztz(tstz, ts) values (current_timestamp, current_timestamp);
INSERT 0 1
test=> SELECT tstz, tstz::date, ts, ts::date FROM tztz;
             tstz              |    tstz    |             ts             |     ts
-------------------------------+------------+----------------------------+------------
 2015-08-02 20:48:56.664932+08 | 2015-08-02 | 2015-08-02 20:48:56.664932 | 2015-08-02
(1 row)

test=> SET TimeZone = UTC;
SET
test=> SELECT tstz, tstz::date, ts, ts::date FROM tztz;
             tstz              |    tstz    |             ts             |     ts
-------------------------------+------------+----------------------------+------------
 2015-08-02 12:48:56.664932+00 | 2015-08-02 | 2015-08-02 20:48:56.664932 | 2015-08-02
(1 row)

test=> SET TimeZone = 'UTC-4';
SET
test=> SELECT tstz, tstz::date, ts, ts::date FROM tztz;
             tstz              |    tstz    |             ts             |     ts
-------------------------------+------------+----------------------------+------------
 2015-08-02 16:48:56.664932+04 | 2015-08-02 | 2015-08-02 20:48:56.664932 | 2015-08-02
(1 row)

Note that in both cases the timestamp is the same, because 16:48 at UTC+04 is the same as 12:48 at UTC or 20:48 at UTC+8 (Australia/West).

However, when the timezone shift causes the timestamp to be in the prior or next day in that time zone...:

test=> SET TimeZone = 'UTC-12';
SET
test=> SELECT tstz, tstz::date, ts, ts::date FROM tztz;
             tstz              |    tstz    |             ts             |     ts
-------------------------------+------------+----------------------------+------------
 2015-08-03 00:48:56.664932+12 | 2015-08-03 | 2015-08-02 20:48:56.664932 | 2015-08-02
(1 row)

it's all fine with the timestamps still, but the timestamp with time zone truncated to date has moved to a different day.

If you're confused by the offsets seeming backwards that's because PostgreSQL respects the POSIX standard for time zone offsets which are, infuriatingly, backwards to the offsets most people use day to day.


why is granting ALL permissions and privileges to a new role/user for a new database difficult with postgresql? With mysql and am sure countless other database softwares, it is pretty easy to create a new database, create a new user and grant that user to own and be able to do everything on that database
this is NOT the case with postgresql
Every time i am about to create a new database and user on postgresql it is a such a pain to think about because i KNOW i will come across head ache again
Why is postgresql such a pain with uses and database and allowing a user to own a database so the user/role has all permissions
It is crazy to always be getting errors like
permission denied for table sites

How can one create a new database, new user with password and grant ownership of the new database to that new user so the user/role can do EVERY and ANYTHING on that new database? what commands to achieve all these? for postgresql?
Thanks!

There are certainly some differences between PostgreSQL and other databases, such as MySQL. When I first started working with PostgreSQL, I found it to have a lot more friction than the servers I was more familiar with. However, a bit of practice — and a lot of snippets in Evernote — reveals the method to the madness.
That said, to answer your question, there are 4.5 steps to create a database, a user account, and grant all permissions.
From the command line:

Create the user:
$ sudo -u postgres createuser <username>

Create the database:
$ sudo -u postgres createdb <dbname>

The next commands are done in PostgreSQL, so step 2.5 is logging in to the server:
$ sudo -u postgres psql

Assign the user a password:
ALTER USER <username> WITH ENCRYPTED PASSWORD '<password>';

Grant privileges on database:
GRANT ALL PRIVILEGES ON DATABASE <dbname> TO <username> ;

Done and done.
You probably do this already, but a note-taking system like Evernote (or just a well-structured directory with text files) is an indispensable tool for processes like this until muscle memory kicks in


updating 1st 5 digit of a column value in sql I need assistance here for updating 1st 4 digit of a column value

for E.g I have value 999999999 (9 digit) and I need to mask 1st 5 digits with 11111

output should look like 111119999

However, in the same column I have a value called MIS I don't want to touch those value

What query should I use in Oracle?

What I have tried:

I have tried various update query like

UPDATE table SET TAX_ID = replace(TAX_ID, '4', '1111');

Something like this? If value consists of digits only, replace first 5 characters with 1s; else, do nothing.

SQL> with test (col) as
  2    (select '999999999' from dual union all
  3     select '123456789' from dual union all
  4     select 'MIS'       from dual union all
  5     select 'ab12345cc' from dual
  6    )
  7  select col,
  8    case when regexp_like(col, '^\d+$') then 11111 || substr(col, 6)
  9         else col
 10    end result
 11  from test;

COL       RESULT
--------- ---------------------
999999999 111119999
123456789 111116789
MIS       MIS
ab12345cc ab12345cc

SQL>


With window functions, this would be almost trivial to add as a column in a query:

COUNT(*) OVER (PARTITION BY status) AS status_count

Since MySQL hasn't implemented them (you'll have to wait, they may be in the next version), you'll need something more complex now. It can be done with a derived table - to calculate these counts - and then join to the base table:

SELECT
    t.id, t.box_name, t.diIndex, t.diMode, t.diStatus, t.status,
    g.status_count, t.last_updated
FROM
    tableX AS t
  JOIN
    ( SELECT status, COUNT(*) AS status_count
      FROM tableX
      GROUP BY status
    ) AS g
  ON
    t.status = g.status ;


How to delete duplicate rows in merge replication Somehow two rows in a table that is party to merge replication are identical, including their rowguid. I have no idea how this happened, it is only with two rows in my entire database (which is used in production).

No matter how I try to delete them, I get the following error:

  Msg 2601, Level 14, State 1, Procedure MSmerge_del_12B5E838BB91458D81AD66DD7EB5ABDC, Line 46
  Cannot insert duplicate key row in object 'dbo.MSmerge_tombstone' with unique index 'uc1MSmerge_tombstone'. The duplicate key value is (7094001, df2e61b2-3d8e-e511-84bb-00155d00c1da).
  The statement has been terminated.

This is somewhat expected, but I have no idea how to get around it. I'd very much like to avoid dropping anything big (i.e. replication, tables, etc.). This is an isolated case and I don't expect it to happen again so I don't mind doing a manual work-around.

Can anyone provide some insight into this?

Thanks!

The duplicate rows in the MSmerge_tombstone occurs when you restore the database with KEEP_REPLICATION bit.

Its a good practice to configure replication from scratch since you never know that you run into some unknown issue just like you did.

As confirmed by OP - drop the index uc1MSmerge_tombstone, delete the offending row and   recreating the index fixes the problem.

Remember that you have to be caution when fiddling with MSmerge* tables.


Why would writing the CTE to an actual table provide a performance improvement? I'm a huge fan of CTE's. They are the best. You know it, I know it, we all know it.

Today, I got burned with a query that is about 1000x faster if the CTE is written to a real table.

The query itself is in the link, but I suspect it isn't important. To simplify, I needed a list of all the claims in an insurance policy where one claim met a certain condition (cat = 1).

A simplified version looks like this:

    with cat_claims as (select distinct pol_nbr from claims where cat = 1)

    select * from claims c
    inner join policies p on p.polnbr = c.polnbr
    inner join cat_claims cat on c.pol_nbr= cat.pol_nbr

which was super duper slow. If I rewrite it to look like this:

    with cat_claims as (select distinct claim_nbr from claims where cat = 1)

    select * into cat_claims
    from cat_claims

    select * from claims c
    inner join policies p on p.polnbr = c.polnbr
    inner join cat_claims cat on c.pol_nbr = cat.pol_nbr

    drop table cat_claims

it's way, way faster. This is odd to me.

Question Why would writing the CTE to an actual table provide a performance improvement?

Slow plan:

https://www.brentozar.com/pastetheplan/?id=HkyrpAtH4

Fast Plan:

https://www.brentozar.com/pastetheplan/?id=ByGkAAKrN

Even though they are estimated plans and not the actual ones, the difference to me looks like it is coming from joining the tables:
RATING_CONTRIB_LOSS,
RATING_CONTRIB_USR,
cmbgrp,
rating_revision_set,
and the Rating table from the ratings CTE.

Joining these tables spawn huge table spools (lazy spool), that  could be even bigger on the actual plan.

For every row in the top part of the nested loops operator, the bottom result set is scanned resulting in

3361 rows * 3359 in the first table spool --> Nested loops --> Top(1)

and

1541 Rows * 3359 in the second table spool --> Nested loops --> Top(1)

These table spools are not present in either of the two plans in the temp table queries.

What I am getting at is that the query part that inserts into the table is alike to the corresponding query part of the CTE. There are some differences but I think that the major cause of the slowdown is because of the table spools.

An example of a difference is that the full table scan on  RATING_CONTRIB_LOSS in the CTE plan is changed to a scan with a residual predicate in the temp table plan.

CTE

Temp table

And you could argue that the second part of the query that uses the temp table might access the RATING_CONTRIB_LOSS table again. And I would have to agree because it will be doing an expensive key lookup.

However, the second table spool in the CTE plan is also based on a nested loops join with theRATING_CONTRIB_LOSS table, which is not present in the temp table plan, and that is a big plus.

Conclusion

I do believe that the difference in execution time comes from the query using the temp table's result in such a way that costly operators are not used. More specifically the Table spools. I would need the actual plan to be 100% sure.

As a sidenote, rewriting CTE's into temp tables or just splitting up queries in general can in many cases give better execution times.

It is easier to guess how many apples are in a bag with apples and pears than guess how many apples are in a bag with apples, pears, mango's, oranges, peaches, .... (Yes the fruit = tables).

I would also use actual #temp tables to store your intermediary results in.

Comparison CTE & Temp table insert query parts

Plan with CTE part 1

Plan with CTE part 2

Plan with temp table part 1

Difference on RATING_CONTRIB_LOSS & Hash match join <> Nested Loops Join. Rest is very much alike, Same operations (2x) on Rating table


Mixed search in multiple columns I have a table which has a particular column of type JSON, where some user's contact information are stored. I can alter the table and add new columns with cached values to increase search performance if needed (this is a new system, which is in the state of designing/at the beginning of implementation).

The top-level keys in the JSON are 'name' (both first and last name as one value), 'address' (a hash containing street, state, zip etc) and 'other' (tel, email, www etc). These are the three categories of information we are storing of customers.

One property of this JSON column is, that I really can't tell what keys will be there (that's why it is a JSON, so it remains flexible in this sense), but I need to search all the values.

My main problem is, that I've been told to implement a search functionality with one single input (like google), which would search in every value, even combined (so if u search 'John Smith Slovakia', it would find customers with the name John Smith who lives in Slovakia').

Another required property is that it has to support partial searches, i.e. if you wan't to find every customer with the last name Smith who live in street named 'Somelongname', then it would be enough to type in 'smith somelong' and it would still find them.

I've looked into full text search and it looked fine, except it did not really support partial searches.

Is there a better, more efficient solution, than searching every input word separately accross all the values using LIKE '%search_token%' and then merging the results for every search token into the final result?

BTW I'm using PostgreSQL.

Pattern matching and operators
Full text search is not the right tool for pattern matching (and possibly even fuzzy, fault tolerant input). Typically, trigram-similarity search with the % operator is the superior approach here. You need to install the additional module pg_trgm once per database:

How to create an index to speed up an aggregate LIKE query on an expression?
PostgreSQL LIKE query performance variations

Possibly combine that with LIKE (also supported by trigram indexes), and text search like you had in mind. Related answer where text search and similarity are combined:

PostgreSQL FTS and Trigram-similarity Query Optimization

Overview of pattern-matching functionality in Postgres:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Database design
json is a very bad choice for your intended functionality. It is very inefficient to have to plow through huge columns holding everything in a semi-structured big string (json).
Consider normalizing your schema, which would result in superior performance (when done properly).
If you are hellbent on json, consider the new jsonb in the upcoming (and soon to be released) Postgres 9.4. More (last paragraph):

How to remove known elements from a JSON[] array in PostgreSQL?

And be sure to use functional indexes like:

How to create an index on an integer json property in postgres
Index for finding an element in a JSON array

Further optimization is likely possible. But that would require more detailed information including table definitions, cardinalities, sample data and typical queries.


Backing up a MySQL server with mixed character sets This may sound like a really simple question, but I've not found a straight answer with a lot of googling!

I have a MySQL database server which has databases with various different character encodings (largely a mix of latin1 and utf8)

Are there any recommendations when using mysqldump to back up the the entire server (one db at a time), when for each individual database I will not know what the character set is that is being used?

(I have a bash script which gets all the DBS on the server, and then runs mysqldump individually on each one - copied below)

I know I can set the default encoding to use with mysqldump by specifiying that as an argument to the mysqldump command - should I just be setting that to UTF-8? Will that be OK for the latin encoded DBs?

#!/bin/bash
mkdir `date -I`;
dblist=`mysql -h myhosthere -u myuser -pmypassword -e "show databases" | sed -n '2,$ p'`
for db in $dblist; do
   mysqldump -h myhosthere -u myuser -pmypassword $db | gzip --best > home/user/`date -I`/$db.sql.gz
done;

I think the safest thing is to specify dumping in utf8mb4.  Hopefully all the characters you have in all the character sets will convert correctly to (and from, when reloading) that charset.


the cache hit rate using pg_statio_user_tables I read from this blog that a small 20% of the data in your database is getting the vast 80% of reads. That means a small portion of the data in the database is frequented. Hence, you want to ensure that 20% is accessed in the cache (main memory) as opposed to disk. Ultimately, you want to have a cache hit rate of 99%. The blog provides this command to find the cache hit rate:

SELECT
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit)  as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM
  pg_statio_user_tables;

Ok, so I am expecting a number close to 99%, but now this is the number I got:

#   pg_statio_user_tables;
   heap_read   |   heap_hit    |         ratio
---------------+---------------+------------------------
 2214811975866 | 1791164728361 | 0.44712310145763220493
(1 row)

0.44? That is not even close to 99%. That must mean that most of the hits are happening on disk and the cache is virtually nonexistant. What could be causing this issue?

The formula given only tells you what the hit rate was for hits within the PostgreSQL buffer cache (shared_buffers).

But maybe all of the misses were pulled from the OS's file cache, not actually from disk.  PostgreSQL provides no direct way for you to know that, as it keeps out of the kernels business as much as possible.  (Some other DBMS by-pass the OS's file cache, but PostgreSQL doesn't, it runs on top of it.)

One way to get a feel for it on recent versions if you run with track_io_timing turned on would be to look at

select sum(blks_read)/sum(blk_read_time) from pg_stat_database

For example, I get 110 blocks read per millisecond of read time.  Since my disk are not nearly that fast, I know that almost all of that was read from the OS file cache, not actually from disk.

If you have shared_buffers set to a small fraction of the system RAM, as is usually recommended for large systems, then you can get better-looking hit ratios by increasing shared_buffers to be a large fraction of the RAM.  Note that this is unlikely to give you better performance, it will just make some arbitrary numbers look spiffy.

In any case, hit ratios are part of "mythology-based administration".  Maybe 20% of my data is responsible for 80% of my reads.  Maybe 20% of the bloggers' data is responsible for 80% of his reads.  Neither me nor the blogger know whether that applies to your database or not.  Maybe it does, maybe is doesn't.

And even if it does, what if 20% of my data is still far larger than my available RAM?  No amount of tinkering is going to make that fit in the cache.  I would have to buy more RAM, which is expensive and involves down time, all to solve a "problem" that was not actually a problem.

If you have a performance problem, you should investigate what the problem is. If you don't have a performance problem, your time would be better spent practicing disaster recovery, or getting a test system and making a realistic load generator for it (so that you can predict what problems are likely to arise on your real system using data from the test system, rather than based on myths).  Also, if you are running a version older than 9.2, you should spend time upgrading to at least that version if not newer.  The tools available to diagnose problems once they do occur are getting better all the time, but if you are running an old version you can't use them.


if 0 show " ", if 500 show "R" I'm working on a query in Access 2010 and need help.

If the value is =0 I want it to show as blank,
if the value is <500 I want it to show as "B",
if the value is >500 I want it to show as "R"

Try:

IIf([value] = 0, "", IIf([value] < 500, "B", "R"))

IIf is a function that returns on of two values depending on the outcome of the conditional.


Wrong estimated number of rows I'm trying to identify an issue I sometime experience on a stored procedure.

I have to say that the stored procedure uses an UDF in the select clause (perhaps that's the cause of the problem) but anyway, whether it's not, here below a part of the execution plan:

where both the output estimated rows of the Hash Match and the estimated of the Index_Seek on the non clustered index IX_Job_OperationID are completely wrong:

I've tried to update the statistic related to the index IX_Job_OperationID, even with fullscan option, but it didn't help.

UPDATE STATISTICS Job [IX_Job_OperationID] with fullscan

I'm also using the recompile option in the stored because given the parameters provided the dataset involved may change a lot.

Can someone point me to the right direction and help me understand why even though statistics have been updated the Estimated values are so far from Actual?

Here a link to the actual execution plan

This execution plan causes a total of 8,610,665 logical reads, while other better plan that somehow are chosen can be around 122,692

Based on your description of the problem it is unlikely that the UDF is the cause of the cardinality estimation errors. You can try commenting out the UDF and checking if the join estimate changes if you want to test that.

It is possible to experience cardinality estimation errors even when statistics have been updated with FULLSCAN. The histograms created by SQL Server may not model your data in a way for the optimizer to create good enough plans, SQL Server may not have histograms that exactly match the joined data (you could be filtering on the inner and/or outer table), you may be joining on multiple columns which complicate estimations, and so on. For example, Microsoft changed some of the join cardinality estimate calculations with the new CE released in SQL Server 2014. Some tables will have data that better matches the assumptions for the legacy CE and some tables will have data that better matches the assumptions for the new CE.

For the first estimation issue (38999 estimated rows versus 701680 actual rows), based on what you've provided I can only recommend reading about join cardinality calculations. Microsoft released a white paper for the new cardinality estimator in SQL Server 2014. I also know of a blog post that dives into the internals of join cardinality quite a bit. As the question is written there are too many possible causes to give a good answer. Please post an actual execution plan if you'd like further guidance.

For the second estimation issue (1 estimated row versus 1546894 actual rows), note that the estimated number of rows is for a single iteration of the inner loop for the join. The actual number of rows is for all rows returned from the table over all iterations of the join, so they will not often match for nested loop joins. What you are seeing is common and not necessarily a sign of a problem.


Import (imp) not showing final report stats/results I'm trying to execute an import of a dmp file an not obtaining the final results of the import. It's really weird because i obtained really big files (nohup.out and imp_xxx.log) with the following content :

Column 98 1
Column 99
Column 100
Column 101 0
Column 102 0
Column 103
Column 104
Column 105
Column 106 0
Column 107 0

Expected:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing CPEDF33's objects into CPEDF33
. . importing table                 "x"            0 rows imported
. . importing table                 "x1"           0 rows imported
. . importing table                 "x2"           0 rows imported
. . importing table                 "x3"     2599617 rows imported
. . importing table                 "x4"     2244766 rows imported
. . importing table                 "x5"     1561223 rows imported
. . importing table                 "xx5"         20 rows imported
. . importing table                 "xx4"    1931263 rows imported
. . importing table                 "xx3"       2167 rows imported
. . importing table                 "xx2"    4044280 rows imported
. . importing table                 "xx1"    4126340 rows imported
. . importing table                 "x8"        2453 rows imported
. . importing table                 "x7"           0 rows imported

Imp command:

nohup imp MINE/Hi2013* file=H_FULL_20140212.dmp log=imp_H__FULL_20140212.log FROMUSER=MINE TOUSER=MINE STATISTICS=NONE ROWS=Y IGNORE=Y &

I imagine the unescaped * character in your password is causing problems in the shell.

Escape it and you should be ok.


how to find out what process(es) caused the autogrowth of a database or a database file? I have a process(es) hungry for tempdb, but I am struggling to identify this process.

any ways I could achieve this?

  We have been alerted as Tempdb has now grown into the space you
  reclaimed on the T:\ drive earlier. Again, there is 10MB remaining on
  disk. I can see a number of auto growth events across the data files
  on the REP instance starting at 10:18AM this morning. In total there
  were 330 auto growth events at 512MB each, totalling 168GB.

  It is difficult to highlight after the fact what has used this space
  in Tempdb, are you aware of any processes that may be using Tempdb in
  this manner today?

/------------------------------------------------------------\

Identifying How Often an Auto-growth Event has Occurred

When SQL Server performs an auto-grow event, the transaction that triggered the auto-grow event will have to wait until the auto-grow event completes before the transaction can finish. These auto-growth events cause your performance to degrade a little when an auto-grow event is taking place. For this reason it is best if you can size your database appropriately so auto-growth events rarely occur.

If you are interested in how often an auto-growth event occurs on your system you can capture those events using a trace. By knowing which databases are performing auto-growth events allows you to adjust those database file growth properties so they will perform auto-growth events less frequently. You can use the profiler “Data File Auto-grow” and/or the “Log File Auto-grow” events to track these database auto-growth events. If you are running SQL Server 2005 or above, both these auto-grow events are already being captured by the default trace. If you haven’t turned off the default trace then you can use the default trace file to find these auto-grow events. If you have turned off the default trace you can either enable it, or setup a new profiler trace to capture the “Data File Auto-grow” and “Log File Auto-grow” events.

The default trace logs to a file. I have provided the code in Listing 4 to show you how to extract all the auto-growth events from the default trace files. If you create your own profiler trace session to capture these auto-grow events then you will need to modify this script to meet your profiler trace settings.

https://www.simple-talk.com/sql/database-administration/sql-server-database-growth-and-autogrowth-settings/

marcelo miorelli
11-mar-2014

\*--------------------------------------------------*/
DECLARE @filename NVARCHAR(1000);
DECLARE @bc INT;
DECLARE @ec INT;
DECLARE @bfn VARCHAR(1000);
DECLARE @efn VARCHAR(10);

-- Get the name of the current default trace
SELECT @filename = CAST(value AS NVARCHAR(1000))
FROM ::fn_trace_getinfo(DEFAULT)
WHERE traceid = 1 AND property = 2;

-- rip apart file name into pieces
SET @filename = REVERSE(@filename);
SET @bc = CHARINDEX('.',@filename);
SET @ec = CHARINDEX('_',@filename)+1;
SET @efn = REVERSE(SUBSTRING(@filename,1,@bc));
SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename)));

-- set filename without rollover number
SET @filename = @bfn + @efn

-- process all trace files
SELECT
  ftg.StartTime
,te.name AS EventName
,DB_NAME(ftg.databaseid) AS DatabaseName
,ftg.Filename
,(ftg.IntegerData*8)/1024.0 AS GrowthMB
,(ftg.duration/1000)AS DurMS
FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id
WHERE (ftg.EventClass = 92  -- Date File Auto-grow
    OR ftg.EventClass = 93) -- Log File Auto-grow
ORDER BY ftg.StartTime

The only way to find out what process caused the autogrowth is to use Extended events esp. EVENT --> sqlserver.database_file_size_change & sqlserver.databases_log_file_size_changed and ACTION --> sqlserver.sql_text.

Looks like @DBA_ANDY already did the hard work of writing an XEvent

-- Original Author : @DBA_ANDY http://nebraskasql.blogspot.com/2016/06/finding-file-growths-with-extended.html
-- Modified by : Kin to filter on tempdb autogrowth events
SET NOCOUNT ON
/* Create Extended Events Session */
IF EXISTS (SELECT 1 FROM master.sys.server_event_sessions WHERE name = 'DemoFileSize')
 DROP EVENT SESSION [DemoFileSize] ON SERVER
GO
CREATE EVENT SESSION [DemoFileSize] ON SERVER
ADD EVENT sqlserver.database_file_size_change(SET collect_database_name=(1)
    ACTION(package0.collect_system_time,sqlos.task_time,
 sqlserver.client_app_name,sqlserver.client_hostname,
 sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,
 sqlserver.server_instance_name,sqlserver.session_id,
 sqlserver.sql_text,sqlserver.username)
 WHERE ( [database_id] = ( 2 ) -- This is tempdb database id
),
 /* Note -  predicate/filter - will collect only for tempdb */
ADD EVENT sqlserver.databases_log_file_size_changed(
    ACTION(package0.collect_system_time,sqlos.task_time,
 sqlserver.client_app_name,sqlserver.client_hostname,
 sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,
 sqlserver.server_instance_name,sqlserver.session_id,
 sqlserver.sql_text,sqlserver.username)
 WHERE ( [database_id] = ( 2 ) -- This is tempdb database id
  )
 /* Note -  predicate/filter - will collect only for tempdb */
ADD TARGET package0.event_file(SET filename=N'D:\XEvent_logs\DemoFileSize.xel',-- change HERE !!
 max_file_size=(500),max_rollover_files=(10))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
 MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,
 MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

ALTER EVENT SESSION [DemoFileSize] ON SERVER
STATE = START;
GO

shred the XEvent xml ...

SELECT
Case when file_type = 'Data file' Then 'Data File Grow' Else File_Type End AS [Event Name]
, database_name AS DatabaseName
, dateadd(minute, datediff(minute, sysutcdatetime(), sysdatetime()), timestamp1) as LocalTimeStamp
/* added the timestamp and in XE is UTC - this code converts it to local server time zone */
, file_names
, size_change_mb
, duration
, client_app_name AS Client_Application
, client_hostname
, session_id AS SessionID
, sql_txt   ----------------> This is the process TSQL text !
, sql_username
, Is_Automatic

FROM
(
SELECT
(n.value ('(data[@name="size_change_kb"]/value)[1]', 'int')/1024.0) AS size_change_mb
, n.value('(@timestamp)[1]', 'datetime2') as timestamp1
, n.value ('(data[@name="database_name"]/value)[1]', 'nvarchar(50)') AS database_name
, n.value ('(data[@name="duration"]/value)[1]', 'int') AS duration
, n.value ('(data[@name="file_type"]/text)[1]','nvarchar(50)') AS file_type
, n.value ('(action[@name="client_app_name"]/value)[1]','nvarchar(50)') AS client_app_name
, n.value ('(action[@name="session_id"]/value)[1]','nvarchar(50)') AS session_id
, n.value ('(action[@name="client_hostname"]/value)[1]','nvarchar(50)') AS Client_HostName
, n.value ('(data[@name="file_name"]/value)[1]','nvarchar(50)') AS file_names
, n.value ('(data[@name="is_automatic"]/value)[1]','nvarchar(50)') AS Is_Automatic
, n.value ('(action[@name="sql_text"]/value)[1]','nvarchar(500)') AS sql_txt
, n.value ('(action[@name="username"]/value)[1]','nvarchar(50)') AS sql_username

FROM
(
SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file(
N'D:\XEvent_logs\DemoFileSize*.xel' -- CHANGE HERE !!
, NULL
, NULL
, NULL
)
) AS Event_Data_Table
CROSS APPLY event_data.nodes('event') AS q(n)) xyz
ORDER BY timestamp1 desc

Below is the output:

Important things :

You can use my modified script for reporting on autogrowth events.
Properly size your data and log files, so that autogrowth events do not occur on a frequent basis. Autogrowth should be as an emergency helper !
Enable Instant file initialization, so that data file autogrowth events can be fast.
Use predicates in XEvent e.g. on certain important databases or on the size_change_mb to log only events that meet those predicates.
Use sensible target file size e.g. shredding a 1GB XML will cause you more trouble than shredding a 50 MB XML.
Refer to This is how to shred Extended Event XML faster or How can I shred this Extended Events XML as fast as possible?


PostgreSQL: The database cluster initialization failed Both C:\PostgreSQL and C:\PostgreSQL\data have postgres user with full access and admin rights.

I ran postgresql-9.1.2-1-windows.exe from postgres user as admin. Target C:\PostgreSQL

Every way I try, I get "The database cluster initialization failed."

Questions

Is it OK to run everything w/o being a Windows Service?
Is there a work-around to install as Windows Service?

I am trying to setup PostGIS to work with GeoDjango.

I was able to manually install PostGIS. New to PostgreSQL and I am having a crisis of confidence over all of this. Coming from MySQL to PostgreSQL for the first time.

Pertinent log output from C:\Users\Larry\AppData\Local\Temp\install-postgresql.log:

WScript.Network initialized...
Called IsVistaOrNewer()...
    'winmgmts' object initialized...
    Version:6.1
    MajorVersion:6
Ensuring we can read the path C: (using icacls) to Larry:
    Executing batch file 'radA3CF7.bat'...
    Output file does not exists...
Called IsVistaOrNewer()...
    'winmgmts' object initialized...
    Version:6.1
    MajorVersion:6
Ensuring we can read the path C:\PostgreSQL (using icacls) to Larry:
    Executing batch file 'radA3CF7.bat'...
    Output file does not exists...
Called IsVistaOrNewer()...
    'winmgmts' object initialized...
    Version:6.1
    MajorVersion:6
Ensuring we can read the path C:\PostgreSQL\data (using icacls) to Larry:
    Executing batch file 'radA3CF7.bat'...
    Output file does not exists...
Called IsVistaOrNewer()...
    'winmgmts' object initialized...
    Version:6.1
    MajorVersion:6
Ensuring we can write to the data directory (using icacls) to  Larry:
    Executing batch file 'radA3CF7.bat'...
    Output file does not exists...
Failed to ensure the data directory is accessible (C:\PostgreSQL\data)
    Executing batch file 'radA3CF7.bat'...
    Output file does not exists...
Called Die(Failed to initialise the database cluster with initdb)...
Failed to initialise the database cluster with initdb

Suggestions?

I had the same issue installing 9.1.4 on Windows 7. I managed to find a solution online that worked.

The steps I followed are:

Uninstall PostgreSQL
Delete the postgres user if it still exists.

net user postgres /delete

Create the postgres user with a password you can remember

net user /add postgres <password>

Add the postgres user to the Administrators group

net localgroup administrators postgres /add

Add the postgres user to the Power Users group

net localgroup "power users" postgres /add

Run a command window as the postgres user

runas /user:postgres cmd.exe

Run the install file from within the command window.

C:\Download\postgresql-9.1.4-1-windows.exe

This should run the installation successfully.
Remove the postgres user from the Administrators group.

net localgroup administrators postgres /delete


Is there any performance advantage in using CTE for ROW_NUMBER query I have following queries to select latest order from each user. Is there any performance advantage in the query using CTE? For my current tables, both are giving same execution plan and time.

Note: I am using SQL Server 2012

DECLARE  @Person TABLE (PersonId INT, NameFirst VARCHAR(100), NameLast VARCHAR(100))
DECLARE @Order TABLE (OrderId INT, PersonId INT, OrderDateTime DATETIME)

--Query1
SELECT A.PersonID,A.NameFirst,A.NameLast, A.OrderID AS LastOrderID,A.OrderDateTime AS LastOrderDateTime
FROM
(
    SELECT P.PersonID,P.NameFirst,P.NameLast,O.OrderID,O.OrderDateTime,
           ROW_NUMBER() OVER(PARTITION BY O.PersonID ORDER BY O.PersonID,O.OrderDateTime DESC) AS RowNumber
    FROM @Person P
    LEFT OUTER JOIN @Order O
        ON P.PersonId = O.PersonId
)A
WHERE RowNumber = 1
ORDER BY PersonID

--Query2
;WITH CTE AS
(
    SELECT  P.PersonID,P.NameFirst,NameLast,O.OrderID AS LastOrderID,O.OrderDateTime AS LastOrderDateTime,
            ROW_NUMBER() OVER(PARTITION BY O.PersonID ORDER BY O.OrderDateTime DESC) AS RowNumber
    FROM @Person P
    LEFT OUTER JOIN @Order O
        ON P.PersonId = O.PersonId
)
SELECT A.PersonID,A.NameFirst,A.NameLast, A.LastOrderID, A.LastOrderDateTime
FROM CTE A
WHERE RowNumber = 1
ORDER BY PersonID

The answer is in your question:

  For my current tables, both are giving same execution plan and time.

When two plans are identical, neither of the queries that produced them can possibly have any performance advantage over the other. The times being in your case identical as well only confirms that.

The above should at least be true for actual (as opposed to estimated) plans, but I believe it is perfectly safe to assume that simply rewriting a derived table as a Common Table Expression without incurring any other change is not going to either speed up or slow down your query a tiniest bit in SQL Server.

That is not to say a CTE cannot have advantages of other nature. One of the most commonly (although, perhaps, still not universally) acknowledged advantages of a CTE over a derived table is readability. I will not go into much detail here as this is beyond the scope of the question but basically, a CTE lets you split your query into logical steps more cleanly, as well as enables you to follow the logic of an unfamiliar script more naturally.


Oracle 10g Database Locking Session Issue I have a problem with locking and not timing out in one of my Oracle databases. This session locks occur on certain transactions that come from Oracle Forms users. For instance, when some user requests to make a change on a locked object the transaction waits for the locked object to be released, but it waits indefinetely until killed by alter session.

I've tried the same in my testing database and it does kill the locked session after 60s, so I thought it has to be a configuration issue.
I've checked the v$parameters DISTRIBUTED_LOCK_TIMEOUT and it is set to 60s in both databases.

Does anyone know where is the "locking session/transaction timeout" set?

Any help is greatly appreciated.

You can change distributed_lock_timeout at the instance level alter system set distributed_lock_timeout=<sec> scope=spfile;
But what you are describing is a session modifiable parameter in 11g called ddl_lock_timeout and I’m not aware of anything that replicates that in 10g. The fact that it runs as expected in one environment and not the other means you might want to pull a list of parameter settings between the two and see what’s different.


How to remove duplicates in col1 and get group MAX in col2? / mysql Given the following table:

col1 Col2
A    1
B    1
C    1
B    2
B    3
A    0
C    5

how do I write a SELECT query (I just assumed has to be SELECT) that returns the unique items of col1 and the max values of col2, like this:

A   1
B   3
C   5

SELECT col1, MAX(col2) FROM mytable GROUP BY col1


Securing MySQL backup process I'm running my MySQL backups through cron scheduled every night, but the thing is in cron the username and password is given for it run, is there any other way I can securely run the backups? where I can credentials remain secure only to the DBAs and not to anyone else? Btw, I use percona server. But I use mysqldump for backups, just that it has to be secure.

Thanks!

You can create a .my.cnf file in your home directory then store the user/password info on the [mysqldump] section.

vi ~/.my.cnf

[mysqldump]
user = dump_user
password = dump_password

Then, with your mysqldump command, use the --defaults-extra-file option to specify explicitly your conf file has your are in crontab.

/usr/bin/mysqldump --defaults-extra-file=/home/max/.my.cnf  --single-transaction  --all-databases >/space/mydump.sql

Max.


Speeding up conversion of MyISAM to InnoDB I've got a mysql 5.1 server with a database of approximately 450 tables, taking up 4GB . The vast majority of these tables (all but 2) are MyIsam. This has been fine for the most part (don't need transactions), but the application has been gaining traffic and certain tables have been impacted because of table-locking on updates. That's the reason 2 of the tables are InnoDB now.

The conversion on the smaller tables (100k rows) don't take long at all, causing minimal downtime. However a few of my tracking tables are approaching 50 million rows. Is there a way to speed up an ALTER TABLE...ENGINE InnoDB on large tables? And if not, are there other methods to convert minimizing downtime on these write-heavy tables?

Let me start by saying, I hate ALTER. It's evil, IMHO.

Say, this is your current table schema -

CREATE TABLE my_table_of_love (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
my_value VARCHAR(40),
date_created DATE,
PRIMARY KEY(id)
) ENGINE=MyISAM CHARSET=utf8;

Here's the path I recommend -

Create a new table object that will replace the old one:

CREATE TABLE my_table_of_love_NEW (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
my_value VARCHAR(40),
date_created DATE,
PRIMARY KEY(id)
) ENGINE=InnoDB CHARSET=utf8

Insert all the rows from the old table by name into the new table:

INSERT INTO my_table_of_love_NEW (id,my_value,date_created)
SELECT id,my_value,date_created FROM my_table_of_love;

Smoke test your migration:

SELECT COUNT(*) FROM my_table_of_love_NEW;
SELECT COUNT(*) FROM my_table_of_love;
SELECT a.id,a.my_value,a.date_created FROM my_table_of_love_NEW a
LEFT JOIN my_table_of_love b ON (b.id = a.id)
WHERE a.my_value != b.my_value;

Swap table names so that you can maintain a back up in case you need to rollback.

RENAME TABLE my_table_of_love TO my_table_of_love_OLD;
RENAME TABLE my_table_of_love_NEW TO my_table_of_love;

Proceed to regression testing.

This approach becomes more and more preferable with tables with multiple indexes and millions of rows.

Thoughts?


MySQL: Get number of rows of table with specific name for all databases I am trying to build a mysql query (I want to use it in phpMyAdmin) to get the number of rows of all tables where the table name contains the word "user" or "member". But instead of questioning a single db, I want to run this on all dbs.
As a result, i want to have a table with the columns: DB name, Table Name, Numer of Rows.

Any tips to achieve that would be much appreciated!

You don't need to query all databases, and anyway you can't. You need to query only one database called information_schema, which contains all metadata about your databases (table structures, etc).

Here is the query you want:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS
    FROM information_schema.TABLES
    WHERE
          TABLE_NAME LIKE '%user%'
          OR TABLE_NAME LIKE '%member%';

Notes:

You don't need any permission on information_schema. But if there is some table you can't access (because you don't have permissions) you will not see it in the results.
The number of rows is MySQL estimation. It is never exact, sometimes it is even inaccurate. If this is not enough for you, you will need to run a query for each table to count the rows. There is no way to get the actual row count with 1 query.
I write the name of system databases, tables and columns with their actual case (information_schema is written lower case). For some reason, this is different than the convention used by the documentation and most technical articles (INFORMATION_SCHEMA). Just use the practice you prefer.


Change posgresql 9.3 slave roll password I need to change the password on my slave posgresql server, because the person who set it up left the password the same as the password for the super user account on our other server.

When I run psql as the super user on the slave server, and then attempt to execute the command

ALTER USER username PASSWORD 'newPassword';

I get the error:

  ERROR: cannot execute ALTER ROLE in a read-only transaction

When I attempt to change the transaction with:

SET TRANSACTION READ WRITE;

I get the error:

  ERROR: cannot set transaction read-write mode during recovery

I understand that the slave server can only do reads, though how do I bypass this to change the roles password, or pull the slave temporarily out of the recover mode?

the person who set it up left the password the same as the password
  for the super user account on our other server

If the other server is the master, it's normal because having different passwords in the replicated database is not possible.
PostgreSQL replicates everything from the master, including user accounts and their passwords. There's no way to divert that temporarily.

If someone needs to be superuser on the secondary without knowing this password, a different authentication method should be used on this server, such as Certificate Authentication, or external methods (ldap, radius...) . As a configuration file, the pg_hba.conf can be different on the slave.


MAXDOP or 1 only? Or sql can use a value between 1 and configured maxdop Is there a way for sql server to use no of cores which is between 1 and the configured MAXDOP?

For example if maxdop is 8. Is it possible for sql to use 3? Lets say that resource gov, hints, and any other control mechanisms are not in place.

Or is it simple 1 for singlethreaded and 8 for parellelized?

When you set MAXDOP = 8 this is not saying that the Degree of Parallelism will be 8 cores for your process.  It means that no more than 8 cores will be used for your process.

It could use only 1 core up to 8 cores with that setting.  This all depends on many other usage factors such as when several long processes are running on your server.  The SQL Server tries to manage the processes so that every running process gets some CPU time allocated to it very regularly.

https://www.brentozar.com/archive/2014/11/many-cpus-parallel-query-using-sql-server/

Brent's post also verifies that Paul White has audited Kendra Little's post as correct. (Just in case you want more verification.)

Again from Paul White from a discussion at http://www.sqlservercentral.com:

  The instance-wide MAXDOP setting sets the maximum possible degree of
  parallelism. A setting of zero (the default) allows SQL Server to use
  as many threads as there are processor cores that it is configured to
  use.

  The cost threshold for parallelism is only used by the optimizer.
  If the cost threshold is set to 5, the query must have an estimated cost of
  at least 5 before the optimizer will look for a parallel plan
  alternative.

In a case like that a MAXDOP 8 setting for the query may not exceed the cost threshold and thus will not create a parallel plan.

If you have a process where you specifically need to manage resources, more than 1 but less than the MAXDOP setting, you could add the OPTION (MAXDOP 4) (as an example) or some other setting that would fit the process you are running.

Therefore, you have some choices and are not stuck with just: 1 core, 8 cores, or "all" processors.


When is it OK to shrink a Database? I know shrink is the devil: It reverses page order and is responsible for skin cancer, data fragmentation, and global warming.  The list goes on... That being said, say I have a 100 GB database and I delete 50 GB of data -- not on one table, but a general pruning of old data on a database wide level, covering 90% of the tables -- does this constitute an appropriate use case for shrinking the database?

If not, what are the appropriate steps to take to clean house after removing such a high percentage of data from a database?  I can think of two: Rebuild Indexes and Update Stats. What else?

Is the database going to grow again? If so then the effort you're going to put into the shrink operations are just going to be a waste, because when you've got the file size down and then you add more data, the file will just have to grow again, and transactions have to wait for that growth to happen. If you have sub-optimal auto-growth settings and/or a slow drive this growth activity is going to be quite painful.

If you do shrink the database, what are you going to use the freed up disk space for? Again if you are just going to keep that space free in case this database grows again, then you're just spinning your wheels.

What you might consider doing, now that you've got all this free space in the file, is rebuilding your indexes so that they are better optimized (and it will be much less painful to do this when you have free space to do so - think about trying to change a sweater in a tiny closet vs. a big bedroom).

So unless this was a major cleanup operation and you really won't be ramping up to the same level of data again, I'd just leave it as is and focus on other areas of optimization.


Using OPENDATASOURCE to insert mdb data into SQL Server I tried inserting data from mdb file to 32 bit SQL Server using below code:

USE [master]
GO

sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO

    USE [ImportTest]
    GO

INSERT INTO [dbo].[PROJECT_IDENTITY]
           ([Licencee_Name]
           ,[JURISDICTION])
        SELECT * FROM OPENDATASOURCE(
        'Microsoft.ACE.OLEDB.12.0',
        'Data Source="C:\\CCNTest.mdb"')...PROJECT_IDENTITY;

The Error Message I got was:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 23
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Any ideas on how to solve this msg?

Thanks

This is what worked for me:
Add full permissions to Everyone on C:\Users\SQL Service account name\AppData\Local\Temp

Here's the full thread:
https://stackoverflow.com/questions/26267224/the-ole-db-provider-microsoft-ace-oledb-12-0-for-linked-server-null


How do I keep a database physically secure? This is more of a conceptual question. I am learning how to integrate Java and databases (using PostgreSQL at the moment) and my initial questions is:

  What are the guarantees that the data I create will remain safe?

For example, if I have a simple website hosted on my computer, what is the guarantee that data will remain safe from physical elements (HD failure or any kind of hardware deficiency)?

  What are the procedures used in small and big databases to prevent this kind of problem? Should the databases be stored in a separated locked up machine?

Thanks.

This is quite broad and the answer largely depends on how much your data is worth to you?

If you are from Amazon and you are storing millions of credit card numbers, then loss and/or revealing that data to third parties could be potentially costly in terms of fines from regulatory bodies and loss of consumer trust and future customers.

If they are the scores from your daughter's little league soccer, then the consequences mightn't be as severe financially (although annoyed little girls have ways of making that feeling very clear :-) ).

Maybe you could explain your potential exposure in more detail and ask more specific questions and you might get better advice tailored to your precise needs?

Take a look here (and links within) on how to ask good questions. This site is a great resource and it's worthwhile making the effort to follow the guidelines. Help us to help you!


When using class inheritance, can all PKs of the derived tables exist as PKs of the base table? Brief questions

When class inheritance is used, the child table inherits the key from the parent, right? Thus, you INSERT INTO the parent and then the child?

This indicates that the PK of the child table (derived class) becomes FK in the parent table (base class). See "but now the primary key of these tables also becomes a foreign key to the People table." That's not true is it? The examples indicate that the child tables reference the parent table. Did I misunderstand something?

Can the PK of both the child and parent tables be the same?

Details
I'm new to handling supertype-subtype instances in RDBs, but I've read a number of posts about three main options. The one I'm interested to implement is class inheritance where I have a base table with all common attributes and derived tables with attributes that are unique to each subtype.
I generically understand that class inheritance and table inheritance aren't the same. Based on the postgres documentation, table inheritance will allow for duplicate data in the child tables that I can't allow. Thus, I don't think table inheritance is a good way for me to go.
I'm interested in class inheritance because I need multiple different subtypes to be referenced as FK in a table. For example, I have a base class table vehicle and derived classes boat and car. I have another table for vehicle_maintenance. As a side note, I have many tables that are equivalent to vehicle_maintenance in that they can be applied to both boat and car. I also have many more subtypes than just boat and car with many attributes that are unique to each subtype.
Here's an example adapted from this post.
CREATE TABLE vehicle (
   vehicle_id          int PRIMARY KEY,
   paint_type    text,

   -- // other common attributes
);

CREATE TABLE boat (
    boat_id         int PRIMARY KEY REFERENCES vehicle (vehicle_id),
    propeller_type    text,
    water_exposure     text,

   -- // other attributes specific to boat ...

);

CREATE TABLE car (
    car_id         int PRIMARY KEY REFERENCES vehicle (vehicle_id),
    steering_type    text,
    wheel_type     text,

   -- // other attributes specific to car ...

);

CREATE TABLE vehicle_maintenance_id (
   vehicle_maintenance_id          int,
   date_complete        datetime,
   FOREIGN KEY (vehicle_id) REFERENCES vehicle (vehicle_id)

);

In this example, I would create a unique ID for every vehicle in the base table and carry that over to the derived tables. This would mean that the same paint_type would be repeated without any other attributes being UNIQUE, except the PK. This doesn't seem very normalized, but it's the only way I understand to get all of the PKs from the derived tables into one table without just doing an all-in-one table instead of class inheritance.
Am I just confusing myself, and the example I provided above is acceptable?
Edit
Perhaps I need to make this a separate question: I can't create composite keys based on attributes in the child tables and use them as PKs in the base table, right? This is because each child table will have unique attributes and the base table would require only one set of attributes to form the composite FK, correct?

Your data model looks reasonable and normalized. If you are using the same paint_type repeatedly, you can break it out into a lookup table.
I don't understand the concern in your edit, so maybe you should explain that with more detail in a different question.


MSOLEDBSQL syntax when using OPENROWSET We have been using this syntax when using OpenRowset to collect data from other SQL Server instances

SELECT a.*
FROM OPENROWSET('SQLNCLI11',
'Server=STARDBS1004\T12;UID=user;PWD=pwd;',
'select * from [MyTable]') as a

Native Client is deprecated and MS advice is to use latest Microsoft® OLE DB Driver 18 for SQL Server®

I am struggling to find the correct syntax for my OPENROWSET query

This I how think it is supposed to be now:

SELECT a.*
FROM OPENROWSET('MSOLEDBSQL',
'Data Source=STARDBS1004\T12;Initial Catalog=Staging_DWH;User ID=user;Password=pwd',
'select * from [MyTable]') AS a

But I get these errors:

  OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message
  "Invalid authorization specification".
  OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message
  "Invalid connection string attribute".
  Msg 7399, Level 16, State 1, Line 2
  The OLE DB provider "MSOLEDBSQL" for linked server "(null)" reported an
  error. Authentication failed.
  Msg 7303, Level 16, State 1, Line 2
  Cannot initialize the data source object of OLE DB provider "MSOLEDBSQL" for linked server "(null)".

Obviously the syntax is wrong, so can anybody help in finding the correct syntax?

OLEDB connections using the MSOLEDBSQL driver should use this format:

SELECT c.*
FROM OPENROWSET(
    'MSOLEDBSQL'
    , 'Server=MyServer;Database=tempdb;UID=test;Pwd=Test;'
    , 'SELECT name FROM sys.databases;'
    ) c;

ConnectionStrings.com is invaluable for obtaining valid connection string details.


CROSS APPLY-ing a parameterised function Given the following definitions:

CREATE TABLE MyTable
(
    col1 int,
    col2 int,
    col3 int,
    col4 int
)

CREATE FUNCTION fn_MyFunction
(
    @Param1 int,
    @Param2 int
)
RETURNS TABLE AS RETURN
(
    SELECT @Param1 * 2 AS 'res1', @Param2 * 4 AS 'res2', @Param1 AS 'col3'
)

I am trying to join the function to the table so that I get a calculated value (s) per row. Example: In a bookings table, I need to get the price based on a start date and end date.

Here is an example query:

SELECT      tbl.col1
,           tbl.col2
,           tbl.col3
,           fn.res1
,           fn.res1
FROM        MyTable tbl
CROSS APPLY fn_MyFunction(tbl.col3, tbl.col4) fn
WHERE       fn.col3 = tbl.col3

While I think I am getting the correct results in my actual query, how does the CROSS APPLY get the results in this case? Does it get them row-by-row (effectively)? Or does it work in a similar way to CROSS JOIN (ie: making a Cartesian Product)?

CROSS APPLY takes a table valued function and 'applies' parameters from each row in the query you are applying it to.  The function is evaluated once for each row and the output is implicitly joined to the source row in the record set from which the parameters were obtained.  Note that this 'join' can be 1:M - with a TVF one row at source can generate multiple rows of output from the TVF.

You don't need to use any more predicates in the where clause to join the results unless (for some reason) you want to further filter the output of the function by applying an additional predicate.

CROSS APPLY is quite useful with xpath queries if you have to go ferreting around XML fields that may contain repeating groups.


Azure Database Link to another Azure database How to query between two databases with the same server in SQL Database?

Currently we are aiming to link two azure databases that will update simultaneously.

Thanks!

Cross-database queries are not available in Azure SQL Database. You need to use Elastic Query to query across multiple Azure SQL Databases or move to a Managed Instance, which does support cross-database queries but obviously has a higher cost and other implications.


PostgreSQL query very slow when subquery added I have a relatively simple query on a table with 1.5M rows:
SELECT mtid FROM publication
WHERE mtid IN (9762715) OR last_modifier=21321
LIMIT 5000;

EXPLAIN ANALYZE output:

Limit  (cost=8.84..12.86 rows=1 width=8) (actual time=0.985..0.986 rows=1 loops=1)
  ->  Bitmap Heap Scan on publication  (cost=8.84..12.86 rows=1 width=8) (actual time=0.984..0.985 rows=1 loops=1)
        Recheck Cond: ((mtid = 9762715) OR (last_modifier = 21321))
        ->  BitmapOr  (cost=8.84..8.84 rows=1 width=0) (actual time=0.971..0.971 rows=0 loops=1)
              ->  Bitmap Index Scan on publication_pkey  (cost=0.00..4.42 rows=1 width=0) (actual time=0.295..0.295 rows=1 loops=1)
                    Index Cond: (mtid = 9762715)
              ->  Bitmap Index Scan on publication_last_modifier_btree  (cost=0.00..4.42 rows=1 width=0) (actual time=0.674..0.674 rows=0 loops=1)
                    Index Cond: (last_modifier = 21321)
Total runtime: 1.027 ms

So far so good, fast and uses the available indexes.
Now, if I modify a query just a bit, the result will be:
SELECT mtid FROM publication
WHERE mtid IN (SELECT 9762715) OR last_modifier=21321
LIMIT 5000;

The EXPLAIN ANALYZE output is:

Limit  (cost=0.01..2347.74 rows=5000 width=8) (actual time=2735.891..2841.398 rows=1 loops=1)
  ->  Seq Scan on publication  (cost=0.01..349652.84 rows=744661 width=8) (actual time=2735.888..2841.393 rows=1 loops=1)
        Filter: ((hashed SubPlan 1) OR (last_modifier = 21321))
        SubPlan 1
          ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
Total runtime: 2841.442 ms

Not so fast, and using seq scan...
Of course, the original query run by the application is a bit more complex, and even slower, and of course the hibernate-generated original is not (SELECT 9762715), but the slowness is there even for that (SELECT 9762715)! The query is generated by hibernate, so it is quite a challenge to change them, and some features are not available (e.g. UNION is not available, which would be fast).
The questions

Why cannot the index be used in the second case? How could they be used?
Can I improve query performance some other way?

Additional thoughts
It seems that we could use the first case by manually doing a SELECT, and then putting the resulting list into the query. Even with 5000 numbers in the IN() list it is four times faster than the second solution. However, it just seems WRONG (also, it could be 100 times faster :) ). It is completely incomprehensible why the query planner uses a completely different method for these two queries, so I would like to find a nicer solution to this problem.

My colleague has found a way to change the query so that it needs a simple rewrite and does what it needs to do, i.e. doing the subselect in one step, and then doing the further operations on the result:

SELECT mtid FROM publication
WHERE
  mtid = ANY( (SELECT ARRAY(SELECT 9762715))::bigint[] )
  OR last_modifier=21321
LIMIT 5000;

The explain analyze now is:

 Limit  (cost=92.58..9442.38 rows=2478 width=8) (actual time=0.071..0.074 rows=1 loops=1)
   InitPlan 2 (returns $1)
     ->  Result  (cost=0.01..0.02 rows=1 width=0) (actual time=0.010..0.011 rows=1 loops=1)
           InitPlan 1 (returns $0)
             ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1)
   ->  Bitmap Heap Scan on publication  (cost=92.56..9442.36 rows=2478 width=8) (actual time=0.069..0.070 rows=1 loops=1)
         Recheck Cond: ((mtid = ANY (($1)::bigint[])) OR (last_modifier = 21321))
         Heap Blocks: exact=1
         ->  BitmapOr  (cost=92.56..92.56 rows=2478 width=0) (actual time=0.060..0.060 rows=0 loops=1)
               ->  Bitmap Index Scan on publication_pkey  (cost=0.00..44.38 rows=10 width=0) (actual time=0.046..0.046 rows=1 loops=1)
                     Index Cond: (mtid = ANY (($1)::bigint[]))
               ->  Bitmap Index Scan on publication_last_modifier_btree  (cost=0.00..46.94 rows=2468 width=0) (actual time=0.011..0.011 rows=0 loops=1)
                     Index Cond: (last_modifier = 21321)
 Planning time: 0.704 ms
 Execution time: 0.153 ms

It seems we can create a simple parser that finds and rewrites all subselects this way, and add it to a hibernate hook to manipulate the native query.


Database T-Log is full due to 'Active_transaction'. SQL server is using new T-log file first rather than old I had a SSIS process which failed due to T-log file Full for the Stage database. T-log file max growth is limited to 510GB however only 180-200 GB of t-log file space is getting used everyday.
Even though T-log file had an enough space but it was failing with T-log FULL error. in order to troubleshoot I've added second T-log file (549 GB) to the new drive.
My understanding is that SQL server will use first T-log file first and once it is reached to max limit then only it will start using 2nd T-log file however I noticed that it started using new T-log file rather than old first. It does not look like normal behavior.
How does SQL server decide to use old T-log file or new T-log file first?
SQL server version:- SQL server 2012 SP4

Why process was failing with T-log full even though T-log file size is set to 500 GB

Is Replication or CDC enabled for the database?
select name, is_published, is_cdc_enabled
from sys.databases

Why would SQL server start using new T-log file rather than old T-Log file first?

Because the old one was full.


SQL Server 2012 AlwaysOn Availability Group We have an application that needs access to the database all the time to work properly and we are planning on deploying SQL Server 2012 AlwaysOn Availability Groups on Windows Server 2012.

We have two geographically separated data centers and we plan to keep one DB server in DC1 and the other in DC2.

All the information that I've seen shows a local synchronous copy and an asynchronous copy in another data center. I wanted to know if there is a way to configure the availability group with just two SQL servers which are separated geographically and can support automatic failover.

You can find here some design pattern for always on solutions
http://blogs.msdn.com/b/sqlcat/archive/2013/11/20/sql-server-2012-alwayson-high-availability-and-disaster-recovery-design-patterns.aspx

just note that for automatic fail-over  you may want to have synchronous commit so you dont loose data.
also to have automatic fail-over you must have node majority to decide to do the fail-over.
this mean you must have extra voter in the cluster. (eg' File Share Witness or another node)

this will prevent the problem known as Split Brain problem
(https://en.wikipedia.org/wiki/Split-brain_(computing))

to summarize, this is very common need to have local automatic fail-over (because local LAN can serve synchronous mode)
and another replica off-site with manual fail-over and asynchronous commit which can cause data loss when main site failed and not all data replicated yet to the another site.

I hope this have been informative for you.
I will be glad to add information if you have any questions about that implementation.


How do I allow Service Broker access to Master Key of database? I am trying to set up SqlDependency on my dev system. The db on my system is a few months old production db which I have attached.

When running SqlDependency from ASP.NET application, I see the following error in Event Viewer.

Service Broker needs to access the master key in the database 'MyDb'. Error code:26. The master key has to exist and the service master key encryption is required.

I searched and found,

  When a database is first attached to a new instance of SQL Server, a
  copy of the Database Master Key (encrypted by the Service Master Key)
  is not yet stored in the server. You must use the OPEN MASTER KEY
  statement to decrypt the Database Master Key. Once the Database Master
  Key has been decrypted, you have the option of enabling automatic
  decryption in the future by using the ALTER MASTER KEY statement to
  provision the server with a copy of the Database Master Key encrypted
  with the Service Master Key. This option is discussed in ALTER MASTER
  KEY (Transact-SQL).

http://msdn.microsoft.com/en-us/library/ms174433(v=SQL.90).aspx

However, it is not very clear what password should I use for the OPEN MASTER KEY statement and from where would I get it? I used the Key_GUID value taken from sys.symmetric_keys from both the machines but got an error "Msg 15151, Level 16, State 1, Line 7
Cannot find the symmetric key 'master key', because it does not exist or you do not have permission."

How do I get this to work?

When the Database Master Key was created a password was supplied to CREATE MASTER KEY it's this password which is required.  If this password is not available to the developers then recreating the encrypted objects losing the currently protected data is the only option.


Access 2010 Need help connecting/updating database data I have a large database that has sporadic fields with incomplete or missing data.  Quite the inherited project, I have to say.  I'm new to Access, so if you have any ideas on how to simplify this pile of awesomeness, I'd love to hear them.  Here's a bit of a primer on my situation:

Columns in tblMaster(yes, it's a mess.  Manufacturing what we do uses a lot of parts):
itemNumber
fittingNumber 1-13
fittingQuantity 1-13
fittingDescription 1-13
fittingInfo 1-13
fittingColor 1-13

Columns in tblDescriptions:
fittingNumber
fittingDescription
fittingInfo
fittingColor

This sprawls over thousands of records.  The problem is that the table of descriptions was incomplete when it was initially imported from Excel, and thus there's a ton of records in the main table with missing descriptions.  I have since updated the descriptions table information to include everything that was missing previously.  My entry form uses the updated information just fine, but the stuff prior is incomplete.  Here's what the tables look like.  tblMaster has columns for the fitting IDs, though they're not in the screenshot.

My question is this:  If each of those fitting columns in tblMaster is a separate fitting, how do I match each of those IDs to their respective descriptions in the other table and update the main table with the completed information?  I've been looking all over the place on how to do this; All I've seen is stuff on lookup tables and I don't want to use those for obvious reasons.  I'm about to scrap it and start over.

I would greatly appreciate any input on this.
Thanks

Alright guys, here's what I did.  I basically exported what I had, brought it into Excel, and wrote some code to restructure the data.  Now I have more records, but far less columns.  Now each Fitting ID will easily correspond to its respective description in the second table.  and here's the code for anyone who's interested:

Private Sub restructure()
Dim sh1 As Worksheet
Dim sh2 As Worksheet

Set sh1 = ThisWorkbook.Worksheets("tblMaster")
Set sh2 = ThisWorkbook.Worksheets("tblNew")

Dim lastRow As Long
Dim lastCol As Long

Dim sourceRow As Long
Dim sourceCol As Long
Dim destRow As Long
Dim destCol As Long
Dim initRow As Long
Dim initCol As Long

Dim n As Long
Dim m As Long
Dim coolerID As String

lastRow = sh1.Range("A2", sh1.Range("A2").End(xlDown)).Rows.Count
lastCol = 20

sourceRow = 2
sourceCol = 2
destRow = 2
destCol = 2
initRow = 2
initCol = 2

For n = 2 To lastRow

    coolerID = sh1.Cells(n, sourceCol - 1).Value

    For m = 2 To lastCol

        sh2.Cells(destRow, destCol - 1).Value = sh1.Cells(sourceRow, initCol - 1).Value

        If IsEmpty(sh1.Cells(sourceRow, sourceCol)) = False Then

            If sourceCol <= 14 Then
                sh2.Cells(destRow, destCol).Value = sh1.Cells(sourceRow, sourceCol).Value
                sh2.Cells(destRow, destCol + 1).Value = sh1.Cells(sourceRow, sourceCol + 13).Value
                sh2.Cells(destRow, destCol + 2).Value = sh1.Cells(sourceRow, initCol + 26).Value
                sh2.Cells(destRow, destCol + 3).Value = sh1.Cells(sourceRow, initCol + 27).Value
                destRow = destRow + 1
                sourceCol = sourceCol + 1
            ElseIf sourceCol > 14 Then
                destRow = destRow + 1
                destCol = initCol
                sourceRow = sourceRow + 1
                sourceCol = initCol
            End If
        End If
        If IsEmpty(sh1.Cells(sourceRow, sourceCol)) = True Then
            destRow = destRow + 1
            destCol = initCol
            sourceRow = sourceRow + 1
            sourceCol = initCol
        End If
    Next m
Next n

End Sub

So glad I got this figured out!  Hope this helps somebody in the future


program column is null in v$session CREATE OR REPLACE TRIGGER AWH_TOOLS_TRG
AFTER LOGON ON HRMS.SCHEMA
DECLARE
v_prog sys.v_$session.program%TYPE;
v_pro  varchar2(250);
BEGIN
SELECT upper(program) INTO v_prog
FROM sys.v_$session
WHERE  audsid = USERENV('SESSIONID')
AND  audsid != 0
AND  ROWNUM = 1;
v_pro := SUBSTR(upper(v_prog),0,INSTR(upper(v_prog),'.')-1);
IF UPPER(USERENV('TERMINAL')) NOT IN ('DESKTOP-U186KO5','WLSRV') THEN
          IF UPPER(v_prog) LIKE '%TOAD%'      OR
             UPPER(v_prog) LIKE '%T.O.A.D%'   OR
             UPPER(v_prog) LIKE '%SQLNAV%'    OR
             UPPER(v_prog) LIKE '%PLSQLDEV%'  OR
             UPPER(v_prog) LIKE '%BUSOBJ%'    OR
             UPPER(v_prog) LIKE '%EXCEL%'     OR
             UPPER(v_prog) LIKE '%SQLPLUS%'   OR
             UPPER(v_prog) LIKE '%FRMBLD%'    OR
             UPPER(v_prog) LIKE '%RWBUILDER%'
          THEN
          RAISE_APPLICATION_ERROR(-20983, '" '||v_pro||' "'||' not allowed... Please contact DBA...!!!');
          END IF;
END IF;
END;

program column is not showing any data during query.
How can i resolve this issue???

The value for PROGRAM is set by the client application.  I'm not sure if it is set before or after a login.

I'll have to verify - I suspect something like Tomcat can set PROGRAM to NULL.

Other Notes

SYS_CONTEXT( 'USERENV', 'CLIENT_PROGRAM_NAME' ) might be a better method.
The value usually comes from the name of the executable.

IIRC - there is a function within the drivers to set the value to something else.

A simple RENAME/mv will bypass your LOGIN TRIGGER.

Limiting login acceptance of users based on the value of PROGRAM is a futile effort.


How to store massive quantities of machine generated data? I am tasked with developing a system that stores massive quantities of machine generated data (syslogs) from a number of server farms and I am wondering what tools you fine folks use such instances.  Our project will need to keep up to 10 TB of data (maybe more in the future) and serve queries fairly quickly.  I have been reading about columnular rdms' as well as nosql options, which all seem promising.

There's a few systems specifically tailored to what you're requesting. I don't know exactly what kind of logs you're dealing with (application specific logs, system logs, web-server logs, a combination, etc.), so you may need to research a bit further.

Please pardon the lack of inline links - I currently don't have the reputation to post more than 2 links (you can find the writeup with inline links here).

Commercially there's:

Splunk
Sumo Logic
Loggly
Papertrail

On the open-source side there's:

Graylog2 (meant as an open-source alternative to Splunk)
OpenTSDB
Roll your own with Apache Flume and elasticsearch
Logstash

Personally, I'd look into Graylog2, Logstash, and (depending upon the requirements) OpenTSDB.

One thing to also look at is the underlying database system and how well it works with the hardware that you've already bought (i.e. MongoDB may not work very well with the hardware you have and the size of data that you have, however Hadoop/Hbase or a SQL server may work out better).


Can SQL Server disable IntelliSense on SSMS? I am using SSMS 2016 (ver: 13.0.15000.23) and connecting to SQL Server database 2016 (ver: 13.0.5081.1).

IntelliSense is not working on my SSMS.

I tried the following:

Tools >> Options >> Text Editor >> Transact-SQL >> IntelliSense, and select Enable IntelliSense
Tools >> Options >> Text Editor >> Transact-SQL >> General, and verify Auto list members and Parameter information check boxes are checked
Query >> IntelliSense Enabled
Verify that SQLCMD is disabled
Refreshed IntelliSense cache (Ctrl+Shift+R)
Making sure no compile errors are in the query
Uninstall and reinstall SSMS

I asked other members in my team and IntelliSense only works sporadically for them, if it does.

Question:
Is there a server setting which is disabling IntelliSense on SSMS?

I've already gone through all of these questions:

IntelliSense not working but it is enabled
IntelliSense is not working at SQL Server Management Studio
Why is SQL Server 2008 Management Studio Intellisense not working?
SQL Server Intellisense not working on some servers
SQL Server 2008 R2 intellisense doesn't work
SSMS IntelliSense doesn't work on particular database

I have limited permissions on the instance, but I can select and update records in the database I'm working in (I didn't try insert).  I can see around 1,600 names of objects and columns by running:

SELECT o.name, c.name
FROM sys.objects o
    INNER JOIN sys.columns c ON o.object_id = c.object_id
WHERE o.is_ms_shipped = 0;

Permissions
As the comment answers you've received so far imply, the most likely reason for this problem is that you don't have the appropriate permissions to that database.  This is (rather vaguely) documented here:
Troubleshooting IntelliSense -> Database Engine Query IntelliSense

Completion lists do not include database objects for which you do not have permissions. IntelliSense flags references to objects for which you do have permissions. For example, if you open a script that is written by someone else, any references to objects for which that person has permissions and you do not are flagged as incorrect.

The easiest way to fix this is to have a DBA grant the "View Definition" privilege to the database user that you're using to connect to this database.
For example, I can create this login, and a database user for a specific database, on my local SQL Server 2016 instance:
USE [master]
GO
CREATE LOGIN [nerd-login] WITH PASSWORD = 'NerdPassword1';

USE [SomeDatabase]
GO
CREATE USER [nerd-user] FOR LOGIN [nerd-login] WITH DEFAULT_SCHEMA=[dbo]
GO

And if I login as nerd-login, I can connect to the SomeDatabase database, but I can't get any intellisense on it.  But, after running this command:
USE [SomeDatabase]
GO
GRANT VIEW DEFINITION TO [nerd-user];

I'm able to get intellisense on the objects in the SomeDatabase database.
This can also be granted more broadly at the instance level (by executing GRANT VIEW ANY DEFINITION in the master database context), or at the per-object level.
Latency
In the comments here, you've mentioned:

Server is about 50 miles away, but I do feel there is some issue with network/computer because opening a new browser takes several seconds.

Factors like the amount of metadata in a particular database (# of tables, views, columns, functions, stored procedures, etc), the network speed between you and the database, and how busy the other database is, can all affect intellisense.
Here's a pretty thorough treatment of the latency issue in a blog post from Aaron Bertrand:
Troubleshooting IntelliSense in SQL Server Management Studio 2012

And finally, you may just need to be patient. Check your network speed, the general health of the server, and be understanding if your metadata is quite large - if you are running SAP, for example, there's a lot more data to bring across than AdventureWorks.
When SSMS is attempting to get metadata from the destination server, it may time out before it starts receiving results. This timeout is hard-coded to two seconds in SSMS 2012, but in previous versions, it would wait for the database connection to time out (which could be 30 seconds or more). It will continue trying in the background, so you may not see the drop-down appear right away, but it could appear after successive attempts at pressing Ctrl+J.
You can force it to try to reload by pressing Ctrl+Shift+R or the menu option Edit > IntelliSense > Refresh Local Cache. Once metadata is retrieved in the background, the data is cached in local memory. If the connection is working and none of the above factors are in play, the cache will eventually be populated and you should be able to use all of the functionality.
But again, on SSMS 2008 or SSMS 2008 R2, this may take a little bit longer because of the more relaxed timeout.

Note: I realize that's about SSMS 2012, but the same general principles still apply


MSSQL Audits are growing out of control. 1gb every 4 minutes - any way to get size down? I am trying to setup audits for a Veeam backup server.

I used the audit specifications from STIG - https://www.stigviewer.com/stig/ms_sql_server_2016_instance/

I haven't setup any database auditing at this point, just the server from above link.

The audit is writing 1gb every 4 minutes. Over 100gb every day!

Is there any way to get the size down? Maybe removing Select statements from audits? If so, how can I do that. Please point to a tutorial or something - I've searched for days ...

TIA

Auditing should be enabled based on requirement from Compliance/IT security or Audit department of your organization. If you enable auditing as mentioned in this link, it would start auditing each and every thing. It will not only make audit files to grow rather, it would impact on performance of your instance badly.

Ideally if you follow C2 auditing, it captures basics of auditing and is accepted internationally.

  C2 Auditing and Common Criteria Compliance are two internationally
  accepted auditing standards.

  C2 audit mode is the auditing option that can be used to facilitate
  auditing standard. By enabling C2 auditing, it allows the
  administrator to enable a comprehensive type of auditing, logging.
  This is named as C2 audit mode because it is logging of the form
  specified by the U.S. Department of Defense regulations to meet the
  certification at the C2 level of trust. Those regulations were
  specified in a document called the Trusted Computer System Evaluation
  Criteria, often referred to as the “Orange Book” in the “Rainbow
  Series.” The Orange Book is now depreciated in favor of the Common
  Criteria. Nonetheless, C2 Auditing is still a commonly used term.

  C2 audit mode generally means assigning a unique generated audit ID to
  each group of related processes, starting at login. Thereafter,
  certain forms of system calls performed by every process are logged
  with the audit ID.

You might read more about C2 auditing at this link.

There is another method of auditing accepted called "Common Criteria Compliance" and details are as below:

  C2 auditing has been deprecated by the Common Criteria specification
  which was developed by the European Union. Common Criteria Compliance
  is internationally recognized set of guidelines for security for
  information technology products. This is applied to operating systems,
  Databases, Network Devices and Smart cards etc. Common criteria
  certified products have been rigorously evaluated by accredited third
  party security labs.

  This option is available same versions as C2 Audit but Common Criteria
  Compliance is available in Enterprise and Data Center editions of SQL
  Server 2008 R2 and after versions. However, with respect to the SQL
  Server, if you are complying with either C2 or common criteria, the
  audit result is similar. In the case of Common Criteria Compliance,
  audited data can be viewed from system views which is more secure than
  the C2 audit mode.

if you enable one of them, your problem of file size and performance should go.

In case, you are really interested to get to the root of your culprit in your current scenario then, you should query your audit log and group them by action and disable that event, same can be done using below query:

SELECT action_id, count(*)
  FROM fn_get_audit_file( 'E:\Test\Audit-*.sqlaudit' , DEFAULT , DEFAULT)
group by action_id

Please remove SCHEMA_OBJECT_ACCESS_GROUP from auditing events and you should be fine for disabling select statement from audit. You can check more about this at link.

Hope this helps.


Broken Slave MySQL : How To Fix MySQL Replication (BINLOG) error in the Slave? Before we start I can give some info about the versions,
Ubuntu - 14.04, MySQL - 5.5
I have successfully configured the Master-Slave replication with two EC2 Ubuntu instances. My master is in Ubuntu 14 and slave is in Ubuntu 18. The replication was successful and the slave was replicating data for the last 3-4 days. But, suddenly my slave(Ubuntu 18) stopped replication with a query error.
Error Message : Last_SQL_Error: Error 'Duplicate entry '11379183' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'INSERT INTO testTable (id,value1,value2,value3,value4,value5,value6) VALUES(22,"5","429438","1592499300","1","8","1")'

Slave_IO_Running: Yes
Slave_SQL_Running: No

I have tried,

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE;

It has helped me for some time. but the replication is broken again.
How can we resolve this kind of replication errors? Is there any automated method for fixing the broken slave?
Is GTID based replication possible with MySQL 5.5? I think GTID is available from MySQL version 5.6?

HOW TO BYPASS ALL DUPLICATE KEY ERRORS
There are two ways to bypass duplicate key errors
METHOD #1 : Configure it in my.cnf
Add this line under the [mysqld] group header in my.cnf
[mysqld]
slave-skip-errors=1062

then restart mysql.
Once replication begins, all duplicate key errors will be bypassed. Once Seconds_Behind_Master is 0, you are all caught up. However, the data on the Slave is out-of-sync with the Master. What do you do next ???
METHOD #2 : Use an Automated Tool
I  am sure you don't have the time to run
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE;

over and over again. Wouldn't it be nice if an automated tool can do that for you ? Well, such a tool exists. It's called pt-slave-restart.
You would run it something like this:
pt-slave-restart --error-numbers 1062

Put this in a crontab to go off every minute.
* * * * * pt-slave-restart --error-numbers 1062

Even better, you can make a server daemon and not use a crontab
pt-slave-restart --error-numbers 1062 --daemonize

and you do not need to restart mysqld.
Either way, pt-slave-restart will skip that error over and over again until Seconds_Behind_Master is 0. When you are done having the Slave get caught up, stop using pt-slave-restart.
However, you still have the same problem as METHOD #1: the data on the Slave is out-of-sync with the Master. What do you do next ???
OK, WHAT DO YOU DO NEXT ???
You must synchronize the data on the Slave with your Master. Wouldn't it be nice if a utility exists that can do that for you ? Well, such a tool exists. In fact, there are two tools. They are called pt-table-checksum and pt-table-sync.
What do they do ???
You use pt-table-checksum to check on MySQL replication integrity. If you encounter any table that have different checksums between Master and Slave, such a table on the Slave is out-of-sync.
You use pt-table-sync to synchronize MySQL table data.
You can run
pt-table-sync --print > sync_file.txt

to see what will change (this is like a dry run of the real thing)
You then run
pt-table-sync --execute

to perform the actual sync.
PLEASE READ THE PERCONA TOOLKIT DOCUMENTATION.
ALWAYS HANDLE PERCONA TOOLKIT RESPONSIBLY !!!
NOTE : Sadly, there is absolutely nothing you can with MySQL 5.5 and GTID.


Installing 10g client next to 11g database on same computer I have Oracle 11.2.0.1.0 installed on Windows 7 64 bit

I want to import data from warehouse which has Oracle 10.2.0.4.0 installed.

When I try to import

impdp usr/pass@WAREHOUSE TABLES=TABLE DIRECTORY=DATA_PUMP_DIR NETWORK_LINK=D_W VERSION=10.2

It gives :

UDI-00018: Data Pump client is incompatible with database version 10.2.0.4.0

To overcome version incompatibility, I will install 10g client on my computer.

But I already have 11gr2 database installed, so I wanted to learn about potential problems.

Especially about having two Oracle Homes, tnsnames.ora files, etc..

Thanks.

You could live quiet safety with two ORACLE_HOMEs if you know:

%TNS_ADMIN% is your tnsnames.ora / listener.ora / sqlnet.ora directory for both;
in your %PATH% you should specify the right order for your preferred oracle bin;
there's a Oracle_home_selector installed somehere (I don't remember) in full client install
take a look at Windows Registry (something like HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE)
pay attention to %NLS_LANG%


Replicate to a secondary database that also allows edits I have a client with a somewhat odd requirement. They want to be able to take a snapshot of their sales database that their accountants can then use once it's disconnected from the live database. That makes sense. But the accountants also want to be able to make edits to historical data in the secondary database, and have those edits retained the next time they take a snapshot.

I'm at a loss for how to do this. I could enable change tracking, then go through the change-tracking tables and reapply their changes after recreating the secondary database, but that sounds like it would quickly get messy.

Could I possibly use log shipping for this? They tell me that the data they would be editing in the secondary, historical database is unlikely to be touched in the primary database. But if there have been changes to the secondary database, will I still be able to restore transaction logs?

I'm really pretty clueless on how to proceed... Any advice would be appreciated!

If you need bidirectional synchronization, have a look at merge replication. (Good introduction: http://www.sqlservercentral.com/stairway/72401/)

However, you question sounds like you don't want those changes to be applied to the master sales database. In that case I would setup shadow tables that take the changes and use replication to load the master tables. The logic to write to the shadow tables and read from the shadow tables if there is a row, form the master table otherwise could be placed in a view with an instead-of trigger. The following is a conceptual example of that model:

create table dbo.product-master(Id int, ...);
create table dbo.product-shadow(Id int,....);
GO
CREATE VIEW dbo.product
AS
SELECT m.Id,
       CASE WHEN s.ID is NULL THEN m.col1 ELSE s.col1 END AS col1, --show shadow row if it exists, master row otherwise
       CASE WHEN s.ID is NULL THEN m.col2 ELSE s.col2 END AS col2,
       CASE WHEN s.ID is NULL THEN m.col2 ELSE s.col3 END AS col3
FROM dbo.product-master m
LEFT JOIN dbo.product-shadow s
ON m.Id = s.Id;
GO
CREATE TRIGGER dbo.product-IUD INSTEADOF INSERT,UPDATE,DELETE
AS
BEGIN
  --use INSERTED and DELETED virtual tables and the MERGE statement to apply changes to the shadow table
END;
GO

To load the master tables you could use any kind of replication (see link above for an introduction).


Is there a simple way in VS SSIS to swap existing DB connection manager with existing project connection manager? I am using Visual Studio 2017 (SSDT).

I have several packages (about 50) I need to migrate to use the project deployment model (instead of package deployment).

I created a new Project and added my first SSIS package. I converted the existing database connection manager to Project connection and everything worked fine.

I then added my next package to the project. I can see 2 connection managers in the package. 1 being the existing Package connection and the other being the Project connection manager I just created.

Since both connection managers point to the same database, is there a way I can just swap the existing package connection manager with my new Project connection manager so that I won't have to go into all the different tasks in the SSIS package and update them to use the Project Connection manager as this would be very time consuming and prone to error?

I did some research and the best I could find was an article on updating the SSIS packages in a text editor by doing a find and replace. I'm hoping there is an easier more automated way of making the change within Visual Studio? Anyone have any suggestions?

In case it helps anyone:

Here's an article I found describing how to get around the issue by editing the SSIS XML

I would have thought this could be done natively within VS without having to edit the package XML.

I guess the answer is no but I can say that I followed the steps in the article and it worked for me using SSDT 2017. Wasn't too bad doing the necessary find and replace in Notedpad ++ (vs the carpal tunnel method of clicking around a million times in VS which is more error prone compared to find and replace)


create a FTS in sql2005 on multile fields in multiple tables? How can I create a Full Text Search catalog on 3 fields where one is in a second table?

You'd have to create a view and create an FT index on this view

From MSDN "Getting Started with Full-Text Search" (my bold)

  Create a full-text index on the table or indexed view

I haven't tried this myself so don't have any tips. However, an indexed view is the only way to have any index span tables.


Moving DataFile to Another Location ORACLE I'm having some problems moving DATAFILE from one location to another due to the shortage of storage space. I've managed to change the path file of control file via command(linux). Now I would like to change the path file of the datafile and logs to another since I've already manual moved the folder of the database.
 SQL> startup;
 ORACLE instance started.

 Total System Global Area 6710886400 bytes
 Fixed Size                  2939560 bytes
 Variable Size            3506439512 bytes
 Database Buffers         3187671040 bytes
 Redo Buffers               13836288 bytes
 Database mounted.
 ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
 ORA-01110: data file 1: '/app/oracle/oradata/ENOVIA/system01.dbf'

Apparently, the data files have already been moved to another location, whereas the path of the files has yet to be changed.
How can I check for the current location of Database in SQL and which command is needed to be used to alter the path file?
The new location is home/oradata/ENOVIA.

The SQL statement required to list the database files locations would be something like:
set lines 230
set pages 50
column file_name format a50
select ddf.FILE_NAME, ddf.TABLESPACE_NAME, ddf.STATUS, ddf.ONLINE_STATUS from dba_data_files ddf;

This will return something like the following:
|              FILE_NAME                  | TABLESPACE_NAME |  STATUS   | ONLINE_STATUS |
|-----------------------------------------|-----------------|-----------|---------------|
| /app/oracle/oradata/ENOVIA/SYSTEM01.DBF | SYSTEM          | AVAILABLE | SYSTEM        |
| /app/oracle/oradata/ENOVIA/SYSAUX01.DBF | SYSAUX          | AVAILABLE | ONLINE        |
| /app/oracle/oradata/ENOVIA/UNDOTS01.DBF | UNDOTS          | AVAILABLE | ONLINE        |
| /app/oracle/oradata/ENOVIA/USERS01.DBF  | USERS           | AVAILABLE | ONLINE        |

Once you have found the contradicting entry, you can modify it with the following SQL statement:
ALTER DATABASE
    RENAME FILE '/app/oracle/oradata/ENOVIA/system01.dbf'
             TO '/home/oradata/ENOVIA/system01.dbf'

Reference: Renaming and Relocating Datafiles (Oracle | Docs)

Note:
To rename or relocate datafiles of the SYSTEM tablespace, the default temporary tablespace, or the active undo tablespace you must use this ALTER DATABASE method because you cannot take these tablespaces offline.

Good luck.


ERWIN create and use database I'm trying to generate physical database from my logical model using ERWIN 7.

All is well in the script that is generated automatically, there is only the problem that the model is created in the database "master" which is the default SQL Server, rather than being created in the database that I inserted along with the model.

I cant find a way to associate my MODEL with the database I'm creating along with the model.

Here is the script that ERWIN auto-generates:

CREATE DATABASE Movies go
ALTER DATABASE Movies SET  RECOVERY FULL go
CREATE SCHEMA fbd AUTHORIZATION dbo go
CREATE DEFAULT Default_Value_movies_gen   AS 1 go
CREATE DEFAULT Default_Value_titulo_movies    AS 'No title' go

But it should generate automatically:

CREATE DATABASE Movies go
--Here should be "USE Movies",to use database that ERWIN have just created
ALTER DATABASE Movies SET RECOVERY FULL go
CREATE SCHEMA fbd AUTHORIZATION dbo go
CREATE DEFAULT Default_Value_movies_gen AS 1 go
CREATE DEFAULT Default_Value_titulo_movies AS 'No title' go

Any ideas?

You have to edit the Forward Engineering Template (FE Template).

Simple said, hard done.

As each class of entities (table, views, SPs, etc.) have their own block of generation code, it has to be done to each major entry points.

As for the SQL Server 2005 Erwin's template at its 7.3 version, the code below, injected at the beginning of the 'Create Entity' template will do for table DMLs.

/*

  We will save a global flag '_SQL_USE_DATABASE' is used to flag a variable of the same name initialization */
/

@ifnot( IsGlobalFlagSet("_SQL_USE_DATABASE") )
{
    Set( "_SQL_USE_DATABASE", "")
    SetGlobalFlag( "_SQL_USE_DATABASE" )
}

@if( IsPropertyNotNull("SQLServer_File_Group_Ref") )
{
    PushReference( "SQLServer_File_Group_Ref" )
    PushOwner

    @ifnot( Equal(Value("_SQL_USE_DATABASE"), Property("Name")) )
    {
        FE::Bucket( "90" )
        "USE " Property("Name")
        FE::EndOfStatement

        Set( "_SQL_USE_DATABASE", Property("Name") )
    }

    Pop
    Pop
}
@else
{
    Set( "_SQL_USE_DATABASE", "" )
}