Delete large table in chunks to minimise log file growth
in [Server Configuration]
|
From: John Brown on 11 Apr 2010 08:34 Helo All, I am deleting a large table in chunks to: 1) Avoid locking issues 2) Restrict log file growth Algorithm init_size = current size of transaction log while TRUE delete X records matching criteria current_size = current size of log If no records deleted then exit while if current_size - init_size > Y MB shrink log file end while I tried it with X = 200 and Y = 50. In practice, I would not try to restrict log growth to 50 MB. I just needed to see what was going on. I shrink the log file using the procedure at http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/ DBCC SHRINKFILE(<TransactionLogName>, 1) BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY DBCC SHRINKFILE(<TransactionLogName>, 1) I changed the backup command to BACKUP LOG <DatabaseName> TO DISK=(a)logfile_bak because TRUNCATE_ONLY is not supported on SQL Server 2008. Some output: cur_size = 48 -- MB cur_size = 53 Cannot shrink log file 2 (MyDB_log) because the logical log file located at the end of the file is in use. DBCC execution completed. If DBCC printed error messages, contact your system administrator. Processed 6083 pages for database 'MyDB', file 'MyDB_log' on file 20. BACKUP LOG successfully processed 6083 pages in 6.530 seconds (7.276 MB/sec). Cannot shrink log file 2 (MyDB_log) because the logical log file located at the end of the file is in use. DBCC execution completed. If DBCC printed error messages, contact your system administrator. shrunk log MyDB_log /* As you see above, the shrink did not succeed */ cur_size = 56 Cannot shrink log file 2 (MyDB_log) because the logical log file located at the end of the file is in use. DBCC execution completed. If DBCC printed error messages, contact your system administrator. Processed 1742 pages for database 'MyDB', file 'MyDB_log' on file 21. BACKUP LOG successfully processed 1742 pages in 1.514 seconds (8.985 MB/sec). Cannot shrink log file 2 (MyDB_log) because the logical log file located at the end of the file is in use. DBCC execution completed. If DBCC printed error messages, contact your system administrator. shrunk log MyDB_log cur_size = 4 Note that although it said that it could not shrink the log file, it *did* shrink it, because on the next iteration, the log file size = 4 MB. I discover the log file size by looking in sysfiles. I divide by 128 to get MB. Questions: 1) Why did it say that it could not shrink the file, even though the file shrunk? 2) In order to reduce unnecessary attempts to shrink, how can I find out if 'the logical log file located at the end of the file is in use' before I try to shrink the file? I am aware of the undocumented "dbcc log", but how would I access the output in a t-sql script? Regards, Alias John Brown
From: Uri Dimant on 11 Apr 2010 09:51 John You can run DBCC Loginfo(db_id) command to look at status column. If you see at the bottom the status =2 that means SQL Server soemhow 'needs' the transaction and solution is to BACKUOP the log file or running dummy INSERT to 'move' active transaction at the beginning. BTW , I use the below technique to remove large portions of the data WHILE 1 = 1 BEGIN DELETE TOP(2000) FROM Foo WHERE <predicate>; IF @@ROWCOUNT < 2000 BREAK; END "John Brown" <JohnBrown(a)discussions.microsoft.com> wrote in message news:C38BA0C1-CBD0-4026-BC37-54266C7CB438(a)microsoft.com... > Helo All, > > I am deleting a large table in chunks to: > > 1) Avoid locking issues > 2) Restrict log file growth > > Algorithm > > init_size = current size of transaction log > while TRUE > delete X records matching criteria > current_size = current size of log > If no records deleted then > exit while > > if current_size - init_size > Y MB > shrink log file > end while > > I tried it with X = 200 and Y = 50. In practice, I would not try to > restrict > log growth to 50 MB. I just needed to see what was going on. > > I shrink the log file using the procedure at > http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/ > > DBCC SHRINKFILE(<TransactionLogName>, 1) > BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY > DBCC SHRINKFILE(<TransactionLogName>, 1) > > I changed the backup command to > BACKUP LOG <DatabaseName> TO DISK=(a)logfile_bak > because TRUNCATE_ONLY is not supported on SQL Server 2008. > > Some output: > > > cur_size = 48 -- MB > cur_size = 53 > Cannot shrink log file 2 (MyDB_log) because the logical log file located > at > the end of the file is in use. > > DBCC execution completed. If DBCC printed error messages, contact your > system administrator. > Processed 6083 pages for database 'MyDB', file 'MyDB_log' on file 20. > BACKUP LOG successfully processed 6083 pages in 6.530 seconds (7.276 > MB/sec). > Cannot shrink log file 2 (MyDB_log) because the logical log file located > at > the end of the file is in use. > > DBCC execution completed. If DBCC printed error messages, contact your > system administrator. > shrunk log MyDB_log > > /* As you see above, the shrink did not succeed */ > > cur_size = 56 > Cannot shrink log file 2 (MyDB_log) because the logical log file located > at > the end of the file is in use. > > DBCC execution completed. If DBCC printed error messages, contact your > system administrator. > Processed 1742 pages for database 'MyDB', file 'MyDB_log' on file 21. > BACKUP LOG successfully processed 1742 pages in 1.514 seconds (8.985 > MB/sec). > Cannot shrink log file 2 (MyDB_log) because the logical log file located > at > the end of the file is in use. > > DBCC execution completed. If DBCC printed error messages, contact your > system administrator. > shrunk log MyDB_log > cur_size = 4 > > Note that although it said that it could not shrink the log file, it *did* > shrink it, because on the next iteration, the log file size = 4 MB. I > discover the log file size by looking in sysfiles. I divide by 128 to get > MB. > > Questions: > > 1) Why did it say that it could not shrink the file, even though the file > shrunk? > > 2) In order to reduce unnecessary attempts to shrink, how can I find out > if > 'the logical log file located at the end of the file is in use' before I > try > to shrink the file? > > I am aware of the undocumented "dbcc log", but how would I access the > output > in a t-sql script? > > Regards, > Alias John Brown
From: John Brown on 11 Apr 2010 11:24 "Uri Dimant" wrote: > John > You can run DBCC Loginfo(db_id) command to look at status column. If you see > at the bottom the status =2 that means SQL Server soemhow 'needs' the > transaction and solution is to BACKUOP the log file or running dummy INSERT > to 'move' active transaction at the beginning. Right, but I am running a script. The script is supposed to monitor the growth of the log file and shrink it whenever it exceeds a threshold. I cal select from a table to find out the log file size, but I can't: select @status = top 1 a.status from (dbcc loginfo(MyDB)) a order by a.StartOffset desc > > BTW , I use the below technique to remove large portions of the data > > WHILE 1 = 1 > BEGIN > DELETE TOP(2000) > FROM Foo > WHERE <predicate>; > > IF @@ROWCOUNT < 2000 BREAK; > END > Right. I was using @@ROWCOUNT = 0, but of course, this is better, because checking for @@ROWCOUNT = 0 would cause the loop to execute 1 final, unnecessary time. Regards, Alias John Brown. > > > >
From: Uri Dimant on 12 Apr 2010 00:53 John > select @status = top 1 a.status > from (dbcc loginfo(MyDB)) a > order by a.StartOffset desc You cant run the above statement Run insert into tbl (columns put here) )exec ('dbcc loginfo (7)') but have a tbl table with the same number of columns as DBCC has "John Brown" <JohnBrown(a)discussions.microsoft.com> wrote in message news:F388F121-0E90-4F7B-97FE-1B55B6B35D49(a)microsoft.com... > > > "Uri Dimant" wrote: > >> John >> You can run DBCC Loginfo(db_id) command to look at status column. If you >> see >> at the bottom the status =2 that means SQL Server soemhow 'needs' the >> transaction and solution is to BACKUOP the log file or running dummy >> INSERT >> to 'move' active transaction at the beginning. > > > Right, but I am running a script. The script is supposed to monitor the > growth of the log file and shrink it whenever it exceeds a threshold. I > cal > select from a table to find out the log file size, but I can't: > > select @status = top 1 a.status > from (dbcc loginfo(MyDB)) a > order by a.StartOffset desc > > > >> >> BTW , I use the below technique to remove large portions of the data >> >> WHILE 1 = 1 >> BEGIN >> DELETE TOP(2000) >> FROM Foo >> WHERE <predicate>; >> >> IF @@ROWCOUNT < 2000 BREAK; >> END >> > > Right. I was using @@ROWCOUNT = 0, but of course, this is better, because > checking for @@ROWCOUNT = 0 would cause the loop to execute 1 final, > unnecessary time. > > Regards, > Alias John Brown. >> >> >> >> >
From: John Brown on 12 Apr 2010 06:20 "Uri Dimant" wrote: > John > > select @status = top 1 a.status > > from (dbcc loginfo(MyDB)) a > > order by a.StartOffset desc > > You cant run the above statement > > > Run > insert into tbl (columns put here) )exec ('dbcc loginfo (7)') but have a tbl > table with the same number of columns as DBCC has > > > That worked. Thanks. > > > >
|
Next
|
Last
Pages: 1 2 Prev: Alter minimum size of transaction log Next: Problem with Simple Create Script |