Emergency!! SQL Backup MDF File too large!!

If in fact it is your mdf, then you are going to have to find data to delete in the tables.  There is no other way that you can shrink that database.

>> Also, will I need to take the DB offline? and will it take 10 years to backup?
Maybe 9.5.. :) Nah, 217 GB, something like 6 hrs on a decent drive give or take.

On a more constructive note, but a new drive, attach it to the server and create a second data file for the database.  For MSSQL you can use SSMS to do it, since you say
>> Not MS SQL, but i needed another zone.
then you need the database specific instructions.

SQL will start migrating data to the 2nd data file.

>>What can I do to get rid of this file or shrink it to a manageable size???

Agree with acperkins on this. As shrinking is not going to help in this case, you need to purge/delete old data regularly to keep the database size in control.

Well, I found the malignant table, and it appears to be a result of a looping import DTSX file.  It's completely full of useless information, and there's no telling what's real and what's not.  Thankfully, after some research, I discovered that nothing acutally uses the table (it's just a copy of the actual data that is available if we were to build something that needed it), so the information is safe to delete.  I paused the task for the DTSX file and took a leap of faith and ran

DELETE FROM IM5_TransactionDetail

It's no longer responding, and I wouldn't expect it to, when I'm deleting 207GB worth of rows.  I wonder if I should have done it 1 month at a time, instead of the entire 3 years... Oh well, I hope it's working.  I'm keeping a close eye on the SQLservr process to make sure it doesn't cause memory errors, but I'm hoping the will remove the cancer.

Soon as this is finished, I'll return to let you know.  Thanks.

PS Any comments on my understandable impulsive action?

Ok, so when it says it's not responding, it's because it timed out and the query is no longer running.  I found out exactly which version of SQL I'm using.  It's SQLEXPRESS, even though I was using SQL Server 2005.  Apparently it's an addon.

So, how can I remove the data without it timing out?  I tried TRUNCATE TABLE, but that's not a valud command in this version.  I also tried deleting between date ranges, but it times out before anything significant happens.

Any ideas?

If you are trying to delete the data in the entire table, then use truncate instead of Delete statement as truncate is pretty fast and is a non logged operation.

So, try the below one:

Truncate table IM5_TransactionDetail

As I mentioned previously, the TRUNCATE command is not available in this version of SQLEXPRESS.

>>the TRUNCATE command is not available in this version of SQLEXPRESS.<<
Sure it is.

Sorry, I was wrong.  It has something to do with the particular query window I was using.  I opened a new query and ran the truncate command, and I get the error

"Msg 4701, Level 16, State 1, Line 1
Cannot find the object "IM5_TransactionDetail" because it does not exist or you do not have permissions."

On

"TRUNCATE TABLE `dbo.IM5_TransactionDetail`"

I'm running as Administrator, but I'm not sure about permissions, so I'll do more research on that.  I don't think it's because of the table not existing, since I'm looking right at it.

>>As I mentioned previously, the TRUNCATE command is not available in this version of SQLEXPRESS.

Sorry, I guess this is not a limitation with express edition. Can you post the exact query u r trying to use and also the error message?

Probably what is happening is that the table has a foreign key so you cannot use TRUNCATE TABLE.  But this would have happened if you had Enterprise Edition.

Why don't you post the exact error message you are getting and we can suggest a workaround.  Doing a DELETE on the whole table will blow up your Transaction Log and you will once again run out of space.

Please check if you have selected the right database in the top.

>>TRUNCATE TABLE `dbo.IM5_TransactionDetail`"<<
Do it like this (assuming you are in the right database):
TRUNCATE TABLE dbo.IM5_TransactionDetail

I've tried it both ways.  I'm able to select from the table just fine... Apparently it's a permissions problem.  I'm running as Administrator, but I don't know that Administrator has all permissions.  Any idea which permission I need to be able to Truncate?  I read through the stipulations for being able to truncate a table, and this one meets them all, as far as I know.

Thanks again for all your help.

Unfortunately, in order to use TRUNCATE (as opposed to DELETE) you need to be a sysadmin, database owner or the table owner. From BOL:
TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.
You may have to resort to doing a DELETE in a loop.  Let me know if you need details.

This is how you would delete all rows in batches:

WHILE 1 = 1
	BEGIN
		DELETE TOP (100000) FROM dbo.Test	-- Modify 100K as appropriate
		IF @@ROWCOUNT = 0
			BREAK	
	END

Select allOpen in new window

Let's try that again:

WHILE 1 = 1
	BEGIN
		DELETE TOP (100000) FROM dbo.IM5_TransactionDetail	-- Modify 100K as appropriate
		IF @@ROWCOUNT = 0
			BREAK	
	END

Select allOpen in new window

Will this still create a giant log file?  Remember, I only have 10GB left on the drive, and REALLY want to avoid the logs.

while exists (select * from IM5_TransactionDetail)
begin
delete top(10000) IM5_TransactionDetail
checkpoint ' this will mark log space as reusable and won't grow
end

I just want to comment that I find this entirely bizarre..

>> Not MS SQL, but i needed another zone.
It's a .MDF file but not MS SQL?

>>  It's SQLEXPRESS, even though I was using SQL Server 2005.
You must be joking right?  Hot off the press was SQL 2008 R2 last week, and that only allows 10 GB databases.  You have Express running a 200+GB db?

>> TRUNCATE TABLE dbo.IM5_TransactionDetail
Let me see, if you were using
   DELETE FROM IM5_TransactionDetail
why suddenly the dbo. ? Are you sure it is not in a different namespace.  Drop the dbo. and try again
   TRUNCATE TABLE IM5_TransactionDetail
The worst you should get is

Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'IM5_TransactionDetail' because it is being referenced by a FOREIGN KEY constraint.

Not "table does not exist"

cyberkiwi,

If the author could use CHECKPOINT then they could just as well use TRUNCATE TABLE, don't you think?

In other words, CHECKPOINT requires practically the same permissions as TRUNCATE TABLE.

Ok, here's the thing.  I'm logged in as "Administrator".  I should have access.  In fact, I can change all my own Roles, but I can't edit the Roles themselves or create new ones.  As far as I know, the Administrator account should be the only "DBA" account there is... If not, well, I'm screwed.

Single DELETE did not work.  I get a timeout error saying that the database did not respond before the timeout expired.  I have looked and all the timeouts are set to infinite, expcept for the override, which I disabled, and still nothing.

There is no other "DBA" here to work with.  I am the only one to speak of.  I should have full rights access with this account.

In case you missed it.  Obviously check that you are in the right db.

>> TRUNCATE TABLE dbo.IM5_TransactionDetail
Let me see, if you were using
   DELETE FROM IM5_TransactionDetail
why suddenly the dbo. ? Are you sure it is not in a different namespace.  Drop the dbo. and try again
   TRUNCATE TABLE IM5_TransactionDetail
The worst you should get is

Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'IM5_TransactionDetail' because it is being referenced by a FOREIGN KEY constraint.

Not "table does not exist"

Well, I've been selecting from simply "IM5_TransactionDetail", so i tried "TRUNCATE TABLE IM5_TransactionDetail" but I tried it as "TRUNCATE TABLE dbo.IM5_TransactionDetails" as well, just in case it mattered to have the DBO namespace.  Apparently it doesn't.

I get:

Msg 4701, Level 16, State 1, Line 1
Cannot find the object "IM5_TransactionDetail" because it does not exist or you do not have permissions.

Is there any way to find out who the DB owner is, in case for some reason the "Administrator" is not the owner

>>Is there any way to find out who the DB owner is, in case for some reason the "Administrator" is not the owner

By Administrator, you mean you are the admin at server level or sql server level?

If you are admin at server level, there is no guarantee that you are admin at sql server level. So, if you are an admin at server level, make sure that Builtin\Administrators group is added and provided sysadmin priveleges in sql server logins.

If you know 'sa' password, then you should be able to perform the truncation.

Just to crosscheck if you are in the right database, try executing the below query:

USE dbnamehere
Go
TRUNCATE TABLE IM5_TransactionDetail

Thanks for all your help guys.  I didn't know there was an "SA" user enabled, and that I needed to log in as that user.  MySQL experience really hasn't helped me with SQL...

Final solution:

Login as SA,

USE dbname
TRUNCATE TABLE tablename
GO
DBCC SHRINKDATABASE (dbname, TRUNCATEONLY)
DBCC SHRINKFILE (DB_data)
GO

Once again, thanks for your help.  I was able to get the DB down to 20MB from 207GB in a matter of seconds once I figured out the proper user.

I only used this sequence because the data was not used, and there was no need for a backup.