June 2, 2004 at 10:24 am
We got a database, mdf file is 400mb and log file is 1.5GB,since last one month we are not talking the trasaction log backup. What is the best way to shrink the log file with out loosing the data?. And i want to move this databse to different server, again what is the best way to do this?.
Any ideas please.
Thanks.
June 2, 2004 at 11:51 am
Why aren't you backing up the transaction log?
What is the best way to shrink the log file with out losing the data?. Backup the transaction log and then run DBCC SHRINKFILE <log filename>.
i want to move this databse to different server, again what is the best way to do this?. Detach the database. Copy the .mdf and .ldf files to the new server. Attach the database to the new instance.
-SQLBill
June 2, 2004 at 12:29 pm
Okay, I got SRVR_A and SRVR_B, DB_A1, DB_A2 on SRVR_A and DB_B2 on SRVR_B.
With in DB_A1 some objects pointing to DB_A2 (Ex: SELECT * FROM DB_A2.DBO.TABLE1), Now i am planning to move the DB_A1 to SRVR_B with same name but that should point to DB_B2,
My question is, how can i find the objects pointing to DB_A2 so that i can change them to point to DB_B2.
Thanks.
June 3, 2004 at 6:43 am
I would suggest scripting out all the views and stored procedures. Copy the script into Query Analyzer and use the FIND and REPLACE options under the EDIT menu. Try searching on 'DB_A2.DBO.'.
June 3, 2004 at 6:48 am
Another option on trying to shrink the transaction log is detaching the database and only attaching the .mdf files after removing or renaming the .ldf file. When you attach the database without the .ldf file, the database will create a new transaction log automatically with the original name of you present transaction log. Since you are planning to move the database anyway, you might want to give it a try.
June 3, 2004 at 10:01 am
You could also backup your current database(s). Then you should just restore those files to the server you want.
Make sure you do full backup before restoring database.
If another instance of SQL Server is at different drive [let's say D: instead of C:], you would have to do RESTORE ... WITH MOVE
June 3, 2004 at 10:27 am
I think Novac' solution is the best way. You can use sp_helptext but it has a restriction for procedures above 4000 characters long
June 15, 2004 at 9:10 am
Emir Sadikovic has the best idea here. Create a backup device. Select your database and look under management. From there you can create a backup device (a file) to backup your database to. Then go to Tools, and Backup database. Read carefully and select the backup device you just created (select the file you created earlier) and do a "FULL" backup. Notice the location the file is saved to (Important). From there copy that backup file (It will have a .BAK extension) to a location you can get to from the new database you want to move it to. Next from the SQL Enterprise Manager select Tools, Restore. From there select the file (Backup Device) you want to restore from. Switch to the "Options" tab. Be sure to change the location you are restoring to on that tab. Change the location for all the files you are restoring. It should match the location of the server you are moving the data to. Once this process is complete, if you have been careful and followed the instructions, you should have successfully copied that database from one Instance of SQL Server to the other.
If I am not mistaken you may have to create a backup device also on the server you are copying the data to. I am pretty sure you need to do this in order for the database to be restored on the new location. Check into this. I don't have the time right now to verify this for you, sorry. Its been a while since I did this, but I do know that it works great!
If you need any help with it please feel free to email me.
Regards,
Keith
June 15, 2004 at 9:52 am
Keith,
You are correct in that that method will work in transfering the database to the new location, but what about the run away transaction log. How would you clean that up? This is why I suggested detaching the database, moving just the data files (.mdf and .ndf) files to the new location and attaching them. When the sp_Attach_DB stored procedure runs, it will automatically create a new transaction log that is 1 MB in size. This method would kill two birds with one stone.
Then all one need to remember is to run sp_Change_Users_Login 'Report' to clean up the userids.
Dave Novak
June 15, 2004 at 10:11 am
Dave,
You are correct that does solve the transaction log problem for now, but how can he avoid the transaction log from getting away from him again?
Verify this, I am not 100% positive on this. (Books On Line, search for backup, and transaction Log Backup)
I do believe my method address both issues and addresses the future as well. Not only does it copy that database, but the backup process will also truncate the transaction log. Once the backup process is created it can be run from a job and scheduled to run every night or once a week as needed. This will keep the transaction log not only backed up but also at a manageable size.
Thoughts?
Regards,
Keith
June 15, 2004 at 11:55 am
Keith,
To make sure that the transaction log does not get away in the future, the DBA will have to set up a Full Backup followed by Log backup with truncate. And to make doubly sure of cleaning up unused space, I would throw in a DBCC ShrinkDatabase call.
I have found in my experience with SQL Server 7.0 and 2K, that just doing a full backup of the database does not free up all of the empty space allocated for a database. Because of this, I would have to go through many iterations of the above process to get the transaction log back under control. This is why I suggest detaching the database. It takes care of the issue in one step, and if you set the second parameter of the detach process to 'False', then it will update the statistics for the database also.
Dave Novak
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply