Search

Monday, October 28, 2013

SQL performance slowing down

I was working on a database since last couple of month. Suddenly it was slowing down day by day. I had not changes anything in the database. 

Then I checked the database properties and found that recovery model was set to Full. 
Full recovery model was not needed as I do not use incremental backup. 
So I set recovery model to Simple and my problem is solved.

Monday, October 21, 2013

Change Database compatibility level

You can change the database compatibility level through Management Studio. (Go to Database Properties - Option).

Here is the script to change the database compatibility level through SQL:

ALTER DATABASE <DB_Name> SET SINGLE_USER 
GO 
EXEC sp_dbcmptlevel <DB_Name>, 100; 
GO 
ALTER DATABASE <DB_Name> SET MULTI_USER 
GO

In above script change <DB_Name> with Database Name (you want to change the compatibility level).

Compatibility Level :

SQL Server 2008 : 100
SQL Server 2005 : 90
SQL Server 2000 : 80
SQL Server 7.0 : 70

Monday, October 7, 2013

ACID rule of thumb for transactions

A transaction must be:
  1. Atomic - It is one unit of work and does not dependent on previous and following transactions.
  2. Consistent - Data is either committed or roll back, no ?in-between? case where something has been updated and something hasn't.
  3. Isolated - no transaction sees the intermediate results of the current transaction).
  4. Durable - the values persist if the data had been committed even if the system crashes right after.