Showing posts with label disk. Show all posts
Showing posts with label disk. Show all posts

Tuesday, March 27, 2012

Can't backup LOG - serious problem

Hello,
I can't backup log. I tray normal backup log to disk, backup with
no_log (clean log without backup). I know where is problem but don't
know how resolve it. My problem cause open transaction.
DBCC OPENTRAN:
Transaction information for database 'db_name'.
Replicated Transaction Information:
Oldest distributed LSN : (68123:350:1)
Oldest non-distributed LSN : (73169:188:1)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
sys.databases (for this db):
log_reuse_wait
--
6
log_reuse_wait_desc
--
REPLICATION
I don't have setup replication, it is disabled, no publication and no
subscription. Maybe in past was.
DBCC LOGINFO return 72000 rows with status 2 .
2 253952 8192 107212 2 128 0
2 253952 262144 107213 2 128 0
2 253952 516096 107214 2 64 0
2 278528 770048 107215 2 64 0
2 253952 1048576 107216 2 128 67569000000013600332
2 253952 1302528 107217 2 128 67569000000013600332
72000 similar rows
2 253952 18823045120 144612 2 64
144608000000025500065
2 253952 18823299072 144613 2 64
144608000000025500065
2 286720 18823553024 144614 2 64
144608000000025500065
2 253952 18823839744 0 0 0 144611000000025300320
2 253952 18824093696 0 0 0 144611000000025300320
2 253952 18824347648 0 0 0 144611000000025300320
2 286720 18824601600 0 0 0 144611000000025300320
Tell me please how backup this log and shrink file
Thank you
--
Regards,
anxcompYou didn't really tell us what the problem with a TLog backup is (no error
message).
>> Tell me please how backup this log and shrink file
In an emergency, use:
Backup log dbName with truncate_only
dbcc shrinkfile('TLog fileName')
<anxcomp@.gmail.com> wrote in message
news:1191065040.370537.182100@.19g2000hsx.googlegroups.com...
> Hello,
> I can't backup log. I tray normal backup log to disk, backup with
> no_log (clean log without backup). I know where is problem but don't
> know how resolve it. My problem cause open transaction.
> DBCC OPENTRAN:
> Transaction information for database 'db_name'.
> Replicated Transaction Information:
> Oldest distributed LSN : (68123:350:1)
> Oldest non-distributed LSN : (73169:188:1)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> sys.databases (for this db):
> log_reuse_wait
> --
> 6
> log_reuse_wait_desc
> --
> REPLICATION
>
> I don't have setup replication, it is disabled, no publication and no
> subscription. Maybe in past was.
> DBCC LOGINFO return 72000 rows with status 2 .
> 2 253952 8192 107212 2 128 0
> 2 253952 262144 107213 2 128 0
> 2 253952 516096 107214 2 64 0
> 2 278528 770048 107215 2 64 0
> 2 253952 1048576 107216 2 128 67569000000013600332
> 2 253952 1302528 107217 2 128 67569000000013600332
> 72000 similar rows
> 2 253952 18823045120 144612 2 64
> 144608000000025500065
> 2 253952 18823299072 144613 2 64
> 144608000000025500065
> 2 286720 18823553024 144614 2 64
> 144608000000025500065
> 2 253952 18823839744 0 0 0 144611000000025300320
> 2 253952 18824093696 0 0 0 144611000000025300320
> 2 253952 18824347648 0 0 0 144611000000025300320
> 2 286720 18824601600 0 0 0 144611000000025300320
>
> Tell me please how backup this log and shrink file
> Thank you
> --
> Regards,
> anxcomp
>|||Chris,
The problem seems to be that SQL Server believes that the database is publisher for transactional
replication (according to output from DBCC OPENTRAN) and it counts the oldest log record that hasn't
been read by the "Log Reader" replications process as an open transaction. I.e., using TRUNCATE_ONLY
or NO_LOG won't help. The solution is to make SQL Server understand that there's no replication
going on. Possibly sp_repldone, but I'm no replication expert, so anxcomp might be best server to
ask in a replication group.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"ChrisR" <ChrisR@.foo.com> wrote in message news:%231b$8%23qAIHA.4836@.TK2MSFTNGP06.phx.gbl...
> You didn't really tell us what the problem with a TLog backup is (no error message).
>> Tell me please how backup this log and shrink file
> In an emergency, use:
> Backup log dbName with truncate_only
> dbcc shrinkfile('TLog fileName')
>
> <anxcomp@.gmail.com> wrote in message news:1191065040.370537.182100@.19g2000hsx.googlegroups.com...
>> Hello,
>> I can't backup log. I tray normal backup log to disk, backup with
>> no_log (clean log without backup). I know where is problem but don't
>> know how resolve it. My problem cause open transaction.
>> DBCC OPENTRAN:
>> Transaction information for database 'db_name'.
>> Replicated Transaction Information:
>> Oldest distributed LSN : (68123:350:1)
>> Oldest non-distributed LSN : (73169:188:1)
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator.
>>
>> sys.databases (for this db):
>> log_reuse_wait
>> --
>> 6
>> log_reuse_wait_desc
>> --
>> REPLICATION
>>
>> I don't have setup replication, it is disabled, no publication and no
>> subscription. Maybe in past was.
>> DBCC LOGINFO return 72000 rows with status 2 .
>> 2 253952 8192 107212 2 128 0
>> 2 253952 262144 107213 2 128 0
>> 2 253952 516096 107214 2 64 0
>> 2 278528 770048 107215 2 64 0
>> 2 253952 1048576 107216 2 128 67569000000013600332
>> 2 253952 1302528 107217 2 128 67569000000013600332
>> 72000 similar rows
>> 2 253952 18823045120 144612 2 64
>> 144608000000025500065
>> 2 253952 18823299072 144613 2 64
>> 144608000000025500065
>> 2 286720 18823553024 144614 2 64
>> 144608000000025500065
>> 2 253952 18823839744 0 0 0 144611000000025300320
>> 2 253952 18824093696 0 0 0 144611000000025300320
>> 2 253952 18824347648 0 0 0 144611000000025300320
>> 2 286720 18824601600 0 0 0 144611000000025300320
>>
>> Tell me please how backup this log and shrink file
>> Thank you
>> --
>> Regards,
>> anxcomp
>|||TRUNCATE_ONLY or NO_LOG doesn't return any error, but it doesn't clean
no active transaction, so I can't shrink file. Log file is full
Virtual Log Files with status 2 and this mean I can't shrink file :(
All right I'll tray send this message to
microsoft.public.sqlserver.replication
Thanks
--
Regards,
anxcomp|||You might also want to Google for below. I got a number of hits, some sounded promising:
sp_repldone "log full"
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<anxcomp@.gmail.com> wrote in message news:1191091375.237234.86020@.d55g2000hsg.googlegroups.com...
> TRUNCATE_ONLY or NO_LOG doesn't return any error, but it doesn't clean
> no active transaction, so I can't shrink file. Log file is full
> Virtual Log Files with status 2 and this mean I can't shrink file :(
> All right I'll tray send this message to
> microsoft.public.sqlserver.replication
> Thanks
> --
> Regards,
> anxcomp
>|||My bad, sorry. We recently had a situation where we couldn't do something
because the DB thought it was being replicated. My employee had to update
something in the sysObjects table if Im not mistaken (I'd look right now my
my wife is loading up the kids to head out).
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:43A7C6FA-C546-49F4-9B6B-6FD9EE4928E5@.microsoft.com...
> Chris,
> The problem seems to be that SQL Server believes that the database is
> publisher for transactional replication (according to output from DBCC
> OPENTRAN) and it counts the oldest log record that hasn't been read by the
> "Log Reader" replications process as an open transaction. I.e., using
> TRUNCATE_ONLY or NO_LOG won't help. The solution is to make SQL Server
> understand that there's no replication going on. Possibly sp_repldone, but
> I'm no replication expert, so anxcomp might be best server to ask in a
> replication group.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "ChrisR" <ChrisR@.foo.com> wrote in message
> news:%231b$8%23qAIHA.4836@.TK2MSFTNGP06.phx.gbl...
>> You didn't really tell us what the problem with a TLog backup is (no
>> error message).
>> Tell me please how backup this log and shrink file
>> In an emergency, use:
>> Backup log dbName with truncate_only
>> dbcc shrinkfile('TLog fileName')
>>
>> <anxcomp@.gmail.com> wrote in message
>> news:1191065040.370537.182100@.19g2000hsx.googlegroups.com...
>> Hello,
>> I can't backup log. I tray normal backup log to disk, backup with
>> no_log (clean log without backup). I know where is problem but don't
>> know how resolve it. My problem cause open transaction.
>> DBCC OPENTRAN:
>> Transaction information for database 'db_name'.
>> Replicated Transaction Information:
>> Oldest distributed LSN : (68123:350:1)
>> Oldest non-distributed LSN : (73169:188:1)
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator.
>>
>> sys.databases (for this db):
>> log_reuse_wait
>> --
>> 6
>> log_reuse_wait_desc
>> --
>> REPLICATION
>>
>> I don't have setup replication, it is disabled, no publication and no
>> subscription. Maybe in past was.
>> DBCC LOGINFO return 72000 rows with status 2 .
>> 2 253952 8192 107212 2 128 0
>> 2 253952 262144 107213 2 128 0
>> 2 253952 516096 107214 2 64 0
>> 2 278528 770048 107215 2 64 0
>> 2 253952 1048576 107216 2 128 67569000000013600332
>> 2 253952 1302528 107217 2 128 67569000000013600332
>> 72000 similar rows
>> 2 253952 18823045120 144612 2 64
>> 144608000000025500065
>> 2 253952 18823299072 144613 2 64
>> 144608000000025500065
>> 2 286720 18823553024 144614 2 64
>> 144608000000025500065
>> 2 253952 18823839744 0 0 0 144611000000025300320
>> 2 253952 18824093696 0 0 0 144611000000025300320
>> 2 253952 18824347648 0 0 0 144611000000025300320
>> 2 286720 18824601600 0 0 0 144611000000025300320
>>
>> Tell me please how backup this log and shrink file
>> Thank you
>> --
>> Regards,
>> anxcomp
>>
>|||Hello,
Thank you Tibor. I got answer at replication group.
Bellow procedure solved my problem
EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0,
@.time
= 0, @.reset = 1
Your advice with Google always was good :)
Thanks
--
Regards,
anxcomp

Friday, February 24, 2012

Cannot shrink Transaction Log

Hello. I've got a situation where a live database has stopped working because
the transaction logs have filled all available disk space. I would really
appreciate any advice on how to get it running again. My knowledge of SQL
Server 2000 is very limited so I would need so quite detailed instructions! I
am happy with entering statements into Query Analyzer.
I've tried to shrink the transaction log by running a backup on it in
Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
enough disk space to back this up.
So somehow I need to shrink these .LDF transaction log files. Also, if
anyone could advise on how to stop this situation happening again, that'd be
fantastic. If anyone can give advice on how to do this, you really would be
saving my life!
1. Consider the recovery model for the database. If not "simple" then SQL Server will not empty the
log files automatically, and the log will keep growing. When not "simple", only backup of the
transaction log will empty the log files.
2. After either setting to simple or scheduling regular transaction log backups, verify that the log
files are more or less empty. Use:
DBCC SQLPERF(LOGSPACE)
3. Now time for the shrink. Use DBCC SHRINKFILE, (not SHRINKDATABASE), as documented in Books
Online.
Also, there are some links and general info about shrink on
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Fluffy_Ninja" <FluffyNinja@.discussions.microsoft.com> wrote in message
news:C338EACF-9E37-47B3-B307-D9A15A87539C@.microsoft.com...
> Hello. I've got a situation where a live database has stopped working because
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd be
> fantastic. If anyone can give advice on how to do this, you really would be
> saving my life!
|||Hi,
What is your recovery model? Full, Simple ?
If you are using Full recovery model then you need to backup the transaction
log regularly.
If you don't need to set recovery model to Full then use Simple Recovery
Model you can find detailed information about it from Books Online.
Check out BACKUP LOG WITH TRUNCATE_ONLY (if it's acceptable for you..)
FROM BOL:
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are
synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Fluffy_Ninja" wrote:

> Hello. I've got a situation where a live database has stopped working because
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd be
> fantastic. If anyone can give advice on how to do this, you really would be
> saving my life!

Cannot shrink Transaction Log

Hello. I've got a situation where a live database has stopped working becaus
e
the transaction logs have filled all available disk space. I would really
appreciate any advice on how to get it running again. My knowledge of SQL
Server 2000 is very limited so I would need so quite detailed instructions!
I
am happy with entering statements into Query Analyzer.
I've tried to shrink the transaction log by running a backup on it in
Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
enough disk space to back this up.
So somehow I need to shrink these .LDF transaction log files. Also, if
anyone could advise on how to stop this situation happening again, that'd be
fantastic. If anyone can give advice on how to do this, you really would be
saving my life!1. Consider the recovery model for the database. If not "simple" then SQL Se
rver will not empty the
log files automatically, and the log will keep growing. When not "simple", o
nly backup of the
transaction log will empty the log files.
2. After either setting to simple or scheduling regular transaction log back
ups, verify that the log
files are more or less empty. Use:
DBCC SQLPERF(LOGSPACE)
3. Now time for the shrink. Use DBCC SHRINKFILE, (not SHRINKDATABASE), as do
cumented in Books
Online.
Also, there are some links and general info about shrink on
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Fluffy_Ninja" <FluffyNinja@.discussions.microsoft.com> wrote in message
news:C338EACF-9E37-47B3-B307-D9A15A87539C@.microsoft.com...
> Hello. I've got a situation where a live database has stopped working beca
use
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions
! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is no
t
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd
be
> fantastic. If anyone can give advice on how to do this, you really would b
e
> saving my life!|||Hi,
What is your recovery model? Full, Simple ?
If you are using Full recovery model then you need to backup the transaction
log regularly.
If you don't need to set recovery model to Full then use Simple Recovery
Model you can find detailed information about it from Books Online.
Check out BACKUP LOG WITH TRUNCATE_ONLY (if it's acceptable for you..)
FROM BOL:
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY ar
e
synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Fluffy_Ninja" wrote:

> Hello. I've got a situation where a live database has stopped working beca
use
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions
! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is no
t
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd
be
> fantastic. If anyone can give advice on how to do this, you really would b
e
> saving my life!

Cannot shrink Transaction Log

Hello. I've got a situation where a live database has stopped working because
the transaction logs have filled all available disk space. I would really
appreciate any advice on how to get it running again. My knowledge of SQL
Server 2000 is very limited so I would need so quite detailed instructions! I
am happy with entering statements into Query Analyzer.
I've tried to shrink the transaction log by running a backup on it in
Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
enough disk space to back this up.
So somehow I need to shrink these .LDF transaction log files. Also, if
anyone could advise on how to stop this situation happening again, that'd be
fantastic. If anyone can give advice on how to do this, you really would be
saving my life!1. Consider the recovery model for the database. If not "simple" then SQL Server will not empty the
log files automatically, and the log will keep growing. When not "simple", only backup of the
transaction log will empty the log files.
2. After either setting to simple or scheduling regular transaction log backups, verify that the log
files are more or less empty. Use:
DBCC SQLPERF(LOGSPACE)
3. Now time for the shrink. Use DBCC SHRINKFILE, (not SHRINKDATABASE), as documented in Books
Online.
Also, there are some links and general info about shrink on
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Fluffy_Ninja" <FluffyNinja@.discussions.microsoft.com> wrote in message
news:C338EACF-9E37-47B3-B307-D9A15A87539C@.microsoft.com...
> Hello. I've got a situation where a live database has stopped working because
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd be
> fantastic. If anyone can give advice on how to do this, you really would be
> saving my life!|||Hi,
What is your recovery model? Full, Simple ?
If you are using Full recovery model then you need to backup the transaction
log regularly.
If you don't need to set recovery model to Full then use Simple Recovery
Model you can find detailed information about it from Books Online.
Check out BACKUP LOG WITH TRUNCATE_ONLY (if it's acceptable for you..)
FROM BOL:
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are
synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Fluffy_Ninja" wrote:
> Hello. I've got a situation where a live database has stopped working because
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd be
> fantastic. If anyone can give advice on how to do this, you really would be
> saving my life!

Sunday, February 19, 2012

Cannot setup SQL Server 2000 on W2003 Server

I have installed Windows Server 2003 Web Edition from scratch on a new system disk. I have updated with the latest service pack and security upgrades.

When I try to install SQL Server 2000 Standard Edition, the initial setup dialog Autoruns OK. I click on "SQL Server 2000 components", then on "Install Database Server". For a second or so a "Demoshield" pop-up appears, but that vanishes and nothing else happens.

Can anyone suggest what might be wrong? I am running on P4 1.7Ghz but only have 128Meg memory until the new chips arrive. I have around 15Gig disk space free on the drive I want to install on.

Thanks

Looks like this is not a supported configuration.

http://support.microsoft.com/default.aspx?scid=kb;en-us;819258

Tuesday, February 14, 2012

Cannot See SAN Disk Drives

I've installed sql server 2005active/passive cluster on Win 2003 Ent Edition
boxes connected to 3 SAN drives L, M, S.
When setup asked me where to install datafiles i choose the S drive.
SP1 installed, everything looks good, failover succesfully both ways.
However in SQL Server Managment Studio i don't see L and M drives.
In cluster admin under resources I see all 3 drives which are owned by Sql
Server 2005 group.
But when i try to detach and move the tran log from S to M i don't see M.
When i go to attach db i only see the S drive.
How can i add the L and M drives to be visible to Sql Server?
Thank you
Stop the SQL Server service, add the M & L drives as dependencies to the
resource. Restart everything and use away!
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Alex S" <alexsdba@.aol.com> wrote in message
news:OlJ46FZ6GHA.3288@.TK2MSFTNGP02.phx.gbl...
> I've installed sql server 2005active/passive cluster on Win 2003 Ent
> Edition boxes connected to 3 SAN drives L, M, S.
> When setup asked me where to install datafiles i choose the S drive.
> SP1 installed, everything looks good, failover succesfully both ways.
> However in SQL Server Managment Studio i don't see L and M drives.
> In cluster admin under resources I see all 3 drives which are owned by Sql
> Server 2005 group.
> But when i try to detach and move the tran log from S to M i don't see M.
> When i go to attach db i only see the S drive.
>
> How can i add the L and M drives to be visible to Sql Server?
>
> Thank you
>
>
|||Most likely your SQL Server resource does not depend on the drive L and M
disk resources. In Cluster Admin, bring the SQL Server resource offline, and
add those two disk resources as its dependencies. Bring the SQL Server
resource online, you should be ready to go.
Linchi
"Alex S" wrote:

> I've installed sql server 2005active/passive cluster on Win 2003 Ent Edition
> boxes connected to 3 SAN drives L, M, S.
> When setup asked me where to install datafiles i choose the S drive.
> SP1 installed, everything looks good, failover succesfully both ways.
> However in SQL Server Managment Studio i don't see L and M drives.
> In cluster admin under resources I see all 3 drives which are owned by Sql
> Server 2005 group.
> But when i try to detach and move the tran log from S to M i don't see M.
> When i go to attach db i only see the S drive.
>
> How can i add the L and M drives to be visible to Sql Server?
>
> Thank you
>
>
|||IT WORKED.
THANK YOU
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:%23JKiWNZ6GHA.4608@.TK2MSFTNGP05.phx.gbl...
> Stop the SQL Server service, add the M & L drives as dependencies to the
> resource. Restart everything and use away!
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://www.msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Alex S" <alexsdba@.aol.com> wrote in message
> news:OlJ46FZ6GHA.3288@.TK2MSFTNGP02.phx.gbl...
>
|||IT WORKED.
THANK YOU
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:2E663D4D-3A0E-4F09-81D1-F348DA2F15AF@.microsoft.com...[vbcol=seagreen]
> Most likely your SQL Server resource does not depend on the drive L and M
> disk resources. In Cluster Admin, bring the SQL Server resource offline,
> and
> add those two disk resources as its dependencies. Bring the SQL Server
> resource online, you should be ready to go.
> Linchi
> "Alex S" wrote:

Cannot see network drive when doing restore from disk

Hello, I recently had the PC replaced where I ran SQL Server 2005.

On the old PC I was able to backup and restore DB on a network drive L: w/o any trouble. On the new PC I reinstalled SQL Server making sure that all the settings are exactly as before. However, when I tried to restore the database, I go to "From device" -> Add file, but I do not see L: drive on the list anymore.

However, I seem to have an access to the network in general, as I can open and run sql queries that are stored on the same network drive.

I am spending the whole day comparing 2 machine setting by setting, and cannot find anything different except that the old system was running Win2000, and new one is WinXP, but I doubt that it has anything to do with it.

I also did not see any similar problem reported elsewhere.

Does anyone have any idea what to look for?

Make sure you have the drive mapped correctly on your system.

you may need to copy and paste the UNC location.

I'm not sure how it works on a local machine, but you may want to make sure the login running the SQL service has access to that network drive as well.

It would probably work better for you to just copy a backup file down from that drive to a local drive and restore from there.

Cannot See Disk Drives

Cannot See Disk DrivesI can't see your post ;) Can you send the body again!
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Alex S" <alexsdba@.aol.com> wrote in message
news:OIWWvAZ6GHA.348@.TK2MSFTNGP02.phx.gbl...
>
|||You must be using the same glasses you used to write this post. Nothing
here.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Alex S" <alexsdba@.aol.com> wrote in message
news:OIWWvAZ6GHA.348@.TK2MSFTNGP02.phx.gbl...
>

Sunday, February 12, 2012

Cannot restore from a UNC file in sql 2005 management studio

Hi,
In sql 2005 management studio, when I tried to restore a database from disk
file saved on a UNC path, I got the following error.
The Database Engine service could not resolve the specified file location.
Either the location does not exist, or the current login account does not
have access to it. Verify that the file location exists, and that the login
account has permissions on it.
The following are the options I selected:
Source for restore: From device
Backup media: File
Selected path: \\home.dept.some.edu\me\sql2005\backups\
Files of type: Backup Files (*.bak, *.trn)
File name: db.bak
The error showed up when I clicked OK to start the restore process.
In Start->Run, the UNC path opens fine. So it's not the permissions issue.
And the path exists. Do I have to restore using the 'restore' command
instead of using the management studio GUI interface if I want to restore
from UNC?
Thanks in advance,
BingThe fully qualified domain name might be what is tripping it up - have you
tried specifying the IP address or using a non-qualified name. This is
assuming, of course, that this server is on the same domain that you're
trying to restore to...
"bing" wrote:
> Hi,
> In sql 2005 management studio, when I tried to restore a database from disk
> file saved on a UNC path, I got the following error.
> The Database Engine service could not resolve the specified file location.
> Either the location does not exist, or the current login account does not
> have access to it. Verify that the file location exists, and that the login
> account has permissions on it.
> The following are the options I selected:
> Source for restore: From device
> Backup media: File
> Selected path: \\home.dept.some.edu\me\sql2005\backups\
> Files of type: Backup Files (*.bak, *.trn)
> File name: db.bak
> The error showed up when I clicked OK to start the restore process.
> In Start->Run, the UNC path opens fine. So it's not the permissions issue.
> And the path exists. Do I have to restore using the 'restore' command
> instead of using the management studio GUI interface if I want to restore
> from UNC?
> Thanks in advance,
> Bing|||Thanks for the response.
I tried the IP address, the same error.
The netwrok share and the sql 2005 server are not in the same domain.
Bing
"James Luetkehoelter" wrote:
> The fully qualified domain name might be what is tripping it up - have you
> tried specifying the IP address or using a non-qualified name. This is
> assuming, of course, that this server is on the same domain that you're
> trying to restore to...
> "bing" wrote:
> > Hi,
> >
> > In sql 2005 management studio, when I tried to restore a database from disk
> > file saved on a UNC path, I got the following error.
> >
> > The Database Engine service could not resolve the specified file location.
> > Either the location does not exist, or the current login account does not
> > have access to it. Verify that the file location exists, and that the login
> > account has permissions on it.
> >
> > The following are the options I selected:
> >
> > Source for restore: From device
> > Backup media: File
> > Selected path: \\home.dept.some.edu\me\sql2005\backups\
> > Files of type: Backup Files (*.bak, *.trn)
> > File name: db.bak
> >
> > The error showed up when I clicked OK to start the restore process.
> >
> > In Start->Run, the UNC path opens fine. So it's not the permissions issue.
> > And the path exists. Do I have to restore using the 'restore' command
> > instead of using the management studio GUI interface if I want to restore
> > from UNC?
> >
> > Thanks in advance,
> >
> > Bing|||"bing" <bing@.discussions.microsoft.com> wrote in message
news:18DF4B33-FFB8-49B7-9526-E6AB310FDB34@.microsoft.com...
> Thanks for the response.
> I tried the IP address, the same error.
> The netwrok share and the sql 2005 server are not in the same domain.
>
Actually this probably IS a permissions issue.
The user context that the SQL is operating in is most likely not the same as
the one you're logged into to run SMS.
> Bing
> "James Luetkehoelter" wrote:
>> The fully qualified domain name might be what is tripping it up - have
>> you
>> tried specifying the IP address or using a non-qualified name. This is
>> assuming, of course, that this server is on the same domain that you're
>> trying to restore to...
>> "bing" wrote:
>> > Hi,
>> >
>> > In sql 2005 management studio, when I tried to restore a database from
>> > disk
>> > file saved on a UNC path, I got the following error.
>> >
>> > The Database Engine service could not resolve the specified file
>> > location.
>> > Either the location does not exist, or the current login account does
>> > not
>> > have access to it. Verify that the file location exists, and that the
>> > login
>> > account has permissions on it.
>> >
>> > The following are the options I selected:
>> >
>> > Source for restore: From device
>> > Backup media: File
>> > Selected path: \\home.dept.some.edu\me\sql2005\backups\
>> > Files of type: Backup Files (*.bak, *.trn)
>> > File name: db.bak
>> >
>> > The error showed up when I clicked OK to start the restore process.
>> >
>> > In Start->Run, the UNC path opens fine. So it's not the permissions
>> > issue.
>> > And the path exists. Do I have to restore using the 'restore' command
>> > instead of using the management studio GUI interface if I want to
>> > restore
>> > from UNC?
>> >
>> > Thanks in advance,
>> >
>> > Bing
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||The SQLServer Service and SQL Agent windows services should always be set up
to login as domain-level accounts with appropriate permissions to access all
system-wide resources they may need access to. This especially includes
network file shares, which is almost certainly the problem here.
--
TheSQLGuru
President
Indicium Resources, Inc.
"bing" <bing@.discussions.microsoft.com> wrote in message
news:C2BD8088-BEE0-4581-9214-5D37F305118C@.microsoft.com...
> Hi,
> In sql 2005 management studio, when I tried to restore a database from
> disk
> file saved on a UNC path, I got the following error.
> The Database Engine service could not resolve the specified file location.
> Either the location does not exist, or the current login account does not
> have access to it. Verify that the file location exists, and that the
> login
> account has permissions on it.
> The following are the options I selected:
> Source for restore: From device
> Backup media: File
> Selected path: \\home.dept.some.edu\me\sql2005\backups\
> Files of type: Backup Files (*.bak, *.trn)
> File name: db.bak
> The error showed up when I clicked OK to start the restore process.
> In Start->Run, the UNC path opens fine. So it's not the permissions
> issue.
> And the path exists. Do I have to restore using the 'restore' command
> instead of using the management studio GUI interface if I want to restore
> from UNC?
> Thanks in advance,
> Bing

Friday, February 10, 2012

Cannot restore database

RESTORE DATABASE upperbridge
FROM DISK = C:\...........
WITH REPLACE
gets the error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not be
obtained because the database is in use.
This is on my test SQL Server 2000 on my intranet with no other users. Both
Enterprise Manager and Query Analyser are not running.
Please advise.
Seems like someone has a connection to the database. Make sure that the connection from where you do the
restore isn't in the database. Check who is in the database using sp_who or sp_who2.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roger Withnell" <rogerREMOVE@.THISFIRSTupperbridge.co.uk> wrote in message
news:c9g2ap$s9f$1@.hercules.btinternet.com...
> RESTORE DATABASE upperbridge
> FROM DISK = C:\...........
> WITH REPLACE
> gets the error
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not be
> obtained because the database is in use.
> This is on my test SQL Server 2000 on my intranet with no other users. Both
> Enterprise Manager and Query Analyser are not running.
> Please advise.
>
>
|||Run sp_who or sp_who2 and see who is connected to it.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Roger Withnell" <rogerREMOVE@.THISFIRSTupperbridge.co.uk> wrote in message
news:c9g2ap$s9f$1@.hercules.btinternet.com...
RESTORE DATABASE upperbridge
FROM DISK = C:\...........
WITH REPLACE
gets the error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not be
obtained because the database is in use.
This is on my test SQL Server 2000 on my intranet with no other users. Both
Enterprise Manager and Query Analyser are not running.
Please advise.
|||Are you sure that the QA session you're using to restore the database isn't
the one that's using the database? Use sp_who2 to see who's using it.
"Roger Withnell" <rogerREMOVE@.THISFIRSTupperbridge.co.uk> wrote in message
news:c9g2ap$s9f$1@.hercules.btinternet.com...
RESTORE DATABASE upperbridge
FROM DISK = C:\...........
WITH REPLACE
gets the error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not be
obtained because the database is in use.
This is on my test SQL Server 2000 on my intranet with no other users. Both
Enterprise Manager and Query Analyser are not running.
Please advise.
|||Hi Roger,
Execute the below script from master to restore the database (Execute all
the lines in a together from query analyzer - Replace dbname , filename ,
directory and
logical name based on ur requirement)
use master
go
alter database <dbname> set single_user with rollback immediate
go
restore database <dbname> from disk = 'C:\filename.bak' with recovery,
move 'logical_mdf_name' to 'c:\mssql\data\filename.mdf',
move 'logical_ldf_name' to 'c:\mssql\data\filename.ldf'
Thanks
Hari
MCDBA
"Roger Withnell" <rogerREMOVE@.THISFIRSTupperbridge.co.uk> wrote in message
news:c9g2ap$s9f$1@.hercules.btinternet.com...
> RESTORE DATABASE upperbridge
> FROM DISK = C:\...........
> WITH REPLACE
> gets the error
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not
be
> obtained because the database is in use.
> This is on my test SQL Server 2000 on my intranet with no other users.
Both
> Enterprise Manager and Query Analyser are not running.
> Please advise.
>
>

Cannot restore database

RESTORE DATABASE upperbridge
FROM DISK = C:\...........
WITH REPLACE
gets the error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not be
obtained because the database is in use.
This is on my test SQL Server 2000 on my intranet with no other users. Both
Enterprise Manager and Query Analyser are not running.
Please advise.Run sp_who or sp_who2 and see who is connected to it.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Roger Withnell" <rogerREMOVE@.THISFIRSTupperbridge.co.uk> wrote in message
news:c9g2ap$s9f$1@.hercules.btinternet.com...
RESTORE DATABASE upperbridge
FROM DISK = C:\...........
WITH REPLACE
gets the error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not be
obtained because the database is in use.
This is on my test SQL Server 2000 on my intranet with no other users. Both
Enterprise Manager and Query Analyser are not running.
Please advise.|||Seems like someone has a connection to the database. Make sure that the connection from where you do the
restore isn't in the database. Check who is in the database using sp_who or sp_who2.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roger Withnell" <rogerREMOVE@.THISFIRSTupperbridge.co.uk> wrote in message
news:c9g2ap$s9f$1@.hercules.btinternet.com...
> RESTORE DATABASE upperbridge
> FROM DISK = C:\...........
> WITH REPLACE
> gets the error
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not be
> obtained because the database is in use.
> This is on my test SQL Server 2000 on my intranet with no other users. Both
> Enterprise Manager and Query Analyser are not running.
> Please advise.
>
>|||Are you sure that the QA session you're using to restore the database isn't
the one that's using the database? Use sp_who2 to see who's using it.
"Roger Withnell" <rogerREMOVE@.THISFIRSTupperbridge.co.uk> wrote in message
news:c9g2ap$s9f$1@.hercules.btinternet.com...
RESTORE DATABASE upperbridge
FROM DISK = C:\...........
WITH REPLACE
gets the error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not be
obtained because the database is in use.
This is on my test SQL Server 2000 on my intranet with no other users. Both
Enterprise Manager and Query Analyser are not running.
Please advise.|||Hi Roger,
Execute the below script from master to restore the database (Execute all
the lines in a together from query analyzer - Replace dbname , filename ,
directory and
logical name based on ur requirement)
use master
go
alter database <dbname> set single_user with rollback immediate
go
restore database <dbname> from disk = 'C:\filename.bak' with recovery,
move 'logical_mdf_name' to 'c:\mssql\data\filename.mdf',
move 'logical_ldf_name' to 'c:\mssql\data\filename.ldf'
Thanks
Hari
MCDBA
"Roger Withnell" <rogerREMOVE@.THISFIRSTupperbridge.co.uk> wrote in message
news:c9g2ap$s9f$1@.hercules.btinternet.com...
> RESTORE DATABASE upperbridge
> FROM DISK = C:\...........
> WITH REPLACE
> gets the error
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not
be
> obtained because the database is in use.
> This is on my test SQL Server 2000 on my intranet with no other users.
Both
> Enterprise Manager and Query Analyser are not running.
> Please advise.
>
>

Cannot restore database

RESTORE DATABASE upperbridge
FROM DISK = C:\...........
WITH REPLACE
gets the error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access
could not be
obtained because the database is in use.
This is on my test SQL Server 2000 on my intranet with no other users. Both
Enterprise Manager and Query Analyser are not running.
Please advise.Seems like someone has a connection to the database. Make sure that the conn
ection from where you do the
restore isn't in the database. Check who is in the database using sp_who or
sp_who2.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roger Withnell" <rogerREMOVE@.THISFIRSTupperbridge.co.uk> wrote in message
news:c9g2ap$s9f$1@.hercules.btinternet.com...
> RESTORE DATABASE upperbridge
> FROM DISK = C:\...........
> WITH REPLACE
> gets the error
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive acces
s could not be
> obtained because the database is in use.
> This is on my test SQL Server 2000 on my intranet with no other users. Bo
th
> Enterprise Manager and Query Analyser are not running.
> Please advise.
>
>|||Run sp_who or sp_who2 and see who is connected to it.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Roger Withnell" <rogerREMOVE@.THISFIRSTupperbridge.co.uk> wrote in message
news:c9g2ap$s9f$1@.hercules.btinternet.com...
RESTORE DATABASE upperbridge
FROM DISK = C:\...........
WITH REPLACE
gets the error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access
could not be
obtained because the database is in use.
This is on my test SQL Server 2000 on my intranet with no other users. Both
Enterprise Manager and Query Analyser are not running.
Please advise.|||Are you sure that the QA session you're using to restore the database isn't
the one that's using the database? Use sp_who2 to see who's using it.
"Roger Withnell" <rogerREMOVE@.THISFIRSTupperbridge.co.uk> wrote in message
news:c9g2ap$s9f$1@.hercules.btinternet.com...
RESTORE DATABASE upperbridge
FROM DISK = C:\...........
WITH REPLACE
gets the error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access
could not be
obtained because the database is in use.
This is on my test SQL Server 2000 on my intranet with no other users. Both
Enterprise Manager and Query Analyser are not running.
Please advise.|||Hi Roger,
Execute the below script from master to restore the database (Execute all
the lines in a together from query analyzer - Replace dbname , filename ,
directory and
logical name based on ur requirement)
use master
go
alter database <dbname> set single_user with rollback immediate
go
restore database <dbname> from disk = 'C:\filename.bak' with recovery,
move 'logical_mdf_name' to 'c:\mssql\data\filename.mdf',
move 'logical_ldf_name' to 'c:\mssql\data\filename.ldf'
Thanks
Hari
MCDBA
"Roger Withnell" <rogerREMOVE@.THISFIRSTupperbridge.co.uk> wrote in message
news:c9g2ap$s9f$1@.hercules.btinternet.com...
> RESTORE DATABASE upperbridge
> FROM DISK = C:\...........
> WITH REPLACE
> gets the error
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could
not
be
> obtained because the database is in use.
> This is on my test SQL Server 2000 on my intranet with no other users.
Both
> Enterprise Manager and Query Analyser are not running.
> Please advise.
>
>