Decrease Size of Database - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Decrease Size of Database

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-01-05 : 07:11:29

Hi,

I have a database in SQL server 2005 called ODSData5 and the files are as follows:

ODSData5.mdf = 50.3 GB
ODSData5_log.ldf = 2MB

This was the original size of the database, then I deleted 27 columns from table called 'opat' (this table is built up with 118 M records) from the database. At this level, I expected the size of the mdf file to reduce drastically. However, the size of the mdf file never changed. Therefore, I ran

DBCC Shrinkdatabase(ODSData5).. It never helped either, though, it reduced the size of log file to 2 MB.

Now, I run these 2 runs and please see the results below each run;

Use ODSData5
Go
Exec sp_spaceused N'dbo.o_pat'
Go

Results

name: opat
rows: 118917656
reserved: 52817344 KB
data: 42542352 KB
indexsize: 10268288 KB
Unused: 6704 KB

Then I run;

Use ODSData5
Go
Exec sp_spaceused
Go

Results
database_name: ODSData5
database_size: 51584.19 MB
unallocated space: 0.88 MB
reserved: 52819264 KB
data: 42543464 KB
unused: 6888 KB

Please could anyone advice me how I can reduce the size of the mdf file. Deleting the 27 columns seemed not to be helping..

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-05 : 13:58:27

The size of the database file does not reduce unless you run DBCC SHRINKFILE on it. When you delete columns or data, what happens instead of reducing the file size is that lots more space is available inside the file.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

kishore_pen
Starting Member

49 Posts

Posted - 2010-01-06 : 04:36:43

use DBCC UPDATEUSAGE (<DBName>), for more info see SQL BOL.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-06 : 10:00:14

You do not need to use DBCC UPDATEUSAGE on SQL Server 2005 unless the database has been upgraded from a previous version such as 2000.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

behrman
Yak Posting Veteran

76 Posts

Posted - 2010-01-06 : 12:01:22

Most likely your have large transaction log, you need to shrink the transaction log file and change to simple recovery model
See
http://support.microsoft.com/kb/873235/en-us

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources