| Author |
Topic |
|
mav13
Starting Member
4 Posts |
 Posted - 2005-12-31 : 01:35:59
|
| I have large DB which has been configured with log shipping. On a weekly basis, whenever I do a full backup of the same, I do a Shrink Log before doing so. I find that the Full Backup job always fails when it coincides with the TLog Backup job of Log Shipping. After altering the schedule, I find that the job succeeds. But the logs show no exact error message in the former cases.Could the failure be because of the TLog Backup? Or could there be any other reason? |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-31 : 10:58:49
|
| I'm not keen on Shrinking databases (except after an abnormal, and massive, delete), it just wastes SQL Servers resources reacquiring the space it needs to re-expand the logs. Is there some reason why you need to do a regular shrink? Is the file bigger at the end of week 2, than at the end of week 1, if you don't do the shrink?Kristen |
 |
|
|
mav13
Starting Member
4 Posts |
Posted - 2006-01-02 : 23:13:09
|
| There are millions of transactions run against the DB on a daily basis resulting in the LOG growing quite extensively. That is why I am doing the shrink. To make my question more specific. Will Shrink Log fail due to a concurrently running Log Backup, if so what would be the error that would be raised?Prashant |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-03 : 08:07:49
|
| "There are millions of transactions run against the DB on a daily basis resulting in the LOG growing quite extensively"... and there will be millions more tomorrow, and those new ones will reuse the log file space. Whereas Shrinking the log file will cause it to need to be EXTEND as new transactions arrive which will take more CPU effort, reduce the availability of the system during the extension (which may lead to errors) and increase the fragmentation of the file.Or am I missing something?Kristen |
|
|
|
mav13
Starting Member
4 Posts |
Posted - 2006-01-04 : 00:47:05
|
| My question still remains -"Will Shrink Log fail due to a concurrently running Log Backup, if so what would be the error that would be raised?"Prashant |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-01-04 : 03:49:16
|
| That's something that you should be able to determine yourself. However Kristen's point is that you are wasting your time doing log file shrinking at all.-------Moo. :) |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2006-01-04 : 04:35:31
|
| FWIW, there is really no point in shrinking a database file (both data and log) just because of the shrinking. Remember, that the file has to grow again. And this is an expensive operation in SQL Server 2000, as SQL Server has to lock the structure of the database while physically expanding the file. It will put a high overhead on the disk subsystem and might cause more or less significant I/O bottlenecks. Since your system seems to be fairly busy anyway, this will contribute to contention and performance degradation.--Frank KalisMicrosoft SQL Server MVPhttp://www.insidesql.deHeute schon gebloggt? http://www.insidesql.de/blogs |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-04 : 07:53:35
|
Frank,Just as a point of possible interest: we had a situation where all our web sites suffered significantly due to databases expansion.We used to have dramatic application slowdown for a period of about 20 minutes at unpredictable times. With the help of folk here we tied it down to database expansion. It was further not helped because our DBs were set to the default 10% expansion - our DBs are around 500MB to 5GB, so we had anything from 50MB to 500MB file extensions being initialised.This increased the query time, which meant more queries were arriving than answers being given [i.e. compared to normal running], this slow down in turn caused users to press "retry", and then, eventually, users started seeing "timeout" errors and kept on pressing retry (or worse, giving up and giving their business to another web site  )We set a more modest, fixed, file expansion size and the problem has gone away - but I wouldn't dream of shrinking a database (other than after some exceptional activity - such as a massive deletion, or some run-away query that had grown the logs immensely).I have on my ToDo list to find out when the database is below some reasonable threshold (e.g. half the expansion likely in an average week) and trigger a file expansion, so that we can do this during the "quiet hours" so that expansion should never happen during the busy hours, but I didn't find an easy way to test for the "getting full" state, etc., so I left it for a rainier day!Kristen |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2006-01-04 : 08:11:01
|
Kristen, in case you don't know it already, SQL Server 2005 can make use of the NTFS sparse file technology which allows almost instantaneously file expansion. At least that's what I've heard.  --Frank KalisMicrosoft SQL Server MVPhttp://www.insidesql.deHeute schon gebloggt? http://www.insidesql.de/blogs |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-04 : 08:53:31
|
| "SQL Server 2005"Ah ... that's defined here as "something to look forward to" - when a majority of clients budget's for the upgrade, and testing, and probably additional hardware that they will decide to include in the upgrade, go through the Red Tape mill !!!Thanks for the info, I've stashed it for a test once we've got the budget for the opportunity!Kristen |
|
|
|
|
Question: Shrink Logs/Backup