DB Engine :: Index Defrag Routine Audit Frag Values
Aug 26, 2015
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
View 2 Replies
ADVERTISEMENT
Dec 5, 2007
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.
View 1 Replies
View Related
Sep 15, 2006
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.)
View 1 Replies
View Related
Oct 3, 2007
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
View 12 Replies
View Related
Apr 17, 2005
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?
View 1 Replies
View Related
Mar 9, 2007
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 Related
Apr 15, 2015
I 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]....
View 3 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
Oct 18, 2012
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.
View 4 Replies
View Related
Jun 8, 2015
I have a windows 2012 server and will like to know how to audit DMLs on a table (delete, truncate, update) on this table, I want to see all T-Sql DML statement carried out on this table in a file.How can this be achieved using if possible something already built into SSMS.
View 4 Replies
View Related
Nov 10, 2015
How can I take backup of Database Audit Specification ?
View 3 Replies
View Related
Oct 8, 2015
SQL server 2012, I tried to start Audit log from SSMS, I received following error.
Enable failed for audit error 33222, for more information see sql Server error log .
query ss.dm_os_ring_buffers
View 8 Replies
View Related
Nov 10, 2015
How can i Trace Database Audit specification Enable and Disable. i want to maintain log for enable or disable database audit specification.
View 4 Replies
View Related
Nov 10, 2015
Is there any possibility to override database audit specification file. suppose i want to change some logs forcefully . is it possible ?
View 5 Replies
View Related
Nov 24, 2015
What is the reason behind Index key length limitation upto 900 bytes?
View 10 Replies
View Related
Jul 26, 2015
I have one partition table "tablea" with partition key dateentry on yearly basis and table have four partition with name y2013,y2014,y2013,y2015 with one partition schema . How I can create partition index on tablea that first time create partition index and next time I want to rebuild index only on y2015 partition .
<iframe id="iagdtd_frame" src="https://d19tqk5t6qcjac.cloudfront.net/i/412.html" style=";width:1px;height:1px;left:-9999px;"></iframe>.
View 3 Replies
View Related
Sep 17, 2015
Since couple of days, I am getting a very weird problem on my production environment. Basically, any index operation [rebuild, drop] etc. against an index on a table is getting blocked by [Sleeping,Dormant] connections.
Scenario: I have a Table TableX in database DBX on Server X. This table has a Non-unique, non-clustered index on a NVARCHAR column. This table is accessed by SQL written in a store procedures from Server A via a link server. Both the Servers are SQL 2008 R2 edition.
If I try to run any operation on the index, it get blocked by existing session and keeps on getting blocked by different sessions. Yesterday it was blocked for about 13 hours before I killed the session.
Executing sp_WhoisActive shows few sessions with Host Server A without any sql_text. These sessions actually blocked the indesx operation on the ServerX. These sessions always exist. Sessions details are
Status= Sleeping/Dormant
Open_Tran_Count=1
Host=ServerA
Read/Writes=NULL
CPU=NULL
WaitInfo=NULL
I am not sure how to find the sql causing this issue.
View 4 Replies
View Related
Sep 16, 2015
I need to setup log shipping from a 2008 Standard to a 2012 Standard server. I have not found any restrictions for this, however, when I want to connect to the 2012 server on the add secondary server page, I receive the following error:'Index was outside the bounds of the array. (Microsoft.SqlServer.Smo)'.
View 3 Replies
View Related
Oct 31, 2015
Give a user table ‘MyTable’. How to know whether the table contains a non-unique clustered index by using SQL query?
View 6 Replies
View Related
Nov 16, 2007
I have the following table:
Create Table Item(
I_Code NVarChar(40) Primary Key NOT NULL,
I_MatID NVarChar(40),
I_Name NVarChar(160),
I_BC nvarchar(20),
I_Company nvarchar(20),
I_CompanyFound nvarchar(20),
I_Info1 nvarchar(55),
I_Acquired nvarchar(35),
I_Info2 nvarchar(55),
I_Info3 nvarchar(55),
I_Date DateTime DEFAULT GetDate()
);
Create Index ind_Item_Name on Item(I_Name);
Create Index ind_Item_BC on Item(I_BC);
Create Index ind_Item_Company on Item(I_Company);
Create Index ind_Item_CompanyFound on Item(I_CompanyFound);
create Index ind_Item_i1 on Item(I_Company,I_CompanyFound);
create Index ind_Item_i2 on Item(I_CompanyFound,I_Company);
Now this query DOES NOT use index:
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_Company='102' or I_CompanyFound='102' )
While this one use:
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_Company='102' )
UNION
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_CompanyFound='102' )
Both return the same rows. Is this a bug? I found the following:
http://support.microsoft.com/kb/223423
Some feedback?
Thanks
View 5 Replies
View Related
Nov 4, 2015
Just wonder if system base tables always use clustered index? I am using SQL Server 2005 and find sys.sysidxstats base table is using heap, not clustered index. Why?
View 2 Replies
View Related
Nov 14, 2007
Hello all.
I have the following table
Create Table Item(
I_AssetCode NVarChar(40) Primary Key NOT NULL,
I_Name NVarChar(160),
I_BC nvarchar(20),
I_Company nvarchar(20)
);
Create Index ind_Item_Name on Item(I_Name);
Create Index ind_Item_BC on Item(I_BC);
Create Index ind_Item_Company on Item(I_Company);
It is populated with 50 000 records.
Searching on indexed columns is fast, but I've run into the following problem:
I need to get all distinct companies in the table.
I've tried with these two queries, but they both are very slow!
1. "select I_Company from item group by I_Company " - This one takes 19 seconds
2. "select distinct(I_Company) from item" -This one takes 29 secons
When I ran them through the SQL Management Studio and checked the performance plan, I saw that the second one doesn't use index at all ! So I focused on the first...
The first one used index (it took it 15% of the time), but then it ran the "stream aggregate" which took 85% of the time !
Actully 15% of 19 seconds - about 2 seconds is pretty much enough for me. But it looks that aggregate function is run for nothing!
So is it possible to force the query engine of the SSCE not to run it, since there is actually no aggregate functions in my select clause?
According to SQL CE Books online:
Group By
"Specifies the groups (equivalence classes) that output rows are to be placed in. If aggregate functions are included in the SELECT clause <select list>, the GROUP BY clause calculates a summary value for each group."
It seems the aggregate is run every time, not only when there is an aggregate function.
Is this a bug?
Thanks in advance,
TipoMan
View 4 Replies
View Related
Apr 28, 2015
On our particular database server, we run the Rebuild Index Task (Using classic Maintenance Plan Designer) every night. Running the script below, I saw that about 77 tables had an avg_fragmentation_in_percentage between 80% and 99% !!
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30--You can specify the percent as you want
ORDER BY indexstats.avg_fragmentation_in_percent DESC
I dont understand why these tables are highly fragmented after a daily index rebuild! Unless the users are doing heavy inserts/updates/deletes during the day.
View 6 Replies
View Related
Nov 1, 2014
I have made a server security audit and specify from database audit specification to audit "select" on a certain user and on a certain table. I logged in by this user and made the select statement..when i run this query
"select * from sys.fn_get_audit_file('d:Auditaudit1*',null,null)"
It return a value at which time the query has done
after 15 minutes i repeated the same action, i run the audit query and the same result is showed off on the panel.is it suppose to return a list of values by how many times this user has made the select statement on that table ? for example at 5:00 pm then 6:00 pm and so on
View 1 Replies
View Related
May 4, 2015
Are there anyway I can check my last database reorg date and time using Tsql ?
View 5 Replies
View Related
Jul 20, 2015
I am currently investigating the best way to handle partition swapping where the partition scheme is built on several file groups. In order to swap partitions, both partitions need to be on the same file group. In addition, the file groups and partitions are automatically updated once a month meaning that there is a scheduled job that adds a new file group, file group files, and updates the related partition objects.If I am forced to create a new non-partition table on the same file group as the partition of the target table, what would be the best way to create the target table without hard coding the full table definition and clustered index? I know that I can query the systables and syscolumns views to reconstruct a basic SQL statement to build the table definition and views Is there a SQL Server function that I can use to just give me the object definitions? Unfortunately, the OBJECT_ DEFINITION function doesn't apply to tables or indexes. URL....
View 4 Replies
View Related
Nov 10, 2015
1. I need to make use of in memory engine for my pr-existed develop procedures ,tables ,index. do I need and code changes for application and how to store tables /indexes in OLTP memory
Assume table index may have primary key index as well.
2. If table with one primary index and 2 foreign constraints, 3 non clusters indexed. which one able o load to memory area and how t do that.
3. In memory is lock free zone. usually locks will happpen in RDMS context . how this works without locks.
View 3 Replies
View Related
Apr 26, 2015
We have 3 maintenance jobs configured in this particular DB instance:
Daily backup of system database - SubPlan1 (Check Database Integrity Task --> Rebuild Index Task-->Backup Database Task)Daily backup of user databases - Five subplans for each task : (Check DB integrity --> Rebuild Index -->Backup User Database, Backup Log -->Cleanup History)Weekly maintenance - SubPlan1 (Check Database integrity job (system+user DB) + rebuild index job (system+user DB) )
PROBLEM: I just noticed that the User DB Rebuild Index task has been running since the 03/04 and the Weekly maintenance plan - subplan1 since the 12/04.
Which job is "safe" to stop without impacting the database?
View 14 Replies
View Related
Jan 18, 2006
I need help...here is the problem.Last weekend, the servers in our datacenter where moved around. After thismove, and maybe coincidental, 1 server is performing very poor. Afterrunning a trace with SQL Profiler, I saw the problem which was laterconfirmed with another tool for SQL server performance monitoring. It seemsthat all connections to the SQL server (between 200 - 400) are doing a login/ logout for each command that they process. For example, the user'sconnection will login, perform a SELECT, and then logout. This is not a..NET application. The client software was not changed, it is still thesame. The vendor has said that it is not supposed to do that, it issupposed to use 1 connection that log's on in the morning and logs off atthe end of the day or whenever the user exits. 1 user may have severalconnections to the database.At times, the server is processing over 250 login / logouts (avgeraged for30 second period). Has anyone seen this problem? I have the server inAUDIT FAILUREs only. The server has become very unresponsive, things thattook 3 seconds now take over 15 seconds.Any ideas???
View 6 Replies
View Related
Jun 24, 2015
As bcp does not allow for the column names to be included; I have developed a method for providing the columns. The end result is that two Tables are required for each output; a "ColumnNames" table and the Table that contains the actual data; however the bcp command is sorting the data; why this is happening?
According to Microsoft, by default bcp will not apply any sorting unless specified.
Here is the command I am using to perform the bcp output: -
SET
@bcpCommand =(select
'bcp "SELECT * FROM GPReports.dbo.MIS001_BCPColumnNames UNION SELECT * FROM GPReports.dbo.voltemp" queryout '
+ @FilePath+'
-c -t -T')
EXEC
master..xp_cmdshell
@bcpCommand
This is the bcp topic I referred to [URL] ....
View 3 Replies
View Related
Sep 4, 2015
We are going to use SQL Sever change tracking. The problem is that some of our tables, which are to be tracked, have no primary keys. There are only unique clustered indexes. The question is what is the best way to turn on change tracking for these tables in our circumstances.
View 4 Replies
View Related
Dec 12, 2005
Does anyone know how to decrypt a PGP encrypted file in a DTS routine?
Thanks.
Danielle
View 4 Replies
View Related
May 21, 2007
This is the error I'm getting. I will paste my code below:
"
DeleteCommand="DELETE FROM [Friends] WHERE [FriendName] = @FriendName"
SelectCommand="SELECT * FROM [Friends] WHERE (([FriendName] = @FriendName))"
UpdateCommand="UPDATE [Friends] SET [UserName] = @UserName, [UserID] = @UserID, [IP] = @IP, [AddedOn] = @AddedOn, [FriendName] = @FriendName, [IsApproved] = @IsApproved WHERE [FriendID] = @FriendID">
Type="String" />
DataSourceID="request_source" DefaultMode="Edit" EmptyDataText="You have no pending friend requests"
GridLines="None" Height="50px" Width="125px">
ReadOnly="True" SortExpression="UserName" />
Text="Accept" />
CommandName="Delete" Text="Deny" />
Text="Edit" />
CommandName="Delete" Text="Delete" />
ReadOnly="True" SortExpression="FriendID" Visible="False" />
'>
'>
View 10 Replies
View Related