Ben Conner
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
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
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
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
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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?