Change Ownership of a Maintenance Plan

To change the ownership of a Maintenance Plan (dts or SSIS package) the following scripts will make te changes

--SQL 2005 version
UPDATE msdb.dbo.sysdtspackages90
SET ownersid = 0x01 --this is the sa user but set to which ever user you want
WHERE name = 'MaintenancePlanName'

If this maintenance plan is scheduled the job owner could also need updating as well.

-- SQL 2005 version job owner update
USE msdb
GO
EXEC msdb.dbo.sp_update_job @job_name=N'Your Job Name',
@owner_login_name=N'sqljobuser'
GO

Examples for other versions of sql server are

--SQL 2000 version
UPDATE msdb.dbo.sysdtspackages
SET owner = 'sa',
owner_sid = 0x01 --sa user
WHERE name = 'MaintenancePlanName'

--SQL 2008 version

UPDATE msdb.dbo.sysssispackages
SET ownersid=0x01 --sa user
WHERE name='MaintenancePlanName'

Login Failed Error States

The common security error states and their descriptions for the error 18456 severity 14 state 8
are listed below:-

ERROR STATE ERROR DESCRIPTION
2 and 5 Invalid userid
6 Attempt to use a Windows login name with SQL Authentication
7 Login disabled and password mismatch
8 Password mismatch
9 Invalid password
11 and 12 Valid login but server access failure
13 SQL Server service paused
18 Change password required

Error Log Management

The SQL server error log may require that the size of the log is limited if the growth rate is excessive. This can be done using the sp_cycle_errorlog system stored procedure. This will start a new error log. The script

use msdb
exec sp_cycle_errorlog

The log displays
Current log
Attempting to cycle error log. This is an informational message only; no user action is required.
New log
The error log has been reinitialized. See the previous log for older entries.

Virtual Log Files

Each transaction log file is divided logically into smaller segments called virtual log files (VLFs). Virtual log files are the unit of truncation for the transaction log. When a virtual log file no longer contains log records for active transactions, it can be truncated and the space becomes available to log new transactions. VLF can be in 4 states

1.Active

2.Inactive and not backed up (or truncated) - recoverable

3.Inactive and backed up or truncated - recycable

4.Unused

DBCC LOGINFO shows a list of virtual log files that are active logs when their status flag is 2. The sample sql is

DBCC LOGINFO('Database_name')

This returns one row per VLF.

Number of rows = Number of VLFs

Sample output from this command.

FileId,FileSize,StartOffset,FSeqNo,Status,Parity,CreateLSN
2,253952,8192,21,0,128,0
2,253952,262144,22,0,128,0
2,253952,516096,20,0,64,0
2,278528,770048,23,2,64,0

The smallest size for a virtual log file is 256 kilobytes (KB). The minimum size for a transaction log is 512 KB, which provides two 256-KB virtual log files.

It is good to Minimize VLF’s. Excessive autogrowth of the transaction log causes:

* Windows call to extend a file (may cause file fragmentation)
* Adds “VLFs” to the file on each autogrowth

If there are excessive VLFs (> 50) then the first cause of action should be to free up log space by first clearing space from the transaction log (using BACKUP LOG)

Another useful command is DBCC SQLPERF (logspace) which shows the transaction log size and log space used percentage.

Active transaction log DBCC log command

This undocumented command is used to view the transaction log

DBCC log ( {dbid|dbname}, [, type={-1|0|1|2|3|4}] )

PARAMETERS:
Dbid or dbname - Enter either the dbid or the name of the database
in question.

type - is the type of output:

0 - minimum information (operation, context, transaction id)
1 - more information (plus flags, tags, row length, description)
2 - very detailed information (plus object name, index name,
page id, slot id)
3 - full information about each operation
4 - full information about each operation plus hexadecimal dump
of the current transaction log's row.
-1 - full information about each operation plus hexadecimal dump
of the current transaction log's row, plus Checkpoint Begin,
DB Version, Max XDESID

by default type = 0

Here is sample data output

dbcc log (testdatabase, 2)

Current LSN,Operation,Context,Transaction ID,Tag Bits,Log Record Fixed Length,Log Record Length,Previous LSN,Flag Bits,AllocUnitId,AllocUnitName,Page ID,Slot ID,Previous Page LSN,Number of Locks,Lock Information,Description 00000017:000001e3:0001,LOP_BEGIN_CKPT,LCX_NULL,0000:00000000,0x0000,96,96,00000017:000001d4:0001,0x0000,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 00000017:000001e4:0001,LOP_END_CKPT,LCX_NULL,0000:00000000,0x0000,136,136,00000017:000001e3:0001,0x0000,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 00000017:000001ed:0001,LOP_BEGIN_XACT,LCX_NULL,0000:000003f2,0x0000,48,92,00000000:00000000:0000,0x0200,NULL,NULL,NULL,NULL,NULL,NULL,NULL,user_transaction;0x01 00000017:000001ed:0002,LOP_INSERT_ROWS,LCX_HEAP,0000:000003f2,0x0000,62,104,00000017:000001ed:0001,0x1200,72057594043695104,dbo.tblmixednumbertext,0001:0000005d,10,00000017:000001e8:0003,3,ACQUIRE_LOCK_IX OBJECT: 10:21575115:0 ;ACQUIRE_LOCK_IX PAGE: 10:1:93;ACQUIRE_LOCK_X RID: 10:1:93:10, 00000017:000001ed:0003,LOP_COMMIT_XACT,LCX_NULL,0000:000003f2,0x0000,48,52,00000017:000001ed:0001,0x0200,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

How to clean up a Database Server

CHECKPOINT
This will write all dirty buffers to disk so that when you run DBCC DROPCLEANBUFFERS, you can be assured that all data buffers are cleaned out.

DBCC DROPCLEANBUFFERS
To remove all the data from the buffers. This is useful to run between performance tests to ensure accurate testing results. This command only removes clean buffers.

DBCC FREEPROCCACHE
Clears out the stored procedure cache for all SQL Server databases.

DBCC ERRORLOG: Rarely restarting the SQL Server service can cause the server log to get very large and take a long time to load and view. This can be truncated (essentially creating a new log) by running DBCC ERRORLOG. Consider scheduling a regular job that runs this command once a week. The same thing can be done using sp_cycle_errorlog.

Reading SQL Server log files

There is an undocumented system stored procedure sp_readerrorlog. This script returns all of the rows from the 3rd archived error log.

EXEC sp_readerrorlog 3

sp_readerrolog accepts only 4 parameters, but the extended stored procedure accepts at least 7 parameters.

EXEC sys.xp_readerrorlog @p1,@p2,@p3,@p4

@P1 = Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc…
@P2 = Log file type: 1 or NULL = error log, 2 = SQL Agent log
@P3 = Search string 1: String one you want to search for
@P4 = Search string 2: String two you want to search for to further refine the results

A few examples

This returns a result when 2008 appears.
EXEC sp_readerrorlog 3, 1, '2008'

Rows are returned where the value '2008' and 'backup' exist.
EXEC sp_readerrorlog 3, 1, '2008', 'backup'

Sample log

2009-06-02 16:38:39.12 Server Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

2009-06-02 16:38:39.13 Server (c) 2005 Microsoft Corporation.
2009-06-02 16:38:39.19 Server All rights reserved.
2009-06-02 16:38:39.19 Server Server process ID is 856.
2009-06-02 16:38:39.19 Server Authentication mode is MIXED.
2009-06-02 16:38:39.19 Server Logging SQL Server messages in file 'D:\MSSQL10.SQLMANAGE\MSSQL\Log\ERRORLOG'.....

How to find the log space usage

DBCC SQLPERF (logspace) provides transaction log space usage statistics for all databases. The sample output returns

Database Name,Log Size (MB),Log Space Used (%),Status
master,1.242188,40.8805,0
tempdb,0.4921875,47.32143,0
model,6.117188,94.44444,0
msdb,1.992188,43.13726,0