Posted by mxhxrdba on November 14, 2008
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.
Posted in Uncategorized | Leave a Comment »
Posted by mxhxrdba on October 15, 2008
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.
Posted in Uncategorized | Tagged: SQL Server 2005 AWE PAE sp_configure memory ram 4gb 32 | Leave a Comment »
Posted by mxhxrdba on July 29, 2008

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
Posted in Uncategorized | Leave a Comment »
Posted by mxhxrdba on July 8, 2008

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).
Posted in Uncategorized | Tagged: sql server interview questions 2005 200 2008 | Leave a Comment »
Posted by mxhxrdba on March 20, 2008
…because you never know when you’ll need one.
Link
Posted in Uncategorized | Leave a Comment »
Posted by mxhxrdba on February 4, 2008
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
Posted in Uncategorized | Tagged: 2005, 2008, fragmentation, fragmented, index, Performance, rebuild, reorg, reorganization, sql, sql server, sql server 2005, versus, vs | 1 Comment »