Quick question on DBCC SHRINKFILE/SHRINKDATABASE

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 Administration (2000)

Quick question on DBCC SHRINKFILE/SHRINKDATABASE

Author

Topic

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2007-01-03 : 18:38:05

Hello everyone.
Have a question in regards to the DBCC SHRINKFILE/SHRINKDATABASE command.

First, are those two the same, or are they different?

I have been doing a lot of reading, testing etc. lately with some databases here at work (i've been given the task to become a DBA here for our company)

Anyway, the more I have learned, the more I know.
Let me give you a quick run down on what I am doing.

I have a copy of our production database on a test box.
The size of the DB is around 17gigs.
however, the transaction log files are around 36gigs (long story, but tlogs were never backed up...they are now though.)

So, looking at things, I have about 35gigs of space I can recover, which I am hoping to do. However, I just want to make sure I get this right.

Reading up, I see I should do something like:

DBCC SHRINKFILE(<mydatabse_LOG>)

Do I need to specificy a size for the log size I want?
This box is purely testing and does not get a lot of traffic on it. I have also changed the recovery mode tosimple. This box also gets backed up nightly.

I was just curious to the proper way to shrink the log file so I could recover space.

I appreciate it.

TCG

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-03 : 18:42:16

will shrink to original size at create time.

first backup log with truncate_only if trying to recover most space possible. what recovery mode is database in?

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2007-01-03 : 18:47:41

quote:
Originally posted by russell

will shrink to original size at create time.

first backup log with truncate_only if trying to recover most space possible. what recovery mode is database in?

Currently, in SIMPLE mode because it is just a test box.

Ok...so just a simple:

BACKUP LOG <mydatabase> WITH TRUNCATE_ONLY

Then run the DBCC SHRINKDATABSE

That do it?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-03 : 20:45:40

[code]
use dbName
go

backup log dbName with truncate_only
go
dbcc shrinkfile(dbName_log, 2)[/code]

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2007-01-04 : 12:46:17

quote:

Originally posted by russell


use dbName
go

backup log dbName with truncate_only
go
dbcc shrinkfile(dbName_log, 2)

thanks. worked like a charm!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources