Hi,
I have been working as Database Administrator for a few years now and everyday I come across questions for which I don't have any answers. So I rely on the Internet to provide me the answers. Google has to be the best colleague of mine, without whom I would not have survived in this profession for this long. So today I thought it was time that I give it back to the community and help my fellow Database Administrators with problems that we all face everyday. Starting today, I will start blogging one new technique, one new details, one new trick within SQL Server. Hopefully this will help others too.
Platform : SQL Server 2005 and 2008
I had set up log shipping between two servers and most days the size of the log file of the primary database remains within acceptable limits. But today there was some bulk load and that resulted in a huge log file. Consequently the log file size of the secondary database (Database to which the logs were being shipped and restored) also got big. So big that the disk drive where the log file was located was almost full.
So I had to figure out a way to shrink the log file of the secondary database but unfortunately it was in READ ONLY mode so there was no way I can shrink that database. Until today I had not reached this situation where I have to shrink the log file of secondary database. Just to try I ran the DBCC SHRINKFILE command against the primary database and that did the trick. This shrank the log file of the secondary server too. But before this happened, I had to run the LSBackp, LSCopy and the LSRestore job to make sure that the transaction was copied over to the secondary database.
Conclusion: In a Log Shipping set up in SQL Server 2005/2008, DBCC SHRINKFILE on primary database does get propagated to the secondary database.
Thanks it's very useful...I have a question...In mirroring..mirror server db log file got full,how to shrink the log file.
ReplyDeleteI assume that in mirroring too you can't control the log file of the secondary database. You will have to shrink the log file for the primary database in order to shrink the log for standby.
Deletehi Kaykay and Kaushik i read your blog and its really nice good job. But i have one question related to log shipping you mentioned in above post you configure log shipping between sql server 2005 and 2008 and it should be like this your primary server is 2005 and secondary server will be 2008 right and your secondary server is read_only mode right??
ReplyDeletei have one confusion here if our both sql server not in same edition that we cant setup secondary server as read_only mode it will work only with no_recovery mode.can you please confirm this will work or not i have little confusion in this..Thanks :)
By 2005/2008 I meant that this post is applicable to both versions. It does not mean that I have tested log shipping between 2005 and 2008 instances. Also, I am not sure if we can set up log shipping between two different editions. I think you should be able to do that. Also, when you set up secondary database, you always initialize it with NORECOVERY option.
Deletehi Kaykay and Kaushik i read your blog and its really nice good job. But i have one question related to log shipping you mentioned in above post you configure log shipping between sql server 2005 and 2008 and it should be like this your primary server is 2005 and secondary server will be 2008 right and your secondary server is read_only mode right??
ReplyDeletei have one confusion here if our both sql server not in same edition that we cant setup secondary server as read_only mode it will work only with no_recovery mode.can you please confirm this will work or not i have little confusion in this..Thanks :)
Thanks for reply Kaushik !! i understood and yes we can configure log shipping with different edition but not rerecorded by Microsoft but you can in this scenario you need to make your lower Sql Server edition(2005) setup as Primary Server and secondary should be 2008 higher and if you setup log shipping with different edition than your secondary server always in No_Recovery mode it cant be Read_only mode :) and i read your blog today its really nice Kaushik and please post some more blog in replication it will be great and really appreciated your efforts ..
ReplyDelete