Problems with log file size in SQL Server 2005

I've dealing with a newly created database in SQL Server 2005 Developer
Edition.
I'm having many trouble with the size of the log file. This database is
being used in a Merge Replication scenario and I'm currently creating,
deleting publications quite often. Initially I had autogrowth enabled
for the log file (initial size of 20 Mb), with 100 MBytes of maximum
size.
With this configuration, two hours ago, I deleted a publication and I
received this error:
The transaction log for database 'X is full. To find out why space in
the log cannot be reused, see the log_reuse_wait_desc column in
sys.databases
Changed database context to 'X'. (Microsoft SQL Server, Error: 9002)
I have now disabled autogrowth and have been able to delete the
publication, but I understand Merge Replication needs very much space
in the log file for all operations it performs.
Any advice or correct approach on this?
Thanks in advance.Hi
Do BACKUP LOG file as it described in the BOL
Read 'recovery [SQL Server replication]' topic in the BOL
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1141651209.039661.101840@.j33g2000cwa.googlegroups.com...
> I've dealing with a newly created database in SQL Server 2005 Developer
> Edition.
> I'm having many trouble with the size of the log file. This database is
> being used in a Merge Replication scenario and I'm currently creating,
> deleting publications quite often. Initially I had autogrowth enabled
> for the log file (initial size of 20 Mb), with 100 MBytes of maximum
> size.
> With this configuration, two hours ago, I deleted a publication and I
> received this error:
> The transaction log for database 'X is full. To find out why space in
> the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases
> Changed database context to 'X'. (Microsoft SQL Server, Error: 9002)
> I have now disabled autogrowth and have been able to delete the
> publication, but I understand Merge Replication needs very much space
> in the log file for all operations it performs.
> Any advice or correct approach on this?
> Thanks in advance.
>|||I'll read it, but at a first glance, what is exactly what I should do?
A backup of the log file every 'X' minutes or something like this?
Thanks very much.|||I tried doing a backup of the log before attempting to change the
publication's properties. This is what I read from BOL section you
suggested me. And I still have the log file with autogrowth disabled.
I tried to delete the current publication and.......same error
appears, that log file cannot be reused. So finally, in order to start
the Merge Agent again, I have to enable autogrowth for the log file,
which I don't like at all.
I need a concrete solution for this urgently!|||Lonifasiko wrote:
> I tried doing a backup of the log before attempting to change the
> publication's properties. This is what I read from BOL section you
> suggested me. And I still have the log file with autogrowth disabled.
> I tried to delete the current publication and.......same error
> appears, that log file cannot be reused. So finally, in order to start
> the Merge Agent again, I have to enable autogrowth for the log file,
> which I don't like at all.
> I need a concrete solution for this urgently!
>
What's the problem in having autogrow enabled? If the logfile really
needs the space, it don't help you anywhere to disable the autogrow.
The only thing that can help you, is to backup the logfile (regularly)
like Uri suggested. When you backup the log, it will be truncated which
means you can re-use the space that's occupied by the data that has been
backed up.
If you have some long running transactions though, these can "prevent"
the backup command from freeing up the space and then the space will not
be freed up and the logfile will have to grow.
Regards
Steen|||Hi,
I now see the unique way is backing up the log file and enabling again
autogrowth.
I've done a manteinance plan that backs up my log file once a day. This
way the log file will be reused? In the wizard, there is no any options
that lets me say "truncate transaction log". And I suppose log file
will continue growing........
When I select the database and then "Back up..." in Management Studio,
the unique log backup I can do is manual. I cannot program it as a DTS
or similar.
Therefore, it's correct the idea of executing a manteinance plan
regularly to prevent log files from being full?
Can you point me in the right direction (exactly what I must do) please
when performing log backups?
Thanks in advance.|||Lonifasiko wrote:
> Hi,
> I now see the unique way is backing up the log file and enabling again
> autogrowth.
> I've done a manteinance plan that backs up my log file once a day. This
> way the log file will be reused? In the wizard, there is no any options
> that lets me say "truncate transaction log". And I suppose log file
> will continue growing........
> When I select the database and then "Back up..." in Management Studio,
> the unique log backup I can do is manual. I cannot program it as a DTS
> or similar.
> Therefore, it's correct the idea of executing a manteinance plan
> regularly to prevent log files from being full?
> Can you point me in the right direction (exactly what I must do) please
> when performing log backups?
> Thanks in advance.
>
I'm not using Maintenance Plans to backup my database and log files, so
I might not be the right to guide you on that. Instead of using a
maintenance plan though, you can write your on SQL statement that back
up the log (and database if you like) and then schedule this script to
run at a certain interval.
You can look up the syntax for backing up you database and log with SQL
in Books On Line.
The short version of the syntax is :
BACKUP LOG YourDatabaseName TO DISK ='The path to your backup
directory\YourBackupFileName.bak'
and
BACKUP DATABASE YourDatabaseName TO DISK ='The path to your backup
directory\YourBackupFileName.trn'
If you choose to backup using SQL, it might be a good idea to expand the
script to use a variable for the backupfilename so you can give it new
name every time you back it up -e.g. based on the date.
Regards
Steen|||OK, I think I'll try next days with the manteinance plan I've written.
If it does not work this way, I'll try with doing it by script.
Sorry to be so repetitive, but back to my worrying log file size:
having log file maximum size limited to 200 MBytes and backing up the
log file regularly, can you surely tell me log file size will never go
over 200 MBytes?
That's exactly what I need. Thanks very much.|||> having log file maximum size limited to 200 MBytes and backing up the
> log file regularly, can you surely tell me log file size will never go
> over 200 MBytes?
If you set max size to 200MB, then SQL Server will not autogrow to more than
that. But max size
doesn't influence how the file can be recycled. If you have a large transact
ion or don't do log
backup often enough and have the size limited, then you will end up in error
9002 any time you do a
modification and the log file is full.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1141806712.812195.315820@.j52g2000cwj.googlegroups.com...
> OK, I think I'll try next days with the manteinance plan I've written.
> If it does not work this way, I'll try with doing it by script.
> Sorry to be so repetitive, but back to my worrying log file size:
> having log file maximum size limited to 200 MBytes and backing up the
> log file regularly, can you surely tell me log file size will never go
> over 200 MBytes?
> That's exactly what I need. Thanks very much.
>