Defrag The Drive
Apr 17, 2006Hi
Can I de-frag the Drive in which the Data files & Log Files of the SQl Server Exists ???
Please Advice.
Thanks
ADVERTISEMENT
DEFRAG Disk Drive
Jul 23, 2005SQL 2000We took SQL Server offline last night and defragged the SAN. Should wereindex or will be be okay ?Thanks,Craig
View 2 Replies View RelatedCan I Used A Shared Drive Rather Than A Mapped Drive With OpenRowSet?
Apr 4, 2008
Hi
I have been trying to use openrowset with a shared drive, and even though the share has "full control" permissions granted to "everyone" and the accout that SQL runs under has been granted explicit full control permissions I am unable to open the file which itself has no security on it.
Can I not use a \ path and only use mapped drives?
Thanks
below works...
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:5People.xls', [Sheet1$])
below doesn't work...
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\cluster02FileManager5People.xls', [Sheet1$])
Defrag Or Not To Defrag
Mar 14, 2007I have been reading many things on the internet and I wanted to create a thread asking my question here. We currently do all the re-indexing and show contig's etc to maintain my sql data and to ensure everything is good to go there.
My question is, what about the physical drive and data. We house our mdf's on a raid 1_0 and our ldfs on raid 5. I am wondering if I need to defrag these drives b/c if not am i impacting my I/O on that box. If so should I stop the sql service so that it does not corrupt SQL data? Any help on this topic would be great.
-patrick
How To Move A Log File From 'e' Drive To 'f' Drive....
Nov 9, 2000I am trying to move a log file from one drive to another.
What I have done is add another file to my file group. So now my log has a file on the 'e' drive and one on the 'f' drive. I now want to remove the file on the 'e' drive. I have emptied the file on the 'e' drive. When doing the command:
ALTER DATABASE Uniprodruntime
REMOVE FILE m_rk_runtime_log
I get the following error message..
Server: Msg 5020, Level 16, State 1, Line 1
The primary data or log file cannot be removed from a database.
I have also gone into enterprise manager and tried to delete the file and it does nothing.
Has anyone run into this?
SAN Drive Speed Vs Local Drive
Feb 12, 2007How do you compare SAN drive vs local drive on a 32 bit server?
Is it good idea to move my DB files to a SAN instead of local?
Canada DBA
TempDB Keeps Getting Filled And It Is In C Drive But It Should Be In T Drive
Nov 28, 2015Server: SQL 2008 R2
1: TempDB keeps getting filled. Restart of the server has not fixed it. I shrink it, but the space gets filled again. Now I can't even shrink it anymore
2: TempDB is at the wrong location. Its current location is this :C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLPROD6MSSQLDATA empdb
How do I change its location?
C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLPROD6MSSQLDATA empdb
Correct location of TempDB should be: TempDB(T:) But its not there
How To Defrag A Table?
Jul 30, 2004I have a large table with no PK but has other indexes. How to defrag the table (not the indexes)? Do I need to drop and re-create the table or something?
Thanks.
Defrag SQL SERVER
Feb 13, 2004Folks
Our sql server machine is badly in need of a defrag however I'm a bit weary of doing this as I'm really not sure of the implications or indeed how to do it.
I'm really not a server type of person so any assistance would be gratefully received. If anyone can explain it to me like I'm a 5 year old then that would probably be best!
Cheers
Database Defrag
Jul 14, 2004Hello,
I am working with a very large db (850+gig) that is in desperate need of defragging. I am familiar with DBCC Reindex and DBCC IndexDefrag but am concerned that these processes will take an extremely long time to run. Has anyone had any experience with any 3rd party tools that better/faster then DBCC? Or any ideas or suggestions how best to attack this? Among the many tables with large footprints and millions of rows, I have 3 tables with reserved space over 100g also 3 tables with over 500Mil rows (1 tbl with > 1bil rows). This is a 24x7 db.
Thanks in advance for any ideas and suggestions
Jeff
Automating Defrag.exe
Jul 19, 2007Isn't there any way to automate Windows defrag? Isn't there any undocumented parameters for DEFRAG.EXE?
Canada DBA
Defrag / Reindex
Oct 21, 2007I#ve been doing disaster recovery on a web box that died today.
So I thought I'd do some "downtime" maintenance on the DB server
I ran a BDREINDEX on all tables, all indexes. (I know this is the 2000 way, but I assume its as good as the proper 2005 way??).
5 minutes on a 10GB database. Not bad!
I checked the DEFRAG and UPDATE STATS processes that run overnight.
They are basically defragging only tables with SHOWCONTIG indicating fragmentation. And then doing an UPDATE STATISTICS WITH FULLSCAN on all tables
That is taking an average of 30 minutes ...
Is DBREINDEX the equivalent of an UPDATE STATISTICS WITH FULLSCAN, or is it in some way a smaller-sample version?
I'm wondering why I don't just lock the DB and do a REINDEX of everything in 5 minutes ...
Kristen
Index Defrag
Dec 5, 2007I 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.
Automating Table Defrag
May 26, 1999I'd like to build a process that will identify all the tables in a database with a scan density less than 100% and generate 'dbcc dbreindex' statements for them. The 'dbcc showcontig' command displays the information I want but I don't know how to access this information from within a script. Any ideas out there?
View 5 Replies View RelatedDefrag Indexed Views?
Jul 7, 2004How do we defrag indexed views? Can any one give me a query to loop thru all the indexed views in the database and find out the fragmentation levels and also defrag them?
Thanks in advance!
Does Index Defrag Get Logged?
Sep 15, 2006I'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.)
How To Defrag SQL Server 2000
Nov 15, 2006Hi everyone, I am fairly new with SQL Server and need a little bit of help in regards to boosting my SQL servers performance. I have been advised that defraging SQL Server will definitely help solve my issue however I have never performed this function before. Can someone please help guide me through the necessary steps to execute this task. Thanks in advance.
View 5 Replies View RelatedDefrag Table With No Clustered Idx
Aug 22, 2007Is there any easy way to defrag a table with no clustered idx other than bcp out then back in ? (SQL 2000)Actually I have a 100GB db that I deleted data from other tables, about 10GB worth, but access to the table in question (38GB) seems to have slowed down dramatically, Or does the entire disk need to be defragmented and a bcp out/in would be a waste of time on this table ?I run an index defrag every night, reindex weekly.Thanks.
View 14 Replies View RelatedYADS -- Yet Another Defrag Script
Oct 2, 2007This script was based on the original posted here: http://sql-server-performance.com/Community/forums/p/20584/114940.aspx#114940
I use the REORGANIZE option of ALTER INDEX. It's default is ONLINE, no matter what the engine edition and makes for simpler code.
I also make heavy use of my own logging tables, which are included at the bottom of the sp.
Please, feel free to comment. I like getting feedback about my scripts and it seems rarely that happens...
USE Admin
GO
IF EXISTS (SELECT [name] FROM Admin.sys.objects WHERE [name] = 'usp_DB_DeFrag' AND TYPE = 'P')
DROP PROCEDURE dbo.usp_DB_DeFrag
GO
CREATE PROCEDURE dbo.usp_DB_DeFrag (@DBName sysname, @Percentage float = 10)
-- EXEC Admin.dbo.usp_DB_Defrag @DBName = 'FooDB', @Percentage = 10
AS
/******************************************************************************
**Name: Admin.dbo.usp_DB_DeFrag.sql
**
**Description: Defragment indexes using REORGANIZE for online operation.
**Record historical fragmentation information to a permanant table
**for trend/history analysis.
**
**Depends on: SQL2005 >= SP2 due to object_name() usage. See BOL for details.
**Admin.dbo.Process_Log - Table
**Admin.dbo.FragTracking - Table
**
** TODO: Open to suggestions...
**
**Author: G. Rayburn <grayburn@---.com>
**
**Date: 10/02/2007
**
*******************************************************************************
**Modification History
*******************************************************************************
**
**Initial Creation: 10/02/2007 G. Rayburn <grayburn@---.com>
**
*******************************************************************************
**
******************************************************************************/
SET NOCOUNT ON;
DECLARE @DynFragList varchar(1024)
, @DynDBAlter varchar(256)
, @DynDefragDriver varchar(max)
, @DynUpdateStats varchar(1024)
, @OrigRecoveryModel nvarchar(128)
, @Process_Name varchar(150)
, @Message varchar(256)
, @Error int
-- Cursor objects:
, @SchemaName sysname
, @ObjectName sysname
, @IndexName sysname
, @IndexType nvarchar(60)
, @AvgFrag int
, @PageCount int
, @RecordCount int
--, @GhostRecordCnt bigint
--, @Partition int
;
-- DEBUG:
--SET @DBName = 'FooDB'
--SET @Percentage = 10;
SET @Process_Name = 'usp_DB_Defrag run on [' + @DBName + ']';
-- Ensure that @DBName is a valid db for db_id() usage.
IF (db_id(@DBName)) IS NULL
BEGIN
SET @Message = '[' + @DBName + '] is not a valid database on ' + @@SERVERNAME + ', please check your spelling and try again.'
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'ERROR', 9999, @Message)
RETURN
END;
-- Record startup message:
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'INFO', 0, '[START] - usp_DB_Defrag @DBName = [' + @DBName + '], @Percent = ' + CONVERT(varchar(3),@Percentage) + '.')
-- Check & alter recovery model if neccessary:
SET @OrigRecoveryModel = (SELECT CONVERT(varchar(55),DATABASEPROPERTYEX(@DBName, 'Recovery')))
IF @OrigRecoveryModel = 'FULL'
BEGIN
SET @DynDBAlter = 'ALTER DATABASE [' + @DBName + ']
SET RECOVERY BULK_LOGGED';
EXEC (@DynDBAlter);
SET @Error = (SELECT @@ERROR)
IF @Error = 0
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'SUCCESS', CONVERT(varchar(15),@Error), 'Successfully set database [' + @DBName + '] to BULK_LOGGED recovery model.')
END;
ELSE
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'ERROR', CONVERT(varchar(15),@Error), 'Failed to set database [' + @DBName + '] to BULK_LOGGED recovery model.')
END;
END;
ELSE
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'INFO', 0, 'Database [' + @DBName + '] is in ' + @OrigRecoveryModel + ' recovery model so no need to change it.')
END;
-- Temp table of initial DBCC results:
CREATE TABLE #_FragList
(
ObjectName varchar(100)
, [Object_ID] int
, Index_ID int
, Partition_Number int
, IndexType varchar(60)
, alloc_unit_type_desc nvarchar(60)
, avg_fragmentation_in_percent float
, avg_fragment_size_in_pages float
, avg_page_space_used_in_percent float
, fragment_count bigint
, page_count bigint
, record_count bigint
, forwarded_record_count bigint
, ghost_record_count bigint
);
INSERT INTO #_FragList
SELECT
LEFT(object_name([object_id], db_id(@DBName)),100)
, [object_id]
, index_id
, partition_number
, index_type_desc
, alloc_unit_type_desc
, avg_fragmentation_in_percent
, avg_fragment_size_in_pages
, avg_page_space_used_in_percent
, fragment_count
, page_count
, record_count
, forwarded_record_count
, ghost_record_count
FROM sys.dm_db_index_physical_stats (db_id(@DBName), NULL, NULL, NULL, 'DETAILED')
WHERE avg_fragmentation_in_percent >= @Percentage
AND index_id >= 1
AND page_count >= 1000
ORDER BY -- Ensure Clustered indexes are rebuilt first.
[object_id]
, index_id ASC;
CREATE INDEX IDX_ObjNameIndexID ON #_FragList (ObjectName, Index_id);
-- Historical tracking:
INSERT INTO Admin.dbo.FragTracking
SELECT @DBName
, ObjectName
, [Object_ID]
, Index_ID
, Partition_Number
, IndexType
, alloc_unit_type_desc
, avg_fragmentation_in_percent
, avg_fragment_size_in_pages
, avg_page_space_used_in_percent
, fragment_count
, page_count
, record_count
, forwarded_record_count
, ghost_record_count
, getdate()
FROM #_FragList
ORDER BY [Object_ID]
, Index_ID ASC;
-- Create & populate Temp table to drive defrag operations from.
CREATE TABLE #_DefragDriver
(
IdentID int IDENTITY(1,1)
, SchemaName sysname
, ObjectName sysname
, IndexName sysname
, IndexType varchar(60)
, avg_fragmentation_in_percent float
, page_count int
, record_count int
, ghost_record_count bigint
, partition_number int
);
SET @DynDefragDriver = '
USE [' + @DBName + ']
INSERT INTO #_DefragDriver
SELECT schema_name(so.schema_id)
, fl.[ObjectName]
, si.[name]
, fl.IndexType
, fl.avg_fragmentation_in_percent
, fl.page_count
, fl.record_count
, fl.ghost_record_count
, fl.partition_number
FROM #_FragList fl
, [' + @DBName + '].sys.indexes si
, [' + @DBName + '].sys.objects so
WHERE object_id(fl.ObjectName) = si.object_id
AND fl.index_id = si.index_id
AND object_id(fl.objectname) = so.object_id
AND si.is_disabled = 0
AND si.allow_page_locks = 1
GROUP BY so.schema_id
, fl.[ObjectName]
, fl.[object_id]
, fl.index_id
, si.[name]
, fl.IndexType
, fl.avg_fragmentation_in_percent
, fl.page_count
, fl.record_count
, fl.ghost_record_count
, fl.partition_number
ORDER BY fl.[object_id]
, fl.index_id ASC; '
EXEC (@DynDefragDriver);
-- Do the defrag.
DECLARE curDBFrag CURSOR
FOR
SELECT SchemaName
, ObjectName
, IndexName
, IndexType
, avg_fragmentation_in_percent
, page_count
, record_count
--, ghost_record_count
--, partition_number
FROM #_DefragDriver
ORDER BY IdentID ASC;
OPEN curDBFrag
FETCH NEXT FROM curDBFrag INTO @SchemaName, @ObjectName, @IndexName, @IndexType, @AvgFrag, @PageCount, @RecordCount --, @GhostRecordCnt, @Partition
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- ALTER INDEX operations:
SET @Message = 'Table: [' + @ObjectName + '] with record count: ' + CONVERT(varchar(15),@RecordCount) + ' and page count: ' + CONVERT(varchar(15),@PageCount) + '. Index: [' + @IndexName + '] of type: ' + @IndexType + ' is ' + CONVERT(varchar(5),@AvgFrag) + '% fragmented.';
SET @DynFragList = 'ALTER INDEX [' + @IndexName + '] ON [' + @DBName + '].[' + @SchemaName + '].[' + @ObjectName + '] REORGANIZE;'
EXEC (@DynFragList);
SET @Error = (SELECT @@ERROR)
IF @Error = 0
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'SUCCESS', CONVERT(varchar(15),@Error), @Message)
END;
ELSE
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'ERROR', CONVERT(varchar(15),@Error), @Message)
END;
-- UPDATE STATISTICS operations:
SET @Message = 'UPDATE STATISTICS [' + @SchemaName + '].[' + @ObjectName + '] [' + @Indexname + '];'
SET @DynUpdateStats = '
USE [' + @DBName + ']
UPDATE STATISTICS [' + @SchemaName + '].[' + @ObjectName + '] [' + @Indexname + ']; '
EXEC (@DynUpdateStats);
SET @Error = (SELECT @@ERROR)
IF @Error = 0
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'SUCCESS', CONVERT(varchar(15),@Error), @Message)
END;
ELSE
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'ERROR', CONVERT(varchar(15),@Error), @Message)
END;
-- Friendly WAITFOR operation:
WAITFOR DELAY '00:00:05.000'
END;
FETCH NEXT FROM curDBFrag INTO @SchemaName, @ObjectName, @IndexName, @IndexType, @AvgFrag, @PageCount, @RecordCount --, @GhostRecordCnt, @Partition
END;
CLOSE curDBFrag
DEALLOCATE curDBFrag;
-- Reset FULL recovery model.
IF @OrigRecoveryModel = 'FULL'
BEGIN
SET @DynDBAlter = 'ALTER DATABASE [' + @DBName + ']
SET RECOVERY FULL';
EXEC (@DynDBAlter);
SET @Error = (SELECT @@ERROR)
IF @Error = 0
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'SUCCESS', CONVERT(varchar(15),@Error), 'Successfully reset database [' + @DBName + '] back to FULL recovery model.')
END;
ELSE
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'ERROR', CONVERT(varchar(15),@Error), 'Failed to reset database [' + @DBName + '] back to FULL recovery model.')
END;
END;
-- Record complete message:
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'INFO', 0, '[COMPLETE] - usp_DB_Defrag @DBName = [' + @DBName + '], @Percent = ' + CONVERT(varchar(3),@Percentage) + '.');
-- Cleanup:
DROP TABLE #_FragList;
DROP TABLE #_DefragDriver;
-- Dependancies:
----
----USE [Admin]
----GO
---- DROP TABLE Admin.dbo.FragTracking
----CREATE TABLE FragTracking
----( TrackID int IDENTITY(1,1) -- PRIMARY KEY CLUSTERED
----, DBName sysname
----, ObjectName sysname
----, Object_ID int
----, Index_ID int
----, Partition_Number int
----, IndexType varchar(60)
----, alloc_unit_type_desc nvarchar(60)
----, avg_fragmentation_in_percent float
----, avg_fragment_size_in_pages float
----, avg_page_space_used_in_percent float
----, fragment_count bigint
----, page_count bigint
----, record_count bigint
----, forwarded_record_count bigint
----, ghost_record_count bigint
----, SnapDate datetime
----);
----
----USE [Admin]
----GO
---- DROP TABLE Admin.dbo.Process_Log
----CREATE TABLE [dbo].[Process_Log](
----[MessageID] [int] IDENTITY(1,1) NOT NULL,
----[Date] [datetime] NOT NULL,
----[Process_Name] [varchar](150) NULL,
----[Severity] [varchar](15) NULL,
----[ErrorCode] [int] NULL,
----[Message] [varchar](255) NULL,
---- CONSTRAINT [PK_Process_Log] PRIMARY KEY CLUSTERED
----(
----[Date] ASC,
----[MessageID] ASC
----)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Admin_Data]
----) ON [Admin_Data]
----GO
GO
Index Rebuild Does Not Defrag
Oct 3, 2007Hi,
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
Moving A SQL Server 2000 Database From A Local Drive To Another Local Drive
Jan 31, 2008Being a very novice SQL Server administrator, I need to ask the experts a question.
How do I go about moving a database from 1 drive to another? The source drive (C is local to the server, but the target drive (E is on a Storage Area Network (SAN), although it is still a local drive for the server. I want to move the database from C: to E:. Can someone provide me with instructions?
Thanks,
Rick
MS SQL Question Regarding Page/index Defrag.
Apr 17, 2005I 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?
Dbreindex Vs Index Defrag Question
Mar 9, 2007Does 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 RelatedSQL 2012 :: Clustered Index Defrag Not Working
Apr 15, 2015I 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]....
Turn Sql Serveice Off Before Performing Disk Defrag?
Oct 15, 2007We have a Windows 2003 R2 server running Sql 2005. Should we stop the sql server engine prior to running the "Defrag" utiltiy that comes with the OS? (Maybe we should also disable the NIC card on that machine too....?)
TIA,
barkingdog
SQL 2012 :: How To Backup Half Of DBs From A Server On C Drive And Other Half On D Drive
Jan 16, 2015How to backup half of dbs from a server on C drive and the other half on D drive and vice versa, first half on D drive and other half On C drive using only one job and one stored procedure??
Using scheduling from job add 2 schedules to the job so first schedule backup first half to C and second half to D , the second schedule backup first half to D and second half to D.
DB Engine :: Index Defrag Routine Audit Frag Values
Aug 26, 2015I 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
T-SQL (SS2K8) :: MASTER And MSDB - Defrag Using Standard Alter Index Command?
Oct 10, 2014On 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?
Transact SQL :: Index Maintenance / Defrag Fails On CDC Enabled Database - 2008 R2
Oct 18, 2012We 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.
DBCC DEFRAG Vs DBCC DBREINDEX
May 11, 2006What factors would make you decide to use DBCC DBREINDES instead of DBCC DEFRAG when you notice index defragmantation?
Thanks.
ekareem
Map Drive
Aug 13, 2004If my computer has a map drive, i want to delete it, how to do it? Is that right if i right click the map drive and click the disconnect?
View 3 Replies View RelatedC Drive Not Big Enough
Oct 26, 2006I am installing the X86 Executable version of SQL Server. I copied it to my desktop and clicked RUN. It extracts the files and then the Install Wizard tells me there is not enough space on the C Drive to load the program. I have 23 gigabits of free space and the program only takes about 900 megabits.
Is there a solution to this problem?
Thanks,
Modez
Possible To Store DB On Another Drive?
Jun 19, 2002I'm running out of space on my C. Is it possible to store additional DBs on another drive or do I need to load SQL server onto the new drive?
View 6 Replies View Related