Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

DB transaction log file on a shared hosting service Expand / Collapse
Author
Message
Posted Wednesday, July 30, 2014 1:49 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:44 PM
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...
Post #1597942
Posted Wednesday, July 30, 2014 1:52 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 18,064, Visits: 16,099
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
Post #1597944
Posted Wednesday, July 30, 2014 1:58 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 1:28 PM
Points: 31,368, Visits: 15,836
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
Post #1597951
Posted Thursday, July 31, 2014 7:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 5,676, Visits: 6,488
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1598244
Posted Thursday, July 31, 2014 8:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:26 PM
Points: 2,330, Visits: 3,509
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1598283
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse