| Author |
Topic |
|
theboyholty
Posting Yak Master
226 Posts |
 Posted - 2011-03-24 : 06:09:26
|
I just tried to run an insert statement for one of my database tables and recieved this error:The transaction log for database x is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.So I ran SELECT Name,Log_Reuse_Wait,Log_Reuse_Wait_Desc FROM sys.databases and got the following result:Log_Reuse_Wait Log_Reuse_Wait_Desc 2 LOG_BACKUPTrouble is, I don't know what this means or how to resolve it. I'm not a DBA so I've never bothered about Transaction Logs and such things. I don't want to pester one of our DBAs until I think I know what I'm on about or whether I've caused this. I have been running some faitrly big updates / inserts (1.3 million rows) but its nothing I haven't done before.---------------------------------------------------------------------------------http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-03-24 : 08:37:54
|
| What I would suggest is take you take a FULL Backup,Set recovery model to SIMPLE and then insert your records.After insertion take a differential backup (so that you do not loose the data had been BULK inserted if you need a backup in case of failure) and then again set the Recovery model to FULL.Remember TAKE A FULLBACKUP BEFORE INSERTION AND DIFFERENTIAL BACKUP AFTER INSERTION.Also do you have any existing backup strategy ?PBUH |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-24 : 10:06:19
|
| Is this a production database? If so, go and bother the DBA and do not mess with recovery models. If you do and something goes wrong afterwards it could result in major data loss and it would be your fault.If this is a development database, and you do not need the ability to restore the DB to point-in-time (to the actual moment of failure), then you can change it to simple recovery. But do some reading on recovery models.Maybe start with this - [url]http://www.sqlservercentral.com/articles/64582/[/url]--Gail ShawSQL Server MVP |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-24 : 14:10:58
|
| Not for a single operation. I mainly wanted to emphasise that if it's a prod database then there's no way in hell that the recovery model should be changed without the DBA's knowledge.--Gail ShawSQL Server MVP |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
theboyholty
Posting Yak Master
226 Posts |
Posted - 2011-03-25 : 10:41:31
|
| Thanks for all your replies. It is a production database. I asked one of the DBAs and he shrank the transaction log for the database and freed up some space. Shows what I know becasue I would have thought shrinking it would make the problem worse.Its all good now.---------------------------------------------------------------------------------http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-25 : 10:59:59
|
| Shrinking's the complete wrong approach if that's the only thing he did.Now, if he shrank after resolving whatever was wrong, that's maybe OK. Otherwise maybe have your DBA read this? http://www.sqlservercentral.com/articles/64582/--Gail ShawSQL Server MVP |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-03-25 : 23:06:39
|
quote:
Originally posted by GilaMonster
Not for a single operation.
What did you meant by that ?PBUH |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-03-26 : 01:44:06
|
| Thanks Tara for clarifying that.Can anyone post first few steps one should take in case of such a situation i.e log files getting FULL? I mean what should be the initial approach or is the answer "it depends" .PBUH |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-03-26 : 02:42:44
|
| Tara,I believe what you have posted is the root cause analysis.I wanted to know the immediate steps on the ways to bring down the log size once it has bloated to such a level.PBUH |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-26 : 06:54:39
|
quote:
Originally posted by Sachin.Nand
Thanks Tara for clarifying that.Can anyone post first few steps one should take in case of such a situation i.e log files getting FULL?
Sure, I happen to have an entire article on that;http://www.sqlservercentral.com/articles/Transaction+Log/72488/--Gail ShawSQL Server MVP |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-26 : 06:55:25
|
quote:
Originally posted by Sachin.Nand
Tara,I believe what you have posted is the root cause analysis.I wanted to know the immediate steps on the ways to bring down the log size once it has bloated to such a level.
Errr, if you don't know why it's full, how do you expect to be able to fix it?--Gail ShawSQL Server MVP |
|
|
|
|
The transaction log for database x is full