data files get smaller

[Former Member]

hi,

few days ago I noticed that data files (mdf, ndf - i am not talking about log file) sometimes get s small. auto shrink is turned off.

I checked it in dbacockpit->space->history->database size history.

is it OK? why it happens so? as I know without shrinking files should not get small (even if delete much data from data base).

we are doing full backup every night.

(OS - Win2003R2, DB - MSSQL2005 SP2, SAP - ERP6)

thanks in advance

Edited by: pharvana on Jan 22, 2011 8:22 AM

[Former Member]

You may want to check following note:

Note 142731 - DBCC checks of SQL server

[Former Member]

thank you Manoj

i know this note, i have reread it but could nod found reason why data files get smaller.

could you give me excerpt?

[xymanuel]

Hi pharvana,

i know this behavoir of the SQL Server for the DBCC CHECKDB command. The DB Files are growing during the CHECKDB and after finishing, the Files shrunk back to their origin size.

For the CHECKDB Command it normal.

I dunno if it is normal for the time where an Fullbackup is running.

Kind regards

Manuel

[Former Member]

hi Manuel

thank you,

we do dbcc checkdb before fullbackup, but we start dbcc... at 00:00 and dbsize value system takes at 23:40 (before checkdb starts).

if after checkdb files get their original size then we would not have such sql server behavior (files could not get smaller at 23:40). am i right?

do you know any official source (sap or microsoft) where this issue is described?