Anything-SQL Lightning Talks - 2nd Edition

The SS SLUG is organizing the 2nd Edition of Anything-SQL Lightning Talks for the August 2012 meet-up. This is where a group of presenters will speak for 5 minutes on any topic related to SQL Server. We are signing up people for this session and would like you to talk as part of it. Interested? Want more information? Just click here...

SS SLUG August 2012 Meet-up

We are planning for the August 2012 SS SLUG meet-up to be held at Pearson Lanka, in Orion City - Dematagoda, happening on August 15th. We shall provide more information on that soon.

SQL Server 2008 R2 SP2 Released

Customers are highly encouraged to stay on a supported service pack to ensure they are on the latest and most secure version of SQL Server 2008 R2.  To obtain SQL Server 2008 R2 SP2 with its improved security and supportability please visit the links below:

·        Download SQL Server 2008 R2 SP2

·        Download SQL Server 2008 R2 SP2 Express

·        Download SQL Server 2008 R2 SP2 Feature Pack

Oracle Unveils Migration Tool for Microsoft SQL Server to MySQL

Oracle is going after users of Microsoft's SQL Server with a new tool for migrating data from SQL Server to its own MySQL database, the vendor announced Wednesday.

The tool has been built into Oracle's MySQL Workbench administration console, and with it, applications written for SQL Server can be easily tweaked for MySQL, Oracle said in a statement.

Oracle is also releasing an improved version of the MySQL Installer for Windows environments, as well as a new MySQL Notifier for Windows tool that "helps developers and DBAs to easily monitor, start and stop their MySQL database instances, with the Microsoft SQL Server look and feel," Oracle said.

In addition, Oracle is offering a new plug-in that allows users with no experience on MySQL to work with MySQL data inside Microsoft Excel.

Oracle claims that MySQL users can experience up to 90% less total cost of ownership compared to running SQL Server 2012.

More at:

http://www.oracle.com/us/corporate/press/1715479

http://www.computerworld.com/s/article/9229646/Oracle_hopes_to_poach_Microsoft_SQL_Server_users_with_MySQL_migration_tool?taxonomyId=18

Find Orphaned Users In SQL Server

Orphan users can occur once you detach databases or restore a database from another SQL Server database instance. Read this FAQ how to manage those instances.

Database Tuning Advisor (DTA) Naming Conventions

DBAs are using Database Tuning Advisor a.k.a. DTA get recommendations for better performance of their databases.

Following is the index script created from the DTA.

in this index name is _dta_index_Contact_13_341576255__K1_4_6 and people might think that this does not have any standards, but it does have a standard.

You have four parts in this index naming.

A

This is straight forward. It is the object name or the table name.

B

13 is the database id for the Adventureworks.

C

Object Id of the contact.

D

Columns order of the key columns.

Restoring to Existing Database

If you want to restore to the existing databases, first you need to make sure that database is not accessible by any other users. So you need to change the database into single user mode before restoring.

However, in SQL Server 2012 you have this option inbuilt with the restoring option.

Cache Plans for Estimated Queries

Let me put this in simple. Yes when you click estimated query plan as shown in the below image it will cache the query plan.

Let us verify this.

Let us clear the cache first and write the query first and then if you click the Display Estimated Execution Plan button

Then if you examine the cache plan with following query.

you can see that query is cached. This is fixed in SQL Server 2012.

Enhanced Features in Database Restore in SQL Server 2012

New article on Enhanced Features in Database Restore in SQL Server 2012

Increasing the LOG file size

I was doing some research into VLF and was trying increasing the LOG files with different sizes.

I came across with wired situation where when ever I crease the log size by 4GB, it increases with no time, but I go with less size it takes few or more seconds.

Here is the test.

Simply I created the database with 1 GB log file.

Then I expand the log file with 1-7 GBs and every time I re-create the database.

Here are the results.

It is almost a straight line except for 4 GB.

Let us see the physical log growth at each instances.

So surely there is something wrong when you are incrementing by 4GB. When searching I found the blog post from Paul where he confirms that this is a BUG.

I confirmed that this is resolved in SQL Server 2012.

Is Truncate a DDL Statement or DML Statement?

Since operational wise or user experience wise, truncate is equal to DELETE entire table and DELETE is a DML statement, most people think that Truncate is DML statement.

So let us verify this.

Let us create a table to play around.

Let me create user with DML permissions.

Now using this user let us truncate the employee table.

You will end up with an error as shown below.

Msg 1088, Level 16, State 7, Line 2
Cannot find the object "Employee" because it does not exist or you do not have permissions.

You will end up with an error as shown below.

Now let us create a user with DDL permission.

Let us truncate the table using this user.

since this is a success Truncate is a DDL statement.