| Author |
Topic |
|
allan8964
Posting Yak Master
249 Posts |
 Posted - 2012-05-30 : 12:26:22
|
| Hi there,I try to run some script but I got this error:The transaction log for database 'xxxx' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databasesI check the column in sys.databases, it says LOG_BACKUP. What does that mean and what shall I do? I try to shrink the db but it says no space to go and DB file is 2.5 Gb and Log file is about 25 Gb and disk space is enough. Thanks in advance. |
|
|
allan8964
Posting Yak Master
249 Posts |
Posted - 2012-05-30 : 12:34:38
|
| OK, I think I found the reason. Log file is set restricted size, now I change it to unrestricted, then it's ok. Still don't get it. How can't I shrink the log file? I use the code:dbcc shrinkfile(N'DBName', 1)dbcc shrinkfile(N'DBName_Log', 2)Why isn't it shrinked? |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-05-30 : 13:35:46
|
| The fil won't shrink unless there is unused space.There won't be unused space unless the log is backed up or truncated.Do not just leave it to grow - it will eventually fill tythe disk and you will be stuck.I suspect yoou need to change it to a simple recovery model if you don't want to install backups.Note a log backup is not the same as a full backup.see (very old but still applicable)http://www.nigelrivett.net/SQLAdmin/TransactionLogFileGrows_1.html==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
allan8964
Posting Yak Master
249 Posts |
Posted - 2012-05-30 : 15:01:02
|
| thanks nigelrivett. Great article. I reset the mode to Simple and run the dbcc shrinkfile (2, truncateonly), now the log goes to 2 Mb. thank you again. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-05-30 : 15:09:51
|
| Don't just shrink it down to minimal size. Find the proper size for your largest recurring operation (data imports, reindexing, regular updates or maintenance, etc.) and size the log file accordingly. Auto growth can cause a significant performance hit, it should not be a regular event for the database. |
|
|
|
allan8964
Posting Yak Master
249 Posts |
Posted - 2012-05-30 : 15:48:54
|
| ok, sounds reasonable but how can i decide largest size for those operations? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-05-30 : 15:53:40
|
quote:
Originally posted by allan8964
ok, sounds reasonable but how can i decide largest size for those operations?
Typically the largest transaction is the rebuild of your largest index. Make sure you tlog is about 125% of that size. So if your largest index is 10GB, you'd set it to 12.5GB or thereabouts. But it all depends on your largest transaction and also if there are multiples occurring at the same time.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-05-31 : 00:56:15
|
| Monitor it.Don't ever shrink it unless you know why.If you see the log grow for an operation then either change that operation or leave the log.For once off things you can shrink but otherwise you need to have room for the log so leave it.There may be a situation where you have two databases which need large logs at different times and you can keep it running by shrinking them after that operation but that's probably heading for trouble and I wouldn't advise it.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
Transaction log is full