Could not access Maintenance Plans in Management Studio

Error :
Method not found: 'Void Microsoft.SqlServer.Management.DatabaseMaintenance.TaskUIUtils..ctor()'. (Microsoft.SqlServer.MaintenancePlanTasksUI)

Investigation : 
SQL Server 2005 Management Studio is unpatched (9.00.1399)
Running SELECT @@Version shows the database engine is to be at Cumulative Update 9 (9.00.4294).

Solution :
Apply Service Pack to Management Studio

Ref : http://mbsturk.blogspot.com/2010/03/method-not-found-void.html

Fragmentation 101

Have had to explain this a number of times recently, hence ....

Inside the database -
 
SQL External fragmentation
External fragmentation is also known as logical fragmentation.
It means the index is jumbled up, is in the wrong order! Accessing the index is no longer a sequential operation.

If the index fill factor does not leave space for new leaf pages, page splits happen and external fragmentation occurs. External Fragmentation becomes more of a problem returning larger or ordered results sets.


Schedule regular index maintenance to prevent this.

SQL Internal fragmentation
Internal fragmentation is the reverse scenerio i.e there is too much free space in the index pages.
Extra reads are issued to return all the data, hence it takes longer to fulfil the I/O operation.



Look at setting index Fill Factor options and rebuild indexes to correct this.


Outside the database -

File system Fragmentation

This is caused by growth of physical data and log files.

As a DBA you manage growth manually. If you don't, the scenario described in Bad Autogrowth happens, i.e. physical fragmentation of sql server files.

Resolving this requires -

1. Resizing your data files appropriately
   and either -
2. Taking SQL offline to run defraggler or similar
   or
3. Taking SQL offline and moving the files off and back onto the drive.

In my recent experience option 3 is quickest.

Renaming SQL Server Instances

Working with virtual machines frequently now I often duplicate entire systems for testing purposes and rename the newly created machine. When SQL Server is included in these images, the new copy is left not knowing what it's own name is.

To tell SQL it has been renamed, you need to run the following.

sp_helpserver
select @@servername
go
 
sp_dropserver 'OLDSNAME'
go
sp_addserver 'NEWNAME','local'
go
 
sp_helpserver
select @@servername
go

Remember you will need to restart the SQL Server service after executing this. You could even do it automatically using this Rename SQL Installation script from back in the day. It will detect the names automatically.

SQL Server : What is running right now?

SQL 2000

select * from master.dbo.sysprocesses where status <> 'sleeping'

SQL 2005+

select * from sys.sysprocesses where status <> 'sleeping'

Easier by far, use Adam Machanic's 'Who is Active'

• sp_whoisactive 

PS : I've told you twice now!

SQL People Interview

My SQLPeople Interview was published today. You can read it here -

http://sqlpeople.net/admin/2011/10/03/richard-doering/

SQLPeople is the brainchild of SSIS Guru Andy Leonard (who I met when he flew over to the UK last week to present at SQLBits 9). It is a community project running events in the US (as well as the website). I found the site recently and enjoyed reading the DBA interviews. More proof if it were needed of the wide range of roles the term 'DBA' can encompass.

r

SQL Server in the Evening (4th Event)

This evening was a first for me. Fresh (knackered) from helping out at SQLBits at the weekend I faced one of my demons and made my debut at public speaking.

The opportunity was provided to me by Gavin Payne (@GavinPayneUK)  and it was tonight at his 'SQL Server in the evening' event that i took the beginners slot. The event is a SQL Server user group that runs in West Surrey. Tonight's venue was the Ramada Hotel on the Hog's Back in Farnham.

My session was first (no pressure) and was entitled CSI SQL : Auditing SQL Server. I felt I did ok but was initially rather nervous. My content addressed the way I have recently approached a large number of audits of SQL Server installations. It had quite a wide scope and was more of a memory jogger than a technical indepth talk. I finished by presenting the spreadsheets and scripts I developed to quickly audit systems. On a side note I loved the usb powerpoint clicker and have since put one on my birthday wish list (should I get the whim to present again!). I enjoyed doing it and found the positive feedback extremely encouraging.

After an delicious but delayed buffet (my fault for overrunning my time slot) we were treated to 2 further presentations. The first was an inspiring deep dive into PowerShell by Pete Rossi   (@RossiPete).  Powershell is high on my 'must play with' list for automating administration so I found this quite an eye opening session. Finally AlwaysOn functionality in Denali was the topic for Microsoft Certified Master Christian Bolton. Christian spent an hour explaining the how High Availability in SQL Server 2012 has improved.

A great evening overall and a large weight lifted for me by public speaking.

r

Windows 2003 : Reviewing Cluster Logs

Spent some time corresponding SQL events with the cluster event log today. A cluster failing over seemingly without good cause. Look for - Source - Clussvc Category - Failover Manager

The cause will be another blog post!

SQLBits 9

The 9th SQLBits was held in Liverpool this weekend. Despite it being 226 miles away from home that was no deterant for a few days of quality SQL content, company and beer.

This was my 5th SQLBits and I volunteered to help. This meant room monitoring (helping spearkers, tidying up etc) , helping attendees (mainly find rooms) and other light duties. For me volunteering gave me a purpose between sessions. Knowing the distributed nature of my work meant I was not able to furfil purchases from the exhibiting vendors I prefered not to add myself (once again) to their mailing lists. There is also a limit to how many conference freebies even my kids would appreciate!

The volunteering meant some of my sessions were allocated for me. This was fine as volunteers had given session preferences so they wouldnt miss content they needed. Ensuring everything went smoothly was expertly organised by Annette Allen (@Mrs_Fatherjack). I'm already looking forward to SQLBits 10 and would encourage SQL Professionals to make this a regular date. As a learning and social experience, SQLBits (and the SQLBits community) rocks!

The sessions I attended this time round, were -

Performance tuning from the field
Simon Sabin

Lightning Talks
Various Speakers    

Myths and Monsters of Flash
Fusion-io

Advanced SQL Server 2008 Troubleshooting
Klaus Aschenbrenner

The Art of War-Fast Track Data Warehouse & Fragmentation
James Rowland-Jones

Designing an SSIS Framework
Andy Leonard

Building a SSMS Add-in; The Agony and Ecstasy
Mark Pryce-Maher

Understanding SQL Server Execution Plans
Klaus Aschenbrenner    
   
Through the virtual looking glass – monitoring virtualised SQL
Gavin Payne

Whats new in Denali-TSQL
Dave Ballantyne    


Other SqlBits 9 Coverage :

• Richard Douglas
• Victoria Holt
• Mark Broadbent

Update, November 2011 : 
The conference videos have been uploaded in record time and are now available at sqlbits.com