Author Archives: Jugal Shah

Unknown's avatar

About Jugal Shah

Jugal Shah has 19 plus years of experience in leading and managing the data and analytics practices. He has done the significant work in databases, analytics and generative AI projects. You can check his profile on http://sqldbpool.com/certificationawards/ URL.

DBCC PAGE

Leave a reply

 DBCC PAGE 

We can use the undocumented DBCC PAGE command to view the page header, data rows, and row offset table for any data page in a database. You have to turn on traceflag 3604 before running this command.  You can also use this command when you find the IO bottleneck and query is waiting Page Latches.

Permission: System Admin rights

Syntax

dbcc page ( {‘dbname’ | dbid}, file number, page number [, print opt={0|1|2|3} ])

 You can pass the below print option parameter as per the requirement

  • 0 – print just the page header
  • 1 – page header plus per-row hex dumps and a dump of the page slot array (unless it’s a page that doesn’t have one, like allocation bitmaps)
  • 2 – page header plus whole page hex dump
  • 3 – page header plus detailed per-row interpretation

Trace Flag – 3604 Trace flag 3604 is the most commonly used trace flag. It sends the output of a trace to the client. For example, before you can run DBCC PAGE, which views data page information, you must run this trace flag. 

DBCC traceon(3604)

DBCC PAGE ( {dbid | dbname}, filenum, pagenum [, printopt] [, cache] ) 

The output from DBCC PAGE is divided into four main sections: Buffer, Page Header, Data, and Offset Table (really the Offset Array).

Shrink Log File

3 Replies

Just a few days back I received an issue regarding disk size is out of space. I have tried to shrink the database using DBCC ShrinkFile but there isn’t any success.

I have follow below steps to resolve this issue and it really works.

I have check the below query for

Select name,log_reuse_wait_desc from sys.databases

  1. There was another database which is waiting log backup to release the space
  2. There was one database who is waiting for Replication to release the space

Database who is waiting for log backup to release the log space

I have taken the log backup two times and executed the database shrink file command to release the space and it has reclaimed 40GB space from transaction log file.

Database who is waiting for Replication to release the log space

This database is in Simple recovery mode and there isn’t any replication enable on this. I have executed the DBCC OPENTRAN command to see any active transaction. I have executed the DBCC OpenTran and it has provided me the below result.

Oldest active transaction:

    SPID (server process ID): 101

    UID (user ID) : -1

    Name          : INSERT

    LSN           : (999:138204:2)

    Start time    : OCT  13 2009  1:34:47:827PM

    SID           : 0x88d52e4051a71143adee5dc7b6619f8a

Replicated Transaction Information:

        Oldest distributed LSN     : (890:2091888:1)

        Oldest non-distributed LSN : (896:2784855:1)

I don’t know the exact reason what happened internally. But from the output it seems that there is unmark distributed transaction. So I have executed Sp_Repldone command to unmark the LSN

 

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,     @time = 0, @reset = 1

 Than I have executed the DBCC ShrinkFile command and it has reclaimed the 400GB space

An error has occurred while establishing a connection to the server

Leave a reply

Problem:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 – Server doesn’t support requested protocol) (Microsoft SQL Server, Error: -1)

Resolution:

Check the below setting at Server Level
1. Check the firewall for any blocking (i.e TCP/IP Port:1433)
2. Check the SQL Server Instance Name, there may be chances of the spelling mistakes
3. May be remote connection is disable. Use the SAC (SQL Server Surface area configuration tool for remote connection)
4. Check the status of SQL Browser Service, it should be running to allow remote connectivity

SQL Server Security Interview Questions

Leave a reply

During the interview I always like to ask below security questions to DBA. I found that sometimes DBAs are not able to answer these questions.

Question 1: What will you do if you lost rights of your SQL Server instance?

We can use the below options

  1. Dedicated Administrator Connection
  2. BUILIN\Administrator Group (Incase its rights are not revoked)
  3. Final Option is to change the registry value
  4. You can change authentication mode via registry 

Question 2: – What is SQL Injection?

SQL Injection is developed where unhandled\unexpected SQL commands are passed to SQL Server in a malicious manner.  It is a problem because unknowingly data can be stolen, deleted, updated, inserted or corrupted. 

Question 3: – What is the Guest user account in SQL Server?  What login is it mapped to it?   

The Guest user account is created by default in all databases and is used when explicit permissions are not granted to access an object.  It is not mapped directly to any login, but can be used by any login.  Depending on your security needs, it may make sense to drop the Guest user account, in all databases except Master and TempDB 

Question 4: – What is the use of BUILTIN\Administrators Group in SQL Server?

Any Windows login in BUILTIN\Administrators group is by default a SQL Server system administrator. This single group can be used to manage administrators from a Windows and SQL Server perspective