MySQL 5.7 log rotate empty We upgraded our Percona XtraDB servers from version 5.6 to 5.7, and we noticed that the rotation slow query log files created after the initial file are empty. Is this something known in 5.7? How can we resolve this issue?

We also noticed that 5.7 does not have the /etc/mysql/debian.cnf file anymore that used to contain a username and password. Can that have anything to do with this?

Below is an example of these rotational slow query logs on one of our 5.7 servers:

-rw-rw---- 1 mysql mysql  1383157070 Sep 20 16:11 db-erp-1-slow.log
-rw-rw---- 1 mysql mysql           0 Jul 27 06:36 db-erp-1-slow.log-20170728
-rw-rw---- 1 mysql mysql           0 Jul 28 06:30 db-erp-1-slow.log-20170729
-rw-rw---- 1 mysql mysql           0 Jul 29 06:43 db-erp-1-slow.log-20170730
-rw-rw---- 1 mysql mysql           0 Jul 30 06:50 db-erp-1-slow.log-20170731
-rw-rw---- 1 mysql mysql           0 Jul 31 06:43 db-erp-1-slow.log-20170801
-rw-rw---- 1 mysql mysql           0 Aug  1 06:37 db-erp-1-slow.log-20170802
-rw-rw---- 1 mysql mysql           0 Aug  2 06:48 db-erp-1-slow.log-20170803
-rw-rw---- 1 mysql mysql           0 Aug  3 06:42 db-erp-1-slow.log-20170804
-rw-rw---- 1 mysql mysql           0 Aug  4 06:43 db-erp-1-slow.log-20170805

So after the initial log file, db-erp-1-slow.log, the following log files are empty.

Do you restart mysql daily?  That could explain the new logs.

Check these VARIABLES:

log_output = FILE   -- or TABLE,FILE
slow_query_log = ON
slow_query_log_file = /...   -- some valid path, including filename
long_query_time = 1   -- or some small value

Perhaps the general log hit a limit, thereby triggering an internal call to FLUSH LOGS, which rotated all logs?


"Connecting to Integration Service failed..." SQL Server 2016 Please see below. We have SSIS services installed and running, but it appears we cannot connect to them.

What is the path to get them set up and working?

TITLE: SSIS Execution Properties
------------------------------

Connecting to the Integration Services service on the computer "886319-DB1C" failed with the following error: "The specified service does not exist as an installed service.".

This error can occur when you try to connect to a SQL Server 2005 Integration Services service from the current version of the SQL Server tools. Instead, add folders to the service configuration file to let the local Integration Services service manage packages on the SQL Server 2005 instance.

For help, click: http://go.microsoft.com/fwlink/?LinkId=506689

------------------------------
ADDITIONAL INFORMATION:

Connecting to the Integration Services service on the computer "886319-DB1C" failed with the following error: "The specified service does not exist as an installed service.".

This error can occur when you try to connect to a SQL Server 2005 Integration Services service from the current version of the SQL Server tools. Instead, add folders to the service configuration file to let the local Integration Services service manage packages on the SQL Server 2005 instance.

------------------------------
BUTTONS:

OK
------------------------------

Solved.

I was using SSMS 17.1 against SQL Server 2016.

SSMS 17 works great w SQL Server 2016.... except it cannot connect to SQL 2016 SSIS. To connect to SQL 2016 SSIS, you must use SSMS 16.x

Installed SSMS 2016, (v16.x), and it connects fine to Integration Services.


Size, audit and rowcount of a table in Vertica I need to find a compressed size, an audit and a row count for each table in some schemes.
For a compressed size and a row count I tried to use projection_storage:

SELECT anchor_table_schema,
   anchor_table_name,
   SUM(used_bytes),
   SUM(row_count)
FROM v_monitor.projection_storage
GROUP BY anchor_table_schema, anchor_table_name

But for empty table (0 rows) I got non-zero SUM(used_bytes) and SUM(row_count). So this way doesn't work properly.
And I calculate an audit by select audit('table_name') for each table, but it's very slowly.
So my questions:

Is there other way to find compressed size (and maybe a row count) of a table? Because I got 80000000 bytes instead of zero.
Is there more faster way to calculate an audit? I know vertica calculates audits every day for each table, but I can't find where results is stored.

Using below query, you will get the size of Vertica tables.

SELECT anchor_table_schema,
anchor_table_name,
SUM(used_bytes) / (1024 * 1024 * 1024) AS TABLE_SIZE_GB
FROM   v_monitor.projection_storage
GROUP  BY anchor_table_schema,
anchor_table_name
order  by sum(used_bytes) desc;

Check out the most important queries to check table size

https://www.orahow.com/2018/06/how-to-check-size-of-tables-in-vertica.html


why OEM allow to connect system as normal and sys dont? In OEM I can connect with system as normal but with sys cant connect as normal. Why is that?

Because this is how it was designed.

7.3.2 SYSDBA and SYSOPER System Privileges

  The SYS user is automatically granted the SYSDBA privilege upon
  installation. When you log in as user SYS, you must connect to the
  database as SYSDBA or SYSOPER.


postgres 9.4 timeline issue We've had to move around the master a bit. It started on server 01 got moved to 02 (which was a slave) We need to move it again so we built 04 and trying to slave it off 02 and getting the following errors.

2018-02-25 17:00:08 UTC FATAL:  highest timeline 3 of the primary is behind recovery timeline 4
2018-02-25 17:00:13 UTC FATAL:  highest timeline 3 of the primary is behind recovery timeline 4
2018-02-25 17:00:18 UTC FATAL:  highest timeline 3 of the primary is behind recovery timeline 4

The initial dump happened like

pg_basebackup --verbose --progress -d "host=10.132.x.x user=backup password=...." -D /var/lib/postgresql/9.4/main/ -l 'instance restore' --xlog-method=stream

recovery file looks like

restore_command = 'if [ -f /srv/postgresql/archive/${DATASET}/%f ]; then cp /srv/postgresql/archive/${DATASET}/%f %p; else aws s3 cp --quiet s3://company-backups/postgresql/${DATASET}/archive/%f %p; fi'
standby_mode = 'on'
primary_conninfo = 'host=10.132.x.x user=backup password=....'
recovery_target_timeline = 'latest'
trigger_file = '/var/lib/postgresql/9.4/main/failover'

As it sounds, you are in a split brain situation.  The original master (01) was never stopped from being master, and after the promotion of 02, it became just another master.

Fixing such issues pre-9.5 is not so easy (at that version pg_rewind became an element of the PostgreSQL ecosystem) - you will need some manual cleanup, most probably.  What is certain is if you got writes to 01 after promotion of 02, they will be lost (or the writes on 02, depending what you choose to do).

I'd take a logical dump from both 01 and 02 to start (to check if there is anything that has to be manually replayed from 01 to 02), stop 01 altogether, remove the older timelines WAL segments from the archive (well, you can move them somewhere else just in case) and then try to build a slave based on 02 again.

You can also use pg_xlogdump to see which relations (tables, indexes, etc.) got writes since the split brain started.  (Note that from version 10 the utility name is pg_waldump.)


Oracle update statement using with clause takes for ever I am trying to write an update statement, which utilizes the result of a series of aggregations of with statement as below.

update final_table
set(destination_col1,destination_col2)=
(
select c4,c5
from
(
  with temp_table1(c1,c2,c3) as
  select c7,c7,c9 from source_table,

  temp_table2(c4,c5,c6) as
  select "some aggregation on c1,c2",c3 from temp_table

  select * from temp_table2
)
where c6= final_table.primary_key
);

This runs forever.

Whereas, when I try to break the same into 2 queries it works.

Creating a table using the inner query.
Use the above-created table to update like,

update final_table
    set(destination_col1,destination_col2)=
    (select c4,c5 from step1_table where c6=final_table.primary_key)

As suggested here, I tried to check if there are any locks. But I don't have any.

Am I abusing the with statement? Why exactly my expectation is not met?

Am I abusing the with statement?

No, you are abusing the UPDATE statement. With an UPDATE statement, the correlated subquery is executed for each row from final_table.

Updating values from an other table should be typically done with MERGE.

Example:

merge into final_table using
(
select c4,c5,c6
from
(
  with temp_table1(c1,c2,c3) as
  select c7,c7,c9 from source_table,

  temp_table2(c4,c5,c6) as
  select "some aggregation on c1,c2",c3 from temp_table

  select * from temp_table2
)
) inner
on inner.c6 = final_table.primary_key
when matched then update set final_table.destination_col1 = inner.c4,
                             final_table.destination_col2 = inner.c5;


Client application disconnects from database/s it is connected to after 10 minutes of inactivity When working with pgAdmin 4 (and actually, several other programs that act as clients to the database), the connection to the server disconnects after some 10 or 15 minutes of inactivity. One coffee and one phone call, and you get a message similar to "Sorry, connection to database is lost. Do you want me to try to reconnect?"

And pgAdmin reconnects always need more than one trial, and the object-tree that was already expanded gets collapsed... So, it's a little bit annoying.

pgAdmin doesn't seem to have any parameter related to that behaviour. (There seems to be ways to change some connection timeouts, but they're related to how pgAdmin behaves with regard to the server taking too long to connect).

What can be done it to avoid pgAdmin from disconnecting from the database?

Disclosure: This is actually a "pseudo-question". It is spin-off of another one, that ended up not being related to loss of connectivity... Given the fact I already had one answer in place, I (not very humbly) think it is worth "asking", in case the answer is helpful to someone.

pgAdmin keeps throwing up a connection timeout every 10 minutes or so.

I would think that the problem might be related to a router (or, in some cases, a stateful firewall) between your pgAdmin client and the PostgreSQL server.
This is quite common when you are at home (or in the office) and your router is performing NAT (Network Address Translation). After some idle time the connection is closed by the router. It is not something done by neither pgAdmin nor PostgreSQL, this is something that depends on the settings on the router,
If this is the case, you have three options:

Change the connection_timeout setting in your router, if that is feasible and you have the proper documentation. Here is an example for Cisco routers.

Make the router believe the connection is never idle for long enough. To do that: change one server parameter, called tcp_keep_alives:

According to the PostgreSQL documentation on Connections:

tcp_keepalives_idle (integer)
Specifies the number of seconds of inactivity after which TCP should send a keepalive message to the client. A value of 0 uses the system default. This parameter is supported only on systems that support the TCP_KEEPIDLE or TCP_KEEPALIVE symbols, and on Windows; on other systems, it must be zero. In sessions connected via a Unix-domain socket, this parameter is ignored and always reads as zero.

The keepalive message should avoid the NAT connection begin dropped.
Keep in mind the remark in the documentation:

This parameter is supported only on systems that support the TCP_KEEPIDLE or TCP_KEEPALIVE symbols, and on Windows

I really don't know which systems "support TCP_KEEPIDLE or TCP_KEEPALIVE symbols", but I guess that Linux and Mac should support them, and so does Windows according to the documentation.
If your system disconnects every 10 minutes, it should be prudent to have this setting to something around 9 * 60 = 540 seconds, or less.
This setting should be done in the postgresql.conf on your server. You can also test before making the change in the server, by either using the commands:
SET tcp_keepalives_idle = 480 ;

which would alter the setting for your current connection, or
ALTER ROLE (the-one-you-use) tcp_keepalives_idle = 480 ;

which would alter the setting only for this role.

Change the keep_alives on the client. You don't need to change any connection_timeout (that parameter is "how long to wait for a connection before deciding that the other side is not responding). Follow the instructions of the accepted answer of Any way to keep connection alive in pgAdmin without setting it on the server?. It's perfectly explained.

Reference about keep-alives: This video talks about How to Keep Your SSH Sessions Alive. Although this is not exactly the same scenario, it is equivalent (substitute SSH by pgAdmin).


Merge Replicate, reinitialise with a different part of the data I have a set of database with merge replicate configured between them.

The publisher is an aggregate of three sets of data.
Call them set 0, 1, and 2

There are subscribers that grab one set of the data depending on their geographical region (in SQL terms the HOST_NAME()).

One of the servers was setup with the wrong set.  (0 instead of 1).
I have corrected the function responsible so that servers should now get the correct set.
What do I need to do to force that server to dump its current data and grab the correct set all over again?

Marking the SQL server for a reinitialise from subscription did not do the trick :(

Please follow the steps in Create a Snapshot for a Merge Publication with Parameterized Filters to generate a new snapshot.  You must first generate a standard snapshot, then generate a Subscriber-specific partitioned snapshot.  After generating the snapshots, mark the Subscriber for reinitialization and start the Merge Agent to pull down the schema and data for the filtered subscription.


MySQL datafiles on SMB3 network storage We have MySQL-server on Ubuntu with InnoDB-datafiles on a local disk. As part of an upgrade program, the company is planning to put the datafiles on a Windows fileserver where they can be mounted with SMB3.

Is it a good idea to put the InnoDB-files on a network share or is it better to keep them on a local disk?

NO!  MySQL is not designed to allow two servers to access the same databases.

Or, maybe...

If your goal is to provide redundancy for the case of server failure, then maybe you could set up a non-running MySQL on a secondary server, ready to mount the disk subsystem in the case of failure.

Consider, instead, Clustering (Galera, PXC, InnoDB Cluster).


Assign value to a field after checking a condition I have this query:

ALTER TABLE MERCHANTS
   ADD COLUMN merchant_image bytea,
   ADD COLUMN merchant_logo bytea,
   ADD COLUMN merchant_address VARCHAR(100),
   ADD COLUMN merchant_phone VARCHAR(50),
   ADD COLUMN order_type VARCHAR(15)
      CHECK(order_type IN ('TAKE_AWAY', 'HOME_DELIVERY','NO_ORDERING')),
   ADD COLUMN open_until TIME,
   ADD COLUMN order_until TIME

For order_until I have this condition: if order_type is 'NO_ORDERING' then its default value should be 0. How can I write this, any suggestions?

order_until is data type time, which is an odd design - typically you would have timestamp or timestamptz here to include the date. Be that as it may, the column cannot be 0, which is not valid for time.

To get on with it, let's assume you want NULL instead, which would make sense for order_type = 'NO_ORDERING'. That's simple because the default DEFAULT value of any column is NULL and you needn't do anything extra. Seems like you want more than just a default ...

Next problem in your question: do you just want to enforce the rule or actually write NULL to the column (overwriting offending values), and if so, do that for INSERT and / or UPDATE?

To enforce your rule (exactly):

ALTER TABLE merchants ADD CONSTRAINT home_delivery_cannot_have_time
   CHECK (order_type <> 'HOME_DELIVERY' OR order_until IS NULL);

The documentation:

  A check constraint can also refer to several columns.

Follow the link, there's more.

SQL Fiddle.


Replication Options with data subsets We have an application that makes of of a SQL Server 2012 (ServerA) database. Some data that we need to display, though, comes from another database, but it's SQL Server 2008 (ServerSOURCE). The database is also on a separate server.

The plan is to create a replication database on the same server as ServerA, called ServerREP, and replicate data from ServerSOURCE into ServerREP. But we only want certain tables from ServerSOURCE. It's a small subset of the tables. ServerSOURCE has hundreds of tables. We only need around 10.

Additionally, the data in the tables could be reduced. For example, say we have a Person table, which has a PersonTypeId. We only want rows where PersonTypeId = x.

Can this be achieved with replication? Can we get subsets of data from a subset of tables from the source, into our new replicated database? Would it be something like creating a VIEW on the source, and replicating the results of that view, as a table in the replicated database?

Can this be achieved with replication?

Yes, Transactional replication can be used in your case.

  Can we get subsets of data from a subset of tables from the source, into our new replicated database?

Yes you can just replicate the tables (articles) that you want along with its subset of data.

e.g. where PersonTypeId = x --> You need to use static row filter as it uses a WHERE clause to select the appropriate data to be published.

selecting specific articles (tables) :

Filtering what data to publish :

  Would it be something like creating a VIEW on the source, and replicating the results of that view, as a table in the replicated database?

No. See above answer to use static row filter when you are publishing the articles.


How to achieve Dynamic SQL Query with Multiple parmeters to handle null values also My code logic is given below, when one of my input parameter passed as null, the parameterlist should be managed according, how could i proceed for this.

DECLARE @SQL NVARCHAR(MAX)
DECLARE @paramlist NVARCHAR(MAX)

SET @SQL='select [Company ID],
[QSS Company ID],
[Company Name],
[Inactive]  from Company WHERE 1=1'

IF @CompanyID IS NOT NULL
SELECT @SQL = @SQL + ' AND ([Company ID] = @CompanyID)'
Select @paramlist=@paramlist+'@CompanyID BIGINT'
IF @QSSCompanyID IS NOT NULL
SELECT @SQL = @SQL + ' AND ([QSS Company ID] = @QSSCompanyID)'
IF @CompanyName IS NOT NULL
SELECT @SQL = @SQL + ' AND ([QSS Company ID] = @CompanyName)'

EXEC sp_executesql @SQL, N'@CompanyID BIGINT, @QSSCompanyID BIGINT,
@CompanyName  nvarchar',  @CompanyID,@QSSCompanyID,@CompanyName

There is no harm in having unused parameters passed into sp_executesql.

You approach above is correct.  Declare and pass in all possible parameters for sp_executesql.  You will still get all the benefits of dynamic sql even with unused parameters.


Most Recent Event Based on Date I have a project I am working on and I need to extract the most recent date from each each equipment number.  I can't get MAX() to function with this.  Please see the following code. The idea is that it goes through the table and pulls each row and then compares the event_date for the most recent date for each eq_num.

SELECT e.event_id,
    l.location,
    e.event_date,
    c.name,
    q.eq_type,
    e.pickup_date,
    e.eq_to_c AS eq_num,
    e.eq_from_c,
    CASE
        WHEN l.location = '' OR l.location IS NULL AND e.eq_to_c != e.eq_from_c THEN 'Out'
        WHEN e.eq_from_c = '' THEN l.location
        WHEN e.eq_from_c IS NULL OR e.eq_from_c = '' THEN l.location
        WHEN e.eq_to_c = e.eq_from_c THEN 'Returned'
        WHEN e.eq_to_c != e.eq_from_c THEN l.location
        WHEN l.location = '' OR l.location IS NULL AND e.eq_to_c = e.eq_from_c THEN 'Returned'
    END AS eq_status,
    CASE
        WHEN CAST(e.event_date AS DATE) < CAST(e.pickup_date AS DATE) THEN e.pickup_date
        WHEN CAST(e.pickup_date AS DATE) < CAST(e.event_date AS DATE) THEN e.event_date
        WHEN CAST(e.event_date AS DATE) = CAST(e.pickup_date AS DATE) THEN e.event_date
    END AS event_date
    FROM events e
        LEFT JOIN location l ON (e.location_id = l.id)
        LEFT JOIN clients c ON (e.event_client_id = c.id)
        LEFT JOIN equip_type q ON (e.req_eq_type_id = q.eq_type_id)
        ORDER BY eq_num, event_date DESC

Why can't you use the MAX() function?

I did the following

mysql> CREATE TABLE fred (myd date);
Query OK, 0 rows affected (0.31 sec)

mysql> INSERT INTO fred VALUES('2015-10-05');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO fred VALUES('2014-10-05');
Query OK, 1 row affected (0.07 sec)

mysql> SELECT MAX(myd) FROM fred;
+------------+
| MAX(myd)   |
+------------+
| 2015-10-05 |
+------------+
1 row in set (0.00 sec)

MAX(myd) works perfectly. Could you show where "I can't get MAX() to function with this." is failing, along with the error message?


postgres9.5: can't create extension on standby I can't create extension on postgresql9.5 server (hot standby). During to attempting I received:

db=# create extension pgrowlocks;
ERROR:  cannot execute CREATE EXTENSION in a read-only transaction

Can anyone assist? Thnx.

As the error message says: the standby is read-only you can't run any statements on the standby that change things.

If you need the extension on the standby you need to create it on the master


SQL Server Agent Service deleted Has anybody run into the issue where an application (arcserv) using SQL Server Express 2008 is uninstalled on a server running SQL Server 2014? When arcserv was uninstalled, it or user deleted the SQL Server agent. What was/is the agent recovery process,

run the repair from the install media
run the install media again
uninstall SQL Server and install again

Our efforts to repair ran into multiple issues including registry component keys that we could not access due to missing permissions.

SQL Server 2008 Express shouldn't have a SQL Server Agent, so I'm assuming that the 2014 Agent is what was uninstalled.

I would run a repair from the SQL Server 2014 install media, not the arcserv media.  It's a good idea to make a backup of all of the SQL Server databases in the 2014 instance before doing this.


Proportion of MySQL variables I made some research on MySQL performance.
After doing some tests and verifyed the improvement, (thank you guys)
I end up asking myself:
"Is there any formula or metric between groups of variables?"

I mean, given a server with X GB of RAM and X2 processor

innodb_additional_mem_pool_size + innodb_buffer_pool_size <= Y% of X

innodb_log_file_size + innodb_log_buffer_size <=  Z% of X

and so on...

Is there any specific proportion between this group of variables?

There is one serious effort to do this - it can be found here. Check out the warning though

  WARNING

  It is extremely important for you to fully understand each change you
  make to a MySQL database server.... &c...

It is virtually impossible to factor in all variables - at the end of the day, it comes down to MySQL expertise. The author (Major Hayden) is a principal architect with RackSpace, so one can assume that he has some expertise.


Linked Server to SQL Server 2000 server from SQL Server 2012 I have a new SQL Server 2012 instance, and am migrating a database from SQL Server 2000. Some of the stored procedures in the DB I am moving reference another database back on that 2000 server. What is the best way to handle this?

Would a linked server help? I am having trouble creating a link for testing. Some people recommend using ODBC, others seem to use the SQL Server 10 client driver (SQLNCLI10). Using the SQL Server 10 client driver seems better to me, are there issues installing it on a SQL Server 2012 instance?

Be careful when using SQLNCLI10 as described in connect item

A workaround can be found here

You have to first test the application that is using such SP's from 2012 to 2000. It also depends on where both server's are located and how much data you are pulling out using Linked Servers.

Is there a reason that the other referenced databases cannot be moved to 2012 ?


Parallel Statistics Update In SQL Server 2008 or later, is UPDATE STATISTICS WITH FULLSCAN a single threaded operation or it can use parallelism? How about update statistics with default sampling - can it use parallelism? I don't see an option specifying MAXDOP with update stats.

Parallel statistics update has been available since SQL Server 2005. It is documented in the TechNet article, "Statistics Used by the Query Optimizer in Microsoft SQL Server 2005":

Where a full scan is performed (whether explicitly requested or not) the internal query generated for the data-gathering has the general form:

SELECT
    StatMan([SC0])
FROM
(
    SELECT TOP 100 PERCENT
        [Column] AS [SC0]
    FROM [Table] WITH (READUNCOMMITTED)
    ORDER BY [SC0]
) AS _MS_UPDSTATS_TBL
OPTION (MAXDOP 16)

Notice the MAXDOP hint there (though there is no way for a user to specify the hint manually). Where sampled statistics are gathered, the internal query uses the TABLESAMPLE clause, which prevents parallelism. The engine also generates a MAXDOP 1 hint on the internal query, which is a bit redundant.

If you ever need to reduce the parallelism, the MAXDOP hint in the internal query can be overridden using Resource Governor (Enterprise only).

SQL Server 2016 adds parallel sampled statistics update.


Adding counts inside a COALESCE So I have a continuation of this question. New requirements have thrown in a third table.

So here's the breakdown of how the tables relate:

TabA(TabA_id, desc)
TabB(TabB_id, TabA_id)
TabC(TabC_id, TabA_id)

I need a count of each time a TabA desc occurs in both TabB and TabC, and I only need the records from TabC that have a TabA_id.

This is the query that is getting me close (sort of):

select  COALESCE(count(b.TabC_id) + count(c.TabC_id), 0) as "Number of Records",
a.desc as "Type",
to_char(round(ratio_to_report(count(b.TabC_id) + count(c.TabC_id)) over()*100)) || '%' as "Percentage of Total"
from TabA a
left join TabB on a.TabA_id = TabB.TabA_id,
TabB b
left join TabC on b.TabC id = TabC.TabC_id,
TabC c
right join TabA on c.TabA_id = TabA.TabA_id
where c.TabA_id is not null
group by b.TabC_id, c.TabC_id, a.desc
order by a.desc asc;

Hope this makes some sense. Looking forward to comments for clarification.

I'm giving it a shot, still not sure if I got it:

select 100.0*(count(b.taba_id) + count(c.taba_id))
     / (( select count(1) from b) + (select count(1) from c))
from taba as a
left join tabb as b
    on a.id = b.id
left join tabc as c
    on a.id = c.id

Some thoughts, count will newer return null so coalesce is pointless. If the above is correct it is possible to use window function (as I think you have tried to do), to replace the total count's


Sp_BlitzWho can get blocked...? I was playing around with sp_BlitzWho and got blocked by another session. The session was from the DB were sp_BlitzWho is... I was planning to automate it and save the results to a table, so I could analyze the accumulated wait stats for different sessions (I have heard it does that in contrast to sp_WhoIsActive), but now it seems not to be such a good idea... Maybe it is better to just use sys.dm_exec_session_wait_stats or sp_WhoIsActive...?

You have a couple of different questions in here.
Can sp_BlitzWho get blocked?
Yes, any query can. There are DMVs that are known to ignore isolation level requests and incur blocking.
Should I use sp_BlitzWho to log wait stats by session?
I wouldn't recommend doing that on a scheduled basis, no. If you need wait stats by session, you're better off with a traditional monitoring tool with a higher sampling rate. You wouldn't want sp_BlitzWho running, say, every 5 seconds, but you really need data at that level of granularity if you want to analyze wait stats by session.
Why shouldn't I log wait stats by session?
Because your server can be a victim of the death-by-a-thousand-cuts scenario: a query that runs thousands of times per second, each time darting in and out, and never accumulating that much wait time each time it runs. However, in total, it can add up to a big deal.


How to get time_zone from other sessions Is there some way to find out the time_zone from other sessions defined in session level (alter session set time_zone = ..)?

While the V$SES_OPTIMIZER_ENV view lets you see the values of parameters that affect the Cost Based Optimizer across sessions, there does not appear to be any view that lets you see non-performance related settings. Besides the security implications of seeing that information, generally those settings are of interest only to an individual session.


Insert query with a subquery I want to insert somewhere around 400 rows into a table. All of the column values will be the same fixed value for most of the columns, with exception to one column which I want to pull from a fairly complicated subquery that will pull over 400 unique values from another table using a series of joins and where statements. We attempted the following query:

INSERT INTO mytable (col1, col2, col3, col4)
VALUES(val1, (subquery), val2, val3)

It stated, that it wouldn't work because the subquery yielded more than one result. The only values being pulled from a different table is in the subquery. I cannot insert the subquery into the respective column by itself, since col1 is a NOT NULL column.

If the (subquery) has SELECT (whatever expression) AS col FROM ..., then you can do:

INSERT INTO mytable
  (col1, col2, col3, col4)
SELECT
  val1, s.col, val2, val3
FROM
  (subquery) AS s ;

or:

WITH s (col) AS
  (subquery)
INSERT INTO mytable
  (col1, col2, col3, col4)
SELECT
  val1, s.col, val2, val3
FROM
  s ;


Is there any way to get the optimization time in PostgreSQL? I'd like to see how long it takes to optimize a query. Does PostgreSQL support this feature?

Yes, starting with Postgres 9.4 this information is included in the execution plan when using the analyze option:

psql (9.4.1)
Type "help" for help.

postgres=> explain (analyze,verbose)
postgres-> select count(*)
postgres-> from product;
                                                                             QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=8431.92..8431.92 rows=1 width=0) (actual time=132.491..132.491 rows=1 loops=1)
   Output: count(*)
   ->  Index Only Scan using product_pkey on public.product  (cost=0.42..7181.92 rows=500000 width=0) (actual time=0.622..91.129 rows=500000 loops=1)
         Output: product_id
         Heap Fetches: 8
 Planning time: 2.756 ms
 Execution time: 132.614 ms
(7 rows)


Problem with How to join with subquery I'm trying to create a report in Postgresql that shows some columns from some tables with join. But one of them is subquery that to show a customer with the location. My Problem is, I always get notice like ERROR: column foo.[column] does not exist.

This is the simply what I've done. sqlfiddle.com

Maybe someone can help, thanks!

Table definitions and data:

CREATE TABLE public.tprog (
    progId integer NOT NULL,
    codeProg character varying(20) NOT NULL,
    nmProg character varying(100)
);
INSERT INTO tprog VALUES (1, '01PROG', 'Prog One');

CREATE TABLE public.tkeg (
    kegId integer NOT NULL,
    codeKeg character varying(20) NOT NULL,
    nmKeg character varying(100),
    progId integer NOT NULL
);
INSERT INTO tkeg VALUES (1, '01KEG', 'Keg One', 1);

CREATE TABLE public.mDonation (
    mDonationId integer NOT NULL,
    nmDonation character varying(100)
);
INSERT INTO mDonation VALUES
(1, 'Flood donation'),
(2, 'Tsunami donation');

CREATE TABLE public.tDonation (
    tDonationId integer NOT NULL,
    customerId integer,
    mDonatioanId integer,
    month character varying(10)
);
INSERT INTO tDonation VALUES
(1, 1, 1, '2019-01'), (1, 2, 1, '2019-02'), (1, 3, 2, '2019-03');

CREATE TABLE public.tmonth (
    tmonthId integer NOT NULL,
    progId integer NOT NULL,
    kegId integer NOT NULL,
    tDonationId integer
);
INSERT INTO tmonth VALUES (1, 1, 1, 1);

CREATE TABLE public.tCustomer (
    tCustomerId integer NOT NULL,
    nmCustomer character varying(30),
    loc3Id integer NOT NULL
);

INSERT INTO tCustomer VALUES
(1, 'Dani', 1),
(2, 'Sarah', 2),
(3, 'Jin', 3);

CREATE TABLE public.loc1 (
    loc1Id integer NOT NULL,
    nmloc1 character varying(50)
);
INSERT INTO loc1 VALUES (1, 'Country1');

CREATE TABLE public.loc2 (
    loc2Id integer NOT NULL,
    nmloc2 character varying(100) DEFAULT NULL::character varying,
    loc1Id integer
);
INSERT INTO loc2 VALUES
(1, 'SubCountry1 A', 1),
(2, 'SubCountry1 B', 1);

CREATE TABLE public.loc3 (
    loc3Id integer NOT NULL,
    nmloc3 character varying(100) DEFAULT NULL::character varying,
    loc2Id integer
);
INSERT INTO loc3 VALUES
(1, 'SubCountry1 A1', 1),
(2, 'SubCountry1 A2', 1),
(3, 'SubCountry1 B1', 2);

Query that results in error:

SELECT a.nmprog, b.nmkeg, f.nmDonation, foo.nmloc1, foo.nmloc2, foo.nmloc3, foo.nmCustomer
FROM tprog a
LEFT JOIN tkeg b ON a.progId=b.progId
LEFT JOIN tmonth d ON d.kegId=b.kegId
LEFT JOIN tDonation e ON e.tDonationId = d.tDonationId
LEFT JOIN mDonation f ON f.mDonationId = e.mDonatioanId
LEFT JOIN (
  SELECT z.nmloc1, y.nmloc2, x.nmloc3, w.nmCustomer FROM tCustomer w
  LEFT JOIN loc3 x ON x.loc3Id=w.loc3Id
  LEFT JOIN loc2 y ON y.loc2Id=x.loc2Id
  LEFT JOIN loc1 z ON z.loc1Id=y.loc2Id
) AS foo
ON foo.tCustomerId=e.customerId
WHERE e.month LIKE '%2019-01%'
;

  ERROR: column foo.tcustomerid does not exist Position: 508

The problem is:

Solution:

Result:

Thanks!


Convert Oracle database to Derby I need to migrate an existing Oracle Database into a Derby one. I want to know if there's a tool, a script or another way to do that work.

It is using any of the interesting features of Oracle, as I can see from the database information from SQL Developer, except sequences and indexes.

Thanks!

Apache DdlUtils can help with some of the migration: http://db.apache.org/ddlutils/

You might also have a skim of the tools listed here: http://wiki.apache.org/db-derby/UsesOfDerby to see if any of those have features that help with migration

Derby also has some built-in tools for bulk-importing of data: http://db.apache.org/derby/docs/10.9/ref/rrefimportproc.html


I am getting an error while trying to download binary for mysql 5.5.11 I am trying to upgrade MySQL 5.1 to 5.5.11 on Ubuntu 10.10. Everytime I try the wget command I get this error:

wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.11-linux2.6-i686.tar.gz/from/http://mysql.llarian.net/
--2012-01-27 16:16:08--  http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.11-linux2.6-i686.tar.gz/from/http://mysql.llarian.net/
Resolving dev.mysql.com... 156.151.63.11
Connecting to dev.mysql.com|156.151.63.11|:80... connected.
HTTP request sent, awaiting response... 404 Not found
2012-01-27 16:16:08 ERROR 404: Not found.

I'm guessing that you got that link from http://www.ovaistariq.net/490/a-step-by-step-guide-to-upgrading-to-mysql-5-5/?  At any rate, MySQL is up to 5.5.20 now and I don't think they offer older versions of 5.5.x.  If you want an older GA release, you'd have to download 5.1.61.

If you visit http://www.mysql.com/downloads/mysql/ in your browser, you can get a correct URL to a current version.


Script to Delete records in multiple table I would like to delete a specific record from two or more tables which are linked to one another.

For Example: I have two tables, Students and winners I would like to delete the names Roy and Peter from both the tables in one shot.

table: Students

> ID     name      class
> 1      Roy         2
> 2      James       3
> 3      Carl        4
> 4      Peter       4
> 5      Alice       5

table: Winners

St_ID          achievement
1              1
2              1
3              3
4              5
5              5

I have more than 100 tables with 50 specific records to be deleted from all the tables.

You have to achieve this using Dynamic SQL Query

1- First you have to list all Tables With there Corresponding Databases in a Temp Table
2- Building Query for databases that contains Students and Winners dataTable

Your script should look like

--Get all Databases With corresponding Database

declare @SQL nvarchar(max)
DECLARE @strQuery AS NVARCHAR(MAX)

SET @strQuery = ''

CREATE TABLE #TblTemp (DatabaseName Varchar(255), Tablename Varchar(255))

set @SQL = (select 'union all
select '''+D.name+''' as DatabaseName,
       T.name collate database_default as TableName
from '+quotename(D.name)+'.sys.tables as T
'
from sys.databases as D
for xml path(''), type).value('substring((./text())[1], 13)', 'nvarchar(max)')

--print @SQL
INSERT INTO #TblTemp
exec (@SQL)

-- Building Queries

SELECT @strQuery = @strQuery + 'Delete T1 from [' + name  + '].dbo.Students As T2
                        Inner join  [' + name  + '].dbo.Winners as T1
                        On T1.[st_ID] = T2.[ID]
                        Where    T1.[name] = IN(''Roy'',''Peter'')  ;

DELETE FROM [' + name  + '].dbo.Students WHERE [name] = IN(''Roy'',''Peter'') ;

'

 from sys.databases
 WHERE EXISTS (SELECT 1 FROM #TblTemp WHERE #TblTemp.DatabaseName = name AND #TblTemp.TableName = 'Students') AND
 EXISTS (SELECT 1 FROM #TblTemp WHERE #TblTemp.DatabaseName = name AND #TblTemp.TableName = 'Winners')

--VIEW QUERIES (you can copy result and execute it manually)
SELECT @strQuery

--EXECUTE QUERIES
EXEC(@strQuery)

--DROP Temp Table
DROP TABLE #TblTemp

This Will Result a query like the following (If these databases contains Students and Winners Table)

Delete T1 from [master].dbo.Students As T2   Inner join  [master].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;
DELETE FROM [master].dbo.Students WHERE [name] = IN('Roy','Peter') ;
Delete T1 from [tempdb].dbo.Students As T2   Inner join  [tempdb].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;
DELETE FROM [tempdb].dbo.Students WHERE [name] = IN('Roy','Peter') ;
Delete T1 from [model].dbo.Students As T2   Inner join  [model].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;
DELETE FROM [model].dbo.Students WHERE [name] = IN('Roy','Peter') ;
Delete T1 from [msdb].dbo.Students As T2   Inner join  [msdb].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;
DELETE FROM [msdb].dbo.Students WHERE [name] = IN('Roy','Peter') ;
Delete T1 from [AdventureWorks2008R2].dbo.Students As T2   Inner join  [AdventureWorks2008R2].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;     DELETE FROM [AdventureWorks2008R2].dbo.Students WHERE [name] = IN('Roy','Peter') ;     Delete T1 from [DbMail].dbo.Students As T2   Inner join  [DbMail].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;

I don't Now if that is what you was asking for in your question below (Your question was not specific like this one, so my answer was providing the dynamic sql logic in general)

Truncate with where clause


Optimal time interval for scheduling of Transaction Log Backup I have a database which is currently of size 50 MB. The database is bound to grow by 10 to 15 MB per hour and the data is important. So, I wanted to ask what should be the optimal time interval in which a transaction log backup should be scheduled so that in case there is any data loss then it is as minimal as possible?
Thanks in advance.

The main question to ask yourself is how much data can you afford to lose. That will determine how often you need to backup the log. But there is nothing wrong with running transaction log backups very often, even every minute. This accomplishes 2 things: 1. Provides you with the security that you won't lose data; 2. Improves performance - the less data you backup at any one time the lower your I/O overhead.
I recently read a blog post stating that, from a performance point of view, log backups taken every minute greatly reduced I/O pressure. The downside of such frequently run backups is restoring them. As Bogdan rightly mentions in the comments, you should write a script to perform the restores. Depending on how far back in time you need to go you may need to restore a large number of transaction log backups. Doing that manually is very time consuming and prone to errors. There are probably third party tools that help with this as well.


Why is autovacuum running during a VACUUM FREEZE on the whole database? I have a 14.7 TB Postgres(9.1) database that has to endure heavy writes for prolonged periods of time. I know that I need to closely manage my transaction ids to prevent db lockup. Recently I noticed the queries slowing, and saw that there were multiple autovacuums running on our massive read-only tables, with the "(to prevent wraparound)" subscript. I stopped the running software and executed a vacuumdb -F -a command. However, when I run select current_query from pg_stat_activity I see that the autovacuum processes are still running, even during a manual vacuum. I tried killing the autovacuums with select pg_cancel_backend(pid) and they died but then immediately restart again. My questions:

Is autovacuum supposed to continue to run during a manual db vacuum?
How do I effectively get these autovacuum processes to stop?
Why would these autovacuums continue to run on a read-only table? What is there to vacuum?

Autovacuum is triggered by the table statistics on the table, and as long as your manual VACUUM (FREEZE) is not done, these are not updated. That is why anti-wraparound autovacuum processes will still start.

But that's not a big problem: Only one VACUUM can run on a table at any given time. Now anti-wraparound autovacuum workers won't give up when they block another process, in that case your manual VACUUM. But if you kill the anti-wraparound autovacuum workers, your manual VACUUM gets the lock, and now it is the restarted anti-wraparound autovacuum worker that is blocked. Look at pg_locks to verify that they are waiting for a lock (granted = FALSE).

Now your vacuumdb processes one table after the other, so you must be ready to kill autovacuum workers as soon as it starts processing the next table and is blocked by a new autovacuum worker there.

It might be easier to manually launch VACUUM on those big read-only tables rather than using vacuumdb, because then you can control when which table gets vacuumed.

Make sure you set maintenance_work_mem high to speed up VACUUM. You should also set autovacuum_vacuum_cost_delay to 2ms or lower at least on these big tables, so that future autovacuum runs get done faster.

To reduce the pain for the future, lower autovacuum_freeze_max_age on the affected tables substantially. Then the next anti-wraparound vacuum will kick in sooner and will be done faster.

Best of all, upgrade to v13 as soon as you can, because from that version on insert-only tables will receive regular vacuum runs as well, which should do away with the problem.


Postgresql update timestamp without timezone to have timezone When I created the table, a Timestamp column without timezone was created. All the imported time is in UTC. Now I want to add the timezone information explicitly, and I have altered the column

ALTER TABLE review ALTER COLUMN review_time TYPE TIMESTAMP WITH TIME ZONE USING review_time AT TIME ZONE 'UTC';

Selecting the existing data does not show the added timezone information

review_time
2017-07-28 02:25:44
2017-07-28 03:10:35
2017-07-28 03:11:32
2017-07-28 03:11:35
2017-07-28 03:11:38
2017-07-28 03:11:41
2017-07-28 18:54:54

Do I need to run an UPDATE statement on the existing data, and if so, what is the syntax?

Update 1

The output that is missing timezone information is due to the application itself (SQLWorkbenchJ). Querying from psql will show the timezone

mydb # SELECT review_time FROM review;
      review_time
------------------------
 2017-08-20 08:00:02+08
 2017-07-27 00:45:33+08
 2017-07-27 00:45:37+08
 2017-07-28 02:24:03+08
 2017-07-28 02:24:27+08
 2017-07-28 02:24:31+08
 2017-07-28 02:25:31+08

I also don't see the problem

Create sample data,

CREATE TABLE foo(ts) AS VALUES (
  now()::timestamp without time zone
);

Display it,

TABLE foo;
             ts
----------------------------
 2017-09-30 14:25:24.954084
(1 row)

You can see in the above no tz in output. Now let's change to use a with time zone.

ALTER TABLE foo
  ALTER COLUMN ts
  SET DATA TYPE timestamp with time zone;

Here is the output, notice you have a tz of -05

TABLE foo;

              ts
-------------------------------
 2017-09-30 14:25:24.954084-05
(1 row)

What's your result of SHOW TIME ZONE?

Interestingly though, I do see what you're talking about with,

# SELECT now()::timestamp with time zone::timestamp;
            now
----------------------------
 2017-09-30 14:27:53.061616
(1 row)

However, we have to know what you're asking.


Unknown number of potential values in a table from a foreign table I can't seem to figure out the best way to do this...

Table A                   Table B
    Id                    Id
    TableBId              ValueNeeded
                          TableAId

So I Table A needs values from Table B, where Table B's 'TableAId' is matching the id.

Example

TableA                   TableB
_________________________________________________________
ID        TableBId       Id     ValueNeeded    TableAId
1         ???            1      'Foo'          1
                         2      'Bar'          1

I need Table A to pull both value's from table B, ('Foo','Bar'). but the complicated part is that Table A can only have one column to return multiple values. And I foresee up to nearly 30 potential rows needed from Table B, for Table A. I think I need a lookup table in between, but does said lookup table need to have 30 columns? and what if a use case requires more than those 30 columns? I need this to work somehow

Edit: clarity - I'm creating a web app for myself and some friends to use for Dungeons and Dragons, this is for the 'Proficiencies' portion of the player character generation. Every player gets different proficiencies based on class, race, and level. So a level 1 player might only have 8-15 proficiencies, whereas a level 20 player could have dozens they've amassed. What I'm trying to accomplish is: One player(TableA) with ideally, one column, that points to a reference table(TableB),  that then (ideally) has a few rows (race, class) that then pulls from another table (TableC) to finally fill out all the proficiencies that the player will have. The more I think about it the more sure I am that I need to make the reference table with a LOT of nullable columns....

Every character has one and only one race, right? So it seems like that field should be on the Character table: TableA(?) Each character could have multiple classes so that should probably be in a one-to-many relationship in the CharacterClass table which has the primary key of both Character and Class tables.

As for the Proficiencies (TableB), each character can have zero to many proficiencies so they must be stored in their own table, but if you want to produce a 'report' which shows all the proficiencies as a delimited list, then you could do it like this:

Select c.id,
    proficiencies = Stuff((Select ',' + proficiency
                             From #character_proficiency As cp
                             Where cp.character_id = c.Id
                             For XML Path ('')), 1, 1, '')
  From #character As c;


INSERT violating unique key constraint on primary key on a table with primary key 'id' with default value nextval(), inserting a row throws the error

Query 1 ERROR: ERROR:  duplicate key value violates unique constraint "table_pkey"
DETAIL:  Key (id)=(6) already exists.

except this table has 200 rows. why isn't Postgres setting the new row id as 201?

You inserted rows manually into the table providing explicit values for the id column, which means the sequence was not advanced as no default value was used. Now your sequence is out of sync with the actual values in the table.

You need to sync the sequence using setval() with your actual values if you do that:

select setval( pg_get_serial_sequence('the_table', 'id'),
               (select max(id) from the_table)
             );

If you want to prevent this kind of problem in the future, you can re-define the column as an identity column which will reject passing explicit values for it, so that you get an error if you try to bypass the default value.


How can I optimize searching a column for a sub-string in SQL Server? I have a very basic table:

CREATE TABLE [obj_local] (
    [obj_id]     INT             NOT NULL,
    [value]      NVARCHAR (1000) NOT NULL
);

This table stores a lot of data, and I need to search the value column for a sequence of characters:

SELECT [obj_id] FROM [obj_local] WHERE [value] LIKE '%{substring}%'

This is extremely slow. My understanding is that an index won't help me due to the wildcards on either side, and a full text index also won't help me, because I'm not searching for tokens(words).

Is there anything I can do to optimize this search?

If you know the exact string, then using a binary collation for your search can help.

WHERE [value] LIKE '%{substring}%' COLLATE Latin1_General_BIN

, because it won't have to do case conversions and the like. This can make it a few times faster, but not lightning fast.

Another option is to consider blowing up your table and indexing that - using an indexed view over a john between a table of numbers and your table, leveraging the substring function.

For example, if you have a table of at least 1000 numbers, you could make a row for each character. Yes, it'll cost space, but it will quickly let you find every "H" in there.

Problem is - it's still not quite what you need, because there will be a lot of "H"s. Better might be to grab three-letter combinations. So if you're looking for 'Hotel', you know that either "Hot", "ote" or "tel" must be in the three-letter combo table. You could make an inline table-valued function to handle this. Naturally, when you search for "Hot", the next block for that obj_id must be like 'el%', and so on, plus you should check that your main table still successfully satisfies the main predicate.

It's an idea... if you can put up with the complexity of working around it like this.


SQL Server 2012 on a Windows 2008 R2 cluster, would like to upgrade Windows to 2012 R2 I need someone to help validate a strategy. I have a pair of Windows 2008 R2 server in a active/passive failover cluster with SQL Server 2012 installed.

I would like to upgrade the Windows OS to 2012 R2. I know that you cannot join a Windows 2012 R2 server to a 2008 R2 cluster. I thought about evicting a node and doing a fresh install of Windows 2012 R2 with cluster services, then detach the databases from SQL Server, destroy the 2008 R2 windows cluster, build the 2012 R2 cluster using the same name, IP, and SAN drives, then install SQL Server 2012 as a cluster using the same instance name and attaching the databases.

Here are the steps that I followed to get a Windows 2008 R2 cluster hosting SQL Server to Windows 2012 R2.

In this example we have a 2 node Active / Passive cluster running Windows Server 2008 R2 Enterprise hosting SQL Server 2012 SP2 with CU6, this will work with any version of SQL Server.

Step 1: Document the following:

Drive letter mapping to shared storage on each node, NTFS Shared folders, IP Addresses for LAN and Heartbeat on each node, Name and IP address of the Windows Virtual Cluster Node, Name and IP address of the SQL Virtual Cluster Node, SQL version, patch level, and features installed, The location of the System Databases

Step 2: In Failover Cluster Manager – Evict the passive node, in this example Node 2 is the passive node.

Step 3: Rebuild Node 2 with Windows Server 2012 R2, using the same server name and IP address. Verify that Node 2 can see the shared storage, but do not make them online at this point.

Step 4: Stop the SQL Services on Node 1.

Step 5: Locate the folder containing the system databases and move them to a secure location. Make sure you get all the system database including TempDB, MSDB, report server, and distribution if you have replication.

Step 6: Shutdown Node 1.

Step 7: In Active Directory Users and Computers disable the accounts for the Windows Cluster Node and SQL Cluster Node.

Step 8: On Node 2, Create the Windows Failover Cluster using the previous Windows Cluster Name and IP. At this point the shared drives will be present, verify they have the correct drive letters.

Step 9: Install SQL Server as a cluster using the same name, IP address, include all features required.

Step 10: Patch SQL Server to the correct SP and CU.

Step 11: Stop the SQL Services using SQL Server Configuration Manager.

Step 12: Copy the system database files that you saved from Step 5 over top the system database files from this new installation.

Step 13: Start the SQL Services using SQL Server Configuration Manager, verify all databases and logins are available.

Step 14: Install Windows Server 2012 R2 on Node 1 and join the Windows Failover Cluster.

Step 15: Install SQL Server as a new node to the SQL Cluster, patch to correct level.


check if some tables exist or not in SQL Server database What is the best way to check if more than one table exist in a database using SQL Server, I tried with this:

IF NOT EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
           WHERE TABLE_NAME = N'Providers') OR NOT EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
           WHERE TABLE_NAME = N'Customers')

but is there a better way to do it  ?

Well if you turn on certain options in SSMS like "Check for object existence" you will get a script that looks like this when dropping and re-creating an object (e.g. table):

IF NOT EXISTS
    (SELECT * FROM sys.objects
        WHERE object_id = OBJECT_ID(N'[<schema>].[<object_name>]')
        AND type in (N'U')
    )

Is it better? Uncertain.
Is it different? Yes.
Is it what Microsoft would do? Yes.


Use result of aggregate in same select? Is it possible to feed the result of an aggregate select into the same select with Postgresql?

I'm aware of the WITH clause -- but, this would be true syntactical sugar, wouldn't it?

SELECT some_agg(x) as y, some_func(y) FROM z;

Is Postgres smart enough to run the aggregation once if it's written as:

SELECT some_agg(x), some_func( some_agg(x) ) FROM z;

As far as I'm aware (up to 9.2), you can not.  They may have implemented that change in 9.3 or 9.4 ... but typically you would use a CTE (WITH) or just the same aggregate function..

SELECT some_agg(x) as y, some_func(some_agg(x)) from z;

I seem to recall that postgres is smart enough to know that it can reuse the some_agg(x) without having to calculate it twice.

EDIT - Adding a CTE example

WITH my_cte AS (
  SELECT
    id,
    some_agg(x) as y
  FROM tab
  GROUP BY id
) SELECT
  id, y, some_func(y)
FROM my_cte;

The easiest way to know for sure if it calculates it more than once would be to run an EXPLAIN on the plan and see.  :)

I entered the following SQL on a local DB that I have.. Below is the SQL and Explain Plan.

explain analyze
select
  product, sum(price) as x, sum(price) as y
from ticketlines
group by product
having sum(price) > 300;

You can see I have the same aggregate used 3 times ... here is the explain output:

HashAggregate  (cost=796.36..796.97 rows=49 width=16) (actual time=4.650..4.658 rows=26 loops=1)
  Filter: (sum(price) > 300::double precision)
  Rows Removed by Filter: 24
  ->  Seq Scan on ticketlines  (cost=0.00..692.68 rows=10368 width=16) (actual time=0.006..1.020 rows=10368 loops=1)
Total runtime: 4.699 ms

It only calculates the aggregate once.


Prevent duplicate record counts in one to many query I'm trying to get a count of ethnicity for a teachers students. My first table contains the student number and ethnicity. The second table contains teacher id's, student numbers, course numbers, and etc... Each student can have a teacher more than 1 time.

Here's my query that is returning duplicate counts:

SELECT DISTINCT Count(StudentsSDRC.Ethnicity) AS CountOfEthnicity,
                StudentsSDRC.Ethnicity
FROM StudentsSDRC
    LEFT JOIN TeacherStudents
        ON StudentsSDRC.StudentNum = TeacherStudents.StudentNum
WHERE (((TeacherStudents.teacherDCID)=43652))
GROUP BY StudentsSDRC.Ethnicity

Any help would be great.

SELECT DISTINCT is not the answer to the issue. You are right that the join is the problem. It produces multiple rows for the same student, when the student is connect to a teacher more than once.

One way to solve this is by using an EXISTS subquery:

SELECT Count(*) AS CountOfEthnicity,
       s.Ethnicity
FROM dbo.StudentsSDRC AS s
WHERE EXISTS
      ( SELECT *
        FROM dbo.TeacherStudents AS t
        WHERE s.StudentNum = t.StudentNum
          AND t.teacherDCID = 43652
      )
GROUP BY s.Ethnicity ;

Other irrelevant to the problem notes / improvements:

The LEFT JOIN was acting as an INNER JOIN as one of the right table's columns was in the WHERE condition.
It's good to alias your tables with shorter names. Improves readability of the queries.
It's good to prefix the tables with the schema names (eg. dbo).
The pile of parentheses was not needed at all.

If you prefer the join for some reason, then this would work, too. I personally don't find it as explanatory as the 1st query.

It assumes that StudentNum is UNIQUE in table StudentsSDRC:

SELECT Count(DISTINCT s.StudentNum) AS CountOfEthnicity,
       s.Ethnicity
FROM dbo.StudentsSDRC AS s
    JOIN dbo.TeacherStudents AS t
        ON s.StudentNum = t.StudentNum
WHERE t.teacherDCID = 43652
GROUP BY s.Ethnicity ;


Creating an admin user in Oracle How can I create a "root" (like on MySQL) in Oracle?

I installed Oracle database on my system, for homework, for school (faculty) but it didn't give me the option to make a root user. Well at least I don't think so.

It asked me on preparation for install to create a password but when I tried connecting with the id and password it failed. (So that is why I presume it did not make a root user)

Connected on the databse with connect /as sysdba but don't know how to create a user with admin privileges.

Tried

create user "user" identified by "password" with grant option;

(error)

create user "user" identified by "password";

(succes)

grant all on "user" with grant option;

(error)

I want to have all privileges on all tables, etc.

you first need to connect as root to the database, then you create the admin user.

console

connect /as sysdba

sql

create user admin identified by secret
grant dba to admin
conn admin/secret


Backup VLDB replicated database in SQL Server? I have a problem with my backup strategy.

Currently I'm doing a Full Backup every night and transaction log backup every 2 hours to TAPE.

This used to work without problems, but the size of the DB is almost 2TB and the Full Backup takes 8 to 12 hours to complete overnight.

During the night I have several jobs running that are affected by the backup.

I was thinking of a different strategy using DB replication that would let me keep 2 databases synced and then only backup the "replicated" database, leaving the production database without an online backup.

Could this be a problem? Do I have any risk of losing information? What do I do in this case if I need to restore a backup?

I don't think this is a very good idea because replication is not just as bullet proof as a sound backup strategy. A blocking situation could stop replication jobs and you could easily be out of sync for some time. Or maybe a network card will fail and replication will be dead for a period. This means you won't be up to date for some time, and also, you will be without a good backup of the main, updated site.

Similar technologies, that will deliver updates to a copy of the main db, are mirroring and log shipping, but they both rely on full recovery model and log backups.

I'd suggest that you'd better study compression for your backups. Starting from SQL 2008 (Enterprise) and SQL 2008 R2 (from Standard) you have the option of compressing the backups using native SQL compression. In addition you should investigate data compression (in place compression for tables and indexes data). These will give you some help by making the backups smaller.

There are also 3rd party backup tools that will be able to help with compression if you need more complex solutions. See this site for question about how to make SQL Server backups as small as possible.

Also, you might think about moving old data from big tables to a history database, and play with connection strings in your app.

You could also try to make the backups locally, in a folder on the main server, and then just copy it to the tape (maybe backup to disk + direct copy will take less than backup to tape).

Sorry for the emphasis: YOU JUST WON'T BE SAFE WITHOUT TESTED PRODUCTION BACKUPS!


MySQL : perform a big data migration between tables I want to migrate data between 2 InnoDB tables.

Currently I'm running this query:

INSERT INTO table_a SELECT * FROM table_b;

If the dataset grows, what's the best way to avoid CPU overload?

Thanks

Since you are using InnoDB I would like to suggest the following:
SUGGESTION #1
If you do INSERT, UPDATEs, and DELETEs, bulk load the table like this:
CREATE TABLE table_new LIKE table_a;
INSERT INTO table_new SELECT * FROM table_b;
ALTER TABLE table_a RENAME table_old;
ALTER TABLE table_new RENAME table_a;
DROP TABLE table_old;

If you do nothing but INSERTs and SELECTs, load new entries into the table. Assuming the primary key of table_a andtable_b is id, perform the load like this:
CREATE TABLE table_new LIKE table_a;
INSERT INTO table_new SELECT B.* FROM table_b B
LEFT JOIN table_a A USING (id) WHERE A.id IS NULL;
INSERT INTO table_a SELECT * FROM table_new;
DROP TABLE table_new;

SUGGESTION #2 : Tune InnoDB for Multiple CPUs
Make sure you are using MySQL 5.5. If you have MySQL 5.1.38 or above, you must install the InnoDB Plugin. If you have MySQl 5.1.37 or prior, just upgrade to MySQL.
Once you have do that (or if you already have MySQL 5.5), you must tune InnoDB for Multiple CPUs. Rather than reinvent the wheel, here are my past posts on how and why to do so:

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian
Apr 26, 2012 : Is the CPU performance relevant for a database server?

Give it a Try !!!
I could suggest other things such as buffers, log files, and so forth. I only addressed just these two concerns.


Should I disable autovacuum on a table while I do a bulk update? I need to perform a simple update on all rows in a table. The table has 40-50 million rows. Dropping indexes and constraints during the UPDATE results in a massive performance improvement.

But what about autovacuum? Can autovacuum start a VACUUM or ANALYZE in the middle of an UPDATE? If so, it would be useless work that would eat up machine resources. I could disable it on the table prior to the UPDATE and then re-enable it afterwards:

ALTER TABLE my_table
SET (autovacuum_enabled = false, toast.autovacuum_enabled = false);
-- Drop constraints, drop indexes, and disable unnecessary triggers

UPDATE my_table SET ....;

-- Restore constraints, indexes, and triggers
ALTER TABLE my_table
SET (autovacuum_enabled = true, toast.autovacuum_enabled = true);

Does this even work if I don't commit after the first ALTER?

Also, if I disable it during the UPDATE, will it trigger after the update, or will it ignore those updates because it was disabled during them? (My suspicion is that it will run, but I'd rather be sure.)

I'm using PG 9.3 right now, but should be upgrading soon. So any mention of changes in newer versions is appreciated.

No

There is simply no point. autovacuum will not clean up any rows locked in a running transaction. So autovacuum

may not do anything useful.
may report that the rows are dead and nonremovable
may do something useful with the other rows (if there are any).

But, it won't block or slow down the update in any meaningful sense that I'm aware of.


Backup name in Maintenance plan I used to have SQL Server 2005 installed on my server. In my maintenance plan, the backup name format was like this: mydatabase_backup_yyyymmddhhss.bak.

Now I am using SQL Server 2012 but I am getting a totally different backup name format. I would like to have the same format name as 2005.

How can I do this?

You would need to build a custom backup script which uses dynamic SQL to build the name of the .bak file. The maintenance plan could execute this with the 'Execute T-SQL Statement Task' item.

The T-SQL would look something like this

Select @SQL = 'BACKUP DATABASE [' + @DBName + '] TO  DISK = N''' + @Path + '\' + @DBName + '_backup_' + @Date + '.bak'' WITH NOFORMAT, INIT,  NAME = N''' + @DBName + '_backup_' + @Date + ''', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10 '

EXEC SP_ExecuteSql @SQLToExecute = @SQL


Why are there different levels of MySQL collation/charsets? Looking at the MySQL server variables the server and database collation are set to latin1_swedish_ci but the collaction_connection is utf8_general_ci. Additionally, the collation/charset is set at many levels: server, database, table and column. You also have the charset of the PHP MySQL connection to consider.

My question is fourfold:

Why are there different levels of MySQL collation/charsets? Is it so you can mix up your character sets to suit your needs? Correct me if I'm wrong but utf8 seems to be the best character set for general use so why is it that most of the LAMP setups I've used have latin1 as default?
Should you always ensure your PHP connection matches the charset of the database you're working on?
If you can have different tables that use different character sets do you just use SET NAMES or mysql(i)_set_charset to switch?
If you have a table that has multiple charsets how do you manage that since the connection can only use one charset at a time?

Many thanks.

EDIT:

Regarding the comment:

“You can use either, the former one only to set results charset and the latter one to set either PHP internal encoding for use with mysqli_real_escape_string and results encoding.”

I thought you aren't meant to use real_escape_string and SET NAMES together. See: http://www.php.net/manual/en/mysqlinfo.concepts.charset.php

QUESTION #1

  Why are there different levels of MySQL collation/charsets?

ANSWER TO QUESTION #1

There are two good reasons for different character sets and collations

Reason #1 : Disk Space

When you run this query

SELECT
    maxlen,
    GROUP_CONCAT(CHARACTER_SET_NAME) CharSets,
    COUNT(1) CharSetCount
FROM information_schema.character_sets
GROUP BY maxlen\G

You get this:

mysql> SELECT
    ->     maxlen,
    ->     GROUP_CONCAT(CHARACTER_SET_NAME) CharSets,
    ->     COUNT(1) CharSetCount
    -> FROM information_schema.character_sets
    -> GROUP BY maxlen\G
*************************** 1. row ***************************
      maxlen: 1
    CharSets: cp1257,cp850,binary,koi8r,latin2,ascii,tis620,koi8u,greek,armscii8,keybcs2,macroman,latin7,cp1251,cp1256,dec8,hp8,geostd8,latin1,swe7,hebrew,cp1250,latin5,cp866,macce,cp852
CharSetCount: 26
*************************** 2. row ***************************
      maxlen: 2
    CharSets: big5,cp932,sjis,gbk,ucs2,euckr,gb2312
CharSetCount: 7
*************************** 3. row ***************************
      maxlen: 3
    CharSets: eucjpms,ujis,utf8
CharSetCount: 3
*************************** 4. row ***************************
      maxlen: 4
    CharSets: utf16,utf32,utf8mb4
CharSetCount: 3
4 rows in set (0.00 sec)

mysql>

Some character sets have a Maximum Length of 1 byte to represent a character. Other need more. Give this information, you may want to refrain from using the eucjpms, ujis, utf8, utf16, utf32, utf8mb4 character sets so that VARCHAR and TEXT data takes less space on disk.

Reason #2 : Internationalization

Characters Sets Each Come With One or More Collations to cover a variety of Languages

When you run this query

SELECT
    A.CHARACTER_SET_NAME,
    GROUP_CONCAT(COLLATION_NAME) Collations,
    COUNT(1) CollationCount
FROM
    information_schema.character_sets A
    INNER JOIN information_schema.collations B
    USING (CHARACTER_SET_NAME)
GROUP BY A.CHARACTER_SET_NAME\G

You will see that some Characters Sets have with multiple collations for Different Parts of Europe. Chinese, Japanese, Greek, and parts of Asia Minor and Scandinavia are also available.

QUESTION #2

  Should you always ensure your PHP connection matches the charset of the database you're working on?

ANSWER TO QUESTION #2

SCENARIO

You are driving at 3:00 AM. You are the only driver on the road. You come to an intersection. You have the red light.

Question : Do you stop or go through the red light?

Answer : Depends on the neighborhood

Safe neighborhood ?

Some abide by the law, stop at the red, and wait for green.
Some chance it and go through

Bad neighborhood or new to the area ?

Some abide by the law, stop at the red, and wait for green AT THE RISK OF A CARJACKING
Some chance it and go through to AVOID OR REDUCE RISK OF A CARJACKING
Assume the worst and find another route

How does this apply?

You should err on the side of caution. You should always check the charset beforehand because you do not know the neighborhood (client program, internet browser) the PHP connection will be entering and if there is a risk of a carjacking (putting invalid data into the database, requesting too much data for retrieval).

QUESTION #3

  If you can have different tables that use different character sets do you just use SET NAMES or mysql(i)_set_charset to switch?

ANSWER TO QUESTION #3

By all means

QUESTION #4

  If you have a table that has multiple charsets how do you manage that since the connection can only use one charset at a time?

ANSWER TO QUESTION #4

You may have to shift character sets with the DB Session. Here are the settings that can be changed at the session level:

character_set_client
character_set_connection
character_set_database
character_set_filesystem
character_set_results

Please set these carefully before reading from and writing to the database. It would also be wise to store the character set name and collation in the same table you will be accessing.


what is the best choice from those two tables? what is the best choice from those tables??

table A : keep DB simple with lower number of tables ... but i will find difficulties if i wanted to extract certain data from column of doses coz it's crowded with age ranges and doses and many routes of admin ... so if i wanted to make analysis for example: all doses given i.v. through all my DB.. i will face difficulties
table B : it make things arranges in separated tables, so it will be easier to retrive data easily..but for one point i had many tables and i have to link them to each others
now i ask: what is the best way to manage my DB and for querying data from it effectively and easily .. following example A or example B ???

Option B is correct.

Quite simply: each row/column stores one and only one item of information

A multiple table design is quite normal: it comes out of the normalisation process which starts with option A as your business data model.


SQL2012 - Send backup to client via SQL Job Something I thought would be fairly easy..

We host a website for a client. They would like us to send them a database backup monthly. They have Email limits that make sending via EMail not viable. They don't have an FTP site.

I'm trying to come up with a fairly simple, reliable way to (from a scheduled SQL job) copy a backup file to a (free) cloud account like GoogleDrive or OneDrive or ?? that the client can easily access.

No problem creating a SQL job for the backup, scheduling it, etc. the problem is how to then copy the file to an internet destination with minimum hassle. The jobs run under the default SQL Service account.

I'm open to suggestions..?

Assuming that all the various security concerns are met; I would use SQL Agent to kick off a PowerShell Step that would move the file for you.  Depending on where you end up pushing the file to this can be easier or harder.

Depending on what version of SQL you are using, you also have the option of backing up directly to Azure Blob storage, builtin. More details here Backup and Restore with Azure Blob Storage


MS SQL Server 2012: once a day full replication (automatic backup/restore) for reporting Our company is w/o a DBA, and it has fallen on me (a complete DB noob) to handle some critical database tasks.

I'm looking for the best solution for us to be able to "copy" a full database to another server, once per day.  We want to handle schema changes, and data can be stale up to 24 hours.

Our current DB size is around 16 GBs, and we don't expect it to grow very fast.

What would be best?

transactional replication?  (if so, how do we schedule this to only update once per day?)
backup and restore? (what is the best way to automate this?  I would assume a differential backup / restore would be best for speed / bandwidth?)
any other method that would better fit our needs?

Thanks for your response!.

We want to handle schema changes

Transactional Replication would be hard to maintain. If you do frequent schema changes, then you have to drop replication and recreate it, which is time consuming and on the top of it, you have to maintain distribution database as well. Network latency also plays a big role if you are replicating your entire database.

For you scenario, I would recommend :

If not on enterprise edition, you can use any of below :

Backup and restore with COMPRESSION. SQL Server native compression is very efficient which will reduce the backup size.
Since data can be stale up-to 24 hrs, another option would be to use logshipping. You just have to delay the restore of the log backups on the secondary server. So when you want to update the data on the reporting server, you can kickoff the job manually or it will get kicked off with the schedule. This way, you just have to restore the T-logs on the secondary server, when you want to refresh the data in the reporting (secondary) server.

So when setting up Logshipping, configure the secondary database in STANDBY mode and make sure you select Disconnect users in the database when restoring backups.

If on Enterprise edition, then AlwaysON with readable secondary. This will offload the reporting work from your primary server to the replica.

Note: For completeness of this answer, even though Database Mirroring is announced as Deprecated, it is still supported in SQL Server 2012. You have to be careful, as the database snapshot name will be different from the original database name, so if your app relies on the database name, it will need modification. Also, as snapshot works on "COPY ON WRITE" method, it can be significant overhead if there are multiple snapshots. Also, it will randomizes IO during query processing.


DB_NAME(database_id) return null when executed through linked server I am trying to execute below query :

SELECT DB_NAME(Database_id) FROM [linked_servername].MASTER.SYS.DATABASE_MIRRORING
WHERE database_id > 4 and mirroring_role = 1

But I am getting Result as NULL means database name is null.

The scope of DB_NAME() is limited to the local instance.  It only applies to databases that exist on the server where you are running the query.

Instead of using DB_NAME(), you should join to sys.databases with four part naming, as in the following example:

SELECT DB.name
FROM [linked_servername].master.sys.database_mirroring DM
    INNER JOIN [linked_servername].master.sys.databases DB ON DM.database_id = DB.database_id
WHERE DM.database_id > 4 and mirroring_role = 1;

I notice you have capitalized portions of the query including the master database, the sys schema, and the database_mirroring system table.  These objects exist in lower case, so you should code them in lower case.  For SQL Servers that have a case-sensitive collation, specifying the correct case is important for many reasons.


-skipErrors in Transactional Replication not happening I have set up a transactional replication initialized using backup for a big database(1.6TB).Due to some error(space issue in the subscriber) the replication was not happening and there were replication error for the past 5 days.
So i added space and now i am getting the 20598 and 547 error in Distribution to Subscriber.

I changed the Agent profile and created a custom profile with the -skipError values as 20598:547.
I restarted the Distribution agent by stopping and starting the distribution job in the subscriber(PULL subscriber).
After restarting i see that, Distributor to Subscriber is initialized in replication monitor,but i don't see that the skipping is happening.

How can i solve this issue.?

I prefer not to drop Publisher/Subscriber as i am initializing using backup and it is a big database.

You don't see the "skipped # rows" in the message window?
Can you maybe post the window screenshot?

Like this blog here (it did what you did, skipped 20598:547), restarted agent
https://www.percyreyes.com/2016/07/the-skiperrors-parameter-for.html


Syncronization with transformation I have the following scenario: a database in SQL Server 2012 that must replicate changes in a transactional way to a SQL Server 2000 database.  Since the schemas of two databases differ, a transformation must be made for the destination.  Both databases are in the same network.
What's the better approach?

Use triggers in the source database to make the transformations and perform the changes in the destination.
Use SQL Server transactional replication (but transformations seems not possible, as I know).
Any alternative?

Use a linked server as Aaron mentioned and bulk load your data to your SQL 2000 tables after doing the transformation within SQL 2012. You could just create a few staging tables in SQL 2012 where you can place your transformed data as it comes in.

Beware of linked server from 2012 to 2000 using SQLNCLI10: see here
SQLNCLI10

The new client SQLNCLI11 does not work with SQL 2000 as far as I know.


Replication and REPAIR TABLE When running a repair table on a corrupted or damaged table, will this guarantee replication to break?

Documentation says "in the event that a table on the master becomes damaged and you use REPAIR TABLE to repair it, you should first stop replication (if it is still running) before using REPAIR TABLE, then afterward compare the master's and slave's copies " ..

Questions

Is this a simple STOP SLAVE; on the replica?
If so, wouldn't the binary logs just send that back once slave has started again?

tia

I think this advice is more a safety suggestion than a requirement.  Further, if a table is corrupted/damaged such than it needs repair ran on it that means something that wasn't supposed to happen happened.  The slave very well may be okay and not need to go through the expensive table rebuild.

While it is true a slave will execute anything written to the binlogs after it was stopped once restarted, you can simply prevent certain statements from even being written to the binlog in the first place.

If you run

set sql_log_bin=0;

then anything you execute in that session thereafter will not be written to the binlog; will never be executed on the slave.

Edit

After repairing a table on the master you'll want to checksum it.  If you can afford to have your master table offline for a period beyond the repair you can just run

checksum table ;

If you need to get back in action quickly you should look to using pt-table-checksum from the Percona Toolkit.  This might also be something to consider over a straight checksum even if the table isn't being actively used.

Here's why.  pt-table-checksum will check your table in chunks.  You can read the details on exactly how it operates in the docs.  In short it will check parts of the table, for example, IDS 1-1000, 1001-2000, etc.  This is meant to server as an anti-lock braking system if you will for live tables.  A portion is checksumed then the lock on the table or rows being checked is released, allowing other live traffic waiting to make some progress in between chunks.

Further, even if extended locking is not a concern it gives you insight to what parts of the tables differ. Maybe it turns out you only need to synch up a small segment of rows instead of copying for gigabytes of data.

The percona toolkit also includes pt-table-sync to resolve such conflicts.


Busy table is not getting vacuumed We're using Postgres 9.2 on Windows to store low-frequency timeseries data:  we're inserting around 2000 rows per second every second 24 hours, 7 days a week with no downtime.  There is a DELETE that runs on the table every 10 minutes or so to keep the length of the table to a fixed number of days.  This ends up being a fairly stable 900 million rows.  (For those interested, SELECT, INSERT, DELETE are all performant).

As such the DELETE, whilst deleting rows is not freeing up disc space.  For that we need VACUUM to run.

I've queries the pg_stat_user_tables and VACUUM appears not to have ever run.

What I understand from various docs (http://www.postgresql.org/docs/9.2/static/routine-vacuuming.html):

we appear to have auto-vacuum on, and it is running on other tables.
auto-vacuum doesn't run FULL, and shouldn't require an exclusive lock on the table.

Does anyone have any thoughts why auto-vacuum is not running?  Is this purely because the table is continuously busy?

And it is worthwhile running VACUUM after every DELETE in this case (which runs every 10 minutes)?

Edit:

Query using the SQL from the SO link below:

-[ RECORD 2 ]---+---------------------------
schemaname      | stats
relname         | statistic_values_by_sec
last_vacuum     |
last_autovacuum |
n_tup           |    932,315,264
dead_tup        |    940,727,818
av_threshold    |    186,463,103
expect_av       | *

and raw output:

-[ RECORD 3 ]-----+---------------------------
relid             | 501908
schemaname        | stats
relname           | statistic_values_by_sec
seq_scan          | 12
seq_tup_read      | 4526762064
idx_scan          | 29643
idx_tup_fetch     | 2544206912
n_tup_ins         | 1573896877
n_tup_upd         | 0
n_tup_del         | 941176496
n_tup_hot_upd     | 0
n_live_tup        | 688858417
n_dead_tup        | 940727818
last_vacuum       |
last_autovacuum   |
last_analyze      |
last_autoanalyze  | 2014-08-09 01:36:21.703+01
vacuum_count      | 0
autovacuum_count  | 0
analyze_count     | 0
autoanalyze_count | 69

I would look into partitioning. If partitioned by day, you could just drop the entire partition once it gets too old. You may even no longer have to vacuum.

Also, overall performance might increase, since you're not inserting where you're deleting. You would just need to write the code to create new partitions and delete old ones.

This is exactly what partitioning is for.


Network connection from old application to SQL Server 2008 R2 lost once in a while I have put this question on serverfault.com as well, but since the problem concerns connecting to SQL Server, I will also put it here.

We are trying to migrate an Axapta 2.5 database from SQL Server 2000 to 2008 R2. We are testing performance etc. on the new server, but once in a while, these two events are thrown on the Axapta server:

  Object Server SandBOX: The database reported (session 11 (CSS)):
  [Microsoft][ODBC SQL Server Driver]Communication link failure. The SQL
  statement was: ""

and

  Object Server SandBOX: Fatal system condition - quitting for restart
  due to lost database connection.

after which all clients have to reconnect.

The Axapta 2.5 server is a virtual server (VMWare) running Windows 2003 SP2.
Info from vSphere:

VM Version: 7
CPU: 1 vCPU
Memory: 1024 MB
VMNetwork: VMXNet 3

The SQL Server is also a virtual server (VMWare) running Windows 2012 R2.
Info from vSphere:

VM Version: 8
CPU: 4 vCPU
Memory: 73728 MB
VMNetwork: VMXNet 3

There are no traces of an error in either the event log on the SQL Server or in the SQL Server Logs.

When searching the internet for a solution, I only found one with this exact problem, and it had no answer (how to troubleshoot sql 2008 dropping connections on sqlservercentral.com). I mostly see suggestions around the virtual network adapter or settings on the virtual server, for more or less similar problems.
I'm probably forgetting a lot of information, but I'm actually a DBA, so bear with me.

I know very little of virtual machines and network. Thanks for your help.
Could anyone point me in a direction with this issue?

Axapta 2.5 was not supported on SQL Server 2005 or later versions so you are out of your depth there. To support later version of the database engine you will need to update the axapta application itself. 2.5 predates SQL Server 2005 by years: http://everything.explained.today/Microsoft_Dynamics_AX/

Any problems you have with incorrect statements are most probably due to the axapta server getting confused over the database version, you could try to change compatability level to 8.0 but I would recommend that you contact an Axapta certified partner and migrate to newer version of the application


Is possible do an online snapshot backup , without archive logs at Oracle Database? Using Oracle Database Standard Edition 11r2 .

I'm looking a way to backup the database with zero downtime , using a snapshot of datafiles (LVM snapshots) and without need backup de archive logs.

All options found so far , we will need save fews archives logs.

My reason for this requirement is today our databases are put offline to copy our datafiles. There are procedures and documentation about backup and the restore . I want to minimize any change here at backup and restore process , all looking for minimize point of failures , human error which could be invalidate all backup.
So , I need to exclude the archive logs from the equation , do not want they involved.

I think something like :

flush all data from database to disk (checkpoint)
lock any write at the database datafiles (how?)
create the snapshot
release the database
mount the snapshot
copy all data of the snapshot
destroy the snapshot.

And if need to restore , just restore de data files and open the database.

Yes, you can do an online backup without archivelogs if you have a storage system or backup tool that can take a point in time snapshot that will be consistent across all your datafiles and control files.  It sounds like you have such a tool and are using it for cold backups.  If that is the case, your documentation will still need to change.  If there is a problem with the system and you need to restore the backup, starting up the restored database will be as though the power plug was pulled a the time of the backup, therefore crash recovery will likely be necessary.

Given that your documentation needs to change anyway and that you want zero downtime backups, you should probably re-think your resistance to archive logs.  It limits your recovery possibilities greatly.  For example, if due to a disk error you get a bad block in one of your datafiles, and you have older consistent copies of that datafile, you can recover it using archive logs.  With snapshots taken online you would probably end up doing lengthy extracts from multiple snapshots of the system (assuming you can tolerate the downtime) and would probably still loose data.


Store both geometry and geography or store one and calculate the other? I am just starting out designing my first GIS database, using PostGIS / PostgreSQL, and I have a question regarding geometry and geography, specifically whether it is more efficient to store geometry (because it supports more functions) and then project into geography when I need it, or whether I should store both geometry and the re-projected geography alongside?

To elaborate a little more, the database will be populated with pre-existing data that contains both British National Grid (EPSG:27700) (OSGR) and converted WGS84 (EPSG:4326) values, and there will be a regular update of that data from external sources. This would argue for two columns, because I don't need to do the calculation.

Going forward, I want to allow data to be edited through a "point and click" web-based map application, using OSM, or text input as either OSGR or via GPS coordinates. Search facilities would also allow either OSGR or lat/lon. Having two columns would mean that I would always have to perform at least one calculation for input (though not search) while having a single column would mean that I may sometimes require a calculation for entry, and again maybe one calculation for search.

I think I've argued myself into wanting both geometry and geography columns in my table (and probably projection columns for each, too, so that I can deal with Ireland at some future time), but is that sensible?

Thanks.

To elaborate a little more, the database will be populated with pre-existing data that contains both British National Grid (EPSG:27700) (OSGR) and converted WGS84 (EPSG:4326) values, and there will be a regular update of that data from external sources. This would argue for two columns, because I don't need to do the calculation.

You can always reproject with ST_Transform (though you may have minor rounding errors). I would convert and store in WGS84 explicitly and reproject to EPSG:27700 when you need those advantages. The geography features are really useful, and it makes life massively simpler to have them.

Not to mention, you can indexes to cover that conversion too if you frequently query on it.


Update column based on sum of rows from another table by join The command I'm trying to find should do this:

UPDATE table1
SET table1.a = x
WHERE table1.b = "something" AND table1.a IS NULL

The x is:

SELECT SUM(c) from table2
INNER JOIN table1
ON table2.id=table1.id
WHERE table1.b = "something" AND table1.a IS NULL
GROUP BY table2.id;

I've tried some variations of the command but with no success.
Would appreciate any help in write the command properly.
Thanks.

One approach:

CREATE TEMPORARY TABLE x
    SELECT id, SUM(c) AS TheSum
        FROM table2 ...;

UPDATE table1 JOIN x ON (table1.id = x.id)
    SET table1.a = x.TheSum
    WHERE table1.b = "something" AND table1.a IS NULL


Unexpected Implicit conversion in a procedure I have a procedure like this (simplified):

CREATE PROCEDURE test @userName VARCHAR(64)
SELECT *
FROM member M
INNER JOIN order O
ON M.MemberId=O.MemberId
WHERE M.Username = @userName

There is a non-clustered index on Username column of the Member table.

Plan cache shows an Implicit Conversion as such:

  Seek Keys[1]: Prefix: [MyDatabase].[dbo].[Member].Username = Scalar
  Operator(CONVERT_IMPLICIT(varchar(64),[@Username],0))

I was just wondering what might be causing this implicit conversion as both the parameter and the field data type "UserName" is varchar(64)?

SP is called from framework like this:

EXEC test @Username=N'webSite.com'

Thank you.

It was all down to collation of the column. It was different from the database's (and the table's) collation. Now changed the column's collation to database's and no more implicit conversion shows up. Have no idea about the internals and why it caused the problem.


How to enable MySQL remote access? I have a MySQL server and I want to enable remote access for a certain IP and localhost only. For some reason I couldn't manage to do it as it doesn't work every time I try. I typed the following command (of course user, password and db where different):

mysql> GRANT ALL ON foo.* TO bar@'192.168.0.1' IDENTIFIED BY 'PASSWORD';

I also disabled skip-networking.

What am I doing wrong then?

You need to change the bind-address parameter to the IP you wish to allow remote access to.  However, most people just comment out the bind-address parameter in the my.cnf file to allow access from anywhere and deal with who can access the DB server with user access control.  I would suggest the latter and not the former. Don't forget to restart mysql server after making changes to my.cnf for the changes to take effect.

Please refer Here for more information.

Additionally, The grant statement should have single quotes around the name as well as the host:

mysql> GRANT ALL ON foo.* TO 'bar'@'192.168.0.1' IDENTIFIED BY 'PASSWORD';

Also, don't forget to

Flush privileges;


'Non-unique column' error when creating a third table using SELECT INTO and FULL JOIN I'm attempting to create a new table from two existing tables, using the following code:

SELECT * INTO CustAndOrders from SalesLT.Customer
FULL JOIN
SalesLT.SalesOrderHeader
ON Customer.CustomerID=SalesOrderHeader.CustomerID;

However, I'm getting the error:

Column names in each table must be unique. Column name 'CustomerID' in table 'CustAndOrders' is specified more than once.

My intention is to create a third table with all the fields from each of the joined tables. Obviously 'CustomerID' appears twice, that's what I am joining on. And if I remove the INTO part and execute as a SELECT statement, it works as expected.

Just in case it isn't clear what the problem is the result of the SELECT has two columns called CustomerID. One from each table. A table can't have two identically named columns so the attempt to create a table with that format fails.

The solution is don't use *.

List out the columns you want explicitly and remove duplicate columns that you don't need or use column aliases to disambiguate any dupes that you do actually need.

Usually for a full outer join what you actually want is

SELECT COALESCE(Customer.CustomerID,SalesOrderHeader.CustomerID) as  CustomerID
       /*, other columns*/
INTO   CustAndOrders
FROM   SalesLT.Customer
       FULL JOIN SalesLT.SalesOrderHeader
              ON Customer.CustomerID = SalesOrderHeader.CustomerID;

but it seems dubious that you need a FULL OUTER JOIN here anyway. Probably you need a LEFT JOIN


Slony initial subscription is causing out-of-memory kernel panics. Why? Context:

We run Slony 2.0 with Postgres 8.4 on two CentOS 6 servers--one master, and one slave. Our database is about 30GB in size, which isn't unusual, but we do have a couple of tables that are more than 5GB each.

Recently, we needed to re-build our Slony cluster. I turned off Slony, restored identical database snapshots on the master and the slave, set up my slony.conf and slon_tools.conf, started the slons, ran slonik_init_cluster | slonik, then slonik_create_set 1 | slonik (we only have one replication set), and finally slonik_subscribe_set 1 2 | slonik. Everything looked good, and I was able to watch subscription progress in the logs.

Then the server stopped responding. I rebooted it, and saw "Kernel panic - not syncing: Out of memory and no killable processes" after it had killed everything it could.

What I've tried:

First I blew away the database completely, re ran initdb, and then restored the identical snapshots again. Same kernel panic. Then I blew it away, uninstalled Postgres and Slony, and reinstalled them. I double-checked all of our memory-based settings in postgresql.conf, and they are all at stock/recommended levels (i.e. shared_buffers is at 1/4 of RAM etc etc). I ran a VACUUM ANALYZE FULL on the database before initializing the Slony cluster. Same result every time: kernel panic, out of memory.

There's no chance of random/manual config changes having caused this: all of our Postgres and Slony configuration is managed by Puppet, and hasn't changed for months.

Question:

Why is this happening?

Our database has grown fairly linearly over the past few months (at the beginning of the year it was about 23GB, now it's 30), and every other time I have had to re-initialize the Slony cluster on these same servers, it has worked fine.

The problem turned out to be unrelated: in /etc/sysctl.conf, the system's shmmax value was set to an amount greater than the available RAM.

Setting it to 60% of RAM (our DB consultant's recommendation) solved the problem.

Why this issue didn't crop up before is a mystery to me.


IS-A relationship with only single sub entity Hi I'm trying to design a database for a dictionary. The words has several fields like meaning, an example, and maybe a picture. but the words that are 'verbs' has three more special fields that the rest does not. So is this a Is-A relation ship? but I tried this:
I made a table for words with all sharing fields and a type field, and a table for verbs. these two tables has a one to one relation. the verb table just has the special fields and the foreign key. so if a user wants to only search for a verb he/she needs to search from all words that has the "type == verb". is this a good idea?
Please help me how to design this, Thanks in advance.

I would not create another table for verbs because join operations on big tables (especially multiple ones in the same query) may be expensive. I would create one table, with the shared AND the non-shared fields, AND add another column "isverb", set it to 1 for verbs and 0 for non-verbs. You could also later on add "isadjective" etc.

In this case it just makes sense to do:

SELECT * FROM words where isverb = 1

rather than:

SELECT *
FROM words w
INNER JOIN verbs v ON w.type = v.id
WHERE w.type = 2

The application is a dictionary. So it's write once, read a zillion times and so I don't personally see a need for a complicated database design. You essentially just need very fast reads which you can achieve without joins to multiple tables


Initial Full Backup Failing Immediately This server was recently installed and configured.  I was in the process of creating some maintenance plans to backup the logs and DBs on a daily basis.  Regardless of my method to perform a full backup, it would fail immediately citing the generic error message

Error: 3041, Severity: 16, State: 1.

This is what I found in the SQL Error Logs:

I've never seen this before and wasn't able to find much during research.  I've verified that the associated service account has permissions to the backup folders and there is more than enough space.

Other details

SQL 12.0.4213.0
Windows Server 2012 R2 Standard
VMware virtual machine

I forgot to update this post with what ended up working for me.  I don't know why, but running SSMS as Administrator allowed the backup to complete as you'd expect.