Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
readysetstop
Posting Yak Master
123 Posts |
 Posted - 2006-11-06 : 14:27:23
|
I'm hoping for some constructive criticism on this script I just wrote. It checks the logfile size for each DB in the system and deletes the log file from the file system if it is over 150 MB. It then emails a count of the log files deleted to the DBA.I wrote this when I found log files pushing the server disk usage over limit on a regular basis. I overcame a few tricky bits with xp_cmdshell and using variables in sql commands with sp_sqlexec. Mostly, I had to add quotes in specific places using ascii character functions. Fun stuff.Any comments at all will be appreciated. (Constructive ones that is. I already know I'm a noob. Don't be redundant. :-) ) Comments dealing with simplifying code, efficiency or readability are especially appreciated. p.s., The code was written with indentations, I just haven't figured out how to post it that way yet. Sorry. :-)Thanks.Script removed. Partially by request, partially due to my own embarrassment. :-)If you really want to see it, contact me. Edit: 11/7/06 - Well, it put most of the indentation back in. I think I massaged it a bit when I posted it. It's mostly right, though. Thanks, Kristen. |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-06 : 14:55:36
|
| So let me get this straight:You are throwing off all active connections - which may be doing any sort of critical workYou are detaching the database (without a backup)You are deleting the Log fileYou are reattaching the database without the benefit of the Log fileAssuming I've got that right I think you are heading for a major fall, and nothing would induce me to do anything remotely like that on a production system.Getting rid of the log file in this way is going to cause all sorts of problems, not least of which is the fact that the database will resume with no log file, and immediately have to start grabbing disk space (ever more fragmented than last time) which will mean that queries are queued whilst the disk space is acquired and initialised (extending a log file is a very resource-costly process IME), which in turn will lead to more queries arriving before the previous ones have been completed - which creates more log space requests and so on.You should backup the log files regularly to stop them growing too big.If they still grow too big find our what queries are causing the problems and optimise them.If you can't optimise any more then you need more disk space. Period.Your most likely process that is generating large log files is optimisation of the database - and if that is what you are killing off in this insane process you will be compounding the problem by preventing the database from being reindexed and having its statistics updated.Kristen |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-06 : 14:57:26
|
| "The code was written with indentations, I just haven't figured out how to post it that way yet"Sorry, meant to answer that:If you go back and re-edit your post you can add[code]...[/code]and the indentation (which should still be there!) will be correctly displayedKristen |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-11-06 : 16:23:24
|
| There's a very simple setting to prevent this:ALTER DATABASE myDB SET RECOVERY SIMPLEThis will truncate your transaction log after every committed transaction. This should prevent your logs from growing too large, it is certainly a lot safer than detaching and reattaching database files.As Kristen pointed out, you risk corrupting your database with the technique you've scripted. I'd strongly recommend not using this script, ever. Transaction log maintenance is far easier, either with SIMPLE recovery or manual log truncation. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-06 : 16:49:09
|
| Well, first up is to check if the DB is already set to Recovery model SIMPLE or FULL.If FULL and there are NO regular (hourly, or preferable every-10-mins) Log backups then just putting those in place will probably bring the whole shebang back into manageable limits.IME crap queries can generate huge LOG files on SIMPLE recovery model - and a bunch of DBCC REINDEX stuff can build huge LOG files too ... so there may be scope for just tuning some of that wayward stuff a smidgen resulting in revolutionised Log File disk space requirement (i.e. downwards!).Kristen |
|
|
|
readysetstop
Posting Yak Master
123 Posts |
Posted - 2006-11-07 : 07:31:17
|
quote:
Originally posted by Kristen
So let me get this straight:You are throwing off all active connections - which may be doing any sort of critical work
The script runs at 10:00 PM. Nobody should be working then, and any scheduled tasks don't start until 1:00 AM. That's the plan, anyway.quote:
You are detaching the database (without a backup)
Full SQL backup runs at 8:00 PM, two hours before this starts. quote:
You are deleting the Log file
Correct. We have one app where the log grows by almost 20GB per day. It's not too extreme of an example, here.quote:
You are reattaching the database without the benefit of the Log file
Allowing for SQL to generate a new one, yes.quote:
Assuming I've got that right I think you are heading for a major fall, and nothing would induce me to do anything remotely like that on a production system.
Good thing I'm running it in test...  quote:
Getting rid of the log file in this way is going to cause all sorts of problems, not least of which is the fact that the database will resume with no log file, and immediately have to start grabbing disk space (ever more fragmented than last time) which will mean that queries are queued whilst the disk space is acquired and initialised (extending a log file is a very resource-costly process IME), which in turn will lead to more queries arriving before the previous ones have been completed - which creates more log space requests and so on.
Shouldn't be any queries coming in at that time of night, though I do see what you mean about the disk fragmentation. I honestly hadn't considered that.quote:
You should backup the log files regularly to stop them growing too big.
Production has about 65 databases on one machine. Log backups for all that would wipe me out quickly. However, we're only taking hourly log backups on DBs that are transactional, i.e., edited during the day. Databases that just load data at night don't get transaction log backups, though they get full backups nightly. Nightly transaction log backups on those may be the way to go, if I understand you correctly. Though it was my understanding initially that a full backup of the DB should truncate the log, shouldn't it?quote:
If they still grow too big find our what queries are causing the problems and optimise them.
Oh, the developers have been ordered to do that. They should get to it 'real soon now...' I've worked out what I can for most of the load tasks, though there are some that are way too cryptic, even for me. (Or embedded in custom VB executables. Oy.)quote:
If you can't optimise any more then you need more disk space. Period.
We've got a terabyte already. It's more my opinion that I should be doing something different with backups and/or load tasks. However, any option is an option, and more disk is always a good thing.quote:
Your most likely process that is generating large log files is optimisation of the database - and if that is what you are killing off in this insane process you will be compounding the problem by preventing the database from being reindexed and having its statistics updated.
The optimization would run outside the time frame of this script, so I'm not worried about killing that off. From what I understand so far, it would be a runnable process anyway, which this script wouldn't touch. (Only kills sleeping processes.) Though I do see what you're saying about re-indexing and having the stats updated. Moots the point of dropping the log if we just build it right back up again.To address a couple other points made in subsequent comments, I know the recovery model is set to full for everybody. That's the default here, so that we can take transaction log backups. I had not considered taking log backups that frequently, (10 min.) and will have to play with that a bit, to see what it does for us. Part of a potential issue with that is that we have to be able to recover the transactional databases anywhere within the last three hours. 12 to 20 log backups may take up more space than we can afford at this point, but I'll play around with it in test and see what happens.It also sounds like it would be worth my time to set the recovery model individually for databases, rather than just say, "The recovery model is Full". I made that decision to standardize the installation process, but it sounds like it may be better to consider each DB individually.Back to the test box... Thanks to you both for your comments and advice. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-11-07 : 08:05:28
|
| >>> insane processI have to say that this has to be one of the very worst ideas for managing logs I have ever heard.If you want to keep the log sizes under control, just run transaction log backups at very short intervals, like 5 minutes, and only keep them for a short time. Since you are deleting the logs anyway, you obviously don't need them.Also, do everyone a favor, and remove that script before someone uses it.CODO ERGO SUM |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-11-07 : 08:20:56
|
quote:
Originally posted by readysetstop
quote:
Originally posted by Kristen
Getting rid of the log file in this way is going to cause all sorts of problems, not least of which is the fact that the database will resume with no log file, and immediately have to start grabbing disk space (ever more fragmented than last time) which will mean that queries are queued whilst the disk space is acquired and initialised (extending a log file is a very resource-costly process IME), which in turn will lead to more queries arriving before the previous ones have been completed - which creates more log space requests and so on.
Shouldn't be any queries coming in at that time of night, though I do see what you mean about the disk fragmentation. I honestly hadn't considered that.
It's worth considering. Also worth considering, if your database is consistently generating 20GB of log a day, is that it has to first of all create the file to put that log into, and the creation process imposes an overhead on the server that would not be imposed if the (empty) file already existed. quote:
quote:
You should backup the log files regularly to stop them growing too big.
Production has about 65 databases on one machine. Log backups for all that would wipe me out quickly.
Your infrastructure is inadequate for purpose, then. Zany schemes are not the way forward.quote:
However, we're only taking hourly log backups on DBs that are transactional, i.e., edited during the day. Databases that just load data at night don't get transaction log backups, though they get full backups nightly. Nightly transaction log backups on those may be the way to go, if I understand you correctly. Though it was my understanding initially that a full backup of the DB should truncate the log, shouldn't it?
No. A full database backup is log-independant. Only log backups allow for log truncation.quote:
... Part of a potential issue with that is that we have to be able to recover the transactional databases anywhere within the last three hours.
Throwing away the log file will invalidate the log sequence for any prior tran log backups, so you'd be a bit stuck if you needed to go back for more than a day.quote:
It also sounds like it would be worth my time to set the recovery model individually for databases, rather than just say, "The recovery model is Full". I made that decision to standardize the installation process, but it sounds like it may be better to consider each DB individually.
No kidding.-------Moo. :) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-07 : 08:43:17
|
"we're only taking hourly log backups on DBs that are transactional, i.e., edited during the day."Sounds fine"Databases that just load data at night don't get transaction log backups"Set the RECOVERY MODEL to Simple. You don't need point-in-time restore on those and maybe ..."though they get full backups nightly"... if you can recreate the database from the previous nights "load data" then no point having a backup at all (you need to be sure that you've got the database DDL structure backed up though, unless that exists somewhere else - perhaps you can backup an "Empty" database for that purpose?)"my understanding initially that a full backup of the DB should truncate the log, shouldn't it?"Nope, only a Log Backup does that (and it doesn't shrink the physical log file, it just frees up space within it for reuse). But that only applies to Recovery Models other than SIMPLE - i.e. the ones that actually keep the logged data; SIMPLE throws the logged data away once the immediate transaction is completed - so its kept long enough for a power-fail-recovery, or a ROLLBACK  , but not indefinitely for a "Restore to 2 minutes and 36 seconds past midnight" type scenario."They should get to it 'real soon now'"You're new here so you probably won't be familiar with my advice for this sort of scenario - I find a Public Execution works well!!The DEV's boss, or someone high-up, should be presented with the choice of code-tightening or $100K for additional hardware, and a risk-analysis on the string-and-gum alternatives, and the probably slow-down-over-time of the poor-quality queries (after all: we ain't talking about a small, Noddy, database here, are we?!) - at least you have the gumption to be asking about the sanity of the string-and-gum you are planning to use!You (or the DEVs) can use SQL Profiler with a filter for "slow running queries", that will isolate the Low Fruit which will give the most bang-for-buck."more disk is always a good thing"Doesn't sit too well with me in combination with profligate developers though  "The optimization would run outside the time frame of this script ..."Yeah, fair enough, I was being facetious anyway, sorry about that.However, the optimisation stuff may be largely responsible for the huge size of your logs (could be all sorts of things, but a bog-standard optimisation done, for example, with the Maintenance Plan Wizard will just REINDEX the lot with HUGE impact on log file size).I suggest you SHRINK the log file (once, as an experiment), and then have some BATCH file that does:TIME /T >>\MyLog.TXTDIR x:\MyPath\MyDatabase.LDF>>\MyLog.TXTand schedule that to run every 10 minutes or so, and review after a day to see at what time the LDF jumps in size. Compare that to any running jobs so you can isolate them first.(Note that shrinking the log file may have killer-impact on performance for a bit, whilst it grows back again)(Might be smarter to start off with a detailed analysis using SQL Profiler, saved to a database table, and thus analysable-after-the-fact with some probing SQL queries. Depends a bit on whether you have knowledge of that type of analysis. I'm about 50:50 on it being some sort of job that's causing the log growth - a bunch of SELECTs during the day are MUCH more likely to put big log strain on TEMPDB rather than the database itself - hence my hunch to look at growth of LDF related to secluded tasks. (But changing the database from Recovery model FULL -> SIMPLE (if that's an option, as above) may wipe out the problem anyway. I'm only speaking, here, of your notional "read-only" databases, of course the transactional ones will be legitimately generating log files)"I know the recovery model is set to full for everybody"That policy needs to change for what are notionally read-only databases. It will improve performance too."I had not considered taking log backups that frequently, (10 min.)"You get more, smaller TLog backup files. Total filesize in 24 hours is about the same (with a bit of overhead for each file, obviously).PRO: The TLog is backed up before it gets as big as before, so the Max size that the TLog LDF file reaches is smaller.CON: You have a SHED LOAD more files if you need to restore!"Part of a potential issue with that is that we have to be able to recover the transactional databases anywhere within the last three hours."In the event of a problem you MAY be able to take a final TLog backup, and then restore most recent FULL (and DIFF if you do those) and then all TLogs in sequence up to, and including [if required], the last special one you took (and you can use the STOP AFTER option to only restore to a specific point-in-time).However, if the database is "hosed" then you are only as good as the most recently made TLog backup - so the more often you do them the more chances you have for recovery (and if you are thinking along those lines you probably need to be thinking about getting the backup moved as soon as they are made too - most obviously by copying the TLog backup to another machine across the LAN and then subsequently, but somewhat less urgently, onto Tape / off-site / etc.). SO increased frequency helps recover-ability too, in general terms.You may also consider introducing a Differential Backup plan too - to reduce local disk space for backup files. We do FULL on Sunday, DIFF every other day, and TLog backups every 10 minutes or so. No point if most of your database changes each day as the DIFF backups will be similar size to the FULLs. We get about a 10% database change in a week, so the last Diff before the next Full is only about 10% the size of a Full. (Greater risk though, if you can't recover last Sunday's FULL (and then the appropriate DIFF) you have to go back to the previous week's full and ALL intervening TLog backups, so greater chance of a corrupt file - but for that level of security ALL backup files should be being test-restored onto a different machine (and the restored DB then tested with DBCC CHEKCDB) - I digress!"It also sounds like it would be worth my time to set the recovery model individually for databases"Yup, definitely - sorry, didn't see this comment until wittering on about it above!Couple of other thoughts:Check how the auto-increase-database-size is set. With the database size you have 10% is going to be bad news - the DB will be thrashing away for a long time acquiring the disk space and pre-initialising it. Setting the extension size to some reasonable amount of MB would be better [more extensions, but each of them for a short period of time]. Also set an initial size that represents what for you is a sensible starting point. If you DO have to delete the LDF file better SQL kicks back off with, say, 1GB rather than 1MB IME a full backup which includes a large log file (even if the log file is EMPTY at the time of the backup) takes MUCH longer to Restore than the same DB with a small log fileIf you don't already know about it look into Index Defrag instead of Index Rebuild as a means of maintaining indexes etc. Index Defrag (or Index Rebuild for that matter) can be based on the fragmentation of the index (see DBCC SHOWCONTIG), and thus NOT be triggered at all if the index isn't fragmented. Alternatively defrag indexes A-N on Mon/Wed/Fri and N-Z the other days (I'm sure you get the picture!)Plenty of utilities for that sort of thing on SQL Team, I've got some links to stuff here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210 (scroll down to Housekeeping section)and Tara's Blogg has some Sprocs for Backup/Index defrag etc.http://weblogs.sqlteam.com/tarad/category/95.aspxKristen |
|
|
|
readysetstop
Posting Yak Master
123 Posts |
Posted - 2006-11-07 : 13:25:02
|
| Man, I'm glad I came here for advice. Thanks, all. Glad to be a new member. Time to go do some inventory. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-07 : 13:34:48
|
| "Time to go do some inventory"Don't forget to build the Gallows either, and send my invite for the public execution!Kristen |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-11-07 : 14:25:29
|
| this post was good for a laugh. I'm glad you didn't learn this was the "worst idea ever" by having to deal with some kind of data corruption.I love Kristen's response too. I wish you would have left the original code though. It would be cool if you put it back. :)-ec |
|
|
|
Kristen
Test
22859 Posts |
|
|
readysetstop
Posting Yak Master
123 Posts |
Posted - 2006-11-08 : 07:39:03
|
(OK, last post, then I let this thread go so I can do real work around here...)You know... I had avoided forums for a long time, due to some bad experiences I had early on in posting code, asking questions, etc. Always seemed to me like a closed society. Not really sure why other than that there was always a pecking order of sorts, and the newbs just got ignored, or treated like HS freshmen. (Around here, read = poorly.) Glad to see that's not the case, here. I think I'm gonna like this place... :-)Personally, I'd rather solve problems than write cool-lookin' code. My street cred isn't hurting that bad...Glad I could at least provide a good laugh. :-)quote:
Although it may be the wrong solution there isn't much wrong with how its written!
I'm dealing with a dual learning curve, with SQL admin and T-SQL. This is nice to hear. Thanks again. |
|
|
|
|
|
|
|
|
Log Killer Procedure, Constructive criticisms?