Alter Index On A Remote Database
Nov 5, 2007
I have created a stored procedure in msdb that will accept a database name and then rebuild non-clustered indexes in that database.
I placed the sp in msdb since msdb is where most job-related information is stored.
The sp identifies non-clustered indexes whose avg fragmentation exceeds a pre-determined threshold, and then runs the following statement for each index in the resulting subset:
SET @str_sql = 'ALTER INDEX ' + @str_index + ' ON ' + @str_object + ' REBUILD '
EXEC sp_executesql @str_sql
where @str_index is the name of the index and @str_object is the name of the object (view, table).
Clearly, I need to revise the statement so that it refers to the appropriate database.
Is there a way to execute 'alter index ... rebuild' so that it points to a database other than the current?
Thanks.
View 3 Replies
ADVERTISEMENT
Jan 2, 2007
hey guys for some reason when i run this:
USE WebPageLog;
GO
ALTER INDEX PK_TNS_report ON TNS_report
REBUILD;
GO
then i check index fragmentation its still the same 50% why is that?
View 3 Replies
View Related
Nov 5, 2007
I am getting an error below when trying to use online indexing.
The error returned list a series of datatypes not supported for ONLINE=ON
The problem is the datatype referred in the table is varbinary which is not listed.
Also I created a test table with a varbinary index and all is good in the hood.
Can someone help with the below error.
Thanks in advance.
Database: Adventureworks
Microsoft SQL Server 2005 - 9.00.2047.00 (X64) Apr 14 2006 01:11:53 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
ALTER INDEX ALL
ON Production.[Document] REBUILD
WITH (ONLINE = ON, SORT_IN_TEMPDB = ON, MAXDOP = 2)
Msg 2725, Level 16, State 2, Line 1
Online index operation cannot be performed for index 'PK_Document_DocumentID' because the index contains column 'Document' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.
Thank you,
Adam
View 9 Replies
View Related
May 1, 2008
If the index to be rebuilt is a clustered index, will all non-clustered indexes be rebuilt also by rebuilding only the clustered index.
View 7 Replies
View Related
Sep 14, 2007
Hi all
We are still testing SQL 2005. We changed all the CREATE INDEX jobs to ALTER INDEX. we ran the index jobs and noticed a lot of blocking. The jobs eventually completed successfully though. The spids were blocking themselves. And this was on and off. I qould query sysprocesses and see the blocking intermittently. I didnt notice this when we run the same indexing jobs in 2000. Is this the way ALTER INDEX works in 2005 or is this something we need to be concerned about? We have build 3186. I need to mention that the jobs finished rather quicker as compared to 2000. And the spids were blocking themselves not other spids.
Thanks for the help.
Don
View 4 Replies
View Related
Nov 16, 2007
Does "Alter Index All Rebuild" rebuild just the tree level of the index? Or does it also rebuild the leaf level like reorganize?
Thanks and God Bless,
Thomas
ThomBeaux
View 1 Replies
View Related
Dec 5, 2007
If you perform an Alter Index All without the FillFactor (example below) and the previous fill factor for the table and it's index were (80%), will the fillfactor be set to the previous value (80) or the sql server default index fill factor value (0 or 100%)?
ALTER INDEX ALL ON [Sales Header] REBUILD
WITH (SORT_IN_TEMPDB=ON, STATISTICS_NORECOMPUTE=OFF)
Thanks,
Ian
View 3 Replies
View Related
Mar 13, 2007
I was checking out an indexes property via SQL 2005 Management Studioand it looks like I can move the index to other filegroups via thedrop down.I checked the BOL ALTER INDEX and I didnt see an argument for thisaction. I see stuff about PARTITION, but I think that is for rowpartitioning??I want to move existing indexes from the PRIMARY filegroup to a newfile group just for indexes, which is called INDEXES.Can this be done via ALTER INDEX or some other way?TIARob
View 1 Replies
View Related
Aug 30, 2007
I have read through BOL but am still confused by the above sql. I think it rebuilds all indexes on a table. Am I correct? (If so, if would seem to be a good thing to run it nightly on all tables in all databases. Or maybe that is too extreme)
Barkingdog
View 1 Replies
View Related
Oct 22, 2007
In SQL Server 2000 one could DBReindex every index that exists in a given database. You can do the same in SQL Server 2005. But how can this be done with the new Alter Index command? It does not allow me to pass in a variable for the object. Any ideas on how to get this done in with Alter Index in 2005? Thanks!
This I can't get to work:
DECLARE
@TableName nvarchar(100)
SET @TableName = 'Account'
USE database;
GO
ALTER INDEX ALL ON @TableName
REBUILD
GO
USE RZTQ5OL02
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT Name from sys.sysobjects where type = 'U'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
View 3 Replies
View Related
Jul 9, 2007
I do a lot of index maintenance and started testing rebuilding my indexes with the ONLINE option, but noticed an increase in record size by 14 bytes. The issue I have is that for each index you build the ONLINE option, you add 14 bytes for row. After the index rebuild is done, normal transaction updates drop the additional 14 bytes.
The act of adding and dropping 14 bytes would cause fragmentation and uncompressed data. Is this correct or do I have something wrong?
View 1 Replies
View Related
Sep 4, 2007
I am using sys.dm_db_index_physical_stats to identify indexes that need to be rebuilt based on a fragmentation limit. Once identified, I execute and ALTER INDEX... REBUILD on the index. If the index is clustered, only that index gets rebuilt for the table. After all the indexes are complete, I receive a report on the indexes that were rebuilt in the databases and what level of fragmentation the index was at before rebuilt. After checking these indexes, I still see that all the Primary Key indexes are still at the same fragmentation level. I run the process again and it does not change. I updated table usage and also ran update statistics after running the rebuild again, but the fragmentation does not change. Why can€™t these PK Clustered indexes be rebuilt as expected? Do I need to drop and recreate the PK before this fragmentation changes?
View 7 Replies
View Related
Mar 10, 2007
I thought I would delve into index fragmentation and I found somegreat sql from many posters (thanks Erland!).My question is how bad is bad? I know this is very subjective.Some scripts I found would reindex if the LogicalFragmenation is over30%.I have some tables that are 98% (I'm guessing really bad). I know itall depends..more as a learning point: I found a table that had over 30%logicalfragmentation, I dropped the indexes, created then ran thescript that used type code segment:'DBCC SHOWCONTIG(' + @TableName + ') WITH TABLERESULTS, ALL_INDEXES,NO_INFOMSGS')In one case, the indexes for the table dropped below 30%, in anothercase the index was still fragmented ever after I dropped and re-created index.SQL Server 2005 x64 SP2This is the script I am running (I found this in another thread thatErland posted):SET NOCOUNT ONUSE ds_v6_sourceDECLARE @TableName VARCHAR(100)-- Create a table to hold the results of DBCC SHOWCONTIGIF OBJECT_ID('Tempdb.dbo.#Contig') IS NOT NULLDROP TABLE #ContigCREATE TABLE #Contig ([ObjectName] VARCHAR(100), [ObjectId] INT,[IndexName]VARCHAR(200),[IndexId] INT, [Level] INT, [Pages] INT , [Rows] INT ,[MinimumRecordSize] INT,[MaximumRecordSize] INT , [AverageRecordSize] INT,[ForwardedRecords] INT ,[Extents] INT, [ExtentSwitches] INT, [AverageFreeBytes]NUMERIC(6,2),[AveragePageDensity] NUMERIC(6,2), [ScanDensity]NUMERIC(6,2) ,[BestCount] INT ,[ActualCount] INT , [LogicalFragmentation] NUMERIC(6,2) ,[ExtentFragmentation] NUMERIC(6,2) )DECLARE curTables CURSOR STATIC LOCALFORSELECT Table_NameFROM Information_Schema.TablesWHERE Table_Type = 'BASE TABLE'OPEN curTablesFETCH NEXT FROM curTables INTO @TableNameSET @TableName = RTRIM(@TableName)WHILE @@FETCH_STATUS = 0BEGININSERT INTO #Contig EXEC('DBCC SHOWCONTIG(' + @TableName + ') WITHTABLERESULTS, ALL_INDEXES, NO_INFOMSGS')FETCH NEXT FROM curTables INTO @TableNameENDCLOSE curTablesDEALLOCATE curTables
View 8 Replies
View Related
Oct 10, 2014
On a 2008r2 server, I ran the frag utility against master and msdb and noticed they were severely fragmented.
Is it ok to defrag them using the standard Alter Index command?
View 7 Replies
View Related
May 20, 2015
I'm working on re-indexing a table using some commands from ALTER INDEX REBUILD from Microsoft. The indexes will be tested for threshold fragmentation. my plan is once the reindex is executed, a transaction backup will occur while controlling the size of the log file. Â The query impose time limitations or stop reindexing after the specified amount of time has elapsed.
my question,
1. How can I integrate a query which checks if transaction log is getting full and which runs a Tlog backup if over 70%
2. How do I impose time limitation?
View 3 Replies
View Related
Jul 10, 2013
Problem ms sql server 2012 not using remote index on sql server 2000 linked server with odbc
Let say my servers are:
srvdb and remotesrv
when do select on srvdb:
select * from remotesrv.database.dbo.inventory this select not using index on remoteserver
but if this select is on remoteserver like
select * from database.dbo.inventory, this select use index PK_Inventory (clustered)
View 5 Replies
View Related
Sep 5, 2006
I want a database user to be able to alter login, database user and database role from my application. so, i assigned that user to sccurityadmin server role, db_accessadmin and db_securityadmin database roles....By now, the user can add or remove login and database user. However, the user cannot add or remove any database role membership. What am I missing here?? What should I do so that the user can create, and alter database roles in the database??
View 1 Replies
View Related
Jul 20, 2015
I have a script that automates some db drop/restore operations and bringing the database to single user mode is part of it: ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE...I want this to executes under a login, that has restricted privileges, so I've created a login and granted it a dbcreator role + ALTER ANY DATABASE privileges.
Problem: When I run the script against a database with an active/sleeping connection:It fails when using the restricted login: "Msg 5061...ALTER DATA BASE failed because a lock could not be placed on database ..."It completes successfully when using a sysadmin login According to stackoverflow.com the solution is to kill the active/sleeping connections to the database, before ALTER-ing it, which works fine, but the question is....
Questions: Why the "ALTER DATABASE..." statement works under the sysadmin login, but not under a dbcreator one?Does this mean the sysadmin login kills the connections to the target database in the background?Is it possible to grant additional privileges to the restricted login, so the "ALTER DATABASE..." statement won't need preventive killing of the connections?
View 5 Replies
View Related
Aug 25, 2000
I am using SQL 6.5. Can I create a database and Alter the same
database in one SQL Script?
Thanks in advance.
View 1 Replies
View Related
Jul 23, 2005
Hi people,I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS 2K.Example:table : item_nota_fiscal_forn_setor_publicofield : qtd_mercadoria integer NOT NULLALTER TABLE item_nota_fiscal_forn_setor_publicoALTER COLUMN qtd_mercadoria decimal(12,4) NOT NULLBut, It doesn't work. A sintax error rises.I need to change that field in a Visual Basic aplication, dinamically.How can I do it? How can I create a decimal(12,4) field via script in MSACCESS?Thanks,Euler Almeida--Message posted via http://www.sqlmonster.com
View 1 Replies
View Related
Jan 23, 2001
I am trying to move one of my database log to different location but I am geeting an error when I try to do this.
I am moving Registration DB log file from 'c:mssql7
egistrstion_log.ldf'
to 'e:sql
egistration_log.ldf'
Here the syntax I am using to do this:
alter database registration modify file (name='registration_log',filename= 'e:sql
egistration_log.ldf')
But I get the following error:
Server: Msg 5037, Level 16, State 1, Line 2
MODIFY FILE failed. Do not specify physical name.
Thank You,
John
View 1 Replies
View Related
Jul 31, 2006
Hi all this is my first of most likely many posts.
I am writting a stored proc that will be used on many diffrent named databases and i am setting the database name with a variable. I would like to use this variable in the following situation any clues on this would be greatly appreciated.
DECLARE @DBName varchar(50)
SET @DBName='Database'
alter database @DBName set recovery full
regards
Phil
View 4 Replies
View Related
Apr 25, 2008
Hey,
I am trying to give a user permission to change the name of a database.
They are a member of the server role 'secuirtyadmin'
They are a member of the database role 'db_owner'
They have been granted ALTER permission on the database.
But every time I try and run the command
ALTER DATABASE <dbname> MODIFY NAME = <newdbname>
I get an error saying the database doesn't exist or you don't have permissions.
What am I missing?
View 7 Replies
View Related
Mar 13, 2003
Hello,
I have Test database with Log file property Automatically grow the file, option Unrestricted file growth, I wanted to alter it with Restrict file growth upto 200 MB. I'm getting an error that you cannot modify as file doesnot exist
But the file does exist. I cannot figure out what is giving the problem.
below is the sql statements
select name from sysfiles
go
Test_Data
Test_Log
USE master
go
ALTER DATABASE Test MODIFY FILE
( NAME='Test_Log',
MAXSIZE=200MB )
go
ERROR
Server: Msg 5041, Level 16, State 1, Line 1
MODIFY FILE failed. File 'Test_Log' does not exist.
Any help is appreciated.
Thanks
Sejal
View 3 Replies
View Related
Jan 22, 2004
Hi
I got the following error
Error: 823, Severity: 24, State: 4
I/O error 33(The process cannot access the file because another process has locked a portion of the file.) detected during write at offset
0x0000000a796000 in file xxxxxxxxx.ndf'.
and the respective database could not be brought online - this was just due to a problem with a .ndf file containing only indexes...is there any way to connect to/alter a database while it is in this transitional state? (it would be no loss if i could just remove the file & its filegroup)
(i tried starting with -f -c, but no go)
thanks in advance
des
View 3 Replies
View Related
Apr 7, 2006
I have a *.sql script that creates database tables, and I need to modify the database to enable the service broker. In addition, the actual name of the database is not known in advance - it is set per instance of the application.
I know I need to do:
ALTER DATABASE dbname SET ENABLE_BROKER
But I must avoid including the name of the database in the script. I did wonder if this would work:
DECLARE @DB varchar(50)
SELECT @DB = DB_NAME()
ALTER DATABASE @DB SET ENABLE_BROKER
But I just get a syntax error. Presumably this also rules out setting the database name as a parameter to the script (SqlParameter stuff)
The only option I can think of is dynamically creating the statement, either in T-SQL or in the calling .NET environment.
Any thoughts?
Ruth
View 6 Replies
View Related
Oct 25, 2006
I've read that when this run's, it removes all db mirroring information on that db. What exactly does it remove?
Here's my senario:
We are using SQL 2005€™s db mirroring process. We are using the certificate method of authentication between the principle and the mirror db€™s.
My question is that when the ALTER DATABASE dbname SET PARTNER OFF is run, does it remove these certificate settings as well? In other words when I want to enable the db mirroring, will I need to recreate these certificates or just recreate the endpoints to use these certificates?
View 5 Replies
View Related
Apr 26, 2003
How can I do an alter table in some table that replicated database ?
I got the error message when I try !
View 3 Replies
View Related
Jan 15, 2007
Conrad writes "I'm currently working on Table Partitioning. I have done everything succesfull for partitioning, what I'm struggling with is to use
" SELECT MAX(NAME) from sys.filegroups WHERE NAME NOT LIKE 'PRIMARY' " to get the last used FileGroup. Now this works just fine, but when I run the following script:
--Decalre variables
DECLARE @LastFilegroupName VARCHAR(50)
DECLARE @FilegroupName VARCHAR(50)
--Retuns the next FileGroup to be used
SET @LastFilegroupName = (select MAX(NAME) from sys.filegroups WHERE NAME NOT LIKE 'PRIMARY')
SET @LastFilegroupName = Replace(@LastFilegroupName,'FileGrp','')
SET @FilegroupName = 'FileGrp' + CAST((@LastFilegroupName + 1) as varchar(10))
--Alter database statement
ALTER DATABASE VadivelTesting
ADD FILEGROUP @NewFG_Name
This script gives the following error "Incorrect syntax near '@NewFG_Name'."
When I give it a static name it works fine, but not with the variable.
Please can someone help me, I'm in struggeling with this one."
View 1 Replies
View Related
Dec 6, 2007
We're upgrading to SQL Server 2005, and we've restored databases from SQL Server 2000 to the development server (SQL Server 2005). Part of what we need to do is alter the database collation to SQL_Latin1_General_CP1_CI_AS; our SQL Server 2000 server is case-sensitive.
My initial attempts to alter the database;
alter database RGDWDEV collate SQL_Latin1_General_CP1_CI_AS
failed because of the presence of computed columns and user functions. So, I wrote scripts to save the definitions of the computed columns and functions, and wrote scripts to drop them. Now that they are gone, I expected to be able to successfully execute the above 'alter database' statement, but I am now getting this error;
Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'sys.sysschobjs' and index name 'nc1'. The duplicate key value is (0, 1, DISB_ADJ).
Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'RGDWDEV' cannot be set to SQL_Latin1_General_CP1_CI_AS.
I do not know how to fix this.
Can anyone help me?
thank you, beth
View 9 Replies
View Related
Dec 1, 2005
If I execute the command ALTER DATASE SET MULTI_USER WITH ROLLBACK IMMEDIATE and there are any connections to the database, the command fails with a "Lock request time out period exceeded." message. If I use SET RESTRICTED_USER, the command succeeds with the following message: "Nonqualified transactions are being rolled back. Estimated rollback completion: 100%." This seems to be a bug.
View 5 Replies
View Related
Apr 20, 2007
Hi.
I'm very new to DB stuff. I've created a SQL Server CE database and I'm trying to implement a query notification using a SqlDependency object, as per the instructions at:
http://www.codeproject.com/cs/database/chatter.asp
I've connected to my DB using SQL Server Management Studio Express, and I'm getting the following error when trying to execute an ALTER DATABASE command:
I have no clue what I could be doing wrong. Is this operation supported using CE? Is there really something wrong with my syntax?
Major Error 0x80040E14, Minor Error 25501
> alter database GESim.Diagnostics set ENABLE_BROKER
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = database ]
Help appreciated...
Just Mike
View 3 Replies
View Related
