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 |
|
techzone12
Starting Member
1 Post |
 Posted - 2010-07-30 : 11:42:19
|
| I had a problem where the log file grows very big. I set the databse "under options to auto -shrink. Since then it appears that the log file is under control. I have a different problem however. The tempdb is now growing like crazy.I am suspecting that it has to do with some batch files (bulk inserts) I am running every 15minutes. The sql script worked fine under SQL 7. The code has this line repeated in every sql script: "dump tran Mydatabase with no_log go"I found out that this syntax is not supported by SQL 2008. I am suspecting that "Commit transaction" is the equivalent of the above. Is this the case? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-07-30 : 14:24:10
|
| Are you sure you haven't got any open transactions when you are looking at the size ? Use the DBCC OPENTRAN to checkJack Vamvas--------------------http://www.sqlserver-dba.com |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-31 : 01:46:23
|
| "I had a problem where the log file grows very big."How big is "very big"? The LDF file being up to 120% of the size of the MDF I would consider "normal" - in the sense that I would not worry about it."I set the databse "under options to auto -shrink."You need to fix the problem and remove this option. It will fragment your database and reduce performance."The sql script worked fine under SQL 7. The code has this line repeated in every sql script: "dump tran Mydatabase with no_log go"I found out that this syntax is not supported by SQL 2008.""This breaks the backup chain and will mean that you cannot restore (assuming you are using FULL Recovery Model you would normally be able to restore to any point in time, including if your database is corrupted being able to take one final TLog backup and restoring everything with no data loss [assuming TLog not also corrupted] - if you use the above command then you cannot do that, you can only recover from the last Full backup - or the one before that if that one was made before you discovered the database was corrupted)"I am suspecting that "Commit transaction" is the equivalent of the above. Is this the case?"No, although if your database is using SIMPLE Recovery Model, rather than FULL, that would help. But you are then back to only being able to restore from a full backup and having a MUCH weaker proposition if ever your database became corrupted.If you are using FULL Recovery Model take Transaction backups frequently - every 15 minutes would be a good interval. (more files perhaps, but total filesize for the day will be the same day, plus a bit of overhead for each file)Then your LDF file will only be as big as the busiest 15 minutes in the day (busiest in terms of number of transactions)Then look at the transaction backup files. Is there a time of day / day of week when particularly large files are created? If so examine what activities are occurring at that time - e.g. scheduled tasks - and review how they operate to potentially reduce the number of transactions |
|
|
|
|
|
|
SQL 2008 tempdb growing very big