Free up disk space 1

[barbola]

Our databases have grown alot in the last year and there is no more room to add cards, so we need a new controller card (this is what I am told). Apparently this costs alot of money, which is why I think it is being delayed.

Currently we only have enough space to keep 3 days of backups. This is unacceptable as far as I am concerned as well as company policy (5 days is required) but nobody seems to be making it a priority.

We run daily database backups (kept 5 days) and hourly transaction logs (kept 3 days). We run weekly update stats and weekly data integrity check. These are our four maintenance plans.

One of my ideas was to move some 10 or so old databases that are no longer used to CD. But my concern about this is, when we upgrade to the new Accounting system (Great Plains) then we need to find a way to upgrade these old db's in case we need to access them, and I'm not 100% sure how to do this without paying hundreds of dollars for advice from our vendor. This is why I think we need to upgrade the server, because the db's we have are only getting larger and larger anyways.

Are there any utilities or SQL statements I can run in the meantime to free up some space? I ran dbcc shrinkdatabase on one of the old db's as a test. It decreased the db file from 220 to 166, but the log file increased from 78 to 122 MB.

Any other suggestions are appreciated.

 

[SQLBill]

Do you have a test bed?

Install SQL Server on the test bed. Then, tell your backup personnel that you need to run a test. Since you haven't done any restores, you need to test restoring the databases from backup. Then do it with them. Either it works or it doesn't. If it doesn't explain to them what needs done to properly backup SQL Server so it can be restored.

Or..........type up a memorandum addressed to the boss and cc the backup personnel. State that you have concerns about how the backups are being done and that restoring from the backups has never been tested. Politely state how SQL Server should be properly backed up and compare that to how it is being backed up. Lastly, state that since a test restore has never been done and that, in your opinion as the DBA, the databases are not being properly backed up, you cannot guarantee that they are restorable from the backup media.

-SQLBill

Posting advice: FAQ481-4875

 

[pgaliardo]

Barbola,

If they are using Veritas, then ask if they are using the SQL Agent to correctly backup.

The alternative as ECAR points out is to run native SQL backups and have Veritas backup the .bak and .trn files. This is way I currently do it, although I would rather do it with the SQL Agent, but that's another issue with Veritas not working correctly on a SQl 2005 cluster.

Once they are backed up to tape, you should be safe enough to clear the backup files off of the hard drive

 

[barbola]

SQL Bill, No we do not have a test environment...and I have asked and asked but told it's too much $$$.

There are SQL backups being done properly, I make sure of this. Then they run a complete network backup of all the files on the SQL server including mdf, ldf, bak, trn but supposedly only the files that changed (I'd still like to know how they know this?)

As far as writing a memo, ya I could just pull the ones I've written every year for the last 5 years because this is a recurring problem.