468,738 Members | 1,697 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,738 developers. It's quick & easy.

reduce trans log size; help needed

Hello,

I am hoping you can help me with the following problem; I need to process
the following steps every couple of hours in order to keep our Sql 2000
database a small as possible (the transaction log is 5x bigger than the db).

1.back-up the entire database
2.truncate the log
3.shrink the log
4.back-up once again.

As you may have determined, I am relatively new to managing a sql server
database and while I have found multiple articles online about the topics I
need to accomplish, I cannot find any actual examples that explain where I
input the coded used to accomplish the above-mentioned steps. I do
understand the theory behind the steps I just do not know how to accomplish
them!

If you know of a well-documented tutorial (Aside from Books Online (F1)),
please point me in the right direction.

Regards.
Jul 20 '05 #1
1 5064
First, create a backup device with whatever name you want. ( I assume you
know how to do this).
Might have to check the syntax on each of these but here goes

1. backup database [your_database_name] to [your_backup_device] with stats,
init
2. backup tran [your_database_name] to [your_backup_device]
3. dbcc shrinkfile 2,truncateonly
4. backup database [your_database_name] to [your_backup_device]

Step 3 above assumes that file 2 is the logfile for your database. You
might have to check this by issuing a select * from sysfiles (in query
analyzer) to make sure that file 2 is the log file.

Hope this helps.

Oscar....

1.back-up the entire database
2.truncate the log
3.shrink the log
4.back-up once again.

As you may have determined, I am relatively new to managing a sql server
database and while I have found multiple articles online about the topics I need to accomplish, I cannot find any actual examples that explain where I
input the coded used to accomplish the above-mentioned steps. I do
understand the theory behind the steps I just do not know how to accomplish them!

Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

226 posts views Thread by Stephen C. Waterbury | last post: by
3 posts views Thread by Ron_Adam | last post: by
6 posts views Thread by Sean C. | last post: by
2 posts views Thread by Sue | last post: by
11 posts views Thread by Parrot | last post: by
9 posts views Thread by Susan Bricker | last post: by
reply views Thread by support1 | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.