If @@trancount &> 0 Commit Tran
Apr 28, 2003
Hi,
Reviewing the MSSQL process info screen, I am seeing the same process appear a numer of times. It is always the same, being
'IF @@TRANCOUNT > 0 COMMIT TRAN'
Sometimes, there can be up to a hundred of these processes (listed in the process info screen). They generally have a 'sleeping' status, but nonetheless, I would like to see these processes disappear if they are not being used.
I have checked in all of the stored procedures and triggers in the application, and none have this sql statement.
When I run profiler, I get these entries, but the profiler says they belong to either SQL Enterprise Manager or 'Microsoft Windows 2000 Operating System', and not to the application I am running.
Does anyone know where these transactions come from? Can I prevent these from appearing? If no, what is the impact (other than sql server having to maintain a connection).
Thanks,
Jim
View 6 Replies
ADVERTISEMENT
Oct 8, 2007
Hi,
I want to rollback my t-sql if it encounters an error. I wrote this code:
begin tran mytrans;
insert into table1 values (1, 'test');
insert into table1 values (1, 'jsaureouwrolsjflseorwurw'); -- it will encounter error here since max value to be inputted is 10
commit tran mytrans;
I forced my insert to have an error by putting a value that exceeds the data size. However, I didn't do any rollback. Anything i missed out?
cherriesh
View 4 Replies
View Related
Mar 28, 2008
Microsoft SQL Server Management Studio Express
select @@trancount
begin tran
select @@trancount
use ProdNetPerfMon
select @@trancount
update Nodes set Caption = 'xxxx' where Vendor = 'yyyy'
select @@trancount
commit tran
select @@trancount
Executes as expected including trancount without errors.
Nodes.Caption is updated but reverts after a few minutes.
sa privileges
What am I missing?
View 1 Replies
View Related
Mar 1, 2006
Hi
Can you think about any reason for why when using a transaction after the COMMIT TRAN the inserted new record is not in the table and there is a gap in the identity????
I'm using SQL 2000 SP3, there are no triggers are on the table and it happanes only under heavy load.
Thanks,
Inon.
View 9 Replies
View Related
Dec 19, 2007
I am running an Execute SQL task that does a Begin Tran, then the next task in the sequence is a data task which imports a XML file into two tables. If i doo a Rollback Tran only one of the two tables is rolled back.
Is it possible to have both tables rolled back from one Begin tran command or do i need to split the datatasl into two and treat each import as a seperate issue ?
The connection is set to retainsameconnection
thanks
View 7 Replies
View Related
Jun 28, 2007
Take a look a the sql code below. Can anyone explain why @@trancount increased to 2 after the 2nd print statement?
SET ANSI_DEFAULTS ON
GO
PRINT @@TRANCOUNT
GO
BEGIN TRANSACTION
PRINT @@TRANCOUNT
GO
Here is the output I got
0
2
View 1 Replies
View Related
Jul 20, 2005
During testing of an application, i noticed a difference betweenSQL 2000 and SQL 7, both with identical config.In a nutshell:A table has a trigger for UPDATE and DELETE.When a column in the table is UPDATED the following happens:In autocommit mode, when entering a trigger the trancount equals1 for both SQL 7 and 2000.When the same update is performed in an explicit transactionin SQL 7 @@TRANCOUNT equal 2, and in SQL 2000 @@TRANCOUNT equals 1.Configuration is the same and there are no implicit transactions.I don't need a work around as this will invalidate the migrationprocess as both products should behave identically.What would influence the difference or why is there a difference???Is there something which has been overlooked?================================================== =======The following code replicates the problemEnsure implicit transactions are off in both versions at the serverlevel, thus defaulting to autocommitted mode.Ensure sp_configure settings are identical.Step 1: Create a DB called test:Step 2: Execute the following under the context of test DB.if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[trigtest]') and OBJECTPROPERTY(id, Outrigger') = 1)drop trigger [dbo].[trigtest]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[test]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[trancount]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[trancount]GOCREATE TABLE [dbo].[test] ([id] [int] IDENTITY (1, 1) NOT NULL ,[text] [char] (10) NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[trancount] ([id] [int] IDENTITY (1, 1) NOT NULL ,[trancount] [int] NOT NULL) ON [PRIMARY]GOCREATE TRIGGER trigtest ON [dbo].[test]FOR UPDATE, DELETEASdeclare @trancount intselect @trancount = @@TRANCOUNTinsert into trancount ( trancount ) values ( @trancount )Step 3: Run the following against the DB, then check trancount table.-- Add a record to the test table (trigger will not fire)insert into test (text) values ( 'xxxx' );go-- Update the value (autocommit mode) to fire trigger-- Under SQL 7 and 2000, trancount table will only indicate 1tranaction open.-- This is being performed in autocommit mode.update test set text = 'test1'go-- Update value using an explicit transaction-- Under SQL 7, trancount will equal 2 in trigger, in SQL 2000trancount equals 1begin transactionupdate test set text = 'test2'commit workgo
View 5 Replies
View Related
Sep 26, 2007
Hi all,
Is there any way to get the @@trancount for a connection from outside the connection?
The reason i'm asking is that a customer just lost a days work, and there is nothing in any table from a certain time onward. One theoy is that a backup was restored, but we checked and that is not the case.
So another theory is that a certain sproc began a transaction, but never finished because of an error. (We had some strange timeouts as well, so this is quite plausible.)
So the question: Can i get a list of current connections with their trancount? I could just run this to see if a certain connection would never get back to zero to check the transaction theory.
Thanks in advance,
Gert-Jan
View 7 Replies
View Related
Apr 7, 2003
I have a transaction log that is over f gig in size....what can be done with this..and what are the pros and cons if I delete it...also how can I keep this from getting that big in the future. Thanks!
View 2 Replies
View Related
Apr 20, 2001
To All:
On my SQL 6.5 box, I have a corrupt Tran log. I do not use my Tran log but now I am getting an 1105 error, that the log is full. I run Dump tran with no log but it does not work. I cannot perform any other function without getting the 1105 error. Now I tried to reboot and now it is hanging during reboot. It is hanging while checking the partition where the tran log resides. I went in to VGA Mode. Any ideas would be appreciated.
Many thanks,
Kelly
View 1 Replies
View Related
Aug 14, 2001
Does this seem right? We have our transaction logs set to "Truncate Log on Checkpoint" and they still grow over 1GB. Is it possible that one transaction (to a checkpoint) generates this much logged information? Will transaction log backups every 5-10 minutes help me out better or is this just a poorly written application?
Thanks!
View 4 Replies
View Related
Oct 11, 1999
All,
Can everyone tell me how I can view the contents of a transaction log in SQL Server 7.
Many Thanks
Mathew hayward
View 3 Replies
View Related
Aug 13, 1999
Help. I have a database with high transaction rates. THe log is 300 mbs. No matter what i do I cannot get it below 64%. I have dumped and trucated the log yet it will not budge. Being friday and it being a time card application this is my heaviest transaction day. Please help
View 2 Replies
View Related
Aug 22, 2002
Hi There,
How do I find the space used for the tran log of the db. sp_spaceused gives the space used for the complete database. but I need the space used for a tran log alone.
Thanks in advance.
pete
View 1 Replies
View Related
Oct 19, 2000
What is the advantage of taking frequent tran log backups (say every 30mins) as opposed to once a day? Say, I backup data and tran log once every night and I lost a table at 10:00am next day. Can't I recover the database to the point in time by restoring the previous night's backup and then applying the transaction log from previous night and then applying the transaction log (to the point int time) that you just dumped when the mishap was reported to you?
Thanks
View 2 Replies
View Related
Oct 22, 2002
We are experiencng high cpu utilization across all 4 cpu's at the top of the hour when our transaction log dump job runs. Has anyone observed this bahavior before? Is there anything we can do to mitigate this? Thank You.
View 4 Replies
View Related
May 16, 2006
I have a database of 22 gb in sql 2000, my database option is set to full recovery mode, the problem i'm having is the tran log is growing too fast, this morning it was 24 gb, more than the database size. Can anyone help how I can keep it in a managable size?
Thanks in advance!!
View 2 Replies
View Related
Mar 23, 1999
Hello,
My Database has 1024 Mb and a log with 384 Mb. The log is 72% full.
None of the options is checked in that DataBase.
I try Dump Tran with no_log, Dump Database and Dump Tran with Truncate_only, click the button of Truncate Transaction Log and more.
And the Log is always the same.
Why I canīt truncate the log? What I must to do?
TIA
[]
View 3 Replies
View Related
May 14, 2001
All my jobs run fine, except for a Transaction Log Backup job that fails with
the following error: Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'Server1' as 'sa' (non-trusted)
Starting maintenance plan 'MaintPlan-TLogs- AllData' on 5/12/2001 10:00:01 AM
Backup can not be performed on database 'AllData'. This sub task is ignored.
I have not change the sa or agent password.
I cannot figure out why this job started failing, it ran fine for a while.
Any insight is appreciated.
Thanks
View 1 Replies
View Related
Dec 19, 2007
The former programmer wrote this stored procedure. It haven't been run for a while, so I was given the assignment to get it working. When I ran the stored procedure, it took almost 9 hours. Then I found that I can't access a few tables, so my guess it there is some issues with table locking. The stored procedure use this...
Code:
BEGIN TRAN
--blah blah
COMMIT TRAN
ERROR_HANDLER:
ROLLBACK TRAN
Obviously there seem to be a logic error in the middle of the script while running the stored procedure. So, how do I cancel the transaction and unlock the table? I'm unable to access the few tables.
Also, does rebooting the computer helped to release the transaction or table locking?
Thanks...
View 3 Replies
View Related
Nov 22, 2004
Hi,
I have my tran log backup running every 30 mins. One of the log backups took 36 mins to complete. So at a time I would have two log backups taking place. It seems to me that the 2nd log back up did not happen at all ( I checked in the EM) as the first one wasn't completed by then.
I am kind of lost here as to how to proceed.
Please advise.
Thanks.
View 2 Replies
View Related
May 26, 2004
I have a table that has 110 million records, I will be deleting over 60 million records, but I dont have enough space to hold all the deletes on the trans log file. Is there a way to delete 1 million record then free the trans action then run another delete in one script.
View 1 Replies
View Related
Jul 23, 2005
Hi,This is probably a trivial question, as I am simply trying to execute aBACKUP LOG database WITH NO_LOG. Except T-SQL will not accept anythingI type when database has a - in it, for example data-base. I havetried:BACKUP LOG 'data-base' WITH NO_LOGBACKUP LOG "data-base" WITH NO_LOGBACKUP LOG (data-base) WITH NO_LOGBACKUP LOG N'data-base' WITH NO_LOGBACKUP LOG N"data-base" WITH NO_LOGBACKUP LOG N('data-base') WITH NO_LOGBACKUP LOG N("data-base") WITH NO_LOGAll T-SQL will tell me is "syntax error" and Books Online has noinformation. So... ???Thank you!
View 1 Replies
View Related
Mar 29, 2006
Hallo All,
Can somebody explain why the same function works different with MS SQL 2000 and MS SQL 2005?
On both systems 2000 and 2005 I have 2x databases named ACCT and PROD (actually only a test environment).
On both systems I try to execute the following statements:
BEGIN TRAN TRAN_01 WITH MARK 'My TRAN_01'
USE PROD
INSERT INTO [PROD].[dbo].[_PROT]([STR_COMMENT], [R_NUM_T1], [R_NUM_T2])
VALUES('PROT_COMMENT', 1004, 1004)
USE ACCT
INSERT INTO [ACCT].[dbo].[_PROT]([STR_COMMENT], [R_NUM_T1], [R_NUM_T2])
VALUES('PROT_COMMENT', 1004, 1004)
COMMIT TRAN TRAN_01
After executing the statements I start the following query:
SELECT * FROM [msdb].[dbo].[logmarkhistory]
On MS SQL 2000 I get as results:
PROD TRAN_01 My TRAN_01 SUPPORTAdministrator 3944000000107600001 2006-03-29 17:15:13.930
ACCT TRAN_01 My TRAN_01 SUPPORTAdministrator 8000000009200001 2006-03-29 17:15:13.930
Seems to be correct. I think it is the way it should work according to the documentation.
On MS SQL 2005 I only get the following results:
PROD TRAN_01 My TRAN_01 SU 29000000107800001 2006-03-29 17:31:32.283
There are no entries in the table for the ACCT database and the account / user_name is shown incorrectly.
It seems to be an ERROR in the processing of such marked transactions in MS SQL 2005.
View 3 Replies
View Related
Aug 2, 2000
I seem to be having a problem on all of my SQL servers. WHen I or a developer attmept to do a delete on a table i get a Log file for database is full. I truncate the log try again and get the same error. IT doesnt seem to matter how much is being deleted or how big the table is. THis is very strange and very frustrating.
Thanks
David
View 5 Replies
View Related
Jan 22, 2001
I have recently implemented a backup solution that keeps our standby server up-to-date with nightly database backup and restores. Ironed out all the problems with syslogins and orphan users. It's been working very well over the past few weeks.
On the production server we do hourly tran log dumps every hour between 8am and 10pm. I would like to implement some form of "log shipping" to bring the window of vulnerability down to 1 hour. By making some alterations to my current process I been able to incorporate the hourly log dumps in the same process.
However, when I try to restore the log on the standby servre I get the following message,
Server: Msg 4305, Level 16, State 1, Line 1
Specified file 'HODB1SQLBACKUPRace_Prd_T_dump.200101231513' is out of sequence. Current time stamp is Jan 23 2001 2:01PM while dump was from Jan 23 2001 3:01PM.
After a bit of investigation I found the dumptrdate field in the sysdatabases table and it was set to Jan 23 2001 14:01. After updating this field to Jan 23 2001 14:01, I ran the load command again but received the same error message.
Is there something else that needs updating on the standby server? Is what I'm trying to do possible in 6.5? Any help or ideas would be greatly appreciated.
Thanks
Phill
View 2 Replies
View Related
Apr 2, 2008
Hi:
I have maintenance plan on DBABC backup log to .trn job to run every 90 minutes (daily).
in order to keep the log file small, I also set up a job (T-SQL) to run at 4:15 am to backup log ABC with truncate_only, then run dbcc shrinkdatabase (DBABC, 10)
it looks "backup log ABC with truncate_only" has conflicts with the every90 minutes backup transaction log.
Question: could I keep the backup transaction log every90 minutes, but still could shrink the log file. The log file is growing very fast.
Or I have to use differential backup instead of backup tran log?
thanks
David
View 4 Replies
View Related
Mar 28, 2002
I noticed our log files are getting way too big. I found that a previous
SQL guy had set up the following scheduled job with the TSQL statement:
BACKUP LOG DEV TO DevDailyTranLog with noinit
This runs every 20 minutes.
There's also a Maint Plan to do a Complete backup every night and a
Transaction Backup every hour (could be set to 20 min)
Why do you suppose the BACKUP LOG job exists ? If the maint plan were
set to backup transaction every 20 min, wouldn't the 2 jobs be
duplicating each other ?
Also, I notice that the Tran Logs specified in the database properties
seem awfully big (4 gig), shouldn't they automatically be truncated when
the daily full backup occurs ? (full recovery model)
Feel free to call me and straighten me out or to get more info.
Win2K, SQL 2K
gdunn@taunton.com
203-426-8171 x 374
View 2 Replies
View Related
Mar 16, 2001
Hi All,
Is there a way to suppress or re-direct the messages that result from a tran. log dump? We're running a tran. log dump every 15 minutes in prod. and the errorlog is really cumbersome to look at.
(In another RDBMS I worked with all backup messages went to a separate error/message log.)
TIA!
View 2 Replies
View Related
Jun 21, 2004
Hi experts,
Pls let me know , how to make normal db to readonly + accept tran logs
Rgds
Srinivas Varanasi.
View 2 Replies
View Related
Oct 9, 2004
Well, I can't backup up my tran log - too big. I knew enough to know you have to back it up, didn't know enough to know that tran log backup is not included in "Full backup". Contractor installation set Growth to "un-restricted". Me, too swamped to delve deeply in time to learn this is a recipe for disaster, and then it was too late - System down because tran log filled up the disk...
(Ironically, this happened the morning of the first day of some training they sent me to - Microsoft's Programming a Microsoft SQL 2000 Database...)
So, some details... The drives on the system :
___ c: size 12G, free 7G
___ d: size 44.9G, free 10M
___ e: size 44.9G, free 13G
Tran log info :
___ filename .... d:Microsoft SQL ServerMSSQLdatahorizon_Log.LDF
___ name ........ horizon_Log
___ size ........ 5606392
___ maxsize ..... -1
___ growth ...... 10
___ status ...... 1081410
___ filename ...E:Horizon_Log2Horizon_Log2_Temp
___ name ........ horizon_1_Log
___ size ........ 167168
___ maxsize ..... 640000
___ growth ...... 640
___ status ...... 32834
(there are 2 because the other totally green newbie dba added 1 the day the system went down...)
I *thought* the addition of the 2nd tran log file meant the 1st file was no longer part of the picture, and that my available free disk space was sufficient to backup the 2nd tran log file.
Clearly I'm missing something - when I try to back up, I get this in SQL server error log :
___ Operating system error 112(There is not enough space on the disk.)
:confused:___ Does that mean it's trying to backup both the 1st & the 2nd tran logs?
:confused:___ Or is it only backing up the 2nd log, but trying to use D: (with only 10M)?
:confused:___ Or, is it using C: or E:, but tran log backups need some multiple of the size of the actual log, and there's not enough space even though there are multiple G?
I've been reading & googling. So far tried to truncate both files.
( dbcc shrinkfile('horizon_1_log',truncateonly) )
No luck, got these :
___ Cannot shrink log file 2 (horizon_Log) because all logical log files are in use.
___ Cannot shrink log file 3 (horizon_1_Log) because all logical log files are in use.
:confused:
:confused:___ How do I get them to be not "in use"?
:confused:___ Must the system be down for that? (certainly seems to...)
Also, checked out the EMPTYFILE option. Books Online says :
"Migrates all data from the specified file to other files in the same filegroup"
:confused: ___ Migrates to which "other files", exactly?
:confused: ___ Is this the right way to go?
:confused: ___ If so, how would I proceed after achieving a successul EMPTYFILE operation?
Also...
:confused: ___ In the database's Properties dialog, Transaction Log tab, there's a Delete button. Could that button be used to get me out of this difficulty?
Last, but not least... If I'm going about this altogether wrongly, can I get a pointer or two?
TIA for any and all feedback...
View 10 Replies
View Related
Mar 9, 2004
Good morning all!
I've created a a new column on a table:
CD_Line int identity
This ADD COLUMN command gave me an error on this morning, we're talking about 40 million rows on this table.
The ROLLBACK TRAN is destroying my system that can't login on the SQL Server and no application can by Database at this moment.
Do anybody know how to STOP this situation and recovery the system?
Tks!
Rafael
View 2 Replies
View Related
Jan 21, 2015
Should BEGIN TRAN ...COMMIT be used in a procedure body if I have only select statements??
CREATE PROCEDURE [dbo].[procname]
@param1int
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
[code]....
View 2 Replies
View Related
