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 23, 2014 1:08 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, December 19, 2014 7:44 PM
Points: 102, 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.
Post #1595359
Posted Wednesday, July 23, 2014 3:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:26 AM
Points: 14,205, Visits: 28,536
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
Post #1595396
Posted Wednesday, July 23, 2014 5:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:46 AM
Points: 5,676, Visits: 6,488
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 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 #1595430
Posted Wednesday, July 23, 2014 1:25 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, December 19, 2014 7:44 PM
Points: 102, 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.
Post #1595686
Posted Wednesday, July 23, 2014 1:30 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 18,064, Visits: 16,108
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
Post #1595688
Posted Wednesday, July 23, 2014 1:43 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, December 19, 2014 7:44 PM
Points: 102, Visits: 216
Thanks Jason - why won't Shrinkfile help? How often is "regular" transaction log backups?
Post #1595695
Posted Wednesday, July 23, 2014 2:01 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 18,064, Visits: 16,108
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
Post #1595702
Posted Wednesday, July 23, 2014 2:27 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:06 PM
Points: 2,340, Visits: 3,515
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1595712
Posted Wednesday, July 23, 2014 8:01 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, December 19, 2014 7:44 PM
Points: 102, 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

Post #1595776
Posted Thursday, July 24, 2014 6:47 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 18,064, Visits: 16,108
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
Post #1595878
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse