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 divantages: 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 file size

Can anyone tell me in detail, what exactly log file stores. Even i shrik the
database log file does not srhink. Even after i backup,delete n restore db,
log file remain the same. How can i squueze the log file size?
And How large log file effetc the performance of db, quereis etc ?All modifications in a database are reflected in the transaction log file. S
ee
http://www.karaszi.com/SQLServer/info_dont_shrink.asp for considerations and
how to shrink file
file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vikram" <aa@.aa> wrote in message news:uB9vy2afGHA.324@.TK2MSFTNGP02.phx.gbl...n">
> Can anyone tell me in detail, what exactly log file stores. Even i shrik t
he
> database log file does not srhink. Even after i backup,delete n restore db
,
> log file remain the same. How can i squueze the log file size?
> And How large log file effetc the performance of db, quereis etc ?
>

log file size

Can anyone tell me in detail, what exactly log file stores. Even i shrik the
database log file does not srhink. Even after i backup,delete n restore db,
log file remain the same. How can i squueze the log file size?
And How large log file effetc the performance of db, quereis etc ?All modifications in a database are reflected in the transaction log file. See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp for considerations and how to shrink file
file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vikram" <aa@.aa> wrote in message news:uB9vy2afGHA.324@.TK2MSFTNGP02.phx.gbl...
> Can anyone tell me in detail, what exactly log file stores. Even i shrik the
> database log file does not srhink. Even after i backup,delete n restore db,
> log file remain the same. How can i squueze the log file size?
> And How large log file effetc the performance of db, quereis etc ?
>

Log file and Restore

I did a backup of a database on server X.
Created the Database on server Y
I copied the backup of server X to server Y and did a restore.
The database is about 50M
The Log file is over 30G.
If I truncate the Log file the Database wont open.
Any Ideas '
Thanks in advanceYou can truncate log with 'backup log ... with
truncate_only' then shrink the log file. And truncate logs
on source server before db full backup can reduce log size.
>--Original Message--
>I did a backup of a database on server X.
>Created the Database on server Y
>I copied the backup of server X to server Y and did a
restore.
>The database is about 50M
>The Log file is over 30G.
>If I truncate the Log file the Database wont open.
>Any Ideas '
>Thanks in advance
>
>
>
>
>.
>|||It sounds as though you do not need or care about the transaction log. =If that is the case you can simply
BACKUP LOG NameOfYourDatabase WITH NO_LOG
GO
DBCC SHRINKFILE ('TheLogFileNameOfYourDatabase', TRUNCATEONLY)
GO
Books Online will contain more information on these topics.
-- Keith
"Jim Campau" <Jim_Campau@.bausch.com> wrote in message =news:eOhDFnbkDHA.2328@.TK2MSFTNGP10.phx.gbl...
> I did a backup of a database on server X.
> Created the Database on server Y
> I copied the backup of server X to server Y and did a restore.
> > The database is about 50M
> The Log file is over 30G.
> If I truncate the Log file the Database wont open.
> > Any Ideas '
> > Thanks in advance
> > > > > > > >

Log backup question.

1).How to restore the active log.
I backup the DB but didn't backup log .
Is there a way to apply the active log after restore the DB
2)How to backup an up-to-date log.
How to ensure the log is backuped right on time when the DB is crashed.
I am afraid ,even a scheduled log-backup with interval of 1 Min can't do
this
Thanks!!
1)
No can do if db is in simple recovery mode. If db is in full recovery mode, see point 2. However, if you never
do log backup, and run in full recovery mode, log will never be truncated. So, recovery modes and whether you
do log backups go hand-in-hand.
2)
After the crash, do a log backup using the NO_TRUNCATE option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Straight" <someone@.> wrote in message news:OVkPazELEHA.1120@.TK2MSFTNGP11.phx.gbl...
> 1).How to restore the active log.
> I backup the DB but didn't backup log .
> Is there a way to apply the active log after restore the DB
> 2)How to backup an up-to-date log.
> How to ensure the log is backuped right on time when the DB is crashed.
> I am afraid ,even a scheduled log-backup with interval of 1 Min can't do
> this
> Thanks!!
>
|||TK;
for point 2, log backup NO_TRUNCATE, does this apply to any recovery model
in general ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:esY$n5ELEHA.2532@.TK2MSFTNGP10.phx.gbl...
> 1)
> No can do if db is in simple recovery mode. If db is in full recovery
mode, see point 2. However, if you never
> do log backup, and run in full recovery mode, log will never be truncated.
So, recovery modes and whether you
> do log backups go hand-in-hand.
> 2)
> After the crash, do a log backup using the NO_TRUNCATE option.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Straight" <someone@.> wrote in message
news:OVkPazELEHA.1120@.TK2MSFTNGP11.phx.gbl...
>
|||Hi,
Only for Bulk_logged and FULL recovery models.
Thanks
Hari
MCDBA
"pohkeat" <pohkeat@.hotmail.com> wrote in message
news:usl0UTFLEHA.2244@.tk2msftngp13.phx.gbl...
> TK;
> for point 2, log backup NO_TRUNCATE, does this apply to any recovery model
> in general ?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in[vbcol=seagreen]
> message news:esY$n5ELEHA.2532@.TK2MSFTNGP10.phx.gbl...
> mode, see point 2. However, if you never
truncated.[vbcol=seagreen]
> So, recovery modes and whether you
> news:OVkPazELEHA.1120@.TK2MSFTNGP11.phx.gbl...
crashed.[vbcol=seagreen]
do
>

Log backup question.

1).How to restore the active log.
I backup the DB but didn't backup log .
Is there a way to apply the active log after restore the DB
2)How to backup an up-to-date log.
How to ensure the log is backuped right on time when the DB is crashed.
I am afraid ,even a scheduled log-backup with interval of 1 Min can't do
this
Thanks!!1)
No can do if db is in simple recovery mode. If db is in full recovery mode,
see point 2. However, if you never
do log backup, and run in full recovery mode, log will never be truncated. S
o, recovery modes and whether you
do log backups go hand-in-hand.
2)
After the crash, do a log backup using the NO_TRUNCATE option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Straight" <someone@.> wrote in message news:OVkPazELEHA.1120@.TK2MSFTNGP11.phx.gbl...eagreen">
> 1).How to restore the active log.
> I backup the DB but didn't backup log .
> Is there a way to apply the active log after restore the DB
> 2)How to backup an up-to-date log.
> How to ensure the log is backuped right on time when the DB is crashed.
> I am afraid ,even a scheduled log-backup with interval of 1 Min can't do
> this
> Thanks!!
>|||TK;
for point 2, log backup NO_TRUNCATE, does this apply to any recovery model
in general ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:esY$n5ELEHA.2532@.TK2MSFTNGP10.phx.gbl...
> 1)
> No can do if db is in simple recovery mode. If db is in full recovery
mode, see point 2. However, if you never
> do log backup, and run in full recovery mode, log will never be truncated.
So, recovery modes and whether you
> do log backups go hand-in-hand.
> 2)
> After the crash, do a log backup using the NO_TRUNCATE option.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Straight" <someone@.> wrote in message
news:OVkPazELEHA.1120@.TK2MSFTNGP11.phx.gbl...
>|||Hi,
Only for Bulk_logged and FULL recovery models.
Thanks
Hari
MCDBA
"pohkeat" <pohkeat@.hotmail.com> wrote in message
news:usl0UTFLEHA.2244@.tk2msftngp13.phx.gbl...
> TK;
> for point 2, log backup NO_TRUNCATE, does this apply to any recovery model
> in general ?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:esY$n5ELEHA.2532@.TK2MSFTNGP10.phx.gbl...
> mode, see point 2. However, if you never
truncated.[vbcol=seagreen]
> So, recovery modes and whether you
> news:OVkPazELEHA.1120@.TK2MSFTNGP11.phx.gbl...
crashed.[vbcol=seagreen]
do[vbcol=seagreen]
>

Log backup question.

1).How to restore the active log.
I backup the DB but didn't backup log .
Is there a way to apply the active log after restore the DB
2)How to backup an up-to-date log.
How to ensure the log is backuped right on time when the DB is crashed.
I am afraid ,even a scheduled log-backup with interval of 1 Min can't do
this
Thanks!!1)
No can do if db is in simple recovery mode. If db is in full recovery mode, see point 2. However, if you never
do log backup, and run in full recovery mode, log will never be truncated. So, recovery modes and whether you
do log backups go hand-in-hand.
2)
After the crash, do a log backup using the NO_TRUNCATE option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Straight" <someone@.> wrote in message news:OVkPazELEHA.1120@.TK2MSFTNGP11.phx.gbl...
> 1).How to restore the active log.
> I backup the DB but didn't backup log .
> Is there a way to apply the active log after restore the DB
> 2)How to backup an up-to-date log.
> How to ensure the log is backuped right on time when the DB is crashed.
> I am afraid ,even a scheduled log-backup with interval of 1 Min can't do
> this
> Thanks!!
>|||TK;
for point 2, log backup NO_TRUNCATE, does this apply to any recovery model
in general ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:esY$n5ELEHA.2532@.TK2MSFTNGP10.phx.gbl...
> 1)
> No can do if db is in simple recovery mode. If db is in full recovery
mode, see point 2. However, if you never
> do log backup, and run in full recovery mode, log will never be truncated.
So, recovery modes and whether you
> do log backups go hand-in-hand.
> 2)
> After the crash, do a log backup using the NO_TRUNCATE option.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Straight" <someone@.> wrote in message
news:OVkPazELEHA.1120@.TK2MSFTNGP11.phx.gbl...
> > 1).How to restore the active log.
> > I backup the DB but didn't backup log .
> > Is there a way to apply the active log after restore the DB
> > 2)How to backup an up-to-date log.
> > How to ensure the log is backuped right on time when the DB is crashed.
> > I am afraid ,even a scheduled log-backup with interval of 1 Min can't do
> > this
> >
> > Thanks!!
> >
> >
>|||Hi,
Only for Bulk_logged and FULL recovery models.
Thanks
Hari
MCDBA
"pohkeat" <pohkeat@.hotmail.com> wrote in message
news:usl0UTFLEHA.2244@.tk2msftngp13.phx.gbl...
> TK;
> for point 2, log backup NO_TRUNCATE, does this apply to any recovery model
> in general ?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:esY$n5ELEHA.2532@.TK2MSFTNGP10.phx.gbl...
> > 1)
> > No can do if db is in simple recovery mode. If db is in full recovery
> mode, see point 2. However, if you never
> > do log backup, and run in full recovery mode, log will never be
truncated.
> So, recovery modes and whether you
> > do log backups go hand-in-hand.
> >
> > 2)
> > After the crash, do a log backup using the NO_TRUNCATE option.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> >
> >
> > "Straight" <someone@.> wrote in message
> news:OVkPazELEHA.1120@.TK2MSFTNGP11.phx.gbl...
> > > 1).How to restore the active log.
> > > I backup the DB but didn't backup log .
> > > Is there a way to apply the active log after restore the DB
> > > 2)How to backup an up-to-date log.
> > > How to ensure the log is backuped right on time when the DB is
crashed.
> > > I am afraid ,even a scheduled log-backup with interval of 1 Min can't
do
> > > this
> > >
> > > Thanks!!
> > >
> > >
> >
> >
>