Deleting Rows from Publisher and from Subscriber

Hello
I have an issue on Deleting rows on Publisher.
I want to keep a 2 months old transaction data both on my Publisher
and on the Subscribers as well,
so eatch night a process is run on the publisher that deletes 2 months
old data, so actually eatch night
I have a deletion of 1 days data (the 61st day).
Now the problem is that the next day, in the morning synchronisation,
eatch of my subscriber gets about 100.000 or even more deletions and
that makes synchronization slow.Although i have filters on my
replication,somehow deletions are send to all subscribers!!!
Now I am looking for solutions!!!
One thought of mine is to delete 2 months old transactions from eatch
subscriber, but because some subscribers share data then I belive that
if a deletion occured in a subscriber then when it synchronises the
deletion is passed to the publisher and then this deletion again is
going to be transfered to other subscribers when they try to
syncronise after.Am I right? So the issue will still be an issue.
Any Ideas of how to delete records in my case?
Please help!
You could replicate the execution of a stored procedure which does the
delete, and this way only the stored proc will be executed on the subscriber.
Rgds,
Paul Ibison
|||Thanks for the quick answer ... can u please be more specific.
How can I do this?
And if the deletion ocurs on the sunscriber wont that be transfer to
the publisher and then to other subscribers
that share data?
Thanks
|||In sp_addarticle there is the option to use @.type = 'proc exec' and this
option also exists on the article properties when using the gui to replicate
a stored procedure using transactional replication. Note that this applies
only when running the stored proc on the publisher so you'd have to remove
the rows in this direction only.
Rgds,
Paul Ibison
|||I am using Merge Replication does this applies to Merge Replication as
well ?
|||No - the replication of the execution of stored procedures is a transactional
attribute.
Rgds,
Paul Ibison
|||Any Ideas about Merge Replication ?
Because I am using merge replication and my system is live, I cannot
change to other type of replication,
i am searching for a solution, something i can implement fast.
Thanks
|||There's nothing obvious because what you need is some way of bypassing the
normal functioning of merge. i suppose you could disable the merge triggers
then send down a delete command using sp_addscriptexec to remove the rows
then reenable the triggers. However then you'll still have the replication
metadata hanging around for these rows until it gets cleaned up. Also if
there are any concurrent updates to these rows then there'll be problems
because the merge agent will be looking for missing rows when it
synchrionizes.
There might be some way of modifying a column on the rows so they fall
outside of a filter you place on the article and are therefore not
replicated. Then you could manually remove them on the publisher and via a
script on the subscriber - this is what I'd investigate.
Rgds,
Paul Ibison
"savvaschr@.nodalsoft.com.cy" wrote:

> Any Ideas about Merge Replication ?
> Because I am using merge replication and my system is live, I cannot
> change to other type of replication,
> i am searching for a solution, something i can implement fast.
> Thanks
>
|||Thanks for your time and Answers,
Finaly I found the answer.
You can specify at the publisher the @.delete_tracking variable of
sp_addmergearticle to 'false'
By doing this the Publisher deletes records and does not send them for
synchronisation.
So in my case, I delete 2 months old rows from my transactions table
at the Publisher, those deletions
are not send in the Synchronisation, and then I delete individually 2
month old transactions from my subscribers.
In this way my synchronisation is faster because Subscribers dont have
to process 100.000 deletions or more
every morning that they Synchronise.
Regards
Savvas

Deleting Older Files

I have a backup job that has failed.

The database size is 20.6 GB and the Transaction logs are 135MB

The amount of disk space I have left is 3.65MB. Which I know is not going to work.

However on the maintenance plan it is suppose to remove files older than 1 day.

I am wondering if a job works like this:

Step 1 create backup file
Step 2 Create Transaction Log Back up
Step 3 Delete old backup file
Step 4 Delete old Transaction log back up

Which tell me I would need to have double amount of disk space to accommodate 2 20 GB backup file and 2 135MB Transaction log file.

Is this correct??

Also is there a way that I can have step 3,4 done first.

LystraAs far as the full backup, you can overwrite the old backup file every time you take a full backup. This way you don't need to reserve the space for two full backup file. In the case of log backup, if you don't need to or don't want to backup the log file, just use simple recovery mode. It looks like you are deleting them any way.|||That's the problem it is not deleting the older files, even though I have it check to delete the files.

The database recovery mode is set to full.

Lystra|||Drives are cheap....

And what happens when you need to migrate data around?

Do you have a disaster box?

How odten do you dump the transaction log?

How many dumps do you have now?

How big is the hard drive?|||I think I have solve the problem. The actual mdf is 47.2GB big and there is not enough disk space to accommodate this backup.

Thanks

Lystra|||Not to sound like a broken record.

I think I have solve the problem. The actual mdf is 47.2GB big and there is not enough disk space to accommodate this backup.

Thanks

Lystra

deleting a tx log backup

Hi,
I backed up my transaction log in order to perform a point in time restore.
I backed it up to an ad hoc folder. The database actually does backup the
log file once a night to a different location. I'm going to change this to
once every thirty minutes.
Can I delete this ad hoc transaction log file backup, or do future backups
depend on it?
Thanks very much for your ideas on this. I just want to be safe!
Many thanks!
AntHello,
If you have a FULL database backup taken after the specific trasnaction log
backup you could delete the log backup file mentioned. Otherwise you need
that specific log
backup file for while recovery.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
> Hi,
> I backed up my transaction log in order to perform a point in time
> restore.
> I backed it up to an ad hoc folder. The database actually does backup the
> log file once a night to a different location. I'm going to change this to
> once every thirty minutes.
> Can I delete this ad hoc transaction log file backup, or do future backups
> depend on it?
> Thanks very much for your ideas on this. I just want to be safe!
> Many thanks!
> Ant|||Hi Hari,
Thanks fro the answer.
Best wishes
Ant
"Hari Prasad" wrote:
> Hello,
> If you have a FULL database backup taken after the specific trasnaction log
> backup you could delete the log backup file mentioned. Otherwise you need
> that specific log
> backup file for while recovery.
> Thanks
> Hari
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
> > Hi,
> >
> > I backed up my transaction log in order to perform a point in time
> > restore.
> >
> > I backed it up to an ad hoc folder. The database actually does backup the
> > log file once a night to a different location. I'm going to change this to
> > once every thirty minutes.
> >
> > Can I delete this ad hoc transaction log file backup, or do future backups
> > depend on it?
> >
> > Thanks very much for your ideas on this. I just want to be safe!
> >
> > Many thanks!
> >
> > Ant
>
>

deleting a tx log backup

Hi,
I backed up my transaction log in order to perform a point in time restore.
I backed it up to an ad hoc folder. The database actually does backup the
log file once a night to a different location. I'm going to change this to
once every thirty minutes.
Can I delete this ad hoc transaction log file backup, or do future backups
depend on it?
Thanks very much for your ideas on this. I just want to be safe!
Many thanks!
Ant
Hello,
If you have a FULL database backup taken after the specific trasnaction log
backup you could delete the log backup file mentioned. Otherwise you need
that specific log
backup file for while recovery.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
> Hi,
> I backed up my transaction log in order to perform a point in time
> restore.
> I backed it up to an ad hoc folder. The database actually does backup the
> log file once a night to a different location. I'm going to change this to
> once every thirty minutes.
> Can I delete this ad hoc transaction log file backup, or do future backups
> depend on it?
> Thanks very much for your ideas on this. I just want to be safe!
> Many thanks!
> Ant
|||Hi Hari,
Thanks fro the answer.
Best wishes
Ant
"Hari Prasad" wrote:

> Hello,
> If you have a FULL database backup taken after the specific trasnaction log
> backup you could delete the log backup file mentioned. Otherwise you need
> that specific log
> backup file for while recovery.
> Thanks
> Hari
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
>
>

deleting a tx log backup

Hi,
I backed up my transaction log in order to perform a point in time restore.
I backed it up to an ad hoc folder. The database actually does backup the
log file once a night to a different location. I'm going to change this to
once every thirty minutes.
Can I delete this ad hoc transaction log file backup, or do future backups
depend on it?
Thanks very much for your ideas on this. I just want to be safe!
Many thanks!
AntHello,
If you have a FULL database backup taken after the specific trasnaction log
backup you could delete the log backup file mentioned. Otherwise you need
that specific log
backup file for while recovery.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
> Hi,
> I backed up my transaction log in order to perform a point in time
> restore.
> I backed it up to an ad hoc folder. The database actually does backup the
> log file once a night to a different location. I'm going to change this to
> once every thirty minutes.
> Can I delete this ad hoc transaction log file backup, or do future backups
> depend on it?
> Thanks very much for your ideas on this. I just want to be safe!
> Many thanks!
> Ant|||Hi Hari,
Thanks fro the answer.
Best wishes
Ant
"Hari Prasad" wrote:

> Hello,
> If you have a FULL database backup taken after the specific trasnaction lo
g
> backup you could delete the log backup file mentioned. Otherwise you need
> that specific log
> backup file for while recovery.
> Thanks
> Hari
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
>
>

deleting a backup device

I have created two backup devices for a database: 1 for the data and 1 for the logs. I back up transaction logs to the device over the course of a day and then do a differential backup for the data nightly.

Once a week, I do a full backup of the database. After the full backup is done, I want to delete the log file device since it's no longer needed. I've done it before, but haven't in a while and can't remember the syntax in the job step setup. Can someone please refresh me?

I know it's a CmdExec job, but the syntax of the step is eluding my memory.

Thanks for any help.just bumping for assistance.|||re-bumping... any assistance would be greatly appreciated.|||why delete the device? don't you want to leave it there to use it again. just set the backup log init to overwrite the previous log file.

Deleted Transaction Logs!

I accidentalty deleted the transaction logs on a DB I had, and now I can get
it up on SQL enterprise, any ideas?
-Gary
Hi,
Try to attach the database with out the tranaction log using
sp_attach_single_file_db system stored procedure. This may not work since
your database is not detached.
Usage:
sp_attach_single_file_db 'dbname' , 'physical_name with the path of MDF
file'
If it fails restore the database from the last successful backup.
Thanks
Hari
MCDBA
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I accidentalty deleted the transaction logs on a DB I had, and now I can
get
> it up on SQL enterprise, any ideas?
> -Gary
>
|||Thx Hari,
I have replications running, and it won't let me detach, any ideas on how to
detach this?
-Gary
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OKQbWmMHEHA.3584@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Try to attach the database with out the tranaction log using
> sp_attach_single_file_db system stored procedure. This may not work since
> your database is not detached.
> Usage:
> sp_attach_single_file_db 'dbname' , 'physical_name with the path of MDF
> file'
> If it fails restore the database from the last successful backup.
> Thanks
> Hari
> MCDBA
> "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> get
>
|||is it safe to delete these files? I wont lose core data will I?
-Gary
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I accidentalty deleted the transaction logs on a DB I had, and now I can
get
> it up on SQL enterprise, any ideas?
> -Gary
>
|||and if I try to disable publishing, I get errors regarding the database
whose transaction logs are deleted.
-Gary
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:uKy83nMHEHA.2924@.TK2MSFTNGP09.phx.gbl...
> Thx Hari,
> I have replications running, and it won't let me detach, any ideas on how
to
> detach this?
> -Gary
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OKQbWmMHEHA.3584@.TK2MSFTNGP09.phx.gbl...
since
MDF
can
>
|||You will loose any uncomitted data and anything after the last CHECKPOINT,
do you have your recovery model full or simple? If its simple I think you
loose less.
You can also create a new database with the same name, stop sql service and
replace the data files with your original database and then start the
service again. If this didnt work set the database to emergency mode and
then try to copy objects using DTS to a new database.
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:uZUp64MHEHA.2668@.TK2MSFTNGP10.phx.gbl...
> is it safe to delete these files? I wont lose core data will I?
> -Gary
> "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> get
>
|||how can I go to emergency mode?
-Gary
"Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
news:%23jMoDfNHEHA.3356@.TK2MSFTNGP11.phx.gbl...
> You will loose any uncomitted data and anything after the last CHECKPOINT,
> do you have your recovery model full or simple? If its simple I think you
> loose less.
> You can also create a new database with the same name, stop sql service
and
> replace the data files with your original database and then start the
> service again. If this didnt work set the database to emergency mode and
> then try to copy objects using DTS to a new database.
>
> "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> news:uZUp64MHEHA.2668@.TK2MSFTNGP10.phx.gbl...
can
>
|||UPDATE master..sysdatabases SET status=-32768 WHERE name='<dbname>'
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:uhpeyhNHEHA.2128@.TK2MSFTNGP11.phx.gbl...
> how can I go to emergency mode?
> -Gary
> "Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
> news:%23jMoDfNHEHA.3356@.TK2MSFTNGP11.phx.gbl...
CHECKPOINT,
you
> and
> can
>
|||You don't have any of the log files at all?
You're best bet is to open a call with PSS. Depending on the circumstances
PSS may be able to help you recover most of the data. There are a number of
things that you could do on your own, that are not documented. But,
honestly, the PSS call is pretty cheap and doing it on your own can be
tricky. I really think the PSS call is worth the price.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I accidentalty deleted the transaction logs on a DB I had, and now I can
get
> it up on SQL enterprise, any ideas?
> -Gary
>
|||Hi Gary,
Here is a technique suggested by Jasper Smith once. I
documented it for later use.
Here's a technique I have used in the past - you need to
change the file/database names as this was for a specific
case :
If you did not detach the database prior to copying it
then you will not be able to attach it easily (probably).
First thing to try is make sure the folder
'D:\DATA2K\MSSQL$SKINNER2K\DATA\ exists,if not
then create it,copy your MDF there and retry your statement
If that doesn't work then you're in a bit of trouble but
you can try this
1) Make sure you have a copy of PowerDVD301_2_Data.MDF
2) Create a new database called fake (default file
locations)
3) Stop SQL Service
4) Delete the fake_Data.MDF and copy PowerDVD301_2_Data.MDF
to where fake_Data.MDF used to be and rename the file
to fake_Data.MDF
5) Start SQL Service
6) Database fake will appear as suspect in EM
7) Open Query Analyser and in master database run the
following :
sp_configure 'allow updates',1
go
reconfigure with override
go
update sysdatabases set
status=-32768 where dbid=DB_ID('fake')
go
sp_configure 'allow updates',0
go
reconfigure with override
go
This will put the database in emergency recovery mode
8) Stop SQL Service
9) Delete the fake_Log.LDF file
10) Restart SQL Service
11) In QA run the following (with correct path for log)
dbcc rebuild_log('fake','h:\fake_log.ldf')
go
dbcc checkdb('fake') -- to check for errors
go
12) Now we need to rename the files, run the following
(make sure
there are no connections to it) in Query Analyser
(At this stage you can actually access the database so you
could use
DTS or bcp to move the data to another database .)
use master
go
sp_helpdb 'fake'
go
/* Make a note of the names of the files , you will need
them
in the next bit of the script to replace datafilename and
logfilename - it might be that they have the right names
*/
sp_renamedb 'fake','PowerDVD301'
go
alter database PowerDVD301
MODIFY FILE(NAME='datafilename', NEWNAME
= 'PowerDVD301_Data')
go
alter database PowerDVD301
MODIFY FILE(NAME='logfilename', NEWNAME
= 'PowerDVD301_Log')
go
dbcc checkdb('PowerDVD301')
go
sp_dboption 'PowerDVD301','dbo use only','false'
go
use PowerDVD301
go
sp_updatestats
go
13) You should now have a working database. However the
log file
will be small so it will be worth increasing its
size
Unfortunately your files will be called
fake_Data.MDF and
fake_Log.LDF but you can get round this by
detaching the
database properly and then renaming the files and
reattaching
it
14) Run the following in QA
sp_detach_db PowerDVD301
--now rename the files then reattach
sp_attach_db 'PowerDVD301','h:\dvd.mdf','h:\DVD.ldf'
Regards
Thirumal

Deleted Transaction Logs!

I accidentalty deleted the transaction logs on a DB I had, and now I can get
it up on SQL enterprise, any ideas?
-GaryHi,
Try to attach the database with out the tranaction log using
sp_attach_single_file_db system stored procedure. This may not work since
your database is not detached.
Usage:
sp_attach_single_file_db 'dbname' , 'physical_name with the path of MDF
file'
If it fails restore the database from the last successful backup.
Thanks
Hari
MCDBA
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I accidentalty deleted the transaction logs on a DB I had, and now I can
get
> it up on SQL enterprise, any ideas?
> -Gary
>|||Thx Hari,
I have replications running, and it won't let me detach, any ideas on how to
detach this?
-Gary
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OKQbWmMHEHA.3584@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Try to attach the database with out the tranaction log using
> sp_attach_single_file_db system stored procedure. This may not work since
> your database is not detached.
> Usage:
> sp_attach_single_file_db 'dbname' , 'physical_name with the path of MDF
> file'
> If it fails restore the database from the last successful backup.
> Thanks
> Hari
> MCDBA
> "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> > I accidentalty deleted the transaction logs on a DB I had, and now I can
> get
> > it up on SQL enterprise, any ideas?
> >
> > -Gary
> >
> >
>|||is it safe to delete these files? I wont lose core data will I?
-Gary
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I accidentalty deleted the transaction logs on a DB I had, and now I can
get
> it up on SQL enterprise, any ideas?
> -Gary
>|||and if I try to disable publishing, I get errors regarding the database
whose transaction logs are deleted.
-Gary
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:uKy83nMHEHA.2924@.TK2MSFTNGP09.phx.gbl...
> Thx Hari,
> I have replications running, and it won't let me detach, any ideas on how
to
> detach this?
> -Gary
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OKQbWmMHEHA.3584@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > Try to attach the database with out the tranaction log using
> > sp_attach_single_file_db system stored procedure. This may not work
since
> > your database is not detached.
> >
> > Usage:
> >
> > sp_attach_single_file_db 'dbname' , 'physical_name with the path of
MDF
> > file'
> >
> > If it fails restore the database from the last successful backup.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> > "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> > news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> > > I accidentalty deleted the transaction logs on a DB I had, and now I
can
> > get
> > > it up on SQL enterprise, any ideas?
> > >
> > > -Gary
> > >
> > >
> >
> >
>|||You will loose any uncomitted data and anything after the last CHECKPOINT,
do you have your recovery model full or simple? If its simple I think you
loose less.
You can also create a new database with the same name, stop sql service and
replace the data files with your original database and then start the
service again. If this didnt work set the database to emergency mode and
then try to copy objects using DTS to a new database.
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:uZUp64MHEHA.2668@.TK2MSFTNGP10.phx.gbl...
> is it safe to delete these files? I wont lose core data will I?
> -Gary
> "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> > I accidentalty deleted the transaction logs on a DB I had, and now I can
> get
> > it up on SQL enterprise, any ideas?
> >
> > -Gary
> >
> >
>|||how can I go to emergency mode?
-Gary
"Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
news:%23jMoDfNHEHA.3356@.TK2MSFTNGP11.phx.gbl...
> You will loose any uncomitted data and anything after the last CHECKPOINT,
> do you have your recovery model full or simple? If its simple I think you
> loose less.
> You can also create a new database with the same name, stop sql service
and
> replace the data files with your original database and then start the
> service again. If this didnt work set the database to emergency mode and
> then try to copy objects using DTS to a new database.
>
> "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> news:uZUp64MHEHA.2668@.TK2MSFTNGP10.phx.gbl...
> > is it safe to delete these files? I wont lose core data will I?
> >
> > -Gary
> >
> > "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> > news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> > > I accidentalty deleted the transaction logs on a DB I had, and now I
can
> > get
> > > it up on SQL enterprise, any ideas?
> > >
> > > -Gary
> > >
> > >
> >
> >
>|||UPDATE master..sysdatabases SET status=-32768 WHERE name='<dbname>'
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:uhpeyhNHEHA.2128@.TK2MSFTNGP11.phx.gbl...
> how can I go to emergency mode?
> -Gary
> "Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
> news:%23jMoDfNHEHA.3356@.TK2MSFTNGP11.phx.gbl...
> > You will loose any uncomitted data and anything after the last
CHECKPOINT,
> > do you have your recovery model full or simple? If its simple I think
you
> > loose less.
> >
> > You can also create a new database with the same name, stop sql service
> and
> > replace the data files with your original database and then start the
> > service again. If this didnt work set the database to emergency mode and
> > then try to copy objects using DTS to a new database.
> >
> >
> >
> > "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> > news:uZUp64MHEHA.2668@.TK2MSFTNGP10.phx.gbl...
> > > is it safe to delete these files? I wont lose core data will I?
> > >
> > > -Gary
> > >
> > > "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> > > news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> > > > I accidentalty deleted the transaction logs on a DB I had, and now I
> can
> > > get
> > > > it up on SQL enterprise, any ideas?
> > > >
> > > > -Gary
> > > >
> > > >
> > >
> > >
> >
> >
>|||You don't have any of the log files at all?
You're best bet is to open a call with PSS. Depending on the circumstances
PSS may be able to help you recover most of the data. There are a number of
things that you could do on your own, that are not documented. But,
honestly, the PSS call is pretty cheap and doing it on your own can be
tricky. I really think the PSS call is worth the price.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I accidentalty deleted the transaction logs on a DB I had, and now I can
get
> it up on SQL enterprise, any ideas?
> -Gary
>|||Hi Gary,
Here is a technique suggested by Jasper Smith once. I
documented it for later use.
--
Here's a technique I have used in the past - you need to
change the file/database names as this was for a specific
case :
If you did not detach the database prior to copying it
then you will not be able to attach it easily (probably).
First thing to try is make sure the folder
'D:\DATA2K\MSSQL$SKINNER2K\DATA\ exists,if not
then create it,copy your MDF there and retry your statement
If that doesn't work then you're in a bit of trouble but
you can try this
1) Make sure you have a copy of PowerDVD301_2_Data.MDF
2) Create a new database called fake (default file
locations)
3) Stop SQL Service
4) Delete the fake_Data.MDF and copy PowerDVD301_2_Data.MDF
to where fake_Data.MDF used to be and rename the file
to fake_Data.MDF
5) Start SQL Service
6) Database fake will appear as suspect in EM
7) Open Query Analyser and in master database run the
following :
sp_configure 'allow updates',1
go
reconfigure with override
go
update sysdatabases set
status=-32768 where dbid=DB_ID('fake')
go
sp_configure 'allow updates',0
go
reconfigure with override
go
This will put the database in emergency recovery mode
8) Stop SQL Service
9) Delete the fake_Log.LDF file
10) Restart SQL Service
11) In QA run the following (with correct path for log)
dbcc rebuild_log('fake','h:\fake_log.ldf')
go
dbcc checkdb('fake') -- to check for errors
go
12) Now we need to rename the files, run the following
(make sure
there are no connections to it) in Query Analyser
(At this stage you can actually access the database so you
could use
DTS or bcp to move the data to another database .)
use master
go
sp_helpdb 'fake'
go
/* Make a note of the names of the files , you will need
them
in the next bit of the script to replace datafilename and
logfilename - it might be that they have the right names
*/
sp_renamedb 'fake','PowerDVD301'
go
alter database PowerDVD301
MODIFY FILE(NAME='datafilename', NEWNAME
= 'PowerDVD301_Data')
go
alter database PowerDVD301
MODIFY FILE(NAME='logfilename', NEWNAME
= 'PowerDVD301_Log')
go
dbcc checkdb('PowerDVD301')
go
sp_dboption 'PowerDVD301','dbo use only','false'
go
use PowerDVD301
go
sp_updatestats
go
13) You should now have a working database. However the
log file
will be small so it will be worth increasing its
size
Unfortunately your files will be called
fake_Data.MDF and
fake_Log.LDF but you can get round this by
detaching the
database properly and then renaming the files and
reattaching
it
14) Run the following in QA
sp_detach_db PowerDVD301
--now rename the files then reattach
sp_attach_db 'PowerDVD301','h:\dvd.mdf','h:\DVD.ldf'
Regards
Thirumal|||thanks for the help!
-Gary
"Thirumal" <treddym@.hotmail.nospam.com> wrote in message
news:19c8701c41d14$d4ea68e0$a501280a@.phx.gbl...
> Hi Gary,
> Here is a technique suggested by Jasper Smith once. I
> documented it for later use.
> --
> Here's a technique I have used in the past - you need to
> change the file/database names as this was for a specific
> case :
> If you did not detach the database prior to copying it
> then you will not be able to attach it easily (probably).
> First thing to try is make sure the folder
> 'D:\DATA2K\MSSQL$SKINNER2K\DATA\ exists,if not
> then create it,copy your MDF there and retry your statement
> If that doesn't work then you're in a bit of trouble but
> you can try this
> 1) Make sure you have a copy of PowerDVD301_2_Data.MDF
> 2) Create a new database called fake (default file
> locations)
> 3) Stop SQL Service
> 4) Delete the fake_Data.MDF and copy PowerDVD301_2_Data.MDF
> to where fake_Data.MDF used to be and rename the file
> to fake_Data.MDF
> 5) Start SQL Service
> 6) Database fake will appear as suspect in EM
> 7) Open Query Analyser and in master database run the
> following :
> sp_configure 'allow updates',1
> go
> reconfigure with override
> go
> update sysdatabases set
> status=-32768 where dbid=DB_ID('fake')
> go
> sp_configure 'allow updates',0
> go
> reconfigure with override
> go
> This will put the database in emergency recovery mode
> 8) Stop SQL Service
> 9) Delete the fake_Log.LDF file
> 10) Restart SQL Service
> 11) In QA run the following (with correct path for log)
> dbcc rebuild_log('fake','h:\fake_log.ldf')
> go
> dbcc checkdb('fake') -- to check for errors
> go
> 12) Now we need to rename the files, run the following
> (make sure
> there are no connections to it) in Query Analyser
> (At this stage you can actually access the database so you
> could use
> DTS or bcp to move the data to another database .)
> use master
> go
> sp_helpdb 'fake'
> go
> /* Make a note of the names of the files , you will need
> them
> in the next bit of the script to replace datafilename and
> logfilename - it might be that they have the right names
> */
> sp_renamedb 'fake','PowerDVD301'
> go
> alter database PowerDVD301
> MODIFY FILE(NAME='datafilename', NEWNAME
> = 'PowerDVD301_Data')
> go
> alter database PowerDVD301
> MODIFY FILE(NAME='logfilename', NEWNAME
> = 'PowerDVD301_Log')
> go
> dbcc checkdb('PowerDVD301')
> go
> sp_dboption 'PowerDVD301','dbo use only','false'
> go
> use PowerDVD301
> go
> sp_updatestats
> go
> 13) You should now have a working database. However the
> log file
> will be small so it will be worth increasing its
> size
> Unfortunately your files will be called
> fake_Data.MDF and
> fake_Log.LDF but you can get round this by
> detaching the
> database properly and then renaming the files and
> reattaching
> it
> 14) Run the following in QA
> sp_detach_db PowerDVD301
>
> --now rename the files then reattach
> sp_attach_db 'PowerDVD301','h:\dvd.mdf','h:\DVD.ldf'
>
> Regards
> Thirumal

Deleted Transaction Logs!

I accidentalty deleted the transaction logs on a DB I had, and now I can get
it up on SQL enterprise, any ideas?
-GaryHi,
Try to attach the database with out the tranaction log using
sp_attach_single_file_db system stored procedure. This may not work since
your database is not detached.
Usage:
sp_attach_single_file_db 'dbname' , 'physical_name with the path of MDF
file'
If it fails restore the database from the last successful backup.
Thanks
Hari
MCDBA
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I accidentalty deleted the transaction logs on a DB I had, and now I can
get
> it up on SQL enterprise, any ideas?
> -Gary
>|||Thx Hari,
I have replications running, and it won't let me detach, any ideas on how to
detach this?
-Gary
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OKQbWmMHEHA.3584@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Try to attach the database with out the tranaction log using
> sp_attach_single_file_db system stored procedure. This may not work since
> your database is not detached.
> Usage:
> sp_attach_single_file_db 'dbname' , 'physical_name with the path of MDF
> file'
> If it fails restore the database from the last successful backup.
> Thanks
> Hari
> MCDBA
> "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> get
>|||is it safe to delete these files? I wont lose core data will I?
-Gary
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I accidentalty deleted the transaction logs on a DB I had, and now I can
get
> it up on SQL enterprise, any ideas?
> -Gary
>|||and if I try to disable publishing, I get errors regarding the database
whose transaction logs are deleted.
-Gary
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:uKy83nMHEHA.2924@.TK2MSFTNGP09.phx.gbl...
> Thx Hari,
> I have replications running, and it won't let me detach, any ideas on how
to
> detach this?
> -Gary
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OKQbWmMHEHA.3584@.TK2MSFTNGP09.phx.gbl...
since
MDF
can
>|||You will loose any uncomitted data and anything after the last CHECKPOINT,
do you have your recovery model full or simple? If its simple I think you
loose less.
You can also create a new database with the same name, stop sql service and
replace the data files with your original database and then start the
service again. If this didnt work set the database to emergency mode and
then try to copy objects using DTS to a new database.
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:uZUp64MHEHA.2668@.TK2MSFTNGP10.phx.gbl...
> is it safe to delete these files? I wont lose core data will I?
> -Gary
> "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> get
>|||how can I go to emergency mode?
-Gary
"Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
news:%23jMoDfNHEHA.3356@.TK2MSFTNGP11.phx.gbl...
> You will loose any uncomitted data and anything after the last CHECKPOINT,
> do you have your recovery model full or simple? If its simple I think you
> loose less.
> You can also create a new database with the same name, stop sql service
and
> replace the data files with your original database and then start the
> service again. If this didnt work set the database to emergency mode and
> then try to copy objects using DTS to a new database.
>
> "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> news:uZUp64MHEHA.2668@.TK2MSFTNGP10.phx.gbl...
can
>|||UPDATE master..sysdatabases SET status=-32768 WHERE name='<dbname>'
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:uhpeyhNHEHA.2128@.TK2MSFTNGP11.phx.gbl...
> how can I go to emergency mode?
> -Gary
> "Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
> news:%23jMoDfNHEHA.3356@.TK2MSFTNGP11.phx.gbl...
CHECKPOINT,
you
> and
> can
>|||You don't have any of the log files at all?
You're best bet is to open a call with PSS. Depending on the circumstances
PSS may be able to help you recover most of the data. There are a number of
things that you could do on your own, that are not documented. But,
honestly, the PSS call is pretty cheap and doing it on your own can be
tricky. I really think the PSS call is worth the price.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I accidentalty deleted the transaction logs on a DB I had, and now I can
get
> it up on SQL enterprise, any ideas?
> -Gary
>|||Hi Gary,
Here is a technique suggested by Jasper Smith once. I
documented it for later use.
Here's a technique I have used in the past - you need to
change the file/database names as this was for a specific
case :
If you did not detach the database prior to copying it
then you will not be able to attach it easily (probably).
First thing to try is make sure the folder
'D:\DATA2K\MSSQL$SKINNER2K\DATA\ exists,if not
then create it,copy your MDF there and retry your statement
If that doesn't work then you're in a bit of trouble but
you can try this
1) Make sure you have a copy of PowerDVD301_2_Data.MDF
2) Create a new database called fake (default file
locations)
3) Stop SQL Service
4) Delete the fake_Data.MDF and copy PowerDVD301_2_Data.MDF
to where fake_Data.MDF used to be and rename the file
to fake_Data.MDF
5) Start SQL Service
6) Database fake will appear as suspect in EM
7) Open Query Analyser and in master database run the
following :
sp_configure 'allow updates',1
go
reconfigure with override
go
update sysdatabases set
status=-32768 where dbid=DB_ID('fake')
go
sp_configure 'allow updates',0
go
reconfigure with override
go
This will put the database in emergency recovery mode
8) Stop SQL Service
9) Delete the fake_Log.LDF file
10) Restart SQL Service
11) In QA run the following (with correct path for log)
dbcc rebuild_log('fake','h:\fake_log.ldf')
go
dbcc checkdb('fake') -- to check for errors
go
12) Now we need to rename the files, run the following
(make sure
there are no connections to it) in Query Analyser
(At this stage you can actually access the database so you
could use
DTS or bcp to move the data to another database .)
use master
go
sp_helpdb 'fake'
go
/* Make a note of the names of the files , you will need
them
in the next bit of the script to replace datafilename and
logfilename - it might be that they have the right names
*/
sp_renamedb 'fake','PowerDVD301'
go
alter database PowerDVD301
MODIFY FILE(NAME='datafilename', NEWNAME
= 'PowerDVD301_Data')
go
alter database PowerDVD301
MODIFY FILE(NAME='logfilename', NEWNAME
= 'PowerDVD301_Log')
go
dbcc checkdb('PowerDVD301')
go
sp_dboption 'PowerDVD301','dbo use only','false'
go
use PowerDVD301
go
sp_updatestats
go
13) You should now have a working database. However the
log file
will be small so it will be worth increasing its
size
Unfortunately your files will be called
fake_Data.MDF and
fake_Log.LDF but you can get round this by
detaching the
database properly and then renaming the files and
reattaching
it
14) Run the following in QA
sp_detach_db PowerDVD301
--now rename the files then reattach
sp_attach_db 'PowerDVD301','h:\dvd.mdf','h:\DVD.ldf'
Regards
Thirumal

Deleted table.

Hi all,

I'm struck, i'm working on a production support by mistake i deleted a table without a transaction, is there anyway i could retrieve the records that got deleted. :(

Thanks in advance.

Arun

restore from backup to another database and copy the records over.

Good luck|||

Is there anyway by which i can retrieve it from transaction log.

|||

Possibly, if you haven't backed-up the transaction log since the deletion and if your database's recovery model is not 'Simple'.

One of these tools should be able to help:

http://www.red-gate.com/products/sql_log_rescue/index.htm

http://www.lumigent.com/products/le_sql.html

For future reference, it's a good idea to execute BEGIN TRANSACTION before making manual corrections to Production data - that way you can always ROLLBACK if your code doesn't behave as expected.

Chris

|||If you can get it from a backup, I would highly recommend that. I know it stinks, but it will be the most straightforward (and the most supported :)

Deleted logfile

Hi,
I have detach my sql database and deleted the transaction logfile.
Now i cannot attach the database bak to SQL.
The logfile is completly gone.
How can i reattach this database (don't want to use the backup)
Can u help me
regards
Steven
Hi
Look at sp_attach_single_file_db stored procedure.
For more details please refer to BOL
"Tjina" <stjinaton@.nagico.com> wrote in message
news:OPJz1f2uEHA.612@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have detach my sql database and deleted the transaction logfile.
> Now i cannot attach the database bak to SQL.
> The logfile is completly gone.
> How can i reattach this database (don't want to use the backup)
> Can u help me
> regards
> Steven
>
|||As far as I know with SQL2K you can re-attach the database without the log
file. SQL Server should notify you that the log file was not found and will
ask you if you want a new log file to be created.
Sasan Saidi, MSc in CS
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Tjina" wrote:

> Hi,
> I have detach my sql database and deleted the transaction logfile.
> Now i cannot attach the database bak to SQL.
> The logfile is completly gone.
> How can i reattach this database (don't want to use the backup)
> Can u help me
> regards
> Steven
>
>
|||Have you tried using sp_attach_single_file_db? You can find more
information on using this in books online.
-Sue
On Tue, 26 Oct 2004 10:27:41 -0400, "Tjina" <stjinaton@.nagico.com>
wrote:

>Hi,
>I have detach my sql database and deleted the transaction logfile.
>Now i cannot attach the database bak to SQL.
>The logfile is completly gone.
>How can i reattach this database (don't want to use the backup)
>Can u help me
>regards
>Steven
>
|||> As far as I know with SQL2K you can re-attach the database without the log
> file.
Not in all cases...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:942597C9-6CED-4A33-8A27-9F73475A4D0C@.microsoft.com...[vbcol=seagreen]
> As far as I know with SQL2K you can re-attach the database without the log
> file. SQL Server should notify you that the log file was not found and will
> ask you if you want a new log file to be created.
> --
> Sasan Saidi, MSc in CS
> "I saw it work in a cartoon once so I am pretty sure I can do it."
>
> "Tjina" wrote:

Deleted logfile

Hi,
I have detach my sql database and deleted the transaction logfile.
Now i cannot attach the database bak to SQL.
The logfile is completly gone.
How can i reattach this database (don't want to use the backup)
Can u help me
regards
StevenHi
Look at sp_attach_single_file_db stored procedure.
For more details please refer to BOL
"Tjina" <stjinaton@.nagico.com> wrote in message
news:OPJz1f2uEHA.612@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have detach my sql database and deleted the transaction logfile.
> Now i cannot attach the database bak to SQL.
> The logfile is completly gone.
> How can i reattach this database (don't want to use the backup)
> Can u help me
> regards
> Steven
>|||As far as I know with SQL2K you can re-attach the database without the log
file. SQL Server should notify you that the log file was not found and will
ask you if you want a new log file to be created.
--
Sasan Saidi, MSc in CS
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Tjina" wrote:
> Hi,
> I have detach my sql database and deleted the transaction logfile.
> Now i cannot attach the database bak to SQL.
> The logfile is completly gone.
> How can i reattach this database (don't want to use the backup)
> Can u help me
> regards
> Steven
>
>|||Have you tried using sp_attach_single_file_db? You can find more
information on using this in books online.
-Sue
On Tue, 26 Oct 2004 10:27:41 -0400, "Tjina" <stjinaton@.nagico.com>
wrote:
>Hi,
>I have detach my sql database and deleted the transaction logfile.
>Now i cannot attach the database bak to SQL.
>The logfile is completly gone.
>How can i reattach this database (don't want to use the backup)
>Can u help me
>regards
>Steven
>|||> As far as I know with SQL2K you can re-attach the database without the log
> file.
Not in all cases...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:942597C9-6CED-4A33-8A27-9F73475A4D0C@.microsoft.com...
> As far as I know with SQL2K you can re-attach the database without the log
> file. SQL Server should notify you that the log file was not found and will
> ask you if you want a new log file to be created.
> --
> Sasan Saidi, MSc in CS
> "I saw it work in a cartoon once so I am pretty sure I can do it."
>
> "Tjina" wrote:
>> Hi,
>> I have detach my sql database and deleted the transaction logfile.
>> Now i cannot attach the database bak to SQL.
>> The logfile is completly gone.
>> How can i reattach this database (don't want to use the backup)
>> Can u help me
>> regards
>> Steven
>>

Deleted logfile

Hi,
I have detach my sql database and deleted the transaction logfile.
Now i cannot attach the database bak to SQL.
The logfile is completly gone.
How can i reattach this database (don't want to use the backup)
Can u help me
regards
StevenHi
Look at sp_attach_single_file_db stored procedure.
For more details please refer to BOL
"Tjina" <stjinaton@.nagico.com> wrote in message
news:OPJz1f2uEHA.612@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have detach my sql database and deleted the transaction logfile.
> Now i cannot attach the database bak to SQL.
> The logfile is completly gone.
> How can i reattach this database (don't want to use the backup)
> Can u help me
> regards
> Steven
>|||As far as I know with SQL2K you can re-attach the database without the log
file. SQL Server should notify you that the log file was not found and will
ask you if you want a new log file to be created.
Sasan Saidi, MSc in CS
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Tjina" wrote:

> Hi,
> I have detach my sql database and deleted the transaction logfile.
> Now i cannot attach the database bak to SQL.
> The logfile is completly gone.
> How can i reattach this database (don't want to use the backup)
> Can u help me
> regards
> Steven
>
>|||Have you tried using sp_attach_single_file_db? You can find more
information on using this in books online.
-Sue
On Tue, 26 Oct 2004 10:27:41 -0400, "Tjina" <stjinaton@.nagico.com>
wrote:

>Hi,
>I have detach my sql database and deleted the transaction logfile.
>Now i cannot attach the database bak to SQL.
>The logfile is completly gone.
>How can i reattach this database (don't want to use the backup)
>Can u help me
>regards
>Steven
>|||> As far as I know with SQL2K you can re-attach the database without the log
> file.
Not in all cases...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:942597C9-6CED-4A33-8A27-9F73475A4D0C@.microsoft.com...[vbcol=seagreen]
> As far as I know with SQL2K you can re-attach the database without the log
> file. SQL Server should notify you that the log file was not found and wil
l
> ask you if you want a new log file to be created.
> --
> Sasan Saidi, MSc in CS
> "I saw it work in a cartoon once so I am pretty sure I can do it."
>
> "Tjina" wrote:
>

Deleted "old" transaction logs = (no items) in EM. How to recover?

A mistake was made and "old" transaction logs (and old backups) were
deleted. It appears that a long living transaction was living in one of
those logs. Now the EP shows (no items). The DBs themselves are still
functioning OK, just the EP shell that's not working.

What's the proper resolution?

Thank you in advance,

FBCK(optimistck@.gmail.com) writes:
> A mistake was made and "old" transaction logs (and old backups) were
> deleted. It appears that a long living transaction was living in one of
> those logs. Now the EP shows (no items). The DBs themselves are still
> functioning OK, just the EP shell that's not working.
> What's the proper resolution?

EP?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

delete/move transaction log

Hi all,

I'm an old Oracle dba that had to convert DB religion :-)

Quick question for you gurus.

If i have a disk that is starting to break down, but still working, and that disk has the Transaction log for the SQL2000 DB, is there an easy, quick way to move the transaction log to a different disk while we repair the disk ? As little downtime as possible is what i'm looking for.

Or do i have to ...
1. Full DB backup
2. Fix the disk
3. Full DB restore since the transactionlog is missing.

I have looked thru the SQL200 Admin Companion but i really miss i small whitepaper giving some general procedures what to do if you loose transction log disk, datafile disk etc etc.

Anyone have any good whitepapers or know where it can be downloaded ?

Hope this question isn't to trivial for you experts or that it has been asked before, i did search first.

Thanks.Hey there,

What you should be able to do is:

1. Shutdown applications and users accessing the database.
2. Detach the database and transaction log.
3. Copy the transaction log file(s) (.LDF) to your new drive.
4. ReAttach DB and log (with new location)
5. Allow users etc back in & fix disk.
6. Once disk fixed repeat process.

The longest part will be the file copy.. There is information in Books Online to help you. You can either use Enterprise manager or T-SQL.

Hope this helps.|||Thanks mate, sounds like good way to do it.

I'll have a look at the books online once more.

Cheers|||No worries,

The stored procs you will require should you choose T-SQL are: sp_attach_db & sp_detach_db. Search for info on these and you should find some info to help you..

Cheers|||This can also be done with no downtime...provided you have diskspace ;-).

1) Create a brand new transaction log on a good disk.
2) Run DBCC SHRINKFILE ((Old logfile id), EMPTYFILE)
This last command marks the old logfile as "not to be used anymore"
3) Backup any transactions that may be in the bad file.
4) Drop the bad logfile.

Good luck.

Delete Without Writing To Transaction Log

I am using a simple
DELETE
FROM tblMyTable
I was told the reason it takes 4.5 hours is because of writing to the
transaction log.
Can I add something to this statement to bypass the transaction log writing?
Thanks so much in advance.Truncate table tbl_MyTable.
Perayu

>I am using a simple
> DELETE
> FROM tblMyTable
> I was told the reason it takes 4.5 hours is because of writing to the
> transaction log.
> Can I add something to this statement to bypass the transaction log
> writing?
> Thanks so much in advance.|||Thanks for your help.
It won't let me do that.
Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'tblMyTable' because it is being referenced
by a FOREIGN KEY constraint.
"Perayu" wrote:

> Truncate table tbl_MyTable.
> Perayu
>
>
>|||How is your foreign key constaint set up? Do you want to delete from child
table as well? If so, drop the contraint first, truncate parent table. Delet
e
from child table by joining to parent table using Right OUTER JOIN clause.
Recreate the constraint.
Otherwise, spend 5 hours in wait until the transaction is fully committed.
Nitin
"alison" wrote:
> Thanks for your help.
> It won't let me do that.
> Server: Msg 4712, Level 16, State 1, Line 1
> Cannot truncate table 'tblMyTable' because it is being referenced
> by a FOREIGN KEY constraint.
> "Perayu" wrote:
>|||No, you have to write to the log? Who told you it takes 4.5 hours because
of this? Do you have evidence of this, or is someone just pulling a guess
out of their...back pocket :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"alison" <alison@.discussions.microsoft.com> wrote in message
news:5A0D2986-E67D-4C09-AE9B-90BA5D77EE4B@.microsoft.com...
>I am using a simple
> DELETE
> FROM tblMyTable
> I was told the reason it takes 4.5 hours is because of writing to the
> transaction log.
> Can I add something to this statement to bypass the transaction log
> writing?
> Thanks so much in advance.|||no, but you can use truncate table, which minimizes logging.
"alison" <alison@.discussions.microsoft.com> wrote in message
news:5A0D2986-E67D-4C09-AE9B-90BA5D77EE4B@.microsoft.com...
> I am using a simple
> DELETE
> FROM tblMyTable
> I was told the reason it takes 4.5 hours is because of writing to the
> transaction log.
> Can I add something to this statement to bypass the transaction log
writing?
> Thanks so much in advance.|||I'm sorry if I'm being a pain. I'm new at this.
Yesterday I did a simple DELETE statement before I left the office.
When I came in this morning I saw that it ran for 4.5 hours. The conversion
on this table takes even longer.
I don't need this table to develop the reports, but it's in the conversion
.cmd script and I was told not to DROP it.
I do really appreciate everyone who's helped me so far. We're still throwing
ideas around here too.
"Louis Davidson" wrote:

> No, you have to write to the log? Who told you it takes 4.5 hours because
> of this? Do you have evidence of this, or is someone just pulling a guess
> out of their...back pocket :)
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "alison" <alison@.discussions.microsoft.com> wrote in message
> news:5A0D2986-E67D-4C09-AE9B-90BA5D77EE4B@.microsoft.com...
>
>|||Hi
Truncate can only minimize logging, contrary to popular myth that It avoids.
Still if you face problem with delete you can probably do one of the followi
ng
1)PIN TABLE TO MEMORY, If you have enough memory
2)SPREAD TABLE TO different file groups to achive maximum cpu optimality.
Regards
R.D
"Brian Selzer" wrote:

> no, but you can use truncate table, which minimizes logging.
> "alison" <alison@.discussions.microsoft.com> wrote in message
> news:5A0D2986-E67D-4C09-AE9B-90BA5D77EE4B@.microsoft.com...
> writing?
>
>|||I imagine if it took four hours that pinning the table to memory is probably
not the best idea unless they have all the data, logs, os, mp3s, wmvs, etc
on 1 500 GB drive :)
The second one is a good possibility, but not the first place to go.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:408CFD0E-7B28-456D-B6CF-0140F80495AD@.microsoft.com...
> Hi
> Truncate can only minimize logging, contrary to popular myth that It
> avoids.
> Still if you face problem with delete you can probably do one of the
> following
> 1)PIN TABLE TO MEMORY, If you have enough memory
> 2)SPREAD TABLE TO different file groups to achive maximum cpu optimality.
> Regards
> R.D
> "Brian Selzer" wrote:
>|||Is this a standalone table? No indexes, no keys, no triggers, etc? If so,
then use TRUNCATE TABLE for sure.
Either way, open up perfmon and watch the Disk Queue items (amongst others),
and see if you have a lot of queuing, and check CPU, etc. You may have
really slow disk performance. It is hard to tell without doing more
research.
But if TRUNCATE works, then this is the best idea. For loading the data,
consider dropping any indexes if no one is using the table during thr
conversion, building the indexes once is cheaper than maintaining them
during a large load.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"alison" <alison@.discussions.microsoft.com> wrote in message
news:77DD37EE-C6BC-4E0E-A28F-21524ED8429E@.microsoft.com...
> I'm sorry if I'm being a pain. I'm new at this.
> Yesterday I did a simple DELETE statement before I left the office.
> When I came in this morning I saw that it ran for 4.5 hours. The
> conversion
> on this table takes even longer.
> I don't need this table to develop the reports, but it's in the conversion
> .cmd script and I was told not to DROP it.
> I do really appreciate everyone who's helped me so far. We're still
> throwing
> ideas around here too.
> "Louis Davidson" wrote:
>

DELETE without transaction?

Hi,
I have a database with about 50 GB - most of the data in one single table
with only three fields. I need to clean up this table frequently (executing
DELETE stantements). My problem is that this locks the table so that BULK
INSERT jobs cannot execute. Also the transaction lok fills up a lot (15 GB
and more) while deleting the data.
Is there a way to delete without using the transaction log and while doing a
bulk insert?
CU,
SvenHi,
I think TRUNCATE TABLE is what you are looking for.
I'm not sure you can do anything else while truncating, but
truncate is very fast.
Check BOL for further information.
Ciao,
Cosmin.
"Sven Erik Matzen" <sven.matzen@.dontspamme.com> wrote in message
news:editI8xYDHA.1004@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a database with about 50 GB - most of the data in one single table
> with only three fields. I need to clean up this table frequently
(executing
> DELETE stantements). My problem is that this locks the table so that BULK
> INSERT jobs cannot execute. Also the transaction lok fills up a lot (15 GB
> and more) while deleting the data.
> Is there a way to delete without using the transaction log and while doing
a
> bulk insert?
> CU,
> Sven
>|||truncate table is a good way of cleaning out the entire
table, assuming there are no foreign key constraints
for deleting large number of rows, try doing it in small
batches, say 1000-10,000 rows at a time, adjust the row
count to keep the batch run time sufficiently short
>--Original Message--
>Hi,
>I have a database with about 50 GB - most of the data in
one single table
>with only three fields. I need to clean up this table
frequently (executing
>DELETE stantements). My problem is that this locks the
table so that BULK
>INSERT jobs cannot execute. Also the transaction lok
fills up a lot (15 GB
>and more) while deleting the data.
>Is there a way to delete without using the transaction
log and while doing a
>bulk insert?
>CU,
>Sven
>
>.
>|||The problem is that I need a where statement - this is not supported by
TRUNCATE TABLE (that's one reason, why it's so fast).
"Cosmin" <cosmin.onea@.infoworld.ro> wrote in message
news:O1NbTJyYDHA.2476@.tk2msftngp13.phx.gbl...
> Hi,
> I think TRUNCATE TABLE is what you are looking for.
> I'm not sure you can do anything else while truncating, but
> truncate is very fast.
> Check BOL for further information.
> Ciao,
> Cosmin.
> "Sven Erik Matzen" <sven.matzen@.dontspamme.com> wrote in message
> news:editI8xYDHA.1004@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > I have a database with about 50 GB - most of the data in one single
table
> > with only three fields. I need to clean up this table frequently
> (executing
> > DELETE stantements). My problem is that this locks the table so that
BULK
> > INSERT jobs cannot execute. Also the transaction lok fills up a lot (15
GB
> > and more) while deleting the data.
> >
> > Is there a way to delete without using the transaction log and while
doing
> a
> > bulk insert?
> >
> > CU,
> > Sven
> >
> >
>

DELETE transaction with SNAPSHOT isolation level - conflicts another table

Hi,

we are executing the following query in a stored procedure using snapshot isolation level:

DELETE FROM tBackgroundProcessProgressReport
FROM tBackgroundProcessProgressReport LEFT OUTER JOIN
tBackgroundProcess ON
tBackgroundProcess.BackgroundProcessProgressReportID =tBackgroundProcessProgressReport.BackgroundProcessProgressReportID LEFTOUTER JOIN
tBackgroundProcessProgressReportItem ON
tBackgroundProcessProgressReport.BackgroundProcessProgressReportID =tBackgroundProcessProgressReportItem.BackgroundProcessProgressReportID
WHERE (tBackgroundProcess.BackgroundProcessID IS NULL) AND
(tBackgroundProcessProgressReportItem.BackgroundProcessProgressReportItemID IS NULL)

The query should delete records from tBackgroundProcessProgressReport which are not connected with the other two tables.
However, for some reasone we get the following exception:

System.Data.SqlClient.SqlException:Snapshot isolation transaction aborted due to update conflict. Youcannot use snapshot isolation to access table 'dbo.tBackgroundProcess'directly or indirectly in database 'RHSS_PRD_PT_Engine' to update,delete, or insert the row that has been modified or deleted by anothertransaction. Retry the transaction or change the isolation level forthe update/delete statement.

The exception specifies that we arenot allowed to update/delete/insert records in tBackgroundProcess, butthe query indeed deletes records from tBackgroundProcessProgressReport,not from the table in the exception.
Is the exception raised because of the join?

Has someone encountered this issue before?

Thanks,

Yani

Hi,

it looks like this forum is not the best place to ask, since it's dedicated to asp.net

So anybody with idea where i could ask for a solution for my problem?

Thanks in advance!

Delete Transaction Log

Hi all,

I am using MSSQL Server 2000. I have my SQL Server Database: mydb.mdf (1 GB) and mydb_log.ldf (30 GB).
Now I would like to delete this VERY BIG transaction log and let the SQL Server automatic create a new one. What is the best way to do that? System downtime is available. Here is my way when I tested on development machine:
1) Backup full database and transaction log.
2) Detach DB by using Enterprise Manager
3) Delete transaction log (mydb_log.ldf file) by using Windows Explorer.
4) Attach DB.
Then I saw SQL Server automatic created a new Transaction log (mydb_log.ldf) only 1 MB in size.

Before I apply on Production, I would like to ask for your idea? Any warning? If possible do you know where I can find any official articles of Microsoft about deleting Transaction log?
I appreciate for all help.
ThanksHave you tried dbcc shrinkfile / dbcc shrinkdatabase ? Have a look at the following article:

article (http://support.microsoft.com/default.aspx?scid=kb;EN-US;q272318)

If all else fails, then your solution is the quickest.

Good luck.|||On deleting Transaction logs:

Re: Before I apply on Production, I would like to ask for your idea? Any warning? If possible do you know where I can find any official articles of Microsoft about deleting Transaction log?
I appreciate for all help

Q1 Before I apply on Production, I would like to ask for your idea?
A1 Why not use DBCC ShrinkFile, and / or enable autoshrink? Also, why is your Log so much larger than your Data e.g., (i Are you not frequently dumping / backing up your Log to transaction log backup *.trn files? ; ii Or, do you have long running transactions that are filling up your DB Log; iii Or, is your DB very heavily used, etc.?)?

DBCC ShrinkFile advantages:
* it is safe
* it may be safely used even if your DB has multiple log files (add several additional log files to your DB, then rigorously test your method)
* ordinary users may work in the DB while its files are being shrunk

Use MyDB
Go
DBCC ShrinkFile ([MyDB_Log], 1, TruncateOnly)
Go

Q2 Any warning?
A2 You may "get away with" using your method indefinitly; however it is not safe.

Q3 If possible do you know where I can find any official articles of Microsoft about deleting Transaction log?
A3 BOL = Books On Line. You may install BOL using the Sql Server installer, it covers various recommended DBMS methods for reducing / limiting transaction log file sizes e.g., (including DBCC ShrinkFile, DBCC ShrinkDatabase). Microsoft Technet is another source of documentation that may help if / when your method gets you into trouble.|||Many thanks for all your help.
First of all, I just received new role as a DBA. Then I found my DB is not monitored, dumped/backed up to *TRN for a long time.
Why I do not want to use SHRINKFile? It seems to me DBCC SHRINKFILE does not work well, the physical size not reduce much as I expected! So I would like to "delete" and create a new log file.

Regards,
John|||Bill,
Below is a reply I made to a post from a month or so ago. Think it will help you out.

A nice feature that was added with SQL 2000 is that you now need to backup the database using the SQL Backup utility in order for the transaction log to shrink. I think that many of us were truncating the log at checkpoint in 7.0 and using a third party backup. We looked at a few things here and ended up setting up a backup device and then doing a complete backup once a day and appending a diff backup every three hours. Seems to do a good job on keeping the log at a reasonable size.

Hope this helps.

Brent|||JohnBill,
Below is a reply I made to a post from a month or so ago. Think it will help you out.

A nice feature that was added with SQL 2000 is that you now need to backup the database using the SQL Backup utility in order for the transaction log to shrink. I think that many of us were truncating the log at checkpoint in 7.0 and using a third party backup. We looked at a few things here and ended up setting up a backup device and then doing a complete backup once a day and appending a diff backup every three hours. Seems to do a good job on keeping the log at a reasonable size.

Hope this helps.

Brent