Jul 23 2009 11:00AM GMT
Posted by: mrdenny
Server Hardware,
System Design
I am so sick and tired of people posting questions on various forums along the lines of “We just bought a Some brand and model of server and it has n hard drives in it. How should I setup the database on these drives?” Continued »
Feb 16 2009 8:58AM GMT
Posted by: mrdenny
Server Hardware,
Article,
enterpriseitplanet.com
A little while back I published an article on Enterprise IT Planet called “Optimal Database Setup Hardware Guide“. In this article I talk about some key points that should be looked at when building a new SQL Server.
Denny
Aug 25 2008 11:00AM GMT
Posted by: mrdenny
Backup & recovery,
SQL Server 2005,
Log Shipping,
Recovery,
KILL,
RESTORE LOG,
xp_dirtree,
xp_delete_file
So you’ve been tasked with setting up a quick and dirty reporting server. The goal is to restore the log files from the production server to the reporting server nightly.
The backups are simple, use the SQL Maintenance plan to backup the logs, and then copy them to the remote machine. But how do you restore the logs to the reporting server nightly.
Well I’ve got a two step SQL job which should help you out.
Step 1 kills all current sessions in the database, and step 2 does the actual restores.
The code for step 1 is:
declare @spid varchar(20)
declare cur CURSOR FOR
select spid
from sys.sysprocesses
where dbid = db_id(’Your Database Name Here’) /*<—Put your database name here*/
and spid > 50
open cur
fetch next from cur into @spid
while @@FETCH_STATUS = 0
BEGIN
exec (’kill ‘ + @spid)
fetch next from cur into @spid
END
close cur
deallocate cur
The code for step 2 is:
create table #Files
(FileName nvarchar(4000),
Depth int,
IsFile bit)insert into #Files
exec xp_dirtree ‘d:\’, 1, 1
delete from #Files
where IsFile = 0
declare @FileName nvarchar(4000)
declare cur CURSOR FOR SELECT FileName from #Files
open cur
fetch next from cur into @FileName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @FileName = ‘D:\Path\To\Your\Log\Backups\’ + @FileName
RESTORE LOG YourDatabaseNameHere FROM DISK=@FileName WITH STANDBY=’D:\Path\To\Your\StandBy\File.standby’
IF @@ERROR <> 0
exec xp_delete_file @FileName
fetch next from cur into @FileName
END
close cur
deallocate cur
I hope this makes your process easier. Now this code only works on SQL Server 2005 and up as the system stored procedures which I use were not included until SQL Server 2005.
Denny
Nov 14 2007 8:00AM GMT
Posted by: mrdenny
DataCenter
When you setup your data center, something that seams to be getting overlooked these days is the data center environmentals. We all know that we need batteries and a generator for the power to keep the servers online, and that we need AC to keep them cool. But I’ve seen a disturbing number of very large data centers who do not have the AC on the generators. This creates a problem when the power goes out. While it’s great that the servers will stay online the data center will quickly get up into the 100+ degree range (F). This can quickly lead to data integrity issues is the hard drives start to fail.
Just a couple of days ago a client of mine who hosts there servers at a company (who shall remain nameless) and their power when out for about 3 hours. The temperature went up to 120 degrees in the data center, because the AC system which they brag about having wasn’t on the generator. They actually called all their clients asking the clients to power down any systems which aren’t mission critical in order to save heat. Frankly, I was surprised that the machines didn’t start to power them selves down. I guess the HP servers are built a little more robust than I thought (not that I really want to try again).
Denny
Nov 5 2007 8:00AM GMT
Posted by: mrdenny
SQL,
Recovery
If you have ever seen the BACKUPTHREAD wait type in the sysprocesses table or sp_who2 output and wondered what it is, I have found the answer.
The basic explanation is “Used when waiting for a backup thread to complete. Wait time may be very long (minutes, hours).” Basically what this means is that there is a backup running and something is waiting for it to complete.
When i saw this show up I was running a restore. That restore had three entries in the sysprocesses table. The first was the main kpid, with two child kpids. The parent kpid was the wait type of BACKUPTHREAD while it was waiting for the child kpid to finish processing. In my case the wait time was short, and it seamed to switch from this wait type to an IO wait type.
Denny
