SQL operation: determines whether the path is on the server and whether the database operation log is transferred to Zhujiang online

Experience and skills: share two Delphi development experiences

Recently, I encountered some difficult problems when I was doing "database Cutting Tools". After many research and searching, I finally solved them. Now I will share them with you, it will take a lot of time to find and explore similar problems in the future! 　　1. Determine whether the input path exists on the server: For example, to execute a database creation program on the client, the database must be created on the server, but the path can be manually entered, in this case, the system is faced with a problem of determining whether the input path exists on the server, so as not to report an error when executing the create database SQL statement: the path cannot be found. The specific method is as follows: Exec master .. xp_export shell 'dir E:/data': execute this SQL statement in the query analyzer. If this path exists, all files and folders under this path will be output, the number of available bytes of the disk and the number of bytes of the folder already exist (as shown in Figure 1). If this path does not exist, output information 2 is shown, and the message "file not found" is displayed ". However, when the path contains spaces, such as C:/program files, directly use exec master .. xp_cmdshell 'dir C:/program files ', the system returns the same result as shown in Figure 2. We need to perform additional processing to get the correct returned result: 　　(1) exec master .. xp_mongoshell 'dir "C:/program files/Microsoft SQL Server/MSSQL "' This method can be directly executed in the query analyzer, and the correct results can also be returned, but if it is put into the program for execution: SQL. Add ('exec master .. xp_mongoshell ''dir" C:/program files/Microsoft SQL Server/MSSQL "''). An error is reported when the program is open and cannot be executed. Why ??? 　　(2) Let's look at the SQL online help. The xp_mongoshell is described as follows: Xp_cmdshell {'COMMAND _ string'} [, no_output] Parameters 'COMMAND _ string' It is a command string executed on the command line interpreter of the operating system. The command_string data type is varchar (255) or nvarchar (4000). No default value is available. Command_string cannot contain a pair of double quotation marks. If the file path referenced by command_string contains spaces, a pair of quotation marks is required. If it is inconvenient to embed spaces, use the fat 8.3 file name as a solution. No_output Is an optional parameter, indicating to execute the given command_string, but does not return any output to the client. The Help File prompts us to pack the file path or program name with a pair of quotation marks. If the entire path package is not up, no error will be reported. Then I will pack the single-step path with spaces, run the following SQL: SQL. add ('exec master .. xp_cmdshell ''dir C:/"Program Files"/"Microsoft SQL Server"/mssql''). In this way, no error is returned when you open the file, it seems that the syntax check of the query analyzer is different from our own syntax check, which cannot be the same. Therefore, when there is a space in the path, the correct method is: Exec master.. xp_mongoshell 'dir C:/"Program Files"/"Microsoft SQL Server"/mssql' This also indicates that the green font section of the SQL Help File command_string cannot contain more than one pair of double quotation marks, which is incorrect, it seems that the SQL Server help file and the product have also encountered the problem of "the specification and the program do not match ...... 　　2. Clear Database Log Files The problem arises: Our Cutting Process is to copy the data before a date in the document data to the new database (select... into ...), then, delete the data from the original database. In this way, when a large number of databases are operated, the log files increase dramatically: when I copied a 0.48 million-record table, I finally found that the operation of this table only increased the log file of the new database by 170 MB. If this operation is not performed, therefore, log files occupy a large amount of valuable disk space. Moreover, the new database we transfer is only used for query, and there will be no insert, update, or delete operations in the future. It is useless to require these log files, therefore, some log files must be reduced in the process of transferring data to it. What should I do ?? The problem arises... 　　(1) No logs are recorded during processing The setting method is as follows: Open "properties" of the corresponding database in Enterprise Manager, and change "model" to "simple" in "options ". The result of this setting is that no transaction log is recorded for any operation on this database. Corresponding SQL: exec sp_dboption @ pdbname, 'trunc. Log On chkpt. ', 'true' However, after testing, we found that after this function is enabled, we cannot use transaction operations when operating on this database. When the program executes begintransaction, an error is reported and cannot be executed, because we cannot guarantee 100% correctness in the operations on this database, we still need transactions. Therefore, this method has limited application space and cannot meet the needs of our programs. We have to continue searching ..... 　　(2) logs can be recorded during the processing process, but the log files must be processed and reduced from time to time. The online help of SQL Server tells us: In the following cases, the physical size of the log file is reduced: When the DBCC shrinkdatabase statement is executed. When you run the DBCC shrinkfile statement that references the log file. When the automatic contraction operation occurs. 　　Next we will analyze these three solutions one by one: 　　① DBCC shrinkdatabase:Shrink all data and log files of a specific database, including our requirements, but also greater than our requirements. This solution is available, but don't worry, I felt that I had bought a dress that could be worn, but it was a little too big and uncomfortable to wear it. Let's analyze the following two solutions... 　　② DBCC shrinkfile:Shrink the size of the specified data file or log file of the relevant database. The difference from solution 1 is only one word: "and" and "or", which is equivalent to splitting solution 1 into two steps. What we need is to shrink the log file. Therefore, it seems more appropriate for us, a little tailor-made. But there is still no better solution. Let's look at the third solution... 　　③ Automatic contraction:The database can also be set to automatically contract at a given interval. The server regularly checks the space usage of each database. If a large amount of idle space is found in the database and its autoshrink option is set to true, SQL Server will reduce the file size in the database. It periodically executes DBCC shrinkdatabase. Since solution 1 is already a large dress, this solution is equivalent to wearing N Large clothes, one piece is enough. What should I do ?? Comprehensive comparison shows that solution 2 is exactly what we need. DBCC shrinkfile ('+ trim (eddbmc. Text) +' _ log, truncateonly) After processing this statement, the log file will return to its minimum state of 504kb, and any log records will be cleared. Combined with our tools, after copying a table, we will execute solution 2. The maximum space temporarily occupied by log files during processing is the log space generated when processing the largest data table, however, the data will be cleared and displayed as more than 500 kb. Compared with a large data file, it is slightly smaller.