The following T-SQL statements will print the logical name and physical location of system database files:
USE master;
GO
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'master')
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb')
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'model')
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'msdb')
Result:
This will get the list of all languages supported by the SQL Server 2008 Full-Text Search:
Code Snippet
- – to determine run_value corresponding to locale [LCID - LoCale IDentifier]
- SELECT lcid, name FROM sys.fulltext_languages
Result:
lcid name
———– ———————————————–
5124 Chinese (Macau SAR)
4100 Chinese (Singapore)
3098 Serbian (Cyrillic)
3082 Spanish
3076 Chinese (Hong Kong SAR, PRC)
2074 Serbian (Latin)
2070 Portuguese
…………….
1036 French
1033 English
1031 German
1028 Traditional Chinese
1027 Catalan
1026 Bulgarian
1025 Arabic
76 76
69 69
25 25
13 13
1 1
0 Neutral
(53 row(s) affected)
The following query will bring the list of system stopwords corresponding to the English locale:
Code Snippet
- – list all stopwords corresponding to 1033 locale [English]
- SELECT * FROM sys.fulltext_system_stopwords
- WHERE language_id = 1033
When this query is run 154 records containing system stopwords will be returned.
From: www.mssqltips.com
Problem
At our organization we need to check for a minimum amount of free space before proceeding with some processes that run on SQL Server 2000, 2005 and 2008 SQL Server instances. Do you know of a way to find out the free disk space and then fail the process if it does not meet the minimum requirements? Can you provide some sample code?
Solution
Checking for free disk space before proceeding with a process is a wise move if disk space is tight or a high percentage of the drive is needed for the process. It is disconcerting to have a process run for hours only to fail towards the end of the process due to insufficient disk space. Although a few different options are available to check for disk space (CLR, WMI, PowerShell, etc.) in SQL Server, let’s see how we can use the xp_fixeddrives extended stored procedure which is available in SQL Server 2000 to 2008.
Sample Stored Procedure to Assess the Free Disk Space on a SQL Server Disk Drive
In the sample stored procedure below, it is accepting a parameter for the minimum amount of megabytes (MB) free on a specific disk drive, then executing the master.sys.xp_fixeddrives extended stored procedure into a temporary table. Once the data is in the temporary table the current amount of free disk space is compared to the minimum amount of free disk space to determine if the process should continue or raise an error.
Read more…
