Database maintenance query

[OldSkoolSkater]

I have a SQL 7 server to look after which I have created a backup strategy for a 12Gb database:
I run full backup jobs for the database twice weekly, with differential backups in between. I also use a maintenance plan to backup transaction logs hourly. This is all backed up to a network disk.
All has gone well until I noticed that I am rapidly running out of disk space, so I am looking to the maintenance plans to do the full backups and also the shrink the database.
My worry is how the server will be impacted when this is done as it already takes 3-4 hours to do a full backup?
Also, does anybody have a clue as to how much the DB will be shrunk down to, or is it reliant upon what is in my DB?
Can the maintenance plans only be used to do full and transaction log backups (I can't see an option for diff)?

Cheers

Steve

 

[aalmeida]

First where are you running out of Disk space? at the disks where the database files reside or at the Netwrok Drive where the back up are stored? AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[OldSkoolSkater]

oops! Sorry about that..

I am running low on the SQL server local disk where the DB files are - I only have 400Mb left.
My backup files are stored on a network SSA unit with 70Gb free - much more comfortable!

Steve

 

[aalmeida]

I would sugest you Two Steps, to apply either one it will depend on the nature of the data:

1) Create a purge criteria to reduce the database Gowth rate (amount of data in the DB)
2) Create a Growth Rate Criteria as a base to Monitor and Plan HD Space so you would not run out of space for your databases.

Keep in mind that Too much Back ups generate some performance issues that may not be acceptable if you need data and your db is high on transaction, Use log back up to shrink total db size also cause I/O hits that reduce over all performance.
Growing the DB Data and Log Files too frequently cause to reduce over all performance on database servers so you do not what to do that. AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[OldSkoolSkater]

I should have explained that I have inherited the SQL server only when it started going wrong!
This means that I have very limited knowledge of SQL and have only used enterprise manager. Transact-SQL and SQL-DMO are definitely out unless I really have to go down that route!
As a result, I don't understand the criteria that you have suggested creating.
Could you elaborate in stupid layman's terms?

Steve

 

[aalmeida]

It is just semantics:

1st) The Purge Criteria requires you to know the relevance of keeping data into the database, per say you are required to keep all record with les than Five years there fore you purge the database from all records that are 6 years or older.
It does require strong T-SQL, and strong Application requirments and functionalitys Knowledge.

2nd) The DB Growth it does requires monitoring and and Knowlodge of some statistica history of the database base so you can plan to by HD before you run out of space, you would create a log and keep a running record of the size of the databse so you would be able to estimate the Growth in Mb.

AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"