Adding nullable column to table costs more than 10 minutes I have problems to add a new column on a table.
I tried to run it a couple of times, but after more than 10 minutes running, I decided to cancel the query because of lock time.

ALTER TABLE mytable ADD mycolumn VARCHAR(50);

Useful information:

PostgreSQL version: 9.1
Number of rows: ~ 250K
Number of columns: 38
Number of nullable columns: 32
Number of constraints: 5 (1 PK, 3 FK, 1 UNIQUE)
Number of indexes: 1
OS type: Debian Squeeze 64

I found interesting information about the way PostgreSQL manages nullable columns (via HeapTupleHeader).

My first guess is that because this table already has 32 nullable columns with 8-bits MAXALIGN, HeapTupleHeader is 4 Bytes length (not verified, and I don't know how to do so).

So adding a new nullable column could need an update of HeapTupleHeader on every row to add a new 8-bits MAXALIGN, which could cause performance issues.

So I tried to alter one of the nullable columns (which is not really nullable in reality) in order to decrease to 31 the number of nullable columns, to check if my guess could be true.

ALTER TABLE mytable ALTER myothercolumn SET NOT NULL;

Unfortunately, this alter also takes very long time, more than 5 minutes, so I also aborted it.

Do you have an idea of what could cause this performance cost?

There are a couple of misunderstandings here:

The null bitmap is not part of the heap tuple header. Per documentation:

  There is a fixed-size header (occupying 23 bytes on most machines),
  followed by an optional null bitmap ...

Your 32 nullable columns are unsuspicious for two reasons:

The null bitmap is added per row, and only if there is at least one actual NULL value in the row. Nullable columns have no direct impact, only actual NULL values do. If the null bitmap is allocated, it's always allocated completely (all or nothing). The actual size of the null bitmap is 1 bit per column, rounded up to the next byte. Per current souce code:

#define BITMAPLEN(NATTS) (((int)(NATTS) + 7) / 8)

The null bitmap is allocated after the heap tuple header and followed by an optional OID and then row data. The start of an OID or row data is indicated by t_hoff in the header. Per comment source code:

  Note that t_hoff must be a multiple of MAXALIGN.

There is one free byte after the heap tuple header, which occupies 23 bytes. So the null bitmap for rows up to 8 columns effectively comes at no additional cost. With the 9th column in the table, t_hoff is advanced another MAXALIGN (typically 8) bytes to provide for another 64 columns. So the next border would be at 72 columns.

To display control information of a PostgreSQL database cluster (incl. MAXALIGN), example for a typical installation of Postgres 9.3 on a Debian machine:

    sudo /usr/lib/postgresql/9.3/bin/pg_controldata /var/lib/postgresql/9.3/main

I updated instructions in the related answer you quoted.

All that aside, even if your ALTER TABLE statement triggers a whole table rewrite (which it probably does, changing a data type), 250K are really not that much and would be a matter of seconds on any halfway decent machine (unless rows are unusually big). 10 minutes or more indicate a completely different problem. Your statement is waiting to get a lock on the table, most likely.

The growing number of entries in pg_stat_activity means more open transactions - indicates concurrent access on the table (most likely) that has to wait for the operation to finish.

A few shots in the dark

Check for possible table bloat, try a gentle VACUUM mytable or a more aggressive VACUUM FULL mytable - which might encounter the same concurrency issues, since this form also acquires an exclusive lock. You could try pg_repack instead ...

I would start by inspecting possible issues with indexes, triggers, foreign key or other constraints, especially those involving the column. Especially a corrupted index might be involved? Try REINDEX TABLE mytable; or DROP all of them and re-add them after ALTER TABLE in the same transaction.

Try running the command in the night or whenever there is not much load.

A brute-force method would be to stop access to the server, then try again:

Force drop db while others may be connected in postgresql

Without being able to pin it down, upgrading to the current version or the upcoming 9.4 in particular might help. There have been several improvements for big tables and for locking details. But if there is something broken in your DB, you should probably figure that out first.


Oracle 11g SYSDBA login I installed Oracle 11g on my win xp machine. Now I want to connect to the database.  I was told that to connect to the database from command prompt I should use
SQLPLUS "/ AS SYSDBA". But I want to connect as sysdba using a gui tool. So what is the equivalent USERNAME/PASSWORD which I can use to connect to the database (I think sysdba is just a role and has nothing to do with username or password. The '/' defaults to some value)

Also I tried to grant SYSDBA TO a user(say TESTUSER/TESTPASSWORD) using GRANT command. But at the next login the access seems to get reverted somehow.

Need your suggestion to resolve this issue .

Thanks in advance

It depends on the GUI but they all should have a way of selecting the SYSDBA privilege. Toad has it appear as a drop-down box called 'Connect as:' as does Oracle Enterprise Manager. For SQL Developer you use the 'Role' box when creating a new connection.

As far as / as sysdba is concerned it just means that operating system authentication is being used so no password is needed. You can only use it if you are logged in as the oracle user or a member of the dba group (or for Windows, I believe as an administrator).

When you grant a user the SYSDBA privilege it only means they have the option to log in with the SYSDBA privilege and not that they will have dba privileges when they log in normally. The same is true for the SYS user: you can log in as SYS without the SYSDBA privilege and so lack the ability to shutdown the database, for example.


It's possible optimize query with only index scan? I have the following tables:

A: (id_a, field1, field2) with 32M rows
B: (id_b, id_a, field3, field4) with 3M rows

All columns are indexed, except b.field4. A.id_a and b.id_b are PK.

And the following query:

SELECT B.field4
FROM A
    INNER JOIN B using (id_a)
WHERE (A.field1 = 123 OR A.field2 = 123) AND B.field3 = 1234
LIMIT 1;

I have 48k rows on table A that match with the condition A.field1 = 123 OR A.field2 = 123) and 1.8k on table B that match with B.field3 = 1234P.

The number of rows returned by the query without the limit is 74.

Explain shows that 1.8k rows will be analyzed.

The question is: can this query be optimized? I was assigned to a task to improve it.
The only thing I think I can do is add field1 to table B and remove the OR field2 = 123 part.

B:  (field3, id_a, field4)
A:  (id_a)

The B index will be optimal and "covering".  So, even if it touches 1.8K 'rows', they will be compactly sitting in the index.

Separate indexes on individual columns is not the same as a composite index.


Separating the affiliates into their own tables would usually be a bad idea. It will not save you anything and will make reporting across populations should you need to later.

I would go further than ypercube suggests: why not record each sale individually?

Clicks:    Affiliate            ClickTime
           (FK to aff. table)   (timestamp)

Salses:    Affiliate            SaleTime      SaleAmount
           (FK to aff. table)   (timestamp)   (fixed precision decimal)

Yes this will take more space, but far from a lot - if this much space is a problem then you are working with a extrememely limited hosting solution. With daily recording as suggested by ypercube each row is going to be 28 bytes (assuming 4 for date, everything else in your table being 4-byte ints, and the affiliate ID being an int also) plus index load. Assuming you need enough indexes to more-or-less double the storage requirements (for tables that do not have many columns this is not unusual, though almost certainly overkill for these tables), 30,000 rows per month is still only ~1.6Mb/month.

The above structure gives you much more detail should you need it for reports later. If you specifically need to report on the basis of days, you can either split the date and the time into separate fields or denorlamise a tad and keep the full timestamp plus a date-only field. You can then generate a daily report for each affiliate just by grouping by the date, affiliate, and data fields and using the count and sum aggregates appropriately.

With proper FKs and other indexes the extra detail should not impose performance issues. Databases are designed for handling large amounts of data (and this is far from large) in tables - millions of rows should not be a problem in that respect, never mind tens of thousands.


Intermittent Disconnection to DB on SAN. Event ID 17053 and 9001 I am getting some strange issues with our MS SQL 2008 database where intermittently we lose connection to the db for 10+ seconds or so.  The db is stored on a SAN over fiber.  The latest event log error I get is "LogWriter: Operating System error 1 (incorrect function) encountered with an event ID of 17053.  Following that error is event ID 9001 about log for database is not available.  I would then see a message stating recovery is completed for database in 12 seconds.  Not sure how to approach this and any help or guidance would be much appreciated.  Running MS SQL 2008 on Windows 2008 SP1.

You need to either have a SAN admin look at the system of bring a consultant in to look at it.  (Yes I'm a SAN consultant, no that's not why I'm saying this.)


Why does sp_replmonitorhelpsubscription not work without parameters? I am just starting to play around with all the cool replication tools to Programmatically Monitor Replication
One of the early finds is sp_replmonitorhelpsubscription
When I run it as
 sp_replmonitorhelpsubscription

I get

Msg 20587, Level 16, State 1, Procedure sp_replmonitorhelpsubscription, Line 77 [Batch Start Line 16]
Invalid '@publication_type' value for stored procedure 'sp_replmonitorhelpsubscription'.

According to MS documents it should be valid with a null default

NULL (default)

If I run it with the Parameter it works fine.
sp_replmonitorhelpsubscription  @publication_type = '0'

I using it with transactional replication, on a single server (Reporting Copy) I have tried and I get the same results on SQL 2017 & 2016.  I am running it against the distribution database
Not sure If I am doing something stupid, if the MS docs are wrong, or what.
Why does sp_replmonitorhelpsubscription not work without parameters?

Checking the code reveals that :

-- validate @publication_type
    --
    if (@publisher_db is NULL or @publication is NULL)
        and @publication_type is NULL
    begin
        raiserror(20587, 16, -1, '@publication_type', 'sp_replmonitorhelpsubscription')
        return (1)
    end

This means that if @publication_type is NULL then it will throw an error which is expected.

Even on 2014, it is throwing same error.

Also, you just need to run with

sp_replmonitorhelpsubscription  @publication_type = 0 -- for Transactional publication. since the data type is INT.


Get profiles values where message.sender_user_id = profile.user_id I have this tables:

default_messages

CREATE TABLE IF NOT EXISTS `default_messages` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `subject` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
 `message` text COLLATE utf8_unicode_ci NOT NULL,
 `sender_user_id` int(11) NOT NULL,
 `reply_to_message_id` int(11) NOT NULL,
 `thread_root_message_id` int(11) NOT NULL,
 `date` datetime NOT NULL,
 `deleted` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

default_profiles:

CREATE TABLE IF NOT EXISTS `default_profiles` (
 `id` int(9) NOT NULL AUTO_INCREMENT,
 `created` datetime DEFAULT NULL,
 `updated` datetime DEFAULT NULL,
 `created_by` int(11) DEFAULT NULL,
 `ordering_count` int(11) DEFAULT NULL,
 `user_id` int(11) unsigned NOT NULL,
 `display_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `first_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `last_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `company` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
 `lang` varchar(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
 `bio` text COLLATE utf8_unicode_ci,
 `dob` int(11) DEFAULT NULL,
 `gender` set('m','f','') COLLATE utf8_unicode_ci DEFAULT NULL,
 `phone` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `mobile` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `address_line1` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `address_line2` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `address_line3` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `postcode` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `website` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `updated_on` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

and

default_recipient:

CREATE TABLE IF NOT EXISTS `default_recipient` (
 `message_id` int(11) NOT NULL,
 `user_id` int(11) NOT NULL,
 `read` int(11) NOT NULL DEFAULT '0',
 `recipient_read_date` datetime DEFAULT NULL,
 `deleted` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Then I have this query for get some messages:

SELECT DISTINCT p.display_name, p.first_name, p.last_name, rcp.*, msg.* FROM default_messages msg LEFT JOIN default_recipient rcp ON (msg.id = rcp.message_id) LEFT JOIN default_profiles p ON (p.user_id = msg.sender_user_id) WHERE rcp.user_id = 1 AND rcp.deleted = 0 ORDER BY msg.date DESC

I need to get the values from default_profile where default_profile.user_id = default_messages.sender_user_id but for some reason I get the wrong values. It's something wrong in my query?

Here are some values for testing purpose and also the complete script for table creation: http://pastebin.com/T3mXfi0k

First, what is "wrong" with the data you are returning?

My guess is that you intend to be using an [INNER] JOIN which will only return rows where there is a matching record, not a LEFT JOIN which will return all the rows in the left table regardless of whether there are matches in the right table.

For example:

mysql> select distinct p.display_name, p.first_name, p.last_name, m.sender_user_id
    -> FROM default_profiles p
    -> JOIN default_messages m ON m.sender_user_id = p.user_id;
+--------------------+------------+------------+----------------+
| display_name       | first_name | last_name  | sender_user_id |
+--------------------+------------+------------+----------------+
| Reynier Perez Mira | Reynier    | Perez Mira |              1 |
| User1              | User1      | Lastname1  |              2 |
+--------------------+------------+------------+----------------+
2 rows in set (0.00 sec)

vs

mysql> select distinct p.display_name, p.first_name, p.last_name, m.sender_user_id
    -> FROM default_profiles p
    -> LEFT JOIN default_messages m ON m.sender_user_id = p.user_id;
+--------------------+------------+---------------+----------------+
| display_name       | first_name | last_name     | sender_user_id |
+--------------------+------------+---------------+----------------+
| Reynier Perez Mira | Reynier    | Perez Mira    |              1 |
| User1              | User1      | Lastname1     |              2 |
| Tomas Losis        | Tomas      | Losis         |           NULL |
| Roberto            | Roberto    | Scharffenorth |           NULL |
| webmaster 3wd      | webmaster  | 3wd           |           NULL |
| polar              | polar      | pilsen        |           NULL |
| rubi               | rubi       | acosta        |           NULL |
+--------------------+------------+---------------+----------------+
7 rows in set (0.00 sec)

I would rewrite your provided query like so:

SELECT DISTINCT p.display_name, p.first_name, p.last_name, rcp.*, msg.*
FROM default_profiles p
JOIN default_messages msg ON p.user_id = msg.sender_user_id
JOIN default_recipient rcp ON msg.id = rcp.message_id
WHERE rcp.user_id = 1
  AND rcp.deleted = 0
ORDER BY msg.date DESC;

That being said, in your sample data, both our queries return the same results.  Perhaps there is different data in the full set.


Can't create a Database on Oracle 11g I'm new on Oracle and I'm trying to create a new database. I followed tutorials online but the problem seems to be in Windows and not in Oracle.
I'm using the Oracle assistant to build my Database but when I arrive at the last stage and the assistant begin to configure all, there is an error (image attached).

From the message, seems that my user haven't the permission to read, write and execute anything from documents but it is not true. I gave full permission to my user and so I don't know why it stuck again here.

My system configuration is:
 - Oracle 11g ver. 2
 - Windows 7 Pro (64bit)

In a command line, run:

attrib C:\Users\Pierpaolo\Documents

If you see a capital R in the output, then the read-only attribute is set for that folder (which is the default setting for the Documents folder).

Either remove the read-only attribute from the folder (I do not know what this will mess up in Windows), or specify a different folder for diagnostic dest.
For the latter, on this screen, go to All Initialization Parameters..., and set the value of diagnostic_dest to a directory that has no read-only attribute set.


My database has been deleted suddenly on the server how to recover it? I'm running an application on windows server that connects to a SQL Server database.

Today, when I opened SQL Server Management Studio, I was surprised the database is not in the list of the databases!

I don't know what's the reason. I searched in the server files but I can't find the database and also in the recycle bin.

I put my database in C:\db\myWeb.mdf and suddenly it's been removed!

I used stealer phoenix to find the database files deleted on disk but unfortunately I didn't find the db then I tried to add new database with the same name of the deleted db but SQL Server Management Studio said that the database MyWeb already exists! That means it's somewhere on the server but I couldn't find it...

Can anyone tell me how to recover the database?

Before restoring from the latest full database backup, you can try Paul Randal's advise - set the database to the EMERGENCY mode and use a login that is a member of the sysadmin SQL Server role to access it. Keep in mind that you will be able only to read the data, as the database will be in the read-only mode.

Search Engine Q&A #4: Using EMERGENCY mode to access a RECOVERY PENDING or SUSPECT database


Target server is not communicating(polling) with master SQL Server We set up multi-server administration to create a job on the master which are executed on target servers, and we had this issue because the target server was shut down after a virus attack and we set up a new SQL Server with the same name and restore all data including the master database on the new server, all the job is functioning as it used to be but now the issue happened when I am trying to update them, I can update the job at master SQL Server to change the schedule however the target server is not updating since it stopped polling after the original old server shutdown because of the virus attack,
Currently, the new SQL Server Agent is not showing (tsx)
I tried to force poll but it's not working.
my trial to add it as target server is not working with a duplicate error too.
Also, my trial to update the job at the target server is not possible since it's not active to even update
Please advise how can I reconnect this master and target server with out losing all jobs.

Does the target server know that it is a target server? In SSMS, does Agent say that this is a TSX?
I have a feeling that the fact that you are in an MSX environment is stored in the registry instead of a system database.
I just looked on an TSX server, an SQL Server instance named "B". The master server is an instance named "A" (on the same machine, this is a test setup). I have a key named:
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.B\SQLServerAgent
In above key, I have an entry named MSXServerName with the name of the master server.
Above is what I would fix first.
After fixing that, I would check the SQLAgent.out file on both MSX and TSX. And possibly resync the TSX server using sp_resync_targetserver. I blogged about this here:
http://sqlblog.karaszi.com/resynchronizing-a-target-server-msx-tsx/


Take average of multiple nullable columns I have an example table (year) as below in PostgreSQL 9.5:

Name | 2010 | 2011 | 2012
-------------------------
A    |   10 |      |   40
B    |   10 |   20 |   30

Now, if I write a simple query as shown below to take average for columns (2010, 2011, 2012) I will get the correct result for B but result for A will be NULL because of the NULL in the 2011 column:

select (2010+2011+2012)/3 as avg from year

Is there any way to write a query so that I can take average of only non-NULLs in a row?

The only correct and scalable solution to that problem is to normalize your model.

However, you can normalize the data "on the fly" and then use standard SQL aggregation on the result.

select name, avg(t.val)
from the_table
  cross join unnest (array["2010","2011","2012"]) with ordinality as t(val)
group by name;

Online example: https://rextester.com/TGTC30399

But I would strongly recommend to fix your data model by properly normalizing it.


Create stored procedure that contains newlines with a oneliner sql statement I want to put code in a single line that will create a stored procedure that contains newlines.

Is that possible?
Do I need to use sp_executesql?
How do I escape newlines in a sql statement?
How do I escape newlines in a string?

Yes, you can do something like this with dynamic SQL:

DECLARE @SQL NVARCHAR(MAX) = N'';
DECLARE @NewLine NCHAR(1) = NCHAR(10);

SET @SQL = @SQL + N'SELECT * ' + @NewLine + N'FROM sys.databases AS d ' + @NewLine + N'WHERE d.database_id > 4;' + @NewLine;

PRINT @SQL;
EXEC sys.sp_executesql @SQL;


Tool to view intermediate query results I am using SQL Server 2008 Express edition and I've worked two days on a problem where seems to be a cross join in running a query.

I would want to know the set returned per join operation visually.

Is there any tool that can give you intermediate query results? I couldn't find one on Google.

EDIT

I solved this problem with the oldest trick in the book, manually running the query before joining to see where exactly the problem of cross join was happening. And found out that there was a mapping DB that sent 3 results for a join instead of just one (because it needed two conditions for a proper inner join, instead of one).

There are none that I know of that will show you the exact results - maintaining hooks so such tools could spy on what is going on would probably be inefficient in production (no doubt MS can do it in their development builds). While intermediate results aer sometimes dumped to temporary tables for further processing, they are often never materialised as a whole unit instead being streamed between parts of the query engine as they are produced.

If you run the query in SQL Server Management Studio, you can request that the query plan used is output. While that will not give you the entire intermediate results it will show you what comparisons are being performed and approximately how many rows are effectively output at each step - if you have a Cartesian product between a pair of large tables this should hopefully make it clear where in the process this happens.

For more specific help you'll need to add more detail to your question (please add it to the question, not as comments here, then there is better visibility of the new information for people who visit this page later):

What makes you think there is a cross join happening? Are you getting many more rows than you expect then whittling them down with a DISTINCT? If it is just that the query is running very slowly and spinning the CPU then it may instead be a index use problem or a correlated subquery that is resulting in a scan per output row (a situation that can actually be worse than a cross join performance wise)
Could you provide a copy of the query and relevant table structures?
What size of data are we talking about?


how to find a connection string value for a session in sql server? I know there was a similar question before at SO as shown here
However, that answer really does not address my question.
So here is my scenario, and I will demo it with PowerShell.

The following code will create two sessions on my local sql2016 instance

#The only difference in the two connection strings is [pooling] property, the $connstr_1 has pooling=true, while the $connstr_2 has pooling=false.

$connstr_1 = "data source=localhost\sql2016; initial catalog=master; trusted_connection=true; app=ConnPoolTest; pooling=true";
$SqlConnection1 = New-Object System.Data.SqlClient.SqlConnection($connstr_1)

$connstr_2 = "data source=localhost\sql2016; initial catalog=master; trusted_connection=true; app=ConnPoolTest; pooling=false";
$SqlConnection2 = New-Object System.Data.SqlClient.SqlConnection($connstr_2)

$SqlConnection1.Open()

$SqlConnection2.Open();

My question is on sql server side, how can I know which session's connection string has pooling property set to true and which set to false?

My question is on sql server side, how can I know which session's connection string has pooling property set to true and which set to false?

You generally won't and can't. The connection string isn't sent to SQL Server, it's a directive for the driver on what information and options it needs in order connect and how it should act in certain situations. The connections string itself, however, is not sent to SQL Server and it's generally not possible to reconstruct it in its entirety just form the information surfaced by SQL Server.

In your example, the pooling options tells the driver whether or not to use connection pooling. SQL Server doesn't care about this, nor does it know about this as it's entirely a driver/application construct.


best way to implement user with trial subscription We have an old database implementation, in PostgreSQL, where users are dependent to an institution subscription, this is based on the renewal_date field of the institution table. Once this has expired users belong to an institution will no longer able to access our Saas webapp. See the image below:

Now, we wanted to implement a registration functionality where users are able to register whether their institution has a subscription to our tool. For this, they will be given a 30-day free trial to our tool. So now what I did is to separate the fields that I think can be placed to their own table. See the image below:

After doing some clean up, I realise it would be redundant to implement an expiry_date field in the Account table to store the dates of each user and use this to know when their subscription will end knowing there is an existing renewal_date that we are using to check that. So this is a challenge to me because not every user that will signup has their institution already subscribe to our Saas webapp. Any great suggestion over this problem will be appreciated.

In the first place, renewal_date is more an attribute of Account than Institution; so it's in the wrong table. But even further, I would recommend a Subscription entity that would define a many-to-many relationship between Account and Tools.

(Oh. You don't have Tools. Well, maybe you should. You seem to have only one tool. Can you guarantee that will forever be the case?)

Then renewal_date would properly be an attribute of the relationship between Account and the tool they have access to.


How does SQL Server handle logins when the user is a part of multiple groups with various privileges? I'm trying to wrap my head around how SQL Server decides to grant certain rights based on a users login privileges.  If a user is part of a group "READ ONLY" which has access to DB1, but they are also a part of a group "SysAdmin" which has full db owner rights to the same DB1, which rights are they given when they log in?  On a similar note. What if they are part of a group but also have a standalone account with other middle of the road privileges?

For normal roles, permissions for all role memberships are cumulative and DENY takes precedence over GRANT.  However, sysadmin is a special case.  SQL Server permissions are not checked for sysadmin role members so members of that role have full permissions to all databases on the instance.


How to use a variable date when running a db2 command in a shell script? I need to use a date variable in a select, like bellow, but I'm getting this error: Expected tokens may include:  "".  SQLSTATE=42601

How can use these variables in a shell script?

day0_T1=`TZ=CST+12 date +"%Y-%m-%d %T"`
day0_T0=`TZ=CST+24 date +"%Y-%m-%d %T"`
dayn_Tn=`TZ=CST+n date +"%Y-%m-%d %T"`

db2 connect to BD
db2 "/path/result.tmp  OF DEL MODIFIED BY COLDEL; decplusblank striplzeros
select count(*),user from table1
and close_datetime between cast(date('$day0_T0') as timestamp) AND cast(date('$day0_T1') as timestamp)
and task = 'task1'
group by user
with ur"
db2 terminate

Shell quotes typically delimit arguments or escape other special shell characters from shell interpretation. You can contain (and therefore escape) different kinds of shell-quotes within other kinds based on various rules:

"''''" - a soft-quoted string can contain any number of hard-quotes.
"\"" - a \ backslash can escape a "soft-quote within a "soft-quoted string.
In this context a \\backslash also escapes itself, the \$expansion token, and \newlines as noted below, but is otherwise treated literally.
"${expand} and then some" - a soft-quoted string can contain an interpreted shell $expansion.
'"\' - a 'hard-quoted string can contain any character other than a 'hard-quote.
\ - an unquoted backslash will escape any following character for literal interpretation - even another backslash - excepting a \newline.
In a \\newline case both the \ backslash and the \newline are completely removed from the resulting interpreted command.
${parameter+expand "$parameter"} - quotes resulting from a shell expansion almost never serve as delimiter markers excepting a few special cases. I won't venture to describe these further here.

Test:

root@onare:/home/onare# echo $day0_T1
2015-07-16 14:10:44
root@onare:/home/onare# db2="something '${day0_T1}'"
root@onare:/home/onare# echo $db2
something '2015-07-16 14:10:44'
root@onare:/home/onare#


Which are the downsides of not deleting the CDC records in change tables? If I do something like this:

USE AdventureWorks2012;
GO
EXECUTE sys.sp_cdc_change_job
    @job_type = N'cleanup',
    @retention = 52494800; -- (100 years)
GO

The records in the change tables will never (until year 2119) be deleted.

Also, I'm applying a script like this to move data from an old instance to a new one every time a new column is added to the tracked table, and I know that if there are many records, it can take a little bit of time to copy them to the new change table.

So, I'm planning to keep the change history forever on these change tables, and never purge the tables using the cleanup job. But the documentation says:

  Data that is deposited in change tables will grow unmanageably if you
  do not periodically and systematically prune the data

If CDC had been in plance since 2007 in my DB, the biggest common change table will contain ~20K rows in 12 years, the source table is replicated to another server, so, having said that:

What does 'grow unmanageably' means for me?
Will it have vey negative impact on the performance or storage in the DB? Maybe an impact on Replication performance?
Am I just fine doing CDC with no cleanup job at all? or
Is it a better alternative to copy the data to a reporting DB using a SSIS package or a job runing a stored procedure?

If so, is there a simple way to handle the creation of new columns in the destination table based on new columns added in the change tables?

Does the answer change if instead of 20K rows in one change table there are 1 million?

What does 'grow unmanageable' means for me?

This all depends on your environment. If you had a DB that was subject to a lot of transactions (one with a lot of CRUD operations), then this table would grow much faster and larger than one with minimal changes making it harder to manage.

  Will it have very negative impact on the performance or storage in the
  DB? Maybe an impact on Replication performance?

The larger your change tables grow, the longer your ETL / change data query functions that are used to store the data on another instance would take to run (they would be slower). You haven't stated of you are storing these records elsewhere, or just using the change tables as your end point for auditing, but it seems like the latter.

  Am I just fine doing CDC with no cleanup job at all?

If your change table isn't expected to grow > 20K rows ever you probably wouldn't see a performance burden.

  Is it a better alternative to copy the data to a reporting DB using a
  SSIS package or a job running a stored procedure? If so, is there a
  simple way to handle the creation of new columns in the destination
  table based on new columns added in the change tables?

Based on this question, and the fact that you state you already have a script that is copying data to another server, it seems like you are trying to capture changes multiple ways. One way you may want to explore is System-Versioned Temporal Tables. This may be the easiest route for you and could eliminate CDC and your SSIS / custom script especially since your DML events seem to be low. These tables will also change when you conduct DDL changes to your source table.

  Does the answer change if instead of 20K rows in one change table
  there are 1 million?

Since that would be 50X the number of rows, sure, you can expect different results regarding performance of any queries against those change tables.


Loading a FACT table per day I have a database with 13 billion rows, per day I have around 20-30 mio rows. On top of this I have one cube, one of its dimensions is DateTime that goes down to milliseconds. To load the fact table I use the following Query as T-SQL Task within SSIS:

INSERT INTO [FACT].[DataMine]
SELECT MONTH(RDM.[DATE]) as 'PartitionID',
       DateTime_Key,
       Price,
       Amount,
FROM   [RAW].[DataMine] RDM
INNER JOIN [DIM].[DateTime] DDT
ON RDM.DateTime_Key = DDT.DateTime_Key
WHERE DM.Date BETWEEN '2011-11-28' AND '2011-11-28' AND
      DateTime_Key NOT IN (SELECT DISTINCT DateTime_Key
                           FROM [FACT].[DataMine] DM
                           INNER JOIN [DIM].[DateTime] DT
                           DM.DateTime_Key = DT.DateTime_Key
                           WHERE [DATE] BETWEEN '2011-11-28' AND '2011-11-28')

PartitionID is used because I partition the FACT table by Month. I have to be able to run the load over a certain date range and should not worry about double rows, therefore it looks first if the rows are already loaded or not.

From the performance this runs not bad, I need around 7-8 minutes for one day of data, but suddenly this goes up like a rocket and then takes > 1 hour for one day of data. What puzzles me is the fact that the load time doesn't go up gradually. Looking at the sql server i see that it is busy in the temp database and I see quiet some disk i/o (eventthough the sql server has around 140 GB RAM still free for him to grab).

Index are all up todate, no fragmentation, statistics are also looking good.

What am I missing to understand where this sudden performance drop comes from ?

Machine is:
(SQL 2008 R2 64bit / 8 cores / 192 GB RAM / SAN Disks / 10GbE)

The execution plan is likely to be changing.

Grab a copy of a fast plan and a slow plan and compare.

By using a plan guide you may be able to force the query to use the one plan for all occasions (after testing of course).


How to get tsql TOP PERCENT with OFFSET select top 10 percent *
from sys.databases
order by database_id

I want to get the same 10 percent result using offset but this query works wrong

offset 0 rows
fetch next (select cast((count(*) * 10/100) as int ) from sys.databases) rows only

TOP PERCENT

  Indicates that the query returns only the first expression percent of
  rows from the result set. Fractional values are rounded up to the next
  integer value.

Source

Meaning that TOP percent is rounding up, we could use the CEILING() function on a numeric value to get the same result.

You could change the query like this:

SELECT *
FROM sys.databases
ORDER BY database_id
OFFSET 0 ROWS
FETCH NEXT (SELECT CAST(CEILING((COUNT(*) * 10.0/100.0)) as int) FROM sys.databases) ROWS ONLY;

To get the same amount.

Issues with applying a filter.

If you add a where clause the results could differ, the reason being that in the changed query, you would need to add the where clause also on the query in the OFFSET FETCH part.

These two queries could return different results:

SELECT *
FROM sys.databases
where name not like '%A%'
ORDER BY database_id
OFFSET 0 ROWS
FETCH NEXT (SELECT CAST(CEILING((COUNT(*) * 10.0/100.0)) as int) FROM sys.databases) ROWS ONLY;

SELECT top 10 percent *
FROM sys.databases
where name not like '%A%'
ORDER BY database_id;

DB<>Fiddle

These two queries should return the same results:

SELECT *
FROM sys.databases
where name not like '%A%'
ORDER BY database_id
OFFSET 0 ROWS
FETCH NEXT (SELECT CAST(CEILING((COUNT(*) * 10.0/100.0)) as int) FROM sys.databases where name not like '%A%') ROWS ONLY;

SELECT top 10 percent *
FROM sys.databases
where name not like '%A%'
ORDER BY database_id;

DB<>Fiddle


SQL Server Disk throughput slower than diskspd This is a bit of a broad question but I am trying to understand a storage performance behavior with two of our servers.

I was following https://www.brentozar.com/archive/2015/09/getting-started-with-diskspd/

On one server I ran dskspd with the below parameters on the same disk as the DB.

diskspd.exe -b2M -d60 -o32 -h -L -t56 -W -w0 -c10G G:\MP13\Data\test.dat

and got around 1400MB/s

I was also able to get comparable throughput using a T-SQL query as below and calculating the throughput from the number of reads and time.  I got this from Glenn Berry SQL Course on PluralSight "Improving Storage Subsystem Performance
".

set statistics io on
set statistics time on
checkpoint
dbcc dropcleanbuffers
select count(*) from table1 with(index(1))

On the other server though, I can get the high throughput numbers from the diskio tool but using SQL server method I am not able to get the throughput numbers.  the SQL numbers are close to what I get if I run diskspd in single thread, even though the plan is running in parallel.

So I was wondering what are the things I can check to see why SQL Server's IO is slow or why SQL Server is not able to push more IO's through.

There are a few conceptual things to work through. When you run a tool like diskspd, you're benchmarking theoretical limits of your storage, not guaranteeing a specific performance profile of a query. Additionally, you're only testing for the configured pattern (2MB block size, queue depth of 32, 56 threads, 100% read [random? sequential?]). SQL Server has varied read/write patterns and there's no guarantee that your query follows this test pattern. Essentially, you're testing two different things with diskspd and a query.

The following two points are primarily valid if you're using a SAN.

Additionally, different storage tools run tests in different ways. sqlio sizes a 10GB test file with a null byte (0x0). diskspd appears to have a varying pattern, but it repeats nonetheless.

fio sizes with random data. I've only used vdbench once and have honestly not checked what it uses to fill files. I bring this up because most SANs will compress and deduplicate empty space and repeating patterns. As such, you're not necessarily testing performance on a files of a similar size (assuming your SQL Server data file is 10GB) on the SAN. Your data file can be compressed and deduped, to be sure, but probably not to the same level as files generated by diskspd.

This leads to a final point I'd like to make. Depending on your SAN's cache size, generally a 10GB file isn't large enough to actually even test your SAN performance (from an IOPS perspective). The SAN controller is probably able to compress and dedupe this test.dat file down to something of reasonable enough size to sit in controller cache and never actually touch the backing disks. As such, what you're testing is the path between the SAN controller and the OS, which you've identified as 1400MB/s.


SQL Select that inserts a new row based on a delimiter I have a table with the following sample values:

ID VALUE
1 |856|421|
2 |123|795|7125|
3 |96412|85251|2245|
4 |4845|88422|9155|15154|
5 |1165|98742|

How can I do write a select statement where the result will be:

    ID Value
    1 |856|
    1 |421|
    2 |123|
    2 |795|
    2 |7125|
    3 |96412|
    3 |85251|
    3 |2245|
    4 |4845|
    4 |88422|
    4 |9155|
    4 |15154|
    5 |1165|
    5 |98742|

The only constant is that each group of numbers are always included between | characters. Any ideas?

We need to preserve the pipes '|'.

I borrowed this split string function from this answer on StackOverflow. Due your data is enclosed using | you must select only those values where data <> '' and finally add the delimiter again using CONCAT function.

create function dbo.splitString(@input Varchar(max), @Splitter VarChar(99)) returns table as
Return
    SELECT Split.a.value('.', 'VARCHAR(max)') AS Data
    FROM (SELECT CAST ('<M>' + REPLACE(@input, @Splitter, '</M><M>') + '</M>' AS XML) AS Data) AS A
    CROSS APPLY Data.nodes ('/M') AS Split(a);
GO

Select id, concat('|', data, '|') value
from mytable x
cross apply dbo.splitString(x.value,'|') Y
where data <> '';
GO

id | value
-: | :------
 1 | |856|
 1 | |421|
 2 | |123|
 2 | |795|
 2 | |7125|
 3 | |96412|
 3 | |85251|
 3 | |2245|
 4 | |4845|
 4 | |88422|
 4 | |9155|
 4 | |15154|
 5 | |1165|
 5 | |98742|

dbfiddle here


Database locking issues? I have run into a transaction related issue on a SQL Server 2008 production database. A brief overview is that we have a website that has numerous concurrent users around the state, who do GUI type work (Adding record, modifying, viewing etc) via an ASP.Net website.

Each insert and update is done in it's own Transaction, handled by the data access layer. The database isolation, I believe, is set to Read_Commited.

All is working fine.

However, a new module has been added, which polls a separate database for information. If there is new info, a process starts a new transaction, and the uses the same data accessor code to read from our database, as well as reading from another separate database for the new info. It then does loads of checks to see what it must do with the new data... And the starts doing loads of updates or inserts into our database. This is all within a large transaction. All inserts and updates from both the UI application, and the polling service, go through the same CRUD procedures. Because an incoming message to be processed can hold a lot of entitys that need updating, the time for a transaction to complete can be between a split second, and a minute.

What we're finding though, is that when a larger message is processed, the UI locks up, and can lock for a user for 3 minutes.

So, we thought that maybe adding 'NOLOCK' hints to the selects may assist. It didn't. Well, it may have helped a bit, but the lockups are still happening.

I thought that the cause may be that the message arrives, and a transaction is started, which is locking other transactions from working (Even SELECT statements, which I don't understand). Profiling the database shows that even simple selects are taking ages to complete on the UI (Simple, such as SELECT fields FROM SingleTable WHERE PrimaryKey = Value

Our indexes seem OK... We do have Triggers on all tables which simply copy updates and inserts into an AUDIT database table. Don't think they're the problem.

I think it';s because of the transaction around the message processing, which is locking the UI out.

Can anyone maybe share an experience or tell me where I can look to see why we're getting UI lockouts? The UI should have priority. Message processing is a background thing... the users need to be priority... but it seems the messages are locking the database... and we're unsure if the UI ever locks the message processing.

Hope someone can assist. I can provide as much info as possible to help.

Try turning on Read Committed Snapshot isolation (RCSI) (but be aware this will put increased pressure on your tempDB, which should ideally be on its own set of dedicated physical spindles).

There are two 'snapshot' levels available in SQL Server 2005 onwards: READ COMMITTED SNAPSHOT does optimistic reads and pessimistic writes; whereas SNAPSHOT ISOLATION performs optimistic reads and optimistic writes. Suggest you try RCSI.

Enabling Row Versioning-Based Isolation Levels

To alter this setting, you need to switch to single-user mode to ensure there are no queries in flight (which would then fail):

ALTER DATABASE dbname
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE dbname
SET READ_COMMITTED_SNAPSHOT ON;
GO

ALTER DATABASE dbname
SET MULTI_USER;
GO


Possible to drop table ignoring dependencies? I had a table from which I wanted to delete a lot of rows but that was failing because of memory constraints.  I saw a suggestion to "simply" copy all the rows that I don't want to delete into a new table, drop the old table, then rename the new table to the old table's name.  That would be fine except for other objects that depend on the original table.  From what I can tell that means I'd have to drop cascade and then recreate everything that depends on the original table.

Is there a way to either script everything that depends on the original table so I can easily recreate them in pgadmin or psql?

Alternatively, is there a way to ignore the dependency long enough to delete the old table and rename the new table to the old table's name?

I believe this is how pg_repack works so there must be a way.

DROP TABLE example CASCADE;

will drop the table example. and drop all REFERENCES constraints that point into that table.

so after replacing the table you will also need to rebuild the references.

to get the dependencies you could use pg_dump --schema-only and then grep for REFERENCES example(

You will not be able to rebuild the references until the needed data is present in the table.


Optimize postgresql query with multiple subqueries I'm trying to optimize this query taking between 3-4 minutes (it's running on hdd right now and it will go as low as 10s on sdd when going to production I guess but it's still very high).
Table candles contains 50 millions rows.

CREATE TABLE candles (
    timestamp BIGINT NOT NULL,
    instrument_id INTEGER NOT NULL,
    notional_usd BIGINT NOT NULL,
    CONSTRAINT candles_pk PRIMARY KEY (timestamp, instrument_id)
);

Note : timestamp cannot be changed from BIGINT to TIMESTAMP for compatibility issue

Query :

EXPLAIN ANALYSE
SELECT
    extract(hour from to_timestamp(timestamp/1000)) as "time",
    extract(hour from to_timestamp(timestamp/1000))::text as metric,
    SUM(notional_usd)/t.max_notional_usd*100 AS notional_usd_percent
    FROM candles,
    (
        SELECT
          MAX(sumed.notional_usd) AS max_notional_usd
        FROM (
          SELECT
            extract(hour from to_timestamp(timestamp/1000)) as "time",
            SUM(notional_usd) AS notional_usd
          FROM candles
          GROUP BY "time"
        ) AS sumed
    ) AS t
GROUP BY "time", t.max_notional_usd

Result :

"GroupAggregate  (cost=15600527.41..16047921.77 rows=220656 width=104) (actual time=185056.051..227426.091 rows=24 loops=1)"
"  Group Key: (date_part('hour'::text, to_timestamp(((candles."timestamp" / 1000))::double precision))), (max((sum(candles_1.notional_usd))))"
"  ->  Sort  (cost=15600527.41..15710031.53 rows=43801648 width=56) (actual time=183186.442..205920.751 rows=43801648 loops=1)"
"        Sort Key: (date_part('hour'::text, to_timestamp(((candles."timestamp" / 1000))::double precision))), (max((sum(candles_1.notional_usd))))"
"        Sort Method: external merge  Disk: 2143000kB"
"        ->  Nested Loop  (cost=3815240.76..5549731.22 rows=43801648 width=56) (actual time=31596.456..86215.017 rows=43801648 loops=1)"
"              ->  Aggregate  (cost=3815240.76..3815240.77 rows=1 width=32) (actual time=31596.429..31596.430 rows=1 loops=1)"
"                    ->  Finalize GroupAggregate  (cost=3611424.70..3812482.56 rows=220656 width=40) (actual time=20496.568..31596.383 rows=24 loops=1)"
"                          Group Key: (date_part('hour'::text, to_timestamp(((candles_1."timestamp" / 1000))::double precision)))"
"                          ->  Gather Merge  (cost=3611424.70..3804207.96 rows=441312 width=40) (actual time=20022.571..31596.259 rows=72 loops=1)"
"                                Workers Planned: 2"
"                                Workers Launched: 2"
"                                ->  Partial GroupAggregate  (cost=3610424.67..3752269.58 rows=220656 width=40) (actual time=19835.282..30720.177 rows=24 loops=3)"
"                                      Group Key: (date_part('hour'::text, to_timestamp(((candles_1."timestamp" / 1000))::double precision)))"
"                                      ->  Sort  (cost=3610424.67..3656051.39 rows=18250687 width=16) (actual time=19370.253..25047.717 rows=14600549 loops=3)"
"                                            Sort Key: (date_part('hour'::text, to_timestamp(((candles_1."timestamp" / 1000))::double precision)))"
"                                            Sort Method: external merge  Disk: 371464kB"
"                                            Worker 0:  Sort Method: external merge  Disk: 377288kB"
"                                            Worker 1:  Sort Method: external merge  Disk: 365720kB"
"                                            ->  Parallel Seq Scan on candles candles_1  (cost=0.00..785454.74 rows=18250687 width=16) (actual time=0.029..8526.498 rows=14600549 loops=3)"
"              ->  Seq Scan on candles  (cost=0.00..858457.48 rows=43801648 width=16) (actual time=0.017..16963.830 rows=43801648 loops=1)"
"Planning Time: 0.154 ms"
"Execution Time: 228151.276 ms"

How could I optimize this query ?
This query 'only' takes 37s without the embeded selects. What I basically want is to divide SUM(notional_usd) for each hour by the max value. Is there a way I could do this without the embeded select ?

You can avoid the second scan of the large table using window functions and a subquery:

SELECT "time",
       metric,
       notional_usd / max(notional_usd) OVER () * 100 AS notional_usd_percent
FROM (SELECT extract (hour FROM to_timestamp(timestamp/1000)) AS "time",
             extract (hour FROM to_timestamp(timestamp/1000))::text AS metric,
             SUM(notional_usd) AS notional_usd
      FROM candles
      GROUP BY "time") AS q;

You will still have to perform the GROUP BY, so raising work_mem for this query is a good idea.


Query slower in Prod than Test I'm a bit puzzled by a query that runs slower in production than test (as always..)

Can't quite figure it out since both queries are identical in every way.
Test query runs in 0,5 sec and prod takes 5 sec. Servers are identical regarding version. I tried the query in prod when it was idling at night, no difference. The test database was created a month ago, no big size difference.

The query plan is identical, looked at it in plan explorer and I can't spot the difference.

Adding some facts:

Prodserver and testserver on same storage and esx-host
Not database related, restored testdb to prod, proddb to test, proddb to proddb-copy
Identical SQL settings (apart from database mail and DAC)
Prodserver not under pressure when testing, testserver got 2 less cores and 15Gb less mem
Restored the db on my laptop, got the same good performance that I get on the test server.

Next step (Thanks Joe Obbish) I'll have a look att the code in the function. Gonna test to execute the code outside function and check what plan it produces, (SELECT dbo.cfn_diffworkingminutes('2016-10-01', GETDATE()

Memory and core count can go a long way towards affecting the plan choice.

You can force the dev instance to think it has the same core-count and memory in place, for the purposes of plan-creation, with DBCC OPTIMIZER_WHATIF.

The syntax is:

dbcc optimizer_whatif ({property/cost_number | property_name}
    [, {integer_value | string_value} ])

Properties you can specify include:

Status
CPUs
MemoryMBs

dbcc optimizer_whatif ('Status'); shows the current settings in effect.  On SQL Server 2016, I see the following output:

---------------------------------------------------------
Optimizer what-if status
---------------------------------------------------------
property_number current_value default_value property_name
---------------------------------------------------------
         1                  8             0 CPUs
         2                  0             0 MemoryMBs
         3                  0             0 Bits
         4               1000          1000 ParallelCardThreshold
         5                  1             1 ParallelCostThreshold
         6                200           200 ParallelCardCrossProd
         7                 50            50 LowCEThresholdFactorBy10
         8                 12            12 HighCEThresholdFactorBy10
         9             100000        100000 CEThresholdFactorCrossover
        10                 10            10 DMLChangeThreshold

So, if your production server has 16 cores, but your dev server only has 4 cores, you can trick your dev server query optimizer to act like it has 16 cores by running this:

dbcc optimizer_whatif ('CPUs', 16);

Memory can be similarly "reconfigured", with:

dbcc optimizer_whatif ('MemoryMBs', 20480);

Now, when I run the Status command, I see:

---------------------------------------------------------
Optimizer what-if status
---------------------------------------------------------
property_number current_value default_value property_name
---------------------------------------------------------
         1                 16             0 CPUs
         2              20480             0 MemoryMBs
         3                  0             0 Bits
         4               1000          1000 ParallelCardThreshold
         5                  1             1 ParallelCostThreshold
         6                200           200 ParallelCardCrossProd
         7                 50            50 LowCEThresholdFactorBy10
         8                 12            12 HighCEThresholdFactorBy10
         9             100000        100000 CEThresholdFactorCrossover
        10                 10            10 DMLChangeThreshold

FYI, in order to see the output above, you need to enable the following trace flags:

dbcc traceon (2588);
dbcc traceon (3604);

2588 can be used to force DBCC HELP to display syntax for undocumented DBCC statements, such as optimizer_whatif.

3604 sends trace output to the client. Effective only when setting trace flags with DBCC TRACEON and DBCC TRACEOFF.

I would recommend setting the CPU and memory with optimizer_whatif to the same values you see in production, and re-run your queries.  You may need to flush the plan cache prior to re-running your queries.


Database creation on Linux with dbca: "command not found" I have installed an Oracle on my machine. When I open a session with VNCServer and type dbca,  either as root or as oracle, I get these errors:

-bash : -dbca : Command Not found
Xlib: connection to ":0.0" refused by server
Xlib: No protocol specified

What do I need to do to fix this?

Regarding your first error ('dbca: command not found'):

ensure ORACLE_HOME is set correctly
ensure $ORACLE_HOME/bin is in your path
run the command as oracle user

If the error persists, check the contents and permissions of $ORACLE_HOME/bin.


Updating records in MS Access linked table will activate triggers? I have a simple yet undocumented (or I could not find the documentation to solve my uneasiness) question, I hope I have come to the right place:

I am using MS Access to act as a poor front end for SQL Server. In this Access file I have some linked tables that will undergo some editing at various points by some users, be it via forms or directly accessing the table in spreadsheet view (I advised against this but, well...).

My concern is: the server has some procedures set on this table that are coded to trigger on insert/update. Will they be triggered when doing these edits via Access front end?

Yes. The Access frontend will not suppress the SQL procedures.


On which filegroup will constraints be created by default when adding them to a table on a filegroup and how should they be separeated? We have multiple filegroups, that look something like this:

PRIMARY (default)
app_data
app_index

When I create a table on filegroup app_data

CREATE TABLE Person
(
    [PersonID] INT NOT NULL IDENTITY,
    [Identifier] NVARCHAR(64) NOT NULL,
    [SupervisorID] INT NULL
) ON app_data

and in the current setup (I haven't made this) constraints will get added in 3 different ways

-- Primary keys are added to app_data
ALTER TABLE Person ADD CONSTRAINT PK_Person
    PRIMARY KEY (PersonID) ON app_data

-- Foreign keys are added to "nothing"
ALTER TABLE Person ADD CONSTRAINT FK_Person_PERSON
    FOREIGN KEY (SupervisorID) REFERENCES Person(PersonID)

-- Unique Indexes are added on app_index
-- Sometimes also with constraint notation (but these are the same in background anyway)
CREATE INDEX UX_Person_Identifier
    ON Person(PersonID) ON app_index

On what filegroup will the foreign key be created? app_data or PRIMARY?

Should all constraints be created on app_index? Or should only "custom" indexes be created on this filegroup?

Only PRIMARY KEY and UNIQUE constraints have an optional ON filegroup clause. That's because these constraints always create an automatic index to support their uniqueness. The filegroup clause decides where this index will live, so in your case it should always be 'app_index'.

You cannot specify a filegroup for other constraints such as FOREIGN KEY, CHECK or DEFAULT.

The metadata about all constraints (their name, table, columns etc.) is kept in system tables and thus is always in the PRIMARY filegroup (even if you change the default filegroup).

BTW: That's why you always have to restore at least the PRIMARY filegroup of a database. Without it, there is no metadata about the database content available to SQL Server. And this is why I recommend to use the PRIMARY filegroup only for metadata - at least in enterprise scenarios. Have at least one other filegroup and set that as default. This way PRIMARY remains the minimal possible size and runs the least risk of corruption.


Best Innodb Buffer Pool Size for 8GB RAM? I found out that my MySQL server is configured with only 128MB for innodb_buffer_pool_size.  That is not enough considering that I have a 8GB RAM server. It hosts MySQL server and an Apache/Catalina server.  What should I change this seeting to?

I have four(4) old posts to demonstrate how to measure and size up the InnoDB Buffer Pool based on the amount of RAM on the DB Server and how much space is currently being taken up by data/index pages:

Apr 14, 2011 : What are the main differences between InnoDB and MyISAM?
Oct 22, 2012 : How much memory do I need for innodb buffer pool?
Oct 22, 1012 : How large should be mysql innodb_buffer_pool_size?
Apr 08, 2013 : MySQL performance impact of increasing innodb_buffer_pool_size

Please look over these posts, run the queries, and use good judgment. Why did I say to use good judgment ? If the ideal Buffer Pool Size is greater than 6GB (75% of your DB Server's RAM), the highest innodb_buffer_pool_size permissible would be 6G.

Give it a Try !!!


Correct organization of the database structure and selection Sorry for newbie question.

For example, there is some kind of abstract device, we will call the Device class and the tasks for it: Task1, Task2, Task3.
For concreteness, I will describe everything in java codes.

class Device {
long id;
String name;
int ip;
int filed1;
String filed2;
// and so on
}

class Task1 {
long id;
long device_id; // id from Device class
int type = 1;
int property1;
char property2;
byte property3;
// Properties and their number are completely different in all Task, that is, the tasks are completely different.
}

class Task2 {
long id;
long device_id; // id from Device class
int type = 2;
String anotherProperty1;
float anotherProperty2;
long anotherProperty3;
short anotherProperty4;
double anotherProperty5;
int anotherProperty6;
// Properties and their number are completely different in all Task, that is, the tasks are completely different.
}

class Task3 {
long id;
long device_id; // id from Device class
int type = 3;
Charsequence yetAnotherProperty1;
AnotherType yetAnotherProperty2;
// Properties and their number are completely different in all Task, that is, the tasks are completely different.
}

Further, for each device a certain number of tasks are assigned, both with different classes and with the same ones. For example.

// pseudocode
Device device1 = new Device (.....);
// The list of tasks for the device 1 (Any combination, any number)
1. new Task2
2. new Task2
3. new Task1
4. new Task3

Device device2 = new Device (.....);
// The list of tasks for the device 2 (Any combination, any number)
1. new Task1
2. new Task3

Device device3 = new Device (.....);
// The list of tasks for the device 3 (Any combination, any number)
1. new Task1
2. new Task3
3. new Task1
4. new Task3
5. new Task1
6. new Task3
7. new Task1
8. new Task3
9. new Task1
10. new Task3
// and so on

I created the Device table with all the required fields, but I do not know how to save all this correctly in the database.
I will assume that:

is need to create more tables: Task1, Task2, Task3?
(when saving) to the specific tables put the all tasks, Device.id which corresponds to Task.device_id and the order of their execution?
(when load) do a query from all the tables Device, Task1, Task2, Task3 - and check from each task whether Device.id = Task1.device_id AND Device.id = Task2.device_id AND Device.id = Task3.device_id?

First, is the correct assumption, is the organization of the database structure correct or ....?

If yes, how correctly to do the optimized selection from several tables, write sql request if it is possible ..

I'm working with sqlite

First of all: It is necessary to create one table for each task?

In my opinion: No

You can create only one table "Tasks" where you define a name for your Task and define your standard properties of a task. Whenever you want to create more tasks, then you need to create new tables every time.

As an example: If you want to save humans, you don't need to create tables like "female_childs", "males", "females", "died_females" or whatever - Just create one table "persons" where you can store stuff like "gender", "alive", "birthDay", "deathDay"...

Your Question: Order of execution
In this case, what you need is some sort of sequence to order your tasks. This can be stored in a new table alongside with your device, like that:

DeviceId
TaskId
SequenceNr

I think I've got your confusion about these dynamic task properties but as Basil Bourque describes, you need a "child" property table to store information like you want to do it.

Your third question is now obsolete :)


SQL Server - export tables to XML files I need to export all tables (500) of a sql server 2008 R2 database to XML. What is the best and fastest way to do that?

This job can be done using the BCP utility.

Pre-requisites:

You would need to configure your server, to enable xp_cmdshell:

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

Permissions, which account xp_cmdshell is running as?

xp_cmdshell 'whoami'

Create a new folder, where you will save the files, and grant Full access on this folder to the user of step 2.

I recommend you first try with a few tables, add a TOP criteria to the cursor selection, just to verify this script runs well in your server.

select TOP 3 TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
from INFORMATION_SCHEMA.Tables
where TABLE_TYPE = 'BASE TABLE';

Basically the script gets a list of tables from INFORMATION_SCHEMA, and executes the BCP utility for each element in the list.

XML files will be named as: DATABASE_SCHEMA_TABLE.XML

This is the full script, before to run it, replace the enclosed <...> values according to your system configuration:

use <your_database>
go

declare @ServerInstance nvarchar(50),
        @Database sysname,
        @Schema sysname,
        @Table sysname,
        @RootFolder nvarchar(165),
        @BcpParams nvarchar(100),
        @cmdBCP nvarchar(500),
        @FQI varchar(600),
        @FileName varchar(600),
        @retExec int;

    set @ServerInstance = '<server\instance>';
    set @BcpParams = '-t -T -w';
    set @RootFolder = '<folder_name>';

    declare curXml cursor fast_forward for
        select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
        from INFORMATION_SCHEMA.Tables
        where TABLE_TYPE = 'BASE TABLE';

    open curXml;
    fetch next from curXml into @Database, @Schema, @Table

    while @@FETCH_STATUS = 0
    begin

        set @FQI = @Database + '.' + @Schema + '.' + @Table;
        set @FileName = @RootFolder + @Database + '_' + @Schema + '_' + @Table + '.xml';

        select @cmdBCP = ' bcp "SELECT * FROM '
                         + @FQI
                         + ' row FOR XML AUTO, ROOT(''' + @Table + '''), elements"'
                         + ' queryout "' + @FileName + '" '
                         + @BcpParams
                         + ' -S ' + @ServerInstance;

        print @cmdBCP;

        EXEC @retExec = xp_cmdshell @cmdBCP;

        if @retExec <> 0
        begin
            close curXml;
            deallocate curXml;
            raiserror('BCP Error', 16, 1);
        end

        fetch next from curXml into @Database, @Schema, @Table;
    end

    close curXml;
    deallocate curXml;


Filtered index only used when the filtered part is in the JOIN not the WHERE I've created the filtered index below however when I run the 2 queries further down this index only gets used for a seek in the first example that has the END_DTTM in the JOIN rather than the where clause (that's the only difference in the queries). Can anybody explain why this happens?

Index Creation

CREATE NONCLUSTERED INDEX [ix_PATIENT_LIST_BESPOKE_LIST_ID_includes] ON [dbo].[PATIENT_LIST_BESPOKE]
(
    [LIST_ID] ASC,
    [END_DTTM] ASC
)
WHERE ([END_DTTM] IS NULL)

Queries

DECLARE @LIST_ID INT = 3655

--This one seeks on the index

SELECT
    PATIENT_LISTS.LIST_ID
FROM
    DBO.PATIENT_LISTS
    LEFT JOIN DBO.PATIENT_LIST_BESPOKE ON PATIENT_LISTS.LIST_ID = PATIENT_LIST_BESPOKE.LIST_ID
                                      AND PATIENT_LIST_BESPOKE.END_DTTM IS NULL
WHERE
    PATIENT_LISTS.LIST_ID = @LIST_ID

--This one scans on the index

SELECT
    PATIENT_LISTS.LIST_ID
FROM
    DBO.PATIENT_LISTS
    LEFT JOIN DBO.PATIENT_LIST_BESPOKE ON PATIENT_LISTS.LIST_ID = PATIENT_LIST_BESPOKE.LIST_ID
WHERE
    PATIENT_LISTS.LIST_ID = @LIST_ID AND
    PATIENT_LIST_BESPOKE.END_DTTM IS NULL

For the optimizer to match a predicate to an index (filtered or otherwise), the predicate must appear adjacent to the Get operation in the logical query tree. To facilitate this, predicates are generally pushed as close as possible to the leaves of the logical tree before optimization begins.

To greatly simplify, the physical index strategy implementation does this:

Predicate + Logical Get -> Physical Get (using Index)

The query you are interested in starts with the predicate above an outer join:

Predicate on T2 --+-- LOJ -- Get (T1)
                       |
                       +---- Get (T2)

This shape does not match the index strategy rule because the predicate is not adjacent to the Get. So, the first part of the answer is that filtered index matching will fail unless the predicate can be pushed past the outer join.

The second part is simply that the optimizer does not contain the necessary exploration rule to move a predicate past an outer join on the preserved side, because the transformation is so rarely valid. It is a general feature of the optimizer that only the most frequently useful rules are implemented.

As a result, matching the filtered index fails in this case. To be clear, the rewrite would be valid in the very specific case you cite (second query).

For the first query form (with different semantics), the predicate is associated with the join from the start, and the predicate push-down logic can move this the short distance to the Get because it does not have to move past an outer join as explained above.

Background and further information:

Query Optimizer Deep Dive (in four parts)


Dimension Modelling Senario Data Warehouse Design I'm looking for some advised on how to model the following scenario in our data warehouse.

We have the following entities

Staff
Mobile Devices
Computers
Mobile Bills

If feels like its simpler to model without fact tables for Mobile Devices and Computers, because essentially there are no measures, Option 1 shows this. Option 2 shows the use of Fact tables.

What's would your advise be on the best practice in this scenario? Any input would be appreciated, thanks.

The second version looks more realistic. Mobile devices and computers can change their users and this version allows to keep the history.


Safe method to rename a crashed myisam table I have a large MyISAM table which has crashed. Repairing the table will take some time. The table only INSERTed to and SELECTed from, never updated. To allow the application to continue working, albeit with reduced capability, I thought of

renaming the crashed table
creating a new table with the original name
switching processing back on
repairing the backup table
switching off processing
merging the repaired and new data
switching on processing

The other steps in this process do not pose any risk due to the nature of the application.
Is it safe to rename a crashed MyISAM table? How?

I believe that I can't simply do ALTER TABLE...RENAME.... as this always does a row-by-row copy into a new table.

Apparently Peter Zaitsev uses a "tiny script which moves out all MyISAM tables out of MySQL database directory" but doesn't seem to give details of what this script does (presumably stops database first?).

This might work; do it with caution:

CREATE TABLE new LIKE real;   -- copy schema
RENAME TABLE real TO bad, new TO real;  -- replace broken table with empty
REPAIR TABLE bad;     -- slowly repair
RENAME TABLE real TO old, bad TO real;  -- replace empty table with fixed table
DROP TABLE old;   -- clean up

I don't know if the CREATE will work with a broken real.

The RENAMEs are 'instantaneous'.

Then schedule moving to InnoDB.


Grouping data to move up and down in a Table depending on Month day I am using TSQL.  I have a set of data in a table. I have a stored procedure that runs every day that populates this table. The thing is I need the order of the data to change each day of the month. This data needs to dynamically split up into 7 groups.

For example:

I have 300 pieces of data, I run the query on the first day and I have items 1-300. The second day I would run it and I would have at the top of my list 43-300 and at the bottom 1-42 (in that order). The third day I would have 86-300 on top and then 1-85 on the bottom (in that order). The fourth day and so on throughout the month would be continuing this.

I already have the initial sort, which has to remain in tack. I need a secondary sort (grouping) that would allow this movement.

I am not sure how to do this. Could anyone give me any ideas on how to do such a thing. I know how to do this if it was based on the week, but month is a bit more confusing.

Lets assume that the query is:

SELECT
    -- long list
FROM
    -- complex joins
WHERE
    -- multiple conditions
ORDER BY
    a, b ;

and that the cutting numbers (0, 42, 85) can be found by some functions fun(@X) where @X is a number from 1 to 7. Then we can use a CASE expression in the ORDER BY:

SELECT
    -- long list
FROM
    -- complex joins
WHERE
    -- multiple conditions
ORDER BY
    -- the "cutoff"
    CASE WHEN ROW_NUMBER() OVER (ORDER BY a,b) > fun(@X)
        THEN 0 ELSE 1
    END,
    a, b ;

If the cutoff is as simple as diving the rows into 7 equal (or almost) parts, then we can use ((@X-1) * COUNT(*) OVER ()) / 7 as fun(X), where @X is between 1 and 7.

If the total count of the returned rows is 300, the expression will give 0, 42, 85, 128, 171, 214 and 257.


SELECT query types and optimization I'm using a database monitoring system that, amongst other data, shows the following values for SELECT queries:

number of joins that use ranges on the first table
number of joins that do a full scan of the first table
number of joins without keys that check for key usage after each row
number of joins that perform table scans because they do not use indexes
number of joins that use a range search on a reference table

Of these queries, which ones are the least performing and hence should be optimized first?  I'd say #2 and #4 in order of importance, but I'd like to hear comments about the other types too.

This monitoring system shows also the total number of SELECT queries as the sum of these five values. Are these really all possible, mutually-exclusive  cases for a SELECT?

I'm not talking about a specific query (which I can find in the slow query log); I'm more interested in the type of query amongst these 5 listed above. For instance, full table scans are usually a bad thing (unless we're working on small tables, in which case we won't care about optimization).

My other question was if these types may be overlapping, because the monitoring system shows a 6th value which is the sum of these five, and this makes no sense if the types are not mutually exclusive (as we suspect).

None of the above.

A "full scan" is irrelevant if the table has only 10 rows.  Ditto for "not using an index".

There are many queries for which no index is useful; you stats will have you stumbling over them.  For example, if you index a 'flag' (yes/no, M/F, etc), the optimizer will usually eschew the index and simply scan the table -- because it is more efficient to do so.

"First table".  Unless you are looking at the EXPLAIN, it is difficult to predict which table will be looked at "first".  Even the existence of LEFT does not conclusively predict ordering of the tables.

What is your goal, anyway?  The main goal, in my opinion, is to find and fix queries that are bogging down the system.  To that end, I want a list of queries, ordered by total elapsed time (number of occurrences times avg time).  An that is very efficiently provided by

long_query_time = 1
turn on slowlog
wait a day
use one of these to get the list:
    mysqldumpslow -s t
    pt-query-digest

Then, I get EXPLAIN and SHOW CREATE TABLE for the first couple of queries and work on them.

If you want to see 'change over time', then record the 'total time' for the worst queries and see it those get better or worse next month.

You may find that some of the worst queries are not SELECTs.


Modelling a relationship which has multiple one-to-many parents I am attempting to model a relationship which could have two parents, one of them being optional, and the other being mandatory. The design should have the following rules,

A House must exist as the top level parent
A Bedroom must have a house as a parent
A House can have many Bedrooms
A House can have many Photos of the house
A Photo could also optionally be linked to a Bedroom, i.e. it is a photo of a bedroom in a house
A User should only be able to access a House and any child (Bedroom and Photo resources) that are owned by them (through the UserID field in House table)

My current design looks like this:

    +---------+         +-----------+
    | House   |         | Bedroom   |
    +---------+         +-----------+
+---> HouseID <---+     | BedroomID <----+
|   | UserID  |   +-----+ HouseID   |    |
|   | ...     |         | ...       |    |
|   +---------+         +-----------+    |
|                                        |
|                                        |
|           +-----------+                |
|           | Photo     |                |
|           +-----------+                |
|           | PhotoID   |                |
+-----------+ HouseID   |   OPTIONAL     |
            | BedroomID +----------------+
            | ...       |
            +-----------+

I currently enforce that a Photo must be linked to a House through the FK, so I can perform a JOIN to get the original UserID, as I know this key will already exist, but it feels like this is not the right way to go about it.

I ideally want to return Photos that are only linked to the House when querying data about the House, and then only Photos linked to a Bedroom when querying data about a Bedroom. I know this is quite easy to do via JOINs, but it makes me question whether this is the right design, as it seems to be more "working around the design" than fixing the root problem.

Would it be more useful to use mapping tables here? Or any further ideas on how to improve it?

I think your table design is good and normalized.

One thing that is not guaranteed yet is that if a photo references a bedroom, that bedroom should belong to the same house as the photo.

You could get that with an additional UNIQUE constraint:

ALTER TABLE bedroom
   ADD UNIQUE (houseid, bedroomid);

Then the foreign key constraint could be defined as:

ALTER TABLE photo
   ADD FOREIGN KEY (houseid, bedroomid) REFERENCES bedroom (houseid, bedroomid);

An extra index is not nice from a performance angle, but you want an index on the houseid column of bedroom anyway because of the foreign key, so an additional column shouldn't hurt.


What are the rules for using COLLATE in a query? I am putting a script together, that gives me the permissions on database objects.

SELECT  permission_order=710
       ,permission_type = 'Object Level Permissions'
       ,db = db_name(),
login_=null,
role_=dp.name collate Latin1_General_CI_AS,
Obj = sys.schemas.name + '.' + so.name collate Latin1_General_CI_AS,
Permission = permission_name collate Latin1_General_CI_AS,
[script]=
'IF OBJECT_ID ('  + '''' + '['+ sys.schemas.name + '].[' + so.name + ']'  + '''' + ') IS NOT NULL ' + CHAR(10) + state_desc +
' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + so.name + '] to [' + dp.name + ']' collate Latin1_General_CI_AS + CHAR(10) +
'ELSE ' + CHAR(10) +
'print ' + '''['+ sys.schemas.name + '].[' + so.name + '] - does not exist'''
+ CHAR(13)

from sys.database_permissions a
INNER JOIN  sys.objects so on a.major_id = so.object_id
INNER JOIN sys.schemas on so.schema_id = sys.schemas.schema_id
INNER JOIN sys.database_principals dp on a.grantee_principal_id = dp.principal_id
WHERE dp.name NOT IN ( 'public', 'guest')
AND a.class = 1

I want to use the word collate as few times as possible and still have a script that runs for multi-databases, multi-collations server.

How can I achieve that?
What is the rule for applying collate?

COLLATE operates per predicate or per expression, depending on context. COLLATE is mainly used to control how the string values are being compared or sorted. Hence it is most commonly used in JOIN, WHERE, and HAVING predicates, as well as GROUP BY and ORDER BY clauses (in which case it can be used per column / expression). It needs to be applied to string columns that could possibly have differing Collations. Non-string types (including XML) do not use COLLATE, and columns that are guaranteed to have the same Collation do not need it. For JOIN, WHERE, and HAVING predicates, it only needs to be specified on one side of the operator since the other side will be coerced into the specified Collation.

It generally wouldn't be used in a SELECT list unless there was a reason to change the Collation of the selected column / expression, which would mainly apply to VARCHAR data as you can change the Code Page, but I really don't see much application for this usage. The most likely reason to do this would be if the client requesting the data needs a different Code Page, but I can't think of a reason that this would ever be needed.

However, one reason to use it in the SELECT list (and possibly other places) is when doing string concatenation involving two or more columns that can potentially have mixed Collations. This scenario would apply to both VARCHAR and NVARCHAR data. You shouldn't need to worry about single columns and string literals as the literals will be coerced into the Collation of the column.

Another reason you might need COLLATE in a SELECT list is when using UNION, INTERSECT, or EXCEPT, and the Collation between columns / expressions in the same position between at least two of the queries is not the same.

So, looking at your query:

You don't need COLLATE in these places:

role_=dp.name collate Latin1_General_CI_AS,
because you are just selecting a single column, nothing is being mixed.
Obj = sys.schemas.name + '.' + so.name collate Latin1_General_CI_AS,
because both columns are database-level meta-data from the same database which is guaranteed to be the same Collation, plus a string literal that is already the database's default Collation (same as the two column's it is being concatenated with), but even if it wasn't already the same, it would be coerced into the Collation of the two columns. Only improvement here would be to prefix the literal with an upper-case N since the expression is NVARCHAR due to the schema names and object names being of type sysname which is an alias for NVARCHAR(128).
Permission = permission_name collate Latin1_General_CI_AS,
because you are just selecting a single column, nothing is being mixed.

You do need COLLATE in the string concatenation because you are mixing database-level meta-data (which uses the database's default Collation) and system-level meta-data (i.e. state_desc and permission_name) which comes from the hidden mssqlsystemresource database and usually has a Collation of Latin1_General_CI_AS_KS_WS. It should be fine to have COLLATE specified only once per expression since it should have the highest precedence and force both literals and columns into the stated Collation.

Finally, regarding which Collation to specify: DATABASE_DEFAULT, CATALOG_DEFAULT, or one of your choosing (such as you are doing here): given that you are working on NVARCHAR data (no chance of changing the Code Page / character set) and not using COLLATE for sorting or comparison (no chance of changing how the query works between different databases), it really doesn't matter; it's all the same in this particular scenario. You would use DATABASE_DEFAULT or CATALOG_DEFAULT if you needed the query (or that particular predicate or ORDER / GROUP item) to be sensitive to local database and change behavior depending on where the query is being executed.

Other notes:

I would suggested enclosing the result set column names in square brackets (i.e. [permission_order] instead of permission_order, [role_] instead of role_, etc).
You have CHAR(13) at the end which should be CHAR(10) like the rest of those newlines in that concatenation.
Even better would be to use NCHAR(10) and prefix each string literal piece of the concatenation with an upper-case N since you are concatenating sysname / NVARCHAR columns which will force the entire string into NVARCHAR, hence those CHAR() references and string literals are being implicitly converted anyway.
Also good would be to wrap the schema / object names in QUOTENAME() (and remove your explicit delimiters — [ and ] — in the string literals) as QUOTENAME has the benefit of escaping embedded delimiters.


Not able to access a database on shard mongodb cluster We have a mongoDB cluster with 2 shards, 3 config servers and 5 mongos instances.
We create new database for each day and drop database(s) that are older than 10 days.

On any one mongos, when I list all databases, there is one database shown that is occupying 40 GB space. This database should have been removed by timely scripts, but that did not happen.

When I try to drop this database, error Occurs as - "database does not exist".
Find below console output of step carried out -

    mongos> show dbs
    dailyDB_A23    37.890GB
    mongos> use dailyDB_A23
    switched to db dailyDB_A23
    mongos> show collections
    mongos> db.dropDatabase()
    { "info" : "database does not exist", "ok" : 1 }
    mongos>

On using this database and listing all collections, there is nothing shown on console.
Plus when I tried taking a dump of this database, nothing is backed up and command exits withing 2 to 3 seconds.

On connecting to PRIMARY of each replica set in shard, this database is visible (ran 'show dbs' after connecting to mongo shell) and files are also present in to data directory.

Kindly help me out here. If this database does not exist (as per error on dropping) why it is showing it in list with size of ~ 40 GB?

Thanks and Regards,

Vibhav

Try db.repairDatabase() which fixes inconsistency across database.
Check mongod and mongos logs for results.


Why is my database becoming suspect? Here is my situation: restored a known good backup (works fine on other machines) that originated on Sql Server 2012 onto a local 2012 install. Created DB as part of the restore. No errors reported.

I am accessing it through a Node.js app, using the Node-SqlServer driver (if that makes a difference). The first time that the app tries to access the db after restore, the db goes into Suspect mode.

I tried deleting the Db restoring from scratch. Same thing.

I was able to "fix" the db by running these commands. After that, the db runs fine (app can access and query the db witout issue). However, this seems like a less than ideal scenario.

Any ideas about what could be causing this and how I might be able to prevent it?

Computer is a laptop with a single SSD. I ran chkdsk and returned a clean bill of health. Note: this has happened with more than one db restore, specifically to this machine. So the circumstantial evidence points to the machine as being at fault here.

Knowing that a CHECKDB returns errors right after restoring a backup, as stated by Remus Rusanu, you have a corrupted backup. As fixing this afterwards is usually much more painful then before, you should plan (or ask your DBA) to check the consistency of your databases on a very regular basis. I remember having anxious moments waiting for the results of a first DBCC CHECKDB for databases that have been in production for a few years, long before I started my job.

If you want to know more about corruption and DBCC, have a look at:

How to corrupt a database, by Kendra Little: http://www.littlekendra.com/2011/01/24/corrupthexeditor/. I found it a great way to understand how things were working under the hood.
Paul Randal's articles, for example http://www.sqlskills.com/blogs/paul/category/checkdb-from-every-angle/ . He is THE authority on DBCC and you can also subscribe to their newsletter. The last one has a great introduction video on corruption and how to fix it.

So check (sorry for this one) the health before, try to restore a valid backup, and if you have no other choice, try DBCC REPAIR_ALLOW_DATA_LOSS. But as stated before, you'll lose data and that can even make your database unusable.


What situation RDBMS need to vm or docker virtualization? Usually  RDBMS just have only one primary server for writing, so what's the point of virtualization?
It cannot make multi-DB, and one additional layer of encapsulation does not mean that the speed will be slowed.
But I know there's a reason for exist, what are the scenarios that are particularly suitable for database virtualization?

Personally, I really like the idea of putting a golden set of data in a VM or container for QA testing. Spin up a new VM/container for each project and you can test it in isolation. When testing is complete, it goes away cleanly. You can do the same thing for development: Think git branches.
I can't tell you how many times I've had projects look like they were failing because someone mucked up the data in the dev/qa environment to test a different project.


Upgraded from SQL Server 2008 R2 Express to SQL Server 2014 I've upgraded my SQL Server from the 2008 R2 Express edition to a full standard version of SQL Server 2014. Now I'm running them both side by side. But my problem is how can I abandon the 2008 version and only run on the 2014 version? I've check the properties on the SQL Server 2014, and it shows that I'm still with 2008 R2 Express edition. How is that possible?

Now i'm running them both side by side

So, to clarify, you did a side-by-side upgrade.  Meaning that you installed a new instance of SQL Server 2014?

In that case, if you are ready to rid yourself of the SQL Server 2008 R2 instance then you would need to transfer all of your necessary entities from the 2008 R2 instance to the 2014 instance.  That'll include everything it would take to make your clients hit the new 2014 instance.  Most notably, you'll be backing up and restoring your databases from the source (SQL Server 2008 R2 instance) to the destination (SQL Server 2014 instance).  But you will most likely need to transfer other server objects as well.

Note: Max brought up a good point in his comment below, ensure that you have a valid database backup/restore on your destination instance before removing anything.

  how can I abandon the 2008 version and only run on the 2014 version

Point your applications/clients to the new SQL Server 2014 instance after the migration.  And then you can rid yourself of the SQL Server 2008 R2 instance if you no longer want those bits to be living on that machine.  Warning, you should verify with the business and end users that functionality is now back to the same state as it was prior to the upgrade.

  I've check the properties on the SQL 2014, and it shows that I'm still with 2008 R2 Express edition

I'm not sure what you mean here.  How did you check the "properties" and what exactly are you referring to?  You're either connecting to your old SQL Server 2008 R2 instance, or you new SQL Server 2014 instance.  If the former, you'd be in the context of the old one.  If the latter, you should be seeing the new 2014 instance.  If that's not what you mean, then please clarify.


Can I backup (Full) the Log-Shipping Secondary Database? I have a Highly pressured Production Environment with Database name DB1. And I want to run a weekly DBCC CHECKDB process on one of the databases there.

(You can understand,I can't do it on my production environment,Performance IMPACT).

So, I thought to use one of my Log-Shipping process.
I am have a Log-Shipping process, from DB1(Primary) to DB1_LS Database(Secondary) which locate in a distant server (DWH server).

Question:

Is there a way that I can take a full backup of the Secondary database DB1_LS?

I want to be able, to restore it and run the DBCC CHECKDB on it (COPY-ONLY BACKUP ?)

I believe you cannot take a native backup of the databases if they are in Standby/Read Only mode.

So backing up the secondary database would be not be a good go.

Also, if LS DB is in "Read only mode" you can run DBCC CHECKDB , it will complete but it would not get into the boot page and hence not the check you expected it to be. Moreover even if it reports or finds an error you would not be able to run a repair statement with DBCC CHECK.

What you can do is, take backup of the DB1 during off peak hours over a network to another test/dev machine with appropriate resources to restore the backup.

From the restored backup, you can perform the DBCC CHECKDB on the restored database.

Also, please read this excellent article by Aaron Minimizing the impact of DBCC CHECKDB : DOs and DON'Ts for best practises on it.


SQL Server database snapshots for integration testing I am trying to define a way of working with a test database (in SQL Server) for our integration testing.

My idea was to do these steps at the launch of the integration test assembly:

create a totally empty database
run the "create database objects" script to create all relevant database objects (table, views, sequences etc.)
fill the "base data" (lookup values etc.)
take a database snapshot called (db)_Basis as the "base line" for future integration tests

Now before every test class (containing 1-n tests), I was planning to simply do a "restore from snapshot" to get back to the well-defined, more or less "empty" state of the database. Works like a charm so far.

However, there are a set of integration tests that need to operate on a large test database - so I was hoping to do this before each of those test fixtures (classes with n individual tests)

restore database from the (db)_Basis snapshot
insert those 50'000+ rows of data into the database
create another snapshot (db)_With_Testdata snapshot

and then for each test, reset the database to the well-defined (db)_With_Testdata snapshot version, run the tests, verify the outcome and so forth.

Trouble is: I cannot seem to have two db snapshots at the same time - once I do, I cannot restore my database to either of them.... I keep getting this error:

  Msg 3137, Level 16, State 4, Line 9
  Database cannot be reverted. Either the primary or the snapshot names are improperly specified, all other snapshots have not been dropped, or there are missing files.

  Msg 3013, Level 16, State 1, Line 9
  RESTORE DATABASE is terminating abnormally.

Is that really how SQL Server database snapshots work?? Seems awfully restricting..... I would understand if I couldn't go back directly to the original "(db)_Basis" snapshot maybe - but just because I now have two snapshots, I cannot even go back to the most recent one?!?!?

Unfortunately, it's by design.

Taken from BOL page "Revert a Database to a Database Snapshot":

  Limitations and Restrictions

  Reverting is unsupported under the following conditions:

  The database must currently have only one database snapshot, to which    you plan to revert.
  Any read-only or compressed filegroups exist in the database.
  Any files are now offline but were online when the snapshot was    created

As an alternative, you could drop the first snapshot (db)_Basis. I can understand that this seems very limiting but look at it this way: snapshots are sparse files based on the original data files, so reverting to a specific snapshot would invalidate all snapshots anyway (the base data files would be changed by the revert operation). The limitation can be annoying, but doesn't look unreasonable.


InnoDB to MyISAM Conversion Problem I have a dedicated server based on CentOS 5 OS , mysql 5 and phpmyadmin

I was trying to convert all database tables from InnoDB to MYISAM. I tried to do it by setting in /etc/my.cnf file the value

skip-innodb

Afterwards, I found some errors in some php scripts in the server

Unknown table engine 'InnoDB'

I checked some databases in phpmyadmin i found in each database some tables IN USE

How can I make a successful conversion of all tables from InnoDB to MyISAM ?

Setting skip-innodb does disable the InnoDB Storage Engine, but that does not convert the tables.

You need to script the conversion of all the tables.

STEP #1

Comment out the skip-innodb

[mysqld]
#skip-innodb

STEP #2

service mysql restart

STEP #3

Execute the Following Script snf View Results

CONVERT_SCRIPT=/tmp/InnoDBConversionToMyISAM.sql
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=MyISAM;') "
SQL="${SQL} FROM information_schema.tables "
SQL="${SQL} WHERE engine='InnoDB' AND table_schema <> 'mysql'"
mysql -uroot -p -ANe"${SQL}" > ${CONVERT_SCRIPT}

STEP #4

View the script

vi /tmp/InnoDBConversionToMyISAM.sql

or

less /tmp/InnoDBConversionToMyISAM.sql

If you like the contents, then ...

STEP #5

Login to mysql and run

mysql> source /tmp/InnoDBConversionToMyISAM.sql

STEP #6

Uncomment out the skip-innodb

[mysqld]
skip-innodb

STEP #7

service mysql restart

STEP #8

There is no STEP 8. That's it !!!

Give it a Try !!!


Row-based Permission in SQL Server Newbie Question I am relatively new to this( Database, general security issues) , so I may be over-complicating this. I have a database containing student grades . I  want to design a view in which I restrict access to each student . I can think of 3 ways of doing this: by granting permissions in master database, using access control lists maybe creating groups in AD and by assigning roles, though this would seem to require one role for each student which seems too much work. Can someone please guide me along? Thanks.

If you want every student get access only to the rows reguarding that sudent, you can accomplish this by adding one more field to your students table where for each student you'll be store his AD account, then create a view that will join all you need including your students table and using a filter like this

where ... and user = dbo.students.ad_account

So every student will get access to his rows only. Here USER is a built-in T-SQL function that will return student's AD account. Certainly you should create logins for your students using windows authentication;  you do not need to map them individually, it will be sufficient to add one windows group yourDom\STUDENTS where every student is a member of yourDom\STUDENTS