SQL Clone
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 (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 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-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32332 Visits: 18552
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 Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62562 Visits: 19109
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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14589 Visits: 8996
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
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7895 Visits: 7155
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)[size=2]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.[/size]
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