June 23, 2009 at 3:02 pm
No.
Leave them the size they are now. Monitor using DBCC SQLPERF(LogSpace) and you'll be able to see file size and space used. Keep an eye on that and see how much space the log uses. Maybe check that before each log backup as that'll give you the high marks for log usage. Measure that for a few days and you should have a good idea how big the log really needs to be. Then you can shrink it if necessary.
The interval between log backups should first be determined by how much data you're willing to lose in the case of a disaster. If say 2 hours of data loss is the most that's acceptable then the log backups should start at 2 hour intervals. If you decide that, at 2 hour intervals, the logs are getting too big, then reduce the interval.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 23, 2009 at 3:10 pm
tosscrosby (6/23/2009)
will (6/23/2009)
ah ok...we are getting there, thanks for all the help gail and others. its much appreciated.
so in order for me to 'start again' i should back up all files, truncate then shink logs, then let them grow backing them up every 4-6 hours?
That depends on your user's tolerance for data loss. If they can only afford one hour of data loss, then backup the log, at minimum, every hour. This, of course, is in addition to you regular full backups, and possibly differentials.
ok thanks both...
one very last thing; when i delete 1000's of rows in a db, the file size does not get smaller of the BD. is this the same reason as the log files?
what do you do to maintaine a smaller footprint of your main database files? or simply put what do you do every so often to reorganize the space used by your mdf files?
June 23, 2009 at 3:19 pm
will (6/23/2009)
one very last thing; when i delete 1000's of rows in a db, the file size does not get smaller of the BD. is this the same reason as the log files?
Yup.
what do you do to maintaine a smaller footprint of your main database files?
Why do anything? Databases tend to grow and SQL needs some free space within the DB file for normal operation. The only time I'd shrink a DB is when there's been a large deletion or large archiving of old data and there's so much free space that I don't expect it to be used up within a couple months
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 23, 2009 at 3:26 pm
ok, well i am thinking of moving to a shared hosting enviroment for some dbs. with this it costs per mb of SQL space. so i idealy want the smallest footprint.
Its not going to make a huge difference, but i havnt shrunk a db file ever, and every now and then i clear out old stat records for say news article views which may have many 100'000's of rows. with this in mind i obviously want to start with the smallest footprint possible.
June 23, 2009 at 3:32 pm
Just make sure that you rebuild your indexes afterwards.
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply