aiDeeva.com

Category: SQL Server 2008

  • Script to Rename Database


    CREATE DATABASE sqldbpool
    –Solution – I
    EXEC Sp_renamedb  ‘SQLDBPool’,  ‘Jugal’

    –Solution – II
    ALTER DATABASE sqldbpool MODIFY name=jugal 

  • Clear/Remove SQL Server Services from Services.msc

    Problem:- 
    One of my friend was installing cluster SQL Server, installation was stopped in the middle of something and he was not able to remove the SQL Server from add/remove programs or SQL Server setup. He has deleted the registry, folder manually but still he can see the SQL Server Services in Services.msc. He called me for the issue, I have provided him the below solution and it will work.

    Solution:
    1) Go to command prompt and use SC command to delete service

    sc delete sqlserveragent
    sc delete mssqlserver

    2) You have to also delete the services registry entry using regedit
    HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services

  • How to take database out of emergency mode?

    In SQL Server 2000

    sp_configure ‘allow’ ,1
    GO
    Reconfigure with override
    GO
    Update sysdatabases set status = status&(~32768) where name = ‘SQLDBPool’
    GO
    sp_configure ‘allow’, 0
    GO
    Reconfigure with override
    go

    IN SQL Server 2005/2008

    ALTER DATABASE sqldbpool
    SET online

  • How to open database in Emergency Mode?

    In SQL Server 2000

    sp_configure ‘allow’ ,1
    GO
    Reconfigure with override
    GO
    Update sysdatabases set status = 32768 where name = ‘SQLDBPool’
    GO
    sp_configure ‘allow’, 0
    GO
    Reconfigure with override
    go

    IN SQL Server 2005/2008

    ALTER DATABASE sqldbpool
    SET emergency 

  • Script to calculate DB size and available size


    SELECT Db_name()                                                           AS
           dbname,
           name                                                                AS
           filename,
           size / 128.0                                                        AS
           currentsizemb,
           size / 128.0 – CAST(Fileproperty(name, ‘SpaceUsed’) AS INT) / 128.0 AS
           freespacemb
    FROM   sys.database_files;