Tuesday, June 23, 2009
I have modified isp_ALTER_INDEX, which is the stored procedure that I use to defragment/rebuild indexes in SQL Server 2005. The changes include two bug fixes and one feature request.
The first bug fix was reported by Fedor Baydarov. He found that @lobData was not being re-initialized to zero after a LOB data type was encountered. This meant that the rest of the indexes to be processed were being done offline even if the online option was available. D’oh!
The second bug fix is that it didn’t check for LOB data types of the included columns in a non-clustered index. The online option is not available for this type of index, so the bug caused the stored procedure to fail when such a condition was encountered. This bug fix might have been found by a blog reader, but I’m unable to find an email regarding it. I came across the bug recently on a system that had such an index.
The feature request was to add the option to do the sort operation in the tempdb database. This is recommended if your tempdb is optimized according to best practices, such as by having a tempdb data file for each of the CPUs. See ALTER INDEX topic in SQL Server Books Online for more details.
You can download the new version of the stored procedure here.
Let me know if you run into any issues with it. I’d also be interested to hear if it works on SQL Server 2008.
Wednesday, June 10, 2009
I was going through my scripts today and found one that I’d like to share.
This SQL Server script will display job history. The benefit of this script over displaying it from the GUI is that you get to see the job durations quickly.
select job_name, run_datetime, run_duration
from
(
select job_name, run_datetime,
SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
SUBSTRING(run_duration, 5, 2) AS run_duration
from
(
select DISTINCT
j.name as job_name,
run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +
(run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
from msdb..sysjobhistory h
inner join msdb..sysjobs j
on h.job_id = j.job_id
) t
) t
order by job_name, run_datetime
I know I stole the code from the inner-most derived table from someone, but I didn’t make a note of the source. I did a quick search for the source, but I came up with too many possibilities.
Tuesday, April 07, 2009
I have modified isp_Backup, which is the stored procedure that I use to backup SQL Server databases. In this version, I fixed two bug and added a feature. Here are the changes:
- Fixed "2000 backupset" bug by qualifying the object to msdb.dbo.backupset
- Fixed the full backup check for SIMPLE recovery model databases (bug found by Eugene from PerfSpot.com)
- Added Red Gate SQL Backup functionality
If you have scheduled jobs that are using any of my previous versions, you might need to modify the job step so that it uses the new @bkpSwType input parameter rather than the @liteSpeed parameter.
You can download the revised stored procedure here.
In the download, I have provided a script, ModifyJobStep.sql, to update the job step by replacing @liteSpeed with @bkpSwType. This only needs to be run if the job step includes @liteSpeed. You may not have this if you were using native backups since "@liteSpeed = N" was the default. If the script fails, run it one more time. There is some sort of SQL Server bug that is encountered on some systems, but it doesn't appear if you run it a second time. It is not a bug with the script though.
Let me know if you encounter any issues with this version so that I can get them fixed.
In the next version, I will add support for SQL Server 2008. It is a rather quick modification, but I need to do some additional testing of it before I release it to the Internet. If you'd like the next version before I finish my testing, just send me an email.
Friday, March 27, 2009
I have modified isp_ALTER_INDEX, which is the stored procedure that I use to defragment indexes in SQL Server 2005. The changes include one bug fix and one feature request.
The bug fix was simply to add "SET QUOTED_IDENTIFIER ON" to the script. You'd only see the bug in special circumstances, such as if you were using indexed views.
The feature request was to add the ability to log the fragmentation data. The logged data could be used to determine if you want to rearrange the index or perhaps add/remove columns to reduce fragmentation. The logged data is stored in a table called DefragmentIndexes. If you don't want to log the data, then you'll still need to create the table if you want to use this new version, otherwise the CREATE PROC statement would fail due to a missing object. To configure the stored procedure to log the data, simply pass 1 to the new input parameter, @logHistory. The default for this new input parameter is 0, so you do not need to modify your job if you don't care about this new feature.
You can download the new version of the stored procedure and the required table here. I put them in the same script to make it easier to deploy the new version.
Let me know if you run into any issues with it.
Tuesday, March 03, 2009
If you've ever had to copy large files on a Windows platform using the Windows copy method (copy/paste in Windows Explorer or copy/xcopy commands), then you know how slow it is. Recently I had to setup database mirroring for a largish database, so I needed to copy the full backup to the mirror server. The database is about 110 GB in size, but since we use Quest's LiteSpeed product, the full backup is just 35.8 GB in size. I had heard about FastCopy, which claims to be the fastest Windows copy product, so I decided to do comparison tests.
Using the Windows copy method, the 35.8 GB file was successfully copied across the WAN in 2 hours and 15 minutes. Using FastCopy, it copied in 1 hour and 53 minutes. FastCopy was about 16% faster than the Windows copy method.
I then tried copying an 11.7 GB file in a different environment, one where file copies seem to take forever over the WAN. Using the normal Windows copy method, the 11.7 GB file copied across the WAN in 55 minutes. Using FastCopy, it copied in 51 minutes. FastCopy was only about 7% faster.
Both of the above tests were done on compressed files. Since I wasn't seeing much of a performance boost with FastCopy, I then tested with uncompressed files.
The first uncompressed file was 51.6 GB in size. That file took 4 hours and 3 minutes using Windows copy method and 3 hours and 16 minutes using FastCopy. That's a 19% improvement.
The second uncompressed file was 15.6 GB in size. That file took 2 hours and 32 minutes using Windows copy method and 2 hours and 23 minutes using FastCopy. That's a 6% improvement. By the way, I was getting about 1.85 MB per second for the transfer rate on this system, so that's why these times are so bad. On the other ones, I was getting around 4.75 MB per second.
FastCopy provides a command-line interface as well, so it can be used in batch files and scheduled jobs, such as those that refresh your development/test databases with production data.
If you don't mind the cluttered GUI and the poorly translated to English documentation, then I'd recommend this product. You can download and read more about FastCopy here.
There are other products out there that claim to be faster than the Windows copy method, such as Robocopy and TeraCopy, but I did not compare them to FastCopy. If you already performed these comparisons, please let me know.
Friday, February 27, 2009
Microsoft published the ERD for the system views about a month ago, but I'm just now seeing it. It is available in PDF or XPS format.
You can download them here.
Wednesday, February 25, 2009
As a DBA, we are often asked to refresh a database, which means to overwrite an existing database using a different database's backup. If you are rarely asked to do this, you may decide to do it manually. If you are asked to do this on a regular and perhaps scheduled basis, then you'd want to automate it. Developers often want to write and debug code against a copy of the production database, so it makes sense to refresh their databases on a daily or weekly basis.
If you were to do the refresh manually, you would probably follow these steps more or less:
- Copy the backup file
- Restore the database
- Unorphan the accounts if the SIDs aren't in sync
- Provide db_owner or similar permissions to the development staff
- Run a script for anything else not handled above
Depending upon the size of the backup, you may want to compress it after the first step from above using tools like WinRAR, PKZIP, or WinZip. These three tools have command line versions, so it can be easily added to the refresh process.
To automate this process, we need to write code that does the above steps and then to schedule that code to run via the SQL Server Agent. To make this easier to understand, I will be referring to the source server as Server1 and the destination server as Server2. An example of a source server is production, and an example of a destination server is development.
Here's how I have the refresh automated in a couple of my environments:
- On Server1, a job exists that compresses the latest full backup
- On Server2, there's a job that performs the following steps:
- Copies the compressed file to a local drive
- Decompresses the file to make the full backup available
- Kills all sessions to the database that is about to be restored
- Restores the database
- Sets the recovery model to Simple
- Grants db_owner privileges to the developers
You'll need to schedule the job on Server2 so that it doesn't run until the job on Server1 completes. I just watch how long the job on Server1 takes and then schedule the job on Server2 to run a few hours afterwards.
You can download all of my code here. The file for Server1 is named RefreshServer1.sql, and the file for Server2 is named RefreshServer2.sql. I perform all of the steps on Server2 in a single stored procedure, which means I only have one job step.
In the code, you'll notice that I am using rar.exe and CPAU.exe. Your refresh process may not need these.
We use WinRAR as our compression utility. WinRAR's command line version is rar.exe. CPAU.exe is used to provide credentials to authenticate to Server1 as Server1 and Server2 are in different domains in my environment, and unfortunately we don't have a trust relationship setup between the two domains. For more information on CPAU, see this blog post. In the comments, I included the restore command if you are using Quest's LiteSpeed. We use it for our backups, so we use their xp_restore_database extended stored procedure to restore them.
I don't have any code to unorphan the accounts in my refresh process as I ensure that the SIDs are identical between the two environments. For more information on that, see this blog post. In case you need a script to unorphan the accounts, I've included it in the download as UnorphanAccounts.sql.
You can easily rewrite the code to handle multiple databases that need to be refreshed on the same Server1 and Server2. In fact, that's what I am doing in my environments as some of the applications that I support use multiple databases. To handle multiple databases on the same instances, you'll need to loop through them in isp_RefreshServer1. My looping code is in there, but it is commented out. You'll also need to perform multiple restores in isp_RefreshServer2. I left the code for multiple databases in that stored procedure too, but I commented it out.
If you have a lot of databases to refresh and they all follow the same code, I would suggest parameterizing the stored procedures rather than hard-coding the various things.
Monday, January 05, 2009
This is very amusing and sad, and it is a great example of why backups are so important.
Now the company is dead thanks to their malicious IT guy.
Wednesday, December 24, 2008
I recently got setup at home with three identical monitors as I work from home often. Here's a picture of them. The left-most monitor is my main monitor for my work laptop. I extend that desktop to the middle monitor. The right-most monitor is for my home computer. For productivity and neatness reasons, I wanted to use only one mouse and one keyboard for both my work laptop and my home computer.
There are many tools out there that allow you to use one keyboard and one mouse with multiple computers, but most of them require special hardware and aren't free. It was recommended that I try Synergy, since it was free and only required software to be installed. I wasn't sure if it would work when I was VPN'ed into work as it doesn't work across my husband's work VPN, but I gave it a try anyway. Luckily it worked great for me. By adding four links to the server, I'm able to move the mouse 360 degrees in either direction. There is a very short delay when moving the mouse between computers, however it is not noticeable to me.
If you are using multiple computers with different monitors and don't already have a solution to the one keyboard and one mouse problem, then I'd highly recommend using Synergy.
Tuesday, December 16, 2008
It is easy to track database growth on a single SQL Server instance. We simply just need to store the results of sp_databases or loop through the databases and call sp_spaceused for each database.
If you support hundreds of SQL instances like I do, you'd want to store the database growth information in a central repository. From this central server, you could create a linked server for each SQL Server instance to track, but I hate creating linked servers. I especially hate having to create hundreds of them on one SQL Server instance. Instead of using linked servers, I created a CLR stored procedure. It requires one table.
You can download the code here. It includes the C# source code as well as the dll for the CLR object and a sample SQL script file to get it setup on your central server.
Once you have set it up, you can create a SQL job to call it. If you have a small number of SQL instances to administer, you can simply add multiple calls to isp_DatabaseGrowth, like this:
EXEC dbo.isp_DatabaseGrowth 'Server1\Instance1'
EXEC dbo.isp_DatabaseGrowth 'Server2'
If you have a large number of SQL instances to administer, I recommend looping through a table that contains one row for every SQL instance. Here is what my job step looks like:
DECLARE @serverId int, @serverName sysname, @max int
SET @serverId = 1
SELECT IDENTITY(int, 1, 1) AS ServerId, ServerName
INTO #Server
FROM dbo.Server
WHERE ServerName NOT IN ('Server1\Instance2', 'Server1\Instance3', 'Server3') --exclude certain SQL instances
SELECT @max = MAX(ServerId)
FROM #Server
WHILE @serverId <= @max
BEGIN
SELECT @serverId = ServerId, @serverName = ServerName
FROM #Server
WHERE ServerId = @serverId
EXEC dbo.isp_DatabaseGrowth @serverName
SET @serverId = @serverId + 1
END
DROP TABLE #Server
Here's the DDL for the Server table:
CREATE TABLE [dbo].[Server]
(
[ServerName] [sysname] NOT NULL,
CONSTRAINT [PK_Server] PRIMARY KEY CLUSTERED
(
[ServerName] ASC
)
)
If any of your databases were upgraded to SQL Server 2005, the data returned from sp_spaceused/sp_databases may contain incorrect data due to row count inaccuracies in the catalog views. Make sure to run DBCC UPDATEUSAGE on your databases after an upgrade to SQL Server 2005. Databases that were created in SQL Server 2005 do not have this issue.
Monday, December 15, 2008
Microsoft has released SQL Server 2005 service pack 3 and cumulative update package 1 for service pack 3. For more information about SP3, check this out. For more information about SP3 CU1, check this out. If you upgrade to just SP3, your SQL Server version will be 9.00.4035. If you upgrade to SP3 CU1, your version will be 9.00.4205.
Make sure you also update Books Online on your client machine. You can get the newest version of BOL here.
Thursday, September 11, 2008
Autogrowth is enabled by default for the SQL Server database files. The default setting of 10% for file growth on the database files can often times lead to poor performance, especially if you don't properly size your files for the near future.
An example of when you'll see poor performance is when you have a database that is 500GB in size. When it runs out of free space, it'll autogrow by 50GB. Depending upon your disk performance, this could take a bit of time to allocate the space which could impact your end-users. To minimize the impact, change the file growth setting to be based upon megabytes rather than a percentage. What you specify for the megabytes option will be based upon your database size and how much data is changing on a regular basis. For larger databases though, you should be somewhere between 100MB and 1024MB.
Kimberly Tripp discusses this topic a bit as well as some other things that DBAs should be aware of. Check this out for more information.
To change the default settings for future databases, make the change on the model database.
What file growth settings do you use when creating a database that is expected to be "large"?
Wednesday, September 03, 2008
EDIT: There is a new version of this stored procedure.
I've made a couple of changes to my defragment indexes stored procedure, isp_ALTER_INDEX, based upon feedback I've received from my blog readers. For those unfamiliar with this object, it defragments indexes in SQL Server 2005 using the ALTER INDEX command. Check the comment header block for its documentation.
Here are the changes:
- Added support for database and object names with special characters.
- Added support for indexes with the ALLOW_PAGE_LOCKS option enabled. Online reindexing is not available for indexes with this option enabled.
- Fixed the code so that it handles "gaps" in the temp table. In previous versions, you would hit a gap in the identity values of the temp table if rows had been deleted from it due to @minRowCount. This issue caused all indexes after the gap to not get defragmented.
In the next version, I will add a logging feature so that you can see what indexes were defragmented, see how bad the fragmentation was, and perhaps do some trending on the data.
Let me know if you'd like me to add any other features or if you find a bug.
You can download the stored procedure here.
Monday, August 25, 2008
There are times when I need to run some process using credentials other than my current security context, such as when my userid doesn't have permissions to a remote resource but another userid does. I can easily do this interactively via the "Run as..." option (right click a process and then enter credentials) or via the runas command in a cmd window. The problem with these though is that I have to type in a password, which creates a problem when I want to do this as a scheduled job. In the past, I've written a batch file to map a drive using the other credentials via the NET USE command. I've never liked that approach so when I recently needed to revisit this, I decided I'd look for alternative solutions.
I tried three different solutions: adding an echo to the runas command, runasspc, and CPAU.
The first solution didn't work at all for me. I found several pages that suggested piping the password with the runas command via echo command. I didn't see any evidence that this approach worked, but rather they were suggesting to try it. Here's what I tried:
echo password | runas /user:DomainName\UserName E:\Folder1\Process1.exe
I couldn't get the second solution, runasspc, to work, but I'm sure I just didn't try hard enough. I had very little patience that day. You don't need to install the tool, but you will need to copy all of the files and subdirectories if you want it to run on other servers. Here is an example call:
runasspc.exe /program:"E:\Folder1\Process1.exe" /domain:"DomainName /user:"UserName" /password:"password"
The third solution, CPAU, worked great and didn't have any other dependent files which makes it easy to setup other servers with it. Here are some example calls:
E:\CPAU\CPAU.exe -u DomainName\UserName -p password -ex E:\Folder1\Process1.exe -nowarn
E:\CPAU\CPAU.exe -u DomainName\UserName -p password -ex E:\Folder1\BatchFile1.cmd -nowarn
E:\CPAU\CPAU.exe -u DomainName\UserName -p password -ex "xcopy \\Server1\Share1\File1.txt E:\Folder1\ /Y" -nowarn
Any of the above example calls would solve my current issue, however I ended up using the last one since I'm simply doing a file copy. The third one is better than the second one as I don't need an extra file to do the xcopy.
Here's what I embedded into my stored procedure that copies the production databases down to development:
EXEC master.sys.xp_cmdshell 'E:\CPAU\CPAU.exe -u DomainName\UserName -p password -ex "xcopy \\Server1\Share1\File1.txt E:\Folder1\ /Y" -nowarn -wait -hide', NO_OUTPUT
There are other ways to do this, but CPAU works great for me and is lightweight. Let me know if there is something better out there though.
Tuesday, August 19, 2008
EDIT: There is a new version of this stored procedure.
I've made several changes to my backup stored procedure, isp_Backup. I fixed a few things and added some much needed features. Here are all of my changes:
- Fixed the retention code so that only those files that pertain to the type of backup are deleted. I had previously decided that I wanted all files older than @retention to be deleted, however I've changed my mind due to requests from my blog readers.
- Added support for database names with spaces, dashes, single quotes, and other annoying, special characters.
- Added support for British date format.
- Added feature for SQL Server 2005 to run a full backup if the transaction log chain is broken. This starts the chain again. If you run a transaction log backup on a database whose chain is broken, those transaction log backups are useless until a full backup is run. I am unable to locate the necessary information in SQL Server 2000. I've been told to locate the information in the msdb backup history tables, however no rows get put into these tables when the chain is broken.
- Added feature to run a full backup when a differential or transaction log backup is attempted but no full backup has ever been run. This situation throws an error, so it's better to just run the full backup rather than having failed jobs.
- Added feature to run a full backup when last full backup is older than the create date or restore date of the database. This is important on SQL Server 2000 instances where restores happen and a database with the same name existed before. In that situation, no full backup has ever been made on the restored database yet the msdb backup history tables might contain rows pertaining to it due to using the same name.
The last 3 changes were made to prevent job failures and for recoverability reasons.
I have not gotten a chance to add code to check if a database is being log shipped by SQL LiteSpeed. I haven't received any requests for this code nor do I have a situation like this where I work, so I've been avoiding this code change.
Let me know if you'd like me to add any other features or if you find a bug. After careful consideration of each, I'll make a decision whether or not to include them in the next version.
In the past I've embedded the code in my blog posts, however I'm now going to provide the script instead so that you get the formatted code.
To download the stored procedure, click here.
If you don't have SQL LiteSpeed installed, ignore the warnings in the output when creating this stored procedure. They are warnings and not errors. The stored procedure works with or without SQL LiteSpeed installed.