I want to start off by saying, I don’t know SQL very well. I received a script from a Service Connect Developer and he gave me a script to shrink by ESC Database. I have over 10k files being dropped into Service Connect daily and it fills up by storage on my SQL Server. He gave me this script, but it fails about 30 mins into running because it runs out of space. So, basically, I get nothing deleted. Please help me add a date range to run it for a shorter amount of time and run it is sections.
aidacra
(Nathan your friendly neighborhood Support Engineer)
2
Can you provide the script that you were executing?
alter table ScaTrace with nocheck nocheck constraint all
alter table ScaTracedInterfacialDocument with nocheck nocheck constraint all
alter table ScaTracedTask with nocheck nocheck constraint all
delete from ScaTracedInterfacialDocument
delete from ScaTracedTask
delete from ScaTrace
alter table ScaTracedInterfacialDocument with check check constraint all
alter table ScaTracedTask with check check constraint all
alter table ScaTrace with check check constraint all
commit tran
–rollbak tran
–select size, * from sys.database_files
–DBCC SHRINKFILE ( ESCDB_log, 1)
–dbcc shrinkdatabase (ESCDB, 10)
–select size, * from sys.database_files
aidacra
(Nathan your friendly neighborhood Support Engineer)
4
try this
--copy below this line
begin tran
alter table ScaTrace with nocheck nocheck constraint all
alter table ScaTracedInterfacialDocument with nocheck nocheck constraint all
alter table ScaTracedTask with nocheck nocheck constraint all
truncate table ScaTracedInterfacialDocument
truncate table ScaTracedTask
truncate table ScaTrace
alter table ScaTracedInterfacialDocument with check check constraint all
alter table ScaTracedTask with check check constraint all
alter table ScaTrace with check check constraint all
commit tran
--rollback tran
PRINT 'rebuild started! ' + CONVERT(char(23), GETDATE(), 121)
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
PRINT @sql
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
PRINT 'rebuild completed! ' + CONVERT(char(23), GETDATE(), 121)
2 Likes
aidacra
(Nathan your friendly neighborhood Support Engineer)
5
and I should have explicitly mentioned this, but, whenever running a script against any database–even if it comes from a friendly neighborhood Support engineer–one should always back up the database first just in case
