Database file shrink if auto-shrink off

Database file shrink if auto-shrink off

Post by Steve » Fri, 21 Feb 2003 11:21:46



We have a database that has several procedures run against
it overnight, these include an optimisation, and also the
sqlmaint equivalent of DBCC CHECKDB.

The database has auto-shrink switched off, but the
database file size does still sometimes shrink. Why is
this?

This is SQL Server v7 SP2 running on Windows NT4 SP6

 
 
 

Database file shrink if auto-shrink off

Post by Uri Diman » Fri, 21 Feb 2003 11:47:23


Steave
What is recovery model of your database?
Do you perform transact log backup?
Have you checked SQL Server Profiler?
Perhaps someone ran DBCC SHRINKDATABASE manualy


Quote:> We have a database that has several procedures run against
> it overnight, these include an optimisation, and also the
> sqlmaint equivalent of DBCC CHECKDB.

> The database has auto-shrink switched off, but the
> database file size does still sometimes shrink. Why is
> this?

> This is SQL Server v7 SP2 running on Windows NT4 SP6


 
 
 

Database file shrink if auto-shrink off

Post by Steve Overto » Sat, 22 Feb 2003 18:27:45


How would I find out the recovery model? I have been
through the docs and can't find anything there
Transaction logs are backed up, but they are not truncated.
SQL server profiler only includes the sample traces and
these have never been run
Since I am the only one with access to the database I know
that no-one ran the DBCC SHRINKDATABASE command.

Any ideas?

>-----Original Message-----
>Steave
>What is recovery model of your database?
>Do you perform transact log backup?
>Have you checked SQL Server Profiler?
>Perhaps someone ran DBCC SHRINKDATABASE manualy


message

>> We have a database that has several procedures run
against
>> it overnight, these include an optimisation, and also
the
>> sqlmaint equivalent of DBCC CHECKDB.

>> The database has auto-shrink switched off, but the
>> database file size does still sometimes shrink. Why is
>> this?

>> This is SQL Server v7 SP2 running on Windows NT4 SP6

>.

 
 
 

Database file shrink if auto-shrink off

Post by Tibor Karasz » Sat, 22 Feb 2003 20:00:08


If you aren't on 2000, then check out sp_dboption.

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...


> How would I find out the recovery model? I have been
> through the docs and can't find anything there
> Transaction logs are backed up, but they are not truncated.
> SQL server profiler only includes the sample traces and
> these have never been run
> Since I am the only one with access to the database I know
> that no-one ran the DBCC SHRINKDATABASE command.

> Any ideas?

> >-----Original Message-----
> >Steave
> >What is recovery model of your database?
> >Do you perform transact log backup?
> >Have you checked SQL Server Profiler?
> >Perhaps someone ran DBCC SHRINKDATABASE manualy


> message

> >> We have a database that has several procedures run
> against
> >> it overnight, these include an optimisation, and also
> the
> >> sqlmaint equivalent of DBCC CHECKDB.

> >> The database has auto-shrink switched off, but the
> >> database file size does still sometimes shrink. Why is
> >> this?

> >> This is SQL Server v7 SP2 running on Windows NT4 SP6

> >.

 
 
 

1. Auto Shrink not shrinking

Hi,
We have a database with about 70 tables.  There is one table that does not
seem to free up its unused space.  Since the database is MSDE (2000 no
service pack, on Win2000) it is set to automatically shrink the database.
However this never seems to happen.  The table has 110,000 rows and its
reserved number is about 963 MB (I converted the KB output from
sp_SpaceUsed).  However its data is only 96 (MB), index is 27 (MB) and the
unused is a whopping 849 (MB).  I can't figure out why the unused stays so
high.  I have not tried a specific dbcc shrinkdatabase since this is at a
customer site and I have limited access to the machine.

Other possibly helpful info:
The log file truncates and shrinks just fine without any intervention.
There are background processes that always maintain a connection to the
database.  Will it not shrink if there are active connections?

Thank you in advance for any help.
    Wayne Antinore

2. Dynamically changing SourceObjectName

3. Shrinking Database Only Shrinks the Log?

4. PICK Programmer Needed in Northern New Jersey (USA) -This ad has been revised based on the comments from several users of the group.

5. Log file auto shrink

6. Problem with off-line backup with 6.1 service pack 8 database - NT 4.0

7. Database Shrink / Auto-extend

8. Dates in DataEase Byond 1999

9. shrink database files and log files

10. Connection pooling: Shrink() does not shrink

11. Shrink tables doesn't shrink enough

12. Shrink table doesn't shrink enough

13. Shrinking Veritas QIO files for Oracle backups or migrating QIO files to reg files