Activity overview
Latest activity by swelsh
Eddie,
I tried the ping from the server running the PowerShell to the new host server, and it resolved correctly. I also verified the IP/name resolution using NSLookup compared the ipconfig on the new host server. / comments
Eddie,
I tried the ping from the server running the PowerShell to the new host server, and it resolved correctly. I also verified the IP/name resolution using NSLookup compared the ipconfig on the ...
Command Line does not carry edition over UNC Path
I have a PowerShell script that I wrote to dynamically propagate a call to SQLDataCompare.exe on a UNC path. The current host with the professional edition on it is Windows Server 2003, SQL Data Co...
Peter and I resolved this via email.
In case anyone else has a similar issue, this is how we resolved it:
We'll need to turn on a trace flag in SQL Backup then. To do this, you need to add a -sqbdebug option to the SQL Backup Agent service.
Stop the SQL Backup Agent service.
In the registry, look for
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\SQLBackupAgent_<your instace name>
In the ImagePath value, add the -sqbdebug option e.g.
C:\Program Files (x86)\Red Gate\SQL Backup 7\SQL2005\SQBCoreService.exe -i SQL2005 -sqbdebug
Start the service. You should see a file named SQBCoreService_log.txt created in the folder where the SQL Backup Agent service executable is located e.g. C:\Program Files (x86)\Red Gate\SQL Backup 7\<your instance name>
Let the job run once, preferably without the GUI running (as it'll add a lot of noise to the log).
Investigated the debug log output and found this long running query: SELECT TOP 1 a.type, a.backup_set_uuid, a.first_lsn, a.last_lsn, a.checkpoint_lsn, a.database_backup_lsn, a.media_set_id, c.name, a.has_backup_checksums, a.is_damaged FROM msdb..backupset a INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id INNER JOIN master..sysdatabases c ON a.database_name COLLATE DATABASE_DEFAULT = c.name COLLATE DATABASE_DEFAULT WHERE b.physical_device_name = 'x' ORDER BY a.media_set_id DESC
To resolve the long running query, I had to cleanup the backup history tables in msdb. backupmediafamily was over 18 million records which is what was causing the slowdown.
Take away: Make sure you either enable the msdb history cleanup in SQL Backup, or run your own maintenance job to prevent the tables from growing too large. / comments
Peter and I resolved this via email.
In case anyone else has a similar issue, this is how we resolved it:
We'll need to turn on a trace flag in SQL Backup then. To do this, you need to add a -sqb...
The server is a Windows Failover Cluster of 2 Dell R900s, 4 quad-core processors and 256GB RAM in each.
Windows Server 2008 R2 SP1 x64
Clustered install of SQL Server 2008 R2 SP1 x64 / comments
The server is a Windows Failover Cluster of 2 Dell R900s, 4 quad-core processors and 256GB RAM in each.
Windows Server 2008 R2 SP1 x64
Clustered install of SQL Server 2008 R2 SP1 x64
This is what I am using now. EXEC [master].[dbo].[sqlbackup] '-SQL "BACKUP LOGS [Databaste, List, Goes, Here] TO DISK = ''I:\MSSQL\Backup\Logs\<AUTO>.sqb'' WITH COMPRESSION = 1, NO_CHECKSUM, THREADCOUNT = 8, MAILTO_ONERRORONLY = ''removed''"';
It was running the command above in a cursor for one database at a time instead of a list, it doesn't change the long running behavior.
We are running the same thing on 4 other servers and it is functioning just fine. This is the only server with such a large number databases receiving log backups though (83 databases), the next closest server is 54 databases and finishes in less than 2 minutes for most runs, even during heavy load. / comments
This is what I am using now.EXEC [master].[dbo].[sqlbackup] '-SQL "BACKUP LOGS [Databaste, List, Goes, Here] TO DISK = ''I:\MSSQL\Backup\Logs\<AUTO>.sqb'' WITH...
Resolved: Long running log backups because service sits idle
We are having an issue after upgrading from 6.5 to 7.6 where our transaction log backups are now taking significantly longer because the SQL Backup service just sits idle for anywhere from 20 secon...
Restore failed with Exit Code 280?
We have a job that nightly restores a copy of a production database to a reporting environment and had an issue last night where the backup ran long and caused the restore job to retry a few times ...
Chris,
I tried your idea of running the generated command manually in a regular command line environment and it actually printed that it was running with the mapping options and ignored the trailing spaces as expected.
After that I ran it manually in the powershell command line the same way and I noticed that it is only printing that it's using the first option specified after /Options: and not reading anything after the first comma.
I fixed it in the PowerShell script by separately declaring each option in its own switch like so: sqldatacompare.exe /v /s1:server1 /db1:database1 /s2:server2 /db2:database2 /include:Identical /include:table:cbsa /excludeColumns:cbsa:row_created /options:IgnoreSpaces /options:IncludeTimestamps /options:IncludeIdentities /options:DisableKeys /options:OutputComments /options:TrimTrailingSpaces /Export:I:\Shawn\RedGate\Export
With this command in PowerShell it's now behaving as expected. I imagine this is related to PowerShell not liking parsing the commas when passing the parameters in to the command. I tooled around with it more and found that wrapping the combined list in double quotes also works like so: sqldatacompare.exe /v /s1:server1 /db1:database1 /s2:server2 /db2:database2 /include:Identical /include:table:cbsa /excludeColumns:cbsa:row_created /options:"IgnoreSpaces,IncludeTimestamps,IncludeIdentities,DisableKeys,OutputComments,TrimTrailingSpaces" /Export:C:\swelsh\RedGate\Export
Thanks for suggesting I try the regular command line as it lead me to my solution, even it wasn't actually RedGate related. [image] / comments
Chris,
I tried your idea of running the generated command manually in a regular command line environment and it actually printed that it was running with the mapping options and ignored the trailin...
Data Compare 9 command line ignoring TrimTrailingSpaces
Hi,
I am working on a PowerShell function to use in automating execution of Data Compare from within a SQL Server job. I am doing this by dynamically building the sqldatacompare.exe command line ca...