Inept "DBA"s rant
-
I have a client whose "DBA" is "Computer Security" guy since they do not have a need for a full time DBA. Here is his setup:
1) Run Tivoli backup on the database files without the special module that allows Tivoli to back up database files.
2) Allow the SQL Server Transaction Log to grow without ever truncating it.
3) Ignore the existence of an SQL Server Event Logs or Windows Event logs or any log for that matter.
4) Thinks backing up a Transaction Log is exactly the same as truncating it.
5) Install random software on the server until the performance stinks.
After the Transaction Log grows to over a certain size, our software starts getting unpredicatble timeout errors.
I have emailed dozens of time, and today it is coming to the endgame. I state my case to the "DBA"s boss (who fought me on this exact issue in 2006 when he was the "DBA") and here is what I get back:
"<font color="navy" face="Arial" size="2">As I see it, this is a lot of work temporary fixed to resolve a problem. If the version of the DB is at fault, then we should upgrade. This seams to be to only DB out of over a dozen that we have that requires such hand holding. WE need to get the files moving so run the script, however I want a SOW to upgrade the DB and bring it close to current version.<o:p>"</o:p></font>
He wants to upgrade the database from 2000 to 2005 because it is too much work to schedule a script to run.
-
The power of system administrators and DB admins is apparently just there to be abused. I think most of us here have had their share of experiences with half-competent DBAs or --perhaps even worse-- their managers who ignore requests until it's too late because they think they look bad to their bosses if they ask for more hardware or whatever.
Perhaps we should start a DBA of the year award and hope that the rest will take notice.
-
@TGV said:
because they think they look bad to their bosses if they ask for more hardware or whatever.
In this case all the manager needed to do was confirm that the suggested solution (start truncating the logs) was appropriate, and if so get the DBA to do so. If the Manager didn't want to do that, delegate to the DBA.
-
Their position is that it is good to have a large transaction log because then it never has to grow. He says that when the file autogrows the "whole database stops". He thinks that when he backs up the Transaction Log, it "deallocates" the file so that there is plenty of space available. Maybe I'm crazy, but does his argument have any logic behind it?
-
@Salami said:
Their position is that it is good to have a large transaction log because then it never has to grow. He says that when the file autogrows the "whole database stops". He thinks that when he backs up the Transaction Log, it "deallocates" the file so that there is plenty of space available. Maybe I'm crazy, but does his argument have any logic behind it?
Yes. In SQL Server the transaction logs are not reused until they are back up. Once the transaction log is backed up then SQL Server will start reusing the space that it has been allocated for it.
-
@tster said:
@Salami said:
Their position is that it is good to have a large transaction log because then it never has to grow. He says that when the file autogrows the "whole database stops". He thinks that when he backs up the Transaction Log, it "deallocates" the file so that there is plenty of space available. Maybe I'm crazy, but does his argument have any logic behind it?
Yes. In SQL Server the transaction logs are not reused until they are back up. Once the transaction log is backed up then SQL Server will start reusing the space that it has been allocated for it.
Is there a downside to truncating the log after you back it up and letting it auto-grow?
-
@tster said:
@Salami said:
Their position is that it is good to have a large transaction log because then it never has to grow. He says that when the file autogrows the "whole database stops". He thinks that when he backs up the Transaction Log, it "deallocates" the file so that there is plenty of space available. Maybe I'm crazy, but does his argument have any logic behind it?
Yes. In SQL Server the transaction logs are not reused until they are back up. Once the transaction log is backed up then SQL Server will start reusing the space that it has been allocated for it.
Looks like someone has to apologize to the DBA after all... :)
-
@ounos said:
@tster said:
Looks like someone has to apologize to the DBA after all... :)@Salami said:
Their position is that it is good to have a large transaction log because then it never has to grow. He says that when the file autogrows the "whole database stops". He thinks that when he backs up the Transaction Log, it "deallocates" the file so that there is plenty of space available. Maybe I'm crazy, but does his argument have any logic behind it?
Yes. In SQL Server the transaction logs are not reused until they are back up. Once the transaction log is backed up then SQL Server will start reusing the space that it has been allocated for it.
Not at all.
-
@ounos said:
@tster said:
Looks like someone has to apologize to the DBA after all... :)Yes. In SQL Server the transaction logs are not reused until they are back up. Once the transaction log is backed up then SQL Server will start reusing the space that it has been allocated for it.
Not necessarily. SQL Server truncates a log when you do a SQL Server Log backup. That Tivoli "try and get the files" thing won't.
Also, truncating the log will not make the *.ldf files any smaller. It will just re-use space inside the files.
I agree with the "create big log files" strategy as that results in files of few fragments. Provided you run a defragger that consolidates free space before you create databases.
B