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 RepliesI 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
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
HiI have a SQL2000 server with 128m rows of data. I want to delete about65m of that. So far I have bcp'ed the relevent data out and put theminto another SQL database.We have a small amount of space for our transaction log so I cannotdelete all 65m rows in one go. So far I have been doing them is 0.5mchuncks, but it is extremly slow.Would a faster way be to bcp the data I wan to keep and truncate thetable and bulk import them in again ?What hapnes to log size in when builk import is happening and is thereanother way of doing this ?Thanks for any help
View 2 Replies View Related
Hi. Does anyone know why a delete statement sometimes hangs when deleting a large amount of data and how to resolve this?
Thanks!
I had write a ActiveX service to delete several tables and those records are more than 100000. When I test it by deleted 1000 records it is ok, but once the volum is increase until 100000, it will give me a error message said timeout operation fail.
how can i overcome this problem. please!!!!
I have a table with about 466 Million rows. In this table there is a int column called WeeksToRetain as well as a EventDate column containing the date the row was inserted. I am trying to delete all the rows that that should be deleted according to the WeeksToRetain. For example, if the EventDate is 5/07/15 with a 1 in the WeeksToRetain column the row should be removed by 5/14/15. I am not sure what days SQL considers the beginning and end of the week. However the core issue I am having is the sheer mass of deletions I must do and log growth.
So I am trying to do the delete in batches. More specifically I want to load a temporary table with a million rows, then use the temporary table to load a sub temporary table with 100,000 rows and join this temporary table to the table I want to delete from looping through 10 times to get 1 million. The Logging.EvenLog table which is the table I'm trying to purge has a clustered index on EventDate (ASC). I would like to run this in a schedule job with enough time between executions for log backups to run.
DECLARE @i int
DECLARE @RowCount int
DECLARE @NextBatchDate datetime
CREATE TABLE #BatchProcess
(
EventDate datetime,
ApplicationID int,
[Code] .....
I have the following scenario:
SQL database on SQL 2012
Large Production table 15 Million record
The table has 3 years of data
New monthly data is being added every month.
A New Monthly data is being loaded, checked and finally approved after 6 or 7 iteration before approval.Because of this iteration the monthly data set is being added then deleted then added then deleted few times.Because the table is big this process takes time, any thoughts on how to make the delete insert process faster.Keep in mind I cannot do much because it is a production table and is being access by other users to do other analysis.
Delete is done based on trx_date which is a year/month combo, like 201508.
The table has monthly sales by customer aggregated.
The table structure is:
CREATE TABLE [dbo].[Sales](
[batch_key] [int] NOT NULL,
[Company_key] [int] NOT NULL,
[customer_key] [char](22) NOT NULL,
[Trx_Date] [int] NOT NULL,
[account] [nvarchar](35) NOT NULL,
[code].....
Currently we has a database of size about 300G. Because our backup system failed some time past we were left with a transaction log file which grew to about 160G. However our backups are working again and everything is working fine. My understanding is that now the transaction log file is practically empty but the capacity remains at 160G.
When you delete records the deleted transactions are going to get logged to the transaction file. My understanding is when a backup is done these transactions get discarded out of the transaction file.
could I make use of this relatively large transaction file and start deleting transactions without out actually adding to the transaction file size.
The plan is to delete records from logging tables that are not referenced to by any other table without this increasing the transaction log file.For example over a period of a few weeks we can delete a chunk of records from a table. Then after it has completed a backup we can delete another chunk of records out of this table until we have got the table down to the records that we now need.Will this work?
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 RelatedTo 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
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!
All,
Can everyone tell me how I can view the contents of a transaction log in SQL Server 7.
Many Thanks
Mathew hayward
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 RelatedHi 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
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
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 RelatedHi,
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
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!!
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
[]
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
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...
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.
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 RelatedHallo 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.
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
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
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
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
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.
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!
Hi experts,
Pls let me know , how to make normal db to readonly + accept tran logs
Rgds
Srinivas Varanasi.
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...