SQL Server 2008 – Problem saving a table change that results in that table being recreated

Some changes in SQL Server require that the table in question be dropped and recreated behind the scenes.  It’s possible that this might cause a problem for some users, so it seems that in the SQL Server 2008 Management Studio, Microsoft has disabled this by default.

You will receive the message:

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

 

Anyway, to allow these changes to occur, go to Tools –> Options, and under Designers, uncheck “Prevent saving changes that require table re-creation”.  That’s it!

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.

What version and service pack am I running?

A quick tip- how to find out what version and service pack of SQL Server a server is running:

SELECT SERVERPROPERTY(‘ProductVersion’) AS SqlServerVersion, SERVERPROPERTY( ‘ProductLevel’) AS ServicePack

Here are the versions and their corresponding build numbers for 2000 and 2005.

RTM	2000.80.194.0
SQL Server 2000 SP1	2000.80.384.0
SQL Server 2000 SP2	2000.80.534.0
SQL Server 2000 SP3	2000.80.760.0
SQL Server 2000 SP3a	2000.80.760.0
SQL Server 2000 SP4	2000.8.00.2039

RTM	2005.90.1399
SQL Server 2005 Service Pack 1	2005.90.2047
SQL Server 2005 Service Pack 2	2005.90.3042

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

max worker threads Options

What does it do?

The max worker threads option controls the number of worker threads available to SQL Server.  One thread typically handles one connection, and it used to handle a batch of SQL statements as the arrive from the client.

Viewing current settings:

Run EXEC sp_configure, and look at the ‘max worker threads’ option.  If you only see a limited list of options, you need to enable the ’show advanced option’ setting, followed by RECONFIGURE.

EXEC sp_configure ’show advanced option’, ‘1′;
RECONFIGURE;
EXEC sp_configure;

As long as the number of connections is less than or equal to the number of threads available, each connection will get a single thread.  After that,  SQL Server will create a pool of worker threads to handle a larger number of clients.

Suggested settings:

For a 32 bit system, Microsoft recommends:

<= 4 processors = 256 max worker threads
8 processors = 288 max worker threads
16 processors = 352 max worker threads
32 processors = 480 max worker threads

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).

Finding the space used by a particular table

A quick tip today: how to find the space used by a particular table:

EXEC sp_spaceused 'tCompany'
GO

Deferred Name Resolution, or “Why does my proc allow invalid table names?”

A developer came to me a few days ago, wondering why when he specifies an invalid table name in a stored procedure, the proc saves to the database, but when he specifies an invalid column name it fails.

Looks like the deferred name resolution only works w/ missing tables…not w/ missing columns: http://msdn.microsoft.com/en-us/library/ms190686.aspx

For instance, this succeeds on an DB:

create proc cp_test
as
select somecolumn from tFakeTable –fake column, fake table

This fails on an DB:

create proc cp_test2
as
select somecolumn from tActualTable–fake column, real table

Text to Binary Translator

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

Link