Archive for December, 2009

[EN] How to get a list of all DBCC commands in SQL Server 2008?

Dec 30th

Posted by Pawel Potasinski in SQL Server

No comments

VN:F [1.7.9_1023]
Rating: 0.0/5 (0 votes cast)

Two years ago I wrote an article about undocumented DBCC commands in SQL Server 2005 for Polish Microsoft TechNet website (the article is in Polish). Yesterday I decided to check if I’m able to make a list of all DBCCs available on SQL Server 2008.

There are only about 30 DBCC commands described in SQL Server 2008 Books Online. Imagine that the full list contains 101 of them (and on SQL Server 2005 the number is even greater). Why Microsoft folks do not document so many commands (some of undocumented DBCCs are quite useful)? Honestly, I have no idea :-)

Fortunately, you can get the full list of DBCC commands easily and play with them on your own. Moreover, you can even get a syntax for each of those commands. So what should you do?

First of all you have to turn on the trace flag:

DBCC TRACEON(2588);

NOTE: On SQL Server 2005 and 2008 the flag number is 2588. On SQL Server 2000 the number is 2520.

Then you can get a full list of DBCCs by executing the following command:

DBCC HELP('?') WITH NO_INFOMSGS;

The result should be a list of all DBCC commands – both documented and undocumented.

NOTE: The DBCC HELP command can be used either to return a list of DBCC commands or to return a syntax of an individual command.

Now, save the result as a file (you can do it easily with SQL Server Management Studio by right-clicking in the Messages window and choosing Save Results As… option).

Assuming you saved the file to C:\dbcc.rpt (if not, change the path respectively in the code) you can use BULK INSERT statement to import the DBCC command names from the file into a temporary table:

IF OBJECT_ID('tempdb.dbo.#dbccs') IS NOT NULL
  DROP TABLE #dbccs;
GO
CREATE TABLE #dbccs(
  [dbcc] nvarchar(4000) NOT NULL);
GO
DECLARE @bulkcmd varchar(8000);
SET @bulkcmd = 'BULK INSERT #dbccs
FROM N''C:\dbcc.rpt''
WITH(
  ROWTERMINATOR = ''' + NCHAR(10) + NCHAR(13) + NCHAR(10) + ''',
  DATAFILETYPE = ''widechar''
)';
EXEC(@bulkcmd);
 
Having a table of DBCC command names turn on the Results to text option in Management Studio run the following query:
 
SELECT 'DBCC HELP(''' + [dbcc] + ''') WITH NO_INFOMSGS'
FROM #dbccs
ORDER BY [dbcc];
 
All you have to do is to copy the generated DBCC HELP commands and execute them.
 
 
NOTE: You will probably have to remove the BOM (byte order mark) from the very first DBCC command name returned by the above query (you can notice some “trash” there). You can delete BOM before running BULK INSERT. The best way to eliminate BOM is to open the .rpt file (it is encoded in UTF-8) with Notepad++ editor and convert it to UTF-8 without BOM.
  
NOTE: The trace flag 2588 (or 2520 on SQL Server 2000) has to be enabled (on session or server level) to be make sure DBCC HELP returns a syntax for every DBCC command.
 

I’m attaching the script containing all the code from this post.

NOTE: Run any undocumented DBCC command at your own risk. Undocumented means unsupported so do not expect Microsoft (or me) to help you with any issue caused by running any unsupported piece of code on your SQL Server instances.

Attachments:

Undocumented DBCCs (T-SQL script)

DBCC commands on SQL Server 2008

DBCC commands on SQL Server 2005

DBCC commands on SQL Server 2000

, ,

[EN/PL] Resources

Dec 29th

Posted by Pawel Potasinski in Blog

No comments

VN:F [1.7.9_1023]
Rating: 2.0/5 (1 vote cast)
[EN]I have just created a new page on my blog – Resources. There you can find some resources – PowerPoint slide decks and demos – from my presentations given at various events in Poland and around the world.
[PL]Właśnie stworzyłem na moim blogu stronę – Resources (Zasoby). Znajdziesz tam zasoby – slajdy PowerPoint oraz dema – z moich sesji zaprezentowanych w czasie różnych konferencji i wydarzeń w Polsce i na świecie. Na początek dodałem prezentację i dema z konferencji “Od studenta do specjalisty Business Intelligence“, która miała miejsce w tym miesiącu w Warszawie.

[EN] How to check with T-SQL query if the TCP/IP protocol is enabled on SQL Server 2008 instance?

Dec 28th

Posted by Pawel Potasinski in SQL Server

No comments

VN:F [1.7.9_1023]
Rating: 0.0/5 (0 votes cast)

Last week I was asked an interesting question: “How to check with T-SQL query if the TCP/IP protocol is enabled on a remote instance of SQL Server 2008 when you can’t use SQL Server Configuration Manager?”. The answer is pretty simple if you know that all the protocol settings are kept in the Windows registry on the server. And so, the query may look like:

DECLARE @key nvarchar(100);
SELECT @key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\' +
  CASE @@SERVICENAME
    WHEN 'MSSQLSERVER' THEN 'MSSQLSERVER'
    ELSE 'MSSQL$' + @@SERVICENAME
  END + '\SuperSocketNetLib\Tcp';
DECLARE @test varchar(20);
EXEC master.dbo.xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @key,
  @value_name = 'Enabled',
  @value = @test OUTPUT;
SELECT CAST(@test AS binary(1));

If the result is binary 1, then the protocol is enabled. You can use regedit.exe to find out the paths to the keys in registry containing the settings for other protocols.

Also, you can check the static TCP port number using the same technique (I assumed the port is the same for all network interfaces):

DECLARE @key nvarchar(100);
SELECT @key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\' +
  CASE @@SERVICENAME
    WHEN 'MSSQLSERVER' THEN 'MSSQLSERVER'
    ELSE 'MSSQL$' + @@SERVICENAME
  END + '\SuperSocketNetLib\Tcp\IpAll';
DECLARE @test varchar(20);
EXEC master.dbo.xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @key,
  @value_name = 'TcpPort',
  @value = @test OUTPUT;
SELECT @test;

Furthermore, you can even enable any protocol using xp_regwrite extended stored procedure (see the article for details on the procedure). Remember that after you make any changes to the protocol settings the SQL Server service has to be restarted (probably the best way is to create a .bat/.cmd batch at the remote server that uses net start and net stop commands to restart the SQL Server instance).

If you think this is something ugly – to play with the registry within T-SQL query – remember that the SQL Server service account has full rights to the registry keys that contain the SQL Server settings.

, ,

[EN / PL] Let’s go!

Dec 28th

Posted by Pawel Potasinski in Offtopic

1 comment

VN:F [1.7.9_1023]
Rating: 0.0/5 (0 votes cast)
Welcome to my new blog. All posts from the previous one will stay there at http://zine.net.pl/blogs/sqlgeek. And for now – Merry Christmas and a Happy New Year! Witaj na moim nowym blogu. Wszystkie notki z poprzedniego bloga zostają pod adresem http://zine.net.pl/blogs/sqlgeek. A tymczasem – Wesołych Świąt i Szczęśliwego Nowego Roku!

[PL] PS. Przepraszam wszystkich, którym wyciąłem komentarze do tej notki. Musiałem niestety przeinstalować silnik WordPress :-)