Recovering a database with a missing ldf files and no backups

Hopefully, you would never find yourself in this position to begin with.  But if so, here are the steps to get a working database back with a viable log:
1. Detach database and move your mdf to save location.
2. Create new databse of same name, same files, same file location and same file size.
3. Stop SQL server.
4. Swap mdf file of just created DB to your save one.
5. Start SQL. DB will go suspect.
6. ALTER DATABASE <your db> SET EMERGENCY
ALTER DATABASE <your db> SET SINGLE_USER
7. DBCC CHECKDB (<your db>, REPAIR_ALLOW_DATA_LOSS)
8. ALTER DATABASE <your db> SET MULTI_USER
ALTER DATABASE <your db> SET ONLINE

In SQL 2000, this could be accomplished using the DBCC REBUILD_LOG command, but that is unsupported in SQL 2005.

Enabling AWE on SQL Server 2005 for servers with > 4GB RAM

Here are the steps to enable AWE so that Sql Server 2005 can use additional memory, if your physical memory is more than 4 GB on 32-bit Windows 2003 box.

1. Add /pae at the end of last line inside c:\boot.ini
2. Run gpedit.msc. On the left hand pane, expand Computer Configuration, expand Windows Settings, expand Security Settings, expand Local Policies, select User Rights Assignment
3. On the right hand pane, find Lock pages in memory and double click, then add your Sql Server startup account into Local Security Policy Setting tab;
4. In Sql Server Management Studio, run:
sp_configure ’show advanced’, 1
reconfigure
sp_configure ‘awe enabled’, 1
reconfigure
Ignore the error message below, if you have it.
Msg 5845, Level 16, State 1, Line 1
Address Windowing Extensions (AWE) requires the ‘lock pages in memory’ privilege which is not currently present in the access token of the process.
5. Reboot.

Find all tables without triggers

In our production environment, every user table has an auto-generated trigger that facilitates auditing.  Since trigger generation is an automated process, it occasionally fails to run for a plethora a reasons; this it is a good practice to periodically scan for tables that are missing triggers.  The following query will return tables that fit this condition by using a LEFT JOIN between sysobjects and itself.

SELECT   sys1.name
FROM     sysobjects sys1
LEFT JOIN sysobjects sys2
ON sys1.id = sys2.parent_obj
AND sys2.xtype = ‘TR’
WHERE    sys2.name IS NULL
AND sys1.xtype = ‘U’
ORDER BY sys1.name

SQL Server Interview Questions

In case you’re looking for some quick questions to screen a prospective candidate working with SQL Server, here is a link to some SQL Server Interview Questions (from SQLAuthority.com).

Text to Binary Translator

…because you never know when you’ll need one.

Link

Index Rebuild versus Reorganize: What’s the difference?

In a nutshell, you should rebuild when an index is over 30% fragmented and reorganize when an index is between 10% and 30% fragmented. If fragmentation is below 10%, you can probably just leave that index alone. Rebuilding takes more server resources (and uses locks unless you use the ONLINE option available in 2005 Enterprise and Development editions), so reorg when possible. Syntax below to rebuild or reorg all indexes on a table named tTable follows…

REBUILD

ALTER INDEX ALL ON dbo.tTable REBUILD
GO

REORGANIZE

ALTER INDEX ALL ON dbo.tTable REORGANIZE
GO