Sql Update command: log

Showing posts with label log. Show all posts

Monday, March 26, 2012

Problems deleting transaction log files older than x days

Hi,
I have a problem with maintainance plans an deleting
transaction log files older than x days. I have made one
maintainance plan for databases and that one works.
But the one made for the transaction log doesn't delete
the old files. They remain and aren't deleted
automatically.
brgds
PeterBelow KB might help:
http://support.microsoft.com/defaul...2&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Peter Junell" <peter.junell@.silverhuset.se> wrote in message
news:9b0601c405c2$d5967c20$a101280a@.phx.gbl...
> Hi,
> I have a problem with maintainance plans an deleting
> transaction log files older than x days. I have made one
> maintainance plan for databases and that one works.
> But the one made for the transaction log doesn't delete
> the old files. They remain and aren't deleted
> automatically.
> brgds
> Peter

Wednesday, March 21, 2012

Problems configuring Execution Log Reports

I receive the following error:
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot create a connection to data source 'RSExecutionLog'.
(rsErrorOpeningConnection) Get Online Help
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
My ReportServer DB is separate from my ReportServer IIS boxes, I'm trying to
use Windows NT Integrated Security to access the Execution Log. I'm a Local
Admin of both servers but still receive the error.
Am I dealing with a permissions hop issue? Any suggestions would help.
DavidYes. Use stored credentials in the shared data source for the reports
(another slightly more complex option would be to use Kerberos delegation)
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"David Dubay" <DavidDubay@.discussions.microsoft.com> wrote in message
news:30C3BBF3-5DE7-4F4E-9008-349CA172D6EC@.microsoft.com...
>I receive the following error:
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot create a connection to data source 'RSExecutionLog'.
> (rsErrorOpeningConnection) Get Online Help
> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
> My ReportServer DB is separate from my ReportServer IIS boxes, I'm trying
> to
> use Windows NT Integrated Security to access the Execution Log. I'm a
> Local
> Admin of both servers but still receive the error.
> Am I dealing with a permissions hop issue? Any suggestions would help.
> David

Friday, March 9, 2012

Problem! Transaction log backup job

Hi there,
I have a problem which began yesterday (monday). Till that day we have been
running a full backup job at 02:00 am - and then in working hours (09:00
am - 7:00 pm) we have been running a transaction log backup every hour -
this has been working fine for about a year.
Sunday we imported some data into our Navision (backend is the SQL 2000 Srv
SP3a).
Now the translog backup job just stands "Executing job step '(step 1)'" for
a very long time (i manually stop it every time) - this job takes 0.390
seconds normally.
In Enterprise Manager the database states that it is 16.266 MB large, and
1435 MB free. This seems as alot to me...
Please help - is there some maintenance I can run to check something...? And
can it be done while users are working? I'm pretty lost.
Best regards
Jakob H. HeidelbergSocrates
What does the job do? Full database backup or transaction log backup
How may data you are inserted?
Did you do it with one statement?
Also use DBCC LOGINFO(dtabase_id) to see whether or not there are opened
transaction.
Status 2 in the bottom lime indicates that there are active transaction/s
""Sokrates"" <remove@.noshit.dk> wrote in message
news:c0smip$7pe$1@.news.cybercity.dk...
> Hi there,
> I have a problem which began yesterday (monday). Till that day we have
been
> running a full backup job at 02:00 am - and then in working hours (09:00
> am - 7:00 pm) we have been running a transaction log backup every hour -
> this has been working fine for about a year.
> Sunday we imported some data into our Navision (backend is the SQL 2000
Srv
> SP3a).
> Now the translog backup job just stands "Executing job step '(step 1)'"
for
> a very long time (i manually stop it every time) - this job takes 0.390
> seconds normally.
> In Enterprise Manager the database states that it is 16.266 MB large, and
> 1435 MB free. This seems as alot to me...
> Please help - is there some maintenance I can run to check something...?
And
> can it be done while users are working? I'm pretty lost.
>
> Best regards
> Jakob H. Heidelberg
>|||Hi,
Execute sp_who from Query analyzer and check the BACKUP TRANSACTION process
id is blocked. If blocked possibly kill the
process id locking this process.
If there is no blocks then execute DBCC SQLPERF(LOGSPACE) and check the log
size. If the size Log is huge then the backup log
will take some time.
How to identify the log backup is going on,
From Query analyzer execute XP_FIXEDDRIVES and check whether the hard disk
free space is coming down.
Thanks
Hari
MCDBA
""Sokrates"" <remove@.noshit.dk> wrote in message
news:c0smip$7pe$1@.news.cybercity.dk...
> Hi there,
> I have a problem which began yesterday (monday). Till that day we have
been
> running a full backup job at 02:00 am - and then in working hours (09:00
> am - 7:00 pm) we have been running a transaction log backup every hour -
> this has been working fine for about a year.
> Sunday we imported some data into our Navision (backend is the SQL 2000
Srv
> SP3a).
> Now the translog backup job just stands "Executing job step '(step 1)'"
for
> a very long time (i manually stop it every time) - this job takes 0.390
> seconds normally.
> In Enterprise Manager the database states that it is 16.266 MB large, and
> 1435 MB free. This seems as alot to me...
> Please help - is there some maintenance I can run to check something...?
And
> can it be done while users are working? I'm pretty lost.
>
> Best regards
> Jakob H. Heidelberg
>|||It seems your import produces a very large amount of log records which of
course will be backed up when you backup the log. Use DBCC SQLPERF(logspace)
to see size of log and how full it is. Of you want to do a once off empty of
the log, use
BACKUP LOG dbname WITH NO_LOG
However, subsequent log backups will now be useless until you do a db
backup.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
""Sokrates"" <remove@.noshit.dk> wrote in message
news:c0smip$7pe$1@.news.cybercity.dk...
> Hi there,
> I have a problem which began yesterday (monday). Till that day we have
been
> running a full backup job at 02:00 am - and then in working hours (09:00
> am - 7:00 pm) we have been running a transaction log backup every hour -
> this has been working fine for about a year.
> Sunday we imported some data into our Navision (backend is the SQL 2000
Srv
> SP3a).
> Now the translog backup job just stands "Executing job step '(step 1)'"
for
> a very long time (i manually stop it every time) - this job takes 0.390
> seconds normally.
> In Enterprise Manager the database states that it is 16.266 MB large, and
> 1435 MB free. This seems as alot to me...
> Please help - is there some maintenance I can run to check something...?
And
> can it be done while users are working? I'm pretty lost.
>
> Best regards
> Jakob H. Heidelberg
>|||Thank you all for the responses - it's wonderfull to feel the team such a
teamspirit ;)
I did the "dbcc sqlperf(logspace)" wich showed more that 9 GB of the
database I'm having troubles with. I then did the "backup log database with
no_log" and everything seems to work nicely now. The job can run again, so
it must have been to many transactions for it to handle...?
As I understand it, my backup tonight at 02:00 (a FULL backup) will be the
basis for the translog backup I will perform through the day (every hour)
tomorrow - right?
Thanx all!
Jakob
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
en meddelelse news:ul10AVU9DHA.1548@.tk2msftngp13.phx.gbl...
> It seems your import produces a very large amount of log records which of
> course will be backed up when you backup the log. Use DBCC
SQLPERF(logspace)
> to see size of log and how full it is. Of you want to do a once off empty
of
> the log, use
> BACKUP LOG dbname WITH NO_LOG
> However, subsequent log backups will now be useless until you do a db
> backup.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> ""Sokrates"" <remove@.noshit.dk> wrote in message
> news:c0smip$7pe$1@.news.cybercity.dk...
> > Hi there,
> >
> > I have a problem which began yesterday (monday). Till that day we have
> been
> > running a full backup job at 02:00 am - and then in working hours (09:00
> > am - 7:00 pm) we have been running a transaction log backup every hour -
> > this has been working fine for about a year.
> >
> > Sunday we imported some data into our Navision (backend is the SQL 2000
> Srv
> > SP3a).
> >
> > Now the translog backup job just stands "Executing job step '(step 1)'"
> for
> > a very long time (i manually stop it every time) - this job takes 0.390
> > seconds normally.
> >
> > In Enterprise Manager the database states that it is 16.266 MB large,
and
> > 1435 MB free. This seems as alot to me...
> >
> > Please help - is there some maintenance I can run to check something...?
> And
> > can it be done while users are working? I'm pretty lost.
> >
> >
> > Best regards
> > Jakob H. Heidelberg
> >
> >
>|||Jakob,
> As I understand it, my backup tonight at 02:00 (a FULL backup) will be the
> basis for the translog backup I will perform through the day (every hour)
> tomorrow - right?
Yes, you have now broken the log sequence, so the log backups you take from
now up to 02:00 will be useless. When you do a db backup the following log
backups will be usable based on that db backup. (Think of what you did as
doing a log backup and throwing away the backup file.)
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
""Sokrates"" <remove@.noshit.dk> wrote in message
news:c0t5ge$lgj$1@.news.cybercity.dk...
> Thank you all for the responses - it's wonderfull to feel the team such a
> teamspirit ;)
> I did the "dbcc sqlperf(logspace)" wich showed more that 9 GB of the
> database I'm having troubles with. I then did the "backup log database
with
> no_log" and everything seems to work nicely now. The job can run again, so
> it must have been to many transactions for it to handle...?
> As I understand it, my backup tonight at 02:00 (a FULL backup) will be the
> basis for the translog backup I will perform through the day (every hour)
> tomorrow - right?
>
> Thanx all!
> Jakob
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
> en meddelelse news:ul10AVU9DHA.1548@.tk2msftngp13.phx.gbl...
> > It seems your import produces a very large amount of log records which
of
> > course will be backed up when you backup the log. Use DBCC
> SQLPERF(logspace)
> > to see size of log and how full it is. Of you want to do a once off
empty
> of
> > the log, use
> > BACKUP LOG dbname WITH NO_LOG
> >
> > However, subsequent log backups will now be useless until you do a db
> > backup.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > ""Sokrates"" <remove@.noshit.dk> wrote in message
> > news:c0smip$7pe$1@.news.cybercity.dk...
> > > Hi there,
> > >
> > > I have a problem which began yesterday (monday). Till that day we have
> > been
> > > running a full backup job at 02:00 am - and then in working hours
(09:00
> > > am - 7:00 pm) we have been running a transaction log backup every
hour -
> > > this has been working fine for about a year.
> > >
> > > Sunday we imported some data into our Navision (backend is the SQL
2000
> > Srv
> > > SP3a).
> > >
> > > Now the translog backup job just stands "Executing job step '(step
1)'"
> > for
> > > a very long time (i manually stop it every time) - this job takes
0.390
> > > seconds normally.
> > >
> > > In Enterprise Manager the database states that it is 16.266 MB large,
> and
> > > 1435 MB free. This seems as alot to me...
> > >
> > > Please help - is there some maintenance I can run to check
something...?
> > And
> > > can it be done while users are working? I'm pretty lost.
> > >
> > >
> > > Best regards
> > > Jakob H. Heidelberg
> > >
> > >
> >
> >
>|||Thanx, my backup ran nicelys today - 02:00 the FULL and then in the working
hours the TRANS LOG backup job did the job - so thanx, we are back "on
line".
Jakob
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
en meddelelse news:%23GmlKKW9DHA.3444@.TK2MSFTNGP09.phx.gbl...
> Jakob,
> > As I understand it, my backup tonight at 02:00 (a FULL backup) will be
the
> > basis for the translog backup I will perform through the day (every
hour)
> > tomorrow - right?
> Yes, you have now broken the log sequence, so the log backups you take
from
> now up to 02:00 will be useless. When you do a db backup the following log
> backups will be usable based on that db backup. (Think of what you did as
> doing a log backup and throwing away the backup file.)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> ""Sokrates"" <remove@.noshit.dk> wrote in message
> news:c0t5ge$lgj$1@.news.cybercity.dk...
> > Thank you all for the responses - it's wonderfull to feel the team such
a
> > teamspirit ;)
> >
> > I did the "dbcc sqlperf(logspace)" wich showed more that 9 GB of the
> > database I'm having troubles with. I then did the "backup log database
> with
> > no_log" and everything seems to work nicely now. The job can run again,
so
> > it must have been to many transactions for it to handle...?
> >
> > As I understand it, my backup tonight at 02:00 (a FULL backup) will be
the
> > basis for the translog backup I will perform through the day (every
hour)
> > tomorrow - right?
> >
> >
> > Thanx all!
> > Jakob
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev
i
> > en meddelelse news:ul10AVU9DHA.1548@.tk2msftngp13.phx.gbl...
> > > It seems your import produces a very large amount of log records which
> of
> > > course will be backed up when you backup the log. Use DBCC
> > SQLPERF(logspace)
> > > to see size of log and how full it is. Of you want to do a once off
> empty
> > of
> > > the log, use
> > > BACKUP LOG dbname WITH NO_LOG
> > >
> > > However, subsequent log backups will now be useless until you do a db
> > > backup.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> > >
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > ""Sokrates"" <remove@.noshit.dk> wrote in message
> > > news:c0smip$7pe$1@.news.cybercity.dk...
> > > > Hi there,
> > > >
> > > > I have a problem which began yesterday (monday). Till that day we
have
> > > been
> > > > running a full backup job at 02:00 am - and then in working hours
> (09:00
> > > > am - 7:00 pm) we have been running a transaction log backup every
> hour -
> > > > this has been working fine for about a year.
> > > >
> > > > Sunday we imported some data into our Navision (backend is the SQL
> 2000
> > > Srv
> > > > SP3a).
> > > >
> > > > Now the translog backup job just stands "Executing job step '(step
> 1)'"
> > > for
> > > > a very long time (i manually stop it every time) - this job takes
> 0.390
> > > > seconds normally.
> > > >
> > > > In Enterprise Manager the database states that it is 16.266 MB
large,
> > and
> > > > 1435 MB free. This seems as alot to me...
> > > >
> > > > Please help - is there some maintenance I can run to check
> something...?
> > > And
> > > > can it be done while users are working? I'm pretty lost.
> > > >
> > > >
> > > > Best regards
> > > > Jakob H. Heidelberg
> > > >
> > > >
> > >
> > >
> >
> >
>

Problem! Transaction log backup job

Monday, February 20, 2012

Problem with updating column in table - transaction log full

Hello,
I had trouble performing an update of one column of a table.
The column is of Bit data type and the table has 60350 rows.
When I run the following query in the Query Analyzer:
update [Specimen CV] set HPV = 0
I get a message "The log file for database DBNAME is full. Back up the
transaction log for the database to free up space"
I backed up both the DB and the log, truncated as well as increased the size
of the log file, first to 100MB.
I even set the DB to simple recovery mode. Still, I got the same message.
Finally, when I set the size to 1 GB did the update run.
After the update ran I checked the Log Space used and it was 12.6%
Does really a simple query like that on a table with only 60K records
require 126 MB of log space?
Any comment would be appreciated.
RagnarOn Mar 7, 7:54 pm, "Ragnar Midtskogen" <ragnar...@.newsgroups.com>
wrote:
> Hello,
> I had trouble performing an update of one column of a table.
> The column is of Bit data type and the table has 60350 rows.
> When I run the following query in the Query Analyzer:
> update [Specimen CV] set HPV = 0
> I get a message "The log file for database DBNAME is full. Back up the
> transaction log for the database to free up space"
> I backed up both the DB and the log, truncated as well as increased the si
ze
> of the log file, first to 100MB.
> I even set the DB to simple recovery mode. Still, I got the same message.
> Finally, when I set the size to 1 GB did the update run.
> After the update ran I checked the Log Space used and it was 12.6%
> Does really a simple query like that on a table with only 60K records
> require 126 MB of log space?
> Any comment would be appreciated.
> Ragnar
Are there triggers on this table? Your single update statement, while
seemingly simple, is creating an implicit transaction, and any change
that the update produces, directly or indirectly, is captured within
that transaction. The log file, which stores the "undo" information
for all of these changes, must be large enough to hold everything that
the update statement is changing, within the table being updated, AND
any other tables that are being affected by triggers on that table.|||Thank you Tracy,
There are no triggers on any of the tables.
I guess this was not an indication of anything wrong.
By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but I have been digging through the SQL Server Books
Online without finding anything about this.
I have seen a few cases in our company where someone has set up an SQL
Server DB without setting up regular backups, where the log just kept
growing. In one case the log used up all the available space on the disk and
effectively shut down the computer, not enough room for the swap file.
I think there is a way to control the growth of the log file, but I have not
found out how to do this.
Any pointers would be appreciated.
Ragnar|||How much space is used for the log is dictated by how much log records you g
enerate and how often
you empty the log. To empty the log, you have basically two options:
Have the database in simple recovery mode. Now SQL Server will empty the log
automatically (every
time a checkpoint occurs).
Have it in full recovery mode and do regular transaction log backups.
I suggest you read a bit about recovery models and backup to get more insigh
t into this. Also, you
might want to check out (related reading):
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://sqlblog.com/blogs/tibor_kara...>
rinking.aspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e8F6%23RaYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> Thank you Tracy,
> There are no triggers on any of the tables.
> I guess this was not an indication of anything wrong.
> By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but
> I have been digging through the SQL Server Books Online without finding an
ything about this.
> I have seen a few cases in our company where someone has set up an SQL Ser
ver DB without setting
> up regular backups, where the log just kept growing. In one case the log u
sed up all the available
> space on the disk and effectively shut down the computer, not enough room
for the swap file.
> I think there is a way to control the growth of the log file, but I have n
ot found out how to do
> this.
> Any pointers would be appreciated.
> Ragnar
>

Problem with updating column in table - transaction log full

Hello,
I had trouble performing an update of one column of a table.
The column is of Bit data type and the table has 60350 rows.
When I run the following query in the Query Analyzer:
update [Specimen CV] set HPV = 0
I get a message "The log file for database DBNAME is full. Back up the
transaction log for the database to free up space"
I backed up both the DB and the log, truncated as well as increased the size
of the log file, first to 100MB.
I even set the DB to simple recovery mode. Still, I got the same message.
Finally, when I set the size to 1 GB did the update run.
After the update ran I checked the Log Space used and it was 12.6%
Does really a simple query like that on a table with only 60K records
require 126 MB of log space?
Any comment would be appreciated.
RagnarOn Mar 7, 7:54 pm, "Ragnar Midtskogen" <ragnar...@.newsgroups.com>
wrote:
> Hello,
> I had trouble performing an update of one column of a table.
> The column is of Bit data type and the table has 60350 rows.
> When I run the following query in the Query Analyzer:
> update [Specimen CV] set HPV = 0
> I get a message "The log file for database DBNAME is full. Back up the
> transaction log for the database to free up space"
> I backed up both the DB and the log, truncated as well as increased the size
> of the log file, first to 100MB.
> I even set the DB to simple recovery mode. Still, I got the same message.
> Finally, when I set the size to 1 GB did the update run.
> After the update ran I checked the Log Space used and it was 12.6%
> Does really a simple query like that on a table with only 60K records
> require 126 MB of log space?
> Any comment would be appreciated.
> Ragnar
Are there triggers on this table? Your single update statement, while
seemingly simple, is creating an implicit transaction, and any change
that the update produces, directly or indirectly, is captured within
that transaction. The log file, which stores the "undo" information
for all of these changes, must be large enough to hold everything that
the update statement is changing, within the table being updated, AND
any other tables that are being affected by triggers on that table.|||Thank you Tracy,
There are no triggers on any of the tables.
I guess this was not an indication of anything wrong.
By the way, I seem to remember that, depending on some settings, the log
will grow if needed, but I have been digging through the SQL Server Books
Online without finding anything about this.
I have seen a few cases in our company where someone has set up an SQL
Server DB without setting up regular backups, where the log just kept
growing. In one case the log used up all the available space on the disk and
effectively shut down the computer, not enough room for the swap file.
I think there is a way to control the growth of the log file, but I have not
found out how to do this.
Any pointers would be appreciated.
Ragnar|||How much space is used for the log is dictated by how much log records you generate and how often
you empty the log. To empty the log, you have basically two options:
Have the database in simple recovery mode. Now SQL Server will empty the log automatically (every
time a checkpoint occurs).
Have it in full recovery mode and do regular transaction log backups.
I suggest you read a bit about recovery models and backup to get more insight into this. Also, you
might want to check out (related reading):
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e8F6%23RaYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> Thank you Tracy,
> There are no triggers on any of the tables.
> I guess this was not an indication of anything wrong.
> By the way, I seem to remember that, depending on some settings, the log will grow if needed, but
> I have been digging through the SQL Server Books Online without finding anything about this.
> I have seen a few cases in our company where someone has set up an SQL Server DB without setting
> up regular backups, where the log just kept growing. In one case the log used up all the available
> space on the disk and effectively shut down the computer, not enough room for the swap file.
> I think there is a way to control the growth of the log file, but I have not found out how to do
> this.
> Any pointers would be appreciated.
> Ragnar
>

Monday, March 26, 2012

Problems deleting transaction log files older than x days

Wednesday, March 21, 2012

Problems configuring Execution Log Reports

Friday, March 9, 2012

Problem! Transaction log backup job

Problem! Transaction log backup job

Monday, February 20, 2012

Problem with updating column in table - transaction log full

Problem with updating column in table - transaction log full

Problem with updating column in table - transaction log full

Sql Update command

Blog Archive

About Me