 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / SQL Server Tools / July 2008SQL 2005 Shrink issue
Hi, We have a SQL 2005 DB which has a .ldf file of around 10GB...so it needs shrinking!! I've tried a shrink from the management tools but nothing actually happens. This is probably due to the fact the DB is set as Full recovery mode. Without having to actually detatch the DB to re-create the .ldf, what other methods are available to us to shrink the .ldf to a much smaller size? We'd rather not have to lose any recovery points should we need to go back to say 2 months ago. Any ideas? Thanks Have you backed up the log? If you are in full recovery mode, you should be doing regular log transaction backups. If you are not, then you may as well be in simple recovery mode, because without log backups, you are still only going to be able to recover to the last full backup in the event your hard drive goes south. Anyway, once you have backed up the log, this should clear up space in the transaction log file, and you should be able to shrink. If you still can't (please use DBCC SHRINKFILE and not the GUI), then there is probably a reason. Run DBCC OPENTRAN in a query window in the context of this database, and you might see a transaction. However, please read the following article: http://www.karaszi.com/SQLServer/info_dont_shrink.asp 10GB may be an appropriate size for your log. If you shrink now, and tomorrow it is just going to grow to 10GB again, then the shrink was a waste of time, and the autogrow event(s) will actually hamper performance in an uncontrollable way. The exception is if log growth was due to an abnormal data move, large bulk operation, massive delete, etc. that would not happen regularly. (As an aside, do you not have backups newer than two months ago???)) On 6/17/08 9:29 AM, in article BD9F875A-9BA5-4842-B554-24C0127DBB68@microsoft.com, "Chris" > Hi, > [quoted text clipped - 12 lines] > > Thanks Aaron, Thanks for the quick reply! Are you able to clarify the tranasctional log backup, log backup and any other types of backup we're able to do in SQL 2005? I think I may be getting confused over some of these. We run backups nightly, creating a new .bak each time and we can go back up to 2 months. any idea? Thanks > Have you backed up the log? If you are in full recovery mode, you should be > doing regular log transaction backups. If you are not, then you may as well [quoted text clipped - 39 lines] > > > > Thanks > Are you able to clarify the tranasctional log backup, log backup and any > other types of backup we're able to do in SQL 2005? I think I may be getting > confused over some of these. You can backup the database, which is typically just called a backup. Backing up the database gives you a file that you can restore somewhere for testing or for recovery purposes. It is basically a point in time snapshot of your data. You can also backup the transaction log in between full backups. This allows you to take that full backup snapshot, restore it, and then apply the log backups to recover up until a point in time. Without log backups, if you take a full backup every night, and then you lose your disk at 4 PM, you lose all of the changes to your database that happened after the backup. If you have transaction log backups every 15 minutes, you can have little or even no data loss. (Assuming, of course, that you put the .BAK files on a different disk than your data files reside on. If you put them in the same place, you will lose both.) Books Online can explain this in much more detail than I can possibly do in a newsgroup post. > We run backups nightly, creating a new .bak each time and we can go back up > to 2 months. In FULL recovery mode, this is not a wise backup model. FULL recovery mode is meant to allow you to recover fully to a specific point in time. However to do so you need to back up your logs periodically in order to apply them to get up to a point in time. If you don't back up your log multiple times throughout the day, and only do a full backup every night, then the log will get bigger and bigger... it is waiting for you to back it up so it can free up space. If you don't back it up to free up the space used by what should be inactive transactions, the log needs to keep those as active because you haven't backed it up. In your current mode, you have no more safety than in simple recovery mode. Whether you need to be in full recovery mode, or can switch to simple recovery mode, depends on your tolerance for data loss. In a catastrophe, if it is okay to lose all data that has changed since last night's backup, then switch to simple recovery mode (this will prevent the log from growing, because in simple recovery mode, it doesn't need to hold all of your active transactions). Otherwise, I would create a job that backs up the transaction log periodically throughout the day. A > night's backup, then switch to simple recovery mode (this will prevent the > log from growing, because in simple recovery mode, it doesn't need to hold > all of your active transactions). Otherwise, I would create a job that I believe you'd say "it doesn't need to hold all of your PASSIVE transactions" instead of "active" as passive virtual logs are deleted in every checkpoint if the recovery model is SIMPLE. Active transactions have to stay in the transaction log file as they are still active. This must be a typo.  SignatureEkrem Önsoy >> Are you able to clarify the tranasctional log backup, log backup and any >> other types of backup we're able to do in SQL 2005? I think I may be [quoted text clipped - 60 lines] > > A Ok, Success. Quite interesting how we've shrunk the log files...can you comment at all how this has worked? We just used MS SQL 2005 Mngment tool and did a FULL BACKUP on the DB...then we did a TRANSACTION LOG BACKUP. The log file shrunk a few MBs. Then we did exactly the same again, FULL BACKUP then a TRANSACTION LOG backup. The .ldf then went from about 10GB to about 70MB. How did this happen, what has actually happened here? Also, what actual data will have been removed? The .bak is about 10GB so I'm guessing the data from the .ldf is in there, but is this true...and what data has been moved? Thanks so far!! Chris > > night's backup, then switch to simple recovery mode (this will prevent the > > log from growing, because in simple recovery mode, it doesn't need to hold [quoted text clipped - 70 lines] > > > > A The log file itself should not have shrunk at all, unless you also issued some kind of shrink command (shrink database or shrink file). If it did then you either came across a new bug or didn't explain exactly what you did using the management tool. The data that has been removed from the log file are transactions that have already been committed prior to the most recent log backup. Since you backed them up in the .TRN file you don't need to keep them in the log file anymore. A On 6/17/08 11:26 AM, in article C5844784-B471-430E-802B-AEFDDBA815F8@microsoft.com, "Chris" > Ok, > [quoted text clipped - 15 lines] > > Chris When you perform a Transaction Log Backup, passive virtual logs in your transaction log file are deleted. So you can shrink your log file. Taking full backup would not truncate your transaction log file. You could also use BACKUP LOG <db_name> WITH TRUNCATE_ONLY or BACKUP LOG <db_name> WITH NO_LOG to truncate your transaction log file. Only your active virtual logs would be left in your transaction log file so when you shrink your log file, it's physical file size would decrease if there was any passive virtual logs (till the last active log) in it before you truncating it. I hope you consider what Aaron has told you about shrinking a database whether it's what you need or not and you've read the article of Tibor. Note that, WITH TRUNCATE_ONLY and NO_LOG are not going to be used in SQL Server 2008. Instead, Microsoft recommends using SIMPLE recovery model. Simply, you can change your database's recovery model to SIMPLE when you want to get rid of your passive virtual logs in your transaction log file. However, it's recommanded using FULL recovery model for most of the production environments. Again, Aaron has advised you reading this topic from Books Online where you'll find useful information about these topics and much more. SignatureEkrem Önsoy > Ok, > [quoted text clipped - 115 lines] >> > >> > A > to truncate your transaction log file. Only your active virtual logs would > be left in your transaction log file so when you shrink your log file, it's > physical file size would decrease if there was any passive virtual logs > (till the last active log) in it before you truncating it. Part of the problem I think (maybe not in this specific thread, but in general) is that people correlate "truncate" and "shrink". So it's important to have a good understanding of the distinction. One way to help is with an analogy. Truncate in terms of log file is kind of like emptying your fridge. The fridge is still there, but there is plenty of room to stock groceries. Shrink is kind of buying a smaller fridge, transferring the groceries you want to keep into the smaller fridge, and throwing the larger fridge away. Of course, come Thanksgiving time, you may regret the latter decision. (One-sentence summary of Tibor's article.) :-) A > (One-sentence summary of Tibor's article.) :-) I hope he wouldn't see this post =) SignatureEkrem Önsoy >> to truncate your transaction log file. Only your active virtual logs >> would [quoted text clipped - 19 lines] > > A True. The one-sentence synopsis in the newspaper is not the best way to enjoy a movie, either. :-) On 6/17/08 12:53 PM, in article FBFB31B9-4628-4079-AD1A-088681EA7F6C@microsoft.com, "Ekrem Önsoy" <ekrem@compecta.com> wrote: >> (One-sentence summary of Tibor's article.) :-) > > I hope he wouldn't see this post =) > Truncate in terms of log file is kind of like emptying your fridge. The > fridge is still there, but there is plenty of room to stock groceries. [quoted text clipped - 4 lines] > Of course, come Thanksgiving time, you may regret the latter decision. > (One-sentence summary of Tibor's article.) :-) Which I agree pretty much sums it up nicely. I'll keep the analogy in mind for future references... :-) SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi >> to truncate your transaction log file. Only your active virtual logs would >> be left in your transaction log file so when you shrink your log file, it's [quoted text clipped - 16 lines] > > A > Success. Quite interesting how we've shrunk the log files...can you > comment at all how this has worked? [quoted text clipped - 5 lines] > > How did this happen, what has actually happened here? Run sp_helpdb to see if you have AUTOSHRINK in force. In such case, turn it off. It's a bad option. SignatureErland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Yes, typo, sorry... I meant the transactions that have been committed... On 6/17/08 11:14 AM, in article 4A1C1BC2-F619-4A66-A0AF-39081DFCB4AA@microsoft.com, "Ekrem Önsoy" <ekrem@compecta.com> wrote: >> night's backup, then switch to simple recovery mode (this will prevent the >> log from growing, because in simple recovery mode, it doesn't need to hold [quoted text clipped - 5 lines] > to stay in the transaction log file as they are still active. This must be a > typo. I'm having a similar issue with both SQL 2000 and 2005, where if the DB's are left in a full recovery mode I can't shrink the DB's. I need to switch the DB's to simple restore mode in order to shrink them. Any thoughts? > Yes, typo, sorry... I meant the transactions that have been committed... > [quoted text clipped - 11 lines] > > to stay in the transaction log file as they are still active. This must be a > > typo. > I'm having a similar issue with both SQL 2000 and 2005, where if the > DB's are left in a full recovery mode I can't shrink the DB's. I need to > switch the DB's to simple restore mode in order to shrink them. Any > thoughts? But why would you shrink the log in the first place? Keep in mind that shrinking the transaction log is something you only would do in exceptional cases. You might have performed some unusual operation that you are not to perform for the next six months. Or the job for backing up the log has inadvertedly been disabled. If you are shrinking your logs on any kind of regular basis, you are doing something wrong. When you say that you need to switch to simple mode to shrink the log, I suspect that you are not backing up the transaction log, which you of course should do, if you want to be able to restore to a point in time. If you are content with restoring the latest backup in case of failure, switch to simple recovery for good. SignatureErland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx I believe that is my problem that I might not be backing up the logs. currently I use Backup Exec 11D to back up my SQL DB's and I use the Full back up method. The Full Method is suppose to backup the entire database, including all tables and file groups. I'm wondering if I need a 2nd job to back up the logs, I thought creating a full back up of the DB's would also backup log files and truncate them. And yes currently I have been having to perform shrinking of DB's on a regular basis. However that still doesn't explain why I would have to switch to simple mode in order to shrink the DB, what would prevent me running the shrink on the .ldf DB's in full mode? > > I'm having a similar issue with both SQL 2000 and 2005, where if the > > DB's are left in a full recovery mode I can't shrink the DB's. I need to [quoted text clipped - 17 lines] > If you are content with restoring the latest backup in case of failure, > switch to simple recovery for good. > I believe that is my problem that I might not be backing up the logs. > currently I use Backup Exec 11D to back up my SQL DB's and I use the Full > back up method. The Full Method is suppose to backup the entire database, > including all tables and file groups. I'm wondering if I need a 2nd job to > back up the logs, I thought creating a full back up of the DB's would also > backup log files and truncate them. Your recovery method is not the same as the type(s) of backup you make, though (of course) the two are related. Simple method: Logs are cut down periodically. Full method: Logs grow until you make a database backup. Your useful choices are as follows: a) Simple method, database backups only. Pro: Simpler. Con: You can only recover to the point of a database backup, and those are slow enough that you shouldn't do them during working hours (unless you're supporting a 24/7 operation, in which case you have no choice). b) Full method, database and log backups. Pro: Can recover to the point of any log backup, and those are fast enough that you can do them during working hours. Con: Recovery is more complex, and should be practiced ahead of time. > And yes currently I have been having to perform shrinking of DB's on a > regular basis. However that still doesn't explain why I would have to switch > to simple mode in order to shrink the DB, what would prevent me running the > shrink on the .ldf DB's in full mode? See above. If you use full mode but only make database backups, then the log grows between database backups for no good reason. If you make database and log backups but use simple mode, then the log is cut down whenever SQL Server feels like it and the log backups won't work. I'm running a full database backup nightly, and my recover mode is full as well in SQL. I'm using Symantec Backup exec 11D to do the backup's. What I got from Symantec today was that I need to run a FULL back up job in SBE11D then a Log backup with truncate option so it deletes the logs. I thought that If I just ran a full back up Job it would truncate the logs as well. It sounds like I should be using simple restore in SQL since I only back up nightly, but my questions is why isn't the log files truncating since I'm running a full database back up nightly? > > I believe that is my problem that I might not be backing up the logs. > > currently I use Backup Exec 11D to back up my SQL DB's and I use the Full [quoted text clipped - 31 lines] > database and log backups but use simple mode, then the log is cut down > whenever SQL Server feels like it and the log backups won't work. > but my questions is why isn't the log files truncating since I'm > running a full database back up nightly? because that's the way it works. you should take transaction log backups for that purpose. SignatureEkrem Önsoy > I'm running a full database backup nightly, and my recover mode is full as > well in SQL. I'm using Symantec Backup exec 11D to do the backup's. What I [quoted text clipped - 48 lines] >> database and log backups but use simple mode, then the log is cut down >> whenever SQL Server feels like it and the log backups won't work. > I'm running a full database backup nightly, and my recover mode is full > as well in SQL. I'm using Symantec Backup exec 11D to do the backup's. [quoted text clipped - 3 lines] > logs as well. It sounds like I should be using simple restore in SQL > since I only back up nightly, Most of all, you should use simple backup, if you are content with recovering from the nightly backup. Note that if you first truncate the log, and then backup your database, you can still recover from failures on the MDF files during the day, as well as from human errors. If you first back up the database, and then truncate the log, you have broken the log chain, and whatever you assemble in the transaction log is useless. > but my questions is why isn't the log > files truncating since I'm running a full database back up nightly? Because you don't backup the transaction log. Backing up the database backs up the database, nothing more. And, again and again, truncating the log is a very unusual operation, as is shrinking a database file. SignatureErland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Check out this scenario to see what happens in case you perform only full backup: 1- You take your database's full backup on 12:00 am 2- You take your database' s log backup every hour Of course this scenario subject to change according to the workload and needs of different environments. However, in this scenario if your database corrupts (because of a power cut or a hardware problem) you'd lose only 1 hour of data at most. To restore your database to its most recent point, you'd first restore the full backup and then the log backups one after another. So let's say your database becomes unusable at 12:15 pm. Then you'd be able to restore it till 12:00 pm and you'd be lost only 15 minutes data. If you take only one full backup once in a day and let's say you take it at 12:00 am then according to the above scenario you'd lose all data that's inserted, modified, deleted from 12:01 am to 12:15 pm. This is why it's important to take log backups for production databases. It's safer, it's more practical and it's faster to restore in case of a point of failure. Regarding to your problem with shrinking in FULL Database Recovery Model. What makes you think that you can't shrink a database when it's in Full Recovery Model? Of course you can shrink your database and log file when your database in FULL Recovery Model. If you can't shrink your log file, then most probably you didn't empty it. To empty a transaction log, you can change your Recovery Model to SIMPLE. Or better, take your transaction log file's backup. And this is what should be done in most of production environments. To empty your transaction log file, you should take it's backup. A Full database backup wouldn't empty it. You should explicitly take your log file's backup. SignatureEkrem Önsoy >I believe that is my problem that I might not be backing up the logs. > currently I use Backup Exec 11D to back up my SQL DB's and I use the Full [quoted text clipped - 33 lines] >> If you are content with restoring the latest backup in case of failure, >> switch to simple recovery for good. FYI: Also, you can't take a transaction log backup when your database in SIMPLE Recovery Model. SignatureEkrem Önsoy >I believe that is my problem that I might not be backing up the logs. > currently I use Backup Exec 11D to back up my SQL DB's and I use the Full [quoted text clipped - 33 lines] >> If you are content with restoring the latest backup in case of failure, >> switch to simple recovery for good. > I believe that is my problem that I might not be backing up the logs. > currently I use Backup Exec 11D to back up my SQL DB's and I use the > Full back up method. The Full Method is suppose to backup the entire > database, including all tables and file groups. I'm wondering if I need > a 2nd job to back up the logs, I thought creating a full back up of the > DB's would also backup log files and truncate them. I'm not acquainted with Backup Exec 11D, so I cannot answer deatils on that product, you may have to consult the vendor. But in any case, first you need to determine what level of recovery do you need? If the database goes belly-up because of hardware or human failure can you afford to lose one day of work and restore the most recent backup? If that backup is bad, can you back one more day? Or do you need to be able to restore as close as possible to the failure as possible? Once you have determined that, you need to test that you actually have somehing working. That is simulate a failure, and try to restore. (A simple variant is to restore on a different server.) Once you have restored, you try run to access the database, and also run DBCC on it to make sure that's fine. This is necessary, so that you know that if disaster strikes that 1) you have all the components you need. 2) you know how to perform the steps. (Keep in mind that when this happens, you may have a bunch of people screaming about the missing database, so that will be a stressful situation.) > And yes currently I have been having to perform shrinking of DB's on a > regular basis. However that still doesn't explain why I would have to > switch to simple mode in order to shrink the DB, what would prevent me > running the shrink on the .ldf DB's in full mode? The most likely reason is that you have not backed up the transaction log. SignatureErland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.