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.