Comments
Sort by recent activity
Michelle, thank-you very much - the comparison works absolutely fine after making that modification to the XML file. [image]
Thanks
Chris / comments
Michelle, thank-you very much - the comparison works absolutely fine after making that modification to the XML file.
Thanks
Chris
Thanks for your responses.
We re-scripted the database to script folders using SQL Compare's ASCII option and, yes, the script files did contain '?' characters, which I duly replaced with '£' using Notepad before saving the files without changing the encoding.
The files are currently encoded using Windows-1252 (according to the file properties in Team Foundation Server) and, when opened in Notepad or SSMS, contain the '£' character.
When using SQL Compare to compare the database objects containing '£' to the Windows-1252 files containing '£', SQL Compare displays the script files' '£' as '?' even though other applications display '£'. SQL Compare then indicates that a difference is present, even when other applications and text editors display '£', as intended.
Presumably SQL Compare is treating the script files as being ASCII rather than Windows-1252 and, hence, the extended characters are being converted to '?' before comparison.
I hope this is a little clearer, but I don't see a way around this without either re-scripting to UNICODE (which is not workable as this will introduce inconsistencies due to the default encoding used by SSMS, which is where the majority of our scripts are created), or by replacing each occurance of '£' in our code with 'CHAR(163)'. :?
Thanks
Chris / comments
Thanks for your responses.
We re-scripted the database to script folders using SQL Compare's ASCII option and, yes, the script files did contain '?' characters, which I duly replaced with '£' using...
This is what we suspected, however we're keen to stick with ANSI (which we deliberately specified when scripting the database) which, for us, is the default encoding for applications such as SQL Server Management Studio and Notepad. We wish to ensure that all scripts use the same encoding when added to our source control software - enforcing non-standard defaults across a large development team would be tricky.
Applications such as Notepad are able to display these characters - why does SQL Compare handle them differently?
Chris / comments
This is what we suspected, however we're keen to stick with ANSI (which we deliberately specified when scripting the database) which, for us, is the default encoding for applications such as SQL Se...
That could be because ~700KB of contiguous free VAS is adequate to support native backups. As I mentioned, you can run into the same problem with T-SQL backups, and you may do so in time should the VAS region become even more fragmented. http://www.red-gate.com/supportcenter/C ... 000121.htm
Chris / comments
That could be because ~700KB of contiguous free VAS is adequate to support native backups. As I mentioned, you can run into the same problem with T-SQL backups, and you may do so in time should the...
To be on the safe side I would schedule some downtime for a quick restart of SQL Server (no need to restart the server). You might want to execute the sqbmemory extended procedure immediately after the restart so that you have figures for comparison at a later date.
It's good practice to regularly perform test restores of backups - if you were to do this then you'd quickly know whether or not the backup files were valid.
Chris
EDIT: Looking at your logs you can see that SQL Backup gradually reduces its memory requirements until the backup runs successfully:
1st attempt: 12,582,912 bytes
2nd attempt: 6,291,456 bytes
3rd attempt: 3,145,728 bytes
4th attempt: 1,572,864 bytes
5th (successful) attempt: presumably 786,432 bytes
I believe that SQL Backup gradually reduces the value of MAXTRANSFERSIZE http://www.red-gate.com/supportcenter/C ... wledgebase\SQL_Backup\KB200708000121.htm
Definitely restart the instance before the backups fail altogether due to a lack of appropriately-sized contiguous VAS, ideally you need at least 12582912 bytes (12MB) to be returned for the 'Maximum Free' value discussed earlier.[/url] / comments
To be on the safe side I would schedule some downtime for a quick restart of SQL Server (no need to restart the server). You might want to execute the sqbmemory extended procedure immediately after...
In my experience, in similar situations, you're unlikely to be at risk of "bad" backups, assuming that by bad backups you mean that the backup file is corrupt, it is far more likely that you'll get to the point where the backups will fail to run at all.
As I mentioned earlier, you need to restart SQL Server then closely monitor the output of sqbmemory to help you determine when the error is next likely to occur and to help you determine exactly what is causing the fragmentation.
Note that this issue is not really a problem with SQL Backup as the same can occur with native backups.
Chris / comments
In my experience, in similar situations, you're unlikely to be at risk of "bad" backups, assuming that by bad backups you mean that the backup file is corrupt, it is far more likely that you'll get...
It looks like the VAS region is heavily fragmented as the maximum 'free' block size is ~768KB. A freshly-restarted 32-bit instance should show a maximum 'free' block size of somewhere in the region of 100MB.
The immediate fix is to restart the instance of SQL Server, the long term fix is to try to work out why the VAS is becoming fragmented.
A few causes of VAS fragmentation and/or pressure are:
1. Using sp_xml_preparedocument without a corresponding sp_xml_removedocument.
2. Use of CLR objects (if you're not using CLR objects then ensure that the CLR sp_configure option is turned off and restart the instance if you change the option).
3. Use of the sp_OA... stored procedures on a SQL Server 2005 SP2 installation - upgrading to SP3 should fix the problem.
4. Use of linked servers.
Upgrading to 64-bit OS and SQL Server should remove the problem, although it's always advisable to first attempt to fix the root cause where possible.
Thanks
Chris / comments
It looks like the VAS region is heavily fragmented as the maximum 'free' block size is ~768KB. A freshly-restarted 32-bit instance should show a maximum 'free' block size of somewhere in the region...
Could you execute the following against the problematic instance of SQL Server and post the results back to the forum?
EXEC master.dbo.sqbmemory
Thanks
Chris / comments
Could you execute the following against the problematic instance of SQL Server and post the results back to the forum?
EXEC master.dbo.sqbmemory
Thanks
Chris
Hi Brian
Thanks for the confirmation.
Chris / comments
Hi Brian
Thanks for the confirmation.
Chris
@Alex B To answer your question regarding development environment, this happens in SSMS but also in VS when working with database projects where the database name is the same as the schema name in which the table is being created. This particular table script was auto-generated by VS when 'importing' the database via the native Database Project import task in VS. / comments
@Alex BTo answer your question regarding development environment, this happens in SSMS but also in VS when working with database projects where the database name is the same as the schema name in w...