Wednesday, June 20, 2012

Sometime, simple and basic problem can trap experts. This happened
last week with one of my senior, who was trying to generate some scripts and
saving output scripts to an output text file. On execution of these scripts
he found that for some queries text was not complete. He tried to get result in
text format but problem was same.

File output

If you are facing same basic problem of SQL Server
Management Studio then no need to worry, as you just need to make some changes
in SSMS options.

Thursday, June 7, 2012

Recently
we have found that our index defragmentation
job is failing on a production server, due to REORGANIZEfailure of one of our index. SQL Server was
unable to REORGANIZE this index because mistakenly we have an index with the ALLOW_PAGE_LOCKS options set to OFF

What
is Page Lock Option?

According
to BOL, If ALLOW_PAGE_LOCKS option is set to ON, it means Page locks are
allowed when accessing the index. The Database Engine determines when page
locks are used.

If ALLOW_PAGE_LOCKS option is set to off following query will return an error.

To make sure to avoid this
problem in futur for all datbases on a instance, Amna Asif has suggested following simple but efficient script to detect indexes with
ALLOW_PAGE_LOCKS option set to OFF and automatically fix this problem for all
of your databases on an instance.

/***********************************

Script By: Amna
Asif

Purpose : To fix
ALLOW_PAGE_LOCKS option on

all indexes of all databases
on a particular instance

***********************************/

USEMASTER

GO

SETQUOTED_IDENTIFIERON

GO

SETARITHABORTON

GO

DECLARE @DBName NVARCHAR(255)

DECLARE
@IndexCount int

DECLARE
@UpdateIndexQuery Varchar(500)

--- Table
variable to hold intermediate result set

DECLARE @IndexsInfo TABLE

(

RowNo intidentity(1,1),

DatabaseName varchar(100),

TableName varchar(100),

IndexName varchar(100)

)

--- Cursor to
work on each changeable index of each db on an instance

DECLARE DatabaseList CURSOR

FOR

SELECT Name

FROMsys.databases

WHERE state_desc ='ONLINE'

AND is_read_only = 0

ORDERBY name

OPEN DatabaseList

FETCHNEXTFROM DatabaseList INTO
@DBName

WHILE@@FETCH_STATUS= 0

BEGIN

INSERTINTO
@IndexsInfo(DatabaseName,TableName,IndexName)

EXEC('SELECT '''+@DBName+''' AS DatabaseName,TABL.NAME AS TableName,indx.NAME AS
IndexName

Tuesday, June 5, 2012

One
can check integrity issues by using DBCC CHECKDB (“DatabaseName”) and unfortunately
if DBCC CHECKDB returns some type of database corruption, then it can be solved
by executing DBCC CHECKTABLE. According to BOL “DBCC
CHECKTABLE: Checks the integrity of all the pages and structures that make up
the table or indexed view.”

Most
of the time it works fine for me at least, but last week it returned a
different error and failed to fix it.

Error message
clearly showing that there is no actual corruption for said page. You can call
it just information that PFS (Page Free Space) entry has wrong calculation for
free space in page. Though PFS showing that page is empty (0%), but in reality
page is full (100%). And it is misleading free-space scanner, which finds it
full when try to insert data.

You can live happily
with this error, but if you have applied a job to execute DBCC CHECKDB then you
will keep on receiving job failure notice.

How to resolve it:

Three methods,
first, you should restore database from latest backup, which is error free. And
second method is to create a replica of culprit table, insert data into it,
delete existing (culprit table) and start enjoying new error free table.

(Once data copied
to new table and verified, don’t forget to check further errors by executing
DBCC CHECKDB.

Before trying above
two methods first go for third one. i.e. DBCC PAGE

Friday, June 1, 2012

If you call yourself SQL Server DBA, then you
must be able to interpret SQL Server Log, One of a common information message
captured by SQL Server Log is

This instance of
SQL Server has been using a process ID of 2308 since 5/3/2012 2:30:52 AM
(local) 5/3/2012 6:30:52 AM (UTC). This is an informational message only; no
user action is required.

(process id and time would be different every time)

First thing to note about this log entry is
that it’s just an information message and no user action is required. Mean NO
NEED TO WORRY. This is just an information message that SQL Server instance
using a process id (in my case it is 2308), since SQL Server services are
started (in my case SQL Server services are started at 5/3/2012 2:30:52 AM)and after a month instance is still running.

SQL Server creates a log entry for this
message on each date change. So you can find one entry for each 24 hours.

To verify this process id, open Task Manager
and move to “Processes” tab, click on “View” in menu, “Select Columns”. Select
PID (Process Identifier). Now it will start showing process identifier for each
process. Check process identifier for sqlserver.exe

Translate

About Me

Aasim Abdullah is working as SQL Server DBA with CureMD (www.curemd.com) based in NY, USA. He has been working with SQL Server since 2007 (Version 2005) and has used it in many projects as a developer, administrator, database designer. Aasim's primary interest is SQL Server performance tuning. If he finds the time, he like to sketch faces with graphite penciles.