Killing all Sql Server Data Bases

Declare @DBName varchar(50), @withmsg bit
Set @withmsg=1
Set @DBName='yourdatabasenmame' ---Change your databasename here
SET NOCOUNT ON
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''
IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END
IF @withmsg =1
PRINT  CONVERT(VARCHAR(10), @ConnKilled) + ' Connection(s) killed for DB '  + @DBName
GO

Note: Change the databasename for @DBName variable

How do we know which files and file groups are ONLINE

select fg.name as FilegroupName, fg.type_desc as FileGroupType, df.name as FileName, df.physical_name, df.state_desc 

from sys.database_files df left outer join sys.filegroups fg on df.data_space_id = fg.data_space_id

Create Folder in SQL Server 2005

USE Master;
GO
SET NOCOUNT ON

-- 1 - Variable declaration
DECLARE @DBName sysname
DECLARE @DataPath nvarchar(500)
DECLARE @LogPath nvarchar(500)
DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)

-- 2 - Initialize variables
SET @DBName = 'Foo'
SET @DataPath = 'C:\zTest1\' + @DBName
SET @LogPath = 'C:\zTest2\' + @DBName

-- 3 - @DataPath values
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @DataPath

-- 4 - Create the @DataPath directory
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName)
EXEC master.dbo.xp_create_subdir @DataPath

-- 5 - Remove all records from @DirTree
DELETE FROM @DirTree

-- 6 - @LogPath values
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @LogPath

-- 7 - Create the @LogPath directory
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName)
EXEC master.dbo.xp_create_subdir @LogPath

SET NOCOUNT OFF

GO

Shrink Log file

use test
GO
DBCC sqlperf(logspace)


Use Test
GO
--'Nul' it is an old DOS trick that behaves as if you are writing a file,
-- but really just dumps the information off, It's not use diskspace
BACKUP LOG test TO DISK = 'Nul'
go
dbcc shrinkfile(2,256)--Shrink file upto 256
GO

--TO know what are the files created for 'Test' database
Use Test
GO
SP_Helpdb 'test'

Monitor tempdb In Sql Server 2005

How to Monitor tempdb Use?
Running out of disk space in tempdb can cause significant disruptions in the SQL Server production environment and can prevent applications that are running from completing operations. You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space that is used by these features in the tempdb files. Additionally, to monitor the page allocation or deallocation activity in tempdb at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. These views can be used to identify large queries, temporary tables, or table variables that are using lots of tempdb disk space. There are also several counters that can be used to monitor the free space that is available in tempdb and also the resources that are using tempdb. For more information, see Troubleshooting Insufficient Disk Space in tempdb.

TempDB Best Practices

What is TempDB responsible for in SQL Server 2005?

Global (##temp) or local (#temp) temporary tables, temporary table indexes, temporary stored procedures, table variables, tables returned in table-valued functions or cursors.
Database Engine objects to complete a query such as work tables to store intermediate results for spools or sorting from particular GROUP BY, ORDER BY, or UNION queries.
Row versioning values for online index processes, Multiple Active Result Sets (MARS) sessions, AFTER triggers and index operations (SORT_IN_TEMPDB).
DBCC CHECKDB work tables.
Large object (varchar(max), nvarchar(max), varbinary(max) text, ntext, image, xml) data type variables and parameters.

What are some of the best practices for TempDB?

Do not change collation from the SQL Server instance collation.
Do not change the database owner from sa.
Do not drop the TempDB database.
Do not drop the guest user from the database.
Do not change the recovery model from SIMPLE.
Ensure the disk drives TempDB resides on have RAID protection i.e. 1, 1 + 0 or 5 in order to prevent a single disk failure from shutting down SQL Server.  Keep in mind that if TempDB is not available then SQL Server cannot operate.
If SQL Server system databases are installed on the system partition, at a minimum move the TempDB database from the system partition to another set of disks.
Size the TempDB database appropriately.  For example, if you use the SORT_IN_TEMPDB option when you rebuild indexes, be sure to have sufficient free space in TempDB to store sorting operations. In addition, if you are running into insufficient space errors in TempDB, be sure to determine the culprit and either expand TempDB or re-code the offending process.

Difference Between FREETEXT and CONTAINS

Yes - Let's take a look at the comparison first and then we can work through a few different examples.  The FREETEXT command is another alternative to access the data indexed by Full Text Search.  In general the FREETEXT command provides the ability to search for a matched term based on the meaning of the terms as opposed to the exact character string.  At a high level, this commands finds matches based on separating the string into individual words, determining inflectional versions of the word and using a thesaurus to expand or replace the term to improve the search.

Now let's compare the FREETEXT functionality with the CONTAINS command.  The CONTAINS command uses exact match or fuzzy logic to perform the matches with a single word or a phrase.  In addition, the words near another word can be found as well as performing a weighted match of multiple words where each word has a weight as compared to the others that are searched.  Check out CONTAINS (Transact-SQL) for a explanation on the CONTAINS command.

Depending on the search you are performing dictates which Full Text Search command you should use.  Keep in mind that FREETEXT and CONTAINS are only two of the four commands available.  The other two commands are CONTAINSTABLE and FREETEXTTABLE.  The comparison between the four commands will be saved for a future tip since it is fairly involved explanation that should include examples.

Until that point in time, here is one data point to consider: according to SQL Server 2005 Books Online FREETEXT (Transact-SQL) "Full-text queries using FREETEXT are less precise than those full-text queries using CONTAINS. The SQL Server full-text search engine identifies important words and phrases. No special meaning is given to any of the reserved keywords or wildcard characters that typically have meaning when specified in the parameter of the CONTAINS predicate."  Based on my testing, when the basic terms are queried with either command similar results are returned, so the precise factor for simple queries seems less of an issue.  For complex searches the CONTAINS command wins hands down with the ability to use wild cards, NEAR statements, etc. As such, if the flexibility of the search is built into the front end application then the highest level of flexibility on the back end, between the FREETEXT and CONTAINS commands, tips the scales toward the CONTAINS command.

Syntax:

USE AdventureWorks;
GO
SELECT *
FROM Production.Product
WHERE FREETEXT(*, 'screw washer spaner');
GO