Log full

Hi! Please help with the following:
Our disk space is limited. I set the database recovery model to
"FULL", and backup transaction log every hour between business hours.
But the disk is always full because the transactions log growth. Do
you think increase the frequency of transaction log backup or backup
transaction 24 hours instead of only business hours will solve the
problem?
Thanks!!"Saiyou Anh" <wangc@.alexian.net> wrote in message
news:a51d3ca7.0310270725.45bf4877@.posting.google.c om...
> Hi! Please help with the following:
> Our disk space is limited. I set the database recovery model to
> "FULL", and backup transaction log every hour between business hours.
> But the disk is always full because the transactions log growth. Do
> you think increase the frequency of transaction log backup or backup
> transaction 24 hours instead of only business hours will solve the
> problem?

I think you're approaching this from the wrong direction. Your transaction
log should be backed up as often as your business rules dictate and the
diskspace sized appropriately.

You say "between business hours" I assume you mean during? (that logically
makes more sense, but what you wrote could be read as from 5:00 PM to 9:00
AM instead when presumably your log isn't really growing.)

I'd say your best bet is to purchase more disk space.

You also mention the disk is always full. Generally your database files are
far larger than your transaction log files, especially if you are doing
transaction backups on a regular basis. In addition a transaction log back
up does NOT shrink the physical disk file, only removes unused portions. So
it might be that your transaction log is far larger than it should be (say
one day you skipped transaction log backups and it swelled to 8 times its
normal size, now it's normally 8x bigger than it needs to be.)

Hope that helps.

> Thanks!!|||"Saiyou Anh" <wangc@.alexian.net> wrote in message
news:a51d3ca7.0310270725.45bf4877@.posting.google.c om...
> Hi! Please help with the following:
> Our disk space is limited. I set the database recovery model to
> "FULL", and backup transaction log every hour between business hours.
> But the disk is always full because the transactions log growth. Do
> you think increase the frequency of transaction log backup or backup
> transaction 24 hours instead of only business hours will solve the
> problem?

I think you're approaching this from the wrong direction. Your transaction
log should be backed up as often as your business rules dictate and the
diskspace sized appropriately.

You say "between business hours" I assume you mean during? (that logically
makes more sense, but what you wrote could be read as from 5:00 PM to 9:00
AM instead when presumably your log isn't really growing.)

I'd say your best bet is to purchase more disk space.

You also mention the disk is always full. Generally your database files are
far larger than your transaction log files, especially if you are doing
transaction backups on a regular basis. In addition a transaction log back
up does NOT shrink the physical disk file, only removes unused portions. So
it might be that your transaction log is far larger than it should be (say
one day you skipped transaction log backups and it swelled to 8 times its
normal size, now it's normally 8x bigger than it needs to be.)

Hope that helps.

> Thanks!!

Log for tempdb is full

I keep getting the following error in my SQL 2K SP3a server logs:
The log file for database 'tempdb' is full. Back up the transaction log for
the database to free up some log space..
But using the EM I cannot backup the transaction logs - the options are
grayed out. If I try to backup the tempdb through EM, I get an error that
Backup and Restore are not allowed on tempdb. I have the options set to
allow unlimited growth. There are many GB's of space on the drive. What is
the problem/solution?
TIA!
http://www.aspfaq.com/2446
http://www.aspfaq.com/
(Reverse address to reply.)
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:u95Znp3wEHA.2624@.TK2MSFTNGP11.phx.gbl...
> I keep getting the following error in my SQL 2K SP3a server logs:
> The log file for database 'tempdb' is full. Back up the transaction log
for
> the database to free up some log space..
> But using the EM I cannot backup the transaction logs - the options are
> grayed out. If I try to backup the tempdb through EM, I get an error that
> Backup and Restore are not allowed on tempdb. I have the options set to
> allow unlimited growth. There are many GB's of space on the drive. What is
> the problem/solution?
> TIA!
>
|||Perhaps there's code in some application that has an open transaction that
has run amok?
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:u95Znp3wEHA.2624@.TK2MSFTNGP11.phx.gbl...
> I keep getting the following error in my SQL 2K SP3a server logs:
> The log file for database 'tempdb' is full. Back up the transaction log
for
> the database to free up some log space..
> But using the EM I cannot backup the transaction logs - the options are
> grayed out. If I try to backup the tempdb through EM, I get an error that
> Backup and Restore are not allowed on tempdb. I have the options set to
> allow unlimited growth. There are many GB's of space on the drive. What is
> the problem/solution?
> TIA!
>

Log for tempdb is full

I keep getting the following error in my SQL 2K SP3a server logs:
The log file for database 'tempdb' is full. Back up the transaction log for
the database to free up some log space..
But using the EM I cannot backup the transaction logs - the options are
grayed out. If I try to backup the tempdb through EM, I get an error that
Backup and Restore are not allowed on tempdb. I have the options set to
allow unlimited growth. There are many GB's of space on the drive. What is
the problem/solution?
TIA!http://www.aspfaq.com/2446
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:u95Znp3wEHA.2624@.TK2MSFTNGP11.phx.gbl...
> I keep getting the following error in my SQL 2K SP3a server logs:
> The log file for database 'tempdb' is full. Back up the transaction log
for
> the database to free up some log space..
> But using the EM I cannot backup the transaction logs - the options are
> grayed out. If I try to backup the tempdb through EM, I get an error that
> Backup and Restore are not allowed on tempdb. I have the options set to
> allow unlimited growth. There are many GB's of space on the drive. What is
> the problem/solution?
> TIA!
>|||Perhaps there's code in some application that has an open transaction that
has run amok?
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:u95Znp3wEHA.2624@.TK2MSFTNGP11.phx.gbl...
> I keep getting the following error in my SQL 2K SP3a server logs:
> The log file for database 'tempdb' is full. Back up the transaction log
for
> the database to free up some log space..
> But using the EM I cannot backup the transaction logs - the options are
> grayed out. If I try to backup the tempdb through EM, I get an error that
> Backup and Restore are not allowed on tempdb. I have the options set to
> allow unlimited growth. There are many GB's of space on the drive. What is
> the problem/solution?
> TIA!
>

Log for tempdb is full

I keep getting the following error in my SQL 2K SP3a server logs:
The log file for database 'tempdb' is full. Back up the transaction log for
the database to free up some log space..
But using the EM I cannot backup the transaction logs - the options are
grayed out. If I try to backup the tempdb through EM, I get an error that
Backup and Restore are not allowed on tempdb. I have the options set to
allow unlimited growth. There are many GB's of space on the drive. What is
the problem/solution?
TIA!http://www.aspfaq.com/2446
http://www.aspfaq.com/
(Reverse address to reply.)
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:u95Znp3wEHA.2624@.TK2MSFTNGP11.phx.gbl...
> I keep getting the following error in my SQL 2K SP3a server logs:
> The log file for database 'tempdb' is full. Back up the transaction log
for
> the database to free up some log space..
> But using the EM I cannot backup the transaction logs - the options are
> grayed out. If I try to backup the tempdb through EM, I get an error that
> Backup and Restore are not allowed on tempdb. I have the options set to
> allow unlimited growth. There are many GB's of space on the drive. What is
> the problem/solution?
> TIA!
>|||Perhaps there's code in some application that has an open transaction that
has run amok?
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:u95Znp3wEHA.2624@.TK2MSFTNGP11.phx.gbl...
> I keep getting the following error in my SQL 2K SP3a server logs:
> The log file for database 'tempdb' is full. Back up the transaction log
for
> the database to free up some log space..
> But using the EM I cannot backup the transaction logs - the options are
> grayed out. If I try to backup the tempdb through EM, I get an error that
> Backup and Restore are not allowed on tempdb. I have the options set to
> allow unlimited growth. There are many GB's of space on the drive. What is
> the problem/solution?
> TIA!
>

Log files WAY too big

I know this seems to be a common issue. My dbs transaction logs are
absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
an effort to shink these bloated transaction logs, I'm trying to follow this
article: http://support.microsoft.com/default...b;EN-US;272318
...but what does step 1 really mean? "Run this code"? How? Where? What
do you mean 'run this code'? As a stored procedure? As a query? Any
advice would be appreciated.
TIA
Open Query Analyzer. Switch the database context to the offending database
with a USE statement or using the dropdown up top. Paste the code. Hit
F5.
http://www.aspfaq.com/
(Reverse address to reply.)
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:#fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/default...b;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>
|||Hi
1.. You must run a BACKUP LOG statement to free up space by removing the
inactive portion of the log.
2.. You must run DBCC SHRINKFILE again with the desired target size until
the log file shrinks to the target size
Run BACKUP LOG ....(see a syntax in the BOL) in QA within your database.
Run DBCC SHRINKFILE in QA within your database as well.
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%23fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/default...b;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>
sql

Log files WAY too big

I know this seems to be a common issue. My dbs transaction logs are
absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
an effort to shink these bloated transaction logs, I'm trying to follow this
article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
...but what does step 1 really mean? "Run this code"? How? Where? What
do you mean 'run this code'? As a stored procedure? As a query? Any
advice would be appreciated.
TIAOpen Query Analyzer. Switch the database context to the offending database
with a USE statement or using the dropdown up top. Paste the code. Hit
F5.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:#fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>|||Hi
1.. You must run a BACKUP LOG statement to free up space by removing the
inactive portion of the log.
2.. You must run DBCC SHRINKFILE again with the desired target size until
the log file shrinks to the target size
Run BACKUP LOG ....(see a syntax in the BOL) in QA within your database.
Run DBCC SHRINKFILE in QA within your database as well.
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%23fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>

Log files WAY too big

I know this seems to be a common issue. My dbs transaction logs are
absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
an effort to shink these bloated transaction logs, I'm trying to follow this
article: http://support.microsoft.com/defaul...kb;EN-US;272318
...but what does step 1 really mean? "Run this code"? How? Where? What
do you mean 'run this code'? As a stored procedure? As a query? Any
advice would be appreciated.
TIAOpen Query Analyzer. Switch the database context to the offending database
with a USE statement or using the dropdown up top. Paste the code. Hit
F5.
http://www.aspfaq.com/
(Reverse address to reply.)
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:#fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/defaul...kb;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>|||Hi
1.. You must run a BACKUP LOG statement to free up space by removing the
inactive portion of the log.
2.. You must run DBCC SHRINKFILE again with the desired target size until
the log file shrinks to the target size
Run BACKUP LOG ....(see a syntax in the BOL) in QA within your database.
Run DBCC SHRINKFILE in QA within your database as well.
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%23fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/defaul...kb;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>

Log Files

I have two questions regarding log files.
1) If I perform a full transaction log backup, does the file get reduced in
size?
2) Is it possible to have incomplete transctions in this file? If so, how
do I get rid of them?> 1) If I perform a full transaction log backup, does the file get reduced
in size?
No. It frees up space within the file, if possible. Partly, the reason is
that if the log file got that big once, it will get that big again. So,
rather than suffer the performance hit of shrinking the file just to grow it
again, it leaves it as is.
See DBCC SHRINKFILE

> 2) Is it possible to have incomplete transctions in this file?
Yes. Run DBCC OPENTRAN

> If so, how do I get rid of them?
Well, you can identify them by SPID, and then KILL them manually. But make
sure it is a transaction you can afford to kill.
A

Log Files

Hi

Im having a problem with disk issue, My database is currently 9.5 gb and my transaction log is 28 gb? is there any way i can reset or recreate a transaction log without affecting the integrity of the database (MDF)? Please help

Hi,

First of all you need to do regular backups to keep the size of your transaction log reasonable.

What you can do then is take a full database backup, then switch to simple recovery model and switch back to full recovery model (or just use the deprecating BACKUP LOG WITH TRUNCATE_ONLY).

After this you should be able to use DBCC SHRINKFILE to shrink the log to a smaller size.

Remember to set up a schedule for your database and log backups afterwards.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

You might want to run this query periodically to make sure you don't have any other issues that are preventing the transaction log from being truncated.

-- Find the problem with the transaction log SELECT name, database_id, log_reuse_wait_desc FROM sys.databases

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!509.entry

|||

Also, consider your recovery model. If you have a "Full" recovery model, the transaction log will grow much larger. Depending on your needs (and your backup plan), you can consider using a "Simple" recovery model.

http://msdn2.microsoft.com/en-us/library/ms191253(SQL.90).aspx

-Steve

Log Files

I have two questions regarding log files.
1) If I perform a full transaction log backup, does the file get reduced in size?
2) Is it possible to have incomplete transctions in this file? If so, how do I get rid of them?
> 1) If I perform a full transaction log backup, does the file get reduced
in size?
No. It frees up space within the file, if possible. Partly, the reason is
that if the log file got that big once, it will get that big again. So,
rather than suffer the performance hit of shrinking the file just to grow it
again, it leaves it as is.
See DBCC SHRINKFILE

> 2) Is it possible to have incomplete transctions in this file?
Yes. Run DBCC OPENTRAN

> If so, how do I get rid of them?
Well, you can identify them by SPID, and then KILL them manually. But make
sure it is a transaction you can afford to kill.
A

Log file won't truncate on backup (no open transaction)

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Running dbcc opentran in the problem database yields no open transactions
running
dbcc sqlperf(logspace)
shows log file over 95% full (1.5 gig )
Then doing a FULL database backup doesn't truncate the log (still 95%
full) - running dbcc sqlperf(logspace) again doesn't change the usage %.
Problem just started in last couple of weeks. Running ok for over a year in
SQL 2000, and several years in SQL 7. Only change is the use of Tivoli
backup.
I am fairly sure that dropping the database and recreating it from a backup
will probably fix this. I have done a restore to a new database and the log
is empty.
Any thoughts on how to fix without dropping and restoring the database?
Any thoughts on what may have caused this?
TIA,
MikeAre you doing transaction log backups? These should empty the log.
Christian Smith
"M Searer" <nospam@.nospam.com> wrote in message
news:%23XlsKHY9DHA.2924@.tk2msftngp13.phx.gbl...
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Running dbcc opentran in the problem database yields no open transactions
> running
> dbcc sqlperf(logspace)
> shows log file over 95% full (1.5 gig )
> Then doing a FULL database backup doesn't truncate the log (still 95%
> full) - running dbcc sqlperf(logspace) again doesn't change the usage %.
> Problem just started in last couple of weeks. Running ok for over a year
in
> SQL 2000, and several years in SQL 7. Only change is the use of Tivoli
> backup.
> I am fairly sure that dropping the database and recreating it from a
backup
> will probably fix this. I have done a restore to a new database and the
log
> is empty.
> Any thoughts on how to fix without dropping and restoring the database?
> Any thoughts on what may have caused this?
> TIA,
> Mike
>
>|||As Christian indicate. Full (DB) backup does not empty the transaction log.
Only transaction log backup does. Perhaps the db was in simple recovery mode
and Tivoli (or someone else) did set it to full recovery mode). Also, before
you did your first db backup, the log is in "auto-truncate mode".
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"M Searer" <nospam@.nospam.com> wrote in message
news:%23XlsKHY9DHA.2924@.tk2msftngp13.phx.gbl...
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Running dbcc opentran in the problem database yields no open transactions
> running
> dbcc sqlperf(logspace)
> shows log file over 95% full (1.5 gig )
> Then doing a FULL database backup doesn't truncate the log (still 95%
> full) - running dbcc sqlperf(logspace) again doesn't change the usage %.
> Problem just started in last couple of weeks. Running ok for over a year
in
> SQL 2000, and several years in SQL 7. Only change is the use of Tivoli
> backup.
> I am fairly sure that dropping the database and recreating it from a
backup
> will probably fix this. I have done a restore to a new database and the
log
> is empty.
> Any thoughts on how to fix without dropping and restoring the database?
> Any thoughts on what may have caused this?
> TIA,
> Mike
>
>sql

Log file won't truncate on backup (no open transaction)

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Running dbcc opentran in the problem database yields no open transactions
running
dbcc sqlperf(logspace)
shows log file over 95% full (1.5 gig )
Then doing a FULL database backup doesn't truncate the log (still 95%
full) - running dbcc sqlperf(logspace) again doesn't change the usage %.
Problem just started in last couple of weeks. Running ok for over a year in
SQL 2000, and several years in SQL 7. Only change is the use of Tivoli
backup.
I am fairly sure that dropping the database and recreating it from a backup
will probably fix this. I have done a restore to a new database and the log
is empty.
Any thoughts on how to fix without dropping and restoring the database?
Any thoughts on what may have caused this?
TIA,
MikeAre you doing transaction log backups? These should empty the log.
Christian Smith
"M Searer" <nospam@.nospam.com> wrote in message
news:%23XlsKHY9DHA.2924@.tk2msftngp13.phx.gbl...
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Running dbcc opentran in the problem database yields no open transactions
> running
> dbcc sqlperf(logspace)
> shows log file over 95% full (1.5 gig )
> Then doing a FULL database backup doesn't truncate the log (still 95%
> full) - running dbcc sqlperf(logspace) again doesn't change the usage %.
> Problem just started in last couple of weeks. Running ok for over a year
in
> SQL 2000, and several years in SQL 7. Only change is the use of Tivoli
> backup.
> I am fairly sure that dropping the database and recreating it from a
backup
> will probably fix this. I have done a restore to a new database and the
log
> is empty.
> Any thoughts on how to fix without dropping and restoring the database?
> Any thoughts on what may have caused this?
> TIA,
> Mike
>
>|||As Christian indicate. Full (DB) backup does not empty the transaction log.
Only transaction log backup does. Perhaps the db was in simple recovery mode
and Tivoli (or someone else) did set it to full recovery mode). Also, before
you did your first db backup, the log is in "auto-truncate mode".
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"M Searer" <nospam@.nospam.com> wrote in message
news:%23XlsKHY9DHA.2924@.tk2msftngp13.phx.gbl...
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Running dbcc opentran in the problem database yields no open transactions
> running
> dbcc sqlperf(logspace)
> shows log file over 95% full (1.5 gig )
> Then doing a FULL database backup doesn't truncate the log (still 95%
> full) - running dbcc sqlperf(logspace) again doesn't change the usage %.
> Problem just started in last couple of weeks. Running ok for over a year
in
> SQL 2000, and several years in SQL 7. Only change is the use of Tivoli
> backup.
> I am fairly sure that dropping the database and recreating it from a
backup
> will probably fix this. I have done a restore to a new database and the
log
> is empty.
> Any thoughts on how to fix without dropping and restoring the database?
> Any thoughts on what may have caused this?
> TIA,
> Mike
>
>

Log file too big

I have a transaction log file too big (about 13GB). I think the one who make
it first time has declared a fixed size. Haw can I make it little ?
First of all fix up the backup of the log so that it doesn't keep growing -
either has to be backed up regularly or the database should be in Simple
Recovery Mode (mainly for development/test databases).
From Enterprise manager you can then right click on the database name,
select All Tasks-Shrink Database. You can then click the Files button and
change the file to the log file and shrink it as much as possible (leave
some room for growth). The shrink may not happen immediately.
Mary
"Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message
news:%23njEtDrqEHA.2900@.TK2MSFTNGP12.phx.gbl...
>I have a transaction log file too big (about 13GB). I think the one who
>make it first time has declared a fixed size. Haw can I make it little ?
>
|||I've tried to shrink from enterprise manager but the minimum size is the
same like log files 13GB and I can't modify it.
Whith query analizer DBCC SHRINKFILE (LOGFILE, 10) i get
"9 2 1693472 128 1693472 128 "
I backup up full this database every night.
What should I do ?
"Mary Bray" <no@.spam.com> a scris n mesajul de
tiri:epc6%23ZrqEHA.3748@.TK2MSFTNGP09.phx.gbl...
> First of all fix up the backup of the log so that it doesn't keep
> growing - either has to be backed up regularly or the database should be
> in Simple Recovery Mode (mainly for development/test databases).
> From Enterprise manager you can then right click on the database name,
> select All Tasks-Shrink Database. You can then click the Files button and
> change the file to the log file and shrink it as much as possible (leave
> some room for growth). The shrink may not happen immediately.
> Mary
> "Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message
> news:%23njEtDrqEHA.2900@.TK2MSFTNGP12.phx.gbl...
>
|||The log file is not emptied when you perform a database backup, only for a transaction log backup.
If you don't perform transaction log backups, set the database in simple recovery mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message news:eZdwGItqEHA.2696@.TK2MSFTNGP15.phx.gbl...
> I've tried to shrink from enterprise manager but the minimum size is the same like log files 13GB
> and I can't modify it.
> Whith query analizer DBCC SHRINKFILE (LOGFILE, 10) i get
> "9 2 1693472 128 1693472 128 "
> I backup up full this database every night.
> What should I do ?
> "Mary Bray" <no@.spam.com> a scris n mesajul de tiri:epc6%23ZrqEHA.3748@.TK2MSFTNGP09.phx.gbl...
>

Log file too big

I have a transaction log file too big (about 13GB). I think the one who make
it first time has declared a fixed size. Haw can I make it little ?First of all fix up the backup of the log so that it doesn't keep growing -
either has to be backed up regularly or the database should be in Simple
Recovery Mode (mainly for development/test databases).
From Enterprise manager you can then right click on the database name,
select All Tasks-Shrink Database. You can then click the Files button and
change the file to the log file and shrink it as much as possible (leave
some room for growth). The shrink may not happen immediately.
Mary
"Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message
news:%23njEtDrqEHA.2900@.TK2MSFTNGP12.phx.gbl...
>I have a transaction log file too big (about 13GB). I think the one who
>make it first time has declared a fixed size. Haw can I make it little ?
>|||I've tried to shrink from enterprise manager but the minimum size is the
same like log files 13GB and I can't modify it.
Whith query analizer DBCC SHRINKFILE (LOGFILE, 10) i get
"9 2 1693472 128 1693472 128 "
I backup up full this database every night.
What should I do ?
"Mary Bray" <no@.spam.com> a scris în mesajul de
ºtiri:epc6%23ZrqEHA.3748@.TK2MSFTNGP09.phx.gbl...
> First of all fix up the backup of the log so that it doesn't keep
> growing - either has to be backed up regularly or the database should be
> in Simple Recovery Mode (mainly for development/test databases).
> From Enterprise manager you can then right click on the database name,
> select All Tasks-Shrink Database. You can then click the Files button and
> change the file to the log file and shrink it as much as possible (leave
> some room for growth). The shrink may not happen immediately.
> Mary
> "Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message
> news:%23njEtDrqEHA.2900@.TK2MSFTNGP12.phx.gbl...
>>I have a transaction log file too big (about 13GB). I think the one who
>>make it first time has declared a fixed size. Haw can I make it little ?
>|||The log file is not emptied when you perform a database backup, only for a transaction log backup.
If you don't perform transaction log backups, set the database in simple recovery mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message news:eZdwGItqEHA.2696@.TK2MSFTNGP15.phx.gbl...
> I've tried to shrink from enterprise manager but the minimum size is the same like log files 13GB
> and I can't modify it.
> Whith query analizer DBCC SHRINKFILE (LOGFILE, 10) i get
> "9 2 1693472 128 1693472 128 "
> I backup up full this database every night.
> What should I do ?
> "Mary Bray" <no@.spam.com> a scris în mesajul de ºtiri:epc6%23ZrqEHA.3748@.TK2MSFTNGP09.phx.gbl...
>> First of all fix up the backup of the log so that it doesn't keep growing - either has to be
>> backed up regularly or the database should be in Simple Recovery Mode (mainly for
>> development/test databases).
>> From Enterprise manager you can then right click on the database name, select All Tasks-Shrink
>> Database. You can then click the Files button and change the file to the log file and shrink it
>> as much as possible (leave some room for growth). The shrink may not happen immediately.
>> Mary
>> "Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message
>> news:%23njEtDrqEHA.2900@.TK2MSFTNGP12.phx.gbl...
>>I have a transaction log file too big (about 13GB). I think the one who make it first time has
>>declared a fixed size. Haw can I make it little ?
>>
>

Log File Size (SQL 2000)

Dear All,
How to maintain the transaction log file size ?
When I should use Restrict filegrowth or Unrestricted filegrowth ?
How many MB shoud I setup for the log file size ?
Thanks.
Vensia
Hi
http://www.sql-server-performance.co...e_settings.asp
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:eUNfuu1FGHA.752@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> How to maintain the transaction log file size ?
> When I should use Restrict filegrowth or Unrestricted filegrowth ?
> How many MB shoud I setup for the log file size ?
> Thanks.
> Vensia
>

Log File Size (SQL 2000)

Dear All,
How to maintain the transaction log file size ?
When I should use Restrict filegrowth or Unrestricted filegrowth ?
How many MB shoud I setup for the log file size ?
Thanks.
VensiaHi
http://www.sql-server-performance.com/database_settings.asp
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:eUNfuu1FGHA.752@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> How to maintain the transaction log file size ?
> When I should use Restrict filegrowth or Unrestricted filegrowth ?
> How many MB shoud I setup for the log file size ?
> Thanks.
> Vensia
>

Log File Size (SQL 2000)

Dear All,
How to maintain the transaction log file size ?
When I should use Restrict filegrowth or Unrestricted filegrowth ?
How many MB shoud I setup for the log file size ?
Thanks.
VensiaHi
http://www.sql-server-performance.c...se_settings.asp
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:eUNfuu1FGHA.752@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> How to maintain the transaction log file size ?
> When I should use Restrict filegrowth or Unrestricted filegrowth ?
> How many MB shoud I setup for the log file size ?
> Thanks.
> Vensia
>

log file shrinking problems

Hello,
I have currently problems shrinking transaction log files in my ms-sql =
server 2000 DB. The problems started after moving to new hardware, so =
there was a fresh install of ms sql2k and the db's were created from =
backups taken from the old system.
The usual steps :
backup log to file
dbcc shrinkfile( dbname, targetsize)
don't shrink the logfile anymore. I can still do "backup log with =
truncate_only", that will reduce the logfile to its original size, but =
that is just an emergency procedure to re-claim some HD space. I do =
need full recovery mode and have a sequence of full, differential, and =
log backups worked out that used to work just fine.
I have read all the microsoft and other articles on how virtual logfiles =
work and how to successfully shrink logfiles, indeed I did this without =
problems on the old system. =20
Here is the interesting bit and probably the cause of my problem. When =
doing "DBCC OPENTRAN" on the db in question I get this output:
Transaction information for database 'customerinformation'.
Replicated Transaction Information:
Oldest distributed LSN : (1739:217:2)
Oldest non-distributed LSN : (0:0:0)
DBCC execution completed. If DBCC printed error messages, contact your =
system administrator.
I'm not exactly sure what this means but have read that open replication =
type transactions will prevent shrinking of the logfile. This looks =
like one to me. =20
BUT....
The sql server I'm using doesn't have any replication enabled. It is =
neither a publisher nor a distributor nor a subscriber. I never touched =
replication since sql server got installed.
Does anyone know how to fix this? Anyone have any idea how this could =
have happened? Any advice/hints would be greatly appreciated, =
maintaining backups at he moment is an administrative nightmare.
BTW, the problem applies to all user DB's on that server, not just =
'customerinformation'.
Thanks
Ray
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 15/10/2004
when you have an oldest non-distributed LSN value of 0, it means that the
log reader has read all of the transactions from the log and marked them as
replicated. You can truncate your log now.
I can't explain why this database thinks it is being replicated. You might
want to do this
sp_replicationdboption 'databasename','published','false'
and see what happens. It should drop any publications in this database or
any related replication metadata if there is any.
"Raymond Delevaux" <ray@.seekmedia.com> wrote in message
news:uuCWM3jtEHA.1048@.tk2msftngp13.phx.gbl...
Hello,
I have currently problems shrinking transaction log files in my ms-sql
server 2000 DB. The problems started after moving to new hardware, so there
was a fresh install of ms sql2k and the db's were created from backups taken
from the old system.
The usual steps :
backup log to file
dbcc shrinkfile( dbname, targetsize)
don't shrink the logfile anymore. I can still do "backup log with
truncate_only", that will reduce the logfile to its original size, but that
is just an emergency procedure to re-claim some HD space. I do need full
recovery mode and have a sequence of full, differential, and log backups
worked out that used to work just fine.
I have read all the microsoft and other articles on how virtual logfiles
work and how to successfully shrink logfiles, indeed I did this without
problems on the old system.
Here is the interesting bit and probably the cause of my problem. When
doing "DBCC OPENTRAN" on the db in question I get this output:
Transaction information for database 'customerinformation'.
Replicated Transaction Information:
Oldest distributed LSN : (1739:217:2)
Oldest non-distributed LSN : (0:0:0)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I'm not exactly sure what this means but have read that open replication
type transactions will prevent shrinking of the logfile. This looks like
one to me.
BUT....
The sql server I'm using doesn't have any replication enabled. It is
neither a publisher nor a distributor nor a subscriber. I never touched
replication since sql server got installed.
Does anyone know how to fix this? Anyone have any idea how this could have
happened? Any advice/hints would be greatly appreciated, maintaining
backups at he moment is an administrative nightmare.
BTW, the problem applies to all user DB's on that server, not just
'customerinformation'.
Thanks
Ray
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 15/10/2004
|||Thanks for your answer, Hilary.
It appears that replication was at one time enabled on the original =
server, and since the new server was loaded with backups from the =
original, replicated transaction information seems to remain embedded in =
the logs. When I detach and then re-attach the DB, letting it build a =
new logfile, the replicated transaction info is gone for good from the =
log.
I've applied the sp you suggested and some other replication related =
sp's and am confident the DB's concerned are not enabled for replication =
and never were.
That proves my theory wrong of course, from you I learnt that there =
weren't any open transactions in the logs so this wasn't the reason why =
my log files don't shrink. At least thats eliminated and I can dig =
elsewhere now.
Thank you
Ray
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message =
news:ec2xoBktEHA.3292@.TK2MSFTNGP12.phx.gbl...
> when you have an oldest non-distributed LSN value of 0, it means that =
the=20
> log reader has read all of the transactions from the log and marked =
them as=20
> replicated. You can truncate your log now.
>=20
> I can't explain why this database thinks it is being replicated. You =
might=20
> want to do this
>=20
> sp_replicationdboption 'databasename','published','false'
>=20
> and see what happens. It should drop any publications in this database =
or=20
> any related replication metadata if there is any.
>=20
> "Raymond Delevaux" <ray@.seekmedia.com> wrote in message=20
> news:uuCWM3jtEHA.1048@.tk2msftngp13.phx.gbl...
> Hello,
>=20
> I have currently problems shrinking transaction log files in my ms-sql =

> server 2000 DB. The problems started after moving to new hardware, so =
there=20
> was a fresh install of ms sql2k and the db's were created from backups =
taken=20
> from the old system.
>=20
> The usual steps :
> backup log to file
> dbcc shrinkfile( dbname, targetsize)
>=20
> don't shrink the logfile anymore. I can still do "backup log with=20
> truncate_only", that will reduce the logfile to its original size, but =
that=20
> is just an emergency procedure to re-claim some HD space. I do need =
full=20
> recovery mode and have a sequence of full, differential, and log =
backups=20
> worked out that used to work just fine.
>=20
> I have read all the microsoft and other articles on how virtual =
logfiles=20
> work and how to successfully shrink logfiles, indeed I did this =
without=20
> problems on the old system.
>=20
> Here is the interesting bit and probably the cause of my problem. =
When=20
> doing "DBCC OPENTRAN" on the db in question I get this output:
> Transaction information for database 'customerinformation'.
>=20
> Replicated Transaction Information:
> Oldest distributed LSN : (1739:217:2)
> Oldest non-distributed LSN : (0:0:0)
> DBCC execution completed. If DBCC printed error messages, contact your =

> system administrator.
>=20
> I'm not exactly sure what this means but have read that open =
replication=20
> type transactions will prevent shrinking of the logfile. This looks =
like=20
> one to me.
> BUT....
> The sql server I'm using doesn't have any replication enabled. It is=20
> neither a publisher nor a distributor nor a subscriber. I never =
touched=20
> replication since sql server got installed.
>=20
> Does anyone know how to fix this? Anyone have any idea how this could =
have=20
> happened? Any advice/hints would be greatly appreciated, maintaining=20
> backups at he moment is an administrative nightmare.
>=20
> BTW, the problem applies to all user DB's on that server, not just=20
> 'customerinformation'.
>=20
> Thanks
>=20
> Ray
>=20
>=20
>=20
>=20
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.778 / Virus Database: 525 - Release Date: 15/10/2004=20
>=20
>=20
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.779 / Virus Database: 526 - Release Date: 19/10/2004
sql

log file shrinking problems

Hello,
I have currently problems shrinking transaction log files in my ms-sql =
server 2000 DB. The problems started after moving to new hardware, so =
there was a fresh install of ms sql2k and the db's were created from =
backups taken from the old system.
The usual steps :
backup log to file
dbcc shrinkfile( dbname, targetsize)
don't shrink the logfile anymore. I can still do "backup log with =
truncate_only", that will reduce the logfile to its original size, but =
that is just an emergency procedure to re-claim some HD space. I do =
need full recovery mode and have a sequence of full, differential, and =
log backups worked out that used to work just fine.
I have read all the microsoft and other articles on how virtual logfiles =
work and how to successfully shrink logfiles, indeed I did this without =
problems on the old system. =20
Here is the interesting bit and probably the cause of my problem. When =
doing "DBCC OPENTRAN" on the db in question I get this output:
Transaction information for database 'customerinformation'.
Replicated Transaction Information:
Oldest distributed LSN : (1739:217:2)
Oldest non-distributed LSN : (0:0:0)
DBCC execution completed. If DBCC printed error messages, contact your =
system administrator.
I'm not exactly sure what this means but have read that open replication =
type transactions will prevent shrinking of the logfile. This looks =
like one to me. =20
BUT....
The sql server I'm using doesn't have any replication enabled. It is =
neither a publisher nor a distributor nor a subscriber. I never touched =
replication since sql server got installed.
Does anyone know how to fix this? Anyone have any idea how this could =
have happened? Any advice/hints would be greatly appreciated, =
maintaining backups at he moment is an administrative nightmare.
BTW, the problem applies to all user DB's on that server, not just =
'customerinformation'.
Thanks
Ray
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 15/10/2004
when you have an oldest non-distributed LSN value of 0, it means that the
log reader has read all of the transactions from the log and marked them as
replicated. You can truncate your log now.
I can't explain why this database thinks it is being replicated. You might
want to do this
sp_replicationdboption 'databasename','published','false'
and see what happens. It should drop any publications in this database or
any related replication metadata if there is any.
"Raymond Delevaux" <ray@.seekmedia.com> wrote in message
news:uuCWM3jtEHA.1048@.tk2msftngp13.phx.gbl...
Hello,
I have currently problems shrinking transaction log files in my ms-sql
server 2000 DB. The problems started after moving to new hardware, so there
was a fresh install of ms sql2k and the db's were created from backups taken
from the old system.
The usual steps :
backup log to file
dbcc shrinkfile( dbname, targetsize)
don't shrink the logfile anymore. I can still do "backup log with
truncate_only", that will reduce the logfile to its original size, but that
is just an emergency procedure to re-claim some HD space. I do need full
recovery mode and have a sequence of full, differential, and log backups
worked out that used to work just fine.
I have read all the microsoft and other articles on how virtual logfiles
work and how to successfully shrink logfiles, indeed I did this without
problems on the old system.
Here is the interesting bit and probably the cause of my problem. When
doing "DBCC OPENTRAN" on the db in question I get this output:
Transaction information for database 'customerinformation'.
Replicated Transaction Information:
Oldest distributed LSN : (1739:217:2)
Oldest non-distributed LSN : (0:0:0)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I'm not exactly sure what this means but have read that open replication
type transactions will prevent shrinking of the logfile. This looks like
one to me.
BUT....
The sql server I'm using doesn't have any replication enabled. It is
neither a publisher nor a distributor nor a subscriber. I never touched
replication since sql server got installed.
Does anyone know how to fix this? Anyone have any idea how this could have
happened? Any advice/hints would be greatly appreciated, maintaining
backups at he moment is an administrative nightmare.
BTW, the problem applies to all user DB's on that server, not just
'customerinformation'.
Thanks
Ray
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 15/10/2004
|||Thanks for your answer, Hilary.
It appears that replication was at one time enabled on the original =
server, and since the new server was loaded with backups from the =
original, replicated transaction information seems to remain embedded in =
the logs. When I detach and then re-attach the DB, letting it build a =
new logfile, the replicated transaction info is gone for good from the =
log.
I've applied the sp you suggested and some other replication related =
sp's and am confident the DB's concerned are not enabled for replication =
and never were.
That proves my theory wrong of course, from you I learnt that there =
weren't any open transactions in the logs so this wasn't the reason why =
my log files don't shrink. At least thats eliminated and I can dig =
elsewhere now.
Thank you
Ray
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message =
news:ec2xoBktEHA.3292@.TK2MSFTNGP12.phx.gbl...
> when you have an oldest non-distributed LSN value of 0, it means that =
the=20
> log reader has read all of the transactions from the log and marked =
them as=20
> replicated. You can truncate your log now.
>=20
> I can't explain why this database thinks it is being replicated. You =
might=20
> want to do this
>=20
> sp_replicationdboption 'databasename','published','false'
>=20
> and see what happens. It should drop any publications in this database =
or=20
> any related replication metadata if there is any.
>=20
> "Raymond Delevaux" <ray@.seekmedia.com> wrote in message=20
> news:uuCWM3jtEHA.1048@.tk2msftngp13.phx.gbl...
> Hello,
>=20
> I have currently problems shrinking transaction log files in my ms-sql =

> server 2000 DB. The problems started after moving to new hardware, so =
there=20
> was a fresh install of ms sql2k and the db's were created from backups =
taken=20
> from the old system.
>=20
> The usual steps :
> backup log to file
> dbcc shrinkfile( dbname, targetsize)
>=20
> don't shrink the logfile anymore. I can still do "backup log with=20
> truncate_only", that will reduce the logfile to its original size, but =
that=20
> is just an emergency procedure to re-claim some HD space. I do need =
full=20
> recovery mode and have a sequence of full, differential, and log =
backups=20
> worked out that used to work just fine.
>=20
> I have read all the microsoft and other articles on how virtual =
logfiles=20
> work and how to successfully shrink logfiles, indeed I did this =
without=20
> problems on the old system.
>=20
> Here is the interesting bit and probably the cause of my problem. =
When=20
> doing "DBCC OPENTRAN" on the db in question I get this output:
> Transaction information for database 'customerinformation'.
>=20
> Replicated Transaction Information:
> Oldest distributed LSN : (1739:217:2)
> Oldest non-distributed LSN : (0:0:0)
> DBCC execution completed. If DBCC printed error messages, contact your =

> system administrator.
>=20
> I'm not exactly sure what this means but have read that open =
replication=20
> type transactions will prevent shrinking of the logfile. This looks =
like=20
> one to me.
> BUT....
> The sql server I'm using doesn't have any replication enabled. It is=20
> neither a publisher nor a distributor nor a subscriber. I never =
touched=20
> replication since sql server got installed.
>=20
> Does anyone know how to fix this? Anyone have any idea how this could =
have=20
> happened? Any advice/hints would be greatly appreciated, maintaining=20
> backups at he moment is an administrative nightmare.
>=20
> BTW, the problem applies to all user DB's on that server, not just=20
> 'customerinformation'.
>=20
> Thanks
>=20
> Ray
>=20
>=20
>=20
>=20
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.778 / Virus Database: 525 - Release Date: 15/10/2004=20
>=20
>=20
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.779 / Virus Database: 526 - Release Date: 19/10/2004

log file shrinking problems

Hello,
I have currently problems shrinking transaction log files in my ms-sql = server 2000 DB. The problems started after moving to new hardware, so = there was a fresh install of ms sql2k and the db's were created from = backups taken from the old system.
The usual steps :
backup log to file
dbcc shrinkfile( dbname, targetsize)
don't shrink the logfile anymore. I can still do "backup log with = truncate_only", that will reduce the logfile to its original size, but = that is just an emergency procedure to re-claim some HD space. I do = need full recovery mode and have a sequence of full, differential, and = log backups worked out that used to work just fine.
I have read all the microsoft and other articles on how virtual logfiles = work and how to successfully shrink logfiles, indeed I did this without = problems on the old system.
Here is the interesting bit and probably the cause of my problem. When = doing "DBCC OPENTRAN" on the db in question I get this output:
Transaction information for database 'customerinformation'.
Replicated Transaction Information:
Oldest distributed LSN : (1739:217:2)
Oldest non-distributed LSN : (0:0:0)
DBCC execution completed. If DBCC printed error messages, contact your = system administrator.
I'm not exactly sure what this means but have read that open replication = type transactions will prevent shrinking of the logfile. This looks = like one to me. BUT....
The sql server I'm using doesn't have any replication enabled. It is = neither a publisher nor a distributor nor a subscriber. I never touched = replication since sql server got installed.
Does anyone know how to fix this? Anyone have any idea how this could = have happened? Any advice/hints would be greatly appreciated, = maintaining backups at he moment is an administrative nightmare.
BTW, the problem applies to all user DB's on that server, not just = 'customerinformation'.
Thanks
Ray
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 15/10/2004when you have an oldest non-distributed LSN value of 0, it means that the
log reader has read all of the transactions from the log and marked them as
replicated. You can truncate your log now.
I can't explain why this database thinks it is being replicated. You might
want to do this
sp_replicationdboption 'databasename','published','false'
and see what happens. It should drop any publications in this database or
any related replication metadata if there is any.
"Raymond Delevaux" <ray@.seekmedia.com> wrote in message
news:uuCWM3jtEHA.1048@.tk2msftngp13.phx.gbl...
Hello,
I have currently problems shrinking transaction log files in my ms-sql
server 2000 DB. The problems started after moving to new hardware, so there
was a fresh install of ms sql2k and the db's were created from backups taken
from the old system.
The usual steps :
backup log to file
dbcc shrinkfile( dbname, targetsize)
don't shrink the logfile anymore. I can still do "backup log with
truncate_only", that will reduce the logfile to its original size, but that
is just an emergency procedure to re-claim some HD space. I do need full
recovery mode and have a sequence of full, differential, and log backups
worked out that used to work just fine.
I have read all the microsoft and other articles on how virtual logfiles
work and how to successfully shrink logfiles, indeed I did this without
problems on the old system.
Here is the interesting bit and probably the cause of my problem. When
doing "DBCC OPENTRAN" on the db in question I get this output:
Transaction information for database 'customerinformation'.
Replicated Transaction Information:
Oldest distributed LSN : (1739:217:2)
Oldest non-distributed LSN : (0:0:0)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I'm not exactly sure what this means but have read that open replication
type transactions will prevent shrinking of the logfile. This looks like
one to me.
BUT....
The sql server I'm using doesn't have any replication enabled. It is
neither a publisher nor a distributor nor a subscriber. I never touched
replication since sql server got installed.
Does anyone know how to fix this? Anyone have any idea how this could have
happened? Any advice/hints would be greatly appreciated, maintaining
backups at he moment is an administrative nightmare.
BTW, the problem applies to all user DB's on that server, not just
'customerinformation'.
Thanks
Ray
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 15/10/2004|||Thanks for your answer, Hilary.
It appears that replication was at one time enabled on the original =server, and since the new server was loaded with backups from the =original, replicated transaction information seems to remain embedded in =the logs. When I detach and then re-attach the DB, letting it build a =new logfile, the replicated transaction info is gone for good from the =log.
I've applied the sp you suggested and some other replication related =sp's and am confident the DB's concerned are not enabled for replication =and never were.
That proves my theory wrong of course, from you I learnt that there =weren't any open transactions in the logs so this wasn't the reason why =my log files don't shrink. At least thats eliminated and I can dig =elsewhere now.
Thank you
Ray
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message =news:ec2xoBktEHA.3292@.TK2MSFTNGP12.phx.gbl...
> when you have an oldest non-distributed LSN value of 0, it means that =the > log reader has read all of the transactions from the log and marked =them as > replicated. You can truncate your log now.
> > I can't explain why this database thinks it is being replicated. You =might > want to do this
> > sp_replicationdboption 'databasename','published','false'
> > and see what happens. It should drop any publications in this database =or > any related replication metadata if there is any.
> > "Raymond Delevaux" <ray@.seekmedia.com> wrote in message > news:uuCWM3jtEHA.1048@.tk2msftngp13.phx.gbl...
> Hello,
> > I have currently problems shrinking transaction log files in my ms-sql =
> server 2000 DB. The problems started after moving to new hardware, so =there > was a fresh install of ms sql2k and the db's were created from backups =taken > from the old system.
> > The usual steps :
> backup log to file
> dbcc shrinkfile( dbname, targetsize)
> > don't shrink the logfile anymore. I can still do "backup log with > truncate_only", that will reduce the logfile to its original size, but =that > is just an emergency procedure to re-claim some HD space. I do need =full > recovery mode and have a sequence of full, differential, and log =backups > worked out that used to work just fine.
> > I have read all the microsoft and other articles on how virtual =logfiles > work and how to successfully shrink logfiles, indeed I did this =without > problems on the old system.
> > Here is the interesting bit and probably the cause of my problem. =When > doing "DBCC OPENTRAN" on the db in question I get this output:
> Transaction information for database 'customerinformation'.
> > Replicated Transaction Information:
> Oldest distributed LSN : (1739:217:2)
> Oldest non-distributed LSN : (0:0:0)
> DBCC execution completed. If DBCC printed error messages, contact your =
> system administrator.
> > I'm not exactly sure what this means but have read that open =replication > type transactions will prevent shrinking of the logfile. This looks =like > one to me.
> BUT....
> The sql server I'm using doesn't have any replication enabled. It is > neither a publisher nor a distributor nor a subscriber. I never =touched > replication since sql server got installed.
> > Does anyone know how to fix this? Anyone have any idea how this could =have > happened? Any advice/hints would be greatly appreciated, maintaining > backups at he moment is an administrative nightmare.
> > BTW, the problem applies to all user DB's on that server, not just > 'customerinformation'.
> > Thanks
> > Ray
> > > > > --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.778 / Virus Database: 525 - Release Date: 15/10/2004 > > Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.779 / Virus Database: 526 - Release Date: 19/10/2004