| Author |
Topic |
|
sql_server_dba
Posting Yak Master
167 Posts |
 Posted - 2011-03-11 : 12:05:39
|
| Experts...please help me to understand what would happen here...My database is in Simple Recovery Mode.My mdf file is 30GB and ldf file is 3GB.The file growth for ldf is set to a limit up to 10GB.Now i am running a transaction which might fill up my transaction log file to 10 GB and get an error of insufficient space.So, my question is..currently the log space used by ldf file is 0.5 MB and once when i run the transaction and blew it up, how much free space is it gonna be available in ldf? Detail explanation would be highly appreciated.Thanks in Advance. |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2011-03-15 : 08:27:11
|
| in Simple Recovery Mode why SQL Server will log your transaction. NEVER, your ldf size must remain almost same.--------------------------http://connectsql.blogspot.com/ |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-15 : 08:37:44
|
quote:
Originally posted by lionofdezert
in Simple Recovery Mode why SQL Server will log your transaction. NEVER, your ldf size must remain almost same.
Absolutely untrue.In Simple recovery SQL logs all database changes. Some are minimally logged, some are fully logged, but everything is logged.If a single transaction needs more log space than is available, the log will grow if it can. If it cannot (or cannot grow large enough), the transaction will fail with error 9002, be rolled back. When the next checkpoint runs the log space will be truncated and made resuable. If the log grew, it will not shrink again.--Gail ShawSQL Server MVP |
|
|
|
ZZartin
Starting Member
30 Posts |
Posted - 2011-03-15 : 11:44:55
|
| Just out of curiousity, what kind of transaction are you running on a 30GB database that will fill up 10GB of logs?Also you can't recovery from transaction log backups if you're using simple recovery mode so you might as well just truncate the transaction logs as needed. Or switch to a different recovery mode if you're worried about losing data between full/partial backups so you can also use transaction log backups. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-15 : 13:23:01
|
| No need to explicitly truncate the logs, they truncate on checkpoint. An open transaction will prevent truncation until it commits or rolls back--Gail ShawSQL Server MVP |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-03-15 : 15:09:49
|
| "Honey, I blew up the log file"?JimEveryday I learn something that somebody else already knew |
|
|
|
sql_server_dba
Posting Yak Master
167 Posts |
Posted - 2011-03-15 : 19:07:29
|
| Forgot to explain what happend after my testing...Here is what it happened....the transaction log grown until it reached to the max growth size limit and it failed saying...The transaction log for database 'XXXXX' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databasesAfter getting this error, the ldf file space was 10GB and i shrinked back to its original size. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sql_server_dba
Posting Yak Master
167 Posts |
Posted - 2011-03-15 : 19:31:59
|
| Yeah, you are right!! After the failure, i did it within batches :) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
What happens if i blew up my Transaction Log file?
Tara Kizer