A weird maintenance problem.

I have a small database that is causing my maintenance plans to hang. There
are no errors logged and it does not eventually quit, it just hangs. If I
rename the database, it works fine, but then fails again when named back. I
dropped the db and looked in Master and msdb, but found no references to the
database name (at least in the few sys tables I looked in). Any ideas?
Send more info about the problem. If the database fails only with a
specific name, what this name is? Which version of SQL Server? Which
activities are you doing in your maintenance plan? Check on your log to see
which activity and database is hanging (log shows messages like dbcc
checkdb(db), Database backed up: Database: db, etc).
Ben Nevarez, MCDBA, OCP
Database Administrator
"Shawn" wrote:

> I have a small database that is causing my maintenance plans to hang. There
> are no errors logged and it does not eventually quit, it just hangs. If I
> rename the database, it works fine, but then fails again when named back. I
> dropped the db and looked in Master and msdb, but found no references to the
> database name (at least in the few sys tables I looked in). Any ideas?
>
>
|||Thanks, but I think I got it. I rebuilt the database and then had to restart
SQL to clear something (I don't know what) out of memory. Now it works fine.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:9686B3F0-4AD7-4A46-A332-5717AB97EE07@.microsoft.com...[vbcol=seagreen]
> Send more info about the problem. If the database fails only with a
> specific name, what this name is? Which version of SQL Server? Which
> activities are you doing in your maintenance plan? Check on your log to
> see
> which activity and database is hanging (log shows messages like dbcc
> checkdb(db), Database backed up: Database: db, etc).
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Shawn" wrote:

A weird maintenance problem.

I have a small database that is causing my maintenance plans to hang. There
are no errors logged and it does not eventually quit, it just hangs. If I
rename the database, it works fine, but then fails again when named back. I
dropped the db and looked in Master and msdb, but found no references to the
database name (at least in the few sys tables I looked in). Any ideas?Send more info about the problem. If the database fails only with a
specific name, what this name is? Which version of SQL Server? Which
activities are you doing in your maintenance plan? Check on your log to see
which activity and database is hanging (log shows messages like dbcc
checkdb(db), Database backed up: Database: db, etc).
Ben Nevarez, MCDBA, OCP
Database Administrator
"Shawn" wrote:

> I have a small database that is causing my maintenance plans to hang. Ther
e
> are no errors logged and it does not eventually quit, it just hangs. If I
> rename the database, it works fine, but then fails again when named back.
I
> dropped the db and looked in Master and msdb, but found no references to t
he
> database name (at least in the few sys tables I looked in). Any ideas?
>
>|||Thanks, but I think I got it. I rebuilt the database and then had to restart
SQL to clear something (I don't know what) out of memory. Now it works fine.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:9686B3F0-4AD7-4A46-A332-5717AB97EE07@.microsoft.com...[vbcol=seagreen]
> Send more info about the problem. If the database fails only with a
> specific name, what this name is? Which version of SQL Server? Which
> activities are you doing in your maintenance plan? Check on your log to
> see
> which activity and database is hanging (log shows messages like dbcc
> checkdb(db), Database backed up: Database: db, etc).
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Shawn" wrote:
>

A weird maintenance problem.

I have a small database that is causing my maintenance plans to hang. There
are no errors logged and it does not eventually quit, it just hangs. If I
rename the database, it works fine, but then fails again when named back. I
dropped the db and looked in Master and msdb, but found no references to the
database name (at least in the few sys tables I looked in). Any ideas?Send more info about the problem. If the database fails only with a
specific name, what this name is? Which version of SQL Server? Which
activities are you doing in your maintenance plan? Check on your log to see
which activity and database is hanging (log shows messages like dbcc
checkdb(db), Database backed up: Database: db, etc).
Ben Nevarez, MCDBA, OCP
Database Administrator
"Shawn" wrote:
> I have a small database that is causing my maintenance plans to hang. There
> are no errors logged and it does not eventually quit, it just hangs. If I
> rename the database, it works fine, but then fails again when named back. I
> dropped the db and looked in Master and msdb, but found no references to the
> database name (at least in the few sys tables I looked in). Any ideas?
>
>|||Thanks, but I think I got it. I rebuilt the database and then had to restart
SQL to clear something (I don't know what) out of memory. Now it works fine.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:9686B3F0-4AD7-4A46-A332-5717AB97EE07@.microsoft.com...
> Send more info about the problem. If the database fails only with a
> specific name, what this name is? Which version of SQL Server? Which
> activities are you doing in your maintenance plan? Check on your log to
> see
> which activity and database is hanging (log shows messages like dbcc
> checkdb(db), Database backed up: Database: db, etc).
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Shawn" wrote:
>> I have a small database that is causing my maintenance plans to hang.
>> There
>> are no errors logged and it does not eventually quit, it just hangs. If I
>> rename the database, it works fine, but then fails again when named back.
>> I
>> dropped the db and looked in Master and msdb, but found no references to
>> the
>> database name (at least in the few sys tables I looked in). Any ideas?
>>sql

A question on when to use bulk logged recovery mode

Hi all,
I was hoping that someone could tell me a couple of scenarios where the
bulk logged recovery mode is used and perhaps why?
I'm guessing you might use it just before doing a large data import, but
does that mean you might switch to it before the import and switch back
to full recovery once it's done? Is that a good idea?
What other instances might it be used? Perhaps in a reporting database
for some reason?
Many thanks to anyone who can advise
Kindest Regards
Simon
Simon
> I'm guessing you might use it just before doing a large data import, but
> does that mean you might switch to it before the import and switch back to
> full recovery once it's done? Is that a good idea?
BOL says
In Microsoft SQL ServerT 2000, you can switch between full and bulk-logged
recovery models easily. It is not necessary to perform a full database
backup after bulk copy operations complete under the Bulk-Logged Recovery
model. Transaction log backups under this model capture both the log and the
results of any bulk operations performed since the last backup.

> I was hoping that someone could tell me a couple of scenarios where the
> bulk logged recovery mode is used and perhaps why?
There are some operatuions like SELECT INTO..,CREATE INDEX under BULK
logged recovery mode are minimally loggged.
A disadvantage is that you cannot restore LOG file at point of time
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:uz%23a2I2HHHA.1264@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I was hoping that someone could tell me a couple of scenarios where the
> bulk logged recovery mode is used and perhaps why?
> I'm guessing you might use it just before doing a large data import, but
> does that mean you might switch to it before the import and switch back to
> full recovery once it's done? Is that a good idea?
> What other instances might it be used? Perhaps in a reporting database for
> some reason?
> Many thanks to anyone who can advise
> Kindest Regards
> Simon

A question on when to use bulk logged recovery mode

Hi all,
I was hoping that someone could tell me a couple of scenarios where the
bulk logged recovery mode is used and perhaps why?
I'm guessing you might use it just before doing a large data import, but
does that mean you might switch to it before the import and switch back
to full recovery once it's done? Is that a good idea?
What other instances might it be used? Perhaps in a reporting database
for some reason?
Many thanks to anyone who can advise
Kindest Regards
SimonSimon
> I'm guessing you might use it just before doing a large data import, but
> does that mean you might switch to it before the import and switch back to
> full recovery once it's done? Is that a good idea?
BOL says
In Microsoft® SQL ServerT 2000, you can switch between full and bulk-logged
recovery models easily. It is not necessary to perform a full database
backup after bulk copy operations complete under the Bulk-Logged Recovery
model. Transaction log backups under this model capture both the log and the
results of any bulk operations performed since the last backup.
> I was hoping that someone could tell me a couple of scenarios where the
> bulk logged recovery mode is used and perhaps why?
There are some operatuions like SELECT INTO..,CREATE INDEX under BULK
logged recovery mode are minimally loggged.
A disadvantage is that you cannot restore LOG file at point of time
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:uz%23a2I2HHHA.1264@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I was hoping that someone could tell me a couple of scenarios where the
> bulk logged recovery mode is used and perhaps why?
> I'm guessing you might use it just before doing a large data import, but
> does that mean you might switch to it before the import and switch back to
> full recovery once it's done? Is that a good idea?
> What other instances might it be used? Perhaps in a reporting database for
> some reason?
> Many thanks to anyone who can advise
> Kindest Regards
> Simon|||> I'm guessing you might use it just before doing a large data import, but does that mean you might
> switch to it before the import and switch back to full recovery once it's done? Is that a good
> idea?
Yes, this brings you the advantage of being in contold of when log backups which includes both log
recards and datapages can occur. Run in full normally, swith down the bulk logged only for this
batch and then up to full again. You are now protected against someone doing SELECT INTO at 3:15 pm
resuting in not being able to do point in time restore for the following log backup (just as an
example).
My take on this is that I weigh what bulk logged will atcually contribute. Will the operation be
faster (compared to full) to warrant bulk logged? And/or the fact that ldf file is smaller, is it
worth it? this need to be compared to the disadvantages (no point in time restore for such log
backup, and not being able to do log backup if for instance mdf file is gone). Also, you need to
think about how larger the following log backup is compared to run in full mode (either fewer log
records but also data pages, or only log records, but more of them).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:uz%23a2I2HHHA.1264@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I was hoping that someone could tell me a couple of scenarios where the bulk logged recovery mode
> is used and perhaps why?
> I'm guessing you might use it just before doing a large data import, but does that mean you might
> switch to it before the import and switch back to full recovery once it's done? Is that a good
> idea?
> What other instances might it be used? Perhaps in a reporting database for some reason?
> Many thanks to anyone who can advise
> Kindest Regards
> Simon

A question on when to use bulk logged recovery mode

Hi all,
I was hoping that someone could tell me a couple of scenarios where the
bulk logged recovery mode is used and perhaps why?
I'm guessing you might use it just before doing a large data import, but
does that mean you might switch to it before the import and switch back
to full recovery once it's done? Is that a good idea?
What other instances might it be used? Perhaps in a reporting database
for some reason?
Many thanks to anyone who can advise
Kindest Regards
SimonSimon
> I'm guessing you might use it just before doing a large data import, but
> does that mean you might switch to it before the import and switch back to
> full recovery once it's done? Is that a good idea?
BOL says
In Microsoft SQL ServerT 2000, you can switch between full and bulk-logged
recovery models easily. It is not necessary to perform a full database
backup after bulk copy operations complete under the Bulk-Logged Recovery
model. Transaction log backups under this model capture both the log and the
results of any bulk operations performed since the last backup.

> I was hoping that someone could tell me a couple of scenarios where the
> bulk logged recovery mode is used and perhaps why?
There are some operatuions like SELECT INTO..,CREATE INDEX under BULK
logged recovery mode are minimally loggged.
A disadvantage is that you cannot restore LOG file at point of time
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:uz%23a2I2HHHA.1264@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I was hoping that someone could tell me a couple of scenarios where the
> bulk logged recovery mode is used and perhaps why?
> I'm guessing you might use it just before doing a large data import, but
> does that mean you might switch to it before the import and switch back to
> full recovery once it's done? Is that a good idea?
> What other instances might it be used? Perhaps in a reporting database for
> some reason?
> Many thanks to anyone who can advise
> Kindest Regards
> Simon|||> I'm guessing you might use it just before doing a large data import, but does that mean yo
u might
> switch to it before the import and switch back to full recovery once it's
done? Is that a good
> idea?
Yes, this brings you the advantage of being in contold of when log backups w
hich includes both log
recards and datapages can occur. Run in full normally, swith down the bulk l
ogged only for this
batch and then up to full again. You are now protected against someone doing
SELECT INTO at 3:15 pm
resuting in not being able to do point in time restore for the following log
backup (just as an
example).
My take on this is that I weigh what bulk logged will atcually contribute. W
ill the operation be
faster (compared to full) to warrant bulk logged? And/or the fact that ldf f
ile is smaller, is it
worth it? this need to be compared to the disadvantages (no point in time re
store for such log
backup, and not being able to do log backup if for instance mdf file is gone
). Also, you need to
think about how larger the following log backup is compared to run in full m
ode (either fewer log
records but also data pages, or only log records, but more of them).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:uz%23a2I2HHHA.1264@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I was hoping that someone could tell me a couple of scenarios where the bu
lk logged recovery mode
> is used and perhaps why?
> I'm guessing you might use it just before doing a large data import, but d
oes that mean you might
> switch to it before the import and switch back to full recovery once it's
done? Is that a good
> idea?
> What other instances might it be used? Perhaps in a reporting database for
some reason?
> Many thanks to anyone who can advise
> Kindest Regards
> Simon