March 2, 2006 at 2:09 pm
I just restored a SQL 2K database to my new SQL 2005 db server in our test environment so I could start learning the new stuff... I set up a Maint Plan using the wizard and have let it run for a few days. My db is being backed up and there is a Shrink Database task being executed, but the transaction log is just growing and growing. It seems to me that in SQL 2k the trans log also was shrunk.
Any pointers, suggestions?
Thanks!
March 3, 2006 at 3:45 am
Hi margo
Yeah, it's really not that intuitive. Only suggestion I have is to attach a "Execute T-SQL Statement Task" with the following: DBCC SHRINKFILE ([TransactionLogName], TRUNCATEONLY)
If you backed-up the db by right clicking on the database from Management Studio and the recovery mode was full or bulk logged, you could select in Options to truncate the log. But then, you can't schedule that...
Hope this helps.
Max
March 6, 2006 at 12:42 am
One DBA of ours told me recently that even if you back up and shrink the transaction log, the file on disk never gets smaller. The server only resets the file pointer and reuses the allocated file space.
March 6, 2006 at 9:03 am
Schedule *transaction log* backups and you'll be fine.
March 8, 2006 at 8:18 pm
I use the following script, and in combination with transaction log backups and full backups, and repeat executions of this, the database files do shrink:
Use myDB
Checkpoint
DBCC ShrinkDatabase (myDB, 10)
Backup Log myDB with Truncate_Only
Go
HTH,
Mandeep Binning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply