SQL Server Database Administration - FAQs

1. What does "breaking the continuity of transaction log" mean?
It basically means that you can't restore a sequence of log backups on top of restoring a full database backup, to recover a database to a point in time in the event of a disaster. The continuity of the transaction log will break when you clear the log without backing it up or when you switch from full recovery model to simple recovery model.
For example if you have a backup strategy where you do a full database backup every week (on Sunday night) and a transaction log backup every day . For some reason, you switched to simple recover model on Wednesday (around 10 am) and you changed the recovery model back to full after an hour. Unfortunately your server crashed on Friday. In this scenario, you will only be able to restore until Tuesday. Since the chain of transaction log backups was broken on Wednesday, all the log backups taken since then will be useless.
2. Does switching from full to Bulk recovery model break the continuity of the transaction log?
No it doesn't, but you need to backup the transaction log right before and right after you switch between full and bulk-logged recovery models.
3. Can I restore a SQL Server 2008 backup to a SQL Server 2005 server?
You can't restore a backup from a higher version of SQL Server on to a lower version of SQL Server. That being said, you can however, script the definition and data in SQL Server 2008(be sure to select the option Script for SQL Server 2005) and use the script to create your database back on SQL Server 2005.
4. What is tail of the log?
Transaction log backup taken after failure is called tail of the log. The tail of the log can be used to recover up to the point of failure.
5. Why is my transaction growing unexpectedly and how can I fix it?
This question is a little tough to answer without knowing more details, but one of the most common reasons for this unexpected growth is due to improper initialization of the transaction log file. Please read this excellent post by Kimberly Tripp for more details.
6. How is error handling done in SQL Server?
SQL Server provides the following functions/commands to help in troubleshooting Transact-SQL code:

• @@ERROR function
• @@ROWCOUNT function
• RAISEERROR function
• Try...Catch

For more details, refer the post Error Handling in SQL Server
7. Can I use bulk-logged model for a database that is setup for Database Mirroring?
Database Mirroring only supports full recovery model, so no.
8. What is the difference between Import Export Wizard and Copy Database Wizard?
The following post will help - Import Export vs Copy Database Wizard in SQL Server.
9. Can I use SQL Server Management Studio(SSMS) 2005 to connect to a SQL Server 2008 instance?
You'll need to install a Cumulative Update in order to connect to a 2008 instance using SQL Server 2005, without that, no.
10. What is the difference between a logical read and a physical read?
A logical read occurs when SQL Server finds the page it needs in the buffer cache. A physical read occurs when SQL Server doesn't find the page it needs in the buffer cache and so has to go to the disk to get it.
11. Will the database backup include all the transactions that occured during the backup process? If yes, how does SQL Server manage that?
Yes, a full database backup is an image of the entire database at the time at which the full backup completed. To accomplish this, SQL Server marks the log between the start of the backup and end of the backup and then applies those transactions to the backup.
12. What is Change Data Capture (CDC)?
Change Data Capture (CDC) tracks DML(Inserts, Updates, Deletes) and DDL(Create, Alter, Drop) activity on SQL Server tables. To store the information about the changes made to the tables being tracked, CDC creates change tables that have the same structure (columns) as that of the source tables plus a few more columns that are specific to CDC. CDC simply reads the transaction log to capture the changes made to the tables and then writes that information into the corresponding change tables. CDC is available on enterprise, developer and evaluation edition only. For more details, check this post - Change Data Capture in SQL Server 2008
13. To enable Change Data Capture (CDC), does the database need to be in full recovery model?
No, Change Data Capture (CDC) can work with any recovery model.
14. I have enabled Change Data Capture (CDC) on our database but nothing is being captured in the change tables. Is there anything that I need to check?
Change Data Capture (CDC) uses SQL Agent jobs to record and clean up data, check if SQL Agent is up and running.
15. Is point in time recovery possible with Bulk-logged recovery model?
With BULK_LOGGED recovery model, you won’t be able to do point in time recovery to any time within a transaction log backup containing at least one bulk-logged operation.
16. What is a covering index?
A covering index is one that has all the columns required to satisfy a query so that there is no need to perform a bookmark lookup (or key lookup) to find additional columns.
17. What is the difference between a clustered and a non-clustered Index?
The following post will help - Clustered Vs Nonclustered Index.
18. What is the use of Merge statement in SQL Server?
This is a new feature of SQL Server 2008 T-SQL language that lets you do INSERTs, UPDATEs, and DELETEs in a single SQL statement. For more details, refer the post Working with Merge in SQL Server 2008.
19. How can I upgrade an Evaluation Edition of SQL Server 2008 to any license edition?
The following post will help - SQL Server 2008 Evaluation Edition Upgrade.
20. How do you optimize a slow running query?
The following post will help - How to optimize a slow running query in SQL Server.
21. How often should I reboot my SQL Server? Should I schedule a reboot?
There is no need to reboot unless it’s really needed, example after Service Pack installations or when the Tempdb database is FULL.
22. How often should I rebuild my indexes?
As often as are required. The general rule of thumb is -

• If the fragmentation level is over 30% - Rebuild the index.

• If the fragmentation level is between 10% and 30% - Reorganize the index.

• If the fragmentation level is below 10% - Don’t need to optimize do any index optimization.

To do this, you will have to automate index optimization. Here is one way to do it:

1. Create a loop to walk through all the tables.

2. Programmatically determine the level of fragmentation for each index.

3. Rebuild/Reorganize each index based on the level of fragmentation.

4. Wrap up all the above code in a Stored Procedure.

5. Create and schedule a job using the Stored Procedure created in step 4.

23. Is backup compression supported in SQL Server 2008 Standard Edition?
No, backup compression is only supported in Enterprise Edition. However every SQL Server 2008 Edition can restore a compressed backup.
23. What operations are minimally logged in Bulk-logged recovery model?
The following operations will be minimally logged:

1. Bulk import operations (BCP, BULK INSERT,etc)
2. SELECT INTO operations
3. WRITETEXT and UPDATETEXT BLOB
4. CREATE INDEX (this also includes indexed views)
5. ALTER INDEX REBUILD or DBCC DBREINDEX
6. DROP INDEX

To be continued...