Maintenance Plans ?

Question

Post reply

Maintenance Plans ?

DC-328448

SSC Enthusiast

Points: 171
More actions
October 17, 2006 at 8:07 am

#170627

I have a database which I use to copy tables to for short periods of time. Nobody access this database but myself. The tables I copy there are pretty large and after a day or two I delete them. However after I delete them the database seems to stay at the same size.
I wanted to run a shrink of the database to get the size back but when I did the database grew larger (the database itself). I used a maintenance plan. I am not very familiar with them and assume I made some kind of mistake to make it grow rather than shrink. Is that possible? It does not to appear to be the transaction log - that is very small. What do you suggest I do to shrink this database as small as possible - keeping in mind it is basically just a short term holding ground for backup data. Thanks.

Viewing 14 posts - 1 through 13 (of 13 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Run a full db backup

run a transaction log backup

rerun a full db backup

run dbcc DBCC SHRINKDATABASE

then DBCC UPDATEUSAGE

then exec sp_helpdb 'DbName'

DC-328448 SSC Enthusiast Points: 171 More actions · Answer 2

Thanks. Dumb question - why the 2 backups? and what does then exec sp_helpdb 'DbName' do?

Also does this take a major hit on performance on my SQL server - the database isn't big at all. Maybe 5gb.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

As I understand it :

first backup creates a starting point.

log backup creates a second point, but since some new transaction can be performed between the operations, it's still not safe to clear the log.

Once you do the 2nd full backup, all the "unsafe" transactions are cleared and the log can be truncated. I'm sure there's a better explaination out there and I'd like to hear it .

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

...and sp_helpdb just gives you the info that you see in the database properties.

DC-328448 SSC Enthusiast Points: 171 More actions · Answer 5

Thanks. Since no one has access to this database but myself - then there will be no unsafe entries after the first backup - am I correct - so one backup should suffice in my case? am I understanding you correctly?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Nope... I'm just telling you what I took for me to get it working. I'm sure there are other ways but I'm in no way an expert on that matter.

debrucer1 SSCertifiable Points: 5366 More actions · Answer 7

just do the "DBCC SHRINKDATABASE ('<dbname>')" step provided below.

You don't care about backups or contents, right? when you're done you just want the space back?

This works... I just tested it to be sure.

Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 8

Do what Ninja told u to. It works. Just doing DBCC SHRINKDATABASE WILL NOT SHRINK if the DB is set to full recovery mode and Backup and Xaction backup are not done.

debrucer1 SSCertifiable Points: 5366 More actions · Answer 9

Like I said, "I tested it". It worked just fine. My database was (is) set to full recovery mode and has NEVER been backed up. Must be exceptions to the rule. I wouldn't bother responding to your post if it weren't for the fact that I wasn't speaking from memory; but has specifically tested it before answering.

Hopefully by now the original poster has figured out what to do

Cheers My Friend!

Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

The shrink database can work if there is free space in the files. But in my last case I had 15 GB of data in the log for a 250 MB database. So even if I shrunk the log file I could only recover a few 100 mbs. But by doing the procedure I jut described I was able to shrink it back down to 500 MB (which is the minimum required for all the days operation and maintenance).

debrucer1 SSCertifiable Points: 5366 More actions · Answer 11

Good answer. thank you.

My mother used to tell a story, not sure if it was true, or just a good story.... when her mother cooked a ham, the last thing she did before putting it in the pot was to cut the butt off. Now, my mother asked one day, "why do you do that?", and the answer was "I don't know, my mother taught me to do it." So when Grandma was around she was asked, and her mother had taught her... and when Great-Grandma was around, she was asked, and her answer? _______

"That's what I had to do to fit the ham in the pan."

Don't you just hate to do something without knowing why? I do.

Thanks again.

David

Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982

DC-328448 SSC Enthusiast Points: 171 More actions · Answer 12

Thanks for the assistance. last week I ran your steps - well - these steps

run dbcc DBCC SHRINKDATABASE

then DBCC UPDATEUSAGE

then exec sp_helpdb 'DbName'

and all was well - I cleared up so much space!!!!!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

HTH, I,'m sure you're not the last one to come across this post in need of the same thing .