texts: Why is Server Configuration in Pgadmin3 inaccessible for me?

texts: My Postgres server is running (localhost:5432), but Tools > Server Configuration is greyed out for me as below. I was attempting to access it to edit/locate my postgres.conf file.


texts: Which part of a DB bulk insert takes the most time?

texts:
One of the most common tip with regards to DB writing is to group your inserts and execute them as a single transaction. My question is, which part of the [begin transaction] - insert - [commit transaction] is taking the bulk of the processing time?

From what I've read the insert statements are first saved in the cache then executed all at once when the commit transaction is executed. If I understand that correctly, wouldn't the commit transaction part take the most time?

The reason I'm asking is because we have this code where we try to limit the size of inserts within a single transaction with the aim to minimize the whole processing time to around 200ms.

The way were doing it is we're constantly checking the time if it has exceeded 200ms whenever we do an insert. Once 200ms has been reached (or all insert statements have been performed) then we execute the commit transaction. But if what I mentioned above is true, wouldn't our code still be prone to significantly exceeding 200ms execution time?

Edit: using SQLite3, btw.


texts:
A user can create tables owned by them in Oracle.
Can this be done using MySQL?


texts: sqlplus command not found after installing oracle 12c

texts: I have going at it for the last 12 hours... I installed Oracle 12c on rhel7, the first installation didn’t go well so Installed a second one without changing the first installation; I created a new folder and proceeded with the installation. However I have not been able to connect to the database in anyway. When I try the command: env|grep -i ORACLE_HOME I get : ORACLE_HOME= /u01/app/oracle/product/12.1.0/dbhome_2/spfileorcl.ora. Can you suggest a solution on how to setup my environment variable based on the configurations in the picture?


texts: access all_mview or user_mviews from app code

texts: I need to access the user_mviews or all_mviews table to get meta information about materialized view in my app. Which grants are necessary to read the user_mviews table?


texts: Can I alter an HSTORE column to JSON datatype?

texts: Is there a way to modify an existing column of HSTORE datatype (that has data) to JSON datatype in a Postgres database?


texts: How to configure transactional replication for managed instance as a publisher and a subscriber for two different sql server at the same time?

texts:
The scenario is such that a subscriber is subscribed to transaction replication based publisher (distributor running on publisher).

I then want this subscriber to be a publisher to another subscriber.
Can I keep the same distributor for this scenario or I have to create another distributor on the subscriber that I want to be publisher to another subscriber?

I have successfully implemented Transactional replication between azure sql vm (publisher and distributor) and azure sql managed instance(subscriber) now I want to perform replication from this managed instance (now subscriber to be publisher) to azure sql database(subscriber)

Do I need to create distributor on every publisher which is on different servers?

**I want the data changed on azure sql vm to be replicated on azure sql database.**But my azure sql server vm replicating on managed instance and my managed instance should be replicating data to azure sql database.
Can some body help me out?


texts: Does Couchbase/CouchDB allow me to move / skip the CRUD middle man from the server?

texts:
So currently this is a common setup:

  Client -> Ajax -> Server CRUD -> Database

Does Couchbase allow me to do this?

  Client -> Ajax -> Database

This might be more technically correct:

  Client -> Ajax REST -> Database

Does this mean that I could just have the client directly interacting with the database, therefore reducing the load on the server? Theoretically this would make scaling a website more cost efficient.

If so, has this method been increasing in popularity usage? If not, why? It seems like this is a huge advantage.


texts: Upgrading 5.6 to 8.0 without losing any data

texts: I am using windows server. As per need of project I need to upgrade MySQL from 5.6 to 8.0 but I already have the existing databases and tables. How should I upgrade without losing any data in windows?


texts:
IDE: Microsoft SQL Server Management Studio

Mistakenly I have cleaned a table data using

delete from table tblBookData

and now it has cleaned that table, please tell me is there any way to get back that data?

If yes than please tell me the process, as I have studied there is a way to get back data if we have not closed the session.


texts:
I have just read an interesting blog article regarding User-Defined resultset ordering.

  Some applications, such as todo lists, need to maintain a user-defined
  order of items. The challenge is that the order is arbitrary and can
  change when the user rearranges items

An example table could be like so:

create table items
(
  itemid int primary key,
  itemdata varchar(200),
  ...
  ...
  userorder ???
);

With a possible retrieval query of:

select * from items order by userorder asc;

Are there any approaches that I should consider given I'd like to keep updates to a minimum, updates atomic, and retrieval queries as simple and "performant" as possible?

My initial thought was to use an extra TIMESTAMP column, and only update userorder and the timestamp values for a single row (with the retrieval query using ORDERing by userorder asc, usertimestamp desc), but that fails when you need to move a row in between two other rows that have the same value.

No specific database in mind, as i use a broad spectrum myself.


texts: Space Calculation for SQL Server Table: What's wrong with my query?

texts:
I'm using the following query in order to calculate some space related measurements on a particular table in my SQL Server database:
SELECT
    t.name AS TableName,
    p.rows,
    (sum(a.total_pages) * 8) as reserved,
    (sum(a.data_pages) * 8) as data,
    N'Not Needed' as index_size,
    (sum(a.total_pages) * 8) -  (sum(a.used_pages) * 8) as unused
FROM
    sys.tables t
INNER JOIN
    sys.indexes i ON t.object_id = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.name = N'XYZ'
GROUP BY t.name, p.rows
ORDER BY
   1 Desc;

It gives me correct results for calculating number of rows, reserved space, and unused space. However, when I compare its output with the output from running sp_spaceused stored procedure, I observe a different value for space used by data:

How can I fix it?


texts: Requirements for IIS server used for web sync, merge replicaiton

texts:
Short question: Can anyone tell me what the requirements (especially when it comes to SQL components) are for the web server used as web sync for merge replication?

Background:

I have a solution which uses merge replication to do one-way data sync with the client application of the solution.

The server uses SQL Server 2008, the client SQL Server 2008 Express and initiates the pull subscription using RMO. The request goes through Web Sync. All is fine when the IIS and the DB is on the same server - the problem occurs when the IIS is on a standalone web server.

From the error logs, it seems certain SQL components are required on the web server to make this work - but I haven't been able to find which ones. I've tried installing SQL Server 2008 Express on this server, with no luck.

So: Can anyone tell me what the requirements (especially when it comes to SQL components) are for the web server used as web sync for merge replication?


texts: Composing the "ultimate database maintenance script"

texts:
I'm trying to compose T-SQL script that will:

A) Shrink database size to the minimum possible size;
B) Make integrity checks, and completely "reorganize" and optimize the database so that it is in a "fresh" state.

Here is what I currently have:

-- Check database integrity:

DBCC CHECKDB WITH NO_INFOMSGS;
GO

-- Get space usage information:

EXEC sp_spaceused @updateusage = N'TRUE';
GO

-- Shrink database:

DBCC SHRINKDATABASE (0, 0);
GO

-- Reindex all tables:

EXEC sp_msforeachtable N'PRINT ''Indexing table: ?''; DBCC DBREINDEX (''?'');'
GO

-- Update statistics for all tables:

EXEC sp_msforeachtable N'UPDATE STATISTICS ? WITH FULLSCAN'
GO

-- Clear procedure cache:

DBCC FREEPROCCACHE;
GO

-- Update all usage info in the database:

DBCC UPDATEUSAGE (0);
GO

Can you please validate if all these are correct? The precedence of commands (e.g. updating statistics after index rebuild, or vice versa), syntax, arguments, etc. are all optimal? Can you suggest any other commands? Performance is not a problem, I'll be executing this script probably once in a month, night hours, so even if it takes hours it doesn't matter. I just need the maximum optimization of the database. Any input would be greatly appreciated.

Thanks!


texts:
I have some db's created with an incorrect logical name for both rows and log. how can I change the logical names to match the db name with a script? (and does not want to change physical file names)

Also how can I find all the databases with same logical name?

(SQL Server 2012)

thanks


texts: Install SQL Server 2012 missing vs_Setup.msi from Visual Studio 2010

texts:
I am trying to install SQL Server 2012 Enterprise with SQL Server Data Tools included.  I understand that SQL Server Data Tools is part of what was formerly known as BIDS.  The installation for SQL Server 2012 prompts me for the ms_setup.msi installation file from Visual Studio 2010.  However, I am using Visual Studio 2012 currently only.  The equivalent file in VS 2012 is called vs_professionalcore.msi and is located in Visual_Studio2012_Pro\packages\professionalcore\Setup.

How can I install SQL Server 2012 with Data Tools having only Visual Studio 2012 given the setup?  Is there a download or registry entry I need to make? Thanks for any pointers.


texts: Backup and Restore Oracle database in Azure

texts:
Has anyone recreated an on-premise Oracle Database in the cloud and specifically an Azure Database(Oracle) Instance?

The total size of the database I want to recreate as backup in the cloud is around 260GBs and the version is Oracle 10G.

The two options I can see it's either RMAN(via tape) or a database dump(exp or expdp). What is the most efficient and trouble-free way out of these two? My opinion is that a database dump could be a better option, however I am worried in terms of performance and running all those insert statements from scratch. Any thoughts?

Any help gladly appreciated! Thanks!


texts: Hive query with RANK() in a WHERE clause

texts:
I want to do this, to get all the rows for a given a,b combo that have the highest value of c:

SELECT a, b, c
FROM x
WHERE RANK() OVER (PARTITION BY a,b ORDER BY c DESC) = 1

It fails, saying invalid column reference 'c': (possible column names are: a, b, c).

I can wrap the RANK() in a subselect, and check the value outside:

SELECT a, b, c
FROM (
  SELECT a, b, c, RANK() OVER (PARTITION BY a,b ORDER BY c DESC) AS r
  FROM x
) rq
WHERE r = 1

Any idea why I can't do this in the WHERE clause of the simple query? Is it because of it being an aggregate/window function, so has to be done after the WHERE, like a GROUP BY?


texts: SQL substract values from same column different rows

texts:
I have above mysql stock table where both inward and outward data are stored and identified each type by type column (i and o). I want to build a query to retrieve balance quantity(qty) per each batchno or drugid.
for example, batch no 2001 has 1000 inward and 200 outward. So the balance should be 800.
if there are many outwards for same batchno, they all should be included.

some help in this regard is highly appreciated.


texts:
What does GRANT REFERENCES ON TYPE::dbo.myType TO public exactly mean?

I found this statement together with every single user defined type we have. I wonder if this is useful or not. AFAIK this permission is required only for creation of new objects (e.g. a table or function) to reference the data type - so in case the regular database user will never create new database schema objects, does this kind of explicit permission make any sense?


texts:
About designing star schema: I have three date columns in a fact table fact_table (insert_date, trade_date, close_date ...). And I don't know how many date dimensions should be created?

Case 1: Dim A . It means: one row @fact_table have three FKs to A.

Case 2: Dim A (for insert_date), Dim B (trade_date),Dim C (close_date). It means: one row @fact_table have one FK to A, one FK to B, and one FK to C .

Question: How many date dimensions should be created?


texts: Performing SQL string manipulations on a single column

texts:
I recently came across a problem at an interview which I wasn't able to answer. The ask was to write some SQL fetch the first three number(s) after crossing two 2 decimals.
For example:
Input table:
IP address
1.2.3.4.5
11.22.33.44.55
5.6.7.8.9
111.222.333.444
.
.
.

Output table:
IP address
1.2.3
11.22.33
5.6.7
111.222.333
.
.

My initial approach was to use the inbuilt substring. For example: select substring(ip,0,5) from table. But this is a broken logic as it does not account for values such as 11.22.33 or 111.222.333
How can this be achieved?


texts:
I'm trying to run sp_blitz on SQL Server 2014 and it seems to be taking a long time. What is the normal length?

We only have around 30 databases. One is 300GB, but it's going on for 15 min.


texts: Can the new “SCRAM-SHA-256” password authentication mechanism in Postgres 10 and later work with any JDBC drivers?

texts:
Postgres 10 added support for a new password authentication mechanism, Salted Challenge Response Authentication Mechanism (SCRAM-SHA-256, see RFC 7677). Discussed by Michael Paquier.

➥ Can this mechanism work with JDBC drivers?

If not, why not?

If so, have any drivers yet implemented this feature?


texts: Need a Function that allows parameters to pass as argument for logic inside the function in Greenplum

texts:
Suppose I have a query :-

select 'public.test123 is' || ' ' || quote_literal(obj_description('public.test123'::regclass)) || ';' as test;
            test
-------------------------------------
     public.test123 is 'dog';
(1 row)

I want to create a function named comment that will help me pass the schema_name and table-name(public and test123) as in above query, and provide me the above output.

And then insert this output to another table public.entries with the table name passed and output of the query in comment column like below-

select * from public.entries;
table_name | comment
------------+---------
public.test123 | public.test123 is 'dog';
(1 rows)

and return the function with SUCCESS.

Output like:-

select comment('public','test123');
           test
-------------------------------------
     SUCCESS


texts:
I'm not a DBA so looking for some advice on tuning a MySQL (MyISAM) database. I've found that during peak hours we are getting pretty severe load spikes which are slowing down the web front-end.

I've also used JMeter to run tests against the server, and with 20 concurrent users, load spikes to 400%. This doesn't seem normal.

I've ran MySQL Tuner and the following have been flagged up:

[!!] Query cache prunes per day: 4351
[!!] Joins performed without indexes: 155460
[!!] Table cache hit rate: 0% (1K open / 11M opened)

The query cache prunes seems straight forward, and I think I could safely increase this to 100MB without any negative scalability isuses.

I'm confused by 'joins without indexes' as all my tables are indexed. Is this more likely to be an issue with queries using non-indexed columns?

The table cache hit rate is also something I'm not sure about. The number of Open_tables never decreases below 1000, suggesting the tables never close. 1000 is the value of table_open_cache in my config.


texts: String replace using concatenated strings from various columns

texts:
I'd like to remove a substring in a column via update statement. The substring to replace consists of multiple strings from other different columns but in strict order.

The specification says:

  replace(string text, from text, to text)

Example:

field1                     |field2       |field3      |field4
---------------------------+-------------+------------+---------------
(varchar)bla abla 123 ooops|(varchar)abla|(varchar)123|(varchar) ooops

The substring to replace consists of strings in field2, field3, field4 and blanks between field2 and field3, field3 and field4. In the given example: "abla 123 ooops".

So, after performing the update statement field1 should only contain the string "bla".

Note: I want to perform this on all rows and not to a particular one.


texts: How do I model a many-to-many relationship on two different columns?

texts:
I am trying to understand how to model a certain type of relationship.

I have a dataset that I would like to put into a database, however, I have this relationship among two columns in my data.

Here is a sample of what I am looking at:

Company_ID1  Company_ID2
      9581091  53506312
      9581091  961273620
     79735371  53506312
     79735371  79735371
     79735371  135962137
     46667523  9122532
     55189732  9122532
     71453880  9122532
     77817617  9122532
     77817617  79834910
     79871820  9122532
     79871820  79834910
     98158277  9122532
     98158277  458182615
    134303192  9122532
    187502299  458182615

As you can see company_id2=53506312 is associated with two different company_id1's. This is just one example, but there is a many-to-many relationship between the two company_Id's. By inspection, this example is the same company.

How would I model this situation so that the database finds this kind of association and is able to group them all together?

The motivation behind this is finding sales and contract by a company. So grouping by either company_id will result in the wrong answer.

How I have solved this with text files, is I have a graph where all the nodes are the unique company_id's and the edges is the tuple (company_id1, company_id2).  Then I find all disconnected subgraphs in the graph and each ones of those is a company.

So I have something like this:

Company_ID1  Company_ID2      company_number
          9581091  53506312         1
          9581091  961273620        1
         79735371  53506312         1
         79735371  79735371         1
         79735371  135962137        1
         46667523  9122532          2
        55189732   9122532          2
        71453880   9122532          2
        77817617   9122532          2
        77817617   79834910         2
        79871820   9122532          2
        79871820   79834910         2
        98158277   9122532          2
        98158277   458182615        2
        134303192  9122532          2
        187502299  458182615        2

So then I groupby company and I can do summary reports.

But I would like to now move to a database and have the database do this calculation for me. So, two questions. What kind of relationship is this called, and how do I model it in my database?

One last think, these two columns company_id1 and company_id2 aren't the primary keys.


texts: Improving Firebase Database Design for Mobile Application

texts:
I'm making a forum-style app in iOS app using Firebase, and I'm wondering if there's anything I can do to improve my database structure. I'm worried about the "HOME" part, where each post will require 2 api calls to receive the poster's image URL. If there are 1000 posts, it will be 2000 api calls per refresh per user.
Login: 1-3 calls

CheckUsers: 1-3 firestore calls depending if user exists

Get Users
Save User / Create User
Set Group

Home: 1 Firestore call + 1 Firestore call/post + 1 Storage call/post (if not cached)

retrievePost():

Get All Posts : 1 firestore call
For Loop

Get User Thumbnail: 1 firestore call per post
Load Image: 1 storage call per post (image is cached)

Add Reply: 3 calls

Add a Reply collection document to the Post document: 1 firestore call
Increment “replies” field by 1: 1 firestore call
Add reply post ID to user DB: 1 firestore call

Add Post: 1 call

Add Post to DB: 1 firestore call

Like/Unlike Post: 1 call

Add/remove from array: 1 firestore call

Account: 1 Firestore call + 1 Storage call

Update User DP:

Upload image to storage: 1 storage call
Set image URL to User DB: 1 firestore call


texts: Proper use of varbinary type in MS SQL Server

texts:
I am at a new position and I am being told to implement a stored procedure that will accept a list of user ids and update a flag.

When I suggested the use of a table value parameter (array emulation), I was told to implement as demonstrated below, as this is how it's been done before.  I'm not a DBA but I am a full stack developer and this smells funny to me.

FYI- the user ids are implemented as type int

CREATE PROCEDURE [dbo].[UpdateUsers]
 @rgIDs varbinary(max)      -- contains several ids
AS
 DECLARE @tblTmp TABLE (ID int PRIMARY KEY)

 DECLARE @ich int, @cch int, @ID int
 SET @cch = DATALENGTH (@rgIDs)
 SET @ich = 1

 WHILE (@ich < @cch)
 BEGIN
  SET @ID = SUBSTRING (@rgIDs, @ich, 4)

  UPDATE dbo.Users u
  SET isUpdated = 1
  WHERE u.ID = @ID

  SET @ich = @ich + 4
 END

Doesn't a table valued type work better here?  More readable,  performance, less error prone, etc...?

I will be using SQL Server 2012 or 2014, I believe.  Definitely > 2008.


texts: MySQL: Grant Read-Only DB Access for one DB and Create/Drop Access for other DBs

texts:
Within MySQL, is it possible to provide a user read-only access to an existing database, but allow the user to create or drop any new databases?

I know it is possible to grant privileges via matching the database name (e.g. by prefix or suffix), but what would be the proper syntax for the first statement below:

GRANT ALL PRIVILEGES ON 'NOT(readonly_db_name)' ...;
GRANT SELECT ON 'readonly_db_name' ...;


texts:
At my work, I am often told that I shouldn't do a table scan because it could negatively impact the database and the app using the database. What exactly do they mean by "negatively impact"? One negative impact I can think of is that a table scan would use up a lot of the disk's time, blocking other requests from using the disk.
Is there a way to do a table scan without any negative impacts? Sometimes I need to do a table scan for one off checks (for routine things, I would of course make an index).


texts: SQL Server CLR procedures and SQLCLR_QUANTUM_PUNISHMENT

texts:
I have a CLR stored procedure running on SQL Server 2014.  This stored procedure gathers some data (generally numbering in the tens-of-thousands of rows range), runs some computations on the data, generating an output dataset, and writes that output dataset to a couple of destination tables.  For small runs, this takes a few seconds.  For our larger runs, it's currently taking about 25 minutes.  During that 25 minutes, we have observed a significant number of SQLCLR_QUANTUM_PUNISHMENT waits.  So I'm trying to educate myself on what we can do to reduce the occurrence of these waits.  I've found plenty of resources that tell me, "Occurs when a CLR task is throttled because it has exceeded its execution quantum. This throttling is done in order to reduce the effect of this resource-intensive task on other tasks."  And I've read that SQL Server's execution quantum is 4ms.  So am I to assume that any time a CLR stored procedure or function takes more that 4ms of CPU time, this wait type will occur?

Also, I found a reference indicating that some CLR procedures should be yielding.  Now, our CLR procedure is single-threaded.  I can certainly add some sleep calls at key places and see what kind of affect that has.  But I would like to have a better understanding of what's going on.  So if anyone has some good knowledge to share or has a link to a good document/article, I would appreciate it.  Finding information about this wait type beyond that BOL quote has been a bit difficult.


texts: MySQL: split value in column to get multiple rows

texts:
I have some data in a table like so:

product_id      | categories
----------------+-------------
10              | 9,12
11              | 8
12              | 11,18,5

I want a select statement that would produce this output:

product_id      | category_id
----------------+-------------
10              | 9
10              | 12
11              | 8
12              | 11
12              | 18
12              | 5

I don't know how to phrase this scenario to be able to google it.


texts: I have to migrate my old Oracle 10g database system to a brand new 12c server. The vendor suggests to use two SSD disks in RAID1. Since I'm very happy with my current configuration (two SAS RAID10 arrays) I'm a little bit scared about this. Do you have suggestion/reccomendations/experiences to share?


texts: MySQL | Complex (for me) SQL Statement, how to?

texts:
I have a table that contains posts and another table that contains meta options for each post in first table. The meta options table is a key value pare table.

Lets say I have the posts tables that look like that

Posts
---------------------------------------------
id | Other columns ....
---------------------------------------------
1  | data ...
2  | data ...
3  | data ...
4  | data ...
5  | data ...

and the meta options table that look like that

Meta
---------------------------------------------
id | post_id | meta_key | meta_value
---------------------------------------------
1  | 1       | views    | 5
2  | 1       | maxViews | 100
3  | 2       | views    | 0
4  | 2       | maxViews | 1000
5  | 1       | publisOn | 2013-05-14 10:41
6  | 1       | auhor    | MyUser
7  | 2       | auhor    | Another author

The question is, how can I get the post with id equals to 1 by making a comparison of the meta_key values views and maxViews.

For example, I like to retrive the post id with ID 1 only if the views is lower than the masViews in the Meta table.

Any help please ?


texts: Determine Nodes in Network with PostgreSQL

texts:
I have a table where every entry is a node and the table contains the direct connections of each node to other nodes. I am looking to create a view with a column for each node containing all the nodes in the chain, not just the nodes the node itself is connected to.
An example would be generating the Nodes in Chain column from the first two columns of the following table:
CREATE TABLE example
(
    node text,
    connections text[],
    nodes_in_chain text[]
)

INSERT INTO example VALUES
('a', ARRAY['a','b'],         null),
('b', ARRAY['a','b','c','d'], null),
('c', ARRAY['b','c'],         null),
('d', ARRAY['b','d'],         null),
('e', ARRAY['e','f'],         null),
('f', ARRAY['e','f'],         null);

Node   Connections    Nodes in Chain
"a"    "{a,b}"        "{a,b,c,d}"
"b"    "{a,b,c,d}"    "{a,b,c,d}"
"c"    "{b,c}"        "{a,b,c,d}"
"d"    "{b,d}"        "{a,b,c,d}"
"e"    "{e,f}"        "{e,f}"
"f"    "{e,f}"        "{e,f}"

That is a small simplified version of the real problem. If I can solve the example, the full table should be no problem.
The data of this table can be visualized in the following way:

I have looked into several different methods to solve this problem. I have looked into recursive CTEs but I have not managed to make them work.
Each node is connected to itself currently in the database. Not a problem to remove the connection to itself in the database if that is necessary.
Probably unnecessary background to the problem:
The origin of this problem comes from attempting to identify vehicles in traffic. The original database contains a vehicles location and speed at every time step t in a given area. The goal is to determine the time spent at a traffic light. To solve this problem a stopping area for the traffic light was identified. Each vehicle in this area with a speed below a certain threshold is considered to be waiting for the traffic light. Due to a long queue line, vehicles may however be queuing outside this area. Therefore a traffic line ("Chain of Nodes") is made with all vehicles that are within a certain distance of each other and have a low speed below. Starting from a vehicle within the identified queue area. The problem is part of scientific research into aircraft taxi times. The vehicles are therefore aircraft and the stoplights are runway thresholds.
I first performed the calculation to identify vehicles in an area with Python and pandas. However the code took 10x longer to run which made it prohibitive to the project. The code was very simple without manually introduced loops and could therefore not be accelerated (I believe). I will also be comparing the speed of performing the queuing algorithm in Python versus PostgreSQL.


texts: Lot of log files in $FSLOG generating. How to stop?

texts:
I'm using SQL Server 2012 Express Edition and I have a problem with huge amount of files generated in:

C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\MyDatabaseStream_Filegroup_Data\$FSLOG\

It is generating roughly 150 files per minute and taking so much disc space on C Drive. Size of MyDatabaseStream is growing rapidly quick and making C Drive full (around 140GB)

How can I stop generating those files?


texts: What is Oracle CPU Time and CPU Usage and why is it important?

texts:
I am trying to understand Oracle's CPU Usage and CPU Time metric for its monitoring (such as in the ASH Viewer) for individual queries

From my understanding:

CPU Time -- the amount of CPU time (in milliseconds) spent on database user-level activities (this does NOT include system-level activities such as garbage collection)

CPU Usage -- CPU Usage (in %) is the percentage of time the CPU spent on database user-level activities over other user-level activities

Does this mean if query #1 has higher CPU usage compared to query #2, it's using more resources than query #2?
Why is knowing CPU Usage helpful for determining how resource heavy a query is? What about memory usage?

The second question is what I don't understand.


texts:
I have a one table Q&A like below:

CREATE TABLE  `totaalconceptterreinonderhoud`.`acties`
(
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
      `type` int(11) NOT NULL DEFAULT '3' COMMENT 'Actie types:\n1 = vraag\n2 = antwoord\n3 = afspraak\n4 = nieuws\n',
      `aanvang_actie` date NOT NULL COMMENT 'Aanvang afspraak datum + tijd',
      `start_tijd` time NOT NULL COMMENT 'Start tijd',
      `eind_tijd` time NOT NULL COMMENT 'Eind tijd',
      `proj_id` int(11) NOT NULL DEFAULT '0' COMMENT 'Koppeling met Project',
      `onderwerp` varchar(100) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Onderwerp afspraak',
      `notitie` text COLLATE utf8_unicode_ci NOT NULL COMMENT 'Tekstuele omschrijving',
      `locatie` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
      `meld_aan_contacts` int(11) NOT NULL DEFAULT '1' COMMENT 'Melden aan contactpersonen',
      `koppel_id` int(11) NOT NULL DEFAULT '0' COMMENT 'koppelinng met andere id',
      `aangemaakt_door` varchar(100) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Wie heeft laatst aangepast',
      `aanmaak_datum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Wanneer aangemaakt',
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=97 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Acties'

There are 4 types of record:

type=1 (question)
type=2 (answer)
3 and 4 (not relevant now)

What I want is to list all questions with their answers below. The questions and answers have a koppel_id field that connects them. The ordering must be last asked question first, with answers right below.

Is this hard to do? Any help would be nice.


texts: Microsoft SQL Server 2017 remove all instances

texts:
I installed Microsoft SQL Server 2017 for like 4-5 with some failures. I can't manage to have a clean re-install. I think there are some residual files or configurations.

I tired uninstall & reinstall, deleted all files download and redownload, cleaned regs (runned CCleaner).

How can I set everything so that I can perform a clean installation?

UPDATE

I found out that I can run that multiple times and remove them one by one. It's that enough?

UPDATE
Followed this tutorial https://www.mssqltips.com/sqlservertip/4050/cleanly-uninstalling-stubborn-sql-server-components/ as recommended by @Kin

Still can't get rid of these


texts: Export, based on query, into INSERT text file

texts:
I need to export some data into a file. The data are selected with a query that will often be made of multiple joins. I want the import operation to be as easy as possible. I was thinking of building an INSERT file with such a method:
Oracle PL/SQL: Dump query result into file

But I am wondering if there is some tool already existing to export data based on a query. If this is the case, I might as well be happy with some binary file, as long as importing this file afterward is straight forward.
I see there is exp, but it doesn't seem to really be able to handle queries, only basic WHERE clauses.


texts: Is there any way to force at least one column NOT NULL

texts:
I have a schedule_days table that is having id, day, date columns.

  id - auto increment, primary key
  day - weekday - enum(weekdays) default not null
  date - a specific date - default not null

The schedule is for the train time table. That will have the same routine for all weeks except for any specific date, such as any festival. Thus I have set both null as I want to set either day or date.

If the date is specified, then I can get the day from it, so it doesn't make sense to ask or force the user to set day, which may cause human error.

I may use TRIGGER but would like to know if there is any better way to do in MySQL - 8.0.* version

Table Query

CREATE TABLE IF NOT EXISTS schedule_days
(
    id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    day  ENUM ('SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY') DEFAULT NULL,
    date DATE DEFAULT NULL,
    INDEX staff_index (day),
    CONSTRAINT day_day_check CHECK (`day` IS NULL + `date` IS NULL = 1) # check constraint
) ENGINE = INNODB;


texts: How to trigger an action without using triggers

texts: I just want to ask. We have a calendar table, let's call it CAL1 in one database, lets call it DB1. CAL1 is manually populated. Now in another database, DB2, there is another calendar table CAL2. What I need to do is to populate CAL2 based on the values from CAL1. I don't know when will CAL1 be populated. There is also some other calendar tables/tables in other databases that will refer to CAL1 so I need to make an API that will populate or at least execute the procedures from the other databases e.g. DB2 whenever new data is loaded to CAL1. I initially think of using triggers but read that it's better to not use it. So is there any other way that I can do this without using trigger? Thanks a lot.


texts: Amazon RDS instance recovery during mysqldump

texts:
I have a db.t2.small read replica, which crashes every time I run mysqldump.

The issues started after I made some table changes - my main suspicion is the use of InnoDB.

This replica is used as a backup (incase the main server was to fail); and allowed me to run mysqldump every night, so I can create an off-site backup without effecting the main server.

AWS now goes though the recovery process when I use mysqldump.

01:53:13 - Recovery of the DB instance has started. Recovery time will vary with the amount of data to be recovered.
01:57:11 - DB instance restarted
01:57:36 - Recovery of the DB instance is complete.

Annoying the error logs are pretty much empty, so I have no idea what's causing it to fail.

The largest tables are:

788 MB = report_data
410 MB = report_data_log
418 MB = email_log_boy

The report_data table used to contain 36 TEXT fields (an example of more fields being added over time), and used MyISAM.

I changed this to use a simpler 1 TEXT field per row (an report_id, field_name, field_value, created_date, author_id), and it's now setup to use InnoDB.

The overall table size has decreased a bit.

The report_data_log table is basically the same table structure, but with a date to record when each value was replaced (as in, on editing, the record is copied from the report_data table, so I have a history of what's changed in the last 3 months).

I export each table individually with:

/usr/bin/mysqldump --defaults-extra-file=/backup/database.cnf --skip-extended-insert --skip-dump-date db report_data_log

I have tried adding --single-transaction, but that hasn't changed anything.

The config file just contains the username, password, host, ssl-ca, and sets ssl-verify-server-cert to true.

The /etc/mysql/conf.d/mysqldump.cnf file contains:

[mysqldump]
quick
quote-names
max_allowed_packet = 1024M

The server configuration hasn't been changed recently, where it includes:

max_allowed_packet: 1073741824
net_read_timeout: 3600
net_write_timeout: 3600

Oddly, it's able to export the 788MB report_data table, but crashes after exporting ~200MB of the 410MB report_data_log table.

Using db.t2.medium will probably fix this issue, but it doubles the cost - which seems a little silly considering the main server is fine, and both are idle most of the time (never appears to exceed 25% CPU, average 1.5%; or running low on Freeable Memory).

I'm tempted to switch back to MyISAM, but was wondering if there are any better AWS RDS logs to find out what's happened, or if there is something different about InnoDB that I'm missing?

As an interesting aside, the "db.t2.small" MariaDB instance provides 2GB of RAM.

The parameter group sets "innodb_buffer_pool_size" to "{DBInstanceClassMemory*3/4}".

But when I run:

SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_size";

It returns 2147483648... so it's being set to 2GB, not 1.5GB.


texts: Does innodb_data_reads include reads from the buffer pool?

texts:
We have set up some monitoring on many of our databases that serve many different applications.  It seems that they all report back more writes than reads when in reality that is very unlikely.  I know much of the code inside and out and we do far more reads than we do writes.  It has me wondering if a read is counted if it comes out of the cache (InnoDB buffer pool)?

Most of the stats seem to be based off of the innodb_data_reads system variable.  Our buffer pools are large enough to contain all of our InnoDB tables.

One server I checked says it is like 10/1 writes to reads.  However, when I view these two status variables:

Innodb_buffer_pool_read_requests: 1458637157995

Innodb_buffer_pool_reads: 516322

Innodb_data_reads: 643489

Innodb_data_writes: 17209580

Something doesn't work here..

Thanks for any help you can give!


texts: What is the difference between int(8) and int(5) in mysql?

texts:
I found out, that if you have a field defined as INT(8) without ZEROFILL it will behave exactly as INT(5)

in both cases the maximum value is

−2,147,483,648 to 2,147,483,647, from −(2^31) to 2^31 − 1

or do i miss something?

I found this Question:
https://dba.stackexchange.com/a/370/12923

  The (5) represents the display width of the field. From the
  manual, it states:

    The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column
    display width from being displayed correctly. For example, a column
    specified as SMALLINT(3) has the usual SMALLINT range of -32768 to
    32767, and values outside the range permitted by three digits are
    displayed in full using more than three digits.

  The display width, from what I can tell, can be used to left-pad
  numbers that are less than the defined width. So 00322, instead of
  322. TBH, I've never used it.

  But it doesn't affect the storage size of the column. An int will take
  up more space than a smallint.

so there seems to be no difference then.


texts: Select rows with the highest value for one ID, after grouping by two IDs

texts:
It is given in the problem that occurrences has to happen more than once, but the person (PID) should only be selected with the (agent) AID having the highest BustCount.
SELECT I.AgentID, I.PersonID, Count(*) AS BustCount
FROM InvolvedIn I
WHERE I.AgentID IS NOT NULL
AND I.isCulprit = TRUE
GROUP BY I.AgentID, I.PersonID HAVING COUNT(*) > 1;

This what I get:
AID     PID     BustCount
1       11      4
2       22      2
3       22      5
4       33      5
5       44      4

But I would like to get only the PID with the highest BustCount (not line row 2).
AID     PID     BustCount
1       11      4
3       22      5
4       33      5
5       44      4


texts: Star schema with both many-to-many relations and inheritance

texts:
The problem

I am trying to make a star schema of the following situation, concerning transactions on a stock market. I have summarized the scenario here:

Each transaction traded on the market has

1 or more buyers, which can either be a person or a venue
1 or more sellers, which can either be a person or a venue

The attempt

I have tried looking up best practices on modelling many to many relationships and inheritance in a star schema, and combined these. This gives me the following star schema

Type       Name                   Description
Fact       Transaction            Fact containing all transaction details
Dim        EntityGroup            Groupings of entities (just there to make a complete model - no attributes)
Dim        Entity                 Superclass of all entities (just there to make a complete model - no attributes)
Bridge     EntityGroup_Entity     Bridge for the many-to-many relationship between entities and groups
Dim        Person                 Subclass of entities, holding all attributes of persons (shares the primary key with Dim Entity)
Dim        Venue                  Subclass of entities, holding all attributes of venues

The question

Am i right that this would be best practice for a star schema, or can you point out any places the design is faulty?


texts:
I have published my website on Amazon EC2 (Singapore region) and I have used MySQL RDS instance for the data storage. Everything is working very fine except performance.

It seems that all queries, but especially select statements, are performing very slow. If I check this issue on my local PC, it is working very well. But when I try to get data from RDS instance, it is slow. Some of the select statements takes 2-3 seconds to fetch data.

I have properly tuned up all table indexes, and normalized/de-normalized as required. I have made all necessary settings on RDS custom parameter group eg. max_connection, buffer etc. (don't know if I am something missing), but it has not helped. Can someone help me figure out the issue?


texts: Do relational databases typically understand '1' and '0' as booleans?

texts:
I'm working on a code library that helps users build database searches from form inputs (a layer on top of an ORM). I want it to be compatible with most relational databases.

I currently have a method for checkbox inputs that converts '1' into true and '0' into false, but I wonder if it's necessary.

From my testing, PostgreSQL and MySQL already understand these values as booleans.

database  |SELECT true = '1'|SELECT true = '0'|SELECT false = '1'|SELECT false = '0'|
-------------------------------------------------------------------------------------
PostgreSQL|      t          |         f       |        f         |        t         |
MySQL     |      1          |         0       |        0         |        1         |

Is this true of other major relational databases - eg, Microsoft SQL Server, Oracle, and DB2? Is it a SQL standard?


texts: Who changed SQL server max memory configuration?

texts:
Is there a way to tell who changed SQL server's max memory after a several SQL reboots?. I looked at the default trace log for event category 81 (memory configuration changes) but could not find anything.
SQL server -->reports--> configuration report changes is also empty.
Please suggest.


texts: How to Update Column in this example in sql?


texts: Operational issue for scheduling mongodump as backup method

texts:
For production backup, MongoDB recommends mongodump instead of mongoexport for accuracy of data. However, a few operational issue remain unclear to me, appreciate if anyone can advise on this matter.

Logging

How to redirect mongodump's progress log into a file or some other repository rather than stdout? I tried the following code but it simply output an empty file and all logs remain in stdout.

mongodump --host MONGO --out %FULLPATH% --oplog >> out.log

Error Handling and Retry

Does mongodump return some error code when it failed? I used windows task scheduler to run mongodump and it always consider the job has been run successfully while mongodump may have failed silently.

I'm running this in Windows.


texts:
I'm migrating a database and i need to change the "container", i don't exactly how to name it. I give an example to keep it clear :

old base:

VARCHAR2(20 BYTE)

new base:

VARCHAR2(20 CHAR)

I can do it individually with alter...modify query but it takes so much time to prepare the queries cause the tables have a hundred or more columns. Is there any scripting way or a query that changes all the byte containers with char ?


texts: How to map similar camelcased, pascalcased, underscored, lowerscored words to the same id?

texts:
I'm working on PostgreSQL and I have a table like this called words_table:

I need all equivalent words (like software_design and software-design) to have its map value = to its word's equivalent id_word.
In other words, I want all the words softwaredesign, software_design, software.design... and so on having the same number on its map column (in this specific case, a number from 1 to 10). The same applies to civil_engineering and IndustrialDesign.

I know this involves some regular expressions and case insensitive comparisons but i'm stuck at the SQL logic.
I know these expressions could be useful:

regexp_replace(word, '(\.|:|,|&|-)','','g')

To handle the separators

lower(something)

To handle the uppercase lowercase matching or

UPDATE words_table SET a.map = b.id_word WHERE word ILIKE something

... WHERE word ~* something

for case insensitive matching.

Should I create new columns with regexp_replaced words and do the mapping after that with some joins?
Or maybe something with CamelCase and underscore matching? Maybe functions?
Which is the optimal solution?


texts: Dynamic Server Name in SQL Statements (from clause)

texts:
My scenario is I have a thick client side app using a SQL DB. The use of the app is divided into 10 "sites" mostly by Geography but some by Usage. I've written an SP to archive selected data out of the DB and put it elsewhere as the DBs are growing to nearly 500GB in size at this point. Further whilst most servers host one application DB there is one server (and instance) that hosts 3 DBs.

My dilemma is I want to write code that is the same everytime. Ideally I would write something like

declare @dbname varchar(20)
set @dbname = 'Archive' + DB_NAME(DB_ID())

INSERT INTO @dbname.dbo.ArchiveTable (ID, DATA)
SELECT (ID, DATA) from TheData
WHERE Archive = 1

That way I can have the same SP installed on each server, any bug/feature fixes doesn't need me to manually type up 10 different SPs to install.

In the real world I'm also limited by SQL2000 however I'd be interested to hear in ideas using SQL2000 and/or SQL2008R2. The servers are a mixture of both.

As I understand it Synonyms wouldn't help as on the instance with 3 DBs I'd still end up with each DB having it's own copy of the SP with it's own hard coded definition of which named synonym to use.

I also don't feel dynamic SQL statements would be a good fit. There's more to it than the example snippet above and I use table variables to marshal all the work to be done- so that would be out of scope for all the other statements I need to work with.


texts: SSIS Dataflow Task issue, moving data from SQL Server 2008 to MySQL via ADO.Net

texts:
Can you please help me in getting this problem solved..

I'm trying to build a SSIS package where I'm moving the data from SQL Server 2008 to MySQL.
However I'm getting a weird problem in dataflow task.

It is getting failed when the length of data in a column (varchar) exceeds the length 32chars.. I'm really confused with this message..

SSIS execution results doesn't show any proper message for the failure.

[ADO NET Destination [329]] Error: An exception has occurred during data insertion, the message returned from the provider is:

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component
"ADO NET Destination" (329) failed with error code 0xC020844B while processing input "ADO NET Destination Input" (332).
The identified component returned an error from the ProcessInput method. The
error is specific to the component,but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

Please help...


texts: Postgres replication/replication to server outside firewall

texts:
I am looking for a way to have a postgresql database (running server version 9.1) replicated. I want the mirroring to be able to have one server (the slave) standing outside our firewall. I have been looking at slony and streaming replication - in both these cases, the slave server needs to talk back to the master, for security reasons, I would like that not to be the case. Ideally, I would like the master just to send the changes off to the slave without expecting any answers. are there any systems that act like this?

(Of course, I know that if the slave goes down, or for some other reason is not able to receive the data, the master will not know, but as we have other tools to keep an eye on server problems, this is acceptable)


texts: How can I build a risk matrix in databse tables?

texts:
I'd like to build a risk matrix in a SQL database.  A very basic one looks like so:

------------------------------------
|          |Consequence            |
|Likelihood|Low    |Medium |High   |
|Low       |Level 1|Level 1|Level 2|
|Medium    |Level 1|Level 2|Level 3|
|High      |Level 2|Level 3|Level 4|
------------------------------------

My current ERD looks like this:

I will have a software program that will read from another table (it exists out of frame immediately to the left of that screenshot).  This other table contains values for likelihood and consequence associated with a particular entry in that table.  The program will then query the risk matrix and return a risk level (eg. Having likelihood = Low and consequence = High would return a risk level = Level 1).

As you can see in the diagram, on the risk matrix table I have foreign keys linking likelihoodid and consequenceid to their respective tables.  I also have a composite primary key across likelihoodid and consequenceid.  Currently there is a composite unique key across likelihoodid and consequenceid also because some research I did suggested I needed this, but it seems wrong to me.

Is this design OK, completely wrong, or needs some tweaking?

Edit

DDL as requested:

CREATE TABLE IF NOT EXISTS `likelihoods` (
  `likelihoodid` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `shortname` varchar(50) NOT NULL,
  `longname` tinytext DEFAULT NULL,
  PRIMARY KEY (`likelihoodid`)

CREATE TABLE IF NOT EXISTS `consequences` (
  `consequenceid` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `shortname` varchar(50) NOT NULL,
  `longname` tinytext DEFAULT NULL,
  PRIMARY KEY (`consequenceid`)

CREATE TABLE IF NOT EXISTS `riskmatrix` (
  `likelihoodid` tinyint(3) unsigned NOT NULL,
  `consequenceid` tinyint(3) unsigned NOT NULL,
  `levelid` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`likelihoodid`,`consequenceid`),
  UNIQUE KEY `UK_likelihoodid_consequenceid` (`likelihoodid`,`consequenceid`),
  KEY `FK_riskmatrix_consequences` (`consequenceid`),
  KEY `FK_riskmatrix_risklevels` (`levelid`),
  KEY `FK_riskmatrix_likelihoods` (`likelihoodid`),
  CONSTRAINT `FK_riskmatrix_consequences` FOREIGN KEY (`consequenceid`) REFERENCES `consequences` (`consequenceid`),
  CONSTRAINT `FK_riskmatrix_likelihoods` FOREIGN KEY (`likelihoodid`) REFERENCES `likelihoods` (`likelihoodid`),
  CONSTRAINT `FK_riskmatrix_risklevels` FOREIGN KEY (`levelid`) REFERENCES `risklevels` (`levelid`)


texts: Why can't I create temporary tablespace?

texts:
I'm trying to create dictionary managed tablespace with the TEMPORARY clause and TEMPFILE clause of CREATE TABLESPACE  statement. But oracle says I can't use the DEFAULT STORAGE clause doing that... What is the error here ?

CREATE TABLESPACE myTempTableSpace
TEMPFILE '/home/folder/tempfile.dbf' size 1M
DEFAULT STORAGE
(
INITIAL 1M
PCTINCREASE 0
)
TEMPORARY
EXTENT MANAGEMENT DICTIONARY;

The error is :

  ORA-02180: invalid option for CREATE TABLESPACE


texts: Database design for multiple user attributes

texts:
I am building an app with Laravel, and I have users which also have a profile.

I am unsure about the best way to set up the database for the user attributes.

For example:

user_ud  |   name  |  gender  |  haircolor
   1         josh       1           3

This quick example shows what my table for profiles currently looks like, as the vales are filled in from select boxes.

Is this the best way to map these attributes to values like this?

 id  |  gender
  1      male
  2      female

 id  | hairColor
  1      blonde
  2      brown
  3      black

and so on creating a new table for each attribute type? or is there a better way of doing this?


texts: Getting Error : [Err] 1615 - Prepared statement needs to be re-prepared with MySQL 5.6.30

texts:
Problem/Issue : Getting Error :  [Err] 1615 - Prepared statement needs to be re-prepared

I have a Stored Procedure which contains a Prepared Statement and a view

DROP PROCEDURE IF EXISTS `sampleProc`;

DELIMITER ;;
CREATE DEFINER = `root`@`localhost` PROCEDURE `sampleProc`()
BEGIN

SET @select = "SELECT * FROM `viewSample` ";

  PREPARE stmt FROM @select ;
  EXECUTE stmt ;
  DEALLOCATE PREPARE stmt ;

END  ;;
DELIMITER ;

Following call sometimes gives error CALL sampleProc();

Possible work around / solutions

It appears that the best solution is to increase the value of table_definition_cache , but it seems that it is not working , as it had already been increased from 1400 (default) to 16384. table_open_cache is also been increased to 32162

Variable_name               Value
table_definition_cache      16384
table_open_cache            32162
table_open_cache_instances  4


texts: AWS DMS: 2 million CDCIncomingChanges resolved without target throughput

texts:
We're attempting to use AWS DMS to replicate MS-SQL to a customer's on-prem DB, and running into a confusing situation.  At 13:00 UTC there are over 2 million CDCIncomingChanges, with CDCTargetLatency of 60k.  Somehow, by 14:50 UTC these changes are processed and latency is resolved down to zero while CDCThroughputRowsTarget stays at zero.  It would seem that somehow there are ~2 million incoming changes from the source DB, which are essentially stuck for 60,000 seconds (16 hours), yet they all amount to no rows being changed on the target.

We've increased the capacity of our replication instance and have tried increasing various constraints in our Task Settings.  I've read all the AWS DMS docs including their three-part "Debugging DMS Migrations" series and I've looked through the logs with maximum debug level, yet I still don't get what is happening here.

In our task settings, we have BatchApplyEnabled:true and BatchApplyPreserveTransaction:true, so my only idea is that this could be caused by a long-running transaction.  In general though, I don't know how to go about debugging this, or determining the root cause.  Any help is appreciated.

Task Status Dashboard:

Replication Instance Dashboard:


texts: How to optimize this Select MAX() SQL query?

texts:
In the following select query, it take almost 1 min to return the result, seems the table has over 2 millions record and growing. There are no index i can use from the table. How do I optimize this query?

SELECT MAX(StartDate) AS Expr1
FROM [MYSERVER].MYDB.dbo.ProductActivity AS ProductActivity_1

I already tried with the following, which also takes 1 min to get the result

SELECT TOP(1) *
FROM [MYSERVER].MYDB.dbo.ProductActivity AS ProductActivity_1
ORDER BY StartDate DESC

Is there another way to optimize the query?


texts: A question about multisite Always On design

texts:
Your advice on Always On  design please...

Imagine a scenario where you have 2 datacentres in different countries. You want to be able to run at full capacity in either datacentre. in each datacentre you wish to offload backup and read-only queries to a readable secondary when that datacentre is active.

In an ideal world you would like to have 4 servers in total, two in datacentre 'A' and two in Datacentre 'B', you wish to reduce the amount of traffic across the wan link so would prefer to limit the replication traffic to one stream of asynchronous traffic. You also want to have automatic fail-over in the active datacentre wherever that might be. In effect, you would like a Windows cluster with all 4 servers in it but have it subdivided by groups into two separate groups, one per site, running synchronous commit within the site yet asynchronous commit between the sites due to WAN latency.

Can a readable secondary on the second site act as the source for synchronous commit to anther node on that site?

If all replication has to stem from the primary then I suppose I am left with writing a PowerShell script to set Availability and fail-over modes for the available replicas in the event of a fail-over between sites.

Is there an easy solution that I'm missing?

Cheers


texts: Proactively Determining Bad Plans/Poor Performance at a Query Level

texts:
tldr; I'm looking for ways to monitor server performance to alert when individual, small, fast queries get a bad plan that will in aggregate hurt the performance of the business.

Over the past week we've suddenly had a few stored procedures grab bad query plans, and drag the server to slow downs.  It hasn't been the same query, but it has involved the same tables for one group (which I imagined was a statistics problem), and when we FREEPROCCACHE'd the server another query got a bad plan, and has picked it up at random again.

Being the guy who is held responsible for the server, I'm being asked to do things like ensure no query will ever get a bad plan again, or that I react to it before the users notice the performance degradation.

I have monitoring tools, I'm monitoring long running queries - but this is a case of many queries that run inside 10ms changing to taking 5s, and that's not immediately noticeable, it barely even blips the performance monitoring CPU and memory-wise (apart from more frequent buffer cache dumps perhaps).

For any individual known query I can set up a baseline and an agent job that alerts me, or FREEPROCCACHEs its own plan when it gets a bad plan - but what is my best way to determine this for a busy server?

Should I be baselining run stats for all queries and checking the query stats for all?  Can BlitzCache help me out?  Will this sort of checking be too intrusive on the performance of the server?


texts:
I come from a bash and mysql environment.  I'm trying to get use to mongodb. I see there are different ways to export and import mongodbs.  Does anyone know the bash and mongodb equivalent of

bash> mysqldump -u username -p -h localhost mydatabase> mydatabase.sql

bash> mysql -u username -p -h localhost;

mysql> create database mynewdatabase; grant all privileges on mynewdatabase.* to 'username'@'localhost' identified by 'mypass'; exit;

bash> mysql -u username -p -h localhost mynewdatabase < mydatabase.sql


texts: Query for: view users and permissions (roles) from root security folder (MS SQL)

texts:
On the Internet I found a lot of scripts how to display users and their permissions under a particular database (Security folder under table) - for example sp_helpuser.
Currently, however, I need to use a query to extract users and their permissions from the root of Security folder in Management Studio.

Can you please tell me how to extract users and permissions (roles) from this folder using the script?
Thank you all in advance for your advice.


texts: Why is sp_Blitz executing for more than 5 hours

texts:
Screenshot
I am running sp_Blitz on my SQL Server 2014 with 93 databases. It has been running for more than 5 hours and still won't complete. I am able to run sp_BlitzWho,sp_BlitzIndex,sp_BlitzFirst and sp_Blitzbackup and they all take about 2-3 minutes to complete.

I wonder why is it (sp_Blitz) running too long and still not providing any results.


texts: MYSQL refuses to set password for new user


texts: Open Source tools to visualise MySQL/MariaDB performance metrics (show global status with interval)

texts:
I'm searching for an Open Source tool to visualise MySQL/MariaDB performance metrics (output of show global status in nice graphs).

In MongoDB world there is the Closed Source Ops Manager and the Open Source mtools. I'm searching for something similar.

In official Oracle training for MySQL 5.6 there are diagrams like

While searching the internet I also found some RRDtool based graphs.

So my question is how to generate ad hoc nice visualisations from MariaDB performance metrics? Is there some Open Source project which helps in this task? How do other DBAs do this?


texts: Possible to run multiple Reporting Services (SSRS) instances on different machines with databases on same machine?

texts:
With SQL Server 2012, can SSRS instances be setup on two different machines, with both instances using the same machine for their SQL Server databases?

Machine 1

SQL Server 2012 Database Server
SSRS instance 1

Machine 2

SSRS instance 2 pointed to machine 1's database server

There's this document describing installing multiple instances in 2005, but on the same machine. Can you point me to a better resource for our design? Is this still supported on 2012?  How are the SSRS databases (ReportServer, ReportServerTempDB) created for the 2nd instance?


texts: SQL Server 2019 Performance issue with large INSERT

texts:
We are converting several ETL processes from SQL Server 2016, Windows 2012 server to a Windows 2019, SQL Server 2019 (CU8) environment.
One process is running longer in SQL Server 2019 than it used to in SQL Server 2016. The process performs an INSERT into B, select x from A, where x is a series of substring and case statements.
Table A basically contains 1 data column, 6,000 bytes long. Table B consists of over 950 data columns.  I bulk insert the data from a flat file into table A, then I parse the data into table B using various substring commands.
Table A contains 4.7 million rows and loads in 47 minutes, I split out 1 million rows for testing purposes and this loads in 10 minutes.
On my SQL Server 2016, Windows 2012 server, a full load runs in 12 - 14 minutes, while the 1 million sampling runs in 1 minute 51 seconds.
For grins, I stalled SQL Server 2016 on my ETL 2019 server and ran the same tests. Full load 7 minutes 14 seconds, whereas the 1 million sampling took 1 minute 32 seconds.
All three instances have a MAXDOP of 8 and a Cost Threshold For Parallelism (CTFP) of 50.
This is a dedicated server running SQL Server 2019 for ETL processing. No other apps running on it:

2 - 3.80Ghz processors with 4 cores per socket and 8 logical processors per socket
1571465 MB of RAM (1.5 TeraBytes)
16,813 GB on-board SSD storage
Very controlled testing, no other processes running at the time of tests

While the actual ETL process is procedure based, I pulled the insert code out and I'm running it in SSMS on the local server as T-SQL commands, for testing.
I understand there are a lot of new "performance" improvements in SQL Server 2019 and I've tried a lot of different combinations (different combinations of MAXDOP and CTFP, turning different SCOPE values on and off), so far, nothing has helped reduce the run times.
Some observations:

reducing the target table to only 100 columns, 1 million rows loaded in 1 minute; 4.7 million loaded in 5 minutes
increasing the column count to 250 and loading 1 million rows took 6 minutes.
I cannot put my finger on it, but something around the substring/case function and how its interacting with SQL Server is causing me some grief.
Multiple executions with different MaxDOP (0,4,8) and CTFP, resulted in no difference, except MaxDop=4, which increased run time.

Snippet of code:
INSERT into Base with (TABLOCK)
SELECT
   CHD_A = substring(importtext,14,28)
  ,CHD_B = getdate()
  ,CHD_C = substring(importtext,14,4)
  ,CHD_D = substring(importtext,18,4)
  ,CHD_E = substring(importtext,22,4)
  ,CHD_F = CASE WHEN substring(importtext,45,6) = ' ' THEN NULL ELSE RTRIM(substring(importtext,45,6)) END
 , and so on and so on for 950+ columns.

P.S. Ran a quick test of this same load on a VM server running SQL Server 2017, with 16GB of ram. Query finished in 1 minute 19 seconds, for 1 million rows.
So, I'm reaching out to SQL Server 2019 guru's for advise and where to look, what to tune. Converting to SSIS at this time is not an option.
Here are the plans;
SQL 2016
SQL 2017
SQL 2019


texts: How to improve this query so it'll run quicker

texts:
I've got this query:

    SELECT m.vlanID, v.vlan_name, m.interfaceName, count(m.macAddress) as mac, n.nd_name, p.interfaceDescription, p.interfacePortType
    FROM     macs m, network_devices n, vlans v, ports p
    WHERE    n.nd_ip_address = m.ipAddr
    AND      m.vlanID        = v.vlan_id
    AND      p.vlanID        = m.vlanID
    GROUP BY m.vlanID, v.vlan_name, m.interfaceName, n.nd_name, p.interfaceDescription, p.interfacePortType
    ORDER BY m.vlanID

Each of these tables have approximately 50.000 records. The query is taking north of 30min. Can I improve it to make it more efficient?

EDIT: Table definitions:
macs table
ipAddr, vlanID, macAddress, interfaceName

network_devices table
network_device_id, nd_location, nd_name, nd_ip_address, etc

vlans table
vlan_id, vlan_name

ports table
interfaceName, interfacePortType, nodeIP, interfaceDescription

I've limited to mostly what's required from the query.

There's no indexes configured atm nor execution plan as this is not an application per se. This is just a basic script to be run occasionally for internal purposes only and the data will become obsolete within a few days, so we just need to query temporarily to get some answer while we upgrade our network.

Thanks


texts:
I'm trying to test out the new SS 2012 RC, but I'm having problems with the installation.

My installation stops a ways in saying that the resource needed cannot be found. I'm using the ISO from MSDN and mounting it using MagicDisc. I browse to the location of the missing resource (vs_setup.msi) on the disc and it says that the vs_setup.msi file is not valid for VS 2010 Ultimate. I tried both vs_setup.msi's on the installation iso and both said they weren't valid. Any ideas on what I am doing incorrectly?

I do have VS2010 Ultimate already installed.

The dialogue specifically says:

The feature you are trying to use is on a network resource that is unavailable.

It tried to default to this directory, but the directory doesn't exist on my machine:

C:\Users\[UserID]\AppData\Local\Temp\30319.01\1033\VS_VSTS\

Browsing to the vs_setup.msi in E:\1033_ENU_LP\redist\VisualStudioShell\VSIntSetup and in E:\1033_ENU_LP\redist\VisualStudioShell\VSSetup doesn't work either.

Anyone have any suggestions for what I might be doing wrong?


texts:
I need help with MySQL statement . I need to know how many calls come in every hour. This is what we have so far.

    SELECT
sum(CASE WHEN session.creationtimestamp BETWEEN '2015-12-02 09:00:00' and '2015-12-02 10:00:00' THEN 1 else 0 END) as Hour2,
    sum(CASE WHEN session.creationtimestamp BETWEEN '2015-12-02 00:00:00' and '2015-12-02 23:00:00' THEN 1 else 0 END) as Hour3,
    sum(CASE WHEN session.creationtimestamp BETWEEN '2015-12-02 00:00:00' and '2015-12-02 23:00:00' THEN 1 else 0 END) as Hour4,
    sum(CASE WHEN session.creationtimestamp BETWEEN '2015-12-02 00:00:00' and '2015-12-02 23:00:00' THEN 1 else 0 END) as Hour5,
    sum(CASE WHEN session.creationtimestamp BETWEEN '2015-12-02 00:00:00' and '2015-12-02 23:00:00' THEN 1 else 0 END) as Hour6,
    sum(CASE WHEN session.creationtimestamp BETWEEN '2015-12-02 00:00:00' and '2015-12-02 23:00:00' THEN 1 else 0 END) as Hour7,
    sum(CASE WHEN session.creationtimestamp BETWEEN '2015-12-02 00:00:00' and '2015-12-02 23:00:00' THEN 1 else 0 END) as Hour8,
    sum(CASE WHEN session.creationtimestamp BETWEEN '2015-12-02 00:00:00' and '2015-12-02 23:00:00' THEN 1 else 0 END) as Hour9,
    sum(CASE WHEN session.creationtimestamp BETWEEN '2015-12-02 00:00:00' and '2015-12-02 23:00:00' THEN 1 else 0 END) as Hour10,
    sum(CASE WHEN session.creationtimestamp BETWEEN '2015-12-02 00:00:00' and '2015-12-02 23:00:00' THEN 1 else 0 END) as Hour11,
    sum(CASE WHEN session.creationtimestamp BETWEEN '2015-12-02 00:00:00' and '2015-12-02 23:00:00' THEN 1 else 0 END) as Hour12,
    sum(CASE WHEN session.creationtimestamp BETWEEN '2015-12-02 00:00:00' and '2015-12-02 23:00:00' THEN 1 else 0 END) as Hour13
    FROM session
    Inner Join mxuser
    ON session.extensionid2=mxuser.extensionid
    WHERE
    mxuser.userprofilename = 'Team1'
    And session.legtype1 = 2
    AND session.Legtype2 = 1
    or session.Legtype2= 2


texts: Where and with what should I be looking for a good hardware backup tool?

texts:
Until now, I was doing my SQL Backups using USB Hard disk, and copying that to my local computer. After that, I went zipping and burning onto DVDs.

Now I want to push that on more professional and more robust level.

What kind of hardware is best for storing and backuping SQL databases ?

My environment looks like:

two MS-SQL-Server 2008, One production database and other replicated for reporting
My apps is not mission critical and I can afford my self down time even and for 20 hours.
My main database is around 32GB when is in backup, others is around 10GB.
I think that I do not need large and long historical data, two or tree snapshots should be OK for me.

Here are further questions:

Should I looking for hardware with TAPES?
Does NAS (network attached storage) provide a good solution ?
If I go with NAS beside RAID, to what should I pay more attention?
Which protocol is essential for NAS so SQL can do backups easily?
Today's SHOHO NAS servers is pretty cheap and available, Should I avoid them ?

Any other suggestions is much appreciated.


texts: Multiple tables for different payment transaction

texts:
I'm building an application that users can pay some fees to sign up for a yearly membership and courses they're interested in.

Currently, I'm planning to have two different tables: 'course_payment' and 'membership_payment'. The reason behind this is that they have very different fields although they share some same information.

For example, course_payment table will have course title, regular price, special price, # of accompanying guests, guest price... and etc. membership_payment table will have the name of membership, membership start date and expiry date, and etc...

Well, they have some common fields such as user_id(payer), subtotal, tax, tax_type and total. However, normalizing tables for payment transaction history seems a bit too much and cumbersome unless there's a very good reason for it.

Do you think I should have two different payment history tables for each? If storing them all in one table is a better approach, what should I do with all the info that are only needed for one or the other such as course name and membership name?

Thanks!


texts:
I have noticed that even on huge tables with millions of records when I do a CREATE INDEX the index is created immediately (I get the console back in nsecs).
Why is this? Doesn't it create the B-Tree with the table data at that point? I can only assume not due to the immediate return of the CREATE INDEX.
But then how is the index created? On each access?


texts: Local MySQL instance running extremely slow compared to remote version

texts:
I'm working on a local project and it have a remote version running normally and I did imported all data from external DB to local instance. When I run a query with a specific FUNCTION, it run too slow, but too slow, that never finish (I wait about 10 minutes, and nothing), but on the remote server, it does very fast, less than 1 second.

I checked and copied a lot of mysql configs and saved to my.ini from the remote MySQL instance. Nothing changed. You can see the difference that remained here. Basically, system things, directories, bits difference, etc. The uniques differences that I noted and I can't change is the compiled version (local is 32 bits, remove is 64 bits), MySQL version (local is 5.6.16, remove is 5.6.21).

This problem starts to occur after I create a MySQL FUNCTION that return a "better version" of SOUNDEX native function. It's very simple, and run fine remotely (the same version of function). Basically get a string and return the SOUNDEX for each word, for instance, SOUND_EX("John Doe") returns J530 only. My SOUNDEX_SPLITTED("John Doe", ",") will return J500,D000. With correct usage, it allow I get the name John Server Doe searching "incorrectly" by Jon Doeh.

CREATE DEFINER=`root`@`localhost` FUNCTION `SOUNDEX_SPLITTED`
        (`strValue` TINYTEXT, `strSeparator` TINYTEXT)
    RETURNS tinytext CHARSET latin1
    LANGUAGE SQL
    DETERMINISTIC
    NO SQL
    SQL SECURITY INVOKER
    COMMENT 'Executa um SOUNDEX separando por espaços.'
BEGIN
    DECLARE spaceFirst INT;
    DECLARE spaceLast INT DEFAULT 0;
    DECLARE strLength INT DEFAULT LENGTH(strValue);
    DECLARE soundexPart TINYTEXT;
    DECLARE soundexValue TINYTEXT;

    IF strValue RLIKE "[0-9]" THEN
        RETURN NULL;
    END IF;

    SET strValue = CONCAT(strValue, " ");
    IF strSeparator IS NULL THEN
        SET strSeparator = " ";
    END IF;

    STR_LOOP:
    WHILE spaceLast < strLength DO
        SET spaceFirst = spaceLast + 1;
        SET spaceLast = LOCATE(" ", strValue, spaceFirst);
        SET soundexPart = SUBSTRING(strValue, spaceFirst, spaceLast - spaceFirst);

        IF LENGTH(soundexPart) <= 2 THEN
            ITERATE STR_LOOP;
        END IF;

        SET soundexValue = CONCAT_WS(strSeparator,
                                     soundexValue,
                                     SOUNDEX(soundexPart));
    END WHILE;

    RETURN TRIM(soundexValue);
END

If I cancel this function to running completly (like a RETURN SOUNDEX(...) imediatly after declarations), the full query back to work again.


texts: Memory usage increases whenever I run stored procedures

texts:
I want to know why SQL Server memory use increases whenever I run stored procedures.

After I stop and restart SQL Server, the memory used is just 1 GB.

Each time I run stored procedures, the memory use increases, and has now reached 8GB.

I checked temporary tables, there are just a few.

I don't know why and how to reduce SQL Server memory use.

I'm using SQL Server 2012 under Windows 8.


texts: Unsupported languages in Postgres Full text search

texts:
My web application supports 3 languages, English, Traditional Chinese, Vietnamese. I understand that Traditional Chinese and Vietnamese is not supported in Postgres' Full Text Search.

If I were to go ahead and implement Full Text Search, what will happen if a text search is performed using an unsupported language?


texts: Best indexing strategy for query that searches between two bigint columns

texts:
I have the following table in MS-SQL:

CREATE TABLE [dbo].[dbip_locations](
    [ip_from] [bigint] NOT NULL,
    [ip_to] [bigint] NOT NULL,
    [country_code] [nvarchar](64) NOT NULL,
    [region_name] [nvarchar](128) NOT NULL,
    [city_name] [nvarchar](128) NOT NULL,
    [latitude] [float] NOT NULL,
    [longitude] [float] NOT NULL
)

The ip_from and ip_to columns are calculated from ipv4 addresses as such:

return (
      convert(bigint, parsename(@ip, 1)) +
      convert(bigint, parsename(@ip, 2)) * convert(bigint, 256) +
      convert(bigint, parsename(@ip, 3)) * convert(bigint, 65536) +
      convert(bigint, parsename(@ip, 4)) * convert(bigint, 16777216)
    )

I am then using an ipv4 address converted to a bigint using the calculation above to search for the row in which the ip address falls between the ip_from and ip_to columns.

I'll always find one row, while it is not enforced by the schema this is the reality in the data.

Here is the query:

SELECT TOP(1) [latitude], [longitude] FROM [dbo].[dbip_locations] WHERE @ip_int BETWEEN ip_from AND ip_to

I currently have two non-clustered, non-unique indexes on both the ip_from and ip_to columns.  The query is executing pretty quickly, but I am performing a huge number of these queries per second and would like to know if I could be getting better performance through the usage of different indexes?  Perhaps a clustered multi-column index or by using unique indexes?

Are there any better indexes I could be using?


texts: Query performance diffrence between equals and between

texts:
I need to know why the correct answer is D ? what is the diffrence between D and A.

Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores the date and time each order is placed.
There is a non-clustered index on the OrderTime column.
The business team wants a report that displays the total number of orders placed on the current day.

You need to write a query that will return the correct results in the most efficient manner.
Which Transact-SQL query should you use?

A. SELECT COUNT(*) FROM SalesOrders WHERE OrderTime = CONVERT(DATE, GETDATE())
B. SELECT COUNT(*) FROM SalesOrders WHERE OrderTime = GETDATE()
C. SELECT COUNT(*) FROM SalesOrders WHERE CONVERT(VARCHAR, OrderTime, 112) = CONVERT(VARCHAR, GETDATE(), 112))
D. SELECT COUNT(*) FROM SalesOrders WHERE OrderTime >= CONVERT(DATE, GETDATE()) AND OrderTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

Correct Answer: D


texts:
In the question Need Help Troubleshooting Sql Server 2005 Deadlock Scenario there was a suggestion of using DBCC TRACEON (1204, -1) to trace deadlocks globally.

When reading about this command in BOL it states that it should only be used  while users or applications are not concurrently running statements on the system. Does it mean that we must be in singleuser-mode when enabling this trace-flag? And in addition, why is it required and is it important to follow the advice? (Just seems a bit hard to follow in a production system that is always running.)


texts:
I'm building an app which will have accounts for organisations leading to organisation.myapp.com subdomains for their account access.

I'm putting together the schema for it and I have a few questions.

This is a simplified overview.

An organisation has users
An organisation has clients
A client has contacts
A client has projects
A project has an estimate

Some initial thoughts

The clients table and the users tables will have organisation_id as a
foreign key.
Contacts and Projects will have client_id as a foreign key
Projects will have estimate_id as a foreign key

However, is there an argument that organisation_id should also be a foreign key in Estimates, Contacts and Projects?.

For example, to make sure that only members of the relevant organisation can view Estimates I need to perform a query each time to make sure that the authorised user belongs to the organisation that owns the estimate. I can do this via a join but I wonder if this is unnecessary overhead. On the other hand, repeating organisation_id across pretty much every table seems to go against denormalization.

Is there a best practice I should be following here?

If it's of any importance this is for a MySQL DB with all the tables running InnoDB.

Thanks.


texts: Rebuilding indexes for a partitioned table having 300 partitions

texts:
Scenario

Partitioned table is empty and I am loading data for 1 partition which has 180k rows. I disabled the indexes and loaded the data and rebuilt the indexes after data is loaded.

Issue

While examining the query plan of rebuilt indexes, I can see the 'Estimated number of rows' is 180k but 'Actual number of rows' is 300 partitions * 180,000 rows = 54 Million rows, even though I am loading data for only one partition.

Can you shed some light on this behavior and how to overcome this issue?

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) Mar 18 2018 09:11:49 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: )


texts: MariaDB ran mysql_secure_installation, but seeing remote login attemps in logs

texts:
I installed MariaDB and ran mysql_secure_installation and answered yes to all questions to remove anonymous user, test DB, remote login, etc.

However, I recently had MariaDB crash and in checking the logs I found numerous login attempts of the form:

  [Warning] IP address '218.60.8.253' could not be resolved: Name or
  service not known

Is this normal?  To me it indicates that the server is indeed accepting remote connections.

I checked /etc/my.cnf, and did not see the expected bind-address=127.0.0.1 directive shutting down remote login.  I have added it manually and restarted the server, but now I am wondering if the mysql_secure_installation did what it was supposed to do.

Is there a way to check that mysql_secure_installation ran correctly?


texts:
I need help selecting data where the date is in the previous 3 months. (note: I'm not asking for the last 3 months i.e. DATEADD(MONTH, -3, GETDATE()) )

The reason I don't want to use the DATEADD method is that this query could be run in the middle of the month and I don't want it to return data leading up til that point


texts: Insert script adds N before each varchar data

texts:
I exported results from a query in order to import it into a table on another DB. I chose the xlsx output format and verified that the data had gotten written to the excel file. While trying to import it into the table, I chose to create an insert script so that I could go over the final data before importing it. On doing this, I noticed that all the varchar fields have a N character right before the opening quote, making it look like:

INSERT INTO ... ()
VALUES(N'...', 0.0, N'....')

Any ideas on why this might be happening and how I can avoid it?


texts: Database email to know the history of backups

texts:
I need to send an email by listing different types of backups, time of backups, summary of these backups, backup file path and name of backup with the help of database email. Can anyone help me with a script for the same.

Thanks in advance.


texts:
Apologies in advance if I am asking in the wrong forum.
I am trying to get the first attribute value for each ID in my table based on the ID's starting week. When using the min() function on weekkey, my query is returning more than one value if an ID has more than one attribute.
Drop table if Exists table1

CREATE TABLE table1 (
  IDNumber INT NOT NULL,
  Attribute VARCHAR(30) NOT NULL,
  WeekKey INT NOT NULL,
  );

INSERT INTO table1
    (IDNumber, Attribute, WeekKey)
VALUES
    (1,'a',1),
    (1,'a',2),
    (1,'b',3),
    (2,'c',1),
    (3,'a',1),
    (3,'c',2);

Select IDNumber, Attribute, Min(WeekKey) StartWeek
From Table1
Group by IDNumber, Attribute
Order by IDNumber, Attribute

My goal output would be:
1  a  1
2  c  1
3  a  1

My current output is:
1  a  1
1  b  3
2  c  1
3  a  1
3  c  2

Does anyone have an idea how to accomplish my goal without too many extra steps?
Thanks,
Taylor


texts: SQL Server TDE - add master key and certificate

texts:
I want to add multiple master database encryption keys and certificates.

Can I do that?


texts: maximum server memory setting in SQL Server

texts:
Just had a memory issue on a 64GB of RAM machine with SQL Server 2012 SP2. 3 running db engine instances, each limited to 16GB (16384). But noticed in Task manager that sqlservr.exe was at over 30GB memory for one of them.

What is the cause ? And is there any way to see what's eating up the RAM, next time it happens ?
Can anyone explain if there's any way to limit the memory used by one instance, besides the "Maximum server memory" setting ?


texts: Why is array_agg() slower than the non-aggregate ARRAY() constructor?

texts:
I was just reviewing some old code written for pre-8.4 PostgreSQL, and I saw something really nifty. I remember having a custom function do some of this back in the day, but I forgot what pre-array_agg() looked like. For review, modern aggregation is written like this.

SELECT array_agg(x ORDER BY x DESC) FROM foobar;

However, once upon a time, it was written like this,

SELECT ARRAY(SELECT x FROM foobar ORDER BY x DESC);

So, I tried it with some test data..

CREATE TEMP TABLE foobar AS
SELECT * FROM generate_series(1,1e7)
  AS t(x);

The results were surprising.. The #OldSchoolCool way was massively faster: a 25% speedup. Moreover, simplifying it without the ORDER, showed the same slowness.

# EXPLAIN ANALYZE SELECT ARRAY(SELECT x FROM foobar);
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Result  (cost=104425.28..104425.29 rows=1 width=0) (actual time=1665.948..1665.949 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Seq Scan on foobar  (cost=0.00..104425.28 rows=6017728 width=32) (actual time=0.032..716.793 rows=10000000 loops=1)
 Planning time: 0.068 ms
 Execution time: 1671.482 ms
(5 rows)

test=# EXPLAIN ANALYZE SELECT array_agg(x) FROM foobar;
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=119469.60..119469.61 rows=1 width=32) (actual time=2155.154..2155.154 rows=1 loops=1)
   ->  Seq Scan on foobar  (cost=0.00..104425.28 rows=6017728 width=32) (actual time=0.031..717.831 rows=10000000 loops=1)
 Planning time: 0.054 ms
 Execution time: 2174.753 ms
(4 rows)

So, what's going on here. Why is array_agg, an internal function so much slower than the planner's SQL voodoo?

Using "PostgreSQL 9.5.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 6.2.0-5ubuntu12) 6.2.0 20161005, 64-bit"


texts: Where to put standby control file in Physical standby database?

texts:
I am creating a physical standby database for which I am creating a standby control file using command = ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/oracle/dbs/stbycf.ctl';

but It creates only one control file while I using 4 control files like:

O:/cntrl_ims01/CNTRLIMS.ctl, O:/cntrl_ims02/CNTRLIMS.ctl, L:/cntrl_ims03/CNTRLIMS.ctl, L:/cntrl_ims04/CNTRLIMS.ctl

, on standby host I am using same paths and structure as primary host, so where to place this single control file. ??


texts: SSIS Package Execution From Visual Studio Unit Test

texts:
Hello I am attempting to run a test which contains the following code:

        string pkgLocation;
        Package pkg;
        Application app;
        DTSExecResult pkgResults;

        pkgLocation =
          @"C:\Users\mYCoMppp\Documents\Visual Studio 2012\Projects\packageOnExeCreatesTable" +
          @"\Integration Services Project8\Package.dtsx";
        app = new Application();
        pkg = app.LoadPackage(pkgLocation, null);
        pkgResults = pkg.Execute();

        Console.WriteLine(pkgResults.ToString());
        Console.ReadKey();

This is an attempt to populate a table which has had all of its records removed thanks to the test I ran before it. I am not getting any errors - however it does take a while to complete the test. I do not know exactly how long it takes, because the test is running as I type this.

So alas, my question. I would like to know if anybody out there can explain to me if this is a time consuming way of executing a package. And also if anybody knows of any other way to execute SSIS packages faster.


texts: Running MySQL in virtualbox with data dir on shared folder sometimes results in data corruption

texts:
I'm trying to move our development environment onto a vagrant box (virtual machine using Oracle virtualbox). Currently I'm stuck on one problem:

The environment needs a MySQL server and some databases. I've got all the scripts set up that install MySQL, create and populate the databases, etc. That works.

Since the developers are likely to set up further data of their own, my idea was to put the MySQL data folder in a shared virtualbox folder. This way when the vagrant machine is destroyed/recreated, the data folder remains and can be reused without loss of data.

However sometimes (but not always, I don't know what triggers it) when I simply suspend and then resume the virtual machine, MySQL starts complaining that a lot of its tables (but not all) are corrupted. Fixing the tables results in all data in them being lost. Obviously this is unacceptable.

Does anyone have any idea why this happens and how to prevent it?

A few possible clues or red herrings - since I read that virtualbox's shared folders are slow, I've increased the InnoDB buffer pool size to 1GB, so that it has a lot of cache space. We do use InnoDB and MyISAM in proportions of about 50/50. I haven't yet checked if the failed tables all belong to the same engine - I'll pay attention to it when it next crashes.

Update

Ha, I managed to catch it red handed! It's actually... suspense... BOTH InnoDB and MyISAM.

Selecting from a MyISAM table gives the error

  Table 'xxx' is marked as crashed and should be repaired

Trying to run mysqlcheck -A -a (analyze all tables) actually CRASHES MySQL. The log files tell the story (edited for clarity):

[Warning] InnoDB: Retry attempts for writing partial data failed.
[ERROR] InnoDB: Write to file ./ib_logfile0failed at offset 29885952, 1024 bytes should have been written, only 0 were written. Operating system error number 71. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
[ERROR] InnoDB: Error number 71 means 'Protocol error'
[ERROR] mysqld got signal 6
Query (0x7fbc12501b50): ANALYZE TABLE `yyy`

And then, in a moment, systemctl restarts the service and all runs perfectly again.

My educated guess is that suspending down the virtual machine releases the locks that virtualbox was holding on opened files, and when virtualbox starts up again, it doesn't re-acquire them. So from MySQL's point of view, it's keeping a file opened, but virtualbox has lost that grip, so when MySQL tries to access the file again, it gets an error.


texts: Partial rollback in function in postgres 9.6

texts:
Pseudo code

INSERT INTO table_a
INSERT INTO table_b
RAISE EXCEPTION
UPDATE table_b

Is there a way to keep the inserted table_a tuples but not the table_b tuples when this code exists within a function?


texts: what is causing arithmetic overflow in the query below?

texts:
when I run the following query on MY_Database

select * from sys.sysfiles

I get the following results:

But when I run a dynamic query that gets the percent of free space I get:

  Msg 8115, Level 16, State 7, Line 93 Arithmetic overflow error
  converting numeric to data type numeric.

DECLARE @command NVARCHAR(MAX)
SELECT @command = 'SELECT  db_name() as db_name,
CAST(S.size/128.0 - CAST(FILEPROPERTY(S.name, ' + '''' +
               'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB
        ,CAST(100 * (CAST (((S.size/128.0 -CAST(FILEPROPERTY(S.name,
        ' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(S.size/128.0))
        AS decimal(5,2))) AS varchar(8)) + ' + '''' +  '''' + ' AS FreeSpacePct
        FROM sys.sysfiles S'

exec sp_executesql @statement = @command

I am struggling to find the reason of the arithmetic overflow.
why is it happening?

  Why divide by 128? It is because both sys.sysfiles and FILEPROPERTY give the number of 8K
  pages, not MB, and to convert from 8K pages to MB you divide by 128
  as it is explained here

Why is it dynamic?

Because I actually get the values from each database using sp_ForEachDB as you can see on the example below:

DECLARE @command VARCHAR(5000)
SELECT @command = 'Use ' + '?' + ' SELECT  db_name() as db_name,
CAST(S.size/128.0 - CAST(FILEPROPERTY(S.name, ' + '''' +
               'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB
        --,CAST(100 * (CAST (((S.size/128.0 -CAST(FILEPROPERTY(S.name,
        --' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(S.size/128.0))
        --AS decimal(4,2))) AS varchar(8)) + ' + '''' +  '''' + ' AS FreeSpacePct
        FROM dbo.sysfiles S'

EXEC sp_ForEachDB @command


texts: How to retrieve data of two columns from two tables?

texts:
I have two Table MATCH_MASTER and TEAM_MASTER

MATCH_MASTER

|---------------------------|
|M_ID | TEAM1_ID | TEAM2_ID |
|---------------------------|
|  1  |     1    |     2    |

TEAM_MASTER

|T_ID | T_NAME |
|--------------|
|  1  |    A   |
|  2  |    B   |

I want to select M_ID, team1 name and team2 name.

My query result should look like this:

|------------------------|
|M_ID | t1_Name | t2Name |
|------------------------|
|  1  |    A    |   B    |

How can I achieve this?


texts: Does it make sense to use SQL Server's bracket notation in hand written code?

texts:
Code generators tend to be simpler when they generate output using the new Microsoft bracket notation ([]) for nearly everything.

When I first saw it, I though wow a reincarnation of the somewhat banned quoted identifier notation.

As far as I know it is a proprietary extension from Microsoft (meaning Oracle doesn't support it).

Looking at SQL Server there is no difference if you define a table like

CREATE TABLE [dbo].[Table_2] ([col1] [int], [col2] [int]);

or

CREATE TABLE dbo.Table_2 (col1 int, col2 int);

Its a matter of personal or corporate style. Be consistent.

Now if you want to migrate your database to Oracle, brackets are no option.

You can use the old quoted identifiers, but these are case sensitive which causes a lot of trouble.

Is it a good idea to remove all brackets from generated code, avoid using blanks, other special characters and reserved keywords for names and just code in a way that most DBMS understand?


texts: Storing Metadata/Attributes for Assets with Common and Varying Information

texts:
I'm looking for some advice on database design.

I'm working on a small asset management utility for learning and to simplify some tasks I do in tracking IT assets. I have a table for "Asset" which represents a physical asset and then "AssetTypes" which represent the different types (e.g. Phone, Computer, Laptop, Server). Each of the assets has some common information (Name, Make, Model, Value, Purchase date) and then unique values based on the Asset Type.

What is the best way to store this extra information (e.g. PC may have CPU, RAM, and HDD; a laptop would have those three attributes, plus ScreenSize; and a monitor would just have ScreenSize)?

I see two ways to achieve, one is a separate table for each Asset Type, which means every-time I add an asset, I need to update my code. The other is to use an "Attributes" (or similar) table that has for each asset a list of the Extra columns. When you add an asset of Type X, it gives you all the additional attributes to fill in.

If I use the attributes, how should I define them and the data types they can use (e.g. Text, Int, Bool)?

I plan on using MS SQL Server as my DB, with a C#.NET front end.

Happy with any advice or thoughts.


texts: Oracle Database: View schema without access to data

texts:
In Oracle Database 10g, I need to create a user with these permissions:

user can access schema to be able to read table names and columns
user can not select (get) any data from any table

Is this possible or is there a way for a workaround?


texts: Installing Full Text Search to SQL Server 2008 R2

texts: I installed sql server 2008 R2 Express successfully, then realised I need to get Full Text Search. So I downloaded the "Advanced Services Installation Package", but when I run it there is no option in the "Feature Selection" part for Full Text Search. Please don't tell me I hav to uninstall and reinstall?


texts: How can I restore my files and photos from binary files?