November 20, 2013 at 11:13 am
Hello,
As far as I know, SQL Server 2008R2 does not prepare the log to be inmediately shrinked after a log backup, because there must be available space at the end of the log before truncating, and thats why I'm getting this warning:
-- 9008: Cannot shrink log file 2 (pdvtest_lo) because the logical log file located at the end of the file is in use.
I have no connections nor transactions opened (re-re-re checked hundred times)
So, in order to achieve this I plan to do this sequence of orders (at a daily basis, at 3;00 AM):
-- the following two backup files will be stored in different directories for each day of the week...
BACKUP DATABASE PDVTEST TO DISK=N'S:\PDV\pdvtest.bak' WITH FORMAT
BACKUP LOG [PDVTEST] TO DISK = N'S:\PDV\pdvtestlog.bak' WITH FORMAT
ALTER DATABASE [PDVTEST] SET RECOVERY SIMPLE - don't close your eyes... 8-D
DBCC SHRINKFILE (N'pdvtest_log' , 0);
ALTER DATABASE G001 SET RECOVERY FULL -- back to normal
This way I got my log in case I need to restore to a specific time, and the log is 1k sized. I know I'm 'breaking' the log chain, but as far as I have a db backup and log backup for each day, who cares ?!
I've tried countless times and other options with no luck (in fact if I repeat n times the backup of db and log, the log also gets 1kb sized, but I don't control neither why nor when).
I think Microsoft should change this to a more easy way to proceed:w00t:, as long as there are hundreds and thousands and millions of people struggling with this in countless forum threads... Don't you agree ?
Thanks in advance,
Roger
November 20, 2013 at 11:38 am
Hoooboy ....
Why do you feel the need to shrink the log file ?
What if you have a disaster at 11 am, and need to restore your database to 10:59 am ? You will lose everything after 3 am.
If you only backup once per day, you cannot do point in time, so you might as well leave it in simple.
Ed Wagner just suggested this T-Log reading material in another thread
November 20, 2013 at 12:45 pm
Hello,
Right now we don't have time to buy and install new disks for the server so we must keep the size for the log controlled.
I would prefer a way to do it that, when the 'magic' series of commands are performed, the log file turns to be 1Mb only (minimal size set at the start).
November 20, 2013 at 12:50 pm
Roger Tranchez (11/20/2013)
This way I got my log in case I need to restore to a specific time, and the log is 1k sized. I know I'm 'breaking' the log chain, but as far as I have a db backup and log backup for each day, who cares ?!
Who cares? Yeah, heard that before. Also heard the 'We're OK because we have a full backup and log backups for each day", sometimes followed by 'What do you mean the backup won't restore and we're going to lose an entire day of data? You said we're OK!!!'
If you really do mean one log backup a day, then that leaves you exposed to up to 24 hours of data loss in the case of a disaster. What does your SLA allow? If it really does allow 24 hours of data loss, switch the DB to simple recovery model and leave it there. With this procedure you're getting none of the advantages of full recovery and all of the disadvantages.
I think Microsoft should change this to a more easy way to proceed:w00t:, as long as there are hundreds and thousands and millions of people struggling with this in countless forum threads... Don't you agree ?
No, but I do think that DBAs should understand how to maintain the log so that they don't struggle with this kind of problem. Btw, the 'easy way to proceed' has existed in the product for many, many, many versions. It's simple recovery model.
http://www.sqlservercentral.com/articles/Administration/64582/
http://www.sqlservercentral.com/stairway/73776/
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
November 20, 2013 at 12:50 pm
How frequent are your transaction log backups ? The more frequent, the less it will grow.
November 20, 2013 at 12:53 pm
Roger Tranchez (11/20/2013)
I would prefer a way to do it that,
ALTER DATABASE <database name> SET RECOVERY SIMPLE
when the 'magic' series of commands are performed, the log file turns to be 1Mb only (minimal size set at the start).
Well, yes. You're running DBCC ShrinkFile with a target size of 0 MB, so SQL is doing exactly as you ask and shrinking the log as close to 0 as possible, that is 1MB.
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
November 20, 2013 at 2:03 pm
If it will grow everyday anyway, why would you want to shrink it? Do you dislike good performance?
As I understand, your log backup is not there for disaster recovery but just to get point in time restore for previous days as an alternative to the whole data backup. It seems kind of pointless.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply