Yesterday night, I rebuilded all indexes one a table since they were having high percentage of fragmentation . After rebuilding, I immediately checked the fragmentation percentage and it got reduced and I felt happy. However, when i saw the percentage of fragmentation after 6 hours, it increased from 1% to 48%. How to find what caused the increase and how to fix it.
View 36 RepliesDatabase size increased from 600GB to 1000 GB in 4 hours. There was no process running. I am not able to understand why would database size increase in 4 hours.
Does anyone know how should I trouble shoot this?
I have rebuilded indexes and some of the indexes still showing fragmentation percentage as 99%. I have cross checked page count for these indexes and they are more than 10000 pages.As i am aware if the page count is less we can ignore this percentage. But in my case the page count is 10000.
View 2 Replies View RelatedWhat is the impact of fragmentation when my tables fragmentation is 99% but page count is 300 only?
View 6 Replies View RelatedI want to display Days Hours Mins Format.
I am Having two columns Like below,
Col1 (in days) Â Â col2 (In Hours : Mins)
3days 4:5Â
In this first have to  add Col1 and Col2 (Here one day is equals to 9 hours ) so the addition is 31.5
From this 31.5 I should display 3 Days 4 Hours 30 Mins because 31.5 contains 3 (9 hours) days 4 Hours and .5 is equals to 30 mins.
I have data coming from a telephony system that keeps track of when anemployee makes a phone call to conduct a survey and which project numberis being billed for the time the employee spends on that phone call in aMS SQL Server 2000 database (which I don't own).The data is being returned to me in a view (see DDL for w_HR_Call_Logbelow). I link to this view in MS access through ODBC to create alinked table. I have my own view in Access that converts the integernumbers for start and end date to Date/Time and inserts some otherinformation i need.This data is eventually going to be compared with data from someelectronic timesheets for purposes of comparing entered hours vs hoursactually spent on the telephone, and the people that will be viewing thedata need the total time on the telephone as wall as that total brokendown by day/evening and weekend. Getting weekend durations is easyenough (see SQL for qryTelephonyData below), but I was wondering ifanyone knew of efficient set-based methods for doing a day/eveningbreakdown of some duration given a start date and end date (with theday/evening boundary being 17:59:59)? My impression is that to do thiscorrectly (i.e., handle employees working in different time zones,adjusting for DST, and figuring out what the boundary is for switchingfrom evening back to day) will require procedural code (probably inVisual Basic or VBA).However, if there are set-based algorithms that can accomplish it inSQL, I'd like to explore those, as well. Can anyone give any pointers?Thanks.--DDL for view in MS SQL 2000 database:CREATE VIEW dbo.w_HR_Call_LogASSELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username ASInitials, dbo.billing.startdate, dbo.billing.startdate +dbo.billing.duration AS EndDate,dbo.billing.duration, dbo.projects.name ASPrjName, dbo.w_GetCallTrackProject6ID(dbo.projects.descript ion) AS ProjID6,dbo.w_GetCallTrackProject10ID(dbo.projects.descrip tion) AS ProjID10,dbo.billing.interactionidFROM dbo.projects INNER JOINdbo.projectsphone INNER JOINdbo.users INNER JOINdbo.TRCUsers ON dbo.users.userid =dbo.TRCUsers.UserID INNER JOINdbo.billing ON dbo.users.userid =dbo.billing.userid ON dbo.projectsphone.projectid =dbo.billing.projectid ONdbo.projects.projectid = dbo.projectsphone.projectidWHERE (dbo.billing.userid 0)ORDER BY dbo.billing.startdateI don't have acess to the tables, but the fields in the view comethrough as the following data types:WinsID - varchar(10)Initials - varchar(30)startdate - long integer (seconds since 1970-01-01 00:00:00)enddate - long integer (seconds since 1970-01-01 00:00:00)duration - long integer (enddate - startdate)ProjID10 - varchar(15)interactionid - varchar(255) (the identifier for this phone call)MS Access SQL statement for qryTelephonyData (based on the view,w_HR_Call_Log):SELECT dbo_w_HR_Call_Log.WinsID, dbo_w_HR_Call_Log.ProjID10,FORMAT(CDATE(DATEADD('s',startdate-(5*60*60),'01-01-197000:00:00')),"yyyy-mm-dd") AS HoursDate,CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970 00:00:00')) ASStartDT,CDATE(DATEADD('s',enddate-(5*60*60),'01-01-1970 00:00:00')) AS EndDT,DatePart('w',[StartDT]) AS StartDTDayOfWeek, Duration,IIf(StartDTDayOfWeek=1 Or StartDTDayOfWeek=7,Duration,0) ASWeekendSeconds,FROM dbo_w_HR_Call_LogWHERE WinsID<>'0'
View 3 Replies View RelatedI have a float variable that holds a decimal number of hours.
So 1.5 equals 1 hour 30 minutes.
I need to change this to the format 1:30
Any idea how to do this?
Hello....Can someone help me please?I had a mdf file with 48 Mb...and suddenly, the size of it increasedto 690 Mb (without any action or movement of data)...Why could it be done?....is there any solution for thar?Thank you.Tony
View 1 Replies View RelatedHow to decrease msdbdat.mdf size? This is a bit urgent and it would be great if anyone can reply on this.
Gaurav Arora
Hi,I want to setup one of the fields in a table so it incrementssequentially(int data type). i.e the first record should be record 1and the second one should be 2 and so on. This field will also be thekey field. I am new to SQL and don't know how to do this.I am using SQL server 2000.Thanks for the help in advance.-S
View 9 Replies View RelatedHi, recently we increased our internal memory from 2 to 3.5 GB.Nevertheless, SQL Server does not seem to use this extra memory.Do we have to do something extra ???Arno de Jong,The Netherlands.
View 1 Replies View Relatedi have a field Count that i want it to be automatically increased by 1 whenever a SELECT stament is called. i come out with the following sql query and it works on SQL Query Analyzer:
declare @count int
select @count = count+1 from pictures_posts where itemtype='3'
update pictures_posts set count=@count where itemtype='3'
select postID, color, count from pictures_posts where itemtype='3'
go
but i am thinking it must be a much better to do this. can you guys help?
Does any one know what could be making the size of a backup get so big in SQL server?
I noticed if you back-up the same database in Enterprise Manager over and over (without making any changes to the database), the size of the backup gets bigger and bigger. To get around this I simply erase the backup and create a new one.
Now I'm experiencing the same kind of problem, different situation. I decided to make very few changes to my database. If anything, I shrunk the size of the tables and stored procedures.... Now all of a sudden my database backup is 7 times larger.
What could be increasing the size so much, if I haven't increased the amount of tables or stored procedures?
What is the log file about? Mine is huge? Is there a way to reset it or clear it?
any help would be great.
Thank you,
Alec
Hi,
I was running out of space and thus deleted some rows from a table. To my surprise the db size increased. I then shrunk it to bring it back to what it was earlier.
When i deleted some 5000 rows, some space must have been released. Where did the space go and why did the db size increase after deleting the records?
I thght it might be log files..but db is set to Simple Recovery which does not utilize a Log File.
Any reasons?
i have add maintenance plan rebuild index in sql server 2014  log file  increased very tremendous space.
View 6 Replies View RelatedWhy I see absolutely no performance improvement when I spread my primary file group over 8 separate files on 8 separate disks, as opposed to having the primary file group all in one file on one disk.
I have set up 2 identical databases, one spread over 8 disks and one on one disk. Each database has a table called DATA and a column called VALUE. Value is NVARCHAR(200). I have filled each table up in both databases with 20,000 rows.
I then perform a select on each table in each database using CHECKPOINT and DBCC DROPCLEANBUFFERS to ensure I am reading from disk before each query and the execution times are identical in both databases.
I then ran the same queries against each database using a load testing tool and the batch requests per second on each DB is identical under load.
Surely the database with data spread over 8 disks should be FAR faster than the single file database as you have the combined reading power of 8 disks as opposed to 2??
Also, the same is happening for write speeds. When I create the data on both databases, the time it takes is identical on both.
BOL says it should be faster with multiple disks.
Just FYI this is on an Azure virtual machine and each disk is a locally redundant data disk that I have attached to the virtual machine.
Whether write speeds should increase with multiple disks or just read speeds?
i want to do fragmentation using SQL 2005 Express. Can their be any helping material for making fragmentation. Any Example
View 2 Replies View RelatedWhat DBCC or otherwise one would be able to find table fragmentation in SQL 7 and how to take some proactive actions ?
Thanks very much for your time.
-Tim
Would someone please let me know which is the better way to reduce fragmentation on Windows:NT drive?
dbcc indexdefrag
or
dbcc reindex
or
dropping and recreating indexes through SQL?
Thanks
Vinnie
Sql Server 2000.
For simplicity, I am avoiding minor details.
Here is a scenario.
Create Table T (T_ID int PK, €¦..) and this table is referenced by 20 other places on T_ID.
And these 20 tables have T_ID as PK or part of PK.
We have large volume of deletes in T and in the other 20 tables based on T_ID.
Once the deletes are done, will dropping and re-creating the FK€™s that reference the T_ID
help reduce the fragmentation caused by the deletes?
hi all,
i have inherited a database running on sql7 sp2. a lot of the tables do not have a clustered index. some of these tables are highly fragmented. below is a sample of the dbcc showcontig output
TABLE level scan performed.
- Pages Scanned................................: 104934
- Extents Scanned..............................: 13124
- Extent Switches..............................: 13123
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.95% [13117:13124]
- Extent Scan Fragmentation ...................: 5.84%
- Avg. Bytes Free per Page.....................: 7757.4
- Avg. Page Density (full).....................: 4.16%
this table has 5 non-null char columns whose total length is 43 chars and the table occupies about 900 mb for only about 670,000 rows!
select on the table sucks!
since there is no clustered index i have to rebuild the table to remove the fragmentation. is there a way to reorganize the table without recreating it?
thanx
:-)
hemant
Hi!
I have a problem with defragmenting the tables. When i ran dbcc showcontig for the tables, it shows 94%, 50%, 33% fragmentations . I have rebuild indexes on all those tables and ran dbcc showcontig again but still it shows the same % of fragmentations. Is there any way to remove fragmentation on all the tables completely. Any help would be appreciated.
Thanks,
Saran
One of my production servers has been determined to be 92% fragmented.
What's the proper procedure for defraging a database server?
I couldn't find anything very helpful in BOL, nor Knowledge Base.
Sidney Ives
Database Administrator
Sentara Healthcare
What's the best way to find out if disk fragmentation on Windows 2000 Server is affecting SQL Server performance?
If disk fragmentation is shown to be a cause of performance problems, what are the recommendations for a disk fragmentation strategy? eg. use the win 2000 built in disk defrag utility or buy a 3rd party product like DiskKeeper? How much of an overhead is a product like DiskKeeper that defrags in the background?
Clive
Hello,
How can I mesure the database fragmentation ? Cause DBCC SHOWCONTIG shows obects fragmentation only. I would like to see the whole database fragmentation.
Thanks for help
What functions of tools do you use for managing index fragmentation?
DBCC?
I am working through MS Press SQL 2005 book and it mentions the
sys.dm_db_index_physical_stats function? It then give an example of code which is very involved.
Does anybody use this function?
Thanks
before rebuild index
---------------------
tablenameIndexname
avg_fragmentation_in_percent
Payoff_QuotePK_Payoff_QuoteDetail
83.3333333333333
ALTER INDEX ALL ON Payoff_Quote REBUILD
after issue the above statement result remain same.
Hi
When I run sys.dm_db_index_physical_stats on AdventureWorks.Production.Product, I get an average fragmentaion of 46%, 0%, 25% & 50% on the Indexes.
I run ALTER INDEX ALL ON AdventureWorks.Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)
and the index fragmentation does not change...
Why is that?? I have tried refreshing the table and the statistics but why does it look like rebuilding the indexes has done nothing?
I have a nice script that will look at the index fragmentation by using the DMV (sys.dm_db_index_physical_stats) and if its above a specificed threshhold, it will rebuild or reorg the index.
What I have noticed is that even after reorging and/or rebuilding the index, the fragmentation percent in the DMV does not change for some indexes. Other indexes are updated just fine.
Is this a result of updating statistics? Why would the fragmentation change for some indexes and not others? Why does it seem no matter how much rebuilding or reorging is done, the fragmentation percent for some indexes does not change?
- Eric
I'm attempting to debug some query timeouts on a production server.
Several tables involved in this query have logical scan fragmentation levels around 10%, and extent scan fragmentation levels 90%+.
Running INDEXDEFRAG or DBREINDEX reduces the logical scan fragmentation down to almost 0%, but the extent scan fragmentation stays upwards of 90%.
Why? What can I do to get this fragmentation down? Is it because this particular index is on a GUID?
We backup a lot of SQL databases. The db admin uses SQL to dump the databases to a *.bak file on a network share, then we pick them up to tape.
The problem is that for some reason, the backup files are MASSIVELY fragmented, which kills our backup speed to tape (via Netbackup Enterprise). If I defragment the drive (via the built-in tool on Win2K3 or PerfectDisk 8) our speed to tape more than doubles (can go from 16MB/sec to 36MB/sec). However, after the next SQL backup, the drive is completley fragmented once again (around 70%).
Is there any way to improve how these files are written to disk (to keep them relatively in one piece)? For some reason it appears this is a bigger issue with the SQL backup files than any other file type. Thanks.
Jim B
I am running this script to see the fragmentation of indexes.
SELECT a.database_id
,a.object_id
,a.index_id
,b.name
,a.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (7,NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id
AND a.index_id = b.index_id
where a.database_id = db_id()
order by a.avg_fragmentation_in_percent desc
When I rebuild an index the fragmentation does not change 10% or lower (default index fill factor is 0, which I think is 90% fill)
SQL Server 2005 EE SP 2
Thanks,
~Joseph~
Hi,
How do you defragement SQL database data files? Running the DBCC DBREINDEX , DBCC INDEXDEFRAG, does that minimize fragmentations of the tables and the indexes files or is it just the index files?
Thanks in advance
a