I am using SQL Server 2008 (RTM) Standard Edition.
In my environment, one of my Database size is 75 gb and I have to create a plan for index rebuild using maintenance plan.But when we rebuild indexes, it requires some space on data and log files of database.how can we calculate disk space requirement for index rebuild process ?
I'm trying to determine how much space I would need for my data drive and log file drive to do index rebuild. I have a database which is 100gb, it is in simple recovery mode. let me know what to have a look at to determine how much space.
View 7 Replies View RelatedWe are planning to standardize our newly deployed sql server, As a part of it we have configured 2 maintenance plans 1) Update Statistics which runs daily and 2) Index Reorganize which runs on weekly.
Apart from above, any other things to be in place for better maintenance of the sql server.
Also, how to Index Rebuild activity for clustered indexes requires any downtime.
I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),
[Code] ....
-- Initialize Control Mechanism
DECLARE@Drive TINYINT,
@SQL VARCHAR(100)
SET@Drive = 97
-- Setup Staging Area
DECLARE@Drives TABLE
(
Drive CHAR(1),
Info VARCHAR(80)
)
WHILE @Drive <= 122
BEGIN
SET@SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''
INSERT@Drives
(
Info
)
EXEC(@SQL)
UPDATE@Drives
SETDrive = CHAR(@Drive)
WHEREDrive IS NULL
SET@Drive = @Drive + 1
END
-- Show the expected output
SELECTDrive,
SUM(CASE WHEN Info LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TotalBytes,
SUM(CASE WHEN Info LIKE 'Total # of free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FreeBytes,
SUM(CASE WHEN Info LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS AvailFreeBytes
FROM(
SELECTDrive,
Info
FROM@Drives
WHEREInfo LIKE 'Total # of %'
) AS d
GROUP BYDrive
ORDER BYDrive
E 12°55'05.25"
N 56°04'39.16"
We had one of the disks in our array fail yesterday morning. The rebuild process using the hot spare went into effect as one would expect. My question is: during this rebuild process, could additional errors be introduced into the database? I'm asking because the current production db has more errors than the restored database from last night.
Msg 8928, Level 16, State 1, Line 3
Object ID 210099789, index ID 255: Page (1:128895) could not be processed. See other errors for details.
Msg 8939, Level 16, State 106, Line 3
Table error: Object ID 210099789, index ID 255, page (1:128895). Test (m_freeCnt == freeCnt) failed. Values are 0 and 8094.
Msg 8939, Level 16, State 108, Line 3
Table error: Object ID 210099789, index ID 255, page (1:128895). Test (emptySlotCnt == 0) failed. Values are 1 and 0.
Msg 8928, Level 16, State 1, Line 3
Object ID 210099789, index ID 255: Page (1:469839) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 3
Table error: Object ID 210099789, index ID 255, page (1:469839). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1.
Msg 8928, Level 16, State 1, Line 3
Object ID 210099789, index ID 255: Page (1:469935) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 3
Table error: Object ID 210099789, index ID 255, page (1:469935). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1.
Msg 8928, Level 16, State 1, Line 3
Object ID 210099789, index ID 255: Page (1:491535) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 3
Table error: Object ID 210099789, index ID 255, page (1:491535). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1.
Msg 8928, Level 16, State 1, Line 3
Object ID 210099789, index ID 255: Page (1:680703) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 3
Table error: Object ID 210099789, index ID 255, page (1:680703). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1.
Msg 8928, Level 16, State 1, Line 3
Object ID 210099789, index ID 255: Page (1:762783) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 3
Table error: Object ID 210099789, index ID 255, page (1:762783). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1.
CHECKDB found 0 allocation errors and 13 consistency errors in table 'session_state' (object ID 210099789).
Msg 8928, Level 16, State 1, Line 3
Object ID 234028165, index ID 0: Page (1:14175) could not be processed. See other errors for details.
Msg 8944, Level 16, State 17, Line 3
Table error: Object ID 234028165, index ID 0, page (1:14175), row 0. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 326 and 300.
Msg 8928, Level 16, State 1, Line 3
Object ID 234028165, index ID 0: Page (1:155583) could not be processed. See other errors for details.
Msg 8944, Level 16, State 12, Line 3
Table error: Object ID 234028165, index ID 0, page (1:155583), row 1. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 3840 and 388.
Msg 8928, Level 16, State 1, Line 3
Object ID 234028165, index ID 0: Page (1:179199) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 3
Table error: Object ID 234028165, index ID 0, page (1:179199). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1.
Msg 8928, Level 16, State 1, Line 3
Object ID 234028165, index ID 0: Page (1:237663) could not be processed. See other errors for details.
Msg 8944, Level 16, State 12, Line 3
Table error: Object ID 234028165, index ID 0, page (1:237663), row 0. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 91 and 26.
Msg 8928, Level 16, State 1, Line 3
Object ID 234028165, index ID 0: Page (1:1060304) could not be processed. See other errors for details.
Msg 8944, Level 16, State 12, Line 3
Table error: Object ID 234028165, index ID 0, page (1:1060304), row 1. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 61455 and 397.
Msg 8928, Level 16, State 1, Line 3
Object ID 234028165, index ID 0: Page (1:1392799) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 3
Table error: Object ID 234028165, index ID 0, page (1:1392799). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1.
CHECKDB found 0 allocation errors and 12 consistency errors in table 'clientCampaignActivityAll' (object ID 234028165).
Hi All,
SQL 7 REQUIRES THE DBA TO MANAGE THE INDEXES AND IN PARTICULAR THE FILL FACTOR AVAILABILITY. DOES SQL SERVER 2000 AUTOMATICALLY ADDRESS THIS OR DO WE STILL HAVE TO PERIODICALLY RUN,
sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"
TO REBUILD ALL INDEXES IN THE DATABASE?
Thanks in advance,
-Praveena
Hi!
I'm not a dba at all, just a simple developer but I have got the assignment to help out with performance problems on a db.
I noticed that almost all indexes are fragmented 75-99%. Can I rebuild them when the database is in use? Can I reorganize indexes when the db is used?
/Magnus
Jesus saves. But Gretzky slaps in the rebound.
In the maintance plans there is a Rebuild Index choice.
If u choose tables and views the plan executes
ALTER INDEX <index> ON <table> ;REBUILD
for all indexes in the datebase.
I am currently using this plan on our production DB, scheduled for every Saturday night.
I wonder if there is a downside of using maintance plans. Because it seems to be doing the job. Any comments?
I am upgrading from SQL2000 to SQL2005.
I have restored my 2000 db to 2005.
I have changed the Compatiblilty level to 90.
Now I need to reindex.
How do I reindex all the tables at once?
Thanks for ALL your help
r/p
how can run a command to rebuild all indexes in a database?
So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this:
When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004
Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing?
If so does this "pseudocode" example also cause this to occur:
sqlstring="select * from item where itemid=12345"
rs.open sqlstring, etc, etc, etc
rs.Fields("ItemName")="My New Item Name"
rs.Fields("ItemPrice")=1.00
rs.Update
Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?
We have a script running everyday for rebuild and re-organisation of indexes. But, somehow its getting failed. Attached script for your consideration. There is no database name with amoperations. There is table called DatabaseObjectAudit but it exist on master db.
Message:
-->Start Index Maint
-> Gathering fragmentation information (can take a while!)
-> Gathering COMPLETE : Total of 43 databases were found.
-> Gathering COMPLETE : Total of 1622 indexes were found.
[code]...
I have a number of databases that are being transactionally replicated from SQL 2000 Enterprise edition publisher to SQL 2005 Enterprise edition subscriber. I have included indexes in the replication. The subscriber database is then accessed and the data de-normalised and aggregated for reporting purposes.
My question is this: I want to periodically re-build the indexes on the publisher and subscriber via an automated task. If I rebuild the indexes on the publisher, will that automatically replicate to the subscriber? Will there be a problem with the "snapshot being out of date", and therefore replication stopping? I run a new snapshot once a day in the small hours of the morning. If there is likely to be a problem with the rebuild throwing the replication out, would it be wise to have the rebuild job running just before the new snapshot is taken?
Any help/advice appreciated.
Hi All.
Our inhouse app used to run on a SQL2000, but we've recently moved it to 2005.
The move was done by way of backup and restore (it was on a whole new server).
After the move, an odd problem showed up: once in a while, the server seems incapable of finding/using its indexes: everything starts working slowly, until I run a maintenance plan that rebuilds its indexes etc.
In the database/server all relevant options seem to be ok (auto update statictiscs etc.), and my conclusion that it doesn't use its indexes comes from the fact that:
* it gives the results from certain select statements in a totally different order (although the set of rows is the same),
* performance is (all of a sudden) very slow (seconds turning to minutes!!)
This happens:
* at least after a reboot of the server
* sometimes just in the middle of the day
The only way I've found to solve the matter, is by running the maintenance plan to rebuild the indexes, but sometimes this only seems to work the second time.
Does anybody share this experience, or know what to do about it?
Thanks,
Nick.
Hi,
I have a script to rebuild and reorganize indexes for sybase i.e reorg rebuild index... like command i have. Now i want similar command for MSSQLSqlserver.plz help me.
Do I need to rebuild my indexes on my High Availability listeners?
When I do a full index rebuild on my primary DB's. Does rebuilding also send the rebuild to the listener(s)?
best method to my issue regarding unused space. SQL SERVER 2014 BI EDITTION..I have a table that showed 62% Data, 7% unallocated, and 29% unused space.I ran the ALTER TABLE <table name> REBUILD, which changed it to 32% data and 67% unused.What I do with this table monthly and what I believe is causing this unused space issue is this:Every 1st of the month I get a csv file that holds around 3.2 million rows/15 columns of data. About 3 gbs every time.It's 13 months of data. I remove the last 12 months from the current table and import the new 13 month data.I do this twice a month and have no choice because the data is constantly updated and why we get it twice a month.So I am deleting around 6 gbs of data a month as well as adding around 6 gbs of data a month.
I believe this is why I end up with so much unused space, but just found the REBUILD command, which worked but now a small hit on performance and have too much space in the current table. I have read several times that shrinking the file is no good, but what other way to get rid of the extra space in this table? Also, the table doesn't have any indexes or primary key because of duplicates.
We have a maintenance plan running everyday for rebuild and re-organisation of indexes. But, somehow its getting failed. Here is the script that we are running for rebuild or re-org.
/*
Script to handle index maintenance
Tuning constants are set in-line current values are;
SET @MinFragmentation
SET @MaxFragmentation
SET @TrivialPageCount
[code]....
If I rebuild some indexes that are above 30% of average fragmentation, should I after that update statistics?
Also, How can I see if I Need to update statistics^on the tables of my database?
I'm upgrading to SQL 2012 from 2008R2, while doing so i will be rebuilding all the indexes on all the database. In my previous environment while doing so, i got space related error in primary filegroup for insufficient space in the primary filegroup. Is there any rule of thumb about how much space is required by index rebuild command for each database, or is there a safe threshold for free space in the database?
View 9 Replies View RelatedNormally we use rebuild, reorganize indexes when it is required, I used a SQL job using maintenance plan to run daily and rebuild, reorganize indexes and update statistics but I do not know if it runs either they are required or not. Should this plan automatically execute the build upon required indexes to be rebuild or it fires either they are required to be executed or not.
View 2 Replies View RelatedHello
I need to setup a compaq sever with 300 MB database, and will be adding around 600 records on a daily basis. Can someone help with how much disk space i should have on sqlserver, providing i have c: and d: setup.
Thank you in advance for all the help.
Paula
Hi All,
I have a server and it has C: D: F: I: Drives and all the system files are on C:Drive and and all the .MDF's and .LDF's(model,temp,master) are on the F: Drive and now I am running out of space on both(C: and F: Drives)
1. Can we add space to the C: and F: drives on the fly?.
2. Can I move the System databases ( MDF's and LDF's to some other drive)and if so, how do I do it?( Moving the databases ) and this is on the production database so when I have to do this.Will there be any impact.
Thanks in Advance,
Reddy
Does anyone know how to get the free disk space for all drives in many different SQL servers.
I need to populate a report (right now output in Excel) with the free disk space of all drives on all of my SQL servers.
I found xp_fixeddrives but that is specific for the server where it is executed.
Any help or pointers to a script in the archives is much appreciated.
Thanks.
I noticed something strange today. I was running a query using query analyzer on a large database (8.8 million records) and the disk space on the c: drive was dropping and eventually went to 0. Availalbe space on the c: drive is 10GB. The query did complete. SQL server and all the databases are on the d: drive. After closing the query results in query analyzer the disk space returned. Is this a concern and is there a way to change it to use the d: for whatever it is doing?
View 3 Replies View RelatedThis is my first attempt using SQL 2000 and DTS. I am importing an Access database using the DTS wizard. The process fails with a "Not enough space on temporary disk" error. There is definitely enough space on the physical disk. I don't have any limits on any folder sizes either. What "disk" is the error talking about, and how do I give it enough space. The database is relatively small, about 10MB. I believe the database was created using Access 97. Please help.
View 1 Replies View RelatedI am trying to find out if it is possible to move indexes to a separate filegroup/disk drive during database restore. I am trying this to see if it improves performance. Also if I cannot move the indexes during restore, how would I move them afterwards to a different filegroup/disk drive? Thanks in advance for all the help.
View 7 Replies View RelatedI need a way to get the used space for a physical drive, or even the total capacity for a drive to do some calculations.
I don't want to have to write my own native code either.
Is there a stored procedure that will give this to me?
I saw the xp_fixeddrives that will give the free space, but I need one of the other as well.
Any way to get this?
Thanks,
Jason
We recently moved from v6.5 to v7.0. Now I have the databases and logs set to "autogrow". How can I monitor the disk space to ensure I do not run out of room (or is that preset as to how large it can grow ?). Can't find anything in the books online. Do I do this through the NT admin tool or through the SQL*Server Enterprise Manager and more importantly - how ???
Thanks so much for any help...
Nancy
I'm trying to save a dts package and it keeps coming back with insufficient disk space. I noiticed that db MSDB was full so I manually increased the size. It was set to manually grow at 1 mb increments. But for some reason it didn't look like it was doing that so I manually increased it. Right now this is about 355 MB free so that should be plenty to save a package. But its still coming back with the same error insufficient disk space to complete operation. Any ideas on why or why it didn't grow on its own? Please help I can't seem to save any packages.
View 1 Replies View RelatedOur database -SQL Server 7.0 sp1 (NT 4.0 sp5)- is growing at a very fast rate despite the fact that we are deleting old record. It doesn't seem to be recovering disk space for the deleted records. Please let me know if there is a specific setting that can help us recover disk space. )
View 1 Replies View RelatedHi,
I'm new to these forums (and to SQL Server), so please be gentle with me.
I am developing a process to obtain information on all our remote servers/databases, and store it in a single local database.
I'm after things like db size, last backup date, free drive space etc...the usual weekly statistics.
I've linked the remote servers to my local one, and have written a few simple procedures (which exist on the local server) to grab backup and file size information from the remote tables. The output is stored locally in tables which we can then query as necessary.
I am having difficulty obtaining the free drive space details.
I'm using :-
'exec <remote_server>.master.dbo.xp_fixeddrives' to get the info, but I cannot store the output in a table on the local server. (remote_server_name, date, drive_letter, space_mb)
I wish to avoid creating any objects on the remote servers if at all possible.
I really want to pass the remote server name into the procedure, and the output to be inserted into the table.
Many thanks