How can I create a clone of a database on the fly?

Question

How can I create a clone of a database on the fly?

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

I'll look at the T-SQL tonight. Not sure yet if there are a wad of cursors. You're saying that would explain the X locks?

Yep... could be... if they're not "Fire Hose" (Forward Only) cursors...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

noeld SSC Guru Points: 96590 More actions · Answer 2

What are Snapshots? In Oracle, Snapshots were a precursor to materialized views, similar (in theory) to SQL 2000's indexed view...which never quite worked.

Database Snapshots are just sparse files that mimic the status of a Database at a point in time and had nothing to do with "materialized(indexed) views".

These files do slow down the insert speed but locking contention is supposed to decrease and your apps can read from the "Snapshot"

* Noel

Jason Wisdom-380639 SSCrazy Points: 2083 More actions · Answer 3

Snapshots in this way almost sounds like a poor man's Snapshot Replication.

I think the problem is cursors. I scriped every Stored Proc in the database, and yes thare are a LOT of cursors. 3/4ths are opened as "LOCAL FORWARD_ONLY STATIC READ_ONLY", while the others don't have any keywords added at all.

What is the default, when nothing is specified? Is it Static, Dynamic, Keyset? Local or Global? Read_Only or read/write?

LOCAL FAST_FORWARD seems the way to go.

Thanks a bunch...everyone. Your ideas gave me ideas I wouldn't have had.