Learning SQL admin and need help with checkdb

Question

Post reply

Learning SQL admin and need help with checkdb

dbrackin

Grasshopper

Points: 22
More actions
February 22, 2012 at 4:37 pm

#268692

I am learning SQL admin, took online classes and studing books and on the job. We have a new 2008 sql R2 standard database I set up and a vendor loaded the data tables. There are thousands of tables in the database. This is the test environment setup and Live data will be added to a seperate database in the same default instance. My concern is the first time I ran DBCC CHECKDB I choose to run it on this server (it is test data). That was 2 days, 4 hours and 40 mins ago- and it is still running. So, how much longer do I give it to run? And what happens if I kill this transaction and how would I do this. I wouldn't be concerned but we do have to complete testing as we are on a schedule for going live. There are no DBA's that work with me to consult with (which probably would have been nice BEFORE). Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sports Nut SSC Enthusiast Points: 111 More actions · Answer 1

Paul Randall is the daddy of DBCC CheckDB (he wrote it). Check out his posts.

http://sqlskills.com/BLOGS/PAUL/category/CHECKDB-From-Every-Angle.aspx

It will run as long as it needs to depending on many factors, size of database, tables, indexes, CPU, RAM disk subsystem etc.

Check out Pauls articles it will explain a lot

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

dbrackin (2/22/2012)
I am learning SQL admin, took online classes and studing books and on the job. We have a new 2008 sql R2 standard database I set up and a vendor loaded the data tables. There are thousands of tables in the database. This is the test environment setup and Live data will be added to a seperate database in the same default instance. My concern is the first time I ran DBCC CHECKDB I choose to run it on this server (it is test data). That was 2 days, 4 hours and 40 mins ago- and it is still running. So, how much longer do I give it to run? And what happens if I kill this transaction and how would I do this. I wouldn't be concerned but we do have to complete testing as we are on a schedule for going live. There are no DBA's that work with me to consult with (which probably would have been nice BEFORE). Thanks

What are the current size stats of this database?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 3

Did u specify an options when running CHECKDB

Jayanth Kurup[/url]

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 4

How big is the database? Is the CPU pegged?

Find what SPID it is running and do an SP_WHO2 54

where 54 is the specific SPID number. You can see if it is actually doing anything if you keep running that... IO, CPU or it if it is being blocked by another SPID.

sengeet86 Ten Centuries Points: 1252 More actions · Answer 5

This may help you to find your answer.

http://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/determining-how-long-it-takes-for-dbcc-checkdb-to-run-137119

java programming

dbrackin Grasshopper Points: 22 More actions · Answer 6

Here is the script I ran:

ALTER DATABASE mytestdb

SET SINGLE_USER;

GO

DBCC CHECKDB ('mytestdb', REPAIR_REBUILD)

WITH EXTENDED_LOGICAL_CHECKS;

GO;

The database has about 7000 tables and around 4GB. AMD Opeteron Proc 6180 SE 2.5 GHz (2 procs)

8.21 GB Ram 32 bit OS

I had to stop checkdb on the 3 day when I saw no data could be added, probably because I set single_user. I am still reviewing all the resources that have been given me and I appreciate the assistance. I do want to be able to run this command again in a syntax that will be useful. I should have a development vs of SQL set up soon and it might be a better place to run commands the 1st time!