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 |
|
triton45
Starting Member
16 Posts |
 Posted - 2008-11-04 : 12:58:53
|
| I have a web filter database that runs on sql server 2005 in simple mode. Using the software tools to remove old data, the log file grows to over 10G before we stop the process. To keep the log from growing so rapidly, I have tried taking log backups during the delete process but it does not shrink. Shrinking the file and truncating also do not work.Options / Suggestions? Can I add another log file and the server switch between them?Thanks,Andy |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-04 : 13:03:31
|
| How can you take log backup if database is in simple recovery mode? Check it. Also delete in batches instead of running one transaction. |
 |
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2008-11-04 : 15:11:20
|
| You need to think about tuning your purging process. If tables are big you can test table partitioning to drop old partitions. |
|
|
|
triton45
Starting Member
16 Posts |
Posted - 2008-11-04 : 15:25:59
|
| I changed the database mode to full. I have limited disk space so I will have to work at timing the backup of the tlog.Unfortunately I have to use the software and because of that I have no control over how the deletes occur.Thanks for the suggestions. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-11-04 : 15:32:26
|
| changing it to full mode does nothing as long as you have your process in a single transaction.if you can try setting the RowCount property to process smaller batches._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
|
|
|
Transaction log backups