Getting Error with "SHRINKDATABASE TRUNCATEONLY"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Getting Error with "SHRINKDATABASE TRUNCATEONLY"

Author

Topic

rpieszak
Starting Member

11 Posts

Posted - 2003-10-09 : 10:05:09

I'm trying to release the allocated but unused space from the db back into the system. I'm running this command:

DBCC SHRINKDATABASE (myDB, 500) TRUNCATEONLY

And I'm getting this error message:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'TRUNCATEONLY'.

Is it a version issue? I'm running on SQL 7.

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-09 : 11:03:09

I think your missing a comma...



DBCC SHRINKDATABASE (myDB, 500), TRUNCATEONLY

But I think youre target percentage is wrong...

BOL:

quote:

target_percent

Is the desired percentage of free space left in the database file after the database has been shrunk.

Brett

8-)

rpieszak
Starting Member

11 Posts

Posted - 2003-10-09 : 11:17:54

Hmm, I thought it was looking for a MB size, that's good to know.
I tried it with the comma:

DBCC SHRINKDATABASE (myDB, 10), TRUNCATEONLY

And now:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.

Important to note: I'm running this in query analyzer, and usually keywords are highlighted in blue (ie: DBCC & SHRINKDATABASE are blue), but TRUNCATEONLY is not blue. Does it not recognize it?

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-09 : 11:29:17

OK...I'm giving it a try..

but the percentage is ignored with that option..sure you want it?

BOL:

quote:

TRUNCATEONLY

Causes any unused space in the data files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. target_percent is ignored when TRUNCATEONLY is used.

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-09 : 11:33:21

OK, got it...

It's instead of the %


DBCC SHRINKDATABASE (Northwind, TRUNCATEONLY)

It remove data from the end of the data file...with a % it moves data around and seems to clean things up more...

maybe some of the pro out here can provide best case reasons on what to do/ not to do...

Brett

8-)

rpieszak
Starting Member

11 Posts

Posted - 2003-10-09 : 11:42:13

I've already shrunk the database, and it only uses 300MB, but it has 2.6GB allocated for use. I need to free up 2GB of the allocated space for system use. I tried the syntax you said, and it completed without error, but it still didn't release that allocated space. Any ideas on how to do that? Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources