Emergency!! SQL Backup MDF File too large!!

Not MS SQL, but i needed another zone.

A file on my SQL server, sql2010.mdf has grown at a rate of 35GB per month and has reached 217GB.  We have 10 GB left on the drive.

What can I do to get rid of this file or shrink it to a manageable size???  If we don't get this down, we will not be able to use our accounting software and will have to shut down until it's fixed.  Any ideas?

Someone mentioned that I might need to run "BACKUP `databasename` WITH TRUNCATE_ONLY"

Will that help?  Since I only have 10GB left, where will I store the Backup file?  I'll have to install an new drive.  Also, will I need to take the DB offline? and will it take 10 years to backup?

Help!!! Thanks
LVL 4
jamesbcox1980Asked:
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.

cyberkiwiCommented:
BACKUP mydbname WITH TRUNCATE_ONLY

does not actually back anything up, but marks parts of the db so that the next backup will ignore (as if already backed up in a transaction log)

The sequence (run each separately)

use mydbname
Checkpoint
BACKUP mydbname WITH TRUNCATE_ONLY
DBCC shinkfile(2,1)
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
Anthony PerkinsCommented:
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.
0
cyberkiwiCommented:
>> 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.
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.

geek_vjCommented:
>>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.
0
jamesbcox1980Author Commented:
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?
0
jamesbcox1980Author Commented:
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?
0
geek_vjCommented:
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
0
jamesbcox1980Author Commented:
As I mentioned previously, the TRUNCATE command is not available in this version of SQLEXPRESS.
0
Anthony PerkinsCommented:
>>the TRUNCATE command is not available in this version of SQLEXPRESS.<<
Sure it is.
0
jamesbcox1980Author Commented:
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.
0
geek_vjCommented:
>>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?
0
Anthony PerkinsCommented:
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.
0
geek_vjCommented:
Please check if you have selected the right database in the top.
0
Anthony PerkinsCommented:
>>TRUNCATE TABLE `dbo.IM5_TransactionDetail`"<<
Do it like this (assuming you are in the right database):
TRUNCATE TABLE dbo.IM5_TransactionDetail


0
jamesbcox1980Author Commented:
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.
0
Anthony PerkinsCommented:
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.
0
Anthony PerkinsCommented:
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

Open in new window

0
Anthony PerkinsCommented:
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

Open in new window

0
jamesbcox1980Author Commented:
Will this still create a giant log file?  Remember, I only have 10GB left on the drive, and REALLY want to avoid the logs.
0
Anthony PerkinsCommented:
>>Will this still create a giant log file? <<
Let me put it this way.  These are your choices from better to worse:
1. Get permissions so that you or the DBA can execute TRUNCATE TABLE or DROP/CREATE TABLE.
2. Delete in batches.  Depending on your Log Transaction Backup you can stretch this over a long time.
3. Single DELETE. Guaranteed you will run out of space.
4. Get permission or ask a DBA to change temporarily the Recovery Model for the database to Simple to reduce the logging.  Please be careful with this option, especially if you are not familiar with SQL Server.

Now pick your poison.
0
cyberkiwiCommented:
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
0
cyberkiwiCommented:
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"
0
Anthony PerkinsCommented:
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.
0
jamesbcox1980Author Commented:
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.
0
cyberkiwiCommented:
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"
0
jamesbcox1980Author Commented:
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
0
geek_vjCommented:

>>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
0
jamesbcox1980Author Commented:
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.
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
Databases

From novice to tech pro — start learning today.