Category: SQL Server

  • How to move data and log file using Alter statement

    You can use Alter command to move data and log file.

    Sample Script

    -- Moving data file to E:\ drive
    ALTER DATABASE tempdb
    MODIFY FILE (Name = tempdev,FILENAME = 'E:\tempdb.mdf')
    -- Moving log file to E:\ drive
    ALTER DATABASE tempdb
    MODIFY FILE (Name = templog,FILENAME = 'E:\templog.ldf')

  • Shrink SQL Server 2000 Database

    Query to generate DBCC shrinkfile script for all the user databases in SQL Server 2000


    select 'use ' + ltrim(rtrim(db_name(sd.dbid))) + char(13) + 'dbcc shrinkfile (' + quotename(ltrim(rtrim(sf.name)),'''') + ' ,truncateonly)' from sysaltfiles sf
    inner join sysdatabases sd on sf.dbid = sd.dbid
    where sd.dbid > 4

  • How to check authentication scheme in SQL 2005/SQL 2008?

    You can use below query to check authentication scheme whether it is Kerberos or NTLM.

    select auth_scheme from sys.dm_exec_connections where session_id=@@spid

  • MVP Award

    Dear Readers,

    With your support and comments, I am announced as MVP
    http://blogs.technet.com/southasiamvp/archive/2010/04/01/new-mvps-announced-april-2010.aspx

    Thank You,
    Jugal Shah

  • SQL Server and Protocols

    TCP/IP and Named Pipes
    By default, clients have TCP and Named Pipes as available protocols on most of client computer. You can manipulate the protocol ordering by using the SQL Server Client utility. The client application uses the protocols in the order specified on the client computer. If you are using SQL Server 2005, the protocol order is stored in the ProtocolOrder registry entry under the following registry subkey:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0

    IPX/SPX
    SQL Server 2005 does not support IPX/SPX. The newer versions of NetWare, such as NetWare 6.5, support TCP/IP which makes it the common protocol for all clients.

    VIA
    The Virtual Interface Adapter (VIA) can be used only by VIA hardware.

    Shared Memory
    Shared Memory can only be used on the local computer and cannot be used as a network protocol.

    Reference: Microsoft SQL Server 2005 Books Online (2006), Index: client connections [SQL Server], about client network connections, choosing a network protocol