Removing Unused Objects - SQL Server 2000 Or 2005

Jan 22, 2008

Sometimes at the end of a project you'll end up with unused Tables,
Stored Procs, Functions, and Views.
Since there is not something like a 'SELECT' trigger ... what is the
best way for telling what is not being used by your solution? To turn
on some kind of tracing?

Any ideas?

Thanks,
Kelly Greer
Join Bytes!
change nospam to yahoo

View 4 Replies


ADVERTISEMENT

Removing Unused Output Columns

Nov 1, 2007

I apologise if this question has been asked before but I have spent ages searching these forums and the internet for an answer and I am yet to find one.

My problem is that I have a package which imports a column, lets call it 'Column A'. Column A is used to create other columns, lets say Columns B, C & D. This is done in a script using asychronous input and once completed column A is no longer used. Other tranformations occur to B, C & D including a split and then finally a merge together again, but all the time A seems to remain a valid input on all processes yet I never choose to use or output it. When I come to the merge process I am required to merge columns B, C & D but also A, surely this is inefficient. Furthermore when the package has run I get a warning telling me that Column A is not required and should be removed, but I can not seem to find anywhere to remove it from the pipe.

I am hoping that I am just missing something obvious here but I have been tearing my hair out so any help would be much appreciated!

Graham.

View 10 Replies View Related

DB Engine :: Removing Unused NC Indexes From Subscriber Database

Nov 13, 2015

We have transaction replication set up on one of our servers whose replicated DB is used for reporting purpose:

Now, the replicated database "D" of size 350 GB has mainly grown huge as compared to Published DB (200 GB) because of the index sizes at subscribed DB. Therefore:

I have found that Database D has one table of data size 15 GB, with rows 8349533 and index size 28 GB the biggest in the database. And adding have total of 109 Non clustered indexes:

On using SP_blitzindex i have found approx 50 NC indexes unused with below usage:

Reads: 0 Writes:273,243 and total size of indexes being 18 GB

Last User Seek
Last User Scan

Last User Lookup all '0' 
0 singleton lookups; 0 scans/seeks; 0 deletes; 0 updates; 
except for few 3 or 4 where updates are more than 4000.

Is this sufficient enough to delete all the above 49 unused NC indexes? And Can i create Missing index on Subscriber database?

View 2 Replies View Related

How To Identify Unused DB Objects For Cleanup

Aug 28, 2013

I need to setup a Server Audit in sql 2012 standard edition. The goal is to identify all procs, views, tables, functions that are not being used anymore. What is the best way to setup a server audit to accomplish this? What events need to be captured at a minimum so I can delete all the unused crap in this database. This is a smaller, growing company.

View 2 Replies View Related

Database Diagram Support Objects Cannot... Attaching Sql 2000 Db To Sql Server 2005

Jan 3, 2006

Hello,

I've seen threads concerning this problem. I have followed the prescribed steps to fix the problem. I've set the db owner to sa, I've set compatibility to 2005. Still I can't get the diagram to function.

Can anyone out there help me?

Thanks, Bill

View 3 Replies View Related

SQL Authentication After Removing SQL 2000 For SQL 2005

Aug 3, 2006

I just removed (via Add and remove programs) SQL 2000 Server and now only have SQL 2005 left on my machine. The 2005 Server (SQLExpress?) is set up for mixed mode and I've verified that the registry setting is 2 for LoginMode, yet I'm still unable to login. This is the following error?

TITLE: Connect to Database Engine
------------------------------

Cannot connect to CJL-SERVERSQLEXPRESS.

------------------------------
ADDITIONAL INFORMATION:

Login failed for user 'drafttrackuser'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18452&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------


Naturally, the link goes no where. I've searhed this site and it seems most of the problems are either more complex (linked servers, clusters etc.) or are people who haven't set the server to work in mixed mode. I've rebooted a couple of times but the errors persist.



Thanks,

Chris

View 3 Replies View Related

Transferring Objects From 2000 To 2005

Dec 1, 2006

I am having immense difficulties trying to transfer tables, sprocs, udfs, etc, from a production SqlServer2000 server to a testing SqlServer2005 server.

Under SS2000 this was easy, using the DTS wizard. Apparently that's no longer an option.

I've tried the Transfer Database, Transfer Sql Server Objects tasks and the wizard in BIDS, and they all fail (for different reasons).

Help!!!!

- Mark

p.s. -- As a side question, why in the world did you guys get rid of the one workhorse tool in Enterprise Manager that made synchronizing databases easy?

View 5 Replies View Related

Copy Data And Objects From 2005 To 2000

May 17, 2008

Anyone who has successfully done this. Do reply. Thanks

View 3 Replies View Related

Simple Objects Transfer From 2000 Db To 2005 Db

Feb 1, 2006

creating a simple package and cant get it to run below is the error. when i check the source 2k db its there see below...

i am clue free of the issus...

SSIS error message

Error: 0xC002F325 at Transfer SQL Server Objects Task, Transfer SQL Server Objects Task: Execution failed with the following error: "ERROR : errorCode=0 description='FK_MoreBDRInformation_dbo_BatchDataReports' is not a constraint. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".

source Server DB sysobjects table record

name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
FK_MoreBDRInformation_dbo_BatchDataReports 1618573300 F 1 0 0 0 0 86043838 2006-01-31 12:35:41.530 0 0 0 F 0 11 0 2006-01-31 12:35:41.530 0 0 0 0 0 0 0

View 1 Replies View Related

Moving Back To MSDE 2000 After Removing SQLServer 2005 Express

Sep 18, 2007

Apparently you cannot go backwards. Once 2005 Express is installed, even removing it does not allow you to setup MSDE 2000 again. So there is no "trying it out" option.

Unfortunately, our software does not use 2005 Express. I tried it out, and now have a useless testing workstation that can't have MSDE 2000 installed again.

Any ideas on how to break the chain here are welcome.

View 1 Replies View Related

Removing Non-useful Records And Duplicate DB Objects. ( Purging)

Oct 9, 2007



hi All,
I have to remove non-useful and duplicate records containing NULL , Blanks and extra spaces (either on left or right side of the column values) etc from all the tables in my server's DB XYZ weekly thru a a scheduled job with the help of a Stored Proc, that s i guess called Purging og DB. Plz help how i can do it with T-SQL.

Also i have to find out and remove all the duplicate DB objects(tables) from the DB .e.g. a table existing with name TABLE_TEST or TABLE_DEBUG etc for an original table TABLE , making sure no any of the base table is dropped.

Plz help me reagrding these two problems.

Thanks in advance for the quick replies.
Mohit

View 1 Replies View Related

Store Objects In Sql Server 2000

Oct 25, 2004

How can we store native .NET objects like arraylists, hashtables in Sql Server? Is this possible? If so, can anybody provide some insight on how to do this?

Thanks

View 4 Replies View Related

How To List The User Objects In Sql Server 2000

Mar 24, 2008



hi all

i am using sysobjects for taking the list of user objects.

by using userid, status column combinations, able to take out the user tables/triggers/views
but for stored procedures, it doesn't work.

is there any better way to list the user objects.

thanks in advance.
sam alex

View 3 Replies View Related

Using SSIS Packages To Process SQL Server 2000 OLAP Objects

Nov 16, 2006

Can a SSIS server, i.e. staging server be used to create packages that update SQL Server 2000 Analysis services objects on another server running SQL server 2000 OLAP? 

It appears that the OLAP connection manager ion SSIS supports only connections (and thus updates) to 2005 OLAP objects.  I work for a company that has a huge investment in a 3rd party DW that uses Analysis Services 2000.  the DW tool vendor will not support an upgrade to SSAS 2005.  We wish to extend the DW from other data sources.  My thought was to use a staging server with SSIS, used solely as the ETL tool for all new development (thus no more DTS development), and to update the sql server 2000 operational data store on another box. 

I can find no documentation on how to process sql server 2000 analysis services objects from within an SSIS package.   Any ideas?

 

 

View 4 Replies View Related

Storing Objects In SQL Server 2005

Oct 24, 2006

Hi All,I am looking to store a DataSet into a Table in SQL Server 2005, or any object for that matter.   I cannot find any code to perform.  Can anyone help?Many Thanks,Peppa.   

View 2 Replies View Related

Scripting Objects In Sql Server 2005

Apr 7, 2006

ok...this (going on eight years now) sql server user has a seeminglyvery simple problem with his new developers edition of sql server 2005.I'm trying to script multiple tables and indexes in the mmc...and usingthe usual control/shift keys to select multiple objects isn't working.Is this a bug in the mmc, or am I losing my mind or is there somethingI haven't yet seen in bol?thanks in advance...

View 1 Replies View Related

SQL Server 2005 Objects Question

Nov 28, 2006

Hi guys,I am pretty happy with the new system views in SQL 2005 but there isone thing I could not figure out: What is the view that let you seewhat object belongs to what DB? Sorry for the simple question, Cheers,G.

View 3 Replies View Related

Stroing C# Objects In SQL Server 2005

Aug 17, 2006

hi

I need to knwo if its possible to easily store / retrieve c# objects in SQL Server 2005 as user defined data types or some other type. We need to be able to store collections of simple objects as a single value and be able to and recreate the c# object. For example one type would be a hashtable of objects that just have 2 public members Date and Value

Any help apprecitaed

Steve

View 3 Replies View Related

2005 Copy Database Objects From One Server To Another

Jan 12, 2007

I just upgraded to SQLServer 2005 Standard. I do development in adatabase on one server. When a solution is completely tested, I moveit to the operational database on a different server. With SQLServer2000, I could just open the .adp file and do File->Get ExternalData->Import to move the objects. That does not work with 2005.What is the recommended method for moving objects from one database toanother? Obviously, the objects that changed will not be the sameevery time.Thanks!Jer

View 2 Replies View Related

Unable To Install SQL Server 2005 - Removing Backup Files Hangs

Oct 6, 2007



HELP!

For the past several weeks, I have been trying to install SQL Server 2005 on a Win XP SP2 PC. Regardless of the options I choose (SQL Engine + Client Tools, Client Tools only, etc.) when the installation gets to the end of the Client Tools setup and the setup status displays "Removing Backup Files", my PC appears to hang.

For a period of time, there is a fair amount of HD activity which gives me the impression that files are in fact being deleted, but then there is no HD activity and the installation appears to hang. Finally I give up and kill the setup process and reboot.

After reboot, it appears that the client tools have been installed (the icons appear and I can open the Management Studio, but when I try to connect to an existing instance of SQL, that hangs. This occurs for either Windows or SQL authentication.

Has anyone experienced this behavior and determined the root cause? What EXACTLY is happening during the "Removing Backup Files" phase of the installation?

Thanks for any help in advance,
Marc Mueller

View 3 Replies View Related

SQL Server 2005 Installation Hangs On Removing Temporary Files Step

Feb 2, 2007

I have a virtual machine that previously had SQL Server 2005 installed. Since I changed the computer name, I have to reinstall SQL. I removed all the SQL components and am trying to reinstall the database and reporting components. The installation is failing on the "Removing temporary files" step. According to the setup progress screen, the following components have been installed:

OWC11

SQL Server Bakcward-Compatibility Files

SQL Server Database Services

Reporting Services

Visual Studio Integrated Development

SQL Server Books Online

SQLXML4

Workstation Components, Books Online - this step is still in "configuring components" status.

The virtual machine is running Windows 2003 Server SP1 with all current patches, using just under 2 GB of memory (1920 MB of RAM), the host is Windows XP SP2 with all current patches, with 4 GB of memory.

I had no problem doing this procedure on a different virtual machine, so I'm not sure what the problem is here.

View 2 Replies View Related

Storing SQL Server 2005 Objects And Queries In Perforce

Jan 29, 2007

We are trying to use source control to store our database objects andqueries in Perforce. The general idea is to produce scripts whichcreate all objects in the DB and then store the SQL in source control.Has anyone done this before and can suggest some advice? Alternativelyif anyone has other methods for using source control on a database,could you please let me know?Also, is there any automated way of extracting individual scripts forobject creation from the database?

View 2 Replies View Related

SQL Server 2012 :: Select Data From XML - Objects Within Objects?

Nov 20, 2013

passing serialised objects to a stored procedure for the purpose of data inserts. I see this as being a way to handle multiple row inserts efficiently.

However, in my limited use of XML data I am not so sure how to link the data when I have a dependency on another "object" within the serialised XML.

Below is a code snippet showing what I have so far.

The first insert statement works fine - but how to retrieve the identifier created by the DB - I want to use an SQL statement that finds the record in the table based on the XML representation (of the PluginInfo), allowing me to insert the ConfigurationInfo with the correct reference to the PluginInfo

DECLARE @Config NVARCHAR(MAX)
DECLARE @Handle AS INT
DECLARE @TransactionCount AS INT
SELECT @Config = '
<ConfigurationDirectory >
<ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo"

[code]....

View 1 Replies View Related

Install Sql 2005 Instance With Reporting Service 2005 On Sql 2000 With RS 2000 Server

Aug 18, 2006

Hi

We would like to install Sql 2005 Enterprise Edition (including database engine, reporting service, integration service and analysis service) as a sepearte instance on a server which already has Sql 2000 with reporting services and analysis services. We do not want to disturb the existing sql 2000 setup.

If we do that then what will happen to my earlier sql 2000 reporting service? Will it be upgraded to sql 2005 reporting service? I heard that reporting services are instance unaware application. Where will be the default reporting service database available?

Please help us.

Regards,

Sankar N

View 1 Replies View Related

Migrating Oracle PL/SQL Objects And Collection Types Into SQL Server 2005

Mar 7, 2008



Hi

Can anyone help me out in migrating Oracle stored procedures & Functions which contains collection types (user-defined datatypes like VARRAYS) into SQL Server 2005. How to achieve this taks? This is very important for me and it's very urgent. Please someone help me.

Thanks & Regards
K P Kumar

View 1 Replies View Related

Removing -1.#IND Float Values From SQL 2000

Dec 12, 2007

How can I remove -1.#IND values from a float column in SQL Server 2000 (8.00.2039 SP4).

So far I've tried
- Using Query Analyzer, setting the value to a proper float value. I receive error €œA floating point exception occurred€?
- Using Query Analyzer, deleting the record with the invalid float. I receive error €œA floating point exception occurred€?
- Using Enterprise Manager, setting the value to a proper float value. I receive error €œA floating point exception occurred€?
- Using Enterprise Manager, deleting the record with the invalid float. I receive error €œA floating point exception occurred€?
- Previously I was able to convert the column to nvarchar, but now this fails because the table is being replicated.

I don€™t really care what happens to the records, I just need them to go away.

Thanks

View 2 Replies View Related

In-place Upgarde Not Removing The SQL 2000 Hotfix - 2191

Oct 5, 2007



Hi All,

We recently did the in-place upgrade of SQL 2000 server having the hotfix - 8.00.2191 (Intel IA-64). The server got upgraded successfully without any error and everything seems to work. However, after the upgrade the SQL 2000 hotfix -2191 - didn't get uninstalled. I would like to know :-

1. How we can remove this hotfix? if we remove from add
emove programs it doesn't get removed.
2. If we don't remove will it cause any problem?

Any help is highly appreciated.


Thanks,
Ritesh

View 3 Replies View Related

SQL Server 2008 :: Removal Of Unused Indexes

May 21, 2015

I am working with one of the production database around 200 GB. This database has above 350 tables and more than 500 Indexes. I am feeling the database has so many Indexes than the required ones

When I run the below query, it gives me some indexes read value "0". The server was restarted a month ago.Is it ok to remove those indexes?

SELECT OBJECT_NAME(s.[object_id]) AS [Table Name] ,
i.name AS [Index Name] ,
i.index_id ,
user_updates AS [Total Writes] ,
user_seeks + user_scans + user_lookups AS [Total Reads] ,
user_updates - ( user_seeks + user_scans + user_lookups )
AS [Difference]

[code]....

View 2 Replies View Related

SQL Server 2008 :: Find Unused Databases Or When Last Used In A Instance?

Mar 30, 2011

Find unused databases in a instance or when last used or accessed?

I'm on SQL SERVER 2008 R2 64bit -enterprize

I need to find when the databse is last accessed.

View 3 Replies View Related

SQL Server 2012 :: Finding Unused Stored Procedures

Mar 3, 2014

How do you find stale stored procedures ?

In a scenario where a developer created a slight modification of a stored procedure because he was afraid of breaking something else and took the easy way out, and a few more later down the line, multiple versions of a stored proc. doing slightly different things are just laying around.

"Last used" would be useful piece of information to determine the most recent date a stored procedure was called, either by the application itself or by another stored procedure itself called by the application.

Any stored proc not used for more than say 6 months would then be identified as a candidate for clean-up.

So - short or creating - after the fact - a trigger to update the usage date upon each call - which means a lot of work and no result for the next six months, how can one go about this ?

And could this be done in SS2K8 ?

View 5 Replies View Related

Bit-data From SQL Server 2000 (2005 Working, 2000 Doesn't)

May 19, 2008

 Hi, I am trying to edit some data from a SQL2000-datasource in ASP.NET 2.0 and have a problem with a column that has bit-data and is used for selection. SQL2005 works fine when declaring             <SelectParameters>                <asp:Parameter DefaultValue="TRUE" Name="APL" Type="boolean" />            </SelectParameters>When running this code with SQL2000, there are no error-msgs, but after editing a record the "APL"-column looses its value of 1 and is set to 0. Looks like an issue with type-conversion, we've hit incompatibilities between SQL200 and 2005 with bit/boolean several times before. So, how is this done correctly with SQL2000?  (I've tried setting the Type to "int16" -> err. Also setting Defval="1" gave an err) ThanksMichael   

View 2 Replies View Related

Removing AS 2005 From A Cluster

Jul 18, 2007

I am trying to remove Analysis Server from a cluster. I have done the research and according to MS you can't use the regular add/remove programs dialog to remove a component. You have to use the setup.exe from the command line.
This article (http://support.microsoft.com/kb/910230) explains at the very bottom to run this command "Start /wait <DiskDrive>setup.exe /qb REMOVE=Analysis_Server INSTANCENAME=<InstanceName>". Well I do that and I get the installer up and running but when it gets to that system configuration check screen, where it runs a bunch of checks on the nodes, it just closes before it does any checks and nothing further happens. I can't find any error in the event log or find any log files on disk related to why its just closing.

Just to investigate further, I wondered what executable was running when you ran the installer though the add/remove programs dialog, so I looked in the registry. Well along with the SQL Server 2005 entry, which runs the setup bootstrap program in your default install folder, I noticed an entry that was idenified only by its GUID. Looking at the sub keys I noticed the DisplayName was Microsoft SQL Server 2005 Analysis Services. The UninstallString key was set to "MsiExec.exe /I{52B0D62A-860D-4136-9A8A-9FD877E8EE89}" so I ran that. The installer seems to work, but it doesnt run any configuration checks. I get to the screen with the big button to change components with no errors. Then it acutally shows Analysis Services installed (the disk icon is not xed out), I can then click and select entire feature will be unavailable. I haven't proceeded on this step because I am worried about the cluster.

I am assuming that this uninstaller will only remove analysis services from the node its running on. This is fine, cause I can just run this same procedure on all the nodes... and then i would assume I would have to manually clean up the cluster resource. But I have not tested this, and unfortuanlly I don't have a development cluster to try this on.

Does anyone have experience with removing Analysis Services from a failover cluster environment?


- Eric

View 1 Replies View Related

SQL 2000 To SQL 2005 Works For One 2000 Server But Not The Next

Jun 15, 2006



I have several SQL 2000 servers I need to setup transactional (non updatable) replication with. The structure is:

SQL Server 2000 as Publisher/Distributor

SQL Server 2005 Standard as Subscriber

The connection is via the Internet with snapshots using FTP.

I setup the first set (2 databases at location A). They work wonderfully. I created the publication and then subscribed using MGMT Studio for 2K5.

II am setting up the same scenario for location B. Here is my problem:

In MGMT Studio I connect to the publisher (SANDRA). I right-click a publication and choose New Subscriptions..., the publication is already selected. I click next - Run each agent at its Subscriber is selected and the only option (this is desired), I click Next

HERE IS THE PROBLEM:

On the Subscriber's screen there are no Subscribers listed. When setting up location A the subscribing server was listed and I could choose a database. The Next button is greyed out and there is no way to create/add one.

I tried setting up the subscription by right-clicking the subcribing server's Replication folder in MGMT Studio but I get the same result (except that I have to authenticate with the publishing server which works fine).

WHAT'S DIFFERENT:

Location A is SQL Server Standard (SP3) running on SBS2K3. It is obviously on a domain and so SQL Server and the SQL Agent are running under domain accounts. Location B is a Windows XP SP2 machine running SQL Server Personal Edition (it actually says Development Edition in the properties window).

The databases are the same strucutre, different data. At location A the firewall is set to allow 1433->*any* and *any*->1433 where *any* is 1024 or higher. On the XP machine the firewall is set to allow port 1433. I don't think this is the issue because I've turned the firewall off on the XP machine and I get the same result.

ANY IDEAS?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved