Does your DBA get paid by how little disk space is used or something?
This is not only useless, but is an actively harmful practice.
Make one final attempt to convince your DBA (or their manager) with the following article, written by someone with unimpeachable credentials on this topic (Paul Randal, who was, for a while, in charge of development on the SQL database engine at Microsoft):
Why you should not shrink your data files:

A data file shrink operation works on a single file at a time, and uses the GAM bitmaps to find the highest page allocated in the file. It then moves it as far towards the front of the file as it can, and so on, and so on. In the case above, it completely reversed the order of the clustered index, taking it from perfectly defragmented to perfectly fragmented.
The same code is used for DBCC SHRINKFILE, DBCC SHRINKDATABASE, and auto-shrink – they’re equally as bad. As well as introducing index fragmentation, data file shrink also generates a lot of I/O, uses a lot of CPU, and generates loads of transaction log – as everything it does is fully logged.
Data file shrink should never be part of regular maintenance, and you should NEVER, NEVER have auto-shrink enabled.

(emphasis mine)
In my experience, 15-20% internal free space within databases is normal and expected. Either way it is going to be free space, there is literally no good reason to have that disk space sitting outside the file as opposed to inside the file.
If your DBA can't be educated (or replaced, and you can't appeal over their head), the only thing I can possibly suggest is to see how much you can reduce  table fragmentation, so your DBA hardly ever finds anything to shrink:

First, make sure that primary keys are set so that newly inserted rows always appear at the end in your current data order, so tables always grow in a contiguous manner.
Make sure your tables and indexes have a smaller "fill factor" (which leaves more free space within each data page), so that updates/inserts are less likely to create page splits and fragmentation.
Finally, rebuild your tables and indexes into a brand new filegroup using CREATE INDEX … WITH (DROP_EXISTING = ON) ON, as discussed near the end of Paul's article. This will make them contiguous from the very beginning of the file.
When small fragmentation does starts to occur, defragment the file, which slowly moves pages in-place, instead of rebuilding, which more often than not tries to move the table or index to a different area.

This won't keep them contiguous forever, especially if someone deletes a big range from the beginning or middle of a table, but it might help.


mariaDB not caching queries cached by mysql

There are many different "caches"; not all of them have 'cache' in their name.  The most important is innodb_buffer_pool_size.  An that one is important only if you are using InnoDB (which you should be) instead of MyISAM.  Furthermore, was the Engine of individual tables changed during the upgrade?

Check the table Engines and buffer_pool_size, then report back.  BTW, the buffer_pool should be about 70% of available ram.  And show us the non-cached queries, together with SHOW CREATE TABLE for them.

I see that the Query cache is set the same way.  However, that is usually bad for an active production server.  But that is another topic.

"Drop in replacement" -- mostly it is.  But, as you found out, it is "faster" in some ways, "slower" in others.  Even if you moved from MySQL 5.1 to MySQL 5.5, you might see similar better/worse scenarios.


Postgresql how to return nested type in a function

To create an instance of a type, you need a row constructor
So to create a value of nestedtype you need row('test1')::nestedtype. To create a value of maintype you essentially do the same, row(...)::maintype but for the second value you need again pass a proper row type:
row('test2 value', row('test1 value')::nestedtype)::maintype

This expression can be used in your function:
CREATE OR REPLACE FUNCTION mytest()
  returns maintype
as
$$
  select row('test2 value', row('test1 value')::nestedtype)::maintype;
$$
LANGUAGE sql STABLE;


I've just done what you want! You'll have to move the entire datadir and not just a single file - not sure if that's possible.

For example, in my datadir, you will find:

pol@pol:~/Downloads/sware/mysql/mysql-5.5.58-linux-glibc2.12-x86_64/data$ ls -lista
total 28724
32774484  5120 -rw-rw----  1 pol pol   5242880 Oct 24 12:47 ib_logfile0
32774483 18432 -rw-rw----  1 pol pol  18874368 Oct 24 12:47 ibdata1
32774487    16 -rw-rw----  1 pol pol     13498 Oct 24 12:47 mysql-bin.000001
32774488     4 drwx------  2 pol pol      4096 Oct 24 12:47 ptest
32774151     4 drwxrwxr-x  6 pol pol      4096 Oct 21 11:55 .
32774465     4 -rw-r-----  1 pol pol      2219 Oct 21 11:46 pol.err
32774486     4 -rw-rw----  1 pol pol         6 Oct 21 11:46 pol.pid
32774482     4 -rw-rw----  1 pol pol        19 Oct 21 11:46 mysql-bin.index
32774485  5120 -rw-rw----  1 pol pol   5242880 Oct 21 11:46 ib_logfile1
32643837     4 drwxrwxr-x 13 pol pol      4096 Oct 21 11:40 ..
32774334     4 drwx------  2 pol root     4096 Oct 21 11:34 mysql
32774404     4 drwx------  2 pol pol      4096 Oct 21 11:34 performance_schema
32774152     4 drwxrwxr-x  2 pol pol      4096 Oct 21 11:26 test

Files like ibdata1 and ib_logfile are very important! So, it's important to copy the whole datadir!

Step 0 - backup everything - DBA's motto - "Backup or F***up!" :-)

Also, you say that this is a production server - test on a non-prod
machine first.

First, take a copy of your datadir - mine's called simply data. As a dba, you should always have a path for backing out your changes!

cp -r ./data ./databak

Then copy your old data to your new directory - with your mysqld server stopped, otherwise you will have inconsistencies.

cp -r data datatest

Then remove your old data directory

rm -rf data

Edit your my.cnf file replacing your old datadir location with your new one, or adding it, if you were using a default.

vi my.cnf

Then kick off your server again.

Et voilà!


What is the SQL Server cost, in terms of performance, of sharing physical disks?

A performance factor which we saw in one of our environment was related to  high disk read write activity from one database on those shared disks. Since one had a higher transaction activity using the storage hitting hard caused slownesss for other. Also it becomes hard to troubleshoot when you have shared instance for those disks. We moved them to their respective virtual servers instead.

Also other factors which made us move was difficulty in performing any reboot activity after patching as one lob would be fine while other conflicting.

Moreover backup of databases in one instance at x timings may impact performance of other sql instance depending upon how big the servers we are talking and how good or bad the storage is.


Looks like you need a one-to-many between orders_products and Features.

Your orders_products will hold the first part of the order:

OrderItemID, OrderID, quantity, ProductID (e.g. "Item 1: 2 Chocolate Cakes")

and a new table (e.g. order_products_features) will hold the one-to-many relation needed for Features:

OrderItemID, FeatureID (e.g. "for Item 1, add "Plain")
OrderItemID, FeatureID (e.g. "for Item 1, add "Small Size")
OrderItemID, FeatureID (e.g. "for Item 1, add "Diet")

You will need some extra logic to check that users only enter valid Product-Feature combinations into this new table.

Designing it this way means that in the future, if you change the Product-Feature links, you will still have a historical record of what people ordered in the past, even though those Product-Feature links might not be valid today.


The /usr/pgsql-9.4/bin/postgresql94-setup script needs to be executed as root.

It detects the data directory parsing the service file.

This is the full command help:

Usage: postgresql94-setup {initdb|upgrade} [SERVICE_NAME]

Script is aimed to help sysadmin with basic database cluster administration.

The SERVICE_NAME is used for selection of proper unit configuration file; For
more info and howto/when use this script please look at the docu file
$README_RPM_DIST.  The 'postgresql'
string is used when no SERVICE_NAME is explicitly passed.

Available operation mode:
  initdb        Create a new PostgreSQL database cluster.  This is usually the
                first action you perform after PostgreSQL server installation.
  upgrade   Upgrade PostgreSQL database cluster to be usable with new
                server.  Use this if you upgraded your PostgreSQL server to
                newer major version (currently from $PREVMAJORVERSION \
to $PGMAJORVERSION).

Environment:
  PGSETUP_INITDB_OPTIONS     Options carried by this variable are passed to
                             subsequent call of \`initdb\` binary (see man
                             initdb(1)).  This variable is used also during
                             'upgrade' mode because the new cluster is actually
                             re-initialized from the old one.
  PGSETUP_PGUPGRADE_OPTIONS  Options in this variable are passed next to the
                             subsequent call of \`pg_upgrade\`.  For more info
                             about possible options please look at man
                             pg_upgrade(1).
  PGSETUP_DEBUG              Set to '1' if you want to see debugging output.


Return a record with PL/pgSQL function - to speed up the query

Using OUT parameters achieve basically the same thing as in @klin's answer, but without creating user-defined types. Just move all your variables from the declare block into the argument-list as OUT parameters:

create or replace function get_user_info(
    IN  _id varchar,
    OUT is_banned boolean,
    OUT reputation integer,
    OUT is_vip boolean,
    OUT completed_games integer
)
-- no returns clause necessary, output structure controlled by OUT parameters
-- returns XXX
as $BODY$
begin
    select true into is_banned from pref_ban where id=_id;

    select
    count(nullif(nice, false)) -
    count(nullif(nice, true))
    into reputation
    from pref_rep where id=_id;

    select vip > now() into is_vip from pref_users where id=_id;

    select completed into completed_games from pref_match where id=_id;

    -- no return statement necessary, output values already stored in OUT parameters
    -- return XXX;
end
$BODY$ language plpgsql;

This will return a record (exactly one), so you can select its values as a normal record:

-- this will return all properties (columns) from your function:
select * from get_user_info();

-- these will return one property (column) from your function:
select is_banned from get_user_info();
select (get_user_info()).is_banned;


Unattended/silent install of Oracle XE Express fails

Never mind, I had mistyped this line:

OracleXEUniv.exe /s /f1"c:\xe\foo.iss"  /f2"c:\xe\foo.log"

Omitted the second set of quotes.  Looks like it worked just fine.


SQL Server Service Account on Windows Server 2016 Workgroup Cluster

It turns out that only Availability Groups are supported on workgroup clusters in 2016. SQL FCI is not supported. You must use a domain.


SELECT query in a SP in AlwaysOn Availability Group Read Only Routing

Read-only routing is per connection, not per batch or statement. Read-only intent is specified via the application connection string and the connection will be directed to a read-only replica (if possible) when the connection is established.


How do you call an external C function from a SQL stored procedure?

hash_md5 is a C-UDF, which must be installed. Without qualified object name it will only be found if it's created in TD_SYSFNLIB or SYSLIB or the current session default database.
This should work:
set l_hash = mydb.hash_md5('abc');


Do my relational algebra operations produce the desired results?

This is indeed the error:

    R4 := R2 ∩ R3

If we try to get the intermediate results of your solution, we get:

R1:

bid  bname      color       sid  date
---  ---------  -----       ---  ----------
102  interlake  red         31   8/11/2014
103  clipper    green       22   7/05/2014
103  clipper    green       58   8/11/2014
104  clipper    red         22   8/10/2014

and then:

R2: (only the 'red')

bid  bname      color       sid  date
---  ---------  -----       ---  ----------
102  interlake  red         31   8/11/2014
104  clipper    red         22   8/10/2014

R3: (only the 'green')

bid  bname      color       sid  date
---  ---------  -----       ---  ----------
103  clipper    green       22   7/05/2014
103  clipper    green       58   8/11/2014

R4 will be an empty relation because nothing can be both red and green. Of course that is not what the exercise asks for.

R4:

bid  bname      color       sid  date
---  ---------  -----       ---  ----------

What you need to do is to get R2' and R3', the projections of R2 and R3 respectively, with only the attribute sid. Then take their intersection for R4:

    R1  := ...
    R2  := ...
    R3  := ...
    R2' := πsid(R2)
    R3' := πsid(R3)
    R4  := R2' ∩ R3'
    R5  := ...
    R6  := ...


It is recommended that you recycle your error log frequently. You can set that up as sql agent job to do that. I do this every night at midnight but you need to decide what is suitable for your environment.

System stored procedure "EXEC sp_cycle_errorlog" will do the recycling for you.

Once you have recycle set up you can decide how many files you want to keep (meaning how many days of error log you want to retain).

Expand the “Management” folder in SSMS.
Right click on “SQL Server Logs”
Select “Configure”
Check the box “Limit the number of error log files before they are
recycled”
Pick some value to put in the “Maximum number of error log files”
box
Click “OK”
List was copied and edited from here.


This is another example that demonstrates how Relational Databases are really good at finding lots of small bits of data and putting them together, but quite rubbish at taking large slabs of data and pulling them apart again.
Xml querying functions go some way towards improving this but by far the best way is to move the data that you'll be filtering by (i.e. that appears in where clauses in your queries) into old-fashion table fields and properly index them.  You already have a separate Phone Number field - make sure that your application code updates that when storing this data.  You might also consider triggers if you're worried about people and/or other applications changing the data by "other means".
Casting the field from XML into text completely defeats the point of holding this data as XML at all.  If that sequence of character were to appear anywhere in the entire XML structure then that record would be returned - potentially lots of "false positives".
Why is it faster with the cast?  The DBMS doesn't have to go through all the [relatively slow] XML processing and can "just" scan through the entire table, doing a [relatively quick] "string" search on the cast'ed field.
Why a table scan?  Well, you have a leading wildcard on that criteria, which will effectively prevent the DBMS from using any indexing on that field.  Expect your query performance to degrade horribly as the table grows in size.


Need to suppress rowcount headers when using \G

I have two suggestions

SUGGESTION #1 : Use grep command

You could use the grep command to filter it out

mysql -u... -p... -ANe"SHOW SLAVE STATUS\G" | grep -v "^\*\*\*\*\*\*\*\*\*"

Here is a quick demonstration:

[root@***** ~]# mysql -uroot -p... -ANe"SHOW SLAVE STATUS\G"
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.64.76.250
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.004474
          Read_Master_Log_Pos: 76212800
               Relay_Log_File: relay-bin.013419
                Relay_Log_Pos: 76212945
        Relay_Master_Log_File: mysql-bin.004474
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 76212800
              Relay_Log_Space: 76213137
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2013
                Last_IO_Error: error reconnecting to master 'replicant@10.64.76.250:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error:
[root@***** ~]# mysql -uroot -p... -ANe"SHOW SLAVE STATUS\G" | grep -v "^\*\*\*\*\*\*\*\*\*"
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.64.76.250
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.004474
          Read_Master_Log_Pos: 111533085
               Relay_Log_File: relay-bin.013419
                Relay_Log_Pos: 111533230
        Relay_Master_Log_File: mysql-bin.004474
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 111533085
              Relay_Log_Space: 111533422
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2013
                Last_IO_Error: error reconnecting to master 'replicant@10.64.76.250:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error:
[root@***** ~]#

SUGGESTION #2 : Use tail command (if expecting one row of output)

Run the \G command to a text file. Use tail to print everything but the first line:

[root@***** ~]# mysql -uroot -p -ANe"SHOW SLAVE STATUS\G" > junk.txt
[root@***** ~]# LINECOUNT=`wc -l < junk.txt`
[root@***** ~]# (( LINECOUNT-- ))
[root@***** ~]# tail -${LINECOUNT} junk.txt
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.64.76.250
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.004474
          Read_Master_Log_Pos: 411163018
               Relay_Log_File: relay-bin.013419
                Relay_Log_Pos: 411163163
        Relay_Master_Log_File: mysql-bin.004474
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 411163018
              Relay_Log_Space: 411163355
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2013
                Last_IO_Error: error reconnecting to master 'replicant@10.64.76.250:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error:
[root@***** ~]#

Give it a Try !!!


Inserting into table: either invalid datatype or duplicate column name error. (But the datatype is valid and there's no duplicate)

If you used an IDE to develop your code, the IDE should show you exactly where you have an error.
SQL Developer shows that you have a syntax error. (magenta squiggly underline)

Remove the data type definitions from your first list of columns.  (The data types are defined at the time of CREATE TABLE)
If you ever have a question about the syntax of something, always refer to the fine manual.  In your case, the "data type" (in your first list of columns) is not part of the formal syntax.  Therefore - you get an error.
Image from Documentation


Why am I seeing OPTION (MAXDOP 1) in my Actual query plan even though I've not set it in the stored proc?

You are performing an index rebuild on Standard edition.

As documented here you would need Enterprise or Developer edition to see parallelism for this.


Choice- or ids-list- field representation

To some extent any solution is going to depend to a great deal on exactly what you are doing and what RDBMS you are using.  In general though using a text field to store a comma separated list of integers is a bad idea.

The starting point solution is always id-based reference.  This is a good place to start because it obeys proper relational rules, and it allows you to handle things like multiple values by breaking these off into new relations.  In fact I would say it is always best to start here.

Alternatives however, work well in some cases and with some RDBMS's.  ENUM fields (or alternatively text domains with a check to keep them within some values) are helpful if you never need to drop options.  If you do, then you have to determine what you want to do about this before you drop them.  Secondly array types can be useful in certain cases too (and they don't necessarily break 1NF if the array as a whole is an atomic value of a domain, for example if we represent an IPv4 address as an array of 4 8-bit integers). Array-based approaches depend highly on what you are doing and db-level support, but on PostgreSQL I would choose an array where cardinality matters (i.e. an array is a tuple instead of a set), and where the semantics are entirely self-contained.


What is the best way to reload data in MongoDB

I'd recommend looking into pre-splitting and/or using a hashed shard key to do the insertion and stick with dropping the collection (with remove you are basically doing a delete for every write, so it will always be slow). The hashed shard key is usually the easiest one to get started with.

If you are looking to measure write throughput then each of those approaches (when done properly) will allow you to utilize all available shards immediately rather than hitting one shard first, waiting for the balancer to move data etc. (also, if you use a monotonically increasing shard key then you will only ever write to a single shard - see the three pitfalls posts for good descriptions of all that).


Which is better for long term database scalability: adding columns or having key/value store

Option 2 is known as "EAV" or Entity-Attribute-Value

not relational
no DB level constraints
requires contortions to read the data unless a simple list

But, it depends what you mean by "settings". If you have a few 1000 rows that are not objects and don't require constraints then, yes, use this pattern. This is what SQL Server does with sys.configurations

If you are trying to have a "flexible schema" that can store anything, then simply don't. It will end in tears. Also see EAV questions here on DBA.SE

Note that "extra columns" (option 1) allows you to define defaults and datatype-safety, whereas "lack of row" (option 2) requires a default value to be stored in code and everything is a string in the database

"It depends"


MYSQL DISTINCT on one column PLUS oher values

SELECT DISTINCT(table1.ordno), table1.invno
FROM ...

You can't use DISTINCT like that, it is not a function. It's a modifier (of the SELECT or UNION clause) and it applies to all the row, not a single column.

You can use GROUP BY instead and MIN() function to find the smallest inventory number for each order, in each table.
Then use UNION (ALL, not DISTINCT) to get these results from both tables.
Then GROUP BY again in case some inventory numbers appear in both tables:

SELECT ordno, MIN(invno) AS invno
FROM
  ( SELECT ordno, MIN(invno) AS invno
    FROM INInvoiceHeaders
    WHERE invdate >= DATE '2016-11-01'
      AND invdate  < DATE '2016-11-01' + INTERVAL 1 MONTH

    UNION ALL

    SELECT ordno, MIN(invno)
    FROM INArchivedInvoiceHeaders
    WHERE invdate >= DATE '2016-11-01'
      AND invdate  < DATE '2016-11-01' + INTERVAL 1 MONTH
    GROUP BY ordno
  ) AS t
GROUP BY ordno ;


Based on your reply of

SELECT ASCIISTR(titolo) FROM generale WHERE titolo LIKE 'Roma drogata%;

TITOLO
----------------------------------------------------------------
Roma drogata: la polizia non pu\FFFD\FFFD intervenire

You get Roma drogata: la polizia non pu?? intervenire because it has been inserted wrong into your database, i.e. when the record was inserted the client had set NLS_LANG wrong.

Unicode character U+FFFD is this one: REPLACEMENT CHARACTER

Probably the SQL script was saved as UTF-8 but NLS_LANG was set to ISO-8859-1 or Windows-Cp1252 or similar.


SQL Server 2014 compression and maximum row size

The limit that you're running into has nothing to do with the data stored on the page. The calculation is done based on the data types of the columns. That's why you run into the error without any data in the table. Compression makes this limit worse. You can read about the technical details behind the overhead here.

You can workaround this issue by using SPARSE columns. That means that it'll be possible for inserts to fail depending on what you insert, but you can bypass the 8060 byte limit. The following code shows that you can create 1023 columns just fine:

drop table t1
GO
create table t1(c1 decimal(26, 8) null)
GO

declare @i int = 2;
declare @sql varchar(100);
while @i <= 1023
begin
    set @sql = 'alter table t1 add c' + convert(varchar, @i) + ' decimal(26, 8) SPARSE null';
    execute (@sql);
    set @i += 1;
end;
GO

However, all of the restrictions around it (read the linked article) may make this not suitable for your use case. Specifically, only NULL values (not 0) are optimized to take up very little space. If you try to insert too many 0s in a single row you'll get an error. Here's what I see when I try to insert 1023 0 values:

  Msg 511, Level 16, State 1, Line 1
  Cannot create a row of size 17402 which is greater than the allowable maximum row size of 8060.

I suppose that if you got really desperate you could create the columns as VARCHAR(27) instead. Variable length columns can be moved off page so that you can exceed the 8060 byte limit in the table definition but inserting certain combinations of values will fail. SQL Server warns you of this when creating the table:

  Warning: The table "t1" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.

Page or row compression may be helpful if you go with the VARCHAR(27) approach. That will minimize the space used by both 0 and NULL. With VARCHAR(27) I'm able to insert 1023 0 values just fine.


Need a help in Query to check whether Task is completed by other user or not before showing on dashboard

SELECT  A1.*,
        CASE WHEN A2.taskId IS NULL THEN 'Task Completed'
        ELSE 'open' END AS CurrentStatus
FROM AssignedTo A1
LEFT JOIN (
    SELECT taskId
    FROM AssignedTo
    WHERE status IN ('open', 'close')
    GROUP BY taskId
    HAVING COUNT(DISTINCT status) = 1
    ) A2 ON A2.taskId = A1.taskId
ORDER BY A1.taskId;

Fiddler: http://rextester.com/CAHOPS39734

Help from this post

The above query will return one extra column as CurrentStatus. If the taskId has any close status, for those tasks the CurrentStatus will be Task Completed and for remaining tasks it will show as open only.

So the output will be:

id  taskId  userId  status  CurrentStatus
1   1       1       open    open
4   1       4       open    open
2   1       2       open    open
3   1       3       open    open
5   2       1       open    Task Completed
6   2       2       close   Task Completed
7   3       1       open    open
8   3       2       open    open


please check your alert.log for your database instance
(your database instance must running).

if you use catalog database, it must be open (the target database does not need to be open).


How keep overview about database permissions?

I do not believe that this is truly possible, at least not in any truly accurate and meaningful way. The problem is, even taking into account the level of complexity that you have already identified (emphasis added):

  information about User-Permissions on object-level directly as well as through DB-Roles and also recursive DB-Role-in-DB-Role permissions inherited.

permissons are still quite a bit more complicated and involve several more layers.

The various layers of permissions that I am aware of are (the first 3 you already mentioned):

(A) Base permissions

Direct grants to Database User on the Object
Grants implied by membership in one or more Database Roles that has permissions on the Object
Grants implied by membership in one or more Database Roles that is in turn a member of one or more Database Roles, across any number of levels of Database Role membership where at least one Database Role has permissions on the Object
Grants implied by Schema-level permissions
Grants implied by Database-level permissions
Grants implied by membership in one or more Fixed-Server Roles by Server Login associated with the Database User
A DENY anywhere in the chain should supersede any GRANTs

Then you need to take into account:

(B) Windows Groups-based Logins

You can verify if a Login is a member of a particular Windows Group using the IS_MEMBER built-in function, but there is no way to determine what Windows Groups exist to be a member of (well, at least not without using SQLCLR ;-).
When using Logins based on Windows Groups, permissions are additive across all Group-based Logins and directly mapped Logins, but can be negated by a DENY.
Windows Logins can be associated with multiple Windows Groups, each Group having it's own Login, but no Login mapped to the Windows Login
Windows Logins can be associated with one or more Windows Groups and also having a Login mapped to the Windows Login

AND THEN you need to take into account the effects of:

(C) Permissions granted at run-time

A Login/User having IMPERSONATE permission on another Login/User having more permissions than the original Login/User
Any Stored Procedure, Function (excluding Inline TVFs), or Trigger created with the EXECUTE AS clause
Ownership Chaining
Module Signing (i.e. granting permissions to code -- Assembly, Stored Procedure, Function (excluding Inline TVFs), or Trigger -- using an Asymmetric Key or Certificate)

Using the sys.fn_my_permissions function you can loop through the various objects in a particular Database and get a list of effective permissions for the current User. And if you want the permissions of another User, then you have to use EXECUTE AS USER = 'user_name'; and then run that loop, and then execute REVERT;. You probably only need to check for a securable_class of OBJECT. So you could try something along the lines of the following:

SET NOCOUNT ON;

DECLARE @InnerSQL NVARCHAR(MAX) = N'';
SELECT @InnerSQL += N'SELECT SESSION_USER, * FROM fn_my_permissions(N''' + ss.[name]
                    + N'.' + so.[name] + ''', ''OBJECT'');' + NCHAR(0x0D) + NCHAR(0x0A)
FROM   sys.objects so
INNER JOIN sys.schemas ss
        ON ss.[schema_id] = so.[schema_id]
WHERE  so.[is_ms_shipped] = 0
AND    so.[type_desc] IN ('AGGREGATE_FUNCTION', 'CHECK_CONSTRAINT',
            'CLR_SCALAR_FUNCTION', 'CLR_STORED_PROCEDURE', 'CLR_TABLE_VALUED_FUNCTION',
                          'CLR_TRIGGER', 'DEFAULT_CONSTRAINT', 'FOREIGN_KEY_CONSTRAINT',
                          'PRIMARY_KEY_CONSTRAINT', 'SEQUENCE_OBJECT',
                          'SQL_INLINE_TABLE_VALUED_FUNCTION', 'SQL_SCALAR_FUNCTION',
                          'SQL_STORED_PROCEDURE', 'SQL_TABLE_VALUED_FUNCTION',
                          'SQL_TRIGGER', 'SYNONYM', 'UNIQUE_CONSTRAINT', 'USER_TABLE',
                          'VIEW');

-- PRINT @InnerSQL;

DECLARE @OuterSQL NVARCHAR(MAX) = N'----------------------------------'
                                  + NCHAR(0x0D) + NCHAR(0x0A);
SELECT  @OuterSQL += N'EXECUTE AS USER = ''' + dp.[name] + ''';'
                     + NCHAR(0x0D) + NCHAR(0x0A) + NCHAR(0x0D) + NCHAR(0x0A)
                     + @InnerSQL + NCHAR(0x0D) + NCHAR(0x0A)
                     + N'REVERT;'+ NCHAR(0x0D) + NCHAR(0x0A)
                     + N'----------------------------------' + NCHAR(0x0D) + NCHAR(0x0A)
FROM    sys.database_principals dp
WHERE   dp.[type_desc] IN ('SQL_USER', 'WINDOWS_USER')
AND     dp.[principal_id] > 4
AND     LEFT(dp.[name], 5) <> N'##MS_'
AND     LEFT(dp.[name], 3) <> N'MS_';

--PRINT @OuterSQL;

IF (OBJECT_ID(N'tempdb..#Permissions') IS NULL)
BEGIN
  -- DROP TABLE #Permissions;
  CREATE TABLE #Permissions
  (
    UserName sysname,
    EntityName sysname,
    SubentityName sysname,
    PermissionName NVARCHAR(100)
  );
END;

INSERT INTO #Permissions (UserName, EntityName, SubentityName, PermissionName)
  EXEC (@OuterSQL);

SELECT * FROM #Permissions;

HOWEVER, you can only get info from section A above, and not even all of that given that you cannot get permissions for Windows Groups-based Users as you cannot use EXECUTE AS on those.

AND, you cannot take into account anything from section B without actually logging in as each individual Login.

AND, you cannot determine any net-effect from anything from section C without cross-referencing everything previously determined and figuring out some means of determining "effective permissions" on Users that cannot be impersonated (e.g. Certificate-based, Asymmetric Key-based, Windows Groups-based, etc), hence they were not discovered by the query shown above.

So, um: No :-(.


Which schools are known for graduation level database research?

MIT is the home of Michael Stonebraker, probably the leading RDBMS researcher working in academia today. A lot of the research is done in private labs tho', e.g. CJ Date and Ted Codd did much of their early work at IBM.

I am missing a huge amount here as I am only really interested in RDBMS research; there's probably a lot more going on in OODBMS research.


How to store a query result into a variable? SQL Server 2008

If you are looking to set the ID variable parameter, you need to specify it as an OUTPUT parameter, and set it's value in your query:

Create procedure addFaculty
@ID int OUTPUT
as
begin
    IF NOT EXISTS (Select AccountType from UserTable where AccountType = 'Faculty')
    begin
        Insert into CredentialsTable values ('iamafaculty');

        Select @ID = CredentialsTable.CredentialsID
        from CredentialsTable
        where CredentialsTable.Password = 'iamafaculty';
    end
end

Is that what you are asking?


mongodb running single instance with --auth flag enabled is it means mongob running autorization enabled?

You should definitely follow the authentication schema upgrades and other procedures as documented in the relevant version upgrade instructions. The documented procedures are the only supported (and tested) upgrade path.

Upgrade steps for authentication schema changes are particularly essential, but skipping any required steps or versions on the upgrade path is very likely to cause issues with your deployment.

You should also take a backup before each major version upgrade. If something goes awry in your upgrade process or some required steps have been skipped, you will then have the option of restoring a previous known-good deployment.


alter table which is under the load (sql server)

Announce that over the weekend there will be a planned maintenance event where new notifications will be delayed for X hours.
Make a copy of the table. Redirect all reads there.
Disable any writes from the application or queue them up if that's unacceptable.
Alter the table.
Apply any queued up writes.
Set everything back.


Assuming you have the .dtsx files, they are just XML so you could use a simple text search of the files, something like this
https://stackoverflow.com/questions/8153750/how-to-search-a-string-in-multiple-files-and-return-the-names-of-files-in-powers


How to get all data before and after 10 days of interval?

It is probably best to issue two queries.

SELECT * FROM events
    WHERE `date` >= NOW() - INTERVAL 10 DAY
      AND `date`  < NOW();

SELECT * FROM events
    WHERE `date` >= NOW()
      AND `date`  < NOW() + INTERVAL 10 DAY;

You could get both at once, but it could be inconvenient if you need to separate them:

SELECT * FROM events
    WHERE `date` >= NOW() - INTERVAL 10 DAY
      AND `date`  < NOW() + INTERVAL 10 DAY;

If you want to base on midnight instead of the current second, use CURDATE() instead of NOW().


How to "cascade delete" children without deleting parent?

Simply re-insert deleted records:

WITH cte AS ( DELETE
              FROM parent
              WHERE {conditions}
              RETURNING * )
INSERT INTO parent
SELECT *
FROM cte;

fiddle


Does SQL server optimize or pre-parse stored procedures?

All queries executed by SQL Server need to be compiled first. Stored Procedures are no different in this respect, they don't get any kind of additional optimisation.

They are parsed when created to check invalid syntax but they don't actually get an execution plan generated until first executed. When the plan is compiled it will use the parameter values passed in this initial execution to generate it. This can lead to parameter sniffing issues.

All execution plans generated will remain in the plan cache generally until either  evicted due to memory pressure or they becomes invalid due to schema changes or it gets an optimality based recompile. (For adhoc queries the "optimize for ad hoc workloads" option can change this behaviour).

The main benefit of stored procedures is that for subsequent similar queries the plan is more likely to be re-used. For adhoc queries this is dependant on auto parametrisation which is quite conservative so you may well end up with lots of compilations for very similar queries but with different parameter values that all end up in the same plan anyway.

I have left out a lot of details in this answer. See this plan caching white paper for full details


Since your databases are in simple recovery mode checkpoint will be taken care of by database engine either automatically or when log file grow 70 % of its size, you can also give manual checkpoint . But still a long running transaction can hold log hostage and would not allow it to truncate even if you manually give checkpoint.

Log backup would not be possible in your case since recovery model is simple.

You can use Bulk logged recovery model to take benefit of bulk logged features supported.But non minimally logged transactions will be fully logged in bulk logged recovery model so be careful. Since you dont care about point in time recovery you can switch recovery model between full and bulk logged as required.

http://technet.microsoft.com/en-us/library/ms191244(v=sql.105).aspx

Laslty if your team is doing huge DML which generates lot of logs you would ask them to break there DML in chunks to avoid filling of logs. Index rebuild of huge table also generates massive logs.

Use truncate table to remove records from table( if you want to remove complete record) as it is mildly logged as compared to delete but dont use if table has Foreign key constraints and Indentity.
http://msdn.microsoft.com/en-us/library/ms177570.aspx

Above points will surely give you some relief with huge logging.

Hope this helps


Synchronize 2 sql server 2019 database over intermittent connection (no replication)

Well... it turn out it couldn't be easier.
Right click on database -> Tasks -> Copy database...


Sqllogship.exe: replace with powershell?

I coded a script to ship logs manually using PowerShell. You can find it here

Basically it does what native Log Shipping does: it takes transaction log backups, copies to a shared folder and restores to the secondary WITH NORECOVERY.

Some limitations apply:

Only a single secondary is allowed
No support for STANDBY

I coded it this way because it was enough for what I had to do, which is what you do 99% of the time with log shipping.


how to download oracle database to a remote server

Using WGET to download My Oracle Support Patches (Doc ID 980924.1).

wget --http-user=<username> --http-password=<password> --no-check-certificate --output-document=filename "paste the copied download address here in quotes"

Also there are a bit different instructions in https://blogs.oracle.com/supportportal/entry/use_of_wget_instead_of_downloa but I believe things a bit changed now when flash MOS page is retired.


I will tell you my experience in defining the terms of "large", "big", "a lot":

a lot is a database that takes around 400 GB for the data of a full month (custom logging information from all our web apps)
big is a table in this database that contains much of this space :-).. about half of this size (200 GB a table, from what I kinda remember)
large is the data in a full day (about 12-15 GB) - which means a partition of that magic table

This is not a rule or a best practice.. but when you feel that your current indexing strategy is a bit lost and nothing seems to make your queries faster, I believe that it's time to take into account the partitioning.


Why would adding a column to a view corrupted a table-valued function?

Yes, this is expected. Functions and views that use SELECT * end up storing part of the metadata themselves relating to the underlying columns, so it is very easy for them to get confused. I talk about this problem in the following post from 2009:

Bad habits to kick : using SELECT * / omitting the column list

And also in my recent GroupBy presentation:

T-SQL : Bad Habits and Best Practices

How do you avoid this problem?

Don't use SELECT * in objects.
Make your functions and views WITH SCHEMABINDING, which means you can't modify the underlying objects without also modifying the objects that reference them (this also has the side effect of prevent * so win-win).
In this specific case, you can fix the object so that Bar returns the correct data by using:

EXEC sys.sp_refreshsqlmodule @name = N'dbo.myTVP';

Incidentally, the documentation for sys.sp_refreshsqlmodule describes this exact scenario:

  Updates the metadata for the specified non-schema-bound stored procedure, user-defined function, view, DML trigger, database-level DDL trigger, or server-level DDL trigger in the current database. Persistent metadata for these objects, such as data types of parameters, can become outdated because of changes to their underlying objects.

As an aside, you should never use FROM myTVP() but instead always specify the schema, e.g. FROM dbo.myTVP(). I talk about that in an old post, too.


Correct case for each reserved word in SQL Server?

So,

Writing it as an answer for anyone else who might need this in the future-the answer to this question is described very nicely in the Why I use lower case for data type names (now) blog post from Aaron Bertrand.

As he explains in his blog post, this is important when using different database collations from the default one, for example (from the blog):

Considering this database:

CREATE DATABASE floobmort COLLATE Latin1_General_BIN2;
GO
USE floobmort;

The following query will provide an empty result set because "That comparison tries to compare N'INT' to what's stored in the table, N'int', and because the comparison is done using a binary collation, it returns false."

SELECT system_type_id
FROM sys.types
WHERE [name] IN (N'INT', N'VARCHAR');


Maintenance plan: Confirmed server as primary but switched to secondary during backups?

We use ola hallengren backup scripts/jobs and that does check the cluster settings for each database.

For each database it will check, can I backup this database?, using the cluster settings and current cluster status. So if your AOAG is set to only allow backups from the Primary it wont backup a database that is currently a secondary.

Works great for us, the only issue we run into is the currently running DB backup will usually fail during a failover event because all connections are briefly severed.


pt-table-sync fails "Turning off AutoCommit"

I think the bug is in your copy of DBD::mysql which seems to have been fixed sometime before version 4.027. Make sure your copy of DBD::mysql is up to date. 4.041 was released in Nov of 2016.

cpan DBD::mysql


Actual Execution Plan - Estimated/Actual Rows Differ Greatly

ClientRequestID in tblCR_ClientRequestTIP has no statistics, and SQL Server would love to have some.  You can see that warning in the actual execution plan.  This leads me to believe perhaps you have auto create statistics turned off.  This may be causing issues.  Have you updated statistics recently?

I'd consider adding the following indexes:

CREATE NONCLUSTERED INDEX IX_tblCR_ClientRequestTIP_ClientRequestID
ON dbo.tblCR_ClientRequestTIP(ClientRequestID)
INCLUDE (TIP_BeginDate, TIP_LocationName1);

CREATE NONCLUSTERED INDEX IX_tblCR_ClientRequestJOB_ClientRequestID
ON tblCR_ClientRequestJOB(ClientRequestID)
INCLUDE (JOB_Date);

CREATE NONCLUSTERED INDEX IX_tblCR_ClientRequestPP_ClientRequestID
ON dbo.tblCR_ClientRequestPP(ClientRequestID)
INCLUDE (PP_BeginningDate)

(there are others that could be added as well, this is just a sample)

Also of note, tblCommitment, tblStaff, and tblProgram do not have clustered indexes.

The tblClientRequestJOB row estimates are not exactly perfect, but the actual data size vs the estimated memory required for that table shows that row estimate is not really problematic.  I doubt SQL Server would choose a different plan if it knew the actual row counts ahead of time in that instance.  A far bigger change is likely to come from getting rid of the eager index spool resulting from the clustered index scan of tblCR_ClientRequestTIP.


How to assign a constant to a value on all rows, if that value is adjacent to some value on any row

Reading the explanation in the question, it seems that you don't really care about the ordering but you want to know if for the same name there is at least one row with status = 'ACTIVE'.

This can be done with a window function, using OVER (PARTITION BY name). The CASE expression has a default of NULL so MAX() will return null if there is no such value. The final COALESCE() will convert those nulls to 'INACTIVE':

SELECT
    status, name,
    COALESCE(MAX(CASE WHEN status = 'ACTIVE' THEN status END)
               OVER (PARTITION BY name),
             'INACTIVE') AS custom
FROM
    table_name ;


Connect to your Analysis Services server in SQL Management Studio.  Right-click the server, then click Properties in the menu.  In the Properties dialog, check the "Show Advanced (All) Properties" check box at the bottom of the list.  Then, in the grid above that check box, set the location you want for SSAS TempDB in the "TempDir" setting.

You will have to restart SQL Server Analysis Services for the change to take effect.


Is there a performance gain in specifying the table name in joins and using explicit cast?

It is a good practice to use aliases to qualify tables etc

Select a.c1, b.c2
From table1 as a
 ....

However, that has very little to do with performance. Identifying each table etc is only a fraction of what the optimizer does and I would be surprised if it even is noticeable.


What is the consensus on using NOT NULL constraints on all text-based fields in a table?

Semantically that data IS NULL, setting it to something else is awkward at best. We can always assume NULL is NULL, we know what that is. if you use '' then all of a sudden you have to keep track of what you're setting as your new NULL. Just use the construct of the system where possible.

Using a value for a null has problems as well; a query will never match a null to anything; it's not a value so no range of options will ever match it; the exception is the IS NULL condition, which has special semantic meaning as NULL has special semantic meaning.

In addition you're dubiously exploiting the database's NOT NULL constraint. That constraint is meant to mean that column always has data. Your data technically fulfills that constraint, but it does not semantically.

The only reason I can see for setting NOT NULL and using a homebrewed "NULL" value is when an application is inserting it's own data and it has it's own value for NULL. For instance PHP's NULL when stored to a database column will appear as a value, not a SQL NULL. This can be avoided with proper programming in the application however, and I would only consider the "fake NULL" solution if I had no control over the application that was inserting "fake nulls"-- at that point the battle is lost, and your solution becomes the most applicable.


How to get the rowid in a TRIGGER for another table in SQLite

Based on CL's suggestion to take another look at last_insert_rowid() and now that it's clear that INSTEAD OF triggers are only for VIEWs (see question's comments), here's a working version:

-- We need a view to create the trigger on
CREATE VIEW Container_Auto AS select * from Container;

-- Since it's automatic we need to check if we need to create a root
CREATE TRIGGER Rooter_Auto
INSTEAD OF INSERT ON Container_Auto WHEN (new.root IS NULL) BEGIN
    INSERT INTO Item(name, parent) VALUES ('ROOT', NULL);
    INSERT INTO Container VALUES (new._id, new.name, last_insert_rowid());
END;
-- If we don't need a root (i.e. the client supplied it), just pass the values through
CREATE TRIGGER Rooter_Transparent
INSTEAD OF INSERT ON Container_Auto WHEN (new.root IS NOT NULL) BEGIN
    INSERT INTO Container VALUES (new._id, new.name, new.root);
END;

-- Tests
INSERT INTO Item VALUES (1, 'ROOT', NULL);
INSERT INTO Container VALUES (1, 'My Box', 1);
INSERT OR IGNORE INTO Container(name) VALUES('Won''t work');
INSERT INTO Container_Auto(name) VALUES('All Automatic');
INSERT INTO Container_Auto(_id, name) VALUES(100, 'Specific ID');
INSERT INTO Container_Auto(name, root) VALUES('Specific root', 1);

-- Results
SELECT '---- Container ----';
SELECT * from Container;
SELECT '---- Item ----';
SELECT * from Item;

Output with comments:

---- Container ----
1|My Box|1          -- everything as we specified
                    -- note the OR IGNORE failed because root IS NULL
3|All Automatic|2   -- the failed insert incremented the rowid as well as created a new root
100|Specific ID|3   -- the supplied _id is used, new root is created
101|Specific root|1 -- the supplied root is used, _id is incremented
---- Item ----
1|ROOT|             -- created manually
2|ROOT|             -- created automatically by INTO Container_Auto(name)
3|ROOT|             -- created automatically by INTO Container_Auto(_id, name)

Notes:

Using INSERT INTO Table VALUES format helps to catch schema modification bugs since when a new column is added the INSERT will fail instead of ignoring the new column.
One can still use the Container table to insert into.
last_insert_rowid() doesn't work when used after an INSERT INTO on a view with INSTEAD OF INSERT, i.e. after INSERT INTO Container_Auto, you'll have to requery the inserted row :(


SQL Server Linked server query problem (group by)

The latest full client will work fine with 10g.
I have experienced similar problems using anything other than the full client, even though according to Oracle the others should work.
Just make sure that you completely uninstall all elements of the previous installs as having multiple clients/versions can create another set of problems.


Oddities with AT TIME ZONE and UTC offsets

A time zone name carries more information than an abbreviation or a simple time zone offset. 'UTC-6' is a "POSIX-style time zone specification" which is just an abbreviation plus offset.
The manual on Time Zones:

  PostgreSQL allows you to specify time zones in three different forms:

  A full time zone name, for example America/New_York. [...]
  A time zone abbreviation, for example PST. [...]
  In addition to the timezone names and abbreviations, PostgreSQL will
  accept POSIX-style time zone specifications of the form STDoffset or
  STDoffsetDST, where STD is a zone abbreviation, offset is a numeric
  offset in hours west from UTC, and DST is an optional daylight-savings
  zone abbreviation, assumed to stand for one hour ahead of the given
  offset.

The difference you observe stems from another oddity. You have to use + instead of -:

timezone('UTC+6', '2017-01-01 12:00:00'::TIMESTAMP AT TIME ZONE 'UTC')

The manual again:

  Another issue to keep in mind is that in POSIX time zone names,
  positive offsets are used for locations west of Greenwich. Everywhere
  else, PostgreSQL follows the ISO-8601 convention that positive
  timezone offsets are east of Greenwich.

However, even after fixing the offset error, both expressions are still not equivalent. Among other things, a time zone name like 'America/Chicago' also considers rules for daylight saving time (DST).

BTW, your expression can be simplified to:

timestamptz '2017-01-01 12:00:00 +0' AT TIME ZONE 'UTC+6'

But you probably want the time zone name, to be safe:

timestamptz '2017-01-01 12:00:00 +0' AT TIME ZONE 'America/Chicago'

Related:

"AT TIME ZONE" with zone name PostgreSQL bug?
Ignoring timezones altogether in Rails and PostgreSQL

To address your second example:

SELECT '1:00 -1'::time with time zone AT TIME ZONE '-1';

Simplified, equivalent syntax:

SELECT timetz '1:00 -1' AT TIME ZONE '-1';

The two instances of the literal -1 have different meaning. The first offset is part of the *timetz* literal signifying a location east of Greenwich (complying to the SQL standard). The second is a POSIX-style time zone specifications signifying an offset west of Greenwich. In the absence of a zone specification, UTC is assumed as base. See:

Why does postgresql interpret numeric UTC offset as POSIX and not ISO-8601?

Also note that the AT TIME ZONE construct returns timetz for timetz input. It just re-bases the time literal on a different offset. This is different from its use with timestamp / timestamptz input, where the data type is also switched.

But do not use the data type timetz (time with time zone) at all. It's broken by design and only included in Postgres because it's part of standard SQL. It's use is explicitly discouraged. See:

What is a valid use case for using TIME WITH TIME ZONE?


How to Implement a Retry Feature in SSIS 2012 When a Database Connection Fails?

There is a way to setup the number of times to retry and to set the interval between each try inside the SQL Agent Job. Simply open the job and edit the job step. Click on advanced link. Once there, you can set the retry attemps and retry interval see below. You can also setup the package to restart from the point of failure. I have not done that personally but I am sure there is a blog that talks about it. This one might help. http://www.mssqltips.com/sqlservertip/1408/integration-services-checkpoints-to-restart-package-from-failure/


The trick here is to realize that these characters that you see in the question with the "accents" aren't really the characters (i.e. "These aren't the droidscharacters you are looking for" ;-) ). The "accents" are various types of notations indicating things like:

vowels (lines and dots that are typically under the letters):

  base letter "ה" = "h"; "הֶ" = "heh" and "הָ" = "hah"
pronunciation (dots that are usually inside or above letters):

  "בּ" = "b" vs "ב" = "v", or "שׂ" = "s" vs "שׁ" = "sh"
punctuation
cantillation (how it should be sung)

The actual Hebrew letters are what is shown in the stripped down version (i.e. the end result of what is being requested here). What we are referring to here as "accents" are known as diacritical marks. The Wikipedia article on Hebrew diacritics has a lot of good information about these marks, including the following image and caption:

Gen. 1:9 And God said, "Let the waters be collected".
Letters in black, pointing in red, cantillation in blue

Getting from those base characters to what the first line (with the vowels, etc) shows is a matter of adding one or more "accents". Unicode (UTF-16 in SQL Server, though default interpretation only handles the UCS-2 / Basic Multilingual Plane (BMP) code points) allows for some characters to overlay another non-overlay character when adjacent to them. These are known as Combining Characters.

Meaning:

SELECT DATALENGTH(N'מַ֖'); -- character taken from original given text

Returns:

6

not 2 as most people would expect from seeing a single, double-byte character. So maybe we try to find what character is there by doing:

SELECT UNICODE(N'מַ֖');

which returns:

1502

Of course, the UNICODE and ASCII functions only return the INT value of the first character of whatever string they are given. But a value of 1502 only covers 2 bytes, which leaves 4 bytes unaccounted for. Looking at the binary/hex values of that same Hebrew "character":

SELECT NCHAR(1502), CONVERT(BINARY(2), UNICODE(N'מַ֖')), CONVERT(VARBINARY(10), N'מַ֖');

we get:

מ
0x05DE  0xDE05B7059605

Now, 0x05DE is the hex representation of 1502, and the 1502 is only the "מ". The next part can be separated into three 2-byte sets: DE05 B705 9605. Now, Unicode string values are stored in Little Endian, which means the byte-order is reversed. If we switch each of those three sets we get:

05DE (the base character) 05B7 0596 (the unaccounted for 4 bytes).

Ok. So what happens if we remove that base character?

SELECT REPLACE(N'מַ֖' COLLATE Hebrew_BIN2, NCHAR(1502) COLLATE Hebrew_BIN2, '');

That returns the two remaining characters (not easy to see here so I have made the following line a header in order to increase the font size; you can also run the above REPLACE to see them):

Removing the מ from the מַ֖ leaves two characters at the bottom: ַ֖

Hence, we need to strip out each individual code-point that is one of these "extra" combining characters (found at: http://unicode-table.com/en/search/?q=hebrew) and that will leave us with the base characters. We can do that via:

CREATE FUNCTION dbo.RemoveHebrewAccents (@txeTwerbeH NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
WITH SCHEMABINDING
AS
BEGIN

  WITH base (dummy) AS
  (
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
  ), nums AS
  (
    -- we will want to generate code points 1425 - 1479
    SELECT TOP (55) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS [Num]
    FROM   base b1
    CROSS JOIN base b2
  )
  SELECT @txeTwerbeH = REPLACE(
                               @txeTwerbeH COLLATE Hebrew_BIN2,
                               NCHAR(1424 + nums.[Num]) COLLATE Hebrew_BIN2,
                               ''
                              )
  FROM   nums;

  RETURN @txeTwerbeH;
END;

And then we can test it with the original text as follows:

DECLARE @Hebrew NVARCHAR(200) = N'בְּרֵאשִׁ֖ית בָּרָ֣א אֱלֹהִ֑ים אֵ֥ת הַשָּׁמַ֖יִם וְאֵ֥ת הָאָֽרֶץ';

SELECT dbo.RemoveHebrewAccents(@Hebrew);

Returns:

בראשית ברא אלהים את השמים ואת הארץ

Additional notes:

Technically, there is a set of code-points between 64298 and 64334 that do have some vowels and pronunciation "accents" built into the character. If those need to be handled, that can be a second step in the function to do a simple replacement of those characters.
It seems that these accent, punctuation, etc code-points only match when using a binary collation. Even using Hebrew_100_CS_AS_KS_WS_SC did not match them. But the following did work: Hebrew_BIN, Hebrew_BIN2, Latin1_General_BIN, and Latin1_General_BIN2. In the function I ended up using Hebrew_BIN2. Please note that when using binary collations, unless you have a specific need to use the older _BIN collations, you should only be using the newer _BIN2 collations.
For anyone who is curious, the Hebrew sample text is actually Bereishis 1:1 (that is also the first word on the right-side as Hebrew is read right-to-left; in English it would be "Genesis 1:1" though that is not a direct translation of the word, just the name of the first book of the Torah / Bible; the direct translation is "in the beginning"):

  In the beginning of God's creating the heavens and the Earth

2015-01-19: I found some great resources which explain both Combining Characters and the Hebrew character set:

http://en.wikipedia.org/wiki/Combining_character (also linked towards the top of this answer)
http://www.alanwood.net/unicode/combining_diacritical_marks.html
http://www.alanwood.net/unicode/hebrew.html
http://people.w3.org/rishida/scripts/pickers/hebrew/  (play with all of the characters in the Hebrew character set; very cool!)


Percona XtraBackup can stream the backup over the network directly to the second server, and use no disk space on the source server.

See examples here:

http://www.percona.com/doc/percona-xtrabackup/2.1/howtos/recipes_ibkx_stream.html


Connect to SQL Server 2012 Database from 2008 Agent Job containing 2008 SSIS Package

Yes, most likely. This is a short answer, but it sounds like you are really just asking - Can a job on SQL Server 2008 calling an SSIS package built on 2008 connect to a database in SQL Server 2012 and do some work within that database?

SSIS can connect to many OLEDB/ODBC compliant connections. You could, for instance use an SSIS 2008 (or 2005 or 2012) Package to read data from one Oracle database, do some transformations and the write the results of that to another Oracle database, never talking to SQL except the SSIS engine.

So an SSIS package in 2008 should be able to work with a connection to 2012. Some caveats like - if you are using a deprecated command structure in an execute SQL task - it will fail.. Or test it and verify first. But this should work, if you are just asking if - from within the SSIS package can I talk to SQL Server 2012.


Expressing some queries without employing JOIN or OUTER JOIN

Left outer join will be:

select s.a, s.b, (select c from t where s.b =t.b) as outercol
from s

Full outer join will be:

select s.a, s.b, (select c from t where s.b =t.b) as outercol
from s
union all
select null, b, c from t
where b not in (select b from s)


Unable to pass scema.object to the query shortcut in SSMS 2016

The result of a shortcut must still be valid SQL. As the documentation makes clear, the parameter values must be surrounded by quotes. You could use trace or Profiler to capture the actual code submitted to see exactly what's going wrong.

As the additional text seems to be appended to the shortcut rather than embedded in it there's no way to pre-wrap the additional text in quotes.

Perhaps a custom snippet would suit your needs better? Though it does take a little work to establish and you're obviously sensitive to gratuitous keystrokes.

If you're feeling very brave you could change the user's default schema inside the dynamic SQL and omit the schema from the appended text, removing the need for quotes.


Significance of has_unchecked_assembly_data due to a persisted computed column after changing compatibility level?

Execute DBCC CHECKTABLE or DBCC CHECKDB to set the has_unchecked_assembly_data to zero.  This is documented in the SQL Server Books Online sys.tables reference.


Does REDUNDANCY retention policy guarantee PITR from the earliest backup?

Does RMAN retain all archivelog backups necessary for PITR to any point since the earliest retained backup?

No, at least not if you have incremental backups in the mix:

  …we don’t guarantee a recoverability to a certain point  in time, just, the number of backupsets.

I'm not sure if the same applies if you aren't using incrementals, but there are too many edge cases with using a redundancy-based policy (see this horror story for example). Use a recovery window instead if you possibly can. The article you quote has the same advice:

  The safest option is to use the redundancy window as several backups on a single day could cause the redundancy option to mark valuable files as redundant.


Oracle RAC failover not working correctly

SCAN is supposed to give you connect-time failover, but not run-time failover (like TAF). Where did you get the (mis-)information that SCAN also means TAF?


You can use LISTAGG() function to gather the groups/tuples, then another GROUP BY to count occurrences of every group/tuple.

Assuming that (order_number, value) is unique, i.e. that no two rows have the same order_number and value:

WITH groups AS
  ( SELECT LISTAGG(value, '-') WITHIN GROUP (ORDER BY value) AS value_group
    FROM employees
    GROUP BY order_number
  )
SELECT value_group,
       COUNT(*) AS occurrences
FROM groups
GROUP BY value_group ;

You could also get the order numbers that belong to each group:

WITH groups AS
  ( SELECT LISTAGG(value, '-') WITHIN GROUP (ORDER BY value)
             AS value_group,
           order_number
    FROM employees
    GROUP BY order_number
  )
SELECT value_group,
       COUNT(*) AS occurrences,
       LISTAGG(order_number, ', ') WITHIN GROUP (ORDER BY order_number)
         AS order_numbers
FROM groups
GROUP BY value_group ;


how to clean huge Dbwarden QueryHistory table

This script will help: http://www.sqlservercentral.com/scripts/dbWarden+monitoring+maintenance/118935/

As per description, "This script creates/modifies all objects necessary for a cleanup of dbWarden History."

Note it is behind a login to SQLServerCentral.Com but if you are using dbWarden I expect you have a login to that site already.

Below is the script (just incase if SCC is down or the article gets removed):

-- Ref: http://www.sqlservercentral.com/scripts/dbWarden+monitoring+maintenance/118935/
IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc4HistoryTableCleanup]'))
BEGIN
    EXECUTE ('CREATE PROCEDURE [dbo].[Proc4HistoryTableCleanup] ( ' +
            '    @TableOwner    varchar(64),' +
            '    @TableName     varchar(512), ' +
            '    @DateFieldName varchar(50), ' +
            '    @retentionDays INT = 0,' +
            '    @Debug         TINYINT = 0' +
            ') ' +
            'AS ' +
            'BEGIN ' +
            '   SELECT ''Not implemented'' ' +
            'END')
END
GO

ALTER PROCEDURE [dbo].[Proc4HistoryTableCleanup] (
    @TableOwner     varchar(64),
    @TableName      varchar(64),
    @DateFieldName  varchar(64),
    @retentionDays  INT,
    @maxRowCnt      INT = 10000,
    @debug          TINYINT = 0
)
AS
/*
 ===================================================================================
  DESCRIPTION:
    This procedure takes in charge the clean-up for a given table based on a date field
    given as parameter and a retention expressed as a number of days.
    If the number of records to delete is more than a configurable limit, a dichotomic
    delete will be performed so that there is no matter on big open transaction without
    commit.

  ARGUMENTS :
    @TableOwner     name of the owner of the table to clean up
    @TableName      name of the table to clean up
    @DateFieldName  name of the date field to use for clean up
    @retentionDays  number of days to keep track
    @maxRowCnt      maximal number of rows to consider in an open transaction.
                    If the cleanup needs more than that number, the query will be cut
                    into pieces implying maximum @maxRowCnt
    @debug          if set to 1, this enables the debug mode

  REQUIREMENTS:

  ==================================================================================
  BUGS:

    BUGID       Fixed   Description
    ==========  =====   ==========================================================
    ----------------------------------------------------------------------------------
  ==================================================================================
  NOTES:
  AUTHORS:
       .   VBO     Vincent Bouquette   (vincent.bouquette@chu.ulg.ac.be)
       .   BBO     Bernard Bozert      (bernard.bozet@chu.ulg.ac.be)
       .   JEL     Jefferson Elias     (jelias@chu.ulg.ac.be)

  COMPANY: CHU Liege
  ==================================================================================
  Revision History

    Date        Nom         Description
    ==========  =====       ==========================================================
    18/11/2014  JEL         Version 0.1.0
    ----------------------------------------------------------------------------------
 ===================================================================================
*/

BEGIN

    --SET NOCOUNT ON;

    DECLARE @versionNb        varchar(16) = '0.1.0';
    DECLARE @tsql             nvarchar(max);            -- text to execute via dynamic SQL
    DECLARE @tmpRetentionDays INT;                      -- number of retention days to remove
    DECLARE @retentionInc     INT;                      -- increment for tmpRetentionDays

    BEGIN TRY
        --
        -- check parameter :
        --      retention days
        --
        if @retentionDays < 0
        BEGIN
            RAISERROR ('Parameter "retentionDays" is negative. Avorting execution', 10,1,@retentionDays);
        END

        if @retentionDays = 0
            return

        --
        -- check parameters
        --      table-related parameters are usable ?
        --
        IF(NOT EXISTS (
            SELECT 1
            FROM
                information_schema.COLUMNS
            where
                TABLE_CATALOG= DB_NAME(DB_id()) -- in current database
            AND TABLE_SCHEMA = @TableOwner
            AND TABLE_NAME   = @TableName
            and COLUMN_NAME  = @DateFieldName
          ))

        BEGIN
            RAISERROR('Parameters about the table are unusable in current database ', 10,1,@TableOwner, @TableName ,@DateFieldName )
        END

        if @debug = 1
        BEGIN
            PRINT '----------------------------------------------------'
            PRINT OBJECT_NAME(@@PROCID)
            PRINT '===================================================='
            PRINT 'Owner      = ' + @TableOwner
            PRINT 'TableName  = ' + @TableName
            PRINT 'Column     = ' + @DateFieldName
            PRINT 'Retention  = ' + CONVERT (VARCHAR ,@retentionDays)
            PRINT 'Max Row Nb = ' + CONVERT (VARCHAR ,@maxRowCnt)
            PRINT '----------------------------------------------------'
            PRINT CHAR(10)
        END

        --
        -- Iteration number evaluation
        --
        DECLARE @dateThresh DATETIME
        SET @dateThresh = GETDATE() - @retentionDays

        DECLARE @dateThreshStr   VARCHAR(100)
        SELECT @dateThreshStr = convert(varchar(100),@dateThresh,112)

        DECLARE @totalRecordNb   BIGINT
        DECLARE @MinDateFieldVal DATETIME

        SET @tsql =  N'SELECT '                             + CHAR(10) +
                    '    @totalRecNb = COUNT_BIG(*),'               + CHAR(10) +
                    '    @minDate    = MIN([' + @DateFieldName + '])'   + CHAR(10) +
                    'FROM'  + CHAR(10) +
                    '    [' + @TableOwner + '].[' + @TableName + ']' + CHAR(10) +
                    'WHERE' + CHAR(10) +
                    '    '  + @DateFieldName + ' < convert(DATETIME,@dateThresh,112)'

        execute sp_executesql @tsql, N'@totalRecNb BIGINT OUTPUT, @minDate DATETIME OUTPUT,@dateThresh VARCHAR(100)', @minDate = @MinDateFieldVal OUTPUT, @totalRecNb = @totalRecordNb OUTPUT, @dateThresh = @dateThreshStr

        if @debug = 1
        BEGIN
            PRINT 'Min Date in table : ' + CONVERT (VARCHAR ,@MinDateFieldVal,112)
            PRINT 'Number of records : ' + CONVERT (VARCHAR ,@totalRecordNb)
        END

        if(@totalRecordNb > @maxRowCnt)
        BEGIN

            -- divide by 2 the number of days between min date and the retention period
            DECLARE @nbOfDaysToAdd INT
            SELECT @nbOfDaysToAdd = (DATEDIFF(day,@MinDateFieldVal,@dateThresh) / 2)

            if @debug = 1
            BEGIN
                PRINT 'Too many records to take care at once !'
                PRINT 'Days to play with : ' + CONVERT (VARCHAR ,@nbOfDaysToAdd)
            END

            -- TODO : if days is not enough => play with hours !

            SET @tmpRetentionDays = @retentionDays+@nbOfDaysToAdd
            if @debug = 1
            BEGIN
                PRINT 'New retention : ' + convert(varchar,@tmpRetentionDays)
            END

            exec [dbo].[Proc4HistoryTableCleanup] @TableOwner=@TableOwner,@TableName=@TableName,@DateFieldName=@DateFieldName,@retentionDays=@tmpRetentionDays,@maxRowCnt=@maxRowCnt,@debug=@debug

            if @debug = 1
            BEGIN
                PRINT 'Restarting procedure with the previous retention :' + CONVERT (varchar,@retentionDays)
            END

            execute [dbo].[Proc4HistoryTableCleanup] @TableOwner=@TableOwner,@TableName=@TableName,@DateFieldName=@DateFieldName,@retentionDays=@retentionDays,@maxRowCnt=@maxRowCnt,@debug=@debug
        END
        ELSE
        BEGIN
            if @debug = 1
            BEGIN
                PRINT 'Deleting ' + CONVERT (varchar,@totalRecordNb) + ' records'
            END

            SET @tsql = 'DELETE'    + CHAR(10) +
                        'FROM'  + CHAR(10) +
                        '    [' + @TableOwner + '].[' + @TableName + ']' + CHAR(10) +
                        'WHERE' + CHAR(10) +
                        '    '  + @DateFieldName + ' < convert(DATETIME,@dateThresh,112)'

            execute sp_executesql @tsql, N'@dateThresh VARCHAR(100)', @dateThresh = @dateThreshStr

            if @debug = 1
            BEGIN
                SET @tsql =  N'SELECT '                             + CHAR(10) +
                        '    @totalRecNb = COUNT_BIG(*),'               + CHAR(10) +
                        '    @minDate    = MIN([' + @DateFieldName + '])'   + CHAR(10) +
                        'FROM'  + CHAR(10) +
                        '    [' + @TableOwner + '].[' + @TableName + ']' + CHAR(10) +
                        'WHERE' + CHAR(10) +
                        '    '  + @DateFieldName + ' < convert(DATETIME,@dateThresh,112)'

                execute sp_executesql @tsql, N'@totalRecNb BIGINT OUTPUT, @minDate DATETIME OUTPUT,@dateThresh VARCHAR(100)', @minDate = @MinDateFieldVal OUTPUT, @totalRecNb = @totalRecordNb OUTPUT, @dateThresh = @dateThreshStr

                PRINT 'Nb of records after delete : ' + CONVERT (varchar,@totalRecordNb)
                PRINT 'Minimum date after delete  : ' + CONVERT(varchar,@MinDateFieldVal,112)
            END

        END

    END TRY

    BEGIN CATCH
        PRINT 'ErrorNumber    : ' + CONVERT (VARCHAR , ERROR_NUMBER())
        PRINT 'ErrorSeverity  : ' + CONVERT (VARCHAR , ERROR_SEVERITY())
        PRINT 'ErrorState     : ' + CONVERT (VARCHAR , ERROR_STATE())
        PRINT 'ErrorProcedure : ' + CONVERT (VARCHAR , ERROR_PROCEDURE())
        PRINT 'ErrorLine      : ' + CONVERT (VARCHAR , ERROR_LINE())
        PRINT 'ErrorMessage   : ' + CONVERT (VARCHAR , ERROR_MESSAGE())
        IF @@TRANCOUNT > 0
            ROLLBACK TRAN --RollBack in case of Error

        RAISERROR ('Unable to proceed !', 10,1,@retentionDays);

    END CATCH
END

/**

Usage example :
==============
exec dbo.Proc4HistoryTableCleanup
    @TableOwner     = 'dbo',
    @TableName      = 'CPUStatsHistory',
    @DateFieldName  = 'DateStamp',
    @retentionDays  = 700,
    @debug          = 1

exec dbo.Proc4HistoryTableCleanup
    @TableOwner     = 'dbo',
    @TableName      = 'CPUStatsHistory',
    @DateFieldName  = 'DateStamp',
    @retentionDays  = 300,
    @debug          = 1
*/

alter table  dbo.DataDictionary_Tables
add retentionDays INT DEFAULT 90

update dbo.DataDictionary_Tables
set retentionDays = null
where TableName in (
    'AlertContacts','AlertSettings','DatabaseSettings','DataDictionary_Fields',
    'DataDictionary_Tables','SchemaChangeLog','ServerChangeLog'
)

update dbo.DataDictionary_Tables
set retentionDays = 90
where TableName in (
    'BlockingHistory','CPUStatsHistory','FileStatsHistory','HealthReport','JobStatsHistory',
    'MemoryUsageHistory','PerfStatsHistory','QueryHistory'
)

alter table  dbo.DataDictionary_Tables
add dateField VARCHAR(100)

update dbo.DataDictionary_Tables
set dateField = 'DateStamp'
where TableName in (
    'BlockingHistory','CPUStatsHistory','HealthReport',
    'MemoryUsageHistory','QueryHistory'
)
update dbo.DataDictionary_Tables
set dateField = 'FileStatsDateStamp'
where TableName = 'FileStatsHistory'

update dbo.DataDictionary_Tables
set dateField = 'JobStatsDateStamp'
where TableName = 'JobStatsHistory'

update dbo.DataDictionary_Tables
set dateField = 'StatDate'
where TableName = 'PerfStatsHistory'

create view dbo.CleanupSettings
AS
select
    SchemaName,
    TableName,
    dateField as DateFieldName,
    retentionDays
from dbo.DataDictionary_Tables
where retentionDays is not null;

IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dbWarden_HistoryCleanup]'))
BEGIN
    EXECUTE ('CREATE PROCEDURE [dbo].[dbWarden_HistoryCleanup] ( ' +
            '    @Debug         TINYINT = 0' +
            ') ' +
            'AS ' +
            'BEGIN ' +
            '   SELECT ''Not implemented'' ' +
            'END')
END
GO

ALTER PROCEDURE [dbo].dbWarden_HistoryCleanup (
    @debug          TINYINT = 0
)
AS
/*
 ===================================================================================
  DESCRIPTION:
    This procedure takes care of purging old data inside tables defined in the
    table or view dbo.CleanupSettings of the same database as the one in which this
    procedure has been created.

  ARGUMENTS :
    @debug          if set to 1, this enables the debug mode

  REQUIREMENTS:

  ==================================================================================
  BUGS:

    BUGID       Fixed   Description
    ==========  =====   ==========================================================
    ----------------------------------------------------------------------------------
  ==================================================================================
  NOTES:
  AUTHORS:
       .   VBO     Vincent Bouquette   (vincent.bouquette@chu.ulg.ac.be)
       .   BBO     Bernard Bozert      (bernard.bozet@chu.ulg.ac.be)
       .   JEL     Jefferson Elias     (jelias@chu.ulg.ac.be)

  COMPANY: CHU Liege
  ==================================================================================
  Revision History

    Date        Nom         Description
    ==========  =====       ==========================================================
    19/11/2014  JEL         Version 0.1.0
    ----------------------------------------------------------------------------------
 ===================================================================================
*/

BEGIN

    --SET NOCOUNT ON;

    DECLARE @versionNb        varchar(16) = '0.1.0';
    DECLARE @tsql             nvarchar(max);            -- text to execute via dynamic SQL

    DECLARE @CurrentOwner       VARCHAR(50)
    DECLARE @CurrentTable       VARCHAR(50)
    DECLARE @CurrentColumn      VARCHAR(50)
    DECLARE @CurrentRetention   INT

    DECLARE getTablesToPurge CURSOR FOR
        SELECT *
        FROM [dbo].[CleanupSettings]

    open getTablesToPurge
    FETCH NEXT
    FROM getTablesToPurge INTO @CurrentOwner,@CurrentTable,@CurrentColumn,@CurrentRetention

    WHILE @@FETCH_STATUS = 0
    BEGIN

        if @debug = 1
        BEGIN
            PRINT 'Current Owner  : '    + @CurrentOwner
            PRINT 'Current Table  : '    + @CurrentTable
            PRINT 'Current Column : '    + @CurrentColumn + '(datetime column used in where clause)'
            PRINT 'Current retention : ' + convert(varchar,@CurrentRetention)
        END

        exec dbo.Proc4HistoryTableCleanup
            @TableOwner     = @CurrentOwner,
            @TableName      = @CurrentTable,
            @DateFieldName  = @CurrentColumn,
            @retentionDays  = @CurrentRetention,
            @debug          = @debug

        -- carry on ...
        FETCH NEXT
        FROM getTablesToPurge INTO @CurrentOwner,@CurrentTable,@CurrentColumn,@CurrentRetention
    END
    CLOSE getTablesToPurge
    DEALLOCATE getTablesToPurge

END

/**

Usage example :
==============
exec dbo.[dbWarden_HistoryCleanup]
    @debug          = 1

exec dbo.[dbWarden_HistoryCleanup]
*/

--
-- ------------------------------
-- Sample job : everyday @8:15PM
-- ------------------------------
--
IF NOT EXISTS (SELECT * FROM msdb..sysjobs WHERE name = 'dbWarden_HistoryCleanup')
BEGIN
    BEGIN TRANSACTION
        DECLARE @ReturnCode INT
        SELECT @ReturnCode = 0

        DECLARE @jobId BINARY(16)
        EXEC
            @ReturnCode =  msdb..sp_add_job @job_name=N'dbWarden_HistoryCleanup',
            @enabled=1,
            @notify_level_eventlog=0,
            @notify_level_email=2,
            @notify_level_netsend=0,
            @notify_level_page=0,
            @delete_level=0,
            @description=N'No description available.',
            @category_name=N'Database Monitoring',
            @owner_login_name=N'sa',
            @notify_email_operator_name=N'SQL_DBA', @job_id = @jobId OUTPUT

        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

        EXEC
            @ReturnCode = msdb..sp_add_jobstep @job_id=@jobId, @step_name=N'run proc',
            @step_id=1,
            @cmdexec_success_code=0,
            @on_success_action=1,
            @on_success_step_id=0,
            @on_fail_action=2,
            @on_fail_step_id=0,
            @retry_attempts=0,
            @retry_interval=0,
            @os_run_priority=0, @subsystem=N'TSQL',
            @command=N'SET NOCOUNT ON
EXEC [DBA].dbo.dbWarden_HistoryCleanup',
            @database_name=N'master',
            @flags=0

        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

        EXEC @ReturnCode = msdb..sp_update_job @job_id = @jobId, @start_step_id = 1

        DECLARE @schedule_id int
        EXEC msdb.dbo.sp_add_jobschedule    @job_id=@jobId, @name=N'dbWarden_Schedule_HistoryCleanup',
                                            @enabled=1,
                                            @freq_type=4,
                                            @freq_interval=1,
                                            @freq_subday_type=1,
                                            @freq_subday_interval=0,
                                            @freq_relative_interval=0,
                                            @freq_recurrence_factor=1,
                                            @active_start_date=20141119,
                                            @active_end_date=99991231,
                                            @active_start_time=201500,
                                            @active_end_time=235959, @schedule_id = @schedule_id OUTPUT

        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

        EXEC @ReturnCode = msdb..sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

        COMMIT TRANSACTION
        GOTO EndSave
QuitWithRollback:
        IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
END
GO


SQL Backups - Full vs Differential vs Log Shipping

By "log shipping" I'm guessing you're meaning log backup?  Log shipping is a high availability strategy.

If your full backups are taking the better part of a day, yes I recommend that you switch things up a bit.  But that all depends on a lot of environment specific parameters.  For instance, do you have a big window on the weekend where you will be able to do a full backup without interfering with users? (If not, you may need to look into filegroup backups)

A full backup once a week, and diff backups daily (again, in a maintenance window) is pretty common.  As for your t-log backups, you need to determine the maximum amount of allowable data loss.  In other words, if you back up your transaction logs every 30 minutes, you will have a maximum of 30 minutes data loss if the tail of the log is corrupt and unrecoverable.  So determination will need to be made for transaction log backups.

(P.S. If you actually did mean Log Shipping then please clarify and I'll rework this answer.  But by judging by your question I am assuming you just mean log backups)


How to clear PostgreSQL temp files after crash

temporary files in the pgsql_tmp directory
You'd have to remove them by manually deleting the files in the pgsql_tmp subdirectory.
But be careful – manually messing with the data directory is always dangerous. I recommend only removing files whose modification timestamp is safely in the past, on Unix systems perhaps
find pgsql_tmp -mtime +1 -mindepth 1 -maxdepth 1 -exec rm {} \;

Other files
If you are not talking about temporary files at all, but about files that got left behind after a crash during an operation that rewrites a table, like VACUUM (FULL), CLUSTER or ALTER TABLE, the case is not so simple.
The proper, safe way to do this
Back up the database with pg_dump, drop it and restore it.
Then all those files should be gone.
The messy, dangerous way if you cannot afford the down time
Again, manually messing with the data directory should be left to an expert, and if you delete something that is vital for your database, you have lost your database. Don't do that unless you have a good physical backup of the database.
Let's assume you are talking about the default tablespace, the base subdirectory of the PostgreSQL data directory.
Change into the directory for your database, whose name you can find with
SELECT oid FROM pg_database
WHERE datname = 'my_database';

The following statement will create a list of all “filenodes” of objects in the default tablespace:
SELECT pg_relation_filenode(oid)
FROM pg_class
WHERE reltablespace = 0;

If the number 12345 occurs in the result, the following files are part of that table, index or other object: 12345, 12345.1, 12345.2, ..., 12345_vm, 12345_fsm (basically everything that starts with 12345).
The files that got orphaned by the crash are those that are left behind if you discount all the above files for all the objects in pg_class. They could in principle be deleted.


You have to use dynamic SQL to hide that column from the parser in 2005. All columns are checked to make sure they exist before the query runs.

DECLARE @sql nvarchar(MAX);

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

EXEC(@sql);


How can I get all of a few users' roles?

Explanation

The view that tells us about GRANTed roles is DBA_ROLE_PRIVS. (There's also USER_ROLE_PRIVS, but it only shows roles granted to the current user.) However, this view does not give us all the inherited roles. Following through the user's roles recursively requires a "hierarchical" query, or more simply known as a recursive query.

Logically, we want to start with roles granted to the user directly. This means that our START WITH clause should ensure that GRANTEE = 'TEMPUSER1'.
Then we want to look at rows where the GRANTEE is one of the roles granted to the user of interest so far. This means our CONNECT BY clause should ensure that PRIOR GRANTED_ROLE = GRANTEE.
To carry through the actual user through to the rows in our result set, we need to include CONNECT_BY_ROOT GRANTEE, which will give us the GRANTEE from the starting rows (and we started with our user of interest).
Last, it's possible for a user to inherit a role through two different roles, as exemplified with how TEMP_ROLE6 inherits TEMP_ROLE3. This means we'll need some kind of DISTINCT or GROUP BY.

The query

Assembling all that into a query gives us:

SELECT DISTINCT CONNECT_BY_ROOT GRANTEE AS GRANTED_USER, GRANTED_ROLE
FROM DBA_ROLE_PRIVS
START WITH GRANTEE IN ('TEMPUSER1', 'TEMPUSER2')
CONNECT BY GRANTEE = PRIOR GRANTED_ROLE
ORDER BY GRANTED_USER, GRANTED_ROLE
;

This gives the exact result desired.

Bonus info: Source of role

Here's an alternate query that includes some extra info about how the user got the role:

SELECT GRANTED_USER, GRANTED_ROLE, LISTAGG(DIRECT_GRANTEE, ',') WITHIN GROUP (ORDER BY DIRECT_GRANTEE) AS ROLE_SOURCE
FROM (
    SELECT DISTINCT CONNECT_BY_ROOT GRANTEE AS GRANTED_USER, GRANTED_ROLE, REPLACE(GRANTEE, CONNECT_BY_ROOT GRANTEE, 'Direct GRANT') AS DIRECT_GRANTEE
    FROM DBA_ROLE_PRIVS
    START WITH GRANTEE IN ('TEMPUSER1', 'TEMPUSER2')
    CONNECT BY GRANTEE = PRIOR GRANTED_ROLE
) USER_ROLES
GROUP BY GRANTED_USER, GRANTED_ROLE
ORDER BY GRANTED_USER, GRANTED_ROLE
;

Notice that the query shows 'Direct GRANT' when the role is granted to the user directly. This stands out a bit better than just showing the user name in the list.

Notice that we still need the DISTINCT in the subquery. This is necessary because TEMPUSER1 gets TEMP_ROLE4 twice, once from a direct GRANT and once from TEMP_ROLE6. Without the DISTINCT, TEMP_ROLE4 would show up twice as the source of TEMP_ROLE3.

The result looks like this:

GRANTED_USER  | GRANTED_ROLE  | ROLE_SOURCE
--------------+---------------+-----------------------------------
TEMPUSER1     | TEMP_ROLE1    | TEMP_ROLE2
TEMPUSER1     | TEMP_ROLE2    | Direct GRANT
TEMPUSER1     | TEMP_ROLE3    | TEMP_ROLE4,TEMP_ROLE5
TEMPUSER1     | TEMP_ROLE4    | Direct GRANT,TEMP_ROLE6
TEMPUSER1     | TEMP_ROLE5    | TEMP_ROLE6
TEMPUSER1     | TEMP_ROLE6    | Direct GRANT
TEMPUSER2     | TEMP_ROLE1    | TEMP_ROLE2
TEMPUSER2     | TEMP_ROLE2    | Direct GRANT

For all users

These queries can be easily adapter to show roles for all the users in the database. All that's needed is to change the START WITH clause to:

START WITH GRANTEE IN (SELECT USERNAME FROM DBA_USERS)

For roles

The queries work equally well if you want to view the roles granted to a particular role. You need only specify the role name as the starting GRANTEE. For example,

START WITH GRANTEE = 'TEMP_ROLE6'

Or for all roles:

START WITH GRANTEE IN (SELECT ROLE FROM DBA_ROLES)


Sample data:

CREATE TABLE dbo.Thing
(
    Product integer NOT NULL,
    TheDate date NOT NULL,
    TheWeight decimal(5, 1) NOT NULL
);

INSERT dbo.Thing
    (Product, TheDate, TheWeight)
VALUES
    (900000, CONVERT(date, '20160101', 112), 20.0),
    (900000, '20160303', 12.2),
    (900000, '20160706', 15.0),
    (900000, '20160707', 14.0),
    (900000, '20160806', 3.0 ),
    (900000, '20160808', 13.0 ),
    (800000, '20160602', 14.0),
    (800000, '20160603', 12.0),
    (800000, '20160604', 12.0),
    (800000, '20160605', 12.0),
    (800000, '20160606', 12.0);

Solution:

The general idea here is to use the extended window aggregate functions available in SQL Server 2012 and later.

The only wrinkle is that AVG does not return null over a window if it is smaller than the required four rows. To address that, we also calculate the number of rows found in the window using COUNT. A simple CASE expression can then be used to return a null if the window holds fewer than four rows:

SELECT
    T.Product,
    T.TheDate,
    T.TheWeight,
    [Average Weight] =
        CASE
            WHEN
                4 > COUNT_BIG(*) OVER (
                    PARTITION BY T.Product
                    ORDER BY T.Product, T.TheDate
                    ROWS BETWEEN 4 PRECEDING
                    AND 1 PRECEDING
                    )
                THEN NULL
            ELSE
                AVG(T.TheWeight) OVER (
                    PARTITION BY T.Product
                    ORDER BY T.Product, T.TheDate
                    ROWS BETWEEN 4 PRECEDING
                    AND 1 PRECEDING
                    )
        END
FROM dbo.Thing AS T
ORDER BY
    T.Product,
    T.TheDate;

Run the query on Stack Exchange Data Explorer

Output:

More information:

Window Functions in SQL Server

Related question:

Date range rolling sum using window functions


Select user activity over past months grouped by cohorts

With the help of Rick to look for pivot table I was able to create the final query as described in this post: https://stackoverflow.com/questions/7674786/mysql-pivot-table

Query for a 6 months analysis:

SELECT
    YEAR(registered), MONTH(registered), COUNT(*) as total,
    SUM(lastLogin >= DATE(registered) - INTERVAL DAYOFMONTH(registered) - 1 DAY + INTERVAL 1 MONTH) AS month1,
    SUM(lastLogin >= DATE(registered) - INTERVAL DAYOFMONTH(registered) - 1 DAY + INTERVAL 2 MONTH) AS month2,
    SUM(lastLogin >= DATE(registered) - INTERVAL DAYOFMONTH(registered) - 1 DAY + INTERVAL 3 MONTH) AS month3,
    SUM(lastLogin >= DATE(registered) - INTERVAL DAYOFMONTH(registered) - 1 DAY + INTERVAL 4 MONTH) AS month4,
    SUM(lastLogin >= DATE(registered) - INTERVAL DAYOFMONTH(registered) - 1 DAY + INTERVAL 5 MONTH) AS month5,
    SUM(lastLogin >= DATE(registered) - INTERVAL DAYOFMONTH(registered) - 1 DAY + INTERVAL 6 MONTH) AS month6
FROM
    user
WHERE
    registered >= "2015-01-01"
GROUP BY YEAR(registered) , MONTH(registered)

The part DATE_SUB(DATE(registered), INTERVAL DAYOFMONTH(registered) - 1 DAY) will return the first of a month to make sure the analysis is based on calendar months. I am not sure if it could be simplified. I was looking at STR_TO_DATE(LEFT(registered, 7), "%y-%m") but I was reading working on Strings is slower and should be avoided. If someone knows a better / cleaner way of doing that, please comment and I will include it in the answer.

Here is some exemplary output of the query:

  year, month, total , month1, month2, month3, month4, month5, month6
'2015',  '1', '14776', '4302', '3225', '2827', '2547', '2330', '2163'
'2015',  '2', '12162', '3859', '2866', '2465', '2259', '2046', '1890'
'2015',  '3', '10770', '3831', '2841', '2507', '2288', '2135', '1987'
'2015',  '4', '15685', '3731', '2641', '2273', '2045', '1872', '1730'
'2015',  '5', '18130', '3686', '2403', '1993', '1744', '1528', '1363'


"lock request time out period exceeded" Error When Trying to See DB Hierarchies

It was being caused by a perpetual rollback of a transaction.  Had to eventually restart my server cluster


Try this one:

create table #test (
    date date,
    overdrawn tinyint
)

insert into #test values
    ('2018-09-20', 1),
    ('2018-09-21', 1),
    ('2018-09-24', 0),
    ('2018-09-25', 1),
    ('2018-09-26', 1)

select sum(
        case datepart(dw, date)
            when 6 then overdrawn * 3 --Friday
            else overdrawn
        end
    )
from #test

This solution is based off the fact that the overdrawn field can either be 0 or 1 only. You'll have to include additional conditions if that's not the case.


Powershell is the way. And it make it easier, just install dbatools on one of your admin servers and use get-dbadiskspace

  Get-DbaDiskSpace -ComputerName server1, server2, server3 -Unit GB

if you have list of servers then you can use

$servers = get-content "D:\serverlist.txt"
foreach ($server in $servers) {
  Get-DbaDiskSpace -ComputerName $server -Unit GB
}

You can even dump the above info using invoke-sqlcmd2 into a central db for reporting .


Sybase Central is no longer provided in the new ASE versions. With ASE16 SP02, you can use ASE cockpit instead.

http://help.sap.com/ase1602#section7


Server audit with more than 1.000 entries

Have you tried reading your audit using sys.fn_get_audit_file?

-- query all audit files in the D:\audit directory
SELECT *
 FROM sys.fn_get_audit_file('D:\audit\*.sqlaudit', default, default)

The CREATE SERVER audit TSQL command you used did not specify

MAXSIZE = { max_size }
The default value is UNLIMITED.

MAX_ROLLOVER_FILES ={ integer | UNLIMITED}
The default value is UNLIMITED.

MAX_FILES =integer
Specifies the maximum number of audit files that can be created. Does not rollover to the first file when the limit is reached. When the MAX_FILES limit is reached, any action that causes additional audit events to be generated will fail with an error.

Not sure what the default is for MAX_FILES but its probably the FS limit which is somewhere around two billion.

So no there should not be any limit on the size of the audit files generated or the number of rows stored within them.


Will a mysqldump ever break my database?

The mysqldump utility will not break your database.

Read the source code for it and/or turn on the general query log while it's running and you'll see that what all it does it look.

But...

If you are using MyISAM, or if you fail to use the --single-transaction option, or you issue a FLUSH TABLES; request from another connection while the backup is reading a large table, you stand a higher chance of stalling your web site because there are things that can happen to cause queries to block until the specific table or the entire backup is complete.

And, if you do have serious corruption in your data, mysqldump stands a good chance of running into it, since it reads every row from every table by default, which means the server will be trying to read through the corruption and could crash.  This is essentially true of any backup, of course.

But directly and ordinarily and by most reasonable definitions, mysqldump cannot harm your database contents.