November 29, 2007 at 1:27 pm
Hi Folks,
First off, I am not a SQL DB but have been charged with getting an answer to the issue I'm about to describe.
Under our databases branch in EP we have a suspect database. It turns out that when we ran out of disk space the DA decided to stop the SQL services, rename the table of the large file, and then create a new one that was empty.
Since then we cannot get the DB back from the suspect status.
What we need to do is shrink the extremely large DB so that it is not nearly as large and then archive it for deletion later.
Unfortunately we cannot get the DB out of suspect mode. We've tried going into single user mode and using the status change command but everytime we go in as 'sa' user it says only one user can be in at time. We do not know who this user could be!
Any ideas would be helpful! Thank you.
Chris
November 30, 2007 at 12:12 am
I think you have manupulated the mdf file. This should be done in any case that is the reason for DB to be in suspect.
Is there any recent full DB backup try to restore that in a different server
with sufficient disk space.
About this suspect DB.
try to do the following steps:
1) Add enough disk space to the system
2) keep a copy of the mdf & ldf file
3) exec sp_who to check the users connected disconnect all of them except sa
3) exec sp_resetstatus 'dbname' -> to bring the database status to 0
) Exec sp_configure 'allow updates',1 reconfigure with override
5) update sysdatabases set status = 32768 where name ='dbname'
6) Run the dbcc checkDB if everything is OK then BCP out the data to the new server.
Hope this helps 🙂
"More Green More Oxygen !! Plant a tree today"
November 30, 2007 at 2:32 am
could be the SQL Server Agent connecting to the database?
Log into the server where you are running SQL. Start SQL Server Management Studio and log in. Right-click the SQL Server Agent node and select 'Stop'.
Also - the extremly large file - does it end in .ldf or .mdf?
November 30, 2007 at 7:11 am
Thanks for the replies!
We ended up detaching the db file from the database list, refreshing the Enterprise Manager db window so that the suspect DB was no longer there, and then reattaching the db to list.
After that we used the information on post http://www.sqlservercentral.com/Forums/Topic225600-5-3.aspx
USE your_database
GO
CHECKPOINT
GO
DBCC SHRINKFILE (your_database_log, 1) -- this needs to be the name of the log_file from EM
This dropped our log file to 1 mb. We then put a 5 gb max threshold on the log file and have scheduled daily backups of the DB.
Thanks again for the replies. Our initial thought was to use the delivered backup and truncate procedures but we had a log file of 189 gb and only 120 mb of space left in the system!
Time to find a new DBA. 🙂
December 2, 2007 at 1:15 pm
One more thing to check - is the database in FULL recovery mode? If so, your log file will continue to grow unless you take log backups...
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply