Aug 282015 Tagged with , 0 Responses

Data Recovery and Fixing Database Corruptions @DBA Lounge

I had recently presented to local DBA and Developers community in Iasi (Romania), a session on database corruptions and data recovery techniques in SQL Server.
It was interesting to discuss pros and cons of the approaches used in the demo, root causes for the corruptions, finding differences and correspondences Microsoft SQL Server vs. Oracle.
This was a touching base with Microsoft SQL Server storage internals and in the same time a “deep dive” into what can be done in order to protect/recover data.

The DBA Lounge group started few years ago on Linkedin, as we wanted to know each other better, share knowledge and have fun. The community is no technology specific. Anyone interested in database systems is welcomed.


poster-dba-lounge-20150827-scc
 

Read More…

Jun 032015 Tagged with , 4 Responses

Convert LSN to Numeric

As part of the Database Corruption Challenge I needed to convert the LSN values returned by fn_dblog() / fn_dump_dblog() to numeric.

The math is not very simple and it is best described by Paul Randal.

  • Take the rightmost 4 characters (2-byte log record number) and convert to a 5-character decimal number, including leading zeroes, to get stringA
  • Take the middle number (4-byte log block number) and convert to a 10-character decimal number, including leading zeroes, to get stringB
  • Take the leftmost number (4-byte VLF sequence number) and convert to a decimal number, with no leading zeroes, to get stringC
  • The LSN string we need is stringC + stringB + stringA

Read More…

May 242015 0 Responses

SQL Server Blogs Aggregated Feeds

Personally, I like to stay up to date with technology related items. I read blog posts, books, knowledge base articles, etc.
To help the community and “make things easier”, I have build a page with links to blogs that I follow.
Blog posts are sorted and aggregated, using a nice WordPress plugin.
The page is available here.

Enjoy it.

May 132015 Tagged with , 0 Responses

Database Corruption Challenge

Last week I came across an interesting DBA challenge. Steve Stedman launched a 10 weeks “Database Corruption Challenge“.

The interesting part, so far, is that data can be recover 100%. To me, this is the actual challenge: how to be 100% sure that all data was recovered?!

From recovering data using indexes, to fixing page headers using DBCC WRITEPAGE or Hex editors, this is an excellent opportunity to practice recovery skills.

I will discuss about my solutions to each of the challenges, in future blog posts.

May 082015 Tagged with , 0 Responses

SQL Server Cluster P2P migration

Motto: “it should be out there”

Recently, my team was involved in several SQL Failover Cluster Instances migration. All nodes were physical boxes and migration was done between two Data Centers.

Due to business constraints, we had no option but to rebuild the clusters in the new Data Center, keeping everything as before and with as minimum downtime as possible.

Migration should be transparent to all applications/users that connect to the instances.

Nice challenge, we said.

Single instance migration was documented and you may find it at:

http://yrushka.com/index.php/database-recovery/sql-server-migration-from-one-server-to-another-detailed-checklist/

How about cluster migration? The steps we took to ensure all migrations were successfully are listed below.

I know.. most of the steps are only backups/information and may be skipped. A backup may never suffice. When downtime is important, any piece of backup/information is valuable.

Always do a test before, check whatever steps apply to your environment, add others, document the procedure and go live. Read More…

Apr 102015 Tagged with , 0 Responses

DBCC CHECKCONSTRAINS & Error 446

On a customer database, on an empty table, DBCC CHECKCONSTRAINTS failed with the below message:

Msg 446, Level 16, State 11, Line 1
Cannot resolve collation conflict between “Latin1_General_BIN” and “Latin1_General_CI_AI” in CASE operator for DISTINCT operation.
Msg 2509, Level 16, State 2, Line 1
DBCC CHECKCONSTRAINTS failed due to an internal query error. Please run DBCC CHECKDATABASE to ensure your data consistency.

According to MSDN, indeed a temp table is created on tempdb. So I suspected (and still am) a coding bug: when database collation is different from the server one, in case of a join on char types columns, the collation should be specified.

Read More…

Feb 102015 0 Responses

a DBA journey

It’s been many years now since I first got my hands dirty on databases “world”.

In 2000, as a young student and being on my first job, I had to choose for the first time.. between SQL Server (6.5 back then) and MySQL (version 3). I have picked MySQL because it was free. In 2003, I had the luck of being asked to substitute the DBA colleague leaving to another company.

And my journey with SQL Server has started.

What times.. being sole DBA for production (24/7) and development.
Hundreds and hundreds of scenarios tested, tens of thousands of SQL code lines, short nights (not only due to work… friends, cigarettes, whiskey, beers, work and always.. good music).
After my leave in 2005, I was asked to mentor the colleague that was replacing me. Some people may say I was tough.

Since ever, I was a self-thought person. I had and still have few words that define me: “keep digging and never stop learning”. To my mentees (yes, I had more than one during time ), I have tried to inoculate these words.

Read More…