May 13, 2010 at 2:56 pm
hi there
I'm new to this and I've spent quite some time online trying to figure out a solution for this.
We're doing manual cleanup on our DBs regularly.
We first manually delete all rows in a DB besides the last 100, and then we run a script that cleans up the log file.
We've been deleting the entries manually through a SQL command in the table, and then using this script to clean up the transaction logs.
backup log [database_name] with truncate_only
go
DBCC SHRINKDATABASE ([database_name], 10, TRUNCATEONLY)
go
Is there a script that we could schedule to run for every database on the system to:
- Delete all rows but the last 100 entered in a specific table
- Cleanup the transaction logs after this
Even if it's in 2 steps. I can't seem to find a good solution for this online.
Thank you much - any ideas would help.
the SQL version is 8.0.2036 (no sqlcmd available)
P.S.
- I also found this online (but can't get it to work - no sqlcmd)
sqlcmd -s <server_namel> -Q "EXEC sp_MSForEachDB @Command1 = N'DBCC
SHRINKDATABASE (?, 10)', @replacechar = '?'" -c go
May 13, 2010 at 8:39 pm
You really should not be shrinking your database at all - because, as you are aware - it just grows again. The additional growth is expensive and will cause your queries to wait for the growth - and, I would be the growth settings are too low anyways.
As for the log - it sounds like you are running in full recovery model and you are not backing up the transaction log on a regular basis (at least every hour).
Review the article I link to in my signature about managing transaction logs. That will show how to manage the size of the transaction log without having to shrink all the time.
BTW - using the truncate_only option to backup the log breaks the log chain. That command is deprecated and will no longer work in SQL Server 2008. If you are backing up the transaction log - using that command will prevent you from restoring to a point in time if your current backup is corrupt.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply