Talk about MS-SQL, Oracle, NoSQL and Babylonian accounting tablets

Looking at the past can teach us a lot of things. This is a great way to know more about the things you like. And this is a great article about some fundamental characteristics of the major database systems today.

It’s definitely worth a look.

https://www.simple-talk.com/opinion/opinion-pieces/ninja-immutable-databases/?utm_source=ssc&utm_medium=publink&utm_content=ninjaimmuntable

Fun quote:

You can either have data under transactional control, or you can have anarchy; take your pick. — Robert Young

cya!

Posted in MongoDB, MySQL, Oracle, SQLServer | Tagged , , | Leave a comment

Oracle: Error ORA-00955 name is already used by an existing object

Sometimes things just won’t work as expected. You know, programming has its quirks and the like. Normally this error alone would mean you are creating some object with a name that is already in use. But what if you just deleted the previous object and get this error when you try to recreate it?

ERROR at line 1:
ORA-00955: name is already used by an existing object

ORA-00955 in SQL Developer

ORA-00955 in SQL Developer

In Oracle, this if possible if you are deleting a unique constraint. This happens because a Unique Constraint is also an Index. Consider you just deleted the old constraint and now tries to create the updated constraint with the same name. Bang:  ORA-00955.

To fix it you must also delete the index that has the same name as the Unique Constraint. Then you will be able to create the constraint again, that will recreate the index too (with the same name – so much for the “name in use” rule, right?). BTW, the same happens with Primary Keys.

Actually, this is the kind of thing that makes life an adventure. Not always pleasant, but an adventure none the less. Below are some code snippets for your delight. Remember: Oracle is case-sensitive for text comparisons.

--Drop Unique Constraint
alter table mytable drop constraint UC_mytable;
--Drop Index for Unique Constraint
drop index myschema.UC_mytable;
--List all indexes from a table
select * from all_indexes where table_name = 'MYTABLE';
--List all constraints with a specific name
select * from all_constraints where constraint_name = 'UC_mytable';

And now some links for future reference also:
List of some ORA errors: http://docs.oracle.com/cd/A91202_01/901_doc/server.901/a90202/e900.htm

Some folks with the same problem: https://community.oracle.com/message/9907669 and http://stepintooracledba.blogspot.com.br/2013/03/ora-00955-name-is-already-used-by.html

That’s it for today. Cya!

Posted in Oracle | Tagged , , , , , | Leave a comment

Enable / Disable Users (not Logins) in SQL Server

mauriciorpp:

Revoke connect is not exactly Disable, but the icons are similar. Too much similar. Read on.

Originally posted on Jim Humphries:

A DBA friend of mine came across a strange problem the other day. He discovered that one of the users in a SQL server database was disabled, thats a database user not a server login. At least we thought it was disabled at first, it behaved as if it was disabled and it had the small red down arrow icon that disabled server logins have, but as we all know there is no way to enable or disable database users in the Management Studio GUI.

I had a quick Google and was quite surprised to discover that lots of people had the same ‘issue’ but most peoples workaround was to delete the user and re-create it.

After some more research, it turns out that the user didnt have CONNECT permissions to the database (which I guess amounts to the same as being disabled). You can grant CONNECT permissions using…

View original 49 more words

Posted in Uncategorized | Leave a comment

MS SQL: Find total server disk space in T-SQL

Hello. Quick post today. I needed to find the total disk space for a remote server with access only to T-SQL. No PowerShell or file system sneak-peek was allowed. Actually I would send my queries to someone else run it, and only then I could look onto the results.

So starting of this post from Pinal Dave and this MSDN article I came up with a quick query. It’s not exactly elegant, but gets the job done. SQL 2008 R2+ code ahead.

SELECT distinct(volume_mount_point), 
  total_bytes/1048576 as Size_in_MB, 
  available_bytes/1048576 as Free_in_MB
FROM sys.master_files AS f CROSS APPLY 
  sys.dm_os_volume_stats(f.database_id, f.file_id)
group by volume_mount_point, total_bytes/1048576, 
  available_bytes/1048576 order by 1

This solution was also posted on SQLServerCentral forums. That is a great place to search for such things.

Also get some familiarity with sys.dm_os_sys_info and EXEC xp_msver; - those can be of great help in such dire situations.

cya!

Posted in Uncategorized | Leave a comment

Virtualizing your databases guide

Recently the guys at SQLServerCentral published a great article about SQL Server virtualization. But it is usefull for SQL Server, Oracle or any other DB technology that must run “on the cloud”.

So here is the link to it: http://www.sqlservercentral.com/articles/Stairway+Series/112555/

And that’s it. Go read it over there, but come back here after.

cya!

Posted in Cloud, MongoDB, MySQL, Oracle, SQLServer, VMware | Tagged , , | Leave a comment

Windows 2012 deduplication – turning it off (completely) and recovering your space!

mauriciorpp:

ok, it is always good to know how to turn off things. So here it is: how to reclaim space on deduplicated drives.

Originally posted on HayesJupe's Blog:

A while back i wrote a post about data deduplication in 2012…. generally a very good feature, but as that specific post talked about, there was a collection of .iso and compressed data that not only did dedup not save me anything, it actually used up more space in the dedup folder than the orginal data size (which i found a little odd)

Today i got around to doing something about this and found

  • Disabling data deduplication (via GUI of powershell) only stops further deduplication from occuring – but data that has already been deduplicated will remain deduplicated
  • In order to”move” (re-hydrate ?) the data back to the original file and out of the deduplication store, use the powershell command “start-dedupjob -Volume <VolumeLetter> -Type Unoptimization”
  • You can check the status on where this is at by using get-dedupjob, or i like using TreeSize which shows the size on disk of…

View original 303 more words

Posted in Uncategorized | Leave a comment

SQL Server 2012 Management Studio download

And finally it’s time to update my post on SSMS 2008 download with the new version, for everyone joy. Well actually we are on SQL2014 now, but as any brand-new Microsoft product, it will take some time until most of us are using it. The cost of updating existing servers is not exactly interesting – the newer versions will be used on mainly on new projects for a while.

And this brings me to the current post. Where do I download the 2012 version of just Management Studio? Actually this has been answered already, and in great detail, in this nice article by Jason Strate: http://www.jasonstrate.com/2013/05/get-just-the-tools-ssms-download/  (notice it’s from 2013).

The key here is to look for the Service Pack 1 product page of SQL 2012 Express. If you searching for the RTM version you will have less luck, as I pointed out on my first post about SSMS 2008r2 download. I strongly recommend the x64 version. But if you need the BIDS/SSIS pieces, you will need the full SQL installer and not these (as pointed out in my previous post).

Enough talk, go to the SQL 2012 SP1 Express page here: http://www.microsoft.com/en-us/download/details.aspx?id=35579, enter your language and look for the files named “SQLManagementStudio_x64_ENU.exe” or “SQLManagementStudio_x86_ENU.exe“.

As with my previous post, below are the direct links too (for english language – actually the only version you should consider):

SQL 2012 SSMS 64bits with SP1 direct link: http://download.microsoft.com/download/5/2/9/529FEF7B-2EFB-439E-A2D1-A1533227CD69/SQLManagementStudio_x64_ENU.exe

SQL 2012 SSMS 32bits with SP1 direct link: http://download.microsoft.com/download/5/2/9/529FEF7B-2EFB-439E-A2D1-A1533227CD69/SQLManagementStudio_x86_ENU.exe

PS 1: while doing research for this post, I came across some great links about SQL lifecycle and support: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/2a9e52dc-4f21-41b1-8640-f0c2476c68d8/most-used-sql-server-version?forum=sqlsetupandupgrade

PS 2: stay away of SQL Express with Advanced Services, unless you really really want a database to run in your local workstation. This version is suited for developers who need a local DB – for remote server administration work you should only use the SSMS version without the local database engine.

cya!

Posted in SQLServer | Tagged | 1 Comment

SQL SERVER – Good Value for Page Life Expectancy – Notes from the Field #026

mauriciorpp:

Todo List: PLE checks.

Originally posted on Journey to SQL Authority with Pinal Dave:

[Notes from Pinal]: In the past, I have been wrong many times, but I was man enough to accept my mistakes and correct myself. Page Life Expectancy is a very similar subject for me. In the past when I had written a blog post based on Microsoft’s white paper, I was corrected by SQL Experts immediately for my error in judgement and incorrect information. I accepted my mistakes and corrected it. I just shared this story with my good friend Tim Radney and he was very kind to give me guidance on this subject. I asked him if he can write further on this topic and help people understand this complex subject in simple words.

Linchpin People are database coaches and wellness experts for a data driven world. In this 26th episode of the Notes from the Fields series database expert Tim Radney (partner at Linchpin People) explains in a…

View original 328 more words

Posted in Uncategorized | Leave a comment

Red-Gate SQL Monitor: issues with Windows Registry

Earlier today I opened a topic on Red-Gate forums, about one issue I found while testing the soon-to-be-discontinued SQL Monitor Hosted service. That is a great service, and it was free while in beta, but unfortunately I did not have enough time to play with it (or write about it). Altough I really wish they extend the product a couple more months, I will post this message with the hope that it could help someone on the future – either with Hosted or On-Premise versions of SQL Monitor.

I was having some errors while trying to set up the monitoring on my Windows Server 2008R2 box, all related to the registry key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones”

It turns out the server did NOT have a valid TimeZone configured. It is easy to check this by cliking on the clock on the taskbar. Once the correct timezone was selected, this error stopped happening, resuming monitoring activities.

See the full post here: http://www.red-gate.com/MessageBoard/viewtopic.php?p=69395#69395

cya!

Posted in SQLServer, Windows | Tagged , , , | Leave a comment

Oracle: view Error Log and connection issues.

Usually I try not to mess with Oracle, and in return it tries not to mess with me. Relationships are difficult when both parties have pretty strong characters. But today Oracle was angry at me, and I needed to do something about it. So SSH here I go!!!

Well, actually a user was asking me if Oracle service was up or down. Basic checking on Enterprise Manager told me it was up. But I wanted to see the logs and look for unusual messages. Unfortunately this is not so easy to find, so here I am writing a small post to help whoever needs this information.

The error log location is customized for each install, so I can not point you to a single default location. It usually stays inside your ORA_HOME folder, somewhere around /diag/rdbms/<instance/something>/trace, but this could be anywhere on your filesystem. To find out where your Oracle log file is located, connect to your database by your prefered way, then query this:

show parameter background_dump_dest; 

The main log file is “alert_orcl1.log”, so you could just locate/search for it too. When you find it, you will have to go back to Operating System and check the file/tail it. You can read more about this here. Also, if you are already troubleshooting, you could take a look at this great article about How to troubleshoot Oracle remote database connection. It’s a rare sight to see such a clean and complete article about some Oracle errors.

Also while we are here, you could read this article too if your Oracle is refusing connections. The query below will show you the configuration and current usage of your available connections.

select * from v$resource_limit where resource_name = 'processes';

And if you are having connection issues, it could be usefull to see who is currently connected on your database. The sample below is from this forum post.

select
substr(a.spid,1,7) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,15) box,
substr(b.username,1,8) username,
b.server,
substr(b.osuser,1,8) os_user,
substr(b.program,1,23) program
from v$session b, v$process a
where b.paddr = a.addr and type='USER' order by spid;

Have you noticed that I like to link whatever sources I find about the subjects posted here? I don’t intend to give just tips, but also link more source material to be used as research. Usually these articles have way more information than what I quote, so it is worth to take a look at them.

cya!

Posted in Linux, Oracle | Tagged , , , , | Leave a comment