| Edwin Daniel's profileMicrosoft technologies a...BlogLists |  Tools  Help |
|
July 29 Another undocumented stored procedure: DBCC LOG and fn_dblogI was tasked to do auditing of some scripts executed in our SQL Server databases and the only way I could think of is to read the transaction logs. This is instead of me going thru the hassle of scrutinizing every script that gets executed in the servers and the databases (which I eventually did). This is where I started to search for ways on how to read the transaction log. There are a couple of third-party tools available to do this taks. ApexSQL Log, Lugiment, Lugiment Log Explorer for SQL Server and SQL Log Rescue are just a few of these tools. But before I really looked in to these tools, I chanced upon two undocumented stuffs which I used to test whether I could really read the transaction logs. One of which is the DBCC LOG command. This is used to view the transaction log for a specific database. The syntax for this is as follows:
Full details of this command can be found here. Even though it is undocumented, a lot of people managed to find this command for use in reading SQL Server transaction logs. Another undocumented stuff is the system function fn_dblog which does the same thing as DBCC LOG.
In my case, I just looked at what these 2 can do but since I was instructed to test some third-party applications, I just stuck with that. Though at first, I did manage to generate some reports using these two. July 14 Moving the transaction log database file in a different locationI was just testing out how to move the transaction log file on a different location the easiest way. I am using SQL Server 2000 as I am doing this. First, you detach the database, making sure that nobody is using it. Next, you move the transaction log file on a different location. Then attach the database file back, pointing to the existing location of the MDF file. You will notice that you have an error on the LDF file indicating that the file cannot be found. Not to worry as you can change the location of the LDF file to the new location where you moved your transaction log file. After the file has been found, you can now attach the database successfully. This now specifies a different location for your transaction log database file. This works for both SQL Server 2000 and SQL Server 2005. The only thing that didn't work is that after detaching the original SQL Server 2000 database file (of course, I did attach it first befor detaching) in SQL Server 2005, I can no longer attach it back to SQL Server 2000. This is because there are schema change made on the database as I attached it to SQL Server 2005. The original SQL Server 2000 database has now been changed after attaching it to SQL Server 2000. Since the new schema is not supported in SQL Server 2000 (new system objects in 2005 that do not exist in 2000), the attach process fails. July 13 What am I doing wrong...pitfalls of configuring log shippingI spent my entire day trying to configure databases for transaction log shipping between two sites which are offshore. After testing a database I just created for this exercise, I started working on the existing databases. Here is the scenario: all databases on the primary server have a database maintenance plan configured with a daily full database backup set at 4:00AM and a transaction log backup set every hour. I asked my team lead if I can disable the transaction log backups so I can set up log shipping for the databases. After disabling the transaction log backups, I copied the latest full database backup to the standby server, restored it in read-only, standby mode in preparation for log shipping. After creating the log shipping plan, I sat down and waited so I can monitor whether it was working fine. After several logs backed up and copied from the primary and standby, I kept getting this kind of error message.
[Microft SQL-DMO (ODBC SQLState: 42000)] Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set begins at LSN 31433000001386500001, which is too late to apply to the database. An earlier log backup that includes LSN 31432000002000300001 can be restored.[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.
This error simply tells me that I may have an incomplete transaction log. Before I continue on with my saga (and spending the entire day figuring out what I did wrong), let me just give you a very short explanation on how SQL Server backups work. The log is not truncated when a full database backup is done. You do a complete backup of the database, and during the backup someone does the tran1 transaction (say modified a few hundred records) and commits it. The last thing the whole database backup does is it goes back to the log and backs up any work that was done during the backup. Each transaction has a number (called the Log Serial Number LSN). The database backup also keeps up with the last LSN that was backed up from the Tran Log. Let's say that the tran1 transaction was the thing backed up and its LSN is 850.
Now, going back to my experience. The reason I am having this error is because I was not able to disable the transaction log backup before performing the full database backup which I need to restore to the standby server. This causes the gap in the LSN values. It took me quite a while before I figured that out. Here's a sequence of steps I performed to make sure that the log shipping plan I created works fine:
I also made sure that I restored the database on the standby server in read-only, standby mode, terminating users during the restore process. After making sure that it works, I started documenting everything I did because it is just one of the 40+ databases which needs log shipping configuration July 12 Undocumented extended stored procedure - xp_fileexistIt's quite shocking to hear the word "undocumented" in SQL Server. This simply means that you can break your neck trying to find these topics in both the Books Online or Microsoft.com and you won't find anything. But there's always Google to take your chances. As I was trying to search for a VBScript to check for an existence of a file in a file system (it would be a lot easier in .NET but I'm limited to what I just have) when I chanced upon this extended stored procedure - xp_fileexist. DBAs do file system checks whenever they do data transformation or data loading from a delimited text file, an Excel spreadsheet or anything similar. If this was done by a batch job you need to check first whether a file exists based on a specific filename format (such as Test_yyyymmdd.xls). You can create a simple stored procedure to check for the formatting of the filename but to check whether the file exists before you to do your task is not something that you can do with SQL Server. Good thing there is such a stored procedure - xp_fileexist. This verifies whether a specified file exist. This stored procedure is located in the master database where you can execute it in the following syntax:
This will return three columns indicating if the file exists, if the file is a directory and if the file's parent directory exists as a result set. You may want to include this in a script to do functional checks such as the example below
Just remember that when using this stored procedure is a bit risky as Microsoft reserves the right to change or even remove this in future versions or even service packs without having any documentation (well, it really doesn't have any after all) so make sure to test your scripts after applying service packs or upgrading. Adding a new Sharepoint template physically on the server cannot be doneI was testing a Windows Sharepoint Services template that can be downloaded from the Microsoft download center for our internal use. I physically logged in to the machine to add the template but I couldn't make it happen. What I did was, I tried accessing the Sharepoint site from a client machine from where I tried adding the template. In a few seconds, my new template is now ready for use. This is because of the security features built into Windows Server 2003. So if you want to add a template to your Sharepoint site, do so by accessing the site NOT physically from the server but from a client machine using your browser. July 08 Creating a shared folder on a Windows Server 2003 clusterWe might be tempted to think that creating a shared folder in a clustered environment is similar to that of an ordinary server setup. We do it by right-clicking on a folder, selecting Sharing and Security, and configure shared folder permissions and shared name. But we must remember that clustered servers behave differently. We are looking at nodes which are not what the users see. Users only see the virtual server. So if you intend to create shared folders in a clustered environment, do so in the Cluster Administrator administration console. Connect to the cluster's server or node. If you created groups, you create your shared folders by right-clicking on the group name, point to New and select Resource. You follow the wizard on creating a new Shared Folder resource. The reason why we need to do this is because of the nature of a clustered environment. When a node fails, it is transparent to the users because the other node automatically fails over. Since you created the shared folder in the cluster (in this case, the virtual server), users can still see the shared folder even if the primary node is no longer working. If you create the shared folder in the node, and the node fails, there may be chances that users will not be able to see the shared folder because the node that has the shared resource is not available. July 03 Backup Strategy with Transaction Log BackupsBackup Strategy with Transaction Log Backups
I started working for a global IT services company as a systems engineer/DBA for the Microsoft platform. One of the things I started working on is to create a backup strategy which uses the concept of transaction log backups. This is a part of the disaster recovery planning and implementation which will be implemented in one of our major clients.
This outlines the requirements in setting up a backup strategy that utilizes transaction log backups in SQL Server 2000. The sequence of events in transaction log backups includes performing a full back up of the database and then performing incremental update backups using transaction log backups.
Requirements In order to transaction log backups in SQL Server 200 databases, several requirements have to be met: 1) The database in which transaction log backups will be applied must have the Full recovery model set. 2) Backup devices have been created prior to running backups for simplicity.
Transaction Log Backup The transaction log backup backs up the transaction log from the last successfully executed BACKUP LOG statement to the end of the current transaction log. It truncates the transaction log up to the beginning of the active portion of the transaction log and discards the information in the inactive portion. The active portion of the transaction log starts at the point of the oldest open transaction and continues to the end of the transaction log. More frequent backups of the transaction logs will mean longer time to restore the database should problems occur. It is recommended that proper planning is done to take into account the strategies to implement.
Backup Strategy using Transaction Log Backups (with Differential Backups) To do a transaction log backup with differential backup is the ideal strategy if the database is frequently being changed and restore time should be kept on a minimum. This requires that a full backup be done prior to doing the combination of a differential and transaction log backups. A strategy example is described below: · A full database backup is performed once a week, say, the last full database backup was done on Sunday at 12:00MN · A differential backup is done at the end of the day, say, a differential backup is executed every 12:00MN every day. · Transaction log backups are done on one hour intervals*, say a transaction log backup is executed at 1:00 AM and again at 2:00 AM and on every hour after that · The database becomes corrupted on Friday at 2:35AM
*NOTE: Frequency of backups should be balanced with server load so it minimizes contention on the server
Restore Process For the scenario specified, you go through the following steps to recover the database. · Back up the transaction log, if possible. Use the WITH NO_TRUNCATE option. This is to ensure that uncommitted transactions in the transaction log before 10:35PM are still available. · Restore the full database backup (and specify WITH NORECOVERY as there are still other backup files to restore) that was created on Sunday at 12:00 MN. This will be the baseline of the restore process. · Restore the differential backup (and still specify WITH NORECOVERY) that was created on Thursday at 12MN. This backup file is the latest differential backup and contains all changes that were made to the database since the full database backup on Sunday at 12:00 MN. You can ignore the other differential backups from Monday to Wednesday. · Apply the transaction log backups (and still specify WITH NORECOVERY) that were created on Friday at 1:00 AM and 2:00 AM. · Apply the transaction log backup that was created at the beginning of the restore process to ensure data consistency. This is the only restore process that requires the WITH RECOVERY option as this is the last step in the recovery process.
Back to bloggingI stopped blogging for quite some time due to very tight schedule. Now, I'm back. Hpefully with more entries than before. |
|
|
