DBCC SHRINKDATABASE

My old thread seems to evoke no response anymore, so I will just post a new
one.
database_name=AP_ODS
database_size=76024.00 MB
unallocated space=49730.95 MB
Above is the output when I ran the statements below:
sp_spaceused @.updateusage=TRUE
go
DBCC SHRINKFILE ('WAREHOUSE_DATA', TRUNCATEONLY)
go
Can anyone please help me out here? How the hell can I get the 49GB free spa
ce released to the OS?
Thank you.Alex,
TruncaleOnly may not do anything at all. From BOL:
Causes any unused space in the files to be released to the operating system
and shrinks the file to the last allocated extent, reducing the file size
without moving any data. No attempt is made to relocate rows to unallocated
pages. target_size is ignored when TRUNCATEONLY is used.
You see all shrink operations happen from the end of the file. If there is
data near the end and free space near the beginning (and you don't specify
TruncateOnly) sql server has to physically move that data to the beginning
to free up the space at the end. So if you omit the TruncateOnly it should
shrink but it may take a while. It will also more than likely heavily
fragment your tables. To unfragment them you need to reindex them and that
will cause the database to grow again and your back to where you started.
DB's need plenty of free space in them to operate properly. If you don't
care about frgamentation then go ahead and shrink it. If you do, and by the
name I would assume you would, leave the free space.
Andrew J. Kelly
SQL Server MVP
"Alex G" <anonymous@.discussions.microsoft.com> wrote in message
news:04DC64C2-6683-425E-BB9C-7F664457F5A1@.microsoft.com...
> My old thread seems to evoke no response anymore, so I will just post a
new one.
> database_name=AP_ODS
> database_size=76024.00 MB
> unallocated space=49730.95 MB
>
> Above is the output when I ran the statements below:
> sp_spaceused @.updateusage=TRUE
> go
> DBCC SHRINKFILE ('WAREHOUSE_DATA', TRUNCATEONLY)
> go
> Can anyone please help me out here? How the hell can I get the 49GB free
space released to the OS?
> Thank you.
>|||Andrew,
I appreciate your response. Let us for a moment forget that there is space o
n the server. Technically, what is the best way to get the free space back?
"So if you omit the TruncateOnly it should shrink but it may take a while."
- No sir, ommiting the TRUNCATEONLY option does nothing as well.
Worst case, what's the explanation for sql server hogging 70% of table space
for nothing?!!
Thanks.
-- Andrew J. Kelly wrote: --
Alex,
TruncaleOnly may not do anything at all. From BOL:
Causes any unused space in the files to be released to the operating system
and shrinks the file to the last allocated extent, reducing the file size
without moving any data. No attempt is made to relocate rows to unallocated
pages. target_size is ignored when TRUNCATEONLY is used.
You see all shrink operations happen from the end of the file. If there is
data near the end and free space near the beginning (and you don't specify
TruncateOnly) sql server has to physically move that data to the beginning
to free up the space at the end. So if you omit the TruncateOnly it should
shrink but it may take a while. It will also more than likely heavily
fragment your tables. To unfragment them you need to reindex them and that
will cause the database to grow again and your back to where you started.
DB's need plenty of free space in them to operate properly. If you don't
care about frgamentation then go ahead and shrink it. If you do, and by the
name I would assume you would, leave the free space.
Andrew J. Kelly
SQL Server MVP
"Alex G" <anonymous@.discussions.microsoft.com> wrote in message
news:04DC64C2-6683-425E-BB9C-7F664457F5A1@.microsoft.com...
> My old thread seems to evoke no response anymore, so I will just post a
new one.
> database_size=76024.00 MB
> unallocated space=49730.95 MB
> go
> DBCC SHRINKFILE ('WAREHOUSE_DATA', TRUNCATEONLY)
> go
space released to the OS?
>|||Alex,
What version are you running? In 7.0 sometimes it was required to backup
the log one or more times to get this to happen. It may not be immediate.
If you have open trans it can also effect it. The other thing that is most
likely the problem is that if the file was originally created at that size
it can not be shrunk below that. Can you post the results of the Shrinkfile
command?
Andrew J. Kelly
SQL Server MVP
"Alex G" <anonymous@.discussions.microsoft.com> wrote in message
news:6654D78D-C1BA-4F9A-8823-5AE362722D3D@.microsoft.com...
> Andrew,
> I appreciate your response. Let us for a moment forget that there is space
on the server. Technically, what is the best way to get the free space back?
> "So if you omit the TruncateOnly it should shrink but it may take a
while." - No sir, ommiting the TRUNCATEONLY option does nothing as well.
> Worst case, what's the explanation for sql server hogging 70% of table
space for nothing?!!
> Thanks.
> -- Andrew J. Kelly wrote: --
> Alex,
> TruncaleOnly may not do anything at all. From BOL:
> Causes any unused space in the files to be released to the operating
system
> and shrinks the file to the last allocated extent, reducing the file
size
> without moving any data. No attempt is made to relocate rows to
unallocated
> pages. target_size is ignored when TRUNCATEONLY is used.
> You see all shrink operations happen from the end of the file. If
there is
> data near the end and free space near the beginning (and you don't
specify
> TruncateOnly) sql server has to physically move that data to the
beginning
> to free up the space at the end. So if you omit the TruncateOnly it
should
> shrink but it may take a while. It will also more than likely
heavily
> fragment your tables. To unfragment them you need to reindex them
and that
> will cause the database to grow again and your back to where you
started.
> DB's need plenty of free space in them to operate properly. If you
don't
> care about frgamentation then go ahead and shrink it. If you do, and
by the
> name I would assume you would, leave the free space.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Alex G" <anonymous@.discussions.microsoft.com> wrote in message
> news:04DC64C2-6683-425E-BB9C-7F664457F5A1@.microsoft.com...
post a
> new one.
49GB free
> space released to the OS?|||Try this
backup log DBName with truncate_only
dbcc shrinkfile ('DBname',0,truncateonly)
dbcc shrinkfile ('DBName',0,truncateonly)

>--Original Message--
>Alex,
>What version are you running? In 7.0 sometimes it was
required to backup
>the log one or more times to get this to happen. It may
not be immediate.
>If you have open trans it can also effect it. The
other thing that is most
>likely the problem is that if the file was originally
created at that size
>it can not be shrunk below that. Can you post the
results of the Shrinkfile
>command?
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Alex G" <anonymous@.discussions.microsoft.com> wrote in
message
>news:6654D78D-C1BA-4F9A-8823-
5AE362722D3D@.microsoft.com...
that there is space
>on the server. Technically, what is the best way to get
the free space back?
it may take a
>while." - No sir, ommiting the TRUNCATEONLY option does
nothing as well.
hogging 70% of table
>space for nothing?!!
BOL:
released to the operating
>system
extent, reducing the file
>size
relocate rows to
>unallocated
is used.
of the file. If
>there is
beginning (and you don't
>specify
that data to the
>beginning
the TruncateOnly it
>should
more than likely
>heavily
need to reindex them
>and that
back to where you
>started.
properly. If you
>don't
it. If you do, and
>by the
space.
wrote in message
7F664457F5A1@.microsoft.com...
anymore, so I will just
>post a
below:
hell can I get the
>49GB free
>
>.
>

DBCC Checktable Error

Hi,
I used DBCC CHECKTABLE ('xorig_pan') WITH ALL_ERRORMSGS
and I had the following response "Serveur : Msg 7965, Niveau 16, =C9tat 2, Ligne 1
Table corrupt: Could not check object ID 704601494, index ID 0 due to invalid allocation (IAM) page(s).
Serveur : Msg 8968, Niveau 16, =C9tat 1, Ligne 1
Table Corrupt: IAM page (1:223) (object ID 704601494, index ID 0) is out of the range of this database.
DBCC results for 'xorig_pan'.
There are 0 rows in 0 pages for object 'xorig_pan'.
CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'xorig_pan' (object ID 704601494).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE ( dbo.xorig_pan )."
What can I do? I try to apply a DBCC CHECKTABLE ('xorig_pan' , REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS but I need to be in a single user mode. How can I switch SQL Server in a single user mode ?
Thank you for your helpHi.
Clik on the right button of the mouse in your DATABASE,
select properties after select options .In the options you have a option the restric access choose SINGLE USER.
OR
By T-SQL
EXEC sp_dboption 'DATABASE_NAME', 'single user', 'TRUE'
>--Original Message--
>Hi,
>I used DBCC CHECKTABLE ('xorig_pan') WITH ALL_ERRORMSGS
>and I had the following response >"Serveur : Msg 7965, Niveau 16, =C9tat 2, Ligne 1
>Table corrupt: Could not check object ID 704601494, index >ID 0 due to invalid allocation (IAM) page(s).
>Serveur : Msg 8968, Niveau 16, =C9tat 1, Ligne 1
>Table Corrupt: IAM page (1:223) (object ID 704601494, >index ID 0) is out of the range of this database.
>DBCC results for 'xorig_pan'.
>There are 0 rows in 0 pages for object 'xorig_pan'.
>CHECKTABLE found 0 allocation errors and 2 consistency >errors in table 'xorig_pan' (object ID 704601494).
>repair_allow_data_loss is the minimum repair level for the >errors found by DBCC CHECKTABLE ( dbo.xorig_pan )."
>What can I do? I try to apply a DBCC CHECKTABLE >('xorig_pan' , REPAIR_ALLOW_DATA_LOSS) WITH >ALL_ERRORMSGS but I need to be in a single user mode. >How can I switch SQL Server in a single user mode ?
>Thank you for your help
>.
>|||Here are the general recommendations for handling a suspect or corrupt
database:
0. Ensure you have a backup strategy that you can use to recover from
hardware failures (including corruption). I recommend performing both
database and log backup in most situations.
1. If you can run DBCC CHECKDB against the database: Search Books Online and
KB for the error numbers that CHECKDB gives you. There might be specific
info for that type of error.
2. Find out why this happened. Check eventlog, do HW diagnostics etc.;
search Books Online and KB for those errors. You don't want this to happen
again! If the database is suspect, the file might have been in use by for
instance an anti-virus program and restarting SQL Server might be all that
is needed - but you still want to read logs etc to find out what happened.
3. If there is a hardware problem, ensure the faulty hardware is replaced.
4. Backup the log. This assumes that log backup schedule is in place, of
course. If the database is suspect, then the NO_TRUNCATE option for the
RESTORE command must be used. Also, you might want to do a file backup of
the mdf and ldf files, for extra safety.
5. Restore is the best thing to do now. If you managed to backup log as per
step 4, then you will most probably have zero dataloss. You should restore
the latest clean database backup and the subsequent log backups including
the one taken in above step.
If the database isn't suspect, then DBCC with a REPAIR option might be a
secondary option but this will often result in loss of data. Additional
solutions, depending on the errors, may be to manually rebuild non-clustered
indexes, manually drop and reload a table if the data is static, and so on.
If the database is suspect, a secondary option can be to try to "un-suspect"
the database using sp_resetstatus. Read about it (books online, KB, google
etc). It might help but if the database is too damaged, it might just pop
back to suspect again. There's also something called "emergency mode" which
is a "panic" status you can set in order to try to get data out of a damaged
database. I think the name of that option speaks for itself. Again search
the net for info.
If you feel uncertain with above steps, I recommend letting MS hand-hold you
through the steps appropriate for your particular situation.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"LEMARIE" <sylvain.lemarie@.solymatic.com> wrote in message
news:023b01c3aa95$d47b3840$a001280a@.phx.gbl...
Hi,
I used DBCC CHECKTABLE ('xorig_pan') WITH ALL_ERRORMSGS
and I had the following response
"Serveur : Msg 7965, Niveau 16, État 2, Ligne 1
Table corrupt: Could not check object ID 704601494, index
ID 0 due to invalid allocation (IAM) page(s).
Serveur : Msg 8968, Niveau 16, État 1, Ligne 1
Table Corrupt: IAM page (1:223) (object ID 704601494,
index ID 0) is out of the range of this database.
DBCC results for 'xorig_pan'.
There are 0 rows in 0 pages for object 'xorig_pan'.
CHECKTABLE found 0 allocation errors and 2 consistency
errors in table 'xorig_pan' (object ID 704601494).
repair_allow_data_loss is the minimum repair level for the
errors found by DBCC CHECKTABLE ( dbo.xorig_pan )."
What can I do? I try to apply a DBCC CHECKTABLE
('xorig_pan' , REPAIR_ALLOW_DATA_LOSS) WITH
ALL_ERRORMSGS but I need to be in a single user mode.
How can I switch SQL Server in a single user mode ?
Thank you for your help|||Hi sylvain:
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
In addition to Jorge and Tibor's information, I provide some for your
referene.
As we know, the 7965 and 8968 errors indicate the allocation or
inconsistency errors on your database. Did you have the recent backup for
this database? Restoring the backup file is the most efficiency methods to
recover the database.
If not, please try the following steps to recover the database. However, we
cannot guarantee that there is no data loss for your database.
1.Backup your current database to avoid the risk during our troubleshooting
process
2.Set the database with single user mode.
There are two ways. I list both of them for your reference.
In the SQL Enterprise Manager, right click the problematic database and
click properties. Click the options tab. Check the ¡®Restrict Access¡¯
checkbox and choose ¡®Single user¡¯
Or
In the SQL query analyzer execute T-SQL statement ¡°EXEC sp_dboption
'DATABASE_NAME', 'single user', 'TRUE'¡°.
Note:
a. Please replace the ¡°'DATABASE_NAME¡± with the actual database name
b. Make sure that no other user is connected with this database before you
perform the operations above.
3.In SQL Query analyzer, execute the ¡®dbcc checkdb with
repair_allow_data_loss¡¯
Note:
Please replace the ¡°'DATABASE_NAME¡± with the actual database name
If the steps above do not work, please post the following information in
order to let us have a concrete idea regarding this issue.
1.The application log and the system log
2.The SQLdiag output
To collect the SQLDiag.txt, open a Command Prompt, and change the current
directory to wherever the SQL binaries are (for example: C:\Program
Files\Microsoft SQL Server\MSSQL\Binn) and execute 'SQLDIAG.EXE'.
Have a good weekend
Sincerely Yours
Wei,Baisong
| Content-Class: urn:content-classes:message
| From: "LEMARIE" <sylvain.lemarie@.solymatic.com>
| Sender: "LEMARIE" <sylvain.lemarie@.solymatic.com>
| Subject: DBCC Checktable Error
| Date: Fri, 14 Nov 2003 01:58:18 -0800
| Lines: 22
| Message-ID: <023b01c3aa95$d47b3840$a001280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: quoted-printable
| X-Newsreader: Microsoft CDO for Windows 2000
| Thread-Index: AcOqldR4uc6m479DRzCXnSdw0qYkGg==| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.sqlserver.server
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:316502
| NNTP-Posting-Host: TK2MSFTNGXA08 10.40.1.160
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Hi,
| I used DBCC CHECKTABLE ('xorig_pan') WITH ALL_ERRORMSGS
| and I had the following response
| "Serveur : Msg 7965, Niveau 16, État 2, Ligne 1
| Table corrupt: Could not check object ID 704601494, index
| ID 0 due to invalid allocation (IAM) page(s).
| Serveur : Msg 8968, Niveau 16, État 1, Ligne 1
| Table Corrupt: IAM page (1:223) (object ID 704601494,
| index ID 0) is out of the range of this database.
| DBCC results for 'xorig_pan'.
| There are 0 rows in 0 pages for object 'xorig_pan'.
| CHECKTABLE found 0 allocation errors and 2 consistency
| errors in table 'xorig_pan' (object ID 704601494).
| repair_allow_data_loss is the minimum repair level for the
| errors found by DBCC CHECKTABLE ( dbo.xorig_pan )."
| What can I do? I try to apply a DBCC CHECKTABLE
| ('xorig_pan' , REPAIR_ALLOW_DATA_LOSS) WITH
| ALL_ERRORMSGS but I need to be in a single user mode.
| How can I switch SQL Server in a single user mode ?
| Thank you for your help
|