DBCC SHRINKFILE - 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

SQL Server 2000 Forums

SQL Server Administration (2000)

DBCC SHRINKFILE

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2006-04-17 : 06:56:44

I had a software problem over the weekend. The transaction log is 30 Gb. I'd like to shrink it.

Recovery model is full.

I've done a full transaction log backup.

dbcc shrinkfile ('mylog', 10, TRUNCATE)

doesn't shrink anything. Returns no error?

Sam

Kristen
Test

22859 Posts

Posted - 2006-04-17 : 07:21:29

I use:

USE MyDatabase
GO

Edit: Make one final log backup

BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE (MyDatabase_log, 1)
GO

Edit: Immediately take a FULL BACKUP, for the reasons described below

Kristen

SamC
White Water Yakist

3467 Posts

Posted - 2006-04-17 : 09:00:59

Gracias!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-17 : 10:10:35

quote:
Originally posted by Kristen

I use:

USE MyDatabase
GO
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE (MyDatabase_log, 1)
GO

Kristen

I believe that using the TRUNCATE_ONLY option will invalidate your chain of transaction log backups, so you should do a full backup as soon as possible if you do this.

I usually just do a shrinkfile. Once the internal log segments roll around, the log will shrink to the size you requested. It may take a while, depending on the amount of activity on your database, but it will shirink.

CODO ERGO SUM

SamC
White Water Yakist

3467 Posts

Posted - 2006-04-17 : 10:15:07

I'm busy confirming a full backup as we write.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-17 : 13:59:58

quote:
Originally posted by Kristen

I use:

USE MyDatabase
GO
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE (MyDatabase_log, 1)
GO

Kristen

I almost never use this in a production environment. In between the time of this command and the time a full backup is taken, there is data that you will not be able to recover if you need to do a restore around this time. I only use Kristen's command when I no longer have the disk space to perform a backup tlog to a file.

Tara Kizer
aka tduggan

SamC
White Water Yakist

3467 Posts

Posted - 2006-04-17 : 15:00:44

quote:
Originally posted by tkizer

I almost never use this in a production environment. In between the time of this command and the time a full backup is taken, there is data that you will not be able to recover if you need to do a restore around this time. I only use Kristen's command when I no longer have the disk space to perform a backup tlog to a file.

So. What do you do?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-17 : 15:04:10

quote:

So. What do you do?

DBCC SHRINKFILE. Check out MVJ's response as to why it didn't work immediately.

Tara Kizer
aka tduggan

SamC
White Water Yakist

3467 Posts

Posted - 2006-04-17 : 15:10:18

Yeah. I saw that response, and read it with suspicious eyes. If I tell something to SHRINK, I want it to shrink!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-17 : 15:22:58

quote:
Originally posted by SamC

Yeah. I saw that response, and read it with suspicious eyes. If I tell something to SHRINK, I want it to shrink!

You don't trust me? That hurts my feelings!

You should read the explanation in SQL Server Books Online about the internal architecture of the transaction log files and how they are segmented.

CODO ERGO SUM

SamC
White Water Yakist

3467 Posts

Posted - 2006-04-17 : 16:38:42

quote:
Originally posted by Michael Valentine Jones

You don't trust me? That hurts my feelings!

I didn't say that. I'm sure it's supposed to work that way. I didn't have the time to wait and see.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-17 : 23:54:38

if the frequent log backups can't manage the log size, it's time to expand the disk space coz there was probably an increase in logged operations

or if it was only a 'once in a blue moon' thingie:
make one final log backup
truncate
shrink
full database backup

to ensure you still get that point in time restoration

--------------------
keeping it simple...

Norwich
Posting Yak Master

158 Posts

Posted - 2006-04-18 : 07:42:55

How many of you use:

Dbcc ShrinkDatabase(DB, TruncateOnly)

and under what circumstances?

Just to get an idea?

The revolution won't be televised!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-18 : 09:54:42

quote:
Originally posted by SamC
...I didn't have the time to wait and see.

Always a good recipe for disaster to act before you understand the implications.

CODO ERGO SUM

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-18 : 21:42:13

I prefer to shrink a file instead of both mdf and ldf at the same time

I rarely shrink a file ofcourse
I believe that things need to take their own course, in this instance, files grow

But in circumstances that there is sudden growth due to temporary permanent tables created and used as staging and there is not enough disk space to accomodate such growth, I do backup, truncate and shrink

in short, I don't use shrinkdatabase

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2006-04-19 : 00:33:33

Hehehhe ... I think we should put lots of SHRINKDB in our "Professional Version" and just disable that line of code to create our "Enterprise Version". That's be a couple of gazillion spondulix please!

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-19 : 02:08:39

it's the shrinkdatabase season (before it was backup and restore...)

topics are seasonal like everything else i guess

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2006-04-19 : 02:14:46

"I want a Shrink for my Database, like everyone else has ..."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources