SQL Server FAQ
Web www.mahipalreddy.com


What Is a Database Transaction ? What are the characteristics of a Database Transaction? What are ACID properties of a Transaction?


A transaction is one or more SQL statements that must be completed as a whole, or in other words, as a single Logical Unit of Work (LUW). Transactions provide a way of collecting and associating multiple actions into a single all-or-nothing multiple operation action. All operations within the transaction must be fully completed or not performed at all.


A transaction is a logical unit of work that has four special characteristics, known as the ACID properties:

Atomicity—Associated modifications are an all-or-nothing proposition; either all are done or none are done.
Consistency—After a transaction finishes, all data is in the state it should be, all internal structures are correct, and everything accurately reflects the transaction that has occurred.
Isolation—A transaction is isolated from other transactions in the database, so transactions can’t overwrite each other’s data. Transactions, in other words, can’t interfere with other transactions that are running concurrently.
Durability—After the transaction has finished, all changes made are permanent.


What is Database Concurrency ? What are the problems associated with Database Concurrency?


Concurrency can be defined as the ability of multiple processes to access or change shared data at the same time. The greater the number of concurrent user processes that can execute without blocking each other, the greater the concurrency of the database system.


Database concurrency problems occur when more than one user attempts to:

   » Read data that another is modifying.
   » Modify data that another is reading.
   » Modify data that another transaction is trying to modify.


Some of the problems associated with database concurrency are as follows:

Dirty reads
Dirty reads occur while a transaction is updating a row, and a second transaction reads the row before the first transaction is committed. If the original update rolls back, the data read by the second transaction is not the same, hence a dirty read has occurred.

Nonrepeatable reads
These occur when a transaction is updating data while a second transaction is reading the same data, both before and after a change. Data retrieved from the first query does not match the second query (this presumes that the second transaction reads the data twice; once before and once after).

Phantom reads
These occur when a transaction retrieves a set of rows once, another transaction inserts or deletes a row from that same table, and the first transaction looks at the query again. The phantom is the missing or new row.

Lost updates
Lost updates occur when two transactions update a row's value, and the transaction that last updates the row 'wins'; thus the first update is lost.


How do I move/copy database diagrams between two databases in sql server?


It"s surprisingly very simple! A table called dtproperties holds the information about all database diagrams in a particular database. So, if you have two identical databases (or at least have the same tables used in the diagrams), then you can transfer the records from dtproperties in the source database (the one with the diagrams) to the dtproperties in the destination database using a query like this:




tha"s all ! you are done. you diagrams have been moved to new database!! cool, eh?

If you are not comfortable with the above sql query, you can use DTS import/export wizard to achieve this.


How do I clear/truncate/free/shrink the transaction log in sql server?


I used pubs database as an example in the next piece of code

--shrink the transaction log file
DBCC shrinkfile('pubs_log')	--LOG FILE NAME (without file extension) WITHIN QUOTES


You can also use NO_LOG instead of TRUNCATE_ONLY. This removes the inactive part of the log without making a backup copy of it and truncates the log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved.


CAUTION: After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are NOT recoverable. For recovery purposes, immediately execute BACKUP DATABASE.


How do I backup a database using T-sql(Transact sql) script in sql server?
How do I programmatically backup a database in sql server?


Create the following stored procedure in your database, then call it with 3 parameters, namely database name, device name and backup folder path(look at the bottom of the following code to see an example that backs up pubs database.

CREATE procedure [dbo].[usp_auto_backup]  @dbname varchar(25), @devicename varchar(25), @folder_path varchar (100)
	declare @filename varchar (50),@exists varchar(50),  @nError int

	-- set variables here
	set @nError = 0
	set @filename = @folder_path + '\' + @dbname +  convert(varchar(8), GetDate(), 112) + '.BAK'
	set @exists = NULL

	-- check if dump device exists and drop if it does exist
	if exists (select * from master.dbo.sysdevices where name = @devicename)
			exec sp_dropdevice   @devicename,  'delfile'

	-- Add dump device
		EXEC sp_addumpdevice 'disk', @devicename, @filename
	-- check for any errors before backing up the database
	Set @nError = @@ERROR
	if @@ERROR <> 0
			RAISERROR(@nError,16,1, 'usp_auto_backup_general', @dbname)
	-- Now, Backup the database		
	BACKUP DATABASE  @dbname to @devicename
	 DESCRIPTION = 'some description like,auto backup', INIT, NAME = @filename


I used pubs database as an example in the next piece of code

	--Here's how to call the procedure 
	exec usp_auto_backup 'pubs','test_pubs_device','C:\Backups\Pubs'


Terms and Conditions of Use
Copyright © 2004 - 2006 Mahipal Padigela. All rights reserved.