Category Archives: Database Server

Recover the Corrupted database in SQL server 2000

Dear All
Today I got one SQL server 2000 database which is in Suspect mode so tried to attach it in my local server but getting error and not able to attach so tried following options to recover it
1) created one new database with same name
2) Stopped SQL server service
3) Replaced corrupted mdf and ldf with newly created database.
4) Started SQL server service
5) shown that database status as SUSPECT
6) Then opened QUERY ANALYSER and executed following commands one by one.

select status from sysdatabases where name = ‘mscit_era3’
update sysdatabases set status= 32768 where name = ‘mscit_era3’
sp_configure ‘allow updates’, 1
reconfigure with override
dbcc rebuild_log (‘mscit_era3’, ‘d:\mscit_era3_log.ldf’)
use mscit_era3
go
alter database mscit_era3 set SINGLE_USER
go
dbcc checkdb (‘mscit_era3’, repair_allow_data_loss)
go
use master
update sysdatabases set status= 0 where name = ‘mscit_era3’
DBCC CHECKALLOC (‘mscit_era3’)
dbcc checkdb (‘mscit_era3′)
alter database mscit_era3 set multi_user

Then lastly again restarted SQL server
and its done

Be sure the LDF file path is d:\mscit_era3_log.ldf’.

Thanks
Prashant Deshpande

1 Comment

Filed under Database Server, SQL Server

Get the Logical File name of SQL server Databases

Dear All

As there was a requirement of shrinking of LOG files  in SQL server and many times we just copy the old Database with new name and we never look for logical file of log file which is required while shrinking teh log file.

Syntax of Shrink database log file is

use Databasename
backup log Databasename with truncate_only
dbcc shrinkfile(“Logical File name “,1)
go

As we don’t know the Logocal File name the following command will help us to get the logical file names of all databases present in SQL server

Open Query Analyser and type

Use Master

select a.name, b.name as ‘Logical filename’, b.filename from sysdatabases a inner join sysaltfiles b on a.dbid = b.dbid where fileid = 2 

 

 Thanks

Prashant Deshpande

Leave a comment

Filed under Database Server, SQL Server

Recover Suspect SQL Server 2005 Database

Dear All

Please Read the follwoing documentation for

Recover Suspect SQL Server 2005 Database
Problem
one of our Digital university client’s SQL server  2005 database that has the wrong database status.  For some reason it is in the ‘Suspect\Offline’ mode.  And I have been told by project manager to check and correct the problem quickly and get the database back online.  How can I do so?   Once the status is correct, do I need to take any further steps? 

Solution

I have taken use of the script below to correct the SQL Server 2005 database status,.  We also need to correct any sort of corruption and understand why this occurred in the first place. 

Database Status Correction Script

The script below will change the database to be in simple recovery mode, which may or may not be the needed configuration for your database.  As such, it is necessary to review the database configurations once this script has been executed.  In addition, it is necessary to change the ‘DBName’ to your database name in single quotes.

EXEC sp_resetstatus ‘DBname’;

ALTER DATABASE DBname SET EMERGENCY

DBCC checkdb(‘DBname’)

ALTER DATABASE DBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB (‘DBname’, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE DBname SET MULTI_USER

Check for Corruption

The next step in this process is very key.  It is necessary to determine if the database has any corruption and ensure that the database will be able to support the users.  If the database has corruption, we may be exposing ourself to more issues by just changing the database status without correcting the underlying issue.  To identify the underlying issue, execute the following commands:

  • DBCC CHECKDB – Validate the overall database integrity
  • DBCC CHECKCATALOG – Validate the system catalog integrity
  • DBCC CHECKTABLE – Validate the integrity for a single table

To resolve the issue, we may need to do one or more of the following:

  • Drop and Recreate Index(es)
  • Move the recoverable data from an existing table to a new table
  • Update statistics
  • DBCC UPDATEUSAGE
  • sp_recompile

To ensure the issue is corrected, it is a good idea to re-run the identification commands listed above and validate that they do not have any issues. 

Thanks

Prashant Deshpande

Leave a comment

Filed under Database Server, Server Application, SQL Server

TempDB issues

Dear All

If we recieve a error message in Event log ” The log file for database ‘tempdb’ is full. Back up the transaction log for the database to free up some log space”

First thing we should check for disk space on which tempdb database database files are present .

If the disk space is enough ( not full or less)

Right click on Tempdb Database and in properties tab set the Space allocated to higer value for LOG file and see to it that it is given option to auto grow and recovery model is set to simple.
Actually when we restart SQL server Tempdb database is created from scratch but for that much time tranasactions will be stopped ( SQL server is stoped )

If we want to check for the oldest transaction taking tempdb space then we can use a technique to know the transaction ( SPID ) and we can kill it

DO the following steps in sequence

1) Open SQL query analyzer   ( type following commands )

<strong>Use tempdb</strong>

<strong>DBCC OPENTRAN(’tempdb’)</strong>
            Check the oldest transaction (if it returns any), and see who the SPID is (there will be a line starting with ‘SPID (Server Process ID) : <number>’).
Use that <number> in the following:

<strong>DBCC INPUTBUFFER(SPID number)</strong>

             This will tell you at least a portion of the last SQL command executed by this SPID, and will help you determine
If you want to end this process with:

<strong>KILL SPID number </strong>

Thanks

Prashant Deshpande

Leave a comment

Filed under Database Server, Server Application, SQL Server

Working With Linked servers in SQL SERVER 2000

Dear All

Please download and read the document whcih i have implemented on DU-webserver SQL server to serve the requiremnet of harsh and his clients.

Download document

Thnaks

Prashant Deshpande

Leave a comment

Filed under Database Server, Server Application, SQL Server

TEMPDB issue in SQL server

Dear All

Yesterday I have received call from DU team that they are getting error of TEMPDB

“The log file for database ‘tempdb’ is full. Back up the transaction log for the database to free up some log space.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.”

Here i would like to mention one note that tempdb is the very sensitive so if we do shrinking of log file it may cause data currption.

so i have used a trick , i have taken backup of tempdb on live server downloaded it on local system and attached and change the database mode in Simple and then given following command in Local SQL query analyser

 

 

 

DBCC SHRINKDATABASE tempdb TRUNCATEONLY

 

 

I got a message

“Server: Msg 2501, Level 16, State 1, Line 1 Could not find table named ‘1525580473’. Check sysobjects. “
after this I have taken backup of this local Tempdb of whcih size was reduced drastically

Again uploaded on live server and attached by stoping SQL server and then restarted SQL server of live server .

After this i got confirmation from DU team that now itw working properly and tranasactions are getting processed fastly.

Thanks

Prashant Deshpande

Leave a comment

Filed under Database Server, Server Application, SQL Server

Resolving Database IO is thawed Error

Dear All

On one of Live server following error has been shown in SQL server Activity Logs

So checked Event Viewer which was filled up of following Event

Event Source: SQLVDI
Event Category: None
Event ID: 1
Date:
Time:
User: N/A
Computer:
Description: SQLVDI: Loc=CVDS::Cleanup. Desc=Release(ClientAliveMutex). ErrorCode=(288)Attempt to release mutex not owned by caller. . Process=7100. Thread=3132. Client. Instance=MSSQLSERVER

and
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (6)
Event ID: 17055
Date:
Time:
User: NT AUTHORITY\SYSTEM
Computer:
Description:
3041 :
BACKUP failed to complete the command BACKUP DATABASE [msdb] TO VIRTUAL_DEVICE='{3E291B59-2DF7-4E2E-80B2-8B7993208DEB}5′ WITH SNAPSHOT,BUFFERCOUNT=1,BLOCKSIZE=1024

done googling but didn’t got solution so executed the command in start–> run –>
Regsvr32 Path\SQLVDI.DLL

This command is used to set the default path of SQLVDI.exe file.

then restarted the SQL server and the issue has been resolved

Thanks

Prashant Deshpande

Leave a comment

Filed under Database Server, SQL Server