Log size is too large in my environment. Is it possible to create new transaction file every hour. Please tell me how to do it?
asked
Can we create new transaction log file after every hour?
Set log backups to be scheduled for each hour, and rename each successful backup (say with the hour)
YOU have to determine what YOUR backup strategy is. But once in place it will be done automatically.
Just to add on, this does not technically create a new transaction log file, but it will truncate it, which is what you really want to happen. Also remember that you have the option of storing many successive log file backups in one file. Whether or not that is wise depends on your situation. I personally name them by the hour as Kev suggested.
It sounds like you have your database in full recovery mode but are not backing up the logs independently. You can use SQL Agent to schedule a log backup once an hour, or, I'd say, even better, once every 15 minutes or so. This will remove completed transactions from the log, keeping the log from growing forever.
However, if you have log backups in place and you're saying the log is growing to very large proportions, then the problem is with the transactions that you're using. Solutions there will be very different.
One solution for my problem I found is alter database for splitting log files to some file size limit. We can also create file group.
http://msdn.microsoft.com/en-us/library/aa275464(SQL.80).aspx
I would not recommend doing this. It can create performance issues for one, not to mention unnecessary complications.
