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 free space question

I have tried all through enterprise manager and dbcc
shrinkfile but does not help it is not the datalog of the
log file getting big but the unused space in the log
files which needs to be truncated to a smaller size any
ideads

>--Original Message--
>Hi
>I am using sql server 7.0 and one of DB is relatively
>small. The size is about 2 MB however the log size is
>about 30GB the problems is the log used space is 38 MB
>but the log free space is about 37 GB so the whole
backup
>and restore takes some time as the toal og size is the
>sum of these two.
>I tried to shrink the file with truncate_only option but
>cant get it to a small size
>Can anyone help
>.
>Did you read the links that were posted? Have you checked to see if you
have any open transactions with DBCC OPENTRAN()? Do you have Truncate Log
on Checkpoint turned on?
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...[vbcol=seagreen]
>I have tried all through enterprise manager and dbcc
> shrinkfile but does not help it is not the datalog of the
> log file getting big but the unused space in the log
> files which needs to be truncated to a smaller size any
> ideads
>
> backup|||Yes i checked both and read all the articles but
does not solve it
basically it is something like this
transaction log space 2553.55
used: 43.25
unused:2510.3
so I am worried as how to get that unused spcae from log
file back and none of the soln seem to work.

>--Original Message--
>Did you read the links that were posted? Have you
checked to see if you
>have any open transactions with DBCC OPENTRAN()? Do you
have Truncate Log
>on Checkpoint turned on?
>--
>Andrew J. Kelly SQL MVP
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...
the[vbcol=seagreen]
but[vbcol=seagreen]
>
>.
>|||Did you have a look with DBCC LOGINFO to see where the active VLF is? If it
is at the end you can not shrink it until you get it wrapped towards the
beginning. One of those links has a script for 7.0 to do this.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:161601c4f79e$6f5a9f80$a501280a@.phx.gbl...[vbcol=seagreen]
> Yes i checked both and read all the articles but
> does not solve it
> basically it is something like this
> transaction log space 2553.55
> used: 43.25
> unused:2510.3
> so I am worried as how to get that unused spcae from log
> file back and none of the soln seem to work.
>
>
> checked to see if you
> have Truncate Log
> the
> but

Log free space question

Hi
I am using sql server 7.0 and one of DB is relatively
small. The size is about 2 MB however the log size is
about 30GB the problems is the log used space is 38 MB
but the log free space is about 37 GB so the whole backup
and restore takes some time as the toal og size is the
sum of these two.
I tried to shrink the file with truncate_only option but
cant get it to a small size
Can anyone helphttp://www.nigelrivett.net/Transact...ileGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Ap" <anonymous@.discussions.microsoft.com> wrote in message
news:154201c4f773$43971110$a501280a@.phx.gbl...
> Hi
> I am using sql server 7.0 and one of DB is relatively
> small. The size is about 2 MB however the log size is
> about 30GB the problems is the log used space is 38 MB
> but the log free space is about 37 GB so the whole backup
> and restore takes some time as the toal og size is the
> sum of these two.
> I tried to shrink the file with truncate_only option but
> cant get it to a small size
> Can anyone help|||Ap wrote:
> Hi
> I am using sql server 7.0 and one of DB is relatively
> small. The size is about 2 MB however the log size is
> about 30GB the problems is the log used space is 38 MB
> but the log free space is about 37 GB so the whole backup
> and restore takes some time as the toal og size is the
> sum of these two.
> I tried to shrink the file with truncate_only option but
> cant get it to a small size
> Can anyone help
Try DBCC SHRINKFILE (off hours, of course).
David Gugick
Imceda Software
www.imceda.com|||I see the same thing all the time and the solutions provided by David
and Andrew work beautifully (I've had logs up to 6GB and this process
has worked as advertised). Order is everything, are you sure you're
running the them in the appropriate order? (Try sticking with isql or
isqlw. Enterprise Manager has it's place, however, for general
maintenance, it's often easier to issue a statement directly.)
Step One:
This will give you an idea of your usage and should provide the
information you'd posted earlier
DBCC SQLPERF(LOGSPACE)
Step Two:
If your logs are huge, check to see where the current active vlf is
located. Active vlfs have a status of two while inactives have a status
of zero. Chances are, if you're running this off hours you'll see a
single active vlf towards the very bottom of the result set.
DBCC LOGINFO
Step Three:
If this is the case, you need to BACKUP LOG WITH NO_LOG or
TRUNCATE_ONLY (synonymous) to move the vlf closer to the beginning of
the log file. This essentially trashes the transactions that are in the
log (they are no longer recoverable), so you'll want perform a BACKUP
DATABASE soon after.
BACKUP LOG DB_NAME WITH NO_LOG
BACKUP DATABASE DB_NAME TO DISK = '\\anywhere\db.bak'
Step Four:
As soon as you BACKUP NO_LOG (and the *highly* recommended BACKUP
DATABASE) you're free to shrink the log file.
DBCC SHRINKFILE(DB_LOG, 2)
Step Five:
Double check that everything went well (hasn't failed for me yet!):
DBCC SQLPERF(LOGSPACE)

Log free space question

Hi
I am using sql server 7.0 and one of DB is relatively
small. The size is about 2 MB however the log size is
about 30GB the problems is the log used space is 38 MB
but the log free space is about 37 GB so the whole backup
and restore takes some time as the toal og size is the
sum of these two.
I tried to shrink the file with truncate_only option but
cant get it to a small size
Can anyone helphttp://www.nigelrivett.net/TransactionLogFileGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Ap" <anonymous@.discussions.microsoft.com> wrote in message
news:154201c4f773$43971110$a501280a@.phx.gbl...
> Hi
> I am using sql server 7.0 and one of DB is relatively
> small. The size is about 2 MB however the log size is
> about 30GB the problems is the log used space is 38 MB
> but the log free space is about 37 GB so the whole backup
> and restore takes some time as the toal og size is the
> sum of these two.
> I tried to shrink the file with truncate_only option but
> cant get it to a small size
> Can anyone help|||Ap wrote:
> Hi
> I am using sql server 7.0 and one of DB is relatively
> small. The size is about 2 MB however the log size is
> about 30GB the problems is the log used space is 38 MB
> but the log free space is about 37 GB so the whole backup
> and restore takes some time as the toal og size is the
> sum of these two.
> I tried to shrink the file with truncate_only option but
> cant get it to a small size
> Can anyone help
Try DBCC SHRINKFILE (off hours, of course).
--
David Gugick
Imceda Software
www.imceda.com|||I have tried all through enterprise manager and dbcc
shrinkfile but does not help it is not the datalog of the
log file getting big but the unused space in the log
files which needs to be truncated to a smaller size any
ideads
>--Original Message--
>Hi
>I am using sql server 7.0 and one of DB is relatively
>small. The size is about 2 MB however the log size is
>about 30GB the problems is the log used space is 38 MB
>but the log free space is about 37 GB so the whole
backup
>and restore takes some time as the toal og size is the
>sum of these two.
>I tried to shrink the file with truncate_only option but
>cant get it to a small size
>Can anyone help
>.
>|||Did you read the links that were posted? Have you checked to see if you
have any open transactions with DBCC OPENTRAN()? Do you have Truncate Log
on Checkpoint turned on?
--
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...
>I have tried all through enterprise manager and dbcc
> shrinkfile but does not help it is not the datalog of the
> log file getting big but the unused space in the log
> files which needs to be truncated to a smaller size any
> ideads
>
>>--Original Message--
>>Hi
>>I am using sql server 7.0 and one of DB is relatively
>>small. The size is about 2 MB however the log size is
>>about 30GB the problems is the log used space is 38 MB
>>but the log free space is about 37 GB so the whole
> backup
>>and restore takes some time as the toal og size is the
>>sum of these two.
>>I tried to shrink the file with truncate_only option but
>>cant get it to a small size
>>Can anyone help
>>.|||Yes i checked both and read all the articles but
does not solve it
basically it is something like this
transaction log space 2553.55
used: 43.25
unused:2510.3
so I am worried as how to get that unused spcae from log
file back and none of the soln seem to work.
>--Original Message--
>Did you read the links that were posted? Have you
checked to see if you
>have any open transactions with DBCC OPENTRAN()? Do you
have Truncate Log
>on Checkpoint turned on?
>--
>Andrew J. Kelly SQL MVP
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...
>>I have tried all through enterprise manager and dbcc
>> shrinkfile but does not help it is not the datalog of
the
>> log file getting big but the unused space in the log
>> files which needs to be truncated to a smaller size any
>> ideads
>>
>>--Original Message--
>>Hi
>>I am using sql server 7.0 and one of DB is relatively
>>small. The size is about 2 MB however the log size is
>>about 30GB the problems is the log used space is 38 MB
>>but the log free space is about 37 GB so the whole
>> backup
>>and restore takes some time as the toal og size is the
>>sum of these two.
>>I tried to shrink the file with truncate_only option
but
>>cant get it to a small size
>>Can anyone help
>>.
>
>.
>|||Did you have a look with DBCC LOGINFO to see where the active VLF is? If it
is at the end you can not shrink it until you get it wrapped towards the
beginning. One of those links has a script for 7.0 to do this.
--
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:161601c4f79e$6f5a9f80$a501280a@.phx.gbl...
> Yes i checked both and read all the articles but
> does not solve it
> basically it is something like this
> transaction log space 2553.55
> used: 43.25
> unused:2510.3
> so I am worried as how to get that unused spcae from log
> file back and none of the soln seem to work.
>
>>--Original Message--
>>Did you read the links that were posted? Have you
> checked to see if you
>>have any open transactions with DBCC OPENTRAN()? Do you
> have Truncate Log
>>on Checkpoint turned on?
>>--
>>Andrew J. Kelly SQL MVP
>>
>><anonymous@.discussions.microsoft.com> wrote in message
>>news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...
>>I have tried all through enterprise manager and dbcc
>> shrinkfile but does not help it is not the datalog of
> the
>> log file getting big but the unused space in the log
>> files which needs to be truncated to a smaller size any
>> ideads
>>
>>--Original Message--
>>Hi
>>I am using sql server 7.0 and one of DB is relatively
>>small. The size is about 2 MB however the log size is
>>about 30GB the problems is the log used space is 38 MB
>>but the log free space is about 37 GB so the whole
>> backup
>>and restore takes some time as the toal og size is the
>>sum of these two.
>>I tried to shrink the file with truncate_only option
> but
>>cant get it to a small size
>>Can anyone help
>>.
>>
>>.|||I see the same thing all the time and the solutions provided by David
and Andrew work beautifully (I've had logs up to 6GB and this process
has worked as advertised). Order is everything, are you sure you're
running the them in the appropriate order? (Try sticking with isql or
isqlw. Enterprise Manager has it's place, however, for general
maintenance, it's often easier to issue a statement directly.)
Step One:
This will give you an idea of your usage and should provide the
information you'd posted earlier
DBCC SQLPERF(LOGSPACE)
Step Two:
If your logs are huge, check to see where the current active vlf is
located. Active vlfs have a status of two while inactives have a status
of zero. Chances are, if you're running this off hours you'll see a
single active vlf towards the very bottom of the result set.
DBCC LOGINFO
Step Three:
If this is the case, you need to BACKUP LOG WITH NO_LOG or
TRUNCATE_ONLY (synonymous) to move the vlf closer to the beginning of
the log file. This essentially trashes the transactions that are in the
log (they are no longer recoverable), so you'll want perform a BACKUP
DATABASE soon after.
BACKUP LOG DB_NAME WITH NO_LOG
BACKUP DATABASE DB_NAME TO DISK = '\\anywhere\db.bak'
Step Four:
As soon as you BACKUP NO_LOG (and the *highly* recommended BACKUP
DATABASE) you're free to shrink the log file.
DBCC SHRINKFILE(DB_LOG, 2)
Step Five:
Double check that everything went well (hasn't failed for me yet!):
DBCC SQLPERF(LOGSPACE)

Log free space question

Hi
I am using sql server 7.0 and one of DB is relatively
small. The size is about 2 MB however the log size is
about 30GB the problems is the log used space is 38 MB
but the log free space is about 37 GB so the whole backup
and restore takes some time as the toal og size is the
sum of these two.
I tried to shrink the file with truncate_only option but
cant get it to a small size
Can anyone help
http://www.nigelrivett.net/Transacti...leGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Ap" <anonymous@.discussions.microsoft.com> wrote in message
news:154201c4f773$43971110$a501280a@.phx.gbl...
> Hi
> I am using sql server 7.0 and one of DB is relatively
> small. The size is about 2 MB however the log size is
> about 30GB the problems is the log used space is 38 MB
> but the log free space is about 37 GB so the whole backup
> and restore takes some time as the toal og size is the
> sum of these two.
> I tried to shrink the file with truncate_only option but
> cant get it to a small size
> Can anyone help
|||Ap wrote:
> Hi
> I am using sql server 7.0 and one of DB is relatively
> small. The size is about 2 MB however the log size is
> about 30GB the problems is the log used space is 38 MB
> but the log free space is about 37 GB so the whole backup
> and restore takes some time as the toal og size is the
> sum of these two.
> I tried to shrink the file with truncate_only option but
> cant get it to a small size
> Can anyone help
Try DBCC SHRINKFILE (off hours, of course).
David Gugick
Imceda Software
www.imceda.com
|||I see the same thing all the time and the solutions provided by David
and Andrew work beautifully (I've had logs up to 6GB and this process
has worked as advertised). Order is everything, are you sure you're
running the them in the appropriate order? (Try sticking with isql or
isqlw. Enterprise Manager has it's place, however, for general
maintenance, it's often easier to issue a statement directly.)
Step One:
This will give you an idea of your usage and should provide the
information you'd posted earlier
DBCC SQLPERF(LOGSPACE)
Step Two:
If your logs are huge, check to see where the current active vlf is
located. Active vlfs have a status of two while inactives have a status
of zero. Chances are, if you're running this off hours you'll see a
single active vlf towards the very bottom of the result set.
DBCC LOGINFO
Step Three:
If this is the case, you need to BACKUP LOG WITH NO_LOG or
TRUNCATE_ONLY (synonymous) to move the vlf closer to the beginning of
the log file. This essentially trashes the transactions that are in the
log (they are no longer recoverable), so you'll want perform a BACKUP
DATABASE soon after.
BACKUP LOG DB_NAME WITH NO_LOG
BACKUP DATABASE DB_NAME TO DISK = '\\anywhere\db.bak'
Step Four:
As soon as you BACKUP NO_LOG (and the *highly* recommended BACKUP
DATABASE) you're free to shrink the log file.
DBCC SHRINKFILE(DB_LOG, 2)
Step Five:
Double check that everything went well (hasn't failed for me yet!):
DBCC SQLPERF(LOGSPACE)

Log free space question

I have tried all through enterprise manager and dbcc
shrinkfile but does not help it is not the datalog of the
log file getting big but the unused space in the log
files which needs to be truncated to a smaller size any
ideads

>--Original Message--
>Hi
>I am using sql server 7.0 and one of DB is relatively
>small. The size is about 2 MB however the log size is
>about 30GB the problems is the log used space is 38 MB
>but the log free space is about 37 GB so the whole
backup
>and restore takes some time as the toal og size is the
>sum of these two.
>I tried to shrink the file with truncate_only option but
>cant get it to a small size
>Can anyone help
>.
>
Did you read the links that were posted? Have you checked to see if you
have any open transactions with DBCC OPENTRAN()? Do you have Truncate Log
on Checkpoint turned on?
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...[vbcol=seagreen]
>I have tried all through enterprise manager and dbcc
> shrinkfile but does not help it is not the datalog of the
> log file getting big but the unused space in the log
> files which needs to be truncated to a smaller size any
> ideads
>
> backup
|||Yes i checked both and read all the articles but
does not solve it
basically it is something like this
transaction log space 2553.55
used: 43.25
unused:2510.3
so I am worried as how to get that unused spcae from log
file back and none of the soln seem to work.

>--Original Message--
>Did you read the links that were posted? Have you
checked to see if you
>have any open transactions with DBCC OPENTRAN()? Do you
have Truncate Log[vbcol=seagreen]
>on Checkpoint turned on?
>--
>Andrew J. Kelly SQL MVP
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...
the[vbcol=seagreen]
but
>
>.
>
|||Did you have a look with DBCC LOGINFO to see where the active VLF is? If it
is at the end you can not shrink it until you get it wrapped towards the
beginning. One of those links has a script for 7.0 to do this.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:161601c4f79e$6f5a9f80$a501280a@.phx.gbl...[vbcol=seagreen]
> Yes i checked both and read all the articles but
> does not solve it
> basically it is something like this
> transaction log space 2553.55
> used: 43.25
> unused:2510.3
> so I am worried as how to get that unused spcae from log
> file back and none of the soln seem to work.
>
> checked to see if you
> have Truncate Log
> the
> but
sql

Log files taking up a lot of disk space

Hello,

I ran into an issue with all these logs made by default.

Disk space crunch leading to performance degradation.

OK, I moved the log dir outside the default app volume (Script/restart) onto a 100GB drive I use for the SQL transactions logs, but.

Analysis Services accumulated 20GB of logs in 2-3 days, did not clean-up the old stuff and led to disk space alerts and applications performance issues. All this with out of the box settings.

The server has in the 30 to 60 connections alive around the clock with each of these connections performing queries every 2-3 seconds or so.

I also use the query log function for optimisation purposes but for that one I log to table on a specific drive.

I run Analysis services 64 bits build 9.00.2047

Log files eating the drive have name like msmdsrv.log, FlightRecorderCurrent.trc, FlightRecorderBack.trc, SQLDmpr4495.mdmp, SQLDmpr4495.log and SQLDUMPER_ERRORLOG.log

Is the high frequency of log files creation (4MB every 5 minutes) the indication of a bigger issue?

Is there any way to set up these logs to be less intrusive, less verbose and more self-cleaning while still reaping the benefits of unattended problems loging?

Thanks,

Philippe

Yes.

You might be having different and potentially more seriouse issues. Creation of SQLDmpr***.mdmp SQLDmpr***.log files is indication of something going wrong in your server.

You should try and contact customer support and report your problems.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thank you,

The problem was very simple, the login in the QueryLog connection string did not have write access to the logDB.

This was generating all these Dmpr log files. Not a single log was generated since I fixed it last week.

Philippe

|||I split and edited this thread from an unrelated thread on the Flight Recorder to make it easier for people searching the forum to find this information.

Log Files - How To Clean Up

I had received a message that my log file is full and it do not enable to me to do a database backup before free up disk space. How do i clean up de log file (_log.ldf)?check 'dbcc shrinkfile' in BOL,

dbcc shrinkfile('db_log',size,truncateonly)|||Thanks, mallier, but my _log.ldf is on another disk driver under mssql\data folder. After executing the command you wrote, i got a message 'not found in the system files'|||run this query to get the log name in ur db


select *from sysfiles
-- u will get log name from name column with extension '_Log'

--replace that name in 'db_Log'|||ok, mallier, excuse me again, but the _log.ldf is 18GB great yet. Some system table was updated after the last command you wrote, but i need clean that _log.ldf to free up space in the disk drive.|||put the database in simple recoverry mode. Checkpoint the database. Backup the database. open QA. run "sp_helpdb (dbname). Find the file number for the log. execute dbcc shrinkfile(<file number for the log.).

Log files

I am getting the error message ,
"Could not allocate space for object '(SYSTEM table id: -1021390423)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full."
when i run a select stmt. I am thinking it's because my log file is full.
Can you pls tell me what i am supposed to do now?Read the error message closely, as it explains the problem very well. The pr
oblem is the tempdb
database. It is not the transaction log for tempdb, it is the database files
. They are not large
enough, and sometimes autogrow doesn't grow fast enough for the space needed
by the query
processing. Either pre.allocate storage for tempdb, or tweak the query (look
at query plan, add
indexes, modify the SQL etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"PH" <PH@.discussions.microsoft.com> wrote in message
news:E381B423-F43B-4D94-9E4C-802BE97EEF97@.microsoft.com...
>I am getting the error message ,
> "Could not allocate space for object '(SYSTEM table id: -1021390423)' in
> database 'TEMPDB' because the 'DEFAULT' filegroup is full."
> when i run a select stmt. I am thinking it's because my log file is full.
> Can you pls tell me what i am supposed to do now?|||How do I make them grow?
"Tibor Karaszi" wrote:

> Read the error message closely, as it explains the problem very well. The
problem is the tempdb
> database. It is not the transaction log for tempdb, it is the database fil
es. They are not large
> enough, and sometimes autogrow doesn't grow fast enough for the space need
ed by the query
> processing. Either pre.allocate storage for tempdb, or tweak the query (lo
ok at query plan, add
> indexes, modify the SQL etc).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "PH" <PH@.discussions.microsoft.com> wrote in message
> news:E381B423-F43B-4D94-9E4C-802BE97EEF97@.microsoft.com...
>
>|||Should i go to properties,data files and increase the size for space
allocated? Pls reply. Thanks in advance.
"PH" wrote:
> How do I make them grow?
> "Tibor Karaszi" wrote:
>|||Yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"PH" <PH@.discussions.microsoft.com> wrote in message
news:BA24839D-8D6E-4263-999A-2CF015CFAAA0@.microsoft.com...
> Should i go to properties,data files and increase the size for space
> allocated? Pls reply. Thanks in advance.
> "PH" wrote:
>

log file size

Dear Sir
I found that my database file (*.mdf) size is about
600 mb, but my log file (*.ldf) size is about 16 GB. My
harddisk has not enough space to place this file, if the
log continues to increase. I want to ask does any method
too reduced the size of the log file, becuase the log file
is not important for us'
Thank youIf you don't backup your transaction log regularly as part of your
recovery plan, you can have SQL Server automatically remove committed
data from the log by setting the recovery model to SIMPLE (SQL 2000) or
turn on the 'trunc. log on chkpt.' database option (SQL 7). Note that
your only recovery option in the SIMPLE recovery model is to restore
from full and differential backup.
For example:
SQL 2000:
ALTER DATABASE MyDatabase
SET RECOVERY SIMPLE
SQL 7:
EXEC sp_dboption 'MyDatabase', 'trunc. log on chkpt.', true
To reduce the size of your log, use DBCC SHRINKFILE. The example below
will shrink the log file to 200MB:
USE MyDatabase
DBCC SHRINKFILE('MyDatabase_Log', 200)
See the Books Online for details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"back1" <back1@.mail.hongkong.com> wrote in message
news:033f01c36aad$9ede6f90$a401280a@.phx.gbl...
> Dear Sir
> I found that my database file (*.mdf) size is about
> 600 mb, but my log file (*.ldf) size is about 16 GB. My
> harddisk has not enough space to place this file, if the
> log continues to increase. I want to ask does any method
> too reduced the size of the log file, becuase the log file
> is not important for us'
> Thank you|||Dear Sir
Thank you for your quickly reply!
I already use this statement "DBCC SHRINKFILE
('MyDatabase_Log', 200)" to shrink the log file, but it
has not any effect. What can i do now '
Thank you
>--Original Message--
>If you don't backup your transaction log regularly as
part of your
>recovery plan, you can have SQL Server automatically
remove committed
>data from the log by setting the recovery model to SIMPLE
(SQL 2000) or
>turn on the 'trunc. log on chkpt.' database option (SQL
7). Note that
>your only recovery option in the SIMPLE recovery model is
to restore
>from full and differential backup.
>For example:
>SQL 2000:
> ALTER DATABASE MyDatabase
> SET RECOVERY SIMPLE
>SQL 7:
> EXEC sp_dboption 'MyDatabase', 'trunc. log on
chkpt.', true
>To reduce the size of your log, use DBCC SHRINKFILE. The
example below
>will shrink the log file to 200MB:
> USE MyDatabase
> DBCC SHRINKFILE('MyDatabase_Log', 200)
>See the Books Online for details.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>--
>SQL FAQ links (courtesy Neil Pike):
>http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
>http://www.sqlserverfaq.com
>http://www.mssqlserver.com/faq
>--
>"back1" <back1@.mail.hongkong.com> wrote in message
>news:033f01c36aad$9ede6f90$a401280a@.phx.gbl...
>> Dear Sir
>> I found that my database file (*.mdf) size is about
>> 600 mb, but my log file (*.ldf) size is about 16 GB. My
>> harddisk has not enough space to place this file, if the
>> log continues to increase. I want to ask does any method
>> too reduced the size of the log file, becuase the log
file
>> is not important for us'
>> Thank you
>
>.
>

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 modified date does not change

Hi ,
I would like to check if the modified date of the log file be changed only
if there's not enough allocated disk space for the txn log and SQL needs to
expand it ?
This is because i have set a full recovery and doing full database & txn
log backup every nite since 2 days ago and it seems that the file size has
been stable and the modified data did not change either
appreciate ur advise
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200511/1
I don't think the modified date is a good indicia to look at. You can set up
performance monitor alerts to alert you about file growth.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:57dd7904ff6e4@.uwe...
> Hi ,
> I would like to check if the modified date of the log file be changed
> only
> if there's not enough allocated disk space for the txn log and SQL needs
> to
> expand it ?
> This is because i have set a full recovery and doing full database & txn
> log backup every nite since 2 days ago and it seems that the file size has
> been stable and the modified data did not change either
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...ation/200511/1
sql

Log file issue

my Log file has grown to a very large size for the last two days(from 1 GB to 9 GB).What should be the reason and how can i see the free space in my databse.
Thanks.What's your recovery model, and when was the last time you a) backed up the database or b) dumped the transaction log...

That's an awfully big log btw...

You set it to unlimited growth?

I Don't think (damn I did it again) that if you do, that it's a good idea...

Need to set alerts when the get to with a % of max...|||Large number of writes can cause it. How often do you backup your transaction log? If you haven't done it for a while, it's time. To see your free space, simply go to EM and right click the database, click view ant then click Task Pad.|||My database doesn't have any writes,But what i observed is that it's coz of sql server agent and its jobs. The log stopped to grow after i stopped sql server agent. Should it be because of backups or anything?
I am just taking a differential back of the database,Not taking any transactional backup.
Can anyone help in this issue?|||And one more question regarding the same issue,how can i bring my log size to the earlier size, which should be around 1 GB(which is now 10 GB)
Thanks|||read up on dbcc shrinkfile in SQL BOL

Log file issue

my Log file has grown to a very large size for the last two days(from 1 GB to 9 GB).What should be the reason and how can i see the free space in my databse.
Thanks.ummm..crosspost?

http://www.dbforums.com/t970452.html

Log file is too large

Hi
I want to clean up my log file (LDF extension) cause is ocupying much space
from the computer server. I'm using 2000 version and can't erase it using
DELETE
button from properties of the database.
What should I do? Is that possible?
Thanks in advanceFirstly, I'd do a backup or truncate the log. Then I might
do a shrinkfile to make it smaller
Vinnie
>--Original Message--
>Hi
>I want to clean up my log file (LDF extension) cause is
ocupying much space
>from the computer server. I'm using 2000 version and
can't erase it using
>DELETE
>button from properties of the database.
>What should I do? Is that possible?
>Thanks in advance
>.
>|||Hi,
Backup the trasnaction log and shrink the transaction log file.
Have a look into the below article,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
http://www.support.microsoft.com/?id=315512
Thanks
Hari
SQL Server MVP
"Katty" <Katty@.discussions.microsoft.com> wrote in message
news:A366CB5F-DF6E-4E16-B374-D452C2599C1C@.microsoft.com...
> Hi
> I want to clean up my log file (LDF extension) cause is ocupying much
> space
> from the computer server. I'm using 2000 version and can't erase it using
> DELETE
> button from properties of the database.
> What should I do? Is that possible?
> Thanks in advance

Log file is Full: How to prevent it happening?

"The log file for database 'DW_BackRoom_RT' is full. Back up the
transaction log for the database to free up some log space."
Now I only know this way to deal with that manually,
Step1. in option , chance Recovery model from FULL to Simple.
Step2: go to task to manually shrink the log file
Step3: Change recovery model back from simple to FULL.
But by this way, I could get same problem again, the log file is fill,
and need free up.
Could you give an idea how to prevent this from happening? what and
how should I do?
Thanks a lot in advance for your help.
If you are periodically wiping the transaction log, then it sounds like it
is of no importance to you so I'd recommend leaving in simple recovery mode.
If you decide that the log is needed for DR, set up a backup plan which
includes full database backups and regular log backups.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Hello,
If the data is this database is not critical/non production you can set the
recovery model to SIMPLE. THis will make sure that after
each checkpoint data will be writtent o disk and clear the transaction log
file. Incase if the data is critical keep the Recovery model
to FULL and make sure that you schedule a transction log backup (BACKUP
LOG). SO after each transction log backup the
LDF file will be cleared. THis will help you to restrict the growth of LDF
file and log backups will help you to recover the
database if needed as welll as you can perform Point_IN_Time recovery if
needed.
Take a look into Recovery models in Books online.
Thanks
Hari
<danceli@.gmail.com> wrote in message
news:1171053424.553101.106320@.s48g2000cws.googlegr oups.com...
> "The log file for database 'DW_BackRoom_RT' is full. Back up the
> transaction log for the database to free up some log space."
> Now I only know this way to deal with that manually,
> Step1. in option , chance Recovery model from FULL to Simple.
> Step2: go to task to manually shrink the log file
> Step3: Change recovery model back from simple to FULL.
> But by this way, I could get same problem again, the log file is fill,
> and need free up.
> Could you give an idea how to prevent this from happening? what and
> how should I do?
> Thanks a lot in advance for your help.
>

Log file is Full: How to prevent it happening?

"The log file for database 'DW_BackRoom_RT' is full. Back up the
transaction log for the database to free up some log space."
Now I only know this way to deal with that manually,
Step1. in option , chance Recovery model from FULL to Simple.
Step2: go to task to manually shrink the log file
Step3: Change recovery model back from simple to FULL.
But by this way, I could get same problem again, the log file is fill,
and need free up.
Could you give an idea how to prevent this from happening? what and
how should I do?
Thanks a lot in advance for your help.If you are periodically wiping the transaction log, then it sounds like it
is of no importance to you so I'd recommend leaving in simple recovery mode.
If you decide that the log is needed for DR, set up a backup plan which
includes full database backups and regular log backups.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hello,
If the data is this database is not critical/non production you can set the
recovery model to SIMPLE. THis will make sure that after
each checkpoint data will be writtent o disk and clear the transaction log
file. Incase if the data is critical keep the Recovery model
to FULL and make sure that you schedule a transction log backup (BACKUP
LOG). SO after each transction log backup the
LDF file will be cleared. THis will help you to restrict the growth of LDF
file and log backups will help you to recover the
database if needed as welll as you can perform Point_IN_Time recovery if
needed.
Take a look into Recovery models in Books online.
Thanks
Hari
<danceli@.gmail.com> wrote in message
news:1171053424.553101.106320@.s48g2000cws.googlegroups.com...
> "The log file for database 'DW_BackRoom_RT' is full. Back up the
> transaction log for the database to free up some log space."
> Now I only know this way to deal with that manually,
> Step1. in option , chance Recovery model from FULL to Simple.
> Step2: go to task to manually shrink the log file
> Step3: Change recovery model back from simple to FULL.
> But by this way, I could get same problem again, the log file is fill,
> and need free up.
> Could you give an idea how to prevent this from happening? what and
> how should I do?
> Thanks a lot in advance for your help.
>

Log file is Full: How to prevent it happening?

"The log file for database 'DW_BackRoom_RT' is full. Back up the
transaction log for the database to free up some log space."
Now I only know this way to deal with that manually,
Step1. in option , chance Recovery model from FULL to Simple.
Step2: go to task to manually shrink the log file
Step3: Change recovery model back from simple to FULL.
But by this way, I could get same problem again, the log file is fill,
and need free up.
Could you give an idea how to prevent this from happening? what and
how should I do?
Thanks a lot in advance for your help.If you are periodically wiping the transaction log, then it sounds like it
is of no importance to you so I'd recommend leaving in simple recovery mode.
If you decide that the log is needed for DR, set up a backup plan which
includes full database backups and regular log backups.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hello,
If the data is this database is not critical/non production you can set the
recovery model to SIMPLE. THis will make sure that after
each checkpoint data will be writtent o disk and clear the transaction log
file. Incase if the data is critical keep the Recovery model
to FULL and make sure that you schedule a transction log backup (BACKUP
LOG). SO after each transction log backup the
LDF file will be cleared. THis will help you to restrict the growth of LDF
file and log backups will help you to recover the
database if needed as welll as you can perform Point_IN_Time recovery if
needed.
Take a look into Recovery models in Books online.
Thanks
Hari
<danceli@.gmail.com> wrote in message
news:1171053424.553101.106320@.s48g2000cws.googlegroups.com...
> "The log file for database 'DW_BackRoom_RT' is full. Back up the
> transaction log for the database to free up some log space."
> Now I only know this way to deal with that manually,
> Step1. in option , chance Recovery model from FULL to Simple.
> Step2: go to task to manually shrink the log file
> Step3: Change recovery model back from simple to FULL.
> But by this way, I could get same problem again, the log file is fill,
> and need free up.
> Could you give an idea how to prevent this from happening? what and
> how should I do?
> Thanks a lot in advance for your help.
>