July 29, 2014 at 5:00 am
So far as I know, explicit transactions vs implicit transactions will not affect how the transaction log grows. Here are a few links to read up on.
July 29, 2014 at 5:03 am
matt6749 (7/23/2014)
I think it's ok because the data is not mission critical and I back it up myself also with a console app I wrote. ?
Matt,
How is your console app actually backing up the transaction log? Is it just making a copy of the .ldf file?
In Simple recovery mode, transaction logs cannot be backed up. The fact that you think you are backing it up worries me that you might be misunderstanding how backups work and might be depending too much on a file that will not be readable if you should happen to need it.
July 29, 2014 at 9:10 am
matt6749 (7/28/2014)
One followup. (Hope I'm not annoying everybody):With the Simple recovery model I'm using, would BEGIN TRANS and COMMIT statements help free up space in the log when used as follows:
instead of using this:
delete from x ...
update x ...
insert into x ...
use something like this below (that way the log space might be reused) ?
BEGIN TRAN
delete from x ...
COMMIT
BEGIN TRAN
update x ...
COMMIT
BEGIN TRAN
insert into x ...
COMMIT
That might (should?) allow the log file to be truncated (freed up for reuse) earlier, particularly if the delete, update and inserts are large-volume transactions.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
July 30, 2014 at 5:30 am
This was removed by the editor as SPAM
July 30, 2014 at 5:40 am
samanta0rodriges (7/30/2014)
why won't Shrinkfile help?
If you are not regularly backing up the transaction logs, the log won't free up any space. It will hold onto all the space it has (and can get) and keep the transactions in there. Therefore, a shrinkfile operation will look just look at the log and say "no free space to shrink" and finish without actually doing anything.
samanta0rodriges (7/30/2014)
How often is "regular" transaction log backups?
That depends on your system setup. Is it an OLTP or a data warehouse? How often are transactions getting pushed into the database? How much data can be lost if the system goes down?
Depending on those factors, a "regular" transaction log backup can be anything between once a day to once an hour to once every 15 minutes. At my workplace, we have some DBs that have a once an hour log backup during business hours only, some DBs (Simple mode) that have no log backups at all, and some DBs that have a log backup once every 4 hours during the 24 hour day. It just depends on what the databases are doing, how often updates happen, their recovery mode, etc.
July 30, 2014 at 1:49 pm
Well, after a crash course in log files, thanks to everyone here, here's what I believe I have to do:
My hosting company limits my combined db + log size to 400mb.
Db is in Simple recovery mode.
The log still grew to over 400mb with the last run of my transactions. Using explicit transactions (BEGIN TRANS..COMMIT) did not help this.
So I've concluded that I HAVE to shrink the log (unfortunately) after I run these transactions (weekly).
After shrinking I will rebuild the indexes to help reduce the fragmentation.
I can't see any other way to handle this.
Sigh...
July 30, 2014 at 1:52 pm
matt6749 (7/30/2014)
Well, after a crash course in log files, thanks to everyone here, here's what I believe I have to do:My hosting company limits my combined db + log size to 400mb.
Db is in Simple recovery mode.
The log still grew to over 400mb with the last run of my transactions. Using explicit transactions (BEGIN TRANS..COMMIT) did not help this.
So I've concluded that I HAVE to shrink the log (unfortunately) after I run these transactions (weekly).
After shrinking I will rebuild the indexes to help reduce the fragmentation.
I can't see any other way to handle this.
Sigh...
Is there any way you can decrease the size of your transactions? That's pretty restrictive sizing from the hosting company.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 30, 2014 at 1:58 pm
Are backups included in the size of your data+log? If not, I'd try to reduce transactions, as Jason mentioned, and have frequent backups.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 31, 2014 at 7:01 am
matt6749 (7/30/2014)
After shrinking I will rebuild the indexes to help reduce the fragmentation.
I have some pretty bad news for you. Rebuilding the indexes will reduce fragmentation, but will increase the size of your log file again and possibly increase the size of your data file (if that has been shrunk smaller than the database wants it to be).
It sounds to me that your hosting company is restricting your size based on how much money for space your company is paying. Chances are you'll have to get your boss and the finance guy involved in this to bargain with the hosting company for more space. And if the hosting company is restricting size on other factors (not based on money) that's a whole 'nother ball of wax.
July 31, 2014 at 8:03 am
Shrinking the log file has absolutely no affect on the data indexes and their fragmentation. You would only need to check on index rebuilds if you shrink the data portion of the database. And with only ~100MB of data, shrinking it is basically a waste of time.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply