Dear All,
i'm planning to drop all the non clustered indexes (as they are not congigured well) on production database, and run the latest script to create fresh non clustered indexes on specific columns.
now my doubts are
1)will the replication affect with dropping and recreating of indexes?
2)query to drop all the non clustered indexes on that database....
can i use the query delete from sysindexes where indid>1
will the query works for me to drop all the non clustered indexes?
3)is it necessary to generate a snapshot again after creating the new indexes? or can i drop and run at subscriber also?
please guide me in this regard
Arnav
Even you learn 1%, Learn it with 100% confidence.
Hi Has anyone heard of MS SQL Server 7 dropping indexes? I had created an index on a table. The next day the index had disappeared. Has anyone expirienced such a problem?
I have a database that is giving me a bad Index error. When I go to drop the necessary Index it is telling me that it either does not exist or cannot be dropped. However when I try to build that index, it tells me one already exists.
Is there any way to drop all of the indexes or at the very least see what the indexes are? This particular database is using 2005 Express.
SQL 7 created by default a clustered index on my primary key field. I would like to drop this index and recreate it on another field, but it is not allowing me. Error message states: "An explicit DROP INDEX is not allowed... It is being used for PRIMARY KEY CONSTRAINT enforcement." Can anybody advise how I can solve this? TIA
I would like to know if there is a way to drop/ disenable all the indexes in a maintenance plan? Or is it better to write scripts for dropping indexes and recreatig the same?
Purpose: Need to drop indexes(not the Primary key) before loading data and recreate the same after loading
I need to add syntax to a stored procedure to check for the existence of a specific indexes on a table before dropping it. If they do not exist I need it to NOT through an error message. Performing this on a table is relatively simple: if exists (select * from sysobjects where id = object_id(N'[dbo].[TABLENAME]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[TABLENAME]
Since indexes are not represented in sysobjects how can I do this?
This is for SQL 2005 and I know how to right click an index and do a "script index as create to new query window"
Basically, the one of the Microsoft scripts or views will tell us that we have 100+ indexes that exist for a database but that they are not being used by SQL server.
I will probably take them "offline" for a while and then drop them later on.
Before dropping them, I would like to be able to recreate them within minutes if system performance is degraded after this happens.
I was working on a script to pull this info out of the sys. tables like below but was wondering if anyone already has a script or an easier way to do this. (...and I don't want to right click 100 indexes within 137 tables and script to a new window and then compile a big script)
I have a huge table 170 Gb of size. On that table we have 10 indexes of around 12 GB in size.
The application is designed such that it bulk inserts the file in to sql server. But , often we are getting time outs and some latching isssues ( as can be seen in activity monitor).
So, will this be a good idea of dropping those indexes and then recreating them again for better performance.
Dear friends, First of all thanks very much for this forum, as well as the participants in this forum. This is a greate source for people who want to get ideas from the professionals from all around the world. I really thank every body to keep this place as an invaluable source for people like me.
Question: We have a table in production, which is saving quotes and whenever these quotes are not used, corosponding rows get delted. This table is heavily exposed to deletes and updates. We have a nightly task running update statistics as well as dbcc chekdb, we often get errors stating that the indexes are corrupted. We beleive it is due to disk fragmentation. we fix the problem and it happens again. Can any body advise why it is happening and what can be done about it?
I am using Access 2000 to link tables from SQL Server. The tables are linked using ODBC DSN`s. I am running a query to return some rows to Access, but once I have returned the rows, I want to drop the linked table connection to SQL Server, because it is locking a table on the server. I think I need to write a bit of VBA code, but I don`t know where to start. Any ideas would be greatly appreciated. Thanks.
I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
Are there any issues with dropping registration of a SQL 6.5 database and then registering it again. I think this is what I am going to have to do because a consultant we are using decided to change the security from standard to Integrated. Now we are getting the following error when we are in the SQL Enterprise Manager and trying to open TAP:
A connection could not be established to TAP[SQL Server]Login failed:Reason:Not associated with trusted SQL Server connection.
There are procs to add linked servers, and procs to add and delete linkedserver logins, but I dont see a proc to delete the actual linked server definition. And yet EM will drop the definition of a linked server. So the obvious question is "How do you code the dropping of a linked server?
I have an asp.net 4.5 webpage that has a detailsview on it that has a bound field that points to a SQL Server 2012 datetime field (CompDate). This field is supposed to capture date AND time of completion of a record (this is a data gathering project).
If e.NewValues.Item("Complete") = True Then Dim dt As DateTime = Now e.NewValues("CompDate") = dt e.NewValues("UNAME") = Membership.GetUser.ToString End If
Everything works great, except when the captured date/time is sent to sql server, the time portion is getting dropped and only zeros are getting stored in the time portion.
I've a VB6 application that writes data to a SQL Server database using ADODB in the VB6 code. My market has been the United States. My application is written for the English language.
I've a new user in Germany. Unfortunately, every number that contains a decimal is now stored in the SQL Server database with the decimal dropped. For example, 29.15 gets stored as 2915. I've done some searching and found the culprit is probably the OS Regional Language setting. The Germany user is using German. So it seems ADODB is expressing "29.15" as "29,15", and SQL Server is having a fit when ADODB is trying to write to the database.
My client is reluctant to changing all their user profile language settings to German (or the other option, changing their German language setting decimal symbol option to "."). They fear other applications may be affected.
Can anyone suggest a solution on my software end? Does ADODB have some options of which I'm unaware that will ignore the regional language setting and express decimal-containing numbers simply as "."? Or is there a setting in SQL Server to compensate?
I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).
Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.
I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.
As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?
I have a query that produces unique rows. However, some of the unique rows have the column called testname that has the same test listed more than once. All I want to do is drop the older testname and keep the testdate column.
Select Distinct TestID, TestDate, TestName
From third.test
I want to keep testdate in the query....guessing I need to put in to a temp table then drop the oldest one somehow by doing a subquery using Select Max....
I am finding it difficult to find an example that allows for insertion of additional rows into a table, without dropping the table I'm inserting into. Or inserting specific values. Like this example..
[URL] ....
I have 6 table I am formatting the data to conform to the final table as I'm inserting it into, but none of these examples gives me the example needed. I am using SQL 2012.
<code> SELECT CONVERT(VARCHAR(50),[FName]) + ' ' + CONVERT(VARCHAR(50),[LName]) AS [CustName] ,CAST('ALARMCOM' as nvarchar(8)) as VendorName ,CONVERT(VARCHAR(25),[CUSTOMER_CS_ACCOUNT_NUMBER]) AS [Cust_ID] ,CONVERT(VARCHAR(40),[Charge_Description])as [ChargeType] ,CASE
1. I have a simple JOIN statement between A and B, e.g. Cities A JOIN Countries B:
SELECT A.City_Name, B.Country_Code, B.Country_Area FROM Cities A JOIN Countries B ON B.Country_Id = A.Country_Id WHERE B.Country_Type='ABC';
That statement works absolutely fine, very fast (less than a second) and returns me 2 records
2. I need to replace Country Area column with 1 for Europe and 0 for all the rest. I implement so in the following way:
SELECT A.City_Name, B.Country_Code, CASE B.Country_Area WHEN 'EUR' THEN 1 ELSE 0 AS Country_Area FROM Cities A JOIN Countries B ON B.Country_Id = A.Country_Id WHERE B.Country_Type='ABC';
Now to get the same two records it takes 03:55 minutes (!)
I have looked into Estimated Execution Plan, but couldn't spot any difference - all straight forward.
It is SQL 2012 SP1 with compatibility level set to 110
I want to create and drop the global temporary table in same statement.
I am using below command but I am getting below error
Msg 2714, Level 16, State 6, Line 11
There is already an object named '##Staging_Temp' in the database.
if object_id('Datastaging..##Staging_Temp') is not null begin drop table ##Staging_Temp end CREATE TABLE ##Staging_Temp( [COL001] [varchar](4000) NULL,
I have this simple full text search query that works perfectly on my own computer using sql server 2005 express, however, on the production server(shared hosting)when I added the first 50+ rows, the full text search works perfect, but as the number of rows increases, the full text search can only see the first50+ rows, but not the new ones. Is there any quick solution for this or it's just a common mistake for developers for not properly indexed columns?Is there a way to re-indexed all rows without loosing data on the live server? search query: SELECT TOP 50 *FROM li_BookmarksWHERE FREETEXT(Keywords,@Keywords)
I currently have a SQL Server cluster setup with a Primary DB Server SERVER1 and the Standby server SERVER2. SERVER1 has been failing more than normal is the past few weeks and its takes upto 5 mins for SERVER2 realize that SERVER1 is down. I am looking for a better way to implement a backup server on production with minimum downtime. Please adivse..
Production and development servers are on different domains and they do not trust each other. How do I import data from the table t1 from a database db1 in production and load it into table t1 inside database db1 in development?
Any help would be greatly appreciated.My problem is that I need to set up a backup SQL Server 2000 machinewhich can be used in case of a failure to my primary. All databases(30 as of now) must be an up to the minute exact copy of productionand include most recent changes in data as well as any structurechanges (Tables, Views, SP's, Triggers, Users . . etc).When I tried this using Transactional Replication, the replicationprocess gets fouled up once I introduce any kind of structure changesto the DB. I've considered the idea of doing periodic backups andrestoring it to my backup SQL server, but this does not give me theconcurrency needed with 0 latency.I've seen articles that recommend using Transaction Replication with'Scheduled Table Refresh', and also doing database dumps to restore onthe backup machine, but I have not been able to find any documentationregarding this to try out. How can I implement this type of backupstrategy in SQL 2000?
I would like to deploy several reports to production server, Do i need to install reporting services entire software in order to run the reports or is it possible to just have runtime files installed on it to run the reports.
please help, i have almost 100 reports to be deployed on this server which is located in other country.
Thanks for the helpful information.
(i am using SQL server 2005 / reporting services 2005.)
ok ok, stop laughing. for real, is there any programatic way of doing this? whom ever created this database i inherited (SQL 2000) created the LDF and DATA files on the same drive and in the same folder for that matter. just trying to do a little disaster magament.
This feels like a silly question, but I`m going to ask it anyway...
I have limited SQL Server experience, but have run into a wall with a client`s Web/Access combination. I need to upgrade to SQL Server. I have Beta3 installed on a development box and am very happy with it. Is anyone running this thing in a production environment? This isn`t going to be experience huge loads, so I`m tempted. Tell me if I`m crazy for wanting to try it.
I have a brand new database server with system databases. I need to copy like four production database from another server to this new server. Can i do restore of the last production backups and restore them on the new server without creating the empty databases on the new server.If any one has better approach i will appreciate
1.First issue The port number already allocated was 2059. We have changed that into the default port 1433 in the node. Now we are able to connect the node from the client application. But we are not able to see the configuration manager in any of the environment now.
2. 2nd issue Cluster Environment
Error while executing the Package The connection details are not loaded in the connection manager tag
I have production server 2000. The server gets disconnected sometime by itself and sometime it is working fine.Sometimes it even doesn't get restarted. Is there any problem with service packs and some performance issues. Can you SQL guru give me best suggestion and how should i proceed.
Hi,I ran test data on my development machine and it took 1 minute toinsert the data. Ran the same set of data on the server and took 5minutes.Check both database and everything is the same. I even copied theproduction DB on my machine and it was taking still about 1 minute.Look at the fragmentation, and all the numbers are better on the serverthan my development machine so it should be faster.In the application I put some timer and discover that the insert istaking 0.015 ms on the server and 0 on the development. So the problemis on the insert.It is a Web application using ASP.NET.Here are the spec of the computers:Development: P4 HT 3.2GHz 1gig memory running WIN XPServer: Xeon 2.8GHz 1.5Gig memory running WIN 2000 serverAny idea how I to pinpoint the problem? I'm not at the point ofthinking that it can be the hardware, but how to verify that?ThanksFrank
