http://msdn2.microsoft.com/en-us/library/ms188917.aspx
This script keeps failing on line 22 on a ) but i do not see where it is erroring.
I put in USE DBNAME
and run it and it fails.
Any suggestions
The script under D:
D. Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes
The following example automatically reorganizes or rebuilds all partitions in a database that have an average fragmentation over 10 percent. Executing this query requires the VIEW DATABASE STATE permission. This example specifies DB_ID as the first parameter without specifying a database name. An error will be generated if the current database has a compatibility level of 80 or lower. To resolve the error, replace DB_ID() with a valid database name. For more information about database compatibility levels, see sp_dbcmptlevel (Transact-SQL).
Copy Code
-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
I am trying to create a new full text index on some table and getting the follwing error
"The Microsoft Search service
cannot be administered under the present user account"
i have already checked that Microsoft Search service is properly installed with the server version of SQL Server and this is running under the system account
could anyone plz comeout with the solution to this problem]
This calls the Sp that does the Reindex. It fails at the update statistics with a very generic message. like " Command: UPDATE STATISTICS [xxxx_DB].[dbo].[xxxx_xxx] [_WA_Sys_00000007_49C3F6B7] [SQ... The step failed."
I suspect it has more error but this is all it is showing me when I right click on the job history. therefore, I updated the job step in the advance tab with log to a txt file. Am I on the right track or there is another way to see error some where else.
I looked at the logs but they didn't show any thing.
We have a new database with cdc enabled on all of its tables. This causes the index maintenance task to fail with following message:
"Executing the query "EXEC DBName.dbo.IndexDefrag_sp" failed with the following error: "The unique index 'PK_TableName' on source table '[dbo].[TableName]' is used by Change Data Capture. To alter or drop the index, you must first disable Change Data Capture on the table. The transaction ended in the trigger. The batch has been aborted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly" We would like to run the index maintenance without losing the cdc data. We plan on installing SP2 on SQL Server 2008 R2 soon, would that solve the issue? Disabling the cdc prior to index maintenance and then re-enabling back upon completion; would delete the data as I found in most discussions, but we would like to retain it.
please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio
We have been using Sql Server 2005 Compact Edition 3.1 RDA synchronization method successfully on Sql Server 2000 database. Recently we moved the database to Sql Server 2005, sync doesn't work anymore, it just hangs on one table. On further investigation, we found out that it's the index on that table that causes this. We removed the index, it works fine. We are wondering the root cause, removing the index is not a solution for us. Any thoughts?. Thanks.
I did a small package with only one ODBC connection (Merant 3.70 32-Bit Progess). This package runs well in Visual Studio and fails when runs by SQL Server Agent.
Configuration:
SQL Server Agent on a 32Bit server.
The ODBC connection configuration in available on System DSN on this server.
The user of Server Agent have full access (Admin).
Connect Manager Provider: ".Net ProvidersOdbc Data Provider"
SQL Server version: 9.0.3042
Error Message:
Executed as user: TEKCON cadmin. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 16:50:33 Error: 2007-06-11 16:50:33.62 Code: 0xC0047062 Source: Data Flow Task DataReader Source [1] Description: System.Data.Odbc.OdbcException: ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcCon... The package execution fa... The step failed.
I created a .bat file with this instruction and It's run well:
I am using Full Text Index to index emails stored in BLOB column in a table. Index process parses stored emails, and, if there is one or more files attached to the email these documents get indexed too. In result when I'm querying the full text index for a word or phrase I am getting reference to the email containing the word of phrase if interest if the word was used in the email body OR if it was used in any document attached to the email.
How to distinguish in a Full Text query that the result came from an embedded document rather than from "main" document? Or if that's not possible how to disable indexing of embedded documents?
My goal is either to give a user an option if he or she wants to search emails (email bodies only) OR emails AND documents attached to them, or at least clearly indicate in the returned result the real source where the word or phrase has been found.
Web Base application or PDA devices use to initiate the order from all over the country. The issue is this table is not Partioned but good HP with 30 GB RAM is installed. this is main table that receive 18,0000 hits or more. All brokers and users are using this table to see the status of their order.
The always search by OrderID, or ClientID or order_SubNo, or enter any two like (Client_ID+Order_Sub_ID) or any combination.
Query takes to much time when ever server receive more querys. some orther indexes are also created on the same table like (OrderDate, OrdCreate Date and Status)
My Question are:-
Q1. IF Person "A" query to DB on Client_ID, then what Index will use ? (If any one do Query on any two combination like Client_ID+Order_ID, So what index will be uesd.? How does MS-SQL SERVER deal with these kind of issues.?
Q2. If i create 3 more indexes on ClientID, ORderID and OrdersubID. will this improve the performance of query.if person "A" search record on orderNo so what index will be used. (Mind it their would be 3 seprate indexes for Each PK columns) and composite-Clustered index is also available.?
Q3. I want to check what indexes has been used? on what search?
Q4. How can i check what table was populated when, or last date of update (DML)?
My Limitation is i Dont Create a Partioned table. I dont have permission to do it.
In Teradata we had more than 4 tb record of CRM data with no issue. i am not new baby in db line but not expert in sql server 2003.
My SSIS package is running very slow taking so much time to execute, One task is taking 2hr for inserting 100k records, i have disabled unused index still it is taking time.I am rebuilding/Refreshing indexes and stats once in month if i try to execute on daily basis will it improve my SSIS Package performance?
hello friends i have table1 and 200 coulumn of table1 :) i have 647.600 records. i entered my records to table1 with for step to code lines in one day :) i select category1 category2 and category3 with select code but i have just one index.. it is productnumber and it is primarykey..So my select code lines is so slow.. it is 7-9 second.. how can i select in 0.1 second ? Should i create index for category1 and category2 and category3 ? But i dont know create index.. My select code lines is below.. Could you learn me and show me index for it ?? or Could you learn me and show me fast Select code lines and index or etc ??? Also my search code line have a dangerous releated to attaching table1 with hackers :) cheersi send 3 value of treview1 node and childnode and child.childnode to below page.aspx :) Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not Me.IsPostBack Then If Request("TextBox1") IsNot Nothing ThenTextBox1.Text = Request("TextBox1") End If If Request("TextBox2") IsNot Nothing ThenTextBox2.Text = Request("TextBox2") End If If Request("TextBox3") IsNot Nothing ThenTextBox3.Text = Request("TextBox3") End If End If Dim searchword As String If Request("TextBox3") = "" And Request("TextBox2") = "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "'" End If If Request("TextBox3") = "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "'" End If If Request("TextBox3") <> "" And Request("TextBox2") <> "" And Request("TextBox1") <> "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "' and kategori2= '" & Request("TextBox3") & "'" End If SqlDataSource1.SelectCommand = searchword End Sub
I'm running a merge replication on a sql2k machine to 6 sql2k subscribers. Since a few day's only one of the merge agents fail's with the following error:
The merge process could not retrieve generation information at the 'Subscriber'. The index entry for row ID was not found in index ID 3, of table 357576312, in database 'PBB006'.
All DBCC CHECKDB command's return 0 errors :confused: I'm not sure if the table that's referred to in the message is on the distribution side or the subscribers side? A select * from sysobjects where id=357576312 gives different results on both sides . .
Hi everyone, When we create a clustered index firstly, and then is it advantageous to create another index which is nonclustered ?? In my opinion, yes it is. Because, since we use clustered index first, our rows are sorted and so while using nonclustered index on this data file, finding adress of the record on this sorted data is really easier than finding adress of the record on unsorted data, is not it ??
I have a clustered index that consists of 3 int columns in this order: DateKey, LocationKey, ItemKey (there are many other columns in this data warehouse table such as quantities, prices, etc.).
Now I want to add a non-clustered index on just one of the other columns, say LocationKey, like this: CREATE INDEX IX_test on TableName (LocationKey)
I understand that the clustered index keys will also be added as key columns to any NC indexes. So, in this case the NC index will also get the other two columns from the clustered index added as key columns. But, in what order will they be added?
Will the resulting index keys on this new NC index effectively be:
LocationKey, DateKey, ItemKey OR LocationKey, ItemKey, DateKey
Do the clustering keys get added to a NC index in the same order as they are defined in the clustered index?
Quick question about the primary purpose of Full Text Index vs. Clustered Index.
The Full Text Index has the purpose of being accessible outside of the database so users can query the tables and columns it needs while being linked to other databases and tables within the SQL Server instance. Is the Full Text Index similar to the global variable in programming where the scope lies outside of the tables and database itself?
I understand the clustered index is created for each table and most likely accessed within the user schema who have access to the database.
Is this correct?
I am kind of confused on why you would use full text index as opposed to clustered index.
One of the major syntax inside the SELECT statment is ..
WHERE FIELDA IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='A') AND WHERE FIELDB IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='B') AND WHERE FIELDC IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='C') AND WHERE FIELDD IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='D') AND WHERE FIELDE IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='E') AND WHERE FIELDF IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='F')
(It's to compare the field content with some user input parameter inside a parameter table... )
I think properly is that the SELECT ... IN is causing much slowness in the sql statement. I have indexed FIELDA , FIELDB, FILEDC etc and those PARAVALUE and PARATYPE in the PARATABLE table. But perfromance is still slow and execution takes >20 seconds for 200000 rows of records.
Do any one know if still any chance to improvide the performance like this?
I'm trying to find whether there is a dmv or system view that can help me see the last time an index was rebuilt or created. Assuming I rebuilt an index using tsql commands (not a job with a history), is there a way to find out the last time that index was rebuilt?
We are trying to install the Standard Edition of SQL Server 2005. We already have SQL Express installed. Standard won't install and Express won't uninstall.
We have tried every suggestion we can find and some we made up. When we try to install Standard, we get to the bit where you choose what you want to install and the only thing available is workstation components.
There are so many cases of this on the Internat that I can't believe MS have not solved the issue. And I am stunned that they want to charge us more money to talk to us about how to fix something we just paid them an arm and a leg for!
What do I need to tell you to get some idea of where to go next with this?
SELECT a.AssetGuid, a.Name, a.LocationGuid FROM Asset a WHERE a.AssociationGuid IN ( SELECT ada.DataAssociationGuid FROM AssociationDataAssociation ada WHERE ada.AssociationGuid = '568B40AD-5133-4237-9F3C-F8EA9D472662')
takes 30-60 seconds to run on my machine, due to a clustered index scan on our an index on asset [about half a million rows]. For this particular association less than 50 rows are returned.
expanding the inner select into a list of guids the query runs instantly:
SELECT a.AssetGuid, a.Name, a.LocationGuid FROM Asset a WHERE a.AssociationGuid IN ( '0F9C1654-9FAC-45FC-9997-5EBDAD21A4B4', '52C616C0-C4C5-45F4-B691-7FA83462CA34', 'C95A6669-D6D1-460A-BC2F-C0F6756A234D')
It runs instantly because of doing a clustered index seek [on the same index as the previous query] instead of a scan. The index in question IX_Asset_AssociationGuid is a nonclustered index on Asset.AssociationGuid.
The tables involved:
Asset, represents an asset. Primary key is AssetGuid, there is an index/FK on Asset.AssociationGuid. The asset table has 28 columns or so... Association, kind of like a place, associations exist in a tree where one association can contain any number of child associations. Each association has a ParentAssociationGuid pointing to its parent. Only leaf associations contain assets. AssociationDataAssociation, a table consisting of two columns, AssociationGuid, DataAssociationGuid. This is a table used to quickly find leaf associations [DataAssociationGuid] beneath a particular association [AssociationGuid]. In the above case the inner select () returns 3 rows.
I'd include .sqlplan files or screenshots, but I don't see a way to attach them.
I understand I can specify to use the index manually [and this also runs instantly], but for such a simple query it is peculiar it is necesscary. This is the query with the index specified manually:
SELECT a.AssetGuid, a.Name, a.LocationGuid FROM Asset a WITH (INDEX (IX_Asset_AssociationGuid)) WHERE a.AssociationGuid IN ( SELECT ada.DataAssociationGuid FROM AssociationDataAssociation ada WHERE ada.AssociationGuid = '568B40AD-5133-4237-9F3C-F8EA9D472662')
To repeat/clarify my question, why might this not be doing a clustered index seek with the first query?
Found out a while back that my facts-tabel has an non-clustered index on its facts_id. In a bunch of procedures an update is executed against a facts_id unfortunately on it's facts-table. I was wondering if changing it into a clustered index is worth the effort / would make sense considering a +110 million facts and re-indexing the other indexes as well? Facts are loaded sequentially, so I would suspect them facts are in the ordered already?
I just ran the Database Engine Tuning Advisor on a relative complex query to find out if a new index might help, and in fact it found a combination that should give a performance gain of 94%. Fair enough to try that.
What I wonder about: The index I should create contains 4 columns, the last of them being the Primary Key column of the table, which is also my clustered index for the table. It is an identity integer btw.
I think I remember that ANY index does include the clustered one as lookup into the data, so having it listed to the list of columns will not help. It might at worst add another duplicate 4 bytes to each index entry.
Right? Wrong? Keep the column in the index, or remove it since it is included implicit anyway?
Hi, I have suddenly run into a problem. I have a DTS which was running well till few days ago. Now when ever it runs, it crashes the server, any body have any idea why???
I have created a Web assistant job which select and displays data from a table (very small amt of data) on to the webpage. the job is schedule to run every 1 minute. the job is not running, in job view history its gives the error msg "could not open the output file" 16821 Error no.
I've been running into a small problem. There are two people that usually create local packages to be executed by the DTS. Either my boss or I. The problem that I have been running into is, there are some local packages that will not execute when I try to run them. These packages are that my boss has created. When she runs them they work fine. But when I run them they just don't run. The only way I can run the local package is if I log in into the server and run it from there.
The error that I keep getting is:
Unspecified Error [Microsoft][ODBC Driver Manager] Data Source Name not found and no default driver specified.
What would I be able to do so that I can execute this local package from my computer?
I'm trying to run a job that will convert a load of data but it keeps failing at the first step. If I run the scripts in each step on their own then they run fine. The error message I get is 'The process did not produce any output. Process exit code 1. The step failed'. I think this may be some kind of security problem but I cannot see where. The command I'm using for the first step is as follows: