Locks and Blocks: databases

Showing posts with label databases. Show all posts

Wednesday, March 28, 2012

Log Full

I am having a problem with one of my databases since the log is getting
rather large dispite regular back up. I have heard that there a extended
stored proceedure that will recycle the log. Anyone have an idea..?
I really appreciate this.
"Recycle The Log" is a term used when talking about the SQL Server Errorlog.
The command in question is sp_cycle_errorlog. To view it use
sp_readerrorlog.
However, neither of these are any good to you as the log you are refering to
is the TransAction Log. There are numerous articles on the subject, here are
a few :-
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
HTH. Ryan
"bluefish" <bluefish@.discussions.microsoft.com> wrote in message
news:32533A4B-FC3A-4536-BD99-BD9BEFEAA1C9@.microsoft.com...
>I am having a problem with one of my databases since the log is getting
> rather large dispite regular back up. I have heard that there a extended
> stored proceedure that will recycle the log. Anyone have an idea..?
> I really appreciate this.
>

Log Full

I am having a problem with one of my databases since the log is getting
rather large dispite regular back up. I have heard that there a extended
stored proceedure that will recycle the log. Anyone have an idea..?
I really appreciate this."Recycle The Log" is a term used when talking about the SQL Server Errorlog.
The command in question is sp_cycle_errorlog. To view it use
sp_readerrorlog.
However, neither of these are any good to you as the log you are refering to
is the TransAction Log. There are numerous articles on the subject, here are
a few :-
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
--
HTH. Ryan
"bluefish" <bluefish@.discussions.microsoft.com> wrote in message
news:32533A4B-FC3A-4536-BD99-BD9BEFEAA1C9@.microsoft.com...
>I am having a problem with one of my databases since the log is getting
> rather large dispite regular back up. I have heard that there a extended
> stored proceedure that will recycle the log. Anyone have an idea..?
> I really appreciate this.
>

Log Full

I am having a problem with one of my databases since the log is getting
rather large dispite regular back up. I have heard that there a extended
stored proceedure that will recycle the log. Anyone have an idea..?
I really appreciate this."Recycle The Log" is a term used when talking about the SQL Server Errorlog.
The command in question is sp_cycle_errorlog. To view it use
sp_readerrorlog.
However, neither of these are any good to you as the log you are refering to
is the TransAction Log. There are numerous articles on the subject, here are
a few :-
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
HTH. Ryan
"bluefish" <bluefish@.discussions.microsoft.com> wrote in message
news:32533A4B-FC3A-4536-BD99-BD9BEFEAA1C9@.microsoft.com...
>I am having a problem with one of my databases since the log is getting
> rather large dispite regular back up. I have heard that there a extended
> stored proceedure that will recycle the log. Anyone have an idea..?
> I really appreciate this.
>sql

log files problem

Hi All,
I have problem with log files on my databases, they grow so much that I have
errors when I backup and restore the data.
The best way that I found to fix this is to detach the db, delete the log
file and attach the data again.
How can I do this programmatically?
Tks in advance
JohnnyThe best way to fix this is to manage the log properly so that it
doesn't grow. If you aren't performing Log backups then set Simple
Recovery mode. On an OLTP system you should not need to allow log files
to grow (disable autogrow) and you should avoid shrinking at all.
David Portas
SQL Server MVP
--|||You can truncate the log file to 1KB using the DBCC command , so do it in a
periodical basis
"JFB" wrote:

> Hi All,
> I have problem with log files on my databases, they grow so much that I ha
ve
> errors when I backup and restore the data.
> The best way that I found to fix this is to detach the db, delete the log
> file and attach the data again.
> How can I do this programmatically?
> Tks in advance
> Johnny
>
>|||Look at "recovering databases-SQL Server" in BOL.
If you don't need transaction logs, put the DB in simple recovery mode.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Renjith" <Renjith@.discussions.microsoft.com> wrote in message
news:601F468D-0398-4048-8301-70332305B33D@.microsoft.com...
> You can truncate the log file to 1KB using the DBCC command , so do it in
> a
> periodical basis
> "JFB" wrote:
>|||Why? Periodically shrinking has di

vantages: http://www.karaszi.com/SQLServer/in...dont_shrink.asp
JFB's problem is probably that db is in full recovery mode and periodic log
backups are not
performed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Renjith" <Renjith@.discussions.microsoft.com> wrote in message
news:601F468D-0398-4048-8301-70332305B33D@.microsoft.com...
> You can truncate the log file to 1KB using the DBCC command , so do it in
a
> periodical basis
> "JFB" wrote:
>

Log Files Growing out of control

Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
Why shrink the logs if they're only going to have to grow again? Meanwhile,
your performance will suffer, since transactions will have to wait while the
log does an auto-grow.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
|||My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe
|||Hi jaylou,
Almost all our databases run in Full mode. Why do you not want to run in
this mode? When you create the Transaction Log, think carefully since it
won't shrink past the size it was originally created at. If you schedule a
TRUNCATE_ONLY then you leave your database in an unrecoverable state
although, I guess if it's over night it's not likely to cause you a problem.
What I suggest is that you put your database into full mode and backup the
log throughout the day which should stop it from growing so uncontrollably
large. Do you know what size you originally set it at? If not you can run a
DBCC shrinkfile how small can you get it? This may well be the original size.
Another option is there maybe something in the application that is not
committing it's jobs properly?
Andrew
"jaylou" wrote:

> Hi All,
> In one of my servers my log files grow uncontrollably. I have set all the
> individual databases to simple mode but the logs still grow. I need to do a
> backup log with truncate only then shrink the files. This is on a daily
> basis at this point. If I set the databases back to full mode and run a
> backup of the trans log in a maintenance plan the files remain the same size.
> Should I schedule a backup log with truncate only and a shrink file on a
> nightly basis? Is there a maintenance plan that would shrink my files back
> to normal?
> TIA,
> Joe
>
|||It really depends on your app. If you have a large transaction, then if you
don't break it down into smaller ones, you're looking at having a large log
file.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe
|||in line with what other NG members have said, I support the breaking down of
the transaction . . however before you can do that you need to know which
process is causing your transaction log to fill up. and grow uncontrolably.
the first thing I would do is is setup a sqlalert to capture percentage log
used . . .based on 75% of( a very large transaction) you can setup a
response (this can be any thing, sqlagent job,email etc.) I would go for a
sqlagent job and the job will run something similar to the following(you do
not have t use this exact sql but just to point you in the right direction)
this will help identify what process is causing your log to grow. on the
other hand you could setup a server side profiler trace using a sql agent
job and sp_trace_setevent etc . . .but this may prove to be laborious
depending on how busy your database is will depend on the volume of data you
have to trawl through to identify the sql causing the problem.
-- Olu Adedeji
-- 12/04/03
-- dump open transaction info
set nocount on
declare @.inputbuffer nvarchar(1000),
@.spid varchar(5),
@.dbname nvarchar(128) -- your database name
set @.dbname = 'Pubs'
-- identify oldest open transactions in the dbname
dbcc opentran(@.dbname)
-- Please note that accessing system tables directly is not supported
-- every effort should be made to refrain from doing this
-- only display inputbuffer for spids with open transaction
if (select count(spid) from master..sysprocesses(nolock)) > 1
begin
declare inputbuffer_cur cursor read_only for
select cast(spid as varchar) from master..sysprocesses(nolock) where
db_name(dbid) = @.dbname and open_tran !=0
open inputbuffer_cur
fetch next from inputbuffer_cur into @.spid
while @.@.fetch_status = 0
begin
select @.inputbuffer = 'dbcc inputbuffer('+@.spid+')'
print @.inputbuffer
exec master..sp_executesql @.inputbuffer
print
'************************************************* ******************'
fetch next from inputbuffer_cur into @.spid
end
close inputbuffer_cur
deallocate inputbuffer_cur
end
else
begin
select @.inputbuffer =(select top 1 ' set nocount on dbcc
inputbuffer('+cast(spid as varchar) + ') ' from master..sysprocesses(nolock)
where db_name(dbid) = @.dbname
and open_tran !=0)
print @.inputbuffer
exec master.dbo.sp_executesql @.inputbuffer
print '************************************************* ******************'
end
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
> My question is...
> Is there a way to stop the log files from growing like this?
> Thanks,
> Joe
|||I would only put databases in full recovery mode if the log backups are
needed. Setting them to simple makes for easier maintenance if you are happy
with full and diff backups - a lot of systems are but people tend to leave
the databases as full because it's the default and they don't think about the
way the database is to be used.
Have a look at
http://www.mindsdoor.net/SQLAdmin/Tr...leGrows_1.html
|||Look, the transaction log will not grow any more in SIMPLE RECOVERY than it
will in the other two recovery modes but it will grow to handle the busiest
and largest single transactions and periods of time. A problem that you may
not have considered is that while in SIMPLE mode, the transaction log is
only flushed out on CHECKPOINT operations. Perhaps, the CHECKPOINT is not
happening frequently enough for your purposes. If the transaction log is
not flushed, through backup, manual or automated purge, it will grow to
accomodate all transactions.
Sincerely,
Anthony Thomas

"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe

Log Files Growing out of control

Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same size
.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
JoeWhy shrink the logs if they're only going to have to grow again? Meanwhile,
your performance will suffer, since transactions will have to wait while the
log does an auto-grow.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe|||My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||Hi jaylou,
Almost all our databases run in Full mode. Why do you not want to run in
this mode? When you create the Transaction Log, think carefully since it
won't shrink past the size it was originally created at. If you schedule a
TRUNCATE_ONLY then you leave your database in an unrecoverable state
although, I guess if it's over night it's not likely to cause you a problem.
What I suggest is that you put your database into full mode and backup the
log throughout the day which should stop it from growing so uncontrollably
large. Do you know what size you originally set it at? If not you can run
a
DBCC shrinkfile how small can you get it? This may well be the original siz
e.
Another option is there maybe something in the application that is not
committing it's jobs properly?
Andrew
"jaylou" wrote:

> Hi All,
> In one of my servers my log files grow uncontrollably. I have set all the
> individual databases to simple mode but the logs still grow. I need to do
a
> backup log with truncate only then shrink the files. This is on a daily
> basis at this point. If I set the databases back to full mode and run a
> backup of the trans log in a maintenance plan the files remain the same si
ze.
> Should I schedule a backup log with truncate only and a shrink file on a
> nightly basis? Is there a maintenance plan that would shrink my files bac
k
> to normal?
> TIA,
> Joe
>|||It really depends on your app. If you have a large transaction, then if you
don't break it down into smaller ones, you're looking at having a large log
file.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||in line with what other NG members have said, I support the breaking down of
the transaction . . however before you can do that you need to know which
process is causing your transaction log to fill up. and grow uncontrolably.
the first thing I would do is is setup a sqlalert to capture percentage log
used . . .based on 75% of( a very large transaction) you can setup a
response (this can be any thing, sqlagent job,email etc.) I would go for a
sqlagent job and the job will run something similar to the following(you do
not have t use this exact sql but just to point you in the right direction)
this will help identify what process is causing your log to grow. on the
other hand you could setup a server side profiler trace using a sql agent
job and sp_trace_setevent etc . . .but this may prove to be laborious
depending on how busy your database is will depend on the volume of data you
have to trawl through to identify the sql causing the problem.
-- Olu Adedeji
-- 12/04/03
-- dump open transaction info
set nocount on
declare @.inputbuffer nvarchar(1000),
@.spid varchar(5),
@.dbname nvarchar(128) -- your database name
set @.dbname = 'Pubs'
-- identify oldest open transactions in the dbname
dbcc opentran(@.dbname)
-- Please note that accessing system tables directly is not supported
-- every effort should be made to refrain from doing this
-- only display inputbuffer for spids with open transaction
if (select count(spid) from master..sysprocesses(nolock)) > 1
begin
declare inputbuffer_cur cursor read_only for
select cast(spid as varchar) from master..sysprocesses(nolock) where
db_name(dbid) = @.dbname and open_tran !=0
open inputbuffer_cur
fetch next from inputbuffer_cur into @.spid
while @.@.fetch_status = 0
begin
select @.inputbuffer = 'dbcc inputbuffer('+@.spid+')'
print @.inputbuffer
exec master..sp_executesql @.inputbuffer
print
'***************************************
****************************'
fetch next from inputbuffer_cur into @.spid
end
close inputbuffer_cur
deallocate inputbuffer_cur
end
else
begin
select @.inputbuffer =(select top 1 ' set nocount on dbcc
inputbuffer('+cast(spid as varchar) + ') ' from master..sysprocesses(nolock)
where db_name(dbid) = @.dbname
and open_tran !=0)
print @.inputbuffer
exec master.dbo.sp_executesql @.inputbuffer
print '***************************************
****************************'
end
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
> My question is...
> Is there a way to stop the log files from growing like this?
> Thanks,
> Joe|||I would only put databases in full recovery mode if the log backups are
needed. Setting them to simple makes for easier maintenance if you are happy
with full and diff backups - a lot of systems are but people tend to leave
the databases as full because it's the default and they don't think about th
e
way the database is to be used.
Have a look at
http://www.mindsdoor.net/SQLAdmin/T...ileGrows_1.html|||Look, the transaction log will not grow any more in SIMPLE RECOVERY than it
will in the other two recovery modes but it will grow to handle the busiest
and largest single transactions and periods of time. A problem that you may
not have considered is that while in SIMPLE mode, the transaction log is
only flushed out on CHECKPOINT operations. Perhaps, the CHECKPOINT is not
happening frequently enough for your purposes. If the transaction log is
not flushed, through backup, manual or automated purge, it will grow to
accomodate all transactions.
Sincerely,
Anthony Thomas
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe

Log Files Growing out of control

Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
JoeWhy shrink the logs if they're only going to have to grow again? Meanwhile,
your performance will suffer, since transactions will have to wait while the
log does an auto-grow.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe|||My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||Hi jaylou,
Almost all our databases run in Full mode. Why do you not want to run in
this mode? When you create the Transaction Log, think carefully since it
won't shrink past the size it was originally created at. If you schedule a
TRUNCATE_ONLY then you leave your database in an unrecoverable state
although, I guess if it's over night it's not likely to cause you a problem.
What I suggest is that you put your database into full mode and backup the
log throughout the day which should stop it from growing so uncontrollably
large. Do you know what size you originally set it at? If not you can run a
DBCC shrinkfile how small can you get it? This may well be the original size.
Another option is there maybe something in the application that is not
committing it's jobs properly?
Andrew
"jaylou" wrote:
> Hi All,
> In one of my servers my log files grow uncontrollably. I have set all the
> individual databases to simple mode but the logs still grow. I need to do a
> backup log with truncate only then shrink the files. This is on a daily
> basis at this point. If I set the databases back to full mode and run a
> backup of the trans log in a maintenance plan the files remain the same size.
> Should I schedule a backup log with truncate only and a shrink file on a
> nightly basis? Is there a maintenance plan that would shrink my files back
> to normal?
> TIA,
> Joe
>|||It really depends on your app. If you have a large transaction, then if you
don't break it down into smaller ones, you're looking at having a large log
file.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||in line with what other NG members have said, I support the breaking down of
the transaction . . however before you can do that you need to know which
process is causing your transaction log to fill up. and grow uncontrolably.
the first thing I would do is is setup a sqlalert to capture percentage log
used . . .based on 75% of( a very large transaction) you can setup a
response (this can be any thing, sqlagent job,email etc.) I would go for a
sqlagent job and the job will run something similar to the following(you do
not have t use this exact sql but just to point you in the right direction)
this will help identify what process is causing your log to grow. on the
other hand you could setup a server side profiler trace using a sql agent
job and sp_trace_setevent etc . . .but this may prove to be laborious
depending on how busy your database is will depend on the volume of data you
have to trawl through to identify the sql causing the problem.
-- Olu Adedeji
-- 12/04/03
-- dump open transaction info
set nocount on
declare @.inputbuffer nvarchar(1000),
@.spid varchar(5),
@.dbname nvarchar(128) -- your database name
set @.dbname = 'Pubs'
-- identify oldest open transactions in the dbname
dbcc opentran(@.dbname)
-- Please note that accessing system tables directly is not supported
-- every effort should be made to refrain from doing this
-- only display inputbuffer for spids with open transaction
if (select count(spid) from master..sysprocesses(nolock)) > 1
begin
declare inputbuffer_cur cursor read_only for
select cast(spid as varchar) from master..sysprocesses(nolock) where
db_name(dbid) = @.dbname and open_tran !=0
open inputbuffer_cur
fetch next from inputbuffer_cur into @.spid
while @.@.fetch_status = 0
begin
select @.inputbuffer = 'dbcc inputbuffer('+@.spid+')'
print @.inputbuffer
exec master..sp_executesql @.inputbuffer
print
'*******************************************************************'
fetch next from inputbuffer_cur into @.spid
end
close inputbuffer_cur
deallocate inputbuffer_cur
end
else
begin
select @.inputbuffer =(select top 1 ' set nocount on dbcc
inputbuffer('+cast(spid as varchar) + ') ' from master..sysprocesses(nolock)
where db_name(dbid) = @.dbname
and open_tran !=0)
print @.inputbuffer
exec master.dbo.sp_executesql @.inputbuffer
print '*******************************************************************'
end
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
> My question is...
> Is there a way to stop the log files from growing like this?
> Thanks,
> Joe|||I would only put databases in full recovery mode if the log backups are
needed. Setting them to simple makes for easier maintenance if you are happy
with full and diff backups - a lot of systems are but people tend to leave
the databases as full because it's the default and they don't think about the
way the database is to be used.
Have a look at
http://www.mindsdoor.net/SQLAdmin/TransactionLogFileGrows_1.html|||Look, the transaction log will not grow any more in SIMPLE RECOVERY than it
will in the other two recovery modes but it will grow to handle the busiest
and largest single transactions and periods of time. A problem that you may
not have considered is that while in SIMPLE mode, the transaction log is
only flushed out on CHECKPOINT operations. Perhaps, the CHECKPOINT is not
happening frequently enough for your purposes. If the transaction log is
not flushed, through backup, manual or automated purge, it will grow to
accomodate all transactions.
Sincerely,
Anthony Thomas
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe

Monday, March 26, 2012

LOG files

Hi,
I have some LOG files, the extension of the file is LOG, and I don't know
where they came from. Some of them are named after my databases, and others
are not. One is almost 4 GB in size. GB is not a typo.
These files are locked, so I have to shut down to view them. Unfortunately,
they are not TXT files, so I can't view them anyway. I tried to rename the
files, but when I try and launch the application that uses SQL, I get an
error message.
Any help on this would be greatly appreciated.In SQL Enterprise manager , right click your database, go to properties,
then select the log tab, it will list the physical names of all of the tran
logs for that database..
"DCIFRTHS" <None> wrote in message
news:#6nh9Rb6DHA.3704@.tk2msftngp13.phx.gbl...
> Hi,
> I have some LOG files, the extension of the file is LOG, and I don't know
> where they came from. Some of them are named after my databases, and
others
> are not. One is almost 4 GB in size. GB is not a typo.
> These files are locked, so I have to shut down to view them.
Unfortunately,
> they are not TXT files, so I can't view them anyway. I tried to rename
the
> files, but when I try and launch the application that uses SQL, I get an
> error message.
> Any help on this would be greatly appreciated.
>|||Okay. I have identified the log files, but I am still a bit confused. Why
are they listed as log files? I thought the transaction logs were the LDF
files?
Do you have any advice for truncating these logs and what settings are best
for them? How do I determine what information, and the date of the
information in these files?
Thanks so much !
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:esOBgDc6DHA.2692@.TK2MSFTNGP09.phx.gbl...
> In SQL Enterprise manager , right click your database, go to properties,
> then select the log tab, it will list the physical names of all of the
tran
> logs for that database..
> "DCIFRTHS" <None> wrote in message
> news:#6nh9Rb6DHA.3704@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > I have some LOG files, the extension of the file is LOG, and I don't
know
> > where they came from. Some of them are named after my databases, and
> others
> > are not. One is almost 4 GB in size. GB is not a typo.
> >
> > These files are locked, so I have to shut down to view them.
> Unfortunately,
> > they are not TXT files, so I can't view them anyway. I tried to rename
> the
> > files, but when I try and launch the application that uses SQL, I get an
> > error message.
> >
> > Any help on this would be greatly appreciated.
> >
> >
>|||The LOG extension is commonly used for ODBC tracing. You'd want to check that (administrative tools,
ODBC).
> Why
> are they listed as log files?
Listed by who? Are then named .LOG? And are really database transaction log files? If so, ask the
person who created the database.
> I thought the transaction logs were the LDF
> files?
That is the recommendation, but you can name the files anyway you want.
> How do I determine what information, and the date of the
> information in these files?
If these are truly transaction log file, they are in binary format. You need 3:rd party app to read
the stuff, like Log Explorer from www.lumigent.com.
> Do you have any advice for truncating these logs and what settings are best
> for them?
Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"DCIFRTHS" <None> wrote in message news:ep3L33c6DHA.1556@.tk2msftngp13.phx.gbl...
> Okay. I have identified the log files, but I am still a bit confused. Why
> are they listed as log files? I thought the transaction logs were the LDF
> files?
> Do you have any advice for truncating these logs and what settings are best
> for them? How do I determine what information, and the date of the
> information in these files?
> Thanks so much !
> "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> news:esOBgDc6DHA.2692@.TK2MSFTNGP09.phx.gbl...
> > In SQL Enterprise manager , right click your database, go to properties,
> > then select the log tab, it will list the physical names of all of the
> tran
> > logs for that database..
> >
> > "DCIFRTHS" <None> wrote in message
> > news:#6nh9Rb6DHA.3704@.tk2msftngp13.phx.gbl...
> > > Hi,
> > >
> > > I have some LOG files, the extension of the file is LOG, and I don't
> know
> > > where they came from. Some of them are named after my databases, and
> > others
> > > are not. One is almost 4 GB in size. GB is not a typo.
> > >
> > > These files are locked, so I have to shut down to view them.
> > Unfortunately,
> > > they are not TXT files, so I can't view them anyway. I tried to rename
> > the
> > > files, but when I try and launch the application that uses SQL, I get an
> > > error message.
> > >
> > > Any help on this would be greatly appreciated.
> > >
> > >
> >
> >
>|||Hi,
I am just checking on your progress regarding the information we provided you. Thanks for
Tibors efforts on your issue and I wonder if we have answered your question. If you
encounter any difficulty, please do not hesitate to let us know. Please post here and let us
know the status of your issue.
Looking forward to hearing from you soon...
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

log files

there is controversial topic in my dept. If we put log files of databases on
different mirrored drive we will improve the performance.
Is that true for system dbs too. What are your reccomendations, guys?
In my oppinion sys db should be secured on different mirrored drive with sql
executables.Eli Milkova wrote:
> there is controversial topic in my dept. If we put log files of
> databases on different mirrored drive we will improve the performance.
> Is that true for system dbs too. What are your reccomendations, guys?
> In my oppinion sys db should be secured on different mirrored drive
> with sql executables.
If by system database you mean tempdb, then the answer is yes. Ideally,
tempdb should be on an array with fast sequential write access (RAID 1
or RAID 10). If you're talking about master, model, and msdb, then the
answer is probably no. You can keep those databases on the array that
holds the rest of the DBs since are generally small and are accessed and
changed with less frequency that user databases. I would not generally
recommend they are on placed the OS array. The OS array is best for the
OS and applications, not database files.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

LOG files

quote:

> Hi,
> I have some LOG files, the extension of the file is LOG, and I don't know
> where they came from. Some of them are named after my databases, and

others

quote:

> are not. One is almost 4 GB in size. GB is not a typo.
> These files are locked, so I have to shut down to view them.

Unfortunately,

quote:

> they are not TXT files, so I can't view them anyway. I tried to rename

the

quote:

> files, but when I try and launch the application that uses SQL, I get an
> error message.
> Any help on this would be greatly appreciated.
>

|||Okay. I have identified the log files, but I am still a bit confused. Why
are they listed as log files? I thought the transaction logs were the LDF
files?
Do you have any advice for truncating these logs and what settings are best
for them? How do I determine what information, and the date of the
information in these files?
Thanks so much !
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:esOBgDc6DHA.2692@.TK2MSFTNGP09.phx.gbl...

quote:

> In SQL Enterprise manager , right click your database, go to properties,
> then select the log tab, it will list the physical names of all of the

tran

quote:

> logs for that database..
> "DCIFRTHS" <None> wrote in message
> news:#6nh9Rb6DHA.3704@.tk2msftngp13.phx.gbl...
know[QUOTE]
> others
> Unfortunately,
> the
>

|||The LOG extension is commonly used for ODBC tracing. You'd want to check tha
t (administrative tools,
ODBC).

quote:

> Why
> are they listed as log files?

Listed by who? Are then named .LOG? And are really database transaction log
files? If so, ask the
person who created the database.

quote:

> I thought the transaction logs were the LDF
> files?

That is the recommendation, but you can name the files anyway you want.

quote:

> How do I determine what information, and the date of the
> information in these files?

If these are truly transaction log file, they are in binary format. You need
3:rd party app to read
the stuff, like Log Explorer from www.lumigent.com.

quote:

> Do you have any advice for truncating these logs and what settings are bes
t
> for them?

Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"DCIFRTHS" <None> wrote in message news:ep3L33c6DHA.1556@.tk2msftngp13.phx.gbl...

quote:
red">
> Okay. I have identified the log files, but I am still a bit confused. Why
> are they listed as log files? I thought the transaction logs were the LDF
> files?
> Do you have any advice for truncating these logs and what settings are bes
t
> for them? How do I determine what information, and the date of the
> information in these files?
> Thanks so much !
> "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> news:esOBgDc6DHA.2692@.TK2MSFTNGP09.phx.gbl...
> tran
> know
>

|||Hi,
I am just checking on your progress regarding the information we provided yo
u. Thanks for
Tibors efforts on your issue and I wonder if we have answered your question.
If you
encounter any difficulty, please do not hesitate to let us know. Please post
here and let us
know the status of your issue.
Looking forward to hearing from you soon...
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

log files

there is controversial topic in my dept. If we put log files of databases o
n
different mirrored drive we will improve the performance.
Is that true for system dbs too. What are your reccomendations, guys?
In my oppinion sys db should be secured on different mirrored drive with sql
executables.Eli Milkova wrote:
> there is controversial topic in my dept. If we put log files of
> databases on different mirrored drive we will improve the performance.
> Is that true for system dbs too. What are your reccomendations, guys?
> In my oppinion sys db should be secured on different mirrored drive
> with sql executables.
If by system database you mean tempdb, then the answer is yes. Ideally,
tempdb should be on an array with fast sequential write access (RAID 1
or RAID 10). If you're talking about master, model, and msdb, then the
answer is probably no. You can keep those databases on the array that
holds the rest of the DBs since are generally small and are accessed and
changed with less frequency that user databases. I would not generally
recommend they are on placed the OS array. The OS array is best for the
OS and applications, not database files.
David Gugick
Quest Software
www.imceda.com
www.quest.com

log files

there is controversial topic in my dept. If we put log files of databases on
different mirrored drive we will improve the performance.
Is that true for system dbs too. What are your reccomendations, guys?
In my oppinion sys db should be secured on different mirrored drive with sql
executables.
Eli Milkova wrote:
> there is controversial topic in my dept. If we put log files of
> databases on different mirrored drive we will improve the performance.
> Is that true for system dbs too. What are your reccomendations, guys?
> In my oppinion sys db should be secured on different mirrored drive
> with sql executables.
If by system database you mean tempdb, then the answer is yes. Ideally,
tempdb should be on an array with fast sequential write access (RAID 1
or RAID 10). If you're talking about master, model, and msdb, then the
answer is probably no. You can keep those databases on the array that
holds the rest of the DBs since are generally small and are accessed and
changed with less frequency that user databases. I would not generally
recommend they are on placed the OS array. The OS array is best for the
OS and applications, not database files.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Friday, March 23, 2012

log file sizes for all databases

Hi All,

I ran the following query to get the log file sizes for all databases:

select (size*8.00)/1024,filename from master..sysaltfiles

When I compared the results from this query with the transaction log properties in EM, not all of the sizes match.
For example, EM shows the transaction log size for tempdb to be 2 MB but the results of the query shows that transaction log size for tempdb is .5 MB. Which query can I run to get the numbers that would match between two? Thanks.I have checked that and find the difference.

Run the below code which gives you the size of tempdb which is as same as physically stored file on your harddisk.

sp_helpdb 'tempdb'

or

select name, filename, (size*8) as size from tempdb..sysfiles

The size in the master..sysaltfiles will helps you to determine the size of files in the tempdb when SQL Server was last started.

Refer the below web page & Sr # 6. RESOURCES

http://www.sqlmag.com/Articles/Print.cfm?ArticleID=39550|||I've ran the statement that you sent me and I am still coming up with wrong number.|||I've ran the statement that you sent me and I am still coming up with wrong number.

The statement I have given will help you to figure out current tempdb log size.

The size in the master..sysaltfiles will help you to determine the size of the files in the tempdb when SQL Server was last started.

Refer the link I have posted above.

LOG File size is 18GB

Hi everyone,
In one of my databases, the size of the LOG has gone upto 18GB. "Truncate
Log" doesn't help. How do I reduce the physical size of the file?
Abhay
Abhay
Perfom BACKUP LOG ..... and then DBCC SHRINKFILE commad (for more details
please refer to BOL)
"Abhay Sobti" <abhay@.stansoftware.com> wrote in message
news:%23OS6JH9nEHA.3668@.TK2MSFTNGP15.phx.gbl...
> Hi everyone,
> In one of my databases, the size of the LOG has gone upto 18GB. "Truncate
> Log" doesn't help. How do I reduce the physical size of the file?
> Abhay
>
|||Enabling the "Truncate log" is just part of the job. You also need to
shrink the file. Check BOL for dbcc shrinkfile.
Adi
|||Fix your clock.
"Abhay Sobti" <abhay@.stansoftware.com> wrote in message
news:%23OS6JH9nEHA.3668@.TK2MSFTNGP15.phx.gbl...
> Hi everyone,
> In one of my databases, the size of the LOG has gone upto 18GB. "Truncate
> Log" doesn't help. How do I reduce the physical size of the file?
> Abhay
>
|||Hi
Do the following tasks:
BACKUP LOG <database name> WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE(<database name>)
Regards
Eli
Abhay Sobti wrote:
> Hi everyone,
> In one of my databases, the size of the LOG has gone upto 18GB. "Truncate
> Log" doesn't help. How do I reduce the physical size of the file?
> Abhay
>

LOG File size is 18GB

Hi everyone,
In one of my databases, the size of the LOG has gone upto 18GB. "Truncate
Log" doesn't help. How do I reduce the physical size of the file?
AbhayAbhay
Perfom BACKUP LOG ..... and then DBCC SHRINKFILE commad (for more details
please refer to BOL)
"Abhay Sobti" <abhay@.stansoftware.com> wrote in message
news:%23OS6JH9nEHA.3668@.TK2MSFTNGP15.phx.gbl...
> Hi everyone,
> In one of my databases, the size of the LOG has gone upto 18GB. "Truncate
> Log" doesn't help. How do I reduce the physical size of the file?
> Abhay
>|||Enabling the "Truncate log" is just part of the job. You also need to
shrink the file. Check BOL for dbcc shrinkfile.
Adi|||Fix your clock.
"Abhay Sobti" <abhay@.stansoftware.com> wrote in message
news:%23OS6JH9nEHA.3668@.TK2MSFTNGP15.phx.gbl...
> Hi everyone,
> In one of my databases, the size of the LOG has gone upto 18GB. "Truncate
> Log" doesn't help. How do I reduce the physical size of the file?
> Abhay
>|||Hi
Do the following tasks:
BACKUP LOG <database name> WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE(<database name>)
Regards
Eli
Abhay Sobti wrote:
> Hi everyone,
> In one of my databases, the size of the LOG has gone upto 18GB. "Truncate
> Log" doesn't help. How do I reduce the physical size of the file?
> Abhay
>

Wednesday, March 21, 2012

Log file is increeasing too much

Hi
i have a database and a proccess which delete everything from database and
then import some stuff from other databases.
how can i reduce to increase log file because sometimes import stops because
of it.
I use the following procedures :
backup log mydatabase with truncate_only
dbcc shrinkdatabase (mydatabase)
i was wondering if it's any possible way to put a maximum size for log file
and when it get to the maximum file size to override the existent logs and
not raise me the error that "The log file for database 'mydatabase' is full.
Back up the transaction log for the database to free up some log space."
thanks in advanceIf you are using the FULL recovery model, you would need to backup the log
before it can be reused. Otherwise, you might consider using the simple
recovery model.
Another possibility is that your import process is done within one
transaction, hence all the data is either committed or rolled back. If this
was the case (and you are using the simple recovery model), you might
consider breaking the import into smaller transactions.
If you are deleting everything initially, you might also consider using the
TRUNCATE TABLE option as the row deletion is not individually logged.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Psycho" <Psycho@.discussions.microsoft.com> wrote in message
news:26E3911D-CEA7-4389-8E26-1E76B3EEDF11@.microsoft.com...
> Hi
> i have a database and a proccess which delete everything from database and
> then import some stuff from other databases.
> how can i reduce to increase log file because sometimes import stops
because
> of it.
> I use the following procedures :
> backup log mydatabase with truncate_only
> dbcc shrinkdatabase (mydatabase)
> i was wondering if it's any possible way to put a maximum size for log
file
> and when it get to the maximum file size to override the existent logs and
> not raise me the error that "The log file for database 'mydatabase' is
full.
> Back up the transaction log for the database to free up some log space."
> thanks in advance|||Thanks
i think this was the problem. I use delete command instead of truncate. I
turned the recovery mode to simple and I put the option auto shrink on.
Thanks again.
"Peter Yeoh" wrote:
> If you are using the FULL recovery model, you would need to backup the log
> before it can be reused. Otherwise, you might consider using the simple
> recovery model.
> Another possibility is that your import process is done within one
> transaction, hence all the data is either committed or rolled back. If this
> was the case (and you are using the simple recovery model), you might
> consider breaking the import into smaller transactions.
> If you are deleting everything initially, you might also consider using the
> TRUNCATE TABLE option as the row deletion is not individually logged.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Psycho" <Psycho@.discussions.microsoft.com> wrote in message
> news:26E3911D-CEA7-4389-8E26-1E76B3EEDF11@.microsoft.com...
> > Hi
> > i have a database and a proccess which delete everything from database and
> > then import some stuff from other databases.
> > how can i reduce to increase log file because sometimes import stops
> because
> > of it.
> > I use the following procedures :
> >
> > backup log mydatabase with truncate_only
> > dbcc shrinkdatabase (mydatabase)
> >
> > i was wondering if it's any possible way to put a maximum size for log
> file
> > and when it get to the maximum file size to override the existent logs and
> > not raise me the error that "The log file for database 'mydatabase' is
> full.
> > Back up the transaction log for the database to free up some log space."
> >
> > thanks in advance
>
>|||You might want to check out my article regarding shrink:
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/
"Psycho" <Psycho@.discussions.microsoft.com> wrote in message
news:AC3F1D5E-CA73-47D8-8EA0-D3FBF3315DAA@.microsoft.com...
> Thanks
> i think this was the problem. I use delete command instead of truncate. I
> turned the recovery mode to simple and I put the option auto shrink on.
> Thanks again.
> "Peter Yeoh" wrote:
>> If you are using the FULL recovery model, you would need to backup the log
>> before it can be reused. Otherwise, you might consider using the simple
>> recovery model.
>> Another possibility is that your import process is done within one
>> transaction, hence all the data is either committed or rolled back. If this
>> was the case (and you are using the simple recovery model), you might
>> consider breaking the import into smaller transactions.
>> If you are deleting everything initially, you might also consider using the
>> TRUNCATE TABLE option as the row deletion is not individually logged.
>> --
>> Peter Yeoh
>> http://www.yohz.com
>> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>>
>> "Psycho" <Psycho@.discussions.microsoft.com> wrote in message
>> news:26E3911D-CEA7-4389-8E26-1E76B3EEDF11@.microsoft.com...
>> > Hi
>> > i have a database and a proccess which delete everything from database and
>> > then import some stuff from other databases.
>> > how can i reduce to increase log file because sometimes import stops
>> because
>> > of it.
>> > I use the following procedures :
>> >
>> > backup log mydatabase with truncate_only
>> > dbcc shrinkdatabase (mydatabase)
>> >
>> > i was wondering if it's any possible way to put a maximum size for log
>> file
>> > and when it get to the maximum file size to override the existent logs and
>> > not raise me the error that "The log file for database 'mydatabase' is
>> full.
>> > Back up the transaction log for the database to free up some log space."
>> >
>> > thanks in advance
>>|||Also Truncate table does NOT work on tables with FKs, so you'll have to
remove those first..
Another alternative is to delete a range of rows ie
set rowcount 10000
delete from mytable
while @.@.rowcount !=0
delete from mytable
set rowcount 0
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Psycho" <Psycho@.discussions.microsoft.com> wrote in message
news:26E3911D-CEA7-4389-8E26-1E76B3EEDF11@.microsoft.com...
> Hi
> i have a database and a proccess which delete everything from database and
> then import some stuff from other databases.
> how can i reduce to increase log file because sometimes import stops
because
> of it.
> I use the following procedures :
> backup log mydatabase with truncate_only
> dbcc shrinkdatabase (mydatabase)
> i was wondering if it's any possible way to put a maximum size for log
file
> and when it get to the maximum file size to override the existent logs and
> not raise me the error that "The log file for database 'mydatabase' is
full.
> Back up the transaction log for the database to free up some log space."
> thanks in advance

Monday, March 19, 2012

Log file is increeasing too much

log file incremental growth question

Last night we upgraded a server with multiple databases to SQL Server
2005 from 2000. While upgrading the msdb database we noticed that the
active process was mostly locked waiting on new transaction log disk
allocation (I forget the wait name). msdb's transaction log was set
to 10% growth. But it never appeared to use 10% of the _current_ log
size, is it possible that the 10% figure is determined at the
beginning of a transaction and not updated as the transaction
proceeds? Monitoring the log file size from the os showed it to be
growing rapidly in small increments.
We know we need to change the default log growth to something more
substantial, but what about the question, is it 10% of the current log
size or 10% of the log as it was when the transaction started?
Thanks.
P. Kline
DBA> We know we need to change the default log growth to something more
> substantial, but what about the question, is it 10% of the current log
> size or 10% of the log as it was when the transaction started?
The way I understand it, it is 10% of the size at the instance the grow occurs. Say the file is 10MB
and a grow occurs. The grow occurs because something need space - but SQL Server do not know at the
moment how much space will be needed *in the end* for that process/transaction.
So at the moment in time it grows to 11MB. Say that the 1 MB wasn't enough and the process still
need more space. This would make the file grow 10% based on the what is now current size: 11MB =>
grow with 1.1MB. etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<pauldkline@.aol.com> wrote in message
news:b9a8ff5c-8ee0-46ff-8c5e-184665daed04@.72g2000hsu.googlegroups.com...
> Last night we upgraded a server with multiple databases to SQL Server
> 2005 from 2000. While upgrading the msdb database we noticed that the
> active process was mostly locked waiting on new transaction log disk
> allocation (I forget the wait name). msdb's transaction log was set
> to 10% growth. But it never appeared to use 10% of the _current_ log
> size, is it possible that the 10% figure is determined at the
> beginning of a transaction and not updated as the transaction
> proceeds? Monitoring the log file size from the os showed it to be
> growing rapidly in small increments.
> We know we need to change the default log growth to something more
> substantial, but what about the question, is it 10% of the current log
> size or 10% of the log as it was when the transaction started?
> Thanks.
> P. Kline
> DBA

Wednesday, March 7, 2012

Log backups required with DPM 2007?

I'm thinking of using DPM 2007 exclusively for backup of our SQL Server
databases. I'm assuming that even thought the data is protected by DPM, I
would still need to run log backups (perhaps with the NO_LOG option?) in
order to truncate the log files and keep them from growing too large. Is
this assumption correct?Hi Steve
Assuming you have full or bulk-logged recovery models! Log backups will free
up the inactive portion of the log file and allow it to be re-used,
therefore the size of the log file should remain a constant size if you
perform log backups regularly, unless you do something unusual. There should
be no need to shrink the files as shrinking database files can lead to disc
fragmentation and performance issues.
The NO_LOG and NO_TRUNCATE options should not be included as these break the
back chain.
John
"steveg" <steveg@.discussions.microsoft.com> wrote in message
news:96651CBC-4433-4D7A-99C5-3EB9FBCCC791@.microsoft.com...
> I'm thinking of using DPM 2007 exclusively for backup of our SQL Server
> databases. I'm assuming that even thought the data is protected by DPM, I
> would still need to run log backups (perhaps with the NO_LOG option?) in
> order to truncate the log files and keep them from growing too large. Is
> this assumption correct?
--== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--

Wednesday, March 28, 2012

Log Full

Log Full

Log Full

log files problem

Log Files Growing out of control

Log Files Growing out of control

Log Files Growing out of control

Monday, March 26, 2012

LOG files

log files

LOG files

log files

log files

Friday, March 23, 2012

log file sizes for all databases

LOG File size is 18GB

LOG File size is 18GB

Wednesday, March 21, 2012

Log file is increeasing too much

Monday, March 19, 2012

Log file is increeasing too much

Log file is increeasing too much

log file incremental growth question

Wednesday, March 7, 2012

Log backups required with DPM 2007?

Locks and Blocks

Blog Archive

About Me