Skip to content

Posts from the ‘SQL Server 2005’ Category

Sep 6

Who changed SQL Server database state to OFFLINE?

To check, who changed the SQL Server database status to OFFLINE, please follow the below steps.

1) Connect to SQL Server → open SQL Server logs and scan through the logs. You will find entry like:
Date 9/6/2012 10:54:54 AM
Log SQL Server (Current – 9/1/2012 12:30:00 PM)
Source spid61
Message
Setting database option OFFLINE to ON for database test21.

This mean, test21 database was changed to OFFLINE on 9/6/2012 @ 10:54:54 AM . Also, we know this change was done by spid 61 at that time.

2) Now go to Windows event viewer (under Administrative Tools) and open Application logs. Here try to look for entries by Source = MSSQLSERVER around 9/6/2012 10:54:54 AM timeframe (This step will be done by logging in to the server by making remote desktop connection)

If windows authenticated account changed the database state then you will find that account here against User. If the change was made by sql authenticated account then it will show N/A.

Proceed to next step if it says N/A.

3) Now we know the spid, timeframe when this change was made. Also, we know the change was made by sql authenticated account. Now, run the below script by changing spid value and starttime to correct value.

DECLARE @FileName VARCHAR(MAX)

SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'
 FROM sys.traces
 WHERE is_default = 1;

SELECT DatabaseID, HostName, ApplicationName, LoginName, DatabaseName
 FROM sys.fn_trace_gettable( @FileName, DEFAULT )
 where starttime = '2012-09-06 10:54:55.117'
 and spid=61

Output:-

DatabaseID HostName ApplicationName LoginName DatabaseName
23 NHYUJM987123456 Microsoft SQL Server Management Studio DM\USER123 test21
23 NHYUJM987123456 Microsoft SQL Server Management Studio DM\USER123 NULL

From output you can see that this change was made by use DM\USER123.

Please note, this step will work only if default trace is enabled and the trace file is still available for the timeframe when database status was changed.

Additionally, you can follow above steps to check who changed database state to ONLINE, READ-ONLY, etc.

May 11

Create file encountered operating system error 3

Applying SQL Server 2005 SP4 may fail with error like below:-

In bootstrap log you will see below message -

Product                   : Database Services (MSSQLSERVER)

Product Version (Previous): 4060

Product Version (Final)   : 

Status                    : Failure

Log File                  : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB2463332_sqlrun_sql.msp.log

Error Number              : 29537

Error Description         : MSP Error: 29537  SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]CREATE DATABASE failed. Some file names listed could not be created. Check related errors.. To continue, correct the problem, and then run SQL Server Setup again. 

Summary

     One or more products failed to install, see above for details

     Exit Code Returned: 29537

Also you will see below error message in windows event viewer under application Log:-

CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘F:\MSSQL.1\MSSQL\DATA\temp_MS_AgentSigningCertificate_database_log.LDF’.

Resolution

You will see below error when the default database data and log file path (mentioned under SQL Server instance properties –> Database Settings) does not exist at OS level. Possibly after installation some one have moved the data and log files to new location and have deleted the old folder structure, but forgot to update the default data and log file path.

To resolve the issue, just update the default database data and log file path under  SQL Server instance properties –> Database Settings and prove a valid path. Then try running your installer again.

Apr 20

SQL Server Maintenance plan not opening

You may get below error when you try to open any SQL Server Maintenance Plan: -

TITLE: Microsoft SQL Server Management Studio
‘Agent XPs’ component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Agent XPs’ by using sp_configure. For more information about enabling ‘Agent XPs’, see “Surface Area Configuration” in SQL Server Books Online. (ObjectExplorer)

Issue: -
Its possible that some one have disabled Agent XPs component on your server.

Resolution: -
Run below sql query to re-enable Agent XPs and then try to open maintenance plan again. It will work.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

Apr 10

1 Comment

Not able to delete SQL Server Maintenance Plan and/or SQL Server Agent jobs

You may get below error while trying to delete MP after server name change: -

TITLE: Microsoft SQL Server Management Studio

Exception has been thrown by the target of an invocation. (mscorlib)

ADDITIONAL INFORMATION:

An error was encountered when trying to remove the package “Maintenance Plans\UserDB_Tlog” from SQL Server. 

and/or

You may get below error while trying to delete SQL Server agent job: -

TITLE: Microsoft SQL Server Management Studio

Drop failed for Job ‘UserDB_Tlog.Subplan_3′.  (Microsoft.SqlServer.Smo)

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_job_id”. The conflict occurred in database “msdb”, table “dbo.sysmaintplan_subplans”, column ‘job_id’.

The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_schedule_id”. The conflict occurred in database “msdb”, table “dbo.sysmaintplan_subplans”, column ‘schedule_id’.

The statement has been terminated.

The statement has been terminated. (Microsoft SQL Server, Error: 547)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4060&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476

Possible cause of this issue: -

Mainly you will face issue if your SQL Server host name is changed.

Solution: -

In order to delete the maintenance plan and/or SQL Server agents jobs, we have to manually delete the entries from system tables in msdb database. Below are the queries you need to fire in msdb database in order to get this done.

select * from sysjobs -- to find the correct jobid

select * from sysjobschedules where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

delete from [dbo].[sysjobschedules] where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

select plan_id from [dbo].[sysmaintplan_subplans] where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

select * from dbo.sysmaintplan_log where plan_id = '755708E5-5B24-4B31-8AC3-38944E6A99A3' -- planid is the value from above sql

delete from [dbo].[sysmaintplan_log] where plan_id = '755708E5-5B24-4B31-8AC3-38944E6A99A3' -- planid is the value frm above sql

select * from [dbo].[sysmaintplan_subplans] where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

delete from [dbo].[sysmaintplan_subplans] where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

delete from sysjobs where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

Now connect to integration services on this server and manually delete the package under msdb

« Older Entries