views:

23

answers:

0

Hi,

I am using following script to shrink the database:

****set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[ShrinkDatabaseProc]
AS
BEGIN
SET NOCOUNT ON

DECLARE @FileName NVARCHAR(260),
 @GroupId SMALLINT,
 @strSQL  NVARCHAR(4000),
 @DbId  INT,
 @DatabaseName NVARCHAR(260)
SELECT @DbId = dbid FROM master..sysprocesses WHERE spid = @@spid
SELECT @DatabaseName = [name] FROM master..sysdatabases where dbid = @DbId

SET NOCOUNT ON
DECLARE Cur_FileInfo CURSOR LOCAL FAST_FORWARD
 FOR SELECT [name], groupid FROM sysfiles
OPEN Cur_FileInfo
FETCH NEXT FROM Cur_FileInfo INTO @FileName, @GroupId
WHILE @@FETCH_STATUS = 0
BEGIN 
IF(@GroupId = 0)
BEGIN   
 IF LEFT(CAST(SERVERPROPERTY('ProductVersion') As Varchar),2)='10'
 BEGIN
    SET @strSQL = 'BEGIN DBCC SHRINKFILE(''' + LTRIM(RTRIM(@FileName)) + ''', 1) WITH NO_INFOMSGS ' +  'DBCC SHRINKDATABASE (''' + @DatabaseName + ''' , TRUNCATEONLY) WITH NO_INFOMSGS END'
 END
 ELSE
    BEGIN
    SET @strSQL = 'BEGIN DBCC SHRINKFILE(''' + LTRIM(RTRIM(@FileName)) + ''', 1) WITH NO_INFOMSGS BACKUP LOG ' + @DatabaseName + ' WITH TRUNCATE_ONLY DBCC SHRINKFILE(''' + LTRIM(RTRIM(@FileName)) + ''', 1) WITH NO_INFOMSGS END'
 END   
 EXEC(@strSQL)
END
ELSE
BEGIN
 SET @strSQL = 'DBCC SHRINKFILE(''' + LTRIM(RTRIM(@FileName)) + ''', 1) WITH NO_INFOMSGS '
 EXEC(@strSQL)
END
  FETCH NEXT FROM Cur_FileInfo INTO @FileName, @GroupId
 END
CLOSE Cur_FileInfo
DEALLOCATE Cur_FileInfo
END****

Following error is returned when script is run first time. However, script runs second time.

The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x000000011d0000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.5\MSSQL\DATA\qc5988_2010927 64341857PM.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

How to resolve this error?

Thanks in advance.