Link to home
Start Free TrialLog in
Avatar of Ben Conner
Ben ConnerFlag for United States of America

asked on

Using dbcc checkdb() in sqlcmd in SQL Server 12 kills server

Hi,

I am testing the SQL Server 12 Express version and am experimenting with the sqlcmd.exe facility.  I can get it to back up databases and thought I would extend the input script file to include a database check (dbcc checkdb(...)  ).

As soon as I add that to the script file and run it via sqlcmd, the SQL Server service immediately terminates.  Can someone verify this with another copy of SQL 12?  

I'd recommend not trying it on a production server. ;-)

Thanks!

--Ben
Avatar of Ryan McCauley
Ryan McCauley
Flag of United States of America image

I don't have a copy of SQL 2012 Express handy, but I just confirmed on Developer edition that I can execute "dbcc checkdb()" via SQLCMD and it returns the results just fine (faster than SSMS, actually).

So confirmed that there's no issue here, though I can't try it on your edition. Can you connect via Management Studio and confirm that the command works there, or does it also crash your instance? Also, is this an upgrade or a fresh install? Any other details that you think might be relevant?
Avatar of Ben Conner

ASKER

This was a fresh install on a new Virtual Machine.  One variation I hadn't tried yet is the in-line dbcc (I had used the -i <full path to file> option.  I can't connect to it over the lan as my workstation is XP and the management package refuses to install on XP.  

My testing was done via RDC to that VM.  Command was in a checkdb.sql file and I had run

sqlcmd -i d:\sys\checkdb.sql -o checkout.txt

The contents of the checkout.txt file was:
Shared Memory Provider: The pipe has been ended.

Communication link failure

And sure enough, no sql server service was running.  I'll have to keep that in mind next time if it hangs when I try to stop it. :)

The command itself (within the .sql file) was:
dbcc checkdb(foundation)
go

Will try it later tonight to see what happens.

--Ben
ok, using the command:

sqlcmd -u sa -p xxx -q "dbcc checkdb(foundation)" also drops the server.

Also tried it from the SQL Query Analyzer vsn 8, same result.

--Ben
ASKER CERTIFIED SOLUTION
Avatar of Ryan McCauley
Ryan McCauley
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sorry, didn't get any notification from EE that there was a response...

Good call.  It's only the Foundation DB that crashes the server.  Interesting.  Was able to run checkdb against other system and user databases.  Wonder what would cause the system to tank on this one.  Hm.

The good news is I can recreate it from another SQL 2008 copy.  And it's a test db anyway.  

I'll drop/recreate it and see if the problem goes away.

Thanks much!

--Ben