Please help, database will not truncate free space at end of file

I'll explain the process, then I'll explain the problem.
SQL Server Enterprise Edition, Windows NT, SP2
We have a main database server A, and a report server B.
This is all done through Enterprise Manager:
We reindex database A, which causes it to grow in size, backup the
transaction log, shrink the database, truncate free space at the end of the
file, back it up, then restore it to the report server. We HAVE to shrink it
because there's little room left on server B for the database.
We did this same process recently, however the free space at the end of the
database file will not truncate. It did before, but not now.
I've read some of the articles posted, and I'm shy about trying any of them
since this is main production and would be bad, to say the least, if anything
should happen. One article I read said that I should do this in QA with a
TRUNCATEONLY switch, will the data be affected?
If any of you have a simple explanation or help I would really appreciate it.
Thanks in advance.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200609/1
It sounds like you have an open transaction in the database. What does DBCC
OPENTRAN() say for that db? The process you are going thru is very flawed
in that the shrink process undoes most of what you tried to accomplish with
the reindexing in the first place. The proper answer is get more disk space
on the reporting server and you can alleviate that whole process and have a
much better operation overall.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Andrew J. Kelly SQL MVP
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:6662f70524ce3@.uwe...
> I'll explain the process, then I'll explain the problem.
> SQL Server Enterprise Edition, Windows NT, SP2
> We have a main database server A, and a report server B.
> This is all done through Enterprise Manager:
> We reindex database A, which causes it to grow in size, backup the
> transaction log, shrink the database, truncate free space at the end of
> the
> file, back it up, then restore it to the report server. We HAVE to shrink
> it
> because there's little room left on server B for the database.
> We did this same process recently, however the free space at the end of
> the
> database file will not truncate. It did before, but not now.
> I've read some of the articles posted, and I'm shy about trying any of
> them
> since this is main production and would be bad, to say the least, if
> anything
> should happen. One article I read said that I should do this in QA with a
> TRUNCATEONLY switch, will the data be affected?
> If any of you have a simple explanation or help I would really appreciate
> it.
> Thanks in advance.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200609/1
>
|||I ran opentran and this is the message:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
Is it true that shrinking the database fragments it? Which ties in with what
you said in your post. Yeah, bottom line, disk space is the answer. Thanks!
Andrew J. Kelly wrote:[vbcol=seagreen]
>It sounds like you have an open transaction in the database. What does DBCC
>OPENTRAN() say for that db? The process you are going thru is very flawed
>in that the shrink process undoes most of what you tried to accomplish with
>the reindexing in the first place. The proper answer is get more disk space
>on the reporting server and you can alleviate that whole process and have a
>much better operation overall.
>http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>[quoted text clipped - 26 lines]
Message posted via http://www.droptable.com
|||> Is it true that shrinking the database fragments it?
Yep. Or, to be more specific, it will fragment the indexes. Or to be even more specific, it will
move pages towards the beginning of the files, page-by-page, possibly resulting in an index which is
more fragmented than it was before the shrink (assuming you had a contiguous index before the
shrink). This is easy to test...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fnadal via droptable.com" <u10790@.uwe> wrote in message news:666545dd8a649@.uwe...
>I ran opentran and this is the message:
> No active open transactions.
> DBCC execution completed. If DBCC printed error messages, contact your system
> administrator.
> Is it true that shrinking the database fragments it? Which ties in with what
> you said in your post. Yeah, bottom line, disk space is the answer. Thanks!
> Andrew J. Kelly wrote:
> --
> Message posted via http://www.droptable.com
>