Broken MDF, Broker, SSIS Ragged Right, SSRS Remote Error, Amazon S3, DBA Skills

Today has been quite an eventful day for me, feels like a mishmash of stuff happened today. Definitely worth a blog post.

Here are some of the things I learned and did today.

And man it amazes me how many smart smart helpful people are out there 🙂

Service Broker

I possibly might need to work on SQL Server Service Broker. I think this might be the right tool for a project, but I need to be certain so I need to give it a test drive.

Thanks to Aaron Bertrand (blog | twitter) for pointing me to these excellent excellent resources on SQL Server Broker.

http://mrdenny.com

http://http://itknowledgeexchange.techtarget.com/sql-server/tag/service-broker/

http://rusanu.com

Attempting to recover damaged mdf file

I have a corrupted mdf file that won’t even attach to Management Studio. Tried attaching without the log file, tried attaching using sp_attach_single_file_db.

I also tried an excellent post from Paul Randal (blog | twitter).

http://www.sqlskills.com/blogs/paul/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx

As per his blog post, I created an empty database with mdf/ldf file that are as close to the size of the original mdf file, shut down the instance, then swapped the corrupt files. Success, I was able to “attach” the database as suspect.

However when I ran the DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS .. unfortunately it’s a no go. At this point I am still stuck. I was hoping there’s a way from within SQL Server tools that I can still use, fix, or repair the corrupted mdf file.

I’ve had to look at a few third party tools .. so far it looks like Stellar Phoenix MDF Repair Tool was able to “view” the contents of the broken mdf file. Not completely sure yet if I want to go this route, so I think I’ll do a bit more poking around.

Using SSIS Ragged Right

I needed to export some records from SQL Server to a fixed width text file using SSIS.

Normally if I export to a text file I’d use some character delimiters. This is the first time I’ve had to use fixed width. I proceeded with a Flat File Destination, and chose Fixed Width as the format (because that’s what I want). However, when I opened my resulting file, lo and behold, all my records are there BUT all in one line!

Felt like maybe I just missed something. Why would all be the records be in just one line? It turns out, Fixed width and Ragged Right formats are almost exactly the same with one *minor* difference. The only difference: Ragged Right has newlines, Fixed Width does not.

http://weblogs.asp.net/guystarbuck/archive/2008/01/31/ssis-flat-file-export-quot-fixed-width-quot-vs-quot-ragged-right-quot.aspx

Enable Remote Errors on SSRS

We had a weird random error on some of our reports, and just had to turn remote errors on. What I love about this, no need for IISReset.

http://technet.microsoft.com/en-us/library/aa337165%28SQL.90%29.aspx

Sharing Big Files with Amazon S3

Very timely article from Brent Ozar on how to save and share big files. I love how he clearly explains how you go about the whole thing – from end to end.


http://www.brentozar.com/archive/2010/10/sharing-big-files-easily-with-amazon-s/

Are DBA Skills Necessary?

Last but not least, read this article from Jeremiah Peschka (blog | twitter) .. a DBA must read:

http://facility9.com/2010/11/02/t-sql-tuesday-why-are-dba-skills-necessary

It’s short and sweet, and straight to the point. Puts things in perspective.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s