August 28, 2008 at 1:47 pm
lets say I'm working on 'fixing' something on a data base. When working with Query Analyizer or whatever program, I manually execute 'begin transaction'. What resources, if any, will this lock up, or is it safe to do this whenever?
I guess what I'm saying is, sometimes I work on user tables where I have to update thousands of users with potentially damaging adhoc queries. If I spent 30minutes/1hr/etc working on a table, is it 'ok' to just 'begin transaction' when i start working and work on it for an hour or 2 before committing? Typically while I'm working on a DB, it's not in use.
Also, is there a way to get a list of current oustanding transaction, incase i make sub transactions and forget how many times I have to commit before everything take affect?
August 28, 2008 at 3:34 pm
Working as you state
I guess what I'm saying is, sometimes I work on user tables where I have to update thousands of users with potentially damaging adhoc queries
on the "live" database I would say is close to playing Russian Roulette with a fully load pistol. Might I suggest obtaining the "Developer Edition" of SQL Server, installing it on your desktop machine. Detach the "live" db, copy the MDF and LDF files to your desktop, reattach the "live" database and do NOT touch it again. Attach the desktop copies to the Developer SQL Server on your desktop. Test, test, test your adhoc queries when you are sure they work correctly test again. Then save the individual proven adhoc queries and continue with the next. Then backup your "Live" database and log file(s). When you are satisfied you will do NO damage to the "live" system using the saved T-SQL create / run them on the "Live" system.
Remember the old saying "What can go wrong will go wrong" or in the vernacular "Cover your own a**"
As an after thought the Developer Edition for SQL2005 is about $45 (USD) and the edition for SQL 2008 is about $99 (USD) and both are available from Amazon.com
August 28, 2008 at 3:38 pm
The BEGIN TRANSACTION itself doesn't hold any resources as such, but once you do any INSERT, UPDATE, DELETE, or SELECT depending on your transaction isolation level, then locks start taking place, and are held until a COMMIT or ROLLBACK occurs.
If you just want to know how many transactions you have open, you can use @@TRANCOUNT. If you want to know what objects are locked, try something like this:
select d.name as database_name, o.name as object_name, l.*
from sys.dm_tran_locks l
inner join sys.databases d on l.resource_database_id = d.database_id
inner join sys.objects o ON l.resource_associated_entity_id = o.object_id
where l.resource_type = 'OBJECT'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply