SHRINKDATABASE - TRUNCATEONLY, NOTRUNCATE, "NOTHING"?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

anxcomp - 10 Mar 2008 21:38 GMT

Hello,

At the beginning, I read Tibor Karaszi article:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

I don't use bellow procedure on production but only on test servers.

One of task I'm using in maintenance plans is "shrink database", default sql
executes SHRINKDATABASE(N'MyDB', 10, TRUNCATEONLY).

I noticed one of my databases (maybe more...) has about 15GB unallocated
space (I checked this by sp_spaceused, column unallocated_space)

So ran this query manually and database still has 10GB unallocated space,
when I ran DBCC SHRINKDATABASE(N'MyDB', 10) unallocated space reduce to about
900MB maybe less I don't remember exactly...

I have two question:

1. I don't understand differences between

SHRINKDATABASE(N'MyDB', 10, TRUNCATEONLY) and SHRINKDATABASE(N'MyDB', 10)?
both should reduce unused space but only second (without truncateonly) works
for me

Does this mean SHRINKDATABASE has three option TRUNCATEONLY, NOTRUNCATE,
"NOTHING"?

2. If I'd like "relay" shrink database should I use two tasks shrink
database, one SHRINKDATABASE(N'MyDB', 10, NOTRUNCATE) and then
SHRINKDATABASE(N'MyDB', 10, TRUNCATEONLY)

Thanks in advance

Signature

Regards,
anxcomp

Reply to this Message

Tibor Karaszi - 10 Mar 2008 21:49 GMT

First, some facts about the options:

TRUNCATEONLY: Don't move any pages, only reduce file size. Limited by the end-most page.

NOTRUNCATE: Opposite to above. Only move pages towards beginning of the file. Do not make file
smaller.

If you don't specify any of the option, then pages will be moved towards beginning of the file *and*
file will be made smaller.

> SHRINKDATABASE(N'MyDB', 10, TRUNCATEONLY) and SHRINKDATABASE(N'MyDB', 10)?
> both should reduce unused space but only second (without truncateonly) works
> for me

Probably because there's some page sitting at a high address limiting first operation.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Hello,
>
[quoted text clipped - 29 lines]
>
> Thanks in advance

Reply to this Message

anxcomp - 10 Mar 2008 23:41 GMT

Hello,

Thanks for answer.

>If you don't specify any of the option, then pages will be moved towards >beginning of the file *and* file will be made smaller.

Does it mean sql use notruncate and truncateonly option together?

Maintenance plan doesn't provide option to set dbcc shrinkdatabase without
truncateonly and notruncate. So how shrink database from maintenance plan
TSQL script?

>Probably because there's some page sitting at a high address limiting first >operation.

Does sql has some option to check ? (in similar way I check status of
transaction log "dbcc loginfo")

Signature

Regards,
anxcomp

Reply to this Message

Tibor Karaszi - 11 Mar 2008 11:29 GMT

>>If you don't specify any of the option, then pages will be moved towards >beginning of the file
>>*and* file will be made smaller.
>
> Does it mean sql use notruncate and truncateonly option together?

No, the opposite. Neither of the options mean that pages *will* be moved as well as files *will* be
made smalled.

> Maintenance plan doesn't provide option to set dbcc shrinkdatabase without
> truncateonly and notruncate.

Maint plan executes DBCC SHRINKDB with the percentage you specify. It do not specify any of the
TRUNCATEONLY or NOTRUNCATE options.

> Does sql has some option to check ? (in similar way I check status of
> transaction log "dbcc loginfo")

I believe that DBCC SHRINKFILE will produce a report for you.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Hello,
>
[quoted text clipped - 13 lines]
> Does sql has some option to check ? (in similar way I check status of
> transaction log "dbcc loginfo")

Reply to this Message

anxcomp - 11 Mar 2008 19:51 GMT

> Maint plan executes DBCC SHRINKDB with the percentage you specify. It do not specify any of the
> TRUNCATEONLY or NOTRUNCATE options.

Bellow are two screens from ssql server Shrink Database task:

http://img443.imageshack.us/img443/5901/clip3cl0.jpg
http://img502.imageshack.us/img502/3624/clip2hp8.jpg

Is not possible to modify task in such a way that sql runs DBCC SHRINKDABASE
without TRUNCATEONLY and NOTRUNCATE.

This means I have to use some sql script and on loop execute DBCC
SHRINKDATABASE('MyDB', 10) , do you agree? :-)

Signature

Regards,
anxcomp

Reply to this Message

Tibor Karaszi - 11 Mar 2008 23:15 GMT

Ahh, OK. I don't do shrink, especially not regularly, so I haven't seen those options. I guess you'd
have to schedule a TSQL command instead. But you can incorporate it into the same plan, since you
can execute a TSQL exec task from a maint plan.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

>> Maint plan executes DBCC SHRINKDB with the percentage you specify. It do not specify any of the
>> TRUNCATEONLY or NOTRUNCATE options.
[quoted text clipped - 9 lines]
> This means I have to use some sql script and on loop execute DBCC
> SHRINKDATABASE('MyDB', 10) , do you agree? :-)

Reply to this Message

anxcomp - 12 Mar 2008 19:22 GMT

> Ahh, OK. I don't do shrink, especially not regularly, so I haven't seen those options.

I know, every people suggest read your article when somebody ask about
shrinking " Why you want to be restrictive with shrink of database files" :-)

>I guess you'd
> have to schedule a TSQL command instead. But you can incorporate it into the same plan, since you
> can execute a TSQL exec task from a maint plan.

Right, thanks. We understand each other :) Do you know or can suggest simple
script to loop every user database and execute dbcc shrinkdatabase
('actual_dbname', 10).

--
Regards
anxcomp

Reply to this Message

Tibor Karaszi - 12 Mar 2008 21:38 GMT

> Do you know or can suggest simple
> script to loop every user database and execute dbcc shrinkdatabase
> ('actual_dbname', 10).

Google for sp_MSForEachTable, and you will find examples... :-)

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

>> Ahh, OK. I don't do shrink, especially not regularly, so I haven't seen those options.
>
[quoted text clipped - 12 lines]
> Regards
> anxcomp

Reply to this Message