Comments
Sort by recent activity
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 ...
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...
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
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...
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...