SQL Server - Empty Database Not Shrinking

Asked By Bachelor Boy on 07-Aug-07 03:05 PM
SQL Server 2K database with size 117GB. I have dropped the all the tables.
So No user tables are there in the database.

Then tried for shrinking using dbcc shrinkfile and dbcc shrinkdatabase. But
its not reducing the size of the database.

When I take a backup, the backup size is coming close to 200MB only. Log
file is not big, its close to 100 MB in size.

I only have dbo permissions on this database, not sysadmin and I am not sure
about the initial size of the database.

How can I shrink this database?




Aaron Bertrand [SQL Server MVP] replied on 07-Aug-07 03:15 PM
What do the following yield?

USE [your_database];
GO

SELECT name,size FROM sysfiles;

SELECT TOP 10
table_or_view = OBJECT_NAME(id), rows
FROM sysindexes
WHERE indid IN (0,1)
AND OBJECTPROPERTY(id, 'IsMsShipped') = 0
ORDER BY rows DESC;

--
Aaron Bertrand
SQL Server MVP
Bachelor Boy replied on 07-Aug-07 03:38 PM
Here is the query results

name             size
---------------- --------
SNRDEV_Data      14712960
SNRDEV_Log       128000

(2 row(s) affected)

table_or_view     rows
----------------- -----------
dtproperties      0

(1 row(s) affected)
Aaron Bertrand [SQL Server MVP] replied on 07-Aug-07 03:50 PM
Ok, so maybe some system table is taking up space?

SELECT TOP 10
table_or_view = OBJECT_NAME(id), rows
FROM sysindexes
WHERE indid IN (0,1)
AND OBJECTPROPERTY(id, 'IsMsShipped') = 1
ORDER BY rows DESC;

You said basically that shrinking doesn't work.  But what *exactly* is the
result of

DBCC SHRINKFILER(SNRDEV_Data, 1)

?

Of course, final silly question, you are running these queries from SNRDEV,
right?  And that is the database you are trying to shrink?

--
Aaron Bertrand
SQL Server MVP
Aaron Bertrand [SQL Server MVP] replied on 07-Aug-07 03:54 PM
I don't think you will be able to shrink the data file beyond its minimum
configured size (see ALTER DATABASE).

Why don't you just drop the database and re-create it at a more appropriate
size?

--
Aaron Bertrand
SQL Server MVP
Bachelor Boy replied on 07-Aug-07 05:21 PM
DBCC commands are not showing any specific messages, It says the regular,

I am running the quries from the SNRDEV database.

sysdepends is the table with max rows, that's 283 rows. rest of the tables
are having less than 100 rows