Renaming a column and replacing references to the old column name

Is there a utility out there to allow be able to rename a column in a table
and then scan the database for any objects (procs, functions, views) that us
e
this column and rename them as well. we have a db with some badly named
columns that are confusing the hell out of developers and want to rename
globally.That would be not easy to do this automatically, if two columns have the sam
e
name (refering to your poor designed database) you can do something like a
search % replace). The safest way would be to iterate (manually) rhough all
the object and change that.
Sorry about that.
"markbate" wrote:

> Is there a utility out there to allow be able to rename a column in a tabl
e
> and then scan the database for any objects (procs, functions, views) that
use
> this column and rename them as well. we have a db with some badly named
> columns that are confusing the hell out of developers and want to rename
> globally.|||You can use Enterprise Manager to generate creation scripts for all
objects, then search and replace the names and re-run the scripts.
I recommend you keep all your scripts in a source control system - then
you minimize the need to go back to the database to find the objects to
be changed.
David Portas
SQL Server MVP
--|||This is how I do this:
For a start I have all my source code within source control.
I change the create statement in question and run a build using to see what
breaks, then I can check out all the relevant scripts and make my changes
until I have no build breaks. I can then check everything back in and create
a custom script to rename the column, adding this to source control. I use D
B
Ghost (http://www.dbghost.com) and not just because I created it but because
this is the only fool proof way of introducing changes that don't break the
system with the added bonus of all the history of changes in my source
control.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"markbate" wrote:

> Is there a utility out there to allow be able to rename a column in a tabl
e
> and then scan the database for any objects (procs, functions, views) that
use
> this column and rename them as well. we have a db with some badly named
> columns that are confusing the hell out of developers and want to rename
> globally.sql

Renaming a column and replacing references to the old column name

Is there a utility out there to allow be able to rename a column in a table
and then scan the database for any objects (procs, functions, views) that use
this column and rename them as well. we have a db with some badly named
columns that are confusing the hell out of developers and want to rename
globally.
That would be not easy to do this automatically, if two columns have the same
name (refering to your poor designed database) you can do something like a
search % replace). The safest way would be to iterate (manually) rhough all
the object and change that.
Sorry about that.
"markbate" wrote:

> Is there a utility out there to allow be able to rename a column in a table
> and then scan the database for any objects (procs, functions, views) that use
> this column and rename them as well. we have a db with some badly named
> columns that are confusing the hell out of developers and want to rename
> globally.
|||You can use Enterprise Manager to generate creation scripts for all
objects, then search and replace the names and re-run the scripts.
I recommend you keep all your scripts in a source control system - then
you minimize the need to go back to the database to find the objects to
be changed.
David Portas
SQL Server MVP
|||This is how I do this:
For a start I have all my source code within source control.
I change the create statement in question and run a build using to see what
breaks, then I can check out all the relevant scripts and make my changes
until I have no build breaks. I can then check everything back in and create
a custom script to rename the column, adding this to source control. I use DB
Ghost (http://www.dbghost.com) and not just because I created it but because
this is the only fool proof way of introducing changes that don't break the
system with the added bonus of all the history of changes in my source
control.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"markbate" wrote:

> Is there a utility out there to allow be able to rename a column in a table
> and then scan the database for any objects (procs, functions, views) that use
> this column and rename them as well. we have a db with some badly named
> columns that are confusing the hell out of developers and want to rename
> globally.

Renaming a column and replacing references to the old column name

Is there a utility out there to allow be able to rename a column in a table
and then scan the database for any objects (procs, functions, views) that use
this column and rename them as well. we have a db with some badly named
columns that are confusing the hell out of developers and want to rename
globally.That would be not easy to do this automatically, if two columns have the same
name (refering to your poor designed database) you can do something like a
search % replace). The safest way would be to iterate (manually) rhough all
the object and change that.
Sorry about that.
"markbate" wrote:
> Is there a utility out there to allow be able to rename a column in a table
> and then scan the database for any objects (procs, functions, views) that use
> this column and rename them as well. we have a db with some badly named
> columns that are confusing the hell out of developers and want to rename
> globally.|||example - we want to rename a column called Deleted - to Active. Then scan
all procs to see where this table.column combination is used in any procs,
functions, views etc and rename as well, hope that is a little clearer. There
must be a utility out there that does this but I cany find one. thanks
"Jens Sü�meyer" wrote:
> That would be not easy to do this automatically, if two columns have the same
> name (refering to your poor designed database) you can do something like a
> search % replace). The safest way would be to iterate (manually) rhough all
> the object and change that.
> Sorry about that.
> "markbate" wrote:
> > Is there a utility out there to allow be able to rename a column in a table
> > and then scan the database for any objects (procs, functions, views) that use
> > this column and rename them as well. we have a db with some badly named
> > columns that are confusing the hell out of developers and want to rename
> > globally.|||You can use Enterprise Manager to generate creation scripts for all
objects, then search and replace the names and re-run the scripts.
I recommend you keep all your scripts in a source control system - then
you minimize the need to go back to the database to find the objects to
be changed.
--
David Portas
SQL Server MVP
--|||Search & Replace will work if you can rely on your definitions.
You want to change the name "Active" to "Deleted" on a special table. You
run S&R that´ll will change also that columns in other which should not be
changed (You should keep a hand on the automatic process), that can bring you
some weird problems if you don´t do that in a accurate way.
HTH, jens Suessmeyer.
"markbate" wrote:
> example - we want to rename a column called Deleted - to Active. Then scan
> all procs to see where this table.column combination is used in any procs,
> functions, views etc and rename as well, hope that is a little clearer. There
> must be a utility out there that does this but I cany find one. thanks
> "Jens Sü�meyer" wrote:
> > That would be not easy to do this automatically, if two columns have the same
> > name (refering to your poor designed database) you can do something like a
> > search % replace). The safest way would be to iterate (manually) rhough all
> > the object and change that.
> >
> > Sorry about that.
> >
> > "markbate" wrote:
> >
> > > Is there a utility out there to allow be able to rename a column in a table
> > > and then scan the database for any objects (procs, functions, views) that use
> > > this column and rename them as well. we have a db with some badly named
> > > columns that are confusing the hell out of developers and want to rename
> > > globally.|||This is how I do this:
For a start I have all my source code within source control.
I change the create statement in question and run a build using to see what
breaks, then I can check out all the relevant scripts and make my changes
until I have no build breaks. I can then check everything back in and create
a custom script to rename the column, adding this to source control. I use DB
Ghost (http://www.dbghost.com) and not just because I created it but because
this is the only fool proof way of introducing changes that don't break the
system with the added bonus of all the history of changes in my source
control.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"markbate" wrote:
> Is there a utility out there to allow be able to rename a column in a table
> and then scan the database for any objects (procs, functions, views) that use
> this column and rename them as well. we have a db with some badly named
> columns that are confusing the hell out of developers and want to rename
> globally.

Rename SqL 2005 default instance and netbios name

I am replacing an old NT 4.0 ,SqL 2000 server with a Server 2003 ,SQL
2005 Cluster. We are wanting to do a side by side installation where
after everything has been tested we flip the switch in DNS and wins and
the apps and dsn's connect to the new server. The problem is that im
not sure if i will be able to change the netbios name and default
instance on virtual server. First off i would like input on if its
possible or not? Secondly if it is possible what are some of the
caveats? Thanks any input will most appreciatedHi
This talks about SQL 2005 but I think it may also be applicable to SQL 2000
as it is the virtual server that gets renamed and not the instance of SQL
Server.
http://msdn2.microsoft.com/en-us/library/ms178083.aspx
Before doing it for real you could run a test.
John
"Dxdunbar@.gmail.com" wrote:

> I am replacing an old NT 4.0 ,SqL 2000 server with a Server 2003 ,SQL
> 2005 Cluster. We are wanting to do a side by side installation where
> after everything has been tested we flip the switch in DNS and wins and
> the apps and dsn's connect to the new server. The problem is that im
> not sure if i will be able to change the netbios name and default
> instance on virtual server. First off i would like input on if its
> possible or not? Secondly if it is possible what are some of the
> caveats? Thanks any input will most appreciated
>

Rename SqL 2005 default instance and netbios name

I am replacing an old NT 4.0 ,SqL 2000 server with a Server 2003 ,SQL
2005 Cluster. We are wanting to do a side by side installation where
after everything has been tested we flip the switch in DNS and wins and
the apps and dsn's connect to the new server. The problem is that im
not sure if i will be able to change the netbios name and default
instance on virtual server. First off i would like input on if its
possible or not? Secondly if it is possible what are some of the
caveats? Thanks any input will most appreciatedHi
This talks about SQL 2005 but I think it may also be applicable to SQL 2000
as it is the virtual server that gets renamed and not the instance of SQL
Server.
http://msdn2.microsoft.com/en-us/library/ms178083.aspx
Before doing it for real you could run a test.
John
"Dxdunbar@.gmail.com" wrote:
> I am replacing an old NT 4.0 ,SqL 2000 server with a Server 2003 ,SQL
> 2005 Cluster. We are wanting to do a side by side installation where
> after everything has been tested we flip the switch in DNS and wins and
> the apps and dsn's connect to the new server. The problem is that im
> not sure if i will be able to change the netbios name and default
> instance on virtual server. First off i would like input on if its
> possible or not? Secondly if it is possible what are some of the
> caveats? Thanks any input will most appreciated
>

rename column

i need t-sql syntax for renaming a column.
the column name contains the [] characters.
alter table <tablename> rename [old field name] to NewFieldName
isn't working for me.
tia,
mcnewsxpthis works:
EXEC sp_rename 'MyTable.[Old Field]', 'NewField', 'COLUMN'|||use sp_rename..
I haven't seen the sytax that you have used.|||You might want to check this out ::
http://vadivel.blogspot.com/2004/08...g-sprename.html
Best Regards
Vadivel
http://vadivel.blogspot.com
"mcnews" wrote:

> i need t-sql syntax for renaming a column.
> the column name contains the [] characters.
> alter table <tablename> rename [old field name] to NewFieldName
> isn't working for me.
> tia,
> mcnewsxp
>sql

Rename A Server

We want to migrate a SQL 7 to SQL2000 (there will be a new box) and then rename the old SQL 7 box and keep it on the network.
Seems like I remember there is a problem when your rename a SQL server.
Anyone have experience with this?
Thanks
ChuckCheck this out (http://www.databasejournal.com/scripts/article.php/1496451)|||...And this is what M$ says about this process:

Q. Can I rename a server after installing SQL Server 2000?

A: Yes, you can rename a server after the installation of SQL Server 2000. When the SQL Server service starts for the first time after the name change, it automatically recognizes the change and resets the computer name. You do not need to run setup again to reset this value. However, you must perform several additional configuration steps. To correct the sysservers system table, you should manually run the following procedures.

For a default instance: sp_dropserver <old_servername>
go
sp_addserver <new_servername> , local
go

For a named instance:sp_dropserver <old_servername\instancename>
go
sp_addserver <new_servername\instancename> , local
go|||Thanks.

I'll try that. It is actually SQL 7. We are upgrading a SQL 7 server to SQL 2000. The SQL 2000 is a new box and we want to keep the SQL 7 server around on the lan for a while.

Thanks again

Chuck

Removing Old/Unused Stored Procs....

Hello Friends,
Situation:
I have been charged with cleaning up a large legacy database.
During this process I have identified tables no longer used and removed them
from the schema.
Question:
I now have may stored procs that reference these tables and are no longer
needed.
How can I identify the procs and remove them programmatically?
Do I need to recompile each one and look for errors?
Any help at all would be most welcome.
Thanks in advance,
B.Hi
Use the sysobjects, sysdepends tables to identify all the procedures using
the given tables.Build a query and from the resultset u know hat objects nee
d
to be dropped.
Imtiaz
"Tam O'Shanter" wrote:

> Hello Friends,
> Situation:
> I have been charged with cleaning up a large legacy database.
> During this process I have identified tables no longer used and removed th
em
> from the schema.
> Question:
> I now have may stored procs that reference these tables and are no longer
> needed.
> How can I identify the procs and remove them programmatically?
> Do I need to recompile each one and look for errors?
> Any help at all would be most welcome.
>
> Thanks in advance,
> B.
>
>|||Cool idea.
However, what if the tables that have been removed are unknown?
Thanks in advance for any tips.
B.
"Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
news:9BE5007C-30BD-465D-A9E7-C54EB5158CE9@.microsoft.com...
> Hi
> Use the sysobjects, sysdepends tables to identify all the procedures using
> the given tables.Build a query and from the resultset u know hat objects
need[vbcol=seagreen]
> to be dropped.
> Imtiaz
> "Tam O'Shanter" wrote:
>
them[vbcol=seagreen]
longer[vbcol=seagreen]|||Tam O'Shanter wrote:[vbcol=seagreen]
> Cool idea.
> However, what if the tables that have been removed are unknown?
> Thanks in advance for any tips.
> B.
> "Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
> news:9BE5007C-30BD-465D-A9E7-C54EB5158CE9@.microsoft.com...
I was going to suggest you try and recompile all your procedures and see
which ones fail because of missing table references, but that does not
seem to work:
create table whatever (col1 int)
go
create proc whateverproc
as
select * from whatever
go
sp_depends whateverproc
go
drop table whatever
go
sp_depends whateverproc
go
drop proc whateverproc
go
If you have a list of tables you dropped, you could search syscomments
table in the database for the table names using:
Select object_name(id)
from syscomments
where text like N'%<table_name>%'
However, if your procedures are encrypted that won't work either.
--
David G.|||If you have a list of tables that you removed, then you can find out
all the stored procedures which are using the tables that were
removed.
Do a
select * from syscomments where text like '%tablename%'
this will give you a list of all the objects which were using the
table that you deleted and hence all this objects should be invalid
and hence you can delete them.
Thank You
-Pranay
"Tam O'Shanter" <Tam@.Oshanter.com> wrote in message news:<SPsWc.22207195$Id.3687174@.news.eas
ynews.com>...
> Hello Friends,
> Situation:
> I have been charged with cleaning up a large legacy database.
> During this process I have identified tables no longer used and removed th
em
> from the schema.
> Question:
> I now have may stored procs that reference these tables and are no longer
> needed.
> How can I identify the procs and remove them programmatically?
> Do I need to recompile each one and look for errors?
> Any help at all would be most welcome.
>
> Thanks in advance,
> B.

Removing Old/Unused Stored Procs....

Hello Friends,
Situation:
I have been charged with cleaning up a large legacy database.
During this process I have identified tables no longer used and removed them
from the schema.
Question:
I now have may stored procs that reference these tables and are no longer
needed.
How can I identify the procs and remove them programmatically?
Do I need to recompile each one and look for errors?
Any help at all would be most welcome.
Thanks in advance,
B.
Hi
Use the sysobjects, sysdepends tables to identify all the procedures using
the given tables.Build a query and from the resultset u know hat objects need
to be dropped.
Imtiaz
"Tam O'Shanter" wrote:

> Hello Friends,
> Situation:
> I have been charged with cleaning up a large legacy database.
> During this process I have identified tables no longer used and removed them
> from the schema.
> Question:
> I now have may stored procs that reference these tables and are no longer
> needed.
> How can I identify the procs and remove them programmatically?
> Do I need to recompile each one and look for errors?
> Any help at all would be most welcome.
>
> Thanks in advance,
> B.
>
>
|||Cool idea.
However, what if the tables that have been removed are unknown?
Thanks in advance for any tips.
B.
"Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
news:9BE5007C-30BD-465D-A9E7-C54EB5158CE9@.microsoft.com...
> Hi
> Use the sysobjects, sysdepends tables to identify all the procedures using
> the given tables.Build a query and from the resultset u know hat objects
need[vbcol=seagreen]
> to be dropped.
> Imtiaz
> "Tam O'Shanter" wrote:
them[vbcol=seagreen]
longer[vbcol=seagreen]
|||Tam O'Shanter wrote:[vbcol=seagreen]
> Cool idea.
> However, what if the tables that have been removed are unknown?
> Thanks in advance for any tips.
> B.
> "Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
> news:9BE5007C-30BD-465D-A9E7-C54EB5158CE9@.microsoft.com...
I was going to suggest you try and recompile all your procedures and see
which ones fail because of missing table references, but that does not
seem to work:
create table whatever (col1 int)
go
create proc whateverproc
as
select * from whatever
go
sp_depends whateverproc
go
drop table whatever
go
sp_depends whateverproc
go
drop proc whateverproc
go
If you have a list of tables you dropped, you could search syscomments
table in the database for the table names using:
Select object_name(id)
from syscomments
where text like N'%<table_name>%'
However, if your procedures are encrypted that won't work either.
David G.
|||If you have a list of tables that you removed, then you can find out
all the stored procedures which are using the tables that were
removed.
Do a
select * from syscomments where text like '%tablename%'
this will give you a list of all the objects which were using the
table that you deleted and hence all this objects should be invalid
and hence you can delete them.
Thank You
-Pranay
"Tam O'Shanter" <Tam@.Oshanter.com> wrote in message news:<SPsWc.22207195$Id.3687174@.news.easynews.com> ...
> Hello Friends,
> Situation:
> I have been charged with cleaning up a large legacy database.
> During this process I have identified tables no longer used and removed them
> from the schema.
> Question:
> I now have may stored procs that reference these tables and are no longer
> needed.
> How can I identify the procs and remove them programmatically?
> Do I need to recompile each one and look for errors?
> Any help at all would be most welcome.
>
> Thanks in advance,
> B.

Removing Old/Unused Stored Procs....

Hello Friends,
Situation:
I have been charged with cleaning up a large legacy database.
During this process I have identified tables no longer used and removed them
from the schema.
Question:
I now have may stored procs that reference these tables and are no longer
needed.
How can I identify the procs and remove them programmatically?
Do I need to recompile each one and look for errors?
Any help at all would be most welcome.
Thanks in advance,
B.Cool idea.
However, what if the tables that have been removed are unknown?
Thanks in advance for any tips.
B.
"Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
news:9BE5007C-30BD-465D-A9E7-C54EB5158CE9@.microsoft.com...
> Hi
> Use the sysobjects, sysdepends tables to identify all the procedures using
> the given tables.Build a query and from the resultset u know hat objects
need
> to be dropped.
> Imtiaz
> "Tam O'Shanter" wrote:
> > Hello Friends,
> >
> > Situation:
> >
> > I have been charged with cleaning up a large legacy database.
> > During this process I have identified tables no longer used and removed
them
> > from the schema.
> >
> > Question:
> >
> > I now have may stored procs that reference these tables and are no
longer
> > needed.
> >
> > How can I identify the procs and remove them programmatically?
> >
> > Do I need to recompile each one and look for errors?
> >
> > Any help at all would be most welcome.
> >
> >
> > Thanks in advance,
> >
> > B.
> >
> >
> >|||Tam O'Shanter wrote:
> Cool idea.
> However, what if the tables that have been removed are unknown?
> Thanks in advance for any tips.
> B.
> "Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
> news:9BE5007C-30BD-465D-A9E7-C54EB5158CE9@.microsoft.com...
>> Hi
>> Use the sysobjects, sysdepends tables to identify all the procedures
>> using the given tables.Build a query and from the resultset u know
>> hat objects need to be dropped.
>> Imtiaz
>> "Tam O'Shanter" wrote:
>> Hello Friends,
>> Situation:
>> I have been charged with cleaning up a large legacy database.
>> During this process I have identified tables no longer used and
>> removed them from the schema.
>> Question:
>> I now have may stored procs that reference these tables and are no
>> longer needed.
>> How can I identify the procs and remove them programmatically?
>> Do I need to recompile each one and look for errors?
>> Any help at all would be most welcome.
>>
>> Thanks in advance,
>> B.
I was going to suggest you try and recompile all your procedures and see
which ones fail because of missing table references, but that does not
seem to work:
create table whatever (col1 int)
go
create proc whateverproc
as
select * from whatever
go
sp_depends whateverproc
go
drop table whatever
go
sp_depends whateverproc
go
drop proc whateverproc
go
If you have a list of tables you dropped, you could search syscomments
table in the database for the table names using:
Select object_name(id)
from syscomments
where text like N'%<table_name>%'
However, if your procedures are encrypted that won't work either.
--
David G.|||If you have a list of tables that you removed, then you can find out
all the stored procedures which are using the tables that were
removed.
Do a
select * from syscomments where text like '%tablename%'
this will give you a list of all the objects which were using the
table that you deleted and hence all this objects should be invalid
and hence you can delete them.
Thank You
-Pranay
"Tam O'Shanter" <Tam@.Oshanter.com> wrote in message news:<SPsWc.22207195$Id.3687174@.news.easynews.com>...
> Hello Friends,
> Situation:
> I have been charged with cleaning up a large legacy database.
> During this process I have identified tables no longer used and removed them
> from the schema.
> Question:
> I now have may stored procs that reference these tables and are no longer
> needed.
> How can I identify the procs and remove them programmatically?
> Do I need to recompile each one and look for errors?
> Any help at all would be most welcome.
>
> Thanks in advance,
> B.

Removing old transaction log backup files

My organization is running SQL Server 2000 on a Windows 2000 server. We have
a maintenance plan set up to perform a daily backup of several database
files. The full database backup runs each day at 2:00 a.m. The transaction
log backup runs every four hours. It is setup in the maintenance plan that
the checkbox for both the data file and transaction log that says "Remove
files older than" is checked and set for 2 days. However, the old transaction
log backup files (*.TRN) are not being automatically removed. The regular
database files (*.BAK) are removed just fine. Any suggestions?
Hello
You should have some more information in the Maintenance Plan output file.
You can configure the Maint plan to produce output from the Maint Plan
properties window.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||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/
"AscentSQLAdmin" <AscentSQLAdmin@.discussions.microsoft.com> wrote in message
news:9A971D9F-D439-4F0F-B1E9-BAB6F42D73B3@.microsoft.com...
> My organization is running SQL Server 2000 on a Windows 2000 server. We have
> a maintenance plan set up to perform a daily backup of several database
> files. The full database backup runs each day at 2:00 a.m. The transaction
> log backup runs every four hours. It is setup in the maintenance plan that
> the checkbox for both the data file and transaction log that says "Remove
> files older than" is checked and set for 2 days. However, the old transaction
> log backup files (*.TRN) are not being automatically removed. The regular
> database files (*.BAK) are removed just fine. Any suggestions?

Removing old transaction log backup files

My organization is running SQL Server 2000 on a Windows 2000 server. We have
a maintenance plan set up to perform a daily backup of several database
files. The full database backup runs each day at 2:00 a.m. The transaction
log backup runs every four hours. It is setup in the maintenance plan that
the checkbox for both the data file and transaction log that says "Remove
files older than" is checked and set for 2 days. However, the old transactio
n
log backup files (*.TRN) are not being automatically removed. The regular
database files (*.BAK) are removed just fine. Any suggestions?Hello
You should have some more information in the Maintenance Plan output file.
You can configure the Maint plan to produce output from the Maint Plan
properties window.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Below 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/
"AscentSQLAdmin" <AscentSQLAdmin@.discussions.microsoft.com> wrote in message
news:9A971D9F-D439-4F0F-B1E9-BAB6F42D73B3@.microsoft.com...
> My organization is running SQL Server 2000 on a Windows 2000 server. We ha
ve
> a maintenance plan set up to perform a daily backup of several database
> files. The full database backup runs each day at 2:00 a.m. The transaction
> log backup runs every four hours. It is setup in the maintenance plan that
> the checkbox for both the data file and transaction log that says "Remove
> files older than" is checked and set for 2 days. However, the old transact
ion
> log backup files (*.TRN) are not being automatically removed. The regular
> database files (*.BAK) are removed just fine. Any suggestions?

removing old logins

Using SS2000. I'm removing old logins. When I tried to remove one, I receive
d this message "removing tis login will remove all associated database users
(if any)."
What does this mean?
Thanks,
--
Dan D.Hi,
This error will come when the user has been allocated permissions to some
other databases. So you have to
drop the user associated to that login from all the databases and then drop
the login.
Command to get the databases user is mapped
sp_helplogins <logn_name>
Then use have to go to each database and drop the user
use <dbname>
go
sp_dropuser <user_name>
Once you drop the user from all the databases you could drop the login..
sp_droplogin <login_name>
Thanks
Hari
MCDBA
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:DC4A9B5E-2EB5-40E6-9BD1-F4A9AC51E63D@.microsoft.com...
> Using SS2000. I'm removing old logins. When I tried to remove one, I
received this message "removing tis login will remove all associated
database users (if any)."
> What does this mean?
> Thanks,
> --
> Dan D.|||I understand. Makes perfect sense.
Thanks,
--
Dan D.
"Hari Prasad" wrote:

> Hi,
> This error will come when the user has been allocated permissions to some
> other databases. So you have to
> drop the user associated to that login from all the databases and then dro
p
> the login.
> Command to get the databases user is mapped
> sp_helplogins <logn_name>
> Then use have to go to each database and drop the user
> use <dbname>
> go
> sp_dropuser <user_name>
> Once you drop the user from all the databases you could drop the login..
> sp_droplogin <login_name>
> Thanks
> Hari
> MCDBA
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:DC4A9B5E-2EB5-40E6-9BD1-F4A9AC51E63D@.microsoft.com...
> received this message "removing tis login will remove all associated
> database users (if any)."
>
>

removing old logins

Using SS2000. I'm removing old logins. When I tried to remove one, I received this message "removing tis login will remove all associated database users (if any)."
What does this mean?
Thanks,
Dan D.
Hi,
This error will come when the user has been allocated permissions to some
other databases. So you have to
drop the user associated to that login from all the databases and then drop
the login.
Command to get the databases user is mapped
sp_helplogins <logn_name>
Then use have to go to each database and drop the user
use <dbname>
go
sp_dropuser <user_name>
Once you drop the user from all the databases you could drop the login..
sp_droplogin <login_name>
Thanks
Hari
MCDBA
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:DC4A9B5E-2EB5-40E6-9BD1-F4A9AC51E63D@.microsoft.com...
> Using SS2000. I'm removing old logins. When I tried to remove one, I
received this message "removing tis login will remove all associated
database users (if any)."
> What does this mean?
> Thanks,
> --
> Dan D.
|||I understand. Makes perfect sense.
Thanks,
Dan D.
"Hari Prasad" wrote:

> Hi,
> This error will come when the user has been allocated permissions to some
> other databases. So you have to
> drop the user associated to that login from all the databases and then drop
> the login.
> Command to get the databases user is mapped
> sp_helplogins <logn_name>
> Then use have to go to each database and drop the user
> use <dbname>
> go
> sp_dropuser <user_name>
> Once you drop the user from all the databases you could drop the login..
> sp_droplogin <login_name>
> Thanks
> Hari
> MCDBA
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:DC4A9B5E-2EB5-40E6-9BD1-F4A9AC51E63D@.microsoft.com...
> received this message "removing tis login will remove all associated
> database users (if any)."
>
>

removing old logins

Using SS2000. I'm removing old logins. When I tried to remove one, I received this message "removing tis login will remove all associated database users (if any)."
What does this mean?
Thanks,
--
Dan D.Hi,
This error will come when the user has been allocated permissions to some
other databases. So you have to
drop the user associated to that login from all the databases and then drop
the login.
Command to get the databases user is mapped
sp_helplogins <logn_name>
Then use have to go to each database and drop the user
use <dbname>
go
sp_dropuser <user_name>
Once you drop the user from all the databases you could drop the login..
sp_droplogin <login_name>
Thanks
Hari
MCDBA
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:DC4A9B5E-2EB5-40E6-9BD1-F4A9AC51E63D@.microsoft.com...
> Using SS2000. I'm removing old logins. When I tried to remove one, I
received this message "removing tis login will remove all associated
database users (if any)."
> What does this mean?
> Thanks,
> --
> Dan D.

Removing Old Databases

My company will soon be upgrading our Access Database to SQL Server 2000. In
order to begin testing and programming for the move, I have downloaded and
installed SQL2K MSDE SP3. My install went smooth and it works fine. The
problem I am having is when I am creating new databases or upgrading from
Access databases. If I decide the SQL Database that I created isn't what I
am needing then I go to the Data directory for MSDE and delete the database.
However, when I use Access to create another database by upsizing it sees
that the previous databases are still installed, as does the MSDE Manager
that I downloaded.
Question is, is there something else that I need to be doing to completely
remove the SQL databases from my system beyond deleting them from the Data
folder wherer they are initially created?
Thanks in advance.
If you mean remove the database, while keep the server (SQL Server/MSDE),
then you need detach the database from SQL Server/MSDE first. Then you can
delete the corresponding *.mdf/*.ldf files.
"digger27" <digger27@.discussions.microsoft.com> wrote in message
news:914CC09F-1341-427B-A6EC-52C4C6FCC191@.microsoft.com...
> My company will soon be upgrading our Access Database to SQL Server 2000.
In
> order to begin testing and programming for the move, I have downloaded and
> installed SQL2K MSDE SP3. My install went smooth and it works fine. The
> problem I am having is when I am creating new databases or upgrading from
> Access databases. If I decide the SQL Database that I created isn't what
I
> am needing then I go to the Data directory for MSDE and delete the
database.
> However, when I use Access to create another database by upsizing it sees
> that the previous databases are still installed, as does the MSDE Manager
> that I downloaded.
> Question is, is there something else that I need to be doing to completely
> remove the SQL databases from my system beyond deleting them from the Data
> folder wherer they are initially created?
> Thanks in advance.
|||Norman,
That was exactly the problem. Everything works great now.
"Norman Yuan" wrote:

> If you mean remove the database, while keep the server (SQL Server/MSDE),
> then you need detach the database from SQL Server/MSDE first. Then you can
> delete the corresponding *.mdf/*.ldf files.
> "digger27" <digger27@.discussions.microsoft.com> wrote in message
> news:914CC09F-1341-427B-A6EC-52C4C6FCC191@.microsoft.com...
> In
> I
> database.
>
>

Removing old .bak & .trn files

I've run into a problem I didn't expect with my backup program. It seems
that Windows admins are very lax about writing scripts to remove old backup
files after copying them to tape, even though they say its easy, they don't
do it for all database backups on all servers in the farm.
So, I'm wondering first, if I should do it from my backup program (based on
RealSQLGuy's) and second, how I should go about it.
My rough thought is to do an xm_cmdshell('dir') in the backup directory into
a table variable, parse the date from the filename into a datetime column,
select the old files and issue an xp_cmdshell('del <file>') command via
dynamic sql.
Thanks,
JayHello Jay!
In SQL Server 2005, you could use Maintenance Plan Wizard to create a
Maintenance Cleanup Task to delete old bak and trn files.
Ekrem Önsoy
"Jay" <spam@.nospam.org> wrote in message
news:eQNEUct9HHA.5404@.TK2MSFTNGP02.phx.gbl...
> I've run into a problem I didn't expect with my backup program. It seems
> that Windows admins are very lax about writing scripts to remove old
> backup files after copying them to tape, even though they say its easy,
> they don't do it for all database backups on all servers in the farm.
> So, I'm wondering first, if I should do it from my backup program (based
> on RealSQLGuy's) and second, how I should go about it.
> My rough thought is to do an xm_cmdshell('dir') in the backup directory
> into a table variable, parse the date from the filename into a datetime
> column, select the old files and issue an xp_cmdshell('del <file>')
> command via dynamic sql.
> Thanks,
> Jay
>
>|||Maintenance plan in 2005?
Besides, I do all my maintenance through SQLagent after writing the scripts
myself.
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:OOO8bft9HHA.5424@.TK2MSFTNGP02.phx.gbl...
> Hello Jay!
>
> In SQL Server 2005, you could use Maintenance Plan Wizard to create a
> Maintenance Cleanup Task to delete old bak and trn files.
>
> --
> Ekrem Önsoy
>
> "Jay" <spam@.nospam.org> wrote in message
> news:eQNEUct9HHA.5404@.TK2MSFTNGP02.phx.gbl...
>> I've run into a problem I didn't expect with my backup program. It seems
>> that Windows admins are very lax about writing scripts to remove old
>> backup files after copying them to tape, even though they say its easy,
>> they don't do it for all database backups on all servers in the farm.
>> So, I'm wondering first, if I should do it from my backup program (based
>> on RealSQLGuy's) and second, how I should go about it.
>> My rough thought is to do an xm_cmdshell('dir') in the backup directory
>> into a table variable, parse the date from the filename into a datetime
>> column, select the old files and issue an xp_cmdshell('del <file>')
>> command via dynamic sql.
>> Thanks,
>> Jay
>>
>|||This is just me but I would create a batch file to delete the files and give
it to the Windows group to execute it after they do the tape backup. Make
sure that the batch file execution step depends on the tape backup so that
you are not without your backups if the tape backup fails.
"Jay" wrote:
> Maintenance plan in 2005?
> Besides, I do all my maintenance through SQLagent after writing the scripts
> myself.
> "Ekrem Ã?nsoy" <ekrem@.btegitim.com> wrote in message
> news:OOO8bft9HHA.5424@.TK2MSFTNGP02.phx.gbl...
> > Hello Jay!
> >
> >
> > In SQL Server 2005, you could use Maintenance Plan Wizard to create a
> > Maintenance Cleanup Task to delete old bak and trn files.
> >
> >
> > --
> > Ekrem Ã?nsoy
> >
> >
> > "Jay" <spam@.nospam.org> wrote in message
> > news:eQNEUct9HHA.5404@.TK2MSFTNGP02.phx.gbl...
> >> I've run into a problem I didn't expect with my backup program. It seems
> >> that Windows admins are very lax about writing scripts to remove old
> >> backup files after copying them to tape, even though they say its easy,
> >> they don't do it for all database backups on all servers in the farm.
> >>
> >> So, I'm wondering first, if I should do it from my backup program (based
> >> on RealSQLGuy's) and second, how I should go about it.
> >>
> >> My rough thought is to do an xm_cmdshell('dir') in the backup directory
> >> into a table variable, parse the date from the filename into a datetime
> >> column, select the old files and issue an xp_cmdshell('del <file>')
> >> command via dynamic sql.
> >>
> >> Thanks,
> >> Jay
> >>
> >>
> >>
> >
>
>