2012 in review !

Posted on by
Reply

Year 2012 was super special for me in many ways. Made lot of new friends, Attended SQLPASS Summit 2012 and had the privilege to meet and greet #SQLfamily members, New learnings, Multiple SQL2012 deployments, Lot of writing and Loads of fun !!!

All about resolutions -

One of the major resolutions for me in 2012 was to explore Virtualization more and move away from standard perception that “All Production environments should be deployed on physical hardware”. By the growing capabilities of virtual infrastructure the move to the virtual world was pretty much an easy one and I’m loving this change.

Attending SQLPASS Summit 2012 -

Dream come true event for me. My 3 key takeaways of PASS Summit was

New friends, Learning & Fun

Sharing and getting involved with SQL community -

I learned a lot by trying to help and answer questions via forums, twitter tags etc. This is a wonderful platform to get engaged and solve real-time issues.

Started sharing my thoughts on SQLServer via a much bigger platform SQLServerCentral.com,all my notes are available under http://www.sqlservercentral.com/blogs/sqlsailorcom/

Here is a quick overview of SQLSailor for 2012 -

Click here to see the complete report.

Overall year 2012 was a great one and I’m looking for a wonderful 2013.

Thanks for reading and wishing every a Happy and Prosperous New Year !

Next Gen File System ReFS(Resilient File System) and SQLServer !

Posted on by
Reply

“ReFS”, this term was absolutely new to me when I was reading this white paper from Microsoft ‘Storage Windows Server 2012′.

ReFS is the next gen file system from Microsoft, and available in Windows Server 2012.

Here are some of the key ReFS benefits and capabilities -

1. Robust disk updating  - This avoids problems associated with power failures during disk updates.

2. Data Integrity - This ensures detection of all forms of disk corruption and it uses checksum.

3. Availability - Ensures that the whole volume will never go down when there is a corruption.

4. Scalability - ReFS is highly scalable and its a great benefit considering the fact that data demands are getting high and high.

5. Proactive error identification  - Scans the volume for errors.

For the complete list and detailed explanation refer the below mentioned white papers and blog posts -

Optimized Storage Efficiency with Windows Server 2012

Windows Server 2012 – Storage

Building the next generation file system for Windows: ReFS

 

Being a SQLServer professional my next focus was to understand how ReFS is going to benefit my SQLServer configurations, and to my surprise there was no real good news !

Denny Cherry(B/T) has a great blog post on why SQLServer will have issues if we run on ReFS. I would encourage you to read that post now.

This is mainly because some of the NTFS features such as named streams and sparse files are not supported in ReFS. SQL Server uses these features for DBCC CHECKDB and Snapshots.

Without using named streams DBCC CHECKDB will lock objects and this is not a desirable situation from everyone’s perspective.

I personally would like to test these situations and see what all scenarios comes up.I’m setting up an environment for this very purpose and looking forward to come with more details.

Conclusion -

If you are deciding to leverage ReFS for SQLServer deployments, then you would need to think twice and hold on to the decision.

Plan cache bloat by SQLAgent (Prior to SQL2008) !

Posted on by
Reply

Recently I was checking the plan cache utilization with the help of the below query which I normally use for this purpose. This is written by Glenn Berry(B/T) and is available along with his SQL Server 2005 Diagnostic Information Queries.All credit goes to him.

SELECT TOP(50) [text] AS [QueryText], cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE cp.cacheobjtype = N'Compiled Plan' 
AND cp.objtype = N'Adhoc' 
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC OPTION (RECOMPILE);

This query will pull top 50 single use ad-hoc queries which are bloating the plan cache. Glenn has mentioned that SQLServer Agent will create lot of ad-hoc single use queries in SQL 2005,however I had never noticed that before.

That day when I ran the above query I received multiple statements like

BEGIN TRAN UpdateMediaTables
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @BackupSetId int, @MediaSetId int, @LogDevName varchar(512)
SELECT @MediaSetId = media_set_id
FROM msdb..backupmediafamily AS bmf WITH (NOLOCK)
WHERE substring(bmf.physical_device_name,5,36) = '98E410FA-78ED-43BE-864F-C4F014EBD055'

This confirmed the fact that SQLAgent indeed fires lot of ad-hoc queries and that was not a good sign.

I was curious to check more and see if this issue was reported anytime to Microsoft and wanted to know if SQLServer product team did fix this issue for releases after SQL 2005.

I was able to find this connect item which was dealing this issue, and on this item Microsoft confirmed that they have fixed this in SQL2008.

I’m not sure for which SQLServer 2008 SP or CU did Microsoft released this fix. Dave Ballantyne who opened the above connect item have also mentioned that this fix is in place for SQL2008 R2 too.

Yes,we have yet another reason for moving forward with upgrading your SQLServer environment to run the latest builds.

Thanks for reading.

PASS Summit 2012 News – Check- in Experience!

Posted on by
Reply

Registration and Check in for PASS Summit 2012 was opened yesterday (Nov 4th), and I decided to utilize this opportunity and check-in little earlier than the first day of the conference (Nov 7th).

A great benefit of completing this well before the conference day is I can avoid checking in during the busy conference day morning. I can spend more time meeting and talking to my friends whom I’m eager to meet.

Yes, I mean it! Really excited to meet you all and make friends for a life time!

I reached Washington State Convention Center with my wife Deepthi by around 5.15 PM, and we were happy to see PASS Summit 2012 posters/banners guiding us to the exact location where the registration/check-in was happening. No confusion what so ever!

There was already a decent queue when I reached the check-in area, and I could see happy faces all around me. Everyone looked really charged and excited about PASS Summit.

I’m new to PASS Summit and was super excited to check-in for the greatest SQLServer event in the world.

I had to stand in queue for like 5 minutes and finally when my turn came, the lady who was responsible for check-in happily inquired my details and handed over me the PASS Summit 2012 kit.

Thats it ! All set to attend PASS 2012 Summit and have a great time learning new things and meeting friends. Connecting with people is the most important part of this conference and I’m really looking forward for this.

There are 2 very important events occurring tomorrow, one is First-Timers Orientation Meeting between 5.15 PM – 6.30 PM PST and PASS Summit 2012 Welcome Reception between 6.30 PM – 8.00 PM PST.I will be attending both the events and will write a detailed blog post with lot of photos.

Check out my flicker stream for all the PASS Summit 2012 photos,I will keep updating this stream during the next few days.

Thanks for reading.

PASS Summit 2012 Countdown Begins !!!

Posted on by
3

All roads lead to Seattle!

If you are a SQLServer professional, then you don’t want to miss this event.

Over 190 technical sessions, 2 days of pre-conf sessions with in-depth learning opportunities, networking with MVPs/Microsoft CSS/SQLCAT members! This is what PASS Summit is all about, and the great news is that you still have chance to register for this mega event.

PASS Summit 2012 will be my first SQLServer conference of this magnitude, and I’m very much humbled and truly excited to get a chance to attend this event.

So when is PASS Summit 2012 happening -

The main event happens from Nov 7th – Nov 9th 2012, however there are 2 days of exclusive pre-conf sessions happening from Nov 5th – Nov 6th.

Count down begins !!!

A complete list of pre-conf sessions and regular conf sessions are made available on the PASS website and you can refer it here.

PASS Summit is not just about attending tech sessions. Apart from learning new things there is great scope for networking and talking to different people with whom you interact over twitter and other social networking means. This means a lot to me and I’m really looking forward for it.

So where’s all the fun is happening?

It’s happening at Seattle, one of the beautiful cities in Washington State(Ever green state).

If you are new to Seattle and also a first timer for PASS Summit, then I would really recommend you watching the on-demand SQL PASS 2012 1st Timers Webcast by Denny Cherry(B/T). Denny provided lot of information during his hour long webcast which includes key information about after hour parties which is very important from networking perspective.

The main conference event occurs at Washington State Convention Center and here is a walking map from the closest transportation station to the convention center -

Apart from attending conf sessions and after hours parties, what else you can do at Seattle. Here are some of my suggestions. I’m local to Seattle and been exploring this beautiful city since last year -

1. Capture this view with your cam and share with your friends and family

[Image source, courtesy - http://www.flickr.com/photos/bryce_edwards/2360672546/ ]

This is Seattle skyline view, and you will get similar great views from Kerry Park

Reaching Kerry Park is very easy and in case you are not driving a car, then the best option is to catch a bus.Approx time to reach the park from downtown via bus in 20 minutes.

2. Ride the ducks of Seattle

This is a mini Seattle tour option, and this ride is fun and exciting. You will see the key places of interests in and around Seattle.You will also get a chance to ride through water.

Your ride captain might wear some funny caps like these.

Check out the details at http://www.ridetheducksofseattle.com/

3.  Pike place market and fish throwing.

Pike place market is easily one of the happening places in Seattle. It’s always a fun place to be,and you will get everything except quality SQLServer training here ! Be it food,drinks or even fresh flowers.

It’s very close to the convention center.

You can also witness ‘ fish throwing ‘ action within the market too. This fish throwing is a tradition. People working in the fish market will be throwing fish that customers have purchased, before they are wrapped.

Here is a shot which came out of my camera couple of months back.

4. Space needle

Space needle is a major landmark and a beautiful tower in Seattle. You can purchase tickets to take lifts and reach the observation deck of the tower to get magnificent views.

Details about pricing and other key information are made available here.

View from the top of the tower is truly amazing.

Reaching space needle is also very much easy and you can either decide to take a quick walk (0.9 miles from West lake center) or take bus.

5. EMP Museum

EMP Museum is dedicated for music. Its founded by Paul Allen (Microsoft co-founder)

Its a nice place to spend time exploring the history of music and also we have a community appreciation party at EMP during summit.

Here are some of my photos from the museum -

Roots and Branches Guitar Sculpture

Big screens with rock music

I am really looking forward to meet you all during PASS Summit 2012 and have a great experience.

See you all soon.

Thanks for reading.

Tale of detaching a database which was enabled for replication!

Posted on by
Reply

In development environments I prefer to use ‘detach’ database option to move around databases. It’s simple, fast and easy.

Almost all the time when I detached a database earlier the feeling was like something which I mentioned before “Simple, fast and easy”, however today it was more sort of a fun experience. Here is that tale for you all.

Today I had to ‘detach’ a database from one of my development environments and straight away got this error !

 

 

[Pic courtesy : temzlandia.blogspot.com ]

The database was setup for replication, hence it cannot be dropped.

Apparently, my next step was to remove the publication which was setup for this database and the tale begins from this point.

Note - This was a very controlled dev environment and action like detaching, removing replication was not going to harm anything. In case you are going to repro this demo, then I would recommend you to do this on your lab environment.

I selected the required publication and went ahead with delete operation. The wizard was double checking with me if I was sure. I said Yes to it !

Bang! here comes the next error

The error was pointing that there are some connection issues to the Distributor or to the Subscriber. 

The distributor and the subscriber for this publication were already retired servers and not online and removing publication requires a connection to be made to the distributor/subscriber servers too. As those servers were not reachable, the wizard couldn’t proceed.

There is no way you could delete this publication directly using SSMS and the best way to do this is via system stored procedures.

I had to use 3 system stored procedures to cleanup this publication and those are -

sp_dropsubscription

sp_droppublication

sp_replicationdboption

The scripts used for the clean up processes are mentioned below -

--Cleans up the subscriptions for a publisher

USE <Your Database>
GO
EXEC sp_dropsubscription @publication = N'Your_Publication', 
@article = N'all', @subscriber = N'all', @destination_db = N'all', @ignore_distributor = 1

--Cleans up the publication

USE <Your Database>
GO
EXEC sp_droppublication @publication = N'Your_Publication', @ignore_distributor = 1
--Set Replication database option to false

USE Master
GO
EXEC sp_replicationdboption @dbname = N'Your_Publication', @optname = N'publish', @value = N'false'
,@ignore_distributor = 1

If you note the scripts you can find that they all have an argument @ignore_distributor passed as 1.All the three scripts were failing without this as the connection to distributor was not available as it was offline.

I was curious to check what exactly @ignore_distributor does, however the below BOL entry didnt gave me any clue -

[ @ignore_distributor = ] ignore_distributor

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

It’s very much clear that removing a publication will check connections to the Distributor and in case it’s not available for connection, there will be connection errors.

After running the above mentioned stored procedures I could proceed and detach the database.

Thanks for reading.

Backing up the “log” of a database which is in Simple recovery model ! – Watch out for this!

Posted on by
2

In theory we cannot backup the log of a database which is in Simple recovery model, however there is a small bug in SQL 2012 which allows you to do this, and you need to be very careful about this.

Below is the detailed repro steps for this issue and the testing has been done using SQL 2012 RTM bits.

1. Change the recovery model of Model database to Simple.

2. Create a new database.

3. The newly created database will be having recovery model as simple as it inherits the properties of the model database.

4. Backup the newly created database.

BACKUP DATABASE Test_LogBackup
TO DISK='I:\Test_LogBackup_Full.bak'
WITH STATS=1

Processed 264 pages for database 'Test_LogBackup', file 'Test_LogBackup' on file 1.
100 percent processed.
Processed 2 pages for database 'Test_LogBackup', file 'Test_LogBackup_log' on file 1.
BACKUP DATABASE successfully processed 266 pages in 0.570 seconds (3.632 MB/sec).

5. Now try to backup the log of this database which is in simple recovery model

BACKUP LOG Test_LogBackup
TO DISK='I:\Test_LogBackup_Tran.trn'
WITH STATS=1

Processed 3 pages for database 'Test_LogBackup', file 'Test_LogBackup_log' on file 1.
BACKUP LOG successfully processed 3 pages in 0.003 seconds (5.371 MB/sec).

This behavior is not expected and this was reported by one of the users(@nayanraval) on twitter. After multiple successful repro’s the user was suggested to open a case with Microsoft PSS group for a quick resolution.  A connect item is also open for you to sign-in and vote.

https://connect.microsoft.com/SQLServer/feedback/details/765315/when-model-db-is-set-to-simple-recovery-newly-created-databases-are-not-truely-in-simple-recovery

I will keep watching this connect item and will update this post once it’s resolved or we get a response from Microsoft on this behavior.

Thanks for reading.