Monthly Archives: March 2010

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

Business Objects Installation issue

Dear All

I was trying to Install Business Objects on windows 2003 server and i was getting error “Error 1920. Service ‘Crystal Reports Cache Server'(BOBJCrystalReportsCacheServer) failed to start. Verify that you have sufficient privileges to start system services” so i have tried to install Business objects on different system but getting same error.

Solution :- These error messages appear because of a new feature called Data Execution Prevention (DEP). DEP is a set of hardware and software technologies that perform additional checks on memory to help prevent malicious code from running on a system.
To add services to the DEP feature so the services can run:

Right-click ‘My Computer’ and click ‘Properties’.
Under the ‘Advanced’ tab in the ‘Performances’ section, click ‘Settings’.
Under the ‘Data Execution Prevention’ tab click the ‘Add’ button
and specify the teh service file name along with Path

Thanks

Prashant Deshpande

Leave a comment

Filed under Business Objects, Operating System

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