Database Size

Question

Database Size

Anabel Sancho

Newbie

Points: 4
More actions
May 26, 2003 at 2:08 am

#159219

I have got a file 5,5 GB and I need to get some space on that server. I have passed registers to an historic databases so the new one is 60MB and it will be bigger (about half of the information in the original will go to the historical) I tried to shrink the database (using SQL Enterprise Manager). "Shrink" is also in the Maintenance Plan. Nothing changes the size of the file. Any help on what to do to get some space will be gratefull.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Levi Justus SSCommitted Points: 1923 More actions · Answer 1

Oops I could not understand your problem correctly. Is the 5.5GB file you are talking about is a database file?. If yes...check the Books Online for the command DBCC shrinkfile, shrinkdatabase and its usage..this might help you.

Jesus My Saviour

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Hi Anabel,

quote:
I have got a file 5,5 GB and I need to get some space on that server. I have passed registers to an historic databases so the new one is 60MB and it will be bigger (about half of the information in the original will go to the historical) I tried to shrink the database (using SQL Enterprise Manager). "Shrink" is also in the Maintenance Plan. Nothing changes the size of the file. Any help on what to do to get some space will be gratefull.

Did the size of the file not change or the space allocated?

What does your matintenance plan look like?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Anabel Sancho Newbie Points: 4 More actions · Answer 3

Sorry for my short explanation. I am talking about the .MDF file. I did shrink from the EM - database - (right button) shrink file, and there is a job scheduled to shrink the database for all sundays.

quote:
Oops I could not understand your problem correctly. Is the 5.5GB file you are talking about is a database file?. If yes...check the Books Online for the command DBCC shrinkfile, shrinkdatabase and its usage..this might help you.

Levi Justus SSCommitted Points: 1923 More actions · Answer 4

Hi Ana.

Try the DBCC SHRINKDATABASE

( database_name [ , target_percent ]

[ , { NOTRUNCATE | TRUNCATEONLY } ]

)

This should work..please check the Books Online for expalination about each options.

hope this will help.

Jesus My Saviour

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Hi,

I have the autoshrink option enabled for my user db's. Never had any trouble with this on SQL7. Might be something to consider for the future? How often you do backup your db and your logs? Are they delete by maintenance plan after a period of time?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Anabel Sancho Newbie Points: 4 More actions · Answer 6

Hello, autoshrink option is enabled and db and log are backed up once a week. Anyway this does not seem to very effective.

I have no idea on what you mean when you say "Are they delete by maintenance plan after a period of time?". Thank you.

quote:
Hi,
I have the autoshrink option enabled for my user db's. Never had any trouble with this on SQL7. Might be something to consider for the future? How often you do backup your db and your logs? Are they delete by maintenance plan after a period of time?
Cheers,
Frank

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Hi Anabel,

I do a full backup every night with logs backup'd every 1 hour between 7:00 and 20:00.

On the dialog where you define your maintenance plan there are two tabs named 'Complete Backup' and 'Transaction Log Backup'. On each is a checkbox 'Remove files older than'...

There you can tell SQL Server to automatically delete useless backups after some period of time. For instance, I delete the files after 2 days (They are hopefully backup'd then by our corporate solution). If you don't check thios option, the files will be there until you delete them manually.

I think you should consider to backup your logs more frequent.

See 'backing up databases, with transaction log backups' in BOL as an entry point for thoughts about a backup strategy

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Anabel Sancho Newbie Points: 4 More actions · Answer 8

Thank you very much to all of you. Yesterday the MDF file was reduced to 4.3 GB after executing DBCCSHRINK NOTRUNCATE and DBCC TRUNCATEONLY in 2 steps.

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 9

I run the following on our system when the SHRINKDATABASE doesn't work properly

For this example the Database is named DB and the MDF is named DB_Data.MDF

DBCC SHRINKFILE(DB_Data, [enter size to shrink to])

This will tell the system to shrink the physical file itself to be the size you set. This is if you have problems with the SHRINKDATABASE command

AJ Ahrens

SQL DBA

Revenue Assurance Management - AT&T

Good Hunting!

AJ Ahrens

webmaster@kritter.net