SQL Truncating 2

[Mkonopinski]

Ok, here is the problem.
Client created a database with Allocated Space of 5GB. THEY WILL ONLY EVER HOLD A MAXIMUM OF 1GB IN DATA!!! Don't ask.

In any case, they currently have a database filled with 600MB of Data and 4.4GB of White Space. From what I can see, the database is backing up that entire database nightly, based on 5GB of allocated space. It doesnt care that only 600MB is being used, it only knows that someone put aside 5GB and its gonna darn well back it up.

I need to get rid of that space. Their Backups are taking hours on end and Batch Files are timing out. Quite honestly, they dont need to back up 4.5GB of white space.

QUESTION:
How can I eliminate that white space? I have tried to run several SQL scripts to Truncate the DB and eliminate unused space, unfortunately it is not working.
Any help would be GREATLY appreciated.

Matt.

 

[mholbert]

I posted a very similar question except regarding the log file. ptheriault responded and recommended I run the following:

DBCC SHRINKFILE(<file_name>, TRUNCATEONLY)

Use sp_helpfile to determine the logical filename you want to shrink. I can't promise this will work on a db, but it works on a log.

His entire answer can be found under thread962-1286743

I hope this helps.

MH

 

[SQLBill]

You have to get the database down to a proper size. As long as the space is allocated, SQL Server believes the space is really needed and will back it up - it will even RESTORE the empty space.

You should be able to use the ALTER DATABASE command to decrease the database file size:

E. Modify a file
This example increases the size of one of the files added to the Test1 database in Example B.

USE master
GO
ALTER DATABASE Test1
MODIFY FILE
(NAME = test1dat3,
SIZE = 20MB)
GO

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

BTW-the issue of ALLOCATED space (for data and/or log files) is different than the space that is used up by auto-growing the file. If you are using autogrow, then the shrinkfile or shrinkdb commands will work to get the space back. But if the database was created with that space size, then you need to ALTER the database to the correct size. You can't shrink a file below it's original size.

-SQLBill

Posting advice: FAQ481-4875

 

[Mkonopinski]

Thank you all for your help. I attempted to run the script Bill supplied.

USE master
GO
ALTER DATABASE Test1
MODIFY FILE
(NAME = test1dat3,
SIZE = 20MB)
GO

I receive this Error Code:
Server: Msg 153, Level 15, State 1, Line 3
Invalid usage of the option size in the CREATE/ALTER DATABASE statement.

This is how my script appeared.

USE freedomtestsizedowngrade
GO
Alter Database freedomtestsizedowngrade
modify file ( name = freedomtestsizedowngrade
size = 1500MB )
GO

Database name = freedomtestsizedowngrade

Is this case sensitive?
What did I do wrong to receive this error message.

Thank you all for your help. It is greatly appreciated.

 

[SQLBill]

Found out I was wrong...oh well it happens...

You cannot shrink an entire database to be smaller than its original size. Therefore, if a database was created with a size of 10 megabytes (MB) and grew to 100 MB, the smallest the database could be shrunk to, assuming all the data in the database has been deleted, is 10 MB.

However, you can shrink the individual database files smaller than their initial size by using the DBCC SHRINKFILE statement. You must shrink each file individually, rather than attempting to shrink the entire database.

So find out what size the data file should be and run DBCC SHRINKFILE command to shrink it to that size.

-SQLBill

Posting advice: FAQ481-4875