| Author |
Topic |
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
 Posted - 2010-03-23 : 01:50:52
|
| [Microsoft][ODBC SQL Server Driver][SQL Server]The transaction log for database 'mydatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases the above is the error message i'm getting what should I do? can anyone advise me?<edit by tkizer>removed code tags as it was making the topic too wide</edit> |
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2010-03-23 : 02:17:50
|
| if i try to shring the file using a dbcc_shrinkfile I get an error A sever error occured on the current command The results, if any, should be discarded |
 |
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2010-03-23 : 03:26:43
|
| I finally fixed by restarting sql and then it let me do a shrinkbut what is the correct way to be doing this. I have my database set as full backup as I need recovery but the files are getting bigger - should I be shrinking the log files regularly? Please advise? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-03-23 : 04:20:09
|
| Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]--Gail ShawSQL Server MVP |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2010-03-23 : 04:37:24
|
| so is the right thing to add a backup transaction log to be scheduled nightly would that help it from becoming so large? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-03-23 : 05:56:58
|
| Nightly? I'd think a little more often than that.You said that your database is in full recovery model as you need 'recovery', but without frequent log backups you don't have any more recoverability than you would have had in simple recovery.Do you need to be able to restore to a point-in-time in case of a disaster?What's the maximum allowable data loss in the case of a disaster?--Gail ShawSQL Server MVP |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2010-03-23 : 06:05:06
|
| yes full recovery ideally i'd like to be able to restore very often my backup script is a script i took from this forum that backs up every night all the databases.how often would you do a dbcc_shrinkfile |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-03-23 : 06:14:57
|
What do you mean by 'restore very often'?What is your maximum allowable data loss in the case of a disaster? If a drive fails, and you can only restore to the previous night, is that acceptable? If not, what is?quote:
how often would you do a dbcc_shrinkfile
Never. It's absolutely unnecessary on a well-managed database.--Gail ShawSQL Server MVP |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2010-03-23 : 06:27:59
|
| i would like to never lose more then an hour of data - this is a crucial database and records are added often |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2010-03-23 : 06:29:11
|
| so if i don't need to shrink - what do i need to add to my scheduled tasks to avoid this happening again -- an hour backup of the log? does that help save on space as well?I think since I did the shrink this morning the sql is working better (using less of the cpu) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-23 : 07:59:20
|
You may need to do the shrink once - to get the TLog file back to a "normal" size, but if you shrink it often, and it just regrows, then a) it needs to be that big  and b) the process makes the file more fragmented, and thus less efficient.I recommend that you backup the TLog every 15 minutes"does that help save on space as well"Backing up the TLog once a day means that the TLog size has to be big enough to hold a whole day's transactions.Backing it up every 15 minutes means the Tlog file only has to be big enough for the busiest 15 minutes in the day.The total size of the Tlog backups, for the day, will be the same - but each file will also have some "overhead" requirements.You will, of course, have a lot more TLog backup files ( 24 hours * 4-per-hour) |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2010-03-23 : 08:05:20
|
| thanks kristen - i think it's actually your backup commands that I use.so only for the big logs is there reason to backup every 15 mintues?and is that just the statement BACKUP LOG db WITH TRUNCATE_ONLY |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-03-23 : 08:34:38
|
| Did you bother to read the article I posted?If you need to be able to recover to 1 hour, you need log backup (backup log to disk) at least once an hour. The interval between log backups should be no larger than the maximum amount of data you're willing to lose in the case of a disaster.If you're running BACKUP LOG db WITH TRUNCATE_ONLY, you may as well switch to Simple Recovery, because that's essentially what you're in.If you truncate the log, the log records are not retained, you cannot back the log up, and if there's a disk failure you're restoring to your last full database backup, because you have no other options. Since this is a crucial database, that may not be exactly what you want.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-23 : 08:50:47
|
| "so only for the big logs is there reason to backup every 15 mintues"You can do it once a day for any database where you are happy to lose 24 hours work ... but want's the point if you can have it backup every 15 minutes and then only lose 15 minutes work instead?"BACKUP LOG db WITH TRUNCATE_ONLY"That will just truncate the file, and not make any backup. |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2010-03-23 : 08:59:48
|
| so how do i backup won't this take more space and not less? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-23 : 10:59:21
|
| "won't this take more space and not less?"What is your thinking for why that would be the case? |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2010-03-23 : 11:22:37
|
| i would think we would have more backups or does it keep overwriting?can you point me to the syntax of this type of backup? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-03-24 : 00:26:37
|
quote:
Originally posted by esthera
won't this take more space and not less?
If you backup your transaction log once a day, the file size will be about the same size of all the 15-minute interval files. So you don't use more space or less. You just have more files, but the total size will be about the same.We backup our transaction logs every 15 minutes even on less critical databases. On systems that can revert to the last full backup, we use SIMPLE recovery model. We don't see the point of an hourly tlog backup when every 15 minutes is better.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2010-03-24 : 02:16:50
|
| thanks can you tell me the syntax you run every 15 minutes to backup |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-24 : 04:10:07
|
| "i would think we would have more backups or does it keep overwriting?"Ah, I see what you are thinking.Lets say you have 24,000 logged transactions a day, and they are 100 bytes each, on average.So that is 24,000 * 100 = 2,400,000 bytes per day.If you back up the Log every hour there will be, on average, only 1,000 new transactions in that hour, of 100 bytes each, so that is 100,000 bytes per hour - and you will have 24 backups in the day, each of 100,000 - so 24 * 100,000 = 2,400,000 bytes.So you will have the same total for all the Tlog files in the day as if you did it only once a day. (Actually there is some overhead to each Tlog backup file, so there will be a little more)However, if you backup the Tlog every hour the maximum size of the TLog is 1,000 transactions * 100 bytes = 100,000 bytes.But if you only back it up once a day then the Tlog file (i.e. the .LDF file) has got to hold 24 * 1,000 * 100 = 2,400,000 bytes. i.e. the TLog/.LDF file has to be 24 times the average size of the hourly system. And if you back up every 15 minutes then that Tlog/.LDF file is about 1% of the sizeIn the real world it isn't that much of a saving, sadly, because the number of transactions per hour is not evenly spread through the day - and there are always some very big transactions - in particular and index rebuild housekeeping.When the TLog backup is made all the transactions it has backed up are flagged so that the space in the Tlog file can be reused.Transaction Backups are NOT cumulative. To restore you must restore ALL Tlog backup files (in sequence)(OTOH Differential backups ARE cumulative - so each time you make a Differential backup it gets bigger - until the next time you make a FULL backup; perhaps that's where the confusion is?)"can you point me to the syntax of this type of backup?Best to read SQL Documentation "Books Online" I think - look under BACKUP LOG |
|
|
|
Next Page
|
log is too full - please advise