Querying SQL Server extended properties for tables/views and its columns I need to get metadata information about tables/views and its columns in SQL Server. When I run the query separatelly for tables/views (sys.tables) or columns (sys.columns) I can get the extended properties values, but when I join sys.tables and sys.columns something doesn't work properly.

Here is what I've done so far, the missing information is the value for the table's extended properties and column name.

Searching the web I wasn't able to find information about it, could somebody help me with this task?

select
    case
        when o.type_desc = 'user_table' and ep.minor_id = 0 then 'Table'
        when o.type_desc = 'user_table' and ep.minor_id > 0 then concat('Column from ', o.name)
        when o.type_desc = 'view' and ep.minor_id = 0 then 'View'
        when o.type_desc = 'view' and ep.minor_id > 0 then 'View'
        when o.type_desc = 'sql_stored_procedure' and ep.minor_id = 0 then 'Stored procedure'
        when o.type_desc = 'sql_trigger' and ep.minor_id = 0 then 'Trigger'
        else '---'
    end as object_type,
    o.name as object_name,
    ep.value as object_descripion,
    o.type,
    o.type_desc
from sys.objects as o
inner join sys.extended_properties as ep on o.object_id = ep.major_id --and ep.minor_id = 0
where len(convert(varchar, ep.value)) > 1 and o.name = 'foo'
order by o.type_desc, o.name

The desired result is something like:

object_type                 object_name         object_descripion
Table                       foo                 Table description.
Column from foo             column_1            column_1 description.
Column from foo             column_2            column_2 description.

If you wish to return Column level Extended Properties for all columns in the database, use this query:

SELECT
    ObjectType = o.type_desc,
    SchemaName = SCHEMA_NAME(o.schema_id),
    ObjectName = o.name,
    ColumnName = clmns.name,
    ExtendedPropertyName = p.name,
    ExtendedPropertyValue = CAST(p.value AS sql_variant)

FROM sys.objects AS o
INNER JOIN sys.all_columns AS clmns
    ON clmns.object_id = o.object_id
INNER JOIN sys.extended_properties AS p
    ON p.major_id = o.object_id
    AND p.minor_id = clmns.column_id
    AND p.class = 1

WHERE o.type IN ('U','V') -- User Tables and Views

Query was taken (and modified) from Working with SQL Server Extended Properties.


Postgres Windows post-install "The database cluster initialization failed" I've seen many solutions and have been working on this issue for hours now.

After installation the post-installation script fails with the message above.

This is a development installation of EnterpriseDB On Windows 10 Pro (German Edition with English language pack).

No user created
No service created
No initialization happened

What I read about and tried without success (I hope I don't forget anything, as far as I remember these are all (at least most) solutions I tried posts from up to 10 years ago):

Checked whole file system privileges up to root of C:\, I have no other drive - this is a dev laptop.
Installed different locales because I read there could be issues. I tried "default", "C", "US English"
COMSPEC and other ENVs are correct, no trailing semicolon or other disturbances.
postgres password is simple

The installation works perfectly on a different laptop.

What I observed:

This issue is caused by a failing objShell.Run call in the VBS script.

The call is simply not executed. No error message. It "looks like" objShell.Run is called, seems to fail somehow and the DoCmd function where it is called returns. I don't even get a return value from objShell.Run, it just jumps out of DoCmd, thats it.

The written batch file exists. The path variables in the VBS are correct. The log file created inside the batch file is not created therefore I think it is not called at all. But it is created when I prevent the batch file from being deleted and calling it manually from command line. I prefixed the objShell.Run with "cmd /c " which caused it to run "more" but kept on getting other errors.

Unfortunately after several reinstalls, tries, reboots and so on I don't have the logs anymore. But if it is required for problem solving I'd be happy to provide it tomorrow.

I need to say I am not familiar with VBS but that is how far I was able to debug it. I also tried /X /D but I think i need deprecated tools for debugging, do I?

I spent hours with this problem and I'll leave it for today but I'm in desperate need for help.

Thanks.

(edit 1) Added which binary distribution

There are two Windows binary packages available for download from the PostgreSQL site: one by BigSQL and one by EDB.

You don't tell us which one you tried, but my advice is to give up on the one that you are using and try the other one.


Which MySQL storage engine is the most similar to ARCHIVE when that is not available? I have a table that contains a log of events that is written to often and read very infrequently by comparison.  I would love to use the ARCHIVE engine, because it is basically designed for exactly this.  However, this engine is not available on my hosting provider.

The only (useful) options available are MyISAM, InnoDB, and CSV.  I am pretty sure I should not use CSV for this option, so let's discount that one.

What I'm looking for then is the engine that provides the best alternative, keeping in mind the following:

Good compression would be very nice.  A lot of the data may be very similar between rows (as is the case with log messages)
I don't need a fast look up.  Indexes aren't really necessary.  Again, I'm looking to save as much space as possible.
There needs to be support for lots of rows.  Hence the need for compression and small footprint.
Rows do have a foreign key to another table, but it is not necessary to retain the relationship.  Each log entry has a name associated with it that is stored in the other table, which doesn't change.

So based on that, which is better, InnoDB, or MyISAM.  I know that the MyISAM engine installed on this server only supports 2^32 rows.  If InnoDB has similar support for compression and supports more rows, that would be better (but I don't know if that is the case).

InnoDB supports compression as of MySQL 5.1 (with InnoDB Plugin), or natively in MySQL 5.5. The level of compression really depends on your table content, but I see 75% reduction in size as common.

Reads and writes to compressed tables involve more CPU. InnoDB will support your foreign keys as well as any indexes.

I would say it is a fairly good out-of-the-box solution for your problem.

MyISAM only supports compression on a read-only mode.

Or you might find an altogether NoSQL solution to do the job well for you, given that you don't really need indexes.

I'm a relational geek, so I would first go for InnoDB. Just:

ALTER TABLE my_table ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;

KEY_BLOCK_SIZE can be 1,4,8,16; Lower numbers will give you better compression. My experience shows that 4 gives good compression. 1 rarely improves much, and consumes a lot more CPU.


Having Problems Installing 64-bit MySQL 5.5.8 x86 on OS X 10.6.6 I'm really new to databases and SQL in general, previously having installed MySQL only to hand it over completely to some CMS or other. Now, I want to get serious about adding SQL and MySQL or PostgreSQL to my skills.

And so, perhaps bearing this in mind, MySQL no longer installs successfully. I'm using the DMG archive for 5.5.8, and everything seems to install just fine. The installers for both MySQL and the startup item give me big green checkmarks, and the preference pane works just fine, in and of itself.

But the MySQL daemon can't seem to come up. If I try to launch MySQL via the preference pane, this gets logged to the console:

2/6/11  3:01:11 AM  1269    System Preferences  about to perform command
2/6/11  3:01:11 AM  1269    [0x0-0x65065].com.apple.systempreferences   /usr/local/mysql/support-files/mysql.server: line 256: my_print_defaults: command not found
2/6/11  3:01:11 AM  1269    System Preferences  status= 0

$ mysqld spits out:

110206  3:04:29 [Warning] Can't create test file /usr/local/mysql-5.5.8-osx10.6-x86/data/Atlas.lower-test
110206  3:04:29 [Warning] Can't create test file /usr/local/mysql-5.5.8-osx10.6-x86/data/Atlas.lower-test
110206  3:04:29 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
110206  3:04:29 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
110206  3:04:29  InnoDB: Initializing buffer pool, size = 128.0M
110206  3:04:29  InnoDB: Completed initialization of buffer pool
110206  3:04:29  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.

Let me know if any other information is needed. And thanks for your help!

Edit:
MySQL is installed in lrwxr-xr-x   _mysql  wheel  /usr/local/mysql -> mysql-5.5.8-osx10.6-x86.

Fixed it. Listening to /usr/local/mysql/support-files/mysql.server: line 256: my_print_defaults: command not found, I took a peek inside. Turns out this is the script that's called when the startup item or the preference pane want to start or stop MySQL. Unfortunately, it doesn't seem to work out of the box anymore.

The comments near the top of the script seem to know this, and give you a list of options to make the script work. I took the easiest and added the installation path to the basedir variable assignment.


“using stale statistics instead of current ones because stats collector is not responding” My postgresql 9.5 logs shows every minute the message:

  using stale statistics instead of current ones because stats collector is not responding

Except a post on serverfault which is not helpfull and seems related to standby databases setup (which is not my case), I don't find anything to solve this.

What's the meaning of this? How can I solve that?

Additional information based on Greda's reply:

I have 4 CPU, and in routine less than 5% of 1 CPU is used
I have 32Gb RAM, and in routine ~ 500Mb are used
This is not a VM
This runs Ubuntu 14.04.3 LTS/Linux 3.13.0 SMB x86/64

I have also tried to tweak the postgresql.conf parameters as follows

shared_buffers: 1024MB
work_mem: 10MB
maintenance_work_mem: 1024MB
effective_cache_size: 4GB

The error message is still there.

P.S: I fully understand that's not a severe error, but it floods my logs, and I have the fear to not see important messages...

Some additional observation:

VACUUM FULL VERBOSE ANALYZE doesn't helps
I have a never ending proccess connected to the database. When this is running, the VACUUM FULL VERBOSE ANALYZE seems blocked.

Can that never ending process be the root causes of my problem? In that case, since I need to have that proccess running, do I have to tweak something on the server?

Edit:

I had a never ending process connected to the db. I've tried to stop it, and it doesn't helps.

My postgresql.conf file is available there

To temporarily alleviate the problem until you find a solution, have you tried sending a SIGHUP to the server when it occurs?

I have a similar problem in a fairly different situation: the database runs on a windows desktop PC together with Apache and proprietary machine control software. My case fits in the "slow or overloaded" category. However, I see this happening even at night while the machine is idle. I've been monitoring many system parameters with PRTG to try to find a link with a specific activity, but I can only see the consequences, not the cause.

I don't see this as a simple warning because, once it starts, queries that normally take 100ms will start taking 10 seconds (but still leave other queries unaffected). Also, it stays that way until I send a SIGHUP (pg_ctl.exe reload ...), which I think is weird since the process should resume once it is no longer starved.

P.S. please don't hail me about the windows desktop part: I know, and ... circumstances.


Stateful views in SQL Server Actually I got stuck while searching a materialized view approach in SQL Server.  My problem was that I have two databases and I have created some views whose functionality is to read data from one DB to another.

For instance, a view in DB "A" is picking up the data from DB "B".  It worked perfectly, but suddenly today I got Null pointer exception.  When I traced the problem, I found that My DB "B" has crashed.  I searched and found that in Oracle, there is support of this problem by Materialized views whose functionality is to restore the old view state in case of crashing of one DB.  But I didn't find anything like this in SQL Server.

I am really stuck in this issue.  Any help would be greatly appreciated.

You could set up transactional replication from your main server to the server on which the views are materialized, and then, use a non materialized or materialized view based on their independent merits

More info:

You have server A and Server B, with A hosting DB A and B Hosting DB B

Now, you can configure transactional replication between A and B in such a way that Server A will have 2 Databases: A and B_Repl , similarly B will have B and A_Repl

The REPL databases are essentially an updated copy of the source databases. You can find many guides on how to configure replication on SQL Server

Once you have done that, you can create views on server A, and when you need to refer to database B, you refer to B_Repl instead. Similarly for server B


Cross-database queries are slow I have two databases/catalogs on the same SQL server and an SQL-user with access to both of them.

Collation on Database01 is Danish_Norwegian_CI_AS

Collation on Database02 is SQL_Latin1_General_CP1_CI_AS

I need to query two views on "Database02". The queries are basically

SELECT * FROM View01 -- (resulting in 43 rows)
SELECT * FROM View02 -- (resulting in 2000 rows)

The two views are rather complex (to say the least) and I do not have access to change them.

If I log onto the SQL server and specify "Database02" as "default catalog", the queries respond in "0.001" and 30 seconds, respectively.

However if I log onto the SQL Server and specify "Database01" as "default catalog" the first query executes in 30 seconds whereas the second query times out after 600 seconds.

The queries executed on Database01 will be something like

SELECT * FROM Database02.View01
SELECT * FROM Database02.View02

Can anyone please explain this behavior? I am not trying to do any joins between the databases, so I would not expect the diffent collations to be an issue.

Slow query: https://www.brentozar.com/pastetheplan/?id=BJgyIUhrG

Fast query: https://www.brentozar.com/pastetheplan/?id=S1-BUIhSG

PARAMETERIZATION is "Simple" on both databases.

I looked at your plans and found the cause of this difference:

you get a slow plan in the database with compatibility level 100 (see the plan: CardinalityEstimationModelVersion="70" i.e. the old cardinality estimator is used).

Your good plan is generated using new cardinality estimator of SQL Server 2014: CardinalityEstimationModelVersion="120"

You should update your question with the view text to find out the exact predicate where estimation goes wrong, but in your slow plan cardinality there is underestimation of the join with OMInternalOrganization so NL was chosen while in the fast plan you see Hash Join for this table, starting with this join slow plan shows many other Nested Loops while Hash Join is better and it's used in fast plan.

I don't know for what reason that database was left in compatibility level 100, maybe other queries perform better with old cardinality estimator, so instead of global changing compatibility level to 120 for that database I suggest you to use local query option: OPTION (QUERYTRACEON 2312) to forse your query to use new cardinality estimator even in that "old-style" database.

More on cardinality estimator here: Cardinality Estimation (SQL Server)

And here: Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator


How comes that login with no rights cam give himself db_creator? I just created a new login using the GUI, which generates this script:

USE [master]
GO
CREATE LOGIN [tester] WITH PASSWORD=N'tester', DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
use [master]
GO
GRANT CREATE ANY DATABASE TO [tester]
GO

This login was granted to a person who wanted to make a new database. I didn't want to give him db_creator rights, so I used the code above. However, after he installed his application (in which the cration of a database is included), I check the login again, and now it has the db_creator server role! Somehow he has granted this to himself, and theoretically he could delete my databases now!  I've tested this 3 times to make sure what this is indeed what is happening. I've made a trigger to prevent the deletion of databases, which he can't access, but I'm still worried.

How is this even possible and how can I prevent it?

Is the other person a local admin on the server? You need to look at the simplest explanation, which is he either knows the SA password, or he reset the password to the SA account, which a local admin at the server level can do.

Dbatools even has a cmdlet for it. If neither of those things is true, then he knows your password. If you keep your passwords on the same instance in a  "password table" encrypted with the default encryption it is relatively easy to retrieve the clear text using any one of the 100s of programs that can do it. Apex makes one that you can download for free.

The simplest explanation isn't you have a magical SQL server that lets users with no rights grant themselves high-level rights. The simpler explanation is he figured out your password or the SA password somehow.


Trigger to prevent column value from A to B, and from B to A I'm trying to create a trigger that prevents a column to be updated as follows:
If column value is "A", it cannot be updated to "B",
and if column value is "B", it cannot be updated to "A".
CREATE OR REPLACE FUNCTION cancelled_order()
    RETURNS trigger
    LANGUAGE plpgsql
AS $function$BEGIN
    IF OLD.status = 'Cancelled' THEN NEW.status != 'Accomplished';
    END IF;
    IF OLD.status = 'Accomplished' THEN NEW.status != 'Cancelled';
    END IF;
    RETURN NEW;
END;
$function$;

CREATE TRIGGER No_cancelled_acomplished
    BEFORE UPDATE OF status
    ON clinical.tb_orders
    EXECUTE PROCEDURE cancelled_order();

The error that comes out is:

Sintaxis error: !=

Any idea, why?

Based on what you want to accomplish logically, shouldn't:
IF OLD.status = 'Cancelled' THEN NEW.status != 'Accomplished';
END IF;
IF OLD.status = 'Accomplished' THEN NEW.status != 'Cancelled';
END IF;

actually say something like this?
IF OLD.status = 'Cancelled' AND NEW.status = 'Accomplished' THEN RETURN OLD; -- Set A = A when trying to set it to B
END IF;
IF OLD.status = 'Accomplished' AND NEW.status = 'Cancelled' THEN RETURN OLD; -- Set B = B when trying to set it to A
END IF;

My syntax might be slightly off for PostgreSQL, but my point is isn't this the kind of logical expression you're looking for? (You shouldn't be doing a boolean comparison in the THEN clause, which I think is why it's mad about the !=.)


Cannot insert record in remote database - Linked server I want to insert record into a table in my remote database table.

Ooo, bad news - this means you're using a trigger:

from ZTable Zh
inner join inserted

If you're trying to work in a trigger, that means there are other changes to other tables in your transaction. The "inserted" table you're using means that you're working with inserted records from another table.

This means you're trying to do a distributed transaction: committing multiple changes on different servers at the same time. This is a recipe for slow performance.

Take a step back and consider the business problem you're trying to solve. For example, if you're trying to log inserts, consider logging on the same table rather than trying to coordinate transactions across multiple SQL Servers. Or, consider using a built-in feature like Change Data Capture or Change Tracking.

There's a chance, depending on your SQL Server version, that it's fixed by this SQL Server 2005 issue, but even if it is - I'd recommend a different approach than triggers that call linked servers.


Why is table variable forcing an index scan while temp table uses seek and bookmark lookup? I am trying to understand why using a table variable is preventing the optimizer from using an index seek and then bookmark lookup versus an index scan.

Populating the table:

CREATE TABLE dbo.Test
(
    RowKey INT NOT NULL PRIMARY KEY,
    SecondColumn CHAR(1) NOT NULL DEFAULT 'x',
    ForeignKey INT NOT NULL
)

INSERT dbo.Test
(
    RowKey,
    ForeignKey
)
SELECT TOP 1000000
    ROW_NUMBER() OVER (ORDER BY (SELECT 0)),
    ABS(CHECKSUM(NEWID()) % 10)
FROM sys.all_objects s1
CROSS JOIN sys.all_objects s2

CREATE INDEX ix_Test_1 ON dbo.Test (ForeignKey)

Populate a table variable with a single record and attempt to lookup the primary key and the second column by searching on the foreign key column:

DECLARE @Keys TABLE (RowKey INT NOT NULL)

INSERT @Keys (RowKey) VALUES (10)

SELECT
    t.RowKey,
    t.SecondColumn
FROM
    dbo.Test t
INNER JOIN
    @Keys k
ON
    t.ForeignKey = k.RowKey

Below is the execution plan:

Now the same query using a temp table instead:

CREATE TABLE #Keys (RowKey INT NOT NULL)

INSERT #Keys (RowKey) VALUES (10)

SELECT
    t.RowKey,
    t.SecondColumn
FROM
    dbo.Test t
INNER JOIN
    #Keys k
ON
    t.ForeignKey = k.RowKey

This query plan uses a seek and bookmark lookup:

Why the optimizer is willing do the bookmark lookup with the temp table, but not the table variable?

The table variable is used in this example to represent data coming through a user-defined table type in a stored procedure.

I realize the index seek might not be appropriate if the foreign key value occurred hundreds of thousands of times. In that case, a scan would probably be a better choice. For the scenario I created, there was no row with a value of 10. I still think the behavior is interesting and would like to know if there is a reason for it.

SQL Fiddle

Adding OPTION (RECOMPILE) did not change the behavior. The UDDT has a primary key.

@@VERSION is SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64) (Build 7601: Service Pack 1) (Hypervisor)

The reason for the behavior is that SQL Server can't determine how many rows will match to ForeignKey, since there is no index with RowKey as the leading column (it can deduce this from statistics on the #temp table, but those don't exist for table variables/UDTTs), so it makes an estimate of 100,000 rows, which is better handled with a scan than a seek+lookup. By the time SQL Server realizes there is only one row, it's too late.

You might be able to construct your UDTT differently; in more modern versions of SQL Server you can create secondary indexes on table variables, but this syntax is not available in 2008 R2.

BTW you can get the seek behavior (at least in my limited trials) if you try to avoid the bitmap/probe by hinting a nested loops join:

DECLARE @Keys TABLE (RowKey INT PRIMARY KEY); -- can't hurt

INSERT @Keys (RowKey) VALUES (10);

SELECT
     t.RowKey
    ,t.SecondColumn
FROM
    dbo.Test t
INNER JOIN
    @Keys k
ON
    t.ForeignKey = k.RowKey
    OPTION (LOOP JOIN);

I learned this trick from Paul White several years ago. Of course, you should be careful about putting any kind of join hints in production code - this can fail if people make changes to the underlying objects and that specific type of join is no longer possible or no longer most optimal.

For more complex queries, and when you move to SQL Server 2012 or above, it's possible that trace flag 2453 could help. That flag didn't help with this simple join, though. And the same disclaimers would apply - this is just an alternative thing you shouldn't generally do without a ton of documentation and rigorous regression testing procedures in place.

Also, Service Pack 1 is long out of support, you should get on Service Pack 3 + MS15-058.


MEMORYCLERK SQLBUFFERPOOL SQL Buffer pool is consuming more memory in sql server 2017. Is there any way to reduce it??
or How can we find what is consuming more? We are receiving alert for low buffer cache hit ratio.
MEMORYCLERK_SQLBUFFERPOOL    90365.62 MB
One particular database alone consuming 87GB buffer pool. Total memory allocated to this instance is 100GB

SQL Buffer pool is consuming more memory in sql server 2017.

That is by design.

The default memory management behavior of the SQL Server Database
Engine is to acquire as much memory as it needs without creating a
memory shortage on the system. The SQL Server Database Engine does
this by using the Memory Notification APIs in Microsoft Windows.

Memory Management Architecture Guide - Dynamic Memory Management
The thinking is that it's better for SQL Server to use the memory to cache data than to leave it unused.

Is there any way to reduce it??

You can set MAX SERVER MEMORY to constrain it if you want.


How do I change the MySQL data folder path in a Mac? How do I change the MySQL data folder path in a Mac?

I am running OS X 10.10 & MySQL 5.6
In Windows I can edit datadir in my.ini
For Mac, mysqld does not have parameter for --defaults-file=

First shutdown mysql

mysqladmin -uroot -p shutdown

Then, launch mysqld by hand

mysqld --defaults-file=...

or if MySQL is running with all defaults and you are just changing datadir, do this

mysqld --datadir=/path/to/mysql/data

Here is a StackOverflow question (MySQL 'my.cnf' location?) with multiple suggestions on where my.cnf might be for Mac OS when installed .


Database design for dentists I have a homework, where I should create a database for a dental clinic using MySQL Workbench. This database will be used later for a web app for dentists.

Here is a picture of the database design:

Login Table:

Contain login information for dentists (it may be used by multiple dentists online)

The id_logged column is a foreign key of the id of logged in dentists, and it should be in all tables. (To let dentists see only their own patient info and details).

The patient_id column is a foreign key too. And it is taken from patient table and used as foreign key in med_history that represents the general medical information of the patients, patient_info that represents the general information, and the reason of visit and what tasks to do, and in the appointment table.

I have 2 questions:

1- Is there more than that information to add for a dentist database?

2- Is this database correct? Or are there errors?

A few things spring to mind.

The patient_info table should be called patient. The (my) patient table should contain patient_name.
Every table should have a Primary Key called table_name_id and not just id as you have (makes debugging difficult - which "id" is causing the problem + problems of ambiguity of column names).
The medical_history table should hang off patient.
The table you call patients should be called appointment.
You vary your table names between singular and plural. Pick one and stick to it. I recommend singular.
These are just my first impressions.
You could also Google things like "Open source medical/dental practice software" and see how they do things in their schemas. Then you might be able to adapt them to your needs. That's the beauty of Open Source software.

p.s. welcome to the forum and good luck with your project! :-)

[Edit in response to OP's request for clarification]

Delete you patients table and replace it with the appointment table.

Your appointment table will have appointment_id, patient_id, dentist_id, date and time.

As regards your dentist friend, that's great that you can run ideas by them, but check out the Open Source s/ware nonetheless.

Another point, I don't know if MySQL is a requirement or not, but if not, I would change to PostgreSQL. Far better for CHECK CONSRINTS - i.e. no overlapping appointments &c.


Trigger reading from two view tables and write on a different table takes more than 12 seconds, what might be the issue here? Trigger reading from two view tables and write on a different table takes more than 12 seconds, what might be the issue here?

In my case, I am using a trigger to read from two view tables (Decisions2 and Con_Decisions_Groupby), each table contains more than 20000 records, and only I want the trigger to work with the updated values and if a new record is added to the view table, and not to go through all the records in order to hide the delay.
This is my trigger: (I am not sure if it is written correctly to cover my case)

USE [W1]
GO
/****** Object:  Trigger [azteca].[trg_WOcommittee_02]    Script Date: 6/5/2018 10:23:41 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [azteca].[trg_WOcommittee_02]
   ON [azteca].[WORKORDER]
   AFTER UPDATE, insert
AS
   UPDATE [azteca].[REQUEST]

SET [azteca].[REQUEST].[TEXT11] = [dbo].[Decisions2].[L1_Desecion],
    [azteca].[REQUEST].[TEXT12] = [dbo].[Decisions2].[L2_Desecion],
    [azteca].[REQUEST].[TEXT13] = [dbo].[Decisions2].[R1_Desecion],
    [azteca].[REQUEST].[TEXT14] = [dbo].[Decisions2].[R2_Desecion],
     [azteca].[REQUEST].[TEXT4] = [dbo].[Decisions2].[HPC1_Desecion],
    [azteca].[REQUEST].[TEXT15] = [dbo].[Decisions2].[SPC2_Desecion],
    [azteca].[REQUEST].[TEXT5] = [dbo].[Decisions2].[Drafting],
    [azteca].[REQUEST].[TEXT2] = [dbo].[Decisions2].[Auditing_Desecion],

        [azteca].[REQUEST].[TEXT20]= [dbo].[Con_Decisions_Groupby].[TEXT20]

FROM [azteca].[REQUEST]  FULL JOIN [dbo].[Decisions2] ON [azteca].[REQUEST].[REQUESTID] = [dbo].[Decisions2].[REQUESTID]
                       Full join [dbo].[Con_Decisions_Groupby] on [azteca].[REQUEST].REQUESTID =  [dbo].[Con_Decisions_Groupby].REQUESTID

Kindly find the attached screen shot which shows all the story:

Sorry for the confusion, the view table is reading from the azteca.workorder table and when adding the trigger to the view table, the trigger is not working,  please find the code of  the view tables:
[dbo].[Decisions2]>>>

SELECT        REQUESTID, Communities, ObjID, [L1] AS [L1_Desecion], [L2] AS [L2_Desecion], [R1] AS [R1_Desecion], [R2] AS [R2_Desecion], [HPC1] AS [HPC1_Desecion], [HPC2] AS [SPC2_Desecion], [Drafting] AS [Drafting],
                     [Auditing] AS [Auditing_Desecion], Attachments, PROBLEMSID

FROM            (SELECT        TEXT1, REQUESTID, Communities, ObjID, Attachments, COMMENTS, PROBLEMSID
                          FROM            dbo.RequestToWorkorder) src PIVOT (max(TEXT1) FOR COMMENTS IN ([L1], [L2], [R1], [R2], [HPC1], [HPC2], [Drafting], [Auditing])) piv;

[dbo].[Con_Decisions_Groupby]>>>

SELECT        MAX(REQUESTID) AS REQUESTID, TEXT20

FROM            dbo.Con_Decisions
GROUP BY REQUESTID, TEXT20


Create a materialized view on commit with PIVOT function I'm trying to create a materialized view with on commit refresh while using a PIVOT on it's select.

I have the following tables:

Events: represents an event that the system process. It's like a representation of a job.

Columns: Id, Type (number)

EventsMessages: messages of the event. 1:N (1 event can have N messages). It has a type column defining its message type: critical, alert, notification, success.

Columns: Id, EventId (FK), Message (text)

EventContainers: containers that have events, so other entities have containers and not a list of event. N:N (1 container han have N event; 1 event is on N containers), the relationship between them is named as EventContainersRelation table.

Columns for the relation table: EventId (FK), ContainerId (FK). Table EventContainers is not needed in this example.

The code is like this:

CREATE MATERIALIZED VIEW test_mv
BUILD IMMEDIATE
REFRESH FORCE
ON COMMIT
AS
SELECT DISTINCT *
FROM
(
    SELECT EC.ContainerId AS Id, EM.Type
    FROM EventMessages EM
    INNER JOIN Events E ON E.Id = EM.EventId
    INNER JOIN EventContainersRelation EC ON EC.EventId = E.Id
    WHERE E.Estado IN (1, 4)
)
PIVOT
(
    COUNT(Type)
    for tipo in (1 AS CriticalMsgs, 2 AS AlertMsgs, 4 AS NotificationMsgs, 8 AS SuccessMsgs)
)

When I run this, I get the following error:

  ORA-12054: não é possível definir o atributo de atualização ON COMMIT
  para a view materializada
  12054. 00000 -  "cannot set the ON COMMIT refresh attribute for the materialized view"
  *Cause:    The materialized view did not satisfy conditions for refresh at
             commit time.
  *Action:   Specify only valid options.

The same code works if I use ON DEMAND instead of ON COMMIT.

I've already checked the restrictions on materialized views on https://docs.oracle.com/cd/B28359_01/server.111/b28313/basicmv.htm and I'm not sure what I'm doing wrong.
Is there anyway to see a more detailed message?
Is there something I'm doing wrong?

Considering I really can't use ON COMMIT here, so while using ON DEMAND, is it a bad thing to make it refreshes everytime it gets inserted, updated or deleted manually in my procedures? (Note that is the almost the same as ON COMMIT, but manually).

Is there another recommended way to achieve this?

To use on commit refreshes, an MV must also be fast refreshable.

If you're struggling to figure out why you can't set certain refresh properties for an MV, it's best to:

Create it refresh complete on demand
Run dbms_mview.explain_mview on it to see what the issues are

Create the mv_capabilities_table before doing this to store the output from the explain procedure.

Do this and you'll find:

create table t (
  c1 int
);

create materialized view log on t
  with rowid, sequence ( c1 ) including new values;

insert into t values ( 1 );
insert into t values ( 2 );
commit;

create materialized view mv
refresh complete on demand
as
  select one, two
  from   t
  pivot (
    count(*) for c1 in ( 1 one, 2 two )
  );

create table mv_capabilities_table (
  statement_id      varchar(30) ,
  mvowner           varchar(30) ,
  mvname            varchar(30) ,
  capability_name   varchar(30) ,
  possible          character(1) ,
  related_text      varchar(2000) ,
  related_num       number ,
  msgno             integer ,
  msgtxt            varchar(2000) ,
  seq               number
) ;

exec dbms_mview.explain_mview('mv');

select capability_name, possible, msgtxt
from   mv_capabilities_table
where  capability_name like 'REFRESH%' ;

CAPABILITY_NAME                 POSSIBLE   MSGTXT
REFRESH_COMPLETE                Y          <null>
REFRESH_FAST                    N          <null>
REFRESH_FAST_AFTER_INSERT       N          SQL PIVOT or UNPIVOT is present
REFRESH_FAST_AFTER_INSERT       N          subquery or named view in FROM list even after view merging
REFRESH_FAST_AFTER_INSERT       N          inline view or subquery in FROM list not supported for this type MV
REFRESH_FAST_AFTER_INSERT       N          view or subquery in from list
REFRESH_FAST_AFTER_ONETAB_DML   N          see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML      N          see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT                N          PCT FAST REFRESH is not possible if query contains an inline view

As the REFRESH_FAST_AFTER_INSERT line says, SQL PIVOT or UNPIVOT is present. So you can't use on commit.

You can get around this by using old-style manual pivots:

drop materialized view mv ;
create materialized view mv
refresh complete on demand
as
  select count ( case when c1 = 1 then 1 end ) one,
         count ( case when c1 = 1 then 1 end ) two
  from   t;

truncate table MV_CAPABILITIES_TABLE;
exec dbms_mview.explain_mview('mv');

select capability_name, possible, msgtxt
from   mv_capabilities_table
where  capability_name like 'REFRESH%' ;

CAPABILITY_NAME                 POSSIBLE   MSGTXT
REFRESH_COMPLETE                Y          <null>
REFRESH_FAST                    Y          <null>
REFRESH_FAST_AFTER_INSERT       Y          <null>
REFRESH_FAST_AFTER_ONETAB_DML   N          CASE expressions present in materialized view
REFRESH_FAST_AFTER_ANY_DML      N          see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT                N          PCT is not possible on any of the detail tables in the materialized view

Some fast refreshes are now possible. So you can do:

alter materialized view mv
  refresh fast on commit;

select refresh_method, refresh_mode
from   user_mviews
where  mview_name = 'MV';

REFRESH_METHOD   REFRESH_MODE
FAST             COMMIT


SQL Server 2012 In-Place Upgrade and Timing of Database Version Change We are updating several SQL Server 2008R2 instances to SQL Server 2012 SP3 CU2. I know that the database versions will be changed from 100 to 110 during this process (e.g. once finished, the databases can no longer be moved back to a 2008R2 instance).

Does this change to the databases occur during the upgrade itself or the first time that the databases are brought online when the server restarts?

The database version is updated automatically, when the database is first brought online, during the recovery process.

The database compatibility level is never automatically modified.  If your database is at 100 now, that won't change.

Refer to https://msdn.microsoft.com/en-us/library/bb510680.aspx for database compatibility.

See https://blogs.msdn.microsoft.com/sqlserverstorageengine/2007/04/26/whats-the-difference-between-database-version-and-database-compatibility-level/ for the differences between version and compatibility level.


PL/pgsql: use cursor to update next record based on a condition from current record I have this function in Postgresql 9.6 using pl/pgsql.
This function is to update a row from a table based on a condition on previous row.

The table has Item id(id), year of ownership (year) , quarter of ownership(quarter), own by ctc status(ownby_ctc), and
own by ftc status (ownby_ftc).

Each item id will be repeated one time each quarter every year from 2013 to 2016.
ownby_ctc will be marked 1 if item owned by CTC and it is either:
1- Happened before ownby_ftc or
2- Happened in the same quarter as ownby_ftc
and ownby_ctc never happened after ownby_ftc.

In case,  ownby_ctc happened before ownby_ftc

I want to select the row where ownby_ctc = 1 and ownby_ftc  = 0 and update the column ownby_ctc in next rows to be 1 until ownby_ftc is equal to 1.

Notice:
* the table is order by id, year, quarter.
*primary key is id_y_q.
Problem with my code is that the function is updating  one row if the condition is true and this is because  I am updating the actual table but not the cursor.

Please advice how to solve this problem.

CREATE OR REPLACE FUNCTION myfun()
   RETURNS VOID AS $$
DECLARE

cur SCROLL CURSOR
FOR select id_y_q, id, year, qrtr, ownby_ctc, ownby_ftc
FROM mytbl ORDER BY id, year, qrtr;
r1 RECORD;
r2 RECORD;

BEGIN

OPEN cur ;

LOOP

    FETCH cur INTO r1;
    EXIT WHEN NOT FOUND;

    IF (r1.ownby_ctc = 1 and r1.ownby_ftc =0 )THEN

        MOVE FORWARD 0 FROM cur;
        FETCH cur INTO r2;
        EXIT WHEN NOT FOUND;

        IF (r1.id = r2.id) THEN
            IF ( r1.ownby_ctc = 0 AND r1.ownby_ftc =0  )
               OR (r1.ownby_ctc = 0 and r1.ownby_ftc =1  ) THEN

                UPDATE mytbl
                SET r1.ownby_ctc = 0 = 1
                WHERE id_y_q = r2.id_y_q ;

                MOVE BACKWARD 1 FROM cur;

            END IF;
        END IF;

    END IF;

END LOOP;

CLOSE cur;

END; $$

LANGUAGE plpgsql;

What you want is something this this with lag() and lead()

UPDATE mytble
SET ownby_ctc = false
FROM (
    SELECT
      id_y_q,
      lead(ownby_ctc) OVER () AS lead_ownby_ctc,
      lead(ownby_ftc) OVER () AS lead_ownby_ctc,
    FROM mytbl
    ORDER BY id, year, qrtr;
) AS t2
WHERE t2.id_y_q = id_y_q
  AND t2.id = id
  AND ownby_ctc = 1
  AND ownby_ftc = 0

I'm not sure your copy works and you don't provide an environment but something like this is what you're after.


How does Log shipping know to keep track I have set up log shipping between 2 SQL Servers (SQL Server 2008 and SQL Server 2012).

At first I took a full backup of the first one and restored it on the other one and then I set up the log shipping. The database's first backup-restore took a few hours and below are my questions that I need to understand :

How does the log shipping process know from when to start the logs it is shipping?
Does it start from when the last backup-restore process happened or from the moment the log shipping itself is set ?
Can I be sure that no information was lost in these few hours ?

I have set up log shipping between 2 SQL Servers (SQL Server 2008 and SQL Server 2012)

This is a ONE WAY situation.

When you have to failover, it will be easy as the logs from sql server 2008 can be restored to sql server 2012.

The stopping stone will be FAIL BACK - if sql server 2008 is Primary and SQL Server 2012 is secondary (standby) server, after failing over to sql server 2012, you wont be able to failback.

Note: SQL Server allows you to restore transactions logs from earlier versions of the product to higher vesions but the upgrade doesn't happen until recovery of the database occurs. And that's why you can't use STANDBY mode in this situation. It has to be in RESTORING mode only.

The only option for failback will be to upgrade the old sql server 2008 to 2012.

  At first i took a full backup of the first one and restored it on the other one and than i set up the log shipping. The databases first backup-restore took a few hours.

Logshipping is an automated process of doing FULL backup, copying it to secondary server and restoring it in NO-RECOVERY state and then taking consequent log-backups from the primary server, copying them to secondary server and restoring them.

Depending on the size of the database backup (whether you are using compression or not) and the network bandwidth, it can take from mins to hours. Other way is to initialize secondary database from the FULL backup of primary database - restore an existing backup of the primary database to the secondary database

  i wish to know: how does the log shipping process knows from when to start the logs it is shipping?

The history and status of log shipping operations are always saved locally by the log shipping jobs. The history and status of the backup operation are stored at the primary server, and the history and status of the copy and restore operations are stored at the secondary server. If you have implemented a remote monitor server, this information is also stored on the monitor server.

Refer to : Monitor Log Shipping for more details about the tables and stored procedures used to track log-shipping jobs and status.

  does it start from when the last backup-restore process happened or from the moment the log shipping itself is set ?

It starts when you initialize the secondary database from the full backup of primary database restored with NO RECOVERY on the secondary server. Logshipping proces relies on the concept called Log-sequence number (LSN's) to link which log backup will be restored after the last log backup.

A picture is more than a thousand words !

Reference

  Can i be sure that no information was lost in these few hours ?

Yes there would be no information lost - unless in these few hours the primary server crashed or the backup becomes corrupted (due to I/O subsystem). As soon as the full backup is restored, logshipping will continue taking log backups on primary and start copying it to secondary server and restoring them.

The minimum backup/copy and restore frequency is 1 min.

Note: Once you configure a database for logshipping, you dont have to take any additional log backups - as this will break the LOG CHAIN and the logshipping will start failing.

Full and differential backups can be taken, but NO log backups.

Read up on more on How to Perform SQL Server Log Shipping BY BRAD MCGEHEE


how to find similar word with more similarities how to Find words with length less than or equal...

  declare @inp nvarchar(max),@data nvarchar(max)
  set @inp='You can dance, you can jive, having .... jove... jve, ...'
  set @data = 'jeve'
  select @inp as results where @inp like '%' + @data +'%'

@inp not return any data, but i want show:

results
=====
jive
jove
jve

I've never thought of SOUNDEX or DIFFERENCE as that useful, and they are certainly a bit old-fashioned these days.  However we can combine it with the full-text parser, and it does in fact answer this query.  Note the extra quotes in the search statement:

DECLARE @inp NVARCHAR(MAX), @data NVARCHAR(MAX)
SET @inp= '"You can dance, you can jive, having .... jove... jve, ..."'
SET @data = 'jeve'

SELECT display_term, SOUNDEX(display_term) sdx
FROM sys.dm_fts_parser(@inp,0,0,0)
WHERE SOUNDEX(display_term) = SOUNDEX(@data)
 AND LEN(display_term) <= LEN(@data)

SELECT display_term, SOUNDEX(display_term) sdx, DIFFERENCE(display_term,@data) diff
FROM sys.dm_fts_parser(@inp,0,0,0)

There is a fairly limited use case for these functions so if you have more complex examples then they probably won't extend.  Worth a mention though.


How to unify three separate tables having same data, same columns but tuples in different languages. Only one key(caption) is common to all This needs to be done in order to complete the unification of many tables present in different databases with different languages. (Part of Dictionary Management, will have caption and messages where multiple columns can make messages)

If I understand your question correctly this is a possible solution. If not, could you please describe your problem more precisely?

To unify 3 tables to one virtual table you can create a view which has the following select query:

select 'en' lang, col1, col2, col3, col4, ... from table1
union all
select 'ru' lang, col1, col2, col3, col4, ... from table2
union all
select 'es' lang, col1, col2, col3, col4, ... from table3

If you need to modify the data in these 3 tables as well you have to know where the data came from. This is why I added the column "lang" -- perhaps it already exists.
With an instead-of triggers you can also manipulate the data in the table even though you insert/update/delete the view.

So here is an example:

update view set col4 = 'asdf' where lang = 'en' and col1 = 12345;

--> In the instead-of trigger you can then rewrite the update to the actual table:

update table1 set col4 = 'asdf' where col1 = 12345;


Is it possible to make schema changes on a replicated subscriber database? I have a table that is published via transactional replication. A developer has requested modifying the subscribed table to add a column that is to be used for reporting purposes. The column will be updated via an outside process.

It is not prudent to add the column to the published table and insert the data there as the data that is needed to update the column is on the subscriber server.

Is this possible? Is there a better way?

Yes, this can be done. It isn't recommended as if you need to re-initialize the subscription the column will be lost and the values would need to be repopulated.  Can the value be stored as a computed column?  If so, then just add it to the publisher which would then replicate the change to the subscriber.


MySQL stored procedure user privilege I have a query regarding user permission on a stored procedure.

If a user that does not have permissions to create tables in a database, but it does have permissions to create and execute stored procedures

If those stored procedures create tables (as part of the SQL code that is executed) then, will the user get an error when executing the stored procedure when it tries to create a table?

It depends on the SQL SECURITY characteristic you defined when you created the procedure.
SQL SECURITY for a stored procedure can be either DEFINER or INVOKER.

If your user can not create table but the definer of the stored procedure can, then your user will be able to create a table through the stored procedure.

By default, the SQL SECURITY characteristic in MySQL is DEFINER.

To put it in a nutshell :

Stored procedure with SQL SECURITY DEFINER : runs with stored procedure creator rights
Stored procedure with SQL SECURITY INVOKER : runs with current user rights


Performance tuning of Purging table Data in SQL Server 2016 I have to Automate the Purging huge volume of data from main table to Archive.
Steps I am following :

I am inserting the data from main table to archive table.

After cross verifying the count in both tables , deleting the data from main table.

Risk: Delete statement may go in hung state and DB will go in recovery mode.
Note: we can't perform any DDL operation on main table as it's live transaction table. We can go with DML.

Database: SQL Server 2016
OS: Windows and IIS

DDL is one of the main ways of speeding up the deletion of records, but seeing that DDL is not an option and you're facing 'hung states' for delete statements, perhaps I suggest looking into deleting records in batches?
Please see link, and here's a query example based on link:
DECLARE @Batch INT = 1000
WHILE 1 = 1
BEGIN
    DELETE TOP (@Batch)
    FROM MyTable
    WHERE Date < ‘20200401’';
    IF @@ROWCOUNT < @Batch BREAK
END

In this case, when we're deleting by Date, make sure you've got an index on Date so that SQL Server can quickly grab just those rows. You'll want to avoid table-level locking.


How to export an Access database and import to MySQL? I am working on a Java project that sources data from an MS Access database, but I want to use MySQL because I have found it to be very easy to manage. How can I export an Access database that has tables of 8 MB and more, and move the data to MySQL?

You can use ODBC to connect from MSAccess to MySQL.
Here is an example.

What you should actually do is:

Configure a new ODBC DNS connection (this might help)
Inside access - click on the table you would like to export, click on the file menu, then go to export and choose the name for the export
Select the ODBC connection you just created from the list of files of type.

MSAccess will connect to your MySQL server and export the data in that table to your MySQL database.


Add article to transactional publication without generating new snapshot Using SQL 2008 R2 transactional replication with pull subscribers, when we add an article, I'd like to avoid having to create an entire snapshot (the db is ~80 GB, so this takes hours).

From this article, I've seen how to do this with a partial snapshot by setting immediate_sync off, but that didn't work for us.

Ideally I'd like to just run this as part of our db script to create the table, so if we want it replicated we do:

Create Table ...
sp_addArticle ...
sp_PushThisToOurSubscribersNow

You can add the article through SSMS using the GUI and even apply filters to it. As long as you do not change any of the other properties of the article you will not need to generate a full snapshot.

When you hit OK in the publication GUI (after adding the article), it will close without prompting to reinitialize - if it does prompt to reinitialize, then you have changed something which requires a FULL snapshot. If that happens, hit cancel and try again.

After you add the article you can simply start the snapshot job and you will notice that it only generates a snapshot for the new article (called a mini-snapshot).

Then check your distribution job, and notice that it created the table at the subscriber and bulk-copied your data.

Good luck, and let me know if you require further assistance.


Error when running sp_BlitzFirst from First-Responder-Kit-20170307 when I run

EXEC sp_BlitzFirst @expertmode=1

I get

Setting up configuration variables
Warning: The join order has been enforced because a local join hint is used.
Now starting diagnostic analysis
Capturing first pass of wait stats, perfmon counters, file stats
Beginning investigatory queries
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@LineFeed".
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ','.
Msg 8169, Level 16, State 1, Line 11
Conversion failed when converting from a character string to uniqueidentifier.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ' AS HowToStopIt,
                (SELECT TOP 1 query_plan FROM sys.dm_exec_query_plan(r.plan_handle)) AS QueryPlan,
           '.
Msg 105, Level 15, State 1, Line 27
Unclosed quotation mark after the character string ' AND tBlocked.wait_duration_ms > 30000;'.
Finished running investigatory queries
Waiting to match @Seconds parameter
Capturing second pass of wait stats, perfmon counters, file stats
Analyzing changes between first and second passes of DMVs
Analysis finished, outputting results

This is from First-Responder-Kit-20170307

The SQL Server version and build I have is:

Microsoft SQL Server 2012 (SP3-CU7) (KB3205051) - 11.0.6579.0 (X64)   Dec 22 2016 11:18:09   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

You have databases in compatibility level 2005, which isn't supported.


MySQL: How to get the maximum disposition and the maximum date from my database I want to get the top disposition of every ID where 1 is the lowest and 13 is the highest, however when there are IDs with similar disposition (e.g. ID #2 having multiple dispositions of '9') I cannot get the record with the latest time / timestamp.

How can I group the results by ID getting the maximum disposition and if there are multiple disposition, get the most recent?
(e.g. ID #2 getting '2017-04-06 14:16:00' as the top or max.)

edit:
I forgot to mention that I also need to group them by date. I have a separate column for date. The expected output is: Latest + Highest dispo of ID #2 on April 6 and Latest + Highest dispo of ID#2 on April 7 and so forth.

edit2:


Query on SQL Server Always On DC1: (servers are in WSFC): DB_Server1 and DB_Server2

DC2: DB_Server3

DC1 and DC2 servers are located in different geo locations. Is it possible to configure SQL 2017 Always-On/SQL 2017 Distributed Always-On in above environmnet?
As per my understanding, the servers should be in WSFC, but DC2 DB server is not in cluster, its standalone machine. If it's not possible to configure SQL always-On in above env, please suggest which SQL HA/DR solution i can use...

This is not enough information in your question, to definatively say if you can or can not meet your goal with your available resources.

It is possible to Create a domain-independent availability group that spans physical locations.

Yes it can be done.  How to set up the infrastructure to support it, is going to be out of scope for DBA.se, but you can get help at https://serverfault.com/help/on-topic & https://networkengineering.stackexchange.com/


Find Stored procedure which are using temp tables We have got nearly 700 plus stored procedures with nearly 50% uses #tmp tables in their queries.

Need to write query to search through all procedures, and list procedures which uses tmp tables.

Can this be done by querying some system tables where procedure information is stored.

Unfortunately, SQL Server doesn't store any type of metadata or dependency information about temp tables potentially created inside of them, so you'll have to parse. A first pass could be:

SELECT [schema] = s.name,
  [procedure] = p.name
FROM sys.procedures AS p
INNER JOIN sys.schemas AS s
ON p.[schema_id] = s.[schema_id]
WHERE OBJECT_DEFINITION(p.[object_id]) LIKE N'%#%';

You'll get some false positives, probably. You can tighten down the search space with various approaches, like:

LOWER(OBJECT_DEFINITION(p.[object_id])) LIKE N'%create%table%#%';
OR LOWER(OBJECT_DEFINITION(p.[object_id])) LIKE N'%select%into%#%';

But those things could still appear in a comment or in multiple completely unrelated statements.


How to get notices to the output stream? I have debugging messages in functions. Those message are raised like

RAISE NOTICE 'Value of id : %', id;

I set my log file with \o messages.txt

Then I do what I need to do with \i process.sql

And when the execution is terminated, \o.

The problem is that I don't have the messages raised by notices into messages.txt. The messages are displayed on the screen but I want them to be written in messages.txt

How could I do that ?

I tried to use RAISE LOG... and messages are written in log file... It's not what I want.

I have a work around with

plsql -f /path/to/process.sql > messages.txt 2>&1

but I would like to know how I could use \i and \o on plsql client having the messages into the file specified in \o

My client, on cygwin is psql (PostgreSQL) 8.2.11 and the server version is 9.0.7

I'm afraid that you won't like this answer, but currently it seems impossible.
From the psql documentation:

  Saves future query results to the file filename or pipes future
  results into a separate Unix shell to execute command. If no arguments
  are specified, the query output will be reset to the standard output.

  "Query results" includes all tables, command responses, and notices
  obtained from the database server, as well as output of various
  backslash commands that query the database (such as \d), but not error
  messages.

And as you noticed, there is no way redirecting error messages when using psql interactively.

(I've been playing around with all kinds of redirection from \o to no avail.  It looks like that the query output channel is different from the one getting error messages - and even errors from the server and raised in your procedures go different ways.

test=# SHOW client_min_messages;
 client_min_messages
---------------------
 notice

-- added the above to show it's not a config problem

CREATE FUNCTION raise_test() RETURNS integer AS
$body$
BEGIN
    RAISE NOTICE 'notice';
    RETURN 1;
END;
$body$
LANGUAGE plpgsql;

test=# \o | cat > out.sql
test=# SELECT raise_test(); -- you could put this in a file and call \i your_file, it's just the same
NOTICE:  notice

test=# \o | cat > out.sql 2>&1
test=# SELECT raise_test();
NOTICE:  notice

out.sql contains

 raise_test
------------
          1
(1 row)

in both cases.
This is why I am clueless about what channel/file descriptor is used for outputting the messages raised from a procedure.)
)

(There is a thread on PostgreSQL-hackers that may shed some light on this issue: http://postgresql.1045698.n5.nabble.com/psql-output-locations-td5068313.html )

What one could possibly do is starting psql like

psql test >/tmp/psql.out 2>&1

and this will redirect all output to the specified file.  The only problem with this that you don't even have a prompt, and lose the commandline editing capabilities.


Mysql database table cursor i have written the function below containing a cursor while executing it an error appears . what' wrong please

CREATE PROCEDURE processorders()
BEGIN
   DECLARE ordernumbers CURSOR FOR
     SELECT * FROM psdms_feature_product

   dept_rec ordernumbers%ROWTYPE
   o ordernumbers%ROWTYPE

   OPEN ordernumbers
   REPEAT
    FETCH ordernumbers INTO o

    IF (o.id_product ) THEN
      IF (o.id_feature  == 510) THEN
        update    psdms_feature_value_lang t1
        LEFT JOIN psdms_feature_product t2
        ON        t1.id_feature_value = t2.id_feature_value
        SET       value ='95090€'
        WHERE     t2.id_product = 1
        AND       t2.id_feature = 510
      ELSE
        insert into psdms_feature_value ( id_feature ) values (510);
        insert into psdms_feature_product ( id_feature, id_product , id_feature_value)
        values (o.id_feature , o.id_product , o.id_feature_value )
      END IF
    END IF

   UNTIL done END REPEAT
   CLOSE ordernumbers
END
END

Better is to avoid cursors.  Remember that SQL loves to do things in bulk, but hates to work with Cursors.

UPDATE  psdms_feature_value_lang t1
    JOIN  ordernumbers AS o  ON ...
    LEFT JOIN  psdms_feature_product t2
          ON t1.id_feature_value = t2.id_feature_value SET
    value  ='95090€'
    WHERE  t2.id_product = 1
      AND  t2.id_feature = 510
      AND  o.id_product
      AND  o.id_feature = 510;
INSERT
     INTO  psdms_feature_value ( id_feature )
SELECT  510
    FROM  ordernumbers
    WHERE  o.id_product
      AND  o.id_feature = 510;
INSERT
     INTO  psdms_feature_product ( id_feature, id_product, id_feature_value)
SELECT  o.id_feature , o.id_product , o.id_feature_value
    FROM  ordernumbers
    WHERE  o.id_product
      AND  o.id_feature = 510;

Index:

ordernumbers: INDEX(id_feature, id_product, id_feature_value)

Other notes:

== is not proper syntax

What do you mean by AND id_product?  It probably tests for non-zero.


How do I grant Read Access to FILESTREAM data in a table but not grant SELECT access? Problem:

I want users to be able to download data stored in a table that contains a FILESTREAM column.  Each file is typically 200 to 500 MBytes.  I however do not want to grant the user SELECT access (specifically SELECT *) to the table.  I was able to create a stored procedure that provides the Path / Transaction Context.  However if I do not grant select permissions on the table the SqlFileStream() class fails to open due to a permission error.

If I grant the SELECT permission I risk a user performing a SELECT * on the table and watch the server die as it is trying to return gigabytes of information.

I believe I have a solution.
I think I can deny select permissions to the table.  The only reasonable way to retrieve the data from the FileStream table is through the application.  I can use an Application Role which has permission to the table to perform the download.  I will try this out, I was hoping to not have to make this more complicated...

There's no way to restrict SELECT access to a set number of rows.

If the users don't need access to the entire table, but just their "own" rows, you could configure row level security (SQL 2016 and up) to restrict access to only what they need. This will not prevent the user from performing a SELECT *, but it will restrict the volume of data returned if they do run one.

Ultimately, this is the kind of situation which would be best solved by not allowing users to have any direct access to the table, and instead have the data returned via app code which can be controlled to not run a SELECT *.


Database Schema -Images in separate tables? I'm pretty new to web development and i'm creating a local guide app. Currently, I have the following table in the database:

Organization Table

Organization_Id(PK)
name
description
site
category_id

Description

organization: entity that describes a merchant
organization profile: page with organization's details (name, description, site, cover header_image, profile_image, detail_images)

header_image: organizations cover image
profile_image: organizations logo image
detail_images: images of the organization building

Relationships

An Organization has_one profile_image, has_one header_image, has_many details_images.

Question

What is the best aproach in terms of normalization to accomplish that?

Add one column for the profile_image and another to header_image in Organization table.
Create one table ProfileImage, other table HeaderImage with one_to_one relationship to Organization table and eventually another DetailImage table with one_to_many relationship.
Create an Image table to store all the images paths table with a column image_type (profile, header, details) using a one_to_many relationship.
Other option

I would go with the first option.

Create two columns, one for profile image, one for header image.
Store the path

You shouldn't be creating new tables for 1:1 relations. For both header and profile you want to store at most one image. That's what columns are for.

Even better. Normalize them all to the same format and appropriate sizes and save them in  pkid/header.png, pkid/profile.png and then just make boolean flags has_image_header has_image_profile If you don't want to hardcode that path in the app, and you'd rather hardcode it in the database do it with a function,

CREATE FUNCTION get_path(pkid int, imgtype text)
RETURNS text
AS $$
  SELECT '/' || pkid::text || '/' || imgtype || '.png';
$$ LANGUAGE sql
IMMUTABLE;

SELECT get_path(432, 'header');
    get_path
-----------------
 /432/header.png
(1 row)


How to set column text when creating a view in SQL? By default, when creating a view, for the Text of each column, if the column is a direct selection from a table/physical file then it will use the Text of that table/physical file's column. If it's not (e.g. a CASE statement), then it's blank.

How can I override it to set what I want for the view's columns' texts?

Simple: use an alias

SELECT ... CASE something WHEN 'somethingelse' ... END AS whatever, ...

The above will have the alias of WHATEVER. You can even get fancy and quote the alias:

... AS "Something Else"

which will have the alias of Something Else.


Can I enforce cascade delete in a delete from statement? I am using constraints in a database meaning that records of a certain table cannot be removed lightly. That is normally the desired case.

However, is there a way to write a DELETE FROM query so that for every constraint that would normally block the removal of record/s, cascade delete will be used?

As you probably know, the way to do this is a foreign key whith ON DELETE CASCADE. But you don't want this, so I see 2 options (there may be more as I'm not a SQL Server expert):

Make this in the hard way: write multi-table DELETE statements. As you can see in the documentation, it is very similar to a JOIN.
If you want to do this easy (for example to hide information about database structure to developers), you'll need a BEFORE DELETE trigger. This trigger can delete rows from other tables. I understand that you want this to happen only in certain cases; this is doable by setting (before your query) and checking (in the trigger) SESSION_CONTEXT. Rows will only be deleted if the variable is set. It should be unset at the end of the trigger itself, so developers won't need to take care of this (they could forget). Since the trigger will delete relevant rows before the query is executed, the constraint will not make it fail.
A very similar approach, but maybe cleaner, would be writing a stored procedure.


MySQL best performance settings I am going to do some performance tests on MySQL server as I want to evaluate how MySQL behaves in some situations. MySQL is the only (additional) software installed on my server (Windows Server 2012), so no other process is going to disturb it.

Currently, I have  default settings on my MySQL instance - the only setting I've changed is a mode when you install it - "standalone" mode, if I remember correctly.

Which settings should I change? I am mostly interested in giving MySQL as much resources as it can take - I really want to give it everything possible.

Can you point me to some settings, manuals, etc.?

I will be testing on multiple hardware configurations (from commodity hardware (starting with i7-based laptop) to Gen9 HP Servers with "top-notch" disks (througput > 1GB/s)).

I will be testing on MySQL 5.7 and InnoDB engine.

I am going to test some SQL statements on a fairly big dataset (2-3 15+ million rows tables). I will be testing (BULK) INSERT, SELECT, UPDATE and DELETE statements.

The answer to this question varies depending on what database engine backend you're using.

If you're wildly unfamiliar with all of them, I really recommend that you use TokuDB, because it's entirely self-configuring with no modifications necessary out of the box.

I use MariaDB (MySQL clone) with Aria tables. My configuration would be totally, and completely different from yours.

If you use InnoDB, set innodb_buffer_pool_size to 80% of what you find appropriate for a database to be using, up to a maximum of 10% larger than your data size.

If you use TokuDB, use stock config.

If you use MyISAM, may Loki have mercy on your soul.


how to Update Value in local tabel when job Finished I need to update some values into a database as soon as all the tasks of Executing Service have finished executing there jobs.

In other words, I want do something like status when the all jobs finished directly change this status .

I think that doing stored procedure like:

Update (Table Name )  Set ( Field ) = 1 )

But i don't know, how to execute the stored procedure, when the job finished.

Because there are several apparently semi-independent jobs running, I would suggest implementing a status table and creating a trigger for that table.  It depends on how complex you want to be, but let's keep it to a very simple example.  Perhaps a table:

CREATE TABLE dbo.JobGroupStatus
(Job1 INT,
 Job2 INT,
 Job3 INT,
 Job4 INT,
 Job5 INT
 Job6 INT);

When the first job starts it could set all columns to 0.  Then as job one finishes it would "UPDATE dbo.JobGroupStatus SET Job1 = 1", then Job2 when it finishes would update Job2, etc.  Create a trigger on that table that discerns when all jobs have finished.

 CREATE TRIGGER UPD_JobGroupStatus ON dbo.JobGroupStatus
 FOR UPDATE
 AS
 IF (SELECT Job1+Job2+Job3+Job4+Job5+Job6
     FROM dbo.JobGroupStatus) = 6
   Update dbo.FinishedJobGroup Set AllDone = 1;
  GO

Of  course, you would need to have a periodic check that would determine that apparently all 6 jobs will not finish and report failure.

This little snippet of code is just a toy, not a serious production program. But perhaps it offers you an idea or two about how you might approach your problem.


Do SQL Server Compatibility Levels Introduce Old Security Vulnerabilities Can anyone help settle what I thought was a simple question.

A fellow DBA friend and I were talking about compatibility levels in general, and their relation to security specifically.  He posited that a good reason to bring them as current as possible was to reduce security vulnerabilities caused by them.  I countered that formerly SPs and currently CUs and hotfixes would surely patch those vulnerabilities, i.e. running a database in 2005 compatibility mode in a 2014 SQL Server instance surely does not revert that database back to 2005 SP4 security levels/vulnerabilities.

I've been searching for documentation to back this claim up but I've been unable to find any.  Does anyone know of any authoritative documentation that would show me either correct or in error?

It does not introduce past vulnerabilities, and applies mostly to T-SQL syntax and T-SQL features. As the documentation states,

  Compatibility level affects behaviors only for the specified
  database, not for the entire server. Compatibility level provides only
  partial backward compatibility with earlier versions of SQL Server.

Later on the documentation goes on to list the differences between each mode and you'll see no mention of security or hotfixes, just features/behaviors that will be differently implemented or unavailable.

Exploits would generally be at the server and DB engine level, so that isn't really a concern here. It would be a pretty huge security risk if I always kept my MSSQL instances patched but had one database in an older mode that was never able to take advantage of my diligent patching efforts. Although that would be a great way to get people onto the newest version!

You could argue that using old T-SQL code constrained by old version syntax might cause one to use work-arounds to inelegantly achieve certain requirements, which in turn might cause code to be more at risk for having vulnerabilities, but that onus is on the developer and nothing inherent to MSSQL. I can't even think of an example where this applies, but I'm sure they exist.


Validate many SQL statements for correctness I have about 150 reports generated from SQL statements. These reports are saved independently from the server. Some generated from sprocs, some from table-valued functions, but mostly just SQL statements.

Now the server goes through a big refactoring of tables and sprocs. And I need a way to validate that all the reports are still running, as it is possible the changes will break the reports.

One way to validate that all the reports are valid - run them and lookout for errors.

The problem - some of the reports are very heavy and include aggregation of of many tables with many joins with millions of records.

Is there a way to validate SQL statements without actually executing them?

I'm working with SQL Server 2008 and have ability to apply C# processing to the statements. Ideally I'd like a way to validate the reports often and without much effort, i.e. 10 times a day. So making a schema-only copy of DB is not an option.

There's the table sys.sysdepends which will help with your stored objects.

For embedded SQL you could SET ROWCOUNT for the batch.  This will terminate the query after compilation (and hence name resolution and object binding) but before large quantities of data are processed.


SQL Server process only using half of the memory on host We doubled the memory on host that has a SQL Server Enterprise 2014 instance on it. After a reboot of the server we noticed that the SQL service is only using half of the memory.

A little background info:

The instance is running in compatibility mode for SQL 2008
We went from 64 gb to 128 gb
On the instance in Properties > General > Memory it shows 131072 MB
On the instance in Properties > Memory > Max Server Memory it shows 2147483647 MB

Do you have any idea why the service is not taking up all of the available memory?

After a reboot of the server we noticed that the SQL service is only using half of the memory.

SQL server will use memory as an when needed. Once you reboot your instance, it will take sql server some time to ramp up (bringing pages in the buffer pool), since a reboot will trash all the query plans and reset DMV data.

Min server memory controls the minimum amount of Physical memory that sql server will try to keep committed. When the SQL Server service starts, it does not acquire all the memory configured in Min Server Memory but instead starts with only the minimum required, growing as necessary. Once memory usage has increased beyond the Min Server Memory setting, SQL Server won’t release any memory below that amount.

Let your workload run and sql server will eventually reach your MAX MEMORY setting.

Check : How to determine ideal memory for instance?

  On the instance in Properties > General > Memory it shows 131072 MB

This is the total memory available to the instance i.e. It lists the amount of RAM installed on the server.

  On the instance in Properties > Memory > Max Server Memory it shows 2147483647 MB

The is the MAX memory and you should change it away from the default (use up all memory)

Also, from sql server 2012, Memory management has gone some major changes.

  Setting max server memory becomes more straightforward with SQL Server 2012. The redesigned Memory Manager takes a central role in providing page allocations for the other components, and the max server memory setting governs all memory manager allocations.


How to Backup a Log-shipped Mirror Database without breaking log-shipping I’ve currently got a slow connection between LIVE and DR data-centres (new pipe is 6 months away).

Databases are log-shipped every 15 minutes.

My problem is that at the weekend the FULL BACKUPS take 3 days to copy over to DR.

I’m trying to find a way to take the Weekly Full Backups directly off the log-shipping Mirror.

I’m limited to switching the Mirror databases between “No Recovery” & “Standby” and I can’t take the Mirror DB off-line.

Has anyone figured out how to do this without breaking Log-shipping? I’m guessing it needs to have the database in Standby Mode. I’ve experimented with “WITH COPY-ONLY” but this needed the database to be in “recovery”.

Is there a third party tool? Please help.

It is not possible to take a backup of a secondary log-shipped DB without bringing it online.  I believe to take a backup with log shipping, the secondary DB has to be brought online WITH RECOVERY as Standby/Read-Only won't allow the DB to be backed up.

Other sources that confirm:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/41d80415-80bc-4821-85b1-762ce9ea72f1/how-to-backup-the-log-shipping-secondary-database?forum=sqldatabaseengine

Can I backup (Full) the Log-Shipping Secondary Database?


Use Distinct With Cast As XML Can you run Distinct() on XML?  I ask because if you look at this garbage data, it produces this error

  output
  SQLState = 37000, NativeError = 402
  Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]The data types char and xml are incompatible in the add operator.
  SQLState = 37000, NativeError = 8180
  Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
  NULL

How can I succesfully run this bcp statement with Distinct and Cast to XML?

Declare @TestData Table (pinkpurpleblue varchar(max))
Insert Into @TestData (pinkpurpleblue) Values
('xx11'), ('rr22'), ('xx11'), ('rr22')

Declare @file nvarchar(200), @sql nvarchar(max)
Set @file = 'C:\Prod\Results\testing.csv'
Set @sql = 'bcp "Select ''pinkpurpleblue'' UNION ALL SELECT CAST(DISTINCT(pinkpurpleblue) As XML) FROM @TestData''" queryout "'
Set @sql = @Sql + @file + '" -c -t, -T -S '+@@SERVERNAME
exec master..xp_cmdshell @sql

Even with this syntax I get the same error, and (I think) I am only attempting to cast a nvarchar column to xml in this example

Declare @TestData Table (pinkpurpleblue varchar(max))
Insert Into @TestData (pinkpurpleblue) Values
('xx11'), ('rr22'), ('xx11'), ('rr22')

Create Table #Test123
(
  pinkpb varchar(max)
)

Insert Into #Test123 (pinkpb)
Select DISTINCT(pinkpurpleblue) FROM @TestData

Declare @file nvarchar(200), @sql nvarchar(max)
Set @file = 'C:\Prod\Results\testing.csv'
Set @sql = 'bcp "Select ''pinkpurpleblue'' UNION ALL SELECT CAST(pinkpurpleblue     As XML) FROM #Test123''" queryout "'
Set @sql = @Sql + @file + '" -c -t, -T -S '+@@SERVERNAME
exec master..xp_cmdshell @sql

You have 2 issues that I can see:

You cannot pass a VARCHAR(MAX) to xp_cmdshell, largest you can pass is VARCHAR(8000) or NVARCHAR(4000), as per the xp_cmdshell documentation here.
Your temp table (and your Table variable for that matter) are out of scope for other connections, so bcp won't be able to see it.

I worked around these issues with the following:

DECLARE @TestData TABLE (
    pinkpurpleblue VARCHAR(MAX)
)

INSERT INTO @TestData (pinkpurpleblue) VALUES ('xx11'), ('rr22'), ('xx11'), ('rr22')

CREATE TABLE ##distinctValues (
    pinkpb VARCHAR(MAX)
)

INSERT INTO ##distinctValues (pinkpb) SELECT DISTINCT pinkpurpleblue FROM @TestData

DECLARE @file VARCHAR(200), @sql VARCHAR(8000)
SET @file = 'C:\Prod\Results\testing.csv'
SET @sql = 'bcp "SELECT ''pinkpurpleblue'' UNION ALL SELECT CAST(pinkpb AS XML) FROM ##distinctValues" queryout "'
SET @sql = @sql + @file + '" -c -t, -T -S '+@@SERVERNAME
EXEC MASTER..xp_cmdshell @sql

DROP TABLE ##distinctValues

Here, I make sure that the type of @sql is VARCHAR(8000) rather than VARCHAR(MAX) and I use a global temp table instead of a local one, so that bcp will be able to see it.


SSRS calculated field strange behavior I have a dataset with a calculated field:

 = IIF(String.IsNullOrEmpty(Fields!Event.Value), "\t", Fields!Event.Value) & ", " &
   IIF(String.IsNullOrEmpty(Fields!Action.Value), "\t", Fields!Action.Value) & ", " &
   IIF(String.IsNullOrEmpty(Fields!RequestedBy.Value), "\t", Fields!RequestedBy.Value)

This field is then accessed inside a Table within the report using a lookup expression:

=Join(LookupSet(Fields!contactid.Value, Fields!ContactParticipantValue.Value, Fields!RowSummary.Value, "EventRequests"), Environment.NewLine)

When I run this, initially everything works fine and the correct data is shown in the cell.  However there is an warning displayed:

Warning 1   [rsRuntimeErrorInExpression] The Value expression for the field ‘RowSummary’ contains an error: (processing): (field.ExprHost != null)  0

Then if I go to the next page, all of the cells now contain #Error instead of the 'RowSummary' info.  If I then go back to the previous page, which used to have all the correct data in it, all of the cells are now replaced with #Error.

I have found a work around whereby I add a hidden list container to the END of the report and set DataSet property to the 'EventRequests' dataset with the calculated field in it.  I don't have to add any fields or make this list visible, but somehow just having the list on the report prevents the #Error from replacing all of my data.  This does not solve the problem completely because when I go to export to PDF, I still receive errors.

Anyone know why this is happening?

It is because it may not have a value at that pariticular field/place so first replace the value providing box with:

iif(IsNothing(sum(field)),"0",(sum(field))

Then replace row summary field with the below expression:

=iif(cInt(ReportItems!textbox1name.value)=0,100,(ReportItems!textbox2.value/iif(cInt(ReportItems!textbox1name.value)=0,100,ReportItems!textbox1name.value)*100))


What is the best way to keep track of all changes implemented on a database? I am a new DBA to a company and in general. I am planning to do some changes to a database settings such as backup compression, enable some trace flags, create more files in TempDB ets.
My question is what would be the best way to keep track of all changes and new implemented settings So it can be easy accessible?
And so people after me can easy access and see what is the current condition of the database?
Is there maybe special software for that? Or common way that all DBA's are follows?
I just want to know how to record everything you did on a database? i can write everything down on a piece of paper, word doc, excel or maybe there is a better way of doing that?
Thank you

Do you have budget for a third party monitoring tool such as SQL Sentry?

This will monitor your SQL Server instance for you, far easier than attempting to create your own solution. You can also create additional Advisory Conditions to alert you for any changes you choose.


Symantec Backup Exec runing DBCC CheckDB and Backup on my SQL server What is Symantec Backup Exec (TM)?
I noticed that it is running in one the database server i am maintaining.
I am a newbie in db administration and i am not sure what it is.
It is running DBCC CheckDB on different databases and running Backup on different databases.
Can anybody give some info on this?Is this something windows server team does to protect data?
I did some research and all i can see is information that says it is some kind of data protection program.

I'm not sure if Symantec still owns it, but Backup Exec is Netbackup's little brother. I don't remember the feature differences but Netbackup is more for Enterprise environments and Backup exec for smaller ones.

Are you sure it's Backup Exec that's running DBCC? I've used Netbackup for SQL for many years and it's always done checksum checking during backup. I've never seen it run a DBCC check directly from the Netbackup server or via the client.

Any third party SQL backup software rides on top of the MS written SQL backup. What you're paying for is much better compression, AlwaysOn support, centralized job scheduling and backup image storage. SQL backup has a checksum option and most of the third party products support it as well. But the only time i've seen something not-SQL run dbcc or alter index on a database is Sharepoint doing it.

I've used both and prefer the third party products to native SQL backup any day.


How to free space after I drop a column of a table in SQL Server? The background is that there is a column Fn in a table DataProcessed in SQL Server. Plus, the property of Fn is varchar(max), NOT NULL.
The problem is that I hope to drop Fn from DataProcessed, and free the space which is occupied by Fn in the NDF files of DataProcessed.
How can I finish that? Or, is this doable?

You can run a shrink on your database or its individual files, but in general it's not recommended to do because it's a heavy operation and generally wasted efforts because the space will likely be re-used by your database anyway which will result in a growth operation which is also not a lightweight operation.
When you drop the column, the space it was previously using on disk will be recycled and re-used by your database as it needs more space anyway. It isn't just wasted space just because the OS shows it consumed from the disk. SQL Server manages the space it reserves from the disk internally for the very reason of reducing those heavy growth operations I mentioned earlier. So no need to worry about shrinking.
You can also find more good information on why shrinking is a wasteful operation here.
Also, last thing to note is that a VARCHAR(MAX) column doesn't consume the entire ~2 GB it's capable of maxing out at, for each row, unless you were filling it to the max. It's approximately equal to 1 byte per character you're actually using in it, in general (not counting any additional space saving features going on under the hood like compression). So the space savings you might anticipate by dropping it is potentially less than you think.


An example of a relation without any functional dependency? I have to find a really bad example where you cannot apply Heaths Theorem because of a missing functional dependency.

But am I right, that every relation has at least one functional dependency?

Or can anyone of you come up with an example table with at least 3 Sets of Attributes to project it and then join it together. The result should of course be different from the original table to proof Heath's Theorem right (no FD -> no lossless decompositions)

It does not matter, wheter it is an sql example or an abstract one.

But am I right, that every relation has at least one functional dependency?

Relation can has no functional dependencies, if it has no non-key attributes, i.e. it is full-keyed relation.


How to design database for monitoring the time trends of article tags? My purpose is to monitor which tags increase/decrease rapidly last week on different bulletin boards. Or which tag do new appear. The current table schema of articles is as follows:
article_id e.g. 123
tags e.g. apple,orange
article_created_at e.g. 2021/5/7 01:23
borad_name e.g. database
author e.g. some_person

My current idea is to calculate the amount of each tag by board weekly. But I want to survey other solutions to reduce the development time. Is it ok to choose a time-series database engine or other possible solution? Thanks for your time.

Having a comma-separated list of tags would save on the number of rows written to a database, but would make querying far more computationally expensive. Typically this sort of table would have one record per tag, as this would allow for simpler queries to be written. A structure similar to something you'd find in a blogging database would work. For example:
TagHistory
  article_id
  tag
  flat
  posted_at
  board_name
  author

Then you would have records like this

article_id
tag
flat
posted_at
board_name
author

123
pokémon
pokemon
2021-05-01 00:00:00
games
BillyBob

129
PokEmon
pokemon
2021-05-01 00:07:41
games
Jackie

129
cards
cards
2021-05-01 00:07:41
games
Jackie

129
Deck
deck
2021-05-01 00:07:41
games
Jackie

147
MewTwo
mewtwo
2021-05-01 00:11:12
games
Archibald

The flat field would contain a lower-case tag value with all accents and other non-alphanumeric components removed so that values like pokémon, PokEmon, and Pokemon do not appear as three separate values.
With this sort of design, you could then begin writing queries that could rank tag usage over a period of time. If you were interested in the five most popular tags from last week, you could write a query like this:
SELECT WEEK(posted_at) as week_no, flat, COUNT(article_id) as usages
  FROM TagHistory
 WHERE `posted_at` BETWEEN DATE_FORMAT(DATE_SUB(NOW(), INTERVAL DAYOFWEEK(NOW()) + 6 DAY), '%Y-%m-%d 00:00:00')
                   AND DATE_FORMAT(DATE_SUB(NOW(), INTERVAL DAYOFWEEK(NOW()) -1 DAY), '%Y-%m-%d 23:59:59')
 GROUP BY week_no, flat
 ORDER BY usages DESC
 LIMIT 5;

Alternatively you could do it by board, or by author, or across a period of weeks or months. The sky is the limit and the queries are simpler to write. If there is a regular means of presentation, this data could be collated every week and written to a summary table that would save you the hassle of looking up and calculating tag usage over time.
For example:
TagHistorySummary
  year
  week_no
  flat
  usages

Then if you wanted to present a chart showing the popularity of the pokemon tag for all of 2020 (similar to Google's Ngram Viewer), you could write a query like this:
SELECT week_no, usages
  FROM TagHistorySummary
 WHERE flat = 'pokemon' and year = 2020
 ORDER BY week_no;

Note: Using year as a field name would be a poor choice given it's a reserved word by most database engines. Do not take these examples as verbatim.

This data could then be used to populate a simple line chart:

Hmm ... who knew that Pokemon was a thing before WWI? I sure as heck didn't.
This isn't the only way to accomplish your goal, of course. Experience has shown that it is one of the simpler ways, though, particularly when working with data that is supplied by people.


MYSQL: How to select all attributes of all duplicate entries? I have a database that containes user data and need to dig up only the entries where two attributes are duplicate between users (surname and social security number). I need all of the attributes for these entries, such as email and firstname.

I first used group by to get every user where those two values are duplicates with this query:

SELECT *
FROM jasenet
GROUP BY hetu, sukunimi
HAVING count(*) >= 2
ORDER BY sukunimi;

This returns all duplicate entries. After some research I tried to use IN operator in order to find out each individual duplicate person with this query:

SELECT *
FROM jasenet
WHERE sukunimi
IN (SELECT sukunimi
    FROM jasenet
    GROUP BY hetu, sukunimi
    HAVING count(*) >= 2
    ORDER BY sukunimi)
ORDER BY sukunimi;

This works somewhat because I get each individual with duplicate surname, but in addition to this I need to be able to have the query use the social security number as well (hetu).

I tried this query, but it didn't work:

SELECT *
FROM jasenet
WHERE sukunimi
IN (SELECT sukunimi
    FROM jasenet
    GROUP BY hetu, sukunimi
    HAVING count(*) >= 2
    ORDER BY sukunimi)
AND hetu
IN (SELECT sukunimi
    FROM jasenet
    GROUP BY hetu, sukunimi
    HAVING count(*) >= 2
    ORDER BY sukunimi)
ORDER BY sukunimi;

How can I use two columns returned from a query in another query?

There are a few way of doing this and depends on the size of your initial table.

Option 1:

/** This gives you all the surnames that are duplicated **/
Create Temporary Table tmpData
(surname CHAR(100) NOT NULL,
counter INT NOT NULL DEFAULT 1,
Primary Key (surname));
Insert Into tmpData
Select surname, count(1) as recs
From (jasenet)
Group BY 1
Having recs > 1;

/** This gives you all the SINS that are duplicated **/
Create Temporary Table tmpData2
(sin CHAR(100) NOT NULL,
counter INT NOT NULL DEFAULT 1,
Primary Key (surname));
Insert Into tmpData
Select sin, count(1) as recs
From (jasenet)
Group BY 1
Having recs > 1;

Select DISTINCT A.*
From jasenet A, tmpData B, tmpData2 C
Where A.surname = B.surname
Or A.sin = C.sin;

Option 2 (Horribly slow -- never do)

Select A.userid, B.userid,
    IF(A.surname = B.surname, 1, 0) as sameSurname,
    IF(A.sin = B.sin, 1, 0) as sameSIN
From (jasenet A, jasenet B)
Where A.userid != B.userid;

Option 3 -- Probably what you're looking for

Select A.surname, group_concat(A.userid) as users
From (jasenet A)
Group By 1
Having COUNT(A.surname) > 1;

Select A.sin, group_concat(A.userid) as users
From (jasenet A)
Group By 1
Having COUNT(A.sin) > 1;

So I gave a few differences but there are a few things I should mention. You are using GROUP BY without an aggregate function which in general means you can have non-deterministic results. Also it's just not how you use group by. What I tell my developers is when using group by it's as if you're building a new table with X fields as the PRIMARY KEY of that table.

Do not use #2 as it has to basically do 1 full scan for every userid, I just wanted to give it to you so you can see that you can double link a table (as long as it's not a temporary table).

Option 1 Is similar to #3 but uses temp tables which are extremely fast and I figured you can then use the temp tables to do your linking on another table. Just remember, temporary tables are gone once you CLOSE the session.

Let me know if this is not what you were looking for


create more logs in Mysql, pros and cons? I just install XAMP in my mac and I'm trying to learn by my own, with books, tutorials and you guys.  I'm working in the backup part and I saw I have only a LOG "mysql-bin.000001' :

What happen when this logs is full??.
Will Mysql create another one by it self??.
Is it possible to have 3 logs and they will delete by itself when the last one is full, like Oracle db does ?.
What are the cons to have more than one log (beside more space)???.
I guess that the Engine doesn't matter, right?

The mysqld process will rotate to the next log by itself when the current binary log reaches 1G (default is 1G, but you can change that by setting max_binlog_size).

Unless you set the expire_logs_days option (By default, it is 0), no logs will disappear.

Keep in mind that restarting mysql also causes log rotations.

If you want to delete logs manually, DO NOT DELETE THEM IN THE OS.

Please use this command : PURGE BINARY LOGS TO 'mysql-bin.000200';

All logs before mysql-bin.000200 will be deleted.

You could also delete logs by timestamp: PURGE BINARY LOGS BEFORE '2012-04-19 00:00:00';

There are no other drawbacks other than wasted diskspace, especiall if the logs have outlived their usefulness.

Storage Engine usage in independent of Binary Logging. DDL for MyISAM and InnoDB can both recorded in the binary logs.


How to shrink "permanent" tablespace? I have a tablespace has size 307,200 MB and "Used (MB)" is 103 229,81. This tablespace was created with "BIG" Datafile. For recent time "Used (MB)" value increase insignificantly. So I want to shrink the tablespace.

alter tablespace MYTABLESPACE shrink space keep 130G

raises

  ORA-12916: cannot shrink permanent or dictionary managed tablespace

Yes. This tablespace is "permanent", but its size is bigger in three times than the used size is, and this fact is a problem for me. How can I shrink it?

The error message is quite straightforward. You are trying to shrink a permanent tablespace using a command to shrink temporary tablespace.

12916, 00000, "cannot shrink permanent or dictionary managed tablespace"
// *Cause: An attempt was made to shrink a permanent tablespace or a
//         dictionary managed tablespace.
// *Action: Check the tablespace type and issue the statement only on
//          locally managed temporary tablespaces.

Example to resize BIGFILE Tablespace.

ALTER TABLESPACE bigtbs RESIZE 80G;

Refer to Altering and Maintaining Tablespaces


Does SSD drive make indexes rebuild and update stats useless with SQL Server? Now that SSD is a standard de-facto. Can the maintenance job of rebuilding indexes and updating statistics, using Ola Hallengren https://ola.hallengren.com/ jobs for instance, be done only on demand instead of regularly such as on weekly basis with Microsoft SQL Server 2017 or lower?

If it is still required, what would be the threshold of the database size to decide when it is necessary ?

I'm going to disagree that SSDs are the de facto standard, though they certainly are far more common than a short time ago. I know my company doesn't set any standards, but nearly all of our virtual database servers still use an auto-tiering partial-flash SAN, against my better judgement

And unfortunately, I'm going to start my answer with an "it depends."

In general, index maintenance is far less important than it was in prior years and older versions - specifically, the long and deep index rebuilds that used to be offline-only. The days of "reorganize everything and rebuild if > 30% fragmented" are pretty much over. I think most would agree that rebuilds are no longer worth doing, and SSDs do play a part in that.

On the other hand, statistics have less to do with fragmentation and physical distribution of data on disks and more to do with logical distribution of data across/among the table(s), so this can still be important. Whether it's important for a given workload depends on a lot of factors.

If you can test stopping these maintenance routines in a non-Prod system, you can look for poorly-performing queries, then analyze the DB engine's steps in the Execution plan. All of the statistics issues I've seen (and I've been dealing with some recently) have been easily visible by looking at the operators in an execution plan and seeing huge differences between "estimated number of rows" vs "actual number of rows". If the queries perform poorly enough, execute a quick statistics update and try again. It's a bit of work going through all of these steps, but once you have a handle on the system you can tell if statistics maintenance jobs are worthless overhead or important and necessary.

Erin Stellato has several great blog posts about statistics that can help with background information on why fresh statistics can be important, and also when they're most useful and how to detect if they're not updated frequently enough.


ORA-28031 while attempting to rebuild or drop index I am in the process of using Data Pump to setup a copy of an Oracle database from 11.2.0.3.0 on Windows to 11.2.0.4.0 on Oracle Linux. I have had a few issues with invalid objects which I have managed to resolve but I also have a number of indexes with the domidx_opstatus of 'FAILED'.

Attempting to rebuild these indexes results in the error:

ORA-29858: error occurred in the execution of ODCIINDEXALTER routine

ORA-28031: maximum of 150 enabled roles exceeded

This is a large database that I have recently begun working on so would like to avoid removing roles from any users to avoid breaking any fuctionality.

Is there away around this or another way of fixing the indexes?

The 150 enabled roles limit is hard coded. There is no way around it other than to remove roles from the user or consolidate the existing roles. Ideally you should do the latter for better long term results. See MOS Doc ID 1101035.1 and Doc ID 780749.1 for more details and some tips on how to identify the relevant roles.