Help with backup plan

Hi all,

I am new to SQL and was given a SQL server to administer. There are lots of databases on the server and no one seems to know which one is used and which one is not. I try to do some cleanning.

1. Can I find out if there is any activity on a database so I can delete them off and how?

2. There are backup jobs that run every night which do a full backup. I am thinking of changing it to do full backup once a week and differential every night. These databases are updated daily. Some have lots of activities, some have less. Does that sound reasonable? and if I do differential backup, should I create separate back up file or should I add on to the full backup file?

Any comments or suggestions are greatly appreciated.You can use perfmon, sp_who, "Current Activity" in enterprise manager and sql profiler (know that profiler is a performance consumer) to monitor database activity.

For backups, my question would be - is doing full backups taking too much time and/or requiring too much space ? If so, then do differentials. Are your backups to tape or disk ?|||look up sqlmaint.exe utility, it'll give you the flexibility and at the same time can help you standardize the retention period on disk for those backups. if backups don't take long, then i'd continue with full nightly, and do db health checks weekly, along with reindexing or updating stats. delete backups older than 2 or 1 days if you are short on disk, and do tape backup nightly of those backup files, and you'll be in good shape.

Help with a large delete

I have a table that is part a merge publication that I need to delete 3
million rows from which will leave about a million in the table. I plan to
just insert a list of the id's to go in a temporary table and then just do
delete where id in table X.
Another way that has been suggested to me is to place a copy of the records
that I want to keep into another table and then truncate the main table and
then put the ones I want back in. Would this method work due to the table
being part of a merge publication, as I presume that the truncate would not
happen on the server that is subscribed to this publication.
I'd do the truncate and reinsert as your transaction log will likely grow
absolutely huge the other way. Not to say it won't grow huge the first way.
keep in mind that in order to do the truncate you will need to drop the
subscriptions and publications.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:F3DE3D1A-A230-4E87-B23D-79CA1B2F8460@.microsoft.com...
> I have a table that is part a merge publication that I need to delete 3
> million rows from which will leave about a million in the table. I plan to
> just insert a list of the id's to go in a temporary table and then just do
> delete where id in table X.
> Another way that has been suggested to me is to place a copy of the
records
> that I want to keep into another table and then truncate the main table
and
> then put the ones I want back in. Would this method work due to the table
> being part of a merge publication, as I presume that the truncate would
not
> happen on the server that is subscribed to this publication.
|||Will it only grow huge on the server that I do the delete on or will it grow
huge on both servers?
"Hilary Cotter" wrote:

> I'd do the truncate and reinsert as your transaction log will likely grow
> absolutely huge the other way. Not to say it won't grow huge the first way.
> keep in mind that in order to do the truncate you will need to drop the
> subscriptions and publications.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:F3DE3D1A-A230-4E87-B23D-79CA1B2F8460@.microsoft.com...
> records
> and
> not
>
>

help to remove old .bak file

I have an old .bak file that is not being removed via our maitenenance plan
backups. When I try to manually remove it, it says 'file in use'. How do
determine what is holding this file open?
The last time the job ran it failed.
We run daily backups and I see current backups and am able to remove some of
the more recent ones manually.Take a look at EM - Current Activity to see if the backup step is still
there. Try KILLing it if it is.
HTH
Jerry
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>I have an old .bak file that is not being removed via our maitenenance plan
> backups. When I try to manually remove it, it says 'file in use'. How
> do
> determine what is holding this file open?
> The last time the job ran it failed.
> We run daily backups and I see current backups and am able to remove some
> of
> the more recent ones manually.|||Thanks for the suggestion, but I already tried that. There was only tran log
going on for different db. I killed it anyway just to make sure and tried to
remove file but still same error. My maintenance window is not until
weekend and I am trying to avoid bouncing server. I can manually clean up
files until then but would prefer if there is another solution. Is there
any what to determine what is holding the file open?
"Jerry Spivey" wrote:
> Take a look at EM - Current Activity to see if the backup step is still
> there. Try KILLing it if it is.
> HTH
> Jerry
> "DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
> news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
> >I have an old .bak file that is not being removed via our maitenenance plan
> > backups. When I try to manually remove it, it says 'file in use'. How
> > do
> > determine what is holding this file open?
> >
> > The last time the job ran it failed.
> >
> > We run daily backups and I see current backups and am able to remove some
> > of
> > the more recent ones manually.
>
>|||Depending on how the maint plans are implemented you might check the
Processes tab in Task Manager. Also, do you need to bounce the server or
just the SQL Server/Agent services? Also, why not wait til off-peak hours
to stop - delete the file?
HTH
Jerry
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:10519EF6-CBAE-415D-8CF9-A9FABA3C0EDC@.microsoft.com...
> Thanks for the suggestion, but I already tried that. There was only tran
> log
> going on for different db. I killed it anyway just to make sure and tried
> to
> remove file but still same error. My maintenance window is not until
> weekend and I am trying to avoid bouncing server. I can manually clean
> up
> files until then but would prefer if there is another solution. Is
> there
> any what to determine what is holding the file open?
> "Jerry Spivey" wrote:
>> Take a look at EM - Current Activity to see if the backup step is still
>> there. Try KILLing it if it is.
>> HTH
>> Jerry
>> "DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
>> news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>> >I have an old .bak file that is not being removed via our maitenenance
>> >plan
>> > backups. When I try to manually remove it, it says 'file in use'.
>> > How
>> > do
>> > determine what is holding this file open?
>> >
>> > The last time the job ran it failed.
>> >
>> > We run daily backups and I see current backups and am able to remove
>> > some
>> > of
>> > the more recent ones manually.
>>|||I believe that http://www.sysinternals.com/ has tools for that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:10519EF6-CBAE-415D-8CF9-A9FABA3C0EDC@.microsoft.com...
> Thanks for the suggestion, but I already tried that. There was only tran log
> going on for different db. I killed it anyway just to make sure and tried to
> remove file but still same error. My maintenance window is not until
> weekend and I am trying to avoid bouncing server. I can manually clean up
> files until then but would prefer if there is another solution. Is there
> any what to determine what is holding the file open?
> "Jerry Spivey" wrote:
>> Take a look at EM - Current Activity to see if the backup step is still
>> there. Try KILLing it if it is.
>> HTH
>> Jerry
>> "DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
>> news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>> >I have an old .bak file that is not being removed via our maitenenance plan
>> > backups. When I try to manually remove it, it says 'file in use'. How
>> > do
>> > determine what is holding this file open?
>> >
>> > The last time the job ran it failed.
>> >
>> > We run daily backups and I see current backups and am able to remove some
>> > of
>> > the more recent ones manually.
>>|||looks at NTHandle from http://www.Sysinternals.com
cheerrs,
Andy
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>I have an old .bak file that is not being removed via our maitenenance plan
> backups. When I try to manually remove it, it says 'file in use'. How
> do
> determine what is holding this file open?
> The last time the job ran it failed.
> We run daily backups and I see current backups and am able to remove some
> of
> the more recent ones manually.