question

sybil avatar image
sybil asked

Can we create new transaction log file after every hour?

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?

transaction-log
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

Set log backups to be scheduled for each hour, and rename each successful backup (say with the hour)

4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
YOU have to determine what YOUR backup strategy is. But once in place it will be done automatically.
2 Likes 2 ·
sybil avatar image sybil commented ·
Cannot it be done automatically with some sql server system stored procedure or like that.
0 Likes 0 ·
TimothyAWiseman avatar image TimothyAWiseman commented ·
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.
0 Likes 0 ·
venkatreddy avatar image venkatreddy commented ·
Thanks Kev.Can we make it through a scheduled job??
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sybil avatar image
sybil answered

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

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image TimothyAWiseman commented ·
I would not recommend doing this. It can create performance issues for one, not to mention unnecessary complications.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.