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.
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.

Open in new window

Emi975Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Emi975Author Commented:

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.
0
Emi975Author Commented:
Can't Re-Index the table either. Doesn't look like maintenance was done on this box
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
check if you havw free space on this box

you may need to restore this DB - looks like you got corruption

check EV logs for HWW errors
you may need to contact vendor for fresh FW drives
also check sql2005 service pack frshness
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
also you may went out of memory ..
reboot may help ,,,,
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
Emi975Author Commented:
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
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
<10 gigs of ram on the new server didn't help. >
how much for sql server?
-------------------------------------

---

if you have a good backup
you need to try to restore over existing DB
--
also check with your vendor for firmware update
---
About the error 1222 solution
http://msdn.microsoft.com/en-us/library/aa337412.aspx

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

about the 1 bad table:
if you have problem with just 1 table
and have no problem drop it

--Drop it and run dbcc checkdb tt see if there are more corruption, post result

--please clarify this: can you or can not truncate/delete data?

<Just don't understand why I am not able to delete all contents of the table using a truncate statement>
<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 >



if you need truncate:
,rename old one,
recreate new table and drop old one-----
---

also you did not post what serverce pack /sql edition do you have..

was this DB upgraded from sql 2000? did it work before
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can try to reindex table by table to find the "bad" table.
EXEC sp_MSforeachtable @command1='DBCC DBREINDEX (''*'')', @replacechar='*'

Open in new window


0
dwkorCommented:
DBCC CHECKDB uses hidden database snapshot by default. You can change this behavior by using WITH TABLOCK option although it requires (temporary) X lock on the database e.g. use single user mode. Also in this mode CHECKCATALOG is not executed. Also make sure that TEMPDB has enough free space to grow

If DBCC CHECKDB failed with "inconsistent metadata error" even in this mode, it means system table are corrupted. Your best bet would be either restore from the last good backup or contact Microsoft CSS team.



0
Emi975Author Commented:
Thanks gang
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
Emi975:  what was the solution?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.