Emi975
asked on
Cannot truncate a large table in SQL Server 2005
I am currently having a problem with a server that does performance/activity monitoring for systems that exists in our environment. One of the tables in SQL server does not respond and is not critical. I am trying to truncate the table, but I keep getting 845 errors in SQL. (Time Out occurred while waiting for buffer latch type 3 and type 4. The table has 18 million records, and I cannot select the top 2 records, to view anything in the table. The table does not have any foreign keys permitting the data to be deleted. The database is 14 gigs that sat on a SQL Server 2005 box with 4 gigs of RAM on a 32 bit system and was copied over to a virtual SQL box that was 64 gigs with 10 gigs of ram with the same errors coming out.
Just don't understand why I am not able to delete all contents of the table using a truncate statement.
Just don't understand why I am not able to delete all contents of the table using a truncate statement.
DBCC checkdb(Solar)
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.
Msg 845, Level 17, State 1, Line 1
Time-out occurred while waiting for buffer latch type 4 for page (1:1169), database ID 17.ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
also you may went out of memory ..
reboot may help ,,,,
reboot may help ,,,,
If you are trying to truncate the table means that you don't need the records?
If not then why not drop the table and recreate it after?
If not then why not drop the table and recreate it after?
ASKER
EugeneZ: 10 gigs of ram on the new server didn't help. Reboot didn't help.
VMontalvao: I can try dropping the table and recreating it. Question, when I click on indexes, rebuild all, it gives me errors on the rebuild of the index. "Lock request time out period exceeded. error 1222
VMontalvao: I can try dropping the table and recreating it. Question, when I click on indexes, rebuild all, it gives me errors on the rebuild of the index. "Lock request time out period exceeded. error 1222
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks gang
Emi975: what was the solution?
ASKER
ALTER DATABASE Solar SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (Solar , REPAIR_ALLOW_DATA_LOSS)
These are the error messages I am still getting
Msg 8930, Level 16, State 3, Line 1
Database error: Database 17 has inconsistent metadata. This error cannot be repaired and prevents further DBCC processing. Please restore from a backup.
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.