September 3, 2004 at 2:37 am
A production logging database with a lot of updates and inserts got 'unmanageable' at a certain point. All DBCC statements timed out. The only TSQL statement that was executed successfully (without timing out) was a full backup.
After restoring from that backup the database was manageable again (no timeouts).
May question: Is there a defragmentation or index rebuilding during backup or restore process ?
September 6, 2004 at 4:12 am
There are two points for fragmentation of database files.
First is FileSystem fragmentation, which occurs in the OS level, when data is added to the database, and it grows, but other files in the OS are added / deleted, creating fragmentation in the .mdf / .ndf / .ldf database files.
Second it DataPage fragmentation, inside the database file, that occurs when sql server adds / deletes data from tables/indexes, etc. This is viewable with DBCC SHOWCONTIG.
DBCC INDEXDEFRAG, DBCC DBREINDEX can help the second fragmentation problem. But not the first.
When you Restored the database, SQL Server probably deleted the original OS databasefiles, and created fresh ones. When doing this on a NTFS permission, windows will do it's best to create the file with as few fragments as possible. Often you will have dramatically fewer fragments after this. Then SQL Server will restore the datapages into this file on disk.
I'm not sure if SQL server places the datapages back in the same order, or in a more logical order after a restore though.
I've seen a number of database files be much faster after being moved to different drives and back, getting them into a single chunk in the filesystem.
Julian Kuiters
juliankuiters.id.au
September 21, 2004 at 7:25 am
What's DBCC DEFRAGDB?
September 21, 2004 at 10:12 am
He probably meant DBCC DBREINDEX.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
September 21, 2004 at 6:18 pm
I think I was asleep.
DBCC SHOWCONTIG will show the fragmentation level
DBCC INDEXDEFRAG will change and compact the physical order of the leaf level of an index
DBCC DBREINDEX will rebuild indexes
I'm still too asleep, but i've mended the original post.
Julian Kuiters
juliankuiters.id.au
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply