| Author |
Topic |
|
jbruyet1
Starting Member
35 Posts |
 Posted - 2015-03-21 : 12:03:50
|
| Hey all, I'm now responsible for a server running SQL 2005. It's slowly running out of space so I did some checking and found some HUGE transaction logs. For example, this server has one database that's about 276 megs but the transaction log is a little over 8 gigs. This database (one of several) is part of a proprietary program but I'm not getting much help from their support because they "don't do SQL support," they just support their program. Unfortunately we aren't even considering a different product. Sorry, enough rant. I Googled this issue and saw that doing a Transaction Log backup with a Backup type of Full would shrink the Transaction Logs. I contacted support and I was able to get them to say that I can change the recovery model from Simple to Full (Management Studio is great) but even after doing a Transaction Log backup the logs remain the same size. Am I missing a key step somewhere? Thanks,Joe B |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-03-21 : 17:08:13
|
| Wait. Did you switch from SIMPLE to FULL or FULL to SIMPLE? After a tlog backup, you have to run DBCC SHRINKFILE to actually shrink it down. Backing it up just frees up space INSIDE the file.If you set it to FULL, then perform regular tlog backups such as every 5-15 minutes. If you set it to SIMPLE, then log maintenance is not needed except for the occasional large runaway transaction that might need you to shrink it back down. Shrinking should be doing very rarely and never by a job.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
jbruyet1
Starting Member
35 Posts |
Posted - 2015-03-27 : 13:34:45
|
| Hi Tara, and thanks for the information. I had been told that after I had backed up the database the transaction logs would automagically shrink back to a more manageable size. As to the DBCC SHRINKFILE command, I'm looking at my databases through the Microsoft SQL Server Management Studio (I'm not a SQL guy so I'm trying to not make too many mistakes as I learn). Under that program I found a Shrink task that can do a "Database" or "Files." Can I use this to shrink my transaction log files? I'm guessing that I should use the Files option, but again what I was told is do the database and the transaction logs would do their thing too. Thanks,Joe B |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-03-27 : 13:38:18
|
| Specify files, and then use the dropdown for File type to select the log file. Shrink it down to a reasonable size using the second radio button. Do not just shrink it down to as small as possible. I would recommend 25-50% of the size of the mdf file as a starting point. The size it needs to be depends on the largest transaction, which is often a rebuild index job or even a purge job. We can't answer what size it'll need to be as it depends on the environment.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
jbruyet1
Starting Member
35 Posts |
Posted - 2015-03-27 : 14:01:39
|
| Got it, and thanks for the help. Thanks,Joe B |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-03-27 : 15:30:39
|
If you stay with SIMPLE recovery model you will only be able to recover to your last Full backup (or a Full backup plus a subsequent Differential backup if you choose to make backups like that - probably not as its not that common)That means, in a disaster, you would have to be capable of repeating all the work since that last backup. In olden times data entry was done from "post" and the post was "filed" in a way that meant that re-entering the post was possible ...... in this day and age most data entry seems to be done in real time reacting to phone calls, emails, whatever. Recreating a day's data changes is simply not possible in those scenarios, and for that "Full Recovery Model" and Log Backups are essential. First off take Log Backups frequently (lets say every 10 minutes). Worst case you will then be able to restore your last Full backup, optionally some more recent Differential backup, and then every single Log file, in order, since then.But before you do that there is the possibility (depending on what sort of disaster you have decided to have!!) that you can make what is called a "Tail" log backup. In the event that that is successful (it often is - unless the server is actually on fire!) then you will have zero data loss. Worst case you lose everything back to the last Log backup - a total of 10 minutes data entry loss.There's another upside. Your database file gets corrupted somehow. It doesn't happen often, SQL Server is bullet proof, but a hardware fault or some other "unforeseen circumstance Force Majeure can write garage directly into your database file. The Log file is independent of the Data file and consequently you can restore the last-known-good Full backup and then every Log file since then and you have a very high level of chance that will give you a clean database again (again, with zero data loss :) ). (Take one last Log Backup before you start that exercise ...)Personally, I just couldn't imagine NOT having transaction log backups for those reasons ...Hopefully after taking the trouble to type this you aren't going to tell me that the data in your database is not important / is read only (that would be a laugh!!) ...One other thing. Let's assume you have set up regular Log Backups (let's say every 10 minutes) and something happens - your backup disk gets full so backups fail and your Log File grows continuously as a result, thus you have, once again, wound up with an 8GB Log File. If that happens backup the log, and then shrink it back to whatever size you have determined "it needs". Shrink it now like Tara said, and check it in a week's time - if it needs to grow for normal everyday working elbow-room it will have done that, so the size it becomes will be roughly what you need, so shrink it to that in the future if it ever gets out of hand again.There are very few circumstances where you don't need Full Recovery Model and Log Backups  Before you can start taking Log Backups again you will have to change your database back to Full Recovery Model. |
|
|
|
jbruyet1
Starting Member
35 Posts |
Posted - 2015-03-27 : 19:23:17
|
| Thanks for the info Kristen. And no, I can't say that the data isn't important. The program is a type of suite that includes Finance (Payroll, AP, GL and such), HR, Operations, Vehicle and Facilities Maintenance and other stuff. If this server were to totally die and we didn't have backups I'd probably be looking for a new job. Sounds like it's time to invest in a copy of "SQL For Dummies." Thanks,Joe B |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-03-27 : 19:25:46
|
| You definitely need to use FULL recovery model and frequent LOG backups given that information. At my current job, we backup the logs every 5 minutes. I've heard some SQL Server experts say to do them every minute. The old way was to do them every 15 minutes, but that's considered the old way and for less critical data.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
jbruyet1
Starting Member
35 Posts |
Posted - 2015-03-28 : 11:12:21
|
| Can either of you recommend a good site for SQL tutorials (other than YouTube)? Or a couple of good SQL books? The odds of getting any kind of formal training are very small, even though SQL is a big part of our system. Thanks,Joe B |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-03-28 : 11:13:03
|
quote:
Originally posted by tkizer
I've heard some SQL Server experts say to do them every minute.
Hmmm ... I'm still in "15 minute" mentality.Perhaps its time for SQL's COMMIT to be able to enforce a write to two LOG files, on different servers ... without having to have the full replication do-dah.Most people I know are backing up LOGs to a separate drive, but in the same server. And then backing those up once a day or so. I'm not sure that's enough - big loss if the building goes down - company will have enough to think about finding new temporary premises and getting the Hot Standby Site up & running without also having to reconstitute 24 hours of data ...We have done a few Log Shipping systems (which might be defined as "Make a log backup and immediately copy it offsite"). Most clients stop before that point, expenditure-wise. |
|
|
|
|
HUGE transaction logs