Category: SQL Server

  • Script to find out data and log file size

    You can use below script to find out data file (*.mdf) and Log file (.ldf) size

    exec sp_helpdb [dbName]

    or

    use [dbName]
    select str(sum(convert(dec(17,2),size)) / 128,10,2) + 'MB'
    from dbo.sysfiles

  • Script to Take database offline

    Why anyone needs to take the database offline?
    1. May be user don’t want to use database for time being
    2. To restore the database which is used by multiple users. Yes you can restore the database eventhough it is offline


    EXEC sp_dboption N'DBName', N'offline', N'true'

    OR

    ALTER DATABASE [DBName] SET OFFLINE WITH
    ROLLBACK IMMEDIATE

  • How to make database read only?

    You can use the below script to make the database read only so that no user can edit the database.


    USE [master]
    GO
    ALTER DATABASE [DBName] SET READ_ONLY WITH NO_WAIT
    OR
    GO
    ALTER DATABASE [DBName] SET READ_ONLY
    GO

  • What is Orphan User and Script to fix the Orphan Users

    Orphan User:

    An orphan user is a user in a SQL Server database that is not associated with a SQL Server login.


    -- Script to check the orphan user
    EXEC sp_change_users_login 'Report'
    --Use below code to fix the Orphan User issue
    DECLARE @username varchar(25)
    DECLARE fixOrphanusers CURSOR
    FOR
    SELECT UserName = name FROM sysusers
    WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
    and suser_sname(sid) is null
    ORDER BY name
    OPEN fixOrphanusers
    FETCH NEXT FROM fixOrphanusers
    INTO @username
    WHILE @@FETCH_STATUS = 0
    BEGIN
    EXEC sp_change_users_login 'update_one', @username, @username
    FETCH NEXT FROM fixOrphanusers
    INTO @username
    END
    CLOSE fixOrphanusers
    DEALLOCATE fixOrphanusers

  • FAQs .Net Assembly

    How is the DLL Hell problem solved in .NET?  Assembly versioning allows the application to specify not only the library it needs to run (which was available under Win32), but also the version of the assembly. 

    How to deploy .Net assembly? An MSI installer, a CAB archive, and XCOPY command. 

    What is a satellite assembly and what is the use of it? When you write a multilingual or multi-cultural application in .NET, and want to distribute the core application separately from the localized modules, the localized assemblies that modify the core application are called satellite assemblies. 

    What namespaces are necessary to create a localized application? System.Globalization and System.Resources. 

    What is the smallest unit of execution in .NET? An Assembly is the smallest unit of execution in .Net 

    When should you call the garbage collector in .NET? It is recommended that, you should not call the garbage collector.  However, you could call the garbage collector when you are done using a large object (or set of objects) to force the garbage collector to dispose of those very large objects from memory. 

    How do you convert a value-type to a reference-type? Use Boxing 

    What happens in memory when you Box and Unbox a value-type?

    Boxing converts a value-type to a reference-type, thus storing the object on the heap.  Unboxing converts a reference-type to a value-type, thus storing the value on the stack