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.