Log Files

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

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

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

Log Files

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

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

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

log file to be reduced

Hi All
I have a mdf file of around 4gb but log file increased to
20gb. how to reduce size of log file.
Any help would be highly appreciated.
Rgds
TriveniHave a look at these articles
INF: How to Shrink the SQL Server 7.0 Transaction Lo
http://support.microsoft.com/default.aspx?scid=kb;en-us;25665
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFIL
http://support.microsoft.com/default.aspx?scid=kb;en-us;27231
http://www.mssqlserver.com/faq/logs-shrinklog.as
Hope this help
John

log file to be reduced

Hi All
I have a mdf file of around 4gb but log file increased to
20gb. how to reduce size of log file.
Any help would be highly appreciated.
Rgds
TriveniHave a look at these articles.
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Hope this helps
John

Log file size gets reduced

Hi,
I increased the total log file size of a database from 30 MB to 300 MB from
Enterprise Manager. I check it in Enterprise manager and it shows the size a
s
300 MB. However, every time I do that it gets reduced to 30 MB after some
time. What am I doing wrong?Hi,
You should check your maintenance plan. Maybe you set "remove unused space"
from logfile.
Regards,
Janos
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
> Hi,
> I increased the total log file size of a database from 30 MB to 300 MB
> from
> Enterprise Manager. I check it in Enterprise manager and it shows the size
> as
> 300 MB. However, every time I do that it gets reduced to 30 MB after some
> time. What am I doing wrong?|||Check if the autoshrink database option is set and also of you have any main
t job or similar that
perform shrink operations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
> Hi,
> I increased the total log file size of a database from 30 MB to 300 MB fro
m
> Enterprise Manager. I check it in Enterprise manager and it shows the size
as
> 300 MB. However, every time I do that it gets reduced to 30 MB after some
> time. What am I doing wrong?|||Hello,
If it is a production server and if the option Tibor mentioned is enabled
then it is recommended to uncheck the option. This will degrade the
performance if the
file gets auto shunk and then grow automatically.
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$Uxm4njHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Check if the autoshrink database option is set and also of you have any
> maint job or similar that perform shrink operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
>sql

Log file size gets reduced

Hi,
I increased the total log file size of a database from 30 MB to 300 MB from
Enterprise Manager. I check it in Enterprise manager and it shows the size as
300 MB. However, every time I do that it gets reduced to 30 MB after some
time. What am I doing wrong?Hi,
You should check your maintenance plan. Maybe you set "remove unused space"
from logfile.
Regards,
Janos
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
> Hi,
> I increased the total log file size of a database from 30 MB to 300 MB
> from
> Enterprise Manager. I check it in Enterprise manager and it shows the size
> as
> 300 MB. However, every time I do that it gets reduced to 30 MB after some
> time. What am I doing wrong?|||Check if the autoshrink database option is set and also of you have any maint job or similar that
perform shrink operations.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
> Hi,
> I increased the total log file size of a database from 30 MB to 300 MB from
> Enterprise Manager. I check it in Enterprise manager and it shows the size as
> 300 MB. However, every time I do that it gets reduced to 30 MB after some
> time. What am I doing wrong?|||Hello,
If it is a production server and if the option Tibor mentioned is enabled
then it is recommended to uncheck the option. This will degrade the
performance if the
file gets auto shunk and then grow automatically.
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$Uxm4njHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Check if the autoshrink database option is set and also of you have any
> maint job or similar that perform shrink operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
>> Hi,
>> I increased the total log file size of a database from 30 MB to 300 MB
>> from
>> Enterprise Manager. I check it in Enterprise manager and it shows the
>> size as
>> 300 MB. However, every time I do that it gets reduced to 30 MB after some
>> time. What am I doing wrong?
>

Log file size gets reduced

Hi,
I increased the total log file size of a database from 30 MB to 300 MB from
Enterprise Manager. I check it in Enterprise manager and it shows the size as
300 MB. However, every time I do that it gets reduced to 30 MB after some
time. What am I doing wrong?
Hi,
You should check your maintenance plan. Maybe you set "remove unused space"
from logfile.
Regards,
Janos
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
> Hi,
> I increased the total log file size of a database from 30 MB to 300 MB
> from
> Enterprise Manager. I check it in Enterprise manager and it shows the size
> as
> 300 MB. However, every time I do that it gets reduced to 30 MB after some
> time. What am I doing wrong?
|||Check if the autoshrink database option is set and also of you have any maint job or similar that
perform shrink operations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
> Hi,
> I increased the total log file size of a database from 30 MB to 300 MB from
> Enterprise Manager. I check it in Enterprise manager and it shows the size as
> 300 MB. However, every time I do that it gets reduced to 30 MB after some
> time. What am I doing wrong?
|||Hello,
If it is a production server and if the option Tibor mentioned is enabled
then it is recommended to uncheck the option. This will degrade the
performance if the
file gets auto shunk and then grow automatically.
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$Uxm4njHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Check if the autoshrink database option is set and also of you have any
> maint job or similar that perform shrink operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
>

Log file growth concern

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

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

|||

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

these are few guidelines

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

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

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

Refer :

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

Madhu

|||

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

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

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

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

backup log [DSS] with truncate_only

dbcc shrinkfile(DSS_Log)

|||

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

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

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

-Sue

|||

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

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

Thanks again!