August 8, 2011 at 3:45 am
Hello
Unfortunately I have to deal with a problem, because my colleague is on vacation for 3 weeks.
We have a backup software from Acronis that was installed using SQL Express databases. One of them has grown to 4 GB and affects the correct function of the backup software. According to some other tips in the www I've already tested, which part is affected by viewing [Database> shrink> file].
The file type "data" (4009.31 MB) is much bigger than "protocol" (1MB).
The db "acronis_cms_cards" has several tables, but only two of them contain very many records. After a lot of tries I was able to shrink the first table by manually deleting all lines. However, the other one drives me crazy. Trying to shrink the Database directly by using the function of the SQL Server Management Studio Express does not make any changes. If i am trying to delete some rows of the table manually i get an error message:
"There were no rows deleted. When trying to delete line 1, a problem has occurred. Error Source:. Net SqlClient Data Provider.
Error message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Correct the error and try again to delete the line, or press the ESC key to the changes (s) cancel. "
August 8, 2011 at 3:52 am
Use a delete query (in a T-SQL query window), not the table editor.
The table editor's got a fair few bugs and does the deletes one row at a time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 8, 2011 at 7:55 am
Thx for your very quick answer and Sry I forgot to mention it...
My colleague made an query for the table:
USE [Acronis_cms_cards]
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
USE [Acronis_cms_cards]
go
dbcc shrinkdatabase ('Acronis_cms_cards', NOTRUNCATE)
go
dbcc shrinkdatabase ('Acronis_cms_cards', TRUNCATEONLY)
Runing it gives me the following Message:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
The statement has been terminated.
Meldung 1105, Ebene 17, Status 2, Zeile 10
Could not allocate space for object 'dbo.SORT temporary run storage: 422212790124544' in database 'acronis_cms_cards' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
(2 Zeile(n) betroffen)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 Zeile(n) betroffen)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
There was a short topic at a webpage for Acronis where the following command was prompted into Windows-Commandline:
osql -E -S .\ACRONIS -d acronis_cms_cards -Q "DELETE FROM
IncomingCardsLog; DELETE FROM SyncWorkersResults; DELETE FROM SyncWorkersLog;
DBCC SHRINKDATABASE(N'acronis_cms_cards')"
In the cmd this command gave an error back. Later I cleared the table "SyncWokersResults" manually. So today I shortened it for the query
USE [Acronis_cms_cards]
DELETE FROM SyncWorkersLog
While the protocol was limited to 256MB I nearly instantly get the message:
(60007 rows affected) Msg 9002, Level 17, State 4, Server SVFIWAHABU01\ACRONIS, Line 1
The transaction log for database ‘acronis_cms_cards’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
After removing the limitation I can see the "acronis_cms_cards_log.ldf" growing to 11.5GB, also the "tempdb.mdf" is growing to 610MB. Then all disc space is used and there is the same Message again
Meldung 9002, Ebene 17, Status 4, Zeile 2
The transaction log for database 'acronis_cms_cards' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Meldung 9002, Ebene 17, Status 4, Zeile 2
The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
August 8, 2011 at 8:15 am
You want to delete in chunks, so that the log doesn't blow out.
Simple recovery or full recovery model?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 8, 2011 at 8:55 am
simple Mode
As you can see, I have no real idea about SQL 😀
But I have learned some interesting things since last week by reading some commands and their opportunities or viewing some similar cases.
August 8, 2011 at 9:09 am
And you're absolutely sure you want to remove every single row from that table?
Is that table referenced by any foreign key constraints?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 8, 2011 at 10:05 am
The table is called *log, so I hope I can simply delete the lines. Each line is time-stamped and this goes back to 2009 ...
I could see that there is only one object that is dependent on the table SyncWorkersLog. It is called SyncLogsRetentionRule and from the type "trigger".
However, I was not able to find it anywhere in the database.
Thanks again for your patience. * grin *
August 8, 2011 at 10:12 am
Excellent. In that case...
TRUNCATE TABLE SyncWorkersLog
You can shrink after that, but I wouldn't recommend it. There will be space free in the data file, so the file won't try to grow (and get those nasty errors)
If you do decide to shrink, don't shrink to nothing, leave some free space and then go and rebuild all of the indexes in the database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 8, 2011 at 10:13 am
p.s. Take a full backup first and store it somewhere. Just incase someone does want those records later.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 9, 2011 at 3:56 am
You are my hero 😉
The Database is 4009 MB large and more than 4000 MB are unused. I switched the Protocol back to a limited size and I hope my Acronis will work in a correct way now, so all tasks are shown with their correct timestamps. THX
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply