Log File Size vs. Data Size ?

I am creating maintenance plans in SQL Server 2005 to manage the backups of
a
database. The Databasename_Data.MDF file is approx. 234 MB in size. The
Databasename_Log.LDF is approx. 3.5 GB. Does this ratio of log size to data
size
sound "normal" ? If not, any thoughts or advice ?
If I want to try shrinking the log file, do I need to use a combination of
'BACKUP LOG' and 'DBCC SHRINKFILE' commands ? Or is there a more
automated way using maintenance plans?
Thanks in advance!
TomTom Glasser wrote:
> I am creating maintenance plans in SQL Server 2005 to manage the backups o
f a
> database. The Databasename_Data.MDF file is approx. 234 MB in size. The
> Databasename_Log.LDF is approx. 3.5 GB. Does this ratio of log size to da
ta
> size
> sound "normal" ? If not, any thoughts or advice ?
> If I want to try shrinking the log file, do I need to use a combination of
> 'BACKUP LOG' and 'DBCC SHRINKFILE' commands ? Or is there a more
> automated way using maintenance plans?
> Thanks in advance!
> Tom
Hi Tom
It sounds like your logfile is much bigger than it needs to be, but it
could also be right. If you are running in FULL recovery mode and you're
not backing up your logfile, it will just grow and grow and grow. When
you backup the logfile, the backup commands will truncate you log which
means that old transactions can be overwritten. In this way the space
will be re-used so the file doesn't need to grow.
If you find out that you have to shrink the logfile, you'll have to run
DBCC SHRINKFILE.
Be carefull about shrinking the log though. A logfile should only be
shrunk in cases where there has been extraordinary activity in the
database which has made the logfile grow. This could e.g. be if you have
imported or deleted a lot of data as a "one time" operation. You
shouldn't shrink a logfile on a regular basis - that will only lead to
poor performance because it will have to grow again, and the file will
most likely be fragmented as well.
Try to have a look at -
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
Regards
Steen Schlüter Persson
Database Administrator / System Administratorsql

Log File Size vs. Data Size ?

I am creating maintenance plans in SQL Server 2005 to manage the backups of a
database. The Databasename_Data.MDF file is approx. 234 MB in size. The
Databasename_Log.LDF is approx. 3.5 GB. Does this ratio of log size to data
size
sound "normal" ? If not, any thoughts or advice ?
If I want to try shrinking the log file, do I need to use a combination of
'BACKUP LOG' and 'DBCC SHRINKFILE' commands ? Or is there a more
automated way using maintenance plans?
Thanks in advance!
TomTom Glasser wrote:
> I am creating maintenance plans in SQL Server 2005 to manage the backups of a
> database. The Databasename_Data.MDF file is approx. 234 MB in size. The
> Databasename_Log.LDF is approx. 3.5 GB. Does this ratio of log size to data
> size
> sound "normal" ? If not, any thoughts or advice ?
> If I want to try shrinking the log file, do I need to use a combination of
> 'BACKUP LOG' and 'DBCC SHRINKFILE' commands ? Or is there a more
> automated way using maintenance plans?
> Thanks in advance!
> Tom
Hi Tom
It sounds like your logfile is much bigger than it needs to be, but it
could also be right. If you are running in FULL recovery mode and you're
not backing up your logfile, it will just grow and grow and grow. When
you backup the logfile, the backup commands will truncate you log which
means that old transactions can be overwritten. In this way the space
will be re-used so the file doesn't need to grow.
If you find out that you have to shrink the logfile, you'll have to run
DBCC SHRINKFILE.
Be carefull about shrinking the log though. A logfile should only be
shrunk in cases where there has been extraordinary activity in the
database which has made the logfile grow. This could e.g. be if you have
imported or deleted a lot of data as a "one time" operation. You
shouldn't shrink a logfile on a regular basis - that will only lead to
poor performance because it will have to grow again, and the file will
most likely be fragmented as well.
Try to have a look at -
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

Log File Shrink

I'm unable to shrink my log file. The databse is about 2 GB & the log file
has grown to 11.5 GB. I've run transaction backups every 3 hours, but the log
won't shrink. I need to reduce the size, to free up space on the drive. I've
tried to shrink the file & it says it is successful, but I cannot shrink it
enough. I've tried changing the recovery model to Simple & then shrinking the
log, but this doesn't work either.
Any suggestions will be appreciated.
BigSamBigSam wrote:
> I'm unable to shrink my log file. The databse is about 2 GB & the log file
> has grown to 11.5 GB. I've run transaction backups every 3 hours, but the log
> won't shrink. I need to reduce the size, to free up space on the drive. I've
> tried to shrink the file & it says it is successful, but I cannot shrink it
> enough. I've tried changing the recovery model to Simple & then shrinking the
> log, but this doesn't work either.
> Any suggestions will be appreciated.
> BigSam
--
I had a heap of trouble with the same problem one day. When I found
the solution, I saved the steps. Here they are:
1) DBCC Shrinkfile (<logfilename>, TruncateOnly)
2) Backup Log <dbname> With Truncate_Only
3) DBCC Shrinkfile (<logfilename>, TruncateOnly)
That should fix it.
Mike|||Sam,
If what Mike wrote doesn't help (and only as a last resort):
First, iiii Be sure to take a full backup of the database!!!!
Then, change the recovery mode to simple.
Next issue the syntax CHECKPOINT in Query Analyzer on that server then
issue DBCC Shrinkfile (<logfilename>, TruncateOnly).
Last, set your recovery mode back to Full.
SQLPoet
MikeR wrote:
> BigSam wrote:
> > I'm unable to shrink my log file. The databse is about 2 GB & the log file
> > has grown to 11.5 GB. I've run transaction backups every 3 hours, but the log
> > won't shrink. I need to reduce the size, to free up space on the drive. I've
> > tried to shrink the file & it says it is successful, but I cannot shrink it
> > enough. I've tried changing the recovery model to Simple & then shrinking the
> > log, but this doesn't work either.
> > Any suggestions will be appreciated.
> >
> > BigSam
> --
> I had a heap of trouble with the same problem one day. When I found
> the solution, I saved the steps. Here they are:
> 1) DBCC Shrinkfile (<logfilename>, TruncateOnly)
> 2) Backup Log <dbname> With Truncate_Only
> 3) DBCC Shrinkfile (<logfilename>, TruncateOnly)
> That should fix it.
> Mike|||Read about backup and especially recovery model in Books Online, Them this will probably help:
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/
"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:7C3F7F32-36EF-49C3-B83C-FCE89FDF4147@.microsoft.com...
> I'm unable to shrink my log file. The databse is about 2 GB & the log file
> has grown to 11.5 GB. I've run transaction backups every 3 hours, but the log
> won't shrink. I need to reduce the size, to free up space on the drive. I've
> tried to shrink the file & it says it is successful, but I cannot shrink it
> enough. I've tried changing the recovery model to Simple & then shrinking the
> log, but this doesn't work either.
> Any suggestions will be appreciated.
> BigSam|||"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:7C3F7F32-36EF-49C3-B83C-FCE89FDF4147@.microsoft.com...
> I'm unable to shrink my log file. The databse is about 2 GB & the log file
> has grown to 11.5 GB. I've run transaction backups every 3 hours, but the
log
> won't shrink. I need to reduce the size, to free up space on the drive.
I've
> tried to shrink the file & it says it is successful, but I cannot shrink
it
> enough. I've tried changing the recovery model to Simple & then shrinking
the
> log, but this doesn't work either.
> Any suggestions will be appreciated.
Do a DBCC OPENTRAN on the database and make sure there are no open
transactions.
But it'snot clear to me if youmean the log is 11.5 gb and FULL or 11.5GB on
disk, but only partially full.
> BigSam

Log file not freeing space (FULL recovery model) - even after transaction log backups

Hey all,
I have a site that has their log file at 40 gig (running SQL Server
2005).
The database is set to FULL recovery model, and transaction log
backups are performed every 3 hours.
The log file is HUGE compared to usual and I need to shrink it down.
However, it has no free space in it which is very strange considering
we are backing up the logs every 3 hours.
I have full backups occuring nightly.
I also tried switching to SIMPLE recovery model and shrinking the log
file.
Obviously this doesn't work as there isn't any free space in the log.
The site did have some db corruption issues a few weeks ago due to a
SAN issue.
The hardware has been repaired as well as the corruptions (they were
index corruptions so we rebuilt the indexes).
Could this have caused the transaction log to have issues to and not
free comitted transactions?
We have many other sites who have small transaction logs and I am at a
loss as to why it is so large.
The database is also 40 gig (and thus backups are 80+ gig in size!)
Anything I can try?
I am concerned about trashing the log by detaching / reattaching the
db without the log file as it seems brutal, but is that the only
choice?
Thanks,
David
Hi David
Check to see if there are open transactions with DBCC OPENTRAN
John
"David Conte" wrote:

> Hey all,
> I have a site that has their log file at 40 gig (running SQL Server
> 2005).
> The database is set to FULL recovery model, and transaction log
> backups are performed every 3 hours.
> The log file is HUGE compared to usual and I need to shrink it down.
> However, it has no free space in it which is very strange considering
> we are backing up the logs every 3 hours.
> I have full backups occuring nightly.
> I also tried switching to SIMPLE recovery model and shrinking the log
> file.
> Obviously this doesn't work as there isn't any free space in the log.
> The site did have some db corruption issues a few weeks ago due to a
> SAN issue.
> The hardware has been repaired as well as the corruptions (they were
> index corruptions so we rebuilt the indexes).
> Could this have caused the transaction log to have issues to and not
> free comitted transactions?
> We have many other sites who have small transaction logs and I am at a
> loss as to why it is so large.
> The database is also 40 gig (and thus backups are 80+ gig in size!)
> Anything I can try?
> I am concerned about trashing the log by detaching / reattaching the
> db without the log file as it seems brutal, but is that the only
> choice?
> Thanks,
> David
>
|||How have you determined that there is 'no free space in it'? Did you
execute dbcc sqlperf(logspace)? or simply try to shrink it and it didn't
reduce in physical size? There is some very complicated stuff about the
internals of the log file that can prevent it from shrinking (much) even if
it has virtually no information in it. Search the web for sql server log
file shrink and you will find a number of helpful scripts to get over this
situation.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"David Conte" <davconts@.gmail.com> wrote in message
news:1194500150.103297.201900@.y27g2000pre.googlegr oups.com...
> Hey all,
> I have a site that has their log file at 40 gig (running SQL Server
> 2005).
> The database is set to FULL recovery model, and transaction log
> backups are performed every 3 hours.
> The log file is HUGE compared to usual and I need to shrink it down.
> However, it has no free space in it which is very strange considering
> we are backing up the logs every 3 hours.
> I have full backups occuring nightly.
> I also tried switching to SIMPLE recovery model and shrinking the log
> file.
> Obviously this doesn't work as there isn't any free space in the log.
> The site did have some db corruption issues a few weeks ago due to a
> SAN issue.
> The hardware has been repaired as well as the corruptions (they were
> index corruptions so we rebuilt the indexes).
> Could this have caused the transaction log to have issues to and not
> free comitted transactions?
> We have many other sites who have small transaction logs and I am at a
> loss as to why it is so large.
> The database is also 40 gig (and thus backups are 80+ gig in size!)
> Anything I can try?
> I am concerned about trashing the log by detaching / reattaching the
> db without the log file as it seems brutal, but is that the only
> choice?
> Thanks,
> David
>
|||Thanks for the info John and Kevin.
I didn't think that there could be an open transaction.. so tried DBCC
OPENTRAN.
Running DBCC OPENTRAN gives:
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (42372:54:2)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Sounds like this has to do with replication? (excuse my ignorance
here!)
We don't have replication setup.
Kevin - I hadn't run dbcc sqlperf(logspace) however when shrinking a
log file via Management Studio it gives the free space percentage
there before attempting the shrink. Just to confirm, I ran the SQL and
it gives:
41297.24 99.79205% full
Thanks
David
On Nov 9, 12:43 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> How have you determined that there is 'no free space in it'? Did you
> execute dbcc sqlperf(logspace)? or simply try to shrink it and it didn't
> reduce in physical size? There is some very complicated stuff about the
> internals of the log file that can prevent it from shrinking (much) even if
> it has virtually no information in it. Search the web for sql server log
> file shrink and you will find a number of helpful scripts to get over this
> situation.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> "David Conte" <davco...@.gmail.com> wrote in message
> news:1194500150.103297.201900@.y27g2000pre.googlegr oups.com...
>
>
>
|||Hi
What does the DBCC command give after you backup the log?
Use DBCC SHRINKFILE to shrink the file rather than Enterprise Manager, you
should not shrink it to a size where under normal operations it will expand.
John
"David Conte" wrote:

> Thanks for the info John and Kevin.
> I didn't think that there could be an open transaction.. so tried DBCC
> OPENTRAN.
> Running DBCC OPENTRAN gives:
> Replicated Transaction Information:
> Oldest distributed LSN : (0:0:0)
> Oldest non-distributed LSN : (42372:54:2)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Sounds like this has to do with replication? (excuse my ignorance
> here!)
> We don't have replication setup.
> Kevin - I hadn't run dbcc sqlperf(logspace) however when shrinking a
> log file via Management Studio it gives the free space percentage
> there before attempting the shrink. Just to confirm, I ran the SQL and
> it gives:
> 41297.24 99.79205% full
> Thanks
> David
>
> On Nov 9, 12:43 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>
>
|||Log still shows minimal free space even after transaction logs.
Hence, running DBCC SHRINKFILE does nothing as there is no free space
to be recovered.
This whole thing smells like a bug.
If I run SELECT * FROM sys.databases, it says the log_reuse_wait_desc
is "REPLICATION".
Again, this database (or any other databases on the server) has never
had replication setup!
I tried running exec sp_removedbreplication - it completes, but the
log_reuse_wait_desc is still "REPLICATION" and DBCC OPENTRAN still
gives:
Transaction information for database 'xxxxx'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (42372:54:2)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I think we are going to resort to setting up replication on it, run
sp_repldone NULL, NULL, 0, 0, 1 and finally remove the replication...
David
On Nov 9, 7:30 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> What does the DBCC command give after you backup the log?
> Use DBCC SHRINKFILE to shrink the file rather than Enterprise Manager, you
> should not shrink it to a size where under normal operations it will expand.
> John
|||Hi David
I am not sure how that has occurred, but you could try rebuilding the log
file.
John
"David Conte" wrote:

> Log still shows minimal free space even after transaction logs.
> Hence, running DBCC SHRINKFILE does nothing as there is no free space
> to be recovered.
> This whole thing smells like a bug.
> If I run SELECT * FROM sys.databases, it says the log_reuse_wait_desc
> is "REPLICATION".
> Again, this database (or any other databases on the server) has never
> had replication setup!
> I tried running exec sp_removedbreplication - it completes, but the
> log_reuse_wait_desc is still "REPLICATION" and DBCC OPENTRAN still
> gives:
> Transaction information for database 'xxxxx'.
> Replicated Transaction Information:
> Oldest distributed LSN : (0:0:0)
> Oldest non-distributed LSN : (42372:54:2)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> I think we are going to resort to setting up replication on it, run
> sp_repldone NULL, NULL, 0, 0, 1 and finally remove the replication...
> David
> On Nov 9, 7:30 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
>
|||Ok well I found a forum post here on an identical issue.
Sounds like it is indeed an SQL Server bug.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=654902&SiteID=1
The workaround (which I've managed to do on a test restore of the live
database which experiences the same issue) is to setup replication on
the database, then remove the publication. After this there was now
99% free space in the logfile and I was able to shrink it from 44 GB
to 1 GB.
DBCC OPENTRAN now says there are no active transactions, and the
log_resuse_wait_desc for the db is "NOTHING" instead of "REPLICATION".
So looking good again.
Now to do the process on the live database
Thanks everyone for their time!
Dave
|||Hi David
According to the post the bug may have been fixed in SP2, if you are not
running this version you may want to upgrade to SP2 plus hotfixes.
John
"David Conte" wrote:

> Ok well I found a forum post here on an identical issue.
> Sounds like it is indeed an SQL Server bug.
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=654902&SiteID=1
> The workaround (which I've managed to do on a test restore of the live
> database which experiences the same issue) is to setup replication on
> the database, then remove the publication. After this there was now
> 99% free space in the logfile and I was able to shrink it from 44 GB
> to 1 GB.
> DBCC OPENTRAN now says there are no active transactions, and the
> log_resuse_wait_desc for the db is "NOTHING" instead of "REPLICATION".
> So looking good again.
> Now to do the process on the live database
> Thanks everyone for their time!
> Dave
>

Log file not freeing space (FULL recovery model) - even after transaction log backups

Hey all,
I have a site that has their log file at 40 gig (running SQL Server
2005).
The database is set to FULL recovery model, and transaction log
backups are performed every 3 hours.
The log file is HUGE compared to usual and I need to shrink it down.
However, it has no free space in it which is very strange considering
we are backing up the logs every 3 hours.
I have full backups occuring nightly.
I also tried switching to SIMPLE recovery model and shrinking the log
file.
Obviously this doesn't work as there isn't any free space in the log.
The site did have some db corruption issues a few weeks ago due to a
SAN issue.
The hardware has been repaired as well as the corruptions (they were
index corruptions so we rebuilt the indexes).
Could this have caused the transaction log to have issues to and not
free comitted transactions?
We have many other sites who have small transaction logs and I am at a
loss as to why it is so large.
The database is also 40 gig (and thus backups are 80+ gig in size!)
Anything I can try?
I am concerned about trashing the log by detaching / reattaching the
db without the log file as it seems brutal, but is that the only
choice?
Thanks,
DavidHi David
Check to see if there are open transactions with DBCC OPENTRAN
John
"David Conte" wrote:
> Hey all,
> I have a site that has their log file at 40 gig (running SQL Server
> 2005).
> The database is set to FULL recovery model, and transaction log
> backups are performed every 3 hours.
> The log file is HUGE compared to usual and I need to shrink it down.
> However, it has no free space in it which is very strange considering
> we are backing up the logs every 3 hours.
> I have full backups occuring nightly.
> I also tried switching to SIMPLE recovery model and shrinking the log
> file.
> Obviously this doesn't work as there isn't any free space in the log.
> The site did have some db corruption issues a few weeks ago due to a
> SAN issue.
> The hardware has been repaired as well as the corruptions (they were
> index corruptions so we rebuilt the indexes).
> Could this have caused the transaction log to have issues to and not
> free comitted transactions?
> We have many other sites who have small transaction logs and I am at a
> loss as to why it is so large.
> The database is also 40 gig (and thus backups are 80+ gig in size!)
> Anything I can try?
> I am concerned about trashing the log by detaching / reattaching the
> db without the log file as it seems brutal, but is that the only
> choice?
> Thanks,
> David
>|||How have you determined that there is 'no free space in it'' Did you
execute dbcc sqlperf(logspace)? or simply try to shrink it and it didn't
reduce in physical size? There is some very complicated stuff about the
internals of the log file that can prevent it from shrinking (much) even if
it has virtually no information in it. Search the web for sql server log
file shrink and you will find a number of helpful scripts to get over this
situation.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"David Conte" <davconts@.gmail.com> wrote in message
news:1194500150.103297.201900@.y27g2000pre.googlegroups.com...
> Hey all,
> I have a site that has their log file at 40 gig (running SQL Server
> 2005).
> The database is set to FULL recovery model, and transaction log
> backups are performed every 3 hours.
> The log file is HUGE compared to usual and I need to shrink it down.
> However, it has no free space in it which is very strange considering
> we are backing up the logs every 3 hours.
> I have full backups occuring nightly.
> I also tried switching to SIMPLE recovery model and shrinking the log
> file.
> Obviously this doesn't work as there isn't any free space in the log.
> The site did have some db corruption issues a few weeks ago due to a
> SAN issue.
> The hardware has been repaired as well as the corruptions (they were
> index corruptions so we rebuilt the indexes).
> Could this have caused the transaction log to have issues to and not
> free comitted transactions?
> We have many other sites who have small transaction logs and I am at a
> loss as to why it is so large.
> The database is also 40 gig (and thus backups are 80+ gig in size!)
> Anything I can try?
> I am concerned about trashing the log by detaching / reattaching the
> db without the log file as it seems brutal, but is that the only
> choice?
> Thanks,
> David
>|||Thanks for the info John and Kevin.
I didn't think that there could be an open transaction.. so tried DBCC
OPENTRAN.
Running DBCC OPENTRAN gives:
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (42372:54:2)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Sounds like this has to do with replication? (excuse my ignorance
here!)
We don't have replication setup.
Kevin - I hadn't run dbcc sqlperf(logspace) however when shrinking a
log file via Management Studio it gives the free space percentage
there before attempting the shrink. Just to confirm, I ran the SQL and
it gives:
41297.24 99.79205% full
Thanks
David
On Nov 9, 12:43 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> How have you determined that there is 'no free space in it'' Did you
> execute dbcc sqlperf(logspace)? or simply try to shrink it and it didn't
> reduce in physical size? There is some very complicated stuff about the
> internals of the log file that can prevent it from shrinking (much) even if
> it has virtually no information in it. Search the web for sql server log
> file shrink and you will find a number of helpful scripts to get over this
> situation.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> "David Conte" <davco...@.gmail.com> wrote in message
> news:1194500150.103297.201900@.y27g2000pre.googlegroups.com...
> > Hey all,
> > I have a site that has their log file at 40 gig (running SQL Server
> > 2005).
> > The database is set to FULL recovery model, and transaction log
> > backups are performed every 3 hours.
> > The log file is HUGE compared to usual and I need to shrink it down.
> > However, it has no free space in it which is very strange considering
> > we are backing up the logs every 3 hours.
> > I have full backups occuring nightly.
> > I also tried switching to SIMPLE recovery model and shrinking the log
> > file.
> > Obviously this doesn't work as there isn't any free space in the log.
> > The site did have some db corruption issues a few weeks ago due to a
> > SAN issue.
> > The hardware has been repaired as well as the corruptions (they were
> > index corruptions so we rebuilt the indexes).
> > Could this have caused the transaction log to have issues to and not
> > free comitted transactions?
> > We have many other sites who have small transaction logs and I am at a
> > loss as to why it is so large.
> > The database is also 40 gig (and thus backups are 80+ gig in size!)
> > Anything I can try?
> > I am concerned about trashing the log by detaching / reattaching the
> > db without the log file as it seems brutal, but is that the only
> > choice?
> > Thanks,
> > David|||Hi
What does the DBCC command give after you backup the log?
Use DBCC SHRINKFILE to shrink the file rather than Enterprise Manager, you
should not shrink it to a size where under normal operations it will expand.
John
"David Conte" wrote:
> Thanks for the info John and Kevin.
> I didn't think that there could be an open transaction.. so tried DBCC
> OPENTRAN.
> Running DBCC OPENTRAN gives:
> Replicated Transaction Information:
> Oldest distributed LSN : (0:0:0)
> Oldest non-distributed LSN : (42372:54:2)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Sounds like this has to do with replication? (excuse my ignorance
> here!)
> We don't have replication setup.
> Kevin - I hadn't run dbcc sqlperf(logspace) however when shrinking a
> log file via Management Studio it gives the free space percentage
> there before attempting the shrink. Just to confirm, I ran the SQL and
> it gives:
> 41297.24 99.79205% full
> Thanks
> David
>
> On Nov 9, 12:43 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> > How have you determined that there is 'no free space in it'' Did you
> > execute dbcc sqlperf(logspace)? or simply try to shrink it and it didn't
> > reduce in physical size? There is some very complicated stuff about the
> > internals of the log file that can prevent it from shrinking (much) even if
> > it has virtually no information in it. Search the web for sql server log
> > file shrink and you will find a number of helpful scripts to get over this
> > situation.
> >
> > --
> > Kevin G. Boles
> > TheSQLGuru
> > Indicium Resources, Inc.
> >
> > "David Conte" <davco...@.gmail.com> wrote in message
> >
> > news:1194500150.103297.201900@.y27g2000pre.googlegroups.com...
> >
> > > Hey all,
> >
> > > I have a site that has their log file at 40 gig (running SQL Server
> > > 2005).
> > > The database is set to FULL recovery model, and transaction log
> > > backups are performed every 3 hours.
> >
> > > The log file is HUGE compared to usual and I need to shrink it down.
> > > However, it has no free space in it which is very strange considering
> > > we are backing up the logs every 3 hours.
> > > I have full backups occuring nightly.
> > > I also tried switching to SIMPLE recovery model and shrinking the log
> > > file.
> > > Obviously this doesn't work as there isn't any free space in the log.
> >
> > > The site did have some db corruption issues a few weeks ago due to a
> > > SAN issue.
> > > The hardware has been repaired as well as the corruptions (they were
> > > index corruptions so we rebuilt the indexes).
> > > Could this have caused the transaction log to have issues to and not
> > > free comitted transactions?
> >
> > > We have many other sites who have small transaction logs and I am at a
> > > loss as to why it is so large.
> > > The database is also 40 gig (and thus backups are 80+ gig in size!)
> >
> > > Anything I can try?
> > > I am concerned about trashing the log by detaching / reattaching the
> > > db without the log file as it seems brutal, but is that the only
> > > choice?
> >
> > > Thanks,
> > > David
>
>|||Log still shows minimal free space even after transaction logs.
Hence, running DBCC SHRINKFILE does nothing as there is no free space
to be recovered.
This whole thing smells like a bug.
If I run SELECT * FROM sys.databases, it says the log_reuse_wait_desc
is "REPLICATION".
Again, this database (or any other databases on the server) has never
had replication setup!
I tried running exec sp_removedbreplication - it completes, but the
log_reuse_wait_desc is still "REPLICATION" and DBCC OPENTRAN still
gives:
Transaction information for database 'xxxxx'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (42372:54:2)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I think we are going to resort to setting up replication on it, run
sp_repldone NULL, NULL, 0, 0, 1 and finally remove the replication...
David
On Nov 9, 7:30 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> What does the DBCC command give after you backup the log?
> Use DBCC SHRINKFILE to shrink the file rather than Enterprise Manager, you
> should not shrink it to a size where under normal operations it will expand.
> John|||Hi David
I am not sure how that has occurred, but you could try rebuilding the log
file.
John
"David Conte" wrote:
> Log still shows minimal free space even after transaction logs.
> Hence, running DBCC SHRINKFILE does nothing as there is no free space
> to be recovered.
> This whole thing smells like a bug.
> If I run SELECT * FROM sys.databases, it says the log_reuse_wait_desc
> is "REPLICATION".
> Again, this database (or any other databases on the server) has never
> had replication setup!
> I tried running exec sp_removedbreplication - it completes, but the
> log_reuse_wait_desc is still "REPLICATION" and DBCC OPENTRAN still
> gives:
> Transaction information for database 'xxxxx'.
> Replicated Transaction Information:
> Oldest distributed LSN : (0:0:0)
> Oldest non-distributed LSN : (42372:54:2)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> I think we are going to resort to setting up replication on it, run
> sp_repldone NULL, NULL, 0, 0, 1 and finally remove the replication...
> David
> On Nov 9, 7:30 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > Hi
> >
> > What does the DBCC command give after you backup the log?
> >
> > Use DBCC SHRINKFILE to shrink the file rather than Enterprise Manager, you
> > should not shrink it to a size where under normal operations it will expand.
> >
> > John
>|||Ok well I found a forum post here on an identical issue.
Sounds like it is indeed an SQL Server bug.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=654902&SiteID=1
The workaround (which I've managed to do on a test restore of the live
database which experiences the same issue) is to setup replication on
the database, then remove the publication. After this there was now
99% free space in the logfile and I was able to shrink it from 44 GB
to 1 GB.
DBCC OPENTRAN now says there are no active transactions, and the
log_resuse_wait_desc for the db is "NOTHING" instead of "REPLICATION".
So looking good again.
Now to do the process on the live database :)
Thanks everyone for their time!
Dave|||Hi David
According to the post the bug may have been fixed in SP2, if you are not
running this version you may want to upgrade to SP2 plus hotfixes.
John
"David Conte" wrote:
> Ok well I found a forum post here on an identical issue.
> Sounds like it is indeed an SQL Server bug.
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=654902&SiteID=1
> The workaround (which I've managed to do on a test restore of the live
> database which experiences the same issue) is to setup replication on
> the database, then remove the publication. After this there was now
> 99% free space in the logfile and I was able to shrink it from 44 GB
> to 1 GB.
> DBCC OPENTRAN now says there are no active transactions, and the
> log_resuse_wait_desc for the db is "NOTHING" instead of "REPLICATION".
> So looking good again.
> Now to do the process on the live database :)
> Thanks everyone for their time!
> Dave
>

Log file not freeing space (FULL recovery model) - even after transaction log backups

Hey all,
I have a site that has their log file at 40 gig (running SQL Server
2005).
The database is set to FULL recovery model, and transaction log
backups are performed every 3 hours.
The log file is HUGE compared to usual and I need to shrink it down.
However, it has no free space in it which is very strange considering
we are backing up the logs every 3 hours.
I have full backups occuring nightly.
I also tried switching to SIMPLE recovery model and shrinking the log
file.
Obviously this doesn't work as there isn't any free space in the log.
The site did have some db corruption issues a few weeks ago due to a
SAN issue.
The hardware has been repaired as well as the corruptions (they were
index corruptions so we rebuilt the indexes).
Could this have caused the transaction log to have issues to and not
free comitted transactions?
We have many other sites who have small transaction logs and I am at a
loss as to why it is so large.
The database is also 40 gig (and thus backups are 80+ gig in size!)
Anything I can try?
I am concerned about trashing the log by detaching / reattaching the
db without the log file as it seems brutal, but is that the only
choice?
Thanks,
DavidHow have you determined that there is 'no free space in it'' Did you
execute dbcc sqlperf(logspace)? or simply try to shrink it and it didn't
reduce in physical size? There is some very complicated stuff about the
internals of the log file that can prevent it from shrinking (much) even if
it has virtually no information in it. Search the web for sql server log
file shrink and you will find a number of helpful scripts to get over this
situation.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"David Conte" <davconts@.gmail.com> wrote in message
news:1194500150.103297.201900@.y27g2000pre.googlegroups.com...
> Hey all,
> I have a site that has their log file at 40 gig (running SQL Server
> 2005).
> The database is set to FULL recovery model, and transaction log
> backups are performed every 3 hours.
> The log file is HUGE compared to usual and I need to shrink it down.
> However, it has no free space in it which is very strange considering
> we are backing up the logs every 3 hours.
> I have full backups occuring nightly.
> I also tried switching to SIMPLE recovery model and shrinking the log
> file.
> Obviously this doesn't work as there isn't any free space in the log.
> The site did have some db corruption issues a few weeks ago due to a
> SAN issue.
> The hardware has been repaired as well as the corruptions (they were
> index corruptions so we rebuilt the indexes).
> Could this have caused the transaction log to have issues to and not
> free comitted transactions?
> We have many other sites who have small transaction logs and I am at a
> loss as to why it is so large.
> The database is also 40 gig (and thus backups are 80+ gig in size!)
> Anything I can try?
> I am concerned about trashing the log by detaching / reattaching the
> db without the log file as it seems brutal, but is that the only
> choice?
> Thanks,
> David
>|||Thanks for the info John and Kevin.
I didn't think that there could be an open transaction.. so tried DBCC
OPENTRAN.
Running DBCC OPENTRAN gives:
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (42372:54:2)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Sounds like this has to do with replication? (excuse my ignorance
here!)
We don't have replication setup.
Kevin - I hadn't run dbcc sqlperf(logspace) however when shrinking a
log file via Management Studio it gives the free space percentage
there before attempting the shrink. Just to confirm, I ran the SQL and
it gives:
41297.24 99.79205% full
Thanks
David
On Nov 9, 12:43 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> How have you determined that there is 'no free space in it'' Did you
> execute dbcc sqlperf(logspace)? or simply try to shrink it and it didn't
> reduce in physical size? There is some very complicated stuff about the
> internals of the log file that can prevent it from shrinking (much) even i
f
> it has virtually no information in it. Search the web for sql server log
> file shrink and you will find a number of helpful scripts to get over this
> situation.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> "David Conte" <davco...@.gmail.com> wrote in message
> news:1194500150.103297.201900@.y27g2000pre.googlegroups.com...
>
>
>
>
>
>
>

Log file growth concern

I am getting a bit concerned with the size of my log file and my understanding of backups and how the log file should be getting reduced in size. I have a production database that is 12 GB and the log file is 275 GB. The database file is set to autogrow at 1 MB and unrestricted file growth. The log file is set to 10% file growth and restricted to 2,097,152 MB file growth. I perform a full database backup each night. I had thought that all transactions in the log file would be rolled into the database file and then the log file auto-truncated in size during the backup process. I have never seen a log file stay larger than the database file. Please advise how I may keep the log file size (growth) down. Thanks!

Forgot to mention that the database is in Full Recovery mode.

|||

there is something wrong in you backup stratergy. You need to relook it as soon as possible. You said it is in Full recovery model. So my first question is , do you take regular backup of Transaction Log(TL) . I doubt , you don't and that is the reason of this outgrown log file.

these are few guidelines

(a) first check whether you need Full recovery model , if not change it to Simple

(b) WHat is the Backup policy. if you are taking the TL backup then increase the frequency of the TL backup

(c) in any case this TL log size is not advisable. You will have to truncate and shrink it. After truncating and shrinking the first step should be a full backup. otherwise the backup chain will break and u will not be able to restore from TL bakcup.

Refer :

http://support.microsoft.com/kb/873235.

Madhu

|||

Running the T-SQL below shrunk the log file down to 1 MB. I am not doing TL backups at this time. I never did them in SQL2000, however it now looks like I need to re-examime SQL2005 requirements.

(a) It appears from the article you referenced that if I stay in Full Recovery mode then I will need to add maintenance to backup up Transaction logs, truncate transaction logs, and run update statistics daily.

(b) We backup databases to disk daily and these are written to tape the same night

(c) This solved the current large log file issue:

backup log [DSS] with truncate_only

dbcc shrinkfile(DSS_Log)

|||

You said you are in full recovery mode but you are not doing any log backups. So why are you in full recovery mode? What are the recovery requirements from the business? I'm guessing you may not be able to meet those requirements. If you can lose whatever data since your last full backup, you should be able to use simple recovery. If not then you need to be doing log backups. And just to clarify one other statement, keep in mind that truncating the log is not physically shrinking the log. It's good to understand that whole concept. Books Online covers it pretty well under Truncating the Transaction Log:
http://msdn2.microsoft.com/en-us/library/ms189085.aspx

Another thing to keep in mind in that regularly performing physical shrinks of your logs is not necessarily a good idea. You should manage the log size with your transacation log backups. You don't want the logs continually growing, shrinking, etc. You want the logs to be at the appropriate size need for the database activities and managed with log backups. Read the following article about shrinking files:

http://www.karaszi.com/sqlserver/info_dont_shrink.asp

-Sue

|||

Thank you for the clarification Sue. Your comments have helped me to wrap my mind around how backup strategies work in SQL2005. A full database back up to disk is performed each night and then written to tape. In the event of a failure, we would only lose 24 hours which at this time is acceptable to the company. To provide better recovery I believe I will perform full backups several times throughout the day.

With this said, it would appear that a good strategy for us would be to change the recovery mode to simple, to manage the transaction log files size, and perform full database backups every 2 hours.

Thanks again!

Log File Backups

I am running regular maintenance jobs to back up database files nightly and
incremental log file backups every hour between 6am and 8pm. I notice that
the first log file backup seem to be extremely large 5gig, the database
backup that is created 7 hours prior to that is 4.1 gig. (SQL2000
standard - SP4) Can someone tell me if there is a better way to run these
job and what are the best parameters to include in a job to run this type of
job ? OR is it a normal thing for the first log file backup to be so
huge? Thanks,...
WANNABE wrote:
> I am running regular maintenance jobs to back up database files nightly and
> incremental log file backups every hour between 6am and 8pm. I notice that
> the first log file backup seem to be extremely large 5gig, the database
> backup that is created 7 hours prior to that is 4.1 gig. (SQL2000
> standard - SP4) Can someone tell me if there is a better way to run these
> job and what are the best parameters to include in a job to run this type of
> job ? OR is it a normal thing for the first log file backup to be so
> huge? Thanks,...
>
If there is alot of activity in the database during the 7 hours between
the full backup and the first log backup, then yes, it will be large.
What processes are run against that database overnight?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Yes, there are about 15 Stored Procs that run during the night to
recalculate
values, and adjustments.. But for the log backup to be larger then the db
backup
I was puzzled. Thanks
==============================================
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45534747.70107@.realsqlguy.com...
> WANNABE wrote:
> If there is alot of activity in the database during the 7 hours between
> the full backup and the first log backup, then yes, it will be large. What
> processes are run against that database overnight?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||WANNABE wrote:
> Yes, there are about 15 Stored Procs that run during the night to
> recalculate
> values, and adjustments.. But for the log backup to be larger then the db
> backup
> I was puzzled. Thanks
The log file is basically a "journal", recording everything that happens
in the database. Those journal entries continue to accumulate until you
perform a log backup, at which point they are flushed out. You might
consider doing log backups around the clock, rather than just between
6am and 8pm...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thank you, I will try log file backups around the clock
==========================================
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45534CF9.2040700@.realsqlguy.com...
> WANNABE wrote:
> The log file is basically a "journal", recording everything that happens
> in the database. Those journal entries continue to accumulate until you
> perform a log backup, at which point they are flushed out. You might
> consider doing log backups around the clock, rather than just between 6am
> and 8pm...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Log File Backups

I am running regular maintenance jobs to back up database files nightly and
incremental log file backups every hour between 6am and 8pm. I notice that
the first log file backup seem to be extremely large 5gig, the database
backup that is created 7 hours prior to that is 4.1 gig. (SQL2000
standard - SP4) Can someone tell me if there is a better way to run these
job and what are the best parameters to include in a job to run this type of
job ' OR is it a normal thing for the first log file backup to be so
huge? Thanks,...WANNABE wrote:
> I am running regular maintenance jobs to back up database files nightly and
> incremental log file backups every hour between 6am and 8pm. I notice that
> the first log file backup seem to be extremely large 5gig, the database
> backup that is created 7 hours prior to that is 4.1 gig. (SQL2000
> standard - SP4) Can someone tell me if there is a better way to run these
> job and what are the best parameters to include in a job to run this type of
> job ' OR is it a normal thing for the first log file backup to be so
> huge? Thanks,...
>
If there is alot of activity in the database during the 7 hours between
the full backup and the first log backup, then yes, it will be large.
What processes are run against that database overnight?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes, there are about 15 Stored Procs that run during the night to
recalculate
values, and adjustments.. But for the log backup to be larger then the db
backup
I was puzzled. Thanks
=============================================="Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45534747.70107@.realsqlguy.com...
> WANNABE wrote:
>> I am running regular maintenance jobs to back up database files nightly
>> and incremental log file backups every hour between 6am and 8pm. I
>> notice that the first log file backup seem to be extremely large 5gig,
>> the database backup that is created 7 hours prior to that is 4.1 gig.
>> (SQL2000 standard - SP4) Can someone tell me if there is a better way
>> to run these job and what are the best parameters to include in a job to
>> run this type of job ' OR is it a normal thing for the first log file
>> backup to be so huge? Thanks,...
> If there is alot of activity in the database during the 7 hours between
> the full backup and the first log backup, then yes, it will be large. What
> processes are run against that database overnight?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||WANNABE wrote:
> Yes, there are about 15 Stored Procs that run during the night to
> recalculate
> values, and adjustments.. But for the log backup to be larger then the db
> backup
> I was puzzled. Thanks
The log file is basically a "journal", recording everything that happens
in the database. Those journal entries continue to accumulate until you
perform a log backup, at which point they are flushed out. You might
consider doing log backups around the clock, rather than just between
6am and 8pm...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you, I will try log file backups around the clock
=========================================="Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45534CF9.2040700@.realsqlguy.com...
> WANNABE wrote:
>> Yes, there are about 15 Stored Procs that run during the night to
>> recalculate
>> values, and adjustments.. But for the log backup to be larger then the
>> db backup
>> I was puzzled. Thanks
> The log file is basically a "journal", recording everything that happens
> in the database. Those journal entries continue to accumulate until you
> perform a log backup, at which point they are flushed out. You might
> consider doing log backups around the clock, rather than just between 6am
> and 8pm...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Log File Backups

I am running regular maintenance jobs to back up database files nightly and
incremental log file backups every hour between 6am and 8pm. I notice that
the first log file backup seem to be extremely large 5gig, the database
backup that is created 7 hours prior to that is 4.1 gig. (SQL2000
standard - SP4) Can someone tell me if there is a better way to run these
job and what are the best parameters to include in a job to run this type of
job ' OR is it a normal thing for the first log file backup to be so
huge? Thanks,...WANNABE wrote:
> I am running regular maintenance jobs to back up database files nightly an
d
> incremental log file backups every hour between 6am and 8pm. I notice tha
t
> the first log file backup seem to be extremely large 5gig, the database
> backup that is created 7 hours prior to that is 4.1 gig. (SQL2000
> standard - SP4) Can someone tell me if there is a better way to run thes
e
> job and what are the best parameters to include in a job to run this type
of
> job ' OR is it a normal thing for the first log file backup to be so
> huge? Thanks,...
>
If there is alot of activity in the database during the 7 hours between
the full backup and the first log backup, then yes, it will be large.
What processes are run against that database overnight?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes, there are about 15 Stored Procs that run during the night to
recalculate
values, and adjustments.. But for the log backup to be larger then the db
backup
I was puzzled. Thanks
========================================
======
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45534747.70107@.realsqlguy.com...
> WANNABE wrote:
> If there is alot of activity in the database during the 7 hours between
> the full backup and the first log backup, then yes, it will be large. What
> processes are run against that database overnight?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||WANNABE wrote:
> Yes, there are about 15 Stored Procs that run during the night to
> recalculate
> values, and adjustments.. But for the log backup to be larger then the db
> backup
> I was puzzled. Thanks
The log file is basically a "journal", recording everything that happens
in the database. Those journal entries continue to accumulate until you
perform a log backup, at which point they are flushed out. You might
consider doing log backups around the clock, rather than just between
6am and 8pm...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you, I will try log file backups around the clock
========================================
==
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45534CF9.2040700@.realsqlguy.com...
> WANNABE wrote:
> The log file is basically a "journal", recording everything that happens
> in the database. Those journal entries continue to accumulate until you
> perform a log backup, at which point they are flushed out. You might
> consider doing log backups around the clock, rather than just between 6am
> and 8pm...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

log file

I was out of town for a few days and when I got back found that my
transaction log backups had been failing. Nice. Anyway I now have a big ass
transaction log that I need to back up. I seem to recall an article a while
ago that if you set a database offline, it shuts it down and commits all the
transactions in the log file. You could then delete the log file and when you
bring the database back online it will create a new empty log file. Is this
true all you people who are WAY smarter than me? (You don't see me answering
question now do you?)
TomWhat I would do is just try to backup the transaction log. Is there a disk
space problem? What about some location on the network?
Ben Nevarez, MCDBA, OCP
Database Administrator
"rk rider" wrote:
> I was out of town for a few days and when I got back found that my
> transaction log backups had been failing. Nice. Anyway I now have a big ass
> transaction log that I need to back up. I seem to recall an article a while
> ago that if you set a database offline, it shuts it down and commits all the
> transactions in the log file. You could then delete the log file and when you
> bring the database back online it will create a new empty log file. Is this
> true all you people who are WAY smarter than me? (You don't see me answering
> question now do you?)
> Tom|||yeah, space is an issue. even if I back up the log it's still gonna have this
big empty tranaction log file out there taking up disk. Thanks for your
reply. Any one have a an answer for the specific scenario I outlined?
"Ben Nevarez" wrote:
> What I would do is just try to backup the transaction log. Is there a disk
> space problem? What about some location on the network?
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "rk rider" wrote:
> > I was out of town for a few days and when I got back found that my
> > transaction log backups had been failing. Nice. Anyway I now have a big ass
> > transaction log that I need to back up. I seem to recall an article a while
> > ago that if you set a database offline, it shuts it down and commits all the
> > transactions in the log file. You could then delete the log file and when you
> > bring the database back online it will create a new empty log file. Is this
> > true all you people who are WAY smarter than me? (You don't see me answering
> > question now do you?)
> >
> > Tom|||"rk rider" <rkrider@.discussions.microsoft.com> wrote in message
news:2674C20F-453D-4D81-82A9-BF3A65BCDF4E@.microsoft.com...
> yeah, space is an issue. even if I back up the log it's still gonna have
> this
> big empty tranaction log file out there taking up disk. Thanks for your
> reply. Any one have a an answer for the specific scenario I outlined?
>
Back up the log file, then shrink it.
David|||Here is the article I was looking for. Anyone have any comments?
http://www.databasejournal.com/features/mssql/article.php/1460151
"rk rider" wrote:
> yeah, space is an issue. even if I back up the log it's still gonna have this
> big empty tranaction log file out there taking up disk. Thanks for your
> reply. Any one have a an answer for the specific scenario I outlined?
> "Ben Nevarez" wrote:
> >
> > What I would do is just try to backup the transaction log. Is there a disk
> > space problem? What about some location on the network?
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "rk rider" wrote:
> >
> > > I was out of town for a few days and when I got back found that my
> > > transaction log backups had been failing. Nice. Anyway I now have a big ass
> > > transaction log that I need to back up. I seem to recall an article a while
> > > ago that if you set a database offline, it shuts it down and commits all the
> > > transactions in the log file. You could then delete the log file and when you
> > > bring the database back online it will create a new empty log file. Is this
> > > true all you people who are WAY smarter than me? (You don't see me answering
> > > question now do you?)
> > >
> > > Tom|||Yeah, got that. Not the question I was asking however but thanks for taking
the time to reply.
"David Browne" wrote:
> "rk rider" <rkrider@.discussions.microsoft.com> wrote in message
> news:2674C20F-453D-4D81-82A9-BF3A65BCDF4E@.microsoft.com...
> > yeah, space is an issue. even if I back up the log it's still gonna have
> > this
> > big empty tranaction log file out there taking up disk. Thanks for your
> > reply. Any one have a an answer for the specific scenario I outlined?
> >
> Back up the log file, then shrink it.
> David
>
>|||"rk rider" <rkrider@.discussions.microsoft.com> wrote in message
news:DCA8DF35-A5B4-4EA8-A2C4-7588E06D74AD@.microsoft.com...
> Here is the article I was looking for. Anyone have any comments?
> http://www.databasejournal.com/features/mssql/article.php/1460151
>
Yes. Don't do that. It breaks the log chain. So long as your log chan is
unbroken, you can restore your database even if you loose a full backup.
David|||I would never dare to just delete the log file for a database, detached or not. I've seen way too
many posts here where they just won't get the database into SQL Server after such operations. Call
me paranoid, if you wish... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rk rider" <rkrider@.discussions.microsoft.com> wrote in message
news:DCA8DF35-A5B4-4EA8-A2C4-7588E06D74AD@.microsoft.com...
> Here is the article I was looking for. Anyone have any comments?
> http://www.databasejournal.com/features/mssql/article.php/1460151
> "rk rider" wrote:
>> yeah, space is an issue. even if I back up the log it's still gonna have this
>> big empty tranaction log file out there taking up disk. Thanks for your
>> reply. Any one have a an answer for the specific scenario I outlined?
>> "Ben Nevarez" wrote:
>> >
>> > What I would do is just try to backup the transaction log. Is there a disk
>> > space problem? What about some location on the network?
>> >
>> > Ben Nevarez, MCDBA, OCP
>> > Database Administrator
>> >
>> >
>> > "rk rider" wrote:
>> >
>> > > I was out of town for a few days and when I got back found that my
>> > > transaction log backups had been failing. Nice. Anyway I now have a big ass
>> > > transaction log that I need to back up. I seem to recall an article a while
>> > > ago that if you set a database offline, it shuts it down and commits all the
>> > > transactions in the log file. You could then delete the log file and when you
>> > > bring the database back online it will create a new empty log file. Is this
>> > > true all you people who are WAY smarter than me? (You don't see me answering
>> > > question now do you?)
>> > >
>> > > Tom|||"rk rider" <rkrider@.discussions.microsoft.com> wrote in message
news:06E6E944-E4EE-4BF1-9652-2EC49B4A5160@.microsoft.com...
> I was out of town for a few days and when I got back found that my
> transaction log backups had been failing. Nice. Anyway I now have a big
ass
> transaction log that I need to back up. I seem to recall an article a
while
> ago that if you set a database offline, it shuts it down and commits all
the
> transactions in the log file. You could then delete the log file and when
you
> bring the database back online it will create a new empty log file. Is
this
> true all you people who are WAY smarter than me? (You don't see me
answering
> question now do you?)
I would simply take the size hit and backup the log and keep your
transaction log chain intact.
> Tom|||"rk rider" <rkrider@.discussions.microsoft.com> wrote in message
news:DCA8DF35-A5B4-4EA8-A2C4-7588E06D74AD@.microsoft.com...
> Here is the article I was looking for. Anyone have any comments?
> http://www.databasejournal.com/features/mssql/article.php/1460151
Yes.
Don't do it.
While this "usually" works, my understanding is Microsoft does NOT support
this.
And this would completely invalidate your transaction log backup chain
anyway.
You're better off doing a:
backup log <foo> with truncate_only
then a FULL backup and then resume your log backups.
> "rk rider" wrote:
> > yeah, space is an issue. even if I back up the log it's still gonna have
this
> > big empty tranaction log file out there taking up disk. Thanks for your
> > reply. Any one have a an answer for the specific scenario I outlined?
> >
> > "Ben Nevarez" wrote:
> >
> > >
> > > What I would do is just try to backup the transaction log. Is there a
disk
> > > space problem? What about some location on the network?
> > >
> > > Ben Nevarez, MCDBA, OCP
> > > Database Administrator
> > >
> > >
> > > "rk rider" wrote:
> > >
> > > > I was out of town for a few days and when I got back found that my
> > > > transaction log backups had been failing. Nice. Anyway I now have a
big ass
> > > > transaction log that I need to back up. I seem to recall an article
a while
> > > > ago that if you set a database offline, it shuts it down and commits
all the
> > > > transactions in the log file. You could then delete the log file and
when you
> > > > bring the database back online it will create a new empty log file.
Is this
> > > > true all you people who are WAY smarter than me? (You don't see me
answering
> > > > question now do you?)
> > > >
> > > > Tom|||Thanks to all replied. I tried the method outlined in the article on my test
database and it worked fine. However everyone made me gun shy about trying in
my production database since I hadn't had a backup for several days. The
transaction log had grown to 50 gig so I did have enough free space in my
cluster to back it up. So...I installed SQL litespeed eval. Backed up the
log, did a shrinkfile, then did a full backup and got my transaction log
backups going again. We have over 100,000k transactions a day so our data
ages very quickly so I wasn't really worried about the log chain at this
point. Thanks again everyone for taking the time to respond
"Greg D. Moore (Strider)" wrote:
> "rk rider" <rkrider@.discussions.microsoft.com> wrote in message
> news:06E6E944-E4EE-4BF1-9652-2EC49B4A5160@.microsoft.com...
> > I was out of town for a few days and when I got back found that my
> > transaction log backups had been failing. Nice. Anyway I now have a big
> ass
> > transaction log that I need to back up. I seem to recall an article a
> while
> > ago that if you set a database offline, it shuts it down and commits all
> the
> > transactions in the log file. You could then delete the log file and when
> you
> > bring the database back online it will create a new empty log file. Is
> this
> > true all you people who are WAY smarter than me? (You don't see me
> answering
> > question now do you?)
> I would simply take the size hit and backup the log and keep your
> transaction log chain intact.
>
> >
> > Tom
>
>

log file

I was out of town for a few days and when I got back found that my
transaction log backups had been failing. Nice. Anyway I now have a big XXX
transaction log that I need to back up. I seem to recall an article a while
ago that if you set a database offline, it shuts it down and commits all the
transactions in the log file. You could then delete the log file and when yo
u
bring the database back online it will create a new empty log file. Is this
true all you people who are WAY smarter than me? (You don't see me answering
question now do you?)
TomWhat I would do is just try to backup the transaction log. Is there a disk
space problem? What about some location on the network?
Ben Nevarez, MCDBA, OCP
Database Administrator
"rk rider" wrote:

> I was out of town for a few days and when I got back found that my
> transaction log backups had been failing. Nice. Anyway I now have a big as
s
> transaction log that I need to back up. I seem to recall an article a whil
e
> ago that if you set a database offline, it shuts it down and commits all t
he
> transactions in the log file. You could then delete the log file and when
you
> bring the database back online it will create a new empty log file. Is thi
s
> true all you people who are WAY smarter than me? (You don't see me answeri
ng
> question now do you?)
> Tom|||yeah, space is an issue. even if I back up the log it's still gonna have thi
s
big empty tranaction log file out there taking up disk. Thanks for your
reply. Any one have a an answer for the specific scenario I outlined?
"Ben Nevarez" wrote:
[vbcol=seagreen]
> What I would do is just try to backup the transaction log. Is there a disk
> space problem? What about some location on the network?
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "rk rider" wrote:
>|||"rk rider" <rkrider@.discussions.microsoft.com> wrote in message
news:2674C20F-453D-4D81-82A9-BF3A65BCDF4E@.microsoft.com...
> yeah, space is an issue. even if I back up the log it's still gonna have
> this
> big empty tranaction log file out there taking up disk. Thanks for your
> reply. Any one have a an answer for the specific scenario I outlined?
>
Back up the log file, then shrink it.
David|||Here is the article I was looking for. Anyone have any comments?
http://www.databasejournal.com/feat...cle.php/1460151
"rk rider" wrote:
[vbcol=seagreen]
> yeah, space is an issue. even if I back up the log it's still gonna have t
his
> big empty tranaction log file out there taking up disk. Thanks for your
> reply. Any one have a an answer for the specific scenario I outlined?
> "Ben Nevarez" wrote:
>|||Yeah, got that. Not the question I was asking however but thanks for taking
the time to reply.
"David Browne" wrote:

> "rk rider" <rkrider@.discussions.microsoft.com> wrote in message
> news:2674C20F-453D-4D81-82A9-BF3A65BCDF4E@.microsoft.com...
> Back up the log file, then shrink it.
> David
>
>|||"rk rider" <rkrider@.discussions.microsoft.com> wrote in message
news:DCA8DF35-A5B4-4EA8-A2C4-7588E06D74AD@.microsoft.com...
> Here is the article I was looking for. Anyone have any comments?
> http://www.databasejournal.com/feat...cle.php/1460151
>
Yes. Don't do that. It breaks the log chain. So long as your log chan is
unbroken, you can restore your database even if you loose a full backup.
David|||I would never dare to just delete the log file for a database, detached or n
ot. I've seen way too
many posts here where they just won't get the database into SQL Server after
such operations. Call
me paranoid, if you wish... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rk rider" <rkrider@.discussions.microsoft.com> wrote in message
news:DCA8DF35-A5B4-4EA8-A2C4-7588E06D74AD@.microsoft.com...[vbcol=seagreen]
> Here is the article I was looking for. Anyone have any comments?
> http://www.databasejournal.com/feat...cle.php/1460151
> "rk rider" wrote:
>|||"rk rider" <rkrider@.discussions.microsoft.com> wrote in message
news:06E6E944-E4EE-4BF1-9652-2EC49B4A5160@.microsoft.com...
> I was out of town for a few days and when I got back found that my
> transaction log backups had been failing. Nice. Anyway I now have a big
XXX
> transaction log that I need to back up. I seem to recall an article a
while
> ago that if you set a database offline, it shuts it down and commits all
the
> transactions in the log file. You could then delete the log file and when
you
> bring the database back online it will create a new empty log file. Is
this
> true all you people who are WAY smarter than me? (You don't see me
answering
> question now do you?)
I would simply take the size hit and backup the log and keep your
transaction log chain intact.

> Tom|||"rk rider" <rkrider@.discussions.microsoft.com> wrote in message
news:DCA8DF35-A5B4-4EA8-A2C4-7588E06D74AD@.microsoft.com...
> Here is the article I was looking for. Anyone have any comments?
> http://www.databasejournal.com/feat...cle.php/1460151
Yes.
Don't do it.
While this "usually" works, my understanding is Microsoft does NOT support
this.
And this would completely invalidate your transaction log backup chain
anyway.
You're better off doing a:
backup log <foo> with truncate_only
then a FULL backup and then resume your log backups.
[vbcol=seagreen]
> "rk rider" wrote:
>
this[vbcol=seagreen]
disk[vbcol=seagreen]
big XXX[vbcol=seagreen]
a while[vbcol=seagreen]
all the[vbcol=seagreen]
when you[vbcol=seagreen]
Is this[vbcol=seagreen]
answering[vbcol=seagreen]

Log Backups vs. Differential Backups

I was recently educated (thanks Dan) that you cannot create a backup of
a database if you lose your log file (due to drive failure of
corruption). Once I learned this, I began wondering about the merits of
even using a log file with the Full recovery model. Why not just use
the Simple recovery model and use frequent differential backups? Why go
through the overhead of log files if you can get almost the same level
of safety using frequent differntial backups?
Is doing differential backups every 15 minutes close enough to the
level of protection afforded by 15 minute log backups?
The way I see it is that the only thing I lose is
up-to-the-point-of-failure restoration. But, what I gain is not having
to worry about a corrupt log file or losing the drive that has the log
file on it. The processing time seems similar.
Am I wildly wrong anout this?
Thanks in advance for your opinions.
- SeanHi Sean,
Are you say that you would only lose you log file drive? What happens if
it's the data drive that goes? If you lose your log file of the database
and then detach and reattach the drive the systm will create a new log file.
Then applying the log backups will get you back to the point of failure -
the time to the last log file backup.
Backups are also used for non disaster situations and log file give you more
options there. I have always found log file backups quicker that diff
backups when it
comes to large databases
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegroups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>|||Greg - thanks for your reply. I was not able to rebuild a log file. I
took a sample database with a log file, stopped SQL server, deleted the
log file, and restarted SQL server. The DB was "Suspect". I was unable
to get a new log to build with any consistency - I played with setting
the DB to "Emergency Mode" and messed with detaching/reattaching and
would get it to rebuild a log file 50% of the time - still, the problem
is by losing your log file, the data becomes suspect, and you can't
trust it even if you rebuild the log.
I understand how log file backups can get you to the point of failure
if the data drive fails - but I was concerned with the log drive
failing. This led me to post on the topic: "Log Backups - Why bother
with every ten minutes?" There I learned from Dan that if you lose a
log file, your database is hosed, and you can only restore to your last
backup.
This was a disappointment to me - I really like the idea that we can
restore to the point of failure using logs if the data drive fails -
but if the log drive fails, we are right back to only getting back what
we have as a "backup".
So - I am thinking that differentials in "Simple" recovery are pretty
close to as good as log file backups in "Full" Recovery mode. Granted,
you can't restore to the point of failure EVER - but you aren't that
much worse off if the backups are frequent enough.
You mention that log file backups are quicker than differentials. A
little quicker? or significantly quicker? The database sizes I am
concerned with are ~35 Gig. The full backup process to disk takes less
than 10 minutes. Diffs and log backups are much faster, naturally.
Are there other problems with diffs that I am unaware of - locking or
something?|||Hi Sean,
In the situation you have describe to create a suspect database I was not
able to reproduct the issue. Having said that it doesn't really matter.
Any backup system that you employ you should follow the MS recommendation .
They are that transaction logs and Data should be protected while on disk.
That is with a form of RAID. While it is unlikely that a disk goes bad is
is very unlikely that two or more will go bad at the same time. So your
first line or protection is always a RAID system.
Next a diff backup is slower than a log backup. This is the bit from BOL
Note If you have created any file backups since the last full database
backup, those files will be scanned by Microsoft® SQL ServerT 2000 at the
beginning of a differential database backup. This may cause some degradation
of performance in the differential database backup. For more information,
see Using File Backups.
So to do a diff backup it needs to read the backup an check the pages that
have been backed up. Where as a log backup backups the entire log file
(pages). Somethime the LOG backup can be larger than the database bacup
because of high transactions but you can always reduce the time between
backups.
In addition having log backups and transaction logs (Full Recovery Mode)
always the use of log reader type productions to see and recover cahnges to
the data without restoring. This might not be an issue now but at sometime
we have all been asked to get back some details that were changed wrongly.
Backup don't tend to cause locking problems for long periods as they backup
pages and do so quickly.
My recommendation is to continue with log backups , ensure that the data and
log files are on RAID systems (RAID 1 for log, RAID 5 for data is typically
good)
Send your backups and log backups to another server via a script See this
link http://www.sql-scripts.com/members/ScriptDetails.aspx?S_ID=4
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124760340.712510.321430@.g47g2000cwa.googlegroups.com...
> Greg - thanks for your reply. I was not able to rebuild a log file. I
> took a sample database with a log file, stopped SQL server, deleted the
> log file, and restarted SQL server. The DB was "Suspect". I was unable
> to get a new log to build with any consistency - I played with setting
> the DB to "Emergency Mode" and messed with detaching/reattaching and
> would get it to rebuild a log file 50% of the time - still, the problem
> is by losing your log file, the data becomes suspect, and you can't
> trust it even if you rebuild the log.
> I understand how log file backups can get you to the point of failure
> if the data drive fails - but I was concerned with the log drive
> failing. This led me to post on the topic: "Log Backups - Why bother
> with every ten minutes?" There I learned from Dan that if you lose a
> log file, your database is hosed, and you can only restore to your last
> backup.
> This was a disappointment to me - I really like the idea that we can
> restore to the point of failure using logs if the data drive fails -
> but if the log drive fails, we are right back to only getting back what
> we have as a "backup".
> So - I am thinking that differentials in "Simple" recovery are pretty
> close to as good as log file backups in "Full" Recovery mode. Granted,
> you can't restore to the point of failure EVER - but you aren't that
> much worse off if the backups are frequent enough.
> You mention that log file backups are quicker than differentials. A
> little quicker? or significantly quicker? The database sizes I am
> concerned with are ~35 Gig. The full backup process to disk takes less
> than 10 minutes. Diffs and log backups are much faster, naturally.
> Are there other problems with diffs that I am unaware of - locking or
> something?
>|||Point in time restore can be vary valuable. Another thing that log backups allow you to do is to
backup the log after the data file is lost (or corrupted). Read about the NO_TRUNCATE option to the
BACKUP LOG command. I think you should ask yourself: "What advantages does db and diff backups have
in contrast to db and log backups?". I always go for log backups and only *complement* with diff
backups when needed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegroups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>|||I think Tibor's approach is a good one - use a combination of differential
database backups as well as log backups. This will simplify your recovery
procedure and still allow you to recover to the point of failure if you lose
data files.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegroups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>|||Thanks to all.
Greg - knowing that the differential backups have to read the full
backup was a key piece of information in understanding why it is slower
than Log Backups. I'll look into this more thoroughly.
Tibor/Dan - I understand that using all three (full backups,
differentials and Log backups) is the recommended practice - and I
understand how it helps you recover to the point of failure.
What still disappoints me is the fact that losing the log drive makes
you lose the database. It undermines my desire to go through the setup
hassle and expense of keeping and maintaing a transaction log. I have
to setup clients with our products database, and train them to do
backups. none of them are DBAs - many are at the level of novice. The
log backups invariably cause problems, and the drawbacks seem to
outweigh the benefits in these cases.
For myself, in a large installation - I would certainly keep a log and
do log backups - but I'm not sure I can gleefully recommend the same
practice to my clients.
Thanks again for the discussion - I appreciate it.
- Sean

Log Backups vs. Differential Backups

I was recently educated (thanks Dan) that you cannot create a backup of
a database if you lose your log file (due to drive failure of
corruption). Once I learned this, I began wondering about the merits of
even using a log file with the Full recovery model. Why not just use
the Simple recovery model and use frequent differential backups? Why go
through the overhead of log files if you can get almost the same level
of safety using frequent differntial backups?
Is doing differential backups every 15 minutes close enough to the
level of protection afforded by 15 minute log backups?
The way I see it is that the only thing I lose is
up-to-the-point-of-failure restoration. But, what I gain is not having
to worry about a corrupt log file or losing the drive that has the log
file on it. The processing time seems similar.
Am I wildly wrong anout this?
Thanks in advance for your opinions.
- Sean
Hi Sean,
Are you say that you would only lose you log file drive? What happens if
it's the data drive that goes? If you lose your log file of the database
and then detach and reattach the drive the systm will create a new log file.
Then applying the log backups will get you back to the point of failure -
the time to the last log file backup.
Backups are also used for non disaster situations and log file give you more
options there. I have always found log file backups quicker that diff
backups when it
comes to large databases
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegro ups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>
|||Greg - thanks for your reply. I was not able to rebuild a log file. I
took a sample database with a log file, stopped SQL server, deleted the
log file, and restarted SQL server. The DB was "Suspect". I was unable
to get a new log to build with any consistency - I played with setting
the DB to "Emergency Mode" and messed with detaching/reattaching and
would get it to rebuild a log file 50% of the time - still, the problem
is by losing your log file, the data becomes suspect, and you can't
trust it even if you rebuild the log.
I understand how log file backups can get you to the point of failure
if the data drive fails - but I was concerned with the log drive
failing. This led me to post on the topic: "Log Backups - Why bother
with every ten minutes?" There I learned from Dan that if you lose a
log file, your database is hosed, and you can only restore to your last
backup.
This was a disappointment to me - I really like the idea that we can
restore to the point of failure using logs if the data drive fails -
but if the log drive fails, we are right back to only getting back what
we have as a "backup".
So - I am thinking that differentials in "Simple" recovery are pretty
close to as good as log file backups in "Full" Recovery mode. Granted,
you can't restore to the point of failure EVER - but you aren't that
much worse off if the backups are frequent enough.
You mention that log file backups are quicker than differentials. A
little quicker? or significantly quicker? The database sizes I am
concerned with are ~35 Gig. The full backup process to disk takes less
than 10 minutes. Diffs and log backups are much faster, naturally.
Are there other problems with diffs that I am unaware of - locking or
something?
|||Hi Sean,
In the situation you have describe to create a suspect database I was not
able to reproduct the issue. Having said that it doesn't really matter.
Any backup system that you employ you should follow the MS recommendation .
They are that transaction logs and Data should be protected while on disk.
That is with a form of RAID. While it is unlikely that a disk goes bad is
is very unlikely that two or more will go bad at the same time. So your
first line or protection is always a RAID system.
Next a diff backup is slower than a log backup. This is the bit from BOL
Note If you have created any file backups since the last full database
backup, those files will be scanned by Microsoft SQL ServerT 2000 at the
beginning of a differential database backup. This may cause some degradation
of performance in the differential database backup. For more information,
see Using File Backups.
So to do a diff backup it needs to read the backup an check the pages that
have been backed up. Where as a log backup backups the entire log file
(pages). Somethime the LOG backup can be larger than the database bacup
because of high transactions but you can always reduce the time between
backups.
In addition having log backups and transaction logs (Full Recovery Mode)
always the use of log reader type productions to see and recover cahnges to
the data without restoring. This might not be an issue now but at sometime
we have all been asked to get back some details that were changed wrongly.
Backup don't tend to cause locking problems for long periods as they backup
pages and do so quickly.
My recommendation is to continue with log backups , ensure that the data and
log files are on RAID systems (RAID 1 for log, RAID 5 for data is typically
good)
Send your backups and log backups to another server via a script See this
link http://www.sql-scripts.com/members/S...ls.aspx?S_ID=4
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124760340.712510.321430@.g47g2000cwa.googlegr oups.com...
> Greg - thanks for your reply. I was not able to rebuild a log file. I
> took a sample database with a log file, stopped SQL server, deleted the
> log file, and restarted SQL server. The DB was "Suspect". I was unable
> to get a new log to build with any consistency - I played with setting
> the DB to "Emergency Mode" and messed with detaching/reattaching and
> would get it to rebuild a log file 50% of the time - still, the problem
> is by losing your log file, the data becomes suspect, and you can't
> trust it even if you rebuild the log.
> I understand how log file backups can get you to the point of failure
> if the data drive fails - but I was concerned with the log drive
> failing. This led me to post on the topic: "Log Backups - Why bother
> with every ten minutes?" There I learned from Dan that if you lose a
> log file, your database is hosed, and you can only restore to your last
> backup.
> This was a disappointment to me - I really like the idea that we can
> restore to the point of failure using logs if the data drive fails -
> but if the log drive fails, we are right back to only getting back what
> we have as a "backup".
> So - I am thinking that differentials in "Simple" recovery are pretty
> close to as good as log file backups in "Full" Recovery mode. Granted,
> you can't restore to the point of failure EVER - but you aren't that
> much worse off if the backups are frequent enough.
> You mention that log file backups are quicker than differentials. A
> little quicker? or significantly quicker? The database sizes I am
> concerned with are ~35 Gig. The full backup process to disk takes less
> than 10 minutes. Diffs and log backups are much faster, naturally.
> Are there other problems with diffs that I am unaware of - locking or
> something?
>
|||Point in time restore can be vary valuable. Another thing that log backups allow you to do is to
backup the log after the data file is lost (or corrupted). Read about the NO_TRUNCATE option to the
BACKUP LOG command. I think you should ask yourself: "What advantages does db and diff backups have
in contrast to db and log backups?". I always go for log backups and only *complement* with diff
backups when needed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegro ups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>
|||I think Tibor's approach is a good one - use a combination of differential
database backups as well as log backups. This will simplify your recovery
procedure and still allow you to recover to the point of failure if you lose
data files.
Hope this helps.
Dan Guzman
SQL Server MVP
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegro ups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>
|||Thanks to all.
Greg - knowing that the differential backups have to read the full
backup was a key piece of information in understanding why it is slower
than Log Backups. I'll look into this more thoroughly.
Tibor/Dan - I understand that using all three (full backups,
differentials and Log backups) is the recommended practice - and I
understand how it helps you recover to the point of failure.
What still disappoints me is the fact that losing the log drive makes
you lose the database. It undermines my desire to go through the setup
hassle and expense of keeping and maintaing a transaction log. I have
to setup clients with our products database, and train them to do
backups. none of them are DBAs - many are at the level of novice. The
log backups invariably cause problems, and the drawbacks seem to
outweigh the benefits in these cases.
For myself, in a large installation - I would certainly keep a log and
do log backups - but I'm not sure I can gleefully recommend the same
practice to my clients.
Thanks again for the discussion - I appreciate it.
- Sean