sp_readerrorlog sql 2005 search for string and dates directly in query by using xp_readerrorlog

Just wanted to share this useful info. We can directly search for Strings between two dates using xp_readerrorlog in SQL 2005 (Undocumented Procedure)

Below is the T-SQL command and usage information. This will be really useful since most of us used to import the error log into table then query it and export the required section and send as email.

Now since DB Mail has option of specifying query directly, we can put this T-SQL Query in DBMail command and with one single line we can close the requirement.

exec xp_readerrorlog 0, 1,'login','error','2009-02-28 11:55:00.000','2009-03-03 11:55:00.000','asc'

Usage Info:
This procedure takes 7 parameters
1.Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
2.Log file type: 1 or NULL = SQLServer Error log, 2 = SQL Server Agent Error log
3.Search string 1: String one you want to search for
4.Search string 2: String two you want to search for to further refine the results
5.Search from start time
6.Search to end time
7.Sort order for results: N'asc' = ascending, N'desc' = descending

Above option will work only for SQL 2005 incase you want to try in SQL 2000 then you use this link for more

Things to be done after Cloning SQL Server... Taking OS Level Image of SQL Server...

Sometimes you may get this requirement... Windows team may clone the SQL Server and ask you to bring up SQL Server in new cloned server since old server will retire soon... then? Below steps will help you

1) After cloning when you start the SQL Server you may get “Cannot find the path specified”. Incase you get this go to SystemàCurrentControlSetàServiceàMSSQLSERVER and change the directory.

2) Now when you start the SQL Server wherever the transaction logs are inconsistent those databases will go into suspect mode. So run below query to bring those databases into Emergency mode so that when you restart SQL Server it will not check for log files.

Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name ='msdb'
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO

3) Now stop the Service and then renamed the LDF of all the databases which have gone into suspect mode. Start SQL Server service and run below commands to create new LDF files for those databases.

DBCC REBUILD_LOG(msdb,'D:\MSSQL\Data\msdblog.ldf')

You will get below output
Warning: The log for database 'msdb' has been rebuilt. Transactional consistency has been lost. DBCC CHECKDB should be run to validate physical consistency. Database options will have to be reset, and extra log files may need to be deleted.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

4) Now after repeating above steps for all suspect databases, reset the status to online by running below command.

Execute sp_resetstatus msdb

You will get below output
Prior to updating sysdatabases entry for database 'msdb', mode = 0 and status = 2048 (status suspect_bit = 0).No row in sysdatabases was updated because mode and status are already correctly reset. No error and no changes made.

5) Now we need to change the @@SERVERNAME to point to new server. But you cannot run the sp_dropserver and sp_addserver incase you have this as publisher in replication. In this case you have to update system tables using below command. You need to do this in all the columns needed in srvid=0 for local server and srvid=1 for distributor server.

Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysservers set datasource = ABCDEF-1' where srvid=1
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO

6) Now also you have to update MSdistpublishers table in the msdb where the distributor server name will be stored.

7) Now you need to update the originating_server column in msdb which will have reference to old server

8) Now Restart SQL Server and everything should work fine…

Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails.

When trying to run sp_helpdb on one of the instances I got this error message

Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column 'owner', table 'tempdb.dbo.#spdbdesc_ The statement has been terminated.

Root Cause:
sp_helpdb returns information about all of your databases on a given instance and can be very useful for different reasons - one can be to monitor the size of the databases. For the above error received - the resolution is pretty straight forward. As the error message states, the database needs to have an owner else it will try to insert a NULL value into the "Owner" column of the temporary table and it will fail to provide the output when the system stored procedure sp_helpdb gets executed.

Solution
And the resolution is pretty straight forward as well - find the databases which do not have an owner assigned and assign an owner to them. We can use sysdatabases or sys.databases (in SQL Server 2005 and 2008):

For SQL Server 2000:

select name as database_name, SUSER_SNAME(sid) as owner_name, crdate as create_date_time from master.dbo.sysdatabases

For SQL Server 2005:

select name as database_name, SUSER_SNAME(owner_sid) as owner_name, create_date as create_date_time from master.sys.databases

And then see which databases do not have an owner assigned to them. The ones that do not have an owner assigned to them can be assigned an owner using this command:

Use {DB Name Here}
GO
EXEC sp_changedbowner {put the owner name here}

Once that is done, execution of sp_helpdb will work perfectly fine.

Web Based SQL Database Administration Tool From Microsoft

This is a web based application that can be installed on a web server in your environment to manage your SQL Servers over internet or intranet.

Click here to Download the tool