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 records that get too old

I must admit I dont know all that much about SQL, which is why I hope someone can show me the light. I have a script almost finished, however I have no idea how to have it trim database entries that are older than, say, 90 days. Any ideas?WHERE date_file > DATEADD(dd,90,GETDATE())|||"Stay away from the light!" [Donkey from "Shrek"]|||"Stay away from the light!" [Donkey from "Shrek"]

Hey, I thought it was my job to handle the disconnected ramblings....

And btw, you do have a row added datetime column, right?

Of course you do.

(hey buddy, wanna buy a watch...cheap)|||Disconneted ramblings...

...Brett's job. Not yours.

...or rdjabarovs...

...<insert random Maragarita reference here>...

[sniped!]

...or connect the ramblings with a join, but not in WHERE clause.

WHERE disconnected_ramblings = ...

Not good.

Is it Friday yet?|||I'm confused.

Huh?

I think I lost my =|||Kansas?

There are only 2 things that come from Kansas boy.....

Steers...ok never mind

BUT, can you tell me the movie?

Did we scare him/her off...

And what about DB2

Damn...gotta run to THE city...

later|||Brett, someday some company will create a programming language that will allow you code in stream-of-consciousness, and you will be a happy man.|||WHERE date_file > DATEADD(dd,90,GETDATE())Wait a minute, shouldn't it be "WHERE date_file < DATEADD(dd,-90,GETDATE())"?|||Now WHERE did I put that backup...?|||Brett, someday some company will create a programming language that will allow you code in stream-of-consciousness, and you will be a happy man.

They did...it's called REXX

I love the INTERPRET keyword most of all scarecrow...

Deleting old unused database issue

I have an old DATABASE that is not more in use , so I want to drop that database.
But before that I want to check that is there any application running on it or not.
can any one suggest me how to do this?
thanksSimplest is execute sp_who2. That will tell you if there are any current connections. If you need something more bullet proof then you will need to audit connections over a time period. Or you could try a scream test if it is not critical - which presumably it is not since you are contemplating dropping it.|||Use sql profiler and let it dump the results to a table and query that

Most of my servers are dedicated to a single database though

Just make sure you have a current dump

Now a soapbox issue

I like to have sproc only access to my databases, and each sproc has sproc logging, so I know everything that's going on|||...Or you could try a scream test...One of my phrases has entered the public lexicon!|||There'll be scads of them soon I'm sure.|||The "scream test" is one of my best, but the "Boxers vs Briefs solution" is more colorful.sql

Deleting old servers still listed in Query Analyzer ????????

I can use the Client Network Utility (CNU) from SQL 2000 to create/delete
aliases.
But when I 1st run Query Analyzer (QA)... it shows a list of some old
servers we used to have... long ago.
How do I delete from that list?
(The old servers have already been removed from the CNU alias list... but
they seem to stay listed in QA forever.)
They have nothing to do with each other. Any registrations you did in QA or
EM you will have to manually delete them from the registrations by right
clicking and choosing Delete.
Andrew J. Kelly SQL MVP
""A_Michigan_User"" <Michigan_RE_M0VE@.ameritech.net> wrote in message
news:%23gKQ7lwOGHA.3944@.tk2msftngp13.phx.gbl...
>I can use the Client Network Utility (CNU) from SQL 2000 to create/delete
>aliases.
> But when I 1st run Query Analyzer (QA)... it shows a list of some old
> servers we used to have... long ago.
> How do I delete from that list?
> (The old servers have already been removed from the CNU alias list... but
> they seem to stay listed in QA forever.)
>
>
|||"A_Michigan_User" wrote:
> I can use the Client Network Utility (CNU) from SQL 2000 to
> create/delete aliases.
> But when I 1st run Query Analyzer (QA)... it shows a list of some old
> servers we used to have... long ago.
> How do I delete from that list?
> (The old servers have already been removed from the CNU alias list...
> but they seem to stay listed in QA forever.)
For QA, the servers are stored here in the registration database:
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
Server\80\Tools\Client\PrefServers
You can remove the pairs you do not need - carefully. I don't know how
to remove servers from the QA drop-down list in the connection dialog
other than by editing the regdb. I see different servers listed in QA
than I have for SQLEM, so removing the server from SQLEM does seem like
it will work in QA.
David Gugick - SQL Server MVP
Quest Software
|||> They have nothing to do with each other.
I thought I *HAD TO* add them with CNU... then they appeared as "available"
in QA and EM.
No?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eP%237NMyOGHA.1312@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> They have nothing to do with each other. Any registrations you did in QA
> or EM you will have to manually delete them from the registrations by
> right clicking and choosing Delete.
> --
> Andrew J. Kelly SQL MVP
>
> ""A_Michigan_User"" <Michigan_RE_M0VE@.ameritech.net> wrote in message
> news:%23gKQ7lwOGHA.3944@.tk2msftngp13.phx.gbl...
|||The CNU just allowed you to alias them. Then maybe you can see them thru
the alias but that is not a requirement to register a server. You just need
the name or IP address of the server and it has to be available on the right
port, 1433 by default.
Andrew J. Kelly SQL MVP
""A_Michigan_User"" <Michigan_RE_M0VE@.ameritech.net> wrote in message
news:eqFg7jlRGHA.2156@.tk2msftngp13.phx.gbl...
> I thought I *HAD TO* add them with CNU... then they appeared as
> "available" in QA and EM.
> No?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eP%237NMyOGHA.1312@.TK2MSFTNGP09.phx.gbl...
>

deleting old cursors

Hello. How can I delete an old cursor which appears when I execute the statement

select * from sys.dm_exec_cursors(0) ?

Thanks.

KILL session_id

...or alternatively if you can issue commands against the connection reported in the session_id column (for instance if the problem is with a SSMS connection that you have open) then you can issue:

CLOSE <cursor name>

DEALLOCATE <cursor name>

Chris

|||Thanks a lot.|||

Just to add that the first example I gave was really an 'emergency' measure. It would be advisable to track and fix the cause of the problem.

Chris

|||That was exactly what I was looking for, an emergency measure. I couldn't use close and deallocate. I forgot to mention that I am using SQL SERVER 2005. In a stored procedure I commented out a block together with close and deallocate for the respective cursor. The weird thing is that the procedure has only the declaration/definition of the cursor, otherwise there is no reference to it. I realized that it still exists when I received the error "cursor ... already exists". Thanks again for your answer.

deleting old cursors

Hello. How can I delete an old cursor which appears when I execute the statement

select * from sys.dm_exec_cursors(0) ?

Thanks.

KILL session_id

...or alternatively if you can issue commands against the connection reported in the session_id column (for instance if the problem is with a SSMS connection that you have open) then you can issue:

CLOSE <cursor name>

DEALLOCATE <cursor name>

Chris

|||Thanks a lot.|||

Just to add that the first example I gave was really an 'emergency' measure. It would be advisable to track and fix the cause of the problem.

Chris

|||That was exactly what I was looking for, an emergency measure. I couldn't use close and deallocate. I forgot to mention that I am using SQL SERVER 2005. In a stored procedure I commented out a block together with close and deallocate for the respective cursor. The weird thing is that the procedure has only the declaration/definition of the cursor, otherwise there is no reference to it. I realized that it still exists when I received the error "cursor ... already exists". Thanks again for your answer.

deleting old backups/trn files.

i have a maintenance plan running on my database, in which I told the wizard, on creation, to "remove files older than 4 week" and yet it doesn't seem to be doing so, as on checking this morning, diskspace was getting low, due to over 300gb of backups and trn' dating back to september.

Anyone have ny problems with maintenance plans not cleaning up when told?

ano because I do not use them. I create my own jobs.|||Maintenance plans are nothing but wizards that create SQL Agent jobs. If the job is modified, the maintenance plan has no clue that anything has changed, and if the maintenance plan is re-edited it will overwrite any other changes made to the jobs.
Maintenance plans are top candidates for the most confusing and misleading functionality within SQL server. I avoid them, except as a means of defining groups of databases for administrative purposes.
Open up the job in SQL Agent and check the code that is being run. It should be something like "EXEC xp_sqlmaint '-PlanID 02A52657-D546-11D1-9D8A-00A0C9054212...".
Post it here.|||here you go...

EXECUTE master.dbo.xp_sqlmaint N'-PlanID 2A998DBD-3F5D-4685-ACCA-70354638C8C5 -Rpt "D:\mssql\REPORTS\live DB Maintenance4.txt" -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRepair -BkUpMedia DISK -BkUpDB "D:\mssql\BACKUP" -DelBkUps 4WEEKS -CrBkSubDir -BkExt "BAK"'|||There is an additional parameter for specifying deletion of reports, and for some reason this parameter is left out of the sql_maint documentation in Books Online. I am thinking it is "-DelRpts 4WEEKS", but I am not sure. I will look it up for you once I get back to my office.

Deleting old backups

Hi,
I'd like to set up a job SQL Server agent which will delete backup files
older than several months once a backup has succeeded. How can I write a
step to do this?
Bascially, it will be: if *.BAK > dateadd(m, -2, getdate()) then delete
(them)
As you see I have no idea as to where I begin with this. Can it be done?
Thanks very much for any ideas on this
Ant
On Mar 9, 2:11 am, Ant <A...@.discussions.microsoft.com> wrote:
> Hi,
> I'd like to set up a job SQL Server agent which will delete backup files
> older than several months once a backup has succeeded. How can I write a
> step to do this?
> Bascially, it will be: if *.BAK > dateadd(m, -2, getdate()) then delete
> (them)
> As you see I have no idea as to where I begin with this. Can it be done?
> Thanks very much for any ideas on this
> Ant
http://realsqlguy.blogspot.com/2007/02/cleaning-up-old-files.html
sql

Deleting old backups

Hi,
I'd like to set up a job SQL Server agent which will delete backup files
older than several months once a backup has succeeded. How can I write a
step to do this?
Bascially, it will be: if *.BAK > dateadd(m, -2, getdate()) then delete
(them)
As you see I have no idea as to where I begin with this. Can it be done?
Thanks very much for any ideas on this
AntOn Mar 9, 2:11 am, Ant <A...@.discussions.microsoft.com> wrote:
> Hi,
> I'd like to set up a job SQL Server agent which will delete backup files
> older than several months once a backup has succeeded. How can I write a
> step to do this?
> Bascially, it will be: if *.BAK > dateadd(m, -2, getdate()) then delete
> (them)
> As you see I have no idea as to where I begin with this. Can it be done?
> Thanks very much for any ideas on this
> Ant
http://realsqlguy.blogspot.com/2007...-old-files.html

Deleting old backups

Hi,
I'd like to set up a job SQL Server agent which will delete backup files
older than several months once a backup has succeeded. How can I write a
step to do this?
Bascially, it will be: if *.BAK > dateadd(m, -2, getdate()) then delete
(them)
As you see I have no idea as to where I begin with this. Can it be done?
Thanks very much for any ideas on this
AntOn Mar 9, 2:11 am, Ant <A...@.discussions.microsoft.com> wrote:
> Hi,
> I'd like to set up a job SQL Server agent which will delete backup files
> older than several months once a backup has succeeded. How can I write a
> step to do this?
> Bascially, it will be: if *.BAK > dateadd(m, -2, getdate()) then delete
> (them)
> As you see I have no idea as to where I begin with this. Can it be done?
> Thanks very much for any ideas on this
> Ant
http://realsqlguy.blogspot.com/2007/02/cleaning-up-old-files.html

Deleting Old .BAK file Problem

Hello,
My Backup Maintenance Plan is scheduled to delete old
backup (.BAK) files but for some odd reason it cannot
delete two old files. When i go in to delete them manually
or check the security settings ,but give me an access
denied prompt. I am logged in as Administrator w/ full
rights, but only these files are giving me denied access.
Please help
Thanks in advance
ATHi,
Stop SQL Agent service and try deleting the file.
Thanks
Hari
MCDBA
"AT" <anonymous@.discussions.microsoft.com> wrote in message
news:dfa701c3f0a2$5230bd80$a301280a@.phx.gbl...
> Hello,
> My Backup Maintenance Plan is scheduled to delete old
> backup (.BAK) files but for some odd reason it cannot
> delete two old files. When i go in to delete them manually
> or check the security settings ,but give me an access
> denied prompt. I am logged in as Administrator w/ full
> rights, but only these files are giving me denied access.
> Please help
> Thanks in advance
> AT|||thanks hari, are there any contingencies incase this does
net work?
thanks
AT
>--Original Message--
>Hi,
>Stop SQL Agent service and try deleting the file.
>Thanks
>Hari
>MCDBA
>"AT" <anonymous@.discussions.microsoft.com> wrote in
message
>news:dfa701c3f0a2$5230bd80$a301280a@.phx.gbl...
>> Hello,
>> My Backup Maintenance Plan is scheduled to delete old
>> backup (.BAK) files but for some odd reason it cannot
>> delete two old files. When i go in to delete them
manually
>> or check the security settings ,but give me an access
>> denied prompt. I am logged in as Administrator w/ full
>> rights, but only these files are giving me denied
access.
>> Please help
>> Thanks in advance
>> AT
>
>.
>|||Hi,
Incase if this fail, check any other process uses this file. Mostly it will
be some backup processes (tape / disk copy).
Thanks
Hari
MCDBA
"AT" <anonymous@.discussions.microsoft.com> wrote in message
news:eb1101c3f0a9$58c30160$a601280a@.phx.gbl...
> thanks hari, are there any contingencies incase this does
> net work?
> thanks
> AT
> >--Original Message--
> >Hi,
> >
> >Stop SQL Agent service and try deleting the file.
> >
> >Thanks
> >Hari
> >MCDBA
> >
> >"AT" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:dfa701c3f0a2$5230bd80$a301280a@.phx.gbl...
> >> Hello,
> >>
> >> My Backup Maintenance Plan is scheduled to delete old
> >> backup (.BAK) files but for some odd reason it cannot
> >> delete two old files. When i go in to delete them
> manually
> >> or check the security settings ,but give me an access
> >> denied prompt. I am logged in as Administrator w/ full
> >> rights, but only these files are giving me denied
> access.
> >> Please help
> >>
> >> Thanks in advance
> >>
> >> AT
> >
> >
> >.
> >|||Hey thanks Hari for the replies...
But I was able to resolve the problem by doing a simple
sys reboot..
thanks again for your help..
AT
>--Original Message--
>Hi,
>Incase if this fail, check any other process uses this
file. Mostly it will
>be some backup processes (tape / disk copy).
>Thanks
>Hari
>MCDBA
>"AT" <anonymous@.discussions.microsoft.com> wrote in
message
>news:eb1101c3f0a9$58c30160$a601280a@.phx.gbl...
>> thanks hari, are there any contingencies incase this
does
>> net work?
>> thanks
>> AT
>> >--Original Message--
>> >Hi,
>> >
>> >Stop SQL Agent service and try deleting the file.
>> >
>> >Thanks
>> >Hari
>> >MCDBA
>> >
>> >"AT" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:dfa701c3f0a2$5230bd80$a301280a@.phx.gbl...
>> >> Hello,
>> >>
>> >> My Backup Maintenance Plan is scheduled to delete old
>> >> backup (.BAK) files but for some odd reason it cannot
>> >> delete two old files. When i go in to delete them
>> manually
>> >> or check the security settings ,but give me an access
>> >> denied prompt. I am logged in as Administrator w/
full
>> >> rights, but only these files are giving me denied
>> access.
>> >> Please help
>> >>
>> >> Thanks in advance
>> >>
>> >> AT
>> >
>> >
>> >.
>> >
>
>.
>

Deleting Old .BAK file Problem

Hello,
My Backup Maintenance Plan is scheduled to delete old
backup (.BAK) files but for some odd reason it cannot
delete two old files. When i go in to delete them manually
or check the security settings ,but give me an access
denied prompt. I am logged in as Administrator w/ full
rights, but only these files are giving me denied access.
Please help
Thanks in advance
ATHi,
Stop SQL Agent service and try deleting the file.
Thanks
Hari
MCDBA
"AT" <anonymous@.discussions.microsoft.com> wrote in message
news:dfa701c3f0a2$5230bd80$a301280a@.phx.gbl...
> Hello,
> My Backup Maintenance Plan is scheduled to delete old
> backup (.BAK) files but for some odd reason it cannot
> delete two old files. When i go in to delete them manually
> or check the security settings ,but give me an access
> denied prompt. I am logged in as Administrator w/ full
> rights, but only these files are giving me denied access.
> Please help
> Thanks in advance
> AT|||thanks hari, are there any contingencies incase this does
net work?
thanks
AT
>--Original Message--
>Hi,
>Stop SQL Agent service and try deleting the file.
>Thanks
>Hari
>MCDBA
>"AT" <anonymous@.discussions.microsoft.com> wrote in
message
>news:dfa701c3f0a2$5230bd80$a301280a@.phx.gbl...
manually
access.
>
>.
>|||Hi,
Incase if this fail, check any other process uses this file. Mostly it will
be some backup processes (tape / disk copy).
Thanks
Hari
MCDBA
"AT" <anonymous@.discussions.microsoft.com> wrote in message
news:eb1101c3f0a9$58c30160$a601280a@.phx.gbl...
> thanks hari, are there any contingencies incase this does
> net work?
> thanks
> AT
> message
> manually
> access.|||Hey thanks Hari for the replies...
But I was able to resolve the problem by doing a simple
sys reboot..
thanks again for your help..
AT
>--Original Message--
>Hi,
>Incase if this fail, check any other process uses this
file. Mostly it will
>be some backup processes (tape / disk copy).
>Thanks
>Hari
>MCDBA
>"AT" <anonymous@.discussions.microsoft.com> wrote in
message
>news:eb1101c3f0a9$58c30160$a601280a@.phx.gbl...
does
full
>
>.
>

Deleting of Databases

Windows 2003 SP2 with SQL 2000:
We are trying to delete some databases that are old and the Enterprise
Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would fix
the problem but it doesn't. We've tried from other SQL boxes' Enterprise
Managers as well with no privail. Is there any way (potentially from SQLCMD)
to properly force the deletion of a database? Thanks in advance to anyone
with some info!
ctowndu33 wrote:
> Windows 2003 SP2 with SQL 2000:
> We are trying to delete some databases that are old and the Enterprise
> Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would fix
> the problem but it doesn't. We've tried from other SQL boxes' Enterprise
> Managers as well with no privail. Is there any way (potentially from SQLCMD)
> to properly force the deletion of a database? Thanks in advance to anyone
> with some info!
Have you tried to switch database to off-line mode (or detach database)
and then delete mdf/ldf files?
PL
|||1) Do you get any error message (perhaps logged in the sql server error
logs?
2) Have you tried this from the command line or from query analyzer?
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"ctowndu33" <ctowndu33@.discussions.microsoft.com> wrote in message
news:450B4B7A-F575-4F29-8D31-55B6F395ABB4@.microsoft.com...
> Windows 2003 SP2 with SQL 2000:
> We are trying to delete some databases that are old and the Enterprise
> Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would
> fix
> the problem but it doesn't. We've tried from other SQL boxes' Enterprise
> Managers as well with no privail. Is there any way (potentially from
> SQLCMD)
> to properly force the deletion of a database? Thanks in advance to anyone
> with some info!
|||Now we realize that we can delete the databases if we clear the check mark to
remove backups. It's almost like it is searching for old backups that don't
exist any more. I though maybe I would see some really slow speeds (if it is
searching for old backups) if I try and do a restore. Turns out it took like
10 minutes for the restore box to pop up. Could the history of backups
contain orphaned informationt that would be causing this non-responsive
behavior? Thanks again everyone!
"ctowndu33" wrote:

> Windows 2003 SP2 with SQL 2000:
> We are trying to delete some databases that are old and the Enterprise
> Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would fix
> the problem but it doesn't. We've tried from other SQL boxes' Enterprise
> Managers as well with no privail. Is there any way (potentially from SQLCMD)
> to properly force the deletion of a database? Thanks in advance to anyone
> with some info!
|||Or perhaps it is simply taking forever to find the backups to delete? I
have a client with 6500+ databases on one server. I long ago had to create
several indexes on msdb tables so that things like this would work. The
index seeks enabled by the indexes let to very fast operations on
backup/restore tables.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"ctowndu33" <ctowndu33@.discussions.microsoft.com> wrote in message
news:AADA5A38-70AC-4B89-862E-BDE64C5F1178@.microsoft.com...[vbcol=seagreen]
> Now we realize that we can delete the databases if we clear the check mark
> to
> remove backups. It's almost like it is searching for old backups that
> don't
> exist any more. I though maybe I would see some really slow speeds (if it
> is
> searching for old backups) if I try and do a restore. Turns out it took
> like
> 10 minutes for the restore box to pop up. Could the history of backups
> contain orphaned informationt that would be causing this non-responsive
> behavior? Thanks again everyone!
> "ctowndu33" wrote:
sql

Deleting of Databases

Windows 2003 SP2 with SQL 2000:
We are trying to delete some databases that are old and the Enterprise
Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would fi
x
the problem but it doesn't. We've tried from other SQL boxes' Enterprise
Managers as well with no privail. Is there any way (potentially from SQLCMD
)
to properly force the deletion of a database? Thanks in advance to anyone
with some info!ctowndu33 wrote:
> Windows 2003 SP2 with SQL 2000:
> We are trying to delete some databases that are old and the Enterprise
> Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would
fix
> the problem but it doesn't. We've tried from other SQL boxes' Enterprise
> Managers as well with no privail. Is there any way (potentially from SQLC
MD)
> to properly force the deletion of a database? Thanks in advance to anyone
> with some info!
Have you tried to switch database to off-line mode (or detach database)
and then delete mdf/ldf files?
PL|||1) Do you get any error message (perhaps logged in the sql server error
logs?
2) Have you tried this from the command line or from query analyzer?
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"ctowndu33" <ctowndu33@.discussions.microsoft.com> wrote in message
news:450B4B7A-F575-4F29-8D31-55B6F395ABB4@.microsoft.com...
> Windows 2003 SP2 with SQL 2000:
> We are trying to delete some databases that are old and the Enterprise
> Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would
> fix
> the problem but it doesn't. We've tried from other SQL boxes' Enterprise
> Managers as well with no privail. Is there any way (potentially from
> SQLCMD)
> to properly force the deletion of a database? Thanks in advance to anyone
> with some info!|||Now we realize that we can delete the databases if we clear the check mark t
o
remove backups. It's almost like it is searching for old backups that don't
exist any more. I though maybe I would see some really slow speeds (if it i
s
searching for old backups) if I try and do a restore. Turns out it took lik
e
10 minutes for the restore box to pop up. Could the history of backups
contain orphaned informationt that would be causing this non-responsive
behavior? Thanks again everyone!
"ctowndu33" wrote:

> Windows 2003 SP2 with SQL 2000:
> We are trying to delete some databases that are old and the Enterprise
> Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would
fix
> the problem but it doesn't. We've tried from other SQL boxes' Enterprise
> Managers as well with no privail. Is there any way (potentially from SQLC
MD)
> to properly force the deletion of a database? Thanks in advance to anyone
> with some info!|||Or perhaps it is simply taking forever to find the backups to delete? I
have a client with 6500+ databases on one server. I long ago had to create
several indexes on msdb tables so that things like this would work. The
index seeks enabled by the indexes let to very fast operations on
backup/restore tables.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"ctowndu33" <ctowndu33@.discussions.microsoft.com> wrote in message
news:AADA5A38-70AC-4B89-862E-BDE64C5F1178@.microsoft.com...[vbcol=seagreen]
> Now we realize that we can delete the databases if we clear the check mark
> to
> remove backups. It's almost like it is searching for old backups that
> don't
> exist any more. I though maybe I would see some really slow speeds (if it
> is
> searching for old backups) if I try and do a restore. Turns out it took
> like
> 10 minutes for the restore box to pop up. Could the history of backups
> contain orphaned informationt that would be causing this non-responsive
> behavior? Thanks again everyone!
> "ctowndu33" wrote:
>

Deleting of Databases

Windows 2003 SP2 with SQL 2000:
We are trying to delete some databases that are old and the Enterprise
Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would fix
the problem but it doesn't. We've tried from other SQL boxes' Enterprise
Managers as well with no privail. Is there any way (potentially from SQLCMD)
to properly force the deletion of a database? Thanks in advance to anyone
with some info!ctowndu33 wrote:
> Windows 2003 SP2 with SQL 2000:
> We are trying to delete some databases that are old and the Enterprise
> Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would fix
> the problem but it doesn't. We've tried from other SQL boxes' Enterprise
> Managers as well with no privail. Is there any way (potentially from SQLCMD)
> to properly force the deletion of a database? Thanks in advance to anyone
> with some info!
Have you tried to switch database to off-line mode (or detach database)
and then delete mdf/ldf files?
PL|||1) Do you get any error message (perhaps logged in the sql server error
logs?
2) Have you tried this from the command line or from query analyzer?
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"ctowndu33" <ctowndu33@.discussions.microsoft.com> wrote in message
news:450B4B7A-F575-4F29-8D31-55B6F395ABB4@.microsoft.com...
> Windows 2003 SP2 with SQL 2000:
> We are trying to delete some databases that are old and the Enterprise
> Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would
> fix
> the problem but it doesn't. We've tried from other SQL boxes' Enterprise
> Managers as well with no privail. Is there any way (potentially from
> SQLCMD)
> to properly force the deletion of a database? Thanks in advance to anyone
> with some info!|||Now we realize that we can delete the databases if we clear the check mark to
remove backups. It's almost like it is searching for old backups that don't
exist any more. I though maybe I would see some really slow speeds (if it is
searching for old backups) if I try and do a restore. Turns out it took like
10 minutes for the restore box to pop up. Could the history of backups
contain orphaned informationt that would be causing this non-responsive
behavior? Thanks again everyone!
"ctowndu33" wrote:
> Windows 2003 SP2 with SQL 2000:
> We are trying to delete some databases that are old and the Enterprise
> Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would fix
> the problem but it doesn't. We've tried from other SQL boxes' Enterprise
> Managers as well with no privail. Is there any way (potentially from SQLCMD)
> to properly force the deletion of a database? Thanks in advance to anyone
> with some info!|||Or perhaps it is simply taking forever to find the backups to delete? I
have a client with 6500+ databases on one server. I long ago had to create
several indexes on msdb tables so that things like this would work. The
index seeks enabled by the indexes let to very fast operations on
backup/restore tables.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"ctowndu33" <ctowndu33@.discussions.microsoft.com> wrote in message
news:AADA5A38-70AC-4B89-862E-BDE64C5F1178@.microsoft.com...
> Now we realize that we can delete the databases if we clear the check mark
> to
> remove backups. It's almost like it is searching for old backups that
> don't
> exist any more. I though maybe I would see some really slow speeds (if it
> is
> searching for old backups) if I try and do a restore. Turns out it took
> like
> 10 minutes for the restore box to pop up. Could the history of backups
> contain orphaned informationt that would be causing this non-responsive
> behavior? Thanks again everyone!
> "ctowndu33" wrote:
>> Windows 2003 SP2 with SQL 2000:
>> We are trying to delete some databases that are old and the Enterprise
>> Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would
>> fix
>> the problem but it doesn't. We've tried from other SQL boxes' Enterprise
>> Managers as well with no privail. Is there any way (potentially from
>> SQLCMD)
>> to properly force the deletion of a database? Thanks in advance to
>> anyone
>> with some info!

Deleting maintenance plan

How do I delete an old maintenance plan that is causing errors in my event
viewer?
EM, Management, Database Maintenance Plans.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"kmd" <kmd@.hotmail.com> wrote in message news:eAyEMCJhEHA.1724@.tk2msftngp13.phx.gbl...
> How do I delete an old maintenance plan that is causing errors in my event
> viewer?
>

Deleting Databases in SMO for C#

Hey all,

I was trying to write a C# function that would delete everything in the old database before doing anything new. The following is the code I tried to use for the deleting part. I tried "Killdatabase" but it doesn't seem to be working. Anyway, is there a way to reference the database in the server and send commands to it? The compiler gives me a error message saying that srv.Databases("Name") is wrong because I am using a property as a method.

p.s. Someone posted a similar question the other day and they solved it in visual basic by introducing a local variable. It didn't work for me on C#.

Many Thanks

Server srv = new Server(con);

srv.Databases("Name").Drop();

Database d = new Database(srv, "Name");

d.Create();

d.ExecuteNonQuery(command);

Ooops, I am sorry. I should have used [] instead of (). But I did have to use a local variable.

Thanks anyway.

Deleting BAK and TRN files

I have a mentanence plan set to backup .trn and .bak files daily and then
delete the ones over 3 days old.
The problem is that they don't delete after 3 days....they just build up
and up!!!
Is this a known issue? or am i doing something wrong?
Any response is much appreciated!!!
Cheers,
Alex
Below KB might help:
http://support.microsoft.com/default...&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"alexbax" <alexbax@.discussions.microsoft.com> wrote in message
news:80006EA9-9286-4D4B-AEFF-2983A4C0CD5C@.microsoft.com...
>I have a mentanence plan set to backup .trn and .bak files daily and then
> delete the ones over 3 days old.
> The problem is that they don't delete after 3 days....they just build up
> and up!!!
> Is this a known issue? or am i doing something wrong?
> Any response is much appreciated!!!
> Cheers,
> Alex
>
|||Thats great! ive made a few changes and will check to see if it works in a
few days!!
Thanks,
Alex
"Tibor Karaszi" wrote:

> Below KB might help:
> http://support.microsoft.com/default...&Product=sql2k
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
> -- Log files don't delete --
> This is likely to be either a permissions problem or a sharing violation
> problem. The maintenance plan is run as a job, and jobs are run by the
> SQLServerAgent service.
> Permissions:
> 1. Determine the startup account for the SQLServerAgent service
> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
> account is the security context for jobs, and thus the maintenance plan.
> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
> account) then skip step 3.
> 3. On that box, log onto NT as that account. Using Explorer, attempt to
> delete an expired backup. If that succeeds then go to Sharing Violation
> section.
> 4. Log onto NT with an account that is an administrator and use Explorer to
> look at the Properties|Security of the folder (where the backups reside)
> and ensure the SQLServerAgent startup account has Full Control. If the
> SQLServerAgent startup account is LocalSystem, then the account to consider
> is SYSTEM.
> 5. In NT, if an account is a member of an NT group, and if that group has
> Access is Denied, then that account will have Access is Denied, even if
> that account is also a member of the Administrators group. Thus you may
> need to check group permissions (if the Startup Account is a member of a
> group).
> 6. Keep in mind that permissions (by default) are inherited from a parent
> folder. Thus, if the backups are stored in C:\bak, and if someone had
> denied permission to the SQLServerAgent startup account for C:\, then
> C:\bak will inherit access is denied.
> Sharing violation:
> This is likely to be rooted in a timing issue, with the most likely cause
> being another scheduled process (such as NT Backup or Anti-Virus software)
> having the backup file open at the time when the SQLServerAgent (i.e., the
> maintenance plan job) tried to delete it.
> 1. Download filemon and handle from www.sysinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed to
> local drive), then you will need to modify the above (with respect to where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "alexbax" <alexbax@.discussions.microsoft.com> wrote in message
> news:80006EA9-9286-4D4B-AEFF-2983A4C0CD5C@.microsoft.com...
>
>
sql

Deleting BAK and TRN files

I have a mentanence plan set to backup .trn and .bak files daily and then
delete the ones over 3 days old.
The problem is that they don't delete after 3 days....they just build up
and up!!!
Is this a known issue? or am i doing something wrong'
Any response is much appreciated!!!
Cheers,
AlexBelow KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"alexbax" <alexbax@.discussions.microsoft.com> wrote in message
news:80006EA9-9286-4D4B-AEFF-2983A4C0CD5C@.microsoft.com...
>I have a mentanence plan set to backup .trn and .bak files daily and then
> delete the ones over 3 days old.
> The problem is that they don't delete after 3 days....they just build up
> and up!!!
> Is this a known issue? or am i doing something wrong'
> Any response is much appreciated!!!
> Cheers,
> Alex
>|||Thats great! ive made a few changes and will check to see if it works in a
few days!!
Thanks,
Alex
"Tibor Karaszi" wrote:
> Below KB might help:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
> -- Log files don't delete --
> This is likely to be either a permissions problem or a sharing violation
> problem. The maintenance plan is run as a job, and jobs are run by the
> SQLServerAgent service.
> Permissions:
> 1. Determine the startup account for the SQLServerAgent service
> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
> account is the security context for jobs, and thus the maintenance plan.
> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
> account) then skip step 3.
> 3. On that box, log onto NT as that account. Using Explorer, attempt to
> delete an expired backup. If that succeeds then go to Sharing Violation
> section.
> 4. Log onto NT with an account that is an administrator and use Explorer to
> look at the Properties|Security of the folder (where the backups reside)
> and ensure the SQLServerAgent startup account has Full Control. If the
> SQLServerAgent startup account is LocalSystem, then the account to consider
> is SYSTEM.
> 5. In NT, if an account is a member of an NT group, and if that group has
> Access is Denied, then that account will have Access is Denied, even if
> that account is also a member of the Administrators group. Thus you may
> need to check group permissions (if the Startup Account is a member of a
> group).
> 6. Keep in mind that permissions (by default) are inherited from a parent
> folder. Thus, if the backups are stored in C:\bak, and if someone had
> denied permission to the SQLServerAgent startup account for C:\, then
> C:\bak will inherit access is denied.
> Sharing violation:
> This is likely to be rooted in a timing issue, with the most likely cause
> being another scheduled process (such as NT Backup or Anti-Virus software)
> having the backup file open at the time when the SQLServerAgent (i.e., the
> maintenance plan job) tried to delete it.
> 1. Download filemon and handle from www.sysinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed to
> local drive), then you will need to modify the above (with respect to where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "alexbax" <alexbax@.discussions.microsoft.com> wrote in message
> news:80006EA9-9286-4D4B-AEFF-2983A4C0CD5C@.microsoft.com...
> >I have a mentanence plan set to backup .trn and .bak files daily and then
> > delete the ones over 3 days old.
> > The problem is that they don't delete after 3 days....they just build up
> > and up!!!
> >
> > Is this a known issue? or am i doing something wrong'
> >
> > Any response is much appreciated!!!
> >
> > Cheers,
> >
> > Alex
> >
> >
>
>

Deleting BAK and TRN files

I have a mentanence plan set to backup .trn and .bak files daily and then
delete the ones over 3 days old.
The problem is that they don't delete after 3 days....they just build up
and up!!!
Is this a known issue? or am i doing something wrong'
Any response is much appreciated!!!
Cheers,
AlexBelow KB might help:
http://support.microsoft.com/defaul...2&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"alexbax" <alexbax@.discussions.microsoft.com> wrote in message
news:80006EA9-9286-4D4B-AEFF-2983A4C0CD5C@.microsoft.com...
>I have a mentanence plan set to backup .trn and .bak files daily and then
> delete the ones over 3 days old.
> The problem is that they don't delete after 3 days....they just build up
> and up!!!
> Is this a known issue? or am i doing something wrong'
> Any response is much appreciated!!!
> Cheers,
> Alex
>|||Thats great! ive made a few changes and will check to see if it works in a
few days!!
Thanks,
Alex
"Tibor Karaszi" wrote:

> Below KB might help:
> http://support.microsoft.com/defaul...sinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it woul
d
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed t
o
> local drive), then you will need to modify the above (with respect to wher
e
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "alexbax" <alexbax@.discussions.microsoft.com> wrote in message
> news:80006EA9-9286-4D4B-AEFF-2983A4C0CD5C@.microsoft.com...
>
>