Tip: turn auto close Off for you SQL Server databases

On a development machine, I had a high CPU usage for the SQL server service. To understand the reason, I looked at the SQL Server logs (From SQL Server Management Studio - SQL Server Logs).

As you may notice from the screenshot above, the activity of starting the reporting service database was recurring again and again.

I checked the database options, and the noticed that the auto close property was set true. This explains why SQL Server tries to start the database at every connection.

If the Auto close property is set to on:

the database is closed and shut down cleanly when the last user of the database exits and all processes in the database complete.

As quoted from msdn.

Unfortunately, the property was set to true when the database was created during the SQL server installation.

Turning Auto close to false (off), and maybe restarting the service, returned the CPU usage of the SQL Server service to a normal level.

Articles I read in 2009

A new year, and as a habit, I share the a list of articles I read in the past year, 2009.
It was a tough year on the personal, career and study levels, I wish I can contribute more to the community in the new year through blogging, article writing and open source software.

2008 list can be fond here
2007 list can be fond here

• MySQL Stored Procedures: Part 2
• Finding a better candidate for your clustered indexes
• How to get index usage information in SQL Server
• Peer-to-Peer Replication in SQL Server 2008
• Elgan: Why products fail
• MySQL Stored Procedures: Part 3
• MySQL Stored Functions
• MySQL and Character Sets
• Security Code Review Using CAT.NET - Part 1
• Security Code Review Using CAT.NET - Part 2
• Avoiding the high cost of bad code
• Self-Promotion at Work: 8 Tips for Shy People
• On the OUTPUT of a data modification
• What’s Hot for 2009? Top 10 areas of opportunity in the downturn
• How To Get Active Directory Users and Groups Using SQL Server Integration Services SSIS 2005
• The sqlcmd utility in SQL Server
• Scripting INSERTs
• Test Run: Lightweight Testing with Windows PowerShell
• 10 Useful Web Application Interface Techniques
• Calculations Made Outside of the Data Warehouse
• Unit Testing Guidelines
• Linking to the Previous Row
• DAC - What to Execute when Connected?
• How to choose and use source code analysis tools
• Mono 2.2 may overtake .NET in some critical categories
• Rob Cheyne Pushes Developer Security Awareness
• Making Time for the MITs (Most Important Things)
• Cross-Domain Communication with IFrames
• Productivity: It Comes from Software Design Rather than Software Tools
• 10 Tips for Writing Consistently Popular Programming Articles
• The big fat lie about battery life
• Why Silverlight is the future
• The case for supporting and using Mono
• Who owns the code? Beware copyright pitfalls
• Over 15 Top Open Source Tools for Web Developers
• What Can Database Developers and DBAs Do About SharePoint?
• Introduction to Multi-Tenant Architecture
• Things I Wish I Had Known
• Windows 7: Enterprise Features Explained
• Correlated Joins Using "Apply"
• Microsoft converging programming languages
• How to Fix The Productivity Problem That's Killing the Software Industry
• Software's Classic Mistakes--2008
• Windows & .NET Watch: Python: Arbitrarily Interesting
• The interview question you must be ready for
• Microsoft Semblio Promises Easy Content Creation for Educators
• Introduction to Version Control with Subversion
• Row Level Versioning
• SQL Server 2005 Paging – The Holy Grail
• TIP: Closing your WCF Connections properly
• Using schema binding to improve SQL Server UDF performance
• Custom Authorization in WCF
• Tapping Into Microsoft Live Services with C#
• Is Five the Optimal Team Size?
• Sending Email Using Embedded Images
• Fundamentals of WCF Security
• Microsoft discloses ambitious security strategy
• The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets (No Excuses!)
• Secret Sauce: 10 Game-changing Tips from the World’s Top Freelancers
• The Tower of Techno-Babel: What Languages Do Devs Love Now?
• PowerShell: A More In-Depth Look - Error Handling
• Tip: HTTP Conditional Get
• Handling Data Conflicts in the Microsoft Sync Framework
• What is sysprocesses and what can it do for you?
• Usages of CONTEXT_INFO
• Why Doing Things Half Right Gives You the Best Results
• Auto generate change scripts in SQL Server Management Studio (SSMS) for tables
• There Must Be 15 Ways To Lose Your Cursors... part 1, Introduction
• There Must Be 15 Ways To Lose Your Cursors… Part 2
• Using Isolated Storage to Store Application User Data in the .NET Framework
• The Developer Iteration
• Yahoo’s YQL Makes the Internet Your Database
• What happens when you perform a rollback inside a trigger?
• My Top 5 SQL Server Indexing Best Practices
• SQL Server standard edition in a virtual world
• What I've already done to be a better developer
• Tip: Detecting Defects in C# Programs
• Implementing multithreading in .NET: Two major factors to consider
• JavaScript - the Web site performance killer, Google guru says
• Why use Stored Procedures?
• Master Data Services – What’s the big deal?
• Announcing VS2010 / .NET Framework 4.0 Beta 1!
  
• Flash vs. Silverlight: What Suits Your Needs Best?
• Cracking the ITIL V3 Foundation exam
• Windows 7 Managed Code APIs
• Cracking the PMP
• Add Styles to Your Reporting Services Reports
• Centralising Reporting Services Stylesheets
• Introducing Page Speed
• Sometimes the Simplest Solution Isn't the Best Solution (The All-In-One Search Query)
• 10 Answers You Should Know Before Your Job Interview
• My Approach to Writing for SQLServerCentral
• Saving Bandwidth Using UpdatePanel Triggers
• Create Quick Database Interfaces with ASP.NET Dynamic Data
• 10 UI Design Patterns You Should Be Paying Attention To
• The Entity Framework In Layered Architectures
• Hardware's Dirty Little Secret, or Why Software Can be Mass Produced
• How to Identify Blocking Problems with SQL Profiler
• Avoiding the EAV of Destruction
• Old Performance Tuning Recommendations Die Hard
• Myth : I don't need point in time recovery so I don't need the transaction log
• Browser Compatibility Development Guide
• Comparison of Dates in SQL
• Using RDLC and DataSets to develop ASP.NET Reporting Services Reports
• C# 4.0's New Features Explained
• Map IP Address to a Geographical Location
• Anti-Patterns To Avoid In N-Tier Applications
• Breaking All The Rules with WCF
• The Cost of Static
• Learning from Our Mistakes
• There Is No Easy Fix
• Claims-Based Authorization with WIF
• How to detect and avoid memory and resources leaks in .NET applications
• An Un-indexed Foreign Key Gotcha
• Seven Lead Principles
• Looking to the Future of ASP.NET
• Introduction to SQL Azure
• 10 Common SEO Mistakes that can Destroy Your Website [Part I]
• WCF Behavior
• Using Agile Techniques to Pay Back Technical Debt
• Using Business Connectivity Services in SharePoint 2010
•