how to simulate a low-memory state when running a query

If you're on SQL Server 2012 SP3 or better, you can use the MAX_GRANT_PERCENT hint to cap a single query's memory grant. However, you'd have to mess with the number to figure out which percent gives you the correct value (to force GrantedMemory to be equal to RequiredMemory).

See here for more details.

Prior to that, you'd have to use Resource Governor (Enterprise only though).


The theory is you should isolate elements of diferent purpose, function or character, in this case fields like invoiceNum, make, model, location and type are normal candidates to live in their own tables, and have only a reference to their id numbers in this table, but it depends a lot on the application itself.

What you are asking for it's called 'Normalization', and you can get a lot of examples from the web:

https://blog.udemy.com/normalization-in-database-with-example/
https://en.wikipedia.org/wiki/Database_normalization

The simple advice is: yes, at least get 1st and 2nd normalization forms when possible, it will make your life easier in the future.

EDIT: I forgot to answer 'why' you should separate different purpose data in different tables: There are a lot of reasons, but even when better explained in the links provided, we can resume it with these points:

Your data will have less redundancy, you can point to a number instead of repeat the same string through thousands or millions of records.
Your data will be easier to modify: if you have to change 'Motorbike' by 'Water Motorbike' you will change it in one row only ( easier, and a lot faster in comparison ).
Your data will be more consistent, as you can have the same concept in different tables, and update all the 'Motorbikes' of one table, but those of other table. If you have normalized your tables, you can change it in one table and would affect all involved tables, not only that you can remember the data was in.
As it's more organized, insertions, updates and searches gets incredibly improved.

This is normally called ACID ( Atomicity, Consistency, Isolation, Durability ), the properties of a well designed database. I'm sure I forget some basic concepts, but there are a lot of reasons to use normalization.


SQL Server service start automatically only after Windows Shutdown, how can I make restart automatically without batch file?

You can set the service property to restart it (service properties --> recovery --> first / second / third failure and have a delay between each restarts e.g. 1 min). You should also, have the service set to automatic delayed start.

Alternatively, you can use powershell and schedule it in task manager to check if the service is running or not. If not running, restart it.

get-service and restart-service are your friends.


MySQL: update field with “x hours xx minutes”using int from another field

I have a single MySQL query that do all the heavy lifting

SELECT TimeDisplay FROM
(
    SELECT TRIM(REPLACE(CONCAT(IF(dy=0,'',
    IF(dy=1,'1 day ',CONCAT(dy,' days '))),
    IF(hr=0,'',IF(hr=1,'1 hour ', CONCAT(hr,' hours  '))),
    IF(mn=0,'',IF(mn=1,'1 minute ',CONCAT(mn,' minutes '))),
    IF(sc=0,'',IF(sc=1,'1 second ',CONCAT(sc,' seconds ')))),'  ',' ')) TimeDisplay
    FROM
    (
        SELECT dy,hr,mn,MOD(sec_aaaa,60) sc FROM
        (
            SELECT dy,hr,FLOOR((sec_aaa-dy*86400-hr*3600)/60) mn,sec_aaa sec_aaaa
            FROM
            (
                SELECT dy,FLOOR((sec_aa-(dy*86400))/3600) hr,sec_aa sec_aaa FROM
                (
                    SELECT FLOOR(sec_a/86400) dy,sec_a sec_aa
                    FROM (SELECT 243 sec_a) A
                ) AA
            ) AAA
        ) AAAA
    ) AAAAA
) AAAAAA;

When you cut and paste it, this is what it looks like

mysql> SELECT TimeDisplay FROM
    -> (
    ->     SELECT TRIM(REPLACE(CONCAT(IF(dy=0,'',
    ->     IF(dy=1,'1 day ',CONCAT(dy,' days '))),
    ->     IF(hr=0,'',IF(hr=1,'1 hour ', CONCAT(hr,' hours  '))),
    ->     IF(mn=0,'',IF(mn=1,'1 minute ',CONCAT(mn,' minutes '))),
    ->     IF(sc=0,'',IF(sc=1,'1 second ',CONCAT(sc,' seconds ')))),'  ',' ')) TimeDisplay
    ->     FROM
    ->     (
    ->         SELECT dy,hr,mn,MOD(sec_aaaa,60) sc FROM
    ->         (
    ->             SELECT dy,hr,FLOOR((sec_aaa-dy*86400-hr*3600)/60) mn,sec_aaa sec_aaaa
    ->             FROM
    ->             (
    ->                 SELECT dy,FLOOR((sec_aa-(dy*86400))/3600) hr,sec_aa sec_aaa FROM
    ->                 (
    ->                     SELECT FLOOR(sec_a/86400) dy,sec_a sec_aa
    ->                     FROM (SELECT 243 sec_a) A
    ->                 ) AA
    ->             ) AAA
    ->         ) AAAA
    ->     ) AAAAA
    -> ) AAAAAA;
+---------------------+
| TimeDisplay         |
+---------------------+
| 4 minutes 3 seconds |
+---------------------+
1 row in set (0.00 sec)

mysql>

The key to using this query is this section

FROM (SELECT 243 sec_a) A

Simply replace the 243 with totalTime and you are off and running.

Your Original Question

Would you like to see a very wicked UPDATE JOIN to fix all totalTimeDesc fields?

Sample Data

use test
drop table if exists mytimes;
create table mytimes
(
    id int not null auto_increment primary key,
    totalTime int not null,
    totalTimeDesc varchar(128)
);
insert into mytimes (totalTime) values (243),(2430),(24300),(243000);
SELECT * FROM mytimes;

Proposed UPDATE JOIN

UPDATE mytimes J INNER JOIN
(
    SELECT id,TRIM(REPLACE(CONCAT(IF(dy=0,'',
    IF(dy=1,'1 day ',CONCAT(dy,' days '))),
    IF(hr=0,'',IF(hr=1,'1 hour ', CONCAT(hr,' hours  '))),
    IF(mn=0,'',IF(mn=1,'1 minute ',CONCAT(mn,' minutes '))),
    IF(sc=0,'',IF(sc=1,'1 second ',CONCAT(sc,' seconds ')))),'  ',' ')) TimeDisplay
    FROM
    (
        SELECT id,dy,hr,mn,MOD(sec_aaaa,60) sc FROM
        (
            SELECT id,dy,hr,FLOOR((sec_aaa-dy*86400-hr*3600)/60) mn,sec_aaa sec_aaaa
            FROM
            (
                SELECT id,dy,FLOOR((sec_aa-(dy*86400))/3600) hr,sec_aa sec_aaa FROM
                (
                    SELECT id,FLOOR(sec_a/86400) dy,sec_a sec_aa
                    FROM (SELECT id,totalTime sec_a FROM mytimes) A
                ) AA
            ) AAA
        ) AAAA
    ) AAAAA
) K USING (id)
SET J.totalTimeDesc = K.TimeDisplay;

If you look carefully, I slipped in

FROM (SELECT id,totalTime sec_a FROM mytimes) A

in place of

FROM (SELECT 243 sec_a) A

and propagated the id column through subqueries

Sample Data Loaded

mysql> use test
Database changed
mysql> drop table if exists mytimes;
Query OK, 0 rows affected (0.01 sec)

mysql> create table mytimes
    -> (
    ->     id int not null auto_increment primary key,
    ->     totalTime int not null,
    ->     totalTimeDesc varchar(128)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into mytimes (totalTime) values (243),(2430),(24300),(243000);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mytimes;
+----+-----------+---------------+
| id | totalTime | totalTimeDesc |
+----+-----------+---------------+
|  1 |       243 | NULL          |
|  2 |      2430 | NULL          |
|  3 |     24300 | NULL          |
|  4 |    243000 | NULL          |
+----+-----------+---------------+
4 rows in set (0.00 sec)

mysql>

Proposed UPDATE JOIN Executed

mysql> UPDATE mytimes J INNER JOIN
    -> (
    ->     SELECT id,TRIM(REPLACE(CONCAT(IF(dy=0,'',
    ->     IF(dy=1,'1 day ',CONCAT(dy,' days '))),
    ->     IF(hr=0,'',IF(hr=1,'1 hour ', CONCAT(hr,' hours  '))),
    ->     IF(mn=0,'',IF(mn=1,'1 minute ',CONCAT(mn,' minutes '))),
    ->     IF(sc=0,'',IF(sc=1,'1 second ',CONCAT(sc,' seconds ')))),'  ',' ')) TimeDisplay
    ->     FROM
    ->     (
    ->         SELECT id,dy,hr,mn,MOD(sec_aaaa,60) sc FROM
    ->         (
    ->             SELECT id,dy,hr,FLOOR((sec_aaa-dy*86400-hr*3600)/60) mn,sec_aaa sec_aaaa
    ->             FROM
    ->             (
    ->                 SELECT id,dy,FLOOR((sec_aa-(dy*86400))/3600) hr,sec_aa sec_aaa FROM
    ->                 (
    ->                     SELECT id,FLOOR(sec_a/86400) dy,sec_a sec_aa
    ->                     FROM (SELECT id,totalTime sec_a FROM mytimes) A
    ->                 ) AA
    ->             ) AAA
    ->         ) AAAA
    ->     ) AAAAA
    -> ) K USING (id)
    -> SET J.totalTimeDesc = K.TimeDisplay;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> SELECT * FROM mytimes;
+----+-----------+----------------------------+
| id | totalTime | totalTimeDesc              |
+----+-----------+----------------------------+
|  1 |       243 | 4 minutes 3 seconds        |
|  2 |      2430 | 40 minutes 30 seconds      |
|  3 |     24300 | 6 hours 45 minutes         |
|  4 |    243000 | 2 days 19 hours 30 minutes |
+----+-----------+----------------------------+
4 rows in set (0.00 sec)

mysql>

UPDATE 2014-07-23 16:20 EDT

I just realized, your units are in minutes not seconds

Here is the quick fix

UPDATE mytimes J INNER JOIN
(
    SELECT id,TRIM(REPLACE(CONCAT(IF(dy=0,'',
    IF(dy=1,'1 day ',CONCAT(dy,' days '))),
    IF(hr=0,'',IF(hr=1,'1 hour ', CONCAT(hr,' hours  '))),
    IF(mn=0,'',IF(mn=1,'1 minute ',CONCAT(mn,' minutes '))),
    IF(sc=0,'',IF(sc=1,'1 second ',CONCAT(sc,' seconds ')))),'  ',' ')) TimeDisplay
    FROM
    (
        SELECT id,dy,hr,mn,MOD(sec_aaaa,60) sc FROM
        (
            SELECT id,dy,hr,FLOOR((sec_aaa-dy*86400-hr*3600)/60) mn,sec_aaa sec_aaaa
            FROM
            (
                SELECT id,dy,FLOOR((sec_aa-(dy*86400))/3600) hr,sec_aa sec_aaa FROM
                (
                    SELECT id,FLOOR(sec_a/86400) dy,sec_a sec_aa
                    FROM (SELECT id,totalTime*60 sec_a FROM mytimes) A
                ) AA
            ) AAA
        ) AAAA
    ) AAAAA
) K USING (id)
SET J.totalTimeDesc = K.TimeDisplay;
SELECT * FROM mytimes;

I changed

FROM (SELECT id,totalTime sec_a FROM mytimes) A

to

FROM (SELECT id,totalTime*60 sec_a FROM mytimes) A

If you rerun the UPDATE JOIN, you get this

mysql> UPDATE mytimes J INNER JOIN
    -> (
    ->     SELECT id,TRIM(REPLACE(CONCAT(IF(dy=0,'',
    ->     IF(dy=1,'1 day ',CONCAT(dy,' days '))),
    ->     IF(hr=0,'',IF(hr=1,'1 hour ', CONCAT(hr,' hours  '))),
    ->     IF(mn=0,'',IF(mn=1,'1 minute ',CONCAT(mn,' minutes '))),
    ->     IF(sc=0,'',IF(sc=1,'1 second ',CONCAT(sc,' seconds ')))),'  ',' ')) TimeDisplay
    ->     FROM
    ->     (
    ->         SELECT id,dy,hr,mn,MOD(sec_aaaa,60) sc FROM
    ->         (
    ->             SELECT id,dy,hr,FLOOR((sec_aaa-dy*86400-hr*3600)/60) mn,sec_aaa sec_aaaa
    ->             FROM
    ->             (
    ->                 SELECT id,dy,FLOOR((sec_aa-(dy*86400))/3600) hr,sec_aa sec_aaa FROM
    ->                 (
    ->                     SELECT id,FLOOR(sec_a/86400) dy,sec_a sec_aa
    ->                     FROM (SELECT id,totalTime*60 sec_a FROM mytimes) A
    ->                 ) AA
    ->             ) AAA
    ->         ) AAAA
    ->     ) AAAAA
    -> ) K USING (id)
    -> SET J.totalTimeDesc = K.TimeDisplay;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 4  Changed: 0  Warnings: 0

mysql> SELECT * FROM mytimes;
+----+-----------+---------------------------+
| id | totalTime | totalTimeDesc             |
+----+-----------+---------------------------+
|  1 |       243 | 4 hours 3 minutes         |
|  2 |      2430 | 1 day 16 hours 30 minutes |
|  3 |     24300 | 16 days 21 hours          |
|  4 |    243000 | 168 days 18 hours         |
+----+-----------+---------------------------+
4 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


Single sql query to get the transpose of a matrix

All you need to do is swap the indexes. Original:

with A as
(
  select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, rownum as val from dual connect by level <= 9
)
select
  *
from
  A
order by
  1, 2
;

  I   J VAL
--- --- ---
  1   1   1
  1   2   2
  1   3   3
  2   1   4
  2   2   5
  2   3   6
  3   1   7
  3   2   8
  3   3   9

Transposed:

with A as
(
  select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, rownum as val from dual connect by level <= 9
)
select
  j as i,
  i as j,
  val
from
  A
order by
  1, 2
;

  I   J VAL
--- --- ---
  1   1   1
  1   2   4
  1   3   7
  2   1   2
  2   2   5
  2   3   8
  3   1   3
  3   2   6
  3   3   9


Why does copied MySQL database have different data than source?

Honestly, I expected you to say MyISAM since I've never seen InnoDB generate a backup that seems inconsistent with what you think is in the table.

As a DBA, I don't trust my data to anything that I can't watch while it's working and peer into its intermediate stages, so I rarely use GUI tools.  My approach to this issue would be to use mysqldump to extract the data into a file and then review that file manually.  Satisfied with the sanity of it, I would load it onto the target server with the mysql command line client and see what the results are.

The specific option I'd suggest with mysqldump in this case would be --skip-extended-insert, which generates one insert statement for each row row in each table.  This is in contrast to the normal format, which combines groups of rows into a smaller number of insert statements as an optimization for faster restoring... a file created with this option will restore somewhat more slowly than one created without it, but the tradeoff is that the files are substantially easier to parse with your eyeballs and see what the data looks like during this intermediate stage of the process.

Since my bet on MyISAM didn't pan out so well, I'm really unsure whether to put money on the dump file looking right and some kind of strangeness on the target server to scramble it up on the way in... or for the dump file to come out looking wrong, though I really will be surprised if the dump file comes out looking wrong... but then again, if the dump file looks right, I'd be pretty surprised if it doesn't restore correctly.

After reviewing the file, if the content looks right (i.e., find the line in the file that represents the 438/2255 row you mentioned above, and see which version it represents... and make sure there's only one such line in the dump file) load it onto the target server and see what the results look like.  If the contents of the dump file don't look right, then of course, restoring it would be unnecessary and we need to look at the origin server for something strange.


mysqldump: Got error: 1066: Not unique table/alias: 'temp1' when using LOCK TABLES

This is may be you are having temp1 table with same name but the cases may be different.
Check the value of lower_case_table_names parameter in your instance.
Rename the same name table with unique name.
select table_schema,table_name
from information_schema.tables
where lcase(table_name)='temp1';


Try:

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

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


How to limit select output from sqlplus?

I don't think you can. The only thing I know that sort of does that (but is slightly annoying IMO) is the PAUSE option which waits for you to press enter between pages. The annoying part is that it pauses before the first row... You can Ctrl C to interrupt when you've seen enough.

SQL> set pause on
SQL> set pages 10 pause 'press enter to continue'
SQL> select object_name from dba_objects;
press enter to continue

OBJECT_NAME
--------------------------------------------------------------------------------
ICOL$
I_USER1
CON$
UNDO$
C_COBJ#
I_OBJ#
PROXY_ROLE_DATA$
press enter to continue

OBJECT_NAME
--------------------------------------------------------------------------------
I_IND1
I_CDEF2
I_OBJ5
I_PROXY_ROLE_DATA$_1
FILE$
UET$
I_FILE#_BLOCK#
press enter to continue

I_FILE1
ERROR:
ORA-01013: user requested cancel of current operation

15 rows selected.


Number of logical reads in SQL server JOIN

There are cases where every single row from your larger table could be read, and cases where it is closer to the number from your smaller table.  SQL Server will pick the method it thinks is most efficient based upon the information it has.
For example, if you have an index on the larger table that includes the join column, there is likely going to be less reading, as it should be able to skip a bunch of records.  If you don't, then the only way it will know what rows to select is by reading each row.  Keep in mind that statistics will impact the choices made, and if they are out of date it could lead to a bad choice.
Note there are implications for adding indexes, and there are numerous cases where they hurt over all performance, versus increasing it, so don't go willy-nilly in creating them.  I'd say ask your DBA, but...
Personally, I would question if you want to retain your DBA.  An INNER JOIN (which is what you have, with the INNER omitted) is a filter and in many (but not all) cases you can move predicates between the WHERE clause and the ON clause with zero impact (SQL Server will optimize to the exact same query plan), so the "because there is no WHERE clause" is not only wrong, but in fundamentally misunderstand sort of way.
This is not to say your product team is correct. Looking at the screen shot, there are clearly more reads than 9K. If your products team is in Marketing, then I can understand their confusion (<grin>).
Lastly, the NOLOCK in there should be a big red flag.  There are cases where it won't cause a problem, but one should be able to clearly be able to articulate what the problems are, and why it won't be an issue before using it.


What happens if a delete query times out SQL Server for php?

When a timeout occurs, the client API sends an attention signal to SQL Server to cancel the executing query. Upon receipt, SQL Server cancels the query and rolls back the transaction. Data modification statements are run in autocommit mode by default so each statement is all-or-none.


Create Range Partition on existing large MySQL table

Unfortunately, no shortcut.

Do have an empty partition for "future" data; REORGANIZE to tomorrow and a fresh future.  More discussion.

The ALTER will copy the entire table over, so be prepared for not being able to use the table for some time.


Logging to a file in PostgreSQL windows service

Locate your postgresql.conf in the datadir

Find the section that looks like this

#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------

# - Where to Log -

#log_destination = 'stderr'             # Valid values are combinations of
                                        # stderr, csvlog, syslog and eventlog,
                                        # depending on platform.  csvlog
                                        # requires logging_collector to be on.

# This is used when logging to stderr:
#logging_collector = off                # Enable capturing of stderr and csvlog
                                        # into log files. Required to be on for
                                        # csvlogs.
                                        # (change requires restart)

# These are only used if logging_collector is on:
#log_directory = 'pg_log'               # directory where log files are written,
                                        # can be absolute or relative to PGDATA
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'        # log file name pattern,
                                        # can include strftime() escapes
#log_truncate_on_rotation = off         # If on, an existing log file of the
                                        # same name as the new log file will be
                                        # truncated rather than appended to.
                                        # But such truncation only occurs on
                                        # time-driven rotation, not on restarts
                                        # or size-driven rotation.  Default is
                                        # off, meaning append to existing files
                                        # in all cases.
log_rotation_age = 7d                   # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.
#log_rotation_size = 10MB               # Automatic rotation of logfiles will
                                        # happen after that much log output.
                                        # 0 disables.

simply uncomment and use these parameters. Then, restart the postgres service,

Since you are running postgresql in Windows, it is possible that you may not be allowed to edit postgresql.conf while the service is up. If that is the case:

shutdown the postgres service
edit postgresql.conf
start the postgres service

Give it a Try !!!


mysql inner join producing more results than it should

Why are you using two syntaxes, old fashioned verbs, roles and then an explicit inner join? Makes the query very hard to read and since you don't have any where clause that defines the relationship between verbs and roles (this is one of the biggest problems with using old fashioned syntax), you essentially have the cartesian product of those first two tables, and this probably explains your row counts.

You need to define the join columns between verbs and roles and, preferably, put that into an ON clause with a proper INNER JOIN. E.g.:

SELECT r.name, v.name, p.enabled
FROM verbs AS v
INNER JOIN privileges AS p
  ON v.id = p.verb_id
INNER JOIN roles AS r
  ON p.role_id = r.id;


Your question amounts to, "How do I use indexes?" You should read some Oracle documentation to get up to speed on how to use indexes and how to determine what columns to index. Oracle has an entire library of free online books and documentation for their products. A few good links to start:

The chapter on Indexes and Index-organized Tables from Oracle Database Concepts (the entire book is definitely worth reading)
The Oracle Database Performance Tuning Guide on Choosing Columns and Expressions to Index
Using Indexes in Database Applications from the
Oracle Database Advanced Application Developer's Guide


execute as login for nonexistent login successful

As Erland marked in https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ad6a80b1-717c-4d08-ae51-2eb029e59738/execute-as-login-for-nonexistent-login-successful?forum=sqldatabaseengine#ad6a80b1-717c-4d08-ae51-2eb029e59738

Yes, you can do EXEUTE AS LOGIN on a Windows login which exists in the AD, even if that login does not have permission to log into SQL Server.

The topic for EXECUTE AS in Books Online, includes this caution box:
The EXECUTE AS statement can succeed as long as the Database Engine can resolve the name. If a domain user exists, Windows might be able to resolve the user for the Database Engine, even though the Windows user does not have access to SQL Server. This can lead to a condition where a login with no access to SQL Server appears to be logged in, though the impersonated login would only have the permissions granted to public or guest.


Query to compute unspecified values in "Other" category

The following query would accomplish what you're looking for:
SELECT name, category, percentage
FROM expense_profile
WHERE name = 'Bob'

UNION ALL

SELECT name, 'Other' AS category, 100.0 - SUM(percentage) AS percentage
FROM expense_profile
WHERE name = 'Bob'
GROUP BY name


How to backup full transaction logs for SQL Server 2008 R2

Does the ALTER DATABASE XYZ SET RECOVERY FULL imply that you are normally running in SIMPLE recovery model?

If so, the logs from the SIMPLE mode period are discarded once they are no longer needed.  There is no time machine to go back and recover the logs that were not saved.  So, to interpret your script:

ALTER DATABASE XYZ SET RECOVERY FULL; -- Begin saving logs
BACKUP DATABASE XYZ TO DISC = foo.bak;-- Full backup gives the log a start point
BACKUP LOG XYZ TO DISC = foo.bak; -- The logs that start with the full can now be backed up.

You can get the full logs by keeping the database in the FULL recovery model at all times.

Also the statistics are not stored in the log files but in the database.


Fetching wrong results on comparing a variable with 0 in unix

You may consider something like:

#!/bin/bash
...
cnt=$(psql -h localhost -U postgres -d $DB_name -At -c "SELECT COUNT(*) AS USER_COUNT FROM pg_roles WHERE rolname = '${user}' ")

if [[ ${cnt} -eq 0 ]]
then
    echo 'New User ' $user
else
    echo 'User Exists ' $user
fi

The error you got saying unary operator expected is indeed because the variable is unset. There are various ways to handle that, but in this case, I think it should be fine


How to create Buffer Pool in Database dedicated only for ONE BIG table?

Create the new bufferpool.
Create a new tablespace that is associated with that bufferpool.
Create a copy of the table you want and place it in the new tablespace.
Copy the data from the original table to the new table.
Drop the old table.

While you can associate tablespaces with different bufferpools using the ALTER TABLESPACE statement, tables can only be associated with one tablespace. You can't issue ALTER TABLE and switch the tablespace. So you unfortunately have to create a new table and move the data from one to the other.

EDIT: Also note that any indexes you have associated with the old table will also get dropped and have to be re-created.

EDIT: Code Example, note for this I am assuming a 4K tablespace. Please substitute your correct size if need be (ie, 4, 8, 16, or 32 K).

db2 create bufferpool TICKET_BP immediate size 250 automatic pagesize 4k;
--note I list automatic storage here. We prefer to use that. Plus, IBM is pushing to move
-- in that direction. This TS is for the table.
db2 create large tablespace TICKETTS pagesize 4k managed by automatic storage bufferpool TICKET_BP;
--this TS is for indexes.
db2 create large tablespace TICKET_IND_TS pagesize 4k managed by automatic storage bufferpool TICKET_BP;
db2 create large tablespace TICKET_LOB_TS pagesize 4k managed by automatic storage bufferpool TICKET_BP;

-- now you can run the stored proc to move the data. It essentially does the same thing as what I specified above, except that it can move the data during normal work loads, so it should have less impact on locking and such, but it may take longer I'm guessing.
db2 "call syscproc.admin_move_table('MAX','TICKET','TICKETTS','TICKET_IND_TS','TICKET_LOB_TS','','','','','','MOVE');

And yes, most definitely take backups before and after. Also note that if you allow any transactions during the data movement period you may lose those transactions if you have to rollback.


It seems like the obvious answer is that you can't stop a shutdown that is happening, but you can do either a shutdown immediate or shutdown abort from a different session if you did a shutdown normal. As long as you have enough online redo log groups and each group has a log that is large enough, then you can recover from either a shutdown immediate or shutdown abort. If you ask someone what is the exact risk that someone faces for doing a shutdown abort, they may struggle to answer you. On shutdown immediate transactions are rolled back as sessions are killed. On shutdown abort, all transactions are rolled back when the database is started up. Hence the same thing should be happening in both cases, just in a different order. Once you start a shutdown normal, either ask everyone to log out, or just do shutdown immediate in a different session. Either way, Oracle won't allow a database to open, if it isn't in a consistent state.


You need to have a rentals table which has the following minimum columns:

id - unique identifier for the rental an autoincrement field
roomid - FK to the id of the room
startdate and enddate - of the rental
weekcoefficient - computed from either the startdate or enddate to be enable a lookup to week coefficient cost table (while its not adviseable to store computed columns, this may be necessary for performance reasons)
buildingid (optional) - FK to the id of the building (allows a direct lookup from the rental to the building without having to go through room table for performance reasons)
categoryid (optional) - similar explanation to the buildingid above to lookup the category coefficient table. This column may also be useful later in case the categories of the building change over time as it tracks the historical record for the category of the building at the time of allocation

UPDATE: To cater for the computation of the room coefficients with dependence on the building in which the room is located

To compute the different room coefficients you can have either:

a table with 3 columns: a number corresponding to output from WEEKOFYEAR function on a specific date, buildingid,  the coefficient (will have 52 values x the number of buildings)
Write a stored function which takes a date and matches the coefficient if there are few values specified by a range or if the combinations have some logic to them based on other features or data (any more than 10 different combinations makes this a nightmare)

Either approach above will require you to save the coefficient in the rentals table for historical reporting/auditing purposes and also maintain a note on what data was used for computation at that time


Oracle Export using EXPDP gives ORA-06512 SYS.UTL_FILE error on Windows 7

I just wanted to chime in and share what got me past this error.  In my case (Ora 11.2.0.3 on Win 7) I found I needed the trailing '\' in my directory path.

Replaced:

CREATE OR REPLACE DIRECTORY dpump_dir AS 'C:\Data\SID'

With:

CREATE OR REPLACE DIRECTORY dpump_dir AS 'C:\Data\SID\'


mongodb: replica-set processing reads on the primary

I'm not sure what documentation you've read so apologies if I'm repeating anything here.

To distribute reads to secondary nodes, most drivers allow you to set a readPreference value for the current session. Clients set read preference on a per-connection basis. With slaveOk, the driver should will always send queries to the secondaries, if they're available.

Distributing reads to secondaries requires the use of
ReplicaSetConnection with ReadPreference.SECONDARY.

See “rs.slaveOk()” for more information and this link.

In the mongo shell, to enable secondary reads, issue the following command :

rs.slaveOk()

The PHP documentation for it is here but I'm guessing that may be the documentation you're referring to.

As a FYI, here's an old discussion about it on the MongoDB Google Group.

If you're still having issues, I'd recommend using the MongoDB Google Group and providing some further information such as the version of MongoDB you're using, the version of the PHP driver, your log files, rs.conf() and rs.status().

As a FYI, you have to be careful with read scaling as sending too many reads to the secondaries can often result in the secondaries lagging the primary and becoming stale, thus requiring a full resync.


Use the built-in function pg_postmaster_start_time

select current_timestamp - pg_postmaster_start_time() as uptime

More details in the manual: http://www.postgresql.org/docs/current/static/functions-info.html

If you want seconds, use extract:

select extract(epoch from current_timestamp - pg_postmaster_start_time()) as uptime


Here is a way of How to calculate the Result Set data size

If using  Management Studio:
Go to Menu >> Query >> Include Client Statistics

Once you enable the client statistics, it will display another tab in the result span; when clicked, we obtain the following:

As shown in the image, it will capture details for different trials and they are compared with each other. Green arrows indicate improved statistics and red arrows indicate degrading statistics. Please note that here an increase does not always imply a good result; sometimes some statistics when marked as “up” can imply a bad result as well. For the same reason, they are colored in green and red, signifying good and bad results, respectively.


Database Hardware and Software to handle million requests per second?

It really isn't a database platform problem. All the major databases and operating systems will do fine provided sufficient hardware with sufficient bandwidth to memory, disk, and network are provided.

All databases are built for this kind of scenario -- that is, where you need to update lots of rows from different clients all the time. This is what indexes and primary keys are used for, and the database is optimized for updating in this manner. (i.e., UPDATE your_table SET your_column=your_value where your_key=12)

The hardware requirements are going to be your biggest issue, and I suspect you will need to think about quite a lot here, including:

Failover (what happens when your main server fails?)
Clustering (You may simply need to have more than one database server to handle your load)
Processors (2? 4? 8? 16? Multi-core? Does the db provide multi-core support that is well optimized?)
Memory (The faster the better, but your chipset must be able to handle the bandwidth as well)
Disk (Faster I/O the better. eSATA/SATA or Fiber, etc.)
Network (You'll need lots of bandwidth to handle this kind of traffic)
Virtualization (Does it make sense to build this as real hardware? Why not as virtual servers in the cloud? Amazon / Rackspace / etc.?)

Thankfully a good majority of the scaling issues are handled either at the hardware or db level. That is, you can start your database now on a slow server and as traffic increases you can adjust accordingly with minimal changes to your code or your db structure. What will change is your hardware and your db configuration. Most database servers will support failover/clustering with little to no change to your existing code or structures. (But be sure to research possible costs and the efficiency thereof first)


How can I represent a list of words and related words in a relational structure?

That is pretty much how you have to do it. The relationship table for a many-to-many link like that is often called a junction table (though goes by many other names on occasion: map table, mapping table, bridge table, ...).

If you have more than one class of relationship between words (abbreviation, antonym, synonim, ..) than that should be in the junction table too.

If you don't have multiple relationship types then there may be no need to have WordRelationshipId: the most efficient way to store what you currently have in SQL Server is just HeaderWordId, WordID with the two columns together being the primary key and that key being clustered (though as a matter of style/consistency some people prefer to have a separate surrogate key for all entities, and some frameworks baulk at composite PKs, so YMMV).


Tom Li- MSFT described a solution to what sounds like your problem, but his solution was for SQL Server 2008.  (But just how much has the installer been improved since then?)

https://social.msdn.microsoft.com/forums/sqlserver/en-US/f009e660-503a-4569-a92d-9cc2ef790ca3/install-failure-certificate-chain-trust

Key points from his post include:

This certificate is used as a mechanism to protect SQL Server's login information(including password) when using SQL authentication.

By default this self-signed certificate should only affect the login information and only when using SQL authentication.

Edit: My certificate name usually matches the SQL Server's login account.
Export the certificate and delete it by using "certmgr.msc". Then run "setup.exe" to install SQL Server 2008 again.

You also can add this certificate to the Trusted Root Certification Authorities store to solve this issue.
In order to do this, please execute:

Certutil -Addstore -f Root <File.Cer>
Run "cliconfg.exe" from a Windows run dialog to determine that you did not check "Force protocol encryption".


Jet OLEDB driver refuses to open a linked table where the original has a lock - any way to force this?

Found the problem - permissions issue in disguise.  The Jet driver needs to have write access on the directory so it can update the lock files.  When it can't write to the lock files it throws its toys out of the cot with a misleading error message.


The simplest solution I can think of is to simply have a table folders with two columns: folder and priority. Simply

select folder
from folders
order by priority;

Then just try them in order and use the first folder that exists as a physical directory.

If folder in your current model is an attribute, then make the folders table a child table of that table; ie, if your current model is:

x (
   id,
   col1,
   col2,
   folder
)

then you should turn that into:

x (
    id,
    col1,
    col2
 )

 folders (
     x_id references x,
     folder,
     priority
  )

and the query becomes:

 select folder
 from folders
 where x_id = <id>
 order by priority;


Redo log is crucial in data guard environment. Hence we enable force logging before configuring data guard at the database level. So, no matter whether you use NOLOGGING clause or not the operation will be logged and replicated in the standby site.

  When you perform a DML or DDL operation using the NOLOGGING or UNRECOVERABLE clause, the standby database is invalidated and may require substantial DBA administrative activities to repair. You can specify the SQL ALTER DATABASE or SQL ALTER TABLESPACE statement with the FORCE LOGGING clause to override the NOLOGGING setting. However, this statement will not repair an already invalidated database.

NOLOGGING or Unrecoverable Operations

You might be interested in this thread from the asktom site.

Dataguard and Nologging


What is the best way to remove large amount of data from large tables?

I've faced such problems a few times so I have 2 tips for you.

1 Use this script that breaks your delete in packages so you will not run the risk of crashing the log

declare @var int, @ var2 int
set @var = (select count (1) from <Table_to_Delete> WHERE <Your-delete-clause>)
set @ var2 = 0

while @ var2 <> @var
begin
set rowcount 50000 - batchsize

print getdate ()
delete from <Table_to_Delete> WHERE <Your-delete-clause>

set @ var2 = @ var2 + @@ ROWCOUNT
print getdate ()

checkpoint - it's optional to avoid log grow

end

2) Now if the amount of rows that should remain in the table is really small, example if you are to delete 80 ~ 90% of the table I recommend you copy the table structure and copy the data to the new table. This option is valid if the table structure is simple because you will have to remember to copy all indices, foreing keys, and explicit permissions.


LOAD DATA mysql command and use only one column

You can do :
load data infile '/home/mohsen/codes/haq/translation-tmp/fa.ghomshei.txt' into table tr_fa_ghomshei LINES TERMINATED BY '\n' (aya);


Oracle 11g stuck at installation in oracle linux no error msg only showing | and stuck

It is very common for the 11.2 installer to throw an error on OL7/RHEL7 at the phase where the agent is being built. Based on your screenshot, that is where your installer was (Building Agent Libraries), but it was unable to display the dialog window for it. This can be fixed by editing $ORACLE_HOME/sysman/lib/ins_emagent.mk, and replacing:

$(MK_EMAGENT_NMECTL)

with:

$(MK_EMAGENT_NMECTL) -lnnz11

and finally clicking Retry. But the installer is unable to display the dialog on your system.

Operating System Requirements

CentOS is not a supported Linux distribution.
Oracle Linux and Red Hat Enterprise Linux 7 are certified starting with 11.2.0.4.

If you have access only to database version 11.2.0.1, then use Oracle Linux 6 or Red hat Enterprise Linux 6.


It looks like the comments may have answered this for you, but so there's an actual answer:

You cannot have two columns in one table that both have an IDENTITY(n,m) specification.

The most sensible approach for your requirement is probably:

your normal always-incrementing identity column should have the IDENTITY specification
use a SEQUENCE (see here for reference documentation and here for more general guidance) to control the other column that should reset each year.

You can restart a sequence on demand using

ALTER SEQUENCE <sequence_name> RESTART WITH <new_start_value>;

This command could be issued in a number of ways, for example from a job scheduled to run yearly on your desired reset date.


Error "Msg 10054, level 20, State 0, Line 0" with MSSQL under Docker

I've solved the issue by making use of the oficial MSSQL image from Microsoft, as I was using a custom Ubuntu 16.04 LTS + MSSQL packages. All the data was stored within a volume, so the transition from one container to the new was easy.

EDIT:
The previous didn't actually solve the problem. The real issue was that I was querying too much data. Querying an extensive dataset implied a huge RAM consumption, as much as it was permitted. I wasn't limiting it at all, so when reached the maximum amount of RAM available, the OS (dispatcher) would kill the process. The solution has resulted to configure a memory limit.


These queries with variables simulates the ROW_NUMBER() Window Function (not supported by MySQL).

Query 1 ID

If you are working with only 1 ID at a time like in you sample, this query can be used:

SELECT ID, MIN(Date), MAX(Date)
FROM (
    SELECT *
        , @n := CASE WHEN @value <> Value THEN @n +1 ELSE @n END as n
        , @value := Value
    FROM data1
    CROSS JOIN (SELECT @value, @n := 0) v
    ORDER BY Date DESC
) as x
WHERE n = 0
GROUP BY ID
;

Output:

ID |              MIN(Date) |                   MAX(Date)
 1 | July, 01 2015 00:00:00 | September, 01 2015 00:00:00

Query several IDs

If you want the same result for several IDs, this query can be used:

SELECT ID, MIN(Date), MAX(Date)
FROM (
    SELECT *
        , @n := CASE
            WHEN @ID <> ID THEN 1
            WHEN @value <> Value THEN @n +1
            ELSE @n END as n
        , @value := Value
        , @ID := ID
    FROM data2
    CROSS JOIN (SELECT @id, @value, @n := 0) v
    ORDER BY ID, Date DESC
) as x
WHERE n = 1
GROUP BY ID
;

Output:

ID |              MIN(Date) |                   MAX(Date)
 1 | July, 01 2015 00:00:00 | September, 01 2015 00:00:00
 2 | July, 01 2015 00:00:00 |      July, 03 2015 00:00:00

This SQL Fiddle contains both samples and queries.

Data used:

INSERT INTO data2
    (`ID`, `Date`, `Value`)
VALUES
    (01, '2015-01-01', 2),
    (01, '2015-02-01', 3),
    (01, '2015-03-01', 3),
    (01, '2015-04-01', 2),
    (01, '2015-05-01', 2),
    (01, '2015-06-01', 1),
    (01, '2015-07-01', 3),
    (01, '2015-08-01', 3),
    (01, '2015-09-01', 3),
    (02, '2015-01-01', 2),
    (02, '2015-02-01', 3),
    (02, '2015-03-01', 3),
    (02, '2015-04-01', 2),
    (02, '2015-05-01', 2),
    (02, '2015-06-01', 1),
    (02, '2015-07-01', 3),
    (02, '2015-07-02', 3),
    (02, '2015-07-03', 3)
;

Table data1 only contains the first rows where ID=1 (see SQL Fiddle).


How does SQL Server determine the order it takes locks in while selecting a table?

How does SQL Server determine what order to lock pages in while doing INSERTs and SELECTs a

Undetermined - done by internal processing and depending on query optimizer output.

  and how can you modify this behaviour?

Control your isolation. If you read in order to write, tell SQL Server to immediately get a write lock. Point closed.


Having no idea of your table... The first thing to try would be to

CREATE INDEX i_code ON students (code);
CREATE INDEX i_eid ON students (eid);


GFS2 as shared storage for MySQL cluster with active active setting

Two instances of mysqld must not use the same data files.  The code is not designed to handle unexpected changes to the file, which will happen by the other instance.

(I assume you are talking about Master-Master replication when you say "cluster"?)


How does MySQL evict pages from the buffer pool?

Flushing and evicting are entirely different.

https://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_flush

MySQL will flush dirty pages in accordance with innodb_max_dirty_pages_pct. The default database page size default in InnoDB is 16KB and the maximum setting for innodb_max_dirty_pages_pct is 99 so there isn't a whole lot of tolerance for the build up of dirty pages (although, as you know from the link you cite the actual flushing schedule calculation is a bit more complicated).

After they are flushed they will then be evicted when they are the LRU.

The documentation states, "When room is needed to add a new page to the buffer pool". So I would imagine it waits until it is pretty close to full. Regarding the characteristics of LRU in general, you can read up on that elsewhere as it is not MySQL specific. MySQL Does however use the "Midpoint insertion strategy" which makes things not a true LRU in order to deprioritize superfluous pages.

https://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_midpoint_insertion_strategy


Does READ UNCOMMITTED have the same effect as AUTOCOMMIT=1?

This is an answer assuming MySQL/InnoDB. Visibility rules are slightly different on Oracle:

No, as you say, SET autocommit = 1 (default behavior) is that every single query is like if it has a START TRANSACTION; ... COMMIT; around it, committing after every query. That is different from READ UNCOMMITTED (which doesn't mean that there is not a lock). InnoDB will always lock the row affected during its operation, the difference is that as the transaction commits just after the statement is executed, the lock will be released just afterwards in all cases.

However, even if you are using autocommit=1, rollback area cannot be purged inmediately when using tx_isolation > READ COMMITED, because other long-running transactions may need those values.

To show you the difference, I can show you an example:

session1> create table test (id int primary key, c varchar(20)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.15 sec)

session1> set autocommit = 1;
Query OK, 0 rows affected (0.00 sec)

session2> set autocommit = 1;
Query OK, 0 rows affected (0.00 sec)

session1> select * from test.test;
Empty set (0.04 sec)

session2> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

session2> insert into test values (1, 'a');
Query OK, 1 row affected (0.10 sec)

session1> select * from test.test;
Empty set (0.04 sec)

Even if the other session is is using autocommit = 1; session 1 can't see the changes of session 2 because a transaction is ongoing. Also:

session1> create table test (id int primary key, c varchar(20)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.15 sec)

session1> set autocommit = 1;
Query OK, 0 rows affected (0.00 sec)

session2> set autocommit = 1;
Query OK, 0 rows affected (0.00 sec)

session1> select * from test.test;
Empty set (0.04 sec)

session1> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

session1> select * from test.test;
Empty set (0.04 sec)

session2> insert into test values (1, 'a');
Query OK, 1 row affected (0.10 sec)

session1> select * from test.test;
Empty set (0.04 sec)

Session 2 cannot see the new value autocommited in session 2 becase start transaction + select is like if you had executed start transaction with consistent snapshot, so in InnoDB's default level "REPEATABLE READ", it cannot be seen even if it has (auto) commited on the other session.

Of course, if you only use autocommit mode, and no longer transactions in any connection, READ COMMITED and READ UNCOMMITED are equivalent, but not because it changes its visibility, but because you commit every single query. Things also get more complex if you throw other non-transactional engines into the mix.

As a side effect, mysql 5.6 changes InnoDB autocommit SELECT into read-only transactions, boosting its performance a bit.


MDF file is close to 900gb but my calculation of reserved table space is less than 30gb... what am I missing?

Since it sounds like you purged data from you tables, the next step is to release the free space within the datafile (.mdf) back to the OS.  You can do this via a DBCC SHRINKFILE statement.

If this doesn't result in releasing sufficient disk back to the OS, you should rebuild indexes on those tables you performed heavy purge operations against in case they are heap tables and then re-issue the DBCC SHRINKFILE command.

I would suggest leaving 20 - 30% free space in the mdf, which if I'm reading the question right, should be 36GB - 40GB in your case. This will avoid growth operations from occurring in the near future.

Finally, after the SHRINKFILE operation completes, you'll want to REBUILD any heavily fragmented indexes as the SHRINKFILE operation will cause fragmentation for any indexes that were stored toward the tail end of the data file.

EDIT: So it seems your calculation of reserved table space (as referenced in the question header) is off by a significant margin based on the comments on this answer.  In this case, what I would suggest you do is run the following statement to get an idea of what's using your disk space:

DECLARE @String NVARCHAR (4000), @ExecString NVARCHAR ( 4000)

DECLARE dbCursor CURSOR
FOR
        SELECT name
        FROM sys .databases
        WHERE name LIKE '%'

CREATE TABLE ##tmpAllDBs
(
     DBID               INT,
     DatabaseName     SYSNAME,
     Name               SYSNAME,
     FileName          SYSNAME,
     Size               INT,
     UsedSpace          INT,
     ID                    INT,
     FileGroup          SYSNAME
)

SET @String = '
USE [?]

DECLARE     @PageSize     FLOAT
SELECT     @PageSize = v.low/1024.0
FROM     [master].[dbo].[spt_values] v
WHERE     v.number=1
          AND v.type=''E''

CREATE TABLE #tmpspc
(     Fileid               INT,
     FileGroup          INT,
     TotalExtents     INT,
     UsedExtents          INT,
     Name               SYSNAME,
     FileName          NCHAR(520)
)
INSERT #tmpspc
EXEC (''DBCC ShowFileStats'')

CREATE TABLE #tmplogspc
(     DatabaseName     SYSNAME,
     LogSize               FLOAT,
     SpaceUsedPerc     FLOAT,
     Status               BIT
)
INSERT #tmplogspc
EXEC (''DBCC SQLPerf(LogSpace)'')

INSERT INTO ##tmpAllDBs
SELECT     db_id() AS dbID,
          db_name() AS [DatabaseName],
          RTRIM(s.name) AS [Name],
          RTRIM(s.filename) AS [FileName],
          (s.size * @PageSize) AS [Size],
          CAST(tspc.UsedExtents * CONVERT(FLOAT,64) AS FLOAT) AS [UsedSpace],
          CAST(s.fileid AS INT) AS [ID],
          RTRIM(g.groupname) AS [FileGroup]
FROM     [dbo].[sysfilegroups] AS g
          INNER JOIN [dbo].[sysfiles] AS s
               ON s.groupid=CAST(g.groupid AS INT)
          INNER JOIN #tmpspc tspc
               ON tspc.Fileid = CAST(s.fileid AS INT)

UNION

SELECT     db_id() AS dbID,
          db_name() AS [DatabaseName],
          RTRIM(s.name) AS [Name],
          RTRIM(s.filename) AS [FileName],
          (s.size * @PageSize) AS [Size],
          (tspclog.LogSize * tspclog.SpaceUsedPerc * 10.24)
               / ( SELECT     COUNT(s_sub.name)
                    FROM     [dbo].[sysfiles] s_sub
                    WHERE     s_sub.groupid = 0)
          AS [UsedSpace],
          CAST(s.fileid AS INT) AS [ID],
          ''LOG FILE'' AS FileGroup
FROM     [dbo].[sysfiles] AS s
          INNER JOIN #tmplogspc tspclog
               ON tspclog.DatabaseName = db_name()
WHERE     (s.groupid = 0)

DROP TABLE #tmpspc
DROP TABLE #tmplogspc
'
--EXEC sp_MSforeachdb @String

DECLARE @name NVARCHAR (128)
OPEN dbCursor
FETCH NEXT FROM dbCursor
INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

     SELECT @ExecString = REPLACE( @String, '?', @name )
        EXECUTE (@ExecString )

        FETCH NEXT FROM dbCursor
        INTO @name
END

-- Close and deallocate the cursor because you've finished traversing all it's data
CLOSE dbCursor
DEALLOCATE dbCursor

SELECT DBID , DatabaseName , Name, FileName, Size /1024.0 AS SizeMB, UsedSpace /1024.0 AS UsedSpaceMB,
     (1.0 * UsedSpace)/(1.0 * Size) AS PercentUsed , ID, FileGroup
FROM ##tmpAllDBs
ORDER BY 2, 8

DROP TABLE ##tmpAllDBs

This returns results that show how much size a file is using according to the OS (SizeMB), how much data is actually contained within the file (UsedSpaceMB) and some other information about the data files.  The script is ugly, but it works all the way back to SQL 2000.

With this output in had, what I would do is look for any files on User Databases where the PercentUsed column is low and the database is not tempdb (I'll talk about what to do with tempdb further below), and then run DBCC SHRINKFILE operations against said files.  This will give back the OS some space, but by no means does this mean your job is done.

Once this is complete, the next step is to identify what data you can purge or which objects you can drop.  Purge old data, drop unused indexes, and then rerun the script and see what else can be reduced in size.  Again, rebuilding indexes is recommended, but as you've already seen, Index Rebuild operations may cause data files to grow so you may have to weigh if a defragmented index is worth the extra consumption of disk space.

Other options available to you will depend on your SQL Server Version and Edition.  If you're running Enterprise or SQL 2016 Standard SP1 or later, you can take advantage of Table Compression.  This can help reduce the space used within your data files as well.

If you see that tempdb is consuming a large amount of space which is relatively unused, reduce the the size of the tempdb files, but make sure that if you have multiple .mdf files, you shrink them all to the same size as per MS's recommended guidelines.  Another word about tempdb, shrinking it is often an act of futility because there's often activity occurring within the database, so you may have to restart the instance to get these files to shrink down as desired.  Here's MS's documentation on the various ways to shrink tempdb.

Another possibility is that you have non-database related clutter on your drive(s).  In this case, simply deleting these files may be the fix for your issue.  I find using WinDirStat helps quickly identify where some of the larger files are.  Be sure you know what you're deleting though.  Sometimes there are lots of "seemingly worthless" files sitting in hidden directories, such as the WinSxS folder that look like they could be quickly deleted.  Don't recklessly delete files; make sure you only purge files you know are safe to remove.

Finally, ask for money and buy some additional storage.  Your server just may be at capacity and it's time for an upgrade.  Hardware isn't always the answer, but sometimes it can be the best answer.


Best embed/reference/field strategy for many inserts (mongo)

Why do you have Score model?
Why not keep only User and Match models and for each have a score attribute?
In this schema you have only the updates per score.
If its due to redundancy of attributes, please note this is common in a document based schema design.


Export sql azure DB as bacpac file without including data for certain tables

You can create a perfect copy of a table and the whole schema by doing:
SELECT TOP 0 * INTO new_table FROM old_table

If you have a linked server or a database on the same server that is called, let's say, database_copy you can actually iterate all tables and you will end up having the same number of tables, all with the same schema.
Otherwise, if you can mask the data or encrypt them. But in this case they will still be on the database


When we set up Database Replication in the Server 2, 3 from Server # 1, Do i need to take DB backup for Server # 2 and Server # 3 as well.

It depends -

If you are replicating whole database and the size of database is huge

then its best to initialize replication from database backup.

IF you are replicating subset of data (as opposed to the entire database)

then you just run the snapshot (behind the scene it does a bcp out and bcp in of the data).

  How do i test whether my databases are running fine even on the failure of Server # 1 ?

This will eventually depend on the frequency of log reader agent. If you set it to run continuously then you will see your changes immediately on the subscribers. The latency depends on the amount of activity at publisher (server 1), network latency, distribution database - if it is on the same server as publisher or is on its own dedicated server.

There are many factors to consider when you choose replication as Disaster recovery (a stand by server when your primary is down).

As a side note:

What you are looking is a good HA - case when server 1 is down .. do we have a standby that can serve the application ? .. something along that line.

You should consider moving to a more recent version - sql server 2014 and look into using AlwaysON Availability groups.


Is there a best practice for recurring calendar events

This is just a suggestion which, while obviously not helping the OP, may help others with similar/analogous problems (won't fit as a comment).

You may also wish to consider using MySQL Events for your application. Depending on your app's functionality, they may provide a more elegant solution to a scheduling/calendar range of capabilities.

Check out the documentation here, and these sites (1, 2). I was looking at this recently and it's really quite powerful and can do a lot of the heavy lifting in the scheduling/polling area of this sort of programme.


What does a TRUNCATE TABLE write to the Log file in SQL Server?

There is a huge difference between the records logged in a transaction log file for a DELETE and for TRUNCATE TABLE statement

A DELETE statement records what exactly has been deleted, i.e. the value of the deleted row e.g. 'JohnSmith', so you can read the transaction log content (you can use fn_dblog), see what was deleted and re-insert the deleted record if you want.

With the TRUNCATE TABLE statement, you cannot do this, as the statement is 'minimally logged'. The exact deleted values are not logged in the transaction log, only the IDs of the pages that held the truncated records are logged. These pages are marked for overwriting in the database data file and the truncated data will be gone for good when the new transactions are written to these pages.

Therefore, reading only the transaction log cannot provide the values that are lost due to the TRUNCATE TABLE statement. The only chance you have to recover the truncated records is to read the page ID in the transaction log and find it in the MDF file, in case it hasn't been overwritten


Difference in architecture between SQL Server 2005 and SQL Server 2012

There are a number of them, a lot of articles on the topic if you have google at it.

Some of them:

Default Schema for Windows Groups

User Defined Server Roles
Enhancments to Auditing
User Contained Databases, authentication without logins.
TDE - Transparent Data Encryption
Hashing Functions - improved.


Restoring database always throwing exception

If you want to restore a *.bak file onto a machine e.g. TESTSERVER, then that *.bak file needs to be

on a local drive of TESTSERVER, or:
on a share in the network that TESTSERVER can reach (a "public" share)

So if you have the *.bak file on your local PC MyPC, you can try to do a restore from \\MyPC\C$\ShareFolder\Backupfile.bak and this might or might not work.

But you cannot use C:\ShareFolder\Backupfile.bak since that will go to the server's C:\ drive and look for that file - and thus you get the error message and it cannot be found.

After all: your SQL Server instance is running on TESTSERVER - so if that process there executes a restore from the C:\ drive, that'll be the C:\ drive on that server machine - not your local PC! If that server process on TESTSERVER can reach your machine, then you might use the UNC path notation to your local disk - so that might work.


Always Encrypted - Lock request time out period exceeded

Apparently, you can't have certain options turned on for the table, like "large value types out of row". Here's what I did:

exec sys.sp_tableoption
    @TableNamePattern = 'dbo.SomeTableName'
    ,@OptionName = 'large value types out of row'
    ,@OptionValue = 'off';


What is an appropriate datatype for an RFID, and is there a standard definition for the format?

I don't like answering my own questions (feels like cheating) but here goes:

According to a post on StackOverflow there is no fixed datasize.  RFIDS can vary in both length and format, depending on the industry.

I will stick with varchar(20) for my own purposes, because the batch of RFIDS that we purchased all fall within that size limit.


I don't think async execution will accomplish what you want. SQL Server returns results to the client as they become available if you've specified a fast-forward ODBC cursor type.

Separately, displaying partial results in a GUI interface while the query is still running may require special programming techniques. That's not really a database problem so StackOverflow would be a better forum if you have additional questions about that.


Postgresql Streaming replication - monitoring

Since you're using 9.2, it would probably be better to use pg_xlog_location_diff instead of your byte_lag function. Something like this is much clearer, less error prone, and should give you what you are looking for.

SELECT client_hostname,
       client_addr,
       pg_xlog_location_diff(sent_location, replay_location) AS difference_in_bytes
FROM pg_stat_replication;

Alternatively, there is always the check_postgres.pl Nagios plugin. It has a huge number of implemented checks that can definitely help keep your PostgreSQL installation happy and healthy.

The particular function you would want for your needs is hot_standby_delay.

Once you have it installed, and the symlinks made, you could simply do something like this from your primary:

check_postgres_hot_standby_delay --dbport=5432,5432  --dbuser=postgres,postgres --dbhost=,10.0.1.2 --warning="1048576 and 2min" --critical="16777216 and 10min" -v

And get alerts if the bytes and the time falls out of your set thresholds.


Connection pooling with python mysql-connector

I did some experimentation in console trying to figure out why this was happening for me as well. In the end, I searched right into the file folder for the package looking to ensure that the pooling file was present in the mysql.connector package version I had.

Sure enough there it was.

In the end, I simply changed my import statement. I had been using

import mysql.connector
pool = mysql.connector.pooling.MySQLConnectionPool(**config)

After playing in console for a bit I realized that it would be happy if I just used;

import mysql.connector
from mysql.connector import pooling
pool = pooling.MySQLConnectionPool(**config)

Worked fine. Couldn't tell you "why", but it worked.


What happens with a synonym if the table which its based on gets deleted?

In this case the synonym should be properly deleted, if not, it cannot be used but still exists on sys.synonyms catalog.

You can check it running next script:

IF OBJECT_ID('T1') IS NOT NULL
    DROP TABLE T1;
GO

IF OBJECT_ID('NEW_T1') IS NOT NULL
    DROP SYNONYM NEW_T1;
GO

CREATE TABLE T1(f1 int);
GO

INSERT INTO T1 VALUES (1),(2),(3);
GO

CREATE SYNONYM NEW_T1 FOR T1;
GO

SELECT * FROM NEW_T1;
GO

f1
-----------
1
2
3

DROP TABLE T1;
GO

SELECT * FROM NEW_T1;
GO

Msg 5313, Level 16, State 1, Line 23
Synonym 'NEW_T1' references an invalid object.

SELECT name, object_id FROM sys.synonyms WHERE name = 'NEW_T1';
GO

name        object_id
------      --------------
NEW_T1      1463676262

IF OBJECT_ID('NEW_T1') IS NOT NULL
    DROP SYNONYM NEW_T1;
GO


Why some triggers run exactly at the same time?

when you use the MERGE statement it triggers all three types of triggers (INSERT / UPDATE / DELETE), but I'm not sure, if it calls a combined trigger three times too, but this should be easy to find out...


So it did pretty much what it is described in Make Schema Changes on Publication Databases:

  If a publication is set to allow the propagation of schema changes,
  schema changes are propagated regardless of how the related schema
  option is set for an article in the publication. For example, if you
  select not to replicate foreign key constraints for a table article,
  but then issue an ALTER TABLE command that adds a foreign key to the
  table at the Publisher, the foreign key is added to the table at the
  Subscriber. To prevent this, disable the propagation of schema changes
  before issuing the ALTER TABLE command.

To boot, your exact case is give as an example :) Also, the solution is given there: disable schema changes replication before issuing the ALTER TABLE.

As to why it behaves this way, I would say that a system that would make 'smart' decision what schema changes to replicate and what not would be not only very hard to design, but also very error prone and would cause a lot of surprises and break the principle of least astonishment a lot of times. The gist of it is the relations between objects that are published and objects that are not are often ambiguous or hard to detect (think why is the subject of dependency tracking so vast...). And even when detected, the semantics of a change that affects both a published article and a non-published object are ambiguous to say the least: allow the change only locally? Block the change? I can assure you that whichever answer you venture forward, someone will complain it breaks his case... because the answer is dependent on specific soft business rules, not hard language rules.


how to change drive for Partitioned tables

You are using the terminology incorrectly and is not clear what you want to achieve. A table has one or more partitions. Each partition resides in a filegroup. A filegroup hase one or more files. A file is stored on a logical drive. A logical drive can be backed by one or more physical drives.

What is that you want to change?

Did you load data in the wrong partition of the table?
Did you load the data in an unpartitioned table and now you need it to be partitioned? (This is really the same question as the first one)
Did you placed the partition in the wrong filegroup?
Did you place one or more filegroup files on the wrong logical drive?
Did you place a logical drive on the wrong physical drive?

Each problem has a different solution.


I'm assuming you don't have automatic failover and no witness, otherwise the mirror would have been promoted automatically.

In this case you need to switch the roles manually, with forced service:

ALTER DATABASE database_name SET PARTNER FORCE_SERVICE_DATA_LOSS

See Role Switching During a Database Mirroring Session and ALTER DATABASE Database Mirroring for more detail.


SQL Server Audit File Metrics Information

I may as well post my terribad approach in case others are interested.  Basically, the following is a 2-step process.  The first is to create a table in a database of your choosing that will hold a log of the Audit file meta data that you wish to collect:

CREATE TABLE dbo.Audit_FileMetrics
(
    ID  BIGINT  IDENTITY(1,1) NOT NULL,
    [Name] VARCHAR(512),
    [LastWriteTime] DATETIME2,
    [Length] BIGINT,
    [CompressedLength] BIGINT
)

I'm keeping it simple by only tracking the name, creation date, and size (both compressed and uncompressed).  Why compressed?  Well, we have systems in our environment that generate terabytes worth of audit activity per day, so we have to compress the .sqlaudit files immediately after they are generated otherwise we run into a Tribble situation.  Because of this, the PowerShell routine below pulls some meta data out of the .zip files directly.

The second part of this process is to setup a SQLAgent job with a PowerShell step that fires off the following:

#required to read the header data from the .zip files
Add-Type -assembly "system.io.compression.filesystem"

#get filepath where audit files are written as well as the last file's timestamp
$filePath = Invoke-Sqlcmd -Query "SELECT TOP 1 sfa.log_file_path, af.LastWriteTime FROM sys.server_file_audits sfa CROSS APPLY(SELECT COALESCE(MAX(LastWriteTime), CAST('1900-01-01' AS DATETIME2)) AS LastWriteTime FROM <EnterDatabaseNameHere>.dbo.Audit_FileMetrics) af WHERE sfa.on_failure = 1" -Database "<EnterDatabaseNameHere>" #-ServerInstance "localhost"
#not "needed", but if you run this block over and over in ISE, this is handy as it clears out the array
$auditFiles = @()
#get list of .zip files we have not yet collected meta data from
$files = ls $filePath.log_file_path -Filter "*.zip" | Where-Object {$_.LastWriteTime -gt $filePath.LastWriteTime}
ForEach($file IN $files)
{
    $myPath = $filePath.log_file_path + $file.Name
    #read/append file header meta data
    $auditFiles += [io.compression.zipfile]::OpenRead("$myPath").Entries | SELECT Name, LastWriteTime, Length, @{Name="CompressedLength";Expression={$file.Length}}
}

#build out INSERT command statement
$sqlCommandInsert = 'INSERT INTO dbo.Audit_FileMetrics ([Name], [LastWriteTime], [Length], [CompressedLength]) '
$sqlCommandValues = 'VALUES '
$sqlQuery = ''
$i = 0

ForEach($auditFile IN $auditFiles)
{
    $sqlCommandValues += "('" + $auditFile.Name + "', '" + $auditFile.LastWriteTime + "', " + $auditFile.Length + ", " + $auditFile.CompressedLength + "), "
    $i++

    if ($i -eq 1000)
    {
        #cannot insert more than 1000 VALUES tuples, so commit a batch of 1000
        $sqlQuery = $sqlCommandInsert + $sqlCommandValues.Substring(0, $sqlCommandValues.Length - 2)
        Invoke-Sqlcmd -Query $sqlQuery -Database "<EnterDatabaseNameHere>" #-ServerInstance "localhost"
        $sqlCommandValues = 'VALUES '
        $i = 0
    }
}

#final insert into log table
$sqlQuery = $sqlCommandInsert + $sqlCommandValues.Substring(0, $sqlCommandValues.Length - 2)
Invoke-Sqlcmd -Query $sqlQuery -Database "<EnterDatabaseNameHere>" #-ServerInstance "localhost"

What this routine does is identify the path where the audit files are written to as well as the timestamp of the last audit file tracked.  Using that, it runs a Get-ChildItem against the identified directory looking for all *.zip files returning only files older than the last file we've identified prior.

ASSUMPTIONS:

This routine assumes the Audit Definition is configured such that the inability to write out an audit file will result with the server being shutdown.  If you do not have this requirement, you will want to adjust the query (i.e. this is the reason for the predicate WHERE sfa.on_failure = 1)
This routine is only looking for .zip files.  If you do not compress your .sqlaudit files, you will run into a scenario where the meta data on the last .sqlaudit file will likely change at the next run because the database engine is still writing to it.  In this event, I would suggest ignoring the last file returned in the array, which should avoid any duplicate/inconsistent data.

Finally, this is totally specific to my environment, but it does exactly what I need.  If you have suggestions, updates, etc. please post another answer as more ideas are always appreciated.


How to return 0 for record count when nothing returns for that hour using datepart?

You can use a table constructor of all hours (0-23) and join to that from your DATEPART function.

DB FIDDLE

SELECT
   h.h as DatePartHour,
   --DATEPART(hour,TempDateTime) AS TempDatePart,
   COUNT(DISTINCT TempNumber) AS RecordCount
FROM #Temp
   full join (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(0)) h(h)
   on h.h = DATEPART(hour,TempDateTime)
GROUP BY  h.h


INSERT INTO with subquery that might return 0 results

INSERT INTO status (status1, subject_id)
SELECT 'processing', u.subject_id
    FROM users AS u
    LEFT OUTER JOIN status ON (u.subject_id = status.subject_id)
    WHERE (status.status1 IS NULL or status.status1 = 'ready')
      and usrs.start_date IS NOT NULL LIMIT 1
ON CONFLICT (subject_id) DO update SET status1 = 'processing'

PS. LIMIT without ORDER BY is a lottery...


How to read data from multiple Excel files with SQL Server Integration Services(New Senario)

When you initially pull the file paths from the ExcelFilePath table, trying returning just names of the folders holding the Excel files that will be used and make sure these end with a \ (i.e. C:\Excel_Exercise\Excel_Source\).  You may need to used a T-SQL function like RIGHT/SUBSTRING, but doing this will allow you to load the files using the method outlined below.  Also, are you filtering for only "Active" file paths in the SQL query from the Execute SQL Task?  As you're probably already doing, make sure to set the DelayValidation and ValidateExternalMetadata properties accordingly.

The First Foreach Loop will be a Foreach ADO Enumerator using the object variable from the Execute SQL Task as the source variable.  In the Variable Mappings pane, add a string variable that will hold the file directory at index 0.
Inside the first Foreach Loop, add another Foreach Loop as a Foreach File Enumerator.  On the Collection pane, add an expression for the Directory property that uses the string variable from the outer Foreach loop with the directory.  On this Foreach Loop, add a variable in the Variable Mappings pane the will be used to hold the full file path (including file name/extension).
Within this Foreach Loop add a Data Flow Task that loads from an Excel Source to the desired destination.  On the Connection Manager for the Excel Source, create an expression for the ExcelFilePath and add the variable holding the complete file path and name/extension.


SQL Server IDENTITY column automatic re-seed

You can use the bigint data type in order to avoid reaching the max value too often.

The range for bigint is -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807), storage = 8 Bytes. Are you often out of this limit?

In case you want to reseed, you should be able to run the dbcc checkident statement inside a transaction, but you'll have to do more error handling inside your code.

I don't know of an IDENTITY property to automatically reseed its value, only manually, by truncating the table or by using dbcc checkident. You could create a trigger on your table and once the last inserted value gets close to the maximum then you'd be able to reseed. But this will probably add cost to your transactions.


How to insert values into a table with default values from a select query in PostgreSQL?

If you add all ColumnsNames (exclude the 'id' Column) after the Tablename, there will be Insert the serial automaticly like:

INSERT INTO animals(nm,typ,tvi,tvf) select nm,typ,tvi,tvf from json_po.....

You can also add a DEFAULT Value in your Column, to set a Default Value if the column is not in the Insert Column-list.


How can I 'fake' stats for generate_series data?

The problem occurs when using the Set Returning Function in the SELECT target-list, but that way of calling these functions is kludgy in the first place.

See for example this thread on pg-hackers list on various reasons why experienced PG users tend to avoid it.

On the other hand, when the SRF is called in the FROM list, without the fake cross join, 9.1.6 produces the standard estimate too:

 explain analyze
 select * from (select 'A' as foo) z, generate_series(1,10) b;

results in:

                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..22.52 rows=1000 width=36) (actual time=0.018..0.041 rows=10 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1)
   ->  Function Scan on generate_series b  (cost=0.00..12.50 rows=1000 width=4) (actual time=0.014..0.021 rows=10 loops=1)
 Total runtime: 0.076 ms
(4 rows)


Your trigger has no WHERE clause, does not correlate to the row(s) that were just inserted, and doesn't handle the case where multiple rows might be inserted in a single operation (unlike some platforms, in SQL Server a trigger fires per statement, not per row). So it will work exactly once: when you insert the very first row into the projects table. Try the following instead:

CREATE TRIGGER dbo.create_proid -- please always use schema prefix
ON dbo.projects
FOR INSERT
AS
BEGIN
  SET NOCOUNT ON;

  UPDATE p
    SET pro_id = CONVERT(VARCHAR(20), c.comp_type)
      + '-'
      + RIGHT('00000' + CONVERT(NVARCHAR(20), i.client_id),
        CASE WHEN i.client_id < 100000 THEN 5 ELSE 6 END)
      + '-'
      + RIGHT('00000' + CONVERT(NVARCHAR(20), i.id),
        CASE WHEN i.id < 100000 THEN 5 ELSE 6 END)
  FROM dbo.projects AS p
  INNER JOIN inserted AS i
  ON p.id = i.id
  INNER JOIN dbo.comp_types AS c
  ON p.comp_type_id = c.comp_type_id;
END

Not sure why you're storing the pro_id value when you can determine it at runtime. Do you also have an update trigger that maintains it when the comp_type_id changes? What if a comp_type is deleted?

Also please always use schema prefix and don't convert to NVARCHAR without specifying a length. And why are you using the text data type? This has been deprecated for ages - you should be using NVARCHAR(MAX) or VARCHAR(MAX) only if these names and descriptions will really exceed 4000/8000 characters (highly unlikely from the naming).


Getting data from Postgres through Linked server of SQL

I think the problem is datetime2 is not a postgres data type:
convert(datetime2, "2020-01-01 00:00:00.0000000")

The openquery statement exucute in the target database the query between '' so it must be written in the targewt dialect
try this

select * from openquery(SALESFORCE,'select closeddate from case where
closeddate > cast(''2020-01-01'' as date)')


SQL to read XML from file into PostgreSQL database

Similar to this answer to a previous question, and if you don't want the restrictions of pg_read_file() (in short: pg_read_file can't read files outside the database directory, and reads text in the current session's character encoding).

This function works for any path, but needs to be created as superuser:

create or replace function stack.bytea_import(p_path text, p_result out bytea)
                   language plpgsql as $$
declare
  l_oid oid;
begin
  select lo_import(p_path) into l_oid;
  select lo_get(l_oid) INTO p_result;
  perform lo_unlink(l_oid);
end;$$;

lo_get was introduced in 9.4 so for older versions you would need:

create or replace function stack.bytea_import(p_path text, p_result out bytea)
                   language plpgsql as $$
declare
  l_oid oid;
  r record;
begin
  p_result := '';
  select lo_import(p_path) into l_oid;
  for r in ( select data
             from pg_largeobject
             where loid = l_oid
             order by pageno ) loop
    p_result = p_result || r.data;
  end loop;
  perform lo_unlink(l_oid);
end;$$;

then:

select convert_from(stack.bytea_import('/tmp/test.xml'), 'utf8')::xml;


Can a query write to the binary log BEFORE it has finished executing?

well, it doesn't explain why the database locked up but it does answer part of my question:

  When a transaction is sent to a MySQL server running with binary
  logging enabled, the transaction is executed normally, and just prior
  to completing the transaction the server records the change serially
  in the binary log. Once this is done, it tells the storage engine that
  the transaction is ready to be committed.

https://www.packtpub.com/books/content/setting-mysql-replication-high-availability

Although, I've now just read the following:

The SQL statements are not

  The SQL statements are not
  written into the binary log until the transaction commits,

which is from the 'Understanding MySQL Internals' book.


Insert from another table, then update using Identity

While creating column SomeDataId add identity

alter table dbo.x
 add  SomeDataId BIGINT Identity(1, 1)
Then enable identity_insert on table SomeData and insert rows

SET IDENTITY_INSERT dbo.SomeData ON
insert into SomeData(id, binary)
select SomeDataid, SomeData
from x


Find current running queries which takes more CPU

there is several methods to find the queries that cause high CPU usage, most common waies:

Method 1: use build in SQL server report to find queries with high CPU time.

Method 2: use the following queries:

Query 1: get all statement with CPU usage and from high CPU usage to low

SELECT
       -- using statement_start_offset and
       -- statement_end_offset we get the query text
       -- from inside the entire batch
       SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
                           ((CASE qs.statement_end_offset
                                        WHEN -1 THEN DATALENGTH(qt.TEXT)
                                        ELSE qs.statement_end_offset
                           END
                           - qs.statement_start_offset)/2)+1)
                           as [Text],
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
-- converting microseconds to seconds
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
       -- Retrieve the query text
       CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
       -- Retrieve the query plan
       CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC -- CPU time

Query 2:  Top 3 CPU-sapping queries for which plans exist in the cache

SELECT TOP 3
total_worker_time ,
execution_count ,
total_worker_time / execution_count AS [Avg CPU Time] ,
CASE WHEN deqs.statement_start_offset = 0
AND deqs.statement_end_offset = -1
THEN '-- see objectText column--'
ELSE '-- query --' + CHAR(13) + CHAR(10)
+ SUBSTRING(execText.text, deqs.statement_start_offset / 2,
( ( CASE WHEN deqs.statement_end_offset = -1
THEN DATALENGTH(execText.text)
ELSE deqs.statement_end_offset
END ) - deqs.statement_start_offset ) / 2)
END AS queryText
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
ORDER BY deqs.total_worker_time DESC ;

Query 3: get number of core usage as well as CPU time (note this query has a lot of other uses like desk I/O)

SELECT TOP 50
    [Avg. MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000 / qs.execution_count,
    [Total MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000,
    [Avg. Elapsed Time(sec)] = qs.total_elapsed_time / 1000000 / qs.execution_count,
    [Total Elapsed Time(sec)] = qs.total_elapsed_time / 1000000,
    qs.execution_count,
    [Avg. I/O] = (total_logical_reads + total_logical_writes) / qs.execution_count,
    [Total I/O] = total_logical_reads + total_logical_writes,
    Query = SUBSTRING(qt.[text], (qs.statement_start_offset / 2) + 1,
        (
            (
                CASE qs.statement_end_offset
                    WHEN -1 THEN DATALENGTH(qt.[text])
                    ELSE qs.statement_end_offset
                END - qs.statement_start_offset
            ) / 2
        ) + 1
    ),
    Batch = qt.[text],
    [DB] = DB_NAME(qt.[dbid]),
    qs.last_execution_time,
    qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
where qs.execution_count > 5    --more than 5 occurences
ORDER BY [Total MultiCore/CPU time(sec)] DESC

EDIT
the following sorted proc is helpful too

sp_who
sp_who2
sp_who3


By default, SQL Server activity is not logged the way you expect. Some write activity is recorded in the Transaction Log, but this also depends on how your databases are set up.

There are four main options for tracking SELECT activity on a server:

You can use SQL Server Profiler to connect to your server and watch for specific activity as it happens.
You can create a server-side trace to log activity to a trace file on the server, which can then be read by SQL Server Profiler, or by using fn_trace_gettable to load it into a table for further analysis.
You can use Extended Events, which offers more functionality than server-side traces, and which Microsoft recommends instead of server-side traces starting with SQL Server 2012.
You could use C2 audit mode.

You can use SQL Server Profiler to set up your trace (choose the specific events, filters, etc. that you want), then script it out using the File menu and execute it on the server to create a server-side trace, as described here.


Detect if any values in NVARCHAR columns are actually unicode

Suppose one of your columns does not contain any unicode data. To verify that you would need to read the column value for every row. Unless you have an index on the column, with a rowstore table you will need to read every data page from the table. With that in mind I think it makes a lot of sense to combine all of the column checks into a single query against the table. That way you won't be reading the table's data many times and you don't have to code a cursor or some other kind of loop.

To check a single column believe that you can just do this:

SELECT COLUMN_1
FROM [P].[Q]
WHERE CAST(COLUMN_1 AS VARCHAR(80)) <> CAST(COLUMN_1 AS NVARCHAR(80));

A cast from NVARCHAR to VARCHAR should give you the same result except if there are unicode characters. Unicode characters will be converted to ?. So the above code should handle NULL cases correctly. You have 24 columns to check, so you check each column in a single query by using scalar aggregates. One implementation is below:

SELECT
  MAX(CASE WHEN CAST(COLUMN_1 AS VARCHAR(80)) <> CAST(COLUMN_1 AS NVARCHAR(80)) THEN 1 ELSE 0 END) COLUMN_1_RESULT
...
, MAX(CASE WHEN CAST(COLUMN_14 AS VARCHAR(30)) <> CAST(COLUMN_14 AS NVARCHAR(30)) THEN 1 ELSE 0 END) COLUMN_14_RESULT
...
, MAX(CASE WHEN CAST(COLUMN_23 AS VARCHAR(8)) <> CAST(COLUMN_23 AS NVARCHAR(8)) THEN 1 ELSE 0 END) COLUMN_23_RESULT
FROM [P].[Q];

For each column you will get a result of 1 if any of its values contain unicode. A result of 0 means that all data can be safely converted.

I strongly recommend making a copy of the table with the new column definitions and copying your data there. You'll be doing expensive conversions if you do it in place so making a copy might not be all that much slower. Having a copy means that you can easily validate that all of the data is still there (one way is to use the EXCEPT keyword) and you can undo the operation very easily.

Also, be aware that you might not have any unicode data currently it's possible that a future ETL could load unicode into a previously clean column. If there is not a check for this in your ETL process you should consider adding that before doing this conversion.


You do not seem to join your activities and messages tables in your where clause. The more correct syntax would be:

update activities
set created_at = messages.created_at,
updated_at = messages.updated_at
from messages
where messages.id = activities.trackable_id
  AND messages.id in (1,2,3)


Which Index for WHERE UPPER("my_col"::text) LIKE "FOO" search on bytea

You could create an immutable function which changes the \000 null characters (plus any other characters you consider to be binary) to spaces, then create a trigram index on that function over the column. There might be such a function built in, but if so I don't know of it.


Adding a running count of distinct values in MySQL 5.7

SELECT t1.run_name, hardware_used, COUNT(*) times_used
FROM hardware t1
JOIN hardware t2 USING (hardware_used)
WHERE t1.run_name >= t2.run_name
GROUP BY t1.run_name, hardware_used
ORDER BY t1.run_name;

fiddle


Apparently the problem was in the postgresql.conf the listen_addresses line had the # still in front of it making it a comment. Removing it solved my problems.


How do I recreate a deleted logfile on an Oracle database?

See the Oracle Database Backup and Recovery Users Guide.  In particular the section titled Recovering After Losing of All Members of an Online Redo Log Group.  Your course of action will depend on other factors you can read about in the documentation.


By default, sysssislog table only exists under System Table for the System databases [msdb].
If you are seeing sysssislog in a user database, then you must have a package that is setup for execution logging and it creates the table in the specified database if it does not exist.


Index names in PostgreSQL

Index names are unique across a single database schema.
Index names cannot be the same as any other index, (foreign) table, (materialized) view, sequence or user-defined composite type in the same schema.
Two tables in the same schema cannot have an index of the same name. (Follows logically.)

If you do not care about the name of the index, have Postgres auto-name it:
CREATE INDEX ON tbl1 (col1);

is (almost) the same as:
CREATE INDEX tbl1_col1_idx ON tbl1 USING btree (col1);

Except that Postgres will avoid a naming collisions and automatically pick the next free name:
tbl1_col1_idx
tbl1_col1_idx2
tbl1_col1_idx3
...

Just try it. But, obviously, you would not want to create multiple redundant indexes. So it wouldn't be a good idea to just blindly create a new one.
Test for existence
Postgres 9.5 or newer
Now available:
CREATE INDEX IF NOT EXISTS ...
Also works for CREATE INDEX CONCURRENTLY IF NOT EXISTS.
However, the manual warns:

Note that there is no guarantee that the existing index is anything
like the one that would have been created.

It's a plain check for the object name. (Applies to variants for older versions below, too.)
To find existing indexes on the same table for the same column(s):
SELECT pg_get_indexdef(indexrelid)
FROM   pg_index
WHERE  indrelid = 'public.big'::regclass
AND   (indkey::int2[])[:] = ARRAY (
   SELECT attnum
   FROM   unnest('{usr_id, created_at}'::text[]) WITH ORDINALITY i(attname, ord)
   JOIN  (
      SELECT attname, attnum
      FROM   pg_attribute
      WHERE  attrelid = 'public.big'::regclass
      ) a USING (attname)
   ORDER BY ord
   );

Restrictions:

Only works for columns, not other index expressions.
Also reports partial indexes (with WHERE clause) and covering indexes (with INCLUDE clause).
Reports any type of index, not just B-tree indexes.

Study the results (if any) before proceeding, or refine the query to your needs ...
Further reading:

Find tables with multiple indexes on same column
Normalize array subscripts for 1-dimensional array so they start with 1

Postgres 9.4
You can use the new function to_regclass() to check without throwing an exception:
DO
$$
BEGIN
   IF to_regclass('myschema.mytable_mycolumn_idx') IS NULL THEN
      CREATE INDEX mytable_mycolumn_idx ON myschema.mytable (mycolumn);
   END IF;

END
$$;

Returns NULL if an index (or another object) of that name does not exist. See:

How to check if a table exists in a given schema

This doesn't work for CREATE INDEX CONCURRENTLY, since that variant cannot be wrapped in an outer transaction. See comment by @Gregory below.
Postgres 9.3 or older
Cast the schema-qualified name to regclass:
SELECT 'myschema.myname'::regclass;

If it throws an exception, the name is free.
Or, to test the same without throwing an exception, use a DO statement:
DO
$$
BEGIN
   IF NOT EXISTS (
      SELECT
      FROM   pg_class c
      JOIN   pg_namespace n ON n.oid = c.relnamespace
      WHERE  c.relname = 'mytable_mycolumn_idx'
      AND    n.nspname = 'myschema'
   ) THEN

        CREATE INDEX mytable_mycolumn_idx ON myschema.mytable (mycolumn);
    END IF;
END
$$;

The DO statement was introduced with Postgres 9.0. In earlier versions you have to create a function to do the same.
Details about pg_class in the manual.
Basics about indexes in the manual.


What can cause DBCC CheckDB to take longer than usual?

Lots of options, kind of hard to speculate:

Slower IO to disks, perhaps due to activity elsewhere on the SAN?
Other activity on the box interfering (user activity, maintenance jobs, other databases, etc.)
Memory or CPU pressure on the box (admin logged on locally leaving things running, big SSIS packages eating up CPU or local memory)

Narrowing down which of these it might be won't be easy, you'll probably have to turn on some long-running perfmon logs and do some trending over time.


Can I make something similar to a makefile for psql?

Write a bash script and pass those files as arguments to psql.

#!/bin/bash

set -e #abort on error

psql -v ON_ERROR_STOP=1 -f CREATE.sql
#...insert your scripts here
#...
psql -v ON_ERROR_STOP=1 -f DROP.sql

If you want the Makefile functionality (e.g. check modified dates of files and dependencies), just write a Makefile, and use the psql commands!


The maximum number of user connections by default is 32,767.  So it doesn't sound like you're reaching the cap.  It is possible to alter that limit though.

But it is worth noting that it is best practice not to leave connections open, not just because the idle state, but it can get messy and mixed up. It is best to open a connection when you require access to the database, and then immediately close it once you are done. Connection pooling takes place, so there would be little to no gain of keeping a connection open and hope other code properly closes it.


This refactoring from cursor on a dblink'ed Oracle table OK?

Joins across database links can lead to sub-optimal execution plans as Oracle doesn't have all the information available about both sites. Queries across db links can (and do) perform just fine when joined to local tables though.

If writing a new query with local and remote tables I'd start joining it all together (set-based) then only break it into separate queries if performance is unacceptable and other tuning hasn't worked.

Given your query is just a simple minus with no joins to the remote site, I would expect the refactored set-based approach to be quicker. Make sure you test it though!


What are the advantages of a monitor server for log shipping

You can monitor the process on the primary and secondaries, here's where a monitor comes in really useful though...

Let's say your log backups and copies are running just fine, no problems, but one of your secondaries goes down. You aren't monitoring the secondary and so are not aware of this. Your log shipping restore alerts are based off of the secondary server, so you are none the wiser (server being down then you are not getting restore alerts that things are out of date).

You notice a couple of days later, but by then it's too late and you have to restart log shipping from scratch.

Having a monitor server just helps to keep an eye on things and gives you a warning point not related to the servers involved in log shipping (think of it kind of like the witness server in mirroring, but without the automatic failover)


Is it bad idea to create foreign keys on the tables in a different schemas in the same database for big applications?

Schemas are nice to isolate tables from logical subsystems. Foreign keys guarantee data integrity. These are orthogonal concepts - as obviously data integrity between subsystems is also a must have. Accounting and Shipping and possibly Central Customer Data do not live in silos where a customer can get deleted while being used in accounting.
This is how I do it on SQL Server (although, again, our definition of schema is IIRC a LITTLE different from the Oracle definition).
As such, I think the requirement of the DBA is a sign of incompetence. T


Invalid Use of Group Function Using Join

Try:

SELECT  a.aircraftid 'Aircraft ID',
        COUNT(s.serviceid)
FROM    aircraft a
JOIN    service s
    ON  a.aircraftid = s.serviceid
GROUP BY    a.aircraftid
HAVING COUNT(s.serviceid) > (   SELECT  COUNT(serviceid) / COUNT(DISTINCT aircraftid)
                                FROM    service )


Several things.

1) Your trigger doesn't look right - I think you need the trigger to be on the locomotive table, rather than the locamotive_class table, and have a newly inserted/updated train have its maximum weight checked against the maximum weight for the class.

2) There is no train_weight column in the locomotive table.

Assuming the above is true, this is the trigger code you require:

CREATE OR REPLACE TRIGGER LOCOWEIGHT
BEFORE INSERT OR UPDATE ON locomotive
REFERENCING NEW AS New
FOR EACH ROW
DECLARE
  MAX_WEIGHT NUMBER;
BEGIN
  SELECT max_tow_weight INTO MAX_WEIGHT
  FROM locomotive_class
  WHERE loco_class_no = :new.loco_class_no;

   IF :new.train_weight > MAX_WEIGHT THEN
       RAISE_APPLICATION_ERROR(-20000,'Train weight has exceeded max tow weight');
  END IF;
END;

Along with:

alter table locomotive add ( train_weight number ) ;


db_datawriter vs grant delete, update, insert

You say "It seems that every table in the database is listed here and they all have update, delete, insert, and select".  If you have not explicitly verified that insert, update, and delete permissions have been provided on every object via this role; these permissions may be missing on some objects.

The role [db_datawriter] guarantees that insert, update, and delete permissions are provided on all objects within the database.  This also includes any future objects to be created.


`Encrypt Connection` causes an invalid certificate error even though server-side encryption is forced

This did not come up in my original search but I think this related question has the answer, in particular:

  One difference is that if you use self-signed certificate and the client is the one request encryption (with “Encrypt connection” option checked), then it will attempt to perform server validation on the certificate to verify the identity of the server machine so that it will be failed for a self-signed certificate since it hasn’t been signed by a trust root authority. For more information, please refer to http://blogs.msdn.com/b/dataaccess/archive/2005/08/05/448401.aspx.

Note that this is at variance with what is implied in the official documentation, suggesting that the certificate always will be checked. Evidently that is not the case. Thus, a client connecting to a SQL Server instance that has Force Encryption will be encrypted but without checking the identity of the server's certificate, unless the client explicitly sets the encryption form client-side.


How is the password file accessed when it's on an ASM diskgroup and required to connect to ASM?

ASM metadata describes the location of the password file on the ASM disk. With that information, even you can read it directly from the disk, with the help of kfed (which is installed with the Oracle binaries) and OS specific tools for reading the disk directly, for example dd, without using ASM. Same goes for the SPFILE (since 11g).
More about this topic:
ASM spfile in a disk group
The ASM password directory
And you do not need a password file to start the ASM instance, that is needed only for remote connections.


In the Vertica database, what is a namespace?

The namespace refers to a schema (select * from [schema].[tablename]). If you search the "SQL Reference guide" you will find the term used interchangeably, although, schema is used a lot more since its standard SQL.