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 (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 216
Hi,
Thanks to this forum. It's great.
I have a SQL DB on a shared hosting service (NewTek/The SBA) with 400Mb disk space allowed.

The DB size is 100Mb, but each week when I run a job to delete and insert ~10,000 records, the transaction log grows to 300-400Mb, which makes my DB exceed the allowed disk space.

I believe that the log file can grow much larger than the data file when insufficient backups are done, but it is a shared host and I can unlikely change that.

Do I run DBCC SHRINKFILE on the log each week and shrink the log to a certain size? If so, what size? Or set the recovery model to simple (which I hear is risky if you need to do a restore)?

Thanks in advance.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93999 Visits: 33010
First off, are you taking regular log backups? If you're not taking those, then you can't do a restore to a point in time anyway. Determine if you need to restore to a point in time. If you do, you need to have log backups running. That will help reduce the size of the log. But, you may also need to limit the transaction size which would mean doing your deletes in smaller chunks. If you don't need to restore to a point in time, then you can go to simple recovery. You may still need to limit the transaction size though.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35821 Visits: 9261
Grant forgot to mention that you can't take transaction backups in Simple recovery mode. So verify your recovery mode first. If it's already in Simple (EDIT: given your original post it might not be), you might be SOL because the trans log actually requires the amount of space it keeps ballooning to.

But if it's Full or Bulk-Logged, definitely check to see if you are taking transaction log backups. If you are, then again, the database might actually require the amount of space it keeps growing to and to try shrinking on a weekly basis is only going to choke the DB and cause other pain. Though limiting the transaction size will help some.

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.
matt6749
matt6749
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 216
Thanks for the replies. As far as regular log backups, restore points, etc... It is a shared hosting DB ($10/month), pretty much locked down. I don't have rights or access to do any of that stuff. The hosting company manages all that and I doubt I can change it.
What I do know is that the DB is in FULL recovery mode, and I can run DBCC SHRINKFILE. I don't care about the transaction log, rollbacks, restores, etc. - it is not mission critical data. So what can I do as a solution? Run SHRINKFILE only on the log file? Would that be so bad? Set a certain size (e.g. 50mb) to shrink to?
Thanks.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63807 Visits: 18570
Shrinkfile won't help unless you are getting regular tlog backups.

Verify with them that they are performing those tlog backups.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

matt6749
matt6749
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 216
Thanks Jason - why won't Shrinkfile help? How often is "regular" transaction log backups?
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63807 Visits: 18570
Regular depends on your transaction volume.

Shrinkfile only works if the log file is empty. If you are not backing up the transaction log, then the file will not be empty. You can't shrink a full file.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

ScottPletcher
ScottPletcher
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18658 Visits: 7402
You simply must get some answers to accurately resolve this:

1) What is the recovery model for your database? Bulk-logged, Full or Simple?

2) If not simple, at what time(s) during the day are log files backed up?

3) What is the specific growth setting on your log file? 10%, 1MB, 20MB, what?

4) What is the specific growth setting on your data file? 10%, 1MB, 20MB, what?

5) Do they ever shrink the log file? (Not truncate, shrink.)

6) Do they rebuild indexes? If so, how often? And do they use SORT_IN_TEMPDB = OFF or ON when they do? [If it's OFF, are they willing to change it to use ON when do your db maintenance? This is highly technical, but it can make a huge difference in how much extra your db will grow during a rebuild.]

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.
matt6749
matt6749
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 216
Thank you all for your valuable help.

The hosting company has done this (hope it's ok):
They said they do nightly backups. They set my recovery mode to Simple and restricted the log size to 30mb. 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. ?

Thanks,
Matt
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63807 Visits: 18570
matt6749 (7/23/2014)
Thank you all for your valuable help.

The hosting company has done this (hope it's ok):
They said they do nightly backups. They set my recovery mode to Simple and restricted the log size to 30mb. 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. ?

Thanks,
Matt



Good. Glad to hear they got it straightened out.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

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