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 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

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 File Size reduction

Dear All,
Our database log file in SQL 2000 is growing and filling the disk.
What are the option to reduce the log file size which is existing?
We tried the options :
DBCC Shrinkfile
SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
However the file size remained same.
Are we making any mistake? Which is the best way to reduce the file size and
keep the size to a preset limit (without affecting the trasaction after
reaching the limit)
Regards
SathianHi
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:e75ujo$sfo$1@.ns2.fe.internet.bosch.com...
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size
> and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>|||take a backup and restore upto that point, truncate the log.
do this when users are offline if you can.
delete redundant objects and temp tables and shrink the log files. if that
doesn't work get more disk.
"Sathian" wrote:

> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size a
nd
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||marcmc wrote:
> take a backup and restore upto that point, truncate the log.
> do this when users are offline if you can.
> delete redundant objects and temp tables and shrink the log files. if that
> doesn't work get more disk.
>
I don't really see any point in restoring anything. The problem seems
like you are running in FULL recovery mode and you've never backed up
your logfile.
If you are running on FULL recovery, you should make sure that you have
a recent backup of your database. Then you run a backup of your logfile.
Now you can run a DBCC SHRINKFILE(YourLogicalFileName, TargetSize) which
will try to shrink your logfile to the size you've specified.
Just keep in mind that if you have any open transactions, then you can't
shrink the file past these transactions.
If you don't care about your logfile backup, then you can also run the
backup log command with the NO_LOG option. This will only truncate the
log file and not create a log backup file. You can then afterwards run
the DBCC SHRINKFILE command.
Another good idea is also to look up "Truncating the transcation log" in
Books On Line.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Hi,
Here is every time working solution
1. Change recovery model into SIMPLE.
2. Truncate and shrink transaction log.
3. Resize log into required size.
Tomasz B.
"Sathian" wrote:

> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size a
nd
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||"Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
message news:EFCEBE5D-1ADA-49BB-AD52-93ECD498C7B7@.microsoft.com...
> Hi,
> Here is every time working solution
> 1. Change recovery model into SIMPLE.
> 2. Truncate and shrink transaction log.
> 3. Resize log into required size.
You forgot the critical (assuming this is a production database or one they
care about otherwise) of "backup"
Once you've changed the recovery model to simple, you've negated your backup
string.
> Tomasz B.
> "Sathian" wrote:
>
and|||If this is production server, and recovery mode=full, I will
1. backup transaction_log dbname to disk='filename'
2. dbcc shrinkfile logical_db name
3. schedule to backup transaction_log depand on needed + recovery purpose
--soonyu
"Sathian" wrote:

> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size a
nd
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>

Log File Size reduction

Dear All,
Our database log file in SQL 2000 is growing and filling the disk.
What are the option to reduce the log file size which is existing?
We tried the options :
DBCC Shrinkfile
SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
However the file size remained same.
Are we making any mistake? Which is the best way to reduce the file size and
keep the size to a preset limit (without affecting the trasaction after
reaching the limit)
Regards
SathianHi
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:e75ujo$sfo$1@.ns2.fe.internet.bosch.com...
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size
> and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>|||take a backup and restore upto that point, truncate the log.
do this when users are offline if you can.
delete redundant objects and temp tables and shrink the log files. if that
doesn't work get more disk.
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||marcmc wrote:
> take a backup and restore upto that point, truncate the log.
> do this when users are offline if you can.
> delete redundant objects and temp tables and shrink the log files. if that
> doesn't work get more disk.
>
I don't really see any point in restoring anything. The problem seems
like you are running in FULL recovery mode and you've never backed up
your logfile.
If you are running on FULL recovery, you should make sure that you have
a recent backup of your database. Then you run a backup of your logfile.
Now you can run a DBCC SHRINKFILE(YourLogicalFileName, TargetSize) which
will try to shrink your logfile to the size you've specified.
Just keep in mind that if you have any open transactions, then you can't
shrink the file past these transactions.
If you don't care about your logfile backup, then you can also run the
backup log command with the NO_LOG option. This will only truncate the
log file and not create a log backup file. You can then afterwards run
the DBCC SHRINKFILE command.
Another good idea is also to look up "Truncating the transcation log" in
Books On Line.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Hi,
Here is every time working solution
1. Change recovery model into SIMPLE.
2. Truncate and shrink transaction log.
3. Resize log into required size.
Tomasz B.
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||"Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
message news:EFCEBE5D-1ADA-49BB-AD52-93ECD498C7B7@.microsoft.com...
> Hi,
> Here is every time working solution
> 1. Change recovery model into SIMPLE.
> 2. Truncate and shrink transaction log.
> 3. Resize log into required size.
You forgot the critical (assuming this is a production database or one they
care about otherwise) of "backup"
Once you've changed the recovery model to simple, you've negated your backup
string.
> Tomasz B.
> "Sathian" wrote:
> > Dear All,
> >
> > Our database log file in SQL 2000 is growing and filling the disk.
> >
> > What are the option to reduce the log file size which is existing?
> >
> > We tried the options :
> >
> > DBCC Shrinkfile
> > SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> >
> > However the file size remained same.
> >
> > Are we making any mistake? Which is the best way to reduce the file size
and
> > keep the size to a preset limit (without affecting the trasaction after
> > reaching the limit)
> >
> > Regards
> >
> > Sathian
> >
> >
> >|||If this is production server, and recovery mode=full, I will
1. backup transaction_log dbname to disk='filename'
2. dbcc shrinkfile logical_db name
3. schedule to backup transaction_log depand on needed + recovery purpose
--soonyu
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>

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 issues

I am having a problem with the size of my primary filegroup. I have the
'automatically grow' option turned on. I have 7GB free disk space.But still
the log file complains that it does not have enough space to grow.Can anyone
please help me out? Thanks in advance.Can somebody pls help?
"HP" wrote:

> I am having a problem with the size of my primary filegroup. I have the
> 'automatically grow' option turned on. I have 7GB free disk space.But stil
l
> the log file complains that it does not have enough space to grow.Can anyo
ne
> please help me out? Thanks in advance.|||Do you back it up regularly ? (Assuming that you're using the FULL recovery
mode which should be the case)
Also, do you have heavy processing which could generate big T-Log ?
Chris
________________________________________
______
It's still better that if it would have been worst, isn't it ?
C'est toujours mieux que si c'etait pire !
"HP" <HP@.discussions.microsoft.com> a crit dans le message de news:
5D527A85-E4AC-4EA2-98AA-6910C81E534E@.microsoft.com...
>I am having a problem with the size of my primary filegroup. I have the
> 'automatically grow' option turned on. I have 7GB free disk space.But
> still
> the log file complains that it does not have enough space to grow.Can
> anyone
> please help me out? Thanks in advance.|||Iam getting this error when i try to change the datatype of a field in a
table which has 2 million records.
"Chris V." wrote:

> Do you back it up regularly ? (Assuming that you're using the FULL recover
y
> mode which should be the case)
> Also, do you have heavy processing which could generate big T-Log ?
> Chris
> --
> ________________________________________
______
> It's still better that if it would have been worst, isn't it ?
> C'est toujours mieux que si c'etait pire !
> "HP" <HP@.discussions.microsoft.com> a écrit dans le message de news:
> 5D527A85-E4AC-4EA2-98AA-6910C81E534E@.microsoft.com...
>
>|||You've got your winner...
To do such operation will generae a lot of writting in the transaction (to
be able to roll-back).
How look the transaction you're using for the update ?
________________________________________
______
It's still better that if it would have been worst, isn't it ?
C'est toujours mieux que si c'etait pire !
"HP" <HP@.discussions.microsoft.com> a crit dans le message de news:
2E0A42E1-8CCC-4F1F-806A-3C1EEC880FD3@.microsoft.com...
> Iam getting this error when i try to change the datatype of a field in a
> table which has 2 million records.
> "Chris V." wrote:
>|||HP
Well, sometimes autogrow is a little 'slow' and you get the error message,i
mean it could not allocate free space to grow the log.
Try change autogrow option to less percent or by 100MB.
"Chris V." <tophe_news@.hotmail.com> wrote in message
news:OAeKOgsJFHA.1040@.TK2MSFTNGP10.phx.gbl...
> You've got your winner...
> To do such operation will generae a lot of writting in the transaction (to
> be able to roll-back).
> How look the transaction you're using for the update ?
> --
> ________________________________________
______
> It's still better that if it would have been worst, isn't it ?
> C'est toujours mieux que si c'etait pire !
> "HP" <HP@.discussions.microsoft.com> a crit dans le message de news:
> 2E0A42E1-8CCC-4F1F-806A-3C1EEC880FD3@.microsoft.com...
the
>sql

Log file failed to grow despite lots of free disk space

Hello -
I had a strange problem last night and I wanted to ask if anyone else
has ever experienced it.
I have a transaction log set to Auto-grow by 10% with unrestricted
file growth. When I left last night the .ldf file took up 3GB of
space but only about 10% of it was used. I also had an additional 2GB
of free space on the disk which holds the log.
When I came in this morning, queries were timing out because the log
was full. The log file had NOT grown and Windows was reporting 2GB of
free space.
I cleared out a few other files, freeing up another 1GB and all of the
sudden the log grew and queries started working again.
Does anybody know what could have happened here? Are there any
reasons why the log file would fail to grow into remaining disk space?
Or, alternatively, could Windows ever report an incorrect amount of
free disk space?
Thanks!
David SimardSometimes, grow doesn't seem to be fast enough for the modifications. Could be such a case...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"DaveS" <davesimard@.hotmail.com> wrote in message
news:f8e708df.0310210656.2c65881b@.posting.google.com...
> Hello -
> I had a strange problem last night and I wanted to ask if anyone else
> has ever experienced it.
> I have a transaction log set to Auto-grow by 10% with unrestricted
> file growth. When I left last night the .ldf file took up 3GB of
> space but only about 10% of it was used. I also had an additional 2GB
> of free space on the disk which holds the log.
> When I came in this morning, queries were timing out because the log
> was full. The log file had NOT grown and Windows was reporting 2GB of
> free space.
> I cleared out a few other files, freeing up another 1GB and all of the
> sudden the log grew and queries started working again.
> Does anybody know what could have happened here? Are there any
> reasons why the log file would fail to grow into remaining disk space?
> Or, alternatively, could Windows ever report an incorrect amount of
> free disk space?
> Thanks!
> David Simard|||See if your error log contains a 'log file is full' message.
If so, it's the problem mentioned by tibor
This is the
"DaveS" <davesimard@.hotmail.com> wrote in message
news:f8e708df.0310210656.2c65881b@.posting.google.com...
> Hello -
> I had a strange problem last night and I wanted to ask if anyone else
> has ever experienced it.
> I have a transaction log set to Auto-grow by 10% with unrestricted
> file growth. When I left last night the .ldf file took up 3GB of
> space but only about 10% of it was used. I also had an additional 2GB
> of free space on the disk which holds the log.
> When I came in this morning, queries were timing out because the log
> was full. The log file had NOT grown and Windows was reporting 2GB of
> free space.
> I cleared out a few other files, freeing up another 1GB and all of the
> sudden the log grew and queries started working again.
> Does anybody know what could have happened here? Are there any
> reasons why the log file would fail to grow into remaining disk space?
> Or, alternatively, could Windows ever report an incorrect amount of
> free disk space?
> Thanks!
> David Simard|||If that were the case, wouldn't the problem resolve itself in moments?
We had queries timing out over a period of a few hours.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in message news:<uZTzBoGmDHA.372@.TK2MSFTNGP11.phx.gbl>...
> Sometimes, grow doesn't seem to be fast enough for the modifications. Could be such a case...
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "DaveS" <davesimard@.hotmail.com> wrote in message
> news:f8e708df.0310210656.2c65881b@.posting.google.com...
> > Hello -
> >
> > I had a strange problem last night and I wanted to ask if anyone else
> > has ever experienced it.
> >
> > I have a transaction log set to Auto-grow by 10% with unrestricted
> > file growth. When I left last night the .ldf file took up 3GB of
> > space but only about 10% of it was used. I also had an additional 2GB
> > of free space on the disk which holds the log.
> >
> > When I came in this morning, queries were timing out because the log
> > was full. The log file had NOT grown and Windows was reporting 2GB of
> > free space.
> >
> > I cleared out a few other files, freeing up another 1GB and all of the
> > sudden the log grew and queries started working again.
> >
> > Does anybody know what could have happened here? Are there any
> > reasons why the log file would fail to grow into remaining disk space?
> > Or, alternatively, could Windows ever report an incorrect amount of
> > free disk space?
> >
> > Thanks!
> >
> > David Simard|||Actually, now that you mention it: no, there weren't any 'log file is
full' messages in the log. I just assumed that was the problem since
the queries starting running again right after the log file grew.
But I guess I know what happens when you assume...
"Stressed" <k@.c.co.uk> wrote in message news:<#me0n5HmDHA.644@.TK2MSFTNGP11.phx.gbl>...
> See if your error log contains a 'log file is full' message.
> If so, it's the problem mentioned by tibor
> This is the
> "DaveS" <davesimard@.hotmail.com> wrote in message
> news:f8e708df.0310210656.2c65881b@.posting.google.com...
> > Hello -
> >
> > I had a strange problem last night and I wanted to ask if anyone else
> > has ever experienced it.
> >
> > I have a transaction log set to Auto-grow by 10% with unrestricted
> > file growth. When I left last night the .ldf file took up 3GB of
> > space but only about 10% of it was used. I also had an additional 2GB
> > of free space on the disk which holds the log.
> >
> > When I came in this morning, queries were timing out because the log
> > was full. The log file had NOT grown and Windows was reporting 2GB of
> > free space.
> >
> > I cleared out a few other files, freeing up another 1GB and all of the
> > sudden the log grew and queries started working again.
> >
> > Does anybody know what could have happened here? Are there any
> > reasons why the log file would fail to grow into remaining disk space?
> > Or, alternatively, could Windows ever report an incorrect amount of
> > free disk space?
> >
> > Thanks!
> >
> > David Simard|||Any other 'interesting' messages in the error log on the run up to this?
"DaveS" <davesimard@.hotmail.com> wrote in message
news:f8e708df.0310220842.f863fef@.posting.google.com...
> Actually, now that you mention it: no, there weren't any 'log file is
> full' messages in the log. I just assumed that was the problem since
> the queries starting running again right after the log file grew.
> But I guess I know what happens when you assume...
> "Stressed" <k@.c.co.uk> wrote in message
news:<#me0n5HmDHA.644@.TK2MSFTNGP11.phx.gbl>...
> > See if your error log contains a 'log file is full' message.
> >
> > If so, it's the problem mentioned by tibor
> >
> > This is the
> >
> > "DaveS" <davesimard@.hotmail.com> wrote in message
> > news:f8e708df.0310210656.2c65881b@.posting.google.com...
> > > Hello -
> > >
> > > I had a strange problem last night and I wanted to ask if anyone else
> > > has ever experienced it.
> > >
> > > I have a transaction log set to Auto-grow by 10% with unrestricted
> > > file growth. When I left last night the .ldf file took up 3GB of
> > > space but only about 10% of it was used. I also had an additional 2GB
> > > of free space on the disk which holds the log.
> > >
> > > When I came in this morning, queries were timing out because the log
> > > was full. The log file had NOT grown and Windows was reporting 2GB of
> > > free space.
> > >
> > > I cleared out a few other files, freeing up another 1GB and all of the
> > > sudden the log grew and queries started working again.
> > >
> > > Does anybody know what could have happened here? Are there any
> > > reasons why the log file would fail to grow into remaining disk space?
> > > Or, alternatively, could Windows ever report an incorrect amount of
> > > free disk space?
> > >
> > > Thanks!
> > >
> > > David Simard

log file dilemma! share disk with OS or Data file?

Generally speaking, If you have to pick a poision between sharing a disk
with Operating system drive or sharing a disk with Data file drive, which
one is the worse in terms of system performance?
Here is the scenerio: I have a mirrored drive where I put OS on c$ partition
and created another partition of d$. And we have 4 more drive left.
My options are:
1. Create RAID 5 volume from all 4 drives and put data and log file on that
volume. The advantage is that Bigger disk space usage but very bad on system
performance.
2. Create RAID5 volume from all 4 drive but put only Data file in that RAID
5 and put log file on d$ (The drive where OS files are).
3. Create Two RAID 1 drive and put data on first RAID and log on 2nd RAID.
This is best for performance but I loose lots of disk space.
I know no. 3 option is the best out of 3. But, if you have to choose between
no. 1 and 2, which would you prefer? and how bad the performance hit will be
if we compare no. 3 against no. 1 and no. 3 against no. 2?
I appreciate your answer. SQL SERVER 2005 SP2I would say Option 2. Once the system is up and running there should not be
much access to the OS volume so the logs would not have too much contention.
This is assuming you don't have other issues that might cause problems like
excessive paging or another process writing a lot of data to the OS volume.
-jeff
"James" wrote:

> Generally speaking, If you have to pick a poision between sharing a disk
> with Operating system drive or sharing a disk with Data file drive, which
> one is the worse in terms of system performance?
> Here is the scenerio: I have a mirrored drive where I put OS on c$ partiti
on
> and created another partition of d$. And we have 4 more drive left.
> My options are:
> 1. Create RAID 5 volume from all 4 drives and put data and log file on tha
t
> volume. The advantage is that Bigger disk space usage but very bad on syst
em
> performance.
> 2. Create RAID5 volume from all 4 drive but put only Data file in that RAI
D
> 5 and put log file on d$ (The drive where OS files are).
> 3. Create Two RAID 1 drive and put data on first RAID and log on 2nd RAID.
> This is best for performance but I loose lots of disk space.
> I know no. 3 option is the best out of 3. But, if you have to choose betwe
en
> no. 1 and 2, which would you prefer? and how bad the performance hit will
be
> if we compare no. 3 against no. 1 and no. 3 against no. 2?
> I appreciate your answer. SQL SERVER 2005 SP2
>
>

log file dilemma! share disk with OS or Data file?

Generally speaking, If you have to pick a poision between sharing a disk
with Operating system drive or sharing a disk with Data file drive, which
one is the worse in terms of system performance?
Here is the scenerio: I have a mirrored drive where I put OS on c$ partition
and created another partition of d$. And we have 4 more drive left.
My options are:
1. Create RAID 5 volume from all 4 drives and put data and log file on that
volume. The advantage is that Bigger disk space usage but very bad on system
performance.
2. Create RAID5 volume from all 4 drive but put only Data file in that RAID
5 and put log file on d$ (The drive where OS files are).
3. Create Two RAID 1 drive and put data on first RAID and log on 2nd RAID.
This is best for performance but I loose lots of disk space.
I know no. 3 option is the best out of 3. But, if you have to choose between
no. 1 and 2, which would you prefer? and how bad the performance hit will be
if we compare no. 3 against no. 1 and no. 3 against no. 2?
I appreciate your answer. SQL SERVER 2005 SP2
I would say Option 2. Once the system is up and running there should not be
much access to the OS volume so the logs would not have too much contention.
This is assuming you don't have other issues that might cause problems like
excessive paging or another process writing a lot of data to the OS volume.
-jeff
"James" wrote:

> Generally speaking, If you have to pick a poision between sharing a disk
> with Operating system drive or sharing a disk with Data file drive, which
> one is the worse in terms of system performance?
> Here is the scenerio: I have a mirrored drive where I put OS on c$ partition
> and created another partition of d$. And we have 4 more drive left.
> My options are:
> 1. Create RAID 5 volume from all 4 drives and put data and log file on that
> volume. The advantage is that Bigger disk space usage but very bad on system
> performance.
> 2. Create RAID5 volume from all 4 drive but put only Data file in that RAID
> 5 and put log file on d$ (The drive where OS files are).
> 3. Create Two RAID 1 drive and put data on first RAID and log on 2nd RAID.
> This is best for performance but I loose lots of disk space.
> I know no. 3 option is the best out of 3. But, if you have to choose between
> no. 1 and 2, which would you prefer? and how bad the performance hit will be
> if we compare no. 3 against no. 1 and no. 3 against no. 2?
> I appreciate your answer. SQL SERVER 2005 SP2
>
>

log file dilemma! share disk with OS or Data file?

Generally speaking, If you have to pick a poision between sharing a disk
with Operating system drive or sharing a disk with Data file drive, which
one is the worse in terms of system performance?
Here is the scenerio: I have a mirrored drive where I put OS on c$ partition
and created another partition of d$. And we have 4 more drive left.
My options are:
1. Create RAID 5 volume from all 4 drives and put data and log file on that
volume. The advantage is that Bigger disk space usage but very bad on system
performance.
2. Create RAID5 volume from all 4 drive but put only Data file in that RAID
5 and put log file on d$ (The drive where OS files are).
3. Create Two RAID 1 drive and put data on first RAID and log on 2nd RAID.
This is best for performance but I loose lots of disk space.
I know no. 3 option is the best out of 3. But, if you have to choose between
no. 1 and 2, which would you prefer? and how bad the performance hit will be
if we compare no. 3 against no. 1 and no. 3 against no. 2?
I appreciate your answer. SQL SERVER 2005 SP2I would say Option 2. Once the system is up and running there should not be
much access to the OS volume so the logs would not have too much contention.
This is assuming you don't have other issues that might cause problems like
excessive paging or another process writing a lot of data to the OS volume.
-jeff
"James" wrote:
> Generally speaking, If you have to pick a poision between sharing a disk
> with Operating system drive or sharing a disk with Data file drive, which
> one is the worse in terms of system performance?
> Here is the scenerio: I have a mirrored drive where I put OS on c$ partition
> and created another partition of d$. And we have 4 more drive left.
> My options are:
> 1. Create RAID 5 volume from all 4 drives and put data and log file on that
> volume. The advantage is that Bigger disk space usage but very bad on system
> performance.
> 2. Create RAID5 volume from all 4 drive but put only Data file in that RAID
> 5 and put log file on d$ (The drive where OS files are).
> 3. Create Two RAID 1 drive and put data on first RAID and log on 2nd RAID.
> This is best for performance but I loose lots of disk space.
> I know no. 3 option is the best out of 3. But, if you have to choose between
> no. 1 and 2, which would you prefer? and how bad the performance hit will be
> if we compare no. 3 against no. 1 and no. 3 against no. 2?
> I appreciate your answer. SQL SERVER 2005 SP2
>
>