Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DB transaction log file on a shared hosting service


DB transaction log file on a shared hosting service

Author
Message
matt6749
matt6749
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 216
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...
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
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

Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36107 Visits: 18741
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: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7779 Visits: 8736
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.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3944 Visits: 6676
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)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search