Re: Shrinking a SQL Server 6.5 db

Al Shannon (noAddress) writes:
Thanks for the description of SHRINKDB. Is Books Online (6.5) the online
manuals installed on the server when SQL Server is installed, or is it a
link to the books on the Microsoft web site? I didn't find online
references for SQL Server 6.5.

Does this mean that my customer will have to do more than execute
SHRINKDB to shrink his database? I suspect that there is a lot of
unused space in the database and that he simply needs to recover that
space. I don't think he needs larger disk drives.

My customer is using an automated application that has been available
for many years. I suspect that either SQL 6.5 was current when he first
started using the application or that's what he had in place, for other
reasons, at the time. In any event, as long as the application
continues to run there's no need to replace the db software.

But as I understood it is not running right now. Or at least it is in the
process of running into a roadblock.

Let me put it this way: if this was my client, I would probably script the
database, bulk-load out all data, rebuild it from scripts and bulk-load
the data back. Or I would simply expand the database. But there is a very
important difference between me and you. I worked with that architecture
for ten years, and you have probably not seen it before.

And that's the problem your customer has: difficult to find competence
to support his installation. That's a good reason why they should look
into migrate the application to SQL 2005 (which supports compatibility
level 6.5). I would guess that it is possible to run this application
on SQL Express, so there would be no license cost. But of course, the
migration will come with a price tag.

To go back to original question, I don't think SHRINK is even useful
in your case. On 6.5 when you create a database, you first create one
or more "devices", which is a file (or a raw partition) which can hold
pieces from many databases. When you create a database, you specify
which devices it should be spread out over. Thus, there is a many-to-many
relation between devices and databases. For each segment you add to a
database, you specify the size of it and there is no autogrow.

So the first thing to examine is whether there any space left on existing
devices. There are some good graphical tools for this in Enterprise
Manager. If there is not, you can create a new device. (I don't think
you and extend an existing device, but I could be wrong on that point.)

One thing is for sure: shrinking is not likely to help, if you get an 1105
error. That errors means that there are no free extents, and shrinking
is about removin unused extents. But it is not unlikely though that
reindexing will recover some space. Problem though is that you may need
some space to reindex... This is why I mentioned the script/bulk
alternative.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Getting started in IT.
    ... Have ordered both books. ... MCITP SQL Server 2005 Database Administration All-in-One Exam Guide ...
    (microsoft.public.cert.mcdba)
  • Re: Transaction log keeps growing
    ... Introduced in SQL Server 7.0 was the ability automatically grow and to ... shrink the physical size of database data and transaction log files. ...
    (comp.databases.ms-sqlserver)
  • Re: MS Access DAO -> ADO.NET Migration
    ... William Vaughn ... Microsoft MVP ... Hitchhiker's Guide to Visual Studio and SQL Server ... My migration app works building a SSCE database file with imported data ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Shrinking MDF and LDF Files
    ... written a stored procedure that will delete all the data in all tables and ... With the database empty I'd like to SHRINK the database so the files are ... State what version of SQL Server you are using and specify the content ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Cluster will not fail over.
    ... > As far as the TCP/IP issue goes, you had to rebuild the cluster and were ... > able to restore the master database. ... > a cluster installation you'll have to revisit. ... >> This worked bringing up the sql server in minimal mode. ...
    (microsoft.public.sqlserver.clustering)
Loading