Error in SQL Maintenance Plan of DBCC Shrink

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Error in SQL Maintenance Plan of DBCC Shrink

Author

Topic

pukprasert
Starting Member

4 Posts

Posted - 2010-07-12 : 23:26:45

In Maintenance Plan, It has 2 step as below.
1. Rebuild Index
2. Shrink File (.mdf , .ldf)
So, I got the error message as below.

NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042
Report was generated on "DISERVE-DB-01".
Maintenance Plan: TEST_RebuildAndShrink
Duration: 02:35:46
Status: Warning: One or more tasks failed..
Details:
Rebuild Index Task (DISERVE-DB-01)
Task start: 2010-07-11T17:33:22.
Task end: 2010-07-11T20:06:56.
Success

Shrink Database & Transaction Log (DISERVE-DB-01)
Execute TSQL on Local server connection
Execution time out: 0
Task start: 2010-07-11T20:06:56.
Task end: 2010-07-11T20:07:25.
Failed:(-1073548784) Executing the query "use DM_TESTDMS01_docbase
dbcc shrinkdatabase('DM_TESTDMS01_docbase')
dbcc shrinkfile('DM_TESTDMS01_docbase',1)
dbcc shrinkfile('DM_TESTDMS01_log',1)
backup log DM_TESTDMS01_docbase with truncate_only
dbcc shrinkfile('DM_TESTDMS01_log',1)
" failed with the following error: "A severe error occurred on the current command. The results, if any, should be discarded.
Changed database context to 'DM_TESTDMS01_docbase'.
File ID 1 of database ID 7 cannot be shrunk as it is either being shrunk by another process or is empty.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
File ID 1 of database ID 7 cannot be shrunk as it is either being shrunk by another process or is empty.
Cannot shrink log file 2 (DM_TESTDMS01_log) because total number of logical log files cannot be fewer than 2.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Cannot shrink log file 2 (DM_TESTDMS01_log) because total number of logical log files cannot be fewer than 2.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Command:use DM_TESTDMS01_docbase
dbcc shrinkdatabase(''DM_TESTDMS01_docbase'')
dbcc shrinkfile(''DM_TESTDMS01_docbase'',1)
dbcc shrinkfile(''DM_TESTDMS01_log'',1)
backup log DM_TESTDMS01_docbase with truncate_only
dbcc shrinkfile(''DM_TESTDMS01_log'',1)
go

As above message,

In green font, This process is rebuild index and then it got success message.

In red font, This process is shrink file and then it got error message. Before rebuild step i stop every connect but didn't stop mssqlserver service yet.

Could you please help me to solve this error!!! Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-13 : 00:26:03

First of all, you shouldn't be shrinking the database on a regular basis. It should rarely be done, almost never.

Second: http://weblogs.sqlteam.com/tarad/archive/2007/11/08/60394.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pukprasert
Starting Member

4 Posts

Posted - 2010-07-13 : 00:55:45

Maintenance Plan will run every month. But i got the error so i couldn't run maintenance plan yet.

Kristen
Test

22859 Posts

Posted - 2010-07-13 : 02:56:21

It doesn't matter that it only runs once a month, you should not shrink a database to a schedule, a database should only be shrunk under certain, and rare, exceptional circumstances.

Why are you needing to shrink the database? The answer to whatever that problem is probably lies via a different solution.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-13 : 13:14:56

See the link I posted for why you are getting the error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-07-13 : 16:29:07

Also, I see that you are performing the following:

backup log DM_TESTDMS01_docbase with truncate_only

This tells me that this database is set to full recovery model, and - you are not performing frequent transaction log backups. You should know that the above command is deprecated in SQL Server 2005 and is not functional (been removed) from SQL Server 2008. That process breaks the log chain and could prevent you from being able to restore the database to a point in time after a disaster. Especially if the most recent backup file is corrupt - you could lose a lot of data.

Kristen
Test

22859 Posts

Posted - 2010-07-14 : 02:44:20

Pukprasert

What are you wanting to achieve with this task? What problem do you have that you want to solve?

People here can give you good advice, but I think it may be better to start with the underlying problem that you are working on.

People here have expressed concern that you are doing some things that are probably not good practice. I'm sure they/we didn't mean to sound harsh.

How about we start over?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-14 : 03:25:18

A rebuild followed by a shrink has a common name - Waste of Time.

Shrink causes massive fragmentation of indexes, usually fragmenting them more than they were before the rebuild. You may as well not rebuild at all.

--
Gail Shaw
SQL Server MVP

pukprasert
Starting Member

4 Posts

Posted - 2010-07-14 : 06:12:11

Thanks for all.
Need to do in SQL Maintenance Plan.
1. Database defragment.
2. Reduce sizing after step 1 success.

Which command can i use for "Database defragment"????
Which command can i use for "Reduce sizing"???

In my SQL maintenance plan, I have 2 steps as below.
1. Rebuild index --> create by sql tools
2. Shrink file --> create by T-SQL with command below.
use DM_TESTDMS01_docbase
dbcc shrinkdatabase(''DM_TESTDMS01_docbase'')
dbcc shrinkfile(''DM_TESTDMS01_docbase'',1)
dbcc shrinkfile(''DM_TESTDMS01_log'',1)
backup log DM_TESTDMS01_docbase with truncate_only
dbcc shrinkfile(''DM_TESTDMS01_log'',1)
go

Please help me to solve this issue.

Thanks so much.

Kristen
Test

22859 Posts

Posted - 2010-07-14 : 06:57:12

You definitely don't want to do "2. Reduce sizing after step 1 success". Your database will shrink, all the defragmented indexes will become fragmented in the process (as Gail has described) and then the database size will increase (as transactions happen) back to the original size. This sort of SHRINK will only cause fragmentation and make your system slower.

If someone has deleted a large amount of data (as a one-off task), or there has been an accidental run-away transaction, etc., then fine - do a one-off shrink of the database to get it back to its normal maximum size but never scheduled automated shrinking, and do not shrink the database below the size it naturally grows to.

If your database is too large, and it is in FULL Recovery model, then make sure your Transaction log backups are running frequently. Every 15 minutes is a sensible setting (and definitely NOT once-a-day, and preferably not once-an-hour).

When you have done that look at the size of the Transaction log Backup files over the previous few days. Are some backups much larger than the others? Do you see the same "much larger" backup files at the same time each day? If so then you have a task running at that time which is causing large numbers of transaction. You need to find that task, and then decide how to stop it bloating the database and transaction logs. It is common for Index Rebuilds to be the cause of this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources