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.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!
