Showing posts with label SQL server. Show all posts
Showing posts with label SQL server. Show all posts

Monday, March 16, 2009

Database log file size issue

Recently I faced a problem where my SQLServer 2005 ran out of space for log files. Usually the log files can be reduced by shrinking them but this time the transaction logs were so much huge that I was not even allowed to run shrink log command.

My DB was in Recovery phase.

My friend Dagupati ( a professional DBA ) helped me getting out of this trouble.

Following are the steps to be followed in sequence to achieve this.

1) Take the DB into emergency mode.

ALTER DATABASE yourDBname SET EMERGENCY
2) Bring DB to single user mode.

ALTER DATABASE yourDBname SET SINGLE_USER

3)Repair you DB

DBCC CheckDB ('yourDBname', REPAIR_REBUILD)

if the above command fails, try this

DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)

NOTE : This may result in dataloss

4) Bring you DB to multi user mode

ALTER DATABASE yourDBname SET MULTI_USER

By now you should have your DB up & running.

DB at times enter into SUSPECT mode - restarting server should solve them if not follow the above steps.

To be on safer side take full / transactional backups regularly

Tuesday, December 16, 2008

Rare issue while deleting full text catalog in SQL server 2005

select * from sysfulltextcatalogs

This Lists all the full text catalogs present for the selected table. This table contains the catalog name and the path where these are physically stored.

In case of broken index or to delete a catalog after restoring database form an external source, update the path to a physical drive present in server. ( These steps to be followed only when the there is mismatch of catalog path in the new server or the SUBST command has failed )

update dbo.sysfulltextcatalogs set path='D:\Full Text Catalog' where ftcatid=X

by default update on sysfulltextcatalogs table is disabled, run the following script to enable this.

exec sp_configure 'allow updates',1
go
reconfigure with override