February 12, 2005 at 5:02 am
Hi, can anyoine help with these? I am SQL developer and dont know much about Administration.
Section 3 – Database Administration
The next set of questions are based on a MS SQL server 6.5 or 7 installation with the following configuration:
Database Size
Master 100Mb
Model 100Mb
Tempdb 2Gb
Customers (user database) 50Gb
3.1 If the Customers database is a heavily used online user database what size log will you specify for this database and why?
Rule of thumb is that the log file should be about 20 - 25 % of the database. Therefore I would make it 1250Mb.
3.2 If you could specify the actual hardware configuration in terms of disc drives that this server will run on, state how you will configure the SQL Server installation and databases to allow for maximum performance and recoverability. Take for granted that all discs are 80Gb SCSI discs so please do not bother specifying RAID setup etc. but rather concentrate on the database and SQL Server setup.
3.3 What backup plan will you implement for this server to ensure maximum uptime in case of failure?
3.4 If you have a disc failure and you loose the master database, how will you go about recovering SQL Server? Please state the SQL you would use.
3.5 If you loose the Customers database for some reason but all other databases are in tact, how will you recover the Customers database without loosing any transactions saved to the database? Please state the SQL you would use.
3.6 If the Customers database becomes corrupt and SQL Server flags it as suspect and you do not have any backups of the database. What route could you take to try and recover data from the corrupt database?
3.7 What is a non-logged operation (list examples) and what is its effect on database recoverability?
3.8 What default database setting should be disabled in a live server environment?
3.9 State the SQL statement to backup the Customers database to the C:\MSSQL7\Backup directory.
3.10 State the SQL statement to truncate the log on the Customers database without logging.
3.11 How do you identify which processes are blocking other processes on MS SQL Server using SQL?
3.12 Which system tables contain the following data?
Data Required System Table Name
All the MS SQL Server system and
user databases
MS SQL Server logins
User database users
Tables, views and procedures on a user database
If Everything Seems To Be Going Well, You Have Obviously Overlooked Something.
February 12, 2005 at 9:22 am
3.2 Place the index and data files on different disks(RAID10). Place the log file on a different disk(RAID 1) . Place the tempdb on its own drive.
3.3 Depends. Weekly full, daily differential and hourly log. (This would vary on your SLA)
3.4 You can use the rebuildm.exe utility.
3.5 Back up the current log file and then perform a restore.
3.6 Try to reset status using sp_resetstatus and if this doesn't work, you can put the database in emergency mode and pull out all the data.
3.7 truncate. you cannot restore the log after that point.
3.8 Auto Close,
3.9 backup database customer to disk = 'c:\mssql7\backup\customer.bak'
3.10 backup log customers with truncate_only
3.11 sp_lock
3.12 sysusers, sysxlogins, sysobjects.
December 29, 2006 at 2:10 pm
So I figured I'd try to answer some of these questions since i have an oral interview coming up in the near future and found some potential(yes, i may not be completely right) corrections for anyone perusing in the future.
3.3 I dont' know if the question was misworded, but to maximize uptime - that is, the time between when the db goes down and when it is back up - you would try to have few txn logs, since the process of applying log after log would take some time. The solution would be to make more frequent differential backups.
3.8 In my fresh install of 2000, autoclose is not selected. For msdn it is. I have auto update, create statistics and torn page detection. Out of these I would assume the most likely would be stats update as it can place a strain on the system if done during times of heavy load. The need for updated stats needs to be balanced with the strain of updating them, so this is not a hard rule.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply