|
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:
INSERT INTO YOUR_DESTINATION_DBNAME.DBO.dtproperties SELECT * FROM
YOUR_SOURCE_DBNAME.DBO.dtproperties
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
BACKUP LOG pubs with TRUNCATE_ONLY -- DATABASE NAME NOT WITHIN QUOTES
go
--shrink the transaction log file
DBCC shrinkfile('pubs_log') --LOG FILE NAME (without file extension) WITHIN QUOTES
go
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)
AS
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)
begin
exec sp_dropdevice @devicename, 'delfile'
end
-- Add dump device
EXEC sp_addumpdevice 'disk', @devicename, @filename
-- check for any errors before backing up the database
Set @nError = @@ERROR
if @@ERROR <> 0
begin
RAISERROR(@nError,16,1, 'usp_auto_backup_general', @dbname)
end
-- Now, Backup the database
BACKUP DATABASE @dbname to @devicename
WITH
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'
Go
|
