May 12, 2008 at 2:25 pm
Hi All,
We recently migrated a database from SQL Server 2000 to SQL Server 2005 (which has yet to have service packs applied). As the migration was a side by side migration, we had to re-create all of the maintenance plans and jobs that were running on the 2000 instance. At this point the database was about 3.5 GB in size (2.4 GB data file and 1.1 GB in indexes)
In re-creating the optimizations I inadvertently missed unchecking the Sunday box on the Reorganize step of the maintenance plan I was creating, so what ended up happening was that a Reorganize job ran at the same time as a DBREINDEX. As a result of this the database grew to over 5 times its original size. (We made sure this was the culprit by restoring the last normal backup in a test environment and running those steps again, and yes, it grew to the same size).
The database is now about 20 GB in size (12.8 GB of data and 7.8 GB of indexes).
We have tried reindexing, updating statistics, and shrinkfile to no avail. Does anybody have any ideas of how we can get the size of this database back down to normal?
Thanks!!
May 12, 2008 at 2:35 pm
Have you run a TX Log backup? That should allow you to shrink the TX Log. Otherwise you should be able to shrink the DB as long as there is free space in the DB.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 12, 2008 at 2:47 pm
The Trans Log is backing up every 1/2 hour and truncates. We have tried to shrink the database, but it never reduces in size.
sp_spaceused shows me the following:
database_namedatabase_sizeunallocated space
mas500_app27825.81 MB4460.17 MB
reserved dataindex_size unused
20729488 KB 12739928 KB7760920 KB 228640 KB
Unless I am reading these results wrong, there doesn't appear to be much free space here.
Thanks,
Jeff
May 12, 2008 at 2:57 pm
According to sp_spaceused you have 4GB of unallocated space in the database. In BOL under DBCC ShrinkFile iit suggests running:
[font="Courier New"]SELECT
name,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS AvailableSpaceInMB
FROM
sys.database_files;[/font]
to verify that there is enough space to shrink it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 12, 2008 at 3:33 pm
Thanks for the input. I am able to shrink the various data files to remove the free space, but that still leaves me with the question of what happened to the data files to begin with that made them grow so much? And is all the growth free space? I did a restore of the last backup before it grew so much and here are the numbers on it:
database_namedatabase_sizeunallocated space
Mas500_test25060.63 MB864.63 MB
reserved data index_size unused
3655672 KB 2477608 KB1113704 KB 64360 KB
I also checked the size of the largest table in order to do a comparison.
Before:
name rowsreserved data index_size tciMaintAuditLog4431351 632592 KB 458752 KB 173672 KB
Unused
168 KB
After:
name rowsreserved data tciMaintAuditLog4467763 4531536 KB 3260080 KB
index_size unused
270912 KB544 KB
I'm not understanding how the data figures can be so different between the two.
May 12, 2008 at 5:49 pm
I just would like to check one thing. As i understand it you're using Maintenance Plans. In the Maintenance Plan Rebuild Index Task you have a setting called 'Change free space per page percentage to'. What did you set that to?
Ola Hallengren
May 13, 2008 at 7:48 am
In our version of SQL Server that value is not available to be set in the maintenance plan, but it got me looking in the right place. A query of sys.indexes showed me that the fill factor was 12. I have been running alter index on a few of the largest tables with a higher fill factor and that is doing the trick.
THANK YOU ALL for pointing me in the right direction.
Jeff
May 13, 2008 at 8:05 am
I have a stored procedure that maybe could be useful for you. It is using the DMV
sys.dm_db_index_physical_stats to dynamically rebuild / reorganize indexes based on the fragmentation levels.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Ola Hallengren
May 13, 2008 at 8:38 am
Thank you, that looks like a great script. We will definitely be looking at ways to implement it once our db is cleaned up.
Thanks again for your help.
Jeff
May 27, 2008 at 1:18 pm
I would say, take a FULL db Backup and try for TLOG backup and then Shrink you log file.
MCP, MCTS (GDBA/EDA)
June 8, 2008 at 2:56 am
If you rebuild your indexes, and don't have the option SORT_IN_TEMPDB, SQL will build the new index in the same filegroup of the original index in these steps:
1) say you have a 5MB index
2) SQL is claiming 5 MB of free space in that file (which might result to a autogrow event)
3) New index is being created and marked as active
4) old index is marked as inactive and pages are marked as free
As a result your files might grow, but contains some space.
If you have multiple files in a filegroup, you'll see that an index in file1 is being rebuild in file2. The next time, it will be rebuilded in file1
If you use the SORT_IN_TEMPDB, step 2 is executed in TempDB
Wilfred
The best things in life are the simple things
June 8, 2008 at 7:33 am
Wilfred van Dijk (6/8/2008)
If you rebuild your indexes, and don't have the option SORT_IN_TEMPDB, SQL will build the new index in the same filegroup of the original index in these steps:1) say you have a 5MB index
2) SQL is claiming 5 MB of free space in that file (which might result to a autogrow event)
3) New index is being created and marked as active
4) old index is marked as inactive and pages are marked as free
As a result your files might grow, but contains some space.
If you have multiple files in a filegroup, you'll see that an index in file1 is being rebuild in file2. The next time, it will be rebuilded in file1
If you use the SORT_IN_TEMPDB, step 2 is executed in TempDB
This is misleading. In Step 2, SQL does not pre-allocate or pre-claim any space for the index rebuild operation. Space is allocated as it is needed during the operation. Space is needed both for the new index (which will always be in the same filegroup as the old index, unless you've used CREATE .... WITH DROP_EXISTING and have moved the index), and for the sort operation (if one is done at all - the sort step can be skipped in some circumstances in 2005). If you use SORT_IN_TEMPDB, only the sort portion of the index build (needing about 20% of the size of the index) will be done in tempdb.
Your statement about multiple files is wrong. The space needed to rebuild the index will be spread across the files in the filegroup according the allocation system's proportional fill algorithm. There's no concept of having an index (or any other object) limited to a single file in a multi-file filegroup.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
June 8, 2008 at 10:05 am
Was the behaviour of spreading a rebuilded index over multiple files also working in SQL 2000?
Wilfred
The best things in life are the simple things
June 8, 2008 at 10:15 am
Absolutely - 2000 and 7.0 both had essentially the same allocation sub-system in the Storage Engine as 2005.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
June 8, 2008 at 8:27 pm
Thanks Paul Randal, I got something new from you.
MCP, MCTS (GDBA/EDA)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply