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?
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.
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.
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)
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
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)
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
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?
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?
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
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?
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??
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?
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
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
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.
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.
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 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.
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?
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."
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.
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.
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:
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 ]
