SQL SERVER – SQL TIPS – SELECT the ERROLOG.

A useful tip for look into the ERRORLOG, is use a SELECT statement with ERROLOG as table.

First is necessary to create a table with the structure of the ERROLOG outcome.

CREATE TABLE TBLERRORLOG

(

LOGDATE DATETIME,

PROCESSINFO VARCHAR(30),

DETAIL VARCHAR(8000)

)

Then, full the table with the results of extended stored procedure xp_readerrolog:

INSERT INTO TBLERRORLOG exec master..xp_readerrorlog

Now is possible SELECT the table TBLERRORLOG with a certain filter:

SELECT * FROM TBLERRORLOG where DETAIL like '%master%'

If you have several error logs, you can use the following code:

truncate table TBLERRORLOG

Go

INSERT INTO TBLERRORLOG exec sp_readerrorlog 2

go

SELECT * FROM TBLERRORLOG where DETAIL like '%master%'

First, the table TBLERRORLOG is truncated, then the table is filled using the undocumented stored procedure sp_readerrolog, there is possible to define the ERROLOG number, in this case I use 2, then a SELECT statement is used. This code can be used several times depending of the ERROLOG allowing us a quickly way to find some issue.

SQL SERVER – TSQL – Handling Errors

For SQL Server 2000 and earlier version, the most common way to working error as using @@ERROR function. SQL Server 2005 introduces the construct TRY…CATCH, it improves handling errors. The construct TRY…CATCH is the principal tool for handling error on SQL Server, specifically with exceptions errors. This capability allows you to manage exception errors in similar way as .NET programming languages.

A TRY…CATCH consist in two parts: these are a TRY block followed immediately by a CATCH block. All the TSQL Code must to be on TRY block and the handling error must to be on CATCH block. When in the TRY block does not exist error, the CATCH block is simply skipped. If the TRY block has an error, then the control is transferred to the next CATCH block.

Both, TRY and CATCH block starts with BEGIN TRY and BEGIN CATCH respectively, and ends with END TRY and END CATCH respectively.

You can test the following code in order to see a case without error on TRY block.

-- TSQL WORKING FINE

BEGIN TRY

PRINT 100 / 10

PRINT 'Working on BLOCK TRY. WITHOUT ERRORS!!!!!!'

END TRY

BEGIN CATCH

PRINT 'Working on BLOCK CATCH. WITH ERRORS!!!!!!'

END CATCH

The code on TRY block was executed successfully; that’s way the CATCH block was not executed. The result is the following:

10

Working on BLOCK TRY. WITHOUT ERRORS!!!!!!

Now, execute the following code with error on the TRY block:

-- TSQL WITH ERROR - DIVIDE BY ZERO

BEGIN TRY

PRINT 100 / 0

PRINT 'Working on BLOCK TRY. WITHOUT ERRORS!!!!!!'

END TRY

BEGIN CATCH

PRINT 'Working on BLOCK CATCH. WITH ERRORS!!!!!!'

END CATCH

The divide by zero causes an ERROR on the TRY block, it cause that the control was passed immediately to CATCH BLOCK and also cause that the second PRINT on TRY block was not executed. The result is the following.

Working on BLOCK CATCH WITH ERRORS!!!!!!

To obtain information about the error that cause the CATCH block to be executed, SQL Server provides the following system functions:

ERROR_NUMBER() function returns an integer with the number of error

ERROR_SEVERITY() function returns the severity number.

ERROR_STATE() function returns the error state number.

ERROR_LINE() function returns the line number inside the routine that caused the error.

ERROR_MESSAGE() function returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

ERROR_PROCEDURE() function returns the name of the stored procedure or trigger where the error occurred.

You can execute the following code in order to create a Stored Procedure that encapsulates the system functions mentioned above. Also you can customize the error messages. The following code is customized when the error number 208 (Invalid Object Name) happens, this customized message error will be used later. :

create procedure dbo.sp_err_msg

as

if ERROR_NUMBER() = 208

print 'Oops, the table queried does not exists. '

else

select

ERROR_NUMBER() as ErrorNumber,

ERROR_SEVERITY() as Severity,

ERROR_STATE() as State,

ERROR_LINE() as ErrorLine,

ERROR_MESSAGE() as ErrorMessage,

ERROR_PROCEDURE() as ErrorOnProcedure

GO

You can modify the second code used in this article in order to see how works the error system functions.

BEGIN TRY

PRINT 100 / 0

PRINT 'Working on BLOCK TRY. WITHOUT ERRORS!!!!!!'

END TRY

BEGIN CATCH

exec dbo.sp_err_msg

END CATCH

The divide by zero cause an error on TRY block, then the control was passed to CATCH block and the stored procedure is executed. The result is the following.

Some errors are not handled by construct TRY…CATCH :

• Compilation errors. Ex syntax error.
• Recompilation errors. Ex object name resolution error.

If an compilation or recompilation errors occurs when they are at TRY … CATCH level, those errors are not handled by construct TRY …CATCH. On the other hand, if the error occurs at a lower execution level (ex. Sp_executesql or a stored procedure) the errors are handled by the construct TRY…CATCH.

Run the following code to demonstrate an event that is not handle by TRY…CATCH. The code, execute a SELECT statement to a non existing table, it cause an error:

BEGIN TRY

SELECT * FROM TABLENOTDEFINED

END TRY

BEGIN CATCH

exec dbo.sp_err_msg

END CATCH

Result:

Msg 208, Level 16, State 1, Line 2

Invalid object name 'TABLENOTDEFINED'.

However, if the SELECT statement is executed by a stored procedure the error will occur at a level lower than TRY block. Then, the error will be handled by the construct TRY … CATCH.

create procedure dbo.sp_exec_TABLENOTDEFINED

as

SELECT * FROM TABLENOTDEFINED

GO

BEGIN TRY

exec dbo.sp_exec_TABLENOTDEFINED

END TRY

BEGIN CATCH

exec dbo.sp_err_msg

END CATCH

The table non defined on the Stored procedure cause an error 208, it is handled by TRY ... CATCH, and a customized error message is shown.

Result.

Oops, the table queried does not exists.

Conclusion

TRY..CATCH is the main tool provided by SQL Server to handling errors. To obtain more information about the errors is possible to use a system functions as: ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_MESSAGE(), ERROR_PROCEDURE().

SQL SERVER – TIPS – Reclaim space for a table after drop column.

In a table, after a column is dropped using alter table drop column statement, is necessary to reclaim the space, just when a fixed-length column or text column were dropped.

To see the behavior you can create 3 tables with 3 fields, this 3 fields must to be different datatypes

-- create a db test create database db_test go use db_test go -- create 3 table with the same fields create table test1 ( a char(20), --fixed-lenght column b varchar(20), --variable-lenght column c text --text column ) go create table test2 ( a char(20), --fixed-lenght column b varchar(20), --variable-lenght column c text --text column ) go create table test3 ( a char(20), --fixed-lenght column b varchar(20), --variable-lenght column c text --text column ) Go

Now, you can add some information and see the space of each table.

-- insert some information into the 3 tables. insert into test1 values ('aaaaaaaaaa','bbbbbbbbb','text text text text text text text text text text text text text ') insert into test2 values ('aaaaaaaaaa','bbbbbbbbb','text text text text text text text text text text text text text ') insert into test3 values ('aaaaaaaaaa','bbbbbbbbb','text text text text text text text text text text text text text ') --checking the space sp_spaceused test1 Go sp_spaceused test2 Go sp_spaceused test3 GO /* --Result test1 1 32 KB 24 KB 8 KB 0 KB test2 1 32 KB 24 KB 8 KB 0 KB test3 1 32 KB 24 KB 8 KB 0 KB */

Then, drop a column for each table, after drop a column the space for each table is the same than before. To reclaim the space is necessary to execute the dbcc cleantable on each table. This dbcc utility must to be executed when a variable-length column or text column were dropped.

-- drop one column in each table in order to -- see the behavior after drop a column alter table test1 drop column a alter table test2 drop column b alter table test3 drop column c --reclaim space dbcc cleantable ('db_test','test1') dbcc cleantable ('db_test','test2') dbcc cleantable ('db_test','test3')

MSDN says “DBCC CLEANTABLE reclaims space after a variable-length column or a text column is dropped by using the ALTER TABLE DROP COLUMN statement. The command does not reclaim space after a fixed-length column is dropped.”

After execute DBCC CLEANTABLE, you can see that some space was reclaimed for the table test3 where the TEXT column was dropped, for the other two tables the space is the same, even for the table 2 where a variable-length column was dropped, in this case some space must to be reclaimed, perhaps does not happening anything because the amount of data is not relevant.

sp_spaceused test1 Go sp_spaceused test2 Go sp_spaceused test3 GO /* --Result test1 1 32 KB 24 KB 8 KB 0 KB test2 1 32 KB 24 KB 8 KB 0 KB test3 1 24 KB 16 KB 8 KB 0 KB */

SQL SERVER – TIPS – Transfer Objects between Schemas

In this tip, I show you, how you can transfer an object between schemas using ALTER SCHEMA statement.

-- create a db test create database db_test go use db_test go --Create a schema original create schema original go -- create a schema final create schema final go -- create a table with the schema original create table original.test1 (a int) go -- checking the schema for the test1 table select schema_name(schema_id) as schema_name, name from sys.objects where name = 'test1' go /* -- Result schema_name name ------------ --------------- original test1 */ -- transfering object test1 from schema original to final -- ORIGINAL -> FINAL alter schema final transfer original.test1 go /* -- Result Command(s) completed successfully. */ -- checking the schema for the test1 table after the changes select schema_name(schema_id) as schema_name, name from sys.objects where name = 'test1' go /* -- Result schema_name name ------------ --------------- final test1 */

SQL SERVER – Working with ERRORLOG.

In this article we are going to see how we can work with ERRORLOG. What is the ERRORLOG? The ERRORLOG, is the SQL Server Log, it means, the place where we can check the errors for SQL Server.

SQL Server has several ERRORLOG files, by default each instance contains 6 ERROLOG files, it value can be changed and we are going to see how to do it, later. Every time that SQL Server starts a new ERRORLOG file is generated and the current ERROLOG is renamed to ERROLOG.1; and the ERRORLOG.1 is renamed to ERRORLOG.2 and so on.

How to see ERRORLOG content?

To see the ERRORLOG we can use the Microsoft SQL Server Management Studio (SSMS) or using TSQL.

Using TSQL:

We can use a extended stored procedure called xp_readerrorlog, it display the errors existing on the last ERRORLOG file.

xp_readerrorlog

Results:

LogDate ProcessInfo Text ----------------------- ------------ ----------------------------------------------------- 2009-07-01 18:35:02.150 spid53 Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) 2009-07-01 18:35:02.150 spid53 (c) 2005 Microsoft Corporation. 2009-07-01 18:35:02.150 spid53 All rights reserved. 2009-07-01 18:35:02.150 spid53 Server process ID is 1208. 2009-07-01 18:35:02.150 spid53 Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.

Using SSMS:

We can check one file or all the files mixed in the following path: SSMS->Management->SQL Server Logs.

How to see ERRORLOG files list?

To know the file name, creation date and file size for the errorlog files in my SQL Server instance, we can use the stored procedure called xp_enumrrorlogs, it does not need any input parameter.

exec xp_enumerrorlogs

Results:

Archive # Date Log File Size (Byte) ----------- --------------------- -------------------- 0 07/01/2009 18:35 1348 1 07/01/2009 18:35 1616 2 07/01/2009 17:42 1616 3 07/01/2009 17:40 1616 4 07/01/2009 17:27 14222 5 06/29/2009 07:19 13004 6 06/28/2009 00:06 2588 (7 row(s) affected)

Obviously, we can use Windows Explorer J as well. In the results of xp_readerrorlog we can see where the errolog files are located.

How ERRORLOG can be Cycling/Purge/Reset?

As is mentioned on the beginning of this article, every time that SQL Server is started the ERRORLOG is purge and a new ERRORLOG file is generated.

When a SQL Server instance is up and running for long periods of time, the errorlog file grow up considerably and it cause hard to read the entire file. SQL Server offers the capability to cycle the ERRORLOG in a manual way, to perform this task we can use the system stored procedure called sp_cycle_errorlog.

exec sp_cycle_errorlog

Result.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

When sp_cycle_errorlog is executed successfully, the current ERRORLOG file is renamed to ERRORLOG.1 and the same behavior happens with the rest of ERRORLOG files. The last ERRORLOG file is deleted.

Another way to purge the SQL Server log is using the DBCC ERROLOG. This DBCC is used when the stored procedure sp_cycle_errorlog is executed.

DBCC ERRORLOG

How many old ERRORLOG are needed?

If the plan is to cycle/purge/reset the errorlog file in a manual way, is necessary keep in mind the timeframe to generate a new log file. Also is necessary to know the frequency to reset the log and the amount of information necessary to hold.

If the plan is to hold more files than the default. We can change using the SSMS->Management -> SQL Server Logs, then right click and choose the Configure option. The values allowed are from the default (6) until 99, it value is the maximum value allowed by SQL Server.

Conclusion

Having an ERRORLOG file in a reasonable size, it makes easier to browse and analyze the log error. This article show us how we can work with the errolog file: cycling the file with sp_cycle_errorlog, reading the file with xp_readerrorlog, listing the files with xp_enumerrologs and changing the total of errorlog files.