aiDeeva.com

Category: SQL Server 2008

  • Change Setting for Recently Used Files in SSMS

    Check  it out on http://www.mssqltips.com/tip.asp?tip=2331

  • Script to Monitor the progress of ALTER, Backup, Restore, DBCC, Rollback and TDE commands

    We often like to check the progess or completed percentage of time consuming command. You can query the sys.dm_exec_requests DMV to check the status of the command.

    You must have atleast view state permission to execute the below query.
    ALTER INDEX REORGANIZE
    AUTO_SHRINK option with ALTER DATABASE
    BACKUP DATABASE
    DBCC CHECKDB
    DBCC CHECKFILEGROUP
    DBCC CHECKTABLE
    DBCC INDEXDEFRAG
    DBCC SHRINKDATABASE
    DBCC SHRINKFILE
    RECOVERY
    RESTORE DATABASE
    ROLLBACK
    TDE ENCRYPTION

    SELECT dmr.session_id,
       dmr.status,
       dmr.start_time,
       dmr.command,
       dmt.TEXT,
       dmr.percent_complete
FROM sys.dm_exec_requests dmr  CROSS APPLY sys.Dm_exec_sql_text(dmr.sql_handle) dmt WHERE dmr.command IN (‘ALTER’, ‘Backup’, ‘Restore’, ‘DBCC’, ‘Rollback’,  ‘TDE’)

  • Script to Update Statistics by passing database name

    You can use below script to update the statistics with the FULL Scan. You can pass the database name in below script, I have given JShah as database name.

    EXEC Sp_msforeachdb
@command1=‘IF ”#” IN (”JShah”) BEGIN PRINT ”#”;
EXEC #.dbo.sp_msForEachTable ”UPDATE STATISTICS ? WITH FULLSCAN”, @command2=”PRINT CONVERT(VARCHAR, GETDATE(), 9) + ”” - ? Stats Updated””” END’
,@replaceChar = ‘#’

    You can use below script to update the statistics with the SAMPLE Percent agrument. You can pass the database name in below script, I have given JShah as database name.

    EXEC Sp_msforeachdb
@command1=‘IF ”#” IN (”JShah”) BEGIN PRINT ”#”;
EXEC #.dbo.sp_msForEachTable ”UPDATE STATISTICS ? WITH SAMPLE 50 PERCENT”, @command2=”PRINT CONVERT(VARCHAR, GETDATE(), 9) + ”” - ? Stats Updated””” END’
,@replaceChar = ‘#’

  • Resolving could not open a connection to SQL Server errors

    Problem

    Sometimes you may have issues connecting to SQL Server and you may get messages such as the following:

    ERROR: (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error:) An error has occurred while establishing a connection to the server. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)

    Or

    An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)

    Or

    A network-related error or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.  Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 – No such host is known.) (Microsoft SQL Server, Error: 11001)

    http://www.mssqltips.com/tip.asp?tip=2340

    Click Me for Solution

  • Msg 15009, Level 16, State 1, Procedure sp_help, sp_helptext or object_definition()

    Whenever user has in-sufficient permission and he is executing sp_help, sp_helptext or the object_definition function, user will get the below error message. This error occurs because user doesn’t have have permissions to the see the object metadata.

    EXEC sp_helptext sp_indexdefrag
    Msg 15009, Level 16, State 1, Procedure sp_helptext

    To fix the issue we have to grant the VIEW DEFINITION permission to user.
    We can turn on the View Definition permission on all databases for all the users having public role using below code.

    USE master
    GO
    GRANT VIEW ANY DEFINITION TO PUBLIC

    We can turn on the View Definition permission on all databases for the user Jugal using below query.

    USE master
    GO
    GRANT VIEW ANY DEFINITION TO Jugal

    We can turn on the View Definition permission on SQLDBPOOL for the user Jugal using below query.

    USE SQLDBPOOL
    GO
    GRANT VIEW DEFINITION TO Jugal