It is being used by another process

The process cannot access the file 'C:\Windows\SysWOW64\perf-MSSQL10_50.MSSQLSERVER-sqlagtctr.dll' because it is being used by another process

	Type: Bug ID: 576412 Opened: 7/19/2010 8:34:44 AM Access Restriction: Public				1 Workaround(s) view | add 3 User(s) can reproduce this bug I can too Watch List add | remove

Windows 7 x64, SQL 2008 SP1 CU8 upgrade to SQL 2008 R2 RTM. i get this error message everytime:

The following error has occurred:

The process cannot access the file 'C:\Windows\SysWOW64\perf-MSSQL10_50.MSSQLSERVER-sqlagtctr.dll' because it is being used by another process.

Click 'Retry' to retry the failed action, or click 'Cancel' to cancel this action and continue setup.

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=10.50.1600.1&EvtType=0x60797DC7%25400x24C2C4E7

I tried to use process explorer to determine what has this locked, but i am starting to suspect some type of redirection is not occurring?

WORKAROUND :- 


1) Stop SQL Services.
2) Rename the file

Original name: perf-MSSQL10_50.MSSQLSERVER-sqlagtctr.dll
Renamed: OLD_perf-MSSQL10_50.MSSQLSERVER-sqlagtctr.dll
and was able to 'Retry' the Repair SQL 2008 R2 installation and continue.

This works.

Delete Secondary File

To shrink a data or log file

1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
2. Expand Databases and then right-click the database that you want to shrink.
3. Point to Tasks, point to Shrink, and then click Files.
4. Select the file type and file name.
5. Optionally, select the Release unused space check box.
   Selecting this option causes any unused space in the file to be released to the operating system and shrinks the file to the last allocated extent. This reduces the file size without moving any data.
6. Optionally, select the Reorganize files before releasing unused space check box. If this is selected, the Shrink file to value must be specified. By default, the option is cleared.
   Selecting this option causes any unused space in the file to be released to the operating system and tries to relocate rows to unallocated pages.
7. Optionally, enter the maximum percentage of free space to be left in the database file after the database has been shrunk. Permissible values are between 0 and 99. This option is only available when Reorganize files before releasing unused space is enabled.
8. Optionally, select the Empty file by migrating the data to other files in the same filegroup check box.
   Selecting this option moves all data from the specified file to other files in the filegroup. The empty file can then be deleted. This option is the same as executing DBCC SHRINKFILE with the EMPTYFILE option.
9. Click OK.

Row Count

Sql server Central

Backup Verification Script - Backup when and what type

Select Distinct convert(varchar(25),@@Servername) as Servername,
            convert(varchar(30),e.database_name) as DBname,
            convert(varchar(20),convert(sysname,DatabasePropertyEx(e.database_name,'Recovery') )) as Recovery_model,
database_creation_date,backup_start_date,backup_finish_date,
           
(Select  convert(varchar(25),Max(backup_finish_date) , 100)
              From msdb..backupset a
              Where a.database_name=e.database_name
                and a.server_name  = @@servername
                and type='D'
              Group by a.database_name) Database_Bk,

             (Select convert(varchar(25),Max(backup_finish_date) , 100)
                From msdb..backupset b
               Where b.database_name=e.database_name
                and b.server_name  = @@servername
                 and type='L' Group by b.database_name) Log_Bk,

             (Select convert(varchar(25),Max(backup_finish_date) , 100)
                From msdb..backupset c
               Where c.database_name=e.database_name
                and c.server_name  = @@servername
                 and type='I' Group by c.database_name) Diff_Bk,

             (Select convert(varchar(25),Max(backup_finish_date) , 100)
                From msdb..backupset d
               Where d.database_name=e.database_name
                and  d.server_name  = @@servername
                 and type='F' Group by d.database_name) File_Bk

 From msdb..backupset e
Where e.database_name Not in ('tempdb','pubs','northwind','model')
  and e.server_name = @@Servername
  and e.database_name in (Select Distinct name from master..sysdatabases)

--never backed up
Union all
select Distinct convert(varchar(25),@@Servername) as Servername,
            convert(varchar(30),name) as DBname,
            convert(varchar(20),convert(sysname,DatabasePropertyEx(name,'Recovery'))),

NULL, NULL , NULL, NULL, NULL, NULL, NULL
from master..sysdatabases as record
where name not in (select distinct database_name from msdb..backupset)
and name not in ('tempdb','pubs','northwind','model')
order by 1,2

Script to find Recovery Model and File Location

select *  from
(select sf.fileid, sd.name, sd.cmptlevel, sf.filename,sf.filename as secfile , DATABASEPROPERTYEX (sd.name,'Recovery') as Rec_model
 from sysaltfiles sf join sysdatabases sd on sf.dbid=sd.dbid ) as s --where s.rec_model='Simple'

Script to find Recovery Model and File Location

select *  from
(select sf.fileid, sd.name, sd.cmptlevel, sf.filename,sf.filename as secfile , DATABASEPROPERTYEX (sd.name,'Recovery') as Rec_model
 from sysaltfiles sf join sysdatabases sd on sf.dbid=sd.dbid ) as s --where s.rec_model='Simple'

Databases Sizes and Current DB Table Size

The following query will create a store proc SP_PH_DATABASE_USAGE, which will give the DataBase Sizes and Table Sizes for the current DataBase.

USE MASTER
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = 'SP_PH_DATABASE_USAGE' AND XTYPE = 'P')
DROP PROC SP_PH_DATABASE_USAGE 
GO


CREATE PROCEDURE SP_PH_DATABASE_USAGE AS
SET NOCOUNT ON
IF EXISTS( SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##DATASPACEFORDB' )
DROP TABLE ##DATASPACEFORDB
IF EXISTS( SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##TEMPFORDB' )
DROP TABLE ##TEMPFORDB




CREATE TABLE ##DATASPACEFORDB
 ( FILEID NUMERIC,
FILEGRP NUMERIC,
TOTEXT NUMERIC,
USDEXT NUMERIC,
   LFILENM VARCHAR( 100),
   PFILENM VARCHAR( 100)
   )
INSERT ##DATASPACEFORDB
 EXEC( 'DBCC SHOWFILESTATS WITH NO_INFOMSGS')


SELECT FILEID,GROUPNAME,(TOTEXT*64)/1024 AS 'TOTALSPACE_MB',(USDEXT*64)/1024 AS 'SPACEUSED_MB',LFILENM,PFILENM 
INTO ##TEMPFORDB FROM ##DATASPACEFORDB WITH (NOLOCK), SYSFILEGROUPS  WITH (NOLOCK) WHERE FILEGRP = GROUPID
ORDER BY GROUPNAME


INSERT INTO ##TEMPFORDB 
SELECT '50','TOTAL DATA FILE SIZE IN GB',SUM((TOTEXT*64)/1024)/1024 ,SUM((USDEXT*64)/1024)/1024  ,'',''
FROM ##DATASPACEFORDB WITH (NOLOCK), SYSFILEGROUPS  WITH (NOLOCK) WHERE FILEGRP = GROUPID




IF EXISTS( SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##DATASPACEFORTEMPDB' )
DROP TABLE ##DATASPACEFORTEMPDB


IF EXISTS( SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##TEMPFORTEMPDB' )
DROP TABLE ##TEMPFORTEMPDB


CREATE TABLE ##DATASPACEFORTEMPDB
 ( FILEID NUMERIC,
FILEGRP NUMERIC,
TOTEXT NUMERIC,
USDEXT NUMERIC,
   LFILENM VARCHAR( 100),
   PFILENM VARCHAR( 100)
   )
INSERT ##DATASPACEFORTEMPDB
 EXEC( 'USE TEMPDB DBCC SHOWFILESTATS WITH NO_INFOMSGS')


SELECT FILEID,GROUPNAME,(TOTEXT*64)/1024 AS 'TOTALSPACE_MB',(USDEXT*64)/1024 AS 'SPACEUSED_MB',LFILENM,PFILENM 
INTO ##TEMPFORTEMPDB FROM ##DATASPACEFORTEMPDB WITH (NOLOCK), SYSFILEGROUPS  WITH (NOLOCK) WHERE FILEGRP = GROUPID
ORDER BY GROUPNAME


INSERT INTO ##TEMPFORTEMPDB 
SELECT '100','TOTAL DATA FILE SIZE IN GB',SUM((TOTEXT*64)/1024)/1024 ,SUM((USDEXT*64)/1024)/1024  ,'',''
FROM ##DATASPACEFORTEMPDB WITH (NOLOCK), SYSFILEGROUPS  WITH (NOLOCK) WHERE FILEGRP = GROUPID


IF EXISTS( SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##TMPLOGSPACE' )
DROP TABLE ##TMPLOGSPACE


CREATE TABLE ##TMPLOGSPACE 
(
DATABASENAME VARCHAR(100),
LOGSIZE VARCHAR(100),
LOGSPACEUSED VARCHAR(100),
STATUS VARCHAR(100)
)


INSERT INTO ##TMPLOGSPACE 
    EXEC ('DBCC SQLPERF ( LOGSPACE ) WITH NO_INFOMSGS')




SELECT '                        ' AS 'DATABASENAME',* INTO ##FINALTABLEFORDB  FROM ##TEMPFORDB WHERE 1=2


INSERT INTO ##FINALTABLEFORDB  
SELECT DB_NAME(),* FROM ##TEMPFORDB 


INSERT INTO ##FINALTABLEFORDB  
 SELECT 'TEMPDB',* FROM ##TEMPFORTEMPDB


INSERT INTO ##FINALTABLEFORDB  
SELECT DATABASENAME,101,'TOTAL LOG SIZE IN GB',CONVERT(NUMERIC(20),LOGSIZE)/1024 , (CONVERT(NUMERIC(20),((CONVERT(NUMERIC(20),LOGSPACEUSED)*LOGSIZE)/100)))/1024,'',''  
FROM ##TMPLOGSPACE WHERE DATABASENAME IN (DB_NAME(),'TEMPDB' )


SELECT UPPER(DATABASENAME),FILEID,GROUPNAME,CONVERT(NUMERIC(20),TOTALSPACE_MB) AS TOTALSPACE_MB,CONVERT(NUMERIC(20),SPACEUSED_MB) AS SPACEUSED_MB ,CONVERT(NUMERIC(20),(TOTALSPACE_MB-SPACEUSED_MB)) AS 'FREESPACE_MB' , LFILENM,PFILENM 
FROM ##FINALTABLEFORDB  ORDER BY 1,2


DROP TABLE ##DATASPACEFORDB, ##TEMPFORDB
DROP TABLE ##DATASPACEFORTEMPDB, ##TEMPFORTEMPDB
DROP TABLE ##TMPLOGSPACE, ##FINALTABLEFORDB


 --temp table for further querying
CREATE TABLE #temp(
rec_id int IDENTITY (1, 1),
table_name varchar(128),
nbr_of_rows int,
data_space decimal(15,2),
index_space decimal(15,2),
total_size decimal(15,2),
percent_of_db decimal(15,12),
db_size decimal(15,2))


-- Get all tables, names, and sizes
EXEC sp_msforeachtable @command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_mstablespace '?'",
@command2="update #temp set table_name = '?' where rec_id = (select max(rec_id) from #temp)"


-- Set the total_size and total database size fields
UPDATE #temp
SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp)


-- Set the percent of the total database size
UPDATE #temp
SET percent_of_db = (total_size/db_size) * 100


-- Get the data
SELECT *
FROM #temp
ORDER BY total_size DESC


-- Comment out the following line if you want to do further querying
DROP TABLE #temp


GO