DBCC IN MSSQL SRVER

DBCC TRACEON(2520)

DBCC HELP ('?')

DBCC HELP(checkcatalog)

DBCC inputbuffer (spid)

DBCC perfmon

1.DBCC CHECKALLOC

DBCC CHECKALLOC checks page usage and allocation in the database. Use this command if allocation errors are found for the database. If you run DBCC CHECKDB, you do not need to run DBCC CHECKALLOC, as DBCC CHECKDB includes the same checks (and more) that DBCC CHECKALLOC performs.

2.DBCC CHECKCATALOG

This command checks for consistency in and between system tables. This command is not executed within the DBCC CHECKDB command, so running this command weekly is recommended.

3.DBCC CHECKCONSTRAINTS

DBCC CHECKCONSTRAINTS alerts you to any CHECK or constraint violations.

Use it if you suspect that there are rows in your tables that do not meet the constraint or CHECK constraint rules.

4.DBCC CHECKDB

A very important DBCC command, DBCC CHECKDB should run on your SQL Server instance on at least a weekly basis. Although each release of SQL Server reduces occurrences of integrity or allocation errors, they still do happen. DBCC CHECKDB includes the same checks as DBCC CHECKALLOC and DBCC CHECKTABLE. DBCC CHECKDB can be rough on concurrency, so be sure to run it at off-peak times.

5.DBCC CHECKTABLE

DBCC CHECKTABLE verifies index and data page links, index sort order, page pointers, index pointers, data page integrity, and page offsets. DBCC CHECKTABLE uses schema locks by default.

6.DBCC CHECKFILEGROUP

DBCC CHECKFILEGROUP works just like DBCC CHECKDB, only DBCC CHECKFILEGROUP checks the specified filegroup for allocation and structural issues. If you have a very large database (this term is relative, and higher end systems may be more apt at performing well with multi-GB or TB systems ) , running DBCC CHECKDB may be time-prohibitive.

If your database is divided into user defined filegroups, DBCC CHECKFILEGROUP will allow you to isolate your integrity checks, as well as stagger them over time.

7.DBCC CHECKIDENT

DBCC CHECKIDENT returns the current identity value for the specified table, and allows you to correct the identity value if necessary.

8.DBCC DBREINDEX

If your database allows modifications and has indexes, you should rebuild your indexes on a regular basis. The frequency of your index rebuilds depends on the level of database activity, and how quickly your database and indexes become fragmented. DBCC DBREINDEX allows you to rebuild one or all indexes for a table. Like DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC, running DBREINDEX during peak activity times can significantly reduce concurrency.

DBCC DBREINDEX ('Person.Contact','PK_Contact_ContactID',80)

9.DBCC INDEXDEFRAG

Microsoft introduced the excellent DBCC INDEXDEFRAG statement beginning with SQL Server 2000. This DBCC command, unlike DBCC DBREINDEX, does not hold long term locks on indexes. Use DBCC INDEXDEFRAG for indexes that are not very fragmented, otherwise the time this operation takes will be far longer then running DBCC DBREINDEX. In spite of it's ability to run during peak periods, DBCC INDEXDEFRAG has had limited effectiveness compared to DBCC DBREINDEX (or drop/create index).

10.DBCC INPUTBUFFER

The DBCC INPUTBUFFER command is used to view the last statement sent by the client connection to SQL Server. When calling this DBCC command, you designate the SPID to examine. (SPID is the process ID, which you can get from viewing current activity in Enterprise Manager or executing sp_who. )

11.DBCC OPENTRAN

DBCC OPENTRAN is a Transact-SQL command that is used to view the oldest running transaction for the selected database. The DBCC command is very useful for troubleshooting orphaned connections (connections still open on the database but disconnected from the application or client), and identification of transactions missing a COMMIT or ROLLBACK. This command also returns the oldest distributed and undistributed replicated transactions, if any exist within the database. If there are no active transactions, no data will be returned. If you are having issues with your transaction log not truncating inactive portions, DBCC OPENTRAN can show if an open transaction may be causing it.

12.DBCC PROCCACHE

You may not use this too frequently, however it is an interesting DBCC command to execute periodically, particularly when you suspect you have memory issues. DBCC PROCCACHE provides information about the size and usage of the SQL Server procedure cache.

13.DBCC SHOWCONTIG

The DBCC SHOWCONTIG command reveals the level of fragmentation for a specific table and its indices. This DBCC command is critical to determining if your table or index has internal or external fragmentation. Internal fragmentation concerns how full an 8K page is.

When a page is underutilized, more I/O operations may be necessary to fulfill a query request than if the page was full, or almost full.

External fragmentation concerns how contiguous the extents are. There are eight 8K pages per extent, making each extent 64K. Several extents can make up the data of a table or index. If the extents are not physically close to each other, and are not in order, performance could diminish.

14.DBCC SHRINKDATABASE

DBCC SHRINKDATABASE shrinks the data and log files in your database.

Avoid executing this command during busy periods in production, as it has a negative impact on I/O and user concurrency. Also remember that you cannot shrink a database past the target percentage specified, shrink smaller than the model database, shrink a file past the original file creation size, or shrink a file size used in an ALTER DATABASE statement.

15.DBCC SHRINKFILE

DBCC SHRINKFILE allows you to shrink the size of individual data and log files. (Use sp_helpfile to gather database file ids and sizes).

16. DBCC TRACEOFF, TRACEON, TRACESTATUS

Trace flags are used within SQL Server to temporarily enable or disable specific SQL Server instance characteristics. Traces are enabled using the DBCC TRACEON command, and disabled using DBCC TRACEOFF. DBCC TRACESTATUS is used to displays the status of trace flags. You'll most often see TRACEON used in conjunction with deadlock logging (providing more verbose error information).

17.DBCC USEROPTIONS

-3

Execute DBCC USEROPTIONS to see what user options are in effect for your specific user connection. This can be helpful if you are trying to determine if you current user options are inconsistent with the database options.

18.CLEANTABLE - Reclaims space from the dropped variable-length columns in tables or index views.
DBCC CLEANTABLE ('AdventureWorks','Person.Contact',0)

2. DBREINDEX - Builds one or more indexes for the table in the specified database.
(Will be removed in the future version, use ALTER INDEX instead) USE AdventureWorks

DBA CheckList

Database Administrators can sometimes have one of the most stressful jobs in the company. If you have been a DBA for long, you know the scenario. You have just sat in your chair with your cup of coffee, and your phone starts ringing off the hook. The voice on the other end states that they can't pull up their data or they are getting timeouts, or the system is running slow. Okay, time to dig in; it's going to be one of those days! Is it Friday yet? In this article, I will present ways to minimize those stressful days by having a pre-defined DBA morning checklist. A morning DBA checklist is a document of pre-defined administrative checks that are performed every morning to ensure that your server is at optimal performance. By having a standard list of items to check, you are more likely to catch and fix issues before there is a real problem. The end result of the morning DBA checklist should have three sections.

Section one contains the list of items that need checked.

Section one should include checks from the following categories: performance, job failures, disk space, backups, connectivity, and anything specific to your environment, such as replication, mirroring, clustering, etc. Section two contains a place to write down issues and how they were resolved. The third section is a confirmation section where it is signed and dated. The third section is very important. Without this section, it is difficult to enforce and guarantee that these checks were performed. The first step to create an effective morning checklist is to meet with all the DBAs and ask them these questions:

1. What do you check in the morning?

2. How do you check it?

3. What do you do when there is a problem?

4. Is there anyone you notify in the event of a failure? In my experience,

every DBA has his own mental checklist and different ways that he / she fix issues. It is important to get a list of the items written down in a document. By combining the ideas of every DBA, you will come up with a more thorough checklist, a standardized way to fix issues, and problems are less likely to fall through the cracks. After the DBA morning checklist is created, completed checklists should be archived in a notebook to ensure that each check was performed every day. This also serves as a history of fixes for past issues, and an audit trail for the DBA.

Since every database environment is different, and every IS shop has its own tools, every DBA's checklist will be different. The end goal is to create a checklist that is customized to your environment, in which issues can be found and fixed quickly, so that you can avoid having one of those difficult days.

With this in mind, listed below is a sample checklist. Your checklist should be unique to your environment and should help find and fix issues as quickly as possible.

DBA Morning Checklist

Backups – Verify that the Network Backups are good by checking the backup emails. If a backup did not complete, contact _____ in the networking group, and send an email to the DBA group. –

Check the SQL Server backups. If a backup failed, research the cause of the failure and ensure that it is scheduled to run tonight. –

Check the database backup run duration of all production servers. Verify that the average time is within the normal range. Any significant increases in backup duration times need to be emailed to the networking group, requesting an explanation. The reason for this is that networking starts placing databases backups to tape at certain times, and if they put it to tape before the DBAs are done backing up, the tape copy will be bad. – Verify that all databases were backed up. If any new databases were not backed up, create a backup maintenance plan for them and check the current schedule to determine a backup time.

Disk Space – Verify the free space on each drive of the servers. If there is significant variance in free space from the day before, research the cause of the free space fluctuation and resolve if necessary. Often times, log files will grow because of monthly jobs.

Job Failures – Check for failed jobs, by connecting to each SQL Server, selecting "job activity" and filtering on failed jobs. If a job failed, resolve the issue by contacting the owner of the job if necessary.

System Checks – Check SQL logs on each server. In the event of a critical error, notify the DBA group and come to an agreement on how to resolve the problem. – Check Application log on each server. In the event of a critical or unusual error, notify the DBA group and the networking group to determine what needs to be done to fix the error.

Performance – Check Performance statistics for All Servers using the monitoring tool and research and resolve any issues. – Check Performance Monitor on ALL production servers and verify that all counters are within the normal range. Connectivity – Log into the Customer application and verify that it can connect to the database and pull up data. Verify that it is performing at an acceptable speed. In the event of a failure, email the Customer Support Group, DBA group, and the DBA manager, before proceeding to resolve the issue. – Log into the Billing application and verify that it can connect to the database and pull up data. Verify that it is performing at an acceptable speed. In the event of a failure, email the Billing Support Group, DBA group, and the DBA manager, before proceeding to resolve the issue.

Replication – Check replication on each server by checking each publication to make sure the distributor is running for each subscription. – When replication is stopped, or changes to replication are made, send an email to the DBA group. For example, if the DBA stops the distributor, let the other DBAs know when it is stopped and then when it is restarted again. – Check for any emails for the SQL Jobs that monitor row counts on major tables on the publisher and subscriber. If a wide variance occurs, send an email message to the DBAs and any appropriate IS personnel.

Section 2: Write down any issues and how they were resolved

This space is reserved for writing down issues and how they were fixed.

Section 3 – Confirmation

Completed By __________________________ Date: ___________________

Conclusion

Creating a morning DBA checklist has helped me many times in the past.

Often times, I found CPU usage up near 100%, broken replication, connectivity problems, and space issues that I have been able to resolve before the majority of the work force was present and the issue could escalate.

By having a standard DBA checklist document, it ensures that nothing is forgotten, which could result in a problem. It also minimizes down time of a company or department, provides a archive of past issues and how they were fixed, and helps ensure that the DBA will have a less stressful day!

Installation of SQL Server 2012 on Windows Server 2012

Mount the CD and RUN the Setup file

You will the face the issue

Once Every thing went smoothly also we will face the issues. Find the below

Failed Because of 3.5 Dotnet framework as to install, For that we can enable the feature .

Even though it failed find the below

It fails again,

then needs to follow one more step see below, Insert windows OS CD and then follow steps

Click OK and Install

Then again to install SQL Server 2012 and Lunch the SQL Server – alone installation

Next and Install

===========================================================================================================

SQL SERVER DATABASE

SQL Server Services

SQL Server VSS Writer

The SQL Writer Service provides added functionality for backup and restore of SQL Server through the Volume Shadow Copy Service framework.
The SQL Writer Service is installed automatically.
It must be running when the Volume Shadow Copy Service (VSS) application requests a backup or restore.

Purpose

When running, Database Engine locks and has exclusive access to the data files. Suppose the SQL Writer Service is not running,

Backup programs running in Windows do not have access to the data files, and backups must be performed using SQL Server backup.

Use the SQL Writer Service to permit Windows backup programs to copy SQL Server data files while SQL Server is running.

MSSQLServerADHelper Service

It adds and removes the objects used to register instances of relational database engine or Analysis server in the Active Directory
It ensures that the Windows account under which a SQL Server service is running has permissions to update all of the Active Directory objects for the instance, as well as any replication publications and databases for that instance.
There is only one MSSQLServerADHelper service on a computer. The single service handles the Active Directory objects for all instances of the SQL Server relational database engine and all Analysis Manager applications running on the computer.

Full-Text Search (SQL Server)

Full-Text Search in SQL Server lets users and applications run full-text queries against character-based data in SQL Server tables.
Before you can run full-text queries on a table, the database administrator must create a full-text index on the table.
The full-text index includes one or more character-based columns in the table. These columns can have any of the following data types:char, varchar, nchar,nvarchar, text, ntext, image, xml, or varbinary(max) and FILESTREAM. Each full-text index indexes one or more columns from the table, and each column can use a specific language.

MiKro Data

Pages

Administrating Microsoft SQL Server