July 23, 2014 at 1:08 am
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.
July 23, 2014 at 3:45 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 23, 2014 at 5:34 am
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.
July 23, 2014 at 1:25 pm
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.
July 23, 2014 at 1:30 pm
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[/url]
Learn Extended Events
July 23, 2014 at 1:43 pm
Thanks Jason - why won't Shrinkfile help? How often is "regular" transaction log backups?
July 23, 2014 at 2:01 pm
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[/url]
Learn Extended Events
July 23, 2014 at 2:27 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
July 23, 2014 at 8:01 pm
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
July 24, 2014 at 6:47 am
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[/url]
Learn Extended Events
July 24, 2014 at 9:07 am
>> and restricted the log size to 30mb <<
That seems a little low, given that you might have a situation where one transaction takes a while to complete. Would another 10-15mb really crimp the data size that much :-)?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 26, 2014 at 2:30 pm
dupe post
July 26, 2014 at 2:34 pm
Looks like limiting the transaction log size to 30mb is not the answer:
Message = "The transaction log for database 'Customers' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases"
UPDATE: I asked the hosting company to remove the size limit on the transaction log. I'm hoping that because the recovery model is Simple, the log won't grow too much even if it's infrequently backed up by the hosting company.
Thanks,
Matt
July 27, 2014 at 11:18 am
matt6749 (7/26/2014)
Looks like limiting the transaction log size to 30mb is not the answer:Message = "The transaction log for database 'Customers' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases"
UPDATE: I asked the hosting company to remove the size limit on the transaction log. I'm hoping that because the recovery model is Simple, the log won't grow too much even if it's infrequently backed up by the hosting company.
Thanks,
Matt
FYI, they won't be able to do tlog backups in simple recovery mode. Keep an eye on the database and see what the largest transaction seems to be and grow the log to that size.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 28, 2014 at 11:39 pm
One followup. (Hope I'm not annoying everybody):
With the Simple recovery model I'm using, would BEGIN TRANS and COMMIT statements help free up space in the log when used as follows:
instead of using this:
delete from x ...
update x ...
insert into x ...
use something like this below (that way the log space might be reused) ?
BEGIN TRAN
delete from x ...
COMMIT
BEGIN TRAN
update x ...
COMMIT
BEGIN TRAN
insert into x ...
COMMIT
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply