|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL Server Transaction Log growingI've got a SQL 2005 server who's transaction log is growing
significantly each day. At night, I do a transaction log backup using Backup Exec 11d which I had thought would commit transactions in the log so that the log entries could then be overwritten, but this does not seem to be happening. Instead, the log file just grows rather than staying the same size. I understand that I can do a log file backup and then shrink in order to make the log smaller, but that is not what I am trying to do, I just want to keep the log the size that it is at. If instead of using backupexec to do the log file backup I use a SQL Maintenance Plan, then this works as I expect it to, but I would like to manage the backup from Backup Exec instead so that it is managed from a central location. Has anyone had and solved this problem, or have any suggestions? Thanks Hi
If it grows that means SQL Server needs the room to fo the work. But sorry, I am not familiar with Backup Exec 11d and how it does work. Show quote "dylan" <dylan.roeh***@gmail.com> wrote in message news:71a8fb0c-9ece-4fd2-9dc7-02b0c1882a1b@a28g2000hsc.googlegroups.com... > I've got a SQL 2005 server who's transaction log is growing > significantly each day. At night, I do a transaction log backup using > Backup Exec 11d which I had thought would commit transactions in the > log so that the log entries could then be overwritten, but this does > not seem to be happening. Instead, the log file just grows rather > than staying the same size. I understand that I can do a log file > backup and then shrink in order to make the log smaller, but that is > not what I am trying to do, I just want to keep the log the size that > it is at. If instead of using backupexec to do the log file backup I > use a SQL Maintenance Plan, then this works as I expect it to, but I > would like to manage the backup from Backup Exec instead so that it is > managed from a central location. Has anyone had and solved this > problem, or have any suggestions? > > Thanks 1) Are you CERTAIN you are doing a transaction log backup? You are correct
in that a tlog backup will remove committed transactions from the log file thus freeing up the space to be reused. 2) Are there uncommitted transactions being held open? -- Show quoteKevin G. Boles TheSQLGuru Indicium Resources, Inc. "dylan" <dylan.roeh***@gmail.com> wrote in message news:71a8fb0c-9ece-4fd2-9dc7-02b0c1882a1b@a28g2000hsc.googlegroups.com... > I've got a SQL 2005 server who's transaction log is growing > significantly each day. At night, I do a transaction log backup using > Backup Exec 11d which I had thought would commit transactions in the > log so that the log entries could then be overwritten, but this does > not seem to be happening. Instead, the log file just grows rather > than staying the same size. I understand that I can do a log file > backup and then shrink in order to make the log smaller, but that is > not what I am trying to do, I just want to keep the log the size that > it is at. If instead of using backupexec to do the log file backup I > use a SQL Maintenance Plan, then this works as I expect it to, but I > would like to manage the backup from Backup Exec instead so that it is > managed from a central location. Has anyone had and solved this > problem, or have any suggestions? > > Thanks
Show quote
On Nov 20, 9:42 am, "TheSQLGuru" <kgbo***@earthlink.net> wrote: Yeah, it's definitely a transaction log backup in backup exec. How> 1) Are you CERTAIN you are doing a transaction log backup? You are correct > in that a tlog backup will remove committed transactions from the log file > thus freeing up the space to be reused. > > 2) Are there uncommitted transactions being held open? > > -- > Kevin G. Boles > TheSQLGuru > Indicium Resources, Inc. > > "dylan" <dylan.roeh***@gmail.com> wrote in message > > news:71a8fb0c-9ece-4fd2-9dc7-02b0c1882a1b@a28g2000hsc.googlegroups.com... > > > I've got a SQL 2005 server who's transaction log is growing > > significantly each day. At night, I do a transaction log backup using > > Backup Exec 11d which I had thought would commit transactions in the > > log so that the log entries could then be overwritten, but this does > > not seem to be happening. Instead, the log file just grows rather > > than staying the same size. I understand that I can do a log file > > backup and then shrink in order to make the log smaller, but that is > > not what I am trying to do, I just want to keep the log the size that > > it is at. If instead of using backupexec to do the log file backup I > > use a SQL Maintenance Plan, then this works as I expect it to, but I > > would like to manage the backup from Backup Exec instead so that it is > > managed from a central location. Has anyone had and solved this > > problem, or have any suggestions? > > > Thanks can I tell if there are uncommitted transaction logs being held open? That's something I was thinking but couldn't find out how to tell. Thanks execute dbcc opentran from within the database context.
-- Show quoteKevin G. Boles TheSQLGuru Indicium Resources, Inc. "dylan" <dylan.roeh***@gmail.com> wrote in message news:512a7869-ccce-4e59-884b-bf47c61d9a1c@e6g2000prf.googlegroups.com... > On Nov 20, 9:42 am, "TheSQLGuru" <kgbo***@earthlink.net> wrote: >> 1) Are you CERTAIN you are doing a transaction log backup? You are >> correct >> in that a tlog backup will remove committed transactions from the log >> file >> thus freeing up the space to be reused. >> >> 2) Are there uncommitted transactions being held open? >> >> -- >> Kevin G. Boles >> TheSQLGuru >> Indicium Resources, Inc. >> >> "dylan" <dylan.roeh***@gmail.com> wrote in message >> >> news:71a8fb0c-9ece-4fd2-9dc7-02b0c1882a1b@a28g2000hsc.googlegroups.com... >> >> > I've got a SQL 2005 server who's transaction log is growing >> > significantly each day. At night, I do a transaction log backup using >> > Backup Exec 11d which I had thought would commit transactions in the >> > log so that the log entries could then be overwritten, but this does >> > not seem to be happening. Instead, the log file just grows rather >> > than staying the same size. I understand that I can do a log file >> > backup and then shrink in order to make the log smaller, but that is >> > not what I am trying to do, I just want to keep the log the size that >> > it is at. If instead of using backupexec to do the log file backup I >> > use a SQL Maintenance Plan, then this works as I expect it to, but I >> > would like to manage the backup from Backup Exec instead so that it is >> > managed from a central location. Has anyone had and solved this >> > problem, or have any suggestions? >> >> > Thanks > > Yeah, it's definitely a transaction log backup in backup exec. How > can I tell if there are uncommitted transaction logs being held open? > That's something I was thinking but couldn't find out how to tell. > > Thanks
Show quote
On Nov 20, 12:33 pm, "TheSQLGuru" <kgbo***@earthlink.net> wrote: That command returned No active open transactions. I just ran a> execute dbcc opentran from within the database context. > > -- > Kevin G. Boles > TheSQLGuru > Indicium Resources, Inc. > > "dylan" <dylan.roeh***@gmail.com> wrote in message > > news:512a7869-ccce-4e59-884b-bf47c61d9a1c@e6g2000prf.googlegroups.com... > > > On Nov 20, 9:42 am, "TheSQLGuru" <kgbo***@earthlink.net> wrote: > >> 1) Are you CERTAIN you are doing a transaction log backup? You are > >> correct > >> in that a tlog backup will remove committed transactions from the log > >> file > >> thus freeing up the space to be reused. > > >> 2) Are there uncommitted transactions being held open? > > >> -- > >> Kevin G. Boles > >> TheSQLGuru > >> Indicium Resources, Inc. > > >> "dylan" <dylan.roeh***@gmail.com> wrote in message > > >>news:71a8fb0c-9ece-4fd2-9dc7-02b0c1882a1b@a28g2000hsc.googlegroups.com... > > >> > I've got a SQL 2005 server who's transaction log is growing > >> > significantly each day. At night, I do a transaction log backup using > >> > Backup Exec 11d which I had thought would commit transactions in the > >> > log so that the log entries could then be overwritten, but this does > >> > not seem to be happening. Instead, the log file just grows rather > >> > than staying the same size. I understand that I can do a log file > >> > backup and then shrink in order to make the log smaller, but that is > >> > not what I am trying to do, I just want to keep the log the size that > >> > it is at. If instead of using backupexec to do the log file backup I > >> > use a SQL Maintenance Plan, then this works as I expect it to, but I > >> > would like to manage the backup from Backup Exec instead so that it is > >> > managed from a central location. Has anyone had and solved this > >> > problem, or have any suggestions? > > >> > Thanks > > > Yeah, it's definitely a transaction log backup in backup exec. How > > can I tell if there are uncommitted transaction logs being held open? > > That's something I was thinking but couldn't find out how to tell. > > > Thanks backup exec log backup and then performed a shrink on the log file and it did in fact shrink down to 17MB, so the committed logs appear to be removed correctly. Why isn't SQL then reusing that space instead of making the log file larger? I cannot explain your circumstances, unless you simply have VERY large
transactions. In any case it is usually a BAD idea to shrink the log file unless you absolutely have to. It is now going to start growing again and each growth does 2 bad things: 1) delays database activity and 2) causes OS level file fragmentation which leads to poor I/O performance. -- Show quoteKevin G. Boles TheSQLGuru Indicium Resources, Inc. "dylan" <dylan.roeh***@gmail.com> wrote in message news:4df846aa-9dd1-458e-b5fb-813effd61c26@d27g2000prf.googlegroups.com... > On Nov 20, 12:33 pm, "TheSQLGuru" <kgbo***@earthlink.net> wrote: >> execute dbcc opentran from within the database context. >> >> -- >> Kevin G. Boles >> TheSQLGuru >> Indicium Resources, Inc. >> >> "dylan" <dylan.roeh***@gmail.com> wrote in message >> >> news:512a7869-ccce-4e59-884b-bf47c61d9a1c@e6g2000prf.googlegroups.com... >> >> > On Nov 20, 9:42 am, "TheSQLGuru" <kgbo***@earthlink.net> wrote: >> >> 1) Are you CERTAIN you are doing a transaction log backup? You are >> >> correct >> >> in that a tlog backup will remove committed transactions from the log >> >> file >> >> thus freeing up the space to be reused. >> >> >> 2) Are there uncommitted transactions being held open? >> >> >> -- >> >> Kevin G. Boles >> >> TheSQLGuru >> >> Indicium Resources, Inc. >> >> >> "dylan" <dylan.roeh***@gmail.com> wrote in message >> >> >>news:71a8fb0c-9ece-4fd2-9dc7-02b0c1882a1b@a28g2000hsc.googlegroups.com... >> >> >> > I've got a SQL 2005 server who's transaction log is growing >> >> > significantly each day. At night, I do a transaction log backup >> >> > using >> >> > Backup Exec 11d which I had thought would commit transactions in the >> >> > log so that the log entries could then be overwritten, but this does >> >> > not seem to be happening. Instead, the log file just grows rather >> >> > than staying the same size. I understand that I can do a log file >> >> > backup and then shrink in order to make the log smaller, but that is >> >> > not what I am trying to do, I just want to keep the log the size >> >> > that >> >> > it is at. If instead of using backupexec to do the log file backup >> >> > I >> >> > use a SQL Maintenance Plan, then this works as I expect it to, but I >> >> > would like to manage the backup from Backup Exec instead so that it >> >> > is >> >> > managed from a central location. Has anyone had and solved this >> >> > problem, or have any suggestions? >> >> >> > Thanks >> >> > Yeah, it's definitely a transaction log backup in backup exec. How >> > can I tell if there are uncommitted transaction logs being held open? >> > That's something I was thinking but couldn't find out how to tell. >> >> > Thanks > > That command returned No active open transactions. I just ran a > backup exec log backup and then performed a shrink on the log file and > it did in fact shrink down to 17MB, so the committed logs appear to be > removed correctly. Why isn't SQL then reusing that space instead of > making the log file larger?
Show quote
On Nov 20, 2:39 pm, "TheSQLGuru" <kgbo***@earthlink.net> wrote: Yeah, thanks for your help> I cannot explain your circumstances, unless you simply have VERY large > transactions. > > In any case it is usually a BAD idea to shrink the log file unless you > absolutely have to. It is now going to start growing again and each growth > does 2 bad things: 1) delays database activity and 2) causes OS level file > fragmentation which leads to poor I/O performance. > > -- > Kevin G. Boles > TheSQLGuru > Indicium Resources, Inc. > > "dylan" <dylan.roeh***@gmail.com> wrote in message > > news:4df846aa-9dd1-458e-b5fb-813effd61c26@d27g2000prf.googlegroups.com... > > > On Nov 20, 12:33 pm, "TheSQLGuru" <kgbo***@earthlink.net> wrote: > >> execute dbcc opentran from within the database context. > > >> -- > >> Kevin G. Boles > >> TheSQLGuru > >> Indicium Resources, Inc. > > >> "dylan" <dylan.roeh***@gmail.com> wrote in message > > >>news:512a7869-ccce-4e59-884b-bf47c61d9a1c@e6g2000prf.googlegroups.com... > > >> > On Nov 20, 9:42 am, "TheSQLGuru" <kgbo***@earthlink.net> wrote: > >> >> 1) Are you CERTAIN you are doing a transaction log backup? You are > >> >> correct > >> >> in that a tlog backup will remove committed transactions from the log > >> >> file > >> >> thus freeing up the space to be reused. > > >> >> 2) Are there uncommitted transactions being held open? > > >> >> -- > >> >> Kevin G. Boles > >> >> TheSQLGuru > >> >> Indicium Resources, Inc. > > >> >> "dylan" <dylan.roeh***@gmail.com> wrote in message > > >> >>news:71a8fb0c-9ece-4fd2-9dc7-02b0c1882a1b@a28g2000hsc.googlegroups.com... > > >> >> > I've got a SQL 2005 server who's transaction log is growing > >> >> > significantly each day. At night, I do a transaction log backup > >> >> > using > >> >> > Backup Exec 11d which I had thought would commit transactions in the > >> >> > log so that the log entries could then be overwritten, but this does > >> >> > not seem to be happening. Instead, the log file just grows rather > >> >> > than staying the same size. I understand that I can do a log file > >> >> > backup and then shrink in order to make the log smaller, but that is > >> >> > not what I am trying to do, I just want to keep the log the size > >> >> > that > >> >> > it is at. If instead of using backupexec to do the log file backup > >> >> > I > >> >> > use a SQL Maintenance Plan, then this works as I expect it to, but I > >> >> > would like to manage the backup from Backup Exec instead so that it > >> >> > is > >> >> > managed from a central location. Has anyone had and solved this > >> >> > problem, or have any suggestions? > > >> >> > Thanks > > >> > Yeah, it's definitely a transaction log backup in backup exec. How > >> > can I tell if there are uncommitted transaction logs being held open? > >> > That's something I was thinking but couldn't find out how to tell. > > >> > Thanks > > > That command returned No active open transactions. I just ran a > > backup exec log backup and then performed a shrink on the log file and > > it did in fact shrink down to 17MB, so the committed logs appear to be > > removed correctly. Why isn't SQL then reusing that space instead of > > making the log file larger? Make sure the Backup from BackupExec is using the SQL Server plugin and not
simply backing up the file itself. And doing a log backup only once a day is pretty pointless. You should think about doing them far more often. -- Show quoteAndrew J. Kelly SQL MVP Solid Quality Mentors "dylan" <dylan.roeh***@gmail.com> wrote in message news:4df846aa-9dd1-458e-b5fb-813effd61c26@d27g2000prf.googlegroups.com... > On Nov 20, 12:33 pm, "TheSQLGuru" <kgbo***@earthlink.net> wrote: >> execute dbcc opentran from within the database context. >> >> -- >> Kevin G. Boles >> TheSQLGuru >> Indicium Resources, Inc. >> >> "dylan" <dylan.roeh***@gmail.com> wrote in message >> >> news:512a7869-ccce-4e59-884b-bf47c61d9a1c@e6g2000prf.googlegroups.com... >> >> > On Nov 20, 9:42 am, "TheSQLGuru" <kgbo***@earthlink.net> wrote: >> >> 1) Are you CERTAIN you are doing a transaction log backup? You are >> >> correct >> >> in that a tlog backup will remove committed transactions from the log >> >> file >> >> thus freeing up the space to be reused. >> >> >> 2) Are there uncommitted transactions being held open? >> >> >> -- >> >> Kevin G. Boles >> >> TheSQLGuru >> >> Indicium Resources, Inc. >> >> >> "dylan" <dylan.roeh***@gmail.com> wrote in message >> >> >>news:71a8fb0c-9ece-4fd2-9dc7-02b0c1882a1b@a28g2000hsc.googlegroups.com... >> >> >> > I've got a SQL 2005 server who's transaction log is growing >> >> > significantly each day. At night, I do a transaction log backup >> >> > using >> >> > Backup Exec 11d which I had thought would commit transactions in the >> >> > log so that the log entries could then be overwritten, but this does >> >> > not seem to be happening. Instead, the log file just grows rather >> >> > than staying the same size. I understand that I can do a log file >> >> > backup and then shrink in order to make the log smaller, but that is >> >> > not what I am trying to do, I just want to keep the log the size >> >> > that >> >> > it is at. If instead of using backupexec to do the log file backup >> >> > I >> >> > use a SQL Maintenance Plan, then this works as I expect it to, but I >> >> > would like to manage the backup from Backup Exec instead so that it >> >> > is >> >> > managed from a central location. Has anyone had and solved this >> >> > problem, or have any suggestions? >> >> >> > Thanks >> >> > Yeah, it's definitely a transaction log backup in backup exec. How >> > can I tell if there are uncommitted transaction logs being held open? >> > That's something I was thinking but couldn't find out how to tell. >> >> > Thanks > > That command returned No active open transactions. I just ran a > backup exec log backup and then performed a shrink on the log file and > it did in fact shrink down to 17MB, so the committed logs appear to be > removed correctly. Why isn't SQL then reusing that space instead of > making the log file larger?
Show quote
On Nov 20, 3:29 pm, "Andrew J. Kelly" <sqlmvpnooos***@shadhawk.com> the backup is using the SQL Server plugin, that's where you specifywrote: > Make sure the Backup from BackupExec is using the SQL Server plugin and not > simply backing up the file itself. And doing a log backup only once a day > is pretty pointless. You should think about doing them far more often. > > -- > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > "dylan" <dylan.roeh***@gmail.com> wrote in message > > news:4df846aa-9dd1-458e-b5fb-813effd61c26@d27g2000prf.googlegroups.com... > > > On Nov 20, 12:33 pm, "TheSQLGuru" <kgbo***@earthlink.net> wrote: > >> execute dbcc opentran from within the database context. > > >> -- > >> Kevin G. Boles > >> TheSQLGuru > >> Indicium Resources, Inc. > > >> "dylan" <dylan.roeh***@gmail.com> wrote in message > > >>news:512a7869-ccce-4e59-884b-bf47c61d9a1c@e6g2000prf.googlegroups.com... > > >> > On Nov 20, 9:42 am, "TheSQLGuru" <kgbo***@earthlink.net> wrote: > >> >> 1) Are you CERTAIN you are doing a transaction log backup? You are > >> >> correct > >> >> in that a tlog backup will remove committed transactions from the log > >> >> file > >> >> thus freeing up the space to be reused. > > >> >> 2) Are there uncommitted transactions being held open? > > >> >> -- > >> >> Kevin G. Boles > >> >> TheSQLGuru > >> >> Indicium Resources, Inc. > > >> >> "dylan" <dylan.roeh***@gmail.com> wrote in message > > >> >>news:71a8fb0c-9ece-4fd2-9dc7-02b0c1882a1b@a28g2000hsc.googlegroups.com... > > >> >> > I've got a SQL 2005 server who's transaction log is growing > >> >> > significantly each day. At night, I do a transaction log backup > >> >> > using > >> >> > Backup Exec 11d which I had thought would commit transactions in the > >> >> > log so that the log entries could then be overwritten, but this does > >> >> > not seem to be happening. Instead, the log file just grows rather > >> >> > than staying the same size. I understand that I can do a log file > >> >> > backup and then shrink in order to make the log smaller, but that is > >> >> > not what I am trying to do, I just want to keep the log the size > >> >> > that > >> >> > it is at. If instead of using backupexec to do the log file backup > >> >> > I > >> >> > use a SQL Maintenance Plan, then this works as I expect it to, but I > >> >> > would like to manage the backup from Backup Exec instead so that it > >> >> > is > >> >> > managed from a central location. Has anyone had and solved this > >> >> > problem, or have any suggestions? > > >> >> > Thanks > > >> > Yeah, it's definitely a transaction log backup in backup exec. How > >> > can I tell if there are uncommitted transaction logs being held open? > >> > That's something I was thinking but couldn't find out how to tell. > > >> > Thanks > > > That command returned No active open transactions. I just ran a > > backup exec log backup and then performed a shrink on the log file and > > it did in fact shrink down to 17MB, so the committed logs appear to be > > removed correctly. Why isn't SQL then reusing that space instead of > > making the log file larger? specifically a Tranaction Log backup. And I'm doing it only once per day only because of trying to keep the log file small, not for recovery purposes. If you aren't concerned about tlog recoveries, why not set the recovery mode
to Simple, which WILL flush commited transactions out of the log and keep it from growing. Guaranteed. -- Show quoteKevin G. Boles TheSQLGuru Indicium Resources, Inc. "dylan" <dylan.roeh***@gmail.com> wrote in message news:59eafabe-0078-4630-a0b0-ef688d7248b3@s8g2000prg.googlegroups.com... > On Nov 20, 3:29 pm, "Andrew J. Kelly" <sqlmvpnooos***@shadhawk.com> > wrote: >> Make sure the Backup from BackupExec is using the SQL Server plugin and >> not >> simply backing up the file itself. And doing a log backup only once a >> day >> is pretty pointless. You should think about doing them far more often. >> >> -- >> Andrew J. Kelly SQL MVP >> Solid Quality Mentors >> >> "dylan" <dylan.roeh***@gmail.com> wrote in message >> >> news:4df846aa-9dd1-458e-b5fb-813effd61c26@d27g2000prf.googlegroups.com... >> >> > On Nov 20, 12:33 pm, "TheSQLGuru" <kgbo***@earthlink.net> wrote: >> >> execute dbcc opentran from within the database context. >> >> >> -- >> >> Kevin G. Boles >> >> TheSQLGuru >> >> Indicium Resources, Inc. >> >> >> "dylan" <dylan.roeh***@gmail.com> wrote in message >> >> >>news:512a7869-ccce-4e59-884b-bf47c61d9a1c@e6g2000prf.googlegroups.com... >> >> >> > On Nov 20, 9:42 am, "TheSQLGuru" <kgbo***@earthlink.net> wrote: >> >> >> 1) Are you CERTAIN you are doing a transaction log backup? You are >> >> >> correct >> >> >> in that a tlog backup will remove committed transactions from the >> >> >> log >> >> >> file >> >> >> thus freeing up the space to be reused. >> >> >> >> 2) Are there uncommitted transactions being held open? >> >> >> >> -- >> >> >> Kevin G. Boles >> >> >> TheSQLGuru >> >> >> Indicium Resources, Inc. >> >> >> >> "dylan" <dylan.roeh***@gmail.com> wrote in message >> >> >> >>news:71a8fb0c-9ece-4fd2-9dc7-02b0c1882a1b@a28g2000hsc.googlegroups.com... >> >> >> >> > I've got a SQL 2005 server who's transaction log is growing >> >> >> > significantly each day. At night, I do a transaction log backup >> >> >> > using >> >> >> > Backup Exec 11d which I had thought would commit transactions in >> >> >> > the >> >> >> > log so that the log entries could then be overwritten, but this >> >> >> > does >> >> >> > not seem to be happening. Instead, the log file just grows >> >> >> > rather >> >> >> > than staying the same size. I understand that I can do a log >> >> >> > file >> >> >> > backup and then shrink in order to make the log smaller, but that >> >> >> > is >> >> >> > not what I am trying to do, I just want to keep the log the size >> >> >> > that >> >> >> > it is at. If instead of using backupexec to do the log file >> >> >> > backup >> >> >> > I >> >> >> > use a SQL Maintenance Plan, then this works as I expect it to, >> >> >> > but I >> >> >> > would like to manage the backup from Backup Exec instead so that >> >> >> > it >> >> >> > is >> >> >> > managed from a central location. Has anyone had and solved this >> >> >> > problem, or have any suggestions? >> >> >> >> > Thanks >> >> >> > Yeah, it's definitely a transaction log backup in backup exec. How >> >> > can I tell if there are uncommitted transaction logs being held >> >> > open? >> >> > That's something I was thinking but couldn't find out how to tell. >> >> >> > Thanks >> >> > That command returned No active open transactions. I just ran a >> > backup exec log backup and then performed a shrink on the log file and >> > it did in fact shrink down to 17MB, so the committed logs appear to be >> > removed correctly. Why isn't SQL then reusing that space instead of >> > making the log file larger? > > the backup is using the SQL Server plugin, that's where you specify > specifically a Tranaction Log backup. And I'm doing it only once per > day only because of trying to keep the log file small, not for > recovery purposes.  
Other interesting topics
|
|||||||||||||||||||||||
