Backup to Network Drive: control

Hello All
I should start by saying, I am not a SQL guru at all!
We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
NetBackup 5.1 MP5 using an online SQL Agent.
I will start with the good news, in that all FULL Backups work fine! I have
discovered that the Transaction logs are NOT included in this FULL Backup.
In NetBackup there is an option to "Backup and Truncate the logs" - so I
attempted to do this, and it claims it worked fine (the Application log also
shows event id 18265 and the description gives an indication "log was backed
up Database ect and ending in "No user action is required".
Great! but here is the problem. The .ldf files are growing WAY out of
control. For example, they can grow to over 40GB in a week !
I know there is a query or a SHRINK command that can be used to help, but I
am being told that NetBackup should be "truncating" the log down. I guess
this means shrinking.
Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
2005 Administrator has to manually shrink the logs or run a query to do this?
Or could something be setup wrong in SQL2005.
Any help is warmly appreciated.
Thank you
SimonConsider a log file a bucket. As modifications are performed, the bucket is filled. It is only
emptied when you BACKUP LOG, not for BACKUP DATABASE. Unless you have the database in simple
recovery mode, when you will get an error message if you do BACKUP LOG. This bucket can grow in size
by SQL Server if it becomes full and modifications are performed (autogrow).
So, one reason for large log files is that you never did backup log. You now did it, and that log
backup was probably pretty big and you should now have a lot of empty space in the log file. This
can be a valid situation for shrinking the log file. See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
But, since you use some 3:rd party tool to do the backup, we don't know what command (like BACKUP
LOG) was submitted. I'd run a Profiler trace to see what backup command is submitted by that app.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:CEEAEBF3-D30B-476B-B248-D0A93510139C@.microsoft.com...
> Hello All
> I should start by saying, I am not a SQL guru at all!
> We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
> NetBackup 5.1 MP5 using an online SQL Agent.
> I will start with the good news, in that all FULL Backups work fine! I have
> discovered that the Transaction logs are NOT included in this FULL Backup.
> In NetBackup there is an option to "Backup and Truncate the logs" - so I
> attempted to do this, and it claims it worked fine (the Application log also
> shows event id 18265 and the description gives an indication "log was backed
> up Database ect and ending in "No user action is required".
> Great! but here is the problem. The .ldf files are growing WAY out of
> control. For example, they can grow to over 40GB in a week !
> I know there is a query or a SHRINK command that can be used to help, but I
> am being told that NetBackup should be "truncating" the log down. I guess
> this means shrinking.
> Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
> 2005 Administrator has to manually shrink the logs or run a query to do this?
> Or could something be setup wrong in SQL2005.
> Any help is warmly appreciated.
> Thank you
> Simon|||Tibor thanks
Is there a way of telling what free space is in the log file. I sort of
understand the "bucket" route now :-)
I am guessing that if the log file size was 2GB in size, it does NOT mean
that SQL would use that - so in other words, the bucket may only contain 1GB
of data. Whats the best way of finding out?
Will check the link out as well. I apprecaite that a 3rd party tool is doing
the backup, but it sounds like I may be on the right track - the only concern
I had is the log file does not shrink. but if the query I showed below is
run, then the file is reduced down in size.
Simon
"Tibor Karaszi" wrote:
> Consider a log file a bucket. As modifications are performed, the bucket is filled. It is only
> emptied when you BACKUP LOG, not for BACKUP DATABASE. Unless you have the database in simple
> recovery mode, when you will get an error message if you do BACKUP LOG. This bucket can grow in size
> by SQL Server if it becomes full and modifications are performed (autogrow).
> So, one reason for large log files is that you never did backup log. You now did it, and that log
> backup was probably pretty big and you should now have a lot of empty space in the log file. This
> can be a valid situation for shrinking the log file. See
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> But, since you use some 3:rd party tool to do the backup, we don't know what command (like BACKUP
> LOG) was submitted. I'd run a Profiler trace to see what backup command is submitted by that app.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:CEEAEBF3-D30B-476B-B248-D0A93510139C@.microsoft.com...
> > Hello All
> > I should start by saying, I am not a SQL guru at all!
> > We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
> > NetBackup 5.1 MP5 using an online SQL Agent.
> >
> > I will start with the good news, in that all FULL Backups work fine! I have
> > discovered that the Transaction logs are NOT included in this FULL Backup.
> >
> > In NetBackup there is an option to "Backup and Truncate the logs" - so I
> > attempted to do this, and it claims it worked fine (the Application log also
> > shows event id 18265 and the description gives an indication "log was backed
> > up Database ect and ending in "No user action is required".
> >
> > Great! but here is the problem. The .ldf files are growing WAY out of
> > control. For example, they can grow to over 40GB in a week !
> >
> > I know there is a query or a SHRINK command that can be used to help, but I
> > am being told that NetBackup should be "truncating" the log down. I guess
> > this means shrinking.
> >
> > Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
> > 2005 Administrator has to manually shrink the logs or run a query to do this?
> >
> > Or could something be setup wrong in SQL2005.
> >
> > Any help is warmly appreciated.
> > Thank you
> > Simon
>|||Tibor thanks
Is there a way of telling what free space is in the log file. I sort of
understand the "bucket" route now :-)
I am guessing that if the log file size was 2GB in size, it does NOT mean
that SQL would use that - so in other words, the bucket may only contain 1GB
of data. Whats the best way of finding out?
Will check the link out as well. I apprecaite that a 3rd party tool is doing
the backup, but it sounds like I may be on the right track - the only concern
I had is the log file does not shrink. but if the query I showed below is
run, then the file is reduced down in size.
Simon
"Tibor Karaszi" wrote:
> Consider a log file a bucket. As modifications are performed, the bucket is filled. It is only
> emptied when you BACKUP LOG, not for BACKUP DATABASE. Unless you have the database in simple
> recovery mode, when you will get an error message if you do BACKUP LOG. This bucket can grow in size
> by SQL Server if it becomes full and modifications are performed (autogrow).
> So, one reason for large log files is that you never did backup log. You now did it, and that log
> backup was probably pretty big and you should now have a lot of empty space in the log file. This
> can be a valid situation for shrinking the log file. See
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> But, since you use some 3:rd party tool to do the backup, we don't know what command (like BACKUP
> LOG) was submitted. I'd run a Profiler trace to see what backup command is submitted by that app.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:CEEAEBF3-D30B-476B-B248-D0A93510139C@.microsoft.com...
> > Hello All
> > I should start by saying, I am not a SQL guru at all!
> > We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
> > NetBackup 5.1 MP5 using an online SQL Agent.
> >
> > I will start with the good news, in that all FULL Backups work fine! I have
> > discovered that the Transaction logs are NOT included in this FULL Backup.
> >
> > In NetBackup there is an option to "Backup and Truncate the logs" - so I
> > attempted to do this, and it claims it worked fine (the Application log also
> > shows event id 18265 and the description gives an indication "log was backed
> > up Database ect and ending in "No user action is required".
> >
> > Great! but here is the problem. The .ldf files are growing WAY out of
> > control. For example, they can grow to over 40GB in a week !
> >
> > I know there is a query or a SHRINK command that can be used to help, but I
> > am being told that NetBackup should be "truncating" the log down. I guess
> > this means shrinking.
> >
> > Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
> > 2005 Administrator has to manually shrink the logs or run a query to do this?
> >
> > Or could something be setup wrong in SQL2005.
> >
> > Any help is warmly appreciated.
> > Thank you
> > Simon
>|||> Is there a way of telling what free space is in the log file.
Sure.:
DBCC SQLPERF(LOGSPACE)
> I am guessing that if the log file size was 2GB in size, it does NOT mean
> that SQL would use that - so in other words, the bucket may only contain 1GB
> of data. Whats the best way of finding out?
Correct thinking. Use above command.
So, when MS is using the term "truncating", I prefer to say "emptying". This is not the same and
shrinking the file size. Here's an elaboration on the "bucket" analogy:
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:300F61B2-2E71-4252-AFEA-DAE45567F14F@.microsoft.com...
> Tibor thanks
> Is there a way of telling what free space is in the log file. I sort of
> understand the "bucket" route now :-)
> I am guessing that if the log file size was 2GB in size, it does NOT mean
> that SQL would use that - so in other words, the bucket may only contain 1GB
> of data. Whats the best way of finding out?
> Will check the link out as well. I apprecaite that a 3rd party tool is doing
> the backup, but it sounds like I may be on the right track - the only concern
> I had is the log file does not shrink. but if the query I showed below is
> run, then the file is reduced down in size.
> Simon
> "Tibor Karaszi" wrote:
>> Consider a log file a bucket. As modifications are performed, the bucket is filled. It is only
>> emptied when you BACKUP LOG, not for BACKUP DATABASE. Unless you have the database in simple
>> recovery mode, when you will get an error message if you do BACKUP LOG. This bucket can grow in
>> size
>> by SQL Server if it becomes full and modifications are performed (autogrow).
>> So, one reason for large log files is that you never did backup log. You now did it, and that log
>> backup was probably pretty big and you should now have a lot of empty space in the log file. This
>> can be a valid situation for shrinking the log file. See
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
>> But, since you use some 3:rd party tool to do the backup, we don't know what command (like BACKUP
>> LOG) was submitted. I'd run a Profiler trace to see what backup command is submitted by that app.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Simon" <Simon@.discussions.microsoft.com> wrote in message
>> news:CEEAEBF3-D30B-476B-B248-D0A93510139C@.microsoft.com...
>> > Hello All
>> > I should start by saying, I am not a SQL guru at all!
>> > We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
>> > NetBackup 5.1 MP5 using an online SQL Agent.
>> >
>> > I will start with the good news, in that all FULL Backups work fine! I have
>> > discovered that the Transaction logs are NOT included in this FULL Backup.
>> >
>> > In NetBackup there is an option to "Backup and Truncate the logs" - so I
>> > attempted to do this, and it claims it worked fine (the Application log also
>> > shows event id 18265 and the description gives an indication "log was backed
>> > up Database ect and ending in "No user action is required".
>> >
>> > Great! but here is the problem. The .ldf files are growing WAY out of
>> > control. For example, they can grow to over 40GB in a week !
>> >
>> > I know there is a query or a SHRINK command that can be used to help, but I
>> > am being told that NetBackup should be "truncating" the log down. I guess
>> > this means shrinking.
>> >
>> > Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
>> > 2005 Administrator has to manually shrink the logs or run a query to do this?
>> >
>> > Or could something be setup wrong in SQL2005.
>> >
>> > Any help is warmly appreciated.
>> > Thank you
>> > Simon
>>|||Thank you! I think I understand a bit better now!
Any recommendations on a 2005 SQL book? Microsoft one perhaps or do you have
another recommendation?
Thanks
"Tibor Karaszi" wrote:
> > Is there a way of telling what free space is in the log file.
> Sure.:
> DBCC SQLPERF(LOGSPACE)
>
> > I am guessing that if the log file size was 2GB in size, it does NOT mean
> > that SQL would use that - so in other words, the bucket may only contain 1GB
> > of data. Whats the best way of finding out?
> Correct thinking. Use above command.
> So, when MS is using the term "truncating", I prefer to say "emptying". This is not the same and
> shrinking the file size. Here's an elaboration on the "bucket" analogy:
> http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:300F61B2-2E71-4252-AFEA-DAE45567F14F@.microsoft.com...
> > Tibor thanks
> > Is there a way of telling what free space is in the log file. I sort of
> > understand the "bucket" route now :-)
> >
> > I am guessing that if the log file size was 2GB in size, it does NOT mean
> > that SQL would use that - so in other words, the bucket may only contain 1GB
> > of data. Whats the best way of finding out?
> >
> > Will check the link out as well. I apprecaite that a 3rd party tool is doing
> > the backup, but it sounds like I may be on the right track - the only concern
> > I had is the log file does not shrink. but if the query I showed below is
> > run, then the file is reduced down in size.
> >
> > Simon
> >
> > "Tibor Karaszi" wrote:
> >
> >> Consider a log file a bucket. As modifications are performed, the bucket is filled. It is only
> >> emptied when you BACKUP LOG, not for BACKUP DATABASE. Unless you have the database in simple
> >> recovery mode, when you will get an error message if you do BACKUP LOG. This bucket can grow in
> >> size
> >> by SQL Server if it becomes full and modifications are performed (autogrow).
> >>
> >> So, one reason for large log files is that you never did backup log. You now did it, and that log
> >> backup was probably pretty big and you should now have a lot of empty space in the log file. This
> >> can be a valid situation for shrinking the log file. See
> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> >>
> >> But, since you use some 3:rd party tool to do the backup, we don't know what command (like BACKUP
> >> LOG) was submitted. I'd run a Profiler trace to see what backup command is submitted by that app.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> >> news:CEEAEBF3-D30B-476B-B248-D0A93510139C@.microsoft.com...
> >> > Hello All
> >> > I should start by saying, I am not a SQL guru at all!
> >> > We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
> >> > NetBackup 5.1 MP5 using an online SQL Agent.
> >> >
> >> > I will start with the good news, in that all FULL Backups work fine! I have
> >> > discovered that the Transaction logs are NOT included in this FULL Backup.
> >> >
> >> > In NetBackup there is an option to "Backup and Truncate the logs" - so I
> >> > attempted to do this, and it claims it worked fine (the Application log also
> >> > shows event id 18265 and the description gives an indication "log was backed
> >> > up Database ect and ending in "No user action is required".
> >> >
> >> > Great! but here is the problem. The .ldf files are growing WAY out of
> >> > control. For example, they can grow to over 40GB in a week !
> >> >
> >> > I know there is a query or a SHRINK command that can be used to help, but I
> >> > am being told that NetBackup should be "truncating" the log down. I guess
> >> > this means shrinking.
> >> >
> >> > Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
> >> > 2005 Administrator has to manually shrink the logs or run a query to do this?
> >> >
> >> > Or could something be setup wrong in SQL2005.
> >> >
> >> > Any help is warmly appreciated.
> >> > Thank you
> >> > Simon
> >>
> >>
>|||There are so many books out there. First think about what area you want (admin, architecture, some
specific component, programming etc), then browse at various book sites. I've always liked the
"Inside SQL Server" series from MS Press.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:26E40685-B062-4B78-BAB5-FCCCCCB5D5E8@.microsoft.com...
> Thank you! I think I understand a bit better now!
> Any recommendations on a 2005 SQL book? Microsoft one perhaps or do you have
> another recommendation?
> Thanks
> "Tibor Karaszi" wrote:
>> > Is there a way of telling what free space is in the log file.
>> Sure.:
>> DBCC SQLPERF(LOGSPACE)
>>
>> > I am guessing that if the log file size was 2GB in size, it does NOT mean
>> > that SQL would use that - so in other words, the bucket may only contain 1GB
>> > of data. Whats the best way of finding out?
>> Correct thinking. Use above command.
>> So, when MS is using the term "truncating", I prefer to say "emptying". This is not the same and
>> shrinking the file size. Here's an elaboration on the "bucket" analogy:
>> http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Simon" <Simon@.discussions.microsoft.com> wrote in message
>> news:300F61B2-2E71-4252-AFEA-DAE45567F14F@.microsoft.com...
>> > Tibor thanks
>> > Is there a way of telling what free space is in the log file. I sort of
>> > understand the "bucket" route now :-)
>> >
>> > I am guessing that if the log file size was 2GB in size, it does NOT mean
>> > that SQL would use that - so in other words, the bucket may only contain 1GB
>> > of data. Whats the best way of finding out?
>> >
>> > Will check the link out as well. I apprecaite that a 3rd party tool is doing
>> > the backup, but it sounds like I may be on the right track - the only concern
>> > I had is the log file does not shrink. but if the query I showed below is
>> > run, then the file is reduced down in size.
>> >
>> > Simon
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Consider a log file a bucket. As modifications are performed, the bucket is filled. It is only
>> >> emptied when you BACKUP LOG, not for BACKUP DATABASE. Unless you have the database in simple
>> >> recovery mode, when you will get an error message if you do BACKUP LOG. This bucket can grow
>> >> in
>> >> size
>> >> by SQL Server if it becomes full and modifications are performed (autogrow).
>> >>
>> >> So, one reason for large log files is that you never did backup log. You now did it, and that
>> >> log
>> >> backup was probably pretty big and you should now have a lot of empty space in the log file.
>> >> This
>> >> can be a valid situation for shrinking the log file. See
>> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
>> >>
>> >> But, since you use some 3:rd party tool to do the backup, we don't know what command (like
>> >> BACKUP
>> >> LOG) was submitted. I'd run a Profiler trace to see what backup command is submitted by that
>> >> app.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "Simon" <Simon@.discussions.microsoft.com> wrote in message
>> >> news:CEEAEBF3-D30B-476B-B248-D0A93510139C@.microsoft.com...
>> >> > Hello All
>> >> > I should start by saying, I am not a SQL guru at all!
>> >> > We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
>> >> > NetBackup 5.1 MP5 using an online SQL Agent.
>> >> >
>> >> > I will start with the good news, in that all FULL Backups work fine! I have
>> >> > discovered that the Transaction logs are NOT included in this FULL Backup.
>> >> >
>> >> > In NetBackup there is an option to "Backup and Truncate the logs" - so I
>> >> > attempted to do this, and it claims it worked fine (the Application log also
>> >> > shows event id 18265 and the description gives an indication "log was backed
>> >> > up Database ect and ending in "No user action is required".
>> >> >
>> >> > Great! but here is the problem. The .ldf files are growing WAY out of
>> >> > control. For example, they can grow to over 40GB in a week !
>> >> >
>> >> > I know there is a query or a SHRINK command that can be used to help, but I
>> >> > am being told that NetBackup should be "truncating" the log down. I guess
>> >> > this means shrinking.
>> >> >
>> >> > Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
>> >> > 2005 Administrator has to manually shrink the logs or run a query to do this?
>> >> >
>> >> > Or could something be setup wrong in SQL2005.
>> >> >
>> >> > Any help is warmly appreciated.
>> >> > Thank you
>> >> > Simon
>> >>
>> >>

Sunday, March 25, 2012

Basic control errors (transaction)

Thursday, March 22, 2012

Barcode Control

Thursday, March 8, 2012

Backups of Logs - Growing way, way out of control & Need Assistanc

Backup to Network Drive

Blog Archive

About Me