April 11, 2008 at 12:31 am
Hi,
I have a server, which is used for replication and this server has been made as Publisher server. In this Server where in the DB Lies (both .mdf and .ldf) the free space has turned to 0MB and the total size of the DB is approx 60GB, so now i need to do delete the old data, if i execute the delete statements for the datas which is quite old after taking the mdf and ldf backup and attach in some other server, will the delete statement execute without any problem.
Or Do we have any other alternative way to make the hard disk size free so that we can have few space in the Hard Disk where the DB lies. Please it is very urgent as it is the publisher server.
I have checked the shrink db option and also the recovery model is turned to simple.
with regards
Dakshina murthy
April 11, 2008 at 1:04 am
dakshinamurthy (4/11/2008)
Hi,I have a server, which is used for replication and this server has been made as Publisher server. In this Server where in the DB Lies (both .mdf and .ldf) the free space has turned to 0MB and the total size of the DB is approx 60GB, so now i need to do delete the old data, if i execute the delete statements for the datas which is quite old after taking the mdf and ldf backup and attach in some other server, will the delete statement execute without any problem.
Or Do we have any other alternative way to make the hard disk size free so that we can have few space in the Hard Disk where the DB lies. Please it is very urgent as it is the publisher server.
I have checked the shrink db option and also the recovery model is turned to simple.
with regards
Dakshina murthy
If you're saying that there is no space left on the disk then I seriously doubt you'll be able to issue a delete statement because the .ldf file is on the same drive as the .mdf file. And therefore, you'll get a tran log full error. You won't be able to even shrink the tran log for the same reason.
I'm trying to think of an easy way around this and right now then only thing I can think is to detach the database, move the log file to another drive (which you should have been doing anyway) and re-attach the database. This should free up some space and give you room to work with.
If you don't have another disk onto which to put the log file - why not? Anyway, if you can't move the log file then you could detach the database, delete the log file and re-attach. SQL Server will create a new log file for you, which will hopefully be smaller than the current log file.
I guess I shouldn't have to tell you now that you should be placing a max cap limit on the data files to avoid this issue happening again. And some disk capacity alerts would probably be a good idea too 😉
April 11, 2008 at 1:13 am
Thanks for the instant and very usefull reply.
I have a doubt, please dont mind if it is a stupid question or a silly question, because i am new to all these.
My question is , as you sugested i will delete the log files and reattach the db, if i do so, what happens to all the data which is in the database will it remain without any change.
After deleting the log files and re-attaching the DB can i delete few records which are quite old. Please suggest very urgent
With Regards
Dakshina Murthy
April 11, 2008 at 1:26 am
dakshinamurthy (4/11/2008)
Thanks for the instant and very usefull reply.I have a doubt, please dont mind if it is a stupid question or a silly question, because i am new to all these.
My question is , as you sugested i will delete the log files and reattach the db, if i do so, what happens to all the data which is in the database will it remain without any change.
After deleting the log files and re-attaching the DB can i delete few records which are quite old. Please suggest very urgent
With Regards
Dakshina Murthy
There's no such thing as a stupid question.
You don't have to worry about the data in the database when you delete the log file. If there are any uncommitted transactions in the log file then you will lose those. But this would be very unlikely given that your data and log files are full. Any uncommitted transactions would have probably failed and rolled back a while ago.
Even so, you should take various precautions. Take a backup of the database (if you haven't already got one). When you detach the database copy the log file to a seperate server for safe keeping (just in case).
Once you have re-attached your database you will hopefully have freed enough space that you'll be able to delete some rows. However, I imagine you would have to delete quite a number of rows for it to have any major impact on the space used. Doing this will require a large transaction log so you might end up in the same position again. So I suggest deleting data in batches (say 20,000 rows at a time).
And don't forget to place a max cap limit on the log file size when you re-attach.
By the way, how large is the log file? I'm assuming here that it's sufficiently large that it will make a difference.
April 11, 2008 at 1:34 am
Thanks again, i will get back to you, with specifying the size of log files and mdf files for the DB
With Regards
Dakshina Murthy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply