sql login permissions: size

Showing posts with label size. Show all posts

Monday, March 26, 2012

LogSpace Script

I am writing a logspace checking script. For now I just
want it to send me the size of all my log files. But I
am doing something stupid or don't understand cursors
because it's only putting the last database into @.msg3.
If anyone has any ideas what I'm doing wrong, I'd sure
appreciate it as this is driving me crazy!
declare @.server varchar(50)
declare @.subject2 varchar(50)
declare @.msg2 varchar(8000)
declare @.msg3 varchar(8000)
declare @.email_body varchar(8000)
declare @.field1 varchar(100)
declare @.field2 varchar(100)
--declare testint int
set @.server = 'SQL_3'
set @.subject2 = 'LOGSPACE ON ' + @.server + ' FOR DBs WITH
LOG SPACE OVER 2G'
print @.subject2
if exists (select name from tempdb..sysobjects where name
like '#temcmlogspace%')
begin
drop table #temcmlogspace
end
create table #temcmlogspace (dbname varchar(100) null,
logsize2 numeric(16, 6) null,
logusedpcnt numeric(16,6) null, status2 int null)
--The size is in Megs:
insert into #temcmlogspace
exec ('dbcc sqlperf(logspace)')
set @.msg3 = ''
declare email_cursor cursor for
select dbname, logsize2 from #temcmlogspace
open email_cursor
while @.@.fetch_status = 0
fetch next from email_cursor into @.field1, @.field2
begin
set @.msg2 = @.field1 + ' ' + cast(@.field2 as varchar
(20))
set @.msg3 = @.msg3 + @.msg2
print '@.msg2'
print @.msg2
end
close email_cursor
deallocate email_cursor
print '@.msg3'
print @.msg3Is it possible that @.field1 and/or @.field2 is ever NULL?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Alley" <anonymous@.discussions.microsoft.com> wrote in message
news:0c9901c4fe6b$3632a120$a601280a@.phx.gbl...
> I am writing a logspace checking script. For now I just
> want it to send me the size of all my log files. But I
> am doing something stupid or don't understand cursors
> because it's only putting the last database into @.msg3.
> If anyone has any ideas what I'm doing wrong, I'd sure
> appreciate it as this is driving me crazy!
>
> declare @.server varchar(50)
> declare @.subject2 varchar(50)
> declare @.msg2 varchar(8000)
> declare @.msg3 varchar(8000)
> declare @.email_body varchar(8000)
> declare @.field1 varchar(100)
> declare @.field2 varchar(100)
> --declare testint int
> set @.server = 'SQL_3'
> set @.subject2 = 'LOGSPACE ON ' + @.server + ' FOR DBs WITH
> LOG SPACE OVER 2G'
> print @.subject2
> if exists (select name from tempdb..sysobjects where name
> like '#temcmlogspace%')
> begin
> drop table #temcmlogspace
> end
> create table #temcmlogspace (dbname varchar(100) null,
> logsize2 numeric(16, 6) null,
> logusedpcnt numeric(16,6) null, status2 int null)
> --The size is in Megs:
> insert into #temcmlogspace
> exec ('dbcc sqlperf(logspace)')
> set @.msg3 = ''
> declare email_cursor cursor for
> select dbname, logsize2 from #temcmlogspace
> open email_cursor
> while @.@.fetch_status = 0
> fetch next from email_cursor into @.field1, @.field2
> begin
> set @.msg2 = @.field1 + ' ' + cast(@.field2 as varchar
> (20))
> set @.msg3 = @.msg3 + @.msg2
> print '@.msg2'
> print @.msg2
> end
> close email_cursor
> deallocate email_cursor
> print '@.msg3'
> print @.msg3
>
>|||Shouldn't be: it's taking the results from DBCC
Logspace. Why?
>--Original Message--
>Is it possible that @.field1 and/or @.field2 is ever NULL?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Alley" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0c9901c4fe6b$3632a120$a601280a@.phx.gbl...
>> I am writing a logspace checking script. For now I
just
>> want it to send me the size of all my log files. But I
>> am doing something stupid or don't understand cursors
>> because it's only putting the last database into @.msg3.
>> If anyone has any ideas what I'm doing wrong, I'd sure
>> appreciate it as this is driving me crazy!
>>
>> declare @.server varchar(50)
>> declare @.subject2 varchar(50)
>> declare @.msg2 varchar(8000)
>> declare @.msg3 varchar(8000)
>> declare @.email_body varchar(8000)
>> declare @.field1 varchar(100)
>> declare @.field2 varchar(100)
>> --declare testint int
>> set @.server = 'SQL_3'
>> set @.subject2 = 'LOGSPACE ON ' + @.server + ' FOR DBs
WITH
>> LOG SPACE OVER 2G'
>> print @.subject2
>> if exists (select name from tempdb..sysobjects where
name
>> like '#temcmlogspace%')
>> begin
>> drop table #temcmlogspace
>> end
>> create table #temcmlogspace (dbname varchar(100) null,
>> logsize2 numeric(16, 6) null,
>> logusedpcnt numeric(16,6) null, status2 int null)
>> --The size is in Megs:
>> insert into #temcmlogspace
>> exec ('dbcc sqlperf(logspace)')
>> set @.msg3 = ''
>> declare email_cursor cursor for
>> select dbname, logsize2 from #temcmlogspace
>> open email_cursor
>> while @.@.fetch_status = 0
>> fetch next from email_cursor into @.field1, @.field2
>> begin
>> set @.msg2 = @.field1 + ' ' + cast(@.field2 as varchar
>> (20))
>> set @.msg3 = @.msg3 + @.msg2
>> print '@.msg2'
>> print @.msg2
>> end
>> close email_cursor
>> deallocate email_cursor
>> print '@.msg3'
>> print @.msg3
>>
>>
>
>.
>|||Well, if you set @.msg3 = @.msg3 + NULL, guess what happens?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Alley" <anonymous@.discussions.microsoft.com> wrote in message
news:1c6201c4fe6f$54022c30$a501280a@.phx.gbl...
> Shouldn't be: it's taking the results from DBCC
> Logspace. Why?
> >--Original Message--
> >Is it possible that @.field1 and/or @.field2 is ever NULL?
> >
> >--
> >http://www.aspfaq.com/
> >(Reverse address to reply.)
> >
> >
> >
> >
> >"Alley" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0c9901c4fe6b$3632a120$a601280a@.phx.gbl...
> >> I am writing a logspace checking script. For now I
> just
> >> want it to send me the size of all my log files. But I
> >> am doing something stupid or don't understand cursors
> >> because it's only putting the last database into @.msg3.
> >> If anyone has any ideas what I'm doing wrong, I'd sure
> >> appreciate it as this is driving me crazy!
> >>
> >>
> >> declare @.server varchar(50)
> >> declare @.subject2 varchar(50)
> >> declare @.msg2 varchar(8000)
> >> declare @.msg3 varchar(8000)
> >> declare @.email_body varchar(8000)
> >> declare @.field1 varchar(100)
> >> declare @.field2 varchar(100)
> >> --declare testint int
> >>
> >> set @.server = 'SQL_3'
> >> set @.subject2 = 'LOGSPACE ON ' + @.server + ' FOR DBs
> WITH
> >> LOG SPACE OVER 2G'
> >> print @.subject2
> >> if exists (select name from tempdb..sysobjects where
> name
> >> like '#temcmlogspace%')
> >> begin
> >> drop table #temcmlogspace
> >> end
> >> create table #temcmlogspace (dbname varchar(100) null,
> >> logsize2 numeric(16, 6) null,
> >> logusedpcnt numeric(16,6) null, status2 int null)
> >> --The size is in Megs:
> >> insert into #temcmlogspace
> >> exec ('dbcc sqlperf(logspace)')
> >> set @.msg3 = ''
> >> declare email_cursor cursor for
> >> select dbname, logsize2 from #temcmlogspace
> >> open email_cursor
> >>
> >> while @.@.fetch_status = 0
> >> fetch next from email_cursor into @.field1, @.field2
> >> begin
> >> set @.msg2 = @.field1 + ' ' + cast(@.field2 as varchar
> >> (20))
> >> set @.msg3 = @.msg3 + @.msg2
> >> print '@.msg2'
> >> print @.msg2
> >> end
> >> close email_cursor
> >> deallocate email_cursor
> >> print '@.msg3'
> >> print @.msg3
> >>
> >>
> >>
> >>
> >
> >
> >.
> >|||Thx for the help, but I figured out what was wrong. I
had the incorrect order. This script works:
declare @.server varchar(50)
declare @.subject2 varchar(50)
declare @.msg2 varchar(8000)
declare @.msg3 varchar(8000)
declare @.email_body varchar(8000)
declare @.field1 varchar(100)
declare @.field2 numeric(16, 6)
--declare testint int
set @.server = 'SQL_3'
set @.subject2 = 'LOGSPACE ON ' + @.server + ' FOR DBs WITH
LOG SPACE OVER 2G'
print @.subject2
if exists (select name from tempdb..sysobjects where name
like '#temcmlogspace%')
begin
drop table #temcmlogspace
end
create table #temcmlogspace (dbname varchar(100) null,
logsize2 numeric(16, 6) null,
logusedpcnt numeric(16,6) null, status2 int null)
--The size is in Megs:
insert into #temcmlogspace
exec ('dbcc sqlperf(logspace)')
--select * from #temcmlogspace
set @.msg3 = ''
declare email_cursor cursor for
select dbname, logsize2 from #temcmlogspace
open email_cursor
while @.@.fetch_status = 0
begin
fetch next from email_cursor into @.field1, @.field2
set @.msg2 = @.field1 + ' ' + cast(@.field2 as varchar
(20))
set @.msg3 = @.msg3 + @.msg2
--print '@.msg2'
--print @.msg2
end
close email_cursor
deallocate email_cursor
--print '@.msg3'
--print @.msg3
>--Original Message--
>Well, if you set @.msg3 = @.msg3 + NULL, guess what
happens?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Alley" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1c6201c4fe6f$54022c30$a501280a@.phx.gbl...
>> Shouldn't be: it's taking the results from DBCC
>> Logspace. Why?
>> >--Original Message--
>> >Is it possible that @.field1 and/or @.field2 is ever
NULL?
>> >
>> >--
>> >http://www.aspfaq.com/
>> >(Reverse address to reply.)
>> >
>> >
>> >
>> >
>> >"Alley" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:0c9901c4fe6b$3632a120$a601280a@.phx.gbl...
>> >> I am writing a logspace checking script. For now I
>> just
>> >> want it to send me the size of all my log files.
But I
>> >> am doing something stupid or don't understand
cursors
>> >> because it's only putting the last database into
@.msg3.
>> >> If anyone has any ideas what I'm doing wrong, I'd
sure
>> >> appreciate it as this is driving me crazy!
>> >>
>> >>
>> >> declare @.server varchar(50)
>> >> declare @.subject2 varchar(50)
>> >> declare @.msg2 varchar(8000)
>> >> declare @.msg3 varchar(8000)
>> >> declare @.email_body varchar(8000)
>> >> declare @.field1 varchar(100)
>> >> declare @.field2 varchar(100)
>> >> --declare testint int
>> >>
>> >> set @.server = 'SQL_3'
>> >> set @.subject2 = 'LOGSPACE ON ' + @.server + ' FOR DBs
>> WITH
>> >> LOG SPACE OVER 2G'
>> >> print @.subject2
>> >> if exists (select name from tempdb..sysobjects where
>> name
>> >> like '#temcmlogspace%')
>> >> begin
>> >> drop table #temcmlogspace
>> >> end
>> >> create table #temcmlogspace (dbname varchar(100)
null,
>> >> logsize2 numeric(16, 6) null,
>> >> logusedpcnt numeric(16,6) null, status2 int null)
>> >> --The size is in Megs:
>> >> insert into #temcmlogspace
>> >> exec ('dbcc sqlperf(logspace)')
>> >> set @.msg3 = ''
>> >> declare email_cursor cursor for
>> >> select dbname, logsize2 from #temcmlogspace
>> >> open email_cursor
>> >>
>> >> while @.@.fetch_status = 0
>> >> fetch next from email_cursor into @.field1, @.field2
>> >> begin
>> >> set @.msg2 = @.field1 + ' ' + cast(@.field2 as
varchar
>> >> (20))
>> >> set @.msg3 = @.msg3 + @.msg2
>> >> print '@.msg2'
>> >> print @.msg2
>> >> end
>> >> close email_cursor
>> >> deallocate email_cursor
>> >> print '@.msg3'
>> >> print @.msg3
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>

Log-size

Hi
MSSQLServer 7.
The size of a database (in production) log is getting
close to 9 gigabyte. It seems that everything is being
kept.
What is the best and safest way to reduce size of the
log? Of course I need to be sure that no data is getting
lost.
Tia
KlausAre you performing regular transaction log backups? If not, set "truncate log on checkpoint". As for
shrinking the physical files size, see the articles at the middle of this article:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Klaus" <anonymous@.discussions.microsoft.com> wrote in message
news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> Hi
> MSSQLServer 7.
> The size of a database (in production) log is getting
> close to 9 gigabyte. It seems that everything is being
> kept.
> What is the best and safest way to reduce size of the
> log? Of course I need to be sure that no data is getting
> lost.
> Tia
> Klaus|||backup transaction log with truncate only param.
"Klaus" <anonymous@.discussions.microsoft.com> wrote in message
news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> Hi
> MSSQLServer 7.
> The size of a database (in production) log is getting
> close to 9 gigabyte. It seems that everything is being
> kept.
> What is the best and safest way to reduce size of the
> log? Of course I need to be sure that no data is getting
> lost.
> Tia
> Klaus|||That will not shrink the physical file size, and also it will break a log backup sequence...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:OltB2gMwEHA.2908@.tk2msftngp13.phx.gbl...
> backup transaction log with truncate only param.
>
> "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> > Hi
> >
> > MSSQLServer 7.
> >
> > The size of a database (in production) log is getting
> > close to 9 gigabyte. It seems that everything is being
> > kept.
> >
> > What is the best and safest way to reduce size of the
> > log? Of course I need to be sure that no data is getting
> > lost.
> >
> > Tia
> > Klaus
>|||Hi lads
Thanks a lot for your quick answers.
I will read through the articles.
Klaus
>--Original Message--
>Are you performing regular transaction log backups? If
not, set "truncate log on checkpoint". As for
>shrinking the physical files size, see the articles at
the middle of this article:
>http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Klaus" <anonymous@.discussions.microsoft.com> wrote in
message
>news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
>> Hi
>> MSSQLServer 7.
>> The size of a database (in production) log is getting
>> close to 9 gigabyte. It seems that everything is being
>> kept.
>> What is the best and safest way to reduce size of the
>> log? Of course I need to be sure that no data is
getting
>> lost.
>> Tia
>> Klaus
>
>.
>|||Dear Tibor;
I ave to talk to u about quality Learning Pape.
Is it possible ? U can find me from melihsarica(at)Hotmail
thnx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uAB3guMwEHA.2540@.TK2MSFTNGP09.phx.gbl...
> That will not shrink the physical file size, and also it will break a log
backup sequence...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> news:OltB2gMwEHA.2908@.tk2msftngp13.phx.gbl...
> > backup transaction log with truncate only param.
> >
> >
> >
> > "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> > news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> > > Hi
> > >
> > > MSSQLServer 7.
> > >
> > > The size of a database (in production) log is getting
> > > close to 9 gigabyte. It seems that everything is being
> > > kept.
> > >
> > > What is the best and safest way to reduce size of the
> > > log? Of course I need to be sure that no data is getting
> > > lost.
> > >
> > > Tia
> > > Klaus
> >
> >
>|||Sure. Mail sent... Let me know I you didn't receive it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:u$F3qINwEHA.1512@.TK2MSFTNGP12.phx.gbl...
> Dear Tibor;
> I ave to talk to u about quality Learning Pape.
> Is it possible ? U can find me from melihsarica(at)Hotmail
>
> thnx
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uAB3guMwEHA.2540@.TK2MSFTNGP09.phx.gbl...
> > That will not shrink the physical file size, and also it will break a log
> backup sequence...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> > news:OltB2gMwEHA.2908@.tk2msftngp13.phx.gbl...
> > > backup transaction log with truncate only param.
> > >
> > >
> > >
> > > "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> > > news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> > > > Hi
> > > >
> > > > MSSQLServer 7.
> > > >
> > > > The size of a database (in production) log is getting
> > > > close to 9 gigabyte. It seems that everything is being
> > > > kept.
> > > >
> > > > What is the best and safest way to reduce size of the
> > > > log? Of course I need to be sure that no data is getting
> > > > lost.
> > > >
> > > > Tia
> > > > Klaus
> > >
> > >
> >
> >
>|||Tibor, not to offend you, but I'm suprised at your response. Are you really
an MVP?
The poster said,
"What is the best and safest way to reduce size of the log? Of course I
need to be sure that no data is getting lost."
Setting the "trunc. log on checkpoint" will not help to protect the user's
data.
You also made a comment about "breaking the transaction log chain" in
response to another respondent's suggestion that this user BACKUP LOG ...
WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to the
transaction log chain?
Look, Klaus, what to do depends on what level of recoverability you need to
provide the users of this database. This ranges from none (in the case that
this is some sort of warehouse or mart were the database could be rebuilt
from the other primary data sources) to full point in time recovery (for
sensitive, transaction level OLTP recovery).
The fact that your transaction log keeps growing means you have not set up
your recovery model correctly.
In order to assist you, we will need to know what the user's requirements
are in terms of recovering this database in some sort of disaster scenario.
Another word of caution, newsgroups have respondendents of all skill levels.
Do not ever take anyone's recommendation without careful consideration. At
the end of the day, you are the one still responsible for the integrity and
availability of your system.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:
> Are you performing regular transaction log backups? If not, set "truncate log on checkpoint". As for
> shrinking the physical files size, see the articles at the middle of this article:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> > Hi
> >
> > MSSQLServer 7.
> >
> > The size of a database (in production) log is getting
> > close to 9 gigabyte. It seems that everything is being
> > kept.
> >
> > What is the best and safest way to reduce size of the
> > log? Of course I need to be sure that no data is getting
> > lost.
> >
> > Tia
> > Klaus
>
>|||Anthony, (Thomas?)
Let me quote my earlier reply:
"Are you performing regular transaction log backups? If not, set "truncate log on checkpoint". "
And you said:
> "What is the best and safest way to reduce size of the log? Of course I
> need to be sure that no data is getting lost."
> Setting the "trunc. log on checkpoint" will not help to protect the user's
> data.
If you re-read my statement, I fail to see what is bad advice or incorrect in my advice. If you
don't do transaction log backups, the log will expand to finally fill the disk. I've been on the
newsgroups since 1997 regularly and we see posts all the time where users aren't doing transaction
log backups but still run the database in full recovery mode (which in 7.0 is the same as truncate
log on checkpoint is set to off). End result: full disks; panic. And on top of that, bad advices to
stop SQL server and delete the transaction log files. Suspect database, no backup. Etc.
I.e., if you don't find it necessary to do log backup, you are best suited to let SQL server empty
the transaction log for you. If you don't agree with this, I would like to hear exactly with what
you disagree.
You also said:
> You also made a comment about "breaking the transaction log chain" in
> response to another respondent's suggestion that this user BACKUP LOG ...
> WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to the
> transaction log chain?
The difference is, again, that I qualified my statement with "If you don't do transaction log
backups". The risk of blindly running BACKUP LOG ... WITH NO_LOG is that there might be a log backup
schedule in place and running this statement will break such a log backup chain.
You also stated:
> The fact that your transaction log keeps growing means you have not set up
> your recovery model correctly.
Recovery model were introduced in SQL Server 2000. I realize that it just was a typo from your side,
but I just want to make it clear to the OP that the thing to look for in SQL Server 7.0 (and
earlier) is not "recovery model" in Books Online, but instead look for the database option "truncate
log on checkpoint".
> Another word of caution, newsgroups have respondendents of all skill levels.
> Do not ever take anyone's recommendation without careful consideration. At
> the end of the day, you are the one still responsible for the integrity and
> availability of your system.
This I absolutely agree with. Communication over the newsgroup is a bit dangerous as those who post
questions don't know the ones who reply and the ones who are posting the replies don't know much
about the OP's requirements. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:3655BD9E-F4C8-4D7B-AE2F-FB72593E0F7D@.microsoft.com...
> Tibor, not to offend you, but I'm suprised at your response. Are you really
> an MVP?
> The poster said,
> "What is the best and safest way to reduce size of the log? Of course I
> need to be sure that no data is getting lost."
> Setting the "trunc. log on checkpoint" will not help to protect the user's
> data.
> You also made a comment about "breaking the transaction log chain" in
> response to another respondent's suggestion that this user BACKUP LOG ...
> WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to the
> transaction log chain?
> Look, Klaus, what to do depends on what level of recoverability you need to
> provide the users of this database. This ranges from none (in the case that
> this is some sort of warehouse or mart were the database could be rebuilt
> from the other primary data sources) to full point in time recovery (for
> sensitive, transaction level OLTP recovery).
> The fact that your transaction log keeps growing means you have not set up
> your recovery model correctly.
> In order to assist you, we will need to know what the user's requirements
> are in terms of recovering this database in some sort of disaster scenario.
> Another word of caution, newsgroups have respondendents of all skill levels.
> Do not ever take anyone's recommendation without careful consideration. At
> the end of the day, you are the one still responsible for the integrity and
> availability of your system.
> Sincerely,
>
> Anthony Thomas
>
> "Tibor Karaszi" wrote:
> > Are you performing regular transaction log backups? If not, set "truncate log on checkpoint". As
for
> > shrinking the physical files size, see the articles at the middle of this article:
> > http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> > news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> > > Hi
> > >
> > > MSSQLServer 7.
> > >
> > > The size of a database (in production) log is getting
> > > close to 9 gigabyte. It seems that everything is being
> > > kept.
> > >
> > > What is the best and safest way to reduce size of the
> > > log? Of course I need to be sure that no data is getting
> > > lost.
> > >
> > > Tia
> > > Klaus
> >
> >
> >|||Ah, there's the rub. And yes, although I prefaced it, I did offend you. My
apoligies. I did not intend to attack your credentials.
Here is my problem--only because I see it offered on the threads all too
often. The user had an immediate problem: their transaction log file(s) were
full. Now, you and I know that was because the "trunc. log on checkpoint"
was not set, for version 7.0, or they were running in Bulk Insert or Full
recovery, for version 2000.
Now, what I see too often is the advice you gave--actually, I see the other
respondents answer more often (BACKUP LOG xxxx WITH NO_LOG). But the point
is not whether or not they are doing transaction log backups but, rather,
SHOULD THEY.
You nor I can tell without knowing the business requirements. The
transaction log is gold. It is their only database modification audit
history. It is more valuable than the online database. The online database
can be blown up, the server can crash, the disks can explode; however, with
at least one FULL backup and the the entire transaction log record, via
periodic transaction log backups, I can recover that database from the FULL
to any point in time since the very first time that database came online.
The user has too choices: change the recovery model to support their
activity, or conduct activity that supports the users' recovery requirements.
The correct response was the one I gave:
"The fact that your transaction log keeps growing means you have not set up
your recovery model correctly.
In order to assist you, we will need to know what the user's requirements
are in terms of recovering this database in some sort of disaster scenario."
My point was not that your suggestion was incorrect, but, without qualifying
the systems requirements, it would be impossible for anyone to give an
appropriate response.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:
> Anthony, (Thomas?)
> Let me quote my earlier reply:
> "Are you performing regular transaction log backups? If not, set "truncate log on checkpoint". "
> And you said:
> > "What is the best and safest way to reduce size of the log? Of course I
> > need to be sure that no data is getting lost."
> >
> > Setting the "trunc. log on checkpoint" will not help to protect the user's
> > data.
> If you re-read my statement, I fail to see what is bad advice or incorrect in my advice. If you
> don't do transaction log backups, the log will expand to finally fill the disk. I've been on the
> newsgroups since 1997 regularly and we see posts all the time where users aren't doing transaction
> log backups but still run the database in full recovery mode (which in 7.0 is the same as truncate
> log on checkpoint is set to off). End result: full disks; panic. And on top of that, bad advices to
> stop SQL server and delete the transaction log files. Suspect database, no backup. Etc.
> I.e., if you don't find it necessary to do log backup, you are best suited to let SQL server empty
> the transaction log for you. If you don't agree with this, I would like to hear exactly with what
> you disagree.
> You also said:
> > You also made a comment about "breaking the transaction log chain" in
> > response to another respondent's suggestion that this user BACKUP LOG ...
> > WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to the
> > transaction log chain?
> The difference is, again, that I qualified my statement with "If you don't do transaction log
> backups". The risk of blindly running BACKUP LOG ... WITH NO_LOG is that there might be a log backup
> schedule in place and running this statement will break such a log backup chain.
> You also stated:
> > The fact that your transaction log keeps growing means you have not set up
> > your recovery model correctly.
> Recovery model were introduced in SQL Server 2000. I realize that it just was a typo from your side,
> but I just want to make it clear to the OP that the thing to look for in SQL Server 7.0 (and
> earlier) is not "recovery model" in Books Online, but instead look for the database option "truncate
> log on checkpoint".
> > Another word of caution, newsgroups have respondendents of all skill levels.
> > Do not ever take anyone's recommendation without careful consideration. At
> > the end of the day, you are the one still responsible for the integrity and
> > availability of your system.
> This I absolutely agree with. Communication over the newsgroup is a bit dangerous as those who post
> questions don't know the ones who reply and the ones who are posting the replies don't know much
> about the OP's requirements. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
> news:3655BD9E-F4C8-4D7B-AE2F-FB72593E0F7D@.microsoft.com...
> > Tibor, not to offend you, but I'm suprised at your response. Are you really
> > an MVP?
> >
> > The poster said,
> >
> > "What is the best and safest way to reduce size of the log? Of course I
> > need to be sure that no data is getting lost."
> >
> > Setting the "trunc. log on checkpoint" will not help to protect the user's
> > data.
> >
> > You also made a comment about "breaking the transaction log chain" in
> > response to another respondent's suggestion that this user BACKUP LOG ...
> > WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to the
> > transaction log chain?
> >
> > Look, Klaus, what to do depends on what level of recoverability you need to
> > provide the users of this database. This ranges from none (in the case that
> > this is some sort of warehouse or mart were the database could be rebuilt
> > from the other primary data sources) to full point in time recovery (for
> > sensitive, transaction level OLTP recovery).
> >
> > The fact that your transaction log keeps growing means you have not set up
> > your recovery model correctly.
> >
> > In order to assist you, we will need to know what the user's requirements
> > are in terms of recovering this database in some sort of disaster scenario.
> >
> > Another word of caution, newsgroups have respondendents of all skill levels.
> > Do not ever take anyone's recommendation without careful consideration. At
> > the end of the day, you are the one still responsible for the integrity and
> > availability of your system.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> > > Are you performing regular transaction log backups? If not, set "truncate log on checkpoint". As
> for
> > > shrinking the physical files size, see the articles at the middle of this article:
> > > http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> > > news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> > > > Hi
> > > >
> > > > MSSQLServer 7.
> > > >
> > > > The size of a database (in production) log is getting
> > > > close to 9 gigabyte. It seems that everything is being
> > > > kept.
> > > >
> > > > What is the best and safest way to reduce size of the
> > > > log? Of course I need to be sure that no data is getting
> > > > lost.
> > > >
> > > > Tia
> > > > Klaus
> > >
> > >
> > >
>
>

Monday, March 26, 2012

LogSpace Script

LogSpace Script

LogSpace Script

Log-size

Log-size

Log-size

sql login permissions

Blog Archive

About Me