I Win.

The most impossible table to deal with was using 63Gb of hard disk space for indexes and 96Gb of space for data.

MS SQL Server was giving me I/O errors and complaining about torn pages.  The SHRINKDATABASE command was just off in some useless cycle that wouldn’t respond.  Trying to CHKDSK the drive resulted in a 12 hour 1% fest.  Copying the data to another drive just froze the OS.

So, I did what any irrational person would do and convinced everyone else that they didn’t need that data any more.  The three billion rows of data were a little excessive anyways.

DROP TABLE ResponseTime

Tada!

The truth is that we only really care about the last seven days, and since it’s been down since Monday – and we won’t really be using it too much over the weekend – we decided that it was worth more to get the database back up and collecting data than it was trying to preserve last week’s data (along with the last 220 weeks of data) and just move on.

I recreated the table and the polling engines are all back online filling up our database as fast as they can.

I created two jobs for SQL Agent to attend to, to clean up data that is older than 15 days and to clean the older transaction logs. I also rebuilt the indexes on all other tables.  In the 232Gb file that is our database, we are now only uses approximately 400Mb of very non-fragmented space.

Before leaving work today I will most definitely try SHRINKFILE again…