question

rizammar avatar image
rizammar asked

Getting Error while running application on sql server 2005 on win server 2008 as drive is full where the .mdf and .ldf files r located

I am running sql server 2005 on windows server 2008. I had .mdf and .ldf file in drive E: which is now showing full(0 bytes free). I also have F: drive(External USB drive) which has a lots of free space. I want to move .ldf/.mdf to C: drive or if possible F: drive because while running the application it is giving error. The other local drive is C: which is 17 GB free. .mdf is 750 mb and . ldf is 230 GB, so big.Also when I am trying to run a query it is not letting me use the same database, it is coming back to masters.Please tell me the steps. As am new to SQL please guide me like a beginner. Help will be greatly appreciated. Please reply people. Its been a day now Thanks in advance
sql-server-2005databasewindows-server-2008
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered
Well it looks like the database is in full recovery and there has been no proper transaction log management. What is the output of SELECT recovery_model_desc FROM sys.databases WHERE name = 'databasename' Also what is the acceptable data loss for this database? Can you afford to loose 5 minutes, 15 minutes, 1 hour, a days worth of data? If you can loose a days worth of data I would switch the database to simple recovery, checkpoint the log, and then shrink the log to a reasonable size and do a full backup daily. If you need to restore to within a time frame then again the above, but switch back to full recovery and impliment transaction log backups to the time frame in question.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ramesh 1 avatar image
ramesh 1 answered
check for the recovery mode of the datbase from the above server . if the database is in FULL Recovery mode then check my blog http://sqlservr.blog.com/2012/06/26/best-practice-to-shrink-in-logldf-file-in-sql-server/ if database is in SIMPLE Recovery mode then take a Full Back and run DBCC SHRINKFILE('DBNAME_LOG',10) replace dbname_log with log file of you database and 10 with your desired size, move ldf file to a usd drive will not imporve the performance
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.