shrinkfile issue

[collierd]

Hello

I am using SQL Server 2000

Basically, every night I run a whole host of packages that update a database

1. During the course of this the transaction log can grow to around 25 Gb

Any tips for quickly clearing this as the routine progresses

2. I use the following code to clear the transaction log and backup the database:

BACKUP LOG [MDW] TO [MDW Trans Backup] WITH  INIT ,  NOUNLOAD ,  NAME = N'MDW Trans backup',  SKIP ,  STATS = 10,  NOFORMAT

BACKUP DATABASE [MDW] TO [MDW Backup] WITH  INIT ,  NOUNLOAD ,  NAME = N'MDW backup',  SKIP ,  STATS = 10,  NOFORMAT

dbcc shrinkfile (MDW_3_Log, 100)

dbcc shrinkfile (MDW_Data)

Problem is, it doesn't always clear the transaction log

If I run this bit of code just after the first dbcc shrinkfile (MDW_3_Log, 100) it seems to work:

BACKUP DATABASE [MDW] TO [MDW Backup] WITH  INIT ,  NOUNLOAD ,  NAME = N'MDW backup',  SKIP ,  STATS = 10,  NOFORMAT

dbcc shrinkfile (MDW_3_Log, 100)

Is that normal as the backup takes an extra 25 minutes as a result

Any help with this would be appreciated

Thanks

damian.

 

[DrSql]

You might need to think about running
dbcc shrinkfile (MDW_3_Log, 100) after certain number of updates. Example, if you have 10 million records total to update, then you should run DBCC every 500,000. This way your file will stay around same size.


Dr. Sql
goEdeveloper@yahoo.com
Good Luck.

 

[SQLBill]

Other options:

1. Change Recovery Mode to SIMPLE prior to doing the updates and then return it to FULL after the updates.

2. Do the updates, then truncate the log (BACKUP LOG dbname WITH TRUNCATE_ONLY), then do a FULL backup.

-SQLBill

Posting advice: FAQ481-4875

 

[collierd]

Considered both of these
What would happen if the server crashed part way through and the recovery mode was simple
Would it cause corruption - I suppose I could revert back to the previous nights backup?

Again what happens if the database/server crashes part way through truncate/backup
Would this cause corruption?

Am I right in thinking, with option 1, you should change to simple (not sure of code), run everything, change back to full (again not sure of code), then backup as normal

DrSQL with what you are suggesting you still need to do a backup as it will not shrink data that hasn't been previously backed up - I think??