Wednesday, May 25, 2005
Databases: Auto shrinking DB files in SQL server
(I've been doing some Database optimization troubleshooting/recommendation lately, so i'll post some of 'em....)
In SQL Server 2000, you can automatically reduce the physical file sizes of your Dabatases (records, Indices, Logs, etc) when SQL server detects that it already has large amounts of free space. But primarily, the purpose of this is just to 'NOT' run out of disk space.
Growing & shrinking your database files causes defragmentation (aside from the consuming resources) which leads to reduced performance of your database. So making these automatic will create additional resource overhead and file fragmentations every time these options kicks in (I believe it checks every 30 minutes).
For me, it's ok to leave the 'automatically grow files' on. But as much as possible be accurate on the file growth values.
Manually 'Shrink, then Reindex' your database regularly. Try to use DBREINDEX command as much as possible. INDEXDEFRAG only if you can't afford to many locks upon these maintenance tasks.
On the other hand, if you really want to defragment your files, use a defragmenter tool (Disk Defragmenter). Problem is, you have to turn-off your SQL server because it does not defrag opened files.
[tidbits]
Data pages in SQL server is 8k. (In Oracle, you can set this per database)
512 bytes for Windows 2000 and above. (hmm,.. DO I hear 'Database corruption' possibilities?)
DBCC MEMUSAGE in SQL Server 2000 has been replaced by these performance counters
-Procedure Cache Pages
-Procedure Cache Hit Ratio
-Procedure Cache Pages
-Procedure Cache Object Counts
In SQL Server 2000, you can automatically reduce the physical file sizes of your Dabatases (records, Indices, Logs, etc) when SQL server detects that it already has large amounts of free space. But primarily, the purpose of this is just to 'NOT' run out of disk space.
Growing & shrinking your database files causes defragmentation (aside from the consuming resources) which leads to reduced performance of your database. So making these automatic will create additional resource overhead and file fragmentations every time these options kicks in (I believe it checks every 30 minutes).
For me, it's ok to leave the 'automatically grow files' on. But as much as possible be accurate on the file growth values.
Manually 'Shrink, then Reindex' your database regularly. Try to use DBREINDEX command as much as possible. INDEXDEFRAG only if you can't afford to many locks upon these maintenance tasks.
On the other hand, if you really want to defragment your files, use a defragmenter tool (Disk Defragmenter). Problem is, you have to turn-off your SQL server because it does not defrag opened files.
[tidbits]
Data pages in SQL server is 8k. (In Oracle, you can set this per database)
512 bytes for Windows 2000 and above. (hmm,.. DO I hear 'Database corruption' possibilities?)
DBCC MEMUSAGE in SQL Server 2000 has been replaced by these performance counters
-Procedure Cache Pages
-Procedure Cache Hit Ratio
-Procedure Cache Pages
-Procedure Cache Object Counts
# posted by umberto @ 5:08 PM 
gs