February 26, 2008 at 9:37 am
Greetings people,
I'm rather new to SQL 2005 and have only had a little time to spend on it so far. But I’ve been thrown direct into the line of fire and stumbled into a small problem that you might have a solution for.
The case is that a SQL server holds a database which stores a lot of logging information from clients. The clients send more logging information than the server can handle (HD size is to small). This can of cause be solved in the application but due to a lot of reason this is not possible atm.
So I was wondering if it is possible to make a scheduled job which backup the database, delete data in a table for a certain database and then shrink the database.
For deletion of the table data it can be done through:
"USE databasename
Truncate table logn".
For now it is a manual procedure to backup the database, truncate the table, backup the database and then shrink it. The size is around 40 GB and it takes around 1 hour to make the backup. If the job can be set to overwrite the previous backup it would be supreme.
If any has a suggestion on how to make a job like that I would be grateful. Or if I can be pointed into the right direction, that would be great to.
Thanks in advance 🙂
February 26, 2008 at 11:55 am
The database maintenance wizard can take care of backups (overwrite) and database shrinking, but cannot truncate a table.
You have to create a job that does each step (Backup overwrite existing,truncate,shrink) and schedule this process to run however often you like.
Or you can create a SSIS package that does the entire process and have it run as a job.
I would choose the first option.
SSMS can create all the code for you. All you have to do is perform the task and generate the script to a new window or clipboard. Then paste the code into a job step.
February 26, 2008 at 12:30 pm
Rather don't shrink the database. If you truncate the table then the empty space in the DB can be reused by future inserts.
If you shrink the DB, it just has to grow again. also the shrink plays havok with any indexes you have.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 27, 2008 at 11:45 am
What is the hardware spec for this server. seeems 40Gb and 1 hour is too long for taking a backup.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 29, 2008 at 4:59 am
Thanks for the replay both.
I will try the first solution with a backup job and then truncate the table and reuse the blank holes.
The reason the backup has taken so long, is that it was a to an USB device which did run 1.1
This has been removed now and the backup runs alot faster.
Thanks for the suggestions and I appreciate the help 🙂
March 3, 2008 at 11:47 am
If you are on SQL Server 2005 - you can do everything you need to do in a maintenance plan. SSMS maintenance plans include a task called Execute SQL Task that you can use. Just add the task with the appropriate code in the appropriate order of the rest of the tasks (probably right after you backup the database).
I would recommend reviewing the table and instead of truncating the table - delete old data from the table based upon a date criteria. If you have a datetime column you can do something like:
DECLARE @this_morning datetime;
Set @this_morning = dateadd(day, datediff(day, 0, getdate()), 0);
DELETE FROM logn WHERE yourdatecolumn < dateadd(day, -7, @this_morning);
The above will delete any data that is older than 7 days - modify for the number of days data you want to keep.
Jeff
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply