How do you GRANT SELECT on hidden resource database mssqlsystemresource? I get an error when I run this query:

    SELECT
      dependencies.referencing_id as object_id,
      dependencies.referencing_minor_id as column_id,
      dependencies.referenced_id AS referenced_object_id,
      RTRIM(referenced_objects.type) AS referenced_type
    FROM
      sys.sql_expression_dependencies AS dependencies
    JOIN sys.objects AS referenced_objects WITH(NOLOCK) ON referenced_objects.object_id = dependencies.referenced_id
    WHERE dependencies.referencing_id != dependencies.referenced_id

Error:

The SELECT permission was denied on the object 'sql_expression_dependencies', database 'mssqlsystemresource', schema 'sys'.

The Login has membership only in the PUBLIC server role and granted VIEW ANY DATABASE and VIEW ANY DEFINITION.  I added a User with membership in the PUBLIC database role for the database I am comparing.  I assumed the server level permission would suffice, but I was wrong. I did this on a 2008R2 database in addition to a 2014 database.

On the 2014 database I also granted server level permission SELECT ALL USER SECURABLES.  The error persisted.

I granted the User SELECT on the database.  The error persisted.

I removed the SELECT permission and added the User to the data_reader database role.  The query resolved, however no data is returned (running the same query as a sysadmin returns 130+ rows).  Now I'm completely baffled.

I granted SELECT to the user on sys.sql_expression_dependencies  explicitly with the same results above.

After researching these permissions, the consensus seems to be that granting SELECT to a User on a database is the same as adding the User to the data_reader role.  But this is obviously not the case.  I want to create a Login/User with the smallest privilege set possible to achieve the comparison.

How do I grant access to the sys.sql_expression_dependencies view in mssqlsystemresource without granting data_reader access on the whole DB? Is there a way to grant SELECT on mssqlsystemresource only?

**Note - I've read a couple of other posts on SE regarding mssqlsystemresource but they don't pertain to this:

I have no DENY anything assigned to this Login/User
Membership in the PUBLIC role (server or database) has no bearing since the permissions are identical to those on a "known good" server.
I'd rather not have the user have blanket SELECT permissions on the entire database.

Not sure where you've stumbled along the way, but this works for me:

CREATE LOGIN permtest WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
USE somedatabase;
GO
CREATE USER permtest FROM LOGIN permtest;
GO

According to this page, the user needs SELECT permission on sys.sql_expression_dependencies, and VIEW DEFINITION on the database.

In my experimentation, the following allowed the user to select from the view, but it returned 0 rows, because they don't have the ability to view definition (which includes dependency chains):

GRANT SELECT ON sys.sql_expression_dependencies TO permtest;

In order to actually see any relationships in somedatabase, I also had to add the following:

GRANT VIEW DEFINITION ON DATABASE::floob TO permtest;

I could not find any way to make that more granular (VIEW/DENY definition worked for individual objects, but without the database-level right, I still couldn't see any rows in the catalog view, and DENY did not prevent the objects from showing up in the catalog view nor did it even prevent me from viewing the definition). I feel like SQL Server would have a hard time resolving that granularity anyway - if you had a view that referenced a table, how should the catalog view look if you have grant on the view and deny on the table, or vice versa?

If you don't want to grant VIEW DEFINITION on the database, then create procedures that use EXECUTE AS OWNER, select (filtered?) rows from the catalog view, and give the users (and of course, that could also be a role) execute permissions on the procedure.

CREATE PROCEDURE dbo.GetDependencies
WITH EXECUTE AS OWNER
AS
BEGIN
  SELECT is_schema_bound_reference --, ...
    FROM sys.sql_expression_dependencies;
END
GO

GRANT EXECUTE ON dbo.GetDependencies TO permtest;


Stored Procedure on a read-only AlwaysOn Availability Group replica We have a read-only reporting server that is part of the AlwaysOn availability group.   Our developer wants to create some stored procedures. My question is where should the stored procedure be created--will that be on the primary since the secondary is read only ?

A possible workaround would be; Just create a database in the secondary instance and then create the stored procedures accessing the data from the databases being replicated.

EDIT: But this database wouldn't be replicated.


Running different instances of SQL Server from different networks on one box I want to be able to run SQLSMS and select an instance to connect to but the instances are on different networks.  I remotely log on to our server and from there, I want to be able to access our clients servers on different networks.

I currently move data from our client server to our server to process over an ftp connection which takes time.  Things would be significantly faster if I could log on to our server and have all the data already there and accessible essentially removing ftp.

I want to know if this is possible and if so, where can I look or go to accomplish this.

If you can access the instances at the IP layer then you will always be able to connect using either SQL Authentication (if enabled) and even Windows Authentication using the runas /netonly trick. Say you are in the domain 'foo' and you want to connect to an instance in the domain 'bar', and there is no trust relationship between 'foo' and 'bar'. Since the instance in the 'bar' domain can only authenticate an user in the 'bar' domain you would normally have to be running as 'bar\user' to connect to this instance, but of course your host in 'foo' will not let you run as 'bar\user'. runas /netonly to the rescue: start your client process (eg. SSMS) using the /netonly option:

runas /netonly bar\user "c:\program files\...\ssms.exe"

When launched this way (you'll be prompted for bar\user password) the SSMS will authenticate to any resource over the network as 'bar\user' and you'll succeed to connect to the 'bar' based SQL Server instance. This trick works with local accounts as well, so instead of bar\user you can use <machinename>\Administrator to appear to that machine as the local administrator.

If the destination server is unreachable at the IP layer then you have no option, you must make it reachable, either by VPN, or by DirectAccess, even a netcat bridge can work with the /netonly trick.


Difference between all_nodes VS cluster_nodes CouchDB 2.X cluster membership? The _membership endpoint in CouchDB (http://user:pass@domain:5985/_membership) returns the following json to me:

{
  all_nodes: [
    "couchdb@n1.domain.me",
    "couchdb@n2.domain.me",
    "couchdb@n3.domain.me"
  ],
  cluster_nodes: [
    "couchdb@n1.domain.me",
    "couchdb@n2.domain.me",
    "couchdb@n3.domain.me"
  ]
}

When would all_nodes NOT be the same as cluster_nodes?

I managed to setup a cluster incorrectly when building CouchDB from source - there were 3 nodes in 'all_nodes', but none in 'cluster_nodes'.

An answer points to the Server Fault question BigCouch all_nodes vs cluster_nodes, where using BigCouch the question author found _membership returned a list of cluster_nodes but nothing in all_nodes. (So similar to the question I'm asking, although opposite to what I've seen where only all_nodes had values).

I don't understand the answer:

  I just heard from Robert Newson on IRC that BigCouch nodes are connected lazily.

Because:

I thought I read in CouchDB somewhere that nodes to be online at the time they are added to a cluster; and
This is at best one reason why there may be differences in the all_nodes and cluster_nodes results.

I'm effectively asking why there are 2 fields for what seems to be the same thing in CouchDB 2.x? My (very limited) experience is that a cluster that shows nodes in the all_nodes field but not in the cluster_nodes field doesn't work.

It's either not an answer to this question, or so far above my level that I don't understand it.

Your question has possibly been answered (in part) in the Server Fault Q & A:

BigCouch all_nodes vs cluster_nodes

The official documentation states that when you add a node to the CouchDB cluster, the output of the query _membership will return a list of all_nodes and cluster_nodes. No indication that this information may differ unless you account for:

  to see the name of the node and all the nodes it knows about and are connected too.

...which slightly implies that there may be differences.

The linked Server Fault answer refers to Robert Newson, who is a developer of CouchDB:

  I just heard from Robert Newson on IRC that BigCouch nodes are connected lazily.

I guess that is a quite an authoritative answer in that case.

When you request the _membership page, CouchDB might have connected all the nodes, or it might not have. This would explain why you noted differences in the all_nodes and cluster_nodes listings.

In your specific case where all_nodes contains more information than cluster_nodes the cause is explained in the official documentation:

  all_nodes are all the nodes thats this node knows about.
  cluster_nodes are the nodes that are connected to this node.

You had a situation where the node you were querying was aware of other nodes (all_nodes), but none of them were part of your cluster (clustered_nodes).

It seems like only the developer or the community on Github could answer your question completely. Consider asking your question on Github, or opening an issue on the apache/couchdb Github page.


Select IDS where all values can't be found in another table Hey I'm pretty new to SQL and I've come up with a problem that I haven't been able to solve

I'm trying to make a query to bring up ID's that have ALL null values and are located in another table.

DB looks like:

Table 1

BOOK ID| User ID
1      |   1
1      |   2
2      |   17
2      |   16
3      |   17
3      |   16
4      |   3
4      |   16

Table 2

USER ID | Name
   1    | Jeff
   2    | Bill
   3    | Fred

The desired outcome would be to bring up Book IDs 2 and 3 as both IDs only have users that cannot be found in Table 2.

I think the sentence is quite clear, using a left join to get null USER_ID's and returning those where count = 0;

SELECT
    BOOK_ID, COUNT(USERS.USER_ID) AS USERS
FROM
    BOOKS
LEFT JOIN
    USERS
    ON USERS.USER_ID = BOOKS.USER_ID
GROUP BY
    BOOK_ID
HAVING COUNT(USERS.USER_ID) = 0;

BOOK_ID | USERS
------: | ----:
      2 |     0
      3 |     0

db<>fiddle here


SQL Server Express 2016 high CPU Usage as soon as I log into Windows I have a Windows Server 2012 R2 configured for ASP.net applications that connect to SQL Server 2016 Express installed on the server. This server is a VPS with 3 cores and 1.5 GB RAM. The load on the server is not high at all.

I am having problems with the servers CPU Usage. Some seconds or minutes, as soon as I log in to the windows account through RDP, the SQL Server process starts consuming between 15% and 33% of CPU constantly, which is equivalent to using 1 CPU almost completely.

I first checked for running queries with SP_WHOISACTIVE and there are no running queries. (http://whoisactive.com/)

I then followed the instructions at: http://logicalread.solarwinds.com/troubleshoot-high-cpu-sql-server-pd01/#.V2xtwLvhCUk
and have found that the culprit is “% User Time” when diagnosed by the performance monitor.

I have not been able to identify what the problem is after this point. All I know is that as soon as I log into the windows account, the CPU starts being consumed. As soon as I log off, the cpu usage of the process drop down to below 3%. Sometimes, while I am logged into windows, the cpu usage drops for some minutes, but it goes up again. I don't have to open any specific Windows application for this behavior to start.

Does anyone know what could be happening?

as Aaron pointed above, the problem actually was related to having only 1.5 GB of RAM memory. The server was upgraded to 2 GB or RAM and the problem was solved.


Very slow postgresql aggregation EDIT: docker image for testing with the same data (obfuscated)

$ docker run --rm --name pg -d homer5439/pgagg
$ docker exec -ti pg bash
# createdb -U postgres test; zcat /tmp/corr.sql.gz | psql -U postgres test
# psql -U postgres test

and you can run the examples below.

I was wondering why the following query takes more than 30 seconds on postgresql 12.2:

SELECT
    contract_id,
    array_agg(corr) AS corr
  FROM
    corr
  GROUP BY contract_id;

More info: the corr table has about 150000 records. Each record has the following structure:

gse=# \d corr ;
                                         Table "public.corr"
    Column   |  Type   | Collation | Nullable |                             Default
-------------+---------+-----------+----------+-----------------------------------------------------------------
 corr_id     | integer |           | not null | nextval('corr_corr_id_seq'::regclass)
 contract_id | integer |           |          |
 start_date  | date    |           |          |
 corr_type   | text    |           |          |
 descr       | text    |           |          |
 credit      | numeric |           |          |
 detail      | text    |           |          |
Indexes:
    "corr_pkey" PRIMARY KEY, btree (corr_id)
    "corr_contract_id_idx" btree (contract_id)

The detail field contains text up to 2/3 MB in length (about 10% of the records have detail that long; the others have some (~10-20) kilobytes).
The number of distinct contract_id values is currently 2317.

Following other suggestions found on this site, I've tried changing the value of work_mem up to 10GB without noticeable changes.

Here's the query plan:

                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate  (cost=9883.29..9911.57 rows=2263 width=36) (actual time=1184.971..1357.309 rows=2317 loops=1)
   Output: contract_id, array_agg(corr.*)
   Group Key: corr.contract_id
   Buffers: shared hit=78012 read=49899
   ->  Seq Scan on public.corr (cost=0.00..9320.19 rows=112619 width=571 (actual time=0.057..959.359 rows=112619 loops=1)
         Output: contract_id, corr.*
         Buffers: shared hit=78012 read=49899
Planning Time: 0.131 ms
Execution Time: 1357.747 ms

When you running "alone", if you mean running it in "psql" but just without the EXPLAIN ANALYZE, then "psql" is reading the entire result set into memory, and going over that set deciding how long the longest entry is for each column so that it can format it to that length.  That can be slow, though I would not expect 30 seconds for 2317 lines!

Except I see you don't have a "corr" column in your "corr" table, so what you are aggregating is the entire row records, which you already said has a column that can be very wide.  So you are slinging a huge amount of data at psql, and it takes a long time to process it, plus maybe doing some swap.


How to select second last row from table? Table Name :- ModelParam_EMS

Primary Key :- ParameterID

No of Rows :- 500

want to select 499th row from table only.

Note :- No. of rows can be less or more but not fixed.

Create Table :-

            CREATE TABLE `modelparam_ems` (
              `MakerModelID` smallint(5) unsigned NOT NULL,
              `ParameterID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
              `Category` tinyint(3) NOT NULL DEFAULT '1',
              `ParameterName` varchar(50) NOT NULL,
              `ParameterAdd` smallint(5) NOT NULL,
              `ParamterType` tinyint(3) unsigned NOT NULL,
              `DataTypeIndex` tinyint(3) unsigned NOT NULL,
              `DefaultResolutionIndex` tinyint(3) unsigned NOT NULL,
              `ParamUnit` varchar(20) NOT NULL,
              `ParamMaxVal` varchar(40) NOT NULL,
              `ParamMinVal` varchar(40) NOT NULL,
              `ParamVal` varchar(40) NOT NULL,
              `CreatedByID` smallint(5) unsigned DEFAULT NULL,
              `CreatedOn` datetime DEFAULT NULL,
              `ModifyById` smallint(5) unsigned DEFAULT NULL,
              `ModifyOn` datetime DEFAULT NULL,
              `chanelno` int(11) DEFAULT NULL,
              PRIMARY KEY (`ParameterID`),
              KEY `MkrMdlId` (`MakerModelID`),
              KEY `DTIndex` (`DataTypeIndex`),
              KEY `ResIndex` (`DefaultResolutionIndex`),
              KEY `CrtByID` (`CreatedByID`),
              KEY `ModfyById` (`ModifyById`),
              KEY `paramTp` (`ParamterType`),
              KEY `Categor` (`Category`),
              KEY `idx_modelparam_ems_parameterid` (`ParameterID`),
              CONSTRAINT `Category` FOREIGN KEY (`Category`) REFERENCES `category_ems` (`categoryid`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `m2m3` FOREIGN KEY (`MakerModelID`) REFERENCES `makermodel_ems` (`MakerModelID`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `modelparam_ems_ibfk_1` FOREIGN KEY (`CreatedByID`) REFERENCES `userlogin_ems` (`UserID`) ON DELETE SET NULL ON UPDATE SET NULL,
              CONSTRAINT `modelparam_ems_ibfk_2` FOREIGN KEY (`ModifyById`) REFERENCES `userlogin_ems` (`UserID`) ON DELETE SET NULL ON UPDATE SET NULL
            ) ENGINE=InnoDB AUTO_INCREMENT=10012 DEFAULT CHARSET=utf8;

You can use this:

SELECT *
FROM table_name
ORDER BY column_name DESC
LIMIT n - 1, 1

The query just returns the first row after n-1 row(s)

For example, if you want to get the second most expensive product (n = 2) in the products table, you just use the following query:

SELECT productCode, productName, buyPrice
FROM products
ORDER BY buyPrice desc
LIMIT 1, 1

Try this. Could test here and it's ok.

Found this site helpfull


Oracle RAC can you have a cluster that contains one node Have oracle RAC production system. Two nodes in the primary, two nodes in the secondary. Getting a test system.Can we have two nodes in the primary and one in the secondary? Or to put it another way  - can you have a one node cluster in standard oracle RAC?

From the documentation:

  Oracle RAC One Node is a new option available with Oracle Database 11g
  Release 2. Oracle RAC One Node is a single instance of an Oracle RAC
  enabled database running on one node in a cluster. This option adds to
  the flexibility that Oracle offers for database consolidation while
  reducing management overhead by providing a standard deployment for
  Oracle Databases in the enterprise.

This is a means to have a multi-node RAC, one which one database can only run on one node at a time, so it allows you to (more cheaply) use RAC for active-passive clustering, which saves on having both RAC and another clustering product such as VCS. Or are you asking if it is possible to have a RAC system entirely on one physical host, running Clusterware, ASM, etc and a database? Yep, that is possible too, the standard installer can do this for you.


Forbid mongodb not authenticated connect If to run mongo with authorization (i.e mongod --auth) it still allows to connect using client mongo and get to mongo cli where one will get refused to execute any command.

But I wonder if it is possible to close such connection for the unauthenticated user so one does not see mongo cli at all?

A new connection to MongoDB has two key aspects:

establishing a connection
authenticating the connection

As at MongoDB 3.4, the source IPs that are able to establish a connection are determined by  your firewall settings. There is also a bindIp setting for MongoDB which determines which network interface(s) mongod listens to.

Once a connection is established, access control and authentication is managed by your MongoDB configuration.

In your example, you are able to connect via the mongo shell because the source IP is not blocked by firewall configuration. Assuming access control is enabled and authentication has been properly configured, establishing a connection to MongoDB does not provide any access to data.

For more information on best practices, see the MongoDB Security Checklist.


How should I best name my timestamp fields? When I am looking to create some timestamp fields (or other date/time style fields), what is the best way to name them? Should I just put record_timestamp?

You should describe the purpose of the column and not necessarily the data type.  You can include date/time/timestamp in the name, but you should also include the meaning.  For example

CreationDate
StartDate
StatusTime
Accessed
Updated

Adding Date/Time/Timestamp and such at the end is particularly useful when the abscence of the addition would conflict with another column.  For example, a table may need both a Status and a StatusTime.


what is the first schema checked? I am a little confused right now. Can someone answer me with a simple answer to this question.

I have a user [user_test] and has [db_owner] role assigned to him for one database [database_test].
The user's default schema is [user_test] and almost all objects from the [database_test] are created under schema [user_test]. In the database exist 3 more objects with schema [dbo]

The user has rights to both schemas: [dbo] and [user_test] without explicitly specifying them in select statements in front of objects reference.

What is the first schema checked by SQL when an object is called by user [user_test] ?

I mean, If [user_test] issues a select for an object from dbo schema (without schema in front), will SQL first check in it's default schema specified and then in dbo?

thanks,

With SQL Server 2005 & 2008 the first schema that is searched is the users default schema.
from SQL Server Best Practices – Implementation of Database Object Schemas

Default Schema
Users can be defined with a default
schema. The default schema is the
first schema that is searched when it
resolves the names of objects it
references.

and

creating the base tables in
the dbo schema will enable table
objects to be referred to without an
explicit schema name. This is because
an object will be located by searching
the default schema first, followed by
the dbo schema. So:
SELECT * FROM Table1
Will assess the following statement first:
SELECT * FROM <defaultschema>.Table1
If it cannot find the object, the server will assess the following statement:
SELECT * FROM dbo.Table1
The assessment process can be improved by using either the fully qualified name
or the DEFAULT_SCHEMA option described earlier. By setting a value for
DEFAULT_SCHEMA for the user, the server will check the DEFAULT_SCHEMA
first, removing an unnecessary ownership checking process.
This can improve performance considerably on heavily utilized systems.

Default Schema was introduced in SQL Server 2005 Upgrading to SQL Server 2005 and DEFAULT_SCHEMA setting


Is there a Portable MySQL For teaching and testing purposes I would like to run MySQL without actually having to install it.

I have found portable PostgreSQL for both Macintosh and Windows, which is good, but have been unable to find the same for mySQL.

I know that I can run MySQL from the popular web server packages, such as AMPPS and XAMPP, but they need installing anyway, and are over the top for my requirements.

Is there a portable app for OS X and for Windows which will do the job?

Thanks

Yes you can have MySQL as independent ZIP archive and it is also available for download in MySQL download page for windows, do the following steps

Download ZIP archive
Extract/UN-zip the archive
Change the default ini file if needed
You may also install service or start mysqld from cmd prompt using

cd \bin
  mysqld.exe

or if service is needed to be installed

cd \bin
   mysqld.exe --install MySQL Service Name --defaults-file=Absolute Path to .ini file

use mysql.exe -uroot to login MySQL

The ZIP archive is also available for UNIX* OS

Hope it helps


SQL server unable to create user with dash in domain name I am in the process of creating a proxy user in SQL server. I run the below SQL but it failed with the error because my domain name has dashes in it.

CREATE LOGIN [ABC-SEN-DEA\proxyssisuser] WITH PASSWORD='Password'

  Msg 15006, Level 16, State 1, Line 1
  'ABC-SEN-DEA\proxyssisuser' is not a valid name because it contains invalid characters.

Please help how to rectify this error.

You have a misconception in your thoughts. A Windows login which is used to create a SQL Server login does not require a password.

Solution

When you create a SQL Login using a Windows account you have to use a different syntax.

CREATE LOGIN [ABC-SEN-DEA\proxyssisuser]
FROM WINDOWS
WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

Reference: CREATE LOGIN (Transact-SQL) (Microsoft)

Explanation

When you link a Windows login with SQL Server, the SQL Server will trust the Windows login which has already been validated by the account logging in to the Windows system.

The chapter Connecting Through Windows Authentication in the article Choosing an Authentication Mode explains how Windows Accounts login to a SQL Server instance:

  When a user connects through a Windows user account, SQL Server validates the account name and password using the Windows principal token in the operating system. This means that the user identity is confirmed by Windows. SQL Server does not ask for the password, and does not perform the identity validation. Windows Authentication is the default authentication mode, and is much more secure than SQL Server Authentication. Windows Authentication uses Kerberos security protocol, provides password policy enforcement with regard to complexity validation for strong passwords, provides support for account lockout, and supports password expiration. A connection made using Windows Authentication is sometimes called a trusted connection, because SQL Server trusts the credentials provided by Windows.

Good luck.


I need to reduce the size of Database file -- I am confused between DBCC Shrinkfile and Data compression options I need to reduce the size of Database file. I am confused between DBCC Shrinkfile and Data compression option.

DBCC shrinkfile will reduce the actual database file size as far as I know but Data compression will not do the same. please advice or their are any other options because I cannot delete any file from my prod DB.

DBCC SHRINKFILE will release unused but allocated space. From books online:
DBCC SHRINKFILE does not shrink a file past the size needed to store the data in the file. For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB.
The data compression feature in SQL Server helps compress the data inside a database, and it can help reduce the size of the database. Apart from the space savings, data compression provides another benefit: Because compressed data is stored in fewer pages, queries need to read fewer pages from the disk, thereby improving the performance of I/O intensive workloads. However, extra CPU resources are required on the database server to compress and decompress the data, while data is exchanged with the application. Therefore, it is important to understand the workload characteristics when deciding which tables to compress.  Compression will not release allocated space. You can do so by shrinking the file once you compress.
Ref: https://technet.microsoft.com/en-us/library/dd894051(v=sql.100).aspx
See the answer to this question here asked earlier.


MySQL out of Memory, but Only Through MSSQL Linked Server I am getting an error "MySQL client ran out of memory" when trying to do some simple MySQL selects or inserts from Microsoft SQL through a linked server. It was working fine for a week or so, and for some reason now throws the error every time.

If I use Toad to connect to MySQL, everything seems fine running inserts or big selects. But trying to insert just 1 row through the MSSQL Linked server gives an error !?!?

I can "SELECT ID FROM MyTable LIMIT 10", but if I add "ORDER BY ID" to it, I get the error, even though there's an index on ID.

Is there some kind of maintenence I need to do with MySQL ?

I'm pretty much a MySQL "noob" as relates to trouble shooting. It's on a Linux box at our hosting site, with 4 gig ram.

Turns out the "fix" was to use OPENQUERY on the MSSQL server to push the processing to the client MySQL server.

-- User57112


Executing the query "UPDATE STATISTICS [dbo].[Contact] WITH FULLSCAN ..." failed My maintenance plan fails with the following error on one of my databases does anyone know how I can fix this problem? is is a corrupt table?

Executing the query

UPDATE STATISTICS [dbo].[Contact]
WITH FULLSCAN
...

Failed with the following error:

  A severe error occurred on the current command.  The results, if any,
  should be discarded.". Possible failure reasons: Problems with the
  query, "ResultSet" property not set correctly, parameters not set
  correctly, or connection not established correctly.

there could be many reasons for this error

it can be a corrupted index or data corruption in your database

check this post https://stackoverflow.com/questions/1175244/sql-server-error-on-update-command-a-severe-error-occurred-on-the-current-com

and this post

http://sqlanddotnetdevelopment.blogspot.com/2011/03/severe-error-occurred-on-current.html

Microsoft has a FIX for this problem, they say if you get this error while "You update the statistics of the table." then this fix is for you... they say This issue occurs because the database engine is trying to load dangling statistics, check this link its a Cumulative update

http://support.microsoft.com/kb/2498796


How to solve SIDs mistmatching in SQL or orphaned users? I moved and restored a database from a backup into another  SQL Server 2008R2 about a year ago. I wasn't aware of the concept of orphaned users. From what I understood, is that we can end up having mistmatched SIDs? So I run the following below query and I see that sid names are not the same for the server and the database for the same login user. So far, users are not complaining but how would I fix this? is there any stored procedure that list them for me? or what problems that I could be facing if I leave everything like it is.

select d.name, d.sid, s.name, s.sid
from sys.database_principals d
full join sys.server_principals s
on d.principal_id = s.principal_id

Any advice is much appreciated.

There are a couple of possibilities.

First if the orphaned user is a windows login/group (type U or G) then no problem.

-- If the login doesn't currently exist on the server
CREATE LOGIN [Windows Account] FROM WINDOWS;
-- Fix the user
USE dbname
ALTER USER [user name] WITH LOGIN = [Windows Account];

If it's a "SQL Login" (type S) and the login exists then again no problem.

-- Fix the user
USE dbname
ALTER USER [user name] WITH LOGIN = [Windows Account];

If the login does not exist for a "SQL Login" then you have to create the login and that will require knowing the password.

-- Create the login
CREATE LOGIN [login name] WITH PASSWORD = '<strong password>';

Don't forget to make sure your password meets your windows policy requirements.
If you still have the old server you can copy the login pretty easily from the other server including the password by getting the HASH of the password from the other server. (There may be some issues depending on if the two servers are different versions).

I did a post on this here: http://sqlstudies.com/2013/03/25/how-do-i-move-a-sql-login-from-one-server-to-another-without-the-password/


Rewrite query to not use CONCAT function in SQL Server 2008 R2? I know that the CONCAT function does not exist in SQL Server prior to 2012. How would i optimize my code to not use the CONCAT function?

         SELECT  dbname = DB_NAME(),
                  file_name = name,
                  TotalMB = CONVERT(decimal(12,1),size/128.0),
                  UsedMB = CONVERT(decimal(12,1),FILEPROPERTY(name,'SpaceUsed')/128.0),
                  FreeMB = CONVERT(decimal(12,1),(size - FILEPROPERTY(name,'SpaceUsed'))/128.0),
                  Command = CONCAT('USE MASTER; ALTER DATABASE ', DB_NAME(), '; MODIFY FILE (name = ',
                        [name], ', size = ',
                        convert(int,round(1.15 * FILEPROPERTY(name,'SpaceUsed')/128,-1)), 'MB)')
           FROM sys.database_files WITH (NOLOCK)
           WHERE type_desc = 'ROWS'
           ORDER BY file_id;

You can concatenate strings in SQL Server just by using the + operator. So to avoid using CONCAT, your code can be rewritten as:

SELECT  dbname = DB_NAME(),
                 file_name = name,
                 TotalMB = CONVERT(decimal(12,1),size/128.0),
                 UsedMB = CONVERT(decimal(12,1),FILEPROPERTY(name,'SpaceUsed')/128.0),
                 FreeMB = CONVERT(decimal(12,1),(size - FILEPROPERTY(name,'SpaceUsed'))/128.0),
                 Command = 'USE MASTER; ALTER DATABASE ' + DB_NAME() + '; MODIFY FILE (name = ' +
                    [name] + ', size = ' + convert(int,round(1.15 * FILEPROPERTY(name,'SpaceUsed')/128,-1)) + 'MB)'
FROM sys.database_files WITH (NOLOCK)
WHERE type_desc = 'ROWS'
ORDER BY file_id;


One Complex Stored Procedure Causes Estimated Cost Exception In SQL Server, I'm getting the following error "The query has been canceled because the estimated cost of this query (5822) exceeds the configured threshold of 300. Contact the system administrator."

This is the result of an execution of a stored procedure, which is pretty complex.  I haven't run into this for other stored procedures, only this one.  Is it possible to change the query cost for this one procedure somehow?  Can I do that in the stored procedure itself upon execution?  Or do I have to define this on the server only?

I'm using ADO.NET command to execute the stored procedure.

Thanks.

It sounds like you're running into query governor cost limits.  What's happening is that the database engine figures that your query will take 5822 seconds (97 minutes) to run.  Somebody, however, has configured a setting to kill any query which is expected to run more than 300 seconds (5 minutes).

To see if this applies to you, exec sp_configure 'query governor cost limit' and see what that run_value is.  My thought is that it's currently set to 300.

If you don't want to change the limit across the board, you should be able to run this query by setting the limit beforehand:  set query_governor_cost_limit 0.  That will adjust the particular session to turn off any cost limits, as you know that your query will blow past the limit and you don't much care.  Do note that you'd need some serious permissions to run that statement (the MSDN doc says sysadmin).


Reason for multiplicity of character/collation settings in MySQL? The last 48 hours or so I spend on trying to determine the cause for sporadic crashes of my MySQL (Aurora) DB. Turns out they were attributed  to my mixing of database, table, column and stored procedure collation/charsets. My bad, or is it?

Aurora aside, what were the practical reasons for all the ways to configure charset/collation in MySQL? Why couldn't utf8 just become utf8mb4 as part of a major release? Why changing charset or collation of a database/table doesn't actually change the database or table, only future additions to the schema? The last one provides for an opportunity to have a mix of columns charset/collations in your tables, for instance. What sane person would want to have different charsets/collations within the same DB? And if there is a rationale for all this madness, why not make it the exception rather than the norm when configuring mysqld?

In my case, to resolve mysqld crashes I had to ensure all of below return consistent charset/collation values:

show procedure status where db = '<database>';
SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "<database>";
SELECT T.table_name, CCSA.character_set_name
 FROM information_schema.`TABLES` T,
       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = "<database>"
;
SELECT table_name, column_name, character_set_name FROM information_schema.`COLUMNS` WHERE table_schema = "<database>";

Example crash stack trace which appears to be tied to my mixing of charset/collation types:

  mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=16777216
read_buffer_size=262144
max_used_connections=22
max_threads=2000
thread_count=18
connection_count=18
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1070055 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x2a2a2a50d000
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x2a2a2a50d000 thread_stack 0x40000
/rdsdbbin/oscar/bin/mysqld(my_print_stacktrace+0x2c)[0x960ccc]
/rdsdbbin/oscar/bin/mysqld(handle_fatal_signal+0x491)[0x6d7bb1]
/lib64/libpthread.so.0(+0xf5b0)[0x2b299b88d5b0]
/rdsdbbin/oscar/bin/mysqld(_ZN4JOIN7prepareEP10TABLE_LISTjP4ItemjP8st_orderS5_S3_P13st_select_lexP18st_select_lex_unit+0x127d)[0x76e0fd]
/rdsdbbin/oscar/bin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_P10SQL_I_ListI8st_orderESB_S7_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x86b)[0x776eab]
/rdsdbbin/oscar/bin/mysqld(_Z13handle_selectP3THDP13select_resultm+0x127)[0x777067]
/rdsdbbin/oscar/bin/mysqld[0x5ac17d]
/rdsdbbin/oscar/bin/mysqld(_Z30mysql_execute_command_internalP3THD+0x3c74)[0x7556d4]
/rdsdbbin/oscar/bin/mysqld(_Z21mysql_execute_commandP3THD+0x30)[0x757670]
/rdsdbbin/oscar/bin/mysqld(_ZN18Prepared_statement7executeEP6Stringb+0x354)[0x768914]
/rdsdbbin/oscar/bin/mysqld(_ZN18Prepared_statement12execute_loopEP6StringbPhS2_+0xb6)[0x768ad6]
/rdsdbbin/oscar/bin/mysqld(_Z19mysqld_stmt_executeP3THDPcj+0x153)[0x768d73]
/rdsdbbin/oscar/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x12e4)[0x75a204]
/rdsdbbin/oscar/bin/mysqld(_ZN22OscarSchedulerConsumer7consumeEjj+0xc0)[0x7e5610]
/rdsdbbin/oscar/bin/mysqld(_ZN22OscarSchedulerConsumer5startEv+0xa0)[0x7e5720]
/rdsdbbin/oscar/bin/mysqld(_ZN22OscarSchedulerConsumer11drain_queueEPv+0x6a)[0x7e596a]
/lib64/libpthread.so.0(+0x7f18)[0x2b299b885f18]
/lib64/libc.so.6(clone+0x6d)[0x2b299e658b2d]

Version 5.7 made good strides toward settling on utf8mb4; the next version, 8.0, is there.

But, changing anything under the hood is all too likely to cause grief.  MySQL learned that lesson when "fixing" the handling of ß in utf8_general_ci collation many years ago.  It broke lots of users' tables.  So, they came out with the kludge utf8_general_mysql500_ci.

The grief over the 767-byte limit on a column in an index would break blindly changing utf8 to utf8mb4.

I predict that utf8 will live on, but only for compatibility for those upgrading.  Meanwhile, a really UTF-8-compliant utf8mb4 will be the default.  And there will be a succession of collations such as utf8mb4_unicode_ci, utf8mb4_unicode_520_ci, utf8mb4_0900_ai_ci, and more will keep up with the Unicode standard (4.0, 5.20, 9.0), which keeps changing.

The charset and collation for a database is only the default for subsequently created tables.  Ditto for table:column.

To change the charset/collation for an entire table:  ALTER TABLE .. CONVERT TO ...

I think I am sane.  This makes sense to me:

CREATE TABLE People (
    name        ... CHARACTER SET utf8mb4,
    postal_code ... CHARACTER SET ascii
);

It is even a performance issue if you let postal_code with utf8mb4 unnecessarily explode by a factor of 4 during certain operations.

As for why things are crashing in Aurora, I don't see enough info to help with this question.  And, since I have not heard of anything similar in MySQL or MariaDB, I suspect is may be an Aurora bug.  (Aurora is MySQL plus low level improvements; I have not heard of charset issues, but who knows?)


Multi-instance 2-node SQL Cluster - do instances need their own disks? I currently have a single default instance of SQL2017 Standard on a 2-node cluster.
Against my preference, I need to setup a second instance on the cluster (the new database requires some users to have sysadmin so they can't be hosted alongside other databases on the existing instance :-( )

On the existing cluster, only the Active node is licenced.

When I install a second instance, do I HAVE to use a separate set of disks, or can it be installed on the same disks as the default instance?
(I'm not even sure if it is possible, let alone whether it is preferable)
Am planning to host BOTH instances on a single node - resource requirements are not high, so can be managed.

My concern is that one of the instances 'could' fail over to the second node.
But the second node is not licenced, so am not allowed to run an instance on each node (though I believe I do have a short period to fail it back).

Presumably I need to implement some monitoring to alert me if EITHER instance fails over, and get it failed back ASAP.

When I install a second instance, do I HAVE to use a separate set of disks, or can it be installed on the same disks as the default instance?

Yes you need to have a separate set of disks for the new instance.

  I'm not even sure if it is possible, let alone whether it is preferable) Am planning to host BOTH instances on a single node

That is not a bad idea, lot of people do and we call it as multi-instance cluster. Both the instances would be on same node in your case. They can also be on different nodes but you need to look at licensing part then.

  My concern is that one of the instances 'could' fail over to the second node. But the second node is not licenced, so am not allowed to run an instance on each node

In your scenario you have 2 instances on same node, this is fine since 2nd node is purely passive. BUT if a failover happens for "an" instance on 2nd node which is not licensed then you would have an instance running both on 2nd node and 1st node, this is a license violation if you run this scenario for long time, unless you immediately move it back such that both instances are on same node. The term "immediately" here means as soon as possible, MS is not going to charge you if let us say for hour you run 2 different instances on 2 different nodes.

PS: I am not a licensing expert but you should get this verified from local licensing expert of your region.


Multiple tables for different payment transaction I'm building an application that users can pay some fees to sign up for a yearly membership and courses they're interested in.

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

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

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

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

Thanks!

Always, start with the data model, you will get complete picture. I don't get your complete requirements, from my understanding, the following points are written.

Business Requirements:

A user can subscribe more than one course.
A course may subscribed by more than one user.
A user can subscribe more than one membership.
A membership can be subscribed by more than one user.
A user can have more than User Payment.
A user payment must contain atleast one User.

Data Model:

For the above requirements, the logical data model may look like this:

I think now you can keep your common payment info on UserPayment table. Also have a thought about future requirements, in that case, create separate entities for the following entities (examples).

Tax (type, rate, etc).
Price (type, value, etc).
Membership type (fulltime, quaterly, etc).
Course type (technical, design, etc).

Depends on the requirements, the model will change. First understand the requirements completely and start with the conceptual data model. It is a starting point, you will learn more, I hope this will give you an idea. Thanks!.


How do I give root@localhost permission to grant privileges in MySQL? Whenever I try to Grant Privileges as root@localhost, it says permission denied (using password=Yes).

My root user is the one that I am logged in as.   I verify this by select user();

It responds with 'root'@'localhost'.

I have also checked out this stack overflow post which seems to be the same issue:

https://stackoverflow.com/questions/28796510/cannot-grant-permissions-in-mysql-using-root-or-any-account-access-denied-f

and this one

https://stackoverflow.com/questions/1559955/host-xxx-xx-xxx-xxx-is-not-allowed-to-connect-to-this-mysql-server

I am having no luck.  If I Show GRANTS;, It lists all GRANT privileges: SELECT, Insert, Update, DELETE, CREATE, DROP, etc.

How do I make sure that ROOT has GRANT option itself?
Why would my root user not have all privileges?  I am using mysql version: 5.5.38-0+wheezy1 on Debian.

I have dealt with this issue before.

When you ran

select count(1) UserTableColumnCount from information_schema.columns
where table_schema='mysql' and table_name='user';

you should have gotten 42. That's how many columns MySQL 5.5 has for mysql.user. Since you got 39, that means you must have upgraded from MySQL 5.1. That has 39 columns.

I wrote an earlier post about the number of columns in mysql.user in different versions : MySQL service stops after trying to grant privileges to a user

Here is the post where I dealt with this : mysql: Restore All privileges to admin user

Hopefully you could run

# mysql_upgrade --upgrade-system-tables

to realign mysql.user and have it autofill missing permissions with Y.

Give it a Try !!!

If you want to try to fix the mysql.user manually, here are the steps:

#
# Backup the mysql.user table
#
CREATE TABLE mysql.user_backup LIKE mysql.user;
INSERT INTO mysql.user_backup SELECT * FROM mysql.user;
#
# Add Missing Columns
#
ALTER TABLE mysql.user
    ADD COLUMN Create_tablespace_priv enum('N','Y') NOT NULL DEFAULT 'N'
    AFTER Trigger_Priv
;
ALTER TABLE mysql.user ADD COLUMN plugin char(64);
ALTER TABLE mysql.user ADD COLUMN authentication_string text DEFAULT NULL;
#
# Give root user all privileges
#
UPDATE mysql.user SET
Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',
Create_priv='Y',Drop_priv='Y',Reload_priv='Y',Shutdown_priv='Y',
Process_priv='Y',File_priv='Y',Grant_priv='Y',References_priv='Y',
Index_priv='Y',Alter_priv='Y',Show_db_priv='Y',Super_priv='Y',
Create_tmp_table_priv='Y',Lock_tables_priv='Y',Execute_priv='Y',
Repl_slave_priv='Y',Repl_client_priv='Y',Create_view_priv='Y',
Show_view_priv='Y',Create_routine_priv='Y',Alter_routine_priv='Y',
Create_user_priv='Y',Event_priv='Y',Trigger_priv='Y',
Create_tablespace_priv='Y'
WHERE user='root';
FLUSH PRIVILEGES;

That's it !!!


Can't see some stored procedures in sys.procedures Documenting a database and find that I cannot see stored procedures in sys.procedures, even though I can see them in Object Explorer.

select p.name, x.value, p.modify_date
from sys.procedures p
left join sys.extended_properties x
on p.object_id = x.major_id
where p.is_ms_shipped = 0
and p.name NOT LIKE 'sp_%'

To ensure you can see the objects you are looking for:

Make sure you are in the right database / right instance - seems simple and silly but it happens all the time. This also includes not using things like AttachDbFileName in your connection string - this means that two different programs will actually have two separate copies of your database, so if one adds a procedure, the other will never see it.
Don't add unnecessary filters that don't accomplish anything (sys.procedures already filters out system objects, and if you use the sp_ prefix for your own procedures - which you shouldn't - you probably don't want that filter to leave those objects out, either).
Validate that you have VIEW DEFINITION access to all of the potential schemas where these objects may live. These are going to be protected in the metadata / catalog views but may still be exposed to you in certain scenarios under Object Explorer (especially if your Object Explorer connection is running under a different context than your query window(s)).

Also note that these are potential answers for future readers too, not just for the current OP (who we now know had an issue with the last bullet).


Usage of RESTRICT or No ACTION in FOREIGN KEY Constraints Sorry if this question is naive, but I was unable to understand possible usage of RESTRICT or No ACTION. Consider a simple database of

CREATE TABLE column1
(
first_id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (first_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci";

CREATE TABLE column2
(
second_id int(11) NOT NULL AUTO_INCREMENT,
first_id int(11) REFERENCES column1(first_id) ON DELETE CASCADE,
PRIMARY KEY (second_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci";

If DELETE FROM column1 WHERE first_id='XX' the corresponding row in the column2 (child column) will be deleted
If DELETE FROM column2 WHERE first_id='XX' nothing will be deleted from column1 (parent column)

Right? then, what and how RESTRICT or NO ACTION can change this scenario ?

In MySQL, RESTRICT and NO ACTION are synonyms:

In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT. [src]

Now, you are asking how this affects a DELETE FROM column1 WHERE first_id='XX' if the table is defined like so:
CREATE TABLE `column2` (
 `second_id` int(11) NOT NULL AUTO_INCREMENT,
 `first_id` int(11) NOT NULL,
 PRIMARY KEY (`second_id`),
 KEY `fk_first_id` (`first_id`),
 CONSTRAINT `fk_first_id` FOREIGN KEY (`first_id`) REFERENCES `column1` (`first_id`)
) ENGINE=InnoDB;

Notice there is no ON DELETE CASCADE or ON UPDATE CASCADE clause. The manual specifically states that by leaving this off, you are implying a RESTRICT:

Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause.

So, now when the table is defined like this, a DELETE FROM column1 WHERE first_id='3' will fail if there are any rows in column2 that have a first_id value of 3.
If you had a child table that you needed to do some processing on prior to deleting the values, you might use this to ensure that the parent cannot be deleted until you have cleaned up the child table appropriately. It still offers protection against orphan rows in the child table (orphans are rows that exist in the child table, with no matching reference in the parent table).


Group By Month With Overlapping years I want my query to show all months grouped together, regardless of the year.  For example, I would want this to be the output:

01/13/2015
01/01/2016
02/07/2015
02/01/2016

This is sample DDL - what must I alter so months are all displayed together regardless of year?

CREATE TABLE randomdate(dateofwork timestamp);

INSERT INTO randomdate VALUES('2016-01-01 00:00:00-05');
INSERT INTO randomdate VALUES('2015-01-13 00:00:00-05');
INSERT INTO randomdate VALUES('2016-02-01 00:00:00-05');
INSERT INTO randomdate VALUES('2015-02-07 00:00:00-05');

Select
date_trunc('month',randomdate)
from randomdate
GROUP BY date_trunc('month', randomdate)
ORDER BY date_trunc('month', randomdate) ASC

date_trunc truncates the date to the specified part. You need extract

select extract('month' from dateofwork) from randomdate group by 1 order by 1;

+-------------+
|   date_part |
|-------------|
|           1 |
|           2 |
+-------------+
SELECT 2
Time: 0.005s


Reduce impact on SQL logs Sharepoint and SQL Server issue as below

When we change setting on SharePoint (Get-SPEnterpriseSearchServiceApplication).QueryLogSettings.CleanupDays = 30

we basically tell SharePoint to purge all data from Search_LinksStore that is older than 30 days.

What it actually does, it starts (after we start respective SPTimerJobs) stored procedure UAT_SP_CF_Search_LinksStore.dbo.proc_MSS_QLog_Cleanup and passes number of days as a parameter, here is manual start example:

EXEC UAT_SP_CF_Search_LinksStore.dbo.proc_MSS_QLog_Cleanup 30;
GO

When this stored procedure runs, it goes trough the tables and performs SELECT and DELETE, but due to enormous number of rows even small reduction on DB has huge impact on sql transaction logs (they generate 200+GB for only 10GB od DB reduction).

In this case, it tried to delete older than 30days but it always hangs due to low disk space on log partition (i even expanded to 400GB ...) and it cannot be done this way.

I will need help of SQL guys and any of SharePoint guys.

Is it possible to create another stored procedure (or modify this one) to have chunks of delete in smaller amount of rows, that way we can reduce impact on sql logs (but it will be performed longer, but i think we can sacrifice time for the sake of log size)?

    SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[proc_MSS_QLog_Cleanup](
    @Days int
)
AS
    DECLARE @TooOld datetime
    SET @TooOld = DATEADD(day, -@Days, GETUTCDATE())
    BEGIN TRANSACTION
    DECLARE @OldPageImpressions table (
        pageImpressionId bigint NOT NULL);
    DECLARE @MinId bigint = (SELECT MIN(pageImpressionId) FROM MSSQLogPageImpressionQuery WHERE searchTime >= @TooOld )
    IF (@MinId IS NOT NULL)
    BEGIN
        DELETE FROM
            MSSQLogPageImpressionQuery
        WHERE
            pageImpressionId < @MinId
        DELETE FROM
            MSSQLogPageImpressionResult
        WHERE
            pageImpressionId < @MinId
        DELETE FROM
            MSSQLogPageImpressionBlock
        WHERE
            pageImpressionId < @MinId
        DELETE FROM
            MSSQLogPageImpressionQueryRule
        WHERE
            pageImpressionId < @MinId
    END
    ELSE
    BEGIN
        TRUNCATE TABLE MSSQLogPageImpressionQuery
        TRUNCATE TABLE MSSQLogPageImpressionBlock
        TRUNCATE TABLE MSSQLogPageImpressionResult
        TRUNCATE TABLE MSSQLogPageImpressionQueryRule
        TRUNCATE TABLE MSSQLogResultDocs
        TRUNCATE TABLE MSSQLogPageClick
        TRUNCATE TABLE MSSQLogO14PageClick
    END
    DELETE FROM
        MSSQLogPageImpressionQuery
    OUTPUT
        DELETED.pageImpressionId
    INTO
        @OldPageImpressions
    WHERE
        searchTime < @TooOld
    DELETE FROM
        dbo.MSSQLogPageImpressionResult
    WHERE
        pageImpressionId IN (SELECT pageImpressionId FROM @OldPageImpressions)
    DELETE FROM
        dbo.MSSQLogPageImpressionBlock
    WHERE
        pageImpressionId IN (SELECT pageImpressionId FROM @OldPageImpressions)
    DELETE FROM
        dbo.MSSQLogPageImpressionQueryRule
    WHERE
        pageImpressionId IN (SELECT pageImpressionId FROM @OldPageImpressions)
    DELETE FROM
        dbo.MSSQLogResultDocs
    WHERE
        lastReference < @TooOld
    DELETE FROM
        dbo.MSSQLogPageClick
    WHERE
        clickTime < @TooOld
    DELETE FROM
        MSSQLogO14PageClick
    WHERE
        searchTime < @TooOld
    COMMIT TRANSACTION

GO

Please, try to analyze your backup strategies.

Also, You can analyze your DATABASE MODE (FULL, Bulk-Logged, Simple)
(https://msdn.microsoft.com/en-us/library/ms189275.aspx)

Also, you can check log space with:

DBCC SQLPERF(LOGSPACE);
GO

check this: https://msdn.microsoft.com/en-us/library/ms190674.aspx


Log File over 400gigs...need to bring this database back to life I'm not a dba so I don't understand the admin side of SQL Server too much.  One of our sharepoint instances blew up and our IT staff asked if I could assist bring it back to life.  I found backups for sharepoint which is great.  The thing about sharepoint is it is huge with many many databases.  I have recovered all but one database (Sharepoint_Config).  This database Sharepoint_Config's log file is over 400 gigs large!

Remember this database is currently "disconnected" have you, meaning I do not have access to it via sql management studio, its just a physical ldf file and an mdf file.  We have software that we use that can restore these physical files back to their original location.  So that part is simple, however I do not want to restore an ldf file that is over 400 gigs large.

Is there anything I can do to fix this mess?

It sounds like no one took log backups on that database for a very long time.  Restore the MDF and try:

EXEC sp_attach_single_file_db @dbname='YourDB',
@physname=N'C:\Path\To\YourDb.mdf'
GO

Then set up regular backups for those databases, both full and log backups.  This is an article on log backups that might help.  This is a more general article on SQL Server backups.

Good luck!


Can we do the domain migration for 4 node SQL cluster? Can we do the domain migration for 4 node SQL cluster ?

Microsoft does not supports migrating cluster from one domain to other. Their is support article describing this.

This support article  clearly mentiones that

  Because of an increased dependence on Active Directory Domain Services, Microsoft does not support moving an already installed and configured Windows Server 2008, Windows Server 2008 R2 and Windows Server 2012 failover cluster from one domain to another. You must create a new cluster. Additionally, you must re-cluster highly available applications.

It would be better to rebuild the cluster from scratch in other domain.

In windows server 2016 you can have cluster nodes in 2 different domain


How to install Microsoft SQL Server 2014 Standard Edition on trial? I want to install SQL Server 2014 Standard and Enterprise editions on two separated servers with same hardware in order to test the performances differences running internal set of procedures.

The DBA gave me two ISOs with the following name:

SW_DVD9_SQL_Svr_Standard_Edtn_2014w_SP1_64Bit_English_-2_MLF_X20-29010
SW_DVD9_SQL_Svr_Ent_Core_2014w_SP1_64Bit_English_-2_MLF_X20-28988

claiming they are downloaded from Microsoft , but once I have installed them and run:

SELECT @@VERSION

I get the following for the Enterprise edition:

  Microsoft SQL Server 2014 - 12.0.4213.0 (X64)     Jun  9 2015 12:06:16
    Copyright (c) Microsoft Corporation     Enterprise Evaluation Edition
  (64-bit) on Windows NT 6.3  (Build 9600: )

and the following for the Standard edition:

  Microsoft SQL Server 2014 - 12.0.4213.0 (X64)     Jun  9 2015 12:06:16
    Copyright (c) Microsoft Corporation     Enterprise Evaluation Edition
  (64-bit) on Windows NT 6.3  (Build 9600: )

So, what I am doing wrong? Why, when I am using the Standard iso again Enterprise is istanlled?

I guess this should be SP1 issue, because I have checked that I had successfully install standard edition of SQL Server 2014 without SP1.

Evaluation Edition is always Enterprise Edition. If you want to run comparisons, get your admins to give you Standard Edition rather than Evaluation Edition.

For more proof, check Books Online, which notes that there is an Enterprise Evaluation, but not a Standard:

https://msdn.microsoft.com/en-us/library/ms174396.aspx


Why does SSRS not update a report's description? I created a SSRS 2016 solution and deploy my reports.
Each report has a "DESCRIPTION" property, that gets visible in the report's properties in report manager. There in the report manager, this property can be modified as well.

Unfortunately this description property will NOT BE changed after it was changed in visual studio and re-deployment of the report.

I checked the report server database to exclude any caching issues. There the value of the description column also stays untouched by/after re-deployment:

USE ReportServer;
select [Description], * from dbo.[Catalog] where Path like '/myReports/Demo';

Since this property is part of the report, I expect it to get modified by deployment. Is there anything we can config or do I miss anything? Why does this not work?

Environment:

ReportServer: SQL-Server 2016 Developer Edition
Microsoft Visual Studio Enterprise 2015
    Version 14.0.25424.00 Update 3
    Microsoft .NET Framework
    Version 4.6.01055
    SQL Server Data Tools   14.0.60812.0
    Microsoft SQL Server Data Tools
    SQL Server Reporting Services   13.0.1700.68
    Designer für Microsoft SQL Server Reporting Services
    Version 13.0.1700.68

This is a "known" issue/feature according to Microsoft.  Descriptions, parameter defaults, subscriptions, etc. all fall under report "meta data" and are maintained separately on the Report Manager server.  The only way to get these to change on the Report Manager is to either manipulate them manually or delete the report and upload anew.  The links below deal specifically with this issue in regards to report parameters, but the same is true for descriptions.
Here is an excerpt from a Technet Article regarding parameters

Parameter properties that you set in Report Manager for a published report are generally preserved if you republish the report definition from Report Designer. If the report definition is republished as the same report, and parameter names and data types remain the same, your property settings are retained. If you add or delete parameters in the report definition, or change the data type or name of an existing parameter, you may need to change the parameter properties in the published report.

This is a related Connect issue

Parameter defaults do not get updated when re-deploying existing reports. These either have to be updated manually or the reports deleted and re-deployed. The latter regenerates all report ID's (GUID's) and makes traking usage from the ExecutionLog more difficult.
This is explained here (link) as being by design however I can't envisage parameter defaults and prompts being maintaned by an administrator.
An override mechanism similar to OverwriteDataSources should be added to Reporting Services projects to allow deploymnent from Visual Studio.


Mongo Shard with Negative Average Size? I'm seeing the following behavior from a shard in mongo:

mongos> db.fs.chunks.getShardDistribution()

Shard SH0
 data : 740.29GiB docs : 5003098 chunks : 32949
 estimated data per chunk : 23MiB
 estimated docs per chunk : 151

Shard SH1
 data : 800.67GiB docs : 5413760 chunks : 32948
 estimated data per chunk : 24.88MiB
 estimated docs per chunk : 164

Shard SH2
 data : -188212B docs : 3083 chunks : 37
 estimated data per chunk : -5087B
 estimated docs per chunk : 83

Totals
 data : 1540.96GiB docs : 10419941 chunks : 65934
 Shard SH0 contains 48.04% data, 48.01% docs in cluster, avg obj size on shard : 155KiB
 Shard SH1 contains 51.95% data, 51.95% docs in cluster, avg obj size on shard : 155KiB
 Shard SH2 contains -0.01% data, 0.02% docs in cluster, avg obj size on shard : -115628438B

I'm attempting to drain SH2, but 37 "jumbo" chunks are left. This is a gridfs collections sharded on files_id, n - it's hard to believe these chunks are actually jumbo, but I haven't been able to do anything to split them (I've tried everything mentioned in the other relevant SO questions about splitting jumbo chunks).

Any ideas, any clue why this is showing up like this?

EDIT

sh.status() outputs:
mongos> sh.status()
--- Sharding Status ---
  sharding version: {
    "_id" : 1,
    "minCompatibleVersion" : 5,
    "currentVersion" : 6,
    "clusterId" : ObjectId("546bf1a7c0671b92b546fb90")
}
  shards:
    {  "_id" : "sh0",  "host" : [redacted] }
    {  "_id" : "sh1",  "host" : [redacted] }
    {  "_id" : "sh2",  "host" : [redacted],  "draining" : true }
  balancer:
    Currently enabled:  yes
    Currently running:  no
    Failed balancer rounds in last 5 attempts:  0
    Migration Results for the last 24 hours:
        8 : Success
        1 : Failed with error 'migration already in progress', from syn-sh_1 to syn-sh_0
  databases:
   .....
    {  "_id" : "Files",  "partitioned" : true,  "primary" : "sh0" }
        Files.fs.chunks
            shard key: { "files_id" : 1, "n" : 1 }
            chunks:
                syn-sh_0    32967
                syn-sh_1    32968
                syn-sh_4    37
            too many chunks to print, use verbose if you want to force print

EDIT 2
When attempting to move, Mongo thinks the chunk size is approx 1200 Petabytes

{
    "cause" : {
        "chunkTooBig" : true,
        "estimatedChunkSize" : NumberLong("1284608926708044072"),
        "ok" : 0,
        "errmsg" : "chunk too big to move",
        "$gleStats" : {
            "lastOpTime" : Timestamp(0, 0),
            "electionId" : ObjectId("553034c33e622e6efa8c0a6c")
        }
    },
    "ok" : 0,
    "errmsg" : "move failed"
}

Reboot of affected shards fixed it.

That's kinda mongo in a nutshell - I ended up submitting a bug and that was their engineer's advice, and it happened to work.


Generate Backup list I have the query below that returns all databases and full backup history with the last backup date and device name.  I am trying to modify the query to only return all the databases and just the last backup date and file location.

So now I get:

DatabaseA - 02/25/2014 - \\FileLocation
DatabaseB - 02/25/2014 - \\FileLocation
DatabaseC - 02/25/2014 - \\FileLocation
DatabaseA - 02/24/2014 - \\FileLocation
DatabaseB - 02/24/2014 - \\FileLocation
DatabaseC - 02/24/2014 - \\FileLocation
DatabaseA - 02/23/2014 - \\FileLocation
etc,etc

I would like to get:
            (Most Recent Full - Last File Location)
DatabaseA - 02/25/2014 - \\FileLocation
DatabaseB - 02/24/2014 - \\FileLocation
DatabaseC - 02/25/2014 - \\FileLocation
DatabaseD - 02/25/2014 - \\FileLocation

SELECT bs.database_name AS DatabaseName, bs.backup_finish_date, MAX(bms.physical_device_name) AS FullBackupName
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bms ON bs.media_set_id = bms.media_set_id
INNER JOIN master.dbo.sysdatabases s ON bs.database_name = s.name
WHERE bs.type IN ('D')
AND s.name NOT IN ('master', 'msdb', 'model')
GROUP BY bs.database_name , bs.backup_finish_date
ORDER BY bs.backup_finish_date DESC

It's not a modified version of your query, but I wrote this a while back (backupset is one of my favorite tables) that serves your purpose.

--When was the last full and where is it?
select
    bs.database_name
    ,bs.backup_finish_date
    ,bs.backup_size/1024.0/1024.0 [backup_size_mb]
    ,bs.compressed_backup_size/1024.0/1024.0 [compressed_size_mb]
    ,datediff(ss,backup_start_date,backup_finish_date) [backup_time]
    ,((bs.backup_size/1024.0/1024.0)/(datediff(ss,backup_start_date,backup_finish_date)+1)) [mb_per_second]
    ,1.0-(bs.compressed_backup_size*1.0/bs.backup_size) [compression_ratio]
    ,bm.physical_device_name
from
    msdb.dbo.backupset bs
    join msdb.dbo.backupmediafamily bm on (bs.media_set_id = bm.media_set_id)
    join (select database_name
            ,max(backup_finish_date) last_backup
          from msdb.dbo.backupset
          where type = 'D'
          group by database_name) lb on (bs.database_name = lb.database_name and bs.backup_finish_date = lb.last_backup)
where type = 'D'


Huge Data Migration in Oracle and space concerns I am trying to do Insert append as part of data migration from SchemaA.table1 to SchemaB.table2 in the same Oracle database. Due to refactoring of Table2, there will be some form of data transformation occurring as part of this migration. i.e. I can't just do some kind of partition exchange here.

Due to the huge size of data, this may take anywhere from 10 hours to 30 hours. I don't want to fail the migration at the very end because of insufficient tablespace. Is there a way in Oracle to predetermine the space requirements based on the size of the data in table1 so that I can fail right way asking the user to increase the table space?

Alternatively, is there an efficient way to remove the data from table1 as I move them to table2 ? Assume that user would have already backed up the source data from schema A. This way, I guess I don't need to worry about space issues??

No, you can't pre-fail the process unless it's something you write yourself.  However, you can use Oracle's Resumable Space Allocation to allow your session(s) to suspend for a given amount of time if it runs out of space, extents, or quota.  Then once the space problem's been resolved, the transaction/session automagically keeps working from where it was suspended.


Importing mysqlnd database to MySQL server Importing a mysqlnd (5.0.12) database to MySQL server (5.1.66) causes error? Currently we are performing this task and encountering errors (like below):

  ERROR 1146 (42S02): Table 'xx-xxx-xxx-xxx' doesn't exist

  ERROR 1273 (HY000): Unknown collation: 'utf8mb4_unicode_ci'

  ERROR 1115 (42000): Unknown character set: 'utf8mb4'

The cause of the problems may be that the utf8mb4 character set has been added in Mysql 5.5.3.

You need to upgrade MySQL server or change your data to working with utf without mb4. Here you can find some tips how to do it


Changed database context to 'MyDbName' I am catching all errors on my Sql Server, I got errors:

  Changed database context to 'MyDbName'.
  Changed language setting to us_english.

I haven't found any clear explanation of problem and severity of this error. Is it critical?

This is an informational message for SQL Server stating successful connection. It is one of three that it will usually return. You can find more info here.


What does it mean to "materialize"? While learning about "data layout", I came across the term "materialize", which does not have a clear definition and explanation. What is materialize and what does materialize do?
This question is not related to materialized views, and is not specific to any particular DBMS.
Context
The source that I am learning from: Youtube Video from Prof. Dr. Jens Dittrich
: Mapping Relations to Devices.
At timepoint 9:24 we have this term materialize. He said that is a process from physical page to storage devices.
And an additional question at this time point: Since physical pages should also belong to main memory, if we have a main-memory database, so the data layout only exists in main memory, will there be a materialize process?

Contrary to popular misconceptions, "materializing" has nothing to do with writing anything to disk or a "storage" layer. During the process of executing a query you have two discrete concepts,

Pipelining
Materializing

The basic idea here is that given something like a WHERE clause you can either

Add selectivity in the retrieval,
Add selectivity at the end of the pipeline,

The process is stateless. Ultimately a pipeline gets constructed and somewhere in that pipeline the magic happens (with the general rule being the earlier the better).

Let's look at a sort, how do you sort 1,5,3 in a pipeline?  You can't. That means if the table isn't clustered by that field, your only option is to

Declare a thing "result set"
Finish writing that "result set"
Process it (sort in this case)
Move on

That "thing", more typically a "materialized result set" is a "relation" and it's typically itself modeled like any other table subject to all of the same operations.

Materialization can be a result of the demands of the problem (like sorting), a shortage of resources, or because of planner limitations -- in PostgreSQL a CTE is an optimization fence. That means optimizations on the outside of the CTE can not be pushed down into the CTE. Why? Because at the point the CTE is done: it's results are in a buffer somewhere (or on disk), the step of pipelining is over.

Real World Validation

PostgreSQL, I would urge you to search through the whole codebase here for "materialization". It's used frequently. You can see one of the popular cases in nodeMaterial.c where a call is made to tuplestore_begin_heap to get a place to store a result set
MySQL uses it in the context of semijoin, subquery, aggregate, and window function code. Further they have a hint MATERIALIZATION which is documented as such,

  The optimizer uses materialization to enable more efficient subquery processing. Materialization speeds up query execution by generating a subquery result as a temporary table, normally in memory. The first time MySQL needs the subquery result, it materializes that result into a temporary table. Any subsequent time the result is needed, MySQL refers again to the temporary table. The optimizer may index the table with a hash index to make lookups fast and inexpensive. The index contains unique values to eliminate duplicates and make the table smaller.

The video in question.

I would advising ignoring this section of the video. He defines "materialization" as the step from "physical pages to storage devices". This is not a definition in any practice for that term.

For the purposes of a DBA, "materialization" means what I said above.
For the purposes of chip design and kernel design, there is a difference between Physical Pages and Virtual Pages.

Physical Pages are in hardware and contiguous. A "page" is one unit of CPU-accessible memory (4096 bytes on x86). It's called a "Physical Page" because you often can't access it directly, and it's not exposed to you at any level.
A logical page is one unit of accessible memory in software that the kernel can get access to.

In the x86 platform for instance, paging is turned in in Long Mode (x86_64) and you can't access Physical Memory without going through a "Logical Page." He calls the process of resolution from Logical Pages to Physical Pages "devirtualization". I've never heard of that and likely neither has he simply because it's a function of the microcode on the CPU and that's all proprietary so naming individual processes that it performs is somewhat useless. For the same reason "materialization" seems equally useless, and it's even more confusing. If you know the physical page how could you not know the storage device? If it's physical, it's not abstracted further. What does the logical<->physical mapping mean in this scheme? It's foreign to me anyway, but maybe it's Oracle parlance or something.

Chosen Answer

  In database research the term "materialization" denotes any form of data storage, i.e. any operation that actually sets some bytes on any storage layer eventually. Examples include a deep copy, memory allocation, replication, materialized views (rather than dynamic views), intra-pipeline materializations, but also any form of (partial) copies along the storage hierarchy.

That is untrue in any research or database parlance and I would challenge the statement that all uses of malloc, memcpy or as the author goes on to say fork with (or without) COW as being "Materialization". They may be a "part" of materialization if they otherwise refer to the mechanism I describe above.


How to set MySQL binlog retention in hours? I'm in a situation where the MySQL log partition is being filled in less than a day. I can't disable binlogs, since I need them for replication.

I know I can set log retention but that accepts days, and a search only showed me this https://serverfault.com/questions/179165/can-expire-logs-days-be-less-than-1-day-in-mysql question, which suggests doing it yourself via scripting.

Since that question is almost 8 years old, I was wondering whether it is possible now.

If it matters, I'm using Percona MySQL 5.7

Actually, you can now. Since MySQL 8.0.1, while expire_log_days still works, the canonical way to setup expiration is by changing binlog_expire_logs_seconds, which, as you may guess, has second resolution. The default value since is 8.0.11 is 2592000 (= 30 days), but can be configured as usual on the configuration file to any other period in seconds.
If you cannot use MySQL 8.0 yet, the way to fix this is to use the PURGE BINARY LOGS syntax, with BEFORE (PURGE BINARY LOGS BEFORE now() - INTERVAL 1 HOUR;' and setting the max-binlog_size to an appropiate value (you cannot delete currently-being-written binlog, although you can always force the rotation with FLUSH BINARY LOGS). You can setup an event to do that regularly, or externally, on a cron/programmed task.
Alternatively, forks of MySQL, like Percona, used to (and still does) allow to setup a max binlog size in bytes -by combining max_binlog_size and max_binlog_files-, in addition to the days limit.


Query Relating Rows W/O A Corresponding Value I have a table setup like this:

ID Code      DateActive   DateInactive  statusCode
  15          20090413       null           A
  15            null         20090416       I
  15          20100101       20130101       I
  16          20130515       20151225       I
  16          20151226       20160101       I

I want to pull results that do not possess a status code of "A" in any of the corresponding rows.  For example, in the table above, I would want only the result set from 16, with a single result grouping all of the possible rows.

    Desired Result:
ID Code       StatusCode
  16               I

I only want those that have an I and do not have an A row in my results.

Have a feeling this might be an easy one, but I can't seem to find a way to solve it.

SELECT [ID Code], statusCode
  FROM dbo.Table AS t
  WHERE NOT EXISTS
  (
    SELECT 1 FROM dbo.Table
    WHERE [ID Code] = t.[ID Code]
    AND statusCode = 'A'
  )
  GROUP BY [ID Code], statusCode;


MongoDB aggregation $unwind and 1NF I have a basic rhetorical/theoretical question for people experienced using both MongoDB and RDBMS. Surprised SO has no Data Munging or Data Engineering forum.

In making an aggregation pipeline in Mongo, we use $unwind to explode nested array values so each has its own document so that we may group or do other aggregation.

To me this smells just like getting the data to First Normal Form. The documents tend to look just like records in 1NF at this stage. It seems like that is the goal. I have searched and searched and not found that phrase uttered in any Mongo topics, certainly it was not said in courses I took in MongoDB University. And this would apply to other non-relational data systems as well.

Is 1NF essentially the same thing as the data form necessary for an aggregation step? Is there any data manipulation term for it?

The documents tend to look just like records in 1NF at this stage.

For an array of primitive values the output of $unwind looks similar to First normal form (1NF), but pedantically the $unwind output should be expected to be UNF (Unnormalised form) since:

documents output by $unwind do not have a unique key constraint (although arguably the includeArrayIndex option can be used to provide an explicit row identifier)
arrays support complex data types (nested arrays and documents)

  I have searched and searched and not found that phrase uttered in any Mongo topics, certainly it was not said in courses I took in MongoDB University.

Database normalisation is a strongly RDBMS concept, where you design a model to reduce data redundancy ("How to efficiently store data"). Standard normal forms are expressions of more rigid data relationships and constraints for tabular data.

MongoDB data model design is application-centric ("How to efficiently use data"). Since the decision on whether to normalise or denormalise data is influenced by the usage context, data model discussion generally focuses on data growth and use case patterns rather than highly normalised forms.

  Is 1NF essentially the same thing as the data form necessary for an aggregation step?

1NF (or an $unwind operation) certainly isn't a necessary step for aggregating or transforming arrays. MongoDB has other array expression operators such as $arrayToObject, $filter, and $map which implement different data transformations.

  Is there any data manipulation term for it?

I'm not sure if there are any generally accepted terms, but I think an $unwind operation would typically be described as either "flattening" or "unnesting" arrays.


Summing Column values based on group by criteria So I have this query

    select *, TIMEDIFF(tbltimetracker.tt_endtime, tbltimetracker.tt_begintime)
as hours from tbltimetracker
          LEFT JOIN tblcustomers
on tbltimetracker.customer_id = tblcustomers.customer_id
          LEFT JOIN tblusers
on tbltimetracker.user_id = tblusers.user_id
          WHERE tbltimetracker.owner_id = 38
          LIMIT 0, 50'

My intent is to group these by customer and sum the hours based on that. So full disclosure is I have a client who wants to import their payroll and their payroll company needs the excel sheet in a certain format which is like this but the only difference is, employees (users) hours are totaled by the customers (or locations) that they worked at. So for instance, currently it's:

Payroll Id | User Name | Customer  | Hours |
5C           Tom         S. County   5
10C          Sam         N. County   3
5C           Tom         S. County   4
5C           Tom         N. County   7

But it should but

Payroll Id | User Name | Customer  | Hours |
5C           Tom         S. County   9
10C          Sam         N. County   3
5C           Tom         N. County   7

This is what I've come up with

    select *, SUM(TIMEDIFF(tbltimetracker.tt_endtime, tbltimetracker.tt_begintime))
as hours from (select * from tbltimetracker where tbltimetracker.customer_id = tblcustomers.customer_id)
          LEFT JOIN tblcustomers
on tbltimetracker.customer_id = tblcustomers.customer_id
          LEFT JOIN tblusers
on tbltimetracker.user_id = tblusers.user_id
          WHERE tbltimetracker.owner_id = 38
          LIMIT 0, 50'

But this does not work, I hope it gives you an idea though.

So to get the hours from the two timestamps as they were summed in the group by (note that tt_timeadjust is by default, in minutes) I used

SELECT SQL_CALC_FOUND_ROWS *, ROUND((SUM( TIMESTAMPDIFF(MINUTE, tbltimetracker.tt_begintime, tbltimetracker.tt_endtime ) + tbltimetracker.tt_timeadjust)) / 60, 2) AS hours FROM tbltimetracker LEFT JOIN tblcustomers on tbltimetracker.customer_id = tblcustomers.customer_id LEFT JOIN tblusers on tbltimetracker.user_id = tblusers.user_id WHERE  tbltimetracker.owner_id = ?  GROUP BY tblcustomers.customer_id tblusers.user_id LIMIT 0, 50


Between optimizing a 1-billion rows table and dump-restoring it, which would be faster? Have multiple tables which had more than 1 billion rows. Archived and deleted many of them, now want to recover the disk space.

Have option to either optimize the table, or dump the table and restore it.

For restoration I will be splitting the dumped sql file into multiple files and will run parallel restore upto the point the CPU is not blocking much on DiskIO, to restore at the best possible rate.

Now, from mysql's point of view alone, which would be faster, and why?

I understand that DiskIO will be an important factor, but I want the technical points from mysql's perspective.

NOTE: The disk on which mysql is, its a high performance SSD disk.

OPTIMIZE is probably better because what it does is

CREATE TABLE ...
Copy all the existing rows over
RENAME ...

(Some time during or after step 2, the indexes are rebuilt.)

Dump and reload:

Read the entire table, write to disk
DROP TABLE or TRUNCATE TABLE and CREATE TABLE ...
Read the dump, inserting into the table

This is slower because of the extra I/O for the dump.

If disk space is tight, execute the dump (mysqldum/xtrabackup/etc) and restore (mysql) from a remote machine and have the dump file on that remote machine.  This may be the only way to do the cleanup without running out of disk space.

Even better...

Next time you need to do a big DELETE...

CREATE TABLE new LIKE real
Copy the rows you do not want to delete into new
RENAME TABLE real TO old, new TO real
DROP TABLE old

This 'eliminates' the effort to do the DELETEs.

Deleting big chunks is a popular topic; see my blog on the topic.

Still even better...

If this is a time series, and you are dropping "old" data, PARTITION on the date.  Then DROP PARTITION to jettison the old data 'instantly'.  Details.

Caveat

If you have only 20GB of spare disk, and the remaining data + index in the InnoDB table is more than 20GB, OPTIMIZE will run out of disk space and fail (without doing 'anything').  So will dump+reload unless you can use a different machine for the dump.  (The 'better' solutions need setup; can't do either this time.)

If the table is in ibdata1 (not innodb_file_per_table), no space will be given back to the OS.  It will be available for future growth in InnoDB tables.

Addenda

Their may be a difference is size or speed between the restore and the OPTIMIZE.  The rebuilding of the indexes may be different.

If you are doing any of this to recoup space on disk, start with the smallest table first and work your way up.  Keep in mind that InnoDB makes some attempt at cleaning up holes in its BTrees.  In theory, a well-kept BTree will average 31% empty space, even after lots of churn.  So, that's the most you can expect to recoup from OPTIMIZE (or equivalent).  However, a huge delete leaves free space in addition to the 31%.


why have to write c## for user creation in oracle 12c I have tried to create user accounts in oracle 12c. But it has shown the error:

  ORA-65096: invalid common user or role name.

I have tried the following two user name:

BANNER
-----------------------------------------------------------------------------
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

SQL> CREATE USER A3 IDENTIFIED BY p3;
CREATE USER A3 IDENTIFIED BY p3
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

SQL> CREATE USER testUser IDENTIFIED BY pass;
CREATE USER testUser IDENTIFIED BY pass
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

SQL> CREATE USER c##testUser IDENTIFIED BY pass;

User created.

SQL>

Then I have googled and found a solution that I have to write c## before the user name.

The reason is not clear to me. Can you please clear it to me?

You have a container database and you are in the CDB$ROOT container.

In the root container, you can create only common users.

Local Users in a CDB

  A local user cannot be created in the root.

The name of common users must start with c##.

Common Users in a CDB

  "The name of every user-created common user must begin with the
  characters c## or C##. (Oracle-supplied common user names do not have
  this restriction.)"

For an overview of users in a container database, read Overview of Commonality in a CDB


Retrieving data speed tweaks SQL Server 2005 I have a database in Microsoft SQL Server 2005.

I have table with 3 columns, namely HASHKEY (BIGINT), NOTE_ID (INT) and TIME_OFFSET (INT).

Columns HASHKEY has 19 digit BIGINT values, which can DUPLICATE. But values will be always of 19 digits.

I have application mainly depends on data in this table. Application retrieves data from this table with query like:

select HASHKEY, NOTE_ID, TIME_OFFSET
from TABLE_NAME
where HASHKEY in (<list of around 30000 hashkeys>)

But this query takes around 2 minutes to retrieve data. This is my problem. The application is real time processing application, and need to retrieve data in about 5 seconds. How can I tweak things in server, so time to retrieve data can be decreased?

I have indexed the table by HASHKEY column when I created table, but still retrieving data is taking much time.

Is there any setting which I can do in database to so time can be decreased? I will welcome any type of solution. But I need to solve this. I am not very expert in this.

Also HASHKEY is just random values of 19 digits, no relation with other values.

Result of following query is,

select count(HASHKEY) from TABLE_NAME
go
select count(distinct(HASHKEY)) from TABLE_NAME

Result:

225899932
189200251

Time taken: 2 minutes, 1 second

EDIT

This is script to create table:

USE [fp]
GO
/****** Object:  Table [dbo].[fp_core]    Script Date: 09/13/2013 22:40:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[fp_core](
    [hashkey] [bigint] NOT NULL,
    [note_id] [int] NOT NULL,
    [timeoffset] [int] NOT NULL
) ON [PRIMARY]

I will ask random set of around 30000 hashkeys for data, no any ordering.

When I was having around 20000000 rows in table, query was taking less than 2 seconds, but now retrieval time is increasing.

EDIT

And here is script to create index, only one index in this table.

USE [fp]
GO
/****** Object:  Index [IX_fp_core]    Script Date: 09/13/2013 23:04:24 ******/
CREATE NONCLUSTERED INDEX [IX_fp_core] ON [dbo].[fp_core]
(
    [hashkey] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

Please help me.

Your table is a "heap," which means that it doesn't have a clustered index.  The short version of what that means is that your data isn't laid out on the disk in a logical way.  A heap really isn't an optimal structure for an actively updated table.  Here's an excellent video on heaps.

So, my suggestions--please test them before deploying them in production--are:

BEGIN TRANSACTION

USE fp; ALTER TABLE fp_core ADD ID bigint NOT NULL IDENTITY (1, 1)
GO

USE fp; CREATE CLUSTERED INDEX IX_fp ON fp_core (ID) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

COMMIT

This will take some time to complete AND will be a blocking query, so do it during downtime.

This will give you a clustered index and lay out your table on a completely random sequential number, unfortunately, but since you don't have any non-unique values that might be the best course of action.

I would then include the columns note_id and timeoffset in your nonclustered index on HASHKEY.

BEGIN TRANSACTION

USE [fp]
GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[fp_core]') AND name = N'IX_Agency_TIMESTAMP')
DROP INDEX [IX_fp_core] ON [dbo].[fp_core] WITH ( ONLINE = OFF )
GO

USE [fp]
GO

CREATE NONCLUSTERED INDEX [IX_fp_core] ON [dbo].[fp_core]
(
    [hashkey] ASC
)
INCLUDE ( [note_id],[timeoffset])
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

COMMIT

(Or you could do it through the GUI.  If you right-click on the index and choose properties, there's an included columns pane.)

This will hopefully have the effect of replacing your heap scans with nonclustered index seeks, but since your query has a "where in [30,000 items], it might do a index scan instead.  Either way, it won't be rooting around in the heap, which I expect will be a good thing.


PostGIS: Cutting Holes in Self Geometry I have a table of geometries in PostGIS, some of which overlap. When I display the geometries, this has the unfortunate consequence of blending the two stacked geometries together and producing an unwanted color in my application.

How can I step over all geometries, identify all that have "children", and cut them out of the "parent"? I created a temporary column called newgeom, as to not clobber my real geometry (geom) for testing, and tried, to no avail:

UPDATE zones a SET newgeom = ST_Difference(b.geom, a.geom) FROM zones b WHERE ST_Contains(b.geom, a.geom) AND a.id != b.id;

I was able to achieve a sub-optimal solution with the help of Craig and Alexandros in the above comments.

Identify which polygons are children, and flag them as such:

UPDATE zones a SET parent = b.id FROM zones b WHERE ST_Contains(b.geom, a.geom) AND a.id != b.id;
Perform two queries in TileMill for both parental, and children geometries

Parents:    (SELECT * from zones WHERE parent IS NULL)
Children:(SELECT * from zones WHERE parent IS NOT NULL)
Apply a comp-op to the parental layer only and ensure that the children geometry layer is "above" the parental one in the layers list so that it is draw on top.

Unfortunately, this is still not perfect. Children geometries still cannot have an opacity value, otherwise the parent color will blend, and cause it to take on another color.


Looking for a set-based solution for a rolling 30-day period I have a stored procedure (MS SQL Server 2008 R2) that needs to see if 10 points/sales fall within a 30-day rolling window.  So far, I have a solution that works, but it's not a "proper" solution as it isn't set based.  I use while loops to process what's going on.  Looking at an agent's points, I take the date of the first point, and see if 9 more fall within a 30-day window, if so, I mark them as processed and used and proceed to the next one.  I'm thinking there has to be a "better" solution that is set-based.

To see what I have, I set up a SQL Fiddle.  It shows at the end that we have processed 3 agents and there are 4 incentives to be given.  Is there a better way to do this without using while loops that's more set-based?

SQL Fiddle

I tried to put together an approach that would work on 2008 and is more set-based.  Here is what I came up with.

It did end up being more complex than I had hoped, but it might be an interesting approach for you to benchmark against your current approach on larger data sets.  For what it's worth, this script runs in about 15ms for the provided data set on my machine (vs. 75ms for the original script).

As others have mentioned, there are probably other better approaches if you were able to use window functions in 2012+ or maybe a natively compiled procedure in 2014.  But it can be fun to think about how to do things without the newer features sometimes!

http://sqlfiddle.com/#!3/ad2be/7

-- Assign each point a sequential rank within each agent's history
SELECT p.internalID, ROW_NUMBER() OVER (PARTITION BY internalID ORDER BY date) AS recordRank, date
INTO #orderedPoints
FROM points p
-- Sort the data for efficient lookup of potential incentives
ALTER TABLE #orderedPoints
ADD UNIQUE CLUSTERED (internalId, recordRank)

-- Identify a potential incentive for any point that has 9+ points in the following 30 days
SELECT s.internalId, s.recordRank, ROW_NUMBER() OVER (PARTITION BY s.internalId ORDER BY s.recordRank) AS potentialIncentiveRank
INTO #potentialIncentives
FROM #orderedPoints s
JOIN #orderedPoints e
    ON e.internalId = s.internalId
    AND e.recordRank = s.recordRank + 9
    AND e.date < DATEADD(dd, 30, s.date)
-- Sort the data to allow for efficient retrieval of subsequent incentives
ALTER TABLE #potentialIncentives
ADD UNIQUE CLUSTERED (internalId, recordRank)

-- A table to hold the incentives achieved
CREATE TABLE #incentives (internalId INT NOT NULL, recordRank INT NOT NULL)
-- A couple transient tables to hold the current "fringe" of incentives that were just inserted
CREATE TABLE #newlyProcessedIncentives (internalId INT NOT NULL, recordRank INT NOT NULL)
CREATE TABLE #newlyProcessedIncentives_forFromClause (internalId INT NOT NULL, recordRank INT NOT NULL)

-- Identify the first incentive for each agent
-- Note that TOP clauses and aggregate functions are not allowed in the recursive portion of a CTE
-- If that were allowed, this could serve as the anchor of a recursive CTE and the loop below would be the recursive portion
INSERT INTO #incentives (internalId, recordRank)
OUTPUT inserted.internalId, inserted.recordRank INTO #newlyProcessedIncentives (internalId, recordRank)
SELECT internalId, recordRank
FROM #potentialIncentives
WHERE potentialIncentiveRank = 1

-- Identify the next incentive for each agent, stopping when no further incentives are identified
WHILE EXISTS (SELECT TOP 1 * FROM #newlyProcessedIncentives)
BEGIN
    -- Transfer the most recently identified incentives, so that we can truncate the table to capture the next iteration of incentives
    TRUNCATE TABLE #newlyProcessedIncentives_forFromClause
    INSERT INTO #newlyProcessedIncentives_forFromClause (internalId, recordRank) SELECT internalId, recordRank FROM #newlyProcessedIncentives
    TRUNCATE TABLE #newlyProcessedIncentives

    -- Identify the next incentive for each agent
    INSERT INTO #incentives (internalId, recordRank)
    OUTPUT inserted.internalId, inserted.recordRank INTO #newlyProcessedIncentives (internalId, recordRank)
    SELECT nextIncentive.internalId, nextIncentive.recordRank
    FROM #newlyProcessedIncentives_forFromClause f
    CROSS APPLY (
        SELECT TOP 1 p.*
        FROM #potentialIncentives p
        WHERE p.internalId = f.internalId
            AND p.recordRank >= f.recordRank + 10 -- A new incentive can only start after all 10 points from the previous incentive
        ORDER BY p.recordRank
    ) nextIncentive
END

-- Present the final results in the same format as the original implementation
SELECT a.internalId, a.points, i.incentives
FROM (  -- Tabulate points
    SELECT internalId, MAX(recordRank) AS points
    FROM #orderedPoints
    GROUP BY internalId
) a
JOIN (  -- Tabulate incentives achieved
    SELECT internalId, COUNT(*) AS incentives
    FROM #incentives
    GROUP BY internalId
) i
    ON i.internalId = a.internalId


How to view a list of databases under server in SQL Anywhere like we do in SQL Server I am very new to SQL Anywhere. I have been working with SQL Server for a long time.

In SQL Server, if we need to see the list of database under server, we can see that under Server Explorer.

How can I do same in SQL Anywhere?

I have just installed SQL Anywhere 16 and have no idea how to find Server name etc.

You have to create a database and then start the database server.

On installation of SQL Anywhere it does not automatically setup a database server/service.

Once it is running, you can connect as DBA to it with SQL Central or other tools.