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 |
|
DanA
Starting Member
3 Posts |
 Posted - 2006-11-24 : 12:36:07
|
| MS SQL Server 2005 on Windows 2003 SP12-3 hrs ago the ldf file of a DB started growing (reaching 6 GB; its normal size is under 10MB) until the space on disk became 0. We made some space by deleting files but the space disappeared again. We don’t know what triggered this process.There is only one program (SSIS) that writes to that database but that program is not active; at least we don’t see it.We tried unsuccessfully to detach, take offline or shrink the database using Managment Studio: "the database is currently in use”. How could we detect and stop the process that has this effect?How could we reduce to a normal size the ldf file?Thank you very much,DanADinu E Anastasiu |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-24 : 13:02:16
|
| Run SQL Profiler to see what is running on the server, and make sure you are running regular transaction log backups.These dynamic management views will also help you to isolate what is causing that activityhttp://msdn2.microsoft.com/en-gb/library/ms178621.aspx |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-24 : 13:56:26
|
| Take a backup now and then.The log file content will decrease in size doing so. If you really are out of space, try to either truncate the log file or buy some more disks.Peter LarssonHelsingborg, Sweden |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-11-24 : 20:35:56
|
| Treat that the database as 24 hour operation eventhough it's 9 to 5 kind of thing. So backup TLog regularly .. let say 15/30 minutes or so.Normally, it may be related on reindexing. Better to check out that as well. If it's try to use IndexDefrag instead. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-11-25 : 00:34:31
|
| do as peso suggests, run regularly scheduled transaction log backups. regularly scheduled means atleast once an hour.or, put the database in simple recovery mode. if you go the simple recovery route it means that you will no longer be able to perform a point in time recovery. If that is not an issue, then change the database to simple recovery mode and you won't have to worry about tlog file growth.-ec |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-25 : 11:56:34
|
quote:
Originally posted by eyechart
do as peso suggests, run regularly scheduled transaction log backups. regularly scheduled means atleast once an hour.or, put the database in simple recovery mode. if you go the simple recovery route it means that you will no longer be able to perform a point in time recovery. If that is not an issue, then change the database to simple recovery mode and you won't have to worry about tlog file growth.
Using simple recovery mode is a bit more drastic than not being able to perform a point in time recovery, it means you cannot backup or restore transaction logs at all. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-11-25 : 15:41:55
|
quote:
Originally posted by snSQLUsing simple recovery mode is a bit more drastic than not being able to perform a point in time recovery, it means you cannot backup or restore transaction logs at all.
right. this means no point in time recovery. What do you use your tlogs for?-ec |
|
|
|
|
|
|
|
|
LDF file growing and growing...