Hot answers tagged dba
21
Sites
Most of these are multiple purpose. For instance, articles + forums + blogs. This is by no means exhaustive. It's a starting point as you'll find a lot of other blogs and resources from these sites.
SQL Server Central
SQL Blog
SQLServerPedia
SQL Skills
SQL Team
SQL Server Performance
IT Knowledge Exchange
Books
This really depends on your role. ...
15
In the SQLOS a scheduler is created for each logical processor that SQL Server sees. With hyperthreading enabled this equates to double the schedulers. One of the purposes for the SQLOS is to minimize and prevent context switching from occuring which is why only one scheduler is created for each logical processor. Once the SQLOS creates the schedulers, ...
12
The free and open-source SchemaCrawler tool will do what you need. SchemaCrawler outputs details of your schema (tables, views, procedures, and more) in a diff-able plain-text format (text, CSV, or XHTML). SchemaCrawler can also output data (including CLOBs and BLOBs)
in the same plain-text formats. You can use a standard diff program to diff the current ...
11
In 2000, log backups could not run at the same time as diff or full backups (because a log backup will clear some transaction log and full/diffs must backup some log to allow the restored copy of the database to be transactionally-consistent).
In 2005 this restriction was lifted. They can occur at the same time but a log backup concurrent with a full or ...
11
I am an Oracle DBA. Your new DBA is acting like alot of Oracle DBAs and over engineering.
NO oracle does NOT need 38 LUNs. I have spread data files out on large numbers of lun's but these are on systems that are VERY active and VERY large. LUNs don't necessary map to new RAID groups right? So having files on seperate luns don't necessary spread anything ...
8
The answer is no, for whatever backup software is being used.
A backup is a physical operation, not a logical operation. It reads all extents containing allocated pages (i.e. even though only a single page from an 8-page extent is allocated, it will backup the entire 64K extent), and it does it in physical order.
A restore is a physical operation, not a ...
8
I think this article gives an excellent overview of defragmentation of SAN drives
http://www.las-solanas.com/storage_virtualization/san_volume_defragmentation.php
Basic points is that defragmenting is not recommended on SAN storage because it is difficult to correlate the physical location of blocks on the disk when the location has been virtualized by the ...
7
Multiple parts to this question and answer:
Physical file fragmentation isn't really relevant for Enterprise SAN storage, as Kevin already pointed out - so nothing to add there. It does really come down to the I/O subsystem and how likely you are to be able to make the drives go from more-random I/Os when performing a scan to more sequential I/Os when ...
7
Leave the SID alone. NewSID has been retired because Mark Russinovich did some digging and found that the whole "duplicate SIDs == bad!" line we've all had drummed into our skulls over the past decade or so is just a load of nonsense.
See Mark's latest blog entry: The Machine SID Duplication Myth.
6
I would definitely recommend against changing the SID, until after you read: Changing Machine SID With NewSID Breaks SQL Server (And How To Fix It)
Apparently, some of the cloned servers
end up with the same SID at the OS
level.
I would hazard to guess ALL of your cloned systems have the same SID. GhostWalk can regenerate SIDs for you. Using ...
6
If you have a replication / high availability solution then using it to avoid downtime is the obvious choice, have one server upgraded while the other is working and then switch and upgrade the next one.
If you don't have such a structure in place, you can do a mini replication setup in the same server, where you have two copies of each database and upgrade ...
6
A native SQL backup is just a page-by-page dump of the backup files, so the answer there is "no". A Quest lightspeed backup likely uses some sort of compression compression algorithm, but it still won't "rebuild" the data files or indexes, which would take a horrendously large amount of time on a big database.
6
What do you need to know about databases to be a DBA? Everything (or at least most things, and where to look for answers to everything else).
Seriously though, there is always a market for good DBAs. If you want to become a good DBA the best advice I can give you is:
If your "databases class" didn't get into the theory behind the relational model & ...
6
When your transaction log grows too large, the first thing to check if your backups are working. Depending on recovery mode, the transaction log keeps growing until you back it up.
If you actually don't want backups, and your recovery mode is not simple, you can change it to simple with:
alter database YourDb set recovery simple with no_wait
dbcc ...
5
Perhaps rather than number of apps supported, consider SLAs (whether formal or informal). Consider the costs of inadequate support - whether due to workload or something going offline while the one DBA is on holiday or sick or whatever.
When you have an inadequate amount of people to meet your SLAs / targets / user expectations / whatever other metric, then ...
5
The data files are compatible with percona server. You just need to start percona server mysqld and everything will work.
Only when you enable some features (like extra rollback segments) on percona server, you might not be able to go back to mysql. but you rarely need to change this parameter.
5
For general schema navigation DBVisualizer is quite nice.
For performance information there are hardly any complete tools like MySQL Enterprise Monitor; I'd suggest to use pgFouine (already suggested) and any tool that can graph performance data (like Munin for example), collect the relevant information from PostgreSQL (pg_stat_ views/system tables) and the ...
5
Books:
Optimising Oracle Performance - Cary Millsap
Pretty much everything by Thomas Kyte (http://asktom.oracle.com)
Cost-Based Oracle Fundamentals (Jonathan Lewis)
Web sites (if you are dealing with Oracle on Linux):
http://www.puschitz.com - installing oracle on Linux, Linux performance tuning tips
http://www.oaktable.net
...and the oracle websites, ...
5
Database backups should normally be serialized.
The large log backup is probably happening because of database maintenance operations which happen between the last log backup and the full backup. Reducing the interval between log backups may help with this.
You can run some T/SQL to check the sysprocesses table (or DMV if SQL 2005+) to see if a backup is ...
4
Be ready to explain WHY you do something and listen to WHY they do something.
It really helps is both parties can back up a few steps and concentrate less on how they currently (or expect to) do something and focus on what the overall purpose is.
If the DBA is focusing on getting program X to talk to the database when the sysadmin doesn't allow program x ...
4
You could use TOAD is a great generic tool for Oracle development. There is a trial version for testing. From the features list:
Data Compare and Sync wizard
Synchronize data across database
platforms
Another product you could test is OraPowerTools:
OraPowerTools is a collection of
native oracle database utilities. This
...
4
If you're using SQL Server then you can always do index fragmentation removal online from SQL Server 2000 onwards. The command DBCC INDEXDEFRAG always performs an online reorganization. I wrote it specifically as an online alternative to DBCC DBREINDEX.
In SQL Server 2005 onwards, the ALTER INDEX ... REORGANIZE command replaces DBCC INDEXDEFRAG and is also ...
4
Depends.
Is this a sudden, out-of-the-blue performance is dead slow issue, or it is a long term case of general poor performance?
If it's the former, I'll start with the execution-related DMVs (sys.dm_exec_requests). Is there any blocking? Is there a query that's running excessively long or using massive resources. Basically I'll be looking for something ...
4
I think it's helpful to remember that when doing your job, sometimes the way you do things may not make the most sense to outside observers, but you do them that way for a reason. The same principle (probably) applies to that difficult DBA. Showing them the same respect you'd like to be treated with can go a long way.
4
No need to restore to a scratch server, you can restore it to something like you say (MSDB_old) and do a query to get your job back:
USE msdb_old
SELECT * FROM sysjobs
JOIN sysjobsteps ON sysjobs.job_id=sysjobsteps.job_id
WHERE sysjobs.NAME='My Lost Job'
ORDER BY sysjobsteps.step_id
You'll have to restore
the entry in sysjobs
each entry in sysjobsteps ...
4
It is the responsibility of the application developers to keep track of the credentials they need to access the database. Reason being that the application NEEDS that password in order to do it's job, but the database will keep on ticking regardless.
If the developers lose their password, it's the job of the DBA to reset it and provide a new one, but they ...
4
Have a look at mysqldump and it's --no-data option to get a copy of the schema. Once you have that you will have to make a script to source in mysql
e.g.
mysqldump --no-data -u user -p database >database-schema.sql
will generate a copy of the schema for database database.
If you had a script like below in a file called for example makedbs.sql
...
4
Correct.
It's best to put this in the general/global (top) part of your my.cnf file.
There are two things you need to do : (1) enable it, and (2) define the logfile destination.
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
Hope this helps.
3
The major databases all have free versions available so you can explore and learn the differences yourself. The basic structure of inserts/updates/deletes is the same, but the specifics are different and the more advanced stuff you want to do, the more different they are, it seems.
Things you wont really want to learn, but will really make you good if you ...
3
Here you go. I'd say that a Junior DBA ought to have the knowledge required to become certified MCTS on SQL Server, probably Exam 70-432 for a general DBA. Assuming that you mean MS SQL Server, and not some other SQL Server.
Only top voted, non community-wiki answers of a minimum length are eligible
