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

Ben Conner
Ben Conner used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan McCauleySenior Data Architect

Commented:
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?
Ben ConnerCTO, SAS developer

Author

Commented:
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
Ben ConnerCTO, SAS developer

Author

Commented:
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
Senior Data Architect
Commented:
If your SQL Service is crashing when you run that command in both SQLCMD and QA/SSMS, then there's something wrong on the server. It's possible the master database could be corrupted - what happens when you run the command on another database instead:

sqlcmd -u sa -p xxx -d model -q "dbcc checkdb(foundation)" 

Open in new window


That will run it on the model database instead of master - does that also crash your service? If so, then I suspect there's a service problem and I'd recommend uninstalling/reinstalling. If it doesn't crash, then you may have a problem in the master database, and there's longer troubleshooting for that (assuming you don't have a good backup).
Ben ConnerCTO, SAS developer

Author

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial