April 7, 2009 at 9:57 am
I have a server that needs a bit of room for a few more db's. There are 3 test db's that have some extra space so I decided to shrink the files a bit. I took each one down 1GB and all was well.....until. The next day, all 3 are back to their exact original sizes. I've never had anything like that happen before. What is going on?
April 7, 2009 at 10:12 am
did you shirnk the database file or the log file?
April 7, 2009 at 10:14 am
What is the recovery model? If its full are there tranaction log backups being run? What part is growing the mdf (data) or the log file? Where there are maintenance jobs that ran like a reindex operation that might increase log space.
Francis
April 7, 2009 at 10:18 am
steveb (4/7/2009)
did you shirnk the database file or the log file?
Ooops, sorry. I shrank the data file. Recovery model is simple on all of them. Daily backups are being run. I don't believe the files are auto growing as they all go back to exactly the same size at the same time and these dbs are not heavily used as they are dev and test and training dbs.
April 7, 2009 at 10:23 am
In that case is Auto Shrink turned on the database(s). I don't know if you are familiar with these options but in case you are not see http://www.sqlservercentral.com/articles/Administration/autocloseandautoshrinkjustdont/984/
Francis
April 7, 2009 at 10:26 am
fhanlon (4/7/2009)
No autoshrink.
April 7, 2009 at 11:52 am
Check if there is anything in the SQL log at the time that might help. (maybe a restore job??)
Francis
April 7, 2009 at 11:58 am
fhanlon (4/7/2009)
Check if there is anything in the SQL log at the time that might help. (maybe a restore job??)
Nope, nothing of the sort.
So, these data files show 2gb of free space in them. I shrink all of them by 1gb. Free space now shows 1gb of free space in each data file. Free space on the drive shows this as well. Not sure of the exact time but sure enough, by tomorrow they will all be right back where they started. I guess I'll have to do some closer monitoring but I'm not sure what to look at. If it was auto growth, they would be growing by 100MB because that's where it's set. What's the chance all of them would grow by exactly the same amount at the same time to arrive at exactly where they started. I'm stumped.
April 7, 2009 at 4:05 pm
ben.rosato (4/7/2009)
fhanlon (4/7/2009)
Check if there is anything in the SQL log at the time that might help. (maybe a restore job??)Nope, nothing of the sort.
So, these data files show 2gb of free space in them. I shrink all of them by 1gb. Free space now shows 1gb of free space in each data file. Free space on the drive shows this as well. Not sure of the exact time but sure enough, by tomorrow they will all be right back where they started. I guess I'll have to do some closer monitoring but I'm not sure what to look at. If it was auto growth, they would be growing by 100MB because that's where it's set. What's the chance all of them would grow by exactly the same amount at the same time to arrive at exactly where they started. I'm stumped.
Have you got any jobs running over the night as part of your maintenance? Can you post the actual figures and also the commands that you are using to shrink the files?
April 7, 2009 at 4:16 pm
ben.rosato (4/7/2009)
fhanlon (4/7/2009)
Check if there is anything in the SQL log at the time that might help. (maybe a restore job??)Nope, nothing of the sort.
So, these data files show 2gb of free space in them. I shrink all of them by 1gb. Free space now shows 1gb of free space in each data file. Free space on the drive shows this as well. Not sure of the exact time but sure enough, by tomorrow they will all be right back where they started. I guess I'll have to do some closer monitoring but I'm not sure what to look at. If it was auto growth, they would be growing by 100MB because that's where it's set. What's the chance all of them would grow by exactly the same amount at the same time to arrive at exactly where they started. I'm stumped.
A maintenance plan that rebuilt indexes could cause this kind of effect. I guess a good clue is if your shrink leaves them pretty much full?
.
April 8, 2009 at 7:02 am
Tim Walker (4/7/2009).
A maintenance plan that rebuilt indexes could cause this kind of effect. I guess a good clue is if your shrink leaves them pretty much full?[/quote]
Nope, I leave 1GB free.
Here's the current numbers
Currently allocated 12300.00 MB
Available free space 1795.81 MB (14%)
Here's what I'll run.
DBCC SHRINKFILE (N'Vanilla500054_Data' , 11300)
April 8, 2009 at 7:47 am
Results are
Currently allocated 11300.00 MB
Available free space 798.88 MB (7%)
April 8, 2009 at 7:49 am
ben.rosato (4/8/2009)
Results areCurrently allocated 11300.00 MB
Available free space 798.88 MB (7%)
So, its almost changing by 1GB?
April 8, 2009 at 7:51 am
Krishna Potlakayala (4/8/2009)
ben.rosato (4/8/2009)
Results areCurrently allocated 11300.00 MB
Available free space 798.88 MB (7%)
So, its almost changing by 1GB?
Yep, It will go right back to 12300 MB exactly.
April 8, 2009 at 8:02 am
You can find out when it expanded by looking in the ..\Microsoft SQL Server\MSSQL.1\MSSQL\LOG directory. By default SQL creates trace files in there and one of the things traced is the expand event. You should be able to use this to find out which process expanded it.
Is your auto expand parameter for the file also set to 1GB? If so, you could try a smaller value to get more idea what is going on.
.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply