July 2, 2005 at 7:28 am
Hi!
I'm the dbo for a small, non-critical, and low volume SQL Server 2000 database that is hosted on a shared server. The hosting company's policy is that they don't use the SQL Server backup procedures, they simply once a day back up the whole physical disk, which includes the web space as well -- everything. In the event of a problem they say they will simply restore to the latest backup and I am on my own after that. I can live with losing a day's worth of data. I have two questions:
1. Is there something terribly wrong with the hosting company's policy that I am not considering?
2. Since my db space is limited, I want to keep the Transaction Log as small as possible since it has hardly any usefulness to me in the event of a disk failure. I set the maximum size to the minimum setting of 1 MB, and told it that it couldn't grow beyond that. Since the volume is very low it is hard to imagine the log ever needing very much space. Still, I got a log full error message. What settings should I use (remember I am only the dbo and not the sa) to make sure that I don't get any more of such errors?
Thanks for helping a newbie!
BR Das
July 4, 2005 at 8:02 am
1. IMHO (without knowing further details) by doing this they might ask for a trouble in the event of a failure, because file system backups of sql server databases should generally be avoided unless it is possible to shutdown server and copy the closed database files. Without this you either can't backup sql server data files (sql server has exclusive access and won't let you copy them) or you can use a 3rd party utility that can copy the files but I would be wary whether it can restore database to a consistent state. I don't know how much you are responsible for the data but in any case you should ask them to give you more info on their backup procedures and whether they perform regular restores on test servers - that should clear up the matters.
2. 1 MB is too low. From what you say I would start with something like 10-50 MB. Also make sure that your db is in simple recovery model if you are satisfied with going back to the last full backup in the event of a disaster.
hope this helps
July 4, 2005 at 8:47 am
Sorry to be blunt and I know you are new but this 'policy' is REALLY DUMB. The data files won't back up unless the SQL Server is shut down, even then there's a risk of inconsistencies and I wouldn't want to restore the Server from tape - I have done it once but I was there half the night.
SQL Server backups are as good as they are simple. Your company should be backing up their DB once a day and the system DBs at least once a month. Get more disk if necessary - it's cheap enough.
Also, 1 MB is was too small for the transaction log even if the db is set to Simple recovery (I assume the db is set to simple otherwiase your transactiuon log will have taken over the disk).
July 4, 2005 at 11:24 am
Post the name of the hosting company here. I want to know the name so I never do business with them. After you're done posting, point them to this site. Ask them how much business they want to lose because of a policy that makes no sense. They are endangering ALL of their customers, not just you.
To be blunt, the are IDIOTS!! If they don't understand how SQL Server works, they shouldn't be offering it as a hosting company. It's probably a bunch of Linux mavericks who just assume the can manage SQL Server because it's "easy".
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.
July 5, 2005 at 5:23 am
Thanks to all for the help. I was afraid it would be something like that! I'll forward your comments to the vendor and see what happens. I may have to switch vendors.
But regarding the Transaction Log size, if I am understanding correctly, even with me using Simple recovery scheme, the Transaction Log is going to keep growing because there are never any backups being done. So no matter what size I set as the maximum, at some point it is going to get full and complain. Now, I can keep the size under control by manually running "Backup Log xxx with truncate_only" regularly. Is there any way that a dbo can tweak the settings so that he doesn't have to remember to manually truncate?
Sorry to have to ask such pedestrian questions. But I have to live in the hole that I have dug for myself, at least until I can crawl out.
July 5, 2005 at 7:03 am
First, I'm appalled that a hosting company would follow such practices. Please take the advice of the other posters and find a new host.
Second, being simple enough to do, using the backup with truncate only can be used in a job step and scheduled to run as often as you'd like.
July 6, 2005 at 2:20 am
Under simple recovery model, you don't need to worry about transaction log growth much because it gets truncated automatically at every checkpoint. Thus only large transaction can cause your transaction log to grow (physical file). So no need to truncate log manually.
July 6, 2005 at 9:29 am
You've all been great. Thanks very much!
BR Das
July 6, 2005 at 2:07 pm
Limiting the transaction file growth will only cause the job that needs it to grow to fail. You need to let it auto grow and manage your transactions closely
Though it is true that having the database truncated automatically at checkpoint will give you back available space, it does not shrink the log file itself back.
If you run big jobs that cause extensive log file growth you might want to manually shrink the log file after the job is completed.
DBCC Shrinkfile ('The log filename here',10)
Cheers,
John Zacharkan
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply