Maintenance Plan
-
Hi:
I have a "Maintenance Plan" job, which has two tasks in the Optimization tab:
First:
under "Reorganize data and index pages" I choose: "change free space per page percentage to" 10%. For this part of job, the log file shows:
Database (db name): Index Rebuild (leaving 10% free space)...
Rebuilding indexes for table 'table1 name'
Rebuilding indexes for table 'table2 name'
Rebuilding indexes for table 'table3 name''
Rebuilding indexes for table 'table4 name'
Rebuilding indexes for table 'table5 name'
** Execution Time: 0 hrs, 20 mins, 28 secs **
So, I believe that the indexes were recreated after the job run successfully, right?
Second: under "Remove unused space from database files" I choose:
Shrink database when it grows beyound: 500 MB.
Amount of free space to remain after shrink: 10%
For this part of task: the log file shows:
Database (dbname) Removing unused space from the database files (if database size is more than 500 MB). Reducing free space to 10 percent of data...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Deleting old text reports... 1 file(s) deleted
.
End of maintenance plan 'DB Maintenance Plan2' on 7/31/2005 2:20:31 AM
SQLMAINT.EXE Process Exit Code: 0 (Success)
The job runs on every Sunday, on Monday, I checked the size of the database, the size of it did not shrink to what I expect. The total size of the db was more than 7g, 3.5g with data and 3.5g free space. On Monday, I still have 3.5 g free space, why?
I can run db shrink job manually and to get the space back, but after 2 days, the released space used again, the ER shows that db still takes 7g space. why?
-
I know that the index rebuild can take up a LOT of place... but you do the shrink after that so I'm a little stumped too. Do you reindex on other days of the week?
Did you delete big blob columns lately?
-
Hi Remi,
Thank you for the reply. However, I don't reindex on other days of the week. The job that contains the two tasks, runs on the same day: every Sunday afternoon.
I don't have blob columns in my database, although I do have columns contains pointers to the image folders, and some folders are deleted each day after the images are sent to the archive.
It really is a stumper, huh?
Thanks
Yan
-
New information that you might find usefull here :
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=207156
-
Hi Carpal:
Thank you for giving me the link, I read it through. However, I don't use "Text" datatype but "varchar", so I don't have to rebuild the tables, right?
The problem is if I run the dbcc shrinkdatabase as an auto job in the night, I can see the database was shrink on the second day from the ER. (for example before shrink it was 7.0g with 3.5 data and 3.5g free space , after shrink, it became 3.8g). However, after 2 days, it became 7.0g again. Why?
Thanks.
Yan
-
1 - My name is Remi, my surname is carpal tunnel because I post too much
.2 - You shouldn't have to rebuild the table(s).
3 - What's the auto-grow setting on that db?
4 - You say that is shrinks, so the shrink does work right??
5 - Do you have any huge job that could make the db grow back in the week days?
6 - Might it be the transaction logs that grow that much during the week?
-
Hi Remi:
Ha...ha..ha.. sorry for the last time, called you the wrong name.
The database auto-grow setting is set to 10% with "unrestricted file growth".
If I only run the shrink job, yes, it does work, but I don't think the maintenance plan job which contains the shrinking task works because on the second day, the freed space disppears again.
I don't have any huge jobs that could make the db grows, the only job I run daily is the full database backup.
Transaction logs grow too much???... hmmmmm. Don't know,
Yan
-
What's the normal growth of the db?
1 gig month/week?
It's hard to tell what's going on without knowing what normal actually is. Can you get this info?
-
Do you have Check Database Integrity on the Integrity tab checked? This will increase the log size but I don't remember if it increases the database size.
Steve
-
Remi:
Not that big, I did not record the growth weekly. It is 8.0g now, last year this time, it was around 6.5g. The database is kept growing on one hand, and on the other, there is a service keeps deleting records when the items have been transfered to the archive.
Yan
-
Hi Steve:
Yes, I do have the "Check Database Integraity" on the Integrity tab. Thanks
Yan
-
Hi Steve:
I do have the "Check Database Integrity" on the Integrity tab checked.
Thanks
Yan
-
You'll have to turn the profiler on to see what's going on... Maybe you could check the db size every 5 minutes combined with a full trace. when you see an abnormal growth, you'll be able to see the command(s) that did it.
-
Hi Remi:
Thanks, I will.
Yan
-
Hi Remi:
This morning I checked the db size and found out that the free space has been jumped from 4.9g to 6.2g. The data size is hardly changed. During weekend, only 3 auto jobs ran: (1) backup (2) maintainance plan: reindex and shrink db (3) check integraty. clearly shrink db did not work, or check integraty enlarge the size of db's free space. Right?
Yan
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply