texts: SQL Prove memory pressure - high Buffer Cache Hit Ratio, but low Page Life Expectancy

texts:
Our production server (2012, VM + SAN) has 32 GB of RAM, the database size is ~80GB. The application uses TempDB heavily - disk is hit ~100 MBps both reads & writes.  Seeing tons of SQL compilations/sec .. 95% of all batch requests are compilations.

Ideally would like to increase the RAM to 64GB or 128GB, but need to 'prove' to team that it's required.

Buffer Cache Hit Ratio (BCHR) is 99.9%, but Page Life Expectancy (PLE) is only ~400.
What's the explanation for this ?
I though PLE & BCHR had a linear relationship (i.e. they increase or decrease together)

On other VMs with larger databases and lot more RAM, both BCHR & PLE are high.

Current wait stats and perf counters


texts: Ignore empty rows while loading data into a table

texts:
I created a table (tbldemo) with 22 columns using a CREATE TABLE statement in MySQL. When trying to insert the data from a .csv file using a LOAD DATA INFILE statement, I get this error:

  Error Code: 1261. Row 11513 doesn't contain data for all columns

How can I ignore the rows whose entire row is null or empty?

This is what I used to load the data from .csv:

LOAD DATA INFILE 'D:/Singapore/rau_sales_order.csv' INTO TABLE tbldemo
 FIELDS TERMINATED BY ','
ENCLOSED BY '"'  LINES TERMINATED BY '\r\n'
 IGNORE 1 LINES;

I don't want to open the .csv file and clean the data from the file by filtering it for null. Instead, I want to do the filtering directly in the query. Is there any way to achieve this?


texts: Subscriber Node down in transactional replication

texts:
1) I have 2 node which node 1 is publisher and distributor in same server and subscriber in node 2. In case publisher is down for few hours and i need to bring up the subscriber for read and write by disabling replication, will the transaction and commands in node 1 distribution database will be rolled back or i will lose certain data?

2) I have node 1 is publisher and node 2 have distributor and subscriber, in case publisher is down for few hours and i need to bring up subscriber for read and write. What happen to transaction marked for replication in publisher but not has been sent to distributor, will it be rolled back or i will lose certain data?

Thank you


texts: Does MySQL InnoDB table perform faster when data is removed

texts:
I have a 5GB InnoDB table in MySQL. I updated several rows where a column had massive amounts of data to be blank, hoping to make the table smaller and perform quicker.

I then discovered that InnoDB doesn't reduce disk space when you remove data from it.

https://stackoverflow.com/questions/1270944/mysql-innodb-not-releasing-disk-space-after-deleting-data-rows-from-table?rq=1

However, there is less data to search through now, so I'd like to assume it will still perform better, especially when performing a LIKE query.

I tried to do some benchmark tests, but very unfortunately did not take accurate before measurements.

Will my InnoDB table perform faster now that I've removed data from the table, or does it still have to search through the same amount of disk and there will be no difference.


texts:
I tried to install oracle 12c in Oracle Linux 7.4 instance upon AWS ORACLE Linux 64 EC2 instance,
I could make the installation and connect to the database from the ec2 server without any issue however when I try connecting to the database from my desktop I get generic TNS Protocol adapter error.

Please find more information about my instance:
1. I have set following rule for my EC2 instance
instance rule

Please note,  I am not sure why i am getting every roles twice also I am not sure why type is 'Oracle -RDS', i always check the type as 'Custom TCP'

hostname value from my unix

ip-xx-x-x-xxx.ap-south-1.compute.internal

tnsnames.ora

ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
ip-xx-x-x-xxx.ap-south-1.compute.internal)(PORT = 1521)) (CONNECT_DATA
= (SERVER = DEDICATED) (SERVICE_NAME = orcl.ap-south-1.compute.internal) )
)

listener.ora

LISTENER =   (DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ip-xx-x-x-xxx.ap-south-1.compute.internal)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)   )

lsnrctl status

eLSNRCTL for Linux: Version 12.1.0.2.0 - Production on 30-JAN-2018 13:53:11'
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ip-xx-x-x-xxx.ap-south-1.compute.internal)(PORT=1521)))
STATUS of the LISTENER
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                30-JAN-2018 13:25:18
Uptime                    0 days 0 hr. 27 min. 52 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ip-10-0-0-253/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ip-xx-x-x-xxx.ap-south-1.compute.internal)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ip-xx-x-x-xxx.ap-south-1.compute.internal)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.1.0/dbhome_1/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "orcl.ap-south-1.compute.internal" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.ap-south-1.compute.internal" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdborcl.ap-south-1.compute.internal" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...

Value from v$instance table

  SQL> select host_name, instance_name from v$instance;

  HOST_NAME
  ---------------------------------------------------------------- INSTANCE_NAME
  ---------------- ip-xx-x-x-xxx.ap-south-1.compute.internal orcl

I am not sure why I cannot connect from outside of ec2  instance; Please help me out of this issue


texts: Upgrading from MySQL 5.1.37 to MySQL 5.1.73

texts:
We have a fairly advanced website that runs on a cloud system. We have one master DB server and 2 Slave DBs that read from the Master.

We're currently using 5.1.37 (I know...its old and from 2009), and we're getting some common slave/replication errors.

I've done my research and MYSQL have fixed the errors I'm getting in later releases. So I need to upgrade.

My concern is losing any configuration I have already set within the server. If I upgrade to 5.1.73 will I lose my current setup of everything? Master users/Slaves/data etc.?

Also, what would be the ssh command to perform this upgrade?

Thanks in advance for your help!


texts: Can I get OSQL if I install SQL Server Client Tools?

texts:
We have some legacy .bat scripts full of osql commands to existing SQL Server instances / dbs on our network.

We've tried running the .bat scripts from our app server (Win Server 2008) but get the error:
 "osql.exe' is not recognized as an internal or external command"

We are hoping we don't need to install a full-blown SQL Server to get access to osql.

I've heard you can get it from the Client Tool installation, and no license required as long as connecting to licensed sql servers. (which we would be).

Can anyone confirm?
Are "Client Tools" part of the installation discs?
Is this a viable solution?

Thanks for your help folks!


texts: Why would DBMS_METADATA.GET_DDL Fail when i have the SELECT_CATALOG_ROLE Role?

texts:
I am trying to get the DDL using METADATA_DBMS.GET_DDL for an object in a database, and I get the following error:

ORA-31603: object "MY_PACKAGE" of type PACKAGE not found in schema "S1"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 3149
ORA-06512: at "SYS.DBMS_METADATA", line 4787
ORA-06512: at line 1

This is how I am calling the METADATA_DBMS.GET_DDL:

SELECT DBMS_METADATA.GET_DDL (object_type, object_name, owner)
       FROM dba_objects
       WHERE OBJECT_ID = (select OBJECT_ID from dba_objects where object_name ='MY_PACKAGE' and object_type = 'PACKAGE' and owner = 'S1')

I wrote it with the subquery to make sure the object exists in the schema S1. I am certain S1.MY_PACKAGE exists, I can even look at it in TOAD Schema Browser.

I have the following roles granted:

select * from USER_ROLE_PRIVS

USERNAME  GRANTED_ROLE         ADMIN_OPTION  DEFAULT_ROLE  OS_GRANTED
MYUSER    CONNECT              NO              YES          NO
MYUSER    DBA                  NO              YES          NO
MYUSER    SELECT_CATALOG_ROLE  NO              YES          NO

I had DBA Role first, and since it was throwing that error I granted myself SELECT_CATALOG_ROLE.

I checked against another Database Instance where the query works and I have the same roles, what else do I need to make DBMS_METADATA.GET_DDL work?

Both databases are a clone from the same source database, and I later got the privileges granted.


texts: Fix line by line parameterization in a table

texts:
This table has a lot of columns but I need to fix "patrimonio".

It looks like this:

As you can see, there are a lot of different values. The correct way to use this table is with S-1234. But theres a lot of S1234,S 1234,X124 and etc.

I'm trying to update ( dealing with one type of error at a time ) all rows following a certain contidion using stuff.

But this query is returning  The subquery is returning more than 1 value....

begin transaction
            update frotas.Viaturas
            set patrimonio =
            (select stuff(patrimonio,2,0,'-')
             from   Frotas.Viaturas
             where  patrimonio not like 's-%'
             and    patrimonio not like 's %')
        where patrimonio=patrimonio
        rollback

This is because, is not updating row by row. How can I achieve this ( to update row by row )? I'm trying to think in something with dynamic sql but I can't think in a logic.

The Idea is to update each row with its own value, but with the stuff.


texts: Difference between clustered index seek and non clustered index seek

texts:
What is the difference between Clustered Index (CI) seek and Non Clustered Index (NCI) seek? Does one perform better than the other?

The reason I am asking this is because I have a table with 50 million rows and 150 columns. It has a column named ID defined as the clustered index. There is one more NCI on it with the same index key ID and seven include-d columns. It seems to me that the NC index is a duplicate here and can be safely dropped.

So I want some expert views/advice if it can be dropped safely or should keep it intact?


texts: Why the table gets deleted by the server in mysql?

texts:
I have a table in mysql. I have one java application which will keep inserting the data into this table. So the size of the table grows drastically and at one point it gets deleted automatically by the mysql server. I am not sure about the reason yet. So please if anybody has come across this issue then please share the way to handle this, so that I can have my data preserved.

UPDATE

This is what I found in the sql log of the server.

> 2013-11-27T15:07:39.285156Z    9 Query    SELECT s.Sql_Id
>       ,s.Child_Number
>       ,s.Executions
>       ,s.Last_Active_Time
>       ,s.User_Io_Wait_Time
>       ,s.Object_Status
>       ,s.Plan_Hash_Value FROM   V$sql s WHERE  s.Sql_Text LIKE 'SELECT /*+ FIRST_ROWS XX */ T1.SYNC_TIMESTMP, T1.CUSTNO, T1.XAFID_ID_IFX,
> T1.XAFID_CLASSIFIED%' 2013-11-27T15:07:39.286132Z    9 Query  SHOW
> WARNINGS 2013-11-27T15:08:08.791015Z    9 Query   select
> @@session.tx_read_only
> 2013-11-27T15:08:08.791992Z    9 Query    drop table devicedata 2013-11-27T15:08:08.926757Z    9 Query    SHOW WARNINGS
> 2013-11-27T15:08:08.936523Z    9 Query    select @@session.tx_read_only

Thanks


texts: Correlated Subquery Versus Non-Correlated Subquery

texts:
There are three tables that may be used by a business to keep track of the absent days of its employees. It's from a collection of SQL puzzles by Joe Celko. The reader is tasked with concocting a DELETE statement to remove those employees who have accrued 40 or more absent days. And for that purpose we use a subquery. The one proposed by the author is this:

DELETE FROM Personnel
WHERE emp_id = (SELECT A1.emp_id
FROM Absenteeism AS A1
WHERE A1.emp_id = Personnel.emp_id
GROUP BY A1.emp_id
HAVING SUM(severity_points) >= 40);

And my question is, why go for a correlated one? Isn't it just more efficient to run something like one below?

DELETE FROM Personnel
WHERE emp_id =
(SELECT emp_id FROM Absenteeism
GROUP BY emp_id
HAVING SUM(severity_points) >= 40)

Thank you!


texts: SQL Server 2000 Replication, Performance, and Query Priorities

texts:
I'm a developer and wanting to confirm the following:

Does anyone here have experience with SQL Server Replication (transactional) on 2000? We are trying to convince the DBAs to allow this in a production environment so that we can concurrently run "tests" -- but the DBAs are telling us that Replication in SQL Server 2000 doesn't work and/or that it has a severe performance impact on the source server.

I tried googling some information on this but haven't found any such information.

Also, do you know if it is true/not true that queries from Stored Procedures or queries called from a Linked Servers will take precedence over other queries (e.g. dynamic queries from a connecting application). I didn't think this was possible and have found no reference to this via my google searches... but the DBA claims this is true for SQL Server 2000, and as a result, causes notable performance slowdown from users of the web app connecting to the database.

Thanks!


texts: Impact of Quotes in T-SQL for Column Name

texts:
Is there a difference between the two T-SQL commands below ?

Column name with quotes

SELECT SUM(DATALENGTH('ColumnName')) / 1048576.0 AS ColumnName
FROM DatabaseName.Schema.Tablename

Column name without quotes

SELECT SUM(DATALENGTH(ColumnName)) / 1048576.0 AS ColumnName
FROM DatabaseName.schema.Tablename


texts: What are some important MySQL status variables to keep an eye on?

texts:
I'm a fairly new DBA with a programming background. As such, I like to build tools to manage tasks to help me familiarize myself with the intricacies of a technology.

In this case, I am managing multiple MySQL servers and would like to build a UI to view the health of the servers.

What are the important status variables to watch out for that throw a red flag that something isn't right on a long-running server (>48 hours)?

An example of what I'm looking for is qcache_lowmem_prunes becoming larger could indicate a misconfigured query_cache


texts:
I'm working on a project and I would need help with proper design a database schema. This is the application where users can add appliances in their home. For example, television, washing machine, dishwasher line, fridge, freezer. Each one of these appliances have different parameters that I need to store in a database. I do not think they are effective represent each category of appliances separately, but on the other hand, I do not know how to include them in a single table.

I only have five categories, so five tables it was not a problem, but if they got a hundred, so I do not come quite inefficient. Do you have someone experience something similar or know to help me in my case?

Thank you and sorry for my English.


texts: Better way of handling incorrect date format in a column with "char(10)" data type / TRY_CONVERT equivalent in PLSQL

texts:
I have a source table with below structure:
create table customer_info
(customer_num    number,
 birth_date      char(10))

Unfortunately the birth_date column data type is char(10) instead of date. Some example data for this table would be like below:
customer_num    |  birth_date
--------------------------------
  1             |  2001/01/01
  1             |  2010/01/01
  1             |  2021/01/01
  1             |  12hhhhuu6   --> Incorrect date
  1             |  2001/01/01
  1             |  2001/23/01  --> Incorrect date

what I've done is writing a function to evaluate every single record and return it's correct format but as you know , using a function for every single record is not a good idea and it somehow kills performance. I was wondering if you could suggest a better way for this.
create or replace function new_to_date_en(d varchar2) return DATE
is
  v_date date;
begin
  select to_date(d,'yyyy/mm/dd' ) into v_date from dual;
  return to_date(d,'yyyy/mm/dd');
  exception when others then return to_dateto_date('2021/03/07', 'yyyy/mm/dd');
end;

Using the function:
 select customer_num,
        new_to_date_en(birth_date)
 from customer_info;

There is a way in T-SQL COALESCE(TRY_CONVERT(date, @date, 111), '2012-01-01'). Is there a similar way in oracle plsql?
Thanks in advance


texts: Combining two similar queries into one (PostgreSQL)

texts:
I have 2 queries like this:

SELECT c.id FROM component c, base_vee bv
    WHERE
        c.id IN (SELECT component_id FROM registry_row WHERE registry_id = 199)
        AND
        c.id = bv.component_id
        AND
        (bv.affidavit IS NULL OR bv.affidavit = '')

SELECT c.id FROM component c, base_vee bv
    WHERE
        c.id IN (SELECT component_id FROM registry_row WHERE registry_id = 199)
        AND
        c.id = bv.component_id
        AND
        (bv.affidavit IS NOT NULL AND bv.affidavit != '')

What I need to get is a list of component IDs with False if bv.affidavit is emptyish or True if it's not emptyish.

Right now I'm obviously getting two lists of component IDs for each case.

It works, but one query like described above would be nicer.


texts:
I had a 3 shard set up, and ran out of capacity, so I added 3 more shards. (Each shard is a replica set).  But the data isn't spread across the cluster evenly.  I have my chunkSize set to the standard 64mb:

mongos> db.settings.find( { _id:"chunksize" } )
{ "_id" : "chunksize", "value" : 64 }

I thought this meant that when a chunk hits 64mb, it splits into two equal chunks both of size 32mb.  That's what is demonstrated here.  Is that not correct?

Here is my sharding distribution:

mongos> db.accounts.getShardDistribution()
Shard rs_0 at rs_0/mongo_rs_0_member_1:27018,mongo_rs_0_member_2:27019,mongo_rs_0_member_3:27020
 data : 137.62GiB docs : 41991598 chunks : 1882
 estimated data per chunk : 74.88MiB
 estimated docs per chunk : 22312

Shard rs_1 at rs_1/mongo_rs_1_member_1:27018,mongo_rs_1_member_2:27019,mongo_rs_1_member_3:27020
 data : 135.2GiB docs : 41159069 chunks : 1882
 estimated data per chunk : 73.56MiB
 estimated docs per chunk : 21869

Shard rs_2 at rs_2/mongo_rs_2_member_1:27018,mongo_rs_2_member_2:27019,mongo_rs_2_member_3:27020
 data : 219.92GiB docs : 69739096 chunks : 1882
 estimated data per chunk : 119.66MiB
 estimated docs per chunk : 37055

Shard rs_3 at rs_3/mongo_rs_3_member_1:27018,mongo_rs_3_member_2:27019,mongo_rs_3_member_3:27020
 data : 101.52GiB docs : 30650628 chunks : 1882
 estimated data per chunk : 55.23MiB
 estimated docs per chunk : 16286

Shard rs_4 at rs_4/mongo_rs_4_member_1:27018,mongo_rs_4_member_2:27019,mongo_rs_4_member_3:27020
 data : 103.38GiB docs : 31071379 chunks : 1883
 estimated data per chunk : 56.22MiB
 estimated docs per chunk : 16500

Shard rs_5 at rs_5/mongo_rs_5_member_1:27018,mongo_rs_5_member_2:27019,mongo_rs_5_member_3:27020
 data : 101.1GiB docs : 30516395 chunks : 1881
 estimated data per chunk : 55.04MiB
 estimated docs per chunk : 16223

Totals
 data : 798.77GiB docs : 245128165 chunks : 11292
 Shard rs_0 contains 17.23% data, 17.13% docs in cluster, avg obj size on shard : 3KiB
 Shard rs_1 contains 16.92% data, 16.79% docs in cluster, avg obj size on shard : 3KiB
 Shard rs_2 contains 27.53% data, 28.45% docs in cluster, avg obj size on shard : 3KiB
 Shard rs_3 contains 12.7% data, 12.5% docs in cluster, avg obj size on shard : 3KiB
 Shard rs_4 contains 12.94% data, 12.67% docs in cluster, avg obj size on shard : 3KiB
 Shard rs_5 contains 12.65% data, 12.44% docs in cluster, avg obj size on shard : 3KiB

What's up with this? How can the first 3 shards/replica sets have an average size greater than 64mb when that is set to be chunkSize? Rs_2 is 119mb!  Rs_2 has 27.53% of the data when it should have 16.6%.

I have very high cardinality in my shardkey, and it is not in a monotonically increasing one.

What should I do here? I can manually find chunks that are large and split them, but that is a pain.  Do I lower my chunkSize? Is there some service/call I need to run to do this automatically?


texts: Unable to change servername after VM Clone

texts:
For reasons out of my control I must find a solution to this issue. Simply reinstalling the instance is not an option. The server name that appears as server_id 0 in sys.servers is still showing the old servername.

I'm getting an error when running the below command:

sp_dropserver 'OLD_INSTANCE'
GO
sp_addserver 'NEW_INSTANCE', Local
GO

Error message:

  Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 67
  There are still remote logins or linked logins for the server 'OLD_INSTANCE'.
  Msg 15028, Level 16, State 1, Procedure sp_addserver, Line 87
  The server 'NEW_INSTANCE' already exists.

The strangest thing is, the remote login is a 'null' login.

exec sp_dropremotelogin @remoteserver = 'OLD_INSTANCE'
go

Error message:

  Msg 15185, Level 16, State 1, Procedure sp_dropremotelogin, Line 70
  There is no remote user '(null)' mapped to local user '(null)' from the remote server 'OLD_INSTANCE'.

No logins exist for the old instance.

sp_helpremotelogin 'OLD_INSTANCE'

  Msg 15201, Level 16, State 1, Procedure sp_helpremotelogin, Line 37
  There are no remote logins for the remote server 'OLD_INSTANCE'.

How do I rename this instance if I can't drop the non-existing login? Is there a way to flush the logins?


texts: Formatting Tabular mysql results in mail on unix

texts:
I am trying to email mysql query results through mail command in unix as below.

mysql -uroot -p*** -e "select query" --table|mail -s "test" abc@example.com

but format is little bit creepy kind of below.

+--------------+-------------+
| PARTNER_NAME | ORDER_COUNT |
+--------------+-------------+
| Legacy       |           0 |
| NextJump     |           0 |
| Provide      |           0 |
+--------------+-------------+

Can you help me with formatting the output if you have any idea


texts: Service Broker locks table even when committed

texts:
I hope someone can explain some behavior I've got within the Service Broker, which is the first time I've used it so it's probably my lack of understanding.

I've got a complex set of stored procedures that have to run to publish a data model.  That's all working great, and is kicked off my sending a message to the broker to start the process.  However, I noticed when it was running that it'd lock a particular table, even though the update to it was very small.

So I set about seeing whether it was the broker, or just the part that's updating that table. I did this by stripping it all down to it's most basic form, and popping a WAITFOR in the stored procedure (UpdatePublishRunStatus) so I could delay completion of the procedure :

BEGIN TRANSACTION

UPDATE
    [xxx].[PublishRuns]
SET
    [Status] = @Status,
    [Message] = @Message,
    LastUpdated = getdate()
WHERE
    Id = @RunId

COMMIT TRANSACTION;

waitfor delay '00:02'

I then kick this off as follows:

declare @token uniqueidentifier

declare @h uniqueidentifier
 , @xmlBody xml
    , @trancount int;
set nocount on;

set @xmlBody = CONVERT(XML,'<procedure><name>[xxx].[UpdatePublishRunStatus]</name><parameters><parameter Name="@RunId" BaseType="int" Precision="10" Scale="0" MaxLength="4">46</parameter><parameter Name="@Status" BaseType="int" Precision="10" Scale="0" MaxLength="4">20</parameter><parameter Name="@Message" BaseType="varchar" Precision="0" Scale="0" MaxLength="8">All good</parameter></parameters></procedure>')

begin dialog conversation @h
    from service [AsyncExecService]
    to service N'AsyncExecService', 'current database'
    with encryption = off;
select @token = [conversation_id]
    from sys.conversation_endpoints
    where [conversation_handle] = @h;

send on conversation @h (@xmlBody);

PRINT cast(@xmlBody as nvarchar(4000))

This will run fine.  However, while it's running - if I execute this from SSMS :

SELECT TOP (1000) *
  FROM [aaa].[xxx].[PublishRuns]

It locks up for the 2 minutes, until the service broker has completed the message.

Sticking NOLOCK hints obviously sorts out the read - but I don't understand why it's locking in the first place, particularly as I've committed the UPDATE before the WAITFOR kicks in.  The update takes milliseconds.

So my question is - why is it locking in this scenario? It's definitely the broker causing it, as running the same procedure in a query window, then running the select in another, does not exhibit the same behavior. This is what I'd expect, but there's obviously something I don't understand about the broker that's causing this.


texts: SQL log file 2TB is not enough, now what?

texts:
This is a hypothetical question, that flows from the answer by sepupic where they explain that 2TB is the physical limit for SQL log file.

  if you need it more than 2Tb you add the second log file.

I have long held the belief that multiple log files are bad as outlined in the post Multiple Transaction Log files and performance impact

I just can't imagine a scenario where having 2TB of log files even occurs, but if it does, and for some reason more frequent log backups will not cure it (multiple scenarios implied), what do you do?

Do you add a second log file, or is there something else?


texts: Full name as a Primary Key (PK) is my preferred choice; is there another way?

texts:
I'm new to DB management and I'm trying to create an ER Schema Diagram to best understand how to build my database for a project.

My project is to list every speaker (i.e. an individual person) that has spoken at specific events.

However, a name is not unique, so how can I make it unique?

Of course, I could just make an AUTO-INCREMENT ID as the PK but that would almost certainly create duplicates...

Any ideas on how I should approach this?

Ideally, I want the speaker's name to be 100% unique because often these speakers speak at multiple events....

All help very much appreciated!


texts: Is a millions-row query fetched in the SQL Studio client slower than other applications?

texts:
I'm not sure if want I'm thinking makes sense. I have a SQL Server database table with 30 million rows.

If I do a select A, B, C, D from huge_table --- (about 30 million--- total 4 GB footprint) --- does it take additional time for the SQL Studio Client to actual display all 30 million of these than it would an application to fetch (and process) the exact same query? (with no display requirements).

I mean -- just to fetch two columns --- a datetime and an int -- from this 30 million record table that has 8 columns total  --- well it takes 6 minutes to pull up all the records in SQL Studio. That's with no additional joins, calculations, or even every field.

Granted, it probably depends on my local client hardware (decent enough) -- and also the database is in Europe, and I'm in the United States -- though in practice I've found that maybe makes a query 1.5x slower at most.

I suppose I'm used to dealing with smaller datasets (like 2 million).

Is this simply an inherent lag in SQL studio, or does it suggest a reasonable fetch time for a similar hardware spec application?

I'm wondering how to 'speed up' this query, which by design, is a full scan. I can chunk it into smaller pieces, but the total time would be the same.

Should I aggregate or pre-calculate the 30 million? To be honest, that will not save time since with all the necessary dimensions, the 30 million would condense to maybe 25 million plus aggregate processing time - it would take longer.

Indices wouldn't help with full table fetches. Right? You're saying return these fields from the full table.

What's a reasonable time to fetch 30 million records? Select A,B,C from big_table?


texts: SQL Replication With Always On Availability Group

texts:
We have an always-on SQL 2012 availability group with two replica servers and an AG listener.
Doing merge replication via anonymous subscriptions through IIS between SQL and multiple remote clients running SQL Express 2012.  We have a separate SQL server that serves as a full-time distributor.

Replicas: SQL01(Active) and SQL02
Listener: AG-SQL
Distributor: SQLDST

Per the MSDN checklist, there is a publisher redirect set up and verified on the distributor, for SQL01 with AG-SQL as the alternate.

We can see the publication on both SQL01 and SQL02.

Replication works if we are not failed over, and the subscribers have SQL01 set as the publisher.

If we leave the subscribers set to SQL01 as the publisher while failed over, replication fails, indicating SQL01 is read-only.

If we try to point the publishers to SQL02 while failed over, the subscription will not create, indicating the publication does not exist.

If we try to point the publishers to the AG-SQL listener, the subscription will not create, indicating the AG is not available, not a publisher, or access denied.

If we fail back over, making SQL01 the primary again, replication starts working again.

All of this is conducted by our C# application.  The subscriptions are created and replication is triggered programmatically.

And now the questions:

Can merge replication work against an AG group?  (I presume yes, given the articles out there on configuring it).

Will merge replication fail over with the AG and work seamlessly, or do the subscriptions have to be reinitialized/recreated?

When not failed over, on the subscription, what should the publisher be?   SQL01, AG-SQL, or something different

Do we need a different publisher when failed over?

Are there any special subscriber settings we need to apply in order to work against an AG?

Thanks


texts: How to Get Backups & Restores Start and Finish Times?

texts: I backup my databases from prod environment to file server. After taking my backups, i restore them to my report server. And i want to know that if i can get information about when backup and restore has started and finished or not. How can i query how long it takes to complete its each process?


texts: Why is my TRY CATCH block in SQL Server immediately exiting on error completely ignoring the remaining code in the block, including my error handling?

texts:
Below is a snippet of my TRY/CATCH logic within a stored procedure with error handling (I did not include all of the DECLARE statements). This is being ran within a stored procedure, however I am simply testing this in a session via SSMS (would this affect the behavior?). I did stumble across this thread, but do I have to go to these lengths to simply capture errors?

BEGIN TRY
DECLARE @sql varchar(1000);
SET @sql = 'select 1/0';
EXEC(@sql);
-- On Error the remaining TRY/CATCH below is compeltely ignored I discovered.
DECLARE @error int;
SET @error = @@error;
IF @error > 0
 BEGIN
     SET @raisemessage = 'SQL Backup Error: ' + cast(@rc as varchar(10));
         RAISERROR (@raisemessage, 16,1);
 END
END TRY

BEGIN CATCH
    SET @errormessage = ERROR_MESSAGE();
    IF @errormessage is null
        SET @errormessage = ''
        SET @errorstring = @errorstring + 'Database: ' + @name + '  Error: ' + @errormessage + char(10) + char(10);
END CATCH

Can anyone explain the shortcomings of this TRY/CATCH block? Does this not work in T-SQL?


texts:
I want to create a table which has 2 columns that would only allow one of them to have value in the same row.

I tried with CHECK constraint but it not working correctly

Ex: SQL Fiddle schema example

thanks in advance


texts: Which version of Oracle supports Streams

texts:
I am using Oracle 10g Express Edition. I want to enable the streams feature in my current edition. However, when I searched on the net I couldn't find any information regarding streams setup on Oracle XE

Can anyone tell me which version of Oracle supports streams? Is it only the Enterprise Edition that supports it? If yes how can I add streams support to my express  or standard editions


texts:
I use SQLite for websites I create and manage for my clients (I am a web developer).  They are very small and straightforward databases.

This is now happened twice, so it is pretty clear that it wasn't a fluke.  This last one was a clear set of steps that then corrupted the database:

Open the database in SQLite3 by linux command line
Make a mistake in the data
Ask Git to restore the released version of the file (this is reaching out to GitHub for the copy)
Open the restored version, and file is corrupt

I then struggle to get it back in order, nothing I do in git fixes it. I can luckily grab it from the live website (no updates there) and all is well.

My two questions:

Is there something I can do differently while getting the file from the git repository to revert, it is not clear to me why git would corrupt the file
Is there something I can do that is easier to restore the file other than get the copy from the live website


texts: Contiunous rise of mysqld memory consumption

texts:
Mysqld memory consumption rises forever and never seems to be freed. It starts out about 6GB but gradually rises to around 10GB in a few weeks, and of the 10GB only 4GB and 50MB is being used by the buffer pool and the dictionary respectively when checking innodb status.

This is with MySQL 5.6.16 on a server with 12GB memory. A few of the tables are partioned and there are roughly 8000 idb files. Also one table is created each day.

I have tried to FLUSH TABLES with no success. The tables are closed but the memory does not get freed at all. In fact, more memory gets consumed.

Why is the memory being consumed? And are there any known issues with memory not being freed when using partioned tables?

my.cnf

query_cache_size = 512M
query_cache_limit = 16M
max_allowed_packet = 16M
table_open_cache = 1024
sort_buffer_size = 2M
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 2MB
myisam_sort_buffer_size = 1M
max_connections = 1024
thread_cache = 1024
tmp_table_size = 16M
max_heap_table_size = 16M
wait_timeout = 20
join_buffer_size = 256KB
thread_cache_size = 50
table_definition_cache = 400
key_buffer_size = 256M


texts: Get all possible parent ids only if has parent bigger than 0

texts:
Right now If I call get_parent_id(4) I will get all id parents. This means 3,2,1. Is not hard to guess that 1 has parent 0.

This is my problem. I want to output all ids unless the parent is 0. To do this I have one more query (check_for_parent_0) in this function.

Please suggest any solution so I can have only one query and not two in the next function. Any other advices for a better performance is welcomed.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `get_parent_id`(`id` INT) RETURNS varchar(1024) CHARSET utf8
    DETERMINISTIC
BEGIN
    DECLARE return_string VARCHAR(1024);
    DECLARE return_string_separator CHAR(1);
    DECLARE parent_id INT;
    DECLARE check_for_parent_0 INT;

    SET return_string = '';
    SET return_string_separator = '';
    SET parent_id = id;
    SET check_for_parent_0 = '';

    WHILE parent_id > 0 DO

        SET parent_id = (
            SELECT
                forum_parent_id
            FROM
                forums
            WHERE
                forum_id = parent_id
        );

        SET check_for_parent_0 = (
            SELECT forum_parent_id FROM forums WHERE forum_id = parent_id AND forum_parent_id != 0
        );

        IF (parent_id > 0 AND check_for_parent_0 > 0) THEN
            SET return_string = CONCAT(return_string, return_string_separator, parent_id);
            SET return_string_separator = ',';
        END IF;

    END WHILE;

    RETURN return_string;
END$$


texts: Determine if query comes after cascade update

texts:
We have a situation that in a trigger we are calling one procedure to update/insert values in another table. Now, the new requirement asked that we have to cascade update foreign key on that table (SITE column). That cascade update triggers calls to that procedure in a loop, but we don't need it that way. Still, we need to check if SITE column was changed with a normal/single update. That said we could check if only one record exists in inserted table then we can exec the proc otherwise not.

Given all of the above, my question is as follows: Can we determine (in a trigger) if an update to a column value was initiated from a CASCADE update from a foreign key?

Thanks,
Dejan


texts: Use specific array column in the where clause

texts:
Having a function that returns an array and in a query we use the function to return results such as:

{"TUNDUMA GOING (BORDER)","ARRIVAL DATE",09/01/2016,22,"AT CHECKPOINT"}
{"TUNDUMA RETURN (BORDER)","DEPARTURE DATE",29/01/2016,2,"ON THE ROAD"}

How can we add a WHERE clause that will filter by the 5th column of the array searching for the ON THE ROAD values only?

Is there a way to reference array[4]?

Tried with

where trip_status(tlid) <@ array['AT CHECKPOINT']::varchar

with trip_status() being the function that returns the array but am getting the following error:

Query failed: ERROR: operator does not exist: character varying[] <@ character varying
LINE 6: ...ere trip_closed(tlid)=false and trip_status(tlid) <@ array[ ... ^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Does something like where trip_status(tlid)[4] = 'AT CHECKPOINT' exist?


texts: Oracle Enterprise Manager read-only access for non-administrative users

texts: Can access to OEM be restricted to the point where you can allow a non-admin user access to it but they can't modify any objects but can still view performance data, alerts, sessions, etc? I am a former DBA now developer and I don't have an environment where I can go verify this myself.


texts: Delete data from huge InnoDB table from MySQL Master and Slaves

texts:
We have some huge tables in MySQL database, We have already archived/deleted data older than 2012 in 2013, Now we need to archive/delete data older than 2013 means we need to archive data for year 2012 without application downtime.

Tables  Size in GB      "TABLE_ROWS"    "TABLE_ROWS BEFORE 2012"
RTesAll 923.65          1982098430       611992998
RTest   32.1            205527090
RAdT    6.97            25324446
RAdv    4.37            28260973

So I need to delete 611992998 records from biggets table.

We have One MySQL Master and 4 MySQL Slaves, We need to delete data from all the servers, What I am thinking is i will delete data in chunks from master so that slaves also didn't lag too much.For that i have created a procedure here is the procedure, I have not yet tested it

DROP PROCEDURE IF EXISTS PurgeOlderData;
DELIMITER $$
CREATE PROCEDURE `PurgeOlderData`(In StartDate DATETIME ,In EndDate DATETIME,In NoOfRecordsToDelete BIGINT,In TableName CHAR(50))
BEGIN
    SET @delete_counter             = 0;
    SET @table_name             = TableName;
    SET @number_of_records_to_delete    = NoOfRecordsToDelete;
    SET @start_date             = StartDate;
    SET @end_date               = EndDate;

    WHILE @delete_counter < @number_of_records_to_delete DO
        SET @varSQL = CONCAT('DELETE FROM ', @table_name,' WHERE recordDate BETWEEN \'',@start_date ,'\' AND \'', @end_date ,'\' LIMIT 5000;');
        PREPARE stmt FROM @varSQL;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        /*SELECT @varSQL;*/
        SET @delete_counter = @delete_counter + 5000;
    END WHILE;
END $$
DELIMITER ;

I have used variable @number_of_records_to_delete in procedure because,I can pass no of records which i target to delete.

Sample call statement

CALL PurgeOlderData('2012-01-01 00:00:00','2012-01-05 00:00:00',100000,'RTestAll');

I can increase the value of @number_of_records_to_delete accordingly.

Why DELETE with LIMIT 5000 : I will first test how much time DELETE with LIMIT 5000 takes if it is Ok (Means executes in 1-3 seconds and Slaves are also Ok), I can increase DELETE LIMIT to 10000, If it is also Ok , I can increase some more.

Table Structure of huge table

CREATE TABLE `RTesAll` (
  `recordDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `networkId` bigint(11) NOT NULL DEFAULT '0',
  `publisherId` bigint(11) NOT NULL DEFAULT '0',
  `feedId` bigint(11) NOT NULL DEFAULT '0',
  `subPublisherId` bigint(11) NOT NULL DEFAULT '0',
  `subId` varchar(100) NOT NULL DEFAULT '',
  `searches` bigint(20) DEFAULT NULL,
  `matches` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`recordDate`,`networkId`,`publisherId`,`feedId`,`subPublisherId`,`subId`),
  KEY `K_networkId` (`networkId`),
  KEY `K_publisherId` (`publisherId`),
  KEY `K_feedId` (`feedId`),
  KEY `K_subPublisherId` (`subPublisherId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

So my questions are

Does the approach i am going to opt will work and is it optimized way to go. Because we don't want any issues in servers (Like slave lag) due to deletions.
What would be the best approach to do it without issues and downtime.
How can i use pt-archiver for doing this.


texts: What or Who is taking SQL Server backups

texts:
I have inherited a SQL Server 2008 R2 (SP3) Database. There are backups being taken daily at seemingly random times. The backups are not being saved locally on the server. I see the backup information in msdb..backupset table. What is taking these backups and where is it storing these backups? I see that VSS writer is the program name, I can disable the Service but I would like to get more information of what was done before I disable this service.

There are no agent jobs
There don't seem to be any Task Scheduler Jobs
There is no external program installed

I have created a trace and I see the following.

SQL:
BACKUP DATABASE [master] TO VIRTUAL_DEVICE='{8305D2E8-56C5-41E5-9F54-    17DDEE1A6991}7' WITH SNAPSHOT,BUFFERCOUNT=1,BLOCKSIZE=1024,COPY_ONLY

HostName is same as Server

Application Name: Microsoft SQL Server VSS Writer

LoginName: NT AUTHORITY\SYSTEM


texts:
I want to disable the caching for my session in oracle 11g to see how long a query takes.

Is this possible or is there a better way for meassuring the time a query takes to run?


texts: Query performance drastically depends on the database in use

texts:
I'm on SQL-Server 12.0.5203.0
There is a query covering multiple databases. I think It is not useful to post the actual query (as it is rather complex and confidential, just think of a query like
SELECT t1.Column1
      ,t2.Column2
      ,t3.Column3
FROM db1.schema.TableName1 t1 with (nolock)
JOIN db1.schema.TableName2 t2 with (nolock) ON SomeCriteria
JOIN db2.schema.TableName3 t3 with (nolock) ON SomeCriteria
JOIN db3.schema.TableName4 t4 with (nolock) ON SomeCriteria
WHERE SomeCriteria
  AND t4.ColumnA = (SELECT DISTINCT ColumnX
                      FROM db2.schema.TableName5 with (nolock)
                      WHERE ColumnY = 2902)

Please: This is not my query... Do not discuss the usage of WITH(NOLOCK) or GROUP BY against DISTINCT, thx :-D
Users reported time-outs due to run durations of more than a minute. I could not reproduce this, as exactly the same query in exactly the same environment brought back exactly the same result in less than a second.
Then - by setting all possible configurations to the same values - I encountered something strange: The difference depends on the database in use.
This is reproducable: If there is USE master; it is 1 second, with USE db1; (or any other) it is horribly slow.
Some general observations

All used tables are fully qualified with database.schema.table and aliased
All tables are called with WITH(NOLOCK)
The profiler for the fast run shows CPU(30), Reads(20000), Duration(30)
slow (same SSMS, just two windows): CPU(11000), Reads(13M!!!), Duration(12000)
The execution plans are extremely different

slow: Starts with an index seek returning 2.6M rows
Combines this with another index seek pushing this to 27M rows (estimnated 45!)
Filters this down to 1626 rows, which is the count of the final result
fast: starting with tiny sets of some 100 rows, Never more than 8000 rows

My questions

What is going on here? Why is the database, where I'm coming from, so important?

Lessons learned and a solution
What I did not know: Query plans are stored with each database separately, hence the context database can be very important. I cannot really grasp the advantage of this concept... Why not better store the plans in a central place, one per action? But this is a different question...
Using a query against sys.dm_exec_cached_plans and dm_exec-query_plan() with query_plan.exist('//*:StmtSimple[contains(@*:StatementText[1],"Some specific part of the query")]')=1 I found a couple of stored plans. After removal everything was fast and fine.
But the next day the bad behavior was back.
The solution: After a thorough look into the best plan I re-organised all JOINs to this order of execution and use OPTION(FORCE ORDER). This seems to solve the issue.


texts: How to export a table on postgresql command line

texts:
Anyone can do tell me how to do that?

(I know maybe is a dumb question but I'm trying to learn how to use command line of postgresql)

~$ psql mybase
psql (9.1.3)
Type "help" for help.

and then

mybase=# \dt

            List of relations
 Schema |        Name         | Type  |  Owner
--------+---------------------+-------+----------
 public | xxxxxx              | table | postgres
 public | xxxxxxx             | table | postgres
 public | xxxxxxxxxxx         | table | postgres
 public | xxxxxxxx            | table | postgres
 public | xxxxxxxxx           | table | postgres
 public | xxxxxxxxx           | table | postgres
 public | xxxxxxxxxxxxxxxx    | table | postgres
 public | xxxxxxxxxxxx        | table | postgres
 public | xxxxxxxxxxxxxxxx    | table | postgres
 public | xxxxxxxxxx          | table | postgres
 public | xxxxxxxxx           | table | postgres
 public | xxxxxxxxxxxxxxxx    | table | postgres
 public | xxxxxxxx            | table | postgres

only want one table to then do a restore =D thanks


texts: How to `vacuumlo` an RDS PostgreSQL database?

texts:
As you may have noticed, as a fully managed data base as a service product, AWS Relational Data base Service (RDS) restricts the execution of userland commands.

I was staging a production database, and got my pg_largeobject table bloated to 40% of the whole persistency virtual device capacity.

How can I run vacuumlo (beautifully well explained in other DBA.SE question here) on a RDS instance running PostgreSQL database?


texts:
I have a tables for logging, and a stored procedure to purge old data that has very slow DELETE performance, beyond what makes sense to me. I am looking for how to modify the tables or DELETE statements to perform reasonably well on LOB data. Alternatively, if there is an acknowledgement from Microsoft of the problem -- something like "we have fixed this with SQL server version x", or even "we see this performs poorly, but it is not a priority" -- that would work also.

This is running on Microsoft SQL Server 2012 (SP3). The below is substantially my actual table and code, just slightly simplified:

CREATE TABLE [LOG_VALUE](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [VALUE] [varchar](max) NOT NULL,
 [CHECKSUM] [int] NOT NULL,
 [VALUE_LEN] [int] NOT NULL,
 CONSTRAINT [PK_LOG_REQUEST] PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The deletes in question are done with this:

WHILE (@@ROWCOUNT > 0)
  DELETE TOP (100) [LOG_VALUE]
  OUTPUT DELETED.[VALUE_LEN] INTO @DELETED_ROWS
  WHERE [ID] IN (SELECT [ID] FROM @DELETE_IDS);

The basic stored procedure process is:

remove the values that reference the rows to be purged
select the [ID] values from the LOB table where NOT EXISTS in the above log table, into table variable @DELETE_IDS
delete top 100 rows at a time (to reduce/prevent contention/locking)

It definitely appears that the LOB data must be loaded into memory on the server in order to be deleted. Support: 1) when I insert 12 thousand rows into the table, it can be nearly instantaneous 2) retrieving 12 thousand rows is nearly instantaneous 3) once they are no longer in cache, both selecting and deleting the same 12 thousand rows takes ~40 seconds.

The most related question I could find is at:
https://serverfault.com/questions/241893/delete-performance-for-lob-data-in-sql-server
from 2011, but sadly has no satisfactory answer. I'm just hoping that asking this on dba.stackexchange.com will find a more knowledgeable audience :). That question's author definitely knew more about this than me, but as I read it the central points are:

when deleting rows, SQL server normally just marks them as deleted, and they are actually cleaned up by a "Ghost Cleanup Task" later on
when deleting LOB data, eXclusive locks are placed on all of the LOB data pages during the delete, and the pages are deallocated
when the pages are not in the buffer cache, these eXclusive locks wait for the pages to be loaded into memory
these pages being deallocated happens "up-front", not in a cleanup task. This means that the delete operation always waits on the data to be loaded before the statement finishes, unlocking the relevant rows

It seems like this deallocation should happen in a clean-up task, and not up-front. And since the LOB data is not referenced in the predicate (or OUTPUT), there should be no need to load it into the cache at all.

More information which may be relevant, but I don't think it is:

the values are often over 100KB, but vary significantly in size
the other table that points to the [LOG_VALUE].[ID] is not enforced with  Referential Integrity
the @DELETED_ROWS table is accumulated to note, at the end of the stored procedure, how many rows and bytes were deleted
there are currently 1.1 million rows, and by the value of the IDENTITY, there were only ever 1.6 million
There are no triggers
Currently, Ghost Record Count = 0
READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION are both OFF.

The whole database (and others on the same server) perform as I would expect in all cases except deleting/updating LOB data.


texts:
Hi I am having an issue with mongo version 2.6.4 my insert queries has became extremely slow after update when i check log on primary i got this

2014-09-16T16:40:18.576+0530 [conn51476] insert ibeat20140916.pageTrendLog query: { _id: ObjectId('54181a9aaf81111fafbe8c44'), url: "http://timesofindia.indiatimes.com/entertainment/telugu/movies/news/Deepika-Padukone-comes-out-in-support-of-Swetha-Babu/articleshow/42396946.cms?inte...", sessionId: "ca2bf536-e98e-44fb-c2d5-a881d2ee75b3", ip: "119.226.77.150", pgDownloadTime: 510, agentType: "Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0...", referer: "http://traffic.outbrain.com/network/redir?p=naQdy%2Fzis%2FCA%2FMjPzUSnnVbtZXahRKTZ3g5i9WAjjPbUG68csKBg1qkMMkajH9nYEcmndghMbZ216hl%2FL3us6Z%2BQq2BI7T1Y...", domain: "timesofindia.indiatimes.com", channel: "TNN", host: "timesofindia.indiatimes.com", pgAccessTime: 1410865818469, contentType: 1, articleId: "42396946", location: 1, action: 1, cat: "Entertainment", subcat: "Telugu", tags: [ "tollywood", "telugu cinema", "swetha basu", "deepika padukone fimls", "deepika padukone" ], isIndia: 1, geoLocation: "Delhi/NCR", publishTime: 1410616680000, author: "", pagePosition: "", ssoId: null } ninserted:1 keyUpdates:0 numYields:0 locks(micros) w:521 113ms

2014-09-16T16:40:18.576+0530 [conn51476] command ibeat20140916.$cmd command: insert { $msg: "query not recording (too large)" } ntoreturn:1 keyUpdates:0 numYields:0 locks(micros) w:177 reslen:140 113ms

Could you please check why this is happening.


texts: Unable to connect to remote SQL Server instance after thorough troubleshooting

texts:
I am at the end of my rope troubleshooting a remote SQL Server connection problem. I am a DBA but not a networking expert.

Certain information below has been sanitized using "x" in place of a letter.

I have access to a remote Windows 2008 R2 server through RDP. I connect to this server with the address jxxxxxx.sxxxx:5000. This succeeds.

Using this RDP connection I installed MS SQL Server 2014 Express on the server. This has been upgraded to SP2 Cumulative Update 8. I used the checkbox to install a default, unnamed instance. Using SQL Server Management Studio from the server I can connect to and use this new instance.

I have already performed the following configuration steps:

SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for MSSQLSERVER > enabled TCP/IP. I did remember to restart the service by going to SQL Server Services and restarting SQL Server (MSSQLSERVER).
In the firewall I created an inbound rule to allow all TCP traffic on 1433.

On my home computer I opened SQL Server Management Studio 2017 and attempted to connect with jxxxxxx.sxxxx. I am unable to connect, receiving the error message below:

Cannot connect to jxxxxxx.sxxxx.

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

A network-related or instance-specific error occurred while establishing a
connection to SQL Server. The server was not found or was not accessible.
Verify that the instance name is correct and that SQL Server is configured to
allow remote connections. (provider: Named Pipes Provider, error: 40 - Could
not open a connection to SQL Server)

To troubleshoot this I have gone to extremes:

On the server I used SSMS to connect to the instance and checked the instance properties. "Allow remote connections to this server" is checked.
I pinged jxxxxxx.sxxxx successfully and tried using that IP address for my home computer's SSMS connection string.
In Configuration Manager I right-clicked on the TCP/IP protocol and scrolled down to IP/All. Dynamic Ports is null and TCP Port is 1433. Though it shouldn't be necessary, I set every other IP type in that menu to "Enabled".
By using SSMS on the server I checked the SQL Server log and confirmed that it is listening to ip4  and ip6  for port 1433.
Though it shouldn't be necessary, I enabled SQL Server browser (I allowed UDP port 1434 inbound on the server firewall, set the browser service to automatic in Configuration Manager, and started it)
Though it shouldn't be necessary, I tried a more explicit connection string from my client SSMS: jxxxxxx.sxxxx,MSSQLSERVER:1433.
Again shouldn't matter because outbound should be allowed, but I fully turned off the client firewall.
Enabled named pipes, and in the Native Client settings enabled TCP/IP and Named Pipes, and restarted the service. I know that this should be unnecessary, but I'm going crazy.
Restarted both computers
Slept on it.

I have no more ideas and I need help. Is there some way that my connection string is wrong, or could be incorrect for some network environments? The Server is not in a domain and is named Oxxxxxxx, if that helps...


texts: SQL Server Management Studio - connection with SQL Server database engine

texts:
This question regards SQL Server 2005 and 2012.

I have a database server with two instances of SQL Server. Through SQL Server Management Studio I was able to connect to both instances by providing "IP-Address"\"InstanceName" and  user ID/password. But, I was not able to telnet the server on port 1433 from command prompt of the client PC.

Finally, when I specified TCP port 1433 through "SQL Server Configuration Manager" on the server and restart SQL Server services I was able to telnet to server on port 1433.

I want to know how "SQL Server Management Studio" was able to make a connection with SQL Server even if port 1433 was not active on SQL Server? And, the sequence of activities that takes place in background when Management Studio initiates connection with SQL Server?


texts: Constraint on existing table - unique combination of values only when another column is null

texts:
I have a pre-existing table structured similarly to this:

[Username]   [Gateway]   [Code]   [ActiveTo]   [OrgId]
=======================================================
user1        gateway      50        Null        100
user1        gateway      50        Null        101
user2        gateway2     51        Null        102
user3        gateway3     52       12/08/17     103

This table already has duplicate combinations of [Username], [Gateway], and [Code].

I want to add a constraint such that any new users added have a unique combination of [Username], [Gateway], and [Code] but that ignores two pre-existing duplicates such as the two user1 entries above (not ignoring new entries that match pre-existing ones).

I only want this to be applied when the duplicate's [ActiveTo] value is null (new entries have ActiveTo as null by default). So:

Applying the constraint to the table above would ignore the two user1 duplicates.
Adding a row [user2], [gateway2], [52], [Null] would be fine.
Adding a row [user2], [gateway2], [51], [Null] would then throw an error.
Then adding a row [user3], [gateway3], [52], [Null] would be fine.

Any ideas?


texts:
Although, the database backup file is only 2GB, the restore can take up to 15-20 minutes even on a x64 12G RAM, i7 machine. When I backup similar sized backup files made from a different database, the restore finishes in 3 seconds.  The backup file contains only one backup set (I always check overwrite existing data option).  On other slower machines it takes even longer to restore.

What could be causing the restore to take so long?


texts:
I want to benchmark the performance of MySQL, I want to know if logging affect performance.
MySQL has logs: general-log, binlog, errlog, redo log. Do these logging behaviors affect performance? For performance, I mean throughput, TPS obtained from benchmarks like TPC-C TPC-H YCSB sysbench oltp.
I know that the redo log (WAL) does affect performance because the queries should wait until the log to be flushed to disk. But do queries wait for other logs?
Thanks!


texts: MySQL: Safe to kill transaction that is stuck in "Waiting for table metadata lock"

texts:
Good morning/afternoon/night.

I attempted to add a column to a table with a good few million rows. Here is the full query:

alter table date_tasks_mark add column noDuplicate int unsigned not null default 0

However it seems to be stuck.

| 28893 | root         | localhost | database_name   | Query   | 10668 | Waiting for table metadata lock | alter table date_tasks_marks add column noDuplicate int unsigned not null default 0

Is it safe for me to kill the query or will it corrupt my table (it has been in this state for about a half hour)?


texts:
What is the ID antipattern and why / under which circumstances is it considered an antipattern?

  The id antipattern is having a unique ID column in each table. The ID
  is generated for each new record. Why is this an antipattern? –
  Andrew Wolfe 11 mins ago


texts: Adding index - There is insufficient system memory in resource pool 'default' to run this query

texts:
Here's the query I'm trying to run in order to add an index to my new memory optimized table

ALTER TABLE [s_Dim_Response_ram]
ADD INDEX response_ram_clientsurveydid NONCLUSTERED ([Clientid],[SurveyDefID])

And here's the error that I get:

There is insufficient system memory in resource pool 'default' to run this query.

The table has 23 million rows in it, and both columns in the index I'm trying to add are ints.

The server has 32GB of ram on it, and SQL Server is set to use a max of 26GB of ram.

It was upgraded from SQL 2008 R2 to SQL Server 2017 over the weekend, so I could try to take advantage of Memory Optimized tables. I made a copy of an existing regular table schema but as a memory optimized, and then tried to add a index as shown above.

How do I fix this error? Do I need more memory for SQL Server? Or more free system memory? Are there settings I could change to help?

edit: I tried deleting all the rows from the table first, and then adding the index. I still get the same error with an empty table.

Here are my memory performance counters:

counter_name                                                    MB
Stolen Server Memory                                        15682.015625
Free Memory                                                 4106.617187
Lock Memory                                                 8.093750
Log Pool Memory                                             3.140625
Connection Memory                                           2.070312
Optimizer Memory                                            1.554687
Cursor memory usage                                         0.031250
Cursor memory usage                                         .031250
Cursor memory usage                                         0.000000
Granted Workspace Memory                                    0.000000

Here are my top 20 Clerks

    type                mb
Total               22085.913770
MEMORYCLERK_XTP         14733.703125
MEMORYCLERK_SQLBUFFERPOOL   6877.562500
CACHESTORE_SQLCP        104.601562
CACHESTORE_OBJCP        94.734375
MEMORYCLERK_SOSNODE     54.804687
MEMORYCLERK_SQLSTORENG      54.156250
CACHESTORE_PHDR         31.093750
USERSTORE_SCHEMAMGR     27.031250
MEMORYCLERK_SQLCLR      21.296875
MEMORYCLERK_SQLTRACE        17.179687
USERSTORE_DBMETADATA        13.648434
CACHESTORE_SYSTEMROWSET     13.539053
MEMORYCLERK_SQLGENERAL      8.132812
OBJECTSTORE_LOCK_MANAGER    8.093749
USERSTORE_TOKENPERM     5.077892
MEMORYCLERK_SQLLOGPOOL      3.140625
USERSTORE_OBJPERM       2.257797
MEMORYCLERK_XE          2.195312
MEMORYCLERK_SQLCONNECTIONPOOL   2.000000
MEMORYCLERK_SQLOPTIMIZER    1.593750

My SQL version is Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)   Aug 22 2017 17:04:49   Copyright (C) 2017 Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hypervisor)


texts:
I have an sql-script that recreates my unit-testing database. At the moment it's run with \i <path_to_my_script>. It goes like

// create user, give him some priveleges

CREATE DATABASE my_database;

CREATE TABLE my_table (
  id UUID primary key,
  data json
);

But my_table is created in postgres database, not in my_database.

How can I specify that my_table should be created in my_database?

I use postgresql 10.5.


texts: Do signed certificates from public certificate authorities add any value for always encrypted?

texts: If I create Always Encrypted columns in Microsoft SQL Server from the SSMS gui, it makes a self signed certificate. Is there any value in making a CSR and paying a certificate authority (or using letsencrypt.org) to make a certificate? Does that chain of trust add and value in this case?


texts: Suggested ways to route traffic between master / slave

texts:
I have multiple Postgres servers up, running in master / slave streaming replication mode.

I was wondering what the suggested methods of routing write traffic to the master, and the read traffic to the slave nodes.


texts: Update large table with millions of rows in Azure SQL Server, in Parallel

texts:
We have a large ProductOrders table with many rows, and plan to update it. The table has range of ProductType Ids from 1-1000.
Table has around 2 million rows, 15 columns. Common ProductOrders table: ProductOrderId, ProductId, ProductTypeid, ManufacturerId, OrderDate, etc, indexes are placed properly on foreign keys and common queries.
Two options are proposed to update the table,

Setup 10,000+ multiple queries (grouped by range of Product TypeIds) which are sent out in Parallel, and update the ProductOrders Table all at once.

Or the second proposition, is using following method. Update them sequentially proposed here to avoid locking up the whole table. https://stackoverflow.com/a/35931214/12425844

I would assume option 1) having multiple update queries can lead to multiple Writer-Writer Deadlocks.
Is it safe to have multiple queries sent in parallel? Which option is best practice? Additionally, thoughts it best practice to let Sql automatically conduct DDL items in parallel with configured Maxdop setting, rather than user attempting.
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver15
first option is being proposed by our Application software team .
Currently using AzureSQl.


texts:
I receive results that I consider inconsistent to simple queries.

-- This returns 3 rows
select * from persons
where person_id in (12,13,14)

-- This returns nothing
select *
from documents
where person_id in (12,13,14)

-- This returns nothing !?!?
select *
  from persons
 where persons.person_id not in ( select documents.person_id from documents )

There is a FK on documents.person_id pointing to persons.person_id

Am I missing something?
This is my @@version:

Microsoft SQL Server 2016 (SP1-CU1) (KB3208177) - 13.0.4411.0 (X64)   Jan  6 2017 14:24:37   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hypervisor)


texts: Is it a common pattern in having two AlwaysOn Availability Groups for DR and Reporting?

texts:
We have a primary OLTP server in the cloud.

We intend to have Synchronous Alwayson Availability Group for Disaster Recovery,
and another Availability Group AAG for Ad-hoc Query Reporting.
This way if our Primary Server goes down, there will not be contention between our Second DR server and Reporting queries. Reporting will be a third node. We will not require different schemas or indexes on the Reporting Server. Do you see any flaws with this design pattern or is it generally common practice?


texts:
This is a follow-up to my previous question Find month to month percentage growth rate

I have a table called Lead. I need to find out the month to month increase in lead how can achieve this in MySQL

I need to find percentage of increase/decrease  in Lead each months..

Table

CREATE TABLE Lead(leadId int(11) NOT NULL AUTO_INCREMENT,
  leadType varchar(99) DEFAULT NULL,
  leadName varchar(99) DEFAULT NULL,
  leadDate date DEFAULT NULL,
  PRIMARY KEY (leadId));

Table Data

insert into Lead values
(16,'individual','dinesh','2016-06-18'),
(15,'corporate','benz','2017-05-10'),
(14,'individual','fino','2017-05-05'),
(13,'individual','jubin','2017-04-30'),
(12,'corporate','chola','2017-06-16'),
(10,'corporate','Volvo','2017-03-31'),
(9,'individual','manoj','2017-06-16'),
(8,'individual','vijay','2017-06-16'),
(6,'corporate','CHS','2017-06-16'),
(5,'corporate','sastra','2017-06-16'),
(4,'individual','rajesh','2017-06-16'),
(3,'individual','karthi','2017-06-16'),
(2,'individual','vijo','2017-06-16'),
(1,'individual','suresh','2017-06-16');


texts:
Our ERP is using an Oracle database while our BI system is based on SQL Server.
We need to get the changed data in Oracle and copy it in our staging area.

At the moment we get the changed rows by comparing Oracle ROWNUM columns and copy the data with "Microsoft Connectors for Oracle by Attunity" in SSIS.
Our problem is that we have to modify the packages by hand to add dynamic queries and it's very inconvenient.

We use this connector because it's easy to create tables on SQL Server with the correct data type based on the oracle source and the performance is great.

We don't like to use linked servers, at least with the default drivers as we had a lot of trouble (a lot of casting to get data and very bad performance compared to the Attunity product)

I've seen that Attunity offers a product called "CDC for ssis".
Does anybody have any experience with this tool?
What's the best way to get changed data from Oracle to SQL Server?
What's the best way to create tables with correct data types?


texts:
I have a table called STUDENT

+-----------+-----------+-----------+---------------+
| StudentID | FirstName | LastName  | EnrollmenDate |
+-----------+-----------+-----------+---------------+
|         1 | x         | x         | x             |
|         2 | x         | x         | x             |
|         3 | x         | x         | x             |
+-----------+-----------+-----------+---------------+

Write a query that pulls the student who registered the last?

  a) select top 1 * from STUDENTorder by EnrollmentDate Desc

or

  b) select * from STUDENT where EnrollmentDate = (select
  Max(EnrollmentDate) from STUDENT)

I tend to ask this question in interview. One candidate answered b) I was expecting a). Which one is better query?


texts: What are the support windows for SQL Server 2012 SP3 and SP4 respectively?

texts: I am hearing two different rumors. One is that SQL Server 2012 SP3 loses support June 2017. I am also hearing SQL Server 2012 SP4 is due out in June 2017. Is SP4 just a rumor? Or is it true? If true, when does SP4 lose support?


texts: Sorting Report entries on alphanumeric combination

texts:
I am trying to move over a database that was previously stored in an excel workbook.  The previous setup had a different worksheet for each bookshelf and the individual shelves were listed at "A-levels" such as A1, A2, A32, etc...  However, on Some shelves, there are subsets such as A9A1, A9A2, A9A5, etc...

The report format that I have created has each different bookshelf on a new page when printed, but based on the sorting, when there are multiple entries for the same shelf (A11A1, A11A4, etc...) it falls apart and they don't stay in order.

Is there any sort expression that you could use to first sort by number, then IF there is another letter, sort by letter, and then sort that subset by their respective numbers?


texts: Unable to take backup of mysql database using Xtrabackup

texts:
I am trying to take mysql backup without any downtime but unable to take the backup using Xtrabackup .

Actual Database is almost about 40+ GB in size. I am trying to first satisfy myself. Hence, I have created a sample database on a server with 5000 Tables (all InnoDB tables).
As per the blog "there are some cases when you can get table entirely missing from the backup if you use this technique "  "--single-transaction  are not safe to use on production environment.

[root@localhost mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 56
Server version: 5.5.25a-log MySQL Community Server (GPL) by Remi
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| innodbtest         |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)
mysql> use innodbtest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show create table T4000;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| T4000 | CREATE TABLE `T4000` (
  `A` int(11) NOT NULL,
  `B` int(11) DEFAULT NULL,
  `C` char(1) DEFAULT NULL,
  PRIMARY KEY (`A`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table T3040;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| T3040 | CREATE TABLE `T3040` (
  `A` int(11) NOT NULL,
  `B` int(11) DEFAULT NULL,
  `C` char(1) DEFAULT NULL,
  PRIMARY KEY (`A`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

I used tool xtrabackup and followed the steps to create a backup

xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/opt/bkp/

After taking the backup I am not getting the ".ibd" files at the backup location.

[root@localhost mysql]# ls -al /opt/bkp/
total 190676
drwxr-xr-x 2 root root      4096 Nov 27 19:02 .
drwxr-xr-x 5 root root      4096 Nov 27 18:08 ..
-rw-r----- 1 root root 195035136 Nov 27 19:02 ibdata1
-rw-r----- 1 root root        79 Nov 27 19:02 xtrabackup_checkpoints
-rw-r----- 1 root root      2560 Nov 27 19:02 xtrabackup_logfile
[root@localhost mysql]#

After restoring the database using xtrabackup, I am not getting the database folder "innodbtest" on the other server too.

Where am I wrong?


texts: MySql not displaying all columns in the result

texts:
I am trying to teach myself DBA. when I execute this query:

SELECT *, CASE WHEN Trans_type LIKE '%C%'
             THEN 'Credit Card'
             ELSE NULL
          END AS type
FROM Transactions
WHERE cust_no=2 && Trans_type LIKE '%C%';

I get the following:

+---------+------------+-------------------+----------------------+--------+------------+-------------+
| cust_no | date       | Category          | Subcategory          | Amount | Trans_Type | type        |
+---------+------------+-------------------+----------------------+--------+------------+-------------+
         | Credit Card | Bills & Utilities | Bills & Utilities Ot | 5      | C
         | Credit Card | Household/Home    | DIY/Home Improvement | 15     | C
         | Credit Card | Shopping          | Clothing & Accessori | 215    | C
         | Credit Card | Shopping          | Clothing & Accessori | 135    | C
+---------+------------+-------------------+----------------------+--------+------------+-------------+

4 rows in set (3.53 sec)

Why are the cust_no, date not displaying and also the CASE type displaying at the start in my mysql console?

Thanks.

The Table Transactions Description below:

+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| cust_no     | varchar(10) | YES  | MUL | NULL    |       |
| date        | date        | YES  |     | NULL    |       |
| Category    | varchar(20) | YES  |     | NULL    |       |
| Subcategory | varchar(20) | YES  |     | NULL    |       |
| Amount      | varchar(6)  | YES  |     | NULL    |       |
| Trans_Type  | varchar(5)  | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+


texts: What does "sample_ms" and "num_of_reads" means in "sys.dm_io_virtual_file_stats". what is the interval time of stats

texts:
As per MS-Docs it returns the Number of milliseconds since the computer was started, but i'm getting different result when i compare with actual server start time in sys.dm_os_sys_info.

Here are the queries:

select sqlserver_start_time from sys.dm_os_sys_info;

select (sample_ms/1000) sample_sec, file_id from sys.dm_io_virtual_file_stats (DB_ID(), NULL);

select (DATEDIFF (SECOND, sqlserver_start_time, SYSDATETIME()) ) as Server_start_since_sec from sys.dm_os_sys_info;

This is the result:

If sample_ms reflects computer/server start-time not the SQL Instance's start-time (comparing 2nd vs 3rd in result screenshot). Does it mean the statistic values (num_of_reads, num_of_writes etc..) in DMF (sys.dm_io_virtual_file_stats) returns cumulative reads since the computer restart or since the SQL Instance restart or there is different interval time that we need to consider?

Before restart SQL Service

After restart SQL Service (NO OS restart)


texts: signal_wait_time_ms > wait_time_ms for SOS_SCHEDULER_YIELD in sys.dm_os_wait_stats

texts:
Question regarding SOS_SCHEDULER_YIELD when using sys.dm_os_wait_stats:
Should signal_wait_time_ms not be exactly the same as wait_time_ms for the SOS_SCHEDULER_YIELD wait type?
For other wait types signal wait is when a worker thread is waiting in the Runnable Queue on the scheduler. Therefore I conclude that signal wait should be the same as total wait time (= wait_time_ms) for SOS_SCHEDULER_YIELD when looking at sys.dm_os_wait_stats.
So my problem is that the two columns mentioned should have the same value in this DMV for this particular wait type.
More specifically, I am looking at a case where signal_wait_time_ms is larger than wait_time_ms, and I would like an explanation for that difference.
The example I am looking at is from the book "Pro SQL Server 2019 wait Statistics" by Van de Laar. In a screenshot on page 120, signal wait is higher for SOS_SCHEDULER_YIELD.


texts:
Created a script to show last backup locations, sizes and dates

SELECT DatabaseName = x.database_name,
        LastBackupDate = x.backup_finish_date,
        BackupSizeKB = x.backup_size,
        LastBackupFileName = x.device_name,
        left(right(x.device_name, 21),17) as Backup_Device_Date,
        type = x.type
        FROM (  SELECT  bs.database_name, bs.type,
                case
                    when exists(
                        SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='backupset' and column_name = 'compressed_backup_size'
                    )
                    then cast(round(max(bs.compressed_backup_size/1024),0) as numeric)
                    else cast(round(max(bs.backup_size/1024),0) as numeric)
                end as backup_size,
                max(bs.backup_finish_date) as backup_finish_date,
                max(bmf.physical_device_name) as device_name
          FROM  msdb.dbo.backupmediafamily bmf
                  JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
                  JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
          WHERE   bs.is_copy_only = 0
          GROUP BY bs.type, bs.database_name) x
WHERE x.database_name not in ('master', 'model', 'msdb')
AND x.type IN ('D')
ORDER BY DatabaseName;

The only problem, is that I get the following error when its executed on a SQL Server 2005 Enterprise 9.00.3042 64bit

Msg 207, Level 16, State 1, Line 12
Invalid column name 'compressed_backup_size'.

If I change the then/else to

case
when exists(
 SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='backupset' and column_name = 'compressed_backup_size'
)
then 1
else 0
end as backup_size

I get 1 or 0 for backup_size depending on whether *compressed_backup_size* exists in backupset.

database_name   type    backup_size backup_finish_date  device_name
model   I   0   2013-09-02 18:30:19.000 Data Protector_(DEFAULT)_model_163022971_0
msdb    I   0   2013-09-02 18:30:22.000 Data Protector_(DEFAULT)_msdb_163022971_0

So in spite of using CASE WHEN THEN ELSE to check for the existence of *compressed_backup_size*, I'm still getting an error. Any thoughts on why the THEN argument is being parsed when the column *compressed_backup_size* clearly doesn't exist ?

Thanks in advance


texts: MAXTRANSFERSIZE setting causes restore to fail

texts:
I have a database that seems to be functioning fine with no apparent errors except that any full backup taken is broken - attempting a restore fails with an error "RESTORE detected an error on page (18469:-1767164485)"

DBCC CheckDB on the database completes without errors.

EDIT #1

The backup is created with the following command:

BACKUP DATABASE @DatabaseName
TO
DISK = @backupFile1,
DISK = @backupFile2,
DISK = @backupFile3
WITH COMPRESSION, RETAINDAYS = 0, FORMAT, INIT, SKIP, NAME = @DatabaseName

EDIT #2

I just completed a backup using the GUI database backup in case my script was wrong somehow - This gave the same result of a damaged backup.

--

This only started happening last night.  The only significant change that I am aware of is that I changed the 'auto-shrink' option of the database (it was originally set to true).

Is anybody aware of a problem in this area with SQL Server 2012 (specifically I'm on version 11.0.3128)?  It could just be coincidence or related to something else entirely but that seems the most likely cause at the moment.

In addition does anybody have any advice on what to do in such a situation?  The database is functioning fine (as far as I can determine), but I don't care to be without backups for very long...!


texts: Is "many database writes" an anti-pattern?

texts:
I am designing a database api. I have two options:

Have a complex aggregation that queries the data as desired or,
Create a materialized view that logs all data changes in an easy to query manner and potentially makes querying this data faster than querying data from option 1.

I was wondering is having many writes an anti-pattern in database design and administration? Because if not I would love to use option 2.
I am using a no-SQL database; MongoDB.


texts: Why is OS authentication considered poor security for Oracle databases?

texts:
Oracle is deprecating OS authentication according to the Oracle Database Security Guide, which says

  Be aware that the REMOTE_OS_AUTHENT
  parameter was deprecated in Oracle
  Database 11g Release 1 (11.1), and is
  retained only for backward
  compatibility.

In addition, most security information and tools consider
OS (external) authentication to be a security problem.  I am trying to understand why this is the case.  Here are some advantages I see of OS authentication:

Without OS Authentication applications must store passwords in a variety of applications each with their own security model and vulnerabilities.
Domain authentication already has to be secure because if it is not then database security just slows down access to the database, but cannot prevent it.
Users that only have to remember one domain password can be made to create more secure domain passwords more easily than they can be made to create even less secure database passwords as the number of different databases they must connect to increases.


texts: Copy related records from 3 tables to another 3 tables and maintain the relationship between them

texts:
I have 3 tables that sore orders, order items and order item comments . I need to copy the data for one order to 3 new tables that have the same structure and maintain the relationship. the order will have a new id for the new table, order items should include this new created id for the order in its values and the comments should also have the id of the newly created order items.

Can this be done in one shot?


texts: Select parent rows and include cost of children

texts:
I have a WORKORDER table that has parent and child WOs in it:
with workorder as (
select 'WO37342' as wonum,      null as parent, 297.36 as actlabcost, 200 as actmatcost, 0 as actservcost, 0 as acttoolcost from dual
union all
select 'WO37427' as wonum, 'WO37342' as parent,  99.12 as actlabcost,   0 as actmatcost, 0 as actservcost, 0 as acttoolcost from dual
union all
select 'WO37429' as wonum, 'WO37342' as parent,  99.12 as actlabcost, 100 as actmatcost, 0 as actservcost, 0 as acttoolcost from dual
)
select
    *
from
    workorder

 WONUM   PARENT  ACTLABCOST ACTMATCOST ACTSERVCOST ACTTOOLCOST
 ------- ------- ---------- ---------- ----------- -----------
 WO37342             297.36        200           0           0
 WO37427 WO37342      99.12          0           0           0
 WO37429 WO37342      99.12        100           0           0

I want to select the parent rows and include the cost of the children in the parents:
 WONUM    ACTLABCOST ACTMATCOST ACTSERVCOST ACTTOOLCOST
 ------- ----------- ---------- ----------- -----------
 WO37342       495.6        300           0           0

Is there a concise way of doing this with Oracle 18c SQL?


texts: Database design for actions on some entity

texts:
I am making a todo list kind of a service. One todo list will have several items , that can be done/deleted/snoozed etc. Users can also add a new item to the list , delete an item etc.

I want to know what the best practice for this kind of a table should be.
say I have 3 actions that can be performed like add/delete/done.
I was thinking of storing all the actions that can be performed on that list individually. But this brings the problem of getting the final state of the list.
How should I store activity performed on some entity as well as be able to get the final state of it.

It is possible to get all the actions ever performed on the list, and then process them , to get to the final result, but this sounds too processor intensive.
Is there a better way , or a best practice that I may follow ?


texts:
Can I improve performance of this query?

SELECT transact.id
           FROM transaction0 transact
           JOIN journ_notes journ ON transact.journal_jdoid = journ.jdoid
           JOIN product account ON transact.product_id = account.id
          WHERE account.classtype::text = 'loan'::text
          AND journ.notes_jdoid IS NOT null;

explain:

Nested Loop  (cost=1.01..135956337.96 rows=1 width=8) (actual time=393.040..50792.689 rows=20 loops=1)
  ->  Nested Loop  (cost=0.58..135956333.67 rows=1 width=16) (actual time=393.018..50790.822 rows=547 loops=1)
        ->  Seq Scan on journ_notes journ  (cost=0.00..469345.58 rows=30466258 width=8) (actual time=0.007..7229.810 rows=30466263 loops=1)
              Filter: (notes_jdoid IS NOT NULL)
        ->  Index Scan using i_transaction0_journal_jdoid on transaction0 transact  (cost=0.58..4.44 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=30466263)
              Index Cond: (journal_jdoid = journ.jdoid)
  ->  Index Scan using i_product_id on product account  (cost=0.43..4.28 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=547)
        Index Cond: (id = transact.product_id)
        Filter: ((classtype)::text = 'loan'::text)
        Rows Removed by Filter: 1
Planning time: 0.464 ms
Execution time: 50792.743 ms

I already have the index listed on the explain log, but also the unused ones below:

create INDEX  _i_transaction0_product_id_id on transaction0(product_id, id)

create INDEX  _i_product_classtype_id on product(classtype, id)


texts: how to group data against more than one conditions

texts:
I have a table named Employees with below two columns.

==================

Employees

1- EmpId  (auto incremental id)

2- EmpName  (varchar)

There is another table named 'LateDays' with below five columns.

==================

LateDays

1- LateDayId  (auto incremental id)

2- EmployeeId  (foreign key)

3- LateDate   (DateTime)

4- FinePaid   (bool)

5- Informed   (bool)

Now I am trying to write a SQL statement to fetch employees along with their Total Fine, Paid Fine and their Allowed Quota Availed. Each employee has 4 allowed days to come late.
I came up with the following (incorrect) SQl, which works fine when there is only one employee in Employees table but fails for more.

SELECT
    Employees.name as EmpName,
    (select count(LateDays.LateDate)*200 from Employees inner join LateDays on Employees.EmpId = LateDays.EmployeeEmpId) as TotalFine,
    count(LateDays.LateDate)*200 as PaidFine,
    (select COUNT(LateDays.Informed)from Employees inner join LateDays on Employees.EmpId = LateDays.EmployeeEmpId where LateDays.Informed='true') as InformedDays
FROM
    Employees inner join LateDays
    on Employees.EmpId = LateDays.EmployeeEmpId
WHERE
    LateDays.FinePaid = 1
GROUP BY
    name

Kindly help me writing a proper SQL for my situation.

Edit:
I am trying to add sample data.

Employees table:

EmpId   Name
1       Pervaiz
2       Nabeel
3       Muhammad
4       james

Late Days table:

LateDayId   EmployeeEmpId   LateDate    FinePaid    Informed
4           1               2014-09-21  0           0
5           1               2014-09-20  0           1
6           6               2014-09-21  0           0
7           6               2014-10-01  1           0


texts: INFORMATION_SCHEMA.VIEW_TABLE_USAGE only shows tables from the same catalog

texts:
I'm working with a database that has many cross-database views. While INFORMATION_SCHEMA.VIEWS lists all these views, INFORMATION_SCHEMA.VIEW_TABLE_USAGE and INFORMATION_SCHEMA.VIEW_COLUMN_USAGE do not.

All views in question are in this format:

CREATE VIEW [dbo].[Invoice]
AS SELECT * FROM [otherdb].[dbo].[Invoice]

The following query returns 0 rows:

SELECT
    VIEW_CATALOG
    ,VIEW_SCHEMA
    ,VIEW_NAME
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE TABLE_CATALOG <> VIEW_CATALOG

Leaving the appropriateness of SELECT * for another discussion, why are the external database references not in the INFORMATION_SCHEMA views and is there any way to get them?


texts: Fuzzystrmatch Postgresql similarity func with array

texts:
How do I pass an array to the SIMILARITY ()function

I am looking for something similar to the following:

SELECT denomination, is_active
   FROM MyTable WHERE
SIMILARITY(denomination, ['Coke', 'Coque']) > 0.4;


texts: Converting to date and performing a count

texts:
SELECT a.UserID, a.SignUpDate, DATE_DIFF(CURRENT_DATE, a.SignUpDate, day) As DaysActive
FROM (SELECT u.Userid, CAST(MIN(u.SessionLogin)AS DATE) AS SignUpDate
      FROM database.data_source.user_session_table as a)

The above code pulls the session login time from the user_session table performs a min function to collect their initial login, whilst converting it from a timestamp to a date format. DaysActive shows how many days since they have logged in.

What i am trying to do is find how many days they have logged into the application since their initial sign in.(Days Active)
SessionLogin field captures a timestamp each time they login, i am trying to convert that to a date, whilst also adding it to the above table as a count.

Current Output

UserID  | SignUpDate| DaysActive |
--------+-----------+-------------
001     |2017-12-04 | 35         |
002     |2017-12-08 | 31         |
003     |2017-12-05 | 34         |

user_session_table

UserID  |SessionLogin         |
--------+---------------------|
001     |2017-12-04 01:37:49  |
001     |2017-12-08 05:47:04  |
001     |2017-12-05 04:40:00  |
002     |2017-12-08 12:25:39  |
002     |2017-12-09 01:37:49  |
003     |2017-12-05 05:47:04  |
003     |2017-12-05 08:40:00  |

Desired Output

UserID  | SignUpDate | DaysActive| DaysEngaged
--------+-----------+------------+-----------
001     |2017-12-04 | 35         |3
002     |2017-12-08 | 31         |2
003     |2017-12-05 | 34         |1

Code: MSSQL / Bigquery


texts: SQL Server installation error: other instance on drive has filepath over 260 characters

texts:
I'm a first-time poster here so apologies for any etiquette lapses, but here is my question...

I am attempting to install SQL Server 2012 Enterprise Edition Database Engine onto a SAN drive called E: on which a SQL Server Analysis Services instance already resides.

My install failed with this error:

  Updating permission setting for folder
  'E:\MSAS11.SASDEV01\OLAP\Data\221_Complaints_atkurib_62857693-2ba5-4348-b91e-da6ff92c02d5.0.db\fact_complaint_cedd5416-245b-4409-b490-6e74a0d93b88.0.dim'
  failed.

When I dig a little deeper (into the Detail.txt file in the Setup Bootstrap/Log folders) I find the reason for the error is:

  Failed to get full path for file
  'E:\MSAS11.SASDEV01\OLAP\Data\221_Complaints_atkurib_62857693-2ba5-4348-b91e-da6ff92c02d5.0.db\fact_complaint_cedd5416-245b-4409-b490-6e74a0d93b88.0.dim\2.H$fact_complaint_cedd5416-245b-4409-b490-6e74a0d93b88$communication_received_channel_scdkey.ID_TO_POS.0.idf'.
  The specified file path exceeds the system-defined maximum length.
  Paths must be less than 260 characters.

So the problem appears to be that a file-path within the already-installed SSAS instance folders exceeds a 260-character limit.

Has anyone else ever had this issue and can anyone advise please on how I should proceed..?


texts:
From what I've gathered from my textbook, 5NF doesn't allow lossy decomposition, so that when we join tables that are in 5NF, no spurious tuples are generated.
The definition in my book states that:
"A relation schema R is in 5NF with respect to a set F of functional, multivalued, and
join dependencies if, for every non-trivial join dependency JD(R1, R2, ..., Rn) in
F+, every Ri is a superkey of R."
If non-trivial join dependency means that the join of relation R1, R2, ..., Rn should be equal to R, then the presence of a join-dependency should be enough for the relations R1, R2, ..., Rn to be in 5NF. Why do we need every Ri to be a super key of R?
Also, the definition of trivial join dependency states that:
"A join dependency JD(R1, R2, ..., Rn), specified on relation schema R,
is a trivial JD if one of the relation schemas Ri in JD(R1, R2, ..., Rn) is equal to R."
If R1, R2, ..., Rn are decompositions of R, how can one of them be equal to R?


texts: Caching impression counts to avoid mysql inserts

texts:
We currently get a LOT of mySQL inserts/updates on duplicate daily (millions) and we're looking at speeding up the application by using some form of counter with memcached and then clearing it on intervals with a cron.

Is this possible? What happens if 2 people hit at the exact same millisecond? Will the counter still go up twice or will it only increment once? What is the best way to handle such a write heavy database?


texts: SQL Server - Standard Deviation in a Windowed Query

texts:
SQL Server provides the STDEV function, but it cannot be used in conjunction with window functions.

For example

SELECT
     TestName
    ,[BatchSize]
    ,[NumIterations] = COUNT(*)
    ,[Min] = MIN(RuntimeMs)
    ,[Average] = AVG(RuntimeMs)
    ,[Max] = MAX(RuntimeMs)
    ,[StdDev] = STDEV(RuntimeMs)
FROM #TimingResult
GROUP BY TestName, [BatchSize]

How can I compute percentiles and standard deviation at the same time?


texts: MDX Query for RANK regardless of hierarchy

texts:
I'm hoping to add a calculation to my SSAS Cube (SSAS 2012) that will show the relative rank of whatever the current member is against all other members in that grouper, without specifying the groupers in advance.

For example, say I have a calculation already for Efficiency which is defined as $$/hour.

I would like to add an Efficiency Rank calculation as well that will tell me what relative position the current member has in it's set.

The Syntax I tried was

RANK([Measures].[Efficiency].CurrentMember, [Measures].[Efficiency])

Which I realize is incorrect.

I can't seem to locate a guide to this anywhere else, so I'm hoping there is some established best practice or pattern for this since it seems like it would be a relatively common business requirement.


texts: I'm busy installing MSSQL 2016, and strongly considering using Windows Auth only. That can interfere with no local installation, but it has occurred to me that if another machine or process wanted to access this service, it would have to be granted a Windows User credential, that I can then admit to the database. Is this a real problem, and if so, what are steps I can take to solve it?


texts: How to use COALESCE with multiple rows and without preceding comma?

texts:
I'm trying to achieve the following:

California | Los Angeles, San Francisco, Sacramento
Florida    | Jacksonville, Miami

Unfortunately, I'm getting ",Los Angeles, San Francisco, Sacramento, Jacksonville, Miami"

I can achieve my desired results using the STUFF function, but was wondering if there's a cleaner way of doing it using COALESCE?

STATE       | CITY
California  | San Francisco
California  | Los Angeles
California  | Sacramento
Florida     | Miami
Florida     | Jacksonville

DECLARE @col NVARCHAR(MAX);
SELECT @col= COALESCE(@col, '') + ',' + city
FROM tbl where city = 'California';
SELECT @col;

Thanks


texts: How can I verify that a full database restore reflects the exact source database in SQL Server?

texts:
We are decommissioning our old SQL Server 2000 Ent. instance in favor of SQL Server 2008 R2 Ent. My planned migration path is:

Terminate client connections (2000)
Full Backup (2000)
Restore (2008 R2)

I am being asked to provide conclusive proof that every single transaction "made it" and that the data is an exact replication of what existed on the 2000 instance.

I hope that I can use the following documentation as evidence:

Full Database Backups
Performing a Complete Database Restore (Full Recovery Model)

However, if this is not sufficient, the only thing I can think of is to rip through ever row of every table of every database and calculate a checksum (on both instances) as well as get row counts for every table in every database.

Is there any better way to satisfy the "exact replica" verification criteria? I'm also open to better documentation.


texts: SSMA for Access - Migration Assistant Wizard fails at link tables

texts:
When using Sql Server Migration Assistant for Access with the Migration Wizard, all goes well converting an Access (2016) database to SQL Server Express (2017) until I get to the Link Tables step.  Here the SQL Server connection dialogue appears:

I enter the same credentials used to create the tables.  My understanding is that this dialogue appears to allow for using a different sql server login with Access, so probably nothing out of the ordinary here.  But then I receive the following error:

Connection failed:
SQLState: '01000'
SQL Server Error: 772
[Microsoft][ODBC SQL Server Driver]
[DBNETLIB]ConnectionOpen (SECDoClientHandshake()).
Connection failed:
SQLState: '08001'
SQL Server Error: 18
[Microsoft][ODBC SQL Server Driver]
[DBNETLIB]SSL Security error

I've tried with and without encrypting the connection with the same error, so it doesn't seem like an SSL error.

I have four SQL Server ODBC drivers installed:

ODBC Driver 11 for SQL Server
ODBC Driver 13 for SQL Server
SQL Server
SQL Server Native Client 11.0

If I create a connections using the ODBC Data Source Administrator, I can produce the same error using the SQL Server driver.  The other three work fine.  So it seems that SSMA is using the SQL Server driver (oldest of the four) instead of the SQL Server Native Client, which is supposedly a dependency of SSMA.