February 22, 2012 at 4:37 pm
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
February 22, 2012 at 7:23 pm
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
February 23, 2012 at 12:30 am
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" 😉
February 23, 2012 at 11:53 pm
February 24, 2012 at 10:42 am
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.
February 24, 2012 at 11:24 am
This may help you to find your answer.
February 28, 2012 at 12:18 pm
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!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply