My production database has a maintenance plan that rebuilds all indexes and it's scheduled to run every day at 5 AM.
I disabled some nonclustered indexes that were not used by any execution plan I found, and run a Profiler session to catch any negative impact in performance.
But I found that the indexes were re-enabled the next day by the Rebuild Index task from the maintenance plan.
Is there a way to keep those index disabled? I don't want to drop them as I could have to re-enable some of them by users' request.
We have a maintenance plan that reorganize all indexes in a database. We disabled one of the index on one table this job failed. How to set up to the maintenance plan to without failing to ignore the reorganizing of disabled indexes?
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?
So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this: When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004 Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing? If so does this "pseudocode" example also cause this to occur: sqlstring="select * from item where itemid=12345" rs.open sqlstring, etc, etc, etc rs.Fields("ItemName")="My New Item Name" rs.Fields("ItemPrice")=1.00 rs.Update Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?
I must introduce myself... Kris Allain. I am a VERY new DBA. Basically I as sort of pushed into the position even though I have no previous DBA experience. I come more from a Net Admin background although I've only graduated with a degree in MIS 2.5 years ago.
With that said, I ran into a problem last week in which took down a mission critical application for 4 hours. We have an app that his highly transactional and I'm using the Full Recovery method for this DB with weekly full backups, daily differential backups, and hourly log backups. Someone (could have been me) disabled the log backup job and a couple of days later the log grew until there was no more disk space (35+ gb). Well, it took me 4 hours to back up and shrink the log and get the system up and running again.
I told my boss what had happened and now he wants to know who did it. I told him it could've possibly been me, but I highly doubt it because it appears to have been disabled between 7-8pm on my "work from home" day, which I specifically remeber packing it up and playing xbox at 4:30pm.
Could someone help me figure out what happened and if there it is possible to track these changes in the future?
Has anyone ever seen TCP/IP become a disabled protocol without manual intervention from a user?
My websites just stopped talking to my MSSQL db and when I went into sql client config utility on the web boxes, tcp/ip was disabled. Any clues? Can't find anything to support this in the event or web logs.
While in the design window of a table, I click the properties button and click the Relationships tab. Here everything is grayed out, and I can't drag and drop to create relationships in my Diagram either. Its like things are locked up right now. Can any of you give me some direction? thanks
hi, all my toolbars except for the standard toolbar in SSRS are disabled. I cannot use anything. For example, I selected the formatting toolbar and it appears on the bottom of the standard toolbar, but I cannot select anything because it's all greyed out. Any suggestions. Thanks.
Environment: Visual Studio V8.0.50727.42 (RTM.050727-4200) Installed Products: Visual Studio Team Explorer V8.0.50727.127
The Variables are not available to the IDE. I cannot 1) Open the Variables Window (View > Other Windows > Variables), 2) Add or Edit Variables (SSIS > Variables).
I am preparing to re-install Visual Studio. Has anyone else seen this behavior? Is it a "feature" of some sort?
I having problem of using SQL Server management studio to create a database. Database have alot of colume, if i want to make column 1 to disabled, may i know how to set it?
The report functionality is working fine with the provided parameters,but the problem is one of the parameter Asofday is getting disabled until then servername and drive are provided and a click on the other remaining three parameters.
i have default for this asofday for today, and i feel that because of default parameter only it is getting disabled. i removed the default and is working fine wihtout getting disabled.
As this Asofday is of thirdplace in parameters list, i moved it to firstplace,it works fine here also.
What is happening when i supplied default and moved to third place?
If any one of you experienced any issue like this.pls share .
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.
Hi, I'm using SQL Server 7.0. I'd like to know if there is anywhere in the information schema or system tables where I can tell that a constraint has been flagged as 'enable constraint for INSERT AND UPDATE' or not.
I have several alerts set up and a job setup to notify via email if alerts occur. There are times when I might be doing maintenace on a box that may generate alerts but I don't want to get emailed or paged for these "dummy" alerts. I thought first of setting the enabled column of sysalerts to 0 - that doesn't work. I still get notified. Then I thought I'd set the enabled column in sysjobs for my notification job to 0 while I'm doing the work. But as soon as I re-enable the job I get an alert. It seems that as soon as the job is re-enabled it checks the NT application log and sees that an alert was fired and somehow knows that there was no notification sent so sends one. If I purge the NT application log before re-enabling the job is solves the problem. But that is a very in-ellegant solution and I don't want to loose all the info stored in the event log any way. Does anyone know how SQL SErver aggent keeps track of this stuff? (ie. that an alert was fired but it's response job is disabled and to check once a job is re-enabled to see if any alerts occured while it was disabled?) I did a Profiler trace while re-enabling the job but didn't see anything pertinant in there.
1) SQL Executive stops running jobs 2) The only way I can get it to START running jobs is to reboot the server 3) When SQL Server and executive come back up, a number of the tasks have been DISABLED! (I have seen this happen over and over on my SQL 6.5 SP4 instances.)
If you have seen this situation, have you found a way to prevent it? (ie: procedure or bug fix, etc.)
Just curious to hear if anyone else has experiences this or knows a reason why it maybe should be this way...
I have some jobs that are disabled accourding to the Job Activity Monitor view in Mgmt Studio. However, if I open the schedules details, this view says Enabled. Can a schedule be enabled for a job, even if the job in itself is disabled maybe? But, I can't see any enable/disable flag in the sysjobschedules table, only in sysjobs.
Hi, I have been scheduling jobs from past couple months on production but it does not execute on the scheduled time.
I checked the schedule enabled tab, it was enabled when i scheduled the job, but was disabled at a later date when i checked to see why the job did not run.......
Can some one please guide me if there is any process in SQL which disables the jobs automatically.
I want to find out how the jobs when scheduled and enabled gets disabled on its own.
Is there something that would cause a SQL Login to get disabled automatically? The login was used successfully yesterday but today we were getting an error:
Login failed for user '<sql login>'. Reason: The account is disabled. [CLIENT: ipaddress]
Perhaps someone accidentally disabled this but that sounds unlikely.
I didn't see anything in the log about the account getting disabled.
my SB configuration involves 3 servers. Server A sends a message to Server B. there's a stored proc on Server B that is being activated (using Activation), does some processing and sends the message to server C. on C there's another SP that is activated and it's supposed to insert a record into a table.
My problem is that on Server C, the record doesn't get inserted when activation is set to ON.
when activation is off on C and I run the proc manually, everything works fine, but when activation is on, the record doesn't get inserted. after this happened I tried to fire the proc manually and I got the following error :
Msg 9617, Level 16, State 1, Procedure spMSG_ReceiveMessagesOnSubscriber, Line 52
The service queue "TargetQueue" is currently disabled.
I am using the following code for activation on server C :
I have an event in my sql server 2005 box that is fired every 5 seconds. Source MSSQLSERVER event ID 9274.
The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: 'The service queue "ExternalMailQueue" is currently disabled.'
I don't know anything about this queue that is mentioned above. How do I disable this message or get rid of that queue?
I receive messages in my target queue but target queue continously become disabled even aften I enable and receive message it still says target queue is disabled?
Should I disable IIS on SQL Server 2005? Is there any reason that I should keep it around? I know that it is necessary for reporting services, but our database servers are on a seperate server from our reporting server. We have reporting services running on a stand alone box by itself.
I have a windows service that uses SqlDependency. It has been working fine until today where it has died. I have traced the problem back to the service broker being disabled, however I have not disabled the service broker. Is there anyway that the service broker can become disabled ?
I am building a website with VS2005Standard. Onto an empty aspx page I drag a SqlDataSource control. In the "Configure datasource" dialogs I connect it with the SQL database file of my web app. In the "Configure the SQL statement" tab I select "*" for the columns and open the "advanced" dialog (I don't know the exact english terms because I use the german version) because I want to create INSERT, UPDATE AND DELETE statements separately. BUT: the upper checkbox (i.e. both checkboxes) is disabled! What can I do the get it enabled.
I'm attached to two different SQL 7 servers and neither one has the options enabled for full-text indexing. I am the DBO for those databases and the services are running.
Any ideas what's missing? I'm using SQL 7 clients tools on my NT Workstation.
Also, are there samples of using the index/search within ASP?
I manage a remote SQL Server 7 box that has the full-text engine installed and running great. However, I am unable to access the full-text options from the Enterprise Manager remotely through my firewall from my NT Workstation or from another NT Server with the EM installed. ALL other functions of the Enterprise Manager work fine through the firewall.
When using the Enterprise Manager from a Win98 machine at home, I can connect to the remote server and all the full-text options are enabled. So... I deduce that the firewall must be the problem.
Does anyone have any clues about this? Do the full-text options via the MMC use a different port than the 1433? Does it require the RPC ports to be opened on the firewall (not likely to happen)? I can find no documentation that states that the full-text options have any port requirements different than those of the normal SQL Server management options.
If a job is currently running and I want to disable it so that it does not run as scheduled for the following night, will it disrupt the current process?
