Functional dependencies applies to whole database or to a particular relation? I am new to dbms. I saw a lecture video about functional dependencies. But I am confused a bit that whether functional dependency is a constraint put on whole database or on a particular schema?

A functional dependency is exactly what the term implies - the output of the function is always determined by the input.  If for example we have a function f(), and provide variable x, and we always receive output y, then y is functionally dependent on x.  You can think of this like a simple graphing function
2x + 1 = y
Plugging some sample values into the function we get:

X    Y
------
1    3
2    5
3    7

and so on.  Thus we know that for every value of x, there is value of y that will always be that value of y for that x.

Codd adapted this to data management with respect to determining if one data element's value always results in a known value of a second data element as if it were the input and output of a function.  So for example, if we find that each employee of a small company is given an employee number, and we create a simple list of employee numbers and names, we find that the name is functionally dependent on that employee number.  Every time we see the employee number 7 for example, we see the name "Jim Brown."  This represents in the database the proposition that is true in the real world - namely that employee number 7 is the person named "Jim Brown" that we can point to and say "that's him."

Functional dependency can be implemented as a unique constraint because there is a one to one relationship between the input to the function and the expected output.  In the algebra example, when we plug 2 into the equation 2x + 1 we will always get 5.  Applying that example to data management in the example above, each time we have the employee number 7 we need to have one and only one name and other characteristics that goes with it.  We can't have employee number 7 associated with "Jim Brown" and "Bob Jones" anymore than we could say that plugging 7 into 2x + 1 can yield 15 and 25.  This application of simple math to data management is the fundamental underpinning that allows the DBMS to be told how to protect data integrity without having to understand the semantics of the data.

Functional dependencies are by definition implemented when a unique constraint is placed on each table identifying each set of data elements that have a one to one relationship with another set of data elements and whose values always vary with that first set.  The notation is typically:

A,B ---> C

This means that the value of A and B determines the value of C.  Perhaps A is employee number and B is Dependent Number, and C is the dependent's name.  So in this sense they apply to each and every table in each and every schema in each and every database.

Some good references on relational theory are Fabian Pascal's Practical Database Foundation Series and Chris Date's book Relational Theory for Computer Professionals.


Automatically reject certain expensive query statements I have some clients which sometimes run some "not allowed" very expensive queries.
I've tracked them down using Extended Events.
Is it possible to reject these queries automatically? I know the SQL statement so would be able to catch them using the "sql_text" event in EE.
But I haven't found a way to perform any actions in EE when e.g. a particular event is happening.
Or is there another way around this?

The main options are the query governor cost limit and Resource Governor.
The former is a bit limited because it applies to all queries and limits them based on estimated cost, which can be very inaccurate.
Further, SQL Server Management Studio issues SET QUERY_GOVERNOR_COST_LIMIT 0 when connecting (with SSMS options set to default). This overrides the instance-level setting and so renders this option pretty ineffective.
The better option is Resource Governor (if it is available to you) when combined with:
Update adds CPU timeout setting to Resource Governor workgroup REQUEST_MAX_CPU_TIME_SEC in SQL Server 2014, 2016 and 2017

When Trace Flag (TF) 2422 is enabled, and if a request exceeds the maximum time set by REQUEST_MAX_CPU_TIME_SEC, then the request is aborted. Additionally, you may receive an error message 10961 that resembles the following:

The request has been aborted because its CPU usage exceeds the maximum configured limit for the workload group.

It isn't possible to manage this with Extended Events.
Your other options were described by Dominique Boucher:

Why not simply talk to those people who are running those query and told them not to? [...] Remove access from those who run the queries after you told them not to and that should do it. Otherwise, [...] you could have a job that run all the time and check running queries and kill the one you don't what to have running. They will still start to execute but technically, the end user will get errors all the time so they may eventually give up.

Erik Darling had a similar suggestion:

If I knew the query text ahead of time, I'd probably just set up a while loop in an Agent Job to find when those queries are executing, collect the SPID, and kill the session.


Basic question: Simplest way to share company databases My company is looking to access large quantities of data from another company we have good relations with. Currently we are using a web interface to interrogate their information, however it is quite limited in its functionality (set reports), and the available data is vast. At the moment we have limited technology resources to develop ways of mining this data, and though I am pretty clued up on basic database construction, this is something totally new to me.

If the data was small enough to be manageable, I could request a CSV file to be sent to an FTP server, pull it into the software we have (Access, Tableau) and query it from there. However, there's going to be loads of it.

So what are my options here?

From the little I know, the simplest thing would be to query source data. Are there any tools that would do that outside them using another API?

Similarly in my head I could foresee a way to send new data to a SQL server somewhere, and then query/link to it.

Is this possible?
Is this how SQL Server works?
How much effort would this take on their part?

I am not looking for a full schematic on how to do this (though I wouldn't turn one down), but just some clear terms and tools I can research on my own would be enough.

Essentially, we are meeting with the company to discuss how best we would do this. They currently have a limited UI for customers, but are interested in expanding. We have very little database infrastructure but are also eager to expand and looking for tools/products that will help us. We don't have SQL Server at the moment - we don't hold much data here, hence why we rely on querying third party data. So without them building a new interface for customers, how would one go about sharing this data considering the volume is considerable?

((Update re data size: Apparently we're looking in the region of 1-2 million rows a day with, probably, about 50 fields (Mostly short number and text < 50)....I don't know exactly what that translates into in terms of Mb/day, but suspect it's going to be too much to handle over email on anything over a daily basis))

It's not really how SQL Server works and I think you're going to see some underwhelming real life implementations of the data distribution process.

Many data publishers (post offices, automotive and parts suppliers) will publish a set data format document along with full monthly data extracts usually in a CSV or similar text format followed by ongoing updates-only extracts. The duty of pulling updates over SFTP, creating a database schema, loading it, and updating it becomes the customer's problem.
Usually a customer will then allocate a unique ID to each table which they'll use for their own referential integrity and to seperate it from the key used by the provider; as providers often delete or reallocate records (even though we know they shouldn't), and you can't have a record of a part sale disappear from someone's invoice just because that part is no longer supplied or something.

Some inter (or intra) company transfers are done literally as entire database backups. In this case you could have a weekly full backup and daily differential backups transferred over SFTP and restored on each client. This requires an outage window on your side to do each restore though.

There are other options. SQL Server Replication (either snapshots, but maybe not daily snapshots because these can't be incremental, or transactional which is an ongoing stream of changes) but it's mostly avoided between companies because they don't want to expose SQL Server to the internet; and it fails too often and requires too much knowledge to troubleshoot and keep going on the client side.

Some data publishers use APIs but only when you're sending a request off; i.e. send a part number get back a part record. It's not usually used to publish complete data sets. And it requires programmers, there's no native SQL interface for it anymore.

I think 1 is the standard way to go. If they want to go the extra mile they can provide a database schema and routines to do everything (pull an initial data stream, then apply changes every day). It's not difficult but it's not trivial either.
2 is the easiest by comparison but also the ugliest.
They can do something in between. Provide a full backup, and then BCP export only new records each day, and provide a script to load these new records into the database. But records do change and get deleted, despite best laid plans, and then those become problems to deal with in this way.
As for your comment on not using SQL Server currently, the free edition (Express) can hold 10GB of data, which is really quite a lot when you get down to it.


Restoring to specific point in time in SQL Server EDIT: The problem turned out to be that while backups are made every night, I was trying to restore to a point in time that had not yet been backed up.  I would have realized this sooner if I were scripting the restore, but because I was using the GUI it simply chose the most recent log file and didn't bother to notify me that the chosen log file didn't actually contain the point in time I was specifying.

After I made a current backup of the db and transaction log then it worked perfectly.

Having said that, I still don't understand how the record with time of '2013-11-27 12:52:08.240' was restored in the scenario described below.  But for now I'll just chalk that up to being something screwy with the application recording an incorrect time.

I am once again befuddled by SQL Server's (in?)ability to restore to a specific point in time.

Sometimes when I've tried this in the past it would seem that SQL Server restored to a point in time near where I asked it to but not quite where I specified.  I chalked this up to being a misunderstanding on my end since I know the dates recorded in the database originate in the application and therefore may vary ever so slightly from the time that the records are actually written.  The variances were actually a little more than 'slight', but this is still the best explanation I could come up with.

Around 12:55pm today I restored a database back 30 minutes to 12:25pm.  Now when I do a query on the log and sort by date/time I see a record with a date of '2013-11-26 18:21:49.200' followed immediately by a record with a date of '2013-11-27 12:52:08.240' right next to each other.

So my first observation is that all of the records from the beginning of today up to the time I restored to (12:25pm) are not there.  My second observation is that there is a single record at 12:52pm which is 27 minutes AFTER the time I tried to restore to (way, way to large for a slight variance in time between when the application picked a time and when the record actually got written).

Has anyone else run into this?  I have no clue why this would be happening and would appreciate any help in figuring out how to backup my databases in a way that they will properly be restored to a point in time when I need to since this is the whole reason I switched to using the Full Recovery model for backups.

The quick answer here is: What you are describing/insinuating is kind of a big deal, and I've not experienced this and this is not the default or expected behavior of SQL Server.

The ability to restore is a fundamental function of an RDBMS. The ability to restore to a point in time is a fundamental part of that fundamental function. And the underpinnings of this ability is the logging mechanism of a Relational Database Management System.

This is not only what gives us the ability to restore to a point in time, but the ability to recover to a consistent state during a crash or restart. It is the same transaction log that is used there.

In a nutshell (there are lot of resources that describe the process. This is a good one to start) - SQL Server uses a write-ahead-logging mechanism. This means everything is written to your transaction log and confirmed to be written there before that transaction is considered durable and your transaction can be considered done. If this didn't work, we could never trust the reliability of SQL Server and the data in your app, because data could become inconsistent with IO issues, memory issues, a restart, etc. We'd have no guarantee of maintaining the ACID properties of the database anymore.

So with that caveat in mind there is one of three things going on here:

An Error/Fault during the restore - I am guessing that is not the case here. Why? Because you were able to restore and query the database after your restore and you are not dealing with a corrupt database or restore failures.
A Bug - I am not saying this isn't possible, but I am skeptical that this is the case. Especially with your comment update indicating some uncertainty about what should be in the application log you are looking at.
A Misunderstanding - This is the most likely guess of what you are experiencing. You can confirm this a bit by doing the same process to a dev server or test server. Either the wrong database could be being restored, the table you are querying is not showing you the data you are expecting to see and not logging the data correctly or there weren't the transactions you were expecting to see.

You actually aren't doing transaction log backups/restores - I suppose there could also be some confusion in what you are doing. To be clear - to restore to a point in time you have to restore a Full backup with NORECOVERY, then apply the necessary transaction logs between your last full backup and the time you want to restore to, specifying a WITH STOPAT on the final transaction log restore and specifying WITH RECOVERY on that final one.. Also, if the time you want to go back to is after your last transaction log backup, you need to first backup the tail of the log one last time to be able to restore to that point in time. (This article talks about how to restore transaction log backups. This one talks about backing up the tail of the log.)

(**note:**I would also confirm time zone settings, the time zone on the server where the backups were taken is the time zone used in your restore as this question got into. So if you are restoring from one server to the other - check your time zones and all. )

One simple way you can prove this out?

Query the table you are looking at for logging after normal activity for a bit. Do you see what you are expecting to see based on your understanding for the activity today? Okay good, go through your normal restore process or the one indicated in the documentation linked to above if you've done something different. Do this restore to another machine/dev machine so you aren't affecting your primary server! Restore to the time you ran your query in this table. Look at your table - if you did the steps above correctly you should see that the data looks as it should. If it doesn't, I would first look at your process, re-read the instructions, confirm the database, confirm the time zones and times on the two servers, and if something is still off and you've totally ruled out something missing in the process? I would open a call immediately with Microsoft, but I'd honestly be surprised if you were experiencing a bug or issue.


PLS_INTEGER giving Error My procedure giving numeric overflow error:

ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at "HDM_DBUSER2.HDM_CLEANUP_UTIL", line 1196

because it exceeds pls_integer maximum value +2,147,483,647. How to overcome this error?
I have to purge 2234202989 rows. So I am getting error.

sp_log_message (LOG_ERROR, 'sp_instrumentationdata', lsCurrentTable, 'Exception', NULL, SQLERRM);

Here it is showing .. This table having starttime and endtime two .. date fileds .. I am using endtime .. I find out max.endtime .. min.endtime and max-min(endtime). I got 144 days .. I have given package.sp_prc(keep days); exec hdm_cleanup_util.sp_instrumentationdata(143). Means i am going to purge Just one day data. whenever I execute the above command .. immediately I am getting ERROR at line 1: ORA-01426: numeric overflow ORA-06512: at "HDM_DBUSER2.HDM_CLEANUP_UTIL", line 1196 .. error.

Why are you purging 2 billion rows in one go?
If that's the entire content of the table, why not just truncate it?
If you really must do this in one go, you'll have to "upgrade" from PLS_INTEGER to INTEGER (a predefined subtype of NUMBER).  OK, you lose the efficiencies of PLS_INTEGER, but you also lose the 32-bit limitation that it imposes on you.
Most "purging" activities I've encountered "nibble away" at data rather than trying to remove great chunks of stuff.  If, say, you had top get rid of six years of very sizeable .. stuff .., you might set up your purging process to remove, one week's worth of data.  You'd configure it with a "start date" including the very oldest data that you want to get rid of and run it.  Then you'd "move" the start date "up" a week and run it again.
The chances are you can run this several times each the day (and more so as the backlog reduces and the process speeds up) until you're running dozens of times a day and clearing years-worth of data in doing so.
Eventually, your "start date" would catch up with today, at which point you simply leave it running daily, just taking the last few days.  By that time, your now "Daily Maintenance Run" will have been well and truly tested!


Speeding up a GROUP BY, HAVING COUNT query I'm trying to speed up this query in Postgres 9.4:

SELECT "groupingsFrameHash", COUNT(*) AS nb
FROM "public"."zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876"
GROUP BY "groupingsFrameHash"
HAVING COUNT(*) > 1
ORDER BY nb DESC LIMIT 10

I have an index on "groupingsFrameHash". I don't need exact results, a fuzzy approximation is good enough.

Here is the query plan:

Limit  (cost=17207.03..17207.05 rows=10 width=25) (actual time=740.056..740.058 rows=10 loops=1)
  ->  Sort  (cost=17207.03..17318.19 rows=44463 width=25) (actual time=740.054..740.055 rows=10 loops=1)
        Sort Key: (count(*))
        Sort Method: top-N heapsort  Memory: 25kB
        ->  GroupAggregate  (cost=14725.95..16246.20 rows=44463 width=25) (actual time=615.109..734.740 rows=25977 loops=1)
              Group Key: "groupingsFrameHash"
              Filter: (count(*) > 1)
              Rows Removed by Filter: 24259
              ->  Sort  (cost=14725.95..14967.07 rows=96446 width=25) (actual time=615.093..705.507 rows=96026 loops=1)
                    Sort Key: "groupingsFrameHash"
                    Sort Method: external merge  Disk: 3280kB
                    ->  Seq Scan on "zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876"  (cost=0.00..4431.46 rows=96446 width=25) (actual time=0.007..33.813 rows=96026 loops=1)
Planning time: 0.080 ms
Execution time: 740.877 ms

I don't understand why it needs to do a Seq Scan.

You want the 10 most common values for "groupingsFrameHash" with their respective counts (excluding unique values) - a common task. This specification caught my attention, though:

  a fuzzy approximation is good enough

This allows for radically faster solutions. Postgres happens to store just those approximations in the system catalogs, the total count in pg_class and most common values in pg_statistic. The manual about the nature of these numbers:

  Entries are created by ANALYZE and subsequently used by the query planner. Note that all the statistical data is inherently approximate, even assuming that it is up-to-date.

You have been warned.

Also consider the chapter Statistics Used by the Planner in the manual.

If you have set up autovacuum properly and the contents of your table do not change too much, these estimates should be good. In case you run this query immediately after substantial changes to the table (so autovacuum didn't get a chance to kick in), run ANALYZE first (or better VACUUM ANALYZE if you can spare the time). You can also fine-tune precision, that's beyond the scope of this question ...

There are security considerations. Quoting the manual again:

  pg_statistic should not be readable by the public, since even
  statistical information about a table's contents might be considered
  sensitive. (Example: minimum and maximum values of a salary column
  might be quite interesting.) pg_stats is a publicly readable view on
  pg_statistic that only exposes information about those tables that are
  readable by the current user.

All this considered, you can get quick estimates:

SELECT v."groupingsFrameHash", (c.reltuples * freq)::int AS estimate_ct
FROM   pg_stats s
CROSS  JOIN LATERAL
   unnest(s.most_common_vals::text::text[]  -- use your actual data type
        , s.most_common_freqs) WITH ORDINALITY v ("groupingsFrameHash", freq, ord)
CROSS  JOIN (
   SELECT reltuples FROM pg_class
   WHERE oid = regclass 'public.zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876'
   ) c
WHERE  schemaname = 'public'
AND    tablename  = 'zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876'
AND    attname    = 'groupingsFrameHash'  -- case sensitive
ORDER  BY v.ord
LIMIT  10;

There are a couple of noteworthy features in this query:

Provide all identifier strings unescaped and case sensitive.
unnest() for multiple arrays requires Postgres 9.4 or later. Details:

Passing arrays to stored procedures in Postgres

pg_stats.most_common_vals is a special column with the data pseudo-type anyarray (not available in user tables). It can store arrays of any type. To decompose, cast to text and then to the array type of your column type. Assuming text[] in the example:

s.most_common_vals::text::text[]

Replace with your actual data type.
I added WITH ORDINALITY to unnest() (Postgres 9.4 or later) to preserve the original order of elements. Since the numbers in the arrays are ordered by descending frequency, we can work with that sort order right away. Consider:

PostgreSQL unnest() with element number

This takes around 1 ms or less - no matter how many rows there are in your table.

Experimental optimizations

If you still need to squeeze out more performance and you have superuser access, you could use pg_statistic directly:

SELECT v."groupingsFrameHash", (c.reltuples * freq)::int AS estimate_ct
FROM   pg_attribute a
JOIN   pg_class     c ON  c.oid = a.attrelid
JOIN   pg_statistic s ON  s.starelid = a.attrelid
                      AND s.staattnum = a.attnum
     , unnest(s.stavalues1::text::text[]
            , s.stanumbers1) WITH ORDINALITY v ("groupingsFrameHash", freq, ord)
WHERE  a.attrelid = regclass 'public.zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876'
AND    a.attname  = 'groupingsFrameHash'
ORDER  BY v.ord
LIMIT  10;

As we are getting closer to the core of Postgres, you need to know what you are doing. We are relying on implementation details that may change across major Postgres versions (though unlikely). Read details about pg_statistics in the manual and comments in the source code.

To squeeze out the last drop, you could even hard-code the attribute number of your column (which changes if you change the position of the column in your table!) and rely on the order of rows returned by unnest(), which normally works:

SELECT v."groupingsFrameHash", (c.reltuples * freq)::int AS estimate_ct
FROM   pg_class     c
JOIN   pg_statistic s ON s.starelid = c.oid
     , unnest(s.stavalues1::text::text[], s.stanumbers1) v("groupingsFrameHash", freq)
WHERE  c.oid = regclass 'public.zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876'
AND    s.staattnum = int2 '6'  -- hard-coded pg_attribute.attnum
LIMIT  10;

Get your own estimates

With the new TABLESAMPLE feature in Postgres 9.5 you can base your aggregates on a (more or less) random sample of the table:

SELECT birthday, 10 * count(*) AS estimate
FROM   big
TABLESAMPLE SYSTEM (10)
GROUP  BY 1
ORDER  BY estimate DESC
LIMIT  10;

Details:

Fast way to discover the row count of a table

Exact counts

If you need exact counts, the best query depends on data distribution and value frequencies. Emulating a loose index scan (like @Mihai commented) can very well improve performance - in a limited fashion, though, (like @ypercube commented) since you need to consider all distinct values for your sort order. For relatively few distinct values the technique still pays, but for your example with ~ 25k distinct values in a table of ~ 100k rows the chances are slim. Basics:

Optimize GROUP BY query to retrieve latest record per user

But first you probably need to tune your cost settings. Using SET LOCAL enable_seqscan = off; is primarily meant for debugging problems. Using it in your transaction is a measure of last resort. It may seem to fix your problem at hand, but can bite you later.

Rather fix the underlying problem. My educated guess is that your setting for random_page_cost is unrealistically high. If most of your database (or at least most of the relevant parts) fit into available cache, the default setting of 4.0 is typically much too high. Depending on the complete picture it can be as low as 1.1 or even 1.0.

The fact that Postgres incorrectly estimates a sequential scan to be faster, while using the index is ten times faster would be a typical indicator for misconfiguration:

Keep PostgreSQL from sometimes choosing a bad query plan


Should a meta_key or other key-value table columns use a HASH or BTREE index? I've noticed that my WordPress database has BTREE indexes everywhere.
By my understanding, BTREE indexes allow more efficient < > and BETWEEN queries, but are otherwise less efficient than a HASH query for = and != queries.
This makes sense for some columns that might be filtered numerically (dates obviously, and IDs potentially, – though in practice ID > x doesn't seem very likely in WordPress), but I can't see any benefit for columns like meta_key which stores the text key of a key-value pair, or indeed the post_id column of a post_metadata table, which will almost without exception be JOINed to the posts table.
Would a HASH index be more performant? If so, is there any reason that my database is using BTREE indexes for these columns, or is this just a default? If not, what am I lacking in my understanding of indexes?

That's the wrong question.  The indexes that WP provides on the meta tables are inefficient.  (And the general EAV pattern sucks.)
This explains how to significantly improve the indexes:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
Back to your question:  BTree is about as fast as Hash (were it to exist).  And Hash is not available for InnoDB.  (InnoDB is the only 'engine' you should use for WP.)
I believe that the developer of InnoDB saw that "one side does fit all" when he implemented only BTree.


i want to show the attendance of a student in percentage In my project attendance management system. I want to show the attendance in percentage after entering enrollment of a particular student
table in database
fsignup(name,email,password,subject)
ssignup(name,email,enrollment,semester,password,branch)
attendance(enrollment,subject,attendance,date,faculty_id)

I'm gonna assume couple of things here. For attendance column I'm gonna assume "Yes" if the student is present on that particular date, and "No" if otherwise.

SELECT f.name, f.subject, t2.attendance_percentage
FROM fsignup f
JOIN (SELECT subject, Yes/SUM(Yes+No)*100 AS attendance_percentage FROM (SELECT subject, SUM(CASE WHEN attendance="Yes" THEN 1 ELSE 0) AS Yes, SUM(CASE WHEN attendance="No" THEN 1 ELSE 0) AS No FROM attendance GROUP BY subject)t1)t2
ON f.subject=t2.subject
GROUP BY f.name, f.subject


How to troubleshoot large transaction log file size? I'm following up on this Why is my SQL Log File Huge? article.

Looking at the disk usage report, the transaction log space use is practically zero. It shows 99.1% unused.

It is schedule to backup the log every hour for 24hours/day. Every hourly trn file is quite small but there is one backup at round 2AM that is close to the actual database data file size.

If I schedule the hourly log backup from 3AM to the next 1AM, then the next big trn file is 4AM.

If I scheduled the log backup from 4AM to 12AM then the next large trn file is 5AM.

If I schedule the hourly log backup from 12AM to 11:59:59PM then the next large trn file is 2AM.

The pattern seems to be that whatever the first of the next day backup, that trn file will be large.

The recovery mode is set to full. The log is set to 5MB autogrowth and max out at 2TB. I've also tried the Monitoring SQL Server database transaction log space to track the log space used at every hour. At every hour the log spaced used was never bigger than 99MB and most are .8MB in size.

Help is much appreciated!

It's probable that you had a long running query that caused a bunch of transactions. Something like a large batch of inserts/deletes/updates. You can track these down in the query store if you have a recent version of SQL Server, or you can query the plan cache for long running transactions.

Once you've found the query, you can figure out what it's doing and go from there. You may just be stuck with it, but finding out what's causing the large amount of transactions is your first step.

Also, you should be aware that an autogrowth of 5MB is extremely small, and can result in a lot of Virtual Log Files when it grows, which can hinder startup performance. I suggest looking into tuning the growth rate as well.

https://www.brentozar.com/blitz/high-virtual-log-file-vlf-count/


MYSQL Query - Unexpected result in text messaging database On a text messaging database I have built, I am trying to return the total amount of messages a user has sent and the total number of messages a user has received. I have the following code:

SELECT People.Name,
SUM(Case When Recipients.RecipientID = People.ID THEN 1 ELSE NULL END) AS 'Messages Received',
Count(Messages.SenderID = People.ID) AS 'Messages Sent'
FROM People JOIN Recipients, Messages
GROUP BY People.ID
ORDER BY People.ID ASC;

which returns unexpected results. I am just using a test database of 3 messages sent. 2 by 1 person and 1 by another person. All 3 messages were group messages sent to the other 9 people listed in the database. So I would expect one user to have 2 messages sent and 1 received, one of the users to have 1 message sent and 2 received, and the other 8 people to have 0 sent and 3 received.

What I am actually getting is the two users who have sent message(s) have 3 messages received and 54 messages sent. The other users all have 6 messages received and 54 messages sent.

The tables are set up as follows:

Messages (MessageID int, SenderID int, Content varchar, Type varchar, DateTime datetime)
People (ID int, Name varchar)
Recipients (RecipientID int, MessageID int)

Because each message was sent to 9 people, there are 9 entries for each messageID in the Recipients table, with each one responding to a recipientID (which corresponds to People.ID).

Where does my query go wrong?

Also, in a somewhat related question, what would be the best way to modify the database if I want to add Groups and then just add people ID's to groups and allow a group to be a recipient and each person that is a member of that specific group gets counted as a recipient?

Your SELECT clauses may not be optimal. I suspect that Count(Messages.SenderID = People.ID) equals the total number of rows in the result set: for each row, Messages.SenderID = People.ID equals either 1 or 0, and MySQL simply counts all the 1s and 0s, and it ends up with a total number of 54 (6 times 9). Try using SUM(Messages.SenderID = People.ID) instead.

By the same logic, I would replace SUM(Case When Recipients.RecipientID = People.ID THEN 1 ELSE NULL END) with SUM(Recipients.RecipientID = People.ID).


Why does NOLOCK make a scan with variable assignment slower? I'm fighting against NOLOCK in my current environment. One argument I've heard is that the overhead of locking slows down a query. So, I devised a test to see just how much this overhead might be.

I discovered that NOLOCK actually slows down my scan.

At first I was delighted, but now I 'm just confused. Is my test invalid somehow? Shouldn't NOLOCK actually allow a slightly faster scan? What's happening here?

Here's my script:

USE TestDB
GO

--Create a five-million row table
DROP TABLE IF EXISTS dbo.JustAnotherTable
GO

CREATE TABLE dbo.JustAnotherTable (
ID INT IDENTITY PRIMARY KEY,
notID CHAR(5) NOT NULL )

INSERT dbo.JustAnotherTable
SELECT TOP 5000000 'datas'
FROM sys.all_objects a1
CROSS JOIN sys.all_objects a2
CROSS JOIN sys.all_objects a3

/********************************************/
-----Testing. Run each multiple times--------
/********************************************/
--How fast is a plain select? (I get about 587ms)
DECLARE @trash CHAR(5), @dt DATETIME = SYSDATETIME()

SELECT @trash = notID  --trash variable prevents any slowdown from returning data to SSMS
FROM dbo.JustAnotherTable
ORDER BY ID
OPTION (MAXDOP 1)

SELECT DATEDIFF(MILLISECOND,@dt,SYSDATETIME())

----------------------------------------------
--Now how fast is it with NOLOCK? About 640ms for me
DECLARE @trash CHAR(5), @dt DATETIME = SYSDATETIME()

SELECT @trash = notID
FROM dbo.JustAnotherTable (NOLOCK)
ORDER BY ID --would be an allocation order scan without this, breaking the comparison
OPTION (MAXDOP 1)

SELECT DATEDIFF(MILLISECOND,@dt,SYSDATETIME())

What I've tried that didn't work:

Running on different servers (same results, servers were 2016-SP1 and 2016-SP2, both quiet)
Running on dbfiddle.uk on different versions (noisy, but probably
same results)
SET ISOLATION LEVEL instead of hints (same results)
Turning off lock escalation on the table (same results)
Examining actual execution time of the scan in the actual query plan
(same results)
Recompile hint (same results)
Read only filegroup (same results)

The most promising exploration comes from removing the trash variable and using a no-results query. Initially this showed NOLOCK as slightly faster, but when I showed the demo to my boss, NOLOCK was back to being slower.

What is it about NOLOCK that slows down a scan with variable assignment?

NOTE: this might not be the type of answer you're looking for. But perhaps it will be helpful to other potential answerers as far as providing clues as where to start looking
When I run these queries under ETW tracing (using PerfView), I get the following results:
Plain  - 608 ms
NOLOCK - 659 ms

So the difference is 51ms.  This is pretty dead on with your difference (~50ms).  My numbers are slightly higher overall because of the profiler sampling overhead.
Finding the difference
Here's a side-by-side comparison showing that the 51ms difference is in the FetchNextRow method in sqlmin.dll:

Plain select is on the left at 332 ms, while the nolock version is on the right at 383 (51ms longer).  You can also see that the two code paths differ in this way:

Plain SELECT

sqlmin!RowsetNewSS::FetchNextRow calls

sqlmin!IndexDataSetSession::GetNextRowValuesInternal

Using NOLOCK

 sqlmin!RowsetNewSS::FetchNextRow calls

sqlmin!DatasetSession::GetNextRowValuesNoLock which calls either

sqlmin!IndexDataSetSession::GetNextRowValuesInternal or
kernel32!TlsGetValue

This shows that there is some branching in the FetchNextRow method based on the isolation level / nolock hint.
Why does the NOLOCK branch take longer?
The nolock branch  actually spends less time calling into GetNextRowValuesInternal (25ms less).  But the code directly in GetNextRowValuesNoLock (not including methods it calls AKA the "Exc" column) runs for 63ms - which is the majority of the difference (63 - 25 = 38ms net increase in CPU time).
So what's the other 13ms (51ms total - 38ms accounted for so far) of overhead in FetchNextRow?
Interface dispatch
I thought this was more of a curiosity than anything, but the nolock version appears to incur some interface dispatch overhead by calling into the Windows API method kernel32!TlsGetValue via kernel32!TlsGetValueStub - a total of 17ms.  The plain select appears to not go through the interface, so it never hits the stub, and only spends 6ms on TlsGetValue (a difference of 11ms).  You can see this above in the first screenshot.
I should probably run this trace again with more iterations of the query, I think there are some small things, like hardware interrupts, that were not picked up by the 1ms sample rate of PerfView

Outside of that method, I noticed another small difference that causes the nolock version to run slower:
Releasing Locks
The nolock branch appears to more aggressively run the  sqlmin!RowsetNewSS::ReleaseRows method, which you can see in this screenshot:

The plain select is on top, at 12ms, while the nolock version is on the bottom at 26ms (14ms longer).  You can also see in the "When" column that the code was executed more frequently during the sample.  This may be an implementation detail of nolock, but it seems to introduce quite a bit of overhead for small samples.

There are lots of other small differences, but those are the big chunks.


how to find backup history of SSAS database Shockingly on Internet, I am unable to find backup history of SSAS database.

Is there no way to do it?

Also should we take multiple backups of SSAS database? I am not much aware of SSAS database.

Thanks in advance.

There is no dynamic management view(DMV) available to get this backup history details about SSAS database. You can get these details by using below method:

Implement this backup process with SSIS either using XMLA or SSIS task
Use to implement the logging and completion of backup task time to a log table.

Unlike a relational database backup file, an SSAS backup file can contain only one backup. In other words, you can’t create backup sets. If you want to retain multiple backups, you must create multiple files. Also, SSAS doesn’t provide options for differential backups or log file backups.

By default, two check boxes are selected in the Backup Database window -  Apply compression and Encrypt backup file. When the Apply compression option is selected, SSAS compresses the backup file, which saves disk space at the expense of higher CPU utilization during the backup operation. When the Encrypt backup file option is selected, SSAS accepts a user-supplied password, which it uses to encrypt the backup file. Note that, unlike the password for a SQL Server relational database backup set, this password prevents the reading of the backup data by other means. If you choose to encrypt SSAS backups, make sure that you keep a record of the password in a safe and separate location. More details are here, here and here.

Script for XMLA and using ascmd.exe is mentioned at this link.

Hope above helps.


Can I see the status of a "repair by sorting" in mysql? I am running an alter statement on a very large table.

show processlist shows the state as "Repair by sorting".

Is there a way to see the status, or get an estimate of how much work has been completed/remaining?

Thanks.

Repair by sorting is a phase that creates a temp table, populates a temp index file, and  prepares all indexes for the target. This would require being in the operating system to view the temp file's size as well as the data and time stamp.

If you ever need to see such progress, there are two things you can do

Migrate the DB to MySQL in Amazon EC2 where you can have OS level access
Migrate the DB to MariaDB in Amazon EC2 since MariaDB gives metered progress on DDL : See my post Is there a progress indicator for OPTIMIZE TABLE progress?

Other than these suggestions, there is nothing else you can do.


Running postgresql in Mac Terminal I have a production SQL dump file that I want to restore to my local psql db for debugging reasons.

I can't access psql via the terminal though pgAdmin3 works just fine. Here is the error I'm getting:

$ psql
psql: could not connect to server: Permission denied
    Is the server running locally and accepting
    connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

$ locate psql | grep /bin
/Library/PostgreSQL/9.0/bin/psql
/Library/PostgreSQL/versions/9.0.1/bin/psql
/opt/local/bin/psql83
/opt/local/lib/postgresql83/bin/psql
/usr/bin/psql

I tried using sudo, no change. Any Idea why I can't connect w terminal on the mac and then restore the sql db dump locally?

Thanks

Try specifying -h localhost as a psql parameter, to connect via tcp/ip rather than a unix socket.

Likely, your psql and postgresql have different ideas of the correct setting for unix_socket_directory. Especially seeing as you have at least two different builds of postgresql installed! You might want to make sure you're using the psql client that corresponds to your postgresql server, since they have different major versions that will have other benefits too. Failing that, you can give a unix socket directory name as the hostname, e.g. psql -h /tmp will look for /tmp/.s.PGSQL.5342.


Audit Trail in NoSQL vs. Relational DB, audit trail feature in MongoDB Two questions regarding selection a DB type and engine for the scenario when the Audit Trail functionality is a must:

Is there any preference in DB type (NoSQL/Relational) choice when the Audit Trail is critical?
Is it a common practice to use a MongoDB in a scenario when the Audit Trail is an obligatory feature?

As far as I know, MySQL and SQL Server offer such functionality, but I don't find such option in MongoDB features list.

MongoDB Enterprise and MongoDB Atlas both include auditing capability with options to to filter for audit events including schema (DDL) changes, CRUD operations, authentication & authorization, and replication/sharding commands.

Auditing was first included in MongoDB Enterprise 2.6 (March, 2014).


SQL Server DIFFERENTIAL BACKUP question? Simple question

At what point is a differential backup considered transactionally consistent?

Is it the creation date of the DIFF, or the time at which it completes?

That is if I restore it what transactions can I expect to have been committed? Those that completed before the time the DIFF started, or those that completed during the DIFF, that is the finished time?

Generally speaking a differential works very similarly to a full backup, except that only the changed extents in the DCM are backed up. Differential backups still contain a portion of the log needed to be transactionally consistent. This will differ for every environment and is not "known" at the time of backup. You can use the RESTORE HEADERONLY of the differential to check LSNs that are included and covered. When you think the transaction finished and when SQL Server wrote the logs that may or may not be flushed to disk when the checkpoint ran before the differential backup may or may not be included.


Exporting from SQL Server 2005 for import into ER Studio 9.0 I am new to ER Studio and need to create an ERD for a remote system. That system can only be accessed by LogMeIn remote control and by FTP. The remote system is running SQL Server 2005. The local system has ER Studio 9.0 installed.

How can I export a file from SQL that will allow me to create a diagram in ER Studio? I can see the import function, but I keep getting errors when I attempt to import a SQL file that I've created from the remote system.

I think I'm simply choosing the wrong options on export. Here's what I'm doing:
SQL Server 2005, Object Explorer
1. right-click on the database I want to diagram
2. Choose Tasks > Generate Scripts
3. Select the desired database, next
4. Leave the default options (I've tried a few things, none worked), next
5. Select Tables only (I don't need anything except the tables, their fields, keys, and relationships)
6. Select all tables (or 4 tables, it doesn't matter)
7. Choose "Script to file", enter a file name, next
8. Finish.

The output script, with a .sql extension, contains commands for creating the tables with all the correct fields. It's lovely.

In ER Studio, New Data Model dialog box
1. Select "Import Model From:" and choose SQL File
2. Click Import
3. Select the appropriate SQL file
4. Select target database platfrom (SQL Server 2005)
5. Don't tick any boxes about inferring keys or domains)
6. Hierarchical layout is the default. Cool with me.
7. Relational model
8. Leave "Use Physical Parser" unchecked. No idea what it does, but checking it doesn't help.
9. OK
10. Invalid token, Line 1, Offset 4.

If I manually delete the line that is giving ER Studio trouble, I still get an error. I googled the heck out of this and found nothing of use. Absolutely any feedback would be appreciated.

I can't believe I didn't actually google the exact error message. There's a lesson to be learned there. Upon doing so, I learned that similar errors occur in other applications. The cause is non-ASCII codes. I was exporting from SQL in Unicode.

Here is the full solution:
In SQL:

Right-click on the desired db, choose Tasks > Generate Scripts
Select the desired database, next
Leave the default options (I've tried a few things, none worked), next
Select Tables only (I don't need anything except the tables, their fields, keys, and relationships)
Select all tables (or 4 tables, it doesn't matter)
Choose "Script to file", enter a file name
Under Save as: choose ANSI text (Unicode breaks the crap out of ER/Studio)
Finish

Then import into ER/Studio. My import has been running for 2 hours so far.

Next up, trying to figure out how to make an ordered list on this site. I'm so full of fail. :)


SQL Server Database Synchronization I have a database in a server in Africa, and need a daily sync\restore to a server in Europe.

My first idea was to sync the data between the 2 databases, but the database has more than 1000 tables, and is not well built, so the software I've tested didn't work great.

For what I've read the best option is to backup the database in one server and restore it to the remote server.

Is this the best solution?

Is Redgate Backup Pro the best tool for the task?

I've been playing with it and it seems to have everything I need: backup\restore scheduling, compression, etc.

Need some advice on this. Thanks in advance :)

Scheduling backups and restores via the SQL Agent isn't very hard. It is only one or two lines of TSQL. Just make sure that the backup finishes before the restore starts.

You do not mention which version or edition of SQL Server you are using. Some versions/editions of SQL Server support Backup compression natively. (Here is the page that talks about the capabilities of the different editions of SQL Server 2012.) If you are not using a version/edition of SQL Server that supports compression, RedGate's backup compression should be a reasonable choice. (I've not used that particular compressor. I have used a competitor, SQL Lightspeed. I believe that the products are fundamentally the same.)

In the past, I've found the larger problem is getting the backup files from one location to the other, as it is often assumed that both servers are on the same LAN and domain and that you can simply use copy.exe or robocopy.exe. I was forced to cobble together my own FTP transfer schemes back then, which is more work and testing.

Another problem is getting the user IDs synced up with the login IDs after restoring the database to the target server. If you have already done this a few times, you know what I mean. If not, here is a link to a relevant SE thread.


difference between MySQL and noSQL databases All these days I've been using MySQL, recently I saw a kind of similar named Database NoSQL. What is NoSQL and what is the difference between them ?

That's a very wide question and hard to answer in a short way. These two are hardly comparable because they aren't the same level of categorization/abstraction.

First of all MySQL is a Relational Database product using SQL from Oracle while noSQL is not a product. It's a (set of) type(s) of "products" or better phrase as a concept. As the name suggests it's not SQL. NoSQL types and products are for example:

key-value stores like Redis, Tokio Cabinet or even Memcache can be mentioned
document database like MongoDb and CouchDB
graph databases like OrientDB
columnar datastores like Hadoop, Cassandra or Bigtable

SQL means Structured Query Language and this is related to how the data can be retrieved. This is not equivalent with Relational Databases. You can find noSQL RDBMS products as well.

Let me put it this way SQL is a language which can be used for data retrieval in relational databases (for example: MySQL, Postgres, Oracle) just like it can be used (however rarely used) in other types of databases/datastores.


PostgreSQL column for year I want to save the year in a PostgreSQL database - just the year. How should I create this field? There is a lot of information out there how I can extract the year from a date field, but not on how I should set up a table for using a year only. I tried this:

CREATE TABLE information (
    id serial PRIMARY KEY,
    year date NOT NULL
);

INSERT INTO "information" ("year") VALUES (2010);

But this fails:

ERROR: invalid input syntax for type date: "2010"

Either use a simple integer and represent BC as negative:

CREATE TABLE information (
  id serial PRIMARY KEY,
  year integer NOT NULL
);

or use a date that's constrained to the 1st of Jan and use 2014-01-01 etc in input/output:

CREATE TABLE information (
    id serial PRIMARY KEY,
    year date NOT NULL,
    CONSTRAINT year_must_be_1st_jan CHECK ( date_trunc('year', year) = year )
);

Which is more appropriate depends mostly on how your app is using the information. I think it's OK to represent a year as a simple integer - just please don't do this for year integer, month integer. That's awful to work with, and is where a constrained date is more appropriate.

I tend to use constrained dates so that when the requirements inevitably change I can adapt more easily.


Switching context between two instances from one account I have a server with two instances running on it (a dev environment) where we use one login to access either instance.  I wanted to be able to switch context from one instance to the other without changing my .profile to reference the other instance's db2profile, logging out and back in.  I wrote a script (iswap) to allow me to do that.  Here's what it looks like:

  if [[ $DB2INSTANCE = "instance1" ]]
  then
     . /usr/users/instance2/sqllib/db2profile
     echo "Switched context from instance1 to instance2"
  elif [[ $DB2INSTANCE = "instance2" ]]
  then
     . /usr/users/instance1/sqllib/db2profile
     echo "Switched context from instance2 to instance1"
  fi

If I do a "db2 list db directory" after running iswap it shows me the databases in the new instance.  If I echo $DB2INSTANCE it reflects the new instance.  But, if I do a "db2 list applications" it still shows me the applications connected to the original instance.   What am I not changing that I need to add to the script?

The list applications command requires an instance attachment, which is performed implicitly when you first run this command against "instance1". The CLP back-end process db2bp, which handles all server commands and SQL statements on behalf of the front-end program CLP db2, remains attached to the "old" instance after you switch the environment.

You need to end the back-end process by issuing the terminate command (db2 terminate), which will cause a new back-end process to spawn and attach to the new default instance, as specified by $DB2INSTANCE, next time it is required.


How to include values in a MySQL sum based on a conditional statement? I have the following MySQL query which takes in the sum of columns 1-4, from the start of the current date, up to the current time.  My table has 5 columns, and I am trying to perform a conditional query based on a value in column_5.

I need to modify this query according to the following:

If column_5 > 0, then I don't want to include the values from column_1 in my sum, Total.
But if column_5=0, then I do want to include the values from column_1 in my sum Total.

At the moment, the values from column_1 are included in my sum, regardless of whether column_5 is greater than or equal to 0.

SELECT (column_1+column_2+column_3+column_4) at 'Total'

FROM myDB.table_1

WHERE TS >= CURDATE() AND TS < CURDATE() + INTERVAL 1 DAY ;

Conditionals can be done by means of the CASE operator/expression:

Your query will work with:

SELECT
    ts,
    (CASE WHEN column_5 > 0
     THEN
        0              /* We are ignoring column_1 */
     ELSE
        column_1       /* We are using its value */
     END) + column_2 + column_3 + column_4 AS total
FROM
    t
ORDER BY
    ts ;

You can check it at http://rextester.com/IHM39024

The CASE expression is standard SQL.

MySQL also offers an IF function that could be used in this case. In fact IF(a,b,c) = CASE WHEN a THEN b ELSE c END.


Database size increases after creating a partition I have just partitioned an existing table.

The database size was 5GB with 14 million records prior to the partition operation.  After the partition operation moved rows to the new FileGroup, I noticed the database size increased to 8GB.

Can someone explain why that happened?

One reason is that new data was allocated but old not cleared. This is partially because it is not available immediatly, the other reason may be that SQL Server has to wait until partitioning finishes to delete the old table. if you are considered by that (which I find funny - heck, it is even funny partitioning a very small table on a tiny database of 5gb) then you may shrink the file to reclaim the space. THAT SAID: perforamnce oriented databases should not autogrow to start with ;)


Is there any significant difference between Month(date) and DatePart(month, date)? MSDN states that both Month(date) and DatePart(month, date) return the exact same value, however is there any difference between the two methods, performance or otherwise?

I'm building some queries that aggregate large sets of data based on month, and sometimes they take a while to run so I'd like to be sure I'm using the most efficient syntax possible.

I'm currently using SQL Server 2005

I just ran several tests on a largish table (12,430,129 rows) in SQL Server 2008r2.

The field is smalldatetime
The field is NOT indexed
I changed the processing order after each run to eliminate page caching issues

Results:

MONTH() - Ran in 6662, 6583, 6661 and 6560 ms.  Average runtime 6616.5ms

DATEPART() - Ran in 6520, 6584, 6552, and 6608 ms. Average runtime 6566ms

So, DATEPART() does seem to be marginally faster.  However, this is 7 tenths of a percent difference, so it probably won't matter a whole lot.


innodb table does not exist I have four databases each holding several hundred tables.

Every table in one database suddenly returns error code 1146 Table xxx does not exist. The other databases are fine. I can see all the .ibd and .frm files ok and their various file sizes suggests they are not empty. There is an ibdata1 as well.

From workbench I tried Analyse Table, which indicates InnoDB: Tablespace is missing for table '...' and going on to 'Doesn't exist'.

I created new test tables and they work fine.

OSX was updated the other day, but as the other databases are fine I can't see a connection.

I haven't manually moved/copied etc any files etc.

Something has gone awry, but what?

This sounds like all your .ibd file have been detached. Why ?

Please look at the InnoDB Architecture from Percona CTO Vadim Tkachenko

ibdata1 has an internal data dictionary that registers every .ibd by a unique tablespace_id. Somewhere along the line, all the tables in the trouble database have become detach from the data dictionary.

I wrote about this years ago

Apr 23, 2012 : How can extract the table schema from just the .frm file?
Dec 21, 2011 : Table compression in InnoDB?

To rightfully give credit where credit is due, I learned about this concept of detached .ibd files and trying to connect them back to the data dictionary from a blogpost written by Chris Calendar back in Feb 2009. I once helped client recover 30 tables with the information from his post (See my post ERROR Cannot find or open table? on what I did to help that client). Please read his biog and then my posts on what you need to try.

As for the why the table would be come detached, here is an example

Your datadir is /var/lib/mysql
Your have a database called mydb
You have a table called mytable

If you create an InnoDB table called mytable in the mydb database

You get two files

/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.ibd

.ibd file would be assigned a tablespace_id.

If you drop that table and create it again, the table would have a different tablespace_id.

Now, picture this:

You make a backup of the database folder /var/lib/mysql/mydb with the tablespace_id of 27 on that one table
You drop the table and create it (TRUNCATE TABLE internally does the same thing), ending up with tablespace_id of 28
You restore the backup of the database folder /var/lib/mysql/mydb with the tablespace_id of 27 on that one table

This causes the detachment because table mydb.mytable has the old tablespace_id of 27 while the data dictionary within ibdata1 has 28.

Since you said you didn't move or copy anything, then you may have too many InnoDB tables. Each InnoDB table needs two file handles to be opened and accessed.

I hope I have given your the information you need to help you recover your database.


Why do we have SHRINKDATABASE command in SQL Server I have read across the internet where Finest Developer says shrinkdatabse should be avoided as it causes increase in fragmentation and decrease in performance .

I would like to know from you guys have you ever used the shrink database command if yes then in which practical scenarios we should be using them.

First and foremost lets distinguish between the need for SHRINK and it's well known unintended side effect: possible increase of fragmentation. The later is an implementation side effect. In fact is perfectly possible to design a shrink that would reduce fragmentation (ie. has the same effect as a REORGANIZE) at the cost of significantly longer runtime.

The need for shrink is a perfectly valid one: reduce the size of a database that has grown for whatever reason. Whether is reduce the size after a massive one-time data delete, reverse the effect of a one time operation that caused unexpected size growth (eg. a bad import), migration to a much improved (and much smaller) data model, clean the fallout of some previous bad DBA management, they're all perfectly valid reason for shrinking a database. The cost of shrink (increased fragmentation) must be considered, of course. But consider a case when a badly design DB of 200Gb is reduced to just 20gb of data. Shrinking it to 50Gb size and rebuilding the indexes makes perfect sense. In other words the capability to shrink a database is needed.

What is frowned upon is the abuse of shrink:

regular scheduled shrink
manual shrink for unfounded reason ('I need to free some disk space for ...'), when often the database actually needs that size and will grow back overnight
shrink for small yields (eg. reduce 200Gb by 5Gb)

As for the LOG shrink, the problem is even more direct: LOG can grow even on a well maintained system. Clear examples: transactional replication or DB mirroring deployments that run into problems with the distribution DB or the mirror partner. Until the problem is solved, the log must be retained by the publisher (for TX) or primary (for DBM). When the issue is finally resolved and the pinned log is cleared, you will end up with an unnecessary large log. This can and should be shrinked back to operational size. Shrinking the log is somehow seen as VooDoo ('I tried it and did not do anythin!', 'Hmm, I tried it and it worked now') but is easy once you understand that in order to shrink the log the tail of the log must be free  which implies that, counter intuitively, more log has to be generated (and freed) before the file can be shrunk. Is all explained in How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server 2005


ODBC Timeout error with SQL server connection I have an odd problem.  The company has used an ODBC connection for a desktop MDB program to connect to SQL Server.  The ODBC connects using a SQL Server account to authenticate.  It has worked without issue in the past.

Recently, it appears any new domain account does not connect.  They always fail for an connection timeout error.  I've tried creating brand new account, I've tried creating an account by "Copy as" and they still get a timeout error.

One additional twist, the first time I discovered this error, last week, was when I was moving a user to a different computer.  The ODBC connection worked on her old computer but when logging on the new one, it timed out but still worked on her old computer.  I also tried logging the user in on MY computer and her profile does a timeout.

I've tried rebooting the Domain Controller as well as the SQL Server.  No changes on the network or any server.

I found a solution to the problem, but not sure why it works.  I added an inbound Windows Firewall rule on the server, for port 1433 and the users in question can connect.  Not sure why this affected a couple users and not 20 others, but at least it gets the spotlight off me!  Thanks for everyone's input.


Determine size of mysqldump prior to dumping This question is a followup on this previous question.

Q: How can I determine or estimate the size of the SQL dump file prior to using something like mysqldump?

I have tried using the most upvoted answer but it doesn't seem to be working in my case. I am actually trying to determine a specific database's size prior to dumping, so I am using this query

SELECT
    Data_BB / POWER(1024,1) Data_KB,
    Data_BB / POWER(1024,2) Data_MB,
    Data_BB / POWER(1024,3) Data_GB
FROM (SELECT SUM(data_length) Data_BB FROM information_schema.tables
WHERE table_schema ='db_name') A;

I am getting a filesize of ~4.5mb whereas my (uncompressed) dump takes less than 2mb. I have also tried excluding VIEWs from my dump as such:

SELECT
    Data_BB / POWER(1024,1) Data_KB,
    Data_BB / POWER(1024,2) Data_MB,
    Data_BB / POWER(1024,3) Data_GB
FROM (SELECT SUM(data_length) Data_BB FROM information_schema.tables
WHERE table_schema ='db_name' AND engine IS NOT NULL) A;

but this made no difference. Am I doing something wrong here?

(Most of my tables are InnoDB except for two who are MyISAM but contain almost no data)

Actually, the numbers you have indicate something special: Your table data are fragmented.

Why such fragmentation ? InnoDB data and index pages are 16K. Row data will never completely fill up 16K.

I am sure if you reload the dump into another mysql instance, the data will take up less space, no fragmentation.

What you could also do is shrink all the InnoDB tables. How ???

To shrink mydb.mytable run

OPTIMIZE TABLE mydb.mytable;

or

ANALYZE TABLE mydb.mytable;

You could probably script i as follows:

SQLSTMT="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') FROM information_schema.tables WHERE engine = 'InnoDB' AND table_schema NOT IN ('information_schema','mysql','performance_schema','sys')"
INNODB_CONV_SCRIPT=Shrunk_InnoDB.sql
echo "SET SQL_LOG_BIN = 0;" > ${INNODB_CONV_SCRIPT}
mysql -h... -u... -p... -ANe"${SQL}" >> ${INNODB_CONV_SCRIPT}
mysql -h... -u... -p... < ${INNODB_CONV_SCRIPT}

After running this all your tables will be smaller.

To answer your actual question, no, you are not doing anything wrong.

Views take up no actual diskspace except for the View Definition.


Is there a workaround for lack of support of cascade triggers with MySQL? As things stand, cascaded foreign key actions do not activate triggers with MySQL. I don't want to get into the debate as to whether or not this is good, I just would like to know whether there is a workaround for this.
I'm currently using triggers to do something like this:
CREATE TRIGGER `Set_field1_on_update` BEFORE UPDATE ON `mytable`
FOR EACH ROW BEGIN
    SET NEW.field1 =  CONCAT(NEW.myforeignkey,'whatever');
END

Which works fine when myforeignkey is changed directly on mytable but not when it is updated with ON UPDATE CASCADE via its referenced table.
Is there anyway to achieve the above functionality (using something else than triggers if necessary) on cascaded foreign key actions?

The workaround, if that's the right word for it, seems like it would be to make the same changes to the child table in an AFTER UPDATE trigger on the parent table, containing the same logic as the BEFORE UPDATE trigger on the child table, wrapped inside a test to only do anything if the primary key in the parent table has actually changed, which would have caused a cascaded update.

DELIMITER $$
CREATE TRIGGER parent_after_update AFTER UPDATE ON parent_table FOR EACH ROW
BEGIN

# not using null-safe <=> comparison since PK cannot be null

IF NEW.primary_key != OLD.primary_key THEN

  UPDATE child_table ct
     SET /* the same logic that's in the before update trigger on the child table */
   WHERE ct.parent_primary_key = NEW.primary_key;

END IF;
END $$
DELIMITER ;

If primary_key on parent_table just now changed, then by definition, all of the rows child_table with the new value in their parent_primary_key column must have just been updated via a cascaded update, because until that value exists in parent_table.primary_key, there could not have been any rows already existing with the new value in child_table.parent_primary_key ... until the parent table's primary_key was updated just now, those rows could not possibly have existed, since they would have violated the referential constraint of the foreign key (referencing a nonexistent value).

Of course, I'm using "primary key" here when in reality the foreign key could reference any superkey of the parent table.

However, this calls into question what you're really using the cascaded updates to accomplish in the first place and whether that application of cascaded updates constitutes a sound design.  You seem to be deriving another column from the value, and storing that derived column, and both of these seem like less-than-ideal practices.

Normally, a cascaded update, at least in principle if not in fact, seems like something that is not a change to the data, as an update changing the child table directly would be.  "These" child records reference "that" row in the parent table.  After a cascaded update, they still do, and nothing has changed, only the key is different.


Update Table Query: Automatically handle "Enter Parameter Value" prompts? EDIT: Here are two database Files that demonstrate an applied example of the problem:

  Example_DBs.zip (Zipped due to 2 link limitation.)

Note: The missing Field in "Not Working.mdb" is intentional. Please have a look at the attached diagram for further clarification.

I'm trying to create a single, Update Query in Access 2007 that does all of these 3 things:

Update (skipping zero-length values)
Append
Automatically handle missing Fields without showing any "Enter Parameter Value" prompts.

I've managed to accomplish the 1st & 2nd items on my own, but I'm stumped on the 3rd. In essence, what I'm looking for is a way of telling Access to automatically use a zero-length/null value for any missing Field(s) the Query encounters, so long as it does not involve manually modifying the Table beforehand. Automation is key. And that's where you guys come in.

Here is a diagram of the desired results from a set of sample Tables:

And here is the Update Query's SQL code adjusted for those sample Tables:

UPDATE Table_1 RIGHT JOIN Table_2 ON Table_1.ID = Table_2.ID SET Table_1.ID = NZ([Table_2].[ID],[Table_1].[ID]), Table_1.Company = NZ([Table_2].[Company],[Table_1].[Company]), Table_1.Address = NZ([Table_2].[Address],[Table_1].[Address]), Table_1.Phone = NZ([Table_2].[Phone],[Table_1].[Phone]);

Any help would be greatly appreciated.

You can't have 'missing fields' in a query without getting a prompt. Your June updates can not reference the phone column that does not exist and your July can not reference address. You need 2 different queries. If your business logic can't determine which query to run then you can use the Fields Collection of the TableDef object.


Why this system table doesn't appear on dictionary table? How you can see I am the sys:

SQL> show user;
     USER is "SYS"

When I search the table in the dictionary the dictionary returns no rows:

select table_name from dictionary where table_name like '%AUDIT_UNIFIED_ENABLED_POLICIES%';

no rows selected

but if I issue the desc command I receive the structure of the table

desc AUDIT_UNIFIED_ENABLED_POLICIES
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USER_NAME                                          VARCHAR2(128)
 POLICY_NAME                                        VARCHAR2(128)
 ENABLED_OPT                                        VARCHAR2(7)
 ENABLED_OPTION                                     VARCHAR2(15)
 ENTITY_NAME                                        VARCHAR2(128)
 ENTITY_TYPE                                        VARCHAR2(7)
 SUCCESS                                            VARCHAR2(3)
 FAILURE                                            VARCHAR2(3)

Why does this happen?

I do not know any proper explanation. It has always been like this. Maybe Oracle was lazy, forgot to update the DICTIONARY view and noone even bothered to report this. Maybe there is a real reason for this.

If you check the definiton of the view DICTIONARY (DICT is a synonym for that), you can see it is a union of 3 branches (simplified):

ALL_*, DBA_*, USER_* views
A specific list of other views
Some others views that have public synonyms created on them with a different name (like DICTIONARY and DICT)

The problem is with 2. in my opinion. These newer views do not follow the regular naming schema and they should be included in this list.

I had the same experience with views related to Data Redaction.

Views such as REDACTION_POLICIES or REDACTION_COLUMNS are also not listed in DICT/DICTIONARY.


FULL recovery and differential backups I am going to begin transitioning several of our databases to FULL recovery from SIMPLE.  I asked a question last week regarding deployment strategies and I have one additional question.

Consider this:

Sunday (midnight) - Run FULL backup
Monday-Saturday (midnight) - Run differentials
Hourly - Transaction log backup

Are the differential backups basically eliminating the need for the transaction logs for that day?

So, if we have a catastrophic failure at 10pm on Tuesday, we would:

Run tail log backup on affected DB(s)
Restore FULL backup from Sunday night
Restore DIFF backup from Monday night
Restore using log chain from the day on Tuesday up to disaster time.

If this is the case, then could we simply remove the transaction log backups from Monday as part of the maintenance plan, since we have a differential from Monday night?  Would it be smarter to keep them for redundancy in case the DIFF backup fails?

There are multiple ways to get to the intended target recovery point with your setup.

A few things:

You cannot eliminate log backups for the most important reason of transaction log re-use.
It's not possible to know when an issue will occur and thus having multiple ways to get to the RPO is useful and sometimes necessary.
Differentials aren't a substitute for log backups, they will lower the RTO as they are much faster to apply than log backups but ultimately aren't worth much on their own (spanning broken lsn points, etc, is useful)
How are you going to know which ones you need and don't need ahead of time?

I would not stop taking log backups but you could put into place an aging mechanism in your backup software or process to eliminate files that are no longer needed.

Using your example, you have many paths but the basis is as such:

Always take a tail of the log backup first

Restore the full backup, latest differential, all logs.
Restore the full backup, all transaction logs.

This is more flexible as a corrupt backup file (say a log file) could be spanned by a differential or a corrupt full could be spanned by differentials, etc.

I would not change how your strategy is currently, but I would make sure it meets your RPO and RTO requirements.


Imported database very slow on a simple query A production database has been exported to a separate location for various reasons, however the dumped/imported database is performing much slower on some very specific queries. Here is 2 queries on production:

mysql> SELECT count(id) FROM payments;
+-----------+
| count(id) |
+-----------+
|    964390 |
+-----------+
1 row in set (0.14 sec)

mysql> SELECT count(id) FROM payments WHERE is_held = True;
+-----------+
| count(id) |
+-----------+
|         0 |
+-----------+
1 row in set (0.32 sec)

meanwhile the imported database:

mysql> SELECT count(id) FROM payments;
+-----------+
| count(id) |
+-----------+
|    963770 |
+-----------+
1 row in set (0.20 sec)

mysql> SELECT count(id) FROM payments WHERE is_held = True;
+-----------+
| count(id) |
+-----------+
|         0 |
+-----------+
1 row in set (37.20 sec)

So when including a non-indexed boolean in the WHERE-clause, the performance of the imported database drops by over 100 times.

An explain for the queries shows the same result in both databases:

mysql> explain SELECT count(id) FROM payments WHERE is_held = True;
+----+-------------+----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+--------+---------------+
|  1 | SIMPLE      | payments | ALL  | NULL          | NULL | NULL    | NULL | 739335 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+--------+---------------+
1 row in set (0.00 sec)

I've tried an OPTIMIZE payments; run on the imported database, it didn't help, though the output was:

mysql> OPTIMIZE TABLE payments;
+----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op       | Msg_type | Msg_text                                                          |
+----------+----------+----------+-------------------------------------------------------------------+
| payments | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| payments | optimize | status   | OK                                                                |
+----------+----------+----------+-------------------------------------------------------------------+
2 rows in set (2 min 13.01 sec)

The database is hosted in Amazon RDS, so my access to slow query logs and looking at hardware etc is a bit limited.

Being on RDS, I've tried upsizing the instance type a couple times, which I believe will move the database to new hardware, and in each case there's been no significant improvement.

I've run this query many times over the last few days, but now when I run it twice in a row it finishes instantly, and I can start a new MySQL connection and it is instant from the first run. I can even scroll up my terminal window and observe multiple earlier runs where it was slow, though in each case delineated by an RDS instance resize.

The reason I investigated this in the first place is a scheduled task hits this every 30 seconds and the scheduled tasks were starting to overlap and consume all my workers from other tasks so it was definitely recurring regularly before, not just first run.

I've re-enabled the scheduled task again, and it seems that the first run was slow, 33 seconds, but the second run was 2 seconds.

Here are the principles.

(I assume id is the primary key of the table and the table is InnoDB.)
SELECT count(id) FROM payments; will read the entire table.  Or it may read some secondary index, which necessarily includes the PK.
The first time you touch a table, the data is sitting on disk must be read into RAM for processing.  Since it needs to read the entire table, that could take some time.
If you run another query on the same table, it can skip the I/O.  This is often a factor of 10 faster.
If the first query used a secondary index, but the WHERE is_held = True needs was not involved in that index, then this query will need to read the entire data BTree.  One of your tables apparently had it cached already (0.32s) and the other did not (37s).
Don't use COUNT(id) unless you need to avoid counting any rows with id IS NULL, which cannot happen.  Instead, use simply COUNT(*).
INDEX(hash_id, id) would make some of your queries run faster because they could run entirely in that index's BTree.
Upgrading/downgrading the server is unlikely to change performance -- unless the table is bigger than the buffer_pool.  In this case, the table cannot be cached to make the second query fast.

For further discussion, please provide SHOW CREATE TABL, SHOW TABLE STATUS, and the value of innodb_buffer_pool_size.


How to, efficiently, search through many sql dump files of really big size We have an ads website that the uncompressed(plain .sql file) size of the database is about 30gb. Until now when a user was deleting his classified the classified was completely getting wiped out from the database.We were always taking a full backup of the database every day and thus we have hundreds of this 30gb sql files . Sometimes there are users that asks us to give them some info about a classified that was deleted like 2 months ago(they usually dont know the exact date) and we have to search thought this back up files in order to find it.Is there a tool designed specifically for this task? I dont mean just a text editor that can handle big files , I mean a tool that can actually load the full database and let us do queries on it(maybe just simplified queries that involve only one table) and maybe even let us search many of the backup files at the same time(and no, loading the database in a sql server is not an option too since we need to quickly search thought tens of this backups).

ApexSQL Restore will mount the backup as virtual Database, and you can query it using SSMS.

I also published a PowerShell script that will let you analyze your backups in a given folder. I wrote it for analyzing size but you could easily adjust this to include properties like the date taken and such to find which backup you need to mount. You can find the script and article around it here.

I have not played with the Apex tool extensively but do not believe it will search all backups in a folder for you to find the one with the specific data you need. You would have to mount at most the full and then all the logs to get it to the date you need.

I have never seen one that does exactly what you are looking for, but the above should help I think.


which one is more efficient query? I have a table called STUDENT

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

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

  a) select top 1 * from STUDENTorder by EnrollmentDate Desc

or

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

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

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

The second query does not necessarily do this, depending on the data type of the EnrolmentDate column and how granular the data is. If this column does not contain a time component, the query will return all students registered on the last day a student registered, which does not satisfy the question. If there is a time component, it's possible (but much less likely) that there will be multiple rows returned.

(Edit: Alex Kuznetsov correctly pointed out in the comments that the first query doesn't necessarily return the last enrolled student either when there are ties. It is, however, guaranteed to return a record in that event, instead of all records, which is normally satisfactory. I think my point was more that comparing the two queries is comparing apples to oranges, so to speak.)

In any event, if we assume all enrollment dates/times are unique, from what's given, the answer to the question isn't necessarily clear cut either. You would need to qualify for me what you mean by more efficient.

The first query will only scan once, but could potentially incur an expensive sort (you didn't say which indexes exist on the table, so I assume none). The latter query will do a scan to find the maximum, then do another scan to find all matching rows, which would possibly use less CPU, but more logical I/Os. It's entirely possible the second query would be less expensive overall (again, with no indexes available).

Having said all that, if I was to start doing performance tuning on this business operation, I would most certainly start with query (a).


MySQL Cluster - partitions on disk I have question about MySQL Cluster:
Is it possible to have database larger than amount of RAM? (to not have all cluster data in-memory, but some data will reside on clusters nodes hard disks)
Especially, is it possible to point to MySQL Cluster which partitions doesn't have to be in-memory and can be stored on disks. (in case of lack of memory).

I am only asking, if solution like this is possible to achieve with MySQL Cluster.

MySQL Cluster FAQ states that it is possible to have a database bigger than available memory, but the documentation referenced from the FAQ entry states that only non-indexed columns of NDB tables can be stored on disk.


Which indexes for joining a table onto itself on two columns I am using SQL Server 2016, and trying to join a table onto itself, but the query is very slow, and it seems its because SQL Server is refusing to use the indexes.

If you'll excuse the crudity of this example, I have a table like this...

TABLE [Balances] (
    account_id NCHAR(20),
    balance_date DATE,
    balance INT,
    next_balance_date DATE
)

... and I essentially want to do this query:

SELECT [t1].*, [t2].[balance] [next_balance]
FROM [Balances] [t1]
LEFT JOIN [Balances] [t2]
ON [t1].[next_balance_date] = [t2].[balance_date]
AND [t1].[account_id] = [t2].[account_id]

The problem is that no matter what index I try to put onto this table, SQL Server insists on doing a hash match. Which is slow on this large database.

And I currently have the following indexes:

NONCLUSTERED INDEX [idx_acc_date] ON [Balances] ([account_id] ASC, [balance_date] ASC)
NONCLUSTERED INDEX [idx_acc_nextdate] ON [Balances] ([account_id] ASC, [next_balance_date] ASC)

What indexes should I be creating to speed-up this query?

(As a small note on the data - all of the balance_dates are as at month end, and therefore the same goes for the next_balance_date. Also, each account_id only has one entry at each balance_date.)

There are 2 options:
1. Create clustered index by account_id. Delete all other indexes.
2. Add new column Balance_ID INT IDENTITY(1,1) and create clustered index on that column. Create another non-clustered index on ONLY one column account_id.

The second option is more preferable because it is much faster. You join INT only by 4 bytes. While your current queries join by 40 bytes. That means your current operation is 10 times more expensive.

Also, ask yourself couple of questions:
- Do you really need have Account_ID as Unicode?
- Can you convert Account_ID to INT or BIGINT?
Hopefully, you've got my point.

ADDITION:

You create clustered index on only one column. Technically, you can do it on multiple, but the main goal to have it as short as possible.
If you created clustered index on account_id you DO NOT NEED any other indexes on that column.
I can guess that your performance improved because before there was no Clustered index and indexes were so bad that SQL decided to do full table scan instead.
Suggestion: read a book about Indexes, their differences and how they work.


Moving MySQL data from RDS to RDS We have an RDS instance with about 200G of data in it.  A few issues cropped up on our end, and we need to both change the collation and charset of all our tables, as well as have all the data encrypted at rest.  RDS supports encryption, but you can't enable it on an already running instance, and can't restore un-encrypted snapshots.

That said, my current strategy is the following:

Create a new RDS instance with encryption enabled, and with the right charset/collation enabled in the associated ParameterGroup.
Take our application down, and start dumping the data from the old instance into the new instance with something like:

mysqldump -C -h xxxxxxx.rds.amazonaws.com --routines --triggers --single-transaction --quick --add-drop-database --default-character-set=utf8mb4 --disable-key -u user -pxxxxxx --ssl_ca xxxx mydatabase | mysql -u user -pxxxxx -h xxxxxx.rds.amazonaws.com --ssl_ca xxxx mydatabase

After data is in the new RDS instance, make all the necessary collation/charset changes to the database itself, tables, columns, change varchar sizes where needed, etc.

For the database:  ALTER DATABASE mydatabase CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci

For each table:  ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

... etc

My question is... is there any faster way to do this!?  I did a straight mysqldump from the primary database onto disk, and it took about 4 hours.  I haven't run all the ALTER TABLE changes yet (only on a small dataset), but I'm assuming those operations are going to take a ton of time.  All my tables are InnoDB tables if that helps.  I was looking at Percona xtrabackup, but realized I can't use that with RDS.

Any thoughts appreciated!

pt-online-schema-change -- It will take longer, but it will not interrupt other activity except briefly.


Unable to create Central Inventory I'm trying to install Oracle 11g on Linux and have already configured the variables and X-host but can't able to create Central Inventory.

My Central Inventory location is /u01/app/u0 and group name is dba.

It throw an error:

  error [INS-32033] Central Inventory location is not writable.

I have already tried chmod 755.

But the central inventory location should be /u01/app/oracle/oraInventory, not what you have. It is much better to follow the recommandations of Oracle for directories naming (see OFA section in installation guide).

check what is the id and the group of the user running the runInstaller, usually it is oracle:oinstall

then as user root

mkdir -p /u01/app/oracle
chown -R oracle:oinstall /u01/app/oracle (assuming oracle:oinstall)

before starting runInstaller make sure you don't have the env variable ORACLE_HOME set. You don't need to set ORACLE_BASE, but if you do then it should be pointing to /u01/app/oracle. So

export ORACLE_BASE=/u01/app/oracle

If the env variable ORACLE_BASE is not set, Oracle is clever enough to recognize /u01/app/oracle as the standard ORACLE_BASE location

Note: as a side note, standardize the id of user oracle and the gid of groups oinstall and dba accross all your system, i.e use the same values everywhere.


Oracle newly created DB is filled with datatables I've just created my first database with oracle database configuration assistant.

Basically nothing special, database was created successfully. What I don't understand is why are there so many tables already in the database? How to create empty database in oracle?

Just to give you an analogy. When you install Windows OS or Linux OS, would you have expected zero files on your filesystem? The same applies for the Oracle Database. After creation of an Oracle Database, there will be tons of objects (tables,functions,procedures,indices,packages,etc..) constitute your Oracle Database. All these objects are owned by SYS,SYSTEM,.. (there are many more).

These owners and objects come into play when you utilize different features of the database. Remember Oracle Database has been developed for +40 years and there is a vast amount of features!

Now that you want to create an empty database. Let's apply the OS analogy again. You would have created a new user... /home/myuser. This is exactly what you do in an Oracle Database as well.

An Oracle Database can handle thousands of users and schemas. (schema = owner of objects, user = manipulate already created objects (DML - select,insert,update,delete)).

-- *nix (example: export TWO_TASK=localhost:1521/SERVICE_NAME)
export TWO_TASK=MY_DB_SERVICE
-- Windows
set LOCAL=MY_DB_SERVICE

sqlplus system/password

-- this user is the schema. The owner of your objects.
create user myschema identified by &password
default tablespace USERS
temporary tablespace TEMP
quota unlimited on USERS;

-- grant privs to the schema
grant create session, resource to myschema;

-- yes, we need to take security into account.
create role myschema_ro;
create role myschema_rw;

-- lets login as myschema
sqlplus myschema/password

-- your database or schema is empty, now create all objects...
create table t (x int);

-- grant access to object
grant select on t to myschema_rw;

-- we are done with the creation of the schema

sqlplus system/password

-- no one is going to login as schema owner.
-- you only unlock this account when you do schema changes
alter user myschema account lock;

-- time to create your app-user-account who is going to access
-- the database/schema
create user myapp identified by &password;

-- grant privileges for your app-user
grant create session, myschema_rw to myapp;

-- login
sqlplus myapp/password

-- you will see your schema objects are listed
select object_name from all_objects;

-- now, if you want to avoid schema-prefixing (select * from myschema.t) there are some options
-- the first thing you do in your app
alter session set current_schema = MYSCHEMA;

-- now you can do: t is owned by myschema
select * from t;

-- you can create a trigger (log in as myschema)
create or replace trigger after_logon_trg
after logon on myschema.schema
begin
  execute immediate 'alter session set current_schema = MYSCHEMA';
end;
/

You are now ready to create an empty database ( = schema in Oracle terms) on your Oracle Database server (instance) where your objects are secured.

Just to clarify: A database is a set of files on disk with one or many schemas (apart from sys/system), an instance is a running system which is composed of the files on disk plus the in-memory structures which define a working piece of software.

Best of luck!


Recent Queries in SQL Server 2005 I'm using a PHP web application to interact with a database and need to catch an erroneous query. It's dynamically generated SQL and I don't know 100% of the code nor did I write it so I need to see what it's doing.

Using SQL Server Management Studio 2008 I've found it amazingly hard to find the last n executed queries on the database. I've searched up a storm but all solutions I've seen require "sql_handle" which is a feature not availible in my database's compatibility level, and to boot all solutions I've seen don't appear to catch dynamic queries, which this web application uses.

I was using this query:

SELECT TOP 50 * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName,
  execution_count,s2.objectid,
    (SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,
      ( (CASE WHEN statement_end_offset = -1
  THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)
ELSE statement_end_offset END)- statement_start_offset) / 2+1)) AS sql_statement,
       last_execution_time
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x
WHERE sql_statement NOT like 'SELECT TOP 50 * FROM(SELECT %'
ORDER BY last_execution_time DESC

But it unacceptably requires changing sp_dbcmptlevel in SQL Server 2005 (the search requires comptlevel at 90, I need it at 80), and in a recent case doesn't even return any dynamic queries run on the database; it's only returning the actions of stored procedures.

A similar solution with the same problems of compatability and incomplete results is:

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

This one returns a very small and very odd subset of queries, including the most recent query (usually itself) and a bunch of create procedure statements.

Is there any feature of SSMS or system database query that will allow me to view or capture recent queries? If I have to capture and save the query strings somehow that's acceptable as this is only in a test system.

SQL Server Profiler was designed to do this.

With it you can:

capture queries as they are executed on the target instance
capture other instance and database events like deadlocks, logins, and errors
filter captured queries by text in the query, login name, database name, and so forth


Get previous row and compare values I need to compare values of neighbouring rows in a table and get the number of times the value 42 of column calledstationid precedes the value 34. This is a system log table and calledstationid is not an auto-increment column.

I created this query but it has some problems:

SELECT COUNT(*) FROM (

    SELECT
        h.acctstarttime, h.acctstoptime, h.radacctid, h.calledstationid,
        (
            SELECT MAX(sub.calledstationid) FROM radius.radacct_historical as sub
            WHERE sub.callingstationid = '54:9F:13:27:31:7F'
            AND sub.acctstarttime < h.acctstarttime
        ) as previous,

    h.callingstationid

    CASE WHEN h.calledstationid = 34 AND previous = 42 THEN 'yes' END AS goal

    FROM radius.radacct_historical  AS h
    WHERE h.callingstationid = '54:9F:13:27:31:7F'
    ORDER BY h.acctstarttime ASC;

) as total WHERE goal = 'yes'

First problem:

(
    SELECT MAX(sub.calledstationid) FROM radius.radacct_historical as sub
    WHERE sub.callingstationid = '54:9F:13:27:31:7F'
    AND sub.acctstarttime < h.acctstarttime
) as previous,

The previous calledstationid is not returned properly. I get the correct value only in the first 2 results, thereafter it seems that MySQL stores the value of the result in memory and does not update the subquery.

Second problem:

CASE WHEN h.calledstationid = 34 AND previous = 42 THEN 'yes' END AS goal

The column name previous in the CASE expression is not recognised.

Could anyone shed any light on how to do this?

http://www.sqlfiddle.com/#!2/774ff/42/1

You have replaced the previous column reference in the CASE expression with the same subquery that you are using earlier to calculate the value of previous. While that certainly resolves the issue of referencing a calculated column at the same nesting level, duplication of code is far from an ideal solution.

You can avoid the duplication by abandoning the CASE expression completely as unnecessary. All it does is producing a "yes"/"no" result based on the values of the current and previous calledstationid only to be compared to "yes" at the outer level. Instead of checking on the value of achou, your outer level can be checking on the values of calledstationid and previous directly, like this:

SELECT COUNT(achou) as total
FROM (
    SELECT
        h.calledstationid,
        (
            SELECT calledstationid
            FROM radius.radacct_historical as sub
            WHERE sub.callingstationid = '54:9F:13:27:31:7F'
              AND sub.acctstarttime < h.acctstarttime
            ORDER BY sub.acctstarttime DESC
            LIMIT 1
        ) as previous,
    FROM radius.radacct_historical  AS h
    WHERE h.callingstationid = '54:9F:13:27:31:7F'
) as S WHERE S.calledstationid = 34 AND S.previous = 42;

As you can see, I have also removed other elements from the query, like most of the columns returned by the nested SELECT (acctstarttime, acctstoptime, radacctid, callingstationid – they neither participate in the output nor affect the count result) and the nested ORDER BY h.acctstarttime ASC (there is no need to sort rows before counting them).


Sybase ASE server request logs Is it possible to log each SQL query statement received from clients for a Sybase ASE server 15.7 ,  same as SET general_log = 1; command for mysql.
Thanks

you could enable the MDA tables (little performance overhead) then use first sp_monitor 'statement'. If it is not enough, you could select into the MDA tables to see the statement running in ASE server such as:

print "**** SQL of the 10 statements having the most CPU time used ****"

select top 10 a.KPID,a.BatchID,a.LineNumber,a.CpuTime,a.StartTime,b.SQLText
from master..monSysStatement a, master..monSysSQLText b
where a.KPID = b.KPID
and a.BatchID = b.BatchID
order by a.CpuTime desc

List of MDA tables:
http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc70002.1550/pdf/asemtpst.pdf