Search Results

Search found 31553 results on 1263 pages for 'read committed snapshot'.

Page 1/1263 | 1 2 3 4 5 6 7 8 9 10 11 12  | Next Page >

  • SQL SERVER – 2008 – Introduction to Snapshot Database – Restore From Snapshot

    - by pinaldave
    Snapshot database is one of the most interesting concepts that I have used at some places recently. Here is a quick definition of the subject from Book On Line: A Database Snapshot is a read-only, static view of a database (the source database). Multiple snapshots can exist on a source database and can always reside on the same server instance as the database. Each database snapshot is consistent, in terms of transactions, with the source database as of the moment of the snapshot’s creation. A snapshot persists until it is explicitly dropped by the database owner. If you do not know how Snapshot database work, here is a quick note on the subject. However, please refer to the official description on Book-on-Line for accuracy. Snapshot database is a read-only database created from an original database called the “source database”. This database operates at page level. When Snapshot database is created, it is produced on sparse files; in fact, it does not occupy any space (or occupies very little space) in the Operating System. When any data page is modified in the source database, that data page is copied to Snapshot database, making the sparse file size increases. When an unmodified data page is read in the Snapshot database, it actually reads the pages of the original database. In other words, the changes that happen in the source database are reflected in the Snapshot database. Let us see a simple example of Snapshot. In the following exercise, we will do a few operations. Please note that this script is for demo purposes only- there are a few considerations of CPU, DISK I/O and memory, which will be discussed in the future posts. Create Snapshot Delete Data from Original DB Restore Data from Snapshot First, let us create the first Snapshot database and observe the sparse file details. USE master GO -- Create Regular Database CREATE DATABASE RegularDB GO USE RegularDB GO -- Populate Regular Database with Sample Table CREATE TABLE FirstTable (ID INT, Value VARCHAR(10)) INSERT INTO FirstTable VALUES(1, 'First'); INSERT INTO FirstTable VALUES(2, 'Second'); INSERT INTO FirstTable VALUES(3, 'Third'); GO -- Create Snapshot Database CREATE DATABASE SnapshotDB ON (Name ='RegularDB', FileName='c:\SSDB.ss1') AS SNAPSHOT OF RegularDB; GO -- Select from Regular and Snapshot Database SELECT * FROM RegularDB.dbo.FirstTable; SELECT * FROM SnapshotDB.dbo.FirstTable; GO Now let us see the resultset for the same. Now let us do delete something from the Original DB and check the same details we checked before. -- Delete from Regular Database DELETE FROM RegularDB.dbo.FirstTable; GO -- Select from Regular and Snapshot Database SELECT * FROM RegularDB.dbo.FirstTable; SELECT * FROM SnapshotDB.dbo.FirstTable; GO When we check the details of sparse file created by Snapshot database, we will find some interesting details. The details of Regular DB remain the same. It clearly shows that when we delete data from Regular/Source DB, it copies the data pages to Snapshot database. This is the reason why the size of the snapshot DB is increased. Now let us take this small exercise to  the next level and restore our deleted data from Snapshot DB to Original Source DB. -- Restore Data from Snapshot Database USE master GO RESTORE DATABASE RegularDB FROM DATABASE_SNAPSHOT = 'SnapshotDB'; GO -- Select from Regular and Snapshot Database SELECT * FROM RegularDB.dbo.FirstTable; SELECT * FROM SnapshotDB.dbo.FirstTable; GO -- Clean up DROP DATABASE [SnapshotDB]; DROP DATABASE [RegularDB]; GO Now let us check the details of the select statement and we can see that we are successful able to restore the database from Snapshot Database. We can clearly see that this is a very useful feature in case you would encounter a good business that needs it. I would like to request the readers to suggest more details if they are using this feature in their business. Also, let me know if you think it can be potentially used to achieve any tasks. Complete Script of the afore- mentioned operation for easy reference is as follows: USE master GO -- Create Regular Database CREATE DATABASE RegularDB GO USE RegularDB GO -- Populate Regular Database with Sample Table CREATE TABLE FirstTable (ID INT, Value VARCHAR(10)) INSERT INTO FirstTable VALUES(1, 'First'); INSERT INTO FirstTable VALUES(2, 'Second'); INSERT INTO FirstTable VALUES(3, 'Third'); GO -- Create Snapshot Database CREATE DATABASE SnapshotDB ON (Name ='RegularDB', FileName='c:\SSDB.ss1') AS SNAPSHOT OF RegularDB; GO -- Select from Regular and Snapshot Database SELECT * FROM RegularDB.dbo.FirstTable; SELECT * FROM SnapshotDB.dbo.FirstTable; GO -- Delete from Regular Database DELETE FROM RegularDB.dbo.FirstTable; GO -- Select from Regular and Snapshot Database SELECT * FROM RegularDB.dbo.FirstTable; SELECT * FROM SnapshotDB.dbo.FirstTable; GO -- Restore Data from Snapshot Database USE master GO RESTORE DATABASE RegularDB FROM DATABASE_SNAPSHOT = 'SnapshotDB'; GO -- Select from Regular and Snapshot Database SELECT * FROM RegularDB.dbo.FirstTable; SELECT * FROM SnapshotDB.dbo.FirstTable; GO -- Clean up DROP DATABASE [SnapshotDB]; DROP DATABASE [RegularDB]; GO Reference : Pinal Dave (http://blog.SQLAuthority.com) Filed under: SQL, SQL Authority, SQL Backup and Restore, SQL Data Storage, SQL Query, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology

    Read the article

  • SQL SERVER 2008 Introduction to Snapshot Database Restore From Snapshot

    Snapshot database is one of the most interesting concepts that I have used at some places recently.Here is a quick definition of the subject from Book On Line:A Database Snapshot is a read-only, static view of a database (the source database). Multiple snapshots can exist on a source database and can always reside on the [...]...Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

    Read the article

  • Merge Publication Data Partition Snapshot generation

    - by David Osborn
    I have a merge publication in SQL 2008 R2 with data partitions and I am wondering when I should generate the snapshots for the data partitions. I get an error sometimes when bringing a new subscribe online related to the partition snapshot being out of date and I am wondering if this has to do with the publication snapshot being scheduled at the same time as the partition snapshots. I'm not really sure how the partition snapshot gets generated, but it appears maybe it is getting generated before the publication snapshot. If this is the case how should I be scheduling the partition snapshots to get generated? Should I set them to run x number of minutes after the publication snapshot? this seems kind of poor to do in case the publication snapshot takes awhile or fails. It seems to me the publication snapshot should just run the data partition snapshot agents when it is done if this is the case.

    Read the article

  • Proper way to remove an active / inactive LVM snapshot

    - by user2622247
    I have created a sample ruby script file for removing extra LVM snapshots from the system. For removing LVM snapshot, we are using lvremove command. This command is working fine and we can remove snapshots from the system. # sudo lvremove /dev/ops/dbbackup lvremove -- do you really want to remove "/dev/ops/dbbackup"? [y/n]: y Sometimes while removing snapshots we are getting following errors. Unable to deactivate open rootfs_12.10_20140812_00-cow (252:8) Failed to resume rootfs_12.10_20140812_00. libdevmapper exiting with 7 device(s) still suspended. The system gets frozen. We cannot fire any command or can not perform any action on it. After restarting the system, it is functioning fine. We can perform all the operations even we can delete that snapshot also. I searched about it I found these threads https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=659762 and https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=674682 Solution from this thread is after getting the error but I have to avoid this type of error. I have a question, Is there any better way removing LVM snapshots? So that we can avoid this type of error. If anyone needs more info feel free to ask me.

    Read the article

  • Emulator restarts after snapshot load

    - by reox
    I've set up a android emulator with a snapshot of a specific state i need to go back every time. the snapshot is created and is listed and i can also start directly with this snapshot with emulator -avd MyAVD -snapshot mystate -no-snapshot-save the problem is now that the emulator restart itselfs after i load the snapshot after i did my changes in the emulator. So if i use echo -e "avd snapshot load mystate\nexit" | nc localhost 5554 the snapshot is loaded correctly but then after some seconds i see the bootloader screen. Whats happening there? this phenomen is repeatable with different emulators and different snapshots. is it possible that i just misuse a feature here? edit: it seems this problem is only occuring when i load a snapshot multiple times. after starting the emulator, everything is working quite well. now i load my snapshot, also everything is working well. but when i now load the same snapshot again, it will correctly load it but then the emulator will restart. so is there any possibility to load a snapshot multiple times? otherwise i always need to restart my emulator, which takes a long time...

    Read the article

  • Geek City: Growing Rows with Snapshot Isolation

    - by Kalen Delaney
    I just finished a wonderful week in Stockholm, teaching a class for Cornerstone Education. We had 19 SQL Server enthusiasts, all eager to find out everything they could about SQL Server Internals. One questions came up on Thursday that I wasn’t sure of the answer to. I jokingly told the student who asked it to consider it a homework exercise, but then I was so interested in the answer, I try to figure it out myself Thursday evening. In this post, I’ll tell you what I did to try to answer the question....(read more)

    Read the article

  • Database Snapshot in Sql Server 2005

    A database snapshot is a read-only, static view of a database (called the source database). Each database snapshot is transactionally consistent with the source database at the moment of the snapshot's creation. When you create a database snapshot, the source database will typically have open transactions. Before the snapshot becomes available, the open transactions are rolled back to make the database snapshot transactionally consistent.

    Read the article

  • Restoring snapshot for Microsoft Exchange server

    - by Mugen
    Hi, The background: I need to do some testing with Microsoft exchange server. Specifically, I'll be installing some software on the Microsoft exchange server machine and uninstalling that same software again. The problem I face: While I repeatedly do this with different versions of my software there is a chance that sometime later the Exchange server installation might get corrupted. When that happens I would need to reinstall Exchange server which I feel is somewhat of a chore. So what I am planning to do is to install the Ms Exchange server on a virtual machine in VMware ESX server and take a snapshot so that during my work whenever the installation is corrupted I can restore the snapshot. So here's my question: Would restoring the snapshot for the Microsoft Exchange server virtual machine work correctly? I'm not familiar with the intricacies of exchange server and any changes (if any) that happen with the Domain controller when we use or install an exchange server (Personally I don't think that should happen but just making sure). I have a shortage of time and hence decided to post this question here. Could someone please tell me whether restoring a snapshot for Exchange server would work fine? Thanks a load, Mugen

    Read the article

  • SQL Server 2008 R2 - Cannot create database snapshot

    - by Chris Diver
    Server: Windows Server 2008 R2 X64 Enterprise SQL: SQL Server 2008 R2 Enterprise X64 I have a default SQL Server instance, the SQL Server service account is running as a domain user. I am trying to create a database snapshot in the directory where the mdf files are stored. The T-SQL syntax is correct. The file system is NTFS. The error message I get is: Msg 1823, Level 16, State 2, Line 1 A database snapshot cannot be created because it failed to start. Msg 5119, Level 16, State 1, Line 1 Cannot make the file "e:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestDB.ss" a sparse file. Make sure the file system supports sparse files. The local SQLServerMSSQLUser$db$MSSQLSERVER group has Full Control permission on the folder where I am trying to create the snapshot. I can fix the problem in two ways, neither of which are suitable. Add the SQL Server service (domain) account to the local Administrators group and restart the SQL service. Grant the local SQLServerMSSQLUser$db$MSSQLSERVER group Full control on E:\ I have tried to change the owner of the DATA directory to SQLServerMSSQLUser$db$MSSQLSERVER to no avail. I have no issue creating a new database Why can I not create a snapshot by giving permission only on the DATA folder? Update 23/09/2010: I have tried mrdenny's suggestion with no luck (but learned something new in the process), I suspect the problem may be due to the fact that the domain is a windows 2000 domain running in mixed mode. I had to install hotfix KB976494 for Server 2008 R2, as the SQL Server 2008 R2 installer would not verify the service account correctly with the domain. I noticed that Server 2000 isn't a supported operating system for SQL 2008 R2 but cannot find anything that would suggest it shouldn't work in a 2000 domain. I dis-joined the test server from the domain and changed the service accounts to the local service account and I still have the same issue. I will try to re-install the server without joining the domain and without the hotfix and see if the issue persists.

    Read the article

  • VMware ESXi 4.1 snapshot of server 2008R2 machine generates 2 indentical snapshots

    - by Peter
    I have 2 VMs that are failing to get veeam backups, and it appears that the culprit is vmware snapshots. We are running Vsphere ESXi 4.1 build 320092, we have multiple server 2008R2 machines that take snapshots fine, but when with these two VMs when I take a snapshot I get 2 identical snapshots a few seconds apart. The snapshot manager only shows 1 snapshot, but there are 2 files 1 number off, that are identical sizes. There is only one disk on each VM, so that isn't the problem. Has anyone seen this behavior before and know how to fix it? Here are the files after a bad snapshot VM-XXX-000001-ctk.vmdk VM-XXX-000001-delta.vmdk VM-XXX-000001.vmdk VM-XXX-000002-ctk.vmdk VM-XXX-000002-delta.vmdk VM-XXX-000002.vmdk VM-XXX-2a659dbf.hlog VM-XXX-2a659dbf.vswp VM-XXX-Snapshot286.vmsn VM-XXX-aux.xml VM-XXX-ctk.vmdk VM-XXX-flat.vmdk VM-XXX-vss_manifests286.zip VM-XXX.nvram VM-XXX.vmdk VM-XXX.vmsd VM-XXX.vmx VM-XXX.vmxf vmware-20.log vmware-21.log vmware-22.log vmware-23.log vmware-24.log vmware-25.log vmware.log VM-XXX-000001.vmdk and VM-XXX-000002.vmdk are the exact same size.

    Read the article

  • Deploy virtual PC based on snapshot using Hyper-V

    - by user27786
    I have a server with Hyper-V configured, it is easy to create a new virtual machine using the management tools, but it can take some time. I often see those hosts that say "We can have your server ready in 5 seconds". And I wonder how do they manage this? I would also like to deploy a full functional clean server in 5 seconds. So how is this done? Is it by first installing a clean server, then take a snapshot of that for later to deploy a new server based on that snapshot? I tried doing this, but I did not find any where to install a new virtual machine based on the snapshot. Anyone got any thoughts to share on this?

    Read the article

  • Make a snapshot of a live mySQL database with myISAM & innoDB tables without locking

    - by Artem
    We have a live database in production where we are running out of space on the server. So I would like to transfer to a new server without any downtime (or as little downtime as possible). In general, I would also like to have a hot failover copy of the database available. I would like to use replication to get all of the data copied to the new machine, and then at some point flip a switch and have that new machine become the master (normal failover scenario). My problem is that I am not sure how to initialize replication without locking the db to make the initial snapshot I will use? Is there any way to do this? I know I could do it using single-transaction if I was using innoDB, but very unfortunately we have some myISAM tables in there (in fact the largest 150GB table is myISAM and I want to switch it to InnoDB but I can't do it until I have more space & a hot copy to switch to). Any ideas? Is there some way to make such a snapshot? Or is there alternatively a way to get replication to "catch up" without an snapshot for initialization?

    Read the article

  • Proper 'cleartool mkview' for ClearCase Snapshot view creation

    - by Jörg Battermann
    Good afternoon, seems like I am somewhat stuck in CC-land these days, but I have one (hopefully) final question regarding proper CC-handling: When using the CC View Creation Wizard with the two steps / details below, I can create a proper Snapshot view on my machine perfectly fine, however when trying to do the same with the mkview command, it fails... Here are the screenshots of the view creation wizard: Now that results into the (working) following view: cleartool> lsview battjo6r_view2 battjo6r_view2 \\Eh40yd4c\Views\battjo6r_view2.vws cleartool> lsview -long battjo6r_view2 Tag: battjo6r_view2 Global path: \\Eh40yd4c\Views\battjo6r_view2.vws Server host: Eh40yd4c Region: CT_WORK Active: NO View tag uuid:f34cf43f.b4d048df.845d.ed:21:a2:9c:45:ff View on host: Eh40yd4c View server access path: D:\Views\battjo6r_view2.vws View uuid: f34cf43f.b4d048df.845d.ed:21:a2:9c:45:ff View attributes: snapshot View owner: WW005\battjo6r However, when trying to create the view manually via mkview -snapshot -tag battjo6r_view2 -vws \\Eh40yd4c\Views\battjo6r_view2.vws -host Eh40yd4c -hpath D:\Views\battjo6r_view2.vws -gpath \\Eh40yd4c\Views\battjo6r_view2.vws battjo6r_view2 ... I get the following error: cleartool> mkview -snapshot -tag battjo6r_view2 -vws \\Eh40yd4c\Views\battjo6r_view2.vws -host Eh40yd4c -hpath D:\Views\battjo6r_view2.vws -gpath \\Eh40yd4c\Views\battjo6r_view2.vws battjo6r_view2 Created view. Host-local path: Eh40yd4c:D:\Views\battjo6r_view2.vws Global path: \\Eh40yd4c\Views\battjo6r_view2.vws cleartool: Error: Unable to find view by uuid:6f99f7ae.6a5d40e4.ba32.37:8e:e5:a4:ed:18, last known at "<viewhost>:<stg_path>". cleartool: Error: Unable to establish connection to snapshot view "6f99f7ae.6a5d40e4.ba32.37:8e:e5:a4:ed:18": ClearCase object not found cleartool: Warning: Unable to open snapshot view "D:\SnapShotViews\battjo6r_view2". cleartool: Error: Unable to create snapshot view "battjo6r_view2". Removing the view ... Any idea why this is happening? Am I missing something?

    Read the article

  • Snapshot/rollback for libvirt+KVM?

    - by jtimberman
    I've recently begun using KVM for my development/test environment on a Linux host system with 8G memory. Prior, I was using VMware Fusion for my virtual environment, but my Macbook only has 2G memory. I tried VMware Server and ESX on the host instead of KVM, but the webUI doesn't run on Mac OSX's Firefox, and we're going to be doing more with KVM anyway. The main feature of VMware I miss is robust snapshot/rollback, but I'm missing this in KVM. I understand the snapshot command, but it shuts down the guest OS when complete, and then copying the disk image to preserve its state is cumbersome. Is this really the best way to manage snapshots on KVM?

    Read the article

  • Node.js/ v8: How to make my own snapshot to accelerate startup

    - by Anand
    I have a node.js (v0.6.12) application that starts by evaluating a Javascript file, startup.js. It takes a long time to evaluate startup.js, and I'd like to 'bake it in' to a custom build of Node if possible. The v8 source directory distributed with Node, node/deps/v8/src, contains a SconScript that can almost be used to do this. On line 302, we have LIBRARY_FILES = ''' runtime.js v8natives.js array.js string.js uri.js math.js messages.js apinatives.js date.js regexp.js json.js liveedit-debugger.js mirror-debugger.js debug-debugger.js '''.split() Those javascript files are present in the same directory. Something in the build process apparently evaluates them, takes a snapshot of state, and saves it as a byte string in node/out/Release/obj/release/snapshot.cc (on Mac OS). Some customization of the startup snapshot is possible by altering the SconScript. For example, I can change the definition of the builtin Date.toString by altering date.js. I can even add new global variables by adding startup.js to the list of library files, with contents global.test = 1. However, I can't put just any javascript code in startup.js. If it contains Date.toString = 1;, an error results even though the code is valid at the node repl: Build failed: -> task failed (err #2): {task: libv8.a SConstruct -> libv8.a} make: *** [program] Error 1 And it obviously can't make use of code that depends on libraries Node adds to v8. global.underscore = require('underscore'); causes the same error. I'd ideally like a tool, customSnapshot, where customSnapshot startup.js evaluates startup.js with node and then dumps a snapshot to a file, snapshot.cc, which I can put into the node source directory. I can then build node and tell it not to rebuild the snapshot.

    Read the article

  • virtualbox snapshot size

    - by intuited
    I've started using Windows 7 under VirtualBox on an Ubuntu 10.10 host. I took about 6 snapshots over the course of setting up the VM from the Windows restore image that came with the computer. My installations were more or less limited to windows updates, antivirus, and the VB Guest Additions. I uninstalled much more than I installed. The VM was running for about 24 hours total. The snapshots increased in size at a worrisome rate, even when the machine was idle: the snapshot .vdi file for the period between 11:22 PM and 9:02 AM is 6 gigs in size; during that time very little happened. The other .vdi files are between 0.5 and 3 GB, most between 1 and 2 GB. The corresponding .sav files are between 0.5 and 1 GB. The Internet connection where I was doing this is limited to 30KB/s download, which, constantly saturated, works out to less than 3 GB per 24 hour period. Is this normal? Is there something that can be done to make snapshots more practical? update On starting up the VM again, I've noticed that mscorsvw is using significant processing time. Apparently this process [precompiles .NET assemblies]. This may have been going on during the period when I was taking snapshots, which might explain some of the snapshot size increase. I would be somewhat surprised to learn that this could be responsible for over 10 GB of additional disk usage, or that it would run for roughly 24 hours. Is this possible?

    Read the article

  • SQL SERVER – Simple Example of Snapshot Isolation – Reduce the Blocking Transactions

    - by pinaldave
    To learn any technology and move to a more advanced level, it is very important to understand the fundamentals of the subject first. Today, we will be talking about something which has been quite introduced a long time ago but not properly explored when it comes to the isolation level. Snapshot Isolation was introduced in SQL Server in 2005. However, the reality is that there are still many software shops which are using the SQL Server 2000, and therefore cannot be able to maintain the Snapshot Isolation. Many software shops have upgraded to the later version of the SQL Server, but their respective developers have not spend enough time to upgrade themselves with the latest technology. “It works!” is a very common answer of many when they are asked about utilizing the new technology, instead of backward compatibility commands. In one of the recent consultation project, I had same experience when developers have “heard about it” but have no idea about snapshot isolation. They were thinking it is the same as Snapshot Replication – which is plain wrong. This is the same demo I am including here which I have created for them. In Snapshot Isolation, the updated row versions for each transaction are maintained in TempDB. Once a transaction has begun, it ignores all the newer rows inserted or updated in the table. Let us examine this example which shows the simple demonstration. This transaction works on optimistic concurrency model. Since reading a certain transaction does not block writing transaction, it also does not block the reading transaction, which reduced the blocking. First, enable database to work with Snapshot Isolation. Additionally, check the existing values in the table from HumanResources.Shift. ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON GO SELECT ModifiedDate FROM HumanResources.Shift GO Now, we will need two different sessions to prove this example. First Session: Set Transaction level isolation to snapshot and begin the transaction. Update the column “ModifiedDate” to today’s date. -- Session 1 SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN UPDATE HumanResources.Shift SET ModifiedDate = GETDATE() GO Please note that we have not yet been committed to the transaction. Now, open the second session and run the following “SELECT” statement. Then, check the values of the table. Please pay attention on setting the Isolation level for the second one as “Snapshot” at the same time when we already start the transaction using BEGIN TRAN. -- Session 2 SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN SELECT ModifiedDate FROM HumanResources.Shift GO You will notice that the values in the table are still original values. They have not been modified yet. Once again, go back to session 1 and begin the transaction. -- Session 1 COMMIT After that, go back to Session 2 and see the values of the table. -- Session 2 SELECT ModifiedDate FROM HumanResources.Shift GO You will notice that the values are yet not changed and they are still the same old values which were there right in the beginning of the session. Now, let us commit the transaction in the session 2. Once committed, run the same SELECT statement once more and see what the result is. -- Session 2 COMMIT SELECT ModifiedDate FROM HumanResources.Shift GO You will notice that it now reflects the new updated value. I hope that this example is clear enough as it would give you good idea how the Snapshot Isolation level works. There is much more to write about an extra level, READ_COMMITTED_SNAPSHOT, which we will be discussing in another post soon. If you wish to use this transaction’s Isolation level in your production database, I would appreciate your comments about their performance on your servers. I have included here the complete script used in this example for your quick reference. ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON GO SELECT ModifiedDate FROM HumanResources.Shift GO -- Session 1 SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN UPDATE HumanResources.Shift SET ModifiedDate = GETDATE() GO -- Session 2 SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN SELECT ModifiedDate FROM HumanResources.Shift GO -- Session 1 COMMIT -- Session 2 SELECT ModifiedDate FROM HumanResources.Shift GO -- Session 2 COMMIT SELECT ModifiedDate FROM HumanResources.Shift GO Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: Pinal Dave, SQL, SQL Authority, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology Tagged: Transaction Isolation

    Read the article

  • SQL SERVER – Simple Example of Snapshot Isolation – Reduce the Blocking Transactions

    - by pinaldave
    To learn any technology and move to a more advanced level, it is very important to understand the fundamentals of the subject first. Today, we will be talking about something which has been quite introduced a long time ago but not properly explored when it comes to the isolation level. Snapshot Isolation was introduced in SQL Server in 2005. However, the reality is that there are still many software shops which are using the SQL Server 2000, and therefore cannot be able to maintain the Snapshot Isolation. Many software shops have upgraded to the later version of the SQL Server, but their respective developers have not spend enough time to upgrade themselves with the latest technology. “It works!” is a very common answer of many when they are asked about utilizing the new technology, instead of backward compatibility commands. In one of the recent consultation project, I had same experience when developers have “heard about it” but have no idea about snapshot isolation. They were thinking it is the same as Snapshot Replication – which is plain wrong. This is the same demo I am including here which I have created for them. In Snapshot Isolation, the updated row versions for each transaction are maintained in TempDB. Once a transaction has begun, it ignores all the newer rows inserted or updated in the table. Let us examine this example which shows the simple demonstration. This transaction works on optimistic concurrency model. Since reading a certain transaction does not block writing transaction, it also does not block the reading transaction, which reduced the blocking. First, enable database to work with Snapshot Isolation. Additionally, check the existing values in the table from HumanResources.Shift. ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON GO SELECT ModifiedDate FROM HumanResources.Shift GO Now, we will need two different sessions to prove this example. First Session: Set Transaction level isolation to snapshot and begin the transaction. Update the column “ModifiedDate” to today’s date. -- Session 1 SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN UPDATE HumanResources.Shift SET ModifiedDate = GETDATE() GO Please note that we have not yet been committed to the transaction. Now, open the second session and run the following “SELECT” statement. Then, check the values of the table. Please pay attention on setting the Isolation level for the second one as “Snapshot” at the same time when we already start the transaction using BEGIN TRAN. -- Session 2 SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN SELECT ModifiedDate FROM HumanResources.Shift GO You will notice that the values in the table are still original values. They have not been modified yet. Once again, go back to session 1 and begin the transaction. -- Session 1 COMMIT After that, go back to Session 2 and see the values of the table. -- Session 2 SELECT ModifiedDate FROM HumanResources.Shift GO You will notice that the values are yet not changed and they are still the same old values which were there right in the beginning of the session. Now, let us commit the transaction in the session 2. Once committed, run the same SELECT statement once more and see what the result is. -- Session 2 COMMIT SELECT ModifiedDate FROM HumanResources.Shift GO You will notice that it now reflects the new updated value. I hope that this example is clear enough as it would give you good idea how the Snapshot Isolation level works. There is much more to write about an extra level, READ_COMMITTED_SNAPSHOT, which we will be discussing in another post soon. If you wish to use this transaction’s Isolation level in your production database, I would appreciate your comments about their performance on your servers. I have included here the complete script used in this example for your quick reference. ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON GO SELECT ModifiedDate FROM HumanResources.Shift GO -- Session 1 SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN UPDATE HumanResources.Shift SET ModifiedDate = GETDATE() GO -- Session 2 SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN SELECT ModifiedDate FROM HumanResources.Shift GO -- Session 1 COMMIT -- Session 2 SELECT ModifiedDate FROM HumanResources.Shift GO -- Session 2 COMMIT SELECT ModifiedDate FROM HumanResources.Shift GO Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: Pinal Dave, SQL, SQL Authority, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology Tagged: Transaction Isolation

    Read the article

  • Samba smb.conf read only and read/write accounts

    - by Pieter
    Below you can see my smb.conf, pieter is my admin user read/write on the shares works good with that account. Then I have a leecher account that has been added with smbpasswd -a leecher to the smb users, it is set up so this user only has read access to the shares. This works on MegaSam and on Thumbnails but not on my other drives, leecher does not get any access on the other shares. [global] security = user [MegaSam] comment = MegaSam path = /media/MegaSam browsable = yes guest ok = no read list = leecher write list = pieter create mask = 0755 [SilentBob] comment = SilentBob path = /media/SilentBob browsable = yes guest ok = no read list = leecher write list = pieter create mask = 0755 [Thumbnails] comment = Thumbnails path = /media/Thumbnails browsable = yes guest ok = no read list = leecher write list = pieter create mask = 0755 [Downloads] comment = Downloads path = /media/Downloads browsable = yes guest ok = no read list = leecher write list = pieter create mask = 0755

    Read the article

  • Online remount btrfs of root filesystem with different subvolume (snapshot) [migrated]

    - by goncalopp
    Let's say you have a btrfs root filesystem on an online system. You want to revert the filesystem to an earlier state, of which you have a snapshot: remount /dev/sdaX / -o remount,subvol=snapshots/Y For the record, I've done this in a test system, and it does not work. The command returns with no errors, but the subvolume mounted is the same. If this did work, what would be the consequences? My guess is that open file descriptors would still point to the old subvolume, thus possibly leading to "data loss" on the new subvolume, if one was not careful. Assuming one goes to the trouble of closing and reopening all open file descriptors, does this sound feasible? Or are there other types of problems?

    Read the article

  • Instant database snapshot

    - by raj
    My product uses oracle 9 database in its backend. every week the new release of the product is launched which will want to fire some DML, DDL queries to the database. I usually test the product release in a dummy database before applying it in the main database. I create a database dump using exp command, then import them into dummy database using imp. then i test the product in the dummy database and checks if there are any errors. This exp and imp takes about 3 hours to complete. Is there any alternative as : instant snapshot of the live database (which will be independent of the live one)? or is there any option to keep dummydatabase in sync with the originl database always. Yhis can be done by making the product firing DML&DDL queries to both the databases.. but this will be a HUGE performance problem.. how can i overcome this?

    Read the article

  • Suscribers locking during snapshot replication

    - by remi_bourgarel
    Hi all :) Here is my architecture : I have a main server and 4 replica (the servers are synchronized with snapshot replication). The replication is working fine, except for one thing : during the replication a lot of SELECT request on one of the replica fail with a time-out. Here are my questions : Can I avoid these time-out ? If I can't : how can I detect the start and the end of the replication to redirect all the request on one of the replica to the main ? Thanks Sorry if you already answered to that kind of question but I couldn't find anything.

    Read the article

1 2 3 4 5 6 7 8 9 10 11 12  | Next Page >