Mathom Solutions Blog-IT

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

As always, I am on the lookout for good free resources.  The good folks at Red-Gate with John Magnabosco have put out a very good book on Protecting SQL Server Data.  This free e-book covers the following topics:

1. Understanding Sensitive Data
2. Data Classification and Roles
3. Schema Architecture Strategies
4. Encryption Basics for SQL Server
5. Cell Level Encryption
6. Transparent Data Encryption (TDE)
7. One-Way Encryption
8. Obfuscation
9. Honeycombing a Database
10. Layering Solutions

The focus of this book is protecting sensitive data that is "at rest" within your SQL Server database (primarily Microsoft SQL Server 2005 and 2008). Security methods and appliances/devices that protect data externally to the DB, such as firewalls, secured network connections, and secure web design are all very important and recommended, but the main success of your efforts to protect sensitive data will depend upon how well you guard your data in the database. 

As you know, I am a big fan of Free...here are some other goodies:

Engineering Software for Accessibility by Microsoft Press. In an effort to enable software developers to create accessible Windows applications, we wanted to share our process with the community. We have captured this engineering process in a new book, Engineering Software for Accessibility. The book addresses three basic questions:

1. How do you plan for accessibility?
2. How do you design your software for accessibility?
3. How can you implement and test to your software to confirm it meets the accessible design?

You will learn that properly implemented accessibility enables access to Windows applications for users with a variety of capabilities.  

Get it before it is gone!

- - - - - - - - -  

SQL Server Tacklebox by Rodney Landrum. Essential tools and scripts for the day-to-day DBA that helps with Scripts, tools and techniques to tackle SQL Server issues, Troubleshoot performance issues, Receive notifications of impending issues, Fight off data corruption, Document and report on your servers, Automate and standardize SQL Server installation, Migrate data and manage data growth, and Secure access to your servers.    

This is sponsored by Red-Gate Software.   

• Data Compression: Strategy, Capacity Planning and Best Practices | Microsoft
  The data compression feature in SQL Server 2008 helps compress the data and help reduce the size of the database. As well, because compressed data is stored in fewer pages, you get improved I/O performance for intensive workloads. However, it all comes at a cost, extra CPU resources are required on the server to compress and decompress the data. Therefore, it is important to understand the workload characteristics when deciding how to best implement compression.
  (tags: sqlserver sql2008 compression)

• High Availability and Disaster Recovery at ServiceU: A SQL Server 2008 Technical Case Study
  Microsoft White Paper -- ServiceU Corporation is a leading provider of event management software. An essential part of our IT mission is maintaining high availability and disaster recovery capability. This technical case study shows how we use Windows failover clustering and SQL Server 2008 database mirroring to eliminate single points of failure in our data centers and enable fast recovery from a possible disaster at our primary data center. These strategies and solutions will be of interest to database administrators, senior IT managers, project leads, and architects.
  (tags: sqlserver sql2008 Whitepaper)
• Mit creme : Reviewing Agile Process Management Tools, Part 2
  More on the Agile web based products.
  (tags: agile)
• Mit creme : Reviewing Agile Process Management Tools
  Good review of some agile web based tools.
  (tags: Agile)
• Finding out how many times a table is being used in an adhoc or procedure calls...| SQLDenis
  Uses DMV to find out how many times a table is being used...not perfect but a fairly good one.
  (tags: sqlserver sql2005 sql2008)

• SQL Server Replication Scripts to Pull Replication Configuration Information | MSSQLTips
  Scripts to find out what tables are being replicated, what databases are published, what servers are subscribers are involved in replication.
  (tags: sqlserver Replication t-sql)

• How to insert into two tables in one statement | Peter Larsson
  A T-SQL method using the MERGE statement to insert the same data into two tables at once.
  (tags: sqlserver Merge)

• SQLClue | Bill Wunder
  SQLClue is a series of DBA tools by Bill Wunder to help you: 1. monitor SQL Server metadata change in mixed SQL Server 2000, 2005 and 2008 environments 2. share, search, and improved the knowledge and information of the data center as a data team 3. define and meet the business continuity needs of the data center 4. provide Database Developers with as needed access to the bits and behaviors of live systemns without direct live system access to accelerated the development environment.
  (tags: SQL runbook configuration)
• The Data Loading Performance Guide | Microsoft
  This white paper describes load strategies for achieving high-speed data modifications of Microsoft SQL Server database
  (tags: sqlserver bulkload performance Microsoft)

It's been awhile but wanted to post something that I found very useful.  

I encountered a situation where I need to parse through tens of thousands of log files for SQL Server and IIS and needed to do an analysis of what was contained within for a possible security incident and to create a standard auditing process for some compliance requirements for a new contract.  I thought about pulling the logs to a secure location, creating a SQL Server database and importing the files and using full text search but then I came across Splunk.   They have a nice little free version that allows you to index up to 500MB per day.  

With Splunk, I was able to pull down the logs to a directory, point Splunk at it and within minutes, it was all indexed and it automatically did the rest as new logs were added.   From there I was able to do research for XSS, SQL Injection, Login/Logout and failed/succesful information.  

Very nice and fast!  Check Splunk out as it has tons of possiblities besides log management!