Saturday, March 27, 2010

New features in coming SQL Azure updates

In one of my previous blogs I already told about my first experiences with SQL Azure. 2 weeks after the launch of SQL Azure the first Service Updates was coming available. At the MIX 10 conference, the Microsoft SQL Azure team announced some new features in the upcoming releases.

Support for MARS
In SU2 (April) Support for Multiple Active Row Sets. Mutilpe Active Row Sets (MARS)  allows you to execute multiple batches in a single connection.

50GB Databases
In SU3 (June) 50 Gb Database will be available.  If you would like to become an early adopter of this new size option before SU3 is generally available, send an email to and it will auto-reply with instructions to fill out a survey. Fill the survey out to nominate your application that requires greater than 10 Gb of storage.

Support for Spatial Data
In SU3 (June) Support for spatial data. This feature will support the Geography and Geometry types as well as query support in T-SQL. This is a nice feature which opens the Windows Azure Platform to support spatial and location aware applications.

SQL Azure Labs
A new site called SQL Azure labs is launched. SQL Azure Labs provides a place where you can access incubations and early preview bits for products and enhancements to SQL Azure. The goal is to gather feedback on the features you want to see in the product. All technologies on this site are for testing and are not ready for production use. Some of these features might not even make it into production – it’s all based upon the feedback of the SQL Azure community. Be aware that these features are actively being worked on, you should not use them against any production SQL Azure databases.

It is great to see that the development on SQL Azure is moving on. What will be the next feature in the upcoming Service Update (SU). If you have ideas you can submit them on:

Thursday, March 4, 2010

My SQL LDF file is so big in comparison with the MDF file, what can I do ?

At a lot of customer sites I  have seen situations in which the transaction log file (LDF) is multiple times bigger than the MDF file. This is not a 'normal' situation. In this blog I will explain what you can do if you have an LDF file of your SQL server database which is too big in comparison with the size of your MDF file.

What is the difference between the MDF and LDF file ?
The .MDF file is the primary data file of a SQL database. The .LDF file is the transaction log file of a SQL database. The transaction log is used to guarantee the data integrity of the database and for data recovery. Data integrity is implemented by design and can't be configured by the database administrator. Data recovery is implemented by design and is configurable by the database administrator.

In SQL Server you have 3 different data recovery models: Simple, Full and bulk-logged. Typically, a database uses the Full recovery model or Simple recovery model.

What is the main difference between Full and Simple?
  • Simple: No transaction log backups. Changes since the most recent backup are unprotected. In the event of a disaster, those changes are lost and need to be redone. You can only recover to the end of a backup.
  • Full: Requiers transaction log backups. No work is lost due to a lost or damaged data file. Can recover to a specific point in time, assuming that your backups are complete up to that point in time. For example, prior to application or user error. 

What is in the transaction log in the Simple recovery model ?
All open transactions. This means not committed transactions. This is called the active part of the transaction log. At the moment a transaction is committed it will be truncated from the transaction log. Log truncation automatically frees disk space for reuse by the transaction log. The biggest size of the transaction log will be the size of all open transactions at one moment. Therefor in a 'normal' SQL production database the transaction log file will never get very big.

What is in the transaction log in the Full recovery model ?
All committed transaction (inactive part) and open transactions (active part). The log records cannot be truncated untill all its log records have been captured in a transaction log backup. The log is truncated when you backup the transaction log.

What can we do to lower the size of the transactional log file (LDF)?
It depends on the configured recovery model of your database, what you can do. The recovery model of your database can be found in the properties of the database:

or via SSMS:
Open a query window and select the database.

SELECT Recovery_model_desc FROM sys.databases WHERE Name = db_name()

What to do if your recovery model is: FULL?
Check if you are making transactional backups on a reguler time interval. If you only make full backups your transaction log will always grow and will never be truncated. In case you never want to lose data in case of a application or user error. Start making transactional log backups. If a database restore to the latest full backup is acceptable, you can switch the recovery model of your database to simple. Before switching the recovery model of your database please read next document: Considerations from switching the recovery model of your database. After switching to the recovery model Simple , you can shrink the log file. See the end of this blog how to do this.

What to do if your recovery model is: Simple?
Shrink the log file.

To set the recovery model to Simple you can run next query:

To set the recovery model to FULL you can run next query:

How many free space is available in my log file?
  • Select the database in the Object Explorer
  • Point to Tasks, Point to Shrink and then click Files.
  • Select File Type: Log
  • Currently allocated: The current size of your log file
  • Available free space: The free space in your log file
To shrink the log file:
  • Select the database in the Object Explorer
  • Point to Tasks, Point to Shrink and then click Files.
  • Select File Type: Log
  • Select Release unused space
  • Press OK to start the shrink process. If you do not want to shrink the log file press the cancel button.