Shrink VS TRUNCATE_ONLY - 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)

Shrink VS TRUNCATE_ONLY

Author

Topic

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-02 : 12:23:40

Just wanted to know what is pro's and con's of using

A.DBCC SHRINKFILE on the ldf vs

B.Running backup with TRUNCATE_ONLY AND
then doing a full BACKUP

Currently I'm doing the following:

1. CHECKPOINT
2. BACKUP LOG
3. HERE GOES A OR B

JamesH
Posting Yak Master

149 Posts

Posted - 2002-12-02 : 12:41:23

It depends on what you're trying to accomplish and how much log your database needs. ShrinkFile is used to physically shrink the database physical files whereas truncate_only is only going to truncate data within the same space. I have run into issues many times where somebody shrinks a log file and then their DTS packages run really slow while the transaction log grows. If you need the space in the Log for transactions, then I would truncate_only and then perform your full backup.

If the space used in the Log file is excessive and unused I would shrink the file and then for backups I would still do the truncate_only followed by a full backup.

Hope this helps.

JamesH.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-02 : 13:07:21

Here is my scenario,

I'm using full recovery model. I have a dts task that imports 50 MB of data every 15 minutes.

My backups occur according to the following schedule.

5:00AM FULL
5:15AM LOG
5:30AM LOG
5:45AM LOG
6:00AM DIFF
...
4:00AM DIFF
4:15AM LOG
4:30AM LOG
4:45AM LOG

The only between 5AM and 8PM AT 15 minute intervals.

Shrinking it once a day would be fine.

I currently have the following for my full backup stored proc.
A. CHECKPOINT
B. FULL BACKUP

Should I modify it to be

A. CHECKPOINT
B. SHRINKFILE LDF
C. BACKUP LOG TRUNCATE_ONLY
D. FULL BACKUP

OR THIS

A.CHECKPOINT
B.BACKUP LOG
C.SHRINKFILE
D.BACKUP LOG TRUNCATE_ONLY
E.FULL BACKUP

Edited by - ValterBorges on 12/02/2002 13:08:00

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-02 : 13:16:55

If you shrink it once a day that implies that it always grows back.
So why do it - the system obviously needs the log space so why not leave it that size.
If you need the space for something else until it grows again then you are living dangerously.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-02 : 14:50:49

Very true,

The reason I'm doing this is because if a database backup fails for some reason then the log grows very rapidly and fills the disk and I can't be here 24/7. This has only happened once due to sql agent password issues, I'm just trying to buy more time in case it happens again.

I'm thinking of putting some checks in the dts to check if the last backup occurred befored continuing. The DTS is not as important as having the system up all the time.

What I would like is to not log the DTS because it's just going to be refreshed 15 minutes later but I need the full recovery model.

Edited by - ValterBorges on 12/02/2002 15:00:58

Edited by - ValterBorges on 12/02/2002 15:08:56

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-02 : 15:13:27

What are you importing anyway? Does it need the 15 minute interval? What about changing the recovery model, do the bulk insert, then set it back? Bulk Insert recovery gives you almost everything that Full recovery does.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-02 : 15:59:39

New Time Keeping System in SQL Server imports Project Data and HR data from Access.

The goal is to have the whole system running from sql however a new time keeping system was necessary and it will take a while to port the rest of the systems to sql. The data entry for projects is still done in access. If someone enters a new project in access at 4:00 and there is someone waiting to charge to it then they will have to wait until 4:15 when the update occurs.

What are the consequences of changing the recovery model and then changing it back?
Does it have to use a bulk insert and not DTS?

Edited by - ValterBorges on 12/02/2002 16:01:03

Edited by - ValterBorges on 12/02/2002 16:03:13

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-03 : 05:56:24

Suspect you are looking at the wrong problem.

Change the procedure so that it does not log so much data - you will find that it is much faster like that too - will probably mean another database but will save a lot of problems.
Don't let the log grow to fill the disk. Put a limit on the size or don't let it grow.
Put in alerts so that someone is notifyed if these simple things go wrong.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-03 : 08:13:05

quote:
Change the procedure so that it does not log so much data - you will find that it is much faster like that too.
will probably mean another database but will save a lot of problems.

If I import the data to a database that does not log. What would I use to get the data over without logging. DTS? SP?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-03 : 08:34:10

You could redesign it so that the frequently imported data is in its own database, and the other tables that refer to it use the 3 part convention to access the other database, instead of keeping it in one DB. Normally that's not a good idea, but it solves the logging problem.

Failing that, you could also use SELECT...INTO to transfer from one DB to another, it's a minimally logged operation (unless you're using Full Recovery, and you really wouldn't need it)

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-03 : 10:07:24

Rob,

quote:

What about changing the recovery model, do the bulk insert, then set it back?

I like this idea the best what commands should I be looking for?
Will it work for DTS or just bulk insert?

Edited by - ValterBorges on 12/03/2002 10:07:56

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-03 : 10:20:56

>> What about changing the recovery model, do the bulk insert, then set it back?

Doesn't sound like a good idea but you can give it a go.

DTS allows a bulk insert task.
I believe the data pump is unlogged by default but haven't tested it.
Don't believe many people would use it if it wasn't.

Prefer bulk insert as it is simpler and stops people trying to add processing to the interface.
If you make sure the data transfer is a seperate task then you can change it at will without affecting the other processing.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-03 : 15:18:40

quote:
Rob,
quote:
What about changing the recovery model, do the bulk insert, then set it back?
I like this idea the best what commands should I be looking for?
Will it work for DTS or just bulk insert?

The thing is, right after I typed that I realized that it's NOT a good idea (as Nigel also said), because it could affect your backups. There may be transactions outside of the DTS job that might not be logged and therefore can't be restored.

Basically, you have to determine for certain whether you absolutely NEED to have the Full recovery model. If you DO need it, then DON'T change it on the fly for a DTS job. If you can live with Bulk Insert recovery, then switch the database to bulk insert recovery and use the Fast Load option on your DTS job (if you can't use a Bulk Insert task)

You can use sp_dboption to set the "select into/bulkcopy" database option, but this doesn't change the recovery model. For that, you need to use SQL-DMO objects in an ActiveX task in your DTS job, or change it manually before you start the DTS job. Books Online has more details.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-03 : 15:35:14

Thanks you all for the good suggestions.

JamesH
Posting Yak Master

149 Posts

Posted - 2002-12-04 : 09:13:51

I know that this is has been beat around quite a bit, but I didn't know if you had considered using Replication from Access to SQL Server. SQL Server does allow replication from heterogeneous data sources including access. You could pull your data directly into your database and the best part is that you could have it close to near time. Just a thought.

JamesH.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources