Mathom Solutions Blog-IT

• Carpe Datum | Buck Woody
  Buck Woody's (blog | twitter) awesome series of blog posts and links to better help you prepare for the 70-432 SQL 2008 Implementation and Maintenance certification.
  (tags: sql2008)
• Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail - SQLServerCentral | Jonathan Kehayias
  Jonathan (blog | twitter) provides the best solution involving a certificate signed assembly to call sp_send_dbmail. This solution prevents one from adding users to msdb and the DatabaseMailUserRole.  It removes the risks associated with having TRUSTWORTHY ON setting enabled.
  (tags: sqlserver certificates security)
• Working with the New SQL Server 2008 Collations and Earlier Versions of the Native Data Provider - Whitepapers | Microsoft
  This Microsoft Whitepaper is on Microsoft SQL Server 2008's new Windows collations (denoted by *_100 in the collation name) that were added that make it in full alignment with the Windows Server 2008 operating systems.  (Authors:  Fernando Caro with technical review by immy Wu, Sanjay Mishra, Qingsong Yao, Juergen Thomas, Burzin Patel, Wanda He, and Tres London)
  (tags: sqlserver Unicode collations sql2008 Whitepaper)
• CSS SQL Server Engineers : How It Works: Are you handling cancels correctly in your SQLCLR code? | CSS SQL Server Engineers | Microsoft
  The CSS SQL Server Engineering team (blog | twitter) discusses that when a query is cancelled and it is currently executing in the SQLCLR that an System.Threading.ThreadAbortException exception is raised. It seems harmless enough to add a catch or finally block to your code to cleanup necessary resources. What they found is that it is not as easy as catching the ThreadAbort call. In some scenarios, the ThreadAbort is sometimes upgraded to a rude abort and the catch/finally block is not executed.
  (tags: sqlserver CLR Error_Trapping)
• Using Star Join and Few-Outer-Row Optimizations to Improve Data Warehousing Queries | Microsoft
  In this Microsoft white paper, there is a good discussions about two new features in Microsoft SQL Server 2008: Star Join and Few-Outer-Row optimizations. Microsoft tested the performance of SQL Server 2008 to highlight the effect of these two features and how they got a significant performance gain (about 75% gain and a reduction in the number of rows processed) over SQL Server 2005 without these two features.
  (tags: sqlserver performance datawarehouse star schema Whitepaper)

• SQL Server 2005 – How To Move 10 Millions Rows In 1 Millisecond | SQLServerPedia | Adam Haines
  Adam Haines (blog | Twitter) asks "How would you move 10 million rows?"  Traditionally, developers would use SELECT INTO or a INSERT INTO statement.  While this way accomplishes the task at hand, it is not nearly fast as using the SWITCH partitioning command.  It will save you tons of time!
  (tags: sqlserver performance SWITCH)

• Troubleshooting Microsoft SQL Server : Transactional Replication Conversations | ChrissK
  Chris K (blog) talks about when troubleshooting Transactional Replication performance he likes to break down the flow of data into 4 simultaneous conversations, examine performance of each conversation and this will help verify where the bottleneck investigation should begin.
  (tags: sql server Replication profiler)

We had a really weird one today for just one database on our server that has about 350 user databases on it.  We got a call from a software engineer and he couldn't access his database XXXXX and the website was also reporting connection errors.  He said he was doing some data exports from the database and was getting weird issues (don't you just love those helpful technical descriptions from the users who should know better).  

We signed in to SQL Server Management Studio (SSMS) and sure enough, we couldn't hit the DB either but all the other databases on the server were accessible and running fine.  (scratching my head here...)  The error message in SSMS was

   Msg 952, Level 16, State 1, Line 1
   Database 'XXXXX' is in transition. Try the statement later. 

and the SQL Server Logs showed

   SPID 465 - Setting database option OFFLINE to ON for database XXXXX. 

When I tried to look at the Activity Monitor, it returned an error with the same message and using select * from sys.sysprocesses returned the same error message.  (more head scratching here...)  A quick Google search turned up several possible solutions and only one appealed to me--find and kill the offending user's SSMS session. The other option was to restart the SQL Server service--the survey says "AHHHNNNNNT".  

First, I had the software engineer exit SSMS.  For some reason, as soon as the user's SSMS process terminated, we could access the database and Activity Monitor.  Second, I then killed all other SPID's the user had.  Finally, I had our hosting operations team kill any Citrix session the user may have had open in the background.   Basically, I just made sure everything was terminated for this user and then everyone was able to access the database again.    My hypothesis is that the SSMS process became corrupt and this was causing the error.     

Resources:

http://social.msdn.microsoft.com/forums/en-US/sqlgetstarted/thread/e779230a-87bc-4cee-b223-98a029097dc1/  

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=115305

• Using the SQL Server Analysis Services (SSAS) Deployment Wizard | MSSQLTIPS
  SSAS includes a tool called the Deployment Wizard which will do exactly what you need.  The Deployment Wizard uses the artifacts created by your SSAS project and can perform the deployment and/or generate an XMLA script that can be used to perform the deployment at a later time.  XMLA stands for XML for Analysis Services and it is the native protocol used by SSAS.  All commands sent to SSAS are in the XMLA format.  An XMLA script is an XML file.  The ability to generate the XMLA script allows the developer to provide the IT team what they need to perform the deployment.  In this tip we will review the BIDS artifacts, walk through the Deployment Wizard to create the XMLA script, then show the options to execute the script.
  (tags: Analysis Services ssas)

• SQL Server 2008 Diagnostic Information Queries - Windows Live | Glenn Berry
  It is a set of pretty heavily commented queries that are very useful for detecting and diagnosing many common performance issues with SQL Server 2008.
  (tags: sql2008 performance)

• Cheat Sheet and Quick Reference Card Directory | devcheatsheet.com - Cheat Sheets for Developers.
  A very large repository of various free cheatsheets, reference cards for developers, programmers and network admins. Includes subjects such as Programming Languages, Markup Languages, Query Languages, Libraries and Frameworks, Operating Systems, Version Controls, Databases, Applications, Security and others.
  (tags: cheatsheets)

• Suggested Max Memory Settings for SQL Server 2005/2008 | SQL Server Central
  Make sure you set the Max Server memory setting for SQL Server 2005/2008 to something other than the default setting which allows SQL Server to use as much memory as it wants (somewhere in the neighborhood of 2TB). This is very important with larger, busier systems that may be under memory pressure.
  (tags: sqlserver memory)

• SQL Server Setup : Creating a merged (slipstreamed) drop containing SQL Server 2008 RTM + Service Pack 1 | Peter Saddow
  How to slipstream or combine SQL 2008 RTM and SP1 into one install file.
  (tags: sqlserver update)