Information générale
Forum:
Microsoft SQL Server
I agree Sergey. Re-reading my message I see how it gives that impression.
>I don't think that it's a good idea to allow autoshrink for the database with a lot of inserts.
>
>>Just to make sure that you don't waste your time trying to fix a page split problem that may or may not be the root cause, have you done the following?
>>
>>Check on the recovery model of the database. If it is set to full recovery than you MUST perform regular backups or log shipping or the database will continue to grow forever.
>>
>>Database configured for autoshrink? If not, use DBCC SHRINKDATABASE after the backup or log ship in the maintenance plan.
>>
>>I strongly suspect that one of the above is the cause of the expanding size. The whole char -> varchar with updates problem is pretty rare because after the first page split, there will be plenty of room for future updates in the half-empty page. But if those items all check out, run DBCC SHOWCONTIG on the tables in the database to find out which ones are highly fragmented.
>>
>>The best fix at the table level is to create a clustered index on an identity column. The changed clustered index will not prevent page splits when an expanding varchar column pushes data out of the page, but it will make better use of that empty space on future inserts.
>>
>>Dropping and recreating the clustered index will also compact the database. After it finishes, run DBCC SHRINKDATABASE to release the empty pages back to the OS.
Précédent
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement