SQL Server: Service Packs and Database Mirroring

Usually the Mirroring feature of SQL Server is pretty resilient to random failures at several parts of the link between the two servers. It is so resilient because it uses only built-in features that are not affected by the outside world – so you can boot your servers, cut and reconect the link, do a lot of stuff and the database will still resume mirroring once everything is good again.

But a Service Pack is something that messes with those built-in SQL features, so this can definetily mess up with your mirroring session. Please be safe and perform these kinds of upgrades gracefully. Kinda, like reading this link: http://www.mssqltips.com/sqlservertip/2701/steps-to-apply-a-service-pack-or-patch-to-mirrored-sql-server-databases/ .

Because if you fail to do so, you may end with two suspended databases. And in this case you will have to take all your servers down, upgrade them all to the same SP level, restart them all, make use of the three little commands noted below, and pray for the database god/goddess/entity to resume your mirroring session. If everything goes fine, you will see your mirroring session back to “Synchonized” state.

See, I can not guarantee that this will work. But, you know, mirroring is resilient.


Edit: I though about creating a new post just to show how resilient Database Mirroring is. But instead I will just do this quick note: even if your mirror server runs out of space, Mirroring will be able to resume as soon as you add some space. You may see the event below in your mirror instance, so be sure to keep an eye open for it (this could cause your main DB Log file too grow a lot too, so you have two symptoms for this situation).

Error: 1454, Severity: 16, State: 1.
Database mirroring will be suspended. Server instance 'YOUR_SERVER' encountered error 5149, state 3, severity 16 when it was acting as a mirroring partner for database 'YOUR_BASE'. The database mirroring partners might try to recover automatically from the error and resume the mirroring session. For more information, view the error log for additional error messages.
Database mirroring is inactive for database 'YOUR_BASE'. This is an informational message only. No user action is required.
Bypassing recovery for database 'YOUR_BASE' because it is marked as a mirror database, which cannot be recovered. This is an informational message only. No user action is required.

To fix it, simply expand the data disks on your mirror server (I’m assuming you know how to do this, since you are reading this kind of article) and issue the command “ALTER DATABASE SET PARTNER RESUME”. Let the Mirroring magic happen, et voilà, database synchronized again.


Posted in Uncategorized | Leave a comment

Free Microsoft exams: get your voucher now!

I was not able to check if there is some exam for SQL Server, but this can help someone anyway.


Quick recap of conditions:

  • Offer good until December 31, 2014 for up to a total of 10,000 vouchers distributed worldwide.
  • Eligible exams: A voucher may be redeemed to take one of the following MCP exams: 71-532 (beta), 70-533, 70-346 or 70-347. This offer does not include Microsoft Technology Associate exams.
  • Offer ends on December 31, 2014 or while supplies last. Individuals must register for and take all exams prior to December 31, 2014.

Exam 71-532 Developing Microsoft Azure Solutions (beta) – This exam is currently in beta.
Exam 70-533 Implementing Microsoft Azure Infrastructure Solutions
Exam 70-346 Managing Office 365 Identities and Requirements
Exam 70-347 Enabling Office 365 Services


Posted in Uncategorized | Leave a comment

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.


Fun quote:

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


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


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.


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.


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

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


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.


Posted in SQLServer | Tagged | 1 Comment

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


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

Get every new post delivered to your Inbox.

Join 47 other followers