SQL Transaction Logging

By default, any operations in SQL Server such as inserts, updates, and deletes are stored in the transaction log. The principle behind the transaction log is to revert the operation if an error occurs and to ship the logs for backup purposes. When you have a huge table and you initiate a delete command on all of the rows, SQL will delete each row after logging it into its transaction log.

The Problem

Logging takes a long time and the log file will grow as each record is deleted from the database. If an error is encountered, even at the last row to be deleted, all of the transactions will need to be reverted. The process of reverting the delete operations in itself takes a significant amount of time.

The Solution

SQL Server 2005 and grater enhanced the functionality of the TOP operator. It can be used in conjunction with insert, update, and delete statements. By leveraging the top function and deleting a limited set at a time, we can minimize the amount of records to be reverted in case of an error, issue less locks on the table making it more usable while the operation is taking place, and allowing the log file space to be re-used.

The following code performs a batch deletion of records 10,000 at a time:

While (select count(*) from TABLENAME) > 0
Begin
Delete Top(10000) from TABLENAME
If @@rowcount == 0 break
End

Just replace TABLENAME with the name of the table you are deleting and run the statement against your database.

HAPPY CODING!

What is collation?

Collation is a set of rules that determine how data is stored and compared in SQL server. These rules define characteristics such as how data is sorted with the correct character sequence, case sensitivity specifications, how accent marks are treated, and character widths.

How can collation cause trouble in SQL Server Management Studio in a limited security environment?

By default, when you click the ‘Databases’ node within the SQL server management studio under the Object Explorer after connecting to a server, a list of databases on that server is enumerated and shown. In a shared environment, such as a hosted SQL server account solution, each log-in only has access to its own database. This means that collation information is only available for the database that the log-in has access to.

SQL server management studio, by default, tries to grab collation information for every database that is enumerated on the server. The trouble occurs when a database that has a collation other then the default is enumerated and the log-in used to enumerate the database does not have access to the collation information. This usually results in a complete error when opening the ‘Database’ node and prevents any databases from being shown.

The error usually states:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
–> An exception occured while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
–> The server principay “login” is not able to access the database “db with non default collation” under the current security context. (Microsoft SQL Server, Error:916)

How can the problem be fixed?

The quickest way to mitigate this issue is to turn off database collation by default. To do this, follow the steps below.

1. Click on the ‘Databases’ folder under the SQL server in the SQL Server Management Studio.

2. Press F7 or click the ‘View’ toolbar menu item then select ‘Object Explorer Details’

3. Right click the headers in the ‘Object Explorer Details’ data grid and deselect ‘Collation’

That’s it! If you try to re-enumerate the databases, the blocking operation will no longer be executed by default.

Happy Coding!

Source/Version/Revision Control

How many times have you spent countless hours writing code just to realize you’ve either overwritten or accidentally deleted your files? How many times have you been working with a team of developers just to realize your coworker has overwritten your code?

This has unfortunately happened to the best of us at one point or another. This issue usually prompts us to look for a way to easily backup our code and share it between multiple developers. On our journey, we find many different flavors of source/revision control. Dependent upon your preference there are multiple options available, some free, some purchased. For more information on source control visit Wikipedia.

The Problem

For those of you that have chosen Tortoise SVN, a windows shell integrated CVS source control solution, you may find it unnecessary to save files or folders created by plug-ins and visual studio. These files can be very annoying as they usually change every time you open up your solution, or build your application. A popular plug-in for Visual Studio that creates extra files and folders is ReSharper. I will focus on removing the files created by this plug-in.

As you can see, ReSharper folders and files are available to add to the source control by default.

The Solution

In order to tell Tortoise SVN to ignore the files and folders created by ReSharper and to not list them every time we commit our files, we have to add the extensions/folders to Tortoise SVN’s global ignore pattern text box.

Ignore pattern to ignore ReSharper Created files:

**.ReSharper** **\_ReSharper.**

Ignore pattern to ignore Visual Studio Files which shouldn’t be source controlled:

*\bin* *\obj* *.suo *.user

Combined Ignore pattern to ignore both Visual Studio and ReSharper files:

*\bin* *\obj* *.suo *.user **.ReSharper** **\_ReSharper.**


Implementation

Adding any of these “ignore” rules is simple. First identify the rule you want to add; I recommend the combined rule, and then:

1. Right click on any of your repositories, hover over TortoiseSVN menu option, and click on the Settings sub link.

2. Add the rule you have chosen to the global ignore pattern and remember to click “OK”.

That’s it! Now when you try to commit your changes, you won’t see any of those annoying extra files!

HAPPY CODING!