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?
Is it possible to use a variable to specify the filegroup in the ALTER/CREATE PARTITION SCHEME command?
I want the partition scheme to use the default filegroup for ALTER and CREATE PARTITION SCHEME. At the time the script is created, I don't know the default filegroup in the database.
My code:
declare @fileGroupName VARCHAR(50) = (select top 1 name from
sys.filegroups where is_default = 1)
ALTER PARTITION SCHEME MyScheme NEXT USED @fileGroupName
Is failing:
Incorrect syntax near '@fileGroupName'.
Q: Is it possible to use a variable for the filegroup in the ALTER/CREATE commands? Is so, what is the correct syntax?
Q: If using a variable is not possible, is there another way to specify the default filegroup?
I am new to sql server and we have sql server 2000
DBCC INDEXDEFRAG ( 0, PS_ORD_LINE, PS_ORD_LINE )
DBCC INDEXDEFRAG ( 0, PS_ORD_LINE, PS0ORD_LINE )
DBCC INDEXDEFRAG ( 0, PS_ORD_LINE, PSAORD_LINE )
DBCC INDEXDEFRAG ( 0, PS_ORD_LINE, PSBORD_LINE )
DBCC INDEXDEFRAG ( 0, PS_ORD_LINE, PSCORD_LINE )
DBCC INDEXDEFRAG ( 0, PS_ORD_LINE, PSEORD_LINE )
DBCC INDEXDEFRAG ( 0, PS_ORD_LINE, PSFORD_LINE )
Please somebody give me for sql server 2005 scripts for above.
I've noticed a huge transaction log size after having run an
index defragmentation. Does a defrag get written to the transaction
log really? (Assuming the full recovery model.)
Hi,
After issuing an index rebuild on a primary key index (and updating statistics), the index still shows a scan density of 12.5%!
Any ideas on why the rebuild doesn't seem to do anything on the fragmentation levels?
I'm using sql 2005
Thanx
I know the shortcut to 'defragging' the database to eliminate and
reclaim 'whitespace' in data and index pages is to setup a maintenance
plan. This doesn't work for me for two reasons:
1. I like to know how things work.
2. I can not create maintenance plans with my hosted provider.
I use the following T-SQL to rebuild all indexes on all tables:
-- exec sp_MSforeachtable "DBCC DBREINDEX ('?', ' ', 90)
But, does that not only address indexes? What about getting whitepace
back from data in tables themselves?
Am I over-thinking this?
When you're paying for SQL by the MB and generate a lot of data (with a
fair amount of churn) you want to keep it optimized and efficient,
right?
Does anyone know if dbreindex and index defrag ideally perform the same function? I have been told that index defrag does not hold locks on a table when executed and dbreindex does. Other than this is there any difference between the two functions? My understanding was that dbreindex reindexes the data stored in a table for faster reads and index defrag removes purged data. Am I correct? I am currently running both functions on my SQL server and was advised that I really only need to run the index defrag job. Is this advise correct?
View 6 Replies View RelatedI have a clustered index which shows as having a fragmentation level of 66% according to sys.dm_db_index_ physical_ stats.avg_ fragmentation_ in_ percent.
But no matter what I try the fragmentation level doesn't budge. And yes I'm updating the statistics after each attempt.Its not a huge issue the table only has 348 records. I'm testing a fixing fragmentation maintenance script. In Ironing out the syntax of my script I've fixed the fragmentation of indexes of over 65 % anyway..I've come across and index that I can't defragment. I've tried
ALTER INDEX ALL ON [GRIDINFO] REBUILD WITH (FILLFACTOR = 90)
I've tried
ALTER INDEX ALL ON [GRIDINFO] REORGANIZE
I've tried
DBCC INDEXDEFRAG (MYDATABASE, 'GRIDINFO', PK__GRIDINFO__3214EC2721F5FC7F);
The command complete successfully yet the avg_fragmentation_in_percent doesn't change. The table also has a nonclustered index. I've gleaned through all the statements of 'this will have no effect if' but so far I've not spotted a reason why this index won't defrag.The script has been modified to remove schema and database names for the forum.
dbcc showcontig('GRIDINFO','IDX_GRIDINFO1') with tableresults , all_levels
go
DBCC SHOWCONTIG ('GRIDINFO')
go
sp_helpindex 'GRIDINFO'
[code]....
I am currently re-writing an overnight index defrag procedure and would like to audit indexes in my database - logging the before defrag action" avg fragmentation value and "after defrag action" frag value in an audit table. This will be for all databases on the server. I have completed the vast majority of it (cycling though all the databases, detecting which indexes need reorganising or rebuilding and inserting the information into a table) but I cannot get the audit values working properly. For example, a sample row in my audit table would look like this:
ID Name DB Table frag_before frag_after
1 Index2 DB1 Table6 70.33456 0.03
2 Index7 DB1 Table9 45.98 1.2567etc
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.
We're just in the process of moving to 7.0, although SQL 7 has the autogrow feature we'd prefer to size MASTER and MSDB properly in the first place to avoid disk fragmentation. Does anyone have an experience/suggestions as to appropriate sizes, particulary for MASTER?
View 5 Replies View RelatedHi All,
I am working on a third party client product which shrinks all the databases in MS SqlServer based on the rule or parameter that is set. This product will be used in production server which has MS SqlServer 7.0.
My question is: Is it advisable to shrink Master,Model and Msdb databases in an production environment? Please give the reasons.
Thanks
Sri
I have a server attached to a SAN, all user db's are on the SAN, master, tempdb, and msdb are on the local machine. We will be replacing the machine (not the SAN) to a newer sql server. Our plan was to create the new server with same name, deattach current user db's,backup master and msdb, then connect new server with same name an IP to SAN. I then wanted to restore the master and msdb db's to the new machine, and attach the user db's. We are running log shipping to a stand-by server in this configuration. I was thinking I could let last tran log backup and copy occur, and then let the last restore happen on the standby. I would then shut down the agent. Should I not be able to restore the MSDB and master to the new server (as long as all file folder locations are the same) and be ready to go? Or are there issues when restoring the Master and MSDB to a new server from a different server.
View 5 Replies View RelatedHello:
I am in the process of setting up a dbcc checkdb and dbcc newalloc job for the user database in production. Do I need to include master and msdb to have checkdb and msdb run against them daily?
Any information you can provide will be greatly appreciated. Thanks.
David Spaisman
Will I encounter any issues running DBCC CHECKDB on either msdb or master?
Thanks!
When SQLserver2K was installed it placed master, model, msdb, tempdb data files in the installation location (i.e. C:Program Files....). This puts pressure on the C: drive, which also holds the page/swapfile. I want to move at least the tempdb location to the new 'Default data directory' and log directory we set after installation (i.e. E:MSSQLData).
How do I get tempdb to relocate to E: given that it gets recreated each time SQLserver starts?
TIA,
Al
How to move master , model and msdb database to some other location. Can we overwrite the master, model and msdb files
Hi Guys,
Since,You cannot mirror the master, msdb, tempdb, or model databases.What will happen if i create new login,change existing security profile and new jobs,change of existing job on princicpal db. how these will be mirrored to other server and in case failover, how it will treat.
Thaks
Hi all
What is best Disaster/Recovery methode to securing the system databases.
Master database allows only Full backup
MSDB allows Full ,Diff
I'm attempting to move my database from one server to the other using attach/detach method. I'm able to reattach everything just fine on the new server but I get the following error when trying to receive messages.
"Service Broker login attempt failed with error. Connection handshake failed. Error 15581 occurred while initializing the private key corresponding to the certificate....."
" Please create a master key in the database or open the master key in the session...."
I have a master key in the master database so I attempted the following:
open master key decryption by password = 'pwd'
alter master key add encryption by service master key
and I receive the following error on the alter master key command:
"An error occurred during decryption"
Any suggestions on how to recover the database to use the same database key? Do I need to regenerate the key?
Thanks for your assistance.
Hello everyone,
I just started learning sql and I came across a slight problem.
Assume I have a customer table composed of the following fields:
Customer_Number,Sname,Street,City,Postcode,credit_lim,balance.
What I want to do is to drop the Street,city,and postcode fields and add the following: fax,email,and phone.
Is it possible to use the following command:
Alter table customer
Drop column street,city,postcode
Or should I do as following:
Alter table customer
Drop column street
Alter table customer
Drop column city
Alter table customer
Drop column postcode
And the same goes to adding the phone,fax,and email field.
Thank You.
NB: I need to use the Alter command and nothing else
I want to query my msdb job and jobschedule related tables to generate a list of runtimes for each of these jobs for the next day or any future date. This query should output JobID, Run_Date(YYYYMMDD), and Run_Time(HHMMSS).
If I have 3 jobs with...
Job#1 scheduled to run once every 4 hours between 6 AM and 10 PM
Job# 2 scheduled to run every 15 minutes between 11 AM and 1 PM
Job# 3 scheduled to run every minute between 4 PM and 4:15 PM
my output should look as below ....
Hi,I'm trying to run the ALTER TABLE command using a dynamic string for thetable, like so:DECLARE @TableName CHARSET @TableName = 'Customers'ALTER TABLE @TableNameADD ...blahIs this possible? We know this works:ALTER TABLE Customers ADD ...blahIt looks like I need a way to convert the CHAR value to a literal or perhapseven a table ID?Thanks in advance,Paul
View 3 Replies View Related
Dear all
I need to add new column in table TranPay , I use ALTER command on PDA on Button Click
Dim SqlStm = "ALTER TABLE TranPay ALTER COLUMN TranKey IDENTITY (1, 1)"
Dim Dcmd As SqlCeCommand = New SqlCeCommand
Dcmd.Connection = Conn
Dcmd.CommandText = SqlStm
Dcmd.ExecuteNonQuery()
But cannot create new Field , Help me please
Brg ,
Tingnong
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?
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
ALTER TABLE customers
add column active_flg int(1)
but i got error.
quote:Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'column'.
what should i do?
ps. thanks for warning... i won't edit the post after i got the answer anymore.
I'm trying to add a column trough command "ALTER" and I've tried in many different ways.
Here is the result of statement..
Is there any way to extend the Alter Procedure command or fire somekind of event when it is executed in SQL Server 2005?
Thanks,
David
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 RelatedHi 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
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
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