DB Maintenance Plan - single user mode

[Mark S]

I have a db maintenance plan which is set to backup (then truncate hopefully)
the transaction log. In order to backup a transaction log the db must be in
single user mode so the maint. plan fails. How do you automatically set the db,
in single user mode, for the transaction log to be backed up then truncated?

Also, I manually set the db in single user mode and it shows itself to be in
single user mode yet the number of users in the db properties says there are 8
users connected. What's this about?

Thanks

Mark

[Mark S]

I realize that the recovery option must be full or bulk-logged for db backup to
occur, so I guess I can use t-sql to truncate but it is not preferred.

Mark S wrote:

I have a db maintenance plan which is set to backup (then truncate
hopefully) the transaction log. In order to backup a transaction log the
db must be in single user mode so the maint. plan fails. How do you
automatically set the db, in single user mode, for the transaction log
to be backed up then truncated?

Also, I manually set the db in single user mode and it shows itself to
be in single user mode yet the number of users in the db properties says
there are 8 users connected. What's this about?

Thanks

Mark

[Geoff N. Hiten]

Simple recovery mode marks the inactive portion of the log to truncate after
the data is checkpointed. No additional steps are required. Note that
truncating the log has no effect on the log file(s) size(s). The truncation
is internal. Shrinking the transaction log files requires using the DBCC
SHRINKFILE command.

Read BOL for more information on choosing a recovery model and implementing
t-log and full backups.

--
Geoff N. Hiten
SQL Server MVP
Senior Database Administrator
Careerbuilder.c om

"Mark S" <ma**********@a v.com> wrote in message
news:bh******** **@nntp2-cm.news.eni.net ...

I realize that the recovery option must be full or bulk-logged for db backup to occur, so I guess I can use t-sql to truncate but it is not preferred.

Mark S wrote:

I have a db maintenance plan which is set to backup (then truncate
hopefully) the transaction log. In order to backup a transaction log the
db must be in single user mode so the maint. plan fails. How do you
automatically set the db, in single user mode, for the transaction log
to be backed up then truncated?

Also, I manually set the db in single user mode and it shows itself to
be in single user mode yet the number of users in the db properties says
there are 8 users connected. What's this about?

Thanks

Mark

[jj]

I had the same problem. Every time I tried to backup the
transaction log (included in my Maintenance Plan), I got an
error that the log could not be backed up, unless the DB was
in single-user mode.

I read through several books, BOS, even posted several
messages on this newsgroup, just like you. But, could not
get my answer. I finally got the answer.

First, ignore the "single user mode" error. I don't know
why SQL Server provides this error message.

The reason why you can't backup the log (and several of the
system databases), is because you have your database in
SIMPLE RECOVERY MODE. You need to change this to FULL
recovery mode.

Can you believe this? After taking a course in SQL Server
2000 Administration, reading 2 full training books, and
working with SQL Server 2K for over 2 years, that NOWHERE
did I find out that the reason the transaction log wouldn't
backup, is because the DB recovery method was set to SIMPLE?
I can't believe something this important isn't covered or
mentioned in documentation. Nor, was anyone on this
newsgroup able to help. That's why I'm taking the time to
help you, because I wish someone would have given me this
information several years ago.

As a result, only recently was I able to backup my
transaction log, for the first time in almost 2 years. It
was 13 Gigs in size. (Now, it's about 20 megs).

To do this, open Enterprise Mgr, and right-click on your
database, and choose PROPERTIES. Under the OPTIONS tab,
change recovery mode to FULL. After doing this, you might
want to take your database OFFLINE, then ONLINE. To do
this, just right-click your database, and choose TAKE
OFFLINE. Then, wait 10-15 seconds, then choose BRING ONLINE.

You can now backup your log, and any of the system databases
that wouldn't backup.

Keep in mind that when you have your database in SIMPLE
recovery mode, you can only RESTORE the database from the
last FULL BACKUP performed on your database.

If recovery mode is set to FULL, you can RESTORE your
database at any point, from the last full backup to current
day/time. However, in FULL mode, the transaction log will
grow. This is because the log is used to help restore your
database, so it keeps track of all the transactions since
the last backup.

Also, in FULL mode, you'll need to regularly backup your
database AND transaction log. And remember to SHRINK the DB
and LOG after the backup, in order to keep the transaction
log to a reasonable size. Otherwise, if you don't shrink
it, it will grow and grow (if your settings allow unlimited
growth), until all your disk space is used up.

Many people backup the log every n hours, or once/day, and
run the backups daily or weekly.

Sometimes when you use the SHRINK database command (using
FILES, to select the LOG file), it doesn't actually shrink
the LOG. So, you may have to use the DBCC SHRINKDATABASE or
SHRINKFILE transact SQL. I still haven't figured this one
out, as far as the syntax.

So, I use a technique, taught to me by a nice person on this
newsgroup, that WILL shrink the log file (in actual size),
using EM's SHRINK DATABASE (FILES) option. If you would
like this info, let me know, and I'll post it. Good luck.

jj
=============== ============
Mark S wrote:

I have a db maintenance plan which is set to backup (then truncate
hopefully) the transaction log. In order to backup a transaction log the
db must be in single user mode so the maint. plan fails. How do you
automatically set the db, in single user mode, for the transaction log
to be backed up then truncated?

Also, I manually set the db in single user mode and it shows itself to
be in single user mode yet the number of users in the db properties says
there are 8 users connected. What's this about?

Thanks

Mark

[grooverider]

<quote> "So, I use a technique, taught to me by a nice person on this

newsgroup, that WILL shrink the log file (in actual size),

using EM's SHRINK DATABASE (FILES) option. If you would

like this info, let me know, and I'll post it. Good luck.

</quote>

Jj,

I would like to know more about your "shrinking technique". I had the
same problem (simple/full recovery mode), but thanks to you I did have
to search for long. Today, I'm going to try your solution concerning
the single user mode-error. After that, I will check out the size of
that log to decide if srinking is needed.

Grooverider
--
Posted via http://dbforums.com

[John Bell]

Hi

You may want to check out

http://msdn.microsoft.com/library/de...r_da2_1uzr.asp

John

"grooveride r" <me*********@db forums.com> wrote in message
news:33******** ********@dbforu ms.com...

<quote> "So, I use a technique, taught to me by a nice person on this

newsgroup, that WILL shrink the log file (in actual size),

using EM's SHRINK DATABASE (FILES) option. If you would

like this info, let me know, and I'll post it. Good luck.

</quote>

Jj,

I would like to know more about your "shrinking technique". I had the
same problem (simple/full recovery mode), but thanks to you I did have
to search for long. Today, I'm going to try your solution concerning
the single user mode-error. After that, I will check out the size of
that log to decide if srinking is needed.

Grooverider
--
Posted via http://dbforums.com