Empty Database Not Shrinking

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?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" <bachelorboy@.gmail.com> wrote in message
news:47DBFAFA-63E1-4592-B917-509DEC782FA7@.microsoft.com...
> 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?|||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]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uq1U4cS2HHA.3916@.TK2MSFTNGP02.phx.gbl...
> 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" <bachelorboy@.gmail.com> wrote in message
> news:47DBFAFA-63E1-4592-B917-509DEC782FA7@.microsoft.com...
>> 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?
>|||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
"Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
news:53CC20A1-2B62-4BBD-9E62-70E932D679DA@.microsoft.com...
> 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)|||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" <bachelorboy@.gmail.com> wrote in message
news:53CC20A1-2B62-4BBD-9E62-70E932D679DA@.microsoft.com...
> 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]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uq1U4cS2HHA.3916@.TK2MSFTNGP02.phx.gbl...
>> 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" <bachelorboy@.gmail.com> wrote in message
>> news:47DBFAFA-63E1-4592-B917-509DEC782FA7@.microsoft.com...
>> 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?
>>|||DBCC commands are not showing any specific messages, It says the regular,
"completed, please contact admin in any error"
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
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uvepewS2HHA.4476@.TK2MSFTNGP06.phx.gbl...
> 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
> "Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
> news:53CC20A1-2B62-4BBD-9E62-70E932D679DA@.microsoft.com...
>> 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)
>