| Author |
Topic |
|
cjmildenberg
Starting Member
7 Posts |
 Posted - 2010-05-17 : 15:43:51
|
| Hello,Please forgive my ignorance, as I am a general PC technician trying to help out a company running their shop software on SQL 2005 Express. Their SQL logfiles grow large, very quickly. Previously, I have been able to run a backup on the database, and then release unused space and truncate, with success.Today, I followed the same procedure, but using the shrink action - 'release unused space' has no effect on the allocated space for the logfile. Even though available free space is 98%. So I have a 12GB logfile that is 98% unused. So the file no longer grows bigger, but the available free space keeps shrinking. Am I doing something wrong? Am I better suited to detach the database and then run this function? This is a production server, and I hate taking chances without knowing the consequences.The software company will NOT help me with SQL managment, but have provided me with the following script that they recommend running.USE XXXXX BACKUP LOG XXXXX WITH TRUNCATE_ONLY DBCC SHRINKFILE (XXXXX_LOG,2)Is that fundamentally different than anything I've done so far? Any harm in running that on top of what I have already done?Again, I apologize for my ignorance, and sincerely appreciate any help provided.Thank you,CJM |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-05-17 : 15:46:09
|
| What's the database recovery model for this database? To see it, right click on the database in SSMS, go to properties, and then to Options. If it's FULL or BULK_LOGGED, then are regular transaction log backups happening on that database?I suspect that FULL recovery model is being used and then NO tlog backups are happening. This is not good. We backup our tlogs every 15 minutes.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-17 : 15:51:04
|
| Shrinking is fundamentally wrong. It causes fragmentation of the file (i.e. repeated Shrink / Expand cycles) which will significantly impact on performance.Shrink should only ever be used as a one-time action after exceptional circumstances - exceptional delete of old data; accidental unnatural growth of log file (e.g. due to abnormal bulk import)For the rest:1) Either set the database to SIMPLE Recovery model (if recovery to a FULL backup is sufficient and there is NO requirement for recovery to point-in-time)2) Use FULL Recovery Model, which allows recovery to point-in-time, and schedule frequent Transaction Log Backups (15 minute intervals is usually the right balance between lots of file [which hampers restore] and acceptable data loss in disaster planning [but "acceptable" is for each organisation to decide, of course] |
|
|
|
cjmildenberg
Starting Member
7 Posts |
Posted - 2010-05-17 : 15:58:53
|
| Thank you for the quick help.It is set by default to Full, and regular log backups are NOT happening. They have no problem going to Simple as long as the most recent backup can be recovered. Going back to a certain date in time would not be necessary.Conversely, if I begin to do regular backups of the logfile, will the 12GB file begin to shrink? I guess what I'm getting at, is, can I shrink the file from where it is if I don't switch to 'Simple?'I was under the impression that SQL Express did not have the ability to schedule backups as a limitation (is this correct?)Thanks!CJM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
cjmildenberg
Starting Member
7 Posts |
Posted - 2010-05-17 : 16:21:55
|
| Thank you for your expertise in this area. When you said SINGLE recovery model (in the above post) I am assuming you meant SIMPLE? Is there any harm in switching recovery model on the fly, on a mounted database? If I go that direction, will I then need to manually shrink by selecting "release unused space?"Currently, they are running a daily full backup with a 10 day history. I would consider this enough of a cushion for database corruption. Or are there more compelling reasons to stick with Full recovery model?CJM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-05-17 : 16:27:20
|
| Oops! Yes SIMPLE. I'm working on a server named SINGLE at the moment, haha!There is no harm on switching the recovery model on the fly. Whichever direction you go, you'll need to shrink the database. I use the second option where you specify a size. Shrink it down to 25% of the MDF file for starters. We can only afford 15 minutes of data loss, so reverting to the last full backup would be unacceptable. Tlog backups are so that you can recover to a point in time such as 15 minutes ago even if the full backup was several hours/days ago. Sticking with FULL recovery model is dependent on how much data loss can be afforded. I use FULL recovery on 99% of my production systems and have tlog backups occuring every 15 minutes on those.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
cjmildenberg
Starting Member
7 Posts |
Posted - 2010-05-17 : 16:44:48
|
| How many Tlogs do you then keep? Months worth?If I DO stay with Full, you had previously indicated that I may be stuck with this 12GB logfile, even tough it is 98% empty space. If this is the case, I will need to move the database to another drive, as the drive it is on is dramatically low on space. Is there any way to manually crunch it down, and begin a full log from today that I could properly manage? I have tried the first two shrink options to no avail, but I have been reluctant to try the last option, which is the 'migrate data to other files in the filegroup' which sounds scary, as there are no other files in the group (as far as I know). This way, I can give the client as many options as possible.Can't tell you how much I appreciate the help.CJM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-05-17 : 16:48:16
|
| I keep 2-5 days on disk, depending upon the system. We also have 5-14 days on tape. But this is due to our contracts with our customers, we'd prefer to keep more. No, you should not be stuck with a 12GB log file. Just how big is your MDF file? You can truncate the log using the command you posted above and then try the shrink. If that doesn't work, we'll need you to run a query to determine what is in use in the tlog file.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-17 : 16:54:54
|
quote:
Originally posted by cjmildenbergCurrently, they are running a daily full backup with a 10 day history. I would consider this enough of a cushion for database corruption. Or are there more compelling reasons to stick with Full recovery model?
"10 day history" - you mean they keep backups for 10 days, and no longer?What if there is a problem recovering from backup, or somehow the backups were not being taken (schedule task has broken), or the database had become corrupted and the backups were also corrupted, and now a recovery is needed?Of course repeating 10 days data re-entry is probably unacceptable, so on that basis 10-days seems reasonable. But what about checking for Fraud? We have had to restore databases from backup going back a year to detect patterns of data abuse - no intention of repeating any work, just needed to make the diagnosis. Might have to do the same for a software data corruption which had gone unnoticed.Whatever period you retain you do need to check that the backups are "sound" - otherwise when you need to restore may be the first time you discover that the backups are corrupted  Restore at least one full backup a week to a temporary database (preferably on another machine), this proves that the backup is able to be restored.Run DBCC CHECKDB on the restored database - this checks that the structure of the database has no corruptions."Or are there more compelling reasons to stick with Full recovery model?"Three main reasons:"Tail log backup". If the main database is corrupted (e.g. a disk controller fault) the Log file may be uncorrupted. Take a "tail log backup" from the log file (which can be done even if the main database file is corrupted), restore the last full "clean" backup and all log backups since, including the "tail log backup" and you don't lose any data at allSecondly, accidental deletion. at 10:01 someone accidentally deletes a whole set of records. Restore the database to 10:00. Or someone accidentally drops a table, but doesn't realise until more, critical, data has been entered. Restore the database to a separate, temporary, database up to 10:00 then "copy" the table from the Temp database to the Live database.Thirdly, you have more chances of restoring the data. In particular if a corruption goes unnoticed for a bit then, lets say, the most recent Full Backups are corrupted. You can restore from a full backup, say, four days ago and then every TLog backup since. This requires that the TLog backups are intact, of course, however the mechanics are very different between database updates and log file backups, so it is not as rare as it might seem - particularly if you are able to mount the log files on a different disk to the data files.Those sorts of things cannot be done in Simple Recovery Model.And I suppose "fourthly" why lose up to a day's work, because you use Simple Recovery Model and only take a Full Backup once a day, when you could use Full Recovery Model and lose a maximum of 15 minutes and in the majority of recovery situations (IME) you would lose none as either the Database or the Log file would not be corrupted.Either way, get the backups off the main machine's disk as soon as they are made. Either copy them to Tape or, if tape backup is once-a-day, then copy them to A.N.Other server so you at least have the backups on two machines, and then they can be copied to tape "overnight".Do NOT use tape backup to backup the database directly to tape (such as Backup Exec) - we see lots of "I can't recover my database directly from tape" questions here, I can't see the point in risking the marriage between Microsoft SQL and A.N.Other company's understanding of direct-backup of SQL - instead: use SQL commands to backup the database to to a disk file, and then tape backup commands to get the file onto tape. Plus, you will have the latest backup(s) on disk, so if you need to restore you don't need to waste time finding the tape, and restoring it onto disk - particularly as the tape you need may even be off-site which creates a further delay when time is critical. |
|
|
|
cjmildenberg
Starting Member
7 Posts |
Posted - 2010-05-17 : 17:00:54
|
| The MDF is 680MBIs this syntax exactly correct? I just need to replace the XXXXX with the database name, no extension (MDF, etc) And I will make this function by clicking on "new query" entering this in the box below, and then clicking on Execute?USE XXXXXBACKUP LOG XXXXX WITH TRUNCATE_ONLYDBCC SHRINKFILE (XXXXX_LOG,2)They recommend I have everybody out of the database before I perform this action. Is this necessary?CJM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
cjmildenberg
Starting Member
7 Posts |
Posted - 2010-05-17 : 17:16:50
|
| Thanks Kristen! - VERY good information, and I will probably present them your message directly.tkizer: the Logical name for the database and log (in database properties) are the following:ABC_DataABC_LogWould this be the correct syntax then?USE ABCBACKUP LOG ABC WITH TRUNCATE_ONLYDBCC SHRINKFILE (ABC_LOG,2)If so, where does the backup go? I am totally out of space on the current drive, so I will need to manually stick it somewhere else.I will be doing this operation very shortly, so I should know if the size can be shrunk or not tonight. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
cjmildenberg
Starting Member
7 Posts |
Posted - 2010-05-17 : 17:29:29
|
| It worked!Down to 23 MB. Huge thanks tkizer; Kristen!I really feel like I have a solid handle on both sides of this issue now, and we can make an effective plan going forward.Incredible help.Sincerely,CJM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Unable to shrink allocated space for logfile.