Hello,
Is there anyway to estimate the transaction log files needed to reindex all
tables in a database?
I mean any formula that can be used?
regards,
meLook at how big your indexes are now. this is different depending on the
version of sql server you are on. tlog will vary depending on this size and
the type of defrag operation done.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:FF5149C0-A8B3-4B34-BFE3-D18A7E2ED15E@.microsoft.com...
> Hello,
> Is there anyway to estimate the transaction log files needed to reindex
> all
> tables in a database?
> I mean any formula that can be used?
> regards,
> me|||Hi
You have to identify what tables are defragmented and run rebuild index only
on them. Please tell us what SQL Server you are using?
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:FF5149C0-A8B3-4B34-BFE3-D18A7E2ED15E@.microsoft.com...
> Hello,
> Is there anyway to estimate the transaction log files needed to reindex
> all
> tables in a database?
> I mean any formula that can be used?
> regards,
> mesql
Showing posts with label transaction. Show all posts
Showing posts with label transaction. Show all posts
Tuesday, March 27, 2012
Monday, March 26, 2012
Establish log dhipping db in sql2005
Hi all
I have db on my log shipping server as standby\readonly and my transaction log
was out of sync, I would like to make full bak from the source server and
restore
on log shipping server but when I right click the db in sql manager studio
the RESTORE database option DIM out. I can do this with sql 2000 to
reestablish
db when my transaction log was out of sync but look like I can't do this
with sql 2005. Have i missed any thing here?
Please help
Thanks in advancedfrank
Personally I have not played yet with it on SQL Server 2005 , but can you
issue BACKUP/RESTORE in the query builder instead of SSMS?
"frank" <frank@.discussions.microsoft.com> wrote in message
news:752AC215-1833-4B97-B57C-2E52C646D6BF@.microsoft.com...
> Hi all
> I have db on my log shipping server as standby\readonly and my transaction
> log
> was out of sync, I would like to make full bak from the source server and
> restore
> on log shipping server but when I right click the db in sql manager studio
> the RESTORE database option DIM out. I can do this with sql 2000 to
> reestablish
> db when my transaction log was out of sync but look like I can't do this
> with sql 2005. Have i missed any thing here?
> Please help
> Thanks in advanced
I have db on my log shipping server as standby\readonly and my transaction log
was out of sync, I would like to make full bak from the source server and
restore
on log shipping server but when I right click the db in sql manager studio
the RESTORE database option DIM out. I can do this with sql 2000 to
reestablish
db when my transaction log was out of sync but look like I can't do this
with sql 2005. Have i missed any thing here?
Please help
Thanks in advancedfrank
Personally I have not played yet with it on SQL Server 2005 , but can you
issue BACKUP/RESTORE in the query builder instead of SSMS?
"frank" <frank@.discussions.microsoft.com> wrote in message
news:752AC215-1833-4B97-B57C-2E52C646D6BF@.microsoft.com...
> Hi all
> I have db on my log shipping server as standby\readonly and my transaction
> log
> was out of sync, I would like to make full bak from the source server and
> restore
> on log shipping server but when I right click the db in sql manager studio
> the RESTORE database option DIM out. I can do this with sql 2000 to
> reestablish
> db when my transaction log was out of sync but look like I can't do this
> with sql 2005. Have i missed any thing here?
> Please help
> Thanks in advanced
Establish log dhipping db in sql2005
Hi all
I have db on my log shipping server as standby\readonly and my transaction log
was out of sync, I would like to make full bak from the source server and
restore
on log shipping server but when I right click the db in sql manager studio
the RESTORE database option DIM out. I can do this with sql 2000 to
reestablish
db when my transaction log was out of sync but look like I can't do this
with sql 2005. Have i missed any thing here?
Please help
Thanks in advanced
frank
Personally I have not played yet with it on SQL Server 2005 , but can you
issue BACKUP/RESTORE in the query builder instead of SSMS?
"frank" <frank@.discussions.microsoft.com> wrote in message
news:752AC215-1833-4B97-B57C-2E52C646D6BF@.microsoft.com...
> Hi all
> I have db on my log shipping server as standby\readonly and my transaction
> log
> was out of sync, I would like to make full bak from the source server and
> restore
> on log shipping server but when I right click the db in sql manager studio
> the RESTORE database option DIM out. I can do this with sql 2000 to
> reestablish
> db when my transaction log was out of sync but look like I can't do this
> with sql 2005. Have i missed any thing here?
> Please help
> Thanks in advanced
I have db on my log shipping server as standby\readonly and my transaction log
was out of sync, I would like to make full bak from the source server and
restore
on log shipping server but when I right click the db in sql manager studio
the RESTORE database option DIM out. I can do this with sql 2000 to
reestablish
db when my transaction log was out of sync but look like I can't do this
with sql 2005. Have i missed any thing here?
Please help
Thanks in advanced
frank
Personally I have not played yet with it on SQL Server 2005 , but can you
issue BACKUP/RESTORE in the query builder instead of SSMS?
"frank" <frank@.discussions.microsoft.com> wrote in message
news:752AC215-1833-4B97-B57C-2E52C646D6BF@.microsoft.com...
> Hi all
> I have db on my log shipping server as standby\readonly and my transaction
> log
> was out of sync, I would like to make full bak from the source server and
> restore
> on log shipping server but when I right click the db in sql manager studio
> the RESTORE database option DIM out. I can do this with sql 2000 to
> reestablish
> db when my transaction log was out of sync but look like I can't do this
> with sql 2005. Have i missed any thing here?
> Please help
> Thanks in advanced
Establish log dhipping db in sql2005
Hi all
I have db on my log shipping server as standby\readonly and my transaction l
og
was out of sync, I would like to make full bak from the source server and
restore
on log shipping server but when I right click the db in sql manager studio
the RESTORE database option DIM out. I can do this with sql 2000 to
reestablish
db when my transaction log was out of sync but look like I can't do this
with sql 2005. Have i missed any thing here?
Please help
Thanks in advancedfrank
Personally I have not played yet with it on SQL Server 2005 , but can you
issue BACKUP/RESTORE in the query builder instead of SSMS?
"frank" <frank@.discussions.microsoft.com> wrote in message
news:752AC215-1833-4B97-B57C-2E52C646D6BF@.microsoft.com...
> Hi all
> I have db on my log shipping server as standby\readonly and my transaction
> log
> was out of sync, I would like to make full bak from the source server and
> restore
> on log shipping server but when I right click the db in sql manager studio
> the RESTORE database option DIM out. I can do this with sql 2000 to
> reestablish
> db when my transaction log was out of sync but look like I can't do this
> with sql 2005. Have i missed any thing here?
> Please help
> Thanks in advanced
I have db on my log shipping server as standby\readonly and my transaction l
og
was out of sync, I would like to make full bak from the source server and
restore
on log shipping server but when I right click the db in sql manager studio
the RESTORE database option DIM out. I can do this with sql 2000 to
reestablish
db when my transaction log was out of sync but look like I can't do this
with sql 2005. Have i missed any thing here?
Please help
Thanks in advancedfrank
Personally I have not played yet with it on SQL Server 2005 , but can you
issue BACKUP/RESTORE in the query builder instead of SSMS?
"frank" <frank@.discussions.microsoft.com> wrote in message
news:752AC215-1833-4B97-B57C-2E52C646D6BF@.microsoft.com...
> Hi all
> I have db on my log shipping server as standby\readonly and my transaction
> log
> was out of sync, I would like to make full bak from the source server and
> restore
> on log shipping server but when I right click the db in sql manager studio
> the RESTORE database option DIM out. I can do this with sql 2000 to
> reestablish
> db when my transaction log was out of sync but look like I can't do this
> with sql 2005. Have i missed any thing here?
> Please help
> Thanks in advanced
escaping a transaction
The code is
proc1
begin tgan
insert
exec proc2
commit tran
end
proc2
begin tran
commit tran
exec proc3 -- do not want to it be in the proc1's tran
end
The desire is to escape the execution of proc3 from the transaction started
by proc1.
If you like, the apology for the decision is: The proc3 does some complex
things (a couple of records are inserted, deleted, updated). This must be
done 1) atomically (all or nothing) but the operations are 2) expensive and
3) must always commit. So, for performance reasons, I have decieded to do it
transceding the transaction. In case of failure, which may happen only
because of system shut down, the state can be restored manually.I'm not sure I completely understand your question, however you may want to
take a look at the following stuff.
Check out ROLLBACK TRAN
http://msdn2.microsoft.com/en-us/library/ms181299.aspx
and TRY ... CATCH (SQL 2005 only)
http://msdn2.microsoft.com/en-us/library/ms175976.aspx
--
Ekrem Önsoy
"valentin tihomirov" <V_tihomirov@.best.ee> wrote in message
news:eNi74gCFIHA.3400@.TK2MSFTNGP03.phx.gbl...
> The code is
> proc1
> begin tgan
> insert
> exec proc2
> commit tran
> end
> proc2
> begin tran
> commit tran
> exec proc3 -- do not want to it be in the proc1's tran
> end
> The desire is to escape the execution of proc3 from the transaction
> started by proc1.
> If you like, the apology for the decision is: The proc3 does some complex
> things (a couple of records are inserted, deleted, updated). This must be
> done 1) atomically (all or nothing) but the operations are 2) expensive
> and 3) must always commit. So, for performance reasons, I have decieded to
> do it transceding the transaction. In case of failure, which may happen
> only because of system shut down, the state can be restored manually.
>|||Hi
I am not sure why you would want to do this as it would prolong the length
of the transaction without participating in it!
You may want to look at service broker, you could call xp_cmdshell to run a
query.
John
"valentin tihomirov" wrote:
> The code is
> proc1
> begin tgan
> insert
> exec proc2
> commit tran
> end
> proc2
> begin tran
> commit tran
> exec proc3 -- do not want to it be in the proc1's tran
> end
> The desire is to escape the execution of proc3 from the transaction started
> by proc1.
> If you like, the apology for the decision is: The proc3 does some complex
> things (a couple of records are inserted, deleted, updated). This must be
> done 1) atomically (all or nothing) but the operations are 2) expensive and
> 3) must always commit. So, for performance reasons, I have decieded to do it
> transceding the transaction. In case of failure, which may happen only
> because of system shut down, the state can be restored manually.
>
>|||On 21 Oct, 22:21, "valentin tihomirov" <V_tihomi...@.best.ee> wrote:
> The code is
> proc1
> begin tgan
> insert
> exec proc2
> commit tran
> end
> proc2
> begin tran
> commit tran
> exec proc3 -- do not want to it be in the proc1's tran
> end
> The desire is to escape the execution of proc3 from the transaction started
> by proc1.
> If you like, the apology for the decision is: The proc3 does some complex
> things (a couple of records are inserted, deleted, updated). This must be
> done 1) atomically (all or nothing) but the operations are 2) expensive and
> 3) must always commit. So, for performance reasons, I have decieded to do it
> transceding the transaction. In case of failure, which may happen only
> because of system shut down, the state can be restored manually.
proc1
begin tran
insert
commit tran
exec proc2
end
--
David Portas
proc1
begin tgan
insert
exec proc2
commit tran
end
proc2
begin tran
commit tran
exec proc3 -- do not want to it be in the proc1's tran
end
The desire is to escape the execution of proc3 from the transaction started
by proc1.
If you like, the apology for the decision is: The proc3 does some complex
things (a couple of records are inserted, deleted, updated). This must be
done 1) atomically (all or nothing) but the operations are 2) expensive and
3) must always commit. So, for performance reasons, I have decieded to do it
transceding the transaction. In case of failure, which may happen only
because of system shut down, the state can be restored manually.I'm not sure I completely understand your question, however you may want to
take a look at the following stuff.
Check out ROLLBACK TRAN
http://msdn2.microsoft.com/en-us/library/ms181299.aspx
and TRY ... CATCH (SQL 2005 only)
http://msdn2.microsoft.com/en-us/library/ms175976.aspx
--
Ekrem Önsoy
"valentin tihomirov" <V_tihomirov@.best.ee> wrote in message
news:eNi74gCFIHA.3400@.TK2MSFTNGP03.phx.gbl...
> The code is
> proc1
> begin tgan
> insert
> exec proc2
> commit tran
> end
> proc2
> begin tran
> commit tran
> exec proc3 -- do not want to it be in the proc1's tran
> end
> The desire is to escape the execution of proc3 from the transaction
> started by proc1.
> If you like, the apology for the decision is: The proc3 does some complex
> things (a couple of records are inserted, deleted, updated). This must be
> done 1) atomically (all or nothing) but the operations are 2) expensive
> and 3) must always commit. So, for performance reasons, I have decieded to
> do it transceding the transaction. In case of failure, which may happen
> only because of system shut down, the state can be restored manually.
>|||Hi
I am not sure why you would want to do this as it would prolong the length
of the transaction without participating in it!
You may want to look at service broker, you could call xp_cmdshell to run a
query.
John
"valentin tihomirov" wrote:
> The code is
> proc1
> begin tgan
> insert
> exec proc2
> commit tran
> end
> proc2
> begin tran
> commit tran
> exec proc3 -- do not want to it be in the proc1's tran
> end
> The desire is to escape the execution of proc3 from the transaction started
> by proc1.
> If you like, the apology for the decision is: The proc3 does some complex
> things (a couple of records are inserted, deleted, updated). This must be
> done 1) atomically (all or nothing) but the operations are 2) expensive and
> 3) must always commit. So, for performance reasons, I have decieded to do it
> transceding the transaction. In case of failure, which may happen only
> because of system shut down, the state can be restored manually.
>
>|||On 21 Oct, 22:21, "valentin tihomirov" <V_tihomi...@.best.ee> wrote:
> The code is
> proc1
> begin tgan
> insert
> exec proc2
> commit tran
> end
> proc2
> begin tran
> commit tran
> exec proc3 -- do not want to it be in the proc1's tran
> end
> The desire is to escape the execution of proc3 from the transaction started
> by proc1.
> If you like, the apology for the decision is: The proc3 does some complex
> things (a couple of records are inserted, deleted, updated). This must be
> done 1) atomically (all or nothing) but the operations are 2) expensive and
> 3) must always commit. So, for performance reasons, I have decieded to do it
> transceding the transaction. In case of failure, which may happen only
> because of system shut down, the state can be restored manually.
proc1
begin tran
insert
commit tran
exec proc2
end
--
David Portas
Monday, March 19, 2012
Errors restoring Transaction Logs
Howdy,
I just accidently wiped out a table that had some pretty important data in
it. Can I restore it from the transaction log? Here's what I've tried so far
and I get the errors as stated below. I did do "net pause mssqlserver"
before running it.
USE master
RESTORE DATABASE cpts
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\RPCS.mdf'
WITH RECOVERY
RESTORE LOG cpts
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Copy of
RPCS.ldf'
WITH RECOVERY, STOPAT = 'Apr 24, 2006 5:20 PM'
Erros
Server: Msg 3101, Level 16, State 2, Line 3
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
Server: Msg 3101, Level 16, State 2, Line 7
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE LOG is terminating abnormally.
Thanks!!
David LozziYour database cpts will need to be changed to single user mode.
EXEC master.dbo.sp_dboption 'cpts', 'single user', true
(Be reminded to set it to false afterwards.)
Martin C K Poon
Senior Analyst Programmer
====================================
"David Lozzi" <dlozzi@.nospam.nospam> bl
news:u6smwZ$ZGHA.1196@.TK2MSFTNGP03.phx.gbl g...
> Howdy,
> I just accidently wiped out a table that had some pretty important data in
> it. Can I restore it from the transaction log? Here's what I've tried so
far
> and I get the errors as stated below. I did do "net pause mssqlserver"
> before running it.
> USE master
> RESTORE DATABASE cpts
> FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\RPCS.mdf'
> WITH RECOVERY
> RESTORE LOG cpts
> FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Copy of
> RPCS.ldf'
> WITH RECOVERY, STOPAT = 'Apr 24, 2006 5:20 PM'
>
> Erros
> Server: Msg 3101, Level 16, State 2, Line 3
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 3
> RESTORE DATABASE is terminating abnormally.
> Server: Msg 3101, Level 16, State 2, Line 7
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 7
> RESTORE LOG is terminating abnormally.
> Thanks!!
> David Lozzi
>|||Pausing the service only prevents new server connections; existing
connections to the database are not affected.
You'll need to kill connections to the database before you can restore.
With SQL 2000 and above, you can do this easily with ALTER DATABASE ...
ROLLBACK IMMEDIATE:
ALTER DATABASE MyDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
Note that you don't necessarily need to overwrite the existing database to
recovery the lost data. An alternative is to restore to a different
database name (and different file names) so you can extract the lost data.
However, if related tables have been modified since the problem, you might
find it easier to overwrite the existing database,
Hope this helps.
Dan Guzman
SQL Server MVP
"David Lozzi" <dlozzi@.nospam.nospam> wrote in message
news:u6smwZ$ZGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Howdy,
> I just accidently wiped out a table that had some pretty important data in
> it. Can I restore it from the transaction log? Here's what I've tried so
> far
> and I get the errors as stated below. I did do "net pause mssqlserver"
> before running it.
> USE master
> RESTORE DATABASE cpts
> FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\RPCS.mdf'
> WITH RECOVERY
> RESTORE LOG cpts
> FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Copy of
> RPCS.ldf'
> WITH RECOVERY, STOPAT = 'Apr 24, 2006 5:20 PM'
>
> Erros
> Server: Msg 3101, Level 16, State 2, Line 3
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 3
> RESTORE DATABASE is terminating abnormally.
> Server: Msg 3101, Level 16, State 2, Line 7
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 7
> RESTORE LOG is terminating abnormally.
> Thanks!!
> David Lozzi
>|||Seems you are trying to restore from the database files (mdf, ldf). That is
not how RESTORE work,
You restore from backup files (taken by the TSQL BACKUP command).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Lozzi" <dlozzi@.nospam.nospam> wrote in message news:u6smwZ$ZGHA.1196@.TK2MSFTNGP03.ph
x.gbl...
> Howdy,
> I just accidently wiped out a table that had some pretty important data in
> it. Can I restore it from the transaction log? Here's what I've tried so f
ar
> and I get the errors as stated below. I did do "net pause mssqlserver"
> before running it.
> USE master
> RESTORE DATABASE cpts
> FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\RPCS.mdf'
> WITH RECOVERY
> RESTORE LOG cpts
> FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Copy of
> RPCS.ldf'
> WITH RECOVERY, STOPAT = 'Apr 24, 2006 5:20 PM'
>
> Erros
> Server: Msg 3101, Level 16, State 2, Line 3
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 3
> RESTORE DATABASE is terminating abnormally.
> Server: Msg 3101, Level 16, State 2, Line 7
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 7
> RESTORE LOG is terminating abnormally.
> Thanks!!
> David Lozzi
>|||Good catch, Tibor.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%235lJqxFaGHA.4292@.TK2MSFTNGP04.phx.gbl...
> Seems you are trying to restore from the database files (mdf, ldf). That
> is not how RESTORE work, You restore from backup files (taken by the TSQL
> BACKUP command).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David Lozzi" <dlozzi@.nospam.nospam> wrote in message
> news:u6smwZ$ZGHA.1196@.TK2MSFTNGP03.phx.gbl...
>
I just accidently wiped out a table that had some pretty important data in
it. Can I restore it from the transaction log? Here's what I've tried so far
and I get the errors as stated below. I did do "net pause mssqlserver"
before running it.
USE master
RESTORE DATABASE cpts
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\RPCS.mdf'
WITH RECOVERY
RESTORE LOG cpts
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Copy of
RPCS.ldf'
WITH RECOVERY, STOPAT = 'Apr 24, 2006 5:20 PM'
Erros
Server: Msg 3101, Level 16, State 2, Line 3
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
Server: Msg 3101, Level 16, State 2, Line 7
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE LOG is terminating abnormally.
Thanks!!
David LozziYour database cpts will need to be changed to single user mode.
EXEC master.dbo.sp_dboption 'cpts', 'single user', true
(Be reminded to set it to false afterwards.)
Martin C K Poon
Senior Analyst Programmer
====================================
"David Lozzi" <dlozzi@.nospam.nospam> bl
news:u6smwZ$ZGHA.1196@.TK2MSFTNGP03.phx.gbl g...
> Howdy,
> I just accidently wiped out a table that had some pretty important data in
> it. Can I restore it from the transaction log? Here's what I've tried so
far
> and I get the errors as stated below. I did do "net pause mssqlserver"
> before running it.
> USE master
> RESTORE DATABASE cpts
> FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\RPCS.mdf'
> WITH RECOVERY
> RESTORE LOG cpts
> FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Copy of
> RPCS.ldf'
> WITH RECOVERY, STOPAT = 'Apr 24, 2006 5:20 PM'
>
> Erros
> Server: Msg 3101, Level 16, State 2, Line 3
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 3
> RESTORE DATABASE is terminating abnormally.
> Server: Msg 3101, Level 16, State 2, Line 7
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 7
> RESTORE LOG is terminating abnormally.
> Thanks!!
> David Lozzi
>|||Pausing the service only prevents new server connections; existing
connections to the database are not affected.
You'll need to kill connections to the database before you can restore.
With SQL 2000 and above, you can do this easily with ALTER DATABASE ...
ROLLBACK IMMEDIATE:
ALTER DATABASE MyDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
Note that you don't necessarily need to overwrite the existing database to
recovery the lost data. An alternative is to restore to a different
database name (and different file names) so you can extract the lost data.
However, if related tables have been modified since the problem, you might
find it easier to overwrite the existing database,
Hope this helps.
Dan Guzman
SQL Server MVP
"David Lozzi" <dlozzi@.nospam.nospam> wrote in message
news:u6smwZ$ZGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Howdy,
> I just accidently wiped out a table that had some pretty important data in
> it. Can I restore it from the transaction log? Here's what I've tried so
> far
> and I get the errors as stated below. I did do "net pause mssqlserver"
> before running it.
> USE master
> RESTORE DATABASE cpts
> FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\RPCS.mdf'
> WITH RECOVERY
> RESTORE LOG cpts
> FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Copy of
> RPCS.ldf'
> WITH RECOVERY, STOPAT = 'Apr 24, 2006 5:20 PM'
>
> Erros
> Server: Msg 3101, Level 16, State 2, Line 3
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 3
> RESTORE DATABASE is terminating abnormally.
> Server: Msg 3101, Level 16, State 2, Line 7
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 7
> RESTORE LOG is terminating abnormally.
> Thanks!!
> David Lozzi
>|||Seems you are trying to restore from the database files (mdf, ldf). That is
not how RESTORE work,
You restore from backup files (taken by the TSQL BACKUP command).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Lozzi" <dlozzi@.nospam.nospam> wrote in message news:u6smwZ$ZGHA.1196@.TK2MSFTNGP03.ph
x.gbl...
> Howdy,
> I just accidently wiped out a table that had some pretty important data in
> it. Can I restore it from the transaction log? Here's what I've tried so f
ar
> and I get the errors as stated below. I did do "net pause mssqlserver"
> before running it.
> USE master
> RESTORE DATABASE cpts
> FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\RPCS.mdf'
> WITH RECOVERY
> RESTORE LOG cpts
> FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Copy of
> RPCS.ldf'
> WITH RECOVERY, STOPAT = 'Apr 24, 2006 5:20 PM'
>
> Erros
> Server: Msg 3101, Level 16, State 2, Line 3
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 3
> RESTORE DATABASE is terminating abnormally.
> Server: Msg 3101, Level 16, State 2, Line 7
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 7
> RESTORE LOG is terminating abnormally.
> Thanks!!
> David Lozzi
>|||Good catch, Tibor.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%235lJqxFaGHA.4292@.TK2MSFTNGP04.phx.gbl...
> Seems you are trying to restore from the database files (mdf, ldf). That
> is not how RESTORE work, You restore from backup files (taken by the TSQL
> BACKUP command).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David Lozzi" <dlozzi@.nospam.nospam> wrote in message
> news:u6smwZ$ZGHA.1196@.TK2MSFTNGP03.phx.gbl...
>
Friday, March 9, 2012
Errors attaching a database after tlog filled.
I have a database, "heat", that crashed after the transaction log filled the HDD.
Since then I have been unsuccessful in bring the database back up.
I'm not concerned with restoring what's in the transaction log, only the data file.
The current state is as follows:
The database is not attached.
When I try to attach the database using the transaction log (I made some additional room on the drive), I receive "Error 9003..."
I noticed in the forum the same error, so I tried the fix provided which was to attach only the data file. I renamed the log file, and tried to attach the data file but received the following error:
Error 1813: Could not open new database "heat". CREATE DATABASE is aborted.
Device activation error. The physical file name "D:\Microsoft SQL Server\MSSQL\data\heat_Log.LDF may be incorrect.
Any help would br greatly appreciated.
-AndyWhat commands are you using to attatch the database ??|||I'm using the Attach database command in enterprise manager.
Ok, I restored a backup of the database, then pulled a switcharoo (I have copies of all of the files, so I'm not overwriting anything).
The database is now suspect. Any ideas?|||try using sp_attach_single_file_db 'dbname','Physical path of the MDF file'
dont know about EM though , never use it at all|||Originally posted by monza700
When I try to attach the database using the transaction log (I made some additional room on the drive), I receive "Error 9003..."
Just a tad confusing...you don't attach using the tranny...
you don't mean that, right?
How did you clean up the hard drive?
What % is left
You need to set up alerts to tell you you're at a threshhold...
How big is the db btw, and the hard drive?|||I tried running the sp_attach_single_file_db command and received the following:
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'heat'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'D:\Microsoft SQL Server\MSSQL\data\heat_Log.LDF' may be incorrect.
Thanks for any/all help.|||In response:
Q: you don't mean that, right?
A: No, sorry, I'm trying to attach the mdf file.
Q: How did you clean up the hard drive?
A: deleted unneeded backups that were on the same drive.
Q: What % is left
A: 6GB
Q: You need to set up alerts to tell you you're at a threshhold...
A: I will do that once I get the database fixed (hindsight)
Q: How big is the db btw, and the hard drive? [/SIZE][/QUOTE]
A: about 1GB.|||First, you restore the mdf
Second, I think (only on rare occasions that is), to attach, you first need to have dettached...
Why not just do a RESTORE?
Like:
ALTER DATABASE TaxReconDB_Prod SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE TaxReconDB_Prod
FROM DISK = 'D:\Tax\BackUp\TaxReconDB.dmp'
WITH MOVE 'TaxReconDB_Data' TO 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Dat a\TaxReconDB_Prod_Data.MDF'
, MOVE 'TaxReconDB_Log' TO 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Dat a\TaxReconDB_Prod_Log.LDF'
, REPLACE
ALTER DATABASE TaxReconDB_Prod SET READ_WRITE|||If you notice, the restore takes tyhe mdf and the ldf from the last time the database was dumped...
the ldf will contain all uncommitted transactions..
If you did tranny dumps after that, and want/need to recover them, they'll have to be applied as well, in order...|||No dump files (dmp) are available.
Unfortunately I came in sort of after the fact. And some people thought they would try deleting the transaction log.
I also found out that before any of this happened the database was set to FULL recovery mode. Would that be causing any issues?
Also, the database has already be detached.
(I'm feeling a Microsoft Tech Support call coming up).
Thanks again,
Andy|||There's nothing in
D:\Microsoft SQL Server\MSSQL\data\
with a .mdf extension?
(or ,bkp perhaps?)
Can you look in Enterprise manager to see if there are any maint. plans set up., or jobs running maint. stored procedures?
That's very odd...
Tecjh support can't help you with what you don't have...
And Database recovery FULL is no problem...just have to make sure to dump the tranny logs with a scheduled job...
If it's no big deal you could use simple...
Then just dump the database...
What did this poor database do, to deserve this?
And what did use to contain, just out curiosity?|||Ok, the solution went something like this:
Restored an old backup of the database.
Stopped the SQL service.
Replaced the new datafile with the old data file.
Started the SQL service.
Ran the following code on it (placed the database into a readonly, single user, dbo state a.k.a. Emergency state):
sp_configure 'allow updates',1
go
reconfigure with override
go
update sysdatabases
set status=-32768
where name = 'heat'
go
sp_configure 'allow updates',0
go
reconfigure with override
go
Copied all of the objects and data from the bad database into a new one.
Deleted the bad database.
Copied all of the objects and data from the new database into another database with the name of the original.
Everything is A-O.K.
Thanks again for all of your help.
Andy
Since then I have been unsuccessful in bring the database back up.
I'm not concerned with restoring what's in the transaction log, only the data file.
The current state is as follows:
The database is not attached.
When I try to attach the database using the transaction log (I made some additional room on the drive), I receive "Error 9003..."
I noticed in the forum the same error, so I tried the fix provided which was to attach only the data file. I renamed the log file, and tried to attach the data file but received the following error:
Error 1813: Could not open new database "heat". CREATE DATABASE is aborted.
Device activation error. The physical file name "D:\Microsoft SQL Server\MSSQL\data\heat_Log.LDF may be incorrect.
Any help would br greatly appreciated.
-AndyWhat commands are you using to attatch the database ??|||I'm using the Attach database command in enterprise manager.
Ok, I restored a backup of the database, then pulled a switcharoo (I have copies of all of the files, so I'm not overwriting anything).
The database is now suspect. Any ideas?|||try using sp_attach_single_file_db 'dbname','Physical path of the MDF file'
dont know about EM though , never use it at all|||Originally posted by monza700
When I try to attach the database using the transaction log (I made some additional room on the drive), I receive "Error 9003..."
Just a tad confusing...you don't attach using the tranny...
you don't mean that, right?
How did you clean up the hard drive?
What % is left
You need to set up alerts to tell you you're at a threshhold...
How big is the db btw, and the hard drive?|||I tried running the sp_attach_single_file_db command and received the following:
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'heat'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'D:\Microsoft SQL Server\MSSQL\data\heat_Log.LDF' may be incorrect.
Thanks for any/all help.|||In response:
Q: you don't mean that, right?
A: No, sorry, I'm trying to attach the mdf file.
Q: How did you clean up the hard drive?
A: deleted unneeded backups that were on the same drive.
Q: What % is left
A: 6GB
Q: You need to set up alerts to tell you you're at a threshhold...
A: I will do that once I get the database fixed (hindsight)
Q: How big is the db btw, and the hard drive? [/SIZE][/QUOTE]
A: about 1GB.|||First, you restore the mdf
Second, I think (only on rare occasions that is), to attach, you first need to have dettached...
Why not just do a RESTORE?
Like:
ALTER DATABASE TaxReconDB_Prod SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE TaxReconDB_Prod
FROM DISK = 'D:\Tax\BackUp\TaxReconDB.dmp'
WITH MOVE 'TaxReconDB_Data' TO 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Dat a\TaxReconDB_Prod_Data.MDF'
, MOVE 'TaxReconDB_Log' TO 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Dat a\TaxReconDB_Prod_Log.LDF'
, REPLACE
ALTER DATABASE TaxReconDB_Prod SET READ_WRITE|||If you notice, the restore takes tyhe mdf and the ldf from the last time the database was dumped...
the ldf will contain all uncommitted transactions..
If you did tranny dumps after that, and want/need to recover them, they'll have to be applied as well, in order...|||No dump files (dmp) are available.
Unfortunately I came in sort of after the fact. And some people thought they would try deleting the transaction log.
I also found out that before any of this happened the database was set to FULL recovery mode. Would that be causing any issues?
Also, the database has already be detached.
(I'm feeling a Microsoft Tech Support call coming up).
Thanks again,
Andy|||There's nothing in
D:\Microsoft SQL Server\MSSQL\data\
with a .mdf extension?
(or ,bkp perhaps?)
Can you look in Enterprise manager to see if there are any maint. plans set up., or jobs running maint. stored procedures?
That's very odd...
Tecjh support can't help you with what you don't have...
And Database recovery FULL is no problem...just have to make sure to dump the tranny logs with a scheduled job...
If it's no big deal you could use simple...
Then just dump the database...
What did this poor database do, to deserve this?
And what did use to contain, just out curiosity?|||Ok, the solution went something like this:
Restored an old backup of the database.
Stopped the SQL service.
Replaced the new datafile with the old data file.
Started the SQL service.
Ran the following code on it (placed the database into a readonly, single user, dbo state a.k.a. Emergency state):
sp_configure 'allow updates',1
go
reconfigure with override
go
update sysdatabases
set status=-32768
where name = 'heat'
go
sp_configure 'allow updates',0
go
reconfigure with override
go
Copied all of the objects and data from the bad database into a new one.
Deleted the bad database.
Copied all of the objects and data from the new database into another database with the name of the original.
Everything is A-O.K.
Thanks again for all of your help.
Andy
Friday, February 24, 2012
Error: Transaction context in use by another session
I am programming in ASP and SQL server.
I am using this tag <%Transaction=Required%> to do the transaction.
And it woks well, but the problem is that sometimes I get this error: "transaction context in use by another session"...
It's really weird because it only happens in the same row. If choose this row to be shown I get that message, but if choose another row I get result with no error. And if take out the tag <%Transaction... I don't get any error.
I've searched in other forums about this error, and one guy claims that there is a bug in the MTS and there is no way to solve the problem.
Could you please send the code of your transaction ?
Also, are you gettign GetContext Transaction ?
Regards-
|||Actually, the transaction is done only by using this tag <%@. Language=VBScript Transaction=Required%> in the begnning of the code, and the MTS (microsoft transaction server) will work the transaction.
It works well... if I get any error in the middle of the code everything is cancelled. And if take this tag out of my code then there will be no transaction.
It looks to me that the problem is not in the code. It happened that even when I don't have any insert,update or delete I get this message. It seems that this "MTS" opens two sessions to execute the queries... I would like to know how could I use that to avoid this problem ? Is there any configuration needed, either in SQL Server or this MTS ?
Sunday, February 19, 2012
Error: The connection manager failed to defect from the transaction.
Does anybody know what it means?
The context is: a package with a single Data Flow finishes with this error: [Connection manager "xxx"] Error: The connection manager failed to defect from the transaction.
All components inside the data flow reports a successfull green status, however the DataFlow Task remains yellow for a long time, when suddenly the package stops with the above message. Another information is that the table remains locked during package execution (a select on that table does not complete...). This is a serious issue, and I couldnt find any relevant information about that. Thanks in advance.
Hi Half Abude,
Can you tell us more about the package? What kind of SQL is being executed? Also I'm just guessing you're using transactions in the package, can you tell us how you've set the transaction boundaries in the package? (i.e. on what object(s) have you set TransactionOption to Required?)
Thanks, -David
Subscribe to:
Posts (Atom)