Reindex and Error 1105

I recently created a new Maintenance Plan to reindex my database. Under the
Optimizations tab I only have Reorganize pages witht the original amount of
free space checked but I still received the 1105 error(I previously had the
Remove unused space from database files checked and received the 1105 error).
I noticed the data file grew from 160GB to 320GB(with ~160GB marked as free
space) after the failed reindex attempt. Please advise how can I fix this
problem. Thanks.
Sometimes autogrow isn't fast enough. In that cases, you need to pre-allocate storage.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ling" <Ling@.discussions.microsoft.com> wrote in message
news:24455A9F-A5CD-45E5-9B89-A3D75FB1BD2D@.microsoft.com...
>I recently created a new Maintenance Plan to reindex my database. Under the
> Optimizations tab I only have Reorganize pages witht the original amount of
> free space checked but I still received the 1105 error(I previously had the
> Remove unused space from database files checked and received the 1105 error).
> I noticed the data file grew from 160GB to 320GB(with ~160GB marked as free
> space) after the failed reindex attempt. Please advise how can I fix this
> problem. Thanks.
|||When you saw 1105 error, did you check if your disk is full or the data file
reaches to its maximum specified size?
The data file growth is expected, when a index is rebuilding, SQL Server has
to keep the old index around. Hence, the space requirement is doubled (plus
additional space for sorting and logging). SQL Server won't truncate the
file automatically, therefore you have a bigger data file after reindex (it
does not matter if the reindex succeeds or fails).
There is no need to worry about the too much free space problem. Since you
have "Remove unused space from database files" checked, DBCC SHRINKDATABASE
will start and truncate the data file sometime later.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ling" <Ling@.discussions.microsoft.com> wrote in message
news:24455A9F-A5CD-45E5-9B89-A3D75FB1BD2D@.microsoft.com...
> I recently created a new Maintenance Plan to reindex my database. Under
the
> Optimizations tab I only have Reorganize pages witht the original amount
of
> free space checked but I still received the 1105 error(I previously had
the
> Remove unused space from database files checked and received the 1105
error).
> I noticed the data file grew from 160GB to 320GB(with ~160GB marked as
free
> space) after the failed reindex attempt. Please advise how can I fix this
> problem. Thanks.
|||I advise you to read the whitepaper below that will explain a bunch about
fragmentation, what to do about it, and when you really need to do anything
about it. Also, be aware that rebuilding an index requires double the index
size (as a new index has to be built before the old one can be dropped).
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eJGECQ0nEHA.1800@.TK2MSFTNGP15.phx.gbl...
> Sometimes autogrow isn't fast enough. In that cases, you need to
pre-allocate storage.[vbcol=seagreen]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ling" <Ling@.discussions.microsoft.com> wrote in message
> news:24455A9F-A5CD-45E5-9B89-A3D75FB1BD2D@.microsoft.com...
the[vbcol=seagreen]
of[vbcol=seagreen]
the[vbcol=seagreen]
error).[vbcol=seagreen]
free[vbcol=seagreen]
this
>