Prev: Alter minimum size of transaction log
Next: Problem with Simple Create Script
From: Uri Dimant on 12 Apr 2010 06:25
You are welcome

"John Brown" <JohnBrown(a)discussions.microsoft.com> wrote in message
news:DDD1E83B-57E8-4563-81AA-A4916346E902(a)microsoft.com...
>
>
> "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.
>>
>>
>>
>>
>


From: iNewbie on 12 Apr 2010 16:18
you should take a look at the "TRUNCATE TABLE" sentence, it uses less log
resources.

the BOLs say
"Removes all rows from a table without logging the individual row deletions.
TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause;
however, TRUNCATE TABLE is faster and uses fewer system and transaction log
resources."

:-)
"John Brown" wrote:

> 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 13 Apr 2010 14:08


"iNewbie" wrote:

> you should take a look at the "TRUNCATE TABLE" sentence, it uses less log
> resources.
>

I cannot use "truncate table" in this case. I want to delete a lot of
records, but not all of them.
First  |  Prev  | 
Pages: 1 2
Prev: Alter minimum size of transaction log
Next: Problem with Simple Create Script