To catch specific SQLExceptions using Microsoft.NET and Microsoft Sequel Server you can use the snippet below. You also need to look for the specific error numbers/codes you want to catch. To do this you can either execute the following query in the SQL Management Studio or you can use this List of SQLException Numbers/Codes (Direct-DL around 15mb).
To get the current Database and Log Filesize using MSSQL you can use the snippet below. Tested using SQL Server 2012.
with database_files_CTE as
name, type_desc, physical_name, size_mb = convert(decimal(11, 2), size * 8.0 / 1024),
space_used_mb = convert(decimal(11, 2), fileproperty(name, 'spaceused') * 8.0 / 1024) from sys.database_files
select name,type_desc as [type],physical_name as [path],size_mb as [Filesize],space_used_mb as [Filesize used],
case size_mb when 0 then 0 else convert(decimal(5, 2), space_used_mb / size_mb * 100) end as [Filesize used %] from database_files_CTE;
To learn how to use the Space() function in MSSQL you can take a look at the following snippet.
Let’s say you have a table (tblTest) like this:
You want to select the next “X” number for Testcolumn. The problem is that when adding something to 000015 you will loose the 0 characters. So after getting the information that 16 is the next highest number you need to fill the gap between X and 16 with 0. And if the length of the X number is not fixed, you need to do that generic. The Space() function can help you alot. What the space function does is, it just creates as much spaces as you need. We will use this to replace the Spaces then with 0.
SELECT TOP 1 Testcolumn AS [Currently Highest Field Value],
LEFT(Testcolumn,1), -- select X
REPLACE( --Replace *
SPACE(LEN(Testcolumn) - 1 - LEN(CONVERT(BIGINT,SUBSTRING(Testcolumn,2,LEN(Testcolumn))))) -- adds the missing characters as Space
,' ','0') -- * each created SPACE() with a 0
CONVERT(BIGINT,SUBSTRING(Testcolumn,2,LEN(Testcolumn))) --select 15
+1 -- adds 1
) AS VALUE
ORDER BY SUBSTRING(testcolumn,2,LEN(testcolumn)) DESC