March 8, 2012 at 10:24 am
I log the size of the database file in a table in every 4 hours
Database Name File_Type File_Size_in_MB Space_Used_in_MB Space_Left_in_MB time_collected
Database1data file 11432 87752658 2012-02-20 04:00:00
Database1data file 11432 87992633 2012-02-20 08:00:00
Database1data file 7156 39873168 2012-02-20 11:06:00
Database1data file 7156 39853170 2012-02-20 12:00:00
The file size suddenly dropped to 7156 from 11432 and I am wondering what causd this. There has not been any bulk deletion. Can anybody tell me why this could have happened or what might have caused this?
Any help is greatly appreciated.
Thanks
March 8, 2012 at 10:44 am
No real way to tell what caused the shrink but here's something that would show you if it did in fact shrink.
SELECT TE.name AS [EventName] ,
T.DatabaseName ,
t.DatabaseID ,
t.NTDomainName ,
t.ApplicationName ,
t.LoginName ,
t.SPID ,
t.Duration ,
t.StartTime ,
t.EndTime
FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1
f.[value]
FROM sys.fn_trace_getinfo(NULL) f
WHERE f.property = 2
)), DEFAULT) T
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
WHERE te.name = 'Data File Auto Grow'
OR te.name = 'Data File Auto Shrink'
ORDER BY t.StartTime ;
March 8, 2012 at 9:45 pm
Do you have any index rebuild jobs enabled between the time period where the shrink of file actually happened. I have a feeling that your tables might be fragemented and the index rebuild would have removed the fragmentation.
I hope this will not be a difficult task to figure it out.
March 8, 2012 at 10:26 pm
Guras (3/8/2012)
Database Name File_Type File_Size_in_MB Space_Used_in_MB Space_Left_in_MB time_collectedDatabase1data file 11432 87992633 2012-02-20 08:00:00
Database1data file 7156 39873168 2012-02-20 11:06:00
The file size suddenly dropped to 7156 from 11432 and I am wondering what causd this.
Your file size has not dropped.
It has grown from 7,156 MB to 11,432 MB.
Auto-growth, I guess.
Note that Space_Used also increased from 3,987 MB to 8,799 MB.
Left aligned numbers confused you, I guess.
March 9, 2012 at 12:25 am
Baabhu is probably going along the right path here. Do you have a maintenance plan on place on this server that rebuilds indexes and maybe shrinks files too?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 9, 2012 at 4:43 pm
I have a reindexing maintenance jOb that runs early in the morning at 4 but here the time when the database shrunk is between 8 and 11 morning. The maintenance job only reindexes . Thanks
March 10, 2012 at 8:53 am
Is Auto-Shrinkk ON?
May be silly question. Still better to confirm.
March 11, 2012 at 10:28 pm
Hi Guras,
Did you manage to figure out what is the issue.
if not, can you please share the scritps that you use to log the db size and if possible the error log where you have believed the shrink would have happened
---- baabhu
March 12, 2012 at 7:09 am
Suresh B. (3/10/2012)
Is Auto-Shrinkk ON?May be silly question. Still better to confirm.
No, the Autoshrink is not ON. Thank you for checking though.
March 12, 2012 at 8:33 am
baabhu (3/11/2012)
Hi Guras,Did you manage to figure out what is the issue.
if not, can you please share the scritps that you use to log the db size and if possible the error log where you have believed the shrink would have happened
---- baabhu
Hi Baabhu,
No, I have no been able to figure this out yet.
Select db_name(),fileid,case when groupid = 0 then 'log file' else 'data file' end,
name,filename,
[file_size] =
convert(int,round((sysfiles.size*1.000)/128.000,0)),
[space_used] =
convert(int,round(fileproperty(sysfiles.name,'SpaceUsed')/128.000,0)),
[space_left] =
convert(int,round((sysfiles.size-fileproperty(sysfiles.name,'SpaceUsed'))/128.000,0)),
getdate()
from
dbo.sysfiles;
The job runs this script and inserts the results in a table.
Thank you.
March 13, 2012 at 1:08 am
Hi Guras,
May be the SQL error log can help you to explain you on the part of the db shrink.
Check with application team any new deployment that deletes junk of data could be the other possibility.
I am running out of ideas.
If possible post the error log of the day when the data was shrink
<Edited>
Have you done any changes in the scripts after you have implemented?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply