Problem :I am having a similar problem where someone managed to put a database into single user mode. Running sp_who and sp_who2 tell me the         database is already open and can only have one user at a time. Trying to go into Activity Monitor gives me the same problem.

select * from sysprocesses

seems to run and returns a lot of results but then switches to

Msg 924, Level 14, State 1, Line 1

Database 'MyDatabase' is already open and can only have one user at a time.

Says at the bottom query complete but with errors

RESOLVED :Use  Dedicated Admin Connection to take the database back to multi user mode.

Use MyDatabase

Alter Database MyDatabase Set Singel_User With Rollback Immediate.

Second step :When you run the below query to find out any session using this database context it will return zero results.

                            SELECT spid, DB_NAME(dbid) FROM sys.sysprocesses WHERE DB_NAME = 'mydb

                            GO

                            But when you run the below query you will see the locks on the database from the session which altered the database to single user mode.

                            SELECT * FROM sys.dm_tran_locks WHERE DB_ID('mydb') = resource_database_id

                            GO

*************************************************************************************************************************************************************

                How to find out who deleted some data SQL Server

SELECT     * FROM     fn_dblog(NULL, NULL)  WHERE     Operation = 'LOP_DELETE_ROWS'

Take the transaction ID for transactions you're interested in and identify the SID that initiated the transaction with:

SELECT [Transaction SID] FROM   fn_dblog(NULL, NULL) WHERE    [Transaction ID] = @TranID AND  [Operation] = 'LOP_BEGIN_XACT'

Then identify the user from the SID:

SELECT   * FROM    sysusers WHERE     [sid] = @SID

Edit: Bringing that all together to find deletes on a specified table:

DECLARE @TableName sysname SET @TableName = 'dbo.Table_1'SELECT    u.[name] AS UserName     , l.[Begin Time] AS TransactionStartTime FROM    fn_dblog(NULL, NULL) l INNER JOIN    (    SELECT        [Transaction ID]    FROM          fn_dblog(NULL, NULL)      WHERE        AllocUnitName LIKE @TableName + '%'    AND        Operation = 'LOP_DELETE_ROWS'    ) deletes ON  deletes.[Transaction ID] = l.[Transaction ID]INNER JOIN    sysusers u ON  u.[sid] = l.[Transaction SID]

*************************************************************************************************************************************************************

                                  Truncate  and delete Table   Large size of table  in Sql Server.

The transaction log for database ‘database name’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

There are several ways to overcome this issue:

1) If you want to delete all rows in the table, consider using TRUNCATE TABLE instead of delete. Truncate will not fill the transaction log. Remember that TRUNCATE will also reset any auto incremental keys.

2) In case you need to keep some of the data in the table, depending on the ratio between the numbers of rows you are keeping to the number of rows you wish to delete, you may want to:

a) Copy to the rows you want to keep to another table

b) Truncate the original table

c) Copy the data from the temporary table back to the original table.

3) Delete manually in chunks. You can run: delete TOP (10000) from [your big table]. Remember to commit between deletes.

4) Use script to delete in chunks with commit every number of rows.

This script for example commits every 100000 rows. Change it to fit your specific scenario:

DECLARE @Count INT

Declare @for_delete INT

Declare @chunk_size INT

SELECT @chunk_size=100000

SELECT @Count = 0

select @for_delete=count(*) from [Your big table] where [Your Where Clause]

While (@Count < @for_delete)

BEGIN

SELECT @Count = @Count + @chunk_size

BEGIN TRAN

DELETE top(@chunk_size) FROM [Your big table] where [Your Where Clause]

COMMIT TRAN

END

**************************************************************************************************************************************************

                                      Data Collection job  Error 

Error : collection_set_5_noncached_collect_and_upload) Invalid object name 'tempdb.dbo.sysutility_batch_time_internal'.

Solution

Create the following table on TempDB, It will resolve the issue:

CREATE TABLE [tempdb].[dbo].[sysutility_batch_time_internal] (

latest_batch_time datetimeoffset(7) PRIMARY KEY NOT NULL)

*****************************************************************************************************************************************************************************

SSIS issue connection issue:

Error message when you use SQL Server Management Studio to connect to SQL Server 2005 Integration Services from a client computer: "Connect to SSIS Service on machine '<SSISServer>' failed Access is denied"

Please use below site 

http://support.microsoft.com/kb/940232

Configuring the Integration Services Service with  named instances.

 http://msdn.microsoft.com/en-us/library/ms137789(SQL.90).aspx

*****************************************************************************************************************************************************************************

                                              Backup all  users DB's in  SQL Server 2000 and 2005 Backup script.

                                      All database backup through  script in sql server 2000

DECLARE @name VARCHAR(50) -- database name 

DECLARE @path VARCHAR(256) -- path for backup files 

DECLARE @fileName VARCHAR(256) -- filename for backup 

DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'D:\MSSQL\DBFiles\MSSQL\BACKUP\' 

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

 DECLARE db_cursor CURSOR FOR 

SELECT name

FROM master.dbo.sysdatabases

WHERE name NOT IN ('tempdb') 

 OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @name  

 WHILE @@FETCH_STATUS = 0  

BEGIN  

       SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 

       BACKUP DATABASE @name TO DISK = @fileName 

        FETCH NEXT FROM db_cursor INTO @name  

END  

 CLOSE db_cursor  

DEALLOCATE db_cursor

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

                                                    All database backup through script in sql server 2005

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE @DBName varchar(255)

DECLARE @DATABASES_Fetch int

DECLARE DATABASES_CURSOR CURSOR FOR

    select

        DATABASE_NAME   = db_name(s_mf.database_id)

    From

        sys.master_files s_mf

    where

       -- ONLINE

        s_mf.state = 0

       -- Only look at databases to which we have access

    and has_dbaccess(db_name(s_mf.database_id)) = 1

        -- Not master, tempdb or model

    and db_name(s_mf.database_id) not in ('Master','tempdb','model')

    group by s_mf.database_id

    order by 1

OPEN DATABASES_CURSOR

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

    declare @DBFileName varchar(256)   

    set @DBFileName = datename(dw, getdate()) + ' - ' +

                       replace(replace(@DBName,':','_'),'\','_')

    exec ('BACKUP DATABASE [' + @DBName + '] TO  DISK = N''c:\db backup\' +

        @DBFileName + ''' WITH NOFORMAT, INIT,  NAME = N''' +

        @DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 100')

    FETCH NEXT FROM DATABASES_CURSOR INTO @DBName

END

CLOSE DATABASES_CURSOR

DEALLOCATE DATABASES_CURSOR

 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

                                       Change the  database Recovery Model to SIMPLE Query

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

use [master]

go

-- Declare container variabels for each column we select in the cursor

declare @databaseName nvarchar(128)

-- Define the cursor name

declare databaseCursor cursor

-- Define the dataset to loop

for

select [name] from sys.databases

-- Start loop

open databaseCursor

-- Get information from the first row

fetch next from databaseCursor into @databaseName

-- Loop until there are no more rows

while @@fetch_status = 0

begin

    print 'Setting recovery model to Simple for database [' + @databaseName + ']'

    exec('alter database [' + @databaseName + '] set recovery Simple')

    print 'Shrinking logfile for database [' + @databaseName + ']'

    exec('

    use [' + @databaseName + '];' +'

    declare @logfileName nvarchar(128);

    set @logfileName = (

        select top 1 [name] from sys.database_files where [type] = 1

    );

    dbcc shrinkfile(@logfileName,1);

    ')

    -- Get information from next row

    fetch next from databaseCursor into @databaseName

end

-- End loop and clean up

close databaseCursor

deallocate databaseCursor

go

  -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

            Finding the Port Number for a particular SQL Server Instance

1) Using the GUI: In SQL Server 2000, you can use the Server Network Utility and in SQL Server 2005, you can use the SQL Server Configuration Manager.  Look under SQL Server 2005 Network Configuration and look at the TCP port for the TCP/IP protocol.

2)  Check the error log.  You will see an entry like: “Server is listening on [ 'any' <ipv4> 1433].”  The last 4 numbers denote the TCP/IP port number that is being used by that particular instance of SQL Server.

3)  Registry entry: HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\TCP

and you will see TCPPort as one of the entries and it’s value represents the port number for that instance.  In case you are using a named instance, then the registry entry will be: HKLM\Software\Microsoft\Microsoft SQL Server\<name of the instance>\MSSQLServer\SuperSocketNetLib\TCP

4) Using the extended stored procedure xp_regread, you can find out the value of the TcpPort by using SQL.  Example:

DECLARE @tcp_port nvarchar(5)

EXEC xp_regread

@rootkey    =    ‘HKEY_LOCAL_MACHINE’,

@key        =    ‘SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP’,

@value_name    =    ‘TcpPort’,

@value        =    @tcp_port OUTPUT

select @tcp_port

5) You can also chose to trace the client to server communication by using Microsoft Network Monitor or a network sniffer utility.  You can also use the TCP/IP netstat utility.  Example: Using “netstat -an” on the database server like :

  Proto  Local Address                 Foreign Address           State 

TCP    192.168.20.196:1433    192.168.20.70:3655     ESTABLISHED

TCP    192.168.20.196:1433    192.168.20.70:3664     ESTABLISHED

TCP    192.168.20.196:1433    192.168.70.15:5277     ESTABLISHED

 Note For SQL Server 2000, use Query analyzer to execute the following queries.

• 1. Start SQL Server Management Studio, and then connect to the instance of SQL Server.

  2. Run the following query:

• 1. In the Results pane, locate the following text (where X.X.X.X is the IP address of the instance of SQL Server and Yis the TCP/IP port where SQL Server is listening):

     1. SQL server listening on X.X.X.X: Y

  2. Note: For example, if you locate the "SQL server listening on 10.150.158.246: 1433" text in the Results pane, 10.150.158.246 is the IP address of the SQL Server and 1433 is the TCP/IP port where the instance of SQL Server is listening.

To verify the port configuration of an instance of SQL Server, follow these steps:

• 1. Start Registry Editor.

  2. In Registry Editor, locate the following registry key:

     1. 1. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\MSSQLServer\SuperSocketNetLib\Tcp

  3. Note If you are using SQL Server 2005, locate the following registry subkey:

• • • 1. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<MSSQL.x>\MSSQLServer\SuperSocketNetLib\Tcp\IPAll

**********************************************************************************************************************************************************************************

Error – cannot be reorganized because page level locking is disabled

During  maintenance the database  REORGANIZE   LIKE  "ALTER INDEX [PK_InterResourceCost] ON [dbo].[InterResourceCost] REORGANIZE  "

below error is getting .

Error: The index "PK_InterResourceCost" (partition 1) on table "InterResourceCost" cannot be reorganized because page level locking is disabled.

Solution :You can resolve this issue by enabling page level locking on the index below example . 

ALTER INDEX [PK_InterResourceCost] ON [dbo].[InterResourceCost]

 SET (

    ALLOW_PAGE_LOCKS = ON

     )

 GO

***********************************************************************************************************************************************************

OLE DB error: OLE DB or ODBC error: The query processor could not start the necessary thread resources for parallel query execution.; 42000

The error message usually happened when your SQL Server was very busy. The error message indicates that SQL Server cannot allocate a new thread for starting the parallel query. To check if your SQL Server has reached to the max worker threads, please follow the steps below:

1. When this issue happens, please first check how many threads your SQL Server process is consuming. You can open Task Manager, add a Threads column to the display list, identify your SQL Server process and check the thread count.

2. Run the following query to your SQL Server instance to check what the value of "max worker threads" is:

select max_workers_count from sys.dm_os_sys_info

3. Compare the two values to see if they are very close or equal.

For 16 CPUs on a 64-bit server, SQL Server can only support maximum 704 worker threads, http://msdn.microsoft.com/en-us/library/ms187024.aspx.

Please also run the following T-SQL to check whether or not the max worker threads value has been changed. We strongly recommend that you keep it the default value 0. If the config_value and run_value are not 0, please change it back to 0 so that SQL Server can best manage the worker threads:

sp_configure 'show advanced options',1

reconfigure

go

sp_configure 'max worker threads'

If SQL Server does not reach to the max worker threads, but the issue persists, probably your SQL Server was experiencing memory problem or a product issue. Before we further diagnose this issue, please post out the following information:

1. All of the configuration information of sp_configure

sp_configure 'show advanced options',1

reconfigure

go

sp_configure

2. Check your SQL Server error log to see if there is any error message logged when this issue happens and post the error message here.

***********************************************************************************************************************************************************

*************************************************************************************************************************************************************

Error :Cannot Connect to Speci ,1433 Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding .

Soloution: Please  you  need to check the following steps

1) Check the SQL Services  up and running or stop.

2) If SQL Services are up and running, please check the sql logs on default folder bcoz some times sql server will not grant new connections at that time also we will see these kind of messages

3) check with the IP adress

4) if you are trying to access remote sql server check the firewall ruels on both ends

5) Plase perform the UDL test with both np and tcp/ip

7) SQL Server Properties --> Advanced --> Remote Login timeout --> increse the value their

***************************************************************************************************************************************************************

                                    Restoring the SQL Server MASTER database  through Single User Mode 

C:\Windows\System32>CD..

C:\Windows>CD..

C:\>net start mssql$SAIFHUSS3 /m"SQLCMD"

The service name is invalid.

More help is available by typing NET HELPMSG 2185.

C:\>net start mssqLSERVER /m"SQLCMD"

The requested service has already been started.

More help is available by typing NET HELPMSG 2182.

C:\>net start mssqLSERVER /m"SQLCMD"

The SQL Server (MSSQLSERVER) service is starting.

The SQL Server (MSSQLSERVER) service was started successfully.

C:\>SQLCMD -S "SAIFHUSS3" -E

1> SELECT @@VERSION

2> GO

------------------------------------------------------------

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)

        Apr  2 2010 15:48:46

        Copyright (c) Microsoft Corporation

        Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service P

ack 1)

(1 rows affected)

1> restore database master  from disk ='C:\Program Files\Microsoft SQL Server\MS

SQL10_50.MSSQLSERVER\MSSQL\Backup\master.bak' with replace;

2> go

Processed 376 pages for database 'master', file 'master' on file 1.

Processed 4 pages for database 'master', file 'mastlog' on file 1.

The master database has been successfully restored. Shutting down SQL Server.

SQL Server is terminating this process.

C:\>

**************************************************************************************************************************************************************

                        Following query helps you to find all unused indexes within database .

SELECT u.* FROM [sys].[indexes] i JOIN [sys].[objects] o ON (i.OBJECT_ID = o.OBJECT_ID) LEFT JOIN [sys].[dm_db_index_usage_stats] u ON (i.OBJECT_ID = u.OBJECT_ID) AND i.[index_id] = u.[index_id] AND u.[database_id] = DB_ID() --returning the database ID of the current databaseWHERE o.[type] <> 'S' --shouldn't be a system base tableAND i.[type_desc] <> 'HEAP'AND i.[name] NOT LIKE 'PK_%'AND u.[user_seeks] + u.[user_scans] + u.[user_lookups] = 0 AND u.[last_system_scan] IS NOT NULLORDER BY 1 ASC

******************************************************************************************************************

                                            Failed SQL Server logins

******************************************************************************************************************

Question We trying to execute the stored procedure sp_helpdb but that gives an error:

Msg 515, Level 16, State 2, Procedure 

sp_helpdb, Line 53 Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails

Solution  1) Please run the below query and check Database Owner name is blank  or dbo" login does not have a username.

select name, suser_sname(sid), crdate, dbid, cmptlevel from master.dbo.sysdatabases

2) Against the Database add a User Name "sa" to the DB Owner "dbo"

exec sp_changedbowner 'sa'

I hope issue will resolved ...

*************************************************************************************************************************************************************

SQL Server 2000 useful undocumented stored procedures document attached.

*************************************************************************************************************************************************************

When execute shrink file  as below command  error getting 

DBCC SHRINKFILE (N'Diamond_log', 0, TRUNCATEONLY)

***********************************************************************************************************

Msg 3023, Level 16, State 2, Line 1

Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.

Cause :Another possible reason of the error is that if we perform shrink operation in parallel to backup operation. (Shrink is NOT something which I recommend, but people would never listen)

Solution: Find out the conflicting operation and retry your operation after stopping or finishing conflicting operation.

select * from sys.sysprocesses where  dbid='24' 

If any backup process is running  need to stop or completed  after that run shrink ffile  command