DBCC log

DBCC log ( {dbiddbname}, [, type={01234}] )

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)

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.by default type = 0

To view the transaction log for the master database, you can use the following command:

DBCC log (master)

DBCC MEMORYSTATUS

How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005:
http://support.microsoft.com/kb/907877/en-us


Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage:
http://support.microsoft.com/kb/271624



select * from sys.dm_os_performance_counters
SELECT
SUM(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
FROM sys.dm_os_memory_clerks

SELECT TOP 10 type,
SUM(single_pages_kb) as [SPA Mem, Kb]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC

SELECT
(Physical_memory_in_bytes/1024.0)/1024.0 AS Physical_memory_in_Mb
FROM
sys.dm_os_sys_info

Check Databases

USE [AutoISSUEdotNet]
GO
DBCC CHECKDB WITH NO_INFOMSGS --index


USE [ReportServer]
GO
DBCC CHECKDB(N'ReportServer', NOINDEX) -----noindex

SQL Server 2005 Error log manaagement

By default, the error log is located at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files.

To cycle or force a new error log to be created you can restart your SQL server or truncate the error log using the Database Consistency Check (DBCC) command DBCC ErrorLog as in the example here:

Exec ('DBCC ErrorLog')

Exec Sp_Cycle_Errorlog


By default, the SQL Server error logs are kept in the SQL Server's Log subfolder. By default, there are seven SQL Server error logs; Errorlog and Errorlog.1 through Errorlog.6


Limit the number of the error log files before they are recycled
Check to limit the number of error logs created before they are recycled. A new error log is created each time an instance of SQL Server is started. SQL Server retains backups of the previous six logs, unless you check this option, and specify a different maximum number of error log files below.
Maximum number of error log files
Specify the maximum number of error log files created before they are recycled. The default is 6, which is the number of previous backup logs SQL Server retains before recycling them.To open the Configure SQL Server Error Logs dialog box
In Object Explorer, expand your server, expand Management, right-click SQL Server Logs, and then click Configure.

transaction log backup issue

In SQL Server 2005, you must perform a full database backup before you back up the transaction log for a database. Otherwise, when you try to back up the transaction log for a database, you receive the following error message:

Msg 4214, Level 16, State 1, Line 1 BACKUP LOG cannot be performed because there is no current database backup. Msg 3013, Level 16, State 1, Line 1 BACKUP LOG is terminating abnormally.

Additionally, messages that resemble the following are logged in the SQL Server Errorlog file:
2006-10-10 11:16:05.63 spid52 Starting up database 'SampleDatabase'. 2006-10-10 11:16:05.92 spid52 Setting database option RECOVERY to FULL for database SampleDatabase. 2006-10-10 11:16:06.41 Backup Error: 3041, Severity: 16, State: 1. 2006-10-10 11:16:06.41 Backup BACKUP failed to complete the command BACKUP LOG SampleDatabase. Check the backup application log for detailed messages.

In SQL Server 2000, if you try to back up a transaction log before you back up the full database, you only receive a warning message that resembles the following:

The CREATE DATABASE process is allocating 0.63 MB on disk 'SampleDatabase'. The CREATE DATABASE process is allocating 0.49 MB on disk ' SampleDatabase_log'. There is no current database backup. This log backup cannot be used to roll forward a preceding database backup. Processed 1 pages for database ' SampleDatabase ', file ' SampleDatabase_log' on file 1. BACKUP LOG successfully processed 1 pages in 0.145 seconds (0.007 MB/sec). Additionally, a successful backup is reported in the SQL Server Errorlog file. The text in the log file resembles the following:

2006-10-10 11:11:04.31 spid51 Starting up database ' SampleDatabase '. 2006-10-10 11:11:04.80 backup Log backed up: Database: SampleDatabase, creation date(time): 2006/10/10(11:11:04), first LSN: 8:23:1, last LSN: 8:25:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\TestTlog.trn'}).


The warning message indicates that you cannot use this transaction log backup for any restore operations if you do not have a full database backup.