I created a database with a log file o 100mg. All of a sudden i find the
size decreasing to 9mg!!!!
My understanding is that you can increase the size but never decrease it.
So, does the SQL engine decrease the file size on its on!?

I backup the transaction log once daily.

Please help.

Thank you

mike2002am@yahoo.com

[posted and mailed, please reply in news]

mike (mike2002am@yahoo.com) writes:
[quoted text, click to view]

If you have set the AUTO_SHRINK option for the database, SQL Server
will indeed shrink it on its own. A database can also be shrunk by
the commamnds DBCC SHRINKFILE or DBCC SHRINKDATABASE.

You can check whether the database in auto-shrink mode with

SELECT databasepropertyex('dbname', 'IsAutoShrink')

You can also see this in Enterprise Manager, on the Options tab
on the Property sheet for the database.

AUTO_SHRINK is rarely a useful option, and can be harmful, as it could
cause performance issues with frequent shrinks and auto-grows for
a database that sees periodic deletions and inserts.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

[quoted text, click to view]

Even so, I thought that it couldn't normally be shrunk smaller than it's
original size?

Or perhaps it's the size of the model DB?

I'm drawing a blank here.

Hmm, hear BOL seems to indicate it's the DB itself can't be made smaller
than the model.

I'll shut up now.

Other than to say, Erland is most likely right as to the cause.

Greg D. Moore (Strider) (mooregr_deleteth1s@greenms.com) writes:
[quoted text, click to view]

If you specify a target size, it can. And note that once you've specified a
target size, that size is now the "original size" according to Books Online.

Here are two repros that I just posted in another place. The first shows
that with a target size you can shrink below the original size. The second
shows that SQL Server indeed respects the original size. I have not
studied how auto-shrink works.

create database nisse_db on
(name = 'data', filename = 'f:\mssql\nisse_db.mdf', size = 50 MB)
log on (name = 'log', filename = 'f:\mssql\nisse_db.ldf', size = 5 MB)
go
use nisse_db
go
exec sp_helpdb nisse_db
go
dbcc shrinkdatabase(nisse_db)
go
exec sp_helpdb nisse_db -- no change
go
dbcc shrinkfile('data')
/* DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ------ ----------- ----------- ----------- --------------
28 1 6400 6400 88 88 */
go
exec sp_helpdb nisse_db -- no change
go
dbcc shrinkfile('data', TRUNCATEONLY) -- Same output as above
go
exec sp_helpdb nisse_db -- Still the same.
go
dbcc shrinkfile('data', 10)
/* DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ------ ----------- ----------- ----------- --------------
28 1 1280 1280 88 88 */

go
exec sp_helpdb nisse_db -- Database have now shrunk
go
use master
go
drop database nisse_db
go



create database nisse_db on
(name = 'data', filename = 'f:\mssql\nisse_db.mdf', size = 50 MB)
log on (name = 'log', filename = 'f:\mssql\nisse_db.ldf', size = 5 MB)
go
use nisse_db
go
select a.* INTO kluns
from Northwind..Orders a
cross join Northwind..Orders b
go
exec sp_helpdb nisse_db
go
dbcc shrinkdatabase(nisse_db)
/* DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ------ ----------- ----------- ----------- --------------
28 1 16576 6400 16568 16568
28 2 640 640 640 640 */
-- Note MinimumSize
go
exec sp_helpdb nisse_db -- Data file unchanged, log has shrunk
go
drop table kluns
go
dbcc shrinkfile('data')
/* DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ------ ----------- ----------- ----------- --------------
28 1 6400 6400 88 88 */
go
exec sp_helpdb nisse_db -- Have now shrunk to 50 MB.
go
dbcc shrinkfile('data', 10)
go
exec sp_helpdb nisse_db -- And have now shrunk to 10 MB.
go
use master
go
drop database nisse_db
go




--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at